eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_YEAR_END_EXTRACT dependencies on PAY_GB_YEAR_END_ASSIGNMENTS

Line 57: for update of pay_gb_year_end_assignments

53: Added TITLE to extract from per_people_f
54: Used cursor for SCON NIF, G and S balances.
55: amills 16-Jan-1998 Bug 572938. Added a new update statement
56: 'Retrieve element entry update recurring'
57: for update of pay_gb_year_end_assignments
58: where an element entry exists that is the
59: result of an update recurring rule. The
60: logic is altered to check for this rule
61: first before locating run result value,

Line 129: pay_gb_year_end_assignments ass,

125: SELECT COUNT(*)
126: INTO l_count
127: FROM
128: pay_gb_year_end_payrolls pay,
129: pay_gb_year_end_assignments ass,
130: pay_gb_year_end_values val
131: WHERE
132: pay.payroll_id = ass.payroll_id
133: AND ass.assignment_id = val.assignment_id

Line 254: select '1' from pay_gb_year_end_assignments ye_asg,

250: -- delete all the rows in values for this permit , if no permit specified
251: -- delete all the rows for this tax_district_reference else if no
252: -- tax_district_reference specified delete all rows within the business group
253: delete from pay_gb_year_end_values v where exists (
254: select '1' from pay_gb_year_end_assignments ye_asg,
255: pay_payrolls_f p,
256: hr_soft_coding_keyflex flex,
257: hr_organization_information org
258: where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id

Line 282: delete from pay_gb_year_end_assignments ye_asg where exists (

278: hr_utility.set_location('PAY_YEAR_END_EXTRACT.TRASH',4);
279: -- delete all the rows in assignments for this permit , if no permit specified
280: -- delete all the rows for this tax_district_reference else if no
281: -- tax_district_reference specified delete all rows within the business group
282: delete from pay_gb_year_end_assignments ye_asg where exists (
283: select '1' from pay_payrolls_f p,
284: hr_soft_coding_keyflex flex, hr_organization_information org
285: where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
286: and org.ORG_INFORMATION_CONTEXT = 'Tax Details References'

Line 374: from pay_gb_year_end_assignments

370: CURSOR get_people is
371: -- fetch all the person rows
372: select assignment_id, effective_end_date, sex, payroll_id,
373: previous_year_asg_action_id, last_asg_action_id, termination_type
374: from pay_gb_year_end_assignments
375: where request_id = p_request_id;
376: --
377:
378: CURSOR get_invalid_multiple_asg is

Line 383: from pay_gb_year_end_assignments ye_asg

379: -- fetch any ye_asg rows that have people with assignments in different
380: -- permits but within the same tax reference
381:
382: select ye_asg.rowid ye_asg_rowid
383: from pay_gb_year_end_assignments ye_asg
384: , pay_gb_year_end_payrolls ye_roll
385: where exists ( select person_id
386: from per_assignments_f asg,
387: pay_gb_year_end_payrolls yep2

Line 402: from pay_gb_year_end_assignments

398: CURSOR get_multi_asg_people is
399: -- fetch all the person rows
400: select assignment_id, effective_end_date, sex, payroll_id,
401: previous_year_asg_action_id, last_asg_action_id, termination_type
402: from pay_gb_year_end_assignments
403: where request_id = p_request_id
404: and eoy_primary_flag = 'Y';
405: --
406: --

Line 419: from pay_gb_year_end_assignments yea_prim,

415: sum(yev.TOTAL_CONTRIBUTIONS) s_tot_con,
416: sum(yev.EMPLOYEES_CONTRIBUTIONS) s_ees_con,
417: sum(yev.EARNINGS_CONTRACTED_OUT) s_earnings_co,
418: sum(yev.CONTRIBUTIONS_CONTRACTED_OUT) s_con_co
419: from pay_gb_year_end_assignments yea_prim,
420: pay_gb_year_end_assignments ye_asg,
421: pay_gb_year_end_values yev,
422: pay_gb_year_end_payrolls yep_prim,
423: pay_gb_year_end_payrolls ye_roll

Line 420: pay_gb_year_end_assignments ye_asg,

416: sum(yev.EMPLOYEES_CONTRIBUTIONS) s_ees_con,
417: sum(yev.EARNINGS_CONTRACTED_OUT) s_earnings_co,
418: sum(yev.CONTRIBUTIONS_CONTRACTED_OUT) s_con_co
419: from pay_gb_year_end_assignments yea_prim,
420: pay_gb_year_end_assignments ye_asg,
421: pay_gb_year_end_values yev,
422: pay_gb_year_end_payrolls yep_prim,
423: pay_gb_year_end_payrolls ye_roll
424: where yea_prim.eoy_primary_flag = 'Y'

Line 891: insert into pay_gb_year_end_assignments (

887: --
888: --
889: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',12);
890: hr_utility.trace( 'extract the latest assignments ' );
891: insert into pay_gb_year_end_assignments (
892: assignment_id,
893: payroll_id,
894: effective_end_date,
895: request_id ,

Line 946: insert into pay_gb_year_end_assignments (

942: hr_utility.trace( 'extract the tax reference transfer asgs' );
943: -- revised the select to only extract payroll transfers and
944: -- to check the tax reference is different date effective the start date of new
945: -- asg assumption here is that the old payroll still exists the day after transfer
946: insert into pay_gb_year_end_assignments (
947: assignment_id,
948: payroll_id,
949: effective_end_date,
950: request_id ,

Line 993: and not exists ( select '1' from pay_gb_year_end_assignments ye_asg

989: PROLL.effective_start_date and PROLL.effective_end_date
990: and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
991: and ASS.payroll_id <> PASS.payroll_id
992: and FLEX.segment1 <> PFLEX.segment1
993: and not exists ( select '1' from pay_gb_year_end_assignments ye_asg
994: where ye_asg.assignment_id = pass.assignment_id
995: and ye_asg.effective_end_date = pass.effective_end_date )
996: and exists ( select null from pay_gb_year_end_payrolls ye_roll
997: where ye_roll.payroll_id = pass.payroll_id

Line 1014: update pay_gb_year_end_assignments ye_asg set ( effective_start_date ) =

1010: /* from the base table */
1011: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',14);
1012: hr_utility.trace( 'set the effective_start_date for transfers' );
1013: if p_permit is null and p_tax_district_ref is null then -- [ for null permit
1014: update pay_gb_year_end_assignments ye_asg set ( effective_start_date ) =
1015: (select max(effective_end_date) + 1 from pay_gb_year_end_assignments yea2
1016: where yea2.assignment_id = ye_asg.assignment_id
1017: and yea2.effective_end_date < ye_asg.effective_end_date )
1018: where ye_asg.request_id = p_request_id;

Line 1015: (select max(effective_end_date) + 1 from pay_gb_year_end_assignments yea2

1011: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',14);
1012: hr_utility.trace( 'set the effective_start_date for transfers' );
1013: if p_permit is null and p_tax_district_ref is null then -- [ for null permit
1014: update pay_gb_year_end_assignments ye_asg set ( effective_start_date ) =
1015: (select max(effective_end_date) + 1 from pay_gb_year_end_assignments yea2
1016: where yea2.assignment_id = ye_asg.assignment_id
1017: and yea2.effective_end_date < ye_asg.effective_end_date )
1018: where ye_asg.request_id = p_request_id;
1019:

Line 1028: update pay_gb_year_end_assignments ye_asg set ( effective_start_date ) =

1024: /* big overhead here is testing the value of tax reference date effective
1025: as of the payroll transfer - could we instead assume that tax reference
1026: on the payroll is not updated mid year? If so the following is simplified */
1027: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',16);
1028: update pay_gb_year_end_assignments ye_asg set ( effective_start_date ) =
1029: ( select max(ASS.effective_start_date)
1030: from per_assignments_f ASS
1031: ,pay_payrolls_f NROLL
1032: ,hr_soft_coding_keyflex FLEX

Line 1062: UPDATE PAY_GB_YEAR_END_ASSIGNMENTS ye_asg

1058: end if; -- ] null permit
1059: --
1060: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',18);
1061: hr_utility.trace( 'find the last action from the current year' );
1062: UPDATE PAY_GB_YEAR_END_ASSIGNMENTS ye_asg
1063: SET (LAST_ASG_ACTION_ID, LAST_EFFECTIVE_DATE) = (
1064: select assact.assignment_action_id,
1065: pact.effective_date
1066: from pay_payroll_actions pact,

Line 1102: update pay_gb_year_end_assignments ye_asg set

1098: plog ( '_ latest assignment_action found '||to_char(SQL%ROWCOUNT));
1099: --
1100: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',20);
1101: hr_utility.trace( 'Pick up person details as of stat_end_date' );
1102: update pay_gb_year_end_assignments ye_asg set
1103: ( LAST_NAME, FIRST_NAME, MIDDLE_NAME, DATE_OF_BIRTH, TITLE,
1104: EXPENSE_CHECK_SEND_TO_ADDRESS, NATIONAL_INSURANCE_NUMBER, SEX,
1105: PENSIONER_INDICATOR, MULTIPLE_ASG_FLAG) =
1106: ( select substr(last_name, 1,20) LAST_NAME,

Line 1126: update pay_gb_year_end_assignments ye_asg set ( DIRECTOR_INDICATOR ) =

1122: --
1123: -- was this employee ever a director this year
1124: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',22);
1125: hr_utility.trace( 'set director indicator ' );
1126: update pay_gb_year_end_assignments ye_asg set ( DIRECTOR_INDICATOR ) =
1127: ( select 'D' from dual where exists ( select '1' from
1128: per_people_f per
1129: where ye_asg.person_id = per.person_id
1130: and per.effective_start_date <= l_end_year

Line 1139: update pay_gb_year_end_assignments ye_asg

1135:
1136: --
1137: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',24);
1138: hr_utility.trace( 'set termination date' );
1139: update pay_gb_year_end_assignments ye_asg
1140: set ( termination_date, termination_type ) =
1141: (select actual_termination_date ,'L'
1142: from per_periods_of_service pos,
1143: per_assignments_f asg,

Line 1178: update pay_gb_year_end_assignments ye_asg

1174: --
1175: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',25);
1176: hr_utility.trace( 'Set start of Employment' );
1177: --
1178: update pay_gb_year_end_assignments ye_asg
1179: set (start_of_emp) =
1180: (select pos.date_start
1181: from per_periods_of_service pos,
1182: per_assignments_f asg,

Line 1207: update pay_gb_year_end_assignments ye_asg

1203: --
1204: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',26);
1205: hr_utility.trace( 'Set Addresses' );
1206: --
1207: update pay_gb_year_end_assignments ye_asg
1208: set (address_line1, address_line2, address_line3, town_or_city,
1209: county, postal_code) = (
1210: select pad.address_line1,
1211: pad.address_line2,

Line 1232: UPDATE PAY_GB_YEAR_END_ASSIGNMENTS ye_asg

1228: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',27);
1229: hr_utility.trace( 'find the last action for last year' );
1230: --
1231: if p_niy = 'Y' then -- [ report NIY
1232: UPDATE PAY_GB_YEAR_END_ASSIGNMENTS ye_asg
1233: SET (PREVIOUS_YEAR_ASG_ACTION_ID, PREVIOUS_YEAR_EFFECTIVE_DATE) = (
1234: select assact.assignment_action_id,
1235: pact.effective_date
1236: from pay_payroll_actions pact,

Line 1305: delete from pay_gb_year_end_assignments

1301: --
1302: -- delete rows for last year that have no NI Y
1303: if ( l_niy is null or l_niy = 0 ) -- [
1304: and l_people.LAST_ASG_ACTION_ID is null then
1305: delete from pay_gb_year_end_assignments
1306: where assignment_id = l_people.assignment_id
1307: and effective_end_date = l_people.effective_end_date;
1308: end if; -- ]
1309: --

Line 1325: delete from pay_gb_year_end_assignments

1321: end if; -- ]
1322: -- MII
1323: else -- report NIY is not 'Y'
1324: if (l_people.LAST_ASG_ACTION_ID is null) then
1325: delete from pay_gb_year_end_assignments
1326: where assignment_id = l_people.assignment_id
1327: and effective_end_date = l_people.effective_end_date;
1328: end if;
1329: end if; -- ] report NIY

Line 1636: update pay_gb_year_end_assignments ye_asg set

1632: l_super := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_super_id);
1633: l_widow := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_widow_id);
1634: l_taxable := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_taxable_id);
1635: l_count := l_count + 6;
1636: update pay_gb_year_end_assignments ye_asg set
1637: SSP = l_ssp,
1638: SMP = l_smp,
1639: GROSS_PAY = l_gross,
1640: TAX_PAID = l_paye,

Line 1656: update pay_gb_year_end_assignments ye_asg set ( TAX_RUN_RESULT_ID ) =

1652: begin -- ( update non bal info
1653: /* most people will have had paye calculated on the last run - pick these up */
1654: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',30);
1655: hr_utility.trace( 'see if tax was calculated on the last run of the year' );
1656: update pay_gb_year_end_assignments ye_asg set ( TAX_RUN_RESULT_ID ) =
1657: (select RUN_RESULT_ID from pay_run_results r
1658: where r.element_type_id = l_paye_details_id
1659: and r.status in ('P', 'PA')
1660: and r.assignment_action_id = ye_asg.LAST_ASG_ACTION_ID )

Line 1667: update pay_gb_year_end_assignments ye_asg set ( TAX_RUN_RESULT_ID ) =

1663: --
1664: /* if there are any who have no tax update find the latest update */
1665: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',32);
1666: hr_utility.trace( 'find the latest tax calculation in the year' );
1667: update pay_gb_year_end_assignments ye_asg set ( TAX_RUN_RESULT_ID ) =
1668: (select r.RUN_RESULT_ID
1669: from pay_assignment_actions assact,
1670: pay_run_results r
1671: where r.element_type_id+0 = l_paye_details_id + decode(assact.assignment_id,null,0,0)

Line 1700: update pay_gb_year_end_assignments ye_asg set

1696: -- this was achieved using an Update Recurring rule.
1697: --
1698: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',33);
1699: hr_utility.trace( 'Retrieve element entry update recurring');
1700: update pay_gb_year_end_assignments ye_asg set
1701: ( TAX_CODE, W1_M1_INDICATOR,PREVIOUS_TAXABLE_PAY,PREVIOUS_TAX_PAID) =
1702: (select max(decode(iv.name,'Tax Code',SCREEN_ENTRY_VALUE,null)) Tax_code,
1703: max(decode(iv.name,'Tax Basis',SCREEN_ENTRY_VALUE,null)) Tax_Basis,
1704: 100 * fnd_number.canonical_to_number(max(decode(iv.name,'Pay Previous',

Line 1736: update pay_gb_year_end_assignments ye_asg set

1732: -- details using the run result.
1733: --
1734: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',34);
1735: hr_utility.trace( 'update taxcode with last taxcode used' );
1736: update pay_gb_year_end_assignments ye_asg set
1737: ( TAX_CODE, W1_M1_INDICATOR,PREVIOUS_TAXABLE_PAY,PREVIOUS_TAX_PAID) =
1738: (select max(decode(name,'Tax Code',result_value,null)) Tax_code,
1739: max(decode(name,'Tax Basis',result_value,null)) Tax_Basis,
1740: 100 * fnd_number.canonical_to_number(max(decode(name,'Pay Previous',result_value,null))) Pay_previous,

Line 1756: update pay_gb_year_end_assignments ye_asg set

1752: -- update recurring criteria.
1753: --
1754: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',35);
1755: hr_utility.trace( 'default taxcode to element entry if no tax calc run');
1756: update pay_gb_year_end_assignments ye_asg set
1757: ( TAX_CODE, W1_M1_INDICATOR,PREVIOUS_TAXABLE_PAY,PREVIOUS_TAX_PAID) =
1758: (select max(decode(iv.name,'Tax Code',SCREEN_ENTRY_VALUE,null)) Tax_code,
1759: max(decode(iv.name,'Tax Basis',SCREEN_ENTRY_VALUE,null)) Tax_Basis,
1760: 100 * fnd_number.canonical_to_number(max(decode(iv.name,'Pay Previous',

Line 1790: update pay_gb_year_end_assignments ye_asg set

1786: --
1787: --
1788: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',36);
1789: hr_utility.trace( 'reset codes on Year End Assignment table' );
1790: update pay_gb_year_end_assignments ye_asg set
1791: ( WEEK_53_INDICATOR, W1_M1_INDICATOR, TAX_REFUND, TAX_PAID,
1792: SUPERANNUATION_REFUND, SUPERANNUATION_PAID, TAX_CODE ) =
1793: ( select
1794: decode(ye_roll.MAX_PERIOD_NUMBER,53,'3',54,'4',56,'6',' ') WEEK_53_INDICATOR,

Line 1827: from pay_gb_year_end_assignments

1823: select substr('EMPNO:'||ASSIGNMENT_NUMBER||
1824: ' '||LAST_NAME||
1825: ' has multiple assignments on more than one permit',1,132) mess
1826: into l_error_text
1827: from pay_gb_year_end_assignments
1828: where rowid = l_invalid_masg.ye_asg_rowid
1829: and rownum = 1;
1830:
1831: hr_utility.trace(l_error_text);

Line 1843: update pay_gb_year_end_assignments pp

1839:
1840: -- identify which of the person records are the eoy primary rows
1841: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',42);
1842: hr_utility.trace( 'find the multiple asgs primary assignment for NI');
1843: update pay_gb_year_end_assignments pp
1844: set eoy_primary_flag = 'Y' where pp.rowid = ( select
1845: substr(max(lpad(gross_pay,9,'0')||p.rowid),-18)
1846: from pay_gb_year_end_assignments p,
1847: pay_gb_year_end_payrolls ye_roll,

Line 1846: from pay_gb_year_end_assignments p,

1842: hr_utility.trace( 'find the multiple asgs primary assignment for NI');
1843: update pay_gb_year_end_assignments pp
1844: set eoy_primary_flag = 'Y' where pp.rowid = ( select
1845: substr(max(lpad(gross_pay,9,'0')||p.rowid),-18)
1846: from pay_gb_year_end_assignments p,
1847: pay_gb_year_end_payrolls ye_roll,
1848: pay_gb_year_end_payrolls yep2
1849: where p.person_id = pp.person_id
1850: and ye_roll.payroll_id = p.payroll_id

Line 1887: and exists ( select '1' from pay_gb_year_end_assignments ye_asg

1883: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',46);
1884: hr_utility.trace( 'set reportable to N on secondary assignments');
1885: update pay_gb_year_end_values yev set REPORTABLE = 'N'
1886: where reportable = 'Y'
1887: and exists ( select '1' from pay_gb_year_end_assignments ye_asg
1888: where ye_asg.MULTIPLE_ASG_FLAG is not null
1889: and ye_asg.assignment_id = yev.assignment_id
1890: and ye_asg.effective_end_date = yev.effective_end_date
1891: and ye_asg.request_id = p_request_id );

Line 1901: from pay_gb_year_end_assignments ye_asg

1897: hr_utility.trace( 'set category to X where no values row exists ');
1898: insert into pay_gb_year_end_values
1899: ( ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE)
1900: select ye_asg.assignment_id, ye_asg.effective_end_date ,'X','Y'
1901: from pay_gb_year_end_assignments ye_asg
1902: where not exists ( select '1' from pay_gb_year_end_values yev
1903: where ye_asg.assignment_id = yev.assignment_id
1904: and ye_asg.effective_end_date = yev.effective_end_date
1905: and yev.reportable <> 'N')