Monitoring List between Sales Order and AR Invoice Oracle R12

/* Formatted on 4/25/2016 4:39:02 PM (QP5 v5.163.1008.3004) */
/*
query monitoring from Sales order to AR Invoice
*/
  SELECT hdr.order_number,
         TO_CHAR (hdr.ordered_date, 'dd-MON-yyyy') ord_date,
         dtl.line_number,
         msi.segment1 sona_part_no,
         msi.description item_desc,
         dtl.ordered_quantity,
         (ractl.quantity_invoiced) quantity_invoiced,
         ractl.attribute3 kanban_no,
         dtl.unit_selling_price,
         ract.trx_number,
         TO_CHAR (ract.trx_date, 'dd-MON-yyyy') txn_date,
         TO_CHAR (ractl.creation_date, 'yyyy/mm/dd') invoice_date,
         TO_CHAR (ractl.creation_date, 'hh24:mm:SS') invoice_time,
         rac.customer_number,
         rac.customer_name customer_name,
         ractl.attribute1 cust_po_no,
         extended_amount,
         ractl.revenue_amount
    FROM oe_order_headers_all hdr,
         oe_order_lines_all dtl,
         ra_customer_trx_lines_all ractl,
         mtl_system_items_b msi,
         ra_customer_trx_all ract,
         ar_customers rac
   WHERE     hdr.header_id = dtl.header_id
         AND hdr.org_id = dtl.org_id
         AND ractl.interface_line_attribute1 = TO_CHAR (hdr.order_number)
         AND ractl.sales_order = hdr.order_number
         AND ractl.interface_line_attribute6 = TO_CHAR (dtl.line_id)
         AND msi.inventory_item_id = dtl.inventory_item_id
         AND msi.organization_id = hdr.ship_from_org_id
         AND ract.customer_trx_id = ractl.customer_trx_id
         AND ract.org_id = ractl.org_id
         AND rac.customer_id = ract.ship_to_customer_id
         AND ractl.line_type = 'LINE'
         --AND TRUNC (ract.creation_date) > '01-APR-2016'
         --AND hdr.sold_to_org_id = ract.ship_to_customer_id
         AND hdr.order_number = :p_order_number --'160102000047'
         -- AND NVL (:p_trx_number, ract.trx_number) = :p_trx_number
ORDER BY hdr.order_number, hdr.ordered_date, dtl.line_number

Advertisement