[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wdd.DELIVERY_DETAIL_ID
, wdl.DELIVERY_ID
, 'Y'
, wdd.CUSTOMER_ID
, wdd.INVENTORY_ITEM_ID
, wdd.SHIP_FROM_LOCATION_ID
, wdd.ORGANIZATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.RELEASED_STATUS
, wdd.CONTAINER_FLAG
, wdd.DATE_REQUESTED
, wdd.DATE_SCHEDULED
, wdd.SHIP_METHOD_CODE
, wdd.CARRIER_ID
, wdd.PARTY_ID
, nvl(wdd.LINE_DIRECTION,'O')
, nvl(wdd.SHIPPING_CONTROL,'BUYER')
, NULL -- AGDUMMY
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl,
wsh_trip_stops wts1
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wdd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wda.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts1.stop_id
AND wts1.trip_id = c_trip_id;
SELECT wdd.DELIVERY_DETAIL_ID
, wdl.DELIVERY_ID
, 'Y'
, wdd.CUSTOMER_ID
, wdd.INVENTORY_ITEM_ID
, wdd.SHIP_FROM_LOCATION_ID
, wdd.ORGANIZATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.RELEASED_STATUS
, wdd.CONTAINER_FLAG
, wdd.DATE_REQUESTED
, wdd.DATE_SCHEDULED
, wdd.SHIP_METHOD_CODE
, wdd.CARRIER_ID
, wdd.PARTY_ID
, nvl(wdd.LINE_DIRECTION,'O')
, nvl(wdd.SHIPPING_CONTROL,'BUYER')
, NULL -- AGDUMMY
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_new_deliveries wnd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wdd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wda.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts1.stop_id
AND wts1.trip_id = c_trip_id
AND wnd.delivery_id = wdl.delivery_id
AND wnd.delivery_type = 'STANDARD';
SELECT wdd.DELIVERY_DETAIL_ID
, wdl1.DELIVERY_ID
, 'Y'
, wdd.CUSTOMER_ID
, wdd.INVENTORY_ITEM_ID
, wdd.SHIP_FROM_LOCATION_ID
, wdd.ORGANIZATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.RELEASED_STATUS
, wdd.CONTAINER_FLAG
, wdd.DATE_REQUESTED
, wdd.DATE_SCHEDULED
, wdd.SHIP_METHOD_CODE
, wdd.CARRIER_ID
, wdd.PARTY_ID
, nvl(wdd.LINE_DIRECTION,'O')
, nvl(wdd.SHIPPING_CONTROL,'BUYER')
, NULL -- AGDUMMY
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_trip_stops wts1,
wsh_delivery_legs wdl1,
wsh_delivery_legs wdl2,
wsh_new_deliveries wnd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wdd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wda.delivery_id = wdl1.delivery_id
AND wdl1.parent_delivery_leg_id = wdl2.delivery_leg_id
AND wdl2.pick_up_stop_id = wts1.stop_id
AND wts1.trip_id = c_trip_id
AND wnd.delivery_id = wdl2.delivery_id
AND wnd.delivery_type = 'CONSOLIDATION';
SELECT wnd.DELIVERY_ID
, c_trip_id
, 'Y'
, wnd.NAME
, wnd.PLANNED_FLAG
, wnd.STATUS_CODE
, wnd.INITIAL_PICKUP_DATE
, wnd.INITIAL_PICKUP_LOCATION_ID
, wnd.ULTIMATE_DROPOFF_LOCATION_ID
, wnd.ULTIMATE_DROPOFF_DATE
, wnd.CUSTOMER_ID
, wnd.INTMED_SHIP_TO_LOCATION_ID
, wnd.SHIP_METHOD_CODE
, wnd.DELIVERY_TYPE
, wnd.CARRIER_ID
, wnd.ORGANIZATION_ID
, wnd.SERVICE_LEVEL
, wnd.MODE_OF_TRANSPORT
, wnd.PARTY_ID
, nvl(wnd.SHIPMENT_DIRECTION,'O')
, nvl(wnd.SHIPPING_CONTROL,'BUYER')
, NULL -- AGDUMMY
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts1
WHERE wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts1.stop_id
AND nvl(wnd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wts1.trip_id = c_trip_id;
SELECT wts.STOP_ID
, 'Y' as exists_in_database
, wts.TRIP_ID
--To handle dummy locations #DUM_LOC(S)
, NVL(wts.PHYSICAL_LOCATION_ID,wts.STOP_LOCATION_ID)
, wts.STATUS_CODE
, wts.STOP_SEQUENCE_NUMBER
, wts.PLANNED_ARRIVAL_DATE
, wts.PLANNED_DEPARTURE_DATE
, wts.ACTUAL_ARRIVAL_DATE
, wts.ACTUAL_DEPARTURE_DATE
--#DUM_LOC(S)
, wts.PHYSICAL_LOCATION_ID
--#DUM_LOC(E)
--#SBAKSHI
, wts.PHYSICAL_STOP_ID
FROM wsh_trip_stops wts
WHERE wts.trip_id = c_tripid
order by wts.PLANNED_ARRIVAL_DATE;
SELECT wnd.DELIVERY_ID
, NULL
, 'Y'
, wnd.NAME
, wnd.PLANNED_FLAG
, wnd.STATUS_CODE
, wnd.INITIAL_PICKUP_DATE
, wnd.INITIAL_PICKUP_LOCATION_ID
, wnd.ULTIMATE_DROPOFF_LOCATION_ID
, wnd.ULTIMATE_DROPOFF_DATE
, wnd.CUSTOMER_ID
, wnd.INTMED_SHIP_TO_LOCATION_ID
, wnd.SHIP_METHOD_CODE
, wnd.DELIVERY_TYPE
, wnd.CARRIER_ID
, wnd.ORGANIZATION_ID
, wnd.SERVICE_LEVEL
, wnd.MODE_OF_TRANSPORT
, wnd.PARTY_ID
, nvl(wnd.SHIPMENT_DIRECTION,'O')
, nvl(wnd.SHIPPING_CONTROL,'BUYER')
, NULL -- AGDUMMY
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id = c_delivery_id;
SELECT wdd.DELIVERY_DETAIL_ID
, wda.DELIVERY_ID
, 'Y'
, wdd.CUSTOMER_ID
, wdd.INVENTORY_ITEM_ID
, wdd.SHIP_FROM_LOCATION_ID
, wdd.ORGANIZATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.RELEASED_STATUS
, wdd.CONTAINER_FLAG
, wdd.DATE_REQUESTED
, wdd.DATE_SCHEDULED
, wdd.SHIP_METHOD_CODE
, wdd.CARRIER_ID
, wdd.PARTY_ID
, nvl(wdd.LINE_DIRECTION,'O')
, nvl(wdd.SHIPPING_CONTROL,'BUYER')
, NULL --AGDUMMY
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wdd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wda.delivery_id = c_delivery_id;
SELECT wdd.DELIVERY_DETAIL_ID
, wda.DELIVERY_ID
, 'Y'
, wdd.CUSTOMER_ID
, wdd.INVENTORY_ITEM_ID
, wdd.SHIP_FROM_LOCATION_ID
, wdd.ORGANIZATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.RELEASED_STATUS
, wdd.CONTAINER_FLAG
, wdd.DATE_REQUESTED
, wdd.DATE_SCHEDULED
, wdd.SHIP_METHOD_CODE
, wdd.CARRIER_ID
, wdd.PARTY_ID
, nvl(wdd.LINE_DIRECTION,'O')
, nvl(wdd.SHIPPING_CONTROL,'BUYER')
, NULL --AGDUMMY
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl1,
wsh_delivery_legs wdl2
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wdd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wda.delivery_id = wdl1.delivery_id --c_delivery_id
AND wdl1.parent_delivery_leg_id = wdl2.delivery_leg_id
AND wdl2.delivery_id = c_delivery_id
AND wda.delivery_id is not null;
SELECT nvl(wts.physical_location_id,wts.stop_location_id),
--#DUM_LOC(E)
wts.stop_sequence_number,wts.planned_arrival_date
FROM wsh_trip_stops wts
WHERE wts.stop_id = c_stop_id
order by wts.planned_arrival_date;
SELECT wts.stop_sequence_number,wts.planned_arrival_date
FROM wsh_trip_stops wts
--#DUM_LOC(S)
WHERE nvl(wts.physical_location_id,wts.stop_location_id) = c_location_id
--#DUM_LOC(E)
AND wts.trip_id = c_trip_id;
g_comp_class_tab.DELETE;
g_comp_constraint_tab.DELETE;
g_reg_const_cache.DELETE;
g_regloc_loc_cache.DELETE;
SELECT *
FROM WSH_FTE_COMP_CLASSES
WHERE COMPATIBILITY_CLASS_ID = c_comp_class_id;
SELECT *
FROM WSH_FTE_COMP_CLASSES
WHERE COMPATIBILITY_CLASS_CODE = c_comp_class_code;
SELECT *
FROM WSH_FTE_COMP_CONSTRAINTS
WHERE COMPATIBILITY_ID = c_constraint_id;
p_failed_constraints.DELETE(k);
p_failed_constraints.DELETE(k);
SELECT wc.carrier_id
FROM wsh_locations wl,
hz_party_sites hps,
hz_parties hp,
wsh_carriers wc
WHERE wl.wsh_location_id = c_location_id
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND hps.party_id = hp.party_id
AND hp.party_id = wc.carrier_id;
SELECT hcas.cust_account_id
FROM wsh_locations wl,
hz_party_sites hps,
hz_cust_acct_sites_all hcas
WHERE wl.wsh_location_id = c_location_id
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND hps.party_site_id = hcas.party_site_id;
SELECT hz.party_id
FROM hz_parties hz,
po_vendors po,
hz_relationships rel,
hz_party_sites hps,
wsh_locations wl
WHERE wl.wsh_location_id = c_location_id
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND rel.relationship_type = 'POS_VENDOR_PARTY'
and rel.object_id = hz.party_id
and rel.object_table_name = 'HZ_PARTIES'
and rel.object_type = 'ORGANIZATION'
and rel.subject_table_name = 'PO_VENDORS'
and rel.subject_id = po.vendor_id
and rel.subject_type = 'POS_VENDOR'
AND hps.party_id = hz.party_id;
SELECT owner_party_id
FROM wsh_location_owners
WHERE owner_type = 1
AND wsh_location_id = c_location_id
AND owner_party_id <> -1 ;
SELECT location_id
FROM wsh_ship_from_orgs_v
WHERE organization_id = v_org_id;*/
SELECT *
FROM WSH_FTE_COMP_CONSTRAINTS
WHERE COMPATIBILITY_CLASS_ID = nvl(c_comp_class_id,COMPATIBILITY_CLASS_ID)
AND nvl(trunc(EFFECTIVE_DATE_FROM,'DDD'),trunc(sysdate,'DDD')) <= trunc(sysdate,'DDD')
AND nvl(trunc(EFFECTIVE_DATE_TO,'DDD'),trunc(sysdate,'DDD')) >= trunc(sysdate,'DDD');
g_comp_constraint_tab.DELETE;
SELECT *
FROM WSH_FTE_COMP_CONSTRAINTS
WHERE COMPATIBILITY_CLASS_ID = c_comp_class_id
AND CONSTRAINT_TYPE = 'I'
AND nvl(trunc(EFFECTIVE_DATE_FROM,'DDD'),trunc(sysdate,'DDD')) <= trunc(sysdate,'DDD')
AND nvl(trunc(EFFECTIVE_DATE_TO,'DDD'),trunc(sysdate,'DDD')) >= trunc(sysdate,'DDD');
x_failed_constraints.DELETE(l_const_count+1,x_failed_constraints.COUNT);
x_failed_constraints.DELETE(l_const_count+1,x_failed_constraints.COUNT);
l_items_tab.DELETE;
l_locations_list.DELETE;
l_target_items_tab.DELETE;
x_failed_constraints.DELETE(l_const_count+1,x_failed_constraints.COUNT);
x_failed_constraints.DELETE(l_const_count+1,x_failed_constraints.COUNT);
x_failed_constraints.DELETE(l_const_count+1,x_failed_constraints.COUNT);
x_failed_constraints.DELETE(l_const_count+1,x_failed_constraints.COUNT);
SELECT wts1.planned_arrival_date, wts2.planned_arrival_date
FROM wsh_trip_stops wts1, wsh_trip_stops wts2, wsh_delivery_legs wdl
WHERE wts1.trip_id = p_trip_id
AND wts2.trip_id = p_trip_id
AND wdl.delivery_id = p_delivery_id
AND wts1.stop_id = wdl.pick_up_stop_id
AND wts2.stop_id = wdl.drop_off_stop_id;
l_locations_list.DELETE;
l_items_tab.DELETE;
x_failed_constraint.DELETE(l_const_count+1,x_failed_constraint.COUNT);
x_failed_constraint.DELETE(l_const_count+1,x_failed_constraint.COUNT);
SELECT initial_pickup_location_id,
ultimate_dropoff_location_id
FROM wsh_new_deliveries
WHERE delivery_id = c_delivery_id;
SELECT * from
wsh_constraint_dleg_tmp v -- global temporary table
where v.delivery_id = c_delivery_id
CONNECT BY PRIOR v.drop_off_stop_location_id = v.pick_up_stop_location_id
START WITH v.pick_up_stop_location_id NOT IN (select nvl(a.physical_location_id,a.stop_location_id)
from wsh_delivery_legs c,wsh_trip_stops a where
c.delivery_id = c_delivery_id and
c.drop_off_stop_id = a.stop_id
);
SELECT COUNT(*)
INTO l_tmp_count
FROM wsh_constraint_dleg_tmp
WHERE delivery_id = p_delivery_id;
SELECT nvl(wts1.physical_location_id,wts1.stop_location_id) pickup_stop_loc,
wts1.stop_sequence_number pickup_stop_seq,
nvl(wts2.physical_location_id,wts2.stop_location_id) dropoff_stop_loc ,
wts2.stop_sequence_number dropoff_stop_seq,
wdl.sequence_number,wt.trip_id
--#DUM_LOC(E)
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trips wt,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wnd.delivery_id = c_delivery_id
AND wdl.delivery_id = wnd.delivery_id
AND wdl.pick_up_stop_id = wts1.stop_id
AND wdl.drop_off_stop_id = wts2.stop_id
AND wts1.trip_id = wt.trip_id
AND wts2.trip_id = wt.trip_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into wsh_constraint_dleg_tmp ');
INSERT INTO wsh_constraint_dleg_tmp (
SELECT wdl.delivery_leg_id,wdl.sequence_number,
wdl.pick_up_stop_id, wdl.drop_off_stop_id,
nvl(wts1.physical_location_id,wts1.stop_location_id) ,
nvl(wts2.physical_location_id,wts2.stop_location_id) ,
wts1.planned_arrival_date ,
wts2.planned_arrival_date ,
wts1.trip_id,
wdl.delivery_id
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wdl.delivery_id = l_delivery_ids(q)
AND wdl.pick_up_stop_id = wts1.stop_id
AND wdl.drop_off_stop_id = wts2.stop_id
AND wdl.delivery_leg_id NOT IN
( select wcdt.delivery_leg_id
from wsh_constraint_dleg_tmp wcdt
where wcdt.delivery_leg_id = wdl.delivery_leg_id)
);
SELECT COUNT(*)
INTO l_tmp_count
FROM wsh_constraint_dleg_tmp;
WSH_DEBUG_SV.logmsg(l_module_name,'Inserted :'||l_tmp_count||' rows into wsh_constraint_dleg_tmp ');
l_stop_locations.DELETE;
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
x_failed_constraints.DELETE(l_const_count+1,x_failed_constraints.COUNT);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
p_entity_tab.DELETE(k);
SELECT TRIP_ID, 'Y'as EXISTS_IN_DATABASE, NAME, PLANNED_FLAG, STATUS_CODE, VEHICLE_ITEM_ID, VEHICLE_NUMBER,
CARRIER_ID, SHIP_METHOD_CODE, VEHICLE_ORGANIZATION_ID, VEHICLE_NUM_PREFIX, SERVICE_LEVEL,
MODE_OF_TRANSPORT
FROM wsh_trips where trip_id=l_tripid;
select wts.TRIP_ID
from wsh_trip_stops wts, wsh_delivery_legs wdl
where wdl.delivery_id =l_deliveryid AND
wdl.pick_up_stop_id = wts.stop_id;
g_itm_mustuse_cache.DELETE;
g_itmloc_mustuse_cache.DELETE;
g_itm_exclusive_cache.DELETE;
g_fac_exclusive_cache.DELETE;
IF p_action_code NOT IN (G_AUTOCRT_DLVY_TRIP,G_ASSIGN_DLVY_TRIP,G_UPDATE_DLVY,G_CREATE_DLVY,G_AUTOCRT_MDC) OR
p_action_code IS NULL THEN
RAISE g_invalid_action_code;
IF p_action_code IN (G_UPDATE_DLVY,G_CREATE_DLVY) AND p_delivery_info.COUNT = 0 THEN
RAISE g_invalid_action_code;
p_action_code = G_UPDATE_DLVY THEN
-- l_upd_delivery_rec may have a dummy location,
-- Changing l_delivery_info,ultimate dropoff location id handles this case.
OPEN c_get_dlvy(p_delivery_info(i).DELIVERY_ID);
Update (delivery, delivery leg) UPD (Mode -FAC_MOD, ITM_MOD Carrier - ITM_CAR,FAC_CAR
*/
-- Assumptions :
-- 1. When you are here and have a delivery that means
-- within a delivery, all attributes are valid
-- When assigning deliveries to a trip
-- 1. It creates trips for deliveries which do not have a trip at that point
-- 2. If the deliveries under creation all have same ship method, it defaults that to the trip
-- 3. Creates stops only for initial pickup and ultimate droppff, ignores intermediate
-- 4. When creating trip stops creates sequence numbers arbitrarily
-- ascending (eg. 10, 20, 30 etc.) as it finds new locations from the
-- list of delivery
-- 5. Assign to trip can pass a delivery and a target trip and
-- override pickup stop and dropoff stop of those of the delivery
-- 6. All validations on trip stops are based on stop sequence number and
-- not stop dates
-- Record types : Child, entity, group
-- Child : child id, entity id
-- Entity : entity id, pickup, dropoff, org, customer, group id
-- Group : Group id
-- Here entity : Delivery
-- For every delivery : search existing groups if can be added
-- If not, create a new group else add to the matching group and proceed
-- search routine should take entity record as well as children table
-- This loop checks only itm-itm and cus-cus for every delivery
-- against every other delivery
-- also itm-fac with pickup / dropoff locations of other delivery
-- also com-fac, cus-fac with pickup / dropoff locations of other delivery
-- Following done for ACT and ADT
-- Valid groups created looking at only Exclusive constraints
--
--DUM_LOC We have modified record p_target_tripstops to store physical locations
--in case of dummy locations.
IF p_action_code = G_ASSIGN_DLVY_TRIP AND
( p_target_tripstops.pickup_location_id IS NOT NULL OR
p_target_tripstops.dropoff_location_id IS NOT NULL ) THEN
IF (l_target_trip.carrier_id IS NULL OR l_target_trip.MODE_OF_TRANSPORT IS NULL) AND
(l_target_trip.ship_method_code IS NOT NULL) THEN
l_carrier_service_inout_rec.ship_method_code := l_target_trip.ship_method_code;
l_entity_tab.DELETE(l_delivery_info_mod); --Bug 9222910
IF p_action_code IN (G_UPDATE_DLVY,G_CREATE_DLVY) THEN
-- Need to skip an Inbound delivery
-- if the trip's status is "In Transit" or "Closed"
IF l_delivery_info(k).status_code IN ('IT','CL') AND
l_delivery_info(k).shipment_direction = 'I' THEN
GOTO next_delivery;
x_failed_lines.DELETE;
l_failed_constraints.DELETE;
SELECT
count(*)
FROM wsh_delivery_legs
WHERE parent_delivery_leg_id = p_delivery_leg_id;
g_itm_mustuse_cache.DELETE;
g_itmloc_mustuse_cache.DELETE;
g_itm_exclusive_cache.DELETE;
g_fac_exclusive_cache.DELETE;
IF p_action_code IN (G_UPDATE_DLEG,G_DLEG_CRT) AND
l_dleg_dlvy_rec.shipping_control <> 'SUPPLIER' THEN
-- FAC - CAR/MOD for dleg against dleg's locations
IF l_comp_class_tab.EXISTS(G_FACILITY_CARRIER_NUM) AND
l_delivery_leg_rec.carrier_id IS NOT NULL THEN
validate_constraint(
p_comp_class_code => G_FACILITY_CARRIER,
p_object1_type => 'FAC',
p_object1_val_num => l_delivery_leg_rec.pickupstop_location_id,
p_object2_type => 'CAR',
p_object2_val_num => l_delivery_leg_rec.carrier_id,
x_validate_result => l_validate_faccar_result,
x_failed_constraint => l_failed_constraint,
x_return_status => l_return_status);
SELECT wdd.DELIVERY_DETAIL_ID,wda.DELIVERY_ID,'Y',wdd.CUSTOMER_ID,
wdd.INVENTORY_ITEM_ID,wdd.SHIP_FROM_LOCATION_ID,wdd.ORGANIZATION_ID,
wdd.SHIP_TO_LOCATION_ID,wdd.INTMED_SHIP_TO_LOCATION_ID,wdd.RELEASED_STATUS,
wdd.CONTAINER_FLAG,wdd.DATE_REQUESTED,wdd.DATE_SCHEDULED,wdd.SHIP_METHOD_CODE,
wdd.CARRIER_ID,wdd.party_id,nvl(wdd.line_direction,'O')
,nvl(wdd.shipping_control,'BUYER'),NULL
FROM WSH_DELIVERY_DETAILS wdd,
wsh_delivery_assignments_v wda
WHERE wda.DELIVERY_DETAIL_ID = wdd.DELIVERY_DETAIL_ID
AND nvl(wdd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wda.PARENT_DELIVERY_DETAIL_ID = c_container_id;
SELECT wdd.DELIVERY_DETAIL_ID,null,'Y',wdd.CUSTOMER_ID,
wdd.INVENTORY_ITEM_ID,wdd.SHIP_FROM_LOCATION_ID,wdd.ORGANIZATION_ID,
wdd.SHIP_TO_LOCATION_ID,wdd.INTMED_SHIP_TO_LOCATION_ID,wdd.RELEASED_STATUS,
wdd.CONTAINER_FLAG,wdd.DATE_REQUESTED,wdd.DATE_SCHEDULED,wdd.SHIP_METHOD_CODE,
wdd.CARRIER_ID,wdd.party_id,nvl(wdd.line_direction,'O')
,nvl(wdd.shipping_control,'BUYER'),NULL
--DUM_LOC
FROM WSH_DELIVERY_DETAILS wdd
WHERE wdd.DELIVERY_DETAIL_ID = c_detail_id;
SELECT wda.DELIVERY_ID
FROM wsh_delivery_assignments_v wda
WHERE wda.delivery_detail_id = c_detail_id;
g_itm_mustuse_cache.DELETE;
g_itmloc_mustuse_cache.DELETE;
g_itm_exclusive_cache.DELETE;
g_fac_exclusive_cache.DELETE;
l_items_tab.DELETE;
l_locations_list.DELETE;
x_line_groups.DELETE(l_linegroup_indx);
x_line_groups.DELETE(l_linegroup_indx);
x_line_groups.DELETE(l_linegroup_indx);
x_line_groups.DELETE(l_linegroup_indx);
x_line_groups.DELETE(l_linegroup_indx);
l_failed_constraints.DELETE;
g_itm_mustuse_cache.DELETE;
g_itmloc_mustuse_cache.DELETE;
g_itm_exclusive_cache.DELETE;
g_fac_exclusive_cache.DELETE;
SELECT wt.TRIP_ID, 'Y'as exists_in_database, wt.NAME, wt.PLANNED_FLAG, wt.STATUS_CODE, wt.VEHICLE_ITEM_ID, wt.VEHICLE_NUMBER,
wt.CARRIER_ID, wt.SHIP_METHOD_CODE, wt.VEHICLE_ORGANIZATION_ID, wt.VEHICLE_NUM_PREFIX, wt.SERVICE_LEVEL,
wt.MODE_OF_TRANSPORT
FROM wsh_trips wt, wsh_trip_stops wts
WHERE wts.stop_id = l_stop_id
AND wts.trip_id= wt.trip_id;
SELECT wt.TRIP_ID, 'Y'as exists_in_database, wt.NAME, wt.PLANNED_FLAG, wt.STATUS_CODE, wt.VEHICLE_ITEM_ID, wt.VEHICLE_NUMBER,
wt.CARRIER_ID, wt.SHIP_METHOD_CODE, wt.VEHICLE_ORGANIZATION_ID, wt.VEHICLE_NUM_PREFIX, wt.SERVICE_LEVEL,
wt.MODE_OF_TRANSPORT
FROM wsh_trips wt
WHERE wt.trip_id = l_trip_id;
SELECT wts1.stop_sequence_number pickup_stop_seq,
wts2.stop_sequence_number dropoff_stop_seq,
wts1.planned_arrival_date pickup_stop_pa_date,
wts2.planned_arrival_date dropoff_stop_pa_date
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_trip_stops wts2
WHERE wnd.delivery_id = c_delivery_id
AND wdl.delivery_id = wnd.delivery_id
AND wdl.pick_up_stop_id = wts1.stop_id
AND wdl.drop_off_stop_id = wts2.stop_id
AND wts1.trip_id = c_trip_id;
g_itm_mustuse_cache.DELETE;
g_itmloc_mustuse_cache.DELETE;
g_itm_exclusive_cache.DELETE;
g_fac_exclusive_cache.DELETE;
IF p_action_code = G_UPDATE_STOP THEN
-- Changes made in cursor c_get_stop_location.
-- To ensure dummy locations are replaced by physical locations.
OPEN c_get_stop_location(l_stop_info(j).stop_id);
WSH_DEBUG_SV.logmsg(l_module_name,'Input stop values before this update : location_id : '||l_old_stop_location_id||' seq_num : '||l_old_stop_seq_num||' Planned arrival date : '||to_char(l_old_stop_pa_date,'DD-MON-YYYY HH24:MI:SS'));
IF p_action_code <> G_DELETE_STOP THEN
--LOOP -- Over parent trips
k := l_parent_trip_info.FIRST;
END IF; -- p_action_code <> G_DELETE_STOP
SELECT wdd.DELIVERY_DETAIL_ID
, wda.DELIVERY_ID
, 'Y'
, wdd.CUSTOMER_ID
, wdd.INVENTORY_ITEM_ID
, wdd.SHIP_FROM_LOCATION_ID
, wdd.ORGANIZATION_ID
, wdd.SHIP_TO_LOCATION_ID
, wdd.INTMED_SHIP_TO_LOCATION_ID
, wdd.RELEASED_STATUS
, wdd.CONTAINER_FLAG
, wdd.DATE_REQUESTED
, wdd.DATE_SCHEDULED
, wdd.SHIP_METHOD_CODE
, wdd.CARRIER_ID
, wdd.PARTY_ID
, nvl(wdd.LINE_DIRECTION,'O')
, nvl(wdd.SHIPPING_CONTROL,'BUYER')
, NULL
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wdd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wda.delivery_id = c_delivery_id;
SELECT constraint_object2_id from wsh_fte_comp_constraints
WHERE constraint_object2_type = 'FAC'
AND constraint_object1_type = c_object1_type
AND constraint_object1_id = c_object1_id
AND Constraint_type = 'I'
AND compatibility_class_id = c_class_id -- 2 for Cus_FAC
AND nvl(trunc(EFFECTIVE_DATE_FROM,'DDD'),trunc(sysdate,'DDD')) <= trunc(sysdate,'DDD')
AND nvl(trunc(EFFECTIVE_DATE_TO,'DDD'),trunc(sysdate,'DDD')) >= trunc(sysdate,'DDD');
SELECT constraint_object2_id from wsh_fte_comp_constraints
WHERE constraint_object1_type = 'CUS'
AND constraint_object2_type = 'FAC'
AND constraint_object1_id = c_object1_id
AND Constraint_type = 'I'
AND nvl(trunc(EFFECTIVE_DATE_FROM,'DDD'),trunc(sysdate,'DDD')) <= trunc(sysdate,'DDD')
AND nvl(trunc(EFFECTIVE_DATE_TO,'DDD'),trunc(sysdate,'DDD')) >= trunc(sysdate,'DDD');*/
SELECT constraint_object1_id, constraint_object2_id from wsh_fte_comp_constraints
WHERE constraint_type = 'I'
AND compatibility_class_id=12
AND nvl(trunc(EFFECTIVE_DATE_FROM,'DDD'),trunc(sysdate,'DDD')) <= trunc(sysdate,'DDD')
AND nvl(trunc(EFFECTIVE_DATE_TO,'DDD'),trunc(sysdate,'DDD')) >= trunc(sysdate,'DDD')
ORDER BY creation_date;
select wts.TRIP_ID
from wsh_trip_stops wts, wsh_delivery_legs wdl
where wdl.delivery_id =l_deliveryid AND
wdl.pick_up_stop_id = wts.stop_id;
SELECT wnd.DELIVERY_ID
, NULL
, 'Y'
, wnd.NAME
, wnd.PLANNED_FLAG
, wnd.STATUS_CODE
, wnd.INITIAL_PICKUP_DATE
, wnd.INITIAL_PICKUP_LOCATION_ID
, wnd.ULTIMATE_DROPOFF_LOCATION_ID
, wnd.ULTIMATE_DROPOFF_DATE
, wnd.CUSTOMER_ID
, wnd.INTMED_SHIP_TO_LOCATION_ID
, wnd.SHIP_METHOD_CODE
, wnd.DELIVERY_TYPE
, wnd.CARRIER_ID
, wnd.ORGANIZATION_ID
, wnd.SERVICE_LEVEL
, wnd.MODE_OF_TRANSPORT
, wnd.PARTY_ID
, nvl(wnd.SHIPMENT_DIRECTION,'O')
, nvl(wnd.SHIPPING_CONTROL,'BUYER')
, NULL -- AGDUMMY
FROM wsh_new_deliveries wnd
WHERE wnd.delivery_id = c_delivery_id;