/* Lokanadham Thandlam Formatted on 5/27/2015 12:48:42 PM
(QP5 v5.163.1008.3004) */
SELECT DISTINCT
yhca.account_number customer_number,
hp.party_name customer_name,
oha.header_id unique_order_header_id,
oha.order_number,
TO_CHAR (oha.ordered_date, 'DD-MON-YYYY') ordered_date,
oha.flow_status_code order_status_header,
--
'?' "STATUS_DATE",
ola.ordered_item product,
--
'?' "ORDER_DUE_DATE",
TO_CHAR (ola.request_date, 'DD-MON-YYYY') request_date,
TO_CHAR (ola.promise_date, 'DD-MON-YYYY') promise_date,
ola.line_id,
jom.order_line_id,
ola.line_number || '.' || ola.shipment_number line_number,
ola.flow_status_code line_status,
TO_CHAR (ola.last_update_date, 'DD-MON-YYYY') line_status_date,
ola.ordered_quantity,
ola.shipped_quantity,
jom.excise_invoice_no "INVOICE_NO",
jom.excise_invoice_date "INVOICE_DATE"
FROM oe_order_headers_all oha,
oe_order_lines_all ola,
hz_parties
hp,
hz_cust_accounts yhca,
jai_om_wsh_lines_all jom
WHERE 1 = 1
AND oha.header_id = ola.header_id
AND yhca.cust_account_id = oha.sold_to_org_id
AND hp.party_id = yhca.party_id
AND oha.org_id = 82
--
AND OHA.FLOW_STATUS_CODE = 'BOOKED'
--
AND jom.customer_id =
Yhca.cust_account_id --L
AND jom.order_line_id = ola.line_id
ORDER BY oha.order_number, ola.line_number || '.' || ola.shipment_number;