[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wlpn.parent_lpn_id, wlpn.outermost_lpn_id
INTO l_parent_lpn_id, l_outermost_lpn_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = p_lpn_id;
SELECT DISTINCT wlc.inventory_item_id,wlc.uom_code,wlc.revision,1
INTO l_lpn_item_id,l_uom_code,l_rev,l_is_standard
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.lpn_id = wlc.parent_lpn_id
AND wlc.organization_id = wlpn.organization_id;
select wlc.inventory_item_id,wlc.uom_code,wlc.revision,1
INTO l_lpn_item_id,l_uom_code,l_rev,l_is_standard
from WMS_LPN_CONTENTS WLC
where WLC.parent_lpn_id in (
select wlpn.lpn_id
from wms_license_plate_numbers wlpn
WHERE WLPN.PARENT_LPN_ID is NOT NULL
START WITH LPN_ID = p_lpn_id
CONNECT BY WLPN.PARENT_LPN_ID = PRIOR LPN_ID)
GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
SELECT Nvl(epc_gtin_serial,0) INTO l_cur_serial_num
FROM mtl_cross_references_b
WHERE CROSS_REFERENCE = To_char(p_gtin)
AND cross_reference_type = G_PROFILE_GTIN
AND inventory_item_id = p_item_id
AND uom_code = p_uom_code
AND nvl(revision_id, -99) = nvl(p_rev_id,-99);
UPDATE mtl_cross_references_b
SET epc_gtin_serial = l_new_serial
WHERE CROSS_REFERENCE = To_char(p_gtin)
AND cross_reference_type = G_PROFILE_GTIN
AND inventory_item_id = p_item_id
AND uom_code = p_uom_code
AND nvl(revision_id, -99) = nvl(p_rev_id,-99);
SELECT uom_code FROM mtl_uom_conversions_view mucv
WHERE mucv.inventory_item_id = p_item_id
AND mucv.organization_id = p_org_id
AND mucv.conversion_rate = p_total_qty
AND Nvl(mucv.uom_code,'@@@') = Nvl(p_primary_uom,Nvl(mucv.uom_code,'@@@'));
SELECT 1, To_number(mcr.cross_reference),mirb.revision_id INTO
l_found_gtin,l_gtin,l_rev_id
FROM mtl_cross_references MCR, mtl_item_revisions_b mirb --USING base TABLE FOR PERFORMANCE
WHERE mcr.cross_reference_type = G_PROFILE_GTIN
AND mcr.inventory_item_id = p_item_id
AND mcr.uom_code = l_mtl_uom.UOM_CODE
AND mcr.inventory_item_id = mirb.inventory_item_id
AND MIRB.revision = p_rev
AND mirb.revision_id = mcr.REVISION_ID
AND (( mcr.org_independent_flag = 'Y' AND
mcr.organization_id IS NULL AND
MIRB.organization_id = p_org_id) OR
(mcr.org_independent_flag = 'N' AND
mcr.organization_id = p_org_id AND
mcr.organization_id = mirb.organization_id))
AND ROWNUM < 2;
SELECT 1, To_number(mcr.cross_reference) INTO
l_found_gtin,l_gtin
FROM mtl_cross_references MCR
WHERE mcr.cross_reference_type = G_PROFILE_GTIN
AND mcr.inventory_item_id = p_item_id
AND MCR.revision_id is NULL
AND mcr.uom_code = l_mtl_uom.UOM_CODE
AND (( mcr.org_independent_flag = 'Y' AND mcr.organization_id IS NULL)
OR (mcr.org_independent_flag = 'N' AND mcr.organization_id = p_org_id))
AND ROWNUM<2;
SELECT DISTINCT mmtt.inventory_item_id, SUM(mmtt.primary_quantity), mmtt.revision
INTO l_lpn_item_id,l_total_qty,l_rev
FROM mtl_material_transactions_temp mmtt,
wms_license_plate_numbers wlpn
WHERE Nvl(mmtt.transfer_lpn_id,mmtt.content_lpn_id) = p_lpn_id
AND mmtt.organization_id = p_org_id
AND wlpn.lpn_id = Nvl(mmtt.transfer_lpn_id,mmtt.content_lpn_id)
AND wlpn.lpn_context = wms_container_pub.LPN_CONTEXT_PACKING
GROUP BY mmtt.inventory_item_id,mmtt.transaction_uom, mmtt.revision;
SELECT mmtt.inventory_item_id, SUM(mmtt.primary_quantity), mmtt.revision
INTO l_lpn_item_id,l_total_qty,l_rev
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.cartonization_id = p_lpn_id
AND mmtt.cartonization_id IS NOT NULL
AND mmtt.organization_id = p_org_id
GROUP BY mmtt.inventory_item_id,mmtt.transaction_uom, mmtt.revision;
SELECT SUM(wlc.primary_quantity),wlc.inventory_item_id,wlc.uom_code,wlc.revision --UOM CODE TO AVOID FUTURE
--support OF HAVING multiple lines FOR same lpn based ON ui UOM
--IT SHOULD FAIL TO USE GTIN FOR EPC GENERATION FOR MULTIPLE GTIN IN LPN
INTO l_total_qty, l_lpn_item_id, l_uom_code,L_rev
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.lpn_id = wlc.parent_lpn_id
AND wlc.organization_id = p_org_id
AND wlc.organization_id = wlpn.organization_id
GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
SELECT SUM(wlc.primary_quantity),wlc.inventory_item_id,wlc.uom_code,wlc.revision
INTO l_total_qty,l_lpn_item_id,l_uom_code,l_rev
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn1, wms_license_plate_numbers wlpn2
WHERE wlpn1.lpn_id = p_lpn_id
and wlpn1.parent_lpn_id = wlpn2.outermost_lpn_id
AND wlpn2.lpn_id = wlc.parent_lpn_id
AND wlpn2.lpn_id <> wlpn1.parent_lpn_id --to avoid content of Pallet
AND wlc.organization_id = p_org_id
aND wlc.organization_id = wlpn1.organization_id
and wlc.organization_id = wlpn2.organization_id
GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
SELECT DISTINCT wlc.inventory_item_id, SUM(wlc.primary_quantity),wlc.uom_code,wlc.revision
INTO l_lpn_item_id,l_total_qty,l_uom_code,l_rev
FROM wms_license_plate_numbers wlpn,
wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn_id
AND wlc.organization_id = p_org_id
AND wlpn.lpn_id = wlc.parent_lpn_id
AND wlpn.LPN_CONTEXT = wms_container_pub.LPN_CONTEXT_WIP
AND wlc.organization_id = wlpn.organization_id
GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
SELECT SUM(wlc.primary_quantity),wlc.inventory_item_id,wlc.uom_code,wlc.revision --UOM CODE TO AVOID FUTURE
--support OF HAVING multiple lines FOR same lpn based ON ui UOM
--IT SHOULD FAIL TO USE GTIN FOR EPC GENERATION FOR MULTIPLE GTIN IN LPN
INTO l_total_qty, l_lpn_item_id, l_uom_code,L_rev
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.lpn_id = wlc.parent_lpn_id
AND wlc.organization_id = p_org_id
AND wlc.organization_id = wlpn.organization_id
GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
select sum(wlc.primary_quantity), wlc.inventory_item_id,wlc.uom_code, WLC.revision
INTO l_total_qty, l_lpn_item_id, l_uom_code,l_rev
from WMS_LPN_CONTENTS WLC
where WLC.parent_lpn_id in (
select wlpn.lpn_id
from wms_license_plate_numbers wlpn
where WLPN.PARENT_LPN_ID is NOT NULL
START WITH LPN_ID = p_lpn_id
CONNECT BY WLPN.PARENT_LPN_ID = PRIOR LPN_ID)
GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
SELECT license_plate_number INTO l_sscc FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id
AND organization_id = p_org_id;
IF p_action = 'UPDATE' THEN
UPDATE wms_epc
SET epc = p_gen_epc,
cross_ref_type = p_cross_ref_type,
group_id = p_group_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
epc_rule_type_id = p_epc_rule_type_id,
sscc = P_sscc,
gtin = P_gtin,
gtin_serial = NULL,
inventory_item_id = NULL,
serial_number = NULL,
filter_object_type = p_filter_value,
status = 'LABEL_PRINTED',
status_code = 'S'
WHERE lpn_id = p_lpn_id;
ELSIF p_action = 'INSERT' THEN
--INSERT NEW EPC RECORD
INSERT INTO wms_epc( group_id,
cross_ref_type,
epc_rule_type_id,
lpn_id,
serial_number,
inventory_item_id,
gtin_serial,
gtin,
sscc,
epc,
filter_object_type,
status_code,
status,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
epc_id,
epc_rule_id
) VALUES (P_group_id,
p_cross_ref_type,
P_epc_rule_type_id,
p_lpn_id,
NULL,--p_serial_number,
NULL,--p_ITEM_ID,
NULL,--p_GTIN_SERIAL,
P_gtin,
P_sscc,
P_gen_epc,
p_filter_value,
'S',
'LABEL_PRINTED',
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.user_id,
NULL, --epc_id NOT used post R12
NULL); --epc_rule_id NOT used post R12
ELSIF p_action = 'DELETE' THEN
-- Delete the existing cross -reference
DELETE FROM wms_epc WHERE lpn_id = p_lpn_id;
IF p_action = 'UPDATE' THEN
UPDATE wms_epc
SET epc = p_gen_epc,
cross_ref_type = p_cross_ref_type,
group_id = p_group_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
epc_rule_type_id = p_epc_rule_type_id,
sscc = NULL,-- No other value possible
gtin = P_gtin,
gtin_serial = NULL,
lpn_id = NULL,
filter_object_type = p_filter_value,
status = 'LABEL_PRINTED',
status_code = 'S'
WHERE inventory_item_id = p_item_id
AND serial_number = p_serial_number;
ELSIF p_action = 'INSERT' THEN
--INSERT NEW EPC RECORD
INSERT INTO wms_epc( group_id,
cross_ref_type,
epc_rule_type_id,
lpn_id,
serial_number,
inventory_item_id,
gtin_serial,
gtin,
sscc,
epc,
filter_object_type,
status_code,
status,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
epc_id,
epc_rule_id
) VALUES (P_group_id,
p_cross_ref_type,
P_epc_rule_type_id,
NULL,-- lpn_id
p_serial_number,
p_item_id,
NULL,--p_gtin_serial,
P_gtin,
NULL,--p_sscc
P_gen_epc,
p_filter_value,
'S',
'LABEL_PRINTED',
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.user_id,
NULL, --epc_id NOT used post R12
NULL); --epc_rule_id NOT used post R12
ELSIF p_action = 'DELETE' THEN
-- Delete the existing cross -reference
DELETE FROM wms_epc
WHERE inventory_item_id = p_item_id
AND serial_number = p_serial_number;
IF p_action = 'UPDATE' THEN
UPDATE wms_epc
SET epc = p_gen_epc,
cross_ref_type = p_cross_ref_type,
group_id = p_group_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
epc_rule_type_id = p_epc_rule_type_id,
sscc = NULL, --NO other value possible in this case
serial_number = NULL,
inventory_item_id = NULL,
lpn_id = NULL,
filter_object_type = p_filter_value,
status = 'LABEL_PRINTED',
status_code = 'S'
WHERE GTIN = p_gtin
AND GTIN_serial = p_gtin_serial;
ELSIF p_action = 'INSERT' THEN
--INSERT NEW EPC RECORD
INSERT INTO wms_epc( group_id,
cross_ref_type,
epc_rule_type_id,
lpn_id,
serial_number,
inventory_item_id,
gtin_serial,
gtin,
sscc,
epc,
filter_object_type,
status_code,
status,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
epc_id,
epc_rule_id
) VALUES (P_group_id,
p_cross_ref_type,
P_epc_rule_type_id,
NULL ,-- p_lpn_id
NULL, --p_serial_number
NULL, --p_item_id,
p_GTIN_serial,
P_gtin,
NULL, --p_sscc,
P_gen_epc,
p_filter_value,
'S',
'LABEL_PRINTED',
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.user_id,
NULL, --epc_id NOT used post R12
NULL); --epc_rule_id NOT used post R12
ELSIF p_action = 'DELETE' THEN
-- Delete the existing cross -reference
DELETE FROM wms_epc
WHERE GTIN = p_gtin
AND GTIN_serial = p_gtin_serial;
select type_id, type_name, nvl(partition_value,0) partition_value
,category_id
from mgd_idencoding_type ;
SELECT epc_rule_type,filter_value,generate_epc,epc_category_id
INTO l_epc_rule_type,l_filter_value,l_regenerate_flag, l_epc_category_id
FROM wms_label_formats
WHERE label_format_id = p_label_format_id
AND Nvl(label_ENTITY_type,0) =0; --label format and NOT label-set
SELECT lpn_id,serial_number,inventory_item_id,revision
INTO l_lpn_id,l_serial_number,l_item_id, l_rev
FROM wms_label_requests
WHERE label_request_id = p_label_request_id;
SELECT wlpn.parent_lpn_id, wlpn.outermost_lpn_id, we.epc
INTO l_parent_lpn_id, l_outermost_lpn_id, l_epc
FROM wms_license_plate_numbers wlpn, wms_epc we
WHERE wlpn.lpn_id = l_lpn_id
AND wlpn.lpn_id = we.lpn_id(+)
AND ((we.epc is NOT NULL and Nvl(we.cross_ref_type,1) = 1)
or (we.epc is NULL )) ;
-- populated AND old data needs to be updated
EXCEPTION
WHEN no_data_found THEN
IF l_debug = 1 THEN
trace('NO DATA found for the LPN');
SELECT we.epc INTO l_epc
FROM wms_epc we
WHERE INVENTORY_item_id = l_item_id
AND serial_number = l_serial_number
AND we.cross_ref_type = 2;
--and it needs to be inserted
l_regenerate_flag := 'Y'; -- override always
--Do not raise exception here as we want to delete
--old cross-reference RECORD FROM wms_epc for some
--CASES BELOW
END;
--Do not raise exception here as we want to delete
--old cross-reference RECORD FROM wms_epc for some
--CASES BELOW
END IF; --l_return_status = 'S for get_epc_gen_info()
--{{ EPC c/r already there: UPDATE LAST EPC with new EPC value }}
uptodate_wms_epc ( p_action => 'UPDATE',
p_group_id => p_group_id,
p_cross_ref_type => l_cross_ref_type,
p_EPC_rule_TYPE_id => l_EPC_rule_TYPE_id,
p_lpn_id => l_lpn_id,
p_item_id => l_item_id,
p_serial_number => l_serial_number,
p_gen_epc => l_gen_epc,
p_sscc => l_sscc,
p_gtin => l_gtin,
p_gtin_serial => l_gtin_serial,
p_filter_VALUE => l_filter_value,
x_return_status => L_RETURN_STATUS);
trace(' uptodate_wms_epc UPDATE: L_RETURN_STATUS:'||l_return_status);
-- Delete the existing cross -reference
uptodate_wms_epc ( p_action => 'DELETE',
p_group_id => p_group_id,
p_cross_ref_type => l_cross_ref_type,
p_EPC_rule_TYPE_id => l_EPC_rule_TYPE_id,
p_lpn_id => l_lpn_id,
p_item_id => l_item_id,
p_serial_number => l_serial_number,
p_gen_epc => l_gen_epc,
p_sscc => l_sscc,
p_gtin => l_gtin,
p_gtin_serial => l_gtin_serial,
p_filter_VALUE => l_filter_value,
x_return_status => L_RETURN_STATUS);
trace(' uptodate_wms_epc DELETE: L_RETURN_STATUS:'||l_return_status);
uptodate_wms_epc( p_action => 'INSERT',
p_group_id => p_group_id,
p_cross_ref_type => l_cross_ref_type,
p_EPC_rule_TYPE_id => l_EPC_rule_TYPE_id,
p_lpn_id => l_lpn_id,
p_item_id => l_item_id,
p_serial_number => l_serial_number,
p_gen_epc => l_gen_epc,
p_sscc => l_sscc,
p_gtin => l_gtin,
p_gtin_serial => l_gtin_serial,
p_filter_VALUE => l_filter_value,
x_return_status => L_RETURN_STATUS);
trace(' uptodate_wms_epc INSERT: L_RETURN_STATUS:'||l_return_status);
INSERT INTO wms_epc( group_id,
cross_ref_type,
epc_rule_type_id,
lpn_id,
serial_number,
inventory_item_id,
gtin_serial,
gtin,
sscc,
epc,
filter_object_type,
status_code,
status,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
epc_id,
epc_rule_id
) VALUES (P_group_id,
p_cross_ref_type,
-1, -- epc_rule_type_id:populated -1 FOR outside party
p_lpn_id,
p_serial_number,
p_ITEM_ID,
p_GTIN_SERIAL,
P_gtin,
NULL,
P_epc,
null,--filter_object_type
'S',
'IMPORTED',
Sysdate,
fnd_global.user_id,
Sysdate,
fnd_global.user_id,
fnd_global.user_id,
NULL, --epc_id NOT used post R12
NULL); --epc_rule_id NOT used post R12
SELECT 1
INTO l_is_epc_enabled
FROM mtl_parameters
WHERE organization_id = p_org_id
AND Nvl(epc_generation_enabled_flag, 'N') = 'Y';