DBA Data[Home] [Help]

APPS.BEN_PRE_DATAPUMP_PROCESS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 16

        ongoing mass updates and IVR Process.
History
	Date		Who         Version	What?
	----		---	    -------	-----
	01 Nov 05	ikasire     115.0       Created
        24 Jan 06       ikasired    115.2       Added update for
                                                ATOMIC_LINKED_CALLS
        08 Feb 06       ikasired    115.3       Added code for full_name and
                                                DOB Validation
        01 Mar 06       ikasired    115.4       Exclude C records
        02 Mar 06       ikasired    115.5       get_pl_id error fix
        23 Mar 06       ikasire     115.6       Added stub for Beneficiaries
                                                future validations if required
                                                for multirows edits
        24 Mar 06       ikasired    115.7       Deleting the POST records to
                                                avoid accidental deletion of the
                                                records.
        13 Apr 06       nkkrishn    115.8       Summary row elimination changes
        19 Apr 06       ikasired    115.9       removed the DELETE for POST rows
                                                as we are not creating any new
                                                rows
        02 May 06       nkkrishn        115.11  Fixed Beneficiary upload
*/
--
--Globals
--
g_debug boolean := hr_utility.debug_enabled;
Line: 48

procedure insert_create_enrollment
         (
          BATCH_ID                         NUMBER
         ,API_MODULE_ID                    NUMBER
         ,USER_SEQUENCE                    NUMBER
         ,LINK_VALUE                       NUMBER
         ,BUSINESS_GROUP_NAME              VARCHAR2
         ,P_LIFE_EVENT_DATE                VARCHAR2
         ,P_EFFECTIVE_DATE                 VARCHAR2
         ,P_PROC_CD                        VARCHAR2
         ,P_PROGRAM                        VARCHAR2
         ,P_PROGRAM_NUM                    VARCHAR2
         ,P_PLAN                           VARCHAR2
         ,P_PLAN_NUM                       VARCHAR2
         ,P_LIFE_EVENT_REASON              VARCHAR2
         ,P_EMPLOYEE_NUMBER                VARCHAR2
         ,P_NATIONAL_IDENTIFIER            VARCHAR2
         ,P_FULL_NAME                      VARCHAR2
         ,P_DATE_OF_BIRTH                  VARCHAR2
         ,P_PERSON_NUM                     VARCHAR2
         ) is
  begin
   INSERT INTO hrdpv_create_enrollment (
     BATCH_ID
     ,BATCH_LINE_ID
     ,API_MODULE_ID
     ,LINE_STATUS
     ,USER_SEQUENCE
     ,LINK_VALUE
     ,BUSINESS_GROUP_NAME
     ,P_LIFE_EVENT_DATE
     ,P_EFFECTIVE_DATE
     ,P_PROC_CD
     ,P_RECORD_TYP_CD
     ,P_PROGRAM
     ,P_PROGRAM_NUM
     ,P_PLAN
     ,P_PLAN_NUM
     ,P_LIFE_EVENT_REASON
     ,P_EMPLOYEE_NUMBER
     ,P_NATIONAL_IDENTIFIER
     ,P_FULL_NAME
     ,P_DATE_OF_BIRTH
     ,P_PERSON_NUM  )
   VALUES
     (BATCH_ID
     ,hr_pump_batch_lines_s.NEXTVAL
     ,API_MODULE_ID
     ,'U'
     ,USER_SEQUENCE
     ,LINK_VALUE
     ,BUSINESS_GROUP_NAME
     ,P_LIFE_EVENT_DATE
     ,P_EFFECTIVE_DATE
     ,P_PROC_CD
     ,'POST'
     ,P_PROGRAM
     ,P_PROGRAM_NUM
     ,P_PLAN
     ,P_PLAN_NUM
     ,P_LIFE_EVENT_REASON
     ,P_EMPLOYEE_NUMBER
     ,P_NATIONAL_IDENTIFIER
     ,P_FULL_NAME
     ,P_DATE_OF_BIRTH
     ,P_PERSON_NUM
     ) ;
Line: 115

  end insert_create_enrollment ;
Line: 117

