[Home] [Help]
48: l_return_status VARCHAR2(10);
49: l_msg_count NUMBER;
50: l_msg_data VARCHAR2(2000);
51: BEGIN
52: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering ENC_LIQ_TRANS
53: p_payroll_id: ' || p_payroll_id || '
54: p_action_type: ' || p_action_type || '
55: p_business_group_id: ' || p_business_group_id || '
56: p_set_of_books_id: ' || p_set_of_books_id);
56: p_set_of_books_id: ' || p_set_of_books_id);
57: g_error_api_path := '';
58: fnd_msg_pub.initialize;
59: psp_general.TRANSACTION_CHANGE_PURGEBLE; -- 2431917
60: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' PA Transaction Purgeble Flag set');
61: g_bg_id := p_business_group_id;
62: g_sob_id := p_set_of_books_id;
63: / *Introduced g_payroll_id global initializations for Restart Update/Quick Update Encumbrance Lines Enh.* /
64: g_payroll_id:=p_payroll_id;
68: g_gl_run := FALSE;
69: g_liq_has_failed_transactions := FALSE;
70: g_request_id := fnd_global.conc_request_id;
71:
72: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_bg_id: ' || g_bg_id || '
73: g_sob_id: ' || g_sob_id || '
74: g_payroll_id: ' || g_payroll_id || '
75: g_currency_code: ' || g_currency_code || '
76: g_control_rec_found: TRUE
86: g_dff_grouping_option: ' || g_dff_grouping_option || '
87: g_request_id: ' || g_request_id || '
88: g_gl_run: FALSE
89: g_g_liq_has_failed_transactions: FALSE');
90: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_begin');
91:
92: enc_batch_begin(p_payroll_action_id, p_payroll_id, p_action_type, l_return_status);
93:
94: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
93:
94: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
95: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96: END IF;
97: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_begin');
98: / ******* No control record found. Exiting from the program successfully ******* /
99: IF g_control_rec_found = 'FALSE'
100: THEN
101: --insert into psp_Stout values(99, 'no control rec found');
104: retcode := FND_API.G_RET_STS_SUCCESS;
105:
106: -- Added code for error message handler by Bijoy , 27-Jul-1999
107:
108: / *psp_message_s.print_error(p_mode => FND_FILE.LOG,
109: p_print_header => FND_API.G_TRUE);* /
110: if p_action_type IN ('L', 'T') then
111: PSP_MESSAGE_S.Print_success;
112: end if;
117: / * Added for Position Control Integration Enh 2505778 * /
118:
119: IF p_action_type in ('U','Q') then
120: if fnd_profile.value('PSP_ENC_ENABLE_PQH') ='Y' then
121: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling pqh_psp_integration.relieve_budget_commitments');
122: pqh_psp_integration.relieve_budget_commitments('L', l_return_status);
123: If l_return_status <>FND_API.G_RET_STS_SUCCESS THEN
124: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
125: fnd_msg_pub.add;
126: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127:
128:
129: end if;
130: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After pqh_psp_integration.relieve_budget_commitments');
131: end if;
132: end if;
133:
134:
133:
134:
135:
136:
137: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_gl_enc_liq_lines');
138: -- FIRST NORMAL RUN
139: -- initiate the gl encumbrance summarization and transfer
140: create_gl_enc_liq_lines(p_payroll_id,
141: p_action_type,
145: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
146: --insert into psp_stout values(11,'unex error in gl_enc');
147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
148: END IF;
149: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_gl_enc_liq_lines');
150: l_phase_status := 11 ; / * gl enc liq lines created in ist run * /
151: -- insert into psp_stout values( 'l_phase '||l_phase_status );
152: -- insert into psp_stout values( 11 ,l_phase_status );
153:
153:
154: -- call the user extension to populate attribute1 through attribute30
155: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
156: -- 2968684 added params to following proc
157: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
158: psp_st_ext.summary_ext_encumber_liq(p_payroll_id,
159: p_action_type ,
160: p_business_group_id,
161: p_set_of_books_id);
158: psp_st_ext.summary_ext_encumber_liq(p_payroll_id,
159: p_action_type ,
160: p_business_group_id,
161: p_set_of_books_id);
162: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
163: END IF;
164:
165: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
166: tr_to_gl_int( p_payroll_id,
161: p_set_of_books_id);
162: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
163: END IF;
164:
165: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
166: tr_to_gl_int( p_payroll_id,
167: p_action_type, -- Added for Restart Update Enh.
168: l_return_status
169: );
170:
171: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
172: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173: END IF;
174: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int
175: Calling create_gms_enc_liq_lines');
176:
177:
178: l_phase_status := 12 ; / * gl transfer success in ist run * /
186:
187: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
188: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189: END IF;
190: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_gms_enc_liq_lines');
191:
192: l_phase_status := 13 ; / * gms enc liq lines created in ist run * /
193: --insert_into_psp_stout( 'l_phase '||l_phase_status );
194: -- insert into psp_stout values( 13 ,l_phase_status );
195:
196: -- call the user extension to populate attribute1 through attribute30
197: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
198: -- 2968684 added params to following proc
199: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
200: psp_st_ext.summary_ext_encumber_liq(p_payroll_id,
201: p_action_type ,
202: p_business_group_id,
203: p_set_of_books_id);
200: psp_st_ext.summary_ext_encumber_liq(p_payroll_id,
201: p_action_type ,
202: p_business_group_id,
203: p_set_of_books_id);
204: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
205: END IF;
206:
207: -- if (G_GMS_AVAILABLE) then commented it out as tr_to_gl may still be required even
208: -- if no records are created in craete_gms_enc
206:
207: -- if (G_GMS_AVAILABLE) then commented it out as tr_to_gl may still be required even
208: -- if no records are created in craete_gms_enc
209:
210: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
211: tr_to_gms_int( p_payroll_id,
212: p_action_type, -- Added for Restart Update Enh.
213: l_return_status
214: );
217: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
218: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219: END IF;
220: -- end if;
221: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
222:
223: --G_GMS_AVAILABLE := FALSE; Commented for bug fix 4625734 as its no longer used in the process
224:
225: l_phase_status := 14 ; / * gms transfer success ist run * /
228: -- insert into psp_stout values( 14 ,l_phase_status );
229:
230: -- Introduced the following for Enh. Removal of Suspense Posting in Liq.
231: IF (g_gl_run) THEN
232: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
233: tr_to_gl_int (p_payroll_id,
234: p_action_type,
235: l_return_status);
236:
236:
237: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
238: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
239: END IF;
240: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
241:
242: l_phase_status := 16 ; / * gl transfer success in 2nd run * /
243: END IF;
244:
320: -- insert into psp_stout values( 18 ,l_phase_status );
321:
322: End of comment for Enh. Removal of Suspense posting in Liq ***** /
323:
324: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
325: enc_batch_end( p_payroll_action_id,
326: p_payroll_id,
327: p_Action_type,
328: 'N', -- added param Bug 2039196
333:
334: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
335: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
336: END IF;
337: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
338: -- retcode := FND_API.G_RET_STS_SUCCESS;
339: -- Replaced default successful return status with warning / success based on failed transactions check
340: IF (g_liq_has_failed_transactions) THEN
341: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
337: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
338: -- retcode := FND_API.G_RET_STS_SUCCESS;
339: -- Replaced default successful return status with warning / success based on failed transactions check
340: IF (g_liq_has_failed_transactions) THEN
341: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
342: retcode := fnd_api.g_ret_sts_error;
343: fnd_message.set_name('PSP','PSP_ENC_LIQ_TRANS_FAILED');
344: fnd_msg_pub.add;
345: psp_message_s.print_error(p_mode => FND_FILE.LOG,
341: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
342: retcode := fnd_api.g_ret_sts_error;
343: fnd_message.set_name('PSP','PSP_ENC_LIQ_TRANS_FAILED');
344: fnd_msg_pub.add;
345: psp_message_s.print_error(p_mode => FND_FILE.LOG,
346: p_print_header => FND_API.G_TRUE);
347: ELSE
348: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
349: retcode := FND_API.G_RET_STS_SUCCESS;
344: fnd_msg_pub.add;
345: psp_message_s.print_error(p_mode => FND_FILE.LOG,
346: p_print_header => FND_API.G_TRUE);
347: ELSE
348: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
349: retcode := FND_API.G_RET_STS_SUCCESS;
350: END IF;
351: -- End of changes for Enh. removal of suspense posting in liquidation.
352:
349: retcode := FND_API.G_RET_STS_SUCCESS;
350: END IF;
351: -- End of changes for Enh. removal of suspense posting in liquidation.
352:
353: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
354: -- Added code for error message handler by Bijoy , 27-Jul-1999
355:
356: / *psp_message_s.print_error(p_mode => FND_FILE.LOG,
357: p_print_header => FND_API.G_TRUE);* /
352:
353: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
354: -- Added code for error message handler by Bijoy , 27-Jul-1999
355:
356: / *psp_message_s.print_error(p_mode => FND_FILE.LOG,
357: p_print_header => FND_API.G_TRUE);* /
358: if p_action_type ='L' then
359: PSP_MESSAGE_S.Print_success;
360: end if;
376: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
377: retcode := 2;
378: -- Added code for error message handler by Bijoy , 27-Jul-1999
379:
380: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
381: psp_message_s.print_error(p_mode => FND_FILE.LOG,
382: p_print_header => FND_API.G_TRUE);
383: return;
384: WHEN OTHERS THEN
377: retcode := 2;
378: -- Added code for error message handler by Bijoy , 27-Jul-1999
379:
380: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
381: psp_message_s.print_error(p_mode => FND_FILE.LOG,
382: p_print_header => FND_API.G_TRUE);
383: return;
384: WHEN OTHERS THEN
385: / * Bug 20393936: commented call to batch_end proc and introduced ROLLBACK * /
398: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
399: retcode := 2;
400: -- Added code for error message handler by Bijoy , 27-Jul-1999
401:
402: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
403: psp_message_s.print_error(p_mode => FND_FILE.LOG,
404: p_print_header => FND_API.G_TRUE);
405: return;
406: END enc_liq_trans;
399: retcode := 2;
400: -- Added code for error message handler by Bijoy , 27-Jul-1999
401:
402: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
403: psp_message_s.print_error(p_mode => FND_FILE.LOG,
404: p_print_header => FND_API.G_TRUE);
405: return;
406: END enc_liq_trans;
407: End of comment for create and update multi thread enh. *****/
414: l_return_status VARCHAR2(10);
415: l_msg_count NUMBER;
416: l_msg_data VARCHAR2(2000);
417: BEGIN
418: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering LIQ_TRANS
419: p_payroll_action_id: ' || p_payroll_action_id || '
420: p_business_group_id: ' || p_business_group_id || '
421: p_set_of_books_id: ' || p_set_of_books_id);
422: g_error_api_path := '';
429: g_request_id := fnd_global.conc_request_id;
430: g_currency_code := psp_general.get_currency_code(p_business_group_id);
431: g_dff_grouping_option := psp_general.get_enc_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
432:
433: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' PA Transaction Purgeble Flag set');
434:
435: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_control_rec_found: TRUE
436: g_person_id: ' || g_person_id || '
437: g_bg_id: ' || g_bg_id || '
431: g_dff_grouping_option := psp_general.get_enc_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
432:
433: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' PA Transaction Purgeble Flag set');
434:
435: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_control_rec_found: TRUE
436: g_person_id: ' || g_person_id || '
437: g_bg_id: ' || g_bg_id || '
438: g_sob_id: ' || g_sob_id || '
439: g_term_period_id: ' || g_term_period_id || '
446: g_request_id: ' || g_request_id || '
447: g_gl_run: FALSE
448: g_g_liq_has_failed_transactions: FALSE');
449:
450: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_begin');
451: enc_batch_begin(p_payroll_action_id, l_return_status);
452: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
453: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454: END IF;
451: enc_batch_begin(p_payroll_action_id, l_return_status);
452: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
453: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454: END IF;
455: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_begin');
456:
457: IF (g_control_rec_found = 'FALSE') THEN
458: fnd_message.set_name('PSP','PSP_ENC_NO_LIQ_REC_FOUND');
459: fnd_msg_pub.add;
461: RETURN;
462: END IF;
463:
464: IF (fnd_profile.value('PSP_ENC_ENABLE_PQH') ='Y') THEN
465: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling pqh_psp_integration.relieve_budget_commitments');
466: pqh_psp_integration.relieve_budget_commitments('L', l_return_status);
467:
468: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
469: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
469: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
470: fnd_msg_pub.add;
471: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472: END IF;
473: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After pqh_psp_integration.relieve_budget_commitments');
474: END IF;
475:
476: IF fnd_profile.value('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
477: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
473: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After pqh_psp_integration.relieve_budget_commitments');
474: END IF;
475:
476: IF fnd_profile.value('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
477: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
478: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
479: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
480: END IF;
481:
475:
476: IF fnd_profile.value('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
477: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
478: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
479: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
480: END IF;
481:
482: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
483: tr_to_gl_int(p_payroll_action_id, l_return_status);
478: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
479: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
480: END IF;
481:
482: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
483: tr_to_gl_int(p_payroll_action_id, l_return_status);
484:
485: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
486: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484:
485: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
486: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
487: END IF;
488: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
489:
490: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
491: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
492: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
487: END IF;
488: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
489:
490: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
491: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
492: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
493: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
494: END IF;
495:
489:
490: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
491: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
492: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
493: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
494: END IF;
495:
496: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
497: tr_to_gms_int(p_payroll_action_id, l_return_status);
492: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
493: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
494: END IF;
495:
496: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
497: tr_to_gms_int(p_payroll_action_id, l_return_status);
498:
499: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
500: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498:
499: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
500: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501: END IF;
502: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
503:
504: IF (g_gl_run) THEN
505: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
506: tr_to_gl_int (p_payroll_action_id, l_return_status);
501: END IF;
502: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
503:
504: IF (g_gl_run) THEN
505: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
506: tr_to_gl_int (p_payroll_action_id, l_return_status);
507:
508: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
509: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
507:
508: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
509: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
510: END IF;
511: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
512: END IF;
513:
514: IF (g_liq_has_failed_transactions) THEN
515: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
511: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
512: END IF;
513:
514: IF (g_liq_has_failed_transactions) THEN
515: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
516: fnd_message.set_name('PSP','PSP_ENC_LIQ_TRANS_FAILED');
517: fnd_msg_pub.add;
518: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
519: p_return_status := fnd_api.g_ret_sts_error;
514: IF (g_liq_has_failed_transactions) THEN
515: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
516: fnd_message.set_name('PSP','PSP_ENC_LIQ_TRANS_FAILED');
517: fnd_msg_pub.add;
518: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
519: p_return_status := fnd_api.g_ret_sts_error;
520: ELSE
521: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
522: p_return_status := FND_API.G_RET_STS_SUCCESS;
517: fnd_msg_pub.add;
518: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
519: p_return_status := fnd_api.g_ret_sts_error;
520: ELSE
521: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
522: p_return_status := FND_API.G_RET_STS_SUCCESS;
523: END IF;
524:
525: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
521: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
522: p_return_status := FND_API.G_RET_STS_SUCCESS;
523: END IF;
524:
525: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
526: enc_batch_end(p_payroll_action_id, p_business_group_id, p_set_of_books_id, l_return_status);
527: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
528: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
529: END IF;
526: enc_batch_end(p_payroll_action_id, p_business_group_id, p_set_of_books_id, l_return_status);
527: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
528: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
529: END IF;
530: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
531: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving LIQ_TRANS');
532: EXCEPTION
533: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
527: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
528: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
529: END IF;
530: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
531: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving LIQ_TRANS');
532: EXCEPTION
533: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
535: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
532: EXCEPTION
533: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
535: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
537: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
538: p_return_status := fnd_api.g_ret_sts_unexp_error;
539: WHEN OTHERS THEN
540: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
533: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
535: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
537: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
538: p_return_status := fnd_api.g_ret_sts_unexp_error;
539: WHEN OTHERS THEN
540: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
541: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
537: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
538: p_return_status := fnd_api.g_ret_sts_unexp_error;
539: WHEN OTHERS THEN
540: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
541: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
542: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
543: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
544: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
539: WHEN OTHERS THEN
540: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
541: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
542: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
543: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
544: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
545: p_return_status := fnd_api.g_ret_sts_unexp_error;
546: END enc_liq_trans;
547: -- End of changes for Create and update multi thread enh.
540: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
541: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
542: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
543: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
544: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
545: p_return_status := fnd_api.g_ret_sts_unexp_error;
546: END enc_liq_trans;
547: -- End of changes for Create and update multi thread enh.
548: -- #######################################################################
903: enc_control_rec2 enc_control_cur2%ROWTYPE;
904: enc_control_rec3 enc_control_cur3%ROWTYPE; --- added for 3413373
905: End of comment for bug fix 4625734 *****/
906: BEGIN
907: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering ENC_BATCH_BEGIN');
908: /***** Commented the following for bug fix 4625734
909: if g_person_id is null then --- 3413373
910: / * Added for bug 2259310 * /
911: OPEN max_period_cur;
998: --COMMIT:
999: End of Comment for bug fix 4625734 *****/
1000:
1001: SELECT psp_st_run_id_s.nextval INTO g_run_id FROM dual;
1002: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_run_id: '||g_run_id);
1003: OPEN payroll_id_cur;
1004: LOOP
1005: FETCH payroll_id_cur INTO l_payroll_id;
1006: EXIT WHEN payroll_id_cur%NOTFOUND;
1004: LOOP
1005: FETCH payroll_id_cur INTO l_payroll_id;
1006: EXIT WHEN payroll_id_cur%NOTFOUND;
1007:
1008: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_payroll_id: '||l_payroll_id);
1009:
1010: OPEN payroll_run_date_cur;
1011: FETCH payroll_run_date_cur INTO l_payroll_run_date;
1012: CLOSE payroll_run_date_cur;
1009:
1010: OPEN payroll_run_date_cur;
1011: FETCH payroll_run_date_cur INTO l_payroll_run_date;
1012: CLOSE payroll_run_date_cur;
1013: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_payroll_run_date: '||l_payroll_run_date);
1014: /**** Commented the following for Create and Update multi threading enh.
1015: IF (g_person_id IS NULL) THEN
1016:
1017: OPEN max_period_cur;
1037: r_enc_controls.run_id, r_enc_controls.gl_phase,
1038: r_enc_controls.gms_phase, r_enc_controls.batch_name;
1039:
1040:
1041: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' enc_control_status_cur1%ROWCOUNT : '||enc_control_status_cur1%ROWCOUNT);
1042: CLOSE enc_control_status_cur1;
1043:
1044: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updating psp_enc_controls table setting action_code to ''IL'' ');
1045:
1040:
1041: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' enc_control_status_cur1%ROWCOUNT : '||enc_control_status_cur1%ROWCOUNT);
1042: CLOSE enc_control_status_cur1;
1043:
1044: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updating psp_enc_controls table setting action_code to ''IL'' ');
1045:
1046: UPDATE psp_enc_controls pec
1047: SET action_code = 'IL',
1048: run_id = g_run_id,
1070: r_enc_controls.action_code, r_enc_controls.action_type,
1071: r_enc_controls.run_id, r_enc_controls.gl_phase,
1072: r_enc_controls.gms_phase, r_enc_controls.batch_name;
1073:
1074: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' enc_control_status_cur2%ROWCOUNT : '||enc_control_status_cur2%ROWCOUNT);
1075: CLOSE enc_control_status_cur2;
1076:
1077: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updating psp_enc_controls table setting action_code to ''IU'' ');
1078:
1073:
1074: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' enc_control_status_cur2%ROWCOUNT : '||enc_control_status_cur2%ROWCOUNT);
1075: CLOSE enc_control_status_cur2;
1076:
1077: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updating psp_enc_controls table setting action_code to ''IU'' ');
1078:
1079: UPDATE psp_enc_controls pec
1080: SET action_code = 'IU',
1081: run_id = g_run_id,
1127: END IF;
1128: END IF;
1129: End of comment for Create and Update multi threading enh. ****/
1130:
1131: fnd_file.put_line(fnd_file.output, 'Liquidation Batch Processing, Started at: ' ||
1132: fnd_date.date_to_canonical(SYSDATE) || 'Run ID: ' || g_run_id || 'Payroll Action Id: ' || p_payroll_action_id || ' Last payroll run date: ' ||
1133: l_payroll_run_date || 'Encumbrance control record(s) status prior to liquidation processing');
1134: fnd_file.put_line(fnd_file.output, LPAD('Run ID', 15, ' ') || ' ' ||
1135: RPAD('Action Code', 15, ' ') || ' ' ||
1130:
1131: fnd_file.put_line(fnd_file.output, 'Liquidation Batch Processing, Started at: ' ||
1132: fnd_date.date_to_canonical(SYSDATE) || 'Run ID: ' || g_run_id || 'Payroll Action Id: ' || p_payroll_action_id || ' Last payroll run date: ' ||
1133: l_payroll_run_date || 'Encumbrance control record(s) status prior to liquidation processing');
1134: fnd_file.put_line(fnd_file.output, LPAD('Run ID', 15, ' ') || ' ' ||
1135: RPAD('Action Code', 15, ' ') || ' ' ||
1136: RPAD('Action Type', 15, ' ') || ' ' ||
1137: RPAD('GL Phase', 15, ' ') || ' ' ||
1138: RPAD('GMS Phase', 15, ' ') || ' ' ||
1151: LPAD('Summary GMS Cr', 15, ' ') || ' ' ||
1152: LPAD('Control Records', 15, ' ') || ' ' ||
1153: LPAD('Min Time Period', 15, ' ') || ' ' ||
1154: LPAD('Max Time Period', 15, ' '));
1155: fnd_file.put_line(fnd_file.output, LPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1156: RPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1157: RPAD('-', 23, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1158: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1159: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1161: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1162: LPAD('-', 15, '-'));
1163: FOR recno IN 1..r_enc_controls.run_id.COUNT
1164: LOOP
1165: fnd_file.put_line(fnd_file.output, LPAD(r_enc_controls.run_id(recno), 15, ' ') || ' ' ||
1166: RPAD(r_enc_controls.action_code(recno), 15, ' ') || ' ' ||
1167: RPAD(r_enc_controls.action_type(recno), 15, ' ') || ' ' ||
1168: RPAD(r_enc_controls.gl_phase(recno), 15, ' ') || ' ' ||
1169: RPAD(r_enc_controls.gms_phase(recno), 15, ' ') || ' ' ||
1193: r_summary_lines.dr_cr_flag, r_summary_lines.status_code,
1194: r_summary_lines.summary_amount, r_summary_lines.summary_lines_count;
1195: CLOSE summary_lines_cur;
1196:
1197: fnd_file.put_line(fnd_file.output, ' Encumbrance summary line(s) status prior to liquidation processing');
1198: fnd_file.put_line(fnd_file.output, RPAD('Employee Name', 50, ' ') || ' ' ||
1199: RPAD('Employee Number', 30, ' ') || ' ' || RPAD('Assignment Number', 30, ' ') || ' ' ||
1200: RPAD('GL Project Flag', 15, ' ') || ' ' || LPAD('Group ID', 15, ' ') || ' ' ||
1201: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1194: r_summary_lines.summary_amount, r_summary_lines.summary_lines_count;
1195: CLOSE summary_lines_cur;
1196:
1197: fnd_file.put_line(fnd_file.output, ' Encumbrance summary line(s) status prior to liquidation processing');
1198: fnd_file.put_line(fnd_file.output, RPAD('Employee Name', 50, ' ') || ' ' ||
1199: RPAD('Employee Number', 30, ' ') || ' ' || RPAD('Assignment Number', 30, ' ') || ' ' ||
1200: RPAD('GL Project Flag', 15, ' ') || ' ' || LPAD('Group ID', 15, ' ') || ' ' ||
1201: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1202: RPAD('Status Code', 15, ' ') || ' ' || LPAD('Summary Amount', 15, ' ') || ' ' ||
1201: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1202: RPAD('Status Code', 15, ' ') || ' ' || LPAD('Summary Amount', 15, ' ') || ' ' ||
1203: LPAD('Summary Lines', 15, ' '));
1204:
1205: fnd_file.put_line(fnd_file.output, RPAD('-', 50, '-') || ' ' || RPAD('-', 30, '-') || ' ' ||
1206: RPAD('-', 30, '-') || ' ' || RPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1207: RPAD('-', 15, '-') || ' ' || RPAD('-', 10, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1208: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-'));
1209:
1208: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-'));
1209:
1210: FOR recno IN 1..r_summary_lines.person_id.COUNT
1211: LOOP
1212: fnd_file.put_line(fnd_file.output, RPAD(r_summary_lines.full_name(recno), 50, ' ') || ' ' ||
1213: RPAD(r_summary_lines.employee_number(recno), 30, ' ') || ' ' ||
1214: RPAD(r_summary_lines.assignment_number(recno), 30, ' ') || ' ' ||
1215: RPAD(r_summary_lines.gl_project_flag(recno), 15, ' ') || ' ' ||
1216: LPAD(r_summary_lines.group_id(recno), 15, ' ') || ' ' ||
1220: LPAD(r_summary_lines.summary_amount(recno), 15, ' ') || ' ' ||
1221: LPAD(r_summary_lines.summary_lines_count(recno), 15, ' '));
1222: END LOOP;
1223:
1224: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_BATCH_BEGIN');
1225: p_return_status := fnd_api.g_ret_sts_success;
1226: END enc_batch_begin;
1227:
1228: -- ##########################################################################
1369: summary_amount t_number,
1370: summary_lines_count t_number_15);
1371: r_summary_lines r_summary_lines_rec;
1372: BEGIN
1373: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering ENC_BATCH_END');
1374: OPEN enc_control_cur;
1375: FETCH enc_control_cur BULK COLLECT INTO t_enc_control_id;
1376: CLOSE enc_control_cur;
1377:
1393: FOR bulk_idx IN 1..t_enc_control_id.COUNT
1394: loop
1395: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
1396: end loop;
1397: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count:'|| g_bulk_row_count);
1398:
1399: FORALL recno IN 1..t_enc_control_id.COUNT
1400: UPDATE psp_enc_summary_lines pesl
1401: SET status_code = 'S'
1410: FOR bulk_idx IN 1..t_enc_control_id.COUNT
1411: loop
1412: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
1413: end loop;
1414: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count'||g_bulk_row_count);
1415:
1416:
1417: FORALL recno IN 1..t_enc_control_id.COUNT
1418: UPDATE psp_enc_summary_lines pesl
1433: FOR bulk_idx IN 1..t_enc_control_id.COUNT
1434: loop
1435: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
1436: end loop;
1437: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines for lines rejected or not imported into target systems, count: '||g_bulk_row_count);
1438:
1439:
1440: FORALL recno IN 1..t_enc_control_id.COUNT
1441: UPDATE psp_enc_lines_history pelh
1450: FOR bulk_idx IN 1..t_enc_control_id.COUNT
1451: loop
1452: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
1453: end loop;
1454: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated change_flag to ''L'' in psp_enc_lines_history for lines that are liquidated count: '||g_bulk_row_count);
1455:
1456:
1457: FORALL recno IN 1..t_enc_control_id.COUNT
1458: UPDATE psp_enc_controls
1467: FOR bulk_idx IN 1..t_enc_control_id.COUNT
1468: loop
1469: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
1470: end loop;
1471: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated action_code to ''L'' in psp_enc_controls for control records whose enc summary lines are completely liquidated count '||g_bulk_row_count);
1472:
1473:
1474: FORALL recno IN 1..t_enc_control_id.COUNT
1475: UPDATE psp_enc_controls
1483: FOR bulk_idx IN 1..t_enc_control_id.COUNT
1484: loop
1485: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
1486: end loop;
1487: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated action_code to ''P'' in psp_enc_controls for control records whose enc summary lines aren''t completely liquidated count'||g_bulk_row_count);
1488:
1489:
1490: UPDATE psp_enc_processes
1491: SET process_phase = 'completed',
1497: WHERE pesl.payroll_action_id = p_payroll_action_id
1498: AND pesl.status_code = 'N');
1499:
1500: IF (SQL%ROWCOUNT> 0) THEN
1501: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to completed as no summarize and transfer is required');
1502: END IF;
1503:
1504: UPDATE psp_enc_processes
1505: SET process_phase = 'summarize_transfer'
1511: AND pesl.status_code = 'N'
1512: AND pesl.superceded_line_id IS NULL);
1513:
1514: IF (SQL%ROWCOUNT> 0) THEN
1515: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to summarize_transfer as there are new lines to be suimmarized and transferred');
1516: END IF;
1517:
1518: UPDATE psp_enc_processes
1519: SET process_phase = 'liquidate'
1523: FROM psp_enc_summary_lines pesl
1524: WHERE pesl.payroll_action_id = p_payroll_action_id
1525: AND pesl.status_code = 'N'
1526: AND pesl.superceded_line_id IS NOT NULL);
1527: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to liquidate as liquidation process isn''t complete');
1528:
1529: /***** Commented for Create and Update multi thread enh.
1530: IF p_action_type in ('U','Q') THEN
1531: IF NOT NVL(g_liq_has_failed_transactions, FALSE) THEN
1528:
1529: /***** Commented for Create and Update multi thread enh.
1530: IF p_action_type in ('U','Q') THEN
1531: IF NOT NVL(g_liq_has_failed_transactions, FALSE) THEN
1532: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_enc_update_lines.cleanup_on_success');
1533: psp_enc_update_lines.cleanup_on_success(p_action_type,
1534: p_payroll_id,
1535: p_business_group_id,
1536: p_set_of_books_id,
1536: p_set_of_books_id,
1537: 'N',
1538: p_return_status);
1539: ELSE
1540: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_enc_update_lines.cleanup_on_success');
1541: psp_enc_update_lines.rollback_rejected_asg (p_payroll_id ,
1542: p_action_type,
1543: g_gms_batch_name,
1544: g_accepted_group_id,
1564: AND pesl.assignment_id = pepa.assignment_id
1565: AND pesl.payroll_id = pepa.payroll_id
1566: AND pesl.status_code = 'N');
1567:
1568: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_enc_process_assignmnets set assignment_status to P sql%rowcount'||sql%rowcount);
1569:
1570:
1571: UPDATE psp_enc_process_assignments pepa
1572: SET assignment_status = 'S'
1578: AND pesl.payroll_id = pepa.payroll_id
1579: AND pesl.status_code = 'N'
1580: AND pesl.superceded_line_id IS NULL);
1581:
1582: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_enc_process_assignmnets set assignment_status to S sql%rowcount'||sql%rowcount);
1583:
1584:
1585: UPDATE psp_enc_process_assignments pepa
1586: SET assignment_status = 'L'
1592: AND pesl.payroll_id = pepa.payroll_id
1593: AND pesl.status_code = 'N'
1594: AND pesl.superceded_line_id IS NOT NULL);
1595:
1596: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_enc_process_assignmnets set assignment_status to L sql%rowcount'||sql%rowcount);
1597:
1598:
1599: COMMIT;
1600: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Committing ');
1596: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update psp_enc_process_assignmnets set assignment_status to L sql%rowcount'||sql%rowcount);
1597:
1598:
1599: COMMIT;
1600: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Committing ');
1601:
1602: p_return_status := fnd_api.g_ret_sts_success;
1603:
1604: OPEN summary_lines_cur;
1609: r_summary_lines.dr_cr_flag, r_summary_lines.status_code,
1610: r_summary_lines.summary_amount, r_summary_lines.summary_lines_count;
1611: CLOSE summary_lines_cur;
1612:
1613: fnd_file.put_line(fnd_file.output, ' Encumbrance summary line(s) status after liquidation processing');
1614: fnd_file.put_line(fnd_file.output, RPAD('Employee Name', 50, ' ') || ' ' ||
1615: RPAD('Employee Number', 30, ' ') || ' ' || RPAD('Assignment Number', 30, ' ') || ' ' ||
1616: RPAD('GL Project Flag', 15, ' ') || ' ' || LPAD('Group ID', 15, ' ') || ' ' ||
1617: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1610: r_summary_lines.summary_amount, r_summary_lines.summary_lines_count;
1611: CLOSE summary_lines_cur;
1612:
1613: fnd_file.put_line(fnd_file.output, ' Encumbrance summary line(s) status after liquidation processing');
1614: fnd_file.put_line(fnd_file.output, RPAD('Employee Name', 50, ' ') || ' ' ||
1615: RPAD('Employee Number', 30, ' ') || ' ' || RPAD('Assignment Number', 30, ' ') || ' ' ||
1616: RPAD('GL Project Flag', 15, ' ') || ' ' || LPAD('Group ID', 15, ' ') || ' ' ||
1617: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1618: RPAD('Status Code', 15, ' ') || ' ' || LPAD('Summary Amount', 15, ' ') || ' ' ||
1617: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1618: RPAD('Status Code', 15, ' ') || ' ' || LPAD('Summary Amount', 15, ' ') || ' ' ||
1619: LPAD('Summary Lines', 15, ' '));
1620:
1621: fnd_file.put_line(fnd_file.output, RPAD('-', 50, '-') || ' ' || RPAD('-', 30, '-') || ' ' ||
1622: RPAD('-', 30, '-') || ' ' || RPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1623: RPAD('-', 15, '-') || ' ' || RPAD('-', 10, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1624: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-'));
1625:
1624: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-'));
1625:
1626: FOR recno IN 1..r_summary_lines.person_id.COUNT
1627: LOOP
1628: fnd_file.put_line(fnd_file.output, RPAD(r_summary_lines.full_name(recno), 50, ' ') || ' ' ||
1629: RPAD(r_summary_lines.employee_number(recno), 30, ' ') || ' ' ||
1630: RPAD(r_summary_lines.assignment_number(recno), 30, ' ') || ' ' ||
1631: RPAD(r_summary_lines.gl_project_flag(recno), 15, ' ') || ' ' ||
1632: LPAD(r_summary_lines.group_id(recno), 15, ' ') || ' ' ||
1650: r_enc_controls.run_id, r_enc_controls.gl_phase,
1651: r_enc_controls.gms_phase, r_enc_controls.batch_name;
1652: CLOSE enc_control_status_cur;
1653:
1654: fnd_file.put_line(fnd_file.output, ' Encumbrance control record(s) status after liquidation processing');
1655: fnd_file.put_line(fnd_file.output, LPAD('Run ID', 15, ' ') || ' ' || RPAD('Action Code', 15, ' ') || ' ' ||
1656: RPAD('Action Type', 15, ' ') || ' ' || RPAD('GL Phase', 15, ' ') || ' ' ||
1657: RPAD('GMS Phase', 15, ' ') || ' ' || RPAD('Batch Name', 23, ' ') || ' ' ||
1658: LPAD('No of Dr', 15, ' ') || ' ' || LPAD('No of Cr', 15, ' ') || ' ' ||
1651: r_enc_controls.gms_phase, r_enc_controls.batch_name;
1652: CLOSE enc_control_status_cur;
1653:
1654: fnd_file.put_line(fnd_file.output, ' Encumbrance control record(s) status after liquidation processing');
1655: fnd_file.put_line(fnd_file.output, LPAD('Run ID', 15, ' ') || ' ' || RPAD('Action Code', 15, ' ') || ' ' ||
1656: RPAD('Action Type', 15, ' ') || ' ' || RPAD('GL Phase', 15, ' ') || ' ' ||
1657: RPAD('GMS Phase', 15, ' ') || ' ' || RPAD('Batch Name', 23, ' ') || ' ' ||
1658: LPAD('No of Dr', 15, ' ') || ' ' || LPAD('No of Cr', 15, ' ') || ' ' ||
1659: LPAD('Total Dr', 15, ' ') || ' ' || LPAD('Total Cr', 15, ' ') || ' ' ||
1662: LPAD('Summary GL Dr', 15, ' ') || ' ' || LPAD('Summary GL Cr', 15, ' ') || ' ' ||
1663: LPAD('Summary GMS Dr', 15, ' ') || ' ' || LPAD('Summary GMS Cr', 15, ' ') || ' ' ||
1664: LPAD('Control Records', 15, ' ') || ' ' || LPAD('Min Time Period', 15, ' ') || ' ' ||
1665: LPAD('Max Time Period', 15, ' '));
1666: fnd_file.put_line(fnd_file.output, LPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1667: RPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1668: RPAD('-', 23, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1669: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1670: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1672: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1673: LPAD('-', 15, '-'));
1674: FOR recno IN 1..r_enc_controls.run_id.COUNT
1675: LOOP
1676: fnd_file.put_line(fnd_file.output, LPAD(r_enc_controls.run_id(recno), 15, ' ') || ' ' ||
1677: RPAD(r_enc_controls.action_code(recno), 15, ' ') || ' ' ||
1678: RPAD(r_enc_controls.action_type(recno), 15, ' ') || ' ' ||
1679: RPAD(r_enc_controls.gl_phase(recno), 15, ' ') || ' ' ||
1680: RPAD(r_enc_controls.gms_phase(recno), 15, ' ') || ' ' ||
1695: LPAD(r_enc_controls.min_time_period_id(recno), 15, ' ') || ' ' ||
1696: LPAD(r_enc_controls.max_time_period_id(recno), 15, ' '));
1697: END LOOP;
1698:
1699: fnd_file.put_line(fnd_file.output, 'Liquidation Batch Processing completed at: ' || fnd_date.date_to_canonical(SYSDATE));
1700: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_BATCH_END');
1701: END enc_batch_end;
1702:
1703: /***** Commented for bug fix 4625734
1696: LPAD(r_enc_controls.max_time_period_id(recno), 15, ' '));
1697: END LOOP;
1698:
1699: fnd_file.put_line(fnd_file.output, 'Liquidation Batch Processing completed at: ' || fnd_date.date_to_canonical(SYSDATE));
1700: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_BATCH_END');
1701: END enc_batch_end;
1702:
1703: /***** Commented for bug fix 4625734
1704: PROCEDURE enc_batch_end(p_payroll_id IN NUMBER,
2176:
2177: l_last_updated_by NUMBER(15);
2178: l_last_update_login NUMBER(15);
2179: BEGIN
2180: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering CREATE_GL_ENC_LIQ_LINES');
2181:
2182: l_last_updated_by := fnd_global.user_id;
2183: l_last_update_login := fnd_global.login_id;
2184:
2239: r_liq_lines.attribute9, r_liq_lines.attribute10;
2240: CLOSE enc_qupd_liq_cur;
2241: END IF;
2242:
2243: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_liq_lines.enc_summary_line_id.COUNT: ' || r_liq_lines.enc_summary_line_id.COUNT);
2244:
2245: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2246: INSERT INTO psp_enc_summary_lines
2247: (enc_summary_line_id, business_group_id, enc_control_id,
2275: UPDATE psp_enc_summary_lines
2276: SET status_code = 'S'
2277: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
2278:
2279: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
2280:
2281: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2282: UPDATE psp_enc_controls
2283: SET gl_phase = 'Summarize'
2282: UPDATE psp_enc_controls
2283: SET gl_phase = 'Summarize'
2284: WHERE enc_control_id = r_liq_lines.enc_control_id(recno);
2285:
2286: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Summarize'' in psp_enc_controls');
2287:
2288: p_return_status := fnd_api.g_ret_sts_success;
2289:
2290: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2286: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Summarize'' in psp_enc_controls');
2287:
2288: p_return_status := fnd_api.g_ret_sts_success;
2289:
2290: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2291: EXCEPTION
2292: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2293: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2294: p_return_status := fnd_api.g_ret_sts_unexp_error;
2291: EXCEPTION
2292: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2293: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2294: p_return_status := fnd_api.g_ret_sts_unexp_error;
2295: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2296: WHEN OTHERS THEN
2297: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2298: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2299: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GL_ENC_LIQ_LINES');
2293: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2294: p_return_status := fnd_api.g_ret_sts_unexp_error;
2295: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2296: WHEN OTHERS THEN
2297: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2298: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2299: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GL_ENC_LIQ_LINES');
2300: p_return_status := fnd_api.g_ret_sts_unexp_error;
2301: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2297: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2298: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2299: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GL_ENC_LIQ_LINES');
2300: p_return_status := fnd_api.g_ret_sts_unexp_error;
2301: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2302: END create_gl_enc_liq_lines;
2303: -- End of changes for bg fix 4625734
2304:
2305: /***** Commented the following for bug fix 4625734
2789: FROM psp_enc_summary_lines
2790: WHERE group_id = l_group_id;
2791: -- End of changes for bug fix 4507892
2792: BEGIN
2793: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering Transfer to GL Interface');
2794:
2795: l_created_by := fnd_global.user_id;
2796:
2797: -- gl_tie_tab.delete; Commented for bug fix 4625734
2801: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2802: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2803: END IF;
2804:
2805: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_user_je_source: ' || l_user_je_source);
2806:
2807: gl_je_cat( l_user_je_cat,
2808: l_return_status);
2809: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2809: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2810: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2811: END IF;
2812:
2813: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_user_je_cat: ' || l_user_je_cat);
2814:
2815: enc_type( l_enc_type_id,
2816: l_return_status);
2817: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2817: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2818: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2819: END IF;
2820:
2821: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_enc_type_id: ' || l_enc_type_id);
2822:
2823: SELECT gl_interface_control_s.nextval
2824: INTO l_group_id
2825: FROM dual;
2823: SELECT gl_interface_control_s.nextval
2824: INTO l_group_id
2825: FROM dual;
2826:
2827: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_group_id: ' || l_group_id);
2828:
2829:
2830: UPDATE psp_enc_summary_lines
2831: SET group_id = l_group_id
2843: AND pec.business_group_id = g_bg_id
2844: AND pec.set_of_books_id = g_sob_id
2845: AND pec.gl_phase = 'Summarize');*/
2846:
2847: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated group_id in psp_enc_summary_lines for new liquidation lines');
2848: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into gl_interface');
2849:
2850: INSERT INTO gl_interface
2851: (status, set_of_books_id, accounting_date,
2844: AND pec.set_of_books_id = g_sob_id
2845: AND pec.gl_phase = 'Summarize');*/
2846:
2847: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated group_id in psp_enc_summary_lines for new liquidation lines');
2848: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into gl_interface');
2849:
2850: INSERT INTO gl_interface
2851: (status, set_of_books_id, accounting_date,
2852: currency_code, date_created, created_by,
3075: SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
3076: INTO l_int_run_id
3077: FROM dual;
3078:
3079: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_int_run_id: ' || l_int_run_id);
3080:
3081: insert into gl_interface_control(
3082: je_source_name,
3083: status,
3091: l_group_id,
3092: g_sob_id
3093: );
3094:
3095: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
3096: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Journal Import');
3097:
3098: req_id := fnd_request.submit_request(
3099: 'SQLGL',
3092: g_sob_id
3093: );
3094:
3095: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
3096: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Journal Import');
3097:
3098: req_id := fnd_request.submit_request(
3099: 'SQLGL',
3100: 'GLLEZL',
3110: 'W'); -- Changed 'N' to 'W' for bug fix 2908859
3111: --dbms_output.put_line('Req id = '||to_char(req_id));
3112:
3113: IF req_id = 0 THEN
3114: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import submission failed');
3115: -- insert into psp_stout values(0, 'req did not get submitted');
3116:
3117: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
3118: fnd_msg_pub.add;
3118: fnd_msg_pub.add;
3119: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3120:
3121: ELSE
3122: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submitted Journal Import (req_id: ' || req_id || ')');
3123: -- insert into psp_stout values(94, 'transfer in Gl');
3124: /***** Converted the following UPDATE TO BULK for R12 performance fixes (bug 4507892)
3125: update psp_enc_controls
3126: set gl_phase = 'Transfer'
3132: OPEN enc_control_id_cur;
3133: FETCH enc_control_id_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
3134: CLOSE enc_control_id_cur;
3135:
3136: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_enc_controls.enc_control_id.COUNT: ' || r_enc_controls.enc_control_id.COUNT);
3137:
3138: FORALL I IN 1..r_enc_controls.enc_control_id.COUNT
3139: UPDATE psp_enc_controls
3140: SET gl_phase = 'Transfer'
3139: UPDATE psp_enc_controls
3140: SET gl_phase = 'Transfer'
3141: WHERE enc_control_id = r_enc_controls.enc_control_id(I);
3142:
3143: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Transfer'' in psp_enc_controls');
3144:
3145: r_enc_controls.enc_control_id.DELETE;
3146: -- End of Changes for bug fix 4507892
3147:
3147:
3148: --insert into psp_stout values(5, ' request submitted ');
3149: COMMIT;
3150:
3151: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3152: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3153: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3154:
3155: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3149: COMMIT;
3150:
3151: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3152: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3153: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3154:
3155: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3156:
3157: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0,
3151: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3152: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3153: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3154:
3155: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3156:
3157: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0,
3158: rphase, rstatus, dphase, dstatus, message);
3159:
3157: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0,
3158: rphase, rstatus, dphase, dstatus, message);
3159:
3160: IF call_status = FALSE then
3161: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import failed');
3162: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
3163: fnd_msg_pub.add;
3164: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3165: END IF;
3162: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
3163: fnd_msg_pub.add;
3164: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3165: END IF;
3166: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import completed');
3167: END IF;
3168: END IF; -- l_rec_count > 0 moved this from below tie back for 2444657 (checks interface.COUNT bug 4625734)
3169:
3170: /***** Commented the following for bug fix 4625734
3189: OPEN gl_group_id_cur;
3190: FETCH gl_group_id_cur BULK COLLECT INTO r_groups.group_id;
3191: CLOSE gl_group_id_cur;
3192:
3193: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_groups.group_id.COUNT: ' || r_groups.group_id.COUNT);
3194:
3195: FOR recno IN 1..r_groups.group_id.COUNT
3196: LOOP
3197: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gl_enc_tie_back for group_id: ' || r_groups.group_id(recno));
3193: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_groups.group_id.COUNT: ' || r_groups.group_id.COUNT);
3194:
3195: FOR recno IN 1..r_groups.group_id.COUNT
3196: LOOP
3197: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gl_enc_tie_back for group_id: ' || r_groups.group_id(recno));
3198:
3199: gl_enc_tie_back(NULL, -- Enc Control id isnt reqd as tie back is by each group
3200: NULL, -- Period end date isnt reqd as tie back doesnt post to suspense
3201: r_groups.group_id(recno),
3206: l_return_status);
3207:
3208: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3209: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3210: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gl_enc_tie_back failed for group_id: ' || r_groups.group_id(recno));
3211: ELSE
3212: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gl_enc_tie_back successful for group_id: ' || r_groups.group_id(recno));
3213: END IF;
3214: END LOOP;
3208: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3209: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3210: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gl_enc_tie_back failed for group_id: ' || r_groups.group_id(recno));
3211: ELSE
3212: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gl_enc_tie_back successful for group_id: ' || r_groups.group_id(recno));
3213: END IF;
3214: END LOOP;
3215: -- End of changes for bug fix 4625734
3216:
3245: WHERE group_id = r_groups.group_id(recno)
3246: AND user_je_source_name = l_user_je_source
3247: AND set_of_books_id = g_sob_id;
3248:
3249: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted groups from gl_interface for which gl_enc_tie_back is complete');
3250: -- End of changes for bug fix 4625734
3251:
3252: COMMIT; -- moved commit below del stmnt for 2479579
3253:
3250: -- End of changes for bug fix 4625734
3251:
3252: COMMIT; -- moved commit below del stmnt for 2479579
3253:
3254: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' COMMITted gl_enc_tie_back');
3255:
3256: /***** commented for Enh. 2768298 Removal of suspense posting in liquidation
3257: if g_invalid_suspense = 'Y' then -- introduced this IF-ELSE for 2479579
3258: enc_batch_end(g_payroll_id,p_action_type,'N',g_bg_id,g_sob_id,l_return_status);
3260: end if;
3261: End of comment for Enh. 2768298 Removal of suspense posting in Liquidation *****/
3262:
3263: p_return_status := fnd_api.g_ret_sts_success;
3264: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
3265: EXCEPTION
3266: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3267: --dbms_output.put_line('Gone to one level top ..................');
3268: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
3266: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3267: --dbms_output.put_line('Gone to one level top ..................');
3268: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
3269: p_return_status := fnd_api.g_ret_sts_unexp_error;
3270: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
3271: /***** Commented for bug fix 4625734
3272: WHEN RETURN_BACK THEN
3273: p_return_status := fnd_api.g_ret_sts_success;
3274: End of comment for bug fix 4625734 *****/
3272: WHEN RETURN_BACK THEN
3273: p_return_status := fnd_api.g_ret_sts_success;
3274: End of comment for bug fix 4625734 *****/
3275: WHEN OTHERS THEN
3276: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3277: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
3278: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','TR_TO_GL_INT');
3279: p_return_status := fnd_api.g_ret_sts_unexp_error;
3280: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
3276: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3277: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
3278: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','TR_TO_GL_INT');
3279: p_return_status := fnd_api.g_ret_sts_unexp_error;
3280: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
3281: END tr_to_gl_int;
3282:
3283: -- ##########################################################################
3284: -- This procedure retrieves the user_je_source_name from gl_je_sources
3302: fnd_message.set_token('PRODUCT',l_product);
3303: fnd_msg_pub.add;
3304: p_return_status := fnd_api.g_ret_sts_unexp_error;
3305: WHEN OTHERS THEN
3306: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3307: g_error_api_path := 'gl_je_source:'||g_error_api_path;
3308: fnd_msg_pub.add_exc_msg('psp_enc_liq_tran','gl_je_source');
3309: p_return_status := fnd_api.g_ret_sts_unexp_error;
3310: END gl_je_source;
3331: fnd_message.set_token('PRODUCT',l_product);
3332: fnd_msg_pub.add;
3333: p_return_status := fnd_api.g_ret_sts_unexp_error;
3334: WHEN OTHERS THEN
3335: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3336: g_error_api_path := 'gl_je_cat:'||g_error_api_path;
3337: fnd_msg_pub.add_exc_msg('psp_enc_liq_tran','gl_je_cat');
3338: p_return_status := fnd_api.g_ret_sts_unexp_error;
3339: END gl_je_cat;
3363: fnd_message.set_token('PRODUCT',l_product);
3364: fnd_msg_pub.add;
3365: p_return_status := fnd_api.g_ret_sts_unexp_error;
3366: WHEN OTHERS THEN
3367: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3368: g_error_api_path := 'enc_type:'||g_error_api_path;
3369: fnd_msg_pub.add_exc_msg('psp_enc_liq_tran','enc_type');
3370: p_return_status := fnd_api.g_ret_sts_unexp_error;
3371:
3622: and group_id = p_group_id
3623: and status = 'NEW';
3624: End of comment for bug fix 4625734 *****/
3625:
3626: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3627:
3628: OPEN int_count_cur;
3629: FETCH int_count_cur INTO l_cnt;
3630: CLOSE int_count_cur;
3628: OPEN int_count_cur;
3629: FETCH int_count_cur INTO l_cnt;
3630: CLOSE int_count_cur;
3631:
3632: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
3633:
3634: if l_cnt = 0 then
3635: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3636: return TRUE;
3631:
3632: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
3633:
3634: if l_cnt = 0 then
3635: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3636: return TRUE;
3637: elsif l_cnt > 0 then
3638:
3639: -- -------------------------------------------------------------------------------------------
3646: where user_je_source_name = 'OLD'
3647: and set_of_books_id = p_set_of_books_id
3648: and group_id = p_group_id;
3649:
3650: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface');
3651:
3652: -- Introduced the following for bug fix 4625734
3653: OPEN superceded_line_id_cur;
3654: FETCH superceded_line_id_cur BULK COLLECT INTO r_superceded_lines.superceded_line_id;
3658: UPDATE psp_enc_summary_lines
3659: SET status_code = 'A'
3660: WHERE enc_summary_line_id = r_superceded_lines.superceded_line_id(recno);
3661:
3662: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to A for original summary lines');
3663:
3664: r_superceded_lines.superceded_line_id.DELETE;
3665:
3666: OPEN enc_controls_cur;
3666: OPEN enc_controls_cur;
3667: FETCH enc_controls_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
3668: CLOSE enc_controls_cur;
3669:
3670: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_enc_controls.enc_control_id.COUNT: ' || r_enc_controls.enc_control_id.COUNT);
3671: -- End of changes for bug fix 4625734
3672:
3673: delete from psp_enc_summary_lines
3674: where group_id = p_group_id;
3673: delete from psp_enc_summary_lines
3674: where group_id = p_group_id;
3675: -- and enc_control_id = p_enc_control_id; Commented for bug fix 4625734
3676:
3677: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
3678:
3679: -- Introduced the following for bug fix 4625734
3680: FORALL recno IN 1..r_enc_controls.enc_control_id.COUNT
3681: UPDATE psp_enc_controls pec
3682: SET gl_phase = 'TieBack'
3683: WHERE enc_control_id = r_enc_controls.enc_control_id(recno);
3684: -- End of changes for bug fix 4625734
3685:
3686: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reset gl_phase to ''TieBack''');
3687:
3688: r_enc_controls.enc_control_id.DELETE;
3689: g_liq_has_failed_transactions := TRUE;
3690:
3687:
3688: r_enc_controls.enc_control_id.DELETE;
3689: g_liq_has_failed_transactions := TRUE;
3690:
3691: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3692:
3693: return FALSE;
3694: end if;
3695:
3697: end PROCESS_COMPLETE;
3698:
3699: BEGIN
3700: --insert_into_psp_stout( 'gl enc tie back' );
3701: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK procedure');
3702: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_group_id: ' || p_group_id);
3703:
3704: IF (PROCESS_COMPLETE) THEN
3705:
3698:
3699: BEGIN
3700: --insert_into_psp_stout( 'gl enc tie back' );
3701: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK procedure');
3702: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_group_id: ' || p_group_id);
3703:
3704: IF (PROCESS_COMPLETE) THEN
3705:
3706: /***** Commented the following for bug fix 4625734
3715: OPEN int_count_cur;
3716: FETCH int_count_cur INTO l_cnt_gl_interface;
3717: CLOSE int_count_cur;
3718:
3719: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gl_interface: ' || l_cnt_gl_interface);
3720:
3721: IF (l_cnt_gl_interface > 0) THEN
3722: OPEN gl_tie_back_reject_cur;
3723: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3722: OPEN gl_tie_back_reject_cur;
3723: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3724: CLOSE gl_tie_back_reject_cur;
3725:
3726: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.status.COUNT: ' || r_interface.status.COUNT);
3727:
3728: FOR recno IN 1..r_interface.status.COUNT
3729: LOOP
3730: if (r_interface.status(recno) = 'P' OR SUBSTR(r_interface.status(recno), 1, 1) = 'W') and
3732: g_gl_run := TRUE;
3733: end if;
3734: END LOOP;
3735: if g_gl_run then
3736: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_gl_run: TRUE');
3737: end if;
3738:
3739:
3740: FORALL recno IN 1..r_interface.status.COUNT
3743: -- status_code = 'R'
3744: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3745: AND r_interface.status(recno) <> 'P'
3746: AND SUBSTR(r_interface.status(recno), 1, 1) <> 'W';
3747: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_enc_summary_lines setting interface_status');
3748:
3749: IF (SQL%ROWCOUNT > 0) THEN
3750: g_liq_has_failed_transactions := TRUE;
3751: g_rejected_group_id := p_group_id;
3748:
3749: IF (SQL%ROWCOUNT > 0) THEN
3750: g_liq_has_failed_transactions := TRUE;
3751: g_rejected_group_id := p_group_id;
3752: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' GL: g_liq_has_failed_transactions: TRUE');
3753: END IF;
3754:
3755: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_rejected_group_id: ' || g_rejected_group_id);
3756: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3751: g_rejected_group_id := p_group_id;
3752: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' GL: g_liq_has_failed_transactions: TRUE');
3753: END IF;
3754:
3755: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_rejected_group_id: ' || g_rejected_group_id);
3756: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3757:
3758: FORALL recno IN 1..r_interface.status.COUNT
3759: UPDATE psp_enc_controls
3752: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' GL: g_liq_has_failed_transactions: TRUE');
3753: END IF;
3754:
3755: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_rejected_group_id: ' || g_rejected_group_id);
3756: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3757:
3758: FORALL recno IN 1..r_interface.status.COUNT
3759: UPDATE psp_enc_controls
3760: SET gl_phase = 'TieBack'
3761: WHERE enc_control_id IN (SELECT pesl.enc_control_id
3762: FROM psp_enc_summary_lines pesl
3763: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3764:
3765: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_controls resetting gl_phase to TieBack SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3766:
3767: FORALL recno IN 1..r_interface.status.COUNT
3768: UPDATE psp_enc_controls
3769: SET gl_phase = 'Summarize'
3771: FROM psp_enc_summary_lines pesl
3772: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3773: AND pesl.status_code = 'N');
3774:
3775: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_controls resetting gl_phase to Summarize SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3776:
3777:
3778: ELSIF (l_cnt_gl_interface = 0) THEN
3779: g_accepted_group_id := p_group_id;
3777:
3778: ELSIF (l_cnt_gl_interface = 0) THEN
3779: g_accepted_group_id := p_group_id;
3780:
3781: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_accepted_group_id: ' || g_accepted_group_id);
3782:
3783: OPEN gl_tie_back_success_cur;
3784: FETCH gl_tie_back_success_cur BULK COLLECT INTO r_interface.enc_summary_line_id, r_interface.enc_control_id, r_interface.dr_cr_flag, r_interface.summary_amount;
3785: CLOSE gl_tie_back_success_cur;
3783: OPEN gl_tie_back_success_cur;
3784: FETCH gl_tie_back_success_cur BULK COLLECT INTO r_interface.enc_summary_line_id, r_interface.enc_control_id, r_interface.dr_cr_flag, r_interface.summary_amount;
3785: CLOSE gl_tie_back_success_cur;
3786:
3787: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.enc_summary_line_id.COUNT: ' || r_interface.enc_summary_line_id.COUNT);
3788:
3789: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3790: UPDATE psp_enc_summary_lines
3791: SET status_code = 'L'
3791: SET status_code = 'L'
3792: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3793: AND status_code = 'N';
3794:
3795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_summary_lines setting status_code to ''L'' SQL%ROWCOUNT ' || SQL%ROWCOUNT);
3796:
3797:
3798: IF (g_person_id IS NOT NULL) THEN
3799: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3801: SET change_flag = 'L'
3802: WHERE enc_summary_line_id = (SELECT pesl2.superceded_line_id
3803: FROM psp_enc_summary_lines pesl2
3804: WHERE pesl2.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3805: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated respective lines in psp_enc_lines_history to ''L'' status SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3806: END IF;
3807:
3808: FORALL recno IN 1..r_interface.enc_control_id.COUNT
3809: UPDATE psp_enc_controls pec
3810: SET gl_phase = 'TieBack',
3811: summ_gl_dr_amount = NVL(summ_gl_dr_amount, 0) + DECODE(r_interface.dr_cr_flag(recno), 'D', r_interface.summary_amount(recno), 0),
3812: summ_gl_cr_amount = NVL(summ_gl_cr_amount, 0) + DECODE(r_interface.dr_cr_flag(recno), 'C', r_interface.summary_amount(recno), 0)
3813: WHERE enc_control_id = r_interface.enc_control_id(recno);
3814: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase, summ_gl_dr_amount, summ_gl_cr_amount in psp_enc_controls SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3815: END IF;
3816:
3817: /***** Commented the following for bug fix 4625734
3818: IF l_cnt_gl_interface > 0 THEN
4196: --
4197: END IF; -- IF (PROCESS_COMPLETE)
4198:
4199: p_return_status := fnd_api.g_ret_sts_success;
4200: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
4201: EXCEPTION
4202:
4203: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4204: --dbms_output.put_line('Gone to one level top ..................');
4203: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4204: --dbms_output.put_line('Gone to one level top ..................');
4205: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
4206: p_return_status := fnd_api.g_ret_sts_unexp_error;
4207: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
4208: /***** Commented the following as part of bug fix 4625734
4209: / * Added Exceptions for bug 2056877 * /
4210: WHEN NO_PROFILE_EXISTS THEN
4211: g_error_api_path := SUBSTR('GL_ENC_TIE_BACK:'||g_error_api_path,1,230);
4232: p_return_status := fnd_api.g_ret_sts_success; --- changed error to success for 2479579
4233: end of comment for bug fix 4625734 *****/
4234:
4235: WHEN OTHERS THEN
4236: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
4237: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
4238: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','GL_ENC_TIE_BACK');
4239: p_return_status := fnd_api.g_ret_sts_unexp_error;
4240: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
4236: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
4237: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
4238: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','GL_ENC_TIE_BACK');
4239: p_return_status := fnd_api.g_ret_sts_unexp_error;
4240: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
4241:
4242: END gl_enc_tie_back;
4243:
4244: /***** Commented the following procedure as part of bug fix 4625734
4650:
4651: l_last_updated_by NUMBER(15);
4652: l_last_update_login NUMBER(15);
4653: BEGIN
4654: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering CREATE_GMS_ENC_LIQ_LINES');
4655:
4656: l_last_updated_by := fnd_global.user_id;
4657: l_last_update_login := fnd_global.login_id;
4658:
4656: l_last_updated_by := fnd_global.user_id;
4657: l_last_update_login := fnd_global.login_id;
4658:
4659: IF (g_person_id IS NOT NULL) THEN
4660: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Liquidation for Emplopyee Termination Lines');
4661: OPEN emp_term_enc_liq_cur;
4662: FETCH emp_term_enc_liq_cur BULK COLLECT INTO r_liq_lines.enc_summary_line_id,
4663: r_liq_lines.effective_date, r_liq_lines.enc_control_id,
4664: r_liq_lines.time_period_id, r_liq_lines.set_of_books_id,
4674: r_liq_lines.attribute7, r_liq_lines.attribute8,
4675: r_liq_lines.attribute9, r_liq_lines.attribute10;
4676: CLOSE emp_term_enc_liq_cur;
4677: ELSIF (g_person_id IS NULL AND p_action_type = 'L') THEN
4678: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Regular Liquidation Lines');
4679: OPEN enc_liq_cur;
4680: FETCH enc_liq_cur BULK COLLECT INTO r_liq_lines.enc_summary_line_id,
4681: r_liq_lines.effective_date, r_liq_lines.enc_control_id,
4682: r_liq_lines.time_period_id, r_liq_lines.set_of_books_id,
4692: r_liq_lines.attribute7, r_liq_lines.attribute8,
4693: r_liq_lines.attribute9, r_liq_lines.attribute10;
4694: CLOSE enc_liq_cur;
4695: ELSIF (p_action_type = 'U') THEN
4696: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Update Liquidation Lines');
4697: OPEN enc_upd_liq_cur;
4698: FETCH enc_upd_liq_cur BULK COLLECT INTO r_liq_lines.enc_summary_line_id,
4699: r_liq_lines.effective_date, r_liq_lines.enc_control_id,
4700: r_liq_lines.time_period_id, r_liq_lines.set_of_books_id,
4710: r_liq_lines.attribute7, r_liq_lines.attribute8,
4711: r_liq_lines.attribute9, r_liq_lines.attribute10;
4712: CLOSE enc_upd_liq_cur;
4713: ELSIF (p_action_type = 'Q') THEN
4714: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Quick Update Liquidation Lines');
4715: OPEN enc_qupd_liq_cur;
4716: FETCH enc_qupd_liq_cur BULK COLLECT INTO r_liq_lines.enc_summary_line_id,
4717: r_liq_lines.effective_date, r_liq_lines.enc_control_id,
4718: r_liq_lines.time_period_id, r_liq_lines.set_of_books_id,
4729: r_liq_lines.attribute9, r_liq_lines.attribute10;
4730: CLOSE enc_qupd_liq_cur;
4731: END IF;
4732:
4733: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_liq_lines.enc_summary_line_id.COUNT: ' || r_liq_lines.enc_summary_line_id.COUNT);
4734:
4735: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4736: INSERT INTO psp_enc_summary_lines
4737: (enc_summary_line_id, business_group_id, enc_control_id,
4769: UPDATE psp_enc_summary_lines
4770: SET status_code = 'S'
4771: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
4772:
4773: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
4774:
4775: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4776: UPDATE psp_enc_controls
4777: SET gms_phase = 'Summarize'
4776: UPDATE psp_enc_controls
4777: SET gms_phase = 'Summarize'
4778: WHERE enc_control_id = r_liq_lines.enc_control_id(recno);
4779:
4780: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''Summarize'' in psp_enc_controls');
4781:
4782: p_return_status := fnd_api.g_ret_sts_success;
4783:
4784: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4780: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''Summarize'' in psp_enc_controls');
4781:
4782: p_return_status := fnd_api.g_ret_sts_success;
4783:
4784: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4785: EXCEPTION
4786: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4787: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4788: p_return_status := fnd_api.g_ret_sts_unexp_error;
4785: EXCEPTION
4786: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4787: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4788: p_return_status := fnd_api.g_ret_sts_unexp_error;
4789: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4790: WHEN OTHERS THEN
4791: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
4792: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4793: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GMS_ENC_LIQ_LINES');
4787: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4788: p_return_status := fnd_api.g_ret_sts_unexp_error;
4789: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4790: WHEN OTHERS THEN
4791: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
4792: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4793: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GMS_ENC_LIQ_LINES');
4794: p_return_status := fnd_api.g_ret_sts_unexp_error;
4795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4791: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
4792: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4793: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GMS_ENC_LIQ_LINES');
4794: p_return_status := fnd_api.g_ret_sts_unexp_error;
4795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4796: END create_gms_enc_liq_lines;
4797: -- End of changes for bug fix 4625734
4798:
4799: /***** Commented the following procedure for revamping it using BULK FETCH features for bug fix 4625734
5247: AND gms_batch_name IS NULL
5248: AND payroll_action_id = p_payroll_action_id;
5249:
5250: BEGIN
5251: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering TR_TO_GMS_INT');
5252:
5253: -- gms_tie_tab.delete; Commented for bug fix 4625734
5254:
5255: -- if (gms_install.site_enabled) then -- Commented for bug fix 2908859
5273: End of comment for bug fix 4625734 *****/
5274: OPEN transaction_source_cur;
5275: FETCH transaction_source_cur INTO l_gms_transaction_source;
5276:
5277: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' transaction_source_cur transaction_source_cur'||transaction_source_cur%rowcount);
5278: CLOSE transaction_source_cur;
5279:
5280: IF (l_gms_transaction_source IS NULL) THEN
5281: fnd_message.set_name('PSP','PSP_TR_NOT_SET_UP');
5302: call_status_tab.delete;
5303:
5304: OPEN operating_unit_csr;
5305: FETCH operating_unit_csr BULK COLLECT INTO org_id_tab;
5306: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' operating_unit_csr operating_unit_csr%rowcount'||operating_unit_csr%rowcount);
5307: CLOSE operating_unit_csr;
5308:
5309:
5310: /***** SELECT to_char(psp_gms_batch_name_s.nextval)
5315: LOOP
5316: SELECT to_char(psp_gms_batch_name_s.nextval)
5317: INTO gms_batch_name_tab(i)
5318: FROM dual;
5319: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gms_batch_name_tab(' || I || '): ' || gms_batch_name_tab(i));
5320: END LOOP;
5321:
5322:
5323: /***** Commented for Create and Update multi thread enh.
5325: FETCH enc_control_cur BULK COLLECT INTO r_enc_control.enc_control_id, r_enc_control.payroll_id,
5326: r_enc_control.time_period_id, r_enc_control.gms_phase, r_enc_control.end_date;
5327: CLOSE enc_control_cur;
5328:
5329: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_enc_control.enc_control_id.COUNT: ' || r_enc_control.enc_control_id.COUNT);
5330: End of Comment for Create and Update multi thread enh. *****/
5331:
5332: -- FORALL recno IN 1..r_enc_control.enc_control_id.COUNT
5333: FORALL I IN 1..org_id_tab.count
5346: loop
5347: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5348: end loop;
5349:
5350: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated batch_name in psp_enc_summary_lines for each org_id count: '||g_bulk_row_count);
5351:
5352:
5353: OPEN int_cur;
5354: FETCH int_cur BULK COLLECT INTO r_interface.txn_interface_id, r_interface.enc_summary_line_id,
5366: r_interface.operating_unit, r_interface.task_number,
5367: r_interface.expenditure_comment, r_interface.period_name,
5368: r_interface.end_date, r_interface.gms_overriding_date,
5369: r_interface.employee_number, r_interface.gms_batch_name;
5370: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' int_cur int_cur%rowcount'||int_cur%rowcount);
5371: CLOSE int_cur;
5372:
5373:
5374:
5383: END IF;
5384: END LOOP;
5385: END LOOP;
5386:
5387: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.txn_interface_id.COUNT: ' || r_interface.txn_interface_id.COUNT);
5388: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Verifying interface records for errors');
5389:
5390: l_raise_error := FALSE;
5391: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5384: END LOOP;
5385: END LOOP;
5386:
5387: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.txn_interface_id.COUNT: ' || r_interface.txn_interface_id.COUNT);
5388: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Verifying interface records for errors');
5389:
5390: l_raise_error := FALSE;
5391: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5392: LOOP
5431: END IF;
5432: END LOOP;
5433:
5434: IF l_raise_error THEN
5435: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Interface records have errors');
5436: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5437: END IF;
5438:
5439: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
5435: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Interface records have errors');
5436: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5437: END IF;
5438:
5439: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
5440: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Computing PA week ending date(s)');
5441:
5442: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5443: LOOP
5436: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5437: END IF;
5438:
5439: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
5440: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Computing PA week ending date(s)');
5441:
5442: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5443: LOOP
5444: psp_general.get_gms_effective_date(r_interface.person_id(recno), r_interface.gms_overriding_date(recno));
5448: END LOOP;
5449: -- set the context again to multiple
5450: mo_global.set_policy_context('M', null);
5451:
5452: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed computation of PA week ending date(s)');
5453:
5454: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
5455: UPDATE psp_enc_summary_lines pesl
5456: SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
5462: loop
5463: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5464: end loop;
5465:
5466: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated GMS Override Date count: '||g_bulk_row_count);
5467:
5468:
5469: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
5470: INSERT INTO pa_transaction_interface_all
5501: FOR bulk_idx IN 1..r_interface.txn_interface_id.COUNT
5502: loop
5503: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5504: end loop;
5505: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into pa_transaction_interface_all count: '||g_bulk_row_count);
5506:
5507:
5508: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.txn_interface_id.COUNT: ' || r_interface.txn_interface_id.COUNT);
5509:
5504: end loop;
5505: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into pa_transaction_interface_all count: '||g_bulk_row_count);
5506:
5507:
5508: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.txn_interface_id.COUNT: ' || r_interface.txn_interface_id.COUNT);
5509:
5510:
5511: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5512: LOOP
5543: fnd_msg_pub.add;
5544: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5545: END IF;
5546: END LOOP;
5547: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into gms_transaction_interface_all by calling gms_transactions_pub.LOAD_GMS_XFACE_API');
5548:
5549: /***** Commented the following for bug fix 4625734
5550: LOOP
5551: FETCH enc_control_cur INTO enc_control_rec;
5811: End of comment for bug fix 4625734 *****/
5812: IF r_interface.txn_interface_id.COUNT > 0 THEN -- Introduced for bug fix 4625734
5813: -- IF l_rec_count > 0 THEN -- uncommented this line and commented the line below for 2444657 Commented for bug fix 4625734
5814: --- if l_rec_no >0 then
5815: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' org_id_tab.count: '||org_id_tab.count);
5816:
5817: FOR request_counter IN 1..org_id_tab.count
5818: LOOP
5819: -- set the context to single to call submit_request
5818: LOOP
5819: -- set the context to single to call submit_request
5820: mo_global.set_policy_context('S', org_id_tab(request_counter) );
5821: fnd_request.set_org_id (org_id_tab(request_counter) );
5822: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submitting Transaction Import for org_id: '||org_id_tab(request_counter));
5823: req_id_tab(request_counter) := fnd_request.submit_request(
5824: 'PA',
5825: 'PAXTRTRX',
5826: NULL,
5832:
5833: --dbms_output.put_line('Req id = '||to_char(req_id));
5834:
5835: IF req_id_tab(request_counter) = 0 THEN
5836: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submission of Transaction Import Failed');
5837: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5838: fnd_msg_pub.add;
5839: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5840: ELSE
5837: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5838: fnd_msg_pub.add;
5839: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5840: ELSE
5841: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submitted Transaction Import');
5842:
5843: /***** Modified teh following update to BULK UPDATE for R12 performance fixes (bug 4507892)
5844: update psp_enc_controls
5845: set gms_phase = 'Transfer'
5861: FOR bulk_idx IN 1..r_enc_controls.enc_control_id.COUNT
5862: loop
5863: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5864: end loop;
5865: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''Transfer'' in psp_enc_controls count: '||g_bulk_row_count);
5866:
5867: r_enc_controls.enc_control_id.DELETE;
5868: -- End of changes for bug fix 4507892
5869: END IF;
5868: -- End of changes for bug fix 4507892
5869: END IF;
5870: END LOOP;
5871: COMMIT;
5872: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Committed for TransactionInterface');
5873:
5874: -- set the context again to multiple
5875: mo_global.set_policy_context('M', null);
5876:
5873:
5874: -- set the context again to multiple
5875: mo_global.set_policy_context('M', null);
5876:
5877: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5878: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5879: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5880:
5881: FOR I IN 1..org_id_tab.count
5875: mo_global.set_policy_context('M', null);
5876:
5877: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5878: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5879: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5880:
5881: FOR I IN 1..org_id_tab.count
5882: LOOP
5883:
5882: LOOP
5883:
5884:
5885: -- insert into psp_Stout values(96, 'gms transfer started');
5886: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for comlpetion of Transaction Import for org_id: '||org_id_tab(I));
5887: call_status := fnd_concurrent.wait_for_request(req_id_tab(I), 10, 0,
5888: rphase, rstatus, dphase, dstatus, message);
5889:
5890: IF call_status = FALSE then
5887: call_status := fnd_concurrent.wait_for_request(req_id_tab(I), 10, 0,
5888: rphase, rstatus, dphase, dstatus, message);
5889:
5890: IF call_status = FALSE then
5891: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import failed');
5892: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5893: fnd_msg_pub.add;
5894: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5895: END IF;
5892: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5893: fnd_msg_pub.add;
5894: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5895: END IF;
5896: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import completed');
5897: END LOOP;
5898: END IF; --record_count > 0 ...move this line from below delete xface...for 2444657
5899:
5900:
5927: OPEN gms_batch_name_cur;
5928: FETCH gms_batch_name_cur BULK COLLECT INTO r_gms_batch.gms_batch_name;
5929: CLOSE gms_batch_name_cur;
5930:
5931: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_gms_batch.gms_batch_name.COUNT: ' || r_gms_batch.gms_batch_name.COUNT);
5932:
5933: FOR recno IN 1..r_gms_batch.gms_batch_name.COUNT
5934: LOOP
5935: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gms_enc_tie_back for gms_batch_name: ' || r_gms_batch.gms_batch_name(recno));
5931: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_gms_batch.gms_batch_name.COUNT: ' || r_gms_batch.gms_batch_name.COUNT);
5932:
5933: FOR recno IN 1..r_gms_batch.gms_batch_name.COUNT
5934: LOOP
5935: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gms_enc_tie_back for gms_batch_name: ' || r_gms_batch.gms_batch_name(recno));
5936: gms_enc_tie_back(NULL, -- Tieback is by batch and not by enc_control_id
5937: NULL, -- Period end date isnt required as liq doesnt have suspense posting
5938: r_gms_batch.gms_batch_name(recno),
5939: g_bg_id,
5942: NULL, --p_action_type,
5943: l_return_status);
5944:
5945: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5946: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gms_enc_tie_back failed for gms_batch_name: ' || r_gms_batch.gms_batch_name(recno));
5947: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5948: END IF;
5949: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gms_enc_tie_back successful for gms_batch_name: ' || r_gms_batch.gms_batch_name(recno));
5950: END LOOP;
5945: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5946: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gms_enc_tie_back failed for gms_batch_name: ' || r_gms_batch.gms_batch_name(recno));
5947: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5948: END IF;
5949: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gms_enc_tie_back successful for gms_batch_name: ' || r_gms_batch.gms_batch_name(recno));
5950: END LOOP;
5951: -- End of changes for bug fix 4625734
5952:
5953: /**** Commented the following LOOP to convert it into a proper gms batch loop for bug fix 4625734
5977: loop
5978: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5979: end loop;
5980:
5981: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_summary_lines setting expenditure_item_id count'||g_bulk_row_count);
5982:
5983:
5984: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5985: DELETE pa_transaction_interface_all
5990: FOR bulk_idx IN 1..r_gms_batch.gms_batch_name.COUNT
5991: loop
5992: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5993: end loop;
5994: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleting from pa_transaction_interface_all count'||g_bulk_row_count);
5995:
5996:
5997: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5998: DELETE gms_transaction_interface_all
6004: FOR bulk_idx IN 1..r_gms_batch.gms_batch_name.COUNT
6005: loop
6006: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6007: end loop;
6008: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleting from gms_transaction_interface_all count'||g_bulk_row_count);
6009:
6010: /***** Commented the following as there will be distinct gms batch names (as part of bug fix 4625734)
6011: for k in 1..gms_tie_tab.count
6012: loop
6021: End of Comment for bug fix 4625734 *****/
6022:
6023: COMMIT; -- moved this commit below for 2479579
6024:
6025: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Commmitting After GMS Tieback');
6026:
6027: /***** Commented for Enh. 2768298 Removal of suspense posting in Liquidation
6028: if g_invalid_suspense = 'Y' then -- introduced this IF-ELSE for 2479579
6029: enc_batch_end(g_payroll_id,p_action_type,'N',g_bg_id,g_sob_id,l_return_status);
6041: WHEN RETURN_BACK THEN
6042: p_return_status := fnd_api.g_ret_sts_success;
6043:
6044: WHEN OTHERS THEN
6045: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6046: g_error_api_path := 'TR_TO_GMS_INT:'||g_error_api_path;
6047: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','TR_TO_GMS_INT');
6048: p_return_status := fnd_api.g_ret_sts_unexp_error;
6049:
6327: AND transaction_Status_code in ('P','I')
6328: GROUP BY transaction_status_code;
6329: -- End of changes for bug fix 4625734
6330: begin
6331: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6332: /*
6333: select count(*), transaction_status_code
6334: into l_cnt, l_status
6335: from pa_transaction_interface_all
6355: l_cnt := 0;
6356: END IF;
6357: CLOSE transaction_status_cur;
6358:
6359: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
6360:
6361: if l_cnt = 0 then
6362: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6363: return TRUE;
6358:
6359: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
6360:
6361: if l_cnt = 0 then
6362: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6363: return TRUE;
6364: elsif l_cnt > 0 then
6365: if l_status = 'P' then
6366:
6375: delete from pa_transaction_interface_all
6376: where transaction_source = 'GOLDE'
6377: and batch_name = p_gms_batch_name;
6378:
6379: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from pa_trancsaction_interface_all sql%rowcount'||sql%rowcount);
6380:
6381: delete from gms_transaction_interface_all
6382: where transaction_source = 'GOLDE'
6383: and batch_name = p_gms_batch_name;
6381: delete from gms_transaction_interface_all
6382: where transaction_source = 'GOLDE'
6383: and batch_name = p_gms_batch_name;
6384:
6385: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all sql%rowcount'||sql%rowcount);
6386: ***/
6387:
6388: /***** Converted the following UPDATE to BULK for R12 performance fixes (bug 4507892)
6389: update psp_enc_summary_lines set status_code ='A' where
6394: OPEN superceded_line_id_cur;
6395: FETCH superceded_line_id_cur BULK COLLECT INTO r_superceded_lines.superceded_line_id;
6396: CLOSE superceded_line_id_cur;
6397:
6398: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_superceded_lines.superceded_line_id.COUNT: ' || r_superceded_lines.superceded_line_id.COUNT);
6399:
6400: FORALL I IN 1..r_superceded_lines.superceded_line_id.COUNT
6401: UPDATE psp_enc_summary_lines
6402: SET status_code = 'A'
6407: loop
6408: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6409: end loop;
6410:
6411: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated original lines to status_code ''A'' in psp_enc_summary_lines g_bulk_row_count'||g_bulk_row_count);
6412:
6413: r_superceded_lines.superceded_line_id.DELETE;
6414: -- End of changes for bug fix 4507892
6415:
6418: FETCH enc_controls_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
6419: CLOSE enc_controls_cur;
6420: -- End of changes for bug fix 4625734
6421:
6422: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_enc_controls.enc_control_id.COUNT: ' || r_enc_controls.enc_control_id.COUNT);
6423:
6424: delete from psp_enc_summary_lines
6425: where enc_summary_line_id in
6426: (
6432: AND gms_batch_name = p_gms_batch_name;
6433: -- Bug 13036705
6434: -- and enc_control_id = p_enc_control_id;
6435:
6436: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines sql%rowcount'||sql%rowcount);
6437:
6438: -- Introduced the folowing for bug fix 4625734
6439: FORALL recno IN 1..r_enc_controls.enc_control_id.COUNT
6440: UPDATE psp_enc_controls pec
6447: loop
6448: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6449: end loop;
6450:
6451: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''TieBack'' in psp_enc_controls count: '||g_bulk_row_count);
6452:
6453: r_enc_controls.enc_control_id.DELETE;
6454: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' GMS g_liq_has_failed_transactions := TRUE');
6455:
6450:
6451: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''TieBack'' in psp_enc_controls count: '||g_bulk_row_count);
6452:
6453: r_enc_controls.enc_control_id.DELETE;
6454: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' GMS g_liq_has_failed_transactions := TRUE');
6455:
6456: g_liq_has_failed_transactions := TRUE;
6457: -- End of changes for bug fix 4625734
6458:
6460: delete from pa_transaction_interface_all
6461: where transaction_source = 'GOLDE'
6462: and batch_name = p_gms_batch_name;
6463:
6464: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from pa_trancsaction_interface_all sql%rowcount'||sql%rowcount);
6465:
6466: delete from gms_transaction_interface_all
6467: where transaction_source = 'GOLDE'
6468: and batch_name = p_gms_batch_name;
6466: delete from gms_transaction_interface_all
6467: where transaction_source = 'GOLDE'
6468: and batch_name = p_gms_batch_name;
6469:
6470: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all sql%rowcount'||sql%rowcount);
6471:
6472:
6473:
6474: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE - P');
6470: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all sql%rowcount'||sql%rowcount);
6471:
6472:
6473:
6474: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE - P');
6475: return FALSE;
6476:
6477: elsif l_status = 'I' then
6478:
6481: -- the Post Processing extension. So return 'NOT_COMPLETE' in this case. User needs to complete
6482: -- this process by running the transaction import manually and re-start the LD process.
6483: -- -------------------------------------------------------------------------------------------
6484:
6485: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE - I');
6486: return FALSE;
6487:
6488: end if;
6489: end if;
6491: --Removed Exception block for Bug 10217464
6492: end PROCESS_COMPLETE;
6493:
6494: BEGIN
6495: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GMS_ENC_TIE_BACK');
6496:
6497: if (PROCESS_COMPLETE) then
6498: /***** Changed the following SELECT into CURSOR for bug fix 4625734
6499: SELECT count(*)
6508: OPEN txn_interface_count_cur;
6509: FETCH txn_interface_count_cur INTO l_cnt_gms_interface;
6510: CLOSE txn_interface_count_cur;
6511: -- End of changes for bug fix 4625734
6512: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gms_interface: ' || l_cnt_gms_interface);
6513:
6514: IF l_cnt_gms_interface > 0 THEN
6515: -- Introduced the following for bug fix 4625734
6516: OPEN gms_tie_back_reject_cur;
6517: FETCH gms_tie_back_reject_cur BULK COLLECT INTO r_reject_recs.reason_code, r_reject_recs.enc_summary_line_id,
6518: r_reject_recs.txn_status_code;
6519: CLOSE gms_tie_back_reject_cur;
6520:
6521: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_reject_recs.enc_summary_line_id.COUNT: ' || r_reject_recs.enc_summary_line_id.COUNT);
6522:
6523: FOR recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6524: LOOP
6525: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_reject_recs.reason_code(' || recno || '): ' || r_reject_recs.reason_code(recno));
6521: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_reject_recs.enc_summary_line_id.COUNT: ' || r_reject_recs.enc_summary_line_id.COUNT);
6522:
6523: FOR recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6524: LOOP
6525: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_reject_recs.reason_code(' || recno || '): ' || r_reject_recs.reason_code(recno));
6526: IF (r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR')) THEN
6527: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Setting g_liq_has_failed_transactions: TRUE');
6528: g_liq_has_failed_transactions := TRUE;
6529: EXIT;
6523: FOR recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6524: LOOP
6525: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_reject_recs.reason_code(' || recno || '): ' || r_reject_recs.reason_code(recno));
6526: IF (r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR')) THEN
6527: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Setting g_liq_has_failed_transactions: TRUE');
6528: g_liq_has_failed_transactions := TRUE;
6529: EXIT;
6530: END IF;
6531: END LOOP;
6541: loop
6542: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6543: end loop;
6544:
6545: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated interface_status with reject reason code in psp_enc_summary_lines count '||g_bulk_row_count);
6546:
6547: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6548: UPDATE psp_enc_summary_lines
6549: SET interface_status = r_reject_recs.reason_code(recno),
6556: FOR bulk_idx IN 1..r_reject_recs.enc_summary_line_id.COUNT
6557: loop
6558: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6559: end loop;
6560: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''L'' for accepted records in psp_enc_summary_lines count:'||g_bulk_row_count);
6561:
6562: IF (g_person_id IS NOT NULL) THEN
6563: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6564: UPDATE psp_enc_lines_history
6574: loop
6575: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6576: end loop;
6577:
6578: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated change_flag to ''L'' in psp_enc_lines_history for employee level encumbrance liquidation count'||g_bulk_row_count);
6579: END IF;
6580:
6581: OPEN get_success_recs_cur;
6582: FETCH get_success_recs_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6582: FETCH get_success_recs_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6583: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6584: CLOSE get_success_recs_cur;
6585:
6586: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_success_recs.enc_summary_line_id.COUNT: ' || r_success_recs.enc_summary_line_id.COUNT);
6587:
6588: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6589: UPDATE psp_enc_controls pec
6590: SET gms_phase = 'TieBack',
6599: loop
6600: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6601: end loop;
6602:
6603: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Update summ_ogm_cr_amount, summ_ogm_dr_amount in psp_enc_controls count'||g_bulk_row_count);
6604:
6605: r_reject_recs.enc_summary_line_id.DELETE;
6606: r_reject_recs.enc_control_id.DELETE;
6607: r_reject_recs.reason_code.DELETE;
6922: FETCH gms_tie_back_success_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6923: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6924: CLOSE gms_tie_back_success_cur;
6925:
6926: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_success_recs.enc_summary_line_id.COUNT: ' || r_success_recs.enc_summary_line_id.COUNT);
6927:
6928: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6929: UPDATE psp_enc_summary_lines
6930: SET status_code = 'L'
6937: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6938: end loop;
6939:
6940:
6941: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''L'' in psp_enc_summary_lines '||g_bulk_row_count);
6942:
6943: IF (g_person_id IS NOT NULL) THEN
6944: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6945: UPDATE psp_enc_lines_history
6953: loop
6954: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6955: end loop;
6956:
6957: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated change_flag to ''L'' in psp_enc_lines_history for employee level liquidation count'||g_bulk_row_count);
6958: END IF;
6959:
6960: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6961: UPDATE psp_enc_controls pec
6968: FOR bulk_idx IN 1..r_success_recs.enc_summary_line_id.COUNT
6969: loop
6970: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6971: end loop;
6972: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated summ_ogm_cr_amount, summ_ogm_dr_amount, gms_phase in psp_enc_controls count: '||g_bulk_row_count);
6973:
6974: r_success_recs.enc_summary_line_id.DELETE;
6975: r_success_recs.enc_control_id.DELETE;
6976: r_success_recs.reason_code.DELETE;
7045: --
7046: END IF; -- IF (PROCESS_COMPLETE)
7047:
7048: p_return_status := fnd_api.g_ret_sts_success;
7049: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
7050: EXCEPTION
7051: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7052: --dbms_output.put_line('Gone to one level top ..................');
7053: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
7051: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7052: --dbms_output.put_line('Gone to one level top ..................');
7053: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
7054: p_return_status := fnd_api.g_ret_sts_unexp_error;
7055: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
7056:
7057: /* Added Exceptions for Bug 2056877 */
7058: /***** Commented the following for bgu fix 4625734
7059: WHEN NO_PROFILE_EXISTS THEN
7082: p_return_status := fnd_api.g_ret_sts_success; --- replaced error with success for 2479579
7083: End of comment for bug fix 4625734 *****/
7084:
7085: WHEN OTHERS THEN
7086: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
7087: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
7088: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','GMS_ENC_TIE_BACK');
7089: p_return_status := fnd_api.g_ret_sts_unexp_error;
7090: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
7086: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
7087: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
7088: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','GMS_ENC_TIE_BACK');
7089: p_return_status := fnd_api.g_ret_sts_unexp_error;
7090: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
7091:
7092: END gms_enc_tie_back;
7093:
7094: /* Commented the procedure below as part of "Zero work days " enhancement Bug 1994421 */
7225: IF p_action_type = 'Q' THEN
7226: fnd_message.set_name('PSP','PSP_ENC_NUM_ASG');
7227: fnd_message.set_token('NUM_ASG',no_of_asg);
7228: fnd_msg_pub.add;
7229: psp_message_s.print_error (p_mode => FND_FILE.LOG,
7230: p_print_header => FND_API.G_FALSE);
7231: END IF;
7232:
7233: p_return_status := FND_API.G_RET_STS_SUCCESS;
7329: FROM per_all_people_f
7330: WHERE person_id = p_person_id
7331: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
7332: begin
7333: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering LIQUIDATE_EMP_TERM');
7334:
7335: g_person_id := p_person_id; --- added for 3413373
7336: g_actual_term_date := p_actual_term_date; --- added for 3413373
7337:
7340: LOOP
7341: FETCH inprogress_controls_cur INTO l_request_id, l_payroll_id, g_term_period_id, l_enc_control_id;
7342: EXIT WHEN inprogress_controls_cur%NOTFOUND;
7343:
7344: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_request_id: ' || l_request_id || '
7345: l_payroll_id: ' || l_payroll_id || '
7346: g_term_period_id: ' || g_term_period_id || '
7347: l_enc_control_id: ' || l_enc_control_id);
7348:
7349: OPEN term_employee_cur;
7350: FETCH term_employee_cur INTO l_person_id1, l_termination_date1;
7351: CLOSE term_employee_cur;
7352:
7353: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_person_id1: ' || l_person_id1 || ';l_termination_date1: ' || fnd_date.date_to_canonical(l_termination_date1));
7354:
7355: IF ((l_person_id1 <> p_person_id) OR (l_termination_date1 <> p_actual_term_date)) THEN
7356: OPEN get_full_name_cur(l_person_id1, l_termination_date1);
7357: FETCH get_full_name_cur INTO l_full_name1;
7370:
7371: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7372: END IF;
7373:
7374: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_liq_trans');
7375:
7376: psp_enc_liq_tran.enc_liq_trans(
7377: errbuf => l_errbuf,
7378: retcode => l_retcode,
7383: p_set_of_books_id => p_set_of_books_id);
7384: IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
7385: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7386: END IF;
7387: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_liq_trans');
7388: end loop;
7389:
7390: OPEN enc_control_cur;
7391: FETCH enc_control_cur BULK COLLECT INTO t_enc_control_id;
7390: OPEN enc_control_cur;
7391: FETCH enc_control_cur BULK COLLECT INTO t_enc_control_id;
7392: CLOSE enc_control_cur;
7393:
7394: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' t_enc_control_id.COUNT: ' || t_enc_control_id.COUNT);
7395:
7396: FORALL recno IN 1..t_enc_control_id.COUNT
7397: UPDATE psp_enc_controls
7398: SET number_of_dr = (SELECT number_of_dr - COUNT(1)
7448: AND person_id = p_person_id
7449: AND encumbrance_date > p_actual_term_date)
7450: WHERE enc_control_id = t_enc_control_id(recno);
7451:
7452: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated number_of_dr, number_of_cr, total_dr_amount, total_cr_amount, gl_dr_amount, gl_cr_amount, ogm_dr_amount, ogm_cr_cmount in psp_enc_controls');
7453:
7454: DELETE psp_enc_lines
7455: WHERE person_id = p_person_id
7456: AND encumbrance_date > p_actual_term_date;
7454: DELETE psp_enc_lines
7455: WHERE person_id = p_person_id
7456: AND encumbrance_date > p_actual_term_date;
7457:
7458: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted in psp_enc_lines');
7459:
7460: IF (inprogress_controls_cur%ROWCOUNT = 0) THEN
7461: open get_enc_hist_lines_cur;
7462: loop
7464: if get_enc_hist_lines_cur%notfound then
7465: close get_enc_hist_lines_cur;
7466: exit;
7467: end if;
7468: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_liq_trans');
7469:
7470: psp_enc_liq_tran.enc_liq_trans(
7471: errbuf => l_errbuf,
7472: retcode => l_retcode,
7477: p_set_of_books_id => p_set_of_books_id);
7478: IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
7479: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7480: END IF;
7481: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_liq_trans');
7482: END LOOP;
7483: END IF;
7484:
7485: CLOSE inprogress_controls_cur;
7484:
7485: CLOSE inprogress_controls_cur;
7486:
7487: retcode := 0;
7488: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving LIQUIDATE_EMP_TERM');
7489: exception
7490: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7491: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','LIQUIDATE_EMP_TERM');
7492: fnd_msg_pub.add;
7489: exception
7490: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7491: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','LIQUIDATE_EMP_TERM');
7492: fnd_msg_pub.add;
7493: psp_message_s.print_error(p_mode => FND_FILE.LOG,
7494: p_print_header => FND_API.G_TRUE);
7495: ROLLBACK;
7496: retcode := 2;
7497:
7497:
7498: when others then
7499: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','LIQUIDATE_EMP_TERM');
7500: fnd_msg_pub.add;
7501: psp_message_s.print_error(p_mode => FND_FILE.LOG,
7502: p_print_header => FND_API.G_TRUE);
7503: ROLLBACK;
7504: errbuf := sqlerrm;
7505: retcode := 2;