[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT effective_date,
start_date,
business_group_id,
TO_NUMBER(SUBSTR(legislative_parameters,
INSTR(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=')
+ LENGTH('TRANSFER_CONSOLIDATION_SET_ID='))),
TO_NUMBER(LTRIM(RTRIM(SUBSTR(legislative_parameters,
INSTR(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ LENGTH('TRANSFER_PAYROLL_ID='),
(INSTR(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=') - 1 )
- (INSTR(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ LENGTH('TRANSFER_PAYROLL_ID='))))))
FROM pay_payroll_actions
WHERE payroll_action_id = cp_payroll_action_id;
SELECT pivf.input_value_id
,pivf.name
,decode(pivf.name,'Hours',1,2) sort_index
FROM pay_input_values_f pivf
WHERE pivf.element_type_id = c_element_type_id
AND substr(pivf.uom,1,1) = 'H'
AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date
ORDER BY sort_index;
SELECT prrv.result_value
FROM pay_run_result_values prrv
WHERE prrv.run_result_id = c_run_result_id
AND prrv.input_value_id = c_input_value_id;
SELECT papf.last_name,
papf.first_name,
papf.middle_names,
papf.pre_name_adjunct,
papf.suffix,
papf.title,
papf.known_as,
papf.marital_status,
papf.sex,
papf.nationality,
paaf.work_at_home
FROM per_people_f papf,
per_assignments_f paaf
WHERE paaf.person_id = papf.person_id
AND paaf.assignment_id = cp_assignment_id
AND p_xfr_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND p_xfr_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
/* api call to insert the data into pay_action_information table with
information category as 'IP EMPLOYEE ADDITIONAL DETAILS' */
hr_utility.set_location(gv_package || lv_procedure_name, 30);
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_assactid
,p_action_context_type=> 'AAP'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_curr_pymt_eff_date => p_curr_pymt_eff_date
,p_tab_rec_data => pay_emp_action_arch.lrr_act_tab
);
/* Insert this into the plsql table if Current or YTD
amount is not Zero */
pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
:= lv_action_category;
SELECT DISTINCT
nvl(petfl.reporting_name, petfl.element_name),
petf.element_information10, -- Primary Balance
petf.element_information12, -- Hours Balance
petf.element_type_id,
petf.processing_priority,
pec.classification_name
FROM pay_element_types_f petf,
pay_element_types_f_tl petfl,
pay_element_classifications pec,
pay_run_results prr,
pay_action_interlocks pai,
pay_assignment_actions paa_pre,
pay_payroll_actions ppa_pre
WHERE ppa_pre.action_type IN ('U', 'P')
AND ppa_pre.effective_date > cp_last_xfr_eff_date
AND ppa_pre.effective_date <= cp_last_pymt_eff_date
AND petf.element_type_id = prr.element_type_id
AND petf.element_type_id = petfl.element_type_id
AND pec.classification_id = petf.classification_id
AND paa_pre.payroll_action_id = ppa_pre.payroll_action_id
AND pai.locking_action_id = paa_pre.assignment_action_id
AND prr.assignment_action_id = pai.locked_action_id
AND ppa_pre.effective_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND petfl.language = USERENV('LANG')
AND petf.business_group_id = cp_business_grp_id
AND paa_pre.assignment_id = cp_assignment_id
AND pec.classification_name NOT IN ('Information','Absence')
ORDER BY 1;
SELECT DISTINCT business_group_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT assignment_id, action_information_category,
jurisdiction_code,
action_information2 element_type_id,
action_information6 primary_balance_id,
action_information9 ytd_amount,
action_information10 reporting_name,
effective_date effective_date,
action_information12 ytd_hours
FROM pay_action_information
WHERE action_information_category IN ('IP EARNINGS', 'IP DEDUCTIONS')
AND action_context_id = cp_xfr_action_id;
SELECT balance_type_id
FROM pay_balance_types
WHERE balance_name = cp_balance_name
AND ((business_group_id = cp_business_group_id AND legislation_code IS NULL)
OR (legislation_code = cp_legislation_code AND business_group_id IS NULL));
SELECT DISTINCT
petf.element_information10, -- Primary Balance
petf.element_information12, -- Hours Balance
petf.element_type_id,
petf.processing_priority,
pec.classification_name
FROM pay_element_types_f petf,
pay_element_classifications pec
WHERE pec.classification_name IN ('Earnings',
'Supplemental Earnings',
'Taxable Benefits',
'Direct Payment',
'Employer Charges',
'Voluntary Deductions',
'Tax Deductions',
'Involuntary Deductions',
'Pre-Tax Deductions'
)
AND pec.classification_id = petf.classification_id
AND petf.element_information10 = cp_primary_balance_id
AND cp_effective_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
ORDER BY 1;
SELECT DISTINCT
nvl(petl.reporting_name, petl.element_name),
petf.element_information10, -- Primary Balance
petf.element_information12, -- Hours Balance
petf.element_type_id,
petf.processing_priority,
pec.classification_name
FROM pay_element_types_f petf,
pay_element_types_f_tl petl,
pay_element_classifications pec,
pay_run_results prr,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
petf.element_type_id = prr.element_type_id
AND pec.classification_id = petf.classification_id
AND ppa.effective_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND petf.element_type_id = petl.element_type_id
AND petl.language = USERENV('LANG')
AND paa.assignment_id = cp_assignment_id
AND prr.assignment_action_id = cp_pymt_action_id
AND prr.assignment_action_id = paa.assignment_action_id
AND NVL(paa.run_type_id, gn_sepchk_run_type_id) IN
(gn_sepchk_run_type_id, gn_np_sepchk_run_type_id)
AND ppa.payroll_action_id = paa.payroll_action_id
AND petf.business_group_id = cp_business_grp_id
AND pec.classification_name NOT IN ('Information','Absence')
ORDER BY 1;
SELECT DISTINCT
nvl(petl.reporting_name, petl.element_name),
petf.element_information10, -- Primary Balance
petf.element_information12, -- Hours Balance
petf.element_type_id,
petf.processing_priority,
pec.classification_name
FROM pay_element_types_f petf,
pay_element_types_f_tl petl,
pay_element_classifications pec,
pay_run_results prr,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_payroll_actions ppa
WHERE
petf.element_type_id = prr.element_type_id
AND pec.classification_id = petf.classification_id
AND ppa.effective_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND petf.element_type_id = petl.element_type_id
AND petl.language = USERENV('LANG')
AND paa.assignment_id = cp_assignment_id
AND pai.locking_action_id = cp_pymt_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.action_sequence <= cp_ytd_act_sequence
AND ppa.payroll_action_id = paa.payroll_action_id
AND petf.business_group_id = cp_business_grp_id
AND pec.classification_name NOT IN ('Information','Absence')
AND NVL(paa.run_type_id, 0) NOT IN
(gn_sepchk_run_type_id, gn_np_sepchk_run_type_id)
ORDER BY 1;
SELECT paa.action_sequence
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_asg_act_id;
SELECT DISTINCT business_group_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT assignment_action_id
,classification_name
,element_category
,element_reporting_name
,sum(amount) amount
,sum(hours) hours
,element_type_id
FROM (
SELECT
paa.assignment_action_id assignment_action_id
,pec.classification_name classification_name
,decode (pec.classification_name
,'Pre-Tax Deductions'
,'Deductions'
,'Tax Deductions'
,'Deductions'
,'Involuntary Deductions'
,'Deductions'
,'Voluntary Deductions'
,'Deductions'
,'Supplemental Earnings'
,'Earnings'
,'Taxable Benefits'
,'Earnings'
,'Direct Payment'
,'Earnings'
,'Employer Charges'
,'Earnings'
,pec.classification_name) element_category
,nvl (petl.reporting_name,petl.element_name) element_reporting_name
,decode (substr (piv.uom,1,1)
,'M'
,prrv.result_value
,NULL) amount
,get_element_payment_hours(prr.assignment_action_id, pet.element_type_id
,paf.pay_basis_id, prr.run_result_id, ppa.effective_date) hours
,pet.element_type_id element_type_id
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
,per_assignments_f paf
WHERE ppa.action_type IN ('R','Q','B','V')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND pec.classification_name NOT IN ('Information','Absence')
AND pec.legislation_code = cp_legislation_code
AND pec.classification_id = pet.classification_id
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 = 'Pay Value'
AND pet.element_type_id = prr.element_type_id
AND prr.run_result_id = prrv.run_result_id
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 ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date /*bug 14845387 */
AND ((paa.source_action_id IS NOT NULL) OR (paa.source_action_id IS NULL AND paa.run_type_id IS NULL))
AND paa.assignment_action_id = cp_pymt_action_id
AND paf.assignment_id = paa.assignment_id
AND cp_sepchk_flag = 'Y'
AND (NVL(paa.run_type_id, gn_sepchk_run_type_id) IN
(gn_sepchk_run_type_id, gn_np_sepchk_run_type_id) OR ppa.action_type = 'B')
)
GROUP BY
assignment_action_id,
classification_name,
element_category,
element_reporting_name,
element_type_id;
SELECT assignment_action_id
,classification_name
,element_category
,element_reporting_name
,sum(amount) amount
,sum(hours) hours
,element_type_id
FROM (
SELECT
pai.locking_action_id assignment_action_id
,pec.classification_name classification_name
,decode (pec.classification_name
,'Pre-Tax Deductions' --
,'Deductions'
,'Tax Deductions' --
,'Deductions'
,'Involuntary Deductions' --
,'Deductions'
,'Voluntary Deductions' --
,'Deductions'
,'Supplemental Earnings'
,'Earnings'
,'Taxable Benefits'
,'Earnings'
,'Direct Payment'
,'Earnings'
,'Employer Charges'
,'Earnings'
,pec.classification_name) element_category
,nvl (petl.reporting_name,petl.element_name) element_reporting_name
,decode (substr (piv.uom,1,1)
,'M'
,prrv.result_value
,NULL) amount
,get_element_payment_hours(prr.assignment_action_id, pet.element_type_id
,paf.pay_basis_id, prr.run_result_id, ppa.effective_date) hours
,pet.element_type_id element_type_id
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
,per_assignments_f paf
WHERE ppa.action_type IN ('R','Q','B','V')
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.locked_action_id = paa.assignment_action_id
AND pec.classification_name NOT IN ('Information','Absence')
AND pec.legislation_code = cp_legislation_code
AND pec.classification_id = pet.classification_id
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 = 'Pay Value'
AND pet.element_type_id = prr.element_type_id
AND prr.run_result_id = prrv.run_result_id
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 ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date /*bug 14845387 */
AND ((paa.source_action_id IS NOT NULL) OR (paa.source_action_id IS NULL AND paa.run_type_id IS NULL))
AND paf.assignment_id = paa.assignment_id
AND cp_sepchk_flag = 'N'
AND pai.locking_action_id = cp_pymt_action_id
AND (NVL(paa.run_type_id, 0) NOT IN
(gn_sepchk_run_type_id, gn_np_sepchk_run_type_id) OR ppa.action_type = 'B')
)
GROUP BY
assignment_action_id,
classification_name,
element_category,
element_reporting_name,
element_type_id;
SELECT DISTINCT business_group_id
FROM per_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT DISTINCT
nvl(petl.reporting_name, petl.element_name),
petf.element_information10, -- Primary Balance
petf.element_information12, -- Hours Balance
petf.element_type_id,
petf.processing_priority,
pec.classification_name
FROM pay_element_types_f petf,
pay_element_types_f_tl petl,
pay_element_classifications pec,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr
WHERE petf.element_type_id = prr.element_type_id
AND pec.classification_id = petf.classification_id
AND petf.element_type_id = petl.element_type_id
AND ppa.effective_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND petl.language = USERENV('LANG')
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = cp_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('Q','R','B')
AND ppa.effective_date >= pay_ip_route_support.tax_year(cp_business_group_id,cp_curr_eff_date)
AND ppa.effective_date <= cp_curr_eff_date
AND petf.business_group_id = cp_business_group_id
AND pec.classification_name NOT IN ('Information','Absence')
ORDER BY 1;
Purpose : This returns the select statement that is
used to created the range rows for the Payslip
Archiver.
Arguments : p_payroll_action_id - Payroll action ID
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE range_code(p_payroll_action_id IN NUMBER
,p_sqlstr OUT NOCOPY VARCHAR2)
IS
ld_end_date DATE;
'SELECT DISTINCT paf.person_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
WHERE ppa.business_group_id = ''' || ln_business_group_id || '''
AND ppa.effective_date BETWEEN fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_start_date) || ''')
AND fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_end_date) || ''')
AND ppa.action_type IN (''U'',''P'',''B'')
AND DECODE(ppa.action_type,
''B'', NVL(ppa.future_process_mode, ''Y''),
''N'') = ''N''
AND ppa.consolidation_set_id = ''' || ln_cons_set_id || '''
AND ppa.payroll_id = ''' || ln_payroll_id || '''
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = ''C''
AND paa.source_action_id IS NULL
AND paf.assignment_id = paa.assignment_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND NOT EXISTS
(SELECT ''x''
FROM pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE pai.locked_action_id = paa.assignment_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.action_type =''X''
AND ppa1.report_type = ''IP_PAYROLL_ARCHIVE'')
AND :payroll_action_id > 0 -- Bug 4202702
ORDER BY paf.person_id';
SELECT paa.assignment_id,
paa.tax_unit_id,
ppa.effective_date,
ppa.date_earned,
ppa.action_type,
paa.assignment_action_id,
paa.payroll_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf
WHERE paf.person_id BETWEEN cp_start_person_id
AND cp_end_person_id
AND paa.assignment_id = paf.assignment_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.consolidation_set_id
= NVL(cp_cons_set_id,ppa.consolidation_set_id)
AND paa.action_status = 'C'
AND ppa.payroll_id = cp_payroll_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.business_group_id = cp_business_group_id
AND ppa.effective_date BETWEEN cp_start_date
AND cp_end_date
AND ppa.action_type IN ('U','P','B')
AND DECODE(ppa.action_type,
'B', NVL(ppa.future_process_mode, 'Y'),
'N') = 'N'
AND paa.source_action_id IS NULL
AND NOT EXISTS
(SELECT 'x'
FROM pay_action_interlocks pai1,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE pai1.locked_action_id = paa.assignment_action_id
AND paa1.assignment_action_id = pai1.locking_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.action_type ='X'
AND ppa1.report_type = 'IP_PAYROLL_ARCHIVE')
ORDER BY 1,2,3,5,6;
SELECT paa.assignment_id,
paa.tax_unit_id,
ppa.effective_date,
ppa.date_earned,
ppa.action_type,
paa.assignment_action_id,
paa.payroll_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_assignments_f paf,
pay_population_ranges ppr
WHERE ppr.payroll_action_id = cp_payroll_action_id
AND ppr.chunk_number = cp_chunk_number
AND paf.person_id = ppr.person_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paa.assignment_id = paf.assignment_id
AND ppa.consolidation_set_id
= NVL(cp_cons_set_id,ppa.consolidation_set_id)
AND paa.action_status = 'C'
AND ppa.payroll_id = cp_payroll_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.business_group_id = cp_business_group_id
AND ppa.effective_date BETWEEN cp_start_date
AND cp_end_date
AND ppa.action_type IN ('U','P','B')
AND DECODE(ppa.action_type,
'B', NVL(ppa.future_process_mode, 'Y'),
'N') = 'N'
AND paa.source_action_id IS NULL
AND NOT EXISTS
(SELECT 'x'
FROM pay_action_interlocks pai1,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE pai1.locked_action_id = paa.assignment_action_id
AND paa1.assignment_action_id = pai1.locking_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.action_type ='X'
AND ppa1.report_type = 'IP_PAYROLL_ARCHIVE')
ORDER BY 1,2,3,5,6;
SELECT MAX(paa.assignment_action_id)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
WHERE pai.locking_action_Id = cp_prepayment_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.source_action_id IS NULL
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R', 'Q');
SELECT pay_assignment_actions_s.NEXTVAL
INTO ln_xfr_action_id
FROM dual;
UPDATE pay_assignment_actions
SET serial_number = lv_serial_number
WHERE assignment_action_id = ln_xfr_action_id;
SELECT 'IP STATUTORY BALANCES' act_info_category,
NVL(pbtl.reporting_name, pbtl.balance_name) reporting_name,
pbtl.balance_type_id,
pdb.defined_balance_id,
pbd.database_item_suffix,
max(pbad.attribute_name) attribute_name
FROM pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types_tl pbtl
WHERE pbad.attribute_name IN ('ONLINE_SOE_BALANCE_ATTRIBUTE','STATUTORY_BALANCE_ATTRIBUTE')
AND pbad.legislation_code = gv_legislation_code
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pbtl.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbtl.language = USERENV('LANG')
Group by
NVL(pbtl.reporting_name, pbtl.balance_name),
pbtl.balance_type_id,
pdb.defined_balance_id,
pbd.database_item_suffix;
SELECT prt.run_type_id,
prt.shortname
FROM pay_run_types_f prt
WHERE prt.run_method = 'S'
AND prt.legislation_code = gv_legislation_code;
SELECT balance_type_id
FROM pay_balance_types
WHERE legislation_code = gv_legislation_code
AND balance_name = 'Total Payments';
SELECT org_information9
FROM hr_organization_information
WHERE org_information_context = 'Business Group Information'
AND organization_id = cp_business_group;
SELECT rule_mode
FROM pay_legislation_rules
WHERE legislation_code = cp_legislation_code
AND rule_type = cp_rule_type;
SELECT business_group_id INTO ln_business_group_id
FROM pay_payroll_actions WHERE payroll_action_id = p_payroll_action_id;
dbt.delete;
/*Insert this into the plsql table */
--hr_utility.trace('Tax Balance Name : '|| dbt(i).bal_name );
SELECT DISTINCT paf.organization_id,
paf.business_group_id
FROM per_assignments_f paf
WHERE paf.payroll_id = cp_payroll_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT DISTINCT paa.tax_unit_id
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id;
SELECT hou.name, address_line_1, address_line_2,
address_line_3, town_or_city,
region_1, region_2,
region_3, postal_code,
country, telephone_number_1
FROM hr_locations hl,
hr_organization_units hou
WHERE hou.organization_id = cp_organization_id
AND hou.location_id = hl.location_id;
SELECT hri.org_information1,
hri.org_information2, hri.org_information3,
hri.org_information4, hri.org_information5,
hri.org_information6, hri.org_information7
FROM hr_organization_information hri
WHERE hri.organization_id = cp_organization_id
AND hri.org_information_context = cp_org_information_context
AND hri.org_information1 = 'MESG';
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_payroll_action_id
,p_action_context_type => 'PA'
,p_assignment_id => NULL
,p_tax_unit_id => NULL
,p_curr_pymt_eff_date => p_effective_date
,p_tab_rec_data => pay_emp_action_arch.ltr_ppa_arch
);
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 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 pai.ACTION_INFORMATION_CATEGORY,
DECODE(pai.ACTION_INFORMATION1,
'Earnings',
'Gross Earnings',
'Supplemental Earnings',
'Gross Earnings',
'Taxable Benefits',
'Gross Earnings',
'Employer Charges',
'Gross Earnings',
'Direct Payment',
'Gross Earnings',
'Involuntary Deductions',
'Deductions',
'Voluntary Deductions',
'Deductions',
'Pre-Tax Deductions',
'Pre-Tax Deductions',
'Tax Deductions',
'Tax Deductions',
ACTION_INFORMATION1) ELEMENT_TYPE,
sum(nvl(ACTION_INFORMATION8,0)) RUN,
sum(nvl(ACTION_INFORMATION9,0)) YTD
FROM pay_action_information pai
WHERE pai.ACTION_CONTEXT_ID = p_arch_act_id
AND pai.ACTION_INFORMATION_CATEGORY IN ('IP DEDUCTIONS','IP EARNINGS')
GROUP BY
pai.ACTION_INFORMATION_CATEGORY,
DECODE(pai.ACTION_INFORMATION1,
'Earnings',
'Gross Earnings',
'Supplemental Earnings',
'Gross Earnings',
'Taxable Benefits',
'Gross Earnings',
'Employer Charges',
'Gross Earnings',
'Direct Payment',
'Gross Earnings',
'Involuntary Deductions',
'Deductions',
'Voluntary Deductions',
'Deductions',
'Pre-Tax Deductions',
'Pre-Tax Deductions',
'Tax Deductions',
'Tax Deductions',
pai.ACTION_INFORMATION1);
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa,
pay_action_interlocks pai,
pay_payroll_actions ppa
WHERE pai.locking_action_id = cp_prepayment_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.assignment_id = cp_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND NVL(paa.run_type_id,0) NOT IN (gn_sepchk_run_type_id,
gn_np_sepchk_run_type_id)
ORDER BY paa.ACTION_SEQUENCE desc, paa.assignment_action_id;
SELECT ptp.time_period_id,
ppa.date_earned,
ppa.effective_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = cp_run_assignment_action
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
SELECT SUBSTR(serial_number,1,1)
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_last_xfr_act_id;
SELECT nvl(ORG_INFORMATION1,'N') FROM
hr_organization_information
WHERE organization_id = cp_business_group_id
AND ORG_INFORMATION_CONTEXT = cp_legislation_code||'_REPORTING_PREFERENCES';
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE source_action_id = cp_action_id
ORDER BY assignment_action_id;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_curr_pymt_eff_date => p_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
table will finally be called to insert the data from the PL-SQL
table onto the actual table.
*******************************************************************************/
PROCEDURE archive_code(p_xfr_action_id IN NUMBER
,p_effective_date IN DATE)
IS
CURSOR c_xfr_info (cp_assignment_action IN NUMBER) IS
SELECT paa.payroll_action_id,
paa.assignment_action_id,
paa.assignment_id,
paa.tax_unit_id,
paa.serial_number,
paa.chunk_number
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_assignment_action;
SELECT DISTINCT paa.assignment_id
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pai.locking_action_id = cp_prepayment_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R', 'Q', 'B')
AND ((ppa.run_type_id IS NULL AND
paa.source_action_id IS NULL) OR
(ppa.run_type_id IS NOT NULL AND
paa.source_action_id IS NOT NULL))
AND paa.action_status = 'C';
SELECT paa.assignment_action_id, paa.payroll_action_id,
ppa.action_type
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
WHERE pai.locking_action_id = cp_prepayment_action_id
AND pai.locked_action_id = paa.assignment_action_id
AND paa.assignment_id = cp_assignment_id
AND paa.source_action_id IS NULL
AND ppa.payroll_action_id = paa.payroll_action_id
ORDER BY paa.assignment_action_id DESC;
SELECT ppa.effective_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = cp_prepayment_action_id;
SELECT count(*)
FROM pay_action_information
WHERE action_context_id = cp_payroll_action_id
AND action_context_type = 'PA';
SELECT assignment_id
,tax_unit_id
,NVL(source_action_id,-999)
,assignment_action_id
FROM pay_payment_information_v
WHERE assignment_action_id = cp_prepay_action_id
ORDER BY 3,1,2;
SELECT paa.assignment_action_id
,paa.source_action_id
FROM pay_assignment_actions paa
,pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.assignment_id = cp_assignment_id
AND ((paa.tax_unit_id = cp_tax_unit_id)
OR (paa.tax_unit_id IS NULL AND cp_tax_unit_id IS NULL))
AND ((paa.source_action_id IS NOT NULL) OR
(paa.source_action_id IS NULL AND paa.run_type_id IS NULL))
AND NOT EXISTS ( SELECT 1
FROM pay_run_types_f prt
WHERE prt.legislation_code = gv_legislation_code
AND prt.run_type_id = paa.run_type_id
AND prt.run_method IN ( 'C', 'S' ) )
ORDER BY paa.action_sequence DESC;
SELECT ppp.assignment_action_id
FROM pay_assignment_actions paa
,pay_pre_payments ppp
WHERE ( paa.assignment_action_id = cp_asg_act_id OR
paa.source_action_id = cp_asg_act_id )
AND ppp.assignment_action_id = paa.assignment_action_id
AND ppp.source_action_id = cp_source_act_id;
SELECT paa.assignment_action_id
,paa.source_action_id
FROM pay_assignment_actions paa
,pay_action_interlocks pai
WHERE pai.locking_action_id = cp_pp_asg_act_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.assignment_id = cp_assignment_id
AND ((paa.tax_unit_id = cp_tax_unit_id)
OR (paa.tax_unit_id IS NULL AND cp_tax_unit_id IS NULL))
ORDER BY paa.action_sequence DESC;
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa,
pay_action_interlocks pai
WHERE pai.locking_action_id = cp_pp_asg_act_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.assignment_id = cp_assignment_id
AND ((paa.tax_unit_id = cp_tax_unit_id)
OR (paa.tax_unit_id IS NULL AND cp_tax_unit_id IS NULL))
AND NVL(paa.run_type_id,0) NOT IN (gn_sepchk_run_type_id,
gn_np_sepchk_run_type_id)
AND NOT EXISTS ( SELECT 1
FROM pay_run_types_f prt
WHERE prt.legislation_code = cp_legislation_code
AND prt.run_type_id = NVL(paa.run_type_id,0)
AND prt.run_method = 'C' );
SELECT locked_action_id
FROM pay_action_interlocks
WHERE locking_action_id = cp_xfr_action_id;
SELECT pay_assignment_actions_s.nextval
INTO ln_child_xfr_action_id
FROM dual;
update pay_assignment_actions
set serial_number = lv_serial_number
WHERE assignment_action_id = ln_child_xfr_action_id;
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => ln_assignment_id
,p_tax_unit_id => ln_tax_unit_id
,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);