MSC_AP_INTRANSIT_RCV

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 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 NEW_ORDER_QUANTITY COMPLETED_QUANTITY DELIVERY_PRICE REVISION SUPPLY_SOURCE_ID EXPENSE_TRANSFER FIRM_PLANNED_TYPE FULFILL_ORCHESTRATION_REQUIRED ORDER_STATUS TOL_ROWID TOH_ROWID SUP_ROWID DOO_ROWID RSL_ROWID

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

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

NEW_ORDER_QUANTITY

COMPLETED_QUANTITY

DELIVERY_PRICE

REVISION

SUPPLY_SOURCE_ID

EXPENSE_TRANSFER

FIRM_PLANNED_TYPE

FULFILL_ORCHESTRATION_REQUIRED

ORDER_STATUS

TOL_ROWID

TOH_ROWID

SUP_ROWID

DOO_ROWID

RSL_ROWID

Query

SQL_Statement
SELECT NULL to_line_id, rsh.shipment_num order_number, rsl.line_num 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, rsh.shipment_header_id disposition_id, 'INT_WSH' interface_status_lookup, DECODE (supply_type_code,'SHIPMENT',11,'RECEIVING',8) order_type, DECODE (supply_type_code,'SHIPMENT',9411,'RECEIVING',9408) fg_order_type, sup.from_organization_id source_organization_id, shporg.organization_code from_organization_code, sup.from_subinventory source_subinventory_code, sup.to_organization_id organization_id, recvorg.organization_code organization_code, sup.to_subinventory subinventory_code, sup.item_id inventory_item_id, itemsv.item_number item_name, NULL carrier_id, NULL carrier_name, NULL mode_of_transport, NULL mode_of_transport_name, NULL service_level, NULL service_level_name, sup.need_by_date, DECODE(supply_type_code,'SHIPMENT',rsh.shipped_date,'RECEIVING',rsh.shipped_date,NULL) new_ship_date, DECODE(supply_type_code,'SHIPMENT',rsh.expected_receipt_date,'RECEIVING',sup.receipt_date,NULL) new_dock_date, sup.expected_delivery_date new_schedule_date, rsl.creation_date new_order_placement_date, sup.to_org_primary_quantity new_order_quantity, 0 completed_quantity, NULL delivery_price, rsl.item_revision revision, sup.supply_source_id, 2 expense_transfer, 2 firm_planned_type, 2 fulfill_orchestration_required, 1 order_status, NULL tol_rowid, NULL toh_rowid, sup.rowid sup_rowid, NULL doo_rowid, rsl.rowid rsl_rowid FROM INV_SUPPLY sup, RCV_SHIPMENT_HEADERS rsh, RCV_SHIPMENT_LINES rsl, INV_ORG_PARAMETERS shporg, INV_ORG_PARAMETERS recvorg, MSC_PLANNED_EGP_SYSTEM_ITEMS itemsv WHERE sup.supply_type_code IN ('SHIPMENT','RECEIVING') AND sup.destination_type_code <> 'EXPENSE' AND sup.Transfer_Order_Line_Id IS NULL AND sup.po_line_location_id IS NULL AND rsh.shipment_header_id = sup.shipment_header_id AND rsl.shipment_line_id = sup.shipment_line_id AND shporg.organization_id = sup.from_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

SQL_Statement

SELECT

NULL to_line_id,

rsh.shipment_num order_number,

rsl.line_num 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,

rsh.shipment_header_id disposition_id,

'INT_WSH' interface_status_lookup,

DECODE (supply_type_code,'SHIPMENT',11,'RECEIVING',8) order_type,

DECODE (supply_type_code,'SHIPMENT',9411,'RECEIVING',9408) fg_order_type,

sup.from_organization_id source_organization_id,

shporg.organization_code from_organization_code,

sup.from_subinventory source_subinventory_code,

sup.to_organization_id organization_id,

recvorg.organization_code organization_code,

sup.to_subinventory subinventory_code,

sup.item_id inventory_item_id,

itemsv.item_number item_name,

NULL carrier_id,

NULL carrier_name,

NULL mode_of_transport,

NULL mode_of_transport_name,

NULL service_level,

NULL service_level_name,

sup.need_by_date,

DECODE(supply_type_code,'SHIPMENT',rsh.shipped_date,'RECEIVING',rsh.shipped_date,NULL) new_ship_date,

DECODE(supply_type_code,'SHIPMENT',rsh.expected_receipt_date,'RECEIVING',sup.receipt_date,NULL) new_dock_date,

sup.expected_delivery_date new_schedule_date,

rsl.creation_date new_order_placement_date,

sup.to_org_primary_quantity new_order_quantity,

0 completed_quantity,

NULL delivery_price,

rsl.item_revision revision,

sup.supply_source_id,

2 expense_transfer,

2 firm_planned_type,

2 fulfill_orchestration_required,

1 order_status,

NULL tol_rowid,

NULL toh_rowid,

sup.rowid sup_rowid,

NULL doo_rowid,

rsl.rowid rsl_rowid

FROM

INV_SUPPLY sup,

RCV_SHIPMENT_HEADERS rsh,

RCV_SHIPMENT_LINES rsl,

INV_ORG_PARAMETERS shporg,

INV_ORG_PARAMETERS recvorg,

MSC_PLANNED_EGP_SYSTEM_ITEMS itemsv

WHERE sup.supply_type_code IN ('SHIPMENT','RECEIVING')

AND sup.destination_type_code <> 'EXPENSE'

AND sup.Transfer_Order_Line_Id IS NULL

AND sup.po_line_location_id IS NULL

AND rsh.shipment_header_id = sup.shipment_header_id

AND rsl.shipment_line_id = sup.shipment_line_id

AND shporg.organization_id = sup.from_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