[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Updated csr_range_format_param to get
value for RANGE_PERSON_ID parameter only.
Changed c_act cursor in get_ff_data to also
fetch payroll actions that are after end of
the tax year in which assignemnt has been
terminated.
Updated get_p45_agg_asg_action_id to add
another check to return only those P45
action which belongs to an assignment that
exists in the aggregation period and is
terminated on same PAYE Ref as the given
assignment.
Updated get_p45_asg_action_id to ignore
transfer P45 actions.
Updated cursor agg_latest_action in
get_ff_data to get aggregated
final action from the aggregation period
only and also added the check to limit
select to aggregated assignments that
share same continuous active period of
employment.
Replaced manual_issue_exists and
p45_existing_action functions with a
call to return_p45_issued_flag function
in arch_act_creation for non-transfer
cases.
Updated csr_person_agg_asg in arcs_act_creation
to fetch only those assignment that share
continuous active period of employment and
exist within the same aggregation period
and replace check to ensure the assignments
that had been on the same PAYE Ref at some
point in time with a check to ensure the same
at the time of termination.
Updated arch_act_creation to archive ids
of included assignments
Updated archive_code to archive final payment
action id.
Updated archive_code to get balances as at
latest action regardless of LSP or final close.
06-NOV-2006 rmakhija 115.64 Bug 5144323, Updated get_p45_asg_action_id to
ignore transfer P45 actions
06-NOV-2006 rmakhija 115.65 Fixed dbdrv line
07-NOV-2006 rmakhija 115.66 Updated aggregation period check subquery in
csr_person_agg_asg cursor
21-NOV-2006 ajeyam 115.67 Code added to check all the transfer payroll
actions when we return P45 payroll action.
In get_p45_asg_action_id procedure.
03-JAN-2007 rmakhija 115.68 Bug 5743581, added to_char to
csr_get_final_payment cursor in
get_p45_agg_asg_action_id
07-Feb-2007 rmakhija 115.69 Bug 5869769, removed sql to get final
payment date in get_p45_agg_asg_action_id
and updated agg_latest_action cursor to
imrove performance.
09-Mar-2007 rmakhija 115.69 Bug 5923552, Changed csr_all_assignments
and csr_all_assignments_range to use
assignment's effective end date instead
of actual termination date to ensure
tax ref transfers are handled correctly
25-Sep-2007 rlingama 115.70 Bug 5671777-2 Update pay_p45_pkg.get_p45_agg_asg_action_id
procedure to first look for a P45 for another assignment
that included the given assignment using
X_P45_INCLUDED_ASSIGNMENT user entity.
Bug 5671777-11 validation added in the p45 process to
ensure it is run for one tax year at a time
16-Nov-2007 parusia 115.71 Archived 2 additional items - X_DATE_OF_BIRTH
and X_SEX. Changed range_cursor to throw unhandled
exception when TestSubmssion is Yes but TestID
is not provided For P45PT1. Bug 6345375.
26-Dec-2007 apmishra 115.72 Added the fix for the first name not to appear with a
space concated with the middle name Bug:6710229
EOY 07-08 P45 PT1: P45PT1 PROCESS ERRORED OUT IF THE MIDDLE NAME IS PROVIDED.
4-Jan-2007 parusia 115.73 Archive middle_name separately from first_name.
Bug 6710229
16-Jan-2007 rlingama 115.75 Modified csr_get_paye_ref to csr_get_p45_another_asg in
get_p45_agg_asg_action_id cursor
28-Feb-2007 pbalu 115.76 Added to_char in csr_get_p45_another_asg in
get_p45_agg_asg_action_id cursor.
31-Mar-2008 rlingama 115.77 Bug 6900025 Modified max effective end date of cursor csr_paye_details to
final process date in get_tax_details procedure to report correct tax code.
01-Apr-2008 rlingama 115.78 Bug 6900025 Added final process date check in get_tax_details procedure
03-Apr-2008 rlingama 115.79 Bug 6900025 Modified pact.effective_date condtion in get_tax_details procedure to
fetch PAYE details from run results instead of element entries.
Reverted the fix did in 115.77 and 78 versions.
02-May-2008 rlingama 115.80 Bug 6900025 modified to_date function to add_months in get_tax_details procedure
14-May-2008 rlingama 115.81 Bug 7028893.Added function PAYE_RETURN_P45_ISSUED_FLAG.
15-Sep-2008 rlingama 115.82 Bug 7410767.Modified the p_eff_date date check in get_tax_detail procedure.
19-Nov-2008 vijranga 115.88 Incorporated the new parameter EDI_VER to modify the already checked in code for
EDI Validations at the Archival level itself. Added one missed 'Tax code missing' validation
and changed the parameters for DOB validation call.
21-Nov-2008 dwkrishn 115.89 Performance fix for Cursor csr_range_assignments.Used Not Exists in place of Outer Join
26-Nov-2008 vijranga 115.90 Incorporated review comments.
27-Nov-2008 vijranga 115.91 Bug #7433580. Incorporated INL team review comments for newly added error messages.
21-OCT-2008 rlingama 115.92 P45 A4 2008-09 Changes.Bug 7261906
07-Jan-2009 namgoyal 115.93 Bug 7281023: modified cursor csr_get_term_period_no to report
correct period number if P45 balance is zero.
08-Jan-2009 namgoyal 115.94 Bug 7281023: Added '=' operator in cursor csr_get_term_period_no.
15-Feb-2009 dwkrishn 115.96 Bug 8254291: Added Procedure populate_run_msg to insert error messages
in pay_message_lines
19-Feb-2009 dwkrishn 115.97 Bug 8254291: Made the process continue even if the process errors
out noting each error EDI error messages, finally error the process.
26-Feb-2009 rlingama 115.98 Bug 8275145 : P45 A4 Laser 4 part changes 2008-09
12-Mar-2009 rlingama 115.99 Bug 8275145 : P45 A4 Continuous report changes 2008-09
03-Apr-2009 dwkrishn 115.100 Bug 8282187 : Few P45 EDI validations commented as it was they
are not to be performed during archive
04-May-2009 jvaradra 115.101 Bug 7601088 Added function PAYE_SYNC_P45_ISSUED_FLAG
14-May-2009 dwkrishn 115.102 Bug 8464343 fetch week month type with payroll id if last action is -9999
08-Jun-2009 dwkrishn 115.103 Bug 8366684 modified agg_latest_action for performance.Removed use_nl
to enable optimizer to choose hash join if needed.
22-06-2009 dwkrishn 115.104 Bug:8566920 Added hints to cursor csr_range_assignments.Issue occured when
DB upgraded from 9i to 10gR2
30-8-2009 dwkrishn 115.105 Bug:8537504 assignment_number validation handled in edi_movded6_asg
16-11-2009 jvaradra 115.106 Bug:9071978 End of time needs to be considered when FPD is NULL
03-11-2009 rlingama 115.107 Bug 9170440 Changed l_printer_style variable declaration.
08-02-2010 rlingama 115.108 Bug 9347169 Modified the code to ensure, fecth address based on the assignment
end date if address not exists on sysdate.
23-01-2010 dwkrishn 115.109 update the payroll_id in the pay_payroll_actions table.
04-03-2010 rlingama 115.110 Bug:8370481 Modifed the Total pay/tax to date and Pay/Tax in this Employment exceeds
999999.99 to 999999999.99.
25-03-2010 dwkrishn 115.111 Bug 9292092 Modified agg_latest_action. Introduced an inline view instead of a
corelated subquery
08-04-2010 pbalu 115.112 Bug:9036584 : Check for Tax code 'NI' is changed to handle payroll runs after
termination also
10-05-2010 pbalu 115.113 Bug 9473697. P45 issued flag is checked even when P45 process errors.
29-06-2010 pbalu 115.114 Bug 9794457. Non aggregated assignment's last payroll action id is not fetched correctly.
03-Jan-2010 npannamp 115.115 Bug 9910054: Modified get_ff_data to skip actions with 0 PAYE for aggregated assignments.
31-may-2011 achandwa 115.116 Bug 10077936: Modified the expression for calculating pay to date value
17-Jan-2012 krreddy 115.117 Bug 13054902 Modified get_ff_data to fetch last asg_act_id only if it has a non-zero value.
05-Jun-2012 achandwa 115.118 Bug 13428461 Modified the cursor csr_transfer to avoid issuing P45 to the employee
whose payroll is not changed but PAYE change happens at payroll level
09-Aug-2012 kvinayku 115.119 Bug 14206931 Modified the cursor csr_all_assignments and csr_all_assignments_range not to
pick up the assignments which are after the effective date
10-Dec-2012 kvinayku 115.120 Bug 15972584 Modified the cursor csr_all_assignments and csr_all_assignments_range to pick
all the assignmentsin a period.
==============================================================================*/
-- Globals
g_package CONSTANT VARCHAR2(20):= 'pay_p45_pkg.';
select fnd_date.canonical_to_date(aei.aei_information4)
from per_assignment_extra_info aei
where aei.assignment_id = c_assignment_id
and aei.information_type = 'GB_P45';
select f.FORMULA_ID
from ff_formulas_f f,
ff_formula_types t
where t.FORMULA_TYPE_ID = f.FORMULA_TYPE_ID
and f.FORMULA_NAME = 'P45';
SELECT peev.screen_entry_value
FROM pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_input_values_f piv,
pay_element_entry_values_f peev
WHERE pee.assignment_id = x_assignment_id
AND upper(pet.element_name) = 'STUDENT LOAN'
AND upper(piv.name) = 'END DATE'
AND pet.business_group_id IS NULL
AND pet.legislation_code = 'GB'
AND pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = peev.input_value_id
AND pee.element_entry_id = peev.element_entry_id
AND x_termination_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND x_termination_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND x_termination_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND x_termination_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND x_termination_date BETWEEN peev.effective_start_date
AND peev.effective_end_date;
cursor c_addr (p_effective_date in date) is select addr.ADDRESS_LINE1,
addr.ADDRESS_LINE2,
addr.ADDRESS_LINE3,
addr.TOWN_OR_CITY,
addr.REGION_1,
addr.COUNTRY,
addr.POSTAL_CODE
from per_addresses addr
where addr.PERSON_ID = X_PERSON_ID
and addr.PRIMARY_FLAG = 'Y'
and p_effective_date between
addr.DATE_FROM and
nvl(addr.DATE_TO,fnd_date.canonical_to_date('4712/12/31'));
select ftv.territory_short_name
from fnd_territories_vl ftv
where ftv.territory_code = p_code;
SELECT substr(hr.meaning,1,30)
INTO X_REGION_1
FROM HR_LOOKUPS hr
WHERE hr.LOOKUP_CODE = l_county
AND hr.LOOKUP_TYPE = 'GB_COUNTY';
SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
pact PAY_PAYROLL_ACTIONS_PK,
r2 PAY_RUN_RESULTS_N50)
USE_NL(assact2, pact, r2) */
to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
r2.run_result_id),17))
FROM pay_assignment_actions assact2,
pay_payroll_actions pact,
pay_run_results r2
WHERE assact2.assignment_id = p_assignment_id
AND r2.element_type_id+0 = l_element_id
AND r2.assignment_action_id = assact2.assignment_action_id
AND r2.status IN ('P', 'PA')
AND pact.payroll_action_id = assact2.payroll_action_id
AND pact.action_type IN ( 'Q','R','B','I')
AND assact2.action_status = 'C'
AND pact.effective_date between
-- Bug 6900025 Modified pact.effective_date condtion to fetch PAYE details from run results instead of element entries.
-- to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(p_eff_date,'YYYY'))),'DD-MM-YYYY')
-- and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(p_eff_date,'YYYY') + 1)),'DD-MM-YYYY')
to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(l_eff_date,'YYYY'))),'DD-MM-YYYY')
and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_eff_date,'YYYY') + 1)),'DD-MM-YYYY')
AND NOT EXISTS(
SELECT '1'
FROM pay_action_interlocks pai,
pay_assignment_actions assact3,
pay_payroll_actions pact3
WHERE pai.locked_action_id = assact2.assignment_action_id
AND pai.locking_action_id = assact3.assignment_action_id
AND pact3.payroll_action_id = assact3.payroll_action_id
AND pact3.action_type = 'V'
AND assact3.action_status = 'C');
SELECT max(decode(name,'Tax Code',result_value,NULL)) tax_code,
max(decode(name,'Tax Basis',result_value,NULL)) tax_basis,
to_number(max(decode(name,'Pay Previous',
fnd_number.canonical_to_number(result_value),NULL)))
pay_previous,
to_number(max(decode(name,'Tax Previous',
fnd_number.canonical_to_number(result_value),NULL)))
tax_previous
FROM pay_input_values_f v,
pay_run_result_values rrv
WHERE rrv.run_result_id = l_run_result_id
AND v.input_value_id = rrv.input_value_id
AND v.element_type_id = l_element_type_id;
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
max(decode(iv.name,'Pay Previous',screen_entry_value)) pay_previous,
max(decode(iv.name,'Tax Previous',screen_entry_value)) tax_previous
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_assignment_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_assignment_id
and e1.element_link_id = link1.element_link_id);
is select act.assignment_action_id,
act.payroll_action_id,
pact.effective_date ,
pact.payroll_id
from pay_assignment_actions act,
pay_payroll_actions pact
where act.assignment_id = X_ASSIGNMENT_ID
and act.action_status = 'C'
and pact.payroll_action_id = act.payroll_action_id
and act.action_sequence = (
select /*+ ORDERED use_nl(a,pact2,t,r,v,f)
user_index(v, PAY_RUN_RESULT_VALUES_PK) */
max(a.action_sequence)
from pay_assignment_actions a
,pay_payroll_actions pact2
,pay_balance_types t
,pay_balance_feeds_f f
,pay_run_results r
,pay_run_result_values v
where t.balance_name in ('Taxable Pay','PAYE')
and t.legislation_code = 'GB'
and f.balance_type_id = t.balance_type_id
and v.input_value_id = f.input_value_id
and v.run_result_id = r.run_result_id
and r.assignment_action_id = a.assignment_action_id
and a.payroll_action_id = pact2.payroll_action_id
and a.assignment_id = X_ASSIGNMENT_ID
and a.action_status = 'C'
and pact2.effective_date <= X_TRANSFER_DATE
and pact2.effective_date >= l_tax_year_start
-- and pact2.effective_date >=
-- to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(x_assignment_end_date,'YYYY'))),'DD-MM-YYYY')
and pact2.effective_date between f.effective_start_date and f.effective_end_date
and v.result_value is not null --Added for bug 13054902
and v.result_value <> '0'); --Added for bug 13054902
cursor c_ptp (taxable_update_payroll number, c_date_paid date) is
select min(PTP.start_date) start_date
from per_time_periods PTP
where PTP.payroll_id = taxable_update_payroll
and (PTP.REGULAR_PAYMENT_DATE ) >= (/*start of fyear prior to session date*/
to_date('06-04-'||
to_char(fnd_number.canonical_to_number(to_char(c_date_paid,'YYYY'))
+ least(sign(c_date_paid - to_date('06-04-'
|| to_char(c_date_paid,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY'));
select /*+ ORDERED index(a PER_ASSIGNMENTS_F_N12) use_nl(paa ppa pay flex) */
-- fnd_number.canonical_to_number(substr(max(
fnd_number.canonical_to_number(substr((
lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)) assignment_action_id
from per_all_assignments_f a,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex flex
,(SELECT a2.assignment_id, max(a2.effective_start_date) max_effective_start_date -- Bug 9292092
FROM per_all_assignments_f a2,
per_assignment_status_types past
WHERE /* a2.assignment_id = a.assignment_id
AND */ a2.effective_start_date <= c_agg_active_end
AND a2.effective_end_date >= c_agg_active_start
AND a2.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') group by a2.assignment_id) b
where a.person_id = c_person_id
and b.assignment_id = a.assignment_id
and a.effective_start_date = b.max_effective_start_date
and paa.assignment_id = a.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
-- and pay.payroll_id = a.payroll_id
and pay.payroll_id = ppa.payroll_id
and flex.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
and flex.segment1 = c_tax_reference
and (paa.source_action_id is not null
or ppa.action_type in ('I','V','B'))
and ppa.effective_date <= c_effective_end_date
-- bug 4553334
-- and c_effective_end_date between a.effective_start_date and a.effective_end_date
-- and c_effective_end_date between pay.effective_start_date and pay.effective_end_date
-- 5144323: ensure payroll is on the same paye ref at the time of payroll action
AND ppa.effective_date between pay.effective_start_date and pay.effective_end_date
-- bug 9910054
-- and ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null
or ppa.action_type in ('I','V','B'))
AND ppa.effective_date <= c_effective_date
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date <= c_effective_date
AND ppa.action_status = 'C'
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT 'Y' FROM
sys.dual target where exists
(select 1
from PAY_BALANCE_FEEDS_F FEED
, PAY_BALANCE_TYPES PBT
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
WHERE PBT.BALANCE_NAME in ('Taxable Pay', 'PAYE')
AND PBT.LEGISLATION_CODE = 'GB'
AND PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PRRV.RESULT_VALUE IS NOT NULL
AND PRRV.RESULT_VALUE <> '0'
AND PRR.ASSIGNMENT_ACTION_ID = c_assignment_action_id);
select paa.payroll_action_id,
ppa.effective_date, ppa.payroll_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = c_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select decode(p.per_information10,'Y','Y',NULL) agg_paye_flag
from per_all_people_f p
where p.person_id = c_person_id
and c_effective_date between
p.effective_start_date and p.effective_end_date;
SELECT to_date('06-04-'||
to_char(fnd_number.canonical_to_number(to_char(X_ASSIGNMENT_END_DATE,'YYYY'))
+ least(sign(X_ASSIGNMENT_END_DATE - to_date('06-04-'
|| to_char(X_ASSIGNMENT_END_DATE,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY')
FROM dual;
SELECT to_date('06-04-'||
to_char(fnd_number.canonical_to_number(to_char(ptp.regular_payment_date,'YYYY'))
+ least(sign(ptp.regular_payment_date - to_date('06-04-'
|| to_char(ptp.regular_payment_date,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY')
FROM per_time_periods ptp, pay_assignment_actions act, pay_payroll_actions pact
WHERE act.assignment_action_id = p_asg_action_id
AND act.payroll_Action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id;
SELECT nvl((min(effective_start_date)-1), hr_general.end_of_time) agg_end_date
FROM per_all_people_f
WHERE person_id = X_PERSON_ID
AND effective_start_date > X_ASSIGNMENT_END_DATE
AND nvl(per_information10, 'N') = 'N';
l_taxable_update_action number;
l_taxable_update_date date;
l_taxable_update_payroll number;
l_taxable_update_action := l_asg_action_id;
l_taxable_update_payroll := l_payroll_id;
l_taxable_update_date :=
least(l_effective_date,nvl(l_override_date,hr_general.end_of_time));
open c_ptp(l_taxable_update_payroll,
nvl(l_taxable_update_date, X_ASSIGNMENT_END_DATE));
if l_payroll_year_start > l_taxable_update_date then
x_assignment_action_id := -9999;
x_assignment_action_id := l_taxable_update_action;
x_date_earned := l_taxable_update_date;
x_assignment_action_id := l_taxable_update_action;
x_date_earned := l_taxable_update_date;
l_taxable_update_date := NULL;
l_taxable_update_date := NULL;
select oi.ORG_INFORMATION3,
oi.ORG_INFORMATION4,
ass.ASSIGNMENT_ID
from hr_organization_information oi,
pay_payrolls_f roll,
hr_soft_coding_keyflex flex,
per_assignments_f ass,
fnd_sessions sess
where oi.ORG_INFORMATION_CONTEXT = 'Tax Details References'
and roll.business_group_id + 0 = oi.organization_id
/* normally P45 is for leaver so pick up data on the assignment_end_date */
/* for non leavers eg. tax district change use the session date */
and sess.SESSION_ID = userenv('sessionid')
and nvl(c_assignment_end_date, sess.effective_date) between
ass.effective_start_date and ass.effective_end_date
and ass.payroll_id = roll.payroll_id
and nvl(c_assignment_end_date, sess.effective_date) between
roll.effective_start_date and roll.effective_end_date
and ass.assignment_id = L_ASSIGNMENT_ID
and flex.segment1 = oi.org_information1 /* same tax district */
and flex.soft_coding_keyflex_id = roll.soft_coding_keyflex_id;
select scl.segment1
from per_all_assignments_f paf,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl
where paf.assignment_id = c_assignment_id
and paf.payroll_id = ppf.payroll_id
and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
and c_effective_end_date between
paf.effective_start_date and paf.effective_end_date
and c_effective_end_date between
ppf.effective_start_date and ppf.effective_end_date;
select scl.segment1
from per_all_assignments_f paf,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl
where paf.assignment_id = c_assignment_id
and paf.payroll_id = ppf.payroll_id
and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
and c_effective_end_date between
paf.effective_start_date and paf.effective_end_date
and c_effective_end_date between
ppf.effective_start_date and ppf.effective_end_date;
select p.last_name
, p.title
, p.first_name
, p.middle_names
, p.person_id
, p.national_identifier
, serv.actual_termination_date
, decode(serv.leaving_reason,'D','D',NULL) deceased_flag
, a.assignment_number
, a.payroll_id
, org.organization_id
, org.name org_name
, p.date_of_birth /* P45 A4 2008/09 */
, p.sex /* P45 A4 2008/09 */
from per_all_people_f p
, per_all_assignments_f a
, per_periods_of_service serv
, hr_all_organization_units org
, fnd_sessions sess
where a.assignment_id = p_assignment_id
and sess.session_id = userenv('sessionid')
and sess.effective_date between
a.effective_start_date and a.effective_end_date
and a.person_id = p.person_id
and sess.effective_date between
p.effective_start_date and p.effective_end_date
and serv.person_id = p.person_id
and serv.date_start = ( select max(s.date_start) from per_periods_of_service s
where s.person_id = p.person_id
and sess.effective_date >= s.date_start )
and a.organization_id = org.organization_id;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = p_entity_name
AND legislation_code = 'GB'
AND business_group_id IS NULL;
SELECT
substr(org.org_information3,1,36) employers_name,
substr(org.org_information4,1,60) employers_address_line,
substr(org.org_information2 ,1,40) tax_district_name
FROM
pay_payroll_actions ppa,
hr_organization_information org
WHERE ppa.payroll_action_id = p_payroll_action_id
AND org.org_information_context = 'Tax Details References'
AND NVL(org.org_information10,'UK') = 'UK'
AND org.organization_id = ppa.business_group_id
AND substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,'TAX_REF=') + 8,
instr(ppa.legislative_parameters||' ',' ',
instr(ppa.legislative_parameters,'TAX_REF=')+8)
- instr(ppa.legislative_parameters,'TAX_REF=') - 8)
= org.org_information1
AND ppa.report_category='P45';
select pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters) payroll_id,
substr(pay_core_utils.get_parameter('TEST',legislative_parameters),1,1) test_indicator,
trim(substr(pay_core_utils.get_parameter('TEST_ID',legislative_parameters),1,8)) test_id,
report_type,
report_category
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
SELECT
start_date,
fnd_date.canonical_to_date
(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'DATE_TO')) end_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT
fnd_date.canonical_to_date
(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'DATE_FROM')) start_date,
fnd_date.canonical_to_date
(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'END_DATE')) end_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
sqlstr := 'select distinct paaf.person_id '||
'from pay_payroll_actions ppa, '||
'per_all_assignments_f paaf '||
'where ppa.payroll_action_id = :payroll_action_id '||
'and paaf.business_group_id + 0 = ppa.business_group_id '||
'and paaf.payroll_id = '||to_char(l_payroll_id)||
' order by paaf.person_id';
sqlstr := 'select distinct person_id '||
'from per_people_f ppf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :payroll_action_id '||
'and ppa.business_group_id = ppf.business_group_id '||
'order by ppf.person_id';
sqlstr := 'select 1 '||
'/* ERROR - The Start Date and the End Date must be within the tax year: '||
sqlerrm(sqlcode)||' */ '||
'from dual where to_char(:payroll_action_id) = dummy';
sqlstr := 'select 1 '||
'/* ERROR - Employer Details Fetch failed with: '||
sqlerrm(sqlcode)||' */ '||
'from dual where to_char(:payroll_action_id) = dummy';
select parameter_value
from pay_action_parameters
where parameter_name = 'RANGE_PERSON_ID';
select par.parameter_value
from pay_report_format_parameters par,
pay_report_format_mappings_f map
where map.report_format_mapping_id = par.report_format_mapping_id
and map.report_type = 'P45'
and map.report_format = c_report_format
and map.report_qualifier = 'GB'
and par.parameter_name = 'RANGE_PERSON_ID';
select
max(decode(fai.user_entity_id,g_period_of_service_eid,fai.VALUE)) pos
,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
from ff_archive_items fai, -- of P45 report type
pay_assignment_actions act,
pay_payroll_actions ppa
where ppa.report_type='P45'
and ppa.report_qualifier='GB'
and ppa.report_category ='P45'
and ppa.action_type = 'X'
and ppa.payroll_action_id = act.payroll_action_id
and act.assignment_id = c_assignment_id
and act.assignment_action_id = fai.context1
and fai.user_entity_id in (g_tax_ref_transfer_eid,
g_period_of_service_eid);
select max(decode(fai.user_entity_id,g_period_of_service_eid,fai.VALUE)) pos
,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
from ff_archive_items fai
where fai.user_entity_id in (g_tax_ref_transfer_eid,g_period_of_service_eid)
and fai.context1 = (select max(act.assignment_action_id)
from pay_payroll_actions ppa,
pay_assignment_actions act
where ppa.report_type='P45'
and ppa.report_qualifier='GB'
and ppa.report_category ='P45'
and ppa.action_type = 'X'
and ppa.payroll_action_id = act.payroll_action_id
and act.assignment_id = c_assignment_id);
select 1 from dual where exists
(select paf.effective_end_date
from per_all_assignments_f paf,
per_assignment_status_types past
where past.assignment_status_type_id = paf.assignment_status_type_id
and paf.assignment_id = c_assignment_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and paf.effective_end_date > c_effective_end_date);
select 1 from dual where exists
(select scl.segment1
from per_all_assignments_f paf,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl
where paf.assignment_id = c_assignment_id
and paf.payroll_id = ppf.payroll_id
and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
and scl.segment1 <> c_tax_ref
--Bug:13428461:
and ppf.payroll_id <> p_payroll_id
--End 13428461:
and paf.effective_start_date between
ppf.effective_start_date and ppf.effective_end_date
and paf.effective_end_date > c_effective_end_date);
select 1 from dual where exists
(select pa.effective_date
from pay_payroll_actions pa,
pay_assignment_actions aa
where aa.assignment_id = c_assignment_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.action_type in ('R','Q','V','I','B')
and pa.date_earned >= c_effective_end_date);
select serv.last_standard_process_date
from per_periods_of_service serv
where serv.period_of_service_id = c_period_of_service_id;
select 1 from dual where exists
(select paa.assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where ptp.time_period_id = ppa.time_period_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = c_assignment_id
and ppa.action_type in ('R','Q','V','I','B')
and ptp.regular_payment_date =
(select max(ptp.regular_payment_date)
from per_all_assignments_f paf,
per_time_periods ptp
where ptp.regular_payment_date <= c_last_process_date
and paf.assignment_id = c_assignment_id
and ptp.payroll_id = paf.payroll_id
and c_effective_end_date between
paf.effective_start_date and paf.effective_end_date));
select 1 from dual where exists
(select aei.aei_information3
from per_assignment_extra_info aei
where aei.assignment_id = c_assignment_id
and aei.aei_information3 is not null
and aei.information_type = 'GB_P45');
select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0') ||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND (paa.source_action_id is not null
or ppa.action_type in ('I','V','B'))
-- AND ppa.effective_date <= c_effective_end_date --Bug 9036584
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
select 1 from dual where exists
(select prrv.result_value
from pay_input_values_f inv,
pay_element_types_f type,
pay_run_results prr,
pay_run_result_values prrv
where prrv.input_value_id = inv.input_value_id
and inv.name = 'Tax Code'
and prr.assignment_action_id = c_assignment_action_id
/* and c_effective_end_date between
inv.effective_start_date and inv.effective_end_date
and c_effective_end_date between
type.effective_start_date and type.effective_end_date */--Bug 9036584
and prrv.result_value = 'NI'
and type.element_name = 'PAYE Details'
and type.element_type_id = prr.element_type_id
and prrv.run_result_id = prr.run_result_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,
start_date,
effective_date,
fnd_date.canonical_to_date
(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'DATE_TO')) end_date,
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = p_entity_name
AND legislation_code = 'GB'
AND business_group_id IS NULL;
select a.assignment_id,
a.effective_end_date
from per_all_assignments_f a,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex flex,
per_periods_of_service serv
where a.person_id = c_person_id
and flex.segment1 = c_tax_ref
and pay.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
and a.payroll_id = pay.payroll_id
and a.effective_end_date between
pay.effective_start_date and pay.effective_end_date
and serv.period_of_service_id = a.period_of_service_id
and a.assignment_id <> c_assignment_id
and a.period_of_service_id = c_period_of_service_id
-- 5144323: only last active/suspended dt instances of the
-- assignemnts are needed
AND a.effective_end_date = ( SELECT max(effective_end_date)
FROM per_all_assignments_f a1,
per_assignment_status_types past
WHERE a.assignment_id = a1.assignment_id
AND a1.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN'))
-- 5144323: assignments must exist during the aggregation period
AND EXISTS (SELECT 1 FROM per_all_assignments_f a2
WHERE a.assignment_id = a2.assignment_id
AND a2.effective_start_date <= c_agg_end_date
AND a2.effective_end_date >= c_agg_start_date)
-- 5144323: assignments must share continuous period of
-- employment with the input assignment
AND EXISTS (SELECT 1
FROM per_all_assignments_f a3,
per_assignment_status_types past
WHERE a.assignment_id = a3.assignment_id
AND a3.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND a3.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_term_date)
AND a3.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_term_date));
SELECT /*+ ORDERED */
a.assignment_id,
a.assignment_number,
a.period_of_service_id,
p.person_id,
decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
max(a.effective_end_date) asg_end_date,
ptp.regular_payment_date
FROM per_all_people_f p,
per_all_assignments_f a,
per_assignment_status_types past,
pay_all_payrolls_f ppf,
per_time_periods ptp,
per_periods_of_service serv,
hr_soft_coding_keyflex flex
WHERE a.person_id BETWEEN stperson AND endperson
AND a.business_group_id +0 = g_business_group_id
AND (g_payroll_id is null
or
a.payroll_id + 0 = g_payroll_id)
AND a.effective_end_date BETWEEN g_start_date AND g_end_date
AND a.payroll_id = ppf.payroll_id
AND a.period_of_service_id = serv.period_of_service_id
AND a.effective_end_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ppf.payroll_id = ptp.payroll_id
AND a.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
AND a.effective_end_date <= g_end_date -- before run end date
AND g_effective_date >= a.effective_start_date -- added by kvinayku for bug 14206931,15971584
-- AND a.effective_end_date = -- the latest active or susp asg exclude DT update
-- (select max(asg2.effective_end_date)
-- from per_all_assignments_f asg2,
-- per_assignment_status_types past
-- where asg2.assignment_id = a.assignment_id
-- and asg2.assignment_status_type_id =past.assignment_status_type_id
-- and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
-- and asg2.effective_end_date <> hr_general.end_of_time)
AND a.assignment_status_type_id =past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
AND a.effective_end_date <> hr_general.end_of_time
AND a.person_id = p.person_id
AND a.effective_end_date between p.effective_start_date and p.effective_end_date
GROUP BY a.assignment_id, a.assignment_number, a.period_of_service_id,
p.person_id, decode(p.per_information10,'Y','Y',NULL),
ptp.regular_payment_date
--BUG:13428461
order by ptp.regular_payment_date desc, a.assignment_id, a.assignment_number, a.period_of_service_id,
p.person_id, decode(p.per_information10,'Y','Y',NULL);
SELECT /*+ ORDERED*/
a.assignment_id, a.assignment_number,
a.period_of_service_id,
p.person_id,
decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
max(a.effective_end_date) asg_end_date,
ptp.regular_payment_date
FROM pay_population_ranges ppr,
per_all_people_f p,
per_all_assignments_f a,
per_assignment_status_types past,
pay_all_payrolls_f ppf,
per_time_periods ptp,
per_periods_of_service serv,
hr_soft_coding_keyflex flex
WHERE p.person_id = ppr.person_id
AND ppr.chunk_number = chunk
AND ppr.payroll_action_id = pactid
AND a.business_group_id +0 = g_business_group_id
AND a.payroll_id +0 = nvl(g_payroll_id,a.payroll_id)
AND a.effective_end_date
BETWEEN g_start_date AND g_end_date
AND a.payroll_id = ppf.payroll_id
AND a.period_of_service_id = serv.period_of_service_id
AND a.effective_end_date
BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ppf.payroll_id = ptp.payroll_id
AND a.effective_end_date
BETWEEN ptp.start_date AND ptp.end_date
AND a.effective_end_date <= g_end_date -- before run end date
AND g_effective_date >= a.effective_start_date -- added by kvinayku for bug 14206931,15971584
--AND a.effective_end_date = -- the latest active or susp asg exclude DT update
-- (select max(asg2.effective_end_date)
-- from per_all_assignments_f asg2,
-- per_assignment_status_types past
-- where asg2.assignment_id = a.assignment_id
-- and asg2.assignment_status_type_id =
-- past.assignment_status_type_id
-- and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
-- and asg2.effective_end_date <> hr_general.end_of_time)
AND a.assignment_status_type_id =past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
AND a.effective_end_date <> hr_general.end_of_time
AND a.person_id = p.person_id
AND a.effective_end_date between p.effective_start_date and p.effective_end_date
GROUP BY a.assignment_id, a.assignment_number, a.period_of_service_id,
p.person_id, decode(p.per_information10,'Y','Y',NULL),
ptp.regular_payment_date
--BUG 13428461:
ORDER BY ptp.regular_payment_date desc, a.assignment_id, a.assignment_number, a.period_of_service_id,
p.person_id, decode(p.per_information10,'Y','Y',NULL);
SELECT nvl((min(effective_start_date)-1), hr_general.end_of_time) agg_end_date
FROM per_all_people_f
WHERE person_id = p_person_id
AND effective_start_date > p_term_date
AND nvl(per_information10, 'N') = 'N';
SELECT nvl((max(effective_end_date)+1), hr_general.start_of_time) agg_start_date
FROM per_all_people_f
WHERE person_id = p_person_id
AND effective_end_date < p_term_date
AND nvl(per_information10, 'N') = 'N';
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
INSERT INTO pay_message_lines(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
VALUES(
pay_message_lines_s.nextval
,null
,'F'
,p45_assignment_action_id
,'A'
,substr(p_message_text,1,240)
);
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = p_entity_name
AND legislation_code = 'GB'
AND business_group_id IS NULL;
SELECT
to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'PAYROLL_ID')) payroll_id,
decode(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'CHAR_ERROR'),
'Y','TRUE','N','FALSE') check_chars,
effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT pay_gb_eoy_archive.get_parameter(legislative_parameters,'EDI_VER') edi_ver
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT element_type_id
INTO g_paye_details_id
FROM pay_element_types_f
WHERE element_name = 'PAYE Details'
AND l_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT payroll_id,pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYROLL_ID')
INTO l_ppa_payroll_id,l_payroll_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id ;
UPDATE pay_payroll_actions
SET payroll_id = l_payroll_id
WHERE payroll_action_id = p_payroll_action_id;
SELECT act.assignment_id,
fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str
(act.assignment_action_id,
g_termination_date_eid)) term_date,
pay_gb_eoy_archive.get_arch_str(act.assignment_action_id,
g_tax_ref_transfer_eid) tax_ref_transfer
FROM pay_assignment_actions act
WHERE act.assignment_action_id = p_asgactid;
SELECT ass.assignment_number,
ass.person_id,
ass.effective_end_date asg_effective_end_date,
serv.last_standard_process_date,
nvl(serv.final_process_date, hr_general.end_of_time), -- For bug 9071978
ass.period_of_service_id,
decode(serv.leaving_reason,'D','D') deceased_flag,
org.name org_name,
upper(p.last_name) , p.title ,
--SUBSTR(upper(p.first_name || ' ' || p.middle_names),1,150),
--SUBSTR(upper(p.first_name || ',' || p.middle_names),1,150),--replaces space with a "comma" for the P45 EOY changes
upper(p.first_name), upper(p.middle_names), /*Bug 6710229*/
p.national_identifier, ass.payroll_id,
decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
p.date_of_birth, p.sex
FROM
per_all_people_f p,
hr_all_organization_units org,
per_periods_of_service serv,
per_all_assignments_f ass
WHERE ass.assignment_id = p_assid
AND serv.period_of_service_id = ass.period_of_service_id
AND ass.effective_end_date = p_term_date
AND ass.organization_id = org.organization_id
AND ass.person_id = p.person_id
AND ass.effective_end_date BETWEEN p.effective_start_date
AND p.effective_end_date;
SELECT decode(target.basic_period_type, 'W', 'W', 'M')
FROM per_time_period_rules target ,
per_time_period_types ptpt ,
pay_payrolls_f roll,
pay_payroll_actions pact
WHERE ptpt.period_type = roll.period_type
AND target.number_per_fiscal_year = ptpt.number_per_fiscal_year
AND roll.payroll_id = pact.payroll_id
AND pact.effective_date BETWEEN roll.effective_start_date
AND roll.effective_end_date
AND pact.payroll_action_id = p_payroll_action_id;
SELECT decode(target.basic_period_type, 'W', 'W', 'M')
FROM per_time_period_rules target ,
per_time_period_types ptpt ,
pay_payrolls_f roll
WHERE ptpt.period_type = roll.period_type
AND target.number_per_fiscal_year = ptpt.number_per_fiscal_year
AND roll.payroll_id = p_payroll_id
AND p_effective_date BETWEEN roll.effective_start_date
AND roll.effective_end_date ;
/*SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
FROM per_time_periods ptp
WHERE ptp.payroll_id = p_payroll_id
AND p_term_date BETWEEN ptp.start_date AND ptp.end_date;
SELECT distinct first_value(period_num) Over(order by regular_payment_date)
FROM per_time_periods
Where payroll_id = p_payroll_id
AND regular_payment_date >= p_term_date;
SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
FROM per_time_periods ptp,
pay_payroll_actions pact
WHERE pact.payroll_action_id = p_payroll_action_id
AND ptp.payroll_id = pact.payroll_id
AND pact.date_earned BETWEEN ptp.start_date AND ptp.end_date;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = 'PAYE';
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = l_paye_element_id
AND name = p_ipv_name;
SELECT result_value
FROM pay_run_result_values
WHERE run_result_id = l_max_run_result_id
AND input_value_id = p_ipv_id;
SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
pact PAY_PAYROLL_ACTIONS_PK,
r2 PAY_RUN_RESULTS_N50)
USE_NL(assact2, pact, r2) */
to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
r2.run_result_id),17))
FROM pay_assignment_actions assact2,
pay_payroll_actions pact,
pay_run_results r2
WHERE assact2.assignment_id = l_assignment_id
AND r2.element_type_id+0 = l_paye_element_id
AND r2.assignment_action_id = assact2.assignment_action_id
AND r2.status IN ('P', 'PA')
AND pact.payroll_action_id = assact2.payroll_action_id
AND pact.action_type IN ( 'Q','R','B','I')
AND assact2.action_status = 'C'
AND pact.effective_date between
to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(l_date_earned,'YYYY'))),'DD-MM-YYYY')
and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_date_earned,'YYYY') + 1)),'DD-MM-YYYY')
AND NOT EXISTS(
SELECT '1'
FROM pay_action_interlocks pai,
pay_assignment_actions assact3,
pay_payroll_actions pact3
WHERE pai.locked_action_id = assact2.assignment_action_id
AND pai.locking_action_id = assact3.assignment_action_id
AND pact3.payroll_action_id = assact3.payroll_action_id
AND pact3.action_type = 'V'
AND assact3.action_status = 'C');
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
max(decode(iv.name,'Pay Previous',screen_entry_value))
pay_previous,
max(decode(iv.name,'Tax Previous',screen_entry_value))
tax_previous
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_assignment_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 p_effective_date
BETWEEN link.effective_start_date AND link.effective_end_date
AND p_effective_date
BETWEEN e.effective_start_date AND e.effective_end_date
AND p_effective_date
BETWEEN iv.effective_start_date AND iv.effective_end_date
AND p_effective_date
BETWEEN v.effective_start_date AND v.effective_end_date;
SELECT a.formula_id
FROM ff_formulas_f a,
ff_formula_types t
WHERE a.formula_name = p_formula_name
AND business_group_id IS NULL
AND legislation_code = 'GB'
AND a.formula_type_id = t.formula_type_id
AND t.formula_type_name = 'Oracle Payroll';
SELECT printer,
print_style,
decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
FROM pay_payroll_actions pact,
fnd_concurrent_requests fcr
WHERE fcr.request_id = pact.request_id
AND pact.payroll_action_id = p_payroll_action_id;
select 1 from dual where exists
(select action_status
from pay_assignment_actions
where payroll_action_id = c_payroll_action_id
and action_status = 'E');
select
pay_gb_eoy_archive.get_parameter(legislative_parameters,
'P45') stationary_type,
pay_gb_eoy_archive.get_parameter(legislative_parameters,
'PDEF') defer_print
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = p_entity_name
AND legislation_code = 'GB'
AND business_group_id IS NULL;
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,
fnd_date.canonical_to_date
(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'DATE_FROM')) start_date,
effective_date end_date,
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT /*+ ORDERED */
DISTINCT
act.assignment_action_id archive_action,
act.assignment_id
FROM pay_payroll_actions pact,
pay_assignment_actions act,
per_assignments_f paf,
pay_action_interlocks pai,
ff_archive_items fai
WHERE pact.report_type ='P45'
AND pact.report_qualifier ='GB'
AND pact.report_category ='P45'
AND pact.action_status = 'C'
AND pact.action_type = 'X'
AND pact.business_group_id +0 = g_business_group_id
AND pact.effective_date BETWEEN g_start_date AND g_end_date
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)
= g_tax_ref
AND (g_payroll_id IS NULL
OR
nvl(substr(pact.legislative_parameters,
instr(pact.legislative_parameters||' PAYROLL_ID='
,'PAYROLL_ID=') + 11,
instr(pact.legislative_parameters||' ',' ',
instr(pact.legislative_parameters,'PAYROLL_ID=')+11)
- instr(pact.legislative_parameters,'PAYROLL_ID=') - 11),
nvl(to_char(g_payroll_id),'x')) = nvl(to_char(g_payroll_id),'x'))
/* restrict by payroll_id in archive */
AND fai.context1 (+) = act.assignment_action_id
AND fai.user_entity_id (+) = g_payroll_id_eid
AND nvl(fai.VALUE,nvl(to_char(g_payroll_id),'x'))
= nvl(nvl(to_char(g_payroll_id),fai.VALUE),'x')
AND pact.payroll_action_id = act.payroll_action_id
AND paf.assignment_id = act.assignment_id
AND paf.person_id BETWEEN stperson AND endperson
AND paf.business_group_id +0 = g_business_group_id
/* restrict to one row per asg. */
-- Comment out this code as it will be replace by distinct
--AND paf.effective_start_date =
-- (SELECT max(paf2.effective_start_date)
-- FROM per_assignments_f paf2
-- WHERE paf2.assignment_id = paf.assignment_id)
/* commnet out this code and replace by the code below */
--AND NOT EXISTS (SELECT 1
-- FROM pay_action_interlocks pai
-- WHERE pai.locked_action_id = act.assignment_action_id);
SELECT /*+ordered
index(pact PAY_PAYROLL_ACTIONS_N52)
index(ppr PAY_POPULATION_RANGES_N4)
index(act PAY_ASSIGNMENT_ACTIONS_N51)
index(fai FF_ARCHIVE_ITEMS_N50)*/
DISTINCT
act.assignment_action_id archive_action,
act.assignment_id
from pay_payroll_actions pact,
pay_population_ranges ppr,
per_assignments_f paf,
pay_assignment_actions act,
ff_archive_items fai
WHERE pact.report_type ='P45'
AND pact.report_qualifier ='GB'
AND pact.report_category ='P45'
AND pact.action_status = 'C'
AND pact.action_type = 'X'
AND pact.business_group_id +0 = g_business_group_id
AND pact.effective_date BETWEEN g_start_date AND g_end_date
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)
= g_tax_ref
AND (g_payroll_id IS NULL
OR
nvl(substr(pact.legislative_parameters,
instr(pact.legislative_parameters||' PAYROLL_ID='
,'PAYROLL_ID=') + 11,
instr(pact.legislative_parameters||' ',' ',
instr(pact.legislative_parameters,'PAYROLL_ID=')+11)
- instr(pact.legislative_parameters,'PAYROLL_ID=') - 11),
nvl(to_char(g_payroll_id),'x')) = nvl(to_char(g_payroll_id),'x'))
/* restrict by payroll_id in archive */
AND fai.context1 (+) = act.assignment_action_id
AND fai.user_entity_id (+) = g_payroll_id_eid
AND nvl(fai.VALUE,nvl(to_char(g_payroll_id),'x'))
= nvl(nvl(to_char(g_payroll_id),fai.VALUE),'x')
AND pact.payroll_action_id = act.payroll_action_id
AND paf.assignment_id = act.assignment_id
AND paf.person_id = ppr.person_id
AND ppr.chunk_number = chunk
AND ppr.payroll_action_id = pactid
AND paf.business_group_id +0 = g_business_group_id
/* restrict to one row per asg. */
/* Comment out the code for performance fix */
--AND paf.effective_start_date =
-- (SELECT max(paf2.effective_start_date)
-- FROM per_assignments_f paf2
-- WHERE paf2.assignment_id = paf.assignment_id)
AND NOT EXISTS (SELECT 1
FROM pay_action_interlocks pai
WHERE pai.locked_action_id = act.assignment_action_id);
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
select nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),1,1),' ') NINO1,
nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),2,1),' ') NINO2,
nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),3,1),' ') NINO3,
nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),4,1),' ') NINO4,
nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),5,1),' ') NINO5,
nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),6,1),' ') NINO6,
nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),7,1),' ') NINO7,
nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),8,1),' ') NINO8,
nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),9,1),' ') NINO9,
nvl(max(decode(fue.user_entity_name,'X_LAST_NAME',fai.VALUE)),' ') LAST_NAME,
nvl(max(decode(fue.user_entity_name,'X_TITLE',fai.VALUE)),' ') TITLE,
nvl(max(decode(fue.user_entity_name,'X_FIRST_NAME',fai.VALUE)),' ') FIRST_NAME,
nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_TERMINATION_DATE',fai.VALUE))),'DD-MM-YYYY'),1,2),' ') DATE_OF_LEAVING_DD,
nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_TERMINATION_DATE',fai.VALUE))),'DD-MM-YYYY'),4,2),' ') DATE_OF_LEAVING_MM,
nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_TERMINATION_DATE',fai.VALUE))),'DD-MM-YYYY'),7,4),' ') DATE_OF_LEAVING_YY,
nvl(max(decode(fue.user_entity_name,'X_TAX_CODE',fai.VALUE)),' ') TAX_CODE,
nvl(max(decode(fue.user_entity_name,'X_W1_M1_INDICATOR',fai.VALUE)),' ') W1_M1_IND,
nvl(max(decode(fue.user_entity_name,'X_WEEK_NUMBER',fai.VALUE)),' ') WEEK_NO,
nvl(max(decode(fue.user_entity_name,'X_MONTH_NUMBER',fai.VALUE)),' ') MONTH_NO,
trunc(nvl(max(decode(fue.user_entity_name,'X_TAXABLE_PAY',fai.VALUE)),0)) PAY_TD_POUNDS,
mod(nvl((max(decode(fue.user_entity_name,'X_TAXABLE_PAY',fai.VALUE))*100),0),100) PAY_TD_PENCE,
trunc(nvl(max(decode(fue.user_entity_name,'X_TAX_PAID',fai.VALUE)),0)) TAX_TD_POUNDS,
mod(nvl((max(decode(fue.user_entity_name,'X_TAX_PAID',fai.VALUE))*100),0),100) TAX_TD_PENCE,
trunc(nvl(max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',fai.value)),0)) PREV_PAY_IN_POUNDS,
mod(nvl((max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',fai.value))*100),0),100) PREV_PAY_IN_PENCE,
trunc(nvl(max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID',fai.value)),0)) PREV_TAX_IN_POUNDS,
mod(nvl((max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID',fai.value))*100),0),100) PREV_TAX_IN_PENCE,
upper(nvl(max(decode(fue.user_entity_name,'X_ASSIGNMENT_NUMBER',fai.VALUE)),' ')) ASSIGNMENT_NUMBER,
upper(nvl(max(decode(fue.user_entity_name,'X_ORGANIZATION_NAME',fai.VALUE)),' ')) ORGANIZATION_NAME,
upper(nvl(max(decode(fue.user_entity_name,'X_ADDRESS_LINE1',fai.VALUE)),' ')) ADDRESS_LINE1,
upper(nvl(max(decode(fue.user_entity_name,'X_ADDRESS_LINE2',fai.VALUE)),' ')) ADDRESS_LINE2,
upper(nvl(max(decode(fue.user_entity_name,'X_ADDRESS_LINE3',fai.VALUE)),' ')) ADDRESS_LINE3,
upper(nvl(max(decode(fue.user_entity_name,'X_TOWN_OR_CITY',fai.VALUE)),' ')) TOWN_OR_CITY,
upper(nvl(max(decode(fue.user_entity_name,'X_COUNTY',fai.VALUE)),' ')) COUNTY,
upper(nvl(max(decode(fue.user_entity_name,'X_POSTAL_CODE',fai.VALUE)),' ')) POSTAL_CODE,
upper(nvl(max(decode(fue.user_entity_name,'X_DECEASED_FLAG',fai.VALUE)),' ')) DECEASED_FLAG,
nvl(max(decode(fue.user_entity_name,'X_ISSUE_DATE',fai.VALUE)),' ') ISSUE_DATE,
upper(nvl(max(decode(fue.user_entity_name,'X_TAX_REF_TRANSFER',fai.VALUE)),' ')) TAX_REF_TRANSFER,
upper(nvl(max(decode(fue.user_entity_name,'X_STUDENT_LOAN_FLAG',fai.VALUE)),' ')) STUDENT_LOAN_FLAG,
upper(nvl(max(decode(fue.user_entity_name,'X_COUNTRY',fai.VALUE)),' ')) COUNTRY,
/*P45 A4 2008/09*/
nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),1,2),' ') DATE_OF_BIRTH_DD,
nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),4,2),' ') DATE_OF_BIRTH_MM,
nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),7,4),' ') DATE_OF_BIRTH_YY,
nvl(max (decode(fue.user_entity_name,'X_SEX', substr(fai.value,1,1))),' ') SEX
/*P45 A4 2008/09*/
from ff_archive_items fai,
ff_user_entities fue
where x_assignment_action_id = fai.context1
and fai.archive_type <>'PA'
and fai.user_entity_id = fue.user_entity_id;
select upper(nvl(max(decode(fue.user_entity_name,'X_EMPLOYERS_NAME',fai.VALUE)),null)) EMPLOYERS_NAME,
upper(nvl(max(decode(fue.user_entity_name,'X_EMPLOYERS_ADDRESS_LINE',fai.VALUE)),null)) EMPLOYERS_ADDRESS
from ff_archive_item_contexts aic,
ff_archive_items fai,
ff_user_entities fue
where X_PAYROLL_ACTION_ID = fai.context1
and fai.user_entity_id = fue.user_entity_id
and fai.archive_item_id = aic.archive_item_id
and aic.context = '0'
and aic.sequence_no = 1;
SELECT act.assignment_action_id, pact.effective_date, act.action_sequence
FROM pay_assignment_actions act, pay_payroll_actions pact
WHERE act.assignment_id = c_assignment_id
AND act.payroll_action_id = pact.payroll_action_id
AND pact.REPORT_QUALIFIER = 'GB'
AND pact.ACTION_TYPE = 'X'
AND act.action_status = 'C'
AND pact.action_status = 'C' --9473697 When Payoll action is in error status, neither P45 pdf report nor EDI can be produced.
AND report_type = 'P45'
AND report_category = 'P45';
SELECT fai.VALUE
FROM ff_archive_items fai, ff_user_entities fue
WHERE fai.user_entity_id = fue.user_entity_id
AND fue.user_entity_name = 'X_TAX_REF_TRANSFER'
AND fue.legislation_code = 'GB'
AND fue.business_group_id IS NULL
AND fai.context1 = c_assignment_action_id ;
select fnd_date.canonical_to_date(aei_information3)
from per_assignment_extra_info
where assignment_id = c_assignment_id
and information_type = 'GB_P45';
select distinct asg1.assignment_id, asg1.person_id
from per_all_assignments_f asg1, per_all_assignments_f asg2
where asg2.assignment_id = c_assignment_id
and asg2.person_id = asg1.person_id
and asg1.assignment_id <> p_assignment_id;
select pact1.payroll_action_id, pact1.effective_date final_payment_date
from FF_ARCHIVE_ITEMS ai,
ff_user_entities ue,
pay_payroll_actions pact1
WHERE ue.user_entity_name in ('X_MONTH_NUMBER', 'X_WEEK_NUMBER') -- for the weekly frequency (and multiples)
AND ue.legislation_code = 'GB'
AND ue.business_group_id IS NULL
and ue.user_entity_id = ai.user_entity_id
and ai.archive_type = 'AAP'
and ai.context1 = c_asg_action_id
and pact1.payroll_action_id =
(
select to_number(substr(max(lpad(to_char(act.action_sequence), 20, '0')||to_char(pact.payroll_action_id)),21)) -- just to be consistent with rest of the code to get highest action based on the action sequence
from pay_assignment_actions act,
pay_payroll_actions pact,
per_time_periods ptp -- moved to subquery to make sure latest payroll action having period num matching the archive is fetched
where pact.payroll_action_id = act.payroll_action_id
and pact.action_type in ('Q', 'R', 'B', 'I', 'V')
and act.assignment_id = c_assignment_id
and pact.action_sequence < c_action_sequence -- assuming you will write another sql to get p_p45_action_sequence, alternatively another join to pay_assignment_actions can get you this value in this sql
-- and act.SOURCE_ACTION_ID is null -- no need to check for source_action_id being null to cover upgrade from R11 cases
and ai.value = to_char(ptp.period_num)
and pact.time_period_id = ptp.time_period_id
);
SELECT flex.segment1 paye_ref, paaf.period_of_service_id
FROM per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.assignment_id = c_assignment_id
AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date
AND paaf.payroll_id = papf.payroll_id
AND c_effective_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id;
select 1
from per_all_assignments_f a,
per_assignment_status_types past
where a.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
and a.assignment_id = c_assignment_id
and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_agg_assignment_id, c_tax_ref, c_effective_date)
and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_agg_assignment_id, c_tax_ref, c_effective_date)
;
select per_information10
from per_all_people_f
where person_id = c_person_id
and c_effective_date between
effective_start_date and effective_end_date;
select max(effective_end_date)
from per_all_assignments_f a,
per_assignment_status_types past
where a.assignment_id = c_assignment_id
and a.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
select max(effective_end_date) + 1
from per_all_people_f
where person_id = c_person_id
and nvl(per_information10,'N') = 'N'
and effective_end_date < c_effective_date;
select 1
from per_all_assignments_f a
where a.assignment_id = c_assignment_id
and a.effective_end_date >= c_start_date
and a.effective_start_date <= c_end_date;
select 1
from ff_archive_items fai,
ff_user_entities fue
where fai.user_entity_id = fue.user_entity_id
and fue.user_entity_name = 'X_P45_INCLUDED_ASSIGNMENT'
and fai.context1 = c_assignment_action_id
and fai.value = to_char(c_assignment_id);
select effective_date
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
select effective_date
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id; */