[Home] [Help]
1: package body pay_year_end_extract as
2: /* $Header: payyeext.pkb 115.8 99/10/11 06:38:04 porting ship $ */
3: /*
4: * ---------------------------------------------------------------------------
5: Copyright (c) Oracle Corporation (UK) Ltd 1992.
237: l_start_year date;
238: l_end_year date;
239:
240: begin
241: hr_utility.set_location('PAY_YEAR_END_EXTRACT.TRASH',2);
242: --
243: l_tax_district_reference := fnd_number.canonical_to_number(substr(p_tax_district_ref,1,3));
244: l_tax_reference_number := substr(ltrim(substr(p_tax_district_ref,
245: 4,8),'/') ,1,7);
274: and v.effective_end_date = ye_asg.effective_end_date);
275: --
276: plog ( '_ value rows deleted '||to_char(SQL%ROWCOUNT));
277:
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 (
299: and ye_asg.payroll_id = p.payroll_id);
300: --
301: plog ( '_ assignment rows deleted '||to_char(SQL%ROWCOUNT));
302:
303: hr_utility.set_location('PAY_YEAR_END_EXTRACT.TRASH',6);
304: -- delete all the rows in payrolls for this permit , if no permit specified
305: -- delete all the rows for this tax_district_reference else if no
306: -- tax_district_reference specified delete all rows within the business group
307: delete from pay_gb_year_end_payrolls ye_roll
440: -- fetch the scon balances for NI F, NI G and/or NI S
441: select substr(bal.balance_name,4,1) cat_code,
442: substr(HR_GENERAL.DECODE_LOOKUP('GB_SCON',decode(substr(bal.balance_name,4,1),
443: 'F',nvl(max(EV_SCON.screen_entry_value),
444: pay_year_end_extract.get_nearest_scon(
445: max(EV_SCON.element_entry_id),'F',max(PACT.effective_date))),
446: 'G',nvl(max(EV_SCON.screen_entry_value),
447: pay_year_end_extract.get_nearest_scon(
448: max(EV_SCON.element_entry_id),'G',max(PACT.effective_date))),
443: 'F',nvl(max(EV_SCON.screen_entry_value),
444: pay_year_end_extract.get_nearest_scon(
445: max(EV_SCON.element_entry_id),'F',max(PACT.effective_date))),
446: 'G',nvl(max(EV_SCON.screen_entry_value),
447: pay_year_end_extract.get_nearest_scon(
448: max(EV_SCON.element_entry_id),'G',max(PACT.effective_date))),
449: 'S',nvl(max(EV_SCON.screen_entry_value),
450: pay_year_end_extract.get_nearest_scon(
451: max(EV_SCON.element_entry_id),'S',max(PACT.effective_date))),
446: 'G',nvl(max(EV_SCON.screen_entry_value),
447: pay_year_end_extract.get_nearest_scon(
448: max(EV_SCON.element_entry_id),'G',max(PACT.effective_date))),
449: 'S',nvl(max(EV_SCON.screen_entry_value),
450: pay_year_end_extract.get_nearest_scon(
451: max(EV_SCON.element_entry_id),'S',max(PACT.effective_date))),
452: null)),1,9) scon,
453: 100*nvl(sum(decode(substr(bal.balance_name,6),'Able',
454: fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) able,
627: when value_error then
628: raise tax_dist_ref_error;
629: end;
630: --
631: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',1);
632: -- delete old data
633: plog ( '_ Delete previous extracted rows for this selection ');
634: pay_year_end_extract.trash(p_permit,p_business_group_id,
635: p_tax_district_ref,p_year);
630: --
631: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',1);
632: -- delete old data
633: plog ( '_ Delete previous extracted rows for this selection ');
634: pay_year_end_extract.trash(p_permit,p_business_group_id,
635: p_tax_district_ref,p_year);
636:
637: begin -- ( setup ids
638: --
635: p_tax_district_ref,p_year);
636:
637: begin -- ( setup ids
638: --
639: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',2);
640:
641: p_retcode := 1; -- default extract to success
642: -- l_tax_district_reference := fnd_number.canonical_to_number(substr(p_tax_district_ref,1,3));
643: l_tax_reference_number := substr(ltrim(substr(p_tax_district_ref,
802: /* ISSUE - check ACCOUNTS_OFFICE_REF looks like its disabled */
803: -- pick up SCL segments and Tax Reference Info
804: -- sometime the tax reference is delimited by a '/' remove this */
805: begin -- ( insert pay_gb_year_end_payrolls
806: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',4);
807: insert into pay_gb_year_end_payrolls
808: ( BUSINESS_GROUP_ID,
809: PAYROLL_ID,
810: PERMIT_NUMBER,
845: and not exists ( select null from pay_gb_year_end_payrolls ye_roll
846: where ye_roll.payroll_id = p.payroll_id ) ;
847: --
848: --
849: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',6);
850: -- set the start and end dates for the payroll year
851: update pay_gb_year_end_payrolls ye_roll
852: set ( START_YEAR, END_YEAR ,PERIOD_TYPE, MAX_PERIOD_NUMBER) =
853: ( select min(start_date), max(end_date), max(PERIOD_TYPE), max(PERIOD_NUM)
855: where PTP.payroll_id = ye_roll.payroll_id
856: and PTP.regular_payment_date between
857: l_start_year and l_end_year);
858: --
859: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',8);
860: -- set the start and end dates for the previous payroll year
861: update pay_gb_year_end_payrolls ye_roll
862: set ( START_PREVIOUS_YEAR, END_PREVIOUS_YEAR ) =
863: ( select min(start_date), max(end_date) from per_time_periods ptp
885: -- current year assignments
886: --
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,
937: yep2.tax_reference_number
938: and yep2.business_group_id = p_business_group_id);
939: plog ( '_ assignments extracted '||to_char(SQL%ROWCOUNT));
940: --
941: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',13);
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
1007: /* do a quick set effective start date by looking at the prior row on */
1008: /* the ye_asg table. If we are extracting for a particular permit we can't */
1009: /* rely on the person records being on the table so set the 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
1023: /* slow effective start date set */
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
1056: --
1057: plog ( '_ transfers effective_start_date set '||to_char(SQL%ROWCOUNT));
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,
1096: )
1097: WHERE ye_asg.request_id = p_request_id;
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,
1120: where ye_asg.request_id = p_request_id;
1121: plog ( '_ pick up person details '||to_char(SQL%ROWCOUNT));
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
1133: where ye_asg.request_id = p_request_id;
1134: plog ( '_ set director indicator '||to_char(SQL%ROWCOUNT));
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'
1171: and least(ye_asg.effective_end_date,ye_roll.end_year))
1172: ;
1173: plog ( '_ pick up termination date '||to_char(SQL%ROWCOUNT));
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) =
1200: and pos.date_start between l_start_year and l_end_year)
1201: ;
1202: plog ( '_ picked up '||to_char(SQL%ROWCOUNT) || ' Start dates');
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,
1224: where ye_asg.request_id = p_request_id;
1225: plog ( '_ picked up '||to_char(SQL%ROWCOUNT) || ' Addresses');
1226:
1227: --
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
1271: --
1272: end; -- ) insert people
1273:
1274: begin -- ( insert balances
1275: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',28);
1276: l_count := 0;
1277: for l_people in get_people loop -- { person loop
1278: l_count_values := 0; -- count the number of rows inserted into values for each
1279: l_niy := 0; -- initialize ni_y amount
1650: --
1651: --
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
1661: where ye_asg.request_id = p_request_id;
1662: plog ( '_ find out if the latest action computed PAYE '||to_char(SQL%ROWCOUNT));
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,
1694: -- Get the details from the element entry on the added criteria that
1695: -- there exists an updating action id on the element_entry. In other words,
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,
1730: --
1731: -- If there is no tax code forthcoming from the last query, retrieve the
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,
1750: --
1751: -- If there is still no tax code, use the element entry query without the
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,
1784: and ye_asg.request_id = p_request_id;
1785: plog ( '_ default tax details set '||to_char(SQL%ROWCOUNT));
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 ) =
1812: -- multiple assignment logic
1813: -- function that fires when permit is specified that checks whether any
1814: -- multiple assignmnet people have all their assignments extracted within
1815: -- the one permit - if not error
1816: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',40);
1817: hr_utility.trace( 'check that multiple asgs dont span permits');
1818:
1819: if p_permit is not null then -- [ permit not null
1820:
1837: plog ( '_ check multi asgs. span permits '||to_char(SQL%ROWCOUNT));
1838: end if; -- ] permit not null
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)
1856: plog ( '_ for multi asgs. assign a primary asg '||to_char(SQL%ROWCOUNT));
1857:
1858: -- insert a summation of all the values rows against the primary
1859: -- ensure the effective_end_date is reset to the primary's
1860: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',44);
1861: hr_utility.trace( 'put all the NI Contribs on the primary assignment');
1862: -- Thad
1863: l_count := 0;
1864: for asg_rec in get_multi_asg_people loop
1879: end loop;
1880: plog ( '_ for multi asgs. set NI Balances on Primary '||to_char(l_count));
1881:
1882: -- set not reportable values to N on multiple assignmnet rows
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
1892:
1893: plog ('_ for multi asgs. set NI Balances to non reportable on Non P Asgs '
1894: ||to_char(SQL%ROWCOUNT));
1895: -- insert x rows on non primaries
1896: hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',48);
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'
1907: plog ('_ set category to X where no values row exists '||to_char(SQL%ROWCOUNT));
1908: commit;
1909:
1910: end; -- ) update non bal info
1911: plog ('PAY_YEAR_END_EXTRACT completed ');
1912:
1913: -- check data extracted --
1914: /*
1915: plog ('Start CHECK TEMPORARY TABLES ');
1928: plog ('Invalid Format for Tax District Reference: Must be three numerics');
1929: end; -- ) end extract
1930: --
1931: --
1932: end pay_year_end_extract;