[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
13-Jun-2012 krreddy 115.8 13918120 Modified to update Starter Declaration field to address
new changes in the requirement.
14-Jun-2012 krreddy 115.9 13918120 Modified to implement appropriate Effective Date check.
15-Jun-2012 rajganga 115.10 14184141 Modified to include rollback when exp occurs in econ val.
15-Jun-2012 rajganga 115.10 14184141 Modified rollback to restored procedure.
15-Jun-2012 krreddy 115.11 13918120 Modified to update Pensioner values.
21-Jun-2012 rajganga 115.12 13918120 Modified Econ reporting and First Fps Balance calc.
21-JUN-2012 ssarap 115.13 13918120 Incorporated the hours worked changes of picking from various other sources.
25-Jun-2012 rajganga 115.14 14231920 Modified the submit_request date argument.
27-Jun-2012 rajganga 115.15 14231920 Modified to get Person Middle name for second forename.
06-Jul-2012 ssarap Removed the negative value changes.
06-Jul-2012 ssarap Fixed the hours worked issue.
07-Jul-2012 rajganga 115.16 14231920 Fixed the empty asg no and inappropriate error msg.
11-Jul-2012 rajganga 115.17 14231920 XML Generation Architecture changed to XDODTEXE and -ve payment changes.
13-Jul-2012 ssarap 115.18 Summed up SPP Adoption Total, SPP Adoption Total
and ASPP Adoption Total ,ASPP Adoption Total.
18-Jul-2012 ssarap Fixing balance related bug.
18-Jul-2012 rajganga 115.19 14309887 Fixed incorrect validation errors and payroll check.
19-Jul-2012 rajganga 115.20 14347577 Fixed Cap and NHS issues.
20-Jul-2012 rajganga 115.21 14347577 Increased Error msg length in the EDI output.
20-Jul-2012 ssarap 115.21 14347577 Code changes for hours worked, NI balances, Tax code issues.
30-Jul-2012 rajganga 115.22 14086377 Fixed issues of number conversion , no data found.
30-Jul-2012 rajganga 115.22 14086377 Output cp will not be spawned if emps count is zero and header error occurs.
31-Jul-2012 krreddy 115.23 14393796 Implemented the below for PAYE Aggregation and NI Only Aggregation:
Categorized the Archive Code flow into Non-Aggregated, PAYE Aggregated and NI Only Aggregated sections.
Implemented the Logic to process PAYE Aggregated employees.
Modified the First FPS logic so that it processes aggregated employees as well.
Modified the Output logic to capture and display warning messages.
ssarap Modified to implement NI Only Aggregation.
PAYE Aggregation - Balances fetching and reporting in output.
5-Aug-2012 krreddy 115.24 14393796 Implemented the below modifications for PAYE Aggregation:
Created new validation procedures for Aggregated Scenarios.
Created new procedure for Det1 context for Aggregated Scenarios.
Modified the warning messages logic.
Resolved the XML program spawning issue.
Implemented the logic for checking Prepayment and error out the employee.
5-Aug-2012 krreddy 115.25 14393796 Arcsed in the correct file.
5-Aug-2012 krreddy 115.26 14393796 Removed the additional cursor opening which is incorrect.
7-Aug-2012 ssarap 115.37 14393796 Made changes for validation related balances, corrected aggregated balances, merged
with Raj changes.
8-Aug-2012 ssarap 115.38 14461582 Fix for the bug 14461582. Corrected the NI balance related index.
9-Aug-2012 ssarap 115.39 14393796 Implemented the code review comments. Added distinct keyword in select into statement
in archinit procedure.
10-Aug-2012 ssarap 115.43 Implemented the bugs reported.
10-Aug-2012 ssarap 115.46 Implement Passport number changes similar to hours worked.
13-Aug-2012 ssarap 115.43 Implemented the review comment ID#32 reported.
17-Aug-2012 ssarap 115.49 Implemented the review comment ID#34,35 reported.
22-Aug-2012 ssarap 115.50 14461582 Removed the usage of g_effective_date in write_body.
Added the counter check to avoid the PLSQL table no data found issue.
23-Aug-2012 krreddy 115.51 14531479 Fixed Cap Balances issue by making use of max child action id.
26-Aug-2012 krreddy 115.52 14530108 Modified the processing logic to fix incorrect output issue.
27-Aug-2012 rajganga 115.53 14503452 Modified csr_ni_info , csr_asg_details and oneoff payment issues.
28-Aug-2012 ssarap 115.54 14549289 Fixed NI mismatch issues.
31-Aug-2012 rajganga 115.55 14549289 Fixed Payment after leaving indicator, added errors to the LOG file
Removed current emp flag check.
03-Sep-2012 rajganga 115.56 14577359 Fixed Termination logic and output file.
05-Sep-2012 ssarap 115.57 14549289 Fixed the NI Mismatch issue. Removed the swapping logic for archived values of NI context.
07-Sep-2012 rajganga 115.58 14576477 Removed unused pay_payrolls_f and added not null check for balances.
13-Sep-2012 ssarap 115.41 14365199 Fix for Interlocks, fix for balance doubling, fix for Retry.
14-Sep-2012 ssarap 115.42 14365199 Modified to raise an error when First FPS is already ran.
17-Sep-2012 rajganga 115.43 14365199 Modified to fix no data found issue.
17-Sep-2012 rajganga 115.44 14365199 Modified to fix no data found issue, added one more condition check
26-Sep-2012 krreddy 115.46 14313532 Leafproged and fixed the Tax Code Error.
27-Sep-2012 rajganga 115.47 14650604 Spawning Reconciliation Process.
03-Oct-2012 rajganga 115.48 14650604 New Starter RTI Sent flag check for Starter Declaration.
05-Oct-2012 rajganga 115.49 14683498 Fixed Econ issue.
05-Oct-2012 rajganga 115.50 14711819 Added the legislation code condition in the cursors get_defined_balance_id,
get_aggr_defined_balance_id, get_defined_balance_id_ni,get_aggr_defined_balance_id_ni.
08-Oct-2012 krreddy 115.51 14704814 Modified to error the program even if one asg error exists.
08-Oct-2012 rajganga 115.52 14704814 Modified address archiving ,End date and First FPS RTI sent logics.
09-Oct-2012 rajganga 115.53 14733866 Fixed the address issues.
10-Oct-2012 krredy 115.54 14709614 Fixed the start date issue for terminated asg.
10-Oct-2012 krredy 115.55 14709614 Further refined the fix for start date issue.
10-Oct-2012 krredy 115.56 14709614 Added few more conditions to the fix for start date issue.
11-Oct-2012 krredy 115.57 14737182 Removed a parameter while Spawning FPS Recon Report.
12-Oct-2012 rajganga 115.58 14737182 Fix re archiving during retry.
22-Oct-2012 ssarap On top of latest issues merged action creation code.
25-Oct-2012 krreddy 115.70 Latest code modified to archive two more columns for accomodating
Aggregation - Action Creation Logic.
25-Oct-2012 krreddy 115.71 Updated to accomodate NI Only Aggr changes in output logic.
25-Oct-2012 rajganga 115.72 14737182 Fixed No data and buffer small issue.
26-Oct-2012 ssarap 115.73 14737182 NI only aggregation and fixed the PAYE agg issues.
26-Oct-2012 ssarap 115.75 14737182 Fixed QA logged issues for PAYE aggregation.
26-Oct-2012 rajganga 115.76 14737182 Fixed det1 and det2 issue.
31-Oct-2012 ssarap 115.77 14827248 NI aggregation issue and interlock changes.
31-Oct-2012 krreddy 115.81 14827248 Fixed PAYE Aggregation issues on NI Records.
2-NOV -2012 ssarap 115.81 14827248 Fixed NI only aggregation issues from NHS.
9-Nov-2012 krreddy 115.87 14827248 This version includes the below changes on top of Action Creation Logic(115.81)
- PAYE Aggregation Errors occured as a result of new actions created.
- NI Only Aggregation Errors occured as a result of new actions created.
- BACS Error - Occuring for PAYE Aggregation scenarios.
9-Nov-2012 ssarap 115.88 14827248 NI Only aggregation case customer case fix.
12-Nov-2012 ssarap 115.88 14827248 NI Only aggregation case customer case fix.
16-Nov-2012 ssarap 115.91 14827248 NI Only aggregation case changes and non aggregation NI balance summed up change.
21-Nov-2012 rajganga 115.92 14827248 Added Reversal and Balance Adjustments.
22-Nov-2012 rajganga 115.93 14827248 Fixed Bacs no data found issue.
22-Nov-2012 krreddy 115.94 14827248 Modified to resolve the issue of incorrect actions getting created.
23-Nov-2012 krreddy 115.95 14827248 Modified to resolve further issues related to incorrect actions getting created.
28-Nov-2012 rajganga 115.96 15903486 Modified to fetch all details for the RTI reporting asg where no payroll exist.
30-Nov-2012 rajganga 115.97 15903486 Modified to fetch RTI Payroll ID for PAYE Aggregation.
03-Dec-2012 rajganga 115.99 15903486 Modified to fix Date of Leaving issue and Director check.
04-Dec-2012 krreddy 115.100 Leapfroged and modified on top of version 98 for Date of Leaving issue.
12-Dec-2012 krreddy 115.101 Leapfroged and modified on top of version 99 to implement
- additional logic for processing PAYE Aggr in First FPS.
14-Dec-2012 rajganga 115.102 15951186 Modified to show error msg if no NI RTI reporting asg is found.
14-Dec-2012 krreddy 115.103 15951186 Modified to use correct asg_id in fetch_fps_aggr_asg_det1.
14-Dec-2012 rajganga 115.104 15951186 Modified to add additional logic for processing NI Only Aggr in First FPS.
24-DEC-2012 achandwa 115.105 16034919 Modified the value of annual pension value to be reported in pens and trimmerd the value
while fetching
03-JAN-2013 ssarap 115.106 Hours worked changes for PAYE aggregation case.
03-JAN-2013 ssarap 115.107 Corrected the typo for category J.
10-JAN-2013 rajganga 115.108 Corrected duplicate actions for same asg.
10-JAN-2013 rajganga 115.108 Removed continue statement.
31-JAN-2013 krreddy 115.110 16164625 Modified to check if really payment made in current tax year.
07-FEB-2013 ssarap 115.111 16276411 Fix for Mckesson reported issues.
08-FEB-2013 ssarap 115.113 16276411 Assignment number data type is modified.
09-FEB-2013 ssarap 115.114 16276411 NHS issues for validation.
12-FEB-2013 sampmand 115.116 16276411 Added logic to delete from pay_gb_fps_details in case of retry.
12-FEB-2013 ssarap 115.117 16310246 Changed the Action informatin column. For irre payemnt it is 3 and for one-off pay it is 4.
14-FEB-2013 sampmand 115.118 16276411 Modified Tax Code Fetch logic to fix Mckesson issue.
Included 'TERM_ASSIGN' in get_eff_start_date cursor
to set first fps starter date correctly.
21-FEB-2013 sampmand 115.119 16328672 Modified starter flag checks in fetch_fps_asg_det1.
21-FEB-2013 ssarap 115.120 16345058 Initialiazed variable l_flag1 to 'Y' each time at
the start of the loop in action creation code.
04-Mar-2013 sampmand 115.121 16386622 Modified Retry Cursor,Ni reporting cursor.
11-Mar-2013 krreddy 115.122 16396194 Modified the below two procedures for correcting the balance values if they contain
Process Separate elements in the run:
fetch_fps_asg_det2 and fetch_fps_agg_asg_det2.
13-Mar-2013 ssarap 115.123 16414987 Hours worked fix for multiple assignments.
15-Mar-2013 ssarap 115.124 16469649 Changed the global variable usage in action creation.
15-Mar-2013 rajganga 115.125 16386622 Modified for Retry.
18-Mar-2013 sampmand 115.127 Modified Partner sur-name fetching logic for SR 3-6903967467.
20-Mar-2013 rajganga 115.129 16501443 Added sysdate+30 condition to Termination date.
20-Mar-2013 sampmand 115.130 16505555 Modified decode logic of tax_basis in procedure fetch_tax_code_basis.
22-Mar-2013 krreddy 115.131 16409794 Resolved the issue of incorrect picking of actions when no payment exists.
25-Mar-2013 krreddy 115.132 16542698 Resolved the NI Only and PAYE Aggr issues with No Payment Exist scenarios.
26-Mar-2013 krreddy 115.133 16459087 Modified two cursor queries in internal_action_creation to improve performance.
16503199 Updated the Starter Declaration validation logic.
26-Mar-2013 ssarap 115.128 16306737 Added a third party prepayment check for pay_pre_payments tables. Removed
the balances Pre Tax Deductions and NIable Deductions Free of Tax from 58B.
29-Mar-2013 ssarap 115.35 16555308 Changes to 58B calculation. Used the updated procedure fetch_HASH_FPS_PER.
Fetched the correct NI Employee values .
============================================================================*/
--
--
g_package CONSTANT VARCHAR2(20) := 'pay_gb_rti_fps.';
SELECT upper(SUBSTR(trim(addr.address_line1),1,35)) addr1,
upper(SUBSTR(trim(addr.address_line2),1,35)) addr2,
upper(SUBSTR(trim(addr.address_line3),1,35)) addr3,
SUBSTR(addr.postal_code,1,10) post_code,
upper(SUBSTR(trim(addr.town_or_city),1,35)) addr4,
upper(SUBSTR(trim(addr.country),1,35)) country
FROM per_addresses addr
WHERE addr.person_id = p_person_id
AND ( addr.primary_flag = 'Y'
OR addr.primary_flag IS NULL)
AND p_effective_date
BETWEEN NVL(addr.date_from,fnd_date.canonical_to_date('0001/01/01 00:00:00'))
AND NVL(addr.date_to, fnd_date.canonical_to_date('4712/12/31 00:00:00'));
select AEI_INFORMATION2 from per_assignment_extra_info
where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
select FREQUENCY , NORMAL_HOURS from per_all_assignments_f where assignment_id = c_asg_id and
l_effective_date between effective_start_date and effective_end_date;
select max(effective_start_date) from per_all_assignments_f
where assignment_id = c_asg_id
and assignment_type = 'E'
and effective_start_date <= g_effective_date;
SELECT trim(nvl(pap.per_information10,'N')) per_agg_flag
FROM per_all_people_f pap,
per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND pap.person_id = asg.person_id
AND asg.business_group_id = g_business_group_id
AND l_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND l_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
select paaf.assignment_id
from per_all_assignments_f paaf,
pay_payrolls_f pay,
hr_soft_coding_keyflex sck
where paaf.person_id = (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =p_assignment_id)
and pay.payroll_id = paaf.payroll_id
and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
and sck.SEGMENT1 = g_tax_ref
and paaf.EFFECTIVE_START_DATE =
( select max(paaf1.effective_start_date)
from per_all_assignments_f paaf1
where paaf1.assignment_id = paaf.assignment_id
and paaf1.assignment_type = 'E'
and paaf1.effective_start_date <= l_effective_date
)
and l_effective_date between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE;
l_sqlstr := 'select ' || l_column_name ||
' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
l_sqlstr := 'select ' || l_column_name ||
' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
SELECT definition
INTO l_package
FROM ff_functions
WHERE name = l_function_name
and business_group_id = g_business_group_id;
'SELECT '
|| l_package
|| ' (:p_assignment_id,:p_eff_date) '
|| 'FROM DUAL';
'SELECT '
|| l_package
|| ' (:p_assignment_id,:p_eff_date) '
|| 'FROM DUAL';
select AEI_INFORMATION1 from per_assignment_extra_info
where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
select max(effective_start_date) from per_all_assignments_f
where assignment_id = p_assignment_id
and assignment_type = 'E'
and effective_start_date <= g_effective_date;
l_sqlstr := 'select ' || l_column_name ||
' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
l_sqlstr := 'select ' || l_column_name ||
' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
SELECT definition
INTO l_package
FROM ff_functions
WHERE name = l_function_name
and business_group_id = g_business_group_id;
'SELECT '
|| l_package
|| ' (:p_assignment_id,:p_eff_date) '
|| 'FROM DUAL';
select AEI_INFORMATION3 from per_assignment_extra_info
where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
select max(effective_start_date) from per_all_assignments_f
where assignment_id = p_assignment_id
and assignment_type = 'E'
and effective_start_date <= g_effective_date;
l_sqlstr := 'select ' || l_column_name ||
' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
l_sqlstr := 'select ' || l_column_name ||
' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
SELECT definition
INTO l_package
FROM ff_functions
WHERE name = l_function_name
and business_group_id = g_business_group_id;
'SELECT '
|| l_package
|| ' (:p_assignment_id,:p_eff_date) '
|| 'FROM DUAL';
select AEI_INFORMATION4 from per_assignment_extra_info
where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
select max(effective_start_date) from per_all_assignments_f
where assignment_id = p_assignment_id
and assignment_type = 'E'
and effective_start_date <= g_effective_date;
l_sqlstr := 'select ' || l_column_name ||
' from per_assignment_extra_info where assignment_id = :assignment_id and AEI_INFORMATION_CATEGORY = :l_context_name';
l_sqlstr := 'select ' || l_column_name ||
' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
SELECT definition
INTO l_package
FROM ff_functions
WHERE name = l_function_name
and business_group_id = g_business_group_id;
'SELECT '
|| l_package
|| ' (:p_assignment_id,:p_eff_date) '
|| 'FROM DUAL';
select hdei.document_number from hr_document_types hdt,hr_document_extra_info hdei
where hdt.category_code = 'PPT_INFO'
and hdt.document_type_id = hdei.document_type_id
and hdei.person_id = p_person_id;
SELECT l_column_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND ATTRIBUTE_CATEGORY = l_context_name
ORDER BY effective_start_date,
effective_end_date;
SELECT l_column_name
FROM per_people_extra_info
WHERE person_id = p_person_id
AND PEI_INFORMATION_CATEGORY = l_context_name;
select max(effective_start_date) from per_all_people_f
where person_id = p_person_id
and effective_start_date <= g_effective_date;
sqlstr := 'select ' || l_column_name ||
' from per_people_extra_info where person_id = :p_person_id and PEI_INFORMATION_CATEGORY = :l_context_name';
sqlstr := 'select ' || l_column_name ||
' from per_all_people_f where person_id = :p_person_id and ATTRIBUTE_CATEGORY = :l_context_name' ||
' and :l_effective_date between effective_start_date and effective_end_date' ;
SELECT definition
INTO l_package
FROM ff_functions
WHERE name = l_function_name
and business_group_id = g_business_group_id;
'SELECT '
|| l_package
|| ' (:p_person_id,:p_eff_date) '
|| 'FROM DUAL';
SELECT rownum Number_of_employee,
pap.person_id p_person_id,
paa.assignment_id,
SUBSTR(trim(pap.last_name), 1,35) last_name,
SUBSTR(trim(pap.first_name), 1,least(DECODE(instr(trim(pap.first_name),' '),0,35,instr(trim(pap.first_name),' ')),35)) first_name,
SUBSTR(trim(pap.middle_names), 1,least(decode(instr(trim(pap.middle_names),' '),0,35,instr(trim(pap.middle_names),' ')),35)) middle_name,
pap.title title,
SUBSTR(pap.national_identifier,1,9) national_identifier,
pap.date_of_birth date_of_birth,
SUBSTR(pap.sex,1,1) sex ,
DECODE(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
DECODE(pap.per_information9,'Y','Y',NULL) multiple_asg_flag
FROM pay_assignment_actions act,
per_all_assignments_f paa,
per_all_people_f pap
WHERE act.assignment_action_id = p_assactid
AND act.assignment_id = paa.assignment_id
AND paa.person_id = pap.person_id
AND p_end_date between pap.effective_start_date and pap.effective_end_date
AND paa.effective_start_date =
(
SELECT MAX(paa2.effective_start_date)
FROM per_all_assignments_f paa2
WHERE paa2.assignment_id = paa.assignment_id
AND paa2.assignment_type = 'E'
AND paa2.effective_start_date <= p_end_date
);
SELECT NVL(partner_name,''),
partner_ni_number
FROM ssp_medicals
WHERE maternity_id IN
(SELECT MAX(maternity_id)
FROM per_absence_attendances
WHERE person_id = c_person_id
AND ABSENCE_ATTENDANCE_TYPE_ID IN
(SELECT ABSENCE_ATTENDANCE_TYPE_ID
FROM per_absence_attendance_types
WHERE ABSENCE_CATEGORY IN ('GB_ADDL_PAT_ADOPT', 'GB_ADDL_PAT_BIRTH')
)
)
AND EVIDENCE_STATUS = 'CURRENT';
SELECT AEI_INFORMATION1 periods_worked,
AEI_INFORMATION3 irr_payment ,
AEI_INFORMATION4 "One-Off Payment",
ASSIGNMENT_EXTRA_INFO_ID,
object_version_number
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
AND assignment_id = p_person_rec.assignment_id;
SELECT assignment_extra_info_id,
aei_information1 starter_decl,
aei_information2 prev_emp_paye_ref,
aei_information3 prev_tax_code,
aei_information4 date_left_prev_employer,
aei_information5 prev_tax_basis,
aei_information6 last_payment_period_type,
aei_information7 last_payment_period,
aei_information8 starter_flag,
aei_information10 continue_with_student_loan,
aei_information8 not_paid_between,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT assignment_extra_info_id,
aei_information9 pensioner_flag,
aei_information12 date_pension_started,
trim(aei_information13) annual_pension,
aei_information14 recently_bereaved,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT assignment_extra_info_id,
aei_information15 expat_statement,
aei_information16 date_emp_start_uk,
aei_information17 eea_cw_citizen,
aei_information18 epm6_scheme,
aei_information19 expat_flag,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT paaf.assignment_number assignment_number,
paaf_old.assignment_number old_assignment_number
FROM per_all_assignments_f paaf,
per_all_assignments_f paaf_old
WHERE paaf.assignment_id = p_person_rec.assignment_id
AND ((paaf_old.assignment_id = paaf.assignment_id
AND paaf_old.effective_start_date < paaf.effective_start_date
AND paaf.effective_start_date > g_start_year
AND paaf_old.effective_end_date > g_start_year)
OR (paaf.assignment_number = paaf_old.assignment_number));
SELECT ptp.regular_payment_date,
ptp.period_num,
ptp.period_type
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE assignment_action_id = p_last_asg_action_id
AND ppa.time_period_id = ptp.time_period_id
AND ppa.payroll_id = ptp.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT ptpt.number_per_fiscal_year
FROM per_time_period_types ptpt
WHERE p_period_type = ptpt.period_type;
SELECT peevf.screen_entry_value screen_value
FROM
pay_element_entries_f peef,
pay_element_entry_values_f peevf
WHERE peef.assignment_id = c_asg_id
AND peef.element_entry_id = peevf.element_entry_id
AND peevf.input_value_id = g_ni_pt_ivid
AND peef.element_type_id = g_ni_pt_eid
AND c_asg_eff_start_date between peef.effective_start_date and peef.effective_end_date
AND c_asg_eff_start_date between peevf.effective_start_date and peevf.effective_end_date;
SELECT MAX(DECODE(name,'Periods',result_value,NULL))
FROM pay_assignment_actions paa ,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f petf ,
pay_input_values_f pivf
WHERE paa.source_action_id = p_last_asg_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id
AND petf.element_name = 'NI DETAILS'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND p_start_date between petf.effective_start_date and petf.effective_end_date
AND p_start_date between pivf.effective_start_date and pivf.effective_end_date;
SELECT MAX(DECODE(name,'Periods',result_value,NULL))
FROM pay_assignment_actions paa ,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f petf ,
pay_input_values_f pivf
WHERE paa.source_action_id = p_last_asg_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id
AND petf.element_name = 'NI'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND p_start_date between petf.effective_start_date and petf.effective_end_date
AND p_start_date between pivf.effective_start_date and pivf.effective_end_date;
SELECT trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
trim(NVL(pap.per_information10,'N')) per_agg_flag,
asg.assignment_number assignment_number
FROM per_all_people_f pap,
per_all_assignments_f asg,
pay_assignment_actions paa
WHERE paa.assignment_action_id = c_asg_act_id
AND paa.assignment_id = asg.assignment_id
AND pap.person_id = asg.person_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND pap.per_information_category = 'GB';
(SELECT MAX(EFFECTIVE_START_DATE)
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = asg.assignment_id
);*/
SELECT
/*+ USE_NL(paa, pact, ptp) */
MIN(ptp.start_date) payroll_period_start_date,
MAX(ptp.end_date) payroll_period_end_date
FROM pay_assignment_actions paa,
pay_payroll_actions pact,
per_time_periods ptp,
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
WHERE paa.assignment_id = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year AND p_end_year
AND ppa1.payroll_action_id = p_pre_pay_id
AND ppa1.action_type IN ('P','U')
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = pai.locked_action_id;
select serv.actual_termination_date actual_termination_date
from
per_all_assignments_f asg,
per_periods_of_service serv
where asg.assignment_id = p_person_rec.assignment_id
and asg.period_of_service_id = serv.period_of_service_id
and p_end_date between asg.effective_start_date and asg.effective_end_date
and actual_termination_date <= g_effective_date
and actual_termination_date <= sysdate + 30 ;
SELECT min(paaf.effective_start_date) min_active_start_date
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
WHERE paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = pay.payroll_id
AND pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
AND upper(g_tax_ref) = upper(sck.segment1); --Modified for the bug 16503199
select ppa_prev.payroll_action_id
from pay_payroll_actions ppa_prev,
pay_payroll_actions ppa_current
where ppa_prev.action_type IN ('P','U')
and ppa_prev.payroll_id = ppa_current.payroll_id
and ppa_prev.payroll_action_id < ppa_current.payroll_action_id
and ppa_current.payroll_action_id = g_pre_pact_id
and ppa_prev.payroll_action_id =
(select max(ppa_max.payroll_action_id)
from pay_payroll_actions ppa_max,
pay_assignment_actions paa_max
where ppa_max.payroll_id = ppa_current.payroll_id
and ppa_max.payroll_action_id < ppa_current.payroll_action_id
and ppa_max.action_type IN ('P','U')
and ppa_max.payroll_action_id = paa_max.payroll_action_id
and paa_max.assignment_id = c_asg_id );
select paa_fps.assignment_action_id
from pay_assignment_actions paa_prepay,
pay_payroll_actions ppa_prepay,
pay_assignment_actions paa_fps,
pay_payroll_actions ppa_fps,
pay_action_interlocks pai
where ppa_prepay.payroll_action_id = c_last_pre_pay_id
and ppa_prepay.payroll_action_id = paa_prepay.payroll_action_id
and paa_prepay.assignment_action_id = pai.locked_action_id
and paa_fps.assignment_action_id = pai.locking_action_id
and paa_fps.payroll_action_id = ppa_fps.payroll_action_id
and ppa_fps.action_type = 'X'
and paa_fps.assignment_id = c_asg_id;
select ACTION_INFORMATION13
from pay_action_information
where action_context_id = c_last_fps_asg_act_id
and action_information_category = 'GB_RTI_FPS_ASG_DET1'
and assignment_id = c_asg_id;
select fnd_date.canonical_to_date(max(decode(pivf.name,'Start Date',peevf.screen_entry_value,null))) start_date,
fnd_date.canonical_to_date(max(decode(pivf.name,'End Date',peevf.screen_entry_value,null))) end_date
from pay_element_entries_f peef,
pay_element_entry_values_f peevf ,
pay_input_values_f pivf
where
peef.assignment_id = p_person_rec.assignment_id
and peef.ELEMENT_TYPE_ID = g_adv_period_id
and peef.element_entry_id = peevf.element_entry_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = peef.ELEMENT_TYPE_ID
and g_effective_date between peevf.effective_start_date and peevf.effective_end_date
and g_effective_date between pivf.effective_start_date and pivf.effective_end_date
and g_effective_date between peef.effective_start_date and peef.effective_end_date;
select PER_INFORMATION2 from per_all_people_f papf
where person_id = p_person_rec.person_id
and effective_start_date =
(select max(effective_start_date)
from per_all_people_f where person_id = papf.person_id and effective_start_date <= g_effective_date);
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_starter_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information8 => 'Y');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_pensioner_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information9 => 'Y');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_expat_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information19 => 'Y');
select
pay_advance_pay_pkg.advanced_periods(
p_person_rec.assignment_id,
l_start_date,
l_end_date
) into l_periods_covered from dual;
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn_extra_info,
p_assignment_extra_info_id => l_asg_info_id,
p_aei_information_category => 'GB_PAY_RTI',
p_aei_information1 => 1);
SELECT AEI_INFORMATION1 periods_worked,
AEI_INFORMATION3 irr_payment ,
AEI_INFORMATION4 "One-Off Payment",
ASSIGNMENT_EXTRA_INFO_ID,
object_version_number
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
AND assignment_id = p_person_rec.assignment_id;
SELECT assignment_extra_info_id,
aei_information1 starter_decl,
aei_information2 prev_emp_paye_ref,
aei_information3 prev_tax_code,
aei_information4 date_left_prev_employer,
aei_information5 prev_tax_basis,
aei_information6 last_payment_period_type,
aei_information7 last_payment_period,
aei_information8 starter_flag,
aei_information10 continue_with_student_loan,
aei_information8 not_paid_between,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT assignment_extra_info_id,
aei_information9 pensioner_flag,
aei_information12 date_pension_started,
trim(aei_information13) annual_pension,
aei_information14 recently_bereaved,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT assignment_extra_info_id,
aei_information15 expat_statement,
aei_information16 date_emp_start_uk,
aei_information17 eea_cw_citizen,
aei_information18 epm6_scheme,
aei_information19 expat_flag,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT paaf.assignment_number assignment_number,
paaf_old.assignment_number old_assignment_number
FROM per_all_assignments_f paaf,
per_all_assignments_f paaf_old
WHERE paaf.assignment_id = p_person_rec.assignment_id
AND ((paaf_old.assignment_id = paaf.assignment_id
AND paaf_old.effective_start_date < paaf.effective_start_date
AND paaf.effective_start_date > g_start_year
AND paaf_old.effective_end_date > g_start_year)
OR (paaf.assignment_number = paaf_old.assignment_number));
SELECT ptp.regular_payment_date,
ptp.period_num,
ptp.period_type
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE assignment_action_id = l_last_asg_action_id
AND ppa.time_period_id = ptp.time_period_id
AND ppa.payroll_id = ptp.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT ptpt.number_per_fiscal_year
FROM per_time_period_types ptpt
WHERE p_period_type = ptpt.period_type;
SELECT peevf.screen_entry_value screen_value
FROM
pay_element_entries_f peef,
pay_element_entry_values_f peevf
WHERE peef.assignment_id = c_asg_id
AND peef.element_entry_id = peevf.element_entry_id
AND peevf.input_value_id = g_ni_pt_ivid
AND peef.element_type_id = g_ni_pt_eid
AND c_asg_eff_start_date between peef.effective_start_date and peef.effective_end_date
AND c_asg_eff_start_date between peevf.effective_start_date and peevf.effective_end_date;
SELECT MAX(DECODE(name,'Periods',result_value,NULL))
FROM pay_assignment_actions paa ,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f petf ,
pay_input_values_f pivf
WHERE paa.source_action_id = p_last_asg_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id
AND petf.element_name = 'NI DETAILS'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND p_start_date between petf.effective_start_date and petf.effective_end_date
AND p_start_date between pivf.effective_start_date and pivf.effective_end_date;
SELECT MAX(DECODE(name,'Periods',result_value,NULL))
FROM pay_assignment_actions paa ,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f petf ,
pay_input_values_f pivf
WHERE paa.source_action_id = p_last_asg_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id
AND petf.element_name = 'NI'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND p_start_date between petf.effective_start_date and petf.effective_end_date
AND p_start_date between pivf.effective_start_date and pivf.effective_end_date;
SELECT trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
trim(NVL(pap.per_information10,'N')) per_agg_flag,
asg.assignment_number assignment_number
FROM per_all_people_f pap,
per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_act_id
AND pap.person_id = asg.person_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND pap.per_information_category = 'GB';
SELECT
/*+ USE_NL(paa, pact, ptp) */
MIN(ptp.start_date) payroll_period_start_date,
MAX(ptp.end_date) payroll_period_end_date
FROM pay_assignment_actions paa,
pay_payroll_actions pact,
per_time_periods ptp,
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
WHERE paa.assignment_id = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year AND p_end_year
AND ppa1.payroll_action_id = p_pre_pay_id
AND ppa1.action_type IN ('P','U')
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = pai.locked_action_id;
select serv.actual_termination_date actual_termination_date
from
per_all_assignments_f asg,
per_periods_of_service serv
where asg.assignment_id = p_person_rec.assignment_id
and asg.period_of_service_id = serv.period_of_service_id
and p_end_date between asg.effective_start_date and asg.effective_end_date
and actual_termination_date <= g_effective_date
and actual_termination_date <= sysdate + 30 ;
SELECT min(paaf.effective_start_date) min_active_start_date
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
WHERE paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = pay.payroll_id
AND pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
AND upper(g_tax_ref) = upper(sck.segment1); --Modified for the bug 16503199
select ppa_prev.payroll_action_id
from pay_payroll_actions ppa_prev,
pay_payroll_actions ppa_current
where ppa_prev.action_type IN ('P','U')
and ppa_prev.payroll_id = ppa_current.payroll_id
and ppa_prev.payroll_action_id < ppa_current.payroll_action_id
and ppa_current.payroll_action_id = g_pre_pact_id
and ppa_prev.payroll_action_id =
(select max(ppa_max.payroll_action_id)
from pay_payroll_actions ppa_max,
pay_assignment_actions paa_max
where ppa_max.payroll_id = ppa_current.payroll_id
and ppa_max.payroll_action_id < ppa_current.payroll_action_id
and ppa_max.action_type IN ('P','U')
and ppa_max.payroll_action_id = paa_max.payroll_action_id
and paa_max.assignment_id = c_asg_id );
select paa_fps.assignment_action_id
from pay_assignment_actions paa_prepay,
pay_payroll_actions ppa_prepay,
pay_assignment_actions paa_fps,
pay_payroll_actions ppa_fps,
pay_action_interlocks pai
where ppa_prepay.payroll_action_id = c_last_pre_pay_id
and ppa_prepay.payroll_action_id = paa_prepay.payroll_action_id
and paa_prepay.assignment_action_id = pai.locked_action_id
and paa_fps.assignment_action_id = pai.locking_action_id
and paa_fps.payroll_action_id = ppa_fps.payroll_action_id
and ppa_fps.action_type = 'X'
and paa_fps.assignment_id = c_asg_id;
select ACTION_INFORMATION13
from pay_action_information
where action_context_id = c_last_fps_asg_act_id
and action_information_category = 'GB_RTI_FPS_ASG_DET1'
and assignment_id = c_asg_id;
select fnd_date.canonical_to_date(max(decode(pivf.name,'Start Date',peevf.screen_entry_value,null))) start_date,
fnd_date.canonical_to_date(max(decode(pivf.name,'End Date',peevf.screen_entry_value,null))) end_date
from pay_element_entries_f peef,
pay_element_entry_values_f peevf ,
pay_input_values_f pivf
where
peef.assignment_id = p_person_rec.assignment_id
and peef.ELEMENT_TYPE_ID = g_adv_period_id
and peef.element_entry_id = peevf.element_entry_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = peef.ELEMENT_TYPE_ID
and g_effective_date between peevf.effective_start_date and peevf.effective_end_date
and g_effective_date between pivf.effective_start_date and pivf.effective_end_date
and g_effective_date between peef.effective_start_date and peef.effective_end_date;
SELECT
/*+ USE_NL(paa, pact, ptp) */
to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id,
MAX(pact.effective_date) effective_date
FROM per_all_assignments_f paaf,
pay_assignment_actions paa,
pay_payroll_actions pact,
per_time_periods ptp,
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
WHERE paaf.person_id = p_person_rec.person_id--59437
AND sysdate between paaf.effective_start_date and paaf.effective_end_date
AND paa.assignment_id = paaf.assignment_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','I','V','B')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN g_start_year AND g_end_year--p_start_year AND p_end_year
AND ppa1.payroll_action_id = g_pre_pact_id --1160712
AND ppa1.action_type IN ('P','U')
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = pai.locked_action_id;
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_starter_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information8 => 'Y');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_pensioner_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information9 => 'Y');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_expat_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information19 => 'Y');
select
pay_advance_pay_pkg.advanced_periods(
p_person_rec.assignment_id,
l_start_date,
l_end_date
) into l_periods_covered from dual;
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn_extra_info,
p_assignment_extra_info_id => l_asg_info_id,
p_aei_information_category => 'GB_PAY_RTI',
p_aei_information1 => 1);
SELECT AEI_INFORMATION1 periods_worked,
AEI_INFORMATION3 irr_payment ,
AEI_INFORMATION4 "One-Off Payment",
ASSIGNMENT_EXTRA_INFO_ID,
object_version_number
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
AND assignment_id = l_asg_id;
SELECT assignment_extra_info_id,
aei_information1 starter_decl,
aei_information2 prev_emp_paye_ref,
aei_information3 prev_tax_code,
aei_information4 date_left_prev_employer,
aei_information5 prev_tax_basis,
aei_information6 last_payment_period_type,
aei_information7 last_payment_period,
aei_information8 starter_flag,
aei_information10 continue_with_student_loan,
aei_information8 not_paid_between,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT assignment_extra_info_id,
aei_information9 pensioner_flag,
aei_information12 date_pension_started,
trim(aei_information13) annual_pension,
aei_information14 recently_bereaved,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT assignment_extra_info_id,
aei_information15 expat_statement,
aei_information16 date_emp_start_uk,
aei_information17 eea_cw_citizen,
aei_information18 epm6_scheme,
aei_information19 expat_flag,
object_version_number object_version_number
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT paaf.assignment_number assignment_number,
paaf_old.assignment_number old_assignment_number
FROM per_all_assignments_f paaf,
per_all_assignments_f paaf_old
WHERE paaf.assignment_id = l_asg_id
AND ((paaf_old.assignment_id = paaf.assignment_id
AND paaf_old.effective_start_date < paaf.effective_start_date
AND paaf.effective_start_date > g_start_year
AND paaf_old.effective_end_date > g_start_year)
OR (paaf.assignment_number = paaf_old.assignment_number));
SELECT ptp.regular_payment_date,
ptp.period_num,
ptp.period_type
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE assignment_action_id = p_last_asg_action_id
AND ppa.time_period_id = ptp.time_period_id
AND ppa.payroll_id = ptp.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT ptpt.number_per_fiscal_year
FROM per_time_period_types ptpt
WHERE p_period_type = ptpt.period_type;
SELECT peevf.screen_entry_value screen_value
FROM
pay_element_entries_f peef,
pay_element_entry_values_f peevf
WHERE peef.assignment_id = c_asg_id
AND peef.element_entry_id = peevf.element_entry_id
AND peevf.input_value_id = g_ni_pt_ivid
AND peef.element_type_id = g_ni_pt_eid
AND c_asg_eff_start_date between peef.effective_start_date and peef.effective_end_date
AND c_asg_eff_start_date between peevf.effective_start_date and peevf.effective_end_date;
SELECT MAX(DECODE(name,'Periods',result_value,NULL))
FROM pay_assignment_actions paa ,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f petf ,
pay_input_values_f pivf
WHERE paa.source_action_id = p_last_asg_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id
AND petf.element_name = 'NI DETAILS'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id;
SELECT MAX(DECODE(name,'Periods',result_value,NULL))
FROM pay_assignment_actions paa ,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f petf ,
pay_input_values_f pivf
WHERE paa.source_action_id = p_last_asg_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id
AND petf.element_name = 'NI'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pivf.input_value_id;
SELECT trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
trim(NVL(pap.per_information10,'N')) per_agg_flag,
asg.assignment_number assignment_number,
asg.assignment_id assignment_id
FROM per_all_people_f pap,
per_all_assignments_f asg,
pay_assignment_actions paa
WHERE paa.assignment_action_id = c_asg_act_id
AND paa.assignment_id = asg.assignment_id
AND pap.person_id = asg.person_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND pap.per_information_category = 'GB';
(SELECT MAX(EFFECTIVE_START_DATE)
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = asg.assignment_id
);*/
SELECT
/*+ USE_NL(paa, pact, ptp) */
MIN(ptp.start_date) payroll_period_start_date,
MAX(ptp.end_date) payroll_period_end_date
FROM pay_assignment_actions paa,
pay_payroll_actions pact,
per_time_periods ptp,
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
WHERE paa.assignment_id = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year AND p_end_year
AND ppa1.payroll_action_id = p_pre_pay_id
AND ppa1.action_type IN ('P','U')
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = pai.locked_action_id;
select serv.actual_termination_date actual_termination_date
from
per_all_assignments_f asg,
per_periods_of_service serv
where asg.assignment_id = l_asg_id
and asg.period_of_service_id = serv.period_of_service_id
and p_end_date between asg.effective_start_date and asg.effective_end_date
and actual_termination_date <= g_effective_date
and actual_termination_date <= sysdate + 30 ;
SELECT min(paaf.effective_start_date) min_active_start_date
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex sck
WHERE paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = pay.payroll_id
AND pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
AND upper(g_tax_ref) = upper(sck.segment1); --Modified for the bug 16503199
select ppa_prev.payroll_action_id
from pay_payroll_actions ppa_prev,
pay_payroll_actions ppa_current
where ppa_prev.action_type IN ('P','U')
and ppa_prev.payroll_id = ppa_current.payroll_id
and ppa_prev.payroll_action_id < ppa_current.payroll_action_id
and ppa_current.payroll_action_id = g_pre_pact_id
and ppa_prev.payroll_action_id =
(select max(ppa_max.payroll_action_id)
from pay_payroll_actions ppa_max,
pay_assignment_actions paa_max
where ppa_max.payroll_id = ppa_current.payroll_id
and ppa_max.payroll_action_id < ppa_current.payroll_action_id
and ppa_max.action_type IN ('P','U')
and ppa_max.payroll_action_id = paa_max.payroll_action_id
and paa_max.assignment_id = c_asg_id );
select paa_fps.assignment_action_id
from pay_assignment_actions paa_prepay,
pay_payroll_actions ppa_prepay,
pay_assignment_actions paa_fps,
pay_payroll_actions ppa_fps,
pay_action_interlocks pai
where ppa_prepay.payroll_action_id = c_last_pre_pay_id
and ppa_prepay.payroll_action_id = paa_prepay.payroll_action_id
and paa_prepay.assignment_action_id = pai.locked_action_id
and paa_fps.assignment_action_id = pai.locking_action_id
and paa_fps.payroll_action_id = ppa_fps.payroll_action_id
and ppa_fps.action_type = 'X'
and paa_fps.assignment_id = c_asg_id;
select ACTION_INFORMATION13
from pay_action_information
where action_context_id = c_last_fps_asg_act_id
and action_information_category = 'GB_RTI_FPS_ASG_DET1'
and assignment_id = c_asg_id;
select fnd_date.canonical_to_date(max(decode(pivf.name,'Start Date',peevf.screen_entry_value,null))) start_date,
fnd_date.canonical_to_date(max(decode(pivf.name,'End Date',peevf.screen_entry_value,null))) end_date
from pay_element_entries_f peef,
pay_element_entry_values_f peevf ,
pay_input_values_f pivf
where
peef.assignment_id = l_asg_id
and peef.ELEMENT_TYPE_ID = g_adv_period_id
and peef.element_entry_id = peevf.element_entry_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = peef.ELEMENT_TYPE_ID;
SELECT aei_information3 rti_payroll_id
FROM per_assignment_extra_info
WHERE assignment_id = cp_assignment_id
AND aei_information_category = 'GB_RTI_AGGREGATION';
select PER_INFORMATION2 from per_all_people_f papf
where person_id = p_person_rec.person_id
and effective_start_date =
(select max(effective_start_date)
from per_all_people_f where person_id = papf.person_id and effective_start_date <= g_effective_date);
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_starter_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information8 => 'Y');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_pensioner_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information9 => 'Y');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_expat_rec.assignment_extra_info_id,
p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information19 => 'Y');
select
pay_advance_pay_pkg.advanced_periods(
l_asg_id,
l_start_date,
l_end_date
) into l_periods_covered from dual;
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn_extra_info,
p_assignment_extra_info_id => l_asg_info_id,
p_aei_information_category => 'GB_PAY_RTI',
p_aei_information1 => 1);
select paaf.effective_start_date from
per_all_assignments_f paaf,
pay_all_payrolls_f papf ,
hr_soft_coding_keyflex flex,
per_assignment_status_types past
where paaf.assignment_id = asg_id and paaf.effective_end_date = eff_date - 1
and papf.payroll_id = paaf.payroll_id
and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
and upper(l_tax_ref) = upper(flex.segment1)
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN', 'TERM_ASSIGN'); --Bug: 16276411
SELECT max(prr.RUN_RESULT_ID)
FROM pay_run_results prr
WHERE prr.ASSIGNMENT_ACTION_ID = p_assignment_action_id
AND prr.ELEMENT_TYPE_ID = p_element_id
AND prr.status in ('P','PA');
SELECT max(decode(name,'Tax Code',result_value,NULL)) tax_code,
MAX(DECODE(name,'Tax Basis',DECODE(result_value,'N','Y',NULL),NULL)) tax_basis
FROM pay_input_values_f v,
pay_run_result_values rrv
WHERE rrv.run_result_id = p_run_result_id
AND v.input_value_id = rrv.input_value_id
AND v.element_type_id = p_element_type_id;
select serv.actual_termination_date actual_termination_date
from
per_all_assignments_f asg,
per_periods_of_service serv
where asg.assignment_id = p_asg_id
and serv.actual_termination_date is not null
and asg.period_of_service_id = serv.period_of_service_id
and p_payment_date between asg.effective_start_date and asg.effective_end_date;
select paaf.effective_start_date,paaf.effective_end_date
from per_all_assignments_f paaf
where paaf.assignment_id = p_asg_id
and p_payment_date between paaf.effective_start_date and paaf.effective_end_date;
SELECT aei_information8 starter_flag , aei_information1 starter_decl
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
SELECT trim(asg.primary_flag) asg_primary_flag,
trim(pap.per_information10) per_agg_flag
FROM per_all_people_f pap,
per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND pap.person_id = asg.person_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)),
decode(max(decode(iv.name,'Tax Basis',screen_entry_value)),'N','Y',NULL)
FROM pay_element_entries_f e,
pay_element_entry_values_f v,
pay_input_values_f iv,
pay_element_links_f link
WHERE e.assignment_id = p_asg_id
AND link.element_type_id = g_paye_details_id
AND e.element_link_id = link.element_link_id
AND e.element_entry_id = v.element_entry_id
AND iv.input_value_id = v.input_value_id
AND e.effective_end_date BETWEEN link.effective_start_date AND link.effective_end_date
AND e.effective_end_date BETWEEN iv.effective_start_date AND iv.effective_end_date
AND e.effective_end_date BETWEEN v.effective_start_date AND v.effective_end_date
AND e.effective_end_date = (select max(e1.effective_end_date)
from pay_element_entries_f e1,
pay_element_links_f link1
where link1.element_type_id = g_paye_details_id
and e1.assignment_id = p_asg_id
and e1.element_link_id = link1.element_link_id);
SELECT NVL(MAX(ASSIGNMENT_ACTION_ID),c_lst_act_id)
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
SELECT MAX(DECODE(name,'Tax Code',prrv.result_value,NULL)),
MAX(DECODE(name,'Tax Basis',DECODE(prrv.result_value,'N','Y',NULL),NULL))
FROM pay_run_results prr ,
pay_run_result_values prrv ,
pay_input_values_f pivf
WHERE prr.assignment_action_id = p_action_id
AND prr.ELEMENT_TYPE_ID = g_paye_element_id
AND prrv.run_result_id = prr.run_result_id
AND pivf.INPUT_VALUE_ID = prrv.INPUT_VALUE_ID;
SELECT trim(asg.primary_flag) asg_primary_flag,
trim(pap.per_information10) per_agg_flag
FROM per_all_people_f pap,
per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND pap.person_id = asg.person_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)),
decode(max(decode(iv.name,'Tax Basis',screen_entry_value)),'N','Y',NULL)
FROM pay_element_entries_f e,
pay_element_entry_values_f v,
pay_input_values_f iv,
pay_element_links_f link
WHERE e.assignment_id = p_asg_id
AND link.element_type_id = g_paye_details_id
AND e.element_link_id = link.element_link_id
AND e.element_entry_id = v.element_entry_id
AND iv.input_value_id = v.input_value_id
AND e.effective_end_date BETWEEN link.effective_start_date AND link.effective_end_date
AND e.effective_end_date BETWEEN iv.effective_start_date AND iv.effective_end_date
AND e.effective_end_date BETWEEN v.effective_start_date AND v.effective_end_date
AND e.effective_end_date = (select max(e1.effective_end_date)
from pay_element_entries_f e1,
pay_element_links_f link1
where link1.element_type_id = g_paye_details_id
and e1.assignment_id = p_asg_id
and e1.element_link_id = link1.element_link_id);
SELECT NVL(MAX(ASSIGNMENT_ACTION_ID),c_lst_act_id)
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
SELECT MAX(DECODE(name,'Tax Code',prrv.result_value,NULL)),
MAX(DECODE(name,'Tax Basis',DECODE(prrv.result_value,'N','Y',NULL),NULL))
FROM pay_run_results prr ,
pay_run_result_values prrv ,
pay_input_values_f pivf
WHERE prr.assignment_action_id = p_action_id
AND prr.ELEMENT_TYPE_ID = g_paye_element_id
AND prrv.run_result_id = prr.run_result_id
AND pivf.INPUT_VALUE_ID = prrv.INPUT_VALUE_ID;
SELECT trim(asg.primary_flag) asg_primary_flag,
trim(pap.per_information10) per_agg_flag
FROM per_all_people_f pap,
per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND pap.person_id = asg.person_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE source_action_id = c_lst_act_id;
SELECT MAX(DECODE(name,'Tax Code',prrv.result_value,NULL)),
MAX(DECODE(name,'Tax Basis',DECODE(prrv.result_value,'N','Y',NULL),NULL))
FROM pay_run_results prr ,
pay_run_result_values prrv ,
pay_input_values_f pivf
WHERE prr.assignment_action_id = p_action_id
AND prr.ELEMENT_TYPE_ID = g_paye_element_id
AND prrv.run_result_id = prr.run_result_id
AND pivf.INPUT_VALUE_ID = prrv.INPUT_VALUE_ID;
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
max(decode(iv.name,'Tax Basis',DECODE(screen_entry_value,'N','Y',NULL),NULL)) tax_basis -- kvinayku bug no 14774165
FROM pay_element_entries_f e,
pay_element_entry_values_f v,
pay_input_values_f iv,
pay_element_links_f link
WHERE e.assignment_id = p_asg_id
AND link.element_type_id = g_paye_details_id
AND e.element_link_id = link.element_link_id
AND e.element_entry_id = v.element_entry_id
AND iv.input_value_id = v.input_value_id
AND e.effective_end_date BETWEEN link.effective_start_date AND link.effective_end_date
AND e.effective_end_date BETWEEN iv.effective_start_date AND iv.effective_end_date
AND e.effective_end_date BETWEEN v.effective_start_date AND v.effective_end_date
AND e.effective_end_date = (select max(e1.effective_end_date)
from pay_element_entries_f e1,
pay_element_links_f link1
where link1.element_type_id = g_paye_details_id
and e1.assignment_id = p_asg_id
and e1.element_link_id = link1.element_link_id);
select pdb.defined_balance_id from pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
where pbt.balance_name = c_balance_name
and pbd.database_item_suffix = c_dim_name
and pbt.balance_type_id= pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id and
pbt.legislation_code = 'GB';
select hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id) from dual;
SELECT max(assignment_action_id)
FROM pay_assignment_actions
WHERE source_action_id = c_lst_act_id;
SELECT trim(asg.primary_flag) asg_primary_flag,
trim(pap.per_information10) per_agg_flag
FROM per_all_people_f pap,
per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND pap.person_id = asg.person_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
SELECT ASSIGNMENT_ACTION_ID
FROM pay_assignment_actions
WHERE SOURCE_ACTION_ID = c_lst_act_id;
SELECT MAX(DECODE(name,'Tax Code',prrv.result_value,NULL)),
MAX(DECODE(name,'Tax Basis',DECODE(prrv.result_value,'N','Y',NULL),NULL))
FROM pay_run_results prr ,
pay_run_result_values prrv ,
pay_input_values_f pivf
WHERE prr.assignment_action_id = p_action_id
AND prr.ELEMENT_TYPE_ID = g_paye_element_id
AND prrv.run_result_id = prr.run_result_id
AND pivf.INPUT_VALUE_ID = prrv.INPUT_VALUE_ID;
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
max(decode(iv.name,'Tax Basis',decode(screen_entry_value,'N','Y',NULL),NULL)) tax_basis -- added by kvinayku 14774165
FROM pay_element_entries_f e,
pay_element_entry_values_f v,
pay_input_values_f iv,
pay_element_links_f link
WHERE e.assignment_id = p_asg_id
AND link.element_type_id = g_paye_details_id
AND e.element_link_id = link.element_link_id
AND e.element_entry_id = v.element_entry_id
AND iv.input_value_id = v.input_value_id
AND e.effective_end_date BETWEEN link.effective_start_date AND link.effective_end_date
AND e.effective_end_date BETWEEN iv.effective_start_date AND iv.effective_end_date
AND e.effective_end_date BETWEEN v.effective_start_date AND v.effective_end_date
AND e.effective_end_date = (select max(e1.effective_end_date)
from pay_element_entries_f e1,
pay_element_links_f link1
where link1.element_type_id = g_paye_details_id
and e1.assignment_id = p_asg_id
and e1.element_link_id = link1.element_link_id);
select pdb.defined_balance_id from pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
where pbt.balance_name = c_balance_name
and pbd.database_item_suffix = c_dim_name
and pbt.balance_type_id= pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id and
pbt.legislation_code = 'GB';
select hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id) from dual;
SELECT max(assignment_action_id)
FROM pay_assignment_actions
WHERE source_action_id = c_lst_act_id;
INSERT
INTO pay_message_lines
(
line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text
)
VALUES
(
pay_message_lines_s.nextval ,
100 ,
p_message_level, --'F' ,
p_assignment_action_id ,
'A' ,
SUBSTR(p_message_text,1,240)
);
PROCEDURE insert_archive_row
(
p_assactid IN NUMBER,
p_effective_date IN DATE,
p_tab_rec_data IN action_info_table
)
IS
l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
END insert_archive_row;
PROCEDURE insert_archive_row_agg
(
p_assactid IN NUMBER,
p_effective_date IN DATE,
p_tab_rec_data IN action_info_table
)
IS
l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row_agg';
select paa.assignment_action_id into l_assact_id from pay_assignment_actions paa
where paa.payroll_action_id = g_payroll_action_id
and paa.assignment_id = p_tab_rec_data(i).assignment_id;
END insert_archive_row_agg;
Purpose : This returns the select statement that is used to create the
range rows.
Arguments :
Notes : The range cursor determines which people should be processed.
The normal practice is to include everyone, and then limit
the list during the assignment action creation.
--------------------------------------------------------------------------*/
PROCEDURE range_cursor
(
pactid IN NUMBER,
sqlstr OUT NOCOPY VARCHAR2
)
IS
CURSOR csr_parameter_info
IS
SELECT SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PRE_PAY_ID'),1,20) pre_pay_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS'),1,20) first_fps,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS_DATE'),1,10)) first_fps_date
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT payroll_id
FROM pay_payroll_actions
WHERE payroll_action_id = c_pre_pact_id;
select 'Y' from pay_payroll_actions ppa
where c_payroll_id = pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYROLL_ID')
and pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS') = 'Y'
and ACTION_STATUS = 'C'
and exists (select 1 from pay_gb_fps_details where PAYROLL_ACTION_ID = ppa.payroll_action_id);
select distinct ppa.payroll_action_id
from pay_payroll_actions ppa
where ppa.effective_date >= sysdate - 30
and ppa.action_type in ('P','U')
and ppa.action_status ='C'
and ppa.payroll_id in (select distinct payroll_id
from pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
where upper(flex.segment1) = c_tax_ref
and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID)
minus
select distinct pre_payment_payroll_action_id from pay_gb_bacs_fps;
fnd_file.put_line (fnd_file.LOG,'Error : First FPS has already been run for the requested payroll. Either rollback or select to run a normal FPS.');
sqlstr := 'select distinct PERSON_ID '|| 'from per_all_assignments_f paaf, '
|| 'pay_payroll_actions ppa '|| 'where ppa.payroll_action_id = :payroll_action_id '
|| 'and ppa.business_group_id = paaf.business_group_id '|| ' and paaf.payroll_id = '
||''''||l_payroll_id||''''|| ' order by person_id';
SELECT to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS'),1,1) first_fps,
effective_date,
business_group_id,
fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS_DATE'),1,10)) first_fps_date
FROM pay_payroll_actions pact
WHERE payroll_action_id = pactid;
SELECT asg.person_id person_id,
trim(asg.primary_flag) asg_primary_flag,
trim(nvl(pap.per_information10,'N')) per_agg_flag,
trim(nvl(pap.per_information9,'N')) ni_agg_flag,
asg.assignment_number
FROM per_all_people_f pap,
per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND pap.person_id = asg.person_id
AND asg.business_group_id = l_business_group_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
SELECT DISTINCT act.assignment_id assignment_id,
act.assignment_action_id asg_act_id
FROM --pay_payroll_actions ppa, --Current pactid
pay_payroll_actions appa, --Payroll Run
pay_payroll_actions appa2, --Prepayments
pay_assignment_actions act, --Payroll Run
pay_assignment_actions act1, --Prepayments
pay_action_interlocks pai, --Prepayments
per_all_assignments_f as1
WHERE --ppa.payroll_action_id = pactid
as1.person_id BETWEEN stperson AND endperson
AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL
AND as1.assignment_id = act.assignment_id
AND appa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND as1.payroll_id = p_payroll_id
--OR p_payroll_id IS NULL)
AND appa2.payroll_action_id = p_pre_pact_id
AND exists( select 1
from pay_pre_payments ppp
where ASSIGNMENT_ACTION_ID = act1.assignment_action_id
and ppp.value <> 0)
ORDER BY act.assignment_id;
select effective_date, payroll_id, business_group_id
from pay_payroll_actions
where payroll_action_id = p_pre_pact_id;
select distinct paaf.assignment_id assignment_id,
trim(paaf.primary_flag) asg_primary_flag,
trim(pap.per_information10) per_agg_flag,
pap.person_id
from per_all_people_f pap,
per_all_assignments_f paaf,
per_assignment_status_types past --,
--pay_payroll_actions paa
where pap.person_id between stperson and endperson
-- and paa.payroll_Action_id = p_prepay_id
and pap.person_id = paaf.person_id
and paaf.assignment_type = 'E'
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN','TERM_ASSIGN')
and paaf.business_group_id = l_bus_grp_id --paa.business_group_id
and paaf.payroll_id = l_prl_id --paa.payroll_id
and pap.effective_start_date =
( select max(pap2.effective_start_date) from
per_all_people_f pap2
where pap2.person_id = pap.person_id
and pap2.effective_start_date <= p_prepayment_date
)
and paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= p_prepayment_date
)
order by pap.person_id;
SELECT paa_pre.assignment_id
FROM pay_assignment_actions paa_pre
, pay_assignment_actions paa_arcv
WHERE paa_arcv.assignment_id = p_asg_id
AND paa_pre.payroll_action_id = pre_pay_id
AND paa_pre.assignment_id = paa_arcv.assignment_id;
SELECT defined_balance_id
FROM pay_defined_balances db
, pay_balance_types b
, pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension_name
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND b.legislation_code = 'GB'
AND d.legislation_code = 'GB';
SELECT
to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| paa.assignment_action_id), 16))
, max (pact.effective_date) effective_date
FROM pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp
WHERE paa.assignment_id = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.payroll_id = l_payroll_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN p_start_year
AND p_end_year;
SELECT max (act.assignment_action_id) assignment_id
-- SELECT min (act.assignment_action_id) assignment_id
-- SELECT max (act.assignment_id) assignment_id
FROM pay_payroll_actions ppa, --Current pactid
pay_payroll_actions appa, --Payroll Run
pay_payroll_actions appa2, --Prepayments
pay_assignment_actions act, --Payroll Run
pay_assignment_actions act1, --Prepayments
pay_action_interlocks pai, --Prepayments
per_all_assignments_f as1
WHERE as1.person_id = p_person_id --45885
AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL
AND as1.assignment_id = act.assignment_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND (as1.payroll_id = p_payroll_id
OR p_payroll_id IS NULL)
AND appa2.payroll_action_id = p_pre_pact_id
AND exists( select 1
from pay_pre_payments ppp
where ASSIGNMENT_ACTION_ID = act1.assignment_action_id
and ppp.value <> 0); --For the bug 16542698
SELECT max (act.assignment_action_id) assignment_id
-- SELECT max (act.assignment_id) assignment_id
FROM pay_payroll_actions ppa, --Current pactid
pay_payroll_actions appa, --Payroll Run
pay_payroll_actions appa2, --Prepayments
pay_assignment_actions act, --Payroll Run
pay_assignment_actions act1, --Prepayments
pay_action_interlocks pai, --Prepayments
per_all_assignments_f as1
WHERE as1.person_id = p_person_id --45885
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
AND as1.assignment_id = act.assignment_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND (as1.payroll_id = p_payroll_id
OR p_payroll_id IS NULL)
AND appa2.payroll_action_id = p_pre_pact_id;
select paaf.assignment_id asg_id from per_all_assignments_f paaf where
paaf.person_id = l_person_id
and paaf.assignment_id <> c_asg_id
and l_effective_date between paaf.effective_start_date and paaf.effective_end_date;
SELECT
/* to_number (substr (max (lpad (paa.action_sequence ,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id*/
distinct paa.assignment_id
/* ,max (pact.effective_date) effective_date
,max(paa1.assignment_action_id)*/
FROM pay_assignment_actions paa
,pay_assignment_actions paa1
,per_all_assignments_f paaf
,pay_payroll_actions pact
,pay_payroll_actions pact1
,per_time_periods ptp
,pay_action_interlocks pai
WHERE paa.assignment_id = paaf.assignment_id
AND paa.assignment_id <> p_asg_id
AND paaf.person_id = p_person_id
AND paa.payroll_action_id = pact.payroll_action_id
AND paa1.payroll_action_id = pact1.payroll_action_id
AND pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = paa1.assignment_action_id
AND pact1.action_type IN ('P','U')
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND paa1.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year
AND p_end_year
AND (
(
paa.assignment_action_id >
(
SELECT nvl (max (payroll_asg_act_id),paa.assignment_action_id)
FROM pay_gb_fps_details pgfd
WHERE pgfd.person_id = p_person_id
)
AND paa.source_action_id IS NULL
)
OR (
(
SELECT count (*)
FROM pay_gb_fps_details pgfd
WHERE pgfd.person_id = p_person_id
) = 0
)
)
AND ptp.regular_payment_date >= nvl (l_first_fps_run_date, l_first_fps_eff_date)
AND paa.assignment_action_id < p_asg_act_id
AND exists( select 1
from pay_pre_payments ppp
where ASSIGNMENT_ACTION_ID = paa1.assignment_action_id
and ppp.value <> 0)
GROUP BY paa.payroll_action_id,paa.assignment_id ;
SELECT /*+ USE_NL(paa, pact, ptp) */
/* to_number (substr (max (lpad (paa.action_sequence ,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id
,max(paa.assignment_id)
,max (pact.effective_date) effective_date*/
distinct max(paa1.assignment_action_id) max_pre_act_id
FROM pay_assignment_actions paa
,pay_assignment_actions paa1
,per_all_assignments_f paaf
,pay_payroll_actions pact
,pay_payroll_actions pact1
,per_time_periods ptp
,pay_action_interlocks pai
WHERE paa.assignment_id = paaf.assignment_id
AND paa.assignment_id = p_asg_id
AND paaf.person_id = p_person_id
AND paa.payroll_action_id = pact.payroll_action_id
AND paa1.payroll_action_id = pact1.payroll_action_id
AND pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = paa1.assignment_action_id
AND pact1.action_type IN ('P','U')
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND paa1.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year
AND p_end_year
AND (
(
paa.assignment_action_id >
(
SELECT nvl (max (payroll_asg_act_id),paa.assignment_action_id)
FROM pay_gb_fps_details pgfd
WHERE pgfd.person_id = p_person_id
-- AND pgfd.assignment_id = p_asg_id
)
AND paa.source_action_id IS NULL
)
OR (
(
SELECT count (*)
FROM pay_gb_fps_details pgfd
WHERE pgfd.person_id = p_person_id
AND pgfd.assignment_id = p_asg_id
) = 0
)
)
AND ptp.regular_payment_date >= nvl (l_first_fps_run_date, l_first_fps_eff_date)
GROUP BY paa.payroll_action_id
ORDER BY max_pre_act_id;
select pai.locking_action_id
from
pay_action_interlocks pai,
pay_payroll_actions ppa,
pay_assignment_actions paa
where pai.locked_action_id = c_cur_asg_action_id
and ppa.action_type in ('P','U')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = pai.locking_action_id;
SELECT paa.assignment_action_id,
ppa.payroll_action_id,
ppa.effective_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_asgid
AND ppa.payroll_action_id = p_pre_pay_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT max (ppa2.effective_date)
FROM pay_payroll_actions ppa2
WHERE ppa2.report_qualifier = 'GB'
AND ppa2.report_type = 'RTI_FPS_REP'
AND ppa2.action_status = 'C'
AND substr (pay_gb_eoy_archive.get_parameter (ppa2.legislative_parameters,'FIRST_FPS'),1,20) = 'Y'
AND substr (pay_gb_eoy_archive.get_parameter (ppa2.legislative_parameters,'PAYROLL_ID'),1,20) = c_payroll_id;
select AEI_INFORMATION1, AEI_INFORMATION2 from per_assignment_extra_info where assignment_id = c_asg_id
and AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION';
select
paei.assignment_id , fnd_date.canonical_to_date(paei.AEI_INFORMATION2)
from per_all_assignments_f paaf1, per_all_assignments_f paaf2 ,per_assignment_extra_info paei
where paaf1.assignment_id = c_asg_id
and paaf1.person_id = paaf2.person_id
and paaf2.ASSIGNMENT_TYPE = 'E'
and paei.assignment_id = paaf2.assignment_id
and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
and paei. AEI_INFORMATION1 = 'Y'
and l_effective_date between paaf1.effective_start_date and paaf1.effective_end_date
and l_effective_date between paaf2.effective_start_date and paaf2.effective_end_date
order by 2 desc;
select pai.locking_action_id prepay_asg_act_id from
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locked_action_id = c_payroll_asg_act_id
and pai.locking_action_id = paa.assignment_action_id
and paa.action_status in ('C','S')
and ppa.action_type in ('P','U')
and paa.payroll_action_id = ppa.payroll_action_id;
select 'Y' from pay_assignment_actions paa,
pay_payroll_actions ppa
where
ppa.payroll_action_id = c_pre_pay_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = c_ni_rpt_asg_id
and ppa.action_type IN ('P','U')
and paa.action_status IN ('C','S')
AND exists( select 1
from pay_pre_payments ppp
where ASSIGNMENT_ACTION_ID = paa.assignment_action_id
and ppp.value <> 0); --For the bug 16542698
SELECT count (*)
FROM pay_gb_fps_details pgfd
WHERE pgfd.person_id = c_person_id
AND pgfd.payroll_asg_act_id = c_cur_asg_action_id;
SELECT count(distinct paa1.assignment_id)
FROM pay_assignment_actions paa,
per_all_assignments_f paaf,
pay_payroll_actions pact,
per_time_periods ptp,
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
WHERE paa.assignment_id = paaf.assignment_id
AND paaf.person_id = p_person_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year AND p_end_year
AND ppa1.payroll_action_id = p_pre_pay_id
AND ppa1.action_type IN ('P','U')
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = pai.locked_action_id;
SELECT max(act.assignment_action_id) assignment_action_id
FROM pay_payroll_actions appa, --Payroll Run
pay_payroll_actions appa2, --Prepayments
pay_assignment_actions act, --Payroll Run
pay_assignment_actions act1, --Prepayments
pay_action_interlocks pai, --Prepayments
per_all_assignments_f as1,
pay_all_payrolls_f papf ,
hr_soft_coding_keyflex flex
WHERE as1.person_id = p_person_id --58105
AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL
AND as1.assignment_id = act.assignment_id
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND appa2.effective_date between g_start_year and l_first_fps_eff_date
AND appa2.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND papf.payroll_id = as1.payroll_id
AND flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
AND upper(flex.segment1) = upper(l_tax_ref);
SELECT assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = c_aggr_max_act_id;
SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
/* SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
SELECT pay_assignment_actions_s.nextval INTO lockingactid FROM dual;
SELECT aei.assignment_extra_info_id,
aei.object_version_number,
aei.aei_information8
FROM pay_assignment_actions paa,
per_assignment_extra_info aei
WHERE paa.assignment_action_id = p_assact
AND aei.assignment_id = paa.assignment_id
AND aei.information_type = p_type;
SELECT aei.assignment_extra_info_id,
aei.object_version_number,
aei.aei_information9
FROM pay_assignment_actions paa,
per_assignment_extra_info aei
WHERE paa.assignment_action_id = p_assact
AND aei.assignment_id = paa.assignment_id
AND aei.information_type = p_type;
SELECT aei.assignment_extra_info_id,
aei.object_version_number,
aei.aei_information19
FROM pay_assignment_actions paa,
per_assignment_extra_info aei
WHERE paa.assignment_action_id = p_assact
AND aei.assignment_id = paa.assignment_id
AND aei.information_type = p_type;
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_aei_starter_rec.assignment_extra_info_id,
p_aei_information_category => p_type,
p_aei_information8 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_aei_pensioner_rec.assignment_extra_info_id,
p_aei_information_category => p_type,
p_aei_information9 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false,
p_object_version_number => l_ovn,
p_assignment_extra_info_id => l_aei_expat_rec.assignment_extra_info_id,
p_aei_information_category => p_type,
p_aei_information19 => 'N');
SELECT effective_date,
business_group_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PRE_PAY_ID'),1,20) pre_pay_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYROLL_ID'),1,20) payroll_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
SUBSTR(pay_gb_eoy_archive.get_parameter (legislative_parameters, 'FIRST_FPS'), 1,1) first_fps
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT
ppa.effective_date
FROM
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = pre_pay_id;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT NVL(UPPER(hoi.org_information11),' ') sender_id,
DECODE(SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
fnd_number.number_to_canonical(pact.request_id) request_id,
NVL(upper(SUBSTR(ltrim(hoi.org_information3),1,35)),' ') employer_name,
NVL(upper(SUBSTR(ltrim(SUBSTR(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
lpad(NVL(SUBSTR(hoi.org_information1,1,3),' '),3,0) tax_office_no,
DECODE(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
NVL(upper(SUBSTR(hoi.org_information6,1,13)),' ') acc_ref_no,
report_type
FROM pay_payroll_actions pact,
hr_organization_information hoi
WHERE pact.payroll_action_id =pactid
AND pact.business_group_id = hoi.organization_id
AND hoi.org_information_context = 'Tax Details References'
AND NVL(hoi.org_information10,'UK') = 'UK'
AND SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8, instr(pact.legislative_parameters
||' ',' ', instr(pact.legislative_parameters,'TAX_REF=') +8) - instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
SELECT petf.element_type_id,
input_value_id
FROM pay_element_types_f petf,
pay_input_values_f pivf
WHERE petf.element_name = 'NI'
AND petf.element_type_id = pivf.element_type_id
AND pivf.name = 'Process Type'
AND petf.legislation_code = 'GB'
AND pivf.legislation_code = 'GB'
AND l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
AND l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
SELECT b.balance_name balance_name,
b.balance_type_id balance_type_id,
d.database_item_suffix database_item_suffix,
d.balance_dimension_id balance_dimension_id,
db.defined_balance_id defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND ((d.database_item_suffix = '_ASG_TD_YTD'
AND b.balance_name IN ('Taxable Pay','PAYE','Student Loan','SSP Total',
'SMP Total','SPP Adoption Total', 'SPP Birth Total','SAP Total',
'ASPP Adoption Total','ASPP Birth Total','Pre Tax Pension Contributions',
'Post Tax Pension Contributions',
'Earnings Free of Tax','Earnings Free of NI','Earnings Free of Tax and NI',
'Benefits Taxed Through Payroll Subject to NIC Earnings',
'Benefits Taxed Through Payroll NOT Subject to NIC Earnings',
'Benefits Taxed Through Payroll Subject to NIC Information',
'Benefits Taxed Through Payroll NOT Subject to NIC Information', 'NI Employer',
'Total Direct Payments'))
OR(d.database_item_suffix = '_ASG_RUN'
AND b.balance_name IN ('Taxable Pay', 'Student Loan', 'PAYE', 'Pre Tax Pension Contributions',
'Post Tax Pension Contributions','NIC able Benefits',
'Earnings Free of Tax','Earnings Free of NI','Earnings Free of Tax and NI',
'Benefits Taxed Through Payroll Subject to NIC Earnings',
'Benefits Taxed Through Payroll NOT Subject to NIC Earnings',
'Benefits Taxed Through Payroll Subject to NIC Information',
'Benefits Taxed Through Payroll NOT Subject to NIC Information',
'Gross Pay', 'Total Deductions', 'NI Employer',
'Total Direct Payments','SSP Total',
'SMP Total','SPP Adoption Total', 'SAP Total',
'ASPP Adoption Total','ASPP Birth Total','Pre Tax Deductions'
,'NIable Deductions Free of Tax'))
/*
OR(d.database_item_suffix = '_ASG_PROC_PTD'
AND b.balance_name IN ('Court Order','Court Order Non Priority'))
*/
OR(d.database_item_suffix = '_PER_TD_YTD'
AND b.balance_name IN ('Taxable Pay', 'Student Loan', 'NI Employer', 'Earnings Free of Tax',
'Earnings Free of NI', 'SSP Total', 'SMP Total', 'SPP Adoption Total',
'SPP Birth Total', 'SAP Total', 'ASPP Adoption Total', 'ASPP Birth Total'))
OR(d.database_item_suffix = '_PER_TD_CPE_YTD'
AND b.balance_name IN ('PAYE')))
AND b.legislation_code = 'GB'
AND d.legislation_code = 'GB'
AND db.legislation_code = 'GB';
SELECT b.balance_name balance_name,
b.balance_type_id balance_type_id,
d.database_item_suffix database_item_suffix,
d.balance_dimension_id balance_dimension_id,
db.defined_balance_id defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND ((d.database_item_suffix = '_PER_TD_YTD'
AND b.balance_name IN ('Taxable Pay','PAYE','Student Loan','SSP Total','SMP Total',
'SPP Adoption Total', 'SAP Total','ASPP Adoption Total',
'Pre Tax Pension Contributions','Post Tax Pension Contributions',
'Earnings Free of Tax','Earnings Free of NI','Earnings Free of Tax and NI',
'Benefits Taxed Through Payroll Subject to NIC Earnings',
'Benefits Taxed Through Payroll NOT Subject to NIC Earnings',
'Benefits Taxed Through Payroll Subject to NIC Information',
'Benefits Taxed Through Payroll NOT Subject to NIC Information','Total Direct Payments',
'SPP Birth Total','ASPP Birth Total'))
OR(d.database_item_suffix = '_ASG_RUN'
AND b.balance_name IN ('Taxable Pay', 'NIC able Benefits','Student Loan', 'PAYE',
'Pre Tax Pension Contributions','Post Tax Pension Contributions',
'Earnings Free of Tax','Earnings Free of NI','Earnings Free of Tax and NI',
'Benefits Taxed Through Payroll Subject to NIC Earnings',
'Benefits Taxed Through Payroll NOT Subject to NIC Earnings',
'Benefits Taxed Through Payroll Subject to NIC Information',
'Benefits Taxed Through Payroll NOT Subject to NIC Information',
'Gross Pay', 'Total Deductions','Total Direct Payments','Pre Tax Deductions'
,'NIable Deductions Free of Tax'))
/* OR(d.database_item_suffix = '_ASG_PROC_PTD'
AND b.balance_name IN ('Court Order','Court Order Non Priority'))
*/
OR(d.database_item_suffix = '_PER_TD_CPE_YTD'
AND b.balance_name IN ('PAYE'))
)
and b.legislation_code = 'GB'
and d.legislation_code = 'GB'
and db.legislation_code = 'GB';
SELECT max (ppa2.effective_date)
FROM pay_payroll_actions ppa2
WHERE ppa2.report_qualifier = 'GB'
AND ppa2.report_type = 'RTI_FPS_REP'
AND ppa2.action_status = 'C'
AND substr (pay_gb_eoy_archive.get_parameter (ppa2.legislative_parameters,'FIRST_FPS'),1,20) = 'Y'
AND substr (pay_gb_eoy_archive.get_parameter (ppa2.legislative_parameters,'PAYROLL_ID'),1,20) = c_payroll_id; --'4064' --'4065' --ppa1.payroll_id
SELECT distinct element_type_id
INTO g_paye_element_id
FROM pay_element_types_f
WHERE element_name = 'PAYE'
AND legislation_code = 'GB';
SELECT distinct element_type_id
INTO g_paye_details_id
FROM pay_element_types_f
WHERE element_name = 'PAYE Details'
AND legislation_code = 'GB';
SELECT distinct element_type_id
INTO g_adv_period_id
FROM pay_element_types_f
WHERE element_name = 'Advance Period'
AND legislation_code = 'GB';
select pdb.defined_balance_id from pay_balance_types pbt, pay_balance_dimensions pbd,
pay_defined_balances pdb where
pbt.balance_name = c_bal_name
and pbd.database_item_suffix = c_dim_name
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.LEGISLATION_CODE = 'GB'
and pbt.LEGISLATION_CODE = 'GB';
SELECT asg.EFFECTIVE_START_DATE asg_eff_start_date,
asg.person_id
FROM per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND g_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT
to_number (substr (max (lpad (paa.action_sequence ,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id
, max(pact.effective_date) effective_date
FROM pay_assignment_actions paa
,pay_payroll_actions pact
,per_time_periods ptp
WHERE paa.assignment_id = c_asg_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN g_start_year
AND g_end_year
AND ptp.regular_payment_date <= g_effective_date;
SELECT
to_number (substr (max (lpad (paa.action_sequence ,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id
, max(pact.effective_date) effective_date
FROM pay_assignment_actions paa
,pay_payroll_actions pact
,per_time_periods ptp
WHERE paa.assignment_id = c_asg_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN g_start_year
AND g_end_year;
SELECT ptp.regular_payment_date payment_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
WHERE assignment_action_id = p_last_asg_action_id
AND ppa.time_period_id = ptp.time_period_id
AND ppa.payroll_id = ptp.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id;
select min(paa2.assignment_action_id) asgactid
from
pay_assignment_actions paa1,
pay_assignment_actions paa2,
per_all_assignments_f asg
where
paa1.assignment_action_id = p_assactid
and paa1.payroll_action_id = paa2.payroll_action_id
and paa2.assignment_id = asg.assignment_id
and asg.person_id = p_person_id ;
SELECT asg.assignment_id,
asg.EFFECTIVE_START_DATE asg_eff_start_date,
asg.person_id,
asg.assignment_number
FROM per_all_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = c_asg_act_id
AND paa.assignment_id = asg.assignment_id
AND ppa.payroll_action_id = g_pre_pact_id
AND ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT trim(NVL(asg.primary_flag,'N')) asg_primary_flag,
trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
trim(NVL(pap.per_information10,'N')) per_paye_agg_flag,
asg.payroll_id payroll_id
FROM per_all_people_f pap,
per_all_assignments_f asg,
pay_assignment_actions paa
WHERE paa.assignment_action_id = c_asg_act_id
AND paa.assignment_id = asg.assignment_id
AND pap.person_id = asg.person_id
-- and asg.business_group_id = l_business_group_id
AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND pap.per_information_category = 'GB';
SELECT
/*+ USE_NL(paa, pact, ptp) */
to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id,
MAX(pact.effective_date) effective_date
FROM pay_assignment_actions paa,
pay_payroll_actions pact,
per_time_periods ptp,
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
WHERE paa.assignment_id = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','I','V','B')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year AND p_end_year
AND ppa1.payroll_action_id = p_pre_pay_id --1160712
AND ppa1.action_type IN ('P','U')
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = pai.locked_action_id;
SELECT
/*+ USE_NL(paa, pact, ptp) */
to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id,
MAX(pact.effective_date) effective_date
FROM pay_assignment_actions paa,
pay_payroll_actions pact,
per_time_periods ptp,
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
WHERE paa.assignment_id = p_asgid --46959
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','I','V','B')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year AND p_end_year
AND ppa1.payroll_action_id = p_pre_pay_id --1160712
AND ppa1.action_type IN ('P','U')
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = pai.locked_action_id
GROUP BY paa.payroll_action_id
ORDER BY max_asg_act_id;
SELECT paa.assignment_action_id,
ppa.payroll_action_id,
ppa.effective_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_asgid
AND ppa.payroll_action_id = p_pre_pay_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT paa.payroll_action_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_asg_actid;
SELECT DISTINCT fps_asg_act_id
FROM pay_gb_fps_details
WHERE payroll_asg_act_id = p_last_asg_action_id;
SELECT action_context_id,
action_context_type,
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,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25,
action_information26,
action_information27,
action_information28,
action_information29,
action_information30,
effective_date,
assignment_id
FROM pay_action_information pai
WHERE pai.action_context_id = p_assignment_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = p_act_info_cat;
SELECT COUNT(*)
FROM pay_action_information pai
WHERE pai.action_context_id = p_assignment_action_id;
SELECT 'Y'
FROM pay_pre_payments ppp,
pay_payment_types ppt,
pay_org_payment_methods_f popf,
pay_personal_payment_methods_f pppf,
pay_external_accounts Orgkey,
pay_external_accounts deskey,
PAY_ASSIGNMENT_ACTIONS PAA,
pay_payroll_actions ppa
WHERE popf.ORG_PAYMENT_METHOD_ID = ppp.ORG_PAYMENT_METHOD_ID
AND pppf.personal_payment_method_ID = ppp.personal_payment_method_ID
AND popf.PAYMENT_TYPE_ID = ppt.PAYMENT_TYPE_ID
AND ppt.PAYMENT_TYPE_NAME = 'BACS Tape'
AND popf.defined_balance_id is not null
AND orgkey.external_account_id = popf.external_account_id
AND deskey.external_account_id = pppf.external_account_id
AND ppa.effective_date BETWEEN popf.effective_start_date AND popf.effective_end_date
AND ppa.effective_date BETWEEN pppf.effective_start_date AND pppf.effective_end_date
AND ppp.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND ppa.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.PAYROLL_ACTION_ID = c_prepay_payroll_action_id --1129781
AND PAA.ASSIGNMENT_ACTION_ID = c_prepay_asg_action_id; --9849423
select (sum(ppp.value) * 100) amount
from pay_pre_payments ppp,
pay_org_payment_methods_f popmf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppp.ASSIGNMENT_ACTION_ID = c_prepay_asg_action_id
and popmf.ORG_PAYMENT_METHOD_ID = ppp.ORG_PAYMENT_METHOD_ID
and popmf.defined_balance_id is not null
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = ppp.assignment_action_id;
select (sum(ppp.value) * 100) amount
from pay_pre_payments ppp
where ASSIGNMENT_ACTION_ID = c_prepay_asg_action_id;
SELECT
/*+ USE_NL(paa, pact, ptp) */
to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id,
MAX(pact.effective_date) effective_date
FROM pay_assignment_actions paa,
per_all_assignments_f paaf,
pay_payroll_actions pact,
per_time_periods ptp,
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
WHERE paa.assignment_id = paaf.assignment_id
AND paaf.person_id = p_person_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN NVL(p_asg_start, p_start_year) AND p_end_year
AND ppa1.payroll_action_id = p_pre_pay_id --1160712
AND ppa1.action_type IN ('P','U')
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND exists( select 1
from pay_pre_payments ppp
where ASSIGNMENT_ACTION_ID = paa1.assignment_action_id
and ppp.value <> 0); --For the bug 16542698
SELECT /*+ USE_NL(paa, pact, ptp) */
to_number (substr (max (lpad (paa.action_sequence ,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id
,max (pact.effective_date) effective_date
FROM pay_assignment_actions paa
,per_all_assignments_f paaf
,pay_payroll_actions pact
,per_time_periods ptp
WHERE paa.assignment_id = paaf.assignment_id
AND paaf.person_id = p_person_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year
AND p_end_year
AND (
(
paa.assignment_action_id >
(
SELECT nvl (max (payroll_asg_act_id),paa.assignment_action_id)
FROM pay_gb_fps_details pgfd
WHERE pgfd.person_id = p_person_id
)
AND source_action_id IS NULL
)
OR (
(
SELECT count (*)
FROM pay_gb_fps_details pgfd
WHERE pgfd.person_id = p_person_id
) = 0
)
)
AND ptp.regular_payment_date >= nvl (g_first_fps_run_date, c_first_fps_eff_date)
AND paa.assignment_action_id <= p_last_asgact_id
GROUP BY paa.payroll_action_id
ORDER BY max_asg_act_id;
SELECT action_sequence
FROM pay_assignment_actions
WHERE assignment_action_id = c_asg_act_id;
select null into dummy
from dual
where not exists
(select null
*/
SELECT min (act.assignment_action_id)
FROM pay_action_classifications class
,pay_payroll_actions pact
,pay_assignment_actions act
,per_all_assignments_f ass
,per_periods_of_service pos
WHERE pos.person_id = p_person_id
AND ass.period_of_service_id = pos.period_of_service_id
AND act.assignment_id = ass.assignment_id
AND act.action_sequence > p_action_sequence
AND act.action_status IN ('C','S','M')
AND act.payroll_action_id = pact.payroll_action_id
AND pact.action_type = class.action_type
AND class.classification_name = 'SEQUENCED';
SELECT 'Y' this_prepayment
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = pai.locking_action_id
AND ppa.action_type in ('P','U')
AND ppa.payroll_action_id = g_pre_pact_id
AND (pai.locked_action_id = c_payroll_asgactid
OR pai.locked_action_id = c_payroll_master_asgactid );
SELECT 'Y' this_prepayment
FROM pay_action_interlocks pai
WHERE locking_action_id = c_prepay_asgactid
AND (locked_action_id = c_payroll_asgactid
OR locked_action_id = c_payroll_master_asgactid );
select distinct assignment_id
from pay_assignment_actions
where assignment_action_id = c_asgactid;
select AEI_INFORMATION1, AEI_INFORMATION2 from per_assignment_extra_info where assignment_id = c_asg_id
and AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION';
select
paei.assignment_id , fnd_date.canonical_to_date(paei.AEI_INFORMATION2)
from per_all_assignments_f paaf1, per_all_assignments_f paaf2 ,per_assignment_extra_info paei
where paaf1.assignment_id = c_asg_id
and paaf1.person_id = paaf2.person_id
and paaf2.ASSIGNMENT_TYPE = 'E'
and paei.assignment_id = paaf2.assignment_id
and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
and paei. AEI_INFORMATION1 = 'Y'
and p_effective_date between paaf1.effective_start_date and paaf1.effective_end_date
and p_effective_date between paaf2.effective_start_date and paaf2.effective_end_date
order by 2 desc;
select
assignment_id
,null
,null
,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
,ACTION_INFORMATION12
,ACTION_INFORMATION13
,ACTION_INFORMATION14
,ACTION_INFORMATION15
,ACTION_INFORMATION16
,ACTION_INFORMATION17
,ACTION_INFORMATION18
,ACTION_INFORMATION19
,ACTION_INFORMATION20
,ACTION_INFORMATION21
,ACTION_INFORMATION22
,ACTION_INFORMATION23
,ACTION_INFORMATION24
,ACTION_INFORMATION25
,ACTION_INFORMATION26
,ACTION_INFORMATION27
,ACTION_INFORMATION28
,ACTION_INFORMATION29
,ACTION_INFORMATION30
from pay_action_information where action_context_id = (select max(FPS_ASG_ACT_ID) from pay_gb_fps_details where assignment_id = c_asg_id
and FPS_PAY_ACT_ID <> g_payroll_action_id) --= 9789749
and ACTION_CONTEXT_TYPE = 'AAP' and
ACTION_INFORMATION_CATEGORY in( 'GB_RTI_FPS_ASG_DET1', 'GB_RTI_FPS_ASG_DET2');
select max(paa.assignment_action_id) asg_act_id from per_all_assignments_f paaf,
per_all_assignments_f paaf1,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paaf.assignment_id = c_asg_id
and paaf1.person_id = paaf.person_id
and paaf1.assignment_id <> c_asg_id
and paaf1.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.effective_date >= g_start_year
and ppa.action_type IN ('Q','R')
and paa.action_status IN ('C','S')
group by paa.assignment_id;
SELECT paa_pre.assignment_id
FROM pay_assignment_actions paa_pre
, pay_assignment_actions paa_arcv
WHERE paa_arcv.assignment_action_id = p_assactid
AND paa_pre.payroll_action_id = pre_pay_id
AND paa_pre.assignment_id = paa_arcv.assignment_id;
SELECT paa.assignment_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = c_asg_act_id;
SELECT substr (pay_gb_eoy_archive.get_parameter (legislative_parameters, 'FIRST_FPS'), 1,1) first_fps,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
FROM pay_payroll_actions pact
, pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assactid
AND pact.payroll_action_id = paa.payroll_action_id;
SELECT defined_balance_id
FROM pay_defined_balances db
, pay_balance_types b
, pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension_name
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND b.legislation_code = 'GB'
AND d.legislation_code = 'GB';
SELECT /*+ USE_NL(paa, pact, ptp) */
to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| paa.assignment_action_id), 16))
, max (pact.effective_date) effective_date
FROM pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp
WHERE paa.assignment_id = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.payroll_id = l_payroll
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN g_start_year
AND g_end_year;
SELECT /*+ USE_NL(paa, pact, ptp) */
to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| paa.assignment_action_id), 16))
, max (pact.effective_date) effective_date
FROM pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp
WHERE paa.assignment_id = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN g_start_year
AND g_end_year;
SELECT assignment_number
FROM per_all_assignments_f
WHERE assignment_id = p_asg_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
ORDER BY effective_start_date;
SELECT assignment_number
FROM per_all_assignments_f
WHERE assignment_id = p_asg_id
ORDER BY effective_start_date;
select count(*)
--into l_asg_act_processed_earlier
from PAY_GB_FPS_DETAILS
where PERSON_ID = c_person_id
and ASSIGNMENT_ID = c_asg_id
and PAYROLL_ASG_ACT_ID = c_cur_asg_action_id;
select count(*) from pay_assignment_actions paa, --run
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
where paa.assignment_action_id = c_assignment_action_id --9917796
AND paa.assignment_action_id = pai.locked_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.action_type IN ('P','U');
SELECT count(distinct act.assignment_action_id),
min(act.assignment_action_id) assignment_action_id
FROM pay_payroll_actions ppa, --Current pactid
pay_payroll_actions appa, --Payroll Run
pay_payroll_actions appa2, --Prepayments
pay_assignment_actions act, --Payroll Run
pay_assignment_actions act1, --Prepayments
pay_action_interlocks pai, --Prepayments
per_all_assignments_f as1
WHERE as1.person_id = p_person_id --45885
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
AND as1.assignment_id = act.assignment_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND appa2.payroll_action_id = p_pre_pact_id;
SELECT max(act.assignment_action_id) assignment_action_id
FROM pay_payroll_actions ppa, --Current pactid
pay_payroll_actions appa, --Payroll Run
pay_payroll_actions appa2, --Prepayments
pay_assignment_actions act, --Payroll Run
pay_assignment_actions act1, --Prepayments
pay_action_interlocks pai, --Prepayments
per_all_assignments_f as1
WHERE as1.person_id = p_person_id --45885
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
AND as1.assignment_id = act.assignment_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND appa2.payroll_action_id = p_pre_pact_id;
SELECT act.assignment_id assignment_id,
max(act.assignment_action_id) asg_act_id
FROM
pay_payroll_actions ppa, --Current pactid
pay_payroll_actions appa, --Payroll Run
pay_payroll_actions appa2, --Prepayments
pay_assignment_actions act, --Payroll Run
pay_assignment_actions act1, --Prepayments
pay_action_interlocks pai, --Prepayments
per_all_assignments_f as1
WHERE
ppa.payroll_action_id = g_payroll_action_id
AND as1.person_id = c_person_id --BETWEEN stperson AND endperson
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
AND as1.assignment_id = act.assignment_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND appa2.payroll_action_id = g_pre_pact_id
group by act.assignment_id
ORDER BY act.assignment_id;
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE source_action_id = c_child_asg_act_id;
SELECT count(distinct act.assignment_action_id),
max(act.assignment_action_id) assignment_action_id
FROM pay_payroll_actions ppa, --Current pactid
pay_payroll_actions appa, --Payroll Run
pay_payroll_actions appa2, --Prepayments
pay_assignment_actions act, --Payroll Run
pay_assignment_actions act1, --Prepayments
pay_action_interlocks pai, --Prepayments
per_all_assignments_f as1
WHERE as1.person_id = p_person_id --45885
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
AND as1.assignment_id = act.assignment_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND appa2.payroll_action_id = p_pre_pact_id
AND exists( select 1
from pay_pre_payments ppp
where ASSIGNMENT_ACTION_ID = act1.assignment_action_id
and ppp.value <> 0); --For the bug 16542698
select pai.locking_action_id
from
pay_action_interlocks pai,
pay_payroll_actions ppa,
pay_assignment_actions paa
where pai.locked_action_id = c_cur_asg_action_id
and ppa.action_type in ('P','U')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = pai.locking_action_id;
select 'Y' from fnd_concurrent_programs fcp,
fnd_concurrent_requests fcr,
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assactid
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.request_id = fcr.request_id
and fcr.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
and CONCURRENT_PROGRAM_NAME = 'RETRY'; */
select 'Y' from
pay_assignment_actions
where assignment_action_id = p_assactid
AND ACTION_STATUS = 'M';
select paa.payroll_action_id
from
pay_assignment_actions paa
where paa.assignment_action_id = c_asg_act_id;
select 'Y' from pay_assignment_actions paa,
pay_payroll_actions ppa
where
ppa.payroll_action_id = c_pre_pay_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = c_ni_rpt_asg_id
and ppa.action_type IN ('P','U')
and paa.action_status IN ('C','S');
SELECT
to_number (substr (max (lpad (paa.action_sequence ,15,'0')
|| paa.assignment_action_id),16)) max_asg_act_id
, max(pact.effective_date) effective_date
FROM pay_assignment_actions paa
,pay_payroll_actions pact
,per_time_periods ptp
WHERE paa.assignment_id = c_asg_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN g_start_year
AND g_end_year
AND ptp.regular_payment_date <= g_effective_date;
select distinct assignment_id
from pay_assignment_actions
where assignment_action_id = c_last_asgact_id;
SELECT count(distinct paa1.assignment_id)
FROM pay_assignment_actions paa,
per_all_assignments_f paaf,
pay_payroll_actions pact,
per_time_periods ptp,
pay_assignment_actions paa1, --Prepayments
pay_payroll_actions ppa1, --Prepayments
pay_action_interlocks pai --Prepayments
WHERE paa.assignment_id = paaf.assignment_id
AND paaf.person_id = p_person_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status IN ('C','S')
AND ptp.regular_payment_date BETWEEN p_start_year AND p_end_year
AND ppa1.payroll_action_id = p_pre_pay_id
AND ppa1.action_type IN ('P','U')
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_action_id = pai.locking_action_id
AND paa.assignment_action_id = pai.locked_action_id;
SELECT fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS_DATE'),1,10)) first_fps_date,
to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id
FROM pay_payroll_actions pact,
pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assactid
AND pact.payroll_action_id = paa.payroll_action_id;
SELECT /*+ USE_NL(paa, pact, ptp) */
to_number (substr (max (lpad (paa.action_sequence, 15
, '0')
|| paa.assignment_action_id), 16))
, max (pact.effective_date) effective_date
FROM per_all_assignments_f paaf,
pay_assignment_actions paa
, pay_payroll_actions pact
, per_time_periods ptp
WHERE paa.assignment_id = paaf.assignment_id
and paaf.person_id = person_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q', 'R', 'B'
, 'I', 'V')
AND paa.action_status IN ('C', 'S')
AND ptp.regular_payment_date
BETWEEN g_start_year
AND l_first_fps_eff_date
AND paaf.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paaf.assignment_id
and asg2.assignment_type = 'E'
and asg2.effective_start_date <= l_prepayment_date
);
SELECT asg.assignment_number
FROM per_all_assignments_f asg,
pay_assignment_actions paa
WHERE paa.assignment_action_id = c_asg_act_id
AND paa.assignment_id = asg.assignment_id
AND c_cur_last_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT assignment_extra_info_id l_aei_id,
aei_information8 starter_flag,
aei_information9 pensioner_flag,
aei_information19 expat_flag,
object_version_number l_ovn
FROM per_assignment_extra_info
WHERE assignment_id = p_asg_id
AND information_type = 'GB_RTI_ASG_DETAILS';
select count(*)
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_asg_id
and paa.action_status ='C'
and paa.assignment_action_id <> p_assactid
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type in ( 'RTI_FPS_REP' , 'RTI_FPS_REP_13');
select 'Y'
from pay_assignment_actions paa
where paa.payroll_action_id = c_payroll_action_id
and paa.assignment_id = c_cur_asg_id;
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_new_starter.l_ovn, p_assignment_extra_info_id => l_new_starter.l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information8 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_new_starter.l_ovn, p_assignment_extra_info_id => l_new_starter.l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information9 => 'N');
hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_new_starter.l_ovn, p_assignment_extra_info_id => l_new_starter.l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
p_aei_information19 => 'N');
select payroll_action_id into l_pact_id
from pay_assignment_actions where assignment_action_id = p_assactid;
delete from pay_gb_fps_details
where person_ID = l_person_id
and FPS_PAY_ACT_ID = l_pact_id;
insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
INSERT
INTO PAY_GB_FPS_DETAILS
(
ASSIGNMENT_ID,
PERSON_ID,
FPS_ASG_ACT_ID,
FPS_PAY_ACT_ID,
FPS_EFFECTIVE_DATE,
PREPAY_ASG_ACT_ID,
PREPAY_PAY_ACT_ID,
PREPAY_EFFECTIVE_DATE,
PAYROLL_ASG_ACT_ID,
PAYROLL_PAY_ACT_ID,
PAYROLL_EFFECTIVE_DATE
)
VALUES
(
l_asg_id,
l_person_id,
p_assactid,
g_payroll_action_id,
g_fps_effective_date,
l_prepay_asg_action_id,
l_prepay_payroll_action_id,
l_prepay_effective_date,
l_last_asg_action_id,
l_payroll_pact_id,
l_last_effective_date
);
insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
select max(paa.assignment_action_id) into l_asg_action_id
from
pay_assignment_actions paa,
per_all_assignments_f paaf
where
paaf.person_id = l_person_id
and paaf.assignment_id = paa.assignment_id
and paa.payroll_action_id = g_payroll_action_id
and paa.assignment_id = l_last_asg_id;
select ppa.payroll_action_id into l_oth_min_pre_pay_id from
pay_action_interlocks pai,
pay_payroll_actions ppa,
pay_assignment_actions paa
where
pai.locked_action_id = l_cur_asg_action_id
and pai.locking_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('P','U');
select paa.assignment_action_id into l_assact_id from pay_assignment_actions paa
where paa.payroll_action_id = g_payroll_action_id
and paa.assignment_id = l_cur_asg_id;
select count(*)
into l_rec_count
from PAY_GB_FPS_DETAILS
where ASSIGNMENT_ID = l_asg_id
and PERSON_ID = l_person_id
and PAYROLL_ASG_ACT_ID = l_cur_asg_action_id;
hr_utility.set_location('1 inserting',999);
hr_utility.set_location('1 inserting',999);
INSERT
INTO PAY_GB_FPS_DETAILS
(
ASSIGNMENT_ID,
PERSON_ID,
FPS_ASG_ACT_ID,
FPS_PAY_ACT_ID,
FPS_EFFECTIVE_DATE,
PREPAY_ASG_ACT_ID,
PREPAY_PAY_ACT_ID,
PREPAY_EFFECTIVE_DATE,
PAYROLL_ASG_ACT_ID,
PAYROLL_PAY_ACT_ID,
PAYROLL_EFFECTIVE_DATE
)
VALUES
(
l_cur_asg_id,
l_person_id,
l_assact_id,
g_payroll_action_id,
g_fps_effective_date,
l_cur_asg_pre_pay_act_id,
l_cur_prepay_payroll_action_id,
l_prepay_effective_date,
l_cur_asg_action_id,
l_payroll_pact_id,
l_last_effective_date
);
select count(*)
into l_rec_count
from PAY_GB_FPS_DETAILS
where ASSIGNMENT_ID = l_cur_asg_id
and PERSON_ID = l_person_id
and PAYROLL_ASG_ACT_ID = l_cur_asg_action_id;
hr_utility.set_location('1 inserting',999);
hr_utility.set_location('1 inserting',999);
INSERT
INTO PAY_GB_FPS_DETAILS
(
ASSIGNMENT_ID,
PERSON_ID,
FPS_ASG_ACT_ID,
FPS_PAY_ACT_ID,
FPS_EFFECTIVE_DATE,
PREPAY_ASG_ACT_ID,
PREPAY_PAY_ACT_ID,
PREPAY_EFFECTIVE_DATE,
PAYROLL_ASG_ACT_ID,
PAYROLL_PAY_ACT_ID,
PAYROLL_EFFECTIVE_DATE
)
VALUES
(
l_cur_asg_id,
l_person_id,
l_assact_id,
g_payroll_action_id,
g_fps_effective_date,
l_cur_asg_pre_pay_act_id,
l_prepay_payroll_action_id,
l_prepay_effective_date,
l_cur_asg_action_id,
l_payroll_pact_id,
l_last_effective_date
);
insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
insert_archive_row_agg(p_assactid, p_effective_date,l_archive_tab_det1);
insert_archive_row_agg(p_assactid, p_effective_date,l_archive_tab_det2);
insert_archive_row_agg(p_assactid, p_effective_date,l_archive_tab_ni_det);
insert_archive_row_agg(p_assactid, p_effective_date,l_archive_tab_ni_det_1);
/*select count(*)
into l_rec_count
from PAY_GB_FPS_DETAILS
where ASSIGNMENT_ID = l_asg_id
and PERSON_ID = l_person_id
and PAYROLL_ASG_ACT_ID = l_last_asg_action_id;
hr_utility.set_location('2 inserting',999);
hr_utility.set_location('2 inserting',999);
INSERT
INTO PAY_GB_FPS_DETAILS
(
ASSIGNMENT_ID,
PERSON_ID,
FPS_ASG_ACT_ID,
FPS_PAY_ACT_ID,
FPS_EFFECTIVE_DATE,
PREPAY_ASG_ACT_ID,
PREPAY_PAY_ACT_ID,
PREPAY_EFFECTIVE_DATE,
PAYROLL_ASG_ACT_ID,
PAYROLL_PAY_ACT_ID,
PAYROLL_EFFECTIVE_DATE
)
VALUES
(
l_asg_id,
l_person_id,
p_assactid,
g_payroll_action_id,
g_fps_effective_date,
l_prepay_asg_action_id,
l_prepay_payroll_action_id,
l_prepay_effective_date,
l_last_asg_action_id,
l_payroll_pact_id,
l_last_effective_date
);
INSERT
INTO PAY_GB_FPS_DETAILS
(
ASSIGNMENT_ID,
PERSON_ID,
FPS_ASG_ACT_ID,
FPS_PAY_ACT_ID,
FPS_EFFECTIVE_DATE,
PREPAY_ASG_ACT_ID,
PREPAY_PAY_ACT_ID,
PREPAY_EFFECTIVE_DATE,
PAYROLL_ASG_ACT_ID,
PAYROLL_PAY_ACT_ID,
PAYROLL_EFFECTIVE_DATE
)
VALUES
(
l_asg_id,
l_person_id,
p_assactid,
g_payroll_action_id,
g_fps_effective_date,
l_prepay_asg_action_id,
l_prepay_payroll_action_id,
l_prepay_effective_date,
l_last_asg_action_id,
l_payroll_pact_id,
l_last_effective_date
);
insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
select paa.assignment_action_id into l_ni_rpt_assact_id from pay_assignment_actions paa
where paa.payroll_action_id = g_payroll_action_id
and paa.assignment_id = l_ni_rpt_asg_id;
select * from pay_payroll_actions ppa,
get_ni_only_asg_details(l_ni_rpt_asg_id,l_archive_tab_ni_rpt,
end if; -- end if for l_archive_tab_ni_rpt.count > 0
select paa.assignment_action_id into l_ni_rpt_assact_id from pay_assignment_actions paa
where paa.payroll_action_id = g_payroll_action_id
and paa.assignment_id = l_ni_rpt_asg_id;
insert_archive_row_agg(p_assactid, p_effective_date,l_archive_tab_ni_rpt);
INSERT
INTO PAY_GB_FPS_DETAILS
(
ASSIGNMENT_ID,
PERSON_ID,
FPS_ASG_ACT_ID,
FPS_PAY_ACT_ID,
FPS_EFFECTIVE_DATE,
PREPAY_ASG_ACT_ID,
PREPAY_PAY_ACT_ID,
PREPAY_EFFECTIVE_DATE,
PAYROLL_ASG_ACT_ID,
PAYROLL_PAY_ACT_ID,
PAYROLL_EFFECTIVE_DATE
)
VALUES
(
l_ni_rpt_asg_id,
l_person_id,
l_ni_rpt_assact_id,
g_payroll_action_id,
g_fps_effective_date,
l_prepay_asg_action_id,
l_prepay_payroll_action_id,
l_prepay_effective_date,
l_last_asg_action_id,
l_payroll_pact_id,
l_last_effective_date
);
insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
SELECT report_type
FROM pay_payroll_actions pact
WHERE pact.payroll_action_id = pactid;
SELECT NVL(UPPER(hoi.org_information11),' ') sender_id,
DECODE(SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
fnd_number.number_to_canonical(pact.request_id) request_id,
NVL(upper(SUBSTR(ltrim(hoi.org_information3),1,35)),' ') employer_name,
NVL(upper(SUBSTR(ltrim(SUBSTR(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
lpad(SUBSTR(hoi.ORG_INFORMATION1,0,instr(hoi.ORG_INFORMATION1,'/')-1),3,0) tax_office_no,
DECODE(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
(lpad(SUBSTR(hoi.ORG_INFORMATION6,0,instr(hoi.ORG_INFORMATION6,'P')-1),3,0)
|| 'P'
|| SUBSTR(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P') +1,1)
|| lpad(SUBSTR(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P')+2,LENGTH(hoi.ORG_INFORMATION6)-3-(instr(hoi.ORG_INFORMATION6,'P')-1)),7,0)
|| SUBSTR(hoi.ORG_INFORMATION6,LENGTH(hoi.ORG_INFORMATION6),1) ) AS acc_ref_no,
hoi.org_information7 econ_number,
pact.business_group_id bus_grp_id,
pact.action_parameter_group_id act_param_grp_id,
org_information19 service_company
FROM pay_payroll_actions pact,
hr_organization_information hoi
WHERE pact.payroll_action_id =pactid
AND pact.business_group_id = hoi.organization_id
AND hoi.org_information_context = 'Tax Details References'
AND NVL(hoi.org_information10,'UK') = 'UK'
AND SUBSTR(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8, instr(pact.legislative_parameters
||' ',' ', instr(pact.legislative_parameters,'TAX_REF=') +8) - instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
SELECT effective_date,
business_group_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TAX_REF'),1,3) tax_dist,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'SPLIT'),1,20) split,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PRE_PAY_ID'),1,20) pre_pay_id,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIRST_FPS'),1,20) first_fps,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'FIN_SUB'),1,20) final_submission,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_PMT_MADE'),1,20) free_tax_payments_made,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'THIRD_PARTY'),1,20) pay_to_third_party,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EXP_BEN_OTHER'),1,20) expenses_benefits_others,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'OUTSIDE_UK'),1,20) employed_outside_uk,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'P11D_DUE'),1,20) p11d_due,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'HMRC_ADVAN'),1,20) hmrc_advance_received,
SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'CIS_DEDUCT'),1,20) cis_deductions,
substr(legislative_parameters,instr(legislative_parameters,'VERSION=')+8) version
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT legislative_parameters para,
fnd_number.number_to_canonical(request_id) control_id,
report_type,
business_group_id
FROM pay_payroll_actions pact
WHERE payroll_action_id = pactid;
SELECT NVL(hoi.org_information11,' ') sender_id,
NVL(upper(hoi.org_information2),' ') hrmc_office,
NVL(upper(hoi.org_information4),' ') er_addr,
NVL(upper(hoi.org_information3),' ') er_name
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_bus_id
AND hoi.org_information_context = 'Tax Details References'
AND NVL(hoi.org_information10,'UK') = 'UK'
AND upper(hoi.org_information1) = upper(p_tax_ref);
SELECT ppf.PAYROLL_NAME , ppapre.EFFECTIVE_DATE
from pay_payrolls_f ppf,pay_payroll_actions ppapre,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid
and ppf.payroll_id = SUBSTR(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'PAYROLL_ID'),1,20)
and ppapre.payroll_action_id = SUBSTR(pay_gb_eoy_archive.get_parameter(ppa.legislative_parameters,'PRE_PAY_ID'),1,20);
SELECT DISTINCT paa.assignment_action_id asg_action_id,
assignment_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = pactid -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'E';
SELECT DISTINCT line_text
FROM pay_message_lines
WHERE source_id = asg_action_id --9919881 --asg_action_id
AND message_level <> 'W'-- p_message_level
AND line_text like '%Pre payment is not run%'
AND payroll_id = 100;
SELECT DISTINCT paa.assignment_action_id asg_action_id,
assignment_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = pactid -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C';
SELECT DISTINCT paa.assignment_action_id asg_action_id,
assignment_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = pactid -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'E';
SELECT DISTINCT pap.first_name f_name ,
pap.middle_names m_name,
pap.last_name l_name,
pap.title title,
paa.assignment_number emp_no,
NVL(pap.national_identifier,' ')ni_no,
NVL(pap.employee_number,' ') employee_number,
TO_CHAR(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
pap.person_id
FROM per_all_assignments_f paa,
per_assignment_status_types past,
per_all_people_f pap
WHERE paa.person_id = pap.person_id
AND paa.assignment_id = c_assignment_id
AND past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') -- Added for Bug#13626488
and pap.effective_start_date =
( select max(pap2.effective_start_date) from
per_all_people_f pap2
where pap2.person_id = pap.person_id
and pap2.effective_start_date <= l_effective_date
)
and paa.effective_start_date =
( select max(asg2.effective_start_date)
from per_all_assignments_f asg2
where asg2.assignment_id = paa.assignment_id
and asg2.assignment_type = 'E'
and ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
and asg2.effective_start_date <= l_effective_date
)
ORDER BY end_date DESC;
SELECT DISTINCT line_text
FROM pay_message_lines
WHERE source_id = asg_action_id
AND message_level <> 'W'-- p_message_level
AND payroll_id = 100;
SELECT DISTINCT line_text
FROM pay_message_lines
WHERE source_id = asg_action_id
AND message_level = 'W'
AND payroll_id = 100;
select EFFECTIVE_DATE from pay_payroll_actions where payroll_action_id = p_pre_pact_id;
SELECT LINE_TEXT into err_msg FROM pay_message_lines WHERE source_id = action_id.asg_action_id and rownum <=1;
SELECT LINE_TEXT into err_msg FROM pay_message_lines WHERE source_id = action_id.asg_action_id
AND MESSAGE_LEVEL = 'W' and rownum <=1;
SELECT COUNT(DISTINCT(paaf.person_id))
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paaf
WHERE ppa.payroll_action_id = pactid -- pact_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paaf.assignment_id = paa.assignment_id;
delete from pay_action_information pai
where pai.action_context_id = pactid
and pai.action_context_type = 'PA'
and pai.action_information_category in ('RTI PAYROLL INFO');
delete from pay_action_information pai
where pai.action_context_id = pactid
and pai.action_context_type = 'PA'
and pai.action_information_category in ('GB_RTI_FPS_EXTRA_DET');