Protected by Copyscape Web Copyright Protection Software

Search This Blog

Sunday, June 9, 2013

CE_BANK_STATEMENT_LOADER Sample Script

create or replace PACKAGE BODY CE_BANK_STATEMENT_LOADER AS

  l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
  --l_DEBUG varchar2(1) := 'Y';

 n number:=0;
 G_last_val2 varchar2(30);
 G_last_val3 varchar2(30);
 G_last_val4 varchar2(30);
 G_lcnt     number :=0;


  FUNCTION body_revision RETURN VARCHAR2 IS
  BEGIN

    RETURN '$Revision: 120.34.12010000.15 $';

  END body_revision;

  FUNCTION spec_revision RETURN VARCHAR2 IS
  BEGIN

    RETURN G_spec_revision;

  END spec_revision;


--
--  The following Procedures and Functions are shared by BAI2 format and Non-BAI2 format loading.
--
--   Init_Hdr_Rec Insert_Hdr Lookup_Pos
-- Init_Line_Rec Insert_Line Lookup_Val
--
--  The following Procedures and Functions are for BAI2 format only.
--
-- Load_BAI2 Decode_Line_BAI
--       Decode_Hdr_BAI
--
--  The following Procedures and Functions are for Non-BAI2 format only.
--
-- Load_Others Decode_Line_Other Hdr_Or_Line
-- Decode_Hdr_Other
--

    --Edifact ER start
  FUNCTION Is_numeric(str IN VARCHAR2)
  RETURN NUMBER IS
  l_number NUMBER(38);
  BEGIN
    l_number := to_number(str);
    RETURN 1;
  EXCEPTION WHEN Value_Error THEN
    RETURN 0;
  END Is_numeric;

 FUNCTION covert_amt_edifact(amount IN VARCHAR2)
  RETURN NUMBER IS
  l_number NUMBER;
  l_amount VARCHAR2(100);
  l_Lastchar VARCHAR2(1);
  BEGIN
      -- Check the Amount is numeric or not.
      l_number:=Is_numeric(amount);

      -- If the Amount is Non numeric
      IF (l_number=0) THEN
             -- Valiadte the Amount Provided at bank statement
             l_number:=Is_numeric(SubStr(amount,1,Length(amount)-1));
             IF (l_number=1) THEN
              -- Get the Last Char from the given string
                  l_Lastchar:=SubStr(amount,-1,Length(amount));
                  -- Get the Ascii value of Last Character
                  l_number := Ascii(l_Lastchar);
                      -- If the Last Character is in ('A','B','C','D','E','F','G','H','I') Decode
                      --the value correcpondigly to (1,  2,  3,  4,  5,  6,  7,  8,  9) and the amount shoule be positive
                      IF (l_number BETWEEN 65 AND 73) THEN
                          l_amount:=substr(amount,1,Length(amount)-1)||To_Char(l_number-64);
                          RETURN To_Number(l_amount);
                      -- If the Last Character is in ('J','K','L','M','N','O','P',Q','R') Decode
                      --the value correcpondigly to (1,  2,  3,  4,  5,  6,  7,  8,  9) and the amount should be negative
                      ELSIF(l_number BETWEEN 74 AND 82) THEN
                          l_amount:=substr(amount,1,Length(amount)-1)||To_Char(l_number-73);
                          RETURN (To_Number(l_amount)*-1);
                      -- If the Last Char is '{' then decode the char to '0' and amount to positive
                      ELSIF(l_number = 123) THEN
                          l_amount:=substr(amount,1,Length(amount)-1)||'0';
                          RETURN To_Number(l_amount);
                      -- If the Last Char is '}' then decode the char to '0' and amount to Negative
                      ELSIF(l_number = 125) THEN
                          l_amount:=substr(amount,1,Length(amount)-1)||'0';
                          RETURN (To_Number(l_amount)*-1);
                      ELSE --8911035
                        FND_MESSAGE.set_name('CE', 'CE_INVALID_EDIFACT_AMOUNT');
                        CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, G_rec_no, fnd_message.get, 'W');
                        RETURN NULL;
                      END IF;
             ELSE
              FND_MESSAGE.set_name('CE', 'CE_INVALID_EDIFACT_AMOUNT');
              CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, G_rec_no, fnd_message.get, 'W');
              RETURN NULL;
             END IF; --8911035
      -- If the amount is Numeric return the amount
      ELSIF(l_number=1) THEN
        RETURN amount;
      END IF;
   END;
 --Edifact ER end
PROCEDURE Init_Hdr_Rec IS
BEGIN
  n := 0; -- 8367682: Added
  G_bank_name := NULL;
  G_bank_branch_name := NULL;
  G_statement_date := NULL;
  G_control_begin_balance := NULL;
  G_control_end_balance := NULL;
  G_cashflow_balance := NULL;
  G_int_calc_balance := NULL;
  G_average_close_ledger_mtd :=NULL;
  G_average_close_ledger_ytd :=NULL;
  G_average_close_available_mtd :=NULL;
  G_average_close_available_ytd :=NULL;
  G_one_day_float := NULL;
  G_two_day_float := NULL;
  G_control_total_dr := NULL;
  G_control_total_cr := NULL;
  G_control_dr_line_count := NULL;
  G_control_cr_line_count := NULL;
  G_control_line_count := NULL;
  G_check_digits := NULL;
  G_hdr_currency_code := NULL;
  G_hdr_precision := NULL;
END Init_Hdr_Rec;

PROCEDURE Init_Line_Rec IS
BEGIN
  G_line_number := NULL;
  G_trx_date := NULL;
  G_trx_code := NULL;
  G_effective_date := NULL;
  G_trx_text := NULL;
  G_invoice_text := NULL;
  G_amount := NULL;
  G_line_currency_code := NULL;
  G_exchange_rate := NULL;
  G_CUSTOMER_TEXT := NULL;
  G_BANK_TRX_NUMBER := NULL;
  G_user_exchange_rate_type := NULL;
  G_exchange_rate_date := NULL;
  G_original_amount := NULL;
  G_charges_amount := NULL;
  G_bank_account_text := NULL;
  G_line_precision := NULL;
END Init_Line_Rec;

