Bank Statement To Wire

Bank Statement To Wire

Overview

·       
Bank Statement is uploaded automatically from the bank file
·       
Based upon the information provided by bank, system should recognize
the customers and invoice numbers
·       
Manual Applications for the unidentified receipts to customers
wherever invoice numbers not found
·       
Report to review the wire receipts collected by bank and its status
of applcations
·       
Automatic Bank Reconciliation for the wire receipts

 
Process Flow









For the purposes of Bank Statement to Wire
process, the process flow is as under:
a.      
Creation of Lookups to identify the invoice numbers by the system as
provided by the bank
The lookups will tell the system on the invoice series starting
numbers and the length in order to identify the invoice numbers
b.      
MT940 Bank Statement Mapping to upload the Bank statements into
Oracle
Standard Swift940 Template being modified for this purpose. In case
Bank provided the receipt number, same to be mapped; in case not, the column is
left blank to avoid NONREF reference.
c.       
Define conditions in the based upon which Receipts will be eligible
for creation
All Bank Statement Credit lines with Transaction codes for TRFC and
MSCC with text as ‘SEPA Credit Transfer’ OR ‘SEPA-Gutschrift’ will be eligible
for pickup
This will be picked up and generated through Lockbox. Program will
be ‘XXEUR Bank Statement to Wire Receipts’
d.      
Define rules for invoice identification
The text in between “?21’ AND ‘?21’ for the bank statement line is
eligible for invoice verification by comparison with the lookup in step (a). An
exception is where either ?21 OR ?20 is not provided by bank, whereby entire
text is considered for invoice identification.
In case more than one invoice is available, all of these invoices
will be applied automatically upto the outstanding amount less discount
In case more than one invoice is identified, but pertaining to
different customers, the receipt will be unidentified
In case no invoice is recognized, this will again be unidentified
In case of Receipt Amount less than invoice outstanding, the balance
will be unapplied.
All the unidentified and unapplied receipts to be taken care
manually
A report will be available (XXEUR Bank Statement to Wire Receipt
Report) for the information on the receipts creation and it’s application from
Bank Statement to Wire Receipt Process
 


e.       
Define Rules for Autoreconciliation by Bank for Wire Receipts
For cases where bank statement line already provided the receipt
number, this will be used as the receipt number during creation of receipt.
Script
CREATE OR REPLACE PACKAGE APPS.xxeur_ge_bs_to_wire
AS
PROCEDURE main(errbuf                 OUT VARCHAR2
              ,retcode                OUT NUMBER
              –,p_transmission_id      IN  NUMBER
              ,P_STATEMENT_NUMBER     IN  VARCHAR2
              ,P_ACCOUNT              IN  NUMBER
              ,P_FROM_DATE            IN  VARCHAR2
              ,P_TO_DATE              IN  VARCHAR2
              );

PROCEDURE insert_lockbox_rec (p_record_type     IN VARCHAR2
                             ,p_batch_amount    IN NUMBER
                             ,p_batch_cnt       IN NUMBER
                             ,p_receipt_number  IN VARCHAR2
                             ,p_customer_number IN VARCHAR2
                             ,p_invoice1        IN VARCHAR2
                             ,p_invoice2        IN VARCHAR2
                             ,p_invoice3        IN VARCHAR2
                             ,p_invoice4        IN VARCHAR2
                             ,p_invoice5        IN VARCHAR2
                             ,p_invoice6        IN VARCHAR2
                             ,p_invoice7        IN VARCHAR2
                             ,p_invoice8        IN VARCHAR2
                             ,p_amount_applied1 IN NUMBER
                             ,p_amount_applied2 IN NUMBER
                             ,p_amount_applied3 IN NUMBER
                             ,p_amount_applied4 IN NUMBER
                             ,p_amount_applied5 IN NUMBER
                             ,p_amount_applied6 IN NUMBER
                             ,p_amount_applied7 IN NUMBER
                             ,p_amount_applied8 IN NUMBER                                                                                                                  
                             ,p_customer_id     IN NUMBER                   
                             ,p_site_use_id     IN NUMBER
                             ,p_transmission_id IN NUMBER
                             ,p_item_number     IN NUMBER
                             ,p_overflow_seq    IN NUMBER
                             ,p_account_num     IN VARCHAR2
                             ,p_gl_date         IN DATE
                             ,p_comments        IN VARCHAR2
                             ,p_remittance_amt  IN NUMBER
                             ,p_batch_name      IN VARCHAR2
                             ,p_trx_date        IN DATE
                             ,p_statement_number IN VARCHAR2
                             ,p_line_number     IN VARCHAR2
                             );
PROCEDURE insert_custom_tb(p_record_type     IN VARCHAR2
                             ,p_batch_amount    IN NUMBER
                             ,p_batch_cnt       IN NUMBER
                             ,p_receipt_number  IN VARCHAR2
                             ,p_customer_number IN VARCHAR2
                             ,p_invoice         IN VARCHAR2                            
                             ,p_customer_id     IN NUMBER                   
                             ,p_site_use_id     IN NUMBER
                             ,p_transmission_id IN NUMBER
                             ,p_item_number     IN NUMBER
                             ,p_overflow_seq    IN NUMBER
                             ,p_account_num     IN VARCHAR2
                             ,p_gl_date         IN DATE
                             ,p_comments        IN VARCHAR2
                             ,p_remittance_amt  IN NUMBER
                             ,p_batch_name      IN VARCHAR2
                             ,p_trx_date        IN DATE
                             ,p_statement_number IN VARCHAR2
                             ,p_line_number     IN VARCHAR2
                             ,p_customer_trx_id IN NUMBER
                             );

FUNCTION get_primary_site_use (p_customer_id IN NUMBER
                              ,p_org_id      IN NUMBER)
RETURN NUMBER;                                   
END; 


CREATE OR REPLACE PACKAGE BODY APPS.xxeur_ge_bs_to_wire
AS
gn_org_id          NUMBER;
gn_transmission_id NUMBER;
gc_lockbox_num     VARCHAR2(30);
gn_batch_name      VARCHAR2(25);
PROCEDURE main (errbuf                 OUT VARCHAR2
               ,retcode                OUT NUMBER
               –,p_transmission_id      IN  NUMBER
               ,p_statement_number     IN  VARCHAR2
               ,p_account              IN  NUMBER
               ,p_from_date            IN  VARCHAR2
               ,p_to_date              IN  VARCHAR2              
               )
