[Home] [Help]
PACKAGE BODY: APPS.CSC_PLAN_HEADERS_B_PKG
Source
1 PACKAGE BODY CSC_PLAN_HEADERS_B_PKG as
2 /* $Header: csctrlpb.pls 120.3 2005/09/19 00:01:34 vshastry ship $ */
3 -- Start of Comments
4 -- Package name : CSC_PLAN_HEADERS_B_PKG
5 -- Purpose : Table handler for CSC_PLAN_HEADERS_B. Contains procedure to INSERT,
6 -- UPDATE, DISABLE, LOCK records in CSC_PLAN_HEADERS_B table.
7 -- History :
8 -- MM-DD-YYYY NAME MODIFICATIONS
9 -- 10-14-1999 dejoseph Created.
10 -- 12-08-1999 dejoseph 'Arcs'ed in for first code freeze.
11 -- 12-21-1999 dejoseph 'Arcs'ed in for second code freeze.
12 -- 01-03-2000 dejoseph 'Arcs'ed in for third code freeze. (10-JAN-2000)
13 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
14 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
15 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
16 -- 04-05-2000 dejoseph Added ADD_LANGUAGE procedure. This proc. is used to
17 -- restore data integrity to a corrupted base/translation
18 -- pair and also called from $CSC_TOP/admin/sql/CSCNLADD.sql
19 -- and $CSC_TOP/sql/CSCNLINS.sql to do inserts into the TL
20 -- tables when a new languages is added in the database.
21 -- 11-08-2000 madhavan Added procedures TRANSLATE_ROW and LOAD_ROW. Fix to
22 -- bug # 1491195. (load_row is added now itself to follow
23 -- standards and to take care of future requirements to add
24 -- Relationship Plans' seed data)
25 -- 01-18-2001 dejoseph Added parameter "P_NAME" to procedure TRANSLATE_ROW.
26 -- 08-17-2001 dejoseph Made the following changes for 11.5.6 to cater to seeding
27 -- Relationship Plans: Reference bug # 1895567
28 -- - Added p_application_id in procedure insert_row
29 -- - Added p_application_id in procedure update_row
30 -- - Performed check (if l_user_id = 1, then seeded_flag = Y)
31 -- in procedure insert_row, update_row and load_row.
32 -- - In procedure translate_row, changed data type of start and
33 -- end_date_active to varchar2 from date. The conversion to
34 -- date is done here as it cannot be done in the .lct file.
35 -- 08-20-2001 dejoseph Modified procedures insert_row and update_row to insert/update
36 -- seeded_flag and application_id for the _tl tables.
37 -- 08-23-2001 axsubram 1952745 Should insert null for application_id if g_miss_num is
38 -- pass from Form. So if statements are added
39 -- TL inserts/updates need not update/insert application_id, seeded_flag
40 -- 02-18-2002 dejoseph Added changes to uptake new functionality for 11.5.8.
41 -- Ct. / Agent facing application
42 -- - Added new IN parameter END_USER_TYPE in procedures:
43 -- INSERT_ROW, UPDATE_ROW and LOAD_ROW.
44 -- Added the dbdrv command.
45 -- 05-23-2002 dejoseph Added checkfile syntax.
46 -- 11-12-2002 bhroy NOCOPY changes made
47 -- 11-25-2002 bhroy FND_API default removed, added WHENEVER OSERROR EXIT FAILURE ROLLBACK
48 -- 05-24-2004 bhroy Commetend Application ID update for CSC_PLAN_HEADERS_B table, fixed bug# 3643065
49 -- 19-july-2005 tpalaniv Modified the translate_row and load_row APIs to fetch last_updated_by using FND API
50 --
51 -- NOTE :
52 -- End of Comments
53
54
55 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_PLAN_HEADERS_B_PKG';
56 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctrlpb.pls';
57
58 /* PROCEDURE TO DO INSERTS INTO THE MLSED TABLES */
59
60 PROCEDURE Insert_Row(
61 px_PLAN_ID IN OUT NOCOPY NUMBER,
62 p_ORIGINAL_PLAN_ID IN NUMBER,
63 p_PLAN_GROUP_CODE IN VARCHAR2,
64 p_START_DATE_ACTIVE IN DATE,
65 p_END_DATE_ACTIVE IN DATE,
66 p_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
67 p_END_USER_TYPE IN VARCHAR2,
68 p_CUSTOMIZED_PLAN IN VARCHAR2,
69 p_PROFILE_CHECK_ID IN NUMBER,
70 p_RELATIONAL_OPERATOR IN VARCHAR2,
74 p_LAST_UPDATE_DATE IN DATE,
71 p_CRITERIA_VALUE_HIGH IN VARCHAR2,
72 p_CRITERIA_VALUE_LOW IN VARCHAR2,
73 p_CREATION_DATE IN DATE,
75 p_CREATED_BY IN NUMBER,
76 p_LAST_UPDATED_BY IN NUMBER,
77 p_LAST_UPDATE_LOGIN IN NUMBER,
78 p_ATTRIBUTE1 IN VARCHAR2,
79 p_ATTRIBUTE2 IN VARCHAR2,
80 p_ATTRIBUTE3 IN VARCHAR2,
81 p_ATTRIBUTE4 IN VARCHAR2,
82 p_ATTRIBUTE5 IN VARCHAR2,
83 p_ATTRIBUTE6 IN VARCHAR2,
84 p_ATTRIBUTE7 IN VARCHAR2,
85 p_ATTRIBUTE8 IN VARCHAR2,
86 p_ATTRIBUTE9 IN VARCHAR2,
87 p_ATTRIBUTE10 IN VARCHAR2,
88 p_ATTRIBUTE11 IN VARCHAR2,
89 p_ATTRIBUTE12 IN VARCHAR2,
90 p_ATTRIBUTE13 IN VARCHAR2,
91 p_ATTRIBUTE14 IN VARCHAR2,
92 p_ATTRIBUTE15 IN VARCHAR2,
93 p_ATTRIBUTE_CATEGORY IN VARCHAR2,
94 P_DESCRIPTION IN VARCHAR2,
95 P_NAME IN VARCHAR2,
96 P_APPLICATION_ID IN NUMBER,
97 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER )
98 IS
99 cursor C is
100 select rowid
101 from CSC_PLAN_HEADERS_B
102 where PLAN_ID = PX_PLAN_ID ;
103
104
105 CURSOR C2 IS
106 SELECT CSC_PLAN_HEADERS_S.nextval
107 FROM sys.dual;
108
109 l_rowid ROWID;
110 l_seeded_flag VARCHAR2(3);
111 l_application_id NUMBER;
112
113 BEGIN
114 If (px_PLAN_ID IS NULL) OR (px_PLAN_ID = FND_API.G_MISS_NUM) then
115 OPEN C2;
116 FETCH C2 INTO px_PLAN_ID;
117 CLOSE C2;
118 End If;
119
120 /* added 120 for bug 4596220 */
121 if ( p_created_by IN (1, 120) ) then
122 l_seeded_flag := 'Y';
123 else
124 l_seeded_flag := 'N';
125 end if;
126
127 if ( p_application_id = fnd_api.g_miss_num ) then
128 l_application_id := NULL;
129 else
130 l_application_id := p_application_id;
131 end if;
132
133 INSERT INTO CSC_PLAN_HEADERS_B (
134 PLAN_ID,
135 ORIGINAL_PLAN_ID,
136 PLAN_GROUP_CODE,
137 START_DATE_ACTIVE,
138 END_DATE_ACTIVE,
139 USE_FOR_CUST_ACCOUNT,
140 END_USER_TYPE,
141 CUSTOMIZED_PLAN,
142 PROFILE_CHECK_ID,
143 RELATIONAL_OPERATOR,
144 CRITERIA_VALUE_HIGH,
145 CRITERIA_VALUE_LOW,
146 CREATION_DATE,
147 LAST_UPDATE_DATE,
148 CREATED_BY,
149 LAST_UPDATED_BY,
150 LAST_UPDATE_LOGIN,
151 ATTRIBUTE1,
152 ATTRIBUTE2,
153 ATTRIBUTE3,
154 ATTRIBUTE4,
155 ATTRIBUTE5,
156 ATTRIBUTE6,
157 ATTRIBUTE7,
158 ATTRIBUTE8,
159 ATTRIBUTE9,
160 ATTRIBUTE10,
161 ATTRIBUTE11,
162 ATTRIBUTE12,
163 ATTRIBUTE13,
164 ATTRIBUTE14,
165 ATTRIBUTE15,
166 ATTRIBUTE_CATEGORY,
167 APPLICATION_ID,
168 SEEDED_FLAG,
169 OBJECT_VERSION_NUMBER)
170 VALUES (
171 PX_PLAN_ID,
172 nvl(P_ORIGINAL_PLAN_ID, PX_PLAN_ID),
173 P_PLAN_GROUP_CODE,
174 P_START_DATE_ACTIVE,
175 P_END_DATE_ACTIVE,
176 P_USE_FOR_CUST_ACCOUNT,
177 P_END_USER_TYPE,
178 P_CUSTOMIZED_PLAN,
179 P_PROFILE_CHECK_ID,
180 P_RELATIONAL_OPERATOR,
181 P_CRITERIA_VALUE_HIGH,
182 P_CRITERIA_VALUE_LOW,
183 P_CREATION_DATE,
184 P_LAST_UPDATE_DATE,
185 P_CREATED_BY,
186 P_LAST_UPDATED_BY,
187 P_LAST_UPDATE_LOGIN,
188 P_ATTRIBUTE1,
189 P_ATTRIBUTE2,
190 P_ATTRIBUTE3,
191 P_ATTRIBUTE4,
192 P_ATTRIBUTE5,
193 P_ATTRIBUTE6,
194 P_ATTRIBUTE7,
195 P_ATTRIBUTE8,
196 P_ATTRIBUTE9,
197 P_ATTRIBUTE10,
198 P_ATTRIBUTE11,
199 P_ATTRIBUTE12,
200 P_ATTRIBUTE13,
201 P_ATTRIBUTE14,
202 P_ATTRIBUTE15,
203 P_ATTRIBUTE_CATEGORY,
204 L_APPLICATION_ID,
205 L_SEEDED_FLAG,
206 1 -- the first time a record is created, the object_version_number = 1
207 );
208
209 insert into CSC_PLAN_HEADERS_TL (
210 PLAN_ID,
211 NAME,
212 DESCRIPTION,
213 LANGUAGE,
214 SOURCE_LANG,
215 CREATION_DATE,
216 LAST_UPDATE_DATE,
217 CREATED_BY,
218 LAST_UPDATED_BY,
219 LAST_UPDATE_LOGIN
220 ) select
221 PX_PLAN_ID,
222 P_NAME,
223 P_DESCRIPTION,
224 L.LANGUAGE_CODE,
225 userenv('LANG'),
226 P_CREATION_DATE,
227 P_LAST_UPDATE_DATE,
228 P_CREATED_BY,
229 P_LAST_UPDATED_BY,
230 P_LAST_UPDATE_LOGIN
231 from FND_LANGUAGES L
232 where L.INSTALLED_FLAG in ('I', 'B')
233 and not exists
234 (select NULL
235 from CSC_PLAN_HEADERS_TL T
236 where T.PLAN_ID = PX_PLAN_ID
237 and T.LANGUAGE = L.LANGUAGE_CODE);
238
239 x_object_version_number := 1;
240
241 END INSERT_ROW;
242
243 /* PROCEDURE TO DO UPDATES INTO THE MLSED TABLES */
244
245 PROCEDURE Update_Row(
246 p_PLAN_ID IN NUMBER,
247 p_ORIGINAL_PLAN_ID IN NUMBER,
248 p_PLAN_GROUP_CODE IN VARCHAR2,
252 p_END_USER_TYPE IN VARCHAR2,
249 p_START_DATE_ACTIVE IN DATE,
250 p_END_DATE_ACTIVE IN DATE,
251 p_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
253 p_CUSTOMIZED_PLAN IN VARCHAR2,
254 p_PROFILE_CHECK_ID IN NUMBER,
255 p_RELATIONAL_OPERATOR IN VARCHAR2,
256 p_CRITERIA_VALUE_HIGH IN VARCHAR2,
257 p_CRITERIA_VALUE_LOW IN VARCHAR2,
258 p_LAST_UPDATE_DATE IN DATE,
259 p_LAST_UPDATED_BY IN NUMBER,
260 p_LAST_UPDATE_LOGIN IN NUMBER,
261 p_ATTRIBUTE1 IN VARCHAR2,
262 p_ATTRIBUTE2 IN VARCHAR2,
263 p_ATTRIBUTE3 IN VARCHAR2,
264 p_ATTRIBUTE4 IN VARCHAR2,
265 p_ATTRIBUTE5 IN VARCHAR2,
266 p_ATTRIBUTE6 IN VARCHAR2,
267 p_ATTRIBUTE7 IN VARCHAR2,
268 p_ATTRIBUTE8 IN VARCHAR2,
269 p_ATTRIBUTE9 IN VARCHAR2,
270 p_ATTRIBUTE10 IN VARCHAR2,
271 p_ATTRIBUTE11 IN VARCHAR2,
272 p_ATTRIBUTE12 IN VARCHAR2,
273 p_ATTRIBUTE13 IN VARCHAR2,
274 p_ATTRIBUTE14 IN VARCHAR2,
275 p_ATTRIBUTE15 IN VARCHAR2,
276 p_ATTRIBUTE_CATEGORY IN VARCHAR2,
277 P_DESCRIPTION IN VARCHAR2,
278 P_NAME IN VARCHAR2,
279 P_APPLICATION_ID IN NUMBER,
280 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER )
281 IS
282 l_seeded_flag VARCHAR2(3);
283 l_application_id NUMBER;
284 BEGIN
285 /* added 120 for bug 4596220 */
286 if ( p_last_updated_by IN (1, 120) ) then
287 l_seeded_flag := 'Y';
288 else
289 l_seeded_flag := 'N';
290 end if;
291
292 if ( p_application_id = fnd_api.g_miss_num ) then
293 l_application_id := NULL;
294 else
295 l_application_id := p_application_id;
296 end if;
297
298 update CSC_PLAN_HEADERS_B set
299 ORIGINAL_PLAN_ID = P_ORIGINAL_PLAN_ID,
300 PLAN_GROUP_CODE = P_PLAN_GROUP_CODE,
301 START_DATE_ACTIVE = P_START_DATE_ACTIVE,
302 END_DATE_ACTIVE = P_END_DATE_ACTIVE,
303 USE_FOR_CUST_ACCOUNT = P_USE_FOR_CUST_ACCOUNT,
304 END_USER_TYPE = P_END_USER_TYPE,
305 CUSTOMIZED_PLAN = P_CUSTOMIZED_PLAN,
306 PROFILE_CHECK_ID = P_PROFILE_CHECK_ID,
307 RELATIONAL_OPERATOR = P_RELATIONAL_OPERATOR,
308 CRITERIA_VALUE_HIGH = P_CRITERIA_VALUE_HIGH,
309 CRITERIA_VALUE_LOW = P_CRITERIA_VALUE_LOW,
310 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
311 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
312 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
313 ATTRIBUTE1 = P_ATTRIBUTE1,
314 ATTRIBUTE2 = P_ATTRIBUTE2,
315 ATTRIBUTE3 = P_ATTRIBUTE3,
316 ATTRIBUTE4 = P_ATTRIBUTE4,
317 ATTRIBUTE5 = P_ATTRIBUTE5,
318 ATTRIBUTE6 = P_ATTRIBUTE6,
319 ATTRIBUTE7 = P_ATTRIBUTE7,
320 ATTRIBUTE8 = P_ATTRIBUTE8,
321 ATTRIBUTE9 = P_ATTRIBUTE9,
322 ATTRIBUTE10 = P_ATTRIBUTE10,
323 ATTRIBUTE11 = P_ATTRIBUTE11,
324 ATTRIBUTE12 = P_ATTRIBUTE12,
325 ATTRIBUTE13 = P_ATTRIBUTE13,
326 ATTRIBUTE14 = P_ATTRIBUTE14,
327 ATTRIBUTE15 = P_ATTRIBUTE15,
328 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
329 -- APPLICATION_ID = L_APPLICATION_ID,
330 SEEDED_FLAG = L_SEEDED_FLAG,
331 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
332 where PLAN_ID = P_PLAN_ID
333 RETURNING object_version_number INTO x_object_version_number;
334
335 if (sql%notfound) then
336 raise no_data_found;
337 end if;
338
339 update CSC_PLAN_HEADERS_TL set
340 NAME = P_NAME,
341 DESCRIPTION = P_DESCRIPTION,
342 SOURCE_LANG = userenv('LANG'),
343 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
344 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
346 where PLAN_ID = P_PLAN_ID
347 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
348
349 if (sql%notfound) then
350 raise no_data_found;
351 end if;
352
353 end UPDATE_ROW;
354
355 /*** Procedure to diasble plan in MLSed tables *****/
356
357 procedure DISABLE_ROW (
358 P_PLAN_ID IN NUMBER) is
359 begin
360 update CSC_PLAN_HEADERS_B
361 set end_date_active = sysdate+1
362 where plan_id = p_plan_id;
363
364 If (SQL%NOTFOUND) then
365 RAISE NO_DATA_FOUND;
366 End If;
367 end Disable_row;
368
369 /* Procedure to lock row in MLSed tables */
370
371 PROCEDURE Lock_Row(
372 p_PLAN_ID IN NUMBER,
373 p_OBJECT_VERSION_NUMBER IN NUMBER
374 )
375 IS
376 cursor c is
377 select
378 ORIGINAL_PLAN_ID, PLAN_GROUP_CODE, START_DATE_ACTIVE,
379 END_DATE_ACTIVE, USE_FOR_CUST_ACCOUNT, END_USER_TYPE,
380 CUSTOMIZED_PLAN, PROFILE_CHECK_ID, RELATIONAL_OPERATOR,
381 CRITERIA_VALUE_HIGH, CRITERIA_VALUE_LOW, ATTRIBUTE1,
382 ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
383 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
384 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
388 FROM CSC_PLAN_HEADERS_VL
385 ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
386 ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE_CATEGORY,
387 OBJECT_VERSION_NUMBER
389 WHERE PLAN_ID = P_PLAN_ID
390 AND OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER
391 FOR UPDATE OF PLAN_ID NOWAIT;
392
393 recinfo c%rowtype;
394
395 cursor c1 is
396 SELECT NAME, DESCRIPTION,
397 DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
398 FROM CSC_PLAN_HEADERS_TL
399 WHERE PLAN_ID = P_PLAN_ID
400 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
401 FOR UPDATE OF PLAN_ID NOWAIT;
402 begin
403 open c;
404 fetch c into recinfo;
405 if (c%notfound) then
406 close c;
407 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
408 app_exception.raise_exception;
409 end if;
410 close c;
411 return;
412 end LOCK_ROW;
413
414 PROCEDURE ADD_LANGUAGE
415 is
416 begin
417 delete from CSC_PLAN_HEADERS_TL T
418 where not exists
419 (select NULL
420 from CSC_PLAN_HEADERS_B B
421 where B.PLAN_ID = T.PLAN_ID
422 );
423
424 update CSC_PLAN_HEADERS_TL T set (
425 NAME,
426 DESCRIPTION
427 ) = (select
428 B.NAME,
429 B.DESCRIPTION
430 from CSC_PLAN_HEADERS_TL B
431 where B.PLAN_ID = T.PLAN_ID
432 and B.LANGUAGE = T.SOURCE_LANG)
433 where (
434 T.PLAN_ID,
435 T.LANGUAGE
436 ) in (select
437 SUBT.PLAN_ID,
438 SUBT.LANGUAGE
439 from CSC_PLAN_HEADERS_TL SUBB, CSC_PLAN_HEADERS_TL SUBT
440 where SUBB.PLAN_ID = SUBT.PLAN_ID
441 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
442 and (SUBB.NAME <> SUBT.NAME
443 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
444 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
445 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
446 ));
447
448 insert into CSC_PLAN_HEADERS_TL (
449 CREATION_DATE,
450 LAST_UPDATE_DATE,
451 CREATED_BY,
452 LAST_UPDATED_BY,
453 LAST_UPDATE_LOGIN,
454 PLAN_ID,
455 NAME,
456 DESCRIPTION,
457 LANGUAGE,
458 SOURCE_LANG
459 ) select
460 B.CREATION_DATE,
461 B.LAST_UPDATE_DATE,
462 B.CREATED_BY,
463 B.LAST_UPDATED_BY,
464 B.LAST_UPDATE_LOGIN,
465 B.PLAN_ID,
466 B.NAME,
467 B.DESCRIPTION,
468 L.LANGUAGE_CODE,
469 B.SOURCE_LANG
470 from CSC_PLAN_HEADERS_TL B, FND_LANGUAGES L
471 where L.INSTALLED_FLAG in ('I', 'B')
472 and B.LANGUAGE = userenv('LANG')
473 and not exists
474 (select NULL
475 from CSC_PLAN_HEADERS_TL T
476 where T.PLAN_ID = B.PLAN_ID
477 and T.LANGUAGE = L.LANGUAGE_CODE);
478 end ADD_LANGUAGE;
479
480 PROCEDURE TRANSLATE_ROW (
481 p_plan_id IN NUMBER,
482 p_name IN VARCHAR2,
483 p_description IN VARCHAR2,
484 p_owner IN VARCHAR2)
485 IS
486 BEGIN
487 UPDATE csc_plan_headers_tl
488 SET name = p_name,
489 description = NVL(p_description,description),
490 last_update_date = sysdate,
491 last_updated_by = fnd_load_util.owner_id(p_owner), /* R12 ATG Project: Removed the decode logic and using FND API*/
492 last_update_login = 0,
493 source_lang = userenv('LANG')
494 WHERE plan_id = p_plan_id
495 AND userenv('LANG') IN (language, source_lang) ;
496 END TRANSLATE_ROW ;
497
498 PROCEDURE LOAD_ROW (
499 p_PLAN_ID IN NUMBER,
500 p_ORIGINAL_PLAN_ID IN NUMBER,
501 p_PLAN_GROUP_CODE IN VARCHAR2,
502 p_START_DATE_ACTIVE IN VARCHAR2,
503 p_END_DATE_ACTIVE IN VARCHAR2,
504 p_USE_FOR_CUST_ACCOUNT IN VARCHAR2,
505 p_END_USER_TYPE IN VARCHAR2,
506 p_CUSTOMIZED_PLAN IN VARCHAR2,
507 p_PROFILE_CHECK_ID IN NUMBER,
508 p_RELATIONAL_OPERATOR IN VARCHAR2,
509 p_CRITERIA_VALUE_HIGH IN VARCHAR2,
510 p_CRITERIA_VALUE_LOW IN VARCHAR2,
511 p_LAST_UPDATE_DATE IN DATE,
512 p_LAST_UPDATED_BY IN NUMBER,
513 p_LAST_UPDATE_LOGIN IN NUMBER,
514 p_ATTRIBUTE1 IN VARCHAR2,
515 p_ATTRIBUTE2 IN VARCHAR2,
516 p_ATTRIBUTE3 IN VARCHAR2,
517 p_ATTRIBUTE4 IN VARCHAR2,
518 p_ATTRIBUTE5 IN VARCHAR2,
519 p_ATTRIBUTE6 IN VARCHAR2,
520 p_ATTRIBUTE7 IN VARCHAR2,
521 p_ATTRIBUTE8 IN VARCHAR2,
522 p_ATTRIBUTE9 IN VARCHAR2,
523 p_ATTRIBUTE10 IN VARCHAR2,
524 p_ATTRIBUTE11 IN VARCHAR2,
525 p_ATTRIBUTE12 IN VARCHAR2,
526 p_ATTRIBUTE13 IN VARCHAR2,
527 p_ATTRIBUTE14 IN VARCHAR2,
528 p_ATTRIBUTE15 IN VARCHAR2,
529 p_ATTRIBUTE_CATEGORY IN VARCHAR2,
530 P_DESCRIPTION IN VARCHAR2,
531 P_NAME IN VARCHAR2,
532 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
536 l_user_id NUMBER := 0;
533 P_APPLICATION_ID IN NUMBER,
534 P_OWNER IN VARCHAR2)
535 IS
537 l_plan_id NUMBER := CSC_CORE_UTILS_PVT.G_MISS_NUM;
538 l_object_version_number NUMBER := 0;
539 BEGIN
540
541 l_plan_id := p_plan_id;
542
543 update_row(
544 p_PLAN_ID => p_plan_id,
545 p_ORIGINAL_PLAN_ID => p_original_plan_id,
546 p_PLAN_GROUP_CODE => p_plan_group_code,
547 p_START_DATE_ACTIVE => to_date(p_start_date_active,'YYYY/MM/DD' ),
548 p_END_DATE_ACTIVE => to_date(p_end_date_active, 'YYYY/MM/DD'),
549 p_USE_FOR_CUST_ACCOUNT => p_use_for_cust_account,
550 p_END_USER_TYPE => p_end_user_type,
551 p_CUSTOMIZED_PLAN => p_customized_plan,
552 p_PROFILE_CHECK_ID => p_profile_check_id,
553 p_RELATIONAL_OPERATOR => p_relational_operator,
554 p_CRITERIA_VALUE_HIGH => p_criteria_value_high,
555 p_CRITERIA_VALUE_LOW => p_criteria_value_low,
556 p_LAST_UPDATE_DATE => p_last_update_date,
557 p_LAST_UPDATED_BY => p_last_updated_by,
558 p_LAST_UPDATE_LOGIN => 0,
559 p_ATTRIBUTE1 => p_attribute1,
560 p_ATTRIBUTE2 => p_attribute2,
561 p_ATTRIBUTE3 => p_attribute3,
562 p_ATTRIBUTE4 => p_attribute4,
563 p_ATTRIBUTE5 => p_attribute5,
564 p_ATTRIBUTE6 => p_attribute6,
565 p_ATTRIBUTE7 => p_attribute7,
566 p_ATTRIBUTE8 => p_attribute8,
567 p_ATTRIBUTE9 => p_attribute9,
568 p_ATTRIBUTE10 => p_attribute10,
569 p_ATTRIBUTE11 => p_attribute11,
570 p_ATTRIBUTE12 => p_attribute12,
571 p_ATTRIBUTE13 => p_attribute13,
572 p_ATTRIBUTE14 => p_attribute14,
573 p_ATTRIBUTE15 => p_attribute15,
574 p_ATTRIBUTE_CATEGORY => p_attribute_category,
575 P_DESCRIPTION => p_description,
576 P_NAME => p_name,
577 P_APPLICATION_ID => p_application_id,
578 X_OBJECT_VERSION_NUMBER => l_object_version_number );
579
580 EXCEPTION
581 WHEN NO_DATA_FOUND THEN
582 insert_row(
583 px_PLAN_ID => l_plan_id,
584 p_ORIGINAL_PLAN_ID => p_original_plan_id,
585 p_PLAN_GROUP_CODE => p_plan_group_code,
586 p_START_DATE_ACTIVE => to_date(p_start_date_active, 'YYYY/MM/DD'),
587 p_END_DATE_ACTIVE => to_date(p_end_date_active, 'YYYY/MM/DD'),
588 p_USE_FOR_CUST_ACCOUNT => p_use_for_cust_account,
589 p_END_USER_TYPE => p_end_user_type,
590 p_CUSTOMIZED_PLAN => p_customized_plan,
591 p_PROFILE_CHECK_ID => p_profile_check_id,
592 p_RELATIONAL_OPERATOR => p_relational_operator,
593 p_CRITERIA_VALUE_HIGH => p_criteria_value_high,
594 p_CRITERIA_VALUE_LOW => p_criteria_value_low,
595 p_CREATION_DATE => p_last_update_date,
596 p_LAST_UPDATE_DATE => p_last_update_date,
597 p_CREATED_BY => p_last_updated_by,
598 p_LAST_UPDATED_BY => p_last_updated_by,
599 p_LAST_UPDATE_LOGIN => 0,
600 p_ATTRIBUTE1 => p_attribute1,
601 p_ATTRIBUTE2 => p_attribute2,
602 p_ATTRIBUTE3 => p_attribute3,
603 p_ATTRIBUTE4 => p_attribute4,
604 p_ATTRIBUTE5 => p_attribute5,
605 p_ATTRIBUTE6 => p_attribute6,
606 p_ATTRIBUTE7 => p_attribute7,
607 p_ATTRIBUTE8 => p_attribute8,
608 p_ATTRIBUTE9 => p_attribute9,
609 p_ATTRIBUTE10 => p_attribute10,
610 p_ATTRIBUTE11 => p_attribute11,
611 p_ATTRIBUTE12 => p_attribute12,
612 p_ATTRIBUTE13 => p_attribute13,
613 p_ATTRIBUTE14 => p_attribute14,
614 p_ATTRIBUTE15 => p_attribute15,
615 p_ATTRIBUTE_CATEGORY => p_attribute_category,
616 P_DESCRIPTION => p_description,
617 P_NAME => p_name,
618 P_APPLICATION_ID => p_application_id,
619 X_OBJECT_VERSION_NUMBER => l_object_version_number );
620
621 END LOAD_ROW;
622
623
624 End CSC_PLAN_HEADERS_B_PKG;