[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CP_LAST_UPDATE_DATE_FROM := to_char(P_LAST_UPDATE_DATE_FROM,C_DATE_FORMAT);
CP_LAST_UPDATE_DATE_TO := to_char(P_LAST_UPDATE_DATE_TO,C_DATE_FORMAT);
SELECT
NAME
FROM
HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = P_ORG_ID;
P_CREATION_UPDATE_DATE := ' ';
P_CREATION_UPDATE_DATE := ' and we.creation_date >= :p_creation_date_from ';
P_CREATION_UPDATE_DATE := P_CREATION_UPDATE_DATE || ' and we.creation_date - ( 86399/86400 ) <= :p_creation_date_to ';
IF (P_LAST_UPDATE_DATE_FROM IS NOT NULL) THEN
P_CREATION_UPDATE_DATE := P_CREATION_UPDATE_DATE || ' and we.last_update_date >= :p_last_update_date_from ';
IF (P_LAST_UPDATE_DATE_TO IS NOT NULL) THEN
P_CREATION_UPDATE_DATE := P_CREATION_UPDATE_DATE || ' and we.last_update_date - ( 86399/86400 ) <= :p_last_update_date_to ';
L_DEL_LINES_CONT := 'UNION select
EXCEPTION_ID
,LOGGED_AT_LOCATION_ID
,LE.MEANING LOGGING_ENTITY_MEANING
,EXCEPTION_NAME
,EXCEPTION_LOCATION_ID
,MESSAGE
,SEV.MEANING SEVERITY_MEANING
,MANUALLY_LOGGED
,STA.MEANING STATUS_MEANING
,we.TRIP_ID
,TRIP_NAME
,we.TRIP_STOP_ID
,we.DELIVERY_ID
,DELIVERY_NAME
,we.DELIVERY_DETAIL_ID
,DELIVERY_ASSIGNMENT_ID
,we.CONTAINER_NAME
, P_ITEM_FLEXSQL C_ITEM_FLEXDAT
,we.INVENTORY_ITEM_ID
,we.LOT_NUMBER
-- HW OPM Convergence Project. Commented Sublot
-- ,we.SUBLOT_NUMBER
,we.REVISION
,we.SERIAL_NUMBER
,UNIT_OF_MEASURE
,UNIT_OF_MEASURE2
,QUANTITY
,QUANTITY2
,we.SUBINVENTORY
,we.LOCATOR_ID
, P_LOCATOR_FLEXSQL C_LOCATOR_FLEXDAT
,ARRIVAL_DATE
,DEPARTURE_DATE
,ERROR_MESSAGE
,we.CREATION_DATE
,we.REQUEST_ID
,WND.NAME DEL_NAME
,WT.NAME TRP_NAME
from
wsh_exceptions we,
mtl_system_items sys,
mtl_item_locations loc, ' || P_WSH_PICKING_BATCHES_TABLE || ' wsh_lookups sta,
wsh_lookups sev,
wsh_lookups le,
WSH_NEW_DELIVERIES WND,
WSH_TRIPS WT,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_DETAILS WDD
where
sta.lookup_type = ''EXCEPTION_STATUS''
and sta.lookup_code = we.status
and sev.lookup_type IN ( ''EXCEPTION_SEVERITY'',''EXCEPTION_BEHAVIOR'')
and sev.lookup_code = we.severity
and le.lookup_type = ''LOGGING_ENTITY''
and le.lookup_code = logging_entity
and sys.organization_id(+) = we.exception_location_id
and sys.inventory_item_id(+) = we.inventory_item_id
and loc.inventory_location_id(+) = we.locator_id
and loc.organization_id(+) = we.exception_location_id ' || P_WE_WPB_OUTER_JOIN || ' and (we.exception_name IS NULL OR we.exception_name not like ''WSH_IB%'')
and we.DELIVERY_ID = WND.DELIVERY_ID (+)
and we.TRIP_ID = WT.TRIP_ID (+)
and we.TRIP_STOP_ID = WTS.STOP_ID (+)
and we.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID (+)
and ( WND.SHIPMENT_DIRECTION is null or WND.SHIPMENT_DIRECTION IN (''O'', ''IO'') ) --Bug# 3748009
and ( WND.DELIVERY_TYPE is null or WND.DELIVERY_TYPE = ''STANDARD'') -- R12 MDC changes
and ( WT.SHIPMENTS_TYPE_FLAG is null or WT.SHIPMENTS_TYPE_FLAG IN (''O'', ''M'') ) --Bug# 3748009
and ( WTS.SHIPMENTS_TYPE_FLAG is null or WTS.SHIPMENTS_TYPE_FLAG IN (''O'', ''M'') ) --Bug# 3748009
and ( WDD.LINE_DIRECTION is null or WDD.LINE_DIRECTION IN (''O'', ''IO'') ) ';
SELECT we1.exception_id
FROM wsh_exceptions we1, wsh_delivery_assignments_v wda
WHERE we1.delivery_detail_id = wda.delivery_detail_id ';
SELECT we1.exception_id
FROM wsh_exceptions we1, wsh_delivery_assignments_v wda, wsh_delivery_details wdd1
WHERE we1.delivery_detail_id = wdd1.delivery_detail_id --LPN Synch Up.added delivery_detail_id instead of container_name..samanna
AND wdd1.container_flag = ''Y''
AND wdd1.delivery_detail_id = wda.delivery_detail_id ';
SELECT
CONTAINER_NAME,
CONTAINER_FLAG
FROM
WSH_DELIVERY_DETAILS WDD
WHERE WDD.DELIVERY_DETAIL_ID = X_DETAIL_ID;
SELECT
NAME
FROM
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA
WHERE WDD.DELIVERY_DETAIL_ID = P_DELIVERY_DETAIL_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND WND.DELIVERY_TYPE = 'STANDARD';