[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM( NVL(msi.unit_weight, 0) *
WSH_WV_PVT.convert_uom(spl.unit_code,
msi.primary_uom_code,
NVL(spld.shipped_quantity,
spld.requested_quantity),
spl.inventory_item_id) ) weight,
msi.weight_uom_code uom
FROM so_picking_line_details spld,
so_picking_lines_all spl,
so_lines_all sl,
so_headers_all sh,
mtl_system_items msi,
wsh_deliveries wd
WHERE spld.delivery_id = x_del_id
AND wd.delivery_id = x_del_id
AND spl.picking_line_id = spld.picking_line_id
AND spl.picking_header_id+0 > 0 -- NOT backordered
AND sl.line_id = spl.order_line_id
AND sh.header_id = sl.header_id
AND sh.order_number = x_order_n
AND sh.order_type_id = NVL(x_order_t, sh.order_type_id)
AND msi.inventory_item_id = spl.inventory_item_id
AND msi.organization_id = wd.organization_id
GROUP BY msi.weight_uom_code;
SELECT SUM( NVL(msi.unit_weight, 0) *
WSH_WV_PVT.convert_uom(spl.unit_code,
msi.primary_uom_code,
-- if qty is NULL, it must be 0 here.
NVL(spld.shipped_quantity, 0),
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,
wsh_deliveries wd
WHERE spld.delivery_id = x_del_id
AND wd.delivery_id = x_del_id
AND spl.picking_line_id = spld.picking_line_id
AND spl.picking_header_id+0 > 0 -- NOT backordered
AND msi.inventory_item_id = spl.inventory_item_id
AND msi.organization_id = wd.organization_id
GROUP BY msi.weight_uom_code;
SELECT l.line_id ato_line_id,
-- if qty is NULL, it must be 0 here.
sum(NVL(pld.shipped_quantity, 0)) qty
FROM so_picking_line_details pld,
so_picking_lines_all pl,
so_line_details ld,
so_lines_all l,
so_headers_all h,
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 -- 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 h.header_id = l.header_id
AND h.order_number = NVL(x_order_n, h.order_number)
AND h.order_type_id = NVL(x_order_t, h.order_type_id)
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 (NVL(sl.ordered_quantity, 0) - NVL(sl.cancelled_quantity, 0)) qty
FROM so_lines_all sl
WHERE sl.line_id = x_a_line_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,
so_lines_all sl,
mtl_system_items msi
WHERE sl.ato_line_id = x_a_line_id
AND sld.line_id = sl.line_id
AND sld.included_item_flag = 'N'
AND NVL(sld.configuration_item_flag, 'N') = 'N'
AND msi.inventory_item_id = sld.inventory_item_id
AND msi.organization_id = sld.warehouse_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,
so_lines_all sl,
mtl_system_items msi
WHERE sl.ato_line_id = x_a_line_id
AND sld.line_id = sl.line_id
AND sld.included_item_flag = 'N'
AND NVL(sld.configuration_item_flag, 'N') = 'N'
AND msi.inventory_item_id = sld.inventory_item_id
AND msi.organization_id = sld.warehouse_id
GROUP BY msi.volume_uom_code;
select wd.weight_uom_code,
wd.volume_uom_code,
wd.organization_id
from wsh_deliveries wd
where wd.delivery_id = x_delivery_id;
select wpc.sequence_number sequence_number,
wpc.container_id container_id,
wpc.container_inventory_item_id inventory_item_id,
wpc.quantity quantity,
-- net_weight = gross_weight - tare_weight
WSH_WV_PVT.convert_uom(wpc.weight_uom_code,
msi.weight_uom_code,
NVL(wpc.gross_weight, 0),
msi.inventory_item_id)
- wpc.quantity * NVL(msi.unit_weight,0) net_weight,
msi.maximum_load_weight * wpc.quantity max_weight,
msi.weight_uom_code weight_uom,
msi.internal_volume * wpc.quantity max_volume,
msi.volume_uom_code volume_uom,
msi.minimum_fill_percent min_fill_percent
from wsh_packed_containers wpc,
mtl_system_items msi
where wpc.delivery_id = x_delivery_id
and msi.inventory_item_id = wpc.container_inventory_item_id
and msi.organization_id = wpc.organization_id
order by wpc.sequence_number;
SELECT pc.container_inventory_item_id inventory_item_id,
NVL(msi.unit_volume, 0) * pc.quantity volume,
msi.volume_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_volume, 0) *
WSH_WV_PVT.convert_uom(pl.unit_code,
msi.primary_uom_code,
NVL(cc.shipped_quantity,
x_ship_f*cc.requested_quantity),
pl.inventory_item_id) ) volume,
msi.volume_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, volume_uom_code;
SELECT l.line_id ato_line_id,
sum(NVL(pld.shipped_quantity,
x_ship_f*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 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(pc.quantity / cl.max_load_quantity) fill
FROM wsh_container_load cl,
wsh_packed_containers pc
WHERE pc.delivery_id = x_del_id
AND NVL(pc.parent_sequence_number, -1) = NVL(x_seq_num, -1)
AND cl.load_item_id = pc.container_inventory_item_id
AND cl.container_item_id = x_container_item_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
GROUP BY 1;
SELECT SUM( WSH_WV_PVT.convert_uom(pl.unit_code,
msi.primary_uom_code,
NVL(pld.shipped_quantity,
x_ship_f*pld.requested_quantity))
/ cl.max_load_quantity) fill
FROM wsh_container_load cl,
so_picking_line_details pld,
so_picking_lines_all pl,
mtl_system_items msi
WHERE pld.delivery_id = x_del_id
AND pld.container_id = x_cont_id
AND pl.picking_line_id = pld.picking_line_id
AND msi.inventory_item_id = pl.inventory_item_id
AND msi.organization_id = x_o_id
AND cl.load_item_id = pl.inventory_item_id
AND cl.container_item_id = x_container_item_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
GROUP BY 1;
SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
P_to_uom,
NVL(msi.unit_weight, 0)) *
NVL(pld.shipped_quantity,
P_pack_flag *
pld.requested_quantity),
0) cont_net_weight
FROM so_picking_line_details pld,
so_picking_lines_all pl,
mtl_system_items msi
WHERE pl.picking_line_id = pld.picking_line_id
AND msi.inventory_item_id = pl.inventory_item_id
AND msi.organization_id = P_organization_id
AND pld.container_id = P_container_id;
SELECT container_id
FROM wsh_packed_containers
START WITH container_id = P_container_id
CONNECT BY PRIOR container_id = parent_container_id;
SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
P_to_uom,
NVL(msi.unit_weight, 0)) * pc.quantity,
0) cont_tare_weight
FROM wsh_packed_containers pc,
mtl_system_items msi
WHERE msi.inventory_item_id = pc.container_inventory_item_id
AND container_id = P_container_id
AND msi.organization_id = p_org_id;
SELECT container_id
FROM wsh_packed_containers pc
START WITH container_id = P_container_id
CONNECT BY PRIOR container_id = parent_container_id;
SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
P_to_uom,
NVL(msi.unit_weight, 0)) * pc.quantity,
0) cont_tare_weight
FROM wsh_packed_containers pc,
mtl_system_items msi
WHERE msi.inventory_item_id = pc.container_inventory_item_id
AND container_id = P_container_id
AND msi.organization_id = P_org_id;
SELECT NVL(wsh_wv_pvt.convert_uom(msi.weight_uom_code,
P_to_uom,
NVL(msi.unit_weight, 0)) * pc.quantity,
0) cont_tare_weight
FROM wsh_packed_containers pc,
mtl_system_items msi
WHERE msi.inventory_item_id = pc.container_inventory_item_id
AND container_id = P_container_id
AND msi.organization_id = P_org_id;
SELECT container_id
FROM wsh_packed_containers pc
WHERE pc.delivery_id = p_del_id;
SELECT pc.container_id id,
pc.weight_uom_code wt_uom,
pc.volume_uom_code v_uom,
pc.sequence_number seq_num,
pc.gross_weight gross_wt,
pc.net_weight net_wt,
pc.volume v,
pc.container_inventory_item_id cont_inv_id,
pc.quantity quantity,
msi.internal_volume max_v,
msi.maximum_load_weight max_wt,
msi.weight_uom_code msi_wt_uom,
msi.volume_uom_code msi_v_uom,
pc.fill_percent fill
FROM wsh_packed_containers pc,
mtl_system_items msi
WHERE pc.delivery_id = p_del_id
AND msi.inventory_item_id = pc.container_inventory_item_id
AND msi.organization_id = p_org_id;
UPDATE wsh_packed_containers
SET gross_weight = l_gross_wt,
net_weight = l_net_wt
WHERE container_id = container.id
AND delivery_id = x_del_id;
UPDATE wsh_packed_containers
SET volume = l_volume
WHERE container_id = container.id
AND delivery_id = x_del_id;
UPDATE wsh_packed_containers
SET fill_percent = l_fill_percent
WHERE container_id = container.id
AND delivery_id = x_del_id;