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:
CE_BANK_ACCOUNTS
CE_BANK_ACCT_USES_ALL
CE_BANK_BRANCHES_V
AP_SYSTEM_PARAMETERS_ALL
CE_PAYMENT_DOCUMENTS
AP_LOOKUP_CODES
AP_CHECKS_ALL
IBY_PAYMENT_METHODS_VL
IBY_PAYMENT_PROFILES
FND_LOOKUPS
FND_LOOKUPS
AP_LOOKUP_CODES
FND_DOCUMENT_SEQUENCES
FND_DOC_SEQUENCE_CATEGORIES
FND_TERRITORIES_VL
GL_DAILY_CONVERSION_TYPES
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
CE_STATEMENT_RECONCILS_ALL
CE_STATEMENT_HEADERS
CE_STATEMENT_LINES
GL_DAILY_CONVERSION_TYPES
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_PARTIES
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”
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