Thursday, 15 February 2018

Supplier and Site bank detail query in R12

/* Formatted on 2/15/2018 2:46:46 PM (QP5 v5.114.809.3010) */
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 aps.vendor_name LIKE '3M%'
         AND assa.vendor_site_id = 6916
         AND ipiua.payment_flow = 'DISBURSEMENTS'
         AND ipiua.order_of_preference = 1;

Supplier / Supplier Site Details:-

SELECT   assa.vendor_site_id
                  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 = nvl(iepa.supplier_site_id,assa.vendor_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 assa.vendor_site_id = 6916
                         AND ieba.ext_bank_account_id = : Bank Id
                         AND assa.org_id = fnd_profile.VALUE (''org_id'')


        ********************  ##################  ********************

SELECT  aps.vendor_name "VERDOR NAME",
        apss.vendor_site_code "VENDOR SITE CODE",
        ieb.bank_name "BANK NAME",
        iebb.bank_branch_name "BANK BRANCH NAME",
        iebb.branch_number "BRANCH NUMBER",
        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM    ap.ap_suppliers aps,
        ap.ap_supplier_sites_all apss,
        apps.iby_ext_bank_accounts ieba,
        apps.iby_account_owners iao,
        apps.iby_ext_banks_v ieb,
        apps.iby_ext_bank_branches_v iebb
WHERE   aps.vendor_id = apss.vendor_id
        and iao.account_owner_party_id = aps.party_id
        and ieba.ext_bank_account_id = iao.ext_bank_account_id
        and ieb.bank_party_id = iebb.bank_party_id
        and ieba.branch_id = iebb.branch_party_id
        and ieba.bank_id = ieb.bank_party_id;

11 comments:

  1. Great Article… I love to read your articles because your writing style is too good....


    Best Training Institute in chennai

    ReplyDelete







  2. This blog was very useful for me waiting for more blog.


    SAP FICO Training in Chennai

    ReplyDelete
  3. A nice article. I totally love the way you presented the topic. Hope to see you post soon again.

    PYTHON Training in Chennai

    ReplyDelete
  4. Wonderful blog....Such a very informative and creative contents
    SEO Training Institute in chennai

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.

    UI Path Training in Bangalore

    ReplyDelete
  8. PLSQL Android Apps - Useful productivity solution for Oracle consultants, please it checkout in google play store :

    Code profiler App
    https://play.google.com/store/apps/details?id=appinventor.ai_pshreedhar.PLSQL_Review1

    Plsql Flowchart App
    https://play.google.com/store/apps/details?id=appinventor.ai_pshreedhar.PLSQL_Flow2

    and also check out the solution demo in Youtube

    https://youtu.be/Wckm1_s9Y0M?list=PLqfoPI5nmJaNJNleopCOYh3rqX-8zraqU

    ReplyDelete
  9. Thanks for the informative article. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.

    Shriram Magizhchi
    Shriram Magizhchi Guduvancheri
    Shriram Magizhchi Price
    Shriram Magizhchi Chennai
    Shriram Magizhchi Apartments
    Shriram Magizhchi Flats
    Shriram Magizhchi Review

    ReplyDelete