MSC_AP_TRANSFER_ORDERS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
TO_LINE_ID ORDER_NUMBER TO_LINE_NUMBER SHIPMENT_HEADER_NUM SHIPMENT_LINE_NUM SHIPMENT_LINE_ID RECEIPT_NUM DISPOSITION_ID INTERFACE_STATUS_LOOKUP ORDER_TYPE FG_ORDER_TYPE EXPENSE_TRANSFER SOURCE_ORGANIZATION_ID FROM_ORGANIZATION_CODE SOURCE_SUBINVENTORY_CODE ORGANIZATION_ID ORGANIZATION_CODE SUBINVENTORY_CODE INVENTORY_ITEM_ID ITEM_NAME CARRIER_ID CARRIER_NAME MODE_OF_TRANSPORT MODE_OF_TRANSPORT_NAME SERVICE_LEVEL SERVICE_LEVEL_NAME NEED_BY_DATE NEW_SHIP_DATE NEW_DOCK_DATE NEW_SCHEDULE_DATE NEW_ORDER_PLACEMENT_DATE CONVERSION_RATE NEW_ORDER_QUANTITY COMPLETED_QUANTITY DELIVERY_PRICE REVISION FIRM_PLANNED_TYPE SUPPLY_SOURCE_ID FULFILL_ORCHESTRATION_REQUIRED SALES_ORDER_HEADER_ID SALES_ORDER_LINE_ID ORCH_ORDER_HEADER_ID ORCH_ORDER_LINE_ID SCHEDULE_SHIP_DATE SCHEDULE_ARRIVAL_DATE REQUEST_DATE ORDER_DATE_TYPE_CODE DEMAND_SOURCE_TYPE ORDERED_UOM ITEM_TYPE_CODE ITEM_SUB_TYPE_CODE SOURCE_DOCUMENT_NUMBER SOURCE_DOCUMENT_LINE_NUMBER INTERFACED_TO_DOO TOL_ROWID TOH_ROWID SUP_ROWID DOO_ROWID RSL_ROWID ORDER_STATUS

Name

TO_LINE_ID

ORDER_NUMBER

TO_LINE_NUMBER

SHIPMENT_HEADER_NUM

SHIPMENT_LINE_NUM

SHIPMENT_LINE_ID

RECEIPT_NUM

DISPOSITION_ID

INTERFACE_STATUS_LOOKUP

ORDER_TYPE

FG_ORDER_TYPE

EXPENSE_TRANSFER

SOURCE_ORGANIZATION_ID

FROM_ORGANIZATION_CODE

SOURCE_SUBINVENTORY_CODE

ORGANIZATION_ID

ORGANIZATION_CODE

SUBINVENTORY_CODE

INVENTORY_ITEM_ID

ITEM_NAME

CARRIER_ID

CARRIER_NAME

MODE_OF_TRANSPORT

MODE_OF_TRANSPORT_NAME

SERVICE_LEVEL

SERVICE_LEVEL_NAME

NEED_BY_DATE

NEW_SHIP_DATE

NEW_DOCK_DATE

NEW_SCHEDULE_DATE

NEW_ORDER_PLACEMENT_DATE

CONVERSION_RATE

NEW_ORDER_QUANTITY

COMPLETED_QUANTITY

DELIVERY_PRICE

REVISION

FIRM_PLANNED_TYPE

SUPPLY_SOURCE_ID

FULFILL_ORCHESTRATION_REQUIRED

SALES_ORDER_HEADER_ID

SALES_ORDER_LINE_ID

ORCH_ORDER_HEADER_ID

ORCH_ORDER_LINE_ID

SCHEDULE_SHIP_DATE

SCHEDULE_ARRIVAL_DATE

REQUEST_DATE

ORDER_DATE_TYPE_CODE

DEMAND_SOURCE_TYPE

ORDERED_UOM

ITEM_TYPE_CODE

ITEM_SUB_TYPE_CODE

SOURCE_DOCUMENT_NUMBER

SOURCE_DOCUMENT_LINE_NUMBER

INTERFACED_TO_DOO

TOL_ROWID

TOH_ROWID

SUP_ROWID

DOO_ROWID

RSL_ROWID

ORDER_STATUS

Query

SQL_Statement
SELECT TOL.LINE_ID TO_LINE_ID, TOH.HEADER_NUMBER ORDER_NUMBER, TOL.LINE_NUMBER TO_LINE_NUMBER, RSH.SHIPMENT_NUM SHIPMENT_HEADER_NUM, RSL.LINE_NUM SHIPMENT_LINE_NUM, RSL.SHIPMENT_LINE_ID SHIPMENT_LINE_ID, TO_CHAR(sup.RCV_TRANSACTION_ID) RECEIPT_NUM, TOL.HEADER_ID DISPOSITION_ID,TOL.INTERFACE_STATUS_LOOKUP, DECODE (supply_type_code,'TRANSFER_ORDER',94,'SHIPMENT',11,'RECEIVING',8) ORDER_TYPE, DECODE (supply_type_code,'TRANSFER_ORDER',9400,'SHIPMENT',9411,'RECEIVING',9408)FG_ORDER_TYPE, 2 EXPENSE_TRANSFER, TOL.SOURCE_ORGANIZATION_ID, shporg.organization_code FROM_ORGANIZATION_CODE, TOL.SOURCE_SUBINVENTORY_CODE, SUP.TO_ORGANIZATION_ID ORGANIZATION_ID, recvorg.organization_code, TOL.DESTINATION_SUBINVENTORY_CODE SUBINVENTORY_CODE, SUP.ITEM_ID INVENTORY_ITEM_ID, ITEMSV.ITEM_NUMBER ITEM_NAME, TOL.FREIGHT_CARRIER_ID CARRIER_ID, HZP.party_name CARRIER_NAME, TOL.MODE_OF_TRANSPORT_LOOKUP MODE_OF_TRANSPORT, FNLMOD.meaning MODE_OF_TRANSPORT_NAME, TOL.SERVICE_LEVEL_LOOKUP SERVICE_LEVEL, FNLSRL.meaning SERVICE_LEVEL_NAME, SUP.NEED_BY_DATE NEED_BY_DATE, DECODE (SUPPLY_TYPE_CODE,'TRANSFER_ORDER',TOL.SCHEDULED_SHIP_DATE,'SHIPMENT',RSH.SHIPPED_DATE,'RECEIVING',RSH.SHIPPED_DATE,NULL) NEW_SHIP_DATE, DECODE (SUPPLY_TYPE_CODE,'TRANSFER_ORDER',SUP.RECEIPT_DATE,'SHIPMENT',RSH.EXPECTED_RECEIPT_DATE,'RECEIVING',SUP.RECEIPT_DATE,NULL) NEW_DOCK_DATE, SUP.EXPECTED_DELIVERY_DATE NEW_SCHEDULE_DATE, TOH.ORDERED_DATE NEW_ORDER_PLACEMENT_DATE, 1 conversion_rate, SUP.TO_ORG_PRIMARY_QUANTITY NEW_ORDER_QUANTITY, 0 completed_quantity, TOL.UNIT_PRICE DELIVERY_PRICE, TOL.ITEM_REVISION REVISION, DECODE(TOL.FIRM_FLAG, '1', 1, 'Y', 1, 2) FIRM_PLANNED_TYPE, SUP.SUPPLY_SOURCE_ID SUPPLY_SOURCE_ID, DECODE(TOH.FULFILL_ORCHESTRATION_REQUIRED, 'Y' , 1 , 2) FULFILL_ORCHESTRATION_REQUIRED, null SALES_ORDER_HEADER_ID, null SALES_ORDER_LINE_ID , null orch_order_header_id, null ORCH_ORDER_LINE_ID, null schedule_ship_date, null schedule_arrival_date, null REQUEST_DATE , null ORDER_DATE_TYPE_CODE , null DEMAND_SOURCE_TYPE, null ORDERED_UOM, null ITEM_TYPE_CODE, null ITEM_SUB_TYPE_CODE, null SOURCE_DOCUMENT_NUMBER, null SOURCE_DOCUMENT_LINE_NUMBER, null INTERFACED_TO_DOO, TOL.ROWID TOL_ROWID, TOH.ROWID TOH_ROWID, SUP.ROWID SUP_ROWID, null DOO_ROWID, RSL.ROWID RSL_ROWID, DECODE(supply_type_code, 'TRANSFER_ORDER', (CASE WHEN (NVL(tol.shipped_qty,0) > 0) THEN 1 WHEN toh.fulfill_orchestration_required = 'Y' THEN 3 ELSE NULL END), NULL) ORDER_STATUS FROM INV_TRANSFER_ORDER_LINES TOL, INV_SUPPLY SUP, INV_TRANSFER_ORDER_HEADERS TOH, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, INV_ORG_PARAMETERS SHPORG, INV_ORG_PARAMETERS RECVORG, MSC_PLANNED_EGP_SYSTEM_ITEMS_V ITEMSV, HZ_PARTIES HZP, FND_LOOKUP_VALUES_VL FNLMOD, FND_LOOKUP_VALUES_VL FNLSRL WHERE TOL.STATUS_LOOKUP = 'OPEN' AND SUP.TRANSFER_ORDER_LINE_ID = TOL.LINE_ID AND TOL.HEADER_ID = TOH.HEADER_ID AND ( ( SUP.SUPPLY_TYPE_CODE = 'TRANSFER_ORDER' AND sup.DESTINATION_TYPE_CODE <> 'EXPENSE' AND TOL.INTERFACE_STATUS_LOOKUP IN ('INT_WSH','INT_DOO') ) OR ( ( sup.supply_type_code = 'SHIPMENT' OR sup.supply_type_code = 'RECEIVING' ) AND sup.DESTINATION_TYPE_CODE <> 'EXPENSE' ) ) AND SUP.Transfer_Order_Line_Id IS NOT NULL AND RSH.shipment_header_id (+) = sup.shipment_header_id AND RSL.SHIPMENT_LINE_ID(+) = SUP.SHIPMENT_LINE_ID AND shporg.organization_id = TOL.SOURCE_ORGANIZATION_ID AND RECVORG.ORGANIZATION_ID = SUP.TO_ORGANIZATION_ID AND ITEMSV.INVENTORY_ITEM_ID = SUP.ITEM_ID AND ITEMSV.organization_id = SUP.TO_ORGANIZATION_ID AND HZP.party_id(+) = TOL.FREIGHT_CARRIER_ID AND FNLMOD.LOOKUP_CODE(+) = TOL.MODE_OF_TRANSPORT_LOOKUP AND FNLMOD.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT' AND FNLMOD.VIEW_APPLICATION_ID(+) = 0 AND FNLMOD.SET_ID(+) = 0 AND FNLSRL.LOOKUP_CODE(+) = TOL.SERVICE_LEVEL_LOOKUP AND FNLSRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS' AND FNLSRL.VIEW_APPLICATION_ID(+) = 0 AND FNLSRL.SET_ID(+) = 0 UNION ALL SELECT TOL.LINE_ID TO_LINE_ID, TOH.HEADER_NUMBER ORDER_NUMBER, TOL.LINE_NUMBER TO_LINE_NUMBER, null SHIPMENT_HEADER_NUM, null SHIPMENT_LINE_NUM, null SHIPMENT_LINE_ID, null RECEIPT_NUM, TOL.HEADER_ID DISPOSITION_ID, TOL.INTERFACE_STATUS_LOOKUP, 94 ORDER_TYPE, 9400 FG_ORDER_TYPE, 1 EXPENSE_TRANSFER, TOL.SOURCE_ORGANIZATION_ID, shporg.organization_code FROM_ORGANIZATION_CODE, TOL.SOURCE_SUBINVENTORY_CODE, TOL.DESTINATION_ORGANIZATION_ID ORGANIZATION_ID, recvorg.organization_code, TOL.DESTINATION_SUBINVENTORY_CODE SUBINVENTORY_CODE, TOL.INVENTORY_ITEM_ID, ITEMSV.ITEM_NUMBER ITEM_NAME, TOL.FREIGHT_CARRIER_ID CARRIER_ID, HZP.party_name CARRIER_NAME, TOL.MODE_OF_TRANSPORT_LOOKUP MODE_OF_TRANSPORT, FNLMOD.meaning MODE_OF_TRANSPORT_NAME, TOL.SERVICE_LEVEL_LOOKUP SERVICE_LEVEL, FNLSRL.meaning SERVICE_LEVEL_NAME, TOL.NEED_BY_DATE NEED_BY_DATE, DECODE(FULFILLMENT.rowid,null,TOL.SCHEDULED_SHIP_DATE,FULFILLMENT.schedule_ship_date) NEW_SHIP_DATE, NULL NEW_DOCK_DATE, DECODE(FULFILLMENT.rowid,null,TOL.SCHEDULED_SHIP_DATE,FULFILLMENT.schedule_arrival_date) NEW_SCHEDULE_DATE, TOH.ORDERED_DATE NEW_ORDER_PLACEMENT_DATE, INV_CONVERT.INV_UM_CONVERT( item_id => TOL.INVENTORY_ITEM_ID, precision => 10, from_quantity => NULL, from_unit => TOL.qty_uom_code, to_unit => ITEMSV.UOM_CODE, from_name => NULL, to_name => NULL ) conversion_rate, TOL.requested_qty NEW_ORDER_QUANTITY, NVL(TOL.SHIPPED_QTY,0) COMPLETED_QUANTITY, TOL.UNIT_PRICE DELIVERY_PRICE, TOL.ITEM_REVISION REVISION, DECODE(TOL.FIRM_FLAG, '1', 1, 'Y', 1, 2) FIRM_PLANNED_TYPE, TOL.LINE_ID SUPPLY_SOURCE_ID, DECODE(TOH.FULFILL_ORCHESTRATION_REQUIRED, 'Y' , 1 , 2) FULFILL_ORCHESTRATION_REQUIRED, 'TOE_'\|\|TOL.HEADER_ID SALES_ORDER_HEADER_ID, 'TOE_'\|\|TOL.line_id SALES_ORDER_LINE_ID , FULFILLMENT.header_id orch_order_header_id, FULFILLMENT.LINE_ID ORCH_ORDER_LINE_ID, FULFILLMENT.schedule_ship_date schedule_ship_date, FULFILLMENT.schedule_arrival_date schedule_arrival_date, DECODE(FULFILLMENT.REQUEST_ARRIVAL_DATE,null,FULFILLMENT.REQUEST_SHIP_DATE,FULFILLMENT.REQUEST_ARRIVAL_DATE) REQUEST_DATE , DECODE(FULFILLMENT.rowid, null, 2, DECODE(FULFILLMENT.REQUEST_ARRIVAL_DATE,null,1,2)) ORDER_DATE_TYPE_CODE , 1 DEMAND_SOURCE_TYPE, ITEMSV.UOM_CODE ORDERED_UOM, DECODE(FULFILLMENT.ITEM_TYPE_CODE,'1','MODEL','2','OPTION_CLASS','4','STANDARD') ITEM_TYPE_CODE, FULFILLMENT.ITEM_SUB_TYPE_CODE ITEM_SUB_TYPE_CODE, FULFILLMENT.SOURCE_ORDER_NUMBER SOURCE_DOCUMENT_NUMBER, FULFILLMENT.SOURCE_LINE_NUMBER SOURCE_DOCUMENT_LINE_NUMBER, DECODE(FULFILLMENT.rowid,null,2,1) INTERFACED_TO_DOO, TOL.ROWID TOL_ROWID, TOH.ROWID TOH_ROWID, NULL SUP_ROWID, FULFILLMENT.rowid DOO_ROWID, NULL RSL_ROWID, NULL ORDER_STATUS FROM INV_TRANSFER_ORDER_LINES TOL, INV_TRANSFER_ORDER_HEADERS TOH, DOO_FULFILL_LINES_ALL FULFILLMENT, DOO_DOCUMENT_REFERENCES DOC, INV_ORG_PARAMETERS SHPORG, INV_ORG_PARAMETERS RECVORG, MSC_PLANNED_EGP_SYSTEM_ITEMS ITEMSV, HZ_PARTIES HZP, FND_LOOKUP_VALUES_VL FNLMOD, FND_LOOKUP_VALUES_VL FNLSRL WHERE TOL.STATUS_LOOKUP = 'OPEN' AND TOL.HEADER_ID = TOH.HEADER_ID AND TOL.DESTINATION_TYPE_LOOKUP = 'EXPENSE' AND TOL.INTERFACE_STATUS_LOOKUP IN ('INT_WSH','INT_DOO') AND FULFILLMENT.SOURCE_ORDER_ID(+) = TO_CHAR(TOL.HEADER_ID) AND FULFILLMENT.SOURCE_LINE_ID(+) = TO_CHAR(TOL.LINE_ID) AND DOC.HEADER_ID(+) = FULFILLMENT.HEADER_ID AND DOC.LINE_ID(+) = FULFILLMENT.LINE_ID AND DOC.FULFILL_LINE_ID(+) = FULFILLMENT.FULFILL_LINE_ID AND DOC_REF_TYPE(+) = 'INTERNAL_ORDER' AND DOC.DOC_CONTEXT_ID(+) = 'EXPENSE' AND FULFILLMENT.CANCELED_FLAG(+) <> 'Y' AND FULFILLMENT.category_code(+) <> 'RETURN' AND ( FULFILLMENT.rowid IS NULL OR EXISTS ( SELECT 1 FROM DOO_HEADERS_ALL HEADERS, DOO_LINES_ALL LINES WHERE HEADERS.SUBMITTED_FLAG = 'Y' AND FULFILLMENT.LINE_ID = LINES.LINE_ID AND LINES.HEADER_ID = HEADERS.HEADER_ID ) ) AND FULFILLMENT.shipped_qty(+) IS NULL AND FULFILLMENT.schedule_ship_date(+) IS NOT NULL AND TOL.SOURCE_ORGANIZATION_ID <> TOL.DESTINATION_ORGANIZATION_ID AND shporg.organization_id = TOL.SOURCE_ORGANIZATION_ID AND RECVORG.ORGANIZATION_ID = TOL.DESTINATION_ORGANIZATION_ID AND ITEMSV.INVENTORY_ITEM_ID = TOL.INVENTORY_ITEM_ID AND ITEMSV.organization_id = TOL.DESTINATION_ORGANIZATION_ID AND HZP.party_id(+) = TOL.FREIGHT_CARRIER_ID AND FNLMOD.LOOKUP_CODE(+) = TOL.MODE_OF_TRANSPORT_LOOKUP AND FNLMOD.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT' AND FNLMOD.VIEW_APPLICATION_ID(+) = 0 AND FNLMOD.SET_ID(+) = 0 AND FNLSRL.LOOKUP_CODE(+) = TOL.SERVICE_LEVEL_LOOKUP AND FNLSRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS' AND FNLSRL.VIEW_APPLICATION_ID(+) = 0 AND FNLSRL.SET_ID(+) = 0

SQL_Statement

SELECT

TOL.LINE_ID TO_LINE_ID,

TOH.HEADER_NUMBER ORDER_NUMBER,

TOL.LINE_NUMBER TO_LINE_NUMBER,

RSH.SHIPMENT_NUM SHIPMENT_HEADER_NUM,

RSL.LINE_NUM SHIPMENT_LINE_NUM,

RSL.SHIPMENT_LINE_ID SHIPMENT_LINE_ID,

TO_CHAR(sup.RCV_TRANSACTION_ID) RECEIPT_NUM,

TOL.HEADER_ID DISPOSITION_ID,TOL.INTERFACE_STATUS_LOOKUP,

DECODE (supply_type_code,'TRANSFER_ORDER',94,'SHIPMENT',11,'RECEIVING',8) ORDER_TYPE,

DECODE (supply_type_code,'TRANSFER_ORDER',9400,'SHIPMENT',9411,'RECEIVING',9408)FG_ORDER_TYPE,

2 EXPENSE_TRANSFER,

TOL.SOURCE_ORGANIZATION_ID,

shporg.organization_code FROM_ORGANIZATION_CODE,

TOL.SOURCE_SUBINVENTORY_CODE,

SUP.TO_ORGANIZATION_ID ORGANIZATION_ID,