PROCEDURE Remove_Return_Char IS
BEGIN

  UPDATE CE_STMT_INT_TMP
  SET    REC_NO = rtrim(REC_NO, '
'||fnd_global.local_chr(13)),
         REC_ID_NO = rtrim(REC_ID_NO, '
'||fnd_global.local_chr(13)),
         COLUMN1 = rtrim(COLUMN1, '
'||fnd_global.local_chr(13)),
         COLUMN2 = rtrim(COLUMN2, '
'||fnd_global.local_chr(13)),
         COLUMN3 = rtrim(COLUMN3, '
'||fnd_global.local_chr(13)),
         COLUMN4 = rtrim(COLUMN4, '
'||fnd_global.local_chr(13)),
         COLUMN5 = rtrim(COLUMN5, '
'||fnd_global.local_chr(13)),
         COLUMN6 = rtrim(COLUMN6, '
'||fnd_global.local_chr(13)),
         COLUMN7 = rtrim(COLUMN7, '
'||fnd_global.local_chr(13)),
         COLUMN8 = rtrim(COLUMN8, '
'||fnd_global.local_chr(13)),
         COLUMN9 = rtrim(COLUMN9, '
'||fnd_global.local_chr(13)),
         COLUMN10 = rtrim(COLUMN10, '
'||fnd_global.local_chr(13)),
         COLUMN11 = rtrim(COLUMN11, '
'||fnd_global.local_chr(13)),
         COLUMN12 = rtrim(COLUMN12, '
'||fnd_global.local_chr(13)),
         COLUMN13 = rtrim(COLUMN13, '
'||fnd_global.local_chr(13)),
         COLUMN14 = rtrim(COLUMN14, '
'||fnd_global.local_chr(13)),
         COLUMN15 = rtrim(COLUMN15, '
'||fnd_global.local_chr(13)),
         COLUMN16 = rtrim(COLUMN16, '
'||fnd_global.local_chr(13)),
         COLUMN17 = rtrim(COLUMN17, '
'||fnd_global.local_chr(13)),
         COLUMN18 = rtrim(COLUMN18, '
'||fnd_global.local_chr(13)),
         COLUMN19 = rtrim(COLUMN19, '
'||fnd_global.local_chr(13)),
         COLUMN20 = rtrim(COLUMN20, '
'||fnd_global.local_chr(13)),
         COLUMN21 = rtrim(COLUMN21, '
'||fnd_global.local_chr(13)),
         COLUMN22 = rtrim(COLUMN22, '
'||fnd_global.local_chr(13)),
         COLUMN23 = rtrim(COLUMN23, '
'||fnd_global.local_chr(13)),
         COLUMN24 = rtrim(COLUMN24, '
'||fnd_global.local_chr(13)),
         COLUMN25 = rtrim(COLUMN25, '
'||fnd_global.local_chr(13)),
         COLUMN26 = rtrim(COLUMN26, '
'||fnd_global.local_chr(13)),
         COLUMN27 = rtrim(COLUMN27, '
'||fnd_global.local_chr(13)),
         COLUMN28 = rtrim(COLUMN28, '
'||fnd_global.local_chr(13)),
         COLUMN29 = rtrim(COLUMN29, '
'||fnd_global.local_chr(13)),
         COLUMN30 = rtrim(COLUMN30, '
'||fnd_global.local_chr(13)),
         COLUMN31 = rtrim(COLUMN31, '
'||fnd_global.local_chr(13)),
         COLUMN32 = rtrim(COLUMN32, '
'||fnd_global.local_chr(13)),
         COLUMN33 = rtrim(COLUMN33, '
'||fnd_global.local_chr(13)),
         COLUMN34 = rtrim(COLUMN34, '
'||fnd_global.local_chr(13)),
         COLUMN35 = rtrim(COLUMN35, '
'||fnd_global.local_chr(13));
END Remove_Return_Char;


FUNCTION CONV_TO_DATE(X_date VARCHAR2)  RETURN DATE IS
BEGIN
  RETURN TO_DATE(X_date, G_date_format);
EXCEPTION
  WHEN OTHERS THEN
    FND_MESSAGE.set_name('CE', 'CE_CANNOT_CONVERT_DATE');
    FND_MESSAGE.set_token('DATE', X_date);
    FND_MESSAGE.set_token('FORMAT', G_date_format);
    CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(G_rec_no, 0), fnd_message.get, 'E');
END;


FUNCTION CONV_TIMESTAMP(X_date VARCHAR2)  RETURN DATE IS
BEGIN
  RETURN TO_DATE(X_date, G_date_format || ' ' || G_timestamp_format);
EXCEPTION
  WHEN OTHERS THEN
    FND_MESSAGE.set_name('CE', 'CE_CANNOT_CONVERT_DATE');
    FND_MESSAGE.set_token('DATE', X_date);
    FND_MESSAGE.set_token('FORMAT', G_date_format);
    CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(G_rec_no, 0), fnd_message.get, 'E');
END;


FUNCTION Is_Number (X_string VARCHAR2)  RETURN BOOLEAN IS
  l_dummy NUMBER;
BEGIN
  l_dummy := to_number(X_string);
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END;


FUNCTION Valid_Statement RETURN BOOLEAN IS
  l_cnt NUMBER := 1;
BEGIN
  --
  -- Check if this statement is the one user wants to import/autoreconcile.
  --
  IF G_bank_account_id IS NOT NULL THEN
    SELECT count(*)
    INTO   l_cnt
    FROM   CE_BANK_ACCOUNTS --FROM   AP_BANK_ACCOUNTS_ALL
    WHERE  bank_account_id  = G_bank_account_id
    AND    bank_account_num = G_bank_account_num;
  ELSIF G_bank_branch_id IS NOT NULL THEN
    SELECT count(*)
    INTO   l_cnt
    FROM   CE_BANK_BRANCHES_V --FROM   AP_BANK_BRANCHES
    WHERE  branch_party_id   = G_bank_branch_id
    AND    bank_branch_name = G_bank_branch_name;
  END IF;

  IF l_cnt = 0 THEN
    RETURN FALSE;
  ELSE
    RETURN TRUE;
  END IF;
END Valid_Statement;


PROCEDURE Delete_Orphaned_Lines IS
  l_bank_account_num  ce_statement_lines_interface.bank_account_num%TYPE;
  l_statement_number ce_statement_lines_interface.statement_number%TYPE;
  h_rec_cnt NUMBER;

  CURSOR l_cursor IS
    SELECT bank_account_num, statement_number
    FROM ce_statement_lines_interface;

  BEGIN
    OPEN l_cursor;
      LOOP
        fetch l_cursor into l_bank_account_num, l_statement_number;
        EXIT WHEN l_cursor%NOTFOUND;

         SELECT count (1)
         INTO h_rec_cnt
         FROM ce_statement_headers_int
         WHERE bank_account_num = l_bank_account_num
         AND statement_number = l_statement_number;

         IF h_rec_cnt = 0 THEN
         DELETE FROM ce_statement_lines_interface
         WHERE bank_account_num = l_bank_account_num
         AND statement_number = l_statement_number;

         END IF;
      END LOOP;
    CLOSE l_cursor;
END Delete_Orphaned_Lines;

PROCEDURE Validate_Subsidiary_Account IS
  l_rec_cnt NUMBER;

BEGIN
SELECT count(1)
INTO l_rec_cnt
FROM CE_BANK_ACCOUNTS BA,
    CE_BANK_BRANCHES_V BB
WHERE BA.ACCOUNT_CLASSIFICATION = 'SUBSIDIARY' AND
     BB.branch_party_id   = BA.bank_branch_id AND
              BA.bank_account_num = G_bank_account_num AND
     BB.BANK_BRANCH_NAME = nvl(G_bank_branch_name,BB.BANK_BRANCH_NAME);

if l_rec_cnt = 1 THEN
G_subsidiary_flag := 'Y';
else
G_subsidiary_flag := 'N';
end if;

EXCEPTION
WHEN OTHERS THEN
G_subsidiary_flag := 'N';
END Validate_Subsidiary_Account;

PROCEDURE Insert_Hdr IS
  l_rec_cnt NUMBER;
  l_err NUMBER;
  l_request_id NUMBER;
  l_program_name VARCHAR2(30);
  l_debug_file VARCHAR2(30);
  l_statement_number    VARCHAR2(50);
  G_conc_req_id NUMBER;
  l_req_data VARCHAR(30);
  ldr_exception EXCEPTION;

  errbuf VARCHAR2(256);
  retcode NUMBER;

  CURSOR C_error IS
    SELECT count(*)
    FROM   CE_SQLLDR_ERRORS
    WHERE  statement_number = G_statement_number
    AND    bank_account_num = G_bank_account_num;
BEGIN
  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Hdr');

    cep_standard.debug('Statement Number -'||G_statement_number||'.');
    cep_standard.debug('Bank Account Num -'||G_bank_account_num||'.');
    cep_standard.debug('Statement_date   -'||G_statement_date||'.');
    cep_standard.debug('Bank Name -'||G_bank_name||'.');
    cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
    cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
    cep_standard.debug('End Bal -'||G_control_end_balance||'.');
    cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
    cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
    cep_standard.debug('Average close ledger mtd   -'||G_average_close_ledger_mtd||'.');
    cep_standard.debug('Average close ledger ytd   -'||G_average_close_ledger_ytd||'.');
    cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
    cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
    cep_standard.debug('One Day Float -'||G_one_day_float||'.');
    cep_standard.debug('Two Day Float   -'||G_two_day_float||'.');
    cep_standard.debug('Total DR -'||G_control_total_dr||'.');
    cep_standard.debug('Total CR -'||G_control_total_cr||'.');
    cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
    cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
    cep_standard.debug('Currency Code   -'||G_hdr_currency_code||'.');
    cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
    cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
    cep_standard.debug('GL Date           -'||to_char(G_gl_date, 'DD-MON-YY')||'.');
    cep_standard.debug('Org ID        -'||G_org_id||'.');
    cep_standard.debug('Format Type -'||G_format_type||'.');
  END IF;

  IF G_format_type = 'SWIFT940' THEN
    l_statement_number := G_statement_number || ' - ' || to_char(G_statement_date);

  /* Bug 3417789 added the IF condition - start code fix*/
  IF (trunc(sysdate) <> trunc(G_statement_date)) THEN
    SELECT count(1)
    INTO   G_total_line_deleted
    FROM   ce_statement_lines_interface
    WHERE  statement_number = l_statement_number
    AND    bank_account_num = G_bank_account_num;

    DELETE ce_statement_lines_interface
    WHERE  statement_number = l_statement_number
    AND    bank_account_num = G_bank_account_num;

    UPDATE ce_statement_lines_interface
    SET    statement_number = l_statement_number
    WHERE  statement_number = G_statement_number || ' - ' || to_char(sysdate);
  END IF;
  /* Bug 3417789 - End code fix */
  ELSE
    l_statement_number := G_statement_number;
  END IF;

  IF Valid_statement THEN
    --
    -- Overwrite the existing bank statement of the same bank account and statement number.
    --
    -- bug 3676745 MO and BA uptake
   /*
    IF G_org_id is not null THEN
      SELECT count(1)
      INTO   l_rec_cnt
      FROM   ce_statement_headers_int
      WHERE  bank_account_num = G_bank_account_num
      AND statement_number = l_statement_number
      AND  nvl(org_id,G_org_id) = G_org_id;
    ELSE*/
      SELECT count(1)
      INTO   l_rec_cnt
      FROM   ce_statement_headers_int
      WHERE  bank_account_num = G_bank_account_num
      AND statement_number = l_statement_number;
    --END IF;
    IF l_rec_cnt > 0 THEN
      /*IF G_org_id is not null THEN
        DELETE FROM ce_statement_headers_int
        WHERE  bank_account_num = G_bank_account_num
        AND statement_number = l_statement_number
        AND  nvl(org_id,G_org_id) = G_org_id;
      ELSE*/
        DELETE FROM ce_statement_headers_int
        WHERE  bank_account_num = G_bank_account_num
        AND statement_number = l_statement_number;
      --END IF;
      G_total_hdr_deleted := G_total_hdr_deleted + l_rec_cnt;
    END IF;

    -- bug 4337623 added for subsidiary bank accounts
validate_subsidiary_account();

    INSERT INTO ce_statement_headers_int(
    STATEMENT_NUMBER,
  BANK_ACCOUNT_NUM,
  STATEMENT_DATE,
  BANK_NAME,
  BANK_BRANCH_NAME,
  CONTROL_BEGIN_BALANCE,
  CONTROL_END_BALANCE,
  CASHFLOW_BALANCE,
  INT_CALC_BALANCE,
AVERAGE_CLOSE_LEDGER_MTD,
AVERAGE_CLOSE_LEDGER_YTD,
AVERAGE_CLOSE_AVAILABLE_MTD,
AVERAGE_CLOSE_AVAILABLE_YTD,
ONE_DAY_FLOAT,
TWO_DAY_FLOAT,
  CONTROL_TOTAL_DR,
  CONTROL_TOTAL_CR,
  CONTROL_DR_LINE_COUNT,
  CONTROL_CR_LINE_COUNT,
  CONTROL_LINE_COUNT,
CHECK_DIGITS,
  RECORD_STATUS_FLAG,
  CURRENCY_CODE,
  CREATED_BY,
  CREATION_DATE ,
LAST_UPDATED_BY,
  LAST_UPDATE_DATE,
  --ORG_ID,
INTRA_DAY_FLAG,
SUBSIDIARY_FLAG)
    VALUES(
  rtrim(l_statement_number),
  rtrim(G_bank_account_num),
  G_statement_date,
  rtrim(G_bank_name),
  rtrim(G_bank_branch_name),
  G_control_begin_balance,
  G_control_end_balance,
  G_cashflow_balance,
  G_int_calc_balance,
G_average_close_ledger_mtd,
G_average_close_ledger_ytd,
G_average_close_available_mtd,
G_average_close_available_ytd,
G_one_day_float,
G_two_day_float,
  G_control_total_dr,
  G_control_total_cr,
  G_control_dr_line_count,
  G_control_cr_line_count,
G_control_line_count,
        G_check_digits,
      'N',
  rtrim(rtrim(G_hdr_currency_code),'/'),
G_user_id,
  SYSDATE,
  G_user_id,
SYSDATE,
--G_org_id,
G_intra_day_flag,
G_subsidiary_flag);

    --
    -- Submit concurrent program for import and auto-reconciliation process.
    --
    IF G_process_option <> 'LOAD' THEN
      --
      -- The program will be submitted only if there is no error.
      --
     /* 3019931
        Import should be done only for those records in the
        statement interface that belong to the current Org */
     --IF (FND_PROFILE.value('ORG_ID') = G_org_id) THEN
      OPEN  C_error;
      FETCH C_error INTO l_rec_cnt;
      CLOSE C_error;

      IF l_rec_cnt = 0 THEN
         IF G_process_option = 'ZALL' THEN
           l_program_name := 'ARPLABIR';
         ELSIF G_process_option = 'IMPORT' THEN
           l_program_name := 'ARPLABIM';
         END IF;

         cep_standard.debug('Process Option -'||G_process_option);
         cep_standard.debug('Program Name -'||l_program_name);

         l_req_data := fnd_conc_global.request_data;

         if(l_req_data IS NOT NULL)THEN
           G_conc_req_id := to_number(l_req_data);
         END IF;

         IF G_debug_file IS NOT NULL THEN
           l_debug_file := G_debug_file || '-REC';
         END IF;
-- pass both org_id and legal_entity_id to CE_AUTO_BANK_REC for Import/AutoRecon
         l_request_id := FND_REQUEST.SUBMIT_REQUEST(
'CE',l_program_name,'','',NULL,
G_process_option,
to_char(G_sub_branch_id),
to_char(G_sub_account_id),
l_statement_number,
l_statement_number,
'',
'',
to_char(G_gl_date, 'YYYY/MM/DD HH24:MI:SS'),
G_org_id,
'',
to_char(G_receivables_trx_id),
to_char(G_payment_method_id),
G_nsf_handling,
G_display_debug,
G_debug_path,
l_debug_file,
G_intra_day_flag,
fnd_global.local_chr(0),
'','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','');
        IF l_request_id = 0 THEN
          IF l_DEBUG in ('Y', 'C') THEN
     cep_standard.debug(FND_MESSAGE.get);
            cep_standard.debug('EXCEPTION: Fail to submit cuncurrent request for '|| l_program_name);
 END IF;
          RAISE ldr_exception;
        END IF;
      ELSE
        FND_MESSAGE.set_name('CE', 'CE_ERROR_EXIST');
        CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get, 'W');
      END IF;

    -- END IF; --org_id
    END IF;
  END IF;
  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Insert_Hdr');
  END IF;
EXCEPTION
  WHEN ldr_exception THEN
    RAISE;
  WHEN OTHERS THEN
    l_err := SQLCODE;
    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Insert_Hdr - '|| to_char(l_err));
    END IF;
    RAISE;
END Insert_Hdr;


FUNCTION Get_Precision(X_precision NUMBER) RETURN NUMBER IS
  l_precision NUMBER;
BEGIN
  IF nvl(X_precision, 0) = 0 THEN
    l_precision := 1;
  ELSIF X_precision = 1 THEN
    l_precision := 10;
  ELSIF X_precision = 2 THEN
    l_precision := 100;
  ELSIF X_precision = 3 THEN
    l_precision := 1000;
  ELSIF X_precision = 4 THEN
    l_precision := 10000;
  ELSIF X_precision = 5 THEN
    l_precision := 100000;
  END IF;

  RETURN l_precision;
END Get_Precision;

FUNCTION Find_Formatted_String(X_format VARCHAR2,
      X_trx_text varchar2) RETURN VARCHAR2 IS
  l_str VARCHAR2(150);
  l_len NUMBER;
  l_pos1 NUMBER;
  l_pos2 NUMBER;
  l_flag NUMBER;
  l_fixed VARCHAR2(30);
  l_format VARCHAR2(1);
  l_fmt VARCHAR2(150);
  l_string VARCHAR2(1);
  l_tmp_str     VARCHAR2(255);
  l_tmp_str2 VARCHAR2(255);
BEGIN
  l_fmt := replace(replace(X_format, '('),')');
  l_len := LENGTH(l_fmt);
  l_pos1 := instr(X_format, '(');

  IF l_pos1 = 1 THEN    -- the format has no indicator
    l_flag := 1;
    l_tmp_str := rtrim(ltrim(substr(X_trx_text, instr(X_trx_text, ' ', -1))));

    IF LENGTH(l_tmp_str) = l_len THEN
      WHILE ( l_len > 0 ) LOOP
        IF ( ( Is_Number(substr(l_tmp_str, l_len, 1))
               AND
               substr(l_fmt, l_len, 1) IN ('a', 'A') )
             OR
             ( NOT Is_Number(substr(l_tmp_str, l_len, 1))
      AND
               substr(l_fmt, l_len, 1) IN ('n', 'N') ) ) THEN
          l_flag := 0;
          EXIT; -- Not in the format
        END IF;
        l_len := l_len-1;
      END LOOP;

      IF l_flag = 1 THEN
        RETURN l_tmp_str;
      ELSE
        IF l_tmp_str = X_trx_text THEN   -- Format was not found.
          RETURN NULL;
        END IF;
        RETURN Find_Formatted_String( X_format, substr(X_trx_text, 1, instr(X_trx_text, ' ', -1)-1) );
      END IF;
    ELSE
      IF l_tmp_str = X_trx_text THEN   -- Format was not found.
          RETURN NULL;
      END IF;
      RETURN Find_Formatted_String( X_format, substr(X_trx_text, 1, instr(X_trx_text, ' ', -1)-1) );
    END IF;
  ELSE -- the format has indicator
    l_fixed := substr(X_format, 1, l_pos1-1);
    l_pos2 := instr(X_trx_text, l_fixed);

    IF l_pos2 = 0 THEN
      RETURN NULL;  -- couldn't find format in TRX_TEXT.
    END IF;

    IF instr(X_format, '~') <> 0 THEN
      l_tmp_str := substr(X_trx_text, l_pos2 + LENGTH(l_fixed)) || ' .';
      RETURN substr(l_tmp_str, 1 , instr(l_tmp_str, ' ')-1);
    END IF;

    l_str := substr(X_trx_text, l_pos2, l_len);

    l_pos1 := l_pos1 + 1;
    l_format := substr(X_format, l_pos1, 1);
    l_string := substr(l_str, l_pos1 - 1, 1);
    WHILE ( l_format <> ')' ) LOOP
      IF ( (l_format in ('A','a') AND Is_Number(l_string))
  OR
           (l_format in ('N','n') AND NOT Is_Number(l_string)) ) THEN
        RETURN Find_Formatted_String(X_format, REPLACE(X_trx_text, l_str));
      END IF;
      l_pos1 := l_pos1 + 1;
      l_format := substr(X_format, l_pos1, 1);
      l_string := substr(l_str, l_pos1 - 1, 1);
    END LOOP;
  END IF;

  IF G_include_indicator = 'Y' THEN
    RETURN l_str;
  ELSE
    RETURN substr(l_str, LENGTH(l_fixed)+1);
  END IF;
END Find_Formatted_String;



FUNCTION Get_Formatted_String(X_string VARCHAR2) RETURN VARCHAR2 IS
  l_pos NUMBER;
  l_format VARCHAR2(50);
  l_tmp_format VARCHAR2(150);
  l_return VARCHAR2(255);
  l_return_tmp VARCHAR2(255);
  l_concatenate_format_flag VARCHAR2(1);
BEGIN
  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('>> Get_Formatted_String' );
    cep_standard.debug('X_string  = '|| X_string );
  END IF;


  l_tmp_format := LTRIM(RTRIM(G_predefined_format));
  l_pos := INSTR(l_tmp_format, ',');
  l_return := NULL;
  l_concatenate_format_flag := nvl(G_concatenate_format_flag, 'N');
  --l_concatenate_format_flag := 'Y';

  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
    cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
    cep_standard.debug('l_pos = '||l_pos||'.');

  END IF;


  WHILE l_pos <> 0 LOOP
    l_format := LTRIM(RTRIM(SUBSTR(l_tmp_format, 1, l_pos-1)));

 -- DBMS_OUTPUT.put_line('l_format 1: '||l_format);
    cep_standard.debug('l_format 1 = '||l_format||'.');

    IF (l_concatenate_format_flag = 'Y') THEN
      IF l_return IS NOT NULL THEN
        l_return := l_return || '/' || Find_Formatted_String(l_tmp_format, x_string);

        IF l_DEBUG in ('Y', 'C') THEN
     cep_standard.debug('l_return not null = '||l_return||'.');
  END IF;
      ELSE
        l_return := Find_Formatted_String(l_tmp_format, x_string);

        IF l_DEBUG in ('Y', 'C') THEN
       cep_standard.debug('l_return  null = '||l_return||'.');
  END IF;
      END IF;
    ELSE
      l_return := Find_Formatted_String(l_format, X_string);

      cep_standard.debug('concat = N - l_return  = '||l_return||'.');

      IF l_return IS NOT NULL THEN
        --l_return := Find_Formatted_String(l_format, X_string);
      --RETURN l_return;
        IF l_DEBUG in ('Y', 'C') THEN
     cep_standard.debug('concat = N - l_return not null = '||l_return||'.');
  END IF;

RETURN rtrim(l_return, '/');

      END IF;
    END IF;

    IF l_return IS NOT NULL THEN
    cep_standard.debug('l_return  = '||l_return||'.');
    END IF;

    l_tmp_format := SUBSTR(l_tmp_format, l_pos+1);
    l_pos := INSTR(l_tmp_format, ',');
  END LOOP;

  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('l_tmp_format 2 = '||l_tmp_format||'.');
  END IF;

  IF (l_concatenate_format_flag = 'Y') THEN
    IF l_return IS NOT NULL THEN
      l_return := l_return || '/' || Find_Formatted_String(LTRIM(RTRIM(l_tmp_format)), x_string);
    ELSE
      l_return := Find_Formatted_String(LTRIM(RTRIM(l_tmp_format)), x_string);
    END IF;
  ELSE
    --RETURN rtrim(Find_Formatted_String(LTRIM(RTRIM(l_tmp_format)), X_string), '/');
      l_return := rtrim(Find_Formatted_String(LTRIM(RTRIM(l_tmp_format)), X_string), '/');
  END IF;

  IF l_DEBUG in ('Y', 'C') THEN
    l_return_tmp :=  rtrim(l_return, '/');
    cep_standard.debug('l_return_tmp = '||l_return_tmp||'.');
  END IF;

    RETURN rtrim(l_return, '/');

END Get_Formatted_String;


PROCEDURE Insert_Line IS
  l_rec_cnt NUMBER;
  l_err   NUMBER;
  l_row_id ROWID;
  l_statement_number VARCHAR2(50);
BEGIN
 IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Line');

  cep_standard.debug('Line_number     -'||to_char(G_line_number)||'.');
  cep_standard.debug('Statement Number -'||G_statement_number||'.');
  cep_standard.debug('bank Account Num  -'||G_bank_account_num||'.');
  cep_standard.debug('Trx_date -'||G_trx_date||'.');
  cep_standard.debug('Trx_code -'||G_trx_code||'.');
  cep_standard.debug('Amount -'||to_char(G_amount)||'.');
  cep_standard.debug('Trx Text -'||G_trx_text||'.');
  cep_standard.debug('Invoice Text -'||G_BANK_TRX_NUMBER||'.');
  cep_standard.debug('Customer Text -'||G_BANK_TRX_NUMBER||'.');
 
  cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
  cep_standard.debug('Effective Date -'||G_effective_date||'.');
  cep_standard.debug('Currency Code   -'||G_line_currency_code||'.');
  cep_standard.debug('Exchange Rate     -'||G_exchange_rate||'.');
  cep_standard.debug('Bank Trx Number -'||G_INVOICE_TEXT||'.');
  cep_standard.debug('Created By -'||G_user_id||'.');
  cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
  cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
  cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
  cep_standard.debug('Original_Amount -'||G_original_amount||'.');
  cep_standard.debug('Charges_Amount -'||G_charges_amount||'.');

  cep_standard.debug('Insert Line'||to_char(G_line_number));
 END IF;

  IF G_format_type = 'SWIFT940' THEN
    l_statement_number := G_statement_number || ' - ' || to_char(sysdate);
  ELSE
    l_statement_number := G_statement_number;
  END IF;

  IF Valid_Statement THEN
    --
    -- Overwrite the existing bank statement of the same bank account and statement number.
    --
    SELECT count(*)
    INTO   l_rec_cnt
    FROM   ce_statement_lines_interface
    WHERE  bank_account_num = G_bank_account_num
    AND   statement_number = l_statement_number
    AND    line_number      = G_line_number;

    IF l_rec_cnt > 0 THEN
      DELETE FROM ce_statement_lines_interface
      WHERE  bank_account_num = G_bank_account_num
      AND    statement_number = l_statement_number
      AND    line_number      = G_line_number;
      G_total_line_deleted := G_total_line_deleted + l_rec_cnt;
    END IF;

    G_CUSTOMER_TEXT := rtrim(rtrim(G_CUSTOMER_TEXT),'/');
    G_trx_text := rtrim(rtrim(rtrim(G_trx_text),'/'));
    G_bank_account_text := rtrim(rtrim(G_bank_account_text),'/');
    G_BANK_TRX_NUMBER := rtrim(rtrim(G_BANK_TRX_NUMBER),'/');
    G_invoice_text := rtrim(rtrim(G_invoice_text),'/');
   

    CE_STAT_LINES_INF_PKG.Insert_Row(l_row_id,
                       rtrim(G_bank_account_num),
                       rtrim(l_statement_number),
                       to_number(rtrim(G_line_number)),
                       rtrim(G_trx_date),
                       rtrim(G_trx_code),
                       rtrim(G_effective_date),
                       G_trx_text,
                       G_bank_trx_number,
                       G_bank_account_text,
                       to_number(G_amount),
                       to_number(rtrim(G_charges_amount)),
      rtrim(G_line_currency_code),
                       to_number(G_exchange_rate),
                       rtrim(G_user_exchange_rate_type),
                       rtrim(G_exchange_rate_date),
      to_number(rtrim(G_original_amount)),
                       G_CUSTOMER_TEXT,
                       G_invoice_text,
                       to_number(G_user_id),
                       SYSDATE,
                       to_number(G_user_id),
                       SYSDATE,
                       NULL,
                       NULL, NULL, NULL, NULL, NULL,
                       NULL, NULL, NULL, NULL, NULL,
                       NULL, NULL, NULL, NULL, NULL);
  END IF;
  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Insert_Line');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_err := SQLCODE;
    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Insert_Line - '|| to_char(l_err));
    END IF;
    RAISE;
END Insert_Line;

FUNCTION Get_Value(X_pos IN NUMBER,
X_rec_no IN NUMBER) RETURN VARCHAR2 IS
  l_result      VARCHAR2(255);
BEGIN
  IF (X_pos = 1) THEN
    SELECT substr(rtrim(ltrim(column1)),1,255)
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 2) THEN
    SELECT rtrim(ltrim(column2))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 3) THEN
    SELECT rtrim(ltrim(column3))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 4) THEN
    SELECT rtrim(ltrim(column4))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 5) THEN
    SELECT rtrim(ltrim(column5))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 6) THEN
    SELECT rtrim(ltrim(column6))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 7) THEN
    SELECT rtrim(ltrim(column7))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 8) THEN
    SELECT rtrim(ltrim(column8))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 9) THEN
    SELECT rtrim(ltrim(column9))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 10) THEN
    SELECT rtrim(ltrim(column10))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 11) THEN
    SELECT rtrim(ltrim(column11))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 12) THEN
    SELECT rtrim(ltrim(column12))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 13) THEN
    SELECT rtrim(ltrim(column13))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 14) THEN
    SELECT rtrim(ltrim(column14))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 15) THEN
    SELECT rtrim(ltrim(column15))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 16) THEN
    SELECT rtrim(ltrim(column16))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 17) THEN
    SELECT rtrim(ltrim(column17))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 18) THEN
    SELECT rtrim(ltrim(column18))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 19) THEN
    SELECT rtrim(ltrim(column19))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 20) THEN
    SELECT rtrim(ltrim(column20))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 21) THEN
    SELECT rtrim(ltrim(column21))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 22) THEN
    SELECT rtrim(ltrim(column22))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 23) THEN
    SELECT rtrim(ltrim(column23))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 24) THEN
    SELECT rtrim(ltrim(column24))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 25) THEN
    SELECT rtrim(ltrim(column25))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 26) THEN
    SELECT rtrim(ltrim(column26))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 27) THEN
    SELECT rtrim(ltrim(column27))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 28) THEN
    SELECT rtrim(ltrim(column28))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 29) THEN
    SELECT rtrim(ltrim(column29))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 30) THEN
    SELECT rtrim(ltrim(column30))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 31) THEN
    SELECT rtrim(ltrim(column31))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 32) THEN
    SELECT rtrim(ltrim(column32))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 33) THEN
    SELECT rtrim(ltrim(column33))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 34) THEN
    SELECT rtrim(ltrim(column34))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  ELSIF (X_pos = 35) THEN
    SELECT rtrim(ltrim(column35))
    INTO l_result
    FROM ce_stmt_int_tmp
    WHERE rec_no = X_rec_no;
  END IF;

  --
  -- If format is entered then search for the target string.
  --
  IF G_predefined_format IS NOT NULL THEN
    l_result := Get_Formatted_String(nvl(l_result,'x'));
  END IF;

  return(l_result);
EXCEPTION
  WHEN OTHERS THEN
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('EXCEPTION: Get_Value - X_pos '|| to_char(X_pos)||', X_rec_no '|| to_char(X_rec_no));
    END IF;
    RAISE;
END Get_Value;

PROCEDURE Lookup_Pos(X_hdr_line IN VARCHAR2,
    X_column IN VARCHAR2,
    X_position OUT NOCOPY NUMBER) IS
  CURSOR C_h IS
    SELECT nvl(position,0),
  format,
  include_format_ind,
  concatenate_format_flag
    FROM   ce_bank_stmt_map_hdr_v
    WHERE  map_id = G_map_id
    AND   column_name = X_column;

  CURSOR C_l IS
    SELECT nvl(position,0),
  format,
  include_format_ind,
  concatenate_format_flag
    FROM   ce_bank_stmt_map_line_v
    WHERE  map_id = G_map_id
    AND   column_name = X_column;

  l_pos NUMBER;
  l_err NUMBER;
BEGIN
  IF X_hdr_line = 'H' THEN
    OPEN C_h;
    FETCH C_h INTO X_position, G_predefined_format, G_include_indicator, G_concatenate_format_flag;
    CLOSE C_h;
  ELSE
    OPEN C_l;
    FETCH C_l INTO X_position, G_predefined_format, G_include_indicator, G_concatenate_format_flag;
    CLOSE C_l;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    X_position := 0;
  WHEN OTHERS THEN
    l_err := SQLCODE;
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('EXCEPTION: Lookup_Pos - '|| to_char(l_err));
    END IF;
    RAISE;
END Lookup_Pos;

FUNCTION Find_Last_Column_Pos(X_rec_no     NUMBER) RETURN NUMBER IS
  l_str VARCHAR2(50);
BEGIN
  SELECT substr(column1,1,1)||substr(column2,1,1)||substr(column3,1,1)||substr(column4,1,1)||substr(column5,1,1)||
         substr(column6,1,1)||substr(column7,1,1)||substr(column8,1,1)||substr(column9,1,1)||substr(column10,1,1)||
  substr(column11,1,1)||substr(column12,1,1)||substr(column13,1,1)||substr(column14,1,1)||substr(column15,1,1)||
         substr(column16,1,1)||substr(column17,1,1)||substr(column18,1,1)||substr(column19,1,1)||substr(column20,1,1)||
  substr(column21,1,1)||substr(column22,1,1)||substr(column23,1,1)||substr(column24,1,1)||substr(column25,1,1)||
         substr(column26,1,1)||substr(column27,1,1)||substr(column28,1,1)||substr(column29,1,1)||substr(column30,1,1)||
  substr(column31,1,1)||substr(column32,1,1)||substr(column33,1,1)||substr(column34,1,1)||substr(column35,1,1)
  INTO   l_str
  FROM   ce_stmt_int_tmp
  WHERE  rec_no = X_rec_no;

  RETURN LENGTH(l_str);
END Find_Last_Column_Pos;


FUNCTION Lookup_Val(X_hdr_line VARCHAR2,
   X_rec_no NUMBER,
   X_column VARCHAR2,
   X_rec_len   NUMBER DEFAULT 0) RETURN VARCHAR2 IS
  l_pos NUMBER;
  l_value VARCHAR2(255);
BEGIN
  Lookup_Pos(X_hdr_line, X_column, l_pos);
  IF l_pos = 0 THEN
   l_value := NULL;
  ELSE
    IF l_pos < 0 THEN
      IF X_rec_len = 0 THEN
        l_pos := Find_Last_Column_Pos(X_rec_no) + 1 + l_pos;
      ELSE
        l_pos := X_rec_len + l_pos;
      END IF;
    END IF;
    l_value := Get_Value(l_pos, X_rec_no);
  END IF;

  RETURN l_value;
END Lookup_Val;

FUNCTION Hdr_Or_Line(X_rec_id VARCHAR2) RETURN VARCHAR2 IS
  l_hdr NUMBER;
  l_line NUMBER;
BEGIN
  SELECT count(*)
  INTO   l_hdr
  FROM   CE_BANK_STMT_MAP_HDR
  WHERE  map_id = G_map_id
  AND    rec_id_no = X_rec_id;

  SELECT count(*)
  INTO   l_line
  FROM   CE_BANK_STMT_MAP_LINE
  WHERE  map_id = G_map_id
  AND    rec_id_no = X_rec_id;

  IF (l_hdr > 0 AND l_line > 0) THEN
    RETURN 'B';
  ELSIF l_hdr > 0 THEN
    RETURN 'H';
  ELSIF l_line > 0 THEN
    RETURN 'L';
  ELSE
    RETURN 'N';
  END IF;
END Hdr_Or_Line;


FUNCTION Valid_Bank_Account RETURN BOOLEAN IS
    CURSOR C_bank IS
        SELECT  BB.bank_name,
                BB.bank_branch_name,
                BB.branch_party_id,
                BA.bank_account_id,
                BA.currency_code
        FROM    CE_BANK_ACCOUNTS BA,
                CE_BANK_BRANCHES_V BB
        WHERE   BB.branch_party_id   = BA.bank_branch_id
         AND    BA.bank_account_num = G_bank_account_num
         AND    NVL(BA.account_classification,'DUMMY') = 'INTERNAL'               -- Bug 6511845
         AND    NVL(G_Bank_Branch_id, BA.Bank_branch_id) = BA.bank_branch_id      -- Bug 8209720
         AND    NVL(G_bank_account_id, BA.bank_account_id) = BA.bank_account_id;  -- Bug 8209720

    l_cnt NUMBER;

BEGIN
    cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.valid_bank_account');
    IF G_bank_account_num IS NULL THEN
        cep_standard.debug('G_bank_account_num is null');
        FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISS_REQ_FIELD');
        FND_MESSAGE.set_token('FIELD', 'BANK_ACCOUNT_NUM');
        CE_SQLLDR_ERRORS_PKG.insert_row(
                NVL(G_statement_number, 'XXXXXXXXXXX'),
                NVL(G_bank_account_num, 'XXXXXXXXXXX'),
                G_rec_no, fnd_message.get, 'E');
        RETURN FALSE;
    END IF;

    SELECT count(*)
    INTO   l_cnt
    FROM   CE_BANK_ACCOUNTS
    WHERE  BANK_ACCOUNT_NUM = G_bank_account_num
     AND   NVL(ACCOUNT_CLASSIFICATION,'DUMMY') = 'INTERNAL'            -- Bug 6511845
     AND   NVL(G_Bank_Branch_id,Bank_branch_id) = Bank_branch_id       -- Bug 8209720
     AND   NVL(G_bank_account_id, bank_account_id) = bank_account_id;  -- Bug 8209720

    cep_standard.debug('l_cnt = '||to_char(l_cnt));
    IF l_cnt = 0 THEN      -- Bank account is not setup.
        FND_MESSAGE.set_name('CE', 'CE_BANK_ACCNT_NOT_DEFINED');
        FND_MESSAGE.set_token('BANK_ACCNT',G_bank_account_num);
        CE_SQLLDR_ERRORS_PKG.insert_row(
                G_statement_number,
                G_bank_account_num,
                G_rec_no, fnd_message.get, 'E');
        RETURN FALSE;

    ELSIF l_cnt > 1 THEN   -- There are more than one bank with this account number.
        FND_MESSAGE.set_name('CE', 'CE_TOO_MANY_BANK_ACCNT');
        FND_MESSAGE.set_token('BANK_ACCNT',G_bank_account_num);
        CE_SQLLDR_ERRORS_PKG.insert_row(
            G_statement_number,
            G_bank_account_num,
            G_rec_no, fnd_message.get, 'W');

    ELSE
        IF ( G_bank_name IS NULL OR G_bank_branch_name IS NULL OR G_hdr_currency_code IS NULL) THEN
            OPEN  C_bank;
            FETCH C_bank INTO G_bank_name, G_bank_branch_name, G_sub_branch_id,
                              G_sub_account_id, G_hdr_currency_code; --, G_org_id;
            CLOSE C_bank;
        END IF;

    END IF;

    cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.valid_bank_account');
    RETURN TRUE;

END Valid_Bank_Account;

PROCEDURE Decode_Line_BAI(X_rec_no NUMBER) IS
  CURSOR C_rec IS
    SELECT column3, column4
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;

  l_val1 VARCHAR2(100);
  l_val2 VARCHAR2(100);
  l_val3 VARCHAR2(100);
  l_rec_len     NUMBER;
  l_err NUMBER;

  l_str VARCHAR2(2000);
BEGIN
  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Line_BAI');
  END IF;

  OPEN C_rec;
  FETCH C_rec
  INTO l_val1, l_val2;
  CLOSE C_rec;

  IF l_val1 = 'S' THEN
    l_rec_len := 10;
  ELSIF l_val1 = 'V' THEN
    l_rec_len := 9;
    G_effective_date := CONV_TO_DATE(l_val2);
  ELSIF l_val1 = 'D' THEN
    l_rec_len := 8 + to_number(l_val2) * 2;
  ELSE
    l_rec_len := 7;
  END IF;

  G_trx_text := substr(Lookup_Val('L', X_rec_no, 'TRX_TEXT', l_rec_len), 1, 255);
  G_BANK_TRX_NUMBER := substr(Lookup_Val('L', X_rec_no, 'BANK_TRX_NUMBER', l_rec_len), 1, 240);
 
  G_invoice_text := substr(Lookup_Val('L', X_rec_no, 'INVOICE_TEXT', l_rec_len), 1, 30);
 
  G_bank_account_text := substr(Lookup_Val('L', X_rec_no, 'BANK_ACCOUNT_TEXT', l_rec_len), 1, 30);
  G_CUSTOMER_TEXT := substr(Lookup_Val('L', X_rec_no, 'CUSTOMER_TEXT', l_rec_len), 1, 80);

  G_trx_date := CONV_TO_DATE(Lookup_Val('L', X_rec_no, 'TRX_DATE', l_rec_len));
  -- G_effective_date := CONV_TO_DATE(Lookup_Val('L', X_rec_no, 'EFFECTIVE_DATE', l_rec_len));
  G_line_currency_code :=  substr(Lookup_Val('L', X_rec_no, 'CURRENCY_CODE', l_rec_len), 1, 15);
  G_exchange_rate := to_number(Lookup_Val('L', X_rec_no, 'EXCHANGE_RATE', l_rec_len));
  G_user_exchange_rate_type := substr(Lookup_Val('L', X_rec_no, 'USER_EXCHANGE_RATE_TYPE', l_rec_len), 1, 30);
  G_exchange_rate_date :=  CONV_TO_DATE(Lookup_Val('L', X_rec_no, 'EXCHANGE_RATE_DATE', l_rec_len));
  G_original_amount := to_number(Lookup_Val('L', X_rec_no, 'ORIGINAL_AMOUNT', l_rec_len)) / G_line_precision;
  G_charges_amount := to_number(Lookup_Val('L', X_rec_no, 'CHARGES_AMOUNT', l_rec_len)) / G_line_precision;

  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Decode_Line_BAI');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_err := SQLCODE;
    FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
    FND_MESSAGE.set_token('ERR', to_char(l_err));
    CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Line_BAI - '|| to_char(l_err));
    END IF;
END Decode_Line_BAI;

PROCEDURE Find_Columns( X_cnt IN NUMBER,
X_rec_no IN NUMBER,
X_col1 OUT NOCOPY VARCHAR2,
X_col2 OUT NOCOPY VARCHAR2,
X_col3 OUT NOCOPY VARCHAR2,
X_col4 OUT NOCOPY VARCHAR2,
X_col5 OUT NOCOPY VARCHAR2) IS
BEGIN
  IF (X_cnt = 1) THEN
    SELECT column1, column2, column3, column4, column5
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 2) THEN
    SELECT column2, column3, column4, column5, column6
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 3) THEN
    SELECT column3, column4, column5, column6, column7
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 4) THEN
    SELECT column4, column5, column6, column7, column8
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 5) THEN
    SELECT column5, column6, column7, column8, column9
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 6) THEN
    SELECT column6, column7, column8, column9, column10
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 7) THEN
    SELECT column7, column8, column9, column10, column11
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 8) THEN
    SELECT column8, column9, column10, column11, column12
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 9) THEN
    SELECT column9, column10, column11, column12, column13
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 10) THEN
    SELECT column10, column11, column12, column13, column14
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 11) THEN
    SELECT column11, column12, column13, column14, column15
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 12) THEN
    SELECT column12, column13, column14, column15, column16
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 13) THEN
    SELECT column13, column14, column15, column16, column17
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 14) THEN
    SELECT column14, column15, column16, column17, column18
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 15) THEN
    SELECT column15, column16, column17, column18, column19
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 16) THEN
    SELECT column16, column17, column18, column19, column20
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 17) THEN
    SELECT column17, column18, column19, column20, column21
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 18) THEN
    SELECT column18, column19, column20, column21, column22
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 19) THEN
    SELECT column19, column20, column21, column22, column23
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 20) THEN
    SELECT column20, column21, column22, column23, column24
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 21) THEN
    SELECT column21, column22, column23, column24, column25
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 22) THEN
    SELECT column22, column23, column24, column25, column26
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 23) THEN
    SELECT column23, column24, column25, column26, column27
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 24) THEN
    SELECT column24, column25, column26, column27, column28
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 25) THEN
    SELECT column25, column26, column27, column28, column29
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 26) THEN
    SELECT column26, column27, column28, column29, column30
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 27) THEN
    SELECT column27, column28, column29, column30, column31
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 28) THEN
    SELECT column28, column29, column30, column31, column32
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 29) THEN
    SELECT column29, column30, column31, column32, column33
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 30) THEN
    SELECT column30, column31, column32, column33, column34
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 31) THEN
    SELECT column31, column32, column33, column34, column35
    INTO   X_col1, X_col2, X_col3, X_col4, X_col5
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  ELSIF (X_cnt = 32) THEN
    SELECT column32, column33, column34, column35
    INTO   X_col1, X_col2, X_col3, X_col4
    FROM   ce_stmt_int_tmp
    WHERE  rec_no = X_rec_no;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('EXCEPTION: Find_Columns - X_cnt '|| to_char(X_cnt)||', X_rec_no '|| to_char(X_rec_no));
    END IF;
    RAISE;
