MSC_AP_TRANSFER_ORDERS_IMT

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

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

Name

FULFILL_LINE_ID

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

NEW_ORDER_QUANTITY

COMPLETED_QUANTITY

DELIVERY_PRICE

REVISION

FIRM_PLANNED_TYPE

PRIMARY_UOM_CODE

FULFILL_ORCHESTRATION_REQUIRED

ORDER_STATUS

TOH_ROWID

TOL_ROWID

SUP_ROWID

DOO_ROWID

RSL_ROWID

Query

SQL_Statement
SELECT fulfillment.fulfill_line_id, tol.line_id to_line_id, toh.header_number order_number, tol.line_number\|\|'.'\|\|fulfillment.fulfill_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, 2 expense_transfer, fulfillment.fulfill_org_id source_organization_id, shporg.organization_code from_organization_code, ( CASE WHEN tol.source_organization_id = fulfillment.fulfill_org_id THEN tol.source_subinventory_code ELSE fulfillment.subinventory END ) source_subinventory_code, tol.destination_organization_id organization_id, org.organization_code organization_code, tol.destination_subinventory_code subinventory_code, fulfillment.inventory_item_id, item.item_number item_name, fulfillment.carrier_id, hzp.partner_name carrier_name, fulfillment.ship_mode_of_transport mode_of_transport, fnlmod.meaning mode_of_transport_name, fulfillment.ship_class_of_service service_level, fnlsrl.meaning service_level_name, NVL(fulfillment.schedule_arrival_date, fulfillment.schedule_ship_date) need_by_date, fulfillment.schedule_ship_date new_ship_date, NVL(fulfillment.schedule_arrival_date, fulfillment.schedule_ship_date) new_dock_date, NVL(fulfillment.schedule_arrival_date, fulfillment.schedule_ship_date) new_schedule_date, toh.ordered_date new_order_placement_date, ( CASE WHEN fulfillment.ordered_uom = item.uom_code THEN fulfillment.ordered_qty ELSE INV_CONVERT.INV_UM_CONVERT ( item_id => fulfillment.inventory_item_id, precision => 10, from_quantity => fulfillment.ordered_qty, from_unit => fulfillment.ordered_uom, to_unit => item.uom_code, from_name => NULL, to_name => NULL ) END ) new_order_quantity, fulfillment.shipped_qty completed_quantity, tol.unit_price delivery_price, tol.item_revision revision, DECODE(tol.firm_flag, '1', 1, 'Y', 1, 2) firm_planned_type, item.uom_code primary_uom_code, 1 fulfill_orchestration_required, 3 order_status, toh.rowid toh_rowid, tol.rowid tol_rowid, null sup_rowid, fulfillment.rowid doo_rowid, null rsl_rowid FROM doo_headers_all headers, doo_fulfill_lines_all fulfillment, inv_org_parameters shporg, inv_org_parameters org, msc_planned_egp_system_items item, inv_transfer_order_headers toh, inv_transfer_order_lines tol, msc_global_trading_partners hzp, msc_sr_lookup_values_vl fnlmod, msc_sr_lookup_values_vl fnlsrl WHERE headers.submitted_flag = 'Y' AND headers.source_document_type_code = 'TO' AND headers.header_id = fulfillment.header_id AND fulfillment.canceled_flag <> 'Y' AND fulfillment.category_code <> 'RETURN' AND fulfillment.schedule_ship_date IS NOT NULL AND fulfillment.shipped_qty IS NULL AND tol.line_id = TO_NUMBER(fulfillment.source_line_id) AND toh.header_id = tol.header_id AND toh.fulfill_orchestration_required = 'Y' AND shporg.organization_id = fulfillment.fulfill_org_id AND org.organization_id = tol.destination_organization_id AND item.inventory_item_id = fulfillment.inventory_item_id AND item.organization_id = fulfillment.fulfill_org_id AND hzp.tp_id(+) = fulfillment.carrier_id AND fnlmod.lookup_type(+) = 'WSH_MODE_OF_TRANSPORT' AND fnlmod.lookup_code(+) = DECODE(UPPER(fulfillment.ship_mode_of_transport), LOWER(fulfillment.ship_mode_of_transport), TO_NUMBER(fulfillment.ship_mode_of_transport),NULL) AND fnlsrl.lookup_type(+) = 'WSH_SERVICE_LEVELS' AND fnlsrl.lookup_code(+) = DECODE(UPPER(fulfillment.ship_class_of_service), LOWER(fulfillment.ship_class_of_service), TO_NUMBER(fulfillment.ship_class_of_service),NULL)

SQL_Statement

SELECT

fulfillment.fulfill_line_id,

tol.line_id to_line_id,

toh.header_number order_number,

tol.line_number||'.'||fulfillment.fulfill_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,

2 expense_transfer,

fulfillment.fulfill_org_id source_organization_id,

shporg.organization_code from_organization_code,

(

CASE

WHEN tol.source_organization_id = fulfillment.fulfill_org_id

THEN tol.source_subinventory_code

ELSE

fulfillment.subinventory

END

) source_subinventory_code,

tol.destination_organization_id organization_id,

org.organization_code organization_code,

tol.destination_subinventory_code subinventory_code,

fulfillment.inventory_item_id,

item.item_number item_name,

fulfillment.carrier_id,

hzp.partner_name carrier_name,

fulfillment.ship_mode_of_transport mode_of_transport,

fnlmod.meaning mode_of_transport_name,

fulfillment.ship_class_of_service service_level,

fnlsrl.meaning service_level_name,

NVL(fulfillment.schedule_arrival_date, fulfillment.schedule_ship_date) need_by_date,

fulfillment.schedule_ship_date new_ship_date,

NVL(fulfillment.schedule_arrival_date, fulfillment.schedule_ship_date) new_dock_date,

NVL(fulfillment.schedule_arrival_date, fulfillment.schedule_ship_date) new_schedule_date,

toh.ordered_date new_order_placement_date,

(

CASE

WHEN fulfillment.ordered_uom = item.uom_code

THEN fulfillment.ordered_qty

ELSE

INV_CONVERT.INV_UM_CONVERT

(

item_id => fulfillment.inventory_item_id,

precision => 10,

from_quantity => fulfillment.ordered_qty,

from_unit => fulfillment.ordered_uom,

to_unit => item.uom_code,

from_name => NULL,

to_name => NULL

)

END

) new_order_quantity,

fulfillment.shipped_qty completed_quantity,

tol.unit_price delivery_price,

tol.item_revision revision,

DECODE(tol.firm_flag, '1', 1, 'Y', 1, 2) firm_planned_type,

item.uom_code primary_uom_code,

1 fulfill_orchestration_required,

3 order_status,

toh.rowid toh_rowid,

tol.rowid tol_rowid,

null sup_rowid,

fulfillment.rowid doo_rowid,

null rsl_rowid

FROM

doo_headers_all headers,

doo_fulfill_lines_all fulfillment,

inv_org_parameters shporg,

inv_org_parameters org,

msc_planned_egp_system_items item,

inv_transfer_order_headers toh,

inv_transfer_order_lines tol,

msc_global_trading_partners hzp,

msc_sr_lookup_values_vl fnlmod,

msc_sr_lookup_values_vl fnlsrl

WHERE headers.submitted_flag = 'Y'

AND headers.source_document_type_code = 'TO'

AND headers.header_id = fulfillment.header_id

AND fulfillment.canceled_flag <> 'Y'

AND fulfillment.category_code <> 'RETURN'

AND fulfillment.schedule_ship_date IS NOT NULL

AND fulfillment.shipped_qty IS NULL

AND tol.line_id = TO_NUMBER(fulfillment.source_line_id)

AND toh.header_id = tol.header_id

AND toh.fulfill_orchestration_required = 'Y'

AND shporg.organization_id = fulfillment.fulfill_org_id

AND org.organization_id = tol.destination_organization_id

AND item.inventory_item_id = fulfillment.inventory_item_id

AND item.organization_id = fulfillment.fulfill_org_id

AND hzp.tp_id(+) = fulfillment.carrier_id

AND fnlmod.lookup_type(+) = 'WSH_MODE_OF_TRANSPORT'

AND fnlmod.lookup_code(+) = DECODE(UPPER(fulfillment.ship_mode_of_transport), LOWER(fulfillment.ship_mode_of_transport), TO_NUMBER(fulfillment.ship_mode_of_transport),NULL)

AND fnlsrl.lookup_type(+) = 'WSH_SERVICE_LEVELS'

AND fnlsrl.lookup_code(+) = DECODE(UPPER(fulfillment.ship_class_of_service), LOWER(fulfillment.ship_class_of_service), TO_NUMBER(fulfillment.ship_class_of_service),NULL)