recvorg.organization_code,

TOL.DESTINATION_SUBINVENTORY_CODE SUBINVENTORY_CODE,

SUP.ITEM_ID INVENTORY_ITEM_ID,

ITEMSV.ITEM_NUMBER ITEM_NAME,

TOL.FREIGHT_CARRIER_ID CARRIER_ID,

HZP.party_name CARRIER_NAME,

TOL.MODE_OF_TRANSPORT_LOOKUP MODE_OF_TRANSPORT,

FNLMOD.meaning MODE_OF_TRANSPORT_NAME,

TOL.SERVICE_LEVEL_LOOKUP SERVICE_LEVEL,

FNLSRL.meaning SERVICE_LEVEL_NAME,

SUP.NEED_BY_DATE NEED_BY_DATE,

DECODE (SUPPLY_TYPE_CODE,'TRANSFER_ORDER',TOL.SCHEDULED_SHIP_DATE,'SHIPMENT',RSH.SHIPPED_DATE,'RECEIVING',RSH.SHIPPED_DATE,NULL) NEW_SHIP_DATE,

DECODE (SUPPLY_TYPE_CODE,'TRANSFER_ORDER',SUP.RECEIPT_DATE,'SHIPMENT',RSH.EXPECTED_RECEIPT_DATE,'RECEIVING',SUP.RECEIPT_DATE,NULL) NEW_DOCK_DATE,

SUP.EXPECTED_DELIVERY_DATE NEW_SCHEDULE_DATE,

TOH.ORDERED_DATE NEW_ORDER_PLACEMENT_DATE,

1 conversion_rate,

SUP.TO_ORG_PRIMARY_QUANTITY NEW_ORDER_QUANTITY,

0 completed_quantity,

TOL.UNIT_PRICE DELIVERY_PRICE,

TOL.ITEM_REVISION REVISION,

DECODE(TOL.FIRM_FLAG, '1', 1, 'Y', 1, 2) FIRM_PLANNED_TYPE,

SUP.SUPPLY_SOURCE_ID SUPPLY_SOURCE_ID,

DECODE(TOH.FULFILL_ORCHESTRATION_REQUIRED, 'Y' , 1 , 2) FULFILL_ORCHESTRATION_REQUIRED,

null SALES_ORDER_HEADER_ID,

null SALES_ORDER_LINE_ID ,

null orch_order_header_id,

null ORCH_ORDER_LINE_ID,

null schedule_ship_date,

null schedule_arrival_date,

null REQUEST_DATE ,

null ORDER_DATE_TYPE_CODE ,

null DEMAND_SOURCE_TYPE,

null ORDERED_UOM,

null ITEM_TYPE_CODE,

null ITEM_SUB_TYPE_CODE,

null SOURCE_DOCUMENT_NUMBER,

null SOURCE_DOCUMENT_LINE_NUMBER,

null INTERFACED_TO_DOO,

TOL.ROWID TOL_ROWID,

TOH.ROWID TOH_ROWID,

SUP.ROWID SUP_ROWID,

null DOO_ROWID,

RSL.ROWID RSL_ROWID,

DECODE(supply_type_code, 'TRANSFER_ORDER',

(CASE WHEN (NVL(tol.shipped_qty,0) > 0) THEN 1

WHEN toh.fulfill_orchestration_required = 'Y' THEN 3

ELSE NULL END), NULL) ORDER_STATUS

FROM

INV_TRANSFER_ORDER_LINES TOL,

INV_SUPPLY SUP,

INV_TRANSFER_ORDER_HEADERS TOH,

RCV_SHIPMENT_HEADERS RSH,

RCV_SHIPMENT_LINES RSL,

INV_ORG_PARAMETERS SHPORG,

INV_ORG_PARAMETERS RECVORG,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V ITEMSV,

HZ_PARTIES HZP,

FND_LOOKUP_VALUES_VL FNLMOD,

FND_LOOKUP_VALUES_VL FNLSRL

WHERE TOL.STATUS_LOOKUP = 'OPEN'

AND SUP.TRANSFER_ORDER_LINE_ID = TOL.LINE_ID

AND TOL.HEADER_ID = TOH.HEADER_ID

