[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'EMPLOYER_ID')
,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'SI_PROVIDER_ID')
,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'NSI_MONTH')
,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'OUTPUT_MEDIA_TYPE')
,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'PAYROLL_ID')
,PAY_NL_NSI_PROCESS.get_parameter(legislative_parameters,'WITHDRAW_ASG_SET_ID')
,business_group_id
,report_type
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT TERRITORY_SHORT_NAME
FROM FND_TERRITORIES_VL
WHERE TERRITORY_CODE = p_territory_code;
|Description : This procedure returns a sql string to select a range of |
| assignments eligible for archival |
-------------------------------------------------------------------------------*/
Procedure RANGE_CODE (pactid IN NUMBER
,sqlstr OUT NOCOPY VARCHAR2) is
--Fetch Org Address - Sender (BG) /Employer
CURSOR csr_get_address(p_organization_id NUMBER) IS
SELECT hr_org.NAME name
,hr_loc.style style
,hr_loc.loc_information14 House_Num
,hr_loc.loc_information15 House_Num_Add
,hr_loc.region_1 street_name
,pay_nl_general.get_postal_code(hr_loc.postal_code) postal_code
,hr_general.decode_lookup('HR_NL_CITY', hr_loc.town_or_city) city
FROM hr_organization_units hr_org
,hr_locations hr_loc
WHERE hr_org.organization_id = p_organization_id
AND hr_org.location_id = hr_loc.location_id (+);
SELECT
DECODE(hoi.org_information3,'ZFW',1,'ZW',2,'WW',3,'WAO',4,'AMI',5,6) sort_order
,hoi.org_information8 Sender_Rep_Name
,hoi.org_information9 Sender_Reg_Number
,hoi.org_information10 Employer_Rep_Name
,hoi.org_information11 Employer_Reg_Number
FROM hr_organization_information hoi
WHERE hoi.org_information_context='NL_SIP'
AND hoi.organization_id = p_organization_id
AND hoi.org_information4 = p_si_provider_id
AND hoi.org_information3 IN('ZFW','ZW','WW','WAO','AMI')
AND p_nsi_process_date between
FND_DATE.CANONICAL_TO_DATE(hoi.org_information1) and
nvl(FND_DATE.CANONICAL_TO_DATE(hoi.org_information2),hr_general.end_of_time)
ORDER BY ORG_INFORMATION7,DECODE(hoi.org_information3,'ZFW',1,'ZW',2,'WW',3,'WAO',4,'AMI',5,6) ;
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND 1 = 2
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
-- Return cursor that selects no rows
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
-- Also has a Union for Select that selects the Date Track Changes
-- in the Cadans Extra Info Change- Occupation Code
-- If the Run is for Cadans- NL_CAD_NSI_ARCHIVE OR NL_CADZ_NSI_ARCHIVE
CURSOR csr_ee_asg_si_info(lp_report_type varchar2
,lp_person_id number,lp_assignment_id number
,lp_si_provider number
,lp_last_nsi_process_date date,lp_nsi_process_date date) IS
SELECT
paa.person_id ,paa.assignment_id
,paa.organization_id,paa.effective_start_date ,paa.effective_end_date
,FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1) si_eff_start_date
,NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time) si_eff_end_date
,hr_general.start_of_time cad_eff_start_date
,hr_general.end_of_time cad_eff_end_date
,scl_flx.SEGMENT2 Employment_Type
,scl_flx.SEGMENT3 Employment_SubType
,scl_flx.SEGMENT6 Work_Pattern
,paa.assignment_status_type_id
,asg_stat.per_system_status
,null occupation_code
,null other_occupation_name
,null collective_agreement_code
,null insurance_abp
,null risk_fund
FROM
per_all_assignments_f paa
,hr_soft_coding_keyflex scl_flx
,per_assignment_status_types asg_stat
,per_assignment_extra_info ee_si
WHERE paa.person_id = lp_person_id
and paa.assignment_id = lp_assignment_id
and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and paa.assignment_status_type_id = asg_stat.assignment_status_type_id
AND paa.assignment_id = ee_si.assignment_id
AND ee_si.aei_information_category='NL_SII'
AND ee_si.aei_information3 IN('ZFW','ZW','WW','WAO','AMI')
AND paa.effective_start_date
BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1)
AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time)
and paa.effective_start_date BETWEEN lp_last_nsi_process_date AND lp_nsi_process_date
AND (lp_report_type = 'NL_GAK_NSI_ARCHIVE' ) /* Gak Asg Date Track Changes*/
UNION
SELECT
paa.person_id,paa.assignment_id
,paa.organization_id,paa.effective_start_date,paa.effective_end_date
,FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1) si_eff_start_date
,NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time) si_eff_end_date
,hr_general.start_of_time cad_eff_start_date
,hr_general.end_of_time cad_eff_end_date
,scl_flx.SEGMENT2 Employment_Type
,scl_flx.SEGMENT3 Employment_SubType
,scl_flx.SEGMENT6 Work_Pattern
,paa.assignment_status_type_id
,asg_stat.per_system_status
,null occupation_code
,null other_occupation_name
,null collective_agreement_code
,null insurance_abp
,null risk_fund
FROM
per_all_assignments_f paa
,hr_soft_coding_keyflex scl_flx
,per_assignment_status_types asg_stat
,per_assignment_extra_info ee_si
WHERE paa.person_id = lp_person_id
AND paa.assignment_id = lp_assignment_id
AND scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND paa.assignment_status_type_id = asg_stat.assignment_status_type_id
AND paa.assignment_id = ee_si.assignment_id
AND ee_si.aei_information_category='NL_SII'
AND ee_si.aei_information3 IN('ZFW','ZW','WW','WAO','AMI')
AND lp_nsi_process_date BETWEEN paa.effective_start_date AND paa.effective_end_date
AND FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1)
BETWEEN lp_last_nsi_process_date AND lp_nsi_process_date /* NL_SII-Code Insurance EIT Date Track Changes*/
UNION
SELECT
paa.person_id,paa.assignment_id
,paa.organization_id,paa.effective_start_date,paa.effective_end_date
,FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1) si_eff_start_date
,NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time) si_eff_end_date
,FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1) cad_eff_start_date
,NVL(FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information2),hr_general.end_of_time) cad_eff_end_date
,scl_flx.SEGMENT2 Employment_Type
,scl_flx.SEGMENT3 Employment_SubType
,scl_flx.SEGMENT6 Work_Pattern
,paa.assignment_status_type_id
,asg_stat.per_system_status
,ee_cadans.aei_information3 occupation_code
,ee_cadans.aei_information4 other_occupation_name
,ee_cadans.aei_information5 collective_agreement_code
,ee_cadans.aei_information6 insurance_abp
,ee_cadans.aei_information7 risk_fund
FROM
per_all_assignments_f paa
,hr_soft_coding_keyflex scl_flx
,per_assignment_status_types asg_stat
,per_assignment_extra_info ee_cadans
,per_assignment_extra_info ee_si
WHERE paa.person_id = lp_person_id
and paa.assignment_id = lp_assignment_id
and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and paa.assignment_status_type_id = asg_stat.assignment_status_type_id
AND paa.assignment_id = ee_si.assignment_id
AND ee_si.aei_information_category='NL_SII'
AND ee_si.aei_information3 IN('ZFW','ZW','WW','WAO','AMI')
AND paa.effective_start_date
BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1)
AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time)
AND paa.assignment_id = ee_cadans.assignment_id(+)
AND ee_cadans.aei_information_category='NL_CADANS_INFO'
and paa.effective_start_date BETWEEN lp_last_nsi_process_date AND lp_nsi_process_date
AND paa.effective_start_date
BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1) AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information2),hr_general.end_of_time)
AND (lp_report_type = 'NL_CAD_NSI_ARCHIVE' OR lp_report_type='NL_CADZ_NSI_ARCHIVE' ) /* Cadans Asg Date Track Changes*/
UNION
SELECT
paa.person_id,paa.assignment_id
,paa.organization_id,paa.effective_start_date,paa.effective_end_date
,FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1) si_eff_start_date
,NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time) si_eff_end_date
,FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1) cad_eff_start_date
,NVL(FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information2),hr_general.end_of_time) cad_eff_end_date
,scl_flx.SEGMENT2 Employment_Type
,scl_flx.SEGMENT3 Employment_SubType
,scl_flx.SEGMENT6 Work_Pattern
,paa.assignment_status_type_id
,asg_stat.per_system_status
,ee_cadans.aei_information3 occupation_code
,ee_cadans.aei_information4 other_occupation_name
,ee_cadans.aei_information5 collective_agreement_code
,ee_cadans.aei_information6 insurance_abp
,ee_cadans.aei_information7 risk_fund
FROM
per_all_assignments_f paa
,hr_soft_coding_keyflex scl_flx
,per_assignment_status_types asg_stat
,per_assignment_extra_info ee_cadans
,per_assignment_extra_info ee_si
WHERE paa.person_id = lp_person_id
AND paa.assignment_id = lp_assignment_id
AND scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND (lp_report_type = 'NL_CAD_NSI_ARCHIVE' OR lp_report_type='NL_CADZ_NSI_ARCHIVE' )
AND paa.assignment_status_type_id = asg_stat.assignment_status_type_id
AND paa.assignment_id = ee_si.assignment_id
AND ee_si.aei_information_category='NL_SII'
AND ee_si.aei_information3 IN('ZFW','ZW','WW','WAO','AMI')
AND paa.effective_start_date
BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information1)
AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_si.aei_information2),hr_general.end_of_time)
AND FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1)
BETWEEN paa.effective_start_date AND paa.effective_end_date
AND paa.assignment_id = ee_cadans.assignment_id
AND ee_cadans.aei_information_category='NL_CADANS_INFO'
AND FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1)
BETWEEN lp_last_nsi_process_date AND lp_nsi_process_date /* Cadans EIT Date Track Changes*/
order by 1,2,4,5;
--Select the Most recently sent NSI Record for comparison
--to determine if a NSI Record needs to be generated or not.
CURSOR csr_ee_nsi_record (lp_person_id number
,lp_assignment_id number
,lp_employer_id number
,lp_si_provider number
,lp_nsi_notify_date date
,lp_lst_nsi_process_date date) IS
SELECT
nl_ee_nsi.action_information1 Employer_ID
,nl_ee_nsi.action_information2 Person_ID
,nl_ee_nsi.action_information3 Assignment_ID
,nl_ee_nsi.action_information4 SI_Provider_ID
,nl_ee_nsi.action_information5 Hire_Date
,nl_ee_nsi.action_information6 Actual_Termination_Date
,nl_ee_nsi.action_information7 Assignment_Start_Date
,nl_ee_nsi.action_information8 Assignment_End_Date
,nl_ee_nsi.action_information9 Notification_a
,nl_ee_nsi.action_information10 Notification_a_date
,nl_ee_nsi.action_information11 Notification_b
,nl_ee_nsi.action_information12 Notification_b_date
,nl_ee_nsi.action_information13 Code_Insurance
,nl_ee_nsi.action_information14 Code_Insurance_Basis
,nl_ee_nsi.action_information15 Code_Occupation
,nl_ee_nsi.action_information16 Work_Pattern
,nl_ee_nsi.action_information17 St_Date_Lab_Rel
FROM PAY_ACTION_INFORMATION nl_ee_nsi
where nl_ee_nsi.action_context_type='AAP'
and nl_ee_nsi.action_information_category = 'NL NSI EMPLOYEE DETAILS'
and nl_ee_nsi.action_information1 = lp_employer_id
and nl_ee_nsi.action_information2 = lp_person_id
and nl_ee_nsi.action_information3 = lp_assignment_id
and nl_ee_nsi.action_information4 = lp_si_provider
and (nl_ee_nsi.action_information10 = TO_CHAR(lp_nsi_notify_date,'DDMMYYYY')
OR nl_ee_nsi.action_information12 = TO_CHAR(lp_nsi_notify_date,'DDMMYYYY'))
and nl_ee_nsi.effective_date <= lp_lst_nsi_process_date
and nl_ee_nsi.action_information9 <>'4'
ORDER BY nl_ee_nsi.effective_date DESC;
--Select the Last NSI Record Notify Dates for comparison
--to determine if a NSI Record needs to be generated or not
--in the Current Run.
CURSOR csr_ee_lat_nsi (lp_employer_id NUMBER
,lp_si_provider_id NUMBER
,lp_person_id NUMBER
,lp_assignment_id NUMBER
,lp_nsi_process_date DATE) IS
SELECT
min(TO_DATE(nl_ee_nsi1.action_information10,'DDMMYYYY')) notify_a_date,
min(TO_DATE(nl_ee_nsi1.action_information12,'DDMMYYYY')) notify_b_date
FROM PAY_ACTION_INFORMATION nl_ee_nsi1
where nl_ee_nsi1.action_context_type='AAP'
and nl_ee_nsi1.action_information_category = 'NL NSI EMPLOYEE DETAILS'
and nl_ee_nsi1.action_information1 = lp_employer_id
and nl_ee_nsi1.action_information2 = lp_person_id
and nl_ee_nsi1.action_information3 = lp_assignment_id
and nl_ee_nsi1.effective_date = lp_nsi_process_date
and nl_ee_nsi1.action_information9 <>'4';
--Select EE Assignment Effective Dates
CURSOR csr_ee_asg_dates (lp_assignment_id NUMBER) IS
SELECT TO_CHAR(min(asg.effective_start_date),'DDMMYYYY') asg_start_date
,TO_CHAR(max(asg.effective_end_date),'DDMMYYYY') asg_end_date
from per_all_assignments_f asg,
per_assignment_status_types past
where asg.assignment_id = lp_assignment_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and asg.assignment_status_type_id = past.assignment_status_type_id;
--Select EE Termination Details
CURSOR csr_ee_term (lp_person_id NUMBER) IS
SELECT leaving_reason,Actual_Termination_Date
from per_periods_of_service pos
where pos.person_id = lp_person_id;
--Select EE Assignment Status
CURSOR csr_ee_asg_status (lp_assignment_id NUMBER,l_effective_date date) IS
SELECT asg.effective_start_date,asg.effective_end_date,past.per_system_status
from per_all_assignments_f asg,
per_assignment_status_types past
where asg.assignment_id = lp_assignment_id
and asg.assignment_status_type_id = past.assignment_status_type_id
and l_effective_date BETWEEN asg.effective_start_date and asg.effective_end_date;
--Select EE SI Info - NL_SII
CURSOR csr_ee_siinfo (lp_assignment_id NUMBER
,lp_organization_id NUMBER
,lp_effective_date DATE) IS
SELECT
PAY_NL_SI_PKG.Get_Si_Status(lp_assignment_id,lp_effective_date,'ZW') zw_si_status
,PAY_NL_SI_PKG.Get_Si_Status(lp_assignment_id,lp_effective_date,'WW') ww_si_status
,PAY_NL_SI_PKG.Get_Si_Status(lp_assignment_id,lp_effective_date,'WAO') wao_si_status
,PAY_NL_SI_PKG.Get_Si_Status(lp_assignment_id,lp_effective_date,'ZFW') zfw_si_status
FROM dual ;
SELECT
DECODE(lp_zw_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'ZW',lp_assignment_id)) zw_provider
,DECODE(lp_ww_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'WEWE',lp_assignment_id)) ww_provider
,DECODE(lp_wao_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'WAOD',lp_assignment_id)) wao_provider
,DECODE(lp_zfw_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'ZFW',lp_assignment_id)) zfw_provider
,DECODE(lp_zw_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'ZW',lp_assignment_id)) zw_er_org_id
,DECODE(lp_ww_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'WEWE',lp_assignment_id)) ww_er_org_id
,DECODE(lp_wao_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'WAOD',lp_assignment_id)) wao_er_org_id
,DECODE(lp_zfw_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'ZFW',lp_assignment_id)) zfw_er_org_id
FROM DUAL;
--Select EE Info : Name/SOFI Number
CURSOR csr_ee_info (lp_person_id NUMBER
,lp_assignment_id NUMBER
,lp_effective_date DATE) IS
SELECT
ee_info.Full_Name
,replace(replace(ee_info.Last_Name,'.',''),',','') Last_Name
,replace(replace(ee_info.Previous_Last_Name,'.',''),',','') Previous_Last_Name
,replace(replace(ee_info.First_Name,'.',''),',','') First_Name
,replace(replace(replace(ee_info.per_information1,'.',''),',',''),' ','') Initials
,replace(replace(replace(ee_info.pre_name_adjunct,'.',''),',',''),' ','') Prefix
,ee_info.National_Identifier SOFI_Number
,ee_info.sex
,ee_info.Marital_Status
,ee_info.Date_Of_Birth
,ee_info.Employee_Number
,paa.Assignment_Number
FROM per_all_people_f ee_info
,per_all_assignments_f paa
WHERE ee_info.person_id = lp_person_id
AND ee_info.person_id = paa.person_id
AND paa.assignment_id = lp_assignment_id
AND lp_effective_date BETWEEN ee_info.Effective_Start_Date and ee_info.Effective_End_Date
AND lp_effective_date BETWEEN paa.Effective_Start_Date and paa.Effective_End_Date;
--Select EE Spouse Info : Name
CURSOR csr_ee_sp_info (lp_person_id NUMBER
,lp_effective_date DATE) IS
SELECT
ee_info.Last_Name
,ee_info.Previous_Last_Name
,replace(replace(replace(ee_info.pre_name_adjunct,'.',''),',',''),' ','') Prefix
FROM per_all_people_f ee_info,
per_contact_relationships con
WHERE ee_info.person_id = con.contact_person_id
AND con.person_id= lp_person_id
AND con.contact_type='S'
AND lp_effective_date BETWEEN ee_info.Effective_Start_Date and ee_info.Effective_End_Date;
--Select EE Address
CURSOR csr_ee_addr (lp_person_id NUMBER
,lp_address_type VARCHAR2
,lp_effective_date DATE) IS
SELECT ee_addr.style style
,ee_addr.add_information13 House_Num
,ee_addr.add_information14 House_Num_Add
,ee_addr.region_1 street_name
,pay_nl_general.get_postal_code(ee_addr.postal_code) postal_code
,hr_general.decode_lookup('HR_NL_CITY', ee_addr.town_or_city) city
,ee_addr.country country
FROM per_addresses ee_addr
WHERE ee_addr.person_id = lp_person_id
AND lp_effective_date between date_from and NVL(date_to,hr_general.end_of_time)
AND ((ee_addr.primary_flag ='Y' AND lp_address_type IS NULL)
OR (lp_address_type IS NOT NULL AND ee_addr.address_type = lp_address_type));
--Select EE Gak Info
CURSOR csr_ee_gak (lp_person_id NUMBER
,lp_assignment_id NUMBER
,lp_process_date DATE) IS
SELECT
hr_general.decode_lookup('NL_GAK_OCCUPATION_DESCRIPTION',ee_gak.aei_information3) Occupation_Desc
,ee_gak.aei_information4 Weekly_4_Exp_SI_Days
FROM per_assignment_extra_info ee_gak
WHERE ee_gak.assignment_id = lp_assignment_id
AND ee_gak.aei_information_category='NL_GAK_INFO'
AND lp_process_date BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_gak.aei_information1)
AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_gak.aei_information2),hr_general.END_OF_TIME) ;
--Select EE Cadans Info
CURSOR csr_ee_cadans (lp_person_id NUMBER
,lp_assignment_id NUMBER
,lp_process_date DATE) IS
SELECT
ee_cadans.aei_information3 occupation_code
,ee_cadans.aei_information4 other_occupation_name
,ee_cadans.aei_information5 collective_agreement_code
,ee_cadans.aei_information6 insurance_abp
,ee_cadans.aei_information7 risk_fund
FROM per_assignment_extra_info ee_cadans
WHERE ee_cadans.assignment_id = lp_assignment_id
AND ee_cadans.aei_information_category='NL_CADANS_INFO'
AND lp_process_date BETWEEN FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information1)
AND NVL(FND_DATE.CANONICAL_TO_DATE(ee_cadans.aei_information2),hr_general.END_OF_TIME) ;
SELECT LEAST(NVL(v_csr_ee_lat_nsi.notify_a_date,v_csr_ee_lat_nsi.notify_b_date),
NVL(v_csr_ee_lat_nsi.notify_b_date,v_csr_ee_lat_nsi.notify_a_date))
INTO l_last_nsi_min_notify_date FROM DUAL;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_asg_act_id
FROM dual;
--Select the Most recently sent NSI Record for comparison
--to determine if a NSI Record needs to be generated or not.
CURSOR csr_ee_nsi_record (lp_person_id number
,lp_assignment_id number
,lp_employer_id number
,lp_si_provider number
,lp_nsi_notify_date date) IS
SELECT
nl_ee_nsi.action_information1 Employer_ID
,nl_ee_nsi.action_information2 Person_ID
,nl_ee_nsi.action_information3 Assignment_ID
,nl_ee_nsi.action_information4 SI_Provider_ID
,nl_ee_nsi.action_information5 Hire_Date
,nl_ee_nsi.action_information6 Actual_Termination_Date
,nl_ee_nsi.action_information7 Assignment_Start_Date
,nl_ee_nsi.action_information8 Assignment_End_Date
,nl_ee_nsi.action_information9 Notification_a
,nl_ee_nsi.action_information10 Notification_a_date
,nl_ee_nsi.action_information11 Notification_b
,nl_ee_nsi.action_information12 Notification_b_date
,nl_ee_nsi.action_information13 Code_Insurance
,nl_ee_nsi.action_information14 Code_Insurance_Basis
,nl_ee_nsi.action_information15 Code_Occupation
,nl_ee_nsi.action_information16 Work_Pattern
,nl_ee_nsi.action_information17 St_Date_Lab_Rel
,nl_ee_nsi.action_information18 Sofi_Number
,nl_ee_nsi.action_information19 Employee_Name
,nl_ee_nsi.action_information20 Employee_Primary_Address
,nl_ee_nsi.action_information21 Employee_Pop_Reg_Add
,nl_ee_nsi.action_information22 Gak_Rep_Info
,nl_ee_nsi.action_information23 Cadans_Rep_Info
,nl_ee_nsi.action_information24 Employee_Details
FROM PAY_ACTION_INFORMATION nl_ee_nsi
where nl_ee_nsi.action_context_type='AAP'
and nl_ee_nsi.action_information_category = 'NL NSI EMPLOYEE DETAILS'
and nl_ee_nsi.action_information1 = lp_employer_id
and nl_ee_nsi.action_information2 = lp_person_id
and nl_ee_nsi.action_information3 = lp_assignment_id
and nl_ee_nsi.action_information4 = lp_si_provider
and nl_ee_nsi.effective_date = lp_nsi_notify_date
and nl_ee_nsi.action_information9 <>'4'
ORDER BY nl_ee_nsi.effective_date DESC;
SELECT
max(effective_date) prev_nsi_process_date
FROM PAY_ACTION_INFORMATION nl_ee_nsi1
where nl_ee_nsi1.action_context_type='AAP'
and nl_ee_nsi1.action_information_category = 'NL NSI EMPLOYEE DETAILS'
and nl_ee_nsi1.action_information1 = lp_employer_id
and nl_ee_nsi1.action_information2 = lp_person_id
and nl_ee_nsi1.action_information3 = lp_assignment_id
and nl_ee_nsi1.action_information4 = lp_si_provider_id
and nl_ee_nsi1.action_information9 <>'4'
and nl_ee_nsi1.effective_date < lp_nsi_process_date;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_asg_act_id
FROM dual;
select
posv.org_structure_version_id
from
per_organization_structures pos,
per_org_structure_versions posv
where pos.organization_structure_id = posv.organization_structure_id
and to_char(pos.organization_structure_id) IN (select org_information1
from hr_organization_information hoi where hoi.org_information_context='NL_BG_INFO'
and hoi.organization_id=lp_business_group_id)
and lp_process_date between posv.date_from and nvl(posv.date_to,hr_general.End_of_time);
SELECT
paa.person_id,paa.assignment_id
,paa.organization_id,paa.effective_start_date,paa.effective_end_date
,PAY_NL_SI_PKG.Get_Si_Status(paa.assignment_id,lp_nsi_process_date,'ZW') zw_si_status
,PAY_NL_SI_PKG.Get_Si_Status(paa.assignment_id,lp_nsi_process_date,'WW') ww_si_status
,PAY_NL_SI_PKG.Get_Si_Status(paa.assignment_id,lp_nsi_process_date,'WAO') wao_si_status
,PAY_NL_SI_PKG.Get_Si_Status(paa.assignment_id,lp_nsi_process_date,'ZFW') zfw_si_status
,scl_flx.SEGMENT2 Employment_Type
,scl_flx.SEGMENT3 Employment_SubType
,scl_flx.SEGMENT6 Work_Pattern
FROM
per_all_assignments_f paa
,per_all_people_f pap
,hr_soft_coding_keyflex scl_flx
WHERE paa.business_group_id = lp_business_group_id
and paa.person_id
BETWEEN lp_start_person_id AND lp_end_person_id
and (paa.payroll_id = lp_payroll_id OR lp_payroll_id IS NULL)
and paa.person_id =pap.person_id
and paa.organization_id IN (
SELECT pose.organization_id_child FROM
per_org_structure_elements pose
CONNECT BY pose.organization_id_parent = prior pose.organization_id_child
AND pose.org_structure_version_id =lp_structure_version_id
START WITH pose.organization_id_parent=lp_employer_id
AND pose.org_structure_version_id =lp_structure_version_id
UNION
SELECT lp_employer_id FROM DUAL
)
and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and ((lp_nsi_process_date >= paa.effective_start_date AND lp_nsi_process_date <= paa.effective_end_date)
or (lp_nsi_process_date >= pap.effective_start_date AND lp_nsi_process_date <= pap.effective_end_date))
order by paa.person_id,paa.assignment_id,paa.effective_start_date,paa.effective_end_date;
SELECT
DECODE(lp_zw_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'ZW',lp_assignment_id)) zw_provider
,DECODE(lp_ww_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'WEWE',lp_assignment_id)) ww_provider
,DECODE(lp_wao_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'WAOD',lp_assignment_id)) wao_provider
,DECODE(lp_zfw_si_status,null,null,HR_NL_ORG_INFO.Get_SI_Provider_Info(lp_organization_id,'ZFW',lp_assignment_id)) zfw_provider
,DECODE(lp_zw_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'ZW',lp_assignment_id)) zw_er_org_id
,DECODE(lp_ww_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'WEWE',lp_assignment_id)) ww_er_org_id
,DECODE(lp_wao_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'WAOD',lp_assignment_id)) wao_er_org_id
,DECODE(lp_zfw_si_status,null,null,HR_NL_ORG_INFO.Get_ER_SI_Prov_HR_Org_ID(lp_organization_id,'ZFW',lp_assignment_id)) zfw_er_org_id
FROM DUAL;
--Select the Last NSI Record Proces Date for comparison
--to determine if a NSI Record needs to be generated or not
--in the Current Run.
CURSOR csr_ee_lat_nsi (lp_employer_id NUMBER
,lp_si_provider_id NUMBER
,lp_person_id number
,lp_assignment_id number
,lp_nsi_process_date date) IS
SELECT
max(effective_date) nsi_process_date
FROM PAY_ACTION_INFORMATION nl_ee_nsi1
where nl_ee_nsi1.action_context_type='AAP'
and nl_ee_nsi1.action_information_category = 'NL NSI EMPLOYEE DETAILS'
and nl_ee_nsi1.action_information1 = lp_employer_id
and nl_ee_nsi1.action_information2 = lp_person_id
and nl_ee_nsi1.action_information3 = lp_assignment_id
and nl_ee_nsi1.action_information4 = lp_si_provider_id
and nl_ee_nsi1.action_information9 <>'4'
and nl_ee_nsi1.effective_date <= lp_nsi_process_date;
SELECT
asg_set.assignment_id
FROM hr_assignment_set_amendments asg_set
WHERE asg_set.assignment_set_id=lp_withdraw_asg_set_id
AND asg_set.assignment_id=lp_assignment_id;