END Find_Columns;

PROCEDURE Decode_Hdr_BAI(X_rec_id VARCHAR2,
    X_rec_no NUMBER) IS
  l_cnt NUMBER;
  l_cursor_id INTEGER;
  l_exec_id INTEGER;
  l_query VARCHAR2(1000);
  l_val1 VARCHAR2(100);
  l_val2 VARCHAR2(100);
  l_val3 VARCHAR2(100);
  l_val4 VARCHAR2(100);
  l_val5 VARCHAR2(100);
  l_temp_val1 VARCHAR2(100);
  l_temp_val2   VARCHAR2(100);
  l_temp_val3 VARCHAR2(100);
  l_temp_val4 VARCHAR2(100);
  l_temp_val5 VARCHAR2(100);
  l_err NUMBER;
BEGIN
  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Hdr_BAI');
  END IF;

  IF X_rec_id in ('3','03') THEN
    l_cnt := 3;
  ELSIF X_rec_id = '88' THEN
/*bug5124547*/
if G_lcnt <> 0 then
   l_cnt := 1+G_lcnt;
   G_lcnt :=0;
else
  l_cnt := 1;
end if;
/*bug5124547*/
  END IF;
  WHILE (l_cnt <= 32) LOOP
    Find_Columns(l_cnt, X_rec_no, l_val1, l_val2, l_val3, l_val4, l_val5);

  /* bug 3771128. Remove the '/' character.Check if the last
read value is a trx_code i.e., g_last_val1 is not null then
swap the values so that the values are correctly placed in
the corresponding variables. This will simulate the 88 record
as being started with a trx_code. - Start*/
/*bug5100563*/
    IF n =1 then
