[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update_flag IN VARCHAR2,
menu_flag IN VARCHAR2,
dpw_pack_flag IN VARCHAR2,
x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
master_weight_uom IN VARCHAR2,
net_weight IN OUT NUMBER,
tare_weight IN OUT NUMBER,
master_volume_uom IN VARCHAR2,
volume IN OUT NUMBER,
status IN OUT NUMBER)
IS
BEGIN
status := 0;
OR update_flag IS NULL
OR menu_flag IS NULL THEN
status := -1;
OR update_flag NOT IN ('Y', 'N')
OR menu_flag NOT IN ('Y', 'N')
OR (dpw_pack_flag NOT IN ('Y', 'N') AND source = 'DPW')
OR (x_sc_wv_mode NOT IN ('ALL', 'ENTERED') AND source = 'SC') THEN
status := -1;
wv_flag, update_flag, menu_flag, dpw_pack_flag, x_sc_wv_mode,
master_weight_uom, net_weight, tare_weight,
master_volume_uom, volume,
status);
update_flag IN VARCHAR2,
menu_flag IN VARCHAR2,
dpw_pack_flag IN VARCHAR2,
x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
master_weight_uom IN VARCHAR2,
net_weight IN OUT NUMBER,
tare_weight IN OUT NUMBER,
master_volume_uom IN VARCHAR2,
volume IN OUT NUMBER,
status IN OUT NUMBER)
IS
CURSOR delivery_recs(x_dep_id NUMBER) IS
SELECT delivery_id,
gross_weight, weight_uom_code,
volume, volume_uom_code
FROM wsh_deliveries
WHERE actual_departure_id = x_dep_id
AND status_code <> 'CA';
SELECT NVL(SUM( wsh_wv_pvt.convert_uom(msi.weight_uom_code, x_to_uom,
NVL(msi.unit_weight, 0))
* pc.quantity),
0) tare_weight
FROM wsh_packed_containers pc,
mtl_system_items msi
WHERE msi.inventory_item_id = pc.container_inventory_item_id
AND pc.delivery_id = x_del_id
AND msi.organization_id = x_o_id;
IF update_flag = 'Y'
AND (x_gross_weight > 0 OR x_volume > 0) THEN
UPDATE wsh_deliveries
SET gross_weight = decode(x_gross_weight, 0, gross_weight,
x_gross_weight),
weight_uom_code = decode(x_gross_weight, 0, weight_uom_code,
master_weight_uom),
volume = decode(x_volume, 0, x_volume,
x_volume),
volume_uom_code = decode(x_volume, 0, volume_uom_code,
master_volume_uom)
WHERE delivery_id = d.delivery_id;
SELECT SUM( NVL(msi.unit_weight, 0) *
WSH_WV_PVT.convert_uom(sld.unit_code,
msi.primary_uom_code,
sld.quantity,
sld.inventory_item_id) ) weight,
msi.weight_uom_code uom
FROM so_line_details sld,
mtl_system_items msi
WHERE sld.departure_id = x_dep_id
AND sld.delivery_id IS NULL
AND msi.inventory_item_id = sld.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.weight_uom_code;
SELECT SUM( NVL(msi.unit_volume, 0) *
WSH_WV_PVT.convert_uom(sld.unit_code,
msi.primary_uom_code,
sld.quantity,
sld.inventory_item_id) ) volume,
msi.volume_uom_code uom
FROM so_line_details sld,
mtl_system_items msi
WHERE sld.departure_id = x_dep_id
AND sld.delivery_id IS NULL
AND msi.inventory_item_id = sld.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.volume_uom_code;
SELECT SUM( NVL(msi.unit_weight, 0) *
WSH_WV_PVT.convert_uom(sl.unit_code,
msi.primary_uom_code,
sld.requested_quantity,
sl.inventory_item_id) ) weight,
msi.weight_uom_code uom
FROM so_picking_line_details sld,
so_picking_lines_all sl,
mtl_system_items msi
WHERE sld.departure_id = x_dep_id
AND sld.delivery_id IS NULL
AND sl.picking_line_id = sld.picking_line_id
AND sl.picking_header_id = 0 -- backordered
AND msi.inventory_item_id = sl.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.weight_uom_code;
SELECT SUM( NVL(msi.unit_volume, 0) *
WSH_WV_PVT.convert_uom(sl.unit_code,
msi.primary_uom_code,
sld.requested_quantity,
sl.inventory_item_id) ) volume,
msi.volume_uom_code uom
FROM so_picking_line_details sld,
so_picking_lines_all sl,
mtl_system_items msi
WHERE sld.departure_id = x_dep_id
AND sld.delivery_id IS NULL
AND sl.picking_line_id = sld.picking_line_id
AND sl.picking_header_id = 0 -- backordered
AND msi.inventory_item_id = sl.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.volume_uom_code;
SELECT l.line_id ato_line_id,
sum(ld.quantity) qty
FROM so_line_details ld,
so_lines_all l
WHERE ld.departure_id = x_dep_id
AND ld.delivery_id IS NULL
AND ld.included_item_flag = 'N'
AND l.line_id = ld.line_id
AND l.ato_flag = 'Y'
AND l.ato_line_id IS NULL
GROUP BY l.line_id;
SELECT l.line_id ato_line_id,
sum(NVL(pld.requested_quantity, 0)) qty,
--
-- for weight and/or volume
-- add the components only if the UOMs and values are same
-- for the model its the configuration item.
-- The WHERE clause only finds models where at least
-- one physical attribute is the same; there may be two.
SELECT SUM( NVL(msi.unit_weight, 0) *
WSH_WV_PVT.convert_uom(sl.unit_code,
msi.primary_uom_code,
NVL(sld.shipped_quantity,
x_wv_mode*sld.requested_quantity),
sl.inventory_item_id) ) weight,
msi.weight_uom_code uom
FROM so_picking_line_details sld,
so_picking_lines_all sl,
mtl_system_items msi
WHERE sld.departure_id = x_dep_id
AND sld.delivery_id IS NULL
AND sl.picking_line_id = sld.picking_line_id
AND sl.picking_header_id+0 > 0 -- NOT backordered
AND msi.inventory_item_id = sl.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.weight_uom_code;
SELECT SUM( NVL(msi.unit_volume, 0) *
WSH_WV_PVT.convert_uom(sl.unit_code,
msi.primary_uom_code,
NVL(sld.shipped_quantity,
x_wv_mode*sld.requested_quantity),
sl.inventory_item_id) ) volume,
msi.volume_uom_code uom
FROM so_picking_line_details sld,
so_picking_lines_all sl,
mtl_system_items msi
WHERE sld.departure_id = x_dep_id
AND sld.delivery_id IS NULL
AND sl.picking_line_id = sld.picking_line_id
AND sl.picking_header_id+0 > 0 -- NOT backordered
AND msi.inventory_item_id = sl.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.volume_uom_code;
SELECT l.line_id ato_line_id,
sum(NVL(pld.shipped_quantity,
x_wv_mode * pld.requested_quantity)) qty,
--
-- for weight and/or volume
-- add the components only if the UOMs and values are same
-- for the model and the configuration item.
-- The WHERE clause only finds models where at least
-- one physical attribute is the same; there may be two.
SELECT SUM(pc.quantity / cl.max_load_quantity) * 100
FROM wsh_container_load cl,
wsh_packed_containers pc,
wsh_deliveries d
WHERE d.actual_departure_id = x_dep_id
AND d.delivery_id = pc.delivery_id
AND pc.container_inventory_item_id = cl.load_item_id
AND cl.container_item_id = x_veh_id
AND cl.master_organization_id =
(SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = x_o_id)
AND NVL(cl.max_load_quantity, 0) > 0
AND (pc.parent_sequence_number IS NULL
OR not exists (select sequence_number from wsh_packed_containers
where delivery_id = pc.delivery_id
and sequence_number = pc.parent_sequence_number))
GROUP BY 1;
update_flag IN VARCHAR2,
menu_flag IN VARCHAR2,
dpw_pack_flag IN VARCHAR2,
x_sc_wv_mode IN VARCHAR2 DEFAULT 'ALL',
master_weight_uom IN VARCHAR2,
gross_weight IN OUT NUMBER,
master_volume_uom IN VARCHAR2,
volume IN OUT NUMBER,
status IN OUT NUMBER)
IS
pack_status NUMBER;
IF update_flag = 'Y' THEN
IF x_gross_weight > 0 AND x_volume > 0 THEN
UPDATE wsh_deliveries
SET gross_weight = x_gross_weight,
weight_uom_code = x_master_weight_uom,
volume = x_volume,
volume_uom_code = x_master_volume_uom
WHERE delivery_id = x_del_id;
UPDATE wsh_deliveries
SET gross_weight = x_gross_weight,
weight_uom_code = x_master_weight_uom
WHERE delivery_id = x_del_id;
UPDATE wsh_deliveries
SET volume = x_volume,
volume_uom_code = x_master_volume_uom
WHERE delivery_id = x_del_id;
SELECT SUM(NVL(msi.unit_volume, 0) * pc.quantity) volume,
msi.volume_uom_code uom
FROM wsh_packed_containers pc,
mtl_system_items msi
WHERE msi.inventory_item_id = pc.container_inventory_item_id
AND pc.delivery_id = x_del_id
AND msi.organization_id = x_o_id
AND (pc.parent_sequence_number IS NULL
OR not exists (select sequence_number from wsh_packed_containers
where delivery_id = pc.delivery_id
and sequence_number = pc.parent_sequence_number))
GROUP BY msi.volume_uom_code;
SELECT SUM( NVL(msi.unit_volume, 0) *
WSH_WV_PVT.convert_uom(sld.unit_code,
msi.primary_uom_code,
sld.quantity,
sld.inventory_item_id) ) volume,
msi.volume_uom_code uom
FROM so_line_details sld,
mtl_system_items msi
WHERE sld.delivery_id = x_del_id
AND sld.master_container_item_id IS NULL -- no default containers may
AND sld.detail_container_item_id IS NULL -- mean that it is unpacked.
AND msi.inventory_item_id = sld.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.volume_uom_code;
SELECT SUM( NVL(msi.unit_volume, 0) *
WSH_WV_PVT.convert_uom(sl.unit_code,
msi.primary_uom_code,
sld.requested_quantity,
sl.inventory_item_id) ) volume,
msi.volume_uom_code uom
FROM so_picking_line_details sld,
so_picking_lines_all sl,
mtl_system_items msi
WHERE sld.delivery_id = x_del_id
AND sl.picking_line_id = sld.picking_line_id
AND sld.master_container_item_id IS NULL -- no default containers may
AND sld.detail_container_item_id IS NULL -- mean that it is unpacked.
AND sl.picking_header_id = 0 -- backordered
AND msi.inventory_item_id = sl.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.volume_uom_code;
SELECT l.line_id ato_line_id,
sum(NVL(ld.quantity, 0)) qty
FROM so_line_details ld,
so_lines_all l
WHERE ld.delivery_id = x_del_id
AND ld.master_container_item_id IS NULL -- no default containers may
AND ld.detail_container_item_id IS NULL -- mean that it is unpacked.
AND ld.included_item_flag = 'N'
AND l.line_id = ld.line_id
AND l.ato_flag = 'Y'
AND l.ato_line_id IS NULL
GROUP BY l.line_id;
SELECT l.line_id ato_line_id,
sum(NVL(pld.requested_quantity, 0)) qty
FROM so_picking_line_details pld,
so_picking_lines_all pl,
so_line_details ld,
so_lines_all l,
mtl_system_items i_msi, -- configuration item
mtl_system_items m_msi -- model
WHERE pld.delivery_id = x_del_id
AND pld.container_id IS NULL
AND pl.picking_line_id = pld.picking_line_id
AND pl.picking_header_id+0 = 0 -- backordered
AND ld.line_detail_id = pl.line_detail_id
AND pld.master_container_item_id IS NULL -- no default containers may
AND pld.detail_container_item_id IS NULL -- mean that it is unpacked.
AND ld.configuration_item_flag = 'Y'
AND l.line_id = ld.line_id
AND l.ato_flag = 'Y'
AND l.ato_line_id IS NULL
AND i_msi.inventory_item_id = pl.inventory_item_id
AND i_msi.organization_id = pl.warehouse_id
AND m_msi.inventory_item_id = l.inventory_item_id
AND m_msi.organization_id = pl.warehouse_id
AND -- same volume attributes
(NVL(m_msi.volume_uom_code, 'EMPTY')
= NVL(i_msi.volume_uom_code, 'EMPTY')
AND NVL(m_msi.unit_volume, 0) = NVL(i_msi.unit_volume, 0))
GROUP BY l.line_id;
SELECT SUM( NVL(msi.unit_volume, 0) *
WSH_WV_PVT.convert_uom(sl.unit_code,
msi.primary_uom_code,
NVL(sld.shipped_quantity,
x_wv_mode*sld.requested_quantity),
sl.inventory_item_id) ) volume,
msi.volume_uom_code uom
FROM so_picking_line_details sld,
so_picking_lines_all sl,
mtl_system_items msi
WHERE sld.delivery_id = x_del_id
AND sl.picking_line_id = sld.picking_line_id
AND sld.container_id IS NULL -- definitely unpacked.
AND sl.picking_header_id+0 > 0 -- NOT backordered
AND msi.inventory_item_id = sl.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.volume_uom_code;
SELECT l.line_id ato_line_id,
sum(NVL(pld.shipped_quantity,
x_wv_mode * pld.requested_quantity)) qty
FROM so_picking_line_details pld,
so_picking_lines_all pl,
so_line_details ld,
so_lines_all l,
mtl_system_items i_msi, -- configuration item
mtl_system_items m_msi -- model
WHERE pld.delivery_id = x_del_id
AND pld.container_id IS NULL
AND pl.picking_line_id = pld.picking_line_id
AND pl.picking_header_id+0 > 0 -- NOT backordered
AND ld.line_detail_id = pl.line_detail_id
AND ld.configuration_item_flag = 'Y'
AND l.line_id = ld.line_id
AND l.ato_flag = 'Y'
AND l.ato_line_id IS NULL
AND i_msi.inventory_item_id = pl.inventory_item_id
AND i_msi.organization_id = pl.warehouse_id
AND m_msi.inventory_item_id = l.inventory_item_id
AND m_msi.organization_id = pl.warehouse_id
AND -- same volume attributes
(NVL(m_msi.volume_uom_code, 'EMPTY')
= NVL(i_msi.volume_uom_code, 'EMPTY')
AND NVL(m_msi.unit_volume, 0) = NVL(i_msi.unit_volume, 0))
GROUP BY l.line_id;
SELECT SUM(NVL(msi.unit_weight, 0) * pc.quantity) weight,
msi.weight_uom_code uom
FROM wsh_packed_containers pc,
mtl_system_items msi
WHERE msi.inventory_item_id = pc.container_inventory_item_id
AND pc.delivery_id = x_del_id
AND msi.organization_id = x_o_id
GROUP BY msi.weight_uom_code;
SELECT SUM( NVL(msi.unit_weight, 0) *
WSH_WV_PVT.convert_uom(sld.unit_code,
msi.primary_uom_code,
sld.quantity,
sld.inventory_item_id) ) weight,
msi.weight_uom_code uom
FROM so_line_details sld,
mtl_system_items msi
WHERE sld.delivery_id = x_del_id
AND msi.inventory_item_id = sld.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.weight_uom_code;
SELECT SUM( NVL(msi.unit_weight, 0) *
WSH_WV_PVT.convert_uom(sl.unit_code,
msi.primary_uom_code,
sld.requested_quantity,
sl.inventory_item_id) ) weight,
msi.weight_uom_code uom
FROM so_picking_line_details sld,
so_picking_lines_all sl,
mtl_system_items msi
WHERE sld.delivery_id = x_del_id
AND sl.picking_line_id = sld.picking_line_id
AND sl.picking_header_id = 0 -- backordered
AND msi.inventory_item_id = sl.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.weight_uom_code;
SELECT l.line_id ato_line_id,
sum(ld.quantity) qty
FROM so_line_details ld,
so_lines_all l
WHERE ld.delivery_id = x_del_id
AND ld.included_item_flag = 'N'
AND l.line_id = ld.line_id
AND l.ato_flag = 'Y'
AND l.ato_line_id IS NULL
GROUP BY l.line_id;
SELECT l.line_id ato_line_id,
sum(NVL(pld.requested_quantity, 0)) qty
FROM so_picking_line_details pld,
so_picking_lines_all pl,
so_line_details ld,
so_lines_all l,
mtl_system_items i_msi, -- configuration item
mtl_system_items m_msi -- model
WHERE pld.delivery_id = x_del_id
AND pl.picking_line_id = pld.picking_line_id
AND pl.picking_header_id+0 = 0 -- backordered
AND ld.line_detail_id = pl.line_detail_id
AND ld.configuration_item_flag = 'Y'
AND l.line_id = ld.line_id
AND l.ato_flag = 'Y'
AND l.ato_line_id IS NULL
AND i_msi.inventory_item_id = pl.inventory_item_id
AND i_msi.organization_id = pl.warehouse_id
AND m_msi.inventory_item_id = l.inventory_item_id
AND m_msi.organization_id = pl.warehouse_id
AND -- same weight attributes
(NVL(m_msi.weight_uom_code, 'EMPTY')
= NVL(i_msi.weight_uom_code, 'EMPTY')
AND NVL(m_msi.unit_weight, 0) = NVL(i_msi.unit_weight, 0))
GROUP BY l.line_id;
SELECT DISTINCT pc.parent_sequence_number
FROM wsh_packed_containers pc
WHERE pc.delivery_id = x_del_id
AND pc.parent_sequence_number IS NOT NULL
AND not exists (select sequence_number from wsh_packed_containers
where delivery_id = pc.delivery_id
and sequence_number = pc.parent_sequence_number);
SELECT spl.inventory_item_id id,
spl.warehouse_id o_id
FROM so_picking_line_details spld,
so_picking_lines_all spl
WHERE spld.delivery_id = x_del_id
AND spld.container_id IS NULL -- not packed.
-- but this item will be shipped...
AND NVL(spld.shipped_quantity, x_ship_all_flag) > 0
AND spl.picking_line_id = spld.picking_line_id
AND spl.picking_header_id+0 > 0
GROUP BY spl.inventory_item_id, spl.warehouse_id
ORDER BY spl.inventory_item_id;
SELECT pc.sequence_number seq_num,
pc.sequence_number name,
pc.quantity quantity,
pc.gross_weight weight,
pc.weight_uom_code w_uom_code,
msi.maximum_load_weight max_weight,
msi.weight_uom_code mw_uom_code
FROM wsh_packed_containers pc,
mtl_system_items msi
WHERE pc.delivery_id = x_del_id
AND msi.inventory_item_id = pc.container_inventory_item_id
AND msi.organization_id = x_o_id
AND NVL(msi.maximum_load_weight, 0) > 0
AND ( ( pc.weight_uom_code = msi.weight_uom_code
AND (pc.gross_weight / pc.quantity) > msi.maximum_load_weight)
OR (pc.weight_uom_code <> msi.weight_uom_code));
SELECT pc.container_id id,
pc.sequence_number sequence_number,
pc.gross_weight gross_weight,
pc.weight_uom_code gross_uom_code,
pc.quantity quantity,
pc.container_inventory_item_id containter_inventory_item_id,
pc.rowid rid,
msi.unit_weight unit_weight,
msi.weight_uom_code uom_code
FROM wsh_packed_containers pc,
mtl_system_items msi
WHERE pc.delivery_id = x_del_id
AND pc.container_inventory_item_id = msi.inventory_item_id
AND msi.organization_id = x_o_id
AND NVL(pc.parent_sequence_number, -1) = NVL(x_seq_num, -1);
SELECT pl.inventory_item_id,
SUM( NVL(msi.unit_weight, 0) *
WSH_WV_PVT.convert_uom(pl.unit_code,
msi.primary_uom_code,
NVL(cc.shipped_quantity,
x_wv_mode*cc.requested_quantity),
pl.inventory_item_id) ) weight,
msi.weight_uom_code uom_code
FROM so_picking_line_details cc,
so_picking_lines_all pl,
mtl_system_items msi
WHERE cc.container_id = x_cont_id
AND cc.delivery_id = x_del_id
AND pl.picking_line_id = cc.picking_line_id
AND pl.picking_header_id+0 > 0
AND pl.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY pl.inventory_item_id, weight_uom_code;
SELECT l.line_id ato_line_id,
sum(NVL(pld.shipped_quantity,
x_wv_mode * pld.requested_quantity)) qty
FROM so_picking_line_details pld,
so_picking_lines_all pl,
so_line_details ld,
so_lines_all l,
mtl_system_items i_msi, -- configuration item
mtl_system_items m_msi -- model
WHERE pld.delivery_id = x_del_id
AND pld.container_id = x_cont_id
AND pl.picking_line_id = pld.picking_line_id
AND pl.picking_header_id+0 > 0 -- NOT backordered
AND ld.line_detail_id = pl.line_detail_id
AND ld.configuration_item_flag = 'Y'
AND l.line_id = ld.line_id
AND l.ato_flag = 'Y'
AND l.ato_line_id IS NULL
AND i_msi.inventory_item_id = pl.inventory_item_id
AND i_msi.organization_id = pl.warehouse_id
AND m_msi.inventory_item_id = l.inventory_item_id
AND m_msi.organization_id = pl.warehouse_id
AND -- same weight attributes
(NVL(m_msi.weight_uom_code, 'EMPTY')
= NVL(i_msi.weight_uom_code, 'EMPTY')
AND NVL(m_msi.unit_weight, 0) = NVL(i_msi.unit_weight, 0))
GROUP BY l.line_id;
UPDATE wsh_packed_containers
SET gross_weight = x_cont_weight,
weight_uom_code = x_master_uom
WHERE rowid = container.rid;
SELECT SUM(NVL(msi.unit_weight, 0) *
WSH_WV_PVT.convert_uom(spl.unit_code,
msi.primary_uom_code,
NVL(spld.shipped_quantity,
x_wv_mode*spld.requested_quantity),
spl.inventory_item_id) ) weight,
msi.weight_uom_code uom
FROM so_picking_line_details spld,
so_picking_lines_all spl,
mtl_system_items msi
WHERE spld.delivery_id = x_del_id
AND spld.container_id IS NULL -- not packed in any container
AND spld.picking_line_id = spl.picking_line_id
AND spl.picking_header_id+0 > 0
AND msi.inventory_item_id = spl.inventory_item_id
AND msi.organization_id = x_o_id
GROUP BY msi.weight_uom_code;
SELECT l.line_id ato_line_id,
sum(NVL(pld.shipped_quantity,
x_wv_mode * pld.requested_quantity)) qty
FROM so_picking_line_details pld,
so_picking_lines_all pl,
so_line_details ld,
so_lines_all l,
mtl_system_items i_msi, -- configuration item
mtl_system_items m_msi -- model
WHERE pld.delivery_id = x_del_id
AND pld.container_id IS NULL -- not packed in any container
AND pl.picking_line_id = pld.picking_line_id
AND pl.picking_header_id+0 > 0
AND ld.line_detail_id = pl.line_detail_id
AND ld.configuration_item_flag = 'Y'
AND l.line_id = ld.line_id
AND l.ato_flag = 'Y'
AND l.ato_line_id IS NULL
AND i_msi.inventory_item_id = pl.inventory_item_id
AND i_msi.organization_id = pl.warehouse_id
AND m_msi.inventory_item_id = l.inventory_item_id
AND m_msi.organization_id = pl.warehouse_id
AND -- same weight attributes
(NVL(m_msi.weight_uom_code, 'EMPTY')
= NVL(i_msi.weight_uom_code, 'EMPTY')
AND NVL(m_msi.unit_weight, 0) = NVL(i_msi.unit_weight, 0))
GROUP BY l.line_id;
SELECT dl.load_seq_number load_seq_number,
wcl.container_item_id iid,
WSH_WV_PVT.convert_uom(dl.unit_code,
item_msi.primary_uom_code,
dl.quantity,
dl.inventory_item_id)
/ wcl.max_load_quantity raw_qty,
0.01*NVL(cont_msi.minimum_fill_percent, 0) min_fill,
cont_msi.weight_uom_code
FROM so_line_details dl,
mtl_system_items item_msi,
wsh_container_load wcl,
mtl_system_items cont_msi
WHERE
dl.delivery_id = x_del_id
AND item_msi.inventory_item_id = dl.inventory_item_id
AND item_msi.organization_id = x_o_id
AND wcl.load_item_id = dl.inventory_item_id
AND wcl.master_organization_id =
(SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = x_o_id)
AND wcl.container_item_id = NVL(dl.master_container_item_id,
dl.detail_container_item_id)
AND wcl.max_load_quantity > 0
AND cont_msi.inventory_item_id = wcl.container_item_id
AND cont_msi.organization_id = x_o_id
UNION ALL
-- copied from SELECT above and modified for backordered picking lines
SELECT dl.load_seq_number load_seq_number,
wcl.container_item_id iid,
WSH_WV_PVT.convert_uom(spl.unit_code,
item_msi.primary_uom_code,
NVL(dl.shipped_quantity,
dl.requested_quantity),
spl.inventory_item_id)
/ wcl.max_load_quantity raw_qty,
0.01*NVL(cont_msi.minimum_fill_percent, 0) min_fill,
cont_msi.weight_uom_code
FROM so_picking_line_details dl,
so_picking_lines_all spl,
mtl_system_items item_msi,
wsh_container_load wcl,
mtl_system_items cont_msi
WHERE
spl.picking_line_id = dl.picking_line_id
AND spl.picking_header_id = 0 -- backordered
AND dl.delivery_id = x_del_id
AND item_msi.inventory_item_id = spl.inventory_item_id
AND item_msi.organization_id = x_o_id
AND wcl.load_item_id = item_msi.inventory_item_id
AND wcl.master_organization_id =
(SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = x_o_id)
AND wcl.container_item_id = NVL(dl.master_container_item_id,
dl.detail_container_item_id)
AND wcl.max_load_quantity > 0
AND cont_msi.inventory_item_id = wcl.container_item_id
AND cont_msi.organization_id = x_o_id
ORDER BY 1, 2;
DELETE FROM wsh_packed_containers
WHERE delivery_id = x_del_id;
-- and update variables with the new container's information.
IF current_container_item_id IS NOT NULL THEN
/* Bug 770276 :Increment the sequence number by 10 */
load_seq_number := nvl(load_seq_number,0) + 10;
INSERT INTO wsh_packed_containers
(container_id, delivery_id,
container_inventory_item_id, quantity,
parent_sequence_number, sequence_number, -- sequences
weight_uom_code, -- weight
organization_id,
creation_date, created_by, -- creation
last_update_date, last_updated_by) -- update
VALUES
(wsh_packed_containers_s.nextval, del_id,
cont_item_id, qty,
parent_seq, load_seq_number, -- sequences
weight_uom_code, -- weight
organization_id,
sysdate, current_user, -- creation
sysdate, current_user -- update
);