Thursday, October 20, 2022

Query to get AP Payments , Bank Statements, Internal Bank account and Vendor Details in Oracle apps R12

 







In this post, we have given the query which gives you the joins between AP payment tables, Bank statement tables, Internal bank Account details and vendor details.  


Tested instance: R12.2.4 


Tables Involved: 

  1.     CE_BANK_ACCOUNTS  

  1.     CE_BANK_ACCT_USES_ALL 

  1.     CE_BANK_BRANCHES_V    

  1.     AP_SYSTEM_PARAMETERS_ALL 

  1.     CE_PAYMENT_DOCUMENTS     

  1.     AP_LOOKUP_CODES      

  1.     AP_CHECKS_ALL       

  1.     IBY_PAYMENT_METHODS_VL   

  1.     IBY_PAYMENT_PROFILES     

  1.     FND_LOOKUPS              

  1.     FND_LOOKUPS              

  1.     AP_LOOKUP_CODES          

  1.     FND_DOCUMENT_SEQUENCES   

  1.     FND_DOC_SEQUENCE_CATEGORIES 

  1.     FND_TERRITORIES_VL          

  1.     GL_DAILY_CONVERSION_TYPES   

  1.     AP_SUPPLIERS                

  1.     AP_SUPPLIER_SITES_ALL       

  1.     CE_STATEMENT_RECONCILS_ALL  

  1.     CE_STATEMENT_HEADERS        

  1.     CE_STATEMENT_LINES 

  1.     GL_DAILY_CONVERSION_TYPES                 

  1.     AP_SUPPLIERS                

  1.     AP_SUPPLIER_SITES_ALL       

  1.     HZ_PARTY_SITES              

  1.     HZ_LOCATIONS                

  1.     HZ_PARTIES                

  1.  

Query:  

