Query : Sales Order Hold & Release

SELECT OHA.ORDER_NUMBER,
HS.HOLD_ENTITY_CODE CREDIT_CHECK_CRITERIA_CODE,
FLV.MEANING CREDIT_CHECK_CRITERIA,
HD.NAME HOLD_NAME,
HD.TYPE_CODE HOLD_TYPE,
HR.RELEASE_REASON_CODE,
FLV1.MEANING RELEASE_REASON,
HS.RELEASED_FLAG,
HS.HOLD_ENTITY_ID,
OH.HOLD_RELEASE_ID,
OH.HEADER_ID,
HS.HOLD_SOURCE_ID
FROM ONT.OE_HOLD_SOURCES_ALL HS,
ONT.OE_HOLD_DEFINITIONS HD,
ONT.OE_ORDER_HOLDS_ALL OH,
ONT.OE_ORDER_HEADERS_ALL OHA,
APPS.FND_LOOKUP_VALUES FLV,
ONT.OE_HOLD_RELEASES HR,
APPS.FND_LOOKUP_VALUES FLV1
WHERE HS.ORG_ID = '&ORG_ID' --- xxx
AND HS.HOLD_ID = HD.HOLD_ID
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND OH.HEADER_ID = OHA.HEADER_ID
AND FLV.LOOKUP_TYPE = 'HOLD_ENTITY_DESC'
AND FLV.VIEW_APPLICATION_ID = 660
AND FLV.LOOKUP_CODE = HS.HOLD_ENTITY_CODE
AND OH.HOLD_RELEASE_ID = HR.HOLD_RELEASE_ID
AND FLV1.LOOKUP_TYPE = 'RELEASE_REASON'
AND FLV1.VIEW_APPLICATION_ID = 660
AND FLV1.LOOKUP_CODE = HR.RELEASE_REASON_CODE

Read More

Query : Get the Serial Number for an Order

SELECT wl.meaning release_status
, wdd.released_status
, wdd.source_line_id
, wdd.source_code
, wser.fm_serial_number
, wser.TO_SERIAL_NUMBER
, wdd.CREATION_DATE
, oelines.header_id
, oeheader.sold_to_org_id
, wdd.shipped_quantity
, wdd.delivery_detail_id
FROM wsh_delivery_details wdd
, apps.wsh_lookups wl
, OE_ORDER_LINES_ALL oelines
, oe_order_headers_all oeheader
, wsh_serial_numbers wser
WHERE 1 = 1
AND wl.lookup_type = 'PICK_STATUS'
AND wl.lookup_code = wdd.released_status
AND wdd.source_code = 'OE'
AND wdd.source_line_id=oelines.line_id
and oelines.header_id = oeheader.header_id
AND WDD.DELIVERY_DETAIL_ID=wser.delivery_detail_id

Read More