[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_information10
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'Business Group Information';
SELECT tag
FROM fnd_lookup_values
WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
INSTR(USERENV('LANGUAGE'), '.') + 1)
AND language = 'US';
SELECT inf_dtl.action_information5 prompt
FROM pay_action_information inf_dtl
WHERE inf_dtl.action_context_type = 'PA'
AND inf_dtl.action_context_id = p_payroll_action_id
AND inf_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND inf_dtl.action_information1 = 'REP_CODE_INFO_DTLS'
AND inf_dtl.action_information2 = p_legal_employer_id
AND inf_dtl.action_information3 = p_reporting_code
AND inf_dtl.action_information4 = p_info_id;
SELECT inf_dtl.action_information6 datatype
FROM pay_action_information inf_dtl
WHERE inf_dtl.action_context_type = 'PA'
AND inf_dtl.action_context_id = p_payroll_action_id
AND inf_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND inf_dtl.action_information1 = 'REP_CODE_INFO_DTLS'
AND inf_dtl.action_information2 = p_legal_employer_id
AND inf_dtl.action_information3 = p_reporting_code
AND inf_dtl.action_information4 = p_info_id;
SELECT inf_dtl.action_information8 xml_orid
FROM pay_action_information inf_dtl
WHERE inf_dtl.action_context_type = 'PA'
AND inf_dtl.action_context_id = p_payroll_action_id
AND inf_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND inf_dtl.action_information1 = 'REP_CODE_INFO_DTLS'
AND inf_dtl.action_information2 = p_legal_employer_id
AND inf_dtl.action_information3 = p_reporting_code
AND inf_dtl.action_information4 = p_info_id;
SELECT inf_dtl.action_information11 description
FROM pay_action_information inf_dtl
WHERE inf_dtl.action_context_type = 'PA'
AND inf_dtl.action_context_id = p_payroll_action_id
AND inf_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND inf_dtl.action_information1 = 'REP_CODE_DTLS'
AND inf_dtl.action_information2 = p_legal_employer_id
AND inf_dtl.action_information3 = p_reporting_code;
SELECT pec.classification_name
INTO l_classification
FROM pay_element_classifications pec
,pay_element_types_f pet
WHERE pet.element_type_id = l_element_type_id
AND pec.classification_id = pet.classification_id
AND l_report_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT paf.assignment_id
,greatest( trunc(p_effective_date,'Y'), paf.effective_start_date) start_date
,least( p_effective_date, paf.effective_end_date) end_date
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex sck
,hr_organization_information hoi
,per_assignment_status_types pas
WHERE paf.person_id = p_person_id
AND sck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND pas.assignment_status_type_id = paf.assignment_status_type_id
AND pas.user_status = 'Active Assignment'
AND hoi.org_information_context = 'NO_LOCAL_UNITS'
AND hoi.org_information1 = sck.segment2
AND hoi.organization_id = p_legal_empoyer_id
AND ((paf.effective_start_date BETWEEN trunc(p_effective_date,'Y') and p_effective_date
OR paf.effective_end_date BETWEEN trunc(p_effective_date,'Y') and p_effective_date)
OR (trunc(p_effective_date,'Y') > paf.effective_start_date
AND p_effective_date < paf.effective_end_date))
ORDER BY paf.effective_start_date;
SELECT greatest( trunc(p_effective_date,'Y'), paf.effective_start_date) start_date
,least( p_effective_date, paf.effective_end_date) end_date
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex sck
,hr_organization_information hoi
,per_assignment_status_types pas
WHERE paf.person_id = p_person_id
AND sck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND pas.assignment_status_type_id = paf.assignment_status_type_id
AND pas.user_status = 'Active Assignment'
AND hoi.org_information_context = 'NO_LOCAL_UNITS'
AND hoi.org_information1 = sck.segment2
AND hoi.organization_id = p_legal_empoyer_id
AND ((paf.effective_start_date BETWEEN trunc(p_effective_date,'Y') and p_effective_date
OR paf.effective_end_date BETWEEN trunc(p_effective_date,'Y') and p_effective_date)
OR (trunc(p_effective_date,'Y') > paf.effective_start_date
AND p_effective_date < paf.effective_end_date))
ORDER BY paf.effective_start_date;
SELECT db.defined_balance_id
FROM pay_defined_balances db
,pay_balance_dimensions bd
,pay_balance_types bt
WHERE bt.balance_name = p_balance_name
AND ((bt.business_group_id IS NULL AND bt.legislation_code = 'NO') OR (bt.legislation_code IS NULL AND bt.business_group_id = p_business_group_id))
AND bd.database_item_suffix = p_database_item_suffix
AND ((bd.business_group_id IS NULL AND bd.legislation_code = 'NO') OR (bd.legislation_code IS NULL AND bd.business_group_id = p_business_group_id))
AND db.balance_type_id = bt.balance_type_id
AND db.balance_dimension_id = bd.balance_dimension_id;
SELECT et.element_type_id
FROM pay_element_types_f et
WHERE et.element_name = p_element_name
AND ((et.business_group_id IS NULL AND et.legislation_code = 'NO') OR (et.legislation_code IS NULL AND et.business_group_id = p_business_group_id));
SELECT glb.global_value
FROM ff_globals_f glb
WHERE glb.global_name = p_global_name
AND glb.legislation_code = 'NO'
AND p_effective_date BETWEEN glb.effective_start_date
AND glb.effective_end_date;
SELECT org.name
,loc.address_line_1
,loc.address_line_2
,loc.address_line_3
,loc.postal_code postcode
,UPPER(SUBSTR(hr_general.decode_lookup('NO_POSTAL_CODE', loc.postal_code)
,INSTR(hr_general.decode_lookup('NO_POSTAL_CODE', loc.postal_code), ' ') + 1)) postoffice
FROM hr_all_organization_units org
,hr_locations_all loc
WHERE org.organization_id = p_organization_id
AND loc.location_id (+) = org.location_id;
SELECT pa.address_line1 address_line_1
,pa.address_line2 address_line_2
,pa.address_line3 address_line_3
,pa.postal_code postcode
,decode(pa.style,'NO',substr(hr_general.decode_lookup('NO_POSTAL_CODE',pa.postal_code),
instr(hr_general.decode_lookup('NO_POSTAL_CODE',pa.postal_code),' ')+1),'NO_GLB',pa.town_or_city) postoffice
FROM per_addresses pa
WHERE pa.person_id = p_person_id
AND pa.primary_flag = 'Y'
AND pa.style IN ('NO', 'NO_GLB')
AND p_report_date BETWEEN pa.date_from
AND nvl(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
SELECT business_group_id
,legislative_parameters
,pay_no_eoy_archive.get_parameter(legislative_parameters, 'LEGAL_EMPLOYER_ID') legal_employer_id
,fnd_date.canonical_to_date(pay_no_eoy_archive.get_parameter(legislative_parameters, 'DATE')) report_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT org.organization_id legal_employer_id
,org.name
,hoi2.org_information1 organization_number
,TO_NUMBER(hoi2.org_information5) tax_office_id
,hoi2.org_information2 tax_municipality
FROM hr_all_organization_units org
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE p_legal_employer_id IS NULL
AND org.business_group_id = p_business_group_id
AND hoi1.organization_id = org.organization_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi2.organization_id (+) = hoi1.organization_id
AND hoi2.org_information_context (+) = 'NO_LEGAL_EMPLOYER_DETAILS'
UNION ALL
SELECT org.organization_id legal_employer_id
,org.name
,hoi1.org_information1 organization_number
,TO_NUMBER(hoi1.org_information5) tax_office_id
,hoi1.org_information2 tax_municipality
FROM hr_all_organization_units org
,hr_organization_information hoi1
WHERE p_legal_employer_id IS NOT NULL
AND org.organization_id = p_legal_employer_id
AND hoi1.organization_id (+) = org.organization_id
AND hoi1.org_information_context (+) = 'NO_LEGAL_EMPLOYER_DETAILS';
SELECT to_number(hoi2.org_information4) economic_aid
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = csr_v_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context ='NO_NI_EXEMPTION_LIMIT'
AND p_report_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information2)
AND fnd_date.canonical_to_date(hoi2.org_information3);
SELECT r.row_low_range_or_name reporting_code
,hr_general.decode_lookup('NO_EOY_REPORTING_CODE',
hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'MAPPING_ID')) description
,hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'FIXED_CODE') fixed_code
,hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'XML_CODE_MAP') XML_CODE -- Added w.r.t phase2 Legislative changes for NORWAY for 2008
,hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'MULT_REC') mult_rec
,hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'REP_EARNINGS') reportable_earnings
,nvl(hoi.org_information4, hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'ASG_INFO_METH')) asg_info_meth
,nvl(DECODE(hoi.org_information4,'BAL', hoi.org_information5,'BAL_CODE_CTX', hoi.org_information5,'RRV_ELEMENT', hoi.org_information6,'PROCEDURE', hoi.org_information7),
decode(hoi.org_information4,'RRV',NULL,hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'ASG_INFO_DFN'))) asg_info_dfn
,nvl(hoi.org_information14, hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'REP_SUMM_METH')) rep_summ_meth
,nvl(DECODE(hoi.org_information14,'PROCEDURE' , hoi.org_information15),
decode(hoi.org_information14, 'PROCEDURE', hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'REP_SUMM_DFN'))) rep_summ_dfn
FROM pay_user_tables t
,pay_user_rows_f r
,hr_organization_information hoi
WHERE t.user_table_name = 'NO_EOY_CODE_REPORTING_RULES'
AND t.legislation_code = 'NO'
AND r.user_table_id = t.user_table_id
AND r.row_low_range_or_name = hoi.org_information3(+)
AND hoi.organization_id(+) = p_legal_employer_id
AND hoi.org_information_context(+) = 'NO_EOY_REPORTING_RULE_OVERRIDE'
AND to_number(to_char(p_report_date,'YYYY')) between to_number(hoi.org_information1(+))
AND to_number(nvl(hoi.org_information2,'4712'))
AND p_report_date BETWEEN r.effective_start_date
AND r.effective_end_date;
SELECT SUBSTR(c.user_column_name, 1, INSTR(c.user_column_name, '_') - 1) info_id
,hr_general.decode_lookup('NO_EOY_INFO_PROMPTS', ci.value) prompt
,nvl(DECODE(SUBSTR(c.user_column_name, 1, INSTR(c.user_column_name, '_') - 1)
,'INFO1',hoi.org_information8
,'INFO2',hoi.org_information9
,'INFO3',hoi.org_information10
,'INFO4',hoi.org_information11
,'INFO5',hoi.org_information12
,'INFO6',hoi.org_information13)
,hr_de_general.get_uci
(p_report_date
,t.user_table_id
,r.user_row_id
,SUBSTR(c.user_column_name, 1, INSTR(c.user_column_name, '_') - 1) || '_ASG_INFO_DFN')) asg_info_dfn
,hr_de_general.get_uci
(p_report_date
,t.user_table_id
,r.user_row_id
,SUBSTR(c.user_column_name, 1, INSTR(c.user_column_name, '_') - 1) || '_DATATYPE') datatype
,hr_de_general.get_uci
(p_report_date
,t.user_table_id
,r.user_row_id
,SUBSTR(c.user_column_name, 1, INSTR(c.user_column_name, '_') - 1) || '_XML_CODE_MAP') XML_VALUE_MAP --2007/08 changes
FROM pay_user_rows_f r
,pay_user_tables t
,pay_user_columns c
,pay_user_column_instances_f ci
,hr_organization_information hoi
WHERE t.user_table_name = 'NO_EOY_CODE_REPORTING_RULES'
AND c.user_table_id = t.user_table_id
AND r.user_table_id = t.user_table_id
AND ci.user_row_id = r.user_row_id
AND ci.user_column_id = c.user_column_id
AND c.user_column_name LIKE '%PROMPT'
AND r.row_low_range_or_name = p_reporting_code
AND r.row_low_range_or_name = hoi.org_information3(+)
AND hoi.organization_id(+) = p_legal_employer_id
AND hoi.org_information_context(+) = 'NO_EOY_REPORTING_RULE_OVERRIDE'
AND to_number(to_char(p_report_date,'YYYY')) BETWEEN to_number(hoi.org_information1(+))
AND to_number(nvl(hoi.org_information2,'4712'))
AND p_report_date BETWEEN r.effective_start_date AND r.effective_end_date
AND p_report_date BETWEEN ci.effective_start_date AND ci.effective_end_date;
SELECT DISTINCT
DECODE(orginf2.org_information5
,'Y', orginf2.org_information4
,orginf1.org_information3) si_status
,org2.organization_id local_unit_id
,org2.name local_unit
,pac2.context_value tax_municipality_id
,lu.meaning tax_municipality
,TO_NUMBER(decode(hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'ZONE'),'1a','6','4a','7',
hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'ZONE'))) ni_zone
-- ,TO_NUMBER(SUBSTR(lu.meaning, 1,1)) ni_zone
,DECODE(orginf2.org_information5,'Y', orginf2.org_information2,orginf1.org_information4) nace_code -- 2007/08 changes
,paa.assignment_id assg_id -- 2007/2008 Changes
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_action_contexts pac1
,pay_action_contexts pac2
,ff_contexts ctx1
,ff_contexts ctx2
,hr_lookups lu
,hr_organization_information orginf1
,hr_all_organization_units org2
,hr_organization_information orginf2
,pay_user_tables t
,pay_user_rows_f r
WHERE ppa.business_group_id = p_business_group_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pac1.assignment_action_id = paa.assignment_action_id
AND pac1.context_id = ctx1.context_id
AND ctx1.context_name = 'LOCAL_UNIT_ID'
AND pac2.assignment_action_id = paa.assignment_action_id
AND pac2.context_id = ctx2.context_id
AND ctx2.context_name = 'JURISDICTION_CODE'
AND r.row_low_range_or_name = pac2.context_value
AND t.user_table_name = 'NO_TAX_MUNICIPALITY'
AND t.legislation_code = 'NO'
AND r.user_table_id = t.user_table_id
AND lu.lookup_type = 'NO_TAX_MUNICIPALITY'
AND lu.lookup_code = hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'MAPPING_ID')
AND paa.tax_unit_id = p_legal_employer_id
AND org2.organization_id = pac1.context_value
AND orginf1.organization_id = paa.tax_unit_id
AND orginf1.org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS'
AND orginf2.organization_id = org2.organization_id
AND orginf2.org_information_context = 'NO_LOCAL_UNIT_DETAILS'
AND ppa.effective_date BETWEEN TRUNC(p_report_date, 'Y') AND p_report_date
AND p_report_date BETWEEN r.effective_start_date AND r.effective_end_date
ORDER BY DECODE(orginf2.org_information5, 'Y', orginf2.org_information4, orginf1.org_information3)
,org2.organization_id;
SELECT max(paa.assignment_action_id)
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.business_group_id = p_business_group_id
AND ppa.action_type IN ('R','Q','I','B')
AND ppa.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = p_assg_id -- changes 2007/2008
AND paa.tax_unit_id = p_legal_employer_id
AND ppa.date_earned BETWEEN TRUNC(p_report_date, 'Y') AND p_report_date;
SELECT DECODE(l_rec2.ni_zone,6,'1a',7,'1a',l_rec2.ni_zone)
INTO l_ni_zone_arc
FROM DUAL;
'SELECT DISTINCT per.person_id
FROM per_people_f per
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND per.business_group_id = ppa.business_group_id
ORDER BY per.person_id';
SELECT asg.person_id
,paa.assignment_id
,paa.tax_unit_id legal_employer_id
,TO_NUMBER(ac1.context_value) local_unit_id
,ac2.context_value tax_municipality
,ac3.context_value municipality_code
,hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'ZONE') ni_zone
,MAX(ppa.effective_date) effective_date
,MAX(asg.effective_end_date) effective_end_date
,MAX(asg.effective_start_date) effective_start_date --Changes 2007/2008
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_action_information ai
,pay_action_contexts ac1
,ff_contexts ctx1
,pay_action_contexts ac2
,ff_contexts ctx2
,pay_action_contexts ac3
,ff_contexts ctx3
,pay_user_tables t
,pay_user_rows_f r
WHERE asg.person_id BETWEEN p_start_person AND p_end_person
AND paa.assignment_id = asg.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ac1.assignment_action_id = paa.assignment_action_id
AND ctx1.context_id = ac1.context_id
AND ctx1.context_name = 'LOCAL_UNIT_ID'
AND ac3.assignment_action_id = paa.assignment_action_id
AND ctx3.context_id = ac3.context_id
AND ctx3.context_name = 'SOURCE_TEXT2'
AND ac2.assignment_action_id = paa.assignment_action_id
AND ctx2.context_id = ac2.context_id
AND ctx2.context_name = 'JURISDICTION_CODE'
AND t.user_table_name = 'NO_TAX_MUNICIPALITY'
AND t.legislation_code = 'NO'
AND r.user_table_id = t.user_table_id
AND r.row_low_range_or_name = ac2.context_value
AND paa.tax_unit_id = TO_NUMBER(ai.action_information2)
AND ai.action_context_type = 'PA'
AND ai.action_context_id = p_payroll_action_id
AND ai.action_information_category = 'EMEA REPORT INFORMATION'
AND ai.action_information1 = 'LEG_EMP_INFO'
AND ppa.effective_date BETWEEN TRUNC(p_report_date,'Y') AND p_report_date
AND p_report_date BETWEEN r.effective_start_date AND r.effective_end_date
GROUP BY asg.person_id
,paa.assignment_id
,paa.tax_unit_id
,TO_NUMBER(ac1.context_value)
,ac2.context_value
,ac3.context_value
,hr_de_general.get_uci(p_report_date,t.user_table_id,r.user_row_id,'ZONE')
ORDER BY asg.person_id
,MAX(ppa.effective_date) DESC;
SELECT per.full_name
,per.national_identifier
,per.employee_number
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND p_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date;
SELECT pay_assignment_actions_s.nextval INTO l_assact_id FROM dual;
SELECT paa.assignment_id
,asg_act.action_information2 legal_employer_id
,asg_act.action_information3 local_unit_id
,asg_act.action_information4 ni_zone
,asg_act.action_information6 tax_municipality_id
,asg_act.action_information8 municipality_code
FROM pay_action_information asg_act
,pay_assignment_actions paa
WHERE asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND paa.assignment_action_id = p_assignment_action_id;
SELECT cde_dtl.action_information3 reporting_code
,TO_NUMBER(cde_dtl.action_information8) defined_balance_id
FROM pay_action_information cde_dtl
WHERE cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = p_payroll_action_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = p_legal_employer_id
AND cde_dtl.action_information7 IN ('BAL','BAL_CODE_CTX');
SELECT inf_dtl.action_information4 info_id
,TO_NUMBER(inf_dtl.action_information7) defined_balance_id
FROM pay_action_information inf_dtl
WHERE inf_dtl.action_context_type = 'PA'
AND inf_dtl.action_context_id = p_payroll_action_id
AND inf_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND inf_dtl.action_information1 = 'REP_CODE_INFO_DTLS'
AND inf_dtl.action_information2 = p_legal_employer_id
AND inf_dtl.action_information3 = p_reporting_code
AND inf_dtl.action_information7 IS NOT NULL;
SELECT rr.run_result_id
,rr.element_type_id element_type
,cde_dtl.action_information3 reporting_code
,cde_info_dtl.action_information4 info_id
,rrv.result_value
,cde_info_dtl.action_information6 dtype
-- ,pivf.uom dtype
FROM pay_action_information cde_dtl
,pay_action_information cde_info_dtl
,pay_element_type_extra_info eei
,pay_assignment_actions paa
,pay_action_contexts ac1
,ff_contexts ctx1
,pay_action_contexts ac2
,ff_contexts ctx2
,pay_payroll_actions ppa
,pay_run_results rr
,pay_run_result_values rrv
,pay_input_values_f pivf
,pay_user_tables t
,pay_user_rows_f r
WHERE cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = p_payroll_action_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = p_legal_employer_id
AND cde_dtl.action_information7 IN ('RRV', 'RRV_ELEMENT')
AND cde_info_dtl.action_context_type = cde_dtl.action_context_type
AND cde_info_dtl.action_context_id = cde_dtl.action_context_id
AND cde_info_dtl.action_information_category = cde_dtl.action_information_category
AND cde_info_dtl.action_information1 = 'REP_CODE_INFO_DTLS'
AND cde_info_dtl.action_information2 = cde_dtl.action_information2
AND cde_info_dtl.action_information3 = cde_dtl.action_information3
AND eei.eei_information3 = cde_info_dtl.action_information3 --Reporting_Code
AND eei.information_type = 'NO_EOY_REPORTING_CODE_MAPPING'
AND to_number(to_char(p_report_date,'YYYY')) BETWEEN to_number(eei.eei_information1)
AND to_number(nvl(eei.eei_information2,'4712'))
AND ppa.business_group_id = p_business_group_id
AND ppa.action_type IN ('R','Q','I','B')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C'
AND ac1.assignment_action_id = paa.assignment_action_id
AND ctx1.context_id = ac1.context_id
AND ctx1.context_name = 'LOCAL_UNIT_ID'
AND ac1.context_value = p_local_unit_id
AND ac2.assignment_action_id = paa.assignment_action_id
AND ctx2.context_id = ac2.context_id
AND ctx2.context_name = 'JURISDICTION_CODE'
AND t.user_table_name = 'NO_TAX_MUNICIPALITY'
AND t.legislation_code = 'NO'
AND r.user_table_id = t.user_table_id
AND r.row_low_range_or_name = ac2.context_value
AND ac2.context_value = p_tax_municipality
AND paa.tax_unit_id = TO_NUMBER(p_legal_employer_id)
AND paa.assignment_id = p_assignment_id
AND ppa.effective_date BETWEEN TRUNC(p_report_date, 'Y') AND p_report_date
AND rr.assignment_action_id = paa.assignment_action_id
AND eei.element_type_id = DECODE(cde_dtl.action_information7
,'RRV', eei.element_type_id
,TO_NUMBER(cde_dtl.action_information8))
AND rr.element_type_id = eei.element_type_id
AND rrv.run_result_id = rr.run_result_id
AND rrv.input_value_id = TO_NUMBER(DECODE(cde_info_dtl.action_information4
,'INFO1' , eei.eei_information6
,'INFO2' , eei.eei_information8
,'INFO3' , eei.eei_information10
,'INFO4' , eei.eei_information12
,'INFO5' , eei.eei_information14
,'INFO6' , eei.eei_information16
,'AMOUNT', eei.eei_information4))
AND pivf.input_value_id = rrv.input_value_id
AND p_report_date BETWEEN pivf.effective_start_date
AND pivf.effective_end_date
AND p_report_date BETWEEN r.effective_start_date
AND r.effective_end_date
ORDER BY cde_dtl.action_information3, rr.run_result_id;
SELECT cde_dtl.action_information3 reporting_code
,cde_dtl.action_information8 procedure_name
FROM pay_action_information cde_dtl
WHERE cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = p_payroll_action_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = p_legal_employer_id
AND cde_dtl.action_information7 = 'PROCEDURE';
SELECT classification_id
FROM pay_element_classifications
WHERE classification_name = 'Seaman _ Earnings';
SELECT 'Y'
FROM pay_sub_classification_rules_f
WHERE element_type_id = l_element_type
AND classification_id = l_classification_id
AND l_report_date BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM pay_defined_balances pdb
,pay_balance_classifications pbc
WHERE pbc.classification_id = l_classification_id
AND pdb.balance_type_id = pbc.balance_type_id
AND pdb.defined_balance_id = l_defined_balance_id;
SELECT rrv.result_value value
,pivf.uom dtype
FROM pay_assignment_actions paa
,pay_action_contexts ac1
,ff_contexts ctx1
,pay_action_contexts ac2
,ff_contexts ctx2
,pay_payroll_actions ppa
,pay_run_results rr
,pay_run_result_values rrv
,pay_input_values_f pivf
,pay_element_types_f petf
,pay_user_tables t
,pay_user_rows_f r
WHERE ppa.business_group_id = p_business_group_id
AND ppa.action_type IN ('R','Q','I','B')
AND ppa.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND ac1.assignment_action_id = paa.assignment_action_id
AND ctx1.context_id = ac1.context_id
AND ctx1.context_name = 'LOCAL_UNIT_ID'
AND ac1.context_value = p_local_unit_id
AND ac2.assignment_action_id = paa.assignment_action_id
AND ctx2.context_id = ac2.context_id
AND ctx2.context_name = 'JURISDICTION_CODE'
AND t.user_table_name = 'NO_TAX_MUNICIPALITY'
AND t.legislation_code = 'NO'
AND r.user_table_id = t.user_table_id
AND r.row_low_range_or_name = ac2.context_value
AND ac2.context_value = p_tax_municipality
AND paa.tax_unit_id = TO_NUMBER(p_legal_employer_id)
AND paa.assignment_id = p_assignment_id
AND rr.assignment_action_id = paa.assignment_action_id
AND rr.element_type_id = petf.element_type_id
AND petf.element_name = l_element_name
AND petf.legislation_code = 'NO'
AND rrv.run_result_id = rr.run_result_id
AND pivf.name = 'Pay Value'
AND pivf.legislation_code = 'NO'
AND pivf.input_value_id = rrv.input_value_id
AND pivf.element_type_id = petf.element_type_id
AND ppa.effective_date BETWEEN TRUNC(p_report_date, 'Y') AND p_report_date
AND p_report_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND p_report_date BETWEEN pivf.effective_start_date
AND pivf.effective_end_date
AND p_report_date BETWEEN r.effective_start_date
AND r.effective_end_date;
SELECT rrv.result_value value
,petf.element_type_id element_type_id
,rr.run_result_id rr_id
,petf.element_name element_name
,ppa.action_type action_type
FROM pay_assignment_actions paa
,pay_action_contexts ac1
,ff_contexts ctx1
,pay_action_contexts ac2
,ff_contexts ctx2
,pay_payroll_actions ppa
,pay_run_results rr
,pay_run_result_values rrv
,pay_input_values_f pivf
,pay_element_types_f petf
,pay_user_tables t
,pay_user_rows_f r
WHERE ppa.business_group_id = p_business_group_id
AND ppa.action_type IN ('R','Q','I','B')
AND ppa.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND ac1.assignment_action_id = paa.assignment_action_id
AND ctx1.context_id = ac1.context_id
AND ctx1.context_name = 'LOCAL_UNIT_ID'
AND ac1.context_value = p_local_unit_id
AND ac2.assignment_action_id = paa.assignment_action_id
AND ctx2.context_id = ac2.context_id
AND ctx2.context_name = 'JURISDICTION_CODE'
AND t.user_table_name = 'NO_TAX_MUNICIPALITY'
AND t.legislation_code = 'NO'
AND r.user_table_id = t.user_table_id
AND r.row_low_range_or_name = ac2.context_value
AND ac2.context_value = p_tax_municipality
AND paa.tax_unit_id = TO_NUMBER(p_legal_employer_id)
AND paa.assignment_id = p_assignment_id
AND rr.assignment_action_id = paa.assignment_action_id
AND rr.element_type_id = petf.element_type_id
AND petf.element_name IN ( l_element_name, 'Adjustment ' || l_element_name )
AND petf.legislation_code = 'NO'
AND rrv.run_result_id = rr.run_result_id
AND pivf.name = 'Travel Reference Number'
AND pivf.legislation_code = 'NO'
AND pivf.input_value_id = rrv.input_value_id
AND pivf.element_type_id = petf.element_type_id
AND ppa.effective_date BETWEEN TRUNC(p_report_date, 'Y') AND p_report_date
AND p_report_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND p_report_date BETWEEN pivf.effective_start_date
AND pivf.effective_end_date
AND p_report_date BETWEEN r.effective_start_date
AND r.effective_end_date
order by rrv.result_value;
SELECT sum(decode (piv.name,'Pay Value', fnd_number.canonical_to_number(rrv.result_value) ,0)) Pay_Value
,sum(decode (piv.name,'Per Diem', fnd_number.canonical_to_number(rrv.result_value) ,0)) Per_Diem
,sum(decode (piv.name,'Number of Days to be reported', fnd_number.canonical_to_number(rrv.result_value) ,0)) Reporting_Days
,sum(decode (piv.name,'Number of Days', fnd_number.canonical_to_number(rrv.result_value) ,0)) Days
,max(decode (piv.name,'Country', rrv.result_value)) Country
FROM pay_run_result_values rrv
,pay_input_values_f piv
WHERE rrv.run_result_id = l_rr_id
AND piv.element_type_id = l_element_type_id
AND piv.name IN ('Pay Value','Number of Days','Per Diem','Number of Days to be reported','Country')
AND piv.input_value_id = rrv.input_value_id
AND p_report_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
l_code_616.DELETE;
SELECT le_lu.action_information2 legal_employer_id
,asg_act.action_information5 person_id
FROM pay_assignment_actions paa
,pay_action_information le_lu
,pay_action_information asg_act
,pay_action_information rep_cde
,pay_action_information cde_dtl
WHERE paa.payroll_action_id = le_lu.action_context_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND asg_act.action_information2 = le_lu.action_information2
AND asg_act.action_information3 = le_lu.action_information4
AND le_lu.action_context_type = 'PA'
AND le_lu.action_context_id = p_payroll_action_id
AND le_lu.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu.action_information1 = 'LE_SI_LU_INFO'
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = le_lu.action_context_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = le_lu.action_information2
AND cde_dtl.action_information3 = rep_cde.action_information2
AND cde_dtl.action_information6 = 'Y'
AND rep_cde.action_context_type = 'AAP'
AND rep_cde.action_context_id = asg_act.action_context_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'ASG_REP_CODE_INFO'
GROUP BY le_lu.action_information2
,asg_act.action_information5
HAVING SUM(fnd_number.canonical_to_number(rep_cde.action_information3)) < 1000;
SELECT asg_act.action_information_id
,asg_act.object_version_number
,paa.assignment_id
FROM pay_action_information asg_act
,pay_assignment_actions paa
WHERE asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND asg_act.action_information2 = p_legal_employer_id
AND asg_act.action_information5 = p_person_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id;
SELECT asg_act.action_information2 legal_employer_id
,asg_act.action_information5 person_id
,asg_act.action_information6 tax_municipality
,rep_cde.action_information2 code
,rep_cde.action_information3 amount
,rep_cde.action_information4 info1
,rep_cde.action_information5 info2
,rep_cde.action_information6 info3
,rep_cde.action_information7 info4
,rep_cde.action_information8 info5
,rep_cde.action_information9 info6
,rep_cde.action_information10 seaman_component
,rep_cde.action_information11 cinfo1
,rep_cde.action_information12 cinfo2
,rep_cde.action_information13 cinfo3
,rep_cde.action_information14 cinfo4
,rep_cde.action_information15 cinfo5
,rep_cde.action_information16 cinfo6
FROM pay_assignment_actions paa
,pay_action_information asg_act
,pay_action_information rep_cde
,pay_action_information cde_dtl
WHERE paa.payroll_action_id = p_payroll_action_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND rep_cde.action_context_type = 'AAP'
AND rep_cde.action_context_id = asg_act.action_context_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'ASG_REP_CODE_INFO'
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = paa.payroll_action_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = asg_act.action_information2
AND cde_dtl.action_information3 = rep_cde.action_information2
AND cde_dtl.action_information9 = 'INDIVIDUAL';
SELECT asg_act.action_information2 legal_employer_id
,asg_act.action_information5 person_id
,asg_act.action_information6 tax_municipality
,rep_cde.action_information2 code
,SUM(fnd_number.canonical_to_number(rep_cde.action_information3)) amount
,SUM(fnd_number.canonical_to_number(nvl(rep_cde.action_information4,0))) info1
,SUM(fnd_number.canonical_to_number(nvl(rep_cde.action_information5,0))) info2
,SUM(fnd_number.canonical_to_number(nvl(rep_cde.action_information6,0))) info3
,SUM(fnd_number.canonical_to_number(nvl(rep_cde.action_information7,0))) info4
,SUM(fnd_number.canonical_to_number(nvl(rep_cde.action_information8,0))) info5
,SUM(fnd_number.canonical_to_number(nvl(rep_cde.action_information9,0))) info6
,rep_cde.action_information10 seaman_component
,rep_cde.action_information11 cinfo1
,rep_cde.action_information12 cinfo2
,rep_cde.action_information13 cinfo3
,rep_cde.action_information14 cinfo4
,rep_cde.action_information15 cinfo5
,rep_cde.action_information16 cinfo6
FROM pay_assignment_actions paa
,pay_action_information asg_act
,pay_action_information rep_cde
,pay_action_information cde_dtl
WHERE paa.payroll_action_id = p_payroll_action_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND rep_cde.action_context_type = 'AAP'
AND rep_cde.action_context_id = asg_act.action_context_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'ASG_REP_CODE_INFO'
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = paa.payroll_action_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = asg_act.action_information2
AND cde_dtl.action_information3 = rep_cde.action_information2
AND cde_dtl.action_information9 = 'SUM'
GROUP BY asg_act.action_information2
,asg_act.action_information5
,asg_act.action_information6
,rep_cde.action_information2
,rep_cde.action_information10
,rep_cde.action_information11
,rep_cde.action_information12
,rep_cde.action_information13
,rep_cde.action_information14
,rep_cde.action_information15
,rep_cde.action_information16 ;
SELECT cde_dtl.action_information2 legal_employer_id
,cde_dtl.action_information3 reporting_code
,cde_dtl.action_information10 procedure_name
FROM pay_action_information cde_dtl
WHERE cde_dtl.action_context_id = p_payroll_action_id
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information9 = 'PROCEDURE';
pay_action_information_api.update_action_information
(p_action_information_id => l_asg_act_rec.action_information_id
,p_object_version_number => l_asg_act_rec.object_version_number
,p_action_information7 => 'Y');
SELECT leg_emp.action_information2 legal_employer_id
,leg_emp.action_information3 legal_employer_name
,leg_emp.action_information4 organization_number
,leg_emp.effective_date effective_date
FROM pay_action_information leg_emp
WHERE leg_emp.action_context_type = 'PA'
AND leg_emp.action_context_id = l_payroll_action_id
AND leg_emp.action_information_category = 'EMEA REPORT INFORMATION'
AND leg_emp.action_information1 = 'LEG_EMP_INFO'
ORDER BY leg_emp.action_information3;
SELECT DISTINCT
summ_cde.action_information3 person_id
,summ_cde.action_information12 tax_municipality
,per.action_information3 full_name
,per.action_information4 employee_number
,per.action_information5 national_identifier
,per.action_information6 employed_throughout
,per.action_information7 seamen
,per.action_information8 employment_date_days
FROM pay_action_information summ_cde
,pay_action_information per
WHERE summ_cde.action_context_type = 'PA'
AND summ_cde.action_context_id = l_payroll_action_id
AND summ_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND summ_cde.action_information1 = 'AUDIT_REP_SUMMARY'
AND summ_cde.action_information2 = p_legal_employer_id
AND per.action_context_type = summ_cde.action_context_type
AND per.action_context_id = summ_cde.action_context_id
AND per.action_information_category = summ_cde.action_information_category
AND per.action_information1 = 'PER_INFO'
AND per.action_information2 = summ_cde.action_information3
AND per.action_information7 = DECODE(l_seaman_emp,'N',per.action_information7,hr_general.decode_lookup('YES_NO','Y'))
ORDER BY per.action_information3;
SELECT summ_cde.action_information4 code
,fnd_number.canonical_to_number(summ_cde.action_information5) amount
,summ_cde.action_information6 info1
,summ_cde.action_information7 info2
,summ_cde.action_information8 info3
,summ_cde.action_information9 info4
,summ_cde.action_information10 info5
,summ_cde.action_information11 info6
,pay_no_eoy_archive.get_code_desc(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4) description
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO1') info1_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO2') info2_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO3') info3_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO4') info4_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO5') info5_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO6') info6_prompt
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO1') info1_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO2') info2_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO3') info3_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO4') info4_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO5') info5_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO6') info6_dtype
FROM pay_action_information summ_cde
WHERE summ_cde.action_context_type = 'PA'
AND summ_cde.action_context_id = l_payroll_action_id
AND summ_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND summ_cde.action_information1 = 'AUDIT_REP_SUMMARY'
AND summ_cde.action_information2 = p_legal_employer_id
AND summ_cde.action_information3 = p_person_id
AND summ_cde.action_information12 = p_tax_municipality
AND summ_cde.action_information13 = l_seaman_status
ORDER BY summ_cde.action_information2,
summ_cde.action_information4;
g_xml_element_table.DELETE;
g_fixed_code.DELETE;
SELECT payroll_action_id
into l_payroll_action_id
from pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
and fcrs.priority_request_id = fcrs1.priority_request_id
and ppa.request_id between fcrs1.request_id and fcrs.request_id
and ppa.request_id = fcrs1.request_id;
SELECT DISTINCT
le_lu_tm.action_information3 si_status
,le_lu_tm.action_information4 local_unit_id
,le_lu_tm.action_information5 local_unit
,le_lu_tm.action_information6 tax_municipality_id
,le_lu_tm.action_information7 tax_municipality
,le_lu_tm.action_information8 ni_zone_arc
,fnd_number.canonical_to_number(le_lu_tm.action_information9) emp_contr_bse
,fnd_number.canonical_to_number(le_lu_tm.action_information10) reimb_bse
,fnd_number.canonical_to_number(le_lu_tm.action_information11) pension_bse
FROM pay_action_information le_lu_tm
WHERE le_lu_tm.action_context_type = 'PA'
AND le_lu_tm.action_context_id = l_payroll_action_id
AND le_lu_tm.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu_tm.action_information1 = 'LE_SI_LU_TM_INFO';
SELECT DISTINCT
le_lu.action_information2 legal_employer_id
,le_lu.action_information3 si_status
,le_lu.action_information5 nace_code -- changes 2007/08
,leg_emp.action_information3 legal_employer_name
,leg_emp.action_information4 organization_number
,leg_emp.action_information5 le_addr1
,leg_emp.action_information6 le_addr2
,leg_emp.action_information7 le_addr3
,leg_emp.action_information8 le_postcode
,leg_emp.action_information9 le_postoffice
,leg_emp.action_information10 tax_office_name
,leg_emp.action_information11 to_addr1
,leg_emp.action_information12 to_addr2
,leg_emp.action_information13 to_addr3
,leg_emp.action_information14 to_postcode
,leg_emp.action_information15 to_postoffice
,leg_emp.action_information16 le_tax_muncipality --2007/2008 Changes
,leg_emp.action_information17 le_economic_aid -- 2007/2008 Changes
,leg_emp.effective_date effective_date
FROM pay_action_information le_lu
,pay_action_information leg_emp
WHERE le_lu.action_context_type = 'PA'
AND le_lu.action_context_id = l_payroll_action_id
AND le_lu.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu.action_information1 = 'LE_SI_LU_INFO'
AND leg_emp.action_context_type = 'PA'
AND leg_emp.action_context_id = le_lu.action_context_id
AND leg_emp.action_information_category = 'EMEA REPORT INFORMATION'
AND leg_emp.action_information1 = 'LEG_EMP_INFO'
AND leg_emp.action_information2 = le_lu.action_information2;
SELECT fnd_number.canonical_to_number(le_si_bals.action_information4) emp_contr_ni_zone1
,fnd_number.canonical_to_number(le_si_bals.action_information5) emp_contr_ni_zone2
,fnd_number.canonical_to_number(le_si_bals.action_information6) emp_contr_ni_zone3
,fnd_number.canonical_to_number(le_si_bals.action_information7) emp_contr_ni_zone4
,fnd_number.canonical_to_number(le_si_bals.action_information8) emp_contr_ni_zone5
,fnd_number.canonical_to_number(le_si_bals.action_information9) emp_contr_ni_total
,fnd_number.canonical_to_number(le_si_bals.action_information10) emp_contr_o62_ni_zone1
,fnd_number.canonical_to_number(le_si_bals.action_information11) emp_contr_o62_ni_zone2
,fnd_number.canonical_to_number(le_si_bals.action_information12) emp_contr_o62_ni_zone3
,fnd_number.canonical_to_number(le_si_bals.action_information13) emp_contr_o62_ni_zone4
,fnd_number.canonical_to_number(le_si_bals.action_information14) emp_contr_o62_ni_zone5
,fnd_number.canonical_to_number(le_si_bals.action_information15) emp_contr_o62_ni_total
,fnd_number.canonical_to_number(le_si_bals.action_information16) tax
,fnd_number.canonical_to_number(le_si_bals.action_information17) emp_contr_spcl_pct_base
,fnd_number.canonical_to_number(le_si_bals.action_information18) emp_contr_spcl_pct
,fnd_number.canonical_to_number(le_si_bals.action_information19) emp_contr_spcl
,fnd_number.canonical_to_number(le_si_bals.action_information20) foreign_special_percentage
,fnd_number.canonical_to_number(le_si_bals.action_information21) foreign_special_amount
,fnd_number.canonical_to_number(le_si_bals.action_information22) emp_contr_reimb_spcl_base --2007/2008 changes
,fnd_number.canonical_to_number(le_si_bals.action_information23) tot_reimb_bse --2007/2008 changes
,fnd_number.canonical_to_number(le_si_bals.action_information24) tot_pension_bse -- 2007/2008 Changes
FROM pay_action_information le_si_bals
WHERE le_si_bals.action_context_type = 'PA'
AND le_si_bals.action_context_id = l_payroll_action_id
AND le_si_bals.action_information_category = 'EMEA REPORT INFORMATION'
AND le_si_bals.action_information1 = 'LE_SI_BALS'
AND le_si_bals.action_information2 = p_legal_employer_id
AND le_si_bals.action_information3 = p_si_status;
SELECT COUNT (DISTINCT asg_act.action_information5) certificates
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'1N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone1
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'2N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone2
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'3N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone3
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'4N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone4
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'5N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone5
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'1Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone1
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'2Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone2
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'3Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone3
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'4Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone4
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'5Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone5
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'11', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone1
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'21', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone2
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'31', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone3
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'41', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone4
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'51', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone5
,SUM(DECODE(TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_totals
FROM pay_assignment_actions paa
,pay_action_information le_lu
,pay_action_information asg_act
,pay_action_information rep_cde
,pay_action_information cde_dtl
WHERE paa.payroll_action_id = le_lu.action_context_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND asg_act.action_information2 = le_lu.action_information2
AND asg_act.action_information3 = le_lu.action_information4
AND le_lu.action_context_type = 'PA'
AND le_lu.action_context_id = l_payroll_action_id
AND le_lu.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu.action_information1 = 'LE_SI_LU_INFO'
AND le_lu.action_information2 = p_legal_employer_id
AND le_lu.action_information3 = p_si_status
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = le_lu.action_context_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = le_lu.action_information2
AND cde_dtl.action_information3 = rep_cde.action_information2
AND cde_dtl.action_information6 = 'Y'
AND rep_cde.action_context_type = 'AAP'
AND rep_cde.action_context_id = asg_act.action_context_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'ASG_REP_CODE_INFO'
GROUP BY asg_act.action_information2
,le_lu.action_information3;
SELECT SUM(DECODE(asg_act.action_information7
,'N', fnd_number.canonical_to_number(rep_cde.action_information3)
, 0)) a_earnings
,SUM(DECODE(asg_act.action_information7
,'Y', fnd_number.canonical_to_number(rep_cde.action_information3)
, 0)) bl_earnings
,SUM(fnd_number.canonical_to_number(rep_cde.action_information3)) sum_earnings
FROM pay_assignment_actions paa
,pay_action_information le_lu
,pay_action_information asg_act
,pay_action_information rep_cde
,pay_action_information cde_dtl
WHERE paa.payroll_action_id = le_lu.action_context_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND asg_act.action_information2 = le_lu.action_information2
AND asg_act.action_information3 = le_lu.action_information4
AND le_lu.action_context_type = 'PA'
AND le_lu.action_context_id = l_payroll_action_id
AND le_lu.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu.action_information1 = 'LE_SI_LU_INFO'
AND le_lu.action_information2 = p_legal_employer_id
AND le_lu.action_information3 = p_si_status
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = le_lu.action_context_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = le_lu.action_information2
AND cde_dtl.action_information3 = rep_cde.action_information2
AND cde_dtl.action_information6 = 'Y'
AND rep_cde.action_context_type = 'AAP'
AND rep_cde.action_context_id = asg_act.action_context_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'ASG_REP_CODE_INFO'
AND rep_cde.action_information2 NOT IN ('000','250','311','312','313','314','315','316','950') -- Changes 2007/2008
GROUP BY asg_act.action_information2
,le_lu.action_information3;
SELECT asg_act.action_information2 legal_employer_id
,le_lu.action_information3 si_status
,DECODE(cde_dtl.action_information4, 'Y', rep_cde.action_information2, 'TOTAL_OTHER_CODES') code
,SUM(fnd_number.canonical_to_number(rep_cde.action_information3)) amount
FROM pay_assignment_actions paa
,pay_action_information le_lu
,pay_action_information asg_act
,pay_action_information rep_cde
,pay_action_information cde_dtl
WHERE paa.payroll_action_id = le_lu.action_context_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND asg_act.action_information2 = le_lu.action_information2
AND asg_act.action_information3 = le_lu.action_information4
AND le_lu.action_context_type = 'PA'
AND le_lu.action_context_id = l_payroll_action_id
AND le_lu.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu.action_information1 = 'LE_SI_LU_INFO'
AND le_lu.action_information2 = p_legal_employer_id
AND le_lu.action_information3 = p_si_status
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = le_lu.action_context_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = le_lu.action_information2
AND cde_dtl.action_information3 = rep_cde.action_information2
AND rep_cde.action_context_type = 'AAP'
AND rep_cde.action_context_id = asg_act.action_context_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'ASG_REP_CODE_INFO'
GROUP BY asg_act.action_information2
,le_lu.action_information3
,DECODE(cde_dtl.action_information4, 'Y',rep_cde.action_information2, 'TOTAL_OTHER_CODES');
SELECT lu.lookup_code si_status
FROM hr_lookups lu
WHERE lu.lookup_type = 'NO_LEGAL_EMP_SI_STATUS';
g_xml_element_table.DELETE;
SELECT payroll_action_id
into l_payroll_action_id
from pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
and fcrs.priority_request_id = fcrs1.priority_request_id
and ppa.request_id between fcrs1.request_id and fcrs.request_id
and ppa.request_id = fcrs1.request_id;
SELECT DISTINCT
le_lu_tm.action_information3 si_status
,le_lu_tm.action_information4 local_unit_id
,le_lu_tm.action_information5 local_unit
,le_lu_tm.action_information6 tax_municipality_id
,le_lu_tm.action_information7 tax_municipality
,le_lu_tm.action_information8 ni_zone_arc
,fnd_number.canonical_to_number(le_lu_tm.action_information9) emp_contr_bse
,fnd_number.canonical_to_number(le_lu_tm.action_information10) reimb_bse
,fnd_number.canonical_to_number(le_lu_tm.action_information11) pension_bse
FROM pay_action_information le_lu_tm
WHERE le_lu_tm.action_context_type = 'PA'
AND le_lu_tm.action_context_id = l_payroll_action_id
AND le_lu_tm.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu_tm.action_information1 = 'LE_SI_LU_TM_INFO';
SELECT DISTINCT
le_lu.action_information2 legal_employer_id
,le_lu.action_information3 si_status
,le_lu.action_information5 nace_code -- changes 2007/08
,leg_emp.action_information3 legal_employer_name
,leg_emp.action_information4 organization_number
,leg_emp.action_information5 le_addr1
,leg_emp.action_information6 le_addr2
,leg_emp.action_information7 le_addr3
,leg_emp.action_information8 le_postcode
,leg_emp.action_information9 le_postoffice
,leg_emp.action_information10 tax_office_name
,leg_emp.action_information11 to_addr1
,leg_emp.action_information12 to_addr2
,leg_emp.action_information13 to_addr3
,leg_emp.action_information14 to_postcode
,leg_emp.action_information15 to_postoffice
,leg_emp.action_information16 le_tax_muncipality -- 2007/2008 Changes
,leg_emp.action_information17 le_economic_aid -- 2007/2008 Changes
,leg_emp.effective_date effective_date
FROM pay_action_information le_lu
,pay_action_information leg_emp
WHERE le_lu.action_context_type = 'PA'
AND le_lu.action_context_id = l_payroll_action_id
AND le_lu.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu.action_information1 = 'LE_SI_LU_INFO'
AND leg_emp.action_context_type = 'PA'
AND leg_emp.action_context_id = le_lu.action_context_id
AND leg_emp.action_information_category = 'EMEA REPORT INFORMATION'
AND leg_emp.action_information1 = 'LEG_EMP_INFO'
AND leg_emp.action_information2 = le_lu.action_information2;
SELECT fnd_number.canonical_to_number(le_si_bals.action_information4) emp_contr_ni_zone1
,fnd_number.canonical_to_number(le_si_bals.action_information5) emp_contr_ni_zone2
,fnd_number.canonical_to_number(le_si_bals.action_information6) emp_contr_ni_zone3
,fnd_number.canonical_to_number(le_si_bals.action_information7) emp_contr_ni_zone4
,fnd_number.canonical_to_number(le_si_bals.action_information8) emp_contr_ni_zone5
,fnd_number.canonical_to_number(le_si_bals.action_information9) emp_contr_ni_total
,fnd_number.canonical_to_number(le_si_bals.action_information10) emp_contr_o62_ni_zone1
,fnd_number.canonical_to_number(le_si_bals.action_information11) emp_contr_o62_ni_zone2
,fnd_number.canonical_to_number(le_si_bals.action_information12) emp_contr_o62_ni_zone3
,fnd_number.canonical_to_number(le_si_bals.action_information13) emp_contr_o62_ni_zone4
,fnd_number.canonical_to_number(le_si_bals.action_information14) emp_contr_o62_ni_zone5
,fnd_number.canonical_to_number(le_si_bals.action_information15) emp_contr_o62_ni_total
,fnd_number.canonical_to_number(le_si_bals.action_information16) tax
,fnd_number.canonical_to_number(le_si_bals.action_information17) emp_contr_spcl_pct_base
,fnd_number.canonical_to_number(le_si_bals.action_information18) emp_contr_spcl_pct
,fnd_number.canonical_to_number(le_si_bals.action_information19) emp_contr_spcl
,fnd_number.canonical_to_number(le_si_bals.action_information20) foreign_special_percentage
,fnd_number.canonical_to_number(le_si_bals.action_information21) foreign_special_amount
,fnd_number.canonical_to_number(le_si_bals.action_information22) emp_contr_reimb_spcl_base --2007/2008 changes
,fnd_number.canonical_to_number(le_si_bals.action_information23) tot_reimb_bse --2007/2008 changes
,fnd_number.canonical_to_number(le_si_bals.action_information24) tot_pension_bse -- 2007/2008 Changes
FROM pay_action_information le_si_bals
WHERE le_si_bals.action_context_type = 'PA'
AND le_si_bals.action_context_id = l_payroll_action_id
AND le_si_bals.action_information_category = 'EMEA REPORT INFORMATION'
AND le_si_bals.action_information1 = 'LE_SI_BALS'
AND le_si_bals.action_information2 = p_legal_employer_id
AND le_si_bals.action_information3 = p_si_status;
SELECT COUNT (DISTINCT asg_act.action_information5) certificates
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'1N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone1
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'2N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone2
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'3N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone3
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'4N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone4
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'5N1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) a_zone5
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'1Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone1
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'2Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone2
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'3Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone3
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'4Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone4
,SUM(DECODE(asg_act.action_information4 || asg_act.action_information7 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'5Y1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) bl_zone5
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'11', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone1
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'21', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone2
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'31', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone3
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'41', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone4
,SUM(DECODE(asg_act.action_information4 || TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'51', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_zone5
,SUM(DECODE(TO_CHAR(SIGN(INSTR(rep_cde.action_information2, 'A')))
,'1', fnd_number.canonical_to_number(rep_cde.action_information3)
,0)) sum_totals
FROM pay_assignment_actions paa
,pay_action_information le_lu
,pay_action_information asg_act
,pay_action_information rep_cde
,pay_action_information cde_dtl
WHERE paa.payroll_action_id = le_lu.action_context_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND asg_act.action_information2 = le_lu.action_information2
AND asg_act.action_information3 = le_lu.action_information4
AND le_lu.action_context_type = 'PA'
AND le_lu.action_context_id = l_payroll_action_id
AND le_lu.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu.action_information1 = 'LE_SI_LU_INFO'
AND le_lu.action_information2 = p_legal_employer_id
AND le_lu.action_information3 = p_si_status
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = le_lu.action_context_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = le_lu.action_information2
AND cde_dtl.action_information3 = rep_cde.action_information2
AND cde_dtl.action_information6 = 'Y'
AND rep_cde.action_context_type = 'AAP'
AND rep_cde.action_context_id = asg_act.action_context_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'ASG_REP_CODE_INFO'
GROUP BY asg_act.action_information2
,le_lu.action_information3;
SELECT SUM(DECODE(asg_act.action_information7
,'N', fnd_number.canonical_to_number(rep_cde.action_information3)
, 0)) a_earnings
,SUM(DECODE(asg_act.action_information7
,'Y', fnd_number.canonical_to_number(rep_cde.action_information3)
, 0)) bl_earnings
,SUM(fnd_number.canonical_to_number(rep_cde.action_information3)) sum_earnings
FROM pay_assignment_actions paa
,pay_action_information le_lu
,pay_action_information asg_act
,pay_action_information rep_cde
,pay_action_information cde_dtl
WHERE paa.payroll_action_id = le_lu.action_context_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND asg_act.action_information2 = le_lu.action_information2
AND asg_act.action_information3 = le_lu.action_information4
AND le_lu.action_context_type = 'PA'
AND le_lu.action_context_id = l_payroll_action_id
AND le_lu.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu.action_information1 = 'LE_SI_LU_INFO'
AND le_lu.action_information2 = p_legal_employer_id
AND le_lu.action_information3 = p_si_status
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = le_lu.action_context_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = le_lu.action_information2
AND cde_dtl.action_information3 = rep_cde.action_information2
AND cde_dtl.action_information6 = 'Y'
AND rep_cde.action_context_type = 'AAP'
AND rep_cde.action_context_id = asg_act.action_context_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'ASG_REP_CODE_INFO'
AND rep_cde.action_information2 NOT IN ('000','250','311','316','950')
GROUP BY asg_act.action_information2
,le_lu.action_information3;
SELECT asg_act.action_information2 legal_employer_id
,le_lu.action_information3 si_status
,DECODE(cde_dtl.action_information4, 'Y', rep_cde.action_information2, 'TOTAL_OTHER_CODES') code
,SUM(fnd_number.canonical_to_number(rep_cde.action_information3)) amount
FROM pay_assignment_actions paa
,pay_action_information le_lu
,pay_action_information asg_act
,pay_action_information rep_cde
,pay_action_information cde_dtl
WHERE paa.payroll_action_id = le_lu.action_context_id
AND asg_act.action_context_type = 'AAP'
AND asg_act.action_context_id = paa.assignment_action_id
AND asg_act.action_information_category = 'EMEA REPORT INFORMATION'
AND asg_act.action_information1 = 'ASG_ACT_INFO'
AND asg_act.action_information2 = le_lu.action_information2
AND asg_act.action_information3 = le_lu.action_information4
AND le_lu.action_context_type = 'PA'
AND le_lu.action_context_id = l_payroll_action_id
AND le_lu.action_information_category = 'EMEA REPORT INFORMATION'
AND le_lu.action_information1 = 'LE_SI_LU_INFO'
AND le_lu.action_information2 = p_legal_employer_id
AND le_lu.action_information3 = p_si_status
AND cde_dtl.action_context_type = 'PA'
AND cde_dtl.action_context_id = le_lu.action_context_id
AND cde_dtl.action_information_category = 'EMEA REPORT INFORMATION'
AND cde_dtl.action_information1 = 'REP_CODE_DTLS'
AND cde_dtl.action_information2 = le_lu.action_information2
AND cde_dtl.action_information3 = rep_cde.action_information2
AND rep_cde.action_context_type = 'AAP'
AND rep_cde.action_context_id = asg_act.action_context_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'ASG_REP_CODE_INFO'
GROUP BY asg_act.action_information2
,le_lu.action_information3
,DECODE(cde_dtl.action_information4, 'Y',rep_cde.action_information2, 'TOTAL_OTHER_CODES');
SELECT lu.lookup_code si_status
FROM hr_lookups lu
WHERE lu.lookup_type = 'NO_LEGAL_EMP_SI_STATUS';
g_xml_element_table.DELETE;
SELECT payroll_action_id
into l_payroll_action_id
from pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
and fcrs.priority_request_id = fcrs1.priority_request_id
and ppa.request_id between fcrs1.request_id and fcrs.request_id
and ppa.request_id = fcrs1.request_id;
SELECT leg_emp.action_information2 legal_employer_id
,leg_emp.action_information3 legal_employer_name
,leg_emp.action_information4 organization_number
,leg_emp.action_information5 add_line_1
,leg_emp.action_information6 add_line_2
,leg_emp.action_information7 add_line_3
,leg_emp.action_information8 add_post_code
,leg_emp.action_information9 add_post_office
,leg_emp.action_information16 le_tax_municipality
,leg_emp.effective_date effective_date
FROM pay_action_information leg_emp
WHERE leg_emp.action_context_type = 'PA'
AND leg_emp.action_context_id = l_payroll_action_id
AND leg_emp.action_information_category = 'EMEA REPORT INFORMATION'
AND leg_emp.action_information1 = 'LEG_EMP_INFO'
ORDER BY leg_emp.action_information3;
SELECT DISTINCT
summ_cde.action_information12 tax_municipality
,per.action_information2 person_id
,per.action_information3 full_name
,per.action_information4 employee_number
,per.action_information5 national_identifier
,per.action_information6 employed_throughout
,per.action_information7 seamen
,per.action_information8 employed_date_or_days
,per.action_information9 add_line_1
,per.action_information10 add_line_2
,per.action_information11 add_line_3
,per.action_information12 add_post_code
,per.action_information13 add_post_office
FROM pay_action_information summ_cde
,pay_action_information per
WHERE summ_cde.action_context_type = 'PA'
AND summ_cde.action_context_id = l_payroll_action_id
AND summ_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND summ_cde.action_information1 = 'AUDIT_REP_SUMMARY'
AND summ_cde.action_information2 = p_legal_employer_id
AND per.action_context_type = summ_cde.action_context_type
AND per.action_context_id = summ_cde.action_context_id
AND per.action_information_category = summ_cde.action_information_category
AND per.action_information1 = 'PER_INFO'
AND per.action_information2 = summ_cde.action_information3
ORDER BY per.action_information3;
SELECT rep_cde.action_information3 code
,SUM(fnd_number.canonical_to_number(aud_smr.action_information5)) amount
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, p_legal_employer_id, rep_cde.action_information3, 'INFO1') info1_dtype
FROM pay_action_information rep_cde
,pay_action_information aud_smr
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information2 = p_legal_employer_id
AND rep_cde.action_information3 = aud_smr.action_information4
AND rep_cde.action_information4 = 'Y'
AND aud_smr.action_context_type = 'PA'
AND aud_smr.action_context_id = l_payroll_action_id
AND aud_smr.action_information_category = 'EMEA REPORT INFORMATION'
AND aud_smr.action_information1 = 'AUDIT_REP_SUMMARY'
AND aud_smr.action_information2 = p_legal_employer_id
AND aud_smr.action_information3 = l_person_id
group by rep_cde.action_information3 ;
SELECT SUM(fnd_number.canonical_to_number(aud_smr.action_information6)) info1
FROM pay_action_information rep_cde
,pay_action_information aud_smr
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information2 = p_legal_employer_id
AND rep_cde.action_information3 = aud_smr.action_information4
AND rep_cde.action_information4 = 'Y'
AND aud_smr.action_context_type = 'PA'
AND aud_smr.action_context_id = l_payroll_action_id
AND aud_smr.action_information_category = 'EMEA REPORT INFORMATION'
AND aud_smr.action_information1 = 'AUDIT_REP_SUMMARY'
AND aud_smr.action_information2 = p_legal_employer_id
AND aud_smr.action_information3 = l_person_id
AND aud_smr.action_information4 = l_eoy_code;
SELECT decode(count(distinct(aud_smr.action_information6)),1,max(aud_smr.action_information6),'') info1
FROM pay_action_information rep_cde
,pay_action_information aud_smr
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information2 = p_legal_employer_id
AND rep_cde.action_information3 = aud_smr.action_information4
AND rep_cde.action_information4 = 'Y'
AND aud_smr.action_context_type = 'PA'
AND aud_smr.action_context_id = l_payroll_action_id
AND aud_smr.action_information_category = 'EMEA REPORT INFORMATION'
AND aud_smr.action_information1 = 'AUDIT_REP_SUMMARY'
AND aud_smr.action_information2 = p_legal_employer_id
AND aud_smr.action_information3 = l_person_id
AND aud_smr.action_information4 = l_eoy_code;
SELECT summ_cde.action_information4 code
,fnd_number.canonical_to_number(summ_cde.action_information5) amount
,summ_cde.action_information6 info1
,summ_cde.action_information7 info2
,summ_cde.action_information8 info3
,summ_cde.action_information9 info4
,summ_cde.action_information10 info5
,summ_cde.action_information11 info6
,pay_no_eoy_archive.get_code_desc(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4) description
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO1') info1_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO2') info2_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO3') info3_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO4') info4_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO5') info5_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO6') info6_prompt
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO1') info1_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO2') info2_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO3') info3_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO4') info4_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO5') info5_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO6') info6_dtype
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO1') info1_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO2') info2_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO3') info3_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO4') info4_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO5') info5_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO6') info6_orid -- changes 2007-08
FROM pay_action_information summ_cde
,pay_action_information rep_cde
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information2 = p_legal_employer_id
AND rep_cde.action_information3 = summ_cde.action_information4
AND rep_cde.action_information4 <> 'Y'
AND summ_cde.action_context_type = 'PA'
AND summ_cde.action_context_id = l_payroll_action_id
AND summ_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND summ_cde.action_information1 = 'AUDIT_REP_SUMMARY'
AND summ_cde.action_information2 = p_legal_employer_id
AND summ_cde.action_information3 = p_person_id
ORDER BY summ_cde.action_information2,
summ_cde.action_information4;
SELECT DISTINCT rep_cde.action_information3 code
,rep_cde.action_information11 description
FROM pay_action_information rep_cde
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information4 = 'Y';
g_xml_element_table.DELETE;
SELECT payroll_action_id
into l_payroll_action_id
from pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
and fcrs.priority_request_id = fcrs1.priority_request_id
and ppa.request_id between fcrs1.request_id and fcrs.request_id
and ppa.request_id = fcrs1.request_id;
g_fixed_code.DELETE;
SELECT leg_emp.action_information2 legal_employer_id
,leg_emp.action_information3 legal_employer_name
,leg_emp.action_information4 organization_number
,leg_emp.action_information5 add_line_1
,leg_emp.action_information6 add_line_2
,leg_emp.action_information7 add_line_3
,leg_emp.action_information8 add_post_code
,leg_emp.action_information9 add_post_office
,leg_emp.action_information16 le_tax_municipality
,leg_emp.effective_date effective_date
FROM pay_action_information leg_emp
WHERE leg_emp.action_context_type = 'PA'
AND leg_emp.action_context_id = l_payroll_action_id
AND leg_emp.action_information_category = 'EMEA REPORT INFORMATION'
AND leg_emp.action_information1 = 'LEG_EMP_INFO'
ORDER BY leg_emp.action_information3;
SELECT DISTINCT
summ_cde.action_information12 tax_municipality
,per.action_information2 person_id
,per.action_information3 full_name
,per.action_information4 employee_number
,per.action_information5 national_identifier
,per.action_information6 employed_throughout
,per.action_information7 seamen
,per.action_information8 employed_date_or_days
,per.action_information9 add_line_1
,per.action_information10 add_line_2
,per.action_information11 add_line_3
,per.action_information12 add_post_code
,per.action_information13 add_post_office
,per.action_information14 employment_start_date --Changes 2007/2008
,per.action_information15 employment_end_date --Changes 2007/2008
FROM pay_action_information summ_cde
,pay_action_information per
WHERE summ_cde.action_context_type = 'PA'
AND summ_cde.action_context_id = l_payroll_action_id
AND summ_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND summ_cde.action_information1 = 'AUDIT_REP_SUMMARY'
AND summ_cde.action_information2 = p_legal_employer_id
AND per.action_context_type = summ_cde.action_context_type
AND per.action_context_id = summ_cde.action_context_id
AND per.action_information_category = summ_cde.action_information_category
AND per.action_information1 = 'PER_INFO'
AND per.action_information2 = summ_cde.action_information3
ORDER BY per.action_information3;
SELECT rep_cde.action_information3 code
,SUM(fnd_number.canonical_to_number(aud_smr.action_information5)) amount
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, p_legal_employer_id, rep_cde.action_information3, 'INFO1') info1_dtype
FROM pay_action_information rep_cde
,pay_action_information aud_smr
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information2 = p_legal_employer_id
AND rep_cde.action_information3 = aud_smr.action_information4
AND rep_cde.action_information4 = 'Y'
AND aud_smr.action_context_type = 'PA'
AND aud_smr.action_context_id = l_payroll_action_id
AND aud_smr.action_information_category = 'EMEA REPORT INFORMATION'
AND aud_smr.action_information1 = 'AUDIT_REP_SUMMARY'
AND aud_smr.action_information2 = p_legal_employer_id
AND aud_smr.action_information3 = l_person_id
group by rep_cde.action_information3 ;
SELECT SUM(fnd_number.canonical_to_number(aud_smr.action_information6)) info1
FROM pay_action_information rep_cde
,pay_action_information aud_smr
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information2 = p_legal_employer_id
AND rep_cde.action_information3 = aud_smr.action_information4
AND rep_cde.action_information4 = 'Y'
AND aud_smr.action_context_type = 'PA'
AND aud_smr.action_context_id = l_payroll_action_id
AND aud_smr.action_information_category = 'EMEA REPORT INFORMATION'
AND aud_smr.action_information1 = 'AUDIT_REP_SUMMARY'
AND aud_smr.action_information2 = p_legal_employer_id
AND aud_smr.action_information3 = l_person_id
AND aud_smr.action_information4 = l_eoy_code;
SELECT decode(count(distinct(aud_smr.action_information6)),1,max(aud_smr.action_information6),'') info1
FROM pay_action_information rep_cde
,pay_action_information aud_smr
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information2 = p_legal_employer_id
AND rep_cde.action_information3 = aud_smr.action_information4
AND rep_cde.action_information4 = 'Y'
AND aud_smr.action_context_type = 'PA'
AND aud_smr.action_context_id = l_payroll_action_id
AND aud_smr.action_information_category = 'EMEA REPORT INFORMATION'
AND aud_smr.action_information1 = 'AUDIT_REP_SUMMARY'
AND aud_smr.action_information2 = p_legal_employer_id
AND aud_smr.action_information3 = l_person_id
AND aud_smr.action_information4 = l_eoy_code;
SELECT summ_cde.action_information4 code
,fnd_number.canonical_to_number(summ_cde.action_information5) amount
,summ_cde.action_information6 info1
,summ_cde.action_information7 info2
,summ_cde.action_information8 info3
,summ_cde.action_information9 info4
,summ_cde.action_information10 info5
,summ_cde.action_information11 info6
,pay_no_eoy_archive.get_code_desc(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4) description
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO1') info1_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO2') info2_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO3') info3_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO4') info4_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO5') info5_prompt
,pay_no_eoy_archive.get_info_prompt(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO6') info6_prompt
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO1') info1_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO2') info2_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO3') info3_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO4') info4_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO5') info5_dtype
,pay_no_eoy_archive.get_info_dtype(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO6') info6_dtype
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO1') info1_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO2') info2_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO3') info3_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO4') info4_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO5') info5_orid -- changes 2007-08
,pay_no_eoy_archive.get_xml_orid(l_payroll_action_id, summ_cde.action_information2, summ_cde.action_information4, 'INFO6') info6_orid -- changes 2007-08
FROM pay_action_information summ_cde
,pay_action_information rep_cde
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information2 = p_legal_employer_id
AND rep_cde.action_information3 = summ_cde.action_information4
AND rep_cde.action_information4 <> 'Y'
AND summ_cde.action_context_type = 'PA'
AND summ_cde.action_context_id = l_payroll_action_id
AND summ_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND summ_cde.action_information1 = 'AUDIT_REP_SUMMARY'
AND summ_cde.action_information2 = p_legal_employer_id
AND summ_cde.action_information3 = p_person_id
ORDER BY summ_cde.action_information2,
summ_cde.action_information4;
SELECT DISTINCT rep_cde.action_information3 code
,rep_cde.action_information11 description
FROM pay_action_information rep_cde
WHERE rep_cde.action_context_type = 'PA'
AND rep_cde.action_context_id = l_payroll_action_id
AND rep_cde.action_information_category = 'EMEA REPORT INFORMATION'
AND rep_cde.action_information1 = 'REP_CODE_DTLS'
AND rep_cde.action_information4 = 'Y';
g_xml_element_table.DELETE;
SELECT payroll_action_id
into l_payroll_action_id
from pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
and fcrs.priority_request_id = fcrs1.priority_request_id
and ppa.request_id between fcrs1.request_id and fcrs.request_id
and ppa.request_id = fcrs1.request_id;
g_fixed_code.DELETE;
g_summary_code_orid.DELETE;