[Home] [Help]
PACKAGE BODY: APPS.ZX_FC_CODES_PKG
Source
1 package body ZX_FC_CODES_PKG as
2 /* $Header: zxcfccodesb.pls 120.8.12010000.2 2008/11/28 12:52:33 nisinha ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_CLASSIFICATION_ID in NUMBER,
7 X_Classification_Code in VARCHAR2,
8 X_CLASSIFICATION_TYPE_CODE in VARCHAR2,
9 X_PARENT_CLASSIFICATION_ID in NUMBER,
10 X_PARENT_CLASSIFICATION_CODE in VARCHAR2,
11 X_COUNTRY_CODE in VARCHAR2,
12 X_Compiled_Flag in VARCHAR2,
13 X_EFFECTIVE_FROM in DATE,
14 X_EFFECTIVE_TO in DATE,
15 X_REQUEST_ID in NUMBER,
16 X_Record_Type_Code in VARCHAR2,
17 X_ATTRIBUTE1 in VARCHAR2,
18 X_ATTRIBUTE2 in VARCHAR2,
19 X_ATTRIBUTE3 in VARCHAR2,
20 X_ATTRIBUTE4 in VARCHAR2,
21 X_ATTRIBUTE5 in VARCHAR2,
22 X_ATTRIBUTE6 in VARCHAR2,
23 X_ATTRIBUTE7 in VARCHAR2,
24 X_ATTRIBUTE8 in VARCHAR2,
25 X_ATTRIBUTE9 in VARCHAR2,
26 X_ATTRIBUTE10 in VARCHAR2,
27 X_ATTRIBUTE11 in VARCHAR2,
28 X_ATTRIBUTE12 in VARCHAR2,
29 X_ATTRIBUTE13 in VARCHAR2,
30 X_ATTRIBUTE14 in VARCHAR2,
31 X_ATTRIBUTE15 in VARCHAR2,
32 X_ATTRIBUTE_CATEGORY in VARCHAR2,
33 X_CLASSIFICATION_NAME in VARCHAR2,
34 X_CREATION_DATE in DATE,
35 X_CREATED_BY in NUMBER,
36 X_LAST_UPDATE_DATE in DATE,
37 X_LAST_UPDATED_BY in NUMBER,
38 X_LAST_UPDATE_LOGIN in NUMBER,
39 X_PROGRAM_APPLICATION_ID in NUMBER,
40 X_PROGRAM_ID in NUMBER,
41 X_Program_Login_Id in NUMBER,
42 X_OBJECT_VERSION_NUMBER in NUMBER
43 ) is
44 p_type_id ZX_FC_TYPES_VL.CLASSIFICATION_TYPE_ID%TYPE;
45 p_type_code ZX_FC_TYPES_VL.CLASSIFICATION_TYPE_CODE%TYPE;
46 p_type_name ZX_FC_TYPES_VL.CLASSIFICATION_TYPE_NAME%TYPE;
47 p_type_categ ZX_FC_TYPES_VL.Classification_Type_Categ_Code%TYPE;
48 p_delimiter ZX_FC_TYPES_VL.DELIMITER%TYPE;
49 p_concat_code Zx_Fc_Codes_Denorm_B.CONCAT_CLASSIF_CODE%TYPE;
50 p_concat_name Zx_Fc_Codes_Denorm_B.CONCAT_CLASSIF_NAME%TYPE;
51 p_code_level Zx_Fc_Codes_Denorm_B.CLASSIFICATION_CODE_LEVEL%TYPE;
52 p_parent_name Zx_Fc_Codes_Denorm_B.CLASSIFICATION_NAME%TYPE;
53 TYPE p_seg_t IS TABLE OF VARCHAR2(30) index BY BINARY_INTEGER;
54 TYPE p_seg_tn IS TABLE OF Zx_Fc_Codes_Denorm_B.CLASSIFICATION_NAME%TYPE index BY BINARY_INTEGER;
55 p_seg p_seg_t;
56 p_seg_name p_seg_tn;
57 p_tmp_seg p_seg_t;
58 p_tmp_seg_name p_seg_tn;
59 cursor C is select ROWID from ZX_FC_CODES_B
60 where CLASSIFICATION_ID = X_CLASSIFICATION_ID ;
61 cursor C_GET_TYPES_INFO is
62 SELECT
63 TYPE.CLASSIFICATION_TYPE_ID,
64 TYPE.CLASSIFICATION_TYPE_CODE,
65 TYPE.CLASSIFICATION_TYPE_NAME,
66 TYPE.Classification_Type_Categ_Code,
67 TYPE.DELIMITER
68 FROM ZX_FC_TYPES_VL TYPE
69 WHERE TYPE.CLASSIFICATION_TYPE_CODE = X_CLASSIFICATION_TYPE_CODE;
70 cursor C_GET_PARENT_DETAIL is
71 SELECT
72 CONCAT_CLASSIF_CODE,CONCAT_CLASSIF_NAME,CLASSIFICATION_CODE_LEVEL,CLASSIFICATION_NAME,
73 SEGMENT1,SEGMENT2,SEGMENT3,SEGMENT4,SEGMENT5,
74 SEGMENT6,SEGMENT7,SEGMENT8,SEGMENT9,SEGMENT10,
75 SEGMENT1_NAME,SEGMENT2_NAME,SEGMENT3_NAME,SEGMENT4_NAME,SEGMENT5_NAME,
76 SEGMENT6_NAME,SEGMENT7_NAME,SEGMENT8_NAME,SEGMENT9_NAME,SEGMENT10_NAME
77 FROM
78 ZX_FC_CODES_DENORM_B
79 WHERE
80 CLASSIFICATION_ID = X_PARENT_CLASSIFICATION_ID AND
81 LANGUAGE = userenv('LANG');
82 begin
83 insert into ZX_FC_CODES_B (
84 CLASSIFICATION_ID,
85 Classification_Code,
86 CLASSIFICATION_TYPE_CODE,
87 PARENT_CLASSIFICATION_ID,
88 PARENT_CLASSIFICATION_CODE,
89 COUNTRY_CODE,
90 Compiled_Flag,
91 EFFECTIVE_FROM,
92 EFFECTIVE_TO,
93 REQUEST_ID,
94 Record_Type_Code,
95 ATTRIBUTE1,
96 ATTRIBUTE2,
97 ATTRIBUTE3,
98 ATTRIBUTE4,
99 ATTRIBUTE5,
100 ATTRIBUTE6,
101 ATTRIBUTE7,
102 ATTRIBUTE8,
103 ATTRIBUTE9,
104 ATTRIBUTE10,
105 ATTRIBUTE11,
106 ATTRIBUTE12,
107 ATTRIBUTE13,
108 ATTRIBUTE14,
109 ATTRIBUTE15,
110 ATTRIBUTE_CATEGORY,
111 CREATION_DATE,
112 CREATED_BY,
113 LAST_UPDATE_DATE,
114 LAST_UPDATED_BY,
115 LAST_UPDATE_LOGIN,
116 PROGRAM_APPLICATION_ID,
117 PROGRAM_ID ,
118 Program_Login_Id,
119 OBJECT_VERSION_NUMBER
120 ) values (
121 X_CLASSIFICATION_ID,
122 X_Classification_Code,
123 X_CLASSIFICATION_TYPE_CODE,
124 X_PARENT_CLASSIFICATION_ID,
125 X_PARENT_CLASSIFICATION_CODE,
126 X_COUNTRY_CODE,
127 X_Compiled_Flag,
128 X_EFFECTIVE_FROM,
129 X_EFFECTIVE_TO,
130 X_REQUEST_ID,
131 X_Record_Type_Code,
132 X_ATTRIBUTE1,
133 X_ATTRIBUTE2,
134 X_ATTRIBUTE3,
135 X_ATTRIBUTE4,
136 X_ATTRIBUTE5,
137 X_ATTRIBUTE6,
138 X_ATTRIBUTE7,
139 X_ATTRIBUTE8,
140 X_ATTRIBUTE9,
141 X_ATTRIBUTE10,
142 X_ATTRIBUTE11,
143 X_ATTRIBUTE12,
144 X_ATTRIBUTE13,
145 X_ATTRIBUTE14,
146 X_ATTRIBUTE15,
147 X_ATTRIBUTE_CATEGORY,
148 X_CREATION_DATE,
149 X_CREATED_BY,
150 X_LAST_UPDATE_DATE,
151 X_LAST_UPDATED_BY,
152 X_LAST_UPDATE_LOGIN,
153 X_PROGRAM_APPLICATION_ID,
154 X_PROGRAM_ID,
155 X_Program_Login_Id,
156 X_OBJECT_VERSION_NUMBER
157 );
158 insert into ZX_FC_CODES_TL (
159 CLASSIFICATION_ID,
160 CLASSIFICATION_NAME,
161 CREATED_BY,
162 CREATION_DATE,
163 LAST_UPDATED_BY,
164 LAST_UPDATE_DATE,
165 LAST_UPDATE_LOGIN,
166 LANGUAGE,
167 SOURCE_LANG
168 ) select
169 X_CLASSIFICATION_ID,
170 X_CLASSIFICATION_NAME,
171 X_CREATED_BY,
172 X_CREATION_DATE,
173 X_LAST_UPDATED_BY,
174 X_LAST_UPDATE_DATE,
175 X_LAST_UPDATE_LOGIN,
176 L.LANGUAGE_CODE,
177 userenv('LANG')
178 from FND_LANGUAGES L
179 where L.INSTALLED_FLAG in ('I', 'B')
180 and not exists
181 (select NULL
182 from ZX_FC_CODES_TL T
183 where T.CLASSIFICATION_ID = X_CLASSIFICATION_ID
184 and T.LANGUAGE = L.LANGUAGE_CODE);
185 open c;
186 fetch c into X_ROWID;
187 if (c%notfound) then
188 close c;
189 raise no_data_found;
190 end if;
191 close c;
192 OPEN C_GET_TYPES_INFO;
193 fetch C_GET_TYPES_INFO into p_type_id,p_type_code,p_type_name,p_type_categ,p_delimiter;
194
195 FOR j IN 1..10
196 LOOP
197 p_seg(j) := '';
198 p_seg_name(j) := '';
199 END LOOP;
200
201 IF X_PARENT_CLASSIFICATION_ID is NULL THEN
202 p_code_level := 1;
203 p_concat_code := X_Classification_Code;
204 p_concat_name := X_CLASSIFICATION_NAME;
205 p_seg(1) := X_Classification_Code;
206 p_seg_name(1) := X_CLASSIFICATION_NAME;
207 else
208 FOR parentRec in C_GET_PARENT_DETAIL
209 LOOP
210 p_concat_code := parentRec.CONCAT_CLASSIF_CODE || p_delimiter || X_Classification_Code;
211 p_concat_name := parentRec.CONCAT_CLASSIF_NAME || p_delimiter || X_CLASSIFICATION_NAME;
212 p_code_level := parentRec.CLASSIFICATION_CODE_LEVEL + 1;
213 p_parent_name := parentRec.CLASSIFICATION_NAME;
214 p_tmp_seg(1) := parentRec.SEGMENT1;
215 p_tmp_seg(2) := parentRec.SEGMENT2;
216 p_tmp_seg(3) := parentRec.SEGMENT3;
217 p_tmp_seg(4) := parentRec.SEGMENT4;
218 p_tmp_seg(5) := parentRec.SEGMENT5;
219 p_tmp_seg(6) := parentRec.SEGMENT6;
220 p_tmp_seg(7) := parentRec.SEGMENT7;
221 p_tmp_seg(8) := parentRec.SEGMENT8;
222 p_tmp_seg(9) := parentRec.SEGMENT9;
223 p_tmp_seg(10) := parentRec.SEGMENT10;
224 p_tmp_seg_name(1) := parentRec.SEGMENT1_NAME;
225 p_tmp_seg_name(2) := parentRec.SEGMENT2_NAME;
226 p_tmp_seg_name(3) := parentRec.SEGMENT3_NAME;
227 p_tmp_seg_name(4) := parentRec.SEGMENT4_NAME;
228 p_tmp_seg_name(5) := parentRec.SEGMENT5_NAME;
229 p_tmp_seg_name(6) := parentRec.SEGMENT6_NAME;
230 p_tmp_seg_name(7) := parentRec.SEGMENT7_NAME;
231 p_tmp_seg_name(8) := parentRec.SEGMENT8_NAME;
232 p_tmp_seg_name(9) := parentRec.SEGMENT9_NAME;
233 p_tmp_seg_name(10) := parentRec.SEGMENT10_NAME;
234 END LOOP;
235 FOR i IN 1..10
236 LOOP
237 IF p_tmp_seg(i) IS NULL THEN
238 p_seg(i) := X_Classification_Code;
239 p_seg_name(i) := X_CLASSIFICATION_NAME;
240 EXIT;
241 ELSE
242 p_seg(i) := p_tmp_seg(i);
243 p_seg_name(i) := p_tmp_seg_name(i);
244 END IF;
245 END LOOP;
246 END IF;
247 CLOSE C_GET_TYPES_INFO;
248
249 INSERT INTO Zx_Fc_Codes_Denorm_B(
250 CLASSIFICATION_TYPE_ID,
251 CLASSIFICATION_TYPE_CODE,
252 CLASSIFICATION_TYPE_NAME,
253 Classification_Type_Categ_Code,
254 CLASSIFICATION_ID,
255 Classification_Code,
256 CLASSIFICATION_NAME,
257 LANGUAGE,
258 EFFECTIVE_FROM,
259 EFFECTIVE_TO,
260 Enabled_Flag,
261 ANCESTOR_ID,
262 ANCESTOR_CODE,
263 ANCESTOR_NAME,
264 CONCAT_CLASSIF_CODE,
265 CONCAT_CLASSIF_NAME,
266 CLASSIFICATION_CODE_LEVEL,
267 COUNTRY_CODE,
268 SEGMENT1,
269 SEGMENT2,
270 SEGMENT3,
271 SEGMENT4,
272 SEGMENT5,
273 SEGMENT6,
274 SEGMENT7,
275 SEGMENT8,
276 SEGMENT9,
277 SEGMENT10,
278 SEGMENT1_NAME,
279 SEGMENT2_NAME,
280 SEGMENT3_NAME,
281 SEGMENT4_NAME,
282 SEGMENT5_NAME,
283 SEGMENT6_NAME,
284 SEGMENT7_NAME,
285 SEGMENT8_NAME,
286 SEGMENT9_NAME,
287 SEGMENT10_NAME,
288 CREATED_BY,
289 CREATION_DATE,
290 LAST_UPDATED_BY,
291 LAST_UPDATE_LOGIN,
292 LAST_UPDATE_DATE,
293 REQUEST_ID,
294 PROGRAM_ID,
295 PROGRAM_APPLICATION_ID,
296 Program_Login_Id,
297 Record_Type_Code)
298 SELECT
299 p_type_id,
300 p_type_code,
301 p_type_name,
302 p_type_categ,
303 X_CLASSIFICATION_ID,
304 X_Classification_Code,
305 X_CLASSIFICATION_NAME,
306 L.LANGUAGE_CODE,
307 X_EFFECTIVE_FROM,
308 X_EFFECTIVE_TO,
309 'Y',
310 X_PARENT_CLASSIFICATION_ID,
311 X_PARENT_CLASSIFICATION_CODE,
312 p_parent_name,
313 p_concat_code,
314 p_concat_name,
315 p_code_level,
316 X_COUNTRY_CODE,
317 p_seg(1),
318 p_seg(2),
319 p_seg(3),
320 p_seg(4),
321 p_seg(5),
322 p_seg(6),
323 p_seg(7),
324 p_seg(8),
325 p_seg(9),
326 p_seg(10),
327 p_seg_name(1),
328 p_seg_name(2),
329 p_seg_name(3),
330 p_seg_name(4),
331 p_seg_name(5),
332 p_seg_name(6),
333 p_seg_name(7),
334 p_seg_name(8),
335 p_seg_name(9),
336 p_seg_name(10),
337 X_CREATED_BY,
338 X_CREATION_DATE,
339 X_LAST_UPDATED_BY,
340 X_LAST_UPDATE_LOGIN,
341 X_LAST_UPDATE_DATE,
342 X_REQUEST_ID,
343 X_PROGRAM_ID,
344 X_PROGRAM_APPLICATION_ID,
345 X_Program_Login_Id,
346 X_Record_Type_Code
347 from FND_LANGUAGES L
348 where L.INSTALLED_FLAG in ('I', 'B')
349 and not exists
350 (select NULL
351 from Zx_Fc_Codes_Denorm_B Denorm
352 where Denorm.CLASSIFICATION_ID = X_CLASSIFICATION_ID
353 and Denorm.LANGUAGE = L.LANGUAGE_CODE);
354
355 end INSERT_ROW;
356
357 procedure LOCK_ROW (
358 X_CLASSIFICATION_ID in NUMBER,
359 X_Classification_Code in VARCHAR2,
360 X_CLASSIFICATION_TYPE_CODE in VARCHAR2,
361 X_PARENT_CLASSIFICATION_ID in NUMBER,
362 X_PARENT_CLASSIFICATION_CODE in VARCHAR2,
363 X_COUNTRY_CODE in VARCHAR2,
364 X_Compiled_Flag in VARCHAR2,
365 X_EFFECTIVE_FROM in DATE,
366 X_EFFECTIVE_TO in DATE,
367 X_REQUEST_ID in NUMBER,
368 X_Record_Type_Code in VARCHAR2,
369 X_ATTRIBUTE1 in VARCHAR2,
370 X_ATTRIBUTE2 in VARCHAR2,
371 X_ATTRIBUTE3 in VARCHAR2,
372 X_ATTRIBUTE4 in VARCHAR2,
373 X_ATTRIBUTE5 in VARCHAR2,
374 X_ATTRIBUTE6 in VARCHAR2,
375 X_ATTRIBUTE7 in VARCHAR2,
376 X_ATTRIBUTE8 in VARCHAR2,
377 X_ATTRIBUTE9 in VARCHAR2,
378 X_ATTRIBUTE10 in VARCHAR2,
379 X_ATTRIBUTE11 in VARCHAR2,
380 X_ATTRIBUTE12 in VARCHAR2,
381 X_ATTRIBUTE13 in VARCHAR2,
382 X_ATTRIBUTE14 in VARCHAR2,
383 X_ATTRIBUTE15 in VARCHAR2,
384 X_ATTRIBUTE_CATEGORY in VARCHAR2,
385 X_CLASSIFICATION_NAME in VARCHAR2,
386 X_PROGRAM_APPLICATION_ID in NUMBER,
387 X_PROGRAM_ID in NUMBER,
388 X_Program_Login_Id in NUMBER,
389 X_OBJECT_VERSION_NUMBER in NUMBER
390 ) is
391 cursor c is select
392 Classification_Code,
393 CLASSIFICATION_TYPE_CODE,
394 PARENT_CLASSIFICATION_ID,
395 PARENT_CLASSIFICATION_CODE,
396 COUNTRY_CODE,
397 Compiled_Flag,
398 EFFECTIVE_FROM,
399 EFFECTIVE_TO,
400 REQUEST_ID,
401 Record_Type_Code,
402 ATTRIBUTE1,
403 ATTRIBUTE2,
404 ATTRIBUTE3,
405 ATTRIBUTE4,
406 ATTRIBUTE5,
407 ATTRIBUTE6,
408 ATTRIBUTE7,
409 ATTRIBUTE8,
410 ATTRIBUTE9,
411 ATTRIBUTE10,
412 ATTRIBUTE11,
413 ATTRIBUTE12,
414 ATTRIBUTE13,
415 ATTRIBUTE14,
416 ATTRIBUTE15,
417 ATTRIBUTE_CATEGORY,
418 PROGRAM_APPLICATION_ID,
419 PROGRAM_ID,
420 Program_Login_Id ,
421 OBJECT_VERSION_NUMBER
422 from ZX_FC_CODES_B
423 where CLASSIFICATION_ID = X_CLASSIFICATION_ID
424 for update of CLASSIFICATION_ID nowait;
425 recinfo c%rowtype;
426 cursor c1 is select
427 CLASSIFICATION_NAME,
428 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
429 from ZX_FC_CODES_TL
430 where CLASSIFICATION_ID = X_CLASSIFICATION_ID
431 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
432 for update of CLASSIFICATION_ID nowait;
433 begin
434 open c;
435 fetch c into recinfo;
436 if (c%notfound) then
437 close c;
438 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
439 app_exception.raise_exception;
440 end if;
441 close c;
442 if ( (recinfo.Classification_Code = X_Classification_Code)
443 AND (recinfo.CLASSIFICATION_TYPE_CODE = X_CLASSIFICATION_TYPE_CODE)
444 AND ((recinfo.PARENT_CLASSIFICATION_ID = X_PARENT_CLASSIFICATION_ID)
445 OR ((recinfo.PARENT_CLASSIFICATION_ID is null) AND (X_PARENT_CLASSIFICATION_ID is null)))
446 AND ((recinfo.PARENT_CLASSIFICATION_CODE = X_PARENT_CLASSIFICATION_CODE)
447 OR ((recinfo.PARENT_CLASSIFICATION_CODE is null) AND (X_PARENT_CLASSIFICATION_CODE is null)))
448 AND ((recinfo.COUNTRY_CODE = X_COUNTRY_CODE)
449 OR ((recinfo.COUNTRY_CODE is null) AND (X_COUNTRY_CODE is null)))
450 AND ((recinfo.Compiled_Flag = X_Compiled_Flag)
451 OR ((recinfo.Compiled_Flag is null) AND (X_Compiled_Flag is null)))
452 AND ((recinfo.EFFECTIVE_FROM = X_EFFECTIVE_FROM)
453 OR ((recinfo.EFFECTIVE_FROM is null) AND (X_EFFECTIVE_FROM is null)))
454 AND ((recinfo.EFFECTIVE_TO = X_EFFECTIVE_TO)
455 OR ((recinfo.EFFECTIVE_TO is null) AND (X_EFFECTIVE_TO is null)))
456 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
457 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
458 AND ((recinfo.Record_Type_Code = X_Record_Type_Code)
459 OR ((recinfo.Record_Type_Code is null) AND (X_Record_Type_Code is null)))
460 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
464 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
461 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
462 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
463 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
465 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
466 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
467 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
468 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
469 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
470 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
471 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
472 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
473 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
474 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
475 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
476 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
477 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
478 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
479 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
480 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
481 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
482 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
483 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
484 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
485 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
486 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
487 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
488 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
489 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
490 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
491 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
492 AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
493 OR ((recinfo.PROGRAM_APPLICATION_ID is null) AND (X_PROGRAM_APPLICATION_ID is null)))
494 AND ((recinfo.PROGRAM_ID = X_PROGRAM_ID)
495 OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null)))
496 AND ((recinfo.Program_Login_Id = X_Program_Login_Id)
497 OR ((recinfo.Program_Login_Id is null) AND (X_Program_Login_Id is null)))
498 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
499 ) then
500 null;
501 else
502 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
503 app_exception.raise_exception;
504 end if;
505 for tlinfo in c1 loop
506 if (tlinfo.BASELANG = 'Y') then
507 if ( (tlinfo.CLASSIFICATION_NAME = X_CLASSIFICATION_NAME)
508 ) then
509 null;
510 else
511 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
512 app_exception.raise_exception;
513 end if;
514 end if;
515 end loop;
516 return;
517 end LOCK_ROW;
518
519 procedure UPDATE_ROW (
520 X_CLASSIFICATION_ID in NUMBER,
521 X_Classification_Code in VARCHAR2,
522 X_CLASSIFICATION_TYPE_CODE in VARCHAR2,
523 X_PARENT_CLASSIFICATION_ID in NUMBER,
524 X_PARENT_CLASSIFICATION_CODE in VARCHAR2,
525 X_COUNTRY_CODE in VARCHAR2,
526 X_Compiled_Flag in VARCHAR2,
527 X_EFFECTIVE_FROM in DATE,
528 X_EFFECTIVE_TO in DATE,
529 X_REQUEST_ID in NUMBER,
530 X_Record_Type_Code in VARCHAR2,
531 X_ATTRIBUTE1 in VARCHAR2,
532 X_ATTRIBUTE2 in VARCHAR2,
533 X_ATTRIBUTE3 in VARCHAR2,
534 X_ATTRIBUTE4 in VARCHAR2,
535 X_ATTRIBUTE5 in VARCHAR2,
536 X_ATTRIBUTE6 in VARCHAR2,
537 X_ATTRIBUTE7 in VARCHAR2,
538 X_ATTRIBUTE8 in VARCHAR2,
539 X_ATTRIBUTE9 in VARCHAR2,
540 X_ATTRIBUTE10 in VARCHAR2,
541 X_ATTRIBUTE11 in VARCHAR2,
542 X_ATTRIBUTE12 in VARCHAR2,
543 X_ATTRIBUTE13 in VARCHAR2,
544 X_ATTRIBUTE14 in VARCHAR2,
545 X_ATTRIBUTE15 in VARCHAR2,
546 X_ATTRIBUTE_CATEGORY in VARCHAR2,
547 X_CLASSIFICATION_NAME in VARCHAR2,
548 X_LAST_UPDATE_DATE in DATE,
549 X_LAST_UPDATED_BY in NUMBER,
550 X_LAST_UPDATE_LOGIN in NUMBER,
551 X_PROGRAM_APPLICATION_ID in NUMBER,
552 X_PROGRAM_ID in NUMBER,
553 X_Program_Login_Id in NUMBER,
554 X_OBJECT_VERSION_NUMBER in NUMBER
555 ) is
556 p_delimiter ZX_FC_TYPES_VL.DELIMITER%TYPE;
557 p_concat_name Zx_Fc_Codes_Denorm_B.CONCAT_CLASSIF_NAME%TYPE;
558 begin
559 update ZX_FC_CODES_B set
560 Classification_Code = X_Classification_Code,
561 CLASSIFICATION_TYPE_CODE = X_CLASSIFICATION_TYPE_CODE,
562 PARENT_CLASSIFICATION_ID = X_PARENT_CLASSIFICATION_ID,
563 PARENT_CLASSIFICATION_CODE = X_PARENT_CLASSIFICATION_CODE,
564 COUNTRY_CODE = X_COUNTRY_CODE,
565 Compiled_Flag = X_Compiled_Flag,
566 EFFECTIVE_FROM = X_EFFECTIVE_FROM,
567 EFFECTIVE_TO = X_EFFECTIVE_TO,
568 REQUEST_ID = X_REQUEST_ID,
569 Record_Type_Code = X_Record_Type_Code,
570 ATTRIBUTE1 = X_ATTRIBUTE1,
571 ATTRIBUTE2 = X_ATTRIBUTE2,
572 ATTRIBUTE3 = X_ATTRIBUTE3,
573 ATTRIBUTE4 = X_ATTRIBUTE4,
574 ATTRIBUTE5 = X_ATTRIBUTE5,
575 ATTRIBUTE6 = X_ATTRIBUTE6,
576 ATTRIBUTE7 = X_ATTRIBUTE7,
580 ATTRIBUTE11 = X_ATTRIBUTE11,
577 ATTRIBUTE8 = X_ATTRIBUTE8,
578 ATTRIBUTE9 = X_ATTRIBUTE9,
579 ATTRIBUTE10 = X_ATTRIBUTE10,
581 ATTRIBUTE12 = X_ATTRIBUTE12,
582 ATTRIBUTE13 = X_ATTRIBUTE13,
583 ATTRIBUTE14 = X_ATTRIBUTE14,
584 ATTRIBUTE15 = X_ATTRIBUTE15,
585 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
586 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
587 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
588 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
589 PROGRAM_APPLICATION_ID=X_PROGRAM_APPLICATION_ID ,
590 PROGRAM_ID=X_PROGRAM_ID ,
591 Program_Login_Id=X_Program_Login_Id,
592 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
593 where CLASSIFICATION_ID = X_CLASSIFICATION_ID;
594 if (sql%notfound) then
595 raise no_data_found;
596 end if;
597 update ZX_FC_CODES_TL set
598 CLASSIFICATION_NAME = X_CLASSIFICATION_NAME,
599 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
600 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
601 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
602 SOURCE_LANG = userenv('LANG')
603 where CLASSIFICATION_ID = X_CLASSIFICATION_ID
604 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
605 if (sql%notfound) then
606 raise no_data_found;
607 end if;
608 p_delimiter := '';
609 p_concat_name := '';
610 IF X_PARENT_CLASSIFICATION_ID IS NOT NULL THEN
611 select DELIMITER into p_delimiter FROM ZX_FC_TYPES_B WHERE
612 CLASSIFICATION_TYPE_CODE = X_CLASSIFICATION_TYPE_CODE;
613 select CONCAT_CLASSIF_NAME into p_concat_name from
614 Zx_Fc_Codes_Denorm_B CodeDenorm where CLASSIFICATION_ID = X_PARENT_CLASSIFICATION_ID
615 and userenv('LANG') in (LANGUAGE);
616 END IF;
617 -- start bug#7600239
618 update Zx_Fc_Codes_Denorm_B set
619 COUNTRY_CODE = X_COUNTRY_CODE,
620 EFFECTIVE_TO = X_EFFECTIVE_TO,
621 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
622 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
623 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
624 PROGRAM_APPLICATION_ID=X_PROGRAM_APPLICATION_ID ,
625 PROGRAM_ID=X_PROGRAM_ID ,
626 Program_Login_Id=X_Program_Login_Id
627 where CLASSIFICATION_ID = X_CLASSIFICATION_ID;
628
629 update Zx_Fc_Codes_Denorm_B set
630 CLASSIFICATION_NAME = X_CLASSIFICATION_NAME,
631 CONCAT_CLASSIF_NAME = p_concat_name || p_delimiter || X_CLASSIFICATION_NAME,
632 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
633 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
634 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
635 PROGRAM_APPLICATION_ID=X_PROGRAM_APPLICATION_ID ,
636 PROGRAM_ID=X_PROGRAM_ID ,
637 Program_Login_Id=X_Program_Login_Id
638 where CLASSIFICATION_ID = X_CLASSIFICATION_ID
639 and userenv('LANG') in (LANGUAGE);
640 -- end bug#7600239
641 end UPDATE_ROW;
642
643 procedure DELETE_ROW (
644 X_CLASSIFICATION_ID in NUMBER
645 ) is
646 begin
647 delete from ZX_FC_CODES_TL
648 where CLASSIFICATION_ID = X_CLASSIFICATION_ID;
649 if (sql%notfound) then
650 raise no_data_found;
651 end if;
652 delete from ZX_FC_CODES_B
653 where CLASSIFICATION_ID = X_CLASSIFICATION_ID;
654 if (sql%notfound) then
655 raise no_data_found;
656 end if;
657 end DELETE_ROW;
658
659 procedure ADD_LANGUAGE
660 is
661 begin
662 delete from ZX_FC_CODES_TL T
663 where not exists
664 (select NULL
665 from ZX_FC_CODES_B B
666 where B.CLASSIFICATION_ID = T.CLASSIFICATION_ID
667 );
668 update ZX_FC_CODES_TL T set (
669 CLASSIFICATION_NAME
670 ) = (select
671 B.CLASSIFICATION_NAME
672 from ZX_FC_CODES_TL B
673 where B.CLASSIFICATION_ID = T.CLASSIFICATION_ID
674 and B.LANGUAGE = T.SOURCE_LANG)
675 where (
676 T.CLASSIFICATION_ID,
677 T.LANGUAGE
678 ) in (select
679 SUBT.CLASSIFICATION_ID,
680 SUBT.LANGUAGE
681 from ZX_FC_CODES_TL SUBB, ZX_FC_CODES_TL SUBT
682 where SUBB.CLASSIFICATION_ID = SUBT.CLASSIFICATION_ID
683 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
684 and (SUBB.CLASSIFICATION_NAME <> SUBT.CLASSIFICATION_NAME
685 ));
686 insert into ZX_FC_CODES_TL (
687 CLASSIFICATION_ID,
688 CLASSIFICATION_NAME,
689 CREATED_BY,
690 CREATION_DATE,
691 LAST_UPDATED_BY,
692 LAST_UPDATE_DATE,
693 LAST_UPDATE_LOGIN,
694 LANGUAGE,
695 SOURCE_LANG
696 ) select
697 B.CLASSIFICATION_ID,
698 B.CLASSIFICATION_NAME,
699 B.CREATED_BY,
700 B.CREATION_DATE,
701 B.LAST_UPDATED_BY,
702 B.LAST_UPDATE_DATE,
703 B.LAST_UPDATE_LOGIN,
704 L.LANGUAGE_CODE,
705 B.SOURCE_LANG
706 from ZX_FC_CODES_TL B, FND_LANGUAGES L
707 where L.INSTALLED_FLAG in ('I', 'B')
708 and B.LANGUAGE = userenv('LANG')
709 and not exists
710 (select NULL
711 from ZX_FC_CODES_TL T
712 where T.CLASSIFICATION_ID = B.CLASSIFICATION_ID
713 and T.LANGUAGE = L.LANGUAGE_CODE);
714
715 /* Logic to delete/update/insert into zx_fc_codes_denorm_b table */
716
717 delete from ZX_FC_CODES_DENORM_B T
718 where not exists
719 (select NULL
720 from ZX_FC_CODES_B B
721 where B.CLASSIFICATION_ID = T.CLASSIFICATION_ID
722 );
723
724 /* commented out the following code, as we don't have
725 SOURCE_LANG column in the ZX_FC_CODES_DENORM_B table */
726 /*
727 update ZX_FC_CODES_DENORM_B T set (
728 CLASSIFICATION_NAME
729 ) = (select
730 B.CLASSIFICATION_NAME
731 from ZX_FC_CODES_DENORM_B B
732 where B.CLASSIFICATION_ID = T.CLASSIFICATION_ID
733 and B.LANGUAGE = T.SOURCE_LANG)
734 where (
735 T.CLASSIFICATION_ID,
736 T.LANGUAGE
737 ) in (select
738 SUBT.CLASSIFICATION_ID,
739 SUBT.LANGUAGE
740 from ZX_FC_CODES_DENORM_B SUBB, ZX_FC_CODES_DENORM_B SUBT
741 where SUBB.CLASSIFICATION_ID = SUBT.CLASSIFICATION_ID
742 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
743 and (SUBB.CLASSIFICATION_NAME <> SUBT.CLASSIFICATION_NAME
744 ));
745 */
746
747 insert into ZX_FC_CODES_DENORM_B (
748 CLASSIFICATION_ID,
749 CLASSIFICATION_CODE,
750 CLASSIFICATION_NAME,
751 CLASSIFICATION_TYPE_ID,
752 CLASSIFICATION_TYPE_CODE,
753 CLASSIFICATION_TYPE_NAME,
754 CLASSIFICATION_TYPE_CATEG_CODE,
755 EFFECTIVE_FROM,
756 EFFECTIVE_TO,
757 ENABLED_FLAG,
758 ANCESTOR_ID,
759 ANCESTOR_CODE,
760 ANCESTOR_NAME,
761 CONCAT_CLASSIF_CODE,
762 CONCAT_CLASSIF_NAME,
763 CLASSIFICATION_CODE_LEVEL,
764 COUNTRY_CODE,
765 SEGMENT1,
766 SEGMENT2,
767 SEGMENT3,
768 SEGMENT4,
769 SEGMENT5,
770 SEGMENT6,
771 SEGMENT7,
772 SEGMENT8,
773 SEGMENT9,
774 SEGMENT10,
775 SEGMENT1_NAME,
776 SEGMENT2_NAME,
777 SEGMENT3_NAME,
778 SEGMENT4_NAME,
779 SEGMENT5_NAME,
780 SEGMENT6_NAME,
781 SEGMENT7_NAME,
782 SEGMENT8_NAME,
783 SEGMENT9_NAME,
784 SEGMENT10_NAME,
785 REQUEST_ID,
786 PROGRAM_ID,
787 PROGRAM_APPLICATION_ID,
788 PROGRAM_LOGIN_ID,
789 RECORD_TYPE_CODE,
790 CREATED_BY,
791 CREATION_DATE,
792 LAST_UPDATED_BY,
793 LAST_UPDATE_DATE,
794 LAST_UPDATE_LOGIN,
795 LANGUAGE
796 ) select
797 B.CLASSIFICATION_ID,
798 B.CLASSIFICATION_CODE,
799 B.CLASSIFICATION_NAME,
800 B.CLASSIFICATION_TYPE_ID,
801 B.CLASSIFICATION_TYPE_CODE,
802 B.CLASSIFICATION_TYPE_NAME,
803 B.CLASSIFICATION_TYPE_CATEG_CODE,
804 B.EFFECTIVE_FROM,
805 B.EFFECTIVE_TO,
806 B.ENABLED_FLAG,
807 B.ANCESTOR_ID,
808 B.ANCESTOR_CODE,
809 B.ANCESTOR_NAME,
810 B.CONCAT_CLASSIF_CODE,
811 B.CONCAT_CLASSIF_NAME,
812 B.CLASSIFICATION_CODE_LEVEL,
813 B.COUNTRY_CODE,
814 B.SEGMENT1,
815 B.SEGMENT2,
816 B.SEGMENT3,
817 B.SEGMENT4,
818 B.SEGMENT5,
819 B.SEGMENT6,
820 B.SEGMENT7,
821 B.SEGMENT8,
822 B.SEGMENT9,
823 B.SEGMENT10,
824 B.SEGMENT1_NAME,
825 B.SEGMENT2_NAME,
826 B.SEGMENT3_NAME,
827 B.SEGMENT4_NAME,
828 B.SEGMENT5_NAME,
829 B.SEGMENT6_NAME,
830 B.SEGMENT7_NAME,
831 B.SEGMENT8_NAME,
832 B.SEGMENT9_NAME,
833 B.SEGMENT10_NAME,
834 B.REQUEST_ID,
835 B.PROGRAM_ID,
836 B.PROGRAM_APPLICATION_ID,
837 B.PROGRAM_LOGIN_ID,
838 B.RECORD_TYPE_CODE,
839 B.CREATED_BY,
840 B.CREATION_DATE,
841 B.LAST_UPDATED_BY,
842 B.LAST_UPDATE_DATE,
843 B.LAST_UPDATE_LOGIN,
844 L.LANGUAGE_CODE
845 from ZX_FC_CODES_DENORM_B B, FND_LANGUAGES L
846 where L.INSTALLED_FLAG in ('I', 'B')
847 and B.LANGUAGE = userenv('LANG')
848 and not exists
849 (select NULL
850 from ZX_FC_CODES_DENORM_B T
851 where T.CLASSIFICATION_ID = B.CLASSIFICATION_ID
852 and T.LANGUAGE = L.LANGUAGE_CODE);
853
854 end ADD_LANGUAGE;
855
856 end ZX_FC_CODES_PKG;