6 Replies Latest reply: May 22, 2014 3:12 AM by 1047543 RSS

    payments query r12

    user599292

      If anyone has any knowledge of the r12 underlying database, I need to identoify a query to list all payments made during a date range filterered on specific flexfield values (cost centres). Any pointers? Or any concurrent reports that can do the same, SQL preferable. Need to list payment amount, date, supplier.

        • 1. Re: payments query r12
          Hussein Sawwan-Oracle

          I believe we have already answered this in your other thread, don't you think ? -- EBS r12 database documentation

           

          Thanks,

          Hussein

          • 2. Re: payments query r12
            user599292

            Unfortunately not as I need SQL as opposed to seeded report? Any pointers on the SQL for such a report?

            • 3. Re: payments query r12
              Cuauhtemoc Amox

              Get the ERD from etr in this link. Start with AP_INVOICE_PAYMENTS and make your way to AP_INVOICES and from there to AP_INVOICE_DISTRIBUTIONS where you can filter by account segments based on the CCID. Just beware that payments are recorded at header level, not distributions, so would would have to assume prorration occurs.

              • 4. Re: payments query r12
                user599292

                Do you have a sample query to get me started?

                • 5. Re: payments query r12
                  1047543

                  hi,

                  please check the sample code

                   

                  SELECT aia.invoice_id, pov.vendor_name, pov.segment1 "Vendor Num", pov.vendor_type_lookup_code, aia.payment_currency_code,

                    aia.invoice_date, aia.invoice_num, aia.invoice_amount, aia.amount_paid, aia.description, apsa.payment_method_lookup_code,

                    apsa.due_date, apsa.gross_amount, apsa.payment_status_flag "Paid Y/N", apsa.amount_remaining, apb.bank_name, apb.bank_branch_name,

                    apba.bank_account_num, aca.check_date, aca.check_number, aca.status_lookup_code

                    FROM ap_payment_schedules_all apsa,

                    ap_invoices_all aia,

                    ap_bank_branches apb,

                    ap_bank_accounts_all apba,

                    po_vendors pov,

                    ap_checks_all aca,

                    ap_invoice_payments_all aipa

                    WHERE aia.invoice_id = apsa.invoice_id(+)

                    AND aia.vendor_id = pov.vendor_id

                  AND pov.vendor_name LIKE 'SIER%'

                    AND apsa.external_bank_account_id = apba.bank_account_id

                    AND apba.bank_branch_id = apb.bank_branch_id

                    AND aipa.invoice_id = aia.invoice_id

                    AND aca.check_id = aipa.check_id

                  ORDER BY aia.invoice_date DESC

                   

                  regards,

                  Anand Kumar Sigicherla.

                  • 6. Re: payments query r12
                    1047543

                    Hi

                    try this query i think this will fill ur requirement.

                    SELECT  A.ORG_ID "ORG ID",

                      E.VENDOR_NAME "VENDOR NAME",

                       UPPER (E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",

                      F.VENDOR_SITE_CODE "VENDOR SITE",

                      F.ADDRESS_LINE1 "ADDRESS",

                      F.CITY "CITY",

                      F.COUNTRY "COUNTRY",

                      TO_CHAR (TRUNC (D.CREATION_DATE)) "PO DATE",

                      D.SEGMENT1 "PO NUMBER",

                      D.TYPE_LOOKUP_CODE "PO TYPE",

                      C.QUANTITY_ORDERED "QTY ORDERED",

                      C.QUANTITY_CANCELLED "QTY CANCALLED",

                      G.ITEM_DESCRIPTION "ITEM DESCRIPTION",

                      G.UNIT_PRICE "UNIT PRICE",

                       (NVL (C.QUANTITY_ORDERED, 0) - NVL (C.QUANTITY_CANCELLED, 0))

                       * NVL (G.UNIT_PRICE, 0)

                       "PO Line Amount",

                       (SELECT  DECODE (PH.APPROVED_FLAG, 'Y', 'Approved')

                       FROM  PO.PO_HEADERS_ALL PH

                       WHERE  PH.PO_HEADER_ID = D.PO_HEADER_ID)

                       "PO STATUS",

                      A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",

                      A.INVOICE_AMOUNT "INVOICE AMOUNT",

                      TO_CHAR (TRUNC (A.INVOICE_DATE)) "INVOICE DATE",

                      A.INVOICE_NUM "INVOICE NUMBER",

                       (SELECT  DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')

                       FROM  AP.AP_INVOICE_DISTRIBUTIONS_ALL X

                       WHERE  X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)

                       "Invoice Approved?",

                      A.AMOUNT_PAID,

                      H.AMOUNT,

                      I.CHECK_NUMBER "CHEQUE NUMBER",

                      TO_CHAR (TRUNC (I.CHECK_DATE)) "PAYMENT DATE"

                      FROM  AP.AP_INVOICES_ALL A,

                      AP.AP_INVOICE_DISTRIBUTIONS_ALL B,

                      PO.PO_DISTRIBUTIONS_ALL C,

                      PO.PO_HEADERS_ALL D,

                      PO.PO_VENDORS E,

                      PO.PO_VENDOR_SITES_ALL F,

                      PO.PO_LINES_ALL G,

                      AP.AP_INVOICE_PAYMENTS_ALL H,

                      AP.AP_CHECKS_ALL I

                    WHERE  A.INVOICE_ID = B.INVOICE_ID

                       AND B.PO_DISTRIBUTION_ID = C.PO_DISTRIBUTION_ID(+)

                       AND C.PO_HEADER_ID = D.PO_HEADER_ID(+)

                       AND E.VENDOR_ID(+) = D.VENDOR_ID

                       AND F.VENDOR_SITE_ID(+) = D.VENDOR_SITE_ID

                       AND D.PO_HEADER_ID = G.PO_HEADER_ID

                       AND C.PO_LINE_ID = G.PO_LINE_ID

                       AND A.INVOICE_ID = H.INVOICE_ID

                       AND H.CHECK_ID = I.CHECK_ID

                       AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID

                       AND C.PO_HEADER_ID IS NOT NULL

                       AND A.PAYMENT_STATUS_FLAG = 'Y'

                       AND D.TYPE_LOOKUP_CODE != 'BLANKET';