[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT get_parameters(c_payroll_action_id, 'Legal_Employer')
,get_parameters(c_payroll_action_id, 'Payroll_Type')
,get_parameters(c_payroll_action_id, 'Sequence_Number')
,start_date
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = c_payroll_action_id;
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token_name)+(LENGTH(p_token_name)+1),
INSTR(legislative_parameters,' ',INSTR(legislative_parameters,p_token_name))
-(INSTR(legislative_parameters,p_token_name)+(LENGTH(p_token_name)+1)))
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
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 1 '||
'/* ERROR - Employer Details Fetch failed with: '||
sqlerrm(sqlcode)||' */ '||
'from dual where to_char(:payroll_action_id) = dummy';
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name = c_balance_name
AND pbt.legislation_code = 'NL'
AND pbd.database_item_suffix = c_database_item_suffix
AND pbd.legislation_code = 'NL'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
g_nom_bal_def_table.delete;
SELECT ffc.context_id
FROM ff_contexts ffc
WHERE ffc.context_name = p_context_name;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT /*+ ORDERED */ 1
FROM pay_assignment_actions paa
,pay_action_information pai
WHERE paa.payroll_action_id = c_pay_act_id
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_WR_NOMINATIVE_REPORT_ADD'
AND pai.action_information11 = 'Y');
g_col_bal_def_table.delete;
SELECT fnd_date.canonical_to_date(org_information1) start_date
,fnd_date.canonical_to_date(org_information2) end_date
,org_information3
,fnd_number.canonical_to_number(org_information4) amount
,org_information5 frequency
,org_information6
FROM hr_organization_information
WHERE organization_id = c_org_id
AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
-- AND org_information3 = '1' -- Bug# 5754707
AND org_information6 = c_payroll_type
ORDER BY fnd_date.canonical_to_date(org_information1);
SELECT number_per_fiscal_year
FROM per_time_period_types
WHERE period_type = DECODE(c_payroll_type,'MONTH','Calendar Month','WEEK','Week','LMONTH','Lunar Month');
SELECT ffc.context_id
FROM ff_contexts ffc
WHERE ffc.context_name = p_context_name;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT /*+ ORDERED */ 1
FROM pay_assignment_actions bal_assact
-- ,pay_payroll_actions bact
,pay_assignment_actions assact
-- ,pay_payroll_actions pact
,pay_element_types_f adj_petf
,pay_run_results adj_prr
WHERE bal_assact.assignment_action_id = c_ass_act_id -- assignment_action_id
--and bal_assact.payroll_action_id = bact.payroll_action_id
--and assact.payroll_action_id = pact.payroll_action_id
--and pact.time_period_id = bact.time_period_id
--and assact.action_sequence <= bal_assact.action_sequence
and assact.assignment_id = bal_assact.assignment_id
AND adj_prr.assignment_action_id = assact.assignment_action_id
AND adj_prr.status in ('P','PA')
AND adj_petf.element_type_id = adj_prr.element_type_id
AND adj_petf.element_name = 'New Wage Report Override'
AND adj_petf.legislation_code = 'NL');
p_balance_values.delete;
SELECT hoi.org_information2 org_id
,hoi.org_information5 Public_Org
,hoi.org_information6 Own_Risk_Cover
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_LE_TAX_DETAILS'
AND hoi.organization_id = p_organization_id
AND EXISTS (SELECT 1
FROM hr_organization_information hoi1
WHERE hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND hoi1.organization_id = hoi.organization_id);
/*SELECT hoi.org_information2 org_id
,hoi.org_information5 Public_Org
,hoi.org_information6 Own_Risk_Cover
FROM hr_organization_information hoi
,hr_organization_information hoi1
WHERE hoi.org_information_context = 'NL_LE_TAX_DETAILS'
AND hoi.organization_id = hoi1.organization_id
AND hoi1.organization_id = p_organization_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS';*/
SELECT hoi.org_information5 Own_Risk_Cover
,hoi.org_information6 Contract_Code_Mapping
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_ORG_WR_INFO'
AND hou.business_group_id = c_business_group_id
AND hou.organization_id = hoi.organization_id
AND hou.organization_id = c_employer_id;
SELECT hoi.org_information17 Public_Org
FROM hr_organization_units hou,hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_ORG_INFORMATION'
AND hou.business_group_id = c_business_group_id
AND hou.organization_id = hoi.organization_id
AND hou.organization_id = c_employer_id;
select fnd_date.canonical_to_date(org_information5)
from hr_organization_information
where organization_id = c_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT hoi.org_information1 sender_id
,hoi.org_information2 contact_name
,hoi.org_information3 contact_num
FROM hr_organization_units hou,hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_ORG_WR_INFO'
AND hou.business_group_id = c_business_group_id
AND hou.organization_id = hoi.organization_id
AND hou.organization_id = c_employer_id;
SELECT hoi.org_information14 tax_rep_name
,hoi.org_information4 tax_reg_num
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_ORG_INFORMATION'
AND hou.business_group_id = c_business_group_id
AND hou.organization_id = hoi.organization_id
AND hou.organization_id = c_employer_id;
SELECT hoi.org_information1 tax_ref_no
,hoi.org_information2 org_id
,hoi.org_information3 tax_rep_name
FROM hr_organization_information hoi
,hr_organization_information hoi1
WHERE hoi.org_information_context = 'NL_LE_TAX_DETAILS'
AND hoi.organization_id = hoi1.organization_id
AND hoi1.organization_id = p_organization_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS';
/*Delete all data archived for the current payroll_action_id - to handle assignment level retry*/
DELETE pay_action_information
WHERE action_context_id = p_actid
AND action_context_type = 'PA';
SELECT hoi.org_information1 sender_id
,hoi.org_information2 contact_name
,hoi.org_information3 contact_num
FROM hr_organization_units hou,hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_ORG_WR_INFO'
AND hou.business_group_id = c_business_group_id
AND hou.organization_id = hoi.organization_id
AND hou.organization_id = c_employer_id;
SELECT hoi.org_information14 tax_rep_name
,hoi.org_information4 tax_reg_num
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_ORG_INFORMATION'
AND hou.business_group_id = c_business_group_id
AND hou.organization_id = hoi.organization_id
AND hou.organization_id = c_employer_id;
SELECT DISTINCT pai.Action_Information2 Start_date
,pai.action_information3 End_date
FROM pay_assignment_actions paa
,pay_action_information pai
WHERE paa.payroll_action_id = c_pact_id
AND paa.assignment_action_id = pai.action_context_id
AND pai.action_information_category = 'NL_WR_EMPLOYMENT_INFO'
AND pai.action_information1 IN ('INITIAL','CORRECTION','CORRECT')
AND pai.action_context_type = 'AAP';
SELECT distinct hoi1.org_information5 sector
,hoi1.org_information6 risk_group
,hoi1.organization_id
FROM hr_organization_information hoi1
,hr_organization_information hoi2
,hr_organization_information hoi3
,per_org_structure_versions posv
WHERE hoi2.org_information4 = hoi1.organization_id
AND hoi1.org_information5 IS NOT NULL
AND hoi1.org_information6 IS NOT NULL
AND hoi2.org_information_context= 'NL_SIP'
AND hoi1.org_information_context= 'NL_UWV'
AND hoi3.organization_id = c_business_group_id
AND hoi3.org_information_context= 'NL_BG_INFO'
--AND hoi2.org_information7 = 'Y'
AND hr_nl_org_info.Get_Tax_Org_Id(posv.ORG_STRUCTURE_VERSION_ID ,hoi2.organization_id) = c_employer_id
AND posv.ORGANIZATION_STRUCTURE_ID = TO_NUMBER(hoi3.org_information1)
AND c_effective_date BETWEEN posv.date_from
AND nvl(posv.date_to,to_date('31-12-4712','dd-mm-yyyy'))
AND c_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1)
AND nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31-12-4712','dd-mm-yyyy'))
AND EXISTS (SELECT 1
FROM hr_organization_information hoi4
WHERE hoi4.organization_id = hoi1.organization_id
AND hoi4.org_information_context = 'NL_SIT'
AND ORG_INFORMATION4 = 'WEWA')
ORDER BY 1,2;
SELECT DISTINCT fnd_date.canonical_to_date(pai.Action_Information2) Start_date
,fnd_date.canonical_to_date(pai.action_information3) End_date
FROM pay_assignment_actions paa
,pay_action_information pai
WHERE paa.payroll_action_id = c_pact_id
AND paa.assignment_action_id = pai.action_context_id
AND pai.action_information_category = 'NL_WR_EMPLOYMENT_INFO'
AND pai.action_information1 IN ('INITIAL','CORRECTION','CORRECT')
AND pai.action_context_type = 'AAP'
AND pai.action_information17 = 'PAY';
SELECT hoi.org_information2 org_id
,hoi.org_information5 Public_Org
,hoi.org_information6 Own_Risk_Cover
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_LE_TAX_DETAILS'
AND hoi.organization_id = p_organization_id
AND EXISTS (SELECT 1
FROM hr_organization_information hoi1
WHERE hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND hoi1.organization_id = hoi.organization_id); */
SELECT hoi.org_information1 tax_ref_no
,hoi.org_information2 org_id
,hoi.org_information3 tax_rep_name
FROM hr_organization_information hoi
,hr_organization_information hoi1
WHERE hoi.org_information_context = 'NL_LE_TAX_DETAILS'
AND hoi.organization_id = hoi1.organization_id
AND hoi1.organization_id = p_organization_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS';
SELECT pai_p.action_information4 Message
,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
,pai_p.action_information6 Description
,substr(pai_p.action_information7,1,30) E_Name
,substr(pai_p.action_information8,1,30) E_Number
,pai_p.action_context_type cxt
FROM pay_action_information pai_p
WHERE pai_p.action_context_id = p_payroll_action_id
AND pai_p.action_information_category = 'NL_WR_EXCEPTION_REPORT'
AND pai_p.action_context_type = 'PA'
ORDER BY pai_p.action_information8 asc;
SELECT pai_p.action_information4 Message
,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
,pai_p.action_information6 Description
,substr(pai_p.action_information7,1,30) E_Name
,substr(pai_p.action_information8,1,30) E_Number
,pai_p.action_context_type cxt
FROM pay_assignment_actions paa
,pay_action_information pai_p
WHERE paa.payroll_action_id = p_payroll_action_id
AND pai_p.action_context_id = paa.assignment_action_id
AND pai_p.action_information_category = 'NL_WR_EXCEPTION_REPORT'
AND pai_p.action_context_type = 'AAP'
ORDER BY pai_p.action_information8 asc;
SELECT DISTINCT paa1.payroll_action_id
FROM pay_assignment_actions paa
,pay_action_interlocks pal
,pay_assignment_actions paa1
WHERE paa.payroll_action_id = p_actid
AND paa.assignment_action_id = pal.locking_action_id
AND pal.locked_action_id = paa1.assignment_action_id
ORDER BY paa1.payroll_action_id DESC;
SELECT pai.*
FROM pay_action_information pai
WHERE pai.action_context_type = 'PA'
AND pai.action_context_id = c_pactid
AND pai.action_information_category = c_category;
SELECT action_information7
,action_information8
,sum(fnd_number.canonical_to_number(action_information9)) action_information9
,sum(fnd_number.canonical_to_number(action_information10))action_information10
FROM pay_action_information pai
,pay_payroll_actions ppa
WHERE ppa.report_type = 'NL_WAGES_REP_ARCHIVE'
AND ppa.report_qualifier = 'NL'
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND INSTR(ppa.legislative_parameters,'Payroll_Type=WEEK') <> 0
AND INSTR(ppa.legislative_parameters,'Legal_Employer='||c_legal_employer) <> 0
AND ppa.effective_date BETWEEN c_start_date
AND c_end_date
AND pai.action_context_type = 'PA'
AND pai.action_context_id = ppa.payroll_action_id
AND pai.action_information_category = 'NL_WR_SWMF_SECTOR_RISK_GROUP'
AND EXISTS ( SELECT 1
FROM pay_assignment_actions paa1
,pay_action_interlocks ai
,pay_assignment_actions paa2
WHERE paa1.payroll_action_id = p_actid
AND paa1.assignment_action_id = ai.locking_action_id
AND ai.locked_action_id = paa2.assignment_action_id
AND paa2.payroll_action_id = ppa.payroll_action_id)
GROUP BY action_information7,action_information8;
SELECT max(locked_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_action_interlocks pai
WHERE ppa.payroll_action_id = c_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.locking_action_id = paa.assignment_action_id;
SELECT max(paa2.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_action_interlocks pai
,pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa2
WHERE ppa.payroll_action_id = c_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.locking_action_id = paa.assignment_action_id
AND paa1.assignment_action_id = pai.locked_action_id
AND paa1.assignment_id = paa2.assignment_id
AND paa2.payroll_action_id = ppa2.payroll_action_id
AND ppa.business_group_id = ppa2.business_group_id
AND ppa2.effective_date between c_start_date and c_end_date
AND ppa2.action_type in ('R','Q')
AND ppa2.action_status = 'C';
SELECT paa2.assignment_action_id, ptp2.end_date
FROM pay_assignment_actions paa1
,pay_payroll_actions ppa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa2
,per_time_periods ptp1
,per_time_periods ptp2
,pay_all_payrolls_f ppf1
,pay_all_payrolls_f ppf2
WHERE paa1.assignment_action_id = c_ass_act_id
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.payroll_id = ppf1.payroll_id
AND ppa1.time_period_id = ptp1.time_period_id
AND ppf1.period_type = 'Calendar Month'
AND ppf1.period_type = ppf2.period_type
AND paa1.tax_unit_id = paa2.tax_unit_id
AND paa2.payroll_action_id = ppa2.payroll_action_id
AND ppa2.payroll_id = ppf2.payroll_id
AND ppa2.time_period_id = ptp2.time_period_id
AND ptp2.end_date < ptp1.end_date
ORDER BY 2 DESC;
/*Delete all data archived for the current payroll_action_id - to handle assignment level retry*/
DELETE pay_action_information
WHERE action_context_id = p_actid
AND action_context_type = 'PA';
SELECT asl.assignment_id assignment_id
,paa.assignment_action_id assignment_action_id
FROM per_all_assignments_f asl
,pay_all_payrolls_f ppf
,pay_payroll_actions ppa
,pay_assignment_actions paa
,per_time_periods ptp
WHERE asl.person_id BETWEEN stperson AND endperson and
ppf.payroll_id = asl.payroll_id
AND ((ppf.period_type = 'Calendar Month'
AND c_payroll_type = 'MONTH') OR
(ppf.period_type = 'Week' AND c_payroll_type = 'WEEK')OR
(ppf.period_type = 'Lunar Month' AND c_payroll_type = 'LMONTH'))
AND ppf.payroll_id = ppa.payroll_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.source_action_id IS NULL
AND paa.tax_unit_id = c_tax_unit_id
AND ppa.time_period_id = ptp.time_period_id
AND c_end_date BETWEEN ptp.start_date
AND ptp.end_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = asl.assignment_id
AND asl.effective_start_date <= c_end_date
AND asl.effective_end_date >= c_start_date
AND c_end_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND asl.business_group_id = ppa.business_group_id
AND ppa.business_group_id = c_business_group_id
AND (EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_run_results prr
WHERE paa1.source_action_id = paa.assignment_action_id
AND prr.assignment_action_id = paa1.assignment_action_id))
UNION
SELECT asl.assignment_id assignment_id
,NULL assignment_action_id
FROM per_all_assignments_f asl
,pay_all_payrolls_f ppf
WHERE asl.person_id BETWEEN stperson AND endperson
AND c_paid_flag = 'N'
AND ppf.payroll_id = asl.payroll_id
AND asl.assignment_type = 'E'
AND asl.business_group_id = c_business_group_id
AND ((ppf.period_type = 'Calendar Month'--,'Lunar Month')
AND c_payroll_type = 'MONTH') OR
(ppf.period_type = 'Week' AND c_payroll_type = 'WEEK')OR
(ppf.period_type = 'Lunar Month' AND c_payroll_type = 'LMONTH'))
AND asl.effective_start_date <= c_end_date
AND asl.effective_end_date >= c_start_date
AND c_end_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.prl_information_category = 'NL'
AND ((asl.establishment_id = c_tax_unit_id ) OR
(asl.establishment_id IS NULL AND ppf.PRL_INFORMATION1 = c_tax_unit_id))
ORDER BY 1;
SELECT hoi.org_information2 org_id
,hoi.org_information4 paid_flag
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_LE_TAX_DETAILS'
AND hoi.organization_id = p_organization_id
AND EXISTS (SELECT 1
FROM hr_organization_information hoi1
WHERE hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND hoi1.organization_id = hoi.organization_id);
SELECT hoi.org_information4 paid_flag
FROM hr_organization_units hou,hr_organization_information hoi
WHERE hoi.org_information_context = 'NL_ORG_WR_INFO'
AND hou.business_group_id = c_business_group_id
AND hou.organization_id = hoi.organization_id
AND hou.organization_id = c_employer_id;
SELECT MAX(paa.assignment_action_id) assignment_action_id
,paa.assignment_id
,paaf.person_id
,ppa1.effective_date
,paaf.primary_flag
FROM pay_payroll_actions ppa
,pay_payroll_actions ppa1
,pay_assignment_actions paa
,per_all_assignments_f paaf
WHERE ppa.report_type = 'NL_WAGES_REP_LOCK'
AND ppa.report_qualifier = 'NL'
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND ppa1.report_type = 'NL_WAGES_REP_ARCHIVE'
AND ppa1.report_qualifier = 'NL'
AND ppa1.action_type = 'X'
AND ppa1.action_status = 'C'
AND INSTR(ppa.legislative_parameters,'REQUEST_ID='||ppa1.payroll_action_id ) <> 0
AND INSTR(ppa1.legislative_parameters,'Payroll_Type=WEEK') <> 0
AND ppa1.effective_date BETWEEN c_start_date
AND c_end_date
AND ppa1.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = paaf.assignment_id
AND paa.tax_unit_id = c_tax_unit_id
AND paaf.person_id BETWEEN stperson
AND endperson
AND paaf.effective_start_date <= ppa1.effective_date
AND paaf.effective_end_date >= ppa1.start_date
AND paaf.business_group_id = c_business_group_id
AND paaf.business_group_id = ppa.business_group_id
AND ppa1.business_group_id = ppa.business_group_id
GROUP BY paa.assignment_id
,paaf.person_id
,ppa1.effective_date
,paaf.primary_flag
ORDER BY paaf.person_id
,paaf.primary_flag DESC
,ppa1.effective_date DESC;
SELECT DISTINCT asl.assignment_id assignment_id
,paa.assignment_action_id assignment_action_id
FROM per_all_assignments_f asl
,pay_payroll_actions ppa
,pay_assignment_actions paa
,per_time_periods ptp
WHERE asl.person_id BETWEEN stperson AND endperson and
ppa.payroll_id = asl.payroll_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.source_action_id IS NULL
AND paa.tax_unit_id = c_tax_unit_id
AND ppa.time_period_id = ptp.time_period_id
AND to_char(ptp.end_date,'RRRR') = to_char(c_start_date,'RRRR')
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = asl.assignment_id
AND asl.effective_start_date <= c_end_date
AND asl.effective_end_date >= c_start_date
AND asl.business_group_id = ppa.business_group_id
AND ppa.business_group_id = c_business_group_id
AND (EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_run_results prr
WHERE paa1.source_action_id = paa.assignment_action_id
AND prr.assignment_action_id = paa1.assignment_action_id))
UNION
SELECT asl.assignment_id assignment_id
,NULL assignment_action_id
FROM per_all_assignments_f asl
,pay_all_payrolls_f ppf
WHERE asl.person_id BETWEEN stperson AND endperson
AND c_paid_flag = 'N'
AND ppf.payroll_id = asl.payroll_id
AND asl.assignment_type = 'E'
AND asl.business_group_id = c_business_group_id
AND asl.effective_start_date <= c_end_date
AND asl.effective_end_date >= c_start_date
AND ppf.effective_end_date >= c_start_date
AND ppf.prl_information_category = 'NL'
AND ((asl.establishment_id = c_tax_unit_id ) OR
(asl.establishment_id IS NULL AND ppf.PRL_INFORMATION1 = c_tax_unit_id))
ORDER BY 1,2 desc;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT paa.assignment_action_id
,paa.assignment_id
FROM pay_assignment_actions paa
,per_all_assignments_f paaf
,pay_payroll_actions ppa
WHERE paa.payroll_action_id = p_arc_pactid
AND paa.payroll_action_id = ppa.payroll_action_id
AND paaf.person_id BETWEEN stperson
AND endperson
AND paa.assignment_id = paaf.assignment_id
AND ppa.effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
ORDER BY paa.assignment_action_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT decode(segment4,'Y','J',segment4) wage_tax_discount
,decode(segment6,'R','J','I','N','S','N') work_pattern
,segment8 income_code
,segment11 wage_tax_table
--,decode(INSTR(NVL(segment10,'00'),'01'),0,(decode(INSTR(segment13,'02'),0,1,3)),2) company_car_use --01/02
,decode(INSTR(NVL(segment10,'00'),'71'),0,'N','J') wage_aow
,decode(INSTR(NVL(segment10,'00'),'72'),0,'N','J') wage_wajong
,decode(INSTR(NVL(segment10,'00'),'43'),0,'N','J') emp_loan
,decode(INSTR(NVL(segment10,'00'),'03'),0,'N','J') transportation
FROM hr_soft_coding_keyflex
WHERE soft_coding_keyflex_id = c_scl_id;
SELECT addr.style
,addr.address_line1 address_line1
,addr.address_line2 address_line2
,addr.address_line3 address_line3
,addr.town_or_city town_or_city
,UPPER(addr.postal_code) postal_code
,addr.region_1 street_name
,addr.region_2 region_2
,addr.region_3 PO_Box_number
,addr.country country
,addr.telephone_number_1 telephone_number_1
,addr.telephone_number_2 telephone_number_2
,addr.telephone_number_3 telephone_number_3
,addr.add_information13 House_Number
,addr.add_information14 House_Number_Addition
FROM per_addresses addr
WHERE addr.person_id = c_person_id
AND addr.primary_flag = 'Y'
AND c_effective_date BETWEEN addr.date_from AND
nvl(addr.date_to,fnd_date.canonical_to_date('4712/12/31'))
ORDER BY 1 DESC;
SELECT DISTINCT hoi.organization_id
,hoi.org_information5 sector
,hoi.org_information6 risk_group
,fnd_date.canonical_to_date(aei.AEI_INFORMATION1) start_date
,fnd_date.canonical_to_date(aei.AEI_INFORMATION2) end_date
FROM hr_organization_information hoi
,per_assignment_extra_info aei
WHERE hoi.organization_id = nvl( aei.aei_information8,HR_NL_ORG_INFO.Get_SI_Provider_Info(aei.assignment_id,aei.AEI_INFORMATION3))
AND aei.assignment_id = c_assignment_id
AND aei.information_type = 'NL_SII'
AND aei.aei_information3 IN ('WW','AMI','ZW','WAO')
AND hoi.org_information5 IS NOT NULL
AND hoi.org_information6 IS NOT NULL
AND hoi.org_information_context= 'NL_UWV'
ORDER BY 2,3;
/*SELECT DISTINCT organization_id
,org_information5 sector
,org_information6 risk_group
FROM hr_organization_information
WHERE organization_id IN (SELECT HR_NL_ORG_INFO.Get_SI_Provider_Info(assignment_id,AEI_INFORMATION3)
FROM per_assignment_extra_info
WHERE assignment_id = c_assignment_id
AND information_type = 'NL_SII'
AND aei_information3 IN ('WW','AMI'))
AND org_information5 IS NOT NULL
AND org_information6 IS NOT NULL
AND org_information_context= 'NL_UWV';*/
SELECT fnd_date.canonical_to_date(aei_information1) start_date
,fnd_date.canonical_to_date(aei_information2) end_date
,aei_information3 info1
,aei_information15 info2
,aei_information_category
FROM per_assignment_extra_info
WHERE assignment_extra_info_id = c_a_extra_info_id
AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII');
SELECT fnd_date.canonical_to_date(aei_information1) start_date
,fnd_date.canonical_to_date(aei_information2) end_date
,aei_information3 info1
,aei_information15 info2
,aei_information_category
FROM per_assignment_extra_info
WHERE assignment_id = c_assignment_id
AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII')
ORDER BY 1 DESC;
p_copy_to(p_to).update_type := p_copy_from(p_from).update_type;
SELECT peef.element_entry_id
FROM pay_element_entries_f peef
,pay_element_types_f pet
WHERE pet.element_name IN ('Holiday Coupons','Incidental Income Decrease','Additional Allowance','Company Car Private Usage')
AND pet.legislation_code = 'NL'
AND peef.assignment_id = c_assignment_id
AND peef.element_type_id = pet.element_type_id
AND c_eff_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND c_eff_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT dated_table_id
FROM pay_dated_tables
WHERE TABLE_NAME = c_table_name;
SELECT fnd_date.canonical_to_date(aei_information1) start_date
FROM per_assignment_extra_info
WHERE assignment_extra_info_id = c_a_extra_info_id
AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII');
l_detail_tab.delete;
l_detail_tab.delete;
p_detail_tab(l_index).update_type := l_detail_tab(i).update_type;
p_detail_tab(l_index).update_type := l_detail_tab(i).update_type;
l_detail_tab.delete;
l_detail_tab.delete;
p_detail_tab(l_index).update_type := l_detail_tab(i).update_type;
p_detail_tab(l_index).update_type := l_detail_tab(i).update_type;
p_rec_changes.delete;
p_rec_changes(l_cnt).update_type := p_rec_changes_init(i).update_type;
SELECT pap.person_id
,pap.national_identifier sofi_number
,pap.employee_number
,pap.nationality
,pap.date_of_birth dob
,pap.pre_name_adjunct prefix
,pap.last_name
,UPPER(replace(replace(pap.per_information1,'.',''),' ','')) initials
,decode(pap.sex,'M',1,'F',2,NULL) gender
,paaf.assignment_id
,paaf.change_reason
,paaf.assignment_number
,paaf.assignment_sequence
,paaf.employment_category
,paaf.employee_category
,paaf.collective_agreement_id
,paaf.effective_start_date
,paaf.soft_coding_keyflex_id
,paaf.assignment_status_type_id
,paaf.payroll_id
,paaf.primary_flag
FROM per_all_assignments_f paaf
,per_all_people_f pap
WHERE paaf.assignment_id = c_assignment_id
AND paaf.person_id = pap.person_id
AND c_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT pps.date_start emp_start_date
,pps.actual_termination_date emp_termination_date
,paaf.primary_flag
FROM per_all_assignments_f paaf
,per_periods_of_service pps
WHERE paaf.assignment_id = c_assignment_id
AND pps.person_id = paaf.person_id
AND pps.period_of_service_id = paaf.period_of_service_id;*/
SELECT pca.cag_information1
FROM per_collective_agreements pca
WHERE pca.collective_agreement_id = c_collective_agreement_id
AND pca.cag_information_category= 'NL';
SELECT 'Y'
FROM DUAL
WHERE EXISTS(SELECT /*+ ORDERED */ 1
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_action_interlocks pai
,pay_assignment_actions pal
,pay_payroll_actions ppl
WHERE paa.assignment_id = c_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.report_type = 'NL_WAGES_REP_ARCHIVE'
AND ppa.report_qualifier = 'NL'
AND ppa.report_category = 'ARCHIVE'
AND ppa.action_status ='C'
AND paa.assignment_action_id = pai.locked_action_id
AND pai.locking_action_id = pal.assignment_action_id
AND pal.payroll_action_id = ppl.payroll_action_id
AND ppl.report_type = 'NL_WAGES_REP_LOCK'
AND ppl.report_qualifier = 'NL'
AND ppl.report_category = 'ARCHIVE'
AND ppl.action_status ='C');
SELECT prr.rowid row_id
,prr.element_entry_id
,min(decode(piv.name, 'Report Type', rrv.RESULT_VALUE, null)) Retro_type
,min(decode(piv.name, 'Period', rrv.RESULT_VALUE, null)) Period
FROM pay_run_results prr
,pay_run_result_values rrv
,pay_input_values_f piv
,pay_element_types_f pet
WHERE prr.run_result_id = rrv.run_result_id
AND rrv.input_value_id + 0 = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND prr.element_type_id = pet.element_type_id
AND prr.assignment_action_id = c_ass_act_id
AND pet.element_name = 'New Wage Report Override'
AND pet.legislation_code = 'NL'
AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
GROUP BY prr.rowid
,prr.element_entry_id
ORDER BY 4,3 DESC;
SELECT DISTINCT ptp.start_date start_date
,ptp.end_date end_date
FROM pay_run_results prr
,pay_element_entries_f pee
,pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
WHERE prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = pee.element_type_id
AND pee.creator_type IN ('RR','EE')
AND pee.assignment_id = paa.assignment_id
AND paa.assignment_action_id = c_assignment_action_id
AND prr.start_date > c_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND prr.start_date BETWEEN ptp.start_date and ptp.end_date
ORDER by 1;
SELECT rr1.start_date , rc.short_name,rr1.element_entry_id
,rr1.end_date
FROM pay_run_results rr1 -- Retro element
,pay_run_results rr2 -- Normal Element
,pay_element_span_usages esu
,pay_retro_component_usages rcu
,pay_retro_components rc
,pay_element_entries_f pee1
,pay_element_entries_f pee2
,pay_retro_assignments pra
,pay_retro_entries pre
WHERE rr1.assignment_action_id = c_assignment_action_id
AND rr2.assignment_action_id = rr1.assignment_action_id
AND rr1.element_type_id = esu.retro_element_type_id
AND esu.retro_component_usage_id = rcu.retro_component_usage_id
AND rcu.creator_id = rr2.element_type_id
AND rcu.creator_type = 'ET' -- check
AND rr1.element_entry_id = pee1.element_entry_id
AND pee1.creator_type = 'RR'
AND pee1.creator_id = pra.retro_assignment_action_id
AND pra.assignment_id = c_assignment_id
-- AND pra.assignment_id = pee1.assignment_id
AND pra.retro_assignment_id = pre.retro_assignment_id
AND rr2.element_entry_id = pee2.element_entry_id
AND pee2.element_entry_id = pre.element_entry_id
AND pre.retro_component_id = rc.retro_component_id
AND rc.legislation_code = 'NL'
AND rc.short_name = 'Standard'
AND c_effective_date between pee1.effective_start_date and pee1.effective_end_date
AND c_effective_date between pee2.effective_start_date and pee2.effective_end_date;*/
SELECT DISTINCT ptp.start_date
,ptp.end_date
FROM pay_run_results rr1 -- Retro element
,pay_element_span_usages esu
,pay_retro_component_usages rcu
,pay_retro_components rc
,pay_element_entries_f pee1
,pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
WHERE paa.assignment_action_id = c_assignment_action_id
AND rr1.assignment_action_id = paa.assignment_action_id
AND rr1.element_entry_id = pee1.element_entry_id
AND pee1.creator_type = 'RR'
AND rr1.element_type_id = esu.retro_element_type_id
AND esu.retro_component_usage_id = rcu.retro_component_usage_id
AND rcu.creator_type = 'ET' -- check
AND rcu.retro_component_id = rc.retro_component_id
AND rc.legislation_code = 'NL'
AND rc.short_name = 'Standard'
AND c_effective_date between pee1.effective_start_date and pee1.effective_end_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND rr1.start_date BETWEEN ptp.start_date and ptp.end_date
ORDER BY 1;
/*SELECT rr.start_date
,rr.end_date
FROM pay_run_results rr
,pay_element_span_usages esu
,pay_retro_component_usages rcu
,pay_retro_components rc
WHERE rr.assignment_action_id = c_assignment_action_id
AND rr.element_type_id = esu.retro_element_type_id
AND esu.retro_component_usage_id = rcu.retro_component_usage_id
AND rcu.retro_component_id = rc.retro_component_id
AND rc.legislation_code = 'NL'
AND rc.short_name = 'Standard';*/
SELECT max(paa.assignment_action_id) assignment_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
WHERE paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R','Q')
AND paa.ACTION_STATUS = 'C'
AND ppa.ACTION_STATUS = 'C'
--AND ppa.date_earned between c_start_date AND c_end_date;
SELECT max(paa.assignment_action_id) assignment_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
WHERE paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R','Q','I','B')
AND paa.ACTION_STATUS = 'C'
AND ppa.ACTION_STATUS = 'C'
-- AND ppa.date_earned between c_start_date AND c_end_date;
SELECT business_group_id,system_type_cd
FROM per_shared_types
WHERE lookup_type = c_lookup --'NL_NATIONALITY'
AND information1 = c_code
AND (business_group_id = c_business_gr_id
OR business_group_id is NULL)
ORDER BY 1;
SELECT ptp.start_date,ptp.end_date
FROM per_time_periods ptp
WHERE ptp.payroll_id = c_payroll_id
AND c_date between ptp.start_date and ptp.end_date;
SELECT dated_table_id
FROM pay_dated_tables
WHERE TABLE_NAME = c_table_name; -- in ('PAY_ELEMENT_ENTRY_VALUES_F','PAY_ELEMENT_ENTRIES_F');
SELECT peev.screen_entry_value
FROM pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f peef
,pay_element_entry_values_f peev
WHERE pet.element_name = c_element_name
AND pet.element_type_id = piv.element_type_id
AND piv.name = c_input_val_name
AND pet.legislation_code = 'NL'
AND piv.legislation_code = 'NL'
AND peef.assignment_id = c_assignment_id
AND peef.element_entry_id = peev.element_entry_id
AND peef.element_type_id = pet.element_type_id
AND peev.input_value_id = piv.input_value_id
AND c_eff_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND c_eff_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_eff_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND c_eff_date BETWEEN peef.effective_start_date
AND peef.effective_end_date;
SELECT pet.element_name
,peev.screen_entry_value
FROM pay_element_types_f pet
,pay_element_entries_f peef
,pay_element_entry_values_f peev
WHERE peev.element_entry_value_id = c_element_entry_value_id
AND peev.element_entry_id = peef.element_entry_id
AND peef.element_type_id = pet.element_type_id
AND pet.legislation_code = 'NL'
AND c_eff_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_eff_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND c_eff_date BETWEEN peef.effective_start_date
AND peef.effective_end_date;
SELECT pet.element_name
,peev.screen_entry_value
FROM pay_element_types_f pet
,pay_element_entries_f peef
,pay_element_entry_values_f peev
WHERE peef.element_entry_id = c_element_entry_id
AND peev.element_entry_id = peef.element_entry_id
AND peef.element_type_id = pet.element_type_id
AND pet.legislation_code = 'NL'
AND c_eff_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_eff_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND c_eff_date BETWEEN peef.effective_start_date
AND peef.effective_end_date; /*assuming one input value*/
SELECT aei_information5
FROM per_assignment_extra_info
WHERE assignment_id = c_assignment_id
AND aei_information_category IN ('NL_CADANS_INFO');
SELECT min(effective_start_date)
--,decode(max(effective_end_date),to_date('31-12-4712','dd-mm-yyyy'),null,max(effective_end_date))
FROM per_all_assignments_F paaf
,PER_ASSIGNMENT_STATUS_TYPES ast
WHERE paaf.assignment_id = c_assignment_id
AND paaf.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status = 'ACTIVE_ASSIGN';
SELECT decode(max(effective_end_date),to_date('31-12-4712','dd-mm-yyyy'),null,max(effective_end_date))
FROM per_all_assignments_F paaf
,PER_ASSIGNMENT_STATUS_TYPES ast
WHERE paaf.assignment_id = c_assignment_id
AND paaf.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN';
SELECT min(effective_start_date)
FROM per_all_assignments_F paaf
,PER_ASSIGNMENT_STATUS_TYPES ast
WHERE paaf.assignment_id = c_assignment_id
AND paaf.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status = 'TERM_ASSIGN';
SELECT paaf.soft_coding_keyflex_id
FROM per_all_assignments_f paaf
WHERE assignment_id = c_assignment_id
AND c_start_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT collective_agreement_id
FROM per_All_assignments_F
WHERE assignment_id = c_assignment_id
AND c_eff_date BETWEEN effective_start_date AND effective_end_date;
l_rec_changes_init(l_cnt3).update_type := l_detail_tab(l_cnt1).update_type;
l_rec_changes_init(l_cnt3).update_type := 'RETRO';
l_rec_changes_init(l_cnt3).update_type := l_detail_tab(l_cnt1).update_type;
l_rec_changes_init(l_cnt3).update_type := l_detail_tab(l_cnt1).update_type;
l_rec_changes_init(l_cnt3).update_type := 'RETRO';
IF l_rec_changes(i).update_type = 'U' AND
l_rec_changes(i).column_name = 'EFFECTIVE_START_DATE' AND
l_rec_changes(i).dated_table_id NOT IN (l_table1,l_table2) AND
l_chg_pending = 'N' THEN
l_chk := 'N';
SELECT soft_coding_keyflex_id
INTO l_emp_rec.soft_coding_keyflex_id
FROM per_all_assignments_f
WHERE assignment_id = l_assignment_id
AND l_rec_changes(i).effective_date BETWEEN effective_start_date AND effective_end_date;
l_rec_changes(i).update_type = 'C' AND
l_rec_changes(i).dated_table_id <> l_table3 THEN
--Fnd_file.put_line(FND_FILE.LOG,' IGNORING CHANGE : Correction in current period');
SELECT pai.*
FROM pay_action_interlocks pal
,pay_action_information pai
WHERE pal.locking_action_id = p_assactid
AND pal.locked_action_id = pai.action_context_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = c_category
AND pai.assignment_id = c_assignment_id
AND pai.action_information1 = c_type
ORDER BY pai.effective_date DESC;
SELECT MIN(fnd_date.canonical_to_date(action_information5))
FROM pay_action_interlocks pal
,pay_action_information pai
WHERE pal.locking_action_id = c_assactid
AND pal.locked_action_id = pai.action_context_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_WR_INCOME_PERIOD'
AND pai.assignment_id = c_assignment_id
AND pai.action_information1 = 'INITIAL';
SELECT MIN(fnd_date.canonical_to_date(action_information5))
FROM pay_action_interlocks pal
,pay_action_information pai
WHERE pal.locking_action_id = c_assactid
AND pal.locked_action_id = pai.action_context_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'NL_WR_SWMF_SECTOR_RISK_GROUP'
AND pai.assignment_id = c_assignment_id
AND pai.action_information1 = 'SECTOR_RISK_GROUP'
AND pai.action_information7 = c_sector
AND pai.action_information8 = c_risk_grp;
SELECT pai.*
FROM pay_action_information pai
WHERE pai.action_context_id = c_assactid
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = c_category;
SELECT pai.*
FROM pay_action_information pai
WHERE pai.action_context_type = 'AAP'
AND pai.action_information2 = fnd_number.number_to_canonical(c_actinfid)
AND pai.action_information_category = c_category
AND pai.action_information1 = c_type ;
SELECT pai.*
FROM pay_action_information pai
WHERE pai.action_context_type = 'AAP'
AND pai.action_information27 = fnd_number.number_to_canonical(c_actinfid)
AND pai.action_context_id = c_action_context_id
AND pai.action_information_category = c_category
AND pai.action_information26 = c_type ;
select sum(fnd_number.canonical_to_number(pai.action_information5)) sum5
,sum(fnd_number.canonical_to_number(pai.action_information6)) sum6
,sum(fnd_number.canonical_to_number(pai.action_information7)) sum7
,sum(fnd_number.canonical_to_number(pai.action_information8)) sum8
,sum(fnd_number.canonical_to_number(pai.action_information9)) sum9
,sum(fnd_number.canonical_to_number(pai.action_information10)) sum10
,sum(fnd_number.canonical_to_number(pai.action_information11)) sum11
,sum(fnd_number.canonical_to_number(pai.action_information12)) sum12
,sum(fnd_number.canonical_to_number(pai.action_information13)) sum13
,sum(fnd_number.canonical_to_number(pai.action_information14)) sum14
,sum(fnd_number.canonical_to_number(pai.action_information15)) sum15
,sum(fnd_number.canonical_to_number(pai.action_information16)) sum16
,sum(fnd_number.canonical_to_number(pai.action_information17)) sum17
,sum(fnd_number.canonical_to_number(pai.action_information18)) sum18
,sum(fnd_number.canonical_to_number(pai.action_information19)) sum19
,sum(fnd_number.canonical_to_number(pai.action_information20)) sum20
,sum(fnd_number.canonical_to_number(pai.action_information21)) sum21
,sum(fnd_number.canonical_to_number(pai.action_information22)) sum22
,sum(fnd_number.canonical_to_number(pai.action_information23)) sum23
,sum(fnd_number.canonical_to_number(pai.action_information24)) sum24
,sum(fnd_number.canonical_to_number(pai.action_information25)) sum25
,sum(fnd_number.canonical_to_number(pai.action_information26)) sum26
,sum(fnd_number.canonical_to_number(pai.action_information27)) sum27
,sum(fnd_number.canonical_to_number(pai.action_information28)) sum28
,sum(fnd_number.canonical_to_number(pai.action_information29)) sum29
from pay_action_interlocks pal
,pay_action_information pai
where pal.locking_action_id = c_assactid
AND pal.locked_action_id = pai.action_context_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = c_category
AND pai.action_information1 = c_type ;
SELECT ffc.context_id
FROM ff_contexts ffc
WHERE ffc.context_name = p_context_name;
p_balance_values.delete;
SELECT pap.person_id
,pap.national_identifier sofi_number
,pap.employee_number
,pap.nationality
,pap.date_of_birth dob
,pap.pre_name_adjunct prefix
,pap.last_name
,UPPER(replace(replace(pap.per_information1,'.',''),' ','')) initials
,decode(pap.sex,'M',1,'F',2,NULL) gender
,paaf.assignment_id
,paaf.change_reason
,paaf.assignment_number
,paaf.assignment_sequence
,paaf.employment_category
,paaf.employee_category
,paaf.collective_agreement_id
,paaf.effective_start_date
,paaf.soft_coding_keyflex_id
,paaf.assignment_status_type_id
,paaf.payroll_id
,paaf.primary_flag
FROM per_all_assignments_f paaf
,per_all_people_f pap
WHERE paaf.assignment_id = c_assignment_id
AND paaf.person_id = pap.person_id
AND c_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT pca.cag_information1
FROM per_collective_agreements pca
WHERE pca.collective_agreement_id = c_collective_agreement_id
AND pca.cag_information_category= 'NL';
SELECT max(paa.assignment_action_id) assignment_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
WHERE paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R','Q')
AND paa.ACTION_STATUS = 'C'
AND ppa.ACTION_STATUS = 'C'
--AND ppa.date_earned between c_start_date AND c_end_date;
SELECT max(paa.assignment_action_id) assignment_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
WHERE paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R','Q','I','B')
AND paa.ACTION_STATUS = 'C'
AND ppa.ACTION_STATUS = 'C'
-- AND ppa.date_earned between c_start_date AND c_end_date;
SELECT business_group_id,system_type_cd
FROM per_shared_types
WHERE lookup_type = c_lookup --'NL_NATIONALITY'
AND information1 = c_code
AND (business_group_id = c_business_gr_id
OR business_group_id is NULL)
ORDER BY 1;
SELECT ptp.start_date,ptp.end_date
FROM per_time_periods ptp
WHERE ptp.payroll_id = c_payroll_id
AND c_date between ptp.start_date and ptp.end_date;
SELECT peev.screen_entry_value
FROM pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f peef
,pay_element_entry_values_f peev
WHERE pet.element_name = c_element_name
AND pet.element_type_id = piv.element_type_id
AND piv.name = c_input_val_name
AND pet.legislation_code = 'NL'
AND piv.legislation_code = 'NL'
AND peef.assignment_id = c_assignment_id
AND peef.element_entry_id = peev.element_entry_id
AND peef.element_type_id = pet.element_type_id
AND peev.input_value_id = piv.input_value_id
AND c_eff_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND c_eff_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_eff_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND c_eff_date BETWEEN peef.effective_start_date
AND peef.effective_end_date;
SELECT pet.element_name
,peev.screen_entry_value
FROM pay_element_types_f pet
,pay_element_entries_f peef
,pay_element_entry_values_f peev
WHERE peev.element_entry_value_id = c_element_entry_value_id
AND peev.element_entry_id = peef.element_entry_id
AND peef.element_type_id = pet.element_type_id
AND pet.legislation_code = 'NL'
AND c_eff_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_eff_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND c_eff_date BETWEEN peef.effective_start_date
AND peef.effective_end_date;
SELECT pet.element_name
,peev.screen_entry_value
FROM pay_element_types_f pet
,pay_element_entries_f peef
,pay_element_entry_values_f peev
WHERE peef.element_entry_id = c_element_entry_id
AND peev.element_entry_id = peef.element_entry_id
AND peef.element_type_id = pet.element_type_id
AND pet.legislation_code = 'NL'
AND c_eff_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_eff_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND c_eff_date BETWEEN peef.effective_start_date
AND peef.effective_end_date; /*assuming one input value*/
SELECT aei_information5
FROM per_assignment_extra_info
WHERE assignment_id = c_assignment_id
AND aei_information_category IN ('NL_CADANS_INFO');
SELECT min(effective_start_date)
--,decode(max(effective_end_date),to_date('31-12-4712','dd-mm-yyyy'),null,max(effective_end_date))
FROM per_all_assignments_F paaf
,PER_ASSIGNMENT_STATUS_TYPES ast
WHERE paaf.assignment_id = c_assignment_id
AND paaf.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status = 'ACTIVE_ASSIGN';
SELECT decode(max(effective_end_date),to_date('31-12-4712','dd-mm-yyyy'),null,max(effective_end_date))
FROM per_all_assignments_F paaf
,PER_ASSIGNMENT_STATUS_TYPES ast
WHERE paaf.assignment_id = c_assignment_id
AND paaf.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN';
SELECT min(effective_start_date)
FROM per_all_assignments_F paaf
,PER_ASSIGNMENT_STATUS_TYPES ast
WHERE paaf.assignment_id = c_assignment_id
AND paaf.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status = 'TERM_ASSIGN';
SELECT paaf.soft_coding_keyflex_id
FROM per_all_assignments_f paaf
WHERE assignment_id = c_assignment_id
AND c_start_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT collective_agreement_id
FROM per_All_assignments_F
WHERE assignment_id = c_assignment_id
AND c_eff_date BETWEEN effective_start_date AND effective_end_date;
select payroll_action_id,assignment_id
into l_pactid, l_assignment_id
from pay_assignment_actions
where assignment_action_id = p_assactid;
SELECT action_information9
FROM pay_action_information pai
WHERE action_context_id = c_payroll_action_id
AND action_context_type = 'PA'
AND action_information_category = 'NL_WR_EMPLOYER_INFO';
SELECT 'EXCEPTION'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pay_action_information pai
WHERE action_context_id = c_payroll_action_id
AND action_information_category = 'NL_WR_EXCEPTION_REPORT')
OR EXISTS (SELECT 1
FROM pay_assignment_actions paa
,pay_action_information pai
WHERE paa.payroll_action_id = c_payroll_action_id
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_information_category = 'NL_WR_EXCEPTION_REPORT');