Oracle Apps Knowledge Sharing

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: 

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;