[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_terrtype (
p_api_version_number 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,
p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_terrtype_out_rec OUT NOCOPY terrtype_out_rec_type,
x_terrtypequal_out_tbl OUT NOCOPY terrtypequal_out_tbl_type,
x_terrtypeusgs_out_tbl OUT NOCOPY terrtypeusgs_out_tbl_type,
x_typequaltypeusgs_out_tbl OUT NOCOPY typequaltypeusgs_out_tbl_type
)
AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrType';
SAVEPOINT update_terrtype_pvt;
update_terrtype_record (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_terrtype_rec => p_terrtype_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_terrtype_out_rec => x_terrtype_out_rec
);
update_terrtypequaltype_usage (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_typequaltypeusgs_tbl => p_typequaltypeusgs_tbl,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_typequaltypeusgs_out_tbl => x_typequaltypeusgs_out_tbl
);
update_terrtype_usages (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_terrtypeusgs_tbl => p_terrtypeusgs_tbl,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_terrtypeusgs_out_tbl => x_terrtypeusgs_out_tbl
);
update_terrtype_qualifier (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_terrtypequal_tbl => p_terrtypequal_tbl,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_terrtypequal_out_tbl => x_terrtypequal_out_tbl
);
ROLLBACK TO update_terrtype_pvt;
ROLLBACK TO update_terrtype_pvt;
ROLLBACK TO update_terrtype_pvt;
PROCEDURE delete_terrtype (
p_api_version_number 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,
p_terrtype_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
AS
l_terrtype_id NUMBER := p_terrtype_id;
SELECT jtq.terr_type_qual_id
FROM jtf_terr_type_qual jtq
WHERE jtq.terr_type_id = l_terrtype_id
FOR UPDATE;
SELECT jtqu.type_qtype_usg_id
FROM jtf_type_qtype_usgs jtqu
WHERE jtqu.terr_type_id = l_terrtype_id
FOR UPDATE;
SELECT jtu.terr_type_usg_id
FROM jtf_terr_type_usgs jtu
WHERE jtu.terr_type_id = l_terrtype_id
FOR UPDATE;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Territory';
SAVEPOINT delete_territory_pvt;
delete_terrtype_qualifier (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_terrtypequal_id => c.terr_type_qual_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
delete_terrtypequaltype_usage (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_terrtypequaltype_usg_id => c.type_qtype_usg_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
delete_terrtype_usages (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_terrtypeusg_id => c.terr_type_usg_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
delete_terrtype_record (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_terrtype_id => l_terrtype_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO delete_territory_pvt;
ROLLBACK TO delete_territory_pvt;
ROLLBACK TO delete_territory_pvt;
END delete_terrtype;
SELECT ROWID,
terr_type_id,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login,
application_short_name,
name,
enabled_flag,
description,
start_date_active,
end_date_active,
org_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM jtf_terr_types
WHERE terr_type_id = l_terrtype_id
FOR UPDATE NOWAIT;
l_ref_terrtype_rec.last_updated_by,
l_ref_terrtype_rec.last_update_date,
l_ref_terrtype_rec.created_by,
l_ref_terrtype_rec.creation_date,
l_ref_terrtype_rec.last_update_login,
l_ref_terrtype_rec.application_short_name,
l_ref_terrtype_rec.name,
l_ref_terrtype_rec.enabled_flag,
l_ref_terrtype_rec.description,
l_ref_terrtype_rec.start_date_active,
l_ref_terrtype_rec.end_date_active,
l_ref_terrtype_rec.org_id,
l_ref_terrtype_rec.attribute_category,
l_ref_terrtype_rec.attribute1,
l_ref_terrtype_rec.attribute2,
l_ref_terrtype_rec.attribute3,
l_ref_terrtype_rec.attribute4,
l_ref_terrtype_rec.attribute5,
l_ref_terrtype_rec.attribute6,
l_ref_terrtype_rec.attribute7,
l_ref_terrtype_rec.attribute8,
l_ref_terrtype_rec.attribute9,
l_ref_terrtype_rec.attribute10,
l_ref_terrtype_rec.attribute11,
l_ref_terrtype_rec.attribute12,
l_ref_terrtype_rec.attribute13,
l_ref_terrtype_rec.attribute14,
l_ref_terrtype_rec.attribute15;
jtf_terr_types_pkg.update_row (
x_rowid => l_rowid,
x_terr_type_id => l_ref_terrtype_rec.terr_type_id,
x_last_updated_by => l_ref_terrtype_rec.last_updated_by,
x_last_update_date => l_ref_terrtype_rec.last_update_date,
x_created_by => l_ref_terrtype_rec.created_by,
x_creation_date => l_ref_terrtype_rec.creation_date,
x_last_update_login => l_ref_terrtype_rec.last_update_login,
x_application_short_name => g_app_short_name,
x_name => l_ref_terrtype_rec.name,
x_enabled_flag => 'N',
x_description => l_ref_terrtype_rec.description,
x_start_date_active => (SYSDATE - 1),
x_end_date_active => (SYSDATE - 1),
x_attribute_category => l_ref_terrtype_rec.attribute_category,
x_attribute1 => l_ref_terrtype_rec.attribute1,
x_attribute2 => l_ref_terrtype_rec.attribute2,
x_attribute3 => l_ref_terrtype_rec.attribute3,
x_attribute4 => l_ref_terrtype_rec.attribute4,
x_attribute5 => l_ref_terrtype_rec.attribute5,
x_attribute6 => l_ref_terrtype_rec.attribute6,
x_attribute7 => l_ref_terrtype_rec.attribute7,
x_attribute8 => l_ref_terrtype_rec.attribute8,
x_attribute9 => l_ref_terrtype_rec.attribute9,
x_attribute10 => l_ref_terrtype_rec.attribute10,
x_attribute11 => l_ref_terrtype_rec.attribute11,
x_attribute12 => l_ref_terrtype_rec.attribute12,
x_attribute13 => l_ref_terrtype_rec.attribute13,
x_attribute14 => l_ref_terrtype_rec.attribute14,
x_attribute15 => l_ref_terrtype_rec.attribute15,
x_org_id => l_ref_terrtype_rec.org_id
);
SELECT jtq.terr_type_qual_id,
jtq.last_update_date,
jtq.last_updated_by,
jtq.creation_date,
jtq.created_by,
jtq.last_update_login,
jtq.qual_usg_id,
jtq.terr_type_id,
jtq.exclusive_use_flag,
jtq.overlap_allowed_flag,
jtq.in_use_flag,
jtq.qualifier_mode,
jtq.org_id
FROM jtf_terr_type_qual jtq
WHERE jtq.terr_type_id = p_terrtype_id
FOR UPDATE;
SELECT jtqu.type_qtype_usg_id,
jtqu.terr_type_id,
jtqu.qual_type_usg_id,
jtqu.last_update_date,
jtqu.last_updated_by,
jtqu.creation_date,
jtqu.created_by,
jtqu.last_update_login,
jtqu.org_id
FROM jtf_type_qtype_usgs jtqu
WHERE jtqu.terr_type_id = p_terrtype_id
FOR UPDATE;
SELECT jtu.terr_type_usg_id,
jtu.source_id,
jtu.terr_type_id,
jtu.last_update_date,
jtu.last_updated_by,
jtu.creation_date,
jtu.created_by,
jtu.last_update_login,
jtu.org_id
FROM jtf_terr_type_usgs jtu
WHERE jtu.terr_type_id = p_terrtype_id
FOR UPDATE;
SELECT terr_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
application_short_name,
name,
enabled_flag,
start_date_active,
end_date_active,
description,
org_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
INTO l_terrtype_rec.terr_type_id,
l_terrtype_rec.last_update_date,
l_terrtype_rec.last_updated_by,
l_terrtype_rec.creation_date,
l_terrtype_rec.created_by,
l_terrtype_rec.last_update_login,
l_terrtype_rec.application_short_name,
l_terrtype_rec.name,
l_terrtype_rec.enabled_flag,
l_terrtype_rec.start_date_active,
l_terrtype_rec.end_date_active,
l_terrtype_rec.description,
l_terrtype_rec.org_id,
l_terrtype_rec.attribute_category,
l_terrtype_rec.attribute1,
l_terrtype_rec.attribute2,
l_terrtype_rec.attribute3,
l_terrtype_rec.attribute4,
l_terrtype_rec.attribute5,
l_terrtype_rec.attribute6,
l_terrtype_rec.attribute7,
l_terrtype_rec.attribute8,
l_terrtype_rec.attribute9,
l_terrtype_rec.attribute10,
l_terrtype_rec.attribute11,
l_terrtype_rec.attribute12,
l_terrtype_rec.attribute13,
l_terrtype_rec.attribute14,
l_terrtype_rec.attribute15
FROM jtf_terr_types
WHERE terr_type_id = p_terrtype_id;
jtf_terr_types_pkg.insert_row (
x_rowid => l_rowid,
x_terr_type_id => l_terrtype_id,
x_last_updated_by => p_terrtype_rec.last_updated_by,
x_last_update_date => p_terrtype_rec.last_update_date,
x_created_by => p_terrtype_rec.created_by,
x_creation_date => p_terrtype_rec.creation_date,
x_last_update_login => p_terrtype_rec.last_update_login,
x_application_short_name => p_terrtype_rec.application_short_name,
x_name => p_terrtype_rec.name,
x_enabled_flag => p_terrtype_rec.enabled_flag,
x_description => p_terrtype_rec.description,
x_start_date_active => p_terrtype_rec.start_date_active,
x_end_date_active => p_terrtype_rec.end_date_active,
x_attribute_category => p_terrtype_rec.attribute_category,
x_attribute1 => p_terrtype_rec.attribute1,
x_attribute2 => p_terrtype_rec.attribute2,
x_attribute3 => p_terrtype_rec.attribute3,
x_attribute4 => p_terrtype_rec.attribute4,
x_attribute5 => p_terrtype_rec.attribute5,
x_attribute6 => p_terrtype_rec.attribute6,
x_attribute7 => p_terrtype_rec.attribute7,
x_attribute8 => p_terrtype_rec.attribute8,
x_attribute9 => p_terrtype_rec.attribute9,
x_attribute10 => p_terrtype_rec.attribute10,
x_attribute11 => p_terrtype_rec.attribute11,
x_attribute12 => p_terrtype_rec.attribute12,
x_attribute13 => p_terrtype_rec.attribute13,
x_attribute14 => p_terrtype_rec.attribute14,
x_attribute15 => p_terrtype_rec.attribute15,
x_org_id => p_terrtype_rec.org_id
);
'Insert_Territory_Type_Record'
);
jtf_terr_type_usgs_pkg.insert_row (
x_rowid => l_rowid,
x_terr_type_usg_id => l_terrtypeusg_id,
x_last_update_date => p_terrtypeusgs_tbl (l_counter).last_update_date,
x_last_updated_by => p_terrtypeusgs_tbl (l_counter).last_updated_by,
x_creation_date => p_terrtypeusgs_tbl (l_counter).creation_date,
x_created_by => p_terrtypeusgs_tbl (l_counter).created_by,
x_last_update_login => p_terrtypeusgs_tbl (l_counter).last_update_login,
x_terr_type_id => p_terrtype_id,
x_source_id => p_terrtypeusgs_tbl (l_counter).source_id,
x_org_id => p_terrtypeusgs_tbl (l_counter).org_id
);
'Others exception in Insert_Territory_Type_Usages'
);
jtf_type_qtype_usgs_pkg.insert_row (
x_rowid => l_rowid,
x_type_qtype_usg_id => l_typequaltype_usg_id,
x_last_updated_by => p_typequaltypeusgs_rec.last_updated_by,
x_last_update_date => p_typequaltypeusgs_rec.last_update_date,
x_created_by => p_typequaltypeusgs_rec.created_by,
x_creation_date => p_typequaltypeusgs_rec.creation_date,
x_last_update_login => p_typequaltypeusgs_rec.last_update_login,
x_terr_type_id => p_terrtype_id,
x_qual_type_usg_id => p_typequaltypeusgs_rec.qual_type_usg_id,
x_org_id => p_typequaltypeusgs_rec.org_id
);
jtf_terr_type_qual_pkg.insert_row (
x_rowid => l_rowid,
x_terr_type_qual_id => l_terrtypequal_id,
x_last_update_date => p_terrtypequal_rec.last_update_date,
x_last_updated_by => p_terrtypequal_rec.last_updated_by,
x_creation_date => p_terrtypequal_rec.creation_date,
x_created_by => p_terrtypequal_rec.created_by,
x_last_update_login => p_terrtypequal_rec.last_update_login,
x_terr_type_id => p_terrtype_id,
x_qual_usg_id => p_terrtypequal_rec.qual_usg_id,
x_in_use_flag => p_terrtypequal_rec.in_use_flag,
x_exclusive_use_flag => p_terrtypequal_rec.exclusive_use_flag,
x_overlap_allowed_flag => p_terrtypequal_rec.overlap_allowed_flag,
x_qualifier_mode => p_terrtypequal_rec.qualifier_mode,
x_org_id => p_terrtypequal_rec.org_id
);
PROCEDURE update_terrtype_record (
p_api_version_number 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,
p_terrtype_rec IN terrtype_rec_type := g_miss_terrtype_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_terrtype_out_rec OUT NOCOPY terrtype_out_rec_type
)
AS
CURSOR c_getterrtype (l_terrtype_id NUMBER)
IS
SELECT ROWID,
terr_type_id,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login,
application_short_name,
name,
enabled_flag,
description,
start_date_active,
end_date_active,
org_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM jtf_terr_types
WHERE terr_type_id = l_terrtype_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrType_Record';
SAVEPOINT update_terrtype_pvt;
l_ref_terrtype_rec.last_updated_by,
l_ref_terrtype_rec.last_update_date,
l_ref_terrtype_rec.created_by,
l_ref_terrtype_rec.creation_date,
l_ref_terrtype_rec.last_update_login,
l_ref_terrtype_rec.application_short_name,
l_ref_terrtype_rec.name,
l_ref_terrtype_rec.enabled_flag,
l_ref_terrtype_rec.description,
l_ref_terrtype_rec.start_date_active,
l_ref_terrtype_rec.end_date_active,
l_ref_terrtype_rec.org_id,
l_ref_terrtype_rec.attribute_category,
l_ref_terrtype_rec.attribute1,
l_ref_terrtype_rec.attribute2,
l_ref_terrtype_rec.attribute3,
l_ref_terrtype_rec.attribute4,
l_ref_terrtype_rec.attribute5,
l_ref_terrtype_rec.attribute6,
l_ref_terrtype_rec.attribute7,
l_ref_terrtype_rec.attribute8,
l_ref_terrtype_rec.attribute9,
l_ref_terrtype_rec.attribute10,
l_ref_terrtype_rec.attribute11,
l_ref_terrtype_rec.attribute12,
l_ref_terrtype_rec.attribute13,
l_ref_terrtype_rec.attribute14,
l_ref_terrtype_rec.attribute15;
fnd_message.set_name ('JTF', 'API_MISSING_UPDATE_TARGET');
jtf_terr_types_pkg.update_row (
x_rowid => l_rowid,
x_terr_type_id => p_terrtype_rec.terr_type_id,
x_last_updated_by => p_terrtype_rec.last_updated_by,
x_last_update_date => p_terrtype_rec.last_update_date,
x_created_by => p_terrtype_rec.created_by,
x_creation_date => p_terrtype_rec.creation_date,
x_last_update_login => p_terrtype_rec.last_update_login,
x_application_short_name => p_terrtype_rec.application_short_name,
x_name => p_terrtype_rec.name,
x_enabled_flag => p_terrtype_rec.enabled_flag,
x_description => p_terrtype_rec.description,
x_start_date_active => p_terrtype_rec.start_date_active,
x_end_date_active => p_terrtype_rec.end_date_active,
x_attribute_category => p_terrtype_rec.attribute_category,
x_attribute1 => p_terrtype_rec.attribute1,
x_attribute2 => p_terrtype_rec.attribute2,
x_attribute3 => p_terrtype_rec.attribute3,
x_attribute4 => p_terrtype_rec.attribute4,
x_attribute5 => p_terrtype_rec.attribute5,
x_attribute6 => p_terrtype_rec.attribute6,
x_attribute7 => p_terrtype_rec.attribute7,
x_attribute8 => p_terrtype_rec.attribute8,
x_attribute9 => p_terrtype_rec.attribute9,
x_attribute10 => p_terrtype_rec.attribute10,
x_attribute11 => p_terrtype_rec.attribute11,
x_attribute12 => p_terrtype_rec.attribute12,
x_attribute13 => p_terrtype_rec.attribute13,
x_attribute14 => p_terrtype_rec.attribute14,
x_attribute15 => p_terrtype_rec.attribute15,
x_org_id => p_terrtype_rec.org_id
);
ROLLBACK TO update_terrtype_pvt;
ROLLBACK TO update_terrtype_pvt;
ROLLBACK TO update_terrtype_pvt;
'Error inside Update_TerrType_Record ' || SQLERRM
);
END update_terrtype_record;
PROCEDURE update_terrtype_usages (
p_api_version_number 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,
p_terrtypeusgs_rec IN terrtypeusgs_rec_type := g_miss_terrtypeusgs_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_terrtypeusgs_out_rec OUT NOCOPY terrtypeusgs_out_rec_type
)
AS
CURSOR c_getterrtypeusage (l_terr_type_usg_id NUMBER)
IS
SELECT ROWID,
terr_type_usg_id,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login,
terr_type_id,
source_id
FROM jtf_terr_type_usgs
WHERE terr_type_usg_id = l_terr_type_usg_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_TerrType_Usages';
SAVEPOINT update_terrtype_usgs_pvt;
l_ref_terrtypeusgs_rec.last_updated_by,
l_ref_terrtypeusgs_rec.last_update_date,
l_ref_terrtypeusgs_rec.created_by,
l_ref_terrtypeusgs_rec.creation_date,
l_ref_terrtypeusgs_rec.last_update_login,
l_ref_terrtypeusgs_rec.terr_type_id,
l_ref_terrtypeusgs_rec.source_id;
fnd_message.set_name ('JTF', 'API_MISSING_UPDATE_TARGET');
jtf_terr_type_usgs_pkg.update_row (
x_rowid => l_rowid,
x_terr_type_usg_id => p_terrtypeusgs_rec.terr_type_usg_id,
x_last_update_date => p_terrtypeusgs_rec.last_update_date,
x_last_updated_by => p_terrtypeusgs_rec.last_updated_by,
x_creation_date => p_terrtypeusgs_rec.creation_date,
x_created_by => p_terrtypeusgs_rec.created_by,
x_last_update_login => p_terrtypeusgs_rec.last_update_login,
x_terr_type_id => p_terrtypeusgs_rec.terr_type_id,
x_source_id => p_terrtypeusgs_rec.source_id,
x_org_id => p_terrtypeusgs_rec.org_id
);
ROLLBACK TO update_terrtype_usgs_pvt;
ROLLBACK TO update_terrtype_usgs_pvt;
ROLLBACK TO update_terrtype_usgs_pvt;
'Error inside Update_Territory_Usages ' || SQLERRM
);
END update_terrtype_usages;
PROCEDURE update_terrtype_usages (
p_api_version_number 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,
p_terrtypeusgs_tbl IN terrtypeusgs_tbl_type := g_miss_terrtypeusgs_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_terrtypeusgs_out_tbl OUT NOCOPY terrtypeusgs_out_tbl_type
)
AS
l_rowid ROWID;
update_terrtype_usages (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_terrtypeusgs_rec => p_terrtypeusgs_tbl (l_counter),
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_terrtypeusgs_out_rec => l_terrtypeusgs_out_rec
);
END update_terrtype_usages;
PROCEDURE update_terrtypequaltype_usage (
p_api_version_number 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,
p_typequaltypeusgs_rec IN typequaltypeusgs_rec_type := g_miss_typequaltypeusgs_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_typequaltypeusgs_out_rec OUT NOCOPY typequaltypeusgs_out_rec_type
)
AS
CURSOR c_gettypequaltypeusgs (l_type_qual_type_usg_id NUMBER)
IS
SELECT ROWID,
type_qtype_usg_id,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login,
terr_type_id,
qual_type_usg_id
FROM jtf_type_qtype_usgs
WHERE type_qtype_usg_id = l_type_qual_type_usg_id
FOR UPDATE NOWAIT;
:= 'Update_TerrTypeQualType_Usage';
SAVEPOINT update_typeqtype_usgs_pvt;
l_ref_typequaltypeusgs_rec.last_updated_by,
l_ref_typequaltypeusgs_rec.last_update_date,
l_ref_typequaltypeusgs_rec.created_by,
l_ref_typequaltypeusgs_rec.creation_date,
l_ref_typequaltypeusgs_rec.last_update_login,
l_ref_typequaltypeusgs_rec.terr_type_id,
l_ref_typequaltypeusgs_rec.qual_type_usg_id;
fnd_message.set_name ('JTF', 'API_MISSING_UPDATE_TARGET');
jtf_type_qtype_usgs_pkg.update_row (
x_rowid => l_rowid,
x_type_qtype_usg_id => p_typequaltypeusgs_rec.type_qual_type_usg_id,
x_last_updated_by => p_typequaltypeusgs_rec.last_updated_by,
x_last_update_date => p_typequaltypeusgs_rec.last_update_date,
x_created_by => p_typequaltypeusgs_rec.created_by,
x_creation_date => p_typequaltypeusgs_rec.creation_date,
x_last_update_login => p_typequaltypeusgs_rec.last_update_login,
x_terr_type_id => p_typequaltypeusgs_rec.terr_type_id,
x_qual_type_usg_id => p_typequaltypeusgs_rec.qual_type_usg_id,
x_org_id => p_typequaltypeusgs_rec.org_id
);
ROLLBACK TO update_typeqtype_usgs_pvt;
ROLLBACK TO update_typeqtype_usgs_pvt;
ROLLBACK TO update_typeqtype_usgs_pvt;
'Update error inside Update_TerrTypeQualType_Usage'
);
END update_terrtypequaltype_usage;
PROCEDURE update_terrtypequaltype_usage (
p_api_version_number 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,
p_typequaltypeusgs_tbl IN typequaltypeusgs_tbl_type := g_miss_typequaltypeusgs_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_typequaltypeusgs_out_tbl OUT NOCOPY typequaltypeusgs_out_tbl_type
)
AS
l_rowid ROWID;
update_terrtypequaltype_usage (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_typequaltypeusgs_rec => p_typequaltypeusgs_tbl (l_counter),
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_typequaltypeusgs_out_rec => l_typeqtypusgs_out_rec
);
END update_terrtypequaltype_usage;
PROCEDURE update_terrtype_qualifier (
p_api_version_number 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,
p_terrtypequal_rec IN terrtypequal_rec_type := g_miss_terrtypequal_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_terrtypequal_out_rec OUT NOCOPY terrtypequal_out_rec_type
)
AS
CURSOR c_getterrqualifier (l_terrtypequal_id NUMBER)
IS
SELECT ROWID,
terr_type_qual_id,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login,
qual_usg_id,
terr_type_id,
exclusive_use_flag,
overlap_allowed_flag,
in_use_flag
FROM jtf_terr_type_qual
WHERE terr_type_qual_id = l_terrtypequal_id
FOR UPDATE NOWAIT;
:= 'Update_Terr_Type_Qualifier';
SAVEPOINT update_terrtype_qualifier;
l_ref_terrtypequal_rec.last_updated_by,
l_ref_terrtypequal_rec.last_update_date,
l_ref_terrtypequal_rec.created_by,
l_ref_terrtypequal_rec.creation_date,
l_ref_terrtypequal_rec.last_update_login,
l_ref_terrtypequal_rec.qual_usg_id,
l_ref_terrtypequal_rec.terr_type_id,
l_ref_terrtypequal_rec.exclusive_use_flag,
l_ref_terrtypequal_rec.overlap_allowed_flag,
l_ref_terrtypequal_rec.in_use_flag;
fnd_message.set_name ('JTF', 'API_MISSING_UPDATE_TARGET');
jtf_terr_type_qual_pkg.update_row (
x_rowid => l_rowid,
x_terr_type_qual_id => p_terrtypequal_rec.terr_type_qual_id,
x_last_update_date => p_terrtypequal_rec.last_update_date,
x_last_updated_by => p_terrtypequal_rec.last_updated_by,
x_creation_date => p_terrtypequal_rec.creation_date,
x_created_by => p_terrtypequal_rec.created_by,
x_last_update_login => p_terrtypequal_rec.last_update_login,
x_qual_usg_id => p_terrtypequal_rec.qual_usg_id,
x_terr_type_id => p_terrtypequal_rec.terr_type_id,
x_exclusive_use_flag => p_terrtypequal_rec.exclusive_use_flag,
x_overlap_allowed_flag => p_terrtypequal_rec.overlap_allowed_flag,
x_in_use_flag => p_terrtypequal_rec.in_use_flag,
x_qualifier_mode => p_terrtypequal_rec.qualifier_mode,
x_org_id => p_terrtypequal_rec.org_id
);
ROLLBACK TO update_terrtype_qualifier;
ROLLBACK TO update_terrtype_qualifier;
ROLLBACK TO update_terrtype_qualifier;
'Error inside Update_TerrType_Qualifer ' || SQLERRM
);
END update_terrtype_qualifier;
PROCEDURE update_terrtype_qualifier (
p_api_version_number 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,
p_terrtypequal_tbl IN terrtypequal_tbl_type := g_miss_terrtypequal_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_terrtypequal_out_tbl OUT NOCOPY terrtypequal_out_tbl_type
)
AS
l_terr_qual_id NUMBER;
update_terrtype_qualifier (
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_terrtypequal_rec => p_terrtypequal_tbl (l_counter),
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_terrtypequal_out_rec => l_terrtypequal_out_rec
);
END update_terrtype_qualifier;
PROCEDURE delete_terrtype_record (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_terrtype_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
)
AS
l_row_count NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_TerrType_Record';
SAVEPOINT delete_terr_record_pvt;
jtf_terr_types_pkg.delete_row (x_terr_type_id => p_terrtype_id);
fnd_message.set_name ('JTF', 'TERRTYPE_RECORDS_DELETED');
fnd_message.set_name ('ITEMS_DELETED', l_row_count);
ROLLBACK TO delete_terr_record_pvt;
ROLLBACK TO delete_terr_record_pvt;
'Error inside Delete_TerrType_Record ' || SQLERRM
);
END delete_terrtype_record;
PROCEDURE delete_terrtype_usages (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_terrtypeusg_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
)
AS
l_row_count NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Territory_Usages';
SAVEPOINT delete_terrtype_usgs_pvt;
jtf_terr_type_usgs_pkg.delete_row (
x_terr_type_usg_id => p_terrtypeusg_id
);
fnd_message.set_name ('JTF', 'TERRTYPE_USGS_DELETED');
fnd_message.set_name ('ITEMS_DELETED', l_row_count);
ROLLBACK TO delete_terrtype_usgs_pvt;
ROLLBACK TO delete_terrtype_usgs_pvt;
'Error inside Delete_TerrType_Usages ' || SQLERRM
);
END delete_terrtype_usages;
PROCEDURE delete_terrtypequaltype_usage (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_terrtypequaltype_usg_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
)
AS
l_row_count NUMBER;
:= 'Delete_TerrTypeQualType_Usage';
SAVEPOINT delete_typequaltypeusg_pvt;
jtf_type_qtype_usgs_pkg.delete_row (
x_type_qtype_usg_id => p_terrtypequaltype_usg_id
);
fnd_message.set_name ('JTF', 'TERRTYPE_QUALUSGS_DELETED');
fnd_message.set_name ('ITEMS_DELETED', l_row_count);
ROLLBACK TO delete_typequaltypeusg_pvt;
ROLLBACK TO delete_typequaltypeusg_pvt;
'Error inside Delete_TerrTypeQualType_Usage ' || SQLERRM
);
END delete_terrtypequaltype_usage;
PROCEDURE delete_terrtype_qualifier (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_terrtypequal_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
)
AS
l_row_count NUMBER;
:= 'Delete_TerrType_Qualifier';
SAVEPOINT delete_terrtypequal_pvt;
jtf_terr_type_qual_pkg.delete_row (
x_terr_type_qual_id => p_terrtypequal_id
);
fnd_message.set_name ('JTF', 'TERRTYPE_QUALIFIERS_DELETED');
fnd_message.set_name ('ITEMS_DELETED', l_row_count);
ROLLBACK TO delete_terrtypequal_pvt;
ROLLBACK TO delete_terrtypequal_pvt;
'Delete error inside Delete_TerrType_Qualifier'
);
END delete_terrtype_qualifier;
IF ( p_terr_type_rec.last_updated_by IS NULL
OR p_terr_type_rec.last_updated_by = fnd_api.g_miss_num)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATED_BY');
IF ( p_terr_type_rec.last_update_date IS NULL
OR p_terr_type_rec.last_update_date = fnd_api.g_miss_date)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_DATE');
IF ( p_terr_type_rec.last_update_login IS NULL
OR p_terr_type_rec.last_update_login = fnd_api.g_miss_num)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_LOGIN');
SELECT 'x'
INTO l_temp
FROM jtf_qual_usgs jqu,
jtf_qual_type_usgs jqtu,
jtf_type_qtype_usgs jtqu
WHERE jtqu.terr_type_id = p_terr_type_id
AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
AND jqu.qual_usg_id = p_terrtypequal_rec.qual_usg_id
AND jqu.enabled_flag = 'Y'
AND jqtu.qual_type_id IN
( SELECT related_id
FROM jtf_qual_type_denorm_v
WHERE qual_type_id = jqtu.qual_type_id)
AND rownum < 2;
IF ( p_terrtypequal_rec.last_updated_by IS NULL
OR p_terrtypequal_rec.last_updated_by = fnd_api.g_miss_num)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATED_BY');
IF ( p_terrtypequal_rec.last_update_date IS NULL
OR p_terrtypequal_rec.last_update_date = fnd_api.g_miss_date)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_DATE');
IF ( p_terrtypequal_rec.last_update_login IS NULL
OR p_terrtypequal_rec.last_update_login = fnd_api.g_miss_num)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_LOGIN');
IF ( p_terrtypeusgs_rec.last_updated_by IS NULL
OR p_terrtypeusgs_rec.last_updated_by = fnd_api.g_miss_num)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATED_BY');
IF ( p_terrtypeusgs_rec.last_update_date IS NULL
OR p_terrtypeusgs_rec.last_update_date = fnd_api.g_miss_date)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_DATE');
IF ( p_terrtypeusgs_rec.last_update_login IS NULL
OR p_terrtypeusgs_rec.last_update_login = fnd_api.g_miss_num)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_LOGIN');
SELECT 1
INTO l_dummy
FROM jtf_terr_type_usgs jtu, jtf_qual_type_usgs jqtu
WHERE jtu.terr_type_id = p_terr_type_id
AND jtu.source_id = jqtu.source_id
AND jqtu.qual_type_usg_id = l_qual_type_usg_id;
IF ( p_type_qualtypeusgs_rec.last_updated_by IS NULL
OR p_type_qualtypeusgs_rec.last_updated_by = fnd_api.g_miss_num)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATED_BY');
IF ( p_type_qualtypeusgs_rec.last_update_date IS NULL
OR p_type_qualtypeusgs_rec.last_update_date = fnd_api.g_miss_date)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_DATE');
IF ( p_type_qualtypeusgs_rec.last_update_login IS NULL
OR p_type_qualtypeusgs_rec.last_update_login = fnd_api.g_miss_num)
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('JTF', 'JTF_TERR_MISSING_COL_VALUE');
fnd_message.set_token ('COL_NAME', 'LAST_UPDATE_LOGIN');
SELECT COUNT (*)
INTO l_count
FROM jtf_terr
WHERE territory_type_id = p_terrtype_id;