1 PACKAGE CSC_CUST_PLANS_PVT AUTHID CURRENT_USER as
2 /* $Header: cscvctps.pls 115.14 2002/12/04 16:08:29 bhroy 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 -- 04-10-2000 dejoseph Removed org_id validations and all reference to org_id in lieu
18 -- of TCA's decision to drop column ORG_ID from
19 -- hz_cust_accounts table. Also removed reference to cust_account_org.
20 -- 26-11-2002 bhroy G_MISS_XXX defaults of API parameters removed, added WHENEVER OSERROR EXIT FAILURE ROLLBACK
21
22 -- NOTE :
23 -- End of Comments
24
25 -- Default number of records fetch per call
26 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
27
28 -- Id of record inserted into CSC_CUST_PLANS_AUDIT when-ever and insert, update or
29 -- a delete is performed on the CSC_CUST_PLANS table.
30 -- NOCOPY changes made for OUT NOCOPY parameters
31 G_PLAN_AUDIT_ID NUMBER := FND_API.G_MISS_NUM;
32
33 TYPE CSC_CUST_PLANS_Rec_Type IS RECORD
34 (
35 CUST_PLAN_ID NUMBER ,
36 PLAN_ID NUMBER,
37 PARTY_ID NUMBER,
38 CUST_ACCOUNT_ID NUMBER,
39 -- CUST_ACCOUNT_ORG NUMBER := FND_API.G_MISS_NUM,,
40 START_DATE_ACTIVE DATE ,
41 END_DATE_ACTIVE DATE,
42 MANUAL_FLAG VARCHAR2(3),
43 PLAN_STATUS_CODE VARCHAR2(30),
44 REQUEST_ID NUMBER ,
45 PROGRAM_APPLICATION_ID NUMBER ,
46 PROGRAM_ID NUMBER ,
47 PROGRAM_UPDATE_DATE DATE ,
48 CREATION_DATE DATE ,
49 LAST_UPDATE_DATE DATE ,
50 CREATED_BY NUMBER ,
51 LAST_UPDATED_BY NUMBER ,
52 LAST_UPDATE_LOGIN NUMBER ,
53 ATTRIBUTE1 VARCHAR2(450) ,
54 ATTRIBUTE2 VARCHAR2(450),
55 ATTRIBUTE3 VARCHAR2(450) ,
56 ATTRIBUTE4 VARCHAR2(450),
57 ATTRIBUTE5 VARCHAR2(450) ,
58 ATTRIBUTE6 VARCHAR2(450),
59 ATTRIBUTE7 VARCHAR2(450) ,
60 ATTRIBUTE8 VARCHAR2(450),
61 ATTRIBUTE9 VARCHAR2(450) ,
62 ATTRIBUTE10 VARCHAR2(450),
63 ATTRIBUTE11 VARCHAR2(450) ,
64 ATTRIBUTE12 VARCHAR2(450),
65 ATTRIBUTE13 VARCHAR2(450) ,
66 ATTRIBUTE14 VARCHAR2(450),
67 ATTRIBUTE15 VARCHAR2(450) ,
68 ATTRIBUTE_CATEGORY VARCHAR2(90) ,
69 OBJECT_VERSION_NUMBER NUMBER );
70
71 G_MISS_CSC_CUST_PLANS_REC CSC_CUST_PLANS_Rec_Type;
72
73 TYPE CSC_CUST_PLANS_Tbl_Type IS TABLE OF CSC_CUST_PLANS_Rec_Type
74 INDEX BY BINARY_INTEGER;
75 G_MISS_CSC_CUST_PLANS_TBL CSC_CUST_PLANS_Tbl_Type;
76
77 TYPE CSC_PARTY_ID_REC_TYPE IS RECORD (
78 PARTY_ID NUMBER := NULL,
79 CUST_ACCOUNT_ID NUMBER := NULL,
80 -- CUST_ACCOUNT_ORG NUMBER := NULL,
81 OBJECT_VERSION_NUMBER NUMBER := NULL);
82
83 TYPE CSC_PARTY_ID_TBL_TYPE IS TABLE OF CSC_PARTY_ID_REC_TYPE
84 INDEX BY BINARY_INTEGER;
85 G_MISS_PARTY_ID_TBL CSC_PARTY_ID_TBL_TYPE;
86
87 -- Table of object_version_numbers to store when multiple updates are done in enable and
88 -- disable plans.
89 TYPE CSC_OBJ_VER_NUM_TBL_TYPE IS TABLE OF NUMBER
90 INDEX BY BINARY_INTEGER;
91
92 -- *******************************************************
93 -- Start of Comments
94 -- *******************************************************
95 -- API Name: Create_cust_plans
96 -- Type : Private
97 -- Pre-Req : None.
98 -- Function: Does item level validations if required and calls the insert table
99 -- handler to insert record into CSC_CUST_PLANS table.
100 -- Parameters:
101 -- IN
102 -- p_api_version_number IN NUMBER Required
103 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
104 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
105 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
106 -- P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_Type Required
107 --
108 -- OUT NOCOPY:
109 -- x_cust_plan_id OUT NOCOPY NUMBER
110 -- x_object_version_number OUT NOCOPY NUMBER
111 -- x_return_status OUT NOCOPY VARCHAR2
112 -- x_msg_count OUT NOCOPY NUMBER
113 -- x_msg_data OUT NOCOPY VARCHAR2
114 -- Version : Current version 1.0
115 -- End of Comments
116 --
117 PROCEDURE Create_cust_plans(
118 P_Api_Version_Number IN NUMBER,
119 P_Init_Msg_List IN VARCHAR2 ,
120 P_Commit IN VARCHAR2 ,
121 p_validation_level IN NUMBER ,
122 P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_Type ,
123 X_CUST_PLAN_ID OUT NOCOPY NUMBER,
124 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
125 X_Return_Status OUT NOCOPY VARCHAR2,
126 X_Msg_Count OUT NOCOPY NUMBER,
127 X_Msg_Data OUT NOCOPY VARCHAR2
128 );
129
130 -- *******************************************************
131 -- Start of Comments
132 -- *******************************************************
133 -- API Name: Create_cust_plans (Overloaded procedure to take in a detailed list
134 -- of parameters instead of a record type parameter)
135 -- Type : Private
136 -- Pre-Req :
137 -- Parameters:
138 -- IN
139 -- p_api_version_number IN NUMBER Required
140 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
141 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
142 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
143 -- P_PLAN_ID IN NUMBER := FND_API.G_MISS_NUM,
144 -- P_CUST_PLAN_ID IN NUMBER := FND_API.G_MISS_NUM,
145 -- P_PARTY_ID IN NUMBER := FND_API.G_MISS_NUM,
146 -- P_CUST_ACCOUNT_ID IN NUMBER := FND_API.G_MISS_NUM,
147 -- P_CUST_ACCOUNT_ORG IN NUMBER := FND_API.G_MISS_NUM,
148 -- P_PLAN_NAME IN VARCHAR2(90) := FND_API.G_MISS_CHAR,
149 -- P_GROUP_NAME IN VARCHAR2(80) := FND_API.G_MISS_CHAR,
150 -- P_PARTY_NUMBER IN VARCHAR2(30) := FND_API.G_MISS_CHAR,
151 -- P_PARTY_NAME IN VARCHAR2(255) := FND_API.G_MISS_CHAR,
152 -- P_PARTY_TYPE IN VARCHAR2(30) := FND_API.G_MISS_CHAR,
153 -- P_ACCOUNT_NUMBER IN VARCHAR2(30) := FND_API.G_MISS_CHAR,
154 -- P_ACCOUNT_NAME IN VARCHAR2(240) := FND_API.G_MISS_CHAR,
155 -- P_PRIORITY IN NUMBER := FND_API.G_MISS_NUM,
156 -- P_START_DATE_ACTIVE IN DATE := FND_API.G_MISS_DATE,
157 -- P_END_DATE_ACTIVE IN DATE := FND_API.G_MISS_DATE,
158 -- P_CUSTOMIZED_PLAN IN VARCHAR2(3) := FND_API.G_MISS_CHAR,
159 -- P_USE_FOR_CUST_ACCOUNT IN VARCHAR2(3) := FND_API.G_MISS_CHAR,
160 -- P_PLAN_STATUS_CODE IN VARCHAR2(30) := FND_API.G_MISS_CHAR,
161 -- P_PLAN_STATUS_MEANING IN VARCHAR2(80) := FND_API.G_MISS_CHAR,
162 -- P_MANUAL_FLAG IN VARCHAR2(3) := FND_API.G_MISS_CHAR,
163 -- P_REQUEST_ID IN NUMBER := FND_API.G_MISS_NUM,
164 -- P_PROGRAM_APPLICATION_ID IN NUMBER := FND_API.G_MISS_NUM,
165 -- P_PROGRAM_ID IN NUMBER := FND_API.G_MISS_NUM,
166 -- P_PROGRAM_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
167 -- P_CREATION_DATE IN DATE := FND_API.G_MISS_DATE,
168 -- P_LAST_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
169 -- P_CREATED_BY IN NUMBER := FND_API.G_MISS_NUM,
170 -- P_LAST_UPDATED_BY IN NUMBER := FND_API.G_MISS_NUM,
171 -- P_LAST_UPDATE_LOGIN IN NUMBER := FND_API.G_MISS_NUM,
172 -- P_ATTRIBUTE1 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
173 -- P_ATTRIBUTE2 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
174 -- P_ATTRIBUTE3 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
175 -- P_ATTRIBUTE4 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
176 -- P_ATTRIBUTE5 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
177 -- P_ATTRIBUTE6 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
178 -- P_ATTRIBUTE7 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
179 -- P_ATTRIBUTE8 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
180 -- P_ATTRIBUTE9 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
181 -- P_ATTRIBUTE10 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
182 -- P_ATTRIBUTE11 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
183 -- P_ATTRIBUTE12 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
184 -- P_ATTRIBUTE13 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
185 -- P_ATTRIBUTE14 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
186 -- P_ATTRIBUTE15 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
187 -- P_ATTRIBUTE_CATEGORY IN VARCHAR2(90) := FND_API.G_MISS_CHAR,
188 -- P_OBJECT_VERSION_NUMBER IN NUMBER := FND_API.G_MISS_NUM
189 --
190 -- X_CUST_PLAN_ID OUT NOCOPY NUMBER,
191 -- X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
192 -- x_return_status OUT NOCOPY VARCHAR2
193 -- x_msg_count OUT NOCOPY NUMBER
194 -- x_msg_data OUT NOCOPY VARCHAR2
195 -- Version : Current version 1.0
196 --
197 -- End of Comments
198 --
199
200 PROCEDURE Create_cust_plans(
201 P_Api_Version_Number IN NUMBER,
202 P_Init_Msg_List IN VARCHAR2,
203 P_Commit IN VARCHAR2,
204 p_validation_level IN NUMBER,
205 P_PLAN_ID IN NUMBER,
206 P_CUST_PLAN_ID IN NUMBER,
207 P_PARTY_ID IN NUMBER,
208 P_CUST_ACCOUNT_ID IN NUMBER,
209 -- P_CUST_ACCOUNT_ORG IN NUMBER,
210 P_START_DATE_ACTIVE IN DATE,
211 P_END_DATE_ACTIVE IN DATE,
212 P_MANUAL_FLAG IN VARCHAR2,
213 P_PLAN_STATUS_CODE IN VARCHAR2,
214 P_REQUEST_ID IN NUMBER,
215 P_PROGRAM_APPLICATION_ID IN NUMBER,
216 P_PROGRAM_ID IN NUMBER,
217 P_PROGRAM_UPDATE_DATE IN DATE,
218 P_CREATION_DATE IN DATE,
219 P_LAST_UPDATE_DATE IN DATE,
220 P_CREATED_BY IN NUMBER,
221 P_LAST_UPDATED_BY IN NUMBER,
222 P_LAST_UPDATE_LOGIN IN NUMBER,
223 P_ATTRIBUTE1 IN VARCHAR2,
224 P_ATTRIBUTE2 IN VARCHAR2,
225 P_ATTRIBUTE3 IN VARCHAR2,
226 P_ATTRIBUTE4 IN VARCHAR2,
227 P_ATTRIBUTE5 IN VARCHAR2,
228 P_ATTRIBUTE6 IN VARCHAR2,
229 P_ATTRIBUTE7 IN VARCHAR2,
230 P_ATTRIBUTE8 IN VARCHAR2,
231 P_ATTRIBUTE9 IN VARCHAR2,
232 P_ATTRIBUTE10 IN VARCHAR2,
233 P_ATTRIBUTE11 IN VARCHAR2,
234 P_ATTRIBUTE12 IN VARCHAR2,
235 P_ATTRIBUTE13 IN VARCHAR2,
236 P_ATTRIBUTE14 IN VARCHAR2,
237 P_ATTRIBUTE15 IN VARCHAR2,
238 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
239 P_OBJECT_VERSION_NUMBER IN NUMBER,
240 X_CUST_PLAN_ID OUT NOCOPY NUMBER,
241 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
242 X_Return_Status OUT NOCOPY VARCHAR2,
243 X_Msg_Count OUT NOCOPY NUMBER,
244 X_Msg_Data OUT NOCOPY VARCHAR2
245 );
246
247 -- *******************************************************
248 -- Start of Comments
249 -- *******************************************************
250 -- API Name: Update_cust_plans
251 -- Type : Private
252 -- Pre-Req : Record in CSC_CUST_PLANS to be updated.
253 -- Function: Does item level validations if required and calls the update table handler
254 -- to update the record in CSC_CUST_PLANS.
255 -- Parameters:
256 -- IN
257 -- p_api_version_number IN NUMBER Required
258 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
259 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
260 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
261 -- P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_Type Required
262 --
263 -- OUT NOCOPY :
264 -- X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
265 -- x_return_status OUT NOCOPY VARCHAR2
266 -- x_msg_count OUT NOCOPY NUMBER
267 -- x_msg_data OUT NOCOPY VARCHAR2
268 -- Version : Current version 1.0
269 --
270 -- End of Comments
271 --
272 PROCEDURE Update_cust_plans(
273 P_Api_Version_Number IN NUMBER,
274 P_Init_Msg_List IN VARCHAR2,
275 P_Commit IN VARCHAR2,
276 p_validation_level IN NUMBER ,
277 P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_Type,
278 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
279 X_Return_Status OUT NOCOPY VARCHAR2,
280 X_Msg_Count OUT NOCOPY NUMBER,
281 X_Msg_Data OUT NOCOPY VARCHAR2
282 );
283
284 -- *******************************************************
285 -- Start of Comments
286 -- *******************************************************
287 -- API Name: Update_cust_plans (Overloaded procedure to take in a detailed list
288 -- of parameters instead of a record type parameter)
289 -- Type : Private
290 -- Pre-Req :
291 -- Parameters:
292 -- IN
293 -- p_api_version_number IN NUMBER Required
294 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
295 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
296 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
297 -- P_PLAN_ID IN NUMBER := FND_API.G_MISS_NUM,
298 -- P_CUST_PLAN_ID IN NUMBER := FND_API.G_MISS_NUM,
299 -- P_PARTY_ID IN NUMBER := FND_API.G_MISS_NUM,
300 -- P_CUST_ACCOUNT_ID IN NUMBER := FND_API.G_MISS_NUM,
301 -- P_CUST_ACCOUNT_ORG IN NUMBER := FND_API.G_MISS_NUM,
302 -- P_START_DATE_ACTIVE IN DATE := FND_API.G_MISS_DATE,
303 -- P_END_DATE_ACTIVE IN DATE := FND_API.G_MISS_DATE,
304 -- P_MANUAL_FLAG IN VARCHAR2(3) := FND_API.G_MISS_CHAR,
305 -- P_PLAN_STATUS_CODE IN VARCHAR2(30) := FND_API.G_MISS_CHAR,
306 -- P_REQUEST_ID IN NUMBER := FND_API.G_MISS_NUM,
307 -- P_PROGRAM_APPLICATION_ID IN NUMBER := FND_API.G_MISS_NUM,
308 -- P_PROGRAM_ID IN NUMBER := FND_API.G_MISS_NUM,
309 -- P_PROGRAM_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
310 -- P_CREATION_DATE IN DATE := FND_API.G_MISS_DATE,
311 -- P_LAST_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
312 -- P_CREATED_BY IN NUMBER := FND_API.G_MISS_NUM,
313 -- P_LAST_UPDATED_BY IN NUMBER := FND_API.G_MISS_NUM,
314 -- P_LAST_UPDATE_LOGIN IN NUMBER := FND_API.G_MISS_NUM,
315 -- P_ATTRIBUTE1 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
316 -- P_ATTRIBUTE2 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
317 -- P_ATTRIBUTE3 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
318 -- P_ATTRIBUTE4 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
319 -- P_ATTRIBUTE5 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
320 -- P_ATTRIBUTE6 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
321 -- P_ATTRIBUTE7 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
322 -- P_ATTRIBUTE8 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
323 -- P_ATTRIBUTE9 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
324 -- P_ATTRIBUTE10 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
325 -- P_ATTRIBUTE11 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
326 -- P_ATTRIBUTE12 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
327 -- P_ATTRIBUTE13 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
328 -- P_ATTRIBUTE14 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
329 -- P_ATTRIBUTE15 IN VARCHAR2(450) := FND_API.G_MISS_CHAR,
330 -- P_ATTRIBUTE_CATEGORY IN VARCHAR2(90) := FND_API.G_MISS_CHAR,
331 -- P_OBJECT_VERSION_NUMBER IN NUMBER := FND_API.G_MISS_NUM
332 --
333 -- OUT NOCOPY :
334 -- X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
335 -- x_return_status OUT NOCOPY VARCHAR2
336 -- x_msg_count OUT NOCOPY NUMBER
337 -- x_msg_data OUT NOCOPY VARCHAR2
338 -- Version : Current version 1.0
339 --
340 -- End of Comments
341 --
342 PROCEDURE Update_cust_plans(
343 P_Api_Version_Number IN NUMBER,
344 P_Init_Msg_List IN VARCHAR2,
345 P_Commit IN VARCHAR2,
346 p_validation_level IN NUMBER,
347 P_PLAN_ID IN NUMBER,
348 P_CUST_PLAN_ID IN NUMBER,
349 P_PARTY_ID IN NUMBER,
350 P_CUST_ACCOUNT_ID IN NUMBER,
351 P_START_DATE_ACTIVE IN DATE := NULL,
352 P_END_DATE_ACTIVE IN DATE := NULL,
353 P_MANUAL_FLAG IN VARCHAR2 := NULL,
354 P_PLAN_STATUS_CODE IN VARCHAR2 := NULL,
355 P_REQUEST_ID IN NUMBER := NULL,
356 P_PROGRAM_APPLICATION_ID IN NUMBER := NULL,
357 P_PROGRAM_ID IN NUMBER := NULL,
358 P_PROGRAM_UPDATE_DATE IN DATE := NULL,
359 P_CREATION_DATE IN DATE := NULL,
360 P_LAST_UPDATE_DATE IN DATE := NULL,
361 P_CREATED_BY IN NUMBER := NULL,
362 P_LAST_UPDATED_BY IN NUMBER := NULL,
363 P_LAST_UPDATE_LOGIN IN NUMBER := NULL,
364 P_ATTRIBUTE1 IN VARCHAR2 := NULL,
365 P_ATTRIBUTE2 IN VARCHAR2 := NULL,
366 P_ATTRIBUTE3 IN VARCHAR2 := NULL,
367 P_ATTRIBUTE4 IN VARCHAR2 := NULL,
368 P_ATTRIBUTE5 IN VARCHAR2 := NULL,
369 P_ATTRIBUTE6 IN VARCHAR2 := NULL,
370 P_ATTRIBUTE7 IN VARCHAR2 := NULL,
371 P_ATTRIBUTE8 IN VARCHAR2 := NULL,
372 P_ATTRIBUTE9 IN VARCHAR2 := NULL,
373 P_ATTRIBUTE10 IN VARCHAR2 := NULL,
374 P_ATTRIBUTE11 IN VARCHAR2 := NULL,
375 P_ATTRIBUTE12 IN VARCHAR2 := NULL,
376 P_ATTRIBUTE13 IN VARCHAR2 := NULL,
377 P_ATTRIBUTE14 IN VARCHAR2 := NULL,
378 P_ATTRIBUTE15 IN VARCHAR2 := NULL,
379 P_ATTRIBUTE_CATEGORY IN VARCHAR2 := NULL,
380 P_OBJECT_VERSION_NUMBER IN NUMBER,
381 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
382 X_Return_Status OUT NOCOPY VARCHAR2,
383 X_Msg_Count OUT NOCOPY NUMBER,
384 X_Msg_Data OUT NOCOPY VARCHAR2
385 );
386
387
388 -- *******************************************************
389 -- Start of Comments
390 -- *******************************************************
391 -- API Name: ENABLE_PLAN
392 -- Type : Private
393 -- Pre-Req : PLAN_STATUS_CODE column in CSC_CUST_PLANS to be updated to 'ENABLED'.
394 -- Function: Calls the update procedure to update the given record in
395 -- CSC_CUST_PLANS.
396 -- Parameters:
397 -- IN
398 -- p_api_version_number IN NUMBER Required
399 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
400 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
401 -- p_plan_id IN NUMBER Required
402 -- p_party_id_tbl IN CSC_PARTY_ID_TBL_TYPE
403 -- Required
404 -- p_plan_status_code IN VARCHAR2 Optional Default =
405 -- CSC_CORE_UTILS_PVT.ENABLE_PLAN
406 -- OUT NOCOPY :
407 -- x_obj_ver_num_tbl OUT NOCOPY CSC_OBJ_VER_NUM_TBL_TYPE
408 -- x_return_status OUT NOCOPY VARCHAR2
409 -- x_msg_count OUT NOCOPY NUMBER
410 -- x_msg_data OUT NOCOPY VARCHAR2
411 -- Version : Current version 1.0
412 --
413 -- End of Comments
414 --
415 PROCEDURE ENABLE_PLAN (
416 P_Api_Version_Number IN NUMBER,
417 P_Init_Msg_List IN VARCHAR2,
418 P_Commit IN VARCHAR2,
419 p_plan_id IN NUMBER,
420 p_party_id_tbl IN CSC_PARTY_ID_TBL_TYPE,
421 p_plan_status_code IN VARCHAR2 := CSC_CORE_UTILS_PVT.ENABLE_PLAN,
422 X_OBJ_VER_NUM_TBL OUT NOCOPY CSC_OBJ_VER_NUM_TBL_TYPE,
423 X_Return_Status OUT NOCOPY VARCHAR2,
424 X_Msg_Count OUT NOCOPY NUMBER,
425 X_Msg_Data OUT NOCOPY VARCHAR2 );
426
427 -- *******************************************************
428 -- Start of Comments
429 -- *******************************************************
430 -- API Name: DISABLE_PLAN
431 -- Type : Private
432 -- Pre-Req : PLAN_STATUS_CODE column in CSC_CUST_PLANS to be updated to 'DISABLED'.
433 -- Function: Calls the update procedure to update the given record in
434 -- CSC_CUST_PLANS.
435 -- Parameters:
436 -- IN
437 -- p_api_version_number IN NUMBER Required
438 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
439 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
440 -- p_plan_id IN NUMBER Required
441 -- p_party_id_tbl IN CSC_PARTY_ID_TBL_TYPE
442 -- Required
443 -- p_plan_status_code IN NUMBER Optional Default =
444 -- CSC_CORE_UTILS_PVT.DISABLE_PLAN
445 -- OUT NOCOPY :
446 -- x_obj_ver_num_tbl OUT NOCOPY CSC_OBJ_VER_NUM_TBL_TYPE
447 -- x_return_status OUT NOCOPY VARCHAR2
448 -- x_msg_count OUT NOCOPY NUMBER
449 -- x_msg_data OUT NOCOPY VARCHAR2
450 -- Version : Current version 1.0
451 --
452 -- End of Comments
453 --
454 PROCEDURE DISABLE_PLAN (
455 P_Api_Version_Number IN NUMBER,
456 P_Init_Msg_List IN VARCHAR2,
457 P_Commit IN VARCHAR2,
458 p_plan_id IN NUMBER,
459 p_party_id_tbl IN CSC_PARTY_ID_TBL_TYPE,
460 p_plan_status_code IN VARCHAR2 := CSC_CORE_UTILS_PVT.DISABLE_PLAN,
461 X_OBJ_VER_NUM_TBL OUT NOCOPY CSC_OBJ_VER_NUM_TBL_TYPE,
462 X_Return_Status OUT NOCOPY VARCHAR2,
463 X_Msg_Count OUT NOCOPY NUMBER,
464 X_Msg_Data OUT NOCOPY VARCHAR2 );
465
466 -- *******************************************************
467 -- Start of Comments
468 -- *******************************************************
469 -- API Name: Delete_cust_plans
470 -- Type : Private
471 -- Pre-Req : Record in CSC_CUST_PLANS to be deleted.
472 -- Function: Calls the delete table handler to delete a given record in
473 -- CSC_CUST_PLANS.
474 -- Parameters:
475 -- IN
476 -- p_api_version_number IN NUMBER Required
477 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
478 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
479 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
480 -- P_CUST_PLAN_ID IN NUMBER Required
481 --
482 -- OUT NOCOPY :
483 -- x_return_status OUT NOCOPY VARCHAR2
484 -- x_msg_count OUT NOCOPY NUMBER
485 -- x_msg_data OUT NOCOPY VARCHAR2
486 -- Version : Current version 1.0
487 --
488 -- End of Comments
489 --
490 PROCEDURE Delete_cust_plans(
491 P_Api_Version_Number IN NUMBER,
492 P_Init_Msg_List IN VARCHAR2,
493 P_Commit IN VARCHAR2,
494 p_validation_level IN NUMBER ,
495 P_CUST_PLAN_ID IN NUMBER,
496 X_Return_Status OUT NOCOPY VARCHAR2,
497 X_Msg_Count OUT NOCOPY NUMBER,
498 X_Msg_Data OUT NOCOPY VARCHAR2
499 );
500
501 -- *******************************************************
502 -- Start of Comments
503 -- *******************************************************
504 -- API Name: Update_for_customized_plans
505 -- Type : Private
506 -- Pre-Req : Record in CSC_CUST_PLANS to be updated to the new customized plan.
507 -- Function: Calls the update table handler to update the plan_ids of the specified
508 -- parties, to the new customized plan id;
509 -- Parameters:
510 -- IN
511 -- p_api_version_number IN NUMBER Required
512 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
513 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
514 -- p_plan_id IN NUMBER Required
515 -- p_original_plan_id IN NUMBER Required
516 -- p_party_id IN NUMBER Required
517 -- p_cust_account_id IN NUMBER Optional Default = NULL
518 -- p_cust_account_org IN NUMBER Optional Default = NULL
519 -- p_object_version_number IN NUMBER Required
520 --
521 -- OUT NOCOPY :
522 -- x_object_version_number OUT NOCOPY NUMBER
523 -- x_return_status OUT NOCOPY VARCHAR2
524 -- x_msg_count OUT NOCOPY NUMBER
525 -- x_msg_data OUT NOCOPY VARCHAR2
526 -- Version : Current version 1.0
527 --
528 -- End of Comments
529 --
530 PROCEDURE Update_for_customized_plans (
531 P_Api_Version_Number IN NUMBER,
532 P_Init_Msg_List IN VARCHAR2,
533 P_Commit IN VARCHAR2,
534 P_PLAN_ID IN NUMBER,
535 P_ORIGINAL_PLAN_ID IN NUMBER,
536 P_PARTY_ID IN NUMBER,
537 P_CUST_ACCOUNT_ID IN NUMBER := NULL,
538 -- P_CUST_ACCOUNT_ORG IN NUMBER := NULL,
539 P_OBJECT_VERSION_NUMBER IN NUMBER,
540 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
541 X_Return_Status OUT NOCOPY VARCHAR2,
542 X_Msg_Count OUT NOCOPY NUMBER,
543 X_Msg_Data OUT NOCOPY VARCHAR2
544 );
545
546 -- *******************************************************
547 -- Start of Comments
548 -- *******************************************************
549 -- API Name: get_cust_plan_id
550 -- Type : Private
551 -- Pre-Req : Record in CSC_CUST_PLANS to be deleted.
552 -- Function: This function is used with varying where clauses to return back the
553 -- primary key CUST_PLAN_ID.
554 --
555 -- Parameters:
556 -- IN
557 -- p_api_version_number IN NUMBER Required
558 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
559 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
560 -- p_where_clause IN VARCHAR2
561 --
562 -- OUT NOCOPY :
563 -- x_cust_plan_id OUT NOCOPY NUMBER
564 -- x_return_status OUT NOCOPY VARCHAR2
565 -- x_msg_count OUT NOCOPY NUMBER
566 -- x_msg_data OUT NOCOPY VARCHAR2
567 -- Version : Current version 1.0
568 --
569 -- End of Comments
570 --
571 PROCEDURE GET_CUST_PLAN_ID(
572 P_Api_Version_Number IN NUMBER,
573 P_Init_Msg_List IN VARCHAR2,
574 P_WHERE_CLAUSE IN VARCHAR2,
575 X_CUST_PLAN_ID OUT NOCOPY NUMBER
576 --X_Return_Status OUT NOCOPY VARCHAR2,
577 --X_Msg_Count OUT NOCOPY NUMBER,
578 --X_Msg_Data OUT NOCOPY VARCHAR2
579 );
580
581 -- *******************************************************
582 -- Start of Comments
583 -- *******************************************************
584 -- API Name: Validate_Cust_Plan_Id
585 -- Type : Private
586 -- Pre-Req :
587 -- Function:
588 -- Parameters:
589 -- IN
590 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
591 -- p_validation_mode IN VARCHAR2 Required
592 -- P_CUST_PLAN_ID IN NUMBER Required
593 --
594 -- OUT NOCOPY :
595 -- x_return_status OUT NOCOPY VARCHAR2
596 -- x_msg_count OUT NOCOPY NUMBER
597 -- x_msg_data OUT NOCOPY VARCHAR2
598 -- Version : Current version 1.0
599 --
600 -- End of Comments
601 --
602 PROCEDURE Validate_CUST_PLAN_ID (
603 P_Init_Msg_List IN VARCHAR2,
604 P_Validation_mode IN VARCHAR2,
605 P_CUST_PLAN_ID IN NUMBER,
606 X_Return_Status OUT NOCOPY VARCHAR2,
607 X_Msg_Count OUT NOCOPY NUMBER,
608 X_Msg_Data OUT NOCOPY VARCHAR2
609 );
610
611 -- *******************************************************
612 -- Start of Comments
613 -- *******************************************************
614 -- API Name: Validate_Plan_Id
615 -- Type : Private
616 -- Pre-Req :
617 -- Function:
618 -- Parameters:
619 -- IN
620 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
621 -- p_validation_mode IN VARCHAR2 Required
622 -- p_identity_salesforce_id IN NUMBER Optional Default = NULL
623 -- P_PLAN_ID IN NUMBER Required
624 --
625 -- OUT NOCOPY :
626 -- x_return_status OUT NOCOPY VARCHAR2
627 -- x_msg_count OUT NOCOPY NUMBER
628 -- x_msg_data OUT NOCOPY VARCHAR2
629 -- Version : Current version 1.0
630 --
631 -- End of Comments
632 --
633 PROCEDURE Validate_PLAN_ID (
634 P_Init_Msg_List IN VARCHAR2,
635 P_Validation_mode IN VARCHAR2,
636 P_PLAN_ID IN NUMBER,
637 X_Return_Status OUT NOCOPY VARCHAR2,
638 X_Msg_Count OUT NOCOPY NUMBER,
639 X_Msg_Data OUT NOCOPY VARCHAR2
640 );
641
642 -- *******************************************************
643 -- Start of Comments
644 -- *******************************************************
645 -- API Name: Validate_Party_Id
646 -- Type : Private
647 -- Pre-Req :
648 -- Function:
649 -- Parameters:
650 -- IN
651 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
652 -- p_validation_mode IN VARCHAR2 Required
653 -- P_PARTY_ID IN NUMBER Required
654 --
655 -- OUT NOCOPY :
656 -- x_return_status OUT NOCOPY VARCHAR2
657 -- x_msg_count OUT NOCOPY NUMBER
658 -- x_msg_data OUT NOCOPY VARCHAR2
659 -- Version : Current version 1.0
660 --
661 -- End of Comments
662 --
663 PROCEDURE Validate_PARTY_ID (
664 P_Init_Msg_List IN VARCHAR2,
665 P_Validation_mode IN VARCHAR2,
666 P_PARTY_ID IN NUMBER,
667 X_Return_Status OUT NOCOPY VARCHAR2,
668 X_Msg_Count OUT NOCOPY NUMBER,
669 X_Msg_Data OUT NOCOPY VARCHAR2
670 );
671
672 -- Removing org_id completely in lieu of TCA's decision to drop column org_id
673 -- from HZ_CUST_ACCOUNTS.
674 -- *******************************************************
675 -- Start of Comments
676 -- *******************************************************
677 -- API Name: Validate_Cust_Acc_Org_Id
678 -- Type : Private
679 -- Pre-Req :
680 -- Function:
681 -- Parameters:
682 -- IN
683 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
684 -- p_validation_mode IN VARCHAR2 Required
685 -- P_Party_Id IN NUMBER Required
686 -- P_Cust_Account_Id IN NUMBER Required
687 -- P_Cust_Account_Org IN NUMBER Required
688 --
689 -- OUT NOCOPY :
690 -- x_return_status OUT NOCOPY VARCHAR2
691 -- x_msg_count OUT NOCOPY NUMBER
692 -- x_msg_data OUT NOCOPY VARCHAR2
693 -- Version : Current version 1.0
694 --
695 -- End of Comments
696 --
697 /****
698 PROCEDURE Validate_CUST_ACC_ORG_ID (
699 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
700 P_Validation_mode IN VARCHAR2,
701 P_PARTY_ID IN NUMBER,
702 P_CUST_ACCOUNT_ID IN NUMBER,
703 P_CUST_ACCOUNT_ORG IN NUMBER,
704 X_Return_Status OUT VARCHAR2,
705 X_Msg_Count OUT NUMBER,
706 X_Msg_Data OUT VARCHAR2
707 );
708 ********/
709
710 -- *******************************************************
711 -- Start of Comments
712 -- *******************************************************
713 -- API Name: Validate_Manual_Flag
714 -- Type : Private
715 -- Pre-Req :
716 -- Function:
717 -- Parameters:
718 -- IN
719 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
720 -- p_validation_mode IN NUMBER Required
721 -- P_MANUAL_FLAG IN VARCHAR2 Required
722 --
723 -- OUT NOCOPY :
724 -- x_return_status OUT NOCOPY VARCHAR2
725 -- x_msg_count OUT NOCOPY NUMBER
726 -- x_msg_data OUT NOCOPY VARCHAR2
727 -- Version : Current version 1.0
728 --
729 -- End of Comments
730 --
731 PROCEDURE Validate_MANUAL_FLAG (
732 P_Init_Msg_List IN VARCHAR2,
733 P_Validation_mode IN VARCHAR2,
734 P_MANUAL_FLAG IN VARCHAR2,
735 X_Return_Status OUT NOCOPY VARCHAR2,
736 X_Msg_Count OUT NOCOPY NUMBER,
737 X_Msg_Data OUT NOCOPY VARCHAR2
738 );
739
740
741 -- *******************************************************
742 -- Start of Comments
743 -- *******************************************************
744 -- API Name: Validate_Plan_Status_Code
745 -- Type : Private
746 -- Pre-Req :
747 -- Function:
748 -- Parameters:
749 -- IN
750 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
751 -- p_validation_mode IN NUMBER Required
752 -- P_Plan_Status_Code IN VARCHAR2 Required
753 --
754 -- OUT NOCOPY :
755 -- x_return_status OUT NOCOPY VARCHAR2
756 -- x_msg_count OUT NOCOPY NUMBER
757 -- x_msg_data OUT NOCOPY VARCHAR2
758 -- Version : Current version 1.0
759 --
760 -- End of Comments
761 --
762 PROCEDURE Validate_PLAN_STATUS_CODE (
763 P_Init_Msg_List IN VARCHAR2,
764 P_Validation_mode IN VARCHAR2,
765 P_PLAN_STATUS_CODE IN VARCHAR2,
766 X_Return_Status OUT NOCOPY VARCHAR2,
767 X_Msg_Count OUT NOCOPY NUMBER,
768 X_Msg_Data OUT NOCOPY VARCHAR2
769 );
770
771 -- *******************************************************
772 -- Start of Comments
773 -- *******************************************************
774 -- API Name: Validate_csc_cust_plans
775 -- Type : Private
776 -- Pre-Req :
777 -- Function:
778 -- Parameters:
779 -- IN
780 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
781 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
782 -- p_validation_mode IN VARCHAR2 Required
783 -- P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_Type Required
784 --
785 -- OUT NOCOPY :
786 -- x_return_status OUT NOCOPY VARCHAR2
787 -- x_msg_count OUT NOCOPY NUMBER
788 -- x_msg_data OUT NOCOPY VARCHAR2
789 -- Version : Current version 1.0
790 --
791 -- End of Comments
792 --
793 PROCEDURE Validate_csc_cust_plans(
794 P_Init_Msg_List IN VARCHAR2,
795 P_Validation_level IN NUMBER ,
796 P_Validation_mode IN VARCHAR2,
797 P_CSC_CUST_PLANS_Rec IN CSC_CUST_PLANS_Rec_Type,
798 X_Return_Status OUT NOCOPY VARCHAR2,
799 X_Msg_Count OUT NOCOPY NUMBER,
800 X_Msg_Data OUT NOCOPY VARCHAR2
801 );
802 End CSC_CUST_PLANS_PVT;