AS              
ln_customer_id          NUMBER;
ln_site_use_id          NUMBER;
ln_rec_count            NUMBER;
lc_invoice1             VARCHAR2(50);
lc_invoice2             VARCHAR2(50);
lc_invoice3             VARCHAR2(50);
lc_invoice4             VARCHAR2(50);
lc_invoice5             VARCHAR2(50);
lc_invoice6             VARCHAR2(50);
lc_invoice7             VARCHAR2(50);
lc_invoice8             VARCHAR2(50);
ln_amount_applied1      NUMBER;
ln_amount_applied2      NUMBER;
ln_amount_applied3      NUMBER;
ln_amount_applied4      NUMBER;
ln_amount_applied5      NUMBER;
ln_amount_applied6      NUMBER;
ln_amount_applied7      NUMBER;
ln_amount_applied8      NUMBER;
ln_org_id               NUMBER;
lc_receipt_number       VARCHAR2(30);
ln_item_number          NUMBER;
lc_bank_account         VARCHAR2(30);
ln_batch_amount         NUMBER;
ln_batch_count          NUMBER;
ln_bank_customer_id     NUMBER;
ln_bank_site_use_id     NUMBER;
ln_inv_customer_id      NUMBER;
ln_inv_site_use_id      NUMBER;
lc_diff_cust            VARCHAR2(1);
ln_cust_count           NUMBER;
lc_cust_num             VARCHAR2(30);
lc_org_name            VARCHAR2(100);
ln_customer_trx_id     NUMBER;
lc_cust_name           VARCHAR2(100);
lc_status              VARCHAR2(1);
lc_error_message       VARCHAR2(4000);
lc_transmission_name   VARCHAR2(30);

CURSOR lcu_bs_head
IS
SELECT *
  FROM ce_statement_headers
 WHERE 1=1
   AND statement_number = NVL(p_statement_number, statement_number) — (‘00153/1 – 07-AUG-15’) –(‘00153/1 – 07-AUG-15’) –‘00157/26 – 13-AUG-15T1’
   AND bank_account_id  = NVL(p_account, bank_account_id)
   AND statement_date BETWEEN NVL(TO_DATE(p_from_date,’DD-MON-YYYY’),statement_date)
                                                        AND NVL(TO_DATE(p_to_date,’DD-MON-YYYY’),statement_date)
   AND attribute1 IS NULL
   AND org_id = gn_org_id;

CURSOR lcu_bs_line (p_statement_header_id NUMBER)
IS
SELECT COUNT(a.trx_text) OVER (PARTITION BY a.STATEMENT_header_ID) AS batch_count,
       SUM(a.amount) OVER (PARTITION BY a.STATEMENT_header_ID) AS batch_amount,
       a.*
  FROM ce_statement_lines a
 WHERE statement_header_id = p_statement_header_id
   AND trx_code            IN (‘MSCC’,’TRFC’)
   –AND currency_code       = ‘EUR’  
   AND (trx_text LIKE ‘%SEPA Credit Transfer%’
        OR trx_text LIKE ‘%SEPA-Gutschrift%’)
   AND attribute1 IS NULL;

CURSOR lcu_inv_lookup(p_org_name VARCHAR)
IS
SELECT DISTINCT LOOKUP_CODE inv_prefix
      ,TAG inv_length
  FROM fnd_lookup_values
 WHERE lookup_type = ‘XXEUR_BANK_STATEMENT_TO_WIRE’
   AND DESCRIPTION = p_org_name
   AND language = USERENV(‘LANG’);
     