l_val5 := l_val4;
l_val4 := l_val3;
l_val3 := l_val2;
l_val2 := l_val1;
l_val1 := G_last_val1;
l_cnt := l_cnt -1;
n:=0;
    ELSIF n =2 then
l_val5 := l_val3;
l_val4 := l_val2;
l_val3 := l_val1;
l_val2 := G_last_val2;
l_val1 := G_last_val1;
l_cnt := l_cnt -2;
n:=0;
    ELSIF n =3 then
l_val5 := l_val2;
l_val4 := l_val1;
l_val3 := G_last_val3;
l_val2 := G_last_val2;
l_val1 := G_last_val1;
l_cnt := l_cnt -3;
n:=0;
    END IF;

 IF rtrim(rtrim(l_val1),'/') IS NOT NULL THEN --7688232  If condition added
    if (instr(l_val1,'/') >0) then
l_val1 := rtrim(rtrim(l_val1),'/');
G_last_val1:= l_val1;
n:=1;
exit;
    elsif (instr(l_val2,'/') >0) then
l_val1 := rtrim(rtrim(l_val1),'/');
l_val2 := rtrim(rtrim(l_val2),'/');
G_last_val1:= l_val1;
G_last_val2:= l_val2;
n:=2;
exit;
    elsif (instr(l_val3,'/') >0) then
