[Home] [Help]
542: FROM CE_SQLLDR_ERRORS
543: WHERE statement_number = G_statement_number
544: AND bank_account_num = G_bank_account_num;
545: BEGIN
546: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Hdr');
547: cep_standard.debug('Statement Number -'||G_statement_number||'.');
548: cep_standard.debug('Bank Account Num -'||G_bank_account_num||'.');
549: cep_standard.debug('Statement_date -'||G_statement_date||'.');
550: cep_standard.debug('Bank Name -'||G_bank_name||'.');
543: WHERE statement_number = G_statement_number
544: AND bank_account_num = G_bank_account_num;
545: BEGIN
546: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Hdr');
547: cep_standard.debug('Statement Number -'||G_statement_number||'.');
548: cep_standard.debug('Bank Account Num -'||G_bank_account_num||'.');
549: cep_standard.debug('Statement_date -'||G_statement_date||'.');
550: cep_standard.debug('Bank Name -'||G_bank_name||'.');
551: cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
544: AND bank_account_num = G_bank_account_num;
545: BEGIN
546: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Hdr');
547: cep_standard.debug('Statement Number -'||G_statement_number||'.');
548: cep_standard.debug('Bank Account Num -'||G_bank_account_num||'.');
549: cep_standard.debug('Statement_date -'||G_statement_date||'.');
550: cep_standard.debug('Bank Name -'||G_bank_name||'.');
551: cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
552: cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
545: BEGIN
546: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Hdr');
547: cep_standard.debug('Statement Number -'||G_statement_number||'.');
548: cep_standard.debug('Bank Account Num -'||G_bank_account_num||'.');
549: cep_standard.debug('Statement_date -'||G_statement_date||'.');
550: cep_standard.debug('Bank Name -'||G_bank_name||'.');
551: cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
552: cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
553: cep_standard.debug('End Bal -'||G_control_end_balance||'.');
546: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Hdr');
547: cep_standard.debug('Statement Number -'||G_statement_number||'.');
548: cep_standard.debug('Bank Account Num -'||G_bank_account_num||'.');
549: cep_standard.debug('Statement_date -'||G_statement_date||'.');
550: cep_standard.debug('Bank Name -'||G_bank_name||'.');
551: cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
552: cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
553: cep_standard.debug('End Bal -'||G_control_end_balance||'.');
554: cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
547: cep_standard.debug('Statement Number -'||G_statement_number||'.');
548: cep_standard.debug('Bank Account Num -'||G_bank_account_num||'.');
549: cep_standard.debug('Statement_date -'||G_statement_date||'.');
550: cep_standard.debug('Bank Name -'||G_bank_name||'.');
551: cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
552: cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
553: cep_standard.debug('End Bal -'||G_control_end_balance||'.');
554: cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
555: cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
548: cep_standard.debug('Bank Account Num -'||G_bank_account_num||'.');
549: cep_standard.debug('Statement_date -'||G_statement_date||'.');
550: cep_standard.debug('Bank Name -'||G_bank_name||'.');
551: cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
552: cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
553: cep_standard.debug('End Bal -'||G_control_end_balance||'.');
554: cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
555: cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
556: cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
549: cep_standard.debug('Statement_date -'||G_statement_date||'.');
550: cep_standard.debug('Bank Name -'||G_bank_name||'.');
551: cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
552: cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
553: cep_standard.debug('End Bal -'||G_control_end_balance||'.');
554: cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
555: cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
556: cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
557: cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
550: cep_standard.debug('Bank Name -'||G_bank_name||'.');
551: cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
552: cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
553: cep_standard.debug('End Bal -'||G_control_end_balance||'.');
554: cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
555: cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
556: cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
557: cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
558: cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
551: cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
552: cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
553: cep_standard.debug('End Bal -'||G_control_end_balance||'.');
554: cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
555: cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
556: cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
557: cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
558: cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
559: cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
552: cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
553: cep_standard.debug('End Bal -'||G_control_end_balance||'.');
554: cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
555: cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
556: cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
557: cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
558: cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
559: cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
560: cep_standard.debug('One Day Float -'||G_one_day_float||'.');
553: cep_standard.debug('End Bal -'||G_control_end_balance||'.');
554: cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
555: cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
556: cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
557: cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
558: cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
559: cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
560: cep_standard.debug('One Day Float -'||G_one_day_float||'.');
561: cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
554: cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
555: cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
556: cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
557: cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
558: cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
559: cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
560: cep_standard.debug('One Day Float -'||G_one_day_float||'.');
561: cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
562: cep_standard.debug('Total DR -'||G_control_total_dr||'.');
555: cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
556: cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
557: cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
558: cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
559: cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
560: cep_standard.debug('One Day Float -'||G_one_day_float||'.');
561: cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
562: cep_standard.debug('Total DR -'||G_control_total_dr||'.');
563: cep_standard.debug('Total CR -'||G_control_total_cr||'.');
556: cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
557: cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
558: cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
559: cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
560: cep_standard.debug('One Day Float -'||G_one_day_float||'.');
561: cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
562: cep_standard.debug('Total DR -'||G_control_total_dr||'.');
563: cep_standard.debug('Total CR -'||G_control_total_cr||'.');
564: cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
557: cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
558: cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
559: cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
560: cep_standard.debug('One Day Float -'||G_one_day_float||'.');
561: cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
562: cep_standard.debug('Total DR -'||G_control_total_dr||'.');
563: cep_standard.debug('Total CR -'||G_control_total_cr||'.');
564: cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
565: cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
558: cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
559: cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
560: cep_standard.debug('One Day Float -'||G_one_day_float||'.');
561: cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
562: cep_standard.debug('Total DR -'||G_control_total_dr||'.');
563: cep_standard.debug('Total CR -'||G_control_total_cr||'.');
564: cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
565: cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
566: cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
559: cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
560: cep_standard.debug('One Day Float -'||G_one_day_float||'.');
561: cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
562: cep_standard.debug('Total DR -'||G_control_total_dr||'.');
563: cep_standard.debug('Total CR -'||G_control_total_cr||'.');
564: cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
565: cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
566: cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
567: cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
560: cep_standard.debug('One Day Float -'||G_one_day_float||'.');
561: cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
562: cep_standard.debug('Total DR -'||G_control_total_dr||'.');
563: cep_standard.debug('Total CR -'||G_control_total_cr||'.');
564: cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
565: cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
566: cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
567: cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
568: cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
561: cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
562: cep_standard.debug('Total DR -'||G_control_total_dr||'.');
563: cep_standard.debug('Total CR -'||G_control_total_cr||'.');
564: cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
565: cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
566: cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
567: cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
568: cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
569: cep_standard.debug('GL Date -'||to_char(G_gl_date, 'DD-MON-YY')||'.');
562: cep_standard.debug('Total DR -'||G_control_total_dr||'.');
563: cep_standard.debug('Total CR -'||G_control_total_cr||'.');
564: cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
565: cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
566: cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
567: cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
568: cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
569: cep_standard.debug('GL Date -'||to_char(G_gl_date, 'DD-MON-YY')||'.');
570: cep_standard.debug('Org ID -'||G_org_id||'.');
563: cep_standard.debug('Total CR -'||G_control_total_cr||'.');
564: cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
565: cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
566: cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
567: cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
568: cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
569: cep_standard.debug('GL Date -'||to_char(G_gl_date, 'DD-MON-YY')||'.');
570: cep_standard.debug('Org ID -'||G_org_id||'.');
571: cep_standard.debug('Format Type -'||G_format_type||'.');
564: cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
565: cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
566: cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
567: cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
568: cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
569: cep_standard.debug('GL Date -'||to_char(G_gl_date, 'DD-MON-YY')||'.');
570: cep_standard.debug('Org ID -'||G_org_id||'.');
571: cep_standard.debug('Format Type -'||G_format_type||'.');
572:
565: cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
566: cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
567: cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
568: cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
569: cep_standard.debug('GL Date -'||to_char(G_gl_date, 'DD-MON-YY')||'.');
570: cep_standard.debug('Org ID -'||G_org_id||'.');
571: cep_standard.debug('Format Type -'||G_format_type||'.');
572:
573: IF G_format_type = 'SWIFT940'
566: cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
567: cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
568: cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
569: cep_standard.debug('GL Date -'||to_char(G_gl_date, 'DD-MON-YY')||'.');
570: cep_standard.debug('Org ID -'||G_org_id||'.');
571: cep_standard.debug('Format Type -'||G_format_type||'.');
572:
573: IF G_format_type = 'SWIFT940'
574: THEN
567: cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
568: cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
569: cep_standard.debug('GL Date -'||to_char(G_gl_date, 'DD-MON-YY')||'.');
570: cep_standard.debug('Org ID -'||G_org_id||'.');
571: cep_standard.debug('Format Type -'||G_format_type||'.');
572:
573: IF G_format_type = 'SWIFT940'
574: THEN
575: l_statement_number := G_statement_number || ' - ' || to_char(G_statement_date);
698: ELSIF G_process_option = 'IMPORT' THEN
699: l_program_name := 'ARPLABIM';
700: END IF;
701:
702: cep_standard.debug('Process Option -'||G_process_option);
703: cep_standard.debug('Program Name -'||l_program_name);
704:
705: l_req_data := fnd_conc_global.request_data;
706:
699: l_program_name := 'ARPLABIM';
700: END IF;
701:
702: cep_standard.debug('Process Option -'||G_process_option);
703: cep_standard.debug('Program Name -'||l_program_name);
704:
705: l_req_data := fnd_conc_global.request_data;
706:
707: if(l_req_data IS NOT NULL)THEN
741: '','','','','','','','','','',
742: '','','','','','','','','','',
743: '','','','','','','','','','');
744: IF l_request_id = 0 THEN
745: cep_standard.debug(FND_MESSAGE.get);
746: cep_standard.debug('EXCEPTION: Fail to submit cuncurrent request for '|| l_program_name);
747: RAISE ldr_exception;
748: END IF;
749: ELSE
742: '','','','','','','','','','',
743: '','','','','','','','','','');
744: IF l_request_id = 0 THEN
745: cep_standard.debug(FND_MESSAGE.get);
746: cep_standard.debug('EXCEPTION: Fail to submit cuncurrent request for '|| l_program_name);
747: RAISE ldr_exception;
748: END IF;
749: ELSE
750: FND_MESSAGE.set_name('CE', 'CE_ERROR_EXIST');
751: CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get, 'W');
752: END IF;
753: END IF;
754: END IF;
755: cep_standard.debug('<
757: WHEN ldr_exception THEN
758: RAISE;
759: WHEN OTHERS THEN
757: WHEN ldr_exception THEN
758: RAISE;
759: WHEN OTHERS THEN
760: l_err := SQLCODE;
761: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Insert_Hdr - '|| to_char(l_err));
762: RAISE;
763: END Insert_Hdr;
764:
765: /* ---------------------------------------------------------------------
924: l_return_tmp VARCHAR2(255);
925: l_concatenate_format_flag VARCHAR2(1);
926: BEGIN
927: IF l_DEBUG in ('Y', 'C') THEN
928: cep_standard.debug('>> Get_Formatted_String' );
929: cep_standard.debug('X_string = '|| X_string );
930: END IF;
931:
932:
925: l_concatenate_format_flag VARCHAR2(1);
926: BEGIN
927: IF l_DEBUG in ('Y', 'C') THEN
928: cep_standard.debug('>> Get_Formatted_String' );
929: cep_standard.debug('X_string = '|| X_string );
930: END IF;
931:
932:
933: l_tmp_format := LTRIM(RTRIM(G_predefined_format));
936: l_concatenate_format_flag := nvl(G_concatenate_format_flag, 'N');
937: --l_concatenate_format_flag := 'Y';
938:
939: IF l_DEBUG in ('Y', 'C') THEN
940: cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
941: cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
942: cep_standard.debug('l_pos = '||l_pos||'.');
943:
944: END IF;
937: --l_concatenate_format_flag := 'Y';
938:
939: IF l_DEBUG in ('Y', 'C') THEN
940: cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
941: cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
942: cep_standard.debug('l_pos = '||l_pos||'.');
943:
944: END IF;
945:
938:
939: IF l_DEBUG in ('Y', 'C') THEN
940: cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
941: cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
942: cep_standard.debug('l_pos = '||l_pos||'.');
943:
944: END IF;
945:
946:
947: WHILE l_pos <> 0 LOOP
948: l_format := LTRIM(RTRIM(SUBSTR(l_tmp_format, 1, l_pos-1)));
949:
950: -- DBMS_OUTPUT.put_line('l_format 1: '||l_format);
951: cep_standard.debug('l_format 1 = '||l_format||'.');
952:
953: IF (l_concatenate_format_flag = 'Y') THEN
954: IF l_return IS NOT NULL THEN
955: l_return := l_return || '/' || Find_Formatted_String(l_tmp_format, x_string);
954: IF l_return IS NOT NULL THEN
955: l_return := l_return || '/' || Find_Formatted_String(l_tmp_format, x_string);
956:
957: IF l_DEBUG in ('Y', 'C') THEN
958: cep_standard.debug('l_return not null = '||l_return||'.');
959: END IF;
960: ELSE
961: l_return := Find_Formatted_String(l_tmp_format, x_string);
962:
960: ELSE
961: l_return := Find_Formatted_String(l_tmp_format, x_string);
962:
963: IF l_DEBUG in ('Y', 'C') THEN
964: cep_standard.debug('l_return null = '||l_return||'.');
965: END IF;
966: END IF;
967: ELSE
968: l_return := Find_Formatted_String(l_format, X_string);
966: END IF;
967: ELSE
968: l_return := Find_Formatted_String(l_format, X_string);
969:
970: cep_standard.debug('concat = N - l_return = '||l_return||'.');
971:
972: IF l_return IS NOT NULL THEN
973: --l_return := Find_Formatted_String(l_format, X_string);
974: --RETURN l_return;
972: IF l_return IS NOT NULL THEN
973: --l_return := Find_Formatted_String(l_format, X_string);
974: --RETURN l_return;
975: IF l_DEBUG in ('Y', 'C') THEN
976: cep_standard.debug('concat = N - l_return not null = '||l_return||'.');
977: END IF;
978:
979: RETURN rtrim(l_return, '/');
980:
981: END IF;
982: END IF;
983:
984: IF l_return IS NOT NULL THEN
985: cep_standard.debug('l_return = '||l_return||'.');
986: END IF;
987:
988: l_tmp_format := SUBSTR(l_tmp_format, l_pos+1);
989: l_pos := INSTR(l_tmp_format, ',');
989: l_pos := INSTR(l_tmp_format, ',');
990: END LOOP;
991:
992: IF l_DEBUG in ('Y', 'C') THEN
993: cep_standard.debug('l_tmp_format 2 = '||l_tmp_format||'.');
994: END IF;
995:
996: IF (l_concatenate_format_flag = 'Y') THEN
997: IF l_return IS NOT NULL THEN
1005: END IF;
1006:
1007: IF l_DEBUG in ('Y', 'C') THEN
1008: l_return_tmp := rtrim(l_return, '/');
1009: cep_standard.debug('l_return_tmp = '||l_return_tmp||'.');
1010: END IF;
1011:
1012: RETURN rtrim(l_return, '/');
1013:
1034: l_row_id ROWID;
1035: l_statement_number VARCHAR2(50);
1036: BEGIN
1037: IF l_DEBUG in ('Y', 'C') THEN
1038: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Line');
1039:
1040: cep_standard.debug('Line_number -'||to_char(G_line_number)||'.');
1041: cep_standard.debug('Statement Number -'||G_statement_number||'.');
1042: cep_standard.debug('bank Account Num -'||G_bank_account_num||'.');
1036: BEGIN
1037: IF l_DEBUG in ('Y', 'C') THEN
1038: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Line');
1039:
1040: cep_standard.debug('Line_number -'||to_char(G_line_number)||'.');
1041: cep_standard.debug('Statement Number -'||G_statement_number||'.');
1042: cep_standard.debug('bank Account Num -'||G_bank_account_num||'.');
1043: cep_standard.debug('Trx_date -'||G_trx_date||'.');
1044: cep_standard.debug('Trx_code -'||G_trx_code||'.');
1037: IF l_DEBUG in ('Y', 'C') THEN
1038: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Line');
1039:
1040: cep_standard.debug('Line_number -'||to_char(G_line_number)||'.');
1041: cep_standard.debug('Statement Number -'||G_statement_number||'.');
1042: cep_standard.debug('bank Account Num -'||G_bank_account_num||'.');
1043: cep_standard.debug('Trx_date -'||G_trx_date||'.');
1044: cep_standard.debug('Trx_code -'||G_trx_code||'.');
1045: cep_standard.debug('Amount -'||to_char(G_amount)||'.');
1038: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Line');
1039:
1040: cep_standard.debug('Line_number -'||to_char(G_line_number)||'.');
1041: cep_standard.debug('Statement Number -'||G_statement_number||'.');
1042: cep_standard.debug('bank Account Num -'||G_bank_account_num||'.');
1043: cep_standard.debug('Trx_date -'||G_trx_date||'.');
1044: cep_standard.debug('Trx_code -'||G_trx_code||'.');
1045: cep_standard.debug('Amount -'||to_char(G_amount)||'.');
1046: cep_standard.debug('Trx Text -'||G_trx_text||'.');
1039:
1040: cep_standard.debug('Line_number -'||to_char(G_line_number)||'.');
1041: cep_standard.debug('Statement Number -'||G_statement_number||'.');
1042: cep_standard.debug('bank Account Num -'||G_bank_account_num||'.');
1043: cep_standard.debug('Trx_date -'||G_trx_date||'.');
1044: cep_standard.debug('Trx_code -'||G_trx_code||'.');
1045: cep_standard.debug('Amount -'||to_char(G_amount)||'.');
1046: cep_standard.debug('Trx Text -'||G_trx_text||'.');
1047: cep_standard.debug('Customer Text -'||G_customer_text||'.');
1040: cep_standard.debug('Line_number -'||to_char(G_line_number)||'.');
1041: cep_standard.debug('Statement Number -'||G_statement_number||'.');
1042: cep_standard.debug('bank Account Num -'||G_bank_account_num||'.');
1043: cep_standard.debug('Trx_date -'||G_trx_date||'.');
1044: cep_standard.debug('Trx_code -'||G_trx_code||'.');
1045: cep_standard.debug('Amount -'||to_char(G_amount)||'.');
1046: cep_standard.debug('Trx Text -'||G_trx_text||'.');
1047: cep_standard.debug('Customer Text -'||G_customer_text||'.');
1048: cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
1041: cep_standard.debug('Statement Number -'||G_statement_number||'.');
1042: cep_standard.debug('bank Account Num -'||G_bank_account_num||'.');
1043: cep_standard.debug('Trx_date -'||G_trx_date||'.');
1044: cep_standard.debug('Trx_code -'||G_trx_code||'.');
1045: cep_standard.debug('Amount -'||to_char(G_amount)||'.');
1046: cep_standard.debug('Trx Text -'||G_trx_text||'.');
1047: cep_standard.debug('Customer Text -'||G_customer_text||'.');
1048: cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
1049: cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
1042: cep_standard.debug('bank Account Num -'||G_bank_account_num||'.');
1043: cep_standard.debug('Trx_date -'||G_trx_date||'.');
1044: cep_standard.debug('Trx_code -'||G_trx_code||'.');
1045: cep_standard.debug('Amount -'||to_char(G_amount)||'.');
1046: cep_standard.debug('Trx Text -'||G_trx_text||'.');
1047: cep_standard.debug('Customer Text -'||G_customer_text||'.');
1048: cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
1049: cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
1050: cep_standard.debug('Effective Date -'||G_effective_date||'.');
1043: cep_standard.debug('Trx_date -'||G_trx_date||'.');
1044: cep_standard.debug('Trx_code -'||G_trx_code||'.');
1045: cep_standard.debug('Amount -'||to_char(G_amount)||'.');
1046: cep_standard.debug('Trx Text -'||G_trx_text||'.');
1047: cep_standard.debug('Customer Text -'||G_customer_text||'.');
1048: cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
1049: cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
1050: cep_standard.debug('Effective Date -'||G_effective_date||'.');
1051: cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
1044: cep_standard.debug('Trx_code -'||G_trx_code||'.');
1045: cep_standard.debug('Amount -'||to_char(G_amount)||'.');
1046: cep_standard.debug('Trx Text -'||G_trx_text||'.');
1047: cep_standard.debug('Customer Text -'||G_customer_text||'.');
1048: cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
1049: cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
1050: cep_standard.debug('Effective Date -'||G_effective_date||'.');
1051: cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
1052: cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
1045: cep_standard.debug('Amount -'||to_char(G_amount)||'.');
1046: cep_standard.debug('Trx Text -'||G_trx_text||'.');
1047: cep_standard.debug('Customer Text -'||G_customer_text||'.');
1048: cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
1049: cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
1050: cep_standard.debug('Effective Date -'||G_effective_date||'.');
1051: cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
1052: cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
1053: cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
1046: cep_standard.debug('Trx Text -'||G_trx_text||'.');
1047: cep_standard.debug('Customer Text -'||G_customer_text||'.');
1048: cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
1049: cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
1050: cep_standard.debug('Effective Date -'||G_effective_date||'.');
1051: cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
1052: cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
1053: cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
1054: cep_standard.debug('Created By -'||G_user_id||'.');
1047: cep_standard.debug('Customer Text -'||G_customer_text||'.');
1048: cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
1049: cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
1050: cep_standard.debug('Effective Date -'||G_effective_date||'.');
1051: cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
1052: cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
1053: cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
1054: cep_standard.debug('Created By -'||G_user_id||'.');
1055: cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
1048: cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
1049: cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
1050: cep_standard.debug('Effective Date -'||G_effective_date||'.');
1051: cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
1052: cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
1053: cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
1054: cep_standard.debug('Created By -'||G_user_id||'.');
1055: cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
1056: cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
1049: cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
1050: cep_standard.debug('Effective Date -'||G_effective_date||'.');
1051: cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
1052: cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
1053: cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
1054: cep_standard.debug('Created By -'||G_user_id||'.');
1055: cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
1056: cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
1057: cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
1050: cep_standard.debug('Effective Date -'||G_effective_date||'.');
1051: cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
1052: cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
1053: cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
1054: cep_standard.debug('Created By -'||G_user_id||'.');
1055: cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
1056: cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
1057: cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
1058: cep_standard.debug('Original_Amount -'||G_original_amount||'.');
1051: cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
1052: cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
1053: cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
1054: cep_standard.debug('Created By -'||G_user_id||'.');
1055: cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
1056: cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
1057: cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
1058: cep_standard.debug('Original_Amount -'||G_original_amount||'.');
1059: cep_standard.debug('Charges_Amount -'||G_charges_amount||'.');
1052: cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
1053: cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
1054: cep_standard.debug('Created By -'||G_user_id||'.');
1055: cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
1056: cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
1057: cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
1058: cep_standard.debug('Original_Amount -'||G_original_amount||'.');
1059: cep_standard.debug('Charges_Amount -'||G_charges_amount||'.');
1060:
1053: cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
1054: cep_standard.debug('Created By -'||G_user_id||'.');
1055: cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
1056: cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
1057: cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
1058: cep_standard.debug('Original_Amount -'||G_original_amount||'.');
1059: cep_standard.debug('Charges_Amount -'||G_charges_amount||'.');
1060:
1061: cep_standard.debug('Insert Line'||to_char(G_line_number));
1054: cep_standard.debug('Created By -'||G_user_id||'.');
1055: cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
1056: cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
1057: cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
1058: cep_standard.debug('Original_Amount -'||G_original_amount||'.');
1059: cep_standard.debug('Charges_Amount -'||G_charges_amount||'.');
1060:
1061: cep_standard.debug('Insert Line'||to_char(G_line_number));
1062: END IF;
1055: cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
1056: cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
1057: cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
1058: cep_standard.debug('Original_Amount -'||G_original_amount||'.');
1059: cep_standard.debug('Charges_Amount -'||G_charges_amount||'.');
1060:
1061: cep_standard.debug('Insert Line'||to_char(G_line_number));
1062: END IF;
1063:
1057: cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
1058: cep_standard.debug('Original_Amount -'||G_original_amount||'.');
1059: cep_standard.debug('Charges_Amount -'||G_charges_amount||'.');
1060:
1061: cep_standard.debug('Insert Line'||to_char(G_line_number));
1062: END IF;
1063:
1064: IF G_format_type = 'SWIFT940' THEN
1065: l_statement_number := G_statement_number || ' - ' || to_char(sysdate);
1120: NULL, NULL, NULL, NULL, NULL,
1121: NULL, NULL, NULL, NULL, NULL);
1122: END IF;
1123: IF l_DEBUG in ('Y', 'C') THEN
1124: cep_standard.debug('<
1126: EXCEPTION
1127: WHEN OTHERS THEN
1128: l_err := SQLCODE;
1126: EXCEPTION
1127: WHEN OTHERS THEN
1128: l_err := SQLCODE;
1129: IF l_DEBUG in ('Y', 'C') THEN
1130: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Insert_Line - '|| to_char(l_err));
1131: END IF;
1132: RAISE;
1133: END Insert_Line;
1134:
1338: return(l_result);
1339: EXCEPTION
1340: WHEN OTHERS THEN
1341: IF l_DEBUG in ('Y', 'C') THEN
1342: cep_standard.debug('EXCEPTION: Get_Value - X_pos '|| to_char(X_pos)||', X_rec_no '|| to_char(X_rec_no));
1343: END IF;
1344: RAISE;
1345: END Get_Value;
1346:
1396: X_position := 0;
1397: WHEN OTHERS THEN
1398: l_err := SQLCODE;
1399: IF l_DEBUG in ('Y', 'C') THEN
1400: cep_standard.debug('EXCEPTION: Lookup_Pos - '|| to_char(l_err));
1401: END IF;
1402: RAISE;
1403: END Lookup_Pos;
1404:
1539:
1540: l_cnt NUMBER;
1541:
1542: BEGIN
1543: cep_standard.debug('<
1545: cep_standard.debug('G_bank_account_num is null');
1546: FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISS_REQ_FIELD');
1547: FND_MESSAGE.set_token('FIELD', 'BANK_ACCOUNT_NUM');
1541:
1542: BEGIN
1543: cep_standard.debug('<
1545: cep_standard.debug('G_bank_account_num is null');
1546: FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISS_REQ_FIELD');
1547: FND_MESSAGE.set_token('FIELD', 'BANK_ACCOUNT_NUM');
1548: CE_SQLLDR_ERRORS_PKG.insert_row(
1549: NVL(G_statement_number, 'XXXXXXXXXXX'),
1551: G_rec_no, fnd_message.get, 'E');
1552: RETURN FALSE;
1553: END IF;
1554:
1555: cep_standard.debug('Finding unique bank account...');
1556: cep_standard.debug('acct_num='||G_bank_account_num
1557: ||',branch_id='||G_Bank_Branch_id
1558: ||',crncy='||G_hdr_currency_code
1559: ||',acct_id='||G_bank_account_id);
1552: RETURN FALSE;
1553: END IF;
1554:
1555: cep_standard.debug('Finding unique bank account...');
1556: cep_standard.debug('acct_num='||G_bank_account_num
1557: ||',branch_id='||G_Bank_Branch_id
1558: ||',crncy='||G_hdr_currency_code
1559: ||',acct_id='||G_bank_account_id);
1560:
1566: AND NVL(G_Bank_Branch_id,Bank_branch_id) = Bank_branch_id -- Bug 8209720
1567: AND NVL(G_bank_account_id, bank_account_id) = bank_account_id -- Bug 8209720
1568: AND NVL(G_hdr_currency_code,currency_code) = currency_code; -- Bug 13688013
1569:
1570: cep_standard.debug('l_cnt = '||to_char(l_cnt));
1571: IF l_cnt = 0 THEN -- Bank account is not setup.
1572: cep_standard.debug('Bank account is not setup');
1573: FND_MESSAGE.set_name('CE', 'CE_BANK_ACCNT_NOT_DEFINED');
1574: FND_MESSAGE.set_token('BANK_ACCNT',G_bank_account_num);
1568: AND NVL(G_hdr_currency_code,currency_code) = currency_code; -- Bug 13688013
1569:
1570: cep_standard.debug('l_cnt = '||to_char(l_cnt));
1571: IF l_cnt = 0 THEN -- Bank account is not setup.
1572: cep_standard.debug('Bank account is not setup');
1573: FND_MESSAGE.set_name('CE', 'CE_BANK_ACCNT_NOT_DEFINED');
1574: FND_MESSAGE.set_token('BANK_ACCNT',G_bank_account_num);
1575: CE_SQLLDR_ERRORS_PKG.insert_row(
1576: G_statement_number,
1578: G_rec_no, fnd_message.get, 'E');
1579: RETURN FALSE;
1580:
1581: ELSIF l_cnt > 1 THEN -- There are more than one bank with this account number.
1582: cep_standard.debug('There are too many matching bank accounts');
1583: FND_MESSAGE.set_name('CE', 'CE_TOO_MANY_BANK_ACCNT');
1584: FND_MESSAGE.set_token('BANK_ACCNT',G_bank_account_num);
1585: CE_SQLLDR_ERRORS_PKG.insert_row(
1586: G_statement_number,
1587: G_bank_account_num,
1588: G_rec_no, fnd_message.get, 'W');
1589:
1590: ELSE
1591: cep_standard.debug('G_bank_name='||G_bank_name);
1592: cep_standard.debug('G_bank_branch_name='||G_bank_branch_name);
1593: cep_standard.debug('G_hdr_currency_code='||G_hdr_currency_code);
1594: cep_standard.debug('G_sub_branch_id='||G_sub_branch_id);
1595: cep_standard.debug('G_sub_account_id='||G_sub_account_id);
1588: G_rec_no, fnd_message.get, 'W');
1589:
1590: ELSE
1591: cep_standard.debug('G_bank_name='||G_bank_name);
1592: cep_standard.debug('G_bank_branch_name='||G_bank_branch_name);
1593: cep_standard.debug('G_hdr_currency_code='||G_hdr_currency_code);
1594: cep_standard.debug('G_sub_branch_id='||G_sub_branch_id);
1595: cep_standard.debug('G_sub_account_id='||G_sub_account_id);
1596: -- 13688013: Always fetch account details for valid account
1589:
1590: ELSE
1591: cep_standard.debug('G_bank_name='||G_bank_name);
1592: cep_standard.debug('G_bank_branch_name='||G_bank_branch_name);
1593: cep_standard.debug('G_hdr_currency_code='||G_hdr_currency_code);
1594: cep_standard.debug('G_sub_branch_id='||G_sub_branch_id);
1595: cep_standard.debug('G_sub_account_id='||G_sub_account_id);
1596: -- 13688013: Always fetch account details for valid account
1597: --IF ( G_bank_name IS NULL OR G_bank_branch_name IS NULL OR G_hdr_currency_code IS NULL) THEN
1590: ELSE
1591: cep_standard.debug('G_bank_name='||G_bank_name);
1592: cep_standard.debug('G_bank_branch_name='||G_bank_branch_name);
1593: cep_standard.debug('G_hdr_currency_code='||G_hdr_currency_code);
1594: cep_standard.debug('G_sub_branch_id='||G_sub_branch_id);
1595: cep_standard.debug('G_sub_account_id='||G_sub_account_id);
1596: -- 13688013: Always fetch account details for valid account
1597: --IF ( G_bank_name IS NULL OR G_bank_branch_name IS NULL OR G_hdr_currency_code IS NULL) THEN
1598: OPEN C_bank;
1591: cep_standard.debug('G_bank_name='||G_bank_name);
1592: cep_standard.debug('G_bank_branch_name='||G_bank_branch_name);
1593: cep_standard.debug('G_hdr_currency_code='||G_hdr_currency_code);
1594: cep_standard.debug('G_sub_branch_id='||G_sub_branch_id);
1595: cep_standard.debug('G_sub_account_id='||G_sub_account_id);
1596: -- 13688013: Always fetch account details for valid account
1597: --IF ( G_bank_name IS NULL OR G_bank_branch_name IS NULL OR G_hdr_currency_code IS NULL) THEN
1598: OPEN C_bank;
1599: FETCH C_bank INTO G_bank_name, G_bank_branch_name, G_sub_branch_id,
1602: --END IF;
1603:
1604: END IF;
1605:
1606: cep_standard.debug('<
1608:
1609: END Valid_Bank_Account;
1610:
1636:
1637: l_str VARCHAR2(2000);
1638: BEGIN
1639: IF l_DEBUG in ('Y', 'C') THEN
1640: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Line_BAI');
1641: END IF;
1642:
1643: OPEN C_rec;
1644: FETCH C_rec
1671: G_original_amount := to_number(Lookup_Val('L', X_rec_no, 'ORIGINAL_AMOUNT', l_rec_len)) / G_line_precision;
1672: G_charges_amount := to_number(Lookup_Val('L', X_rec_no, 'CHARGES_AMOUNT', l_rec_len)) / G_line_precision;
1673:
1674: IF l_DEBUG in ('Y', 'C') THEN
1675: cep_standard.debug('<
1677: EXCEPTION
1678: WHEN OTHERS THEN
1679: l_err := SQLCODE;
1680: FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
1681: FND_MESSAGE.set_token('ERR', to_char(l_err));
1682: CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
1683: IF l_DEBUG in ('Y', 'C') THEN
1684: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Line_BAI - '|| to_char(l_err));
1685: END IF;
1686: END Decode_Line_BAI;
1687:
1688: /* ---------------------------------------------------------------------
1932:
1933: EXCEPTION
1934: WHEN OTHERS THEN
1935: IF l_DEBUG in ('Y', 'C') THEN
1936: cep_standard.debug('EXCEPTION: Find_Columns - X_cnt '|| to_char(X_cnt)||', X_rec_no '|| to_char(X_rec_no));
1937: END IF;
1938: RAISE;
1939: END Find_Columns;
1940:
1972: l_str VARCHAR2(2000):= null;
1973:
1974: BEGIN
1975: IF l_DEBUG in ('Y', 'C') THEN
1976: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Hdr_BAI');
1977: END IF;
1978:
1979: IF X_rec_id in ('3','03') THEN
1980: l_cnt := 3;
2164:
2165: END LOOP;
2166:
2167: IF l_DEBUG in ('Y', 'C') THEN
2168: cep_standard.debug('<
2170: EXCEPTION -- Bug 3608257 added this EXCEPTION section
2171: WHEN OTHERS THEN
2172: l_err := SQLCODE;
2172: l_err := SQLCODE;
2173: FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
2174: FND_MESSAGE.set_token('ERR', to_char(l_err));
2175: CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
2176: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Hdr_BAI - '|| to_char(l_err));
2177: END Decode_Hdr_BAI;
2178:
2179: /* ---------------------------------------------------------------------
2180: | PRIVATE FUNCTION |
2224: l_line_precision NUMBER; -- OXY ER
2225: l_exchange_rate VARCHAR2(250):=null; /* 13947921 */
2226: BEGIN
2227: IF l_DEBUG in ('Y', 'C') THEN
2228: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_BAI');
2229: END IF;
2230:
2231: FOR C_rec IN C_tmp_tbl LOOP
2232: G_rec_no := C_rec.rec_no;
2231: FOR C_rec IN C_tmp_tbl LOOP
2232: G_rec_no := C_rec.rec_no;
2233:
2234: IF l_DEBUG in ('Y', 'C') THEN
2235: cep_standard.debug('CE_BANK_STATEMENT_LOADER.Load_BAI - G_rec_no = '||G_rec_no );
2236: END IF;
2237:
2238: IF ( G_rec_no = 1 AND
2239: C_rec.rec_id_no <> '01' ) THEN
2252: END IF;
2253: ELSIF C_rec.rec_id_no = '02' THEN
2254:
2255: IF l_DEBUG in ('Y', 'C') THEN
2256: cep_standard.debug('>>C_rec.rec_id_no = 02');
2257: END IF;
2258:
2259: G_statement_number := lookup_val('H', C_rec.rec_no, 'STATEMENT_NUMBER');
2260:
2258:
2259: G_statement_number := lookup_val('H', C_rec.rec_no, 'STATEMENT_NUMBER');
2260:
2261: IF l_DEBUG in ('Y', 'C') THEN
2262: cep_standard.debug('G_statement_number = '|| G_statement_number);
2263: END IF;
2264:
2265: G_statement_date := CONV_TIMESTAMP(lookup_val('H', C_rec.rec_no, 'STATEMENT_DATE') || ' ' || lookup_val('H', C_rec.rec_no, 'STATEMENT_TIMESTAMP'));
2266:
2264:
2265: G_statement_date := CONV_TIMESTAMP(lookup_val('H', C_rec.rec_no, 'STATEMENT_DATE') || ' ' || lookup_val('H', C_rec.rec_no, 'STATEMENT_TIMESTAMP'));
2266:
2267: IF l_DEBUG in ('Y', 'C') THEN
2268: cep_standard.debug('G_statement_date = '|| G_statement_date);
2269: END IF;
2270:
2271: IF G_statement_number IS NULL THEN
2272: FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISS_REQ_FIELD');
2292: IF (G_gl_date_source = 'STATEMENT') THEN
2293: G_gl_date := trunc(G_statement_date);
2294: END IF;
2295: IF l_DEBUG in ('Y', 'C') THEN
2296: cep_standard.debug('<
2298:
2299: ELSIF C_rec.rec_id_no = '03' THEN
2300: l_rec_cnt := 1;
2299: ELSIF C_rec.rec_id_no = '03' THEN
2300: l_rec_cnt := 1;
2301:
2302: IF l_DEBUG in ('Y', 'C') THEN
2303: cep_standard.debug('>>C_rec.rec_id_no = 03');
2304: END IF;
2305:
2306: G_bank_account_num := lookup_val('H', C_rec.rec_no, 'BANK_ACCOUNT_NUM');
2307:
2305:
2306: G_bank_account_num := lookup_val('H', C_rec.rec_no, 'BANK_ACCOUNT_NUM');
2307:
2308: IF l_DEBUG in ('Y', 'C') THEN
2309: cep_standard.debug('G_bank_account_num: '||G_bank_account_num);
2310: END IF;
2311:
2312: -- Start OF OXY ER for header
2313:
2322:
2323: G_hdr_currency_code := nvl(RTrim(rtrim(lookup_val('H', C_rec.rec_no, 'CURRENCY_CODE')),'/'), l_currency_code);
2324:
2325: IF l_DEBUG in ('Y', 'C') THEN
2326: cep_standard.debug('G_hdr_currency_code: '||G_hdr_currency_code);
2327: END IF;
2328:
2329: --Get the precision from Bank Statement File
2330: l_hdr_precision := To_Number(RTrim(RTrim(Lookup_Val('H', C_rec.rec_no, 'PRECISION')),'/')) ;
2342: Nvl(G_statement_date,SYSDATE) <= Nvl(END_DATE_ACTIVE,nvl(G_statement_date,SYSDATE))
2343: );
2344:
2345: IF l_DEBUG in ('Y', 'C') THEN
2346: cep_standard.debug('G_hdr_precision from Fnd_Currencies: '||l_hdr_precision);
2347: END IF;
2348:
2349: G_hdr_precision := Get_Precision(l_hdr_precision);
2350: G_precision := G_hdr_precision;
2352: EXCEPTION
2353: WHEN No_Data_Found THEN
2354: G_hdr_precision := G_precision;
2355: IF l_DEBUG in ('Y', 'C') THEN
2356: cep_standard.debug('G_hdr_precision from Mappings Template: '||Log(10,G_hdr_precision));
2357: END IF;
2358: END;
2359: ELSE -- Precision as sent by the bank at header level
2360: G_hdr_precision := Get_Precision(l_hdr_precision);
2360: G_hdr_precision := Get_Precision(l_hdr_precision);
2361: G_precision := G_hdr_precision;
2362:
2363: IF l_DEBUG in ('Y', 'C') THEN
2364: cep_standard.debug('G_hdr_precision from Bank File: '||l_hdr_precision);
2365: END IF;
2366: END IF;
2367: -- END OF OXY ER for header
2368:
2392:
2393: l_last_rid := '3';
2394:
2395: IF l_DEBUG in ('Y', 'C') THEN
2396: cep_standard.debug('<
2398:
2399: ELSIF C_rec.rec_id_no = '16' THEN
2400: Init_Line_Rec;
2416: -- If there is no presion set at header level, the precision set at mappings will be considered
2417: G_line_precision := G_hdr_precision;
2418:
2419: IF l_DEBUG in ('Y', 'C') THEN
2420: cep_standard.debug('G_line_precision = G_hdr_precision: '||log(10,G_line_precision));
2421: END IF;
2422:
2423: ELSE
2424: -- Precision as sent by the bank at Line level
2424: -- Precision as sent by the bank at Line level
2425: G_line_precision := Get_Precision(l_line_precision);
2426:
2427: IF l_DEBUG in ('Y', 'C') THEN
2428: cep_standard.debug('G_line_precision from bank: '||l_line_precision);
2429: END IF;
2430:
2431: END IF;
2432: -- end of OXY ER for Line
2434: G_trx_code := lookup_val('L', C_rec.rec_no, 'TRX_CODE');
2435: G_amount := to_number(lookup_val('L', C_rec.rec_no, 'AMOUNT'))/G_line_precision;
2436:
2437: IF l_DEBUG in ('Y', 'C') THEN
2438: cep_standard.debug('Decode_Line_BAI(C_rec.rec_no) = ' );
2439: END IF;
2440:
2441: Decode_Line_BAI(C_rec.rec_no);
2442:
2523: G_include_indicator := C_rec1.include_format_ind;
2524: G_concatenate_format_flag := C_rec1.concatenate_format_flag;
2525:
2526: IF l_DEBUG in ('Y', 'C') THEN
2527: cep_standard.debug('C_rec.rec_id_no = 88 and l_last_rid = 16 ' );
2528: cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
2529: cep_standard.debug('G_include_indicator = '||G_include_indicator||'.');
2530: cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
2531: END IF;
2524: G_concatenate_format_flag := C_rec1.concatenate_format_flag;
2525:
2526: IF l_DEBUG in ('Y', 'C') THEN
2527: cep_standard.debug('C_rec.rec_id_no = 88 and l_last_rid = 16 ' );
2528: cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
2529: cep_standard.debug('G_include_indicator = '||G_include_indicator||'.');
2530: cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
2531: END IF;
2532: IF (C_rec1.column_name = 'BANK_TRX_NUMBER') THEN --Start 8273630
2525:
2526: IF l_DEBUG in ('Y', 'C') THEN
2527: cep_standard.debug('C_rec.rec_id_no = 88 and l_last_rid = 16 ' );
2528: cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
2529: cep_standard.debug('G_include_indicator = '||G_include_indicator||'.');
2530: cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
2531: END IF;
2532: IF (C_rec1.column_name = 'BANK_TRX_NUMBER') THEN --Start 8273630
2533: IF (G_predefined_format IS NOT NULL AND G_bank_trx_number IS NULL) THEN
2526: IF l_DEBUG in ('Y', 'C') THEN
2527: cep_standard.debug('C_rec.rec_id_no = 88 and l_last_rid = 16 ' );
2528: cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
2529: cep_standard.debug('G_include_indicator = '||G_include_indicator||'.');
2530: cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
2531: END IF;
2532: IF (C_rec1.column_name = 'BANK_TRX_NUMBER') THEN --Start 8273630
2533: IF (G_predefined_format IS NOT NULL AND G_bank_trx_number IS NULL) THEN
2534: G_bank_trx_number := Get_Formatted_String(ltrim(C_rec.column1)); --Bug 6448644
2544: EXCEPTION
2545: WHEN OTHERS THEN --no_data_found
2546: l_err := SQLCODE;
2547: IF l_DEBUG in ('Y', 'C') THEN
2548: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Load_BAI - '|| to_char(l_err) || ' Rec no - '||nvl(G_rec_no, 0)||'No description for the line');
2549: END IF;
2550: RAISE;
2551: END;
2552: END IF;--end of 8273630
2580: G_exchange_rate := To_Number(l_exchange_rate);
2581: ELSE
2582: G_exchange_rate := NULL;
2583: IF l_DEBUG in ('Y', 'C') THEN
2584: cep_standard.debug('The exchange rate for this line is not valid: '||l_exchange_rate);
2585: END IF ;
2586: END IF;
2587: /* End 13947921 */
2588:
2594: G_charges_amount := Get_Formatted_String(C_rec.column1)/G_line_precision; --bug 12671889
2595: END IF;
2596:
2597: IF l_DEBUG in ('Y', 'C') THEN
2598: cep_standard.debug('C_rec1.column_name = '||C_rec1.column_name||'.');
2599: cep_standard.debug('G_bank_trx_number = '||G_bank_trx_number||'.');
2600: cep_standard.debug('G_invoice_text = '||G_invoice_text ||'.');
2601: cep_standard.debug('G_customer_text = '||G_customer_text ||'.');
2602: cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2595: END IF;
2596:
2597: IF l_DEBUG in ('Y', 'C') THEN
2598: cep_standard.debug('C_rec1.column_name = '||C_rec1.column_name||'.');
2599: cep_standard.debug('G_bank_trx_number = '||G_bank_trx_number||'.');
2600: cep_standard.debug('G_invoice_text = '||G_invoice_text ||'.');
2601: cep_standard.debug('G_customer_text = '||G_customer_text ||'.');
2602: cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2603: cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2596:
2597: IF l_DEBUG in ('Y', 'C') THEN
2598: cep_standard.debug('C_rec1.column_name = '||C_rec1.column_name||'.');
2599: cep_standard.debug('G_bank_trx_number = '||G_bank_trx_number||'.');
2600: cep_standard.debug('G_invoice_text = '||G_invoice_text ||'.');
2601: cep_standard.debug('G_customer_text = '||G_customer_text ||'.');
2602: cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2603: cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2604: cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2597: IF l_DEBUG in ('Y', 'C') THEN
2598: cep_standard.debug('C_rec1.column_name = '||C_rec1.column_name||'.');
2599: cep_standard.debug('G_bank_trx_number = '||G_bank_trx_number||'.');
2600: cep_standard.debug('G_invoice_text = '||G_invoice_text ||'.');
2601: cep_standard.debug('G_customer_text = '||G_customer_text ||'.');
2602: cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2603: cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2604: cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2605: cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
2598: cep_standard.debug('C_rec1.column_name = '||C_rec1.column_name||'.');
2599: cep_standard.debug('G_bank_trx_number = '||G_bank_trx_number||'.');
2600: cep_standard.debug('G_invoice_text = '||G_invoice_text ||'.');
2601: cep_standard.debug('G_customer_text = '||G_customer_text ||'.');
2602: cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2603: cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2604: cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2605: cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
2606: cep_standard.debug('G_exchange_rate = '||G_exchange_rate ||'.');
2599: cep_standard.debug('G_bank_trx_number = '||G_bank_trx_number||'.');
2600: cep_standard.debug('G_invoice_text = '||G_invoice_text ||'.');
2601: cep_standard.debug('G_customer_text = '||G_customer_text ||'.');
2602: cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2603: cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2604: cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2605: cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
2606: cep_standard.debug('G_exchange_rate = '||G_exchange_rate ||'.');
2607: cep_standard.debug('G_original_amount = '||G_original_amount ||'.');
2600: cep_standard.debug('G_invoice_text = '||G_invoice_text ||'.');
2601: cep_standard.debug('G_customer_text = '||G_customer_text ||'.');
2602: cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2603: cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2604: cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2605: cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
2606: cep_standard.debug('G_exchange_rate = '||G_exchange_rate ||'.');
2607: cep_standard.debug('G_original_amount = '||G_original_amount ||'.');
2608: cep_standard.debug('G_charges_amount = '||G_charges_amount||'.');
2601: cep_standard.debug('G_customer_text = '||G_customer_text ||'.');
2602: cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2603: cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2604: cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2605: cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
2606: cep_standard.debug('G_exchange_rate = '||G_exchange_rate ||'.');
2607: cep_standard.debug('G_original_amount = '||G_original_amount ||'.');
2608: cep_standard.debug('G_charges_amount = '||G_charges_amount||'.');
2609: END IF;
2602: cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2603: cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2604: cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2605: cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
2606: cep_standard.debug('G_exchange_rate = '||G_exchange_rate ||'.');
2607: cep_standard.debug('G_original_amount = '||G_original_amount ||'.');
2608: cep_standard.debug('G_charges_amount = '||G_charges_amount||'.');
2609: END IF;
2610:
2603: cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2604: cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2605: cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
2606: cep_standard.debug('G_exchange_rate = '||G_exchange_rate ||'.');
2607: cep_standard.debug('G_original_amount = '||G_original_amount ||'.');
2608: cep_standard.debug('G_charges_amount = '||G_charges_amount||'.');
2609: END IF;
2610:
2611: UPDATE ce_statement_lines_interface
2604: cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2605: cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
2606: cep_standard.debug('G_exchange_rate = '||G_exchange_rate ||'.');
2607: cep_standard.debug('G_original_amount = '||G_original_amount ||'.');
2608: cep_standard.debug('G_charges_amount = '||G_charges_amount||'.');
2609: END IF;
2610:
2611: UPDATE ce_statement_lines_interface
2612: SET bank_trx_number = G_bank_trx_number,
2653: END IF;
2654:
2655: END LOOP;
2656: IF l_DEBUG in ('Y', 'C') THEN
2657: cep_standard.debug('<
2659: EXCEPTION
2660: WHEN OTHERS THEN
2661: l_err := SQLCODE;
2659: EXCEPTION
2660: WHEN OTHERS THEN
2661: l_err := SQLCODE;
2662: IF l_DEBUG in ('Y', 'C') THEN
2663: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Load_BAI - '|| to_char(l_err) ||
2664: ' Rec no - '|| nvl(G_rec_no, 0));
2665: END IF;
2666: RAISE;
2667: END Load_BAI2;
2686: X_column_name VARCHAR2)
2687: IS
2688: l_err NUMBER;
2689: BEGIN
2690: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Hdr_Other --- '
2691: ||'X_rec_no='||X_rec_no||',X_column_name='||X_column_name);
2692:
2693: IF X_column_name = 'STATEMENT_NUMBER'
2694: THEN
2716: G_bank_branch_name := Lookup_Val('H', X_rec_no, X_column_name);
2717: END IF;
2718: ELSIF X_column_name = 'CONTROL_BEGIN_BALANCE'
2719: THEN
2720: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2721: -- EDifact ER To Handle Non Numeric Values at Balances
2722: IF( G_format_type='EDIFACT_FR') THEN
2723: G_control_begin_balance := fnd_number.canonical_to_number(covert_amt_edifact(Lookup_Val('H', X_rec_no, X_column_name)))/ G_hdr_precision;
2724: ELSE
2723: G_control_begin_balance := fnd_number.canonical_to_number(covert_amt_edifact(Lookup_Val('H', X_rec_no, X_column_name)))/ G_hdr_precision;
2724: ELSE
2725: G_control_begin_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2726: END IF;
2727: cep_standard.debug('Decode_Hdr_Other: ' || 'G_control_begin_balance: ' || to_char(G_control_begin_balance));
2728:
2729: ELSIF X_column_name = 'CONTROL_END_BALANCE'
2730: THEN
2731: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2727: cep_standard.debug('Decode_Hdr_Other: ' || 'G_control_begin_balance: ' || to_char(G_control_begin_balance));
2728:
2729: ELSIF X_column_name = 'CONTROL_END_BALANCE'
2730: THEN
2731: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2732: -- EDifact ER To Handle Non Numeric Values at Balances
2733: IF( G_format_type='EDIFACT_FR') THEN
2734: G_control_end_balance := fnd_number.canonical_to_number(covert_amt_edifact(Lookup_Val('H', X_rec_no, X_column_name)))/ G_hdr_precision;
2735: ELSE
2734: G_control_end_balance := fnd_number.canonical_to_number(covert_amt_edifact(Lookup_Val('H', X_rec_no, X_column_name)))/ G_hdr_precision;
2735: ELSE
2736: G_control_end_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2737: END IF;
2738: cep_standard.debug('Decode_Hdr_Other: ' || 'G_control_end_balance: ' || to_char(G_control_end_balance));
2739: ELSIF X_column_name = 'AVAILABLE_BALANCE'
2740: THEN
2741: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2742: G_cashflow_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2737: END IF;
2738: cep_standard.debug('Decode_Hdr_Other: ' || 'G_control_end_balance: ' || to_char(G_control_end_balance));
2739: ELSIF X_column_name = 'AVAILABLE_BALANCE'
2740: THEN
2741: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2742: G_cashflow_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2743: ELSIF X_column_name = 'VALUE_DATED_BALANCE'
2744: THEN
2745: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2741: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2742: G_cashflow_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2743: ELSIF X_column_name = 'VALUE_DATED_BALANCE'
2744: THEN
2745: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2746: G_int_calc_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2747: ELSIF X_column_name = 'ONE_DAY_FLOAT'
2748: THEN
2749: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2745: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2746: G_int_calc_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2747: ELSIF X_column_name = 'ONE_DAY_FLOAT'
2748: THEN
2749: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2750: G_one_day_float := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2751: ELSIF X_column_name = 'TWO_DAY_FLOAT'
2752: THEN
2753: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2749: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2750: G_one_day_float := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2751: ELSIF X_column_name = 'TWO_DAY_FLOAT'
2752: THEN
2753: cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2754: G_two_day_float := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2755: ELSIF X_column_name = 'CONTROL_TOTAL_DR'
2756: THEN
2757: G_control_total_dr := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2768: THEN
2769: G_control_line_count := to_number(Lookup_Val('H', X_rec_no, X_column_name));
2770: ELSIF X_column_name = 'CURRENCY_CODE'
2771: THEN
2772: cep_standard.debug('Decode_Hdr_Other: Calling Lookup_Val');
2773: cep_standard.debug('X_column_name='||X_column_name||',X_rec_no='||X_rec_no);
2774: G_hdr_currency_code := Lookup_Val('H', X_rec_no, X_column_name);
2775: cep_standard.debug('G_HDR_CURRENCY_CODE='||G_hdr_currency_code);
2776: ELSIF X_column_name = 'CHECK_DIGITS'
2769: G_control_line_count := to_number(Lookup_Val('H', X_rec_no, X_column_name));
2770: ELSIF X_column_name = 'CURRENCY_CODE'
2771: THEN
2772: cep_standard.debug('Decode_Hdr_Other: Calling Lookup_Val');
2773: cep_standard.debug('X_column_name='||X_column_name||',X_rec_no='||X_rec_no);
2774: G_hdr_currency_code := Lookup_Val('H', X_rec_no, X_column_name);
2775: cep_standard.debug('G_HDR_CURRENCY_CODE='||G_hdr_currency_code);
2776: ELSIF X_column_name = 'CHECK_DIGITS'
2777: THEN
2771: THEN
2772: cep_standard.debug('Decode_Hdr_Other: Calling Lookup_Val');
2773: cep_standard.debug('X_column_name='||X_column_name||',X_rec_no='||X_rec_no);
2774: G_hdr_currency_code := Lookup_Val('H', X_rec_no, X_column_name);
2775: cep_standard.debug('G_HDR_CURRENCY_CODE='||G_hdr_currency_code);
2776: ELSIF X_column_name = 'CHECK_DIGITS'
2777: THEN
2778: G_check_digits := Lookup_Val('H', X_rec_no, X_column_name);
2779: END IF;
2780:
2781: -- Added for p2p
2782: IF G_gl_date_source = 'STATEMENT'
2783: THEN
2784: cep_standard.debug('G_gl_date ==> G_statement_date='||G_statement_date);
2785: G_gl_date := G_statement_date;
2786: END IF;
2787:
2788: cep_standard.debug('<
2784: cep_standard.debug('G_gl_date ==> G_statement_date='||G_statement_date);
2785: G_gl_date := G_statement_date;
2786: END IF;
2787:
2788: cep_standard.debug('<
2790: WHEN OTHERS THEN
2791: l_err := SQLCODE;
2792: FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
2791: l_err := SQLCODE;
2792: FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
2793: FND_MESSAGE.set_token('ERR', to_char(l_err));
2794: CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
2795: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Hdr_Other - '|| to_char(l_err));
2796: END Decode_Hdr_Other;
2797:
2798: /* ---------------------------------------------------------------------
2799: | PRIVATE FUNCTION |
2814: l_err NUMBER;
2815: REQ_FIELD_MISSING EXCEPTION;
2816: BEGIN
2817: IF l_DEBUG in ('Y', 'C') THEN
2818: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Line_Other');
2819: END IF;
2820:
2821: IF X_column_name = 'LINE_NUMBER' THEN
2822: G_line_number := NVL(to_number(Lookup_Val('L', X_rec_no, X_column_name)), G_line_number);
2835: ELSIF (G_format_type <> 'EDIFACT_FR' ) THEN
2836: G_invoice_text := Lookup_Val('L', X_rec_no, X_column_name);
2837: END IF;
2838: IF l_DEBUG in ('Y', 'C') THEN
2839: cep_standard.debug('Decode_Line_Other: ' || 'G_invoice_text: ' || to_char(G_invoice_text));
2840: END IF;
2841: ELSIF X_column_name = 'AMOUNT' THEN
2842: --Edifact ER To handle the Non Numeric characters at Edifact Line Amounts
2843: IF( G_format_type='EDIFACT_FR') THEN
2845: ELSE
2846: G_amount := fnd_number.canonical_to_number(Lookup_Val('L', X_rec_no, X_column_name))/ G_line_precision;
2847: END IF;
2848: IF l_DEBUG in ('Y', 'C') THEN
2849: cep_standard.debug('Decode_Line_Other: ' || 'G_amount: ' || to_char(G_amount));
2850: END IF;
2851: ELSIF X_column_name = 'CURRENCY_CODE' THEN
2852: G_line_currency_code := Lookup_Val('L', X_rec_no, X_column_name);
2853: ELSIF X_column_name = 'EXCHANGE_RATE' THEN
2868: ELSIF X_column_name = 'BANK_ACCOUNT_TEXT' THEN
2869: G_bank_account_text := Lookup_Val('L', X_rec_no, X_column_name);
2870: END IF;
2871: IF l_DEBUG in ('Y', 'C') THEN
2872: cep_standard.debug('<
2874: EXCEPTION
2875: WHEN OTHERS THEN
2876: l_err := SQLCODE;
2877: FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
2878: FND_MESSAGE.set_token('ERR', to_char(l_err));
2879: CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
2880: IF l_DEBUG in ('Y', 'C') THEN
2881: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Line_Other - '||to_char(l_err));
2882: END IF;
2883: END Decode_Line_Other;
2884:
2885: FUNCTION Get_Main_Rec_ID (X_hdr_or_line VARCHAR2) RETURN VARCHAR2 IS
2929: WHEN OTHERS THEN
2930: l_err := SQLCODE;
2931: IF X_hdr_or_Line = 'L' THEN
2932: IF l_DEBUG in ('Y', 'C') THEN
2933: cep_standard.debug('Get_Main_Rec_ID: ' || 'EXCEPTION: Fail finding the record id of major line which contains
2934: the main line information - '|| to_char(l_err) ||
2935: ' Rec no - '|| nvl(G_rec_no, 0));
2936: END IF;
2937: ELSE
2935: ' Rec no - '|| nvl(G_rec_no, 0));
2936: END IF;
2937: ELSE
2938: IF l_DEBUG in ('Y', 'C') THEN
2939: cep_standard.debug('Get_Main_Rec_ID: ' || 'EXCEPTION: Fail finding the record id of major line which contains
2940: the main header information - ' || to_char(l_err) ||
2941: ' Rec no - '|| nvl(G_rec_no, 0));
2942: END IF;
2943: END IF;
2988: l_crncy_cd_read BOOLEAN := FALSE; -- 13688013: Added
2989:
2990: INVALID_ACCOUNT EXCEPTION;
2991: BEGIN
2992: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_Others');
2993:
2994: l_line_rec_id := Get_Main_Rec_ID('L');
2995: l_hdr_rec_id := Get_Main_Rec_ID('H');
2996:
3004: G_rec_id := C_rec.rec_id_no;
3005: -- If the file not started with record id '01' discard the file processing
3006: IF (G_rec_no=1 AND G_rec_id <> '01' )
3007: THEN
3008: cep_standard.debug('Load_Others: The File Not started with 01 Record id');
3009: FND_MESSAGE.set_name('CE', 'CE_INVALID_EDIFACT');
3010: CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
3011: EXIT;
3012: -- If the file has orphan '05' discard the file processing
3011: EXIT;
3012: -- If the file has orphan '05' discard the file processing
3013: ELSIF ( G_rec_id = '05' AND G_prev_rec_id NOT IN ('05','04') )
3014: THEN
3015: cep_standard.debug('Load_Others: The File has 05 Record with out Parent 04 Record at Rec no: '||To_Char(G_rec_no));
3016: FND_MESSAGE.set_name('CE', 'CE_INVALID_EDIFACT');
3017: CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
3018: EXIT;
3019: END IF;
3053: G_hdr_precision := G_precision;
3054: ELSIF ( G_hdr_precision <> 1 AND G_precision = 1 )THEN
3055: G_precision := G_hdr_precision;
3056: END IF;
3057: cep_standard.debug('G_precision: ' || to_char(G_precision));
3058: cep_standard.debug('G_hdr_precision: ' || to_char(G_hdr_precision));
3059:
3060: FOR C_hdr IN C_hdr_tbl(C_rec.rec_id_no)
3061: LOOP
3054: ELSIF ( G_hdr_precision <> 1 AND G_precision = 1 )THEN
3055: G_precision := G_hdr_precision;
3056: END IF;
3057: cep_standard.debug('G_precision: ' || to_char(G_precision));
3058: cep_standard.debug('G_hdr_precision: ' || to_char(G_hdr_precision));
3059:
3060: FOR C_hdr IN C_hdr_tbl(C_rec.rec_id_no)
3061: LOOP
3062: Decode_Hdr_Other(C_rec.rec_no, C_hdr.column_name);
3123: l_line_num := l_line_num + 1;
3124: END IF;
3125: ELSIF l_hdr_or_line = 'B'
3126: THEN
3127: cep_standard.debug('This rec_id '|| C_rec.rec_id_no ||' is assigned for both header and line');
3128: FND_MESSAGE.set_name('CE', 'CE_RECID_IN_HDR_LINE');
3129: FND_MESSAGE.set_token('RECID', C_rec.rec_id_no);
3130: CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, G_rec_no, fnd_message.get, 'E');
3131: EXIT;
3132: END IF;
3133:
3134: IF (G_format_type='EDIFACT_FR')
3135: THEN --8835022 start
3136: cep_standard.debug('G_prev_rec_id: '|| G_prev_rec_id);
3137: G_prev_rec_id := C_rec.rec_id_no; --Edifact ER Previous Record Id
3138: END IF; --8835022 End
3139: END LOOP;
3140:
3153: IF (l_process_this_record) THEN
3154: Insert_Hdr;
3155: END IF;
3156: END IF;
3157: cep_standard.debug('<
3159: WHEN INVALID_ACCOUNT THEN
3160: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.INVALID_ACCOUNT');
3161:
3156: END IF;
3157: cep_standard.debug('<
3159: WHEN INVALID_ACCOUNT THEN
3160: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.INVALID_ACCOUNT');
3161:
3162: WHEN OTHERS THEN
3163: l_err := SQLCODE;
3164: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Load_Others - '
3160: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.INVALID_ACCOUNT');
3161:
3162: WHEN OTHERS THEN
3163: l_err := SQLCODE;
3164: cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Load_Others - '
3165: ||to_char(l_err)||
3166: 'Rec no - '|| nvl(G_rec_no, 0));
3167: RAISE;
3168: END Load_Others;
3202: l_pos1 NUMBER;
3203: l_pos2 NUMBER;
3204: l_pos3 NUMBER;
3205: BEGIN
3206: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Line_SWIFT');
3207: FOR C_rec IN C_tmp_tbl
3208: LOOP
3209: l_str := C_rec.column35;
3210:
3273: l_col7 := SUBSTR(l_str, 1, l_pos - 1);
3274: l_col8 := SUBSTR(l_str, l_pos + 2);
3275: END IF;
3276:
3277: cep_standard.debug('C_rec.rec_no='||C_rec.rec_no);
3278: cep_standard.debug('l_col2='||l_col2
3279: ||',l_col3='||l_col3
3280: ||',l_col4='||l_col4
3281: ||',l_col5='||l_col5
3274: l_col8 := SUBSTR(l_str, l_pos + 2);
3275: END IF;
3276:
3277: cep_standard.debug('C_rec.rec_no='||C_rec.rec_no);
3278: cep_standard.debug('l_col2='||l_col2
3279: ||',l_col3='||l_col3
3280: ||',l_col4='||l_col4
3281: ||',l_col5='||l_col5
3282: ||',l_col6='||l_col6
3323: l_rec_id VARCHAR2(30);
3324: l_rec_id_no NUMBER; --14578532
3325:
3326: BEGIN
3327: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Description');
3328: cep_standard.debug('Looping C_tmp_tbl...');
3329:
3330: FOR C_rec IN C_tmp_tbl
3331: LOOP
3324: l_rec_id_no NUMBER; --14578532
3325:
3326: BEGIN
3327: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Description');
3328: cep_standard.debug('Looping C_tmp_tbl...');
3329:
3330: FOR C_rec IN C_tmp_tbl
3331: LOOP
3332: cep_standard.debug('C_rec.rec_no='||C_rec.rec_no
3328: cep_standard.debug('Looping C_tmp_tbl...');
3329:
3330: FOR C_rec IN C_tmp_tbl
3331: LOOP
3332: cep_standard.debug('C_rec.rec_no='||C_rec.rec_no
3333: ||',C_rec.column1='||C_rec.column1);
3334:
3335: SELECT MAX(rec_no)
3336: INTO l_rec_no
3338: WHERE rec_no < C_rec.rec_no
3339: AND rec_id_no <> '9';
3340:
3341:
3342: cep_standard.debug('l_rec_no='||l_rec_no);
3343:
3344: /* Bug 4041064 added the following IF */
3345: IF l_rec_no IS NOT NULL THEN
3346: SELECT rec_id_no
3362: END IF;
3363: END IF; --4041064
3364: END LOOP;
3365:
3366: cep_standard.debug('Looping C_tmp_tbl2...');
3367: FOR C_rec IN C_tmp_tbl2
3368: LOOP
3369: cep_standard.debug('C_rec.rec_no='||C_rec.rec_no
3370: ||',C_rec.column1='||C_rec.column1);
3365:
3366: cep_standard.debug('Looping C_tmp_tbl2...');
3367: FOR C_rec IN C_tmp_tbl2
3368: LOOP
3369: cep_standard.debug('C_rec.rec_no='||C_rec.rec_no
3370: ||',C_rec.column1='||C_rec.column1);
3371:
3372: /* SELECT rec_id_no
3373: INTO l_rec_id
3381: CE_STMT_INT_TMP WHERE rec_id_no IN ( '61', '61A')
3382: AND rec_no < C_rec.rec_no);
3383:
3384:
3385: cep_standard.debug('l_rec_id='||l_rec_id||' l_rec_id_no:'||l_rec_id_no);
3386:
3387: IF l_rec_id = '61' THEN
3388: UPDATE CE_STMT_INT_TMP -- This 86 record comes after 61.
3389: SET rec_id_no = '61A' -- Marks it as 61A.
3399: WHERE rec_no = C_rec.rec_no;
3400: END IF;
3401: END LOOP;
3402:
3403: cep_standard.debug('<
3405:
3406: /* ---------------------------------------------------------------------
3407: | PROCEDURE |
3414: | 21-Feb-2012 vnetan Bug 13688013: Added debug statements |
3415: --------------------------------------------------------------------- */
3416: PROCEDURE Load_SWIFT940 IS
3417: BEGIN
3418: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_SWIFT940');
3419: Decode_Line_SWIFT;
3420: Decode_Description;
3421: Load_Others;
3422: cep_standard.debug('<
3418: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_SWIFT940');
3419: Decode_Line_SWIFT;
3420: Decode_Description;
3421: Load_Others;
3422: cep_standard.debug('<
3424:
3425: PROCEDURE Load_Bank_Statement(errbuf OUT NOCOPY VARCHAR2,
3426: retcode OUT NOCOPY NUMBER,
3483: G_gl_date := SYSDATE;
3484: END IF;
3485:
3486: IF l_DEBUG in ('Y', 'C') THEN
3487: cep_standard.enable_debug(G_debug_path,
3488: G_debug_file || '-LBS');
3489: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_Bank_Statement');
3490: END IF;
3491: --
3485:
3486: IF l_DEBUG in ('Y', 'C') THEN
3487: cep_standard.enable_debug(G_debug_path,
3488: G_debug_file || '-LBS');
3489: cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_Bank_Statement');
3490: END IF;
3491: --
3492: -- Wait until SQL*Loader program is completed.
3493: --
3551: --
3552: G_precision := Get_Precision(nvl(l_precision, 0));
3553:
3554: IF l_DEBUG in ('Y', 'C') THEN
3555: cep_standard.debug('Load_Bank_Statement: ' || 'Format Type:'||l_format_type);
3556: cep_standard.debug('>>G_format_type= '||G_format_type);
3557: END IF;
3558:
3559:
3552: G_precision := Get_Precision(nvl(l_precision, 0));
3553:
3554: IF l_DEBUG in ('Y', 'C') THEN
3555: cep_standard.debug('Load_Bank_Statement: ' || 'Format Type:'||l_format_type);
3556: cep_standard.debug('>>G_format_type= '||G_format_type);
3557: END IF;
3558:
3559:
3560: IF G_format_type = 'BAI2' THEN
3558:
3559:
3560: IF G_format_type = 'BAI2' THEN
3561: IF l_DEBUG in ('Y', 'C') THEN
3562: cep_standard.debug('>>call Load_BAI2');
3563: END IF;
3564: Load_BAI2;
3565: ELSIF G_format_type = 'SWIFT940' THEN
3566: IF l_DEBUG in ('Y', 'C') THEN
3563: END IF;
3564: Load_BAI2;
3565: ELSIF G_format_type = 'SWIFT940' THEN
3566: IF l_DEBUG in ('Y', 'C') THEN
3567: cep_standard.debug('>>call Load_SWIFT940');
3568: END IF;
3569: Load_SWIFT940;
3570: ELSE
3571: IF l_DEBUG in ('Y', 'C') THEN
3568: END IF;
3569: Load_SWIFT940;
3570: ELSE
3571: IF l_DEBUG in ('Y', 'C') THEN
3572: cep_standard.debug('>>call Load_Others');
3573: END IF;
3574: Load_Others;
3575: END IF;
3576: Delete_Orphaned_Lines;
3588:
3589: CE_BANK_STMT_SQL_LDR.Print_Report(G_map_id, G_data_file_name);
3590:
3591: IF l_DEBUG in ('Y', 'C') THEN
3592: cep_standard.debug('<
3594: IF G_display_debug = 'Y' THEN
3595: cep_standard.disable_debug(G_display_debug);
3596: END IF;
3591: IF l_DEBUG in ('Y', 'C') THEN
3592: cep_standard.debug('<
3594: IF G_display_debug = 'Y' THEN
3595: cep_standard.disable_debug(G_display_debug);
3596: END IF;
3597: EXCEPTION
3598: WHEN SQL_LOADER_ERROR THEN
3599: IF l_DEBUG in ('Y', 'C') THEN
3596: END IF;
3597: EXCEPTION
3598: WHEN SQL_LOADER_ERROR THEN
3599: IF l_DEBUG in ('Y', 'C') THEN
3600: cep_standard.debug('EXCEPTION: Load_Bank_Statement - Sql*Loader was not finished');
3601: END IF;
3602: FND_MESSAGE.set_name('CE', 'CE_SQLLOADER_FAIL');
3603: CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get, 'E');
3604: CE_BANK_STMT_SQL_LDR.Print_Report(G_map_id, G_data_file_name);
3609: CE_BANK_STMT_SQL_LDR.Print_Report(G_map_id, G_data_file_name);
3610: WHEN OTHERS THEN
3611: l_err := SQLCODE;
3612: IF l_DEBUG in ('Y', 'C') THEN
3613: cep_standard.debug('EXCEPTION: Load_Bank_Statement - '||to_char(l_err));
3614: END IF;
3615: RAISE;
3616: END Load_Bank_Statement;
3617: