Intercompany AR Receipt Creation & Application

Intercompany AR Receipt Creation & Application

Overview
An automatic receipt should be created and
applied to the AR transaction when receiver org creates the payment for the
invoice raised in their system.
Business Rules

  • ·       
    Alternative sites should have
    receiver inventory org code mapping
     
  • ·       
    Only the payments related to
    intercompany suppliers would be considered.
     
  • ·       
    Check number would be
    considered as AR receipt number.
     
  • ·       
    Payment amount would be the
    receipt amount
     
  • ·       
    Customer will be identified
    based on the intercompany org setup in the customer attribute.
  •       AR invoice number would be same as AP invoice number. And AP invoice will be identified from the payment.
  • ·       
    By considering all the above
    scenarios, AR receipt will be created in the sender org.

 Script

CREATE OR REPLACE PACKAGE APPS.XXEUR_INTERCOMPREC_CREATE_PKG 
IS
PROCEDURE XXEUR_INTERCOMPREC_CREATE_PROC(errorbuf VARCHAR2,retcode VARCHAR2);

PROCEDURE XXEUR_INTERCOMPREC_APPLY_PROC(errorbuf VARCHAR2,retcode VARCHAR2);
END;
/



CREATE OR REPLACE PACKAGE BODY APPS.XXEUR_INTERCOMPREC_CREATE_PKG 
IS
PROCEDURE XXEUR_INTERCOMPREC_CREATE_PROC(errorbuf VARCHAR2,retcode VARCHAR2) 

IS
   p_cr_id                 NUMBER;
   l_return_status         VARCHAR2 (1);
   l_msg_count             NUMBER:=0;
   l_msg_data              VARCHAR2 (10000);
   ln_inv_amount           NUMBER;
   lc_inv_number           VARCHAR2(40);
   lc_receipt_number       VARCHAR2(30):=NULL;
   ln_receipt_id           NUMBER;
   ln_receipt_method       NUMBER;
   ln_bank_acct_id         NUMBER;
   ln_rec_org              NUMBER;
   p_attribute_rec         ar_receipt_api_pub.attribute_rec_type;
   lc_ap_iban                 VARCHAR2(50);
   lc_ar_iban                 VARCHAR2(50);
   –p_attribute_rec_type     ar_receipt_api_pub.attribute_rec_type%ROWTYPE;
  
  CURSOR lcu_ap_dets 

IS
   SELECT  check_number receipt_num
             ,AIA.cust_account_id
             ,ACA.check_id
             ,ACA.amount  amount
             ,ACA.currency_code
             ,AIA.ic_inv_org   
             ,iban SUPPLIER_IBAN

      FROM  ap_invoice_payments_all   AIPA
           ,ap_checks_all             ACA
           ,(SELECT ASPS.vendor_site_code_alt ic_inv_org
                 ,AIA.invoice_num
                 ,AIA.org_id
                 ,AIA.invoice_amount
                 ,HCA.cust_account_id
                 ,AIA.invoice_currency_code
                 ,AIA.invoice_id
                 ,EBA.iban 

             FROM ap_invoices_all          AIA
                 ,ap_suppliers             ASP
                 ,ap_supplier_sites_all    ASPS
                 ,hz_cust_accounts         HCA
                 ,iby_external_payees_all  EPA               

                 ,iby_pmt_instr_uses_all   PIU                                                  
                ,iby_ext_bank_accounts    EBA 
            WHERE AIA.vendor_id=ASP.vendor_id
              AND ASP.vendor_id=ASPS.vendor_id
              AND ASP.vendor_type_lookup_code=’INTERCOMPANY’
              AND AIA.invoice_type_lookup_code=’STANDARD’
              AND AIA.vendor_site_id=ASPS.vendor_site_id
              AND ASPS.vendor_site_code_alt IS NOT NULL
              AND HCA.attribute20=AIA.org_id

              AND HCA.customer_class_code=’INTERCOMPANY’
              AND ASPS.vendor_site_id = EPA.supplier_site_id
              AND ASPS.org_id         = EPA.org_id
              AND EPA.ext_payee_id    = PIU.ext_pmt_party_id  
              AND PIU.instrument_id   = EBA.ext_bank_account_id                  
              ) AIA
       WHERE  AIA.invoice_id= AIPA.invoice_id
         AND  ACA.check_id=AIPA.check_id
         –AND TRUNC(ACA.creation_date)=TRUNC(SYSDATE)
         AND NOT EXISTS
                ( SELECT 1
                    FROM ar_cash_receipts_all ACA
                   WHERE ACA.attribute1=TO_CHAR(ACA.check_id)
                     AND ACA.attribute1 IS NOT NULL)
       GROUP BY ACA.check_number
               ,AIA.cust_account_id
               ,ACA.check_id
               ,ACA.currency_code
               ,AIA.ic_inv_org
               ,ACA.amount              

               ,iban;            
