[Home] [Help]
53:
54:
55: BEGIN
56:
57: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Begin Adj S&T');
58:
59: g_error_api_path := '';
60: fnd_msg_pub.initialize;
61: psp_general.TRANSACTION_CHANGE_PURGEBLE;
64:
65: g_dff_grouping_option := psp_general.get_act_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
66:
67: /*Added the following cursor open-fetch-close for Bug 6902514*/
68: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' open create_stat_batch_in_gms_cur');
69:
70: open create_stat_batch_in_gms_cur;
71: fetch create_stat_batch_in_gms_cur into g_create_stat_batch_in_gms;
72: close create_stat_batch_in_gms_cur;
70: open create_stat_batch_in_gms_cur;
71: fetch create_stat_batch_in_gms_cur into g_create_stat_batch_in_gms;
72: close create_stat_batch_in_gms_cur;
73:
74: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling mark_batch_begin');
75:
76:
77: mark_batch_begin(p_adj_sum_batch_name,
78: p_business_group_id,
85: --retcode := 2;
86: --return;
87: END IF;
88:
89: psp_message_s.print_error(p_mode => FND_FILE.LOG,
90: p_print_header => FND_API.G_FALSE);
91:
92: fnd_msg_pub.initialize;
93:
90: p_print_header => FND_API.G_FALSE);
91:
92: fnd_msg_pub.initialize;
93:
94: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling create_gms_sum_lines for p_adj_sum_batch_name: '||p_adj_sum_batch_name);
95:
96: create_gms_sum_lines(p_adj_sum_batch_name,
97: p_business_group_id,
98: p_set_of_books_id,
104:
105: -- call the user extension to populate attribute1 through attribute30
106: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
107: --- 2968684: added params and exception handler to proc.
108: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling EXT ADJ code 1');
109:
110: psp_st_ext.summary_ext_adjustment(p_adj_sum_batch_name,
111: p_business_group_id ,
112: p_set_of_books_id );
112: p_set_of_books_id );
113: END IF;
114:
115: if g_run_type = 'R' then
116: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling GMS check_interface_status');
117:
118: check_interface_status('GMS', p_adj_sum_batch_name);
119: end if;
120:
119: end if;
120:
121: -- initiate the ogm summarization and transfer
122: if (g_gms_avail = 'Y' OR g_run_type = 'R') then
123: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling transfer_to_gms_interface for p_adj_sum_batch_name: '||p_adj_sum_batch_name);
124:
125: transfer_to_gms_interface(p_adj_sum_batch_name,
126: p_business_group_id,
127: p_set_of_books_id,
127: p_set_of_books_id,
128: l_return_status);
129:
130: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
131: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling GMS check_interface_status for failure');
132:
133: check_interface_status('GMS', p_adj_sum_batch_name);
134: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135: END IF;
134: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135: END IF;
136:
137: -- Check the target system status and do the tieback
138: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling gms_tie_back');
139:
140: gms_tie_back(p_adj_sum_batch_name,
141: p_business_group_id,
142: p_set_of_books_id,
147: END IF;
148:
149: end if; ---- end if for g_gms_avail = 'Y'
150: -- initiate the gl summarization and transfer
151: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling create_gl_sum_lines for p_adj_sum_batch_name: '||p_adj_sum_batch_name);
152:
153: create_gl_sum_lines(p_adj_sum_batch_name,
154: p_business_group_id,
155: p_set_of_books_id,
160:
161: -- call the user extension to populate attribute1 through attribute30
162: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
163: --- 2968684: added params and exception handler to proc.
164: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling EXT ADJ code 2');
165:
166: psp_st_ext.summary_ext_adjustment(p_adj_sum_batch_name,
167: p_business_group_id ,
168: p_set_of_books_id );
168: p_set_of_books_id );
169: END IF;
170:
171: if g_run_type = 'R' then
172: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling GL check_interface_status ');
173:
174: check_interface_status('GL', p_adj_sum_batch_name);
175: end if;
176: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling transfer_to_gl_interface for p_adj_sum_batch_name: '||p_adj_sum_batch_name);
172: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling GL check_interface_status ');
173:
174: check_interface_status('GL', p_adj_sum_batch_name);
175: end if;
176: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling transfer_to_gl_interface for p_adj_sum_batch_name: '||p_adj_sum_batch_name);
177:
178: transfer_to_gl_interface(p_adj_sum_batch_name,
179: p_business_group_id,
180: p_set_of_books_id,
181: l_return_status);
182:
183: IF l_return_status <> FND_API.G_RET_STS_SUCCESS
184: THEN
185: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling GL check_interface_status for failure');
186:
187: check_interface_status('GL', p_adj_sum_batch_name);
188: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189: END IF;
189: END IF;
190:
191: -- dbms_output.put_line('Going to gl_tie_back');
192:
193: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling gl_tie_back ');
194:
195: gl_tie_back(p_adj_sum_batch_name,
196: p_business_group_id,
197: p_set_of_books_id,
200: if l_return_status <> FND_API.G_RET_STS_SUCCESS then
201: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202: end if;
203:
204: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling Mark batch_end ');
205:
206: mark_batch_end(p_adj_sum_batch_name,
207: p_business_group_id,
208: p_set_of_books_id,
212: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213: END IF;
214:
215: if fnd_msg_pub.Count_Msg > 0 then
216: psp_message_s.print_error(p_mode => FND_FILE.LOG,
217: p_print_header => FND_API.G_FALSE);
218: else
219: PSP_MESSAGE_S.Print_success;
220: end if;
219: PSP_MESSAGE_S.Print_success;
220: end if;
221:
222: retcode := FND_API.G_RET_STS_SUCCESS;
223: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' End of Adj S&T');
224:
225: EXCEPTION
226: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227: -- Bug 1776606 : Introduced Rollback ,
226: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227: -- Bug 1776606 : Introduced Rollback ,
228: rollback;
229:
230: psp_message_s.print_error(p_mode => FND_FILE.LOG,
231: p_print_header => FND_API.G_TRUE);
232: retcode := 2;
233: WHEN OTHERS THEN
234: --Bug 1776606 : Introduced Rollback
232: retcode := 2;
233: WHEN OTHERS THEN
234: --Bug 1776606 : Introduced Rollback
235: rollback;
236: psp_message_s.print_error(p_mode => FND_FILE.LOG,
237: p_print_header => FND_API.G_TRUE);
238: retcode := 2;
239: END;
240:
380: SET status_code = 'I',
381: adj_sum_batch_name = p_adj_sum_batch_name,
382: run_id = g_run_id
383: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
384: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' IF updated psp_payroll_controls count: '||SQL%rowcount);
385:
386: else
387: UPDATE psp_payroll_controls
388: SET run_id = g_run_id
386: else
387: UPDATE psp_payroll_controls
388: SET run_id = g_run_id
389: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
390: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' ELSE updated psp_payroll_controls count: '||SQL%rowcount);
391:
392: end if;
393: end if;
394:
482: /* Start of Changes to check migration to OAF Effort Reporting before Supercedence */
483:
484: IF l_migration_status THEN
485:
486: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling PSP_SUM_TRANS.SUPERCEDE_ER');
487:
488:
489: PSP_SUM_TRANS.SUPERCEDE_ER(g_run_id,
490: l_errbuf,
514: WHERE adj_sum_batch_name =p_adj_sum_batch_name))
515: ---AND transaction_status_code = 'A' delete 'R' also, for 2445196
516: AND transaction_source in ('OLD', 'GOLD');
517:
518: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' delete from pa_transaction_interface_all count : '||sql%rowcount);
519:
520: -- introduced following stmnt for 2445196
521: delete from gms_transaction_interface_all
522: -- where batch_name = g_gms_batch_name
526: FROM psp_payroll_controls
527: WHERE adj_sum_batch_name =p_adj_sum_batch_name))
528: and transaction_source = 'GOLD';
529:
530: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' delete from gms_transaction_interface_all count : '||sql%rowcount);
531:
532: OPEN pc_batch_cur;
533: LOOP
534: FETCH pc_batch_cur into pc_batch_rec;
550: l_sum_count := 0;
551: l_adj_count := 0;
552: OPEN sum_lines_cur(payroll_control_rec.payroll_control_id);
553: FETCH sum_lines_cur INTO l_sum_count;
554: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_sum_count: '||l_sum_count);
555:
556: CLOSE sum_lines_cur;
557:
558: OPEN adj_lines_cur(payroll_control_rec.payroll_control_id);
556: CLOSE sum_lines_cur;
557:
558: OPEN adj_lines_cur(payroll_control_rec.payroll_control_id);
559: FETCH adj_lines_cur INTO l_adj_count;
560: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_adj_count: '||l_adj_count);
561:
562: CLOSE adj_lines_cur;
563:
564: IF (l_sum_count =0) and (l_adj_count =0) THEN
564: IF (l_sum_count =0) and (l_adj_count =0) THEN
565: UPDATE psp_payroll_controls
566: SET status_code = 'P'
567: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
568: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' UPDATE psp_payroll_controls status_code P count: '||sql%rowcount);
569:
570: END IF;
571:
572: END LOOP;
749: l_attribute8 VARCHAR2(150);
750: l_attribute9 VARCHAR2(150);
751: l_attribute10 VARCHAR2(150);
752: BEGIN
753: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering create_gl_sum_lines');
754:
755:
756: OPEN pc_batch_cur;
757: LOOP
792: if (l_rec_count > 1) then
793: update psp_payroll_controls
794: set gl_phase = 'Summarize_GL_Lines'
795: where payroll_control_id = payroll_control_rec.payroll_control_id;
796: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updating psp_payroll_controls gl_phase to Summarize_GL_Lines for control_id: '||payroll_control_rec.payroll_control_id);
797:
798: end if;
799: CLOSE gl_sum_lines_cursor;
800: EXIT;
842: nvl( gl_sum_lines_rec.accounting_date, fnd_date.canonical_to_date('1800/01/31')) OR
843: nvl(l_exchange_rate_type,'-999') <>
844: nvl(gl_sum_lines_rec.exchange_rate_type,'-999') THEN
845:
846: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_summary_lines');
847:
848: insert_into_summary_lines(
849: l_summary_line_id,
850: l_person_id,
883:
884: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
885: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
886: END IF;
887: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_summary_lines');
888:
889:
890: FOR j IN 1 .. dist_line_id.COUNT LOOP
891:
897: WHERE adjustment_line_id = l_dist_line_id;
898: END IF;
899:
900: END LOOP;
901: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 1');
902:
903:
904: -- initialise the summary amount and dist_line_id
905: l_summary_amount := 0;
974:
975: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
976: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
977: END IF;
978: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' After insert_into_summary_lines 2 l_summary_line_id: '||l_summary_line_id);
979:
980:
981: FOR j IN 1 .. dist_line_id.COUNT LOOP
982: l_dist_line_id := dist_line_id(j);
987: status_code = 'N'
988: WHERE adjustment_line_id = l_dist_line_id;
989: END IF;
990: END LOOP;
991: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 2 set status_code to N');
992:
993: -- moved this code from above for 3108109
994: if dist_line_id.count > 0 then
995: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling gl_balance_transaction');
991: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 2 set status_code to N');
992:
993: -- moved this code from above for 3108109
994: if dist_line_id.count > 0 then
995: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling gl_balance_transaction');
996:
997: gl_balance_transaction(payroll_control_rec.source_type,
998: payroll_control_rec.payroll_control_id,
999: p_business_group_id,
1316: WHERE pal.payroll_control_id = gl_batch_rec.payroll_control_id
1317: AND rownum = 1); --Bug 2133056 removed distinct and replace batch_name with payroll_control_id
1318:
1319: BEGIN
1320: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering transfer_to_gl_interface ');
1321:
1322:
1323: -- get the source name
1324: get_gl_je_sources(l_user_je_source_name,
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
1414: IF gl_interface_cursor%NOTFOUND THEN
1415: CLOSE gl_interface_cursor;
1416: EXIT;
1417: END IF;
1418: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' gl_interface_cursor count: '||gl_interface_cursor%rowcount);
1419:
1420:
1421: --l_batch_cnt := l_batch_cnt + 1; commented for Bug 1977939
1422:
1429: l_entered_cr := gl_interface_rec.summary_amount;
1430: END IF;
1431:
1432: -- Corrected currency_code reference and introduced exchange_rate_type and conversion_date for bug fix 2916848
1433: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling insert_into_gl_interface ');
1434:
1435: insert_into_gl_interface(
1436: L_SOB_ID,GL_INTERFACE_REC.EFFECTIVE_DATE, gl_batch_rec.currency_code,
1437: L_USER_JE_CATEGORY_NAME,L_USER_JE_SOURCE_NAME,L_ENCUMBRANCE_TYPE_ID,
1487: SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
1488: INTO l_int_run_id
1489: FROM DUAL;
1490:
1491: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Calling insert_into_gl_interface ');
1492:
1493: insert into gl_interface_control(
1494: je_source_name,
1495: status,
1502: l_int_run_id,
1503: l_group_id,
1504: l_sob_id
1505: );
1506: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Submitting Journal Import');
1507:
1508:
1509: req_id := fnd_request.submit_request('SQLGL',
1510: 'GLLEZL',
1528: set gl_phase = 'Submitted_Import_Request'
1529: --where payroll_control_id = gl_batch_rec.payroll_control_id; Replaced this line for Bug 2133056
1530: where source_type = 'A'
1531: and batch_name = pc_batch_rec.batch_name;
1532: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Submitting Journal Import');
1533:
1534:
1535:
1536: commit;
1770: AND status = 'NEW';
1771: l_status_new integer;
1772:
1773: BEGIN
1774: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering gl_tie_back');
1775:
1776:
1777: OPEN pc_batch_cur;
1778: LOOP
1808: set gl_phase = 'Summarize_GL_Lines'
1809: where gl_phase = 'Submitted_Import_Request'
1810: and batch_name = pc_batch_rec.batch_name; --- 3184075
1811: ---and adj_sum_batch_name = p_adj_sum_batch_name;
1812: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updating psp_payroll_controls gl_phase to
1813: Summarize_GL_Lines for batch_name: '||pc_batch_rec.batch_name||' count: '||sql%rowcount);
1814:
1815:
1816: delete from gl_interface
1815:
1816: delete from gl_interface
1817: where group_id = l_group_id
1818: and user_je_source_name = 'OLD';
1819: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gl_interface count: '||sql%rowcount);
1820:
1821:
1822: delete from gl_interface_control
1823: where group_id = l_group_id
1821:
1822: delete from gl_interface_control
1823: where group_id = l_group_id
1824: and je_source_name = 'OLD';
1825: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gl_interface count: '||sql%rowcount);
1826:
1827:
1828: commit;
1829:
1843: END IF;
1844: UPDATE psp_payroll_controls
1845: SET gl_phase = 'GL_Tie_Back'
1846: WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id;
1847: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_payroll_controls set gl_phase to GL_Tie_Back count: '||sql%rowcount||' for control_id : '||gl_tie_back_rec.payroll_control_id);
1848:
1849:
1850: -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
1851:
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:
1947: SET gl_dr_amount = nvl(gl_dr_amount,0) + l_dr_summary_amount,
1948: gl_cr_amount = nvl(gl_cr_amount,0) + l_cr_summary_amount
1949: WHERE payroll_control_id = gl_tie_back_rec.payroll_control_id;
1950:
1951: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_payroll_controls set gl_dr_amount and gl_cr_amount for cotrol_id : '||gl_tie_back_rec.payroll_control_id);
1952:
1953:
1954: END IF;
1955: END LOOP; -- end loop for gl_tie_back_cur
2539:
2540: -- R12 MOAC Uptake
2541: l_org_id number(15);
2542: BEGIN
2543: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering create_gms_sum_lines');
2544:
2545:
2546: OPEN pc_batch_cur;
2547: LOOP
2571: g_gms_avail := 'Y';
2572: UPDATE psp_payroll_controls
2573: SET gms_phase = 'Summarize_GMS_Lines'
2574: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
2575: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updating psp_payroll_controls gms_phase to Summarize_GMS_Lines for control_id: '||payroll_control_rec.payroll_control_id);
2576:
2577: end if;
2578: CLOSE gms_sum_lines_cursor;
2579: EXIT;
2676:
2677: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2678: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2679: END IF;
2680: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' After insert_into_summary_lines 3 l_summary_line_id: '||l_summary_line_id);
2681:
2682:
2683: FOR j IN 1 .. dist_line_id.COUNT LOOP
2684: l_dist_line_id := dist_line_id(j);
2686: UPDATE psp_adjustment_lines
2687: SET summary_line_id = l_summary_line_id WHERE adjustment_line_id = l_dist_line_id;
2688: END IF;
2689: END LOOP;
2690: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 3');
2691:
2692:
2693: -- initialise the summary amount and dist_line_id
2694: l_summary_amount := 0;
2774:
2775: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2776: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2777: END IF;
2778: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' After insert_into_summary_lines 4 l_summary_line_id: '||l_summary_line_id);
2779:
2780:
2781: FOR j IN 1 .. dist_line_id.COUNT LOOP
2782: l_dist_line_id := dist_line_id(j);
2787: status_code = 'N'
2788: WHERE adjustment_line_id = l_dist_line_id;
2789: END IF;
2790: END LOOP;
2791: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_adjustment_lines 4 set status_code to N');
2792:
2793: dist_line_id.delete;
2794: END IF;
2795:
3014: AND psl.status_code = 'N'
3015: AND psl.gl_code_combination_id IS NULL;
3016:
3017: BEGIN
3018: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering transfer to gms_interface');
3019:
3020: -- get the source name
3021: -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
3022: OPEN transaction_source_cur;
3074: OPEN pc_batch_cur;
3075: LOOP
3076: FETCH pc_batch_cur into pc_batch_rec;
3077: IF pc_batch_cur%NOTFOUND THEN
3078: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' pc_batch_cur%NOTFOUND');
3079:
3080: CLOSE pc_batch_cur;
3081: EXIT;
3082: END IF;
3084: OPEN gms_batch_cursor(pc_batch_rec.batch_name);
3085: LOOP
3086: FETCH gms_batch_cursor INTO gms_batch_rec;
3087: IF gms_batch_cursor%NOTFOUND THEN
3088: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' gms_batch_cursor%NOTFOUND');
3089:
3090: CLOSE gms_batch_cursor;
3091: EXIT;
3092: END IF;
3091: EXIT;
3092: END IF;
3093:
3094: l_batch_cnt := l_batch_cnt + 1;
3095: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 1 : '||l_batch_cnt);
3096:
3097:
3098: -- R12 MOAC Uptake. Moved this code to loop
3099: FOR I in 1..org_id_tab.count
3142: -- fnd_msg_pub.add;
3143: cleanup_batch_details(gms_batch_rec.payroll_control_id,null);
3144: close gms_batch_cursor;
3145: l_batch_cnt := l_batch_cnt - 1;
3146: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 2 : '||l_batch_cnt);
3147:
3148: exit;
3149: END IF;
3150: CLOSE time_period_cur;
3164: -- end if;
3165: CLOSE gms_interface_cursor;
3166: EXIT;
3167: END IF;
3168: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' gms_interface_cursor count: '||gms_interface_cursor%rowcount);
3169:
3170:
3171: -- get the employee number
3172: -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
3186: fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); --Included for bug fix 1828519
3187: fnd_message.set_token('PERSON_NAME',l_person_name); --Included for bug fix 1828519
3188: fnd_msg_pub.add;
3189: l_batch_cnt := l_batch_cnt - 1;
3190: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 3 : '||l_batch_cnt);
3191:
3192: -- Commented the following message as part of bug fix 1828519
3193: -- fnd_message.set_name('PSP', 'PSP_ADJ_GMS_FAILED');
3194: -- fnd_message.set_token('ERR_NAME', 'PERSON ID NOT FOUND');
3220: fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); -- Included for bug fix 1828519
3221: fnd_message.set_token('PERSON_NAME',l_person_name); -- Included for bug fix 1828519
3222: fnd_msg_pub.add;
3223: l_batch_cnt := l_batch_cnt - 1;
3224: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 4 : '||l_batch_cnt);
3225:
3226: -- Commented the following message as part of bug fix 1828519
3227: -- fnd_message.set_name('PSP', 'PSP_ADJ_GMS_FAILED');
3228: -- fnd_message.set_token('ERR_NAME', 'ORGANIZATION NOT FOUND');
3254: fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); -- Included for bug fix 1828519
3255: fnd_message.set_token('PERSON_NAME',l_person_name); -- Included for bug fix 1828519
3256: fnd_msg_pub.add;
3257: l_batch_cnt := l_batch_cnt - 1;
3258: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 5 : '||l_batch_cnt);
3259:
3260: -- Commented the following message as part of bug fix 1828519
3261: -- fnd_message.set_name('PSP', 'PSP_ADJ_GMS_FAILED');
3262: -- fnd_message.set_token('ERR_NAME', 'ORGANIZATION NOT FOUND');
3288: fnd_message.set_token('BATCH_NAME',pc_batch_rec.batch_name); -- Included for bug fix 1828519
3289: fnd_message.set_token('PERSON_NAME',l_person_name); -- Included for bug fix 1828519
3290: fnd_msg_pub.add;
3291: l_batch_cnt := l_batch_cnt - 1;
3292: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 6 : '||l_batch_cnt);
3293:
3294: -- Commented the following message as part of bug fix 1828519
3295: -- fnd_message.set_name('PSP', 'PSP_ADJ_GMS_FAILED');
3296: -- fnd_message.set_token('ERR_NAME', 'ORGANIZATION NOT FOUND');
3337:
3338:
3339: -- Corrected currency code value, introduced acct_rate_type and acct_rate_date values for bug fix 2916848
3340: IF (gms_batch_rec.currency_code <> 'STAT') THEN -- code changes for bug 5167562
3341: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_pa_interface for USD ');
3342:
3343: insert_into_pa_interface(l_txn_interface_id,
3344: L_TXN_SOURCE,
3345: L_GMS_BATCH_NAME,
3379: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3380: END IF;
3381: --- moved code ends
3382: ELSE
3383: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' calling insert_into_pa_interface for STAT ');
3384:
3385: insert_into_pa_interface(l_txn_interface_id,
3386: L_TXN_SOURCE,
3387: L_GMS_BATCH_NAME,
3453: END LOOP;
3454: -- For a failed small batch spanning more than one payroll control id, Transaction import is kicked off,
3455: -- Fixed this issue thru the following code along with bug fix 1828519
3456:
3457: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_batch_cnt 7 : '||l_batch_cnt);
3458:
3459: IF (l_batch_cnt = 0) THEN
3460: CLOSE gms_batch_cursor;
3461: EXIT;
3475: SET BATCH_NAME = l_gms_stat_batch_name
3476: where DENOM_CURRENCY_CODE = 'STAT'
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
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
3493: SELECT count(*) INTO l_not_stat_count
3494: FROM pa_transaction_interface_all
3495: WHERE BATCH_NAME = l_gms_batch_name;
3496:
3497: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_batch_name'||l_gms_batch_name);
3498: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_stat_batch_name'||l_gms_stat_batch_name);
3499: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_not_stat_count'||l_not_stat_count);
3500:
3501:
3494: FROM pa_transaction_interface_all
3495: WHERE BATCH_NAME = l_gms_batch_name;
3496:
3497: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_batch_name'||l_gms_batch_name);
3498: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_stat_batch_name'||l_gms_stat_batch_name);
3499: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_not_stat_count'||l_not_stat_count);
3500:
3501:
3502:
3495: WHERE BATCH_NAME = l_gms_batch_name;
3496:
3497: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_batch_name'||l_gms_batch_name);
3498: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_stat_batch_name'||l_gms_stat_batch_name);
3499: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_not_stat_count'||l_not_stat_count);
3500:
3501:
3502:
3503:
3507: FOR I in 1..org_id_tab.count
3508: LOOP
3509: l_gms_batch_name := gms_batch_name_tab(I);
3510:
3511: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_batch_name1 : '||l_gms_batch_name);
3512:
3513: -- set the context to single to submit_request
3514: mo_global.set_policy_context('S', org_id_tab(I) );
3515: fnd_request.set_org_id (org_id_tab(I) );
3536: FOR J in 1..org_id_tab.count --13502385
3537: LOOP --13502385
3538: l_gms_batch_name := gms_batch_name_tab(J); --13502385
3539:
3540: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'l_gms_batch_name2 : '||l_gms_batch_name); --13502385
3541:
3542: OPEN payroll_control_id_cur;
3543: FETCH payroll_control_id_cur BULK COLLECT INTO r_payroll_controls.payroll_control_id;
3544: CLOSE payroll_control_id_cur;
3550:
3551:
3552: FOR I IN 1..r_payroll_controls.payroll_control_id.COUNT
3553: LOOP
3554: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updating psp_payroll_controls count: '||sql%bulk_rowcount(I));
3555: END LOOP;
3556:
3557: END LOOP; --13502385
3558:
3586: SELECT count(*) INTO l_stat_count -- change
3587: FROM pa_transaction_interface_all
3588: WHERE BATCH_NAME = l_gms_stat_batch_name;
3589:
3590: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_stat_count'||l_stat_count);
3591:
3592: g_skip_flag_gms := 'N';
3593:
3594: l_gms_batch_name := l_gms_stat_batch_name;
3598:
3599: g_skip_flag_gms := 'Y';
3600:
3601: IF (g_create_stat_batch_in_gms = 'Y') THEN
3602: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'STAT entrance');
3603:
3604: g_skip_flag_gms := 'N';
3605:
3606: FOR I in 1..org_id_tab.count
3610: -- set the context to single to submit_request
3611: mo_global.set_policy_context('S', org_id_tab(I) );
3612: fnd_request.set_org_id (org_id_tab(I) );
3613:
3614: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Submitting PRC: Transaction Import for STAT');
3615:
3616: req_id_tab(i) := fnd_request.submit_request(
3617: 'PA',
3618: 'PAXTRTRX',
3642: WHERE payroll_control_id = r_payroll_controls.payroll_control_id(I);
3643:
3644: FOR I IN 1..r_payroll_controls.payroll_control_id.COUNT
3645: LOOP
3646: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updating psp_payroll_controls count: '||sql%bulk_rowcount(I));
3647: END LOOP;
3648:
3649:
3650:
3683:
3684:
3685: END IF; -- l_batch_cnt -- end of changes for 6902514
3686:
3687: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' g_skip_flag_gms = '||g_skip_flag_gms);
3688:
3689: p_return_status := fnd_api.g_ret_sts_success;
3690: EXCEPTION
3691: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3875: and expenditure_group is not null;
3876:
3877: l_exp_group_name varchar2(20) := NULL;
3878: begin
3879: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Entering PROCESS_COMPLETE ');
3880:
3881:
3882: open get_completion;
3883: loop
3891: return FALSE;
3892: end if;
3893:
3894: if get_completion_rec.transaction_status_code = 'P' then
3895: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' get_completion_rec.transaction_status_code = P');
3896:
3897:
3898: -- -------------------------------------------------------------------------------------------
3899: -- If transaction_status_code = 'P' then the transaction import process did not kick off
3904: delete from pa_transaction_interface_all
3905: where batch_name = to_char(l_gms_batch_name)
3906: and transaction_source = l_txn_source;
3907:
3908: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_transaction_interface_all count: '||sql%rowcount);
3909:
3910:
3911: if (l_txn_source = 'GOLD') then
3912: delete from gms_transaction_interface_all
3911: if (l_txn_source = 'GOLD') then
3912: delete from gms_transaction_interface_all
3913: where batch_name = to_char(l_gms_batch_name)
3914: and transaction_source = 'GOLD';
3915: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gms_transaction_interface_all count: '||sql%rowcount);
3916:
3917: end if;
3918:
3919: /* Commented for 2133056, Let recover start from Import and not Summarize
3926: set gms_phase = 'Summarize_GMS_Lines'
3927: where gms_phase is not null
3928: and adj_sum_batch_name = p_adj_sum_batch_name;
3929:
3930: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_payroll_controls set gms_phase to Summarize_GMS_Lines
3931: for adj_sum_batch_name: '||p_adj_sum_batch_name||' count: '||sql%rowcount);
3932:
3933: commit;
3934: return false;
3932:
3933: commit;
3934: return false;
3935: elsif get_completion_rec.transaction_status_code = 'I' then
3936: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' get_completion_rec.transaction_status_code = I');
3937:
3938: -- -------------------------------------------------------------------------------------------
3939: -- If transaction_status_code = 'I' then the transaction import process did not complete
3940: -- the Post Processing extension. So return 'NOT_COMPLETE' in this case.
3958: (select expenditure_id
3959: from pa_expenditures_all
3960: where expenditure_group = l_exp_group_name));
3961:
3962: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gms_award_distributions count: '||sql%rowcount);
3963:
3964: delete pa_expenditure_items_all
3965: where transaction_source in ('OLD','GOLD')
3966: and expenditure_id in
3967: (select expenditure_id
3968: from pa_expenditures_all
3969: where expenditure_group = l_exp_group_name);
3970:
3971: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditure_items_all count: '||sql%rowcount);
3972:
3973: delete pa_expenditures_all
3974: where expenditure_group = l_exp_group_name;
3975:
3972:
3973: delete pa_expenditures_all
3974: where expenditure_group = l_exp_group_name;
3975:
3976: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditures_all count: '||sql%rowcount);
3977:
3978: delete pa_expenditure_Groups_all
3979: where expenditure_group = l_exp_group_name;
3980: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditure_Groups_all count: '||sql%rowcount);
3976: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditures_all count: '||sql%rowcount);
3977:
3978: delete pa_expenditure_Groups_all
3979: where expenditure_group = l_exp_group_name;
3980: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from pa_expenditure_Groups_all count: '||sql%rowcount);
3981:
3982: end if;
3983:
3984: if (l_txn_source = 'GOLD') then
3984: if (l_txn_source = 'GOLD') then
3985: delete gms_transaction_interface_all
3986: where transaction_source in ('GOLD') and
3987: batch_name = l_gms_batch_name;
3988: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' deleted from gms_transaction_interface_all count: '||sql%rowcount);
3989:
3990: end if;
3991:
3992: delete pa_transaction_interface_all
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
4007: where gms_phase is not null
4008: and source_type = 'A'
4009: and adj_sum_batch_name = p_adj_sum_batch_name;
4010:
4011: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_payroll_controls set gms_phase to null count: '||sql%rowcount);
4012:
4013: commit;
4014: return false;
4015:
4019: end loop;
4020:
4021: exception
4022: when others then
4023: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' exception = '||sqlerrm);
4024:
4025: fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','GET_PROCESS');
4026: return FALSE;
4027: end PROCESS_COMPLETE;
4027: end PROCESS_COMPLETE;
4028:
4029: BEGIN
4030:
4031: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering gms_tie_back');
4032:
4033:
4034: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' g_run_id..' || g_run_id);
4035:
4030:
4031: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering gms_tie_back');
4032:
4033:
4034: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' g_run_id..' || g_run_id);
4035:
4036:
4037:
4038: open pc_batch_cur;
4043: exit;
4044: end if;
4045:
4046: --dbms_output.put_line('Getting the batches..' || pc_batch_rec.batch_name);
4047: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Getting the batches..' || pc_batch_rec.batch_name);
4048:
4049: -- Added for bug 9481186
4050: IF gms_tie_back_cur%ISOPEN THEN
4051: close gms_tie_back_cur;
4058: close gms_tie_back_cur;
4059: exit;
4060: end if;
4061:
4062: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Getting the payroll.. ' || to_char(gms_tie_back_rec.payroll_control_id));
4063:
4064: --dbms_output.put_line('Getting the payroll.. ' || to_char(gms_tie_back_rec.payroll_control_id));
4065: -- Replaced the earlier 'select stmt.' code with new 'cursor' code for bug fix 1765678
4066:
4078: EXIT;
4079: END IF;
4080: -- CLOSE gms_batch_name_cur;
4081:
4082: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' before userhook - l_gms_batch_name='||l_gms_batch_name);
4083: ----- new procedure for 5463110
4084: psp_st_ext.tieback_adjustment(gms_tie_back_rec.payroll_control_id,
4085: pc_batch_rec.batch_name,
4086: l_gms_batch_name ,
4085: pc_batch_rec.batch_name,
4086: l_gms_batch_name ,
4087: p_business_group_id,
4088: p_set_of_books_id );
4089: --fnd_file.put_line(fnd_file.log, 'after user hook');
4090: hr_utility.trace('after userhook - ');
4091: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'after userhook - ');
4092:
4093:
4087: p_business_group_id,
4088: p_set_of_books_id );
4089: --fnd_file.put_line(fnd_file.log, 'after user hook');
4090: hr_utility.trace('after userhook - ');
4091: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||'after userhook - ');
4092:
4093:
4094: OPEN transaction_source_cur;
4095: FETCH transaction_source_cur INTO l_txn_source;
4126: EXIT;
4127: END IF;
4128: CLOSE cnt_gms_interface_cur;
4129:
4130: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' l_cnt_gms_interface : ' || to_char(l_cnt_gms_interface));
4131:
4132: IF l_cnt_gms_interface > 0 THEN
4133: -- Commented the following loop for finding rejection reason code as part of bug fix 1828519
4134: -- OPEN gms_tie_back_reject_cur(l_gms_batch_name, gms_tie_back_rec.payroll_control_id, l_txn_source);
4150: /* 1685685 added update statement...to clean pa tables in cleanup_batch_details..Venkat.*/
4151: update psp_payroll_controls
4152: set gms_phase = 'GMS_Tie_Back'
4153: where payroll_control_id = gms_tie_back_rec.payroll_control_id;
4154: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' updated psp_payroll_controls for control_id = '||gms_tie_back_rec.payroll_control_id);
4155:
4156: cleanup_batch_details(gms_tie_back_rec.payroll_control_id,null);
4157: close gms_tie_back_cur;
4158: CLOSE gms_batch_name_cur;
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.
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
4263: SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
4264: ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
4265: WHERE payroll_control_id = gms_tie_back_rec.payroll_control_id;
4266:
4267: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) ||' Updated psp_payroll_controls set ogm_dr_amount and ogm_cr_amount for cotrol_id : '||gms_tie_back_rec.payroll_control_id);
4268:
4269: END IF;
4270: END LOOP; -- End Loop for gms_batch_name_cur
4271:
4270: END LOOP; -- End Loop for gms_batch_name_cur
4271:
4272: if gms_tie_back_cur%isopen then -- added for the bug 11841641
4273: NULL;
4274: --fnd_file.put_line(fnd_file.log,' gms_batch_name_cur open');
4275: else
4276: exit;
4277: end if;
4278: