View: WSH_DELIVERY_LINES_SC_V

View Text

SELECT PLD.ROWID ROW_ID
, PLD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PLD.LAST_UPDATED_BY LAST_UPDATED_BY
, PLD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PLD.PICKING_LINE_DETAIL_ID PICKING_LINE_DETAIL_ID
, PLD.PICKING_LINE_ID PICKING_LINE_ID
, PLD.WAREHOUSE_ID ORGANIZATION_ID
, ORG.ORGANIZATION_CODE ORGANIZATION_CODE
, H_ORG.NAME ORGANIZATION
, PLD.REQUESTED_QUANTITY REQUESTED_QUANTITY
, PLD.SHIPPED_QUANTITY SHIPPED_QUANTITY
, PLD.SERIAL_NUMBER SERIAL_NUMBER
, PLD.LOT_NUMBER LOT_NUMBER
, PLD.CUSTOMER_REQUESTED_LOT_FLAG CUSTOMER_REQUESTED_LOT_FLAG
, PLD.REVISION REVISION
, PLD.SUBINVENTORY SUBINVENTORY
, PLD.INVENTORY_LOCATION_ID INVENTORY_LOCATION_ID
, PLD.INVENTORY_LOCATION_SEGMENTS INVENTORY_LOCATION_SEGMENTS
, PLD.DETAIL_TYPE_CODE DETAIL_TYPE_CODE
, PLD.CONTEXT CONTEXT
, PLD.ATTRIBUTE1 ATTRIBUTE1
, PLD.ATTRIBUTE2 ATTRIBUTE2
, PLD.ATTRIBUTE3 ATTRIBUTE3
, PLD.ATTRIBUTE4 ATTRIBUTE4
, PLD.ATTRIBUTE5 ATTRIBUTE5
, PLD.ATTRIBUTE6 ATTRIBUTE6
, PLD.ATTRIBUTE7 ATTRIBUTE7
, PLD.ATTRIBUTE8 ATTRIBUTE8
, PLD.ATTRIBUTE9 ATTRIBUTE9
, PLD.ATTRIBUTE10 ATTRIBUTE10
, PLD.ATTRIBUTE11 ATTRIBUTE11
, PLD.ATTRIBUTE12 ATTRIBUTE12
, PLD.ATTRIBUTE13 ATTRIBUTE13
, PLD.ATTRIBUTE14 ATTRIBUTE14
, PLD.ATTRIBUTE15 ATTRIBUTE15
, PLD.RELEASED_FLAG RELEASED_FLAG
, PLD.SCHEDULE_DATE SCHEDULE_DATE
, PLD.DEMAND_ID DEMAND_ID
, PLD.AUTOSCHEDULED_FLAG AUTOSCHEDULED_FLAG
, PLD.DELIVERY DELIVERY
, PLD.RESERVABLE_FLAG RESERVABLE_FLAG
, PLD.TRANSACTABLE_FLAG TRANSACTABLE_FLAG
, PLD.LATEST_ACCEPTABLE_DATE LATEST_ACCEPTABLE_DATE
, PLD.DEPARTURE_ID DEPARTURE_ID
, PLD.DELIVERY_ID DELIVERY_ID
, PLD.CONTAINER_ID CONTAINER_ID
, PC.CONTAINER_INVENTORY_ITEM_ID CONTAINER_INVENTORY_ITEM_ID
, MSI2.DESCRIPTION CONTAINER_DESCRIPTION
, PLD.MASTER_CONTAINER_ITEM_ID MASTER_CONTAINER_ITEM_ID
, PLD.DETAIL_CONTAINER_ITEM_ID DETAIL_CONTAINER_ITEM_ID
, PLD.LOAD_SEQ_NUMBER LOAD_SEQ_NUMBER
, PLD.DPW_ASSIGNED_FLAG PLANNED_FLAG
, PLD.TRANSACTION_TEMP_ID TRANSACTION_TEMP_ID
, PLD.SCHEDULE_LEVEL SCHEDULE_LEVEL
, PLD.SCHEDULE_STATUS_CODE SCHEDULE_STATUS_CODE
, PLD.WIP_RESERVED_QUANTITY WIP_RESERVED_QUANTITY
, PLD.WIP_COMPLETED_QUANTITY WIP_COMPLETED_QUANTITY
, PLD.SUPPLY_SOURCE_TYPE SUPPLY_SOURCE_TYPE
, PLD.SUPPLY_SOURCE_HEADER_ID SUPPLY_SOURCE_HEADER_ID
, PLD.UPDATE_FLAG UPDATE_FLAG
, PLD.DEMAND_CLASS_CODE DEMAND_CLASS_CODE
, PL.DEP_PLAN_REQUIRED_FLAG DEP_PLAN_REQUIRED_FLAG
, PL.CUSTOMER_ITEM_ID CUSTOMER_ITEM_ID
, PLD.DPW_ASSIGNED_FLAG DPW_ASSIGNED_FLAG
, PL.ROWID PL_ROW_ID
, DECODE(PL.BO_PICKING_LINE_ID
, ''
, SL.SHIP_SET_NUMBER
, NULL) SHIP_SET_NUMBER
, PLD.PICK_SLIP_NUMBER PICK_SLIP_NUMBER
, PL.ORIGINAL_REQUESTED_QUANTITY ORIGINAL_REQUESTED_QUANTITY
, PL.REQUESTED_QUANTITY PL_REQUESTED_QUANTITY
, PL.SHIPPED_QUANTITY PL_SHIPPED_QUANTITY
, PL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI1.DESCRIPTION ITEM_DESCRIPTION
, DECODE(MSI1.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N') SUBINV_RESTRICTED_FLAG
, DECODE(MSI1.REVISION_QTY_CONTROL_CODE
, 2
, 'Y'
, 'N') REVISION_CONTROL_FLAG
, DECODE(MSI1.LOT_CONTROL_CODE
, 2
, 'Y'
, 'N') LOT_CONTROL_FLAG
, DECODE(MSI1.SERIAL_NUMBER_CONTROL_CODE
, 2
, 'Y'
, 5
, 'Y'
, 6
, DECODE(SH.ORDER_CATEGORY
, 'P'
, 'N'
, 'Y')
, 'N') SERIAL_NUMBER_CONTROL_FLAG
, MSI1.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI1.RESTRICT_LOCATORS_CODE RESTRICT_LOCATORS_CODE
, ORG.STOCK_LOCATOR_CONTROL_CODE ORG_LOCATION_CONTROL_CODE
, ORG.NEGATIVE_INV_RECEIPT_CODE NEGATIVE_INV_RECEIPT_CODE
, MSI1.LOCATION_CONTROL_CODE ITEM_LOCATION_CONTROL_CODE
, PL.INCLUDED_ITEM_FLAG INCLUDED_ITEM_FLAG
, DECODE(MSI1.RESERVABLE_TYPE
, 1
, 'Y'
, 'N') ITEM_RESERVABLE_FLAG
, MSI1.PRIMARY_UOM_CODE PRIMARY_UOM_CODE
, PL.CONFIGURATION_ITEM_FLAG CONFIGURATION_ITEM_FLAG
, PL.PICKING_HEADER_ID PICKING_HEADER_ID
, PL.ORDER_LINE_ID ORDER_LINE_ID
, PL.LINE_DETAIL_ID LINE_DETAIL_ID
, NVL(PL.SHIP_TO_SITE_USE_ID
, SH.SHIP_TO_SITE_USE_ID) ULTIMATE_SHIP_TO_ID
, SU1.LOCATION ULTIMATE_SHIP_TO
, PL.SHIP_TO_CONTACT_ID SHIP_TO_CONTACT_ID
, PL.SHIPMENT_PRIORITY_CODE SHIPMENT_PRIORITY_CODE
, NVL(PL.SHIP_METHOD_CODE
, SH.SHIP_METHOD_CODE) FREIGHT_CARRIER_CODE
, PL.UNIT_CODE UNIT_CODE
, PL.DATE_REQUESTED DATE_REQUESTED
, PL.DATE_CONFIRMED PL_DATE_CONFIRMED
, PL.SEQUENCE_NUMBER SEQUENCE_NUMBER
, PL.CONTEXT PL_CONTEXT
, PL.ATTRIBUTE1 PL_ATTRIBUTE1
, PL.ATTRIBUTE2 PL_ATTRIBUTE2
, PL.ATTRIBUTE3 PL_ATTRIBUTE3
, PL.ATTRIBUTE4 PL_ATTRIBUTE4
, PL.ATTRIBUTE5 PL_ATTRIBUTE5
, PL.ATTRIBUTE6 PL_ATTRIBUTE6
, PL.ATTRIBUTE7 PL_ATTRIBUTE7
, PL.ATTRIBUTE8 PL_ATTRIBUTE8
, PL.ATTRIBUTE9 PL_ATTRIBUTE9
, PL.ATTRIBUTE10 PL_ATTRIBUTE10
, PL.ATTRIBUTE11 PL_ATTRIBUTE11
, PL.ATTRIBUTE12 PL_ATTRIBUTE12
, PL.ATTRIBUTE13 PL_ATTRIBUTE13
, PL.ATTRIBUTE14 PL_ATTRIBUTE14
, PL.ATTRIBUTE15 PL_ATTRIBUTE15
, DEL.NAME DEL_NAME
, DEL.STATUS_CODE DEL_STATUS_CODE
, S1.MEANING DEL_STATUS
, DEL.SOURCE_CODE DEL_SOURCE_CODE
, PH.STATUS_CODE PH_STATUS
, PH.ORDER_HEADER_ID ORDER_HEADER_ID
, PH.BATCH_ID BATCH_ID
, PB.NAME BATCH_NAME
, PH.DATE_RELEASED DATE_RELEASED
, SH.ORDER_NUMBER ORDER_NUMBER
, SH.CUSTOMER_ID CUSTOMER_ID
, SH.ORDER_CATEGORY ORDER_CATEGORY
, RC.CUSTOMER_NAME CUSTOMER_NAME
, RC.CUSTOMER_NUMBER CUSTOMER_NUMBER
, SH.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE
, S2.MEANING FREIGHT_TERMS
, SH.FOB_CODE FOB_CODE
, AL.MEANING FOB_NAME
, SH.CURRENCY_CODE CURRENCY_CODE
, SL.INTERMEDIATE_SHIP_TO_ID INTERMEDIATE_SHIP_TO_ID
, SU2.LOCATION INTERMEDIATE_SHIP_TO
, SL.ATO_FLAG ATO_FLAG
, SL.LINE_NUMBER LINE_NUMBER
, SL.PARENT_LINE_ID PARENT_LINE_ID
, SL.LINK_TO_LINE_ID LINK_TO_LINE_ID
, SL.CUSTOMER_JOB CUSTOMER_JOB
, SL.PLANNING_PROD_SEQ_NUMBER PLANNING_PROD_SEQ_NUMBER
, SL.CUSTOMER_MODEL_SERIAL_NUMBER CUSTOMER_MODEL_SERIAL_NUMBER
, SL.CUSTOMER_DOCK_CODE CUSTOMER_DOCK_CODE
, SL.CUSTOMER_PRODUCTION_LINE CUSTOMER_PRODUCTION_LINE
, SL.ITEM_TYPE_CODE ITEM_TYPE_CODE
, PC.SEQUENCE_NUMBER CONTAINER_SEQUENCE_NUMBER
, PLD.MOVEMENT_ID MOVEMENT_ID
, WIP.WIP_ENTITY_NAME WIP_NUMBER
FROM SO_PICKING_LINE_DETAILS PLD
, MTL_PARAMETERS ORG
, HR_ORGANIZATION_UNITS H_ORG
, SO_PICKING_LINES_ALL PL
, WSH_DELIVERIES DEL
, SO_HEADERS_ALL SH
, RA_CUSTOMERS RC
, SO_PICKING_BATCHES_ALL PB
, SO_PICKING_HEADERS_ALL PH
, SO_LINES_ALL SL
, RA_SITE_USES_ALL SU1
, RA_SITE_USES_ALL SU2
, AR_LOOKUPS AL
, SO_LOOKUPS S1
, SO_LOOKUPS S2
, MTL_SYSTEM_ITEMS MSI1
, WSH_PACKED_CONTAINERS PC
, MTL_SYSTEM_ITEMS MSI2
, WIP_ENTITIES WIP
WHERE PLD.PICKING_LINE_ID = PL.PICKING_LINE_ID
AND PH.PICKING_HEADER_ID = PL.PICKING_HEADER_ID
AND PH.BATCH_ID = PB.BATCH_ID
AND PH.ORDER_HEADER_ID = SH.HEADER_ID
AND RC.CUSTOMER_ID = SH.CUSTOMER_ID
AND PL.PICKING_HEADER_ID + 0 > 0
AND ORG.ORGANIZATION_ID = PLD.WAREHOUSE_ID
AND H_ORG.ORGANIZATION_ID = PLD.WAREHOUSE_ID
AND PL.ORDER_LINE_ID = SL.LINE_ID
AND SU1.SITE_USE_ID(+) = PL.SHIP_TO_SITE_USE_ID
AND SU2.SITE_USE_ID (+) = SL.INTERMEDIATE_SHIP_TO_ID
AND PL.INVENTORY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
AND PLD.WAREHOUSE_ID = MSI1.ORGANIZATION_ID
AND PLD.CONTAINER_ID = PC.CONTAINER_ID (+)
AND PC.CONTAINER_INVENTORY_ITEM_ID = MSI2.INVENTORY_ITEM_ID (+)
AND PC.ORGANIZATION_ID = MSI2.ORGANIZATION_ID (+)
AND DEL.DELIVERY_ID(+) = PLD.DELIVERY_ID
AND AL.LOOKUP_TYPE (+) = 'FOB'
AND AL.LOOKUP_CODE (+) = SH.FOB_CODE
AND S1.LOOKUP_TYPE(+) = 'SHIPPING_STATUS'
AND S1.LOOKUP_CODE(+) = DEL.STATUS_CODE
AND S2.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
AND S2.LOOKUP_CODE(+) = SH.FREIGHT_TERMS_CODE
AND WIP.WIP_ENTITY_ID (+) = PLD.SUPPLY_SOURCE_HEADER_ID

Columns