BEGIN

apps.fnd_file.put_line(apps.fnd_file.LOG,’========================================================’);

   FOR lcr_apdets IN  lcu_ap_dets
     LOOP             
                —- To get receipt method and bank details—– 
           BEGIN                                       
                 SELECT CBA.iban_number
                       ,ARM.receipt_method_id
                       ,ARMA.remit_bank_acct_use_id
                  INTO lc_ar_iban
                      ,ln_receipt_method
                      ,ln_bank_acct_id
                 FROM  ar_receipt_method_accounts_all ARMA
                      ,ar_receipt_methods             ARM
                      ,ce_bank_acct_uses_all          CBAU
                      ,ce_bank_accounts               CBA
                WHERE ARM.receipt_method_id = ARMA.receipt_method_id
                  AND CBA.bank_account_id   = CBAU.bank_account_id
                  AND bank_acct_use_id      = remit_bank_acct_use_id
                  AND CBA.iban_number       = lcr_apdets.supplier_iban
                  AND ARM.name              = ‘Intercompany Receipts’;
          
           EXCEPTION
           WHEN NO_DATA_FOUND THEN
           fnd_file.put_line(fnd_file.LOG,’No data found for Inter company receipt name’);
          
           WHEN OTHERS THEN
           fnd_file.put_line(fnd_file.LOG,’Error fetching Inter comp receipt method’);
          
           END;
       
            
                
        
    
        IF lc_ar_iban is NOT NULL THEN–lc_ap_iban=lc_ar_iban THEN
      
             —————Get org to create receipt ————–
            
           BEGIN
          
           SELECT operating_unit
             INTO ln_rec_org
             FROM org_organization_definitions 
            WHERE organization_code=lcr_apdets.ic_inv_org;
           
            EXCEPTION
            WHEN OTHERS THEN
            fnd_file.put_line(fnd_file.LOG,’Error fetching org to create receipt ‘);
          
           END;
    
     
          apps.fnd_global.resp_id,apps.fnd_global.resp_appl_id); 
           mo_global.init(‘AR’);
           mo_global.set_policy_context(‘S’,ln_rec_org);

           fnd_msg_pub.initialize;  
            
           fnd_file.put_line (fnd_file.LOG,’TRACK1′);
          
           p_attribute_rec.attribute1:=lcr_apdets.check_id;
         
                apps.ar_receipt_api_pub.create_cash
            (p_api_version                   => 1.0
            ,p_init_msg_list                 => apps.FND_API.G_FALSE
            ,p_commit                        => apps.FND_API.G_FALSE
            ,p_validation_level              => apps.fnd_api.g_valid_level_full
            ,x_return_status                 => l_return_status
            ,x_msg_count                     => l_msg_count
            ,x_msg_data                      => l_msg_data
            ,p_usr_currency_code             => NULL
            ,p_currency_code                 => lcr_apdets.currency_code–‘EUR’–NULL
            ,p_usr_exchange_rate_type        => NULL
            ,p_exchange_rate_type            => NULL
            ,p_exchange_rate                 => NULL
            ,p_exchange_rate_date            => NULL
            ,p_amount                        => lcr_apdets.amount
            ,p_factor_discount_amount        => NULL   
            ,p_receipt_number                => lcr_apdets.receipt_num
            ,p_receipt_date                  => SYSDATE– p_receipt_date
            ,p_gl_date                       => SYSDATE–p_gl_date
            ,p_maturity_date                 => NULL
            ,p_postmark_date                 => NULL
            ,p_customer_id                   => lcr_apdets.cust_account_id                 
,p_customer_name                 => NULL
            ,p_customer_number               => NULL
            ,p_customer_bank_account_id      => NULL
            ,p_customer_bank_account_num     => NULL
            ,p_customer_bank_account_name    => NULL
            ,p_customer_site_use_id          => NULL
            ,p_customer_receipt_reference    => NULL
            ,p_remittance_bank_account_id    => ln_bank_acct_id
            ,p_remittance_bank_account_num   => NULL
            ,p_remittance_bank_account_name  => NULL
            ,p_deposit_date                  => NULL
            ,p_receipt_method_id             => ln_receipt_method

            ,p_receipt_method_name           => NULL
            ,p_doc_sequence_value            => NULL
            ,p_ussgl_transaction_code        => NULL
            ,p_anticipated_clearing_date     => NULL
            ,p_called_from                   => NULL–‘PLSQL’
            ,p_comments                      => ‘Intercompany Receipt’–p_comments
            ,p_attribute_rec                 =>p_attribute_rec
            —   ***  Notes Receivable Additional Information  ***
            ,p_issuer_name                   => NULL
            ,p_issue_date                    => NULL
            ,p_issuer_bank_branch_id         => NULL
            ,p_org_id                        => ln_rec_org
                 —   ** OUT NOCOPY variables
            ,p_cr_id                         => ln_receipt_id
            );   
     
             

                     IF l_return_status = ‘S’
                        THEN
                           COMMIT;
                        
                           FND_FILE.PUT_LINE (
                              FND_FILE.LOG,
                              ‘Receipt created successfully’       );
                           FND_FILE.PUT_LINE (FND_FILE.LOG,
                                              ‘Return status-‘ || l_return_status);
                           FND_FILE.PUT_LINE (FND_FILE.LOG,
                                              ‘Message count-‘ || l_msg_count);
                           FND_FILE.PUT_LINE (FND_FILE.LOG, ‘message data’ || l_msg_data);
                        ELSE
                           ROLLBACK;
                           FND_FILE.PUT_LINE (FND_FILE.LOG,
                                              ‘Return status’ || ‘-‘ || l_return_status);
                           FND_FILE.PUT_LINE (FND_FILE.LOG,
                                              ‘Message count’ || ‘-‘ || l_msg_count);
                           FND_FILE.PUT_LINE (FND_FILE.LOG,
                                              ‘Message data’ || ‘-‘ || l_msg_data);
                          
                              IF l_msg_count = 1    THEN
                               fnd_file.put_line (fnd_file.LOG,’Error messgae: ‘ || ‘–‘ || l_msg_data);
                               ELSIF l_msg_count > 1
                               THEN
                                LOOP
                                 l_msg_data :=
                                    fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

                                 IF l_msg_data IS NULL
                                 THEN
                                    EXIT;
                                 END IF;
                                   fnd_file.put_line (fnd_file.LOG,  ‘Error message:-‘
                                                      || ‘  ‘
                                                      || l_msg_data);
                              END LOOP;
                                
                              END IF;
                          
                     END IF;
            
            
                              
                   apps.fnd_file.put_line(apps.fnd_file.LOG,’Receipt Id –>’||ln_receipt_id||’ ,Receipt Num–>’||lcr_apdets.receipt_num);
              
                   apps.fnd_file.put_line(apps.fnd_file.LOG,’========================================================’);
        ELSE
          
                   apps.fnd_file.put_line(apps.fnd_file.LOG,’Supplier Bank accoun IBAN does not macth with Intercompany IBAN  for Check Num–>’||lcr_apdets.receipt_num);       
                  
       END IF;
      
      
      
     END LOOP;
    
     EXCEPTION
       WHEN OTHERS THEN
       apps.fnd_file.put_line(apps.fnd_file.log,’Final exception Error :-> ‘||SQLERRM);
 END ;

