[Home] [Help]
164: AND superceded_line_id IS NULL
165: AND award_id IS NOT NULL
166: AND status_code = 'N';
167: BEGIN
168: 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 || '
169: p_business_group_id: ' || p_business_group_id || '
170: p_set_of_books_id: ' || p_set_of_books_id);
171:
172: g_error_api_path := '';
226: SET process_status = 'S',
227: process_phase = 'completed'
228: WHERE payroll_action_id = p_payroll_action_id
229: AND process_code IN ('CEL', 'LET');
230: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated original and current process status in psp_enc_processes');
231:
232: OPEN st_asgs_cur;
233: FETCH st_asgs_cur BULK COLLECT INTO l_asgs.assignment_id, l_asgs.payroll_id;
234: CLOSE st_asgs_cur;
239: FETCH liq_check_cur INTO l_liq_check;
240: CLOSE liq_check_cur;
241:
242: IF (l_liq_check > 0) THEN
243: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_enc_liq_tran.enc_liq_trans');
244: psp_enc_liq_tran.enc_liq_trans(p_payroll_action_id, p_business_group_id, p_set_of_books_id, l_return_status);
245:
246: IF l_return_status NOT IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_success) THEN
247: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245:
246: IF l_return_status NOT IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_success) THEN
247: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248: END IF;
249: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_enc_liq_tran.enc_liq_trans');
250: END IF;
251:
252: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_begin');
253: enc_batch_begin(p_payroll_action_id, l_return_status);
248: END IF;
249: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_enc_liq_tran.enc_liq_trans');
250: END IF;
251:
252: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_begin');
253: enc_batch_begin(p_payroll_action_id, l_return_status);
254:
255: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
256: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254:
255: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
256: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257: END IF;
258: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_begin');
259:
260: IF g_control_rec_found = 'FALSE' THEN
261: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
262: enc_batch_end(p_payroll_action_id, l_return_status);
257: END IF;
258: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_begin');
259:
260: IF g_control_rec_found = 'FALSE' THEN
261: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
262: enc_batch_end(p_payroll_action_id, l_return_status);
263:
264: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
265: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263:
264: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
265: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
266: END IF;
267: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
268: retcode := FND_API.G_RET_STS_SUCCESS;
269:
270: psp_message_s.print_error(p_mode=>FND_FILE.log,
271: p_print_header=>FND_API.G_FALSE);
266: END IF;
267: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
268: retcode := FND_API.G_RET_STS_SUCCESS;
269:
270: psp_message_s.print_error(p_mode=>FND_FILE.log,
271: p_print_header=>FND_API.G_FALSE);
272: 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 || '
273: p_business_group_id: ' || p_business_group_id || '
274: p_set_of_books_id: ' || p_set_of_books_id);
268: retcode := FND_API.G_RET_STS_SUCCESS;
269:
270: psp_message_s.print_error(p_mode=>FND_FILE.log,
271: p_print_header=>FND_API.G_FALSE);
272: 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 || '
273: p_business_group_id: ' || p_business_group_id || '
274: p_set_of_books_id: ' || p_set_of_books_id);
275: PSP_MESSAGE_S.Print_success;
276: return;
278:
279: -- Enh 2505778 LD, GMS Integration with PQH
280:
281: if fnd_profile.value('PSP_ENC_ENABLE_PQH') ='Y' then
282: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling pqh_psp_integration.relieve_budget_commitments');
283: pqh_psp_integration.relieve_budget_commitments( 'S', l_return_status);
284: If l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
285: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
286: fnd_msg_pub.add;
285: fnd_message.set_name('PSP','PSP_ENC_PQH_ERROR');
286: fnd_msg_pub.add;
287: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
288: end if;
289: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After pqh_psp_integration.relieve_budget_commitments');
290: end if;
291:
292: -- FIRST NORMAL RUN
293: -- initiate the gl encumbrance summarization and transfer
293: -- initiate the gl encumbrance summarization and transfer
294: -- call the user extension to populate attribute1 through attribute30
295: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
296: -- 2968684 added params to following proc
297: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
298: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
299: p_business_group_id,
300: p_set_of_books_id);
301: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
297: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
298: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
299: p_business_group_id,
300: p_set_of_books_id);
301: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
302: END IF;
303:
304: OPEN check_gl_lines;
305: FETCH check_gl_lines INTO l_gl_check;
303:
304: OPEN check_gl_lines;
305: FETCH check_gl_lines INTO l_gl_check;
306: CLOSE check_gl_lines;
307: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gl_check: ' || l_gl_check);
308:
309: IF (l_gl_check > 0) THEN
310: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
311: tr_to_gl_int(p_payroll_action_id, l_return_status);
306: CLOSE check_gl_lines;
307: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gl_check: ' || l_gl_check);
308:
309: IF (l_gl_check > 0) THEN
310: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
311: tr_to_gl_int(p_payroll_action_id, l_return_status);
312:
313: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
312:
313: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315: END IF;
316: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
317: END IF;
318:
319: -- call the user extension to populate attribute1 through attribute30
320: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
318:
319: -- call the user extension to populate attribute1 through attribute30
320: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
321: -- 2968684 added params to following proc
322: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
323: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
324: p_business_group_id,
325: p_set_of_books_id);
326: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
322: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
323: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
324: p_business_group_id,
325: p_set_of_books_id);
326: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
327: END IF;
328:
329: -- initiate the ogm encumbrance summarization and transfer
330: OPEN check_gms_lines;
329: -- initiate the ogm encumbrance summarization and transfer
330: OPEN check_gms_lines;
331: FETCH check_gms_lines INTO l_gms_check;
332: CLOSE check_gms_lines;
333: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gms_check: ' || l_gms_check);
334:
335: IF (l_gms_check > 0) THEN
336: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
337: tr_to_gms_int(p_payroll_action_id, l_return_status);
332: CLOSE check_gms_lines;
333: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gms_check: ' || l_gms_check);
334:
335: IF (l_gms_check > 0) THEN
336: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
337: tr_to_gms_int(p_payroll_action_id, l_return_status);
338:
339: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
340: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338:
339: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
340: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341: END IF;
342: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
343: END IF;
344:
345: -- SECOND RUN TO TAKE CARE OF TIE-BACK
346: -- initiate the suspense lines summarization
343: END IF;
344:
345: -- SECOND RUN TO TAKE CARE OF TIE-BACK
346: -- initiate the suspense lines summarization
347: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_sum_lines');
348: create_sum_lines(p_payroll_action_id, l_return_status);
349:
350: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
351: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
349:
350: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
351: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352: END IF;
353: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_sum_lines');
354:
355: -- call the user extension to populate attribute1 through attribute30
356: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
357: -- 2968684 added params to following proc
354:
355: -- call the user extension to populate attribute1 through attribute30
356: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
357: -- 2968684 added params to following proc
358: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
359: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
360: p_business_group_id,
361: p_set_of_books_id);
362: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
358: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
359: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
360: p_business_group_id,
361: p_set_of_books_id);
362: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
363: END IF;
364:
365: OPEN check_gl_lines;
366: FETCH check_gl_lines INTO l_gl_check;
364:
365: OPEN check_gl_lines;
366: FETCH check_gl_lines INTO l_gl_check;
367: CLOSE check_gl_lines;
368: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gl_check: ' || l_gl_check);
369:
370: IF (l_gl_check > 0) THEN
371: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
372: tr_to_gl_int(p_payroll_action_id, l_return_status);
367: CLOSE check_gl_lines;
368: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gl_check: ' || l_gl_check);
369:
370: IF (l_gl_check > 0) THEN
371: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gl_int');
372: tr_to_gl_int(p_payroll_action_id, l_return_status);
373:
374: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
375: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373:
374: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
375: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
376: END IF;
377: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gl_int');
378: END IF;
379:
380: -- call the user extension to populate attribute1 through attribute30
381: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
379:
380: -- call the user extension to populate attribute1 through attribute30
381: IF FND_PROFILE.VALUE('PSP_ST_EXTENSION_ENABLE') = 'Y' THEN
382: -- 2968684 added params to following proc
383: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
384: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
385: p_business_group_id,
386: p_set_of_books_id);
387: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
383: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling psp_st_ext.summary_ext_encumber');
384: psp_st_ext.summary_ext_encumber(p_payroll_action_id ,
385: p_business_group_id,
386: p_set_of_books_id);
387: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After psp_st_ext.summary_ext_encumber');
388: END IF;
389:
390: OPEN check_gms_lines;
391: FETCH check_gms_lines INTO l_gms_check;
389:
390: OPEN check_gms_lines;
391: FETCH check_gms_lines INTO l_gms_check;
392: CLOSE check_gms_lines;
393: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gms_check: ' || l_gms_check);
394:
395: IF (l_gms_check > 0) THEN
396: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
397: tr_to_gms_int(p_payroll_action_id, l_return_status);
392: CLOSE check_gms_lines;
393: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_gms_check: ' || l_gms_check);
394:
395: IF (l_gms_check > 0) THEN
396: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling tr_to_gms_int');
397: tr_to_gms_int(p_payroll_action_id, l_return_status);
398:
399: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
400: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
398:
399: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
400: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401: END IF;
402: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
403: END IF;
404:
405: g_suspense_failed := 'TRUE';
406: DELETE psp_enc_summary_lines
406: DELETE psp_enc_summary_lines
407: WHERE payroll_action_id = p_payroll_action_id
408: AND superceded_line_id IS NULL
409: AND status_code = 'N';
410: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted un-imported summary lines (' || SQL%ROWCOUNT || ')');
411:
412: UPDATE psp_enc_lines
413: SET suspense_org_account_id = NULL,
414: gl_code_combination_id = orig_gl_code_combination_id,
431: orig_expenditure_type = NULL,
432: suspense_reason_code = NULL
433: WHERE suspense_reason_code like 'ES:%'
434: AND payroll_action_id = p_payroll_action_id;
435: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted un-imported suspense postings posted in this process (' || SQL%ROWCOUNT || ')');
436:
437: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_sum_lines');
438: create_sum_lines(p_payroll_action_id, l_return_status);
439:
433: WHERE suspense_reason_code like 'ES:%'
434: AND payroll_action_id = p_payroll_action_id;
435: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted un-imported suspense postings posted in this process (' || SQL%ROWCOUNT || ')');
436:
437: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling create_sum_lines');
438: create_sum_lines(p_payroll_action_id, l_return_status);
439:
440: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
441: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
439:
440: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
441: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442: END IF;
443: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_sum_lines');
444:
445: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
446: enc_batch_end(p_payroll_action_id, l_return_status);
447:
441: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442: END IF;
443: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After create_sum_lines');
444:
445: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling enc_batch_end');
446: enc_batch_end(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 enc_batch_end');
452: retcode := FND_API.G_RET_STS_SUCCESS;
453:
454: 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 || '
455: p_business_group_id: ' || p_business_group_id || '
450: END IF;
451: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After enc_batch_end');
452: retcode := FND_API.G_RET_STS_SUCCESS;
453:
454: 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 || '
455: p_business_group_id: ' || p_business_group_id || '
456: p_set_of_books_id: ' || p_set_of_books_id);
457: PSP_MESSAGE_S.Print_success;
458: EXCEPTION
461: g_error_api_path := 'ENC_SUM_TRANS:'||g_error_api_path;
462: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','ENC_SUM_TRANS');
463: retcode := 2;
464:
465: 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 || '
466: p_business_group_id: ' || p_business_group_id || '
467: p_set_of_books_id: ' || p_set_of_books_id);
468: psp_message_s.print_error(p_mode => FND_FILE.LOG,
469: p_print_header => FND_API.G_TRUE);
464:
465: 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 || '
466: p_business_group_id: ' || p_business_group_id || '
467: p_set_of_books_id: ' || p_set_of_books_id);
468: psp_message_s.print_error(p_mode => FND_FILE.LOG,
469: p_print_header => FND_API.G_TRUE);
470: return;
471:
472: WHEN OTHERS THEN
474: g_error_api_path := 'ENC_SUM_TRANS:'||g_error_api_path;
475: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','ENC_SUM_TRANS');
476: retcode := 2;
477:
478: 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 || '
479: p_business_group_id: ' || p_business_group_id || '
480: p_set_of_books_id: ' || p_set_of_books_id);
481: psp_message_s.print_error(p_mode => FND_FILE.LOG,
482: p_print_header => FND_API.G_TRUE);
477:
478: 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 || '
479: p_business_group_id: ' || p_business_group_id || '
480: p_set_of_books_id: ' || p_set_of_books_id);
481: psp_message_s.print_error(p_mode => FND_FILE.LOG,
482: p_print_header => FND_API.G_TRUE);
483: return;
484: END;
485:
606: WHERE pesl.payroll_action_id = p_payroll_action_id
607: AND pesl.enc_control_id = pec.enc_control_id
608: AND pesl.status_code = 'N');
609: IF (SQL%ROWCOUNT > 0) THEN
610: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated action_code to ''P''');
611: END IF;
612:
613: UPDATE psp_enc_processes
614: SET process_status = 'P', process_phase = 'completed'
618: FROM psp_enc_summary_lines pesl
619: WHERE pesl.payroll_action_id = p_payroll_action_id
620: AND pesl.status_code = 'N');
621: IF (SQL%ROWCOUNT > 0) THEN
622: 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');
623: END IF;
624:
625: UPDATE psp_enc_processes
626: SET process_phase = 'summarize_transfer'
629: AND EXISTS (SELECT 1
630: FROM psp_enc_lines pel
631: WHERE pel.payroll_action_id = p_payroll_action_id);
632: IF (SQL%ROWCOUNT > 0) THEN
633: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to summarize and transfer re-processing');
634: END IF;
635:
636: UPDATE psp_enc_processes
637: SET process_phase = 'liquidate'
642: WHERE pesl.payroll_action_id = p_payroll_action_id
643: AND pesl.status_code = 'N'
644: AND pesl.superceded_line_id IS NOT NULL);
645: IF (SQL%ROWCOUNT > 0) THEN
646: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to liquidate for liquidation re-processing');
647: END IF;
648:
649: UPDATE psp_enc_process_assignments pepa
650: SET assignment_status = 'P'
655: AND pesl.assignment_id = pepa.assignment_id
656: AND pesl.payroll_id = pepa.payroll_id
657: AND pesl.status_code = 'N');
658: IF (SQL%ROWCOUNT > 0) THEN
659: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated assignment_status to processed for summarized and transferred assignments');
660: END IF;
661:
662: UPDATE psp_enc_process_assignments pepa
663: SET assignment_status = 'S'
669: AND pesl.payroll_id = pepa.payroll_id
670: AND pesl.status_code = 'N'
671: AND pesl.superceded_line_id IS NULL);
672: IF (SQL%ROWCOUNT > 0) THEN
673: 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');
674: END IF;
675:
676: UPDATE psp_enc_process_assignments pepa
677: SET assignment_status = 'L'
683: AND pesl.payroll_id = pepa.payroll_id
684: AND pesl.status_code = 'N'
685: AND pesl.superceded_line_id IS NOT NULL);
686: IF (SQL%ROWCOUNT > 0) THEN
687: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated assignment_status to liquidate for assignments to be liquidated');
688: END IF;
689:
690: COMMIT;
691:
693: FETCH pending_line_cur INTO l_pending_line_count;
694: CLOSE pending_line_cur;
695:
696: IF (l_pending_line_count > 0) THEN
697: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Need to re-process');
698: RAISE fnd_api.g_exc_unexpected_error;
699: END IF;
700: p_return_status := fnd_api.g_ret_sts_success;
701: EXCEPTION
840: AND NOT EXISTS (SELECT 1
841: FROM psp_enc_summary_lines pesl
842: WHERE pesl.payroll_action_id = p_payroll_action_id
843: AND pesl.status_code = 'N');
844: 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');
845:
846: UPDATE psp_enc_processes
847: SET process_phase = 'summarize_transfer'
848: WHERE payroll_action_id = p_payroll_action_id
850: FROM psp_enc_summary_lines pesl
851: WHERE pesl.payroll_action_id = p_payroll_action_id
852: AND pesl.status_code = 'N'
853: AND pesl.superceded_line_id IS NULL);
854: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to summarize and transfer re-processing');
855:
856: UPDATE psp_enc_processes
857: SET process_phase = 'liquidate'
858: WHERE payroll_action_id = p_payroll_action_id
860: FROM psp_enc_summary_lines pesl
861: WHERE pesl.payroll_action_id = p_payroll_action_id
862: AND pesl.status_code = 'N'
863: AND pesl.superceded_line_id IS NOT NULL);
864: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to liquidate for liqduidation re-processing');
865:
866: COMMIT;
867: p_return_status := fnd_api.g_ret_sts_success;
868: EXCEPTION
2336: SELECT enc_control_id
2337: FROM psp_enc_controls
2338: WHERE payroll_action_id = p_payroll_action_id;
2339: BEGIN
2340: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering Transfer to GL Interface');
2341:
2342: l_created_by := fnd_global.user_id;
2343: l_tie_back_failed := NULL;
2344: l_summarization_option := NVL(fnd_profile.value('PSP_ENABLE_ENC_SUMM_GL'),'N');
2349: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2350: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2351: END IF;
2352:
2353: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_user_je_source: ' || l_user_je_source);
2354:
2355: gl_je_cat( l_user_je_cat,
2356: l_return_status);
2357: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2357: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2358: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2359: END IF;
2360:
2361: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_user_je_cat: ' || l_user_je_cat);
2362:
2363: enc_type( l_enc_type_id,
2364: l_return_status);
2365: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2365: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2366: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2367: END IF;
2368:
2369: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_enc_type_id: ' || l_enc_type_id);
2370:
2371: SELECT gl_interface_control_s.nextval
2372: INTO l_group_id
2373: FROM dual;
2371: SELECT gl_interface_control_s.nextval
2372: INTO l_group_id
2373: FROM dual;
2374:
2375: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_group_id: ' || l_group_id);
2376:
2377: UPDATE psp_enc_summary_lines pesl
2378: SET group_id = l_group_id
2379: WHERE status_code = 'N'
2388: AND pesl2.status_code IN ('N', 'R')
2389: AND pesl2.superceded_line_id IS NOT NULL)
2390: AND payroll_action_id = p_payroll_action_id;
2391:
2392: 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');
2393:
2394: INSERT INTO gl_interface
2395: (status, set_of_books_id, accounting_date,
2396: currency_code, date_created, created_by,
2437: AND pesl.superceded_line_id IS NULL
2438: AND pesl.group_id = l_group_id
2439: AND pesl.payroll_action_id = p_payroll_action_id;
2440:
2441: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Number of records inserted into GL_INTERFACE: ' || SQL%ROWCOUNT);
2442:
2443: IF (SQL%ROWCOUNT > 0) THEN
2444: SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
2445: INTO l_int_run_id
2444: SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
2445: INTO l_int_run_id
2446: FROM DUAL;
2447:
2448: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_int_run_id: ' || l_int_run_id);
2449:
2450: INSERT INTO gl_interface_control
2451: (je_source_name, status, interface_run_id,
2452: group_id, set_of_books_id)
2452: group_id, set_of_books_id)
2453: VALUES (l_user_je_source, 'S', l_int_run_id,
2454: l_group_id, g_set_of_books_id);
2455:
2456: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
2457: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Journal Import');
2458:
2459: req_id := fnd_request.submit_request( 'SQLGL',
2460: 'GLLEZL',
2453: VALUES (l_user_je_source, 'S', l_int_run_id,
2454: l_group_id, g_set_of_books_id);
2455:
2456: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted control record into gl_interface_control');
2457: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Before submitting Journal Import');
2458:
2459: req_id := fnd_request.submit_request( 'SQLGL',
2460: 'GLLEZL',
2461: '',
2469: l_summarization_option,
2470: 'W');
2471:
2472: IF req_id = 0 THEN
2473: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import submission failed');
2474: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
2475: fnd_msg_pub.add;
2476: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2477: ELSE
2474: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
2475: fnd_msg_pub.add;
2476: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2477: ELSE
2478: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submitted Journal Import (req_id: ' || req_id || ')');
2479: OPEN enc_control_id_cur;
2480: FETCH enc_control_id_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
2481: CLOSE enc_control_id_cur;
2482:
2479: OPEN enc_control_id_cur;
2480: FETCH enc_control_id_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
2481: CLOSE enc_control_id_cur;
2482:
2483: 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);
2484:
2485: FORALL I IN 1..r_enc_controls.enc_control_id.COUNT
2486: UPDATE psp_enc_controls
2487: SET gl_phase = 'Transfer'
2486: UPDATE psp_enc_controls
2487: SET gl_phase = 'Transfer'
2488: WHERE enc_control_id = r_enc_controls.enc_control_id(I);
2489:
2490: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated gl_phase to ''Transfer'' in psp_enc_controls');
2491:
2492: r_enc_controls.enc_control_id.DELETE;
2493:
2494: COMMIT;
2492: r_enc_controls.enc_control_id.DELETE;
2493:
2494: COMMIT;
2495:
2496: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
2497: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
2498: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
2499:
2500: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
2494: COMMIT;
2495:
2496: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
2497: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
2498: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
2499:
2500: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
2501:
2502: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
2496: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
2497: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
2498: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
2499:
2500: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
2501:
2502: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
2503:
2504: IF call_status = FALSE THEN
2501:
2502: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
2503:
2504: IF call_status = FALSE THEN
2505: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import failed');
2506: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
2507: fnd_msg_pub.add;
2508: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2509: END IF;
2506: fnd_message.set_name('PSP','PSP_TR_GL_IMP_FAILED');
2507: fnd_msg_pub.add;
2508: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2509: END IF;
2510: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Journal Import completed');
2511: END IF;
2512: END IF;
2513:
2514: OPEN gl_group_id_cur;
2514: OPEN gl_group_id_cur;
2515: FETCH gl_group_id_cur BULK COLLECT INTO r_groups.group_id;
2516: CLOSE gl_group_id_cur;
2517:
2518: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_groups.group_id.COUNT: ' || r_groups.group_id.COUNT);
2519:
2520: FOR recno IN 1..r_groups.group_id.COUNT
2521: LOOP
2522: 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));
2518: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_groups.group_id.COUNT: ' || r_groups.group_id.COUNT);
2519:
2520: FOR recno IN 1..r_groups.group_id.COUNT
2521: LOOP
2522: 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));
2523:
2524: gl_enc_tie_back(p_payroll_action_id,
2525: r_groups.group_id(recno),
2526: g_business_group_id,
2529: l_return_status);
2530:
2531: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2532: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2533: 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));
2534: ELSE
2535: 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));
2536: END IF;
2537: END LOOP;
2531: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2532: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2533: 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));
2534: ELSE
2535: 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));
2536: END IF;
2537: END LOOP;
2538:
2539: FORALL recno IN 1..r_groups.group_id.COUNT
2541: WHERE group_id = r_groups.group_id(recno)
2542: AND user_je_source_name = l_user_je_source
2543: AND set_of_books_id = g_set_of_books_id;
2544:
2545: 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');
2546:
2547: FORALL recno IN 1..r_groups.group_id.COUNT
2548: UPDATE psp_enc_summary_lines
2549: SET group_id = NULL
2549: SET group_id = NULL
2550: WHERE group_id = r_groups.group_id(recno)
2551: AND status_code = 'N';
2552:
2553: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Set the un-imported summary lines to New status');
2554:
2555: COMMIT;
2556: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' COMMITted gl_enc_tie_back');
2557:
2552:
2553: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Set the un-imported summary lines to New status');
2554:
2555: COMMIT;
2556: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' COMMITted gl_enc_tie_back');
2557:
2558: p_return_status := fnd_api.g_ret_sts_success;
2559: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2560: EXCEPTION
2555: COMMIT;
2556: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' COMMITted gl_enc_tie_back');
2557:
2558: p_return_status := fnd_api.g_ret_sts_success;
2559: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2560: EXCEPTION
2561: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2562: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2563: p_return_status := fnd_api.g_ret_sts_unexp_error;
2560: EXCEPTION
2561: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2562: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2563: p_return_status := fnd_api.g_ret_sts_unexp_error;
2564: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2565: WHEN OTHERS THEN
2566: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2567: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','TR_TO_GL_INT');
2568: p_return_status := fnd_api.g_ret_sts_unexp_error;
2565: WHEN OTHERS THEN
2566: g_error_api_path := 'TR_TO_GL_INT:'||g_error_api_path;
2567: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','TR_TO_GL_INT');
2568: p_return_status := fnd_api.g_ret_sts_unexp_error;
2569: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving TR_TO_GL_INT');
2570: END tr_to_gl_int;
2571: -- End of changes for Create and Update multi thread enh.
2572:
2573: -- ##########################################################################
3470: SELECT DISTINCT enc_control_id
3471: FROM psp_enc_summary_lines
3472: WHERE group_id = p_group_id;
3473: BEGIN
3474: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3475:
3476: OPEN int_count_cur;
3477: FETCH int_count_cur INTO l_cnt;
3478: CLOSE int_count_cur;
3476: OPEN int_count_cur;
3477: FETCH int_count_cur INTO l_cnt;
3478: CLOSE int_count_cur;
3479:
3480: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
3481:
3482: IF l_cnt = 0 THEN
3483: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3484: RETURN TRUE;
3479:
3480: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
3481:
3482: IF l_cnt = 0 THEN
3483: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3484: RETURN TRUE;
3485: ELSIF l_cnt > 0 THEN
3486: DELETE FROM gl_interface
3487: WHERE user_je_source_name = 'OLD'
3486: DELETE FROM gl_interface
3487: WHERE user_je_source_name = 'OLD'
3488: AND set_of_books_id = p_set_of_books_id
3489: AND group_id = p_group_id;
3490: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface');
3491: DELETE FROM psp_enc_summary_lines
3492: WHERE group_id = p_group_id;
3493: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
3494: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3489: AND group_id = p_group_id;
3490: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface');
3491: DELETE FROM psp_enc_summary_lines
3492: WHERE group_id = p_group_id;
3493: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
3494: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3495: RETURN FALSE;
3496: END IF;
3497: EXCEPTION
3490: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface');
3491: DELETE FROM psp_enc_summary_lines
3492: WHERE group_id = p_group_id;
3493: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
3494: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3495: RETURN FALSE;
3496: END IF;
3497: EXCEPTION
3498: WHEN OTHERS THEN
3495: RETURN FALSE;
3496: END IF;
3497: EXCEPTION
3498: WHEN OTHERS THEN
3499: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3500: RETURN TRUE;
3501: END PROCESS_COMPLETE;
3502:
3503: BEGIN
3500: RETURN TRUE;
3501: END PROCESS_COMPLETE;
3502:
3503: BEGIN
3504: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK procedure');
3505: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_group_id: ' || p_group_id);
3506: l_suspense_ac_failed := 'N';
3507: l_susp_ac_found := 'TRUE';
3508: l_susp_exception := NULL;
3501: END PROCESS_COMPLETE;
3502:
3503: BEGIN
3504: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK procedure');
3505: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' p_group_id: ' || p_group_id);
3506: l_suspense_ac_failed := 'N';
3507: l_susp_ac_found := 'TRUE';
3508: l_susp_exception := NULL;
3509: l_suspense_ac_not_found := 'N';
3512: OPEN int_count_cur;
3513: FETCH int_count_cur INTO l_cnt_gl_interface;
3514: CLOSE int_count_cur;
3515:
3516: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gl_interface: ' || l_cnt_gl_interface);
3517:
3518: IF (l_cnt_gl_interface > 0) THEN
3519: OPEN gl_tie_back_reject_cur;
3520: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3519: OPEN gl_tie_back_reject_cur;
3520: FETCH gl_tie_back_reject_cur BULK COLLECT INTO r_interface.status, r_interface.enc_summary_line_id;
3521: CLOSE gl_tie_back_reject_cur;
3522:
3523: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.status.COUNT: ' || r_interface.status.COUNT);
3524:
3525: FORALL recno IN 1..r_interface.status.COUNT
3526: UPDATE psp_enc_summary_lines
3527: SET interface_status = r_interface.status(recno),
3529: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3530: AND r_interface.status(recno) <> 'P'
3531: AND SUBSTR(r_interface.status(recno), 1, 1) <> 'W';
3532:
3533: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated rejected lines status in psp_enc_summary_lines');
3534:
3535: OPEN assign_susp_ac_cur;
3536: FETCH assign_susp_ac_cur BULK COLLECT INTO r_suspense_ac.row_id, r_suspense_ac.encumbrance_date,
3537: r_suspense_ac.enc_start_date, r_suspense_ac.enc_end_date, r_suspense_ac.assignment_id,
3702: WHERE enc_control_id IN (SELECT pesl.enc_control_id
3703: FROM psp_enc_summary_lines pesl
3704: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3705:
3706: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' TieBack SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3707:
3708: FORALL recno IN 1..r_interface.status.COUNT
3709: UPDATE psp_enc_controls
3710: SET gl_phase = 'Summarize'
3712: FROM psp_enc_summary_lines pesl
3713: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3714: AND pesl.status_code = 'N');
3715:
3716: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Summarize SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3717: ELSIF (l_cnt_gl_interface = 0) THEN
3718: OPEN gl_tie_back_success_cur;
3719: 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;
3720: CLOSE gl_tie_back_success_cur;
3718: OPEN gl_tie_back_success_cur;
3719: 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;
3720: CLOSE gl_tie_back_success_cur;
3721:
3722: 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);
3723:
3724: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3725: UPDATE psp_enc_summary_lines
3726: SET status_code = 'A'
3725: UPDATE psp_enc_summary_lines
3726: SET status_code = 'A'
3727: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno);
3728:
3729: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines');
3730:
3731: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3732: INSERT INTO psp_enc_lines_history
3733: (enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
3761: orig_gl_code_combination_id, orig_project_id, orig_task_id, orig_award_id,
3762: orig_expenditure_org_id, orig_expenditure_type
3763: FROM psp_enc_lines
3764: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno);
3765: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied successfully summarized and transferred lines into psp_enc_lines_history');
3766:
3767: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3768: DELETE FROM psp_enc_lines
3769: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno);
3766:
3767: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3768: DELETE FROM psp_enc_lines
3769: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno);
3770: 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');
3771:
3772: FORALL recno IN 1..r_interface.enc_control_id.COUNT
3773: UPDATE psp_enc_controls pec
3774: SET gl_phase = 'TieBack',
3774: SET gl_phase = 'TieBack',
3775: summ_gl_dr_amount = NVL(summ_gl_dr_amount, 0) + DECODE(r_interface.dr_cr_flag(recno), 'D', r_interface.summary_amount(recno), 0),
3776: summ_gl_cr_amount = NVL(summ_gl_cr_amount, 0) + DECODE(r_interface.dr_cr_flag(recno), 'C', r_interface.summary_amount(recno), 0)
3777: WHERE enc_control_id = r_interface.enc_control_id(recno);
3778: 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');
3779: END IF;
3780: ELSE
3781: g_process_complete := FALSE;
3782: END IF;
3781: g_process_complete := FALSE;
3782: END IF;
3783:
3784: p_return_status := fnd_api.g_ret_sts_success;
3785: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
3786: EXCEPTION
3787: WHEN NO_PROFILE_EXISTS THEN
3788: g_error_api_path := SUBSTR('GL_ENC_TIE_BACK:'||g_error_api_path,1,230);
3789: fnd_message.set_name('PSP','PSP_NO_PROFILE_EXISTS');
3805: p_return_status := fnd_api.g_ret_sts_success;
3806: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3807: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
3808: p_return_status := fnd_api.g_ret_sts_unexp_error;
3809: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
3810: WHEN OTHERS THEN
3811: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
3812: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GL_ENC_TIE_BACK');
3813: p_return_status := fnd_api.g_ret_sts_unexp_error;
3810: WHEN OTHERS THEN
3811: g_error_api_path := 'GL_ENC_TIE_BACK:'||g_error_api_path;
3812: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GL_ENC_TIE_BACK');
3813: p_return_status := fnd_api.g_ret_sts_unexp_error;
3814: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK');
3815: END gl_enc_tie_back;
3816: -- End of changes for Create and Update multi thread enh.
3817:
3818: -- ##########################################################################
4653: AND gms_batch_name IS NULL
4654: AND payroll_action_id = p_payroll_action_id;
4655:
4656: BEGIN
4657: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering TR_TO_GMS_INT');
4658:
4659: l_tie_back_failed:= NULL;
4660: l_rec_no := 0;
4661: l_rec_count := 0;
4682:
4683: OPEN operating_unit_csr;
4684: FETCH operating_unit_csr BULK COLLECT INTO org_id_tab;
4685: CLOSE operating_unit_csr;
4686: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' org_id_tab.COUNT: ' || org_id_tab.COUNT);
4687:
4688: FOR I in 1..org_id_tab.COUNT
4689: LOOP
4690: SELECT TO_CHAR(psp_gms_batch_name_s.NEXTVAL)
4689: LOOP
4690: SELECT TO_CHAR(psp_gms_batch_name_s.NEXTVAL)
4691: INTO gms_batch_name_tab(i)
4692: FROM DUAL;
4693: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gms_batch_name_tab(' || I || '): ' || gms_batch_name_tab(i));
4694: END LOOP;
4695:
4696:
4697: FORALL I IN 1..org_id_tab.count
4742: END LOOP;
4743: END LOOP;
4744: */
4745:
4746: 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);
4747: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Verifying interface records for errors');
4748:
4749: OPEN employee_number_cur;
4750: FETCH employee_number_cur BULK COLLECT INTO r_person.person_id, r_person.employee_number;
4743: END LOOP;
4744: */
4745:
4746: 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);
4747: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Verifying interface records for errors');
4748:
4749: OPEN employee_number_cur;
4750: FETCH employee_number_cur BULK COLLECT INTO r_person.person_id, r_person.employee_number;
4751: CLOSE employee_number_cur;
4802: END IF;
4803: END LOOP;
4804:
4805: IF l_raise_error THEN
4806: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Interface records have errors');
4807: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4808: END IF;
4809:
4810: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
4806: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Interface records have errors');
4807: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4808: END IF;
4809:
4810: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
4811: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Computing PA week ending date(s)');
4812:
4813: FOR recno IN 1..r_interface.txn_interface_id.COUNT
4814: LOOP
4807: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4808: END IF;
4809:
4810: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed interface records for errors');
4811: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Computing PA week ending date(s)');
4812:
4813: FOR recno IN 1..r_interface.txn_interface_id.COUNT
4814: LOOP
4815: psp_general.get_gms_effective_date(r_interface.person_id(recno), r_interface.gms_overriding_date(recno));
4819: END LOOP;
4820: -- set the context again to multiple
4821: mo_global.set_policy_context('M', null);
4822:
4823: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed computation of PA week ending date(s)');
4824:
4825: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
4826: UPDATE psp_enc_summary_lines pesl
4827: SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
4827: SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
4828: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
4829: AND TRUNC(r_interface.effective_date(recno)) <> TRUNC(r_interface.gms_overriding_date(recno));
4830:
4831: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated GMS Override Date');
4832:
4833: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
4834: INSERT INTO pa_transaction_interface_all
4835: (txn_interface_id, transaction_source,
4860: r_interface.attribute7(recno), r_interface.attribute8(recno),
4861: r_interface.attribute9(recno), r_interface.attribute10(recno),
4862: g_business_group_id,r_interface.unmatched_nve_txn_flag(recno));
4863:
4864: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into pa_transaction_interface_all');
4865:
4866: FOR recno IN 1..r_interface.txn_interface_id.COUNT
4867: LOOP
4868: GMS_REC.TXN_INTERFACE_ID := r_interface.txn_interface_id(recno);
4891: fnd_msg_pub.add;
4892: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4893: END IF;
4894: END LOOP;
4895: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserted into gms_transaction_interface_all');
4896: IF r_interface.txn_interface_id.COUNT > 0 THEN
4897:
4898: FOR request_counter IN 1..org_id_tab.count
4899: LOOP
4910: l_gms_transaction_source,
4911: gms_batch_name_tab(request_counter));
4912:
4913: IF req_id_tab(request_counter) = 0 THEN
4914: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submission of Transaction Import Failed');
4915: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
4916: fnd_msg_pub.add;
4917: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4918: ELSE
4915: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
4916: fnd_msg_pub.add;
4917: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4918: ELSE
4919: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Submitted Transaction Import');
4920:
4921: OPEN enc_control_id_cur;
4922: FETCH enc_control_id_cur BULK COLLECT INTO r_enc_controls.enc_control_id;
4923: CLOSE enc_control_id_cur;
4926: UPDATE psp_enc_controls
4927: SET gms_phase = 'Transfer'
4928: WHERE enc_control_id = r_enc_controls.enc_control_id(I);
4929:
4930: 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);
4931:
4932: r_enc_controls.enc_control_id.DELETE;
4933: END IF;
4934: END LOOP;
4935: COMMIT;
4936: -- set the context again to multiple
4937: mo_global.set_policy_context('M', null);
4938:
4939: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
4940: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
4941: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
4942:
4943: FOR I IN 1..org_id_tab.count
4937: mo_global.set_policy_context('M', null);
4938:
4939: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
4940: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
4941: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
4942:
4943: FOR I IN 1..org_id_tab.count
4944: LOOP
4945: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for comlpetion of Transaction Import');
4941: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
4942:
4943: FOR I IN 1..org_id_tab.count
4944: LOOP
4945: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for comlpetion of Transaction Import');
4946: call_status := fnd_concurrent.wait_for_request(req_id_tab(I), 10, 0, rphase, rstatus, dphase, dstatus, message);
4947:
4948: IF call_status = FALSE then
4949: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import failed');
4945: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for comlpetion of Transaction Import');
4946: call_status := fnd_concurrent.wait_for_request(req_id_tab(I), 10, 0, rphase, rstatus, dphase, dstatus, message);
4947:
4948: IF call_status = FALSE then
4949: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import failed');
4950: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
4951: fnd_msg_pub.add;
4952: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4953: END IF;
4950: fnd_message.set_name('PSP','PSP_TR_GMS_IMP_FAILED');
4951: fnd_msg_pub.add;
4952: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4953: END IF;
4954: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Transaction Import completed');
4955: END LOOP;
4956: END IF;
4957:
4958: OPEN gms_batch_name_cur;
4958: OPEN gms_batch_name_cur;
4959: FETCH gms_batch_name_cur BULK COLLECT INTO r_gms_batch.gms_batch_name;
4960: CLOSE gms_batch_name_cur;
4961:
4962: 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);
4963:
4964: FOR recno IN 1..r_gms_batch.gms_batch_name.COUNT
4965: LOOP
4966: 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));
4962: 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);
4963:
4964: FOR recno IN 1..r_gms_batch.gms_batch_name.COUNT
4965: LOOP
4966: 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));
4967: gms_enc_tie_back(p_payroll_action_id,
4968: r_gms_batch.gms_batch_name(recno),
4969: g_business_group_id,
4970: g_set_of_books_id,
4970: g_set_of_books_id,
4971: l_return_status);
4972:
4973: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4974: 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));
4975: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4976: END IF;
4977: 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));
4978: END LOOP;
4973: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4974: 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));
4975: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4976: END IF;
4977: 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));
4978: END LOOP;
4979:
4980: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
4981: UPDATE psp_enc_summary_lines pesl
5004: SET gms_batch_name = NULL
5005: WHERE gms_batch_name = r_gms_batch.gms_batch_name(recno)
5006: AND status_code = 'N';
5007:
5008: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Set the un-imported summary lines to New status');
5009:
5010: COMMIT;
5011:
5012: p_return_status := fnd_api.g_ret_sts_success;
5959: AND batch_name = p_gms_batch_name
5960: AND transaction_Status_code in ('P','I')
5961: GROUP BY transaction_status_code;
5962: BEGIN
5963: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
5964:
5965: OPEN transaction_status_cur;
5966: FETCH transaction_status_cur INTO l_cnt, l_status;
5967: IF (transaction_status_cur%ROWCOUNT = 0) THEN
5968: l_cnt := 0;
5969: END IF;
5970: CLOSE transaction_status_cur;
5971:
5972: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
5973:
5974: IF l_cnt = 0 THEN
5975: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
5976: RETURN TRUE;
5971:
5972: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt: ' || l_cnt);
5973:
5974: IF l_cnt = 0 THEN
5975: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
5976: RETURN TRUE;
5977: ELSIF l_cnt > 0 THEN
5978: IF l_status = 'P' THEN
5979: DELETE FROM pa_transaction_interface_all
5978: IF l_status = 'P' THEN
5979: DELETE FROM pa_transaction_interface_all
5980: WHERE transaction_source = 'GOLDE'
5981: AND batch_name = p_gms_batch_name;
5982: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from pa_trancsaction_interface_all');
5983:
5984: DELETE FROM gms_transaction_interface_all
5985: WHERE transaction_source = 'GOLDE'
5986: AND batch_name = p_gms_batch_name;
5983:
5984: DELETE FROM gms_transaction_interface_all
5985: WHERE transaction_source = 'GOLDE'
5986: AND batch_name = p_gms_batch_name;
5987: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all');
5988:
5989: DELETE FROM psp_enc_summary_lines
5990: WHERE gms_batch_name = p_gms_batch_name;
5991: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
5987: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all');
5988:
5989: DELETE FROM psp_enc_summary_lines
5990: WHERE gms_batch_name = p_gms_batch_name;
5991: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
5992:
5993: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
5994: RETURN FALSE;
5995: ELSIF l_status = 'I' THEN
5989: DELETE FROM psp_enc_summary_lines
5990: WHERE gms_batch_name = p_gms_batch_name;
5991: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
5992:
5993: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
5994: RETURN FALSE;
5995: ELSIF l_status = 'I' THEN
5996: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
5997: RETURN FALSE;
5992:
5993: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
5994: RETURN FALSE;
5995: ELSIF l_status = 'I' THEN
5996: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE');
5997: RETURN FALSE;
5998: END IF;
5999: END IF;
6000: EXCEPTION
6001: WHEN OTHERS THEN
6002: RETURN TRUE;
6003: END PROCESS_COMPLETE;
6004: BEGIN
6005: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GMS_ENC_TIE_BACK');
6006:
6007: IF (PROCESS_COMPLETE) THEN
6008: OPEN txn_interface_count_cur;
6009: FETCH txn_interface_count_cur INTO l_cnt_gms_interface;
6007: IF (PROCESS_COMPLETE) THEN
6008: OPEN txn_interface_count_cur;
6009: FETCH txn_interface_count_cur INTO l_cnt_gms_interface;
6010: CLOSE txn_interface_count_cur;
6011: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_cnt_gms_interface: ' || l_cnt_gms_interface);
6012:
6013: IF l_cnt_gms_interface > 0 THEN
6014: OPEN gms_tie_back_reject_cur;
6015: FETCH gms_tie_back_reject_cur BULK COLLECT INTO r_reject_recs.reason_code, r_reject_recs.enc_summary_line_id,
6015: FETCH gms_tie_back_reject_cur BULK COLLECT INTO r_reject_recs.reason_code, r_reject_recs.enc_summary_line_id,
6016: r_reject_recs.txn_status_code;
6017: CLOSE gms_tie_back_reject_cur;
6018:
6019: 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);
6020:
6021: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6022: UPDATE psp_enc_summary_lines
6023: SET interface_status = r_reject_recs.reason_code(recno),
6024: status_code = 'R'
6025: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6026: AND r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR');
6027:
6028: 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');
6029:
6030: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6031: UPDATE psp_enc_summary_lines
6032: SET interface_status = r_reject_recs.reason_code(recno),
6033: status_code = 'A'
6034: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6035: AND r_reject_recs.txn_status_code(recno) = 'A';
6036:
6037: 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');
6038:
6039: OPEN get_success_recs_cur;
6040: FETCH get_success_recs_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6041: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6040: FETCH get_success_recs_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6041: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6042: CLOSE get_success_recs_cur;
6043:
6044: 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);
6045:
6046: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6047: INSERT INTO psp_enc_lines_history
6048: (enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
6076: orig_gl_code_combination_id, orig_project_id, orig_task_id, orig_award_id,
6077: orig_expenditure_org_id, orig_expenditure_type
6078: FROM psp_enc_lines
6079: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6080: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied successfully summarized and transferred lines into psp_enc_lines_history');
6081:
6082: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6083: DELETE FROM psp_enc_lines
6084: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6081:
6082: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6083: DELETE FROM psp_enc_lines
6084: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6085: 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');
6086:
6087: OPEN assign_susp_ac_cur;
6088: FETCH assign_susp_ac_cur BULK COLLECT INTO r_suspense_ac.row_id, r_suspense_ac.encumbrance_date,
6089: r_suspense_ac.enc_start_date, r_suspense_ac.enc_end_date, r_suspense_ac.person_id,
6189: fnd_message.set_token('END_DATE', l_effective_date);
6190: fnd_message.set_token('GL', l_gl_description);
6191: fnd_message.set_token('AUTOPOP_STATUS', l_autopop_error);
6192: g_warning_message := fnd_message.get;
6193: fnd_file.put_line(fnd_file.log, g_warning_message);
6194: ELSE
6195: l_susp_glccid := l_new_gl_code_combination_id;
6196: END IF;
6197: END IF;
6254: fnd_message.set_token('EO', l_exp_org_name);
6255: fnd_message.set_token('ET', l_expenditure_type);
6256: fnd_message.set_token('AUTOPOP_STATUS', l_autopop_error);
6257: g_warning_message := fnd_message.get;
6258: fnd_file.put_line(fnd_file.log, g_warning_message);
6259: ELSE
6260: l_expenditure_type := l_new_expenditure_type;
6261: END IF;
6262: END IF;
6363: 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),
6364: 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)
6365: WHERE enc_control_id = r_success_recs.enc_control_id(recno);
6366:
6367: 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');
6368:
6369: r_reject_recs.enc_summary_line_id.DELETE;
6370: r_reject_recs.enc_control_id.DELETE;
6371: r_reject_recs.reason_code.DELETE;
6379: FETCH gms_tie_back_success_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6380: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;
6381: CLOSE gms_tie_back_success_cur;
6382:
6383: 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);
6384:
6385: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6386: UPDATE psp_enc_summary_lines
6387: SET status_code = 'A'
6385: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6386: UPDATE psp_enc_summary_lines
6387: SET status_code = 'A'
6388: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6389: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines');
6390:
6391: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6392: INSERT INTO psp_enc_lines_history
6393: (enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,
6417: attribute7, attribute8, attribute9, attribute10,
6418: payroll_action_id, hierarchy_code, hierarchy_start_date, hierarchy_end_date
6419: FROM psp_enc_lines
6420: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6421: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Copied successfully summarized and transferred lines into psp_enc_lines_history');
6422:
6423: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6424: DELETE FROM psp_enc_lines
6425: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6422:
6423: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6424: DELETE FROM psp_enc_lines
6425: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6426: 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');
6427:
6428: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6429: UPDATE psp_enc_controls pec
6430: SET gms_phase = 'TieBack',
6431: 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),
6432: 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)
6433: WHERE enc_control_id = r_success_recs.enc_control_id(recno);
6434:
6435: 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');
6436:
6437: r_success_recs.enc_summary_line_id.DELETE;
6438: r_success_recs.enc_control_id.DELETE;
6439: r_success_recs.reason_code.DELETE;
6443: g_process_complete := FALSE;
6444: END IF;
6445:
6446: p_return_status := fnd_api.g_ret_sts_success;
6447: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6448: EXCEPTION
6449: WHEN NO_PROFILE_EXISTS THEN
6450: g_error_api_path := SUBSTR('GMS_ENC_TIE_BACK:'||g_error_api_path,1,230);
6451: fnd_message.set_name('PSP','PSP_NO_PROFILE_EXISTS');
6467: p_return_status := fnd_api.g_ret_sts_success;
6468: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6469: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6470: p_return_status := fnd_api.g_ret_sts_unexp_error;
6471: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6472: WHEN OTHERS THEN
6473: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6474: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GMS_ENC_TIE_BACK');
6475: p_return_status := fnd_api.g_ret_sts_unexp_error;
6472: WHEN OTHERS THEN
6473: g_error_api_path := 'GMS_ENC_TIE_BACK:'||g_error_api_path;
6474: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','GMS_ENC_TIE_BACK');
6475: p_return_status := fnd_api.g_ret_sts_unexp_error;
6476: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK');
6477: END gms_enc_tie_back;
6478: -- End of changes for Create and Update multi thread enh.
6479:
6480: PROCEDURE create_sum_lines (p_payroll_action_id IN NUMBER,
6632: t_sum_lines.attribute10(recno), t_sum_lines.org_id(recno),
6633: p_payroll_action_id, SYSDATE,
6634: l_last_updated_by, l_last_update_login,
6635: l_last_updated_by, SYSDATE);
6636: 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);
6637:
6638: IF (g_dff_grouping_option = 'Y') THEN
6639: FORALL recno IN 1..t_sum_lines.enc_summary_line_id.COUNT
6640: UPDATE psp_enc_lines pel
6710: ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
6711:
6712: l_nlines_counter NUMBER(15);
6713: BEGIN
6714: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering UPDATE_HIERARCHY_DATES');
6715: l_nlines_counter := 0;
6716:
6717: FOR asg_no IN 1..l_asgs.payroll_id.COUNT
6718: LOOP
6831: AND enc_start_date <= t_enc_nlines.enc_end_date(recno)
6832: AND enc_end_date >= t_enc_nlines.enc_start_date(recno);
6833:
6834: p_return_status := fnd_api.g_ret_sts_success;
6835: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
6836: EXCEPTION
6837: WHEN OTHERS THEN
6838: g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
6839: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN', ' UPDATE_HIERARCHY_DATES');
6837: WHEN OTHERS THEN
6838: g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
6839: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN', ' UPDATE_HIERARCHY_DATES');
6840: p_return_status := fnd_api.g_ret_sts_unexp_error;
6841: fnd_file.put_line(fnd_file.log, 'SQLCODE: ' || SQLCODE || ' SQLERRM: ' || SQLERRM);
6842: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
6843: END update_hierarchy_dates;
6844:
6845: PROCEDURE log_st_warnings IS
6838: g_error_api_path := SUBSTR(' UPDATE_HIERARCHY_DATES:'||g_error_api_path,1,230);
6839: fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN', ' UPDATE_HIERARCHY_DATES');
6840: p_return_status := fnd_api.g_ret_sts_unexp_error;
6841: fnd_file.put_line(fnd_file.log, 'SQLCODE: ' || SQLCODE || ' SQLERRM: ' || SQLERRM);
6842: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving UPDATE_HIERARCHY_DATES');
6843: END update_hierarchy_dates;
6844:
6845: PROCEDURE log_st_warnings IS
6846: l_request_id NUMBER(15);
7016: p_error_status IN VARCHAR2 DEFAULT NULL) IS
7017: l_warning_ind NUMBER(15);
7018: BEGIN
7019: l_warning_ind := st_warnings.start_date.COUNT;
7020: fnd_file.put_line(fnd_file.log, 'Entering add_st_warnings');
7021: hr_utility.trace('p_start_date: ' || p_start_date || ' p_end_date: ' || p_end_date ||
7022: ' p_assignment_id: ' || p_assignment_id || ' p_payroll_id: ' || p_payroll_id ||
7023: ' p_element_type_id: ' || p_element_type_id || ' p_gl_ccid: ' || p_gl_ccid ||
7024: ' p_project_id: ' || p_project_id || ' p_task_id: ' || p_task_id ||
7075: st_warnings.effective_date(l_warning_ind) := p_effective_date;
7076: END IF;
7077:
7078: hr_utility.trace('st_warnings.start_date.COUNT: ' || st_warnings.start_date.COUNT);
7079: fnd_file.put_line(fnd_file.log, 'Leaving add_st_warnings');
7080: END add_st_warnings;
7081:
7082: PROCEDURE move_rej_lines_to_arch (p_payroll_action_id IN NUMBER) IS
7083: BEGIN
7136: WHERE payroll_action_id = p_payroll_action_id
7137: AND status_code = 'R';
7138:
7139: IF (SQL%ROWCOUNT > 0) THEN
7140: fnd_file.put_line(fnd_file.log, 'Moved rejected lines (if any) to archival table as they are no longer useful');
7141: END IF;
7142:
7143: DELETE psp_enc_summary_lines
7144: WHERE payroll_action_id = p_payroll_action_id