SELECT 
    cba.bank_account_id ,
    ac.bank_account_name,
    ac.bank_account_num,
    ac.bank_account_type,
    ac.bank_num,
    ac.base_amount,
    ac.address_line1,
    ac.address_line2,
    ac.address_line3,
    ac.address_line4,
    nvl(ac.address_style, 'DEFAULT') address_style,
    ac.amount,
    ac.checkrun_id,
    ac.checkrun_name,
    ac.check_date,
    ac.check_id,
    ac.check_number,
    pd.payment_document_id,
    ac.check_voucher_num,
    ac.city,
    ac.cleared_amount,
    ac.cleared_base_amount,
    ac.cleared_date,
    ac.cleared_exchange_date,
    ac.cleared_exchange_rate,
    ac.cleared_exchange_rate_type,
    ac.country,
    ac.county,
    ac.currency_code,
    ac.doc_category_code,
    ac.doc_sequence_id,
    ac.doc_sequence_value,
    ac.exchange_date,
    ac.exchange_rate,
    ac.exchange_rate_type,
    ac.future_pay_due_date,
    ac.org_id,
    ac.payment_method_code,
    ac.payment_type_flag,
    ac.province,
    ac.released_date,
    ac.released_by,
    ac.state,
    ac.status_lookup_code,
    ac.stopped_date,
    ac.stopped_by,
    ac.treasury_pay_date,
    ac.treasury_pay_number,
    ac.vendor_id,
    ac.vendor_name,
    ac.vendor_site_code,
    ac.vendor_site_id,
    ac.void_date,
    ac.withholding_status_lookup_code,
    ac.zip,
    ac.address_line1
    || DECODE(ac.address_line1, NULL, '', fnd_global.local_chr(10))
    || ac.address_line2
    || DECODE(ac.address_line2, NULL, '', fnd_global.local_chr(10))
    || ac.address_line3
    || DECODE(ac.address_line3, NULL, '', fnd_global.local_chr(10))
    || ac.city
    || ', '
    || ac.state
    || ' '
    || ac.zip
    || DECODE(ac.city, 
             NULL, DECODE(ac.state, 
                          NULL, DECODE(ac.zip, 
                                       NULL, '', fnd_global.local_chr(10)
                                      ), 
                          fnd_global.local_chr(10)
                         ), 
             fnd_global.local_chr(10)
             ) 
    || ac.country address,
    cbb.bank_name,
    DECODE(paycard_reference_id, 
           NULL, cba.bank_account_name, 
           ac.bank_account_name
          ),
    cba.currency_code ,
    aspa.set_of_books_id  ,
    pd.payment_document_name,
    alc1.displayed_field,
    iby1.payment_method_name ,
    alc3.displayed_field check_status,
    stopped_date stop_date,
    fds.name doc_sequence_name,
    fdsc.name doc_category_name,
    ft.territory_short_name,
    gdct.user_conversion_type user_rate_type,
    nvl(pv.vendor_name, 
        DECODE(ac.party_id, '', '*****', hzp.party_name)
        ) current_vendor_name,
    DECODE(ac.vendor_id, 
           '', DECODE(ac.party_id, '', '****', NULL), 
           pv.segment1
           )  vendor_number,
    DECODE(ac.vendor_id, 
          '', DECODE(ac.party_id, '', '***', NULL), 
          pv.num_1099
          )  num_1099,
    DECODE(ac.vendor_site_id, '', 
            DECODE(ac.party_id, '', '****', NULL), 
            DECODE(sign(ac.vendor_site_id), - 1, NULL, pvs.vendor_site_code)
          )  current_vendor_site_code,
    DECODE(sign(ac.vendor_site_id), - 1, 
           NULL, hzl.address1
                 || ' '
                 || hzl.address2
                 || ' '
                 || hzl.city
                 || ' '
                 || hzl.state
                 || ' '
                 || hzl.postal_code
         )  trading_partner_address,
    csh.statement_number ,
    csl.line_number statement_line_number,
    ap_auto_payment_pkg.selection_criteria_exists(ac.check_id) 
    selection_criteria_flag,
    ap_invoice_payments_pkg.get_max_gl_date(ac.check_id) max_payment_gl_date,
    ac.positive_pay_status_code,
    ac.transfer_priority,
    ac.external_bank_account_id, 
    ac.stamp_duty_amt,
    ac.stamp_duty_base_amt,
    ac.maturity_exchange_date,
    ac.maturity_exchange_rate_type,
    ac.maturity_exchange_rate,
    gdct1.user_conversion_type maturity_user_rate_type,
    ac.description,
    ac.anticipated_value_date,
    ac.actual_value_date,
    ap_checks_pkg.get_posting_status(ac.check_id),
    iby3.meaning bank_charge_bearer_dsp,
    iby5.meaning settlement_priority_dsp,
    ac.bank_charge_bearer,
    ac.settlement_priority,
    ac.party_id,
    ac.party_site_id,
    iby2.payment_profile_id,
    iby2.payment_profile_name,
    iby2.processing_type,
    ac.payment_id,
    ac.legal_entity_id,
    ac.void_check_id,
    ac.void_check_number,
    ac.ce_bank_acct_use_id,
    ac.remit_to_supplier_name remit_to_supplier_name,
    ac.remit_to_supplier_id remit_to_supplier_id,
    ac.remit_to_supplier_site remit_to_supplier_site,
    ac.remit_to_supplier_site_id  remit_to_supplier_site_id,
    pv1.segment1 remit_to_supplier_number,
    DECODE(sign(ac.remit_to_supplier_site_id),
           - 1, NULL, hzl1.address1
                      || ' '
                      || hzl1.address2
                      || ' '
                      || hzl1.city
                      || ' '
                      || hzl1.state
                      || ' '
                      || hzl1.postal_code
           ) remit_to_supplier_address,
    ac.relationship_id relationship_id, 
    ac.paycard_authorization_number,
    ac.paycard_reference_id,
    DECODE(ac.remit_to_supplier_id, 
           '', DECODE(pv1.party_id, '', 'NA', NULL), 
           pv1.num_1099
          ) remit_num_1099,
    DECODE(pv1.party_id, 
           '', 'NA', 
           hzp1.party_name
           ) current_remit_to_supplier_name,
    DECODE(ac.remit_to_supplier_site_id,
           '', DECODE(pv1.party_id, '', 'NA', NULL), 
           DECODE(sign(ac.remit_to_supplier_site_id), - 1,NULL, 
                  pvs1.vendor_site_code
                 )
          ) current_remit_vendor_site_code,
    ac.acknowledged_flag  acknowledged_flag
