R12 Payment Query for Checkrun Name

SELECT asic.selected_check_id, asic.currency_code,

asic.check_amount -- ibypsr.payment_amount,

'A' paystatus, 'APPAYMENT' cashflowtype, 'EFT' paymentmethod,

TO_CHAR (ibydoc.payment_date, 'MM/DD/YYYY') transdate,

TO_CHAR (ibydoc.payment_date, 'MM/DD/YYYY') valuedate,

asu.segment1 descriptionreference, cba.description entityid,

'Y' supplypayingaccount, cba.bank_account_num entbankacctnum,

cba.currency_code entbankcurrcode, 'Y' supplycptydetails,

ieba.bank_account_name counterpartyname,

SUBSTR

(CONCAT (CONCAT (CONCAT (assa.address_line1, ' '),

CONCAT (assa.address_line2, ' ')

),

assa.address_line3

),

1,

35

) counterpartyaddress,

assa.city counterpartycity, assa.state counterpartystate,

assa.country counterpartycountry, assa.zip counterpartyzip,

cbbv.bank_branch_name cptybankcode,

DECODE (cbbv.bank_branch_type,

'SWIFT', 'S',

'IS', 'S',

'A'

) cptybankcodetype,

cbbv.bank_branch_type cptybankbranchid, cbbv.description cptybankname,

SUBSTR (CONCAT (CONCAT (CONCAT (cbbv.address_line1, ' '),

CONCAT (cbbv.address_line2, ' ')

),

cbbv.address_line3

),

1,

35

) cptybankaddress,

cbbv.city cptybankcity, cbbv.state cptybankstate, cbbv.zip cptybankzip,

ieba.bank_account_num cptybankacctnum,

ieba.attribute1 cptybankacctnumtype, ieba.country_code cptybankcountry,

ieba.currency_code cptybankacctcurrcode, ieba.attribute5 taxpayerid,

ieba.attribute14 intmedbankprecept, iia.bank_name intmedbankname,

iia.bank_code intmedbankcode,

DECODE (ieba.attribute14, 'IS', 'S', 'A') intmedbankcodetype,

iia.account_number intmedbankacctnum, ieba.attribute2 bankinstructions,

ieba.attribute3 cptymessage, '2' finchrgalloc,

asic.check_number custrefnum, '' chequenum,

ieba.attribute7 personalidnum,

apdl.call_app_pay_service_req_code custbatchrefid,

ieba.attribute6 reasonforpay, '' transtypecode

FROM

ap_selected_invoice_checks_all asic,

ap_document_lines_v apdl,

iby_docs_payable_all ibydoc,

iby_pay_service_requests ibypsr,

ap_suppliers asu,

ap_supplier_sites_all assa,

ce_bank_accounts cba,

iby_ext_bank_accounts ieba,

iby_external_payees_all iepa,

iby_pmt_instr_uses_all ipisa,

ce_bank_branches_v cbbv,

iby_intermediary_accts iia

WHERE

apdl.call_app_pay_service_req_code = :p_checkrun_name

AND apdl.call_app_pay_service_req_code = asic.checkrun_name

AND ibydoc.calling_app_id = apdl.calling_app_id

AND ibydoc.payment_service_request_id = ibypsr.payment_service_request_id

AND ibypsr.call_app_pay_service_req_code =

apdl.call_app_pay_service_req_code

AND NVL (ibydoc.calling_app_doc_unique_ref1, -99) =

NVL (apdl.calling_app_doc_unique_ref1,

-99)

AND NVL (ibydoc.calling_app_doc_unique_ref2, -99) =

NVL (apdl.calling_app_doc_unique_ref2,

-99)

AND NVL (ibydoc.calling_app_doc_unique_ref3, -99) =

NVL (apdl.calling_app_doc_unique_ref3,

-99)

AND NVL (ibydoc.calling_app_doc_unique_ref4, -99) =

NVL (apdl.calling_app_doc_unique_ref4,

-99)

AND NVL (ibydoc.calling_app_doc_unique_ref5, -99) =

NVL (apdl.calling_app_doc_unique_ref5,

-99)

AND assa.vendor_id = asu.vendor_id

AND assa.vendor_site_id = ibydoc.supplier_site_id

AND assa.party_site_id = ibydoc.party_site_id

AND asu.party_id = ibydoc.payee_party_id

AND cba.bank_account_id = ibydoc.internal_bank_account_id

AND iepa.ext_payee_id = ibydoc.ext_payee_id

AND ipisa.ext_pmt_party_id = iepa.ext_payee_id

AND ieba.ext_bank_account_id = ipisa.instrument_id

AND cbbv.branch_party_id = ieba.branch_id

AND cbbv.bank_party_id = ieba.bank_id

AND iia.bank_acct_id = ieba.ext_bank_account_id

/