eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PA_CI_TYPES_PKG

Source


1 package body PA_CI_TYPES_PKG as
2 /* $Header: PACITYTB.pls 120.5 2009/05/04 18:24:07 cklee ship $ */
3 
4 procedure INSERT_ROW (
5   X_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6   X_CI_TYPE_ID in NUMBER,
7   X_CI_TYPE_CLASS_CODE in VARCHAR2,
8   X_AUTO_NUMBER_FLAG in VARCHAR2,
9   X_RESOLUTION_REQUIRED_FLAG in VARCHAR2,
10   X_APPROVAL_REQUIRED_FLAG in VARCHAR2,
11   X_SOURCE_ATTRS_ENABLED_FLAG in VARCHAR2,
12   X_ALLOW_ALL_USAGE_FLAG in VARCHAR2,
13   X_RECORD_VERSION_NUMBER in NUMBER,
14   X_START_DATE_ACTIVE in DATE,
15   X_END_DATE_ACTIVE in DATE,
16   X_CLASSIFICATION_CATEGORY in VARCHAR2,
17   X_REASON_CATEGORY in VARCHAR2,
18   X_RESOLUTION_CATEGORY in VARCHAR2,
19   X_ATTRIBUTE_CATEGORY in VARCHAR2,
20   X_ATTRIBUTE1 in VARCHAR2,
21   X_ATTRIBUTE2 in VARCHAR2,
22   X_ATTRIBUTE3 in VARCHAR2,
23   X_ATTRIBUTE4 in VARCHAR2,
24   X_ATTRIBUTE5 in VARCHAR2,
25   X_ATTRIBUTE6 in VARCHAR2,
26   X_ATTRIBUTE7 in VARCHAR2,
27   X_ATTRIBUTE8 in VARCHAR2,
28   X_ATTRIBUTE9 in VARCHAR2,
29   X_ATTRIBUTE10 in VARCHAR2,
30   X_ATTRIBUTE11 in VARCHAR2,
31   X_ATTRIBUTE12 in VARCHAR2,
32   X_ATTRIBUTE13 in VARCHAR2,
33   X_ATTRIBUTE14 in VARCHAR2,
34   X_ATTRIBUTE15 in VARCHAR2,
35   X_NAME in VARCHAR2,
36   X_SHORT_NAME in VARCHAR2,
37   X_DESCRIPTION in VARCHAR2,
38   X_CREATION_DATE in DATE,
39   X_CREATED_BY in NUMBER,
40   X_LAST_UPDATE_DATE in DATE,
41   X_LAST_UPDATED_BY in NUMBER,
42   X_LAST_UPDATE_LOGIN in NUMBER,
43 --|start:   16-FEB-2009  cklee  R12.1.2 setup ehancement
44   X_APPROVAL_TYPE_CODE            IN VARCHAR2 DEFAULT 'STANDARD',
45   X_SUBCONTRACTOR_REPORTING_FLAG  IN VARCHAR2 DEFAULT 'N',
46   X_PREFIX_AUTO_NUMBER            IN VARCHAR2 DEFAULT NULL,
47 --|end:   16-FEB-2009  cklee  R12.1.2 setup ehancement
48 --|start   29-APR-2009  cklee  R12.1.2 setup ehancement v2
49   X_IMPACT_BUDGET_TYPE_CODE       IN VARCHAR2 DEFAULT 'NA',
50   X_COST_COL_FLAG                 IN VARCHAR2 DEFAULT 'N',
51   X_REV_COL_FLAG                  IN VARCHAR2 DEFAULT 'N',
52   X_DIR_COST_REG_FLAG             IN VARCHAR2 DEFAULT 'N',
53   X_SUPP_COST_REG_FLAG            IN VARCHAR2 DEFAULT 'N',
54   X_DIR_REG_REV_COL_FLAG          IN VARCHAR2 DEFAULT 'N'
55 --|end   29-APR-2009  cklee  R12.1.2 setup ehancement v2
56 
57 ) is
58   cursor C is select ROWID from PA_CI_TYPES_B
59     where CI_TYPE_ID = X_CI_TYPE_ID
60     ;
61   l_rowid  rowid;  --Added for bug#4565156
62 begin
63 
64   l_rowid := x_rowid; --Added for bug#4565156
65   insert into PA_CI_TYPES_B (
66     CI_TYPE_ID,
67     CI_TYPE_CLASS_CODE,
68     AUTO_NUMBER_FLAG,
69     RESOLUTION_REQUIRED_FLAG,
70     APPROVAL_REQUIRED_FLAG,
71     SOURCE_ATTRS_ENABLED_FLAG,
72     ALLOW_ALL_USAGE_FLAG,
73     RECORD_VERSION_NUMBER,
74     START_DATE_ACTIVE,
75     END_DATE_ACTIVE,
76     CLASSIFICATION_CATEGORY,
77     REASON_CATEGORY,
78     RESOLUTION_CATEGORY,
79     ATTRIBUTE_CATEGORY,
80     ATTRIBUTE1,
81     ATTRIBUTE2,
82     ATTRIBUTE3,
83     ATTRIBUTE4,
84     ATTRIBUTE5,
85     ATTRIBUTE6,
86     ATTRIBUTE7,
87     ATTRIBUTE8,
88     ATTRIBUTE9,
89     ATTRIBUTE10,
90     ATTRIBUTE11,
91     ATTRIBUTE12,
92     ATTRIBUTE13,
93     ATTRIBUTE14,
94     ATTRIBUTE15,
95     CREATION_DATE,
96     CREATED_BY,
97     LAST_UPDATE_DATE,
98     LAST_UPDATED_BY,
99     LAST_UPDATE_LOGIN,
100   --|start:   16-FEB-2009  cklee  R12.1.2 setup ehancement
101     APPROVAL_TYPE_CODE,
102     SUBCONTRACTOR_REPORTING_FLAG,
103     PREFIX_AUTO_NUMBER,
104 --|end:   16-FEB-2009  cklee  R12.1.2 setup ehancement
105 IMPACT_BUDGET_TYPE_CODE,
106 COST_COL_FLAG,
107 REV_COL_FLAG,
108 DIR_COST_REG_FLAG,
109 SUPP_COST_REG_FLAG,
110 DIR_REG_REV_COL_FLAG
111 
112 ) values (
113     X_CI_TYPE_ID,
114     X_CI_TYPE_CLASS_CODE,
115     X_AUTO_NUMBER_FLAG,
116     X_RESOLUTION_REQUIRED_FLAG,
117     X_APPROVAL_REQUIRED_FLAG,
118     X_SOURCE_ATTRS_ENABLED_FLAG,
119     X_ALLOW_ALL_USAGE_FLAG,
120     X_RECORD_VERSION_NUMBER,
121     X_START_DATE_ACTIVE,
122     X_END_DATE_ACTIVE,
123     X_CLASSIFICATION_CATEGORY,
124     X_REASON_CATEGORY,
125     X_RESOLUTION_CATEGORY,
126     X_ATTRIBUTE_CATEGORY,
127     X_ATTRIBUTE1,
128     X_ATTRIBUTE2,
129     X_ATTRIBUTE3,
130     X_ATTRIBUTE4,
131     X_ATTRIBUTE5,
132     X_ATTRIBUTE6,
133     X_ATTRIBUTE7,
134     X_ATTRIBUTE8,
135     X_ATTRIBUTE9,
136     X_ATTRIBUTE10,
137     X_ATTRIBUTE11,
138     X_ATTRIBUTE12,
139     X_ATTRIBUTE13,
140     X_ATTRIBUTE14,
141     X_ATTRIBUTE15,
142     X_CREATION_DATE,
143     X_CREATED_BY,
144     X_LAST_UPDATE_DATE,
145     X_LAST_UPDATED_BY,
146     X_LAST_UPDATE_LOGIN,
147   --|start:   16-FEB-2009  cklee  R12.1.2 setup ehancement
148     X_APPROVAL_TYPE_CODE,
149     X_SUBCONTRACTOR_REPORTING_FLAG,
150     X_PREFIX_AUTO_NUMBER,
151 --|end:   16-FEB-2009  cklee  R12.1.2 setup ehancement
152     X_IMPACT_BUDGET_TYPE_CODE,
153     X_COST_COL_FLAG,
154     X_REV_COL_FLAG,
155     X_DIR_COST_REG_FLAG,
156     X_SUPP_COST_REG_FLAG,
157     X_DIR_REG_REV_COL_FLAG
158 );
159 
160   insert into PA_CI_TYPES_TL (
161     CI_TYPE_ID,
162     NAME,
163     SHORT_NAME,
164     LAST_UPDATED_BY,
165     CREATED_BY,
166     CREATION_DATE,
167     LAST_UPDATE_DATE,
168     LAST_UPDATE_LOGIN,
169     DESCRIPTION,
170     LANGUAGE,
171     SOURCE_LANG
172   ) select
173     X_CI_TYPE_ID,
174     X_NAME,
175     X_SHORT_NAME,
176     X_LAST_UPDATED_BY,
177     X_CREATED_BY,
178     X_CREATION_DATE,
179     X_LAST_UPDATE_DATE,
180     X_LAST_UPDATE_LOGIN,
181     X_DESCRIPTION,
182     L.LANGUAGE_CODE,
183     userenv('LANG')
184   from FND_LANGUAGES L
185   where L.INSTALLED_FLAG in ('I', 'B')
186   and not exists
187     (select NULL
188     from PA_CI_TYPES_TL T
189     where T.CI_TYPE_ID = X_CI_TYPE_ID
190     and T.LANGUAGE = L.LANGUAGE_CODE);
191 
192   open c;
193   fetch c into X_ROWID;
194   if (c%notfound) then
195     close c;
196     raise no_data_found;
197   end if;
198   close c;
199 
200   exception --Added for bug 4565156
201       when others then
202        x_rowid := l_rowid;
203        raise;
204 
205 end INSERT_ROW;
206 
207 procedure LOCK_ROW (
208   X_CI_TYPE_ID in NUMBER,
209   X_CI_TYPE_CLASS_CODE in VARCHAR2,
210   X_AUTO_NUMBER_FLAG in VARCHAR2,
211   X_RESOLUTION_REQUIRED_FLAG in VARCHAR2,
212   X_APPROVAL_REQUIRED_FLAG in VARCHAR2,
213   X_SOURCE_ATTRS_ENABLED_FLAG in VARCHAR2,
214   X_ALLOW_ALL_USAGE_FLAG in VARCHAR2,
215   X_RECORD_VERSION_NUMBER in NUMBER,
216   X_START_DATE_ACTIVE in DATE,
217   X_END_DATE_ACTIVE in DATE,
218   X_CLASSIFICATION_CATEGORY in VARCHAR2,
219   X_REASON_CATEGORY in VARCHAR2,
223   X_ATTRIBUTE2 in VARCHAR2,
220   X_RESOLUTION_CATEGORY in VARCHAR2,
221   X_ATTRIBUTE_CATEGORY in VARCHAR2,
222   X_ATTRIBUTE1 in VARCHAR2,
224   X_ATTRIBUTE3 in VARCHAR2,
225   X_ATTRIBUTE4 in VARCHAR2,
226   X_ATTRIBUTE5 in VARCHAR2,
227   X_ATTRIBUTE6 in VARCHAR2,
228   X_ATTRIBUTE7 in VARCHAR2,
229   X_ATTRIBUTE8 in VARCHAR2,
230   X_ATTRIBUTE9 in VARCHAR2,
231   X_ATTRIBUTE10 in VARCHAR2,
232   X_ATTRIBUTE11 in VARCHAR2,
233   X_ATTRIBUTE12 in VARCHAR2,
234   X_ATTRIBUTE13 in VARCHAR2,
235   X_ATTRIBUTE14 in VARCHAR2,
236   X_ATTRIBUTE15 in VARCHAR2,
237   X_NAME in VARCHAR2,
238   X_SHORT_NAME in VARCHAR2,
239   X_DESCRIPTION in VARCHAR2,
240  --|start:   16-FEB-2009  cklee  R12.1.2 setup ehancement
241   X_APPROVAL_TYPE_CODE            IN VARCHAR2 DEFAULT 'STANDARD',
242   X_SUBCONTRACTOR_REPORTING_FLAG  IN VARCHAR2 DEFAULT 'N',
243   X_PREFIX_AUTO_NUMBER            IN VARCHAR2 DEFAULT NULL,
244 --|end:   16-FEB-2009  cklee  R12.1.2 setup ehancement
245 --|start   29-APR-2009  cklee  R12.1.2 setup ehancement v2
246   X_IMPACT_BUDGET_TYPE_CODE       IN VARCHAR2 DEFAULT 'NA',
247   X_COST_COL_FLAG                 IN VARCHAR2 DEFAULT 'N',
248   X_REV_COL_FLAG                  IN VARCHAR2 DEFAULT 'N',
249   X_DIR_COST_REG_FLAG             IN VARCHAR2 DEFAULT 'N',
250   X_SUPP_COST_REG_FLAG            IN VARCHAR2 DEFAULT 'N',
251   X_DIR_REG_REV_COL_FLAG          IN VARCHAR2 DEFAULT 'N'
252 --|end   29-APR-2009  cklee  R12.1.2 setup ehancement v2
253 
254 ) is
255   cursor c is select
256       CI_TYPE_CLASS_CODE,
257       AUTO_NUMBER_FLAG,
258       RESOLUTION_REQUIRED_FLAG,
259       APPROVAL_REQUIRED_FLAG,
260       SOURCE_ATTRS_ENABLED_FLAG,
261       ALLOW_ALL_USAGE_FLAG,
262       RECORD_VERSION_NUMBER,
263       START_DATE_ACTIVE,
264       END_DATE_ACTIVE,
265       CLASSIFICATION_CATEGORY,
266       REASON_CATEGORY,
267       RESOLUTION_CATEGORY,
268       ATTRIBUTE_CATEGORY,
269       ATTRIBUTE1,
270       ATTRIBUTE2,
271       ATTRIBUTE3,
272       ATTRIBUTE4,
273       ATTRIBUTE5,
274       ATTRIBUTE6,
275       ATTRIBUTE7,
276       ATTRIBUTE8,
277       ATTRIBUTE9,
278       ATTRIBUTE10,
279       ATTRIBUTE11,
280       ATTRIBUTE12,
281       ATTRIBUTE13,
282       ATTRIBUTE14,
283       ATTRIBUTE15,
284 --|start:   16-FEB-2009  cklee  R12.1.2 setup ehancement
285       APPROVAL_TYPE_CODE,
286       SUBCONTRACTOR_REPORTING_FLAG,
287       PREFIX_AUTO_NUMBER,
288 --|end:   16-FEB-2009  cklee  R12.1.2 setup ehancement
289     IMPACT_BUDGET_TYPE_CODE,
290     COST_COL_FLAG,
291     REV_COL_FLAG,
292     DIR_COST_REG_FLAG,
293     SUPP_COST_REG_FLAG,
294     DIR_REG_REV_COL_FLAG
295     from PA_CI_TYPES_B
296     where CI_TYPE_ID = X_CI_TYPE_ID
297     for update of CI_TYPE_ID nowait;
298   recinfo c%rowtype;
299 
300   cursor c1 is select
301       NAME,
302       SHORT_NAME,
303       DESCRIPTION,
304       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
305     from PA_CI_TYPES_TL
306     where CI_TYPE_ID = X_CI_TYPE_ID
307     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
308     for update of CI_TYPE_ID nowait;
309 begin
310   open c;
311   fetch c into recinfo;
312   if (c%notfound) then
313     close c;
314     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
315     app_exception.raise_exception;
316   end if;
317   close c;
318   if (    (recinfo.CI_TYPE_CLASS_CODE = X_CI_TYPE_CLASS_CODE)
319       AND (recinfo.AUTO_NUMBER_FLAG = X_AUTO_NUMBER_FLAG)
320       AND (recinfo.RESOLUTION_REQUIRED_FLAG = X_RESOLUTION_REQUIRED_FLAG)
321       AND (recinfo.APPROVAL_REQUIRED_FLAG = X_APPROVAL_REQUIRED_FLAG)
322       AND (recinfo.SOURCE_ATTRS_ENABLED_FLAG = X_SOURCE_ATTRS_ENABLED_FLAG)
323       AND (recinfo.ALLOW_ALL_USAGE_FLAG = X_ALLOW_ALL_USAGE_FLAG)
324       AND (recinfo.RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER)
325       AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
326       AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
327            OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
328       AND ((recinfo.CLASSIFICATION_CATEGORY = X_CLASSIFICATION_CATEGORY)
329            OR ((recinfo.CLASSIFICATION_CATEGORY is null) AND (X_CLASSIFICATION_CATEGORY is null)))
330       AND ((recinfo.REASON_CATEGORY = X_REASON_CATEGORY)
331            OR ((recinfo.REASON_CATEGORY is null) AND (X_REASON_CATEGORY is null)))
332       AND ((recinfo.RESOLUTION_CATEGORY = X_RESOLUTION_CATEGORY)
333            OR ((recinfo.RESOLUTION_CATEGORY is null) AND (X_RESOLUTION_CATEGORY is null)))
334       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
335            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
336       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
337            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
338       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
339            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
340       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
341            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
342       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
343            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
344       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
345            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
346       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
347            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
348       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
349            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
350       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
351            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
352       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
356       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
353            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
354       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
355            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
357            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
358       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
359            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
360       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
361            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
362       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
366 --|start:   16-FEB-2009  cklee  R12.1.2 setup ehancement
363            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
364       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
365            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
367       AND ((recinfo.APPROVAL_TYPE_CODE = X_APPROVAL_TYPE_CODE)
368            OR ((recinfo.APPROVAL_TYPE_CODE is null) AND (X_APPROVAL_TYPE_CODE is null)))
369       AND ((recinfo.SUBCONTRACTOR_REPORTING_FLAG = X_SUBCONTRACTOR_REPORTING_FLAG)
370            OR ((recinfo.SUBCONTRACTOR_REPORTING_FLAG is null) AND (X_SUBCONTRACTOR_REPORTING_FLAG is null)))
371       AND ((recinfo.PREFIX_AUTO_NUMBER = X_PREFIX_AUTO_NUMBER)
372            OR ((recinfo.PREFIX_AUTO_NUMBER is null) AND (X_PREFIX_AUTO_NUMBER is null)))
373 --|end:   16-FEB-2009  cklee  R12.1.2 setup ehancement
374       AND ((recinfo.IMPACT_BUDGET_TYPE_CODE = X_IMPACT_BUDGET_TYPE_CODE)
375            OR ((recinfo.IMPACT_BUDGET_TYPE_CODE is null) AND (X_IMPACT_BUDGET_TYPE_CODE is null)))
376       AND ((recinfo.COST_COL_FLAG = X_COST_COL_FLAG)
377            OR ((recinfo.COST_COL_FLAG is null) AND (X_COST_COL_FLAG is null)))
378       AND ((recinfo.REV_COL_FLAG = X_REV_COL_FLAG)
379            OR ((recinfo.REV_COL_FLAG is null) AND (X_REV_COL_FLAG is null)))
380       AND ((recinfo.DIR_COST_REG_FLAG = X_DIR_COST_REG_FLAG)
381            OR ((recinfo.DIR_COST_REG_FLAG is null) AND (X_DIR_COST_REG_FLAG is null)))
382       AND ((recinfo.SUPP_COST_REG_FLAG = X_SUPP_COST_REG_FLAG)
383            OR ((recinfo.SUPP_COST_REG_FLAG is null) AND (X_REV_COL_FLAG is null)))
384       AND ((recinfo.DIR_REG_REV_COL_FLAG = X_DIR_REG_REV_COL_FLAG)
385            OR ((recinfo.DIR_REG_REV_COL_FLAG is null) AND (X_DIR_REG_REV_COL_FLAG is null)))
386 
387   ) then
388     null;
389   else
390     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
391     app_exception.raise_exception;
392   end if;
393 
394   for tlinfo in c1 loop
395     if (tlinfo.BASELANG = 'Y') then
396       if (    (tlinfo.NAME = X_NAME)
397           AND (tlinfo.SHORT_NAME = X_SHORT_NAME)
398           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
399                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
400       ) then
401         null;
402       else
403         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
404         app_exception.raise_exception;
405       end if;
406     end if;
407   end loop;
408   return;
409 end LOCK_ROW;
410 
411 procedure UPDATE_ROW (
412   X_CI_TYPE_ID in NUMBER,
413   X_CI_TYPE_CLASS_CODE in VARCHAR2,
414   X_AUTO_NUMBER_FLAG in VARCHAR2,
415   X_RESOLUTION_REQUIRED_FLAG in VARCHAR2,
416   X_APPROVAL_REQUIRED_FLAG in VARCHAR2,
417   X_SOURCE_ATTRS_ENABLED_FLAG in VARCHAR2,
418   X_ALLOW_ALL_USAGE_FLAG in VARCHAR2,
419   X_RECORD_VERSION_NUMBER in NUMBER,
420   X_START_DATE_ACTIVE in DATE,
421   X_END_DATE_ACTIVE in DATE,
422   X_CLASSIFICATION_CATEGORY in VARCHAR2,
423   X_REASON_CATEGORY in VARCHAR2,
424   X_RESOLUTION_CATEGORY in VARCHAR2,
425   X_ATTRIBUTE_CATEGORY in VARCHAR2,
426   X_ATTRIBUTE1 in VARCHAR2,
427   X_ATTRIBUTE2 in VARCHAR2,
428   X_ATTRIBUTE3 in VARCHAR2,
429   X_ATTRIBUTE4 in VARCHAR2,
430   X_ATTRIBUTE5 in VARCHAR2,
431   X_ATTRIBUTE6 in VARCHAR2,
432   X_ATTRIBUTE7 in VARCHAR2,
433   X_ATTRIBUTE8 in VARCHAR2,
434   X_ATTRIBUTE9 in VARCHAR2,
435   X_ATTRIBUTE10 in VARCHAR2,
436   X_ATTRIBUTE11 in VARCHAR2,
437   X_ATTRIBUTE12 in VARCHAR2,
438   X_ATTRIBUTE13 in VARCHAR2,
439   X_ATTRIBUTE14 in VARCHAR2,
440   X_ATTRIBUTE15 in VARCHAR2,
441   X_NAME in VARCHAR2,
442   X_SHORT_NAME in VARCHAR2,
443   X_DESCRIPTION in VARCHAR2,
444   X_LAST_UPDATE_DATE in DATE,
445   X_LAST_UPDATED_BY in NUMBER,
446   X_LAST_UPDATE_LOGIN in NUMBER,
447 --|start:   16-FEB-2009  cklee  R12.1.2 setup ehancement
448   X_APPROVAL_TYPE_CODE            IN VARCHAR2 DEFAULT 'STANDARD',
449   X_SUBCONTRACTOR_REPORTING_FLAG  IN VARCHAR2 DEFAULT 'N',
450   X_PREFIX_AUTO_NUMBER            IN VARCHAR2 DEFAULT NULL,
451 --|end:   16-FEB-2009  cklee  R12.1.2 setup ehancement
452 --|start   29-APR-2009  cklee  R12.1.2 setup ehancement v2
453   X_IMPACT_BUDGET_TYPE_CODE       IN VARCHAR2 DEFAULT 'NA',
454   X_COST_COL_FLAG                 IN VARCHAR2 DEFAULT 'N',
455   X_REV_COL_FLAG                  IN VARCHAR2 DEFAULT 'N',
456   X_DIR_COST_REG_FLAG             IN VARCHAR2 DEFAULT 'N',
460 
457   X_SUPP_COST_REG_FLAG            IN VARCHAR2 DEFAULT 'N',
458   X_DIR_REG_REV_COL_FLAG          IN VARCHAR2 DEFAULT 'N'
459 --|end   29-APR-2009  cklee  R12.1.2 setup ehancement v2
461 ) is
462 begin
463   update PA_CI_TYPES_B set
464     CI_TYPE_CLASS_CODE = X_CI_TYPE_CLASS_CODE,
465     AUTO_NUMBER_FLAG = X_AUTO_NUMBER_FLAG,
466     RESOLUTION_REQUIRED_FLAG = X_RESOLUTION_REQUIRED_FLAG,
467     APPROVAL_REQUIRED_FLAG = X_APPROVAL_REQUIRED_FLAG,
468     SOURCE_ATTRS_ENABLED_FLAG = X_SOURCE_ATTRS_ENABLED_FLAG,
469     ALLOW_ALL_USAGE_FLAG = X_ALLOW_ALL_USAGE_FLAG,
470     RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER,
471     START_DATE_ACTIVE = X_START_DATE_ACTIVE,
472     END_DATE_ACTIVE = X_END_DATE_ACTIVE,
473     CLASSIFICATION_CATEGORY = X_CLASSIFICATION_CATEGORY,
474     REASON_CATEGORY = X_REASON_CATEGORY,
475     RESOLUTION_CATEGORY = X_RESOLUTION_CATEGORY,
476     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
477     ATTRIBUTE1 = X_ATTRIBUTE1,
478     ATTRIBUTE2 = X_ATTRIBUTE2,
479     ATTRIBUTE3 = X_ATTRIBUTE3,
480     ATTRIBUTE4 = X_ATTRIBUTE4,
481     ATTRIBUTE5 = X_ATTRIBUTE5,
482     ATTRIBUTE6 = X_ATTRIBUTE6,
483     ATTRIBUTE7 = X_ATTRIBUTE7,
484     ATTRIBUTE8 = X_ATTRIBUTE8,
485     ATTRIBUTE9 = X_ATTRIBUTE9,
486     ATTRIBUTE10 = X_ATTRIBUTE10,
487     ATTRIBUTE11 = X_ATTRIBUTE11,
488     ATTRIBUTE12 = X_ATTRIBUTE12,
489     ATTRIBUTE13 = X_ATTRIBUTE13,
490     ATTRIBUTE14 = X_ATTRIBUTE14,
491     ATTRIBUTE15 = X_ATTRIBUTE15,
492     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
493     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
494     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
495   --|start:   16-FEB-2009  cklee  R12.1.2 setup ehancement
496     APPROVAL_TYPE_CODE = X_APPROVAL_TYPE_CODE,
497     SUBCONTRACTOR_REPORTING_FLAG = X_SUBCONTRACTOR_REPORTING_FLAG,
498     PREFIX_AUTO_NUMBER = X_PREFIX_AUTO_NUMBER,
499 --|end:   16-FEB-2009  cklee  R12.1.2 setup ehancement
500 IMPACT_BUDGET_TYPE_CODE = X_IMPACT_BUDGET_TYPE_CODE,
501 COST_COL_FLAG = X_COST_COL_FLAG,
502 REV_COL_FLAG = X_REV_COL_FLAG,
503 DIR_COST_REG_FLAG = X_DIR_COST_REG_FLAG,
504 SUPP_COST_REG_FLAG = X_SUPP_COST_REG_FLAG,
505 DIR_REG_REV_COL_FLAG = X_DIR_REG_REV_COL_FLAG
506 
507   where CI_TYPE_ID = X_CI_TYPE_ID;
508 
509   if (sql%notfound) then
510     raise no_data_found;
511   end if;
512 
513   update PA_CI_TYPES_TL set
514     NAME = X_NAME,
515     SHORT_NAME = X_SHORT_NAME,
519     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
516     DESCRIPTION = X_DESCRIPTION,
517     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
518     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
520     SOURCE_LANG = userenv('LANG')
521   where CI_TYPE_ID = X_CI_TYPE_ID
522   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
523 
524   if (sql%notfound) then
525     raise no_data_found;
526   end if;
527 end UPDATE_ROW;
528 
529 procedure DELETE_ROW (
530   X_CI_TYPE_ID in NUMBER
531 ) is
532 begin
533   delete from PA_CI_TYPES_TL
534   where CI_TYPE_ID = X_CI_TYPE_ID;
535 
536   if (sql%notfound) then
537     raise no_data_found;
538   end if;
539 
540   delete from PA_CI_TYPES_B
541   where CI_TYPE_ID = X_CI_TYPE_ID;
542 
543   if (sql%notfound) then
544     raise no_data_found;
545   end if;
546 end DELETE_ROW;
547 
548 procedure ADD_LANGUAGE
549 is
550 begin
551   delete from PA_CI_TYPES_TL T
552   where not exists
553     (select NULL
554     from PA_CI_TYPES_B B
555     where B.CI_TYPE_ID = T.CI_TYPE_ID
556     );
557 
558   update PA_CI_TYPES_TL T set (
559       NAME,
560       SHORT_NAME,
561       DESCRIPTION
562     ) = (select
563       B.NAME,
564       B.SHORT_NAME,
565       B.DESCRIPTION
566     from PA_CI_TYPES_TL B
567     where B.CI_TYPE_ID = T.CI_TYPE_ID
568     and B.LANGUAGE = T.SOURCE_LANG)
569   where (
570       T.CI_TYPE_ID,
571       T.LANGUAGE
572   ) in (select
573       SUBT.CI_TYPE_ID,
574       SUBT.LANGUAGE
578     and (SUBB.NAME <> SUBT.NAME
575     from PA_CI_TYPES_TL SUBB, PA_CI_TYPES_TL SUBT
576     where SUBB.CI_TYPE_ID = SUBT.CI_TYPE_ID
577     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
579       or SUBB.SHORT_NAME <> SUBT.SHORT_NAME
580       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
581       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
582       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
583   ));
584 
585   insert into PA_CI_TYPES_TL (
586     CI_TYPE_ID,
587     NAME,
588     SHORT_NAME,
589     LAST_UPDATED_BY,
590     CREATED_BY,
591     CREATION_DATE,
592     LAST_UPDATE_DATE,
593     LAST_UPDATE_LOGIN,
594     DESCRIPTION,
595     LANGUAGE,
596     SOURCE_LANG
597   ) select
598     B.CI_TYPE_ID,
599     B.NAME,
600     B.SHORT_NAME,
601     B.LAST_UPDATED_BY,
602     B.CREATED_BY,
603     B.CREATION_DATE,
604     B.LAST_UPDATE_DATE,
605     B.LAST_UPDATE_LOGIN,
606     B.DESCRIPTION,
607     L.LANGUAGE_CODE,
608     B.SOURCE_LANG
609   from PA_CI_TYPES_TL B, FND_LANGUAGES L
610   where L.INSTALLED_FLAG in ('I', 'B')
611   and B.LANGUAGE = userenv('LANG')
612   and not exists
613     (select NULL
614     from PA_CI_TYPES_TL T
615     where T.CI_TYPE_ID = B.CI_TYPE_ID
616     and T.LANGUAGE = L.LANGUAGE_CODE);
617 end ADD_LANGUAGE;
618 
619 end PA_CI_TYPES_PKG;