procedure insert_process_dependent
         (
          BATCH_ID                         NUMBER
         ,API_MODULE_ID                    NUMBER
         ,USER_SEQUENCE                    NUMBER
         ,LINK_VALUE                       NUMBER
         ,BUSINESS_GROUP_NAME              VARCHAR2
         ,P_LIFE_EVENT_DATE                VARCHAR2
         ,P_EFFECTIVE_DATE                 VARCHAR2
         ,P_PROGRAM                        VARCHAR2
         ,P_PROGRAM_NUM                    VARCHAR2
         ,P_PLAN                           VARCHAR2
         ,P_PLAN_NUM                       VARCHAR2
         ,P_OPTION                         VARCHAR2
         ,P_OPTION_NUM                     VARCHAR2
         ,P_LIFE_EVENT_REASON              VARCHAR2
         ,P_EMPLOYEE_NUMBER                VARCHAR2
         ,P_NATIONAL_IDENTIFIER            VARCHAR2
         ,P_FULL_NAME                      VARCHAR2
         ,P_DATE_OF_BIRTH                  VARCHAR2
         ,P_PERSON_NUM                     VARCHAR2
         ) is
  begin
   INSERT INTO hrdpv_process_dependent (
      BATCH_ID
     ,BATCH_LINE_ID
     ,API_MODULE_ID
     ,LINE_STATUS
     ,USER_SEQUENCE
     ,LINK_VALUE
     ,BUSINESS_GROUP_NAME
     ,P_LIFE_EVENT_DATE
     ,P_EFFECTIVE_DATE
     ,P_RECORD_TYP_CD
     ,P_PROGRAM
     ,P_PROGRAM_NUM
     ,P_PLAN
     ,P_PLAN_NUM
     ,P_OPTION
     ,P_OPTION_NUM
     ,P_LIFE_EVENT_REASON
     ,P_EMPLOYEE_NUMBER
     ,P_NATIONAL_IDENTIFIER
     ,P_FULL_NAME
     ,P_DATE_OF_BIRTH
     ,P_PERSON_NUM  )
   VALUES
     (BATCH_ID
     ,hr_pump_batch_lines_s.NEXTVAL
     ,API_MODULE_ID
     ,'U'
     ,USER_SEQUENCE
     ,LINK_VALUE
     ,BUSINESS_GROUP_NAME
     ,P_LIFE_EVENT_DATE
     ,P_EFFECTIVE_DATE
     ,'POST'
     ,P_PROGRAM
     ,P_PROGRAM_NUM
     ,P_PLAN
     ,P_PLAN_NUM
     ,P_OPTION
     ,P_OPTION_NUM
     ,P_LIFE_EVENT_REASON
     ,P_EMPLOYEE_NUMBER
     ,P_NATIONAL_IDENTIFIER
     ,P_FULL_NAME
     ,P_DATE_OF_BIRTH
     ,P_PERSON_NUM
     ) ;
Line: 187

  end insert_process_dependent ;
Line: 195

    select ch.*
      from hrdpv_create_enrollment ch
     where ch.batch_id = p_batch_id
       and ch.line_status <> 'C'
     order by p_person_num,
              p_employee_number,
              p_national_identifier,
              p_full_name,
              p_date_of_birth,
              p_program,
              p_program_num,
              p_plan,
              p_plan_num,
              p_record_typ_cd
     for update;
Line: 249

    UPDATE hr_pump_batch_headers bh
       SET bh.ATOMIC_LINKED_CALLS = 'Y'
     WHERE bh.batch_id = p_batch_id ;
Line: 254

    UPDATE hrdpv_create_enrollment
       SET P_RECORD_TYP_CD = 'ENROLL'
    WHERE batch_id = p_batch_id;
Line: 261

         buff  => 'Updated Header for ATOMIC_LINKED_CALLS');
Line: 364

         INSERT_CREATE_ENROLLMENT
                (BATCH_ID                 => p_batch_id
                ,API_MODULE_ID            => l_prev_rec.api_module_id
                ,USER_SEQUENCE            => l_sequence
                ,LINK_VALUE               => l_prev_link
                ,BUSINESS_GROUP_NAME      => l_prev_rec.BUSINESS_GROUP_NAME
                ,P_LIFE_EVENT_DATE        => l_prev_rec.P_LIFE_EVENT_DATE
                ,P_EFFECTIVE_DATE         => l_prev_rec.P_EFFECTIVE_DATE
                ,P_PROC_CD                => l_prev_rec.P_PROC_CD
                ,P_PROGRAM                => l_prev_rec.P_PROGRAM
                ,P_PROGRAM_NUM            => l_prev_rec.P_PROGRAM_NUM
                ,P_PLAN                   => l_prev_rec.P_PLAN
                ,P_PLAN_NUM               => l_prev_rec.P_PLAN_NUM
                ,P_LIFE_EVENT_REASON      => l_prev_rec.P_LIFE_EVENT_REASON
                ,P_EMPLOYEE_NUMBER        => l_prev_rec.P_EMPLOYEE_NUMBER
                ,P_NATIONAL_IDENTIFIER    => l_prev_rec.P_NATIONAL_IDENTIFIER
                ,P_FULL_NAME              => l_prev_rec.P_FULL_NAME
                ,P_DATE_OF_BIRTH          => l_prev_rec.P_DATE_OF_BIRTH
                ,P_PERSON_NUM             => l_prev_rec.P_PERSON_NUM
                );
