[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select dd.delivery_detail_id, dd.transaction_temp_id,
to_number(msnt.serial_prefix) "quantity",
msnt.fm_serial_number, msnt.to_serial_number,
msnt.attribute_category, -- Bug 3628620
msnt.attribute1,
msnt.attribute2,
msnt.attribute3,
msnt.attribute4,
msnt.attribute5,
msnt.attribute6,
msnt.attribute7,
msnt.attribute8,
msnt.attribute9,
msnt.attribute10,
msnt.attribute11,
msnt.attribute12,
msnt.attribute13,
msnt.attribute14,
msnt.attribute15, -- End of Bug 3628620
dd.inventory_item_id, -- 3704188
--Bug 8467875
msnt.cycles_since_mark,
msnt.cycles_since_new,
msnt.cycles_since_overhaul,
msnt.cycles_since_repair,
msnt.cycles_since_visit,
msnt.c_attribute1,
msnt.c_attribute10,
msnt.c_attribute11,
msnt.c_attribute12,
msnt.c_attribute13,
msnt.c_attribute14,
msnt.c_attribute15,
msnt.c_attribute16,
msnt.c_attribute17,
msnt.c_attribute18,
msnt.c_attribute19,
msnt.c_attribute2,
msnt.c_attribute20,
msnt.c_attribute3,
msnt.c_attribute4,
msnt.c_attribute5,
msnt.c_attribute6,
msnt.c_attribute7,
msnt.c_attribute8,
msnt.c_attribute9,
msnt.d_attribute1,
msnt.d_attribute10,
msnt.d_attribute2,
msnt.d_attribute3,
msnt.d_attribute4,
msnt.d_attribute5,
msnt.d_attribute6,
msnt.d_attribute7,
msnt.d_attribute8,
msnt.d_attribute9,
msnt.number_of_repairs,
msnt.n_attribute1,
msnt.n_attribute10,
msnt.n_attribute2,
msnt.n_attribute3,
msnt.n_attribute4,
msnt.n_attribute5,
msnt.n_attribute6,
msnt.n_attribute7,
msnt.n_attribute8,
msnt.n_attribute9,
msnt.territory_code,
msnt.time_since_mark,
msnt.time_since_new,
msnt.time_since_overhaul,
msnt.time_since_repair,
msnt.time_since_visit,
msnt.serial_attribute_category
--Bug 8467875
from wsh_delivery_details dd,
wsh_delivery_assignments_v da,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
mtl_serial_numbers_temp msnt
where st.batch_id = p_batch_id
and st.stop_location_id = dl.initial_pickup_location_id
and dg.delivery_id = dl.delivery_id
and da.delivery_id = dl.delivery_id
and dd.delivery_detail_id = da.delivery_detail_id
-- bug 2787888 : removed oe_interfaced_flag and source_code comparison for OKE lines
and st.stop_id = dg.pick_up_stop_id
and dd.released_status = 'C'
and dd.container_flag='N'
and dd.transaction_temp_id = msnt.transaction_temp_id
-- and msnt.fm_serial_number <> nvl(msnt.to_serial_number, msnt.fm_serial_number)
and nvl(dl.shipment_direction , 'O') IN ('O', 'IO'); -- J Inbound Logistics jckwok
select dd.delivery_detail_id, dd.transaction_temp_id,
to_number(msnt.serial_prefix) "quantity",
msnt.fm_serial_number, msnt.to_serial_number,
msnt.attribute_category, -- Bug 3628620
msnt.attribute1,
msnt.attribute2,
msnt.attribute3,
msnt.attribute4,
msnt.attribute5,
msnt.attribute6,
msnt.attribute7,
msnt.attribute8,
msnt.attribute9,
msnt.attribute10,
msnt.attribute11,
msnt.attribute12,
msnt.attribute13,
msnt.attribute14,
msnt.attribute15, -- End of Bug 3628620
dd.inventory_item_id,-- 3704188
--Bug 8467875
msnt.cycles_since_mark,
msnt.cycles_since_new,
msnt.cycles_since_overhaul,
msnt.cycles_since_repair,
msnt.cycles_since_visit,
msnt.c_attribute1,
msnt.c_attribute10,
msnt.c_attribute11,
msnt.c_attribute12,
msnt.c_attribute13,
msnt.c_attribute14,
msnt.c_attribute15,
msnt.c_attribute16,
msnt.c_attribute17,
msnt.c_attribute18,
msnt.c_attribute19,
msnt.c_attribute2,
msnt.c_attribute20,
msnt.c_attribute3,
msnt.c_attribute4,
msnt.c_attribute5,
msnt.c_attribute6,
msnt.c_attribute7,
msnt.c_attribute8,
msnt.c_attribute9,
msnt.d_attribute1,
msnt.d_attribute10,
msnt.d_attribute2,
msnt.d_attribute3,
msnt.d_attribute4,
msnt.d_attribute5,
msnt.d_attribute6,
msnt.d_attribute7,
msnt.d_attribute8,
msnt.d_attribute9,
msnt.number_of_repairs,
msnt.n_attribute1,
msnt.n_attribute10,
msnt.n_attribute2,
msnt.n_attribute3,
msnt.n_attribute4,
msnt.n_attribute5,
msnt.n_attribute6,
msnt.n_attribute7,
msnt.n_attribute8,
msnt.n_attribute9,
msnt.territory_code,
msnt.time_since_mark,
msnt.time_since_new,
msnt.time_since_overhaul,
msnt.time_since_repair,
msnt.time_since_visit,
msnt.serial_attribute_category
--Bug 8467875
from wsh_delivery_details dd,
wsh_delivery_assignments_v da,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
mtl_serial_numbers_temp msnt
where st.batch_id = p_batch_id
and st.stop_location_id = dl.initial_pickup_location_id
and dg.delivery_id = dl.delivery_id
and da.delivery_id = dl.delivery_id
and dd.delivery_detail_id = da.delivery_detail_id
and st.stop_id = dg.pick_up_stop_id
and dd.released_status = 'C'
and dd.container_flag='N'
and NVL(dd.oe_interfaced_flag , 'N') = 'Y'
and dd.transaction_temp_id = msnt.transaction_temp_id
-- and msnt.fm_serial_number <> nvl(msnt.to_serial_number, msnt.fm_serial_number)
and nvl(dl.shipment_direction , 'O') IN ('O', 'IO');
select dd.delivery_detail_id, msn.group_mark_id,
dd.shipped_quantity "quantity",
dd.serial_number, dd.serial_number "to_serial_number",
msn.attribute_category, -- Bug 3628620
msn.attribute1,
msn.attribute2,
msn.attribute3,
msn.attribute4,
msn.attribute5,
msn.attribute6,
msn.attribute7,
msn.attribute8,
msn.attribute9,
msn.attribute10,
msn.attribute11,
msn.attribute12,
msn.attribute13,
msn.attribute14,
msn.attribute15, -- End of Bug 3628620
dd.inventory_item_id, -- 3704188
--Bug 8467875
msn.cycles_since_mark,
msn.cycles_since_new,
msn.cycles_since_overhaul,
msn.cycles_since_repair,
msn.cycles_since_visit,
msn.c_attribute1,
msn.c_attribute10,
msn.c_attribute11,
msn.c_attribute12,
msn.c_attribute13,
msn.c_attribute14,
msn.c_attribute15,
msn.c_attribute16,
msn.c_attribute17,
msn.c_attribute18,
msn.c_attribute19,
msn.c_attribute2,
msn.c_attribute20,
msn.c_attribute3,
msn.c_attribute4,
msn.c_attribute5,
msn.c_attribute6,
msn.c_attribute7,
msn.c_attribute8,
msn.c_attribute9,
msn.d_attribute1,
msn.d_attribute10,
msn.d_attribute2,
msn.d_attribute3,
msn.d_attribute4,
msn.d_attribute5,
msn.d_attribute6,
msn.d_attribute7,
msn.d_attribute8,
msn.d_attribute9,
msn.number_of_repairs,
msn.n_attribute1,
msn.n_attribute10,
msn.n_attribute2,
msn.n_attribute3,
msn.n_attribute4,
msn.n_attribute5,
msn.n_attribute6,
msn.n_attribute7,
msn.n_attribute8,
msn.n_attribute9,
msn.territory_code,
msn.time_since_mark,
msn.time_since_new,
msn.time_since_overhaul,
msn.time_since_repair,
msn.time_since_visit,
msn.serial_attribute_category
--Bug 8467875
from wsh_delivery_details dd,
wsh_delivery_assignments_v da,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
mtl_serial_numbers msn
where st.batch_id = p_batch_id
and st.stop_location_id = dl.initial_pickup_location_id
and dg.delivery_id = dl.delivery_id
and da.delivery_id = dl.delivery_id
and dd.delivery_detail_id = da.delivery_detail_id
-- bug 2787888 : removed oe_interfaced_flag and source_code comparison for OKE lines
and st.stop_id = dg.pick_up_stop_id
and dd.released_status = 'C'
and dd.container_flag='N'
and dd.serial_number = msn.serial_number
and dd.inventory_item_id = msn.inventory_item_id -- bug 3704188: part of mtl_serial_numbers_u1
and nvl(dl.shipment_direction , 'O') IN ('O', 'IO'); -- J Inbound Logistics jckwok
select dd.delivery_detail_id, msn.group_mark_id,
dd.shipped_quantity "quantity",
dd.serial_number, dd.serial_number "to_serial_number",
msn.attribute_category, -- Bug 3628620
msn.attribute1,
msn.attribute2,
msn.attribute3,
msn.attribute4,
msn.attribute5,
msn.attribute6,
msn.attribute7,
msn.attribute8,
msn.attribute9,
msn.attribute10,
msn.attribute11,
msn.attribute12,
msn.attribute13,
msn.attribute14,
msn.attribute15, -- End of Bug 3628620
dd.inventory_item_id, -- 3704188
--Bug 8467875
msn.cycles_since_mark,
msn.cycles_since_new,
msn.cycles_since_overhaul,
msn.cycles_since_repair,
msn.cycles_since_visit,
msn.c_attribute1,
msn.c_attribute10,
msn.c_attribute11,
msn.c_attribute12,
msn.c_attribute13,
msn.c_attribute14,
msn.c_attribute15,
msn.c_attribute16,
msn.c_attribute17,
msn.c_attribute18,
msn.c_attribute19,
msn.c_attribute2,
msn.c_attribute20,
msn.c_attribute3,
msn.c_attribute4,
msn.c_attribute5,
msn.c_attribute6,
msn.c_attribute7,
msn.c_attribute8,
msn.c_attribute9,
msn.d_attribute1,
msn.d_attribute10,
msn.d_attribute2,
msn.d_attribute3,
msn.d_attribute4,
msn.d_attribute5,
msn.d_attribute6,
msn.d_attribute7,
msn.d_attribute8,
msn.d_attribute9,
msn.number_of_repairs,
msn.n_attribute1,
msn.n_attribute10,
msn.n_attribute2,
msn.n_attribute3,
msn.n_attribute4,
msn.n_attribute5,
msn.n_attribute6,
msn.n_attribute7,
msn.n_attribute8,
msn.n_attribute9,
msn.territory_code,
msn.time_since_mark,
msn.time_since_new,
msn.time_since_overhaul,
msn.time_since_repair,
msn.time_since_visit,
msn.serial_attribute_category
--Bug 8467875
from wsh_delivery_details dd,
wsh_delivery_assignments_v da,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
mtl_serial_numbers msn
where st.batch_id = p_batch_id
and st.stop_location_id = dl.initial_pickup_location_id
and dg.delivery_id = dl.delivery_id
and da.delivery_id = dl.delivery_id
and dd.delivery_detail_id = da.delivery_detail_id
and st.stop_id = dg.pick_up_stop_id
and dd.released_status = 'C'
and dd.container_flag='N'
and NVL(dd.oe_interfaced_flag , 'N') = 'Y'
and dd.serial_number = msn.serial_number
and dd.inventory_item_id = msn.inventory_item_id -- bug 3704188: part of mtl_serial_numbers_u1
and nvl(dl.shipment_direction , 'O') IN ('O', 'IO');
select msn.attribute1,
msn.attribute2,
msn.attribute3,
msn.attribute4,
msn.attribute5,
msn.attribute6,
msn.attribute7,
msn.attribute8,
msn.attribute9,
msn.attribute10,
msn.attribute11,
msn.attribute12,
msn.attribute13,
msn.attribute14,
msn.attribute15
from mtl_serial_numbers msn
where msn.serial_number = x_serial_number
and msn.inventory_item_id = x_inventory_item_id -- bug 3704188: part of mtl_serial_numbers_u1
;
INSERT INTO wsh_serial_numbers
( delivery_detail_id,
quantity,
fm_serial_number,
to_serial_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
attribute_category, -- Bug 3628620
attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9,
attribute10, attribute11,attribute12,
attribute13, attribute14,attribute15, -- End of Bug 3628620
-- Bug 8467875
serial_attribute_category,c_attribute1,c_attribute2,
c_attribute3,c_attribute4,c_attribute5,
c_attribute6,c_attribute7,c_attribute8,
c_attribute9,c_attribute10,c_attribute11,
c_attribute12,c_attribute13,c_attribute14,
c_attribute15,c_attribute16,c_attribute17,
c_attribute18,c_attribute19,c_attribute20,
cycles_since_mark,cycles_since_new,cycles_since_overhaul,
cycles_since_repair,cycles_since_visit,number_of_repairs,
n_attribute1,n_attribute10,n_attribute2,
n_attribute3,n_attribute4,n_attribute5,
n_attribute6,n_attribute7,n_attribute8,
n_attribute9,time_since_mark,time_since_new,
time_since_overhaul,time_since_repair,time_since_visit,
d_attribute1,d_attribute10,d_attribute2,
d_attribute3,d_attribute4,d_attribute5,
d_attribute6,d_attribute7,d_attribute8,
d_attribute9,territory_code
-- Bug 8467875
)
VALUES
( l_delivery_detail_id(i),
l_quantity(i),
l_fm_serial_number(i),
l_to_serial_number(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
l_attribute_category(i), -- Bug 3628620
l_attribute1(i), l_attribute2(i), l_attribute3(i),
l_attribute4(i), l_attribute5(i), l_attribute6(i),
l_attribute7(i), l_attribute8(i), l_attribute9(i),
l_attribute10(i), l_attribute11(i), l_attribute12(i),
l_attribute13(i), l_attribute14(i), l_attribute15(i),
-- End of Bug 3628620
-- Bug 8467875
l_serial_attribute_category(i),l_c_attribute1(i),l_c_attribute2(i),
l_c_attribute3(i),l_c_attribute4(i),l_c_attribute5(i),
l_c_attribute6(i),l_c_attribute7(i),l_c_attribute8(i),
l_c_attribute9(i),l_c_attribute10(i),l_c_attribute11(i),
l_c_attribute12(i),l_c_attribute13(i),l_c_attribute14(i),
l_c_attribute15(i),l_c_attribute16(i),l_c_attribute17(i),
l_c_attribute18(i),l_c_attribute19(i),l_c_attribute20(i),
l_cycles_since_mark(i),l_cycles_since_new(i),l_cycles_since_overhaul(i),
l_cycles_since_repair(i),l_cycles_since_visit(i),l_number_of_repairs(i),
l_n_attribute1(i),l_n_attribute10(i),l_n_attribute2(i),
l_n_attribute3(i),l_n_attribute4(i),l_n_attribute5(i),
l_n_attribute6(i),l_n_attribute7(i),l_n_attribute8(i),
l_n_attribute9(i),l_time_since_mark(i),l_time_since_new(i),
l_time_since_overhaul(i),l_time_since_repair(i),l_time_since_visit(i),
l_d_attribute1(i),l_d_attribute10(i),l_d_attribute2(i),
l_d_attribute3(i),l_d_attribute4(i),l_d_attribute5(i),
l_d_attribute6(i),l_d_attribute7(i),l_d_attribute8(i),
l_d_attribute9(i),l_territory_code(i)
-- Bug 8467875
);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_transaction_temp_id(i)
AND fm_serial_number = l_fm_serial_number(i);
UPDATE wsh_delivery_details
SET serial_number = NULL,
--Added as part of bug 7645262
last_update_date = sysdate,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id,
transaction_temp_id = l_transaction_temp_id(i)
WHERE delivery_detail_id = l_delivery_detail_id(i);
-- bug 6625172: Deleting the msnt records for all the selected transaction_temp_id's for OM records
IF (ins_rows > 0) THEN
--{
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id IN
( SELECT DISTINCT dd.transaction_temp_id
from wsh_delivery_details dd,
wsh_delivery_assignments da,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
mtl_serial_numbers_temp msnt
where st.batch_id = p_batch_id
and st.stop_location_id = dl.initial_pickup_location_id
and dg.delivery_id = dl.delivery_id
and da.delivery_id = dl.delivery_id
and dd.delivery_detail_id = da.delivery_detail_id
and st.stop_id = dg.pick_up_stop_id
and dd.released_status = 'C'
and dd.container_flag='N'
and NVL(dd.oe_interfaced_flag , 'N') = 'Y'
and dd.transaction_temp_id = msnt.transaction_temp_id
and nvl(dl.shipment_direction , 'O') IN ('O', 'IO'));
-- bug 6625172: Deleting the msnt records for all the selected transaction_temp_id's for non-OM records
IF (ins_rows > 0) THEN
--{
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id IN
( SELECT DISTINCT dd.transaction_temp_id
from wsh_delivery_details dd,
wsh_delivery_assignments da,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
mtl_serial_numbers_temp msnt
where st.batch_id = p_batch_id
and st.stop_location_id = dl.initial_pickup_location_id
and dg.delivery_id = dl.delivery_id
and da.delivery_id = dl.delivery_id
and dd.delivery_detail_id = da.delivery_detail_id
and st.stop_id = dg.pick_up_stop_id
and dd.released_status = 'C'
and dd.container_flag='N'
and dd.transaction_temp_id = msnt.transaction_temp_id
and nvl(dl.shipment_direction , 'O') IN ('O', 'IO'));
WSH_DEBUG_SV.log(l_module_name,'Numbers of rows inserted into wsh_serial_numbers: '|| to_char(ins_rows) ||
', Number of rows deleted from mtl_serial_numbers_temp: '|| to_char(del_rows) ||
', Number of rows updated in wsh_delivery_details: '|| to_char(upd_rows) );
procedure Update_Completion_Status(p_num_stops IN NUMBER,
p_batch_id IN NUMBER,
x_master_status IN OUT NOCOPY VARCHAR2,
p_api_status IN OUT NOCOPY VARCHAR2,
x_normal_count IN OUT NOCOPY NUMBER,
x_warning_count IN OUT NOCOPY NUMBER,
x_interfaced_count IN OUT NOCOPY NUMBER
) IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_COMPLETION_STATUS';
SELECT count(*)
FROM wsh_trip_stops
WHERE batch_id = p_batch_id;
END Update_Completion_Status;
SELECT DISTINCT wts.stop_id
FROM wsh_trip_stops wts,
wsh_trip_stops wts2,
wsh_delivery_legs wdl,
wsh_delivery_legs wdl2,
wsh_delivery_assignments_v wda,
wsh_delivery_assignments_v wda2,
wsh_delivery_details wdd,
wsh_delivery_details wdd2
WHERE wts.batch_id = p_batch_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wda.delivery_id = wdl.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_code = 'OE'
AND nvl(wdd.oe_interfaced_flag,'N') <> 'Y'
AND wdd.released_status <> 'D'
AND wts2.batch_id = p_batch_id
AND wts2.stop_id = wdl2.pick_up_stop_id
AND wda2.delivery_id = wdl2.delivery_id
AND wda2.delivery_detail_id = wdd2.delivery_detail_id
AND wdd2.source_line_id = wdd.source_line_id
AND wdd2.source_code = 'OE'
AND nvl(wdd2.oe_interfaced_flag,'N') <> 'Y'
AND wdd2.released_status <> 'D'
AND wts2.stop_id <> wts.stop_id;
x_stop_tab.DELETE;
UPDATE wsh_trip_stops
SET batch_id = p_batch_id
WHERE stop_id = x_split_stops(x);
x_split_stops.DELETE;
UPDATE wsh_trip_stops
SET batch_id = NULL
WHERE stop_id = l_stop_tab(x)
AND batch_id = p_batch_id;
x_split_stops.DELETE(x_split_stops.LAST);
SELECT stop_id
FROM wsh_trip_stops
WHERE stop_id = p_stop_id
AND pending_interface_flag = p_flag
FOR UPDATE NOWAIT;
SELECT stop_id
FROM wsh_trip_stops
WHERE batch_id = p_batch_id
AND pending_interface_flag = p_flag
FOR UPDATE NOWAIT;
SELECT wts.stop_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd
WHERE wnd.delivery_id = p_delivery_id
AND wdl.delivery_id = wnd.delivery_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wts.stop_location_id = wnd.initial_pickup_location_id;
SELECT wts.stop_id
FROM wsh_trip_stops wts
WHERE wts.stop_id = p_trip_stop_id
AND wts.pending_interface_flag = 'Y'
AND nvl(wts.SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M');
SELECT wts.stop_id
FROM wsh_trip_stops wts
WHERE wts.pending_interface_flag = 'Y'
AND nvl(wts.SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M') -- J Inbound Logistics jckwok
ORDER BY DECODE(wts.lock_stop_id, wts.stop_id, 1, NULL, 2, 3);
SELECT DISTINCT wts.stop_id
FROM wsh_trip_stops wts,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl
WHERE wts.pending_interface_flag = 'Y'
AND wdl.pick_up_stop_id = wts.stop_id
AND wnd.initial_pickup_location_id = wts.stop_location_id
AND wdl.delivery_id = wnd.delivery_id
AND nvl(wts.SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M')
AND wnd.organization_id = p_organization_id
ORDER BY wts.stop_id;
SELECT wts.stop_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_picking_batches wpb
WHERE p_batch_id IS NOT NULL
AND wnd.batch_id = p_batch_id
AND wdl.delivery_id = wnd.delivery_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wts.stop_location_id = wnd.initial_pickup_location_id
AND wpb.batch_id = wnd.batch_id
AND (p_trip_type IS NULL OR
(p_trip_type = 'AC' AND wpb.creation_date <= wts.creation_date) OR
(p_trip_type = 'MC' AND wpb.creation_date > wts.creation_date))
AND nvl(wnd.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
ORDER BY DECODE(wts.lock_stop_id, wts.stop_id,1,
NULL,2,3);
SELECT 1
FROM wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
WHERE st.stop_id = dg.pick_up_stop_id AND
nvl(dl.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') AND -- J Inbound Logistics jckwok
st.stop_id = p_stop_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id AND
dl.asn_seq_number is not null
and rownum=1;
SELECT WSH_STOP_BATCH_S.NEXTVAL
FROM sys.dual;
SELECT wnd.delivery_id,
wnd.organization_id,
wnd.initial_pickup_location_id,
wnd.delivery_scpod_wf_process,
wnd.del_wf_interface_attr
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = p_stop_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wts.stop_location_id = wnd.initial_pickup_location_id;
l_dsno_stop_tab.DELETE;
l_stop_tab.DELETE;
UPDATE wsh_trip_stops
SET pending_interface_flag = 'P',
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
batch_id = l_stop_batch_id
WHERE stop_id = l_stop_id;
Update_Completion_Status(l_err_stops_count,
NULL,
l_completion_status,
l_api_completion_status_bkp,
l_stops_normal(1),
l_stops_warning(1),
l_stops_interfaced(1));
Update_Completion_Status(l_stop_count ,
NULL,
l_completion_status,
l_api_completion_status,
l_stops_normal(1),
l_stops_warning(1),
l_stops_interfaced(1));
l_inv_stops.DELETE;
l_stop_tab.DELETE;
l_err_stops.DELETE;
Update_Completion_Status(1,
NULL,
l_completion_status,
l_api_completion_status,
l_stops_normal(2),
l_stops_warning(2),
l_stops_interfaced(2));
UPDATE wsh_trip_stops
SET pending_interface_flag = 'Y',
batch_id = NULL
WHERE stop_id = l_err_stops(x);
l_err_stops.DELETE;
l_dsno_stop_tab.DELETE;
Update_Completion_Status(NULL,
l_stop_batch_id,
l_completion_status,
l_api_completion_status,
l_stops_normal(3),
l_stops_warning(3),
l_stops_interfaced(3));
l_dsno_stop_tab.DELETE;
l_inv_batch_table.DELETE;
UPDATE wsh_trip_stops
SET pending_interface_flag = l_new_flag,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate
WHERE batch_id = l_stop_batch_id;
WSH_UTIL_CORE.PrintMsg('Locking issue: batch '|| l_stop_batch_id || ' needs pending_interface_flag updated to ' || l_new_flag);
UPDATE wsh_trip_stops wts
SET wts.pending_interface_flag = 'Y',
wts.last_updated_by = fnd_global.user_id,
wts.last_update_date = sysdate
WHERE wts.pending_interface_flag = 'P'
AND EXISTS (SELECT 'request completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = wts.request_id
AND fcr.phase_code = 'C');
PROCEDURE Insert_inv_records(
p_start_index IN number,
p_end_index IN number,
p_mtl_txn_if_rec IN WSH_SHIP_CONFIRM_ACTIONS.Mtl_txn_if_rec_type,
p_mtl_ser_txn_if_rec IN OUT NOCOPY Mtl_ser_txn_if_rec_type,
p_mtl_lot_txn_if_rec IN OUT NOCOPY Mtl_lot_txn_if_rec_type,
p_def_inv_online IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Insert_inv_records';
SAVEPOINT sp_insert_inv_records;
WSH_TRX_HANDLER.INSERT_ROW_BULK (
p_start_index =>p_start_index,
p_end_index =>p_end_index,
p_mtl_txn_if_rec =>p_mtl_txn_if_rec,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'WSH_TRX_HANDLER.INSERT_ROW_BULK l_return_status',l_return_status);
WSH_TRXSN_HANDLER.INSERT_ROW_BULK
(p_mtl_ser_txn_if_rec => p_mtl_ser_txn_if_rec,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'WSH_TRXSN_HANDLER.INSERT_ROW_BULK l_return_status',l_return_status);
p_mtl_ser_txn_if_rec.source_code.delete;
p_mtl_ser_txn_if_rec.source_line_id.delete;
p_mtl_ser_txn_if_rec.fm_serial_number.delete;
p_mtl_ser_txn_if_rec.to_serial_number.delete;
p_mtl_ser_txn_if_rec.transaction_interface_id.delete;
WSH_TRXLOTS_HANDLER.insert_ROW_bulk
(p_mtl_lot_txn_if_rec => p_mtl_lot_txn_if_rec,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'WSH_TRXLOT_HANDLER.INSERT_ROW_BULK l_return_status',l_return_status);
p_mtl_lot_txn_if_rec.source_code.delete;
p_mtl_lot_txn_if_rec.source_line_id.delete;
p_mtl_lot_txn_if_rec.lot_number.delete;
p_mtl_lot_txn_if_rec.trx_quantity.delete;
p_mtl_lot_txn_if_rec.serial_transaction_temp_id.delete;
p_mtl_lot_txn_if_rec.transaction_interface_id.delete;
p_mtl_lot_txn_if_rec.grade_code.delete;
p_mtl_lot_txn_if_rec.secondary_trx_quantity.delete;
ROLLBACK TO SAVEPOINT sp_insert_inv_records;
ROLLBACK TO SAVEPOINT sp_insert_inv_records;
END Insert_inv_records;
SELECT source_document_type_id, source_document_id, source_document_line_id
FROM oe_order_lines_all
WHERE line_id = c_order_line_id;
SELECT destination_type_code,
destination_subinventory,
source_organization_id,
destination_organization_id,
deliver_to_location_id,
pl.requisition_line_id,
pd.distribution_id,
pl.unit_price,
nvl(pd.budget_account_id,-1) budget_account_id,
decode(nvl(pd.prevent_encumbrance_flag,'N'),'N',nvl(pd.encumbered_flag,'N'),'N') encumbered_flag
FROM po_requisition_lines_all pl,
po_req_distributions_all pd
WHERE pl.requisition_line_id = c_po_line_id
AND pl.requisition_header_id = c_source_document_id
AND pl.requisition_line_id = pd.requisition_line_id;
SELECT intransit_type
FROM mtl_interorg_parameters
WHERE from_organization_id = c_from_organization_id AND
to_organization_id = c_to_organization_id;
SELECT fm_serial_number,
to_serial_number,
rownum, --haperf
mtl_material_transactions_s.nextval seq_num,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
--Bug 8467875
cycles_since_mark,
cycles_since_new,
cycles_since_overhaul,
cycles_since_repair,
cycles_since_visit,
c_attribute1,
c_attribute10,
c_attribute11,
c_attribute12,
c_attribute13,
c_attribute14,
c_attribute15,
c_attribute16,
c_attribute17,
c_attribute18,
c_attribute19,
c_attribute2,
c_attribute20,
c_attribute3,
c_attribute4,
c_attribute5,
c_attribute6,
c_attribute7,
c_attribute8,
c_attribute9,
d_attribute1,
d_attribute10,
d_attribute2,
d_attribute3,
d_attribute4,
d_attribute5,
d_attribute6,
d_attribute7,
d_attribute8,
d_attribute9,
number_of_repairs,
n_attribute1,
n_attribute10,
n_attribute2,
n_attribute3,
n_attribute4,
n_attribute5,
n_attribute6,
n_attribute7,
n_attribute8,
n_attribute9,
territory_code,
time_since_mark,
time_since_new,
time_since_overhaul,
time_since_repair,
time_since_visit,
serial_attribute_category
--Bug 8467875
from wsh_serial_numbers
where delivery_detail_id = c_delivery_detail_id;
SELECT attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
c_attribute1,
c_attribute10,
c_attribute11,
c_attribute12,
c_attribute13,
c_attribute14,
c_attribute15,
c_attribute16,
c_attribute17,
c_attribute18,
c_attribute19,
c_attribute2,
c_attribute20,
c_attribute3,
c_attribute4,
c_attribute5,
c_attribute6,
c_attribute7,
c_attribute8,
c_attribute9,
d_attribute1,
d_attribute10,
d_attribute2,
d_attribute3,
d_attribute4,
d_attribute5,
d_attribute6,
d_attribute7,
d_attribute8,
d_attribute9,
n_attribute1,
n_attribute10,
n_attribute2,
n_attribute3,
n_attribute4,
n_attribute5,
n_attribute6,
n_attribute7,
n_attribute8,
n_attribute9,
lot_attribute_category
FROM mtl_lot_numbers
WHERE inventory_item_id = c_inv_item_id
AND organization_id = c_organization_id
AND lot_number = c_lot_name;
SELECT source_location_id
FROM wsh_locations
WHERE wsh_location_id = v_location_id;
SELECT source_code,
source_header_id,
source_line_id,
inventory_item_id,
subinventory,
trx_quantity,
trx_date,
organization_id,
trx_source_id,
trx_source_type_id,
trx_action_id,
trx_type_id,
distribution_account_id,
trx_reference,
trx_header_id,
trx_source_line_id,
trx_source_delivery_id,
revision,
locator_id,
picking_line_id,
transfer_subinventory,
transfer_organization,
ship_to_location_id,
requisition_line_id,
requisition_distribution_id,
trx_uom,
mtl_material_transactions_s.nextval trx_interface_id,
shipment_number,
expected_arrival_date,
encumbrance_account,
encumbrance_amount,
movement_id,
freight_code,
waybill_airbill,
content_lpn_id,
requested_quantity,
inv_interfaced_flag,
ship_method_code,
cycle_count_quantity,
src_requested_quantity_uom,
transaction_temp_id,
lot_number,
serial_number,
to_serial_number,
trip_id,
-- HW OPMCONV. No need for sublot anymore
-- sublot_number,
ship_tolerance_above,
ship_tolerance_below,
src_requested_quantity,
org_id,
trx_quantity2,
error_flag,
-- HW OPMCONV. Retrieve grade and uom2
preferred_grade,
requested_quantity_uom2,
--added for BUG 4538005
ship_from_location_id,
ship_to_site_use_id
--
FROM (SELECT dd.source_code,
dd.source_header_id,
dd.source_line_id,
dd.inventory_item_id,
dd.subinventory,
dd.shipped_quantity trx_quantity,
st.actual_departure_date trx_date,
dd.organization_id,
NULL trx_source_id,
NULL trx_source_type_id,
NULL trx_action_id,
NULL trx_type_id,
NULL distribution_account_id,
dd.source_header_id trx_reference,
NULL trx_header_id,
dd.source_line_id trx_source_line_id,
dl.delivery_id trx_source_delivery_id,
dd.revision,
dd.locator_id,
dd.delivery_detail_id picking_line_id,
NULL transfer_subinventory,
NULL transfer_organization,
dd.ship_to_location_id ship_to_location_id,
NULL requisition_line_id,
NULL requisition_distribution_id,
dd.requested_quantity_uom trx_uom,
--haperf NULL trx_interface_id,
dl.name shipment_number,
dl.ultimate_dropoff_date expected_arrival_date,
NULL encumbrance_account,
NULL encumbrance_amount,
dd.movement_id,
wcv.freight_code freight_code,
dl.waybill waybill_airbill,
dd1.lpn_id content_lpn_id,
---
dd.requested_quantity,
dd.inv_interfaced_flag,
tr.ship_method_code,
dd.cycle_count_quantity,
dd.src_requested_quantity_uom,
dd.transaction_temp_id,
dd.lot_number,
dd.serial_number,
dd.to_serial_number,
st.trip_id,
-- HW OPMCONV. No need for sublot anymore
-- dd.sublot_number,
dd.ship_tolerance_above,
dd.ship_tolerance_below,
dd.src_requested_quantity,
dd.org_id,
-- HW OPM 3064890 added trx_quantity2
dd.shipped_quantity2 trx_quantity2,
'N' error_flag,
-- HW OPMCONV. Retrieve grade and uom2
dd.preferred_grade,
dd.requested_quantity_uom2,
--added for BUG 4538005
dd.ship_from_location_id ship_from_location_id,
dd.ship_to_site_use_id ship_to_site_use_id
--
FROM wsh_delivery_details dd,
wsh_delivery_assignments_v da,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
wsh_trips tr,
wsh_carriers wcv,
wsh_delivery_details dd1,
wsh_delivery_assignments_v da1
WHERE st.stop_id = dg.pick_up_stop_id
AND st.batch_id = p_batch_id
AND st.stop_location_id = dl.initial_pickup_location_id
AND dg.delivery_id = dl.delivery_id
AND da.delivery_id = dl.delivery_id
AND dd.delivery_detail_id = da.delivery_detail_id
AND st.trip_id = tr.trip_id
AND dd.container_flag = 'N'
AND dd.source_code <> 'RTV' -- RTV changes
AND dd.inv_interfaced_flag = 'N'
AND dd.released_status <> 'D'
AND nvl(dd.line_direction,'O') in ('O','IO')
AND tr.carrier_id = wcv.carrier_id (+)
AND dd.delivery_detail_id=da1.delivery_detail_id
AND da1.parent_delivery_detail_id = dd1.delivery_detail_id(+)
ORDER BY dd.organization_id,
dd.source_header_id,
dd.ship_to_location_id);
SELECT mrp_atp_schedule_temp_s.nextVal
FROM dual;
SELECT mtl_material_transactions_s.nextval
FROM sys.dual;
SELECT wcv.freight_code freight_code
FROM wsh_carrier_services wcs,
wsh_org_carrier_services wocs,
wsh_carriers_v wcv
WHERE wcs.carrier_service_id = wocs.carrier_service_id AND
wcs.carrier_id = wcv.carrier_id AND
wcs.ship_method_code = p_ship_method_code AND
wocs.organization_id = p_organization_id;
SELECT wc.freight_code
FROM wsh_new_deliveries wnd,
wsh_carriers wc
WHERE wnd.delivery_id = p_delivery_id AND
wc.carrier_id = wnd.carrier_id;
l_insert_inv_calls number:=0;
l_insert_inv_not_success number:=0;
WSH_DEBUG_SV.logmsg(l_module_name, 'INSERTED '||l_serial_count||' SERIAL RECORDS ');
'INSERTING SRL NUMBER ' || l_mtl_txn_if_rec.SERIAL_NUMBER(l_index) || 'INTO MSNI.');
l_insert_inv_calls := l_insert_inv_calls + 1;
Insert_inv_records(
p_start_index => l_start_index,
p_end_index => l_index,
p_mtl_txn_if_rec => l_mtl_txn_if_rec,
p_mtl_ser_txn_if_rec => l_mtl_ser_txn_if_rec,
p_mtl_lot_txn_if_rec => l_mtl_lot_txn_if_rec,
p_def_inv_online => l_def_inv_online,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Insert_inv_records l_return_status',l_return_status);
l_insert_inv_not_success := l_insert_inv_not_success + 1;
WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_calls',l_insert_inv_calls);
WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_not_success',l_insert_inv_not_success);
l_insert_inv_calls := l_insert_inv_calls + 1;
Insert_inv_records(
p_start_index => l_start_index,
p_end_index => l_index,
p_mtl_txn_if_rec => l_mtl_txn_if_rec,
p_mtl_ser_txn_if_rec => l_mtl_ser_txn_if_rec,
p_mtl_lot_txn_if_rec => l_mtl_lot_txn_if_rec,
p_def_inv_online => l_def_inv_online,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Insert_inv_records l_return_status',l_return_status);
l_insert_inv_not_success := l_insert_inv_not_success + 1;
WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_calls',l_insert_inv_calls);
WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_not_success',l_insert_inv_not_success);
l_insert_inv_calls := l_insert_inv_calls + 1;
Insert_inv_records(
p_start_index => l_start_index,
p_end_index => l_index,
p_mtl_txn_if_rec => l_mtl_txn_if_rec,
p_mtl_ser_txn_if_rec => l_mtl_ser_txn_if_rec,
p_mtl_lot_txn_if_rec => l_mtl_lot_txn_if_rec,
p_def_inv_online => l_def_inv_online,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name,'Insert_inv_records l_return_status',l_return_status);
l_insert_inv_not_success := l_insert_inv_not_success + 1;
WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_calls',l_insert_inv_calls);
WSH_DEBUG_SV.log(l_module_name,'l_insert_inv_not_success',l_insert_inv_not_success);
UPDATE wsh_delivery_details
SET inv_interfaced_flag = c_inv_int_full ,
--Added as part of bug 7645262
last_update_date = sysdate,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id
WHERE delivery_detail_id = l_delivery_detail_tbl(i)
AND container_flag = 'N';
IF (l_insert_inv_not_success >= l_insert_inv_calls) THEN
raise fnd_api.g_exc_error;
ELSIF (l_insert_inv_not_success > 0 and l_insert_inv_not_success < l_insert_inv_calls) THEN
RAISE wsh_util_core.g_exc_warning;
SELECT stop_id
FROM wsh_trip_stops
WHERE batch_id = p_batch_id
FOR UPDATE NOWAIT;
SELECT da.delivery_detail_id
FROM wsh_delivery_assignments da , wsh_delivery_legs dg, wsh_new_deliveries dl, wsh_trip_stops st
where dl.delivery_id = da.delivery_id
AND da.delivery_id IS NOT NULL
AND st.stop_id = dg.pick_up_stop_id
AND st.batch_id = p_batch_id
AND st.stop_location_id = dl.initial_pickup_location_id
AND dg.delivery_id = dl.delivery_id;
SELECT mti.picking_line_id
FROM mtl_transactions_interface mti,
wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_delivery_details wdd,
wsh_trip_stops st
WHERE mti.picking_line_id = da.delivery_detail_id
AND wdd.delivery_detail_id = da.delivery_detail_id
AND wdd.source_code <> 'RTV' -- RTV changes
AND dl.delivery_id = da.delivery_id
AND st.stop_id = dg.pick_up_stop_id
AND st.batch_id = p_batch_id
AND st.stop_location_id = dl.initial_pickup_location_id
AND dg.delivery_id = dl.delivery_id
AND nvl(mti.lock_flag,2) = 2;
SELECT DISTINCT wnd.delivery_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts
where wdd.source_code = 'RTV'
and nvl(wdd.inv_interfaced_flag, 'N') = 'N'
AND wdd.container_flag = 'N'
AND wdd.released_status <> 'D'
AND nvl(wdd.line_direction,'O') in ('O','IO')
and wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and wda.delivery_id is not null
and wnd.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and wdd.ship_from_location_id = wts.stop_location_id
and wts.batch_id = p_batch_id;
Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
update wsh_delivery_details dd
set inv_interfaced_flag = 'N' ,
--Added as part of bug 7645262
last_update_date = sysdate,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id
where inv_interfaced_flag = 'P'
and not exists (
select picking_line_id
from mtl_transactions_interface mti
where source_line_id = mti.trx_source_line_id
and mti.picking_line_id = dd.delivery_detail_id)
and delivery_detail_id = l_detail_ids_tbl(i)
and container_flag = 'N'
and released_status <> 'D'; /* H integration: wrudge */
SELECT mtl_material_transactions_s.nextval
INTO l_transaction_header_id
FROM sys.dual;
update mtl_transactions_interface
set transaction_header_id = l_transaction_header_id
where picking_line_id = l_picking_line_id_tbl(i)
and nvl(process_flag,1) <> 3
and nvl(lock_flag,2) = 2;
WSH_DEBUG_SV.log(l_module_name,'No. existing record updated in MTI', l_count);
SELECT count(*) total
from wsh_delivery_details dd,
wsh_delivery_assignments_v da,
wsh_new_deliveries ds
where dd.delivery_detail_id = da.delivery_detail_id
and da.delivery_id = ds.delivery_id
and ds.status_code NOT IN ('CL','IT','CO', 'SR', 'SC') /* Closed, In Transit, Confirmed */
and da.delivery_id <> p_delivery_id
and da.delivery_id IS NOT NULL
and dd.source_line_id = p_source_line_id
and dd.source_code = p_source_code
and dd.container_flag = 'N'
and dd.released_status <> 'D' ; /* H integration: wrudge */
SELECT count(*) total
from wsh_delivery_details dd,
wsh_delivery_assignments_v da
where dd.delivery_detail_id = da.delivery_detail_id
and da.delivery_id is NULL
and dd.source_line_id = p_source_line_id
and dd.source_code = p_source_code
and dd.container_flag = 'N'
and dd.released_status <> 'D' ; /* H integration: wrudge */
select sum(decode(p_fulfill_base,'S',nvl(wdd.picked_quantity2, wdd.requested_quantity2),nvl(wdd.picked_quantity, wdd.requested_quantity)))
from wsh_delivery_details wdd,
wsh_delivery_assignments_v da,
wsh_new_deliveries wnd
where wdd.source_line_id=p_source_line_id and
wdd.source_code = 'OE' and
wdd.delivery_detail_id=da.delivery_detail_id and
not exists (select 1 from wsh_delivery_legs lg, wsh_trip_stops st
where st.batch_id= p_batch_id and
st.stop_id = lg.pick_up_stop_id and
nvl(da.delivery_id,0)= lg.delivery_id)
and wnd.delivery_id(+) = da.delivery_id
and ( (wdd.released_status = 'Y')
OR (wdd.released_status = 'C' AND wdd.oe_interfaced_flag <> 'Y')
OR (wnd.status_code IN ('SR', 'SC'))
);
Select order_quantity_uom,
ordered_quantity_uom2
from oe_order_Lines_all
where line_id = c_source_line_id ;
l_delete_detail_id NUMBER;
SELECT delivery_detail_id
FROM wsh_delivery_details dd
WHERE source_line_id = c_source_line_id
AND source_code = 'OE'
AND released_status IN ('R', 'B','N','S','X')
AND NVL(container_flag, 'N') = 'N';
SELECT DISTINCT wdd.source_line_id
FROM wsh_delivery_details wdd
WHERE wdd.source_header_id = c_source_header_id
AND wdd.source_code = 'OE'
AND wdd.source_line_set_id = c_source_line_set_id
AND NOT EXISTS
( SELECT 'x'
FROM wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd ,
wsh_delivery_legs wdl ,
wsh_trip_stops wts
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wda.delivery_id IS NOT NULL
AND wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wdd.ship_from_location_id = wts.stop_location_id
AND wts.batch_id = p_batch_id);
SELECT 'x'
FROM wsh_delivery_details wdd ,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
WHERE wdd.source_line_id = c_source_line_id
AND wdd.source_code = 'OE'
AND wdd.source_header_id = c_source_header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id(+) = wda.delivery_id
AND ( ( wdd.released_status = 'Y' ) OR
( wdd.released_status = 'C' AND wdd.oe_interfaced_flag <> 'Y' ) OR
( wnd.status_code IN ('SR','SC')));
SELECT SUM(dd.requested_quantity),
SUM( NVL(dd.shipped_quantity, 0 )),
-- muom
SUM(dd.requested_quantity2),
SUM( NVL(dd.shipped_quantity2, 0 )),
max(nvl(dd.client_id,0)) -- LSP PROJECT : needs to check clientId on WDD records.
INTO l_tot_dd_req_qty,
l_tot_dd_shp_qty,
-- muom
l_tot_dd_req_qty2,
l_tot_dd_shp_qty2,
l_client_id -- LSP PROJECT
FROM wsh_delivery_Details dd ,
wsh_delivery_assignments_v da ,
wsh_delivery_legs dg ,
wsh_new_deliveries dl ,
wsh_trip_stops st
WHERE st.stop_id = dg.pick_up_stop_id
AND st.batch_id = P_batch_id
AND st.stop_location_id = dl.initial_pickup_location_id
AND dg.delivery_id = dl.delivery_id
AND dl.delivery_id = da.delivery_id
AND da.delivery_id IS NOT NULL
AND da.delivery_detail_id = dd.delivery_detail_id
AND NVL ( dd.oe_interfaced_flag , 'N' ) <> 'Y'
AND dd.source_code = 'OE'
AND dd.source_header_id = p_oe_interface_rec.source_header_id
AND dd.source_line_set_id = p_oe_interface_rec.source_line_set_id
AND dd.released_status <> 'D';
SELECT SUM(WSH_WV_UTILS.CONVERT_UOM(ol.order_quantity_uom, p_oe_interface_rec.requested_quantity_uom, ol.ordered_quantity, ol.inventory_item_id)) order_line_quantity,
-- muom
SUM(ol.ordered_quantity2) order_line_quantity2
INTO l_tot_ord_qty, l_tot_ord_qty2
FROM oe_order_lines_all ol
WHERE ol.header_id = p_oe_interface_rec.source_header_id
AND ol.line_set_id = p_oe_interface_rec.source_line_set_id;
SELECT DISTINCT client_id
INTO l_client_id
FROM wsh_delivery_details
WHERE source_code = 'OE'
AND source_line_id = p_oe_interface_rec.source_line_id;
INTO l_delete_detail_id;
l_remain_details_id(l_remain_detail_index) := l_delete_detail_id;
WSH_DEBUG_SV.logmsg(l_module_name, 'l_remain_detail_index = '||l_remain_detail_index||' detail_id = '||l_delete_detail_id);
INTO l_delete_detail_id;
l_remain_details_id(l_remain_detail_index) := l_delete_detail_id;
WSH_DEBUG_SV.logmsg(l_module_name, 'l_remain_detail_index = '||l_remain_detail_index||' detail_id = '||l_delete_detail_id);
SELECT count(*) into l_count
from wsh_delivery_details
where top_model_line_id is not null
and source_line_id = p_source_line_id
and source_code = p_source_code
and container_flag = 'N'
and released_status <> 'D' ; /* H integration: wrudge */
SELECT distinct top_model_line_id into l_top_id
from wsh_delivery_details
where source_line_id = p_source_line_id
and source_code = p_source_code
and container_flag = 'N'
and released_status <> 'D' ; /* H integration: wrudge */
procedure update_interfaced_details ( p_batch_id number , x_return_status out NOCOPY varchar2 ) is
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_INTERFACED_DETAILS';
update wsh_delivery_details dd
set inv_interfaced_flag = c_inv_int_full ,
--Added as part of bug 7645262
last_update_date = sysdate,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id
where (exists (
SELECT mmt.picking_line_id
FROM mtl_material_transactions mmt
WHERE mmt.picking_line_id = dd.delivery_detail_id
and transaction_source_type_id in ( 2,8,13,16 )
and trx_source_line_id = dd.source_line_id
)
)
and container_flag = 'N'
and nvl(inv_interfaced_flag , 'N') <> c_inv_int_full
and nvl(inv_interfaced_flag , 'N') <> 'X'
and dd.delivery_Detail_id in (
SELECT da.delivery_detail_id
FROM wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
where dl.delivery_id = da.delivery_id AND
da.delivery_id IS NOT NULL AND
st.stop_id = dg.pick_up_stop_id AND
st.batch_id = p_batch_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id AND
nvl(dl.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
)
and dd.source_code <> 'RTV' -- RTV changes
and dd.released_status <> 'D'; /* H integration: wrudge */
WSH_DEBUG_SV.logmsg(l_module_name, 'NUMBER OF ROWS UPDATED AS INTERFACED TO INVENTORY = ' || SQL%ROWCOUNT );
WSH_UTIL_CORE.PrintMsg('UPDATE_INTERFACED_DETAILS : unexpected error ');
end update_interfaced_details ;
SELECT batch_id
FROM wsh_trip_stops
WHERE batch_id = p_batch_id
FOR UPDATE NOWAIT;
SELECT DISTINCT stop_id
FROM wsh_delivery_Details dd,
wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
WHERE st.stop_id = dg.pick_up_stop_id
AND st.batch_id = p_batch_id
AND st.stop_location_id = dl.initial_pickup_location_id
AND dg.delivery_id = dl.delivery_id
AND dl.delivery_id = da.delivery_id
AND da.delivery_id IS NOT NULL
AND da.delivery_detail_id = dd.delivery_detail_id
AND dd.oe_interfaced_flag = 'N'
AND dd.source_code = 'OE'
AND dd.released_status <> 'D'
AND nvl(dd.line_direction,'O') IN ('O','IO');
UPDATE wsh_trip_stops
SET batch_id = NULL,
pending_interface_flag = 'Y'
WHERE stop_id = l_err_stops(i);
SELECT stop_id
FROM wsh_trip_stops
WHERE batch_id = p_batch_id
FOR UPDATE NOWAIT;
SELECT delivery_detail_id
FROM wsh_delivery_details wdd
WHERE wdd.top_model_line_id = p_top_model_line_id
AND wdd.source_header_id = p_source_header_id -- bug 3642085
AND wdd.source_code = 'OE' -- bug 3642085
MINUS
SELECT wdd.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl,
wsh_trip_stops wts
where wts.batch_id = p_batch_id
and wdl.pick_up_stop_id = wts.stop_id
and wda.delivery_id = wdl.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.container_flag = 'N'
and wdd.source_code = 'OE'
AND wdd.source_line_set_id IS NULL
AND wdd.source_header_id = p_source_header_id -- frontport of bug 4324971
and wdd.top_model_line_id = p_top_model_line_id;
Select delivery_detail_id
from wsh_delivery_details wdd
WHERE wdd.ship_set_id = p_ship_set_id
AND wdd.source_header_id = p_source_header_id -- bug 3642085
AND wdd.source_code = 'OE' -- bug 3642085
MINUS
select wdd.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl,
wsh_trip_stops wts
where wts.batch_id = p_batch_id
and wdl.pick_up_stop_id = wts.stop_id
and wda.delivery_id = wdl.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.container_flag = 'N'
and wdd.source_code = 'OE'
AND wdd.source_line_set_id IS NULL
and wdd.ship_set_id = p_ship_set_id;
SELECT delivery_detail_id
from wsh_delivery_details wdd
WHERE wdd.source_line_id = p_source_line_id
AND wdd.source_code = 'OE' -- bug 3642085
MINUS
SELECT wdd.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl,
wsh_trip_stops wts
where wts.batch_id = p_batch_id
and wdl.pick_up_stop_id = wts.stop_id
and wda.delivery_id = wdl.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.container_flag = 'N'
and wdd.source_code = 'OE'
AND wdd.source_line_set_id IS NULL
and wdd.source_line_id = p_source_line_id;
SELECT stop_id
FROM wsh_trip_stops
WHERE batch_id = p_batch_id
ORDER BY stop_id ;
SELECT wdd.source_line_id
FROM wsh_delivery_details wdd,
wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_delivery_assignments_v wda
WHERE wts.stop_id = p_stop_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wdl.delivery_id = wda.delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO')
AND wdd.released_status = 'C'
AND wdd.container_flag='N'
AND wdd.oe_interfaced_flag <> 'Y'
AND wdd.source_code = 'OE'
ORDER BY wdd.source_line_id;
l_lines_tab.DELETE;
l_freight_costs_all.DELETE;
UPDATE wsh_delivery_details dd
SET oe_interfaced_flag = l_oe_interfaced_flag ,
--Added as part of bug 7645262
last_update_date = sysdate,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id
WHERE source_line_id = p_bulk_ship_line.line_id(i)
and container_flag = l_container_flag
and source_code = l_source_code
and released_status <> l_released_status
and dd.delivery_detail_id in (
SELECT /*+ no_unnest */ da.delivery_detail_id
FROM wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
where da.delivery_detail_id = dd.delivery_detail_id AND
dl.delivery_id = da.delivery_id AND
da.delivery_id IS NOT NULL AND
st.stop_id = dg.pick_up_stop_id AND
st.batch_id = p_batch_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id
);
WSH_DEBUG_SV.logmsg(l_module_name,'After Forall Update');
UPDATE wsh_delivery_details dd
set oe_interfaced_flag = l_oe_interfaced_flag ,
--Added as part of bug 7645262
last_update_date = sysdate,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id
where delivery_detail_id in (
SELECT da.delivery_detail_id
FROM wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
oe_order_lines_all ol
where da.delivery_detail_id = dd.delivery_detail_id AND
dl.delivery_id = da.delivery_id AND
da.delivery_id IS NOT NULL AND
st.stop_id = dg.pick_up_stop_id AND
st.batch_id = p_batch_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id AND
ol.line_id = dd.source_line_id AND
ol.shipped_quantity > 0
)
and dd.source_header_id = l_header_id
and container_flag = l_container_flag
and source_code = l_source_code
and released_status <> l_released_status;
UPDATE wsh_delivery_details dd
SET oe_interfaced_flag = l_oe_interfaced_flag ,
--Added as part of bug 7645262
last_update_date = sysdate,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id
WHERE source_line_id = p_bulk_ship_line.line_id(i)
and container_flag = l_container_flag
and source_code = l_source_code
and released_status <> l_released_status
and dd.delivery_detail_id in (
SELECT /*+ no_unnest */ da.delivery_detail_id
FROM wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
where da.delivery_detail_id = dd.delivery_detail_id AND
dl.delivery_id = da.delivery_id AND
da.delivery_id IS NOT NULL AND
st.stop_id = dg.pick_up_stop_id AND
st.batch_id = p_batch_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id
);
WSH_DEBUG_SV.log(l_module_name,'No. Success Rec. Update to Y', l_count);
UPDATE wsh_delivery_details dd
SET oe_interfaced_flag = Decode(p_bulk_ship_line.error_flag(i),'Y','N','Y') ,
--Added as part of bug 7645262
last_update_date = sysdate,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id
WHERE source_line_id = p_bulk_ship_line.line_id(i)
and container_flag = l_container_flag
and source_code = l_source_code
and released_status <> l_released_status
and dd.delivery_detail_id in (
SELECT /*+ no_unnest */ da.delivery_detail_id
FROM wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
where da.delivery_detail_id = dd.delivery_detail_id AND
dl.delivery_id = da.delivery_id AND
da.delivery_id IS NOT NULL AND
st.stop_id = dg.pick_up_stop_id AND
st.batch_id = p_batch_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id
);
WSH_DEBUG_SV.log(l_module_name,'No. Warning Rec. Update',l_count);
l_ship_adj_line.cost_id.DELETE;
l_ship_adj_line.automatic_flag.DELETE;
l_ship_adj_line.list_line_type_code.DELETE;
l_ship_adj_line.charge_type_code.DELETE;
l_ship_adj_line.header_id.DELETE;
l_ship_adj_line.line_id.DELETE;
l_ship_adj_line.adjusted_amount.DELETE;
l_ship_adj_line.arithmetic_operator.DELETE;
l_ship_adj_line.operation.DELETE;
l_ship_adj_line.cost_id.DELETE;
l_ship_adj_line.automatic_flag.DELETE;
l_ship_adj_line.list_line_type_code.DELETE;
l_ship_adj_line.charge_type_code.DELETE;
l_ship_adj_line.header_id.DELETE;
l_ship_adj_line.line_id.DELETE;
l_ship_adj_line.adjusted_amount.DELETE;
l_ship_adj_line.arithmetic_operator.DELETE;
l_ship_adj_line.operation.DELETE;
SELECT dd.source_line_id
FROM wsh_delivery_Details dd,
wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
WHERE st.stop_id = dg.pick_up_stop_id AND
st.batch_id = p_batch_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id AND
dl.delivery_id = da.delivery_id AND
da.delivery_id IS NOT NULL AND
da.delivery_detail_id = dd.delivery_detail_id
and nvl ( dd.oe_interfaced_flag , 'N' ) <> 'Y'
and dd.source_code = 'OE'
and dd.source_header_id = c_source_header_id
and dd.source_line_id = c_source_line_id
and dd.released_status <> 'D' /* H integration: wrudge */
for update nowait;
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd
WHERE wdd.source_code = 'OE'
AND wdd.source_header_id = c_source_header_id
AND wdd.source_line_set_id = c_source_line_set_id
AND wdd.released_status <> 'D'
AND EXISTS (
SELECT 'X'
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wts.batch_id = c_batch_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wdl.delivery_id = wda.delivery_id
)
FOR UPDATE NOWAIT;
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd
WHERE wdd.source_code = 'OE'
AND wdd.source_header_id = c_source_header_id
AND wdd.source_line_id = c_source_line_id
AND wdd.released_status <> 'D'
AND EXISTS (
SELECT 'X'
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wts.batch_id = c_batch_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wdl.delivery_id = wda.delivery_id
)
FOR UPDATE NOWAIT;
SELECT delivery_detail_id
FROM wsh_delivery_details dd
WHERE source_line_id = c_source_line_id AND
source_code = 'OE' AND
released_status IN ('R', 'B', 'N', 'S', 'X') AND
NVL(container_flag, 'N') = 'N';
SELECT DISTINCT wdd.source_line_id
from wsh_delivery_details wdd
where wdd.source_header_id = c_source_header_id
and wdd.source_code = 'OE'
and wdd.source_line_set_id = c_source_line_set_id
and not exists (
select 'x'
from wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts
where wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and wda.delivery_id is not null
and wnd.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and wdd.ship_from_location_id = wts.stop_location_id
and wts.batch_id = p_batch_id);
select 'x'
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
where wdd.source_line_id = c_source_line_id
and wdd.source_code = 'OE'
and wdd.source_header_id = c_source_header_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wnd.delivery_id(+) = wda.delivery_id
and ( (wdd.released_status = 'Y') OR
(wdd.released_status = 'C' AND wdd.oe_interfaced_flag <> 'Y') OR
(wnd.status_code IN ('SR', 'SC'))
);
l_delete_detail_id NUMBER;
select 'X'
from wsh_delivery_details
where source_line_id in (select source_line_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl,
wsh_trip_stops wts
where wts.batch_id = cp_batch_id
and wdl.pick_up_stop_id = wts.stop_id
and wda.delivery_id = wdl.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.container_flag = 'N'
and wdd.source_code = 'OE'
)
and (delivery_detail_id not in
(select wdd.delivery_detail_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl,
wsh_trip_stops wts
where wts.batch_id = cp_batch_id
and wdl.pick_up_stop_id = wts.stop_id
and wda.delivery_id = wdl.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.container_flag = 'N'
and wdd.source_code = 'OE'
)
or top_model_line_id is not null
or ship_set_id is not null
or nvl(ship_model_complete_flag,'N') = 'Y'
or requested_quantity <> nvl(shipped_quantity,-99)
or source_line_set_id IS NOT NULL
)
and source_code = 'OE'
and container_flag = 'N';
select 'X'
from wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_delivery_assignments wda ,
wsh_delivery_details wdd
where wts.batch_id = cp_batch_id
and wdl.pick_up_stop_id = wts.stop_id
and wda.delivery_id = wdl.delivery_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.container_flag = 'N'
and wdd.source_code = 'OE'
and (EXISTS (select 'any non-cancelled line outside batch'
from wsh_delivery_details wdd2
where wdd2.source_line_id = wdd.source_line_id
and wdd2.source_code = 'OE'
and wdd2.container_flag = 'N'
and wdd2.released_status <> 'D'
and wdd2.delivery_detail_id NOT IN
(select wda3.delivery_detail_id
from wsh_delivery_assignments wda3 ,
wsh_delivery_legs wdl3,
wsh_trip_stops wts3,
wsh_delivery_details wdd3
where wts3.batch_id = cp_batch_id
and wdl3.pick_up_stop_id = wts3.stop_id
and wda3.delivery_id = wdl3.delivery_id
and wdd3.delivery_detail_id = wda3.delivery_detail_id
and wdd3.source_line_id = wdd.source_line_id
and wdd3.source_code = 'OE'
and wdd3.container_flag = 'N'))
or wdd.top_model_line_id is not null
or wdd.ship_set_id is not null
or nvl(wdd.ship_model_complete_flag,'N') = 'Y'
or wdd.requested_quantity <> nvl(wdd.shipped_quantity,-99)
-- bug 5688051
or nvl(requested_quantity2, -99) <> nvl(shipped_quantity2, -99)
or wdd.source_line_set_id IS NOT NULL )
and rownum = 1;
SELECT dd.source_header_id header_id,
dd.source_line_id line_id,
dd.top_model_line_id top_model_line_id,
dd.ship_set_id ship_set_id,
dd.arrival_set_id arrival_set_id,
NVL(dl.initial_pickup_date,sysdate) actual_shipment_date,
dd.requested_quantity_uom shipping_quantity_uom,
dd.requested_quantity_uom2 shipping_quantity_uom2,
ol.flow_status_code flow_status_code,
ol.ordered_quantity ordered_quantity,
ol.ordered_quantity2 ordered_quantity2,
ol.org_id org_id,
sum( nvl(dd.shipped_quantity,0) ) shipping_quantity,
sum( nvl(dd.shipped_quantity2, 0 )) shipping_quantity2,
-- muom
-- Derive shipped_quantity (to pass to OM) only when Fufillment Base is Secondary
sum( decode(dd.requested_quantity_uom2, null, null,
decode(WSH_UTIL_CORE.Get_Line_Fulfillment_Base(dd.source_code, dd.source_line_id),
'S', WSH_WV_UTILS.CONVERT_UOM(from_uom => dd.requested_quantity_uom2,
to_uom => ol.order_quantity_uom,
quantity => nvl(dd.shipped_quantity2,0),
item_id => dd.inventory_item_id)))) shipped_quantity
FROM wsh_delivery_Details dd,
wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
oe_order_lines_all ol
WHERE st.stop_id = dg.pick_up_stop_id AND
st.batch_id = cp_batch_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id AND
dl.delivery_id = da.delivery_id AND
da.delivery_detail_id = dd.delivery_detail_id
and nvl ( dd.oe_interfaced_flag , 'N' ) <> 'Y'
and dd.source_code = 'OE'
and dd.released_status <> 'D'
and ol.line_id = dd.source_line_id
and dd.client_id IS NULL -- LSP PROJECT : Should not perform OM interface for LSP orders
GROUP BY
dd.source_header_id ,
dd.source_line_id,
dd.top_model_line_id,
dd.ship_set_id,
dd.arrival_set_id,
dl.initial_pickup_date,
dd.requested_quantity_uom,
dd.requested_quantity_uom2,
ol.flow_status_code,
ol.ordered_quantity,
ol.ordered_quantity2,
ol.order_quantity_uom, -- muom
ol.org_id
ORDER BY --Bugfix 14127623
dd.source_header_id,
dd.source_line_id;
SELECT dd.source_header_id ,
dd.source_header_number ,
dd.source_line_set_id,
dd.source_line_id ,
WSH_WV_UTILS.CONVERT_UOM(ol.order_quantity_uom,
dd.requested_quantity_uom,
ol.ordered_quantity,
dd.inventory_item_id) order_line_quantity,
-- muom
ol.ordered_quantity2 order_line_quantity2,
dd.requested_quantity_uom ,
dd.requested_quantity_uom2 ,
ol.ordered_quantity,
ol.order_quantity_uom,
ol.ordered_quantity2,
ol.ordered_quantity_uom2,
ol.model_remnant_flag,
ol.item_type_code,
ol.calculate_price_flag,
dd.ship_tolerance_below ,
dd.ship_tolerance_above,
ol.org_id org_id ,
dd.organization_id organization_id ,
NVL(dd.oe_interfaced_flag, 'N') oe_interfaced_flag,
dl.initial_pickup_date,
dd.top_model_line_id ,
dd.ato_line_id,
dd.ship_set_id,
dd.ship_model_complete_flag,
dd.arrival_set_id,
dd.inventory_item_id,
ol.flow_status_code,
sum( dd.requested_quantity ) total_requested_quantity,
sum( dd.requested_quantity2 ) total_requested_quantity2,
sum( nvl(dd.shipped_quantity, 0 )) total_shipped_quantity ,
sum( nvl(dd.shipped_quantity2, 0 )) total_shipped_quantity2,
-- muom
-- Derive shipped_quantity (to pass to OM) only when Fufillment Base is Secondary
sum( decode(dd.requested_quantity_uom2, null, null,
decode(WSH_UTIL_CORE.Get_Line_Fulfillment_Base(dd.source_code, dd.source_line_id),
'S',WSH_WV_UTILS.CONVERT_UOM(
from_uom => ol.ordered_quantity_uom2,
to_uom => ol.order_quantity_uom,
quantity => nvl(dd.shipped_quantity2, 0 ),
item_id => dd.inventory_item_id)))) total_om_shipped_quantity
FROM wsh_delivery_Details dd,
wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st,
oe_order_lines_all ol
WHERE st.stop_id = dg.pick_up_stop_id AND
st.batch_id = cp_batch_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id AND
dl.delivery_id = da.delivery_id AND
da.delivery_id IS NOT NULL AND
da.delivery_detail_id = dd.delivery_detail_id
and nvl ( dd.oe_interfaced_flag , 'N' ) <> 'Y'
and dd.source_code = 'OE'
and ol.line_id = dd.source_line_id
and dd.released_status <> 'D'
and dd.client_id IS NULL -- LSP PROJECT : Should not perform OM interface for LSP orders
group by ol.org_id ,
dd.source_header_id ,
dd.source_header_number ,
dd.source_line_set_id,
dd.source_line_id,
dd.top_model_line_id,
dd.ship_set_id,
dd.ato_line_id,
dd.ship_set_id,
dd.arrival_set_id,
dd.inventory_item_id,
dd.ship_model_complete_flag,
WSH_WV_UTILS.CONVERT_UOM(ol.order_quantity_uom,
dd.requested_quantity_uom,
ol.ordered_quantity,
dd.inventory_item_id) ,
dd.requested_quantity_uom,
dd.requested_quantity_uom2,
ol.ordered_quantity,
ol.order_quantity_uom,
ol.ordered_quantity2,
ol.ordered_quantity_uom2,
ol.model_remnant_flag,
ol.item_type_code,
ol.calculate_price_flag,
dd.ship_tolerance_below ,
dd.ship_tolerance_above ,
dd.organization_id ,
NVL(dd.oe_interfaced_flag, 'N') ,
dl.initial_pickup_date,
ol.flow_status_code
ORDER BY ol.org_id,
dd.source_header_id,
dd.source_header_number,
--bug fix 3286811 : replaced total_shipped_quantity -total_requested_quantity in order by for 8.1.7.4 compatibility
(sum( nvl(dd.shipped_quantity, 0 )) - sum( dd.requested_quantity )),
dd.source_line_set_id,
dd.source_line_id,
dd.top_model_line_id,
dd.ato_line_id,
dd.ship_set_id,
dd.arrival_set_id,
dd.inventory_item_id,
dd.ship_model_complete_flag,
WSH_WV_UTILS.CONVERT_UOM(ol.order_quantity_uom,
dd.requested_quantity_uom,
ol.ordered_quantity,
dd.inventory_item_id) ,
dd.requested_quantity_uom,
dd.requested_quantity_uom2,
ol.ordered_quantity,
ol.order_quantity_uom,
ol.ordered_quantity2,
ol.ordered_quantity_uom2,
ol.model_remnant_flag,
ol.item_type_code,
ol.calculate_price_flag,
dd.ship_tolerance_below,
dd.ship_tolerance_above ,
dd.organization_id,
NVL(dd.oe_interfaced_flag, 'N') ,
dl.initial_pickup_date ;
WSH_DEBUG_SV.log(l_module_name,'Rows insert into l_bulk_ship_line',l_bulk_count);
l_non_bulk_ship_line.line_id.delete;
l_non_bulk_ship_line.requested_quantity.delete;
l_non_bulk_ship_line.requested_quantity2.delete;
l_non_bulk_ship_line.shipping_quantity2.delete;
l_non_bulk_ship_line.shipping_quantity.delete;
l_non_bulk_ship_line.shipped_quantity.delete; -- muom
l_non_bulk_ship_line.shipping_quantity_uom2.delete;
l_non_bulk_ship_line.shipping_quantity_uom.delete;
l_non_bulk_ship_line.order_quantity_uom.delete;
l_non_bulk_ship_line.order_quantity_uom2.delete;
l_non_bulk_ship_line.ordered_quantity.delete;
l_non_bulk_ship_line.ordered_quantity2.delete;
l_non_bulk_ship_line.fulfilled_flag.delete;
l_non_bulk_ship_line.actual_shipment_date.delete;
l_non_bulk_ship_line.header_id.delete;
l_non_bulk_ship_line.top_model_line_id.delete;
l_non_bulk_ship_line.ato_line_id.delete;
l_non_bulk_ship_line.ship_set_id.delete;
l_non_bulk_ship_line.arrival_set_id.delete;
l_non_bulk_ship_line.inventory_item_id.delete;
l_non_bulk_ship_line.ship_from_org_id.delete;
l_non_bulk_ship_line.line_set_id.delete;
l_non_bulk_ship_line.smc_flag.delete;
l_non_bulk_ship_line.over_ship_reason_code.delete;
l_non_bulk_ship_line.pending_quantity.delete;
l_non_bulk_ship_line.pending_quantity2.delete;
l_non_bulk_ship_line.pending_requested_flag.delete;
l_non_bulk_ship_line.item_type_code.delete;
l_non_bulk_ship_line.calculate_price_flag.delete;
l_non_bulk_req_line.line_id.delete;
l_non_bulk_req_line.requested_quantity.delete;
l_non_bulk_req_line.requested_quantity2.delete;
l_non_bulk_req_line.shipping_quantity2.delete;
l_non_bulk_req_line.shipping_quantity.delete;
l_non_bulk_req_line.shipped_quantity.delete; -- muom
l_non_bulk_req_line.shipping_quantity_uom2.delete;
l_non_bulk_req_line.shipping_quantity_uom.delete;
l_non_bulk_req_line.order_quantity_uom.delete;
l_non_bulk_req_line.order_quantity_uom2.delete;
l_non_bulk_req_line.ordered_quantity.delete;
l_non_bulk_req_line.ordered_quantity2.delete;
l_non_bulk_req_line.line_set_id.delete;
l_non_bulk_req_line.item_type_code.delete;
l_non_bulk_req_line.ato_line_id.delete;
l_non_bulk_req_line.top_model_line_id.delete;
l_non_bulk_req_line.inventory_item_id.delete;
l_non_bulk_req_line.ship_from_org_id.delete;
update wsh_delivery_details
set inv_interfaced_flag = 'P' ,
--Added as part of bug 7645262
last_update_date = sysdate,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id
where delivery_detail_id in (
SELECT da.delivery_detail_id
FROM wsh_delivery_assignments_v da ,
wsh_delivery_legs dg,
wsh_new_deliveries dl,
wsh_trip_stops st
where dl.delivery_id = da.delivery_id AND
da.delivery_id IS NOT NULL AND
st.stop_id = dg.pick_up_stop_id AND
st.batch_id = p_batch_id AND
st.stop_location_id = dl.initial_pickup_location_id AND
dg.delivery_id = dl.delivery_id AND
nvl(dl.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
)
--RTV changes
and source_code <> 'RTV'
and inv_interfaced_flag = 'N'
and container_flag = 'N'
and released_status <> 'D' ; /* H integration: wrudge */
WSH_DEBUG_SV.log(l_module_name,'Number of rows updated',SQL%ROWCOUNT);
Update_Interfaced_Details ( p_batch_id , l_return_status ) ;
SELECT source_line_id, organization_id, org_id from wsh_delivery_details
where delivery_detail_id = p_del_detail_id
and container_flag = 'N'
and released_status <> 'D' ; /* H integration: wrudge */
SELECT goods_dispatched_account
FROM wsh_shipping_parameters
WHERE organization_id = p_organization_id;
SELECT ORG_ID
INTO l_detail_rec.org_id
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = l_detail_rec.SOURCE_LINE_ID;
SELECT wdd.delivery_detail_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE wdd.inv_interfaced_flag IN ('N', 'P')
AND wts.batch_id = p_batch_id
AND wts.stop_location_id = wdd.ship_from_location_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wdl.delivery_id = wda.delivery_id
AND wda.delivery_id IS NOT NULL
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_code in ('OE','OKE', 'WSH','RTV') -- RTV changes
AND wdd.released_status <> 'D' /* H integration: wrudge */
AND nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
AND rownum = 1;
SELECT wdd.delivery_detail_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE -- wdd.oe_interfaced_flag <> 'Y'
wdd.oe_interfaced_flag NOT IN ( 'Y' ,'X') --Standalone WMS project changes
AND wts.batch_id = p_batch_id
AND wts.stop_location_id = wdd.ship_from_location_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wdl.delivery_id = wda.delivery_id
AND wda.delivery_id IS NOT NULL
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_code = 'OE'
AND wdd.released_status <> 'D' /* H integration: wrudge */
AND rownum = 1;
SELECT request_id
FROM FND_CONCURRENT_REQUESTS
WHERE parent_request_id = p_parent_request_id;
SELECT dd.source_header_id ,
dd.source_header_number,
NULL source_line_set_id,
dd.source_line_id,
WSH_WV_UTILS.CONVERT_UOM(ol.order_quantity_uom,
dd.requested_quantity_uom,
ol.ordered_quantity,
dd.inventory_item_id) order_line_quantity,
-- muom
ol.ordered_quantity2 order_line_quantity2,
dd.requested_quantity_uom,
NULL requested_quantity_uom2,
ol.ordered_quantity,
ol.order_quantity_uom,
NULL ordered_quantity2,
NULL ordered_quantity_uom2,
NULL model_remnant_flag,
ol.item_type_code,
ol.calculate_price_flag,
dd.ship_tolerance_below,
dd.ship_tolerance_above,
NULL org_id,
dd.organization_id,
NVL(dd.oe_interfaced_flag,'X') oe_interfaced_flag,
NULL initial_pickup_date ,
NULL top_model_line_id,
NULL ato_line_id ,
NULL ship_set_id ,
NULL ship_model_complete_flag ,
NULL arrival_set_id,
dd.inventory_item_id,
ol.flow_status_code,
SUM( dd.requested_quantity ) total_requested_quantity ,
NULL total_requested_quantity2,
SUM( NVL(dd.shipped_quantity, 0 )) total_shipped_quantity,
NULL total_shipped_quantity2,
-- muom
NULL total_om_shipped_quantity
FROM wsh_delivery_Details dd ,
oe_order_lines_all ol
WHERE dd.source_line_id = ol.line_id
AND dd.released_status = 'C'
AND dd.source_code = 'OE'
AND ol.shipped_quantity IS NULL
AND dd.source_line_id IN
( SELECT DISTINCT dd1.source_line_id
FROM wsh_delivery_details dd1 ,
wsh_delivery_assignments_v da
WHERE da.delivery_id = c_delivery_id
AND da.delivery_detail_id = dd1.delivery_detail_id
AND NVL(dd1.oe_interfaced_flag,'X') = 'X'
AND dd1.source_code = 'OE'
AND dd1.released_status = 'C')
GROUP BY dd.source_header_id,
dd.source_header_number,
dd.source_line_id,
ol.ordered_quantity,
dd.requested_quantity_uom,
ol.ordered_quantity,
ol.order_quantity_uom,
dd.inventory_item_id,
ol.item_type_code,
ol.calculate_price_flag,
dd.ship_tolerance_below,
dd.ship_tolerance_above,
dd.organization_id,
NVL(dd.oe_interfaced_flag, 'X') ,
ol.flow_status_code
ORDER BY dd.organization_id ,
dd.source_line_id ;
l_delete_detail_id NUMBER;
SELECT dd.source_header_id,
dd.source_header_number ,
dd.source_line_id ,
ol.ordered_quantity ,
dd.requested_quantity_uom,
ol.order_quantity_uom ,
dd.inventory_item_id,
ol.item_type_code ,
ol.org_id ,
ol.calculate_price_flag ,
dd.ship_tolerance_below,
dd.ship_tolerance_above ,
dd.organization_id ,
NVL(dd.oe_interfaced_flag, 'X') oe_interfaced_flag ,
ol.flow_status_code ,
SUM( dd.requested_quantity ) total_requested_quantity ,
SUM( NVL(dd.shipped_quantity, 0 )) total_shipped_quantity
FROM wsh_delivery_Details dd ,
oe_order_lines_all ol
WHERE dd.source_line_id = ol.line_id
AND dd.released_status = 'C'
AND dd.source_line_id = c_line_id
AND dd.source_code = 'OE'
and ol.shipped_quantity is NULL
AND NOT EXISTS
( SELECT 'X'
FROM wsh_delivery_details wdd2
WHERE wdd2.source_line_id = dd.source_line_id
AND wdd2.source_code = 'OE'
AND wdd2.released_status IN ( 'R','B','S','Y','X'))
GROUP BY dd.source_header_id ,
dd.source_header_number ,
dd.source_line_id ,
ol.ordered_quantity ,
dd.requested_quantity_uom ,
ol.ordered_quantity ,
ol.order_quantity_uom ,
dd.inventory_item_id ,
ol.item_type_code ,
ol.org_id ,
ol.calculate_price_flag ,
dd.ship_tolerance_below ,
dd.ship_tolerance_above ,
dd.organization_id ,
NVL(dd.oe_interfaced_flag, 'X') ,
ol.flow_status_code
ORDER BY dd.organization_id ,
dd.source_line_id ;
l_bulk_ship_line.line_id.DELETE;
l_non_bulk_ship_line.line_id.DELETE;
SELECT initial_pickup_date
INTO l_pick_up_date
FROM wsh_delivery_details wdd ,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE wdd.source_line_id = line_rec.source_line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND ROWNUM =1;