[Home] [Help]
166: AND superceded_line_id IS NULL
167: AND award_id IS NOT NULL
168: AND status_code = 'N';
169: BEGIN
170: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering enc_sum_trans p_payroll_action_id: ' || p_payroll_action_id || '
171: p_business_group_id: ' || p_business_group_id || '
172: p_set_of_books_id: ' || p_set_of_books_id);
173:
174: g_error_api_path := '';
178: g_set_of_books_id := p_set_of_books_id;
179: g_payroll_action_id := p_payroll_action_id; /* for Bug 2030232 */
180:
181: g_currency_code := psp_general.get_currency_code(p_business_group_id);-- For bug 2478000 Qubec fix
182: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'calling psp_general.get_currency_precision');
183: psp_general.get_currency_precision(g_currency_code, g_precision, g_ext_precision);
184:
185: g_dff_grouping_option := psp_general.get_enc_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
186: g_suspense_failed := 'FALSE';
223: FROM psp_enc_processes pep2
224: WHERE pep2.payroll_action_id = p_payroll_action_id
225: AND pep2.process_code = 'ST');
226:
227: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted row for process_code ST');
228:
229: UPDATE psp_enc_processes
230: SET process_status = 'S',
231: process_phase = 'completed'
230: SET process_status = 'S',
231: process_phase = 'completed'
232: WHERE payroll_action_id = p_payroll_action_id
233: AND process_code IN ('CEL', 'LET');
234: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated original and current process status in psp_enc_processes');
235:
236: OPEN st_asgs_cur;
237: FETCH st_asgs_cur BULK COLLECT INTO l_asgs.assignment_id, l_asgs.payroll_id;
238: CLOSE st_asgs_cur;
236: OPEN st_asgs_cur;
237: FETCH st_asgs_cur BULK COLLECT INTO l_asgs.assignment_id, l_asgs.payroll_id;
238: CLOSE st_asgs_cur;
239:
240: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' calling move_rej_lines_to_arch ');
241: move_rej_lines_to_arch(p_payroll_action_id);
242:
243: OPEN liq_check_cur;
244: FETCH liq_check_cur INTO l_liq_check;
242:
243: OPEN liq_check_cur;
244: FETCH liq_check_cur INTO l_liq_check;
245: CLOSE liq_check_cur;
246: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_liq_check: '||l_liq_check);
247:
248:
249: IF (l_liq_check > 0) THEN
250: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_enc_liq_tran.enc_liq_trans');
246: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_liq_check: '||l_liq_check);
247:
248:
249: IF (l_liq_check > 0) THEN
250: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_enc_liq_tran.enc_liq_trans');
251: psp_enc_liq_tran.enc_liq_trans(p_payroll_action_id, p_business_group_id, p_set_of_books_id, l_return_status);
252:
253: IF l_return_status NOT IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_success) THEN
254: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252:
253: IF l_return_status NOT IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_success) THEN
254: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255: END IF;
256: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_enc_liq_tran.enc_liq_trans');
257: END IF;
258:
259: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_begin');
260: enc_batch_begin(p_payroll_action_id, l_return_status);
255: END IF;
256: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_enc_liq_tran.enc_liq_trans');
257: END IF;
258:
259: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_begin');
260: enc_batch_begin(p_payroll_action_id, l_return_status);
261:
262: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
263: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261:
262: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
263: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
264: END IF;
265: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_begin');
266:
267: IF g_control_rec_found = 'FALSE' THEN
268: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
269: enc_batch_end(p_payroll_action_id, l_return_status);
264: END IF;
265: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_begin');
266:
267: IF g_control_rec_found = 'FALSE' THEN
268: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
269: enc_batch_end(p_payroll_action_id, l_return_status);
270:
271: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
272: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270:
271: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
272: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273: END IF;
274: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
275: retcode := FND_API.G_RET_STS_SUCCESS;
276:
277: psp_message_s.print_error(p_mode=>FND_FILE.log,
278: p_print_header=>FND_API.G_FALSE);
273: END IF;
274: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
275: retcode := FND_API.G_RET_STS_SUCCESS;
276:
277: psp_message_s.print_error(p_mode=>FND_FILE.log,
278: p_print_header=>FND_API.G_FALSE);
279: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving enc_sum_trans p_payroll_action_id: ' || p_payroll_action_id || '
280: p_business_group_id: ' || p_business_group_id || '
281: p_set_of_books_id: ' || p_set_of_books_id);
275: retcode := FND_API.G_RET_STS_SUCCESS;
276:
277: psp_message_s.print_error(p_mode=>FND_FILE.log,
278: p_print_header=>FND_API.G_FALSE);
279: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving enc_sum_trans p_payroll_action_id: ' || p_payroll_action_id || '
280: p_business_group_id: ' || p_business_group_id || '
281: p_set_of_books_id: ' || p_set_of_books_id);
282: PSP_MESSAGE_S.Print_success;
283: return;
285:
286: -- Enh 2505778 LD, GMS Integration with PQH
287:
288: if fnd_profile.value('PSP_ENC_ENABLE_PQH') ='Y' then
289: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling pqh_psp_integration.relieve_budget_commitments');
290: pqh_psp_integration.relieve_budget_commitments( 'S', l_return_status);
291: If l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
292: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
293: fnd_msg_pub.add;
292: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
293: fnd_msg_pub.add;
294: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295: end if;
296: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After pqh_psp_integration.relieve_budget_commitments');
297: end if;
298:
299: -- FIRST NORMAL RUN
300: -- initiate the gl encumbrance summarization and transfer
300: -- initiate the gl encumbrance summarization and transfer
301: -- call the user extension to populate attribute1 through attribute30
302: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
303: -- 2968684 added params to following proc
304: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
305: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
306: p_business_group_id,
307: p_set_of_books_id);
308: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
304: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
305: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
306: p_business_group_id,
307: p_set_of_books_id);
308: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
309: END IF;
310:
311: OPEN check_gl_lines;
312: FETCH check_gl_lines INTO l_gl_check;
311: OPEN check_gl_lines;
312: FETCH check_gl_lines INTO l_gl_check;
313: CLOSE check_gl_lines;
314:
315: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gl_check: ' || l_gl_check);
316:
317: IF (l_gl_check > 0) THEN
318: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
319: tr_to_gl_int(p_payroll_action_id, l_return_status);
314:
315: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gl_check: ' || l_gl_check);
316:
317: IF (l_gl_check > 0) THEN
318: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
319: tr_to_gl_int(p_payroll_action_id, l_return_status);
320:
321: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
322: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
320:
321: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
322: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
323: END IF;
324: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
325: END IF;
326:
327: -- call the user extension to populate attribute1 through attribute30
328: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
326:
327: -- call the user extension to populate attribute1 through attribute30
328: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
329: -- 2968684 added params to following proc
330: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
331: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
332: p_business_group_id,
333: p_set_of_books_id);
334: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
330: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
331: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
332: p_business_group_id,
333: p_set_of_books_id);
334: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
335: END IF;
336:
337: -- initiate the ogm encumbrance summarization and transfer
338: OPEN check_gms_lines;
337: -- initiate the ogm encumbrance summarization and transfer
338: OPEN check_gms_lines;
339: FETCH check_gms_lines INTO l_gms_check;
340: CLOSE check_gms_lines;
341: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gms_check: ' || l_gms_check);
342:
343: IF (l_gms_check > 0) THEN
344: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
345: tr_to_gms_int(p_payroll_action_id, l_return_status);
340: CLOSE check_gms_lines;
341: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gms_check: ' || l_gms_check);
342:
343: IF (l_gms_check > 0) THEN
344: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
345: tr_to_gms_int(p_payroll_action_id, l_return_status);
346:
347: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
348: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346:
347: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
348: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
349: END IF;
350: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
351: END IF;
352:
353: -- SECOND RUN TO TAKE CARE OF TIE-BACK
354: -- initiate the suspense lines summarization
351: END IF;
352:
353: -- SECOND RUN TO TAKE CARE OF TIE-BACK
354: -- initiate the suspense lines summarization
355: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_sum_lines');
356: create_sum_lines(p_payroll_action_id, l_return_status);
357:
358: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
359: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357:
358: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
359: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
360: END IF;
361: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_sum_lines');
362:
363: -- call the user extension to populate attribute1 through attribute30
364: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
365: -- 2968684 added params to following proc
362:
363: -- call the user extension to populate attribute1 through attribute30
364: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
365: -- 2968684 added params to following proc
366: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
367: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
368: p_business_group_id,
369: p_set_of_books_id);
370: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
366: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
367: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
368: p_business_group_id,
369: p_set_of_books_id);
370: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
371: END IF;
372:
373: OPEN check_gl_lines;
374: FETCH check_gl_lines INTO l_gl_check;
372:
373: OPEN check_gl_lines;
374: FETCH check_gl_lines INTO l_gl_check;
375: CLOSE check_gl_lines;
376: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gl_check: ' || l_gl_check);
377:
378: IF (l_gl_check > 0) THEN
379: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
380: tr_to_gl_int(p_payroll_action_id, l_return_status);
375: CLOSE check_gl_lines;
376: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gl_check: ' || l_gl_check);
377:
378: IF (l_gl_check > 0) THEN
379: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
380: tr_to_gl_int(p_payroll_action_id, l_return_status);
381:
382: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
383: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381:
382: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
383: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
384: END IF;
385: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
386: END IF;
387:
388: -- call the user extension to populate attribute1 through attribute30
389: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
387:
388: -- call the user extension to populate attribute1 through attribute30
389: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
390: -- 2968684 added params to following proc
391: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
392: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
393: p_business_group_id,
394: p_set_of_books_id);
395: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
391: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
392: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
393: p_business_group_id,
394: p_set_of_books_id);
395: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
396: END IF;
397:
398: OPEN check_gms_lines;
399: FETCH check_gms_lines INTO l_gms_check;
397:
398: OPEN check_gms_lines;
399: FETCH check_gms_lines INTO l_gms_check;
400: CLOSE check_gms_lines;
401: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gms_check: ' || l_gms_check);
402:
403: IF (l_gms_check > 0) THEN
404: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
405: tr_to_gms_int(p_payroll_action_id, l_return_status);
400: CLOSE check_gms_lines;
401: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gms_check: ' || l_gms_check);
402:
403: IF (l_gms_check > 0) THEN
404: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
405: tr_to_gms_int(p_payroll_action_id, l_return_status);
406:
407: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
408: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
406:
407: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
408: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
409: END IF;
410: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
411: END IF;
412:
413: g_suspense_failed := 'TRUE';
414: DELETE psp_enc_summary_lines
414: DELETE psp_enc_summary_lines
415: WHERE payroll_action_id = p_payroll_action_id
416: AND superceded_line_id IS NULL
417: AND status_code = 'N';
418: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted un-imported summary lines (' || SQL%ROWCOUNT || ')');
419:
420: UPDATE psp_enc_lines
421: SET suspense_org_account_id = NULL,
422: gl_code_combination_id = orig_gl_code_combination_id,
439: orig_expenditure_type = NULL,
440: suspense_reason_code = NULL
441: WHERE suspense_reason_code like 'ES:%'
442: AND payroll_action_id = p_payroll_action_id;
443: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted un-imported suspense postings posted in this process (' || SQL%ROWCOUNT || ')');
444:
445: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_sum_lines');
446: create_sum_lines(p_payroll_action_id, l_return_status);
447:
441: WHERE suspense_reason_code like 'ES:%'
442: AND payroll_action_id = p_payroll_action_id;
443: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted un-imported suspense postings posted in this process (' || SQL%ROWCOUNT || ')');
444:
445: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_sum_lines');
446: create_sum_lines(p_payroll_action_id, l_return_status);
447:
448: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
449: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
447:
448: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
449: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450: END IF;
451: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_sum_lines');
452:
453: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
454: enc_batch_end(p_payroll_action_id, l_return_status);
455:
449: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450: END IF;
451: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_sum_lines');
452:
453: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
454: enc_batch_end(p_payroll_action_id, l_return_status);
455:
456: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
457: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455:
456: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
457: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458: END IF;
459: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
460: retcode := FND_API.G_RET_STS_SUCCESS;
461:
462: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving enc_sum_trans p_payroll_action_id: ' || p_payroll_action_id || '
463: p_business_group_id: ' || p_business_group_id || '
458: END IF;
459: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
460: retcode := FND_API.G_RET_STS_SUCCESS;
461:
462: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving enc_sum_trans p_payroll_action_id: ' || p_payroll_action_id || '
463: p_business_group_id: ' || p_business_group_id || '
464: p_set_of_books_id: ' || p_set_of_books_id);
465: PSP_MESSAGE_S.Print_success;
466: EXCEPTION
469: g_error_api_path := 'ENC_SUM_TRANS:'||g_error_api_path;
470: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','ENC_SUM_TRANS');
471: retcode := 2;
472:
473: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving enc_sum_trans p_payroll_action_id: ' || p_payroll_action_id || '
474: p_business_group_id: ' || p_business_group_id || '
475: p_set_of_books_id: ' || p_set_of_books_id);
476: psp_message_s.print_error(p_mode => FND_FILE.LOG,
477: p_print_header => FND_API.G_TRUE);
472:
473: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving enc_sum_trans p_payroll_action_id: ' || p_payroll_action_id || '
474: p_business_group_id: ' || p_business_group_id || '
475: p_set_of_books_id: ' || p_set_of_books_id);
476: psp_message_s.print_error(p_mode => FND_FILE.LOG,
477: p_print_header => FND_API.G_TRUE);
478: return;
479:
480: WHEN OTHERS THEN
477: p_print_header => FND_API.G_TRUE);
478: return;
479:
480: WHEN OTHERS THEN
481: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
482: ROLLBACK; --Introduced as part of bug fix 1776606
483: g_error_api_path := 'ENC_SUM_TRANS:'||g_error_api_path;
484: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','ENC_SUM_TRANS');
485: retcode := 2;
483: g_error_api_path := 'ENC_SUM_TRANS:'||g_error_api_path;
484: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','ENC_SUM_TRANS');
485: retcode := 2;
486:
487: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving enc_sum_trans p_payroll_action_id: ' || p_payroll_action_id || '
488: p_business_group_id: ' || p_business_group_id || '
489: p_set_of_books_id: ' || p_set_of_books_id);
490: psp_message_s.print_error(p_mode => FND_FILE.LOG,
491: p_print_header => FND_API.G_TRUE);
486:
487: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving enc_sum_trans p_payroll_action_id: ' || p_payroll_action_id || '
488: p_business_group_id: ' || p_business_group_id || '
489: p_set_of_books_id: ' || p_set_of_books_id);
490: psp_message_s.print_error(p_mode => FND_FILE.LOG,
491: p_print_header => FND_API.G_TRUE);
492: return;
493: END;
494:
519:
520: enc_control_rec enc_control_cur%ROWTYPE;
521: l_bg_id NUMBER := g_business_group_id;
522: BEGIN
523: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'Entering Enc_Batch_begin ');
524: SELECT psp_st_run_id_s.nextval INTO g_run_id FROM dual;
525:
526: OPEN enc_control_cur;
527: LOOP
549: WHERE enc_control_id = enc_control_rec.enc_control_id
550: AND time_period_id = enc_control_rec.time_period_id;
551: END LOOP;
552:
553: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'Leaving Enc_Batch_begin ');
554: EXCEPTION
555: /* Introduced as part of bug fix #1776606 */
556: when others then
557: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
553: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'Leaving Enc_Batch_begin ');
554: EXCEPTION
555: /* Introduced as part of bug fix #1776606 */
556: when others then
557: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
558: g_error_api_path := 'ENC_BATCH_BEGIN:'||g_error_api_path;
559: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','ENC_BATCH_BEGIN');
560: p_return_status := fnd_api.g_ret_sts_unexp_error;
561: RAISE;
603: OPEN check_enc_lines;
604: FETCH check_enc_lines INTO l_enc_lines_cnt;
605: CLOSE check_enc_lines;
606:
607: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_enc_lines_cnt: '||l_enc_lines_cnt);
608:
609: IF (l_enc_lines_cnt = 0) THEN
610: DELETE psp_enc_lines
611: WHERE payroll_action_id = p_payroll_action_id;
609: IF (l_enc_lines_cnt = 0) THEN
610: DELETE psp_enc_lines
611: WHERE payroll_action_id = p_payroll_action_id;
612: END IF;
613: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'Calling move_rej_lines_to_arch');
614: move_rej_lines_to_arch(p_payroll_action_id);
615: END IF;
616:
617: UPDATE psp_enc_controls pec
622: WHERE pesl.payroll_action_id = p_payroll_action_id
623: AND pesl.enc_control_id = pec.enc_control_id
624: AND pesl.status_code = 'N');
625: IF (SQL%ROWCOUNT > 0) THEN
626: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated action_code to ''P'' count'||SQL%ROWCOUNT);
627: END IF;
628:
629: UPDATE psp_enc_processes
630: SET process_status = 'P', process_phase = 'completed'
634: FROM psp_enc_summary_lines pesl
635: WHERE pesl.payroll_action_id = p_payroll_action_id
636: AND pesl.status_code = 'N');
637: IF (SQL%ROWCOUNT > 0) THEN
638: 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');
639: END IF;
640:
641: UPDATE psp_enc_processes
642: SET process_phase = 'summarize_transfer'
645: AND EXISTS (SELECT 1
646: FROM psp_enc_lines pel
647: WHERE pel.payroll_action_id = p_payroll_action_id);
648: IF (SQL%ROWCOUNT > 0) THEN
649: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to summarize and transfer re-processing');
650: END IF;
651:
652: UPDATE psp_enc_processes
653: SET process_phase = 'liquidate'
658: WHERE pesl.payroll_action_id = p_payroll_action_id
659: AND pesl.status_code = 'N'
660: AND pesl.superceded_line_id IS NOT NULL);
661: IF (SQL%ROWCOUNT > 0) THEN
662: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to liquidate for liquidation re-processing');
663: END IF;
664:
665: UPDATE psp_enc_process_assignments pepa
666: SET assignment_status = 'P'
671: AND pesl.assignment_id = pepa.assignment_id
672: AND pesl.payroll_id = pepa.payroll_id
673: AND pesl.status_code = 'N');
674: IF (SQL%ROWCOUNT > 0) THEN
675: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated assignment_status to processed for summarized and transferred assignments count: '||SQL%ROWCOUNT);
676: END IF;
677:
678: UPDATE psp_enc_process_assignments pepa
679: SET assignment_status = 'S'
685: AND pesl.payroll_id = pepa.payroll_id
686: AND pesl.status_code = 'N'
687: AND pesl.superceded_line_id IS NULL);
688: IF (SQL%ROWCOUNT > 0) THEN
689: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated assignment_status to summarize and transfer for assignments to be summarized and transferred count: '||SQL%ROWCOUNT);
690: END IF;
691:
692: UPDATE psp_enc_process_assignments pepa
693: SET assignment_status = 'L'
699: AND pesl.payroll_id = pepa.payroll_id
700: AND pesl.status_code = 'N'
701: AND pesl.superceded_line_id IS NOT NULL);
702: IF (SQL%ROWCOUNT > 0) THEN
703: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated assignment_status to liquidate for assignments to be liquidated count: '||SQL%ROWCOUNT);
704: END IF;
705:
706: COMMIT;
707:
704: END IF;
705:
706: COMMIT;
707:
708: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Committing');
709: OPEN pending_line_cur;
710: FETCH pending_line_cur INTO l_pending_line_count;
711: CLOSE pending_line_cur;
712:
709: OPEN pending_line_cur;
710: FETCH pending_line_cur INTO l_pending_line_count;
711: CLOSE pending_line_cur;
712:
713: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_pending_line_count'||l_pending_line_count);
714:
715: IF (l_pending_line_count > 0) THEN
716: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Need to re-process');
717: RAISE fnd_api.g_exc_unexpected_error;
712:
713: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_pending_line_count'||l_pending_line_count);
714:
715: IF (l_pending_line_count > 0) THEN
716: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Need to re-process');
717: RAISE fnd_api.g_exc_unexpected_error;
718: END IF;
719: p_return_status := fnd_api.g_ret_sts_success;
720: EXCEPTION
718: END IF;
719: p_return_status := fnd_api.g_ret_sts_success;
720: EXCEPTION
721: WHEN OTHERS THEN
722: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
723: g_error_api_path := 'ENC_BATCH_END:'||g_error_api_path;
724: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','ENC_BATCH_END');
725: p_return_status := fnd_api.g_ret_sts_unexp_error;
726: END enc_batch_end;
860: AND NOT EXISTS (SELECT 1
861: FROM psp_enc_summary_lines pesl
862: WHERE pesl.payroll_action_id = p_payroll_action_id
863: AND pesl.status_code = 'N');
864: 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');
865:
866: UPDATE psp_enc_processes
867: SET process_phase = 'summarize_transfer'
868: WHERE payroll_action_id = p_payroll_action_id
870: FROM psp_enc_summary_lines pesl
871: WHERE pesl.payroll_action_id = p_payroll_action_id
872: AND pesl.status_code = 'N'
873: AND pesl.superceded_line_id IS NULL);
874: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to summarize and transfer re-processing');
875:
876: UPDATE psp_enc_processes
877: SET process_phase = 'liquidate'
878: WHERE payroll_action_id = p_payroll_action_id
880: FROM psp_enc_summary_lines pesl
881: WHERE pesl.payroll_action_id = p_payroll_action_id
882: AND pesl.status_code = 'N'
883: AND pesl.superceded_line_id IS NOT NULL);
884: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to liquidate for liqduidation re-processing');
885:
886: COMMIT;
887: p_return_status := fnd_api.g_ret_sts_success;
888: EXCEPTION
1831: SYSDATE);
1832: p_return_status := fnd_api.g_ret_sts_success;
1833: EXCEPTION
1834: WHEN OTHERS THEN
1835: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1836: g_error_api_path := 'INSERT_INTO_ENC_SUM_LINES:'||g_error_api_path;
1837: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','INSERT_INTO_ENC_SUM_LINES');
1838: p_return_status := fnd_api.g_ret_sts_unexp_error;
1839: END;
2357: SELECT enc_control_id
2358: FROM psp_enc_controls
2359: WHERE payroll_action_id = p_payroll_action_id;
2360: BEGIN
2361: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering Transfer to GL Interface');
2362:
2363: l_created_by := fnd_global.user_id;
2364: l_tie_back_failed := NULL;
2365: l_summarization_option := NVL(fnd_profile.value('PSP_ENABLE_ENC_SUMM_GL'),'N');
2370: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2371: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2372: END IF;
2373:
2374: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_user_je_source: ' || l_user_je_source);
2375:
2376: gl_je_cat( l_user_je_cat,
2377: l_return_status);
2378: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2378: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2379: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2380: END IF;
2381:
2382: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_user_je_cat: ' || l_user_je_cat);
2383:
2384: enc_type( l_enc_type_id,
2385: l_return_status);
2386: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2386: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2387: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2388: END IF;
2389:
2390: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_enc_type_id: ' || l_enc_type_id);
2391:
2392: SELECT gl_interface_control_s.nextval
2393: INTO l_group_id
2394: FROM dual;
2392: SELECT gl_interface_control_s.nextval
2393: INTO l_group_id
2394: FROM dual;
2395:
2396: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_group_id: ' || l_group_id);
2397:
2398: UPDATE psp_enc_summary_lines pesl
2399: SET group_id = l_group_id
2400: WHERE status_code = 'N'
2409: AND pesl2.status_code IN ('N', 'R')
2410: AND pesl2.superceded_line_id IS NOT NULL)
2411: AND payroll_action_id = p_payroll_action_id;
2412:
2413: 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 count: '||sql%rowcount);
2414: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'Inserting into gl_interface');
2415: INSERT INTO gl_interface
2416: (status, set_of_books_id, accounting_date,
2417: currency_code, date_created, created_by,
2410: AND pesl2.superceded_line_id IS NOT NULL)
2411: AND payroll_action_id = p_payroll_action_id;
2412:
2413: 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 count: '||sql%rowcount);
2414: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'Inserting into gl_interface');
2415: INSERT INTO gl_interface
2416: (status, set_of_books_id, accounting_date,
2417: currency_code, date_created, created_by,
2418: actual_flag, user_je_category_name, user_je_source_name,
2463: SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
2464: INTO l_int_run_id
2465: FROM DUAL;
2466:
2467: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_int_run_id: ' || l_int_run_id);
2468:
2469: INSERT INTO gl_interface_control
2470: (je_source_name, status, interface_run_id,
2471: group_id, set_of_books_id)
2471: group_id, set_of_books_id)
2472: VALUES (l_user_je_source, 'S', l_int_run_id,
2473: l_group_id, g_set_of_books_id);
2474:
2475: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
2476: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Journal Import');
2477:
2478: req_id := fnd_request.submit_request( 'SQLGL',
2479: 'GLLEZL',
2472: VALUES (l_user_je_source, 'S', l_int_run_id,
2473: l_group_id, g_set_of_books_id);
2474:
2475: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
2476: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Journal Import');
2477:
2478: req_id := fnd_request.submit_request( 'SQLGL',
2479: 'GLLEZL',
2480: '',
2488: l_summarization_option,
2489: 'W');
2490:
2491: IF req_id = 0 THEN
2492: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import submission failed');
2493: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
2494: fnd_msg_pub.add;
2495: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2496: ELSE
2493: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
2494: fnd_msg_pub.add;
2495: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2496: ELSE
2497: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submitted Journal Import (req_id: ' || req_id || ')');
2498: OPEN enc_control_id_cur;
2499: FETCH enc_control_id_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
2500: CLOSE enc_control_id_cur;
2501:
2498: OPEN enc_control_id_cur;
2499: FETCH enc_control_id_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
2500: CLOSE enc_control_id_cur;
2501:
2502: 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);
2503:
2504: FORALL I IN 1..r_enc_controls.enc_control_id.COUNT
2505: UPDATE psp_enc_controls
2506: SET gl_phase = 'Transfer'
2512: loop
2513: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
2514: end loop;
2515:
2516: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Transfer'' in psp_enc_controls count:'||g_bulk_row_count);
2517:
2518: r_enc_controls.enc_control_id.DELETE;
2519:
2520: COMMIT;
2518: r_enc_controls.enc_control_id.DELETE;
2519:
2520: COMMIT;
2521:
2522: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
2523: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
2524: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
2525:
2526: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
2520: COMMIT;
2521:
2522: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
2523: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
2524: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
2525:
2526: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
2527:
2528: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
2522: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
2523: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
2524: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
2525:
2526: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
2527:
2528: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
2529:
2530: IF call_status = FALSE THEN
2527:
2528: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
2529:
2530: IF call_status = FALSE THEN
2531: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import failed');
2532: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
2533: fnd_msg_pub.add;
2534: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2535: END IF;
2532: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
2533: fnd_msg_pub.add;
2534: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2535: END IF;
2536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import completed');
2537: END IF;
2538: END IF;
2539:
2540: OPEN gl_group_id_cur;
2540: OPEN gl_group_id_cur;
2541: FETCH gl_group_id_cur BULK COLLECT INTO r_groups.group_id;
2542: CLOSE gl_group_id_cur;
2543:
2544: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_groups.group_id.COUNT: ' || r_groups.group_id.COUNT);
2545:
2546: FOR recno IN 1..r_groups.group_id.COUNT
2547: LOOP
2548: 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));
2544: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_groups.group_id.COUNT: ' || r_groups.group_id.COUNT);
2545:
2546: FOR recno IN 1..r_groups.group_id.COUNT
2547: LOOP
2548: 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));
2549:
2550: gl_enc_tie_back(p_payroll_action_id,
2551: r_groups.group_id(recno),
2552: g_business_group_id,
2555: l_return_status);
2556:
2557: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2558: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2559: 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));
2560: ELSE
2561: 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));
2562: END IF;
2563: END LOOP;
2557: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2558: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2559: 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));
2560: ELSE
2561: 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));
2562: END IF;
2563: END LOOP;
2564:
2565: FORALL recno IN 1..r_groups.group_id.COUNT
2573: loop
2574: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
2575: end loop;
2576:
2577: 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 count: '||g_bulk_row_count);
2578:
2579: FORALL recno IN 1..r_groups.group_id.COUNT
2580: UPDATE psp_enc_summary_lines
2581: SET group_id = NULL
2587: loop
2588: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
2589: end loop;
2590:
2591: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Set the un-imported summary lines to New status count: '||g_bulk_row_count);
2592:
2593: COMMIT;
2594: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' COMMITted gl_enc_tie_back');
2595:
2590:
2591: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Set the un-imported summary lines to New status count: '||g_bulk_row_count);
2592:
2593: COMMIT;
2594: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' COMMITted gl_enc_tie_back');
2595:
2596: p_return_status := fnd_api.g_ret_sts_success;
2597: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2598: EXCEPTION
2593: COMMIT;
2594: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' COMMITted gl_enc_tie_back');
2595:
2596: p_return_status := fnd_api.g_ret_sts_success;
2597: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2598: EXCEPTION
2599: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2600: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2601: p_return_status := fnd_api.g_ret_sts_unexp_error;
2598: EXCEPTION
2599: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2600: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2601: p_return_status := fnd_api.g_ret_sts_unexp_error;
2602: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2603: WHEN OTHERS THEN
2604: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2605: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2606: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','TR_TO_GL_INT');
2600: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2601: p_return_status := fnd_api.g_ret_sts_unexp_error;
2602: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2603: WHEN OTHERS THEN
2604: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2605: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2606: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','TR_TO_GL_INT');
2607: p_return_status := fnd_api.g_ret_sts_unexp_error;
2608: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2604: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2605: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2606: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','TR_TO_GL_INT');
2607: p_return_status := fnd_api.g_ret_sts_unexp_error;
2608: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2609: END tr_to_gl_int;
2610: -- End of changes for Create and Update multi thread enh.
2611:
2612: -- ##########################################################################
2631: fnd_message.set_token('PRODUCT',l_product);
2632: fnd_msg_pub.add;
2633: p_return_status := fnd_api.g_ret_sts_unexp_error;
2634: WHEN OTHERS THEN
2635: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2636: g_error_api_path := 'gl_je_source:'||g_error_api_path;
2637: fnd_msg_pub.add_exc_msg('psp_enc_sum_tran','gl_je_source');
2638: p_return_status := fnd_api.g_ret_sts_unexp_error;
2639: END;
2660: fnd_message.set_token('PRODUCT',l_product);
2661: fnd_msg_pub.add;
2662: p_return_status := fnd_api.g_ret_sts_unexp_error;
2663: WHEN OTHERS THEN
2664: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2665: g_error_api_path := 'gl_je_cat:'||g_error_api_path;
2666: fnd_msg_pub.add_exc_msg('psp_enc_sum_tran','gl_je_cat');
2667: p_return_status := fnd_api.g_ret_sts_unexp_error;
2668: END;
2692: fnd_message.set_token('PRODUCT',l_product);
2693: fnd_msg_pub.add;
2694: p_return_status := fnd_api.g_ret_sts_unexp_error;
2695: WHEN OTHERS THEN
2696: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2697: g_error_api_path := 'enc_type:'||g_error_api_path;
2698: fnd_msg_pub.add_exc_msg('psp_enc_sum_tran','enc_type');
2699: p_return_status := fnd_api.g_ret_sts_unexp_error;
2700:
3512: SELECT DISTINCT enc_control_id
3513: FROM psp_enc_summary_lines
3514: WHERE group_id = p_group_id;
3515: BEGIN
3516: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3517:
3518: OPEN int_count_cur;
3519: FETCH int_count_cur INTO l_cnt;
3520: CLOSE int_count_cur;
3518: OPEN int_count_cur;
3519: FETCH int_count_cur INTO l_cnt;
3520: CLOSE int_count_cur;
3521:
3522: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
3523:
3524: IF l_cnt = 0 THEN
3525: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3526: RETURN TRUE;
3521:
3522: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
3523:
3524: IF l_cnt = 0 THEN
3525: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3526: RETURN TRUE;
3527: ELSIF l_cnt > 0 THEN
3528: DELETE FROM gl_interface
3529: WHERE user_je_source_name = 'OLD'
3528: DELETE FROM gl_interface
3529: WHERE user_je_source_name = 'OLD'
3530: AND set_of_books_id = p_set_of_books_id
3531: AND group_id = p_group_id;
3532: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface count: '||sql%rowcount);
3533:
3534: DELETE FROM psp_enc_summary_lines
3535: WHERE group_id = p_group_id;
3536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines '||sql%rowcount);
3532: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface count: '||sql%rowcount);
3533:
3534: DELETE FROM psp_enc_summary_lines
3535: WHERE group_id = p_group_id;
3536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines '||sql%rowcount);
3537: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3538: RETURN FALSE;
3539: END IF;
3540:
3533:
3534: DELETE FROM psp_enc_summary_lines
3535: WHERE group_id = p_group_id;
3536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines '||sql%rowcount);
3537: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3538: RETURN FALSE;
3539: END IF;
3540:
3541: END PROCESS_COMPLETE;
3540:
3541: END PROCESS_COMPLETE;
3542:
3543: BEGIN
3544: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK procedure');
3545: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_group_id: ' || p_group_id);
3546: l_suspense_ac_failed := 'N';
3547: l_susp_ac_found := 'TRUE';
3548: l_susp_exception := NULL;
3541: END PROCESS_COMPLETE;
3542:
3543: BEGIN
3544: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK procedure');
3545: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_group_id: ' || p_group_id);
3546: l_suspense_ac_failed := 'N';
3547: l_susp_ac_found := 'TRUE';
3548: l_susp_exception := NULL;
3549: l_suspense_ac_not_found := 'N';
3552: OPEN int_count_cur;
3553: FETCH int_count_cur INTO l_cnt_gl_interface;
3554: CLOSE int_count_cur;
3555:
3556: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gl_interface: ' || l_cnt_gl_interface);
3557:
3558: IF (l_cnt_gl_interface > 0) THEN
3559: OPEN gl_tie_back_reject_cur;
3560: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3559: OPEN gl_tie_back_reject_cur;
3560: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3561: CLOSE gl_tie_back_reject_cur;
3562:
3563: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.status.COUNT: ' || r_interface.status.COUNT);
3564:
3565: FORALL recno IN 1..r_interface.status.COUNT
3566: UPDATE psp_enc_summary_lines
3567: SET interface_status = r_interface.status(recno),
3575: loop
3576: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
3577: end loop;
3578:
3579: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated rejected lines status in psp_enc_summary_lines count: ' ||g_bulk_row_count);
3580:
3581: OPEN assign_susp_ac_cur;
3582: FETCH assign_susp_ac_cur BULK COLLECT INTO r_suspense_ac.row_id, r_suspense_ac.encumbrance_date,
3583: r_suspense_ac.enc_start_date, r_suspense_ac.enc_end_date, r_suspense_ac.assignment_id,
3585: r_suspense_ac.gl_code_combination_id, r_suspense_ac.suspense_org_account_id,
3586: r_suspense_ac.interface_status, r_suspense_ac.end_date;
3587: CLOSE assign_susp_ac_cur;
3588:
3589: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'r_suspense_ac.row_id.COUNT : '||r_suspense_ac.row_id.COUNT);
3590:
3591: FOR recno IN 1..r_suspense_ac.row_id.COUNT
3592: LOOP
3593: IF r_suspense_ac.suspense_org_account_id(recno) IS NOT NULL THEN
3672: orig_expenditure_type = expenditure_type
3673: WHERE rowid = r_suspense_ac.row_id(recno)
3674: AND enc_start_date <= g_def_end_date;
3675:
3676: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines count'||sql%rowcount);
3677:
3678: UPDATE psp_enc_lines
3679: SET suspense_org_account_id = l_organization_account_id,
3680: gl_code_combination_id = l_susp_glccid,
3689: status_code = 'N'
3690: WHERE rowid = r_suspense_ac.row_id(recno)
3691: AND enc_start_date <= g_def_end_date;
3692:
3693: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines1 count'||sql%rowcount);
3694:
3695: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'add_st_warnings');
3696: add_st_warnings(p_assignment_id => r_suspense_ac.assignment_id(recno),
3697: p_payroll_id => r_suspense_ac.payroll_id(recno),
3691: AND enc_start_date <= g_def_end_date;
3692:
3693: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines1 count'||sql%rowcount);
3694:
3695: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'add_st_warnings');
3696: add_st_warnings(p_assignment_id => r_suspense_ac.assignment_id(recno),
3697: p_payroll_id => r_suspense_ac.payroll_id(recno),
3698: p_element_type_id => r_suspense_ac.element_type_id(recno),
3699: p_start_date => r_suspense_ac.enc_start_date(recno),
3707: END IF;
3708: END LOOP;
3709:
3710: IF l_suspense_ac_failed = 'Y' THEN
3711: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'Suspence account failed');
3712: UPDATE psp_enc_lines
3713: SET suspense_org_account_id = NULL,
3714: gl_code_combination_id = orig_gl_code_combination_id,
3715: project_id = orig_project_id,
3724: FROM psp_enc_summary_lines pesl
3725: WHERE pesl.payroll_action_id = p_payroll_action_id
3726: AND pesl.group_id = p_group_id
3727: AND status_code = 'R');
3728: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines2 count'||sql%rowcount);
3729:
3730: UPDATE psp_enc_lines
3731: SET orig_gl_code_combination_id = NULL,
3732: orig_project_id = NULL,
3741: WHERE pesl.payroll_action_id = p_payroll_action_id
3742: AND pesl.group_id = p_group_id
3743: AND status_code = 'R');
3744:
3745: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines3 count'||sql%rowcount);
3746:
3747: END IF;
3748:
3749: IF l_suspense_ac_not_found = 'Y' THEN
3769: loop
3770: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
3771: end loop;
3772:
3773: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' TieBack count: '||g_bulk_row_count);
3774:
3775: FORALL recno IN 1..r_interface.status.COUNT
3776: UPDATE psp_enc_controls
3777: SET gl_phase = 'Summarize'
3785: loop
3786: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
3787: end loop;
3788:
3789: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Summarize count: '||g_bulk_row_count);
3790: ELSIF (l_cnt_gl_interface = 0) THEN
3791: OPEN gl_tie_back_success_cur;
3792: 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;
3793: CLOSE gl_tie_back_success_cur;
3791: OPEN gl_tie_back_success_cur;
3792: 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;
3793: CLOSE gl_tie_back_success_cur;
3794:
3795: 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);
3796:
3797: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3798: UPDATE psp_enc_summary_lines
3799: SET status_code = 'A'
3804: loop
3805: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
3806: end loop;
3807:
3808: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines count: '||g_bulk_row_count);
3809:
3810: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3811: INSERT INTO psp_enc_lines_history
3812: (enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
3847: loop
3848: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
3849: end loop;
3850:
3851: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied successfully summarized and transferred lines into psp_enc_lines_history count: '||g_bulk_row_count);
3852:
3853: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3854: DELETE FROM psp_enc_lines
3855: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno);
3859: loop
3860: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
3861: end loop;
3862:
3863: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted lines from psp_enc_lines that are summarized and trasnferred to target systems count: '||g_bulk_row_count);
3864:
3865:
3866:
3867: FORALL recno IN 1..r_interface.enc_control_id.COUNT
3876: loop
3877: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
3878: end loop;
3879:
3880: 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 count: '||g_bulk_row_count);
3881: END IF;
3882: ELSE
3883: g_process_complete := FALSE;
3884: END IF;
3883: g_process_complete := FALSE;
3884: END IF;
3885:
3886: p_return_status := fnd_api.g_ret_sts_success;
3887: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
3888: EXCEPTION
3889: WHEN NO_PROFILE_EXISTS THEN
3890: g_error_api_path := SUBSTR('GL_ENC_TIE_BACK:'||g_error_api_path,1,230);
3891: fnd_message.set_name('PSP','PSP_NO_PROFILE_EXISTS');
3907: p_return_status := fnd_api.g_ret_sts_success;
3908: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3909: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
3910: p_return_status := fnd_api.g_ret_sts_unexp_error;
3911: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
3912: WHEN OTHERS THEN
3913: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3914: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
3915: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GL_ENC_TIE_BACK');
3909: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
3910: p_return_status := fnd_api.g_ret_sts_unexp_error;
3911: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
3912: WHEN OTHERS THEN
3913: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3914: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
3915: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GL_ENC_TIE_BACK');
3916: p_return_status := fnd_api.g_ret_sts_unexp_error;
3917: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
3913: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
3914: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
3915: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GL_ENC_TIE_BACK');
3916: p_return_status := fnd_api.g_ret_sts_unexp_error;
3917: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
3918: END gl_enc_tie_back;
3919: -- End of changes for Create and Update multi thread enh.
3920:
3921: -- ##########################################################################
4076: p_return_status := fnd_api.g_ret_sts_success;
4077:
4078: EXCEPTION
4079: WHEN OTHERS THEN
4080: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
4081: -- dbms_output.put_line('Error while inserting into gl_interface..........');
4082: g_error_api_path := 'insert_into_gl_int:'||g_error_api_path;
4083: fnd_msg_pub.add_exc_msg('psp_enc_sum_tran','insert_into_gl_int');
4084: p_return_status := fnd_api.g_ret_sts_unexp_error;
4762: AND gms_batch_name IS NULL
4763: AND payroll_action_id = p_payroll_action_id;
4764:
4765: BEGIN
4766: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering TR_TO_GMS_INT');
4767:
4768: l_tie_back_failed:= NULL;
4769: l_rec_no := 0;
4770: l_rec_count := 0;
4791:
4792: OPEN operating_unit_csr;
4793: FETCH operating_unit_csr BULK COLLECT INTO org_id_tab;
4794: CLOSE operating_unit_csr;
4795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' org_id_tab.COUNT: ' || org_id_tab.COUNT);
4796:
4797: FOR I in 1..org_id_tab.COUNT
4798: LOOP
4799: SELECT TO_CHAR(psp_gms_batch_name_s.NEXTVAL)
4798: LOOP
4799: SELECT TO_CHAR(psp_gms_batch_name_s.NEXTVAL)
4800: INTO gms_batch_name_tab(i)
4801: FROM DUAL;
4802: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gms_batch_name_tab(' || I || '): ' || gms_batch_name_tab(i));
4803: END LOOP;
4804:
4805:
4806: FORALL I IN 1..org_id_tab.count
4825: loop
4826: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
4827: end loop;
4828:
4829: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updating psp_enc_summary_lines setting status_code N count: '||g_bulk_row_count);
4830:
4831: OPEN int_cur;
4832: FETCH int_cur BULK COLLECT INTO r_interface.txn_interface_id, r_interface.enc_summary_line_id,
4833: r_interface.effective_date, r_interface.time_period_id,
4861: END LOOP;
4862: END LOOP;
4863: */
4864:
4865: 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);
4866: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Verifying interface records for errors');
4867:
4868: -- Commented the following for Bug 10126350
4869: /*
4862: END LOOP;
4863: */
4864:
4865: 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);
4866: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Verifying interface records for errors');
4867:
4868: -- Commented the following for Bug 10126350
4869: /*
4870: OPEN employee_number_cur;
4926: END IF;
4927: END LOOP;
4928:
4929: IF l_raise_error THEN
4930: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Interface records have errors');
4931: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4932: END IF;
4933:
4934: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
4930: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Interface records have errors');
4931: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4932: END IF;
4933:
4934: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
4935: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Computing PA week ending date(s)');
4936:
4937: FOR recno IN 1..r_interface.txn_interface_id.COUNT
4938: LOOP
4931: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4932: END IF;
4933:
4934: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
4935: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Computing PA week ending date(s)');
4936:
4937: FOR recno IN 1..r_interface.txn_interface_id.COUNT
4938: LOOP
4939: psp_general.get_gms_effective_date(r_interface.person_id(recno), r_interface.gms_overriding_date(recno));
4943: END LOOP;
4944: -- set the context again to multiple
4945: mo_global.set_policy_context('M', null);
4946:
4947: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed computation of PA week ending date(s)');
4948:
4949: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
4950: UPDATE psp_enc_summary_lines pesl
4951: SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
4957: loop
4958: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
4959: end loop;
4960:
4961: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated GMS Override Date count: '||g_bulk_row_count);
4962:
4963: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
4964: INSERT INTO pa_transaction_interface_all
4965: (txn_interface_id, transaction_source,
4996: loop
4997: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
4998: end loop;
4999:
5000: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into pa_transaction_interface_all '||g_bulk_row_count);
5001:
5002: FOR recno IN 1..r_interface.txn_interface_id.COUNT
5003: LOOP
5004: GMS_REC.TXN_INTERFACE_ID := r_interface.txn_interface_id(recno);
5027: fnd_msg_pub.add;
5028: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5029: END IF;
5030: END LOOP;
5031: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into gms_transaction_interface_all by calling gms_transactions_pub.LOAD_GMS_XFACE_API');
5032: IF r_interface.txn_interface_id.COUNT > 0 THEN
5033:
5034: FOR request_counter IN 1..org_id_tab.count
5035: LOOP
5035: LOOP
5036: -- set the context to single to call submit_request
5037: mo_global.set_policy_context('S', org_id_tab(request_counter) );
5038: fnd_request.set_org_id (org_id_tab(request_counter) );
5039: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'Calling Transaction import process for org_id: '||org_id_tab(request_counter));
5040: req_id_tab(request_counter) := fnd_request.submit_request
5041: ('PA',
5042: 'PAXTRTRX',
5043: NULL,
5046: l_gms_transaction_source,
5047: gms_batch_name_tab(request_counter));
5048:
5049: IF req_id_tab(request_counter) = 0 THEN
5050: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submission of Transaction Import Failed');
5051: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5052: fnd_msg_pub.add;
5053: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5054: ELSE
5051: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5052: fnd_msg_pub.add;
5053: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5054: ELSE
5055: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submitted Transaction Import');
5056:
5057: OPEN enc_control_id_cur;
5058: FETCH enc_control_id_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
5059: CLOSE enc_control_id_cur;
5068: loop
5069: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5070: end loop;
5071:
5072: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gms_phase to ''Transfer'' in psp_enc_controls count ' ||g_bulk_row_count);
5073:
5074: r_enc_controls.enc_control_id.DELETE;
5075: END IF;
5076: END LOOP;
5077: COMMIT;
5078: -- set the context again to multiple
5079: mo_global.set_policy_context('M', null);
5080:
5081: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5082: -- fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5083: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5084:
5085: FOR I IN 1..org_id_tab.count
5079: mo_global.set_policy_context('M', null);
5080:
5081: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5082: -- fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5083: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5084:
5085: FOR I IN 1..org_id_tab.count
5086: LOOP
5087: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for comlpetion of Transaction Import');
5083: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5084:
5085: FOR I IN 1..org_id_tab.count
5086: LOOP
5087: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for comlpetion of Transaction Import');
5088: call_status := fnd_concurrent.wait_for_request(req_id_tab(I), 10, 0, rphase, rstatus, dphase, dstatus, message);
5089:
5090: IF call_status = FALSE then
5091: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import failed');
5087: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for comlpetion of Transaction Import');
5088: call_status := fnd_concurrent.wait_for_request(req_id_tab(I), 10, 0, rphase, rstatus, dphase, dstatus, message);
5089:
5090: IF call_status = FALSE then
5091: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import failed');
5092: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5093: fnd_msg_pub.add;
5094: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5095: END IF;
5092: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
5093: fnd_msg_pub.add;
5094: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5095: END IF;
5096: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import completed');
5097: END LOOP;
5098: END IF;
5099:
5100: OPEN gms_batch_name_cur;
5100: OPEN gms_batch_name_cur;
5101: FETCH gms_batch_name_cur BULK COLLECT INTO r_gms_batch.gms_batch_name;
5102: CLOSE gms_batch_name_cur;
5103:
5104: 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);
5105:
5106: FOR recno IN 1..r_gms_batch.gms_batch_name.COUNT
5107: LOOP
5108: 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));
5104: 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);
5105:
5106: FOR recno IN 1..r_gms_batch.gms_batch_name.COUNT
5107: LOOP
5108: 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));
5109: gms_enc_tie_back(p_payroll_action_id,
5110: r_gms_batch.gms_batch_name(recno),
5111: g_business_group_id,
5112: g_set_of_books_id,
5112: g_set_of_books_id,
5113: l_return_status);
5114:
5115: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5116: 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));
5117: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5118: END IF;
5119: 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));
5120: END LOOP;
5115: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5116: 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));
5117: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5118: END IF;
5119: 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));
5120: END LOOP;
5121:
5122: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5123: UPDATE psp_enc_summary_lines pesl
5136: loop
5137: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5138: end loop;
5139:
5140: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'update psp_enc_summary_lines setting encumbrance_item_id count: '||g_bulk_row_count);
5141:
5142:
5143: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5144: DELETE pa_transaction_interface_all
5150: loop
5151: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5152: end loop;
5153:
5154: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'delete from pa_transaction_interface_all count:'||g_bulk_row_count);
5155:
5156:
5157:
5158: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5165: loop
5166: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5167: end loop;
5168:
5169: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'delete from gms_transaction_interface_all count:'||g_bulk_row_count);
5170:
5171:
5172:
5173: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5181: loop
5182: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5183: end loop;
5184:
5185: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'setting gms_batch_name to null count:'||g_bulk_row_count);
5186:
5187:
5188:
5189: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Set the un-imported summary lines to New status');
5185: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'setting gms_batch_name to null count:'||g_bulk_row_count);
5186:
5187:
5188:
5189: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Set the un-imported summary lines to New status');
5190:
5191: COMMIT;
5192: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Committing');
5193:
5188:
5189: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Set the un-imported summary lines to New status');
5190:
5191: COMMIT;
5192: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Committing');
5193:
5194: p_return_status := fnd_api.g_ret_sts_success;
5195: EXCEPTION
5196: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5200: WHEN RETURN_BACK THEN
5201: p_return_status := fnd_api.g_ret_sts_success;
5202:
5203: WHEN OTHERS THEN
5204: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
5205: g_error_api_path := 'TR_TO_GMS_INT:'||g_error_api_path;
5206: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','TR_TO_GMS_INT');
5207: p_return_status := fnd_api.g_ret_sts_unexp_error;
5208: END tr_to_gms_int;
6142: AND batch_name = p_gms_batch_name
6143: AND transaction_Status_code in ('P','I')
6144: GROUP BY transaction_status_code;
6145: BEGIN
6146: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6147:
6148: OPEN transaction_status_cur;
6149: FETCH transaction_status_cur INTO l_cnt, l_status;
6150: IF (transaction_status_cur%ROWCOUNT = 0) THEN
6151: l_cnt := 0;
6152: END IF;
6153: CLOSE transaction_status_cur;
6154:
6155: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
6156:
6157: IF l_cnt = 0 THEN
6158: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6159: RETURN TRUE;
6154:
6155: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
6156:
6157: IF l_cnt = 0 THEN
6158: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
6159: RETURN TRUE;
6160: ELSIF l_cnt > 0 THEN
6161: IF l_status = 'P' THEN
6162: DELETE FROM pa_transaction_interface_all
6161: IF l_status = 'P' THEN
6162: DELETE FROM pa_transaction_interface_all
6163: WHERE transaction_source = 'GOLDE'
6164: AND batch_name = p_gms_batch_name;
6165: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from pa_trancsaction_interface_all count: '||sql%rowcount);
6166:
6167: DELETE FROM gms_transaction_interface_all
6168: WHERE transaction_source = 'GOLDE'
6169: AND batch_name = p_gms_batch_name;
6166:
6167: DELETE FROM gms_transaction_interface_all
6168: WHERE transaction_source = 'GOLDE'
6169: AND batch_name = p_gms_batch_name;
6170: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all count: '||sql%rowcount);
6171: DELETE FROM psp_enc_summary_lines
6172: WHERE gms_batch_name = p_gms_batch_name;
6173: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines count: '||sql%rowcount);
6174:
6169: AND batch_name = p_gms_batch_name;
6170: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all count: '||sql%rowcount);
6171: DELETE FROM psp_enc_summary_lines
6172: WHERE gms_batch_name = p_gms_batch_name;
6173: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines count: '||sql%rowcount);
6174:
6175: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE count: '||sql%rowcount);
6176: RETURN FALSE;
6177: ELSIF l_status = 'I' THEN
6171: DELETE FROM psp_enc_summary_lines
6172: WHERE gms_batch_name = p_gms_batch_name;
6173: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines count: '||sql%rowcount);
6174:
6175: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE count: '||sql%rowcount);
6176: RETURN FALSE;
6177: ELSIF l_status = 'I' THEN
6178: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE count: '||sql%rowcount);
6179: RETURN FALSE;
6174:
6175: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE count: '||sql%rowcount);
6176: RETURN FALSE;
6177: ELSIF l_status = 'I' THEN
6178: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE count: '||sql%rowcount);
6179: RETURN FALSE;
6180: END IF;
6181: END IF;
6182:
6181: END IF;
6182:
6183: END PROCESS_COMPLETE;
6184: BEGIN
6185: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GMS_ENC_TIE_BACK');
6186:
6187: IF (PROCESS_COMPLETE) THEN
6188: OPEN txn_interface_count_cur;
6189: FETCH txn_interface_count_cur INTO l_cnt_gms_interface;
6187: IF (PROCESS_COMPLETE) THEN
6188: OPEN txn_interface_count_cur;
6189: FETCH txn_interface_count_cur INTO l_cnt_gms_interface;
6190: CLOSE txn_interface_count_cur;
6191: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gms_interface: ' || l_cnt_gms_interface);
6192:
6193: IF l_cnt_gms_interface > 0 THEN
6194: OPEN gms_tie_back_reject_cur;
6195: FETCH gms_tie_back_reject_cur BULK COLLECT INTO r_reject_recs.reason_code, r_reject_recs.enc_summary_line_id,
6195: FETCH gms_tie_back_reject_cur BULK COLLECT INTO r_reject_recs.reason_code, r_reject_recs.enc_summary_line_id,
6196: r_reject_recs.txn_status_code;
6197: CLOSE gms_tie_back_reject_cur;
6198:
6199: 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);
6200:
6201: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6202: UPDATE psp_enc_summary_lines
6203: SET interface_status = r_reject_recs.reason_code(recno),
6211: loop
6212: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6213: end loop;
6214:
6215: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated interface_status with reject reason code in psp_enc_summary_lines count: '||g_bulk_row_count);
6216:
6217: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6218: UPDATE psp_enc_summary_lines
6219: SET interface_status = r_reject_recs.reason_code(recno),
6227: loop
6228: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6229: end loop;
6230:
6231: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' for accepted records in psp_enc_summary_lines count: '||g_bulk_row_count);
6232:
6233: OPEN get_success_recs_cur;
6234: FETCH get_success_recs_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6235: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6234: FETCH get_success_recs_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6235: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6236: CLOSE get_success_recs_cur;
6237:
6238: 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);
6239:
6240: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6241: INSERT INTO psp_enc_lines_history
6242: (enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
6270: orig_gl_code_combination_id, orig_project_id, orig_task_id, orig_award_id,
6271: orig_expenditure_org_id, orig_expenditure_type
6272: FROM psp_enc_lines
6273: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6274: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied successfully summarized and transferred lines into psp_enc_lines_history');
6275:
6276:
6277: g_bulk_row_count :=0;
6278: FOR bulk_idx IN 1..r_success_recs.enc_summary_line_id.COUNT
6282:
6283: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6284: DELETE FROM psp_enc_lines
6285: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6286: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted lines from psp_enc_lines that are summarized and trasnferred to target systems count: '||g_bulk_row_count);
6287:
6288: OPEN assign_susp_ac_cur;
6289: FETCH assign_susp_ac_cur BULK COLLECT INTO r_suspense_ac.row_id, r_suspense_ac.encumbrance_date,
6290: r_suspense_ac.enc_start_date, r_suspense_ac.enc_end_date, r_suspense_ac.person_id,
6390: fnd_message.set_token('END_DATE', l_effective_date);
6391: fnd_message.set_token('GL', l_gl_description);
6392: fnd_message.set_token('AUTOPOP_STATUS', l_autopop_error);
6393: g_warning_message := fnd_message.get;
6394: fnd_file.put_line(fnd_file.log, g_warning_message);
6395: ELSE
6396: l_susp_glccid := l_new_gl_code_combination_id;
6397: END IF;
6398: END IF;
6455: fnd_message.set_token('EO', l_exp_org_name);
6456: fnd_message.set_token('ET', l_expenditure_type);
6457: fnd_message.set_token('AUTOPOP_STATUS', l_autopop_error);
6458: g_warning_message := fnd_message.get;
6459: fnd_file.put_line(fnd_file.log, g_warning_message);
6460: ELSE
6461: l_expenditure_type := l_new_expenditure_type;
6462: END IF;
6463: END IF;
6474: orig_expenditure_org_id = expenditure_organization_id,
6475: orig_expenditure_type = expenditure_type
6476: WHERE rowid = r_suspense_ac.row_id(recno);
6477:
6478: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines4 count'||sql%rowcount);
6479:
6480: UPDATE psp_enc_lines
6481: SET suspense_org_account_id = l_organization_account_id,
6482: gl_code_combination_id = l_susp_glccid,
6492: encumbrance_amount = (encumbrance_amount * (psp_general.business_days(enc_start_date, LEAST(enc_end_date, g_def_end_date)) / psp_general.business_days(enc_start_date, enc_end_date))),
6493: status_code = 'N'
6494: WHERE rowid = r_suspense_ac.row_id(recno);
6495:
6496: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines5 count'||sql%rowcount);
6497: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'add_st_warnings');
6498: add_st_warnings(p_assignment_id => r_suspense_ac.assignment_id(recno),
6499: p_payroll_id => r_suspense_ac.payroll_id(recno),
6500: p_element_type_id => r_suspense_ac.element_type_id(recno),
6493: status_code = 'N'
6494: WHERE rowid = r_suspense_ac.row_id(recno);
6495:
6496: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines5 count'||sql%rowcount);
6497: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'add_st_warnings');
6498: add_st_warnings(p_assignment_id => r_suspense_ac.assignment_id(recno),
6499: p_payroll_id => r_suspense_ac.payroll_id(recno),
6500: p_element_type_id => r_suspense_ac.element_type_id(recno),
6501: p_start_date => r_suspense_ac.enc_start_date(recno),
6568: SET 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),
6569: 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)
6570: WHERE enc_control_id = r_success_recs.enc_control_id(recno);
6571:
6572: 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');
6573:
6574: r_reject_recs.enc_summary_line_id.DELETE;
6575: r_reject_recs.enc_control_id.DELETE;
6576: r_reject_recs.reason_code.DELETE;
6584: FETCH gms_tie_back_success_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6585: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6586: CLOSE gms_tie_back_success_cur;
6587:
6588: 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);
6589:
6590: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6591: UPDATE psp_enc_summary_lines
6592: SET status_code = 'A'
6597: loop
6598: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6599: end loop;
6600:
6601: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines count: '||g_bulk_row_count);
6602:
6603:
6604:
6605: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6638: loop
6639: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6640: end loop;
6641:
6642: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied successfully summarized and transferred lines into psp_enc_lines_history count: '||g_bulk_row_count);
6643:
6644: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6645: DELETE FROM psp_enc_lines
6646: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6649: FOR bulk_idx IN 1..r_success_recs.enc_summary_line_id.COUNT
6650: loop
6651: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6652: end loop;
6653: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted lines from psp_enc_lines that are summarized and trasnferred to target systems'||g_bulk_row_count);
6654:
6655: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6656: UPDATE psp_enc_controls pec
6657: SET gms_phase = 'TieBack',
6664: loop
6665: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6666: end loop;
6667:
6668: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated summ_ogm_cr_amount, summ_ogm_dr_amount, gms_phase in psp_enc_controls count: '||g_bulk_row_count);
6669:
6670: r_success_recs.enc_summary_line_id.DELETE;
6671: r_success_recs.enc_control_id.DELETE;
6672: r_success_recs.reason_code.DELETE;
6676: g_process_complete := FALSE;
6677: END IF;
6678:
6679: p_return_status := fnd_api.g_ret_sts_success;
6680: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6681: EXCEPTION
6682: WHEN NO_PROFILE_EXISTS THEN
6683: g_error_api_path := SUBSTR('GMS_ENC_TIE_BACK:'||g_error_api_path,1,230);
6684: fnd_message.set_name('PSP','PSP_NO_PROFILE_EXISTS');
6700: p_return_status := fnd_api.g_ret_sts_success;
6701: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6702: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6703: p_return_status := fnd_api.g_ret_sts_unexp_error;
6704: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6705: WHEN OTHERS THEN
6706: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6707: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6708: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GMS_ENC_TIE_BACK');
6702: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6703: p_return_status := fnd_api.g_ret_sts_unexp_error;
6704: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6705: WHEN OTHERS THEN
6706: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6707: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6708: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GMS_ENC_TIE_BACK');
6709: p_return_status := fnd_api.g_ret_sts_unexp_error;
6710: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6706: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6707: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6708: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GMS_ENC_TIE_BACK');
6709: p_return_status := fnd_api.g_ret_sts_unexp_error;
6710: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6711: END gms_enc_tie_back;
6712: -- End of changes for Create and Update multi thread enh.
6713:
6714: PROCEDURE create_sum_lines (p_payroll_action_id IN NUMBER,
6866: t_sum_lines.attribute10(recno), t_sum_lines.org_id(recno),
6867: p_payroll_action_id, SYSDATE,
6868: l_last_updated_by, l_last_update_login,
6869: l_last_updated_by, SYSDATE);
6870: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' No of Summary lines created (t_sum_lines.enc_control_id.COUNT): ' || t_sum_lines.enc_control_id.COUNT);
6871:
6872: IF (g_dff_grouping_option = 'Y') THEN
6873: FORALL recno IN 1..t_sum_lines.enc_summary_line_id.COUNT
6874: UPDATE psp_enc_lines pel
6917: END IF;
6918: p_return_status := fnd_api.g_ret_sts_success;
6919: EXCEPTION
6920: WHEN OTHERS THEN
6921: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
6922: g_error_api_path := SUBSTR('CREATE_SUM_LINES:' || g_error_api_path,1,230);
6923: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN', 'CREATE_SUM_LINES');
6924: p_return_status := fnd_api.g_ret_sts_unexp_error;
6925: END create_sum_lines;
6945: ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
6946:
6947: l_nlines_counter NUMBER(15);
6948: BEGIN
6949: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering UPDATE_HIERARCHY_DATES');
6950: l_nlines_counter := 0;
6951:
6952: FOR asg_no IN 1..l_asgs.payroll_id.COUNT
6953: LOOP
7066: AND enc_start_date <= t_enc_nlines.enc_end_date(recno)
7067: AND enc_end_date >= t_enc_nlines.enc_start_date(recno);
7068:
7069: p_return_status := fnd_api.g_ret_sts_success;
7070: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
7071: EXCEPTION
7072: WHEN OTHERS THEN
7073: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
7074: g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
7069: p_return_status := fnd_api.g_ret_sts_success;
7070: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
7071: EXCEPTION
7072: WHEN OTHERS THEN
7073: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
7074: g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
7075: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN', ' UPDATE_HIERARCHY_DATES');
7076: p_return_status := fnd_api.g_ret_sts_unexp_error;
7077: fnd_file.put_line(fnd_file.log, 'SQLCODE: ' || SQLCODE || ' SQLERRM: ' || SQLERRM);
7073: fnd_file.put_line(fnd_file.log,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
7074: g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
7075: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN', ' UPDATE_HIERARCHY_DATES');
7076: p_return_status := fnd_api.g_ret_sts_unexp_error;
7077: fnd_file.put_line(fnd_file.log, 'SQLCODE: ' || SQLCODE || ' SQLERRM: ' || SQLERRM);
7078: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
7079: END update_hierarchy_dates;
7080:
7081: PROCEDURE log_st_warnings IS
7074: g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
7075: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN', ' UPDATE_HIERARCHY_DATES');
7076: p_return_status := fnd_api.g_ret_sts_unexp_error;
7077: fnd_file.put_line(fnd_file.log, 'SQLCODE: ' || SQLCODE || ' SQLERRM: ' || SQLERRM);
7078: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
7079: END update_hierarchy_dates;
7080:
7081: PROCEDURE log_st_warnings IS
7082: l_request_id NUMBER(15);
7252: p_error_status IN VARCHAR2 DEFAULT NULL) IS
7253: l_warning_ind NUMBER(15);
7254: BEGIN
7255: l_warning_ind := st_warnings.start_date.COUNT;
7256: fnd_file.put_line(fnd_file.log, 'Entering add_st_warnings');
7257: hr_utility.trace('p_start_date: ' || p_start_date || ' p_end_date: ' || p_end_date ||
7258: ' p_assignment_id: ' || p_assignment_id || ' p_payroll_id: ' || p_payroll_id ||
7259: ' p_element_type_id: ' || p_element_type_id || ' p_gl_ccid: ' || p_gl_ccid ||
7260: ' p_project_id: ' || p_project_id || ' p_task_id: ' || p_task_id ||
7311: st_warnings.effective_date(l_warning_ind) := p_effective_date;
7312: END IF;
7313:
7314: hr_utility.trace('st_warnings.start_date.COUNT: ' || st_warnings.start_date.COUNT);
7315: fnd_file.put_line(fnd_file.log, 'Leaving add_st_warnings');
7316: END add_st_warnings;
7317:
7318: PROCEDURE move_rej_lines_to_arch (p_payroll_action_id IN NUMBER) IS
7319: BEGIN
7372: WHERE payroll_action_id = p_payroll_action_id
7373: AND status_code = 'R';
7374:
7375: IF (SQL%ROWCOUNT > 0) THEN
7376: fnd_file.put_line(fnd_file.log, 'Moved rejected lines (if any) to archival table as they are no longer useful count '|| sql%rowcount);
7377: END IF;
7378:
7379: DELETE psp_enc_summary_lines
7380: WHERE payroll_action_id = p_payroll_action_id
7379: DELETE psp_enc_summary_lines
7380: WHERE payroll_action_id = p_payroll_action_id
7381: AND status_code = 'R';
7382:
7383: fnd_file.put_line(fnd_file.log, 'Deleted rows from psp_enc_summary_lines with rejection code R'||sql%rowcount);
7384:
7385: END move_rej_lines_to_arch;
7386: END psp_enc_sum_tran;