AND (

(

SUP.SUPPLY_TYPE_CODE = 'TRANSFER_ORDER'

AND sup.DESTINATION_TYPE_CODE <> 'EXPENSE'

AND TOL.INTERFACE_STATUS_LOOKUP IN ('INT_WSH','INT_DOO')

)

OR (

(

sup.supply_type_code = 'SHIPMENT'

OR

sup.supply_type_code = 'RECEIVING'

)

AND sup.DESTINATION_TYPE_CODE <> 'EXPENSE'

)

AND SUP.Transfer_Order_Line_Id IS NOT NULL

AND RSH.shipment_header_id (+) = sup.shipment_header_id

AND RSL.SHIPMENT_LINE_ID(+) = SUP.SHIPMENT_LINE_ID

AND shporg.organization_id = TOL.SOURCE_ORGANIZATION_ID

AND RECVORG.ORGANIZATION_ID = SUP.TO_ORGANIZATION_ID

AND ITEMSV.INVENTORY_ITEM_ID = SUP.ITEM_ID

AND ITEMSV.organization_id = SUP.TO_ORGANIZATION_ID

AND HZP.party_id(+) = TOL.FREIGHT_CARRIER_ID

AND FNLMOD.LOOKUP_CODE(+) = TOL.MODE_OF_TRANSPORT_LOOKUP

AND FNLMOD.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT'

AND FNLMOD.VIEW_APPLICATION_ID(+) = 0

AND FNLMOD.SET_ID(+) = 0

AND FNLSRL.LOOKUP_CODE(+) = TOL.SERVICE_LEVEL_LOOKUP

AND FNLSRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS'

AND FNLSRL.VIEW_APPLICATION_ID(+) = 0

AND FNLSRL.SET_ID(+) = 0

UNION ALL

SELECT

TOL.LINE_ID TO_LINE_ID,

TOH.HEADER_NUMBER ORDER_NUMBER,

TOL.LINE_NUMBER TO_LINE_NUMBER,

null SHIPMENT_HEADER_NUM,

null SHIPMENT_LINE_NUM,

null SHIPMENT_LINE_ID,

null RECEIPT_NUM,

TOL.HEADER_ID DISPOSITION_ID,

TOL.INTERFACE_STATUS_LOOKUP,

94 ORDER_TYPE,

9400 FG_ORDER_TYPE,

1 EXPENSE_TRANSFER,

TOL.SOURCE_ORGANIZATION_ID,

shporg.organization_code FROM_ORGANIZATION_CODE,

TOL.SOURCE_SUBINVENTORY_CODE,

TOL.DESTINATION_ORGANIZATION_ID ORGANIZATION_ID,

recvorg.organization_code,

TOL.DESTINATION_SUBINVENTORY_CODE SUBINVENTORY_CODE,

TOL.INVENTORY_ITEM_ID,

ITEMSV.ITEM_NUMBER ITEM_NAME,

TOL.FREIGHT_CARRIER_ID CARRIER_ID,

HZP.party_name CARRIER_NAME,

TOL.MODE_OF_TRANSPORT_LOOKUP MODE_OF_TRANSPORT,

FNLMOD.meaning MODE_OF_TRANSPORT_NAME,

TOL.SERVICE_LEVEL_LOOKUP SERVICE_LEVEL,

FNLSRL.meaning SERVICE_LEVEL_NAME,

TOL.NEED_BY_DATE NEED_BY_DATE,

DECODE(FULFILLMENT.rowid,null,TOL.SCHEDULED_SHIP_DATE,FULFILLMENT.schedule_ship_date) NEW_SHIP_DATE,

NULL NEW_DOCK_DATE,

DECODE(FULFILLMENT.rowid,null,TOL.SCHEDULED_SHIP_DATE,FULFILLMENT.schedule_arrival_date) NEW_SCHEDULE_DATE,

TOH.ORDERED_DATE NEW_ORDER_PLACEMENT_DATE,

INV_CONVERT.INV_UM_CONVERT(

item_id => TOL.INVENTORY_ITEM_ID,

precision => 10,

from_quantity => NULL,

from_unit => TOL.qty_uom_code,

to_unit => ITEMSV.UOM_CODE,

from_name => NULL,

to_name => NULL

) conversion_rate,

TOL.requested_qty NEW_ORDER_QUANTITY,

NVL(TOL.SHIPPED_QTY,0) COMPLETED_QUANTITY,

TOL.UNIT_PRICE DELIVERY_PRICE, TOL.ITEM_REVISION REVISION,

DECODE(TOL.FIRM_FLAG, '1', 1, 'Y', 1, 2) FIRM_PLANNED_TYPE,

TOL.LINE_ID SUPPLY_SOURCE_ID,

DECODE(TOH.FULFILL_ORCHESTRATION_REQUIRED, 'Y' , 1 , 2) FULFILL_ORCHESTRATION_REQUIRED,

'TOE_'||TOL.HEADER_ID SALES_ORDER_HEADER_ID,

'TOE_'||TOL.line_id SALES_ORDER_LINE_ID ,

FULFILLMENT.header_id orch_order_header_id,

FULFILLMENT.LINE_ID ORCH_ORDER_LINE_ID,

FULFILLMENT.schedule_ship_date schedule_ship_date,

FULFILLMENT.schedule_arrival_date schedule_arrival_date,

DECODE(FULFILLMENT.REQUEST_ARRIVAL_DATE,null,FULFILLMENT.REQUEST_SHIP_DATE,FULFILLMENT.REQUEST_ARRIVAL_DATE) REQUEST_DATE ,

DECODE(FULFILLMENT.rowid, null, 2, DECODE(FULFILLMENT.REQUEST_ARRIVAL_DATE,null,1,2)) ORDER_DATE_TYPE_CODE ,

1 DEMAND_SOURCE_TYPE,

ITEMSV.UOM_CODE ORDERED_UOM,

DECODE(FULFILLMENT.ITEM_TYPE_CODE,'1','MODEL','2','OPTION_CLASS','4','STANDARD') ITEM_TYPE_CODE,

FULFILLMENT.ITEM_SUB_TYPE_CODE ITEM_SUB_TYPE_CODE,

FULFILLMENT.SOURCE_ORDER_NUMBER SOURCE_DOCUMENT_NUMBER,

FULFILLMENT.SOURCE_LINE_NUMBER SOURCE_DOCUMENT_LINE_NUMBER,

DECODE(FULFILLMENT.rowid,null,2,1) INTERFACED_TO_DOO,

TOL.ROWID TOL_ROWID,

TOH.ROWID TOH_ROWID,

NULL SUP_ROWID,

FULFILLMENT.rowid DOO_ROWID,

NULL RSL_ROWID,

NULL ORDER_STATUS

FROM

INV_TRANSFER_ORDER_LINES TOL,

INV_TRANSFER_ORDER_HEADERS TOH,

DOO_FULFILL_LINES_ALL FULFILLMENT,

DOO_DOCUMENT_REFERENCES DOC,

INV_ORG_PARAMETERS SHPORG,

INV_ORG_PARAMETERS RECVORG,

MSC_PLANNED_EGP_SYSTEM_ITEMS ITEMSV,

HZ_PARTIES HZP,

FND_LOOKUP_VALUES_VL FNLMOD,

FND_LOOKUP_VALUES_VL FNLSRL

WHERE TOL.STATUS_LOOKUP = 'OPEN'

AND TOL.HEADER_ID = TOH.HEADER_ID

AND TOL.DESTINATION_TYPE_LOOKUP = 'EXPENSE'

AND TOL.INTERFACE_STATUS_LOOKUP IN ('INT_WSH','INT_DOO')

AND FULFILLMENT.SOURCE_ORDER_ID(+) = TO_CHAR(TOL.HEADER_ID)

AND FULFILLMENT.SOURCE_LINE_ID(+) = TO_CHAR(TOL.LINE_ID)

AND DOC.HEADER_ID(+) = FULFILLMENT.HEADER_ID

AND DOC.LINE_ID(+) = FULFILLMENT.LINE_ID

AND DOC.FULFILL_LINE_ID(+) = FULFILLMENT.FULFILL_LINE_ID

AND DOC_REF_TYPE(+) = 'INTERNAL_ORDER'

AND DOC.DOC_CONTEXT_ID(+) = 'EXPENSE'

AND FULFILLMENT.CANCELED_FLAG(+) <> 'Y'

AND FULFILLMENT.category_code(+) <> 'RETURN'

AND (

FULFILLMENT.rowid IS NULL

OR EXISTS (

SELECT 1

FROM DOO_HEADERS_ALL HEADERS,

DOO_LINES_ALL LINES

WHERE HEADERS.SUBMITTED_FLAG = 'Y'

AND FULFILLMENT.LINE_ID = LINES.LINE_ID

AND LINES.HEADER_ID = HEADERS.HEADER_ID

)

AND FULFILLMENT.shipped_qty(+) IS NULL

AND FULFILLMENT.schedule_ship_date(+) IS NOT NULL

AND TOL.SOURCE_ORGANIZATION_ID <> TOL.DESTINATION_ORGANIZATION_ID