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. This comment has been removed by the author.

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

    ReplyDelete
  6. 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
  7. I prefer to study this kind of material. Nicely written information in this post, the quality of content is fine and the conclusion is lovely. Things are very open and intensely clear explanation of issues

    Data Science course in Chennai
    Data science course in bangalore
    Data science course in pune
    Data science online course
    Data Science Interview questions and answers
    Data Science Tutorial
    Data science course in bangalore

    ReplyDelete
  8. You ought to indulge in a contest for one of the finest blogs on the internet. I am going to suggest this great site! If you are looking for the best Things to do around the world, this list has something for everybody from food to history and culture.

    ReplyDelete
  9. Haryana Govt. Schools monthly syllabus for 1st to 5th, 6th to 8th, 9th to 12th is divided by the department. Download HBSE syllabus for 1st to 12th. SCERT made this distribution of syllabus month wise. SCERT Haryana 7th Class Syllabus Teachers have to cover the mentioned monthly syllabus in the given time. The whole syllabus of all subjects is divided month wise but exam date (student assessment test) is not monthwise.

    ReplyDelete