MSC_AP_TRANSACTED_TO

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, null shipment_line_id, RSH.RECEIPT_NUM, TOL.HEADER_ID DISPOSITION_ID, TOL.INTERFACE_STATUS_LOOKUP, 94 ORDER_TYPE, 9400 FG_ORDER_TYPE, 2 EXPENSE_TRANSFER, TOL.SOURCE_ORGANIZATION_ID, shporg.organization_code FROM_ORGANIZATION_CODE, TOL.SOURCE_SUBINVENTORY_CODE, RSL.TO_ORGANIZATION_ID ORGANIZATION_ID, recvorg.organization_code, TOL.DESTINATION_SUBINVENTORY_CODE SUBINVENTORY_CODE, RSL.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, TOL.NEED_BY_DATE, RSH.SHIPPED_DATE NEW_SHIP_DATE, nvl(RSH.EXPECTED_RECEIPT_DATE, TOL.NEED_BY_DATE) NEW_DOCK_DATE, nvl(RSH.EXPECTED_RECEIPT_DATE, TOL.NEED_BY_DATE) NEW_SCHEDULE_DATE, TOH.ORDERED_DATE NEW_ORDER_PLACEMENT_DATE, 1 conversion_rate, 0 new_order_quantity, RSL.QUANTITY_DELIVERED completed_quantity, TOL.UNIT_PRICE DELIVERY_PRICE, TOL.ITEM_REVISION REVISION, DECODE(TOL.FIRM_FLAG, 'Y' , 1 , 2) FIRM_PLANNED_TYPE, null 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, null SUP_ROWID, null DOO_ROWID, RSL.ROWID RSL_ROWID, null order_status FROM RCV_SHIPMENT_HEADERS RSH, INV_TRANSFER_ORDER_LINES TOL, INV_TRANSFER_ORDER_HEADERS TOH, 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, MSC_PARAMETERS ORG WHERE RSL.TRANSFER_ORDER_HEADER_ID = TOL.HEADER_ID AND RSL.TRANSFER_ORDER_LINE_ID = TOL.LINE_ID AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND shporg.organization_id = TOL.SOURCE_ORGANIZATION_ID AND ITEMSV.INVENTORY_ITEM_ID = RSL.ITEM_ID AND ITEMSV.organization_id = RSL.TO_ORGANIZATION_ID AND RECVORG.ORGANIZATION_ID = RSL.TO_ORGANIZATION_ID AND TOL.HEADER_ID = TOH.HEADER_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 AND RSL.QUANTITY_DELIVERED > 0 AND RSH.SHIPPED_DATE >= TRUNC(SYSDATE) - NVL(ORG.PAST_DUE_SUPPLY_DAYS,40) AND ORG.ORGANIZATION_CODE(+) = SHPORG.ORGANIZATION_CODE

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,

null shipment_line_id,

RSH.RECEIPT_NUM,

TOL.HEADER_ID DISPOSITION_ID,

TOL.INTERFACE_STATUS_LOOKUP,

94 ORDER_TYPE,

9400 FG_ORDER_TYPE,

2 EXPENSE_TRANSFER,

TOL.SOURCE_ORGANIZATION_ID,

shporg.organization_code FROM_ORGANIZATION_CODE,

TOL.SOURCE_SUBINVENTORY_CODE,

RSL.TO_ORGANIZATION_ID ORGANIZATION_ID,

recvorg.organization_code,

TOL.DESTINATION_SUBINVENTORY_CODE SUBINVENTORY_CODE,

RSL.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,

TOL.NEED_BY_DATE,

RSH.SHIPPED_DATE NEW_SHIP_DATE,

nvl(RSH.EXPECTED_RECEIPT_DATE, TOL.NEED_BY_DATE) NEW_DOCK_DATE,

nvl(RSH.EXPECTED_RECEIPT_DATE, TOL.NEED_BY_DATE) NEW_SCHEDULE_DATE,

TOH.ORDERED_DATE NEW_ORDER_PLACEMENT_DATE,

1 conversion_rate,

0 new_order_quantity,

RSL.QUANTITY_DELIVERED completed_quantity,

TOL.UNIT_PRICE DELIVERY_PRICE, TOL.ITEM_REVISION REVISION,

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

null 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,

null SUP_ROWID,

null DOO_ROWID,

RSL.ROWID RSL_ROWID,

null order_status

FROM

RCV_SHIPMENT_HEADERS RSH,

INV_TRANSFER_ORDER_LINES TOL,

INV_TRANSFER_ORDER_HEADERS TOH,

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,

MSC_PARAMETERS ORG

WHERE

RSL.TRANSFER_ORDER_HEADER_ID = TOL.HEADER_ID

AND RSL.TRANSFER_ORDER_LINE_ID = TOL.LINE_ID

AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

AND shporg.organization_id = TOL.SOURCE_ORGANIZATION_ID

AND ITEMSV.INVENTORY_ITEM_ID = RSL.ITEM_ID

AND ITEMSV.organization_id = RSL.TO_ORGANIZATION_ID

AND RECVORG.ORGANIZATION_ID = RSL.TO_ORGANIZATION_ID

AND TOL.HEADER_ID = TOH.HEADER_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

AND RSL.QUANTITY_DELIVERED > 0

AND RSH.SHIPPED_DATE >= TRUNC(SYSDATE) - NVL(ORG.PAST_DUE_SUPPLY_DAYS,40)

AND ORG.ORGANIZATION_CODE(+) = SHPORG.ORGANIZATION_CODE