[Home] [Help]
444:
445: --Bug 1776606 : Introduced the two cursors
446: CURSOR sum_lines_cur(P_PAYROLL_CONTROL_ID IN NUMBER) IS
447: SELECT count(*)
448: FROM psp_summary_lines
449: WHERE payroll_control_id = p_payroll_control_id
450: AND status_code <> 'A';
451:
452: CURSOR adj_lines_cur(P_PAYROLL_CONTROL_ID IN NUMBER) IS
507:
508: DELETE FROM pa_transaction_interface_all
509: -- WHERE batch_name = g_gms_batch_name
510: WHERE batch_name IN (SELECT GMS_BATCH_NAME
511: FROM psp_summary_lines
512: WHERE PAYROLL_CONTROL_ID IN(SELECT payroll_control_id
513: FROM psp_payroll_controls
514: WHERE adj_sum_batch_name =p_adj_sum_batch_name))
515: ---AND transaction_status_code = 'A' delete 'R' also, for 2445196
520: -- introduced following stmnt for 2445196
521: delete from gms_transaction_interface_all
522: -- where batch_name = g_gms_batch_name
523: WHERE batch_name IN (SELECT GMS_BATCH_NAME
524: FROM psp_summary_lines
525: WHERE PAYROLL_CONTROL_ID IN(SELECT payroll_control_id
526: FROM psp_payroll_controls
527: WHERE adj_sum_batch_name =p_adj_sum_batch_name))
528: and transaction_source = 'GOLD';
641: DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
642: DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
643: FROM psp_adjustment_lines pal,
644: psp_distribution_lines_history pdlh,
645: psp_summary_lines psl
646: WHERE pal.status_code = 'N'
647: AND pal.gl_code_combination_id is not null
648: AND pal.payroll_control_id = p_payroll_control_id
649: AND pal.orig_source_type = 'D'
672: DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
673: DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
674: FROM psp_adjustment_lines pal,
675: psp_adjustment_lines_history palh,
676: psp_summary_lines psl
677: WHERE pal.status_code = 'N'
678: AND pal.gl_code_combination_id is not null
679: AND pal.payroll_control_id = p_payroll_control_id
680: AND pal.orig_source_type = 'A'
703: DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
704: DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
705: FROM psp_adjustment_lines pal,
706: psp_pre_gen_dist_lines_history pglh,
707: psp_summary_lines psl
708: WHERE pal.status_code = 'N'
709: AND pal.gl_code_combination_id is not null
710: AND pal.payroll_control_id = p_payroll_control_id
711: AND pal.orig_source_type = 'P'
1076: END IF;
1077: -- Code ended for Enhancement Employee Assignment with Zero Work Days
1078:
1079:
1080: SELECT PSP_SUMMARY_LINES_S.NEXTVAL
1081: INTO P_SUMMARY_LINE_ID
1082: FROM DUAL;
1083:
1084: INSERT INTO PSP_SUMMARY_LINES(
1080: SELECT PSP_SUMMARY_LINES_S.NEXTVAL
1081: INTO P_SUMMARY_LINE_ID
1082: FROM DUAL;
1083:
1084: INSERT INTO PSP_SUMMARY_LINES(
1085: SUMMARY_LINE_ID,
1086: PERSON_ID,
1087: ASSIGNMENT_ID,
1088: TIME_PERIOD_ID,
1253: psl.attribute28,
1254: psl.attribute29,
1255: psl.attribute30,
1256: psl.person_id --Included this column as part of bug fix 1828519
1257: FROM psp_summary_lines psl
1258: WHERE psl.status_code = 'N'
1259: AND psl.gl_code_combination_id IS NOT NULL
1260: AND psl.payroll_control_id = p_payroll_control_id;
1261:
1358: EXIT;
1359: END IF;
1360:
1361:
1362: -- update psp_summary_lines with group_id
1363: UPDATE psp_summary_lines
1364: SET group_id = l_group_id
1365: WHERE status_code = 'N'
1366: AND gl_code_combination_id IS NOT NULL
1359: END IF;
1360:
1361:
1362: -- update psp_summary_lines with group_id
1363: UPDATE psp_summary_lines
1364: SET group_id = l_group_id
1365: WHERE status_code = 'N'
1366: AND gl_code_combination_id IS NOT NULL
1367: AND payroll_control_id = gl_batch_rec.payroll_control_id;
1364: SET group_id = l_group_id
1365: WHERE status_code = 'N'
1366: AND gl_code_combination_id IS NOT NULL
1367: AND payroll_control_id = gl_batch_rec.payroll_control_id;
1368: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updating psp_summary_lines group_id set to '||l_group_id||' for GL lines with control_id: '||gl_batch_rec.payroll_control_id);
1369:
1370:
1371: -- get the period_name
1372: -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
1671: CURSOR gl_tie_back_success_cur(p_group_id IN NUMBER,
1672: p_payroll_control_id IN NUMBER ) IS ----added for Bug 2133056
1673: SELECT summary_line_id,
1674: dr_cr_flag,summary_amount
1675: FROM psp_summary_lines
1676: WHERE group_id = p_group_id
1677: AND payroll_control_id = p_payroll_control_id;
1678:
1679: CURSOR gl_reversal_cur(p_summary_line_id IN NUMBER) IS
1744:
1745: -- Included the following cursors here for accessing local varibales for bug fix 1765678
1746: CURSOR summary_group_cur IS
1747: SELECT PSL.group_id
1748: FROM psp_summary_lines PSL,
1749: psp_payroll_controls PPC
1750: WHERE PPC.payroll_control_id = PSL.payroll_control_id
1751: -- WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id
1752: AND PPC.batch_name = pc_batch_rec.batch_name
1887: END IF;
1888:
1889: --dbms_output.put_line('Am in the success loop');
1890:
1891: -- update records in psp_summary_lines as 'A'
1892: UPDATE psp_summary_lines
1893: SET status_code = 'A'
1894: WHERE summary_line_id = l_summary_line_id;
1895:
1888:
1889: --dbms_output.put_line('Am in the success loop');
1890:
1891: -- update records in psp_summary_lines as 'A'
1892: UPDATE psp_summary_lines
1893: SET status_code = 'A'
1894: WHERE summary_line_id = l_summary_line_id;
1895:
1896: IF l_dr_cr_flag = 'D' THEN
1937: DELETE FROM psp_adjustment_lines
1938: WHERE status_code = 'A'
1939: AND summary_line_id = l_summary_line_id;
1940:
1941: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_summary_lines, psp_adjustment_lines inserted into
1942: psp_adjustment_lines_history then deleted from psp_adjustment_lines for summary_line_id : '||l_summary_line_id);
1943:
1944: END LOOP;
1945:
1993: time_period_id,
1994: summary_amount reversal_dist_amount,
1995: interface_batch_name,
1996: summary_line_id
1997: FROM psp_summary_lines
1998: WHERE payroll_control_id = p_payroll_control_id
1999: AND gl_code_combination_id IS NOT NULL
2000: AND status_code = 'N'
2001: AND business_group_id = p_business_group_id
2417: DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
2418: DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
2419: FROM psp_adjustment_lines pal,
2420: psp_distribution_lines_history pdh,
2421: psp_summary_lines psl
2422: WHERE pal.status_code = 'N'
2423: AND pal.gl_code_combination_id IS NULL
2424: AND pal.payroll_control_id = p_payroll_control_id
2425: AND pdh.distribution_line_id = pal.orig_line_id
2452: DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
2453: DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
2454: FROM psp_adjustment_lines pal,
2455: psp_adjustment_lines_history pal2,
2456: psp_summary_lines psl
2457: WHERE pal.status_code = 'N'
2458: AND pal.gl_code_combination_id IS NULL
2459: AND pal.payroll_control_id = p_payroll_control_id
2460: AND pal2.adjustment_line_id = pal.orig_line_id
2487: DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
2488: DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
2489: FROM psp_adjustment_lines pal,
2490: psp_pre_gen_dist_lines_history pgh,
2491: psp_summary_lines psl
2492: WHERE pal.status_code = 'N'
2493: AND pal.gl_code_combination_id IS NULL
2494: AND pal.payroll_control_id = p_payroll_control_id
2495: AND pgh.pre_gen_dist_line_id = pal.orig_line_id
2874: psl.attribute8,
2875: psl.attribute9,
2876: psl.attribute10,
2877: org_id -- R12 MOAC uptake
2878: FROM psp_summary_lines psl
2879: WHERE psl.status_code = 'N'
2880: AND psl.gl_code_combination_id IS NULL
2881: AND psl.payroll_control_id = p_payroll_control_id;
2882:
2979: r_payroll_controls payroll_control_rec;
2980:
2981: CURSOR payroll_control_id_cur IS
2982: SELECT DISTINCT payroll_control_id
2983: FROM psp_summary_lines
2984: WHERE gms_batch_name = l_gms_batch_name;
2985: -- End of changes for bug fix 4507892
2986:
2987: -- R12 MOAC Uptake
3000:
3001: CURSOR operating_unit_csr IS
3002: SELECT distinct org_id
3003: FROM psp_payroll_controls ppc,
3004: psp_summary_lines psl
3005: WHERE ppc.payroll_control_id = psl.payroll_control_id
3006: AND ppc.adj_sum_batch_name = p_adj_sum_batch_name
3007: AND ppc.source_type = 'A'
3008: AND (ppc.dist_dr_amount IS NOT NULL OR ppc.dist_cr_amount IS NOT NULL)
3097:
3098: -- R12 MOAC Uptake. Moved this code to loop
3099: FOR I in 1..org_id_tab.count
3100: LOOP
3101: UPDATE psp_summary_lines
3102: SET gms_batch_name = gms_batch_name_tab(i) -- l_gms_batch_name
3103: WHERE payroll_control_id = gms_batch_rec.payroll_control_id
3104: AND status_code = 'N'
3105: AND gl_code_combination_id IS NULL
3107: END LOOP;
3108:
3109: FOR i in 1..org_id_tab.count
3110: LOOP
3111: -- update psp_summary_lines with gms batch name
3112: UPDATE psp_summary_lines
3113: SET gms_batch_name = gms_batch_name_tab(i) -- l_gms_batch_name
3114: WHERE payroll_control_id = gms_batch_rec.payroll_control_id
3115: AND status_code = 'N'
3108:
3109: FOR i in 1..org_id_tab.count
3110: LOOP
3111: -- update psp_summary_lines with gms batch name
3112: UPDATE psp_summary_lines
3113: SET gms_batch_name = gms_batch_name_tab(i) -- l_gms_batch_name
3114: WHERE payroll_control_id = gms_batch_rec.payroll_control_id
3115: AND status_code = 'N'
3116: AND gl_code_combination_id IS NULL
3477: and BATCH_NAME = l_gms_batch_name
3478: and TRANSACTION_SOURCE = l_txn_source;
3479: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated pa_transaction_interface_all set batch_name to '||l_gms_stat_batch_name||' for STAT records count: '||sql%rowcount);
3480:
3481: UPDATE psp_summary_lines
3482: SET gms_batch_name = l_gms_stat_batch_name
3483: WHERE payroll_control_id IN(select payroll_control_id from psp_payroll_controls
3484: where run_id = g_run_id
3485: and currency_code = 'STAT')
3485: and currency_code = 'STAT')
3486: AND gms_batch_name = l_gms_batch_name
3487: AND status_code = 'N'
3488: AND gl_code_combination_id IS NULL;
3489: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_summary_lines set gms_batch_name to '||l_gms_stat_batch_name||' for STAT records count: '||sql%rowcount);
3490:
3491:
3492:
3493: SELECT count(*) INTO l_not_stat_count
3745:
3746: CURSOR gms_tie_back_success_cur(p_gms_batch_name number, p_payroll_control_id number) IS
3747: SELECT summary_line_id,
3748: dr_cr_flag,summary_amount
3749: FROM psp_summary_lines
3750: WHERE gms_batch_name = p_gms_batch_name
3751: AND payroll_control_id = p_payroll_control_id;
3752:
3753:
3760: AND transaction_status_code in ('R', 'PO', 'PI', 'PR')
3761: AND transaction_source = p_txn_src
3762: AND orig_transaction_reference in
3763: (select to_char(summary_line_id)
3764: from psp_summary_lines
3765: where payroll_control_id = p_payroll_control_id
3766: and gms_batch_name = p_gms_batch_name);
3767:
3768: l_orig_org_name1 hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
3831:
3832: -- the following cursors are included here for accessing the local variables for bug fix 1765678
3833: CURSOR gms_batch_name_cur IS
3834: SELECT DISTINCT gms_batch_name
3835: FROM psp_summary_lines
3836: WHERE payroll_control_id = gms_tie_back_rec.payroll_control_id
3837: AND gms_batch_name IS NOT NULL;
3838:
3839: CURSOR transaction_source_cur IS
3847: WHERE batch_name = TO_CHAR(l_gms_batch_name)
3848: AND transaction_source = l_txn_source
3849: AND transaction_status_code IN ('R', 'PO', 'PI', 'PR')
3850: AND orig_transaction_reference IN (SELECT TO_CHAR(summary_line_id)
3851: FROM psp_summary_lines
3852: WHERE payroll_control_id = gms_tie_back_rec.payroll_control_id
3853: AND gms_batch_name = l_gms_batch_name);
3854: FUNCTION PROCESS_COMPLETE RETURN BOOLEAN IS
3855:
3916:
3917: end if;
3918:
3919: /* Commented for 2133056, Let recover start from Import and not Summarize
3920: delete from psp_summary_lines
3921: where gms_batch_name = l_gms_batch_name
3922: and payroll_control_id = gms_tie_back_rec.payroll_control_id;
3923: */
3924: -- Added following update for 2133056
3992: delete pa_transaction_interface_all
3993: where transaction_source in ('GOLD','OLD') and
3994: batch_name = l_gms_batch_name;
3995:
3996: delete psp_summary_lines
3997: where payroll_control_id in
3998: (select payroll_control_id
3999: from psp_payroll_controls
4000: where source_type = 'A'
3999: from psp_payroll_controls
4000: where source_type = 'A'
4001: and adj_sum_batch_name = p_adj_sum_batch_name);
4002:
4003: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from psp_summary_lines count: '||sql%rowcount);
4004:
4005: update psp_payroll_controls
4006: set gms_phase = null
4007: where gms_phase is not null
4159: exit;
4160:
4161:
4162: ELSIF l_cnt_gms_interface = 0 THEN
4163: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' before bulk update of psp_summary_lines in tieback - ');
4164:
4165:
4166: l_gms_batch_name1 := l_gms_batch_name; --Bug 6118274
4167: --- moved this stmnt from the loop below, and modified for 2445196.
4164:
4165:
4166: l_gms_batch_name1 := l_gms_batch_name; --Bug 6118274
4167: --- moved this stmnt from the loop below, and modified for 2445196.
4168: UPDATE psp_summary_lines PSL
4169: SET (PSL.status_code, PSL.expenditure_ending_date,PSL.expenditure_id,
4170: PSL.interface_id,PSL.expenditure_item_id,PSL.txn_interface_id) =
4171: (select 'A', PTXN.expenditure_ending_date,PTXN.expenditure_id,
4172: PTXN.interface_id,PTXN.expenditure_item_id,PTXN.txn_interface_id
4198: CLOSE gms_tie_back_success_cur;
4199: EXIT;
4200: END IF;
4201:
4202: -- update records in psp_summary_lines as 'A' , moved this stmnt above for 2445196
4203:
4204: IF l_dr_cr_flag = 'D' THEN
4205: l_dr_summary_amount := l_dr_summary_amount + l_summary_amount;
4206: ELSIF l_dr_cr_flag = 'C' THEN
4252: DELETE FROM pa_transaction_interface_all
4253: WHERE orig_transaction_reference = to_char(l_summary_line_id)
4254: AND transaction_status_code = 'A'
4255: AND transaction_source = l_txn_source; */
4256: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_summary_lines, psp_adjustment_lines inserted into
4257: psp_adjustment_lines_history then deleted from psp_adjustment_lines for summary_line_id : '||l_summary_line_id);
4258:
4259:
4260: END LOOP; -- End loop for gms_tie_back_success_cur
4474: from pa_transaction_interface_all
4475: where transaction_source in ('OLD', 'GOLD') and
4476: transaction_status_code = 'A' and
4477: -- batch_name = g_gms_batch_name and
4478: batch_name IN (SELECT gms_batch_name FROM psp_summary_lines WHERE payroll_control_id = p_payroll_control_id) and
4479: rownum = 1);
4480:
4481: adj_batch_rec adj_batch_cur%ROWTYPE;
4482: BEGIN
4505: from pa_expenditure_items_all
4506: where transaction_source = 'GOLD'
4507: and orig_transaction_reference in
4508: (select to_char(summary_line_id)
4509: from psp_summary_lines
4510: where payroll_control_id in
4511: (select payroll_control_id
4512: from psp_payroll_controls
4513: where batch_name = l_batch_name
4517: delete from pa_expenditure_items_all
4518: where transaction_source in ('OLD','GOLD')
4519: and orig_transaction_reference in
4520: (select to_char(summary_line_id)
4521: from psp_summary_lines
4522: where payroll_control_id in
4523: (select payroll_control_id
4524: from psp_payroll_controls
4525: where batch_name = l_batch_name
4531: where transaction_source in ('GOLD') and
4532: batch_name = g_gms_batch_name and
4533: orig_transaction_reference in
4534: (select to_char(summary_line_id)
4535: from psp_summary_lines
4536: where payroll_control_id in
4537: (select payroll_control_id
4538: from psp_payroll_controls
4539: where batch_name = l_batch_name
4547: from pa_transaction_interface_all XFACE
4548: where XFACE.transaction_source in('OLD','GOLD')
4549: and XFACE.orig_transaction_reference in
4550: (select to_char(PSL.summary_line_id)
4551: from psp_summary_lines PSL
4552: where PSL.payroll_control_id in
4553: (select PPC.payroll_control_id
4554: from psp_payroll_controls PPC
4555: where PPC.batch_name=l_batch_name
4564: where transaction_source in ('OLD','GOLD') and
4565: batch_name = g_gms_batch_name and
4566: orig_transaction_reference in
4567: (select to_char(summary_line_id)
4568: from psp_summary_lines
4569: where payroll_control_id in
4570: (select payroll_control_id
4571: from psp_payroll_controls
4572: where batch_name = l_batch_name
4634:
4635: delete from psp_adjustment_lines
4636: where batch_name = l_batch_name;
4637:
4638: delete from psp_summary_lines
4639: where payroll_control_id in (select payroll_control_id
4640: from psp_payroll_controls
4641: where batch_name = l_batch_name
4642: and source_type = 'A'); --- added this condn 2133056
4683:
4684: -- Included the following cursors here for accessing the local variables for bug fix 1765678
4685: CURSOR summary_group_cur IS
4686: SELECT MAX(group_id)
4687: FROM psp_summary_lines
4688: WHERE payroll_control_id = payroll_control_rec.payroll_control_id
4689: AND group_id IS NOT NULL;
4690:
4691: CURSOR gl_interface_status_cur IS
4696: AND status = 'NEW';
4697:
4698: CURSOR gms_batch_name_cur IS
4699: SELECT MAX(gms_batch_name)
4700: FROM psp_summary_lines
4701: WHERE payroll_control_id = payroll_control_rec.payroll_control_id
4702: AND gms_batch_name IS NOT NULL;
4703:
4704: CURSOR pa_txn_int_status_p_cur IS
4804: delete from gms_transaction_interface_all
4805: where batch_name = to_char(l_gms_batch_name)
4806: and transaction_source = 'GOLD';
4807:
4808: delete from psp_summary_lines
4809: where payroll_control_id = payroll_control_rec.payroll_control_id
4810: and gms_batch_name = l_gms_batch_name;
4811:
4812: