Customer & Supplier Banks(External Banks) Technical Information.................

-
 
 
Suppliers and their Bank Details in Oracle Apps R12 
 
In this post, I tried to show the relationship between Important Supplier tables, TCA Tables,Payment Tables with the help of a query. I hope it will be useful.

Table Introduction:
Ø  IBY_EXTERNAL_PAYEES_ALL :  supplier information and customer information
Ø  IBY_EXT_BANK_ACCOUNTS : external bank account information
Ø  IBY_PMT_INSTR_USES_ALL : Payment instruments assignments
Ø  AP_SUPPLIERS : Stores Supplier Information
Ø  AP_SUPPLIERS_SITES_ALL : Stores Supplier site Information     

Query:               

SELECT                           
             /*Supplier Information*/           
              aps.segment1                       oracle_supplier_number
             ,aps.vendor_id
             ,aps.vendor_name                    supplier_name
             ,aps.party_id                       supplier_party_id
             ,iepa.remit_advice_fax              remit_advice_fax
             ,iepa.remit_advice_email            remit_advice_email
             /* Supplier Site Information */
             ,assa.vendor_site_id
             ,assa.party_site_id                 supplier_party_site_id
             ,assa.vendor_site_code              vendor_site_code
             ,assa.pay_site_flag                 pay_site_flag
             ,assa.purchasing_site_flag          purchasing_site_flag
             ,assa.rfq_only_site_flag            rfq_only_site_flag
             /* Bank Information*/
             ,ieba.ext_bank_account_id
             ,hp.party_name                      Bank_party_name
             ,ieba.bank_account_num              bank_account_num
             ,ieba.bank_account_name             bank_account_name
             ,ieba.country_code                  bank_acct_country_code
             ,ieba.currency_code                 bank_acct_currency_code
             /* Bank Address */
             ,hp.address1                        bank_address_line1
             ,hp.address2                        bank_address_line2
             ,hp.address3                        bank_address_line3
             ,hp.city                            bank_address_city
             ,hp.state                           bank_address_state
             ,hp.postal_code                     bank_address_zip
             ,hp.country                         bank_address_country
             /* Bank Branch Address */
             ,hp1.address1                       branch_address_line1
             ,hp1.address2                       branch_address_line2
             ,hp1.address3                       branch_address_line3
             ,hp1.city                           branch_address_city
             ,hp1.state                          branch_address_state
             ,hp1.postal_code                    branch_address_zip
             ,hp1.country                        branch_address_country
FROM          ap_supplier_sites_all              assa
             ,hz_parties                         hp           
             ,iby_ext_bank_accounts              ieba
             ,iby_external_payees_all            iepa
             ,iby_pmt_instr_uses_all             ipiua            
             ,ap_suppliers                       aps
             ,hz_parties                         hp1           
WHERE        assa.vendor_site_id         =      iepa.supplier_site_id
AND          hp.party_id                 =      ieba.bank_id
AND          ipiua.instrument_id         =      ieba.ext_bank_account_id
AND          ipiua.ext_pmt_party_id      =      iepa.ext_payee_id
AND          assa.vendor_id              =      aps.vendor_id
AND          ieba.branch_id              =      hp1.party_id
AND          ipiua.instrument_type       =      'BANKACCOUNT'
AND          ipiua.payment_flow          =      'DISBURSEMENTS'
AND          ipiua.order_of_preference   =      1;
 
Suppliers and their Bank Details in Oracle Apps R12
 
Let us go step by step and finally we can see the consolidated Query. All these queries were tested in R12.1.1 instance.

Query to Fetch Customer Data:
SELECT
      cust.party_name customer_name
    , cust_acct.cust_account_id
    , cust_acct.account_number
    , cust_uses.site_use_code
    , cust_loc.address1
    , cust_loc.address2
    , cust_loc.address3
    , cust_loc.address4
    , cust_loc.city
    , cust_loc.postal_code
FROM
      hz_parties             cust
    , hz_cust_accounts       cust_acct
    , hz_cust_acct_sites_all cust_site
    , hz_party_sites         party_site
    , hz_cust_site_uses_all  cust_uses
    , hz_locations           cust_loc
WHERE cust.party_id               = cust_acct.party_id
AND   cust_acct.cust_account_id   = cust_site.cust_account_id
AND   cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND   cust_site.party_site_id     = party_site.party_site_id
AND   party_site.location_id      = cust_loc.location_id
AND   cust.party_name             like '%&party_name%';

Query to Find the Bank Account id based on Customer Info
SELECT
    account.ext_bank_account_id -- Link to Bank and Branch Information
   ,acc_instr.instrument_id
   ,acc_instr.ext_pmt_party_id
   ,ext_payer.ext_payer_id
   ,ext_payer.cust_account_id  -- Link to Cust Account Info
   ,ext_payer.acct_site_use_id
FROM
    iby_ext_bank_accounts   account
  , iby_pmt_instr_uses_all  acc_instr
  , iby_external_payers_all ext_payer 
WHERE 1= 1
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id  = ext_payer.ext_payer_id
AND ext_payer.cust_account_id   = '&id_from_previus_query';

Query to Find the Bank And Branch Information based on previous Query
SELECT
     cust.party_name                   customer_Party_name
    ,cust.party_id                     customer_party_id
    ,bank.party_name                   bank_name
    ,bank_prof.home_country   
    ,account.bank_account_num
    ,account.bank_account_name
    ,branch.party_name                 branch_name
    ,branch_prof.bank_or_branch_number branch_number
FROM hz_parties               bank
   , hz_relationships         rel
   , hz_parties               branch
   , hz_organization_profiles bank_prof
   , hz_organization_profiles branch_prof
   , iby_ext_bank_accounts    account
   , iby_account_owners       acc_owner
   ,hz_parties                cust
WHERE 1=1
AND bank.party_id                    = rel.object_id
and bank.party_type                  = rel.object_type
AND rel.object_table_name            = 'HZ_PARTIES'
AND rel.relationship_code            = 'BRANCH_OF'
AND rel.subject_id                   = branch.party_id
AND rel.subject_type                 = branch.party_type
AND rel.subject_table_name           = 'HZ_PARTIES'
AND bank.party_id                    = bank_prof.party_id
AND branch.party_id                  = branch_prof.party_id
AND bank.party_id                    = account.bank_id
AND branch.party_id                  = account.branch_id
AND account.ext_bank_account_id      = acc_owner.ext_bank_account_id
AND acc_owner.account_owner_party_id = cust.party_id
AND account.ext_bank_account_id      ='&ext_bank_accout_id_frm_previous_query';

Consolidated Query to fetch Customer info, Customer Site info, Bank Info and Bank Branch Info:
SELECT
     cust.party_name customer_name
   , cust_acct.account_number
   , cust_acct.cust_account_id
   , cust_uses.site_use_code
   , cust_loc.address1
   , cust_loc.address2
   , cust_loc.address3
   , cust_loc.address4
   , cust_loc.city
   , cust_loc.postal_code
   , bank.party_name                   bank_name
   , bank_prof.home_country
   , branch.party_name                 branch_name
   , branch_prof.bank_or_branch_number branch_number
   , account.bank_account_num
   , account.bank_account_name
FROM hz_parties               bank
   , hz_relationships         rel
   , hz_parties               branch
   , hz_organization_profiles bank_prof
   , hz_organization_profiles branch_prof
   , iby_ext_bank_accounts    account
   , iby_external_payers_all  ext_payer
   , iby_pmt_instr_uses_all   acc_instr
   , hz_parties               cust
   , hz_cust_accounts         cust_acct
   , hz_cust_acct_sites_all   cust_site
   , hz_cust_site_uses_all    cust_uses
   , hz_party_sites           party_site
   , hz_locations             cust_loc
WHERE 1=1
AND bank.party_id                    = rel.object_id
and bank.party_type                  = rel.object_type
AND rel.object_table_name            = 'HZ_PARTIES'
AND rel.relationship_code            = 'BRANCH_OF'
AND rel.subject_id                   = branch.party_id
AND rel.subject_type                 = branch.party_type
AND rel.subject_table_name           = 'HZ_PARTIES'
AND bank.party_id                    = bank_prof.party_id
AND branch.party_id                  = branch_prof.party_id
AND cust_acct.cust_account_id        = cust_site.cust_account_id
AND cust_site.cust_acct_site_id      = cust_uses.cust_acct_site_id
AND party_site.party_id              = cust.party_id
AND party_site.party_site_id         = cust_site.party_site_id
AND party_site.location_id           = cust_loc.location_id
AND cust.party_id                    = cust_acct.party_id
AND bank.party_id                    = account.bank_id
AND branch.party_id                  = account.branch_id
AND account.ext_bank_account_id      = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id       = ext_payer.ext_payer_id
AND ext_payer.cust_account_id        = cust_acct.cust_account_id
AND cust_uses.site_use_id            = ext_payer.acct_site_use_id
AND cust.party_name             like '%$Party_name%';

Comments

  1. Unknown16 July 2016 at 00:43

    This comment has been removed by the author.

    ReplyDelete
  2. NNT25 May 2018 at 00:18

    This comment has been removed by the author.

    ReplyDelete
  3. James jones24 September 2018 at 05:46

    It’s nearly impossible to find knowledgeable folks on this topic, but the truth is could be seen as do you know what you are dealing with! Thanks AMREP Vietnam

    ReplyDelete
  4. Shron Prysock7 May 2020 at 14:01


    BEST WAY TO HAVE GOOD AMOUNT TO START A GOOD BUSINESS or TO START LIVING A GOOD LIFE….. Hack and take money directly from any ATM Machine Vault with the use of ATM Programmed Card which runs in automatic mode. email (williamshackers@hotmail.com) for how to get it and its cost . ………. EXPLANATION OF HOW THESE CARD WORKS………. You just slot in these card into any ATM Machine and it will automatically bring up a MENU of 1st VAULT $1,000, 2nd VAULT $2,000, RE-PROGRAMMED, EXIT, CANCEL. Just click on either of the VAULTS, and it will take you to another SUB-MENU of ALL, OTHERS, EXIT, CANCEL. Just click on others and type in the amount you wish to withdraw from the ATM and you have it cashed instantly… Done. ***NOTE: DON’T EVER MAKE THE MISTAKE OF CLICKING THE “ALL” OPTION. BECAUSE IT WILL TAKE OUT ALL THE AMOUNT OF THE SELECTED VAULT. email (williamshackers@hotmail.com) We are located in USA.

    ReplyDelete
  5. Shron Prysock7 May 2020 at 14:02


    BEST WAY TO HAVE GOOD AMOUNT TO START A GOOD BUSINESS or TO START LIVING A GOOD LIFE….. Hack and take money directly from any ATM Machine Vault with the use of ATM Programmed Card which runs in automatic mode. email (williamshackers@hotmail.com) for how to get it and its cost . ………. EXPLANATION OF HOW THESE CARD WORKS………. You just slot in these card into any ATM Machine and it will automatically bring up a MENU of 1st VAULT $1,000, 2nd VAULT $2,000, RE-PROGRAMMED, EXIT, CANCEL. Just click on either of the VAULTS, and it will take you to another SUB-MENU of ALL, OTHERS, EXIT, CANCEL. Just click on others and type in the amount you wish to withdraw from the ATM and you have it cashed instantly… Done. ***NOTE: DON’T EVER MAKE THE MISTAKE OF CLICKING THE “ALL” OPTION. BECAUSE IT WILL TAKE OUT ALL THE AMOUNT OF THE SELECTED VAULT. email (williamshackers@hotmail.com) We are located in USA.

    ReplyDelete
  6. saivenkat15 February 2021 at 20:27

    Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.
    CRM with Invoicing

    ReplyDelete
  7. Charles Silva24 March 2021 at 00:54


    IT WORKS EVERYWHERE IN THE WORLD!! JUST LOCATE AN ATM MACHINE!!!
    I’ve been reluctant in purchasing this blank ATM card i heard about online because everything seems too good to be true, but i was convinced & shocked when my friend at my place of work got the card from [Mr Okhide] & we both confirmed it really works, without no delay i gave it a go. Ever since then I’ve been withdrawing $5000 daily from the card & the money .has been in my own account. So glad i gave it a try at last & this card has really changed my life financially without getting caught, its real & truly works though its illegal but made me rich!! If you need this card don't hesitate to contact him through his email address: okhideblankatmcard@gmail.com or WhatsApp: +44(752)060-6341 ..

    ReplyDelete
  8. Williams Morgan5 August 2021 at 19:00

    GET RICH WITH BLANK ATM CARD ... Whats-app: +1(209)-643-1515

    I want to testify about Oscar White blank ATM cards which can withdraw money from any ATM machines around the world. I was very poor before and have no job. I saw so many testimony about how Oscar White hackers send them the ATM blank card and use it to collect money in any ATM machine and become rich. ( oscarwhitehackersworld@gmail.com ) I email them also and they sent me the blank ATM card. I have use it to get 75,000 dollars. withdraw the maximum of 4,500 USD daily. Oscar White is giving out the card just to help the poor. Hack and take money directly from any ATM machine vault with the use of ATM programmed card which runs in automatic mode.
    Email:oscarwhitehackersworld@gmail.com
    Text & Call or Whats-app: +1(209)-643-1515

    ReplyDelete
  9. WENDY CUTRONA30 August 2021 at 16:54

    HAVE YOU BEEN IN SEARCH FOR GENUINE HACKER'S ONLINE?. HAVE YOU LOST YOUR MONEY TO BINARY OPTION SCAM OR ANY ONLINE SCAM WHATSOEVER?. WELL, YOU HAVE FOUND REDEMPTION .

    BLANK ATM CARD :We have specially programmed BLANK ATM CARDS that can be used to hack any ATM machine, these ATM cards can be used to withdraw at the ATM or swipe, stores and outlets. We sell this BLANK CARDS to all our customers and interested buyers worldwide, the BLANK CARDS has a daily withdrawal limit of $5000 in ATM and up to $50,000 spending limit in stores. and also if you are in need of any other cyber hacking services, we are here for you at any time any day. Email :
    Email:Creditcards.atm@gmail.com
    WhatsApp: +1(539) 888-2243

    -hack into any kind of phone
    _Increase Credit Scores
    _western union, bitcoin and money gram hacking
    _criminal records deletion
    _BLANK ATM/CREDIT CARDS
    _Hacking of phones(that of your spouse, boss, friends, and see whatever is being discussed behind your back)
    _Security system hacking...and so much more. Contact THEM now and get whatever you want at
    Email:Creditcards.atm@gmail.com
    WhatsApp: +1(539) 888-2243
    You can also contact us on Telegram.
    ����������✔����������������������������

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle Purchasing Module Step by Step in R12

