[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
11-Feb-2000 A.Parkes 110.1 1178972 Changed select of gross_pay
in emps_cur cursor.
29-Feb-2000 A.Mills 115.0 forward ported.
22-Mar-2000 A.Mills 115.1 1232417 Expanded error message size
for bug fix of eoy process.
Using DBI for error message.
31-Mar-2000 A.Parkes 115.2 1232417 Allow length(tax_code) <= 7
and smp <= 99999999
17-Apr-2000 A.Mills 115.3 1265531 Changed emps_cur to ensure
Middle name is 7 chars
in the Magtape.
12-Jun-2000 A.Blinko 115.4 1268568 Now processes assignments with
>5 NI categories correctly.
13-Jul-2000 A.Mills 115.5 1364509 Add EET, Student Loans,
=110.6 Tax Credits and Ees Rebate
to outputs to MAG_RECORD2 and 4.
Altered validation of name fields
removed SCON checking in main
procedure, now validated in the
formula using call to new
generic validate function.
02-Aug-2000 A.Mills 115.6 Fixed minor conversion error
found in unit testing.
07-Sep-2000 A.Parkes 115.7 Fixed magtape validation so
+ is disallowed.
Added EDI validation checks.
19-Oct-2000 A.Mills 115.8 Performance tune for emps_cur.
NB more substr etc formatting
inline with 10.7 code, this
speeds up code due to reduced
sort key.
16-Feb-2001 A.Parkes 115.9 Allow = in EDI charset
13-Mar-2001 A.Parkes 115.10 1682586 Changed header_cur subquery
to filter on char payroll_ids
Also removed 'Dan Tow Decode'.
18-Sep-2001 A.Mills 115.11 1778139 Added Assignment Message for
asgs that have been updated
during the run (warnings).
20-Sep-2001 R. Makhija 115.12 1585510 Removed references to EET
1802363 balance values, changed emps_cur
to select full assignment_number,
increased length of number
variabes.
19-Oct-2001 K.Thampan 115.13 Put blank into tax code field of
mag record row two
17-DEC-2001 R.Makhija 115.14 Increased length of student
loan variables
18-Nov-2001 R.Makhija 115.15 Added P14 EDI functionality
09-JAN-2002 R.Makhija 115.16 Added Checkfile commands
29-JAN-2002 R.Makhija 115.17 Added 'SET VARIFY OFF' at the
beginning to fix GSCC warning.
11-FEB-2002 R.Makhija 115.18 Added 2 more parameters for
EDI EMP HEADER formula to
pass middle name and
and title of an employee.
08-MAY-2002 A.Mills 115.19 Aggregated PAYE changes. Skip
the employee type 2 record if
all balances are zero, must be
aggregated.
19-jul-2002 Vimal 115.21 Fixes bug 2392279. The chanegs added
to version 20 does not work as the pkg
fails to compile on UTF8 database.
So the fix was to chaneg the variable
declaration of the address line
to size greater than 27. Some other
variable were also changed so that
the process does not fail bcos of this
error again.
05-DEC-2002 V.Vinod 115.22 2696015 P14 EDI Enhancement for Year 2003
13-SEP-2003 npershad 115.25 3133921 P14 EDI/P35 MT Functional Changes
for End of Year 2003/2004
24-MAR-2004 A.Mills 115.26 3527428 Fixed header_cur to ensure that
Tax District Ref is 3 characters,
issue found in P14EDI with short
Tax Dist Ref No.
10-MAY-2004 npershad 115.27 3614251 Added nvl call in cursor emps_cur,
for field X_SUPERANNUATION_PAID.
21-OCT-2004 rmakhija 115.28 3962706 Changed cursor emps_cur to suppress
21-OCT-2004 rmakhija 115.28 3962706 Changed cursor emps_cur to suppress
21-OCT-2004 rmakhija 115.28 3962706 Changed cursor emps_cur to suppress
secondary aggregated asssignments.
15-NOV-2004 rmakhija 115.29 4011263 P14 EDI Changes for 2004-2005.
07-DEC-2004 rmakhija 115.30 4011263 Added coomit and exit at the end
21-JAN-2005 rmakhija 115.31 4108896 Added new validations for First, Last
and Middle name in validate_input
function. Also changed emp_values
cursor to select only non-zero
NI records.
01-MAR-2005 rmakhija 115.32 4216135 Changed emp_values to make sure
atleast NI Cat X is reported
when there is not enough earning
therefore NI balances are 0
11-MAR-2005 rmakhija 115.33 4234348 Changed emp_values to make sure
Ni Cats with 0 lel/et/uel are
processed first so that contribs
in these NI Cats can be rolled up
into another NI Cat
19-MAY-2005 rmakhija 115.34 4362883 Change submit_reports to set
printer and copies oprions
as entered by the user on EOY
request before submitting the
reports.
09-JUN-2005 rmakhija 115.35 Added nvl,ltrim and rtrim around
first_name, middle_names, title
and country to handle spaces in
these fields as null values.
16-JUN-2005 rmakhija 115.36 Increased length of some number
variables in this package so
pl/sql error is not raised by
this package when value is too
large but a user friendly error
message will be raised by the EOY
formula
14-Nov-2005 rmakhija 115.37 Changed for EOY 2005-06
01-Dec-205 mgera 115.38 Added extra validation for SCON check.
in validate_input function
02-Dec-2005 rmakhija 115.39 Further changes for EOY 2005-06
10-JAN-2005 rmakhija 115.40 Further changes for EOY 2005-06
08-FEB-2006 kthampan 115.41 Added validation for P11D_EDI
08-DEC-2006 rmakhija 115.42 EOY 2006-07 changes
21-JAN-2007 rmakhija 115.43 Excluded NI Cat C from aggregated
validations. Also added sum of
total contributions as a
parameter to the P14 emp trailer
formula.
25-Nov-2007 A.Ganguly 115.44 Added validate_tax_code_1
29-Oct-2007 pbalu 115.45 6281170 Added a new parameter for Formula
PAY_GB_EDI_P14_EMP_TRAILER
29-Oct-2007 rlingama 115.46 5671777 BUG 5671777-5 Changed Start date of the EOY process
to reflect start of the tax year.so no need to add
12 months to the start date
2-Nov-2007 parusia 115.47 6345375 Included 2 additional validation modes for
in validate_input function for validating Last_name
and First_name in P45(3) and P46 PENNOT
13-Nov-2007 A.Ganguly 115.48 6345375 Added function get_payroll_version
for the EOY Apr 08 Changes
26-Nov-2007 parusia 115.49 6345375 Added validation modes in validate_input()
for PostalCode and Title.
Added code to remove leading minus
sign from NUMBER_1 validate_mode.
28-Nov-2007 parusia 115.50 6345375 Remove hardcoded 'apps' from csr_get_version
as it was failing in GSCC checks.
30-Nov-2007 parusia 115.51 6345375 Removed numbers from valid character set for
P45_46_FIRST_NAME, P45_46_LAST_NAME, P45_46_TITLE
30-NOV-2007 pbalu 115.52 6281170 To change the condition for contribution
rollup and LEL rollup as part of EOY 07/08
20-Nov-2008 namgoyal 115.55 7540858 Allowed space as a valid character in P45_46_FIRST_NAME
19-DEC-2009 vijranga 115.56 7043405 LEL Rollup condition (added for EOY 07/08) reverted back.
17-Mar-2009 rlingama 115.57 8338575 Removed the first character validation for address lines
22-Apr-2009 dwkrishn 115.58 8439388 Last Name should not have '.' Full stop in the char set
18-Jun-2009 pbalu 115.59/60 8357870 Created new formula based on PAY_GB_EDI_P14_NI_DETAILS to
enable users to run EOY for reconciliation.
25-Jun-2009 pbalu 115.61 8357870 To pass NI UAP balance value to PAY_GB_EDI_P14_NI_DETAILS_INTERIM
21-Aug-2009 krreddy 115.62 8541978 Added PRAGMA statement in the procedure submit_recon_report.
10-Sep-2009 npannamp 115.63 8816832 Implement 2009-10 EOY validations as in MIG
03-Nov-2009 namgoyal 115.64 8986543 Added mode P46_CAR_TIT_N_FSTNM in validate_input for
P46 Car EDI version3
05-Nov-2009 npannamp 115.65 8833756 2009-10 EOY - Added check to identify single assignments with
NI Aggregation flag set wrongly.
05-Nov-2009 npannamp 115.66 8833756 Code review comments incorporated.
26-Feb-2010 npannamp 115.67 9414865 Bug Fix in create_record_type1 procedure.
06-Oct-2010 npannamp 115.68 10188309 EOY 10/11 Changes.
18-Feb-2011 krreddy 115.69 10066755 Modified to avoid impact of enabling skip term leg rule
04-Jul-2011 pprvenka 115.70 12694562 EOY 11/12 Changes.
18-Jul-2011 pprvenka 115.71 12765309 EOY 11/12 Changes. Included 2 parameters for the
formula PAY_GB_EDI_P14_NI_DETAILS
08-OCT-12 sampmand 115.72 14729775 EOY 2012/13 changes for bug
17-OCT-12 sampmand 115.73 14729775 EOY 2012/13 changes-updated comments
17-OCT-12 sampmand 115.74 14729775 EOY 2012/13 changes-updated comments
18-NOV-12 sampmand 115.75 16090623 prm_values reduced for 26 to 24 for PAY_GB_EDI_P14_NI_DETAILS formula
*/
fetch_new_header BOOLEAN := TRUE; -- Shows if new header record needed
select to_char(fnd_date.canonical_to_date(max(ni.global_value)),'YYYY')
from ff_globals_f ni
where ni.global_name = 'NI_NEW_TAX_YEAR'
and ni.business_group_id is null
and ni.legislation_code = 'GB';
SELECT UPPER(a.permit_number)
,a.payroll_id
,lpad(TO_CHAR(a.tax_district_reference),3,'0')
,a.tax_reference_number
,NVL(TO_CHAR(a.tax_year),' ') -- 4011263
,a.employers_name
/* 4752018 - EOY 2005-06
,a.employers_address_line
4752018 */
/* Start 4011263
,UPPER(NVL(a.econ,'?'))
,nvl(a.ssp_recovered,0)
,nvl(a.smp_recovered,0)
,nvl(a.smp_compensation,0)
--Added the below four fields for P35/P14 EOY 2003/2004
,nvl(a.spp_recovered,0)
,nvl(a.spp_compensation,0)
,nvl(a.sap_recovered,0)
,nvl(a.sap_compensation,0)
End 4011263 */
FROM pay_gb_year_end_payrolls_v a
WHERE a.payroll_action_id = c_payroll_action_id
AND EXISTS (SELECT '1'
FROM pay_assignment_actions paa,
ff_user_entities fue,
ff_archive_items fai
WHERE paa.payroll_action_id = a.payroll_action_id
AND fue.user_entity_name = 'X_PAYROLL_ID'
AND fai.user_entity_id = fue.user_entity_id
AND fai.context1 = paa.assignment_action_id
AND fai.value = to_char(a.payroll_id))
ORDER BY a.tax_district_reference, a.tax_reference_number, a.permit_number,a.payroll_id;
SELECT
max(decode(fue2.user_entity_name,'X_ASSIGNMENT_NUMBER',
fai2.VALUE))
,act.assignment_action_id
,nvl(max(decode(fue2.user_entity_name,'X_LAST_NAME',
substr(fai2.value,1,35))),' ') LAST_NAME
,nvl(max(decode(fue2.user_entity_name,'X_FIRST_NAME',
SUBSTR(ltrim(rtrim(fai2.value)),1,35))), ' ') FIRST_NAME
,nvl(max(decode(fue2.user_entity_name,'X_MIDDLE_NAME', SUBSTR(ltrim(rtrim(fai2.value)),1,35))), ' ')
,nvl(max(decode(fue2.user_entity_name,'X_TITLE', SUBSTR(ltrim(rtrim(fai2.value)),1,35))), ' ')
,nvl(max(decode(fue2.user_entity_name,'X_DATE_OF_BIRTH',
TO_CHAR(fnd_date.canonical_to_date(fai2.value),'DDMMYYYY'))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_SEX', substr(UPPER(fai2.value),1,1))),' ')
,nvl(ltrim(max(decode(fue2.user_entity_name,'X_ADDRESS_LINE1',
decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
,nvl(ltrim(max(decode(fue2.user_entity_name,'X_ADDRESS_LINE2',
decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
,nvl(ltrim(max(decode(fue2.user_entity_name,'X_ADDRESS_LINE3',
decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
,nvl(ltrim(max(decode(fue2.user_entity_name,'X_TOWN_OR_CITY',
decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
,nvl(max(decode(fue2.user_entity_name,'X_COUNTRY', -- 4011263
decode(fai2.value,'','',rpad(ltrim(rtrim(fai2.value)),27)))), ' ')
,nvl(max(decode(fue2.user_entity_name,'X_POSTAL_CODE',
substr(fai2.value,1,9))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_TAX_CODE',
ltrim(rtrim(UPPER(fai2.value))))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_W1_M1_INDICATOR',
substr(UPPER(fai2.value),1,1))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',
substr(UPPER(fai2.value),1,9))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_SSP', to_number(fai2.value))),0)
,nvl(max(decode(fue2.user_entity_name,'X_SMP', to_number(fai2.value))),0)
-- Added the below 2 fields for P35/P14 EOY 2003/2004
,nvl(max(decode(fue2.user_entity_name,'X_SPP_ADOPT', to_number(fai2.value))),0) -- for SPP
,nvl(max(decode(fue2.user_entity_name,'X_SPP_BIRTH', to_number(fai2.value))),0) -- for SPP
,nvl(max(decode(fue2.user_entity_name,'X_ASPP_ADOPT', to_number(fai2.value))),0) -- for ASPP EOY 2012/13
,nvl(max(decode(fue2.user_entity_name,'X_ASPP_BIRTH', to_number(fai2.value))),0) -- for ASPP
,nvl(max(decode(fue2.user_entity_name,'X_SAP', to_number(fai2.value))),0) -- for SAP
/*4011263: Gross Pay not needed anymore
,nvl(max(decode(fue2.user_entity_name,'X_GROSS_PAY',to_number(fai2.VALUE))),0) gross_pay
*/
--
,decode(max(decode(fue2.user_entity_name,'X_TAX_REFUND',substr(fai2.VALUE,1,1))), 'R',
NVL(-1*max(decode(fue2.user_entity_name,'X_TAX_PAID',to_number(fai2.VALUE))),0),
NVL(max(decode(fue2.user_entity_name,'X_TAX_PAID',to_number(fai2.VALUE))),0)) tax_paid
--
,nvl(max(decode(fue2.user_entity_name,'X_TAX_REFUND',
substr(UPPER(fai2.value),1,1))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',
to_number(fai2.value))),0) previous_taxable
,nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAX_PAID', to_number(fai2.value))),0)
,nvl(max(decode(fue2.user_entity_name,'X_START_OF_EMP',
TO_CHAR(fnd_date.canonical_to_date(fai2.value),'DDMMYYYY'))),' ')
,max(decode(fue2.user_entity_name,'X_TERMINATION_DATE',
TO_CHAR(fnd_date.canonical_to_date(fai2.value),'DDMMYYYY')))
,nvl(max(decode(fue2.user_entity_name,'X_WIDOWS_AND_ORPHANS',
ROUND(to_number(fai2.value)/100))),0)
,nvl(max(decode(fue2.user_entity_name,'X_STUDENT_LOANS', trunc(fai2.value/100))),0) student_loans
,nvl(max(decode(fue2.user_entity_name,'X_WEEK_53_INDICATOR',
substr(UPPER(fai2.value),1,1))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_TAXABLE_PAY', to_number(fai2.value))),0) taxable_pay
/* 4011263
,nvl(max(decode(fue2.user_entity_name,'X_PENSIONER_INDICATOR',
substr(UPPER(fai2.value),1,1))),' ')
4011263 */
,nvl(max(decode(fue2.user_entity_name,'X_DIRECTOR_INDICATOR',
substr(UPPER(fai2.value),1,1))),' ')
,act.assignment_id
,max(decode(fue2.user_entity_name,'X_EFFECTIVE_END_DATE',
fnd_date.canonical_to_date(fai2.value)))
,nvl(max(decode(fue2.user_entity_name,'X_ASSIGNMENT_MESSAGE',
SUBSTR(fai2.VALUE, 1,60))),' ')
,nvl(max(decode(fue2.user_entity_name,'X_MULTIPLE_ASG_FLAG',
SUBSTR(fai2.VALUE, 1,1))),' ')
FROM
ff_archive_items fai1,
ff_user_entities fue1,
ff_archive_items fai2,
ff_user_entities fue2,
pay_assignment_actions act
WHERE act.assignment_action_id = fai1.context1
AND act.payroll_action_id = c_payroll_action_id
--AND act.action_status = 'C'
AND act.action_status in ('C','S') --Modified for the bug 10066755
AND fue1.legislation_code = 'GB'
AND fue1.user_entity_name = 'X_PAYROLL_ID'
AND fue1.business_group_id IS NULL
AND fue1.user_entity_id + decode(act.assignment_action_id,0,0,0) = fai1.user_entity_id
and fai1.value = to_char(c_payroll_id)
AND fue2.user_entity_id = fai2.user_entity_id
AND fai2.context1 = act.assignment_action_id
GROUP BY
act.assignment_action_id
, act.assignment_id
HAVING
(
nvl(max(decode(fue2.user_entity_name,'X_AGGREGATED_PAYE_FLAG', fai2.value)), 'N')='N'
OR (
nvl(max(decode(fue2.user_entity_name,'X_AGGREGATED_PAYE_FLAG', fai2.value)), 'N')='Y'
AND nvl(max(decode(fue2.user_entity_name,'X_EOY_PRIMARY_FLAG', fai2.value)), 'N')='Y'
)
)
AND
(
nvl(max(decode(fue2.user_entity_name, 'X_TAXABLE_PAY', to_number(fai2.value))),0) <> 0
OR NVL(max(decode(fue2.user_entity_name,'X_TAX_PAID',to_number(fai2.VALUE))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_STUDENT_LOANS', trunc(fai2.value/100))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAXABLE_PAY', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAX_PAID', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SSP', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SMP', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SAP', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SPP_ADOPT', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_SPP_BIRTH', to_number(fai2.value))),0) <> 0
OR nvl(max(decode(fue2.user_entity_name,'X_REPORTABLE_NI', fai2.value)),'N') <> 'N'
)
ORDER BY last_name, first_name;
SELECT /*NVL(UPPER(a.scon),' ') scon
,*/UPPER(a.ni_category_code) cat_code
FROM pay_gb_year_end_values_v a
WHERE a.assignment_action_id = c_assignment_action_id
AND a.reportable <> 'N'
AND NVL(trunc(a.ni_able_uel/100),0) > 0
AND NVL(a.employees_contributions,0) > 0
AND UPPER(a.ni_category_code) <> 'X'
AND UPPER(a.ni_category_code) <> 'C'
ORDER BY NVL(trunc(a.ni_able_uel/100),0), NVL(a.employees_contributions,0),
UPPER(a.ni_category_code)/*, NVL(UPPER(a.scon),' ')*/ DESC;
SELECT UPPER(a.ni_category_code) cat_code
FROM pay_gb_year_end_values_v a
WHERE a.assignment_action_id = c_assignment_action_id
AND a.reportable <> 'N'
AND NVL(trunc(a.ni_able_et/100),0) > 0
AND NVL(trunc(a.ni_able_lel/100),0) > 0
AND UPPER(a.ni_category_code) <> 'X'
ORDER BY NVL(trunc(a.ni_able_et/100),0),
NVL(trunc(a.ni_able_lel/100),0),
NVL(a.employees_contributions,0),
UPPER(a.ni_category_code)/*, NVL(UPPER(a.scon),' ')*/ DESC;
SELECT NVL(sum(trunc(a.ni_able_lel/100)),0) tot_ni_able_lel
FROM pay_gb_year_end_values_v a
WHERE a.assignment_action_id = c_assignment_action_id
AND a.reportable <> 'N'
AND UPPER(a.ni_category_code) <> 'X'
-- Check LEL > 0 but ET, UEL, EE and ER Conrib = 0
-- Bug#7043405 LEL Rollup condition reverted back
AND NVL(a.total_contributions,0) = 0 -- EOY 07/08 removed Total contribution from LEL roll up
AND NVL(trunc(a.ni_able_et/100),0) = 0
AND NVL(trunc(a.ni_able_uap/100),0) = 0 -- 8816832 EOY 09/10
AND NVL(trunc(a.ni_able_uel/100),0) = 0
-- Bug#7043405 LEL Rollup condition reverted back
AND NVL(a.employees_contributions,0) = 0 -- EOY 07/08 removed Employee contribution from LEL roll up
AND NVL(trunc(a.ni_able_lel/100),0) > 0;
SELECT /*NVL(UPPER(a.scon),' ') scon
,*/UPPER(a.ni_category_code) cat_code
,NVL(a.total_contributions,0) tot_cont
,NVL(a.employees_contributions,0) emps_cont
,NVL(trunc(a.ni_able_et/100),0) ni_able_et
,NVL(trunc(a.ni_able_lel/100),0) ni_able_lel
,NVL(trunc(a.ni_able_uel/100),0) ni_able_uel
,NVL(trunc(a.ni_able_uap/100),0) ni_able_uap -- 8357870
,NVL(trunc(a.ni_able_auel/100),0) ni_able_auel --EOY 07/08 added AUEL for contributions rollup
,NVL(a.employers_rebate,0) employers_rebate
,NVL(a.employees_rebate,0) employees_rebate
FROM pay_gb_year_end_values_v a
WHERE a.assignment_action_id = c_assignment_action_id
AND a.reportable <> 'N'
-- Check atleast one value is non-zero to report
AND NOT (NVL(a.total_contributions,0) = 0
AND NVL(trunc(a.ni_able_et/100),0) = 0
AND NVL(trunc(a.ni_able_lel/100),0) = 0
AND NVL(trunc(a.ni_able_uap/100),0) = 0 -- 8816832 EOY 09/10
AND NVL(trunc(a.ni_able_uel/100),0) = 0
AND NVL(a.employees_contributions,0) = 0)
UNION -- Added union to fix 4216135
SELECT /*' ' EOY 2012/2013 remove '' to match no of cols
,*/'X'
,0
,0
,0
,0 -- 8357870
,0 --EOY 07/08
,0
,0
,0
,0
FROM dual
WHERE NOT EXISTS
(SELECT 1 FROM pay_gb_year_end_values_v b
WHERE b.assignment_action_id = c_assignment_action_id
AND b.reportable <> 'N'
AND NOT (NVL(b.total_contributions,0) = 0
AND NVL(trunc(b.ni_able_et/100),0) = 0
AND NVL(trunc(b.ni_able_lel/100),0) = 0
AND NVL(trunc(b.ni_able_uap/100),0) = 0 -- 8816832 EOY 09/10
AND NVL(trunc(b.ni_able_uel/100),0) = 0
AND NVL(b.employees_contributions,0) = 0))
/* Bug Fix 8816832 EOY 09/10 Included UAP also in order by
ORDER BY 6, 5, 7, 2, 1; -- order by clause added for 4234348 to ensure Ni Cats with 0 lel/et/uel are processed first */
SELECT 1
FROM ff_archive_item_contexts fac,
ff_archive_items fai,
ff_user_entities fue,
ff_archive_items fai2,
ff_user_entities fue2,
pay_assignment_actions paa
WHERE paa.payroll_action_id = c_payroll_action_id
AND fue.user_entity_name = 'X_NI_TOTAL_CONTRIBUTIONS'
AND fue.user_entity_id + decode(paa.assignment_action_id,0,0,0)
= fai.user_entity_id
AND fue.legislation_code = 'GB'
AND fai.context1 = paa.assignment_action_id
AND fai.archive_item_id = fac.archive_item_id
AND fac.sequence_no = 2
AND fac.context in ('D','E','L') --P35/P14 EOY 2003/2004
AND fue2.user_entity_name = 'X_PAYROLL_ID'
AND fue2.user_entity_id + decode(paa.assignment_action_id,0,0,0)
= fai2.user_entity_id
AND fue2.legislation_code = 'GB'
AND fai2.context1 = paa.assignment_action_id
AND decode (c_tax_dist_ref,NULL,1,
pay_gb_eoy_archive.get_arch_num(c_payroll_action_id,
'X_TAX_DISTRICT_REFERENCE',fai2.value),1,0) = 1
AND decode (c_tax_ref_no,NULL,1,
pay_gb_eoy_archive.get_arch_str(c_payroll_action_id,
'X_TAX_REFERENCE_NUMBER',fai2.value),1,0) = 1
AND decode (c_permit_no,NULL,1,
pay_gb_eoy_archive.get_arch_str(c_payroll_action_id,
'X_PERMIT_NUMBER',fai2.value),1,0) = 1;
SELECT printer, number_of_copies
FROM fnd_concurrent_requests
WHERE request_id = fnd_global.conc_request_id;
SELECT printer, number_of_copies
FROM fnd_concurrent_requests
WHERE request_id = fnd_global.conc_request_id;
select person_id from per_all_assignments_f where
assignment_id=c_assignment_id order by effective_end_date DESC;
select count(distinct master.assignment_id) from
( SELECT /*+ ORDERED INDEX (asg PER_ASSIGNMENTS_F_N12,
ppf PAY_PAYROLLS_F_PK,
flex HR_SOFT_CODING_KEYFLEX_PK,
org HR_ORGANIZATION_INFORMATIO_FK1)
USE_NL(asg,ppf,flex,org) */
distinct asg.assignment_id, asg.effective_start_date, asg.effective_end_date
FROM per_all_assignments_f asg,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex flex,
hr_organization_information org
WHERE asg.person_id = p_person_id
AND asg.effective_end_date >= p_min_start_year_date
AND asg.effective_start_date <= p_max_end_year_date
AND asg.payroll_id = ppf.payroll_id
AND asg.period_of_service_id is not null
AND ppf.effective_end_date >= p_min_start_year_date
AND ppf.effective_start_date <= p_max_end_year_date
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND asg.business_group_id +0 = org.organization_id
AND org.org_information_context =
'Tax Details References'||decode(flex.segment1,'','','')
AND org.org_information1 = flex.segment1
AND nvl(org.org_information10,'UK') = 'UK'
AND nvl(p_tax_dist_ref, substr(flex.segment1,1,3)) =
substr(flex.segment1,1,3)
AND nvl(p_tax_ref, substr(ltrim(substr(org_information1,4,11),'/') ,1,10))
= substr(ltrim(substr(org_information1,4,11),'/') ,1,10)
) master,
( SELECT /*+ ORDERED INDEX (asg PER_ASSIGNMENTS_F_N12,
ppf PAY_PAYROLLS_F_PK,
flex HR_SOFT_CODING_KEYFLEX_PK,
org HR_ORGANIZATION_INFORMATIO_FK1)
USE_NL(asg,ppf,flex,org) */
distinct asg.assignment_id, asg.effective_start_date, asg.effective_end_date
FROM per_all_assignments_f asg,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex flex,
hr_organization_information org
WHERE asg.person_id = p_person_id
AND asg.effective_end_date >= p_min_start_year_date
AND asg.effective_start_date <= p_max_end_year_date
AND asg.payroll_id = ppf.payroll_id
AND asg.period_of_service_id is not null
AND ppf.effective_end_date >= p_min_start_year_date
AND ppf.effective_start_date <= p_max_end_year_date
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND asg.business_group_id +0 = org.organization_id
AND org.org_information_context =
'Tax Details References'||decode(flex.segment1,'','','')
AND org.org_information1 = flex.segment1
AND nvl(org.org_information10,'UK') = 'UK'
AND nvl(p_tax_dist_ref, substr(flex.segment1,1,3)) =
substr(flex.segment1,1,3)
AND nvl(p_tax_ref, substr(ltrim(substr(org_information1,4,11),'/') ,1,10))
= substr(ltrim(substr(org_information1,4,11),'/') ,1,10)
) child
where (master.effective_start_date between child.effective_start_date and child.effective_end_date
or
master.effective_end_date between child.effective_start_date and child.effective_end_date)
and master.assignment_id <> child.assignment_id;
SELECT a.formula_id
FROM ff_formulas_f a,
ff_formula_types t
WHERE a.formula_name = p_formula_name
AND a.formula_type_id = t.formula_type_id
AND t.formula_type_name = 'Oracle Payroll';
SELECT upper(nvl(org_information11,' ')) edi_sender_id,
pact.request_id
FROM pay_payroll_actions pact,
hr_organization_information hoi
WHERE pact.payroll_action_id = p_payroll_action_id
AND hoi.org_information_context = 'Tax Details References'
AND hoi.org_information1 = g_tax_district_ref||'/'||g_tax_ref_no
AND hoi.organization_id = pact.business_group_id;
SELECT nvl(org_information13,' ') edi_submitter_no
FROM pay_payroll_actions pact,
hr_organization_information hoi
WHERE pact.payroll_action_id = p_payroll_action_id
AND hoi.org_information_context = 'Tax Details References'
AND hoi.org_information1 = g_tax_district_ref||'/'||g_tax_ref_no
AND hoi.organization_id = pact.business_group_id;
select report_category
from pay_payroll_actions
where payroll_action_id = c_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 to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy')
-- add_months(to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy'),12)
-- End of BUG 5671777-5
start_year,
effective_date end_year
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
hr_utility.trace('Update rollup figures.');
select
assignment_id
from pay_assignment_actions
where assignment_action_id = c_assignment_action_id;
select ver.version from
ad_file_versions ver, ad_files f
where f.file_id = ver.file_id
and f.filename = 'pygbffedi.hdt'
order by ver.file_version_id desc;