[Home] [Help]
52: p_sql out nocopy varchar2 )
53: is
54: begin
55: if g_debug then
56: hr_utility.set_location('pysgiraa: Start of range_code',1);
57: end if;
58: --
59: p_sql := 'select distinct person_id ' ||
60: 'from per_people_f ppf, ' ||
63: 'and ppa.business_group_id = ppf.business_group_id ' ||
64: 'order by ppf.person_id';
65: --
66: if g_debug then
67: hr_utility.set_location('pysgiraa: End of range_code',2);
68: end if;
69: end range_code;
70: ----------------------------------------------------------------------------
71: -- Bug 3435334 - Pre-processor process now introduced for this archive.
148: ) ;
149:
150: begin
151: if g_debug then
152: hr_utility.set_location('pysgiraa: Start of assignment_action_code',3);
153: end if;
154: --
155: initialization_code(p_payroll_action_id);
156:
167: fetch process_assignments into v_assignment_id;
168: exit when process_assignments%notfound;
169: --
170: if g_debug then
171: hr_utility.set_location('pysgiraa: Before calling hr_nonrun_asact.insact',4);
172: end if;
173: --
174: open next_action_id ;
175: fetch next_action_id into v_next_action_id;
198:
199: end loop;
200: --
201: if g_debug then
202: hr_utility.set_location('pysgiraa: After calling hr_nonrun_asact.insact',4);
203: end if;
204: end loop;
205: --
206: close process_assignments;
205: --
206: close process_assignments;
207: --
208: if g_debug then
209: hr_utility.set_location('pysgiraa: End of assignment_action_code',5);
210: end if;
211: end assignment_action_code;
212: ------------------------------------------------------------------------
213: -- Bug 3435334 - Pre-processor process now introduced for this archive.
238: and organization_id = pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters)
239: and payroll_action_id = p_payroll_action_id;
240: begin
241: if g_debug then
242: hr_utility.set_location('pysgiraa: Start of initialization_code',6);
243: end if;
244: --
245: if g_business_group_id is null then
246: open get_params( p_payroll_action_id );
264: g_org_a8a_run := 'Y';
265: end if;
266:
267: if g_debug then
268: hr_utility.set_location('pysgiraa: End of initialization_code',8);
269: end if;
270: end initialization_code;
271: ------------------------------------------------------------------------
272: -- Selects the SRS parameters for the archive and calls other procedures
301: and paa.person_id = pap.person_id ;
302:
303: begin
304: if g_debug then
305: hr_utility.set_location('pysgiraa: Start of archive_code',10);
306: end if;
307: --
308:
309: open get_details ( p_assignment_action_id );
314: if get_details%found then
315: close get_details;
316: --
317: if g_debug then
318: hr_utility.set_location('pysgiraa: Person Id: ' || to_char(v_person_id) ,100);
319: end if;
320: ------------------------------------------------------------------------------------------------
321: -- Because there are different routes for each group of data, a separate procedure
322: -- has been written for each.
371: l_archived_person_id := v_person_id;
372: t_archived_person(l_archived_person_id).person_id:= v_person_id;
373: else
374: if g_debug then
375: hr_utility.trace('The Employee has a duplicate employee so will not be processed');
376: end if;
377: end if;
378: else
379: close get_details;
379: close get_details;
380: end if;
381: --
382: if g_debug then
383: hr_utility.set_location('pysgiraa: End of archive_code',20);
384: end if;
385: end archive_code;
386: --------------------------------------------------------------------------------------
387: -- Bug#3501927 Added new function to fetch and calculate A8A Balances
842: end loop;
843: --
844: exception
845: when others then
846: hr_utility.set_location('pysgiraa: Error in a8a_balances_value',10);
847: raise;
848: end;
849:
850: ---------------------------------------------------------------------------
1135: duplicate_exists := 'N';
1136: l_arch_counter := 1;
1137: --
1138: if g_debug then
1139: hr_utility.set_location('pysgiraa: Start of archive_balances',10);
1140: end if;
1141: ------------------------------------------------------------------------------------------------
1142: -- Bug 3435334 Table g_person_id_tab is populated with duplicate records for current person
1143: -- in employee_if_latest( ) function
1166: ------------------------------------------------------------------------------------------------
1167: -- 2556026 Used pl/sql table to store the month_balances values.
1168: -- now month_balances will get executed only once
1169: ------------------------------------------------------------------------------------------------
1170: hr_utility.set_location('pysgiraa: archive_balances ',1110);
1171: if t_month_balanceid_store.count = 0 then
1172: open month_balances;
1173: loop
1174: fetch month_balances into t_month_balanceid_store(l_mon_counter).user_entity_id,
1177: exit when month_balances%NOTFOUND;
1178: end loop;
1179: close month_balances;
1180: end if;
1181: hr_utility.set_location('pysgiraa: archive_balances ',1120);
1182: ------------------------------------------------------------------------------------------------
1183: -- 2556026 Used pl/sql table to store the ytd_balances values.
1184: -- Now ytd_balances will get executed only once
1185: ------------------------------------------------------------------------------------------------
1192: exit when ytd_balances_ir8a%NOTFOUND;
1193: end loop;
1194: close ytd_balances_ir8a;
1195: end if;
1196: hr_utility.set_location('pysgiraa: archive_balances ',1130);
1197: if t_ytd_balanceid_store_ir8s.count = 0 then
1198: open ytd_balances_ir8s;
1199: loop
1200: fetch ytd_balances_ir8s into t_ytd_balanceid_store_ir8s(l_ytd_counter_ir8s).user_entity_id,
1215: ------------------------------------------------------------------------------------------------
1216: if person_id_rec.count > 0 then
1217: for l_person_counter in 1..person_id_rec.last
1218: loop
1219: hr_utility.set_location('pysgiraa: archive_balances ',1140);
1220: if person_id_rec.exists(l_person_counter) then
1221: open month_year_action_sequence( person_id_rec(l_person_counter).person_id,
1222: p_business_group_id,
1223: p_tax_unit_id,
1246: g_balance_value_tab.delete;
1247: g_context_tab.delete;
1248: g_detailed_bal_out_tab.delete;
1249: --
1250: hr_utility.set_location('pysgiraa: archive_balances ',1150);
1251: for counter in 1..t_month_balanceid_store.count
1252: loop
1253: g_balance_value_tab(counter).defined_balance_id := t_month_balanceid_store(counter).defined_balance_id;
1254: g_context_tab(counter).tax_unit_id := month_year_action_rec.tax_uid;
1262: false,
1263: false,
1264: g_detailed_bal_out_tab );
1265: --
1266: hr_utility.set_location('pysgiraa: archive_balances ',1160);
1267: if duplicate_exists = 'N' then /* Bug 3162955 */
1268: for counter in 1..t_month_balanceid_store.count
1269: loop
1270: if t_month_balanceid_store.exists(counter) then
1293: end if;
1294: end if;
1295: close month_year_action;
1296: end loop;
1297: hr_utility.set_location('pysgiraa: archive_balances ',1170);
1298: --
1299: close month_year_action_sequence;
1300: ----------------------------------------------------------------------------------
1301: -- Bulk Balance Fetch for Bug 3064282
1302: ----------------------------------------------------------------------------------
1303: g_balance_value_tab.delete;
1304: g_context_tab.delete;
1305: g_detailed_bal_out_tab.delete;
1306: hr_utility.set_location('pysgiraa: archive_balances ',1180);
1307: --
1308: for counter in 1..t_ytd_balanceid_store_ir8a.count
1309: loop
1310: g_balance_value_tab(counter).defined_balance_id := t_ytd_balanceid_store_ir8a(counter).defined_balance_id;
1309: loop
1310: g_balance_value_tab(counter).defined_balance_id := t_ytd_balanceid_store_ir8a(counter).defined_balance_id;
1311: g_context_tab(counter).tax_unit_id := month_year_action_rec.tax_uid;
1312: end loop;
1313: hr_utility.set_location('pysgiraa: archive_balances ',1190);
1314: ----------------------------------------------------------------------------------
1315: -- Bug 3223822 - Modified call to the function pay_balance_pkg.get_value
1316: -- Bug 3430277 - Put a condition before function pay_balance_pkg.get_value call.
1317: ----------------------------------------------------------------------------------
1328: -- Bug 3249043 - v_run_ass_action_id is initialized to latest persion assact_id
1329: -- Assign here so cursor variable can be accessed outside of loop
1330: -- Bug# 3328760 - Added g_detailed_bal_out_tab.exists(counter) check
1331: ----------------------------------------------------------------------------------
1332: hr_utility.set_location('pysgiraa: archive_balances ',1200);
1333: for counter in 1..t_ytd_balanceid_store_ir8a.count
1334: loop
1335: if l_person_counter = 1 then
1336: if g_detailed_bal_out_tab.exists(counter) then
1346: end if;
1347: end if;
1348: end if;
1349: end loop;
1350: hr_utility.set_location('pysgiraa: archive_balances ',1210);
1351: g_balance_value_tab.delete;
1352: g_context_tab.delete;
1353: g_detailed_bal_out_tab.delete;
1354:
1375: -- Bug 3249043 - v_run_ass_action_id is initialized to latest persion assact_id
1376: -- Assign here so cursor variable can be accessed outside of loop
1377: -- Bug# 3328760 - Added g_detailed_bal_out_tab.exists(counter) check
1378: ----------------------------------------------------------------------------------
1379: hr_utility.set_location('pysgiraa: archive_balances ',1220);
1380: for counter in 1..t_ytd_balanceid_store_ir8s.count
1381: loop
1382: if l_person_counter = 1 then
1383: if g_detailed_bal_out_tab.exists(counter) then
1393: end if;
1394: end if;
1395: end if;
1396: end loop;
1397: hr_utility.set_location('pysgiraa: archive_balances ',1230);
1398: --
1399: --
1400: --
1401: ------------------------------------------------------------------------------------
1419: ------------------------------------------------------------------------------------------------
1420: month_year_action_sequence_rec.act_seq := null;
1421: month_year_action_rec.assact_id := null;
1422: --
1423: hr_utility.set_location('pysgiraa: archive_balances ',1240);
1424: end loop ;
1425: end if;
1426: ------------------------------------------------------------------------------------------------
1427: -- Bug 3162955 Month Balance Implementation
1461: end if;
1462: end loop;
1463: end if;
1464:
1465: hr_utility.set_location('pysgiraa:archive_balances ',1250);
1466: ------------------------------------------------------------------------------------------------
1467: -- Bug 3038605 - Added the following code to archive balances with 0 values for months with no payroll runs
1468: -- Logic Used:
1469: -- Search the pl/sql table month_recs to see if the specified month is already archived.
1508: l_pmon_counter := false;
1509: end if;
1510: end loop;
1511:
1512: hr_utility.set_location('pysgiraa: archive_balances ',15);
1513: ------------------------------------------------------------------------------------------------
1514: -- Bug: 3260855 Bulk Insert into ff_archive_items for month balances
1515: ------------------------------------------------------------------------------------------------
1516: select context_id
1578: END;
1579:
1580: t_amend_value(i) := l_temp_value;
1581: END LOOP;
1582: hr_utility.set_location('pysgiraa: archive_balances ',20);
1583: g_amend_ir8s_m_flag :='N';
1584:
1585:
1586: FOR I IN 1..t_user_entity_id.count LOOP
1598: /* need to archive t_amend_value(i) - t_archive_value(i) .
1599: handle when no data exists, ensure only numbers.
1600: Special handling for fields that dont allow negative*/
1601:
1602: hr_utility.set_location('pysgiraa: archive_balances ',30);
1603:
1604: l_arch_counter := 1;
1605: --
1606:
1621: t_archive_value_ir8a(l_arch_counter) := ytd_balance_rec_ir8a(counter).balance_value;
1622: l_arch_counter := l_arch_counter + 1;
1623: end if;
1624: end loop;
1625: hr_utility.set_location('pysgiraa: archive_balances ',40);
1626: ---------------------------------------------------------------------------------------------------
1627: -- Bug# 3501927 A8A_USABLITY
1628: ---------------------------------------------------------------------------------------------------
1629: --Bug#3933332
1640: end if;
1641: end loop;
1642: --
1643: end if;
1644: hr_utility.set_location('pysgiraa: archive_balances ',50);
1645:
1646: FOR counter IN 1..t_user_entity_id_ir8a.COUNT LOOP
1647:
1648: select user_entity_name
1680: t_amend_value_ir8a(counter) := l_temp_value;
1681:
1682:
1683: END LOOP;
1684: hr_utility.set_location('pysgiraa: archive_balances ',60);
1685: g_amend_ir8a_flag := 'N';
1686:
1687: for counter in 1..t_user_entity_id_ir8a.count
1688: loop
1697: end if;
1698:
1699: end loop;
1700:
1701: hr_utility.set_location('pysgiraa: archive_balances ',70);
1702:
1703: FOR counter IN 1..t_user_entity_id_ir8s.COUNT LOOP
1704:
1705: select user_entity_name
1736:
1737: t_amend_value_ir8s(counter) := l_temp_value;
1738:
1739: END LOOP;
1740: hr_utility.set_location('pysgiraa: archive_balances ',80);
1741: g_amend_ir8a_flag := 'N';
1742:
1743: for counter in 1..t_user_entity_id_ir8a.count
1744: loop
1753: end if;
1754:
1755: end loop;
1756:
1757: hr_utility.set_location('pysgiraa: archive_balances ',90);
1758: g_amend_ir8s_flag := 'N';
1759:
1760: for counter in 1..t_user_entity_id_ir8s.count
1761: loop
1770: end if;
1771:
1772: end loop;
1773:
1774: hr_utility.set_location('pysgiraa: archive_balances ',100);
1775: FOR i in 1..t_user_entity_id_a8a.count LOOP
1776:
1777: select user_entity_name
1778: into l_name_ue
1809:
1810: t_amend_value_a8a(i) := l_temp_value;
1811:
1812: END LOOP;
1813: hr_utility.set_location('pysgiraa: archive_balances ',110);
1814:
1815: g_amend_a8a_flag := 'N';
1816: -- ARCHIVE ITEMS
1817:
1828: exit;
1829: end if;
1830: end loop;
1831:
1832: hr_utility.set_location('pysgiraa: archive_balances ',120);
1833:
1834: if g_amend_ir8s_c_flag = 'Y' or g_amend_ir8s_m_flag = 'Y' then
1835: g_amend_ir8s_flag := 'Y';
1836: end if;
1935: l_date_cont_id );
1936: --
1937: t_archive_items.delete;
1938:
1939: hr_utility.set_location('pysgiraa: archive_balances ',130);
1940:
1941: forall counter in 1..t_user_entity_id_ir8a.count
1942: insert into ff_archive_items
1943: ( archive_item_id,
1980: p_tax_unit_id,
1981: l_tax_cont_id );
1982: t_archive_items_ir8a.delete;
1983:
1984: hr_utility.set_location('pysgiraa: archive_balances ',140);
1985: forall counter in 1..t_user_entity_id_ir8s.count
1986: insert into ff_archive_items
1987: ( archive_item_id,
1988: user_entity_id,
2024: p_tax_unit_id,
2025: l_tax_cont_id );
2026: t_archive_items_ir8s.delete;
2027:
2028: hr_utility.set_location('pysgiraa: archive_balances ',150);
2029:
2030: forall counter in 1..t_user_entity_id_a8a.count
2031: insert into ff_archive_items
2032: ( archive_item_id,
2068: 2,
2069: p_tax_unit_id,
2070: l_tax_cont_id );
2071: t_archive_items_a8a.delete;
2072: hr_utility.set_location('pysgiraa: archive_balances ',160);
2073:
2074: select arch.value,ue.user_entity_id
2075: bulk collect into t_orig_value,t_orig_user_entity_id
2076: from ff_user_entities ue ,
2147: and ppa.report_type='SG_IRAS_ARCHIVE'
2148: and paa.assignment_id = l_assignment_id) ;
2149:
2150:
2151: hr_utility.set_location('pysgiraa: archive_balances ',170);
2152: if t_orig_user_entity_id.count >0 then
2153:
2154: forall counter in 1..t_orig_user_entity_id.count
2155: insert into ff_archive_items
2180: l_asac_cont_id );
2181:
2182: end if;
2183: end if;
2184: hr_utility.set_location('pysgiraa: archive_balances ',180);
2185: t_user_entity_id.delete;
2186: t_archive_value.delete;
2187: t_date_earned.delete;
2188:
2201: -- employees having terminated and rehired in the same financial year
2202: ------------------------------------------------------------------------------------------------
2203:
2204: if g_debug then
2205: hr_utility.set_location('pysgiraa: End of archive_balances',100);
2206: end if;
2207: end archive_balances;
2208:
2209: ---------------------------------------------------------------------------
2396: l_count := 0;
2397: v_archive := 'N';
2398:
2399: if g_debug then
2400: hr_utility.set_location('pysgiraa: Start of archive_share_details', 10);
2401: end if;
2402:
2403: SELECT assignment_id
2404: INTO l_assignment_id
2758: end if;
2759: end if;
2760:
2761: if g_debug then
2762: hr_utility.set_location('pysgiraa: End of archive_share_details', 100);
2763: end if;
2764: end archive_shares_details;
2765:
2766:
2797: l_counter := 1;
2798: duplicate_exists := 'N';
2799: --
2800: if g_debug then
2801: hr_utility.set_location('pysgiraa: Start of archive_ir8s_c_details', 10);
2802: end if;
2803: ----------------------------------------------------------------------------------
2804: -- Added for bug 3162319
2805: -- Bug 3435334 Table g_person_id_tab is populated with duplicate records for current person
2837:
2838: end if;
2839: --
2840: if g_debug then
2841: hr_utility.set_location('pysgiraa: End of archive_ir8s_c_details', 100);
2842: end if;
2843: end archive_ir8s_c_details;
2844:
2845:
2961: v_ir8s_total_moa410 := 0;
2962: l_count := 0;
2963: g_amend_ir8s_c_flag := 'N';
2964: if g_debug then
2965: hr_utility.set_location('pysgiraa: Start of archive_ir8s_c_detail_moas', 10);
2966: end if;
2967: --
2968: select ue.user_entity_id, ue.user_entity_name
2969: bulk collect into t_user_entity_id_ir8s_c, t_user_entity_name_ir8s_c
3222: archive_item('X_IR8S_TOTAL_MOA410', p_assignment_action_id, v_ir8s_total_moa410);
3223: end if;
3224: --
3225: if g_debug then
3226: hr_utility.set_location('pysgiraa: End of archive_ir8s_c_detail_moas', 100);
3227: end if;
3228: end archive_ir8s_c_detail_moas;
3229: ---------------------------------------------------------------------------
3230: -- Calls the archive utility to actually perform the archive of the item.
3250: where user_entity_name = c_user_entity_name;
3251: --
3252: begin
3253: if g_debug then
3254: hr_utility.set_location('Start of archive_item',10);
3255: end if;
3256: --
3257: open user_entity_id (p_user_entity_name);
3258: fetch user_entity_id into v_user_entity_id;
3271: ,p_context1 => p_assignment_action_id
3272: ,p_some_warning => v_some_warning);
3273: --
3274: if g_debug then
3275: hr_utility.set_location('End of archive_item',20);
3276: end if;
3277: end archive_item;
3278:
3279: -----------------------------------------------------------------------------
3302: where user_entity_name = c_user_entity_name;
3303: --
3304: begin
3305: if g_debug then
3306: hr_utility.set_location('Start of archive_item_2',10);
3307: end if;
3308: --
3309: open user_entity_id (p_user_entity_name);
3310: fetch user_entity_id into v_user_entity_id;
3325: ,p_context2 => p_context_value2
3326: ,p_some_warning => v_some_warning);
3327: --
3328: if g_debug then
3329: hr_utility.set_location('End of archive_item_2',20);
3330: end if;
3331: end archive_item_2;
3332:
3333: -----------------------------------------------------------------------------
3357: where user_entity_name = c_user_entity_name;
3358: --
3359: begin
3360: if g_debug then
3361: hr_utility.set_location('Start of archive_item_3',10);
3362: end if;
3363: --
3364: open user_entity_id (p_user_entity_name);
3365: fetch user_entity_id into v_user_entity_id;
3382: ,p_context3 => p_context_value3
3383: ,p_some_warning => v_some_warning );
3384: --
3385: if g_debug then
3386: hr_utility.set_location('End of archive_item_3',20);
3387: end if;
3388: end archive_item_3;
3389: --------------------------------------------------------------------------------
3390: -- Bug 3118540 -
3399: begin
3400: l_rep_req_id := 0;
3401: v_setup_action := 0;
3402: if g_debug then
3403: hr_utility.set_location('pysgiraa: Start of deinit_code',10);
3404: end if;
3405: --
3406: select report_type
3407: into l_report_type
3427: end if;
3428: exception
3429: when others then
3430: if g_debug then
3431: hr_utility.set_location('pysgiraa: End of deinit_code',10);
3432: end if;
3433: raise;
3434: end deinit_code;
3435: ----------------------------------------------------------------------
3482: is
3483: l_archived_person_id binary_integer;
3484: begin
3485: if g_debug then
3486: hr_utility.set_location('pysgiraa: Start of person_if_archived',10);
3487: end if;
3488:
3489: l_archived_person_id := p_person_id;
3490: if t_archived_person.exists(l_archived_person_id) then
3489: l_archived_person_id := p_person_id;
3490: if t_archived_person.exists(l_archived_person_id) then
3491: if (t_archived_person(l_archived_person_id).person_id = p_person_id) then
3492: if g_debug then
3493: hr_utility.set_location('End of person_if_archived',20);
3494: end if;
3495: return true;
3496: end if;
3497: end if;
3495: return true;
3496: end if;
3497: end if;
3498: if g_debug then
3499: hr_utility.set_location('End of person_if_archived',20);
3500: end if;
3501: return false;
3502: end person_if_archived;
3503:
3538: and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
3539:
3540: begin
3541: if g_debug then
3542: hr_utility.set_location('pysgiraa: Start of pri_if_latest',10);
3543: end if;
3544:
3545: open pri_latest (p_person_id,
3546: p_tax_unit_id,
3550: --
3551: if pri_latest%found then
3552: close pri_latest;
3553: if g_debug then
3554: hr_utility.set_location('End of pri_if_latest',20);
3555: end if;
3556: return TRUE;
3557: end if;
3558: close pri_latest;
3556: return TRUE;
3557: end if;
3558: close pri_latest;
3559: if g_debug then
3560: hr_utility.set_location('End of pri_if_latest',20);
3561: end if;
3562: return FALSE;
3563:
3564: end pri_if_latest;
3602: and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
3603:
3604: begin
3605: if g_debug then
3606: hr_utility.set_location('pysgiraa: Start of pri_LE_if_latest',10);
3607: end if;
3608:
3609: open pri_latest_LE (p_person_id,
3610: p_tax_unit_id,
3614: --
3615: if pri_latest_LE%found then
3616: close pri_latest_LE;
3617: if g_debug then
3618: hr_utility.set_location('End of pri_LE_if_latest',20);
3619: end if;
3620: return v_assignment_id;
3621: end if;
3622: close pri_latest_LE;
3620: return v_assignment_id;
3621: end if;
3622: close pri_latest_LE;
3623: if g_debug then
3624: hr_utility.set_location('End of pri_LE_if_latest',20);
3625: end if;
3626: return null;
3627:
3628: end pri_LE_if_latest;
3669: and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
3670:
3671: begin
3672: if g_debug then
3673: hr_utility.set_location('pysgiraa: Start of id_LE_if_latest',10);
3674: end if;
3675:
3676: open id_latest_LE ( p_person_id
3677: , p_tax_unit_id
3681: --
3682: if id_latest_LE%found then
3683: close id_latest_LE;
3684: if g_debug then
3685: hr_utility.set_location('End of id_LE_if_latest',20);
3686: end if;
3687: return v_assignment_id;
3688: end if;
3689: close id_latest_LE;
3687: return v_assignment_id;
3688: end if;
3689: close id_latest_LE;
3690: if g_debug then
3691: hr_utility.set_location('End of id_LE_if_latest',20);
3692: end if;
3693: return null;
3694:
3695: end id_LE_if_latest;
3694:
3695: end id_LE_if_latest;
3696:
3697: begin
3698: g_debug := hr_utility.debug_enabled;
3699: g_org_run := 'N';
3700: g_org_a8a_run := 'N';
3701: g_iras_method := 'A';
3702: g_a8b_moa_348 := 0;