[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- update on columns which requires that there should be no
-- association between plans and customers. ie. if a record
-- exist in this table, then do not allow the update on that
-- column. eg. use_for_cust_account, profile_check_id etc.
/*** PROCEDURE THAT CONVERTS INDIVIDUAL COLUMN PARAMETERS INTO RECORD TYPE FOR
PROCEDURE OVERLOADING ***/
PROCEDURE convert_columns_to_rec_type(
P_ROW_ID IN ROWID,
P_PLAN_ID IN NUMBER,
P_ORIGINAL_PLAN_ID IN NUMBER,
P_PLAN_GROUP_CODE IN VARCHAR2,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
P_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
P_END_USER_TYPE IN VARCHAR2,
P_CUSTOMIZED_PLAN IN VARCHAR2,
P_PROFILE_CHECK_ID IN NUMBER,
P_RELATIONAL_OPERATOR IN VARCHAR2,
P_CRITERIA_VALUE_HIGH IN VARCHAR2,
P_CRITERIA_VALUE_LOW IN VARCHAR2,
P_CREATION_DATE IN DATE,
P_LAST_UPDATE_DATE IN DATE,
P_CREATED_BY IN NUMBER,
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATE_LOGIN IN NUMBER,
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_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_OBJECT_VERSION_NUMBER IN NUMBER,
X_PVT_CSC_PLAN_HEADERS_B_REC OUT NOCOPY CSC_PLAN_HEADERS_B_REC_TYPE )
IS
BEGIN
x_pvt_csc_plan_headers_b_rec.ROW_ID := p_row_id;
x_pvt_csc_plan_headers_b_rec.LAST_UPDATE_DATE := p_last_update_date;
x_pvt_csc_plan_headers_b_rec.LAST_UPDATED_BY := p_last_updated_by;
x_pvt_csc_plan_headers_b_rec.LAST_UPDATE_LOGIN := p_last_update_login;
P_LAST_UPDATE_DATE IN DATE,
P_CREATED_BY IN NUMBER,
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATE_LOGIN IN NUMBER,
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_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_OBJECT_VERSION_NUMBER IN NUMBER,
P_DESCRIPTION IN VARCHAR2,
P_NAME IN VARCHAR2,
P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
X_PLAN_ID OUT NOCOPY NUMBER,
X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2 )
IS
l_pvt_csc_plan_headers_b_rec CSC_PLAN_HEADERS_B_REC_TYPE;
P_LAST_UPDATE_DATE => p_last_update_date,
P_CREATED_BY => p_created_by,
P_LAST_UPDATED_BY => p_last_updated_by,
P_LAST_UPDATE_LOGIN => p_last_update_login,
P_ATTRIBUTE1 => p_attribute1,
P_ATTRIBUTE2 => p_attribute2,
P_ATTRIBUTE3 => p_attribute3,
P_ATTRIBUTE4 => p_attribute4,
P_ATTRIBUTE5 => p_attribute5,
P_ATTRIBUTE6 => p_attribute6,
P_ATTRIBUTE7 => p_attribute7,
P_ATTRIBUTE8 => p_attribute8,
P_ATTRIBUTE9 => p_attribute9,
P_ATTRIBUTE10 => p_attribute10,
P_ATTRIBUTE11 => p_attribute11,
P_ATTRIBUTE12 => p_attribute12,
P_ATTRIBUTE13 => p_attribute13,
P_ATTRIBUTE14 => p_attribute14,
P_ATTRIBUTE15 => p_attribute15,
P_ATTRIBUTE_CATEGORY => p_attribute_category,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
X_PVT_CSC_PLAN_HEADERS_B_REC => l_pvt_csc_plan_headers_b_rec );
-- cannot allow update because parent plan is at account level and this
-- operation is trying to customize at party level.
-- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
CSC_PLAN_HEADERS_B_PKG.Insert_Row(
px_PLAN_ID => x_plan_id,
p_ORIGINAL_PLAN_ID => l_ins_plan_headers_b_rec.ORIGINAL_PLAN_ID,
p_PLAN_GROUP_CODE => l_ins_plan_headers_b_rec.PLAN_GROUP_CODE,
p_START_DATE_ACTIVE => l_ins_plan_headers_b_rec.START_DATE_ACTIVE,
p_END_DATE_ACTIVE => l_ins_plan_headers_b_rec.END_DATE_ACTIVE,
p_USE_FOR_CUST_ACCOUNT => l_ins_plan_headers_b_rec.USE_FOR_CUST_ACCOUNT,
p_END_USER_TYPE => l_ins_plan_headers_b_rec.END_USER_TYPE,
p_CUSTOMIZED_PLAN => l_ins_plan_headers_b_rec.CUSTOMIZED_PLAN,
p_PROFILE_CHECK_ID => l_ins_plan_headers_b_rec.PROFILE_CHECK_ID,
p_RELATIONAL_OPERATOR => l_ins_plan_headers_b_rec.RELATIONAL_OPERATOR,
p_CRITERIA_VALUE_HIGH => l_ins_plan_headers_b_rec.CRITERIA_VALUE_HIGH,
p_CRITERIA_VALUE_LOW => l_ins_plan_headers_b_rec.CRITERIA_VALUE_LOW,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATE_DATE => SYSDATE,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_ATTRIBUTE1 => l_ins_plan_headers_b_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_ins_plan_headers_b_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_ins_plan_headers_b_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_ins_plan_headers_b_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_ins_plan_headers_b_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_ins_plan_headers_b_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_ins_plan_headers_b_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_ins_plan_headers_b_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_ins_plan_headers_b_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_ins_plan_headers_b_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_ins_plan_headers_b_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_ins_plan_headers_b_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_ins_plan_headers_b_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_ins_plan_headers_b_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_ins_plan_headers_b_rec.ATTRIBUTE15,
p_ATTRIBUTE_CATEGORY => l_ins_plan_headers_b_rec.ATTRIBUTE_CATEGORY,
p_DESCRIPTION => l_DESCRIPTION,
p_NAME => l_NAME,
x_OBJECT_VERSION_NUMBER => x_object_version_number );
CSC_CUSTOMIZED_PLANS_PKG.INSERT_ROW(
px_id => g_customized_id,
p_plan_id => x_plan_id,
p_party_id => l_party_id_tbl(i).party_id,
p_cust_account_id => l_party_id_tbl(i).cust_account_id );
CSC_CUST_PLANS_PVT.Update_for_customized_plans (
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_TRUE,
P_Commit => p_commit,
P_PLAN_ID => x_plan_id,
P_ORIGINAL_PLAN_ID => p_csc_plan_headers_b_rec.original_plan_id,
P_PARTY_ID => l_party_id_tbl(i).party_id,
P_CUST_ACCOUNT_ID => l_party_id_tbl(i).cust_account_id,
-- P_CUST_ACCOUNT_ORG => l_party_id_tbl(i).cust_account_org,
P_OBJECT_VERSION_NUMBER => l_party_id_tbl(i).object_version_number,
X_OBJECT_VERSION_NUMBER => x_cust_object_version_number,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data );
PROCEDURE update_plan_header(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2,
P_Commit IN VARCHAR2,
P_Validation_level IN NUMBER,
P_ROW_ID IN ROWID,
P_PLAN_ID IN NUMBER,
P_ORIGINAL_PLAN_ID IN NUMBER,
P_PLAN_GROUP_CODE IN VARCHAR2,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
P_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
P_END_USER_TYPE IN VARCHAR2,
P_CUSTOMIZED_PLAN IN VARCHAR2,
P_PROFILE_CHECK_ID IN NUMBER,
P_RELATIONAL_OPERATOR IN VARCHAR2,
P_CRITERIA_VALUE_HIGH IN VARCHAR2,
P_CRITERIA_VALUE_LOW IN VARCHAR2,
P_CREATION_DATE IN DATE,
P_LAST_UPDATE_DATE IN DATE,
P_CREATED_BY IN NUMBER,
P_LAST_UPDATED_BY IN NUMBER,
P_LAST_UPDATE_LOGIN IN NUMBER,
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_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_OBJECT_VERSION_NUMBER IN NUMBER,
P_DESCRIPTION IN VARCHAR2,
P_NAME IN VARCHAR2,
P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_pvt_csc_plan_headers_b_rec CSC_PLAN_HEADERS_B_REC_TYPE;
P_LAST_UPDATE_DATE => p_last_update_date,
P_CREATED_BY => p_created_by,
P_LAST_UPDATED_BY => p_last_updated_by,
P_LAST_UPDATE_LOGIN => p_last_update_login,
P_ATTRIBUTE1 => p_attribute1,
P_ATTRIBUTE2 => p_attribute2,
P_ATTRIBUTE3 => p_attribute3,
P_ATTRIBUTE4 => p_attribute4,
P_ATTRIBUTE5 => p_attribute5,
P_ATTRIBUTE6 => p_attribute6,
P_ATTRIBUTE7 => p_attribute7,
P_ATTRIBUTE8 => p_attribute8,
P_ATTRIBUTE9 => p_attribute9,
P_ATTRIBUTE10 => p_attribute10,
P_ATTRIBUTE11 => p_attribute11,
P_ATTRIBUTE12 => p_attribute12,
P_ATTRIBUTE13 => p_attribute13,
P_ATTRIBUTE14 => p_attribute14,
P_ATTRIBUTE15 => p_attribute15,
P_ATTRIBUTE_CATEGORY => p_attribute_category,
P_OBJECT_VERSION_NUMBER => p_object_version_number,
X_PVT_CSC_PLAN_HEADERS_B_REC => l_pvt_csc_plan_headers_b_rec );
update_plan_header(
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_CSC_PLAN_HEADERS_B_REC => l_pvt_csc_plan_headers_b_rec,
P_DESCRIPTION => p_description,
P_NAME => p_name,
P_PARTY_ID_TBL => p_party_id_tbl,
X_Object_Version_number => x_object_version_number,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data );
END update_plan_header;
PROCEDURE Update_plan_header(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2,
P_Commit IN VARCHAR2,
p_validation_level IN NUMBER,
P_CSC_PLAN_HEADERS_B_REC IN CSC_PLAN_HEADERS_B_REC_TYPE,
P_DESCRIPTION IN VARCHAR2,
P_NAME IN VARCHAR2,
P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
Cursor c_get_csc_plan_headers( c_PLAN_ID Number ) IS
Select rowid, PLAN_ID, ORIGINAL_PLAN_ID,
PLAN_GROUP_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE,
USE_FOR_CUST_ACCOUNT, END_USER_TYPE, CUSTOMIZED_PLAN,
PROFILE_CHECK_ID, RELATIONAL_OPERATOR, CRITERIA_VALUE_HIGH,
CRITERIA_VALUE_LOW, CREATION_DATE, LAST_UPDATE_DATE,
CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
ATTRIBUTE_CATEGORY, OBJECT_VERSION_NUMBER, NAME,
DESCRIPTION
From CSC_PLAN_HEADERS_VL
where plan_id = c_plan_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_plan_header';
Select CUST_PLAN_ID, PARTY_ID, CUST_ACCOUNT_ID, OBJECT_VERSION_NUMBER
From CSC_CUST_PLANS
WHERE PLAN_ID = nvl(p_csc_plan_headers_b_rec.plan_id, plan_id)
AND plan_status_code not in ('MERGED', 'TRANSFERED') ORDER BY OBJECT_VERSION_NUMBER;
SAVEPOINT UPDATE_PLAN_HEADER_PVT;
l_ref_PLAN_HEADER_rec.LAST_UPDATE_DATE,
l_ref_PLAN_HEADER_rec.CREATED_BY,
l_ref_PLAN_HEADER_rec.LAST_UPDATED_BY,
l_ref_PLAN_HEADER_rec.LAST_UPDATE_LOGIN,
l_ref_PLAN_HEADER_rec.ATTRIBUTE1,
l_ref_PLAN_HEADER_rec.ATTRIBUTE2,
l_ref_PLAN_HEADER_rec.ATTRIBUTE3,
l_ref_PLAN_HEADER_rec.ATTRIBUTE4,
l_ref_PLAN_HEADER_rec.ATTRIBUTE5,
l_ref_PLAN_HEADER_rec.ATTRIBUTE6,
l_ref_PLAN_HEADER_rec.ATTRIBUTE7,
l_ref_PLAN_HEADER_rec.ATTRIBUTE8,
l_ref_PLAN_HEADER_rec.ATTRIBUTE9,
l_ref_PLAN_HEADER_rec.ATTRIBUTE10,
l_ref_PLAN_HEADER_rec.ATTRIBUTE11,
l_ref_PLAN_HEADER_rec.ATTRIBUTE12,
l_ref_PLAN_HEADER_rec.ATTRIBUTE13,
l_ref_PLAN_HEADER_rec.ATTRIBUTE14,
l_ref_PLAN_HEADER_rec.ATTRIBUTE15,
l_ref_PLAN_HEADER_rec.ATTRIBUTE_CATEGORY,
l_ref_PLAN_HEADER_rec.OBJECT_VERSION_NUMBER,
l_name,
l_description;
FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'API_MISSING_UPDATE_TARGET');
p_validation_mode => CSC_CORE_UTILS_PVT.G_UPDATE,
P_CSC_PLAN_HEADERS_B_REC => L_UPD_PLAN_HEADERS_B_REC,
P_OLD_PLAN_HEADERS_B_REC => l_ref_plan_header_rec,
P_DESCRIPTION => l_description,
P_NAME => l_name,
--P_PARTY_ID => p_party_id,
--P_CUST_ACCOUNT_ID => p_cust_account_id,
--P_CUST_ACCOUNT_ORG => p_cust_account_org,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CSC_PLAN_HEADERS_B_PKG.Update_Row(
p_PLAN_ID => l_upd_plan_headers_b_rec.PLAN_ID,
p_ORIGINAL_PLAN_ID => l_upd_plan_headers_b_rec.ORIGINAL_PLAN_ID,
p_PLAN_GROUP_CODE => l_upd_plan_headers_b_rec.PLAN_GROUP_CODE,
p_START_DATE_ACTIVE => l_upd_plan_headers_b_rec.START_DATE_ACTIVE,
p_END_DATE_ACTIVE => l_upd_plan_headers_b_rec.END_DATE_ACTIVE,
p_USE_FOR_CUST_ACCOUNT => l_upd_plan_headers_b_rec.USE_FOR_CUST_ACCOUNT,
p_END_USER_TYPE => l_upd_plan_headers_b_rec.END_USER_TYPE,
p_CUSTOMIZED_PLAN => l_upd_plan_headers_b_rec.CUSTOMIZED_PLAN,
p_PROFILE_CHECK_ID => l_upd_plan_headers_b_rec.PROFILE_CHECK_ID,
p_RELATIONAL_OPERATOR => l_upd_plan_headers_b_rec.RELATIONAL_OPERATOR,
p_CRITERIA_VALUE_HIGH => l_upd_plan_headers_b_rec.CRITERIA_VALUE_HIGH,
p_CRITERIA_VALUE_LOW => l_upd_plan_headers_b_rec.CRITERIA_VALUE_LOW,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
p_ATTRIBUTE1 => l_upd_plan_headers_b_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_upd_plan_headers_b_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_upd_plan_headers_b_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_upd_plan_headers_b_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_upd_plan_headers_b_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_upd_plan_headers_b_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_upd_plan_headers_b_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_upd_plan_headers_b_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_upd_plan_headers_b_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_upd_plan_headers_b_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_upd_plan_headers_b_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_upd_plan_headers_b_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_upd_plan_headers_b_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_upd_plan_headers_b_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_upd_plan_headers_b_rec.ATTRIBUTE15,
p_ATTRIBUTE_CATEGORY => l_upd_plan_headers_b_rec.ATTRIBUTE_CATEGORY,
P_DESCRIPTION => l_description,
P_NAME => l_name,
X_OBJECT_VERSION_NUMBER => x_object_version_number );
delete from csc_customized_plans
where plan_id = p_csc_plan_headers_b_rec.plan_id;
CSC_CUST_PLANS_PVT.UPDATE_CUST_PLANS (
P_Api_Version_Number => p_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => p_commit,
p_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
P_PLAN_ID => p_csc_plan_headers_b_rec.plan_id,
P_CUST_PLAN_ID => l_ref_CSC_CUST_PLANS_rec.CUST_PLAN_ID,
P_PARTY_ID => l_ref_CSC_CUST_PLANS_rec.PARTY_ID,
P_CUST_ACCOUNT_ID => l_ref_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ID,
p_start_date_active => p_csc_plan_headers_b_rec.start_date_active,
p_end_date_active => p_csc_plan_headers_b_rec.end_date_active,
P_OBJECT_VERSION_NUMBER => l_ref_CSC_CUST_PLANS_rec.OBJECT_VERSION_NUMBER,
X_Object_Version_number => x_object_version_number,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data );
End Update_plan_header;
select cust_plan_id, party_id, cust_account_id, -- cust_account_org,
object_version_number
from csc_cust_plans
where plan_id = p_plan_id;
CSC_CUST_PLANS_PVT.UPDATE_CUST_PLANS (
P_Api_Version_Number => p_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => p_commit,
p_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
p_CUST_PLAN_ID => c1rec.cust_plan_id,
P_PLAN_ID => p_plan_id,
p_end_date_active => sysdate+1,
p_party_id => c1rec.party_id,
p_cust_account_id => c1rec.cust_account_id,
-- p_cust_account_org => c1rec.cust_account_org,
p_object_version_number => c1rec.object_version_number,
X_Object_Version_number => x_object_version_number,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data );
select plan_id
from CSC_PLAN_HEADERS_B
where plan_id = p_plan_id;
if (p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE) then
IF(p_PLAN_ID is NULL or p_plan_id = FND_API.G_MISS_NUM) then
-- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
select plan_id
from CSC_PLAN_HEADERS_VL
where name = p_name;
if ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE and
l_plan_id <> p_plan_id ) then -- some other plan exists with this name
-- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_DUPLICATE_VALUE');
select plan_id
from CSC_PLAN_HEADERS_B
where plan_id = p_original_plan_id;
IF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE ) OR
( p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE AND
p_customized_plan = 'Y' )
THEN
-- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
select count(*)
from fnd_lookups
where lookup_code = p_PLAN_GROUP_CODE
and sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate);
select count(*)
from csc_cust_plans
where plan_id = p_plan_id;
ELSIF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE ) then
if ( p_use_for_cust_account <> p_old_use_for_cust_account ) then
-- Check if there are any existing customer-to-plan associations for this particular
-- PLAN_ID. If ther are, then do not allow the update on the column.
open c1;
-- Use_for_cust_account cannot be updated. There are existing customers
-- associated to this plan.
-- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
fnd_message.set_token('UPDATE_PARAM', 'PLAN_LEVEL');
select count(*)
from csc_lookups
where lookup_type = 'CSC_END_USER_TYPE'
and lookup_code = P_END_USER_TYPE
and sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate);
select count(*)
from csc_prof_checks_b
where check_id = p_profile_check_id
and sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate);
select count(*)
from csc_cust_plans
where plan_id = p_plan_id;
ELSIF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
p_profile_check_id <> p_old_profile_check_id ) then
--if ( G_CUST_PLANS_REC_CNT = FND_API.G_MISS_NUM ) then
open c1;
-- Profile check id cannot be updated. There are customers attached to this plan.
-- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
fnd_message.set_token('UPDATE_PARAM', 'PROFILE_CHECK_ID');
if ( ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
p_profile_check_id <> p_old_profile_check_id ) OR
p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE )
then
open id_in_prof_checks;
select count(*)
from csc_cust_plans
where plan_id = p_plan_id;
IF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
p_criteria_value_low <> p_old_criteria_value_low ) then
--if ( G_CUST_PLANS_REC_CNT = FND_API.G_MISS_NUM ) then
open c1;
-- Criteria_value_low cannot be updated. There are customers attached to this plan.
-- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
fnd_message.set_token('UPDATE_PARAM', 'CRITERIA_VALUE_LOW');
select count(*)
from csc_cust_plans
where plan_id = p_plan_id;
IF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
nvl(p_criteria_value_high,0) <> nvl(p_old_criteria_value_high,0) ) then
--if ( G_CUST_PLANS_REC_CNT = FND_API.G_MISS_NUM ) then
open c1;
-- Criteria_value_high cannot be updated. There are customers attached to this plan.
-- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
fnd_message.set_token('UPDATE_PARAM', 'CRITERIA_VALUE_HIGH');
select count(*)
from csc_cust_plans
where plan_id = p_plan_id;
ELSIF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
p_relational_operator <> p_old_relational_operator ) then
--if ( G_CUST_PLANS_REC_CNT = FND_API.G_MISS_NUM ) then
open c1;
-- Relational_operator cannot be updated. There are customers attached to this plan.
-- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
fnd_message.set_token('UPDATE_PARAM', 'RELATIONAL_OPERATOR');
select min(start_date_active)
from csc_cust_plans
where plan_id = p_plan_id;
if ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE ) then
open c1;
-- START date cannot be updated to specified value. There are customers who are associated
-- to this plan EARLIER than the specified date. Valid dates are LESS than MIN_DATE;
select max(end_date_active)
from csc_cust_plans
where plan_id = p_plan_id;
if ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE ) then
open c1;
-- DATE_TYPE date cannot be updated to specified value. There are customers who are associated
-- to this plan EARLIER_LATER than the specified date. Valid dates are GREATER_LESSER than
-- MAX_MIN_DATE;
fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CSC_RSP_INVALID_DATE_UPDATE');
-- END date cannot be updated to specified value. There are customers who are associated
-- to this plan LATER than the specified date. Valid dates are GREATER than MAX_DATE;