Tuesday, October 28, 2014

Oracle R12 Supplier Bank, Bank Branch and Bank Account Creation Scripts


DECLARE
      x_response_rec          apps.iby_fndcpt_common_pub.result_rec_type;
      l_init_msg_list         VARCHAR2 (2000);
      o_bank_id               NUMBER;
      x_return_status         VARCHAR2 (3000);
      l_msg_count             NUMBER;
      l_msg_data              VARCHAR2 (3000);
      l_msg_dummy             VARCHAR2 (3000);
      l_output                VARCHAR2 (3000);
      l_extbank_rec           apps.iby_ext_bankacct_pub.extbank_rec_type;
      l_ext_bank_branch_rec   apps.iby_ext_bankacct_pub.extbankbranch_rec_type;
      l_bank_acct_rec         apps.iby_ext_bankacct_pub.extbankacct_rec_type;
      out_mesg                apps.iby_fndcpt_common_pub.result_rec_type;
      l_acct                  NUMBER;
      l_assign                apps.iby_fndcpt_setup_pub.pmtinstrassignment_tbl_type;
      l_payee_rec             apps.iby_disbursement_setup_pub.payeecontext_rec_type;
      l_return_status         VARCHAR2 (30);
      l_bank_id               NUMBER;
      l_branch_id             NUMBER;
      l_bank                  VARCHAR2 (1000);
      l_acct_owner_party_id   NUMBER;
      l_supplier_site_id      NUMBER;
      l_party_site_id         NUMBER;
------------------------------------------------------------------------------
-->  BANK
-------------------------------------------------------------------------------
BEGIN
            x_return_status := '';
            l_msg_count := '';
            l_msg_data := '';
            apps.fnd_msg_pub.delete_msg (NULL);
            apps.fnd_msg_pub.initialize ();
            l_extbank_rec.bank_name   := 'Test Supp Bank' ;
            l_extbank_rec.bank_number := 'TSB0000001';
            l_extbank_rec.country_code:= 'IN';
            APPS.IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK (1.0,
                                                       'F',
                                                       l_extbank_rec,
                                                       o_bank_id,
                                                       x_return_status,
                                                       l_msg_count,
                                                       l_msg_data,
                                                       x_response_rec
                                                      );
            l_output := ' ';
            IF x_return_status <> 'S'
            THEN
               FOR i IN 1 .. l_msg_count
               LOOP
                  apps.fnd_msg_pub.get (i,
                                        apps.fnd_api.g_false,
                                        l_msg_data,
                                        l_msg_dummy
                                       );
                  l_output := l_output || (TO_CHAR (i) || ': ' || SUBSTR (l_msg_data, 1, 250));
               END LOOP;
               apps.fnd_file.put_line (apps.fnd_file.output, 'Error Occured while Creating Bank: ');
            END IF;
      COMMIT;
END ;
------------------------------------------------------------------------------
-->  BANK BRANCH
/* Get The Bank ID from Banks */
-------------------------------------------------------------------------------
BEGIN
            x_return_status := '';
            l_msg_count := '';
            l_msg_data := '';
            apps.fnd_msg_pub.delete_msg (NULL);
            apps.fnd_msg_pub.initialize ();
            l_ext_bank_branch_rec.bank_party_id := '70117' --> Get The Bank ID from Banks;
            l_ext_bank_branch_rec.branch_name   := 'Test Supp Bank Branch';
            l_ext_bank_branch_rec.branch_number := 'TSBB000001';
            l_ext_bank_branch_rec.branch_type   := 'SWIFT';
            l_ext_bank_branch_rec.bic           := 'TSBBBIC001';
            APPS.IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH
                                                      (1.0,
                                                       'F',
                                                       l_ext_bank_branch_rec,
                                                       o_branch_id,
                                                       x_return_status,
                                                       l_msg_count,
                                                       l_msg_data,
                                                       x_response_rec
                                                      );
            l_output := ' ';
            IF x_return_status <> 'S'
            THEN
               FOR i IN 1 .. l_msg_count
               LOOP
                  apps.fnd_msg_pub.get (i,
                                        apps.fnd_api.g_false,
                                        l_msg_data,
                                        l_msg_dummy
                                       );
                  l_output := l_output || (TO_CHAR (i) || ': ' || SUBSTR (l_msg_data, 1, 250));
               END LOOP;
               apps.fnd_file.put_line(apps.fnd_file.output,'Error Occured while Creating Bank Branches: ');
            END IF;
        COMMIT;                
END;
------------------------------------------------------------------------------
--> BANK ACCOUNTS
/*  Get the bank ID and Branch ID from Branches and site ID , org id from suppliers */
-------------------------------------------------------------------------------
BEGIN
            l_return_status := '';
            l_msg_count := '';
            l_msg_data := '';
            apps.fnd_msg_pub.delete_msg (NULL);
            apps.fnd_msg_pub.initialize ();
            l_bank_acct_rec.branch_id                    := 70120 ;
            l_bank_acct_rec.bank_id                      := 70117 ;
            l_bank_acct_rec.acct_owner_party_id := 69118 ;
            l_bank_acct_rec.bank_account_num    := 'TSB0000001';
            l_bank_acct_rec.iban                                 := 'TSBIBAN001';
            l_bank_acct_rec.start_date                   := SYSDATE;
            l_bank_acct_rec.country_code                 := 'IN';
            l_bank_acct_rec.currency                    := 'INR';
            l_bank_acct_rec.foreign_payment_use_flag := 'Y';
            apps.iby_ext_bankacct_pub.create_ext_bank_acct
                        (p_api_version            => 1.0,
                         p_init_msg_list          => 'F',
                         p_ext_bank_acct_rec      => l_bank_acct_rec,
                         p_association_level      => 'SS',
                         p_supplier_site_id       => 5467,
                         p_party_site_id          => 64032,
                         p_org_id                 => 98,
                         p_org_type               => 'OPERATING_UNIT',
                         x_acct_id                => l_acct,
                         x_return_status          => l_return_status,
                         x_msg_count              => l_msg_count,
                         x_msg_data               => l_msg_data,
                         x_response               => out_mesg
                        );
            l_output := ' ';
            IF l_return_status = fnd_api.g_ret_sts_error
            THEN
               FOR i IN 1 .. l_msg_count
               LOOP
                  apps.fnd_msg_pub.get (i,
                                        apps.fnd_api.g_false,
                                        l_msg_data,
                                        l_msg_dummy
                                       );
                  l_output := l_output || (TO_CHAR (i) || ': ' || SUBSTR (l_msg_data, 1, 250));
               END LOOP;
               apps.fnd_file.put_line
                             (apps.fnd_file.output,'Error Occured while Creating Bank Account: ');
            END IF;
END;
END;  
--------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect