[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
*** pick runs when an assignment update is done.
*** 11 MAY 04 punmehta 1.11 3622295 Replaced Assignmetn_status_type_id check with per_system_Status check
*** 13 MAY 04 avenkatk 1.12 3627293 Modified Balances archival - Archive values only for Master action ID.
*** 07 JUN 04 abhkumar 1.13 3662449 Modfied the archive and assignment code to include assignment statuses SUSPEND and END.
*** 09 AUG 04 abhkumar 1.17 2610141 Legal Employer enhancement Changes.
*** 05 OCT 04 ksingla 1.18 3953702 Modified cursor c_employee_details for Grade display - Archived Grade Name
*** fetched from table per_grades_tl
*** 06 DEC 04 abhkumar 1.19 3953706 Mainline fix for Earnings Reporting enhancement.
*** 06 DEC 04 srrajago 1.20 4045910 Modified the cursor 'c_element_details' to handle the issue raised when user-defined secondary classification
*** is attached to an element (Distinct clause introduced).
*** 13 DEC 04 JLin 1.21 3953615 Added the call pay_au_reconciliation.check_report_parameters to
*** spawn_archive_reports to check the validation for the parameters
*** 29-DEC-04 abhkumar 1.22 4040688 Modified CURSOR csr_assignment_payroll_period so that
*** assignment IS reported FOR the latest payroll AS ON END DATE OF report.
*** Modified archive_code TO call YTD balances ONLY FOR the last RUN IN the period
*** Modified CURSOR c_employee_details TO FETCH the latest Legal Employer OF the assignment
*** 30-DEC-04 abhkumar 1.23 4040688 Modified the cursor csr_get_max_asg_dates and csr_get_max_asg_action to improve performance.
*** Modified the logic of archiving YTD balances. Two new contexts introduced
*** AU_BALANCE_RECON_DETAILS_RUN and AU_BALANCE_RECON_DETAILS_YTD.
*** 06-JAN-05 abhkumar 1.24 4099317 Modified assignment CURSOR - added CHECK OF action_status = 'C'
*** Modified element details cursor - added CHECK OF action_status = 'C' on
*** pay_assignment_actions table
*** 13-JAN-05 avenkatk 1.25 4116833 Set the Report Request number of copies to be read from Archive Request.
*** 21-JAN-05 abhkumar 1.26 4132525 Modified cursor c_employee_details to archive organization name, payroll name and Legal Employer
*** name. Sorting of records in reports to be done on basis of Org. names, Payroll names, Legal Employer names.
*** 25-JAN-05 abhkumar 1.27 4142159 Introduced Parameter P_DELETE_ACTIONS.
*** 08-FEB-05 ksingla 1.28 4161540 Modified cursors csr_assignment_org_period , csr_assignment_legal_period,
*** csr_assignment_payroll_period ,csr_assignment_period ,csr_assignment_default_period.
*** Removed join for per_assignment_status_types.
*** 09-FEB-05 ksingla 1.29 4161540 Modified cursors csr_assignment_org_period , csr_assignment_legal_period,
*** csr_assignment_payroll_period ,csr_assignment_period ,csr_assignment_default_period.
*** Modified the subquery for the check of termination in the same way as in pay_au_payment_summary .
*** 11-FEB-05 abhkumar 1.30 4132149 Modified initialisation_code to initialise the global variables for legislative parameters.
*** 13-APR-2005 abhkumar 1.31 3935471 Modified element_detail cursor to get the tax unit id of master assignment action id.
*** 05-MAY-2005 abhkumar 1.32 3935471 Modified file to put proper comments.
*** 25 May 05 abhkumar 1.33 4688872 Modified assignment action code to fix for cases where employee has a nulled payroll
*** at the end of year
*** 27 Feb 06 ksingla 1.34 5063359 Modified Cursor c_element_details for employer charges
*** 19 Oct 06 ksingla 1.35 5461557 Modified cursor c_element_details to get rate and hours and group by on rate.
*** 29-Oct-06 hnainani 1.36 5603254 Added Function get_element_payment_hours to fetch hours in c_element_details.
*** 16-Nov-06 abhargav 115.40 5603254 Modified cursor c_element_details to remove joins for pay_input_values_f piv2 and pay_run_result_values prrv2.
*** 13-Feb-06 priupadh 115.41 N/A Version for restoring Triple Maintanence between 11i-->R12(Branch) -->R12(MainLine)
*** 02-MAR-07 hnainani 1.42 5599310 Added Function get_element_payment_rate to fetch rate in c_element_details.
-- 13-MAR-07 hnainani 115.43 5599310 Added Debug messages to function get_element_payment_rate
***11-Jun-07 vamittal 115.44 6109668 Modified cursor get_rate_input_value in function get_element_payment_rate
*** to fetch the rate input having UOM as Number from input value
***29-Jun-07 vamittal 115.45 6109668 Modified cursor get_rate_input_value in function get_element_payment_rate
*** to fetch the rate input having UOM as Number or Money or Integer from input value
***02-Aug-07 skshin 115.46 5987877 Added check in Function get_element_payment_hours for multiple Hours Input
***26-Feb-08 vdabgar 115.47 6839263 Modified proc spawn_archive_reports,csr_params and csr_report_params cursors
*** to call the concurrent programs accordingly.
***18-Mar-08 avenkatk 115.48 6839263 Backed out changes from assignment_action_code, initialization_code
***21-Mar-08 avenkatk 115.49 6839263 Added Logic to set the OPP Template options for PDF output
*** ------------------------------------------------------------------------+
*/
g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
p_sql := ' select distinct p.person_id' ||
' from per_people_f p,' ||
' pay_payroll_actions pa' ||
' where pa.payroll_action_id = :payroll_action_id' ||
' and p.business_group_id = pa.business_group_id' ||
' order by p.person_id';
SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
SELECT ppa.date_earned
FROM pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = c_payroll_action_id;
hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
hr_organization_units hou,
per_periods_of_service pps
-- ,per_assignment_status_types past
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paaf.organization_id = hou.organization_id
and hou.business_group_id = c_business_group_id
and hou.organization_id = c_organization_id
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date)
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
hr_organization_units hou,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paaf.organization_id = hou.organization_id
and hou.business_group_id = c_business_group_id
and NVL(pap.current_employee_flag,'N') like c_employee_type
and hou.organization_id = c_organization_id
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
-- ,per_assignment_status_types past
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.tax_unit_id = c_legal_employer
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --bug 4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date)
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and NVL(pap.current_employee_flag,'N') like c_employee_type
and paa.tax_unit_id = c_legal_employer
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
-- per_assignment_status_types past
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
AND paaf.payroll_id = c_payroll_id /*Bug 4040688*/
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date
AND iipaf.payroll_id IS NOT NULL) /*Bug 4688872*/
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and NVL(pap.current_employee_flag,'N') like c_employee_type
and ppa1.payroll_id = c_payroll_id
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
-- ,per_assignment_status_types past
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and paa.assignment_id = c_assignment_id
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date)
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and NVL(pap.current_employee_flag,'N') like c_employee_type
and paa.assignment_id = c_assignment_id
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
-- ,per_assignment_status_types past
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and ppa1.effective_date between c_archive_start_date and c_archive_end_date
and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
From per_assignments_f iipaf
WHERE iipaf.assignment_id = paaf.assignment_id
and iipaf.effective_end_date >= c_archive_start_date
and iipaf.effective_start_date <= c_archive_end_date)
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
select paa.assignment_action_id,
paa.action_sequence,
paaf.assignment_id,
paa.tax_unit_id
from per_people_f pap,
per_assignments_f paaf,
pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_assignment_actions paa,
per_periods_of_service pps
where ppa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = paaf.assignment_id
and pap.person_id between c_start_person and c_end_person
and pap.person_id = paaf.person_id
and pap.person_id = pps.person_id
and pps.period_of_service_id = paaf.period_of_service_id
and ppa1.date_earned between paaf.effective_start_date and paaf.effective_end_date
and ppa1.date_earned between pap.effective_start_date and pap.effective_end_date
and ppa1.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C' /*Bug 4099317*/
and ppa1.business_group_id = ppa.business_group_id
and ppa.business_group_id = c_business_group_id
and ppa1.action_type in ('R','Q','I','B','V')
and NVL(pap.current_employee_flag,'N') like c_employee_type
and ppa1.payroll_action_id = c_pact_id
order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
SELECT ppa.date_earned
FROM pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = c_payroll_action_id;
select pay_assignment_actions_s.nextval
from dual;
hr_utility.set_location('g_parameters.delete_actions..........= '||g_parameters.delete_actions,30);/*Bug# 4142159*/
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
assignment_id
)
values(
pay_action_information_s.nextval,
l_next_assignment_action_id,
'AAP',
null,
null,
csr_rec.tax_unit_id,
'AU_ARCHIVE_ASG_DETAILS',
csr_rec.assignment_action_id,
p_payroll_action_id,
csr_rec.action_sequence,
csr_rec.assignment_id
);
select pap.full_name,
paa.assignment_number,
paa.assignment_id,
to_number(pro.proposed_salary_n) actual_salary,
paa.normal_hours,
pps.actual_termination_date,
pgr.name grade, /* Bug 3953702*/
paa.organization_id,
hou.NAME organization_name, /*Bug 3953706 */
-- paa.payroll_id, /*Bug 4688872*/
-- papf.payroll_name, /*Bug 4132525, Bug 4688872*/
hsc.segment1 tax_unit_id, /*Bug 4040688*/
hou1.NAME Legal_Employer /*Bug 4132525*/
from per_people_f pap,
per_assignments_f paa,
per_grades_tl pgr, /* Bug 3953702*/
per_periods_of_service pps,
per_pay_bases ppb,
per_pay_proposals pro,
per_assignment_status_types past,
hr_soft_coding_keyflex hsc, /*Bug 4040688*/
hr_organization_units hou, /*Bug 3953706 */
hr_organization_units hou1 /*Bug 4132525*/
-- pay_payrolls_f papf /*Bug 4132525, Bug 4688872*/
where pap.person_id = paa.person_id
and paa.assignment_id = c_assignment_id
and paa.business_group_id = c_business_group_id
and paa.grade_id = pgr.grade_id(+) /* Bug 3953702*/
and pgr.language(+) = userenv('LANG')
and paa.pay_basis_id = ppb.pay_basis_id(+)
and paa.assignment_id = pro.assignment_id(+)
AND hou.organization_id = paa.organization_id /*Bug 3953706 */
AND hou1.organization_id = hsc.segment1 /*Bug 4132525*/
-- AND papf.payroll_id = paa.payroll_id /*Bug 4132525, Bug 4688872*/
-- AND c_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date /*Bug 4132525, Bug 4688872*/
and pps.period_of_service_id = paa.period_of_service_id
AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id /*Bug 4040688*/
and paa.assignment_status_type_id = past.assignment_status_type_id
and paa.effective_end_date = ( select max(effective_end_date) /*Bug#3662449 sub query added*/
from per_assignments_f
WHERE assignment_id = c_assignment_id
and effective_end_date >= c_start_date
and effective_start_date <= c_end_date)
and c_end_date between pap.effective_start_date and pap.effective_end_date
and pps.person_id = pap.person_id
and pro.change_date(+) <= c_end_date
and nvl(pro.approved,'Y') = 'Y'
and nvl(pro.change_date,to_date('4712/12/31','YYYY/MM/DD')) = (select nvl(max(pro1.change_date),to_date('4712/12/31','YYYY/MM/DD'))
from per_pay_proposals pro1
where pro1.assignment_id(+) = paa.assignment_id
and pro1.change_date(+) <= c_end_date
and nvl(pro1.approved,'Y')='Y');
SELECT paaf.payroll_id, pay.payroll_name
FROM per_all_assignments_f paaf,
pay_payrolls_f pay
WHERE paaf.assignment_id = c_assignment_id
and paaf.effective_end_date = (select max(effective_end_date)
From per_assignments_f iipaf
WHERE iipaf.assignment_id = c_assignment_id
and iipaf.effective_end_date >= c_start_date
and iipaf.effective_start_date <= c_end_date
AND iipaf.payroll_id IS NOT NULL)
AND pay.payroll_id = paaf.payroll_id
AND paaf.effective_end_date BETWEEN pay.effective_start_date AND pay.effective_end_date;
select hr.meaning fin_year
from per_assignment_extra_info pae,
hr_lookups hr
where pae.aei_information_category = 'HR_PS_ISSUE_DATE_AU'
and pae.information_type = 'HR_PS_ISSUE_DATE_AU'
and pae.assignment_id = c_assignment_id
and pae.aei_information1 = c_fin_date
and pae.aei_information1 = hr.lookup_code
and hr.lookup_type = 'AU_PS_FINANCIAL_YEAR';
select element_name,label classification_name,sum(amount) payment,sum(hours) hours,source_action_id master_action_id, tax_unit_id master_tax_unit_id,rate /*Bug 3935471 ,5461557 */
from
(select distinct -- Bug No: 4045910
nvl(pet.reporting_name, pet.element_name) element_name,
decode(instr(pec.classification_name, 'Earnings'), 0, null,
decode(pec2.classification_name, 'Non Taxable', 'Non Taxable Earnings', 'Taxable Earnings')) ||
decode(instr(pec.classification_name, 'Payments'), 0, null,
decode(instr(pec.classification_name, 'Direct'), 0, 'Taxable Earnings', 'Direct Payments')) ||
decode(instr(pec.classification_name, 'Deductions'), 0, null,
decode(pec.classification_name , 'Termination Deductions' , 'Tax Deductions'
, 'Involuntary Deductions' , 'Post Tax Deductions'
, 'Voluntary Deductions' , 'Post Tax Deductions'
, pec.classification_name )) ||
decode(instr(pec.classification_name, 'Employer Charges'), 0,null,'Employer Charges' ) label,
decode(substr(piv.uom,1,1), 'M', prrv.result_value, null) amount,
pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date) hours, /*Bug 5603254 */
decode(pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date), null,
(prrv.result_value/pay_au_rec_det_archive.get_element_payment_hours(prr.assignment_action_id,pet.element_type_id,prr.run_result_id, ppa.effective_date)),
pay_au_rec_det_archive.get_element_payment_rate(prr.assignment_action_id,pet.element_type_id,prr.run_result_id,ppa.effective_date)) rate, /* 5599310 */
prr.run_result_id,
paa.source_action_id,
paa2.tax_unit_id /*Bug 3935471*/
from pay_element_types_f pet
,pay_input_values_f piv
,pay_element_classifications pec
,pay_assignment_actions paa
,pay_assignment_actions paa2 /*Bug 3935471*/
,pay_payroll_actions ppa
,per_assignments_f paaf
,pay_run_results prr
,pay_run_result_values prrv
,pay_element_classifications pec2
,pay_sub_classification_rules_f pscr
where pet.element_type_id = piv.element_type_id
and pet.element_type_id = prr.element_type_id
and prr.assignment_action_id = paa.assignment_action_id
and paaf.assignment_id = paa.assignment_id
AND paa2.assignment_action_id = nvl(paa.source_action_id, paa.assignment_action_id) /*Bug 3935471*/
and paaf.business_group_id = c_business_group_id/*Bug 5370001 */
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = piv.input_value_id
and pet.classification_id = pec.classification_id
and pec.legislation_code = 'AU'
and paa.assignment_action_id = c_assignment_action_id/*Bug 5370001 */
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C' /*Bug 4099317*/
and piv.name = 'Pay Value'
and (instr(pec.classification_name, 'Earnings') > 0
or instr(pec.classification_name, 'Payments') > 0
or instr(pec.classification_name, 'Deductions') > 0
or instr(pec.classification_name, 'Employer Charges' ) > 0 )
and pet.element_type_id = pscr.element_type_id (+)
and ppa.effective_date between nvl(pscr.effective_start_date, ppa.effective_date)
and nvl(pscr.effective_end_date, ppa.effective_date)
and pscr.classification_id = pec2.classification_id(+)
and ppa.date_earned between pet.effective_start_date and pet.effective_end_date
and ppa.date_earned between paaf.effective_start_date and paaf.effective_end_date
and prr.status in ('P','PA')
)
group by element_name,label,source_action_id, tax_unit_id, rate ; /*Bug 3935471*/
select pai.action_information1, pai.tax_unit_id, pai.assignment_id,pai.action_information3
from pay_action_information pai
where action_information_category = 'AU_ARCHIVE_ASG_DETAILS'
and pai.action_context_id = c_arc_ass_act_id;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
,max(paa.action_sequence)
from pay_assignment_actions paa
, pay_payroll_actions ppa
, per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_start_date and c_end_date
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status='C'
AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
,max(paa.action_sequence)
from pay_assignment_actions paa
, pay_payroll_actions ppa
, per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_action_id = c_payroll_action_id
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status='C'
AND paa.tax_unit_id = nvl(c_tax_unit_id, paa.tax_unit_id);
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
null,
l_assignment_id,
'AU_BALANCE_RECON_DETAILS_YTD',
l_YTD_TAXABLE_EARNINGS,
l_YTD_NON_TAXABLE_EARNINGS,
l_YTD_DEDUCTIONS,
l_YTD_TAX,
l_YTD_NET_PAYMENT,
l_YTD_EMPLOYER_CHARGES,
l_YTD_GROSS_EARNINGS,
l_YTD_PRE_TAX_DEDUCTIONS,
l_YTD_DIRECT_PAYMENTS,
l_max_action_sequence);
insert into pay_action_information(
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
assignment_id)
values(
pay_action_information_s.nextval,
g_arc_payroll_action_id,
'PA',
p_effective_date,
null,
l_tax_unit_id,
'AU_EMPLOYEE_RECON_DETAILS',
csr_rec.full_name,
csr_rec.assignment_number,
csr_rec.actual_salary,
csr_rec.grade, /* Bug 3953702*/
csr_rec.normal_hours,
csr_rec.actual_termination_date,
l_fin_year,
csr_rec.organization_name,/*Bug 4132525*/
csr_rec.Legal_Employer, /*Bug 4040688, Bug 4132525*/
l_payroll_name, /*Bug 4132525, Bug 4688872*/
csr_rec.organization_name, /*Bug 3953706*/
l_assignment_id);
l_context_lst.delete;
l_output_tab.delete;
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
assignment_id)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
l_tax_unit_id,
'AU_ELEMENT_RECON_DETAILS',
csr_ele_det.element_name,
csr_ele_det.classification_name,
null,
csr_ele_det.hours,
csr_ele_det.rate, /* 5599310 */
csr_ele_det.payment,
l_assignment_id);
/*Bug 4040688 - Modified insert statement to store run balance details.*/
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAP',
p_effective_date,
null,
l_tax_unit_id,
l_assignment_id,
'AU_BALANCE_RECON_DETAILS_RUN',
l_taxable_earnings,
l_NON_TAXABLE_EARNINGS,
l_DEDUCTIONS,
l_TAX,
l_NET_PAYMENT,
l_EMPLOYER_CHARGES,
l_GROSS_EARNINGS,
l_PRE_TAX_DEDUCTIONS,
l_DIRECT_PAYMENTS,
l_action_sequence);
SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
to_date(pay_core_utils.get_parameter('SDATE',legislative_parameters),'YYYY/MM/DD') start_date,
to_date(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') end_date,
pay_core_utils.get_parameter('PACTID',legislative_parameters) pact_id,
pay_core_utils.get_parameter('LE',legislative_parameters) legal_employer,
pay_core_utils.get_parameter('ASG',legislative_parameters) assignment_id,
pay_core_utils.get_parameter('SO1',legislative_parameters) sort_order_1,
pay_core_utils.get_parameter('SO2',legislative_parameters) sort_order_2,
pay_core_utils.get_parameter('SO3',legislative_parameters) sort_order_3,
pay_core_utils.get_parameter('SO4',legislative_parameters) sort_order_4,
to_date(pay_core_utils.get_parameter('PEDATE',legislative_parameters),'YYYY/MM/DD') period_end_date,
pay_core_utils.get_parameter('YTD_TOT',legislative_parameters) ytd_totals,
pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters) employee_type,
pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions, /*Bug# 4142159*/
pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type /* Bug# 6839263 */
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_payroll_action_id;
SELECT printer,
print_style,
decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
,number_of_copies /* Bug 4116833 */
FROM pay_payroll_actions pact,
fnd_concurrent_requests fcr
WHERE fcr.request_id = pact.request_id
AND pact.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('in delete action'||l_parameters.delete_actions,19); /*Bug# 4142159*/
'P_DELETE_ACTIONS='||l_parameters.delete_actions, /*Bug# 4142159*/
'BLANKPAGES=NO',
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
);
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 pivf.input_value_id
FROM pay_input_values_f pivf
WHERE pivf.element_type_id = c_element_type_id
AND upper(pivf.name) like 'RATE%'
AND pivf.uom in ('N','M','I') /*bug 6109668 */
AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date;
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;