Monday, July 5, 2010

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

3 comments:

  1. Hi Amit,

    We are migrating one report from 11i to R12, but we dont see any data in ap_selected_invoice_checks_all. Do you have any idea the above table is assoicated in which table in R12.

    Thanks,
    Sanjay.

    ReplyDelete
  2. Use below tables and get the data..ap_selected_invoice_checks_all is no longer used in R12...

    iby_docs_payable_all
    iby_pay_service_requests
    iby_pay_instructions_all

    ReplyDelete
  3. Amazing you have shared almost all the concepts about PL/SQL that can be asked in an interview. This post is a great help to revise all the concepts. Each one is explained to the point and accurately.
    sap migration

    ReplyDelete

Note: Only a member of this blog may post a comment.