l_val1 := rtrim(rtrim(l_val1),'/');
l_val2 := rtrim(rtrim(l_val2),'/');
l_val3 := rtrim(rtrim(l_val3),'/');
G_last_val1:= l_val1;
G_last_val2:= l_val2;
G_last_val3:= l_val3;
n:=3;
exit;
    end if;
 END IF; --7688232
/*bug5100563*/
    IF l_val1 = '010' THEN
      G_control_begin_balance := to_number(l_val2)/G_hdr_precision;
    ELSIF l_val1 = '015' THEN
      G_control_end_balance := to_number(l_val2)/G_hdr_precision;
    ELSIF l_val1 = '045' THEN
      G_cashflow_balance := to_number(l_val2)/G_hdr_precision;
      G_int_calc_balance := to_number(l_val2)/G_hdr_precision;
    ELSIF l_val1 = '020' THEN
      G_average_close_ledger_mtd := to_number(l_val2)/G_hdr_precision;
    ELSIF l_val1 = '025' THEN
      G_average_close_ledger_ytd := to_number(l_val2)/G_hdr_precision;
    ELSIF l_val1 = '050' THEN
      G_average_close_available_mtd := to_number(l_val2)/G_hdr_precision;
    ELSIF l_val1 = '055' THEN
      G_average_close_available_ytd := to_number(l_val2)/G_hdr_precision;
    ELSIF l_val1 = '072' THEN
      G_one_day_float := to_number(l_val2)/G_hdr_precision;
    ELSIF l_val1 = '074' THEN
      G_two_day_float := to_number(l_val2)/G_hdr_precision;
    ELSIF l_val1 = '400' THEN
G_control_total_dr := to_number(l_val2)/G_hdr_precision;
IF(l_val3 IS NOT NULL) THEN     -- Bug 9005502 Added if Condition
     /*Bug 3164477 added the following 2 lines for dr line count*/
     G_control_dr_line_count := to_number(l_val3);
     select decode(G_control_dr_line_count,null,
                   G_control_line_count,
nvl(G_control_line_count,0)+G_control_dr_line_count)
    into G_control_line_count from dual;
END IF;      -- Bug 9005502 Added if Condition
    ELSIF l_val1 = '100' THEN
G_control_total_cr := to_number(l_val2)/G_hdr_precision;
IF(l_val3 IS NOT NULL) THEN  -- Bug 9005502 Added if Condition
    /*Bug 3164477 added the following 2 lines for cr line count*/
     G_control_cr_line_count := to_number(l_val3);
     select decode(G_control_cr_line_count,null,
                   G_control_line_count,
nvl(G_control_line_count,0)+G_control_cr_line_count)
    into G_control_line_count from dual;
END IF;        -- Bug 9005502 Added if Condition
-- Bug 9005502 Start
    ELSIF L_VAL1 = '102' THEN
G_control_cr_line_count := to_number(l_val2);
G_control_line_count := Nvl(G_control_line_count,0) + Nvl(G_control_cr_line_count,0);


    ELSIF L_VAL1 = '402' THEN
G_control_dr_line_count := to_number(l_val2);
G_control_line_count :=  Nvl(G_control_line_count,0) + Nvl(G_control_dr_line_count,0);
-- Bug 9005502 End

END IF;


   IF l_val4 = 'S' THEN
/*bug5124547*/
Find_Columns(l_cnt+4,X_rec_no, l_temp_val1, l_temp_val2, l_temp_val3, l_temp_val4, l_temp_val5);
if (instr(l_temp_val1,'/')>0) then
G_lcnt :=2;
elsif (instr(l_temp_val2,'/')>0) then
G_lcnt :=1;
elsif (instr(l_temp_val3,'/')>0) then
G_lcnt :=0;
end if;
     l_cnt := l_cnt + 7;
/*bug5124547*/
    ELSIF l_val4 = 'V' THEN
      l_cnt := l_cnt + 6;
    ELSIF l_val4 = 'D' THEN
      l_cnt := l_cnt + 5 + to_number(l_val5) * 2;
    ELSE
      l_cnt := l_cnt + 4;
    END IF;
  END LOOP;

  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Decode_Hdr_BAI');
  END IF;
EXCEPTION  -- Bug 3608257 added this EXCEPTION section
  WHEN OTHERS THEN
    l_err := SQLCODE;
    FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
    FND_MESSAGE.set_token('ERR', to_char(l_err));
    CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
    cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Hdr_BAI - '|| to_char(l_err));
END Decode_Hdr_BAI;

PROCEDURE Load_BAI2 IS

  CURSOR C_tmp_tbl IS
    SELECT rec_no, rec_id_no, column1, rtrim(replace(column2,' '),'/') col2, column6, rtrim(replace(column8,' '),'/') col8
    FROM   ce_stmt_int_tmp
    ORDER BY rec_no;

  CURSOR C_trx_text IS
    SELECT  column_name,
            format,
            include_format_ind,
   concatenate_format_flag
    FROM    ce_bank_stmt_map_line
    WHERE   map_id = G_map_id
    AND     position = -1;

  l_control_total_cr NUMBER := 0;  -- Calculate total credit  amount.
  l_control_total_dr NUMBER := 0;  -- Calculate total debit  amount.
  l_control_total_cnt NUMBER := 0;  -- Count total number of records in 03,16,88,49.
  l_rec_cnt     NUMBER;       -- Count total number of records in 03,16,88,49.
  l_cnt NUMBER;

  l_currency_code VARCHAR2(15);
  l_line_cnt   NUMBER;
  l_last_rid   VARCHAR2(2);
  l_err       NUMBER;
  l_statement_date DATE;
  l_bank_trx_num VARCHAR2(240);
  l_process_this_record BOOLEAN := TRUE; /* 2643505 */
  l_rec ce_stmt_int_tmp%rowtype; -- Bug 3228203

BEGIN
  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_BAI');
  END IF;

  FOR C_rec IN C_tmp_tbl LOOP
    G_rec_no := C_rec.rec_no;

    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('CE_BANK_STATEMENT_LOADER.Load_BAI - G_rec_no = '||G_rec_no );
    END IF;

    IF ( G_rec_no = 1 AND
         C_rec.rec_id_no <> '01' ) THEN
      FND_MESSAGE.set_name('CE', 'CE_INVALID_BAI2');
      CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
      EXIT;
    ELSIF C_rec.rec_id_no = '01' THEN
      Init_Hdr_Rec;
      Init_Line_Rec;
      l_line_cnt := 0;
      l_last_rid := '1';
      IF nvl(C_rec.col8,'X') <> '2' THEN
        FND_MESSAGE.set_name('CE', 'CE_INVALID_BAI2');
        CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
        EXIT;
      END IF;
    ELSIF C_rec.rec_id_no = '02' THEN

    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('>>C_rec.rec_id_no = 02');
    END IF;

      G_statement_number := lookup_val('H', C_rec.rec_no, 'STATEMENT_NUMBER');

    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('G_statement_number = '|| G_statement_number);
    END IF;

      G_statement_date   := CONV_TIMESTAMP(lookup_val('H', C_rec.rec_no, 'STATEMENT_DATE') || ' ' || lookup_val('H', C_rec.rec_no, 'STATEMENT_TIMESTAMP'));

    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('G_statement_date = '|| G_statement_date);
    END IF;

      IF G_statement_number IS NULL THEN
        FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISS_REQ_FIELD');
        FND_MESSAGE.set_token('FIELD', 'STATEMENT_NUMBER');
        CE_SQLLDR_ERRORS_PKG.insert_row('XXXXXXXXXXX' , NVL(G_bank_account_num, 'XXXXXXXXXXX'),
C_rec.rec_no, fnd_message.get);
        EXIT;
      END IF;

      IF G_statement_date IS NULL THEN
        FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISS_REQ_FIELD');
        FND_MESSAGE.set_token('FIELD', 'STATEMENT_DATE');
        CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, NVL(G_bank_account_num, 'XXXXXXXXXXX'),
C_rec.rec_no, fnd_message.get);
        EXIT;
      END IF;

      l_statement_date := G_statement_date;
      l_currency_code := C_rec.column6;
      l_last_rid := '2';

      -- Added for p2p
      IF (G_gl_date_source = 'STATEMENT') THEN
        G_gl_date := trunc(G_statement_date);
      END IF;
    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('<<C_rec.rec_id_no = 02');
    END IF;

    ELSIF C_rec.rec_id_no = '03' THEN
      l_rec_cnt := 1;

      G_bank_account_num  := lookup_val('H', C_rec.rec_no, 'BANK_ACCOUNT_NUM');
      G_hdr_precision := Get_Precision(to_number(Lookup_Val('H', C_rec.rec_no, 'PRECISION')));
      IF ( G_hdr_precision = 1 AND G_precision <> 1 )THEN
        G_hdr_precision := G_precision;
      ELSIF ( G_hdr_precision <> 1 AND G_precision = 1 )THEN
        G_precision := G_hdr_precision;
      END IF;

      G_hdr_currency_code := nvl(lookup_val('H', C_rec.rec_no, 'CURRENCY_CODE'), l_currency_code);

      /* 2643505 Added */
      IF Valid_Bank_Account THEN
l_process_this_record := TRUE;
      ELSE
l_process_this_record := FALSE;
      END IF;
      /* 2643505 End Code Added */

      Decode_Hdr_BAI(C_rec.rec_id_no, C_rec.rec_no);

      l_last_rid := '3';
    ELSIF C_rec.rec_id_no = '16' THEN
      Init_Line_Rec;
      l_line_cnt := l_line_cnt + 1;
      l_rec_cnt := l_rec_cnt + 1;

      G_line_number       := l_line_cnt;

      G_line_precision := Get_Precision(to_number(Lookup_Val('L', C_rec.rec_no, 'PRECISION')));
      IF ( G_line_precision = 1 AND G_precision <> 1 )THEN
        G_line_precision := G_precision;
      END IF;

      G_trx_code := lookup_val('L', C_rec.rec_no, 'TRX_CODE');
      G_amount   := to_number(lookup_val('L', C_rec.rec_no, 'AMOUNT'))/G_line_precision;

      IF l_DEBUG in ('Y', 'C') THEN
        cep_standard.debug('Decode_Line_BAI(C_rec.rec_no)  = ' );
      END IF;

      Decode_Line_BAI(C_rec.rec_no);

      IF (to_number(G_trx_code) BETWEEN 100 AND 399) THEN
        l_control_total_cr := l_control_total_cr + G_amount;
      /* 2933873
         New specifications state that transaction codes upto 890
         can be used for Debit
      ELSIF (to_number(G_trx_code) BETWEEN 400 AND 699) THEN */
      ELSIF (to_number(G_trx_code) BETWEEN 400 AND 760) THEN
        l_control_total_dr := l_control_total_dr + G_amount;
      ELSIF (to_number(G_trx_code)=890 AND G_amount IS NULL) THEN    --Start 9381722
        G_amount:=0;
      END IF;

      IF G_trx_date IS NULL THEN
        G_trx_date := NVL(G_statement_date, l_statement_date);
      END IF;

      l_last_rid := '16';
      /* 2643505 Added the IF Condition */
      IF (l_process_this_record) THEN
      Insert_Line;
      END IF;

    ELSIF C_rec.rec_id_no = '88' THEN
      l_rec_cnt := l_rec_cnt + 1;

      IF l_last_rid = '3' THEN
        Decode_Hdr_BAI(C_rec.rec_id_no, C_rec.rec_no);
      ELSIF l_last_rid = '16' THEN
/* 2643505 Added the IF Condition */
IF l_process_this_record THEN
/* Bug3228203 added the following select stmt and modified
  the next update stmt.*/
  SELECT * INTO l_rec FROM ce_stmt_int_tmp
