[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT heirarchy_id
FROM ozf_terr_levels_all
WHERE heirarchy_id = l_start_node_id; */
ozf_terr_levels_pvt.delete_terr_levels (
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_hierarchy_id => l_start_node_id
);
ozf_utility_pvt.write_conc_log (' Failed to delete existing levels for terr_id ' || l_start_node_id);
ozf_utility_pvt.write_conc_log (' delete_terr_levels returns error. Msg count='
|| i
|| '-'
|| x_msg_data);
ozf_utility_pvt.write_conc_log ('Update active_flag to N for all OZF Territories to identify the old territories.');
UPDATE ozf_terr_levels_all
SET active_flag = 'N';
ozf_utility_pvt.write_conc_log ('Territory Insertion Call');
ozf_terr_levels_pvt.insert_terr_levels (
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_start_node_id => p_start_node_id
);
ozf_utility_pvt.write_conc_log ('Update active_flag to N for all OZF Territories to identify the old territories.');
UPDATE ozf_terr_levels_all
SET active_flag = 'N';
ozf_utility_pvt.write_conc_log ('Bulk Territories Insertion Call');
ozf_terr_levels_pvt.bulk_insert_terr_levels (
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
);
ozf_utility_pvt.write_conc_log (' Failed to insert levels for terr_id ' || l_start_node_id);
ozf_utility_pvt.write_conc_log (' insert_terr_levels returns error. Msg count='
|| i
|| '-'
|| x_msg_data);
PROCEDURE insert_terr_levels (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_start_node_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'Insert_terr_levels';
l_insert_count NUMBER;
SELECT NVL (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10), -99)
FROM DUAL;
SELECT ozf_terr_levels_all_s.NEXTVAL
FROM DUAL;
SELECT terr_id
,territory_type_id
FROM jtf_terr_all jtf
WHERE jtf.terr_id = l_terr_id
AND jtf.parent_territory_id = 1;
SELECT territory_type_id
FROM jtf_terr_all jtf
WHERE jtf.terr_id = l_terr_id;
SELECT olv.level_depth
FROM ozf_terr_levels_all olv
WHERE olv.heirarchy_id = p_hierarchy_id
AND olv.terr_type_id = p_terr_type_id;
SELECT DISTINCT creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_application_id
,program_id
,program_update_date
,request_id
,territory_type_id
,TO_NUMBER (LEVEL) level_depth
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,org_id
,terr_id
,parent_territory_id
-- Bug # 5723438 fixed by ateotia (+)
,end_date_active
,name
,enabled_flag
-- Bug # 5723438 fixed by ateotia (-)
FROM jtf_terr_all
-- 07/13/2001 mpande removed the where condition instead put a error message so that the user sets the
-- territory type properly
-- WHERE TERRITORY_TYPE_ID is not null
CONNECT BY parent_territory_id = PRIOR terr_id
START WITH terr_id = l_start_node_id;
SAVEPOINT insert_terr_levels;
ozf_utility_pvt.write_conc_log('**********Start of Hierarchy Insert*******' );
DELETE FROM ozf_terr_levels_all
WHERE heirarchy_id = l_terr_level_rec.terr_id;
-- Commented not to check for level_depth while insertion
/* IF l_terr_level IS NOT NULL
AND l_terr_level <> l_terr_level_rec.level_depth THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
fnd_message.set_name ('OZF', 'OZF_TERR_TYPE_DUPLICATE_RECORD');
ozf_utility_pvt.write_conc_log('******* Insert into OZF Schema *******');
INSERT INTO ozf_terr_levels_all
(terr_level_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_application_id
,program_id
,program_update_date
,request_id
,terr_type_id
,level_depth
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,org_id
,territory_id
,parent_territory_id
,object_version_number
,heirarchy_id
-- Bug # 5723438 fixed by ateotia (+)
,hierarchy_name
,end_date_active
,enabled_flag
-- Bug # 5723438 fixed by ateotia (-)
)
VALUES (l_terr_level_id
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.conc_login_id
,l_terr_level_rec.program_application_id
,l_terr_level_rec.program_id
,l_terr_level_rec.program_update_date
,l_terr_level_rec.request_id
,l_terr_level_rec.territory_type_id
,l_terr_level_rec.level_depth
,l_terr_level_rec.attribute1
,l_terr_level_rec.attribute2
,l_terr_level_rec.attribute3
,l_terr_level_rec.attribute4
,l_terr_level_rec.attribute5
,l_terr_level_rec.attribute6
,l_terr_level_rec.attribute7
,l_terr_level_rec.attribute8
,l_terr_level_rec.attribute9
,l_terr_level_rec.attribute10
,l_terr_level_rec.attribute11
,l_terr_level_rec.attribute12
,l_terr_level_rec.attribute13
,l_terr_level_rec.attribute14
,l_terr_level_rec.attribute15
,l_org_id
,l_terr_level_rec.terr_id
,l_terr_level_rec.parent_territory_id
,1
,p_start_node_id
-- Bug # 5723438 fixed by ateotia (+)
,l_terr_level_rec.name
,l_terr_level_rec.end_date_active
,l_terr_level_rec.enabled_flag
-- Bug # 5723438 fixed by ateotia (-)
);
ozf_utility_pvt.write_conc_log('******* Delete Duplicates *******');
DELETE from ozf_terr_levels_all
WHERE active_flag = 'N'
AND territory_id = l_terr_level_rec.terr_id
AND parent_territory_id = l_terr_level_rec.parent_territory_id;
l_insert_count := SQL%ROWCOUNT;
ROLLBACK TO insert_terr_levels;
ROLLBACK TO insert_terr_levels;
ROLLBACK TO insert_terr_levels;
END insert_terr_levels;
PROCEDURE bulk_insert_terr_levels (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'bulk_insert_terr_levels';
SELECT JTR.TERR_ID
FROM JTF_TERR_ALL JTR , JTF_TERR_USGS_ALL JTU , JTF_SOURCES_ALL JSE
WHERE JTU.TERR_ID = JTR.TERR_ID
AND JTU.SOURCE_ID = JSE.SOURCE_ID
AND JTU.SOURCE_ID = -1003
AND JTR.PARENT_TERRITORY_ID = 1
AND NVL(JTR.ORG_ID, -99) = NVL(JTU.ORG_ID, NVL(JTR.ORG_ID, -99))
AND JSE.ORG_ID IS NULL
AND JTR.TERRITORY_TYPE_ID IS NOT NULL
AND NVL(JTR.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ' , NULL, SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99)) =
NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ', NULL, SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99);
ozf_terr_levels_pvt.insert_terr_levels (
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_start_node_id => l_terrIdTbl(i)
);
ozf_utility_pvt.write_conc_log(' /****** Failed to bulk insert level for hier id ' || l_terrIdTbl(i));
ozf_utility_pvt.debug_message(' D: ' || l_api_name || ' successfully insert levels for terr id' || l_terrIdTbl(i));
END bulk_insert_terr_levels;
PROCEDURE delete_terr_levels (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_hierarchy_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_terr_levels';
l_insert_count NUMBER;
/* mpadne 07/13/2001 -- we will delete all records for that hierarhcy and recreate it
CURSOR c_delete_terr (l_hierarchy_id IN NUMBER) IS
SELECT *
FROM ozf_terr_levels_all a
WHERE a.heirarchy_id = l_hierarchy_id;
SAVEPOINT delete_terr_levels;
DELETE FROM ozf_terr_levels_all
WHERE heirarchy_id = p_hierarchy_id;
FOR l_terr_level_rec IN c_delete_terr (p_hierarchy_id)
LOOP
ozf_utility_pvt.debug_message ( l_full_name
|| ': begin');
DELETE FROM ozf_terr_levels_all
WHERE terr_level_id = l_terr_level_rec.terr_level_id;
ROLLBACK TO delete_terr_levels;
ROLLBACK TO delete_terr_levels;
ROLLBACK TO delete_terr_levels;
END delete_terr_levels;