[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT object_id, object_type
INTO x_object_id, x_object_type
FROM mtl_object_numbers_v
WHERE object_number = p_object_number
AND inventory_item_id = p_inventory_item_id
AND object_type = 2
AND organization_id = NVL(p_organization_id, organization_id);
* Insert a new Genealogy record connecting a child and it's parent
* Due to AK Navigator considerations, to show 'Child Genealogy', all objects
* should have a record connecting them with a parent. Similary, to show
* 'Parent-Genealogy' all objects should have a record connecting them
* to a child.
*/
PROCEDURE gen_insert(
p_rowid IN OUT NOCOPY VARCHAR2
, p_item_id IN NUMBER
, p_object_num IN VARCHAR2
, p_parent_item_id IN NUMBER
, p_parent_object_num IN VARCHAR2
, p_origin_txn_id IN NUMBER
, p_org_id IN NUMBER := NULL
) IS
l_trx_date DATE;
SELECT ROWID
FROM mtl_object_genealogy
WHERE object_id = l_obj_id
AND parent_object_id = l_parent_obj_id;
INSERT INTO mtl_object_genealogy
(
object_id
, object_type
, parent_object_type
, parent_object_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, start_date_active
, genealogy_origin
, origin_txn_id
, genealogy_type
)
VALUES (
l_obj_id
, l_obj_type
, l_parent_obj_type
, l_parent_obj_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_trx_date
, l_genealogy_origin
, p_origin_txn_id
, l_genealogy_type
);
END gen_insert;
PROCEDURE gen_insert(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_item_id IN NUMBER
, p_object_num IN VARCHAR2
, p_parent_item_id IN NUMBER
, p_parent_object_num IN VARCHAR2
, p_origin_txn_id IN NUMBER
, p_org_id IN NUMBER
) IS
l_trx_date DATE;
SELECT ROWID
FROM mtl_object_genealogy
WHERE object_id = l_obj_id
AND parent_object_id = l_parent_obj_id;
SELECT serial_number_control_code INTO l_parent_ser_ctrl
FROM mtl_system_items
WHERE inventory_item_id = p_parent_item_id
AND organization_id = p_org_id;
inv_log_util.trace('Parent not Serial Controlled','INV_OG_UTIL_PKG.GET_INSERT',3);
INSERT INTO mtl_object_genealogy
(
object_id
, object_type
, parent_object_type
, parent_object_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, start_date_active
, genealogy_origin
, origin_txn_id
, genealogy_type
)
VALUES (
l_obj_id
, l_obj_type
, l_parent_obj_type
, l_parent_obj_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_trx_date
, l_genealogy_origin
, p_origin_txn_id
, l_genealogy_type
);
fnd_message.set_name(application=> 'INV',NAME => 'INV_INSERT_ERROR');
END gen_insert;
/** added the procedure gen_update for the 'Serial Tracking in WIP Project.
This updates the mtl_object_genealogy and mtl_serial_numbers tables
when a serialized component is returned to stores from a WIP job.
The genealogy between the parent and the child serials should be disabled
whenever a component return transaction is performed. */
PROCEDURE gen_update(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_item_id IN NUMBER
, p_sernum IN VARCHAR2
, p_parent_sernum IN VARCHAR2
, p_org_id IN NUMBER
) IS
l_object_id NUMBER;
SELECT gen_object_id INTO l_object_id
FROM mtl_serial_numbers
WHERE serial_number = p_sernum
AND parent_serial_number = p_parent_sernum
AND current_organization_id = p_org_id
AND inventory_item_id = p_item_id; --Bug # 2682600
SELECT gen_object_id into l_object_id
FROM mtl_serial_numbers
WHERE serial_number = p_sernum
AND current_organization_id = p_org_id
AND inventory_item_id = p_item_id; --Bug # 2682600
inv_trx_util_pub.trace(' no data found in gen_update','MTL_OG_UTIL_PKG');
inv_trx_util_pub.trace(' exception in gen_update','MTL_OG_UTIL_PKG');
UPDATE mtl_object_genealogy
SET end_date_active = SYSDATE
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = -1
,LAST_UPDATE_LOGIN = -1
WHERE object_id = l_object_id
AND END_date_active IS NULL
AND genealogy_type <> 5 ;
UPDATE mtl_serial_numbers
SET parent_serial_number = NULL
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = -1
,LAST_UPDATE_LOGIN = -1
WHERE gen_object_id = l_object_id;
inv_trx_util_pub.trace(' no data found while trying to update in gen_update','MTL_OG_UTIL_PKG');
inv_trx_util_pub.trace(' exception in gen_update when trying to update','MTL_OG_UTIL_PKG');
END gen_update;
/** Insert a record corresponding to an event into the MTL_OBJECT_EVENTS table
*/
PROCEDURE event_insert(
p_rowid IN OUT NOCOPY VARCHAR2
, p_item_id IN NUMBER
, p_object_number IN VARCHAR2
, p_trx_id IN NUMBER
, p_trx_date IN DATE
, p_trx_src_id IN NUMBER
, p_trx_actin_id IN NUMBER
, p_org_id IN NUMBER := NULL
) IS
l_gen_event_type NUMBER;
Removed the cursot C, to select rowid of the row just inserted into mtl_object_event
since it is un-necessary.
Also , now populating p_rowid (IN OUT parameter) with '0' to avoid any problems with NULL
calue in inltis.
p_rowid is not used anywhere */
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
INSERT INTO mtl_object_events
(
object_id
, genealogy_event_type
, genealogy_event_date
, transaction_id
, creation_date
, created_by
, last_update_date
, last_updated_by
)
VALUES (
l_object_id
, l_gen_event_type
, p_trx_date
, p_trx_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
);
END event_insert;