WHERE rec_id_no = C_rec.rec_id_no AND rec_no = C_rec.rec_no;

         /* Bug 6792668 Added rtrim */

         UPDATE ce_statement_lines_interface
         SET  trx_text = rtrim(substr(ltrim(rtrim(trx_text ||' '|| C_rec.column1)) ||
decode(l_rec.column2,null,null,','||ltrim(rtrim(l_rec.column2))) ||
decode(l_rec.column3,null,null,','||ltrim(rtrim(l_rec.column3))) ||
decode(l_rec.column4,null,null,','||ltrim(rtrim(l_rec.column4))) ||
decode(l_rec.column5,null,null,','||ltrim(rtrim(l_rec.column5))) ||
decode(l_rec.column6,null,null,','||ltrim(rtrim(l_rec.column6))) ||
decode(l_rec.column7,null,null,','||ltrim(rtrim(l_rec.column7))) ||
decode(l_rec.column8,null,null,','||ltrim(rtrim(l_rec.column8))) ||
decode(l_rec.column9,null,null,','||ltrim(rtrim(l_rec.column9))) ||
decode(l_rec.column10,null,null,','||ltrim(rtrim(l_rec.column10))) ||
decode(l_rec.column11,null,null,','||ltrim(rtrim(l_rec.column11))) ||
decode(l_rec.column12,null,null,','||ltrim(rtrim(l_rec.column12))) ||
decode(l_rec.column13,null,null,','||ltrim(rtrim(l_rec.column13))) ||
decode(l_rec.column14,null,null,','||ltrim(rtrim(l_rec.column14))) ||
decode(l_rec.column15,null,null,','||ltrim(rtrim(l_rec.column15))) ||
decode(l_rec.column16,null,null,','||ltrim(rtrim(l_rec.column16))) ||
decode(l_rec.column17,null,null,','||ltrim(rtrim(l_rec.column17))) ||
decode(l_rec.column18,null,null,','||ltrim(rtrim(l_rec.column18))) ||
decode(l_rec.column19,null,null,','||ltrim(rtrim(l_rec.column19))) ||
decode(l_rec.column20,null,null,','||ltrim(rtrim(l_rec.column20))) ||
decode(l_rec.column21,null,null,','||ltrim(rtrim(l_rec.column21))) ||
decode(l_rec.column22,null,null,','||ltrim(rtrim(l_rec.column22))) ||
decode(l_rec.column23,null,null,','||ltrim(rtrim(l_rec.column23))) ||
decode(l_rec.column24,null,null,','||ltrim(rtrim(l_rec.column24))) ||
decode(l_rec.column25,null,null,','||ltrim(rtrim(l_rec.column25))) ||
decode(l_rec.column26,null,null,','||ltrim(rtrim(l_rec.column26))) ||
decode(l_rec.column27,null,null,','||ltrim(rtrim(l_rec.column27))) ||
decode(l_rec.column28,null,null,','||ltrim(rtrim(l_rec.column28))) ||
decode(l_rec.column29,null,null,','||ltrim(rtrim(l_rec.column29))) ||
decode(l_rec.column30,null,null,','||ltrim(rtrim(l_rec.column30))) ||
decode(l_rec.column31,null,null,','||ltrim(rtrim(l_rec.column31))) ||
decode(l_rec.column32,null,null,','||ltrim(rtrim(l_rec.column32))) ||
decode(l_rec.column33,null,null,','||ltrim(rtrim(l_rec.column33))) ||
decode(l_rec.column34,null,null,','||ltrim(rtrim(l_rec.column34))) ||
decode(l_rec.column35,null,null,','||ltrim(rtrim(l_rec.column35)))
,1,255))
         WHERE   bank_account_num = G_bank_account_num
         AND     statement_number = G_statement_number
AND line_number   = l_line_cnt;

         FOR C_rec1 IN C_trx_text LOOP
           G_predefined_format := C_rec1.format;
           G_include_indicator := C_rec1.include_format_ind;
           G_concatenate_format_flag := C_rec1.concatenate_format_flag;

  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('C_rec.rec_id_no = 88 and l_last_rid = 16 ' );
    cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
    cep_standard.debug('G_include_indicator = '||G_include_indicator||'.');
    cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
  END IF;
    IF (C_rec1.column_name = 'CUSTOMER_TEXT') THEN  --Start 8273630
                 IF (G_predefined_format IS NOT NULL AND G_CUSTOMER_TEXT IS NULL) THEN
                    G_CUSTOMER_TEXT := Get_Formatted_String(ltrim(C_rec.column1));  --Bug 6448644
                 ELSIF(G_predefined_format IS NULL) THEN
                    BEGIN

     select SubStr(trx_text,1,240) INTO G_CUSTOMER_TEXT
                      FROM ce_statement_lines_interface
                      WHERE bank_account_num = G_bank_account_num
                      AND statement_number = G_statement_number
                   AND line_number = l_line_cnt;

EXCEPTION
                      WHEN OTHERS THEN  --no_data_found
                      l_err := SQLCODE;
                      IF l_DEBUG in ('Y', 'C') THEN
                        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');
                      END IF;
                      RAISE;
                    END;
                  END IF;--end of 8273630
           ELSIF ( C_rec1.column_name = 'BANK_TRX_NUMBER'AND
                   G_BANK_TRX_NUMBER IS NULL ) THEN
      G_BANK_TRX_NUMBER := Get_Formatted_String(C_rec.column1);
           ELSIF ( C_rec1.column_name = 'INVOICE_TEXT'AND
                   G_invoice_text IS NULL ) THEN
      G_invoice_text := Get_Formatted_String(C_rec.column1);
           ELSIF ( C_rec1.column_name = 'BANK_ACCOUNT_TEXT'AND
                   G_bank_account_text IS NULL ) THEN
               G_bank_account_text := Get_Formatted_String(C_rec.column1);
  ELSIF ( C_rec1.column_name = 'CURRENCY_CODE'AND
                   G_line_currency_code IS NULL ) THEN
      G_line_currency_code := Get_Formatted_String(C_rec.column1);
           ELSIF ( C_rec1.column_name = 'USER_EXCHANGE_RATE_TYPE'AND
                   G_user_exchange_rate_type IS NULL ) THEN
      G_user_exchange_rate_type := Get_Formatted_String(C_rec.column1);
           ELSIF ( C_rec1.column_name = 'EXCHANGE_RATE_DATE'AND
                   G_exchange_rate_date IS NULL ) THEN
               G_exchange_rate_date := Get_Formatted_String(C_rec.column1);
           ELSIF ( C_rec1.column_name = 'EXCHANGE_RATE'AND
                   G_exchange_rate IS NULL ) THEN
               G_exchange_rate := Get_Formatted_String(C_rec.column1);
           ELSIF ( C_rec1.column_name = 'ORIGINAL_AMOUNT'AND
                   G_original_amount IS NULL ) THEN
               G_original_amount := Get_Formatted_String(C_rec.column1);
           ELSIF ( C_rec1.column_name = 'CHARGES_AMOUNT'AND
                   G_charges_amount IS NULL ) THEN
               G_charges_amount := Get_Formatted_String(C_rec.column1);
           END IF;

  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('C_rec1.column_name = '||C_rec1.column_name||'.');
    cep_standard.debug('G_CUSTOMER_TEXT = '||G_CUSTOMER_TEXT||'.');
    cep_standard.debug('G_invoice_text = '||G_invoice_text ||'.');
    cep_standard.debug('G_BANK_TRX_NUMBER = '||G_BANK_TRX_NUMBER ||'.');
    cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
    cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
    cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
    cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
    cep_standard.debug('G_exchange_rate = '||G_exchange_rate ||'.');
    cep_standard.debug('G_original_amount = '||G_original_amount ||'.');
    cep_standard.debug('G_charges_amount = '||G_charges_amount||'.');
  END IF;

           UPDATE  ce_statement_lines_interface
           SET   CUSTOMER_TEXT         = G_CUSTOMER_TEXT,
  invoice_text            =  G_BANK_TRX_NUMBER,
  BANK_TRX_NUMBER           =G_invoice_text,
                   bank_account_text       = G_bank_account_text,
  currency_code           = G_line_currency_code,
  user_exchange_rate_type = G_user_exchange_rate_type,
  exchange_rate_date      = G_exchange_rate_date,
  exchange_rate           = G_exchange_rate,
          original_amount         = G_original_amount,
  charges_amount          = G_charges_amount
           WHERE   bank_account_num   = G_bank_account_num
           AND     statement_number   = G_statement_number
    AND  line_number        = l_line_cnt;
         END LOOP;
        END IF; /* 2643505 Added */
      END IF;
    ELSIF C_rec.rec_id_no = '49' THEN
      l_rec_cnt := l_rec_cnt + 1;
      l_line_cnt := 0;

      IF (nvl(to_number(C_rec.col2), l_rec_cnt) <> l_rec_cnt) THEN
        FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISSING_RECORD');
        FND_MESSAGE.set_token('GIVEN', C_rec.col2);
        FND_MESSAGE.set_token('COUNTED', to_char(l_rec_cnt));
        CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get);
      END IF;

      G_statement_date := nvl(G_statement_date, l_statement_date);
      /* 2643505 Added the IF Condition */
      IF l_process_this_record THEN
      Insert_Hdr;
      END IF;
      l_process_this_record := TRUE;
      /*2643505 End Code Added */
      Init_Hdr_Rec;
      l_control_total_cr := 0;
      l_control_total_dr := 0;
    ELSIF C_rec.rec_id_no NOT IN ('98', '99') THEN
      FND_MESSAGE.set_name('CE', 'CE_INVALID_BAI2');
      CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
      EXIT;
    END IF;

  END LOOP;
  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Load_BAI');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_err := SQLCODE;
    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Load_BAI - '|| to_char(l_err) ||
' Rec no - '|| nvl(G_rec_no, 0));
    END IF;
    RAISE;
END Load_BAI2;

PROCEDURE Decode_Hdr_Other(X_rec_no NUMBER,
    X_column_name VARCHAR2) IS
  l_err NUMBER;
BEGIN
  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Hdr_Other');
  END IF;

  IF X_column_name = 'STATEMENT_NUMBER' THEN
    G_statement_number := Lookup_Val('H', X_rec_no, X_column_name);
  ELSIF X_column_name = 'BANK_ACCOUNT_NUM' THEN
    G_bank_account_num := Lookup_Val('H', X_rec_no, X_column_name);
  ELSIF X_column_name = 'STATEMENT_DATE' THEN
    IF G_statement_date IS NULL THEN
     G_statement_date := CONV_TO_DATE(Lookup_Val('H', X_rec_no, X_column_name));
    END IF;
  ELSIF X_column_name = 'STATEMENT_TIMESTAMP' THEN
    G_statement_date   := CONV_TIMESTAMP( Lookup_Val('H', X_rec_no, 'STATEMENT_DATE') || ' ' || lookup_val('H', X_rec_no, 'STATEMENT_TIMESTAMP'));
  ELSIF X_column_name = 'BANK_NAME' THEN
    IF G_bank_name IS NULL THEN
      G_bank_name := Lookup_Val('H', X_rec_no, X_column_name);
    END IF;
  ELSIF X_column_name = 'BANK_BRANCH_NAME' THEN
    IF G_bank_branch_name IS NULL THEN
      G_bank_branch_name := Lookup_Val('H', X_rec_no, X_column_name);
    END IF;
  ELSIF X_column_name = 'CONTROL_BEGIN_BALANCE' THEN
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
    END IF;
    -- EDifact ER To Handle Non Numeric Values at Balances
    IF( G_format_type='EDIFACT_FR') THEN
      G_control_begin_balance := fnd_number.canonical_to_number(covert_amt_edifact(Lookup_Val('H', X_rec_no, X_column_name)))/ G_hdr_precision;
    ELSE
      G_control_begin_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
    END IF;
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('Decode_Hdr_Other: ' || 'G_control_begin_balance: ' || to_char(G_control_begin_balance));
    END IF;
  ELSIF X_column_name = 'CONTROL_END_BALANCE' THEN
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
    END IF;
    -- EDifact ER To Handle Non Numeric Values at Balances
    IF( G_format_type='EDIFACT_FR') THEN
      G_control_end_balance := fnd_number.canonical_to_number(covert_amt_edifact(Lookup_Val('H', X_rec_no, X_column_name)))/ G_hdr_precision;
    ELSE
      G_control_end_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
    END IF;
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('Decode_Hdr_Other: ' || 'G_control_end_balance: ' || to_char(G_control_end_balance));
    END IF;
  ELSIF X_column_name = 'AVAILABLE_BALANCE' THEN
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
    END IF;
    G_cashflow_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
  ELSIF X_column_name = 'VALUE_DATED_BALANCE' THEN
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
    END IF;
    G_int_calc_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
  ELSIF X_column_name = 'ONE_DAY_FLOAT' THEN
    IF l_DEBUG in ('Y', 'C') THEN
cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
    END IF;
    G_one_day_float := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
  ELSIF X_column_name = 'TWO_DAY_FLOAT' THEN
    IF l_DEBUG in ('Y', 'C') THEN
cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
    END IF;
    G_two_day_float := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
  ELSIF X_column_name = 'CONTROL_TOTAL_DR' THEN
    G_control_total_dr := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
  ELSIF X_column_name = 'CONTROL_TOTAL_CR' THEN
    G_control_total_cr := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
  ELSIF X_column_name = 'CONTROL_DR_LINE_COUNT' THEN
    G_control_dr_line_count := to_number(Lookup_Val('H', X_rec_no, X_column_name));
  ELSIF X_column_name = 'CONTROL_CR_LINE_COUNT' THEN
    G_control_cr_line_count := to_number(Lookup_Val('H', X_rec_no, X_column_name));
  ELSIF X_column_name = 'CONTROL_LINE_COUNT' THEN
    G_control_line_count := to_number(Lookup_Val('H', X_rec_no, X_column_name));
  ELSIF X_column_name = 'CURRENCY_CODE' THEN
    G_hdr_currency_code := Lookup_Val('H', X_rec_no, X_column_name);
  ELSIF X_column_name = 'CHECK_DIGITS' THEN
    G_check_digits := Lookup_Val('H', X_rec_no, X_column_name);
  END IF;

  -- Added for p2p
  IF G_gl_date_source = 'STATEMENT' THEN
    G_gl_date := G_statement_date;
  END IF;

  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Decode_Hdr_Other');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_err := SQLCODE;
    FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
    FND_MESSAGE.set_token('ERR', to_char(l_err));
    CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Hdr_Other - '|| to_char(l_err));
    END IF;
END Decode_Hdr_Other;

PROCEDURE Decode_Line_Other(X_rec_no NUMBER,
       X_column_name VARCHAR2) IS
  l_err NUMBER;
  REQ_FIELD_MISSING EXCEPTION;
