VIEW: APPS.MRP_ATP_SCHEDULE_TEMP_V

Source

View Text - Preformatted

SELECT MAST.ACTION ,MAST.CALLING_MODULE ,MAST.ROWID ROW_ID ,MAST.SESSION_ID ,MAST.ORDER_HEADER_ID ,MAST.ORDER_NUMBER ,MAST.ORDER_LINE_ID ,MAST.INVENTORY_ITEM_ID ,MAST.ORGANIZATION_ID ,MAST.SR_INSTANCE_ID ,MAST.ORGANIZATION_CODE ,MAST.SOURCE_ORGANIZATION_ID ,MAST.CUSTOMER_ID ,MAST.CUSTOMER_SITE_ID ,MAST.DESTINATION_TIME_ZONE ,MAST.QUANTITY_ORDERED ,MAST.UOM_CODE ,MAST.REQUESTED_SHIP_DATE ,MAST.REQUESTED_ARRIVAL_DATE ,MAST.LATEST_ACCEPTABLE_DATE ,MAST.DELIVERY_LEAD_TIME ,MAST.FREIGHT_CARRIER ,MAST.SHIP_METHOD ,MAST.DEMAND_CLASS ,MAST.SHIP_SET_NAME ,MAST.ARRIVAL_SET_NAME ,MAST.OVERRIDE_FLAG ,MAST.SCHEDULED_SHIP_DATE ,MAST.SCHEDULED_ARRIVAL_DATE ,MAST.AVAILABLE_QUANTITY ,MAST.REQUESTED_DATE_QUANTITY ,MAST.GROUP_SHIP_DATE ,MAST.GROUP_ARRIVAL_DATE ,MAST.VENDOR_ID ,MAST.VENDOR_SITE_ID ,MAST.INSERT_FLAG ,MAST.ERROR_CODE ,ML.MEANING ERROR_MESSAGE ,MAST.SEQUENCE_NUMBER ,MAST.FIRM_FLAG ,MAST.INVENTORY_ITEM_NAME ,MAST.SOURCE_ORGANIZATION_CODE ,MAST.INSTANCE_ID1 ,MAST.ORDER_LINE_NUMBER ,MAST.PROMISE_DATE ,MAST.CUSTOMER_NAME ,MAST.CUSTOMER_LOCATION ,MAST.OLD_LINE_SCHEDULE_DATE ,MAST.OLD_SOURCE_ORGANIZATION_CODE ,MAST.SCENARIO_ID ,MAST.VENDOR_NAME ,MAST.VENDOR_SITE_NAME , MAST.STATUS_FLAG , OO.SCHEDULE_STATUS_CODE , MAST.ITEM_DESC , MAST.INTRANSIT_LEAD_TIME , MAST.SHIP_METHOD_TEXT , MAST.END_PEGGING_ID , MAST.SHIPMENT_NUMBER , MAST.OPTION_NUMBER , MAST.PROJECT_ID , MAST.TASK_ID , MAST.SHIP_SET_ID , MAST.ARRIVAL_SET_ID , MAST.PROJECT_NUMBER , MAST.TASK_NUMBER , MAST.EXCEPTION1 , MAST.EXCEPTION2 , MAST.EXCEPTION3 , MAST.EXCEPTION4 , MAST.EXCEPTION5 , MAST.EXCEPTION6 , MAST.EXCEPTION7 , MAST.EXCEPTION8 , MAST.EXCEPTION9 , MAST.EXCEPTION10 , MAST.EXCEPTION11 , MAST.EXCEPTION12 , MAST.EXCEPTION13 , MAST.EXCEPTION14 , MAST.EXCEPTION15 , MAST.FIRM_SOURCE_ORG_ID , MAST.FIRM_SOURCE_ORG_CODE , MAST.FIRM_SHIP_DATE , MAST.FIRM_ARRIVAL_DATE , MAST.REQUEST_ITEM_NAME , MAST.REQ_ITEM_AVAILABLE_DATE , MAST.REQ_ITEM_AVAILABLE_DATE_QTY , MAST.REQ_ITEM_REQ_DATE_QTY ,MAST.COMPILE_DESIGNATOR ,MAST.FLOW_STATUS_CODE ,MAST.MATCHED_ITEM_NAME ,MAST.LINE_NUMBER ,MAST.ATO_MODEL_LINE_ID ,decode(MAST.bom_item_type, 1, decode(MAST.ato_model_line_id, NULL, 0, decode(MAST.order_line_id - MAST.ato_model_line_id, 0, 1, 2) ), 2, 3, 4, decode(MAST.parent_line_id, NULL, 4, 5) ) , decode(MAST.requested_ship_date, NULL, trunc(MAST.scheduled_ship_date - GREATEST( nvl(mast.first_valid_ship_arrival_date, mast.requested_arrival_date ), MAST.requested_arrival_date)), trunc(MAST.scheduled_ship_date - GREATEST( nvl(mast.first_valid_ship_arrival_date, mast.requested_ship_date), MAST.requested_ship_date))) FROM MRP_ATP_SCHEDULE_TEMP MAST, OE_ORDER_LINES_ALL OO, MFG_LOOKUPS ML WHERE MAST.ORDER_LINE_ID = OO.LINE_ID(+) AND MAST.ORDER_HEADER_ID = OO.HEADER_ID(+) AND ML.LOOKUP_TYPE(+) = 'MTL_DEMAND_INTERFACE_ERRORS' AND NVL(MAST.ERROR_CODE,-1) = ML.LOOKUP_CODE(+)

View Text - HTML Formatted

