[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT status_id,
status_code
FROM mtl_material_statuses
WHERE locator_control = 1
AND enabled_flag = 1;
SELECT status_id, status_code
FROM mtl_material_statuses
WHERE zone_control = 1
AND enabled_flag = 1;
SELECT meaning BULK COLLECT
INTO g_locator_types
FROM mfg_lookups
WHERE lookup_type = 'MTL_LOCATOR_TYPES'
ORDER BY lookup_code;
SELECT meaning BULK COLLECT
INTO g_subinventory_types
FROM mfg_lookups
WHERE lookup_type = 'MTL_SUB_TYPES'
ORDER BY lookup_code;
DELETE FROM wms_zone_locators_temp;
DEBUG ('deleted from wms_zone_locators_temp ' || SQL%ROWCOUNT,
l_module_name,
9
);
INSERT INTO wms_zone_locators_temp(
message
, message_id
, inventory_location_id
, locator_name
, subinventory_code
, picking_order
, dropping_order
, locator_status
, subinventory_status
, locator_status_code
, subinventory_status_code
, inventory_location_type
, subinventory_type
, locator_type_meaning
, subinventory_type_meaning
, organization_id) (
SELECT NULL
, TO_NUMBER(NULL) message_id
, wzl.inventory_location_id
, milk.concatenated_segments locator_name
, wzl.subinventory_code
, milk.picking_order
, milk.dropping_order
, milk.status_id locator_status
, msi.status_id subinventory_status
, mms1.status_code locator_status_code
, mms2.status_code subinventory_status_code
, milk.inventory_location_type
, msi.subinventory_type
, DECODE (milk.inventory_location_type,
1, g_locator_types (1),
2, g_locator_types (2),
3, g_locator_types (3),
4, g_locator_types (4),
5, g_locator_types (5),
6, g_locator_types (6),
7, g_locator_types (7),
-- Default value is Storage,i.e. 3
g_locator_types (3)
)
, DECODE (msi.subinventory_type,
1, g_subinventory_types (1),
2, g_subinventory_types (2),
-- Default value should be Storage..i.e 1
g_subinventory_types(1)
)
, p_org_id
FROM wms_zone_locators wzl,
mtl_item_locations_kfv milk,
mtl_secondary_inventories msi,
mtl_material_statuses mms1,
mtl_material_statuses mms2
WHERE wzl.zone_id = p_zone_id
AND wzl.organization_id = p_org_id
AND NVL(wzl.entire_sub_flag,'N') = 'N'
AND wzl.organization_id = msi.organization_id
AND wzl.subinventory_code = msi.secondary_inventory_name
AND wzl.organization_id = milk.organization_id
AND wzl.subinventory_code = milk.subinventory_code
AND wzl.inventory_location_id = milk.inventory_location_id
AND mms1.status_id(+) = milk.status_id
AND mms2.status_id(+) = msi.status_id
UNION
SELECT NULL
, TO_NUMBER(NULL) message_id
, wzl.inventory_location_id
, g_all_locators_message locator_name
, wzl.subinventory_code
, TO_NUMBER(NULL) picking_order
, TO_NUMBER(NULL) dropping_orders
, TO_NUMBER(NULL) locator_status
, msi.status_id subinventory_status
, NULL locator_status_code
, mms.status_code subinventory_status_code
, TO_NUMBER(NULL)
, msi.subinventory_type
, NULL
, DECODE (msi.subinventory_type,
1, g_subinventory_types (1),
2, g_subinventory_types (2),
-- The default value should be Storage, i.e. 1
g_subinventory_types (1)
)
, p_org_id
FROM wms_zone_locators wzl,
mtl_secondary_inventories msi,
mtl_material_statuses mms
WHERE wzl.zone_id = p_zone_id
AND wzl.organization_id = p_org_id
AND NVL(wzl.entire_sub_flag,'N') = 'Y'
AND wzl.organization_id = msi.organization_id
AND wzl.subinventory_code = msi.secondary_inventory_name
AND mms.status_id = msi.status_id
);
* inserts the locators into the table WMS_ZONE_LOCATORS_TEMP.
*
* @param p_fm_zone_id from_zone_id in the range. Will have a
* null value if the user doesnt choose a from_zone
* @param p_to_zone_id to_zone_id in the range. Can have a null
* value if the user doesnt choose a to_zone
* @param p_current_zone_id The zone_id of the current zone,
* for which more locators are being added
* @param p_fm_sub_code From Subinventory code
* @param p_to_sub_code To Subinventory Code
* @param p_fm_loc_id From Locator Id in a range of locators.
* Should contain a value only if either
* p_fm_sub_code or p_to_sub_code is populated.
* @param p_to_loc_id To Locator Id in a range of locators.
* Should contain a value only if either
* p_fm_sub_code or p_to_sub_code is populated.
* @param p_subinventory_status Status id of the subinventories
* @param p_locator_status Status id of the locators
* @param p_subinventory_type Subinventory Type
* @param p_locator_type Locator Type
* @param p_fm_picking_order Picking order of the Locators
* @param p_to_picking_order Picking order of the Locators
* @param p_fm_dropping_order Dropping order of the Locators
* @param p_to_dropping_order Dropping order of the Locators
* @param p_organization_id Organization identifier
**/
PROCEDURE add_locators_to_grid (
p_fm_zone_id IN NUMBER DEFAULT NULL,
p_to_zone_id IN NUMBER DEFAULT NULL,
p_current_zone_id IN NUMBER DEFAULT NULL,
p_fm_sub_code IN VARCHAR2 DEFAULT NULL,
p_to_sub_code IN VARCHAR2 DEFAULT NULL,
p_fm_loc_id IN NUMBER DEFAULT NULL,
p_to_loc_id IN NUMBER DEFAULT NULL,
p_subinventory_status IN NUMBER DEFAULT NULL,
p_locator_status IN NUMBER DEFAULT NULL,
p_subinventory_type IN NUMBER DEFAULT NULL,
p_locator_type IN NUMBER DEFAULT NULL,
p_fm_picking_order IN NUMBER DEFAULT NULL,
p_to_picking_order IN NUMBER DEFAULT NULL,
p_fm_dropping_order IN NUMBER DEFAULT NULL,
p_to_dropping_order IN NUMBER DEFAULT NULL,
p_organization_id IN NUMBER,
p_mode IN NUMBER DEFAULT NULL) IS
l_insert_str VARCHAR2 (2000);
l_select_str VARCHAR2 (2000);
DELETE wms_zone_locators_temp;
SELECT concatenated_segments
INTO l_fm_loc_name
FROM mtl_item_locations_kfv
WHERE inventory_location_id = p_fm_loc_id
AND organization_id = p_organization_id;
SELECT concatenated_segments
INTO l_to_loc_name
FROM mtl_item_locations_kfv
WHERE inventory_location_id = p_to_loc_id
AND organization_id = p_organization_id;
l_insert_str :=
'INSERT INTO wms_zone_locators_temp(
message,
message_id,
inventory_location_id,
locator_name,
subinventory_code,
picking_order,
dropping_order,
locator_status,
subinventory_status,
locator_status_code,
subinventory_status_code,
inventory_location_type,
subinventory_type,
locator_type_meaning,
subinventory_type_meaning,
organization_id)';
l_select_str :=
'(SELECT '''
|| l_jtf_message
|| ''','
|| l_jtf_message_id ||' , wzlv.inventory_location_id,
nvl(wzlv.locator_name,:all_locators),
wzlv.subinventory_code,
wzlv.picking_order,
wzlv.dropping_order,
wzlv.locator_status,
wzlv.subinventory_status,
mms1.status_code locator_status_code,
mms2.status_code subinventory_status_code,';
DEBUG ('10 l_select_str is ' || l_select_str, l_module_name, 9);
l_select_str :=
l_select_str
|| 'wzlv.inventory_location_type, wzlv.subinventory_type, ';
DEBUG ('20 l_insert_str is ' || l_select_str, ' add_locators_grid',
9);
l_select_str :=
l_select_str
|| 'decode(wzlv.inventory_location_type,1, '''
|| g_locator_types (1)
|| ''',2,'''
|| g_locator_types (2)
|| ''',3,'''
|| g_locator_types (3)
|| ''', 4, '''
|| g_locator_types (4)
|| ''', 5,'''
|| g_locator_types (5)
|| ''', 6,'''
|| g_locator_types (6)
|| ''', 7,'''
|| g_locator_types (7)
|| ''', '''
|| g_locator_types (3)
|| '''),';
DEBUG ('30 l_insert_str is ' || l_select_str, ' add_locators_grid',
9);
l_select_str :=
l_select_str
|| 'decode(wzlv.subinventory_type,1, '''
|| g_subinventory_types (1)
|| ''',2, '''
|| g_subinventory_types (2)
|| ''', '''
|| g_subinventory_types (1)
|| ''') , '||p_organization_id;
DEBUG ('40 l_select_str is ' || l_select_str, ' add_locators_grid',
9);
l_select_str :=
'(SELECT '''
|| l_jtf_message
|| ''','
|| l_jtf_message_id ||',
milk.inventory_location_id,
milk.concatenated_segments,
milk.subinventory_code,
milk.picking_order,
milk.dropping_order,
milk.status_id,
msi.status_id subinventory_status,
mms1.status_code locator_status_code,
mms2.status_code subinventory_status_code,';
DEBUG ('10 l_select_str is ' || l_select_str, ' add_locators_grid',
9);
l_select_str :=
l_select_str
|| 'inventory_location_type, subinventory_type, ';
DEBUG ('20 l_select_str is ' || l_select_str, ' add_locators_grid',
9);
l_select_str :=
l_select_str
|| 'decode(milk.inventory_location_type,1, '''
|| g_locator_types (1)
|| ''',2,'''
|| g_locator_types (2)
|| ''',3,'''
|| g_locator_types (3)
|| ''', 4, '''
|| g_locator_types (4)
|| ''', 5,'''
|| g_locator_types (5)
|| ''', 6,'''
|| g_locator_types (6)
|| ''', 7,'''
|| g_locator_types (7)
|| ''', '''
|| g_locator_types (3)
|| '''),';
DEBUG ('30 l_select_str is ' || l_select_str, ' add_locators_grid',
9);
l_select_str :=
l_select_str
|| 'decode(msi.subinventory_type,1, '''
|| g_subinventory_types (1)
|| ''',2, '''
|| g_subinventory_types (2)
|| ''', '''
|| g_subinventory_types (1)
|| ''') , '||p_organization_id;
DEBUG ('40 l_select_str is ' || l_select_str, ' add_locators_grid',
9);
/* Case 1 - When only a range of subinventories is selected. Locators
* field is null. Then We have to chose all the locators in these subs
* which are not already present in the current zone -This includes
* "All Locators" option also
*/
IF p_fm_loc_id IS NULL AND p_to_loc_id IS NULL
THEN
NULL;
|| ' (select locator_id from wms_zone_locators_all_v
where zone_id = :cur_zone_id) ';
l_select_str := l_select_str || l_from_str || l_where_str || ')';
DEBUG ('select string ' || l_select_str, l_module_name, 9);
l_query_str := l_insert_str || l_select_str;
* Contains code to insert records into wms_zones_b and
* wms_zones_tl
* @param x_return_status Return Status - Success, Error, Unexpected Error
* @param x_msg_data Contains any error messages added to the stack
* @param x_msg_count Contains the count of the messages added to the stack
* @param p_zone_id Zone_id
* @param p_zone_name Name of the new Zone
* @param p_description Description of the zone
* @param enabled_flag Flag to indicate whether the zone is enabled or not. '
Y' indicates that the zone is enabled.
'N' indicates that the zone is not enabled.
Any other value will be an error
* @param disable_date The date when the zone will be disabled.
This date cannot be less than the SYSDATE.
* @param p_organization_id Current Organization id
* @param p_attribute_category Attribute Category of the Zones Descriptive Flexfield
* @param p_attribute1 Attribute1
* @param p_attribute2 Attribute2
* @param p_attribute3 Attribute3
* @param p_attribute4 Attribute4
* @param p_attribute5 Attribute5
* @param p_attribute6 Attribute6
* @param p_attribute7 Attribute7
* @param p_attribute8 Attribute8
* @param p_attribute9 Attribute9
* @param p_attribute10 Attribute10
* @param p_attribute11 Attribute11
* @param p_attribute12 Attribute12
* @param p_attribute13 Attribute13
* @param p_attribute14 Attribute14
* @param p_attribute15 Attribute15
**/
PROCEDURE insert_wms_zones (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_zone_id IN NUMBER,
p_zone_name IN VARCHAR2,
p_description IN VARCHAR2,
p_enabled_flag IN VARCHAR2,
p_labor_enabled IN VARCHAR2,
p_disable_date IN DATE,
p_organization_id IN NUMBER,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER
) IS
l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
l_module_name VARCHAR2(20) := 'INSERT_WMS_ZONES';
DEBUG (' p_last_update_date==> ' || p_last_update_date,
l_module_name,
9
);
DEBUG (' p_last_update_login==> ' || p_last_update_login,
l_module_name,
9
);
DEBUG (' p_last_updated_by==> ' || p_last_updated_by,
l_module_name,
9
);
WMS_ZONES_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_ZONE_ID => p_zone_id,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_ATTRIBUTE1 => p_attribute1,
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15,
X_ORGANIZATION_ID => p_organization_id,
X_DISABLE_DATE => p_disable_date,
X_ENABLED_FLAG => p_enabled_flag,
X_LABOR_ENABLED => p_labor_enabled,
X_ZONE_NAME => p_zone_name,
X_DESCRIPTION => p_description,
X_CREATION_DATE => p_creation_date,
X_CREATED_BY => p_created_by,
X_LAST_UPDATE_DATE => p_last_update_date,
X_LAST_UPDATED_BY => p_last_updated_by,
X_LAST_UPDATE_LOGIN => p_last_update_login
);
DEBUG (' Inserted row with rowid ' || l_rowid,
l_module_name,
9
);
END insert_wms_zones;
* Contains code to update records in wms_zones_b and
* wms_zones_tl
* @param x_return_status Return Status - Success, Error, Unexpected Error
* @param x_msg_data Contains any error messages added to the stack
* @param x_msg_count Contains the count of the messages added to the stack
* @param p_zone_id Zone_id
* @param p_zone_name Name of the new Zone
* @param p_description Description of the zone
* @param enabled_flag Flag to indicate whether the zone is enabled or not. 'Y' indicates that the zone is enabled 'N' indicates that the zone is not enabled. Any other value will be an error
* @param disable_date The date when the zone will be disabled. This date cannot be less than the SYSDATE.
* @param p_organization_id Current Organization id
* @param p_attribute_category Attribute Category of the Zones Descriptive Flexfield
* @param p_attribute1 Attribute1
* @param p_attribute2 Attribute2
* @param p_attribute3 Attribute3
* @param p_attribute4 Attribute4
* @param p_attribute5 Attribute5
* @param p_attribute6 Attribute6
* @param p_attribute7 Attribute7
* @param p_attribute8 Attribute8
* @param p_attribute9 Attribute9
* @param p_attribute10 Attribute10
* @param p_attribute11 Attribute11
* @param p_attribute12 Attribute12
* @param p_attribute13 Attribute13
* @param p_attribute14 Attribute14
* @param p_attribute15 Attribute15
**/
PROCEDURE update_wms_zones (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_zone_id IN NUMBER,
p_zone_name IN VARCHAR2,
p_description IN VARCHAR2,
p_enabled_flag IN VARCHAR2,
p_labor_enabled IN VARCHAR2,
p_disable_date IN DATE,
p_organization_id IN NUMBER,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER
) IS
l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
l_module_name VARCHAR2(20) := 'UPDATE_WMS_ZONES';
DEBUG (' p_last_update_date==> ' || p_last_update_date,
l_module_name,
9
);
DEBUG (' p_last_update_login==> ' || p_last_update_login,
l_module_name,
9
);
DEBUG (' p_last_updated_by==> ' || p_last_updated_by,
l_module_name,
9
);
WMS_ZONES_PKG.UPDATE_ROW (
X_ZONE_ID => p_zone_id,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_ATTRIBUTE1 => p_attribute1,
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15,
X_ORGANIZATION_ID => p_organization_id,
X_DISABLE_DATE => p_disable_date,
X_ENABLED_FLAG => p_enabled_flag,
X_LABOR_ENABLED => p_labor_enabled,
X_ZONE_NAME => p_zone_name,
X_DESCRIPTION => p_description,
X_LAST_UPDATE_DATE => p_last_update_date,
X_LAST_UPDATED_BY => p_last_updated_by,
X_LAST_UPDATE_LOGIN => p_last_update_login
);
END update_wms_zones;
* corresponding record will be inserted into the table.
* If the value in p_zoneloc_messages_t is 1, the
* corresponding record will be deleted from the table.
* Else do nothing.
* @param p_zoneloc_rowid_t Table of records containing the rowids of all the records to be inserted or deleted.
* @param p_zoneloc_messages_t Indicates whether the corresponding record should be inserted or deleted.
If the value is 0, the corresponding record will be inserted into the table.
If the value is 1, the corresponding record will be deleted from the table.
Else do nothing.
**/
PROCEDURE save_sel_locators (
p_zoneloc_rowid_t IN wms_zones_pvt.zoneloc_rowid_t,
p_zone_id IN wms_zone_locators.zone_id%TYPE
) IS
l_progress_indicator VARCHAR2(10) := '0';
DELETE FROM wms_zone_locators
WHERE ROWID IN (
SELECT wzl.ROWID
FROM wms_zone_locators wzl,
wms_zone_locators_temp wzlt
WHERE wzl.zone_id = p_zone_id
AND wzlt.rowid = p_zoneloc_rowid_t(i)
AND wzlt.message_id = 1
AND wzl.organization_id = wzlt.organization_id
AND wzl.subinventory_code = wzlt.subinventory_code
AND NVL(wzlt.inventory_location_id, -999) = -999
);
DEBUG('Rows deleted before insert :'||SQL%ROWCOUNT, l_module_name, 9);
INSERT INTO wms_zone_locators (
organization_id
, zone_id
, inventory_location_id
, subinventory_code
, entire_sub_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
) (
SELECT wzlt.organization_id
, p_zone_id
, DECODE(wzlt.inventory_location_id,
-999
, NULL
, wzlt.inventory_location_id)
, wzlt.subinventory_code
, DECODE(wzlt.inventory_location_id,
-999
, 'Y'
, NULL
, 'Y'
, 'N')
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
FROM wms_zone_locators_temp wzlt
WHERE wzlt.message_id = 1
AND ROWID = p_zoneloc_rowid_t (i)
AND NOT EXISTS (
SELECT 1
FROM wms_zone_locators wzl
WHERE wzl.zone_id = p_zone_id
AND wzl.organization_id = wzlt.organization_id
AND wzl.subinventory_code = wzlt.subinventory_code
AND wzl.entire_sub_flag = 'Y'
)
AND NOT EXISTS (
SELECT 1
FROM wms_zone_locators wzl
WHERE wzl.zone_id = p_zone_id
AND wzl.organization_id = wzlt.organization_id
AND wzl.subinventory_code = wzlt.subinventory_code
AND NVL(wzl.entire_sub_flag,'N') = 'N'
AND wzl.inventory_location_id = wzlt.inventory_location_id
)
);
DEBUG('Rows inserted :'||SQL%ROWCOUNT, l_module_name, 9);
DELETE FROM wms_zone_locators
WHERE ROWID IN (
SELECT wzl.ROWID
FROM wms_zone_locators wzl,
wms_zone_locators_temp wzlt
WHERE wzl.zone_id = p_zone_id
AND wzlt.rowid = p_zoneloc_rowid_t(i)
AND wzlt.message_id = 2
AND wzl.organization_id = wzlt.organization_id
AND wzl.subinventory_code = wzlt.subinventory_code
AND ( ( wzlt.inventory_location_id IS NULL ) OR
( wzl.inventory_location_id = wzlt.inventory_location_id ) ) );
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
INSERT INTO wms_zone_locators
(organization_id, zone_id, inventory_location_id,
subinventory_code, entire_sub_flag, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login)
(SELECT p_org_id, p_zone_id, inventory_location_id,
subinventory_code,
DECODE (inventory_location_id, -999, 'Y', 'N'), SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
fnd_global.user_id
FROM wms_zone_locators_temp
WHERE MESSAGE_ID = 1 );
DELETE FROM wms_zone_locators
WHERE inventory_location_id =
(SELECT inventory_location_id
FROM wms_zone_locators_temp
WHERE MESSAGE_id = 2)
AND zone_id = p_zone_id;
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER
) IS
l_module_name CONSTANT VARCHAR2(30) := 'LOCK_ROW';
SELECT *
FROM WMS_ZONES_VL
WHERE ZONE_ID = P_ZONE_ID
FOR UPDATE OF ZONE_ID NOWAIT;
( (rec1.last_updated_by = p_last_updated_by) OR
(rec1.last_updated_by IS NULL AND p_last_updated_by IS NULL) ) AND
( (rec1.last_update_date = p_last_update_date) OR
(rec1.last_update_date IS NULL AND p_last_update_date IS NULL) ) AND
( (rec1.last_update_login = p_last_update_login) OR
(rec1.last_update_login IS NULL AND p_last_update_login IS NULL) ) AND
( (rec1.attribute_category = p_attribute_category) OR
(rec1.attribute_category IS NULL AND p_attribute_category IS NULL) ) AND
( (rec1.attribute1 = p_attribute1) OR
(rec1.attribute1 IS NULL AND p_attribute1 IS NULL) ) AND
( (rec1.attribute2 = p_attribute2) OR
(rec1.attribute2 IS NULL AND p_attribute2 IS NULL) ) AND
( (rec1.attribute3 = p_attribute3) OR
(rec1.attribute3 IS NULL AND p_attribute3 IS NULL) ) AND
( (rec1.attribute4 = p_attribute4) OR
(rec1.attribute4 IS NULL AND p_attribute4 IS NULL) ) AND
( (rec1.attribute5 = p_attribute5) OR
(rec1.attribute5 IS NULL AND p_attribute5 IS NULL) ) AND
( (rec1.attribute6 = p_attribute6) OR
(rec1.attribute6 IS NULL AND p_attribute6 IS NULL) ) AND
( (rec1.attribute7 = p_attribute7) OR
(rec1.attribute7 IS NULL AND p_attribute7 IS NULL) ) AND
( (rec1.attribute8 = p_attribute8) OR
(rec1.attribute8 IS NULL AND p_attribute8 IS NULL) ) AND
( (rec1.attribute9 = p_attribute9) OR
(rec1.attribute9 IS NULL AND p_attribute9 IS NULL) ) AND
( (rec1.attribute10 = p_attribute10) OR
(rec1.attribute10 IS NULL AND p_attribute10 IS NULL) ) AND
( (rec1.attribute11 = p_attribute11) OR
(rec1.attribute11 IS NULL AND p_attribute11 IS NULL) ) AND
( (rec1.attribute12 = p_attribute12) OR
(rec1.attribute12 IS NULL AND p_attribute12 IS NULL) ) AND
( (rec1.attribute13 = p_attribute13) OR
(rec1.attribute13 IS NULL AND p_attribute13 IS NULL) ) AND
( (rec1.attribute14 = p_attribute14) OR
(rec1.attribute14 IS NULL AND p_attribute14 IS NULL) ) AND
( (rec1.attribute15 = p_attribute15) OR
(rec1.attribute15 IS NULL AND p_attribute15 IS NULL) )
) THEN
NULL;
fnd_message.set_name('INV','OE_LOCK_ROW_DELETED');
* @param p_last_update_date WHO column
* @param p_last_updated_by WHO column
* @param p_last_update_login WHO column
*
**/
PROCEDURE validate_row(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_zone_id IN NUMBER,
p_zone_name IN VARCHAR2,
p_description IN VARCHAR2,
p_enabled_flag IN VARCHAR2,
p_disable_date IN DATE,
p_organization_id IN NUMBER,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER
) IS
l_progress_indicator VARCHAR2(10) := '0';
SELECT 1
FROM wms_zones_b
WHERE zone_id = p_zone_id;
SELECT 1
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT 1
FROM wms_zones_vl
WHERE zone_name = p_zone_name
AND organization_id = p_organization_id;
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER
) IS
l_module_name VARCHAR2(20) := 'ADD_LOCATORS';
DEBUG (' p_last_update_date==> ' || p_last_update_date,
l_module_name, 9);
DEBUG (' p_last_update_login==> ' || p_last_update_login,
l_module_name, 9);
DEBUG (' p_last_updated_by==> ' || p_last_updated_by,
l_module_name, 9);
DELETE wms_zone_locators
WHERE zone_id = p_zone_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(p_entire_sub_flag, 'N') = 'Y';
DEBUG('Records deleted :'||SQL%ROWCOUNT, l_module_name,0);
INSERT INTO wms_zone_locators(
organization_id
, zone_id
, inventory_location_id
, subinventory_code
, entire_sub_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
)
(
SELECT p_organization_id
, p_zone_id
, DECODE(p_entire_sub_flag, 'Y', NULL, p_locator_id)
, p_subinventory_code
, p_entire_sub_flag
, p_last_update_date
, p_last_updated_by
, p_creation_date
, p_created_by
, p_last_update_login
FROM dual
WHERE NOT EXISTS (
SELECT 1
FROM wms_zone_locators
WHERE zone_id = p_zone_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND entire_sub_flag = 'Y'
)
AND NOT EXISTS (
SELECT 1
FROM wms_zone_locators
WHERE zone_id = p_zone_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND NVL(entire_sub_flag,'N') = 'N'
AND inventory_location_id = p_locator_id
)
);
DEBUG('Records inserted :'||SQL%ROWCOUNT, l_module_name,0);
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER
) IS
l_module_name VARCHAR2(20) := 'VALIDATE_LOCATORS';
SELECT 1
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT 1
FROM wms_zones_b
WHERE zone_id = p_zone_id;
SELECT 1
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT 1
FROM mtl_item_locations
WHERE inventory_location_id = p_locator_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code;
DEBUG (' p_last_update_date==> ' || p_last_update_date,
l_module_name, 9);
DEBUG (' p_last_update_login==> ' || p_last_update_login,
l_module_name, 9);
DEBUG (' p_last_updated_by==> ' || p_last_updated_by,
l_module_name, 9);
PROCEDURE delete_locators(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_zone_id IN NUMBER,
p_organization_id IN NUMBER,
p_subinventory_code IN VARCHAR,
p_locator_id IN NUMBER,
p_entire_sub_flag IN VARCHAR
) IS
l_module_name VARCHAR2(20) := 'DELETE_LOCATORS';
DELETE wms_zone_locators
WHERE zone_id = p_zone_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND ( (p_entire_sub_flag = 'Y') OR (
nvl(p_entire_sub_flag,'N') = 'N' AND
inventory_location_id = p_locator_id ) );
DEBUG('Deleted Rows : '||SQL%ROWCOUNT, l_module_name, 9);
END delete_locators;