[Home] [Help]
124: BEGIN
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
231: --
232: -- delete all the rows in values for this permit , if no permit specified
233: -- delete all rows within the business group
234:
235: l_tax_district_reference pay_gb_year_end_payrolls.tax_district_reference%TYPE;
236: l_tax_reference_number pay_gb_year_end_payrolls.tax_reference_number%TYPE;
237: l_start_year date;
238: l_end_year date;
239:
232: -- delete all the rows in values for this permit , if no permit specified
233: -- delete all rows within the business group
234:
235: l_tax_district_reference pay_gb_year_end_payrolls.tax_district_reference%TYPE;
236: l_tax_reference_number pay_gb_year_end_payrolls.tax_reference_number%TYPE;
237: l_start_year date;
238: l_end_year date;
239:
240: begin
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
308: where exists ( select '1' from pay_payrolls_f p,
309: hr_soft_coding_keyflex flex, hr_organization_information org
310: where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
311: and org.ORG_INFORMATION_CONTEXT = 'Tax Details References'
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
388: where yep2.payroll_id = ye_asg.payroll_id
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
388: where yep2.payroll_id = ye_asg.payroll_id
389: and yep2.TAX_REFERENCE_NUMBER = ye_roll.TAX_REFERENCE_NUMBER
390: and yep2.PERMIT_NUMBER <> ye_roll.PERMIT_NUMBER
391: and asg.effective_start_date < ye_roll.end_year
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'
425: and ye_asg.person_id = yea_prim.person_id
426: and yea_prim.payroll_id = yep_prim.payroll_id
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'
425: and ye_asg.person_id = yea_prim.person_id
426: and yea_prim.payroll_id = yep_prim.payroll_id
427: and ye_asg.payroll_id = ye_roll.payroll_id
546: group by EV_SCON.screen_entry_value, substr(bal.balance_name,4,1)
547: order by EV_SCON.screen_entry_value, substr(bal.balance_name,4,1);
548: --
549: --
550: l_tax_district_reference pay_gb_year_end_payrolls.tax_district_reference%TYPE;
551: l_tax_reference_number pay_gb_year_end_payrolls.tax_reference_number%TYPE;
552: l_start_year date;
553: l_end_year date;
554: l_niy number(9);
547: order by EV_SCON.screen_entry_value, substr(bal.balance_name,4,1);
548: --
549: --
550: l_tax_district_reference pay_gb_year_end_payrolls.tax_district_reference%TYPE;
551: l_tax_reference_number pay_gb_year_end_payrolls.tax_reference_number%TYPE;
552: l_start_year date;
553: l_end_year date;
554: l_niy number(9);
555: l_niy_id number(9);
797: effective_start_date and effective_end_date;
798: --
799: end; -- ) setup ids
800:
801: /* populate pay_gb_year_end_payrolls table for each payroll */
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
801: /* populate pay_gb_year_end_payrolls table for each payroll */
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,
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,
811: PAYROLL_NAME,
841: and p.business_group_id = p_business_group_id
842: and org.organization_id = p_business_group_id
843: and l_end_year between
844: p.effective_start_date and p.effective_end_date
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);
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)
854: from per_time_periods ptp
855: where PTP.payroll_id = ye_roll.payroll_id
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
864: where PTP.payroll_id = ye_roll.payroll_id
865: and PTP.regular_payment_date between
866: add_months(l_start_year,-12)
867: and add_months(l_end_year,-12));
868: --
869: commit;
870: plog ( '_ pay_gb_year_end_payrolls data populated '||to_char(SQL%ROWCOUNT));
871:
872:
873: end; -- ) insert pay_gb_year_end_payrolls
874:
869: commit;
870: plog ( '_ pay_gb_year_end_payrolls data populated '||to_char(SQL%ROWCOUNT));
871:
872:
873: end; -- ) insert pay_gb_year_end_payrolls
874:
875: begin -- ( insert assignments
876: -- select all the assignments for a particular permit
877: -- note we only want the last date effective row - the permit on the
909: ass.organization_id,
910: ass.location_id,
911: ass.people_group_id
912: from per_assignments_f ASS,
913: pay_gb_year_end_payrolls ye_roll
914: where ASS.payroll_id = ye_roll.payroll_id
915: /* 2 years scan to pick up NI Y last year - note we don't need to
916: worry about transfers for NI Y as it uses the STAT_YTD dimension */
917: and ASS.effective_end_date >=
918: decode(p_niy,'N', ye_roll.START_YEAR,
919: nvl(ye_roll.START_PREVIOUS_YEAR,ye_roll.START_YEAR))
920: and ASS.effective_start_date <= ye_roll.END_YEAR
921: and not exists ( select 1 from per_assignments_f ass2,
922: pay_gb_year_end_payrolls ye_roll2
923: where ass.assignment_id = ass2.assignment_id
924: and ass2.payroll_id = ye_roll2.payroll_id
925: and ass2.effective_end_date > ass.effective_end_date
926: and ass2.effective_end_date >=
927: decode(p_niy,'N', ye_roll2.START_YEAR,
928: nvl(ye_roll2.START_PREVIOUS_YEAR,ye_roll2.START_YEAR))
929: and ass2.effective_start_date <= ye_roll2.END_YEAR)
930: and exists
931: ( select yep2.payroll_id from pay_gb_year_end_payrolls yep2
932: where ass.payroll_id = yep2.payroll_id
933: and yep2.permit_number = nvl(p_permit,nvl(yep2.permit_number,'x'))
934: and nvl(l_tax_district_reference,yep2.tax_district_reference) =
935: yep2.tax_district_reference
973: ,hr_soft_coding_keyflex FLEX
974: ,per_assignments_f PASS
975: ,pay_payrolls_f PROLL
976: ,hr_soft_coding_keyflex PFLEX
977: ,pay_gb_year_end_payrolls ye_roll
978: where NROLL.payroll_id = ASS.payroll_id
979: and ye_roll.payroll_id = PASS.payroll_id
980: and ASS.effective_start_date between
981: NROLL.effective_start_date and NROLL.effective_end_date
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
998: and ye_roll.permit_number = nvl(p_permit,ye_roll.permit_number)
999: and nvl(l_tax_district_reference,ye_roll.tax_district_reference) =
1000: ye_roll.tax_district_reference
1032: ,hr_soft_coding_keyflex FLEX
1033: ,per_assignments_f PASS
1034: ,pay_payrolls_f PROLL
1035: ,hr_soft_coding_keyflex PFLEX
1036: ,pay_gb_year_end_payrolls ye_roll
1037: where ass.assignment_id = ye_asg.assignment_id
1038: and ass.effective_start_date < ye_asg.effective_end_date
1039: and NROLL.payroll_id = ASS.payroll_id
1040: and ye_roll.payroll_id = PASS.payroll_id
1072: (
1073: select max(assact2.action_sequence)
1074: from pay_assignment_actions assact2,
1075: pay_payroll_actions pact2,
1076: pay_gb_year_end_payrolls ye_roll
1077: where assact2.assignment_id = ye_asg.assignment_id
1078: and assact2.payroll_action_id = pact2.payroll_action_id
1079: and pact2.payroll_id = ye_roll.payroll_id
1080: and pact2.action_type in ( 'Q','R','B','I')
1140: set ( termination_date, termination_type ) =
1141: (select actual_termination_date ,'L'
1142: from per_periods_of_service pos,
1143: per_assignments_f asg,
1144: pay_gb_year_end_payrolls ye_roll
1145: where pos.person_id = ye_asg.person_id
1146: and ye_asg.assignment_id = asg.assignment_id
1147: and ye_asg.effective_end_date = asg.effective_end_date
1148: and asg.period_of_service_id = pos.period_of_service_id
1156: where ye_asg.request_id = p_request_id
1157: and exists ( select 1
1158: from per_periods_of_service pos,
1159: per_assignments_f asg,
1160: pay_gb_year_end_payrolls ye_roll
1161: where pos.person_id = ye_asg.person_id
1162: and ye_asg.assignment_id = asg.assignment_id
1163: and ye_asg.effective_end_date = asg.effective_end_date
1164: and asg.period_of_service_id = pos.period_of_service_id
1179: set (start_of_emp) =
1180: (select pos.date_start
1181: from per_periods_of_service pos,
1182: per_assignments_f asg,
1183: pay_gb_year_end_payrolls ye_roll
1184: where pos.person_id = ye_asg.person_id
1185: and ye_asg.assignment_id = asg.assignment_id
1186: and ye_asg.effective_end_date = asg.effective_end_date
1187: and asg.period_of_service_id = pos.period_of_service_id
1190: where ye_asg.request_id = p_request_id
1191: and exists ( select 1
1192: from per_periods_of_service pos,
1193: per_assignments_f asg,
1194: pay_gb_year_end_payrolls ye_roll
1195: where pos.person_id = ye_asg.person_id
1196: and ye_asg.assignment_id = asg.assignment_id
1197: and ye_asg.effective_end_date = asg.effective_end_date
1198: and asg.period_of_service_id = pos.period_of_service_id
1242: (
1243: select max(assact2.action_sequence)
1244: from pay_assignment_actions assact2,
1245: pay_payroll_actions pact2,
1246: pay_gb_year_end_payrolls ye_roll
1247: where assact2.assignment_id = ye_asg.assignment_id
1248: and assact2.payroll_action_id = pact2.payroll_action_id
1249: and pact2.payroll_id = ye_roll.payroll_id
1250: and pact2.action_type in ( 'Q','R','B','I')
1604: SCREEN_ENTRY_VALUE,
1605: 'Y'
1606: from pay_element_entries_f e,
1607: pay_element_entry_values_f v,
1608: pay_gb_year_end_payrolls ye_roll,
1609: pay_element_links_f link
1610: where e.assignment_id = l_people.assignment_id
1611: and v.input_value_id + 0 = l_category_input_id
1612: and link.element_type_id = l_ni_id
1675: and assact.action_sequence = (
1676: select max(assact2.action_sequence)
1677: from pay_assignment_actions assact2,
1678: pay_payroll_actions pact,
1679: pay_gb_year_end_payrolls ye_roll
1680: where assact2.assignment_id = ye_asg.assignment_id
1681: and ye_roll.payroll_id = pact.payroll_id
1682: and pact.payroll_action_id = assact2.payroll_action_id
1683: and pact.effective_date between ye_roll.start_year and ye_roll.end_year
1706: 100 * fnd_number.canonical_to_number(max(decode(iv.name,'Tax Previous',
1707: SCREEN_ENTRY_VALUE,null))) Tax_previous
1708: from pay_element_entries_f e,
1709: pay_element_entry_values_f v,
1710: pay_gb_year_end_payrolls ye_roll,
1711: pay_input_values_f iv,
1712: pay_element_links_f link
1713: where e.assignment_id = ye_asg.assignment_id
1714: and link.element_type_id = l_paye_details_id
1762: 100 * fnd_number.canonical_to_number(max(decode(iv.name,'Tax Previous',
1763: SCREEN_ENTRY_VALUE,null))) Tax_previous
1764: from pay_element_entries_f e,
1765: pay_element_entry_values_f v,
1766: pay_gb_year_end_payrolls ye_roll,
1767: pay_input_values_f iv,
1768: pay_element_links_f link
1769: where e.assignment_id = ye_asg.assignment_id
1770: and link.element_type_id = l_paye_details_id
1799: decode(sign(ye_asg.SUPERANNUATION_PAID),-1,'R',' ') SUPERANNUATION_REFUND,
1800: ye_asg.SUPERANNUATION_PAID *
1801: sign(ye_asg.SUPERANNUATION_PAID) SUPERANNUATION_PAID,
1802: nvl(ye_asg.TAX_CODE,'NI') TAX_CODE
1803: from pay_gb_year_end_payrolls ye_roll,
1804: per_time_period_types ptpt
1805: where ye_roll.payroll_id = ye_asg.payroll_id
1806: and ye_roll.period_type = ptpt.period_type
1807: and ye_asg.request_id = p_request_id )
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
1851: and yep2.payroll_id = pp.payroll_id
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
1851: and yep2.payroll_id = pp.payroll_id
1852: and ye_roll.tax_reference_number = yep2.tax_reference_number