[Home] [Help]
47: l_return_status VARCHAR2(10);
48: l_msg_count NUMBER;
49: l_msg_data VARCHAR2(2000);
50: BEGIN
51: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering ENC_LIQ_TRANS
52: p_payroll_id: ' || p_payroll_id || '
53: p_action_type: ' || p_action_type || '
54: p_business_group_id: ' || p_business_group_id || '
55: p_set_of_books_id: ' || p_set_of_books_id);
55: p_set_of_books_id: ' || p_set_of_books_id);
56: g_error_api_path := '';
57: fnd_msg_pub.initialize;
58: psp_general.TRANSACTION_CHANGE_PURGEBLE; -- 2431917
59: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' PA Transaction Purgeble Flag set');
60: g_bg_id := p_business_group_id;
61: g_sob_id := p_set_of_books_id;
62: / *Introduced g_payroll_id global initializations for Restart Update/Quick Update Encumbrance Lines Enh.* /
63: g_payroll_id:=p_payroll_id;
67: g_gl_run := FALSE;
68: g_liq_has_failed_transactions := FALSE;
69: g_request_id := fnd_global.conc_request_id;
70:
71: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_bg_id: ' || g_bg_id || '
72: g_sob_id: ' || g_sob_id || '
73: g_payroll_id: ' || g_payroll_id || '
74: g_currency_code: ' || g_currency_code || '
75: g_control_rec_found: TRUE
85: g_dff_grouping_option: ' || g_dff_grouping_option || '
86: g_request_id: ' || g_request_id || '
87: g_gl_run: FALSE
88: g_g_liq_has_failed_transactions: FALSE');
89: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_begin');
90:
91: enc_batch_begin(p_payroll_action_id, p_payroll_id, p_action_type, l_return_status);
92:
93: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
92:
93: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
94: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95: END IF;
96: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_begin');
97: / ******* No control record found. Exiting from the program successfully ******* /
98: IF g_control_rec_found = 'FALSE'
99: THEN
100: --insert into psp_Stout values(99, 'no control rec found');
103: retcode := FND_API.G_RET_STS_SUCCESS;
104:
105: -- Added code for error message handler by Bijoy , 27-Jul-1999
106:
107: / *psp_message_s.print_error(p_mode => FND_FILE.LOG,
108: p_print_header => FND_API.G_TRUE);* /
109: if p_action_type IN ('L', 'T') then
110: PSP_MESSAGE_S.Print_success;
111: end if;
116: / * Added for Position Control Integration Enh 2505778 * /
117:
118: IF p_action_type in ('U','Q') then
119: if fnd_profile.value('PSP_ENC_ENABLE_PQH') ='Y' then
120: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling pqh_psp_integration.relieve_budget_commitments');
121: pqh_psp_integration.relieve_budget_commitments('L', l_return_status);
122: If l_return_status <>FND_API.G_RET_STS_SUCCESS THEN
123: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
124: fnd_msg_pub.add;
125: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126:
127:
128: end if;
129: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After pqh_psp_integration.relieve_budget_commitments');
130: end if;
131: end if;
132:
133:
132:
133:
134:
135:
136: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_gl_enc_liq_lines');
137: -- FIRST NORMAL RUN
138: -- initiate the gl encumbrance summarization and transfer
139: create_gl_enc_liq_lines(p_payroll_id,
140: p_action_type,
144: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
145: --insert into psp_stout values(11,'unex error in gl_enc');
146: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147: END IF;
148: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_gl_enc_liq_lines');
149: l_phase_status := 11 ; / * gl enc liq lines created in ist run * /
150: -- insert into psp_stout values( 'l_phase '||l_phase_status );
151: -- insert into psp_stout values( 11 ,l_phase_status );
152:
152:
153: -- call the user extension to populate attribute1 through attribute30
154: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
155: -- 2968684 added params to following proc
156: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
157: psp_st_ext.summary_ext_encumber_liq(p_payroll_id,
158: p_action_type ,
159: p_business_group_id,
160: p_set_of_books_id);
157: psp_st_ext.summary_ext_encumber_liq(p_payroll_id,
158: p_action_type ,
159: p_business_group_id,
160: p_set_of_books_id);
161: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
162: END IF;
163:
164: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
165: tr_to_gl_int( p_payroll_id,
160: p_set_of_books_id);
161: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
162: END IF;
163:
164: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
165: tr_to_gl_int( p_payroll_id,
166: p_action_type, -- Added for Restart Update Enh.
167: l_return_status
168: );
169:
170: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
171: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172: END IF;
173: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int
174: Calling create_gms_enc_liq_lines');
175:
176:
177: l_phase_status := 12 ; / * gl transfer success in ist run * /
185:
186: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
187: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
188: END IF;
189: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_gms_enc_liq_lines');
190:
191: l_phase_status := 13 ; / * gms enc liq lines created in ist run * /
192: --insert_into_psp_stout( 'l_phase '||l_phase_status );
193: -- insert into psp_stout values( 13 ,l_phase_status );
194:
195: -- call the user extension to populate attribute1 through attribute30
196: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
197: -- 2968684 added params to following proc
198: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
199: psp_st_ext.summary_ext_encumber_liq(p_payroll_id,
200: p_action_type ,
201: p_business_group_id,
202: p_set_of_books_id);
199: psp_st_ext.summary_ext_encumber_liq(p_payroll_id,
200: p_action_type ,
201: p_business_group_id,
202: p_set_of_books_id);
203: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
204: END IF;
205:
206: -- if (G_GMS_AVAILABLE) then commented it out as tr_to_gl may still be required even
207: -- if no records are created in craete_gms_enc
205:
206: -- if (G_GMS_AVAILABLE) then commented it out as tr_to_gl may still be required even
207: -- if no records are created in craete_gms_enc
208:
209: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
210: tr_to_gms_int( p_payroll_id,
211: p_action_type, -- Added for Restart Update Enh.
212: l_return_status
213: );
216: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
217: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218: END IF;
219: -- end if;
220: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
221:
222: --G_GMS_AVAILABLE := FALSE; Commented for bug fix 4625734 as its no longer used in the process
223:
224: l_phase_status := 14 ; / * gms transfer success ist run * /
227: -- insert into psp_stout values( 14 ,l_phase_status );
228:
229: -- Introduced the following for Enh. Removal of Suspense Posting in Liq.
230: IF (g_gl_run) THEN
231: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
232: tr_to_gl_int (p_payroll_id,
233: p_action_type,
234: l_return_status);
235:
235:
236: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
237: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
238: END IF;
239: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
240:
241: l_phase_status := 16 ; / * gl transfer success in 2nd run * /
242: END IF;
243:
319: -- insert into psp_stout values( 18 ,l_phase_status );
320:
321: End of comment for Enh. Removal of Suspense posting in Liq ***** /
322:
323: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
324: enc_batch_end( p_payroll_action_id,
325: p_payroll_id,
326: p_Action_type,
327: 'N', -- added param Bug 2039196
332:
333: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
334: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
335: END IF;
336: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
337: -- retcode := FND_API.G_RET_STS_SUCCESS;
338: -- Replaced default successful return status with warning / success based on failed transactions check
339: IF (g_liq_has_failed_transactions) THEN
340: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
336: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
337: -- retcode := FND_API.G_RET_STS_SUCCESS;
338: -- Replaced default successful return status with warning / success based on failed transactions check
339: IF (g_liq_has_failed_transactions) THEN
340: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
341: retcode := fnd_api.g_ret_sts_error;
342: fnd_message.set_name('PSP','PSP_ENC_LIQ_TRANS_FAILED');
343: fnd_msg_pub.add;
344: psp_message_s.print_error(p_mode => FND_FILE.LOG,
340: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
341: retcode := fnd_api.g_ret_sts_error;
342: fnd_message.set_name('PSP','PSP_ENC_LIQ_TRANS_FAILED');
343: fnd_msg_pub.add;
344: psp_message_s.print_error(p_mode => FND_FILE.LOG,
345: p_print_header => FND_API.G_TRUE);
346: ELSE
347: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
348: retcode := FND_API.G_RET_STS_SUCCESS;
343: fnd_msg_pub.add;
344: psp_message_s.print_error(p_mode => FND_FILE.LOG,
345: p_print_header => FND_API.G_TRUE);
346: ELSE
347: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
348: retcode := FND_API.G_RET_STS_SUCCESS;
349: END IF;
350: -- End of changes for Enh. removal of suspense posting in liquidation.
351:
348: retcode := FND_API.G_RET_STS_SUCCESS;
349: END IF;
350: -- End of changes for Enh. removal of suspense posting in liquidation.
351:
352: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
353: -- Added code for error message handler by Bijoy , 27-Jul-1999
354:
355: / *psp_message_s.print_error(p_mode => FND_FILE.LOG,
356: p_print_header => FND_API.G_TRUE);* /
351:
352: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
353: -- Added code for error message handler by Bijoy , 27-Jul-1999
354:
355: / *psp_message_s.print_error(p_mode => FND_FILE.LOG,
356: p_print_header => FND_API.G_TRUE);* /
357: if p_action_type ='L' then
358: PSP_MESSAGE_S.Print_success;
359: end if;
375: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
376: retcode := 2;
377: -- Added code for error message handler by Bijoy , 27-Jul-1999
378:
379: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
380: psp_message_s.print_error(p_mode => FND_FILE.LOG,
381: p_print_header => FND_API.G_TRUE);
382: return;
383: WHEN OTHERS THEN
376: retcode := 2;
377: -- Added code for error message handler by Bijoy , 27-Jul-1999
378:
379: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
380: psp_message_s.print_error(p_mode => FND_FILE.LOG,
381: p_print_header => FND_API.G_TRUE);
382: return;
383: WHEN OTHERS THEN
384: / * Bug 20393936: commented call to batch_end proc and introduced ROLLBACK * /
397: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
398: retcode := 2;
399: -- Added code for error message handler by Bijoy , 27-Jul-1999
400:
401: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
402: psp_message_s.print_error(p_mode => FND_FILE.LOG,
403: p_print_header => FND_API.G_TRUE);
404: return;
405: END enc_liq_trans;
398: retcode := 2;
399: -- Added code for error message handler by Bijoy , 27-Jul-1999
400:
401: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
402: psp_message_s.print_error(p_mode => FND_FILE.LOG,
403: p_print_header => FND_API.G_TRUE);
404: return;
405: END enc_liq_trans;
406: End of comment for create and update multi thread enh. *****/
413: l_return_status VARCHAR2(10);
414: l_msg_count NUMBER;
415: l_msg_data VARCHAR2(2000);
416: BEGIN
417: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering LIQ_TRANS
418: p_payroll_action_id: ' || p_payroll_action_id || '
419: p_business_group_id: ' || p_business_group_id || '
420: p_set_of_books_id: ' || p_set_of_books_id);
421: g_error_api_path := '';
428: g_request_id := fnd_global.conc_request_id;
429: g_currency_code := psp_general.get_currency_code(p_business_group_id);
430: g_dff_grouping_option := psp_general.get_enc_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
431:
432: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' PA Transaction Purgeble Flag set');
433:
434: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_control_rec_found: TRUE
435: g_person_id: ' || g_person_id || '
436: g_bg_id: ' || g_bg_id || '
430: g_dff_grouping_option := psp_general.get_enc_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
431:
432: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' PA Transaction Purgeble Flag set');
433:
434: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_control_rec_found: TRUE
435: g_person_id: ' || g_person_id || '
436: g_bg_id: ' || g_bg_id || '
437: g_sob_id: ' || g_sob_id || '
438: g_term_period_id: ' || g_term_period_id || '
445: g_request_id: ' || g_request_id || '
446: g_gl_run: FALSE
447: g_g_liq_has_failed_transactions: FALSE');
448:
449: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_begin');
450: enc_batch_begin(p_payroll_action_id, l_return_status);
451: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
452: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
453: END IF;
450: enc_batch_begin(p_payroll_action_id, l_return_status);
451: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
452: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
453: END IF;
454: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_begin');
455:
456: IF (g_control_rec_found = 'FALSE') THEN
457: fnd_message.set_name('PSP','PSP_ENC_NO_LIQ_REC_FOUND');
458: fnd_msg_pub.add;
460: RETURN;
461: END IF;
462:
463: IF (fnd_profile.value('PSP_ENC_ENABLE_PQH') ='Y') THEN
464: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling pqh_psp_integration.relieve_budget_commitments');
465: pqh_psp_integration.relieve_budget_commitments('L', l_return_status);
466:
467: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
468: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
468: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
469: fnd_msg_pub.add;
470: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
471: END IF;
472: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After pqh_psp_integration.relieve_budget_commitments');
473: END IF;
474:
475: IF fnd_profile.value('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
476: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
472: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After pqh_psp_integration.relieve_budget_commitments');
473: END IF;
474:
475: IF fnd_profile.value('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
476: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
477: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
478: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
479: END IF;
480:
474:
475: IF fnd_profile.value('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
476: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
477: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
478: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
479: END IF;
480:
481: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
482: tr_to_gl_int(p_payroll_action_id, l_return_status);
477: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
478: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
479: END IF;
480:
481: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
482: tr_to_gl_int(p_payroll_action_id, l_return_status);
483:
484: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
485: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
483:
484: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
485: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
486: END IF;
487: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
488:
489: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
490: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
491: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
486: END IF;
487: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
488:
489: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
490: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
491: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
492: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
493: END IF;
494:
488:
489: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
490: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber_liq');
491: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
492: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
493: END IF;
494:
495: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
496: tr_to_gms_int(p_payroll_action_id, l_return_status);
491: psp_st_ext.summary_ext_encumber_liq(p_payroll_action_id, 'U', p_business_group_id, p_set_of_books_id);
492: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber_liq');
493: END IF;
494:
495: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
496: tr_to_gms_int(p_payroll_action_id, l_return_status);
497:
498: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
499: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497:
498: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
499: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
500: END IF;
501: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
502:
503: IF (g_gl_run) THEN
504: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
505: tr_to_gl_int (p_payroll_action_id, l_return_status);
500: END IF;
501: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
502:
503: IF (g_gl_run) THEN
504: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
505: tr_to_gl_int (p_payroll_action_id, l_return_status);
506:
507: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
508: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
506:
507: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
508: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
509: END IF;
510: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
511: END IF;
512:
513: IF (g_liq_has_failed_transactions) THEN
514: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
510: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
511: END IF;
512:
513: IF (g_liq_has_failed_transactions) THEN
514: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
515: fnd_message.set_name('PSP','PSP_ENC_LIQ_TRANS_FAILED');
516: fnd_msg_pub.add;
517: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
518: p_return_status := fnd_api.g_ret_sts_error;
513: IF (g_liq_has_failed_transactions) THEN
514: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
515: fnd_message.set_name('PSP','PSP_ENC_LIQ_TRANS_FAILED');
516: fnd_msg_pub.add;
517: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
518: p_return_status := fnd_api.g_ret_sts_error;
519: ELSE
520: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
521: p_return_status := FND_API.G_RET_STS_SUCCESS;
516: fnd_msg_pub.add;
517: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
518: p_return_status := fnd_api.g_ret_sts_error;
519: ELSE
520: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
521: p_return_status := FND_API.G_RET_STS_SUCCESS;
522: END IF;
523:
524: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
520: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: FALSE');
521: p_return_status := FND_API.G_RET_STS_SUCCESS;
522: END IF;
523:
524: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
525: enc_batch_end(p_payroll_action_id, p_business_group_id, p_set_of_books_id, l_return_status);
526: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
527: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
528: END IF;
525: enc_batch_end(p_payroll_action_id, p_business_group_id, p_set_of_books_id, l_return_status);
526: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
527: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
528: END IF;
529: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
530: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving LIQ_TRANS');
531: EXCEPTION
532: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
526: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
527: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
528: END IF;
529: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
530: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving LIQ_TRANS');
531: EXCEPTION
532: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
534: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
531: EXCEPTION
532: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
534: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
535: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
536: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
537: p_return_status := fnd_api.g_ret_sts_unexp_error;
538: WHEN OTHERS THEN
539: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
532: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
534: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
535: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
536: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
537: p_return_status := fnd_api.g_ret_sts_unexp_error;
538: WHEN OTHERS THEN
539: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
540: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
537: p_return_status := fnd_api.g_ret_sts_unexp_error;
538: WHEN OTHERS THEN
539: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
540: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
541: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
542: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
543: p_return_status := fnd_api.g_ret_sts_unexp_error;
544: END enc_liq_trans;
545: -- End of changes for Create and update multi thread enh.
538: WHEN OTHERS THEN
539: g_error_api_path := 'ENC_LIQ_TRANS:'||g_error_api_path;
540: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','ENC_LIQ_TRANS');
541: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_LIQ_TRANS');
542: psp_message_s.print_error(p_mode => FND_FILE.LOG, p_print_header => FND_API.G_TRUE);
543: p_return_status := fnd_api.g_ret_sts_unexp_error;
544: END enc_liq_trans;
545: -- End of changes for Create and update multi thread enh.
546: -- #######################################################################
901: enc_control_rec2 enc_control_cur2%ROWTYPE;
902: enc_control_rec3 enc_control_cur3%ROWTYPE; --- added for 3413373
903: End of comment for bug fix 4625734 *****/
904: BEGIN
905: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering ENC_BATCH_BEGIN');
906: /***** Commented the following for bug fix 4625734
907: if g_person_id is null then --- 3413373
908: / * Added for bug 2259310 * /
909: OPEN max_period_cur;
1114: END IF;
1115: END IF;
1116: End of comment for Create and Update multi threading enh. ****/
1117:
1118: fnd_file.put_line(fnd_file.output, 'Liquidation Batch Processing, Started at: ' ||
1119: fnd_date.date_to_canonical(SYSDATE) || '
1120: Run ID: ' || g_run_id || '
1121: Payroll Action Id: ' || p_payroll_action_id || ' Last payroll run date: ' || l_payroll_run_date || '
1122:
1120: Run ID: ' || g_run_id || '
1121: Payroll Action Id: ' || p_payroll_action_id || ' Last payroll run date: ' || l_payroll_run_date || '
1122:
1123: Encumbrance control record(s) status prior to liquidation processing');
1124: fnd_file.put_line(fnd_file.output, LPAD('Run ID', 15, ' ') || ' ' ||
1125: RPAD('Action Code', 15, ' ') || ' ' ||
1126: RPAD('Action Type', 15, ' ') || ' ' ||
1127: RPAD('GL Phase', 15, ' ') || ' ' ||
1128: RPAD('GMS Phase', 15, ' ') || ' ' ||
1141: LPAD('Summary GMS Cr', 15, ' ') || ' ' ||
1142: LPAD('Control Records', 15, ' ') || ' ' ||
1143: LPAD('Min Time Period', 15, ' ') || ' ' ||
1144: LPAD('Max Time Period', 15, ' '));
1145: fnd_file.put_line(fnd_file.output, LPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1146: RPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1147: RPAD('-', 23, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1148: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1149: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1151: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1152: LPAD('-', 15, '-'));
1153: FOR recno IN 1..r_enc_controls.run_id.COUNT
1154: LOOP
1155: fnd_file.put_line(fnd_file.output, LPAD(r_enc_controls.run_id(recno), 15, ' ') || ' ' ||
1156: RPAD(r_enc_controls.action_code(recno), 15, ' ') || ' ' ||
1157: RPAD(r_enc_controls.action_type(recno), 15, ' ') || ' ' ||
1158: RPAD(r_enc_controls.gl_phase(recno), 15, ' ') || ' ' ||
1159: RPAD(r_enc_controls.gms_phase(recno), 15, ' ') || ' ' ||
1183: r_summary_lines.dr_cr_flag, r_summary_lines.status_code,
1184: r_summary_lines.summary_amount, r_summary_lines.summary_lines_count;
1185: CLOSE summary_lines_cur;
1186:
1187: fnd_file.put_line(fnd_file.output, '
1188: Encumbrance summary line(s) status prior to liquidation processing');
1189: fnd_file.put_line(fnd_file.output, RPAD('Employee Name', 50, ' ') || ' ' ||
1190: RPAD('Employee Number', 30, ' ') || ' ' || RPAD('Assignment Number', 30, ' ') || ' ' ||
1191: RPAD('GL Project Flag', 15, ' ') || ' ' || LPAD('Group ID', 15, ' ') || ' ' ||
1185: CLOSE summary_lines_cur;
1186:
1187: fnd_file.put_line(fnd_file.output, '
1188: Encumbrance summary line(s) status prior to liquidation processing');
1189: fnd_file.put_line(fnd_file.output, RPAD('Employee Name', 50, ' ') || ' ' ||
1190: RPAD('Employee Number', 30, ' ') || ' ' || RPAD('Assignment Number', 30, ' ') || ' ' ||
1191: RPAD('GL Project Flag', 15, ' ') || ' ' || LPAD('Group ID', 15, ' ') || ' ' ||
1192: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1193: RPAD('Status Code', 15, ' ') || ' ' || LPAD('Summary Amount', 15, ' ') || ' ' ||
1192: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1193: RPAD('Status Code', 15, ' ') || ' ' || LPAD('Summary Amount', 15, ' ') || ' ' ||
1194: LPAD('Summary Lines', 15, ' '));
1195:
1196: fnd_file.put_line(fnd_file.output, RPAD('-', 50, '-') || ' ' || RPAD('-', 30, '-') || ' ' ||
1197: RPAD('-', 30, '-') || ' ' || RPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1198: RPAD('-', 15, '-') || ' ' || RPAD('-', 10, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1199: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-'));
1200:
1199: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-'));
1200:
1201: FOR recno IN 1..r_summary_lines.person_id.COUNT
1202: LOOP
1203: fnd_file.put_line(fnd_file.output, RPAD(r_summary_lines.full_name(recno), 50, ' ') || ' ' ||
1204: RPAD(r_summary_lines.employee_number(recno), 30, ' ') || ' ' ||
1205: RPAD(r_summary_lines.assignment_number(recno), 30, ' ') || ' ' ||
1206: RPAD(r_summary_lines.gl_project_flag(recno), 15, ' ') || ' ' ||
1207: LPAD(r_summary_lines.group_id(recno), 15, ' ') || ' ' ||
1211: LPAD(r_summary_lines.summary_amount(recno), 15, ' ') || ' ' ||
1212: LPAD(r_summary_lines.summary_lines_count(recno), 15, ' '));
1213: END LOOP;
1214:
1215: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_BATCH_BEGIN');
1216: p_return_status := fnd_api.g_ret_sts_success;
1217: END enc_batch_begin;
1218:
1219: -- ##########################################################################
1360: summary_amount t_number,
1361: summary_lines_count t_number_15);
1362: r_summary_lines r_summary_lines_rec;
1363: BEGIN
1364: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering ENC_BATCH_END');
1365: OPEN enc_control_cur;
1366: FETCH enc_control_cur BULK COLLECT INTO t_enc_control_id;
1367: CLOSE enc_control_cur;
1368:
1379: AND pesl2.enc_control_id = t_enc_control_id(recno)
1380: AND pesl2.superceded_line_id = pesl.enc_summary_line_id))
1381: AND pelh.enc_control_id = t_enc_control_id(recno);
1382:
1383: 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 SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
1384:
1385: FORALL recno IN 1..t_enc_control_id.COUNT
1386: UPDATE psp_enc_summary_lines pesl
1387: SET status_code = 'S'
1391: AND pesl3.enc_control_id = t_enc_control_id(recno)
1392: AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
1393: AND pesl.enc_control_id = t_enc_control_id(recno);
1394:
1395: 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 SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
1396:
1397: FORALL recno IN 1..t_enc_control_id.COUNT
1398: UPDATE psp_enc_summary_lines pesl
1399: SET status_code = 'A'
1408: AND pesl3.enc_control_id = t_enc_control_id(recno)
1409: AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
1410: AND pesl.enc_control_id = t_enc_control_id(recno);
1411:
1412: 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, SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
1413:
1414: FORALL recno IN 1..t_enc_control_id.COUNT
1415: UPDATE psp_enc_lines_history pelh
1416: SET change_flag = 'L'
1419: WHERE pesl.status_code = 'L'
1420: AND pesl.enc_control_id = t_enc_control_id(recno))
1421: AND pelh.enc_control_id = t_enc_control_id(recno);
1422:
1423: 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');
1424:
1425: FORALL recno IN 1..t_enc_control_id.COUNT
1426: UPDATE psp_enc_controls
1427: SET action_code = 'L'
1430: FROM psp_enc_summary_lines pesl
1431: WHERE pesl.enc_control_id = t_enc_control_id(recno)
1432: AND status_code IN ('N','R','A'));
1433:
1434: 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');
1435:
1436: FORALL recno IN 1..t_enc_control_id.COUNT
1437: UPDATE psp_enc_controls
1438: SET action_code = 'P'
1441: FROM psp_enc_summary_lines pesl
1442: WHERE pesl.enc_control_id = t_enc_control_id(recno)
1443: AND status_code = 'A');
1444:
1445: 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');
1446:
1447: UPDATE psp_enc_processes
1448: SET process_phase = 'completed',
1449: process_status = 'P'
1454: WHERE pesl.payroll_action_id = p_payroll_action_id
1455: AND pesl.status_code = 'N');
1456:
1457: IF (SQL%ROWCOUNT> 0) THEN
1458: 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');
1459: END IF;
1460:
1461: UPDATE psp_enc_processes
1462: SET process_phase = 'summarize_transfer'
1468: AND pesl.status_code = 'N'
1469: AND pesl.superceded_line_id IS NULL);
1470:
1471: IF (SQL%ROWCOUNT> 0) THEN
1472: 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');
1473: END IF;
1474:
1475: UPDATE psp_enc_processes
1476: SET process_phase = 'liquidate'
1480: FROM psp_enc_summary_lines pesl
1481: WHERE pesl.payroll_action_id = p_payroll_action_id
1482: AND pesl.status_code = 'N'
1483: AND pesl.superceded_line_id IS NOT NULL);
1484: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to liquidate as liquidation process isn''t complete');
1485:
1486: /***** Commented for Create and Update multi thread enh.
1487: IF p_action_type in ('U','Q') THEN
1488: IF NOT NVL(g_liq_has_failed_transactions, FALSE) THEN
1485:
1486: /***** Commented for Create and Update multi thread enh.
1487: IF p_action_type in ('U','Q') THEN
1488: IF NOT NVL(g_liq_has_failed_transactions, FALSE) THEN
1489: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_enc_update_lines.cleanup_on_success');
1490: psp_enc_update_lines.cleanup_on_success(p_action_type,
1491: p_payroll_id,
1492: p_business_group_id,
1493: p_set_of_books_id,
1493: p_set_of_books_id,
1494: 'N',
1495: p_return_status);
1496: ELSE
1497: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_enc_update_lines.cleanup_on_success');
1498: psp_enc_update_lines.rollback_rejected_asg (p_payroll_id ,
1499: p_action_type,
1500: g_gms_batch_name,
1501: g_accepted_group_id,
1555: r_summary_lines.dr_cr_flag, r_summary_lines.status_code,
1556: r_summary_lines.summary_amount, r_summary_lines.summary_lines_count;
1557: CLOSE summary_lines_cur;
1558:
1559: fnd_file.put_line(fnd_file.output, '
1560: Encumbrance summary line(s) status after liquidation processing');
1561: fnd_file.put_line(fnd_file.output, RPAD('Employee Name', 50, ' ') || ' ' ||
1562: RPAD('Employee Number', 30, ' ') || ' ' || RPAD('Assignment Number', 30, ' ') || ' ' ||
1563: RPAD('GL Project Flag', 15, ' ') || ' ' || LPAD('Group ID', 15, ' ') || ' ' ||
1557: CLOSE summary_lines_cur;
1558:
1559: fnd_file.put_line(fnd_file.output, '
1560: Encumbrance summary line(s) status after liquidation processing');
1561: fnd_file.put_line(fnd_file.output, RPAD('Employee Name', 50, ' ') || ' ' ||
1562: RPAD('Employee Number', 30, ' ') || ' ' || RPAD('Assignment Number', 30, ' ') || ' ' ||
1563: RPAD('GL Project Flag', 15, ' ') || ' ' || LPAD('Group ID', 15, ' ') || ' ' ||
1564: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1565: RPAD('Status Code', 15, ' ') || ' ' || LPAD('Summary Amount', 15, ' ') || ' ' ||
1564: RPAD('GMS Batch Name', 15, ' ') || ' ' || RPAD('Dr/Cr Flag', 10, ' ') || ' ' ||
1565: RPAD('Status Code', 15, ' ') || ' ' || LPAD('Summary Amount', 15, ' ') || ' ' ||
1566: LPAD('Summary Lines', 15, ' '));
1567:
1568: fnd_file.put_line(fnd_file.output, RPAD('-', 50, '-') || ' ' || RPAD('-', 30, '-') || ' ' ||
1569: RPAD('-', 30, '-') || ' ' || RPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1570: RPAD('-', 15, '-') || ' ' || RPAD('-', 10, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1571: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-'));
1572:
1571: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-'));
1572:
1573: FOR recno IN 1..r_summary_lines.person_id.COUNT
1574: LOOP
1575: fnd_file.put_line(fnd_file.output, RPAD(r_summary_lines.full_name(recno), 50, ' ') || ' ' ||
1576: RPAD(r_summary_lines.employee_number(recno), 30, ' ') || ' ' ||
1577: RPAD(r_summary_lines.assignment_number(recno), 30, ' ') || ' ' ||
1578: RPAD(r_summary_lines.gl_project_flag(recno), 15, ' ') || ' ' ||
1579: LPAD(r_summary_lines.group_id(recno), 15, ' ') || ' ' ||
1597: r_enc_controls.run_id, r_enc_controls.gl_phase,
1598: r_enc_controls.gms_phase, r_enc_controls.batch_name;
1599: CLOSE enc_control_status_cur;
1600:
1601: fnd_file.put_line(fnd_file.output, '
1602: Encumbrance control record(s) status after liquidation processing');
1603: fnd_file.put_line(fnd_file.output, LPAD('Run ID', 15, ' ') || ' ' || RPAD('Action Code', 15, ' ') || ' ' ||
1604: RPAD('Action Type', 15, ' ') || ' ' || RPAD('GL Phase', 15, ' ') || ' ' ||
1605: RPAD('GMS Phase', 15, ' ') || ' ' || RPAD('Batch Name', 23, ' ') || ' ' ||
1599: CLOSE enc_control_status_cur;
1600:
1601: fnd_file.put_line(fnd_file.output, '
1602: Encumbrance control record(s) status after liquidation processing');
1603: fnd_file.put_line(fnd_file.output, LPAD('Run ID', 15, ' ') || ' ' || RPAD('Action Code', 15, ' ') || ' ' ||
1604: RPAD('Action Type', 15, ' ') || ' ' || RPAD('GL Phase', 15, ' ') || ' ' ||
1605: RPAD('GMS Phase', 15, ' ') || ' ' || RPAD('Batch Name', 23, ' ') || ' ' ||
1606: LPAD('No of Dr', 15, ' ') || ' ' || LPAD('No of Cr', 15, ' ') || ' ' ||
1607: LPAD('Total Dr', 15, ' ') || ' ' || LPAD('Total Cr', 15, ' ') || ' ' ||
1610: LPAD('Summary GL Dr', 15, ' ') || ' ' || LPAD('Summary GL Cr', 15, ' ') || ' ' ||
1611: LPAD('Summary GMS Dr', 15, ' ') || ' ' || LPAD('Summary GMS Cr', 15, ' ') || ' ' ||
1612: LPAD('Control Records', 15, ' ') || ' ' || LPAD('Min Time Period', 15, ' ') || ' ' ||
1613: LPAD('Max Time Period', 15, ' '));
1614: fnd_file.put_line(fnd_file.output, LPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1615: RPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' || RPAD('-', 15, '-') || ' ' ||
1616: RPAD('-', 23, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1617: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1618: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1620: LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' || LPAD('-', 15, '-') || ' ' ||
1621: LPAD('-', 15, '-'));
1622: FOR recno IN 1..r_enc_controls.run_id.COUNT
1623: LOOP
1624: fnd_file.put_line(fnd_file.output, LPAD(r_enc_controls.run_id(recno), 15, ' ') || ' ' ||
1625: RPAD(r_enc_controls.action_code(recno), 15, ' ') || ' ' ||
1626: RPAD(r_enc_controls.action_type(recno), 15, ' ') || ' ' ||
1627: RPAD(r_enc_controls.gl_phase(recno), 15, ' ') || ' ' ||
1628: RPAD(r_enc_controls.gms_phase(recno), 15, ' ') || ' ' ||
1643: LPAD(r_enc_controls.min_time_period_id(recno), 15, ' ') || ' ' ||
1644: LPAD(r_enc_controls.max_time_period_id(recno), 15, ' '));
1645: END LOOP;
1646:
1647: fnd_file.put_line(fnd_file.output, 'Liquidation Batch Processing completed at: ' || fnd_date.date_to_canonical(SYSDATE));
1648: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_BATCH_END');
1649: END enc_batch_end;
1650:
1651: /***** Commented for bug fix 4625734
1644: LPAD(r_enc_controls.max_time_period_id(recno), 15, ' '));
1645: END LOOP;
1646:
1647: fnd_file.put_line(fnd_file.output, 'Liquidation Batch Processing completed at: ' || fnd_date.date_to_canonical(SYSDATE));
1648: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving ENC_BATCH_END');
1649: END enc_batch_end;
1650:
1651: /***** Commented for bug fix 4625734
1652: PROCEDURE enc_batch_end(p_payroll_id IN NUMBER,
2124:
2125: l_last_updated_by NUMBER(15);
2126: l_last_update_login NUMBER(15);
2127: BEGIN
2128: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering CREATE_GL_ENC_LIQ_LINES');
2129:
2130: l_last_updated_by := fnd_global.user_id;
2131: l_last_update_login := fnd_global.login_id;
2132:
2187: r_liq_lines.attribute9, r_liq_lines.attribute10;
2188: CLOSE enc_qupd_liq_cur;
2189: END IF;
2190:
2191: 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);
2192:
2193: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2194: INSERT INTO psp_enc_summary_lines
2195: (enc_summary_line_id, business_group_id, enc_control_id,
2223: UPDATE psp_enc_summary_lines
2224: SET status_code = 'S'
2225: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
2226:
2227: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
2228:
2229: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2230: UPDATE psp_enc_controls
2231: SET gl_phase = 'Summarize'
2230: UPDATE psp_enc_controls
2231: SET gl_phase = 'Summarize'
2232: WHERE enc_control_id = r_liq_lines.enc_control_id(recno);
2233:
2234: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Summarize'' in psp_enc_controls');
2235:
2236: p_return_status := fnd_api.g_ret_sts_success;
2237:
2238: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2234: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Summarize'' in psp_enc_controls');
2235:
2236: p_return_status := fnd_api.g_ret_sts_success;
2237:
2238: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2239: EXCEPTION
2240: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2241: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2242: p_return_status := fnd_api.g_ret_sts_unexp_error;
2239: EXCEPTION
2240: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2241: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2242: p_return_status := fnd_api.g_ret_sts_unexp_error;
2243: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2244: WHEN OTHERS THEN
2245: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2246: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GL_ENC_LIQ_LINES');
2247: p_return_status := fnd_api.g_ret_sts_unexp_error;
2244: WHEN OTHERS THEN
2245: g_error_api_path := 'CREATE_GL_ENC_LIQ_LINES:'||g_error_api_path;
2246: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GL_ENC_LIQ_LINES');
2247: p_return_status := fnd_api.g_ret_sts_unexp_error;
2248: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GL_ENC_LIQ_LINES');
2249: END create_gl_enc_liq_lines;
2250: -- End of changes for bg fix 4625734
2251:
2252: /***** Commented the following for bug fix 4625734
2736: FROM psp_enc_summary_lines
2737: WHERE group_id = l_group_id;
2738: -- End of changes for bug fix 4507892
2739: BEGIN
2740: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering Transfer to GL Interface');
2741:
2742: l_created_by := fnd_global.user_id;
2743:
2744: -- gl_tie_tab.delete; Commented for bug fix 4625734
2748: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2749: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2750: END IF;
2751:
2752: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_user_je_source: ' || l_user_je_source);
2753:
2754: gl_je_cat( l_user_je_cat,
2755: l_return_status);
2756: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2756: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2757: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2758: END IF;
2759:
2760: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_user_je_cat: ' || l_user_je_cat);
2761:
2762: enc_type( l_enc_type_id,
2763: l_return_status);
2764: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2764: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2765: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2766: END IF;
2767:
2768: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_enc_type_id: ' || l_enc_type_id);
2769:
2770: SELECT gl_interface_control_s.nextval
2771: INTO l_group_id
2772: FROM dual;
2770: SELECT gl_interface_control_s.nextval
2771: INTO l_group_id
2772: FROM dual;
2773:
2774: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_group_id: ' || l_group_id);
2775:
2776: UPDATE psp_enc_summary_lines
2777: SET group_id = l_group_id
2778: WHERE status_code = 'N'
2789: AND pec.business_group_id = g_bg_id
2790: AND pec.set_of_books_id = g_sob_id
2791: AND pec.gl_phase = 'Summarize');*/
2792:
2793: 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');
2794:
2795:
2796: INSERT INTO gl_interface
2797: (status, set_of_books_id, accounting_date,
2847: AND pec.business_group_id = g_bg_id
2848: AND pec.set_of_books_id = g_sob_id
2849: AND pec.gl_phase = 'Summarize');*/
2850:
2851: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Number of records inserted into GL_INTERFACE: ' || SQL%ROWCOUNT);
2852:
2853: /***** Commented the following as part of bug fix 4625734
2854: OPEN enc_control_cur;
2855: LOOP
3022: SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
3023: INTO l_int_run_id
3024: FROM dual;
3025:
3026: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_int_run_id: ' || l_int_run_id);
3027:
3028: insert into gl_interface_control(
3029: je_source_name,
3030: status,
3038: l_group_id,
3039: g_sob_id
3040: );
3041:
3042: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
3043: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Journal Import');
3044:
3045: req_id := fnd_request.submit_request(
3046: 'SQLGL',
3039: g_sob_id
3040: );
3041:
3042: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
3043: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Journal Import');
3044:
3045: req_id := fnd_request.submit_request(
3046: 'SQLGL',
3047: 'GLLEZL',
3057: 'W'); -- Changed 'N' to 'W' for bug fix 2908859
3058: --dbms_output.put_line('Req id = '||to_char(req_id));
3059:
3060: IF req_id = 0 THEN
3061: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import submission failed');
3062: -- insert into psp_stout values(0, 'req did not get submitted');
3063:
3064: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
3065: fnd_msg_pub.add;
3065: fnd_msg_pub.add;
3066: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3067:
3068: ELSE
3069: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submitted Journal Import (req_id: ' || req_id || ')');
3070: -- insert into psp_stout values(94, 'transfer in Gl');
3071: /***** Converted the following UPDATE TO BULK for R12 performance fixes (bug 4507892)
3072: update psp_enc_controls
3073: set gl_phase = 'Transfer'
3079: OPEN enc_control_id_cur;
3080: FETCH enc_control_id_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
3081: CLOSE enc_control_id_cur;
3082:
3083: 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);
3084:
3085: FORALL I IN 1..r_enc_controls.enc_control_id.COUNT
3086: UPDATE psp_enc_controls
3087: SET gl_phase = 'Transfer'
3086: UPDATE psp_enc_controls
3087: SET gl_phase = 'Transfer'
3088: WHERE enc_control_id = r_enc_controls.enc_control_id(I);
3089:
3090: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Transfer'' in psp_enc_controls');
3091:
3092: r_enc_controls.enc_control_id.DELETE;
3093: -- End of Changes for bug fix 4507892
3094:
3094:
3095: --insert into psp_stout values(5, ' request submitted ');
3096: COMMIT;
3097:
3098: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3099: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3100: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3101:
3102: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3096: COMMIT;
3097:
3098: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3099: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3100: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3101:
3102: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3103:
3104: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0,
3098: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3099: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3100: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3101:
3102: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3103:
3104: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0,
3105: rphase, rstatus, dphase, dstatus, message);
3106:
3104: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0,
3105: rphase, rstatus, dphase, dstatus, message);
3106:
3107: IF call_status = FALSE then
3108: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import failed');
3109: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
3110: fnd_msg_pub.add;
3111: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3112: END IF;
3109: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
3110: fnd_msg_pub.add;
3111: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3112: END IF;
3113: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import completed');
3114: END IF;
3115: END IF; -- l_rec_count > 0 moved this from below tie back for 2444657 (checks interface.COUNT bug 4625734)
3116:
3117: /***** Commented the following for bug fix 4625734
3136: OPEN gl_group_id_cur;
3137: FETCH gl_group_id_cur BULK COLLECT INTO r_groups.group_id;
3138: CLOSE gl_group_id_cur;
3139:
3140: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_groups.group_id.COUNT: ' || r_groups.group_id.COUNT);
3141:
3142: FOR recno IN 1..r_groups.group_id.COUNT
3143: LOOP
3144: 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));
3140: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_groups.group_id.COUNT: ' || r_groups.group_id.COUNT);
3141:
3142: FOR recno IN 1..r_groups.group_id.COUNT
3143: LOOP
3144: 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));
3145:
3146: gl_enc_tie_back(NULL, -- Enc Control id isnt reqd as tie back is by each group
3147: NULL, -- Period end date isnt reqd as tie back doesnt post to suspense
3148: r_groups.group_id(recno),
3153: l_return_status);
3154:
3155: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3156: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3157: 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));
3158: ELSE
3159: 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));
3160: END IF;
3161: END LOOP;
3155: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3156: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3157: 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));
3158: ELSE
3159: 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));
3160: END IF;
3161: END LOOP;
3162: -- End of changes for bug fix 4625734
3163:
3192: WHERE group_id = r_groups.group_id(recno)
3193: AND user_je_source_name = l_user_je_source
3194: AND set_of_books_id = g_sob_id;
3195:
3196: 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');
3197: -- End of changes for bug fix 4625734
3198:
3199: COMMIT; -- moved commit below del stmnt for 2479579
3200:
3197: -- End of changes for bug fix 4625734
3198:
3199: COMMIT; -- moved commit below del stmnt for 2479579
3200:
3201: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' COMMITted gl_enc_tie_back');
3202:
3203: /***** commented for Enh. 2768298 Removal of suspense posting in liquidation
3204: if g_invalid_suspense = 'Y' then -- introduced this IF-ELSE for 2479579
3205: enc_batch_end(g_payroll_id,p_action_type,'N',g_bg_id,g_sob_id,l_return_status);
3207: end if;
3208: End of comment for Enh. 2768298 Removal of suspense posting in Liquidation *****/
3209:
3210: p_return_status := fnd_api.g_ret_sts_success;
3211: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
3212: EXCEPTION
3213: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3214: --dbms_output.put_line('Gone to one level top ..................');
3215: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
3213: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3214: --dbms_output.put_line('Gone to one level top ..................');
3215: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
3216: p_return_status := fnd_api.g_ret_sts_unexp_error;
3217: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
3218: /***** Commented for bug fix 4625734
3219: WHEN RETURN_BACK THEN
3220: p_return_status := fnd_api.g_ret_sts_success;
3221: End of comment for bug fix 4625734 *****/
3222: WHEN OTHERS THEN
3223: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
3224: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','TR_TO_GL_INT');
3225: p_return_status := fnd_api.g_ret_sts_unexp_error;
3226: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
3227: END tr_to_gl_int;
3228:
3229: -- ##########################################################################
3230: -- This procedure retrieves the user_je_source_name from gl_je_sources
3565: and group_id = p_group_id
3566: and status = 'NEW';
3567: End of comment for bug fix 4625734 *****/
3568:
3569: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3570:
3571: OPEN int_count_cur;
3572: FETCH int_count_cur INTO l_cnt;
3573: CLOSE int_count_cur;
3571: OPEN int_count_cur;
3572: FETCH int_count_cur INTO l_cnt;
3573: CLOSE int_count_cur;
3574:
3575: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
3576:
3577: if l_cnt = 0 then
3578: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3579: return TRUE;
3574:
3575: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
3576:
3577: if l_cnt = 0 then
3578: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3579: return TRUE;
3580: elsif l_cnt > 0 then
3581:
3582: -- -------------------------------------------------------------------------------------------
3589: where user_je_source_name = 'OLD'
3590: and set_of_books_id = p_set_of_books_id
3591: and group_id = p_group_id;
3592:
3593: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface');
3594:
3595: -- Introduced the following for bug fix 4625734
3596: OPEN superceded_line_id_cur;
3597: FETCH superceded_line_id_cur BULK COLLECT INTO r_superceded_lines.superceded_line_id;
3607: OPEN enc_controls_cur;
3608: FETCH enc_controls_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
3609: CLOSE enc_controls_cur;
3610:
3611: 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);
3612: -- End of changes for bug fix 4625734
3613:
3614: delete from psp_enc_summary_lines
3615: where group_id = p_group_id;
3614: delete from psp_enc_summary_lines
3615: where group_id = p_group_id;
3616: -- and enc_control_id = p_enc_control_id; Commented for bug fix 4625734
3617:
3618: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
3619:
3620: -- Introduced the following for bug fix 4625734
3621: FORALL recno IN 1..r_enc_controls.enc_control_id.COUNT
3622: UPDATE psp_enc_controls pec
3623: SET gl_phase = 'TieBack'
3624: WHERE enc_control_id = r_enc_controls.enc_control_id(recno);
3625: -- End of changes for bug fix 4625734
3626:
3627: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reset gl_phase to ''TieBack''');
3628:
3629: r_enc_controls.enc_control_id.DELETE;
3630: g_liq_has_failed_transactions := TRUE;
3631:
3628:
3629: r_enc_controls.enc_control_id.DELETE;
3630: g_liq_has_failed_transactions := TRUE;
3631:
3632: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3633:
3634: return FALSE;
3635: end if;
3636: exception
3634: return FALSE;
3635: end if;
3636: exception
3637: when others then
3638: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3639: return TRUE;
3640: end PROCESS_COMPLETE;
3641:
3642: BEGIN
3640: end PROCESS_COMPLETE;
3641:
3642: BEGIN
3643: --insert_into_psp_stout( 'gl enc tie back' );
3644: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK procedure');
3645: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_group_id: ' || p_group_id);
3646:
3647: IF (PROCESS_COMPLETE) THEN
3648:
3641:
3642: BEGIN
3643: --insert_into_psp_stout( 'gl enc tie back' );
3644: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK procedure');
3645: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_group_id: ' || p_group_id);
3646:
3647: IF (PROCESS_COMPLETE) THEN
3648:
3649: /***** Commented the following for bug fix 4625734
3658: OPEN int_count_cur;
3659: FETCH int_count_cur INTO l_cnt_gl_interface;
3660: CLOSE int_count_cur;
3661:
3662: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gl_interface: ' || l_cnt_gl_interface);
3663:
3664: IF (l_cnt_gl_interface > 0) THEN
3665: OPEN gl_tie_back_reject_cur;
3666: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3665: OPEN gl_tie_back_reject_cur;
3666: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3667: CLOSE gl_tie_back_reject_cur;
3668:
3669: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.status.COUNT: ' || r_interface.status.COUNT);
3670:
3671: FOR recno IN 1..r_interface.status.COUNT
3672: LOOP
3673: if (r_interface.status(recno) = 'P' OR SUBSTR(r_interface.status(recno), 1, 1) = 'W') and
3675: g_gl_run := TRUE;
3676: end if;
3677: END LOOP;
3678: if g_gl_run then
3679: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_gl_run: TRUE');
3680: end if;
3681:
3682:
3683: FORALL recno IN 1..r_interface.status.COUNT
3690:
3691: IF (SQL%ROWCOUNT > 0) THEN
3692: g_liq_has_failed_transactions := TRUE;
3693: g_rejected_group_id := p_group_id;
3694: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
3695: END IF;
3696:
3697: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_rejected_group_id: ' || g_rejected_group_id);
3698: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3693: g_rejected_group_id := p_group_id;
3694: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
3695: END IF;
3696:
3697: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_rejected_group_id: ' || g_rejected_group_id);
3698: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3699:
3700: FORALL recno IN 1..r_interface.status.COUNT
3701: UPDATE psp_enc_controls
3694: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
3695: END IF;
3696:
3697: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_rejected_group_id: ' || g_rejected_group_id);
3698: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3699:
3700: FORALL recno IN 1..r_interface.status.COUNT
3701: UPDATE psp_enc_controls
3702: SET gl_phase = 'TieBack'
3703: WHERE enc_control_id IN (SELECT pesl.enc_control_id
3704: FROM psp_enc_summary_lines pesl
3705: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3706:
3707: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' TieBack SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3708:
3709: FORALL recno IN 1..r_interface.status.COUNT
3710: UPDATE psp_enc_controls
3711: SET gl_phase = 'Summarize'
3713: FROM psp_enc_summary_lines pesl
3714: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3715: AND pesl.status_code = 'N');
3716:
3717: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Summarize SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3718: ELSIF (l_cnt_gl_interface = 0) THEN
3719: g_accepted_group_id := p_group_id;
3720:
3721: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_accepted_group_id: ' || g_accepted_group_id);
3717: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Summarize SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3718: ELSIF (l_cnt_gl_interface = 0) THEN
3719: g_accepted_group_id := p_group_id;
3720:
3721: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_accepted_group_id: ' || g_accepted_group_id);
3722:
3723: OPEN gl_tie_back_success_cur;
3724: 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;
3725: CLOSE gl_tie_back_success_cur;
3723: OPEN gl_tie_back_success_cur;
3724: 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;
3725: CLOSE gl_tie_back_success_cur;
3726:
3727: 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);
3728:
3729: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3730: UPDATE psp_enc_summary_lines
3731: SET status_code = 'L'
3738: SET change_flag = 'L'
3739: WHERE enc_summary_line_id = (SELECT pesl2.superceded_line_id
3740: FROM psp_enc_summary_lines pesl2
3741: WHERE pesl2.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3742: 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);
3743: END IF;
3744:
3745: FORALL recno IN 1..r_interface.enc_control_id.COUNT
3746: UPDATE psp_enc_controls pec
3747: SET gl_phase = 'TieBack',
3748: summ_gl_dr_amount = NVL(summ_gl_dr_amount, 0) + DECODE(r_interface.dr_cr_flag(recno), 'D', r_interface.summary_amount(recno), 0),
3749: summ_gl_cr_amount = NVL(summ_gl_cr_amount, 0) + DECODE(r_interface.dr_cr_flag(recno), 'C', r_interface.summary_amount(recno), 0)
3750: WHERE enc_control_id = r_interface.enc_control_id(recno);
3751: 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);
3752: END IF;
3753:
3754: /***** Commented the following for bug fix 4625734
3755: IF l_cnt_gl_interface > 0 THEN
4133: --
4134: END IF; -- IF (PROCESS_COMPLETE)
4135:
4136: p_return_status := fnd_api.g_ret_sts_success;
4137: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
4138: EXCEPTION
4139:
4140: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4141: --dbms_output.put_line('Gone to one level top ..................');
4140: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4141: --dbms_output.put_line('Gone to one level top ..................');
4142: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
4143: p_return_status := fnd_api.g_ret_sts_unexp_error;
4144: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
4145: /***** Commented the following as part of bug fix 4625734
4146: / * Added Exceptions for bug 2056877 * /
4147: WHEN NO_PROFILE_EXISTS THEN
4148: g_error_api_path := SUBSTR('GL_ENC_TIE_BACK:'||g_error_api_path,1,230);
4172: WHEN OTHERS THEN
4173: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
4174: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','GL_ENC_TIE_BACK');
4175: p_return_status := fnd_api.g_ret_sts_unexp_error;
4176: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
4177:
4178: END gl_enc_tie_back;
4179:
4180: /***** Commented the following procedure as part of bug fix 4625734
4586:
4587: l_last_updated_by NUMBER(15);
4588: l_last_update_login NUMBER(15);
4589: BEGIN
4590: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering CREATE_GMS_ENC_LIQ_LINES');
4591:
4592: l_last_updated_by := fnd_global.user_id;
4593: l_last_update_login := fnd_global.login_id;
4594:
4592: l_last_updated_by := fnd_global.user_id;
4593: l_last_update_login := fnd_global.login_id;
4594:
4595: IF (g_person_id IS NOT NULL) THEN
4596: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Liquidation for Emplopyee Termination Lines');
4597: OPEN emp_term_enc_liq_cur;
4598: FETCH emp_term_enc_liq_cur BULK COLLECT INTO r_liq_lines.enc_summary_line_id,
4599: r_liq_lines.effective_date, r_liq_lines.enc_control_id,
4600: r_liq_lines.time_period_id, r_liq_lines.set_of_books_id,
4610: r_liq_lines.attribute7, r_liq_lines.attribute8,
4611: r_liq_lines.attribute9, r_liq_lines.attribute10;
4612: CLOSE emp_term_enc_liq_cur;
4613: ELSIF (g_person_id IS NULL AND p_action_type = 'L') THEN
4614: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Regular Liquidation Lines');
4615: OPEN enc_liq_cur;
4616: FETCH enc_liq_cur BULK COLLECT INTO r_liq_lines.enc_summary_line_id,
4617: r_liq_lines.effective_date, r_liq_lines.enc_control_id,
4618: r_liq_lines.time_period_id, r_liq_lines.set_of_books_id,
4628: r_liq_lines.attribute7, r_liq_lines.attribute8,
4629: r_liq_lines.attribute9, r_liq_lines.attribute10;
4630: CLOSE enc_liq_cur;
4631: ELSIF (p_action_type = 'U') THEN
4632: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Update Liquidation Lines');
4633: OPEN enc_upd_liq_cur;
4634: FETCH enc_upd_liq_cur BULK COLLECT INTO r_liq_lines.enc_summary_line_id,
4635: r_liq_lines.effective_date, r_liq_lines.enc_control_id,
4636: r_liq_lines.time_period_id, r_liq_lines.set_of_books_id,
4646: r_liq_lines.attribute7, r_liq_lines.attribute8,
4647: r_liq_lines.attribute9, r_liq_lines.attribute10;
4648: CLOSE enc_upd_liq_cur;
4649: ELSIF (p_action_type = 'Q') THEN
4650: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Fetching Quick Update Liquidation Lines');
4651: OPEN enc_qupd_liq_cur;
4652: FETCH enc_qupd_liq_cur BULK COLLECT INTO r_liq_lines.enc_summary_line_id,
4653: r_liq_lines.effective_date, r_liq_lines.enc_control_id,
4654: r_liq_lines.time_period_id, r_liq_lines.set_of_books_id,
4665: r_liq_lines.attribute9, r_liq_lines.attribute10;
4666: CLOSE enc_qupd_liq_cur;
4667: END IF;
4668:
4669: 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);
4670:
4671: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4672: INSERT INTO psp_enc_summary_lines
4673: (enc_summary_line_id, business_group_id, enc_control_id,
4705: UPDATE psp_enc_summary_lines
4706: SET status_code = 'S'
4707: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
4708:
4709: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
4710:
4711: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4712: UPDATE psp_enc_controls
4713: SET gms_phase = 'Summarize'
4712: UPDATE psp_enc_controls
4713: SET gms_phase = 'Summarize'
4714: WHERE enc_control_id = r_liq_lines.enc_control_id(recno);
4715:
4716: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''Summarize'' in psp_enc_controls');
4717:
4718: p_return_status := fnd_api.g_ret_sts_success;
4719:
4720: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4716: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''Summarize'' in psp_enc_controls');
4717:
4718: p_return_status := fnd_api.g_ret_sts_success;
4719:
4720: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4721: EXCEPTION
4722: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4723: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4724: p_return_status := fnd_api.g_ret_sts_unexp_error;
4721: EXCEPTION
4722: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4723: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4724: p_return_status := fnd_api.g_ret_sts_unexp_error;
4725: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4726: WHEN OTHERS THEN
4727: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4728: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GMS_ENC_LIQ_LINES');
4729: p_return_status := fnd_api.g_ret_sts_unexp_error;
4726: WHEN OTHERS THEN
4727: g_error_api_path := 'CREATE_GMS_ENC_LIQ_LINES:'||g_error_api_path;
4728: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','CREATE_GMS_ENC_LIQ_LINES');
4729: p_return_status := fnd_api.g_ret_sts_unexp_error;
4730: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving CREATE_GMS_ENC_LIQ_LINES');
4731: END create_gms_enc_liq_lines;
4732: -- End of changes for bug fix 4625734
4733:
4734: /***** Commented the following procedure for revamping it using BULK FETCH features for bug fix 4625734
5182: AND gms_batch_name IS NULL
5183: AND payroll_action_id = p_payroll_action_id;
5184:
5185: BEGIN
5186: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering TR_TO_GMS_INT');
5187:
5188: -- gms_tie_tab.delete; Commented for bug fix 4625734
5189:
5190: -- if (gms_install.site_enabled) then -- Commented for bug fix 2908859
5247: LOOP
5248: SELECT to_char(psp_gms_batch_name_s.nextval)
5249: INTO gms_batch_name_tab(i)
5250: FROM dual;
5251: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'gms_batch_name_tab(' || I || '): ' || gms_batch_name_tab(i));
5252: END LOOP;
5253:
5254:
5255: /***** Commented for Create and Update multi thread enh.
5257: FETCH enc_control_cur BULK COLLECT INTO r_enc_control.enc_control_id, r_enc_control.payroll_id,
5258: r_enc_control.time_period_id, r_enc_control.gms_phase, r_enc_control.end_date;
5259: CLOSE enc_control_cur;
5260:
5261: 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);
5262: End of Comment for Create and Update multi thread enh. *****/
5263:
5264: -- FORALL recno IN 1..r_enc_control.enc_control_id.COUNT
5265: FORALL I IN 1..org_id_tab.count
5302: END IF;
5303: END LOOP;
5304: END LOOP;
5305:
5306: 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);
5307: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Verifying interface records for errors');
5308:
5309: l_raise_error := FALSE;
5310: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5303: END LOOP;
5304: END LOOP;
5305:
5306: 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);
5307: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Verifying interface records for errors');
5308:
5309: l_raise_error := FALSE;
5310: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5311: LOOP
5350: END IF;
5351: END LOOP;
5352:
5353: IF l_raise_error THEN
5354: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Interface records have errors');
5355: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5356: END IF;
5357:
5358: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
5354: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Interface records have errors');
5355: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5356: END IF;
5357:
5358: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
5359: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Computing PA week ending date(s)');
5360:
5361: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5362: LOOP
5355: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5356: END IF;
5357:
5358: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
5359: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Computing PA week ending date(s)');
5360:
5361: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5362: LOOP
5363: psp_general.get_gms_effective_date(r_interface.person_id(recno), r_interface.gms_overriding_date(recno));
5367: END LOOP;
5368: -- set the context again to multiple
5369: mo_global.set_policy_context('M', null);
5370:
5371: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed computation of PA week ending date(s)');
5372:
5373: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
5374: UPDATE psp_enc_summary_lines pesl
5375: SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
5375: SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
5376: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
5377: AND TRUNC(r_interface.effective_date(recno)) <> TRUNC(r_interface.gms_overriding_date(recno));
5378:
5379: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated GMS Override Date');
5380:
5381: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
5382: INSERT INTO pa_transaction_interface_all
5383: (txn_interface_id, transaction_source,
5408: r_interface.attribute7(recno), r_interface.attribute8(recno),
5409: r_interface.attribute9(recno), r_interface.attribute10(recno),
5410: g_bg_id);
5411:
5412: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into pa_transaction_interface_all');
5413:
5414: 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);
5415:
5416: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5410: g_bg_id);
5411:
5412: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into pa_transaction_interface_all');
5413:
5414: 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);
5415:
5416: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5417: LOOP
5418:
5448: fnd_msg_pub.add;
5449: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5450: END IF;
5451: END LOOP;
5452: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into gms_transaction_interface_all');
5453:
5454: /***** Commented the following for bug fix 4625734
5455: LOOP
5456: FETCH enc_control_cur INTO enc_control_rec;
5736:
5737: --dbms_output.put_line('Req id = '||to_char(req_id));
5738:
5739: IF req_id_tab(request_counter) = 0 THEN
5740: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submission of Transaction Import Failed');
5741: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5742: fnd_msg_pub.add;
5743: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5744: ELSE
5741: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5742: fnd_msg_pub.add;
5743: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5744: ELSE
5745: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submitted Transaction Import');
5746:
5747: /***** Modified teh following update to BULK UPDATE for R12 performance fixes (bug 4507892)
5748: update psp_enc_controls
5749: set gms_phase = 'Transfer'
5760: UPDATE psp_enc_controls
5761: SET gms_phase = 'Transfer'
5762: WHERE enc_control_id = r_enc_controls.enc_control_id(I);
5763:
5764: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''Transfer'' in psp_enc_controls SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
5765:
5766: r_enc_controls.enc_control_id.DELETE;
5767: -- End of changes for bug fix 4507892
5768: END IF;
5770: COMMIT;
5771: -- set the context again to multiple
5772: mo_global.set_policy_context('M', null);
5773:
5774: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5775: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5776: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5777:
5778: FOR I IN 1..org_id_tab.count
5772: mo_global.set_policy_context('M', null);
5773:
5774: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5775: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5776: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5777:
5778: FOR I IN 1..org_id_tab.count
5779: LOOP
5780: -- insert into psp_Stout values(96, 'gms transfer started');
5777:
5778: FOR I IN 1..org_id_tab.count
5779: LOOP
5780: -- insert into psp_Stout values(96, 'gms transfer started');
5781: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for comlpetion of Transaction Import');
5782: call_status := fnd_concurrent.wait_for_request(req_id_tab(I), 10, 0,
5783: rphase, rstatus, dphase, dstatus, message);
5784:
5785: IF call_status = FALSE then
5782: call_status := fnd_concurrent.wait_for_request(req_id_tab(I), 10, 0,
5783: rphase, rstatus, dphase, dstatus, message);
5784:
5785: IF call_status = FALSE then
5786: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import failed');
5787: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5788: fnd_msg_pub.add;
5789: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5790: END IF;
5787: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5788: fnd_msg_pub.add;
5789: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5790: END IF;
5791: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import completed');
5792: END LOOP;
5793: END IF; --record_count > 0 ...move this line from below delete xface...for 2444657
5794:
5795:
5822: OPEN gms_batch_name_cur;
5823: FETCH gms_batch_name_cur BULK COLLECT INTO r_gms_batch.gms_batch_name;
5824: CLOSE gms_batch_name_cur;
5825:
5826: 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);
5827:
5828: FOR recno IN 1..r_gms_batch.gms_batch_name.COUNT
5829: LOOP
5830: 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));
5826: 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);
5827:
5828: FOR recno IN 1..r_gms_batch.gms_batch_name.COUNT
5829: LOOP
5830: 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));
5831: gms_enc_tie_back(NULL, -- Tieback is by batch and not by enc_control_id
5832: NULL, -- Period end date isnt required as liq doesnt have suspense posting
5833: r_gms_batch.gms_batch_name(recno),
5834: g_bg_id,
5837: NULL, --p_action_type,
5838: l_return_status);
5839:
5840: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5841: 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));
5842: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5843: END IF;
5844: 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));
5845: END LOOP;
5840: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5841: 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));
5842: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5843: END IF;
5844: 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));
5845: END LOOP;
5846: -- End of changes for bug fix 4625734
5847:
5848: /**** Commented the following LOOP to convert it into a proper gms batch loop for bug fix 4625734
6178: AND transaction_Status_code in ('P','I')
6179: GROUP BY transaction_status_code;
6180: -- End of changes for bug fix 4625734
6181: begin
6182: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6183: /*
6184: select count(*), transaction_status_code
6185: into l_cnt, l_status
6186: from pa_transaction_interface_all
6206: l_cnt := 0;
6207: END IF;
6208: CLOSE transaction_status_cur;
6209:
6210: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
6211:
6212: if l_cnt = 0 then
6213: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6214: return TRUE;
6209:
6210: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
6211:
6212: if l_cnt = 0 then
6213: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6214: return TRUE;
6215: elsif l_cnt > 0 then
6216: if l_status = 'P' then
6217:
6223: delete from pa_transaction_interface_all
6224: where transaction_source = 'GOLDE'
6225: and batch_name = p_gms_batch_name;
6226:
6227: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from pa_trancsaction_interface_all');
6228:
6229: delete from gms_transaction_interface_all
6230: where transaction_source = 'GOLDE'
6231: and batch_name = p_gms_batch_name;
6229: delete from gms_transaction_interface_all
6230: where transaction_source = 'GOLDE'
6231: and batch_name = p_gms_batch_name;
6232:
6233: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all');
6234:
6235: /***** Converted the following UPDATE to BULK for R12 performance fixes (bug 4507892)
6236: update psp_enc_summary_lines set status_code ='A' where
6237: enc_summary_line_id in (select superceded_line_id from
6241: OPEN superceded_line_id_cur;
6242: FETCH superceded_line_id_cur BULK COLLECT INTO r_superceded_lines.superceded_line_id;
6243: CLOSE superceded_line_id_cur;
6244:
6245: 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);
6246:
6247: FORALL I IN 1..r_superceded_lines.superceded_line_id.COUNT
6248: UPDATE psp_enc_summary_lines
6249: SET status_code = 'A'
6248: UPDATE psp_enc_summary_lines
6249: SET status_code = 'A'
6250: WHERE enc_summary_line_id = r_superceded_lines.superceded_line_id(I);
6251:
6252: 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');
6253:
6254: r_superceded_lines.superceded_line_id.DELETE;
6255: -- End of changes for bug fix 4507892
6256:
6259: FETCH enc_controls_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
6260: CLOSE enc_controls_cur;
6261: -- End of changes for bug fix 4625734
6262:
6263: 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);
6264:
6265: delete from psp_enc_summary_lines
6266: where gms_batch_name = p_gms_batch_name;
6267: -- and enc_control_id = p_enc_control_id;
6265: delete from psp_enc_summary_lines
6266: where gms_batch_name = p_gms_batch_name;
6267: -- and enc_control_id = p_enc_control_id;
6268:
6269: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
6270:
6271: -- Introduced the folowing for bug fix 4625734
6272: FORALL recno IN 1..r_enc_controls.enc_control_id.COUNT
6273: UPDATE psp_enc_controls pec
6273: UPDATE psp_enc_controls pec
6274: SET gms_phase = 'TieBack'
6275: WHERE enc_control_id = r_enc_controls.enc_control_id(recno);
6276:
6277: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''TieBack'' in psp_enc_controls');
6278:
6279: r_enc_controls.enc_control_id.DELETE;
6280: g_liq_has_failed_transactions := TRUE;
6281: -- End of changes for bug fix 4625734
6279: r_enc_controls.enc_control_id.DELETE;
6280: g_liq_has_failed_transactions := TRUE;
6281: -- End of changes for bug fix 4625734
6282:
6283: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6284: return FALSE;
6285:
6286: elsif l_status = 'I' then
6287:
6290: -- the Post Processing extension. So return 'NOT_COMPLETE' in this case. User needs to complete
6291: -- this process by running the transaction import manually and re-start the LD process.
6292: -- -------------------------------------------------------------------------------------------
6293:
6294: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6295: return FALSE;
6296:
6297: end if;
6298: end if;
6302: return TRUE;
6303: end PROCESS_COMPLETE;
6304:
6305: BEGIN
6306: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GMS_ENC_TIE_BACK');
6307:
6308: if (PROCESS_COMPLETE) then
6309: /***** Changed the following SELECT into CURSOR for bug fix 4625734
6310: SELECT count(*)
6319: OPEN txn_interface_count_cur;
6320: FETCH txn_interface_count_cur INTO l_cnt_gms_interface;
6321: CLOSE txn_interface_count_cur;
6322: -- End of changes for bug fix 4625734
6323: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gms_interface: ' || l_cnt_gms_interface);
6324:
6325: IF l_cnt_gms_interface > 0 THEN
6326: -- Introduced the following for bug fix 4625734
6327: OPEN gms_tie_back_reject_cur;
6328: FETCH gms_tie_back_reject_cur BULK COLLECT INTO r_reject_recs.reason_code, r_reject_recs.enc_summary_line_id,
6329: r_reject_recs.txn_status_code;
6330: CLOSE gms_tie_back_reject_cur;
6331:
6332: 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);
6333:
6334: FOR recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6335: LOOP
6336: 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));
6332: 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);
6333:
6334: FOR recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6335: LOOP
6336: 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));
6337: IF (r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR')) THEN
6338: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
6339: g_liq_has_failed_transactions := TRUE;
6340: EXIT;
6334: FOR recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6335: LOOP
6336: 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));
6337: IF (r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR')) THEN
6338: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' g_liq_has_failed_transactions: TRUE');
6339: g_liq_has_failed_transactions := TRUE;
6340: EXIT;
6341: END IF;
6342: END LOOP;
6346: SET interface_status = r_reject_recs.reason_code(recno)
6347: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6348: AND r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR');
6349:
6350: 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');
6351:
6352: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6353: UPDATE psp_enc_summary_lines
6354: SET interface_status = r_reject_recs.reason_code(recno),
6355: status_code = 'L'
6356: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6357: AND r_reject_recs.txn_status_code(recno) = 'A';
6358:
6359: 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');
6360:
6361: IF (g_person_id IS NOT NULL) THEN
6362: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6363: UPDATE psp_enc_lines_history
6365: WHERE enc_summary_line_id IN (SELECT superceded_line_id
6366: FROM psp_enc_summary_lines pesl
6367: WHERE pesl.enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno))
6368: AND r_reject_recs.txn_status_code(recno) = 'A';
6369: 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');
6370: END IF;
6371:
6372: OPEN get_success_recs_cur;
6373: FETCH get_success_recs_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6373: FETCH get_success_recs_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6374: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6375: CLOSE get_success_recs_cur;
6376:
6377: 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);
6378:
6379: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6380: UPDATE psp_enc_controls pec
6381: SET gms_phase = 'TieBack',
6382: summ_ogm_dr_amount = NVL(pec.summ_ogm_dr_amount, 0) + DECODE(r_success_recs.dr_cr_flag(recno), 'D', r_success_recs.summary_amount(recno), 0),
6383: summ_ogm_cr_amount = NVL(pec.summ_ogm_cr_amount, 0) + DECODE(r_success_recs.dr_cr_flag(recno), 'C', r_success_recs.summary_amount(recno), 0)
6384: WHERE enc_control_id = r_success_recs.enc_control_id(recno);
6385:
6386: 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');
6387:
6388: r_reject_recs.enc_summary_line_id.DELETE;
6389: r_reject_recs.enc_control_id.DELETE;
6390: r_reject_recs.reason_code.DELETE;
6705: FETCH gms_tie_back_success_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6706: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6707: CLOSE gms_tie_back_success_cur;
6708:
6709: 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);
6710:
6711: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6712: UPDATE psp_enc_summary_lines
6713: SET status_code = 'L'
6712: UPDATE psp_enc_summary_lines
6713: SET status_code = 'L'
6714: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6715:
6716: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''L'' in psp_enc_summary_lines');
6717:
6718: IF (g_person_id IS NOT NULL) THEN
6719: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6720: UPDATE psp_enc_lines_history
6721: SET change_flag = 'L'
6722: WHERE enc_summary_line_id IN (SELECT superceded_line_id
6723: FROM psp_enc_summary_lines pesl
6724: WHERE pesl.enc_summary_line_id = r_success_recs.enc_summary_line_id(recno));
6725: 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');
6726: END IF;
6727:
6728: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6729: UPDATE psp_enc_controls pec
6731: summ_ogm_dr_amount = NVL(pec.summ_ogm_dr_amount, 0) + DECODE(r_success_recs.dr_cr_flag(recno), 'D', r_success_recs.summary_amount(recno), 0),
6732: summ_ogm_cr_amount = NVL(pec.summ_ogm_cr_amount, 0) + DECODE(r_success_recs.dr_cr_flag(recno), 'C', r_success_recs.summary_amount(recno), 0)
6733: WHERE enc_control_id = r_success_recs.enc_control_id(recno);
6734:
6735: 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');
6736:
6737: r_success_recs.enc_summary_line_id.DELETE;
6738: r_success_recs.enc_control_id.DELETE;
6739: r_success_recs.reason_code.DELETE;
6808: --
6809: END IF; -- IF (PROCESS_COMPLETE)
6810:
6811: p_return_status := fnd_api.g_ret_sts_success;
6812: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6813: EXCEPTION
6814: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6815: --dbms_output.put_line('Gone to one level top ..................');
6816: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6814: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6815: --dbms_output.put_line('Gone to one level top ..................');
6816: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6817: p_return_status := fnd_api.g_ret_sts_unexp_error;
6818: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6819:
6820: /* Added Exceptions for Bug 2056877 */
6821: /***** Commented the following for bgu fix 4625734
6822: WHEN NO_PROFILE_EXISTS THEN
6848: WHEN OTHERS THEN
6849: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6850: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','GMS_ENC_TIE_BACK');
6851: p_return_status := fnd_api.g_ret_sts_unexp_error;
6852: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6853:
6854: END gms_enc_tie_back;
6855:
6856: /* Commented the procedure below as part of "Zero work days " enhancement Bug 1994421 */
6987: IF p_action_type = 'Q' THEN
6988: fnd_message.set_name('PSP','PSP_ENC_NUM_ASG');
6989: fnd_message.set_token('NUM_ASG',no_of_asg);
6990: fnd_msg_pub.add;
6991: psp_message_s.print_error (p_mode => FND_FILE.LOG,
6992: p_print_header => FND_API.G_FALSE);
6993: END IF;
6994:
6995: p_return_status := FND_API.G_RET_STS_SUCCESS;
7091: FROM per_all_people_f
7092: WHERE person_id = p_person_id
7093: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
7094: begin
7095: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering LIQUIDATE_EMP_TERM');
7096:
7097: g_person_id := p_person_id; --- added for 3413373
7098: g_actual_term_date := p_actual_term_date; --- added for 3413373
7099:
7102: LOOP
7103: FETCH inprogress_controls_cur INTO l_request_id, l_payroll_id, g_term_period_id, l_enc_control_id;
7104: EXIT WHEN inprogress_controls_cur%NOTFOUND;
7105:
7106: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_request_id: ' || l_request_id || '
7107: l_payroll_id: ' || l_payroll_id || '
7108: g_term_period_id: ' || g_term_period_id || '
7109: l_enc_control_id: ' || l_enc_control_id);
7110:
7111: OPEN term_employee_cur;
7112: FETCH term_employee_cur INTO l_person_id1, l_termination_date1;
7113: CLOSE term_employee_cur;
7114:
7115: 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));
7116:
7117: IF ((l_person_id1 <> p_person_id) OR (l_termination_date1 <> p_actual_term_date)) THEN
7118: OPEN get_full_name_cur(l_person_id1, l_termination_date1);
7119: FETCH get_full_name_cur INTO l_full_name1;
7132:
7133: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7134: END IF;
7135:
7136: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_liq_trans');
7137:
7138: psp_enc_liq_tran.enc_liq_trans(
7139: errbuf => l_errbuf,
7140: retcode => l_retcode,
7145: p_set_of_books_id => p_set_of_books_id);
7146: IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
7147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7148: END IF;
7149: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_liq_trans');
7150: end loop;
7151:
7152: OPEN enc_control_cur;
7153: FETCH enc_control_cur BULK COLLECT INTO t_enc_control_id;
7152: OPEN enc_control_cur;
7153: FETCH enc_control_cur BULK COLLECT INTO t_enc_control_id;
7154: CLOSE enc_control_cur;
7155:
7156: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' t_enc_control_id.COUNT: ' || t_enc_control_id.COUNT);
7157:
7158: FORALL recno IN 1..t_enc_control_id.COUNT
7159: UPDATE psp_enc_controls
7160: SET number_of_dr = (SELECT number_of_dr - COUNT(1)
7210: AND person_id = p_person_id
7211: AND encumbrance_date > p_actual_term_date)
7212: WHERE enc_control_id = t_enc_control_id(recno);
7213:
7214: 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');
7215:
7216: DELETE psp_enc_lines
7217: WHERE person_id = p_person_id
7218: AND encumbrance_date > p_actual_term_date;
7216: DELETE psp_enc_lines
7217: WHERE person_id = p_person_id
7218: AND encumbrance_date > p_actual_term_date;
7219:
7220: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted in psp_enc_lines');
7221:
7222: IF (inprogress_controls_cur%ROWCOUNT = 0) THEN
7223: open get_enc_hist_lines_cur;
7224: loop
7226: if get_enc_hist_lines_cur%notfound then
7227: close get_enc_hist_lines_cur;
7228: exit;
7229: end if;
7230: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_liq_trans');
7231:
7232: psp_enc_liq_tran.enc_liq_trans(
7233: errbuf => l_errbuf,
7234: retcode => l_retcode,
7239: p_set_of_books_id => p_set_of_books_id);
7240: IF l_retcode <> FND_API.G_RET_STS_SUCCESS THEN
7241: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7242: END IF;
7243: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_liq_trans');
7244: END LOOP;
7245: END IF;
7246:
7247: CLOSE inprogress_controls_cur;
7246:
7247: CLOSE inprogress_controls_cur;
7248:
7249: retcode := 0;
7250: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving LIQUIDATE_EMP_TERM');
7251: exception
7252: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7253: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','LIQUIDATE_EMP_TERM');
7254: fnd_msg_pub.add;
7251: exception
7252: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7253: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','LIQUIDATE_EMP_TERM');
7254: fnd_msg_pub.add;
7255: psp_message_s.print_error(p_mode => FND_FILE.LOG,
7256: p_print_header => FND_API.G_TRUE);
7257: ROLLBACK;
7258: retcode := 2;
7259:
7259:
7260: when others then
7261: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','LIQUIDATE_EMP_TERM');
7262: fnd_msg_pub.add;
7263: psp_message_s.print_error(p_mode => FND_FILE.LOG,
7264: p_print_header => FND_API.G_TRUE);
7265: ROLLBACK;
7266: errbuf := sqlerrm;
7267: retcode := 2;