[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Decode(rin.seq_num,1,'00',
2,'01',
3,'02',
4,'04',
5,'05',
7,'08',
8,'09',
10,'12',
12,'20',
14,'21',
16,'22',
17,'30',
19,'31',
21,'40',
23,'41',
26,'94',
27,'95',
28,'96',
29,'97',
30,'99',
'~') rec_num,
rin.seq_num,
rin.hide_flag,
rcd.ext_rcd_id,
rcd.rcd_type_cd
FROM ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id = Ben_Ext_Thread.g_ext_dfn_id
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
ORDER BY rin.seq_num;
SELECT rcd.ext_rcd_id
FROM ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id = Ben_Ext_Thread.g_ext_dfn_id -- The extract executing currently
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = c_seq;
SELECT paf.person_id
,paf.organization_id
,paf.assignment_type
,paf.effective_start_date
,paf.effective_end_date
,ast.user_status
,Hr_General.decode_lookup
('EMP_CAT',
paf.employment_category) employment_category
,pps.date_start
,pps.actual_termination_date
,paf.payroll_id
,'ER'
,per.employee_number
,paf.assignment_sequence
,per.national_identifier
,per.last_name
,per.per_information1
,per.pre_name_adjunct
,per.sex
,per.date_of_birth
,'PLN'
,'PIX'
,per.per_information14
,per.marital_status
,paf.primary_flag
FROM per_all_assignments_f paf,
per_all_people_f per,
per_periods_of_service pps,
per_assignment_status_types ast
WHERE paf.assignment_id = c_assignment_id
AND paf.person_id = per.person_id
AND pps.period_of_service_id(+) = paf.period_of_service_id
AND ast.assignment_status_type_id = paf.assignment_status_type_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND c_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND paf.business_group_id = c_business_group_id
AND per.business_group_id = c_business_group_id;
SELECT paf.organization_id
,paf.payroll_id
FROM per_all_assignments_f paf,
per_all_people_f per
WHERE paf.assignment_id <> c_assignment_id
AND paf.person_id = c_person_id
AND paf.person_id = per.person_id
AND c_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND c_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND paf.business_group_id = c_business_group_id
AND per.business_group_id = c_business_group_id;
SELECT db.defined_balance_id
FROM pay_balance_types pbt
,pay_defined_balances db
,pay_balance_dimensions bd
WHERE pbt.balance_name = c_balance_name
AND pbt.balance_type_id = db.balance_type_id
AND bd.balance_dimension_id = db.balance_dimension_id
AND bd.dimension_name = c_dimension_name
AND (pbt.business_group_id = c_business_group_id OR
pbt.legislation_code = g_legislation_code)
AND (db.business_group_id = pbt.business_group_id OR
db.legislation_code = g_legislation_code);
SELECT db.defined_balance_id
FROM pay_defined_balances db
,pay_balance_dimensions bd
WHERE db.balance_type_id = c_balance_type_id
AND bd.balance_dimension_id = db.balance_dimension_id
AND bd.dimension_name = c_dimension_name
AND (db.business_group_id = c_business_group_id OR
db.legislation_code = g_legislation_code);
SELECT db.defined_balance_id
FROM pay_defined_balances db
WHERE db.balance_type_id = c_balance_type_id
AND db.balance_dimension_id = c_balance_dimension_id
AND (db.business_group_id = c_business_group_id OR
db.legislation_code = g_legislation_code);
SELECT paa.assignment_action_id
,ppa.effective_date
,ppa.action_type
,ppa.date_earned
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND ppa.action_type IN ('Q','R')
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = Nvl(c_payroll_id,ppa.payroll_id)
AND ppa.consolidation_set_id = Nvl(c_con_set_id,ppa.consolidation_set_id)
AND ppa.effective_date BETWEEN c_start_date
AND c_end_date
AND source_action_id IS NOT NULL
ORDER BY ppa.effective_date;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.action_status = 'C'
AND ppa.action_type IN ('Q','R')
AND paa.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = Nvl(c_payroll_id,ppa.payroll_id)
AND ppa.consolidation_set_id = Nvl(c_con_set_id,ppa.consolidation_set_id)
AND source_action_id IS NOT NULL
AND ppa.effective_date BETWEEN c_start_date
AND c_end_date;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.action_status = 'C'
AND ppa.action_type IN ('Q','R')
AND paa.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND source_action_id IS NOT NULL
AND ppa.date_earned BETWEEN c_start_de
AND c_end_de
AND ppa.effective_date BETWEEN g_extract_params(c_bg_id).extract_start_date
AND g_extract_params(c_bg_id).extract_end_date;
SELECT rcd.ext_rcd_id
FROM ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id = Ben_Ext_Thread.g_ext_dfn_id -- The extract
AND rin.ext_file_id = dfn.ext_file_id
AND rin.hide_flag = c_hide_flag -- Y=Hidden, N=Not Hidden
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND rcd.rcd_type_cd = c_rcd_type_cd; -- D=Detail,H=Header,F=Footer
SELECT rcd.ext_rcd_id
FROM ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id = Ben_Ext_Thread.g_ext_dfn_id
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND rcd.rcd_type_cd = c_rcd_type_cd
ORDER BY rin.seq_num;
SELECT *
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND dtl.person_id = c_person_id
AND dtl.ext_rcd_id = c_ext_dtl_rcd_id;
SELECT pbt.balance_type_id
FROM pay_balance_types pbt
WHERE pbt.balance_name = c_balance_name
AND (pbt.business_group_id = c_business_group_id
OR
pbt.legislation_code = c_legislation_code);
SELECT NAME
FROM hr_all_organization_units
WHERE organization_id = c_org_id;
SELECT asg.person_id
,asg.organization_id
,asg.assignment_type
,asg.effective_start_date
,asg.effective_end_date
,'NO'
,asg.assignment_id
FROM per_all_assignments_f asg
WHERE asg.person_id = c_person_id
AND asg.assignment_id <> c_primary_assignment_id
AND asg.assignment_type ='E'
AND (( c_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
)
OR
( asg.effective_end_date =
(SELECT Max(asx.effective_end_date)
FROM per_all_assignments_f asx
WHERE asx.assignment_id = asg.assignment_id
AND asx.person_id = c_person_id
AND asx.assignment_type = 'E'
AND ((asx.effective_end_date BETWEEN c_extract_start_date
AND c_extract_end_date)
OR
(asx.effective_start_date BETWEEN c_extract_start_date
AND c_extract_end_date)
)
)
)
)
ORDER BY asg.effective_start_date ASC;
SELECT pet.element_type_id
,pei.eei_information9||' Employee Pension Basis' bal_name
,pei.eei_information12 sub_cat
,Decode (pei.eei_information12,'AAOP','AP','IPBW_H','IH','IPBW_L','IL'
,'FPB','FB','FPU_C','FO','OP') code
,-1 defined_bal_id
,pei.eei_information18 cy_retro_element_id
,pei.eei_information19 py_retro_element_id
,(SELECT retro_element_type_id
FROM pay_element_span_usages pesu,
pay_retro_component_usages prcu
WHERE prcu.retro_component_usage_id = pesu.retro_component_usage_id
AND retro_component_id = ( SELECT retro_component_id
FROM pay_retro_components
WHERE legislation_code = 'NL'
AND short_name = 'Adjustment'
AND component_name = 'Adjustment')
AND creator_type = 'ET'
AND creator_id = pet.element_type_id) py_cy_adj_retro_element_id
FROM pay_element_type_extra_info pei,
pay_element_types_f pet
WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
-- AND c_effective_date BETWEEN To_Date(pei.eei_information10,'DD/MM/RRRR') AND
-- To_Date(pei.eei_information11,'DD/MM/RRRR')
AND c_effective_date BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND pet.element_type_id = pei.element_type_id
AND pet.business_group_id = c_bg_id
AND pei.EEI_INFORMATION12 IN ('OPNP','IPBW_H','IPBW_L','AAOP');
SELECT pet.element_type_id
,pei.eei_information9||' Employee Pension Basis' bal_name
,pei.eei_information12 sub_cat
,'02' code
,-1 defined_bal_id
,pei.eei_information18 cy_retro_element_id
,pei.eei_information19 py_retro_element_id
FROM pay_element_type_extra_info pei,
pay_element_types_f pet
WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND c_effective_date BETWEEN To_Date(pei.eei_information10,'DD/MM/RRRR') AND
To_Date(pei.eei_information11,'DD/MM/RRRR')
AND c_effective_date BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND pet.element_type_id = pei.element_type_id
AND pet.business_group_id = c_bg_id
AND pei.EEI_INFORMATION12 IN ('IPAP');
SELECT pet.element_type_id
,pei.eei_information9||' Employee Pension Basis' bal_name
,pei.eei_information12 sub_cat
,'99' code
,-1 defined_bal_id
,pei.eei_information18 cy_retro_element_id
,pei.eei_information19 py_retro_element_id
FROM pay_element_type_extra_info pei,
pay_element_types_f pet
WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND c_effective_date BETWEEN To_Date(pei.eei_information10,'DD/MM/RRRR') AND
To_Date(pei.eei_information11,'DD/MM/RRRR')
AND c_effective_date BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND pet.element_type_id = pei.element_type_id
AND pet.business_group_id = c_bg_id
AND pei.EEI_INFORMATION12 IN ('FUR_S');
SELECT old_val1,new_val1,ext_chg_evt_log_id
FROM ben_ext_chg_evt_log bec
WHERE chg_evt_cd = 'DAT'
AND person_id = g_person_id
AND bec.business_group_id = p_business_group_id
AND fnd_date.canonical_to_date(prmtr_09) BETWEEN
g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND EXISTS(SELECT 1
FROM per_periods_of_service pps
,per_all_assignments_f asg
WHERE pps.person_id = g_person_id
AND asg.assignment_id = p_assignment_id
AND asg.period_of_service_id = pps.period_of_service_id
AND (pps.actual_termination_date IS NULL
AND assignment_status_type_id IN
(SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'ACTIVE_ASSIGN'
AND active_flag = 'Y'))
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date )
ORDER by bec.ext_chg_evt_log_id desc;
SELECT min(effective_start_date) - 1 term_date
,period_of_service_id
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND effective_start_date <= g_extract_params(p_business_group_id).extract_end_date
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y')
group by period_of_service_id
UNION
--
-- Get the dates for any ended assignments. Note that this is for sec
-- assignments only.
--
SELECT max(effective_end_date)
,period_of_service_id
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND asg.primary_flag = 'N'
AND effective_end_date <= g_extract_params(p_business_group_id).extract_end_date
AND NOT EXISTS( SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = p_assignment_id
AND asg1.effective_start_date = asg.effective_end_date + 1
AND asg.assignment_id = asg1.assignment_id )
AND NOT EXISTS( SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = p_assignment_id
AND asg1.effective_start_date < asg.effective_start_date
AND asg.assignment_id = asg1.assignment_id
AND asg1.assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'))
group by period_of_service_id
;
SELECT old_val1
,to_char(pps.actual_termination_date,'DD/MM/YYYY') term_date
,ext_chg_evt_log_id
,fnd_number.canonical_to_number(prmtr_01)
FROM ben_ext_chg_evt_log bec
,per_periods_of_service pps
,per_all_assignments_f asg
WHERE bec.chg_evt_cd = 'AAT'
AND bec.person_id = g_person_id
AND bec.business_group_id = p_business_group_id
AND fnd_date.canonical_to_date(bec.prmtr_09)
BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND pps.person_id = g_person_id
AND asg.assignment_id = p_assignment_id
AND asg.period_of_service_id = pps.period_of_service_id
AND pps.actual_termination_date IS NOT NULL
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
UNION
SELECT
NULL
,term_date
,9999999999 - rownum
,period_of_service_id FROM (
SELECT NULL
,to_char(effective_start_date - 1,'DD/MM/YYYY') term_date
,9999999999
,period_of_service_id
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y')
AND effective_start_date BETWEEN
g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND NOT EXISTS ( SELECT 1
FROM ben_ext_chg_evt_log bec
WHERE chg_evt_cd = 'AAT'
AND person_id = g_person_id
AND bec.business_group_id = p_business_group_id
AND fnd_date.canonical_to_date(prmtr_09)
BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date )
AND NOT EXISTS( SELECT 1
FROM per_all_assignments_f asg1
WHERE assignment_id = p_assignment_id
AND effective_start_date <
g_extract_params(p_business_group_id).extract_start_date
AND asg.assignment_id = asg1.assignment_id
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'))
ORDER BY effective_start_date )
UNION
SELECT NULL
,to_char(effective_end_date,'DD/MM/YYYY')
,9999999999
,period_of_service_id
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND asg.primary_flag = 'N'
AND effective_end_date BETWEEN
g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND NOT EXISTS( SELECT 1
FROM per_all_assignments_f asg1
WHERE assignment_id = p_assignment_id
AND effective_start_date = asg.effective_end_date + 1
AND asg.assignment_id = asg1.assignment_id )
AND NOT EXISTS( SELECT 1
FROM per_all_assignments_f asg1
WHERE assignment_id = p_assignment_id
AND effective_start_date <
g_extract_params(p_business_group_id).extract_start_date
AND asg.assignment_id = asg1.assignment_id
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'))
AND NOT EXISTS ( SELECT 1
FROM ben_ext_chg_evt_log bec
WHERE chg_evt_cd = 'AAT'
AND person_id = g_person_id
AND bec.business_group_id = p_business_group_id
AND fnd_date.canonical_to_date(prmtr_09)
BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date )
--
-- Reporting Retro Termination of sec asg
--
UNION
SELECT NULL
,to_char(min(effective_start_date) - 1,'DD/MM/YYYY')
,9999999999
,period_of_service_id
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND asg.primary_flag = 'N'
AND effective_start_date < g_extract_params(p_business_group_id).extract_start_date
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y')
AND NOT EXISTS( SELECT 1
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt)< g_extract_params(p_business_group_id).extract_start_date
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 5
AND val_04 = c_asg_seq_num
AND val_07 <> '00000000')
group by period_of_service_id
ORDER by 3 desc;
SELECT LEAST(fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')),125) ptp
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.assignment_id = c_asg_id
AND target.enabled_flag = 'Y'
AND trunc(c_eff_dt) BETWEEN asg.effective_start_date AND
asg.effective_end_date;
SELECT nvl(hourly_salaried_code,'H') hourly_salaried_code
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.assignment_id = c_asg_id
AND target.enabled_flag = 'Y'
AND trunc(c_eff_dt) BETWEEN asg.effective_start_date AND
asg.effective_end_date;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = c_element_type_id
AND name = 'ABP Employee Pension Basis';
SELECT pei.eei_information12 sub_cat
,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) date_earned
,Decode (pei.eei_information12,'OPNP_65',5,'OPNP_W25',6,'OPNP_W50',7,
'VSG',9,'FPU_E',4,'FPU_R',2,'FPU_S',1,'FPU_T',3,'FPU_B',1
,'PPP',11) code
,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) amount
,pty.ee_contribution_bal_type_id
,pty.er_contribution_bal_type_id
FROM pay_element_type_extra_info pei,
pay_element_types_f pet,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_entry_values_f peev,
pay_input_values_f pivf,
pqp_pension_types_f pty
WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND c_effective_date BETWEEN To_Date(pei.eei_information10,'DD/MM/RRRR') AND
To_Date(pei.eei_information11,'DD/MM/RRRR')
AND c_effective_date BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND c_effective_date BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date AND
peev.effective_end_date
AND c_effective_date BETWEEN pty.effective_start_date AND
pty.effective_end_date
AND c_effective_date BETWEEN pivf.effective_start_date AND
pivf.effective_end_date
AND (to_number(pei.eei_information18) = pet.element_type_id
OR to_number(pei.eei_information19) = pet.element_type_id
OR to_number(pei.eei_information20) = pet.element_type_id
OR to_number(pei.eei_information21) = pet.element_type_id
OR pet.element_type_id IN (SELECT retro_element_type_id
FROM pay_element_span_usages pesu,
pay_retro_component_usages prcu
WHERE prcu.retro_component_usage_id = pesu.retro_component_usage_id
AND retro_component_id = ( SELECT retro_component_id
FROM pay_retro_components
WHERE legislation_code = 'NL'
AND short_name = 'Adjustment'
AND component_name = 'Adjustment')
AND creator_type = 'ET'
AND (creator_id = pei.element_type_id OR
creator_id IN (SELECT element_type_id
FROM pay_element_types_f pet1
WHERE pet1.element_name = pei.eei_information9 || ' ABP Employer Pension Contribution'
AND pet1.business_group_id = pet.business_group_id))) )
AND pelf.element_type_id = pet.element_type_id
AND pivf.element_type_id = pet.element_type_id
AND pivf.name = 'Pay Value'
AND peef.element_link_id = pelf.element_link_id
AND peev.input_value_id = pivf.input_value_id
AND peev.element_entry_id = peef.element_entry_id
AND pet.business_group_id = c_bg_id
AND peef.assignment_id = c_asg_id
AND pei.EEI_INFORMATION12 IN ('OPNP_65','OPNP_W25','OPNP_W50','PPP','FPU_B',
'VSG','FPU_E','FPU_R','FPU_S','FPU_T')
AND pty.pension_type_id = to_number(pei.eei_information2)
GROUP BY pei.eei_information12,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id)
,pty.ee_contribution_bal_type_id,pty.er_contribution_bal_type_id
UNION
SELECT pension_sub_category sub_cat
,c_effective_date date_earned
,Decode (pension_sub_category,'OPNP_65',5,'OPNP_W25',6,'OPNP_W50',7,'PPP',11,
'VSG',9,'FPU_E',4,'FPU_R',2,'FPU_S',1,'FPU_T',3,'FPU_B',1) code
,-999999 amount
,ee_contribution_bal_type_id
,er_contribution_bal_type_id
FROM pqp_pension_types_f pty
WHERE pension_sub_category IN ('OPNP_65','OPNP_W25','OPNP_W50','PPP',
'VSG','FPU_E','FPU_R','FPU_S','FPU_T','FPU_B')
AND business_group_id = c_bg_id
AND c_effective_date BETWEEN pty.effective_start_date AND
pty.effective_end_date
GROUP BY pension_sub_category,c_effective_date
,ee_contribution_bal_type_id
,er_contribution_bal_type_id;
SELECT pei.eei_information12 sub_cat
,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) date_earned
,9 code
,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) amount
,pty.ee_contribution_bal_type_id
,pty.er_contribution_bal_type_id
FROM pay_element_type_extra_info pei,
pay_element_types_f pet,
pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_entry_values_f peev,
pay_input_values_f pivf,
pqp_pension_types_f pty
WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND c_effective_date BETWEEN To_Date(pei.eei_information10,'DD/MM/RRRR') AND
To_Date(pei.eei_information11,'DD/MM/RRRR')
AND c_effective_date BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND c_effective_date BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date AND
peev.effective_end_date
AND c_effective_date BETWEEN pivf.effective_start_date AND
pivf.effective_end_date
AND c_effective_date BETWEEN pty.effective_start_date AND
pty.effective_end_date
AND (to_number(pei.eei_information18) = pet.element_type_id
OR to_number(pei.eei_information19) = pet.element_type_id
OR to_number(pei.eei_information20) = pet.element_type_id
OR to_number(pei.eei_information21) = pet.element_type_id)
AND pelf.element_type_id = pet.element_type_id
AND pivf.element_type_id = pet.element_type_id
AND pivf.name = 'Pay Value'
AND peef.element_link_id = pelf.element_link_id
AND peev.input_value_id = pivf.input_value_id
AND peev.element_entry_id = peef.element_entry_id
AND pet.business_group_id = c_bg_id
AND peef.assignment_id = c_asg_id
AND pei.EEI_INFORMATION12 IN ('FUR_S')
AND pty.pension_type_id = to_number(pei.eei_information2)
GROUP BY pei.eei_information12,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id)
,pty.ee_contribution_bal_type_id,pty.er_contribution_bal_type_id
UNION
SELECT pension_sub_category sub_cat
,c_effective_date date_earned
,9 code
,-999999 amount
,ee_contribution_bal_type_id
,er_contribution_bal_type_id
FROM pqp_pension_types_f pty
WHERE pension_sub_category IN ('FUR_S')
AND business_group_id = c_bg_id
AND c_effective_date BETWEEN pty.effective_start_date AND
pty.effective_end_date
GROUP BY pension_sub_category,c_effective_date
,ee_contribution_bal_type_id,er_contribution_bal_type_id;
SELECT element_entry_id
FROM pay_run_results prr
WHERE prr.assignment_action_id = c_assignment_action_id
AND prr.element_type_id = c_element_type_id
ORDER BY element_entry_id;
SELECT element_entry_id
FROM pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN c_start_date
AND c_end_date
AND paa.assignment_id = c_assignment_id
AND prr.element_type_id = c_element_type_id
ORDER BY element_entry_id;
SELECT piv.input_value_id
,pet.element_type_id
FROM pay_input_values_f piv
,pay_element_types_f pet
WHERE piv.name = c_input_value_name
AND piv.element_type_id = pet.element_type_id
AND pet.element_name = c_element_name;
SELECT fnd_number.canonical_to_number(nvl(screen_entry_value,'0'))
FROM pay_element_entry_values_f
WHERE element_entry_id = c_element_entry_id
AND input_value_id = c_input_value_id;
SELECT nvl(screen_entry_value,' ')
FROM pay_element_entry_values_f
WHERE element_entry_id = c_element_entry_id
AND input_value_id = c_input_value_id;
SELECT 1
FROM pay_element_entry_values_f
WHERE input_value_id = c_input_value_id
AND screen_entry_value IS NOT NULL
AND element_entry_id IN
(SELECT element_entry_id
FROM pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN c_start_date
AND c_end_date
AND paa.assignment_id = c_assignment_id
AND prr.element_type_id = c_element_type_id
);
SELECT ptp.start_date,ptp.end_date
FROM per_time_periods ptp
,per_all_assignments_f paa
WHERE paa.assignment_id = c_assignment_id
AND ptp.payroll_id = paa.payroll_id
AND pay_paywsmee_pkg.get_original_date_earned(c_element_entry_id)
BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND pay_paywsmee_pkg.get_original_date_earned(c_element_entry_id)
BETWEEN ptp.start_date
AND ptp.end_date;
SELECT COUNT(*)
FROM per_assignments_f asg
,per_assignment_status_types past
,hr_soft_coding_keyflex target
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date BETWEEN c_period_start
AND c_period_end
AND asg.assignment_id = c_asg_id
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND target.enabled_flag = 'Y'
AND fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) > 0;
SELECT asg.effective_start_date Start_Date
,asg.effective_end_date End_Date
,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp
FROM per_assignments_f asg
,per_assignment_status_types past
,hr_soft_coding_keyflex target
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date BETWEEN c_period_start
AND c_period_end
AND asg.assignment_id = c_asg_id
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND target.enabled_flag = 'Y';
SELECT to_date('1/'||to_char(pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id),'MM/YYYY'),'DD/MM/YYYY') start_date
,add_months(to_date('1/'||to_char(pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id),'MM/YYYY'),'DD/MM/YYYY'),1) - 1 end_date
,fnd_number.canonical_to_number(peev.screen_entry_value) ptp
FROM pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_entry_values_f peev
WHERE c_effective_date BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date AND
peev.effective_end_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_input_val_id
AND peef.assignment_id = c_asg_id
AND peev.screen_entry_value IS NOT NULL
order by start_date;
SELECT 1
FROM pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_entry_values_f peev
WHERE c_effective_date BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date AND
peev.effective_end_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_input_val_id
AND peef.assignment_id = c_asg_id
AND peev.screen_entry_value IS NOT NULL;
SELECT to_date('1/'||to_char(ppa.date_earned,'MM/YYYY'),'DD/MM/YYYY') start_date
,add_months(to_date('1/'||to_char(ppa.date_earned,'MM/YYYY'),'DD/MM/YYYY'),1) - 1 end_date
,fnd_number.canonical_to_number(prrv.result_value) ptp, 'Y' Yes
FROM pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_entry_values_f peev,
pay_run_results prr,
pay_run_result_values prrv,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.date_earned BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND ppa.date_earned BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND ppa.date_earned BETWEEN peev.effective_start_date AND
peev.effective_end_date
AND c_effective_date BETWEEN to_date('1/'||to_char(ppa.effective_date,'MM/YYYY'),'DD/MM/YYYY') AND
add_months(to_date('1/'||to_char(ppa.effective_date,'MM/YYYY'),'DD/MM/YYYY'),1) - 1
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND prr.element_entry_id = peef.element_entry_id
AND prr.element_type_id = pelf.element_type_id
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = peev.input_value_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_input_val_id
AND peef.assignment_id = c_asg_id
AND fnd_number.canonical_to_number(prrv.result_value) > 0 ;
SELECT substr(fnd_date.date_to_canonical(
trunc(pay_paywsmee_pkg.get_original_date_earned(c_element_entry_id))
),1,10)
FROM dual;
SELECT Userenv('SESSIONID') INTO l_session_id FROM dual;
DELETE FROM pay_us_rpt_totals
WHERE organization_name = 'NL ABP Pension Extracts';
hr_utility.set_location('inserting into rpt totals : '||p_business_group_id,20);
INSERT INTO pay_us_rpt_totals
(session_id -- Session id
,organization_name -- Concurrent Program Name
,business_group_id -- Business Group
,tax_unit_id -- Concurrent Request Id
,value1 -- Extract Definition Id
,value2 -- Payroll Id
,value3 -- Consolidation Set
,value4 -- Organization Id
,value5 -- Sort Order --9278285
,value6 --
,attribute1 --
,attribute2 --
,attribute3 -- Extract Start Date
,attribute4 -- Extract End Date
,attribute5 -- Extract Record 01 Flag
)
VALUES
(l_session_id
,'NL ABP Pension Extracts'
,p_business_group_id
,g_conc_request_id
,p_ext_dfn_id
,p_payroll_id
,p_consolidation_set
,p_org_id
,p_sort_position --9278285
,NULL
,NULL
,NULL
,p_start_date
,p_end_date
,p_extract_rec_01
);
PROCEDURE Update_Record_Values
(p_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
,p_ext_data_element_name IN ben_ext_data_elmt.NAME%TYPE
,p_data_element_value IN ben_ext_rslt_dtl.val_01%TYPE
,p_data_ele_seqnum IN Number
,p_ext_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE
) IS
CURSOR csr_seqnum (c_ext_rcd_id IN ben_ext_rcd.ext_rcd_id%TYPE
,c_ext_data_element_name IN ben_ext_data_elmt.NAME%TYPE
) IS
SELECT der.ext_data_elmt_id,
der.seq_num,
ede.NAME
FROM ben_ext_data_elmt_in_rcd der
,ben_ext_data_elmt ede
WHERE der.ext_rcd_id = c_ext_rcd_id
AND ede.ext_data_elmt_id = der.ext_data_elmt_id
AND ede.NAME LIKE '%'|| c_ext_data_element_name
ORDER BY seq_num;
l_proc_name Varchar2(150):= g_proc_name||'Update_Record_Values';
END Update_Record_Values;
SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
INSERT INTO ben_ext_rslt_dtl
(EXT_RSLT_DTL_ID
,EXT_RSLT_ID
,BUSINESS_GROUP_ID
,EXT_RCD_ID
,PERSON_ID
,VAL_01
,VAL_02
,VAL_03
,VAL_04
,VAL_05
,VAL_06
,VAL_07
,VAL_08
,VAL_09
,VAL_10
,VAL_11
,VAL_12
,VAL_13
,VAL_14
,VAL_15
,VAL_16
,VAL_17
,VAL_19
,VAL_18
,VAL_20
,VAL_21
,VAL_22
,VAL_23
,VAL_24
,VAL_25
,VAL_26
,VAL_27
,VAL_28
,VAL_29
,VAL_30
,VAL_31
,VAL_32
,VAL_33
,VAL_34
,VAL_35
,VAL_36
,VAL_37
,VAL_38
,VAL_39
,VAL_40
,VAL_41
,VAL_42
,VAL_43
,VAL_44
,VAL_45
,VAL_46
,VAL_47
,VAL_48
,VAL_49
,VAL_50
,VAL_51
,VAL_52
,VAL_53
,VAL_54
,VAL_55
,VAL_56
,VAL_57
,VAL_58
,VAL_59
,VAL_60
,VAL_61
,VAL_62
,VAL_63
,VAL_64
,VAL_65
,VAL_66
,VAL_67
,VAL_68
,VAL_69
,VAL_70
,VAL_71
,VAL_72
,VAL_73
,VAL_74
,VAL_75
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,OBJECT_VERSION_NUMBER
,PRMY_SORT_VAL
,SCND_SORT_VAL
,THRD_SORT_VAL
,TRANS_SEQ_NUM
,RCRD_SEQ_NUM
)
VALUES
(p_dtl_rec.EXT_RSLT_DTL_ID
,p_dtl_rec.EXT_RSLT_ID
,p_dtl_rec.BUSINESS_GROUP_ID
,p_dtl_rec.EXT_RCD_ID
,p_dtl_rec.PERSON_ID
,p_dtl_rec.VAL_01
,p_dtl_rec.VAL_02
,p_dtl_rec.VAL_03
,p_dtl_rec.VAL_04
,p_dtl_rec.VAL_05
,p_dtl_rec.VAL_06
,p_dtl_rec.VAL_07
,p_dtl_rec.VAL_08
,p_dtl_rec.VAL_09
,p_dtl_rec.VAL_10
,p_dtl_rec.VAL_11
,p_dtl_rec.VAL_12
,p_dtl_rec.VAL_13
,p_dtl_rec.VAL_14
,p_dtl_rec.VAL_15
,p_dtl_rec.VAL_16
,p_dtl_rec.VAL_17
,p_dtl_rec.VAL_19
,p_dtl_rec.VAL_18
,p_dtl_rec.VAL_20
,p_dtl_rec.VAL_21
,p_dtl_rec.VAL_22
,p_dtl_rec.VAL_23
,p_dtl_rec.VAL_24
,p_dtl_rec.VAL_25
,p_dtl_rec.VAL_26
,p_dtl_rec.VAL_27
,p_dtl_rec.VAL_28
,p_dtl_rec.VAL_29
,p_dtl_rec.VAL_30
,p_dtl_rec.VAL_31
,p_dtl_rec.VAL_32
,p_dtl_rec.VAL_33
,p_dtl_rec.VAL_34
,p_dtl_rec.VAL_35
,p_dtl_rec.VAL_36
,p_dtl_rec.VAL_37
,p_dtl_rec.VAL_38
,p_dtl_rec.VAL_39
,p_dtl_rec.VAL_40
,p_dtl_rec.VAL_41
,p_dtl_rec.VAL_42
,p_dtl_rec.VAL_43
,p_dtl_rec.VAL_44
,p_dtl_rec.VAL_45
,p_dtl_rec.VAL_46
,p_dtl_rec.VAL_47
,p_dtl_rec.VAL_48
,p_dtl_rec.VAL_49
,p_dtl_rec.VAL_50
,p_dtl_rec.VAL_51
,p_dtl_rec.VAL_52
,p_dtl_rec.VAL_53
,p_dtl_rec.VAL_54
,p_dtl_rec.VAL_55
,p_dtl_rec.VAL_56
,p_dtl_rec.VAL_57
,p_dtl_rec.VAL_58
,p_dtl_rec.VAL_59
,p_dtl_rec.VAL_60
,p_dtl_rec.VAL_61
,p_dtl_rec.VAL_62
,p_dtl_rec.VAL_63
,p_dtl_rec.VAL_64
,p_dtl_rec.VAL_65
,p_dtl_rec.VAL_66
,p_dtl_rec.VAL_67
,p_dtl_rec.VAL_68
,p_dtl_rec.VAL_69
,p_dtl_rec.VAL_70
,p_dtl_rec.VAL_71
,p_dtl_rec.VAL_72
,p_dtl_rec.VAL_73
,p_dtl_rec.VAL_74
,p_dtl_rec.VAL_75
,p_dtl_rec.CREATED_BY
,p_dtl_rec.CREATION_DATE
,p_dtl_rec.LAST_UPDATE_DATE
,p_dtl_rec.LAST_UPDATED_BY
,p_dtl_rec.LAST_UPDATE_LOGIN
,p_dtl_rec.PROGRAM_APPLICATION_ID
,p_dtl_rec.PROGRAM_ID
,p_dtl_rec.PROGRAM_UPDATE_DATE
,p_dtl_rec.REQUEST_ID
,p_dtl_rec.OBJECT_VERSION_NUMBER
,p_dtl_rec.PRMY_SORT_VAL
,p_dtl_rec.SCND_SORT_VAL
,p_dtl_rec.THRD_SORT_VAL
,p_dtl_rec.TRANS_SEQ_NUM
,p_dtl_rec.RCRD_SEQ_NUM
);
UPDATE ben_ext_rslt_dtl
SET VAL_01 = p_dtl_rec.VAL_01
,VAL_02 = p_dtl_rec.VAL_02
,VAL_03 = p_dtl_rec.VAL_03
,VAL_04 = p_dtl_rec.VAL_04
,VAL_05 = p_dtl_rec.VAL_05
,VAL_06 = p_dtl_rec.VAL_06
,VAL_07 = p_dtl_rec.VAL_07
,VAL_08 = p_dtl_rec.VAL_08
,VAL_09 = p_dtl_rec.VAL_09
,VAL_10 = p_dtl_rec.VAL_10
,VAL_11 = p_dtl_rec.VAL_11
,VAL_12 = p_dtl_rec.VAL_12
,VAL_13 = p_dtl_rec.VAL_13
,VAL_14 = p_dtl_rec.VAL_14
,VAL_15 = p_dtl_rec.VAL_15
,VAL_16 = p_dtl_rec.VAL_16
,VAL_17 = p_dtl_rec.VAL_17
,VAL_19 = p_dtl_rec.VAL_19
,VAL_18 = p_dtl_rec.VAL_18
,VAL_20 = p_dtl_rec.VAL_20
,VAL_21 = p_dtl_rec.VAL_21
,VAL_22 = p_dtl_rec.VAL_22
,VAL_23 = p_dtl_rec.VAL_23
,VAL_24 = p_dtl_rec.VAL_24
,VAL_25 = p_dtl_rec.VAL_25
,VAL_26 = p_dtl_rec.VAL_26
,VAL_27 = p_dtl_rec.VAL_27
,VAL_28 = p_dtl_rec.VAL_28
,VAL_29 = p_dtl_rec.VAL_29
,VAL_30 = p_dtl_rec.VAL_30
,VAL_31 = p_dtl_rec.VAL_31
,VAL_32 = p_dtl_rec.VAL_32
,VAL_33 = p_dtl_rec.VAL_33
,VAL_34 = p_dtl_rec.VAL_34
,VAL_35 = p_dtl_rec.VAL_35
,VAL_36 = p_dtl_rec.VAL_36
,VAL_37 = p_dtl_rec.VAL_37
,VAL_38 = p_dtl_rec.VAL_38
,VAL_39 = p_dtl_rec.VAL_39
,VAL_40 = p_dtl_rec.VAL_40
,VAL_41 = p_dtl_rec.VAL_41
,VAL_42 = p_dtl_rec.VAL_42
,VAL_43 = p_dtl_rec.VAL_43
,VAL_44 = p_dtl_rec.VAL_44
,VAL_45 = p_dtl_rec.VAL_45
,VAL_46 = p_dtl_rec.VAL_46
,VAL_47 = p_dtl_rec.VAL_47
,VAL_48 = p_dtl_rec.VAL_48
,VAL_49 = p_dtl_rec.VAL_49
,VAL_50 = p_dtl_rec.VAL_50
,VAL_51 = p_dtl_rec.VAL_51
,VAL_52 = p_dtl_rec.VAL_52
,VAL_53 = p_dtl_rec.VAL_53
,VAL_54 = p_dtl_rec.VAL_54
,VAL_55 = p_dtl_rec.VAL_55
,VAL_56 = p_dtl_rec.VAL_56
,VAL_57 = p_dtl_rec.VAL_57
,VAL_58 = p_dtl_rec.VAL_58
,VAL_59 = p_dtl_rec.VAL_59
,VAL_60 = p_dtl_rec.VAL_60
,VAL_61 = p_dtl_rec.VAL_61
,VAL_62 = p_dtl_rec.VAL_62
,VAL_63 = p_dtl_rec.VAL_63
,VAL_64 = p_dtl_rec.VAL_64
,VAL_65 = p_dtl_rec.VAL_65
,VAL_66 = p_dtl_rec.VAL_66
,VAL_67 = p_dtl_rec.VAL_67
,VAL_68 = p_dtl_rec.VAL_68
,VAL_69 = p_dtl_rec.VAL_69
,VAL_70 = p_dtl_rec.VAL_70
,VAL_71 = p_dtl_rec.VAL_71
,VAL_72 = p_dtl_rec.VAL_72
,VAL_73 = p_dtl_rec.VAL_73
,VAL_74 = p_dtl_rec.VAL_74
,VAL_75 = p_dtl_rec.VAL_75
,OBJECT_VERSION_NUMBER = p_dtl_rec.OBJECT_VERSION_NUMBER
,THRD_SORT_VAL = p_dtl_rec.THRD_SORT_VAL
,prmy_sort_val =p_dtl_rec.prmy_sort_val
WHERE ext_rslt_dtl_id = p_dtl_rec.ext_rslt_dtl_id;
SELECT a.ext_data_elmt_in_rcd_id
,a.seq_num
,a.sprs_cd
,a.strt_pos
,a.dlmtr_val
,a.rqd_flag
,b.ext_data_elmt_id
,b.data_elmt_typ_cd
,b.data_elmt_rl
,b.NAME
,Hr_General.decode_lookup('BEN_EXT_FRMT_MASK', b.frmt_mask_cd) frmt_mask_cd
,b.frmt_mask_cd frmt_mask_lookup_cd
,b.string_val
,b.dflt_val
,b.max_length_num
,b.just_cd
FROM ben_ext_data_elmt b,
ben_ext_data_elmt_in_rcd a
WHERE a.ext_data_elmt_id = b.ext_data_elmt_id
AND b.data_elmt_typ_cd = 'R'
AND a.ext_rcd_id = c_ext_rcd_id
-- AND a.hide_flag = 'N'
ORDER BY a.seq_num;
SELECT formula_type_id
FROM ff_formulas_f
WHERE formula_id = c_formula_type_id
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT organization_id,business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND business_group_id = g_business_group_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
Update_Record_Values (p_ext_rcd_id => p_ext_dtl_rcd_id
,p_ext_data_element_name => NULL
,p_data_element_value => l_ff_value
,p_data_ele_seqnum => i.seq_num
,p_ext_dtl_rec => p_rslt_rec);
ELSIF p_header_type = 'SELECTION_CRITERIA' THEN
l_return_value := g_conc_prog_details(0).selection_criteria;
SELECT Substr(ed.NAME,1,240)
FROM ben_ext_dfn ed
WHERE ed.ext_dfn_id = p_ext_dfn_id;
SELECT pay.payroll_name
FROM pay_payrolls_f pay
WHERE pay.payroll_id = c_payroll_id
AND c_end_date BETWEEN pay.effective_start_date
AND pay.effective_end_date;
SELECT con.consolidation_set_name
FROM pay_consolidation_sets con
WHERE con.consolidation_set_id = c_con_set;
SELECT 1
FROM pay_run_results
WHERE assignment_action_id = p_ass_act_id
AND element_type_id = p_element_type_id;
SELECT old_val1 old_date,
new_val1 new_date
FROM ben_ext_chg_evt_log
WHERE person_id = p_person_id
AND chg_evt_cd = 'COPOS'
AND fnd_date.canonical_to_date(prmtr_09)
BETWEEN g_extract_params(p_business_group_id).extract_start_date AND
g_extract_params(p_business_group_id).extract_end_date
ORDER BY ext_chg_evt_log_id DESC;
SELECT MIN(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND assignment_type = 'E';
SELECT 1
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
AND val_05 <> '00000000' -- Ptpn St Date
AND val_11 IS NOT NULL -- Kind of Ptpn
AND val_12 IS NOT NULL -- Value of Ptpn
AND val_16 IS NOT NULL -- PTP
AND val_04 = c_asg_seq
AND dtl.person_id = g_person_id
AND ext_stat_cd = 'A'
AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
AND dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 5;
SELECT trunc(date_of_birth)
FROM per_all_people_f per
,per_all_assignments_f paf
WHERE per.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id
AND p_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT Decode(aei_information5,'Y','J',' ') obj_cd
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'NL_USZO_INFO'
AND Trunc(p_effective_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 ROWNUM = 1;
l_rec_09_values.DELETE(j);
SELECT pos.org_structure_version_id
FROM per_org_structure_versions_v pos,
hr_organization_information hoi
WHERE hoi.organization_id = p_business_group_id
AND To_Number(hoi.org_information1) = pos.organization_structure_id
AND Trunc(p_effective_date) BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND hoi.org_information_context = 'NL_BG_INFO';
SELECT os.organization_id_child
FROM (SELECT *
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = c_org_struct_ver_id ) os
WHERE os.organization_id_parent = c_org_id;
SELECT 'x'
FROM hr_organization_information
WHERE organization_id = c_org_id
AND org_information_context = 'NL_ORG_INFORMATION'
AND org_information3 IS NOT NULL
AND org_information4 IS NOT NULL;
SELECT 'x'
FROM (SELECT *
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = c_org_struct_ver_id ) os
WHERE os.organization_id_parent = c_org_id;
SELECT 1
FROM pay_element_entries_f peef,
pay_element_links_f pelf
WHERE p_effective_date BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND p_effective_date BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND peef.element_link_id = pelf.element_link_id
AND peef.assignment_id = p_assignment_id
AND pelf.element_type_id =
(SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = 'ABP Pensions'
AND TRUNC(g_extract_params(p_business_group_id).extract_start_date)
BETWEEN effective_start_date AND effective_end_date);
SELECT payroll_id
FROM per_all_assignments_f
WHERE p_effective_date between effective_start_date AND
effective_end_date
AND assignment_id = p_assignment_id
AND payroll_id IS NOT NULL;
SELECT MIN(effective_start_date) - 1 term_date
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y')
UNION
--
-- Get the dates for any ended assignments. Note that this is for sec
-- assignments only.
--
SELECT MAX(effective_end_date)
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND asg.primary_flag = 'N'
AND NOT EXISTS( SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = p_assignment_id
AND asg1.effective_start_date = asg.effective_end_date + 1
AND asg.assignment_id = asg1.assignment_id )
AND NOT EXISTS( SELECT 1
FROM per_all_assignments_f asg1
WHERE asg1.assignment_id = p_assignment_id
AND asg1.effective_start_date < asg.effective_start_date
AND asg.assignment_id = asg1.assignment_id
AND asg1.assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y'))
;
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE legislation_code = 'NL'
AND database_item_suffix = '_ASG_RUN';
SELECT old_val1 old_date,
new_val1 new_date
FROM ben_ext_chg_evt_log
WHERE person_id = c_person_id
AND chg_evt_cd = 'COPOS'
AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
ORDER BY ext_chg_evt_log_id desc;
SELECT assignment_id
,effective_start_date start_date
,effective_end_date end_date
,fnd_number.canonical_to_number(new_val1) ptp
FROM per_all_assignments_f asg,
ben_ext_chg_evt_log log
,per_assignment_status_types past
,hr_soft_coding_keyflex sck
WHERE asg.assignment_id = p_assignment_id
AND asg.assignment_status_type_id = past.assignment_status_type_id
AND sck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND log.person_id = g_person_id
AND log.chg_evt_cd = 'COPTP'
AND fnd_date.canonical_to_date(log.prmtr_09)
BETWEEN c_start_date AND c_end_date
AND asg.effective_start_date between c_asg_st_dt AND c_asg_ed_dt
AND asg.soft_coding_keyflex_id = log.prmtr_02
AND asg.assignment_id = log.prmtr_01
AND fnd_number.canonical_to_number(new_val1) =
fnd_number.canonical_to_number(sck.segment29)
order by effective_start_date;
SELECT NVL(sum(round(fnd_number.canonical_to_number(peev.screen_entry_value),2)),0) prior_ptp
FROM pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_entry_values_f peev
WHERE peef.effective_start_date < c_effective_date
AND c_effective_date BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND peev.effective_start_date < c_effective_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_input_val_id
AND peef.assignment_id = c_asg_id
AND peev.screen_entry_value IS NOT NULL
AND pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id)
BETWEEN c_orig_st_date AND c_orig_ed_date;
SELECT 1
FROM ben_ext_rslt res
WHERE ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
AND ext_stat_cd = 'A'
AND EXISTS ( SELECT 1 FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.person_id = g_person_id)
AND trunc(res.eff_dt) = trunc(c_eff_dt)
ORDER BY ext_rslt_id DESC;
SELECT fnd_number.canonical_to_number(val_06)/100 basis
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt)= trunc(c_eff_dt)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 8
AND val_05 = c_pt_code
ORDER BY res.ext_rslt_id desc;
SELECT fnd_number.canonical_to_number(val_06)/100 basis
,TRUNC(res.eff_dt) eff_dt
, val_05 code
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt)< trunc(c_eff_dt)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 8
AND val_08 = '0000'
AND val_09 = '00'
AND val_10 <> c_poj_cd
ORDER BY res.ext_rslt_id desc;
SELECT 1
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt)> TRUNC(c_eff_dt)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 8
AND val_08 = to_char(TRUNC(c_eff_dt),'YYYY')
AND val_09 = to_char(TRUNC(c_eff_dt),'MM')
AND val_10 = c_poj_cd -- current_code
ORDER BY res.ext_rslt_id desc;
SELECT fnd_number.canonical_to_number(val_06)/100 amount
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt)= trunc(c_eff_dt)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
AND dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 10
AND val_05 = c_code
AND val_08 = '0000'
AND val_09 = '00'
ORDER BY res.ext_rslt_id desc;
SELECT fnd_number.canonical_to_number(val_06)/100 amount
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt)>= trunc(c_eff_dt)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
AND dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 10
AND val_05 = c_code
AND val_08 = c_year
AND val_09 = c_mon
ORDER BY res.ext_rslt_id desc;
SELECT asg.assignment_id
,effective_start_date start_date
,effective_end_date end_date
,least(fnd_number.canonical_to_number(nvl(sck.segment29,100)),125) ptp
FROM per_all_assignments_f asg
,per_assignment_status_types past
,hr_soft_coding_keyflex sck
WHERE asg.assignment_id = p_assignment_id
AND asg.assignment_status_type_id = past.assignment_status_type_id
AND sck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date BETWEEN
g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND asg.effective_start_date >= c_min_st_dt
ORDER BY effective_start_date;
SELECT effective_start_date
FROM per_all_assignments_f asg
,per_assignment_status_types past
,hr_soft_coding_keyflex sck
WHERE asg.assignment_id = p_assignment_id
AND asg.assignment_status_type_id = past.assignment_status_type_id
AND sck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date BETWEEN
g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND EXISTS (SELECT 1
FROM per_all_assignments_f asg1
,per_assignment_status_types past1
,hr_soft_coding_keyflex sck1
WHERE asg1.assignment_id = p_assignment_id
AND asg1.effective_end_date = asg.effective_start_date - 1
AND asg1.assignment_status_type_id = past1.assignment_status_type_id
AND sck1.soft_coding_keyflex_id = asg1.soft_coding_keyflex_id
AND past1.per_system_status = 'ACTIVE_ASSIGN'
AND fnd_number.canonical_to_number(nvl(sck.segment29,'100'))
<> fnd_number.canonical_to_number(nvl(sck1.segment29,'100'))
)
ORDER BY effective_start_date;
SELECT MIN(effective_start_date) hire_date
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'ACTIVE_ASSIGN'
AND active_flag = 'Y')
AND assignment_type = 'E';
SELECT MIN(effective_start_date) - 1 term_date
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y')
AND assignment_type = 'E';
SELECT piv.input_value_id start_dt_id, piv1.input_value_id end_dt_id
,pet.element_type_id
FROM pay_input_values_f piv
,pay_input_values_f piv1
,pay_element_types_f pet
WHERE piv.name = 'Start Date'
AND piv.element_type_id = pet.element_type_id
AND piv1.name = 'End Date'
AND piv1.element_type_id = pet.element_type_id
AND pet.element_name = 'ABP Record 05 Reporting'
AND pet.legislation_code IS NULL
AND pet.business_group_id = p_bg_id
AND p_effective_date between pet.effective_start_date AND pet.effective_end_date
AND p_effective_date between piv.effective_start_date AND piv.effective_end_date
AND p_effective_date between piv1.effective_start_date AND piv1.effective_end_date;
SELECT fnd_date.canonical_to_date(peev.screen_entry_value) start_date, fnd_date.canonical_to_date(peev1.screen_entry_value) end_date
FROM pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_entry_values_f peev,
pay_element_entry_values_f peev1
WHERE p_effective_date BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND p_effective_date BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND p_effective_date BETWEEN peev.effective_start_date AND
peev.effective_end_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = p_ele_type_id
AND peev.input_value_id = p_start_dt_id
AND peef.assignment_id = p_asg_id
AND p_effective_date BETWEEN peev1.effective_start_date AND
peev1.effective_end_date
AND peev1.element_entry_id = peef.element_entry_id
AND peev1.input_value_id = p_end_dt_id;
SELECT DISTINCT asg.effective_start_date Start_Date
,asg.effective_end_date End_Date
,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))*100 ptp
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE asg.assignment_id = p_asg_id
AND asg.effective_start_date BETWEEN p_start_date AND nvl(p_end_date,to_date('31-12-4712','dd-mm-rrrr'))
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND target.enabled_flag = 'Y'
order by Start_Date;
SELECT
MIN(to_date('1/'||to_char(pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id),'MM/YYYY'),'DD/MM/YYYY')) start_date,
MAX(add_months(to_date('1/'||to_char(pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id),'MM/YYYY'),'DD/MM/YYYY'),1) - 1) end_date
FROM pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_entry_values_f peev
WHERE c_effective_date BETWEEN peef.effective_start_date AND
peef.effective_end_date
AND c_effective_date BETWEEN pelf.effective_start_date AND
pelf.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date AND
peev.effective_end_date
AND peef.element_link_id = pelf.element_link_id
AND peev.element_entry_id = peef.element_entry_id
AND pelf.element_type_id = c_ele_type_id
AND peev.input_value_id = c_input_val_id
AND peef.assignment_id = c_asg_id
AND peev.screen_entry_value IS NOT NULL;
SELECT DISTINCT asg.effective_start_date Start_Date
,asg.effective_end_date End_Date
,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))*100 ptp
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE asg.assignment_id = p_asg_id
AND asg.effective_start_date BETWEEN p_start_date AND nvl(p_end_date,to_date('31-12-4712','dd-mm-rrrr'))
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND target.enabled_flag = 'Y'
order by Start_Date;
l_rec_09.DELETE;
l_rec_31.DELETE;
l_basis_rec_41.DELETE;
hr_utility.set_location('...Updated the date',20);
hr_utility.set_location('...Updated the date',20);
SELECT ext_dfn_type
FROM pqp_extract_attributes
WHERE ext_dfn_id = c_ext_dfn_id;
SELECT request_id
FROM ben_ext_rslt
WHERE ext_rslt_id = c_ext_rslt_id
AND ext_dfn_id = c_ext_dfn_id
AND business_group_id = c_business_group_id;
SELECT session_id -- Session id
,organization_name -- Concurrent Program Name
,business_group_id -- Business Group
,tax_unit_id -- Concurrent Request Id
,value1 -- Extract Definition Id
,value2 -- Payroll Id
,value3 -- Consolidation Set
,value4 -- Organization Id
,value5 -- Sort Position -- 9278285
,value6 --
,attribute1 --
,attribute2 --
,attribute3 -- Extract Start Date
,attribute4 -- Extract End Date
,attribute5 -- Extract Record 01 Flag
FROM pay_us_rpt_totals
WHERE tax_unit_id = c_request_id
AND value1 = c_ext_dfn_id
AND business_group_id = c_business_group_id;
SELECT pbg.legislation_code
,pbg.currency_code
FROM per_business_groups_perf pbg
WHERE pbg.business_group_id = c_business_group_id;
SELECT pos.org_structure_version_id
FROM per_org_structure_versions_v pos,
hr_organization_information hoi
WHERE hoi.organization_id = p_business_group_id
AND To_Number(hoi.org_information1) = pos.organization_structure_id
AND Trunc(p_effective_date) BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND hoi.org_information_context = 'NL_BG_INFO';
SELECT os.organization_id_child
FROM (SELECT *
FROM per_org_structure_elements a
WHERE a.org_structure_version_id = c_org_str_ver_id ) os
START WITH os.organization_id_parent = c_org_id
CONNECT BY os.organization_id_parent = PRIOR os.organization_id_child;
SELECT 'x'
FROM hr_organization_information
WHERE organization_id = c_org_id
AND org_information_context = 'NL_ORG_INFORMATION'
AND org_information3 IS NOT NULL
AND org_information4 IS NOT NULL;
SELECT Decode(rin.seq_num,1,'00',
2,'01',
3,'02',
4,'04',
5,'05',
7,'08',
8,'09',
10,'12',
12,'20',
14,'21',
16,'22',
17,'30',
19,'31',
21,'40',
23,'41',
24,'41h',
26,'94',
27,'95',
28,'96',
29,'97',
30,'99',
'~') rec_num,
rin.seq_num,
rin.hide_flag,
rcd.ext_rcd_id,
rcd.rcd_type_cd
FROM ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id = Ben_Ext_Thread.g_ext_dfn_id
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
ORDER BY rin.seq_num;
SELECT 1
FROM per_periods_of_service
WHERE PERSON_ID = c_per_id
AND TRUNC(actual_termination_date) =
TRUNC(date_start)
AND NOT EXISTS(SELECT 1
FROM PER_PERIODS_OF_SERVICE
WHERE person_id = c_per_id
AND TRUNC(date_start) >
TRUNC(g_extract_params(p_business_group_id).extract_start_date));
SELECT 1
FROM per_periods_of_service ppos1
WHERE ppos1.PERSON_ID = c_per_id
AND TRUNC(ppos1.actual_termination_date) =
TRUNC(ppos1.date_start)
AND TRUNC(ppos1.date_start) <= TRUNC(g_extract_params(p_business_group_id).extract_end_date)
AND NOT EXISTS(SELECT 1
FROM PER_PERIODS_OF_SERVICE ppos2
WHERE ppos2.person_id = c_per_id
AND ppos1.period_of_service_id <> ppos2.period_of_service_id
AND TRUNC(ppos2.date_start) > TRUNC(ppos1.date_start)
AND TRUNC(g_extract_params(p_business_group_id).extract_end_date) >= TRUNC(ppos2.date_start)
AND ppos2.actual_termination_date IS NULL
AND ppos2.final_process_date IS NULL
AND ppos2.last_standard_process_date IS NULL);
SELECT 1
FROM per_periods_of_service pps
,per_all_assignments_f asg
WHERE asg.assignment_id = c_ass_id
AND c_eff_dt BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.period_of_service_id = pps.period_of_service_id
AND pps.actual_termination_date IS NOT NULL
AND pps.final_process_date IS NOT NULL
AND pps.final_process_date <
TRUNC(g_extract_params(p_business_group_id).extract_start_date);
SELECT 1
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = p_asg_id
AND ppa.BUSINESS_GROUP_ID = p_bg_id
AND ppa.action_status = 'C'
AND ppa.action_type IN ('Q','R')
AND paa.action_status = 'C'
AND ppa.payroll_id = Nvl(p_payroll_id,ppa.payroll_id)
AND ppa.consolidation_set_id = Nvl(p_cons_id,ppa.consolidation_set_id)
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.effective_date BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date;
SELECT 1
FROM pay_element_entries_f peef
where
peef.Assignment_id = p_asg_id
AND (g_extract_params(p_business_group_id).extract_start_date BETWEEN peef.EFFECTIVE_START_DATE AND peef.EFFECTIVE_END_DATE
OR
g_extract_params(p_business_group_id).extract_end_date BETWEEN peef.EFFECTIVE_START_DATE AND peef.EFFECTIVE_END_DATE
)
AND EXISTS
(SELECT 1
FROM pay_element_types_f petf
WHERE
peef.element_type_id = petf.element_type_id
AND UPPER(element_name) like '%RETRO%ABP%'
AND (g_extract_params(p_business_group_id).extract_start_date BETWEEN petf.EFFECTIVE_START_DATE AND petf.EFFECTIVE_END_DATE
OR
g_extract_params(p_business_group_id).extract_end_date BETWEEN petf.EFFECTIVE_START_DATE AND petf.EFFECTIVE_END_DATE
)
);
SELECT person_id
INTO l_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND ROWNUM < 2;
SELECT Substr(replace(per_information1,'.',NULL),0,5)
FROM per_all_people_f
WHERE person_id = c_person_id
AND business_group_id = p_business_group_id
AND c_date_earned BETWEEN effective_start_date
AND effective_end_date;
SELECT last_name
FROM per_all_people_f
WHERE person_id IN
( SELECT contact_person_id
FROM per_contact_relationships
WHERE person_id = c_person_id
AND business_group_id = p_business_group_id
AND contact_type IN ('S','D')
AND c_date_earned
BETWEEN Nvl(date_start,
g_extract_params(p_business_group_id).extract_start_date )
AND Nvl(date_end,
g_extract_params(p_business_group_id).extract_end_date)
)
AND business_group_id = p_business_group_id
AND c_date_earned BETWEEN effective_start_date
AND effective_end_date;
SELECT Decode(sex,'F','V','M') gender
FROM per_all_people_f
WHERE person_id = g_person_id
AND business_group_id = c_business_group_id
AND c_date_earned BETWEEN effective_start_date
AND effective_end_date;
SELECT pre_name_adjunct
FROM per_all_people_f
WHERE person_id IN
(SELECT contact_person_id
FROM per_contact_relationships
WHERE person_id = g_person_id
AND business_group_id = p_business_group_id
AND contact_type IN('S','D')
AND c_date_earned
BETWEEN Nvl(date_start,
g_extract_params(p_business_group_id).extract_start_date )
AND Nvl(date_end,
g_extract_params(p_business_group_id).extract_end_date)
)
AND business_group_id = p_business_group_id
AND c_date_earned BETWEEN effective_start_date
AND effective_end_date;
SELECT per_information13
FROM per_all_people_f
WHERE person_id = g_person_id
AND p_effective_date BETWEEN
effective_start_date AND effective_end_date
AND business_group_id = p_business_group_id
AND per_information_category = 'NL';
SELECT NVL(lpad(aei_information2,2,'0'),'00') old_num
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'PQP_NL_ABP_OLD_EE_INFO';
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT org_structure_version_id
FROM per_org_structure_versions_v
WHERE organization_structure_id = c_hierarchy_id
AND g_extract_params(p_business_group_id).extract_end_date BETWEEN date_from
AND nvl(date_to,hr_api.g_eot);
SELECT org_structure_version_id
FROM per_org_structure_versions_v
WHERE business_group_id = p_business_group_id
AND g_extract_params(p_business_group_id).extract_end_date BETWEEN date_from
AND nvl( date_to,hr_api.g_eot);
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = p_business_group_id;
SELECT UPPER(nvl(lpad(org_information4,7,'0'),'0000000')) old_num
,SUBSTR(NVL(org_information2,'-1'),0,7) new_num
FROM hr_organization_information
WHERE org_information_context = 'PQP_ABP_PROVIDER'
AND organization_id = c_org_id;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND business_group_id = p_business_group_id
AND p_effective_date BETWEEN effective_start_date and effective_end_date --9583106
ORDER BY effective_end_date DESC;
SELECT NVL(lpad(aei_information1,15,'0'),'000000000000000') old_num
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'PQP_NL_ABP_OLD_EE_INFO';
SELECT region_1
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND primary_flag = 'Y'
AND style = 'NL'
UNION
SELECT address_line1
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND primary_flag = 'Y'
AND style = 'NL_GLB';
SELECT add_information13
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style = 'NL'
AND primary_flag = 'Y'
UNION
SELECT address_line2
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style = 'NL_GLB'
AND primary_flag = 'Y';
SELECT add_information14
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND primary_flag = 'Y'
AND style = 'NL'
UNION
SELECT address_line3
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND primary_flag = 'Y'
AND style = 'NL_GLB';
SELECT postal_code
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style IN ('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT substr(l_postal_code,i,1) INTO temp_str from dual;
SELECT town_or_city
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style IN ('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT Decode(country,'NL','N',country) code
,d_country
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style IN ('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'HR_NL_CITY'
AND lookup_code = p_lookup_code;
SELECT Decode(country,'NL',' ','J')
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot)
AND style IN ('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT DECODE(marital_status, 'S',1,
'M',2,
'D',3,
'W',4,
'DP',0,
'L',3,
'BE_LIV_TOG',1,
'REG_PART',1,
'BE_WID_PENS',4,
NULL) ms_code
FROM per_all_people_f
WHERE person_id = c_person_id
AND business_group_id = p_business_group_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT DECODE(country,'NL','N','J')
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND NVL(date_to,hr_api.g_eot)
AND style IN('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT NVL(aei_information6,'0') ,
TRUNC(fnd_date.canonical_to_date(aei_information1))
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'NL_ABP_PAR_INFO'
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND p_effective_date BETWEEN
TRUNC(fnd_date.canonical_to_date(aei_information1))
AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),hr_api.g_eot))
AND aei_information6 IS NOT NULL;
SELECT MIN(asg.effective_start_date)
FROM per_assignments_f asg,per_assignment_status_types past
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.assignment_id = c_assign_id;
SELECT fnd_date.canonical_to_date(TO_CHAR(c_effective_dt,'YYYY')||'/01/01')
FROM dual ;
SELECT fnd_date.canonical_to_date(prmtr_02)
FROM ben_ext_chg_evt_log
WHERE person_id = g_person_id
AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id
AND chg_eff_dt BETWEEN p_start_date AND p_end_date
AND chg_evt_cd = 'COAPS'
AND ext_chg_evt_log_id =
(SELECT Max(ext_chg_evt_log_id)
FROM ben_ext_chg_evt_log
WHERE person_id = g_person_id
AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id
AND chg_eff_dt BETWEEN p_start_date AND p_end_date
AND chg_evt_cd = 'COAPS');
l_rec_31_values.DELETE(j);
l_rec_41_basis_values.DELETE(j);
l_rec_12_values.DELETE(j);
l_rec_41_contrib_values.DELETE(j);
SELECT 1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'PQP_NL_ABP_OLD_EE_INFO';
SELECT 'x'
FROM ben_ext_chg_evt_log
WHERE person_id = c_person_id
AND business_group_id = c_business_group_id
AND chg_evt_cd IN ('COLN','COSS','COUN','COG','CODB')
AND fnd_date.canonical_to_date(prmtr_09)
BETWEEN c_ext_start_date AND c_ext_end_date;
SELECT contact_person_id
FROM per_contact_relationships
WHERE person_id = g_person_id
AND p_effective_date BETWEEN date_start
AND Nvl(date_end,Hr_Api.g_eot)
AND contact_type IN ('S','D')
AND business_group_id = p_business_group_id;
SELECT 'X'
FROM ben_ext_chg_evt_log
WHERE person_id = c_person_id
AND business_group_id = p_business_group_id
AND chg_evt_cd IN ('COUN','COLN','CCFN')
AND fnd_date.canonical_to_date(prmtr_09) BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date;
SELECT 1
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
AND dtl.person_id = g_person_id
AND ext_stat_cd = 'A'
AND TRUNC(res.eff_dt) <
TRUNC(g_extract_params(p_business_group_id).extract_start_date)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND dtl.val_04 = c_asg_seq_no
AND rin.seq_num = 3;
SELECT 'x'
FROM ben_ext_chg_evt_log
WHERE person_id = c_person_id
AND business_group_id = c_business_group_id
AND chg_evt_cd = 'COM'
AND fnd_date.canonical_to_date(prmtr_09)
BETWEEN c_ext_start_date AND c_ext_end_date;
SELECT DECODE(country,'NL','N','J')
FROM per_addresses_v
WHERE person_id = c_person_id
AND p_effective_date BETWEEN date_from
AND NVL(date_to,hr_api.g_eot)
AND style IN('NL','NL_GLB')
AND primary_flag = 'Y';
SELECT 1
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
AND dtl.person_id = g_person_id
AND ext_stat_cd = 'A'
AND TRUNC(res.eff_dt) <
TRUNC(g_extract_params(p_business_group_id).extract_start_date)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND dtl.val_04 = c_asg_seq_no
AND rin.seq_num = 4;
SELECT 1
FROM per_addresses_v padr
WHERE person_id = c_person_id
-- if the current primary address is not in NL
AND p_effective_date BETWEEN date_from AND NVL(date_to,hr_api.g_eot)
AND primary_flag = 'Y'
AND country <> 'NL'
AND EXISTS (SELECT 1
-- exists change in address event logs
-- for the primary address
FROM ben_ext_chg_evt_log log
WHERE padr.person_id = log.person_id
AND chg_evt_cd = 'COPR'
AND padr.address_id = log.prmtr_01
AND fnd_date.canonical_to_date(prmtr_09)
BETWEEN c_ext_start_date AND c_ext_end_date);
SELECT 1
FROM per_addresses_v padr
WHERE person_id = c_person_id
-- if the current primary address is in NL
AND p_effective_date BETWEEN date_from AND NVL(date_to,hr_api.g_eot)
AND primary_flag = 'Y'
AND country = 'NL'
AND EXISTS (SELECT 1
-- exists change in address event logs
-- for the primary address
FROM ben_ext_chg_evt_log log
WHERE padr.person_id = log.person_id
AND chg_evt_cd = 'COPR'
AND padr.address_id = log.prmtr_01
-- there is a change in primary address
AND log.prmtr_02 IS NOT NULL
AND fnd_date.canonical_to_date(prmtr_09)
BETWEEN c_ext_start_date AND c_ext_end_date
AND EXISTS (SELECT 1
FROM per_addresses_v adr
WHERE adr.person_id = log.person_id
AND adr.address_id = to_number(log.prmtr_02)
-- old address was not in NL
AND country <> 'NL'));
SELECT TO_NUMBER(prmtr_01) addr_id,new_val1 country
FROM ben_ext_chg_evt_log log
WHERE person_id = c_person_id
AND chg_evt_cd = 'COCN'
AND fnd_date.canonical_to_date(prmtr_09)
BETWEEN c_ext_start_date AND c_ext_end_date
ORDER BY ext_chg_evt_log_id DESC;
SELECT 1
FROM per_addresses_v padr
WHERE person_id = g_person_id
AND p_effective_date BETWEEN date_from AND NVL(date_to,hr_api.g_eot)
AND address_id = c_addr_id
AND primary_flag = 'Y'
AND country = c_code;
SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
FROM ben_ext_chg_evt_log
WHERE person_id = g_person_id
AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND chg_evt_cd = 'COAPS'
AND ext_chg_evt_log_id =
(SELECT MAX(ext_chg_evt_log_id)
FROM ben_ext_chg_evt_log
WHERE person_id = g_person_id
AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND chg_evt_cd = 'COAPS');
SELECT TO_CHAR(p_effective_date,'MM')
FROM dual;
SELECT 1
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt) BETWEEN c_start_of_yr
AND TRUNC(g_extract_params(p_business_group_id).extract_start_date) - 1
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND dtl.val_04 = c_asg_seq_no
AND rin.seq_num = 7;
SELECT 1
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ASG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND pty.pension_sub_category = p_sub_cat
AND prmtr_03 = 'Y'
AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
AND person_id = (SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
)
AND bec.business_group_id = p_business_group_id;
SELECT 1
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ORG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND pty.pension_sub_category = p_sub_cat
AND prmtr_03 = 'Y'
AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
AND bec.business_group_id = p_business_group_id;
SELECT 1
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ASG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND prmtr_03 = 'Y'
AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
AND pty.pension_sub_category = p_sub_cat
AND person_id = (SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
)
AND bec.business_group_id = p_business_group_id
AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
ORDER BY ext_chg_evt_log_id;
SELECT 1
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ORG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND prmtr_03 = 'Y'
AND pty.pension_sub_category = p_sub_cat
AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
AND bec.business_group_id = p_business_group_id
AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND chg_eff_dt >= c_hire_date
ORDER BY ext_chg_evt_log_id;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT ORG_STRUCTURE_VERSION_ID
FROM per_org_structure_versions_v
WHERE organization_structure_id = c_hierarchy_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot);
SELECT ORG_STRUCTURE_VERSION_ID
FROM per_org_structure_versions_v
WHERE business_group_id = p_business_group_id
AND p_effective_date BETWEEN date_from
AND Nvl( date_to,Hr_Api.g_eot);
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = p_business_group_id;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_effective_date;
SELECT pty.pension_type_id
FROM pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_type_extra_info pei,
pqp_pension_types_f pty
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND paa.assignment_id = p_assignment_id
AND pei.element_type_id = prr.element_type_id
AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
AND pty.pension_sub_category = p_sub_cat;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND Trunc(p_date_earned) BETWEEN effective_start_date AND effective_end_date
AND business_group_id = p_business_group_id;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT ORG_STRUCTURE_VERSION_ID
FROM per_org_structure_versions_v
WHERE organization_structure_id = c_hierarchy_id
AND p_date_earned BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot);
SELECT ORG_STRUCTURE_VERSION_ID
FROM per_org_structure_versions_v
WHERE business_group_id = p_business_group_id
AND p_date_earned BETWEEN date_from
AND Nvl( date_to,Hr_Api.g_eot);
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = p_business_group_id;
SELECT hoi.org_information_id
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
AND hoi.org_information3 = To_Char(p_pension_type_id)
AND NVL(hoi.org_information7,'Y') = 'Y'
AND hoi.organization_id = c_org_id;
SELECT Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
AND hoi.org_information3 = To_Char(p_pension_type_id)
AND hoi.org_information6 = 'Y'
AND NVL(hoi.org_information7,'Y') = 'Y'
AND hoi.organization_id = c_org_id
AND p_date_earned BETWEEN Fnd_Date.canonical_to_date(hoi.org_information1)
AND Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
SELECT 1
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ASG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND pty.pension_sub_category = 'FUR_S'
AND prmtr_03 = 'Y'
AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
AND person_id = (SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
)
AND bec.business_group_id = p_business_group_id;
SELECT 1
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ORG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND pty.pension_sub_category = 'FUR_S'
AND prmtr_03 = 'Y'
AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
AND bec.business_group_id = p_business_group_id;
SELECT old_val1,new_val1,old_val2,new_val2
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ASG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND prmtr_03 = 'Y'
AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
AND pty.pension_sub_category = 'FUR_S'
AND person_id = (SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
)
AND bec.business_group_id = p_business_group_id
AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
ORDER BY ext_chg_evt_log_id;
SELECT old_val1,new_val1,old_val2,new_val2
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ORG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND prmtr_03 = 'Y'
AND pty.pension_sub_category = 'FUR_S'
AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
AND bec.business_group_id = p_business_group_id
AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND chg_eff_dt >= c_hire_date
ORDER BY ext_chg_evt_log_id;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT ORG_STRUCTURE_VERSION_ID
FROM per_org_structure_versions_v
WHERE organization_structure_id = c_hierarchy_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot);
SELECT ORG_STRUCTURE_VERSION_ID
FROM per_org_structure_versions_v
WHERE business_group_id = p_business_group_id
AND p_effective_date BETWEEN date_from
AND Nvl( date_to,Hr_Api.g_eot);
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = p_business_group_id;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_effective_date;
SELECT pty.pension_type_id
FROM pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_type_extra_info pei,
pqp_pension_types_f pty
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND paa.assignment_id = p_assignment_id
AND pei.element_type_id = prr.element_type_id
AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
AND pty.pension_sub_category = 'FUR_S';
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND TRUNC(p_effective_date) BETWEEN effective_start_date
AND effective_end_date;
SELECT 1
FROM ben_ext_chg_evt_log bec
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ASG'
AND prmtr_04 = 'PPP'
AND prmtr_03 = 'Y'
AND fnd_number.canonical_to_number(prmtr_10) = p_assignment_id
-- AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
-- AND g_extract_params(p_business_group_id).extract_end_date
AND bec.business_group_id = p_business_group_id;
SELECT 1
FROM per_assignment_extra_info paei,
pqp_pension_types_f pty
WHERE paei.information_type = 'NL_ABP_PI'
AND paei.aei_information_category = 'NL_ABP_PI'
AND paei.assignment_id = p_assignment_id
AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id
AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
AND pty.pension_sub_category IN ('PPP')
AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT 1
FROM pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_type_extra_info pei,
pqp_pension_types_f pty
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND paa.assignment_id = p_assignment_id
AND pei.element_type_id = prr.element_type_id
AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
AND pty.pension_sub_category = 'PPP';*/
SELECT 1
FROM per_all_assignments_f paf,
hr_organization_information hoi,
pqp_pension_types_f pty,
pay_all_payrolls_f ppf
WHERE paf.assignment_id = p_assignment_id
AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.payroll_id = ppf.payroll_id
AND ppf.prl_information_category = 'NL'
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND (paf.organization_id = hoi.organization_id
OR
(fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id
AND NOT EXISTS (SELECT 1
FROM hr_organization_information hoi1
WHERE hoi1.org_information_context = 'PQP_NL_ABP_PT'
AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
AND hoi1.organization_id = paf.organization_id
AND ( NVL(hoi1.org_information6,'N')= 'N'
OR NVL(hoi1.org_information7,'N')= 'N')
AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
fnd_date.date_to_canonical(hr_api.g_eot))))
))
AND hoi.org_information_context = 'PQP_NL_ABP_PT'
AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)
AND p_effective_date BETWEEN pty.effective_start_date AND pty.effective_end_date
AND pty.pension_sub_category IN ('PPP')
AND NVL(hoi.org_information6,'N') = 'Y'
AND NVL(hoi.org_information7,'N') = 'Y'
AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT decode (pty.pension_sub_category,'FPU_B','S'
,'FPU_C','S'
,'FPU_E','C'
,'FPU_R','A'
,'FPU_S','S'
,'FPU_T' ,'B') fpu_code
FROM pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_type_extra_info pei,
pqp_pension_types_f pty
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND paa.assignment_id = p_assignment_id
AND pei.element_type_id = prr.element_type_id
AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
AND pty.pension_sub_category IN ('FPU_B',
'FPU_E',
'FPU_R',
'FPU_S',
'FPU_T');*/
SELECT DISTINCT DECODE (pty.pension_sub_category,'FPU_B','S'
,'FPU_C','S'
,'FPU_E','C'
,'FPU_R','A'
,'FPU_S','S'
,'FPU_T' ,'B') fpu_code
FROM per_all_assignments_f paf,
hr_organization_information hoi,
pqp_pension_types_f pty,
pay_all_payrolls_f ppf
WHERE paf.assignment_id = p_assignment_id
AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.payroll_id = ppf.payroll_id
AND ppf.prl_information_category = 'NL'
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND (paf.organization_id = hoi.organization_id
OR
(fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id
AND NOT EXISTS (SELECT 1
FROM hr_organization_information hoi1
WHERE hoi1.org_information_context = 'PQP_NL_ABP_PT'
AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
AND hoi1.organization_id = paf.organization_id
AND ( NVL(hoi1.org_information6,'N')= 'N'
OR NVL(hoi1.org_information7,'N')= 'N')
AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
fnd_date.date_to_canonical(hr_api.g_eot))))
))
AND hoi.org_information_context = 'PQP_NL_ABP_PT'
AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)
AND p_effective_date BETWEEN pty.effective_start_date AND pty.effective_end_date
AND pty.pension_sub_category IN ('FPU_B',
'FPU_E',
'FPU_R',
'FPU_S',
'FPU_T')
AND NVL(hoi.org_information6,'N') = 'Y'
AND NVL(hoi.org_information7,'N') = 'Y'
AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
fnd_date.date_to_canonical(hr_api.g_eot)))
AND NOT EXISTS (SELECT 1
FROM per_assignment_extra_info paei1
WHERE paei1.information_type='NL_ABP_RI'
AND paei1.aei_information_category='NL_ABP_RI'
AND paei1.assignment_id = p_assignment_id
AND paei1.aei_information3 = 'G'
AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
fnd_date.date_to_canonical(hr_api.g_eot))));
SELECT DISTINCT DECODE (pty.pension_sub_category,'FPU_B','S'
,'FPU_C','S'
,'FPU_E','C'
,'FPU_R','A'
,'FPU_S','S'
,'FPU_T' ,'B') fpu_code
FROM per_assignment_extra_info paei,
pqp_pension_types_f pty
WHERE paei.information_type = 'NL_ABP_PI'
AND paei.aei_information_category = 'NL_ABP_PI'
AND paei.assignment_id = p_assignment_id
AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id
AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
AND pty.pension_sub_category IN ('FPU_B',
'FPU_E',
'FPU_R',
'FPU_S',
'FPU_T')
AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
fnd_date.date_to_canonical(hr_api.g_eot)))
AND NOT EXISTS (SELECT 1
FROM per_assignment_extra_info paei1
WHERE paei1.information_type='NL_ABP_RI'
AND paei1.aei_information_category='NL_ABP_RI'
AND paei1.assignment_id = p_assignment_id
AND paei1.aei_information3 = 'G'
AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
fnd_date.date_to_canonical(hr_api.g_eot))));
SELECT Substr(Nvl(aei_information4,'G'),0,1)
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
AND aei_information_category = 'NL_ABP_RI'
AND information_type = 'NL_ABP_RI';
SELECT fnd_date.canonical_to_date(aei_information1) start_dt
,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
,aei_information4 kind
,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND aei_information4 IS NOT NULL
AND paei.information_type = 'NL_ABP_PAR_INFO'
AND fnd_date.canonical_to_date(aei_information1)
BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND NOT EXISTS ( SELECT 1
FROM per_assignment_extra_info paei1
WHERE paei1.assignment_id = p_assignment_id
AND paei1.information_type = 'NL_ABP_PAR_INFO'
AND fnd_date.canonical_to_date(paei1.aei_information1) <
g_extract_params(p_business_group_id).extract_start_date
AND paei1.aei_information4 IS NOT NULL
and paei1.aei_information4 = paei.aei_information4
AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1
)
UNION
SELECT fnd_date.canonical_to_date(aei_information1) start_dt
,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
,aei_information4 kind
,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND aei_information4 IS NOT NULL
AND paei.information_type = 'NL_ABP_PAR_INFO'
AND fnd_date.canonical_to_date(aei_information1)
< g_extract_params(p_business_group_id).extract_start_date
AND EXISTS ( SELECT 1 FROM
ben_ext_chg_evt_log blog
WHERE blog.person_id = g_person_id
AND blog.chg_evt_cd = 'COAPKOP'
AND blog.prmtr_10 = paei.assignment_id
AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id
AND fnd_date.canonical_to_date(blog.prmtr_09) BETWEEN
g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date)
ORDER BY start_dt;
SELECT 1
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND aei_information4 = c_ptpn_kind
AND aei_information4 is not null
AND paei.information_type = 'NL_ABP_PAR_INFO'
AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
SELECT pet.element_type_id base_ele
,pei.eei_information12 sub_cat
,pei.eei_information18 cy_retro_ele
,pei.eei_information19 py_retro_ele
,pei.eei_information2 pt_id
FROM pay_element_type_extra_info pei,
pay_element_types_f pet
WHERE pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND p_effective_date BETWEEN To_Date(pei.eei_information10,'DD/MM/RRRR') AND
To_Date(pei.eei_information11,'DD/MM/RRRR')
AND p_effective_date BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND pet.element_type_id = pei.element_type_id
AND pet.business_group_id = p_business_group_id
AND pei.EEI_INFORMATION12 = p_sub_cat;
SELECT fnd_date.canonical_to_date(aei_information1) start_dt
,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
AND paei.information_type = 'NL_ABP_PI'
AND fnd_date.canonical_to_date(aei_information1)
BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND NOT EXISTS ( SELECT 1
FROM per_assignment_extra_info paei1
WHERE paei1.assignment_id = p_assignment_id
AND paei1.information_type = 'NL_ABP_PI'
AND fnd_date.canonical_to_date(paei1.aei_information1) <
g_extract_params(p_business_group_id).extract_start_date
AND fnd_number.canonical_to_number(paei1.aei_information3) = c_pt_id
AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1
)
UNION
SELECT fnd_date.canonical_to_date(aei_information1) start_dt
,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
AND paei.information_type = 'NL_ABP_PI'
AND fnd_date.canonical_to_date(aei_information1)
< g_extract_params(p_business_group_id).extract_start_date
AND EXISTS ( SELECT 1 FROM
ben_ext_chg_evt_log blog
WHERE blog.person_id = g_person_id
AND blog.chg_evt_cd = 'COAPP'
AND blog.prmtr_10 = paei.assignment_id
AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id
AND fnd_date.canonical_to_date(blog.prmtr_09) BETWEEN
g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date)
ORDER BY start_dt ;
SELECT 1
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
AND paei.information_type = 'NL_ABP_PI'
AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT Nvl(aei_information4,'WNE') kind,
LEAST(Nvl(fnd_number.canonical_to_number(aei_information5),1),1) VALUE
FROM per_assignment_extra_info
WHERE information_type = 'NL_ABP_PAR_INFO'
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND assignment_id = p_assignment_id
AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_effective_date;
SELECT pty.pension_type_id
,pty.pension_sub_category sub_cat
FROM pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_type_extra_info pei,
pqp_pension_types_f pty
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND paa.assignment_id = p_assignment_id
AND pei.element_type_id = prr.element_type_id
AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
AND (pty.pension_sub_category LIKE 'FPU%'
OR pty.pension_sub_category LIKE 'OPNP%'
OR pty.pension_sub_category = 'PPP');
SELECT /*decode(nvl(leaving_reason ,'A'),'D','I','A') term_reas*/
decode(nvl(leaving_reason ,'A'),'D','I','B','B','A') term_reas
FROM per_periods_of_service pps,
per_all_assignments_f asg
WHERE asg.period_of_service_id = pps.period_of_service_id
AND assignment_id = p_assignment_id
AND p_effective_date between effective_start_date and
effective_end_date ;
SELECT asg.effective_start_date Start_Date
,asg.effective_end_date End_Date
,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
,per_assignment_status_types past
WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.assignment_id = c_asg_id
AND target.enabled_flag = 'Y'
AND asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date BETWEEN
trunc(g_extract_params(p_business_group_id).extract_start_date)
AND trunc(g_extract_params(p_business_group_id).extract_end_date)
ORDER BY START_DATE;
SELECT TRUNC(date_start)
FROM per_periods_of_service
WHERE period_of_service_id = c_pos_id
AND TRUNC(date_start) = trunc(actual_termination_date);
SELECT dtl.val_07,
DECODE(dtl.val_09,' ','A')
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 5
AND NVL(dtl.val_07,'X') <> '00000000'
AND dtl.val_04 = c_asg_seq_no
order by ext_rslt_dtl_id desc;
SELECT 1
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 5
AND NVL(dtl.val_05,'X') = '00000000'
AND NVL(dtl.val_06,'X') = '00000000'
AND NVL(dtl.val_07,'X') = '00000000'
AND NVL(dtl.val_08,'X') = c_in_term_date
AND dtl.val_04 = c_asg_seq_no
order by ext_rslt_dtl_id desc;
SELECT old_val1 old_date,
new_val1 new_date
FROM ben_ext_chg_evt_log
WHERE person_id = c_person_id
AND chg_evt_cd = 'COPOS'
AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
ORDER BY ext_chg_evt_log_id desc;
SELECT 1
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
AND paei.information_type = 'NL_ABP_PI'
AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
SELECT 1
FROM per_assignment_extra_info paei
WHERE paei.assignment_id = p_assignment_id
AND aei_information4 = c_ptpn_kind
AND aei_information4 is not null
AND paei.information_type = 'NL_ABP_PAR_INFO'
AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
SELECT lookup_code sub_cat, DECODE(lookup_code,'PPP','1'
,'OPNP','G'
,'OPNP_65','A'
,'OPNP_AOW','G'
,'OPNP_W25','B'
,'OPNP_W50','C'
,'FPU_B','S'
,'FPU_E','C'
,'FPU_R','A'
,'FPU_S','S'
,'FPU_T','B'
,' ') code
FROM fnd_lookup_values
WHERE lookup_type = 'PQP_PENSION_SUB_CATEGORY'
AND lookup_code IN ('PPP','OPNP','OPNP_65','OPNP_AOW'
,'OPNP_W25','OPNP_W50','FPU_B','FPU_E'
,'FPU_R','FPU_S','FPU_T')
AND NVL(enabled_flag,'N') = 'Y'
AND language = 'US';
SELECT 1
FROM ben_ext_rslt_dtl dtl
,ben_ext_rslt res
,ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id IN (SELECT ext_dfn_id
FROM pqp_extract_attributes
WHERE ext_dfn_type = 'NL_FPR')
and dtl.person_id = g_person_id
and ext_stat_cd = 'A'
AND TRUNC(res.eff_dt) < TRUNC(g_extract_params(p_business_group_id).extract_start_date)
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND dfn.ext_dfn_id = res.ext_dfn_id
and dtl.ext_rslt_id = res.ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 5;
SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
,per_assignment_status_types past
WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND asg.assignment_id = c_asg_id
AND target.enabled_flag = 'Y'
AND asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND trunc(c_eff_date) BETWEEN asg.effective_start_date AND
asg.effective_end_date
order by asg.effective_start_date;
SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date AND
effective_end_date;
SELECT MIN(effective_start_date) - 1 term_date
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y')
AND assignment_type = 'E';*/
SELECT 'Y'
FROM pay_element_types_f petf
,pay_element_entries_f peef
WHERE petf.element_name = 'ABP flex workers PTP End Date'
AND petf.element_type_id = peef.element_type_id
AND peef.assignment_id = p_assignment_id
AND p_effective_date BETWEEN peef.effective_start_date
AND peef.effective_end_date;
l_rec_05_retro_ptp.DELETE;
g_rec05_rows.DELETE(l_terminated_row);
l_out_retro_ptpn_kind.DELETE;
l_out_retro_ptpn.DELETE;
hr_utility.set_location('....Inserting Rec 05 as PTP has changed : ',24);
hr_utility.set_location('....Inside the IF condition to update the end date: ',24);
SELECT 1
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ASG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND pty.pension_sub_category = 'IPAP'
AND prmtr_03 = 'Y'
AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
AND person_id = (SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
)
AND bec.business_group_id = p_business_group_id;
SELECT 1
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ORG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND pty.pension_sub_category = 'IPAP'
AND prmtr_03 = 'Y'
AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
AND bec.business_group_id = p_business_group_id;
SELECT old_val1,new_val1,old_val2,new_val2
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ASG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND prmtr_03 = 'Y'
AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
AND pty.pension_sub_category = 'IPAP'
AND person_id = (SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
)
AND bec.business_group_id = p_business_group_id
AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
ORDER BY ext_chg_evt_log_id;
SELECT old_val1,new_val1,old_val2,new_val2
FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
WHERE chg_evt_cd = 'COAPPD'
AND prmtr_01 = 'ORG'
AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
AND prmtr_03 = 'Y'
AND pty.pension_sub_category = 'IPAP'
AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
AND bec.business_group_id = p_business_group_id
AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND chg_eff_dt >= c_hire_date
ORDER BY ext_chg_evt_log_id;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT ORG_STRUCTURE_VERSION_ID
FROM per_org_structure_versions_v
WHERE organization_structure_id = c_hierarchy_id
AND p_effective_date BETWEEN date_from
AND Nvl(date_to,Hr_Api.g_eot);
SELECT ORG_STRUCTURE_VERSION_ID
FROM per_org_structure_versions_v
WHERE business_group_id = p_business_group_id
AND p_effective_date BETWEEN date_from
AND Nvl( date_to,Hr_Api.g_eot);
SELECT organization_id_parent
FROM per_org_structure_elements
WHERE organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = p_business_group_id;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_effective_date;
SELECT pty.pension_type_id
FROM pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_type_extra_info pei,
pqp_pension_types_f pty
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND paa.assignment_id = p_assignment_id
AND pei.element_type_id = prr.element_type_id
AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
AND pty.pension_sub_category = 'IPAP';
SELECT old_val1 old_date,
new_val1 new_date
FROM ben_ext_chg_evt_log
WHERE person_id = c_person_id
AND chg_evt_cd = 'COPOS'
AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
ORDER BY ext_chg_evt_log_id desc;
SELECT 1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND aei_information_category = 'NL_ABP_PAR_INFO'
AND information_type = 'NL_ABP_PAR_INFO'
AND trunc(c_nh_date) BETWEEN fnd_date.canonical_to_date(aei_information1)
AND fnd_date.canonical_to_date(nvl(aei_information2,
fnd_date.date_to_canonical(hr_api.g_eot)))
AND aei_information6 IS NOT NULL;
SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
,fnd_date.canonical_to_date(prmtr_02)
FROM ben_ext_chg_evt_log
WHERE person_id = g_person_id
AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND chg_evt_cd = 'COAPS'
AND fnd_number.canonical_to_number(nvl(new_val1,'0')) <> 0
AND fnd_date.canonical_to_date(prmtr_02) BETWEEN c_start and c_end;
SELECT date_earned
INTO l_max_de
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id =l_de_asg_act;
SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO l_ext_rslt_dtl_id FROM dual;
INSERT INTO ben_ext_rslt_dtl
( EXT_RSLT_DTL_ID
,EXT_RSLT_ID
,BUSINESS_GROUP_ID
,EXT_RCD_ID
,PERSON_ID
,VAL_01
,VAL_02
,VAL_03
,VAL_04
,VAL_05
,VAL_06
,VAL_07
,VAL_08
,VAL_09
,VAL_10
,VAL_25
,VAL_26
,VAL_70
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,OBJECT_VERSION_NUMBER
,PRMY_SORT_VAL
,SCND_SORT_VAL
,THRD_SORT_VAL
,TRANS_SEQ_NUM
,RCRD_SEQ_NUM)
VALUES( l_ext_rslt_dtl_id
,l_main_rec.EXT_RSLT_ID
,l_main_rec.BUSINESS_GROUP_ID
,l_main_rec.EXT_RCD_ID
,l_main_rec.PERSON_ID
,l_main_rec.VAL_01
,l_main_rec.VAL_02
,l_main_rec.VAL_03
,l_seq_num
,l_pension_sal_char
,l_main_rec.VAL_06
,l_pension_yr_char
,l_main_rec.VAL_08
,l_main_rec.VAL_09
,l_main_rec.VAL_10
,l_main_rec.VAL_25
,l_main_rec.VAL_26
,l_main_rec.VAL_70
,l_main_rec.CREATED_BY
,l_main_rec.CREATION_DATE
,l_main_rec.LAST_UPDATE_DATE
,l_main_rec.LAST_UPDATED_BY
,l_main_rec.LAST_UPDATE_LOGIN
,l_main_rec.PROGRAM_APPLICATION_ID
,l_main_rec.PROGRAM_ID
,l_main_rec.PROGRAM_UPDATE_DATE
,l_main_rec.REQUEST_ID
,l_main_rec.OBJECT_VERSION_NUMBER
,l_main_rec.PRMY_SORT_VAL
,l_main_rec.SCND_SORT_VAL
,l_main_rec.THRD_SORT_VAL
,l_main_rec.TRANS_SEQ_NUM
,l_main_rec.RCRD_SEQ_NUM);
SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO l_ext_rslt_dtl_id FROM dual;
INSERT INTO ben_ext_rslt_dtl
( EXT_RSLT_DTL_ID
,EXT_RSLT_ID
,BUSINESS_GROUP_ID
,EXT_RCD_ID
,PERSON_ID
,VAL_01
,VAL_02
,VAL_03
,VAL_04
,VAL_05
,VAL_06
,VAL_07
,VAL_08
,VAL_09
,VAL_10
,VAL_25
,VAL_26
,VAL_70
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,OBJECT_VERSION_NUMBER
,PRMY_SORT_VAL
,SCND_SORT_VAL
,THRD_SORT_VAL
,TRANS_SEQ_NUM
,RCRD_SEQ_NUM)
VALUES( l_ext_rslt_dtl_id
,l_main_rec.EXT_RSLT_ID
,l_main_rec.BUSINESS_GROUP_ID
,l_main_rec.EXT_RCD_ID
,l_main_rec.PERSON_ID
,l_main_rec.VAL_01
,l_main_rec.VAL_02
,l_main_rec.VAL_03
,l_main_rec.VAL_04
,l_main_rec.VAL_05
,l_main_rec.VAL_06
,to_char(l_new_hire_dt,'YYYYMMDD')
,l_main_rec.VAL_08
,l_main_rec.VAL_09
,l_main_rec.VAL_10
,l_main_rec.VAL_25
,l_main_rec.VAL_26
,l_main_rec.VAL_70
,l_main_rec.CREATED_BY
,l_main_rec.CREATION_DATE
,l_main_rec.LAST_UPDATE_DATE
,l_main_rec.LAST_UPDATED_BY
,l_main_rec.LAST_UPDATE_LOGIN
,l_main_rec.PROGRAM_APPLICATION_ID
,l_main_rec.PROGRAM_ID
,l_main_rec.PROGRAM_UPDATE_DATE
,l_main_rec.REQUEST_ID
,l_main_rec.OBJECT_VERSION_NUMBER
,l_main_rec.PRMY_SORT_VAL
,l_main_rec.SCND_SORT_VAL
,l_main_rec.THRD_SORT_VAL
,l_main_rec.TRANS_SEQ_NUM
,l_main_rec.RCRD_SEQ_NUM);
SELECT paa.assignment_action_id
FROM pay_payroll_actions ppa,pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND paa.assignment_id = p_assignment_id
AND paa.assignment_action_id IN
(SELECT assignment_action_id
FROM pay_run_results
WHERE element_type_id IN
(SELECT element_type_id
FROM pay_element_type_extra_info,pqp_pension_types_f pty
WHERE information_type = 'PQP_NL_ABP_DEDUCTION'
AND eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
AND pty.pension_sub_category = 'FUR_S'
)
);
SELECT prr.assignment_action_id,
pei.eei_information9
FROM pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_type_extra_info pei,
pqp_pension_types_f pty
WHERE paa.assignment_action_id = prr.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND paa.assignment_id = p_assignment_id
AND pei.element_type_id = prr.element_type_id
AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
AND pty.pension_sub_category = p_sub_cat;
SELECT paa.assignment_action_id
FROM pay_payroll_actions ppa,pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned BETWEEN g_extract_params(p_business_group_id).extract_start_date
AND g_extract_params(p_business_group_id).extract_end_date
AND paa.assignment_id = p_assignment_id
AND paa.assignment_action_id IN
(SELECT assignment_action_id
FROM pay_run_results
WHERE element_type_id IN
(SELECT element_type_id
FROM pay_element_type_extra_info,pqp_pension_types_f pty
WHERE information_type = 'PQP_NL_ABP_DEDUCTION'
AND eei_information_category = 'PQP_NL_ABP_DEDUCTION'
AND eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
AND pty.pension_sub_category = 'IPAP'
)
);
SELECT To_Char(p_effective_date,'MM')
FROM dual;
SELECT TO_CHAR(p_effective_date,'YYYY')
FROM dual;
SELECT Substr(Nvl(aei_information7,'D'),0,1)
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
AND aei_information_category = 'NL_ABP_RI'
AND information_type = 'NL_ABP_RI';
SELECT Substr(Nvl(aei_information5,'01'),0,2),Substr(Nvl(aei_information6,'01'),0,2)
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND aei_information_category = 'NL_ABP_RI'
AND information_type = 'NL_ABP_RI'
AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
SELECT Nvl(aei_information3,'0')
FROM per_assignment_extra_info
WHERE information_type = 'NL_USZO_INFO'
AND aei_information_category = 'NL_USZO_INFO'
AND assignment_id = p_assignment_id
AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
AND ROWNUM = 1;
SELECT scl.SEGMENT2||scl.SEGMENT3
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
SELECT fnd_date.canonical_to_date(aei_information3)
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'PQP_NL_ABP_OLD_EE_INFO';
SELECT national_identifier
,UPPER(last_name)
,UPPER(pre_name_adjunct)
,TO_CHAR(date_of_birth,'YYYYMMDD')
FROM per_all_people_f
WHERE person_id = p_person_id
AND g_extract_params(p_business_group_id).extract_end_date
BETWEEN effective_start_date AND effective_end_date ;
l_main_rec.program_update_date := SYSDATE;
l_rec_09_values.delete;
DELETE ben_ext_rslt_dtl
WHERE ext_rslt_dtl_id = l_main_rec.ext_rslt_dtl_id
AND person_id = l_person_id;
DELETE ben_ext_rslt_dtl
WHERE ext_rslt_dtl_id = l_main_rec.ext_rslt_dtl_id
AND person_id = l_person_id;
g_primary_assig.DELETE(l_assignment_id);
g_retro_hires.DELETE;
g_rec05_rows.delete;
l_rec_09_values.delete;
l_rec_12_values.delete;
SELECT SUBSTR(NVL(org_information2,'-1'),0,7)
FROM hr_organization_information
WHERE org_information_context = 'PQP_ABP_PROVIDER'
AND organization_id = c_org_id;
SELECT Substr(org_information3,0,4)
FROM hr_organization_information
WHERE org_information_context = 'PQP_ABP_PROVIDER'
AND organization_id = p_org_id;
SELECT Sign(c_amount)
FROM dual;
SELECT Count(dtl.ext_rslt_dtl_id)
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
AND ext_rcd_id NOT IN(c_recordid_1,c_recordid_2)
AND val_25 = c_emr_id;
SELECT Count(dtl.ext_rslt_dtl_id)
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND ext_rcd_id IN(c_recordid_1,c_recordid_2,c_recordid_3)
AND val_25=c_emr_id;
SELECT DISTINCT(val_26) val_26
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND val_25= c_org_id
ORDER BY val_26 ASC ;
SELECT DISTINCT(val_26) val_26
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND val_25 IS NOT NULL
AND val_26 IS NOT NULL
ORDER BY val_26 ASC ;
SELECT val_25,val_26
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
ORDER BY val_26 ASC ;
SELECT *
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
-- AND dtl.person_id = c_person_id
AND dtl.val_26 =c_val_26;
SELECT *
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND ext_rcd_id= c_ext_dtl_rcd_id;
SELECT *
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND ext_rcd_id= c_ext_dtl_rcd_id;
SELECT 'x'
FROM ben_ext_rslt_dtl
WHERE ext_rslt_id=Ben_Ext_Thread.g_ext_rslt_id
AND val_25=c_org_id;
SELECT rcd.ext_rcd_id,rin.seq_num
FROM ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id = Ben_Ext_Thread.g_ext_dfn_id
AND rin.ext_file_id = dfn.ext_file_id
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND rin.seq_num = 5;
SELECT ext_rslt_dtl_id
FROM ben_ext_rslt_dtl
WHERE ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND ext_rcd_id = p_ext_rcd_id
AND val_05 = '00000000'
AND val_06 = '00000000'
AND val_07 = '00000000'
AND val_08 = '00000000'
AND val_10 = '00000000'
AND val_17 = '00000000'
AND business_group_id = p_business_group_id;
l_00_inserted Number := 0;
l_insert_trailer Number := 1;
SELECT put.USER_TABLE_ID, puc.USER_COLUMN_ID
FROM PAY_USER_TABLES put,
PAY_USER_COLUMNS puc
WHERE
put.USER_TABLE_ID = puc.USER_TABLE_ID
AND put.LEGISLATION_CODE = puc.LEGISLATION_CODE
AND put.USER_TABLE_NAME = 'NL_DIACRITICAL_MARKS'
AND put.LEGISLATION_CODE = 'NL';
select UPPER(purf.ROW_LOW_RANGE_OR_NAME) ROW_LOW_RANGE_OR_NAME, UPPER(pucif.VALUE) VALUE
from
PAY_USER_COLUMN_INSTANCES_F pucif,
PAY_USER_ROWS_F purf
where
pucif.USER_COLUMN_ID = p_USER_COLUMN_ID
AND purf.USER_TABLE_ID = p_USER_TABLE_ID
AND pucif.USER_ROW_ID = purf.USER_ROW_ID
AND pucif.business_group_id = purf.business_group_id
AND pucif.business_group_id = p_business_group_id
AND g_extract_params(p_business_group_id).extract_start_date BETWEEN pucif.EFFECTIVE_START_DATE AND pucif.EFFECTIVE_END_DATE
AND g_extract_params(p_business_group_id).extract_start_date BETWEEN purf.EFFECTIVE_START_DATE AND purf.EFFECTIVE_END_DATE;
DELETE ben_ext_rslt_dtl
WHERE ext_rcd_id = csr_rcd_rec.ext_rcd_id
AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND business_group_id = p_business_group_id;
DELETE ben_ext_rslt_dtl
WHERE ext_rslt_dtl_id = csr_rec05_del_rec.ext_rslt_dtl_id;
IF l_00_inserted=0 THEN
Ins_Rslt_Dtl(p_dtl_rec => l_header_new_rec);
l_00_inserted:=1;
l_insert_trailer := 1;
l_insert_trailer := 1;
l_insert_trailer := 0;
l_insert_trailer := 1;
l_insert_trailer := 0;
l_insert_trailer := 1;
l_insert_trailer := 0;
l_insert_trailer := 1;
l_insert_trailer := 0;
--Inserting new ones
IF l_insert_trailer = 1 THEN
Ins_Rslt_Dtl(p_dtl_rec => l_trailer_new_rec);
DELETE
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND dtl.ext_rcd_id = csr_rcd_rec.ext_rcd_id
AND dtl.ext_rslt_dtl_id = l_ext_rslt_dtl_id
AND business_group_id = p_business_group_id;
DELETE
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND dtl.ext_rcd_id = csr_rcd_rec.ext_rcd_id
AND dtl.ext_rslt_dtl_id = l_ext_rslt_dtl_id
AND business_group_id = p_business_group_id;
UPDATE ben_ext_rslt_dtl
SET VAL_07 = translate(VAL_07,l_SOURCE,l_DEST),
VAL_08 = translate(VAL_08,l_SOURCE,l_DEST),
VAL_09 = translate(VAL_09,l_SOURCE,l_DEST),
VAL_12 = translate(VAL_12,l_SOURCE,l_DEST),
VAL_13 = translate(VAL_13,l_SOURCE,l_DEST)
WHERE EXT_RSLT_ID = Ben_Ext_Thread.g_ext_rslt_id
AND VAL_01 = '02'
AND BUSINESS_GROUP_ID = p_business_group_id;
UPDATE ben_ext_rslt_dtl
SET VAL_07 = translate(VAL_07,l_SOURCE,l_DEST),
VAL_11 = translate(VAL_11,l_SOURCE,l_DEST),
VAL_13 = translate(VAL_13,l_SOURCE,l_DEST),
VAL_14 = translate(VAL_14,l_SOURCE,l_DEST)
WHERE EXT_RSLT_ID = Ben_Ext_Thread.g_ext_rslt_id
AND VAL_01 = '04'
AND BUSINESS_GROUP_ID = p_business_group_id;