EBS开发_创建销售人员API

--以下代码于20190708在Tony环境实现

--CREATE OR REPLACE PROCEDURE CUX_TEST_CREATE_SALESREP_API(p_effective_date DATE) AS
DECLARE
  CURSOR cur_emp_fetch_data IS
  
    SELECT
    
     PER.person_id,
     
     PER.full_name,
     
     PER.first_name,
     
     PER.last_name,
     
     USR.user_name,
     
     PER.employee_number,
     
     PER.EMAIL_ADDRESS,
     
     USR.user_id,
     PER.EFFECTIVE_START_DATE
    
      FROM apps.PER_ALL_PEOPLE_F PER,
           
           apps.FND_USER USR
    
     WHERE
    
     USR.employee_id = PER.person_id
    
     AND PER.EFFECTIVE_START_DATE =
     NVL(TO_DATE('2018-1-14', 'YYYY-MM-DD'), TRUNC(SYSDATE));

  ln_responsibility_id NUMBER;

  ln_application_id NUMBER;

  ln_user_id PLS_INTEGER := fnd_global.user_id;

  lc_return_status VARCHAR2(1);

  ln_msg_count NUMBER;

  lc_msg_data VARCHAR2(5000);

  lc_msg_dummy VARCHAR2(5000);

  lc_output VARCHAR2(5000);

  ln_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;

  lc_resource_number JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE;

  ln_salesrep_number NUMBER;

  ln_salesrep_id NUMBER;

  l_msg_index_out NUMBER;

BEGIN

  FOR rec_cur_emp_fetch_data IN cur_emp_fetch_data
  
   LOOP
  
    DBMS_OUTPUT.PUT_LINE('COMES HERE');
  
    SELECT
    
     responsibility_id,
     
     application_id
    
      INTO ln_responsibility_id,
           
           ln_application_id
    
      FROM fnd_responsibility_vl
    
     WHERE
    
     responsib