FROM
    ce_bank_accounts            cba,
    ce_bank_acct_uses_all       cbau,
    ce_bank_branches_v          cbb,
    ap_system_parameters_all    aspa,
    ce_payment_documents        pd,
    ap_lookup_codes             alc1,
    iby_payment_methods_vl      iby1,
    iby_payment_profiles        iby2,
    fnd_lookups                 iby3,
    fnd_lookups                 iby5,
    ap_lookup_codes             alc3,
    fnd_document_sequences      fds,
    fnd_doc_sequence_categories fdsc,
    fnd_territories_vl          ft,
    gl_daily_conversion_types   gdct,
    ap_suppliers                pv,
    ap_supplier_sites_all       pvs,
    ce_statement_reconcils_all  csr,
    ce_statement_headers        csh,
    ce_statement_lines          csl,
    ap_checks_all               ac,
    gl_daily_conversion_types   gdct1,
    hz_parties                  hzp,
    hz_party_sites              hps,
    hz_locations                hzl, 
    ap_suppliers                pv1,
    ap_supplier_sites_all       pvs1,
    hz_party_sites              hps1,
    hz_locations                hzl1, 
    hz_parties                  hzp1 
WHERE ac.ce_bank_acct_use_id    = cbau.bank_acct_use_id (+)
    AND cbau.bank_account_id    = cba.bank_account_id (+)
    AND cbau.org_id             = aspa.org_id (+)
    AND ac.maturity_exchange_rate_type = gdct1.conversion_type (+)
    AND cbb.branch_party_id (+) = cba.bank_branch_id
    AND ac.payment_document_id  = pd.payment_document_id (+)
    AND alc1.lookup_type        = 'PAYMENT TYPE'
    AND alc1.lookup_code        = ac.payment_type_flag
    AND iby1.payment_method_code (+) = ac.payment_method_code
    AND iby2.payment_profile_id (+) = ac.payment_profile_id
    AND alc3.lookup_type (+)    = 'CHECK STATE'
    AND alc3.lookup_code (+)    = ac.status_lookup_code
    AND ac.bank_charge_bearer   = iby3.lookup_code (+)
    AND iby3.lookup_type (+)    = 'IBY_BANK_CHARGE_BEARER'
    AND ac.settlement_priority  = iby5.lookup_code (+)
    AND iby5.lookup_type (+)    = 'IBY_SETTLEMENT_PRIORITY'
    AND ac.doc_sequence_id      = fds.doc_sequence_id (+)
    AND fdsc.application_id (+) = 200
    AND ac.doc_category_code    = fdsc.code (+)
    AND ac.country              = ft.territory_code (+)
    AND ac.exchange_rate_type   = gdct.conversion_type (+)
    AND ac.vendor_id            = pv.vendor_id (+)
    AND ac.party_id             = hzp.party_id (+)
    AND ac.vendor_site_id       = pvs.vendor_site_id (+)
    AND ac.party_site_id        = hps.party_site_id (+)
    AND hps.location_id         = hzl.location_id (+)
    AND csr.reference_type (+)  = 'PAYMENT'
    AND csr.reference_id (+)    = ac.check_id
    AND csr.current_record_flag (+) = 'Y'
    AND csr.statement_line_id   = csl.statement_line_id (+)
    AND csl.statement_header_id = csh.statement_header_id (+)
    AND csr.status_flag (+)     = 'M' 
    AND ac.remit_to_supplier_id = pv1.vendor_id (+)
    AND ac.remit_to_supplier_site_id = pvs1.vendor_site_id (+)
    AND pvs1.party_site_id      = hps1.party_site_id (+)
    AND hps1.location_id        = hzl1.location_id (+) 
    AND pv1.party_id            = hzp1.party_id (+) 
    AND ac.creation_date > SYSDATE - 50;

1 Responses to “Query to get AP Payments , Bank Statements, Internal Bank account and Vendor Details in Oracle apps R12”

vQsk3j0ZqwGf said...
December 3, 2022 at 8:32 PM

Play right now to win real cash and be in with the prospect of successful jackpots of a lot as} £100,000. 20 Free Spins credited upon your first £10 deposit on Fishin' Frenzy The Big Catch only, valued at 10p per spin. All winnings are uncapped and credited to your real money balance. You can get pleasure from basic slot video games 코인카지노 like “Crazy train” or Linked Jackpot video games like “Vegas Cash”. You can also get pleasure from an interactive story-driven slot game from our “SlotoStories” sequence or a collectible slot game like ‘Cubs & Joeys”! The greatest way to discover out is to spin and see what fits you greatest.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.