[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_PROGRAM_UPDATE_DATE IN DATE ,
P_CREATION_DATE IN DATE,
P_CREATED_BY IN NUMBER,
P_LAST_UPDATE_DATE IN DATE ,
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_CSC_CUST_PLANS_REC_TYPE OUT NOCOPY CSC_CUST_PLANS_REC_TYPE )
IS
BEGIN
x_csc_cust_plans_rec_type.PLAN_ID := P_PLAN_ID ;
x_csc_cust_plans_rec_type.PROGRAM_UPDATE_DATE := P_PROGRAM_UPDATE_DATE ;
x_csc_cust_plans_rec_type.LAST_UPDATE_DATE := P_LAST_UPDATE_DATE ;
x_csc_cust_plans_rec_type.LAST_UPDATED_BY := P_LAST_UPDATED_BY ;
x_csc_cust_plans_rec_type.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN ;
P_PROGRAM_UPDATE_DATE IN DATE ,
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_CUST_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_csc_cust_plans_rec CSC_CUST_PLANS_REC_TYPE;
P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE ,
P_CREATION_DATE => P_CREATION_DATE ,
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_CSC_CUST_PLANS_REC_TYPE => l_csc_cust_plans_rec );
select start_date_active,
end_date_active
from csc_plan_headers_b
where plan_id = c_plan_id;
CSC_CUST_PLANS_PKG.Insert_Row(
px_CUST_PLAN_ID => x_CUST_PLAN_ID,
p_PLAN_ID => p_CSC_CUST_PLANS_rec.PLAN_ID,
p_PARTY_ID => p_CSC_CUST_PLANS_rec.PARTY_ID,
p_CUST_ACCOUNT_ID => p_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ID,
-- p_CUST_ACCOUNT_ORG => p_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ORG,
p_START_DATE_ACTIVE => l_start_date_active,
p_END_DATE_ACTIVE => l_end_date_active,
p_MANUAL_FLAG => p_CSC_CUST_PLANS_rec.MANUAL_FLAG,
p_PLAN_STATUS_CODE => CSC_CORE_UTILS_PVT.APPLY_PLAN,
p_REQUEST_ID => p_CSC_CUST_PLANS_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => p_CSC_CUST_PLANS_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => p_CSC_CUST_PLANS_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => p_CSC_CUST_PLANS_rec.PROGRAM_UPDATE_DATE,
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 => p_CSC_CUST_PLANS_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => p_CSC_CUST_PLANS_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => p_CSC_CUST_PLANS_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => p_CSC_CUST_PLANS_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => p_CSC_CUST_PLANS_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => p_CSC_CUST_PLANS_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => p_CSC_CUST_PLANS_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => p_CSC_CUST_PLANS_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => p_CSC_CUST_PLANS_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => p_CSC_CUST_PLANS_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => p_CSC_CUST_PLANS_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => p_CSC_CUST_PLANS_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => p_CSC_CUST_PLANS_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => p_CSC_CUST_PLANS_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => p_CSC_CUST_PLANS_rec.ATTRIBUTE15,
p_ATTRIBUTE_CATEGORY => p_CSC_CUST_PLANS_rec.ATTRIBUTE_CATEGORY,
X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER);
CSC_CUST_PLANS_AUDIT_PKG.Insert_Row(
--px_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID,-- will be selected from the sequence.
p_PLAN_ID => p_CSC_CUST_PLANS_rec.PLAN_ID ,
p_PARTY_ID => p_CSC_CUST_PLANS_rec.PARTY_ID ,
p_CUST_ACCOUNT_ID => p_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ID ,
-- p_CUST_ACCOUNT_ORG => p_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ORG ,
p_PLAN_STATUS_CODE => CSC_CORE_UTILS_PVT.APPLY_PLAN,
p_REQUEST_ID => p_CSC_CUST_PLANS_rec.REQUEST_ID ,
p_PROGRAM_APPLICATION_ID => p_CSC_CUST_PLANS_rec.PROGRAM_APPLICATION_ID ,
p_PROGRAM_ID => p_CSC_CUST_PLANS_rec.PROGRAM_ID ,
p_PROGRAM_UPDATE_DATE => p_CSC_CUST_PLANS_rec.PROGRAM_UPDATE_DATE ,
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 => p_CSC_CUST_PLANS_rec.ATTRIBUTE1 ,
p_ATTRIBUTE2 => p_CSC_CUST_PLANS_rec.ATTRIBUTE2 ,
p_ATTRIBUTE3 => p_CSC_CUST_PLANS_rec.ATTRIBUTE3 ,
p_ATTRIBUTE4 => p_CSC_CUST_PLANS_rec.ATTRIBUTE4 ,
p_ATTRIBUTE5 => p_CSC_CUST_PLANS_rec.ATTRIBUTE5 ,
p_ATTRIBUTE6 => p_CSC_CUST_PLANS_rec.ATTRIBUTE6 ,
p_ATTRIBUTE7 => p_CSC_CUST_PLANS_rec.ATTRIBUTE7 ,
p_ATTRIBUTE8 => p_CSC_CUST_PLANS_rec.ATTRIBUTE8 ,
p_ATTRIBUTE9 => p_CSC_CUST_PLANS_rec.ATTRIBUTE9 ,
p_ATTRIBUTE10 => p_CSC_CUST_PLANS_rec.ATTRIBUTE10 ,
p_ATTRIBUTE11 => p_CSC_CUST_PLANS_rec.ATTRIBUTE11 ,
p_ATTRIBUTE12 => p_CSC_CUST_PLANS_rec.ATTRIBUTE12 ,
p_ATTRIBUTE13 => p_CSC_CUST_PLANS_rec.ATTRIBUTE13 ,
p_ATTRIBUTE14 => p_CSC_CUST_PLANS_rec.ATTRIBUTE14 ,
p_ATTRIBUTE15 => p_CSC_CUST_PLANS_rec.ATTRIBUTE15 ,
p_ATTRIBUTE_CATEGORY => p_CSC_CUST_PLANS_rec.ATTRIBUTE_CATEGORY,
x_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID );
PROCEDURE Update_cust_plans(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2,
P_Commit IN VARCHAR2,
p_validation_level IN NUMBER,
P_PLAN_ID IN NUMBER,
P_CUST_PLAN_ID IN NUMBER,
P_PARTY_ID IN NUMBER,
P_CUST_ACCOUNT_ID IN NUMBER,
-- P_CUST_ACCOUNT_ORG IN NUMBER := FND_API.G_MISS_NUM,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
P_MANUAL_FLAG IN VARCHAR2,
P_PLAN_STATUS_CODE IN VARCHAR2,
P_REQUEST_ID IN NUMBER,
P_PROGRAM_APPLICATION_ID IN NUMBER,
P_PROGRAM_ID IN NUMBER,
P_PROGRAM_UPDATE_DATE IN DATE,
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_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_csc_cust_plans_rec CSC_CUST_PLANS_REC_TYPE;
P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE ,
P_CREATION_DATE => P_CREATION_DATE ,
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_CSC_CUST_PLANS_REC_TYPE => l_csc_cust_plans_rec );
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 => p_validation_level,
P_CSC_CUST_PLANS_Rec => l_csc_cust_plans_rec,
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_CUST_PLANS; -- end of overloaded procedure;
PROCEDURE Update_cust_plans(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2,
P_Commit IN VARCHAR2,
p_validation_level IN NUMBER ,
P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_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_cust_plans IS
Select rowid, CUST_PLAN_ID, PLAN_ID,
PARTY_ID, CUST_ACCOUNT_ID, -- CUST_ACCOUNT_ORG,
START_DATE_ACTIVE, END_DATE_ACTIVE, MANUAL_FLAG,
PLAN_STATUS_CODE, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
CREATION_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15, ATTRIBUTE_CATEGORY, OBJECT_VERSION_NUMBER
From CSC_CUST_PLANS
WHERE CUST_PLAN_ID = nvl(p_csc_cust_plans_rec.cust_plan_id, cust_plan_id)
AND PLAN_ID = nvl(p_csc_cust_plans_rec.plan_id, plan_id)
AND PARTY_ID = nvl(p_csc_cust_plans_rec.party_id, party_id)
AND nvl(CUST_ACCOUNT_ID,0) = nvl(p_csc_cust_plans_rec.cust_account_id,
nvl(cust_account_id,0) );
l_api_name CONSTANT VARCHAR2(30) := 'Update_cust_plans';
SAVEPOINT UPDATE_CUST_PLANS_PVT;
l_ref_CSC_CUST_PLANS_rec.PROGRAM_UPDATE_DATE,
l_ref_CSC_CUST_PLANS_rec.LAST_UPDATE_DATE,
l_ref_CSC_CUST_PLANS_rec.CREATION_DATE,
l_ref_CSC_CUST_PLANS_rec.LAST_UPDATED_BY,
l_ref_CSC_CUST_PLANS_rec.CREATED_BY,
l_ref_CSC_CUST_PLANS_rec.LAST_UPDATE_LOGIN,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE1,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE2,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE3,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE4,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE5,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE6,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE7,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE8,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE9,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE10,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE11,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE12,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE13,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE14,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE15,
l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE_CATEGORY,
l_ref_CSC_CUST_PLANS_rec.OBJECT_VERSION_NUMBER;
FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'API_MISSING_UPDATE_TARGET');
l_upd_csc_cust_plans_rec.program_update_date := CSC_CORE_UTILS_PVT.Get_G_Miss_date(p_csc_cust_plans_rec.program_update_date, l_ref_CSC_CUST_PLANS_rec.PROGRAM_UPDATE_DATE);
l_upd_csc_cust_plans_rec.last_update_date := CSC_CORE_UTILS_PVT.Get_G_Miss_date(p_csc_cust_plans_rec.last_update_date, l_ref_CSC_CUST_PLANS_rec.LAST_UPDATE_DATE);
p_validation_mode => CSC_CORE_UTILS_PVT.G_UPDATE,
P_CSC_CUST_PLANS_Rec => L_UPD_CSC_CUST_PLANS_REC,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
CSC_CUST_PLANS_PKG.Update_Row(
p_CUST_PLAN_ID => l_upd_csc_cust_plans_rec.CUST_PLAN_ID,
p_PLAN_ID => l_upd_csc_cust_plans_rec.PLAN_ID,
p_PARTY_ID => l_upd_csc_cust_plans_rec.PARTY_ID,
p_CUST_ACCOUNT_ID => l_upd_csc_cust_plans_rec.CUST_ACCOUNT_ID,
-- p_CUST_ACCOUNT_ORG => l_upd_csc_cust_plans_rec.CUST_ACCOUNT_ORG,
p_START_DATE_ACTIVE => l_upd_csc_cust_plans_rec.START_DATE_ACTIVE,
p_END_DATE_ACTIVE => l_upd_csc_cust_plans_rec.END_DATE_ACTIVE,
p_MANUAL_FLAG => l_upd_csc_cust_plans_rec.MANUAL_FLAG,
p_PLAN_STATUS_CODE => l_upd_csc_cust_plans_rec.PLAN_STATUS_CODE,
p_REQUEST_ID => l_upd_csc_cust_plans_rec.REQUEST_ID,
p_PROGRAM_APPLICATION_ID => l_upd_csc_cust_plans_rec.PROGRAM_APPLICATION_ID,
p_PROGRAM_ID => l_upd_csc_cust_plans_rec.PROGRAM_ID,
p_PROGRAM_UPDATE_DATE => l_upd_csc_cust_plans_rec.PROGRAM_UPDATE_DATE,
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_csc_cust_plans_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => l_upd_csc_cust_plans_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => l_upd_csc_cust_plans_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => l_upd_csc_cust_plans_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => l_upd_csc_cust_plans_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => l_upd_csc_cust_plans_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => l_upd_csc_cust_plans_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => l_upd_csc_cust_plans_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => l_upd_csc_cust_plans_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => l_upd_csc_cust_plans_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => l_upd_csc_cust_plans_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => l_upd_csc_cust_plans_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => l_upd_csc_cust_plans_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => l_upd_csc_cust_plans_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => l_upd_csc_cust_plans_rec.ATTRIBUTE15,
p_ATTRIBUTE_CATEGORY => l_upd_csc_cust_plans_rec.ATTRIBUTE_CATEGORY,
X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER);
CSC_CUST_PLANS_AUDIT_PKG.Insert_Row(
--px_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID,-- will be selected from the sequence.
p_PLAN_ID => l_upd_csc_cust_plans_rec.PLAN_ID ,
p_PARTY_ID => l_upd_csc_cust_plans_rec.PARTY_ID ,
p_CUST_ACCOUNT_ID => l_upd_csc_cust_plans_rec.CUST_ACCOUNT_ID ,
-- p_CUST_ACCOUNT_ORG => l_upd_csc_cust_plans_rec.CUST_ACCOUNT_ORG ,
p_PLAN_STATUS_CODE => l_upd_csc_cust_plans_rec.PLAN_STATUS_CODE ,
p_REQUEST_ID => l_upd_csc_cust_plans_rec.REQUEST_ID ,
p_PROGRAM_APPLICATION_ID => l_upd_csc_cust_plans_rec.PROGRAM_APPLICATION_ID ,
p_PROGRAM_ID => l_upd_csc_cust_plans_rec.PROGRAM_ID ,
p_PROGRAM_UPDATE_DATE => l_upd_csc_cust_plans_rec.PROGRAM_UPDATE_DATE ,
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_upd_csc_cust_plans_rec.ATTRIBUTE1 ,
p_ATTRIBUTE2 => l_upd_csc_cust_plans_rec.ATTRIBUTE2 ,
p_ATTRIBUTE3 => l_upd_csc_cust_plans_rec.ATTRIBUTE3 ,
p_ATTRIBUTE4 => l_upd_csc_cust_plans_rec.ATTRIBUTE4 ,
p_ATTRIBUTE5 => l_upd_csc_cust_plans_rec.ATTRIBUTE5 ,
p_ATTRIBUTE6 => l_upd_csc_cust_plans_rec.ATTRIBUTE6 ,
p_ATTRIBUTE7 => l_upd_csc_cust_plans_rec.ATTRIBUTE7 ,
p_ATTRIBUTE8 => l_upd_csc_cust_plans_rec.ATTRIBUTE8 ,
p_ATTRIBUTE9 => l_upd_csc_cust_plans_rec.ATTRIBUTE9 ,
p_ATTRIBUTE10 => l_upd_csc_cust_plans_rec.ATTRIBUTE10 ,
p_ATTRIBUTE11 => l_upd_csc_cust_plans_rec.ATTRIBUTE11 ,
p_ATTRIBUTE12 => l_upd_csc_cust_plans_rec.ATTRIBUTE12 ,
p_ATTRIBUTE13 => l_upd_csc_cust_plans_rec.ATTRIBUTE13 ,
p_ATTRIBUTE14 => l_upd_csc_cust_plans_rec.ATTRIBUTE14 ,
p_ATTRIBUTE15 => l_upd_csc_cust_plans_rec.ATTRIBUTE15 ,
p_ATTRIBUTE_CATEGORY => p_CSC_CUST_PLANS_rec.ATTRIBUTE_CATEGORY,
x_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID );
End Update_cust_plans;
Update_cust_plans(
P_Api_Version_Number => 1.0,
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 => NULL,
P_PLAN_ID => p_plan_id,
P_PARTY_ID => p_party_id_tbl(i).party_id,
P_CUST_ACCOUNT_ID => p_party_id_tbl(i).cust_account_id,
-- P_CUST_ACCOUNT_ORG => p_party_id_tbl(i).cust_account_org,
P_PLAN_STATUS_CODE => p_plan_status_code,
P_OBJECT_VERSION_NUMBER => p_party_id_tbl(i).object_version_number,
X_OBJECT_VERSION_NUMBER => x_obj_ver_num_tbl(i),
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data );
/******************* using regular update procedure
CSC_CUST_PLANS_PKG.LOCK_ROW (
p_plan_id => p_plan_id,
p_party_id => p_party_id_tbl(i).party_id,
p_cust_account_id => p_party_id_tbl(i).cust_account_id,
-- p_cust_account_org => p_party_id_tbl(i).cust_account_org,
p_object_version_number => p_party_id_tbl(i).object_version_number );
update csc_cust_plans
set plan_status_code = p_plan_status_code,
object_version_number = object_version_number + 1
where plan_id = p_plan_id
and party_id = p_party_id_tbl(i).party_id
and nvl(cust_account_id,0) = nvl(p_party_id_tbl(i).cust_account_id,0)
and object_version_number = p_party_id_tbl(i).object_version_number;
Update_cust_plans(
P_Api_Version_Number => 1.0,
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 => NULL,
P_PLAN_ID => p_plan_id,
P_PARTY_ID => p_party_id_tbl(i).party_id,
P_CUST_ACCOUNT_ID => p_party_id_tbl(i).cust_account_id,
-- P_CUST_ACCOUNT_ORG => p_party_id_tbl(i).cust_account_org,
P_PLAN_STATUS_CODE => p_plan_status_code,
P_OBJECT_VERSION_NUMBER => p_party_id_tbl(i).object_version_number,
X_OBJECT_VERSION_NUMBER => x_obj_ver_num_tbl(i),
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data );
/******* using regular update procedure to take of audit insert
CSC_CUST_PLANS_PKG.LOCK_ROW (
p_plan_id => p_plan_id,
p_party_id => p_party_id_tbl(i).party_id,
p_cust_account_id => p_party_id_tbl(i).cust_account_id,
-- p_cust_account_org => p_party_id_tbl(i).cust_account_org,
p_object_version_number => p_party_id_tbl(i).object_version_number );
update csc_cust_plans
set plan_status_code = p_plan_status_code,
object_version_number = object_version_number + 1
where plan_id = p_plan_id
and party_id = p_party_id_tbl(i).party_id
and nvl(cust_account_id,0) = nvl(p_party_id_tbl(i).cust_account_id,0)
and object_version_number = p_party_id_tbl(i).object_version_number;
PROCEDURE Update_for_customized_plans (
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2,
P_Commit IN VARCHAR2,
P_PLAN_ID IN NUMBER,
P_ORIGINAL_PLAN_ID IN NUMBER,
P_PARTY_ID IN NUMBER,
P_CUST_ACCOUNT_ID IN NUMBER := NULL,
-- P_CUST_ACCOUNT_ORG IN NUMBER := NULL,
P_OBJECT_VERSION_NUMBER IN 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
cursor c1 is
select *
from csc_cust_plans
where plan_id = p_original_plan_id
and party_id = p_party_id
and nvl(cust_account_id,0) = nvl(p_cust_account_id, nvl(cust_account_id,0))
and object_version_number = p_object_version_number;
l_api_name CONSTANT VARCHAR2(30) := 'Update_for_customized_plans';
SAVEPOINT UPDATE_FOR_CUSTMIZED_PLANS_PVT;
FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'API_MISSING_UPDATE_TARGET');
update csc_cust_plans
SET PLAN_ID = P_PLAN_ID,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
where plan_id = p_original_plan_id
and party_id = nvl(p_party_id, party_id)
and nvl(cust_account_id, 0) = nvl(p_cust_account_id, nvl(cust_account_id,0) )
and object_version_number = p_object_version_number;
CSC_CUST_PLANS_AUDIT_PKG.Insert_Row(
--px_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID,-- will be selected from the sequence.
p_PLAN_ID => P_PLAN_ID ,
p_PARTY_ID => P_PARTY_ID ,
p_CUST_ACCOUNT_ID => P_CUST_ACCOUNT_ID ,
-- p_CUST_ACCOUNT_ORG => P_CUST_ACCOUNT_ORG ,
p_PLAN_STATUS_CODE => c1rec.PLAN_STATUS_CODE ,
p_REQUEST_ID => c1rec.REQUEST_ID ,
p_PROGRAM_APPLICATION_ID => c1rec.PROGRAM_APPLICATION_ID ,
p_PROGRAM_ID => c1rec.PROGRAM_ID ,
p_PROGRAM_UPDATE_DATE => c1rec.PROGRAM_UPDATE_DATE ,
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 => c1rec.ATTRIBUTE1 ,
p_ATTRIBUTE2 => c1rec.ATTRIBUTE2 ,
p_ATTRIBUTE3 => c1rec.ATTRIBUTE3 ,
p_ATTRIBUTE4 => c1rec.ATTRIBUTE4 ,
p_ATTRIBUTE5 => c1rec.ATTRIBUTE5 ,
p_ATTRIBUTE6 => c1rec.ATTRIBUTE6 ,
p_ATTRIBUTE7 => c1rec.ATTRIBUTE7 ,
p_ATTRIBUTE8 => c1rec.ATTRIBUTE8 ,
p_ATTRIBUTE9 => c1rec.ATTRIBUTE9 ,
p_ATTRIBUTE10 => c1rec.ATTRIBUTE10 ,
p_ATTRIBUTE11 => c1rec.ATTRIBUTE11 ,
p_ATTRIBUTE12 => c1rec.ATTRIBUTE12 ,
p_ATTRIBUTE13 => c1rec.ATTRIBUTE13 ,
p_ATTRIBUTE14 => c1rec.ATTRIBUTE14 ,
p_ATTRIBUTE15 => c1rec.ATTRIBUTE15 ,
p_ATTRIBUTE_CATEGORY => c1rec.ATTRIBUTE_CATEGORY,
x_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID );
END update_for_customized_plans;
PROCEDURE Delete_cust_plans(
P_Api_Version_Number IN NUMBER,
P_Init_Msg_List IN VARCHAR2,
P_Commit IN VARCHAR2,
p_validation_level IN NUMBER ,
P_CUST_PLAN_ID IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
-- cursor to select the values of CSC_CUST_PLANS table which will be used as
-- input values to do insert into CSC_CUST_PLANS_AUDIT table.
cursor C1 ( C_CUST_PLAN_ID NUMBER ) is
SELECT CUST_PLAN_ID , PLAN_ID , PARTY_ID ,
CUST_ACCOUNT_ID , -- CUST_ACCOUNT_ORG , START_DATE_ACTIVE ,
END_DATE_ACTIVE , MANUAL_FLAG , PLAN_STATUS_CODE ,
REQUEST_ID , PROGRAM_APPLICATION_ID , PROGRAM_ID ,
PROGRAM_UPDATE_DATE , LAST_UPDATE_DATE , CREATION_DATE ,
LAST_UPDATED_BY , CREATED_BY , LAST_UPDATE_LOGIN ,
ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ,
ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 ,
ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 ,
ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 ,
ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 ,
ATTRIBUTE_CATEGORY , OBJECT_VERSION_NUMBER
FROM csc_cust_plans
WHERE cust_plan_id = c_cust_plan_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_cust_plans';
SAVEPOINT DELETE_CUST_PLANS_PVT;
-- Fetch the values of the cust_plans rec to insert into the audit table.
open c1 (p_cust_plan_id);
CSC_CUST_PLANS_PKG.Delete_Row(
p_CUST_PLAN_ID => p_CUST_PLAN_ID);
-- and with PLAN_STATUS_CODE = 'DELETED';
CSC_CUST_PLANS_AUDIT_PKG.Insert_Row(
--px_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID,-- will be selected from the sequence.
p_PLAN_ID => c1rec.PLAN_ID ,
p_PARTY_ID => c1rec.PARTY_ID ,
p_CUST_ACCOUNT_ID => c1rec.CUST_ACCOUNT_ID ,
-- p_CUST_ACCOUNT_ORG => c1rec.CUST_ACCOUNT_ORG ,
p_PLAN_STATUS_CODE => CSC_CORE_UTILS_PVT.REMOVE_PLAN,
p_REQUEST_ID => c1rec.REQUEST_ID ,
p_PROGRAM_APPLICATION_ID => c1rec.PROGRAM_APPLICATION_ID ,
p_PROGRAM_ID => c1rec.PROGRAM_ID ,
p_PROGRAM_UPDATE_DATE => c1rec.PROGRAM_UPDATE_DATE ,
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 => c1rec.ATTRIBUTE1 ,
p_ATTRIBUTE2 => c1rec.ATTRIBUTE2 ,
p_ATTRIBUTE3 => c1rec.ATTRIBUTE3 ,
p_ATTRIBUTE4 => c1rec.ATTRIBUTE4 ,
p_ATTRIBUTE5 => c1rec.ATTRIBUTE5 ,
p_ATTRIBUTE6 => c1rec.ATTRIBUTE6 ,
p_ATTRIBUTE7 => c1rec.ATTRIBUTE7 ,
p_ATTRIBUTE8 => c1rec.ATTRIBUTE8 ,
p_ATTRIBUTE9 => c1rec.ATTRIBUTE9 ,
p_ATTRIBUTE10 => c1rec.ATTRIBUTE10 ,
p_ATTRIBUTE11 => c1rec.ATTRIBUTE11 ,
p_ATTRIBUTE12 => c1rec.ATTRIBUTE12 ,
p_ATTRIBUTE13 => c1rec.ATTRIBUTE13 ,
p_ATTRIBUTE14 => c1rec.ATTRIBUTE14 ,
p_ATTRIBUTE15 => c1rec.ATTRIBUTE15 ,
p_ATTRIBUTE_CATEGORY => c1rec.ATTRIBUTE_CATEGORY,
x_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID );
End Delete_cust_plans;
sql_stmt := 'select cust_plan_id from csc_cust_plans where ' ||
' party_id = :1 and plan_id = :2 and cust_account_id is null ';
sql_stmt := 'select cust_plan_id from csc_cust_plans where ' ||
' party_id = :1 and plan_id = :2 and cust_account_id = :3 ';
select cust_plan_id
from CSC_CUST_PLANS
where cust_plan_id = p_cust_plan_id;
if (p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE) then
IF(p_CUST_PLAN_ID is NULL or p_CUST_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_b
where plan_id = p_plan_id;
select party_id
from jtf_parties_all_v
where party_id = p_party_id;
select cust_account_id, org_id
from jtf_cust_accounts_all_v
where party_id = p_party_id
and cust_account_id = p_cust_account_id;
select count(*)
from csc_lookups
where lookup_type = 'CSC_PLAN_STATUS'
and lookup_code = p_PLAN_STATUS_CODE
and sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate);
P_COLUMN_NAME => 'LAST_UPDATE_DATE',
P_COLUMN_VALUE => P_CSC_CUST_PLANS_REC.LAST_UPDATE_DATE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
P_COLUMN_NAME => 'LAST_UPDATED_BY',
P_COLUMN_VALUE => P_CSC_CUST_PLANS_REC.LAST_UPDATED_BY,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);