Search This Blog

Sunday, November 21, 2010

All Po’S With Approval, Invoice, & Payment Details

SELECT
,a.org_id "ORG ID"
,E.SEGMENT1 "VENDOR NUM"
,e.vendor_name "SUPPLIER NAME"
,UPPER(e.vendor_type_lookup_code) "VENDOR TYPE"
,f.vendor_site_code "VENDOR SITE CODE"
,f.ADDRESS_LINE1 "ADDRESS"
,f.city "CITY"
,f.country "COUNTRY"
,to_char(trunc(d.CREATION_DATE)) "PO Date"
,d.segment1 "PO NUM"
,d.type_lookup_code "PO Type"
,c.quantity_ordered "QTY ORDERED"
,c.quantity_cancelled "QTY CANCELLED"
,g.item_id "ITEM ID"
,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 Approved?"
, 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
,h.check_id
,h.invoice_payment_id "Payment Id"
,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'

4 comments:

  1. Hi there, just wanted to tell you, I loved this article.
    It was inspiring. Keep on posting!

    Take a look at my web blog - kanapy z funkcja spania

    ReplyDelete
  2. It is truly a great and useful piece of information.

    I am glad that you simply shared this useful
    information with us. Please keep us informed like this.
    Thanks for sharing.

    Here is my webpage :: tusze

    ReplyDelete
  3. Amazing! Its in fact amazing article, I have got much clear idea about
    from this post.

    Also visit my webpage :: reklama w internecie
    my web page: reklama w internecie

    ReplyDelete
  4. I visited multiple websites except the audio feature for audio
    songs existing at this website is truly marvelous.


    Have a look at my weblog ... meble dla młodzieży

    ReplyDelete