Transfers Orders are used in Oracle Fusion SCM for the intercompany material transfers, similar to the Internal Requisition/Internal Sales Order process in the Oracle EBS.
No cost markup for the material transfers within the BU. Across the Business Units/Legal entities, Transfer Pricing policies can be setup based on the Cost Plus markup / List Less discounts.
Below is the SQL Query to get all the transfer orders info and the BI Publisher report can be built based on the query for all the Open, Closed and In-Transit Transfer orders, including shipping & receiving info. The query/report is very useful to monitor internal material movements.
SELECT toh.HEADER_NUMBER TRANSFER_ORDER,
— toh.SOURCE_TYPE_LOOKUP,
toh.ORDERED_DATE,
tol.LINE_NUMBER LINE_NUM,
— (CAST (tol.Line_Number AS VARCHAR2(150))) AS LINE_NUMBER,
ESI.ITEM_NUMBER ITEM,
SourceOrg.ORGANIZATION_CODE Source_Org,
DestOrg.ORGANIZATION_CODE Destination_Org,
— tol.STATUS_LOOKUP, — TOL status
(SELECT MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE = ‘INV_TO_LINE_STATUS’ and lookup_code = tol.STATUS_LOOKUP) LINE_STATUS,
tol.QTY_UOM_CODE UOM,
tol.REQUESTED_QTY,
tol.SHIPPED_QTY,
tol.RECEIVED_QTY,
tol.DELIVERED_QTY,
tol.DESTINATION_SUBINVENTORY_CODE,
–tol.DESTINATION_TYPE_LOOKUP,
— tol.INVENTORY_ITEM_ID,
(SELECT meaning FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE = ‘INV_TO_TXN_ORIGIN’ and lookup_code = tol.SOURCE_TYPE_LOOKUP) Trx_Origin_Type,
tol.NOTE_TO_SUPPLIER,
— tol.ATTRIBUTE_CHAR1 SERIAL_NUMBER ,
SHIP.delivery_name SHIPMENT_NUM,
SHIP.ACTUAL_SHIP_DATE ACTUAL_SHIP_DATE,
RCV.RSH_RECEIPT_NUM RECEIPT_NUM,
RCV.RCV_TRX_DATE RECEIVED_DATE
FROM INV_TRANSFER_ORDER_HEADERS toh,
INV_TRANSFER_ORDER_LINES tol,
INV_ORG_PARAMETERS_V SourceOrg,
INV_ORG_PARAMETERS_V DestOrg,
EGP_SYSTEM_ITEMS_B_V 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
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
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) SHIP
WHERE toh.HEADER_ID = tol.HEADER_ID
AND tol.SOURCE_ORGANIZATION_ID = SourceOrg.ORGANIZATION_ID
AND tol.DESTINATION_ORGANIZATION_ID = DestOrg.ORGANIZATION_ID
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 tol.STATUS_LOOKUP <> ‘CANCELED’
— AND SourceOrg.ORGANIZATION_CODE = :P_SOURCE_ORG
— AND DestOrg.ORGANIZATION_CODE = :P_DEST_ORG
AND toh.HEADER_NUMBER IN (‘1016372′,’1182447’)
ORDER BY toh.HEADER_NUMBER, tol.LINE_NUMBER
Sample Output:
