MSC_AP_INTRANSIT_SUPPLIES

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
HEADER_ID PO_NUMBER INVENTORY_ITEM_ID ORDER_TYPE PURCH_LINE_NUM FIRM_PLANNED_STATUS_TYPE LINE_ID NEW_ORDER_QUANTITY NEW_SCHEDULE_DATE ORGANIZATION_ID CUSTOMER_ID ARRIVED_FLAG POSTPROCESSING_LEAD_TIME

Name

HEADER_ID

PO_NUMBER

INVENTORY_ITEM_ID

ORDER_TYPE

PURCH_LINE_NUM

FIRM_PLANNED_STATUS_TYPE

LINE_ID

NEW_ORDER_QUANTITY

NEW_SCHEDULE_DATE

ORGANIZATION_ID

CUSTOMER_ID

ARRIVED_FLAG

POSTPROCESSING_LEAD_TIME

Query

SQL_Statement
SELECT /+ ORDERED / oh.Header_ID, oh.Order_Number Po_Number, msi.Inventory_Item_ID, 11 Order_Type, ol.line_id Purch_Line_Num, 2 Firm_Planned_Status_Type, ol.line_id Line_ID, wdd.Shipped_Quantity New_Order_Quantity, dfl.schedule_ship_date New_Schedule_Date, msi.Organization_ID, dfl.SHIP_TO_CUSTOMER_ID customer_id, decode(wnd.ultimate_dropoff_date,NULL,2,1) arrived_flag, msi.POSTPROCESSING_LEAD_TIME FROM HR_Organization_Information hoi, DOO_HEADERS_ALL OH, DOO_LINES_ALL OL, DOO_FULFILL_LINES_ALL DFL, WSH_DELIVERY_DETAILS WDD, WSH_DELIVERY_ASSIGNMENTS WDA, WSH_NEW_DELIVERIES WND, MSC_PLANNED_EGP_SYSTEM_ITEMS_V MSI WHERE hoi.Org_Information_Context= 'Customer/Supplier Association' AND nvl(to_char(oh.sold_to_customer_id), -1) = nvl(hoi.Org_Information1,-1) AND nvl(to_char(wdd.sold_to_party_id), -1) = nvl(hoi.Org_Information1,-1) AND ol.header_id = oh.header_id AND ol.line_id = dfl.line_id AND nvl(to_char(dfl.SHIP_TO_CUSTOMER_ID), -1) = nvl(hoi.Org_Information2,-1) AND wdd.source_shipment_id=dfl.fulfill_line_id AND wdd.source_document_type_id=2 AND wda.delivery_detail_id = wdd.delivery_detail_id AND wda.delivery_id is not null AND wnd.delivery_id = wda.delivery_id AND msi.Inventory_Item_ID= wdd.Inventory_Item_ID AND msi.Organization_ID= hoi.Organization_ID

SQL_Statement

SELECT /*+ ORDERED */

oh.Header_ID,

oh.Order_Number Po_Number,

msi.Inventory_Item_ID,

11 Order_Type,

ol.line_id Purch_Line_Num,

2 Firm_Planned_Status_Type,

ol.line_id Line_ID,

wdd.Shipped_Quantity New_Order_Quantity,

dfl.schedule_ship_date New_Schedule_Date,

msi.Organization_ID,

dfl.SHIP_TO_CUSTOMER_ID customer_id,

decode(wnd.ultimate_dropoff_date,NULL,2,1) arrived_flag,

msi.POSTPROCESSING_LEAD_TIME

FROM HR_Organization_Information hoi,

DOO_HEADERS_ALL OH,

DOO_LINES_ALL OL,

DOO_FULFILL_LINES_ALL DFL,

WSH_DELIVERY_DETAILS WDD,

WSH_DELIVERY_ASSIGNMENTS WDA,

WSH_NEW_DELIVERIES WND,

MSC_PLANNED_EGP_SYSTEM_ITEMS_V MSI

WHERE

hoi.Org_Information_Context= 'Customer/Supplier Association'

AND nvl(to_char(oh.sold_to_customer_id), -1) = nvl(hoi.Org_Information1,-1)

AND nvl(to_char(wdd.sold_to_party_id), -1) = nvl(hoi.Org_Information1,-1)

AND ol.header_id = oh.header_id

AND ol.line_id = dfl.line_id

AND nvl(to_char(dfl.SHIP_TO_CUSTOMER_ID), -1) = nvl(hoi.Org_Information2,-1)

AND wdd.source_shipment_id=dfl.fulfill_line_id

AND wdd.source_document_type_id=2

AND wda.delivery_detail_id = wdd.delivery_detail_id

AND wda.delivery_id is not null

AND wnd.delivery_id = wda.delivery_id

AND msi.Inventory_Item_ID= wdd.Inventory_Item_ID

AND msi.Organization_ID= hoi.Organization_ID