SELECT MAST.ACTION
, MAST.CALLING_MODULE
, MAST.ROWID ROW_ID
, MAST.SESSION_ID
, MAST.ORDER_HEADER_ID
, MAST.ORDER_NUMBER
, MAST.ORDER_LINE_ID
, MAST.INVENTORY_ITEM_ID
, MAST.ORGANIZATION_ID
, MAST.SR_INSTANCE_ID
, MAST.ORGANIZATION_CODE
, MAST.SOURCE_ORGANIZATION_ID
, MAST.CUSTOMER_ID
, MAST.CUSTOMER_SITE_ID
, MAST.DESTINATION_TIME_ZONE
, MAST.QUANTITY_ORDERED
, MAST.UOM_CODE
, MAST.REQUESTED_SHIP_DATE
, MAST.REQUESTED_ARRIVAL_DATE
, MAST.LATEST_ACCEPTABLE_DATE
, MAST.DELIVERY_LEAD_TIME
, MAST.FREIGHT_CARRIER
, MAST.SHIP_METHOD
, MAST.DEMAND_CLASS
, MAST.SHIP_SET_NAME
, MAST.ARRIVAL_SET_NAME
, MAST.OVERRIDE_FLAG
, MAST.SCHEDULED_SHIP_DATE
, MAST.SCHEDULED_ARRIVAL_DATE
, MAST.AVAILABLE_QUANTITY
, MAST.REQUESTED_DATE_QUANTITY
, MAST.GROUP_SHIP_DATE
, MAST.GROUP_ARRIVAL_DATE
, MAST.VENDOR_ID
, MAST.VENDOR_SITE_ID
, MAST.INSERT_FLAG
, MAST.ERROR_CODE
, ML.MEANING ERROR_MESSAGE
, MAST.SEQUENCE_NUMBER
, MAST.FIRM_FLAG
, MAST.INVENTORY_ITEM_NAME
, MAST.SOURCE_ORGANIZATION_CODE
, MAST.INSTANCE_ID1
, MAST.ORDER_LINE_NUMBER
, MAST.PROMISE_DATE
, MAST.CUSTOMER_NAME
, MAST.CUSTOMER_LOCATION
, MAST.OLD_LINE_SCHEDULE_DATE
, MAST.OLD_SOURCE_ORGANIZATION_CODE
, MAST.SCENARIO_ID
, MAST.VENDOR_NAME
, MAST.VENDOR_SITE_NAME
, MAST.STATUS_FLAG
, OO.SCHEDULE_STATUS_CODE
, MAST.ITEM_DESC
, MAST.INTRANSIT_LEAD_TIME
, MAST.SHIP_METHOD_TEXT
, MAST.END_PEGGING_ID
, MAST.SHIPMENT_NUMBER
, MAST.OPTION_NUMBER
, MAST.PROJECT_ID
, MAST.TASK_ID
, MAST.SHIP_SET_ID
, MAST.ARRIVAL_SET_ID
, MAST.PROJECT_NUMBER
, MAST.TASK_NUMBER
, MAST.EXCEPTION1
, MAST.EXCEPTION2
, MAST.EXCEPTION3
, MAST.EXCEPTION4
, MAST.EXCEPTION5
, MAST.EXCEPTION6
, MAST.EXCEPTION7
, MAST.EXCEPTION8
, MAST.EXCEPTION9
, MAST.EXCEPTION10
, MAST.EXCEPTION11
, MAST.EXCEPTION12
, MAST.EXCEPTION13
, MAST.EXCEPTION14
, MAST.EXCEPTION15
, MAST.FIRM_SOURCE_ORG_ID
, MAST.FIRM_SOURCE_ORG_CODE
, MAST.FIRM_SHIP_DATE
, MAST.FIRM_ARRIVAL_DATE
, MAST.REQUEST_ITEM_NAME
, MAST.REQ_ITEM_AVAILABLE_DATE
, MAST.REQ_ITEM_AVAILABLE_DATE_QTY
, MAST.REQ_ITEM_REQ_DATE_QTY
, MAST.COMPILE_DESIGNATOR
, MAST.FLOW_STATUS_CODE
, MAST.MATCHED_ITEM_NAME
, MAST.LINE_NUMBER
, MAST.ATO_MODEL_LINE_ID
, DECODE(MAST.BOM_ITEM_TYPE
, 1
, DECODE(MAST.ATO_MODEL_LINE_ID
, NULL
, 0
, DECODE(MAST.ORDER_LINE_ID - MAST.ATO_MODEL_LINE_ID
, 0
, 1
, 2) )
, 2
, 3
, 4
, DECODE(MAST.PARENT_LINE_ID
, NULL
, 4
, 5) )
, DECODE(MAST.REQUESTED_SHIP_DATE
, NULL
, TRUNC(MAST.SCHEDULED_SHIP_DATE - GREATEST( NVL(MAST.FIRST_VALID_SHIP_ARRIVAL_DATE
, MAST.REQUESTED_ARRIVAL_DATE )
, MAST.REQUESTED_ARRIVAL_DATE))
, TRUNC(MAST.SCHEDULED_SHIP_DATE - GREATEST( NVL(MAST.FIRST_VALID_SHIP_ARRIVAL_DATE
, MAST.REQUESTED_SHIP_DATE)
, MAST.REQUESTED_SHIP_DATE)))
FROM MRP_ATP_SCHEDULE_TEMP MAST
, OE_ORDER_LINES_ALL OO
, MFG_LOOKUPS ML
WHERE MAST.ORDER_LINE_ID = OO.LINE_ID(+)
AND MAST.ORDER_HEADER_ID = OO.HEADER_ID(+)
AND ML.LOOKUP_TYPE(+) = 'MTL_DEMAND_INTERFACE_ERRORS'
AND NVL(MAST.ERROR_CODE
, -1) = ML.LOOKUP_CODE(+)