Monday, October 23, 2017

Query to display PO details Which don't have Invoices in Oracle Apps R12

SELECT asp.vendor_name,
         pha.segment1,
         pha.creation_date po_date,
         pha.type_lookup_code,
         SUM (pla.unit_price * pla.quantity) po_amount
    FROM po_distributions_all pda,
         po_headers_all pha,
         rcv_shipment_lines rsl,
         ap_suppliers asp,
         po_lines_all pla
   WHERE     1 = 1
         AND pda.po_header_id = pha.po_header_id
         AND pda.po_distribution_id NOT IN (SELECT po_distribution_id
                                              FROM po_distributions_all pda
                                             WHERE po_distribution_id IN (SELECT DISTINCT
                                                                                 po_distribution_id
                                                                            FROM ap_invoice_distributions_all))
         AND rsl.po_header_id = pha.po_header_id
         AND asp.vendor_id = pha.vendor_id
         AND pha.po_header_id = pla.po_header_id
         AND pla.po_line_id = pda.po_line_id
GROUP BY asp.vendor_name,
         pha.segment1,
         pha.creation_date,
         pha.type_lookup_code;

No comments:

Post a Comment

SupplierAddressImportTemplate.xlsm South Africa Suburb Field mapping in POZ_SUPPLIER_ADDRESSES_INT

Suburb mpping in Supplier Address Import Template will be mapped to Address Element Attribute2 (HZ_LOCATIONS. ADDR_ELEMENT_ATTRIBUTE2)