Line: 388

         update hrdpv_create_enrollment
            set p_record_typ_cd = 'POST'
          where batch_id = p_batch_id
            and batch_line_id = l_prev_rec.batch_line_id;
Line: 396

      update hrdpv_create_enrollment
         set user_sequence = l_sequence ,
                link_value = l_link_value
       where batch_id = p_batch_id
         and batch_line_id = i.batch_line_id ;
Line: 436

       INSERT_CREATE_ENROLLMENT
                (BATCH_ID                 => p_batch_id
                ,API_MODULE_ID            => l_prev_rec.api_module_id
                ,USER_SEQUENCE            => l_sequence
                ,LINK_VALUE               => l_prev_link
                ,BUSINESS_GROUP_NAME      => l_prev_rec.BUSINESS_GROUP_NAME
                ,P_LIFE_EVENT_DATE        => l_prev_rec.P_LIFE_EVENT_DATE
                ,P_EFFECTIVE_DATE         => l_prev_rec.P_EFFECTIVE_DATE
                ,P_PROC_CD                => l_prev_rec.P_PROC_CD
                ,P_PROGRAM                => l_prev_rec.P_PROGRAM
                ,P_PROGRAM_NUM            => l_prev_rec.P_PROGRAM_NUM
                ,P_PLAN                   => l_prev_rec.P_PLAN
                ,P_PLAN_NUM               => l_prev_rec.P_PLAN_NUM
                ,P_LIFE_EVENT_REASON      => l_prev_rec.P_LIFE_EVENT_REASON
                ,P_EMPLOYEE_NUMBER        => l_prev_rec.P_EMPLOYEE_NUMBER
                ,P_NATIONAL_IDENTIFIER    => l_prev_rec.P_NATIONAL_IDENTIFIER
                ,P_FULL_NAME              => l_prev_rec.P_FULL_NAME
                ,P_DATE_OF_BIRTH          => l_prev_rec.P_DATE_OF_BIRTH
                ,P_PERSON_NUM             => l_prev_rec.P_PERSON_NUM
                );
Line: 457

       update hrdpv_create_enrollment
          set p_record_typ_cd = 'POST'
        where batch_id = p_batch_id
          and batch_line_id = l_prev_rec.batch_line_id;
Line: 476

    select ch.*
      from hrdpv_process_dependent ch
     where ch.batch_id = p_batch_id
       and ch.line_status <> 'C'
     order by p_person_num,
              p_employee_number,
              p_national_identifier,
              p_full_name,
              p_date_of_birth,
              p_program,
              p_program_num,
              p_plan,
              p_plan_num,
              p_option,
              p_option_num,
              p_record_typ_cd
     for update;
Line: 532

    UPDATE hr_pump_batch_headers bh
       SET bh.ATOMIC_LINKED_CALLS = 'Y'
     WHERE bh.batch_id = p_batch_id ;
Line: 538

         buff  => 'Updated Header for ATOMIC_LINKED_CALLS');
Line: 541

    UPDATE hrdpv_process_dependent
       SET P_RECORD_TYP_CD = 'ENROLL'
    WHERE batch_id = p_batch_id;
Line: 702

	 INSERT_PROCESS_DEPENDENT
                (BATCH_ID                 => p_batch_id
                ,API_MODULE_ID            => l_prev_rec.api_module_id
                ,USER_SEQUENCE            => l_sequence
                ,LINK_VALUE               => l_prev_link
                ,BUSINESS_GROUP_NAME      => l_prev_rec.BUSINESS_GROUP_NAME
                ,P_LIFE_EVENT_DATE        => l_prev_rec.P_LIFE_EVENT_DATE
                ,P_EFFECTIVE_DATE         => l_prev_rec.P_EFFECTIVE_DATE
                ,P_PROGRAM                => l_prev_rec.P_PROGRAM
                ,P_PROGRAM_NUM            => l_prev_rec.P_PROGRAM_NUM
                ,P_PLAN                   => l_prev_rec.P_PLAN
                ,P_PLAN_NUM               => l_prev_rec.P_PLAN_NUM
                ,P_OPTION                 => l_prev_rec.P_OPTION
                ,P_OPTION_NUM             => l_prev_rec.P_OPTION_NUM
                ,P_LIFE_EVENT_REASON      => l_prev_rec.P_LIFE_EVENT_REASON
                ,P_EMPLOYEE_NUMBER        => l_prev_rec.P_EMPLOYEE_NUMBER
                ,P_NATIONAL_IDENTIFIER    => l_prev_rec.P_NATIONAL_IDENTIFIER
                ,P_FULL_NAME              => l_prev_rec.P_FULL_NAME
                ,P_DATE_OF_BIRTH          => l_prev_rec.P_DATE_OF_BIRTH
                ,P_PERSON_NUM             => l_prev_rec.P_PERSON_NUM
                );
