SQL Drop-ship Order

SELECT -- THONG TIN SALES ORDER HEADER
OH.HEADER_ID,
OH.ORDER_NUMBER,
OH.ORDER_TYPE_ID,
OH.PRICE_LIST_ID,
OH.SHIP_TOLERANCE_ABOVE, -- GIOI HEN TREN CUA GIA TRI DUOC PHEP SHIPPING
OH.SHIP_TOLERANCE_ABOVE, -- GIOI HAN DUOI CUA GIA TRI DUOC PHEP SHIPPING
OH.FOB_POINT_CODE,

-- THONG TIN SALES ORDER LINES
OL.LINE_ID,
OL.INVENTORY_ITEM_ID,
OL.ORG_ID,
OL.SHIP_FROM_ORG_ID,
OL.SOLD_TO_ORG_ID,
OL.ORDER_QUANTITY_UOM,
OL.ORDERED_QUANTITY,
OL.ORDERED_QUANTITY_UOM2,
OL.ORDERED_QUANTITY2,
OL.SUBINVENTORY,
OL.UNIT_SELLING_PRICE,
OL.PRICE_LIST_ID,
OL.FOB_POINT_CODE,


-- THONG TIN REQUISITION MUC HEADER
PRH.REQUISITION_HEADER_ID,
PRH.SEGMENT1, -- SO REQUISITION
PRH.TYPE_LOOKUP_CODE,
PRH.START_DATE_ACTIVE,
PRH.END_DATE_ACTIVE,
PRH.DESCRIPTION,
PRH.CANCEL_FLAG,

-- THONG TIN REQUISITION MUC LINES
PRL.REQUISITION_LINE_ID,
PRL.ITEM_ID,
PRL.LINE_NUM,
PRL.ITEM_DESCRIPTION,
PRL.UNIT_MEAS_LOOKUP_CODE,
PRL.SECONDARY_UNIT_OF_MEASURE,
PRL.UNIT_PRICE,
PRL.QUANTITY,
PRL.QUANTITY_CANCELLED,
PRL.QUANTITY_DELIVERED,
PRL.SECONDARY_QUANTITY,
PRL.SECONDARY_QUANTITY_RECEIVED,
PRL.SECONDARY_QUANTITY_CANCELLED,
PRL.SOURCE_TYPE_CODE,
PRL.SOURCE_ORGANIZATION_ID,
PRL.SOURCE_SUBINVENTORY,
PRL.DESTINATION_TYPE_CODE,
PRL.DESTINATION_ORGANIZATION_ID,
PRL.DESTINATION_SUBINVENTORY,
PRL.SOURCE_REQ_LINE_ID,
PRL.AMOUNT,


-- THONG TIN PO HEADERS
POH.PO_HEADER_ID,
POH.TYPE_LOOKUP_CODE,
POH.SEGMENT1,
POH.VENDOR_ID,
POH.FOB_LOOKUP_CODE,
POH.STATUS_LOOKUP_CODE,
POH.START_DATE_ACTIVE,
POH.END_DATE_ACTIVE,
POH.AGENT_ID,
POH.TERMS_ID,
POH.CURRENCY_CODE,
POH.RATE,

-- THONG TIN PO LINES
POL.PO_LINE_ID,
POL.ITEM_ID,
POL.ITEM_DESCRIPTION,
POL.ORG_ID,
POL.BASE_UOM,
POL.BASE_QTY,
POL.SECONDARY_UOM,
POL.SECONDARY_QTY,
POL.UNIT_PRICE,
POL.UNIT_MEAS_LOOKUP_CODE,
POL.QUANTITY,
POL.SECONDARY_UNIT_OF_MEASURE,
POL.SECONDARY_QUANTITY,

-- THONG TIN RECEIPT HEADER
RSH.SHIPMENT_HEADER_ID,
RSH.RECEIPT_SOURCE_CODE,
RSH.SHIPMENT_NUM,
RSH.RECEIPT_NUM,
RSH.BILL_OF_LADING,
RSH.SHIPPED_DATE,
RSH.ORGANIZATION_ID,
RSH.APPROVAL_STATUS,

-- THONG TIN RECEIPT LINES
RSL.SHIPMENT_LINE_ID,
RSL.FROM_ORGANIZATION_ID,
RSL.TO_ORGANIZATION_ID,
RSL.TO_SUBINVENTORY,
RSL.MMT_TRANSACTION_ID,
RSL.UNIT_OF_MEASURE,
RSL.QUANTITY_SHIPPED,
RSL.QUANTITY_RECEIVED,
RSL.SOURCE_DOCUMENT_CODE,
RSL.DESTINATION_TYPE_CODE,
RSL.ITEM_ID,
RSL.ITEM_DESCRIPTION

FROM PO_REQUISITION_HEADERS_ALL PRH, -- requisition headers
PO_REQUISITION_LINES_ALL PRL, -- requisition lines
OE_DROP_SHIP_SOURCES ODSS, -- drop ship
OE_ORDER_LINES_ALL OL, -- sales order lines
OE_ORDER_HEADERS_ALL OH, -- sales order headers
PO_LINES_ALL POL, -- purchasing order lines
PO_HEADERS_ALL POH, -- purchasing order headers
PO_LINE_LOCATIONS_ALL PLL, -- lo trinh giao hang
RCV_SHIPMENT_LINES RSL, -- receipt lines
RCV_SHIPMENT_HEADERS RSH
WHERE ODSS.LINE_ID = OL.LINE_ID -- drop ship link voi SO line
AND ODSS.HEADER_ID = OH.HEADER_ID -- drop ship link voi SO header
AND ODSS.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID -- drop ship link voi requisition header
AND ODSS.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID -- drop ship link voi requisition lines
AND ODSS.PO_LINE_ID = POL.PO_LINE_ID -- drop ship link voi PO lines
AND ODSS.PO_HEADER_ID = POH.PO_HEADER_ID -- drop ship link voi PO header
AND POL.PO_LINE_ID = PLL.PO_LINE_ID -- PO line link voi LineLocation
AND PLL.LINE_LOCATION_ID = RSL.PO_LINE_LOCATION_ID -- Line Location link receipt lines
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID -- receipt lines link voi receipt header