BEGIN
  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Line_Other');
  END IF;

  IF X_column_name = 'LINE_NUMBER' THEN
    G_line_number := NVL(to_number(Lookup_Val('L', X_rec_no, X_column_name)), G_line_number);
  ELSIF X_column_name = 'TRX_DATE' THEN
    G_trx_date := NVL(CONV_TO_DATE(Lookup_Val('L', X_rec_no, X_column_name)), G_statement_date);
  ELSIF X_column_name = 'TRX_CODE' THEN
    G_trx_code := Lookup_Val('L', X_rec_no, X_column_name);
  ELSIF X_column_name = 'EFFECTIVE_DATE' THEN
    G_effective_date := CONV_TO_DATE(Lookup_Val('L', X_rec_no, X_column_name));
  ELSIF X_column_name = 'TRX_TEXT' THEN
    G_trx_text := Lookup_Val('L', X_rec_no, X_column_name);
  ELSIF X_column_name = 'INVOICE_TEXT' THEN
    --Edifact ER To Extract the Invoice text from the immediate 05 record
    IF( G_format_type='EDIFACT_FR' AND G_rec_id='05' AND G_prev_rec_id='04' ) THEN
       G_invoice_text := Lookup_Val('L', X_rec_no, X_column_name);
    ELSIF (G_format_type <> 'EDIFACT_FR' ) THEN
       G_invoice_text := Lookup_Val('L', X_rec_no, X_column_name);
    END IF;
    IF l_DEBUG in ('Y', 'C') THEN
   cep_standard.debug('Decode_Line_Other: ' || 'G_invoice_text: ' || to_char(G_invoice_text));
    END IF;
  ELSIF X_column_name = 'AMOUNT' THEN
    --Edifact ER To handle the Non Numeric characters at Edifact Line Amounts
    IF( G_format_type='EDIFACT_FR') THEN
       G_amount := fnd_number.canonical_to_number(Abs(covert_amt_edifact(Lookup_Val('L', X_rec_no, X_column_name))))/ G_line_precision;
    ELSE
       G_amount := fnd_number.canonical_to_number(Lookup_Val('L', X_rec_no, X_column_name))/ G_line_precision;
    END IF;
    IF l_DEBUG in ('Y', 'C') THEN
   cep_standard.debug('Decode_Line_Other: ' || 'G_amount: ' || to_char(G_amount));
    END IF;
  ELSIF X_column_name = 'CURRENCY_CODE' THEN
    G_line_currency_code := Lookup_Val('L', X_rec_no, X_column_name);
  ELSIF X_column_name = 'EXCHANGE_RATE' THEN
    G_exchange_rate := fnd_number.canonical_to_number(Lookup_Val('L', X_rec_no, X_column_name));
  ELSIF X_column_name = 'CUSTOMER_TEXT' THEN
    G_CUSTOMER_TEXT := Lookup_Val('L', X_rec_no, X_column_name);
  ELSIF X_column_name = 'BANK_TRX_NUMBER' THEN
    G_BANK_TRX_NUMBER := Lookup_Val('L', X_rec_no, X_column_name);
  ELSIF X_column_name = 'USER_EXCHANGE_RATE_TYPE' THEN
    G_user_exchange_rate_type := Lookup_Val('L', X_rec_no, X_column_name);
  ELSIF X_column_name = 'EXCHANGE_RATE_DATE' THEN
    G_exchange_rate_date := CONV_TO_DATE(Lookup_Val('L', X_rec_no, X_column_name));
  ELSIF X_column_name = 'ORIGINAL_AMOUNT' THEN
    G_original_amount := fnd_number.canonical_to_number(Lookup_Val('L', X_rec_no, X_column_name)) / G_line_precision;
  ELSIF X_column_name = 'CHARGES_AMOUNT' THEN
    G_charges_amount := fnd_number.canonical_to_number(Lookup_Val('L', X_rec_no, X_column_name)) / G_line_precision;
  ELSIF X_column_name = 'BANK_ACCOUNT_TEXT' THEN
    G_bank_account_text := Lookup_Val('L', X_rec_no, X_column_name);
  END IF;
  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Decode_Line_Other');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    l_err := SQLCODE;
    FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
    FND_MESSAGE.set_token('ERR', to_char(l_err));
    CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
    IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Line_Other - '||to_char(l_err));
    END IF;
END Decode_Line_Other;

FUNCTION Get_Main_Rec_ID (X_hdr_or_line VARCHAR2) RETURN VARCHAR2 IS
  CURSOR C_line_rec_id IS
    SELECT distinct(rec_id_no) recID
    FROM   ce_bank_stmt_map_line
    WHERE  map_id = G_map_id;

  CURSOR C_hdr_rec_id IS
    SELECT distinct(rec_id_no) recID
    FROM   ce_bank_stmt_map_hdr
    WHERE  map_id = G_map_id;

  l_lowest_rec_no NUMBER := 10000000;
  l_min_rec_no   NUMBER;
  l_rec_id VARCHAR2(30);
  l_err NUMBER;
BEGIN
  IF X_hdr_or_Line = 'L' THEN
    FOR C_rec IN C_line_rec_id LOOP
      SELECT MIN(rec_no)
      INTO   l_min_rec_no
      FROM   ce_stmt_int_tmp
      WHERE  rec_id_no = C_rec.recID;

      IF l_lowest_rec_no > l_min_rec_no THEN
        l_lowest_rec_no := l_min_rec_no;
        l_rec_id := C_rec.recID;
      END IF;
    END LOOP;
  ELSE
    FOR C_rec IN C_hdr_rec_id LOOP
      SELECT MIN(rec_no)
      INTO   l_min_rec_no
      FROM   ce_stmt_int_tmp
      WHERE  rec_id_no = C_rec.recID;

      IF l_lowest_rec_no > l_min_rec_no THEN
        l_lowest_rec_no := l_min_rec_no;
        l_rec_id := C_rec.recID;
      END IF;
    END LOOP;
  END IF;

  RETURN (l_rec_id);
EXCEPTION
  WHEN OTHERS THEN
    l_err := SQLCODE;
    IF X_hdr_or_Line = 'L' THEN
      IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('Get_Main_Rec_ID: ' || 'EXCEPTION: Fail finding the record id of major line which contains
    the main line information - '|| to_char(l_err) ||
' Rec no - '|| nvl(G_rec_no, 0));
      END IF;
    ELSE
      IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('Get_Main_Rec_ID: ' || 'EXCEPTION: Fail finding the record id of major line which contains
    the main header information - ' || to_char(l_err) ||
' Rec no - '|| nvl(G_rec_no, 0));
      END IF;
    END IF;
    RAISE;
END Get_Main_Rec_ID;

PROCEDURE Load_Others IS
  CURSOR C_tmp_tbl IS
    SELECT rec_no, rec_id_no
    FROM   CE_STMT_INT_TMP
    ORDER BY rec_no;

  CURSOR C_hdr_tbl(p_rec_id VARCHAR2) IS
    SELECT column_name, position
    FROM   CE_BANK_STMT_MAP_HDR
    WHERE  map_id = G_map_id
    AND    rec_id_no = p_rec_id;

  CURSOR C_line_tbl(p_rec_id VARCHAR2) IS
    SELECT column_name, position
    FROM   CE_BANK_STMT_MAP_LINE
    WHERE  map_id = G_map_id
    AND    rec_id_no = p_rec_id;

  l_hdr_flag NUMBER := 0;
  l_line_flag NUMBER := 0;
  l_line_num NUMBER;

  l_line_rec_id VARCHAR2(30);
  l_hdr_rec_id VARCHAR2(30);

  l_hdr_or_line VARCHAR2(1);
  l_old_rec_type VARCHAR2(1) := 'H';
  l_err NUMBER;
  l_process_this_record BOOLEAN := TRUE; /* 2831725 */

  INVALID_ACCOUNT EXCEPTION;
BEGIN
  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_Others');
  END IF;

  l_line_rec_id := Get_Main_Rec_ID('L');
  l_hdr_rec_id  := Get_Main_Rec_ID('H');

  FOR C_rec IN C_tmp_tbl LOOP
     G_rec_no := C_rec.rec_no;

          --Edifact ER Validations are included for EDIFACT
     IF (G_format_type='EDIFACT_FR') THEN
          G_rec_id := C_rec.rec_id_no;
          -- If the file not started with record id '01' discard the file processing
          IF (G_rec_no=1 AND G_rec_id <> '01' ) THEN
              FND_MESSAGE.set_name('CE', 'CE_INVALID_EDIFACT');
              CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
              IF l_DEBUG in ('Y', 'C') THEN
                cep_standard.debug('Load_Others: The File Not started with 01 Record id');
              END IF;
              EXIT;
          -- If the file has orphan '05' discard the file processing
          ELSIF ( G_rec_id = '05' AND G_prev_rec_id NOT IN ('05','04') )THEN
              FND_MESSAGE.set_name('CE', 'CE_INVALID_EDIFACT');
              CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
              IF l_DEBUG in ('Y', 'C') THEN
                cep_standard.debug('Load_Others: The File has 05 Record with out Parent 04 Record at Rec no: '||To_Char(G_rec_no));
              END IF;
              EXIT;
          END IF;
     END IF;
      --Edifact ER


     l_hdr_or_line := Hdr_Or_Line(C_rec.rec_id_no);

     IF l_hdr_or_line = 'H' THEN
       IF l_old_rec_type = 'L' THEN
/* 2831725 Added the IF Condition below */
IF (l_process_this_record) THEN
          Insert_Line;
END IF;
         Init_Line_Rec;
       END IF;
       l_old_rec_type := 'H';

       IF ( C_rec.rec_id_no = l_hdr_rec_id AND l_hdr_flag = 1 ) THEN
/* 2831725 Added the IF Condition below */
IF (l_process_this_record) THEN
          Insert_Hdr;
END IF;
      Init_Hdr_Rec;
         l_line_flag := 0;
       END IF;

       --
       -- Get precision first
       --
       G_hdr_precision := Get_Precision(to_number(Lookup_Val('H', G_rec_no, 'PRECISION')));
       IF ( G_hdr_precision = 1 AND G_precision <> 1 )THEN
         G_hdr_precision := G_precision;
       ELSIF ( G_hdr_precision <> 1 AND G_precision = 1 )THEN
        G_precision := G_hdr_precision;
       END IF;
       IF l_DEBUG in ('Y', 'C') THEN
         cep_standard.debug('G_precision: ' || to_char(G_precision));
         cep_standard.debug('G_hdr_precision: ' || to_char(G_hdr_precision));
       END IF;

       FOR C_hdr IN C_hdr_tbl(C_rec.rec_id_no) LOOP
         Decode_Hdr_Other(C_rec.rec_no, C_hdr.column_name);
/* 2831725 Code Added begins */
         IF ( C_hdr.column_name = 'BANK_ACCOUNT_NUM' ) THEN
          IF (NOT Valid_Bank_Account ) THEN
l_process_this_record := FALSE;
ELSE
l_process_this_record := TRUE;
END IF;
/*RAISE INVALID_ACCOUNT;*/
         END IF;
/* 2831725 Added Code Ends */
         IF C_rec.rec_id_no = l_hdr_rec_id THEN
           l_hdr_flag := 1;
         END IF;
       END LOOP;
       l_line_num := 1;
     ELSIF l_hdr_or_line = 'L' THEN
       l_old_rec_type := 'L';
       IF ( C_rec.rec_id_no = l_line_rec_id AND l_line_flag = 1 ) THEN
/* 2831725 Added the IF Condition below */
IF (l_process_this_record) THEN
            Insert_Line;
END IF;
        Init_Line_Rec;
       END IF;

       --
       -- Get precision first
       --
       G_line_precision := Get_Precision(to_number(Lookup_Val('L', G_rec_no, 'PRECISION')));
       IF ( G_line_precision = 1 AND G_precision <> 1 )THEN
         G_line_precision := G_precision;
       END IF;

       FOR C_line IN C_line_tbl(C_rec.rec_id_no) LOOP
         Decode_Line_Other(C_rec.rec_no, C_line.column_name);
       END LOOP;

       IF C_rec.rec_id_no = l_line_rec_id THEN
         IF G_line_number IS NULL THEN
            G_line_number := l_line_num;
END IF;
         l_line_flag := 1;
         l_line_num := l_line_num + 1;
       END IF;
     ELSIF l_hdr_or_line = 'B' THEN
        IF l_DEBUG in ('Y', 'C') THEN
   cep_standard.debug('This rec_id '|| C_rec.rec_id_no ||' is assigned for both header and line');
        END IF;
        FND_MESSAGE.set_name('CE', 'CE_RECID_IN_HDR_LINE');
        FND_MESSAGE.set_token('RECID', C_rec.rec_id_no);
  CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, G_rec_no, fnd_message.get, 'E');
        EXIT;
     END IF;
      IF (G_format_type='EDIFACT_FR') THEN  --8835022 start
          G_prev_rec_id := C_rec.rec_id_no; --Edifact ER Previous Record Id
          IF l_DEBUG in ('Y', 'C') THEN
         cep_standard.debug('G_prev_rec_id: '|| G_prev_rec_id);
          END IF;
      END IF; --8835022 End
   END LOOP;

   --
   -- Insert any remain line or header record
   --
   IF G_amount IS NOT NULL THEN
/* 2831725 Added the IF Condition below */
IF (l_process_this_record) THEN
      Insert_Line;
END IF;
   END IF;

   IF G_statement_date IS NOT NULL THEN
/* 2831725 Added the IF Condition below */
IF (l_process_this_record) THEN
      Insert_Hdr;
END IF;
   END IF;
  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Load_Others');
  END IF;
EXCEPTION
  WHEN INVALID_ACCOUNT THEN
    null;
  WHEN OTHERS THEN
    l_err := SQLCODE;
    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Load_Others - '||to_char(l_err)||
