1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Invoice-Payment Query

Discussion in 'Oracle Financials' started by sanjaykhandelwal, Feb 10, 2010.

  1. sanjaykhandelwal

    sanjaykhandelwal Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,
    Can any one help me....
    I need sql query for payment-invoice details ( Supplier Name, No., Invoice no., Invoice Date, Amount, Payment Amount, Payment Document No., Bank Account, Org ID, Segment1, payment voucher no., Payment date) Please help me out in this regard..
     
  2. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    The following query gives the following

    1. Requisition Detail
    2. Purchase Order Details
    3. Receiving Details
    4. Invoicing Detail
    5. Payment Details

    Just modify it to query only the invoice and payment part.

    Code (SQL):
    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';
     
    nestor likes this.
  3. Iwanhe

    Iwanhe Active Member

    Messages:
    30
    Likes Received:
    2
    Trophy Points:
    135
    Location:
    Indonesia
    Thanks apps_expert

    Regards,
    Iwanhe
     
  4. krishna

    krishna Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    Hyderabad
    Hi

    The Above queary is not working, Toad given below errorORA-00942 table are view does not exist
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Krishna,

    use
    PO_VENDORS E,
    PO_VENDOR_SITES_ALL F,

    instead of below
    PO.PO_VENDORS E,
    PO.PO_VENDOR_SITES_ALL F,