[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
SELECT pdb.defined_balance_id
INTO l_defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pbd.database_item_suffix = p_dbi_suffix
AND pbd.legislation_code = 'NO'
AND pbt.balance_name = p_balance_name
AND pbt.legislation_code = 'NO'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.legislation_code = 'NO';
SELECT PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_NAME')
,PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_NAME')
,PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT o1.name lu_name
,hoi2.org_information4 industry_status
,hoi2.org_information2 nace_code
,hoi2.org_information1 org_num
,hoi2.org_information6 municipal_no
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'NO_LOCAL_UNIT_DETAILS';
SELECT o1.name le_name
,hoi2.org_information1 org_number
,hoi2.org_information2 municipal_no
,hoi2.org_information3 industry_status
,hoi2.org_information4 nace_code
-- ,hoi2.org_information5 tax_off
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_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_LEGAL_EMPLOYER_DETAILS' ;
SELECT hoi2.org_information2 email
,hoi3.org_information2 phone
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
,hr_organization_information hoi3
WHERE o1.business_group_id = l_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 hoi2.organization_id (+) = o1.organization_id
AND hoi2.org_information_context(+) = 'ORG_CONTACT_DETAILS'
AND hoi2.org_information1(+) = 'EMAIL'
AND hoi3.organization_id (+) = o1.organization_id
AND hoi3.org_information_context(+) = 'ORG_CONTACT_DETAILS'
AND hoi3.org_information1(+) = 'PHONE';
SELECT hoi1.address_line_1 address_line_1
,hoi1.address_line_2 address_line_2
,hoi1.address_line_3 address_line_3
,hoi1.postal_code postal_code
,SUBSTR(hlu.meaning, INSTR(hlu.meaning,' ', 1,1), LENGTH(hlu.meaning)-(INSTR(hlu.meaning,' ', 1,1)-1)) postal_office
FROM hr_organization_units o1
,hr_locations hoi1
,hr_organization_information hoi2
,hr_lookups hlu
WHERE o1.business_group_id = l_business_group_id
AND hoi1.location_id = o1.location_id
AND hoi2.organization_id = o1.organization_id
AND hoi2.organization_id = csr_v_legal_emp_id
AND hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi2.org_information_context = 'CLASS'
AND hlu.lookup_type = 'NO_POSTAL_CODE'
AND hlu.enabled_flag = 'Y'
AND hlu.lookup_code = hoi1.POSTAL_CODE;
SELECT DISTINCT act.assignment_id assignment_id
FROM pay_payroll_actions ppa
,pay_payroll_actions appa
,pay_payroll_actions appa2
,pay_assignment_actions act
,pay_assignment_actions act1
,pay_action_interlocks pai
,per_all_assignments_f as1
,hr_soft_coding_keyflex hsck
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_start_date AND l_end_date
AND appa.action_type IN ('R','Q')
-- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C' -- Completed
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C' -- Completed
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_start_date AND l_end_date
-- Prepayments or Quickpay Prepayments
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
AND hsck.SOFT_CODING_KEYFLEX_ID = as1.SOFT_CODING_KEYFLEX_ID
AND EXISTS (SELECT hoi1.organization_id
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
,hr_organization_information hoi3
,hr_organization_information hoi4
WHERE hoi1.organization_id = o1.organization_id
AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.org_information_context ='NO_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.org_information_context ='CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
aND hoi3.organization_id = p_legal_employer_id
AND hoi1.organization_id = hoi4.organization_id
AND hoi4.org_information_context ='NO_LOCAL_UNIT_DETAILS'
AND hoi4.org_information5 = 'N'
AND to_char(hoi1.organization_id) = hsck.segment2 );
SELECT DISTINCT act.assignment_id assignment_id
FROM pay_payroll_actions ppa
,pay_payroll_actions appa
,pay_payroll_actions appa2
,pay_assignment_actions act
,pay_assignment_actions act1
,pay_action_interlocks pai
,per_all_assignments_f as1
,hr_soft_coding_keyflex hsck
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_start_date AND l_end_date
AND appa.action_type IN ('R','Q')
-- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C' -- Completed
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C' -- Completed
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_start_date AND l_end_date
-- Prepayments or Quickpay Prepayments
AND hsck.soft_coding_keyflex_id = as1.soft_coding_keyflex_id
AND hsck.segment2 = to_char(p_local_unit_id)
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id ;
SELECT eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND asg2.primary_flag = 'Y'
AND et.element_name = 'Tax Card'
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Tax Municipality'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
SELECT hr_de_general.get_uci(l_effective_date,t.user_table_id,r.user_row_id,'ZONE') zone
,hr_general.decode_lookup('NO_TAX_MUNICIPALITY',
hr_de_general.get_uci(l_effective_date,t.user_table_id,r.user_row_id,'MAPPING_ID')) municipal_name
FROM pay_user_tables t
,pay_user_rows_f r
WHERE 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 = p_municipal_no
AND l_effective_date BETWEEN r.effective_start_date AND r.effective_end_date;
SELECT hoi1.organization_id lu_id
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
,hr_organization_information hoi3
,hr_organization_information hoi4
WHERE hoi1.organization_id = o1.organization_id
AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.org_information_context ='NO_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.org_information_context = 'CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi3.organization_id = p_legal_employer_id
AND hoi1.organization_id = hoi4.organization_id
AND hoi4.org_information_context = 'NO_LOCAL_UNIT_DETAILS'
AND hoi4.org_information5 = 'N';
SELECT to_number(hoi2.org_information4)
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'NO_NI_EXEMPTION_LIMIT'
AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi2.org_information2)
AND fnd_date.canonical_to_date(hoi2.org_information3);
SELECT to_number(hoi2.org_information4)
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_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_date_earned BETWEEN fnd_date.canonical_to_date(hoi2.org_information2)
AND fnd_date.canonical_to_date(hoi2.org_information3);
SELECT SUM(hoi2.org_information1) exempt_limit
,SUM(hoi2.org_information4) economic_aid
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'NO_NI_EXEMPTION_LIMIT'
AND trunc(p_date_earned,'Y') >= fnd_date.canonical_to_date(hoi2.org_information2)
AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
SELECT SUM(hoi2.org_information1) exempt_limit
,SUM(hoi2.org_information4) economic_aid
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_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 trunc(p_date_earned,'Y') >= fnd_date.canonical_to_date(hoi2.org_information2)
AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
SELECT SUM(hoi2.org_information4) economic_aid
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'NO_NI_EXEMPTION_LIMIT'
AND p_date_earned > fnd_date.canonical_to_date(hoi2.org_information2)
AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
SELECT SUM(hoi2.org_information4) economic_aid
FROM hr_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE o1.business_group_id = l_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_date_earned > fnd_date.canonical_to_date(hoi2.org_information2)
AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
SELECT global_value
FROM ff_globals_f
WHERE global_name = p_global_name
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
p_sql :='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';
SELECT count(*) INTO l_count
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_context_type = 'PA'
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYNORSEA';
/* Inserting header details belonging to Employer*/
pay_action_information_api.create_action_information (
p_action_information_id => l_action_info_id
,p_action_context_id => p_payroll_action_id
,p_action_context_type => 'PA'
,p_object_version_number => l_ovn
,p_effective_date => l_effective_date
,p_source_id => NULL
,p_source_text => NULL
,p_action_information_category => 'EMEA REPORT INFORMATION'
,p_action_information1 => 'PYNORSEA'
,p_action_information2 => l_legal_employer_id
,p_action_information3 => l_period || l_year
,p_action_information4 => rg_LE_Details.org_number
,p_action_information5 => rg_LE_Details.municipal_no
,p_action_information6 => rg_LE_Details.le_name
,p_action_information7 => rg_LE_addr.address_line_1
,p_action_information8 => rg_LE_addr.address_line_2
,p_action_information9 => rg_LE_addr.postal_code
,p_action_information10 => rg_LE_addr.postal_office
,p_action_information11 => rg_LE_Contact.email
,p_action_information12 => rg_LE_Contact.phone
,p_action_information13 => null -- for Tax unit details
,p_action_information14 => null -- for Tax unit details
,p_action_information15 => null -- for Tax unit details
,p_action_information16 => null -- for Tax unit details
,p_action_information17 => l_industry_status
,p_action_information18 => fnd_number.number_to_canonical(NVL(l_el,0)) -- Other economic support (Economic Aid)
,p_action_information19 => l_nace_code
,p_action_information20 => rg_LE_addr.address_line_3
,p_action_information21 => fnd_number.number_to_canonical(NVL(l_el_b,0)) -- remaining exemption limit prev rep term
,p_action_information22 => fnd_number.number_to_canonical(NVL(l_el_a,0)) -- remaining exemption limit after rep term
);
/* Inserting the selection criteria for generating the report*/
pay_action_information_api.create_action_information (
p_action_information_id => l_action_info_id
,p_action_context_id => p_payroll_action_id
,p_action_context_type => 'PA'
,p_object_version_number => l_ovn
,p_effective_date => l_effective_date
,p_source_id => NULL
,p_source_text => NULL
,p_action_information_category => 'EMEA REPORT DETAILS'
,p_action_information1 => 'PYNORSEA'
,p_action_information2 => rg_LE_Details.le_name
,p_action_information3 => l_lu_name
,p_action_information4 => l_period
,p_action_information5 => l_year
);
/* Inserting municipal codes for the Legal Employer in a PL/SQL table */
IF l_local_unit_id IS NULL THEN
--
l_counter := 0;
/* Inserting municipal codes for the Local Unit in a PL/SQL table */
l_counter := 0;
p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT leg_emp.action_information2 le_id
,leg_emp.action_information3 period_year
,leg_emp.action_information4 org_num
,leg_emp.action_information5 municipal_no
,leg_emp.action_information6 le_name
,leg_emp.action_information7 ada_line1
,leg_emp.action_information8 ada_line2
,leg_emp.action_information9 post_code
,leg_emp.action_information10 post_off
,leg_emp.action_information11 email
,leg_emp.action_information12 phone
,leg_emp.action_information17 industry_status
,fnd_number.canonical_to_number(leg_emp.action_information18) exempt_limit
,leg_emp.action_information19 nace_code
,leg_emp.action_information20 ada_line3
,fnd_number.canonical_to_number(leg_emp.action_information21) exempt_limit_prev
,fnd_number.canonical_to_number(leg_emp.action_information22) exempt_limit_after
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 = 'PYNORSEA';
SELECT lu.action_information4 lu_org_num
,lu.action_information5 lu_municipal_num
,lu.action_information6 lu_municipal_name
,lu.action_information7 lu_zone
,fnd_number.canonical_to_number(lu.action_information8) ec_base
,fnd_number.canonical_to_number(lu.action_information9) ec_amt
,fnd_number.canonical_to_number(lu.action_information10) reimburse_base
,fnd_number.canonical_to_number(lu.action_information11) reimburse_amt
,fnd_number.canonical_to_number(lu.action_information12) UTL1_base
,fnd_number.canonical_to_number(lu.action_information13) UTL1_amt
,fnd_number.canonical_to_number(lu.action_information14) UTR1_base
,fnd_number.canonical_to_number(lu.action_information15) UTR1_amt
,fnd_number.canonical_to_number(lu.action_information16) UTL2_base
,fnd_number.canonical_to_number(lu.action_information17) UTL2_amt
,fnd_number.canonical_to_number(lu.action_information18) pension_base
FROM pay_action_information lu
WHERE lu.action_context_type = 'PA'
AND lu.action_context_id = l_payroll_action_id
AND lu.action_information_category = 'EMEA REPORT INFORMATION'
AND lu.action_information1 = 'PYNORSEA-EC'
ORDER BY 3;
SELECT wt.action_information2 wt_municipal_num
,wt.action_information3 wt_municipal_name
,fnd_number.canonical_to_number(wt.action_information4) wt_tax_value
FROM pay_action_information wt
WHERE wt.action_context_type = 'PA'
AND wt.action_context_id = l_payroll_action_id
AND wt.action_information_category = 'EMEA REPORT INFORMATION'
AND wt.action_information1 = 'PYNORSEA-WT'
ORDER BY 2;
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_information3 period_year
,leg_emp.action_information4 org_num
,leg_emp.action_information5 municipal_no
,leg_emp.action_information6 le_name
,leg_emp.action_information7 add_line1
,leg_emp.action_information8 add_line2
,leg_emp.action_information9 post_code
,leg_emp.action_information10 post_off
,leg_emp.action_information17 industry_status
,fnd_number.canonical_to_number(leg_emp.action_information18) exempt_limit
,leg_emp.action_information19 nace_code
,leg_emp.action_information20 add_line3
,fnd_number.canonical_to_number(leg_emp.action_information21) exempt_limit_prev
,fnd_number.canonical_to_number(leg_emp.action_information22) exempt_limit_after
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 = 'PYNORSEA';
SELECT SUM(fnd_number.canonical_to_number(lu.action_information8)) EC_base_sum
,SUM(fnd_number.canonical_to_number(lu.action_information10)) REIM_base_sum
,SUM(fnd_number.canonical_to_number(lu.action_information12)) UTL1_base_sum
,SUM(fnd_number.canonical_to_number(lu.action_information14)) UTR1_base_sum
,SUM(fnd_number.canonical_to_number(lu.action_information16)) UTL2_base_sum
,SUM(fnd_number.canonical_to_number(lu.action_information9))
+ SUM(fnd_number.canonical_to_number(lu.action_information11))
+ SUM(fnd_number.canonical_to_number(lu.action_information13))
+ SUM(fnd_number.canonical_to_number(lu.action_information15))
+ SUM(fnd_number.canonical_to_number(lu.action_information17)) amt_sum
FROM pay_action_information lu
WHERE lu.action_context_type = 'PA'
AND lu.action_context_id = l_payroll_action_id
AND lu.action_information_category = 'EMEA REPORT INFORMATION'
AND lu.action_information1 = 'PYNORSEA-EC';
SELECT lu.action_information4 lu_org_num
,lu.action_information5 lu_municipal_num
,fnd_number.canonical_to_number(lu.action_information8) ec_base
,fnd_number.canonical_to_number(lu.action_information10) reimburse_base
,fnd_number.canonical_to_number(lu.action_information18) pension_base_sum
FROM pay_action_information lu
WHERE lu.action_context_type = 'PA'
AND lu.action_context_id = l_payroll_action_id
AND lu.action_information_category = 'EMEA REPORT INFORMATION'
AND lu.action_information1 = 'PYNORSEA-EC'
ORDER BY 3;
SELECT wt.action_information2 wt_municipal_num
,fnd_number.canonical_to_number(wt.action_information4) wt_tax_value
FROM pay_action_information wt
WHERE wt.action_context_type = 'PA'
AND wt.action_context_id = l_payroll_action_id
AND wt.action_information_category = 'EMEA REPORT INFORMATION'
AND wt.action_information1 = 'PYNORSEA-WT'
ORDER BY 1;
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;