-
Image
PURCHASING  All the Purchasing related activity information will be declared in Purchasing              Procure 2 Pay Cycle : Requisition à RFQ à Quotation à Quotation Analysis à Purchasing à Receiving à Invoice  à  Payment à Reconciliation PO-Integration :  PO  is integrated by GL, AP, I Supplier, Procurement, and Project Accounting  PO  Responsibility             Step1: Define  PO  Responsibility                         Nav: System administrator à Security à Responsibility à Define             Step2: Assign  PO  options to the PO Responsibility                         Nav: System administrator à Profile à system             Step3: Assign  PO  Responsibility to User                         Nav: System administrator à security à user à define Note:  PO  is including some of i.e. Options, Supplier, Item, Expenses, and Purchasing . Options : Options nothing but a Controlling future at the operating unit level.                         Th
Read more

Sub Ledger Accounting SLA (Complete Functional Information)

-
Image
SubLegder Accounting in R12 SLA Part -1 In these article, we will explore the very basics of SLA and why it exists. Firstly, what exactly does SLA do? SLA is a module which now sits between the SubLedgers like AP/AR etc and the General Ledger. Have a look at this diagram below.  As you will notice, SLA can act as a mediator between the subledgers and Oracle General Ledger. Before we progress, some terminologies of R12 must be revisited. In 11i we had set of books, and in R12 we call them Ledgers. Likewise in R12 we also have secondary ledgers and reporting ledgers. Hence from 11i perspective think of Ledger as Set of Books. As for Subledger, a Subledger is nothing but a module like AP/AR/PO/Inventory etc. In the diagram below, the second scenario is explained whereby let's say that payables module generates a charge account segment combination for an invoice distribution line as A.B.C.D. In such case, if SLA module is not customized, then the very same A.B.C.D
Read more

Difference between ATO and PTO in oracle apps

-
Difference between ATO and PTO in oracle apps 1) MAKE TO ORDER: This is based on Customer specification, you make the product and supply it to them. This is applicable for Engineering items or any Product which you can make based on Customer order Example: Engineering Items 2) ASSEMBLE TO ORDER: This is you have items in parts and it needs to be assembled according to customer requirements For example you have Laptop coming with standard configuration of 160GB Hard disk and 1 GB RAM. But there are customer orders coming in asking for 200GB Hard Disk and 2 GB RAM and if you can assemble them to suit Customer Requirement 3) CONFIGURE TO ORDER: This is Configuring the Product itself to suit Customer requirements Example. The Thread Manufacturing unit Machineries are configured. I have seen Laxmi Machine Works in INDIA which manufactures textile Machineries configure the machineries based on No.of spindles 4) PICK TO ORDER: This is same as Computer Example in ATO. But the  difference is
Read more