The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
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
) ;
end insert_create_enrollment ;
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
) ;
end insert_process_dependent ;
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;
UPDATE hr_pump_batch_headers bh
SET bh.ATOMIC_LINKED_CALLS = 'Y'
WHERE bh.batch_id = p_batch_id ;
UPDATE hrdpv_create_enrollment
SET P_RECORD_TYP_CD = 'ENROLL'
WHERE batch_id = p_batch_id;
buff => 'Updated Header for ATOMIC_LINKED_CALLS');
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
);
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;
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 ;
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
);
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;
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;
UPDATE hr_pump_batch_headers bh
SET bh.ATOMIC_LINKED_CALLS = 'Y'
WHERE bh.batch_id = p_batch_id ;
buff => 'Updated Header for ATOMIC_LINKED_CALLS');
UPDATE hrdpv_process_dependent
SET P_RECORD_TYP_CD = 'ENROLL'
WHERE batch_id = p_batch_id;
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
);
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;
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 ;
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
);
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;
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;
UPDATE hr_pump_batch_headers bh
SET bh.ATOMIC_LINKED_CALLS = 'Y'
WHERE bh.batch_id = p_batch_id ;
buff => 'Updated Header for ATOMIC_LINKED_CALLS');
UPDATE hrdpv_process_beneficiary
SET P_RECORD_TYP_CD = 'ENROLL'
WHERE batch_id = p_batch_id;
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;
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 ;
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;