[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y' from
PAY_ACTION_INFORMATION
where ACTION_INFORMATION_CATEGORY = p_action_information_category
and ACTION_INFORMATION1 = p_action_information1
and ACTION_CONTEXT_ID = p_payroll_action_id;
select 'Y' from
PAY_ACTION_INFORMATION
where ACTION_INFORMATION_CATEGORY = p_action_information_category
and ACTION_INFORMATION26 = p_action_information1
and ACTION_CONTEXT_ID = p_payroll_action_id;
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
--,NVL(to_char(fnd_date.canonical_to_date(pay_nl_wage_report_pkg.get_parameters(c_payroll_action_id, 'CORR_YR')),'RRRR'),-9999) --10100628
,NVL(pay_nl_wage_report_pkg.get_parameters(c_payroll_action_id, 'CORR_YR'),'N') --10100628
,NVL(pay_nl_wage_report_pkg.get_parameters(c_payroll_action_id, 'ER_ONLY'),'N') --10100628
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;
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 fnd_date.canonical_to_date (org_information1) start_date
,fnd_date.canonical_to_date (org_information2) end_date
,org_information3 tag_name
,fnd_number.canonical_to_number (org_information4) amount
,org_information5 frequency
FROM hr_organization_information
WHERE organization_id = c_org_id
AND org_information_context = 'NL_ORG_WR_PREV_YR_CORRECTION'
AND org_information6 = c_payroll_type
AND c_balance_date BETWEEN fnd_date.canonical_to_date (org_information1)
AND nvl (fnd_date.canonical_to_date (org_information2)
,hr_general.end_of_time)
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
--fnd_date.canonical_to_date(paei.AEI_INFORMATION1) start_date
--,fnd_date.canonical_to_date(paei.AEI_INFORMATION2) end_date
SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION3)) LnLbPh
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION4)) LnSV
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION5)) LnTabBB
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION6)) VakBsl
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION7)) OpgRchtVakBsl
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION8)) ExtrSal
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION9)) OpgRchtExtrSal
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION10)) LnInGld
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION11)) WrdLn
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION12)) LnOwrk
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION13)) IngLbPh
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION14)) PrWAOAof
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION15)) PrWAOAok
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION16)) PrAWF
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION17)) PrWgf
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION18)) PrUFO
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION19)) BijdrZvw
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION20)) VergZvw
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION21)) Reisk
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION22)) VerrArbKrt
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION23)) AantSV
--,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION24)) AantVerlU --10100628
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION25)) LvLpReg
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION26)) LvLpRegToeg
--10100628
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION24)) PrLnWao
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION27)) PrLnWaoWga
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION28)) PrLnWwAwf
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION29)) PrLnUfo
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION30)) PrLnPrSectFnds
--10100628
FROM per_assignment_extra_info paei
WHERE
paei.aei_information_category = 'NL_WR_PREV_CORR'
AND p_date BETWEEN fnd_date.canonical_to_date(paei.aei_information1)
AND nvl(fnd_date.canonical_to_date(paei.aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
AND EXISTS
(
SELECT 1
from
pay_assignment_actions paa
where
paa.payroll_action_id = p_payroll_action_id
and paa.action_status = 'C'
and paa.assignment_id = paei.assignment_id
);
SELECT org_information2 method
,fnd_number.canonical_to_number (org_information3) amount
FROM hr_organization_information
WHERE organization_id = c_org_id
AND org_information_context = 'NL_ORG_LCA_INFO'
AND org_information1 = to_char(c_date,'YYYY');
SELECT fnd_number.canonical_to_number (global_value)
FROM ff_globals_f
WHERE global_name = c_global_name
AND legislation_code = 'NL'
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT get_parameters (c_payroll_action_id
,'Sequence_Number') seq_no
,start_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = c_payroll_action_id;
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 sum (CASE
WHEN info.action_information2 = 'TotLnLbPh'
THEN fnd_number.canonical_to_number (nvl (action_information6
,0)) END) taxableincome
,sum (CASE
WHEN info.action_information2 = 'EhOvsFrfWrkkstrg'
THEN fnd_number.canonical_to_number (nvl (action_information6
,0)) END) lcatax
,min (fnd_date.canonical_to_date (info.action_information3)) startdate
,max (fnd_date.canonical_to_date (info.action_information4)) enddate
,count (DISTINCT generator.request_id) wagereports
FROM pay_action_information info
,pay_payroll_actions generator
,pay_payroll_actions datalock
WHERE info.action_context_id = generator.payroll_action_id
AND info.action_information2 IN ('TotLnLbPh','EhOvsFrfWrkkstrg')
AND datalock.report_type = 'NL_WAGES_REP_LOCK'
AND datalock.business_group_id = c_bg_id
AND datalock.action_status = 'C'
AND to_char (generator.effective_date
,'YYYY') = to_char (c_date
,'YYYY')
AND generator.effective_date <= c_date
AND get_parameters (generator.payroll_action_id
,'Legal_Employer') = c_tax_unit_id
AND get_parameters (generator.payroll_action_id
,'Payroll_Type') = c_payroll_type
AND get_parameters (datalock.payroll_action_id
,'REQUEST_ID') = generator.payroll_action_id;
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 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
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION5) ,0)))) LnLbPh,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION6) ,0)))) LnSV,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION15),0)))) IngLbPh,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION16),0)))) PrWAOAof,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION17),0)))) PrWAOAok,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION18),0)))) PrAWF,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION19),0)))) PrWgf,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION20),0)))) PrUFO,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION21),0)))) BijdrZvw,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(pai.ACTION_INFORMATION22),0)))) VergZvw
FROM PAY_ACTION_INFORMATION pai
WHERE ACTION_CONTEXT_ID IN
(SELECT ASSIGNMENT_ACTION_ID
FROM PAY_ASSIGNMENT_ACTIONS paa
WHERE PAYROLL_ACTION_ID=c_payroll_action_id)
AND ACTION_INFORMATION_CATEGORY='NL_WR_NOMINATIVE_REPORT'
AND ACTION_INFORMATION1='INITIAL';
/* SELECT
COUNT(pai.ACTION_INFORMATION7) Sect,
COUNT(pai.ACTION_INFORMATION8) RisGrp,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(ACTION_INFORMATION10),0)))) PrWgf,
fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(ACTION_INFORMATION9),0)))) PrLnWgf
FROM pay_action_information pai
WHERE ACTION_CONTEXT_ID IN
(SELECT ASSIGNMENT_ACTION_ID
FROM PAY_ASSIGNMENT_ACTIONS paa
WHERE PAYROLL_ACTION_ID=c_payroll_action_id)
AND ACTION_INFORMATION_CATEGORY ='NL_WR_SWMF_SECTOR_RISK_GROUP';
SELECT fnd_number.number_to_canonical(ROUND(SUM(NVL(fnd_number.canonical_to_number(PAI1.ACTION_INFORMATION9),0)))) PrLnWgf
FROM pay_action_information PAI1, pay_action_information PAI2
WHERE PAI1.action_context_id = c_payroll_action_id
AND PAI1.action_context_id = PAI2.action_context_id
AND PAI2.action_context_type = 'PA'
AND PAI2.action_information_category = 'NL_WR_EMPLOYER_INFO'
AND PAI1.action_information_category ='NL_WR_SWMF_SECTOR_RISK_GROUP'
AND PAI1.ACTION_INFORMATION1 ='SWMF'
AND PAI1.action_information5 = PAI2.action_information10
AND PAI1.action_information6 = PAI2.action_information11;
SELECT * FROM
(SELECT
ACTION_CONTEXT_ID ER_assactid,
TAX_UNIT_ID ER_tax_unit_id,
ACTION_INFORMATION10||ACTION_INFORMATION11||ACTION_INFORMATION9 ER_name,
ACTION_INFORMATION4 ER_assignment_number,
ACTION_INFORMATION8 Sofi,
ACTION_INFORMATION5 PersNr,
ACTION_INFORMATION18 NumIV,
EFFECTIVE_DATE ER_eff_date,
ASSIGNMENT_ID ER_assignment_id,
count(*) over(partition by ACTION_INFORMATION18,ACTION_INFORMATION8) NumIVSofi,
count(*) over(partition by ACTION_INFORMATION18,ACTION_INFORMATION5) NumIVPersNr
FROM pay_action_information pai
WHERE ACTION_CONTEXT_ID IN
(SELECT ASSIGNMENT_ACTION_ID
FROM PAY_ASSIGNMENT_ACTIONS paa
WHERE PAYROLL_ACTION_ID=c_payroll_action_id)
AND ACTION_INFORMATION_CATEGORY = 'NL_WR_EMPLOYMENT_INFO'
AND ACTION_INFORMATION1=c_payroll_type)
WHERE (NumIVPersNr+NumIVSofi)>2;
SELECT
fnd_date.canonical_to_date(pai1.action_information2) start_date
,fnd_date.canonical_to_date(pai1.action_information3) end_date
FROM pay_action_information pai1,
pay_action_information pai2
,pay_assignment_actions paa
WHERE pai1.action_context_type = 'AAP'
AND pai2.action_context_type = 'AAP'
AND pai1.action_information_category = 'NL_WR_EMPLOYMENT_INFO'
AND pai2.action_information_category = 'NL_WR_EMPLOYMENT_INFO'
AND pai1.action_context_id = paa.assignment_action_id
AND pai2.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = c_payroll_action_id
AND pai1.action_information1 = 'INITIAL'
AND pai2.action_information1 <>'INITIAL'
AND pai1.action_information2 = pai2.action_information2
AND pai1.action_information3 = pai2.action_information3;
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;
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 ('CORRECTION','CORRECT')
AND pai.action_context_type = 'AAP'
AND pai.action_information17 = 'PAY'
ORDER BY 1;
SELECT NVL(org_information7,'N')
--10100628
/*distinct 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 */
--10100628
FROM hr_organization_information
WHERE organization_id = p_tax_unit_id
AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
AND org_information6 = c_payroll_type
AND p_start_date BETWEEN fnd_date.canonical_to_date(org_information1)
AND NVL(fnd_date.canonical_to_date(org_information2),fnd_date.canonical_to_date('4712/12/31'))
ORDER BY fnd_date.canonical_to_date(org_information1);
select PAYROLL_ID
from pay_payrolls_f
where
PRL_INFORMATION1 = to_char(p_tax_unit_id)
AND BUSINESS_GROUP_ID = p_bg_id
AND PERIOD_TYPE = DECODE(p_payroll_type,'MONTH','Calendar Month','WEEK','Week','LMONTH','Lunar Month')
AND p_start_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT DISTINCT START_DATE, END_DATE
from per_time_periods
where
PAYROLL_ID = p_payroll_id
--AND p_start_date BETWEEN START_DATE AND END_DATE
AND (START_DATE BETWEEN p_start_date AND p_end_date
OR
END_DATE BETWEEN p_start_date AND p_end_date
)
AND
EXISTS
(
SELECT 1
FROM hr_organization_information
WHERE organization_id = p_tax_unit_id
AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
AND org_information6 = c_payroll_type
AND START_DATE BETWEEN fnd_date.canonical_to_date(org_information1)
AND NVL(fnd_date.canonical_to_date(org_information2),fnd_date.canonical_to_date('4712/12/31'))
AND NVL(org_information7,'N') = 'Y'
)
ORDER by 1 asc; */
SELECT DISTINCT START_DATE, END_DATE
from per_time_periods
where
PAYROLL_ID = p_payroll_id
--AND p_start_date BETWEEN START_DATE AND END_DATE
AND (START_DATE BETWEEN p_start_date AND p_end_date
OR
END_DATE BETWEEN p_start_date AND p_end_date
)
AND
EXISTS
(
SELECT 1
FROM hr_organization_information
WHERE organization_id = p_tax_unit_id
AND (
(
org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
AND nvl (org_information7
,'N') = 'Y'
)
OR (
org_information_context = 'NL_ORG_WR_PREV_YR_CORRECTION'
)
)
AND org_information6 = c_payroll_type
AND start_date BETWEEN fnd_date.canonical_to_date (org_information1)
AND nvl (fnd_date.canonical_to_date (org_information2)
,fnd_date.canonical_to_date ('4712/12/31'))
)
ORDER by 1 asc;
select max(paa.assignment_action_id)
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_payrolls_f ppf
where
ppa.payroll_action_id = paa.payroll_action_id
AND ppa.business_group_id = p_bg_id
AND ppa.action_type IN ('R','Q')
AND paa.ACTION_STATUS IN ('C','S') -- 10228241
AND ppa.ACTION_STATUS = 'C'
AND ppf.payroll_id = ppa.payroll_id
AND PRL_INFORMATION1 = to_char(p_tax_unit_id)
AND ppa.EFFECTIVE_DATE BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE
and ppa.EFFECTIVE_DATE between p_pd_start_date and p_pd_end_date;
SELECT
SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION17)) PrWgf
,SUM(fnd_number.canonical_to_number(paei.AEI_INFORMATION30)) PrLnPrSectFnds
FROM per_assignment_extra_info paei
WHERE
paei.aei_information_category = 'NL_WR_PREV_CORR'
AND p_date BETWEEN fnd_date.canonical_to_date(paei.aei_information1)
AND nvl(fnd_date.canonical_to_date(paei.aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
AND EXISTS
(
SELECT 1
from
pay_assignment_actions paa
where
paa.payroll_action_id = p_payroll_action_id
and paa.action_status = 'C'
and paa.assignment_id = paei.assignment_id
);
/*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 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 (ptp.start_date BETWEEN c_start_date AND c_end_date
OR
ptp.end_date BETWEEN c_start_date AND c_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))
AND EXISTS
--10100628
(
SELECT 1
FROM per_assignment_extra_info
WHERE assignment_id = asl.assignment_id
AND aei_information_category = 'NL_WR_PREV_CORR'
AND ptp.start_date BETWEEN fnd_date.canonical_to_date(aei_information1)
AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
)
--10100628
/* ( SELECT 1
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 paa1
,pay_payroll_actions ppa1
,per_time_periods ptp
WHERE paa1.source_action_id = paa.assignment_action_id
--AND paa1.assignment_action_id = paa.assignment_action_id
AND rr1.assignment_action_id = paa1.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 ppa.effective_date between pee1.effective_start_date and pee1.effective_end_date
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND ptp.payroll_id = ppa1.payroll_id
AND rr1.start_date BETWEEN ptp.start_date and ptp.end_date
AND to_char(ptp.end_date,'RRRR') = p_prev_corr_year
) */ --10100628
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'
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,2 DESC NULLS LAST; --10100628
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 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
,fnd_number.canonical_to_number(AEI_INFORMATION3) LnLbPh
,fnd_number.canonical_to_number(AEI_INFORMATION4) LnSV
,fnd_number.canonical_to_number(AEI_INFORMATION5) LnTabBB
,fnd_number.canonical_to_number(AEI_INFORMATION6) VakBsl
,fnd_number.canonical_to_number(AEI_INFORMATION7) OpgRchtVakBsl
,fnd_number.canonical_to_number(AEI_INFORMATION8) ExtrSal
,fnd_number.canonical_to_number(AEI_INFORMATION9) OpgRchtExtrSal
,fnd_number.canonical_to_number(AEI_INFORMATION10) LnInGld
,fnd_number.canonical_to_number(AEI_INFORMATION11) WrdLn
,fnd_number.canonical_to_number(AEI_INFORMATION12) LnOwrk
,fnd_number.canonical_to_number(AEI_INFORMATION13) IngLbPh
,fnd_number.canonical_to_number(AEI_INFORMATION14) PrWAOAof
,fnd_number.canonical_to_number(AEI_INFORMATION15) PrWAOAok
,fnd_number.canonical_to_number(AEI_INFORMATION16) PrAWF
,fnd_number.canonical_to_number(AEI_INFORMATION17) PrWgf
,fnd_number.canonical_to_number(AEI_INFORMATION18) PrUFO
,fnd_number.canonical_to_number(AEI_INFORMATION19) BijdrZvw
,fnd_number.canonical_to_number(AEI_INFORMATION20) VergZvw
,fnd_number.canonical_to_number(AEI_INFORMATION21) Reisk
,fnd_number.canonical_to_number(AEI_INFORMATION22) VerrArbKrt
,fnd_number.canonical_to_number(AEI_INFORMATION23) AantSV
--,fnd_number.canonical_to_number(AEI_INFORMATION24) AantVerlU
,fnd_number.canonical_to_number(AEI_INFORMATION25) LvLpReg
,fnd_number.canonical_to_number(AEI_INFORMATION26) LvLpRegToeg
--10100628
,fnd_number.canonical_to_number(AEI_INFORMATION24) PrLnWao
,fnd_number.canonical_to_number(AEI_INFORMATION27) PrLnWaoWga
,fnd_number.canonical_to_number(AEI_INFORMATION28) PrLnWwAwf
,fnd_number.canonical_to_number(AEI_INFORMATION29) PrLnUfo
,fnd_number.canonical_to_number(AEI_INFORMATION30) PrLnPrSectFnds
--10100628
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND aei_information_category = 'NL_WR_PREV_CORR'
AND p_date BETWEEN fnd_date.canonical_to_date(aei_information1)
AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
ORDER BY 1 ASC;
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',
'NL_MEI','NL_WMR','NL_TTD'); /*LC 2010 and LC 2013*/
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',
'NL_MEI','NL_WMR','NL_TTD') /*LC 2010 and LC 2013*/
--9257875
/* AND p_end_date BETWEEN fnd_date.canonical_to_date(aei_information1)
AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy')) */
--9257875
--10377795 START. Commented above code.
AND fnd_date.date_to_canonical (p_end_date) BETWEEN aei_information1
AND nvl(aei_information2,fnd_date.date_to_canonical(to_date('31-12-4712','dd-mm-yyyy')))
--10377795 END
ORDER BY 1 DESC;
SELECT aei_information3 info1
FROM per_assignment_extra_info
WHERE assignment_id = c_assignment_id
AND aei_information_category = 'NL_LHI'
AND p_end_date BETWEEN fnd_date.canonical_to_date(aei_information1)
AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
AND aei_information3 not in ('0','1','2','3','4');
select papf.date_of_birth, TRUNC(MONTHS_BETWEEN(p_end_date,papf.date_of_birth)/12) Age
from
per_all_people_f papf,
per_all_assignments_f paaf
where paaf.assignment_id = c_assignment_id
AND papf.person_id = paaf.person_id
AND p_end_date between paaf.effective_start_date and paaf.effective_end_date
AND p_end_date between papf.effective_start_date and papf.effective_end_date
AND papf.date_of_birth is not null;
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 IN ('C','S') -- 10228241
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 --11731857
fnd_number.canonical_to_number(substr(max(lpad(NVL(paa.action_sequence,0),15,'0')||paa.assignment_action_id),16)) assignment_action_id --11731857
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 IN ('C','S') -- 10228241
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 aei_information1 NUMIV_OVERRIDE
from per_assignment_extra_info
where assignment_id = p_asg_id
and aei_information_category = 'NL_NUMIV_OVERRIDE';
SELECT prrv.result_value
FROM pay_run_result_values prrv
,pay_input_values_f piv
,pay_element_types_f pet
,pay_run_results prr
WHERE pet.element_name = 'Small Job Indicator'
AND pet.element_type_id = piv.element_type_id
AND piv.name = 'Exempt Small Jobs'
AND pet.legislation_code = 'NL'
AND piv.legislation_code = 'NL'
AND prrv.input_value_id = piv.input_value_id
AND prr.run_result_id = prrv.run_result_id
AND prr.element_type_id = pet.element_type_id
AND prr.assignment_action_id = c_assignment_action_id
AND prr.status in ('P','PA')
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;
SELECT distinct asl.assignment_id assignment_id
FROM per_all_assignments_f asl
,per_all_assignments_f asl2
,pay_all_payrolls_f ppf
,pay_payroll_actions ppa
,pay_assignment_actions paa
,per_time_periods ptp
WHERE asl.person_id = asl2.person_id
AND asl2.assignment_id = c_assg_id
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.business_group_id = c_business_group_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;
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 distinct ptp.start_date, ptp.end_date
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
where
ppa.action_type in ('R', 'Q')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_asg_id
and ppa.EFFECTIVE_DATE between p_start_date and p_end_date
and ptp.TIME_PERIOD_ID = ppa.TIME_PERIOD_ID
and ptp.PAYROLL_ID = ppa.payroll_id
AND ppa.action_type IN ('R','Q')
AND paa.ACTION_STATUS = 'C'
AND ppa.ACTION_STATUS = 'C'
AND paa.source_action_id IS NOT NULL
AND EXISTS
(
SELECT 1
FROM per_assignment_extra_info
WHERE assignment_id = paa.assignment_id
AND aei_information_category = 'NL_WR_PREV_CORR'
AND ptp.start_date BETWEEN fnd_date.canonical_to_date(aei_information1)
AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
)
order by ptp.start_date Asc;
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
,sum(fnd_number.canonical_to_number(pai.action_information30)) sum30
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 IN ('C','S') -- 10228241
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
fnd_number.canonical_to_number(substr(max(lpad(NVL(paa.action_sequence,0),15,'0')||paa.assignment_action_id),16)) assignment_action_id --11731857
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 IN ('C','S') -- 10228241
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 prrv.result_value
FROM pay_run_result_values prrv
,pay_input_values_f piv
,pay_element_types_f pet
,pay_run_results prr
WHERE pet.element_name = 'Small Job Indicator'
AND pet.element_type_id = piv.element_type_id
AND piv.name = 'Exempt Small Jobs'
AND pet.legislation_code = 'NL'
AND piv.legislation_code = 'NL'
AND prrv.input_value_id = piv.input_value_id
AND prr.run_result_id = prrv.run_result_id
AND prr.element_type_id = pet.element_type_id
AND prr.assignment_action_id = c_assignment_action_id
AND prr.status in ('P','PA')
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;
SELECT distinct asl.assignment_id assignment_id
FROM per_all_assignments_f asl
,per_all_assignments_f asl2
,pay_payroll_actions ppa
,pay_assignment_actions paa
,per_time_periods ptp
WHERE asl.person_id = asl2.person_id
AND asl2.assignment_id = c_assg_id
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.business_group_id = c_business_group_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;
select aei_information1 NUMIV_OVERRIDE
from per_assignment_extra_info
where assignment_id = p_asg_id
and aei_information_category = 'NL_NUMIV_OVERRIDE';
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 max(paa.assignment_action_id) assignment_action_id, ptp.start_date, ptp.end_date
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
where
ppa.action_type in ('R', 'Q')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.EFFECTIVE_DATE between p_start_date and p_end_date
and ptp.TIME_PERIOD_ID = ppa.TIME_PERIOD_ID
and ptp.PAYROLL_ID = ppa.payroll_id
AND ppa.action_type IN ('R','Q')
AND paa.ACTION_STATUS = 'C'
AND ppa.ACTION_STATUS = 'C'
AND paa.source_action_id IS NOT NULL
AND EXISTS
--10100628
(
SELECT 1
FROM per_assignment_extra_info
WHERE assignment_id = paa.assignment_id
AND aei_information_category = 'NL_WR_PREV_CORR'
AND ptp.start_date BETWEEN fnd_date.canonical_to_date(aei_information1)
AND nvl(fnd_date.canonical_to_date(aei_information2),to_date('31-12-4712','dd-mm-yyyy'))
)
--10100628
/*
( SELECT 1
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 paa1
,pay_payroll_actions ppa1
,per_time_periods ptp
WHERE --paa1.source_action_id = paa.assignment_action_id
paa1.assignment_action_id = paa.assignment_action_id
AND rr1.assignment_action_id = paa1.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 ppa.effective_date between pee1.effective_start_date and pee1.effective_end_date
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND ptp.payroll_id = ppa1.payroll_id
AND rr1.start_date BETWEEN ptp.start_date and ptp.end_date
AND to_char(ptp.end_date,'RRRR') = p_prev_corr_year
)
*/
--10100628
group by ptp.start_date, ptp.end_date
order by ptp.start_date;
SELECT DISTINCT ptp.start_date
,ptp.end_date
FROM pay_run_results rr1
,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'
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 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');
SELECT sum (fnd_number.canonical_to_number(nvl (action_information6,0))) taxableincome
FROM pay_action_information info
,pay_payroll_actions generator
,pay_payroll_actions datalock
WHERE info.action_context_id = generator.payroll_action_id
AND info.action_information2 = 'TotLnLbPh'
AND datalock.report_type = 'NL_WAGES_REP_LOCK'
AND datalock.business_group_id = c_bg_id
AND datalock.action_status = 'C'
AND to_char (generator.effective_date,'YYYY') = c_year
AND pay_nl_wage_report_pkg.get_parameters (generator.payroll_action_id
,'Legal_Employer') = c_tax_unit_id
AND pay_nl_wage_report_pkg.get_parameters (datalock.payroll_action_id
,'REQUEST_ID') = generator.payroll_action_id ;