Oracle Applications: Creating Banks and Branches

Query to find the Supplier Banks and Branches

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, ASS.VENDOR_SITE_CODE "SITE CODE"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER"
FROM HZ_PARTIES HZP
, AP_SUPPLIERS APS
, HZ_PARTY_SITES SITE_SUPP
, AP_SUPPLIER_SITES_ALL ASS
, IBY_EXTERNAL_PAYEES_ALL IEP
, IBY_PMT_INSTR_USES_ALL IPI
, IBY_EXT_BANK_ACCOUNTS IEB
, HZ_PARTIES HZPBANK
, HZ_PARTIES HZPBRANCH
, HZ_ORGANIZATION_PROFILES HOPBANK
, HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID
AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASS.VENDOR_ID = APS.VENDOR_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.PARTY_SITE_ID = SITE_SUPP.PARTY_SITE_ID
AND IEP.SUPPLIER_SITE_ID = ASS.VENDOR_SITE_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
ORDER BY 1,3

Creating Banks

DECLARE
lc_output VARCHAR2(3000);
lc_msg_dummy VARCHAR2(3000);
lc_return_status VARCHAR2(3000);
lc_msg_data VARCHAR2(3000);

ln_bank_id NUMBER;
ln_msg_count NUMBER;
lr_extbank_rec apps.iby_ext_bankacct_pub.extbank_rec_type;
lr_response_rec apps.iby_fndcpt_common_pub.result_rec_type;

BEGIN
lc_return_status := '';
ln_msg_count := '';
lc_msg_data := '';

lr_extbank_rec.bank_name := 'Test Supp Bank';
lr_extbank_rec.bank_number := 'TSB0000001';
lr_extbank_rec.country_code := 'IN';

apps.fnd_msg_pub.delete_msg(NULL);
apps.fnd_msg_pub.initialize();

IBY_EXT_BANKACCT_PUB.create_ext_bank
( -- ------------------------------
-- Input data elements
-- ------------------------------
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_ext_bank_rec => lr_extbank_rec,
-- --------------------------------
-- Output data elements
-- --------------------------------
x_bank_id => ln_bank_id,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
x_response => lr_response_rec
);

lc_output := ' ';

IF (lc_return_status <> 'S')
THEN
FOR i IN 1 .. ln_msg_count
LOOP
apps.fnd_msg_pub.get
( i,
apps.fnd_api.g_false,
lc_msg_data,
lc_msg_dummy
);

lc_output := lc_output ||
(TO_CHAR (i) ||
': ' ||
SUBSTR (lc_msg_data, 1, 250));
END LOOP;

apps.fnd_file.put_line
(apps.fnd_file.output, 'Error Occured while Creating Bank: ');

END IF;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line(SQLERRM);
END;
/

Creating Branches

-- API to Create External Bank Branch

DECLARE     p_api_version    NUMBER                   := 1.0;
     p_init_msg_list  VARCHAR2(1)          := 'F';
    v_bank_id           NUMBER                   := 530705;
     x_return_status  VARCHAR2(2000);
    x_msg_count      NUMBER(5);
    x_msg_data        VARCHAR2(2000);
    x_branch_id        NUMBER;
    p_count               NUMBER;

x_response iby_fndcpt_common_pub.result_rec_type;
p_ext_bank_branch_rec iby_ext_bankacct_pub.extbankbranch_rec_type;

BEGIN
      dbms_output.put_line ('BEFORE BANK BRANCH API');

      p_ext_bank_branch_rec.bch_object_version_number := 1.0;
     p_ext_bank_branch_rec.branch_name        := 'TEST BANK BRANCH';
     p_ext_bank_branch_rec.branch_type          := 'ABA';
     p_ext_bank_branch_rec.bank_party_id      := v_bank_id;

    IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH    (    -- -----------------------------
          -- Input data elements
          -- -----------------------------          p_api_version                  => p_api_version,
          p_init_msg_list                => p_init_msg_list,
          p_ext_bank_branch_rec => p_ext_bank_branch_rec,
          -- --------------------------------
          -- Output data elements
          -- --------------------------------          x_branch_id                     => x_branch_id,
          x_return_status               => x_return_status,
          x_msg_count                   => x_msg_count,
          x_msg_data                     => x_msg_data,
          x_response                      => x_response
     );

dbms_output.put_line ('x_branch_id = ' || x_branch_id);

     IF (x_msg_count = 1)
     THEN
              dbms_output.put_line ('x_msg_data ' || x_msg_data);

     ELSIF (x_msg_count > 1)
    THEN
               LOOP
                     p_count := p_count + 1;
                     x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

                    IF (x_msg_data IS NULL)
                    THEN
                             EXIT;
                    END IF;

                  dbms_output.put_line ('Message' || p_count || ' ---' || x_msg_data);
               END LOOP;
     END IF;

