Sql query to get Transfer order details
SELECT DISTINCT
poh.po_header_id,
poh.segment1 AS po_number,
pol.po_line_id,
pol.line_num,
pol.item_id,
pol.quantity line_quantity,
pod.po_distribution_id,
pod.distribution_num,
pod.quantity_ordered,
pll.quantity schedule_qty,
pll.need_by_date,
pll.promised_date,
pll.requested_ship_date
FROM
po_headers_all poh,
po_lines_all pol,
po_distributions_all pod,
po_line_locations_all pll
WHERE
pol.po_header_id = poh.po_header_id
AND pod.po_line_id = pol.po_line_id
AND pll.po_line_id = pol.po_line_id
AND pod.line_location_id = pll.line_location_id and poh.segment1 =:PURCHASE_ORDER_NUMBER ORDER BY poh.segment1
/ SELECT
123 transaction_id,
123 accounting_id,
123 wh_reporting_code,
toh.header_number doc_id,
tol.scheduled_ship_date shipment_date,
esi.item_number vendor_material_num,
esi.description privatelabelname,
tol.shipped_qty lineqty,
ship.shipped_quantity --RCV.QUANTITY
,
CASE
WHEN ceil(ship.shipped_quantity / 230) = qty.seqnum THEN
ship.shipped_quantity - compareqtyfrom + 1
ELSE
230
END qtyordered --, QTY.SEQNUM, CEIL(SHIP.SHIPPED_QUANTITY/230) PALLETCOUNT, SHIP.SHIPPED_QUANTITY
,
tol.qty_uom_code uom,
NULL orderno,
NULL line_num,
NULL "Reference_num",
NULL "Vendor_Order_num",
ROWNUM pallet_id,
0 "Pallet_Weight / Qty",
0 net_lbs_orders_for_item,
ship.actual_ship_date actual_shipment_date,
NULL departure_time,
NULL transportation_method,
NULL scac,
NULL "Routing",
NULL "Equipment_num",
houd.name "Ship_To_Name",
hp_dest.address1 "Ship_To_Address",
hp_dest.city "Ship_To_City",
hp_dest.state "Ship_To_State",
hp_dest.postal_code "Ship_To_Zip",
hp_dest.country "Ship_To_Country",
123 "Store_num",
123 "DC_num",
NULL "Material_Storage_Location",
NULL "Plant_ID",
NULL "No_of_Units_Shipped",
NULL "Weight",
toh.header_number "Shipment_ID",
toh.header_number "PO_num" --SHIP.CUST_PO_NUMBER
,
123 "GTIN_num",
ship.gross_weight "Gross_Weight",
hous.name "Ship_From_Name",
hp_source.address1 "Ship_From_Address",
hp_source.city "Ship_From_City",
hp_source.state "Ship_From_State",
hp_source.postal_code "Ship_From_Zip",
hp_source.country "Ship_From_Country",
ship.lot_number "Lot_num",
123 "Production_Date",
123 "Expiration_Date",
hp_source.address1 ship_from_address,
hp_source.city ship_from_city,
hp_source.state ship_from_state,
hp_source.postal_code ship_from_zip,
hp_source.country ship_from_country,
ship.lot_number lot_num
FROM
inv_transfer_order_headers toh,
inv_transfer_order_lines tol,
inv_org_parameters_v sourceorg,
hr_organization_units_f_tl hous,
inv_org_parameters_v destorg,
hr_organization_units_f_tl houd,
egp_system_items_vl esi
/*, ( SELECT RSH.RECEIPT_NUM AS RSH_RECEIPT_NUM, RT.TRANSACTION_DATE AS RCV_TRX_DATE
, RSL.TO_ORGANIZATION_ID RCV_ORG, RT.TRANSFER_ORDER_LINE_ID, RT.QUANTITY
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, RCV_TRANSACTIONS RT
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_TYPE = 'RECEIVE') RCV */,
(
SELECT
wnd.delivery_name,
wnd.actual_ship_date,
wdd.sales_order_number,
wdd.sales_order_line_number,
wdd.shipped_quantity,
wdd.lot_number,
wdd.cust_po_number,
wdd.gross_weight,
wdd.source_line_id
FROM
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE
wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_line_type IN ( 'TRANSFER_ORDER', 'TRANSFER_ORDER_RETURN' )
) ship --- , DOO_LINES_ALL DLA, DOO_Fulfill_LINES_ALL DFLA
,
hz_parties hp_source,
hz_parties hp_dest,
(
SELECT
ROWNUM seqnum,
( ROWNUM - 1 ) * 230 compareqty,
( ROWNUM - 1 ) * 230 + 1 compareqtyfrom,
ROWNUM * 230 compareqtyto
FROM
inv_transfer_order_headers
WHERE
ROWNUM <= 1000
) qty
WHERE
tol.source_organization_id = '3034300'
AND tol.destination_organization_id = '3534348'
AND toh.header_id = tol.header_id
AND tol.source_organization_id = sourceorg.organization_id (+)
AND sourceorg.organization_id = hous.organization_id
AND hous.language = 'US'
AND sysdate BETWEEN hous.effective_start_date AND hous.effective_end_date
AND tol.destination_organization_id = destorg.organization_id
(+)
AND destorg.organization_id = houd.organization_id
AND houd.language = 'US'
AND sysdate BETWEEN houd.effective_start_date AND
houd.effective_end_date
AND
tol.inventory_item_id = esi.inventory_item_id
AND
tol.destination_organization_id = esi.organization_id --- AND TOL.LINE_ID = RCV.TRANSFER_ORDER_LINE_ID(+)
AND
toh.header_number = ship.sales_order_number
(
+
)
AND
to_char
(
tol.line_number
) = ship.sales_order_line_number
(
+
) --- AND dfla.LINE_ID = dla.LINE_ID AND DFLA.FULFILL_LINE_ID = SHIP.SOURCE_LINE_ID (+)
--- AND RSL.REQUISITION_LINE_ID = DLA.SOURCE_DOCUMENT_LINE_ID (+)
AND
tol.status_lookup <> 'CANCELED'
AND
destorg.party_id = hp_dest.party_id
(
+
)
AND
sourceorg.party_id = hp_source.party_id
(
+
)
AND
qty.compareqty < ship.shipped_quantity
AND
toh.header_number = '12002'
ORDER BY
toh.header_number,
tol.line_number,
qty.seqnum