CURSOR lcu_inv_list (p_trx_text VARCHAR2, p_inv_prefix VARCHAR2, p_inv_length VARCHAR2)
IS
SELECT inv_num
  FROM (SELECT SUBSTR(p_trx_text,INSTR(p_trx_text,p_inv_prefix,1,LEVEL),p_inv_length) inv_num
         FROM DUAL
        CONNECT BY level <= REGEXP_COUNT(p_trx_text, p_inv_prefix, 1, ‘i’)
        )
 WHERE LENGTH(inv_num)                   = p_inv_length
   AND REGEXP_INSTR(inv_num,'[^0-9]+’) = 0;
     
CURSOR lcu_bank_acct (p_iban VARCHAR2)
IS
SELECT DISTINCT hca.account_number,hca.cust_account_id,COUNT(eb.iban) OVER (PARTITION BY eb.iban) AS cust_count
  FROM iby_external_payers_all ie
      ,iby_pmt_instr_uses_all ip
      ,iby_ext_bank_accounts eb
      ,hz_parties hp
      ,hz_cust_accounts hca
      ,hz_cust_site_uses_all hcu
      ,hz_cust_acct_sites_all hcas
 WHERE ip.ext_pmt_party_id    = ie.ext_payer_id
   AND eb.ext_bank_account_id = ip.instrument_id
   AND hp.party_id            = ie.party_id
   AND hca.cust_account_id    = hcas.cust_account_id
   AND hcu.site_use_code      = ‘BILL_TO’
   AND hcu.cust_acct_site_id  = hcas.cust_acct_site_id
   –AND hp.party_name          = :party_name
   AND ip.instrument_type     = ‘BANKACCOUNT’
   AND ie.acct_site_use_id    = hcu.site_use_id
   –AND eb.bank_account_num    = p_bank_acct_num;
   –AND hcu.primary_flag       = ‘Y’
   AND eb.iban                = p_iban
   AND hcas.org_id            = gn_org_id;
  
CURSOR lcu_org_name (p_org_id IN NUMBER)
IS
  SELECT name        
    FROM hr_operating_units
   WHERE organization_id = p_org_id;    
  
CURSOR lcu_cust (p_customer_id NUMBER)
IS
  SELECT customer_name, customer_number
    FROM ar_customers
   WHERE customer_id = p_customer_id;  

CURSOR lcu_transmission(p_org_name VARCHAR)
IS
SELECT meaning
  FROM fnd_lookup_values     
 WHERE lookup_type = ‘XXEUR_BANK_STATEMENT’
   AND language    = USERENV(‘LANG’)
   AND tag         = ‘TRANSMISSION_NAME’
   AND DESCRIPTION = p_org_name; 

  
CURSOR lcu_lockbox(p_org_name VARCHAR)
IS
SELECT meaning
  FROM fnd_lookup_values     
 WHERE lookup_type = ‘XXEUR_BANK_STATEMENT’
   AND language    = USERENV(‘LANG’)
   AND tag         = ‘LOCKBOX_NUMBER’
   AND DESCRIPTION = p_org_name;

BEGIN

gn_org_id := fnd_profile.value(‘ORG_ID’);

gn_batch_name := xxeur_ge_bs_batch_name_s.NEXTVAL;

   OPEN lcu_org_name(gn_org_id);
   FETCH lcu_org_name INTO lc_org_name;
   CLOSE lcu_org_name;
  
   OPEN lcu_transmission(lc_org_name);
   FETCH lcu_transmission INTO lc_transmission_name;
   CLOSE lcu_transmission;
  
   BEGIN
       SELECT transmission_id
         INTO gn_transmission_id
         FROM ar_transmissions_all
        WHERE transmission_name = lc_transmission_name 

          AND org_id            = gn_org_id;
   EXCEPTION WHEN OTHERS
   THEN
   fnd_file.put_line (fnd_file.log,’**** ERROR -> Transmission Name – ‘||lc_transmission_name||’ – is not defined in the system ‘||SQLERRM);
   END;
  
   OPEN lcu_lockbox(lc_org_name);
   FETCH lcu_lockbox INTO gc_lockbox_num;
   CLOSE lcu_lockbox;
  
   IF gc_lockbox_num IS NOT NULL
   THEN
   fnd_file.put_line (fnd_file.log,’**** ERROR -> Lockbox Number – is not defined in the system ‘||SQLERRM);
   END IF;  

          fnd_file.put_line (fnd_file.output,RPAD(‘*’,150,’*’));
          fnd_file.put_line(fnd_file.output,
          ‘=================================================================      Interface Summary     =========================================================’
                           );
          fnd_file.put_line (fnd_file.output,RPAD(‘*’,150,’*’));
          fnd_file.put_line (fnd_file.output,”);
          fnd_file.put_line (fnd_file.output,”);
          fnd_file.put_line (fnd_file.output,RPAD(‘=’,150,’=’));                      
      fnd_file.put_line (fnd_file.output,
                            RPAD (‘Statement Number’, 25)
                         || RPAD (‘Line Number’, 14)
                         || RPAD (‘Receipt Number’, 17)
                         || RPAD (‘Receipt Amount’, 17)                           
                         || RPAD (‘No.of Invocies’, 15)
                         || RPAD (‘Customer Number’, 20)
                         || RPAD (‘Customer Name’, 50)                        
                         –|| RPAD (‘Bill To Location’, 25)                                                 
                        );
      fnd_file.put_line (fnd_file.output,RPAD(‘=’,150,’=’));

FOR lcu_bs_head_rec IN lcu_bs_head
LOOP

fnd_file.put_line (fnd_file.log,’*** Stage 1′);

fnd_file.put_line (fnd_file.log,’STATEMENT_NUMBER : ‘||lcu_bs_head_rec.statement_number);

    lc_bank_account  := NULL;
    lc_status        := ‘S’;
    lc_error_message := NULL;
    gc_lockbox_num   := NULL;

    BEGIN
    SELECT bank_account_num, attribute1
      INTO lc_bank_account, gc_lockbox_num
      FROM ce_bank_accounts
     WHERE bank_account_id = lcu_bs_head_rec.bank_account_id;
    EXCEPTION WHEN OTHERS
    THEN
    fnd_file.put_line (fnd_file.log,’Error in bank account selection ->’||SQLERRM);
    END;
   
    IF gc_lockbox_num IS NULL
    THEN
    lc_status := ‘E’;
    fnd_file.put_line (fnd_file.log,’*** ERROR -> Lockbox Number is not available in Bank Account ->’||lc_bank_account);
    END IF;
   
  IF lc_status = ‘S’
  THEN 

    FOR lcu_bs_line_rec IN lcu_bs_line (lcu_bs_head_rec.statement_header_id)
    LOOP
   
       ln_bank_site_use_id := NULL;
       ln_bank_customer_id := NULL;
       ln_inv_customer_id  := NULL;
       ln_inv_site_use_id  := NULL;
       ln_cust_count       := 0;
       lc_diff_cust        := ‘N’;
       lc_invoice1        := NULL;
       lc_invoice2        := NULL;
       lc_invoice3        := NULL;
       lc_invoice4        := NULL;
       lc_invoice5        := NULL;
       lc_invoice6        := NULL;
       lc_invoice7        := NULL;
       lc_invoice8        := NULL;
       lc_cust_num        := NULL;
       lc_cust_name       := NULL;
   
       OPEN lcu_bank_acct (lcu_bs_line_rec.bank_account_text);
       FETCH lcu_bank_acct INTO lc_cust_num,ln_bank_customer_id,ln_cust_count;
       CLOSE lcu_bank_acct;
      
       ln_rec_count := 0;
      
       fnd_file.put_line (fnd_file.log,’*** Line Loop -> Line : ‘||lcu_bs_line_rec.line_number||’| STATEMENT_LINE_ID : ‘||lcu_bs_line_rec.statement_line_id);
      
       fnd_file.put_line (fnd_file.log,’*** IBAN CUST Info : ‘||’lc_cust_num’||’->’||lc_cust_num||’, ln_bank_customer_id’||’->’||ln_bank_customer_id||
                 ‘, ln_cust_count’||’->’||ln_cust_count);

                           
—       ln_inv_customer_id := ln_bank_customer_id;
—       ln_inv_site_use_id := ln_bank_site_use_id;
       ln_customer_id     := NULL;
       ln_site_use_id     := NULL;
      
       ln_item_number     := xxeur_ge_item_number_s.NEXTVAL;
      
       IF NVL(ln_cust_count, 0) > 1
       THEN
       –lc_diff_cust       := ‘Y’;      
       lc_cust_num         := NULL;
       ln_bank_customer_id := NULL;
       fnd_file.put_line (fnd_file.log,’*** Diff Cust By IBAN’);
       END IF;
      
       –fnd_file.put_line (fnd_file.log,’*** 123′);
      
       IF TRIM(lcu_bs_line_rec.bank_trx_number) IS NULL
       THEN
       lc_receipt_number  := xxeur_ge_receipt_number_s.NEXTVAL;
      
       ELSE
       lc_receipt_number  := (TRIM(lcu_bs_line_rec.bank_trx_number));
       fnd_file.put_line (fnd_file.log,’*** 123-2′);
       END IF;
   
        FOR lcu_inv_lookup_rec IN lcu_inv_lookup(lc_org_name)
        LOOP
       
            FOR lcu_inv_list_rec IN lcu_inv_list (lcu_bs_line_rec.trx_text,lcu_inv_lookup_rec.inv_prefix,lcu_inv_lookup_rec.inv_length)
            LOOP
                                                             
             ln_customer_trx_id := NULL;
             fnd_file.put_line (fnd_file.log,’*** Stage 3′);
             fnd_file.put_line (fnd_file.log,’Invoice Number -> ‘||lcu_inv_list_rec.inv_num);
             fnd_file.put_line (fnd_file.log,’Customer Id -> ‘||ln_bank_customer_id);
             fnd_file.put_line (fnd_file.log,’IBAN -> ‘||lcu_bs_line_rec.bank_account_text);
            
             BEGIN
             SELECT TRX.bill_to_customer_id, TRX.bill_to_site_use_id,TRX.customer_trx_id
               INTO ln_customer_id, ln_site_use_id,ln_customer_trx_id
               FROM ra_customer_trx_all TRX
              WHERE TRX.trx_number          = lcu_inv_list_rec.inv_num
                –AND TRX.bill_to_site_use_id = NVL(NVL(ln_bank_site_use_id,ln_site_use_id), TRX.bill_to_site_use_id)
                AND TRX.bill_to_customer_id = NVL( (ln_bank_customer_id), TRX.bill_to_customer_id)
                AND TRX.org_id              = gn_org_id;
               
                ln_rec_count := ln_rec_count + 1;
               
             EXCEPTION WHEN NO_DATA_FOUND
             THEN
                 ln_customer_id := NULL;
                 ln_site_use_id := NULL;
             WHEN OTHERS
             THEN
             fnd_file.put_line (fnd_file.log,’Multiple Transaction for the given TRX_NUMBER’);
             END;
            
             fnd_file.put_line (fnd_file.log,’ln_rec_count-> ‘||ln_rec_count);
            
             IF ln_rec_count = 1 AND ln_customer_id IS NOT NULL
             THEN
                 ln_inv_customer_id := ln_customer_id;
                 ln_inv_site_use_id := ln_site_use_id;
                
                 fnd_file.put_line (fnd_file.log,’ln_inv_customer_id-> ‘||ln_inv_customer_id||’, ln_inv_site_use_id-> ‘||ln_inv_site_use_id);
             END IF;
            
             IF ln_site_use_id IS NOT NULL
             THEN                                 
                 fnd_file.put_line (fnd_file.log,’*** Invoice Found & Line Data Insertion ***’);
                
                         insert_custom_tb (
                              p_record_type     => ‘5’
                             ,p_batch_amount    => lcu_bs_line_rec.batch_amount
                             ,p_batch_cnt       => lcu_bs_line_rec.batch_count
                             ,p_receipt_number  => lc_receipt_number
                             ,p_customer_number => lc_cust_num
                             ,p_invoice        => lcu_inv_list_rec.inv_num
                             ,p_customer_id     => NVL(ln_bank_customer_id,ln_inv_customer_id) –ln_inv_customer_id
                             ,p_site_use_id     => NVL(ln_bank_site_use_id, ln_inv_site_use_id) –ln_inv_site_use_id
                             ,p_transmission_id => gn_transmission_id
                             ,p_item_number     => ln_item_number
                             ,p_overflow_seq    => ”–ln_overflow_cnt
                             ,p_account_num     => lc_bank_account –NVL(p_account,’50099009′)
                             ,p_gl_date         => lcu_bs_head_rec.gl_date
                             ,p_comments        => lcu_bs_line_rec.trx_text –SUBSTR(lcu_bs_line_rec.trx_text,1,239)
                             ,p_remittance_amt  => lcu_bs_line_rec.amount
                             ,p_batch_name      => gn_batch_name –SUBSTR(lcu_bs_head_rec.statement_number,1,25)
                             ,p_trx_date        => lcu_bs_line_rec.trx_date
                             ,p_line_number     => lcu_bs_line_rec.line_number
                             ,p_statement_number => lcu_bs_head_rec.statement_number
                             ,p_customer_trx_id => ln_customer_trx_id
                             );             
                
             END IF;                         
            
             IF ln_inv_customer_id <> ln_customer_id AND ln_customer_id IS NOT NULL AND ln_bank_customer_id IS NULL
             THEN
             fnd_file.put_line (fnd_file.log,’Different Customer Found in Invoice level’);
                 lc_diff_cust := ‘Y’;
             END IF;
            
             IF ln_inv_site_use_id <> ln_site_use_id
             THEN
             ln_inv_site_use_id := get_primary_site_use (ln_customer_id, gn_org_id);
             END IF;
            
             IF ln_site_use_id IS NOT NULL AND ln_rec_count = 1
             THEN
                 lc_invoice1        := lcu_inv_list_rec.inv_num;
                 fnd_file.put_line (fnd_file.log,’*** INV Found 1 -> ‘||lcu_inv_list_rec.inv_num);
                
             ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 2
             THEN
                 lc_invoice2        := lcu_inv_list_rec.inv_num;
                 fnd_file.put_line (fnd_file.log,’*** INV Found 2 -> ‘||lcu_inv_list_rec.inv_num);
                
             ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 3
             THEN
                 lc_invoice3        := lcu_inv_list_rec.inv_num;                
                 fnd_file.put_line (fnd_file.log,’*** INV Found 3 -> ‘||lcu_inv_list_rec.inv_num);
                
             ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 4
             THEN
                 lc_invoice4        := lcu_inv_list_rec.inv_num;                
                 fnd_file.put_line (fnd_file.log,’*** INV Found 4 -> ‘||lcu_inv_list_rec.inv_num);
            
             ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 5
             THEN
                 lc_invoice5        := lcu_inv_list_rec.inv_num;                
                 fnd_file.put_line (fnd_file.log,’*** INV Found 5 -> ‘||lcu_inv_list_rec.inv_num);
            
             ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 6
             THEN
                 lc_invoice6        := lcu_inv_list_rec.inv_num;                
                 fnd_file.put_line (fnd_file.log,’*** INV Found 6 -> ‘||lcu_inv_list_rec.inv_num);
                
             ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 7
             THEN
                 lc_invoice7        := lcu_inv_list_rec.inv_num;                
                 fnd_file.put_line (fnd_file.log,’*** INV Found 7 -> ‘||lcu_inv_list_rec.inv_num);
                
             ELSIF ln_site_use_id IS NOT NULL AND ln_rec_count = 8
             THEN
                 lc_invoice8        := lcu_inv_list_rec.inv_num;                
                 fnd_file.put_line (fnd_file.log,’*** INV Found 8 -> ‘||lcu_inv_list_rec.inv_num);
                    
             END IF;
            
            fnd_file.put_line (fnd_file.log,’ln_customer_id -> ‘||NVL(ln_bank_customer_id,ln_inv_customer_id)|| ‘ln_site_use_id -> ‘ ||
            NVL(ln_bank_site_use_id, ln_inv_site_use_id)
            );

            fnd_file.put_line (fnd_file.log,’*** Stage 3′);
                   
            END LOOP;                       
       
        END LOOP;
       
        IF lc_diff_cust = ‘Y’
        THEN
        ln_inv_customer_id := NULL;
        ln_inv_site_use_id := NULL;
        lc_invoice1        := NULL;
        lc_invoice2        := NULL;
        lc_invoice3        := NULL;
        lc_invoice4        := NULL;
        lc_invoice5        := NULL;
        lc_invoice6        := NULL;
        lc_invoice7        := NULL;
        lc_invoice8        := NULL;
        END IF;
       
        OPEN lcu_cust (NVL(ln_bank_customer_id,ln_inv_customer_id));
        FETCH lcu_cust INTO lc_cust_name,lc_cust_num;
        CLOSE lcu_cust;
       
        insert_lockbox_rec (
                              p_record_type     => ‘5’
                             ,p_batch_amount    => lcu_bs_line_rec.batch_amount
                             ,p_batch_cnt       => lcu_bs_line_rec.batch_count
                             ,p_receipt_number  => lc_receipt_number
                             ,p_customer_number => lc_cust_num
                             ,p_invoice1        => lc_invoice1
                             ,p_invoice2        => lc_invoice2      
                             ,p_invoice3        => lc_invoice3      
                             ,p_invoice4        => lc_invoice4      
                             ,p_invoice5        => lc_invoice5      
                             ,p_invoice6        => lc_invoice6      
                             ,p_invoice7        => lc_invoice7      
                             ,p_invoice8        => lc_invoice8      
                             ,p_amount_applied1 => ln_amount_applied1
                             ,p_amount_applied2 => ln_amount_applied2
                             ,p_amount_applied3 => ln_amount_applied3
                             ,p_amount_applied4 => ln_amount_applied4
                             ,p_amount_applied5 => ln_amount_applied5
                             ,p_amount_applied6 => ln_amount_applied6
                             ,p_amount_applied7 => ln_amount_applied7
                             ,p_amount_applied8 => ln_amount_applied8
                             ,p_customer_id     => NVL(ln_bank_customer_id,ln_inv_customer_id) –ln_inv_customer_id
                             ,p_site_use_id     => NVL(ln_bank_site_use_id, ln_inv_site_use_id) –ln_inv_site_use_id
                             ,p_transmission_id => gn_transmission_id
                             ,p_item_number     => ln_item_number
                             ,p_overflow_seq    => ”–ln_overflow_cnt
                             ,p_account_num     => lc_bank_account –NVL(p_account,’50099009′)
                             ,p_gl_date         => lcu_bs_head_rec.gl_date
                             ,p_comments        => lcu_bs_line_rec.trx_text –SUBSTR(lcu_bs_line_rec.trx_text,1,239)
                             ,p_remittance_amt  => lcu_bs_line_rec.amount
                             ,p_batch_name      => gn_batch_name —SUBSTR(lcu_bs_head_rec.statement_number,1,25)
                             ,p_trx_date        => lcu_bs_line_rec.trx_date
                             ,p_statement_number => lcu_bs_head_rec.statement_number
                             ,p_line_number     => lcu_bs_line_rec.line_number
                             );
       fnd_file.put_line (fnd_file.log,’*** Headers Table Insertion’);
      
       BEGIN
        UPDATE ce_statement_lines
           SET attribute1          = ‘Transferred’
              ,bank_trx_number     = lc_receipt_number
         WHERE statement_line_id = lcu_bs_line_rec.statement_line_id;
        END;
    COMMIT;
   
    fnd_file.put_line (fnd_file.output,
                            RPAD (lcu_bs_head_rec.statement_number, 25)
                         || RPAD (lcu_bs_line_rec.line_number, 14)
                         || RPAD (lc_receipt_number, 17)
                         || RPAD (lcu_bs_line_rec.amount, 17)                           
                         || RPAD (ln_rec_count, 15)
                         || RPAD (lc_cust_num, 20)
                         || RPAD (lc_cust_name, 50)                        
                         –|| RPAD (‘Bill To Location’, 25)                                                 
                        );
    fnd_file.put_line (fnd_file.output,RPAD(‘-‘,150,’-‘));
    fnd_file.put_line (fnd_file.log,RPAD(‘-‘,150,’-‘));           

    END LOOP;
   
    BEGIN
    UPDATE ce_statement_headers
       SET attribute1       = ‘Transferred’
     WHERE statement_number = lcu_bs_head_rec.statement_number
       AND bank_account_id  = lcu_bs_head_rec.bank_account_id;
    END;
    COMMIT;
   
  END IF;   

END LOOP;

    BEGIN
    UPDATE ar_transmissions_all
       SET validated_count           = 0
          ,VALIDATED_AMOUNT          = 0
          ,ORIGIN                    = NULL
          ,status                    = ‘NB’
          ,requested_trans_format_id = (SELECT transmission_format_id FROM ar_transmission_formats
                                                WHERE format_name = ‘XXEUR Transmission Format’)
          ,REQUESTED_LOCKBOX_ID      = NULL
          ,REQUESTED_GL_DATE         = NULL
     WHERE transmission_id           = gn_transmission_id;    
    END;
    COMMIT;
   
    fnd_file.put_line (fnd_file.output,RPAD(‘=’,150,’=’));     
   
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,’Error in main procedure -> ‘||SQLERRM);
END main;

