[Home] [Help]
PACKAGE BODY: APPS.CSC_CUST_PLANS_PVT
Source
1 PACKAGE BODY CSC_CUST_PLANS_PVT as
2 /* $Header: cscvctpb.pls 115.18 2003/05/01 23:23:57 jamose ship $ */
3 -- Start of Comments
4 -- Package name : CSC_CUST_PLANS_PVT
5 -- Purpose : Private package to perform inserts, updates and deletes operations
6 -- on CSC_CUST_PLANS table. It contains procedure to perform item
7 -- level validations if the validation level is set to 100 (FULL).
8 -- History :
9 -- MM-DD-YYYY NAME MODIFICATIONS
10 -- 10-28-1999 dejoseph Created.
11 -- 12-08-1999 dejoseph 'Arcs'ed in for first code freeze.
12 -- 12-21-1999 dejoseph 'Arcs'ed in for second code freeze.
13 -- 01-03-2000 dejoseph 'Arcs'ed in for third code freeze. (10-JAN-2000)
14 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
15 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
16 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
17 -- 03-28-2000 dejoseph Removed references to CUST_ACCOUNT_ID and ORG_ID from all
18 -- 'where' clauses. ie. and nvl(cust_account_org,0) =
19 -- nvl(p_cust_account_org, nvl(cust_account_org,0) )
20 -- Replaced call to HZ_CUST_ACCOUNT_ALL to HZ_CUST_ACCOUNTS.
21 -- 04-10-2000 dejoseph Removed org_id validations and all reference to org_id in lieu
22 -- of TCA's decision to drop column ORG_ID from
23 -- hz_cust_accounts table. Also removed references to all
24 -- 'HZ_' tables and used 'JTF_'.
25 -- Removed reference to cust_account_org.
26 -- 10-23-2000 dejoseph Removed references to px_plan_audit_id when invoking procedure
27 -- to perform insert into CSC_CUST_PLANS_AUDIT table. Fix to
28 -- Bug # 1467071.
29 -- NOCOPY changes made for OUT parameters
30 --
31 -- 26-11-2002 bhroy G_MISS_XXX defaults of API parameters removed, added WHENEVER OSERROR EXIT FAILURE ROLLBACK
32 -- 01-05-2003 jamose The code has been changed on the procedure get_cust_plan_id
33 -- for making the dynamic sql bind variable complains. Bug# for
34 -- reference is 2935833
35 -- NOTE :
36 -- End of Comments
37
38 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_CUST_PLANS_PVT';
39 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscvctpb.pls';
40
41 PROCEDURE CONVERT_COLUMNS_TO_REC_TYPE(
42 P_PLAN_ID IN NUMBER ,
43 P_CUST_PLAN_ID IN NUMBER ,
44 P_PARTY_ID IN NUMBER ,
45 P_CUST_ACCOUNT_ID IN NUMBER ,
46 -- P_CUST_ACCOUNT_ORG IN NUMBER,
47 P_START_DATE_ACTIVE IN DATE ,
48 P_END_DATE_ACTIVE IN DATE ,
49 P_MANUAL_FLAG IN VARCHAR2,
50 P_PLAN_STATUS_CODE IN VARCHAR2 ,
51 P_REQUEST_ID IN NUMBER ,
52 P_PROGRAM_APPLICATION_ID IN NUMBER ,
53 P_PROGRAM_ID IN NUMBER ,
54 P_PROGRAM_UPDATE_DATE IN DATE ,
55 P_CREATION_DATE IN DATE,
56 P_CREATED_BY IN NUMBER,
57 P_LAST_UPDATE_DATE IN DATE ,
58 P_LAST_UPDATED_BY IN NUMBER,
59 P_LAST_UPDATE_LOGIN IN NUMBER,
60 P_ATTRIBUTE1 IN VARCHAR2,
61 P_ATTRIBUTE2 IN VARCHAR2,
62 P_ATTRIBUTE3 IN VARCHAR2,
63 P_ATTRIBUTE4 IN VARCHAR2,
64 P_ATTRIBUTE5 IN VARCHAR2,
65 P_ATTRIBUTE6 IN VARCHAR2,
66 P_ATTRIBUTE7 IN VARCHAR2,
67 P_ATTRIBUTE8 IN VARCHAR2,
68 P_ATTRIBUTE9 IN VARCHAR2,
69 P_ATTRIBUTE10 IN VARCHAR2,
70 P_ATTRIBUTE11 IN VARCHAR2,
71 P_ATTRIBUTE12 IN VARCHAR2,
72 P_ATTRIBUTE13 IN VARCHAR2,
73 P_ATTRIBUTE14 IN VARCHAR2,
74 P_ATTRIBUTE15 IN VARCHAR2,
75 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
76 P_OBJECT_VERSION_NUMBER IN NUMBER,
77 X_CSC_CUST_PLANS_REC_TYPE OUT NOCOPY CSC_CUST_PLANS_REC_TYPE )
78 IS
79 BEGIN
80 x_csc_cust_plans_rec_type.PLAN_ID := P_PLAN_ID ;
81 x_csc_cust_plans_rec_type.CUST_PLAN_ID := P_CUST_PLAN_ID ;
82 x_csc_cust_plans_rec_type.PARTY_ID := P_PARTY_ID ;
83 x_csc_cust_plans_rec_type.CUST_ACCOUNT_ID := P_CUST_ACCOUNT_ID ;
84 -- x_csc_cust_plans_rec_type.CUST_ACCOUNT_ORG := P_CUST_ACCOUNT_ORG ;
85 x_csc_cust_plans_rec_type.START_DATE_ACTIVE := P_START_DATE_ACTIVE ;
86 x_csc_cust_plans_rec_type.END_DATE_ACTIVE := P_END_DATE_ACTIVE ;
87 x_csc_cust_plans_rec_type.MANUAL_FLAG := P_MANUAL_FLAG ;
88 x_csc_cust_plans_rec_type.PLAN_STATUS_CODE := P_PLAN_STATUS_CODE ;
89 x_csc_cust_plans_rec_type.REQUEST_ID := P_REQUEST_ID ;
90 x_csc_cust_plans_rec_type.PROGRAM_APPLICATION_ID := P_PROGRAM_APPLICATION_ID ;
91 x_csc_cust_plans_rec_type.PROGRAM_ID := P_PROGRAM_ID ;
92 x_csc_cust_plans_rec_type.PROGRAM_UPDATE_DATE := P_PROGRAM_UPDATE_DATE ;
93 x_csc_cust_plans_rec_type.CREATION_DATE := P_CREATION_DATE ;
94 x_csc_cust_plans_rec_type.LAST_UPDATE_DATE := P_LAST_UPDATE_DATE ;
95 x_csc_cust_plans_rec_type.CREATED_BY := P_CREATED_BY ;
96 x_csc_cust_plans_rec_type.LAST_UPDATED_BY := P_LAST_UPDATED_BY ;
97 x_csc_cust_plans_rec_type.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN ;
98 x_csc_cust_plans_rec_type.ATTRIBUTE1 := P_ATTRIBUTE1 ;
99 x_csc_cust_plans_rec_type.ATTRIBUTE2 := P_ATTRIBUTE2 ;
100 x_csc_cust_plans_rec_type.ATTRIBUTE3 := P_ATTRIBUTE3 ;
101 x_csc_cust_plans_rec_type.ATTRIBUTE4 := P_ATTRIBUTE4 ;
102 x_csc_cust_plans_rec_type.ATTRIBUTE5 := P_ATTRIBUTE5 ;
103 x_csc_cust_plans_rec_type.ATTRIBUTE6 := P_ATTRIBUTE6 ;
104 x_csc_cust_plans_rec_type.ATTRIBUTE7 := P_ATTRIBUTE7 ;
105 x_csc_cust_plans_rec_type.ATTRIBUTE8 := P_ATTRIBUTE8 ;
106 x_csc_cust_plans_rec_type.ATTRIBUTE9 := P_ATTRIBUTE9 ;
107 x_csc_cust_plans_rec_type.ATTRIBUTE10 := P_ATTRIBUTE10 ;
108 x_csc_cust_plans_rec_type.ATTRIBUTE11 := P_ATTRIBUTE11 ;
109 x_csc_cust_plans_rec_type.ATTRIBUTE12 := P_ATTRIBUTE12 ;
110 x_csc_cust_plans_rec_type.ATTRIBUTE13 := P_ATTRIBUTE13 ;
111 x_csc_cust_plans_rec_type.ATTRIBUTE14 := P_ATTRIBUTE14 ;
112 x_csc_cust_plans_rec_type.ATTRIBUTE15 := P_ATTRIBUTE15 ;
113 x_csc_cust_plans_rec_type.ATTRIBUTE_CATEGORY := P_ATTRIBUTE_CATEGORY ;
114 x_csc_cust_plans_rec_type.OBJECT_VERSION_NUMBER := P_OBJECT_VERSION_NUMBER ;
115
116 END CONVERT_COLUMNS_TO_REC_TYPE;
117
118 /*** Overloaded proc. that accepts a detailed parameter list, converts the list
119 into a record type parameter and calls the create procedure that accepts
120 a record type IN parameter ***/
121
122 PROCEDURE Create_cust_plans(
123 P_Api_Version_Number IN NUMBER,
124 P_Init_Msg_List IN VARCHAR2,
125 P_Commit IN VARCHAR2,
126 p_validation_level IN NUMBER ,
127 P_PLAN_ID IN NUMBER ,
128 P_CUST_PLAN_ID IN NUMBER ,
129 P_PARTY_ID IN NUMBER ,
130 P_CUST_ACCOUNT_ID IN NUMBER ,
131 -- P_CUST_ACCOUNT_ORG IN NUMBER,
132 P_START_DATE_ACTIVE IN DATE,
133 P_END_DATE_ACTIVE IN DATE,
134 P_MANUAL_FLAG IN VARCHAR2,
135 P_PLAN_STATUS_CODE IN VARCHAR2,
136 P_REQUEST_ID IN NUMBER,
137 P_PROGRAM_APPLICATION_ID IN NUMBER,
138 P_PROGRAM_ID IN NUMBER,
139 P_PROGRAM_UPDATE_DATE IN DATE ,
140 P_CREATION_DATE IN DATE ,
141 P_LAST_UPDATE_DATE IN DATE ,
142 P_CREATED_BY IN NUMBER,
143 P_LAST_UPDATED_BY IN NUMBER,
144 P_LAST_UPDATE_LOGIN IN NUMBER,
145 P_ATTRIBUTE1 IN VARCHAR2,
146 P_ATTRIBUTE2 IN VARCHAR2,
147 P_ATTRIBUTE3 IN VARCHAR2,
148 P_ATTRIBUTE4 IN VARCHAR2,
149 P_ATTRIBUTE5 IN VARCHAR2,
150 P_ATTRIBUTE6 IN VARCHAR2,
151 P_ATTRIBUTE7 IN VARCHAR2,
152 P_ATTRIBUTE8 IN VARCHAR2,
153 P_ATTRIBUTE9 IN VARCHAR2,
154 P_ATTRIBUTE10 IN VARCHAR2,
155 P_ATTRIBUTE11 IN VARCHAR2,
156 P_ATTRIBUTE12 IN VARCHAR2,
157 P_ATTRIBUTE13 IN VARCHAR2,
158 P_ATTRIBUTE14 IN VARCHAR2,
159 P_ATTRIBUTE15 IN VARCHAR2,
160 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
161 P_OBJECT_VERSION_NUMBER IN NUMBER,
162 X_CUST_PLAN_ID OUT NOCOPY NUMBER,
163 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
164 X_Return_Status OUT NOCOPY VARCHAR2,
165 X_Msg_Count OUT NOCOPY NUMBER,
166 X_Msg_Data OUT NOCOPY VARCHAR2
167 )
168 IS
169 l_csc_cust_plans_rec CSC_CUST_PLANS_REC_TYPE;
170 BEGIN
171
172 CONVERT_COLUMNS_TO_REC_TYPE(
173 P_PLAN_ID => P_PLAN_ID ,
174 P_CUST_PLAN_ID => P_CUST_PLAN_ID ,
175 P_PARTY_ID => P_PARTY_ID ,
176 P_CUST_ACCOUNT_ID => P_CUST_ACCOUNT_ID ,
177 -- P_CUST_ACCOUNT_ORG => P_CUST_ACCOUNT_ORG ,
178 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE ,
179 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE ,
180 P_PLAN_STATUS_CODE => P_PLAN_STATUS_CODE ,
181 P_MANUAL_FLAG => P_MANUAL_FLAG ,
182 P_REQUEST_ID => P_REQUEST_ID ,
183 P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID ,
184 P_PROGRAM_ID => P_PROGRAM_ID ,
185 P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE ,
186 P_CREATION_DATE => P_CREATION_DATE ,
187 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE ,
188 P_CREATED_BY => P_CREATED_BY ,
189 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY ,
190 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ,
191 P_ATTRIBUTE1 => P_ATTRIBUTE1 ,
192 P_ATTRIBUTE2 => P_ATTRIBUTE2 ,
193 P_ATTRIBUTE3 => P_ATTRIBUTE3 ,
194 P_ATTRIBUTE4 => P_ATTRIBUTE4 ,
195 P_ATTRIBUTE5 => P_ATTRIBUTE5 ,
196 P_ATTRIBUTE6 => P_ATTRIBUTE6 ,
197 P_ATTRIBUTE7 => P_ATTRIBUTE7 ,
198 P_ATTRIBUTE8 => P_ATTRIBUTE8 ,
199 P_ATTRIBUTE9 => P_ATTRIBUTE9 ,
200 P_ATTRIBUTE10 => P_ATTRIBUTE10 ,
201 P_ATTRIBUTE11 => P_ATTRIBUTE11 ,
202 P_ATTRIBUTE12 => P_ATTRIBUTE12 ,
203 P_ATTRIBUTE13 => P_ATTRIBUTE13 ,
204 P_ATTRIBUTE14 => P_ATTRIBUTE14 ,
205 P_ATTRIBUTE15 => P_ATTRIBUTE15 ,
206 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY ,
207 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER ,
208 X_CSC_CUST_PLANS_REC_TYPE => l_csc_cust_plans_rec );
209
210 -- issue a call to the create_cust_plans proc. with the record type parameter
211 Create_cust_plans(
212 P_Api_Version_Number => p_api_version_number,
213 P_Init_Msg_List => p_init_msg_list,
214 P_Commit => p_commit,
215 P_Validation_level => p_validation_level,
216 P_CSC_CUST_PLANS_Rec => l_csc_cust_plans_rec,
217 X_CUST_PLAN_ID => x_cust_plan_id,
218 X_OBJECT_VERSION_NUMBER => x_object_version_number,
219 X_Return_Status => x_return_status,
220 X_Msg_Count => x_msg_count,
221 X_Msg_Data => x_msg_data );
222
223 END CREATE_CUST_PLANS;
224
225 -- Hint: Primary key needs to be returned.
226 PROCEDURE Create_cust_plans(
227 P_Api_Version_Number IN NUMBER,
228 P_Init_Msg_List IN VARCHAR2,
229 P_Commit IN VARCHAR2,
230 p_validation_level IN NUMBER ,
231 P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_Type ,
232 X_CUST_PLAN_ID OUT NOCOPY NUMBER,
233 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
234 X_Return_Status OUT NOCOPY VARCHAR2,
235 X_Msg_Count OUT NOCOPY NUMBER,
236 X_Msg_Data OUT NOCOPY VARCHAR2
237 )
238 IS
239 -- Retreive the start and end date of the given plan_id to default
240 -- it into the cust_plans table, if they are passed in as nulls.
241 cursor c1( c_plan_id number ) is
242 select start_date_active,
243 end_date_active
244 from csc_plan_headers_b
245 where plan_id = c_plan_id;
246
247 l_api_name CONSTANT VARCHAR2(30) := 'Create_cust_plans';
248 l_api_version_number CONSTANT NUMBER := 1.0;
249 l_return_status_full VARCHAR2(1);
250
251 l_start_date_active DATE := p_csc_cust_plans_rec.start_date_active;
252 l_end_date_active DATE := p_csc_cust_plans_rec.end_date_active;
253 BEGIN
254 -- Standard Start of API savepoint
255 SAVEPOINT CREATE_CUST_PLANS_PVT;
256
257 -- Standard call to check for call compatibility.
258 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
259 p_api_version_number,
260 l_api_name,
261 G_PKG_NAME)
262 THEN
263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
264 END IF;
265
266 -- Initialize message list if p_init_msg_list is set to TRUE.
267 IF FND_API.to_Boolean( p_init_msg_list )
268 THEN
269 FND_MSG_PUB.initialize;
270 END IF;
271
272 -- Initialize API return status to SUCCESS
273 x_return_status := FND_API.G_RET_STS_SUCCESS;
274
275 -- ******************************************************************
276 -- Validate Environment
277 -- ******************************************************************
278 IF FND_GLOBAL.User_Id IS NULL
279 THEN
280 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
281 THEN
282 FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'UT_CANNOT_GET_PROFILE_VALUE');
283 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
284 -- FND_MSG_PUB.ADD;
285 END IF;
286 RAISE FND_API.G_EXC_ERROR;
287 END IF;
288
289 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
290 THEN
291 -- Invoke validation procedures
292 Validate_csc_cust_plans(
293 p_init_msg_list => FND_API.G_FALSE,
294 p_validation_level => p_validation_level,
295 p_validation_mode => CSC_CORE_UTILS_PVT.G_CREATE,
296 P_CSC_CUST_PLANS_Rec => P_CSC_CUST_PLANS_Rec,
297 x_return_status => x_return_status,
298 x_msg_count => x_msg_count,
299 x_msg_data => x_msg_data);
300 END IF;
301
302 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
303 RAISE FND_API.G_EXC_ERROR;
304 END IF;
305
306 -- if either start_date_active or end_date_active is passed in as null, then
307 -- retreive these value from the csc_plan_headers_b table for the given plan_id;
308
309 if (( p_csc_cust_plans_rec.START_DATE_ACTIVE is NULL OR
310 p_csc_cust_plans_rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE ) OR
311 ( p_csc_cust_plans_rec.END_DATE_ACTIVE is NULL OR
312 p_csc_cust_plans_rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE )) THEN
313 OPEN c1 (p_csc_cust_plans_rec.plan_id);
314 FETCH c1 INTO l_start_date_active, l_end_date_active;
315 IF C1%NOTFOUND THEN
316 --FND_MESSAGE.SET_NAME(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, '
317 CLOSE c1;
318 RAISE NO_DATA_FOUND;
319 END IF;
320 CLOSE c1;
321 end if;
322
323 x_cust_plan_id := p_csc_cust_plans_rec.cust_plan_id;
324
325 -- Invoke table handler(CSC_CUST_PLANS_PKG.Insert_Row)
326 CSC_CUST_PLANS_PKG.Insert_Row(
327 px_CUST_PLAN_ID => x_CUST_PLAN_ID,
328 p_PLAN_ID => p_CSC_CUST_PLANS_rec.PLAN_ID,
329 p_PARTY_ID => p_CSC_CUST_PLANS_rec.PARTY_ID,
330 p_CUST_ACCOUNT_ID => p_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ID,
331 -- p_CUST_ACCOUNT_ORG => p_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ORG,
332 p_START_DATE_ACTIVE => l_start_date_active,
333 p_END_DATE_ACTIVE => l_end_date_active,
334 p_MANUAL_FLAG => p_CSC_CUST_PLANS_rec.MANUAL_FLAG,
335 p_PLAN_STATUS_CODE => CSC_CORE_UTILS_PVT.APPLY_PLAN,
336 p_REQUEST_ID => p_CSC_CUST_PLANS_rec.REQUEST_ID,
337 p_PROGRAM_APPLICATION_ID => p_CSC_CUST_PLANS_rec.PROGRAM_APPLICATION_ID,
338 p_PROGRAM_ID => p_CSC_CUST_PLANS_rec.PROGRAM_ID,
339 p_PROGRAM_UPDATE_DATE => p_CSC_CUST_PLANS_rec.PROGRAM_UPDATE_DATE,
340 p_CREATION_DATE => SYSDATE,
341 p_LAST_UPDATE_DATE => SYSDATE,
342 p_CREATED_BY => FND_GLOBAL.USER_ID,
343 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
344 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
345 p_ATTRIBUTE1 => p_CSC_CUST_PLANS_rec.ATTRIBUTE1,
346 p_ATTRIBUTE2 => p_CSC_CUST_PLANS_rec.ATTRIBUTE2,
347 p_ATTRIBUTE3 => p_CSC_CUST_PLANS_rec.ATTRIBUTE3,
348 p_ATTRIBUTE4 => p_CSC_CUST_PLANS_rec.ATTRIBUTE4,
349 p_ATTRIBUTE5 => p_CSC_CUST_PLANS_rec.ATTRIBUTE5,
350 p_ATTRIBUTE6 => p_CSC_CUST_PLANS_rec.ATTRIBUTE6,
351 p_ATTRIBUTE7 => p_CSC_CUST_PLANS_rec.ATTRIBUTE7,
352 p_ATTRIBUTE8 => p_CSC_CUST_PLANS_rec.ATTRIBUTE8,
353 p_ATTRIBUTE9 => p_CSC_CUST_PLANS_rec.ATTRIBUTE9,
354 p_ATTRIBUTE10 => p_CSC_CUST_PLANS_rec.ATTRIBUTE10,
355 p_ATTRIBUTE11 => p_CSC_CUST_PLANS_rec.ATTRIBUTE11,
356 p_ATTRIBUTE12 => p_CSC_CUST_PLANS_rec.ATTRIBUTE12,
357 p_ATTRIBUTE13 => p_CSC_CUST_PLANS_rec.ATTRIBUTE13,
358 p_ATTRIBUTE14 => p_CSC_CUST_PLANS_rec.ATTRIBUTE14,
359 p_ATTRIBUTE15 => p_CSC_CUST_PLANS_rec.ATTRIBUTE15,
360 p_ATTRIBUTE_CATEGORY => p_CSC_CUST_PLANS_rec.ATTRIBUTE_CATEGORY,
361 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER);
362
363 -- For every operation on the CSC_CUST_PLANS table insert a record in the
364 -- CSC_CUST_PLANS_AUDIT table.
365
366 CSC_CUST_PLANS_AUDIT_PKG.Insert_Row(
367 --px_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID,-- will be selected from the sequence.
368 p_PLAN_ID => p_CSC_CUST_PLANS_rec.PLAN_ID ,
369 p_PARTY_ID => p_CSC_CUST_PLANS_rec.PARTY_ID ,
370 p_CUST_ACCOUNT_ID => p_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ID ,
371 -- p_CUST_ACCOUNT_ORG => p_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ORG ,
372 p_PLAN_STATUS_CODE => CSC_CORE_UTILS_PVT.APPLY_PLAN,
373 p_REQUEST_ID => p_CSC_CUST_PLANS_rec.REQUEST_ID ,
374 p_PROGRAM_APPLICATION_ID => p_CSC_CUST_PLANS_rec.PROGRAM_APPLICATION_ID ,
375 p_PROGRAM_ID => p_CSC_CUST_PLANS_rec.PROGRAM_ID ,
376 p_PROGRAM_UPDATE_DATE => p_CSC_CUST_PLANS_rec.PROGRAM_UPDATE_DATE ,
377 p_CREATION_DATE => SYSDATE,
378 p_LAST_UPDATE_DATE => SYSDATE,
379 p_CREATED_BY => FND_GLOBAL.USER_ID,
380 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
381 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
382 p_ATTRIBUTE1 => p_CSC_CUST_PLANS_rec.ATTRIBUTE1 ,
383 p_ATTRIBUTE2 => p_CSC_CUST_PLANS_rec.ATTRIBUTE2 ,
384 p_ATTRIBUTE3 => p_CSC_CUST_PLANS_rec.ATTRIBUTE3 ,
385 p_ATTRIBUTE4 => p_CSC_CUST_PLANS_rec.ATTRIBUTE4 ,
386 p_ATTRIBUTE5 => p_CSC_CUST_PLANS_rec.ATTRIBUTE5 ,
387 p_ATTRIBUTE6 => p_CSC_CUST_PLANS_rec.ATTRIBUTE6 ,
388 p_ATTRIBUTE7 => p_CSC_CUST_PLANS_rec.ATTRIBUTE7 ,
389 p_ATTRIBUTE8 => p_CSC_CUST_PLANS_rec.ATTRIBUTE8 ,
390 p_ATTRIBUTE9 => p_CSC_CUST_PLANS_rec.ATTRIBUTE9 ,
391 p_ATTRIBUTE10 => p_CSC_CUST_PLANS_rec.ATTRIBUTE10 ,
392 p_ATTRIBUTE11 => p_CSC_CUST_PLANS_rec.ATTRIBUTE11 ,
393 p_ATTRIBUTE12 => p_CSC_CUST_PLANS_rec.ATTRIBUTE12 ,
394 p_ATTRIBUTE13 => p_CSC_CUST_PLANS_rec.ATTRIBUTE13 ,
395 p_ATTRIBUTE14 => p_CSC_CUST_PLANS_rec.ATTRIBUTE14 ,
396 p_ATTRIBUTE15 => p_CSC_CUST_PLANS_rec.ATTRIBUTE15 ,
397 p_ATTRIBUTE_CATEGORY => p_CSC_CUST_PLANS_rec.ATTRIBUTE_CATEGORY,
398 x_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID );
399
400 -- Standard check for p_commit
401 IF FND_API.to_Boolean( p_commit )
402 THEN
403 COMMIT WORK;
404 END IF;
405
406 -- Standard call to get message count and if count is 1, get message info.
407 FND_MSG_PUB.Count_And_Get
408 ( p_count => x_msg_count,
409 p_data => x_msg_data );
410
411 EXCEPTION
412 WHEN FND_API.G_EXC_ERROR THEN
413 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
414 P_API_NAME => L_API_NAME,
415 P_PKG_NAME => G_PKG_NAME,
416 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
417 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
418 X_MSG_COUNT => X_MSG_COUNT,
419 X_MSG_DATA => X_MSG_DATA,
420 X_RETURN_STATUS => X_RETURN_STATUS);
421 APP_EXCEPTION.RAISE_EXCEPTION;
422
423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
424 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
425 P_API_NAME => L_API_NAME,
426 P_PKG_NAME => G_PKG_NAME,
427 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
428 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
429 X_MSG_COUNT => X_MSG_COUNT,
430 X_MSG_DATA => X_MSG_DATA,
431 X_RETURN_STATUS => X_RETURN_STATUS);
432 APP_EXCEPTION.RAISE_EXCEPTION;
433
434 WHEN OTHERS THEN
435 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
436 P_API_NAME => L_API_NAME,
437 P_PKG_NAME => G_PKG_NAME,
438 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
439 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
440 X_MSG_COUNT => X_MSG_COUNT,
441 X_MSG_DATA => X_MSG_DATA,
442 X_RETURN_STATUS => X_RETURN_STATUS);
443 APP_EXCEPTION.RAISE_EXCEPTION;
444
445 End Create_cust_plans;
446
447
448 /*** Overloaded proc. that accepts a detailed parameter list, converts the list
449 into a record type parameter and calls the create procedure that accepts
450 a record type IN parameter ***/
451
452 PROCEDURE Update_cust_plans(
453 P_Api_Version_Number IN NUMBER,
454 P_Init_Msg_List IN VARCHAR2,
455 P_Commit IN VARCHAR2,
456 p_validation_level IN NUMBER,
457 P_PLAN_ID IN NUMBER,
458 P_CUST_PLAN_ID IN NUMBER,
459 P_PARTY_ID IN NUMBER,
460 P_CUST_ACCOUNT_ID IN NUMBER,
461 -- P_CUST_ACCOUNT_ORG IN NUMBER := FND_API.G_MISS_NUM,
462 P_START_DATE_ACTIVE IN DATE,
463 P_END_DATE_ACTIVE IN DATE,
464 P_MANUAL_FLAG IN VARCHAR2,
465 P_PLAN_STATUS_CODE IN VARCHAR2,
466 P_REQUEST_ID IN NUMBER,
467 P_PROGRAM_APPLICATION_ID IN NUMBER,
468 P_PROGRAM_ID IN NUMBER,
469 P_PROGRAM_UPDATE_DATE IN DATE,
470 P_CREATION_DATE IN DATE,
471 P_LAST_UPDATE_DATE IN DATE,
472 P_CREATED_BY IN NUMBER,
473 P_LAST_UPDATED_BY IN NUMBER,
474 P_LAST_UPDATE_LOGIN IN NUMBER,
475 P_ATTRIBUTE1 IN VARCHAR2,
476 P_ATTRIBUTE2 IN VARCHAR2,
477 P_ATTRIBUTE3 IN VARCHAR2,
478 P_ATTRIBUTE4 IN VARCHAR2,
479 P_ATTRIBUTE5 IN VARCHAR2,
480 P_ATTRIBUTE6 IN VARCHAR2,
481 P_ATTRIBUTE7 IN VARCHAR2,
482 P_ATTRIBUTE8 IN VARCHAR2,
483 P_ATTRIBUTE9 IN VARCHAR2,
484 P_ATTRIBUTE10 IN VARCHAR2,
485 P_ATTRIBUTE11 IN VARCHAR2,
486 P_ATTRIBUTE12 IN VARCHAR2,
487 P_ATTRIBUTE13 IN VARCHAR2,
488 P_ATTRIBUTE14 IN VARCHAR2,
489 P_ATTRIBUTE15 IN VARCHAR2,
490 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
491 P_OBJECT_VERSION_NUMBER IN NUMBER,
492 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
493 X_Return_Status OUT NOCOPY VARCHAR2,
494 X_Msg_Count OUT NOCOPY NUMBER,
495 X_Msg_Data OUT NOCOPY VARCHAR2
496 )
497 IS
498 l_csc_cust_plans_rec CSC_CUST_PLANS_REC_TYPE;
499 BEGIN
500
501 CONVERT_COLUMNS_TO_REC_TYPE(
502 P_PLAN_ID => P_PLAN_ID ,
503 P_CUST_PLAN_ID => P_CUST_PLAN_ID ,
504 P_PARTY_ID => P_PARTY_ID ,
505 P_CUST_ACCOUNT_ID => P_CUST_ACCOUNT_ID ,
506 -- P_CUST_ACCOUNT_ORG => P_CUST_ACCOUNT_ORG ,
507 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE ,
508 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE ,
509 P_MANUAL_FLAG => P_MANUAL_FLAG ,
510 P_PLAN_STATUS_CODE => P_PLAN_STATUS_CODE ,
511 P_REQUEST_ID => P_REQUEST_ID ,
512 P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID ,
513 P_PROGRAM_ID => P_PROGRAM_ID ,
514 P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE ,
515 P_CREATION_DATE => P_CREATION_DATE ,
516 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE ,
517 P_CREATED_BY => P_CREATED_BY ,
518 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY ,
519 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ,
520 P_ATTRIBUTE1 => P_ATTRIBUTE1 ,
521 P_ATTRIBUTE2 => P_ATTRIBUTE2 ,
522 P_ATTRIBUTE3 => P_ATTRIBUTE3 ,
523 P_ATTRIBUTE4 => P_ATTRIBUTE4 ,
524 P_ATTRIBUTE5 => P_ATTRIBUTE5 ,
525 P_ATTRIBUTE6 => P_ATTRIBUTE6 ,
526 P_ATTRIBUTE7 => P_ATTRIBUTE7 ,
527 P_ATTRIBUTE8 => P_ATTRIBUTE8 ,
528 P_ATTRIBUTE9 => P_ATTRIBUTE9 ,
529 P_ATTRIBUTE10 => P_ATTRIBUTE10 ,
530 P_ATTRIBUTE11 => P_ATTRIBUTE11 ,
531 P_ATTRIBUTE12 => P_ATTRIBUTE12 ,
532 P_ATTRIBUTE13 => P_ATTRIBUTE13 ,
533 P_ATTRIBUTE14 => P_ATTRIBUTE14 ,
534 P_ATTRIBUTE15 => P_ATTRIBUTE15 ,
535 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY ,
536 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER ,
537 X_CSC_CUST_PLANS_REC_TYPE => l_csc_cust_plans_rec );
538
539 -- issue a call to the create_cust_plans proc. with the record type parameter
540 Update_cust_plans(
541 P_Api_Version_Number => p_api_version_number,
542 P_Init_Msg_List => p_init_msg_list,
543 P_Commit => p_commit,
544 p_validation_level => p_validation_level,
545 P_CSC_CUST_PLANS_Rec => l_csc_cust_plans_rec,
546 X_OBJECT_VERSION_NUMBER => x_object_version_number,
547 X_Return_Status => x_return_status,
548 X_Msg_Count => x_msg_count,
549 X_Msg_Data => x_msg_data);
550
551 END UPDATE_CUST_PLANS; -- end of overloaded procedure;
552
553 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
554 PROCEDURE Update_cust_plans(
555 P_Api_Version_Number IN NUMBER,
556 P_Init_Msg_List IN VARCHAR2,
557 P_Commit IN VARCHAR2,
558 p_validation_level IN NUMBER ,
559 P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_Type,
560 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
561 X_Return_Status OUT NOCOPY VARCHAR2,
562 X_Msg_Count OUT NOCOPY NUMBER,
563 X_Msg_Data OUT NOCOPY VARCHAR2
564 )
565 IS
566 Cursor C_Get_cust_plans IS
567 Select rowid, CUST_PLAN_ID, PLAN_ID,
568 PARTY_ID, CUST_ACCOUNT_ID, -- CUST_ACCOUNT_ORG,
569 START_DATE_ACTIVE, END_DATE_ACTIVE, MANUAL_FLAG,
570 PLAN_STATUS_CODE, REQUEST_ID, PROGRAM_APPLICATION_ID,
571 PROGRAM_ID, PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE,
572 CREATION_DATE, LAST_UPDATED_BY, CREATED_BY,
573 LAST_UPDATE_LOGIN, ATTRIBUTE1, ATTRIBUTE2,
574 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
575 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
576 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
577 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
578 ATTRIBUTE15, ATTRIBUTE_CATEGORY, OBJECT_VERSION_NUMBER
579 From CSC_CUST_PLANS
580 WHERE CUST_PLAN_ID = nvl(p_csc_cust_plans_rec.cust_plan_id, cust_plan_id)
581 AND PLAN_ID = nvl(p_csc_cust_plans_rec.plan_id, plan_id)
582 AND PARTY_ID = nvl(p_csc_cust_plans_rec.party_id, party_id)
583 AND nvl(CUST_ACCOUNT_ID,0) = nvl(p_csc_cust_plans_rec.cust_account_id,
584 nvl(cust_account_id,0) );
585
586 l_api_name CONSTANT VARCHAR2(30) := 'Update_cust_plans';
587 l_api_version_number CONSTANT NUMBER := 1.0;
588
589 l_ref_CSC_CUST_PLANS_rec CSC_cust_plans_PVT.CSC_CUST_PLANS_Rec_Type;
590 --l_tar_CSC_CUST_PLANS_rec CSC_cust_plans_PVT.CSC_CUST_PLANS_Rec_Type := P_CSC_CUST_PLANS_Rec;
591 l_upd_CSC_CUST_PLANS_rec CSC_cust_plans_PVT.CSC_CUST_PLANS_Rec_Type := P_CSC_CUST_PLANS_REC;
592 l_rowid ROWID;
593 BEGIN
594 -- Standard Start of API savepoint
595 SAVEPOINT UPDATE_CUST_PLANS_PVT;
596
597 -- Standard call to check for call compatibility.
598 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
599 p_api_version_number,
600 l_api_name,
601 G_PKG_NAME)
602 THEN
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
604 END IF;
605
606 -- Initialize message list if p_init_msg_list is set to TRUE.
607 IF FND_API.to_Boolean( p_init_msg_list )
608 THEN
609 FND_MSG_PUB.initialize;
610 END IF;
611
612 -- Initialize API return status to SUCCESS
613 x_return_status := FND_API.G_RET_STS_SUCCESS;
614
615 CSC_CUST_PLANS_PKG.Lock_Row(
616 p_CUST_PLAN_ID => p_csc_cust_plans_rec.cust_plan_id,
617 p_PLAN_ID => p_csc_cust_plans_rec.plan_id,
618 p_PARTY_ID => p_csc_cust_plans_rec.party_id,
619 P_CUST_ACCOUNT_ID => p_csc_cust_plans_rec.cust_account_id,
620 -- P_CUST_ACCOUNT_ORG => p_csc_cust_plans_rec.cust_account_org,
621 p_OBJECT_VERSION_NUMBER => p_csc_cust_plans_rec.object_version_number);
622
623 Open C_Get_cust_plans;
624
625 Fetch C_Get_cust_plans into
626 l_rowid,
627 l_ref_CSC_CUST_PLANS_rec.CUST_PLAN_ID,
628 l_ref_CSC_CUST_PLANS_rec.PLAN_ID,
629 l_ref_CSC_CUST_PLANS_rec.PARTY_ID,
630 l_ref_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ID,
631 -- l_ref_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ORG,
632 l_ref_CSC_CUST_PLANS_rec.START_DATE_ACTIVE,
633 l_ref_CSC_CUST_PLANS_rec.END_DATE_ACTIVE,
634 l_ref_CSC_CUST_PLANS_rec.MANUAL_FLAG,
635 l_ref_CSC_CUST_PLANS_rec.PLAN_STATUS_CODE,
636 l_ref_CSC_CUST_PLANS_rec.REQUEST_ID,
637 l_ref_CSC_CUST_PLANS_rec.PROGRAM_APPLICATION_ID,
638 l_ref_CSC_CUST_PLANS_rec.PROGRAM_ID,
639 l_ref_CSC_CUST_PLANS_rec.PROGRAM_UPDATE_DATE,
640 l_ref_CSC_CUST_PLANS_rec.LAST_UPDATE_DATE,
641 l_ref_CSC_CUST_PLANS_rec.CREATION_DATE,
642 l_ref_CSC_CUST_PLANS_rec.LAST_UPDATED_BY,
643 l_ref_CSC_CUST_PLANS_rec.CREATED_BY,
644 l_ref_CSC_CUST_PLANS_rec.LAST_UPDATE_LOGIN,
645 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE1,
646 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE2,
647 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE3,
648 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE4,
649 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE5,
650 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE6,
651 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE7,
652 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE8,
653 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE9,
654 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE10,
655 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE11,
656 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE12,
657 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE13,
658 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE14,
659 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE15,
660 l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE_CATEGORY,
661 l_ref_CSC_CUST_PLANS_rec.OBJECT_VERSION_NUMBER;
662
663 If ( C_Get_cust_plans%NOTFOUND) Then
664 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
665 THEN
666 Close C_Get_cust_plans;
667 FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'API_MISSING_UPDATE_TARGET');
668 FND_MESSAGE.Set_Token ('INFO', 'cust_plans', FALSE);
669 -- FND_MSG_PUB.Add;
670 END IF;
671 raise FND_API.G_EXC_ERROR;
672 END IF;
673 Close C_Get_cust_plans;
674
675 l_upd_csc_cust_plans_rec.cust_account_id := CSC_CORE_UTILS_PVT.Get_G_Miss_Num(p_csc_cust_plans_rec.cust_account_id, l_ref_CSC_CUST_PLANS_rec.CUST_ACCOUNT_ID);
676
677 l_upd_csc_cust_plans_rec.start_date_active := CSC_CORE_UTILS_PVT.Get_G_Miss_date(p_csc_cust_plans_rec.start_date_active, l_ref_CSC_CUST_PLANS_rec.START_DATE_ACTIVE);
678
679 l_upd_csc_cust_plans_rec.end_date_active := CSC_CORE_UTILS_PVT.Get_G_Miss_date(p_csc_cust_plans_rec.end_date_active, l_ref_CSC_CUST_PLANS_rec.END_DATE_ACTIVE);
680
681 l_upd_csc_cust_plans_rec.manual_flag := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.manual_flag, l_ref_CSC_CUST_PLANS_rec.MANUAL_FLAG);
682
683 l_upd_csc_cust_plans_rec.plan_status_code := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.plan_status_code, l_ref_CSC_CUST_PLANS_rec.PLAN_STATUS_CODE);
684
685 l_upd_csc_cust_plans_rec.request_id := CSC_CORE_UTILS_PVT.Get_G_Miss_Num(p_csc_cust_plans_rec.request_id, l_ref_CSC_CUST_PLANS_rec.REQUEST_ID);
686
687 l_upd_csc_cust_plans_rec.program_application_id := CSC_CORE_UTILS_PVT.Get_G_Miss_Num(p_csc_cust_plans_rec.program_application_id, l_ref_CSC_CUST_PLANS_rec.PROGRAM_APPLICATION_ID);
688
689 l_upd_csc_cust_plans_rec.program_id := CSC_CORE_UTILS_PVT.Get_G_Miss_Num(p_csc_cust_plans_rec.program_id, l_ref_CSC_CUST_PLANS_rec.PROGRAM_ID);
690
691 l_upd_csc_cust_plans_rec.program_update_date := CSC_CORE_UTILS_PVT.Get_G_Miss_date(p_csc_cust_plans_rec.program_update_date, l_ref_CSC_CUST_PLANS_rec.PROGRAM_UPDATE_DATE);
692
693 l_upd_csc_cust_plans_rec.last_update_date := CSC_CORE_UTILS_PVT.Get_G_Miss_date(p_csc_cust_plans_rec.last_update_date, l_ref_CSC_CUST_PLANS_rec.LAST_UPDATE_DATE);
694
695 l_upd_csc_cust_plans_rec.attribute1 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute1, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE1);
696
697 l_upd_csc_cust_plans_rec.attribute2 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute2, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE2);
698
699 l_upd_csc_cust_plans_rec.attribute3 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute3, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE3);
700
701 l_upd_csc_cust_plans_rec.attribute4 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute4, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE4);
702
703 l_upd_csc_cust_plans_rec.attribute5 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute5, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE5);
704
705 l_upd_csc_cust_plans_rec.attribute6 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute6, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE6);
706
707 l_upd_csc_cust_plans_rec.attribute7 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute7, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE7);
708
709 l_upd_csc_cust_plans_rec.attribute8 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute8, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE8);
710
711 l_upd_csc_cust_plans_rec.attribute9 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute9, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE9);
712
713 l_upd_csc_cust_plans_rec.attribute10 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute10, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE10);
714
715 l_upd_csc_cust_plans_rec.attribute11 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute11, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE11);
716
717 l_upd_csc_cust_plans_rec.attribute12 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute12, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE12);
718
719 l_upd_csc_cust_plans_rec.attribute13 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute13, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE13);
720
721 l_upd_csc_cust_plans_rec.attribute14 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute14, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE14);
722
723 l_upd_csc_cust_plans_rec.attribute15 := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute15, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE15);
724
725 l_upd_csc_cust_plans_rec.attribute_category := CSC_CORE_UTILS_PVT.Get_G_Miss_Char(p_csc_cust_plans_rec.attribute_category, l_ref_CSC_CUST_PLANS_rec.ATTRIBUTE_CATEGORY);
726
727 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL )
728 THEN
729 -- Invoke validation procedures
730 Validate_csc_cust_plans(
731 p_init_msg_list => FND_API.G_FALSE,
732 p_validation_level => p_validation_level,
733 p_validation_mode => CSC_CORE_UTILS_PVT.G_UPDATE,
734 P_CSC_CUST_PLANS_Rec => L_UPD_CSC_CUST_PLANS_REC,
735 x_return_status => x_return_status,
736 x_msg_count => x_msg_count,
737 x_msg_data => x_msg_data);
738
739 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
740 RAISE FND_API.G_EXC_ERROR;
741 END IF;
742 END IF;
743
744 -- Invoke table handler(CSC_CUST_PLANS_PKG.Update_Row)
745 CSC_CUST_PLANS_PKG.Update_Row(
746 p_CUST_PLAN_ID => l_upd_csc_cust_plans_rec.CUST_PLAN_ID,
747 p_PLAN_ID => l_upd_csc_cust_plans_rec.PLAN_ID,
748 p_PARTY_ID => l_upd_csc_cust_plans_rec.PARTY_ID,
749 p_CUST_ACCOUNT_ID => l_upd_csc_cust_plans_rec.CUST_ACCOUNT_ID,
750 -- p_CUST_ACCOUNT_ORG => l_upd_csc_cust_plans_rec.CUST_ACCOUNT_ORG,
751 p_START_DATE_ACTIVE => l_upd_csc_cust_plans_rec.START_DATE_ACTIVE,
752 p_END_DATE_ACTIVE => l_upd_csc_cust_plans_rec.END_DATE_ACTIVE,
753 p_MANUAL_FLAG => l_upd_csc_cust_plans_rec.MANUAL_FLAG,
754 p_PLAN_STATUS_CODE => l_upd_csc_cust_plans_rec.PLAN_STATUS_CODE,
755 p_REQUEST_ID => l_upd_csc_cust_plans_rec.REQUEST_ID,
756 p_PROGRAM_APPLICATION_ID => l_upd_csc_cust_plans_rec.PROGRAM_APPLICATION_ID,
757 p_PROGRAM_ID => l_upd_csc_cust_plans_rec.PROGRAM_ID,
758 p_PROGRAM_UPDATE_DATE => l_upd_csc_cust_plans_rec.PROGRAM_UPDATE_DATE,
759 p_LAST_UPDATE_DATE => SYSDATE,
760 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
761 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
762 p_ATTRIBUTE1 => l_upd_csc_cust_plans_rec.ATTRIBUTE1,
763 p_ATTRIBUTE2 => l_upd_csc_cust_plans_rec.ATTRIBUTE2,
764 p_ATTRIBUTE3 => l_upd_csc_cust_plans_rec.ATTRIBUTE3,
765 p_ATTRIBUTE4 => l_upd_csc_cust_plans_rec.ATTRIBUTE4,
766 p_ATTRIBUTE5 => l_upd_csc_cust_plans_rec.ATTRIBUTE5,
767 p_ATTRIBUTE6 => l_upd_csc_cust_plans_rec.ATTRIBUTE6,
768 p_ATTRIBUTE7 => l_upd_csc_cust_plans_rec.ATTRIBUTE7,
769 p_ATTRIBUTE8 => l_upd_csc_cust_plans_rec.ATTRIBUTE8,
770 p_ATTRIBUTE9 => l_upd_csc_cust_plans_rec.ATTRIBUTE9,
771 p_ATTRIBUTE10 => l_upd_csc_cust_plans_rec.ATTRIBUTE10,
772 p_ATTRIBUTE11 => l_upd_csc_cust_plans_rec.ATTRIBUTE11,
773 p_ATTRIBUTE12 => l_upd_csc_cust_plans_rec.ATTRIBUTE12,
774 p_ATTRIBUTE13 => l_upd_csc_cust_plans_rec.ATTRIBUTE13,
775 p_ATTRIBUTE14 => l_upd_csc_cust_plans_rec.ATTRIBUTE14,
776 p_ATTRIBUTE15 => l_upd_csc_cust_plans_rec.ATTRIBUTE15,
777 p_ATTRIBUTE_CATEGORY => l_upd_csc_cust_plans_rec.ATTRIBUTE_CATEGORY,
778 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER);
779
780 CSC_CUST_PLANS_AUDIT_PKG.Insert_Row(
781 --px_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID,-- will be selected from the sequence.
782 p_PLAN_ID => l_upd_csc_cust_plans_rec.PLAN_ID ,
783 p_PARTY_ID => l_upd_csc_cust_plans_rec.PARTY_ID ,
784 p_CUST_ACCOUNT_ID => l_upd_csc_cust_plans_rec.CUST_ACCOUNT_ID ,
785 -- p_CUST_ACCOUNT_ORG => l_upd_csc_cust_plans_rec.CUST_ACCOUNT_ORG ,
786 p_PLAN_STATUS_CODE => l_upd_csc_cust_plans_rec.PLAN_STATUS_CODE ,
787 p_REQUEST_ID => l_upd_csc_cust_plans_rec.REQUEST_ID ,
788 p_PROGRAM_APPLICATION_ID => l_upd_csc_cust_plans_rec.PROGRAM_APPLICATION_ID ,
789 p_PROGRAM_ID => l_upd_csc_cust_plans_rec.PROGRAM_ID ,
790 p_PROGRAM_UPDATE_DATE => l_upd_csc_cust_plans_rec.PROGRAM_UPDATE_DATE ,
791 p_CREATION_DATE => SYSDATE,
792 p_LAST_UPDATE_DATE => SYSDATE,
793 p_CREATED_BY => FND_GLOBAL.USER_ID,
794 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
795 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
796 p_ATTRIBUTE1 => l_upd_csc_cust_plans_rec.ATTRIBUTE1 ,
797 p_ATTRIBUTE2 => l_upd_csc_cust_plans_rec.ATTRIBUTE2 ,
798 p_ATTRIBUTE3 => l_upd_csc_cust_plans_rec.ATTRIBUTE3 ,
799 p_ATTRIBUTE4 => l_upd_csc_cust_plans_rec.ATTRIBUTE4 ,
800 p_ATTRIBUTE5 => l_upd_csc_cust_plans_rec.ATTRIBUTE5 ,
801 p_ATTRIBUTE6 => l_upd_csc_cust_plans_rec.ATTRIBUTE6 ,
802 p_ATTRIBUTE7 => l_upd_csc_cust_plans_rec.ATTRIBUTE7 ,
803 p_ATTRIBUTE8 => l_upd_csc_cust_plans_rec.ATTRIBUTE8 ,
804 p_ATTRIBUTE9 => l_upd_csc_cust_plans_rec.ATTRIBUTE9 ,
805 p_ATTRIBUTE10 => l_upd_csc_cust_plans_rec.ATTRIBUTE10 ,
806 p_ATTRIBUTE11 => l_upd_csc_cust_plans_rec.ATTRIBUTE11 ,
807 p_ATTRIBUTE12 => l_upd_csc_cust_plans_rec.ATTRIBUTE12 ,
808 p_ATTRIBUTE13 => l_upd_csc_cust_plans_rec.ATTRIBUTE13 ,
809 p_ATTRIBUTE14 => l_upd_csc_cust_plans_rec.ATTRIBUTE14 ,
810 p_ATTRIBUTE15 => l_upd_csc_cust_plans_rec.ATTRIBUTE15 ,
811 p_ATTRIBUTE_CATEGORY => p_CSC_CUST_PLANS_rec.ATTRIBUTE_CATEGORY,
812 x_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID );
813
814 --denzb***************************************************/
815 -- Standard check for p_commit
816 IF FND_API.to_Boolean( p_commit )
817 THEN
818 COMMIT WORK;
819 END IF;
820
821 -- Standard call to get message count and if count is 1, get message info.
822 FND_MSG_PUB.Count_And_Get
823 ( p_count => x_msg_count,
824 p_data => x_msg_data );
825
826 EXCEPTION
827 WHEN FND_API.G_EXC_ERROR THEN
828 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
829 P_API_NAME => L_API_NAME,
830 P_PKG_NAME => G_PKG_NAME,
831 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
832 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
833 X_MSG_COUNT => X_MSG_COUNT,
834 X_MSG_DATA => X_MSG_DATA,
835 X_RETURN_STATUS => X_RETURN_STATUS);
836 APP_EXCEPTION.RAISE_EXCEPTION;
837
838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
839 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
840 P_API_NAME => L_API_NAME,
841 P_PKG_NAME => G_PKG_NAME,
842 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
843 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
844 X_MSG_COUNT => X_MSG_COUNT,
845 X_MSG_DATA => X_MSG_DATA,
846 X_RETURN_STATUS => X_RETURN_STATUS);
847 APP_EXCEPTION.RAISE_EXCEPTION;
848
849 WHEN OTHERS THEN
850 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
851 P_API_NAME => L_API_NAME,
852 P_PKG_NAME => G_PKG_NAME,
853 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
854 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
855 X_MSG_COUNT => X_MSG_COUNT,
856 X_MSG_DATA => X_MSG_DATA,
857 X_RETURN_STATUS => X_RETURN_STATUS);
858 APP_EXCEPTION.RAISE_EXCEPTION;
859
860 End Update_cust_plans;
861
862 PROCEDURE ENABLE_PLAN (
863 P_Api_Version_Number IN NUMBER,
864 P_Init_Msg_List IN VARCHAR2,
865 P_Commit IN VARCHAR2,
866 p_plan_id IN NUMBER,
867 p_party_id_tbl IN CSC_PARTY_ID_TBL_TYPE,
868 p_plan_status_code IN VARCHAR2,
869 X_OBJ_VER_NUM_TBL OUT NOCOPY CSC_OBJ_VER_NUM_TBL_TYPE,
870 X_Return_Status OUT NOCOPY VARCHAR2,
871 X_Msg_Count OUT NOCOPY NUMBER,
872 X_Msg_Data OUT NOCOPY VARCHAR2
873 )
874 IS
875 l_csc_cust_plans_rec CSC_CUST_PLANS_REC_TYPE;
876 l_api_name CONSTANT VARCHAR2(30) := 'Enable_Plan';
877 l_api_version_number CONSTANT NUMBER := 1.0;
878 BEGIN
879 -- Standard Start of API savepoint
880 SAVEPOINT ENABLE_PLAN_PVT;
881
882 -- Standard call to check for call compatibility.
883 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
884 p_api_version_number,
885 l_api_name,
886 G_PKG_NAME)
887 THEN
888 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
889 END IF;
890
891 -- Initialize message list if p_init_msg_list is set to TRUE.
892 IF FND_API.to_Boolean( p_init_msg_list )
893 THEN
894 FND_MSG_PUB.initialize;
895 END IF;
896
897 -- Initialize API return status to SUCCESS
898 x_return_status := FND_API.G_RET_STS_SUCCESS;
899
900
901 for i in 1..p_party_id_tbl.count
902 loop
903 Update_cust_plans(
904 P_Api_Version_Number => 1.0,
905 P_Init_Msg_List => p_init_msg_list,
906 P_Commit => p_commit,
907 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
908 p_cust_plan_id => NULL,
909 P_PLAN_ID => p_plan_id,
910 P_PARTY_ID => p_party_id_tbl(i).party_id,
911 P_CUST_ACCOUNT_ID => p_party_id_tbl(i).cust_account_id,
912 -- P_CUST_ACCOUNT_ORG => p_party_id_tbl(i).cust_account_org,
913 P_PLAN_STATUS_CODE => p_plan_status_code,
914 P_OBJECT_VERSION_NUMBER => p_party_id_tbl(i).object_version_number,
915 X_OBJECT_VERSION_NUMBER => x_obj_ver_num_tbl(i),
916 X_Return_Status => x_return_status,
917 X_Msg_Count => x_msg_count,
918 X_Msg_Data => x_msg_data );
919
920 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
921 raise FND_API.G_EXC_ERROR;
922 end if;
923 end loop;
924
925
926 /******************* using regular update procedure
927
928 CSC_CUST_PLANS_PKG.LOCK_ROW (
929 p_plan_id => p_plan_id,
930 p_party_id => p_party_id_tbl(i).party_id,
931 p_cust_account_id => p_party_id_tbl(i).cust_account_id,
932 -- p_cust_account_org => p_party_id_tbl(i).cust_account_org,
933 p_object_version_number => p_party_id_tbl(i).object_version_number );
934
935 update csc_cust_plans
936 set plan_status_code = p_plan_status_code,
937 object_version_number = object_version_number + 1
938 where plan_id = p_plan_id
939 and party_id = p_party_id_tbl(i).party_id
940 and nvl(cust_account_id,0) = nvl(p_party_id_tbl(i).cust_account_id,0)
941 and object_version_number = p_party_id_tbl(i).object_version_number;
942
943 X_OBJ_VER_NUM_TBL(i) := p_party_id_tbl(i).object_version_number + 1;
944 end loop;
945 ****************/
946
947 -- Standard check for p_commit
948 IF FND_API.to_Boolean( p_commit )
949 THEN
950 COMMIT WORK;
951 END IF;
952
953 -- Standard call to get message count and if count is 1, get message info.
954 FND_MSG_PUB.Count_And_Get
955 ( p_count => x_msg_count,
956 p_data => x_msg_data );
957
958 END ENABLE_PLAN;
959
960 PROCEDURE DISABLE_PLAN (
961 P_Api_Version_Number IN NUMBER,
962 P_Init_Msg_List IN VARCHAR2,
963 P_Commit IN VARCHAR2,
964 p_plan_id IN NUMBER,
965 p_party_id_tbl IN CSC_PARTY_ID_TBL_TYPE,
966 p_plan_status_code IN VARCHAR2,
967 X_OBJ_VER_NUM_TBL OUT NOCOPY CSC_OBJ_VER_NUM_TBL_TYPE,
968 X_Return_Status OUT NOCOPY VARCHAR2,
969 X_Msg_Count OUT NOCOPY NUMBER,
970 X_Msg_Data OUT NOCOPY VARCHAR2
971 )
972 IS
973 l_csc_cust_plans_rec CSC_CUST_PLANS_REC_TYPE;
974 l_api_name CONSTANT VARCHAR2(30) := 'Disable_Plan';
975 l_api_version_number CONSTANT NUMBER := 1.0;
976 BEGIN
977 -- Standard Start of API savepoint
978 SAVEPOINT DISABLE_PLAN_PVT;
979
980 -- Standard call to check for call compatibility.
981 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
982 p_api_version_number,
983 l_api_name,
984 G_PKG_NAME)
985 THEN
986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
987 END IF;
988
989 -- Initialize message list if p_init_msg_list is set to TRUE.
990 IF FND_API.to_Boolean( p_init_msg_list )
991 THEN
992 FND_MSG_PUB.initialize;
993 END IF;
994
995 -- Initialize API return status to SUCCESS
996 x_return_status := FND_API.G_RET_STS_SUCCESS;
997
998 for i in 1..p_party_id_tbl.count
999 loop
1000 Update_cust_plans(
1001 P_Api_Version_Number => 1.0,
1002 P_Init_Msg_List => p_init_msg_list,
1003 P_Commit => p_commit,
1004 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1005 p_cust_plan_id => NULL,
1006 P_PLAN_ID => p_plan_id,
1007 P_PARTY_ID => p_party_id_tbl(i).party_id,
1008 P_CUST_ACCOUNT_ID => p_party_id_tbl(i).cust_account_id,
1009 -- P_CUST_ACCOUNT_ORG => p_party_id_tbl(i).cust_account_org,
1010 P_PLAN_STATUS_CODE => p_plan_status_code,
1011 P_OBJECT_VERSION_NUMBER => p_party_id_tbl(i).object_version_number,
1012 X_OBJECT_VERSION_NUMBER => x_obj_ver_num_tbl(i),
1013 X_Return_Status => x_return_status,
1014 X_Msg_Count => x_msg_count,
1015 X_Msg_Data => x_msg_data );
1016
1017 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1018 raise FND_API.G_EXC_ERROR;
1019 end if;
1020 end loop;
1021
1022 /******* using regular update procedure to take of audit insert
1023
1024 CSC_CUST_PLANS_PKG.LOCK_ROW (
1025 p_plan_id => p_plan_id,
1026 p_party_id => p_party_id_tbl(i).party_id,
1027 p_cust_account_id => p_party_id_tbl(i).cust_account_id,
1028 -- p_cust_account_org => p_party_id_tbl(i).cust_account_org,
1029 p_object_version_number => p_party_id_tbl(i).object_version_number );
1030
1031 update csc_cust_plans
1032 set plan_status_code = p_plan_status_code,
1033 object_version_number = object_version_number + 1
1034 where plan_id = p_plan_id
1035 and party_id = p_party_id_tbl(i).party_id
1036 and nvl(cust_account_id,0) = nvl(p_party_id_tbl(i).cust_account_id,0)
1037 and object_version_number = p_party_id_tbl(i).object_version_number;
1038
1039 X_OBJ_VER_NUM_TBL(i) := p_party_id_tbl(i).object_version_number + 1;
1040
1041 end loop;
1042 ***********/
1043
1044 -- Standard check for p_commit
1045 IF FND_API.to_Boolean( p_commit )
1046 THEN
1047 COMMIT WORK;
1048 END IF;
1049
1050 -- Standard call to get message count and if count is 1, get message info.
1051 FND_MSG_PUB.Count_And_Get
1052 ( p_count => x_msg_count,
1053 p_data => x_msg_data );
1054
1055 END DISABLE_PLAN;
1056
1057 PROCEDURE Update_for_customized_plans (
1058 P_Api_Version_Number IN NUMBER,
1059 P_Init_Msg_List IN VARCHAR2,
1060 P_Commit IN VARCHAR2,
1061 P_PLAN_ID IN NUMBER,
1062 P_ORIGINAL_PLAN_ID IN NUMBER,
1063 P_PARTY_ID IN NUMBER,
1064 P_CUST_ACCOUNT_ID IN NUMBER := NULL,
1065 -- P_CUST_ACCOUNT_ORG IN NUMBER := NULL,
1066 P_OBJECT_VERSION_NUMBER IN NUMBER,
1067 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
1068 X_Return_Status OUT NOCOPY VARCHAR2,
1069 X_Msg_Count OUT NOCOPY NUMBER,
1070 X_Msg_Data OUT NOCOPY VARCHAR2
1071 )
1072 IS
1073 cursor c1 is
1074 select *
1075 from csc_cust_plans
1076 where plan_id = p_original_plan_id
1077 and party_id = p_party_id
1078 and nvl(cust_account_id,0) = nvl(p_cust_account_id, nvl(cust_account_id,0))
1079 and object_version_number = p_object_version_number;
1080
1081 c1rec c1%rowtype;
1082
1083 l_api_name CONSTANT VARCHAR2(30) := 'Update_for_customized_plans';
1084 l_api_version_number CONSTANT NUMBER := 1.0;
1085
1086 l_cust_plan_id NUMBER;
1087 l_object_version_number NUMBER;
1088 lx_object_version_number NUMBER;
1089 BEGIN
1090 -- Standard Start of API savepoint
1091 SAVEPOINT UPDATE_FOR_CUSTMIZED_PLANS_PVT;
1092
1093 -- Standard call to check for call compatibility.
1094 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1095 p_api_version_number,
1096 l_api_name,
1097 G_PKG_NAME)
1098 THEN
1099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100 END IF;
1101
1102 -- Initialize message list if p_init_msg_list is set to TRUE.
1103 IF FND_API.to_Boolean( p_init_msg_list )
1104 THEN
1105 FND_MSG_PUB.initialize;
1106 END IF;
1107
1108 -- Initialize API return status to SUCCESS
1109 x_return_status := FND_API.G_RET_STS_SUCCESS;
1110
1111 open c1;
1112 fetch c1 into c1rec;
1113
1114 if ( c1%NOTFOUND ) then
1115 close c1;
1116 FND_MESSAGE.Set_Name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'API_MISSING_UPDATE_TARGET');
1117 FND_MESSAGE.Set_Token ('INFO', 'CSC_CUST_PLANS', FALSE);
1118 x_return_status := FND_API.G_RET_STS_ERROR;
1119 raise FND_API.G_EXC_ERROR;
1120 END IF;
1121 close c1;
1122
1123 CSC_CUST_PLANS_PKG.LOCK_ROW(
1124 p_plan_id => p_original_plan_id,
1125 p_party_id => p_party_id,
1126 p_cust_account_id => p_cust_account_id,
1127 -- p_cust_account_org => p_cust_account_org,
1128 p_object_version_number => p_object_version_number );
1129
1130
1131 update csc_cust_plans
1132 SET PLAN_ID = P_PLAN_ID,
1133 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
1134 where plan_id = p_original_plan_id
1135 and party_id = nvl(p_party_id, party_id)
1136 and nvl(cust_account_id, 0) = nvl(p_cust_account_id, nvl(cust_account_id,0) )
1137 and object_version_number = p_object_version_number;
1138
1139 CSC_CUST_PLANS_AUDIT_PKG.Insert_Row(
1140 --px_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID,-- will be selected from the sequence.
1141 p_PLAN_ID => P_PLAN_ID ,
1142 p_PARTY_ID => P_PARTY_ID ,
1143 p_CUST_ACCOUNT_ID => P_CUST_ACCOUNT_ID ,
1144 -- p_CUST_ACCOUNT_ORG => P_CUST_ACCOUNT_ORG ,
1145 p_PLAN_STATUS_CODE => c1rec.PLAN_STATUS_CODE ,
1146 p_REQUEST_ID => c1rec.REQUEST_ID ,
1147 p_PROGRAM_APPLICATION_ID => c1rec.PROGRAM_APPLICATION_ID ,
1148 p_PROGRAM_ID => c1rec.PROGRAM_ID ,
1149 p_PROGRAM_UPDATE_DATE => c1rec.PROGRAM_UPDATE_DATE ,
1150 p_CREATION_DATE => SYSDATE,
1151 p_LAST_UPDATE_DATE => SYSDATE,
1152 p_CREATED_BY => FND_GLOBAL.USER_ID,
1153 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1154 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1155 p_ATTRIBUTE1 => c1rec.ATTRIBUTE1 ,
1156 p_ATTRIBUTE2 => c1rec.ATTRIBUTE2 ,
1157 p_ATTRIBUTE3 => c1rec.ATTRIBUTE3 ,
1158 p_ATTRIBUTE4 => c1rec.ATTRIBUTE4 ,
1159 p_ATTRIBUTE5 => c1rec.ATTRIBUTE5 ,
1160 p_ATTRIBUTE6 => c1rec.ATTRIBUTE6 ,
1161 p_ATTRIBUTE7 => c1rec.ATTRIBUTE7 ,
1162 p_ATTRIBUTE8 => c1rec.ATTRIBUTE8 ,
1163 p_ATTRIBUTE9 => c1rec.ATTRIBUTE9 ,
1164 p_ATTRIBUTE10 => c1rec.ATTRIBUTE10 ,
1165 p_ATTRIBUTE11 => c1rec.ATTRIBUTE11 ,
1166 p_ATTRIBUTE12 => c1rec.ATTRIBUTE12 ,
1167 p_ATTRIBUTE13 => c1rec.ATTRIBUTE13 ,
1168 p_ATTRIBUTE14 => c1rec.ATTRIBUTE14 ,
1169 p_ATTRIBUTE15 => c1rec.ATTRIBUTE15 ,
1170 p_ATTRIBUTE_CATEGORY => c1rec.ATTRIBUTE_CATEGORY,
1171 x_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID );
1172
1173 -- Standard check for p_commit
1174 IF FND_API.to_Boolean( p_commit )
1175 THEN
1176 COMMIT WORK;
1177 END IF;
1178
1179 -- Standard call to get message count and if count is 1, get message info.
1180 FND_MSG_PUB.Count_And_Get
1181 ( p_count => x_msg_count,
1182 p_data => x_msg_data );
1183
1184 END update_for_customized_plans;
1185
1186 PROCEDURE Delete_cust_plans(
1187 P_Api_Version_Number IN NUMBER,
1188 P_Init_Msg_List IN VARCHAR2,
1189 P_Commit IN VARCHAR2,
1190 p_validation_level IN NUMBER ,
1191 P_CUST_PLAN_ID IN NUMBER,
1192 X_Return_Status OUT NOCOPY VARCHAR2,
1193 X_Msg_Count OUT NOCOPY NUMBER,
1194 X_Msg_Data OUT NOCOPY VARCHAR2
1195 )
1196 IS
1197 -- cursor to select the values of CSC_CUST_PLANS table which will be used as
1198 -- input values to do insert into CSC_CUST_PLANS_AUDIT table.
1199 cursor C1 ( C_CUST_PLAN_ID NUMBER ) is
1200 SELECT CUST_PLAN_ID , PLAN_ID , PARTY_ID ,
1201 CUST_ACCOUNT_ID , -- CUST_ACCOUNT_ORG , START_DATE_ACTIVE ,
1202 END_DATE_ACTIVE , MANUAL_FLAG , PLAN_STATUS_CODE ,
1203 REQUEST_ID , PROGRAM_APPLICATION_ID , PROGRAM_ID ,
1204 PROGRAM_UPDATE_DATE , LAST_UPDATE_DATE , CREATION_DATE ,
1205 LAST_UPDATED_BY , CREATED_BY , LAST_UPDATE_LOGIN ,
1206 ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ,
1207 ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 ,
1208 ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 ,
1209 ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 ,
1210 ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 ,
1211 ATTRIBUTE_CATEGORY , OBJECT_VERSION_NUMBER
1212 FROM csc_cust_plans
1213 WHERE cust_plan_id = c_cust_plan_id;
1214
1215 C1REC C1%ROWTYPE;
1216
1217 l_api_name CONSTANT VARCHAR2(30) := 'Delete_cust_plans';
1218 l_api_version_number CONSTANT NUMBER := 1.0;
1219 BEGIN
1220 -- Standard Start of API savepoint
1221 SAVEPOINT DELETE_CUST_PLANS_PVT;
1222
1223 -- Standard call to check for call compatibility.
1224 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1225 p_api_version_number,
1226 l_api_name,
1227 G_PKG_NAME)
1228 THEN
1229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1230 END IF;
1231
1232 -- Initialize message list if p_init_msg_list is set to TRUE.
1233 IF FND_API.to_Boolean( p_init_msg_list )
1234 THEN
1235 FND_MSG_PUB.initialize;
1236 END IF;
1237
1238 -- Initialize API return status to SUCCESS
1239 x_return_status := FND_API.G_RET_STS_SUCCESS;
1240
1241 -- Fetch the values of the cust_plans rec to insert into the audit table.
1242 open c1 (p_cust_plan_id);
1243 fetch c1 into c1rec;
1244 if c1%notfound then
1245 close c1;
1246 raise no_data_found;
1247 end if;
1248 close c1;
1249
1250 -- Invoke table handler(CSC_CUST_PLANS_PKG.Delete_Row)
1251 CSC_CUST_PLANS_PKG.Delete_Row(
1252 p_CUST_PLAN_ID => p_CUST_PLAN_ID);
1253
1254 -- Insert row into CSC_CUST_PLANS_AUDIT table with the selected cursor values
1255 -- and with PLAN_STATUS_CODE = 'DELETED';
1256 CSC_CUST_PLANS_AUDIT_PKG.Insert_Row(
1257 --px_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID,-- will be selected from the sequence.
1258 p_PLAN_ID => c1rec.PLAN_ID ,
1259 p_PARTY_ID => c1rec.PARTY_ID ,
1260 p_CUST_ACCOUNT_ID => c1rec.CUST_ACCOUNT_ID ,
1261 -- p_CUST_ACCOUNT_ORG => c1rec.CUST_ACCOUNT_ORG ,
1262 p_PLAN_STATUS_CODE => CSC_CORE_UTILS_PVT.REMOVE_PLAN,
1263 p_REQUEST_ID => c1rec.REQUEST_ID ,
1264 p_PROGRAM_APPLICATION_ID => c1rec.PROGRAM_APPLICATION_ID ,
1265 p_PROGRAM_ID => c1rec.PROGRAM_ID ,
1266 p_PROGRAM_UPDATE_DATE => c1rec.PROGRAM_UPDATE_DATE ,
1267 p_CREATION_DATE => SYSDATE,
1268 p_LAST_UPDATE_DATE => SYSDATE,
1269 p_CREATED_BY => FND_GLOBAL.USER_ID,
1270 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1271 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1272 p_ATTRIBUTE1 => c1rec.ATTRIBUTE1 ,
1273 p_ATTRIBUTE2 => c1rec.ATTRIBUTE2 ,
1274 p_ATTRIBUTE3 => c1rec.ATTRIBUTE3 ,
1275 p_ATTRIBUTE4 => c1rec.ATTRIBUTE4 ,
1276 p_ATTRIBUTE5 => c1rec.ATTRIBUTE5 ,
1277 p_ATTRIBUTE6 => c1rec.ATTRIBUTE6 ,
1278 p_ATTRIBUTE7 => c1rec.ATTRIBUTE7 ,
1279 p_ATTRIBUTE8 => c1rec.ATTRIBUTE8 ,
1280 p_ATTRIBUTE9 => c1rec.ATTRIBUTE9 ,
1281 p_ATTRIBUTE10 => c1rec.ATTRIBUTE10 ,
1282 p_ATTRIBUTE11 => c1rec.ATTRIBUTE11 ,
1283 p_ATTRIBUTE12 => c1rec.ATTRIBUTE12 ,
1284 p_ATTRIBUTE13 => c1rec.ATTRIBUTE13 ,
1285 p_ATTRIBUTE14 => c1rec.ATTRIBUTE14 ,
1286 p_ATTRIBUTE15 => c1rec.ATTRIBUTE15 ,
1287 p_ATTRIBUTE_CATEGORY => c1rec.ATTRIBUTE_CATEGORY,
1288 x_PLAN_AUDIT_ID => G_PLAN_AUDIT_ID );
1289
1290 -- Standard check for p_commit
1291 IF FND_API.to_Boolean( p_commit )
1292 THEN
1293 COMMIT WORK;
1294 END IF;
1295
1296 -- Standard call to get message count and if count is 1, get message info.
1297 FND_MSG_PUB.Count_And_Get
1298 ( p_count => x_msg_count,
1299 p_data => x_msg_data );
1300
1301 EXCEPTION
1302 WHEN FND_API.G_EXC_ERROR 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 => FND_MSG_PUB.G_MSG_LVL_ERROR,
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
1313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1314 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
1315 P_API_NAME => L_API_NAME,
1316 P_PKG_NAME => G_PKG_NAME,
1317 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1318 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
1319 X_MSG_COUNT => X_MSG_COUNT,
1320 X_MSG_DATA => X_MSG_DATA,
1321 X_RETURN_STATUS => X_RETURN_STATUS);
1322 APP_EXCEPTION.RAISE_EXCEPTION;
1323
1324 WHEN OTHERS THEN
1325 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
1326 P_API_NAME => L_API_NAME,
1327 P_PKG_NAME => G_PKG_NAME,
1328 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
1329 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
1330 X_MSG_COUNT => X_MSG_COUNT,
1331 X_MSG_DATA => X_MSG_DATA,
1332 X_RETURN_STATUS => X_RETURN_STATUS);
1333 APP_EXCEPTION.RAISE_EXCEPTION;
1334
1335 End Delete_cust_plans;
1336
1337
1338 PROCEDURE GET_CUST_PLAN_ID(
1339 P_Api_Version_Number IN NUMBER,
1340 P_Init_Msg_List IN VARCHAR2,
1341 P_WHERE_CLAUSE IN VARCHAR2,
1342 X_CUST_PLAN_ID OUT NOCOPY NUMBER
1343 --X_Return_Status OUT NOCOPY VARCHAR2,
1344 --X_Msg_Count OUT NOCOPY NUMBER,
1345 --X_Msg_Data OUT NOCOPY VARCHAR2
1346 )
1347 IS
1348 sql_stmt varchar2(500);
1349
1350 v_party_id NUMBER(15);
1351 v_plan_id NUMBER(15);
1352 v_c_acct_id NUMBER(15);
1353
1354 v_position1 NUMBER(10);
1355 v_position2 NUMBER(10);
1356
1357 v_where varchar2(300);
1358
1359 BEGIN
1360 -- Initialize message list if p_init_msg_list is set to TRUE.
1361 IF FND_API.to_Boolean( p_init_msg_list )
1362 THEN
1363 FND_MSG_PUB.initialize;
1364 END IF;
1365
1366 -- Initialize API return status to SUCCESS
1367 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
1368
1369 -- sql_stmt := 'select cust_plan_id from csc_cust_plans where ' || p_where_clause;
1370 -- execute immediate sql_stmt into x_cust_plan_id;
1371
1372 /* The code has been modfied to have dynamic sql bind variable complaince
1373 The existing dynamic where clause has two parts, the second part of the where cluase
1374 is return from the ftree funtion there it already constructs the column
1375 values. In order to make the sql to have bind variable complains, the following
1376 changs has been made */
1377
1378 v_where := upper(p_where_clause);
1379 v_position1 := instr(v_where , '=',1,1);
1380 v_position2 := instr(v_where , 'AND' ,1,1);
1381 v_party_id := substr(v_where,v_position1+1,(v_position2-v_position1-1));
1382
1383 v_position1 := instr(v_where,'=',1,2);
1384 v_position2 := instr(v_where , 'AND' ,1,2);
1385 v_plan_id := substr(v_where,v_position1+1,(v_position2-v_position1-1));
1386
1387 if (instr(v_where,'IS',1,1)>0) then
1388 sql_stmt := 'select cust_plan_id from csc_cust_plans where ' ||
1389 ' party_id = :1 and plan_id = :2 and cust_account_id is null ';
1390 execute immediate sql_stmt into x_cust_plan_id using v_party_id,v_plan_id;
1391 else
1392 sql_stmt := 'select cust_plan_id from csc_cust_plans where ' ||
1393 ' party_id = :1 and plan_id = :2 and cust_account_id = :3 ';
1394 v_position1 := instr(v_where,'=',1,3);
1395 v_position2 := length(v_where);
1396 v_c_acct_id := substr(v_where,v_position1+1,(v_position2-v_position1));
1397 execute immediate sql_stmt into x_cust_plan_id using v_party_id,v_plan_id,v_c_acct_id;
1398 end if;
1399
1400 END get_cust_plan_id;
1401
1402
1403 -- Item-level validation procedures
1404
1405 PROCEDURE Validate_CUST_PLAN_ID (
1406 P_Init_Msg_List IN VARCHAR2,
1407 P_Validation_mode IN VARCHAR2,
1408 P_CUST_PLAN_ID IN NUMBER,
1409 X_Return_Status OUT NOCOPY VARCHAR2,
1410 X_Msg_Count OUT NOCOPY NUMBER,
1411 X_Msg_Data OUT NOCOPY VARCHAR2
1412 )
1413 IS
1414 cursor check_dup_id is
1415 select cust_plan_id
1416 from CSC_CUST_PLANS
1417 where cust_plan_id = p_cust_plan_id;
1418
1419 l_cust_plan_id number;
1420 l_api_name varchar2(30) := 'Validate_Cust_Plan_Id';
1421 BEGIN
1422 -- Initialize message list if p_init_msg_list is set to TRUE.
1423 IF FND_API.to_Boolean( p_init_msg_list )
1424 THEN
1425 FND_MSG_PUB.initialize;
1426 END IF;
1427
1428 -- Initialize API return status to SUCCESS
1429 x_return_status := FND_API.G_RET_STS_SUCCESS;
1430
1431 -- validate NOT NULL column.
1432 if (p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE) then
1433 IF(p_CUST_PLAN_ID is NULL or p_CUST_PLAN_ID = FND_API.G_MISS_NUM) then
1434 fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1435 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1436 fnd_message.set_token('NULL_PARAM', 'CUST_PLAN_ID');
1437 -- fnd_msg_pub.add;
1438 x_return_status := FND_API.G_RET_STS_ERROR;
1439 END IF;
1440 end if;
1441
1442 -- validate for duplicate plan_ids.
1443 IF (p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE)
1444 THEN
1445 open check_dup_id;
1446 fetch check_dup_id into l_cust_plan_id;
1447 if check_dup_id%FOUND then
1448 fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_DUPLICATE_VALUE');
1449 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1450 fnd_message.set_token('DUPLICATE_VAL_PARAM', 'CUST_PLAN_ID');
1451 -- fnd_msg_pub.add;
1452 x_return_status := FND_API.G_RET_STS_ERROR;
1453 end if;
1454 close check_dup_id;
1455 END IF;
1456
1457 -- Standard call to get message count and if count is 1, get message info.
1458 FND_MSG_PUB.Count_And_Get
1459 ( p_count => x_msg_count,
1460 p_data => x_msg_data );
1461
1462 END Validate_CUST_PLAN_ID;
1463
1464
1465 PROCEDURE Validate_PLAN_ID (
1466 P_Init_Msg_List IN VARCHAR2,
1467 P_Validation_mode IN VARCHAR2,
1468 P_PLAN_ID IN NUMBER,
1469 X_Return_Status OUT NOCOPY VARCHAR2,
1470 X_Msg_Count OUT NOCOPY NUMBER,
1471 X_Msg_Data OUT NOCOPY VARCHAR2
1472 )
1473 IS
1474 cursor c1 is
1475 select plan_id
1476 from csc_plan_headers_b
1477 where plan_id = p_plan_id;
1478
1479 l_plan_id number;
1480 l_api_name varchar2(30) := 'Validate_Plan_Id';
1481 BEGIN
1482 -- Initialize message list if p_init_msg_list is set to TRUE.
1483 IF FND_API.to_Boolean( p_init_msg_list )
1484 THEN
1485 FND_MSG_PUB.initialize;
1486 END IF;
1487
1488 -- Initialize API return status to SUCCESS
1489 x_return_status := FND_API.G_RET_STS_SUCCESS;
1490
1491 -- validate NOT NULL column
1492 IF(p_PLAN_ID is NULL or p_plan_id = FND_API.G_MISS_NUM) then
1493 fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1494 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1495 fnd_message.set_token('NULL_PARAM', 'PLAN_ID');
1496 -- fnd_msg_pub.add;
1497 x_return_status := FND_API.G_RET_STS_ERROR;
1498 END IF;
1499
1500 -- validate for valid plan_ids.
1501 if ( x_return_status = FND_API.G_RET_STS_SUCCESS ) then
1502 open c1;
1503 fetch c1 into l_plan_id;
1504 if c1%NOTFOUND then
1505 fnd_message.set_name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1506 fnd_message.set_token('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1507 fnd_message.set_token('VALUE', p_plan_id);
1508 fnd_message.set_token('PARAMETER', 'PLAN_ID');
1509 -- fnd_msg_pub.add;
1510 x_return_status := FND_API.G_RET_STS_ERROR;
1511 end if;
1512 close c1;
1513 end if;
1514
1515 -- Standard call to get message count and if count is 1, get message info.
1516 FND_MSG_PUB.Count_And_Get
1517 ( p_count => x_msg_count,
1518 p_data => x_msg_data );
1519
1520 END Validate_PLAN_ID;
1521
1522 PROCEDURE Validate_PARTY_ID (
1523 P_Init_Msg_List IN VARCHAR2,
1524 P_Validation_mode IN VARCHAR2,
1525 P_PARTY_ID IN NUMBER,
1526 X_Return_Status OUT NOCOPY VARCHAR2,
1527 X_Msg_Count OUT NOCOPY NUMBER,
1528 X_Msg_Data OUT NOCOPY VARCHAR2
1529 )
1530 IS
1531 cursor c1 is
1532 select party_id
1533 from jtf_parties_all_v
1534 where party_id = p_party_id;
1535
1536 l_party_id NUMBER;
1537 l_api_name varchar2(30) := 'Validate_Party_Id';
1538 BEGIN
1539 -- Initialize message list if p_init_msg_list is set to TRUE.
1540 IF FND_API.to_Boolean( p_init_msg_list )
1541 THEN
1542 FND_MSG_PUB.initialize;
1543 END IF;
1544
1545 -- Initialize API return status to SUCCESS
1546 x_return_status := FND_API.G_RET_STS_SUCCESS;
1547
1548 -- validate NOT NULL column
1549 IF( p_PARTY_ID is NULL or p_party_id = FND_API.G_MISS_NUM ) then
1550 fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1551 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1552 fnd_message.set_token('NULL_PARAM', 'PARTY_ID');
1553 -- fnd_msg_pub.add;
1554 x_return_status := FND_API.G_RET_STS_ERROR;
1555 END IF;
1556
1557 -- validate for valid party_ids.
1558 if ( x_return_status = FND_API.G_RET_STS_SUCCESS ) then
1559 open c1;
1560 fetch c1 into l_party_id;
1561 if c1%NOTFOUND then
1562 fnd_message.set_name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1563 fnd_message.set_token('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1564 fnd_message.set_token('VALUE', p_party_id);
1565 fnd_message.set_token('PARAMETER', 'PARTY_ID');
1566 -- fnd_msg_pub.add;
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568 end if;
1569 close c1;
1570 end if;
1571
1572 -- Standard call to get message count and if count is 1, get message info.
1573 FND_MSG_PUB.Count_And_Get
1574 ( p_count => x_msg_count,
1575 p_data => x_msg_data );
1576
1577 END Validate_PARTY_ID;
1578
1579
1580 /******************************REMOVE ORG_ID 04-10-2000 ************************
1581 Incorparating TCA changes - Removing org_id reference completely
1582
1583 PROCEDURE Validate_CUST_ACC_ORG_ID (
1584 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1585 P_Validation_mode IN VARCHAR2,
1586 P_PARTY_ID IN NUMBER,
1587 P_CUST_ACCOUNT_ID IN NUMBER,
1588 P_CUST_ACCOUNT_ORG IN NUMBER,
1589 X_Return_Status OUT NOCOPY VARCHAR2,
1590 X_Msg_Count OUT NOCOPY NUMBER,
1591 X_Msg_Data OUT NOCOPY VARCHAR2
1592 )
1593 IS
1594 cursor c1 is
1595 select cust_account_id, org_id
1596 from jtf_cust_accounts_all_v
1597 where party_id = p_party_id
1598 and cust_account_id = p_cust_account_id;
1599
1600 l_cust_account_id number;
1601 l_org_id number;
1602 l_api_name varchar2(30) := 'Validate_Cust_Acc_Org_Id';
1603
1604 BEGIN
1605 -- Initialize message list if p_init_msg_list is set to TRUE.
1606 IF FND_API.to_Boolean( p_init_msg_list )
1607 THEN
1608 FND_MSG_PUB.initialize;
1609 END IF;
1610
1611 -- Initialize API return status to SUCCESS
1612 x_return_status := FND_API.G_RET_STS_SUCCESS;
1613
1614 if (p_cust_account_id is NULL or p_cust_account_org is NULL ) THEN
1615 fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1616 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1617 if ( p_cust_account_id is NULL and p_cust_account_org is NULL ) then
1618 fnd_message.set_token('NULL_PARAM', 'P_CUST_ACCOUNT_ID'||' and '||'P_CUST_ACCOUNT_ORG' );
1619 elsif ( p_cust_account_id is NULL and p_cust_account_org is not NULL ) then
1620 fnd_message.set_token('NULL_PARAM', 'P_CUST_ACCOUNT_ID');
1621 else
1622 fnd_message.set_token('NULL_PARAM', 'P_CUST_ACCOUNT_ORG');
1623 end if;
1624 -- fnd_msg_pub.add;
1625 x_return_status := FND_API.G_RET_STS_ERROR;
1626 end if;
1627
1628 if ( p_cust_account_id is not null and p_cust_account_org is not null ) then
1629 open c1;
1630 fetch c1 into l_cust_account_id, l_org_id;
1631 if c1%NOTFOUND then
1632 fnd_message.set_name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1633 fnd_message.set_token('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1634 fnd_message.set_token('VALUE', p_cust_account_id ||', '|| p_cust_account_org);
1635 fnd_message.set_token('PARAMETER', 'CUST_ACCOUNT_ID, CUST_ACCOUNT_ORG');
1636 -- fnd_msg_pub.add;
1637 x_return_status := FND_API.G_RET_STS_ERROR;
1638 end if;
1639 close c1;
1640 end if;
1641
1642 -- Standard call to get message count and if count is 1, get message info.
1643 FND_MSG_PUB.Count_And_Get
1644 ( p_count => x_msg_count,
1645 p_data => x_msg_data );
1646
1647 END Validate_CUST_ACC_ORG_ID ;
1648 */
1649
1650 PROCEDURE Validate_MANUAL_FLAG (
1651 P_Init_Msg_List IN VARCHAR2,
1652 P_Validation_mode IN VARCHAR2,
1653 P_MANUAL_FLAG IN VARCHAR2,
1654 X_Return_Status OUT NOCOPY VARCHAR2,
1655 X_Msg_Count OUT NOCOPY NUMBER,
1656 X_Msg_Data OUT NOCOPY VARCHAR2
1657 )
1658 IS
1659 l_api_name varchar2(30) := 'Validate_Manual_Flag';
1660 BEGIN
1661 -- Initialize message list if p_init_msg_list is set to TRUE.
1662 IF FND_API.to_Boolean( p_init_msg_list )
1663 THEN
1664 FND_MSG_PUB.initialize;
1665 END IF;
1666
1667 -- Initialize API return status to SUCCESS
1668 x_return_status := FND_API.G_RET_STS_SUCCESS;
1669
1670 -- validate NOT NULL column
1671 IF( p_MANUAL_FLAG is NULL or p_MANUAL_FLAG = FND_API.G_MISS_CHAR ) then
1672 fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1673 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1674 fnd_message.set_token('NULL_PARAM', 'MANUAL_FLAG');
1675 -- fnd_msg_pub.add;
1676 x_return_status := FND_API.G_RET_STS_ERROR;
1677 END IF;
1678
1679 if ( x_return_status = FND_API.G_RET_STS_SUCCESS ) then
1680 if ( p_manual_flag <> 'Y' and p_manual_flag <> 'N' ) then
1681 fnd_message.set_name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1682 fnd_message.set_token('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1683 fnd_message.set_token('VALUE', p_manual_flag);
1684 fnd_message.set_token('PARAMETER', 'P_MANUAL_FLAG');
1685 -- fnd_msg_pub.add;
1686 x_return_status := FND_API.G_RET_STS_ERROR;
1687 end if;
1688 end if;
1689
1690 -- Standard call to get message count and if count is 1, get message info.
1691 FND_MSG_PUB.Count_And_Get
1692 ( p_count => x_msg_count,
1693 p_data => x_msg_data );
1694
1695 END Validate_MANUAL_FLAG;
1696
1697 PROCEDURE Validate_PLAN_STATUS_CODE (
1698 P_Init_Msg_List IN VARCHAR2,
1699 P_Validation_mode IN VARCHAR2,
1700 P_PLAN_STATUS_CODE IN VARCHAR2,
1701 X_Return_Status OUT NOCOPY VARCHAR2,
1702 X_Msg_Count OUT NOCOPY NUMBER,
1703 X_Msg_Data OUT NOCOPY VARCHAR2
1704 )
1705 IS
1706 cursor group_in_lookup is
1707 select count(*)
1708 from csc_lookups
1709 where lookup_type = 'CSC_PLAN_STATUS'
1710 and lookup_code = p_PLAN_STATUS_CODE
1711 and sysdate between nvl(start_date_active, sysdate)
1712 and nvl(end_date_active, sysdate);
1713
1714 l_count NUMBER := 0;
1715 l_api_name varchar2(30) := 'Validate_Plan_Status_Code';
1716 BEGIN
1717 -- Initialize message list if p_init_msg_list is set to TRUE.
1718 IF FND_API.to_Boolean( p_init_msg_list )
1719 THEN
1720 FND_MSG_PUB.initialize;
1721 END IF;
1722
1723 -- Initialize API return status to SUCCESS
1724 x_return_status := FND_API.G_RET_STS_SUCCESS;
1725
1726 -- validate NOT NULL column
1727 IF (p_PLAN_STATUS_CODE is NULL or p_PLAN_STATUS_CODE = FND_API.G_MISS_CHAR) then
1728 fnd_message.set_name (CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_NULL_PARAMETER');
1729 fnd_message.set_token ('API_NAME', G_PKG_NAME||'.'||l_api_name);
1730 fnd_message.set_token('NULL_PARAM', 'PLAN_STATUS_CODE');
1731 -- fnd_msg_pub.add;
1732 x_return_status := FND_API.G_RET_STS_ERROR;
1733 END IF;
1734
1735 -- validate PLAN_STATUS_CODE exists in fnd_lookup_values.
1736 if ( x_return_status = FND_API.G_RET_STS_SUCCESS ) then
1737 open group_in_lookup;
1738 fetch group_in_lookup into l_count;
1739 close group_in_lookup;
1740
1741 if ( l_count = 0 or l_count > 1 ) then
1742 fnd_message.set_name(CSC_CORE_UTILS_PVT.G_APP_SHORTNAME, 'CS_API_ALL_INVALID_ARGUMENT');
1743 fnd_message.set_token('API_NAME', G_PKG_NAME||'.'|| l_api_name);
1744 fnd_message.set_token('VALUE', p_plan_status_code);
1745 fnd_message.set_token('PARAMETER', 'P_STATUS_CODE');
1746 -- fnd_msg_pub.add;
1747 x_return_status := FND_API.G_RET_STS_ERROR;
1748 end if;
1749 end if;
1750
1751 -- Standard call to get message count and if count is 1, get message info.
1752 FND_MSG_PUB.Count_And_Get
1753 ( p_count => x_msg_count,
1754 p_data => x_msg_data );
1755
1756 END Validate_PLAN_STATUS_CODE;
1757
1758
1759 PROCEDURE Validate_csc_cust_plans(
1760 P_Init_Msg_List IN VARCHAR2,
1761 P_Validation_level IN NUMBER ,
1762 P_Validation_mode IN VARCHAR2,
1763 P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_Type,
1764 X_Return_Status OUT NOCOPY VARCHAR2,
1765 X_Msg_Count OUT NOCOPY NUMBER,
1766 X_Msg_Data OUT NOCOPY VARCHAR2
1767 )
1768 IS
1769 l_api_name CONSTANT VARCHAR2(30) := 'Validate_csc_cust_plans';
1770 BEGIN
1771 -- Initialize API return status to SUCCESS
1772 x_return_status := FND_API.G_RET_STS_SUCCESS;
1773
1774 IF (p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
1775
1776 Validate_CUST_PLAN_ID(
1777 p_init_msg_list => FND_API.G_TRUE,
1778 p_validation_mode => p_validation_mode,
1779 p_CUST_PLAN_ID => P_CSC_CUST_PLANS_Rec.CUST_PLAN_ID,
1780 x_return_status => x_return_status,
1781 x_msg_count => x_msg_count,
1782 x_msg_data => x_msg_data);
1783 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1784 raise FND_API.G_EXC_ERROR;
1785 END IF;
1786
1787 Validate_PLAN_ID(
1788 p_init_msg_list => FND_API.G_TRUE,
1789 p_validation_mode => p_validation_mode,
1790 p_PLAN_ID => P_CSC_CUST_PLANS_Rec.PLAN_ID,
1791 x_return_status => x_return_status,
1792 x_msg_count => x_msg_count,
1793 x_msg_data => x_msg_data);
1794 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1795 raise FND_API.G_EXC_ERROR;
1796 END IF;
1797
1798 Validate_PARTY_ID(
1799 p_init_msg_list => FND_API.G_TRUE,
1800 p_validation_mode => p_validation_mode,
1801 p_PARTY_ID => P_CSC_CUST_PLANS_Rec.PARTY_ID,
1802 x_return_status => x_return_status,
1803 x_msg_count => x_msg_count,
1804 x_msg_data => x_msg_data);
1805 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1806 raise FND_API.G_EXC_ERROR;
1807 END IF;
1808
1809 /******************************REMOVE ORG_ID************************
1810 Incorparating TCA changes - Removing org_id reference completely
1811
1812 Validate_CUST_ACC_ORG_ID (
1813 p_init_msg_list => FND_API.G_TRUE,
1814 p_validation_mode => p_validation_mode,
1815 p_PARTY_ID => P_CSC_CUST_PLANS_Rec.PARTY_ID,
1816 p_CUST_ACCOUNT_ID => P_CSC_CUST_PLANS_Rec.CUST_ACCOUNT_ID,
1817 p_CUST_ACCOUNT_ORG => P_CSC_CUST_PLANS_Rec.CUST_ACCOUNT_ORG,
1818 x_return_status => x_return_status,
1819 x_msg_count => x_msg_count,
1820 x_msg_data => x_msg_data);
1821 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1822 raise FND_API.G_EXC_ERROR;
1823 END IF;
1824 **********************************************************/
1825
1826 Validate_MANUAL_FLAG(
1827 p_init_msg_list => FND_API.G_TRUE,
1828 p_validation_mode => p_validation_mode,
1829 p_MANUAL_FLAG => P_CSC_CUST_PLANS_Rec.MANUAL_FLAG,
1830 x_return_status => x_return_status,
1831 x_msg_count => x_msg_count,
1832 x_msg_data => x_msg_data);
1833 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1834 raise FND_API.G_EXC_ERROR;
1835 END IF;
1836
1837 Validate_PLAN_STATUS_CODE(
1838 p_init_msg_list => FND_API.G_TRUE,
1839 p_validation_mode => p_validation_mode,
1840 p_PLAN_STATUS_CODE => P_CSC_CUST_PLANS_Rec.PLAN_STATUS_CODE,
1841 x_return_status => x_return_status,
1842 x_msg_count => x_msg_count,
1843 x_msg_data => x_msg_data);
1844 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1845 raise FND_API.G_EXC_ERROR;
1846 END IF;
1847
1848 /*
1849 CSC_CORE_UTILS_PVT.VALIDATE_NOT_NULLS(
1850 p_init_msg_list => FND_API.G_TRUE,
1851 p_validation_mode => p_validation_mode,
1852 P_COLUMN_NAME => 'CREATION_DATE',
1853 P_COLUMN_VALUE => P_CSC_CUST_PLANS_REC.CREATION_DATE,
1854 x_return_status => x_return_status,
1855 x_msg_count => x_msg_count,
1856 x_msg_data => x_msg_data);
1857 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1858 raise FND_API.G_EXC_ERROR;
1859 END IF;
1860
1861 CSC_CORE_UTILS_PVT.VALIDATE_NOT_NULLS(
1862 p_init_msg_list => FND_API.G_TRUE,
1863 p_validation_mode => p_validation_mode,
1864 P_COLUMN_NAME => 'LAST_UPDATE_DATE',
1865 P_COLUMN_VALUE => P_CSC_CUST_PLANS_REC.LAST_UPDATE_DATE,
1866 x_return_status => x_return_status,
1867 x_msg_count => x_msg_count,
1868 x_msg_data => x_msg_data);
1869 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1870 raise FND_API.G_EXC_ERROR;
1871 END IF;
1872
1873 CSC_CORE_UTILS_PVT.VALIDATE_NOT_NULLS(
1874 p_init_msg_list => FND_API.G_TRUE,
1875 p_validation_mode => p_validation_mode,
1876 P_COLUMN_NAME => 'CREATED_BY',
1877 P_COLUMN_VALUE => P_CSC_CUST_PLANS_REC.CREATED_BY,
1878 x_return_status => x_return_status,
1879 x_msg_count => x_msg_count,
1880 x_msg_data => x_msg_data);
1881 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1882 raise FND_API.G_EXC_ERROR;
1883 END IF;
1884
1885 CSC_CORE_UTILS_PVT.VALIDATE_NOT_NULLS(
1886 p_init_msg_list => FND_API.G_TRUE,
1887 p_validation_mode => p_validation_mode,
1888 P_COLUMN_NAME => 'LAST_UPDATED_BY',
1889 P_COLUMN_VALUE => P_CSC_CUST_PLANS_REC.LAST_UPDATED_BY,
1890 x_return_status => x_return_status,
1891 x_msg_count => x_msg_count,
1892 x_msg_data => x_msg_data);
1893 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1894 raise FND_API.G_EXC_ERROR;
1895 END IF;
1896
1897 CSC_CORE_UTILS_PVT.VALIDATE_NOT_NULLS(
1898 p_init_msg_list => FND_API.G_TRUE,
1899 p_validation_mode => p_validation_mode,
1900 P_COLUMN_NAME => 'OBJECT_VERSION_NUMBER',
1901 P_COLUMN_VALUE => P_CSC_CUST_PLANS_REC.OBJECT_VERSION_NUMBER,
1902 x_return_status => x_return_status,
1903 x_msg_count => x_msg_count,
1904 x_msg_data => x_msg_data);
1905 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1906 raise FND_API.G_EXC_ERROR;
1907 END IF;
1908 */
1909 END IF;
1910
1911 END Validate_csc_cust_plans;
1912
1913 End CSC_CUST_PLANS_PVT;