[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_update_action_if_exists VARCHAR2 (1);
| procedures for inserting data into pay_batch_headers |
| and pay_batch_lines table. |
----------------------------------------------------------------------*/
PROCEDURE iza_upload( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_file_name IN VARCHAR2,
p_batch_name IN VARCHAR2,
p_effective_date IN VARCHAR2,
p_business_group_id IN per_business_groups.business_group_id%TYPE,
p_action_if_exists IN VARCHAR2 DEFAULT NULL,
p_dummy_action_if_exists IN VARCHAR2 DEFAULT NULL,
p_date_effective_changes IN VARCHAR2 DEFAULT NULL
) IS
-- Procedure name
l_proc VARCHAR2 (72) ;
select ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3
from HR_ORGANIZATION_INFORMATION
where ORG_INFORMATION_CONTEXT='NL_IZA_REPO_INFO'
and organization_id = v_org_id;
select hoi.organization_id
from HR_ORGANIZATION_INFORMATION hoi, HR_ORGANIZATION_UNITS hou
where ORG_INFORMATION_CONTEXT='NL_IZA_REPO_INFO'
and lpad(ORG_INFORMATION1,3,'0') = v_client_number
and lpad(ORG_INFORMATION2,3,'0') = v_sub_emplr_number
and hou.ORGANIZATION_ID = hoi.organization_id
and hou.business_group_id = v_bg_id;
SELECT
paa.organization_id org_id,
ltrim(substr(pap.employee_number,1,9),'0') employee_number
,pap.PER_INFORMATION1 initials
,pap.PRE_NAME_ADJUNCT prefix
,pap.LAST_NAME last_name
,pap.date_of_birth
,pap.per_information15
FROM
per_all_people_f pap
,per_all_assignments_f paa
WHERE pap.business_group_id = v_business_group_id
and pap.person_id = v_person_id
and pap.person_id = paa.person_id
and v_period_end_date between pap.effective_start_date and pap.effective_end_date
and paa.effective_start_date =
(
SELECT MIN(asg.effective_start_date)
FROM per_assignment_status_types past, per_all_assignments_f asg
WHERE asg.assignment_id = paa.assignment_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and asg.assignment_status_type_id = past.assignment_status_type_id
and asg.effective_start_date <= v_period_End_Date
and asg.effective_end_date >= v_period_Start_Date
);
SELECT
pap.person_id person_id
FROM
per_all_people_f pap
,per_all_assignments_f paa
,PER_ASSIGNMENT_EXTRA_INFO pae_iza
,PER_ASSIGNMENT_EXTRA_INFO pae_sii
WHERE pap.business_group_id = v_business_group_id
and pap.person_id = paa.person_id
and paa.primary_flag='Y'
and v_period_end_date between pap.effective_start_date and pap.effective_end_date
and paa.effective_start_date =
(
SELECT MIN(asg.effective_start_date)
FROM per_assignment_status_types past, per_all_assignments_f asg
WHERE asg.assignment_id = paa.assignment_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and asg.assignment_status_type_id = past.assignment_status_type_id
and asg.effective_start_date <= v_period_End_Date
and asg.effective_end_date >= v_period_Start_Date
)
and paa.organization_id in (select distinct piza.organization_id iza_org_id
from pay_nl_iza_upld_status piza
where piza.process_year_month = v_period_End_Date
AND lpad(piza.employer_number,3,'0') = l_rec_client_num
AND piza.business_group_id = p_business_group_id
UNION
(
SELECT iza_org_id from hr_organization_information e,(
SELECT distinct pose.organization_id_child iza_org_id
FROM per_org_structure_elements pose
where pose.org_structure_version_id = v_org_struct_version_id
START WITH pose.organization_id_parent in (select distinct piza.organization_id
from pay_nl_iza_upld_status piza
where piza.process_year_month = v_period_End_Date
AND lpad(piza.employer_number,3,'0') = l_rec_client_num
AND piza.business_group_id = p_business_group_id
)
CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent)
MINUS
SELECT iza_org_id from hr_organization_information e1,(
SELECT distinct pose.organization_id_child iza_org_id
FROM per_org_structure_elements pose
where pose.org_structure_version_id = v_org_struct_version_id
START WITH pose.organization_id_parent in (select distinct piza.organization_id
from pay_nl_iza_upld_status piza
where piza.process_year_month = v_period_End_Date
AND lpad(piza.employer_number,3,'0') = l_rec_client_num
AND piza.business_group_id = p_business_group_id
)
CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent)
where
e1.organization_id=iza_org_id and
e1.org_information_context = 'NL_IZA_REPO_INFO'
AND e1.org_information1 IS NOT NULL
AND e1.org_information2 IS NOT NULL
)
)
and paa.assignment_id = pae_iza.assignment_id
and pae_iza.AEI_INFORMATION_CATEGORY = 'NL_IZA_INFO'
and v_period_end_date >= fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION1)
and v_period_start_date <= NVL(fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION2),v_period_start_date)
and pae_sii.AEI_INFORMATION_CATEGORY = 'NL_SII'
and pae_sii.AEI_INFORMATION3 in ('ZFW','AMI')
and pae_sii.AEI_INFORMATION4 = '4'
and v_period_end_date >= fnd_date.canonical_to_date(pae_sii.AEI_INFORMATION1)
and v_period_start_date <= NVL(fnd_date.canonical_to_date(pae_sii.AEI_INFORMATION2),v_period_start_date)
and paa.assignment_id = pae_iza.assignment_id
and pae_iza.assignment_id = pae_sii.assignment_id
minus
SELECT
piza1.person_id
FROM pay_nl_iza_upld_status piza1
WHERE piza1.process_year_month = v_period_End_Date
AND lpad(piza1.employer_number,3,'0') = l_rec_client_num
AND piza1.business_group_id = p_business_group_id;
SELECT sv.org_structure_version_id
FROM per_org_structure_versions sv
WHERE sv.organization_structure_id in
(
SELECT TO_NUMBER(inf.org_information1) organization_structure_id
FROM hr_organization_information inf
WHERE inf.organization_id = v_bg_id
AND inf.org_information_context = 'NL_BG_INFO'
AND inf.org_information1 IS NOT NULL
)
AND v_period_end_date BETWEEN sv.date_from
AND NVL(sv.date_to, Hr_general.End_Of_time);
insert into PAY_NL_IZA_UPLD_STATUS( BUSINESS_GROUP_ID
,ORGANIZATION_ID
,EMPLOYER_NUMBER
,SUB_EMPLOYER_NUMBER
,PAYROLL_CENTER
,PROCESS_YEAR_MONTH
,PERSON_ID
,EMPLOYEE_NUMBER
,PROCESS_STATUS
,PROVINCE_CODE
,DATE_OF_BIRTH
,PARTICIPANT_NUMBER
,EMPLOYEE_NAME
,CONTRIBUTION_1
,CORRECTION_CONTRIBUTION_1
,DATE_CORRECTION_1
,CONTRIBUTION_2
,CORRECTION_CONTRIBUTION_2
,DATE_CORRECTION_2
,REJECT_REASON)
values (p_business_group_id
,l_rec_org_id
,l_client_num
,l_sub_emplr_num
,NULL
,l_period_eff_end_date
,csr_missing_employees_rec.person_id
,l_employee_number
,l_process_status
,l_province_code
,l_date_of_birth
,l_participant_number
,l_last_name || ' ' || l_initials || ' ' || l_prefix
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL);
c_update_action_if_exists := 'U'; --Update existing element entry;
c_default_dt_effective_changes := 'C'; --Update/Change Insert;
IF (p_action_if_exists = c_update_action_if_exists)
THEN
IF (p_date_effective_changes IS NULL)
THEN -- Default p_date_effective_changes
l_date_effective_changes := c_default_dt_effective_changes;
select pap.effective_start_date - 1
from per_all_people_f pap
where PER_INFORMATION_CATEGORY='NL'
and pap.business_group_id = v_bg_id
and pap.person_id = v_person_id
and pap.current_employee_flag is null
and v_period_end_date >= pap.effective_start_date
and v_period_start_date <= pap.effective_end_date
and v_period_end_date between pap.effective_start_date and pap.effective_end_date;
insert into PAY_NL_IZA_UPLD_STATUS( BUSINESS_GROUP_ID
,ORGANIZATION_ID
,EMPLOYER_NUMBER
,SUB_EMPLOYER_NUMBER
,PAYROLL_CENTER
,PROCESS_YEAR_MONTH
,PERSON_ID
,EMPLOYEE_NUMBER
,PROCESS_STATUS
,PROVINCE_CODE
,DATE_OF_BIRTH
,PARTICIPANT_NUMBER
,EMPLOYEE_NAME
,CONTRIBUTION_1
,CORRECTION_CONTRIBUTION_1
,DATE_CORRECTION_1
,CONTRIBUTION_2
,CORRECTION_CONTRIBUTION_2
,DATE_CORRECTION_2
,REJECT_REASON)
select p_bg_id
,p_org_id
,decode(l_client_num,'000','0',ltrim(l_client_num,'0'))
,decode(l_sub_emplr_num,'000','0',ltrim(l_sub_emplr_num,'0'))
,p_payroll_center
,l_record_eff_end_date
,l_person_id
,l_employee_number
,l_process_status
,l_province_code
,l_date_of_birth
,l_participant_number
,l_employee_name
,l_contribution1
,l_corr_cont1
,l_iza_corr_end_date
,l_contribution2
,l_corr_cont2
,l_iza_corr2_end_date
,NULL
from dual;
select decode(l_reject_reason_code1,'00',decode(l_reject_reason_code2,'00',l_reject_reason_code3,l_reject_reason_code2),l_reject_reason_code1) into l_reject_code from dual;
insert into PAY_NL_IZA_UPLD_STATUS( BUSINESS_GROUP_ID
,ORGANIZATION_ID
,EMPLOYER_NUMBER
,SUB_EMPLOYER_NUMBER
,PAYROLL_CENTER
,PROCESS_YEAR_MONTH
,PERSON_ID
,EMPLOYEE_NUMBER
,PROCESS_STATUS
,PROVINCE_CODE
,DATE_OF_BIRTH
,PARTICIPANT_NUMBER
,EMPLOYEE_NAME
,CONTRIBUTION_1
,CORRECTION_CONTRIBUTION_1
,DATE_CORRECTION_1
,CONTRIBUTION_2
,CORRECTION_CONTRIBUTION_2
,DATE_CORRECTION_2
,REJECT_REASON)
select p_bg_id
,p_org_id
,decode(l_client_num,'000','0',ltrim(l_client_num,'0'))
,decode(l_sub_emplr_num,'000','0',ltrim(l_sub_emplr_num,'0'))
,p_payroll_center
,l_record_eff_end_date
,l_person_id
,l_employee_number
,l_process_status
,l_province_code
,l_date_of_birth
,l_participant_number
,l_employee_name
,l_contribution1
,l_corr_cont1
,l_iza_corr_end_date
,l_contribution2
,l_corr_cont2
,l_iza_corr2_end_date
,l_reject_code
from dual;
select sum((NVL(paa.date_end,paa.date_start) - paa.date_start)+1)
from PER_ABSENCE_ATTENDANCES paa
where paa.business_group_id = v_bg_id
and paa.person_id = v_person_id
and decode(paa.ABS_INFORMATION_CATEGORY,'NL_S',ABS_INFORMATION2,ABS_INFORMATION1) = 'Y'
and v_period_end_date >= paa.date_start
and v_period_start_date <= NVL(paa.date_end,v_period_start_date);
select pap.per_information16
from per_all_people_f pap
where PER_INFORMATION_CATEGORY='NL'
and pap.business_group_id = v_bg_id
and pap.person_id = v_person_id
and pap.per_information16 = 'Y'
and v_period_end_date >= pap.effective_start_date
and v_period_start_date <= NVL(pap.effective_end_date,v_period_start_date);
SELECT pap.person_id,paa.assignment_id,paa.assignment_number
FROM per_all_people_f pap
,per_all_assignments_f paa
WHERE ltrim(substr(pap.employee_number,1,9),'0') = v_employee_num
AND paa.person_id = pap.person_id
AND pap.business_group_id = v_bg_id
AND v_period_end_date >= pap.effective_start_date
AND v_period_start_date <= pap.effective_end_date;
select pap.current_employee_flag
from per_all_people_f pap
where PER_INFORMATION_CATEGORY='NL'
and pap.business_group_id = v_bg_id
and pap.person_id = v_person_id
and pap.current_employee_flag = 'Y'
and v_period_end_date >= pap.effective_start_date
and v_period_start_date <= pap.effective_end_date;
select pae_iza.AEI_INFORMATION3
from PER_ASSIGNMENT_EXTRA_INFO pae_iza
,PER_ASSIGNMENT_EXTRA_INFO pae_sii
where pae_iza.AEI_INFORMATION_CATEGORY = 'NL_IZA_INFO'
and pae_iza.assignment_id = v_assignment_id
and v_period_end_date >= fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION1)
and v_period_start_date <= NVL(fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION2),v_period_start_date)
and pae_sii.AEI_INFORMATION_CATEGORY = 'NL_SII'
and pae_sii.AEI_INFORMATION3 in ('ZFW','AMI')
and pae_sii.AEI_INFORMATION4 = '4'
and v_period_end_date >= fnd_date.canonical_to_date(pae_sii.AEI_INFORMATION1)
and v_period_start_date <= NVL(fnd_date.canonical_to_date(pae_sii.AEI_INFORMATION2),v_period_start_date)
and pae_sii.assignment_id = pae_iza.assignment_id;
select paa.assignment_id, paa.assignment_number
from per_all_assignments_f paa
where paa.person_id = v_person_id
and paa.primary_flag = 'Y'
and paa.business_group_id = v_bg_id
and hr_nl_org_info.Get_iza_Org_Id(p_org_struct_version_id,paa.organization_id) = v_org_id
and paa.effective_start_date =
(
SELECT MIN(asg.effective_start_date)
FROM per_assignment_status_types past, per_all_assignments_f asg
WHERE asg.assignment_id = paa.assignment_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and asg.assignment_status_type_id = past.assignment_status_type_id
and asg.effective_start_date <= v_period_end_date
and asg.effective_end_date >= v_period_start_date
);
DELETE from PAY_NL_IZA_UPLD_STATUS pizas
WHERE pizas.process_year_month between l_period_start_date and l_period_end_date
AND pizas.organization_id = p_employer_id
AND pizas.business_group_id = p_business_group_id;