PROCEDURE insert_lockbox_rec (p_record_type     IN VARCHAR2
                             ,p_batch_amount    IN NUMBER
                             ,p_batch_cnt       IN NUMBER
                             ,p_receipt_number  IN VARCHAR2
                             ,p_customer_number IN VARCHAR2
                             ,p_invoice1        IN VARCHAR2
                             ,p_invoice2        IN VARCHAR2
                             ,p_invoice3        IN VARCHAR2
                             ,p_invoice4        IN VARCHAR2
                             ,p_invoice5        IN VARCHAR2
                             ,p_invoice6        IN VARCHAR2
                             ,p_invoice7        IN VARCHAR2
                             ,p_invoice8        IN VARCHAR2
                             ,p_amount_applied1 IN NUMBER
                             ,p_amount_applied2 IN NUMBER
                             ,p_amount_applied3 IN NUMBER
                             ,p_amount_applied4 IN NUMBER
                             ,p_amount_applied5 IN NUMBER
                             ,p_amount_applied6 IN NUMBER
                             ,p_amount_applied7 IN NUMBER
                             ,p_amount_applied8 IN NUMBER
                             ,p_customer_id     IN NUMBER
                             ,p_site_use_id     IN NUMBER
                             ,p_transmission_id IN NUMBER
                             ,p_item_number     IN NUMBER
                             ,p_overflow_seq    IN NUMBER
                             ,p_account_num     IN VARCHAR2
                             ,p_gl_date         IN DATE
                             ,p_comments        IN VARCHAR2
                             ,p_remittance_amt  IN NUMBER
                             ,p_batch_name      IN VARCHAR2
                             ,p_trx_date        IN DATE
                             ,p_statement_number IN VARCHAR2
                             ,p_line_number     IN VARCHAR2
                             )
