[Home] [Help]
PACKAGE BODY: APPS.CSC_RELATIONSHIP_PLANS_PVT
Source
1 PACKAGE BODY CSC_RELATIONSHIP_PLANS_PVT as
2 /* $Header: cscvrlpb.pls 115.28 2004/02/24 07:09:12 bhroy ship $ */
3 -- Start of Comments
4 -- Package name : CSC_RELATIONSHIP_PLANS_PVT
5 -- Purpose : This package contains all procedures and functions that are required
6 -- to create and modify plan headers and details and disable plans.
7 -- History
8 -- MM-DD-YYYY NAME MODIFICATIONS
9 -- 10-08-1999 dejoseph Created.
10 -- 12-08-1999 dejoseph 'Arcs'ed in for first code freeze.
11 -- 12-21-1999 dejoseph 'Arcs'ed in for second code freeze.
12 -- 01-03-2000 dejoseph 'Arcs'ed in for third code freeze. (10-JAN-2000)
13 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
14 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
15 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
16 -- 04-06-2000 dejoseph Modified validate_end_date_active proc. to denote the
17 -- correct operand '<' instead of '>'.
18 -- 04-10-2000 dejoseph Removed reference to cust_account_org in lieu of TCA's
19 -- decision to drop column org_id from hz_cust_accounts.
20 -- 04-10-2000 dejoseph Added logic not to allow update of end_date_active to < sysdate
21 -- when existing end_date_active is null;
22 -- 06-28-2001 dejoseph Corrected default values for parameters 'p_description' and
23 -- 'p_name' in procedure UPDATE_PLAN_HEADER to match with the
24 -- package spec. default values. Fix to bug# 1852893.
25 -- 02-18-2002 dejoseph Added changes to uptake new functionality for 11.5.8.
26 -- Ct. / Agent facing application
27 -- - Added new IN parameter END_USER_TYPE to procedures:
28 -- convert_columns_to_rec_type
29 -- create_plan_header
30 -- update_plan_header
31 -- - Added a new procedure VALIDATE_END_USER_TYPE
32 -- Added the dbdrv command.
33 -- 05-23-2002 dejoseph Added checkfile syntax.
34 -- 02-11-2002 bhroy Fixed Bug# 2412929,2250056 - CSC_CUST_PLANS table was not
35 -- getting updated when user changes End_Date_Active
36 --
37 -- 04-Nov-2002 kmotepal Added OSERROR command to fix GSCC warnings for patch 2633080
38 -- 13-Nov-2002 bhroy NOCOPY changes made
39 -- 11-27-2002 bhroy All the default values have been removed, also fixed Bug# 2250056
40 -- 16-JUN-2003 bhroy Modified CSC_CORE_UTILS_PVT.G_APP_SHORTNAME to CS, it was pointed to CSC, messages are in CS
41 -- 26-NOV-2003 bhroy Fixed bug# 2805474, update Start_Date_Active
42 -- 22-DEC-2003 bhroy Fixed bug# 3319977, 3319946, chanegd text messages, removed FND_API.G_MISS_NUM for G_CUSTOMIZED_ID
43 --
44 -- End of Comments
45
46
47 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_RELATIONSHIP_PLANS_PVT';
48 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscvrlpb.pls';
49
50 G_CUSTOMIZED_ID NUMBER;
51 -- G_CUSTOMIZED_ID NUMBER := FND_API.G_MISS_NUM;
52 -- used to get back the id of the record
53 -- created in CSC_CUSTOMIZED_PLANS table
54 -- when a customzied plan is created.
55
56 -- not using this global variable coz, when executing the validation procedure stand-alone
57 -- from forms, this errors out.
58 --G_CUST_PLANS_REC_CNT NUMBER := FND_API.G_MISS_NUM;
59 -- Used to keep a count on the number of records in
60 -- CSC_CUST_PLANS table. This count is used while performing
61 -- update on columns which requires that there should be no
62 -- association between plans and customers. ie. if a record
63 -- exist in this table, then do not allow the update on that
64 -- column. eg. use_for_cust_account, profile_check_id etc.
65
66 /*** PROCEDURE THAT CONVERTS INDIVIDUAL COLUMN PARAMETERS INTO RECORD TYPE FOR
67 PROCEDURE OVERLOADING ***/
68
69 PROCEDURE convert_columns_to_rec_type(
70 P_ROW_ID IN ROWID,
71 P_PLAN_ID IN NUMBER,
72 P_ORIGINAL_PLAN_ID IN NUMBER,
73 P_PLAN_GROUP_CODE IN VARCHAR2,
74 P_START_DATE_ACTIVE IN DATE,
75 P_END_DATE_ACTIVE IN DATE,
76 P_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
77 P_END_USER_TYPE IN VARCHAR2,
78 P_CUSTOMIZED_PLAN IN VARCHAR2,
79 P_PROFILE_CHECK_ID IN NUMBER,
80 P_RELATIONAL_OPERATOR IN VARCHAR2,
81 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
82 P_CRITERIA_VALUE_LOW IN VARCHAR2,
83 P_CREATION_DATE IN DATE,
84 P_LAST_UPDATE_DATE IN DATE,
85 P_CREATED_BY IN NUMBER,
86 P_LAST_UPDATED_BY IN NUMBER,
87 P_LAST_UPDATE_LOGIN IN NUMBER,
88 P_ATTRIBUTE1 IN VARCHAR2,
89 P_ATTRIBUTE2 IN VARCHAR2,
90 P_ATTRIBUTE3 IN VARCHAR2,
91 P_ATTRIBUTE4 IN VARCHAR2,
92 P_ATTRIBUTE5 IN VARCHAR2,
93 P_ATTRIBUTE6 IN VARCHAR2,
94 P_ATTRIBUTE7 IN VARCHAR2,
95 P_ATTRIBUTE8 IN VARCHAR2,
96 P_ATTRIBUTE9 IN VARCHAR2,
97 P_ATTRIBUTE10 IN VARCHAR2,
98 P_ATTRIBUTE11 IN VARCHAR2,
99 P_ATTRIBUTE12 IN VARCHAR2,
100 P_ATTRIBUTE13 IN VARCHAR2,
101 P_ATTRIBUTE14 IN VARCHAR2,
102 P_ATTRIBUTE15 IN VARCHAR2,
103 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
104 P_OBJECT_VERSION_NUMBER IN NUMBER,
105 X_PVT_CSC_PLAN_HEADERS_B_REC OUT NOCOPY CSC_PLAN_HEADERS_B_REC_TYPE )
106 IS
107
108 BEGIN
109 x_pvt_csc_plan_headers_b_rec.ROW_ID := p_row_id;
110 x_pvt_csc_plan_headers_b_rec.PLAN_ID := p_plan_id;
111 x_pvt_csc_plan_headers_b_rec.ORIGINAL_PLAN_ID := p_original_plan_id;
112 x_pvt_csc_plan_headers_b_rec.PLAN_GROUP_CODE := p_plan_group_code;
113 x_pvt_csc_plan_headers_b_rec.START_DATE_ACTIVE := p_start_date_active;
114 x_pvt_csc_plan_headers_b_rec.END_DATE_ACTIVE := p_end_date_active;
115 x_pvt_csc_plan_headers_b_rec.USE_FOR_CUST_ACCOUNT := p_use_for_cust_account;
116 x_pvt_csc_plan_headers_b_rec.END_USER_TYPE := p_end_user_type;
117 x_pvt_csc_plan_headers_b_rec.CUSTOMIZED_PLAN := p_customized_plan;
118 x_pvt_csc_plan_headers_b_rec.PROFILE_CHECK_ID := p_profile_check_id;
119 x_pvt_csc_plan_headers_b_rec.RELATIONAL_OPERATOR := p_relational_operator;
120 x_pvt_csc_plan_headers_b_rec.CRITERIA_VALUE_HIGH := p_criteria_value_high;
121 x_pvt_csc_plan_headers_b_rec.CRITERIA_VALUE_LOW := p_criteria_value_low;
122 x_pvt_csc_plan_headers_b_rec.CREATION_DATE := p_creation_date;
123 x_pvt_csc_plan_headers_b_rec.LAST_UPDATE_DATE := p_last_update_date;
124 x_pvt_csc_plan_headers_b_rec.CREATED_BY := p_created_by;
125 x_pvt_csc_plan_headers_b_rec.LAST_UPDATED_BY := p_last_updated_by;
126 x_pvt_csc_plan_headers_b_rec.LAST_UPDATE_LOGIN := p_last_update_login;
127 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE1 := p_attribute1;
128 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE2 := p_attribute2;
129 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE3 := p_attribute3;
130 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE4 := p_attribute4;
131 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE5 := p_attribute5;
132 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE6 := p_attribute6;
133 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE7 := p_attribute7;
134 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE8 := p_attribute8;
135 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE9 := p_attribute9;
136 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE10 := p_attribute10;
137 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE11 := p_attribute11;
138 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE12 := p_attribute12;
139 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE13 := p_attribute13;
140 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE14 := p_attribute14;
141 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE15 := p_attribute15;
142 x_pvt_csc_plan_headers_b_rec.ATTRIBUTE_CATEGORY := p_attribute_category;
143 x_pvt_csc_plan_headers_b_rec.OBJECT_VERSION_NUMBER:= p_object_version_number;
144
145 END convert_columns_to_rec_type;
146
147
148 /*********** OVERLOADED PROCEDURE TO TAKE COLUMNS INSTEAD OF RECORD TYPE *******/
149
150 PROCEDURE create_plan_header(
151 P_Api_Version_Number IN NUMBER,
152 P_Init_Msg_List IN VARCHAR2,
153 P_Commit IN VARCHAR2,
154 p_validation_level IN NUMBER,
155 P_ROW_ID IN ROWID,
156 P_PLAN_ID IN NUMBER,
157 P_ORIGINAL_PLAN_ID IN NUMBER,
158 P_PLAN_GROUP_CODE IN VARCHAR2,
159 P_START_DATE_ACTIVE IN DATE,
160 P_END_DATE_ACTIVE IN DATE,
161 P_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
162 P_END_USER_TYPE IN VARCHAR2,
163 P_CUSTOMIZED_PLAN IN VARCHAR2,
164 P_PROFILE_CHECK_ID IN NUMBER,
165 P_RELATIONAL_OPERATOR IN VARCHAR2,
166 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
167 P_CRITERIA_VALUE_LOW IN VARCHAR2,
168 P_CREATION_DATE IN DATE,
169 P_LAST_UPDATE_DATE IN DATE,
170 P_CREATED_BY IN NUMBER,
171 P_LAST_UPDATED_BY IN NUMBER,
172 P_LAST_UPDATE_LOGIN IN NUMBER,
173 P_ATTRIBUTE1 IN VARCHAR2,
174 P_ATTRIBUTE2 IN VARCHAR2,
175 P_ATTRIBUTE3 IN VARCHAR2,
176 P_ATTRIBUTE4 IN VARCHAR2,
177 P_ATTRIBUTE5 IN VARCHAR2,
178 P_ATTRIBUTE6 IN VARCHAR2,
179 P_ATTRIBUTE7 IN VARCHAR2,
180 P_ATTRIBUTE8 IN VARCHAR2,
181 P_ATTRIBUTE9 IN VARCHAR2,
182 P_ATTRIBUTE10 IN VARCHAR2,
183 P_ATTRIBUTE11 IN VARCHAR2,
184 P_ATTRIBUTE12 IN VARCHAR2,
185 P_ATTRIBUTE13 IN VARCHAR2,
186 P_ATTRIBUTE14 IN VARCHAR2,
187 P_ATTRIBUTE15 IN VARCHAR2,
188 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
189 P_OBJECT_VERSION_NUMBER IN NUMBER,
190 P_DESCRIPTION IN VARCHAR2,
191 P_NAME IN VARCHAR2,
192 P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
193 X_PLAN_ID OUT NOCOPY NUMBER,
194 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
195 X_Return_Status OUT NOCOPY VARCHAR2,
196 X_Msg_Count OUT NOCOPY NUMBER,
197 X_Msg_Data OUT NOCOPY VARCHAR2 )
198 IS
199 l_pvt_csc_plan_headers_b_rec CSC_PLAN_HEADERS_B_REC_TYPE;
200
201 BEGIN
202 convert_columns_to_rec_type(
203 p_row_id => p_row_id,
204 P_PLAN_ID => p_plan_id,
205 P_ORIGINAL_PLAN_ID => p_original_plan_id,
206 P_PLAN_GROUP_CODE => p_plan_group_code,
207 P_START_DATE_ACTIVE => p_start_date_active,
208 P_END_DATE_ACTIVE => p_end_date_active ,
209 P_USE_FOR_CUST_ACCOUNT => P_use_for_cust_account,
210 P_END_USER_TYPE => P_end_user_type,
211 P_CUSTOMIZED_PLAN => p_customized_plan,
212 P_PROFILE_CHECK_ID => p_profile_check_id,
213 P_RELATIONAL_OPERATOR => p_relational_operator,
214 P_CRITERIA_VALUE_HIGH => p_criteria_value_high,
215 P_CRITERIA_VALUE_LOW => p_criteria_value_low,
216 P_CREATION_DATE => p_creation_date,
217 P_LAST_UPDATE_DATE => p_last_update_date,
218 P_CREATED_BY => p_created_by,
219 P_LAST_UPDATED_BY => p_last_updated_by,
220 P_LAST_UPDATE_LOGIN => p_last_update_login,
221 P_ATTRIBUTE1 => p_attribute1,
222 P_ATTRIBUTE2 => p_attribute2,
223 P_ATTRIBUTE3 => p_attribute3,
224 P_ATTRIBUTE4 => p_attribute4,
225 P_ATTRIBUTE5 => p_attribute5,
226 P_ATTRIBUTE6 => p_attribute6,
227 P_ATTRIBUTE7 => p_attribute7,
228 P_ATTRIBUTE8 => p_attribute8,
229 P_ATTRIBUTE9 => p_attribute9,
230 P_ATTRIBUTE10 => p_attribute10,
231 P_ATTRIBUTE11 => p_attribute11,
232 P_ATTRIBUTE12 => p_attribute12,
233 P_ATTRIBUTE13 => p_attribute13,
234 P_ATTRIBUTE14 => p_attribute14,
235 P_ATTRIBUTE15 => p_attribute15,
236 P_ATTRIBUTE_CATEGORY => p_attribute_category,
237 P_OBJECT_VERSION_NUMBER => p_object_version_number,
238 X_PVT_CSC_PLAN_HEADERS_B_REC => l_pvt_csc_plan_headers_b_rec );
239
240 /*** CALL PROCEDURE WITH RECORD TYPE STRUCTURE ***/
241
242 create_plan_header(
243 p_api_version_number => p_api_version_number,
244 p_init_msg_list => p_init_msg_list,
245 p_commit => p_commit,
246 p_validation_level => p_validation_level,
247 p_csc_plan_headers_b_rec => l_pvt_csc_plan_headers_b_rec,
248 p_description => p_description,
249 p_name => p_name,
250 p_party_id_tbl => p_party_id_tbl,
251 x_plan_id => x_plan_id,
252 x_object_version_number => x_object_version_number,
253 x_return_status => x_return_status,
254 x_msg_count => x_msg_count,
255 x_msg_data => x_msg_data );
256
257 END create_plan_header;
258
259
260 PROCEDURE create_plan_header(
261 P_Api_Version_Number IN NUMBER,
262 P_Init_Msg_List IN VARCHAR2,
263 P_Commit IN VARCHAR2,
264 p_validation_level IN NUMBER,
265 P_CSC_PLAN_HEADERS_B_REC IN CSC_PLAN_HEADERS_B_REC_TYPE,
266 P_DESCRIPTION IN VARCHAR2,
267 P_NAME IN VARCHAR2,
268 P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
269 X_PLAN_ID OUT NOCOPY NUMBER,
270 X_Object_Version_Number OUT NOCOPY NUMBER,
271 X_Return_Status OUT NOCOPY VARCHAR2,
272 X_Msg_Count OUT NOCOPY NUMBER,
273 X_Msg_Data OUT NOCOPY VARCHAR2
274 )
275 IS
276 l_api_name CONSTANT VARCHAR2(30) := 'create_plan_header';
277 l_api_version_number CONSTANT NUMBER := 1.0;
278 l_cust_plan_id NUMBER;
279 -- The following assignment is done, because the correct values have to be passed into the
280 -- table handler insert pkg. If FND_API.G_MISS_ is being passed in, then these values have to
281 -- be nullified because FND_API.G_MISS_NUM is a huge value and does not fit into most db
282 -- columns. These assignments are done before the call to the table handler API.
283 l_ins_plan_headers_b_rec CSC_PLAN_HEADERS_B_REC_TYPE := P_CSC_PLAN_HEADERS_B_REC;
284 l_name VARCHAR2(90) := P_NAME;
285 l_description VARCHAR2(720) := P_DESCRIPTION;
286
287 -- A local party_id table type is declared because sometimes plans can be customized
288 -- at party level, in which case the cust_account_id and cust_account_org should be
289 -- nullified. 'IN' parameters cannot be assigned a value, so this local table type is
290 -- used.
291 --l_party_id_tbl CSC_PARTY_ID_TBL_TYPE := P_PARTY_ID_TBL;
292 l_party_id_tbl CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE := P_PARTY_ID_TBL;
293
294 x_cust_object_version_number NUMBER; -- used to get back the OUT NOCOPY value when updating
295 -- CSC_CUST_PLANS table, when customizing plans
296 BEGIN
297 -- Standard Start of API savepoint
298 SAVEPOINT CREATE_PLAN_HEADER_PVT;
299
300 -- Standard call to check for call compatibility.
301 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
302 p_api_version_number,
303 l_api_name,
304 G_PKG_NAME)
305 THEN
306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
307 END IF;
308
309 -- Initialize message list if p_init_msg_list is set to TRUE.
310 IF FND_API.to_Boolean( p_init_msg_list )
311 THEN
312 FND_MSG_PUB.initialize;
313 END IF;
314
315 -- Initialize API return status to SUCCESS
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 -- ******************************************************************
319 -- Validate Environment
320 -- ******************************************************************
321 IF FND_GLOBAL.User_Id IS NULL THEN
322 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
323 FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'UT_CANNOT_GET_PROFILE_VALUE');
324 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
325 --FND_MSG_PUB.ADD;
326 END IF;
327 RAISE FND_API.G_EXC_ERROR;
328 END IF;
329
330 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
331 -- Invoke validation procedures
332 Validate_csc_relationship_plan(
333 p_init_msg_list => FND_API.G_FALSE,
334 p_validation_level => p_validation_level,
335 p_validation_mode => CSC_CORE_UTILS_PVT.G_CREATE,
336 P_CSC_PLAN_HEADERS_B_REC => P_CSC_PLAN_HEADERS_B_REC,
337 P_DESCRIPTION => p_description,
338 P_NAME => p_name,
339 x_return_status => x_return_status,
340 x_msg_count => x_msg_count,
341 x_msg_data => x_msg_data);
342
343 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346 END IF;
347
348 --
349 -- Default checks to be performed, ir-respective of the validation level passed in.
350 --
351 -- if a customized plan is being created,ie. CUST0MIZED_PLAN = 'Y' then
352 -- 1. Validate party_id.
353 -- 2. If plan is being customized for account level, ie.USE_FOR_CUST_ACCOUNTS = 'Y' then
354 -- 2a. Validate CUST_ACCOUNT_ID and CUST_ACCOUNT_ORG
355 -- 3. Insert a record into CSC_CUSTOMIZED_PLANS table to save the
356 -- customer-plan relationship.
357 -- 4. Insert a record into CSC_CUST_PLANS table.
358 -- 5. Update CSC_CUST_PLANS table to show new customized plan_id where ever the customer
359 -- plan association exists
360 -- NOTE : If parent plan which is customized is a PARTY level plan, then allow the
361 -- customization only at PARTY level, else return back and error status. The same
362 -- applies to ACCOUNT level plans.
363
364 IF ( P_CSC_PLAN_HEADERS_B_REC.CUSTOMIZED_PLAN = 'Y' ) then
365 Validate_ORIGINAL_PLAN_ID(
366 p_init_msg_list => FND_API.G_FALSE,
367 p_validation_mode => CSC_CORE_UTILS_PVT.G_CREATE,
368 p_PLAN_ID => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
369 p_ORIGINAL_PLAN_ID => P_CSC_PLAN_HEADERS_B_REC.ORIGINAL_PLAN_ID,
370 p_CUSTOMIZED_PLAN => P_CSC_PLAN_HEADERS_B_REC.CUSTOMIZED_PLAN,
371 x_return_status => x_return_status,
372 x_msg_count => x_msg_count,
373 x_msg_data => x_msg_data);
374 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
375 raise FND_API.G_EXC_ERROR;
376 END IF;
377 if ( l_party_id_tbl.count = 0 ) then
378 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
379 fnd_message.set_name ('CS', 'CS_API_ALL_NULL_PARAMETER');
380 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
381 fnd_message.set_token('NULL_PARAM', 'PARTY_ID');
382 -- fnd_msg_pub.add;
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 RAISE FND_API.G_EXC_ERROR;
385 else
386 for i in 1..l_party_id_tbl.count
387 loop
388 if ( p_csc_plan_headers_b_rec.use_for_cust_account = 'N' ) then
389 if ( l_party_id_tbl(i).cust_account_id is not NULL )
390 -- OR l_party_id_tbl(i).cust_account_org is not NULL )
391 then
392 -- cannot allow update because parent plan is at account level and this
393 -- operation is trying to customize at party level.
394 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
395 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
396 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
397 fnd_message.set_token('VALUE', p_csc_plan_headers_b_rec.use_for_cust_account);
398 fnd_message.set_token('PARAMETER', 'USE_FOR_CUST_ACCOUNT');
399 x_return_status := FND_API.G_RET_STS_ERROR;
400 RAISE FND_API.G_EXC_ERROR;
401 end if;
402 end if;
403 CSC_CUST_PLANS_PVT.Validate_PARTY_ID (
404 P_Init_Msg_List => FND_API.G_TRUE,
405 P_Validation_mode => CSC_CORE_UTILS_PVT.G_CREATE,
406 P_PARTY_ID => l_party_id_tbl(i).party_id,
407 x_Return_Status => x_return_status,
408 X_Msg_Count => x_msg_count,
409 X_Msg_Data => x_msg_data );
410 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
411 RAISE FND_API.G_EXC_ERROR;
412 end if;
413
414 /********************** 04-10-2000
415
416 IF ( P_CSC_PLAN_HEADERS_B_REC.USE_FOR_CUST_ACCOUNT = 'Y' ) then
417 CSC_CUST_PLANS_PVT.Validate_CUST_ACC_ORG_ID (
418 P_Init_Msg_List => FND_API.G_TRUE,
419 P_Validation_mode => CSC_CORE_UTILS_PVT.G_CREATE,
420 P_PARTY_ID => l_party_id_tbl(i).party_id,
421 P_CUST_ACCOUNT_ID => l_party_id_tbl(i).cust_account_id,
422 -- CUST_ACCOUNT_ORG => l_party_id_tbl(i).cust_account_org,
423 X_Return_Status => x_return_status,
424 X_Msg_Count => x_msg_count,
425 X_Msg_Data => x_msg_data );
426 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
427 RAISE FND_API.G_EXC_ERROR;
428 end if;
429 END IF; -- end of use_for_cust_account = 'Y'
430
431 ***********************************/
432 end loop;
433 end if; -- end for count = 0
434 END IF; -- end for customized_plan = 'Y'
435
436 -- Get the final values of all the parameters that have to be passed into the table
437 -- handler insert package; The decode is done here rather than in the insert statement
438 -- of the table handler, because, when submitting the insert sql. statement to the SGA,
439 -- we need to pass an identical insert statement every time. This saves SGA space and
440 -- increases performance by using shared pool.
441
442 /********** This check is not required, NULL can be passed directly as parameter value
443
444 if ( p_csc_plan_headers_b_rec.original_plan_id = FND_API.G_MISS_NUM ) then
445 l_ins_plan_headers_b_rec.original_plan_id := NULL;
446 end if;
447
448 if ( p_csc_plan_headers_b_rec.plan_group_code = FND_API.G_MISS_CHAR ) then
449 l_ins_plan_headers_b_rec.plan_group_code := NULL;
450 end if;
451
452 if ( p_csc_plan_headers_b_rec.start_date_active = FND_API.G_MISS_DATE ) then
453 l_ins_plan_headers_b_rec.start_date_active := NULL;
454 end if;
455
456 if ( p_csc_plan_headers_b_rec.end_date_active = FND_API.G_MISS_DATE ) then
457 l_ins_plan_headers_b_rec.end_date_active := NULL;
458 end if;
459
460 if ( p_csc_plan_headers_b_rec.use_for_cust_account = FND_API.G_MISS_CHAR ) then
461 l_ins_plan_headers_b_rec.use_for_cust_account := NULL;
462 end if;
463
464 if ( p_csc_plan_headers_b_rec.end_user_type = CSC_CORE_UTILS_PVT.G_MISS_CHAR ) then
465 l_ins_plan_headers_b_rec.end_user_type := NULL;
466 end if;
467
468 if ( p_csc_plan_headers_b_rec.customized_plan = FND_API.G_MISS_CHAR ) then
469 l_ins_plan_headers_b_rec.customized_plan := NULL;
470 end if;
471
472 if ( p_csc_plan_headers_b_rec.profile_check_id = FND_API.G_MISS_NUM ) then
473 l_ins_plan_headers_b_rec.profile_check_id := NULL;
474 end if;
475
476 if ( p_csc_plan_headers_b_rec.relational_operator = FND_API.G_MISS_CHAR ) then
477 l_ins_plan_headers_b_rec.relational_operator := NULL;
478 end if;
479
480 if ( p_csc_plan_headers_b_rec.criteria_value_high = FND_API.G_MISS_CHAR ) then
481 l_ins_plan_headers_b_rec.criteria_value_high := NULL;
482 end if;
483
484 if ( p_csc_plan_headers_b_rec.criteria_value_low = FND_API.G_MISS_CHAR ) then
485 l_ins_plan_headers_b_rec.criteria_value_low := NULL;
486 end if;
487
488 if ( p_csc_plan_headers_b_rec.attribute1 = FND_API.G_MISS_CHAR ) then
489 l_ins_plan_headers_b_rec.attribute1 := NULL;
490 end if;
491
492 if ( p_csc_plan_headers_b_rec.attribute2 = FND_API.G_MISS_CHAR ) then
493 l_ins_plan_headers_b_rec.attribute2 := NULL;
494 end if;
495
496 if ( p_csc_plan_headers_b_rec.attribute3 = FND_API.G_MISS_CHAR ) then
497 l_ins_plan_headers_b_rec.attribute3 := NULL;
498 end if;
499
500 if ( p_csc_plan_headers_b_rec.attribute4 = FND_API.G_MISS_CHAR ) then
501 l_ins_plan_headers_b_rec.attribute4 := NULL;
502 end if;
503
504 if ( p_csc_plan_headers_b_rec.attribute5 = FND_API.G_MISS_CHAR ) then
505 l_ins_plan_headers_b_rec.attribute5 := NULL;
506 end if;
507
508 if ( p_csc_plan_headers_b_rec.attribute6 = FND_API.G_MISS_CHAR ) then
509 l_ins_plan_headers_b_rec.attribute6 := NULL;
510 end if;
511
512 if ( p_csc_plan_headers_b_rec.attribute7 = FND_API.G_MISS_CHAR ) then
513 l_ins_plan_headers_b_rec.attribute7 := NULL;
514 end if;
515
516 if ( p_csc_plan_headers_b_rec.attribute8 = FND_API.G_MISS_CHAR ) then
517 l_ins_plan_headers_b_rec.attribute8 := NULL;
518 end if;
519
520 if ( p_csc_plan_headers_b_rec.attribute9 = FND_API.G_MISS_CHAR ) then
521 l_ins_plan_headers_b_rec.attribute9 := NULL;
522 end if;
523
524 if ( p_csc_plan_headers_b_rec.attribute10 = FND_API.G_MISS_CHAR ) then
525 l_ins_plan_headers_b_rec.attribute10 := NULL;
526 end if;
527
528 if ( p_csc_plan_headers_b_rec.attribute11 = FND_API.G_MISS_CHAR ) then
529 l_ins_plan_headers_b_rec.attribute11 := NULL;
530 end if;
531
532 if ( p_csc_plan_headers_b_rec.attribute12 = FND_API.G_MISS_CHAR ) then
533 l_ins_plan_headers_b_rec.attribute12 := NULL;
534 end if;
535
536 if ( p_csc_plan_headers_b_rec.attribute13 = FND_API.G_MISS_CHAR ) then
537 l_ins_plan_headers_b_rec.attribute13 := NULL;
538 end if;
539
540 if ( p_csc_plan_headers_b_rec.attribute14 = FND_API.G_MISS_CHAR ) then
541 l_ins_plan_headers_b_rec.attribute14 := NULL;
542 end if;
543
544 if ( p_csc_plan_headers_b_rec.attribute15 = FND_API.G_MISS_CHAR ) then
545 l_ins_plan_headers_b_rec.attribute15 := NULL;
546 end if;
547
548 if ( p_csc_plan_headers_b_rec.attribute_category = FND_API.G_MISS_CHAR ) then
549 l_ins_plan_headers_b_rec.attribute_category := NULL;
550 end if;
551
552 **************************/
553
554 if ( p_name <> FND_API.G_MISS_CHAR ) then
555 l_name:= p_name;
556 end if;
557
558 if ( p_description <> FND_API.G_MISS_CHAR ) then
559 l_description:= p_description;
560 end if;
561
562 -- Invoke table handler(CSC_PLAN_HEADERS_B_PKG.Insert_Row)
563 x_plan_id := p_csc_plan_headers_b_rec.plan_id;
564 CSC_PLAN_HEADERS_B_PKG.Insert_Row(
565 px_PLAN_ID => x_plan_id,
566 p_ORIGINAL_PLAN_ID => l_ins_plan_headers_b_rec.ORIGINAL_PLAN_ID,
567 p_PLAN_GROUP_CODE => l_ins_plan_headers_b_rec.PLAN_GROUP_CODE,
568 p_START_DATE_ACTIVE => l_ins_plan_headers_b_rec.START_DATE_ACTIVE,
569 p_END_DATE_ACTIVE => l_ins_plan_headers_b_rec.END_DATE_ACTIVE,
570 p_USE_FOR_CUST_ACCOUNT => l_ins_plan_headers_b_rec.USE_FOR_CUST_ACCOUNT,
571 p_END_USER_TYPE => l_ins_plan_headers_b_rec.END_USER_TYPE,
572 p_CUSTOMIZED_PLAN => l_ins_plan_headers_b_rec.CUSTOMIZED_PLAN,
573 p_PROFILE_CHECK_ID => l_ins_plan_headers_b_rec.PROFILE_CHECK_ID,
574 p_RELATIONAL_OPERATOR => l_ins_plan_headers_b_rec.RELATIONAL_OPERATOR,
575 p_CRITERIA_VALUE_HIGH => l_ins_plan_headers_b_rec.CRITERIA_VALUE_HIGH,
576 p_CRITERIA_VALUE_LOW => l_ins_plan_headers_b_rec.CRITERIA_VALUE_LOW,
577 p_CREATION_DATE => SYSDATE,
578 p_LAST_UPDATE_DATE => SYSDATE,
579 p_CREATED_BY => FND_GLOBAL.USER_ID,
580 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
581 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
582 p_ATTRIBUTE1 => l_ins_plan_headers_b_rec.ATTRIBUTE1,
583 p_ATTRIBUTE2 => l_ins_plan_headers_b_rec.ATTRIBUTE2,
584 p_ATTRIBUTE3 => l_ins_plan_headers_b_rec.ATTRIBUTE3,
585 p_ATTRIBUTE4 => l_ins_plan_headers_b_rec.ATTRIBUTE4,
586 p_ATTRIBUTE5 => l_ins_plan_headers_b_rec.ATTRIBUTE5,
587 p_ATTRIBUTE6 => l_ins_plan_headers_b_rec.ATTRIBUTE6,
588 p_ATTRIBUTE7 => l_ins_plan_headers_b_rec.ATTRIBUTE7,
589 p_ATTRIBUTE8 => l_ins_plan_headers_b_rec.ATTRIBUTE8,
590 p_ATTRIBUTE9 => l_ins_plan_headers_b_rec.ATTRIBUTE9,
591 p_ATTRIBUTE10 => l_ins_plan_headers_b_rec.ATTRIBUTE10,
592 p_ATTRIBUTE11 => l_ins_plan_headers_b_rec.ATTRIBUTE11,
593 p_ATTRIBUTE12 => l_ins_plan_headers_b_rec.ATTRIBUTE12,
594 p_ATTRIBUTE13 => l_ins_plan_headers_b_rec.ATTRIBUTE13,
595 p_ATTRIBUTE14 => l_ins_plan_headers_b_rec.ATTRIBUTE14,
596 p_ATTRIBUTE15 => l_ins_plan_headers_b_rec.ATTRIBUTE15,
597 p_ATTRIBUTE_CATEGORY => l_ins_plan_headers_b_rec.ATTRIBUTE_CATEGORY,
598 p_DESCRIPTION => l_DESCRIPTION,
599 p_NAME => l_NAME,
600 x_OBJECT_VERSION_NUMBER => x_object_version_number );
601
602 IF ( p_csc_plan_headers_b_rec.customized_plan = 'Y' ) then
603 for i in 1..l_party_id_tbl.count
604 LOOP
605 CSC_CUSTOMIZED_PLANS_PKG.INSERT_ROW(
606 px_id => g_customized_id,
607 p_plan_id => x_plan_id,
608 p_party_id => l_party_id_tbl(i).party_id,
609 p_cust_account_id => l_party_id_tbl(i).cust_account_id );
610 --p_cust_account_org => l_party_id_tbl(i).cust_account_org );
611
612 -- Call update_for_customized_plans to do the update of the plan_id for all the parties
613 -- with the new plan_id that was customzied.
614 CSC_CUST_PLANS_PVT.Update_for_customized_plans (
615 P_Api_Version_Number => 1.0,
616 P_Init_Msg_List => FND_API.G_TRUE,
617 P_Commit => p_commit,
618 P_PLAN_ID => x_plan_id,
619 P_ORIGINAL_PLAN_ID => p_csc_plan_headers_b_rec.original_plan_id,
620 P_PARTY_ID => l_party_id_tbl(i).party_id,
621 P_CUST_ACCOUNT_ID => l_party_id_tbl(i).cust_account_id,
622 -- P_CUST_ACCOUNT_ORG => l_party_id_tbl(i).cust_account_org,
623 P_OBJECT_VERSION_NUMBER => l_party_id_tbl(i).object_version_number,
624 X_OBJECT_VERSION_NUMBER => x_cust_object_version_number,
625 X_Return_Status => x_return_status,
626 X_Msg_Count => x_msg_count,
627 X_Msg_Data => x_msg_data );
628 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
629 RAISE FND_API.G_EXC_ERROR;
630 end if;
631 END LOOP;
632 END IF;
633
634 -- Standard check for p_commit
635 IF FND_API.to_Boolean( p_commit )
636 THEN
637 COMMIT WORK;
638 END IF;
639
640 -- Standard call to get message count and if count is 1, get message info.
641 FND_MSG_PUB.Count_And_Get
642 ( p_encoded => FND_API.G_FALSE,
643 p_count => x_msg_count,
644 p_data => x_msg_data );
645
646 EXCEPTION
647 WHEN FND_API.G_EXC_ERROR THEN
648 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
649 P_API_NAME => L_API_NAME,
650 P_PKG_NAME => G_PKG_NAME,
651 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
652 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
653 X_MSG_COUNT => X_MSG_COUNT,
654 X_MSG_DATA => X_MSG_DATA,
655 X_RETURN_STATUS => X_RETURN_STATUS);
656 APP_EXCEPTION.RAISE_EXCEPTION;
657
658 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
659 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
660 P_API_NAME => L_API_NAME,
661 P_PKG_NAME => G_PKG_NAME,
662 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
663 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
664 X_MSG_COUNT => X_MSG_COUNT,
665 X_MSG_DATA => X_MSG_DATA,
666 X_RETURN_STATUS => X_RETURN_STATUS);
667 APP_EXCEPTION.RAISE_EXCEPTION;
668
669 WHEN OTHERS THEN
670 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
671 P_API_NAME => L_API_NAME,
672 P_PKG_NAME => G_PKG_NAME,
673 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
674 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
675 X_MSG_COUNT => X_MSG_COUNT,
676 X_MSG_DATA => X_MSG_DATA,
677 X_RETURN_STATUS => X_RETURN_STATUS);
678 APP_EXCEPTION.RAISE_EXCEPTION;
679
680 End create_plan_header;
681
682
683 /*********** OVERLOADED PROCEDURE TO TAKE COLUMNS INSTEAD OF RECORD TYPE *******/
684
685 PROCEDURE update_plan_header(
686 P_Api_Version_Number IN NUMBER,
687 P_Init_Msg_List IN VARCHAR2,
688 P_Commit IN VARCHAR2,
689 P_Validation_level IN NUMBER,
690 P_ROW_ID IN ROWID,
691 P_PLAN_ID IN NUMBER,
692 P_ORIGINAL_PLAN_ID IN NUMBER,
693 P_PLAN_GROUP_CODE IN VARCHAR2,
694 P_START_DATE_ACTIVE IN DATE,
695 P_END_DATE_ACTIVE IN DATE,
696 P_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
697 P_END_USER_TYPE IN VARCHAR2,
698 P_CUSTOMIZED_PLAN IN VARCHAR2,
699 P_PROFILE_CHECK_ID IN NUMBER,
700 P_RELATIONAL_OPERATOR IN VARCHAR2,
701 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
702 P_CRITERIA_VALUE_LOW IN VARCHAR2,
703 P_CREATION_DATE IN DATE,
704 P_LAST_UPDATE_DATE IN DATE,
705 P_CREATED_BY IN NUMBER,
706 P_LAST_UPDATED_BY IN NUMBER,
707 P_LAST_UPDATE_LOGIN IN NUMBER,
708 P_ATTRIBUTE1 IN VARCHAR2,
709 P_ATTRIBUTE2 IN VARCHAR2,
710 P_ATTRIBUTE3 IN VARCHAR2,
711 P_ATTRIBUTE4 IN VARCHAR2,
712 P_ATTRIBUTE5 IN VARCHAR2,
713 P_ATTRIBUTE6 IN VARCHAR2,
714 P_ATTRIBUTE7 IN VARCHAR2,
715 P_ATTRIBUTE8 IN VARCHAR2,
716 P_ATTRIBUTE9 IN VARCHAR2,
717 P_ATTRIBUTE10 IN VARCHAR2,
718 P_ATTRIBUTE11 IN VARCHAR2,
719 P_ATTRIBUTE12 IN VARCHAR2,
720 P_ATTRIBUTE13 IN VARCHAR2,
721 P_ATTRIBUTE14 IN VARCHAR2,
722 P_ATTRIBUTE15 IN VARCHAR2,
723 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
724 P_OBJECT_VERSION_NUMBER IN NUMBER,
725 P_DESCRIPTION IN VARCHAR2,
726 P_NAME IN VARCHAR2,
727 P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
728 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
729 X_Return_Status OUT NOCOPY VARCHAR2,
730 X_Msg_Count OUT NOCOPY NUMBER,
731 X_Msg_Data OUT NOCOPY VARCHAR2
732 )
733 IS
734 l_pvt_csc_plan_headers_b_rec CSC_PLAN_HEADERS_B_REC_TYPE;
735
736 BEGIN
737 convert_columns_to_rec_type(
738 P_ROW_ID => p_row_id,
739 P_PLAN_ID => p_plan_id,
740 P_ORIGINAL_PLAN_ID => p_original_plan_id,
741 P_PLAN_GROUP_CODE => p_plan_group_code,
742 P_START_DATE_ACTIVE => p_start_date_active,
743 P_END_DATE_ACTIVE => p_end_date_active ,
744 P_USE_FOR_CUST_ACCOUNT => P_use_for_cust_account,
745 P_END_USER_TYPE => P_end_user_type,
746 P_CUSTOMIZED_PLAN => p_customized_plan,
747 P_PROFILE_CHECK_ID => p_profile_check_id,
748 P_RELATIONAL_OPERATOR => p_relational_operator,
749 P_CRITERIA_VALUE_HIGH => p_criteria_value_high,
750 P_CRITERIA_VALUE_LOW => p_criteria_value_low,
751 P_CREATION_DATE => p_creation_date,
752 P_LAST_UPDATE_DATE => p_last_update_date,
753 P_CREATED_BY => p_created_by,
754 P_LAST_UPDATED_BY => p_last_updated_by,
755 P_LAST_UPDATE_LOGIN => p_last_update_login,
756 P_ATTRIBUTE1 => p_attribute1,
757 P_ATTRIBUTE2 => p_attribute2,
758 P_ATTRIBUTE3 => p_attribute3,
759 P_ATTRIBUTE4 => p_attribute4,
760 P_ATTRIBUTE5 => p_attribute5,
761 P_ATTRIBUTE6 => p_attribute6,
762 P_ATTRIBUTE7 => p_attribute7,
763 P_ATTRIBUTE8 => p_attribute8,
764 P_ATTRIBUTE9 => p_attribute9,
765 P_ATTRIBUTE10 => p_attribute10,
766 P_ATTRIBUTE11 => p_attribute11,
767 P_ATTRIBUTE12 => p_attribute12,
768 P_ATTRIBUTE13 => p_attribute13,
769 P_ATTRIBUTE14 => p_attribute14,
770 P_ATTRIBUTE15 => p_attribute15,
771 P_ATTRIBUTE_CATEGORY => p_attribute_category,
772 P_OBJECT_VERSION_NUMBER => p_object_version_number,
773 X_PVT_CSC_PLAN_HEADERS_B_REC => l_pvt_csc_plan_headers_b_rec );
774
775 /*** CALL PROCEDURE WITH RECORD TYPE STRUCTURE ***/
776 update_plan_header(
777 P_Api_Version_Number => p_api_version_number,
778 P_Init_Msg_List => p_init_msg_list,
779 P_Commit => p_commit,
780 P_Validation_level => p_validation_level,
781 P_CSC_PLAN_HEADERS_B_REC => l_pvt_csc_plan_headers_b_rec,
782 P_DESCRIPTION => p_description,
783 P_NAME => p_name,
784 P_PARTY_ID_TBL => p_party_id_tbl,
785 X_Object_Version_number => x_object_version_number,
786 X_Return_Status => x_return_status,
787 X_Msg_Count => x_msg_count,
788 X_Msg_Data => x_msg_data );
789
790 END update_plan_header;
791
792
793 PROCEDURE Update_plan_header(
794 P_Api_Version_Number IN NUMBER,
795 P_Init_Msg_List IN VARCHAR2,
796 P_Commit IN VARCHAR2,
797 p_validation_level IN NUMBER,
798 P_CSC_PLAN_HEADERS_B_REC IN CSC_PLAN_HEADERS_B_REC_TYPE,
799 P_DESCRIPTION IN VARCHAR2,
800 P_NAME IN VARCHAR2,
801 P_PARTY_ID_TBL IN CSC_CUST_PLANS_PVT.CSC_PARTY_ID_TBL_TYPE,
802 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
803 X_Return_Status OUT NOCOPY VARCHAR2,
804 X_Msg_Count OUT NOCOPY NUMBER,
805 X_Msg_Data OUT NOCOPY VARCHAR2
806 )
807 IS
808 Cursor c_get_csc_plan_headers( c_PLAN_ID Number ) IS
809 Select rowid, PLAN_ID, ORIGINAL_PLAN_ID,
810 PLAN_GROUP_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE,
811 USE_FOR_CUST_ACCOUNT, END_USER_TYPE, CUSTOMIZED_PLAN,
812 PROFILE_CHECK_ID, RELATIONAL_OPERATOR, CRITERIA_VALUE_HIGH,
813 CRITERIA_VALUE_LOW, CREATION_DATE, LAST_UPDATE_DATE,
814 CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
815 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
816 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
817 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
818 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
819 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
820 ATTRIBUTE_CATEGORY, OBJECT_VERSION_NUMBER, NAME,
821 DESCRIPTION
822 From CSC_PLAN_HEADERS_VL
823 where plan_id = c_plan_id;
824
825 l_api_name CONSTANT VARCHAR2(30) := 'Update_plan_header';
826 l_api_version_number CONSTANT NUMBER := 1.0;
827
828 -- Local Variables
829 l_ref_PLAN_HEADER_rec CSC_relationship_plans_PVT.CSC_PLAN_HEADERS_B_REC_TYPE;
830 l_upd_plan_headers_b_rec CSC_relationship_plans_PVT.CSC_PLAN_HEADERS_B_REC_TYPE :=
831 P_CSC_PLAN_HEADERS_B_REC;
832 l_name VARCHAR2(90) := p_name;
833 l_description VARCHAR2(720):= p_description;
834 l_rowid ROWID;
835
836 Cursor C_Get_cust_plans IS
837 Select CUST_PLAN_ID, PARTY_ID, CUST_ACCOUNT_ID, OBJECT_VERSION_NUMBER
838 From CSC_CUST_PLANS
839 WHERE PLAN_ID = nvl(p_csc_plan_headers_b_rec.plan_id, plan_id)
840 AND plan_status_code not in ('MERGED', 'TRANSFERED') ORDER BY OBJECT_VERSION_NUMBER;
841 l_ref_CSC_CUST_PLANS_rec CSC_cust_plans_PVT.CSC_CUST_PLANS_Rec_Type;
842
843 BEGIN
844 -- Standard Start of API savepoint
845 SAVEPOINT UPDATE_PLAN_HEADER_PVT;
846
847 -- Standard call to check for call compatibility.
848 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
849 p_api_version_number,
850 l_api_name,
851 G_PKG_NAME)
852 THEN
853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
854 END IF;
855
856 -- Initialize message list if p_init_msg_list is set to TRUE.
857 IF FND_API.to_Boolean( p_init_msg_list )
858 THEN
859 FND_MSG_PUB.initialize;
860 END IF;
861
862 -- Initialize API return status to SUCCESS
863 x_return_status := FND_API.G_RET_STS_SUCCESS;
864
865 CSC_PLAN_HEADERS_B_PKG.LOCK_ROW(
866 P_PLAN_ID => p_csc_plan_headers_b_rec.plan_id,
867 P_OBJECT_VERSION_NUMBER => p_csc_plan_headers_b_rec.OBJECT_VERSION_NUMBER );
868
869 Open c_get_csc_plan_headers( p_csc_plan_headers_b_rec.PLAN_ID );
870
871 Fetch c_get_csc_plan_headers into
872 l_rowid,
873 l_ref_PLAN_HEADER_rec.PLAN_ID,
874 l_ref_PLAN_HEADER_rec.ORIGINAL_PLAN_ID,
875 l_ref_PLAN_HEADER_rec.PLAN_GROUP_CODE,
876 l_ref_PLAN_HEADER_rec.START_DATE_ACTIVE,
877 l_ref_PLAN_HEADER_rec.END_DATE_ACTIVE,
878 l_ref_PLAN_HEADER_rec.USE_FOR_CUST_ACCOUNT,
879 l_ref_PLAN_HEADER_rec.END_USER_TYPE,
880 l_ref_PLAN_HEADER_rec.CUSTOMIZED_PLAN,
881 l_ref_PLAN_HEADER_rec.PROFILE_CHECK_ID,
882 l_ref_PLAN_HEADER_rec.RELATIONAL_OPERATOR,
883 l_ref_PLAN_HEADER_rec.CRITERIA_VALUE_HIGH,
884 l_ref_PLAN_HEADER_rec.CRITERIA_VALUE_LOW,
885 l_ref_PLAN_HEADER_rec.CREATION_DATE,
886 l_ref_PLAN_HEADER_rec.LAST_UPDATE_DATE,
887 l_ref_PLAN_HEADER_rec.CREATED_BY,
888 l_ref_PLAN_HEADER_rec.LAST_UPDATED_BY,
889 l_ref_PLAN_HEADER_rec.LAST_UPDATE_LOGIN,
890 l_ref_PLAN_HEADER_rec.ATTRIBUTE1,
891 l_ref_PLAN_HEADER_rec.ATTRIBUTE2,
892 l_ref_PLAN_HEADER_rec.ATTRIBUTE3,
893 l_ref_PLAN_HEADER_rec.ATTRIBUTE4,
894 l_ref_PLAN_HEADER_rec.ATTRIBUTE5,
895 l_ref_PLAN_HEADER_rec.ATTRIBUTE6,
896 l_ref_PLAN_HEADER_rec.ATTRIBUTE7,
897 l_ref_PLAN_HEADER_rec.ATTRIBUTE8,
898 l_ref_PLAN_HEADER_rec.ATTRIBUTE9,
899 l_ref_PLAN_HEADER_rec.ATTRIBUTE10,
900 l_ref_PLAN_HEADER_rec.ATTRIBUTE11,
901 l_ref_PLAN_HEADER_rec.ATTRIBUTE12,
902 l_ref_PLAN_HEADER_rec.ATTRIBUTE13,
903 l_ref_PLAN_HEADER_rec.ATTRIBUTE14,
904 l_ref_PLAN_HEADER_rec.ATTRIBUTE15,
905 l_ref_PLAN_HEADER_rec.ATTRIBUTE_CATEGORY,
906 l_ref_PLAN_HEADER_rec.OBJECT_VERSION_NUMBER,
907 l_name,
908 l_description;
909
910 If ( c_get_csc_plan_headers%NOTFOUND) Then
911 --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
912 --THEN
913 FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'API_MISSING_UPDATE_TARGET');
914 FND_MESSAGE.Set_Token ('INFO', 'csc_relationship_plans', FALSE);
915 -- FND_MSG_PUB.Add;
916 --END IF;
917 Close c_get_csc_plan_headers;
918 raise FND_API.G_EXC_ERROR;
919 END IF;
920 Close c_get_csc_plan_headers;
921
922 -- Check Whether record has been changed by someone else
923 -- This check is not neccessary because we do the locking based on the plan_id and the
924 -- object_version_number. This lock would fail if any other user updates the selected
925 -- record because the object_version_number would be different.
926
927 If ( P_CSC_PLAN_HEADERS_B_rec.object_version_number <>
928 l_ref_PLAN_HEADER_rec.OBJECT_VERSION_NUMBER ) THEN
929 --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
930 --THEN
931 FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'API_RECORD_CHANGED');
932 FND_MESSAGE.Set_Token('INFO', 'csc_relationship_plans', FALSE);
933 -- FND_MSG_PUB.ADD;
934 --END IF;
935 raise FND_API.G_EXC_ERROR;
936 End if;
937
938 -- Get the final values of all the parameters that have to be passed into the table
939 -- handler update package; The decode is done here rather than in the update statement
940 -- of the table handler, because, when submitting the update sql. statement to the SGA,
941 -- we need to pass an identical update statement every time. This saves SGA space and
942 -- increases performance by using shared pool.
943
944 l_upd_plan_headers_b_rec.original_plan_id := CSC_CORE_UTILS_PVT.Get_G_Miss_Num(p_csc_plan_headers_b_rec.original_plan_id, l_ref_plan_header_rec.original_plan_id);
945
946 l_upd_plan_headers_b_rec.plan_group_code := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.plan_group_code, l_ref_plan_header_rec.plan_group_code);
947
948 l_upd_plan_headers_b_rec.start_date_active := CSC_CORE_UTILS_PVT.Get_G_Miss_Date(p_csc_plan_headers_b_rec.start_date_active, l_ref_plan_header_rec.start_date_active);
949
950 l_upd_plan_headers_b_rec.end_date_active := CSC_CORE_UTILS_PVT.Get_G_Miss_Date(p_csc_plan_headers_b_rec.end_date_active, l_ref_plan_header_rec.end_date_active);
951
952 l_upd_plan_headers_b_rec.use_for_cust_account := p_csc_plan_headers_b_rec.use_for_cust_account;
953
954 l_upd_plan_headers_b_rec.end_user_type := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.end_user_type, l_ref_plan_header_rec.end_user_type);
955
956 l_upd_plan_headers_b_rec.customized_plan := p_csc_plan_headers_b_rec.customized_plan;
957
958 l_upd_plan_headers_b_rec.profile_check_id := p_csc_plan_headers_b_rec.profile_check_id;
959
960 l_upd_plan_headers_b_rec.relational_operator := p_csc_plan_headers_b_rec.relational_operator;
961
962 l_upd_plan_headers_b_rec.criteria_value_high := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.criteria_value_high, l_ref_plan_header_rec.criteria_value_high);
963
964 l_upd_plan_headers_b_rec.criteria_value_low := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.criteria_value_low, l_ref_plan_header_rec.criteria_value_low);
965
966 l_upd_plan_headers_b_rec.attribute1 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute1, l_ref_plan_header_rec.attribute1);
967
968 l_upd_plan_headers_b_rec.attribute2 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute2, l_ref_plan_header_rec.attribute2);
969
970 l_upd_plan_headers_b_rec.attribute3 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute3, l_ref_plan_header_rec.attribute3);
971
972 l_upd_plan_headers_b_rec.attribute4 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute4, l_ref_plan_header_rec.attribute4);
973
974 l_upd_plan_headers_b_rec.attribute5 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute5, l_ref_plan_header_rec.attribute5);
975
976 l_upd_plan_headers_b_rec.attribute6 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute6, l_ref_plan_header_rec.attribute6);
977
978 l_upd_plan_headers_b_rec.attribute7 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute7, l_ref_plan_header_rec.attribute7);
979
980 l_upd_plan_headers_b_rec.attribute8 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute8, l_ref_plan_header_rec.attribute8);
981
982 l_upd_plan_headers_b_rec.attribute9 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute9, l_ref_plan_header_rec.attribute9);
983
984 l_upd_plan_headers_b_rec.attribute10 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute10, l_ref_plan_header_rec.attribute10);
985
986 l_upd_plan_headers_b_rec.attribute11 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute11, l_ref_plan_header_rec.attribute11);
987
988 l_upd_plan_headers_b_rec.attribute12 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute12, l_ref_plan_header_rec.attribute12);
989
990 l_upd_plan_headers_b_rec.attribute13 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute13, l_ref_plan_header_rec.attribute13);
991
992 l_upd_plan_headers_b_rec.attribute14 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute14, l_ref_plan_header_rec.attribute14);
993
994 l_upd_plan_headers_b_rec.attribute15 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute15, l_ref_plan_header_rec.attribute15);
995
996 l_upd_plan_headers_b_rec.attribute_category := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_plan_headers_b_rec.attribute_category, l_ref_plan_header_rec.attribute_category);
997
998 if ( p_name <> FND_API.G_MISS_CHAR ) then
999 l_name := p_name;
1000 end if;
1001
1002 if ( p_description <> FND_API.G_MISS_CHAR ) then
1003 l_description := p_description;
1004 else
1005 l_description := NULL;
1006 end if;
1007
1008 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL )
1009 THEN
1010 -- Invoke validation procedures
1011 Validate_csc_relationship_plan(
1012 p_init_msg_list => FND_API.G_FALSE,
1013 p_validation_level => p_validation_level,
1014 p_validation_mode => CSC_CORE_UTILS_PVT.G_UPDATE,
1015 P_CSC_PLAN_HEADERS_B_REC => L_UPD_PLAN_HEADERS_B_REC,
1016 P_OLD_PLAN_HEADERS_B_REC => l_ref_plan_header_rec,
1017 P_DESCRIPTION => l_description,
1018 P_NAME => l_name,
1019 --P_PARTY_ID => p_party_id,
1020 --P_CUST_ACCOUNT_ID => p_cust_account_id,
1021 --P_CUST_ACCOUNT_ORG => p_cust_account_org,
1022 x_return_status => x_return_status,
1023 x_msg_count => x_msg_count,
1024 x_msg_data => x_msg_data);
1025 END IF;
1026
1027 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1028 RAISE FND_API.G_EXC_ERROR;
1029 END IF;
1030
1031 -- Invoke table handler(CSC_PLAN_HEADERS_B_PKG.Update_Row)
1032 CSC_PLAN_HEADERS_B_PKG.Update_Row(
1033 p_PLAN_ID => l_upd_plan_headers_b_rec.PLAN_ID,
1034 p_ORIGINAL_PLAN_ID => l_upd_plan_headers_b_rec.ORIGINAL_PLAN_ID,
1035 p_PLAN_GROUP_CODE => l_upd_plan_headers_b_rec.PLAN_GROUP_CODE,
1036 p_START_DATE_ACTIVE => l_upd_plan_headers_b_rec.START_DATE_ACTIVE,
1037 p_END_DATE_ACTIVE => l_upd_plan_headers_b_rec.END_DATE_ACTIVE,
1038 p_USE_FOR_CUST_ACCOUNT => l_upd_plan_headers_b_rec.USE_FOR_CUST_ACCOUNT,
1039 p_END_USER_TYPE => l_upd_plan_headers_b_rec.END_USER_TYPE,
1040 p_CUSTOMIZED_PLAN => l_upd_plan_headers_b_rec.CUSTOMIZED_PLAN,
1041 p_PROFILE_CHECK_ID => l_upd_plan_headers_b_rec.PROFILE_CHECK_ID,
1042 p_RELATIONAL_OPERATOR => l_upd_plan_headers_b_rec.RELATIONAL_OPERATOR,
1043 p_CRITERIA_VALUE_HIGH => l_upd_plan_headers_b_rec.CRITERIA_VALUE_HIGH,
1044 p_CRITERIA_VALUE_LOW => l_upd_plan_headers_b_rec.CRITERIA_VALUE_LOW,
1045 p_LAST_UPDATE_DATE => SYSDATE,
1046 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1047 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1048 p_ATTRIBUTE1 => l_upd_plan_headers_b_rec.ATTRIBUTE1,
1049 p_ATTRIBUTE2 => l_upd_plan_headers_b_rec.ATTRIBUTE2,
1050 p_ATTRIBUTE3 => l_upd_plan_headers_b_rec.ATTRIBUTE3,
1051 p_ATTRIBUTE4 => l_upd_plan_headers_b_rec.ATTRIBUTE4,
1052 p_ATTRIBUTE5 => l_upd_plan_headers_b_rec.ATTRIBUTE5,
1053 p_ATTRIBUTE6 => l_upd_plan_headers_b_rec.ATTRIBUTE6,
1054 p_ATTRIBUTE7 => l_upd_plan_headers_b_rec.ATTRIBUTE7,
1055 p_ATTRIBUTE8 => l_upd_plan_headers_b_rec.ATTRIBUTE8,
1056 p_ATTRIBUTE9 => l_upd_plan_headers_b_rec.ATTRIBUTE9,
1057 p_ATTRIBUTE10 => l_upd_plan_headers_b_rec.ATTRIBUTE10,
1058 p_ATTRIBUTE11 => l_upd_plan_headers_b_rec.ATTRIBUTE11,
1059 p_ATTRIBUTE12 => l_upd_plan_headers_b_rec.ATTRIBUTE12,
1060 p_ATTRIBUTE13 => l_upd_plan_headers_b_rec.ATTRIBUTE13,
1061 p_ATTRIBUTE14 => l_upd_plan_headers_b_rec.ATTRIBUTE14,
1062 p_ATTRIBUTE15 => l_upd_plan_headers_b_rec.ATTRIBUTE15,
1063 p_ATTRIBUTE_CATEGORY => l_upd_plan_headers_b_rec.ATTRIBUTE_CATEGORY,
1064 P_DESCRIPTION => l_description,
1065 P_NAME => l_name,
1066 X_OBJECT_VERSION_NUMBER => x_object_version_number );
1067
1068 -- if customized_plans is updated from 'Y' to 'N' then delete the customer-plan
1069 -- relationship in CSC_CUSTOMIZED_PLANS table.
1070 -- Do not delete records from the CSC_CUST_PLANS table, because the only thing
1071 -- happening here is removing the customization part of the plan to the customer
1072 -- and making the plan available to all other eligible customers. The customer
1073 -- may still be assigned to the plan.
1074
1075 if ( l_ref_plan_header_rec.customized_plan = 'Y'
1076 and p_csc_plan_headers_b_rec.customized_plan = 'N' ) then
1077 delete from csc_customized_plans
1078 where plan_id = p_csc_plan_headers_b_rec.plan_id;
1079 end if;
1080
1081 -- if plan header end_date_active was null and is updated, update the end_date_active
1082 -- in CSC_CUST_PLANS table for all customers associated to this plan.
1083 -- as per bug# 2250056, first condition is taken out, even the old date not null, user is
1084 -- allowed to change the end date to any future date to extend the plan
1085 -- also party_id, cust_plan_id, cust_account_id and object_version_number, otherwise
1086 -- update fails
1087 -- check for the existing and the new date, before update
1088 -- Fixed bug# 2805474, update Start_Date_Active
1089
1090 -- if ( p_csc_plan_headers_b_rec.end_date_active >= TRUNC (SYSDATE) ) then
1091
1092 -- if ( l_ref_plan_header_rec.end_date_active is null ) or
1093 if ( l_ref_plan_header_rec.end_date_active <> p_csc_plan_headers_b_rec.end_date_active ) or
1094 ( l_ref_plan_header_rec.start_date_active <> p_csc_plan_headers_b_rec.start_date_active ) then
1095
1096 Open C_Get_cust_plans;
1097
1098 LOOP
1099
1100 Fetch C_Get_cust_plans into
1101 l_ref_CSC_CUST_PLANS_rec.CUST_PLAN_ID,
1102 l_ref_CSC_CUST_PLANS_rec.PARTY_ID,
1103 l_ref_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ID,
1104 l_ref_CSC_CUST_PLANS_rec.OBJECT_VERSION_NUMBER;
1105
1106 Exit when C_Get_cust_plans%NOTFOUND;
1107
1108 CSC_CUST_PLANS_PVT.UPDATE_CUST_PLANS (
1109 P_Api_Version_Number => p_api_version_number,
1110 P_Init_Msg_List => p_init_msg_list,
1111 P_Commit => p_commit,
1112 p_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
1113 P_PLAN_ID => p_csc_plan_headers_b_rec.plan_id,
1114 P_CUST_PLAN_ID => l_ref_CSC_CUST_PLANS_rec.CUST_PLAN_ID,
1115 P_PARTY_ID => l_ref_CSC_CUST_PLANS_rec.PARTY_ID,
1116 P_CUST_ACCOUNT_ID => l_ref_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ID,
1117 p_start_date_active => p_csc_plan_headers_b_rec.start_date_active,
1118 p_end_date_active => p_csc_plan_headers_b_rec.end_date_active,
1119 P_OBJECT_VERSION_NUMBER => l_ref_CSC_CUST_PLANS_rec.OBJECT_VERSION_NUMBER,
1120 X_Object_Version_number => x_object_version_number,
1121 X_Return_Status => x_return_status,
1122 X_Msg_Count => x_msg_count,
1123 X_Msg_Data => x_msg_data );
1124
1125 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1126 RAISE FND_API.G_EXC_ERROR;
1127 END IF;
1128
1129 END LOOP;
1130
1131 Close C_Get_cust_plans;
1132
1133 end if;
1134
1135 -- end if;
1136
1137
1138 -- Standard check for p_commit
1139 IF FND_API.to_Boolean( p_commit )
1140 THEN
1141 COMMIT WORK;
1142 END IF;
1143
1144 -- Standard call to get message count and if count is 1, get message info.
1145 FND_MSG_PUB.Count_And_Get
1146 ( p_encoded => FND_API.G_FALSE,
1147 p_count => x_msg_count,
1148 p_data => x_msg_data );
1149
1150 EXCEPTION
1151 WHEN FND_API.G_EXC_ERROR THEN
1152 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
1153 P_API_NAME => L_API_NAME,
1154 P_PKG_NAME => G_PKG_NAME,
1155 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
1156 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
1157 X_MSG_COUNT => X_MSG_COUNT,
1158 X_MSG_DATA => X_MSG_DATA,
1159 X_RETURN_STATUS => X_RETURN_STATUS);
1160 APP_EXCEPTION.RAISE_EXCEPTION;
1161
1162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1163 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
1164 P_API_NAME => L_API_NAME,
1165 P_PKG_NAME => G_PKG_NAME,
1166 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1167 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
1168 X_MSG_COUNT => X_MSG_COUNT,
1169 X_MSG_DATA => X_MSG_DATA,
1170 X_RETURN_STATUS => X_RETURN_STATUS);
1171 APP_EXCEPTION.RAISE_EXCEPTION;
1172
1173 WHEN OTHERS THEN
1174 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
1175 P_API_NAME => L_API_NAME,
1176 P_PKG_NAME => G_PKG_NAME,
1177 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
1178 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
1179 X_MSG_COUNT => X_MSG_COUNT,
1180 X_MSG_DATA => X_MSG_DATA,
1181 X_RETURN_STATUS => X_RETURN_STATUS);
1182 APP_EXCEPTION.RAISE_EXCEPTION;
1183 End Update_plan_header;
1184
1185
1186 PROCEDURE Disable_plan(
1187 P_Api_Version_Number IN NUMBER,
1188 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1189 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1190 p_plan_id IN NUMBER,
1191 X_Return_Status OUT NOCOPY VARCHAR2,
1192 X_Msg_Count OUT NOCOPY NUMBER,
1193 X_Msg_Data OUT NOCOPY VARCHAR2
1194 )
1195 IS
1196 cursor c1 is
1197 select cust_plan_id, party_id, cust_account_id, -- cust_account_org,
1198 object_version_number
1199 from csc_cust_plans
1200 where plan_id = p_plan_id;
1201
1202 c1rec c1%rowtype;
1203
1204 l_api_name CONSTANT VARCHAR2(30) := 'Disable_plan';
1205 l_api_version_number CONSTANT NUMBER := 1.0;
1206 x_object_version_number NUMBER;
1207 BEGIN
1208 -- Standard Start of API savepoint
1209 SAVEPOINT Disable_plan_pvt;
1210
1211 -- Standard call to check for call compatibility.
1212 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1213 p_api_version_number,
1214 l_api_name,
1215 G_PKG_NAME)
1216 THEN
1217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1218 END IF;
1219
1220 -- Initialize message list if p_init_msg_list is set to TRUE.
1221 IF FND_API.to_Boolean( p_init_msg_list )
1222 THEN
1223 FND_MSG_PUB.initialize;
1224 END IF;
1225
1226 -- Initialize API return status to SUCCESS
1227 x_return_status := FND_API.G_RET_STS_SUCCESS;
1228
1229 -- Invoke table handler(CSC_PLAN_HEADERS_B_PKG.Disable_Row)
1230 CSC_PLAN_HEADERS_B_PKG.Disable_Row(
1231 p_PLAN_ID => p_PLAN_ID);
1232
1233 -- Change the end_date_active of the CSC_CUST_PLANS table to sysdate+1 for all records
1234 -- with this plan_id. The plan assignment engine will then check all records with
1235 -- end_date not valid and delete the records.
1236
1237 open c1;
1238 loop
1239 fetch c1 into c1rec;
1240 exit when c1%notfound;
1241
1242 CSC_CUST_PLANS_PVT.UPDATE_CUST_PLANS (
1243 P_Api_Version_Number => p_api_version_number,
1244 P_Init_Msg_List => p_init_msg_list,
1245 P_Commit => p_commit,
1246 p_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
1247 p_CUST_PLAN_ID => c1rec.cust_plan_id,
1248 P_PLAN_ID => p_plan_id,
1249 p_end_date_active => sysdate+1,
1250 p_party_id => c1rec.party_id,
1251 p_cust_account_id => c1rec.cust_account_id,
1252 -- p_cust_account_org => c1rec.cust_account_org,
1253 p_object_version_number => c1rec.object_version_number,
1254 X_Object_Version_number => x_object_version_number,
1255 X_Return_Status => x_return_status,
1256 X_Msg_Count => x_msg_count,
1257 X_Msg_Data => x_msg_data );
1258
1259 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1260 close c1;
1261 RAISE FND_API.G_EXC_ERROR;
1262 END IF;
1263
1264 end loop;
1265 close c1;
1266
1267 -- Standard check for p_commit
1268 IF FND_API.to_Boolean( p_commit )
1269 THEN
1270 COMMIT WORK;
1271 END IF;
1272
1273 -- Standard call to get message count and if count is 1, get message info.
1274 FND_MSG_PUB.Count_And_Get
1275 ( p_encoded => FND_API.G_FALSE,
1276 p_count => x_msg_count,
1277 p_data => x_msg_data);
1278
1279 EXCEPTION
1280 WHEN FND_API.G_EXC_ERROR THEN
1281 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
1282 P_API_NAME => L_API_NAME,
1283 P_PKG_NAME => G_PKG_NAME,
1284 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
1285 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
1286 X_MSG_COUNT => X_MSG_COUNT,
1287 X_MSG_DATA => X_MSG_DATA,
1288 X_RETURN_STATUS => X_RETURN_STATUS);
1289 APP_EXCEPTION.RAISE_EXCEPTION;
1290
1291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1292 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
1293 P_API_NAME => L_API_NAME,
1294 P_PKG_NAME => G_PKG_NAME,
1295 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1296 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
1297 X_MSG_COUNT => X_MSG_COUNT,
1298 X_MSG_DATA => X_MSG_DATA,
1299 X_RETURN_STATUS => X_RETURN_STATUS);
1300 APP_EXCEPTION.RAISE_EXCEPTION;
1301
1302 WHEN OTHERS THEN
1303 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
1304 P_API_NAME => L_API_NAME,
1305 P_PKG_NAME => G_PKG_NAME,
1306 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
1307 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
1308 X_MSG_COUNT => X_MSG_COUNT,
1309 X_MSG_DATA => X_MSG_DATA,
1310 X_RETURN_STATUS => X_RETURN_STATUS);
1311 APP_EXCEPTION.RAISE_EXCEPTION;
1312 End Disable_plan;
1313
1314
1315 -- Item-level validation procedures
1316 PROCEDURE Validate_PLAN_ID (
1317 P_Init_Msg_List IN VARCHAR2,
1318 P_Validation_mode IN VARCHAR2,
1319 P_PLAN_ID IN NUMBER,
1320 X_Return_Status OUT NOCOPY VARCHAR2,
1321 X_Msg_Count OUT NOCOPY NUMBER,
1322 X_Msg_Data OUT NOCOPY VARCHAR2
1323 )
1324 IS
1325 cursor check_dup_plan_id is
1326 select plan_id
1327 from CSC_PLAN_HEADERS_B
1328 where plan_id = p_plan_id;
1329
1330 l_plan_id number;
1331 l_api_name varchar2(30) := 'Validate_Plan_Id';
1332 BEGIN
1333 -- Initialize message list if p_init_msg_list is set to TRUE.
1334 IF FND_API.to_Boolean( p_init_msg_list )
1335 THEN
1336 FND_MSG_PUB.initialize;
1337 END IF;
1338
1339 -- Initialize API return status to SUCCESS
1340 x_return_status := FND_API.G_RET_STS_SUCCESS;
1341
1342 -- validate NOT NULL column.
1343 if (p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE) then
1344 IF(p_PLAN_ID is NULL or p_plan_id = FND_API.G_MISS_NUM) then
1345 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1346 fnd_message.set_name ('CS', 'CS_API_ALL_NULL_PARAMETER');
1347 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1348 fnd_message.set_token('NULL_PARAM', 'PLAN_ID');
1349 -- fnd_msg_pub.add;
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 END IF;
1352 end if;
1353
1354 -- validate for duplicate plan_ids.
1355 IF (p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE)
1356 THEN
1357 open check_dup_plan_id;
1358 fetch check_dup_plan_id into l_plan_id;
1359 if check_dup_plan_id%FOUND then
1360 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_DUPLICATE_VALUE');
1361 fnd_message.set_name ('CS', 'CS_API_ALL_DUPLICATE_VALUE');
1362 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1363 fnd_message.set_token('DUPLICATE_VAL_PARAM', 'PLAN_ID');
1364 -- fnd_msg_pub.add;
1365 x_return_status := FND_API.G_RET_STS_ERROR;
1366 end if;
1367 close check_dup_plan_id;
1368 END IF;
1369
1370 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1371 APP_EXCEPTION.RAISE_EXCEPTION;
1372 end if;
1373
1374 END Validate_PLAN_ID;
1375
1376
1377 PROCEDURE Validate_NAME (
1378 P_Init_Msg_List IN VARCHAR2,
1379 P_Validation_mode IN VARCHAR2,
1380 P_PLAN_ID IN NUMBER,
1381 P_NAME IN VARCHAR2,
1382 X_Return_Status OUT NOCOPY VARCHAR2,
1383 X_Msg_Count OUT NOCOPY NUMBER,
1384 X_Msg_Data OUT NOCOPY VARCHAR2
1385 )
1386 IS
1387 cursor check_dup_plan_name is
1388 select plan_id
1389 from CSC_PLAN_HEADERS_VL
1390 where name = p_name;
1391
1392 l_plan_id NUMBER;
1393 l_api_name varchar2(30) := 'Validate_Name';
1394 BEGIN
1395 -- Initialize message list if p_init_msg_list is set to TRUE.
1396 IF FND_API.to_Boolean( p_init_msg_list )
1397 THEN
1398 FND_MSG_PUB.initialize;
1399 END IF;
1400
1401 -- Initialize API return status to SUCCESS
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403
1404 -- validate NOT NULL column
1405 IF(p_NAME is NULL or p_name = FND_API.G_MISS_CHAR) then
1406 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1407 fnd_message.set_name ('CS', 'CS_API_ALL_NULL_PARAMETER');
1408 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1409 fnd_message.set_token('NULL_PARAM', 'PLAN_NAME');
1410 -- fnd_msg_pub.add;
1411 x_return_status := FND_API.G_RET_STS_ERROR;
1412 END IF;
1413
1414 -- validate for duplicate plan_names.
1415 if ( x_return_status = FND_API.G_RET_STS_SUCCESS ) then
1416 if p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE then
1417 l_plan_id := 0;
1418 else
1419 l_plan_id := p_plan_id;
1420 end if;
1421
1422 open check_dup_plan_name;
1423 fetch check_dup_plan_name into l_plan_id;
1424 close check_dup_plan_name;
1425
1426 if (p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE and l_plan_id <> 0 ) then
1427 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_DUPLICATE_VALUE');
1428 -- fnd_message.set_name ('CS', 'CS_API_ALL_DUPLICATE_VALUE');
1429 -- fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1430 -- fnd_message.set_token('DUPLICATE_VAL_PARAM', 'PLAN_NAME');
1431 -- fnd_msg_pub.add;
1432 fnd_message.set_name ('CSC', 'CSC_RSP_DUPLICATE_NAME');
1433 x_return_status := FND_API.G_RET_STS_ERROR;
1434 else
1435 if ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE and
1436 l_plan_id <> p_plan_id ) then -- some other plan exists with this name
1437 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_DUPLICATE_VALUE');
1438 fnd_message.set_name ('CS', 'CS_API_ALL_DUPLICATE_VALUE');
1439 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1440 fnd_message.set_token('DUPLICATE_VAL_PARAM', 'PLAN_NAME');
1441 -- fnd_msg_pub.add;
1442 x_return_status := FND_API.G_RET_STS_ERROR;
1443 end if;
1444 end if;
1445 end if;
1446
1447 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1448 APP_EXCEPTION.RAISE_EXCEPTION;
1449 end if;
1450
1451 END Validate_NAME;
1452
1453
1454 PROCEDURE Validate_ORIGINAL_PLAN_ID (
1455 P_Init_Msg_List IN VARCHAR2,
1456 P_Validation_mode IN VARCHAR2,
1457 P_PLAN_ID IN NUMBER,
1458 P_ORIGINAL_PLAN_ID IN NUMBER,
1459 P_CUSTOMIZED_PLAN IN VARCHAR2 := 'N',
1460 X_Return_Status OUT NOCOPY VARCHAR2,
1461 X_Msg_Count OUT NOCOPY NUMBER,
1462 X_Msg_Data OUT NOCOPY VARCHAR2
1463 )
1464 IS
1465 cursor chk_original_plan_id is
1466 select plan_id
1467 from CSC_PLAN_HEADERS_B
1468 where plan_id = p_original_plan_id;
1469
1470 l_plan_id number;
1471 l_api_name varchar2(30) := 'Validate_Original_Plan_Id';
1472 BEGIN
1473 -- Initialize message list if p_init_msg_list is set to TRUE.
1474 IF FND_API.to_Boolean( p_init_msg_list )
1475 THEN
1476 FND_MSG_PUB.initialize;
1477 END IF;
1478
1479 -- Initialize API return status to SUCCESS
1480 x_return_status := FND_API.G_RET_STS_SUCCESS;
1481
1482 -- validate NOT NULL column
1483 IF ( p_original_plan_id is NULL or p_original_plan_id = FND_API.G_MISS_NUM ) THEN
1484 IF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE ) OR
1485 ( p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE AND
1486 p_customized_plan = 'Y' )
1487 THEN
1488 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1489 fnd_message.set_name ('CS', 'CS_API_ALL_NULL_PARAMETER');
1490 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1491 fnd_message.set_token('NULL_PARAM', 'ORIGINAL_PLAN_ID');
1492 -- fnd_msg_pub.add;
1493 x_return_status := FND_API.G_RET_STS_ERROR;
1494 APP_EXCEPTION.RAISE_EXCEPTION;
1495 END IF;
1496 END IF;
1497
1498 IF ( p_original_plan_id is not NULL and p_original_plan_id <> FND_API.G_MISS_NUM ) THEN
1499 open chk_original_plan_id;
1500 fetch chk_original_plan_id into l_plan_id;
1501 if ( chk_original_plan_id%notfound ) then
1502 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1503 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
1504 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1505 fnd_message.set_token('VALUE', p_original_plan_id);
1506 fnd_message.set_token('PARAMETER', 'ORIGINAL_PLAN_ID');
1507 x_return_status := FND_API.G_RET_STS_ERROR;
1508 end if;
1509 close chk_original_plan_id;
1510 END IF;
1511
1512 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1513 APP_EXCEPTION.RAISE_EXCEPTION;
1514 end if;
1515
1516 END Validate_ORIGINAL_PLAN_ID;
1517
1518 PROCEDURE Validate_PLAN_GROUP_CODE (
1519 P_Init_Msg_List IN VARCHAR2,
1520 P_Validation_mode IN VARCHAR2,
1521 P_PLAN_GROUP_CODE IN VARCHAR2,
1522 X_Return_Status OUT NOCOPY VARCHAR2,
1523 X_Msg_Count OUT NOCOPY NUMBER,
1524 X_Msg_Data OUT NOCOPY VARCHAR2
1525 )
1526 IS
1527 cursor group_in_lookup is
1528 select count(*)
1529 from fnd_lookups
1530 where lookup_code = p_PLAN_GROUP_CODE
1531 and sysdate between nvl(start_date_active, sysdate)
1532 and nvl(end_date_active, sysdate);
1533
1534 l_rec_count NUMBER := 0;
1535 l_api_name varchar2(30) := 'Validate_Plan_Group_Code';
1536 BEGIN
1537 NULL;
1538 /********** commented out; change to make plan_group NULLABLE
1539 01-26-2000
1540 -- Initialize message list if p_init_msg_list is set to TRUE.
1541 IF FND_API.to_Boolean( p_init_msg_list )
1542 THEN
1543 FND_MSG_PUB.initialize;
1544 END IF;
1545
1546 -- Initialize API return status to SUCCESS
1547 x_return_status := FND_API.G_RET_STS_SUCCESS;
1548
1549 -- validate NOT NULL column
1550 IF (p_PLAN_GROUP_CODE is NULL or p_PLAN_GROUP_CODE = FND_API.G_MISS_CHAR) then
1551 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1552 fnd_message.set_name ('CS', 'CS_API_ALL_NULL_PARAMETER');
1553 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1554 fnd_message.set_token('NULL_PARAM', 'PLAN_GROUP_CODE');
1555 -- fnd_msg_pub.add;
1556 x_return_status := FND_API.G_RET_STS_ERROR;
1557 else
1558 -- validate PLAN_GROUP_CODE exists in fnd_lookup_values.
1559 open group_in_lookup;
1560 fetch group_in_lookup into l_rec_count;
1561 close group_in_lookup;
1562
1563 if ( l_rec_count = 0 ) then
1564 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1565 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
1566 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1567 fnd_message.set_token('VALUE', p_plan_group_code);
1568 fnd_message.set_token('PARAMETER', 'PLAN_GROUP_CODE');
1569 -- fnd_msg_pub.add;
1570 x_return_status := FND_API.G_RET_STS_ERROR;
1571 end if;
1572 --end if;
1573
1574 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1575 APP_EXCEPTION.RAISE_EXCEPTION;
1576 end if;
1577 ********* commented out; change to make plan_group NULLABLE */
1578
1579 END Validate_PLAN_GROUP_CODE;
1580
1581
1582 PROCEDURE Validate_USE_FOR_CUST_ACCOUNT (
1583 P_Init_Msg_List IN VARCHAR2,
1584 P_Validation_mode IN VARCHAR2,
1585 P_PLAN_ID IN NUMBER,
1586 P_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
1587 P_OLD_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
1588 X_Return_Status OUT NOCOPY VARCHAR2,
1589 X_Msg_Count OUT NOCOPY NUMBER,
1590 X_Msg_Data OUT NOCOPY VARCHAR2
1591 )
1592 IS
1593 cursor c1 is
1594 select count(*)
1595 from csc_cust_plans
1596 where plan_id = p_plan_id;
1597
1598 l_rec_count number := 0;
1599 l_api_name varchar2(30) := 'Validate_Use_For_Cust_Account';
1600 BEGIN
1601 -- Initialize message list if p_init_msg_list is set to TRUE.
1602 IF FND_API.to_Boolean( p_init_msg_list )
1603 THEN
1604 FND_MSG_PUB.initialize;
1605 END IF;
1606
1607 -- Initialize API return status to SUCCESS
1608 x_return_status := FND_API.G_RET_STS_SUCCESS;
1609
1610 -- validate NOT NULL column
1611 IF ( p_use_for_cust_account is NULL or p_use_for_cust_account = FND_API.G_MISS_CHAR ) then
1612 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1613 fnd_message.set_name ('CS', 'CS_API_ALL_NULL_PARAMETER');
1614 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1615 fnd_message.set_token('NULL_PARAM', 'USE_FOR_CUST_ACCOUNT');
1616 -- fnd_msg_pub.add;
1617 x_return_status := FND_API.G_RET_STS_ERROR;
1618 ELSIF ( p_use_for_cust_account <> 'Y' and p_use_for_cust_account <> 'N' ) then
1619 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1620 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
1621 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1622 fnd_message.set_token('VALUE', p_use_for_cust_account);
1623 fnd_message.set_token('PARAMETER', 'USE_FOR_CUST_ACCOUNT');
1624 -- fnd_msg_pub.add;
1625 x_return_status := FND_API.G_RET_STS_ERROR;
1626 ELSIF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE ) then
1627 if ( p_use_for_cust_account <> p_old_use_for_cust_account ) then
1628 -- Check if there are any existing customer-to-plan associations for this particular
1629 -- PLAN_ID. If ther are, then do not allow the update on the column.
1630 open c1;
1631 fetch c1 into l_rec_count;
1632 close c1;
1633 if ( l_rec_count <> 0 ) then
1634 -- Use_for_cust_account cannot be updated. There are existing customers
1635 -- associated to this plan.
1636 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
1637 -- fnd_message.set_name ('CS', 'CS_ALL_UPDATE_NOT_ALLOWED');
1638 -- Fixed bug# 3319977
1639 fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
1640 fnd_message.set_token('UPDATE_PARAM', 'PLAN_LEVEL');
1641 -- fnd_msg_pub.add;
1642 x_return_status := FND_API.G_RET_STS_ERROR;
1643 end if;
1644 end if;
1645 END IF;
1646
1647 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1648 APP_EXCEPTION.RAISE_EXCEPTION;
1649 end if;
1650
1651 END Validate_USE_FOR_CUST_ACCOUNT;
1652
1653 PROCEDURE Validate_END_USER_TYPE (
1654 P_Init_Msg_List IN VARCHAR2,
1655 P_Validation_mode IN VARCHAR2,
1656 P_END_USER_TYPE IN VARCHAR2,
1657 X_Return_Status OUT NOCOPY VARCHAR2,
1658 X_Msg_Count OUT NOCOPY NUMBER,
1659 X_Msg_Data OUT NOCOPY VARCHAR2
1660 )
1661 IS
1662 cursor type_in_lookup is
1663 select count(*)
1664 from csc_lookups
1665 where lookup_type = 'CSC_END_USER_TYPE'
1666 and lookup_code = P_END_USER_TYPE
1667 and sysdate between nvl(start_date_active, sysdate)
1668 and nvl(end_date_active, sysdate);
1669
1670 -- from fnd_lookups
1671 l_rec_count NUMBER := 0;
1672 l_api_name varchar2(30) := 'Validate_End_User_Type';
1673 BEGIN
1674 -- Initialize message list if p_init_msg_list is set to TRUE.
1675 IF FND_API.to_Boolean( p_init_msg_list )
1676 THEN
1677 FND_MSG_PUB.initialize;
1678 END IF;
1679
1680 -- Initialize API return status to SUCCESS
1681 x_return_status := FND_API.G_RET_STS_SUCCESS;
1682
1683 IF (p_END_USER_TYPE is NULL or p_END_USER_TYPE = FND_API.G_MISS_CHAR) then
1684 return;
1685 else
1686 -- validate PLAN_GROUP_CODE exists in fnd_lookup_values.
1687 open type_in_lookup;
1688 fetch type_in_lookup into l_rec_count;
1689 close type_in_lookup;
1690
1691 if ( l_rec_count = 0 ) then
1692 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1693 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
1694 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1695 fnd_message.set_token('VALUE', p_end_user_type);
1696 fnd_message.set_token('PARAMETER', 'END_USER_TYPE');
1697 x_return_status := FND_API.G_RET_STS_ERROR;
1698 end if;
1699 end if;
1700
1701 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1702 APP_EXCEPTION.RAISE_EXCEPTION;
1703 end if;
1704
1705 END Validate_END_USER_TYPE;
1706
1707 PROCEDURE Validate_CUSTOMIZED_PLAN (
1708 P_Init_Msg_List IN VARCHAR2,
1709 P_Validation_mode IN VARCHAR2,
1710 P_CUSTOMIZED_PLAN IN VARCHAR2,
1711 X_Return_Status OUT NOCOPY VARCHAR2,
1712 X_Msg_Count OUT NOCOPY NUMBER,
1713 X_Msg_Data OUT NOCOPY VARCHAR2
1714 )
1715 IS
1716 l_api_name varchar2(30) := 'Validate_Customized_Plan';
1717 BEGIN
1718 -- Initialize message list if p_init_msg_list is set to TRUE.
1719 IF FND_API.to_Boolean( p_init_msg_list )
1720 THEN
1721 FND_MSG_PUB.initialize;
1722 END IF;
1723
1724 -- Initialize API return status to SUCCESS
1725 x_return_status := FND_API.G_RET_STS_SUCCESS;
1726
1727 -- validate NOT NULL column
1728 IF( p_CUSTOMIZED_PLAN is NULL or p_customized_plan = FND_API.G_MISS_CHAR ) then
1729 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1730 fnd_message.set_name ('CS', 'CS_API_ALL_NULL_PARAMETER');
1731 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1732 fnd_message.set_token('NULL_PARAM', 'CUSTOMIZED_PLAN');
1733 x_return_status := FND_API.G_RET_STS_ERROR;
1734 ELSIF ( p_customized_plan <> 'Y' and p_customized_plan <> 'N' ) then
1735 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1736 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
1737 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1738 fnd_message.set_token('VALUE', p_customized_plan);
1739 fnd_message.set_token('PARAMETER', 'CUSTOMIZED_PLAN');
1740 -- fnd_msg_pub.add;
1741 x_return_status := FND_API.G_RET_STS_ERROR;
1742 END IF;
1743
1744 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1745 APP_EXCEPTION.RAISE_EXCEPTION;
1746 end if;
1747
1748 END Validate_CUSTOMIZED_PLAN;
1749
1750 PROCEDURE Validate_PROFILE_CHECK_ID (
1751 P_Init_Msg_List IN VARCHAR2,
1752 P_Validation_mode IN VARCHAR2,
1753 P_PLAN_ID IN NUMBER,
1754 P_PROFILE_CHECK_ID IN NUMBER,
1755 P_OLD_PROFILE_CHECK_ID IN NUMBER,
1756 X_Return_Status OUT NOCOPY VARCHAR2,
1757 X_Msg_Count OUT NOCOPY NUMBER,
1758 X_Msg_Data OUT NOCOPY VARCHAR2
1759 )
1760 IS
1761 cursor id_in_prof_checks is
1762 select count(*)
1763 from csc_prof_checks_b
1764 where check_id = p_profile_check_id
1765 and sysdate between nvl(start_date_active, sysdate)
1766 and nvl(end_date_active, sysdate);
1767
1768 cursor c1 is
1769 select count(*)
1770 from csc_cust_plans
1771 where plan_id = p_plan_id;
1772
1773 l_rec_count number := 0;
1774 l_api_name varchar2(30) := 'Validate_Profile_Check_Id';
1775 BEGIN
1776
1777 -- Initialize message list if p_init_msg_list is set to TRUE.
1778 IF FND_API.to_Boolean( p_init_msg_list )
1779 THEN
1780 FND_MSG_PUB.initialize;
1781 END IF;
1782
1783 -- Initialize API return status to SUCCESS
1784 x_return_status := FND_API.G_RET_STS_SUCCESS;
1785
1786 -- validate NOT NULL column
1787 IF(p_PROFILE_CHECK_ID is NULL or p_profile_check_id = FND_API.G_MISS_NUM) then
1788 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1789 fnd_message.set_name ('CS', 'CS_API_ALL_NULL_PARAMETER');
1790 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1791 fnd_message.set_token('NULL_PARAM', 'PROFILE_CHECK_ID');
1792 -- fnd_msg_pub.add;
1793 x_return_status := FND_API.G_RET_STS_ERROR;
1794 ELSIF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
1795 p_profile_check_id <> p_old_profile_check_id ) then
1796 --if ( G_CUST_PLANS_REC_CNT = FND_API.G_MISS_NUM ) then
1797 open c1;
1798 fetch c1 into l_rec_count;
1799 close c1;
1800 --end if;
1801 if ( l_rec_count <> 0 ) then
1802 -- Profile check id cannot be updated. There are customers attached to this plan.
1803 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
1804 -- fnd_message.set_name ('CS', 'CS_ALL_UPDATE_NOT_ALLOWED');
1805 -- Fixed bug# 3319977
1806 fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
1807 fnd_message.set_token('UPDATE_PARAM', 'PROFILE_CHECK_ID');
1808 x_return_status := FND_API.G_RET_STS_ERROR;
1809 end if;
1810 END IF;
1811
1812 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS ) then
1813 -- validate PROFILE_CHECK_ID exists in fnd_lookup_values.
1814 if ( ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
1815 p_profile_check_id <> p_old_profile_check_id ) OR
1816 p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE )
1817 then
1818 open id_in_prof_checks;
1819 fetch id_in_prof_checks into l_rec_count;
1820 close id_in_prof_checks;
1821
1822 if ( l_rec_count = 0 or l_rec_count > 1 ) then
1823 -- Profile check id is not a valid condition id.
1824 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1825 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
1826 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1827 fnd_message.set_token('VALUE', p_profile_check_id);
1828 fnd_message.set_token('PARAMETER', 'PROFILE_CHECK_ID');
1829 -- fnd_msg_pub.add;
1830 x_return_status := FND_API.G_RET_STS_ERROR;
1831 end if;
1832 end if;
1833 END IF;
1834 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1835 APP_EXCEPTION.RAISE_EXCEPTION;
1836 end if;
1837
1838 END Validate_PROFILE_CHECK_ID;
1839
1840 PROCEDURE Validate_CRITERIA_VALUE_LOW (
1841 P_Init_Msg_List IN VARCHAR2,
1842 P_Validation_mode IN VARCHAR2,
1843 P_PLAN_ID IN NUMBER,
1844 P_CRITERIA_VALUE_LOW IN VARCHAR2,
1845 P_OLD_CRITERIA_VALUE_LOW IN VARCHAR2,
1846 X_Return_Status OUT NOCOPY VARCHAR2,
1847 X_Msg_Count OUT NOCOPY NUMBER,
1848 X_Msg_Data OUT NOCOPY VARCHAR2
1849 )
1850 IS
1851 cursor c1 is
1852 select count(*)
1853 from csc_cust_plans
1854 where plan_id = p_plan_id;
1855
1856 l_rec_count number := 0;
1857 l_api_name varchar2(30) := 'Validate_Criteria_Value_Low';
1858 BEGIN
1859
1860 -- Initialize message list if p_init_msg_list is set to TRUE.
1861 IF FND_API.to_Boolean( p_init_msg_list )
1862 THEN
1863 FND_MSG_PUB.initialize;
1864 END IF;
1865
1866 -- Initialize API return status to SUCCESS
1867 x_return_status := FND_API.G_RET_STS_SUCCESS;
1868
1869 -- validate NOT NULL column
1870 --IF(p_CRITERIA_VALUE_LOW is NULL or p_CRITERIA_VALUE_LOW = FND_API.G_MISS_CHAR ) then
1871 --fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1872 --fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1873 --fnd_message.set_token('NULL_PARAM', 'CRITERIA_VALUE_LOW');
1874 -- fnd_msg_pub.add;
1875 --x_return_status := FND_API.G_RET_STS_ERROR;
1876 IF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
1877 p_criteria_value_low <> p_old_criteria_value_low ) then
1878 --if ( G_CUST_PLANS_REC_CNT = FND_API.G_MISS_NUM ) then
1879 open c1;
1880 fetch c1 into l_rec_count;
1881 close c1;
1882 --end if;
1883 if ( l_rec_count <> 0 ) then
1884 -- Criteria_value_low cannot be updated. There are customers attached to this plan.
1885 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
1886 -- fnd_message.set_name ('CS', 'CS_ALL_UPDATE_NOT_ALLOWED');
1887 -- Fixed bug# 3319977
1888 fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
1889 fnd_message.set_token('UPDATE_PARAM', 'CRITERIA_VALUE_LOW');
1890 x_return_status := FND_API.G_RET_STS_ERROR;
1891 end if;
1892 END IF;
1893
1894 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1895 APP_EXCEPTION.RAISE_EXCEPTION;
1896 end if;
1897
1898 END Validate_CRITERIA_VALUE_LOW;
1899
1900 PROCEDURE Validate_CRITERIA_VALUE_HIGH (
1901 P_Init_Msg_List IN VARCHAR2,
1902 P_Validation_mode IN VARCHAR2,
1903 P_PLAN_ID IN NUMBER,
1904 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
1905 P_OLD_CRITERIA_VALUE_HIGH IN VARCHAR2,
1906 X_Return_Status OUT NOCOPY VARCHAR2,
1907 X_Msg_Count OUT NOCOPY NUMBER,
1908 X_Msg_Data OUT NOCOPY VARCHAR2
1909 )
1910 IS
1911 cursor c1 is
1912 select count(*)
1913 from csc_cust_plans
1914 where plan_id = p_plan_id;
1915
1916 l_rec_count number := 0;
1917 l_api_name varchar2(30) := 'Validate_Criteria_Value_High';
1918 BEGIN
1919
1920 -- Initialize message list if p_init_msg_list is set to TRUE.
1921 IF FND_API.to_Boolean( p_init_msg_list )
1922 THEN
1923 FND_MSG_PUB.initialize;
1924 END IF;
1925
1926 -- Initialize API return status to SUCCESS
1927 x_return_status := FND_API.G_RET_STS_SUCCESS;
1928
1929 IF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
1930 nvl(p_criteria_value_high,0) <> nvl(p_old_criteria_value_high,0) ) then
1931 --if ( G_CUST_PLANS_REC_CNT = FND_API.G_MISS_NUM ) then
1932 open c1;
1933 fetch c1 into l_rec_count;
1934 close c1;
1935 --end if;
1936 if ( l_rec_count <> 0 ) then
1937 -- Criteria_value_high cannot be updated. There are customers attached to this plan.
1938 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
1939 -- fnd_message.set_name ('CS', 'CS_ALL_UPDATE_NOT_ALLOWED');
1940 -- Fixed bug# 3319977
1941 fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
1942 fnd_message.set_token('UPDATE_PARAM', 'CRITERIA_VALUE_HIGH');
1943 x_return_status := FND_API.G_RET_STS_ERROR;
1944 end if;
1945 END IF;
1946
1947 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1948 APP_EXCEPTION.RAISE_EXCEPTION;
1949 end if;
1950
1951 END Validate_CRITERIA_VALUE_HIGH;
1952
1953 PROCEDURE Validate_RELATIONAL_OPERATOR (
1954 P_Init_Msg_List IN VARCHAR2,
1955 P_Validation_mode IN VARCHAR2,
1956 P_PLAN_ID IN NUMBER,
1957 P_RELATIONAL_OPERATOR IN VARCHAR2,
1958 P_OLD_RELATIONAL_OPERATOR IN VARCHAR2,
1959 X_Return_Status OUT NOCOPY VARCHAR2,
1960 X_Msg_Count OUT NOCOPY NUMBER,
1961 X_Msg_Data OUT NOCOPY VARCHAR2
1962 )
1963 IS
1964 cursor c1 is
1965 select count(*)
1966 from csc_cust_plans
1967 where plan_id = p_plan_id;
1968
1969 l_rec_count number := 0;
1970 l_api_name varchar2(30) := 'Validate_Relational_Operator';
1971 BEGIN
1972
1973 -- Initialize message list if p_init_msg_list is set to TRUE.
1974 IF FND_API.to_Boolean( p_init_msg_list )
1975 THEN
1976 FND_MSG_PUB.initialize;
1977 END IF;
1978
1979 -- Initialize API return status to SUCCESS
1980 x_return_status := FND_API.G_RET_STS_SUCCESS;
1981
1982 -- validate NOT NULL column
1983 IF(p_RELATIONAL_OPERATOR is NULL or p_RELATIONAL_OPERATOR = FND_API.G_MISS_CHAR ) then
1984 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1985 fnd_message.set_name ('CS', 'CS_API_ALL_NULL_PARAMETER');
1986 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1987 fnd_message.set_token('NULL_PARAM', 'RELATIONAL_OPERATOR');
1988 -- fnd_msg_pub.add;
1989 x_return_status := FND_API.G_RET_STS_ERROR;
1990 ELSIF ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE AND
1991 p_relational_operator <> p_old_relational_operator ) then
1992 --if ( G_CUST_PLANS_REC_CNT = FND_API.G_MISS_NUM ) then
1993 open c1;
1994 fetch c1 into l_rec_count;
1995 close c1;
1996 --end if;
1997 if ( l_rec_count <> 0 ) then
1998 -- Relational_operator cannot be updated. There are customers attached to this plan.
1999 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_ALL_UPDATE_NOT_ALLOWED');
2000 -- fnd_message.set_name ('CS', 'CS_ALL_UPDATE_NOT_ALLOWED');
2001 -- Fixed bug# 3319977
2002 fnd_message.set_name ('CSC', 'CSC_RSP_INVALID_UPDATE');
2003 fnd_message.set_token('UPDATE_PARAM', 'RELATIONAL_OPERATOR');
2004 x_return_status := FND_API.G_RET_STS_ERROR;
2005 end if;
2006 END IF;
2007
2008 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2009 APP_EXCEPTION.RAISE_EXCEPTION;
2010 end if;
2011
2012 END Validate_RELATIONAL_OPERATOR;
2013
2014 PROCEDURE Validate_Plan_Criteria (
2015 P_Init_Msg_List IN VARCHAR2,
2016 P_Validation_mode IN VARCHAR2,
2017 P_PLAN_ID IN NUMBER,
2018 P_RELATIONAL_OPERATOR IN VARCHAR2,
2019 P_CRITERIA_VALUE_LOW IN VARCHAR2,
2020 P_CRITERIA_VALUE_HIGH IN VARCHAR2,
2021 X_Return_Status OUT NOCOPY VARCHAR2,
2022 X_Msg_Count OUT NOCOPY NUMBER,
2023 X_Msg_Data OUT NOCOPY VARCHAR2
2024 )
2025 IS
2026 l_api_name varchar2(30) := 'Validate_Plan_Criteria';
2027 BEGIN
2028 -- Initialize message list if p_init_msg_list is set to TRUE.
2029 IF FND_API.to_Boolean( p_init_msg_list )
2030 THEN
2031 FND_MSG_PUB.initialize;
2032 END IF;
2033
2034 -- Initialize API return status to SUCCESS
2035 x_return_status := FND_API.G_RET_STS_SUCCESS;
2036
2037 IF ( p_relational_operator = '=' OR
2038 p_relational_operator = '<>' OR
2039 p_relational_operator = '>' OR
2040 p_relational_operator = '<' OR
2041 p_relational_operator = '>=' OR
2042 p_relational_operator = '<=' OR
2043 p_relational_operator = 'LIKE' OR
2044 p_relational_operator = 'NOT LIKE' )
2045 THEN
2046 if ( p_criteria_value_low IS NULL AND p_criteria_value_high IS NOT NULL ) then
2047 -- Error in plan criteria. Criteria value low should be specified and criteria
2048 -- value high should not be specified.
2049 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
2050 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
2051 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
2052 fnd_message.set_token('VALUE', p_relational_operator);
2053 fnd_message.set_token('PARAMETER', 'RELATIONAL OPERATOR');
2054 x_return_status := FND_API.G_RET_STS_ERROR;
2055 elsif ( p_criteria_value_low is NULL AND p_criteria_value_high IS NULL ) then
2056 -- Error in plan criteria. Criteria value low should be specified.
2057 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
2058 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
2059 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
2060 fnd_message.set_token('VALUE', p_criteria_value_low);
2061 fnd_message.set_token('PARAMETER', 'CRITERIA VALUE LOW');
2062 x_return_status := FND_API.G_RET_STS_ERROR;
2063 elsif ( p_criteria_value_low IS NOT NULL AND p_criteria_value_high IS NOT NULL ) then
2064 -- Error in plan criteria. Criteria value high should not be specified.
2065 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
2066 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
2067 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
2068 fnd_message.set_token('VALUE', p_criteria_value_high);
2069 fnd_message.set_token('PARAMETER', 'CRITERIA VALUE HIGH');
2070 x_return_status := FND_API.G_RET_STS_ERROR;
2071 end if;
2072 ELSIF ( p_relational_operator = 'BETWEEN' OR
2073 p_relational_operator = 'NOT BETWEEN' )
2074 THEN
2075 if ( p_criteria_value_low IS NULL OR p_criteria_value_high IS NULL ) then
2076 -- Error in plan criteria. Criteria value low and high should be specified.
2077 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
2078 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
2079 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
2080 fnd_message.set_token('VALUE', p_relational_operator);
2081 fnd_message.set_token('PARAMETER', 'RELATIONAL OPERATOR');
2082 x_return_status := FND_API.G_RET_STS_ERROR;
2083 elsif ( p_criteria_value_low > p_criteria_value_high ) then
2084 -- Error in plan criteria. Criteria value low should be less than criteria
2085 -- value high.
2086 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
2087 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
2088 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
2089 fnd_message.set_token('VALUE', p_criteria_value_high);
2090 fnd_message.set_token('PARAMETER', 'CRITERIA VALUE HIGH');
2091 x_return_status := FND_API.G_RET_STS_ERROR;
2092 end if;
2093 ELSIF ( p_relational_operator = 'IS NULL' OR
2094 p_relational_operator = 'IS NOT NULL' )
2095 THEN
2096 if ( p_criteria_value_low IS NOT NULL OR p_criteria_value_high IS NOT NULL ) then
2097 -- Error in plan criteria. Criteria value low and high should not be specified.
2098 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
2099 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
2100 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
2101 fnd_message.set_token('VALUE', p_relational_operator);
2102 fnd_message.set_token('PARAMETER', 'RELATIONAL OPERATOR');
2103 x_return_status := FND_API.G_RET_STS_ERROR;
2104 end if;
2105 END IF;
2106
2107 END Validate_Plan_Criteria;
2108
2109
2110 PROCEDURE Validate_START_DATE_ACTIVE (
2111 P_Init_Msg_List IN VARCHAR2,
2112 P_Validation_mode IN VARCHAR2,
2113 P_PLAN_ID IN NUMBER,
2114 P_START_DATE_ACTIVE IN DATE,
2115 X_Return_Status OUT NOCOPY VARCHAR2,
2116 X_Msg_Count OUT NOCOPY NUMBER,
2117 X_Msg_Data OUT NOCOPY VARCHAR2
2118 )
2119 IS
2120 cursor c1 is
2121 select min(start_date_active)
2122 from csc_cust_plans
2123 where plan_id = p_plan_id;
2124
2125 l_min_date DATE;
2126 l_api_name VARCHAR2(30) := 'Validate_Start_Date_Active';
2127
2128 BEGIN
2129
2130 -- Initialize message list if p_init_msg_list is set to TRUE.
2131 IF FND_API.to_Boolean( p_init_msg_list )
2132 THEN
2133 FND_MSG_PUB.initialize;
2134 END IF;
2135
2136 -- Initialize API return status to SUCCESS
2137 x_return_status := FND_API.G_RET_STS_SUCCESS;
2138
2139 if ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE ) then
2140 open c1;
2141 fetch c1 into l_min_date;
2142 close c1;
2143
2144 if ( trunc(p_start_date_active) > trunc(l_min_date) ) then
2145 -- START date cannot be updated to specified value. There are customers who are associated
2146 -- to this plan EARLIER than the specified date. Valid dates are LESS than MIN_DATE;
2147 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
2148 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
2149 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
2150 fnd_message.set_token('VALUE', p_start_date_active);
2151 fnd_message.set_token('PARAMETER', 'START_DATE_ACTIVE');
2152 -- fnd_msg_pub.add;
2153 x_return_status := FND_API.G_RET_STS_ERROR;
2154 end if;
2155 end if;
2156
2157 if ( x_Return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2158 APP_EXCEPTION.RAISE_EXCEPTION;
2159 end if;
2160
2161 END Validate_START_DATE_ACTIVE;
2162
2163
2164 PROCEDURE Validate_END_DATE_ACTIVE (
2165 P_Init_Msg_List IN VARCHAR2,
2166 P_Validation_mode IN VARCHAR2,
2167 P_PLAN_ID IN NUMBER,
2168 P_END_DATE_ACTIVE IN DATE,
2169 X_Return_Status OUT NOCOPY VARCHAR2,
2170 X_Msg_Count OUT NOCOPY NUMBER,
2171 X_Msg_Data OUT NOCOPY VARCHAR2
2172 )
2173 IS
2174 cursor c1 is
2175 select max(end_date_active)
2176 from csc_cust_plans
2177 where plan_id = p_plan_id;
2178
2179 l_max_date DATE;
2180 l_api_name VARCHAR2(30) := 'Validate_End_Date_Active';
2181
2182 BEGIN
2183 -- Initialize message list if p_init_msg_list is set to TRUE.
2184 IF FND_API.to_Boolean( p_init_msg_list )
2185 THEN
2186 FND_MSG_PUB.initialize;
2187 END IF;
2188
2189 -- Initialize API return status to SUCCESS
2190 x_return_status := FND_API.G_RET_STS_SUCCESS;
2191
2192 if ( p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE ) then
2193 open c1;
2194 fetch c1 into l_max_date;
2195 close c1;
2196
2197 if l_max_date is null then
2198 l_max_date := SYSDATE;
2199 end if;
2200
2201 if ( trunc(p_end_date_active) < trunc(l_max_date) ) then
2202 -- DATE_TYPE date cannot be updated to specified value. There are customers who are associated
2203 -- to this plan EARLIER_LATER than the specified date. Valid dates are GREATER_LESSER than
2204 -- MAX_MIN_DATE;
2205 fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CSC_RSP_INVALID_DATE_UPDATE');
2206 fnd_message.set_token ('DATE_TYPE', 'End Date Active');
2207 fnd_message.set_token('EARLIER_LATER', 'later');
2208 fnd_message.set_token('GREATER_LESSER', 'greater');
2209 fnd_message.set_token('MAX_MIN_DATE', l_max_date);
2210 x_return_status := FND_API.G_RET_STS_ERROR;
2211 end if;
2212
2213 /*
2214 if ( trunc(p_end_date_active) < trunc(l_max_date) ) then
2215 -- END date cannot be updated to specified value. There are customers who are associated
2216 -- to this plan LATER than the specified date. Valid dates are GREATER than MAX_DATE;
2217 -- fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
2218 fnd_message.set_name ('CS', 'CS_API_ALL_INVALID_ARGUMENT');
2219 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
2220 fnd_message.set_token('VALUE', p_end_date_active);
2221 fnd_message.set_token('PARAMETER', 'END_DATE_ACTIVE');
2222 -- fnd_msg_pub.add;
2223 x_return_status := FND_API.G_RET_STS_ERROR;
2224 end if;
2225 */
2226 end if;
2227
2228 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2229 APP_EXCEPTION.RAISE_EXCEPTION;
2230 end if;
2231
2232 END Validate_END_DATE_ACTIVE;
2233
2234
2235 PROCEDURE Validate_csc_relationship_plan(
2236 P_Init_Msg_List IN VARCHAR2,
2237 P_Validation_level IN NUMBER,
2238 P_Validation_mode IN VARCHAR2,
2239 P_CSC_PLAN_HEADERS_B_REC IN CSC_PLAN_HEADERS_B_REC_TYPE,
2240 P_OLD_PLAN_HEADERS_B_REC IN CSC_PLAN_HEADERS_B_REC_TYPE,
2241 P_DESCRIPTION IN VARCHAR2,
2242 P_NAME IN VARCHAR2,
2243 --P_PARTY_ID IN NUMBER := FND_API.G_MISS_NUM,
2244 --P_CUST_ACCOUNT_ID IN NUMBER := FND_API.G_MISS_NUM,
2245 --P_CUST_ACCOUNT_ORG IN NUMBER := FND_API.G_MISS_NUM,
2246 X_Return_Status OUT NOCOPY VARCHAR2,
2247 X_Msg_Count OUT NOCOPY NUMBER,
2248 X_Msg_Data OUT NOCOPY VARCHAR2
2249 )
2250 IS
2251 l_api_name CONSTANT VARCHAR2(30) := 'Validate_csc_relationship_plan';
2252 BEGIN
2253 -- Initialize API return status to SUCCESS
2254 x_return_status := FND_API.G_RET_STS_SUCCESS;
2255
2256 IF (p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
2257 Validate_PLAN_ID(
2258 p_init_msg_list => FND_API.G_FALSE,
2259 p_validation_mode => p_validation_mode,
2260 p_PLAN_ID => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2261 x_return_status => x_return_status,
2262 x_msg_count => x_msg_count,
2263 x_msg_data => x_msg_data);
2264 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2265 raise FND_API.G_EXC_ERROR;
2266 END IF;
2267
2268 Validate_NAME(
2269 p_init_msg_list => FND_API.G_FALSE,
2270 p_validation_mode => p_validation_mode,
2271 p_plan_id => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2272 p_NAME => P_NAME,
2273 x_return_status => x_return_status,
2274 x_msg_count => x_msg_count,
2275 x_msg_data => x_msg_data);
2276 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2277 raise FND_API.G_EXC_ERROR;
2278 END IF;
2279
2280 Validate_ORIGINAL_PLAN_ID(
2281 p_init_msg_list => FND_API.G_FALSE,
2282 p_validation_mode => p_validation_mode,
2283 p_PLAN_ID => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2284 p_ORIGINAL_PLAN_ID => P_CSC_PLAN_HEADERS_B_REC.ORIGINAL_PLAN_ID,
2285 p_CUSTOMIZED_PLAN => P_CSC_PLAN_HEADERS_B_REC.CUSTOMIZED_PLAN,
2286 x_return_status => x_return_status,
2287 x_msg_count => x_msg_count,
2288 x_msg_data => x_msg_data);
2289 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2290 raise FND_API.G_EXC_ERROR;
2291 END IF;
2292
2293 Validate_PLAN_GROUP_CODE(
2294 p_init_msg_list => FND_API.G_FALSE,
2295 p_validation_mode => p_validation_mode,
2296 p_PLAN_GROUP_CODE => P_CSC_PLAN_HEADERS_B_REC.PLAN_GROUP_CODE,
2297 x_return_status => x_return_status,
2298 x_msg_count => x_msg_count,
2299 x_msg_data => x_msg_data);
2300 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2301 raise FND_API.G_EXC_ERROR;
2302 END IF;
2303
2304 Validate_START_DATE_ACTIVE (
2305 P_Init_Msg_List => FND_API.G_TRUE,
2306 P_Validation_mode => p_validation_mode,
2307 P_PLAN_ID => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2308 P_START_DATE_ACTIVE => P_CSC_PLAN_HEADERS_B_REC.START_DATE_ACTIVE,
2309 X_Return_Status => x_return_status,
2310 X_Msg_Count => x_msg_count,
2311 X_Msg_Data => x_msg_data );
2312 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2313 raise FND_API.G_EXC_ERROR;
2314 END IF;
2315
2316 Validate_END_DATE_ACTIVE (
2317 P_Init_Msg_List => FND_API.G_TRUE,
2318 P_Validation_mode => p_validation_mode,
2319 P_PLAN_ID => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2320 P_END_DATE_ACTIVE => P_CSC_PLAN_HEADERS_B_REC.END_DATE_ACTIVE,
2321 X_Return_Status => x_return_status,
2322 X_Msg_Count => x_msg_count,
2323 X_Msg_Data => x_msg_data );
2324 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2325 raise FND_API.G_EXC_ERROR;
2326 END IF;
2327
2328 -- issue a call to the CORE UTILITIES package to validate the date fields.
2329 CSC_CORE_UTILS_PVT.VALIDATE_DATES(
2330 p_init_msg_list => FND_API.G_FALSE,
2331 p_validation_mode => p_validation_mode,
2332 P_START_DATE => p_csc_plan_headers_b_rec.start_date_active,
2333 P_END_DATE => p_csc_plan_headers_b_rec.end_date_active,
2334 x_return_status => x_return_status,
2335 x_msg_count => x_msg_count,
2336 x_msg_data => x_msg_data);
2337 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2338 raise FND_API.G_EXC_ERROR;
2339 END IF;
2340
2341 Validate_USE_FOR_CUST_ACCOUNT(
2342 p_init_msg_list => FND_API.G_FALSE,
2343 p_validation_mode => p_validation_mode,
2344 p_PLAN_ID => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2345 p_USE_FOR_CUST_ACCOUNT => P_CSC_PLAN_HEADERS_B_REC.USE_FOR_CUST_ACCOUNT,
2346 p_OLD_USE_FOR_CUST_ACCOUNT => P_OLD_PLAN_HEADERS_B_REC.USE_FOR_CUST_ACCOUNT,
2347 x_return_status => x_return_status,
2348 x_msg_count => x_msg_count,
2349 x_msg_data => x_msg_data);
2350 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2351 raise FND_API.G_EXC_ERROR;
2352 END IF;
2353
2354 Validate_END_USER_TYPE (
2355 P_Init_Msg_List => FND_API.G_FALSE,
2356 P_Validation_mode => p_validation_mode,
2357 P_END_USER_TYPE => P_CSC_PLAN_HEADERS_B_REC.END_USER_TYPE,
2358 X_Return_Status => x_return_status,
2359 X_Msg_Count => x_msg_count,
2360 X_Msg_Data => x_msg_data );
2361 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2362 raise FND_API.G_EXC_ERROR;
2363 END IF;
2364
2365 Validate_CUSTOMIZED_PLAN(
2366 p_init_msg_list => FND_API.G_FALSE,
2367 p_validation_mode => p_validation_mode,
2368 p_CUSTOMIZED_PLAN => P_CSC_PLAN_HEADERS_B_REC.CUSTOMIZED_PLAN,
2369 x_return_status => x_return_status,
2370 x_msg_count => x_msg_count,
2371 x_msg_data => x_msg_data);
2372 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2373 raise FND_API.G_EXC_ERROR;
2374 END IF;
2375
2376 Validate_PROFILE_CHECK_ID(
2377 p_init_msg_list => FND_API.G_FALSE,
2378 p_validation_mode => p_validation_mode,
2379 p_plan_id => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2380 p_PROFILE_CHECK_ID => P_CSC_PLAN_HEADERS_B_REC.PROFILE_CHECK_ID,
2381 p_OLD_PROFILE_CHECK_ID => P_OLD_PLAN_HEADERS_B_REC.PROFILE_CHECK_ID,
2382 x_return_status => x_return_status,
2383 x_msg_count => x_msg_count,
2384 x_msg_data => x_msg_data);
2385 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2386 raise FND_API.G_EXC_ERROR;
2387 END IF;
2388
2389 Validate_CRITERIA_VALUE_LOW(
2390 p_init_msg_list => FND_API.G_FALSE,
2391 p_validation_mode => p_validation_mode,
2392 p_plan_id => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2393 p_CRITERIA_VALUE_LOW => P_CSC_PLAN_HEADERS_B_REC.CRITERIA_VALUE_LOW,
2394 p_OLD_CRITERIA_VALUE_LOW => P_OLD_PLAN_HEADERS_B_REC.CRITERIA_VALUE_LOW,
2395 x_return_status => x_return_status,
2396 x_msg_count => x_msg_count,
2397 x_msg_data => x_msg_data);
2398 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2399 raise FND_API.G_EXC_ERROR;
2400 END IF;
2401
2402 Validate_CRITERIA_VALUE_HIGH(
2403 p_init_msg_list => FND_API.G_FALSE,
2404 p_validation_mode => p_validation_mode,
2405 p_plan_id => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2406 p_CRITERIA_VALUE_HIGH => P_CSC_PLAN_HEADERS_B_REC.CRITERIA_VALUE_HIGH,
2407 p_OLD_CRITERIA_VALUE_HIGH => P_OLD_PLAN_HEADERS_B_REC.CRITERIA_VALUE_HIGH,
2408 x_return_status => x_return_status,
2409 x_msg_count => x_msg_count,
2410 x_msg_data => x_msg_data);
2411 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2412 raise FND_API.G_EXC_ERROR;
2413 END IF;
2414
2415 Validate_RELATIONAL_OPERATOR(
2416 p_init_msg_list => FND_API.G_FALSE,
2417 p_validation_mode => p_validation_mode,
2418 p_plan_id => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2419 p_RELATIONAL_OPERATOR => P_CSC_PLAN_HEADERS_B_REC.RELATIONAL_OPERATOR,
2420 p_OLD_RELATIONAL_OPERATOR => P_OLD_PLAN_HEADERS_B_REC.RELATIONAL_OPERATOR,
2421 x_return_status => x_return_status,
2422 x_msg_count => x_msg_count,
2423 x_msg_data => x_msg_data);
2424 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2425 raise FND_API.G_EXC_ERROR;
2426 END IF;
2427
2428 Validate_Plan_Criteria (
2429 P_Init_Msg_List => p_init_msg_list,
2430 P_Validation_mode => p_validation_mode,
2431 P_PLAN_ID => P_CSC_PLAN_HEADERS_B_REC.PLAN_ID,
2432 P_RELATIONAL_OPERATOR => P_CSC_PLAN_HEADERS_B_REC.RELATIONAL_OPERATOR,
2433 P_CRITERIA_VALUE_LOW => P_CSC_PLAN_HEADERS_B_REC.CRITERIA_VALUE_LOW,
2434 P_CRITERIA_VALUE_HIGH => P_CSC_PLAN_HEADERS_B_REC.CRITERIA_VALUE_HIGH,
2435 X_Return_Status => x_return_status,
2436 X_Msg_Count => x_msg_count,
2437 X_Msg_Data => x_msg_data );
2438 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2439 raise FND_API.G_EXC_ERROR;
2440 END IF;
2441
2442 END IF;
2443
2444 END Validate_csc_relationship_plan;
2445
2446 End CSC_RELATIONSHIP_PLANS_PVT;