[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
For every New stop entered or Updated
1. Check if stop sequence number is positive integer
2. Check if stop status is OPEN for update,OPEN for insert as well
3. Check if the new planned arrival date is greater than the planned arrival date of
arrived or closed stop
FP Bug 425334,per bug 4245339, validation of uniqueness is deferred to handle_internal_stops in WSHTRACB.pls.
*/
-- Stop id is there as of now,but no validation,in case required later
PROCEDURE validate_sequence_number
(p_stop_id IN NUMBER,
p_stop_sequence_number IN NUMBER,
p_trip_id IN NUMBER,
p_status_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR c_lock_trip IS
SELECT trip_id
FROM wsh_trips
WHERE trip_id = p_trip_id
FOR UPDATE NOWAIT;
SELECT nvl(max(stop_sequence_number),0) stop_sequence_number
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND status_code IN ('AR','CL');
for update it will be populated
*/
PROCEDURE validate_unique_sequence
(p_trip_id IN NUMBER,
p_stop_id IN NUMBER,
p_stop_sequence_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR c_check_unique IS
SELECT stop_id
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND stop_sequence_number = p_stop_sequence_number
AND rownum = 1 ;
/* for update check if the new sequence number is same as existing value
then success */
IF (
(p_stop_id IS NOT NULL
AND l_stop_id = p_stop_id
) OR
(c_check_unique%NOTFOUND) OR
(l_stop_id IS NULL)
)THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
SELECT stop_sequence_number,
stop_id
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND status_code = 'OP' -- this is a case only for Open stops
AND stop_id <> p_stop_id ;
SELECT pick_up_stop_id,
drop_off_stop_id,
wdl.delivery_id
FROM wsh_delivery_legs wdl
WHERE (pick_up_stop_id = p_stop_id
OR drop_off_stop_id = p_stop_id);
SELECT pick_up_stop_id,
drop_off_stop_id,
delivery_id
FROM wsh_delivery_legs
WHERE drop_off_stop_id = p_stop_id;
SELECT nvl(max(stop_sequence_number),0) stop_sequence_number
FROM wsh_trip_stops
WHERE trip_id = p_trip_id;
select wnd.delivery_id,
wnd.organization_id,
wnd.status_code,
wnd.planned_flag,
/*J inbound logistics new column jckwok*/
NVL(wnd.shipment_direction, 'O') shipment_direction,
wnd.delivery_type, --MDC
NVL(wnd.ignore_for_planning, 'N') ignore_for_planning, --OTM R12, glog proj
NVL(wnd.tms_interface_flag,WSH_TRIP_STOPS_PVT.C_TMS_NOT_TO_BE_SENT) tms_interface_flag, -- OTM R12, glog proj
NVL(mcp.otm_enabled,wsp.otm_enabled) otm_enabled, -- LSP PROJECT : checking OTM enabled flag on client parameters.
wnd.client_id -- LSP PROJECT
from wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_shipping_parameters wsp,
mtl_client_parameters_v mcp
where wnd.delivery_id = wdl.delivery_id
and (wdl.pick_up_stop_id = p_stop_id
OR wdl.drop_off_stop_id = p_stop_id )
and wnd.organization_id = wsp.organization_id
and wnd.client_id = mcp.client_id (+);
select distinct 'X'
from wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl
where (wdl.pick_up_stop_id = p_stop_id OR wdl.drop_off_stop_id = p_stop_id)
and wnd.delivery_id = wdl.delivery_id
and wda.delivery_id = wnd.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.source_code = 'WSH'
and wdd.container_flag = 'N';
select wnd.delivery_id,
wnd.delivery_scpod_wf_process,
wnd.del_wf_intransit_attr,
wnd.del_wf_close_attr,
decode(p_stop_id,wdl.drop_off_stop_id,'D',wdl.pick_up_stop_id,'P','X') stop_type
from wsh_new_deliveries wnd,
wsh_delivery_legs wdl
where wnd.delivery_id = wdl.delivery_id
and (wdl.pick_up_stop_id = p_stop_id OR
wdl.drop_off_stop_id = p_stop_id );
ELSIF (p_action IN('UPDATE-STATUS')) THEN
l_override_wf:= fnd_profile.value('WSH_OVERRIDE_SCPOD_WF');
SELECT trip_id
FROM wsh_trip_stops
WHERE stop_id = p_stop_rec.STOP_ID;
SELECT STOP_ID
,TRIP_ID
,STOP_LOCATION_ID
,STATUS_CODE
,STOP_SEQUENCE_NUMBER
,PLANNED_ARRIVAL_DATE
,PLANNED_DEPARTURE_DATE
,ACTUAL_ARRIVAL_DATE
,ACTUAL_DEPARTURE_DATE
,DEPARTURE_GROSS_WEIGHT
,DEPARTURE_NET_WEIGHT
,WEIGHT_UOM_CODE
,DEPARTURE_VOLUME
,VOLUME_UOM_CODE
,DEPARTURE_SEAL_CODE
,DEPARTURE_FILL_PERCENT
,TP_ATTRIBUTE_CATEGORY
,TP_ATTRIBUTE1
,TP_ATTRIBUTE2
,TP_ATTRIBUTE3
,TP_ATTRIBUTE4
,TP_ATTRIBUTE5
,TP_ATTRIBUTE6
,TP_ATTRIBUTE7
,TP_ATTRIBUTE8
,TP_ATTRIBUTE9
,TP_ATTRIBUTE10
,TP_ATTRIBUTE11
,TP_ATTRIBUTE12
,TP_ATTRIBUTE13
,TP_ATTRIBUTE14
,TP_ATTRIBUTE15
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CREATION_DATE
,CREATED_BY
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,WSH_LOCATION_ID
,TRACKING_DRILLDOWN_FLAG
,TRACKING_REMARKS
,CARRIER_EST_DEPARTURE_DATE
,CARRIER_EST_ARRIVAL_DATE
,LOADING_START_DATETIME
,LOADING_END_DATETIME
,UNLOADING_START_DATETIME
,UNLOADING_END_DATETIME
,p_stop_rec.ROWID
,p_stop_rec.TRIP_NAME
,p_stop_rec.STOP_LOCATION_CODE
,p_stop_rec.WEIGHT_UOM_DESC
,p_stop_rec.VOLUME_UOM_DESC
,p_stop_rec.LOCK_STOP_ID
,p_stop_rec.PENDING_INTERFACE_FLAG
,p_stop_rec.TRANSACTION_HEADER_ID
/*J inbound logistics jckwok */
,nvl(SHIPMENTS_TYPE_FLAG, 'O') SHIPMENTS_TYPE_FLAG
-- J: W/V Changes
,WV_FROZEN_FLAG
/* J TL/TP ttrichy */
, wkend_layover_stops
, wkday_layover_stops
, tp_stop_id
, physical_stop_id
, physical_location_id
, TMS_INTERFACE_FLAG -- OTM R12, glog proj
FROM wsh_trip_stops
WHERE stop_id = p_stop_rec.STOP_ID;
ELSIF p_in_rec.action_code = 'UPDATE' THEN
--
l_parent_entity_id := p_parent_entity_id;
END IF; /* if action = 'UPDATE' */
IF p_action IN ('PLAN','UNPLAN','UPDATE-STATUS','PICK-RELEASE',
'PICK-RELEASE-UI','DELETE')
THEN
i := i+1;
SELECT stop_sequence_number,
wts.status_code,
wts.trip_id,
nvl(wts.shipments_type_flag,'O') shipments_Type_flag,
stop_location_id,
wt.ship_method_code,
wt.carrier_id,
wt.mode_of_transport,
wt.service_level,
wt.status_code trip_status_code,
wt.seal_code trip_seal_Code,
wt.name trip_name,
-- J: W/V Changes
wts.departure_gross_weight,
wts.departure_volume,
wts.physical_location_id
FROM wsh_trip_stops wts,
wsh_trips wt
WHERE stop_id = p_stop_id
AND wts.trip_id = wt.trip_id;
SELECT wts.stop_id,
wts.stop_sequence_number,
wts.status_code,
nvl(wts.shipments_type_flag,'O') shipments_Type_flag,
wts.stop_location_id,
wts.departure_gross_weight,
wts.departure_volume
FROM wsh_trip_stops wts
WHERE wts.physical_stop_id = p_stop_id
AND wts.trip_id = p_trip_id
AND wts.status_code IN ('OP', 'AR');
SELECT wts.stop_id,
wts.stop_sequence_number,
wts.status_code,
wts.shipments_type_flag shipments_Type_flag,
wts.stop_location_id,
wts.departure_gross_weight,
wts.departure_volume
FROM wsh_trip_stops wts
WHERE wts.physical_stop_id = p_stop_id
AND wts.trip_id = p_trip_id
AND wts.status_code IN ('OP', 'AR')
AND wts.shipments_type_flag = 'I';
SELECT nvl(shipments_type_flag,'O') shipments_Type_flag,
stop_location_id
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND status_code IN ('OP','AR')
AND stop_sequence_number < p_stop_sequence
AND stop_id <> NVL(p_linked_stop_id, -1)
order by stop_sequence_number;
SELECT nvl(shipment_direction,'O') shipment_direction
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl
WHERE wdl.pick_up_stop_id = p_stop_id
AND wnd.delivery_id = wdl.delivery_id
AND wnd.status_code IN ('OP', 'PA')
ORDER BY DECODE( NVL(wnd.shipment_direction,'O'), 'O', 1, 'IO', 1, 'I', 2, 'D', 2, 2 );
SELECT NVL(wnd.shipment_direction,'O') shipment_direction,
wnd.ultimate_dropoff_location_id,
wnd.status_code,
DECODE(wnd.ultimate_dropoff_location_id,p_stop_location_id,1,
(DECODE(wnd.ultimate_dropoff_location_id,p_dummy_location_id,1, 2))
) last_stop,
wdl.drop_off_stop_id drop_off_stop_id
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl
WHERE wdl.drop_off_stop_id IN (p_stop_id, p_dummy_stop_id)
AND wnd.delivery_id = wdl.delivery_id
AND (
(
NVL(wnd.shipment_direction,'O') IN ('O','IO')
AND wnd.status_code IN ('OP', 'PA', 'CO' )
)
OR
(
NVL(wnd.shipment_direction,'O') NOT IN ('O','IO')
AND wnd.status_code IN ('OP', 'IT' )
)
)
ORDER BY DECODE( NVL(wnd.shipment_direction,'O'), 'O', 1, 'IO', 1, 'I', 2, 'D', 2, 2 ) ASC, wnd.status_code DESC, last_stop ASC;
SELECT prev_leg_do_stop.status_code do_stop_status_code,
NVL(prev_leg_do_stop.shipments_type_flag,'O') do_stop_shipments_type_flag,
prev_leg_do_stop.stop_location_id do_stop_locationId,
prev_leg_do_stop.stop_id do_stop_id,
prev_leg_do_stop.stop_sequence_number do_stop_sequence_number,
prev_leg_pu_stop.status_code pu_stop_status_code,
NVL(prev_leg_pu_stop.shipments_type_flag,'O') pu_stop_shipments_type_flag,
prev_leg_pu_stop.stop_location_id pu_stop_locationId,
prev_leg_pu_stop.stop_id pu_stop_id,
NVL(wnd.shipment_direction,'O') shipment_direction,
wnd.status_code dlvy_status_code,
wnd.delivery_id delivery_id,
wnd.initial_pickup_location_id dlvy_initialPULocationId,
wnd.name dlvy_name,
DECODE(prev_leg_do_stop.status_code,'OP','OP','XX') do_stop_status_code_ord,
DECODE(prev_leg_pu_stop.status_code,'OP','OP','XX') pu_stop_status_code_ord,
prev_leg_do_stop.trip_id prev_leg_trip_id,
wt.name prev_leg_trip_name
FROM wsh_trip_stops prev_leg_do_stop,
wsh_trip_stops prev_leg_pu_stop,
wsh_trip_stops curr_leg_pu_stop,
wsh_delivery_legs prev_leg,
wsh_delivery_legs curr_leg,
wsh_new_deliveries wnd,
wsh_trips wt
WHERE prev_leg.drop_off_stop_id = prev_leg_do_stop.stop_id
--AND st1.status_code = 'OP'
AND prev_leg.pick_up_stop_id = prev_leg_pu_stop.stop_id
AND prev_leg_do_stop.stop_location_id = curr_leg_pu_stop.stop_location_id
AND prev_leg_do_stop.trip_id = wt.trip_id
AND prev_leg.delivery_id = curr_leg.delivery_id
AND curr_leg_pu_stop.stop_id = p_stop_id
AND curr_leg.pick_up_stop_id = p_stop_id
AND wnd.delivery_id = curr_leg.delivery_id
AND (
p_delivery_id IS NULL
OR
wnd.delivery_id = p_delivery_id
)
ORDER BY DECODE( NVL(wnd.shipment_direction,'O'), 'O', 1, 'IO', 1, 'I', 2, 'D', 2, 2 ), do_stop_status_code_ord, pu_stop_status_code_ord, wnd.delivery_id;
SELECT prev_leg_do_stop.status_code do_stop_status_code,
NVL(prev_leg_do_stop.shipments_type_flag,'O') do_stop_shipments_type_flag,
prev_leg_do_stop.stop_location_id do_stop_locationId,
prev_leg_do_stop.stop_id do_stop_id,
prev_leg_do_stop.stop_sequence_number do_stop_sequence_number,
prev_leg_pu_stop.status_code pu_stop_status_code,
NVL(prev_leg_pu_stop.shipments_type_flag,'O') pu_stop_shipments_type_flag,
prev_leg_pu_stop.stop_location_id pu_stop_locationId,
prev_leg_pu_stop.stop_id pu_stop_id,
NVL(wnd.shipment_direction,'O') shipment_direction,
wnd.status_code dlvy_status_code,
wnd.delivery_id delivery_id,
wnd.initial_pickup_location_id dlvy_initialPULocationId,
wnd.name dlvy_name,
DECODE(prev_leg_do_stop.status_code,'OP','OP','XX') do_stop_status_code_ord,
DECODE(prev_leg_pu_stop.status_code,'OP','OP','XX') pu_stop_status_code_ord,
prev_leg_do_stop.trip_id prev_leg_trip_id,
wt.name prev_leg_trip_name
FROM wsh_trip_stops prev_leg_do_stop,
wsh_trip_stops prev_leg_pu_stop,
wsh_trip_stops curr_leg_pu_stop,
wsh_delivery_legs prev_leg,
wsh_delivery_legs curr_leg,
wsh_new_deliveries wnd,
wsh_trips wt
WHERE prev_leg.drop_off_stop_id = prev_leg_do_stop.stop_id
--AND st1.status_code = 'OP'
AND prev_leg.pick_up_stop_id = prev_leg_pu_stop.stop_id
AND prev_leg_do_stop.stop_location_id = curr_leg_pu_stop.stop_location_id
AND prev_leg_do_stop.trip_id = wt.trip_id
AND prev_leg.delivery_id = curr_leg.delivery_id
AND curr_leg_pu_stop.stop_id = p_stop_id
AND curr_leg.pick_up_stop_id = p_stop_id
AND wnd.delivery_id = curr_leg.delivery_id
AND (
p_delivery_id IS NULL
OR
wnd.delivery_id = p_delivery_id
)
ORDER BY DECODE( NVL(wnd.shipment_direction,'O'), 'O', 1, 'IO', 1, 'I', 2, 'D', 2, 2 ), do_stop_status_code_ord, pu_stop_status_code_ord, wnd.delivery_id;
SELECT NVL(st1.shipments_type_flag,'O') pu_shipments_type_flag,
st1.status_code pu_stop_statusCode,
st1.stop_location_id pu_stop_locationId,
st1.stop_id pu_stop_id,
wnd.delivery_id delivery_id,
wnd.status_code delivery_statusCode,
wnd.initial_pickup_location_id dlvy_initialPULocationId
FROM wsh_trip_stops st1,
wsh_delivery_legs dl2,
wsh_new_deliveries wnd
WHERE dl2.pick_up_stop_id = st1.stop_id
AND st1.status_code = 'CL'
AND dl2.drop_off_stop_id IN (p_stop_id, p_linked_stop_id)
AND wnd.delivery_id = dl2.delivery_id
AND nvl(wnd.shipment_direction,'O') NOT IN ('O','IO');
SELECT dl.delivery_id,
dl.organization_id,
DECODE(NVL(dl.shipment_direction,'O'), 'IO', dl.ultimate_dropoff_location_id, NULL) io_location_id,
dl.name,
dl.status_code
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st
WHERE dg.delivery_id = dl.delivery_id
AND st.stop_location_id = dl.initial_pickup_location_id
AND st.stop_id = dg.pick_up_stop_id
AND st.stop_id = p_stop_id
AND NVL(dl.shipment_direction,'O') IN ('O','IO')
ORDER BY organization_id;
SELECT dl.delivery_id,
dl.organization_id,
dl.name,
dl.status_code,
dg.drop_off_stop_id
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st
WHERE dg.delivery_id = dl.delivery_id
AND st.stop_location_id = dl.ultimate_dropoff_location_id
AND st.stop_id = dg.drop_off_stop_id
AND st.stop_id IN (p_stop_id, p_dummy_stop_id)
AND NVL(dl.shipment_direction,'O') IN ('O','IO')
AND dl.status_code = 'IT'
ORDER BY organization_id;
SELECT pl.destination_organization_id, pl.destination_type_code
FROM wsh_delivery_assignments wda, wsh_delivery_details wdd,
oe_order_lines_all oel, po_requisition_lines_all pl
WHERE wda.delivery_id = c_delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_document_type_id = 10
AND wdd.source_line_id = oel.line_id
AND wdd.source_code = 'OE'
AND pl.requisition_line_id = oel.source_document_line_id
AND pl.destination_organization_id <> pl.source_organization_id;
SELECT intransit_type
FROM mtl_interorg_parameters
WHERE from_organization_id = c_from_organization_id
AND to_organization_id = c_to_organization_id;
x_out_rec.initial_pu_dlvy_recTbl.id_tbl.DELETE;
x_out_rec.initial_pu_dlvy_recTbl.name_tbl.DELETE;
x_out_rec.initial_pu_dlvy_recTbl.statusCode_tbl.DELETE;
x_out_rec.initial_pu_dlvy_recTbl.orgId_tbl.DELETE;
x_out_rec.ultimate_do_dlvy_recTbl.id_tbl.DELETE;
x_out_rec.ultimate_do_dlvy_recTbl.name_tbl.DELETE;
x_out_rec.ultimate_do_dlvy_recTbl.statusCode_tbl.DELETE;
x_out_rec.ultimate_do_dlvy_recTbl.orgId_tbl.DELETE;
x_out_rec.initial_pu_err_dlvy_id_tbl.DELETE;
l_exceptions_tbl.delete;
SELECT wnd.initial_pickup_date delivery_Date,
DECODE(wts.stop_location_id,wnd.initial_pickup_location_id,1,3) order_seq
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wts.stop_id = p_stop_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wdl.delivery_id = wnd.delivery_id
UNION
SELECT wnd.ultimate_dropoff_date delivery_Date,
DECODE(wts.stop_location_id,wnd.ultimate_dropoff_location_id,2,4) order_seq
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wts.stop_id = p_stop_id
AND wdl.drop_off_stop_id = wts.stop_id
AND wdl.delivery_id = wnd.delivery_id
ORDER BY order_seq ASC;
SELECT actual_departure_date
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND stop_sequence_number < p_stop_sequence_number
ORDER BY stop_sequence_number DESC;
SELECT actual_departure_date
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND stop_sequence_number > p_stop_sequence_number
ORDER BY stop_sequence_number ASC;
IF p_user_in_rec.LAST_UPDATE_DATE <> FND_API.G_MISS_DATE
AND NVL(p_user_in_rec.LAST_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.LAST_UPDATE_DATE,TO_DATE('2','j'))
THEN
l_attributes := l_attributes || 'LAST_UPDATE_DATE, ';
IF p_user_in_rec.LAST_UPDATED_BY <> FND_API.G_MISS_NUM
AND NVL(p_user_in_rec.LAST_UPDATED_BY,-99) <> NVL(p_out_rec.LAST_UPDATED_BY,-99)
THEN
l_attributes := l_attributes || 'LAST_UPDATED_BY, ';
IF p_user_in_rec.LAST_UPDATE_LOGIN <> FND_API.G_MISS_NUM
AND NVL(p_user_in_rec.LAST_UPDATE_LOGIN,-99) <> NVL(p_out_rec.LAST_UPDATE_LOGIN,-99)
THEN
l_attributes := l_attributes || 'LAST_UPDATE_LOGIN, ';
IF p_user_in_rec.PROGRAM_UPDATE_DATE <> FND_API.G_MISS_DATE
AND NVL(p_user_in_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j'))
THEN
l_attributes := l_attributes || 'PROGRAM_UPDATE_DATE, ';