To Get the Sales order, Line, Delivery and invoice Status

-- ---------------------------------------------------------------------------------
-- Purpose : Query to retrive the Sales order,Line, Delivery and invoice Status.
-- Date      : 22.May.2009
-- Created by : Chaitanya
-- .....
-- ---------------------------------------------------------------------------------

SELECT
ooh.ORDER_NUMBER So_Num,
ooh.HEADER_ID,
ool.ordered_quantity ordered_qty,
(select sum(a.SHIPPED_QUANTITY) from wsh_delivery_details a
where SOURCE_HEADER_ID =ooh.HEADER_ID) shipped_qty,
wnd.DELIVERY_ID,
wnd.NAME delivery_name,
(select distinct batch_id from wsh_delivery_details
where SOURCE_HEADER_ID =ooh.HEADER_ID) batch_id,
initcap(ooh.FLOW_STATUS_CODE) Order_Status,
initcap(ool.FLOW_STATUS_CODE) Line_status,
decode(wnd.STATUS_CODE,'CL','Closed','Open') Delivery_status,
(select decode(count(*),0,'Invoice Not Created','Invoice Generated') from ra_customer_trx_lines_all rctl
where rctl.line_type = 'LINE'
and rctl.interface_line_attribute1 = to_char(ooh.order_number)) inv_Status
from
oe_order_headers_all ooh,
wsh_new_deliveries wnd,
oe_order_lines_all ool
where ooh.HEADER_ID = ool.HEADER_ID
and wnd.SOURCE_HEADER_ID = ooh.HEADER_ID
and ooh.order_number = :order_num;


--=======================================================
--- Query to retrive the Sales order and associate invoice.
--=======================================================

SELECT
ooh.order_number,
ooh.ORDERED_DATE,
ooh.FLOW_STATUS_CODE SO_Status,
ool.line_number,
msi.SEGMENT1 Item_Name,
ool.ordered_quantity,
rct.TRX_NUMBER Invoice_Num,
rct.TRX_date Invoice_Date,
rct.STATUS_TRX,
decode(rct.COMPLETE_FLAG,'Y','Completed','In Complete') Inv_Status,
ool.UNIT_SELLING_price*ool.ordered_quantity line_total
from
oe_order_headers_all ooh,
oe_order_lines_all ool,
hz_cust_accounts hca,
ra_customer_trx_lines_all rctl,
ra_customer_trx_all rct,
mtl_system_items msi
where
ooh.header_id=ool.header_id
and ooh.sold_to_org_id=hca.cust_account_id
and msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID
and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_ID
and rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID
and rctl.LINE_TYPE = 'LINE'
and rctl.interface_line_attribute1 = to_char(ooh.ORDER_NUMBER)
and rctl.QUANTITY_invoiced = ool.ORDERED_QUANTITY
and ooh.order_number= 1195372 --517980
order by ool.line_number;


Note: This Query is based on one Invoice for every sales order, and invoice will have those many lines as in the salesorder.


-- ---------------------------------------------------------------------------------