[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fnd_number.canonical_to_number(
pay_fr_ducs.get_parameter(legislative_parameters, 'COMPANY_ID'))
,pay_fr_ducs.get_parameter(legislative_parameters, 'PERIOD_TYPE')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
Purpose : This returns the select statement that is used to created the
range rows.
------------------------------------------------------------------------*/
PROCEDURE range_code(p_payroll_action_id in number
,sqlstr out nocopy varchar2) IS
-- Local Variable
l_proc VARCHAR2(60) := g_package||' range_cursor ';
SELECT payact.payroll_action_id
FROM pay_payroll_actions payact
,pay_action_information ref_actinfo
WHERE payact.payroll_action_id = ref_actinfo.action_context_id
and ref_actinfo.action_information_category = 'FR_DUCS_REFERENCE_INFO'
and ref_actinfo.action_context_type = 'PA'
and ref_actinfo.action_information1 = p_company_id_chr
and ref_actinfo.action_information2 = l_period_code
and payact.business_group_id = g_business_group_id
and payact.payroll_action_id <> p_payroll_action_id;
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT /*+ ORDERED */ assact.assignment_id
, assact.assignment_action_id
, assact.tax_unit_id establishment_id
, payact.action_type
FROM pay_assignment_actions assact
, pay_payroll_actions payact
, hr_organization_information cmp_check
WHERE assact.source_action_id is null
AND assact.action_status = 'C'
AND assact.payroll_action_id = payact.payroll_action_id
AND payact.effective_date between g_period_start_date
and g_effective_date
AND assact.assignment_id in
(select assignment_id
from per_all_assignments_f asg
where asg.business_group_id+0 = g_business_group_id
and asg.person_id between p_start_person_id and p_end_person_id
and asg.effective_end_date >= g_period_start_date
and asg.effective_start_date <= g_effective_date
and asg.period_of_service_id is not null)
AND (payact.action_type in ('Q','R','B','I') or
(payact.action_type = 'X'
and payact.report_type = 'DUCS_ARCHIVE'
and payact.report_qualifier = 'FR'
and payact.report_category = 'DUCS_ARCHIVE'
AND legislative_parameters like '%COMPANY_ID='||
p_company_id_chr ||' %'))
/* Bug 2309322 Run assg_actions restricted by company */
AND assact.tax_unit_id = cmp_check.organization_id
AND cmp_check.org_information_context = 'FR_ESTAB_INFO'
AND cmp_check.org_information1 = p_company_id_chr;
SELECT /*+ ORDERED */ assact.assignment_id
, assact.assignment_action_id
, assact.tax_unit_id establishment_id
, payact.action_type
FROM pay_population_ranges pop
, per_periods_of_service pos
, per_all_assignments_f asg
, pay_assignment_actions assact
, pay_payroll_actions payact
, hr_organization_information cmp_check
WHERE pop.payroll_action_id = p_payroll_action_id
and pop.chunk_number = p_chunk
and asg.business_group_id+0 = g_business_group_id
and asg.effective_end_date >= g_period_start_date
and asg.effective_start_date <= g_effective_date
and asg.period_of_service_id = pos.period_of_service_id
and pos.person_id = pop.person_id
and assact.source_action_id is null
and assact.action_status = 'C'
AND assact.payroll_action_id = payact.payroll_action_id
AND payact.effective_date between g_period_start_date
and g_effective_date
AND (asg.effective_start_date,assact.assignment_id) =
(select max(asg2.effective_start_date), asg2.assignment_id
from per_all_assignments_f asg2
where asg.assignment_id = asg2.assignment_id
and asg2.effective_end_date >= g_period_start_date
and asg2.effective_start_date <= g_effective_date
group by asg2.assignment_id)
AND (payact.action_type in ('Q','R','B','I') or
(payact.action_type = 'X'
and payact.report_type = 'DUCS_ARCHIVE'
and payact.report_qualifier = 'FR'
and payact.report_category = 'DUCS_ARCHIVE'
AND legislative_parameters like '%COMPANY_ID='||
p_company_id_chr ||' %'))
/* Bug 2309322 Run assg_actions restricted by company */
AND assact.tax_unit_id = cmp_check.organization_id
AND cmp_check.org_information_context = 'FR_ESTAB_INFO'
AND cmp_check.org_information1 = p_company_id_chr;
SELECT /*+ ORDERED */ 1
FROM pay_action_interlocks plock
, pay_assignment_actions assact
, pay_action_information actinfo
WHERE plock.locked_action_id = p_run_act_id
AND plock.locking_action_id = assact.assignment_action_id
AND assact.payroll_action_id = actinfo.action_context_id
AND actinfo.action_context_type = 'PA'
AND actinfo.action_information_category = 'FR_DUCS_REFERENCE_INFO';
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
SELECT payact.action_type type,
locked_assact.assignment_action_id id,
locked_assact.tax_unit_id
FROM pay_action_interlocks interlock
, pay_assignment_actions locked_assact
, pay_payroll_actions payact
WHERE interlock.locking_action_id = p_assignment_action_id
AND interlock.locked_action_id = locked_assact.assignment_action_id
AND locked_assact.payroll_action_id = payact.payroll_action_id;
SELECT 1 /* if the run action is reversed exclude it */
FROM pay_action_interlocks rev_interlock
, pay_assignment_actions rev_assact
, pay_payroll_actions rev_payact
WHERE rev_interlock.locked_action_id = p_run_act_id
AND rev_interlock.locking_action_id = rev_assact.assignment_action_id
AND rev_assact.action_status = 'C'
AND rev_payact.payroll_action_id = rev_assact.payroll_action_id
AND rev_payact.action_type = 'V'
AND rev_payact.action_status = 'C';
SELECT assact.chunk_number
,runchild.payroll_action_id
,runchild.assignment_action_id
,runchild.assignment_id
,runchild.tax_unit_id
,pay_assignment_actions_s.nextval new_ass_act_id
FROM pay_assignment_actions assact
,pay_action_interlocks interlock
,pay_assignment_actions runchild
WHERE assact.assignment_action_id = p_assignment_action_id
AND interlock.locking_action_id = assact.assignment_action_id
AND interlock.locked_action_id = runchild.source_action_id;
SELECT assact.assignment_action_id
,assact.tax_unit_id
,pay_assignment_actions_s.nextval new_ass_act_id
FROM pay_assignment_actions assact
WHERE assact.source_action_id = p_child_action_id;
update pay_assignment_actions
set action_status = 'C'
where assignment_action_id = grand_child.new_ass_act_id;
update pay_assignment_actions
set action_status = 'C'
where assignment_action_id = child.new_ass_act_id;
SELECT decode(substr(contribution_code,1,1),'1','URSSAF'
,'2','ASSEDIC'
,'3','AGIRC'
,'4','ARRCO') contribution_type
, contribution_code
, base
, source_asg_action_id
, nvl(process_path,' ') retro_process_path
, retro_adjustment_type
, sum(rate) rate
, sum(pv) pv
FROM (
SELECT /*+ ORDERED USE_NL(et) INDEX(et PAY_ELEMENT_TYPES_F_PK) */
rr.run_result_id
, nvl(epd.source_asg_action_id,rr.assignment_action_id) source_asg_action_id
, epd.process_path
, epd.adjustment_type retro_adjustment_type
, max(decode(iv.name,
g_english_contrib_code,rrv.result_value,
g_french_contrib_code, rrv.result_value)) contribution_code
, nvl(max(decode(iv.name,
g_english_base, fnd_number.canonical_to_number(rrv.result_value),
g_french_base, fnd_number.canonical_to_number(rrv.result_value))),0) base
, nvl(max(decode(iv.name,
g_english_rate, fnd_number.canonical_to_number(rrv.result_value),
g_french_rate, fnd_number.canonical_to_number(rrv.result_value))),0) rate
, nvl(max(decode(iv.name,
g_english_pay_value, decode(ec.classification_name,'Rebates',-1,1) *
fnd_number.canonical_to_number(rrv.result_value),
g_french_pay_value, decode(ec.classification_name,'Rebates',-1,1) *
fnd_number.canonical_to_number(rrv.result_value))),0) pv
FROM pay_action_interlocks ail,
pay_run_results rr,
pay_element_types_f et,
pay_element_classifications ec,
pay_input_values_f iv,
pay_run_result_values rrv,
pay_entry_process_details epd
WHERE ail.locking_action_id = p_assignment_action_id
AND rr.assignment_action_id = ail.locked_action_id
AND rr.element_type_id = et.element_type_id
AND et.classification_id = ec.classification_id
AND ec.classification_name in
('Statutory EE Deductions'
,'Statutory ER Charges'
,'CSG Non-Deductible'
,'Conventional EE Deductions'
,'Conventional ER Charges'
,'Rebates')
AND ec.legislation_code = 'FR'
AND g_effective_date between
et.effective_start_date and et.effective_end_date
AND rr.element_type_id = et.element_type_id
AND rrv.run_result_id = rr.run_result_id
AND rr.status in ('P','PA')
AND rrv.input_value_id = iv.input_value_id
AND iv.element_type_id = et.element_type_id
AND iv.name in (g_english_base,g_french_base
,g_english_rate,g_french_rate
,g_english_pay_value,g_french_pay_value
,g_english_contrib_code,g_french_contrib_code)
AND g_effective_date between
iv.effective_start_date and iv.effective_end_date
and epd.element_entry_id (+) = rr.element_entry_id
and epd.retro_component_id (+) is not null
GROUP BY rr.run_result_id,
nvl(epd.source_asg_action_id,rr.assignment_action_id),
epd.process_path,epd.adjustment_type
HAVING max(decode(iv.name,
g_english_contrib_code,rrv.result_value,
g_french_contrib_code, rrv.result_value)) < '5')
--
GROUP BY decode(substr(contribution_code,1,1),'1','URSSAF'
,'2','ASSEDIC'
,'3','AGIRC'
,'4','ARRCO')
, source_asg_action_id
, nvl(process_path,' ')
, retro_adjustment_type
, contribution_code,base
ORDER BY decode(substr(contribution_code,1,1),'1','URSSAF'
,'2','ASSEDIC'
,'3','AGIRC'
,'4','ARRCO')
, source_asg_action_id
, nvl(process_path,' ')
, retro_adjustment_type
, contribution_code;
SELECT tax_unit_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id;
SELECT org_information1 -- Org ID of Pension Provider
FROM hr_organization_information
WHERE organization_id = l_establishment_id
and org_information4 = p_Order_Number
and org_information_context = 'FR_ESTAB_PE_PRVS';
g_estab_pens_provs.pens_provs.delete;
SELECT meaning,tag
FROM fnd_lookup_values
WHERE lookup_type=p_lookup_type
AND lookup_code=p_lookup_code
AND language = userenv('LANG')
AND view_application_id = 3;
SELECT distinct paa.person_id
FROM pay_action_information pai
,pay_assignment_actions pac
,per_all_assignments_f paa
WHERE pac.payroll_action_id=p_payroll_action_id
and pai.action_information_category = 'FR_DUCS_ACTION_CONTRIB_INFO'
and pai.action_information1 = l_page_id_chr
and pai.action_information2 = p_page_type
and pai.action_context_id =pac.assignment_action_id
and paa.assignment_id=pac.assignment_id;
SELECT per.sex
FROM per_all_people_f per
WHERE per.person_id = l_person_id;
SELECT count(distinct pac.assignment_id),
count(distinct pac.assignment_action_id)
FROM pay_action_information pai
,pay_assignment_actions pac
WHERE pac.payroll_action_id=p_payroll_action_id
and pai.action_information_category = 'FR_DUCS_ACTION_CONTRIB_INFO'
and pai.action_information1 = l_page_id_chr
and pai.action_information2 = p_page_type
and pai.action_context_id =pac.assignment_action_id;
SELECT /*+ ORDERED */
contrib.action_information1 subpage_identifier
, contrib.action_information3 contribution_type
, substr(contrib.action_information4,1,1)||
translate(substr(contrib.action_information4,2,2), '1234567890',
decode(contrib.action_information8
,'FULL','1234567890'
,'PARTIAL','XXXXXXXXXX'))||
substr(contrib.action_information4,4,4) contribution_code
, round(sum(fnd_number.canonical_to_number(contrib.action_information5))) base
, fnd_number.canonical_to_number(contrib.action_information6) rate
, sum(fnd_number.canonical_to_number(contrib.action_information7)) pay_value
, count(distinct assact.assignment_id) number_of_employees
FROM pay_assignment_actions assact
, pay_action_information contrib
WHERE
assact.payroll_action_id in
(SELECT payroll_action_id
FROM pay_payroll_actions payact
, pay_action_information actinfo
WHERE payact.effective_date between g_period_start_date
and g_effective_date
and payact.payroll_action_id = actinfo.action_context_id
and actinfo.action_context_type = 'PA'
and actinfo.action_information_category = 'FR_DUCS_REFERENCE_INFO'
and payact.report_type = 'DUCS_ARCHIVE'
and payact.report_qualifier = 'FR'
and payact.report_category = 'DUCS_ARCHIVE')
and assact.assignment_action_id = contrib.action_context_id
and contrib.action_context_type = 'AAP'
and contrib.action_information1 = to_char(p_page_identifier)
and contrib.action_information2 = p_page_type
and contrib.action_information_category = 'FR_DUCS_ACTION_CONTRIB_INFO'
GROUP BY contrib.action_information1
, contrib.action_information3
, substr(contrib.action_information4,1,1)||
translate(substr(contrib.action_information4,2,2), '1234567890',
decode(contrib.action_information8 ,'FULL','1234567890'
,'PARTIAL','XXXXXXXXXX'))||
substr(contrib.action_information4,4,4)
, fnd_number.canonical_to_number(contrib.action_information6)
ORDER BY contrib.action_information1 ,contrib.action_information3;
SELECT /*+ ORDERED */
contrib.action_information1 subpage_identifier
, contrib.action_information3 contribution_type
, substr(contrib.action_information4,1,1)||
translate(substr(contrib.action_information4,2,2), '1234567890',
decode(contrib.action_information8
,'FULL','1234567890'
,'PARTIAL','XXXXXXXXXX'))||
substr(contrib.action_information4,4,4) contribution_code
, round(sum(fnd_number.canonical_to_number(contrib.action_information5))) base
, fnd_number.canonical_to_number(contrib.action_information6) rate
, sum(fnd_number.canonical_to_number(contrib.action_information7)) pay_value
, count(distinct assact.assignment_id) number_of_employees
FROM pay_payroll_actions payact
, pay_assignment_actions assact
, pay_action_information contrib
WHERE assact.payroll_action_id = payact.payroll_action_id
and payact.report_type = 'DUCS_ARCHIVE'
and payact.report_qualifier = 'FR'
and payact.report_category = 'DUCS_ARCHIVE'
and payact.effective_date between g_period_start_date and g_effective_date
and payact.business_group_id = g_business_group_id
and contrib.action_context_type = 'AAP'
and assact.assignment_action_id = contrib.action_context_id
and ((contrib.action_information1 in
(SELECT pens_prv.org_information1
FROM hr_organization_information pens_prv
WHERE pens_prv.org_information_id = p_page_identifier
AND pens_prv.org_information_context = 'FR_COMP_PE_PRVS'))
or
(contrib.action_information1 in
(SELECT fnd_number.number_to_canonical(ind_pens_prv.organization_id)
FROM hr_organization_information ind_pens_prv
, hr_organization_information pens_grp
WHERE pens_grp.org_information_id = p_page_identifier
AND pens_grp.org_information_context = 'FR_COMP_PE_PRVS'
AND ind_pens_prv.org_information3 = pens_grp.org_information1
AND ind_pens_prv.org_information_context = 'FR_PE_PRV_INFO')))
and contrib.action_information2 = p_page_type
and contrib.action_information_category = 'FR_DUCS_ACTION_CONTRIB_INFO'
GROUP BY contrib.action_information1
, contrib.action_information3
, substr(contrib.action_information4,1,1)||
translate(substr(contrib.action_information4,2,2), '1234567890',
decode(contrib.action_information8 ,'FULL','1234567890'
,'PARTIAL','XXXXXXXXXX'))||
substr(contrib.action_information4,4,4)
, fnd_number.canonical_to_number(contrib.action_information6)
ORDER BY contrib.action_information1, contrib.action_information3;
SELECT org_information1
INTO l_pension_provider
FROM hr_organization_information
WHERE organization_id = l_pension_provider_id
AND org_information_context = 'FR_PE_PRV_INFO';
SELECT payact.payroll_action_id
FROM pay_payroll_actions payact
,pay_action_information ref_actinfo
WHERE payact.payroll_action_id = ref_actinfo.action_context_id
and ref_actinfo.action_information_category = 'FR_DUCS_REFERENCE_INFO'
and ref_actinfo.action_context_type = 'PA'
and ref_actinfo.action_information1 = p_company_id_chr
and ref_actinfo.action_information2 = l_period_code
and payact.business_group_id = g_business_group_id
and payact.payroll_action_id <> p_payroll_action_id;
SELECT substr(o.name,1,150) company_name
, substr(l.address_line_1,1,150) company_address_line_1
, substr(l.address_line_2,1,150) company_address_line_2
, substr(l.region_3,1,150) company_address_line_3
, l.town_or_city company_address_line_4
, l.telephone_number_1 company_telephone
, l.telephone_number_2 company_fax
, rep_estab_info.org_information2 rep_estab_SIRET
, rep_estab_info.org_information3 rep_estab_NAF
, comp_rep_info.ORG_INFORMATION1 Declaration_Due_Offset
, comp_rep_info.ORG_INFORMATION2 Latest_Declaration_Offset
, comp_rep_info.ORG_INFORMATION3 Last_Contribution_Offset
, comp_rep_info.ORG_INFORMATION4 Payment_Date_Offset
, comp_rep_info.ORG_INFORMATION5 Activities_Ceased_Date
, comp_rep_info.ORG_INFORMATION6 No_Employees_Date
, comp_rep_info.ORG_INFORMATION7 Activities_Suspended
, comp_rep_info.ORG_INFORMATION8 Keep_Account_Open
, comp_rep_info.ORG_INFORMATION9 Administrator_Line_1
, comp_rep_info.ORG_INFORMATION10 Administrator_Line_2
, comp_rep_info.ORG_INFORMATION11 Administrator_Telephone_Number
, comp_rep_info.ORG_INFORMATION12 Administrator_FAX_Number
FROM hr_all_organization_units o
, hr_locations l
, hr_organization_information comp_info
, hr_organization_information rep_estab_info
, hr_organization_information comp_rep_info
WHERE o.organization_id = g_company_id
and o.location_id = l.location_id (+)
and comp_info.organization_id (+) = o.organization_id
and comp_info.org_information_context (+) = 'FR_COMP_INFO'
and rep_estab_info.organization_id (+) =
to_number(comp_info.org_information10)
and rep_estab_info.org_information_context (+) = 'FR_ESTAB_INFO'
and comp_rep_info.organization_id (+) = o.organization_id
and comp_rep_info.org_information_context (+) = 'FR_COMP_REPORTING_INFO';
SELECT estab_info.organization_id establishment_id
, estab_info.org_information2 estab_SIRET
, estab_info.org_information3 estab_NAF
--
-- Establishment Reporting Details
--
, estab_rep_info.ORG_INFORMATION1 Activities_Ceased_Date
, estab_rep_info.ORG_INFORMATION2 No_Employees_Date
, estab_rep_info.ORG_INFORMATION3 Activities_Suspended
, estab_rep_info.ORG_INFORMATION4 Keep_Account_Open
--
-- URSSAF Details
--
, urssaf.organization_id urssaf_id
, substr(urssaf.name,1,150) urssaf_name
, estab_urssaf_info.org_information2 estab_urssaf_ID
, estab_urssaf_info.ORG_INFORMATION6 U_Declaration_Due_Offset
, estab_urssaf_info.ORG_INFORMATION7 U_Latest_Declaration_Offset
, estab_urssaf_info.ORG_INFORMATION8 U_Last_Contribution_Offset
, estab_urssaf_info.ORG_INFORMATION9 U_Payment_Date_Offset
, estab_urssaf_info.ORG_INFORMATION10 URSSAF_Payment_1_Account
, estab_urssaf_info.ORG_INFORMATION11 URSSAF_Payment_1_Type
, estab_urssaf_info.ORG_INFORMATION12 URSSAF_Payment_1_Limit
, estab_urssaf_info.ORG_INFORMATION13 URSSAF_Payment_2_Account
, estab_urssaf_info.ORG_INFORMATION14 URSSAF_Payment_2_Type
, estab_urssaf_info.ORG_INFORMATION15 URSSAF_Payment_2_Limit
, estab_urssaf_info.ORG_INFORMATION16 URSSAF_Payment_3_Account
, estab_urssaf_info.ORG_INFORMATION17 URSSAF_Payment_3_Type
, estab_urssaf_info.ORG_INFORMATION18 URSSAF_Payment_3_Limit
, substr(urssaf_loc.address_line_1,1,150) urssaf_address_line_1
, substr(urssaf_loc.address_line_2,1,150) urssaf_address_line_2
, substr(urssaf_loc.region_3,1,150) urssaf_address_line_3
, urssaf_loc.postal_code||' '||urssaf_loc.town_or_city urssaf_address_line_4
--
-- ASSEDIC Details
--
, substr(assedic.name,1,150) assedic_name
, estab_assedic_info.org_information2 estab_ASSEDIC_ID
, estab_assedic_info.ORG_INFORMATION4 A_Declaration_Due_Offset
, estab_assedic_info.ORG_INFORMATION5 A_Latest_Declaration_Offset
, estab_assedic_info.ORG_INFORMATION6 A_Last_Contribution_Offset
, estab_assedic_info.ORG_INFORMATION7 A_Payment_Date_Offset
, estab_assedic_info.ORG_INFORMATION8 ASSEDIC_Payment_1_Account
, estab_assedic_info.ORG_INFORMATION9 ASSEDIC_Payment_1_Type
, estab_assedic_info.ORG_INFORMATION10 ASSEDIC_Payment_1_Limit
, estab_assedic_info.ORG_INFORMATION11 ASSEDIC_Payment_2_Account
, estab_assedic_info.ORG_INFORMATION12 ASSEDIC_Payment_2_Type
, estab_assedic_info.ORG_INFORMATION13 ASSEDIC_Payment_2_Limit
, estab_assedic_info.ORG_INFORMATION14 ASSEDIC_Payment_3_Account
, estab_assedic_info.ORG_INFORMATION15 ASSEDIC_Payment_3_Type
, estab_assedic_info.ORG_INFORMATION16 ASSEDIC_Payment_3_Limit
, substr(assedic_loc.address_line_1,1,150) assedic_address_line_1
, substr(assedic_loc.address_line_2,1,150) assedic_address_line_2
, substr(assedic_loc.region_3,1,150) assedic_address_line_3
, assedic_loc.postal_code||' '||assedic_loc.town_or_city assedic_address_line_4
FROM hr_organization_information estab_info
, hr_organization_information estab_urssaf_info
, hr_organization_information estab_assedic_info
, hr_organization_information estab_rep_info
, hr_all_organization_units urssaf
, hr_all_organization_units assedic
, hr_locations_all urssaf_loc
, hr_locations_all assedic_loc
WHERE estab_info.org_information1 = p_company_id_chr
and estab_info.org_information_context = 'FR_ESTAB_INFO'
--
-- Get the URSSAF details
--
and estab_info.organization_id = estab_urssaf_info.organization_id (+)
and estab_urssaf_info.org_information_context (+) = 'FR_ESTAB_URSSAF'
and estab_urssaf_info.org_information1 = urssaf.organization_id(+)
and urssaf.location_id = urssaf_loc.location_id (+)
--
-- Get the ASSEDIC details
--
and estab_info.organization_id = estab_assedic_info.organization_id (+)
and estab_assedic_info.org_information_context (+) = 'FR_ESTAB_ASSEDIC'
and estab_assedic_info.org_information1 = assedic.organization_id(+)
and assedic.location_id = assedic_loc.location_id (+)
--
-- Get the Establishment Reporting details
--
and estab_info.organization_id = estab_rep_info.organization_id (+)
and estab_rep_info.org_information_context (+) = 'FR_ESTAB_REPORTING_INFO';
SELECT /*+ ORDERED */
pens_prov_info.org_information_id comp_pens_prov_id
, pens_prov.organization_id pens_prov_id
, substr(pens_prov.name,1,150) name
, substr(pens_loc.address_line_1,1,150) address_line_1
, substr(pens_loc.address_line_2,1,150) address_line_2
, substr(pens_loc.region_3,1,150) address_line_3
, pens_loc.postal_code||' '||pens_loc.town_or_city address_line_4
, pens_prov_info.ORG_INFORMATION3 Declaration_Due_Offset
, pens_prov_info.ORG_INFORMATION4 Latest_Declaration_Offset
, pens_prov_info.ORG_INFORMATION5 Last_Contribution_Offset
, pens_prov_info.ORG_INFORMATION6 Payment_Date_Offset
, pens_prov_info.ORG_INFORMATION7 Payment_1_Account
, pens_prov_info.ORG_INFORMATION8 Payment_1_Type
, pens_prov_info.ORG_INFORMATION9 Payment_1_Limit
, pens_prov_info.ORG_INFORMATION10 Payment_2_Account
, pens_prov_info.ORG_INFORMATION11 Payment_2_Type
, pens_prov_info.ORG_INFORMATION12 Payment_2_Limit
, pens_prov_info.ORG_INFORMATION13 Payment_3_Account
, pens_prov_info.ORG_INFORMATION14 Payment_3_Type
, pens_prov_info.ORG_INFORMATION15 Payment_3_Limit
FROM hr_organization_information pens_prov_info
, hr_all_organization_units pens_prov
, hr_locations_all pens_loc
WHERE pens_prov_info.organization_id = g_company_id
and pens_prov_info.org_information_context = 'FR_COMP_PE_PRVS'
and pens_prov_info.org_information1 = pens_prov.organization_id
and pens_prov.location_id = pens_loc.location_id (+);
SELECT ea.segment2 || ea.segment3 || replace(ea.segment5,'-','')
FROM pay_org_payment_methods_f opm
, pay_external_accounts ea
WHERE opm.org_payment_method_id = l_org_method_id
AND opm.external_account_id = ea.external_account_id
AND g_effective_date between opm.effective_start_date
and opm.effective_end_date;
SELECT org_information7 payment_1_account
, org_information8 payment_1_type
, org_information9 payment_1_limit
, org_information10 payment_2_account
, org_information11 payment_2_type
, org_information12 payment_2_limit
, org_information13 payment_3_account
, org_information14 payment_3_type
, org_information15 payment_3_limit
, fnd_number.canonical_to_number(org_information16) advances
, fnd_number.canonical_to_number(org_information17) regularisation
FROM hr_organization_information
WHERE organization_id = g_company_id
and org_information_context = 'FR_COMP_PAYMENT_OVERRIDE'
and org_information2 = p_page_identifier
and org_information4 = p_page_type;
DELETE FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
and action_context_type = 'PA'
and action_information_category IN
('FR_DUCS_COMP_INFO'
,'FR_DUCS_ESTAB_INFO'
,'FR_DUCS_PAGE_INFO'
,'FR_DUCS_REFERENCE_INFO'
,'FR_DUCS_CONTRIB_INFO');
DELETE FROM hr_organization_information
WHERE organization_id = g_company_id
AND org_information_context = 'FR_COMP_PAYMENT_OVERRIDE'
AND org_information1 <> p_payroll_action_id;
SELECT payment.org_information1 payroll_action_id
, payment.org_information2 page_identifier
, payment.org_information4 page_type
, payment.org_information7 payment_1_account
, payment.org_information8 payment_1_type
, payment.org_information9 payment_1_limit
, payment.org_information10 payment_2_account
, payment.org_information11 payment_2_type
, payment.org_information12 payment_2_limit
, payment.org_information13 payment_3_account
, payment.org_information14 payment_3_type
, payment.org_information15 payment_3_limit
, payment.org_information16 advances
, payment.org_information17 regularisation
, page.action_information_id
, page.object_version_number
, page.action_information4 organization_name
, page.action_information25 total_contributions
FROM hr_organization_information payment
, pay_action_information page
WHERE payment.organization_id = p_company_id
and payment.org_information3 =
to_char(l_period_end_date,'YY') ||
to_char(l_period_end_date,'Q') ||
decode(p_period_type,'CM',
to_char(to_number(to_char(l_period_end_date,'MM'))
-(to_number(to_char(l_period_end_date,'Q'))*3-2)+1)
,'0')
and payment.org_information_context = 'FR_COMP_PAYMENT_OVERRIDE'
and payment.org_information_id =
nvl(p_override_information_id, payment.org_information_id)
and payment.org_information2 = page.action_information1
and payment.org_information1 = page.action_context_id
and page.action_context_type = 'PA'
and page.action_information_category = 'FR_DUCS_PAGE_INFO'
and page.action_information1 = payment.org_information2
and page.action_information2 = payment.org_information4;
SELECT ea.segment2 || ea.segment3 || replace(ea.segment5,'-','')
FROM pay_org_payment_methods_f opm
, pay_external_accounts ea
WHERE opm.org_payment_method_id = l_org_method_id
AND opm.external_account_id = ea.external_account_id
AND l_period_end_date between opm.effective_start_date
and opm.effective_end_date;
pay_action_information_api.update_action_information(
p_action_information_id => payment.action_information_id
,p_object_version_number => payment.object_version_number
,p_action_information11 => l_payment_1_acc_no
,p_action_information12 => fnd_number.number_to_canonical(l_payment_1_val)
,p_action_information13 => l_payment_2_acc_no
,p_action_information14 => fnd_number.number_to_canonical(l_payment_2_val)
,p_action_information15 => l_payment_3_acc_no
,p_action_information16 => fnd_number.number_to_canonical(l_payment_3_val)
,p_action_information26 => fnd_number.number_to_canonical(l_advances)
,p_action_information27 => fnd_number.number_to_canonical(l_regularisation)
,p_action_information28 => fnd_number.number_to_canonical(l_total_payment));