Line: 727

         update hrdpv_process_dependent
            set p_record_typ_cd = 'POST'
          where batch_id = p_batch_id
            and batch_line_id = l_prev_rec.batch_line_id;
Line: 735

      update hrdpv_process_dependent
         set user_sequence = l_sequence ,
                link_value = l_link_value
       where batch_id = p_batch_id
         and batch_line_id = i.batch_line_id ;
Line: 778

       INSERT_PROCESS_DEPENDENT
                (BATCH_ID                 => p_batch_id
                ,API_MODULE_ID            => l_prev_rec.api_module_id
                ,USER_SEQUENCE            => l_sequence
                ,LINK_VALUE               => l_prev_link
                ,BUSINESS_GROUP_NAME      => l_prev_rec.BUSINESS_GROUP_NAME
                ,P_LIFE_EVENT_DATE        => l_prev_rec.P_LIFE_EVENT_DATE
                ,P_EFFECTIVE_DATE         => l_prev_rec.P_EFFECTIVE_DATE
                ,P_PROGRAM                => l_prev_rec.P_PROGRAM
                ,P_PROGRAM_NUM            => l_prev_rec.P_PROGRAM_NUM
                ,P_PLAN                   => l_prev_rec.P_PLAN
                ,P_PLAN_NUM               => l_prev_rec.P_PLAN_NUM
                ,P_OPTION                 => l_prev_rec.P_OPTION
                ,P_OPTION_NUM             => l_prev_rec.P_OPTION_NUM
                ,P_LIFE_EVENT_REASON      => l_prev_rec.P_LIFE_EVENT_REASON
                ,P_EMPLOYEE_NUMBER        => l_prev_rec.P_EMPLOYEE_NUMBER
                ,P_NATIONAL_IDENTIFIER    => l_prev_rec.P_NATIONAL_IDENTIFIER
                ,P_FULL_NAME              => l_prev_rec.P_FULL_NAME
                ,P_DATE_OF_BIRTH          => l_prev_rec.P_DATE_OF_BIRTH
                ,P_PERSON_NUM             => l_prev_rec.P_PERSON_NUM
                );
Line: 800

       update hrdpv_process_dependent
          set p_record_typ_cd = 'POST'
        where batch_id = p_batch_id
          and batch_line_id = l_prev_rec.batch_line_id;
Line: 823

    select ch.*
      from hrdpv_process_beneficiary ch
     where ch.batch_id = p_batch_id
       and ch.line_status <> 'C'
     order by p_person_num,
              p_employee_number,
              p_national_identifier,
              p_full_name,
              p_date_of_birth,
              p_program,
              p_program_num,
              p_plan,
              p_plan_num,
              p_option,
              p_option_num,
              p_record_typ_cd
     for update;
Line: 879

    UPDATE hr_pump_batch_headers bh
       SET bh.ATOMIC_LINKED_CALLS = 'Y'
     WHERE bh.batch_id = p_batch_id ;
Line: 885

         buff  => 'Updated Header for ATOMIC_LINKED_CALLS');
Line: 888

    UPDATE hrdpv_process_beneficiary
       SET P_RECORD_TYP_CD = 'ENROLL'
    WHERE batch_id = p_batch_id;
Line: 1043

         update hrdpv_process_beneficiary
            set p_record_typ_cd = 'POST'
          where batch_id = p_batch_id
            and batch_line_id = l_prev_rec.batch_line_id;
Line: 1051

      update hrdpv_process_beneficiary
         set user_sequence = l_sequence ,
                link_value = l_link_value
       where batch_id = p_batch_id
         and batch_line_id = i.batch_line_id ;
Line: 1078

       update hrdpv_process_beneficiary
          set p_record_typ_cd = 'POST'
        where batch_id = p_batch_id
          and batch_line_id = l_prev_rec.batch_line_id;