oracle 删除po查询,Oracle EBS:PO 常用的查询及Tips

----- List and PO With there approval , invoice and 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'