To identify redilvery lines

select -- UNDELIVERED LINE CANCELLED REDELIVERY LINE NOT CANCELLED


h.ORDER_NUMBER "Order Number",

Rl.ORDERED_ITEM "Sku Number",

Rl.LINE_NUMBER "Closed Redelivery Line#",

a.LINE_NUMBER "Cancelled Outbound Line#",

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

ol.LINE_NUMBER "Orig Line#",

mp.ORGANIZATION_CODE "Shipping Org",

mp2.ORGANIZATION_CODE "Selling Store",

trunc(oe.HIST_CREATION_DATE) "Cancellation 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 Rl,

oe_order_lines_all a,

oe_order_lines_all ol,

oe_ORDER_LINES_AUDIT_V oe,

fnd_user fnd,

inv.mtl_parameters mp,

inv.mtl_parameters mp2

WHERE 1 = 1

and h.ATTRIBUTE1 = mp2.ORGANIZATION_ID

and h.HEADER_ID = oe.HEADER_ID

and h.ORDER_NUMBER = oe.ORDER_NUMBER

and a.LINE_ID = oe.LINE_ID

and fnd.user_id = oe.USER_ID

and a.SHIP_FROM_ORG_ID = mp.ORGANIZATION_ID



/*the cancelled outbound redelivery line*/

and h.HEADER_ID = a.HEADER_ID

and a.SUBINVENTORY = 'Redelivery'

and a.GLOBAL_ATTRIBUTE4 = 'POND'

and a.FLOW_STATUS_CODE = 'CANCELLED'

and a.CANCELLED_FLAG = 'Y'

and a.Item_type_Code = 'STANDARD'

and a.LINE_CATEGORY_CODE = 'ORDER'

and oe.HIST_TYPE_CODE = 'CANCELLATION'





/*the closed undelivered/redelivery line*/

and h.HEADER_ID = Rl.HEADER_ID

and Rl.ORIG_SYS_LINE_REF = a.ORIG_SYS_LINE_REF

and Rl.line_id = a.Global_attribute3

and Rl.ORDERED_ITEM = a.ORDERED_ITEM

and Rl.SUBINVENTORY = 'Redelivery'

and Rl.GLOBAL_ATTRIBUTE4 = 'POND'

and Rl.FLOW_STATUS_CODE = 'CLOSED'

and Rl.CANCELLED_FLAG = 'N'

and Rl.Item_type_Code = 'STANDARD'

and Rl.LINE_CATEGORY_CODE = 'RETURN'

-- find original closed order line

and h.HEADER_ID = ol.HEADER_ID

and ol.LINE_ID = Rl.REFERENCE_LINE_ID

and ol.LINE_ID = Rl.RETURN_ATTRIBUTE2

and ol.ORDERED_ITEM = a.ORDERED_ITEM

and ol.FLOW_STATUS_CODE = 'CLOSED'

and ol.CANCELLED_FLAG = 'N'

and ol.LINE_CATEGORY_CODE = 'ORDER'

/*enter the date range*/

and trunc(oe.HIST_CREATION_DATE) BETWEEN to_date('16-OCT-2008','dd-mon-yyyy') and to_date('22-Oct-2008','dd-mon-yyyy')

order by h.ORDER_NUMBER, a.LINE_ID