[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Aug 10, 2000. mrana Value for 4th column in insert |
| object_genealogy should be l_parent_id |
+-------------------------------------------------------------------------------*/
--
-- FILENAME
--
-- INVPVCGB.pls
--
-- DESCRIPTION
-- Body of package INV_genealogy_PUB
--
-- NOTES
--
-- HISTORY
-- 10-MAY-00 Created sthamman
-- 23-May-00 Modified sthamman
-- Introduced the following parameters
-- 1. p_object_id
-- 2. p_inventory_item_id
-- 3. p_org_id
-- 4. p_parent_object_id
-- 5. p_parent_inventory_item_id
-- 6. p_parent_org_id
--
/* Genealogy_object_types :
* ----------------
* 1 Lot
* 2 Serial
* 3 External
* 4 Container
* 5 Job
*
* Genealogy Type
* ----------------
* 1 Assembly
* 2 Lot Split
* 3 Lot merge
* 4 Sublot
* 5 Assets (used by EAM)
*
* Genealogy Origin
* ----------------
* 1 WIP
* 2 Transaction
* 3 Manual
* */
-- Global constant holding the package name
g_pkg_name CONSTANT VARCHAR2(30) := 'INV_genealogy_PUB';
, p_update_txn_id IN NUMBER
, p_object_type2 IN OUT NOCOPY NUMBER
, p_object_id2 IN OUT NOCOPY NUMBER
, p_object_number2 IN VARCHAR2
, p_parent_object_type2 IN OUT NOCOPY NUMBER
, p_parent_object_id2 IN OUT NOCOPY NUMBER
, p_parent_object_number2 IN VARCHAR2
, p_child_lot_control_code IN NUMBER
, p_parent_lot_control_code IN NUMBER
, p_action IN VARCHAR2
, p_debug IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2) ;
between this function and the previous select statement
using a connect by clause. This is a fix for bug # 2287872
*/
FUNCTION genealogy_loop(
object_id IN NUMBER
, parent_object_id IN NUMBER
, start_date IN DATE
, end_date IN DATE
, object_table IN OUT NOCOPY object_id_tbl_t
)
RETURN NUMBER AS
l_dummy NUMBER := 0;
SELECT 1
INTO l_dummy
FROM DUAL
WHERE parent_object_id IN (SELECT mog.object_id
FROM mtl_object_genealogy mog
WHERE (end_date_active IS NULL OR end_date_active >= start_date)
AND parent_object_id = l_object_id
AND genealogy_type = 5);
SELECT 1
INTO l_dummy
FROM DUAL
WHERE parent_object_id IN (SELECT mog.object_id
FROM mtl_object_genealogy mog
WHERE (((start_date_active <= start_date)
AND (end_date_active IS NULL
OR (end_date_active >= start_date)))
OR ((start_date_active >= start_date)
AND (start_date_active <= end_date)))
AND parent_object_id = l_object_id
AND genealogy_type = 5);
FOR object_id_rec IN (SELECT object_id
, start_date_active
, end_date_active
FROM mtl_object_genealogy
WHERE genealogy_type = 5
AND parent_object_id = l_object_id
AND (end_date_active IS NULL OR end_date_active >= start_date))
LOOP
l_dummy_char := 'N';
SELECT object_id_rec.object_id
, object_id_rec.start_date_active
, object_id_rec.end_date_active
INTO object_table(l_count).object_id
, object_table(l_count).start_date_active
, object_table(l_count).end_date_active
FROM DUAL;
FOR object_id_rec IN (SELECT object_id
, start_date_active
, end_date_active
FROM mtl_object_genealogy
WHERE genealogy_type = 5
AND parent_object_id = l_object_id
AND (((start_date_active <= start_date)
AND (end_date_active IS NULL
OR (end_date_active >= start_date)))
OR ((start_date_active >= start_date)
AND (start_date_active <= end_date))))
LOOP
l_dummy_char := 'N';
SELECT object_id_rec.object_id
, object_id_rec.start_date_active
, object_id_rec.end_date_active
INTO object_table(l_count + 1).object_id
, object_table(l_count + 1).start_date_active
, object_table(l_count + 1).end_date_active
FROM DUAL;
PROCEDURE insert_genealogy(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
, p_commit IN VARCHAR2 := gen_fnd_g_false
, p_validation_level IN NUMBER := gen_fnd_valid_level_full
, p_object_type IN NUMBER
, p_parent_object_type IN NUMBER := NULL
, p_object_id IN NUMBER := NULL
, p_object_number IN VARCHAR2 := NULL
, p_inventory_item_id IN NUMBER := NULL
, p_org_id IN NUMBER := NULL
, p_parent_object_id IN NUMBER := NULL
, p_parent_object_number IN VARCHAR2 := NULL
, p_parent_inventory_item_id IN NUMBER := NULL
, p_parent_org_id IN NUMBER := NULL
, p_genealogy_origin IN NUMBER := NULL
, p_genealogy_type IN NUMBER := NULL
, p_start_date_active IN DATE := SYSDATE
, p_end_date_active IN DATE := NULL
, p_origin_txn_id IN NUMBER := NULL
, p_update_txn_id IN NUMBER := NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_object_type2 IN NUMBER := NULL -- R12 Genealogy Enhancements
, p_object_id2 IN NUMBER := NULL -- R12 Genealogy Enhancements
, p_object_number2 IN VARCHAR2 := NULL -- R12 Genealogy Enhancements
, p_parent_object_type2 IN NUMBER := NULL -- R12 Genealogy Enhancements
, p_parent_object_id2 IN NUMBER := NULL -- R12 Genealogy Enhancements
, p_parent_object_number2 IN VARCHAR2 := NULL -- R12 Genealogy Enhancements
, p_child_lot_control_code IN NUMBER := NULL -- R12 Genealogy Enhancements
, p_parent_lot_control_code IN NUMBER := NULL -- R12 Genealogy Enhancements
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'insert_genealogy';
SAVEPOINT save_insert_genealogy;
g_mod_name := 'Insert_Genealogy';
mydebug('p_update_txn_id: ' || p_update_txn_id );
l_action := 'INSERT';
, p_update_txn_id => p_update_txn_id
, p_object_type2 => l_object_type2
, p_object_id2 => l_object_id2
, p_object_number2 => p_object_number2
, p_parent_object_type2 => l_parent_object_type2
, p_parent_object_id2 => l_parent_object_id2
, p_parent_object_number2 => p_parent_object_number2
, p_child_lot_control_code => p_child_lot_control_code
, p_parent_lot_control_code => p_parent_lot_control_code
, p_action => l_action
, p_debug => l_debug
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
g_mod_name := 'Insert_Genealogy';
'an expected exception now..before inserting into genealogy }}' );
'an unexpected exception now..before inserting into genealogy }}');
SELECT msn.current_organization_id
INTO l_org_id
FROM mtl_serial_numbers msn
WHERE msn.gen_object_id = l_object_id;
SELECT msn.current_organization_id
INTO l_parent_org_id
FROM mtl_serial_numbers msn
WHERE msn.gen_object_id = l_parent_object_id;
SELECT msn.inventory_item_id
INTO l_parent_inventory_item_id
FROM mtl_serial_numbers msn
WHERE msn.gen_object_id = l_parent_object_id;
SELECT msi.eam_item_type
INTO l_parent_item_type
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_parent_inventory_item_id
AND msi.organization_id = l_parent_org_id;
SELECT msn.inventory_item_id
INTO l_inventory_item_id
FROM mtl_serial_numbers msn
WHERE msn.gen_object_id = l_object_id;
SELECT msi.eam_item_type
INTO l_child_item_type
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_inventory_item_id
AND msi.organization_id = l_org_id;
SELECT serial_number
INTO l_serial_number
FROM mtl_serial_numbers
WHERE gen_object_id = l_object_id;
SELECT mog.start_date_active start_date_active
, mog.end_date_active end_date_active
, msn.serial_number parent_serial_number
, msn.inventory_item_id parent_inventory_item_id
FROM mtl_object_genealogy mog, mtl_serial_numbers msn
WHERE mog.object_id = l_object_id
AND msn.gen_object_id = mog.parent_object_id
AND mog.genealogy_type = 5;
select instance_number into l_instance_number
from csi_item_instances
where serial_number = l_serial_number
and inventory_item_id = l_inventory_item_id
and last_vld_organization_id = l_org_id;
select instance_number into l_parent_instance_number
from csi_item_instances
where serial_number = i.parent_serial_number
and inventory_item_id = i.parent_inventory_item_id
and last_vld_organization_id = l_parent_org_id;
select instance_number into l_instance_number
from csi_item_instances
where serial_number = l_serial_number
and inventory_item_id = l_inventory_item_id
and last_vld_organization_id = l_org_id;
select instance_number into l_parent_instance_number
from csi_item_instances
where serial_number = i.parent_serial_number
and inventory_item_id = i.parent_inventory_item_id
and last_vld_organization_id = l_parent_org_id;
select instance_number into l_instance_number
from csi_item_instances
where serial_number = l_serial_number
and inventory_item_id = l_inventory_item_id
and last_vld_organization_id = l_org_id;
select instance_number into l_parent_instance_number
from csi_item_instances
where serial_number = i.parent_serial_number
and inventory_item_id = i.parent_inventory_item_id
and last_vld_organization_id = l_parent_org_id;
select instance_number into l_instance_number
from csi_item_instances
where serial_number = l_serial_number
and inventory_item_id = l_inventory_item_id
and last_vld_organization_id = l_org_id;
select instance_number into l_parent_instance_number
from csi_item_instances
where serial_number = i.parent_serial_number
and inventory_item_id = i.parent_inventory_item_id
and last_vld_organization_id = l_parent_org_id;
SELECT l_object_id
INTO l_object_table(1).object_id
FROM DUAL;
SELECT serial_number, inventory_item_id
INTO l_parent_serial_number, l_parent_inventory_item_id
FROM mtl_serial_numbers msn
WHERE gen_object_id = l_parent_object_id;
select instance_number into l_instance_number
from csi_item_instances
where serial_number = l_serial_number
and inventory_item_id = l_inventory_item_id
and last_vld_organization_id = l_org_id;
select instance_number into l_parent_instance_number
from csi_item_instances
where serial_number = l_parent_serial_number
and inventory_item_id = l_parent_inventory_item_id
and last_vld_organization_id = l_parent_org_id;
/* Fix bug 2138294, in EAM, object is not inserted into genealogy table
Reason: l_dummy = 0 because there is existing parent/child relationship
Fix: For EAM, it is allowed to have existing parent/child relationship,
Added 'AND GENEALOGY_TYPE <> 5' to not include genealogy_type of 5 (Assets) */
/* bug 2712800 The genealogy was not getting built when the serial number
is completed the second time. Add the condition 'end_date_active is null'
so that the genealogy is built if the end_date_active is already marked.
For serials, end_date_active is marked with the sysdate once the serial
number is returned (Serial-Tracking in WIP)*/
l_dummy := -999;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_object_genealogy
WHERE object_id = l_object_id
AND object_id2 IS NULL -- added this for lot+serial controlled items
AND parent_object_id = l_parent_object_id
AND parent_object_id2 IS NULL
AND end_date_active IS NULL
AND genealogy_type <> 5;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_object_genealogy
WHERE object_id = l_object_id
AND object_id2 IS NULL -- added this for lot+serial controlled items
AND parent_object_id = l_parent_object_id
AND parent_object_id2 = l_parent_object_id2
AND end_date_active IS NULL
AND genealogy_type <> 5;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_object_genealogy
WHERE object_id = l_object_id
AND object_id2 = l_object_id2 -- added this for lot+serial controlled items
AND parent_object_id = l_parent_object_id
AND parent_object_id2 IS NULL
AND end_date_active IS NULL
AND genealogy_type <> 5;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_object_genealogy
WHERE object_id = l_object_id
AND object_id2 = l_object_id2 -- added this for lot+serial controlled items
AND parent_object_id = l_parent_object_id
AND parent_object_id2 = l_parent_object_id2
AND end_date_active IS NULL
AND genealogy_type <> 5;
'{{ inserted in mtl_object_genealogy for the given l_object_id, l_object_id2 }} ' ||
'{{ and l_parent_object_id, l_parent_object_id2 combination }}' );
INSERT INTO mtl_object_genealogy
(
object_id
, object_type
, object_id2 -- R12 Genealogy Enhancements
, object_type2 -- R12 Genealogy Enhancements
, parent_object_type
, parent_object_id
, parent_object_type2 -- R12 Genealogy Enhancements
, parent_object_id2 -- R12 Genealogy Enhancements
, last_update_date
, last_updated_by
, creation_date
, created_by
, start_date_active
, end_date_active
, genealogy_origin
, origin_txn_id
, update_txn_id
, genealogy_type
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, request_id
, program_application_id
, program_id
, program_update_date
)
VALUES (
l_object_id
, p_object_type
, l_object_id2 -- R12Genealogy Enhancements
, l_object_type2 -- R12Genealogy Enhancements
, p_parent_object_type
, l_parent_object_id
, l_parent_object_type2 -- R12Genealogy Enhancements
, l_parent_object_id2 -- R12Genealogy Enhancements
, SYSDATE
, -1
, SYSDATE + 10
, fnd_global.user_id
, p_start_date_active
, p_end_date_active
, p_genealogy_origin
, p_origin_txn_id
, p_update_txn_id
, p_genealogy_type
, -1
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, fnd_global.conc_request_id
, fnd_global.prog_appl_id
, fnd_global.conc_program_id
, SYSDATE
);
IF (l_debug = 1) THEN mydebug( 'Inserted a New Record ' ); END IF;
ROLLBACK TO apiinsert_genealogy_apipub;
ROLLBACK TO apiinsert_genealogy_apipub;
ROLLBACK TO apiinsert_genealogy_apipub;
END insert_genealogy;
PROCEDURE update_genealogy(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
, p_commit IN VARCHAR2 := gen_fnd_g_false
, p_validation_level IN NUMBER := gen_fnd_valid_level_full
, p_object_type IN NUMBER
, p_object_id IN NUMBER := NULL
, p_object_number IN VARCHAR2 := NULL
, p_inventory_item_id IN NUMBER := NULL
, p_org_id IN NUMBER := NULL
, p_genealogy_origin IN NUMBER := NULL
, p_genealogy_type IN NUMBER := NULL
, p_end_date_active IN DATE := NULL
, p_update_txn_id IN NUMBER := NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_genealogy';
SAVEPOINT update_genealogy_pub;
g_mod_name := 'Update Genealogy';
SELECT COUNT(*)
INTO l_dummy
FROM mtl_lot_numbers
WHERE gen_object_id = p_object_id;
SELECT gen_object_id
INTO l_object_id
FROM mtl_lot_numbers
WHERE lot_number = p_object_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_serial_numbers
WHERE gen_object_id = p_object_id;
SELECT gen_object_id
INTO l_object_id
FROM mtl_serial_numbers
WHERE serial_number = p_object_number
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_org_id;
SELECT COUNT(*)
INTO l_dummy
FROM mfg_lookups
WHERE lookup_type = 'INV_GENEALOGY_ORIGIN'
AND lookup_code = p_genealogy_origin;
SELECT 'Y'
INTO l_dummy_char
FROM DUAL
WHERE EXISTS( SELECT *
FROM mtl_object_genealogy
WHERE genealogy_type = 5
AND object_id = l_object_id
AND end_date_active IS NULL);
SELECT start_date_active
INTO l_dummy_date
FROM mtl_object_genealogy
WHERE genealogy_type = 5
AND object_id = l_object_id
AND end_date_active IS NULL;
UPDATE mtl_object_genealogy
SET last_update_date = SYSDATE
, last_updated_by = -1
, end_date_active = p_end_date_active
, update_txn_id = p_update_txn_id
, last_update_login = -1
, request_id = fnd_global.conc_request_id
, program_application_id = fnd_global.prog_appl_id
, program_id = fnd_global.conc_program_id
, program_update_date = SYSDATE
WHERE object_id = l_object_id
AND end_date_active IS NULL;
ROLLBACK TO update_genealogy_pub;
ROLLBACK TO update_genealogy_pub;
ROLLBACK TO update_genealogy_pub;
END update_genealogy;
PROCEDURE insert_flow_genealogy(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
, p_commit IN VARCHAR2 := gen_fnd_g_false
, p_validation_level IN NUMBER := gen_fnd_valid_level_full
, p_transaction_source_id IN NUMBER
, p_completion_transaction_id IN NUMBER
, p_parent_object_id IN NUMBER := NULL
, p_parent_object_number IN VARCHAR2 := NULL
, p_parent_inventory_item_id IN NUMBER := NULL
, p_parent_org_id IN NUMBER := NULL
, p_genealogy_origin IN NUMBER := NULL
, p_genealogy_type IN NUMBER := NULL
, p_start_date_active IN DATE := SYSDATE
, p_end_date_active IN DATE := NULL
, p_origin_txn_id IN NUMBER := NULL
, p_update_txn_id IN NUMBER := NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
--, debug_count OUT NUMBER
) IS
l_transaction_action_id NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'insert_flow_genealogy';
SELECT mtlt.lot_number
, mmtt.organization_id
, mmtt.inventory_item_id
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.transaction_source_id = p_transaction_source_id
AND mmtt.completion_transaction_id = p_completion_transaction_id
AND mmtt.transaction_action_id = l_transaction_action_id
AND mmtt.transaction_source_type_id = l_transaction_source_type_id;
SELECT mtlt.lot_number
, mmtt.organization_id
, mmtt.inventory_item_id
, mmtt.transaction_id
FROM mtl_material_transactions mmtt, mtl_transaction_lot_numbers mtlt
WHERE mmtt.transaction_id = mtlt.transaction_id
AND mmtt.transaction_source_id = p_transaction_source_id
AND mmtt.completion_transaction_id = p_completion_transaction_id
AND mmtt.transaction_action_id = l_transaction_action_id
AND mmtt.transaction_source_type_id = l_transaction_source_type_id;
mydebug('Inside Insert_Flow_Genealogy');
|| ' p_update_txn_id = ' || p_update_txn_id
|| ' p_start_date_active = ' || TO_CHAR(p_start_date_active, 'DD-MON-RRRR')
|| ' p_end_date_active = ' || TO_CHAR(p_end_date_active, 'DD-MON-RRRR') );
SAVEPOINT apiinsert_genealogy_apipub;
mydebug('call insert_genealogy');
inv_genealogy_pub.insert_genealogy(
p_api_version => 1.0
, p_init_msg_list => lg_fnd_g_false
, p_commit => lg_fnd_g_false
, p_validation_level => lg_fnd_valid_level_full
, p_object_type => l_object_type
, p_parent_object_type => l_parent_object_type
, p_object_id => NULL
, p_object_number => l_object_number
, p_inventory_item_id => l_inventory_item_id
, p_org_id => l_organization_id
, p_parent_object_id => NULL
, p_parent_object_number => p_parent_object_number
, p_parent_inventory_item_id => p_parent_inventory_item_id
, p_parent_org_id => p_parent_org_id
, p_genealogy_origin => p_genealogy_origin
, p_genealogy_type => p_genealogy_type
, p_start_date_active => SYSDATE
, p_end_date_active => NULL
, p_origin_txn_id => p_origin_txn_id
, p_update_txn_id => NULL
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
SELECT COUNT(*)
INTO l_count
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.transaction_source_id = p_transaction_source_id
AND mmtt.transaction_action_id = l_transaction_action_id
AND mmtt.transaction_source_type_id = l_transaction_source_type_id
AND mmtt.completion_transaction_id = p_completion_transaction_id;
mydebug('call insert_genealogy');
inv_genealogy_pub.insert_genealogy(
p_api_version => 1.0
, p_init_msg_list => lg_fnd_g_false
, p_commit => lg_fnd_g_false
, p_validation_level => lg_fnd_valid_level_full
, p_object_type => l_object_type
, p_parent_object_type => l_parent_object_type
, p_object_id => NULL
, p_object_number => l_object_number
, p_inventory_item_id => l_inventory_item_id
, p_org_id => l_organization_id
, p_parent_object_id => NULL
, p_parent_object_number => p_parent_object_number
, p_parent_inventory_item_id => p_parent_inventory_item_id
, p_parent_org_id => p_parent_org_id
, p_genealogy_origin => p_genealogy_origin
, p_genealogy_type => p_genealogy_type
, p_start_date_active => SYSDATE
, p_end_date_active => NULL
, p_origin_txn_id => l_origin_txn_id
, p_update_txn_id => NULL
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
ROLLBACK TO save_insert_genealogy;
ROLLBACK TO save_insert_genealogy;
ROLLBACK TO save_insert_genealogy;
END insert_flow_genealogy;
PROCEDURE DELETE_EAM_ROW(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := gen_fnd_g_false,
P_COMMIT IN VARCHAR2 := gen_fnd_g_false,
P_VALIDATION_LEVEL IN NUMBER := gen_fnd_valid_level_full,
P_OBJECT_ID IN NUMBER,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
Delete from mtl_object_genealogy
where object_id = p_object_id
and start_date_active = p_start_date_active
and end_date_active = p_end_date_active
and genealogy_type = 5;
END Delete_EAM_Row;
, p_update_txn_id IN NUMBER
, p_object_type2 IN OUT NOCOPY NUMBER
, p_object_id2 IN OUT NOCOPY NUMBER
, p_object_number2 IN VARCHAR2
, p_parent_object_type2 IN OUT NOCOPY NUMBER
, p_parent_object_id2 IN OUT NOCOPY NUMBER
, p_parent_object_number2 IN VARCHAR2
, p_child_lot_control_code IN NUMBER
, p_parent_lot_control_code IN NUMBER
, p_action IN VARCHAR2
, p_debug IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'parameter_validations';
mydebug('p_update_txn_id: ' || p_update_txn_id );
SELECT INVENTORY_ITEM_ID
INTO l_inventory_item_id
FROM mtl_serial_numbers
WHERE gen_object_id = p_object_id
AND current_Organization_id = p_org_id;
SELECT lot_control_code
INTO l_child_lot_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND Organization_id = p_org_id;
SELECT INVENTORY_ITEM_ID
INTO l_parent_inventory_item_id
FROM mtl_serial_numbers
WHERE gen_object_id = p_parent_object_id
AND current_Organization_id = p_parent_org_id;
SELECT lot_control_code
INTO l_parent_lot_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_parent_inventory_item_id
AND Organization_id = p_parent_org_id;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_lot_numbers
WHERE gen_object_id = p_object_id;
SELECT gen_object_id
INTO l_object_id
FROM mtl_lot_numbers
WHERE lot_number = LTRIM(RTRIM(p_object_number))
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_serial_numbers
WHERE gen_object_id = p_object_id;
SELECT gen_object_id
INTO l_object_id
FROM mtl_serial_numbers
WHERE serial_number = LTRIM(RTRIM(p_object_number))
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_org_id;
SELECT wip_entity_id
INTO l_dummy
FROM wip_entities
WHERE gen_object_id = p_object_id;
SELECT gen_object_id
INTO l_object_id
FROM wip_entities
WHERE wip_entity_name = LTRIM(RTRIM(p_object_number))
AND organization_id = p_org_id;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_lot_numbers
WHERE gen_object_id = p_parent_object_id;
SELECT gen_object_id
INTO l_parent_object_id
FROM mtl_lot_numbers
WHERE lot_number = LTRIM(RTRIM(p_parent_object_number))
AND inventory_item_id = p_parent_inventory_item_id
AND organization_id = p_parent_org_id;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_serial_numbers
WHERE gen_object_id = p_parent_object_id;
SELECT gen_object_id
INTO l_parent_object_id
FROM mtl_serial_numbers
WHERE serial_number = LTRIM(RTRIM(p_parent_object_number))
AND inventory_item_id = p_parent_inventory_item_id
AND current_organization_id = p_parent_org_id;
SELECT wip_entity_id
INTO l_dummy
FROM wip_entities
WHERE gen_object_id = p_parent_object_id;
SELECT gen_object_id
INTO l_parent_object_id
FROM wip_entities
WHERE wip_entity_name = LTRIM(RTRIM(p_parent_object_number))
AND organization_id = p_parent_org_id;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_lot_numbers
WHERE gen_object_id = p_object_id2;
SELECT gen_object_id
INTO l_object_id2
FROM mtl_lot_numbers
WHERE lot_number = LTRIM(RTRIM(p_object_number2))
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT COUNT(*)
INTO l_dummy
FROM mtl_lot_numbers
WHERE gen_object_id = p_parent_object_id2;
SELECT gen_object_id
INTO l_parent_object_id2
FROM mtl_lot_numbers
WHERE lot_number = LTRIM(RTRIM(p_parent_object_number2))
AND inventory_item_id = p_parent_inventory_item_id
AND organization_id = p_parent_org_id;
SELECT COUNT(*)
INTO l_dummy
FROM mfg_lookups
WHERE lookup_type = 'INV_GENEALOGY_ORIGIN'
AND lookup_code = p_genealogy_origin;
PROCEDURE update_genealogy(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := gen_fnd_g_false
, p_commit IN VARCHAR2 := gen_fnd_g_false
, p_validation_level IN NUMBER := gen_fnd_valid_level_full
, p_object_type IN NUMBER
, p_parent_object_type IN NUMBER := NULL
, p_object_id IN NUMBER := NULL
, p_object_number IN VARCHAR2 := NULL
, p_inventory_item_id IN NUMBER := NULL
, p_organization_id IN NUMBER := NULL
, p_parent_object_id IN NUMBER := NULL
, p_parent_object_number IN VARCHAR2 := NULL
, p_parent_inventory_item_id IN NUMBER := NULL
, p_parent_org_id IN NUMBER := NULL
, p_genealogy_origin IN NUMBER := NULL
, p_genealogy_type IN NUMBER := NULL
, p_start_date_active IN DATE := SYSDATE
, p_end_date_active IN DATE := NULL
, p_origin_txn_id IN NUMBER := NULL
, p_update_txn_id IN NUMBER := NULL
, p_object_type2 IN NUMBER := NULL
, p_object_id2 IN NUMBER := NULL
, p_object_number2 IN VARCHAR2 := NULL
, p_parent_object_type2 IN NUMBER := NULL
, p_parent_object_id2 IN NUMBER := NULL
, p_parent_object_number2 IN VARCHAR2 := NULL
, p_child_lot_control_code IN NUMBER := NULL
, p_parent_lot_control_code IN NUMBER := NULL
, p_transaction_type IN VARCHAR2 := NULL -- ASSEMBLY_RETURN, COMP_RETURN, NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
-- 2/2/06: Bug: 4997221 : Added new parameter p_transaction_type
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_genealogy';
SAVEPOINT save_update_genealogy;
g_mod_name := 'update_Genealogy';
mydebug('p_update_txn_id: ' || p_update_txn_id );
l_action := 'UPDATE';
, p_update_txn_id => p_update_txn_id
, p_object_type2 => l_object_type2 -- IN OUT
, p_object_id2 => l_object_id2 -- IN OUT
, p_object_number2 => p_object_number2
, p_parent_object_type2 => l_parent_object_type2 -- IN OUT
, p_parent_object_id2 => l_parent_object_id2 -- IN OUT
, p_parent_object_number2 => p_parent_object_number2
, p_child_lot_control_code => p_child_lot_control_code
, p_parent_lot_control_code => p_parent_lot_control_code
, p_action => l_action
, p_debug => l_debug
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
g_mod_name := 'update_Genealogy';
'an expected exception now..before inserting into genealogy }}' );
'an unexpected exception now..before inserting into genealogy }}');
mydebug('{{- Only if the relationship exists that it can be updated }}' );
UPDATE mtl_object_genealogy
SET last_update_date = SYSDATE
, last_updated_by = -1
, end_date_active = l_end_date_active
, update_txn_id = p_update_txn_id
, last_update_login = -1
, request_id = -1
, program_application_id = fnd_global.prog_appl_id
, program_id = fnd_global.conc_program_id
, program_update_date = SYSDATE
WHERE end_date_active IS NULL
AND parent_object_id = l_object_id
AND parent_object_id2 IS NULL
AND object_type = 5 -- Job : 5368998
AND object_id = l_parent_object_id -- Job's gen object id : 5368998
AND genealogy_type <> 5;
mydebug(' {{- AR: Number of relationships deleted for parent_object_id :'
|| l_object_id || ' - IS:' || sql%rowcount || '}}');
UPDATE mtl_object_genealogy
SET last_update_date = SYSDATE
, last_updated_by = -1
, end_date_active = l_end_date_active
, update_txn_id = p_update_txn_id
, last_update_login = -1
, request_id = fnd_global.conc_request_id
, program_application_id = fnd_global.prog_appl_id
, program_id = fnd_global.conc_program_id
, program_update_date = SYSDATE
WHERE end_date_active IS NULL
AND parent_object_id = l_object_id
AND parent_object_id2 = l_object_id2
AND object_type = 5 -- Job : 5368998
AND object_id = l_parent_object_id -- Job's gen object id : 5368998
AND genealogy_type <> 5;
mydebug(' {{- AR: Number of relationships deleted for object_id ,object_id2 and any other object :'
|| l_object_id || ':' || l_object_id2 || ' - IS:' || sql%rowcount || '}}');
UPDATE mtl_object_genealogy
SET last_update_date = SYSDATE
, last_updated_by = -1
, end_date_active = l_end_date_active
, update_txn_id = p_update_txn_id
, last_update_login = -1
, request_id = -1
, program_application_id = fnd_global.prog_appl_id
, program_id = fnd_global.conc_program_id
, program_update_date = SYSDATE
WHERE end_date_active IS NULL
AND object_id = l_object_id
AND object_id2 IS NULL
AND ( (parent_object_type = 5 -- Job : 5368998
AND parent_object_id = l_parent_object_id) -- Job's gen object id : 5368998
OR (parent_object_type <> 5))
AND genealogy_type = 1;
created by comp issue/assembly completion gets updated */
IF SQL%NOTFOUND THEN
IF (l_debug = 1) THEN
mydebug(' {{- CR: NO relationship between object_id and any other object :' || l_object_id || '}}');
mydebug(' {{- CR: Number of relationships deleted for parent_object_id :'
|| l_object_id || ' - IS:' || sql%rowcount || '}}');
UPDATE mtl_object_genealogy
SET last_update_date = SYSDATE
, last_updated_by = -1
, end_date_active = l_end_date_active
, update_txn_id = p_update_txn_id
, last_update_login = -1
, request_id = fnd_global.conc_request_id
, program_application_id = fnd_global.prog_appl_id
, program_id = fnd_global.conc_program_id
, program_update_date = SYSDATE
WHERE end_date_active IS NULL
AND object_id = l_object_id
AND object_id2 = l_object_id2
AND ( (parent_object_type = 5 -- Job : 5368998
AND parent_object_id = l_parent_object_id) -- Job's gen object id : 5368998
OR (parent_object_type <> 5))
AND genealogy_type = 1;
created by comp issue/assembly completion gets updated */
IF SQL%NOTFOUND THEN
IF (l_debug = 1) THEN
mydebug(' {{- CR: NO relationship between object_id,object_id2 and any other object :' || l_object_id
|| ':' || l_object_id2 || '}}');
mydebug(' {{- CR: Number of relationships deleted for object_id ,object_id2 and any other object :'
|| l_object_id || ':' || l_object_id2 || ' - IS:' || sql%rowcount || '}}');
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;
ROLLBACK TO save_update_genealogy;
ROLLBACK TO save_update_genealogy;
ROLLBACK TO save_update_genealogy;
END update_genealogy;