AS
lc_overflow_indicator VARCHAR2(1);
ln_batch_amount       NUMBER;
ln_remittance_amount  NUMBER;
ld_receipt_date       DATE;
lc_receipt_method     VARCHAR2(50);
lc_receipt_number     VARCHAR2(100);
lc_customer_number    VARCHAR2(100);
ln_customer_id        NUMBER;
ln_site_use_id        NUMBER;
ln_overflow_seq       NUMBER;
BEGIN

  IF p_record_type = 3
  THEN
 
  lc_overflow_indicator := 0;
  ln_batch_amount       := NULL;
  ln_remittance_amount  := NULL;
  lc_receipt_number     := NULL;
  lc_customer_number    := NULL;
  ln_customer_id        := NULL;
  ln_site_use_id        := NULL;
  ld_receipt_date       := NULL;
  ln_overflow_seq       := p_overflow_seq;
                     
  ELSE
 
  lc_overflow_indicator := NULL;
  ln_batch_amount       := p_batch_amount;
  ln_remittance_amount  := p_remittance_amt;
  ln_overflow_seq       := 1;
  lc_receipt_number     := p_receipt_number;
  lc_customer_number    := p_customer_number;
  ln_customer_id        := p_customer_id;   
  ln_site_use_id        := p_site_use_id ;                      
  ld_receipt_date       := p_trx_date;
  lc_receipt_method     := ‘Wire Bank Statement – DE’;

  END IF;
 
     BEGIN
      INSERT INTO ar_payments_interface_all(TRANSMISSION_RECORD_ID
                                          ,CREATION_DATE
                                          ,CREATED_BY
                                          ,LAST_UPDATE_LOGIN
                                          ,LAST_UPDATED_BY
                                          ,LAST_UPDATE_DATE
                                          ,RECORD_TYPE
                                          ,STATUS
                                          ,LOCKBOX_NUMBER
                                          ,BATCH_NAME
                                          –,BATCH_AMOUNT                       — commented by anand on 13-Oct-15
                                          –,BATCH_RECORD_COUNT                 — commented by anand on 13-Oct-15
                                          ,REMITTANCE_AMOUNT
                                          ,CHECK_NUMBER
                                          ,CUSTOMER_NUMBER
                                          ,INVOICE1
                                          ,INVOICE2
                                          ,INVOICE3
                                          ,INVOICE4
                                          ,INVOICE5
                                          ,INVOICE6
                                          ,INVOICE7
                                          ,INVOICE8
                                          ,AMOUNT_APPLIED1
                                          ,AMOUNT_APPLIED2
                                          ,AMOUNT_APPLIED3
                                          ,AMOUNT_APPLIED4
                                          ,AMOUNT_APPLIED5
                                          ,AMOUNT_APPLIED6
                                          ,AMOUNT_APPLIED7
                                          ,AMOUNT_APPLIED8
                                          ,GL_DATE
                                          ,COMMENTS
                                          ,CUSTOMER_ID
                                          ,RECEIPT_METHOD
                                          ,CUSTOMER_SITE_USE_ID
                                          ,RECEIPT_DATE
                                          ,ORG_ID
                                          ,TRANSMISSION_ID
                                          ,ITEM_NUMBER
                                          ,ACCOUNT
                                          ,overflow_sequence
                                          ,OVERFLOW_INDICATOR
                                          ,ATTRIBUTE_CATEGORY
                                          ,ATTRIBUTE1
                                          ,ATTRIBUTE2
                                           )
                                    values(ar_payments_interface_s.NEXTVAL           — TRANSMISSION_RECORD_ID
                                          ,SYSDATE                            — CREATION_DATE
                                          ,fnd_profile.value(‘USER_ID’)              — CREATED_BY
                                          ,fnd_profile.value(‘USER_ID’)              — LAST_UPDATE_LOGIN
                                          ,fnd_profile.value(‘USER_ID’)              — LAST_UPDATED_BY
                                          ,SYSDATE                            — LAST_UPDATE_DATE
                                          ,p_record_type                             — RECORD_TYPE
                                          ,’AR_PLB_NEW_RECORD’                       — STATUS                                         
                                          ,gc_lockbox_num                            — LOCKBOX_NUMBER
                                          ,p_batch_name                              — BATCH_NAME
                                          –,ln_batch_amount                           — BATCH_AMOUNT         — commented by anand on 13-Oct-15
                                          –,p_batch_cnt                               — BATCH_RECORD_COUNT   — commented by anand on 13-Oct-15
                                          ,ln_remittance_amount                      — REMITTANCE_AMOUNT
                                          ,lc_receipt_number                         — CHECK_NUMBER
                                          ,lc_customer_number                        — CUSTOMER_NUMBER
                                          ,p_invoice1                               — INVOICE1
                                          ,p_invoice2                               — INVOICE2
                                          ,p_invoice3                               — INVOICE3
                                          ,p_invoice4                               — INVOICE4
                                          ,p_invoice5                               — INVOICE5
                                          ,p_invoice6                               — INVOICE6
                                          ,p_invoice7                               — INVOICE7
                                          ,p_invoice8                               — INVOICE8
                                          ,p_amount_applied1                        — AMOUNT_APPLIED1
                                          ,p_amount_applied2                        — AMOUNT_APPLIED2
                                          ,p_amount_applied3                        — AMOUNT_APPLIED3
                                          ,p_amount_applied4                        — AMOUNT_APPLIED4
                                          ,p_amount_applied5                        — AMOUNT_APPLIED5
                                          ,p_amount_applied6                        — AMOUNT_APPLIED6
                                          ,p_amount_applied7                        — AMOUNT_APPLIED7
                                          ,p_amount_applied8                        — AMOUNT_APPLIED8
                                          ,p_gl_date –TRUNC(SYSDATE)                            — GL_DATE
                                          ,SUBSTR(p_comments,1,239)                                — COMMENTS
                                          ,ln_customer_id                             — CUSTOMER_ID
                                          ,lc_receipt_method                          — RECEIPT_METHOD
                                          ,ln_site_use_id                             — CUSTOMER_SITE_USE_ID
                                          ,ld_receipt_date                            — RECEIPT_DATE
                                          ,gn_org_id                                     — ORG_ID
                                          ,p_transmission_id                         — TRANSMISSION_ID
                                          ,p_item_number                             — ITEM_NUMBER
                                          ,p_account_num                             — ACCOUNT
                                          ,ln_overflow_seq
                                          ,lc_overflow_indicator
                                          ,’BANK_STATEMENT’                          –ATTRIBUTE_CATEGORY
                                          ,p_statement_number                              –ATTRIBUTE1
                                          ,p_line_number                             –ATTRIBUTE2
                                          );          
     EXCEPTION WHEN OTHERS
     THEN
     fnd_file.put_line (fnd_file.log,’Error in insert ar_payments_interface_all -> statement_number : ‘||p_batch_name||’ *** ‘||SQLERRM);                                              
     END;                     
    
     BEGIN
        INSERT INTO xxeur_ge_bs_to_wire_header (RECORD_ID
                                          ,CREATION_DATE
                                          ,CREATED_BY                                         
                                          ,LAST_UPDATED_BY
                                          ,LAST_UPDATE_DATE
                                          ,RECORD_TYPE                                         
                                          ,LOCKBOX_NUMBER
                                          ,BATCH_NAME
                                          ,BATCH_AMOUNT
                                          ,BATCH_RECORD_COUNT
                                          ,REMITTANCE_AMOUNT
                                          ,CHECK_NUMBER
                                          ,CUSTOMER_NUMBER
                                          ,INVOICE1
                                          ,INVOICE2
                                          ,INVOICE3
                                          ,INVOICE4
                                          ,INVOICE5
                                          ,INVOICE6
                                          ,INVOICE7
                                          ,INVOICE8
                                          ,GL_DATE
                                          ,COMMENTS
                                          ,CUSTOMER_ID
                                          ,RECEIPT_METHOD
                                          ,CUSTOMER_SITE_USE_ID
                                          ,RECEIPT_DATE
                                          ,ORG_ID
                                          ,TRANSMISSION_ID
                                          ,ITEM_NUMBER
                                          ,ACCOUNT
                                          ,overflow_sequence
                                          ,OVERFLOW_INDICATOR
                                          ,statement_line_num
                                          ,STATEMENT_NUMBER
                                           )
                                    values(xxeur_ge_bs_to_wire_header_s.NEXTVAL           — TRANSMISSION_RECORD_ID
                                          ,SYSDATE                            — CREATION_DATE
                                          ,fnd_profile.value(‘USER_ID’)              — CREATED_BY                                         
                                          ,fnd_profile.value(‘USER_ID’)              — LAST_UPDATED_BY
                                          ,SYSDATE                                   — LAST_UPDATE_DATE
                                          ,p_record_type                             — RECORD_TYPE                                             
                                          ,gc_lockbox_num                            — LOCKBOX_NUMBER
                                          ,p_batch_name                              — BATCH_NAME
                                          ,ln_batch_amount                           — BATCH_AMOUNT
                                          ,p_batch_cnt                               — BATCH_RECORD_COUNT
                                          ,ln_remittance_amount                      — REMITTANCE_AMOUNT
                                          ,lc_receipt_number                         — CHECK_NUMBER
                                          ,lc_customer_number                        — CUSTOMER_NUMBER
                                          ,p_invoice1                               — INVOICE1
                                          ,p_invoice2                               — INVOICE2
                                          ,p_invoice3                               — INVOICE3
                                          ,p_invoice4                               — INVOICE4
                                          ,p_invoice5                               — INVOICE5
                                          ,p_invoice6                               — INVOICE6
                                          ,p_invoice7                               — INVOICE7
                                          ,p_invoice8                               — INVOICE8
                                          ,p_gl_date –TRUNC(SYSDATE)                            — GL_DATE
                                          ,p_comments                                — COMMENTS
                                          ,ln_customer_id                             — CUSTOMER_ID
                                          ,lc_RECEIPT_METHOD                          — RECEIPT_METHOD
                                          ,ln_site_use_id                             — CUSTOMER_SITE_USE_ID
                                          ,ld_receipt_date                            — RECEIPT_DATE
                                          ,gn_org_id                                   — ORG_ID
                                          ,p_transmission_id                         — TRANSMISSION_ID
                                          ,p_item_number                             — ITEM_NUMBER
                                          ,p_account_num                             — ACCOUNT
                                          ,ln_overflow_seq
                                          ,lc_overflow_indicator
                                          ,p_line_number                             — statement_line_num
                                          ,p_statement_number
                                          );          
     EXCEPTION WHEN OTHERS
     THEN
     fnd_file.put_line (fnd_file.log,’Error in insert xxeur_ge_bs_to_wire_header -> statement_number : ‘||p_batch_name||’ *** ‘||SQLERRM);                                         
     END;
  COMMIT;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,’Error in insert_lockbox_rec procedure -> statement_number : ‘||p_batch_name||’ *** ‘||SQLERRM);
