To get order lines cancelled before delivery.sql

select


h.ORDER_NUMBER "Order Number",

oel.ORDERED_ITEM "Sku Number",

oel.LINE_NUMBER "Cancelled Line Number",

decode(oel.SUBINVENTORY,'','Available',oel.subinventory) "Orig Sub",

oel.LINE_NUMBER "Orig Line#",

mp.ORGANIZATION_CODE "Shipping Org",

mp2.ORGANIZATION_CODE "Selling Store",

trunc(oea.HIST_CREATION_DATE) "Cancellation Date",

---trunc(oel.schedule_ship_date) "Original ship date",

SUBSTR(fnd.USER_NAME,1,15) "Cancelled by (ID)",

SUBSTR(fnd.DESCRIPTION,1,25) "Cancelled by (Name)"

FROM oe_order_headers_all h,

oe_order_lines_all oel,

oe_ORDER_LINES_AUDIT_V oea,

fnd_user fnd,

inv.mtl_parameters mp,

inv.mtl_parameters mp2

WHERE 1 = 1

and h.ATTRIBUTE1 = mp2.ORGANIZATION_ID

and h.HEADER_ID = oel.HEADER_ID

and h.ORDER_NUMBER = oea.ORDER_NUMBER

and oel.LINE_ID = oea.LINE_ID

and fnd.user_id = oea.USER_ID

and oel.SHIP_FROM_ORG_ID = mp.ORGANIZATION_ID

and h.HEADER_ID = oel.HEADER_ID

and oel.FLOW_STATUS_CODE = 'CANCELLED'

and oel.CANCELLED_FLAG = 'Y'

and oel.Item_type_Code = 'STANDARD'

and oel.LINE_CATEGORY_CODE = 'ORDER'

and oea.HIST_TYPE_CODE = 'CANCELLATION'

and oel.shipping_method_code='000001_Home Deliv_T_STORESTOCK'

and (oel.schedule_ship_date is null

or trunc(oea.HIST_CREATION_DATE) < trunc(oel.schedule_ship_date))

and h.order_number in(1132427,1110368,1155737,1016372,1016525,1111516,1103495,1104359)