[Home] [Help]
64: AND legislation_code IS NULL
65: AND NVL(business_group_id, p_business_group_id) = p_business_group_id; -- change
66:
67: BEGIN
68: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering sum_and_transfer ');
69:
70: ---hr_utility.trace_on('Y','SandT');
71: g_error_api_path := '';
72: fnd_msg_pub.initialize;
92:
93:
94: g_dff_grouping_option := psp_general.get_act_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
95:
96: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling mark_batch_begin');
97:
98: mark_batch_begin(p_source_type,
99: p_source_code,
100: p_payroll_id,
107: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
108: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109: END IF;
110:
111: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' FIRST NORMAL RUN');
112: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_gl_sum_lines');
113:
114:
115: -- FIRST NORMAL RUN
108: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109: END IF;
110:
111: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' FIRST NORMAL RUN');
112: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_gl_sum_lines');
113:
114:
115: -- FIRST NORMAL RUN
116: -- initiate the gl summarization and transfer
136: p_business_group_id,
137: p_set_of_books_id );
138: END IF;
139:
140: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling transfer_to_gl_interface');
141:
142: transfer_to_gl_interface(p_source_type,
143: p_source_code,
144: p_time_period_id,
150: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
151: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
152: END IF;
153:
154: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_gms_sum_lines');
155:
156: create_gms_sum_lines(p_source_type,
157: p_source_code,
158: p_time_period_id,
175: p_business_group_id,
176: p_set_of_books_id );
177: END IF;
178:
179: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling transfer_to_gms_interface');
180: transfer_to_gms_interface(p_source_type,
181: p_source_code,
182: p_time_period_id,
183: p_batch_name,
188: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
189: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190: END IF;
191:
192: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' SECOND RUN TO TAKE CARE OF TIE-BACK');
193:
194:
195: -- SECOND RUN TO TAKE CARE OF TIE-BACK
196: -- initiate the gl summarization
193:
194:
195: -- SECOND RUN TO TAKE CARE OF TIE-BACK
196: -- initiate the gl summarization
197: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling create_gl_sum_lines');
198:
199: create_gl_sum_lines(p_source_type,
200: p_source_code,
201: p_time_period_id,
219: p_business_group_id,
220: p_set_of_books_id );
221: END IF;
222:
223: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling transfer_to_gl_interface');
224: transfer_to_gl_interface(p_source_type,
225: p_source_code,
226: p_time_period_id,
227: p_batch_name,
233: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
234: END IF;
235:
236:
237: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling create_gms_sum_lines');
238:
239: create_gms_sum_lines(p_source_type,
240: p_source_code,
241: p_time_period_id,
259: p_business_group_id,
260: p_set_of_books_id );
261: END IF;
262:
263: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling transfer_to_gms_interface');
264:
265: transfer_to_gms_interface(p_source_type,
266: p_source_code,
267: p_time_period_id,
272:
273: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
274: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
275: END IF;
276: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling mark_batch_end');
277:
278:
279: mark_batch_end(p_source_type,
280: p_source_code,
314: p_msg_index_out => l_msg_count); */
315:
316: g_error_api_path := 'SUM_AND_TRANSFER:'||g_error_api_path;
317: errbuf := SUBSTR(l_msg_data || fnd_global.local_chr(10) ||fnd_global.local_chr(10)||g_error_api_path,1,230);
318: psp_message_s.print_error(p_mode => FND_FILE.LOG,
319: p_print_header => FND_API.G_TRUE);
320: retcode := 2;
321: WHEN OTHERS THEN
322: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
318: psp_message_s.print_error(p_mode => FND_FILE.LOG,
319: p_print_header => FND_API.G_TRUE);
320: retcode := 2;
321: WHEN OTHERS THEN
322: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
323:
324: /* Introduced as part of Bug fix #1776606 */
325: ROLLBACK;
326: /* Commented as part of Bug fix #1776606
344: p_msg_index_out => l_msg_count); */
345:
346: g_error_api_path := 'SUM_AND_TRANSFER:'||g_error_api_path;
347: errbuf := SUBSTR(l_msg_data || fnd_global.local_chr(10) ||fnd_global.local_chr(10)||g_error_api_path,1,230);
348: psp_message_s.print_error(p_mode => FND_FILE.LOG,
349: p_print_header => FND_API.G_TRUE);
350: retcode := 2;
351: END;
352:
445: end loop;
446: /* Introduced as part of Bug fix #1776606 */
447: EXCEPTION
448: WHEN OTHERS THEN
449: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
450:
451: g_error_api_path := 'MARK_BATCH_BEGIN: ' || g_error_api_path;
452: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','MARK_BATCH_BEGIN');
453: p_return_status := fnd_api.g_ret_sts_unexp_error;
618:
619: /* Introduced as part of Bug fix #1776606 */
620: EXCEPTION
621: WHEN OTHERS THEN
622: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
623:
624: g_error_api_path := 'MARK_BATCH_END: ' || g_error_api_path;
625: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','MARK_BATCH_END');
626: p_return_status := fnd_api.g_ret_sts_unexp_error;
833: l_period_end_date DATE;
834: l_begin_of_time DATE := to_date('01/01/1900','dd/mm/yyyy');
835:
836: BEGIN
837: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entered create_gl_summary_lines');
838:
839: OPEN payroll_control_cur;
840: LOOP
841: FETCH payroll_control_cur INTO payroll_control_rec;
876: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
877: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
878: END IF;
879:
880: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' openinging gl_sum_lines_cursor');
881:
882: OPEN gl_sum_lines_cursor(payroll_control_rec.payroll_control_id);
883: l_rec_count := 0;
884: l_summary_amount := 0;
898: END IF;
899: --
900:
901: IF l_rec_count = 1 THEN
902: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_rec_count = 1');
903:
904: l_person_id := gl_sum_lines_rec.person_id;
905: l_assignment_id := gl_sum_lines_rec.assignment_id;
906: l_gl_ccid := gl_sum_lines_rec.gl_ccid;
940: nvl(gl_sum_lines_rec.accounting_date,l_begin_of_time) OR --------- added two condns for 3108109
941: nvl(l_exchange_rate_type,'-999') <>
942: nvl(gl_sum_lines_rec.exchange_rate_type,'-999') THEN
943:
944: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling insert_into_summary_lines');
945: -- insert into summary lines
946: insert_into_summary_lines(
947: l_summary_line_id,
948: l_person_id,
988: l_dist_line_id := dist_line_id(j);
989:
990: IF gl_sum_lines_rec.tab_flag = 'D' THEN
991:
992: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updating psp_distribution_lines setting summary_line_id : '||l_summary_line_id);
993:
994: UPDATE psp_distribution_lines
995: SET summary_line_id = l_summary_line_id WHERE distribution_line_id = l_dist_line_id;
996: ELSIF gl_sum_lines_rec.tab_flag = 'P' THEN
994: UPDATE psp_distribution_lines
995: SET summary_line_id = l_summary_line_id WHERE distribution_line_id = l_dist_line_id;
996: ELSIF gl_sum_lines_rec.tab_flag = 'P' THEN
997:
998: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updating psp_pre_gen_dist_lines setting summary_line_id : '||l_summary_line_id);
999:
1000: UPDATE psp_pre_gen_dist_lines
1001: SET summary_line_id = l_summary_line_id WHERE pre_gen_dist_line_id = l_dist_line_id;
1002: END IF;
1036: END LOOP;
1037:
1038: IF l_rec_count > 1 THEN
1039:
1040: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_rec_count: '||l_rec_count);
1041: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling insert_into_summary_lines');
1042:
1043: -- insert into summary lines
1044: insert_into_summary_lines(
1037:
1038: IF l_rec_count > 1 THEN
1039:
1040: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_rec_count: '||l_rec_count);
1041: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling insert_into_summary_lines');
1042:
1043: -- insert into summary lines
1044: insert_into_summary_lines(
1045: l_summary_line_id,
1087: FOR j IN 1 .. dist_line_id.COUNT LOOP
1088: l_dist_line_id := dist_line_id(j);
1089:
1090: IF gl_sum_lines_rec.tab_flag = 'D' THEN
1091: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updating psp_distribution_lines status_code to N and summary_line_id to '||l_summary_line_id||' for dist_line_id: '||l_dist_line_id);
1092:
1093: UPDATE psp_distribution_lines
1094: SET summary_line_id = l_summary_line_id,
1095: status_code = 'N'
1095: status_code = 'N'
1096: WHERE distribution_line_id = l_dist_line_id;
1097: ELSIF gl_sum_lines_rec.tab_flag = 'P' THEN
1098:
1099: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updating psp_distribution_lines status_code to N and summary_line_id to '||l_summary_line_id||' for dist_line_id: '||l_dist_line_id);
1100:
1101: UPDATE psp_pre_gen_dist_lines
1102: SET summary_line_id = l_summary_line_id,
1103: status_code = 'N'
1111: END LOOP;
1112: --
1113: p_return_status := fnd_api.g_ret_sts_success;
1114:
1115: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving create_gl_sum_lines');
1116:
1117: EXCEPTION
1118:
1119: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1119: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1120: g_error_api_path := 'CREATE_GL_SUM_LINES:'||g_error_api_path;
1121: p_return_status := fnd_api.g_ret_sts_unexp_error;
1122: WHEN OTHERS THEN
1123: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1124:
1125: g_error_api_path := 'CREATE_GL_SUM_LINES:'||g_error_api_path;
1126: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','CREATE_GL_SUM_LINES');
1127: p_return_status := fnd_api.g_ret_sts_unexp_error;
1269: --
1270: p_return_status := fnd_api.g_ret_sts_success;
1271: EXCEPTION
1272: WHEN OTHERS THEN
1273: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1274:
1275: --dbms_output.put_line('Errrrrrrrrrrrrrrrrrrrrrrrrrrorrrrrrrrrrr.........');
1276: g_error_api_path := 'INSERT_INTO_SUMMARY_LINES:'||g_error_api_path;
1277: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','INSERT_INTO_SUMMARY_LINES');
1404: l_table VARCHAR2(100);
1405: BEGIN
1406:
1407: -- get the source name
1408: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' entered transfer_to_gl_interface');
1409: get_gl_je_sources(l_user_je_source_name,
1410: l_return_status);
1411: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1412: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1420: END IF;
1421:
1422: /* Changed the following code for Bug 3112053 , so as to submit one journal import for a batch having same
1423: source_code,source_type,time_period_id,batch_name combination */
1424: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' open g_batch_cursor');
1425:
1426: OPEN gl_batch_cursor;
1427: LOOP
1428:
1460: l_reference1 := gl_batch_rec.source_type||':'||gl_batch_rec.payroll_source_code||':'||l_period_name||':'||gl_batch_rec.batch_name;
1461:
1462: l_reference4 := 'LD ACTUALS DISTRIBUTION';
1463:
1464: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' open gl_payroll_control_cursor');
1465:
1466: OPEN gl_payroll_control_cursor(gl_batch_rec.source_type, gl_batch_rec.payroll_source_code,
1467: gl_batch_rec.time_period_id, gl_batch_rec.batch_name, gl_batch_rec.phase);
1468:
1472: FETCH gl_payroll_control_cursor INTO gl_payroll_control_rec;
1473: EXIT WHEN gl_payroll_control_cursor%NOTFOUND;
1474: -- update psp_summary_lines with group_id
1475:
1476: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' update psp_summary_lines with group_id : '||l_group_id);
1477:
1478: UPDATE psp_summary_lines
1479: SET group_id = l_group_id
1480: WHERE status_code = 'N'
1480: WHERE status_code = 'N'
1481: AND gl_code_combination_id IS NOT NULL
1482: AND payroll_control_id = gl_payroll_control_rec.payroll_control_id;
1483:
1484: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' open gl_interface_cursor');
1485:
1486: OPEN gl_interface_cursor(gl_payroll_control_rec.payroll_control_id);
1487: LOOP
1488: FETCH gl_interface_cursor INTO gl_interface_rec;
1505:
1506: -- Changed The parameter g_currency_code to gl_batch_rec.currency_code, Introduced exchange_rate_type
1507: -- and conversion_date for Bug fix 2916848
1508:
1509: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling insert_into_gl_interface GL_INTERFACE_REC.SUMMARY_LINE_ID: '||GL_INTERFACE_REC.SUMMARY_LINE_ID);
1510:
1511: insert_into_gl_interface(
1512: P_SET_OF_BOOKS_ID, GL_INTERFACE_REC.EFFECTIVE_DATE,
1513: gl_payroll_control_rec.CURRENCY_CODE,
1588:
1589: SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
1590: INTO l_int_run_id
1591: FROM dual;
1592: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' insert into gl_interface_control');
1593: INSERT into gl_interface_control(je_source_name,status,interface_run_id,
1594: group_id,set_of_books_id)
1595: VALUES (l_user_je_source_name, 'S',l_int_run_id,
1596: l_group_id,p_set_of_books_id);
1595: VALUES (l_user_je_source_name, 'S',l_int_run_id,
1596: l_group_id,p_set_of_books_id);
1597:
1598: --dbms_output.put_line('Calling GL .............');
1599: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' submitting request for journal import');
1600:
1601: req_id := fnd_request.submit_request(
1602: 'SQLGL',
1603: 'GLLEZL',
1635: AND set_of_books_id = p_set_of_books_id
1636: AND run_id = g_run_id;
1637: commit;
1638:
1639: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Committed after updating psp_payroll_controls SET phase = Submitted_JI_Request count: '||sql%rowcount);
1640:
1641: gl_batch_rec.phase := 'Submitted_JI_Request'; ---2444657
1642: call_status := fnd_concurrent.wait_for_request(req_id, 20, 0,
1643: rphase, rstatus, dphase, dstatus, message);
1668: -- mark the successfully transferred records as 'A' in psp_summary_lines and psp_distribution_lines
1669: -- and transfer the successful records to the history table
1670: -- 1874696:changed l_period_end_date to NULL in tie back call
1671: -- Bug 22663344 reverted null value to l_period_end_date
1672: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gl_tie_back');
1673:
1674: gl_tie_back(gl_payroll_control_rec.payroll_control_id,
1675: gl_batch_rec.source_type,
1676: l_period_end_date,
1708: WHEN RETURN_BACK THEN
1709: p_return_status := fnd_api.g_ret_sts_success;
1710:
1711: WHEN OTHERS THEN
1712: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1713:
1714: g_error_api_path := 'TRANSFER_TO_GL_INTERFACE:'||g_error_api_path;
1715: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','TRANSFER_TO_GL_INTERFACE');
1716: p_return_status := fnd_api.g_ret_sts_unexp_error;
1735: fnd_message.set_token('PRODUCT',l_product);
1736: fnd_msg_pub.add;
1737: p_return_status := fnd_api.g_ret_sts_unexp_error;
1738: WHEN OTHERS THEN
1739: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1740:
1741: g_error_api_path := 'GL_JE_SOURCES:'||g_error_api_path;
1742: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','GL_JE_SOURCES');
1743: p_return_status := fnd_api.g_ret_sts_unexp_error;
1762: fnd_message.set_token('PRODUCT',l_product);
1763: fnd_msg_pub.add;
1764: p_return_status := fnd_api.g_ret_sts_unexp_error;
1765: WHEN OTHERS THEN
1766: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1767:
1768: g_error_api_path := 'GL_JE_CATEGORY_NAME:'||g_error_api_path;
1769: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','GL_JE_CATEGORY_NAME');
1770: p_return_status := fnd_api.g_ret_sts_unexp_error;
1791: fnd_message.set_token('PRODUCT',l_product);
1792: fnd_msg_pub.add;
1793: p_return_status := fnd_api.g_ret_sts_unexp_error;
1794: WHEN OTHERS THEN
1795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1796:
1797: g_error_api_path := 'ENCUMBRANCE_TYPE_ID:'||g_error_api_path;
1798: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','ENCUMBRANCE_TYPE_ID');
1799: p_return_status := fnd_api.g_ret_sts_unexp_error;
2020: and hou.organization_id = paf.organization_id;
2021:
2022: BEGIN
2023:
2024: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entered gl_tie_back');
2025:
2026: select count(*)
2027: into l_no_run
2028: from gl_interface
2032: AND reference6 in(select summary_line_id FROM psp_summary_lines -- Bug 7376898
2033: WHERE GROUP_ID = p_group_id
2034: AND payroll_control_id = p_payroll_control_id);
2035:
2036: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_no_run: '||l_no_run);
2037:
2038: if l_no_run > 0 then
2039:
2040: delete from gl_interface
2043: AND reference6 in(select summary_line_id FROM psp_summary_lines -- Bug 7376898
2044: WHERE GROUP_ID = p_group_id
2045: AND payroll_control_id = p_payroll_control_id);
2046:
2047: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface count: '||sql%rowcount);
2048:
2049:
2050: delete from psp_summary_lines
2051: where payroll_control_id = p_payroll_control_id
2049:
2050: delete from psp_summary_lines
2051: where payroll_control_id = p_payroll_control_id
2052: and group_id = p_group_id;
2053: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_summary_lines count: '||sql%rowcount);
2054:
2055: --- bug 4328598 -- fix begin
2056:
2057: -- get the source name
2054:
2055: --- bug 4328598 -- fix begin
2056:
2057: -- get the source name
2058: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling get_gl_je_source');
2059:
2060: get_gl_je_sources(l_user_je_source_name,
2061: l_return_status);
2062: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2066: delete gl_interface_Control
2067: where group_id = p_group_id
2068: and je_source_name = l_user_je_source_name;
2069:
2070: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface_control count: '||sql%rowcount);
2071:
2072: update psp_payroll_controls
2073: set phase = null
2074: where payroll_control_id = p_payroll_control_id;
2073: set phase = null
2074: where payroll_control_id = p_payroll_control_id;
2075:
2076: commit;
2077: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_payroll_controls count: '||sql%rowcount);
2078:
2079: --- bug 4328598--- fix end
2080: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Commit');
2081:
2076: commit;
2077: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_payroll_controls count: '||sql%rowcount);
2078:
2079: --- bug 4328598--- fix end
2080: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Commit');
2081:
2082:
2083: fnd_message.set_name('PSP','PSP_JI_DID_NOT_RUN');
2084: fnd_message.set_token('PAYROLL_CONTROL_ID',p_payroll_control_id);
2350: effective_date = l_effective_date, --- for Bug 2663344
2351: suspense_auto_glccid = l_new_glccid, --- added suspense_auto for 5080403
2352: suspense_auto_exp_type = l_new_expenditure_type
2353: WHERE rowid = l_rowid;
2354: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 1 updated psp_distribution_lines count: '||sql%rowcount);
2355: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2356:
2357: ELSIF p_source_type = 'P' THEN
2358: UPDATE psp_pre_gen_dist_lines
2351: suspense_auto_glccid = l_new_glccid, --- added suspense_auto for 5080403
2352: suspense_auto_exp_type = l_new_expenditure_type
2353: WHERE rowid = l_rowid;
2354: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 1 updated psp_distribution_lines count: '||sql%rowcount);
2355: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2356:
2357: ELSIF p_source_type = 'P' THEN
2358: UPDATE psp_pre_gen_dist_lines
2359: SET suspense_org_account_id = l_organization_account_id,
2362: effective_date = l_effective_date, --- for Bug 2663344
2363: suspense_auto_glccid = l_new_glccid, --- added suspense_auto for 5080403
2364: suspense_auto_exp_type = l_new_expenditure_type
2365: WHERE rowid = l_rowid;
2366: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 1 updated psp_pre_gen_dist_lines count: '||sql%rowcount);
2367: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2368:
2369: END IF;
2370: ELSE -- introduced for 2479579
2363: suspense_auto_glccid = l_new_glccid, --- added suspense_auto for 5080403
2364: suspense_auto_exp_type = l_new_expenditure_type
2365: WHERE rowid = l_rowid;
2366: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 1 updated psp_pre_gen_dist_lines count: '||sql%rowcount);
2367: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2368:
2369: END IF;
2370: ELSE -- introduced for 2479579
2371: l_susp_exception := l_susp_ac_found;
2389: AND user_je_source_name = l_user_je_source_name
2390: and reference6 in(select summary_line_id FROM psp_summary_lines -- Bug 7376898
2391: WHERE GROUP_ID = p_group_id
2392: AND payroll_control_id = p_payroll_control_id);
2393: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from gl_interface count: '||sql%rowcount);
2394:
2395: IF l_reversal_ac_failed = 'Y' THEN
2396: fnd_message.set_name('PSP','PSP_GL_REVERSE_AC_REJECT');
2397: fnd_message.set_token('GLCCID',x_lines_glccid);
2411: IN (SELECT summary_line_id
2412: FROM psp_summary_lines
2413: WHERE payroll_control_id = p_payroll_control_id);
2414:
2415: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 2 updated psp_distribution_lines count: '||sql%rowcount);
2416: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2417:
2418: ELSIF p_source_type = 'P' THEN
2419:
2412: FROM psp_summary_lines
2413: WHERE payroll_control_id = p_payroll_control_id);
2414:
2415: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 2 updated psp_distribution_lines count: '||sql%rowcount);
2416: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2417:
2418: ELSIF p_source_type = 'P' THEN
2419:
2420: UPDATE psp_pre_gen_dist_lines
2426: AND summary_line_id
2427: IN (SELECT summary_line_id
2428: FROM psp_summary_lines
2429: WHERE payroll_control_id = p_payroll_control_id);
2430: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 2 updated psp_pre_gen_dist_lines count: '||sql%rowcount);
2431: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2432:
2433: END IF;
2434:
2427: IN (SELECT summary_line_id
2428: FROM psp_summary_lines
2429: WHERE payroll_control_id = p_payroll_control_id);
2430: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 2 updated psp_pre_gen_dist_lines count: '||sql%rowcount);
2431: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2432:
2433: END IF;
2434:
2435: /* End of code for Bug 3065856 */
2437:
2438: /* Introduced the following check as part of Bug fix #1776606 */
2439: if p_mode = 'N' then
2440: /* introduced mark batch end Bug: 1929317 */
2441: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling mark_batch_end');
2442:
2443: mark_batch_end(p_source_type,
2444: g_source_code,
2445: g_time_period_id,
2453:
2454: IF l_suspense_ac_failed = 'Y' or
2455: nvl(l_auto_status,'X') in ('AUTO_POP_NA_ERROR', 'AUTO_POP_EXP_ERROR', 'AUTO_POP_NO_VALUE') then
2456: --- above check for autopop error 5080403
2457: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_suspense_ac_failed = Y ');
2458:
2459: if nvl(l_suspense_ac_failed,'N') = 'Y' then
2460: fnd_message.set_name('PSP','PSP_TR_GL_SUSP_AC_REJECT');
2461: fnd_message.set_token('ORG_NAME',x_susp_failed_org_name);
2480: IN (SELECT summary_line_id
2481: FROM psp_summary_lines
2482: WHERE payroll_control_id = p_payroll_control_id );
2483:
2484: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 3 updated psp_distribution_lines count: '||sql%rowcount);
2485: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2486:
2487: ELSIF p_source_type = 'P' THEN
2488: UPDATE psp_pre_gen_dist_lines
2481: FROM psp_summary_lines
2482: WHERE payroll_control_id = p_payroll_control_id );
2483:
2484: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 3 updated psp_distribution_lines count: '||sql%rowcount);
2485: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2486:
2487: ELSIF p_source_type = 'P' THEN
2488: UPDATE psp_pre_gen_dist_lines
2489: SET suspense_org_account_id = NULL,
2495: IN ( SELECT summary_line_id
2496: FROM psp_summary_lines
2497: WHERE payroll_control_id = p_payroll_control_id );
2498:
2499: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 3 updated psp_pre_gen_dist_lines count: '||sql%rowcount);
2500: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2501:
2502: END IF;
2503:
2496: FROM psp_summary_lines
2497: WHERE payroll_control_id = p_payroll_control_id );
2498:
2499: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 3 updated psp_pre_gen_dist_lines count: '||sql%rowcount);
2500: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_effective_date: '||l_effective_date);
2501:
2502: END IF;
2503:
2504: /* End of code for Bug 3065866 */
2505:
2506: /* Introduced the following check as part of Bug fix #1776606 */
2507: if p_mode = 'N' then
2508: /* introduced mark batch end Bug: 1929317 */
2509: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling mark_batch_end');
2510:
2511: mark_batch_end(p_source_type,
2512: g_source_code,
2513: g_time_period_id,
2519: end if;
2520: END IF;
2521:
2522: IF l_suspense_ac_not_found = 'Y' THEN
2523: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_suspense_ac_not_found = Y ');
2524:
2525: /* commented following code for 2479579
2526: fnd_message.set_name('PSP','PSP_LD_SUSPENSE_AC_NOT_EXIST');
2527: fnd_message.set_token('ORG_NAME',x_susp_nf_org_name);
2530:
2531: /* Introduced the following check as part of Bug fix #1776606 */
2532: if p_mode = 'N' then
2533: /* introduced mark batch end Bug: 1929317 */
2534: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'calling mark_batch_end');
2535:
2536: mark_batch_end(p_source_type,
2537: g_source_code,
2538: g_time_period_id,
2555:
2556:
2557: ELSIF l_cnt_gl_interface = 0 and l_no_run = 0 THEN
2558: --
2559: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Open gl_tie_back_success_cur');
2560:
2561: OPEN gl_tie_back_success_cur;
2562: LOOP
2563: FETCH gl_tie_back_success_cur INTO l_summary_line_id,
2570: UPDATE psp_summary_lines
2571: SET status_code = 'A'
2572: WHERE summary_line_id = l_summary_line_id;
2573:
2574: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_summary_lines set status_code = A count: '||sql%rowcount);
2575:
2576: IF l_dr_cr_flag = 'D' THEN
2577: l_dr_summary_amount := l_dr_summary_amount + l_summary_amount;
2578: ELSIF l_dr_cr_flag = 'C' THEN
2582: IF p_source_type = 'O' OR p_source_type = 'N' THEN
2583:
2584: UPDATE psp_distribution_lines
2585: SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
2586: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines set status_code = A count: '||sql%rowcount);
2587:
2588: -- move the transferred records to psp_distribution_lines_history
2589: INSERT INTO psp_distribution_lines_history
2590: (distribution_line_id,payroll_sub_line_id,distribution_date,
2618: FROM psp_distribution_lines
2619: WHERE status_code = 'A'
2620: AND summary_line_id = l_summary_line_id;
2621:
2622: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' inserted into psp_distribution_lines_history count: '||sql%rowcount);
2623:
2624: DELETE FROM psp_distribution_lines
2625: WHERE status_code = 'A'
2626: AND summary_line_id = l_summary_line_id;
2624: DELETE FROM psp_distribution_lines
2625: WHERE status_code = 'A'
2626: AND summary_line_id = l_summary_line_id;
2627:
2628: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from psp_distribution_lines count: '||sql%rowcount);
2629:
2630: ELSIF p_source_type = 'P' THEN
2631:
2632: UPDATE psp_pre_gen_dist_lines
2630: ELSIF p_source_type = 'P' THEN
2631:
2632: UPDATE psp_pre_gen_dist_lines
2633: SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
2634: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' 4 updated psp_pre_gen_distribution_lines set status_code = A count: '||sql%rowcount);
2635:
2636:
2637: -- move the transferred records to psp_pre_gen_dist_lines_history
2638: -- Introduced DFF columns for bug fix 2908859
2664: FROM psp_pre_gen_dist_lines
2665: WHERE status_code = 'A'
2666: AND summary_line_id = l_summary_line_id;
2667:
2668: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' inserted into psp_pre_gen_dist_lines_history count: '||sql%rowcount);
2669:
2670: DELETE FROM psp_pre_gen_dist_lines
2671: WHERE status_code = 'A'
2672: AND summary_line_id = l_summary_line_id;
2669:
2670: DELETE FROM psp_pre_gen_dist_lines
2671: WHERE status_code = 'A'
2672: AND summary_line_id = l_summary_line_id;
2673: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from psp_pre_gen_dist_lines count: '||sql%rowcount);
2674:
2675: END IF;
2676:
2677: END LOOP;
2680: SET gl_dr_amount = nvl(gl_dr_amount,0) + l_dr_summary_amount,
2681: gl_cr_amount = nvl(gl_cr_amount,0) + l_cr_summary_amount
2682: WHERE payroll_control_id = p_payroll_control_id;
2683:
2684: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_payroll_controls count: '||sql%rowcount);
2685:
2686:
2687: END IF;
2688: --
2717: fnd_msg_pub.add;
2718: p_return_status := fnd_api.g_ret_sts_unexp_error; --End of Changes for Bug 2056877.
2719:
2720: WHEN OTHERS THEN
2721: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2722:
2723: g_error_api_path := 'GL_TIE_BACK:'||g_error_api_path;
2724: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','GL_TIE_BACK');
2725: p_return_status := fnd_api.g_ret_sts_unexp_error;
2936: g_error_api_path := 'GL_BALANCE_TRANSACTION:'||g_error_api_path;
2937: p_return_status := fnd_api.g_ret_sts_unexp_error;
2938:
2939: WHEN OTHERS THEN
2940: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2941:
2942: g_error_api_path := 'GL_BALANCE_TRANSACTION:'||g_error_api_path;
2943: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','GL_BALANCE_TRANSACTION');
2944: p_return_status := fnd_api.g_ret_sts_unexp_error;
3111: --dbms_output.put_line('DDDDDDDDDDDDDDDDDGL Interface Inserted ....................');
3112: p_return_status := fnd_api.g_ret_sts_success;
3113: EXCEPTION
3114: WHEN OTHERS THEN
3115: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3116:
3117: --dbms_output.put_line('Error while inserting .........................');
3118: g_error_api_path := 'INSERT_INTO_GL_INTERFACE:'||g_error_api_path;
3119: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','INSERT_INTO_GL_INTERFACE');
3358: l_org_id Number(15);
3359:
3360: BEGIN
3361:
3362: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering create_gms_sum_lines');
3363:
3364: OPEN payroll_control_cur;
3365: LOOP
3366: FETCH payroll_control_cur INTO payroll_control_rec;
3384: fnd_msg_pub.add;
3385: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3386: END;
3387:
3388: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' opening gms_sum_lines_cursor');
3389:
3390:
3391: OPEN gms_sum_lines_cursor(payroll_control_rec.payroll_control_id);
3392: l_rec_count := 0;
3468: -- R12 MOAC Uptake
3469: l_org_id := psp_general.Get_transaction_org_id (l_project_id,l_expenditure_organization_id);
3470:
3471: -- insert into summary lines
3472: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling insert_into_summary_lines');
3473:
3474: insert_into_summary_lines(
3475: l_summary_line_id,
3476: l_person_id,
3642: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3643: g_error_api_path := 'CREATE_GMS_SUM_LINES:'||g_error_api_path;
3644: p_return_status := fnd_api.g_ret_sts_unexp_error;
3645: WHEN OTHERS THEN
3646: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3647:
3648: g_error_api_path := 'CREATE_GMS_SUM_LINES:'||g_error_api_path;
3649: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','CREATE_GMS_SUM_LINES');
3650: p_return_status := fnd_api.g_ret_sts_unexp_error;
3768:
3769: BEGIN
3770:
3771: -- get the source name
3772: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entered transfer_to_gms_interface');
3773:
3774: BEGIN
3775: SELECT transaction_source
3776: INTO l_transaction_source
3804: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3805: END;
3806: end if;
3807:
3808: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' open gms_batch_cursor');
3809:
3810: OPEN gms_batch_cursor;
3811: LOOP
3812: FETCH gms_batch_cursor INTO gms_batch_rec;
3869: AND status_code = 'N'
3870: AND gl_code_combination_id IS NULL
3871: AND org_id = org_id_tab(i); -- R12 MOAC uptake
3872:
3873: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_summary_lines set status_code for org_id'||org_id_tab(i)||' to N count: '||sql%rowcount);
3874:
3875: END LOOP;
3876:
3877:
3878: l_expenditure_comment := gms_batch_rec.source_type||':'||gms_batch_rec.payroll_source_code||':'||l_period_name||':'||gms_batch_rec.batch_name;
3879:
3880: -- l_interface_id := pa_txn_interface_s.nextval;
3881:
3882: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' open gms_interface_cursor');
3883:
3884: OPEN gms_interface_cursor(gms_batch_rec.payroll_control_id);
3885: l_rec_count := 0;
3886: LOOP
4025: -- Modified g_currency_code to gms_batch_rec.currency_code,Introduced Actual_exchange_rate_type
4026: -- actual rate_date for Bug 2916848
4027:
4028: IF (gms_batch_rec.currency_code <> 'STAT') THEN
4029: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling insert_into_pa_interface ');
4030:
4031: insert_into_pa_interface(l_txn_interface_id,
4032: l_txn_source,
4033: L_GMS_BATCH_NAME,
4178: -- set the context to single to submit_request
4179: mo_global.set_policy_context('S', org_id_tab(I) );
4180: fnd_request.set_org_id (org_id_tab(I) );
4181:
4182: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' submitting request for Transaction_Import for org_id: '||org_id_tab(I));
4183:
4184: req_id_tab(i) := fnd_request.submit_request(
4185: 'PA',
4186: 'PAXTRTRX',
4203: set phase = 'Submitted_TI_Request'
4204: WHERE payroll_control_id = gms_batch_rec.payroll_control_id;
4205:
4206: commit;
4207: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Committed after updating psp_payroll_controls SET phase = Submitted_TI_Request count: '||sql%rowcount);
4208:
4209:
4210: IF g_skip_flag_gms = 'N' THEN -- bug 6902514 -- change
4211:
4249: hr_utility.trace(' tab_count ='||i);
4250: l_gms_batch_name := gms_batch_name_tab(I);
4251: hr_utility.trace(' tab_count 2nd time='||i);
4252:
4253: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gl_tie_back for org_id: '||org_id_tab(I));
4254:
4255: gms_tie_back(gms_batch_rec.payroll_control_id,
4256: gms_batch_rec.source_type,
4257: l_period_end_date,
4288: WHEN RETURN_BACK THEN
4289: p_return_status := fnd_api.g_ret_sts_success;
4290:
4291: WHEN OTHERS THEN
4292: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
4293:
4294: g_error_api_path := 'TRANSFER_TO_GMS_INTERFACE:'||g_error_api_path;
4295: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','TRANSFER_TO_GMS_INTERFACE');
4296: p_return_status := fnd_api.g_ret_sts_unexp_error;
4547: get_completion_rec get_completion%ROWTYPE;
4548:
4549: begin
4550:
4551: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering process_complete');
4552:
4553: open get_completion;
4554: loop
4555: fetch get_completion into get_completion_rec;
4561: close get_completion;
4562: return FALSE;
4563: end if;
4564:
4565: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' get_completion_rec.transaction_status_code = P');
4566: if (get_completion_rec.transaction_status_code = 'P' AND -- change
4567: g_skip_flag_gms = 'N') then -- bug 6902514
4568:
4569: -- -------------------------------------------------------------------------------------------
4575: delete from pa_transaction_interface_all
4576: where batch_name = p_gms_batch_name
4577: and transaction_source = p_txn_source;
4578:
4579: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from pa_transaction_interface_all count:'||sql%rowcount);
4580:
4581:
4582: if p_txn_source = 'GOLD' then
4583: delete from gms_transaction_interface_all
4583: delete from gms_transaction_interface_all
4584: where batch_name = p_gms_batch_name
4585: and transaction_source = 'GOLD';
4586:
4587: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from gms_transaction_interface_all count:'||sql%rowcount);
4588:
4589: end if;
4590:
4591: delete from psp_summary_lines
4590:
4591: delete from psp_summary_lines
4592: where gms_batch_name = to_number(p_gms_batch_name)
4593: and payroll_control_id = p_payroll_control_id;
4594: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from psp_summary_lines count:'||sql%rowcount);
4595:
4596:
4597:
4598: elsif (get_completion_rec.transaction_status_code = 'I' AND -- change
4613: end loop;
4614:
4615: exception
4616: when others then
4617: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
4618:
4619: return FALSE;
4620: end PROCESS_COMPLETE;
4621:
4621:
4622: BEGIN
4623:
4624: hr_utility.trace('entered gms_tie_back');
4625: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' entered gms_tie_back');
4626:
4627:
4628: if NOT PROCESS_COMPLETE then
4629: if (l_no_run_status = 'I') then
4634: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4635: end if;
4636: end if;
4637: hr_utility.trace('entered gms_tie_back2');
4638: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' entered gms_tie_back2');
4639:
4640: -- Status codes have been added to transaction import process,
4641: -- to accomodate pre and post import extensions in 11i
4642: -- to following select statement
4647: WHERE batch_name = p_gms_batch_name
4648: AND transaction_source = p_txn_source
4649: AND transaction_status_code in ('R', 'PO', 'PI', 'PR');
4650:
4651: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' selected pa_transaction_interface_all with status_code in R,PO,PI,PR l_cnt_gms_interface :'||l_cnt_gms_interface);
4652:
4653: /* moved this statement to beginning for 2444657 */
4654: UPDATE psp_payroll_controls
4655: SET phase = 'GL_Tie_Back'
4654: UPDATE psp_payroll_controls
4655: SET phase = 'GL_Tie_Back'
4656: WHERE payroll_control_id = p_payroll_control_id;
4657: hr_utility.trace('entered gms_tie_back3');
4658: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' update psp_payroll_controls SQL%ROWCOUNT :'||SQL%ROWCOUNT);
4659: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' entered gms_tie_back3');
4660:
4661:
4662: IF l_cnt_gms_interface > 0 THEN
4655: SET phase = 'GL_Tie_Back'
4656: WHERE payroll_control_id = p_payroll_control_id;
4657: hr_utility.trace('entered gms_tie_back3');
4658: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' update psp_payroll_controls SQL%ROWCOUNT :'||SQL%ROWCOUNT);
4659: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' entered gms_tie_back3');
4660:
4661:
4662: IF l_cnt_gms_interface > 0 THEN
4663: --
4660:
4661:
4662: IF l_cnt_gms_interface > 0 THEN
4663: --
4664: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Opening gms_tie_back_reject_cur ');
4665:
4666: OPEN gms_tie_back_reject_cur;
4667: LOOP
4668: FETCH gms_tie_back_reject_cur INTO l_trx_reject_code,l_orig_trx_reference,l_trx_status_code,
4675: CLOSE gms_tie_back_reject_cur;
4676: EXIT;
4677: END IF;
4678:
4679: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_trx_reject_code,l_orig_trx_reference(Summary line id),l_trx_status_code, l_expenditure_ending_date,l_expenditure_id,
4680: l_interface_id,l_expenditure_item_id, l_txn_interface_id'||l_trx_reject_code||','||l_orig_trx_reference||','||l_trx_status_code||','||
4681: l_expenditure_ending_date||','||l_expenditure_id||','||l_interface_id||','||l_expenditure_item_id||','|| l_txn_interface_id);
4682: -- update summary_lines with the reject status code
4683: IF l_trx_status_code in ('R', 'PO', 'PI', 'PR') THEN
4680: l_interface_id,l_expenditure_item_id, l_txn_interface_id'||l_trx_reject_code||','||l_orig_trx_reference||','||l_trx_status_code||','||
4681: l_expenditure_ending_date||','||l_expenditure_id||','||l_interface_id||','||l_expenditure_item_id||','|| l_txn_interface_id);
4682: -- update summary_lines with the reject status code
4683: IF l_trx_status_code in ('R', 'PO', 'PI', 'PR') THEN
4684: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' status_code in R, PO,PI,PR');
4685:
4686: UPDATE psp_summary_lines
4687: SET interface_status = l_trx_reject_code, status_code = 'R',
4688: expenditure_ending_date = l_expenditure_ending_date, -- added 5 fields for 2445196
4688: expenditure_ending_date = l_expenditure_ending_date, -- added 5 fields for 2445196
4689: expenditure_id = l_expenditure_id, interface_id=l_interface_id,
4690: expenditure_item_id=l_expenditure_item_id, txn_interface_id=l_txn_interface_id
4691: WHERE summary_line_id = to_number(l_orig_trx_reference);
4692: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_summary_lines for rejected records count: '||sql%rowcount);
4693:
4694: ELSIF l_trx_status_code = 'A' THEN
4695: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' status_code A');
4696:
4691: WHERE summary_line_id = to_number(l_orig_trx_reference);
4692: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_summary_lines for rejected records count: '||sql%rowcount);
4693:
4694: ELSIF l_trx_status_code = 'A' THEN
4695: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' status_code A');
4696:
4697: UPDATE psp_summary_lines
4698: SET interface_status = l_trx_reject_code, status_code = 'A',
4699: expenditure_ending_date = l_expenditure_ending_date, -- added 5 fields for 2445196
4700: expenditure_id = l_expenditure_id, interface_id=l_interface_id,
4701: expenditure_item_id=l_expenditure_item_id, txn_interface_id=l_txn_interface_id
4702: WHERE summary_line_id = to_number(l_orig_trx_reference);
4703:
4704: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_summary_lines for accepted records count: '||sql%rowcount);
4705:
4706: SELECT summary_amount,dr_cr_flag
4707: INTO l_summary_amount,l_dr_cr_flag
4708: FROM psp_summary_lines
4707: INTO l_summary_amount,l_dr_cr_flag
4708: FROM psp_summary_lines
4709: WHERE summary_line_id = to_number(l_orig_trx_reference) ;
4710: hr_utility.trace('entered gms_tie_back4');
4711: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' entered gms_tie_back4');
4712:
4713:
4714: IF l_dr_cr_flag = 'D' THEN
4715: l_dr_summary_amount := l_dr_summary_amount + l_summary_amount;
4718: l_cr_summary_amount := l_cr_summary_amount - l_summary_amount;
4719: END IF;
4720: END IF;
4721: hr_utility.trace('before open assign_susp_ac_cur');
4722: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' before open assign_susp_ac_cur');
4723:
4724: OPEN assign_susp_ac_cur(l_orig_trx_reference);
4725: LOOP
4726:
4730: CLOSE assign_susp_ac_cur;
4731: EXIT;
4732: END IF;
4733:
4734: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' From (Pre Gen) Distribution lines Summary_line_id :'||l_orig_trx_reference||
4735: 'row id :'||l_rowid||'distribution line id :'||l_distribution_date||'suspence_org_acc_id: '||l_suspense_org_account_id);
4736:
4737:
4738: -- Bug 9307730
4745: and payroll_line_id = (select payroll_line_id from psp_payroll_sub_lines
4746: where payroll_sub_line_id = (select payroll_sub_line_id
4747: from psp_distribution_lines
4748: where distribution_line_id = x_line_id));
4749: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' from psp_payroll_lines: l_pay_action_type, l_effective_date'||l_pay_action_type||l_effective_date);
4750:
4751: /*Bug 7376898 End*/
4752: END IF;
4753:
4751: /*Bug 7376898 End*/
4752: END IF;
4753:
4754: hr_utility.trace('entered gms_tie_back5');
4755: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' entered gms_tie_back5');
4756:
4757: IF l_trx_status_code = 'A' THEN
4758: IF p_source_type = 'O' OR p_source_type = 'N' THEN
4759: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Payroll type O or N');
4755: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' entered gms_tie_back5');
4756:
4757: IF l_trx_status_code = 'A' THEN
4758: IF p_source_type = 'O' OR p_source_type = 'N' THEN
4759: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Payroll type O or N');
4760:
4761: UPDATE psp_distribution_lines
4762: SET status_code = 'A'
4763: WHERE rowid = l_rowid;
4761: UPDATE psp_distribution_lines
4762: SET status_code = 'A'
4763: WHERE rowid = l_rowid;
4764:
4765: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_distribution_lines set status_code A count: '||sql%rowcount);
4766: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code in psp_distribution_lines to A for rowid :'||l_rowid);
4767:
4768: INSERT INTO psp_distribution_lines_history
4769: (distribution_line_id,payroll_sub_line_id,distribution_date,
4762: SET status_code = 'A'
4763: WHERE rowid = l_rowid;
4764:
4765: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_distribution_lines set status_code A count: '||sql%rowcount);
4766: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code in psp_distribution_lines to A for rowid :'||l_rowid);
4767:
4768: INSERT INTO psp_distribution_lines_history
4769: (distribution_line_id,payroll_sub_line_id,distribution_date,
4770: effective_date,distribution_amount,status_code,suspense_reason_code,
4797: FROM psp_distribution_lines
4798: WHERE status_code = 'A'
4799: AND summary_line_id = to_number(l_orig_trx_reference);
4800:
4801: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
4802: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
4803: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' after insert into dist lines');
4804:
4805: hr_utility.trace('after insert into dist lines');
4799: AND summary_line_id = to_number(l_orig_trx_reference);
4800:
4801: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
4802: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
4803: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' after insert into dist lines');
4804:
4805: hr_utility.trace('after insert into dist lines');
4806: DELETE FROM psp_distribution_lines
4807: WHERE status_code = 'A'
4806: DELETE FROM psp_distribution_lines
4807: WHERE status_code = 'A'
4808: AND summary_line_id = to_number(l_orig_trx_reference);
4809:
4810: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleting from psp_distribution_lines for summary_line_id: '||l_orig_trx_reference
4811: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
4812:
4813: --moved the two del stmnts above for 2445196
4814:
4816: UPDATE psp_pre_gen_dist_lines
4817: SET status_code = 'A'
4818: WHERE rowid = l_rowid;
4819:
4820: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Payroll type P');
4821:
4822: -- move the transferred records to psp_pre_gen_dist_lines_history
4823: INSERT INTO psp_pre_gen_dist_lines_history
4824: (pre_gen_dist_line_id,distribution_interface_id,person_id,assignment_id,
4848: FROM psp_pre_gen_dist_lines
4849: WHERE status_code = 'A'
4850: AND summary_line_id = to_number(l_orig_trx_reference);
4851:
4852: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_pre_gen_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
4853: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
4854: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' after insert into pregen lines');
4855:
4856: hr_utility.trace('after insert into pregen lines');
4850: AND summary_line_id = to_number(l_orig_trx_reference);
4851:
4852: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_pre_gen_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
4853: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
4854: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' after insert into pregen lines');
4855:
4856: hr_utility.trace('after insert into pregen lines');
4857: DELETE FROM psp_pre_gen_dist_lines
4858: WHERE status_code = 'A'
4856: hr_utility.trace('after insert into pregen lines');
4857: DELETE FROM psp_pre_gen_dist_lines
4858: WHERE status_code = 'A'
4859: AND summary_line_id = to_number(l_orig_trx_reference);
4860: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleting from psp_pre_gen_distribution_lines for summary_line_id: '||l_orig_trx_reference
4861: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
4862:
4863: END IF;
4864: --- moved the two del stmnts below for 2445196, earlier commented for 2290051
4866: /* Bug 1866362, S and T Failing with Fatal error Suspense A/C invalid, eventhough Suspense A/C is valid.
4867: Introduced reject_code not null condition, stick suspense only if this condition satisified */
4868: -- if a suspense a/c failed,update the status of the whole batch and display the error
4869: ELSIF l_trx_status_code <> 'A' and l_trx_reject_code is not NULL then
4870: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_trx_status_code <> A and l_trx_reject_code not null');
4871: -- introduced following IF by splitting from above ELSIF stmt: 2428953.
4872: If l_suspense_org_account_id is NOT NULL then
4873: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' suspence_account_id not null');
4874:
4869: ELSIF l_trx_status_code <> 'A' and l_trx_reject_code is not NULL then
4870: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_trx_status_code <> A and l_trx_reject_code not null');
4871: -- introduced following IF by splitting from above ELSIF stmt: 2428953.
4872: If l_suspense_org_account_id is NOT NULL then
4873: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' suspence_account_id not null');
4874:
4875: OPEN get_susp_org_cur1(l_suspense_org_account_id);
4876: FETCH get_susp_org_cur1 into l_organization_id, l_organization_name;
4877: CLOSE get_susp_org_cur1;
4911: IF l_pay_action_type = 'L' THEN
4912: l_distribution_date := l_effective_date;
4913: END IF;
4914: --Bug 7040943 End
4915: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Opening org_susp_ac_cur');
4916:
4917: OPEN org_susp_ac_cur(l_orig_org_id1, l_distribution_date);
4918: FETCH org_susp_ac_cur INTO l_organization_account_id,l_gl_code_combination_id,l_project_id,l_award_id,l_task_id,
4919: l_exp_org_id, l_expenditure_type;
4918: FETCH org_susp_ac_cur INTO l_organization_account_id,l_gl_code_combination_id,l_project_id,l_award_id,l_task_id,
4919: l_exp_org_id, l_expenditure_type;
4920:
4921: IF org_susp_ac_cur%NOTFOUND THEN
4922: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' org_susp_ac_cur not found for l_orig_org_id1, l_distribution_date'||l_orig_org_id1||','|| l_distribution_date);
4923:
4924: /* Following code is added for bug 2056877 ,Added validation for generic suspense account */
4925: l_return_value := psp_general.find_global_suspense(l_distribution_date,
4926: p_business_group_id,
4940: FETCH global_susp_ac_cur INTO l_organization_account_id,l_gl_code_combination_id,l_project_id,l_award_id,
4941: l_task_id, l_exp_org_id, l_expenditure_type;
4942:
4943: IF global_susp_ac_cur%NOTFOUND THEN
4944: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Global suspence account not found');
4945:
4946: /* l_susp_ac_found := 'FALSE';
4947: l_suspense_ac_not_found := 'Y';
4948: x_susp_nf_org_name := l_organization_name;
4970: END IF;
4971: CLOSE org_susp_ac_cur;
4972: -- introduced for 5080403
4973: if g_suspense_autopop = 'Y' and l_organization_account_id is not null then
4974: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' suspence autopop set and org_acc_id not null');
4975:
4976: if l_gl_code_combination_id is null then
4977: l_acct_type:='E';
4978: else
5009: l_auto_status := 'AUTO_POP_NO_VALUE';
5010: end if;
5011: open get_asg_details;
5012: fetch get_asg_details into l_person_name, l_assignment_number, l_element_type, l_auto_org_name;
5013: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_person_name, l_assignment_number, l_element_type, l_auto_org_name'||l_person_name||','|| l_assignment_number||','|| l_element_type||','|| l_auto_org_name);
5014:
5015: close get_asg_details;
5016: psp_enc_crt_xml.p_set_of_books_id := p_set_of_books_id;
5017: psp_enc_crt_xml.p_business_group_id := p_business_group_id;
5049: end if;
5050: end if;
5051:
5052: if l_susp_ac_found = 'TRUE' then
5053: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Suspence account found');
5054:
5055: IF l_gl_code_combination_id IS NOT NULL THEN
5056: l_gl_project_flag := 'G';
5057: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_period_end_date: '||p_period_end_date);
5053: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Suspence account found');
5054:
5055: IF l_gl_code_combination_id IS NOT NULL THEN
5056: l_gl_project_flag := 'G';
5057: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_period_end_date: '||p_period_end_date);
5058:
5059: -- l_effective_date := p_period_end_date; --- uncommented for Bug 2663344 COMMENTED by Bug 7040943
5060: ELSE
5061: l_gl_project_flag := 'P';
5058:
5059: -- l_effective_date := p_period_end_date; --- uncommented for Bug 2663344 COMMENTED by Bug 7040943
5060: ELSE
5061: l_gl_project_flag := 'P';
5062: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_distribution_date: '||l_distribution_date);
5063:
5064: l_effective_date := l_distribution_date; --- added for Bug 2663344
5065: END IF;
5066:
5073: SET pre_distribution_run_flag = gl_project_flag
5074: WHERE rowid = l_rowid;
5075:
5076: /* End of changes for Bug 3065866*/
5077: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_distribution_lines for rowid: '||l_rowid||' with l_effective_date'||l_effective_date);
5078: UPDATE psp_distribution_lines
5079: SET suspense_org_account_id = l_organization_account_id,
5080: suspense_reason_code = 'ST:' || l_trx_reject_code,
5081: gl_project_flag = l_gl_project_flag,
5083: -- effective_date = l_effective_date, ---uncommented this line for Bug 2663344 -- Commented for Bug 10181561
5084: suspense_auto_glccid = l_new_glccid, --- added suspense_auto for 5080403
5085: suspense_auto_exp_type = l_new_expenditure_type
5086: WHERE rowid = l_rowid;
5087: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines set pre_distribution_run_flag count: '||sql%rowcount);
5088:
5089: ELSIF p_source_type = 'P' THEN
5090: UPDATE psp_pre_gen_dist_lines
5091: SET suspense_org_account_id = l_organization_account_id,
5094: -- effective_date = l_effective_date, ---uncommented this line for Bug 2663344 -- Commented for Bug 10181561
5095: suspense_auto_glccid = l_new_glccid,
5096: suspense_auto_exp_type = l_new_expenditure_type
5097: WHERE rowid = l_rowid;
5098: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_pre_gen_dist_lines for rowid: '||l_rowid||' with l_effective_date'||l_effective_date||' count: '||sql%rowcount);
5099:
5100: END IF;
5101: else -- added for 2479579
5102: l_susp_exception := l_susp_ac_found;
5106:
5107: END LOOP;
5108: END LOOP;
5109: hr_utility.trace('update control record');
5110: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' update control record');
5111:
5112: UPDATE psp_payroll_controls
5113: SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
5114: ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
5112: UPDATE psp_payroll_controls
5113: SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
5114: ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
5115: WHERE payroll_control_id = p_payroll_control_id;
5116: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_payroll_controls count: '||sql%rowcount);
5117:
5118:
5119: -- Introduced for bug fix 2643228
5120: DELETE pa_transaction_interface_all
5119: -- Introduced for bug fix 2643228
5120: DELETE pa_transaction_interface_all
5121: WHERE transaction_source = p_txn_source
5122: AND batch_name = p_gms_batch_name;
5123: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from pa_transaction_interface_all count: '||sql%rowcount);
5124:
5125:
5126: DELETE gms_transaction_interface_all
5127: WHERE transaction_source = p_txn_source
5125:
5126: DELETE gms_transaction_interface_all
5127: WHERE transaction_source = p_txn_source
5128: AND batch_name = p_gms_batch_name;
5129: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' deleted from gms_transaction_interface_all count: '||sql%rowcount);
5130:
5131: -- End of bug fix 2643228
5132: IF l_suspense_ac_failed = 'Y' or
5133: nvl(l_auto_status,'X') in ('AUTO_POP_NA_ERROR', 'AUTO_POP_EXP_ERROR', 'AUTO_POP_NO_VALUE') then
5143: /* Added this code for Bug 3065866 */
5144: IF p_source_type = 'O' OR p_source_type = 'N' THEN
5145:
5146:
5147: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updating psp_distribution_lines for p_payroll_control_id: '||p_payroll_control_id);
5148:
5149: UPDATE psp_distribution_lines
5150: SET suspense_org_account_id = NULL,
5151: suspense_reason_code = NULL,
5156: AND summary_line_id
5157: IN ( SELECT summary_line_id
5158: FROM psp_summary_lines
5159: WHERE payroll_control_id = p_payroll_control_id);
5160: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines count: '||sql%rowcount);
5161:
5162: ELSIF p_source_type = 'P' THEN
5163: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updating psp_pre_gen_distribution_lines for p_payroll_control_id: '||p_payroll_control_id);
5164:
5159: WHERE payroll_control_id = p_payroll_control_id);
5160: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines count: '||sql%rowcount);
5161:
5162: ELSIF p_source_type = 'P' THEN
5163: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updating psp_pre_gen_distribution_lines for p_payroll_control_id: '||p_payroll_control_id);
5164:
5165: UPDATE psp_pre_gen_dist_lines
5166: SET suspense_org_account_id = NULL,
5167: suspense_reason_code = NULL,
5171: AND summary_line_id
5172: IN (SELECT summary_line_id
5173: FROM psp_summary_lines
5174: WHERE payroll_control_id = p_payroll_control_id);
5175: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_pre_gen_dist_lines count: '||sql%rowcount);
5176:
5177:
5178: END IF;
5179: /* End of code for Bug 3065866 */
5180:
5181: /* Introduced the followinbg check as part of Bug fix #1776606 */
5182: if p_mode = 'N' then
5183: /* introduced mark batch end Bug: 1929317 */
5184: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling mark_batch_end ');
5185:
5186: mark_batch_end(p_source_type,
5187: g_source_code,
5188: g_time_period_id,
5194: end if;
5195: END IF;
5196:
5197: IF l_suspense_ac_not_found = 'Y' THEN
5198: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Suspence account not found');
5199:
5200: /* commented for 2479579
5201: fnd_message.set_name('PSP','PSP_LD_SUSPENSE_AC_NOT_EXIST');
5202: fnd_message.set_token('ORG_NAME',x_susp_nf_org_name);
5205:
5206: /* Added the following code for Bug 3065866 */
5207:
5208: IF p_source_type = 'O' OR p_source_type = 'N' THEN
5209: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_source_type O or N');
5210:
5211: UPDATE psp_distribution_lines
5212: SET suspense_org_account_id = NULL,
5213: suspense_reason_code = NULL
5215: AND summary_line_id
5216: IN( SELECT summary_line_id
5217: FROM psp_summary_lines
5218: WHERE payroll_control_id = p_payroll_control_id);
5219: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updating psp_distribution_lines for p_payroll_control_id: '||p_payroll_control_id);
5220:
5221: ELSIF p_source_type = 'P' THEN
5222:
5223: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_source_type P');
5219: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updating psp_distribution_lines for p_payroll_control_id: '||p_payroll_control_id);
5220:
5221: ELSIF p_source_type = 'P' THEN
5222:
5223: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_source_type P');
5224:
5225: UPDATE psp_pre_gen_dist_lines
5226: SET suspense_org_account_id = NULL,
5227: suspense_reason_code = NULL
5236:
5237: /* Introduced the followinbg check as part of Bug fix #1776606 */
5238: if p_mode = 'N' then
5239: /* introduced mark batch end Bug: 1929317 */
5240: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling mark_batch_end');
5241:
5242: mark_batch_end(p_source_type,
5243: g_source_code,
5244: g_time_period_id,
5261:
5262: ELSIF l_cnt_gms_interface = 0 THEN
5263:
5264: -- Bug 4369939: Performance fix START
5265: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Opening summary_lines_csr');
5266:
5267: OPEN SUMMARY_LINES_CSR;
5268: FETCH SUMMARY_LINES_CSR bulk Collect into SUMMARY_LINES_REC.L_SUMMARY_LINE_ID,
5269: SUMMARY_LINES_rec.L_SUMMARY_LINE_ID_CHAR, SUMMARY_LINES_REC.L_GMS_BATCH_NAME;
5283: )
5284: WHERE --GMS_BATCH_NAME = SUMMARY_LINES_REC.L_GMS_BATCH_NAME(i) AND
5285: PSL.SUMMARY_LINE_ID = SUMMARY_LINES_REC.L_SUMMARY_LINE_ID(i);
5286:
5287: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_summary_lines table SQL%ROWCOUNT: '||SQL%ROWCOUNT);
5288: END LOOP;
5289:
5290: /*
5291: -- changed to update all sum lines at one shot for 2445196
5301: WHERE gms_batch_name = p_gms_batch_name;
5302: */
5303: -- Bug 4369939: Performance fix END
5304:
5305: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Opening gms_tie_back_success_cur ');
5306:
5307: OPEN gms_tie_back_success_cur;
5308: LOOP
5309: FETCH gms_tie_back_success_cur INTO l_summary_line_id,
5312: IF gms_tie_back_success_cur%NOTFOUND THEN
5313: CLOSE gms_tie_back_success_cur;
5314: EXIT;
5315: END IF;
5316: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Summary line id: '||l_summary_line_id);
5317: -- update records in psp_summary_lines as 'A' moved this stmnt from here to above for 2445196.
5318:
5319: IF l_dr_cr_flag = 'D' THEN
5320: l_dr_summary_amount := l_dr_summary_amount + l_summary_amount;
5324: END IF;
5325:
5326: IF p_source_type = 'O' OR p_source_type = 'N' THEN
5327:
5328: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_distribution_lines for summary_line_id'||l_summary_line_id);
5329:
5330: UPDATE psp_distribution_lines
5331: SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
5332: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines set status_code to A count: '||sql%rowcount);
5328: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_distribution_lines for summary_line_id'||l_summary_line_id);
5329:
5330: UPDATE psp_distribution_lines
5331: SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
5332: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_distribution_lines set status_code to A count: '||sql%rowcount);
5333:
5334:
5335: -- move the transferred records to psp_distribution_lines_history
5336: INSERT INTO psp_distribution_lines_history
5365: FROM psp_distribution_lines
5366: WHERE status_code = 'A'
5367: AND summary_line_id = l_summary_line_id;
5368:
5369: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
5370: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
5371:
5372: DELETE FROM psp_distribution_lines
5373: WHERE status_code = 'A'
5371:
5372: DELETE FROM psp_distribution_lines
5373: WHERE status_code = 'A'
5374: AND summary_line_id = l_summary_line_id;
5375: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleting from psp_distribution_lines for summary_line_id: '||l_orig_trx_reference
5376: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
5377:
5378: -- Moved the purging of xface lines from here below LOOP, for Bug 2445196
5379: ELSIF p_source_type = 'P' THEN
5377:
5378: -- Moved the purging of xface lines from here below LOOP, for Bug 2445196
5379: ELSIF p_source_type = 'P' THEN
5380:
5381: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Payroll type P');
5382:
5383: UPDATE psp_pre_gen_dist_lines
5384: SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
5385:
5382:
5383: UPDATE psp_pre_gen_dist_lines
5384: SET status_code = 'A' WHERE summary_line_id = l_summary_line_id;
5385:
5386: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_pre_gen_distribution_lines for summary_line_id'||l_summary_line_id);
5387:
5388: -- move the transferred records to psp_pre_gen_dist_lines_history
5389: INSERT INTO psp_pre_gen_dist_lines_history
5390: (pre_gen_dist_line_id,distribution_interface_id,person_id,assignment_id,
5414: FROM psp_pre_gen_dist_lines
5415: WHERE status_code = 'A'
5416: AND summary_line_id = l_summary_line_id;
5417:
5418: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into psp_pre_gen_distribution_lines_history for summary_line_id: '||l_orig_trx_reference
5419: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
5420:
5421: DELETE FROM psp_pre_gen_dist_lines
5422: WHERE status_code = 'A'
5420:
5421: DELETE FROM psp_pre_gen_dist_lines
5422: WHERE status_code = 'A'
5423: AND summary_line_id = l_summary_line_id;
5424: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleting from psp_distribution_lines for summary_line_id: '||l_orig_trx_reference
5425: ||'number of rows SQL%ROWCOUNT: '||SQL%ROWCOUNT);
5426:
5427: --Moved delete xface stmnts from here to below for bug 2445196:
5428: END IF;
5431: UPDATE psp_payroll_controls
5432: SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
5433: ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
5434: WHERE payroll_control_id = p_payroll_control_id;
5435: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_payroll_controls count: '||sql%rowcount);
5436:
5437:
5438: -- Introduced for bug fix 2643228
5439: DELETE pa_transaction_interface_all
5438: -- Introduced for bug fix 2643228
5439: DELETE pa_transaction_interface_all
5440: WHERE transaction_source = p_txn_source
5441: AND batch_name = p_gms_batch_name;
5442: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' delete from pa_transaction_interface_all for batch_name: '||p_gms_batch_name);
5443:
5444:
5445: DELETE gms_transaction_interface_all
5446: WHERE transaction_source = p_txn_source
5445: DELETE gms_transaction_interface_all
5446: WHERE transaction_source = p_txn_source
5447: AND batch_name = p_gms_batch_name;
5448:
5449: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' delete from gms_transaction_interface_all for batch_name: '||p_gms_batch_name);
5450:
5451: -- End of bug fix 2643228
5452: END IF;
5453: /***** Commented for bug fix 2643228
5489: fnd_msg_pub.add;
5490: p_return_status := fnd_api.g_ret_sts_unexp_error; --End of Modification for Bug 2056877.
5491:
5492: WHEN OTHERS THEN
5493: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
5494: g_error_api_path := 'GMS_TIE_BACK:'||g_error_api_path;
5495: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','GMS_TIE_BACK');
5496: p_return_status := fnd_api.g_ret_sts_unexp_error;
5497: END;
5618: p_return_status := fnd_api.g_ret_sts_success;
5619: EXCEPTION
5620:
5621: WHEN OTHERS THEN
5622: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
5623: -- dbms_output.put_line('Error while inserting .........................');
5624: g_error_api_path := 'INSERT_INTO_PA_INTERFACE:'||g_error_api_path;
5625: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','INSERT_INTO_PA_INTERFACE');
5626: p_return_status := fnd_api.g_ret_sts_unexp_error;
6276: end loop;
6277:
6278: EXCEPTION
6279: when others then
6280: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6281: hr_utility.trace( 'SandT_Supercede-->send notifications --> GET_RECEIVER_TYPES when others '||sqlerrm);
6282: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','GET_RECEIVER_TYPES');
6283: raise;
6284: END GET_RECEIVER_TYPES;
6382: wf_engine.StartProcess(itemtype => 'PSPERAVL',
6383: itemkey => l_wf_itemkey);
6384: EXCEPTION
6385: when others then
6386: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6387:
6388: hr_utility.trace( 'SandT_Supercede-->send notifications --> call_wf when others '||sqlerrm);
6389: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','CALL_WF');
6390: raise;
6503: end loop;
6504: end if;
6505: EXCEPTION
6506: when others then
6507: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6508:
6509: hr_utility.trace( 'SandT_Supercede-->send notifications when others '||sqlerrm);
6510: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','SEND_NOTIFICATIONS');
6511: raise;
6547:
6548: open check_emp_overlap;
6549: fetch check_emp_overlap into l_emp_overlap_count;
6550: close check_emp_overlap;
6551: ---fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' before emp count');
6552: if l_emp_overlap_count > 0 then
6553:
6554: open check_emp_match_option;
6555: fetch check_emp_match_option into l_count_emp_match;
6553:
6554: open check_emp_match_option;
6555: fetch check_emp_match_option into l_count_emp_match;
6556: close check_emp_match_option;
6557: ---fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_count_emp_match = '||l_count_emp_match);
6558: hr_utility.trace( 'SandT_Supercede--> Main Loop Entered');
6559: hr_utility.trace( 'SandT_Supercede--> reqid string, p_source_type, template_id ='||
6560: l_supercede_reqid_str||':'||p_source_type||':'||t_template_id(k));
6561: if l_count_emp_match > 0 then
6588: elsif p_source_type in ('N','O') then
6589: open payroll_asg_er_supercede;
6590: fetch payroll_asg_er_supercede bulk collect into t_efforts_table, t_person_table, t_sum_line_id;
6591: close payroll_asg_er_Supercede;
6592: ---fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' t_efforts_table '||t_efforts_table.count);
6593: else
6594: open all_asg_er_supercede;
6595: fetch all_asg_er_supercede bulk collect into t_efforts_table, t_person_table, t_sum_line_id;
6596: close all_asg_er_Supercede;
6596: close all_asg_er_Supercede;
6597: end if; --- source type adjustments
6598: end if;
6599: hr_utility.trace( 'SandT_Supercede--> loaded the superceded persons, count ='|| t_efforts_table.count);
6600: --fnd_file.put_line(fnd_file.log, 'SandT_Supercede--> loaded the superceded persons, count ='|| t_efforts_table.count);
6601: open supercede_tolerance(t_template_id(k));
6602: fetch supercede_tolerance into l_tolerance_amt, l_tolerance_percent;
6603: close supercede_tolerance;
6604: hr_utility.trace( 'SandT_Supercede--> get_tolerance limits AMT, %'||
6875: t_tolerance_sline.delete;
6876: hr_utility.trace( 'SandT_Supercede--> EXITING');
6877: EXCEPTION
6878: when others then
6879: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6880: hr_utility.trace( 'SandT_Supercede--> when others in supercede_er'||sqlerrm) ;
6881: fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS', 'SUPERCEDE_ER');
6882: p_return_status := fnd_api.g_ret_sts_unexp_error;
6883: END supercede_er;