END insert_lockbox_rec;

PROCEDURE insert_custom_tb(p_record_type     IN VARCHAR2
                             ,p_batch_amount    IN NUMBER
                             ,p_batch_cnt       IN NUMBER
                             ,p_receipt_number  IN VARCHAR2
                             ,p_customer_number IN VARCHAR2
                             ,p_invoice         IN VARCHAR2                            
                             ,p_customer_id     IN NUMBER                   
                             ,p_site_use_id     IN NUMBER
                             ,p_transmission_id IN NUMBER
                             ,p_item_number     IN NUMBER
                             ,p_overflow_seq    IN NUMBER
                             ,p_account_num     IN VARCHAR2
                             ,p_gl_date         IN DATE
                             ,p_comments        IN VARCHAR2
                             ,p_remittance_amt  IN NUMBER
                             ,p_batch_name      IN VARCHAR2
                             ,p_trx_date        IN DATE
                             ,p_statement_number IN VARCHAR2
                             ,p_line_number     IN VARCHAR2                            
                             ,p_customer_trx_id IN NUMBER
                             )
AS
lc_overflow_indicator VARCHAR2(1);
ln_batch_amount       NUMBER;
ln_remittance_amount  NUMBER;
ld_receipt_date       DATE;
lc_receipt_method     VARCHAR2(50);
lc_receipt_number     VARCHAR2(100);
lc_customer_number    VARCHAR2(100);
ln_customer_id        NUMBER;
ln_site_use_id        NUMBER;
ln_overflow_seq       NUMBER;
BEGIN

  IF p_record_type = 3
  THEN
 
  lc_overflow_indicator := 0;
  ln_batch_amount       := NULL;
  ln_remittance_amount  := NULL;
  lc_receipt_number     := NULL;
  lc_customer_number    := NULL;
  ln_customer_id        := NULL;
  ln_site_use_id        := NULL;
  ld_receipt_date       := NULL;
  ln_overflow_seq       := p_overflow_seq;
                     
  ELSE
 
  lc_overflow_indicator := NULL;
  ln_batch_amount       := p_batch_amount;
  ln_remittance_amount  := p_remittance_amt;
  ln_overflow_seq       := 1;
  lc_receipt_number     := p_receipt_number;
  lc_customer_number    := p_customer_number;
  ln_customer_id        := p_customer_id;   
  ln_site_use_id        := p_site_use_id ;                      
  ld_receipt_date       := p_trx_date;
  lc_receipt_method     := ‘Wire Bank Statement – DE’;

  END IF;
      
     BEGIN
        INSERT INTO xxeur_ge_bs_to_wire_lines (RECORD_ID
                                          ,CREATION_DATE
                                          ,CREATED_BY                                         
                                          ,LAST_UPDATED_BY
                                          ,LAST_UPDATE_DATE
                                          ,RECORD_TYPE                                         
                                          ,LOCKBOX_NUMBER
                                          ,BATCH_NAME
                                          ,BATCH_AMOUNT
                                          ,BATCH_RECORD_COUNT
                                          ,REMITTANCE_AMOUNT
                                          ,CHECK_NUMBER
                                          ,CUSTOMER_NUMBER
                                          ,INVOICE
                                          ,GL_DATE
                                          ,COMMENTS
                                          ,CUSTOMER_ID
                                          ,RECEIPT_METHOD
                                          ,CUSTOMER_SITE_USE_ID
                                          ,RECEIPT_DATE
                                          ,ORG_ID
                                          ,TRANSMISSION_ID
                                          ,ITEM_NUMBER
                                          ,ACCOUNT
                                          ,overflow_sequence
                                          ,OVERFLOW_INDICATOR
                                          ,statement_line_num
                                          ,customer_trx_id
                                          ,statement_number
                                           )
                                    values(APPS.xxeur_ge_bs_to_wire_lines_s.NEXTVAL           — TRANSMISSION_RECORD_ID
                                          ,SYSDATE                            — CREATION_DATE
                                          ,fnd_profile.value(‘USER_ID’)              — CREATED_BY                                         
                                          ,fnd_profile.value(‘USER_ID’)              — LAST_UPDATED_BY
                                          ,SYSDATE                            — LAST_UPDATE_DATE
                                          ,p_record_type                             — RECORD_TYPE                                             
                                          ,gc_lockbox_num                                     — LOCKBOX_NUMBER
                                          ,p_batch_name                              — BATCH_NAME
                                          ,ln_batch_amount                           — BATCH_AMOUNT
                                          ,p_batch_cnt                               — BATCH_RECORD_COUNT
                                          ,ln_remittance_amount                      — REMITTANCE_AMOUNT
                                          ,lc_receipt_number                         — CHECK_NUMBER
                                          ,lc_customer_number                        — CUSTOMER_NUMBER
                                          ,p_invoice                                 — INVOICE1
                                          ,p_gl_date –TRUNC(SYSDATE)                            — GL_DATE
                                          ,p_comments                                — COMMENTS
                                          ,ln_customer_id                             — CUSTOMER_ID
                                          ,lc_RECEIPT_METHOD                          — RECEIPT_METHOD
                                          ,ln_site_use_id                             — CUSTOMER_SITE_USE_ID
                                          ,ld_receipt_date                            — RECEIPT_DATE
                                          ,gn_org_id                                  — ORG_ID
                                          ,p_transmission_id                         — TRANSMISSION_ID
                                          ,p_item_number                             — ITEM_NUMBER
                                          ,p_account_num                             — ACCOUNT
                                          ,ln_overflow_seq
                                          ,lc_overflow_indicator
                                          ,p_line_number                             — statement_line_num
                                          ,p_customer_trx_id
                                          ,p_statement_number
                                          );          
     EXCEPTION WHEN OTHERS
     THEN
     fnd_file.put_line (fnd_file.log,’Error in insert xxeur_ge_bs_to_wire_lines -> statement_number : ‘||p_batch_name||’ *** ‘||SQLERRM);                                           
     END;
  COMMIT;
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,’Error in insert_custom_tb procedure -> statement_number : ‘||p_batch_name||’ *** ‘||SQLERRM);
END insert_custom_tb;                            

FUNCTION get_primary_site_use (p_customer_id IN NUMBER
                              ,p_org_id      IN NUMBER)
RETURN NUMBER                              
AS
ln_site_use_id NUMBER;
BEGIN

BEGIN
SELECT HCSU.site_use_id
  INTO ln_site_use_id
  FROM hz_cust_acct_sites_all HCAS
      ,hz_cust_site_uses_all HCSU
 WHERE HCAS.cust_acct_site_id  = HCSU.cust_acct_site_id
   AND HCSU.site_use_code      = ‘BILL_TO’
   AND HCSU.primary_flag       = ‘Y’
   AND HCAS.cust_account_id    = p_customer_id
   AND HCAS.org_id             = p_org_id;
END;

RETURN ln_site_use_id;

END get_primary_site_use;

END xxeur_ge_bs_to_wire;


 

 


  • June 3, 2016 | 15 views