[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT legislative_parameters,payroll_id
INTO leg_param,l_ppa_payroll_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id ;
UPDATE pay_payroll_actions
SET payroll_id = l_payroll_id
WHERE payroll_action_id = p_payroll_action_id;
SELECT pap.accrual_plan_name
,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
,pap.accrual_units_of_measure
,ppa.payroll_id
,pap.business_group_id
,pap.accrual_plan_id
FROM pay_accrual_plans pap
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE pet.element_type_id = pap.accrual_plan_element_type_id
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND paa.assignment_id = pee.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R','Q')
AND ppa.action_status = 'C'
AND ppa.date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.date_earned BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND ppa.date_earned BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND paa.assignment_id = p_assignment_id
AND paa.assignment_action_id = p_assignment_action_id;
SELECT substr(p.product_version,1,2) INTO l_product_release
FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
WHERE a.application_id = p.application_id
AND a.application_id = t.application_id
AND t.language = Userenv ('LANG')
AND Substr (a.application_short_name, 1, 5) = 'PAY';
select accrual_plan_name into l_accrual_plan_name from pay_accrual_plans_tl
where accrual_plan_id = l_accrual_plan_id
and LANGUAGE = USERENV('LANG');
SELECT pat.name absence_type
,pet.reporting_name reporting_name
,decode(pet.processing_type,'R',greatest(pab.date_start,PTP.START_DATE),pab.date_start) start_date
,decode(pet.processing_type,'R',least(pab.date_end,PTP.END_DATE),pab.date_end) end_date
,decode(pet.processing_type,'R',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours)) absence_days
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
,pay_run_result_values prrv
,per_time_periods ptp
,pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f pee
,per_absence_attendance_types pat
,per_absence_attendances pab
WHERE paa.assignment_action_id = p_assg_act_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q','R')
AND ptp.time_period_id = ppa.time_period_id
AND paa.assignment_action_id = prr.assignment_action_id
AND pet.element_type_id = prr.element_type_id
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = pat.input_value_id
AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
AND pab.absence_attendance_id = pee.creator_id
AND pee.creator_type = 'A'
AND pee.assignment_id = paa.assignment_id
AND pee.element_entry_id = prr.source_id
AND piv.input_value_id = prrv.input_value_id
AND prr.run_result_id = prrv.run_result_id
AND prr.status in ('P','PA')
AND ppa.effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT nvl(petl.reporting_name,petl.element_name) element_reporting_name
, decode(pec.classification_name ,'Special Payments','Taxable Earnings'
,'Annual Bonus','Taxable Earnings'
,'Retro Taxable Earnings','Taxable Earnings'
,'Retro Special Payments','Taxable Earnings'
,'Retro Annual Bonus','Taxable Earnings'
,'Voluntary Deductions','Voluntary Dedn'
,'Severance Payments','Taxable Earnings'
,'Direct Payments','Non Taxable Earnings'
,'Retro Statutory Deductions','Statutory Deductions'
,'Retro Variable Yearly Earnings','Taxable Earnings'
,'Variable Yearly Earnings','Taxable Earnings'
,'Retro Pre Tax Non Statutory Deductions' , 'Pre Tax Non Statutory Deductions'
,pec.classification_name
) classification_name
, sum(decode(substr(piv.uom,1,1), 'M', fnd_number.canonical_to_number(prrv.result_value), null)) amount
, decode(pet.input_currency_code, 'CNY',NULL
, pet.input_currency_code) foreign_currency_code
, pay_cn_payslip.get_exchange_rate(pet.input_currency_code
,pet.output_currency_code
,ppa.effective_date
,ppa.business_group_id
) exchange_rate
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_run_results prr
, pay_run_result_values prrv
, pay_input_values_f piv
, pay_element_types_f pet
, pay_element_types_f_tl petl
, pay_element_classifications pec
,pay_action_interlocks pai
WHERE ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND pai.locking_action_id = p_assignment_action_id
AND pec.classification_name IN ('Taxable Earnings'
,'Voluntary Deductions'
,'Non Taxable Earnings'
,'Statutory Deductions'
,'Special Payments'
,'Annual Bonus'
,'Severance Payments'
,'Direct Payments'
,'Retro Taxable Earnings'
,'Retro Statutory Deductions'
,'Retro Special Payments'
,'Retro Annual Bonus'
,'Variable Yearly Earnings'
,'Retro Variable Yearly Earnings'
,'Pre Tax Non Statutory Deductions'
,'Retro Pre Tax Non Statutory Deductions'
)
AND pec.legislation_code = 'CN'
AND pec.classification_id = pet.classification_id
AND pet.element_name <> 'Special Payments Normal'
AND pet.element_type_id = petl.element_type_id
AND petl.language = USERENV('LANG')
AND pet.element_type_id = piv.element_type_id
AND piv.name = decode(pec.classification_name,'Special Payments','Payment Amount'
,'Pay Value')
AND pet.element_type_id = prr.element_type_id
AND prr.run_result_id = prrv.run_result_id
AND prr.status in ('P','PA')
AND piv.input_value_id = prrv.input_value_id
AND ppa.effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pai.locked_action_id = paa.assignment_action_id
GROUP BY pet.rowid
, decode(pec.classification_name ,'Special Payments','Taxable Earnings'
,'Annual Bonus','Taxable Earnings'
,'Retro Taxable Earnings','Taxable Earnings'
,'Retro Special Payments','Taxable Earnings'
,'Retro Annual Bonus','Taxable Earnings'
,'Voluntary Deductions','Voluntary Dedn'
,'Severance Payments','Taxable Earnings'
,'Direct Payments','Non Taxable Earnings'
,'Retro Statutory Deductions','Statutory Deductions'
,'Retro Variable Yearly Earnings','Taxable Earnings'
,'Variable Yearly Earnings','Taxable Earnings'
,'Retro Pre Tax Non Statutory Deductions' , 'Pre Tax Non Statutory Deductions'
,pec.classification_name
)
, nvl(petl.reporting_name,petl.element_name)
, pet.processing_priority
, pet.input_currency_code
, pay_cn_payslip.get_exchange_rate(pet.input_currency_code
,pet.output_currency_code
,ppa.effective_date
, ppa.business_group_id
);
SELECT petl.element_name
FROM pay_element_types_f pet,
pay_element_types_f_tl petl
WHERE pet.element_name = p_element_name
AND pet.legislation_code = 'CN'
AND pet.element_type_id = petl.element_type_id
AND petl.language = userenv('LANG');
SELECT nvl(bal_tl.reporting_name, bal_tl.balance_name)
FROM pay_balance_types bal
,pay_balance_types_tl bal_tl
WHERE bal.balance_name = c_balance_name
AND bal.legislation_code = 'CN'
AND bal.balance_type_id = bal_tl.balance_type_id
AND bal_tl.language = USERENV('LANG');
SELECT 'exists'
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_context_type = 'PA'
AND action_information_category = 'ADDRESS DETAILS'
AND action_information1 = p_employer_id
AND action_information14 = 'Legal Employer Address'
AND effective_date = p_effective_date;
SELECT hl.address_line_1
,hl.address_line_2
,hr_general.decode_lookup('CN_PROVINCE',hl.town_or_city) province
,ft.territory_short_name country
,hl.postal_code
,hl.telephone_number_1
FROM hr_all_organization_units hou
,hr_locations hl
,fnd_territories_tl ft
WHERE hou.organization_id = p_employer_id
AND hou.location_id = hl.location_id
AND hl.country = ft.territory_code
AND ft.language = userenv ('LANG');
SELECT hoi.org_information8
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_employer_id
AND hoi.org_information_context like 'PER_EMPLOYER_INFO_CN' ;
SELECT action_information_id
,action_information1
,action_information2
FROM pay_action_information
WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
AND action_context_id = p_assactid
AND action_context_type = 'AAP';
SELECT action_information_id
FROM pay_action_information
WHERE action_information_category = 'EMPLOYEE DETAILS'
AND action_context_id = p_assactid
AND action_context_type = 'AAP';
SELECT tax_unit_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_curr_pymt_ass_act_id;
SELECT pea.segment1 bank_name
,pea.segment2 bank_branch
,pea.segment3 account_number
,ppm.percentage percentage
,pop.currency_code
FROM pay_external_accounts pea
,pay_pre_payments ppp
,pay_org_payment_methods_f pop
,pay_personal_payment_methods_f ppm
WHERE ppp.assignment_action_id = p_curr_pymt_ass_act_id
AND nvl(ppp.personal_payment_method_id,0) = nvl(p_personal_payment_method_id,0)
AND ppp.org_payment_method_id = p_org_payment_method_id
AND ppp.personal_payment_method_id = ppm.personal_payment_method_id (+)
AND ppp.org_payment_method_id = pop.org_payment_method_id
AND ppm.external_account_id = pea.external_account_id (+)
AND p_pre_effective_date BETWEEN pop.effective_start_date
AND pop.effective_end_date
AND p_pre_effective_date BETWEEN nvl(ppm.effective_start_date,p_pre_effective_date)
AND nvl(ppm.effective_end_date,p_pre_effective_date);
SELECT hsck.segment20 -- Tax Area
,hr_general.decode_lookup('CN_PAYOUT_LOCATION',hsck.segment22) -- Payout Location
FROM hr_soft_coding_keyflex hsck
,per_all_assignments_f paaf
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_assactid
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paaf.assignment_id
AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
AND ppa.effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT paa.person_id
FROM per_all_assignments_f paa
WHERE paa.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT pap.per_information8 expatriate
FROM per_all_people_f pap
WHERE pap.person_id = p_person_id
AND p_date_earned BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT pei.pei_information2
FROM per_people_extra_info pei
WHERE pei.person_id = p_person_id
AND pei.pei_information_category = 'PER_PASSPORT_INFO_CN' ;
hr_utility.set_location('Calling update Net Pay Distribution',60);
pay_action_information_api.update_action_information
(
p_action_information_id => net_pay_rec.action_information_id
,p_object_version_number => l_ovn
,p_action_information5 => l_bank_name
,p_action_information6 => l_bank_branch
,p_action_information7 => l_account_number
,p_action_information12 => l_percentage
,p_action_information13 => l_currency_code
);
pay_action_information_api.update_action_information
(
p_action_information_id => l_emp_det_act_info_id
,p_object_version_number => l_ovn
,p_action_information23 => l_tax_area
,p_action_information24 => l_payroll_location
,p_action_information25 => l_expatriate
,p_action_information26 => l_passport
);
SELECT paa_arch_chd.assignment_action_id chld_arc_assignment_action_id
,paa_arch_chd.payroll_action_id arc_payroll_action_id
,paa_pre.assignment_action_id pre_assignment_action_id
,paa_run.assignment_action_id run_assignment_action_id
,paa_run.payroll_action_id run_payroll_action_id
,ppa_pre.effective_date pre_effective_date
,paa_arch_chd.assignment_id
,ppa_run.effective_date run_effective_date
,ppa_run.date_earned run_date_earned
,ptp.end_date period_end_date
,ptp.time_period_id
,ptp.start_date period_start_date
,ptp.regular_payment_date
FROM pay_assignment_actions paa_arch_chd
,pay_assignment_actions paa_arch_mst
,pay_assignment_actions paa_pre
,pay_action_interlocks pai_pre
,pay_assignment_actions paa_run
,pay_action_interlocks pai_run
,pay_payroll_actions ppa_pre
,pay_payroll_actions ppa_run
,per_time_periods ptp
,per_business_groups pbg
WHERE paa_arch_mst.assignment_action_id = p_master_aa_id
AND paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
AND paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
AND ppa_pre.business_group_id = pbg.business_group_id
AND pbg.business_group_id = ppa_run.business_group_id
AND ppa_pre.payroll_id = ppa_run.payroll_id
AND paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
AND pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
AND pai_pre.locked_action_id = paa_pre.assignment_action_id
AND pai_run.locking_action_id = paa_arch_chd.assignment_action_id
AND pai_run.locked_action_id = paa_run.assignment_action_id
AND ppa_pre.payroll_action_id = paa_pre.payroll_action_id
AND ppa_pre.action_type IN ('P','U')
AND ppa_run.payroll_action_id = paa_run.payroll_action_id
AND ppa_run.action_type IN ('R','Q')
AND ptp.payroll_id = ppa_run.payroll_id
AND ppa_run.date_earned BETWEEN ptp.start_date
AND ptp.end_date
-- Get the highest in sequence for this payslip
AND paa_run.action_sequence =
(
SELECT MAX(paa_run2.action_sequence)
FROM pay_assignment_actions paa_run2
,pay_action_interlocks pai_run2
WHERE pai_run2.locking_action_id = paa_arch_chd.assignment_action_id
AND pai_run2.locked_action_id = paa_run2.assignment_action_id
);
SELECT pps.actual_termination_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp,
per_all_assignments_f paf,
per_periods_of_service pps
WHERE paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND paf.assignment_id = paa.assignment_id
AND pps.period_of_service_id = paf.period_of_service_id
AND ppa.date_earned between ptp.start_date AND ptp.end_date
AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;