'Rec no - '|| nvl(G_rec_no, 0));
    END IF;
    RAISE;
END Load_Others;

PROCEDURE Decode_Line_SWIFT IS
  CURSOR C_tmp_tbl IS
  SELECT rec_no,
column35
  FROM   CE_STMT_INT_TMP
  WHERE  rec_id_no = '61'
  ORDER BY rec_no;

  l_col2 VARCHAR2(255);
  l_col3 VARCHAR2(255);
  l_col4 VARCHAR2(255);
  l_col5 VARCHAR2(255);
  l_col6 VARCHAR2(255);
  l_col7 VARCHAR2(255);
  l_col8 VARCHAR2(255);

  l_tmp VARCHAR2(255);
  l_str VARCHAR2(255);
  l_cd VARCHAR2(1);

  l_pos NUMBER;
  l_pos1 NUMBER;
  l_pos2 NUMBER;
  l_pos3 NUMBER;
BEGIN
  FOR C_rec IN C_tmp_tbl LOOP
    l_str  := C_rec.column35;

    -- Get Entry Date
    l_tmp := SUBSTR(l_str, 1, 4);
    IF Is_Number(l_tmp) THEN
      l_col2 := l_tmp;
      l_str := SUBSTR(l_str, 5);
    ELSE
      l_col2 := NULL;
    END IF;

    -- Get Debit/Credit Mark
    l_tmp := SUBSTR(l_str, 1, 1);
    IF l_tmp = 'R' THEN
      l_col3 := SUBSTR(l_str, 1, 2);
      l_str := SUBSTR(l_str, 3);
      IF SUBSTR(l_col3, 2, 1) = 'C' THEN
        l_cd := 'D';
      ELSE
        l_cd := 'C';
      END IF;
    ELSE
      l_col3 := l_tmp;
      l_str := SUBSTR(l_str, 2);
      l_cd := l_tmp;
    END IF;

    -- Get Fund Code
    l_tmp := SUBSTR(l_str, 1, 1);
    IF NOT Is_Number(l_tmp) THEN
      l_col4 := l_tmp;
      l_str := SUBSTR(l_str, 2);
    ELSE
      l_col4 := NULL;
    END IF;

    -- Get Amount
    /*Bug 4127039*/

    l_pos1 := INSTR(l_str,'N');
    l_pos2 := INSTR(l_str,'S');
    l_pos3 := INSTR(l_str,'F');

    IF l_pos1 = 0 THEN l_pos1 := 9999999; END IF;
    IF l_pos2 = 0 THEN l_pos2 := 9999999; END IF;
    IF l_pos3 = 0 THEN l_pos3 := 9999999; END IF;

    l_pos := LEAST(l_pos1,l_pos2,l_pos3);

    l_col5 := replace(SUBSTR(l_str, 1, l_pos - 1), ',', '.');

--    IF l_col3 IN ('D', 'RC') THEN
--      l_col5 := '-' || l_col5;
--    END IF;
    l_str := SUBSTR(l_str, l_pos + 1);

    -- Get Trx Codes
    l_col6 := SUBSTR(l_str, 1, 3) || l_cd;
    l_str  := SUBSTR(l_str, 4);

    -- Get Reference to Account Owner and Accounting Service Institution's Reference
    l_pos := INSTR(l_str, '//');
    IF l_pos = 0 THEN
      l_col7 := l_str;
      l_col8 := NULL;
    ELSE
      l_col7 :=  SUBSTR(l_str, 1, l_pos - 1);
      l_col8 :=  SUBSTR(l_str, l_pos + 2);
    END IF;

    UPDATE CE_STMT_INT_TMP
    SET   column2 = l_col2,
column3 = l_col3,
column4 = l_col4,
column5 = l_col5,
column6 = l_col6,
column7 = l_col7,
column8 = l_col8
    WHERE rec_no = C_rec.rec_no;
  END LOOP;
END Decode_Line_SWIFT;

PROCEDURE Decode_Description IS
  CURSOR C_tmp_tbl IS
  SELECT rec_no,
         column1
  FROM   CE_STMT_INT_TMP
  WHERE  rec_id_no = '9'
  ORDER BY rec_no;

  CURSOR C_tmp_tbl2 IS
  SELECT rec_no,
         column1
  FROM   CE_STMT_INT_TMP
  WHERE  rec_id_no = '86'
  ORDER BY rec_no;

  l_rec_no NUMBER;
  l_rec_id VARCHAR2(30);
BEGIN
  FOR C_rec IN C_tmp_tbl LOOP
    SELECT MAX(rec_no)
    INTO l_rec_no
    FROM     CE_STMT_INT_TMP
    WHERE rec_no < C_rec.rec_no
    AND         rec_id_no <> '9';

  /* Bug 4041064 added the following IF */
  IF l_rec_no IS NOT NULL THEN
    SELECT rec_id_no
    INTO l_rec_id
    FROM CE_STMT_INT_TMP
    WHERE       rec_no = l_rec_no;

    IF l_rec_id = '61' THEN
      UPDATE CE_STMT_INT_TMP
      SET rec_id_no = '61A'
      WHERE     rec_no = C_rec.rec_no;
    ELSIF l_rec_id = '86' THEN
      UPDATE CE_STMT_INT_TMP
      SET column1 = column1 || '   ' || C_rec.column1
      WHERE     rec_no = l_rec_no;

      DELETE FROM CE_STMT_INT_TMP
      WHERE  rec_no = C_rec.rec_no;
    END IF;
  END IF; --4041064
  END LOOP;

  FOR C_rec IN C_tmp_tbl2 LOOP
    SELECT rec_id_no
    INTO l_rec_id
    FROM CE_STMT_INT_TMP
    WHERE       rec_no = C_rec.rec_no - 1;

    IF l_rec_id = '61' THEN
      UPDATE CE_STMT_INT_TMP         -- This 86 record comes after 61.
      SET rec_id_no = '61A'         -- Marks it as 61A.
      WHERE     rec_no = C_rec.rec_no;
    ELSIF l_rec_id = '61A' THEN
      UPDATE CE_STMT_INT_TMP -- Comes after supplementary details (61A).
      SET column1 = column1 || '   '      -- Concatinate 86 to 61A.
 || C_rec.column1
      WHERE     rec_no = C_rec.rec_no - 1;

      DELETE FROM CE_STMT_INT_TMP -- Delete 86.
      WHERE  rec_no = C_rec.rec_no;
    END IF;
  END LOOP;

END Decode_Description;

PROCEDURE Load_SWIFT940 IS
BEGIN
  Decode_Line_SWIFT;
  Decode_Description;
  Load_Others;
END Load_SWIFT940;

PROCEDURE Load_Bank_Statement(errbuf OUT NOCOPY VARCHAR2,
     retcode OUT NOCOPY NUMBER,
     X_MAP_ID NUMBER,
     X_REQUEST_ID NUMBER,
     X_data_file VARCHAR2,
       X_process_option VARCHAR2,
       X_gl_date VARCHAR2,
       X_org_id VARCHAR2,
       X_receivables_trx_id NUMBER,
       X_payment_method_id NUMBER,
       X_nsf_handling VARCHAR2,
       X_display_debug VARCHAR2,
       X_debug_path VARCHAR2,
       X_debug_file VARCHAR2,
       X_bank_branch_id NUMBER,
       X_bank_account_id NUMBER,
     X_intra_day_flag VARCHAR2 DEFAULT 'N',
                              X_gl_date_source          VARCHAR2 DEFAULT NULL) IS
  l_return BOOLEAN;

  l_phase VARCHAR2(30);
  l_status VARCHAR2(30);
  l_dev_phase VARCHAR2(30);
  l_dev_status VARCHAR2(30);
  l_message VARCHAR2(1000);

  l_cnt NUMBER;
  l_err NUMBER;
  l_format_type VARCHAR2(30);
  l_precision        NUMBER;
  l_request_id NUMBER := X_REQUEST_ID;
  SQL_LOADER_ERROR EXCEPTION;
  INVALID_DATA_FILE EXCEPTION;
BEGIN

  G_data_file_name    :=  X_data_file;
  G_process_option    :=  X_process_option;
  G_gl_date        := to_date(X_gl_date,'YYYY/MM/DD HH24:MI:SS');
  G_intra_day_flag    :=  X_intra_day_flag;
  -- modified for p2p
  G_gl_date_source          :=  X_gl_date_source;

  G_receivables_trx_id      := X_receivables_trx_id;
  G_payment_method_id    := X_payment_method_id;
  G_nsf_handling    := X_nsf_handling;
  G_display_debug    := X_display_debug;
  G_debug_path        := X_debug_path;
  G_debug_file        := X_debug_file;
  G_bank_branch_id    := X_bank_branch_id;
  G_bank_account_id    := X_bank_account_id;
  G_org_id      := X_org_id;

  G_map_id := X_MAP_ID;
  G_user_id := FND_GLOBAL.user_id;

  IF (G_gl_date_source = 'USER') THEN
    G_gl_date :=  to_date(X_gl_date,'YYYY/MM/DD HH24:MI:SS');
  ELSIF (G_gl_date_source = 'SYSTEM') THEN
    G_gl_date :=  SYSDATE;
  END IF;

  IF l_DEBUG in ('Y', 'C') THEN
cep_standard.enable_debug(G_debug_path,
     G_debug_file || '-LBS');
  cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_Bank_Statement');
  END IF;
  --
  -- Wait until SQL*Loader program is completed.
  --
  IF (l_request_id IS NOT NULL) THEN
    LOOP
      l_return := FND_CONCURRENT.wait_for_request(
l_request_id,
10,
300,
l_phase,
l_status,
l_dev_phase,
l_dev_status,
l_message);

      l_return := FND_CONCURRENT.get_request_status(
l_request_id,
NULL,
NULL,
l_phase,
l_status,
l_dev_phase,
l_dev_status,
l_message);

      IF (NVL(l_dev_phase,'ERROR') = 'COMPLETE') THEN
        EXIT;
      END IF;
    END LOOP;

    IF (NVL(l_dev_status, 'ERROR') NOT IN ('NORMAL', 'WARNING')) THEN
      RAISE SQL_LOADER_ERROR;
    END IF;
  END IF;

  --
  -- Check if the CE_STAT_INT_TMP was populated by SQL*Loader.
  --
  SELECT count(*)
  INTO   l_cnt
  FROM   CE_STMT_INT_TMP;
  IF l_cnt = 0 THEN
    RAISE INVALID_DATA_FILE;
  END IF;

  Remove_Return_Char;

  SELECT format_type,
precision,
date_format,
timestamp_format
  INTO   G_format_type,
l_precision,
G_date_format,
G_timestamp_format
  FROM   ce_bank_stmt_int_map_v
  WHERE   map_id = X_MAP_ID;

  --
  -- assign devision factor depending on the precision.
  --
  G_precision := Get_Precision(nvl(l_precision, 0));

  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('Load_Bank_Statement: ' || 'Format Type:'||l_format_type);
    cep_standard.debug('>>G_format_type= '||G_format_type);
  END IF;


  IF G_format_type = 'BAI2' THEN
    IF l_DEBUG in ('Y', 'C') THEN
      cep_standard.debug('>>call Load_BAI2');
    END IF;
    Load_BAI2;
  ELSIF G_format_type = 'SWIFT940' THEN
    IF l_DEBUG in ('Y', 'C') THEN
     cep_standard.debug('>>call Load_SWIFT940');
    END IF;
   Load_SWIFT940;
  ELSE
    IF l_DEBUG in ('Y', 'C') THEN
     cep_standard.debug('>>call Load_Others');
    END IF;
   Load_Others;
  END IF;
  Delete_Orphaned_Lines;

  IF ( nvl(G_total_hdr_deleted, 0) <> 0 ) THEN
    FND_MESSAGE.set_name('CE', 'CE_EXIST_HDR_DELETED');
    FND_MESSAGE.set_token('CNT', to_char(G_total_hdr_deleted));
    CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get);
  END IF;
  IF ( nvl(G_total_line_deleted, 0) <> 0 ) THEN
    FND_MESSAGE.set_name('CE', 'CE_EXIST_LINE_DELETED');
    FND_MESSAGE.set_token('CNT', to_char(G_total_line_deleted));
    CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get);
  END IF;

  CE_BANK_STMT_SQL_LDR.Print_Report(G_map_id, G_data_file_name);

  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Load_Bank_Statement');
  END IF;
  IF G_display_debug = 'Y' THEN
    cep_standard.disable_debug(G_display_debug);
  END IF;
EXCEPTION
WHEN SQL_LOADER_ERROR THEN
  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('EXCEPTION: Load_Bank_Statement - Sql*Loader was not finished');
  END IF;
  FND_MESSAGE.set_name('CE', 'CE_SQLLOADER_FAIL');
  CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get, 'E');
  CE_BANK_STMT_SQL_LDR.Print_Report(G_map_id, G_data_file_name);
WHEN INVALID_DATA_FILE THEN
  FND_MESSAGE.set_name('CE', 'CE_INVALID_DATA_FILE');
  FND_MESSAGE.set_token('DATA_FILE',G_data_file_name);
  CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get, 'E');
  CE_BANK_STMT_SQL_LDR.Print_Report(G_map_id, G_data_file_name);
WHEN OTHERS THEN
  l_err     := SQLCODE;
  IF l_DEBUG in ('Y', 'C') THEN
  cep_standard.debug('EXCEPTION: Load_Bank_Statement - '||to_char(l_err));
  END IF;
  RAISE;
END Load_Bank_Statement;

END CE_BANK_STATEMENT_LOADER;

select * from all_errors where name='CE_BANK_STATEMENT_LOADER'