[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_ARCHIVE_RSEA.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_NAME')
,PAY_NO_ARCHIVE_RSEA.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_NAME')
-- ,LPAD(PAY_NO_ARCHIVE_RSEA.GET_PARAMETER(legislative_parameters,'PERIOD_RPT'),2,'0')
-- ,PAY_NO_ARCHIVE_RSEA.GET_PARAMETER(legislative_parameters,'YEAR_RPT')
,PAY_NO_ARCHIVE_RSEA.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 , 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_LOCAL_UNIT_DETAILS';
SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2 , hoi2.ORG_INFORMATION3 , hoi2.ORG_INFORMATION5
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 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
hoi1.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 o1.name , hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
hoi1.POSTAL_CODE , SUBSTR(hlu.MEANING , INSTR(hlu.MEANING,' ', 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_tax_office_id
AND hoi2.org_information1 = 'NO_TAX_OFFICE'
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_canonical_start_date
AND l_canonical_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 IN ('C','S') -- 10229512
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10229512
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_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_canonical_start_date
AND l_canonical_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 IN ('C','S') -- 10229512
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10229512
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_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 SUBSTR( meaning ,1,1) zone , TRIM(SUBSTR( meaning ,2)) municipal_name
FROM hr_lookups
WHERE lookup_type='NO_TAX_MUNICIPALITY'
AND enabled_flag='Y'
AND lookup_code = p_municipal_no;
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';
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;
SELECT hoi2.org_information1
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 hoi2.org_information1
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);
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 DETAILS'
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_emp_id
,p_action_information3 => l_period||l_year
,p_action_information4 => l_org_number
,p_action_information5 => l_municipal_no
,p_action_information6 => l_le_name
,p_action_information7 => l_address_line_1
,p_action_information8 => l_address_line_2||' '||l_address_line_3
,p_action_information9 => l_postal_code
,p_action_information10 => l_postal_office
,p_action_information11 => l_email
,p_action_information12 => l_phone
,p_action_information13 => l_tax_office_name
,p_action_information14 => l_taddress_line_1
,p_action_information15 => l_taddress_line_2||' '||l_taddress_line_3
,p_action_information16 => l_tpostal_code||' '||l_tpostal_office
,p_action_information17 => l_industry_status
,p_action_information18 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_el,0))
,p_action_information19 => null
,p_action_information20 => null
,p_action_information21 => null
,p_action_information22 => null
,p_action_information23 => null
,p_action_information24 => null
,p_action_information25 => null
,p_action_information26 => null
,p_action_information27 => null
,p_action_information28 => null
,p_action_information29 => null
,p_action_information30 => null );
/* 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 => l_le_name
,p_action_information3 => l_lu_name
,p_action_information4 => l_period
,p_action_information5 => l_year
,p_action_information6 => null
,p_action_information7 => null
,p_action_information8 => null
,p_action_information9 => null
,p_action_information10 => null
,p_action_information11 => null
,p_action_information12 => null
,p_action_information13 => null
,p_action_information14 => null
,p_action_information15 => null
,p_action_information16 => null
,p_action_information17 => null
,p_action_information18 => null
,p_action_information19 => null
,p_action_information20 => null
,p_action_information21 => null
,p_action_information22 => null
,p_action_information23 => null
,p_action_information24 => null
,p_action_information25 => null
,p_action_information26 => null
,p_action_information27 => null
,p_action_information28 => null
,p_action_information29 => null
,p_action_information30 => null );
/* 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;
/* Inserting Local unit level data related to employer contributions*/
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 => 'M'
,p_action_information3 => l_municipal_no
,p_action_information4 => l_municipal_name
,p_action_information5 => l_zone
,p_action_information6 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_Witholding_Tax,0)))
,p_action_information7 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_u_contribution_basis,0)))
,p_action_information8 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_o_contribution_basis,0)))
,p_action_information9 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_u_rate,0))
,p_action_information10 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_o_rate,0))
,p_action_information11 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_u_calc_contribution,0)))
,p_action_information12 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_o_calc_contribution,0)))
,p_action_information13 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eWitholding_Tax,0)))
,p_action_information14 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eu_contribution_basis,0)))
,p_action_information15 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eo_contribution_basis,0)))
,p_action_information16 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eu_rate,0))
,p_action_information17 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eo_rate,0))
,p_action_information18 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eu_calc_contribution,0)))
,p_action_information19 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eo_calc_contribution ,0)))
,p_action_information20 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_spr_contribution_basis,0)))
,p_action_information21 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_spr_calc_contribution,0)))
,p_action_information22 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_spr_rate,0))
,p_action_information23 => nvl(l_fe_fma_calc_contribution ,0)/ nvl(l_fe_fm_amount,0)
,p_action_information24 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_fm_amount,0)))
,p_action_information25 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_fma_calc_contribution ,0)))
,p_action_information26 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_emp_contri_el,0)))
,p_action_information27 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_emp_contri_el_bimonth,0)))
,p_action_information28 => null
,p_action_information29 => null
,p_action_information30 => null );
-- Return cursor that selects no rows
p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';