PROCEDURE XXEUR_INTERCOMPREC_APPLY_PROC(errorbuf VARCHAR2,retcode VARCHAR2) 

IS
   p_cr_id                 NUMBER;
   l_return_status         VARCHAR2 (1);
   l_msg_count             NUMBER:=0;
   l_msg_data              VARCHAR2 (10000);
   ln_inv_amount           NUMBER;
   lc_inv_number           VARCHAR2(40);
   lc_receipt_number       VARCHAR2(30):=NULL;
   ln_receipt_id           NUMBER;
   ln_receipt_method       NUMBER;
   ln_bank_acct_id         NUMBER;
   ln_rec_org              NUMBER;
  
  CURSOR lcu_ar_dets IS
             SELECT AIPA.amount ap_amt
                   ,RCT.customer_trx_id
                   ,AIA.invoice_num
                   ,RCT.trx_number    
                   ,ACR.receipt_number
                   ,APS.amount_due_remaining         
                   ,ACR.org_id
                   ,ACR.cash_receipt_id
              FROM ar_cash_receipts_all          ACR
                  ,ap_checks_all                 ACA
                  ,ap_invoices_all               AIA
                  ,ap_invoice_payments_all       AIPA
                  ,ra_customer_trx_all           RCT
                  ,hz_cust_accounts              HCA
                  ,ar_payment_schedules_all      APS
             WHERE  TO_CHAR(ACA.check_id)=ACR.attribute1
               AND AIPA.invoice_id=AIA.invoice_id
               AND ACA.check_id=AIPA.check_id
               AND AIA.invoice_num=RCT.trx_number
               AND AIA.org_id=HCA.attribute20
               AND HCA.cust_account_id=ACR.pay_from_customer
               AND HCA.customer_class_code=’INTERCOMPANY’  
               AND RCT.customer_trx_id=APS.customer_trx_id
               AND APS.status=’OP’
               AND AIPA.amount<=APS.amount_due_remaining
               ORDER BY receipt_number;