COMMIT;

EXCEPTION
             WHEN OTHERS THEN
                           ROLLBACK;
                           dbms_output.put_line(SQLERRM);
END;
/

SHOW ERR;

--Creating Bank Account Numbers

DECLARE

pApiVersion NUMBER := 1.0;

pInitMsgList VARCHAR2 ( 1 ) := 'F';

xReturnStatus VARCHAR2 ( 2000 );

xMsgCount NUMBER ( 5 );

xMsgData VARCHAR2 ( 2000 );

vBankName VARCHAR2 ( 100 );

vVendorName VARCHAR2 ( 100 );

xResponse iby_fndcpt_common_pub.result_rec_type;

pExtBankAcctRec iby_ext_bankacct_pub.extbankacct_rec_type;

vSupplierPartyID NUMBER; -- EXISTING SUPPLIERS/CUSTOMER PARTY_ID

vBankID NUMBER; -- EXISTING BANK PARTY ID

vBankBranchID NUMBER; -- EXISTING BRANCH PARTY ID

xAcctID NUMBER;

pCount NUMBER;

BEGIN

vVendorName := 'XXAOA SUPPLIER - DEMO'; -- Replace this with supplier, for whom the Bank Account needs to create

vBankName := 'XXAOA BANK - DEMO'; -- Replace this with Bank name

-- SELECT EXISTING Supplier

BEGIN

SELECT party_id

INTO vSupplierPartyID

FROM ap_suppliers

WHERE UPPER ( vendor_name ) = UPPER ( vVendorName );

EXCEPTION

WHEN OTHERS

THEN

vSupplierPartyID := 0;

DBMS_OUTPUT.put_line( 'Exception while fetching Supplier Details '

|| SQLERRM

);

END;

-- SELECT EXISTING Branch PARTY ID to create Branch

BEGIN

SELECT bank_party_id, branch_party_id

INTO vBankID, vBankBranchID

FROM ce_bank_branches_v

WHERE UPPER ( bank_name ) = UPPER ( vBankName );

EXCEPTION

WHEN OTHERS

THEN

vBankID := 0;

vBankBranchID := 0;

DBMS_OUTPUT.put_line( 'Exception while fetching Bank Branch Details '

|| SQLERRM

);

END;

pExtBankAcctRec.object_version_number := 1.0;

pExtBankAcctRec.acct_owner_party_id := vSupplierPartyID;

pExtBankAcctRec.bank_account_name := 'TEST DEC 11 BANK ACCOUNT';

pExtBankAcctRec.bank_account_num := 11273345;

pExtBankAcctRec.bank_id := vBankID;

pExtBankAcctRec.branch_id := vBankBranchID;

pExtBankAcctRec.start_date := SYSDATE;

pExtBankAcctRec.country_code := 'US';

pExtBankAcctRec.currency := 'USD';

pExtBankAcctRec.foreign_payment_use_flag := 'Y';

IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT

( p_api_version => pApiVersion

,p_init_msg_list => pInitMsgList

,p_ext_bank_acct_rec => pExtBankAcctRec

,x_acct_id => xAcctID

,x_return_status => xReturnStatus

,x_msg_count => xMsgCount

,x_msg_data => xMsgData

,x_response => xResponse

);

DBMS_OUTPUT.put_line ( 'xReturnStatus :' || xReturnStatus );

DBMS_OUTPUT.put_line ( 'xMsgCount :' || xMsgCount );

DBMS_OUTPUT.put_line ( 'xMsgData :' || xMsgData );

DBMS_OUTPUT.put_line ( 'xAcctID :' || xAcctID );

DBMS_OUTPUT.put_line ( 'xResponse.Result_Code :' || xResponse.result_code );

DBMS_OUTPUT.put_line ( 'xResponse.Result_Category :' || xResponse.result_category);

DBMS_OUTPUT.put_line ( 'xResponse.Result_Message :' || xResponse.result_message);

IF xReturnStatus = 'S'

THEN

COMMIT;

ELSE

IF xMsgCount > 1

THEN

FOR i IN 1 .. xMsgCount

LOOP

DBMS_OUTPUT.put_line

( i

|| '.'

|| SUBSTR

( fnd_msg_pub.get ( p_encoded => fnd_api.g_false )

,255

)

);

END LOOP;

END IF;

ROLLBACK;

END IF;

END;

SHOW ERRORS

Oracle Applications

Pages

Friday, June 30, 2017

Creating Banks and Branches

No comments:

Post a Comment