[Home] [Help]
286: BEGIN
287: l_module_name := g_module_name || '.extract';
288: p_error_code := g_SUCCESS;
289:
290: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
291: fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
292: END IF;
293:
294: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
287: l_module_name := g_module_name || '.extract';
288: p_error_code := g_SUCCESS;
289:
290: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
291: fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
292: END IF;
293:
294: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
295: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
290: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
291: fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
292: END IF;
293:
294: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
295: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
296: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_gl_period = '||p_gl_period);
297: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_alc = '||p_alc);
298: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_run_mode = '||p_run_mode);
291: fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
292: END IF;
293:
294: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
295: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
296: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_gl_period = '||p_gl_period);
297: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_alc = '||p_alc);
298: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_run_mode = '||p_run_mode);
299: END IF;
292: END IF;
293:
294: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
295: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
296: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_gl_period = '||p_gl_period);
297: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_alc = '||p_alc);
298: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_run_mode = '||p_run_mode);
299: END IF;
300:
293:
294: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
295: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
296: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_gl_period = '||p_gl_period);
297: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_alc = '||p_alc);
298: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_run_mode = '||p_run_mode);
299: END IF;
300:
301: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
294: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
295: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
296: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_gl_period = '||p_gl_period);
297: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_alc = '||p_alc);
298: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_run_mode = '||p_run_mode);
299: END IF;
300:
301: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
302: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'SELECT FROM gl_period_statuses');
297: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_alc = '||p_alc);
298: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_run_mode = '||p_run_mode);
299: END IF;
300:
301: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
302: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'SELECT FROM gl_period_statuses');
303: END IF;
304: BEGIN
305: SELECT start_date,
298: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_run_mode = '||p_run_mode);
299: END IF;
300:
301: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
302: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'SELECT FROM gl_period_statuses');
303: END IF;
304: BEGIN
305: SELECT start_date,
306: end_date,
312: WHERE glp.period_name = p_gl_period
313: AND glp.ledger_id = p_set_of_books_id
314: AND glp.application_id = 101;
315: p_end_period_date := l_end_date;
316: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
317: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_start_date ='||l_start_date);
318: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_end_date ='||l_end_date);
319: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_yr_start_date ='||l_yr_start_date);
320: END IF;
313: AND glp.ledger_id = p_set_of_books_id
314: AND glp.application_id = 101;
315: p_end_period_date := l_end_date;
316: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
317: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_start_date ='||l_start_date);
318: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_end_date ='||l_end_date);
319: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_yr_start_date ='||l_yr_start_date);
320: END IF;
321: EXCEPTION
314: AND glp.application_id = 101;
315: p_end_period_date := l_end_date;
316: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
317: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_start_date ='||l_start_date);
318: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_end_date ='||l_end_date);
319: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_yr_start_date ='||l_yr_start_date);
320: END IF;
321: EXCEPTION
322: WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
315: p_end_period_date := l_end_date;
316: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
317: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_start_date ='||l_start_date);
318: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_end_date ='||l_end_date);
319: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_yr_start_date ='||l_yr_start_date);
320: END IF;
321: EXCEPTION
322: WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
323: p_error_code := g_FAILURE;
321: EXCEPTION
322: WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
323: p_error_code := g_FAILURE;
324: p_error_desc := 'No Such Period (' || p_gl_period|| ') exists';
325: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
326: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
327: WHEN OTHERS THEN
328: p_error_code := g_FAILURE;
329: p_error_desc := SQLERRM;
322: WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
323: p_error_code := g_FAILURE;
324: p_error_desc := 'No Such Period (' || p_gl_period|| ') exists';
325: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
326: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
327: WHEN OTHERS THEN
328: p_error_code := g_FAILURE;
329: p_error_desc := SQLERRM;
330: l_location := l_module_name||'select_gl_period_statuses1';
327: WHEN OTHERS THEN
328: p_error_code := g_FAILURE;
329: p_error_desc := SQLERRM;
330: l_location := l_module_name||'select_gl_period_statuses1';
331: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
332: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
333: END ;
334:
335: IF (p_error_code = g_SUCCESS) THEN
328: p_error_code := g_FAILURE;
329: p_error_desc := SQLERRM;
330: l_location := l_module_name||'select_gl_period_statuses1';
331: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
332: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
333: END ;
334:
335: IF (p_error_code = g_SUCCESS) THEN
336:
334:
335: IF (p_error_code = g_SUCCESS) THEN
336:
337: l_last_fetch := FALSE;
338: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
339: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'OPEN current_224_cur');
340: END IF;
341: OPEN current_224_cur (p_set_of_books_id, p_alc, TRUNC(l_end_date)+1);
342: LOOP
335: IF (p_error_code = g_SUCCESS) THEN
336:
337: l_last_fetch := FALSE;
338: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
339: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'OPEN current_224_cur');
340: END IF;
341: OPEN current_224_cur (p_set_of_books_id, p_alc, TRUNC(l_end_date)+1);
342: LOOP
343: FETCH current_224_cur
379: IF (l_gl_period.count = 0 AND l_last_fetch) THEN
380: EXIT;
381: END IF;
382:
383: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
384: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'FETCH current_224_cur');
385: END IF;
386: FOR i IN l_rowid.first .. l_rowid.last LOOP
387: BEGIN
380: EXIT;
381: END IF;
382:
383: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
384: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'FETCH current_224_cur');
385: END IF;
386: FOR i IN l_rowid.first .. l_rowid.last LOOP
387: BEGIN
388: SELECT start_date,
396: EXCEPTION
397: WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
398: p_error_code := g_FAILURE;
399: p_error_desc := 'No Such Period (' || l_gl_period(i)|| ') exists';
400: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
401: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
402: WHEN OTHERS THEN
403: p_error_code := g_FAILURE;
404: p_error_desc := SQLERRM;
397: WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
398: p_error_code := g_FAILURE;
399: p_error_desc := 'No Such Period (' || l_gl_period(i)|| ') exists';
400: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
401: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
402: WHEN OTHERS THEN
403: p_error_code := g_FAILURE;
404: p_error_desc := SQLERRM;
405: l_location := l_module_name||'select_gl_period_statuses2';
402: WHEN OTHERS THEN
403: p_error_code := g_FAILURE;
404: p_error_desc := SQLERRM;
405: l_location := l_module_name||'select_gl_period_statuses2';
406: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
407: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
408: END;
409:
410: IF (p_error_code = g_SUCCESS) THEN
403: p_error_code := g_FAILURE;
404: p_error_desc := SQLERRM;
405: l_location := l_module_name||'select_gl_period_statuses2';
406: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
407: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
408: END;
409:
410: IF (p_error_code = g_SUCCESS) THEN
411: IF (l_accomplish_date(i) BETWEEN l_start_date AND l_end_date) THEN
521: WHEN OTHERS THEN
522: p_error_code := g_FAILURE;
523: p_error_desc := SQLERRM;
524: l_location := l_module_name||'select_fv_sf224_audits';
525: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
526: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
527: END;
528: END IF;
529: END IF;
522: p_error_code := g_FAILURE;
523: p_error_desc := SQLERRM;
524: l_location := l_module_name||'select_fv_sf224_audits';
525: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
526: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
527: END;
528: END IF;
529: END IF;
530: END IF;
592: WHEN OTHERS THEN
593: p_error_code := g_FAILURE;
594: p_error_desc := SQLERRM;
595: l_location := l_module_name||'update_fv_interagency_funds_all';
596: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
597: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
598: END;
599: END IF;
600:
593: p_error_code := g_FAILURE;
594: p_error_desc := SQLERRM;
595: l_location := l_module_name||'update_fv_interagency_funds_all';
596: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
597: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
598: END;
599: END IF;
600:
601: IF (l_processed_flag(i) = 'Y' AND l_reported_month(i) like '%CURRENT%') then
614: WHEN OTHERS THEN
615: p_error_code := g_FAILURE;
616: p_error_desc := SQLERRM;
617: l_location := l_module_name||'update_fv_refunds_voids_all';
618: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
619: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
620: END;
621: END IF;
622: END IF;
615: p_error_code := g_FAILURE;
616: p_error_desc := SQLERRM;
617: l_location := l_module_name||'update_fv_refunds_voids_all';
618: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
619: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
620: END;
621: END IF;
622: END IF;
623: END IF;
648: WHEN OTHERS THEN
649: p_error_code := g_FAILURE;
650: p_error_desc := SQLERRM;
651: l_location := l_module_name||'select_fv_alc_business_activity_v';
652: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
653: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
654: END;
655: END IF;
656:
649: p_error_code := g_FAILURE;
650: p_error_desc := SQLERRM;
651: l_location := l_module_name||'select_fv_alc_business_activity_v';
652: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
653: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
654: END;
655: END IF;
656:
657: IF (p_error_code = g_SUCCESS) THEN
676: WHEN OTHERS THEN
677: p_error_code := g_FAILURE;
678: p_error_desc := SQLERRM;
679: l_location := l_module_name||'select_fv_sf224_map';
680: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
681: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
682: END;
683: END IF;
684:
677: p_error_code := g_FAILURE;
678: p_error_desc := SQLERRM;
679: l_location := l_module_name||'select_fv_sf224_map';
680: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
681: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
682: END;
683: END IF;
684:
685: IF ((p_business_activity <> 'ALL') AND (l_business_activity_code <> p_business_activity)) THEN
728: WHEN OTHERS THEN
729: p_error_code := g_FAILURE;
730: p_error_desc := SQLERRM;
731: l_location := l_module_name||'update_fv_sf224_temp';
732: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
733: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
734: END;
735: END IF;
736: END LOOP;
729: p_error_code := g_FAILURE;
730: p_error_desc := SQLERRM;
731: l_location := l_module_name||'update_fv_sf224_temp';
732: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
733: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
734: END;
735: END IF;
736: END LOOP;
737:
743: WHEN OTHERS THEN
744: p_error_code := g_FAILURE;
745: p_error_desc := SQLERRM;
746: l_location := l_module_name||'.final_exception';
747: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
748: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
749: IF (current_224_cur%ISOPEN) THEN
750: CLOSE current_224_cur;
751: END IF;
744: p_error_code := g_FAILURE;
745: p_error_desc := SQLERRM;
746: l_location := l_module_name||'.final_exception';
747: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
748: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
749: IF (current_224_cur%ISOPEN) THEN
750: CLOSE current_224_cur;
751: END IF;
752: END post_process_for_main;
770: l_module_name := g_module_name || 'Update_audit_info';
771: p_error_code := g_SUCCESS;
772: -- Updating Audit tables
773:
774: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
775: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'INSERTING INTO THE AUDITS TABLE.');
776: END IF;
777:
778: BEGIN
771: p_error_code := g_SUCCESS;
772: -- Updating Audit tables
773:
774: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
775: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'INSERTING INTO THE AUDITS TABLE.');
776: END IF;
777:
778: BEGIN
779: INSERT INTO fv_sf224_audits
839: WHEN OTHERS THEN
840: p_error_code := g_FAILURE;
841: p_error_desc := SQLERRM;
842: l_location := l_module_name||'insert_fv_sf224_temp';
843: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
844: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
845: END;
846:
847: IF (p_error_code = g_SUCCESS) THEN
840: p_error_code := g_FAILURE;
841: p_error_desc := SQLERRM;
842: l_location := l_module_name||'insert_fv_sf224_temp';
843: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
844: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
845: END;
846:
847: IF (p_error_code = g_SUCCESS) THEN
848: BEGIN
857: WHEN OTHERS THEN
858: p_error_code := g_FAILURE;
859: p_error_desc := SQLERRM;
860: l_location := l_module_name||'delete_fv_sf224_temp';
861: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
862: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
863: END;
864: END IF;
865:
858: p_error_code := g_FAILURE;
859: p_error_desc := SQLERRM;
860: l_location := l_module_name||'delete_fv_sf224_temp';
861: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
862: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
863: END;
864: END IF;
865:
866: EXCEPTION
867: WHEN OTHERS THEN
868: p_error_code := g_FAILURE;
869: p_error_desc := SQLERRM;
870: l_location := l_module_name||'.final_exception';
871: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
872: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
873:
874: END update_audit_info;
875:
868: p_error_code := g_FAILURE;
869: p_error_desc := SQLERRM;
870: l_location := l_module_name||'.final_exception';
871: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
872: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
873:
874: END update_audit_info;
875:
876: --------------------------------------------------------------------
917:
918: BEGIN
919: l_module_name := g_module_name || 'submit_224_report';
920: p_error_code := g_SUCCESS;
921: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
922: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'submitting 224 reports .....') ;
923: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
924: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gl_period='||p_gl_period) ;
925: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_alc='||p_alc) ;
918: BEGIN
919: l_module_name := g_module_name || 'submit_224_report';
920: p_error_code := g_SUCCESS;
921: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
922: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'submitting 224 reports .....') ;
923: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
924: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gl_period='||p_gl_period) ;
925: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_alc='||p_alc) ;
926: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_partial_or_full='||p_partial_or_full) ;
919: l_module_name := g_module_name || 'submit_224_report';
920: p_error_code := g_SUCCESS;
921: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
922: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'submitting 224 reports .....') ;
923: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
924: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gl_period='||p_gl_period) ;
925: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_alc='||p_alc) ;
926: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_partial_or_full='||p_partial_or_full) ;
927: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_business_activity='||p_business_activity) ;
920: p_error_code := g_SUCCESS;
921: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
922: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'submitting 224 reports .....') ;
923: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
924: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gl_period='||p_gl_period) ;
925: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_alc='||p_alc) ;
926: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_partial_or_full='||p_partial_or_full) ;
927: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_business_activity='||p_business_activity) ;
928: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gwa_reporter_category='||p_gwa_reporter_category) ;
921: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
922: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'submitting 224 reports .....') ;
923: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
924: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gl_period='||p_gl_period) ;
925: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_alc='||p_alc) ;
926: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_partial_or_full='||p_partial_or_full) ;
927: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_business_activity='||p_business_activity) ;
928: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gwa_reporter_category='||p_gwa_reporter_category) ;
929: END IF;
922: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'submitting 224 reports .....') ;
923: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
924: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gl_period='||p_gl_period) ;
925: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_alc='||p_alc) ;
926: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_partial_or_full='||p_partial_or_full) ;
927: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_business_activity='||p_business_activity) ;
928: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gwa_reporter_category='||p_gwa_reporter_category) ;
929: END IF;
930:
923: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
924: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gl_period='||p_gl_period) ;
925: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_alc='||p_alc) ;
926: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_partial_or_full='||p_partial_or_full) ;
927: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_business_activity='||p_business_activity) ;
928: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gwa_reporter_category='||p_gwa_reporter_category) ;
929: END IF;
930:
931: l_request_id := fnd_global.conc_request_id;
924: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gl_period='||p_gl_period) ;
925: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_alc='||p_alc) ;
926: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_partial_or_full='||p_partial_or_full) ;
927: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_business_activity='||p_business_activity) ;
928: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_gwa_reporter_category='||p_gwa_reporter_category) ;
929: END IF;
930:
931: l_request_id := fnd_global.conc_request_id;
932:
940: WHEN OTHERS THEN
941: p_error_code := g_FAILURE;
942: p_error_desc := SQLERRM;
943: l_location := l_module_name||'cursor_get_print_options_c';
944: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
945: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
946: END;
947:
948: IF NVL(fnd_profile.value('FV_GEN_224_REPORTS'), 'Y') = 'Y' THEN
941: p_error_code := g_FAILURE;
942: p_error_desc := SQLERRM;
943: l_location := l_module_name||'cursor_get_print_options_c';
944: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
945: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
946: END;
947:
948: IF NVL(fnd_profile.value('FV_GEN_224_REPORTS'), 'Y') = 'Y' THEN
949: IF (p_error_code = g_SUCCESS) THEN
971: IF (l_req_id1 = 0) THEN
972: p_error_code := g_FAILURE;
973: p_error_desc := 'ERROR SUBMITTING 224 REPORT';
974: l_location := 'submit_FVSF224R';
975: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
976: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
977: ELSE
978: COMMIT;
979: END IF;
972: p_error_code := g_FAILURE;
973: p_error_desc := 'ERROR SUBMITTING 224 REPORT';
974: l_location := 'submit_FVSF224R';
975: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
976: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
977: ELSE
978: COMMIT;
979: END IF;
980: END IF;
1003: IF (l_req_id2 = 0) THEN
1004: p_error_code := g_FAILURE;
1005: p_error_desc := 'ERROR SUBMITTING 224 LISTING';
1006: l_location := 'submit_FVSF224L';
1007: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1008: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1009: ELSE
1010: COMMIT;
1011: END IF;
1004: p_error_code := g_FAILURE;
1005: p_error_desc := 'ERROR SUBMITTING 224 LISTING';
1006: l_location := 'submit_FVSF224L';
1007: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1008: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1009: ELSE
1010: COMMIT;
1011: END IF;
1012: END IF;
1033: IF (l_req_id3 = 0) THEN
1034: p_error_code := g_FAILURE;
1035: p_error_desc := 'ERROR SUBMITTING 224 EXCEPTION REPORT';
1036: l_location := 'submit_FV224EXR';
1037: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1038: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1039: ELSE
1040: COMMIT;
1041: END IF;
1034: p_error_code := g_FAILURE;
1035: p_error_desc := 'ERROR SUBMITTING 224 EXCEPTION REPORT';
1036: l_location := 'submit_FV224EXR';
1037: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1038: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1039: ELSE
1040: COMMIT;
1041: END IF;
1042: END IF;
1057: IF (l_call_status = FALSE) THEN
1058: p_error_code := g_FAILURE;
1059: p_error_desc := 'Cannot wait for the status of 224 Report';
1060: l_location := 'wait_req1';
1061: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1062: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1063: END IF;
1064: END IF;
1065:
1058: p_error_code := g_FAILURE;
1059: p_error_desc := 'Cannot wait for the status of 224 Report';
1060: l_location := 'wait_req1';
1061: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1062: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1063: END IF;
1064: END IF;
1065:
1066: IF (p_error_code = g_SUCCESS) THEN
1078: IF (l_call_status = FALSE) THEN
1079: p_error_code := g_FAILURE;
1080: p_error_desc := 'Cannot wait for the status of 224 Detail Report';
1081: l_location := 'wait_req2';
1082: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1083: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1084: END IF;
1085: END IF;
1086:
1079: p_error_code := g_FAILURE;
1080: p_error_desc := 'Cannot wait for the status of 224 Detail Report';
1081: l_location := 'wait_req2';
1082: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1083: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1084: END IF;
1085: END IF;
1086:
1087: IF (p_error_code = g_SUCCESS) THEN
1099: IF (l_call_status = FALSE) THEN
1100: p_error_code := g_FAILURE;
1101: p_error_desc := 'Cannot wait for the status of 224 Exception Report';
1102: l_location := 'wait_req3';
1103: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1104: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1105: END IF;
1106: END IF;
1107:
1100: p_error_code := g_FAILURE;
1101: p_error_desc := 'Cannot wait for the status of 224 Exception Report';
1102: l_location := 'wait_req3';
1103: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1104: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1105: END IF;
1106: END IF;
1107:
1108: END IF; --profile
1131: IF (l_req_id4 = 0) THEN
1132: p_error_code := g_FAILURE;
1133: p_error_desc := 'ERROR SUBMITTING GOALS 224 PROCESS';
1134: l_location := 'submit_FVTI224P';
1135: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1136: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1137: ELSE
1138: COMMIT;
1139: END IF;
1132: p_error_code := g_FAILURE;
1133: p_error_desc := 'ERROR SUBMITTING GOALS 224 PROCESS';
1134: l_location := 'submit_FVTI224P';
1135: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1136: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1137: ELSE
1138: COMMIT;
1139: END IF;
1140: END IF;
1154: IF (l_call_status = FALSE) THEN
1155: p_error_code := g_FAILURE;
1156: p_error_desc := 'Cannot wait for the status of GOALS 224 process';
1157: l_location := 'wait_req4';
1158: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1159: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1160: END IF;
1161: END IF;
1162:
1155: p_error_code := g_FAILURE;
1156: p_error_desc := 'Cannot wait for the status of GOALS 224 process';
1157: l_location := 'wait_req4';
1158: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1159: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1160: END IF;
1161: END IF;
1162:
1163: IF (p_error_code = g_SUCCESS) THEN
1179: IF (l_req_id5 = 0) THEN
1180: p_error_code := g_FAILURE;
1181: p_error_desc := 'ERROR SUBMITTING GOALS 224 REPORT';
1182: l_location := 'submit_FVTI224R';
1183: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1184: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1185: ELSE
1186: COMMIT;
1187: END IF;
1180: p_error_code := g_FAILURE;
1181: p_error_desc := 'ERROR SUBMITTING GOALS 224 REPORT';
1182: l_location := 'submit_FVTI224R';
1183: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1184: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1185: ELSE
1186: COMMIT;
1187: END IF;
1188: END IF;
1202: IF (l_call_status = FALSE) THEN
1203: p_error_code := g_FAILURE;
1204: p_error_desc := 'Cannot wait for the status of GOALS 224 report';
1205: l_location := 'wait_req5';
1206: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1207: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1208: END IF;
1209: END IF;
1210: EXCEPTION
1203: p_error_code := g_FAILURE;
1204: p_error_desc := 'Cannot wait for the status of GOALS 224 report';
1205: l_location := 'wait_req5';
1206: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1207: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1208: END IF;
1209: END IF;
1210: EXCEPTION
1211: WHEN OTHERS THEN
1211: WHEN OTHERS THEN
1212: p_error_code := g_FAILURE;
1213: p_error_desc := SQLERRM;
1214: l_location := l_module_name||'.final_exception';
1215: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1216: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1217:
1218: END submit_224_report;
1219:
1212: p_error_code := g_FAILURE;
1213: p_error_desc := SQLERRM;
1214: l_location := l_module_name||'.final_exception';
1215: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1216: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1217:
1218: END submit_224_report;
1219:
1220: --------------------------------------------------------------------
1242: BEGIN
1243: l_module_name := g_module_name || 'call_extract_process';
1244: p_error_code := g_SUCCESS;
1245: p_def_org_id := mo_global.get_current_org_id;
1246: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1247: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'In call_extact process');
1248: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
1249: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'org_id='||p_def_org_id) ;
1250: END IF;
1243: l_module_name := g_module_name || 'call_extract_process';
1244: p_error_code := g_SUCCESS;
1245: p_def_org_id := mo_global.get_current_org_id;
1246: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1247: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'In call_extact process');
1248: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
1249: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'org_id='||p_def_org_id) ;
1250: END IF;
1251:
1244: p_error_code := g_SUCCESS;
1245: p_def_org_id := mo_global.get_current_org_id;
1246: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1247: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'In call_extact process');
1248: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
1249: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'org_id='||p_def_org_id) ;
1250: END IF;
1251:
1252: l_req_id := fnd_request.submit_request
1245: p_def_org_id := mo_global.get_current_org_id;
1246: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1247: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'In call_extact process');
1248: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'p_set_of_books_id='||p_set_of_books_id) ;
1249: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'org_id='||p_def_org_id) ;
1250: END IF;
1251:
1252: l_req_id := fnd_request.submit_request
1253: (
1261: IF (l_req_id = 0) THEN
1262: p_error_code := g_FAILURE;
1263: p_error_desc := 'ERROR SUBMITTING 224 EXTRACT PROCESS';
1264: l_location := 'submit_FVSF224E';
1265: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1266: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1267: ELSE
1268: COMMIT;
1269: END IF;
1262: p_error_code := g_FAILURE;
1263: p_error_desc := 'ERROR SUBMITTING 224 EXTRACT PROCESS';
1264: l_location := 'submit_FVSF224E';
1265: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1266: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1267: ELSE
1268: COMMIT;
1269: END IF;
1270:
1283: IF (l_call_status = FALSE) THEN
1284: p_error_code := g_FAILURE;
1285: p_error_desc := 'Cannot wait for the status of 224 Extract Process';
1286: l_location := 'wait_req1';
1287: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1288: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1289: END IF;
1290: END IF;
1291:
1284: p_error_code := g_FAILURE;
1285: p_error_desc := 'Cannot wait for the status of 224 Extract Process';
1286: l_location := 'wait_req1';
1287: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1288: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1289: END IF;
1290: END IF;
1291:
1292: EXCEPTION
1293: WHEN OTHERS THEN
1294: p_error_code := g_FAILURE;
1295: p_error_desc := SQLERRM;
1296: l_location := l_module_name||'.final_exception';
1297: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1298: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1299:
1300: END call_extract_process;
1301:
1294: p_error_code := g_FAILURE;
1295: p_error_desc := SQLERRM;
1296: l_location := l_module_name||'.final_exception';
1297: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1298: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1299:
1300: END call_extract_process;
1301:
1302:
1321: l_end_period_date DATE;
1322: BEGIN
1323:
1324: l_module_name := g_module_name || 'main';
1325: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1326: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS.....');
1327: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS 121.12...');
1328: END IF;
1329:
1322: BEGIN
1323:
1324: l_module_name := g_module_name || 'main';
1325: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1326: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS.....');
1327: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS 121.12...');
1328: END IF;
1329:
1330: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1323:
1324: l_module_name := g_module_name || 'main';
1325: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1326: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS.....');
1327: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS 121.12...');
1328: END IF;
1329:
1330: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INITIALIZING...');
1326: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS.....');
1327: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS 121.12...');
1328: END IF;
1329:
1330: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INITIALIZING...');
1332: END IF;
1333: p_retcode := g_SUCCESS;
1334:
1327: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START OF THE 224 MAIN PROCESS 121.12...');
1328: END IF;
1329:
1330: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1331: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INITIALIZING...');
1332: END IF;
1333: p_retcode := g_SUCCESS;
1334:
1335: update_flag := p_run_mode;
1339: g_business_activity := p_business_activity;
1340: g_gwa_reporter_category := p_gwa_reporter_category;
1341:
1342: IF (p_retcode = g_SUCCESS) THEN
1343: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1344: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling call_extract_process procedure and run with org_id .' || p_def_org_id);
1345: END IF;
1346:
1347: call_extract_process
1340: g_gwa_reporter_category := p_gwa_reporter_category;
1341:
1342: IF (p_retcode = g_SUCCESS) THEN
1343: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1344: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling call_extract_process procedure and run with org_id .' || p_def_org_id);
1345: END IF;
1346:
1347: call_extract_process
1348: (
1352: );
1353: END IF;
1354:
1355: IF (p_retcode = g_SUCCESS) THEN
1356: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1357: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling post_process_for_main procedure.....');
1358: END IF;
1359: post_process_for_main
1360: (
1353: END IF;
1354:
1355: IF (p_retcode = g_SUCCESS) THEN
1356: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1357: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling post_process_for_main procedure.....');
1358: END IF;
1359: post_process_for_main
1360: (
1361: p_set_of_books_id => p_set_of_books_id,
1371: );
1372: END IF;
1373:
1374: IF (p_retcode = g_SUCCESS) THEN
1375: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1376: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling submit_224_report procedure.....');
1377: END IF;
1378: submit_224_report
1379: (
1372: END IF;
1373:
1374: IF (p_retcode = g_SUCCESS) THEN
1375: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1376: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling submit_224_report procedure.....');
1377: END IF;
1378: submit_224_report
1379: (
1380: p_set_of_books_id => p_set_of_books_id,
1390: END IF;
1391:
1392: IF (p_retcode = g_SUCCESS) THEN
1393: IF (update_flag = 'F') THEN
1394: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1395: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling update_audit_info procedure.....');
1396: END IF;
1397: update_audit_info
1398: (
1391:
1392: IF (p_retcode = g_SUCCESS) THEN
1393: IF (update_flag = 'F') THEN
1394: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1395: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'calling update_audit_info procedure.....');
1396: END IF;
1397: update_audit_info
1398: (
1399: p_set_of_books_id => p_set_of_books_id,
1406: END IF;
1407:
1408: IF (p_retcode = g_SUCCESS) THEN
1409: IF (p_partial_or_full='Full') THEN
1410: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1411: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CALLING CHENCK ALC ADDRESS ROCEDURE.....');
1412: END IF;
1413: check_alc_address (p_alc) ;
1414: END IF;
1407:
1408: IF (p_retcode = g_SUCCESS) THEN
1409: IF (p_partial_or_full='Full') THEN
1410: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1411: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CALLING CHENCK ALC ADDRESS ROCEDURE.....');
1412: END IF;
1413: check_alc_address (p_alc) ;
1414: END IF;
1415: END IF;
1417: WHEN OTHERS THEN
1418: p_retcode := g_FAILURE;
1419: p_errbuf := SQLERRM;
1420: l_location := l_module_name||'.final_exception';
1421: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1422: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
1423:
1424: END main;
1425:
1418: p_retcode := g_FAILURE;
1419: p_errbuf := SQLERRM;
1420: l_location := l_module_name||'.final_exception';
1421: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1422: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
1423:
1424: END main;
1425:
1426: ----------------------------------------------------------------------
1437: l_cnt NUMBER;
1438: BEGIN
1439: l_module_name := g_module_name || 'check_report_definitions';
1440: p_error_code := g_SUCCESS;
1441: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1442: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'In ' || l_module_name);
1443: End if;
1444:
1445: SELECT COUNT(*)
1438: BEGIN
1439: l_module_name := g_module_name || 'check_report_definitions';
1440: p_error_code := g_SUCCESS;
1441: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1442: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'In ' || l_module_name);
1443: End if;
1444:
1445: SELECT COUNT(*)
1446: INTO l_cnt
1454: p_error_desc := 'The agency location code needs to be updated '||
1455: 'for the Disbursement and Receipt records '||
1456: 'in the 224 and Fund Balance with Treasury Form, '||
1457: 'before running the 224 Process';
1458: fv_utility.log_mesg(fnd_log.level_error, l_module_name||'.error1', p_error_desc) ;
1459: RETURN;
1460: END IF;
1461: EXCEPTION
1462: WHEN OTHERS THEN
1461: EXCEPTION
1462: WHEN OTHERS THEN
1463: p_error_code := g_FAILURE;
1464: p_error_desc := SQLERRM || ' -- Error in Check_Report_Defintions procedure.';
1465: fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception', p_error_desc) ;
1466: RETURN;
1467: END Check_Report_Definitions;
1468:
1469: ----------------------------------------------------------------------
1482: BEGIN
1483: l_module_name := g_module_name || 'get_previous_run_date';
1484: p_error_code := g_SUCCESS;
1485:
1486: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1487: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
1488: END IF;
1489:
1490: BEGIN
1483: l_module_name := g_module_name || 'get_previous_run_date';
1484: p_error_code := g_SUCCESS;
1485:
1486: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1487: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
1488: END IF;
1489:
1490: BEGIN
1491: SELECT last_run_date
1508: WHEN OTHERS THEN
1509: p_error_code := g_FAILURE;
1510: p_error_desc := SQLERRM;
1511: l_location := l_module_name||'select_fv_sf224_run';
1512: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1513: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1514: END;
1515:
1516: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1509: p_error_code := g_FAILURE;
1510: p_error_desc := SQLERRM;
1511: l_location := l_module_name||'select_fv_sf224_run';
1512: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1513: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1514: END;
1515:
1516: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1517: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'Last 224 run_date ='||p_previous_run_date);
1512: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1513: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1514: END;
1515:
1516: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1517: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'Last 224 run_date ='||p_previous_run_date);
1518: END IF;
1519:
1520: EXCEPTION
1513: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1514: END;
1515:
1516: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1517: fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'Last 224 run_date ='||p_previous_run_date);
1518: END IF;
1519:
1520: EXCEPTION
1521: WHEN OTHERS THEN
1521: WHEN OTHERS THEN
1522: p_error_code := g_FAILURE;
1523: p_error_desc := SQLERRM;
1524: l_location := l_module_name||'.final_exception';
1525: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1526: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1527: END get_run_date;
1528:
1529: ----------------------------------------------------------------------
1522: p_error_code := g_FAILURE;
1523: p_error_desc := SQLERRM;
1524: l_location := l_module_name||'.final_exception';
1525: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1526: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1527: END get_run_date;
1528:
1529: ----------------------------------------------------------------------
1530: -- SET_RUN_DATE
1542: BEGIN
1543: l_module_name := g_module_name || 'set_run_date';
1544: p_error_code := g_SUCCESS;
1545:
1546: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1547: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
1548: END IF;
1549:
1550: BEGIN
1543: l_module_name := g_module_name || 'set_run_date';
1544: p_error_code := g_SUCCESS;
1545:
1546: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1547: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
1548: END IF;
1549:
1550: BEGIN
1551: UPDATE fv_sf224_run
1560: WHEN OTHERS THEN
1561: p_error_code := g_FAILURE;
1562: p_error_desc := SQLERRM;
1563: l_location := l_module_name||'update_fv_sf224_run';
1564: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1565: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1566: END;
1567: IF (p_error_code = g_SUCCESS AND l_insert_required) THEN
1568: BEGIN
1561: p_error_code := g_FAILURE;
1562: p_error_desc := SQLERRM;
1563: l_location := l_module_name||'update_fv_sf224_run';
1564: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1565: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1566: END;
1567: IF (p_error_code = g_SUCCESS AND l_insert_required) THEN
1568: BEGIN
1569: INSERT INTO fv_sf224_run
1580: WHEN OTHERS THEN
1581: p_error_code := g_FAILURE;
1582: p_error_desc := SQLERRM;
1583: l_location := l_module_name||'insert_fv_sf224_run';
1584: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1585: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1586: END;
1587: END IF;
1588:
1581: p_error_code := g_FAILURE;
1582: p_error_desc := SQLERRM;
1583: l_location := l_module_name||'insert_fv_sf224_run';
1584: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1585: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1586: END;
1587: END IF;
1588:
1589: EXCEPTION
1590: WHEN OTHERS THEN
1591: p_error_code := g_FAILURE;
1592: p_error_desc := SQLERRM;
1593: l_location := l_module_name||'.final_exception';
1594: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1595: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1596: END set_run_date;
1597:
1598:
1591: p_error_code := g_FAILURE;
1592: p_error_desc := SQLERRM;
1593: l_location := l_module_name||'.final_exception';
1594: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1595: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1596: END set_run_date;
1597:
1598:
1599: ----------------------------------------------------------------------
1628: p_chart_of_accounts_id := NULL;
1629: p_acct_segment := NULL;
1630: p_acct_value_set_id := NULL;
1631:
1632: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1633: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
1634: END IF;
1635:
1636: IF (p_error_code = g_SUCCESS) THEN
1629: p_acct_segment := NULL;
1630: p_acct_value_set_id := NULL;
1631:
1632: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1633: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_set_of_books_id = '||p_set_of_books_id);
1634: END IF;
1635:
1636: IF (p_error_code = g_SUCCESS) THEN
1637: BEGIN
1634: END IF;
1635:
1636: IF (p_error_code = g_SUCCESS) THEN
1637: BEGIN
1638: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1639: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from gl_sets_of_books');
1640: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_set_of_books_id='||p_set_of_books_id);
1641: END IF;
1642: SELECT gsob.chart_of_accounts_id,
1635:
1636: IF (p_error_code = g_SUCCESS) THEN
1637: BEGIN
1638: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1639: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from gl_sets_of_books');
1640: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_set_of_books_id='||p_set_of_books_id);
1641: END IF;
1642: SELECT gsob.chart_of_accounts_id,
1643: gsob.currency_code,
1636: IF (p_error_code = g_SUCCESS) THEN
1637: BEGIN
1638: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1639: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Selecting from gl_sets_of_books');
1640: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'g_set_of_books_id='||p_set_of_books_id);
1641: END IF;
1642: SELECT gsob.chart_of_accounts_id,
1643: gsob.currency_code,
1644: gsob.period_set_name
1646: p_currency_code,
1647: l_period_set_name
1648: FROM gl_sets_of_books gsob
1649: WHERE set_of_books_id = p_set_of_books_id;
1650: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1651: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id='||p_chart_of_accounts_id);
1652: END IF;
1653: EXCEPTION
1654: WHEN OTHERS THEN
1647: l_period_set_name
1648: FROM gl_sets_of_books gsob
1649: WHERE set_of_books_id = p_set_of_books_id;
1650: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1651: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id='||p_chart_of_accounts_id);
1652: END IF;
1653: EXCEPTION
1654: WHEN OTHERS THEN
1655: p_error_code := g_FAILURE;
1654: WHEN OTHERS THEN
1655: p_error_code := g_FAILURE;
1656: p_error_desc := SQLERRM;
1657: l_location := l_module_name||'select_gl_sets_of_books';
1658: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1659: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1660: END;
1661: END IF;
1662:
1655: p_error_code := g_FAILURE;
1656: p_error_desc := SQLERRM;
1657: l_location := l_module_name||'select_gl_sets_of_books';
1658: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1659: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1660: END;
1661: END IF;
1662:
1663: IF (p_error_code = g_SUCCESS) THEN
1660: END;
1661: END IF;
1662:
1663: IF (p_error_code = g_SUCCESS) THEN
1664: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1665: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1666: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1667: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1668: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1661: END IF;
1662:
1663: IF (p_error_code = g_SUCCESS) THEN
1664: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1665: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1666: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1667: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1668: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1669: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_acct_seg_attr_type);
1662:
1663: IF (p_error_code = g_SUCCESS) THEN
1664: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1665: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1666: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1667: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1668: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1669: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_acct_seg_attr_type);
1670: END IF;
1663: IF (p_error_code = g_SUCCESS) THEN
1664: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1665: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1666: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1667: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1668: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1669: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_acct_seg_attr_type);
1670: END IF;
1671: l_retval := fnd_flex_apis.get_segment_column
1664: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1665: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1666: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1667: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1668: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1669: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_acct_seg_attr_type);
1670: END IF;
1671: l_retval := fnd_flex_apis.get_segment_column
1672: (
1665: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1666: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1667: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1668: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1669: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_acct_seg_attr_type);
1670: END IF;
1671: l_retval := fnd_flex_apis.get_segment_column
1672: (
1673: x_application_id => l_application_id,
1675: x_id_flex_num => p_chart_of_accounts_id,
1676: x_seg_attr_type => l_acct_seg_attr_type,
1677: x_app_column_name => p_acct_segment
1678: );
1679: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1680: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment ='||p_acct_segment);
1681: END IF;
1682: IF (NOT l_retval) THEN
1683: p_error_code := g_FAILURE;
1676: x_seg_attr_type => l_acct_seg_attr_type,
1677: x_app_column_name => p_acct_segment
1678: );
1679: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1680: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment ='||p_acct_segment);
1681: END IF;
1682: IF (NOT l_retval) THEN
1683: p_error_code := g_FAILURE;
1684: p_error_desc := fnd_message.get;
1682: IF (NOT l_retval) THEN
1683: p_error_code := g_FAILURE;
1684: p_error_desc := fnd_message.get;
1685: l_location := l_module_name||'call_fnd_flex_apis.get_segment_column';
1686: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1687: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1688: END IF;
1689: END IF;
1690:
1683: p_error_code := g_FAILURE;
1684: p_error_desc := fnd_message.get;
1685: l_location := l_module_name||'call_fnd_flex_apis.get_segment_column';
1686: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1687: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1688: END IF;
1689: END IF;
1690:
1691: IF (p_error_code = g_SUCCESS) THEN
1688: END IF;
1689: END IF;
1690:
1691: IF (p_error_code = g_SUCCESS) THEN
1692: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1693: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1694: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1695: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1696: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1689: END IF;
1690:
1691: IF (p_error_code = g_SUCCESS) THEN
1692: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1693: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1694: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1695: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1696: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1697: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_bal_seg_attr_type);
1690:
1691: IF (p_error_code = g_SUCCESS) THEN
1692: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1693: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1694: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1695: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1696: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1697: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_bal_seg_attr_type);
1698: END IF;
1691: IF (p_error_code = g_SUCCESS) THEN
1692: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1693: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1694: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1695: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1696: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1697: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_bal_seg_attr_type);
1698: END IF;
1699: l_retval := fnd_flex_apis.get_segment_column
1692: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1693: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1694: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1695: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1696: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1697: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_bal_seg_attr_type);
1698: END IF;
1699: l_retval := fnd_flex_apis.get_segment_column
1700: (
1693: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling fnd_flex_apis.get_segment_column');
1694: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_application_id ='||l_application_id);
1695: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_id_flex_code ='||l_id_flex_code);
1696: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_chart_of_accounts_id ='||p_chart_of_accounts_id);
1697: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'l_acct_seg_attr_type ='||l_bal_seg_attr_type);
1698: END IF;
1699: l_retval := fnd_flex_apis.get_segment_column
1700: (
1701: x_application_id => l_application_id,
1703: x_id_flex_num => p_chart_of_accounts_id,
1704: x_seg_attr_type => l_bal_seg_attr_type,
1705: x_app_column_name => p_bal_segment
1706: );
1707: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1708: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment ='||p_acct_segment);
1709: END IF;
1710: IF (NOT l_retval) THEN
1711: p_error_code := g_FAILURE;
1704: x_seg_attr_type => l_bal_seg_attr_type,
1705: x_app_column_name => p_bal_segment
1706: );
1707: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1708: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'p_acct_segment ='||p_acct_segment);
1709: END IF;
1710: IF (NOT l_retval) THEN
1711: p_error_code := g_FAILURE;
1712: p_error_desc := fnd_message.get;
1710: IF (NOT l_retval) THEN
1711: p_error_code := g_FAILURE;
1712: p_error_desc := fnd_message.get;
1713: l_location := l_module_name||'call_fnd_flex_apis.get_segment_column';
1714: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1715: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1716: END IF;
1717: END IF;
1718:
1711: p_error_code := g_FAILURE;
1712: p_error_desc := fnd_message.get;
1713: l_location := l_module_name||'call_fnd_flex_apis.get_segment_column';
1714: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1715: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1716: END IF;
1717: END IF;
1718:
1719: IF (p_error_code = g_SUCCESS) THEN
1730: WHEN OTHERS THEN
1731: p_error_code := g_FAILURE;
1732: p_error_desc := SQLERRM;
1733: l_location := l_module_name||'select_fnd_id_flex_segments';
1734: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1735: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1736: END;
1737: END IF;
1738:
1731: p_error_code := g_FAILURE;
1732: p_error_desc := SQLERRM;
1733: l_location := l_module_name||'select_fnd_id_flex_segments';
1734: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1735: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1736: END;
1737: END IF;
1738:
1739: IF (p_error_code = g_SUCCESS) THEN
1745: WHEN OTHERS THEN
1746: p_error_code := g_FAILURE;
1747: p_error_desc := SQLERRM;
1748: l_location := l_module_name||'select_fv_system_parameters';
1749: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1750: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1751: END;
1752: END IF;
1753:
1746: p_error_code := g_FAILURE;
1747: p_error_desc := SQLERRM;
1748: l_location := l_module_name||'select_fv_system_parameters';
1749: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1750: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1751: END;
1752: END IF;
1753:
1754: EXCEPTION
1755: WHEN OTHERS THEN
1756: p_error_code := g_FAILURE;
1757: p_error_desc := SQLERRM;
1758: l_location := l_module_name||'.final_exception';
1759: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1760: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1761: END initialize_program_variables;
1762:
1763: ----------------------------------------------------------------------
1756: p_error_code := g_FAILURE;
1757: p_error_desc := SQLERRM;
1758: l_location := l_module_name||'.final_exception';
1759: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
1760: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
1761: END initialize_program_variables;
1762:
1763: ----------------------------------------------------------------------
1764: -- INSERT_SF224_BATCHES
1789: BEGIN
1790: p_error_code := g_SUCCESS;
1791: l_module_name := g_module_name || 'insert_sf224_batches';
1792:
1793: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1794: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'in insert_sf224_batches procedure, before the insert.');
1795: END IF;
1796: l_string1 := 'INSERT INTO FV_SF224_TEMP
1797: (
1790: p_error_code := g_SUCCESS;
1791: l_module_name := g_module_name || 'insert_sf224_batches';
1792:
1793: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1794: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'in insert_sf224_batches procedure, before the insert.');
1795: END IF;
1796: l_string1 := 'INSERT INTO FV_SF224_TEMP
1797: (
1798: je_batch_id,
1930: dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
1931: l_row := dbms_sql.EXECUTE(l_cur);
1932: dbms_sql.close_cursor(l_cur);
1933: /* END for non-sla, upgraded 11i data */
1934: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for upgraded 11i data ');
1935: /* Start for je_source is payables and je_category is non treasury */
1936:
1937: l_string2 := ' SELECT glb.je_batch_id,
1938: ffp.fund_value,
2250: dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2251: l_row := dbms_sql.EXECUTE(l_cur);
2252: dbms_sql.close_cursor(l_cur);
2253: /* END for je_source is payables and je_category is non treasury*/
2254: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is payables and je_category is non treasury');
2255: /* Start for je_source is payables and je_category is treasury confirmation */
2256:
2257: l_string2 := ' SELECT glb.je_batch_id,
2258: ffp.fund_value,
2361: dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2362: l_row := dbms_sql.EXECUTE(l_cur);
2363: dbms_sql.close_cursor(l_cur);
2364: /* END for je_source is payables and je_category is treasury confirmation */
2365: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is payables and je_category is treasury confirmation');
2366:
2367: /* Start for je_source is project and je_category is labour_cost */
2368:
2369: l_string2 := ' SELECT glb.je_batch_id,
2471: dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2472: l_row := dbms_sql.EXECUTE(l_cur);
2473: dbms_sql.close_cursor(l_cur);
2474: /* END for je_source is project and je_category is labour_cost */
2475: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is project and je_category is labour_cost');
2476: /* Start for je_source is Receivables, based on ar_cash_receipt_history_all */
2477:
2478: l_string2 := ' SELECT glb.je_batch_id,
2479: ffp.fund_value,
2584: dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2585: l_row := dbms_sql.EXECUTE(l_cur);
2586: dbms_sql.close_cursor(l_cur);
2587: /* END for je_source is Receivables, based on ar_cash_receipt_history_all*/
2588: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is Receivables, based on ar_cash_receipt_history_all');
2589: /* Start for je_source is Receivables , based on AR_RECEIVABLE_APPLICATIONS_ALL */
2590:
2591: l_string2 := ' SELECT glb.je_batch_id,
2592: ffp.fund_value,
2698: l_row := dbms_sql.EXECUTE(l_cur);
2699: dbms_sql.close_cursor(l_cur);
2700:
2701: /* END for je_source is Receivables , based on AR_RECEIVABLE_APPLICATIONS_ALL */
2702: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is Receivables , based on AR_RECEIVABLE_APPLICATIONS_ALL ');
2703: /* Start for for je_source is Receivables , based on AR_MISC_CASH_DISTRIBUTIONS_ALL */
2704:
2705: l_string2 := ' SELECT glb.je_batch_id,
2706: ffp.fund_value,
2811: dbms_sql.bind_variable(l_cur,':b_request_id',g_request_id);
2812: l_row := dbms_sql.EXECUTE(l_cur);
2813: dbms_sql.close_cursor(l_cur);
2814: /* END for je_source is Receivables , based on AR_MISC_CASH_DISTRIBUTIONS_ALL */
2815: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert for je_source is Receivables , based on AR_MISC_CASH_DISTRIBUTIONS_ALL ');
2816:
2817: EXCEPTION
2818: WHEN OTHERS THEN
2819: p_error_code := sqlcode;
2817: EXCEPTION
2818: WHEN OTHERS THEN
2819: p_error_code := sqlcode;
2820: p_error_desc := sqlerrm;
2821: fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception', p_error_desc) ;
2822: END insert_sf224_batches ;
2823:
2824:
2825: ----------------------------------------------------------------------
2980: RETURN;
2981: END IF;
2982:
2983: p_def_org_id := mo_global.get_current_org_id;
2984: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2985: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Deriving PAYGROUP for org id '|| p_def_org_id);
2986: END IF;
2987:
2988: -- Determine the default paygroup based on the org_id
2981: END IF;
2982:
2983: p_def_org_id := mo_global.get_current_org_id;
2984: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2985: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Deriving PAYGROUP for org id '|| p_def_org_id);
2986: END IF;
2987:
2988: -- Determine the default paygroup based on the org_id
2989: BEGIN
2992: FROM FV_Operating_units_all
2993: WHERE org_id = p_def_org_id;
2994: EXCEPTION
2995: WHEN No_Data_Found THEN
2996: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2997: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PAYABLES IA PAYGROUP NOT FOUND,BASED ON THE ORG_ID '
2998: ||TO_CHAR(p_def_org_id));
2999: END IF;
3000: NULL;
2993: WHERE org_id = p_def_org_id;
2994: EXCEPTION
2995: WHEN No_Data_Found THEN
2996: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2997: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PAYABLES IA PAYGROUP NOT FOUND,BASED ON THE ORG_ID '
2998: ||TO_CHAR(p_def_org_id));
2999: END IF;
3000: NULL;
3001: WHEN OTHERS THEN
3001: WHEN OTHERS THEN
3002: error_code := g_FAILURE;
3003: error_buf :=SQLERRM || '-- Error in Process_Sf224_Transactions procedure '||
3004: 'while determining the payables ia paygroup.';
3005: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2', error_buf) ;
3006: END;
3007:
3008: --Determine the attribute column used in AR Reclass Receipt DFF
3009: BEGIN
3012: FROM fv_system_parameters;
3013: EXCEPTION
3014: WHEN NO_DATA_FOUND THEN
3015: l_ar_reclass_attribute:=NULL;
3016: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3017: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Receivables Reclassification Field' ||
3018: ' has not set in Define Federal System Parameters Form');
3019: END IF;
3020: END;
3013: EXCEPTION
3014: WHEN NO_DATA_FOUND THEN
3015: l_ar_reclass_attribute:=NULL;
3016: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3017: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Receivables Reclassification Field' ||
3018: ' has not set in Define Federal System Parameters Form');
3019: END IF;
3020: END;
3021:
3063: IF (C1%NOTFOUND) THEN
3064: EXIT;
3065: END IF;
3066:
3067: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3068: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'---------------------------------');
3069: END IF;
3070: -- Determine the default alc
3071: BEGIN
3064: EXIT;
3065: END IF;
3066:
3067: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3068: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'---------------------------------');
3069: END IF;
3070: -- Determine the default alc
3071: BEGIN
3072: SELECT agency_location_code
3077: WHEN NO_DATA_FOUND THEN
3078: error_buf := 'The default agency_location_code could not be determined
3079: -- Error in Process_Sf224_Transactions procedure.';
3080: error_code := g_FAILURE;
3081: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error3', error_buf) ;
3082: RETURN;
3083: WHEN OTHERS THEN
3084: error_buf := SQLERRM||'-- Error in Process_Sf224_Transactions procedure.';
3085: error_code := g_FAILURE;
3082: RETURN;
3083: WHEN OTHERS THEN
3084: error_buf := SQLERRM||'-- Error in Process_Sf224_Transactions procedure.';
3085: error_code := g_FAILURE;
3086: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error4', error_buf) ;
3087: RETURN;
3088: END;
3089: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3090: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DEFAULT ALC IS '||P_DEF_ALC_CODE);
3085: error_code := g_FAILURE;
3086: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error4', error_buf) ;
3087: RETURN;
3088: END;
3089: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3090: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DEFAULT ALC IS '||P_DEF_ALC_CODE);
3091: END IF;
3092: --Fetch the end date for the period in which txn was entered
3093: BEGIN
3086: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error4', error_buf) ;
3087: RETURN;
3088: END;
3089: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3090: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DEFAULT ALC IS '||P_DEF_ALC_CODE);
3091: END IF;
3092: --Fetch the end date for the period in which txn was entered
3093: BEGIN
3094: SELECT start_date,
3101: AND glp.application_id = 101;
3102: EXCEPTION WHEN OTHERS THEN
3103: error_code := 2;
3104: error_buf := substr(sqlerrm,1,50) || ' while fetching txn end date';
3105: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error5', error_buf) ;
3106: END;
3107:
3108: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3109: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'END DATE OF THE TRANSACTION PERIOD '||
3104: error_buf := substr(sqlerrm,1,50) || ' while fetching txn end date';
3105: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error5', error_buf) ;
3106: END;
3107:
3108: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3109: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'END DATE OF THE TRANSACTION PERIOD '||
3110: to_char(l_txn_end_date, 'MM/DD/YYYY'));
3111: END IF;
3112:
3105: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error5', error_buf) ;
3106: END;
3107:
3108: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3109: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'END DATE OF THE TRANSACTION PERIOD '||
3110: to_char(l_txn_end_date, 'MM/DD/YYYY'));
3111: END IF;
3112:
3113:
3114: -----------------------------------------------------------------------
3115: -- Initializing variables
3116: -----------------------------------------------------------------------
3117:
3118: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3119: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING TXN WITH SOURCE ='||
3120: L_JE_SOURCE||',Category='||l_je_category||',Batch='||l_name||
3121: ',Header='||l_je_header_id||',Line='||l_je_line_num );
3122: END IF;
3115: -- Initializing variables
3116: -----------------------------------------------------------------------
3117:
3118: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3119: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING TXN WITH SOURCE ='||
3120: L_JE_SOURCE||',Category='||l_je_category||',Batch='||l_name||
3121: ',Header='||l_je_header_id||',Line='||l_je_line_num );
3122: END IF;
3123: l_processed_flag := 'N';
3148: IF (l_je_category = 'Treasury Confirmation' AND
3149: ((NVL(l_je_from_sla_flag,'N') IN ('N', 'U') AND upper(l_name) like '%TREASURY%') OR
3150: (NVL(l_je_from_sla_flag,'N')='Y' AND upper(l_name) like '%BUDGET EXECUTION%'))) then -- source1
3151: -- get the document number
3152: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3153: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_3='|| l_reference_3);
3154: END IF;
3155: BEGIN
3156: SELECT checkrun_name
3149: ((NVL(l_je_from_sla_flag,'N') IN ('N', 'U') AND upper(l_name) like '%TREASURY%') OR
3150: (NVL(l_je_from_sla_flag,'N')='Y' AND upper(l_name) like '%BUDGET EXECUTION%'))) then -- source1
3151: -- get the document number
3152: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3153: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_3='|| l_reference_3);
3154: END IF;
3155: BEGIN
3156: SELECT checkrun_name
3157: INTO l_document_number
3237: EXCEPTION
3238: WHEN others THEN
3239: error_buf :='PA Accomplish Date Error'||sqlerrm;
3240: error_code := g_FAILURE;
3241: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error7', error_buf) ;
3242: RETURN;
3243: END;
3244: END IF;
3245: ELSE --l_je_from_sla_flag
3289: EXCEPTION
3290: WHEN others THEN
3291: error_buf :='PA Accomplish Date Error'||sqlerrm;
3292: error_code := g_FAILURE;
3293: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error7', error_buf) ;
3294: RETURN;
3295: END;
3296: END IF;
3297: END IF;
3311: INTO l_document_number
3312: FROM gl_je_headers
3313: WHERE je_header_id = l_je_header_id;
3314: END;
3315: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3316: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Document_Num => ' || l_document_number);
3317: END IF;
3318: IF l_reference_2 IS NULL Then
3319: x_name := 'Blank Invoice Id' ;
3312: FROM gl_je_headers
3313: WHERE je_header_id = l_je_header_id;
3314: END;
3315: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3316: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Document_Num => ' || l_document_number);
3317: END IF;
3318: IF l_reference_2 IS NULL Then
3319: x_name := 'Blank Invoice Id' ;
3320: Set_Exception('R');
3375: ELSIF (l_je_source = 'Receivables') THEN -- source1
3376: vl_misc_cd_flag := 'N';
3377: IF (NVL(l_je_from_sla_flag,'N') IN ('N', 'U')) THEN
3378: IF (l_je_category = 'Misc Receipts') THEN -- rec category
3379: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3380: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING A MISC RECEIPT');
3381: END IF;
3382: l_cash_receipt_id := l_reference_2;
3383: l_cash_receipt_hist_id := l_reference_5;
3376: vl_misc_cd_flag := 'N';
3377: IF (NVL(l_je_from_sla_flag,'N') IN ('N', 'U')) THEN
3378: IF (l_je_category = 'Misc Receipts') THEN -- rec category
3379: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3380: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING A MISC RECEIPT');
3381: END IF;
3382: l_cash_receipt_id := l_reference_2;
3383: l_cash_receipt_hist_id := l_reference_5;
3384: ELSE -- rec category
3381: END IF;
3382: l_cash_receipt_id := l_reference_2;
3383: l_cash_receipt_hist_id := l_reference_5;
3384: ELSE -- rec category
3385: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3386: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING A TRADE RECEIPT OR OTHER');
3387: END IF;
3388: -- get the cash receipt id, and the cash receipt history id
3389: l_cash_receipt_id := SUBSTR(l_reference_2,0,INSTR(l_reference_2,'C')-1);
3382: l_cash_receipt_id := l_reference_2;
3383: l_cash_receipt_hist_id := l_reference_5;
3384: ELSE -- rec category
3385: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3386: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING A TRADE RECEIPT OR OTHER');
3387: END IF;
3388: -- get the cash receipt id, and the cash receipt history id
3389: l_cash_receipt_id := SUBSTR(l_reference_2,0,INSTR(l_reference_2,'C')-1);
3390: l_cash_receipt_hist_id := SUBSTR(l_reference_2,INSTR(l_reference_2,'C')+1,LENGTH(l_reference_2));
3389: l_cash_receipt_id := SUBSTR(l_reference_2,0,INSTR(l_reference_2,'C')-1);
3390: l_cash_receipt_hist_id := SUBSTR(l_reference_2,INSTR(l_reference_2,'C')+1,LENGTH(l_reference_2));
3391: END IF;
3392: ELSE --l_je_from_sla_flag is 'Y'
3393: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3394: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING RECEIPT WHEN SOURCE IS SLA');
3395: END IF;
3396: l_cash_receipt_id := l_reference_2;
3397: l_cash_receipt_hist_id := l_reference_5;
3390: l_cash_receipt_hist_id := SUBSTR(l_reference_2,INSTR(l_reference_2,'C')+1,LENGTH(l_reference_2));
3391: END IF;
3392: ELSE --l_je_from_sla_flag is 'Y'
3393: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3394: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING RECEIPT WHEN SOURCE IS SLA');
3395: END IF;
3396: l_cash_receipt_id := l_reference_2;
3397: l_cash_receipt_hist_id := l_reference_5;
3398: END IF;
3395: END IF;
3396: l_cash_receipt_id := l_reference_2;
3397: l_cash_receipt_hist_id := l_reference_5;
3398: END IF;
3399: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3400: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT ID = '
3401: ||TO_NUMBER(l_cash_receipt_id));
3402: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HISTORY ID = '
3403: ||TO_NUMBER(l_cash_receipt_hist_id));
3396: l_cash_receipt_id := l_reference_2;
3397: l_cash_receipt_hist_id := l_reference_5;
3398: END IF;
3399: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3400: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT ID = '
3401: ||TO_NUMBER(l_cash_receipt_id));
3402: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HISTORY ID = '
3403: ||TO_NUMBER(l_cash_receipt_hist_id));
3404: END IF;
3398: END IF;
3399: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3400: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT ID = '
3401: ||TO_NUMBER(l_cash_receipt_id));
3402: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HISTORY ID = '
3403: ||TO_NUMBER(l_cash_receipt_hist_id));
3404: END IF;
3405: IF ((l_cash_receipt_id IS NULL) OR (l_cash_receipt_hist_id IS NULL)) THEN
3406: x_name := 'Blank Cash Receipt Id ' ;
3410: SELECT 'Y'
3411: INTO l_exists
3412: FROM Ar_Cash_Receipts_All
3413: WHERE cash_receipt_id = to_number(l_cash_receipt_id);
3414: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3415: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT ID EXISTS');
3416: END IF;
3417: EXCEPTION
3418: WHEN no_data_found THEN
3411: INTO l_exists
3412: FROM Ar_Cash_Receipts_All
3413: WHERE cash_receipt_id = to_number(l_cash_receipt_id);
3414: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3415: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT ID EXISTS');
3416: END IF;
3417: EXCEPTION
3418: WHEN no_data_found THEN
3419: x_name := 'Invalid Cash Receipt Id - '||TO_NUMBER(l_cash_receipt_id) ;
3427: INTO l_exists
3428: FROM Ar_Cash_Receipt_History_All
3429: WHERE cash_receipt_history_id = to_number(l_cash_receipt_hist_id)
3430: AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3431: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3432: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HIST ID EXISTS IN '||
3433: 'Ar_Cash_Receipt_History_All.');
3434: END IF;
3435: EXCEPTION
3428: FROM Ar_Cash_Receipt_History_All
3429: WHERE cash_receipt_history_id = to_number(l_cash_receipt_hist_id)
3430: AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3431: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3432: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HIST ID EXISTS IN '||
3433: 'Ar_Cash_Receipt_History_All.');
3434: END IF;
3435: EXCEPTION
3436: WHEN no_data_found THEN
3434: END IF;
3435: EXCEPTION
3436: WHEN no_data_found THEN
3437: IF(l_je_category = 'Misc Receipts') THEN
3438: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3439: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOR MISC RECEIPT: '||
3440: 'Cash Receipt Hist Id does not '||
3441: 'exist in Ar_Cash_Receipt_History_All table.'||
3442: 'Checking in Ar_Misc_Cash_Distributions_All table.');
3435: EXCEPTION
3436: WHEN no_data_found THEN
3437: IF(l_je_category = 'Misc Receipts') THEN
3438: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3439: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOR MISC RECEIPT: '||
3440: 'Cash Receipt Hist Id does not '||
3441: 'exist in Ar_Cash_Receipt_History_All table.'||
3442: 'Checking in Ar_Misc_Cash_Distributions_All table.');
3443: END IF;
3442: 'Checking in Ar_Misc_Cash_Distributions_All table.');
3443: END IF;
3444: l_exists := 'M';
3445: ELSE
3446: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3447: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOR TRADE RECEIPT: '||
3448: 'Cash Receipt Hist Id does not '||
3449: 'exist in Ar_Cash_Receipt_History_All table.'||
3450: 'Checking in Ar_Receivable_Applications_All table.');
3443: END IF;
3444: l_exists := 'M';
3445: ELSE
3446: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3447: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOR TRADE RECEIPT: '||
3448: 'Cash Receipt Hist Id does not '||
3449: 'exist in Ar_Cash_Receipt_History_All table.'||
3450: 'Checking in Ar_Receivable_Applications_All table.');
3451: END IF;
3462: INTO l_temp_cr_hist_id
3463: FROM Ar_Receivable_Applications_All
3464: WHERE receivable_application_id = TO_NUMBER(l_cash_receipt_hist_id);
3465: l_cash_receipt_hist_id := l_temp_cr_hist_id;
3466: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3467: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HIST ID EXISTS IN '||
3468: 'Ar_Receivable_Applications_All table and is '||
3469: TO_NUMBER(l_cash_receipt_hist_id)||'.Checking in '||
3470: 'Ar_Cash_Receipt_History_All to see if it is a valid '||
3463: FROM Ar_Receivable_Applications_All
3464: WHERE receivable_application_id = TO_NUMBER(l_cash_receipt_hist_id);
3465: l_cash_receipt_hist_id := l_temp_cr_hist_id;
3466: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3467: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HIST ID EXISTS IN '||
3468: 'Ar_Receivable_Applications_All table and is '||
3469: TO_NUMBER(l_cash_receipt_hist_id)||'.Checking in '||
3470: 'Ar_Cash_Receipt_History_All to see if it is a valid '||
3471: 'cash receipt hist id.');
3475: INTO l_exists
3476: FROM Ar_Cash_Receipt_History_All
3477: WHERE cash_receipt_history_id = TO_NUMBER(l_cash_receipt_hist_id)
3478: AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3479: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3480: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HIST ID EXISTS IN '||
3481: 'the Ar_Cash_Receipt_History_All table.');
3482: END IF;
3483: EXCEPTION
3476: FROM Ar_Cash_Receipt_History_All
3477: WHERE cash_receipt_history_id = TO_NUMBER(l_cash_receipt_hist_id)
3478: AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3479: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3480: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASH RECEIPT HIST ID EXISTS IN '||
3481: 'the Ar_Cash_Receipt_History_All table.');
3482: END IF;
3483: EXCEPTION
3484: WHEN NO_DATA_FOUND THEN
3506: INTO l_exists
3507: FROM Ar_Misc_Cash_Distributions_All
3508: WHERE misc_cash_distribution_id = TO_NUMBER(l_cash_receipt_hist_id)
3509: AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3510: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3511: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'MISC CASH DISTRIBUTION ID EXISTS IN '||
3512: 'Ar_Misc_Cash_Distributions_All table and is '|| TO_NUMBER(l_cash_receipt_hist_id));
3513: END IF;
3514: vl_misc_cd_flag := 'Y';
3507: FROM Ar_Misc_Cash_Distributions_All
3508: WHERE misc_cash_distribution_id = TO_NUMBER(l_cash_receipt_hist_id)
3509: AND cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3510: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3511: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'MISC CASH DISTRIBUTION ID EXISTS IN '||
3512: 'Ar_Misc_Cash_Distributions_All table and is '|| TO_NUMBER(l_cash_receipt_hist_id));
3513: END IF;
3514: vl_misc_cd_flag := 'Y';
3515: EXCEPTION
3605: vg_bank_acct_id := NULL;
3606: IF (x_name <> 'MANUAL') THEN -- manual
3607: IF (l_je_source = 'Receivables' ) THEN -- source2
3608: l_exists := 'N';
3609: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3610: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3611: 'GETTING THE ACCOMPLISH DATE FOR RECEIPTS');
3612: END IF;
3613: -- Check IF the cash receipt is a reversal
3606: IF (x_name <> 'MANUAL') THEN -- manual
3607: IF (l_je_source = 'Receivables' ) THEN -- source2
3608: l_exists := 'N';
3609: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3610: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3611: 'GETTING THE ACCOMPLISH DATE FOR RECEIPTS');
3612: END IF;
3613: -- Check IF the cash receipt is a reversal
3614: IF ((l_je_category = 'Misc Receipts') AND (vl_misc_cd_flag = 'Y')) THEN -- rev check
3618: FROM Ar_Misc_Cash_Distributions_All
3619: WHERE misc_cash_distribution_id = l_cash_receipt_hist_id
3620: AND cash_receipt_id = l_cash_receipt_id
3621: AND created_from = 'ARP_REVERSE_RECEIPT.REVERSE';
3622: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3623: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3624: 'MISC CASH DIST ID HAS REVERSE VALUE IN '||'created from column....it is a reversal');
3625: END IF;
3626: EXCEPTION
3619: WHERE misc_cash_distribution_id = l_cash_receipt_hist_id
3620: AND cash_receipt_id = l_cash_receipt_id
3621: AND created_from = 'ARP_REVERSE_RECEIPT.REVERSE';
3622: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3623: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3624: 'MISC CASH DIST ID HAS REVERSE VALUE IN '||'created from column....it is a reversal');
3625: END IF;
3626: EXCEPTION
3627: WHEN NO_DATA_FOUND THEN
3629: WHEN OTHERS THEN
3630: error_buf := SQLERRM||'- Error while deriving the reversal status'
3631: ||' for the misc cash dist id '||l_cash_receipt_hist_id;
3632: error_code := 2;
3633: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error10', error_buf) ;
3634: RETURN;
3635: END;
3636: ELSE -- rev check
3637: BEGIN
3640: FROM Ar_Cash_Receipt_History_All
3641: WHERE cash_receipt_history_id = l_cash_receipt_hist_id
3642: AND cash_receipt_id = l_cash_receipt_id
3643: AND status='REVERSED';
3644: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3645: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3646: 'REVERSAL CASH RECEIPT HIST ID EXISTS..it is a reversal');
3647: END IF;
3648: EXCEPTION
3641: WHERE cash_receipt_history_id = l_cash_receipt_hist_id
3642: AND cash_receipt_id = l_cash_receipt_id
3643: AND status='REVERSED';
3644: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3645: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3646: 'REVERSAL CASH RECEIPT HIST ID EXISTS..it is a reversal');
3647: END IF;
3648: EXCEPTION
3649: WHEN NO_DATA_FOUND THEN
3651: WHEN OTHERS THEN
3652: error_buf := SQLERRM||'- Error while deriving the status'
3653: ||' for the cash receipt hist id '||l_cash_receipt_hist_id;
3654: error_code := 2;
3655: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error11', error_buf) ;
3656: RETURN;
3657: END;
3658: END IF; -- rev check
3659: BEGIN
3665: WHEN OTHERS THEN
3666: error_buf := SQLERRM|| '- Error while deriving the accomplish date'
3667: ||' for the cash receipt id '||l_cash_receipt_id;
3668: error_code := 1 ;
3669: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error12', error_buf) ;
3670: RETURN;
3671: END;
3672: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3673: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3668: error_code := 1 ;
3669: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error12', error_buf) ;
3670: RETURN;
3671: END;
3672: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3673: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3674: 'ACCOMPLISH DATE IS '||TO_CHAR(L_ACCOMPLISH_DATE, 'MM/DD/YYYY'));
3675: END IF;
3676: BEGIN
3669: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error12', error_buf) ;
3670: RETURN;
3671: END;
3672: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3673: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3674: 'ACCOMPLISH DATE IS '||TO_CHAR(L_ACCOMPLISH_DATE, 'MM/DD/YYYY'));
3675: END IF;
3676: BEGIN
3677: SELECT remit_bank_acct_use_id
3677: SELECT remit_bank_acct_use_id
3678: INTO vg_bank_acct_id
3679: FROM Ar_Cash_Receipts_All
3680: WHERE cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3681: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3682: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
3683: END IF;
3684: -- Get the agency location code
3685: Get_Alc
3678: INTO vg_bank_acct_id
3679: FROM Ar_Cash_Receipts_All
3680: WHERE cash_receipt_id = TO_NUMBER(l_cash_receipt_id);
3681: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3682: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
3683: END IF;
3684: -- Get the agency location code
3685: Get_Alc
3686: (
3691: );
3692: EXCEPTION
3693: WHEN NO_DATA_FOUND THEN
3694: -- Unable to find the remittance_bank_account_id
3695: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3696: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3697: 'UNABLE TO FIND THE BANK ACCT ID');
3698: END IF;
3699: Set_Exception('D');
3692: EXCEPTION
3693: WHEN NO_DATA_FOUND THEN
3694: -- Unable to find the remittance_bank_account_id
3695: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3696: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3697: 'UNABLE TO FIND THE BANK ACCT ID');
3698: END IF;
3699: Set_Exception('D');
3700: WHEN OTHERS THEN
3700: WHEN OTHERS THEN
3701: error_buf := SQLERRM||'- Error while deriving the agency_location_code'
3702: ||' for the cash receipt id '||l_cash_receipt_id;
3703: error_code := 2;
3704: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error13', error_buf) ;
3705: RETURN;
3706: END;
3707: --- Check for Interagency funds
3708: l_ia_flag := 'N';
3729: ' FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.cash_receipt_id = '
3730: || l_cash_receipt_id;
3731: l_reclass_receipt_number := NULL;
3732: EXECUTE IMMEDIATE sql_stmt1 INTO l_reclass_receipt_number ;
3733: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3734: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
3735: 'Reclass Receipt Number: '|| l_reclass_receipt_number );
3736: END IF;
3737: EXCEPTION
3730: || l_cash_receipt_id;
3731: l_reclass_receipt_number := NULL;
3732: EXECUTE IMMEDIATE sql_stmt1 INTO l_reclass_receipt_number ;
3733: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3734: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
3735: 'Reclass Receipt Number: '|| l_reclass_receipt_number );
3736: END IF;
3737: EXCEPTION
3738: WHEN NO_DATA_FOUND THEN
3772: INTO l_obligation_date,
3773: l_inv_amount ;
3774: EXIT WHEN C2%NOTFOUND;
3775: vl_refund_cnt := 0;
3776: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3777: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3778: 'BATCH_ID,HEADER_ID,FUND,REF2 ='||L_BATCH_ID||','||
3779: l_je_header_id||','||l_fund_code||','||l_reference_2);
3780: END IF;
3773: l_inv_amount ;
3774: EXIT WHEN C2%NOTFOUND;
3775: vl_refund_cnt := 0;
3776: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3777: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3778: 'BATCH_ID,HEADER_ID,FUND,REF2 ='||L_BATCH_ID||','||
3779: l_je_header_id||','||l_fund_code||','||l_reference_2);
3780: END IF;
3781: OPEN Get_Count_Csr
3788: l_je_from_sla_flag
3789: );
3790: FETCH Get_Count_Csr INTO vl_refund_cnt;
3791: CLOSE Get_Count_Csr;
3792: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3793: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3794: 'THE # OF RECORDS IN FV_SF224_TEMP '||VL_REFUND_CNT);
3795: END IF;
3796: IF (vl_refund_cnt = 0) THEN
3789: );
3790: FETCH Get_Count_Csr INTO vl_refund_cnt;
3791: CLOSE Get_Count_Csr;
3792: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3793: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3794: 'THE # OF RECORDS IN FV_SF224_TEMP '||VL_REFUND_CNT);
3795: END IF;
3796: IF (vl_refund_cnt = 0) THEN
3797: l_record_type := 'Receipt_refund';
3794: 'THE # OF RECORDS IN FV_SF224_TEMP '||VL_REFUND_CNT);
3795: END IF;
3796: IF (vl_refund_cnt = 0) THEN
3797: l_record_type := 'Receipt_refund';
3798: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3799: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3800: 'RECORD_TYPE AFTER REFUNDS CHECK IS '||L_RECORD_TYPE);
3801: END IF;
3802: l_type := 'AP_REFUND';
3795: END IF;
3796: IF (vl_refund_cnt = 0) THEN
3797: l_record_type := 'Receipt_refund';
3798: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3799: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3800: 'RECORD_TYPE AFTER REFUNDS CHECK IS '||L_RECORD_TYPE);
3801: END IF;
3802: l_type := 'AP_REFUND';
3803: l_update_type := 'RECEIPT';
3803: l_update_type := 'RECEIPT';
3804: l_processed_flag := 'Y';
3805: --l_record_type := 'Receipt_refund';
3806: x_name := 'Refunds_and_Voids';
3807: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3808: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3809: 'BEFORE SET_TRANSACTION_TYPE');
3810: END IF;
3811: set_transaction_type;
3804: l_processed_flag := 'Y';
3805: --l_record_type := 'Receipt_refund';
3806: x_name := 'Refunds_and_Voids';
3807: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3808: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3809: 'BEFORE SET_TRANSACTION_TYPE');
3810: END IF;
3811: set_transaction_type;
3812: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3808: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3809: 'BEFORE SET_TRANSACTION_TYPE');
3810: END IF;
3811: set_transaction_type;
3812: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3813: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3814: 'AFTER SET_TRANSACTION_TYPE');
3815: END IF;
3816: -- Added for WAPA bug fix.
3809: 'BEFORE SET_TRANSACTION_TYPE');
3810: END IF;
3811: set_transaction_type;
3812: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3813: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3814: 'AFTER SET_TRANSACTION_TYPE');
3815: END IF;
3816: -- Added for WAPA bug fix.
3817: -- Bug 1013752
3845: -- Hence accomplish date would be ref6.
3846: l_accomplish_date := l_reference_6;
3847: IF l_reference_3 IS NULL THEN -- ref3-0
3848: -- Case when ref1 is null, ref3 is null and ref6 is not null
3849: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3850: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
3851: 'CASE:REF1,REF3 ARE NULL; REF6 IS NOT NULL');
3852: END IF;
3853: Set_Exception('D');
3846: l_accomplish_date := l_reference_6;
3847: IF l_reference_3 IS NULL THEN -- ref3-0
3848: -- Case when ref1 is null, ref3 is null and ref6 is not null
3849: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3850: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
3851: 'CASE:REF1,REF3 ARE NULL; REF6 IS NOT NULL');
3852: END IF;
3853: Set_Exception('D');
3854: GOTO end_label;
3853: Set_Exception('D');
3854: GOTO end_label;
3855: ELSE -- ref3-0
3856: -- Case when ref1 is null,ref3 and ref6 are not null
3857: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3858: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3859: 'CASE:REF1 IS NULL, REF3 AND REF6 ARE NOT NULL');
3860: END IF;
3861: BEGIN
3854: GOTO end_label;
3855: ELSE -- ref3-0
3856: -- Case when ref1 is null,ref3 and ref6 are not null
3857: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3858: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3859: 'CASE:REF1 IS NULL, REF3 AND REF6 ARE NOT NULL');
3860: END IF;
3861: BEGIN
3862: SELECT ce_bank_acct_use_id
3862: SELECT ce_bank_acct_use_id
3863: INTO vg_bank_acct_id
3864: FROM Ap_Checks_All
3865: WHERE check_id = TO_NUMBER(l_reference_3);
3866: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3867: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
3868: END IF;
3869: EXCEPTION
3870: WHEN NO_DATA_FOUND THEN
3863: INTO vg_bank_acct_id
3864: FROM Ap_Checks_All
3865: WHERE check_id = TO_NUMBER(l_reference_3);
3866: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3867: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
3868: END IF;
3869: EXCEPTION
3870: WHEN NO_DATA_FOUND THEN
3871: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3867: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
3868: END IF;
3869: EXCEPTION
3870: WHEN NO_DATA_FOUND THEN
3871: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3872: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3873: 'UNABLE TO FIND THE BANK ACCOUNT ID');
3874: END IF;
3875: -- Unable to find the bank_account_id
3868: END IF;
3869: EXCEPTION
3870: WHEN NO_DATA_FOUND THEN
3871: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3872: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3873: 'UNABLE TO FIND THE BANK ACCOUNT ID');
3874: END IF;
3875: -- Unable to find the bank_account_id
3876: Set_Exception('D');
3878: WHEN OTHERS THEN
3879: error_buf := SQLERRM||'- Error while deriving the '||
3880: 'bank_account_id from Ap_Checks_All table.';
3881: error_code := 2;
3882: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error14', error_buf) ;
3883: RETURN;
3884: END;
3885: END IF; -- ref3-0
3886: ELSE -- ref1
3887: -- Case when ref1 is not null
3888: IF l_reference_3 IS NULL THEN -- ref3
3889: IF l_reference_6 IS NULL THEN -- ref6-1
3890: -- Case when ref1 is not null,ref3 and ref6 are null
3891: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3892: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3893: 'CASE:REF1 IS NOT NULL;REF3 AND REF6 ARE NULL');
3894: END IF;
3895: BEGIN
3888: IF l_reference_3 IS NULL THEN -- ref3
3889: IF l_reference_6 IS NULL THEN -- ref6-1
3890: -- Case when ref1 is not null,ref3 and ref6 are null
3891: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3892: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3893: 'CASE:REF1 IS NOT NULL;REF3 AND REF6 ARE NULL');
3894: END IF;
3895: BEGIN
3896: SELECT checkrun_name,treasury_doc_date,org_id
3896: SELECT checkrun_name,treasury_doc_date,org_id
3897: INTO vl_checkrun_name, l_accomplish_date, l_org_id
3898: FROM Fv_Treasury_Confirmations_All
3899: WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
3900: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3901: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3902: 'FOUND THE CHECKRUN_NAME,DOC_DATE,ORG_ID');
3903: END IF;
3904: EXCEPTION
3897: INTO vl_checkrun_name, l_accomplish_date, l_org_id
3898: FROM Fv_Treasury_Confirmations_All
3899: WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
3900: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3901: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3902: 'FOUND THE CHECKRUN_NAME,DOC_DATE,ORG_ID');
3903: END IF;
3904: EXCEPTION
3905: WHEN OTHERS THEN
3906: error_buf := SQLERRM||'- Error while deriving the '||
3907: 'checkrun_name,treasury_doc_date from '||
3908: 'Fv_Treasury_Confirmations_All table.';
3909: error_code := 2;
3910: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error15', error_buf) ;
3911: RETURN;
3912: END;
3913: ELSE -- ref6-1
3914: -- Case when ref1 is not null,ref3 is null, and ref6 is not null
3911: RETURN;
3912: END;
3913: ELSE -- ref6-1
3914: -- Case when ref1 is not null,ref3 is null, and ref6 is not null
3915: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3916: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3917: 'CASE:REF1 AND REF6 ARE NOT NULL;REF3 IS NULL');
3918: END IF;
3919: l_accomplish_date := l_reference_6;
3912: END;
3913: ELSE -- ref6-1
3914: -- Case when ref1 is not null,ref3 is null, and ref6 is not null
3915: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3916: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3917: 'CASE:REF1 AND REF6 ARE NOT NULL;REF3 IS NULL');
3918: END IF;
3919: l_accomplish_date := l_reference_6;
3920: BEGIN
3921: SELECT checkrun_name,org_id
3922: INTO vl_checkrun_name,l_org_id
3923: FROM Fv_Treasury_Confirmations_All
3924: WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
3925: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3926: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3927: 'FOUND THE CHECKRUN_NAME,ORG_ID');
3928: END IF;
3929: EXCEPTION
3922: INTO vl_checkrun_name,l_org_id
3923: FROM Fv_Treasury_Confirmations_All
3924: WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
3925: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3926: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3927: 'FOUND THE CHECKRUN_NAME,ORG_ID');
3928: END IF;
3929: EXCEPTION
3930: WHEN OTHERS THEN
3930: WHEN OTHERS THEN
3931: error_buf := SQLERRM||'- Error while deriving the '||
3932: 'checkrun_name from Fv_Treasury_Confirmations_All table.';
3933: error_code := 2;
3934: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error16', error_buf);
3935: RETURN;
3936: END;
3937: END IF; -- ref6-1
3938: IF (vl_checkrun_name IS NOT NULL) THEN -- vl_checkrun
3941: INTO vg_bank_acct_id
3942: FROM Ap_Inv_Selection_Criteria_All
3943: WHERE checkrun_name = vl_checkrun_name
3944: AND org_id = l_org_id;
3945: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3946: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3947: 'FOUND THE BANK ACCOUNT ID.');
3948: END IF;
3949: EXCEPTION
3942: FROM Ap_Inv_Selection_Criteria_All
3943: WHERE checkrun_name = vl_checkrun_name
3944: AND org_id = l_org_id;
3945: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3946: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3947: 'FOUND THE BANK ACCOUNT ID.');
3948: END IF;
3949: EXCEPTION
3950: WHEN NO_DATA_FOUND THEN
3947: 'FOUND THE BANK ACCOUNT ID.');
3948: END IF;
3949: EXCEPTION
3950: WHEN NO_DATA_FOUND THEN
3951: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3952: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3953: 'UNABLE TO FIND BANK ACCOUNT ID');
3954: END IF;
3955: -- Unable to find the bank_account_id
3948: END IF;
3949: EXCEPTION
3950: WHEN NO_DATA_FOUND THEN
3951: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3952: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3953: 'UNABLE TO FIND BANK ACCOUNT ID');
3954: END IF;
3955: -- Unable to find the bank_account_id
3956: Set_Exception('D');
3958: WHEN OTHERS THEN
3959: error_buf := SQLERRM||'- Error while deriving the '||
3960: 'bank_account_id from Ap_Inv_Selection_Criteria_All table.';
3961: error_code := 2;
3962: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error17', error_buf) ;
3963: RETURN;
3964: END;
3965: END IF; -- vl_checkrun
3966: ELSIF l_reference_3 IS NOT NULL THEN -- ref3
3963: RETURN;
3964: END;
3965: END IF; -- vl_checkrun
3966: ELSIF l_reference_3 IS NOT NULL THEN -- ref3
3967: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3968: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASE WHEN REF3 IS NOT NULL');
3969: END IF;
3970: IF l_reference_6 IS NULL THEN -- ref6-2
3971: -- Case when ref1 and ref3 is not null, and ref6 is null
3964: END;
3965: END IF; -- vl_checkrun
3966: ELSIF l_reference_3 IS NOT NULL THEN -- ref3
3967: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3968: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CASE WHEN REF3 IS NOT NULL');
3969: END IF;
3970: IF l_reference_6 IS NULL THEN -- ref6-2
3971: -- Case when ref1 and ref3 is not null, and ref6 is null
3972: -- In this scenario, we determine the accomplish date,
3970: IF l_reference_6 IS NULL THEN -- ref6-2
3971: -- Case when ref1 and ref3 is not null, and ref6 is null
3972: -- In this scenario, we determine the accomplish date,
3973: -- based on the reference_1, and alc based on reference_3.
3974: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3975: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3976: 'CASE:REF1 AND REF3 ARE NOT NULL;REF6 IS NULL');
3977: END IF;
3978: BEGIN
3971: -- Case when ref1 and ref3 is not null, and ref6 is null
3972: -- In this scenario, we determine the accomplish date,
3973: -- based on the reference_1, and alc based on reference_3.
3974: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3975: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3976: 'CASE:REF1 AND REF3 ARE NOT NULL;REF6 IS NULL');
3977: END IF;
3978: BEGIN
3979: SELECT treasury_doc_date
3979: SELECT treasury_doc_date
3980: INTO l_accomplish_date
3981: FROM Fv_Treasury_Confirmations_All
3982: WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
3983: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3984: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND TREASURY_DOC_DATE');
3985: END IF;
3986: EXCEPTION
3987: WHEN OTHERS THEN
3980: INTO l_accomplish_date
3981: FROM Fv_Treasury_Confirmations_All
3982: WHERE treasury_confirmation_id = TO_NUMBER(l_reference_1);
3983: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3984: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND TREASURY_DOC_DATE');
3985: END IF;
3986: EXCEPTION
3987: WHEN OTHERS THEN
3988: error_buf := SQLERRM||'- Error while deriving the '||
3987: WHEN OTHERS THEN
3988: error_buf := SQLERRM||'- Error while deriving the '||
3989: 'treasury_doc_date from Fv_Treasury_Confirmations_All table.';
3990: error_code := 2;
3991: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error18', error_buf) ;
3992: RETURN;
3993: END;
3994: ELSE -- ref6-2
3995: -- Case when ref3 is not null, and ref6 is not null
3992: RETURN;
3993: END;
3994: ELSE -- ref6-2
3995: -- Case when ref3 is not null, and ref6 is not null
3996: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3997: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3998: 'CASE:REF1,REF3 AND REF6 ARE NOT NULL');
3999: END IF;
4000: l_accomplish_date := l_reference_6;
3993: END;
3994: ELSE -- ref6-2
3995: -- Case when ref3 is not null, and ref6 is not null
3996: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3997: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3998: 'CASE:REF1,REF3 AND REF6 ARE NOT NULL');
3999: END IF;
4000: l_accomplish_date := l_reference_6;
4001: END IF; -- ref6-2
4003: SELECT ce_bank_acct_use_id
4004: INTO vg_bank_acct_id
4005: FROM Ap_Checks_All
4006: WHERE check_id = TO_NUMBER(l_reference_3);
4007: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4008: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
4009: END IF;
4010: EXCEPTION
4011: WHEN NO_DATA_FOUND THEN
4004: INTO vg_bank_acct_id
4005: FROM Ap_Checks_All
4006: WHERE check_id = TO_NUMBER(l_reference_3);
4007: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4008: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCOUNT ID');
4009: END IF;
4010: EXCEPTION
4011: WHEN NO_DATA_FOUND THEN
4012: -- Unable to find the bank_account_id
4015: WHEN OTHERS THEN
4016: error_buf := SQLERRM||'- Error while deriving the '||
4017: 'bank_account_id from Ap_Checks_All table.';
4018: error_code := 2;
4019: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error19', error_buf) ;
4020: RETURN;
4021: END;
4022: END IF; -- ref3
4023: END IF; -- ref1
4021: END;
4022: END IF; -- ref3
4023: END IF; -- ref1
4024: -- Determine the Alc for the bank_account_id found above.
4025: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4026: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GETTING THE ALC..');
4027: END IF;
4028: Get_Alc
4029: (
4022: END IF; -- ref3
4023: END IF; -- ref1
4024: -- Determine the Alc for the bank_account_id found above.
4025: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4026: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GETTING THE ALC..');
4027: END IF;
4028: Get_Alc
4029: (
4030: p_bank_acct_id => vg_bank_acct_id,
4045: WHEN OTHERS THEN
4046: error_code := 2;
4047: error_buf := SQLERRM||'--Error while deriving the org_id, in the '||
4048: 'procedure Process_Sf224_Transactions.';
4049: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error20', error_buf) ;
4050: END;
4051: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4052: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
4053: 'ORG ID OF THE TXN IS '||TO_CHAR(L_ORG_ID));
4047: error_buf := SQLERRM||'--Error while deriving the org_id, in the '||
4048: 'procedure Process_Sf224_Transactions.';
4049: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error20', error_buf) ;
4050: END;
4051: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4052: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
4053: 'ORG ID OF THE TXN IS '||TO_CHAR(L_ORG_ID));
4054: END IF;
4055: IF l_org_id IS NULL THEN
4048: 'procedure Process_Sf224_Transactions.';
4049: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error20', error_buf) ;
4050: END;
4051: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4052: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
4053: 'ORG ID OF THE TXN IS '||TO_CHAR(L_ORG_ID));
4054: END IF;
4055: IF l_org_id IS NULL THEN
4056: l_payables_ia_paygroup := p_def_p_ia_paygroup ;
4064: WHEN OTHERS THEN
4065: error_code := 2;
4066: error_buf := SQLERRM ||'--Error while deriving the '||
4067: 'payables_ia_paygroup in the procedure Process_Sf224_Transactions';
4068: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error21', error_buf) ;
4069: END;
4070: END IF ;
4071: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4072: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4067: 'payables_ia_paygroup in the procedure Process_Sf224_Transactions';
4068: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error21', error_buf) ;
4069: END;
4070: END IF ;
4071: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4072: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4073: 'PAYBLES PAY GROUP IS '||L_PAYABLES_IA_PAYGROUP);
4074: END IF;
4075: l_record_type := 'Disbursement';
4068: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error21', error_buf) ;
4069: END;
4070: END IF ;
4071: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4072: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4073: 'PAYBLES PAY GROUP IS '||L_PAYABLES_IA_PAYGROUP);
4074: END IF;
4075: l_record_type := 'Disbursement';
4076: BEGIN /* to process DIT payments */
4097: EXCEPTION
4098: WHEN too_many_rows THEN
4099: error_buf := 'Too many rows in invoice info,dit select';
4100: error_code := g_FAILURE;
4101: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error22', error_buf) ;
4102: RETURN;
4103: WHEN No_Data_Found THEN
4104: l_ia_flag := 'N' ;
4105: END;
4102: RETURN;
4103: WHEN No_Data_Found THEN
4104: l_ia_flag := 'N' ;
4105: END;
4106: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4107: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4108: 'VENDOR ID,INVOICE AMT,CHECK DATE ARE: '||
4109: TO_CHAR(l_vendor_id)||' '||TO_CHAR(l_inv_amount)||' '||
4110: TO_CHAR(l_check_date, 'MM/DD/YYYY'));
4103: WHEN No_Data_Found THEN
4104: l_ia_flag := 'N' ;
4105: END;
4106: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4107: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4108: 'VENDOR ID,INVOICE AMT,CHECK DATE ARE: '||
4109: TO_CHAR(l_vendor_id)||' '||TO_CHAR(l_inv_amount)||' '||
4110: TO_CHAR(l_check_date, 'MM/DD/YYYY'));
4111: END IF;
4108: 'VENDOR ID,INVOICE AMT,CHECK DATE ARE: '||
4109: TO_CHAR(l_vendor_id)||' '||TO_CHAR(l_inv_amount)||' '||
4110: TO_CHAR(l_check_date, 'MM/DD/YYYY'));
4111: END IF;
4112: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4113: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'INTERAGENCY FLAG IS '||L_IA_FLAG);
4114: END IF;
4115: l_error_stage := 1;
4116: BEGIN
4109: TO_CHAR(l_vendor_id)||' '||TO_CHAR(l_inv_amount)||' '||
4110: TO_CHAR(l_check_date, 'MM/DD/YYYY'));
4111: END IF;
4112: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4113: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'INTERAGENCY FLAG IS '||L_IA_FLAG);
4114: END IF;
4115: l_error_stage := 1;
4116: BEGIN
4117: SELECT ce_bank_acct_use_id
4126: error_buf := SQLERRM||'- Error while deriving the '||
4127: 'bank_account_id from Ap_Checks_All table when '||
4128: 'category<>Trea Conf.';
4129: error_code := 2;
4130: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error24', error_buf) ;
4131: RETURN;
4132: END;
4133: -- Determine the Alc for the bank_account_id found above.
4134: Get_Alc
4148: EXCEPTION
4149: WHEN too_many_rows THEN
4150: error_buf := 'Too many rows in chargeback flag Prelim select';
4151: error_code := g_FAILURE;
4152: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error23', error_buf) ;
4153: RETURN;
4154: END;
4155: END IF ;
4156: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4152: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error23', error_buf) ;
4153: RETURN;
4154: END;
4155: END IF ;
4156: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4157: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4158: 'CHARGEBACK FLAG AND BILLING AGENCY FUND ARE :'
4159: || L_CB_FLAG ||' '|| L_BILLING_AGENCY_FUND);
4160: END IF;
4153: RETURN;
4154: END;
4155: END IF ;
4156: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4157: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4158: 'CHARGEBACK FLAG AND BILLING AGENCY FUND ARE :'
4159: || L_CB_FLAG ||' '|| L_BILLING_AGENCY_FUND);
4160: END IF;
4161: BEGIN /* Void Date */
4166: FROM ap_checks_all apc,
4167: ap_invoices_all api
4168: WHERE api.invoice_id = Nvl(l_reference, 0)
4169: AND apc.check_id = nvl(l_reference_3,0);
4170: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4171: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4172: 'CHECK DATE/ACCOM DATE AND VOID DATE ARE '||
4173: TO_CHAR(l_accomplish_date, 'MM/DD/YYYY')||' '||
4174: TO_CHAR(l_void_date, 'MM/DD/YYYY'));
4167: ap_invoices_all api
4168: WHERE api.invoice_id = Nvl(l_reference, 0)
4169: AND apc.check_id = nvl(l_reference_3,0);
4170: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4171: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4172: 'CHECK DATE/ACCOM DATE AND VOID DATE ARE '||
4173: TO_CHAR(l_accomplish_date, 'MM/DD/YYYY')||' '||
4174: TO_CHAR(l_void_date, 'MM/DD/YYYY'));
4175: END IF;
4181: FROM ap_invoice_payments
4182: WHERE invoice_id = NVL(l_reference, 0)
4183: AND check_id = NVL(l_reference_3,0)
4184: AND invoice_payment_id >l_reference_9;
4185: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4186: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4187: 'VOID DATE IS NOT NULL AND INVOICE '||
4188: 'payment id is '||TO_CHAR(l_inv_pay_id));
4189: END IF;
4182: WHERE invoice_id = NVL(l_reference, 0)
4183: AND check_id = NVL(l_reference_3,0)
4184: AND invoice_payment_id >l_reference_9;
4185: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4186: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4187: 'VOID DATE IS NOT NULL AND INVOICE '||
4188: 'payment id is '||TO_CHAR(l_inv_pay_id));
4189: END IF;
4190: IF (l_inv_pay_id = 0) THEN
4190: IF (l_inv_pay_id = 0) THEN
4191: l_accomplish_date := l_void_date ;
4192: l_record_type := 'VOID';
4193: BEGIN /* V1 */
4194: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4195: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4196: 'Before Getting the Obligation Date');
4197: END IF;
4198: l_obligation_date := get_void_check_obligation_date
4191: l_accomplish_date := l_void_date ;
4192: l_record_type := 'VOID';
4193: BEGIN /* V1 */
4194: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4195: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4196: 'Before Getting the Obligation Date');
4197: END IF;
4198: l_obligation_date := get_void_check_obligation_date
4199: ( TO_NUMBER(l_reference_2),
4203: l_record_type := 'VOID';
4204: l_processed_flag := 'Y';
4205: l_update_type := 'VOID_PAYABLE';
4206: l_type := 'VOID';
4207: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4208: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4209: 'OBLIGATION DATE IS '||TO_CHAR(l_obligation_date, 'MM/DD/YYYY'));
4210: END IF;
4211: EXCEPTION
4204: l_processed_flag := 'Y';
4205: l_update_type := 'VOID_PAYABLE';
4206: l_type := 'VOID';
4207: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4208: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4209: 'OBLIGATION DATE IS '||TO_CHAR(l_obligation_date, 'MM/DD/YYYY'));
4210: END IF;
4211: EXCEPTION
4212: WHEN No_Data_Found THEN
4209: 'OBLIGATION DATE IS '||TO_CHAR(l_obligation_date, 'MM/DD/YYYY'));
4210: END IF;
4211: EXCEPTION
4212: WHEN No_Data_Found THEN
4213: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4214: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4215: 'NO DATE FUND Exception while getting the Obligation Date');
4216: END IF;
4217: error_buf := 'No Data Fund Exception while getting the Obligation Date for VOID';
4210: END IF;
4211: EXCEPTION
4212: WHEN No_Data_Found THEN
4213: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4214: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4215: 'NO DATE FUND Exception while getting the Obligation Date');
4216: END IF;
4217: error_buf := 'No Data Fund Exception while getting the Obligation Date for VOID';
4218: error_code := g_FAILURE;
4215: 'NO DATE FUND Exception while getting the Obligation Date');
4216: END IF;
4217: error_buf := 'No Data Fund Exception while getting the Obligation Date for VOID';
4218: error_code := g_FAILURE;
4219: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error25', error_buf);
4220: RETURN;
4221: WHEN too_many_rows THEN
4222: error_buf := 'Too many rows in obligation_date select';
4223: error_code := g_FAILURE;
4220: RETURN;
4221: WHEN too_many_rows THEN
4222: error_buf := 'Too many rows in obligation_date select';
4223: error_code := g_FAILURE;
4224: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error25', error_buf);
4225: RETURN;
4226: END ; /* V1 */
4227: END IF;
4228: END IF;
4230: EXCEPTION
4231: WHEN too_many_rows THEN
4232: error_buf := 'Too many rows in void_date disbursement select';
4233: error_code := g_FAILURE;
4234: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error26', error_buf) ;
4235: RETURN;
4236: WHEN no_data_found THEN
4237: NULL;
4238: WHEN OTHERS THEN
4237: NULL;
4238: WHEN OTHERS THEN
4239: error_Buf := sqlerrm ;
4240: error_Code := g_FAILURE ;
4241: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error27', error_buf) ;
4242: ROLLBACK ;
4243: RETURN;
4244: END ; /* Void Date */
4245: IF (l_ia_flag = 'Y' AND l_error_stage <> g_FAILURE) THEN
4279: l_accomplish_date := l_check_date ;
4280: END IF;
4281: END IF; /* charge back flag = 'Y' */
4282: END IF;
4283: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4284: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4285: 'RECORD TYPE IS '||L_RECORD_TYPE);
4286: END IF;
4287: EXCEPTION
4280: END IF;
4281: END IF; /* charge back flag = 'Y' */
4282: END IF;
4283: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4284: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4285: 'RECORD TYPE IS '||L_RECORD_TYPE);
4286: END IF;
4287: EXCEPTION
4288: WHEN no_data_found THEN
4300: END IF;
4301: WHEN others THEN
4302: error_buf := sqlerrm;
4303: error_code := g_FAILURE;
4304: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error28', error_buf) ;
4305: ROLLBACK;
4306: RETURN;
4307: END ; /* End proces DIT */
4308: ELSIF (l_je_category = 'Treasury Confirmation' AND
4313: SELECT void_date
4314: INTO l_accomplish_date
4315: FROM ap_checks_all
4316: WHERE check_id = To_number (nvl(l_reference_3,'0')) ;
4317: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4318: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4319: 'VOID DATE/ACCOMPLISH DATE IS '||
4320: TO_CHAR(l_accomplish_date, 'MM/DD/YYYY'));
4321: END IF;
4314: INTO l_accomplish_date
4315: FROM ap_checks_all
4316: WHERE check_id = To_number (nvl(l_reference_3,'0')) ;
4317: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4318: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4319: 'VOID DATE/ACCOMPLISH DATE IS '||
4320: TO_CHAR(l_accomplish_date, 'MM/DD/YYYY'));
4321: END IF;
4322: -- invoice_id
4323: SELECT invoice_date
4324: INTO l_invoice_date
4325: FROM ap_invoices_all
4326: WHERE Invoice_id = to_number (nvl(l_reference_4,'0')) ;
4327: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4328: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INVOICE DATE IS '||
4329: TO_CHAR(l_invoice_date, 'MM/DD/YYYY'));
4330: END IF;
4331: EXCEPTION
4324: INTO l_invoice_date
4325: FROM ap_invoices_all
4326: WHERE Invoice_id = to_number (nvl(l_reference_4,'0')) ;
4327: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4328: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INVOICE DATE IS '||
4329: TO_CHAR(l_invoice_date, 'MM/DD/YYYY'));
4330: END IF;
4331: EXCEPTION
4332: WHEN no_data_found THEN
4329: TO_CHAR(l_invoice_date, 'MM/DD/YYYY'));
4330: END IF;
4331: EXCEPTION
4332: WHEN no_data_found THEN
4333: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4334: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4335: 'UNABLE TO FIND EITHER THE VOID '||
4336: 'date or invoice date');
4337: END IF;
4330: END IF;
4331: EXCEPTION
4332: WHEN no_data_found THEN
4333: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4334: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4335: 'UNABLE TO FIND EITHER THE VOID '||
4336: 'date or invoice date');
4337: END IF;
4338: l_billing_agency_fund := 'UNDEFINED';
4348: SELECT ce_bank_acct_use_id
4349: INTO vg_bank_acct_id
4350: FROM Ap_Checks_All
4351: WHERE check_id = TO_NUMBER(l_reference_3);
4352: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4353: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCT ID');
4354: END IF;
4355: EXCEPTION
4356: WHEN NO_DATA_FOUND THEN
4349: INTO vg_bank_acct_id
4350: FROM Ap_Checks_All
4351: WHERE check_id = TO_NUMBER(l_reference_3);
4352: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4353: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOUND THE BANK ACCT ID');
4354: END IF;
4355: EXCEPTION
4356: WHEN NO_DATA_FOUND THEN
4357: -- Unable to find the bank_account_id
4354: END IF;
4355: EXCEPTION
4356: WHEN NO_DATA_FOUND THEN
4357: -- Unable to find the bank_account_id
4358: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4359: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4360: 'UNABLE TO FIND THE BANK ACCT ID');
4361: END IF;
4362: Set_Exception('D');
4355: EXCEPTION
4356: WHEN NO_DATA_FOUND THEN
4357: -- Unable to find the bank_account_id
4358: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4359: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4360: 'UNABLE TO FIND THE BANK ACCT ID');
4361: END IF;
4362: Set_Exception('D');
4363: WHEN OTHERS THEN
4363: WHEN OTHERS THEN
4364: error_buf := SQLERRM||'- Error while deriving the '||
4365: 'bank_account_id from Ap_Checks_All table when name like VOID.';
4366: error_code := 2;
4367: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error29', error_buf) ;
4368: RETURN;
4369: END;
4370: -- Determine the Alc for the bank_account_id found above.
4371: Get_Alc
4378: /* Reassigning l_reference_4 to l_reference_2. This is because
4379: the process is saving invoice_id in reference_2 column. */
4380: l_reference_2 := l_reference_4;
4381: BEGIN /* V1 */
4382: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4383: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4384: 'Before Calling the get_void_check_obligation_date Procedure ... ');
4385: END IF;
4386: l_obligation_date :=get_void_check_obligation_date
4379: the process is saving invoice_id in reference_2 column. */
4380: l_reference_2 := l_reference_4;
4381: BEGIN /* V1 */
4382: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4383: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4384: 'Before Calling the get_void_check_obligation_date Procedure ... ');
4385: END IF;
4386: l_obligation_date :=get_void_check_obligation_date
4387: ( TO_NUMBER(l_reference_2),
4387: ( TO_NUMBER(l_reference_2),
4388: TO_NUMBER(l_reference_3),
4389: TO_NUMBER(l_reference_8)
4390: );
4391: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4392: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4393: 'After Calling the get_void_check_obligation_date Procedure l_obligation_date ');
4394: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'OBLIGATION DATE IS '||
4395: TO_CHAR(l_obligation_date, 'MM/DD/YYYY'));
4388: TO_NUMBER(l_reference_3),
4389: TO_NUMBER(l_reference_8)
4390: );
4391: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4392: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4393: 'After Calling the get_void_check_obligation_date Procedure l_obligation_date ');
4394: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'OBLIGATION DATE IS '||
4395: TO_CHAR(l_obligation_date, 'MM/DD/YYYY'));
4396: END IF;
4390: );
4391: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4392: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4393: 'After Calling the get_void_check_obligation_date Procedure l_obligation_date ');
4394: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'OBLIGATION DATE IS '||
4395: TO_CHAR(l_obligation_date, 'MM/DD/YYYY'));
4396: END IF;
4397: l_record_type := 'VOID';
4398: l_processed_flag := 'Y';
4399: l_update_type := 'VOID_PAYABLE';
4400: l_type := 'VOID';
4401: EXCEPTION
4402: WHEN No_Data_Found THEN
4403: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4404: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4405: 'UNABLE TO FIND OBLIGATION DATE');
4406: END IF;
4407: error_buf := 'No Data Fund Exception while getting the Obligation Date for VOID';
4400: l_type := 'VOID';
4401: EXCEPTION
4402: WHEN No_Data_Found THEN
4403: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4404: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4405: 'UNABLE TO FIND OBLIGATION DATE');
4406: END IF;
4407: error_buf := 'No Data Fund Exception while getting the Obligation Date for VOID';
4408: error_code := g_FAILURE;
4405: 'UNABLE TO FIND OBLIGATION DATE');
4406: END IF;
4407: error_buf := 'No Data Fund Exception while getting the Obligation Date for VOID';
4408: error_code := g_FAILURE;
4409: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error25', error_buf) ;
4410: RETURN;
4411: WHEN too_many_rows THEN
4412: error_buf := 'Too many rows in obligation_date select';
4413: error_code := g_FAILURE;
4410: RETURN;
4411: WHEN too_many_rows THEN
4412: error_buf := 'Too many rows in obligation_date select';
4413: error_code := g_FAILURE;
4414: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error30', error_buf) ;
4415: RETURN;
4416: END ; /* V1 */
4417: ELSIF (l_je_source = 'Project Accounting') AND(l_je_category ='Labor Cost') THEN -- source2
4418: IF (l_exception_category = 'NO_PA_ACCOMPLISH_DATE') THEN
4428: l_record_type := 'MANUAL';
4429: IF (x_name = 'MANUAL') THEN
4430: Set_Exception('R');
4431: END IF;
4432: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4433: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ACCOMPLISH DATE IS '
4434: ||TO_CHAR(l_accomplish_date, 'MM/DD/YYYY'));
4435: END IF;
4436: END IF; -- Manual
4429: IF (x_name = 'MANUAL') THEN
4430: Set_Exception('R');
4431: END IF;
4432: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4433: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ACCOMPLISH DATE IS '
4434: ||TO_CHAR(l_accomplish_date, 'MM/DD/YYYY'));
4435: END IF;
4436: END IF; -- Manual
4437: ------------------------------------------------------------
4441: AND g_payment_type_flag = 'R'
4442: AND g_invoice_type_lookup_code IN ( 'CREDIT','DEBIT')
4443: AND l_reference_2 IS NOT NULL AND l_reference_3 IS NOT NULL
4444: AND l_reference_8 IS NOT NULL THEN /*g_payment_type_flag = 'R' */
4445: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4446: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_payment_type_flag = R');
4447: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_invoice_type_lookup_code ' ||
4448: g_invoice_type_lookup_code);
4449: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Check Void Date : '||
4442: AND g_invoice_type_lookup_code IN ( 'CREDIT','DEBIT')
4443: AND l_reference_2 IS NOT NULL AND l_reference_3 IS NOT NULL
4444: AND l_reference_8 IS NOT NULL THEN /*g_payment_type_flag = 'R' */
4445: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4446: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_payment_type_flag = R');
4447: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_invoice_type_lookup_code ' ||
4448: g_invoice_type_lookup_code);
4449: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Check Void Date : '||
4450: g_check_void_date );
4443: AND l_reference_2 IS NOT NULL AND l_reference_3 IS NOT NULL
4444: AND l_reference_8 IS NOT NULL THEN /*g_payment_type_flag = 'R' */
4445: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4446: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_payment_type_flag = R');
4447: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_invoice_type_lookup_code ' ||
4448: g_invoice_type_lookup_code);
4449: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Check Void Date : '||
4450: g_check_void_date );
4451: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_1 : ' || l_reference_1 );
4445: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4446: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_payment_type_flag = R');
4447: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_invoice_type_lookup_code ' ||
4448: g_invoice_type_lookup_code);
4449: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Check Void Date : '||
4450: g_check_void_date );
4451: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_1 : ' || l_reference_1 );
4452: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_2 (INV ID) : ' ||
4453: l_reference_2 );
4447: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_invoice_type_lookup_code ' ||
4448: g_invoice_type_lookup_code);
4449: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Check Void Date : '||
4450: g_check_void_date );
4451: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_1 : ' || l_reference_1 );
4452: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_2 (INV ID) : ' ||
4453: l_reference_2 );
4454: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_3 (CHECK ID) : ' ||
4455: l_reference_3 );
4448: g_invoice_type_lookup_code);
4449: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Check Void Date : '||
4450: g_check_void_date );
4451: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_1 : ' || l_reference_1 );
4452: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_2 (INV ID) : ' ||
4453: l_reference_2 );
4454: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_3 (CHECK ID) : ' ||
4455: l_reference_3 );
4456: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_4 : ' || l_reference_4 );
4450: g_check_void_date );
4451: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_1 : ' || l_reference_1 );
4452: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_2 (INV ID) : ' ||
4453: l_reference_2 );
4454: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_3 (CHECK ID) : ' ||
4455: l_reference_3 );
4456: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_4 : ' || l_reference_4 );
4457: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_5 : ' || l_reference_5 );
4458: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_6 : ' || l_reference_6 );
4452: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_2 (INV ID) : ' ||
4453: l_reference_2 );
4454: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_3 (CHECK ID) : ' ||
4455: l_reference_3 );
4456: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_4 : ' || l_reference_4 );
4457: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_5 : ' || l_reference_5 );
4458: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_6 : ' || l_reference_6 );
4459: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_8 (INV DIST NO) : ' ||
4460: l_reference_8 );
4453: l_reference_2 );
4454: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_3 (CHECK ID) : ' ||
4455: l_reference_3 );
4456: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_4 : ' || l_reference_4 );
4457: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_5 : ' || l_reference_5 );
4458: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_6 : ' || l_reference_6 );
4459: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_8 (INV DIST NO) : ' ||
4460: l_reference_8 );
4461: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_9 : ' || l_reference_9 );
4454: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_3 (CHECK ID) : ' ||
4455: l_reference_3 );
4456: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_4 : ' || l_reference_4 );
4457: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_5 : ' || l_reference_5 );
4458: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_6 : ' || l_reference_6 );
4459: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_8 (INV DIST NO) : ' ||
4460: l_reference_8 );
4461: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_9 : ' || l_reference_9 );
4462: END IF;
4455: l_reference_3 );
4456: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_4 : ' || l_reference_4 );
4457: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_5 : ' || l_reference_5 );
4458: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_6 : ' || l_reference_6 );
4459: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_8 (INV DIST NO) : ' ||
4460: l_reference_8 );
4461: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_9 : ' || l_reference_9 );
4462: END IF;
4463: BEGIN /* Refund Checks */
4457: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_5 : ' || l_reference_5 );
4458: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_6 : ' || l_reference_6 );
4459: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_8 (INV DIST NO) : ' ||
4460: l_reference_8 );
4461: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_reference_9 : ' || l_reference_9 );
4462: END IF;
4463: BEGIN /* Refund Checks */
4464: -- Bug 10240460: As the obligation date is same for a particular invoice_id and since multiple
4465: -- distribution_line_number can contain the same invoice_id, using max(obligation_date).
4472: AND invoice_id = l_reference_2;
4473: --AND check_id = l_reference_3
4474: -- AND invoice_payment_id = l_reference_9
4475: --AND distribution_line_number = l_reference_8;
4476: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4477: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Obligation Date : ' ||
4478: l_obligation_date );
4479: END IF;
4480: l_processed_flag := 'Y';
4473: --AND check_id = l_reference_3
4474: -- AND invoice_payment_id = l_reference_9
4475: --AND distribution_line_number = l_reference_8;
4476: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4477: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Obligation Date : ' ||
4478: l_obligation_date );
4479: END IF;
4480: l_processed_flag := 'Y';
4481: l_update_type := 'PAYABLE_REFUND';
4523: SELECT apc.void_check_number /* replace attribute1 with new field name */
4524: INTO l_void_check_number
4525: FROM ap_checks_all apc
4526: WHERE apc.check_id = to_number(l_reference_3);
4527: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4528: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Void Check Number : '|| l_void_check_number );
4529: END IF;
4530: EXCEPTION
4531: WHEN NO_DATA_FOUND THEN
4524: INTO l_void_check_number
4525: FROM ap_checks_all apc
4526: WHERE apc.check_id = to_number(l_reference_3);
4527: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4528: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Void Check Number : '|| l_void_check_number );
4529: END IF;
4530: EXCEPTION
4531: WHEN NO_DATA_FOUND THEN
4532: l_void_check_number:=NULL;
4538: WHERE APP.CHECK_ID = TO_NUMBER(L_REFERENCE_3)
4539: AND APP.invoice_payment_id = TO_NUMBER(L_REFERENCE_9)
4540: AND APP.reversal_inv_pmt_id IS NOT NULL
4541: AND APP.reversal_flag ='Y';
4542: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4543: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Voided Reclassified Payment: '||l_voided_reclassified_payment);
4544: END IF;
4545: EXCEPTION
4546: WHEN NO_DATA_FOUND THEN
4539: AND APP.invoice_payment_id = TO_NUMBER(L_REFERENCE_9)
4540: AND APP.reversal_inv_pmt_id IS NOT NULL
4541: AND APP.reversal_flag ='Y';
4542: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4543: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Voided Reclassified Payment: '||l_voided_reclassified_payment);
4544: END IF;
4545: EXCEPTION
4546: WHEN NO_DATA_FOUND THEN
4547: l_voided_reclassified_payment:='N';
4550: BEGIN
4551: sql_stmt1:= 'SELECT ACR.' || l_ar_reclass_attribute ||
4552: ' FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.cash_receipt_id = '||l_cash_receipt_id;
4553: EXECUTE IMMEDIATE sql_stmt1 INTO l_reclass_receipt_number ;
4554: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4555: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Reclass Receipt Number: '|| l_reclass_receipt_number );
4556: END IF;
4557: EXCEPTION
4558: WHEN NO_DATA_FOUND THEN
4551: sql_stmt1:= 'SELECT ACR.' || l_ar_reclass_attribute ||
4552: ' FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.cash_receipt_id = '||l_cash_receipt_id;
4553: EXECUTE IMMEDIATE sql_stmt1 INTO l_reclass_receipt_number ;
4554: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4555: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Reclass Receipt Number: '|| l_reclass_receipt_number );
4556: END IF;
4557: EXCEPTION
4558: WHEN NO_DATA_FOUND THEN
4559: l_reclass_receipt_number:=NULL;
4567: ' and exists (SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL AC2 '||
4568: ' WHERE AC2.' || l_ar_reclass_attribute ||
4569: ' = ' || '''' || l_document_number || '''' || ' )' ;
4570: EXECUTE IMMEDIATE sql_stmt INTO l_receipt_reclassified ;
4571: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4572: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Receipt Reclassified: '|| l_receipt_reclassified );
4573: END IF;
4574: EXCEPTION
4575: WHEN NO_DATA_FOUND THEN
4568: ' WHERE AC2.' || l_ar_reclass_attribute ||
4569: ' = ' || '''' || l_document_number || '''' || ' )' ;
4570: EXECUTE IMMEDIATE sql_stmt INTO l_receipt_reclassified ;
4571: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4572: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Receipt Reclassified: '|| l_receipt_reclassified );
4573: END IF;
4574: EXCEPTION
4575: WHEN NO_DATA_FOUND THEN
4576: l_receipt_reclassified:='N';
4586: ' and exists (SELECT ''Y'' FROM AR_CASH_RECEIPTS_ALL AC2 '||
4587: ' WHERE AC2.' || l_ar_reclass_attribute ||
4588: ' = ' || '''' || l_document_number || '''' || ' )' ;
4589: EXECUTE IMMEDIATE sql_stmt INTO l_receipt_reclassified ;
4590: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4591: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Receipt Reclassified: '|| l_receipt_reclassified );
4592: END IF;
4593: EXCEPTION
4594: WHEN NO_DATA_FOUND THEN
4587: ' WHERE AC2.' || l_ar_reclass_attribute ||
4588: ' = ' || '''' || l_document_number || '''' || ' )' ;
4589: EXECUTE IMMEDIATE sql_stmt INTO l_receipt_reclassified ;
4590: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4591: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Receipt Reclassified: '|| l_receipt_reclassified );
4592: END IF;
4593: EXCEPTION
4594: WHEN NO_DATA_FOUND THEN
4595: l_receipt_reclassified:='N';
4630: IF error_code <> g_SUCCESS THEN
4631: RETURN;
4632: END IF;
4633: ELSE
4634: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' No GWA Reporter Category found for the ALC: ' || l_alc_code ||
4635: ' for the selected period: '|| l_gl_period);
4636: l_reportable:='E';
4637: END IF;
4638: ELSE
4639: l_reportable :='I';
4640: END IF;
4641:
4642: IF l_reportable='X' or l_reportable='E' THEN
4643: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4644: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'deleting the section VI, VII exception ');
4645: END IF;
4646: DELETE fv_sf224_temp fv
4647: WHERE fv.je_line_num = L_JE_LINE_NUM
4640: END IF;
4641:
4642: IF l_reportable='X' or l_reportable='E' THEN
4643: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4644: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'deleting the section VI, VII exception ');
4645: END IF;
4646: DELETE fv_sf224_temp fv
4647: WHERE fv.je_line_num = L_JE_LINE_NUM
4648: AND fv.je_header_id = L_JE_HEADER_ID
4661: l_record_category := 'EXCEPTION' ;
4662: Insert_New_Transaction(l_org_amount, 1);
4663:
4664: END IF;
4665: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4666: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATING FV_SF224_TEMP WITH THE ALC_CODE');
4667: END IF;
4668:
4669:
4662: Insert_New_Transaction(l_org_amount, 1);
4663:
4664: END IF;
4665: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4666: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATING FV_SF224_TEMP WITH THE ALC_CODE');
4667: END IF;
4668:
4669:
4670: Update fv_sf224_temp
4714:
4715: IF (sqlcode < 0) THEN
4716: error_buf := 'fv_sf224_temp table Update failed ';
4717: error_code := g_FAILURE;
4718: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error31', error_buf) ;
4719: RETURN;
4720: END IF;
4721: END LOOP; -- end c1
4722: CLOSE c1;
4719: RETURN;
4720: END IF;
4721: END LOOP; -- end c1
4722: CLOSE c1;
4723: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4724: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-----------------------------------------------');
4725: END IF;
4726: COMMIT;
4727: EXCEPTION
4720: END IF;
4721: END LOOP; -- end c1
4722: CLOSE c1;
4723: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4724: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'-----------------------------------------------');
4725: END IF;
4726: COMMIT;
4727: EXCEPTION
4728: WHEN OTHERS THEN
4730: error_buf := sqlerrm ;
4731: IF C1%ISOPEN THEN
4732: CLOSE C1;
4733: END IF ;
4734: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
4735: ROLLBACK ;
4736: RETURN ;
4737: END Process_Sf224_Transactions; /* of procedure */
4738:
4766: END;
4767: ELSE
4768: /*CHECK FOR PAYMENTS AND COLLECTION BASED ON COLUMN_GROUP */
4769: IF (l_column_group = 20 OR l_column_group = 31 ) THEN
4770: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4771: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'column group is 20 or 31 ');
4772: END IF;
4773: BEGIN
4774: SELECT mp.trx_category_coll
4767: ELSE
4768: /*CHECK FOR PAYMENTS AND COLLECTION BASED ON COLUMN_GROUP */
4769: IF (l_column_group = 20 OR l_column_group = 31 ) THEN
4770: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4771: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'column group is 20 or 31 ');
4772: END IF;
4773: BEGIN
4774: SELECT mp.trx_category_coll
4775: INTO l_reportable
4780: WHEN no_data_found THEN
4781: l_reportable:='E';
4782: END;
4783: ELSIF (l_column_group = 21 OR l_column_group = 30 ) THEN
4784: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4785: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'column group is 21 or 30 ');
4786: END IF;
4787: BEGIN
4788: SELECT mp.trx_category_pay
4781: l_reportable:='E';
4782: END;
4783: ELSIF (l_column_group = 21 OR l_column_group = 30 ) THEN
4784: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4785: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'column group is 21 or 30 ');
4786: END IF;
4787: BEGIN
4788: SELECT mp.trx_category_pay
4789: INTO l_reportable
4799: EXCEPTION
4800: WHEN OTHERS THEN
4801: p_error_code := g_FAILURE;
4802: p_error_desc := SQLERRM || '-- Error in procedure Check_partial_reporting.';
4803: fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception', p_error_desc) ;
4804: RETURN;
4805: END Check_partial_reporting;
4806:
4807:
4812: PROCEDURE Set_Exception(exp_type VARCHAR2) IS
4813: l_module_name VARCHAR2(200) ;
4814: BEGIN
4815: l_module_name := g_module_name || 'Set_Exception';
4816: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4817: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN SET_EXCEPTION PROC WITH EXP_TYPE = '||EXP_TYPE);
4818: END IF;
4819: IF (exp_type = 'R') THEN
4820: -- Reported as Manual exception
4813: l_module_name VARCHAR2(200) ;
4814: BEGIN
4815: l_module_name := g_module_name || 'Set_Exception';
4816: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4817: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN SET_EXCEPTION PROC WITH EXP_TYPE = '||EXP_TYPE);
4818: END IF;
4819: IF (exp_type = 'R') THEN
4820: -- Reported as Manual exception
4821: l_name := 'MANUAL';
4832: l_reported_flag := 'Y';
4833: l_exception_exists := 'Y';
4834: l_record_category := 'EXCEPTION';
4835: Insert_New_Transaction(l_amount, 1);
4836: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4837: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TXN INSERTED');
4838: END IF;
4839: EXCEPTION
4840: WHEN OTHERS THEN
4833: l_exception_exists := 'Y';
4834: l_record_category := 'EXCEPTION';
4835: Insert_New_Transaction(l_amount, 1);
4836: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4837: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TXN INSERTED');
4838: END IF;
4839: EXCEPTION
4840: WHEN OTHERS THEN
4841: error_code := 2;
4839: EXCEPTION
4840: WHEN OTHERS THEN
4841: error_code := 2;
4842: error_buf := SQLERRM || '-- Error in procedure Set_Exception.';
4843: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
4844: RETURN;
4845: END Set_Exception;
4846:
4847:
4887: WHEN OTHERS THEN
4888: p_error_code := g_FAILURE;
4889: p_error_desc := SQLERRM;
4890: l_location := l_module_name||'.final_exception';
4891: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4892: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4893: END Get_Alc;
4894:
4895:
4888: p_error_code := g_FAILURE;
4889: p_error_desc := SQLERRM;
4890: l_location := l_module_name||'.final_exception';
4891: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
4892: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
4893: END Get_Alc;
4894:
4895:
4896:
4992: WHEN OTHERS THEN
4993: error_code := sqlcode ;
4994: error_buf := sqlerrm ;
4995: RollBack ;
4996: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
4997: Return ;
4998: END Insert_new_transaction;
4999:
5000: ----------------------------------------------------------------------
5003: Procedure set_transaction_type is
5004: l_module_name VARCHAR2(200) ;
5005: BEGIN
5006: l_module_name := g_module_name || 'set_transaction_type';
5007: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5008: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN SET_TRANSACTION_TYPE PROC');
5009: END IF;
5010: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5011: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'EXCEPTION CATEGORY IS '||L_EXCEPTION_CATEGORY);
5004: l_module_name VARCHAR2(200) ;
5005: BEGIN
5006: l_module_name := g_module_name || 'set_transaction_type';
5007: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5008: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN SET_TRANSACTION_TYPE PROC');
5009: END IF;
5010: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5011: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'EXCEPTION CATEGORY IS '||L_EXCEPTION_CATEGORY);
5012: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD TYPE IS '||L_RECORD_TYPE);
5006: l_module_name := g_module_name || 'set_transaction_type';
5007: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5008: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN SET_TRANSACTION_TYPE PROC');
5009: END IF;
5010: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5011: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'EXCEPTION CATEGORY IS '||L_EXCEPTION_CATEGORY);
5012: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD TYPE IS '||L_RECORD_TYPE);
5013: END IF;
5014: l_sf224_type_code := upper(l_sf224_type_code);
5007: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5008: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN SET_TRANSACTION_TYPE PROC');
5009: END IF;
5010: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5011: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'EXCEPTION CATEGORY IS '||L_EXCEPTION_CATEGORY);
5012: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD TYPE IS '||L_RECORD_TYPE);
5013: END IF;
5014: l_sf224_type_code := upper(l_sf224_type_code);
5015: l_sign_number := 1;
5008: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN SET_TRANSACTION_TYPE PROC');
5009: END IF;
5010: IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5011: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'EXCEPTION CATEGORY IS '||L_EXCEPTION_CATEGORY);
5012: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECORD TYPE IS '||L_RECORD_TYPE);
5013: END IF;
5014: l_sf224_type_code := upper(l_sf224_type_code);
5015: l_sign_number := 1;
5016: IF(l_record_type = 'Receipt') THEN
5049: WHEN OTHERS THEN
5050: error_code := sqlcode ;
5051: error_buf := sqlerrm ;
5052: ROLLBACK ;
5053: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
5054: RETURN;
5055: END set_transaction_type;
5056:
5057:
5082: WHEN OTHERS THEN
5083: p_error_code := g_FAILURE;
5084: p_error_desc := SQLERRM;
5085: l_location := l_module_name||'delete_fv_sf224_temp';
5086: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
5087: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
5088: END;
5089:
5090: IF (p_error_code = g_SUCCESS) THEN
5083: p_error_code := g_FAILURE;
5084: p_error_desc := SQLERRM;
5085: l_location := l_module_name||'delete_fv_sf224_temp';
5086: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
5087: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
5088: END;
5089:
5090: IF (p_error_code = g_SUCCESS) THEN
5091: BEGIN
5102: WHEN OTHERS THEN
5103: p_error_code := g_FAILURE;
5104: p_error_desc := SQLERRM;
5105: l_location := l_module_name||'update_fv_sf224_temp';
5106: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
5107: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
5108: END;
5109: END IF;
5110:
5103: p_error_code := g_FAILURE;
5104: p_error_desc := SQLERRM;
5105: l_location := l_module_name||'update_fv_sf224_temp';
5106: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
5107: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
5108: END;
5109: END IF;
5110:
5111: EXCEPTION
5112: WHEN OTHERS THEN
5113: p_error_code := g_FAILURE;
5114: p_error_desc := SQLERRM;
5115: l_location := l_module_name||'.final_exception';
5116: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
5117: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
5118: END pre_process;
5119:
5120:
5113: p_error_code := g_FAILURE;
5114: p_error_desc := SQLERRM;
5115: l_location := l_module_name||'.final_exception';
5116: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
5117: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_error_desc) ;
5118: END pre_process;
5119:
5120:
5121:
5141: WHERE tmp.set_of_books_id = sob
5142: AND tmp.alc_code IS NOT NULL;
5143: BEGIN
5144: l_module_name := g_module_name || 'check_alc_address';
5145: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5146: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Start of check_alc_address p_alc_code => ' || p_alc) ;
5147: END IF;
5148: IF p_alc = 'ALL' THEN /* p_alc = 'ALL' */
5149: FOR alc_code_rec IN alc_code_csr LOOP
5142: AND tmp.alc_code IS NOT NULL;
5143: BEGIN
5144: l_module_name := g_module_name || 'check_alc_address';
5145: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5146: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Start of check_alc_address p_alc_code => ' || p_alc) ;
5147: END IF;
5148: IF p_alc = 'ALL' THEN /* p_alc = 'ALL' */
5149: FOR alc_code_rec IN alc_code_csr LOOP
5150: BEGIN
5155: AND set_of_books_id = sob;
5156: EXCEPTION
5157: WHEN NO_DATA_FOUND THEN
5158: IF l_alc_add_missing_count = 0 THEN
5159: fv_utility.log_mesg(fnd_log.LEVEL_ERROR, l_module_name,'ALC address not found for the following ALC(s)');
5160: error_buf := SUBSTR(error_buf,1,40) || ' WITH WARNING **' ||
5161: fnd_global.newline() || 'ALC Address Not Found For The ALC(s)' ;
5162: END IF;
5163: l_alc_add_missing_count := l_alc_add_missing_count+1;
5160: error_buf := SUBSTR(error_buf,1,40) || ' WITH WARNING **' ||
5161: fnd_global.newline() || 'ALC Address Not Found For The ALC(s)' ;
5162: END IF;
5163: l_alc_add_missing_count := l_alc_add_missing_count+1;
5164: fv_utility.log_mesg(fnd_log.LEVEL_ERROR, l_module_name,l_alc_add_missing_count || ' : ' || alc_code_rec.alc_code);
5165: error_buf := error_buf || fnd_global.newline() || l_alc_add_missing_count ||' : ' || alc_code_rec.alc_code ;
5166: END;
5167: END LOOP;
5168: ELSE /* when p_alc is not 'ALL' */
5174: AND set_of_books_id = sob;
5175: EXCEPTION
5176: WHEN NO_DATA_FOUND THEN
5177: l_alc_add_missing_count := l_alc_add_missing_count+1;
5178: fv_utility.LOG_MESG(Fnd_Log.LEVEL_ERROR, l_module_name,'ALC address not found for the following ALC ' || p_alc);
5179: error_buf := SUBSTR(error_buf,1,40) || ' WITH WARNING **' || fnd_global.newline() ||
5180: 'ALC Address Not Found For The ALC ' || p_alc ;
5181: END;
5182: END IF; /* p_alc = 'ALL' */
5185: -- error_buf := SUBSTR(error_buf,1,40) || ' WITH WARNING **' || fnd_global.newline() ||
5186: -- 'ALC address not found for the ALC(s)' ;
5187: NULL;
5188: ELSE
5189: fv_utility.LOG_MESG(Fnd_Log.LEVEL_ERROR, l_module_name,'Error in seting the Concurent Program Status as Waring');
5190: error_code := SQLCODE ;
5191: error_buf := SQLERRM;
5192: END IF;
5193: END IF; /* l_alc_add_missing_count > 0 */
5190: error_code := SQLCODE ;
5191: error_buf := SQLERRM;
5192: END IF;
5193: END IF; /* l_alc_add_missing_count > 0 */
5194: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5195: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'End of check_alc_address ') ;
5196: END IF;
5197: EXCEPTION
5198: WHEN OTHERS THEN
5191: error_buf := SQLERRM;
5192: END IF;
5193: END IF; /* l_alc_add_missing_count > 0 */
5194: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5195: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'End of check_alc_address ') ;
5196: END IF;
5197: EXCEPTION
5198: WHEN OTHERS THEN
5199: error_code := SQLCODE;
5197: EXCEPTION
5198: WHEN OTHERS THEN
5199: error_code := SQLCODE;
5200: error_buf := SQLERRM;
5201: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception', error_buf) ;
5202: RAISE;
5203: END check_alc_address;
5204:
5205: ----------------------------------------------------------------------
5225: l_module_name := g_module_name || '.extract';
5226: p_retcode := g_SUCCESS;
5227: sob := p_set_of_books_id;
5228:
5229: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5230: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'In extrac procdure ');
5231: End if;
5232:
5233:
5226: p_retcode := g_SUCCESS;
5227: sob := p_set_of_books_id;
5228:
5229: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
5230: fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'In extrac procdure ');
5231: End if;
5232:
5233:
5234: check_report_definitions
5310: WHEN OTHERS THEN
5311: p_retcode := g_FAILURE;
5312: p_errbuf := SQLERRM;
5313: l_location := l_module_name||'.final_exception';
5314: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
5315: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
5316: END extract;
5317:
5318:
5311: p_retcode := g_FAILURE;
5312: p_errbuf := SQLERRM;
5313: l_location := l_module_name||'.final_exception';
5314: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
5315: fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
5316: END extract;
5317:
5318:
5319:
5336: l_po_dist_id ap_invoice_distributions_all.po_distribution_id%TYPE;
5337: l_parent_invoice_id ap_invoices_all.invoice_id%TYPE;
5338: BEGIN
5339: l_module_name := g_module_name || 'get_void_check_obligation_date';
5340: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5341: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Start of get_void_check_obligation_date') ;
5342: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, ' p_invoice_id => ' || p_invoice_id ||
5343: ' p_check_id => ' || p_check_id ||
5344: ' P_inv_dist_num => ' || P_inv_dist_num) ;
5337: l_parent_invoice_id ap_invoices_all.invoice_id%TYPE;
5338: BEGIN
5339: l_module_name := g_module_name || 'get_void_check_obligation_date';
5340: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5341: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Start of get_void_check_obligation_date') ;
5342: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, ' p_invoice_id => ' || p_invoice_id ||
5343: ' p_check_id => ' || p_check_id ||
5344: ' P_inv_dist_num => ' || P_inv_dist_num) ;
5345: END IF;
5338: BEGIN
5339: l_module_name := g_module_name || 'get_void_check_obligation_date';
5340: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5341: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Start of get_void_check_obligation_date') ;
5342: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, ' p_invoice_id => ' || p_invoice_id ||
5343: ' p_check_id => ' || p_check_id ||
5344: ' P_inv_dist_num => ' || P_inv_dist_num) ;
5345: END IF;
5346:
5351: FROM ap_invoice_distributions_all
5352: WHERE invoice_id = p_invoice_id ;
5353: --AND distribution_line_number = p_inv_dist_num;
5354: --------------
5355: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5356: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5357: ' l_po_dist_id => ' || l_po_dist_id || ' l_parent_invoice_id => ' || l_parent_invoice_id) ;
5358: END IF;
5359: IF l_po_dist_id <> 0 THEN
5352: WHERE invoice_id = p_invoice_id ;
5353: --AND distribution_line_number = p_inv_dist_num;
5354: --------------
5355: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5356: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5357: ' l_po_dist_id => ' || l_po_dist_id || ' l_parent_invoice_id => ' || l_parent_invoice_id) ;
5358: END IF;
5359: IF l_po_dist_id <> 0 THEN
5360: SELECT NVL(gl_encumbered_date,creation_date)
5360: SELECT NVL(gl_encumbered_date,creation_date)
5361: INTO l_obligation_date
5362: FROM po_distributions_all
5363: WHERE po_distribution_id =l_po_dist_id;
5364: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5365: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5366: 'Obligation Date is PO Date => ' || l_obligation_date);
5367: END IF;
5368: ELSIF l_parent_invoice_id <> 0 THEN
5361: INTO l_obligation_date
5362: FROM po_distributions_all
5363: WHERE po_distribution_id =l_po_dist_id;
5364: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5365: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5366: 'Obligation Date is PO Date => ' || l_obligation_date);
5367: END IF;
5368: ELSIF l_parent_invoice_id <> 0 THEN
5369: SELECT INVOICE_DATE
5369: SELECT INVOICE_DATE
5370: INTO l_obligation_date
5371: FROM ap_invoices_all
5372: WHERE invoice_id = l_parent_invoice_id;
5373: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5374: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5375: 'Obligation Date is Parent Invoice Date => ' || l_obligation_date);
5376: END IF;
5377: ELSE
5370: INTO l_obligation_date
5371: FROM ap_invoices_all
5372: WHERE invoice_id = l_parent_invoice_id;
5373: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5374: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5375: 'Obligation Date is Parent Invoice Date => ' || l_obligation_date);
5376: END IF;
5377: ELSE
5378: SELECT INVOICE_DATE
5378: SELECT INVOICE_DATE
5379: INTO l_obligation_date
5380: FROM ap_invoices_all
5381: WHERE invoice_id = p_invoice_id;
5382: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5383: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5384: 'Obligation Date is Invoice Date => ' || l_obligation_date);
5385: END IF;
5386: END IF;
5379: INTO l_obligation_date
5380: FROM ap_invoices_all
5381: WHERE invoice_id = p_invoice_id;
5382: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5383: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5384: 'Obligation Date is Invoice Date => ' || l_obligation_date);
5385: END IF;
5386: END IF;
5387:
5385: END IF;
5386: END IF;
5387:
5388:
5389: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5390: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5391: 'End of get_void_check_obligation_date l_obligation_date NEW CODE => ' || l_obligation_date) ;
5392: END IF;
5393: RETURN l_obligation_date;
5386: END IF;
5387:
5388:
5389: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5390: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5391: 'End of get_void_check_obligation_date l_obligation_date NEW CODE => ' || l_obligation_date) ;
5392: END IF;
5393: RETURN l_obligation_date;
5394: EXCEPTION
5392: END IF;
5393: RETURN l_obligation_date;
5394: EXCEPTION
5395: WHEN NO_DATA_FOUND THEN
5396: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5397: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'NO DATA FOUND EXCEPTION');
5398: END IF;
5399: RAISE;
5400: WHEN OTHERS THEN
5393: RETURN l_obligation_date;
5394: EXCEPTION
5395: WHEN NO_DATA_FOUND THEN
5396: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5397: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'NO DATA FOUND EXCEPTION');
5398: END IF;
5399: RAISE;
5400: WHEN OTHERS THEN
5401: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5397: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'NO DATA FOUND EXCEPTION');
5398: END IF;
5399: RAISE;
5400: WHEN OTHERS THEN
5401: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5402: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'WHEN OTHERS EXCEPTION');
5403: END IF;
5404: error_code := SQLCODE;
5405: error_buf := SQLERRM;
5398: END IF;
5399: RAISE;
5400: WHEN OTHERS THEN
5401: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5402: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'WHEN OTHERS EXCEPTION');
5403: END IF;
5404: error_code := SQLCODE;
5405: error_buf := SQLERRM;
5406: RAISE ;
5457: l_module_name := g_module_name || '.fv_ap_refund_populate';
5458:
5459: --Bug 12752106
5460:
5461: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5462: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Strat of the fv_ap_refund_populate Process');
5463: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_set_of_books_id : ' || p_set_of_books_id) ;
5464: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_org_id : ' || p_org_id ) ;
5465: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_low : ' || p_gl_period_low) ;
5458:
5459: --Bug 12752106
5460:
5461: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5462: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Strat of the fv_ap_refund_populate Process');
5463: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_set_of_books_id : ' || p_set_of_books_id) ;
5464: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_org_id : ' || p_org_id ) ;
5465: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_low : ' || p_gl_period_low) ;
5466: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_high : ' || p_gl_period_high) ;
5459: --Bug 12752106
5460:
5461: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5462: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Strat of the fv_ap_refund_populate Process');
5463: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_set_of_books_id : ' || p_set_of_books_id) ;
5464: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_org_id : ' || p_org_id ) ;
5465: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_low : ' || p_gl_period_low) ;
5466: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_high : ' || p_gl_period_high) ;
5467: END IF;
5460:
5461: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5462: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Strat of the fv_ap_refund_populate Process');
5463: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_set_of_books_id : ' || p_set_of_books_id) ;
5464: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_org_id : ' || p_org_id ) ;
5465: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_low : ' || p_gl_period_low) ;
5466: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_high : ' || p_gl_period_high) ;
5467: END IF;
5468: l_user_id := fnd_global.user_id;
5461: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5462: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Strat of the fv_ap_refund_populate Process');
5463: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_set_of_books_id : ' || p_set_of_books_id) ;
5464: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_org_id : ' || p_org_id ) ;
5465: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_low : ' || p_gl_period_low) ;
5466: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_high : ' || p_gl_period_high) ;
5467: END IF;
5468: l_user_id := fnd_global.user_id;
5469: l_conc_request_id := fnd_global.conc_request_id;
5462: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Strat of the fv_ap_refund_populate Process');
5463: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_set_of_books_id : ' || p_set_of_books_id) ;
5464: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_org_id : ' || p_org_id ) ;
5465: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_low : ' || p_gl_period_low) ;
5466: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'p_gl_period_high : ' || p_gl_period_high) ;
5467: END IF;
5468: l_user_id := fnd_global.user_id;
5469: l_conc_request_id := fnd_global.conc_request_id;
5470: l_set_of_books_id := p_set_of_books_id;
5470: l_set_of_books_id := p_set_of_books_id;
5471: --Bug 12752106
5472: --l_org_id := p_org_id;
5473: l_org_id := g_org_id;
5474: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_org_id : ' || l_org_id ) ;
5475: -- Get the start Date GL_PERIOD_LOW and End Date of GL_PERIOD_HIGH
5476: BEGIN
5477: SELECT start_date
5478: INTO l_gl_start_date
5479: FROM gl_period_statuses
5480: WHERE ledger_id = l_set_of_books_id
5481: AND application_id = 101
5482: AND period_name = p_gl_period_low;
5483: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5484: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Start Date for the period low is : ' ||
5485: l_gl_start_date ) ;
5486: END IF;
5487: EXCEPTION
5480: WHERE ledger_id = l_set_of_books_id
5481: AND application_id = 101
5482: AND period_name = p_gl_period_low;
5483: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5484: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Start Date for the period low is : ' ||
5485: l_gl_start_date ) ;
5486: END IF;
5487: EXCEPTION
5488: WHEN NO_DATA_FOUND THEN
5485: l_gl_start_date ) ;
5486: END IF;
5487: EXCEPTION
5488: WHEN NO_DATA_FOUND THEN
5489: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5490: 'Error in getting Start Date for the period Low ' ) ;
5491: RAISE;
5492: END;
5493: BEGIN
5496: FROM gl_period_statuses
5497: WHERE ledger_id = l_set_of_books_id
5498: AND application_id = 101
5499: AND period_name = p_gl_period_high;
5500: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5501: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'End Date for the period low is : ' ||
5502: l_gl_end_date ) ;
5503: END IF;
5504: EXCEPTION
5497: WHERE ledger_id = l_set_of_books_id
5498: AND application_id = 101
5499: AND period_name = p_gl_period_high;
5500: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5501: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'End Date for the period low is : ' ||
5502: l_gl_end_date ) ;
5503: END IF;
5504: EXCEPTION
5505: WHEN NO_DATA_FOUND THEN
5502: l_gl_end_date ) ;
5503: END IF;
5504: EXCEPTION
5505: WHEN NO_DATA_FOUND THEN
5506: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5507: 'Error in getting End Date for the Period High ' ) ;
5508: RAISE;
5509: END;
5510:
5662: AND apid.invoice_id = api.invoice_id
5663: )*/
5664:
5665: );
5666: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5667: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'No of Rows inserted into the FV table : '
5668: || SQL%ROWCOUNT ) ;
5669: END IF;
5670: COMMIT;
5663: )*/
5664:
5665: );
5666: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5667: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'No of Rows inserted into the FV table : '
5668: || SQL%ROWCOUNT ) ;
5669: END IF;
5670: COMMIT;
5671: -- Call the report to display the data populated by the process
5668: || SQL%ROWCOUNT ) ;
5669: END IF;
5670: COMMIT;
5671: -- Call the report to display the data populated by the process
5672: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5673: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5674: 'SUBMITTING FMS 224 Populate Payables Refunds Process Report .....') ;
5675: END IF;
5676: SELECT printer,
5669: END IF;
5670: COMMIT;
5671: -- Call the report to display the data populated by the process
5672: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5673: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5674: 'SUBMITTING FMS 224 Populate Payables Refunds Process Report .....') ;
5675: END IF;
5676: SELECT printer,
5677: number_of_copies
5687: l_org_id,p_gl_period_low,p_gl_period_high);
5688: IF l_report_conc_request_id = 0 THEN
5689: retcode := -1 ;
5690: errbuf := 'ERROR SUBMITTING FMS 224 Populate Payables Refunds Process Report. ';
5691: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
5692: 'ERROR SUBMITTING FMS 224 Populate Payables Refunds Process Report. ') ;
5693: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5694: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5695: 'Deleting all the Data from FV table inserted by the current process') ;
5689: retcode := -1 ;
5690: errbuf := 'ERROR SUBMITTING FMS 224 Populate Payables Refunds Process Report. ';
5691: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
5692: 'ERROR SUBMITTING FMS 224 Populate Payables Refunds Process Report. ') ;
5693: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5694: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5695: 'Deleting all the Data from FV table inserted by the current process') ;
5696: END IF;
5697: DELETE FROM fv_refunds_voids_all
5690: errbuf := 'ERROR SUBMITTING FMS 224 Populate Payables Refunds Process Report. ';
5691: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
5692: 'ERROR SUBMITTING FMS 224 Populate Payables Refunds Process Report. ') ;
5693: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5694: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5695: 'Deleting all the Data from FV table inserted by the current process') ;
5696: END IF;
5697: DELETE FROM fv_refunds_voids_all
5698: WHERE TYPE = 'PAYABLE_REFUND' AND conc_request_id = l_conc_request_id;
5696: END IF;
5697: DELETE FROM fv_refunds_voids_all
5698: WHERE TYPE = 'PAYABLE_REFUND' AND conc_request_id = l_conc_request_id;
5699: COMMIT;
5700: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5701: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5702: 'No of Rows Deleted from FV table : ' || SQL%ROWCOUNT ) ;
5703: END IF;
5704: END IF;
5697: DELETE FROM fv_refunds_voids_all
5698: WHERE TYPE = 'PAYABLE_REFUND' AND conc_request_id = l_conc_request_id;
5699: COMMIT;
5700: IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5701: FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
5702: 'No of Rows Deleted from FV table : ' || SQL%ROWCOUNT ) ;
5703: END IF;
5704: END IF;
5705: EXCEPTION
5703: END IF;
5704: END IF;
5705: EXCEPTION
5706: WHEN OTHERS THEN
5707: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','When Others Exception') ;
5708: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Error Code : ' ||
5709: SQLCODE) ;
5710: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Error Mesg : ' ||
5711: SQLERRM) ;
5704: END IF;
5705: EXCEPTION
5706: WHEN OTHERS THEN
5707: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','When Others Exception') ;
5708: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Error Code : ' ||
5709: SQLCODE) ;
5710: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Error Mesg : ' ||
5711: SQLERRM) ;
5712: errbuf := SQLERRM;
5706: WHEN OTHERS THEN
5707: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','When Others Exception') ;
5708: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Error Code : ' ||
5709: SQLCODE) ;
5710: FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Error Mesg : ' ||
5711: SQLERRM) ;
5712: errbuf := SQLERRM;
5713: retcode := SQLCODE;
5714: END fv_ap_refund_populate;