View: WSH_PICKING_BATCHES_V

Product:	WSH - Shipping Execution
Description:	/* Bug: 1924581 , HR_LOCATION changes made on 8/15/01
Implementation/DBA Data:	APPS.WSH_PICKING_BATCHES_V

View Text

SELECT WPB.ROWID ROW_ID
, WPB.BATCH_ID
, WPB.CREATION_DATE
, WPB.CREATED_BY
, WPB.LAST_UPDATE_DATE
, WPB.LAST_UPDATED_BY
, WPB.LAST_UPDATE_LOGIN
, WPB.PROGRAM_APPLICATION_ID
, WPB.PROGRAM_ID
, WPB.PROGRAM_UPDATE_DATE
, WPB.REQUEST_ID
, WPB.NAME BATCH_NAME
, WPB.BACKORDERS_ONLY_FLAG
, S1.MEANING BACKORDERS
, WPB.DOCUMENT_SET_ID
, WRS.NAME DOC_NAME
, WPB.EXISTING_RSVS_ONLY_FLAG
, S3.MEANING RESERVATION
, DECODE(WPB.EXISTING_RSVS_ONLY_FLAG
, 'Y'
, '*'
, NULL) RESERVATION_STAR
, WPB.SHIPMENT_PRIORITY_CODE
, S2.MEANING SHIPMENT_PRIORITY
, WPB.SHIP_METHOD_CODE
, WPB.CUSTOMER_ID
, SUBSTRB ( CUST.PARTY_NAME
, 1
, 50 ) /* CUSTOMER_NAME */
, CUST.ACCOUNT_NUMBER
, WPB.ORDER_HEADER_ID
, H.ORDER_NUMBER
, WPB.SHIP_SET_NUMBER SHIP_SET_ID
, OS.SET_NAME SHIP_SET_NUMBER
, WPB.INVENTORY_ITEM_ID
, WPB.ORDER_TYPE_ID
, WPB.TASK_ID
, PJM_PROJECT.ALL_TASK_IDTONUM(WPB.TASK_ID) TASK_NUMBER
, WPB.PROJECT_ID
, PJM_PROJECT.ALL_PROJ_IDTONUM(WPB.PROJECT_ID) PROJECT_NUMBER
, OT.NAME ORDER_TYPE
, WPB.FROM_REQUESTED_DATE
, WPB.TO_REQUESTED_DATE
, WPB.FROM_SCHEDULED_SHIP_DATE
, WPB.TO_SCHEDULED_SHIP_DATE
, WPB.SHIP_TO_LOCATION_ID
, WSTL.UI_LOCATION_CODE SHIP_TO_LOCATION
, WSFL.UI_LOCATION_CODE SHIP_FROM_LOCATION
, WPB.SHIP_FROM_LOCATION_ID
, ORG.ORGANIZATION_CODE WAREHOUSE_CODE
, H_ORG_TL.NAME WAREHOUSE
, WPB.SUBINVENTORY
, WPB.ATTRIBUTE_CATEGORY
, WPB.ATTRIBUTE1
, WPB.ATTRIBUTE2
, WPB.ATTRIBUTE3
, WPB.ATTRIBUTE4
, WPB.ATTRIBUTE5
, WPB.ATTRIBUTE6
, WPB.ATTRIBUTE7
, WPB.ATTRIBUTE8
, WPB.ATTRIBUTE9
, WPB.ATTRIBUTE10
, WPB.ATTRIBUTE11
, WPB.ATTRIBUTE12
, WPB.ATTRIBUTE13
, WPB.ATTRIBUTE14
, WPB.ATTRIBUTE15
, WPB.ORGANIZATION_ID
, WPB.TRIP_STOP_ID STOP_ID
, WSSL.UI_LOCATION_CODE STOP_NAME
, WPB.TRIP_ID
, WPB.CARRIER_ID
, WT.NAME TRIP_NAME
, WPB.DEFAULT_STAGE_SUBINVENTORY
, WPB.DEFAULT_STAGE_LOCATOR_ID
, WPB.PICK_FROM_SUBINVENTORY
, WPB.PICK_FROM_LOCATOR_ID
, WPB.DELIVERY_ID
, WND.NAME DELIVERY_NAME
, WPB.PICK_GROUPING_RULE_ID
, PGR.NAME PICK_GROUPING_RULE_NAME
, WPB.PICK_SEQUENCE_RULE_ID
, PSQR.NAME PICK_SEQ_RULE_NAME
, WPB.AUTODETAIL_PR_FLAG
, WPB.AUTO_PICK_CONFIRM_FLAG
, WPB.INCLUDE_PLANNED_LINES
, WPB.AUTOCREATE_DELIVERY_FLAG
, WPB.DELIVERY_DETAIL_ID
, WPB.AUTO_SEND_DOC_FLAG
, WPB.TASK_PLANNING_FLAG
, WPB.SHIP_CONFIRM_RULE_ID
, WPB.AUTOPACK_FLAG
, WPB.AUTOPACK_LEVEL
, WPB.APPEND_FLAG
, WPB.REGION_ID
, WPB.ZONE_ID
, WPB.CATEGORY_ID
, WPB.CATEGORY_SET_ID
, WPB.AC_DELIVERY_CRITERIA
, WPB.REL_SUBINVENTORY
, WPB.TASK_PRIORITY
, WPB.ALLOCATION_METHOD
, WPB.CROSSDOCK_CRITERIA_ID
, WPB.ACTUAL_DEPARTURE_DATE
, WPB.DYNAMIC_REPLENISHMENT_FLAG
, WPB.CLIENT_ID
FROM MTL_PARAMETERS ORG
, HR_ALL_ORGANIZATION_UNITS_TL H_ORG_TL
, ( SELECT PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, CUST_ACCT.CUST_ACCOUNT_ID
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID ) CUST
, WSH_REPORT_SETS WRS
, OE_ORDER_TYPES_V OT
, WSH_DELIVERY_LEGS WLG
, WSH_TRIPS WT
, WSH_TRIP_STOPS WTS
, WSH_NEW_DELIVERIES WND
, WSH_PICK_GROUPING_RULES PGR
, WSH_PICK_SEQUENCE_RULES PSQR
, OE_ORDER_HEADERS_ALL H
, OE_LOOKUPS S1
, OE_LOOKUPS S2
, OE_LOOKUPS S3
, OE_SETS OS
, WSH_PICKING_BATCHES WPB
, WSH_LOCATIONS WSTL
, WSH_LOCATIONS WSFL
, WSH_LOCATIONS WSSL
WHERE WT.TRIP_ID(+) = WTS.TRIP_ID
AND WTS.STOP_ID(+) = WLG.PICK_UP_STOP_ID
AND WLG.DELIVERY_ID(+) = WND.DELIVERY_ID
AND WND.DELIVERY_ID(+) = WPB.DELIVERY_ID
AND NVL(WLG.SEQUENCE_NUMBER
, -1) = ( SELECT NVL(MIN(G.SEQUENCE_NUMBER)
, -1) FROM WSH_DELIVERY_LEGS G WHERE G.DELIVERY_ID(+) = WND.DELIVERY_ID )
AND CUST.CUST_ACCOUNT_ID(+) = WPB.CUSTOMER_ID
AND ORG.ORGANIZATION_ID(+) = WPB.ORGANIZATION_ID
AND H_ORG_TL.ORGANIZATION_ID(+) = WPB.ORGANIZATION_ID AND H_ORG_TL.LANGUAGE(+) = USERENV('LANG')
AND WRS.REPORT_SET_ID(+) = TO_NUMBER(DECODE(WPB.DOCUMENT_SET_ID
, ''
, '-1'
, 'S'
, '3'
, 'B'
, '3'
, 'C'
, '3'
, 'N'
, '3'
, WPB.DOCUMENT_SET_ID) )
AND OT.ORDER_TYPE_ID(+) = NVL(WPB.ORDER_TYPE_ID
, -1)
AND H.HEADER_ID(+) = NVL(WPB.ORDER_HEADER_ID
, -1)
AND OS.SET_ID(+) = WPB.SHIP_SET_NUMBER
AND S1.LOOKUP_TYPE(+) = 'PICK_RELEASE_OPTIONS'
AND S1.LOOKUP_CODE(+) = WPB.BACKORDERS_ONLY_FLAG
AND S2.LOOKUP_TYPE(+) = 'SHIPMENT_PRIORITY'
AND S2.LOOKUP_CODE(+) = WPB.SHIPMENT_PRIORITY_CODE
AND S3.LOOKUP_TYPE(+) = 'YES_NO'
AND S3.LOOKUP_CODE(+) = WPB.EXISTING_RSVS_ONLY_FLAG
AND PGR.PICK_GROUPING_RULE_ID(+) = WPB.PICK_GROUPING_RULE_ID
AND PSQR.PICK_SEQUENCE_RULE_ID(+) = WPB.PICK_SEQUENCE_RULE_ID
AND WPB.SHIP_TO_LOCATION_ID = WSTL.WSH_LOCATION_ID (+)
AND WPB.SHIP_FROM_LOCATION_ID = WSFL.WSH_LOCATION_ID (+)
AND WTS.STOP_LOCATION_ID = WSSL.WSH_LOCATION_ID (+)
AND NVL(WND.SHIPMENT_DIRECTION
, 'O') IN ('O'
, 'IO')
AND NVL(WTS.SHIPMENTS_TYPE_FLAG
, 'O') IN ('O'
, 'M')
AND NVL(WT.SHIPMENTS_TYPE_FLAG
, 'O') IN ('O'
, 'M')

Columns