BEGIN

     –apps.fnd_file.put_line(apps.fnd_file.LOG,’========================================================’);
   FOR lcr_ardets IN  lcu_ar_dets
  
     LOOP
          
     fnd_file.put_line (fnd_file.LOG,’==========================================================’);
         
apps.fnd_global.resp_id,apps.fnd_global.resp_appl_id);

           mo_global.init(‘AR’);
         
           mo_global.set_policy_context(‘S’,lcr_ardets.org_id);                     
          
           fnd_msg_pub.initialize;  
            
           fnd_file.put_line (fnd_file.LOG,’TRACK1′);
         
           fnd_file.put_line (fnd_file.LOG,’Receipt Num–‘||lcr_ardets.receipt_number);
          
           fnd_file.put_line (fnd_file.LOG,’Invoice Num–‘||lcr_ardets.trx_number);
          
          
                 ar_receipt_api_pub.apply
                            ( p_api_version             => 1.0,
                            p_init_msg_list             => FND_API.G_TRUE,
                            p_commit                    => FND_API.G_TRUE,
                            p_validation_level          => FND_API.G_VALID_LEVEL_FULL,
                            p_cash_receipt_id           => lcr_ardets.cash_receipt_id,
                            p_customer_trx_id           => lcr_ardets.customer_trx_id,
                            p_amount_applied            => lcr_ardets.ap_amt,
                            x_return_status             => l_return_status,
                            x_msg_count                 => l_msg_count,
                            x_msg_data                  => l_msg_data
                            );
                  
                    

                     IF l_return_status = ‘S’                    THEN
                          COMMIT;
                           fnd_file.put_line (fnd_file.LOG,’Receipt applied successfully’       );
                           fnd_file.put_line (fnd_file.LOG,’Return status’ || l_return_status);
                           fnd_file.put_line (fnd_file.LOG,’Message count’ || l_msg_count);
                           fnd_file.put_line (fnd_file.LOG,’Message data’ || l_msg_data);
                        ELSE
                           ROLLBACK;
                           fnd_file.put_line (fnd_file.LOG,’Return status’ || ‘-‘ || l_return_status);
                           fnd_file.put_line (fnd_file.LOG,’Message count’ || ‘-‘ || l_msg_count);
                           fnd_file.put_line (fnd_file.LOG,’Message data’  || ‘-‘ || l_msg_data);
                          
                              IF l_msg_count = 1    THEN
                               fnd_file.put_line (fnd_file.LOG,’Error messgae: ‘ || ‘  ‘ || l_msg_data);
                               ELSIF l_msg_count > 1
                               THEN
                                LOOP
                                 l_msg_data :=
                                    fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

                                 IF l_msg_data IS NULL
                                 THEN
                                    EXIT;
                                 END IF;
                                   fnd_file.put_line (fnd_file.LOG,  ‘Error messgae:-‘|| ‘-‘|| l_msg_data);
                              END LOOP;
                                
                              END IF;
                          
                     END IF;                                                                apps.fnd_file.put_line(apps.fnd_file.LOG,’========================================================’);      
      
     END LOOP;
    
     EXCEPTION
       WHEN OTHERS THEN
       apps.fnd_file.put_line(apps.fnd_file.log,’Final exception Error :-> ‘||SQLERRM);
 END ;

END;
/

  • June 8, 2016 | 19 views