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.OKE_CHG_STATUSES_PKG

Source


1 package body OKE_CHG_STATUSES_PKG as
2 /* $Header: OKEOCSXB.pls 115.6 2002/11/21 23:06:41 ybchen ship $ */
3 procedure INSERT_ROW (
4   X_ROWID 			in out NOCOPY VARCHAR2
5 , X_CHG_STATUS_CODE 		in 	VARCHAR2
6 , X_CREATION_DATE 		in 	DATE
7 , X_CREATED_BY 			in 	NUMBER
8 , X_LAST_UPDATE_DATE 		in 	DATE
9 , X_LAST_UPDATED_BY 		in 	NUMBER
10 , X_LAST_UPDATE_LOGIN 		in 	NUMBER
11 , X_CHG_STATUS_NAME 		in 	VARCHAR2
12 , X_CHG_STATUS_TYPE_CODE 	in 	VARCHAR2
13 , X_DEFAULT_STATUS              in      VARCHAR2
14 , X_WF_ITEM_TYPE		in 	VARCHAR2
15 , X_WF_PROCESS  		in 	VARCHAR2
16 , X_DESCRIPTION 		in 	VARCHAR2
17 , X_START_DATE_ACTIVE 		in 	DATE
18 , X_END_DATE_ACTIVE   		in 	DATE
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 ) is
36   cursor C is select ROWID from OKE_CHG_STATUSES_B
37     where CHG_STATUS_CODE = X_CHG_STATUS_CODE
38     ;
39 begin
40   insert into OKE_CHG_STATUSES_B (
41   CHG_STATUS_CODE
42 , CREATION_DATE
43 , CREATED_BY
44 , LAST_UPDATE_DATE
45 , LAST_UPDATED_BY
46 , LAST_UPDATE_LOGIN
47 , CHG_STATUS_TYPE_CODE
48 , DEFAULT_STATUS
49 , WF_ITEM_TYPE
50 , WF_PROCESS
51 , START_DATE_ACTIVE
52 , END_DATE_ACTIVE
53 , ATTRIBUTE_CATEGORY
54 , ATTRIBUTE1
55 , ATTRIBUTE2
56 , ATTRIBUTE3
57 , ATTRIBUTE4
58 , ATTRIBUTE5
59 , ATTRIBUTE6
60 , ATTRIBUTE7
61 , ATTRIBUTE8
62 , ATTRIBUTE9
63 , ATTRIBUTE10
64 , ATTRIBUTE11
65 , ATTRIBUTE12
66 , ATTRIBUTE13
67 , ATTRIBUTE14
68 , ATTRIBUTE15
69   ) values (
70   X_CHG_STATUS_CODE
71 , X_CREATION_DATE
72 , X_CREATED_BY
73 , X_LAST_UPDATE_DATE
74 , X_LAST_UPDATED_BY
75 , X_LAST_UPDATE_LOGIN
76 , X_CHG_STATUS_TYPE_CODE
77 , X_DEFAULT_STATUS
78 , X_WF_ITEM_TYPE
79 , X_WF_PROCESS
80 , X_START_DATE_ACTIVE
81 , X_END_DATE_ACTIVE
82 , X_ATTRIBUTE_CATEGORY
83 , X_ATTRIBUTE1
84 , X_ATTRIBUTE2
85 , X_ATTRIBUTE3
86 , X_ATTRIBUTE4
87 , X_ATTRIBUTE5
88 , X_ATTRIBUTE6
89 , X_ATTRIBUTE7
90 , X_ATTRIBUTE8
91 , X_ATTRIBUTE9
92 , X_ATTRIBUTE10
93 , X_ATTRIBUTE11
94 , X_ATTRIBUTE12
95 , X_ATTRIBUTE13
96 , X_ATTRIBUTE14
97 , X_ATTRIBUTE15
98 );
99 
100   insert into OKE_CHG_STATUSES_TL (
101   CHG_STATUS_CODE
102 , LANGUAGE
103 , CREATION_DATE
104 , CREATED_BY
105 , LAST_UPDATE_DATE
106 , LAST_UPDATED_BY
107 , LAST_UPDATE_LOGIN
108 , CHG_STATUS_NAME
109 , DESCRIPTION
110 , SOURCE_LANG
111   ) select
112   X_CHG_STATUS_CODE
113 , L.LANGUAGE_CODE
114 , X_CREATION_DATE
115 , X_CREATED_BY
116 , X_LAST_UPDATE_DATE
117 , X_LAST_UPDATED_BY
118 , X_LAST_UPDATE_LOGIN
119 , X_CHG_STATUS_NAME
120 , X_DESCRIPTION
121 , userenv('LANG')
122   from FND_LANGUAGES L
123  where L.INSTALLED_FLAG in ('I', 'B')
124    and not exists
125     (select NULL
126     from OKE_CHG_STATUSES_TL T
127    where T.CHG_STATUS_CODE = X_CHG_STATUS_CODE
128      and T.LANGUAGE = L.LANGUAGE_CODE)
129 ;
130 
131   open c;
132   fetch c into X_ROWID;
133   if (c%notfound) then
134     close c;
135     raise no_data_found;
136   end if;
137   close c;
138 
139 end INSERT_ROW;
140 
141 procedure LOCK_ROW (
142   X_CHG_STATUS_CODE		in 	VARCHAR2
143 , X_CHG_STATUS_NAME 		in 	VARCHAR2
144 , X_CHG_STATUS_TYPE_CODE 	in 	VARCHAR2
145 , X_DEFAULT_STATUS              in      VARCHAR2
146 , X_WF_ITEM_TYPE		in 	VARCHAR2
147 , X_WF_PROCESS  		in 	VARCHAR2
148 , X_DESCRIPTION 		in 	VARCHAR2
149 , X_START_DATE_ACTIVE 		in 	DATE
150 , X_END_DATE_ACTIVE   		in 	DATE
151 , X_ATTRIBUTE_CATEGORY 		in 	VARCHAR2
152 , X_ATTRIBUTE1 			in 	VARCHAR2
153 , X_ATTRIBUTE2 			in 	VARCHAR2
154 , X_ATTRIBUTE3 			in 	VARCHAR2
155 , X_ATTRIBUTE4 			in 	VARCHAR2
156 , X_ATTRIBUTE5 			in 	VARCHAR2
157 , X_ATTRIBUTE6 			in 	VARCHAR2
158 , X_ATTRIBUTE7 			in 	VARCHAR2
159 , X_ATTRIBUTE8 			in 	VARCHAR2
160 , X_ATTRIBUTE9 			in 	VARCHAR2
161 , X_ATTRIBUTE10 		in 	VARCHAR2
162 , X_ATTRIBUTE11 		in 	VARCHAR2
163 , X_ATTRIBUTE12 		in 	VARCHAR2
164 , X_ATTRIBUTE13 		in 	VARCHAR2
165 , X_ATTRIBUTE14 		in 	VARCHAR2
166 , X_ATTRIBUTE15 		in 	VARCHAR2
167 ) is
168   cursor c is select
169        CHG_STATUS_CODE
170      , CHG_STATUS_TYPE_CODE
171      , DEFAULT_STATUS
172      , WF_ITEM_TYPE
173      , WF_PROCESS
174      , START_DATE_ACTIVE
175      , END_DATE_ACTIVE
176      , ATTRIBUTE_CATEGORY
177      , ATTRIBUTE1
178      , ATTRIBUTE2
179      , ATTRIBUTE3
180      , ATTRIBUTE4
181      , ATTRIBUTE5
182      , ATTRIBUTE6
183      , ATTRIBUTE7
184      , ATTRIBUTE8
185      , ATTRIBUTE9
186      , ATTRIBUTE10
187      , ATTRIBUTE11
188      , ATTRIBUTE12
189      , ATTRIBUTE13
190      , ATTRIBUTE14
191      , ATTRIBUTE15
192     from OKE_CHG_STATUSES_B
193     where CHG_STATUS_CODE = X_CHG_STATUS_CODE
194     for update of CHG_STATUS_CODE nowait;
195   recinfo c%rowtype;
196 
197   cursor c1 is select
198       CHG_STATUS_NAME
199      ,DESCRIPTION
200      ,decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
201     from OKE_CHG_STATUSES_TL
202     where CHG_STATUS_CODE= X_CHG_STATUS_CODE
203     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
204     for update of CHG_STATUS_CODE nowait;
205   tlinfo c1%rowtype;
206 
207 begin
208   open c;
209   fetch c into recinfo;
210   if (c%notfound) then
211     close c;
212     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
213     app_exception.raise_exception;
214   end if;
215   close c;
216 
217   if (    (recinfo.CHG_STATUS_CODE = X_CHG_STATUS_CODE)
218       AND ((recinfo.CHG_STATUS_TYPE_CODE = X_CHG_STATUS_TYPE_CODE)
219            OR ((recinfo.CHG_STATUS_TYPE_CODE is null) AND (X_CHG_STATUS_TYPE_CODE is null)))
220       AND ((recinfo.DEFAULT_STATUS = X_DEFAULT_STATUS)
221            OR ((recinfo.DEFAULT_STATUS is null) AND (X_DEFAULT_STATUS is null)))
222       AND ((recinfo.WF_ITEM_TYPE = X_WF_ITEM_TYPE)
223            OR ((recinfo.WF_ITEM_TYPE is null) AND (X_WF_ITEM_TYPE is null)))
224       AND ((recinfo.WF_PROCESS = X_WF_PROCESS)
225            OR ((recinfo.WF_PROCESS is null) AND (X_WF_PROCESS is null)))
226       AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
227            OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
228       AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
229            OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
230       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
231            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
232       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
233            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
234       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
235            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
236       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
237            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
238       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
239            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
240       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
241            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
242       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
243            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
244       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
245            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
246       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
247            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
248       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
249            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
250       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
251            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
252       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
253            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
254       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
255            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
256       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
257            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
258       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
259            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
260       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
261            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
262   ) then
263     null;
264   else
265     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
266     app_exception.raise_exception;
267   end if;
268 
269   for tlinfo in c1 loop
270     if (tlinfo.BASELANG = 'Y') then
271       if (    ((tlinfo.CHG_STATUS_NAME = X_CHG_STATUS_NAME)
272                OR ((tlinfo.CHG_STATUS_NAME is null) AND (X_CHG_STATUS_NAME is null)))
273           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
274                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
275       ) then
276         null;
277       else
278         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
279         app_exception.raise_exception;
280       end if;
281     end if;
282   end loop;
283 
284   return;
285 end LOCK_ROW;
286 
287 procedure UPDATE_ROW (
288   X_CHG_STATUS_CODE 		in 	VARCHAR2
289 , X_LAST_UPDATE_DATE 		in 	DATE
290 , X_LAST_UPDATED_BY 		in 	NUMBER
291 , X_LAST_UPDATE_LOGIN 		in 	NUMBER
292 , X_CHG_STATUS_NAME 		in 	VARCHAR2
293 , X_CHG_STATUS_TYPE_CODE 	in 	VARCHAR2
294 , X_DEFAULT_STATUS              in      VARCHAR2
295 , X_WF_ITEM_TYPE		in 	VARCHAR2
296 , X_WF_PROCESS  		in 	VARCHAR2
297 , X_DESCRIPTION 		in 	VARCHAR2
298 , X_START_DATE_ACTIVE 		in 	DATE
299 , X_END_DATE_ACTIVE   		in 	DATE
300 , X_ATTRIBUTE_CATEGORY 		in 	VARCHAR2
301 , X_ATTRIBUTE1 			in 	VARCHAR2
302 , X_ATTRIBUTE2 			in 	VARCHAR2
303 , X_ATTRIBUTE3 			in 	VARCHAR2
304 , X_ATTRIBUTE4 			in 	VARCHAR2
305 , X_ATTRIBUTE5 			in 	VARCHAR2
306 , X_ATTRIBUTE6 			in 	VARCHAR2
307 , X_ATTRIBUTE7 			in 	VARCHAR2
308 , X_ATTRIBUTE8 			in 	VARCHAR2
309 , X_ATTRIBUTE9 			in 	VARCHAR2
310 , X_ATTRIBUTE10 		in 	VARCHAR2
311 , X_ATTRIBUTE11 		in 	VARCHAR2
312 , X_ATTRIBUTE12 		in 	VARCHAR2
313 , X_ATTRIBUTE13 		in 	VARCHAR2
314 , X_ATTRIBUTE14 		in 	VARCHAR2
315 , X_ATTRIBUTE15 		in 	VARCHAR2
316 ) is
317 begin
318   update OKE_CHG_STATUSES_B set
319   LAST_UPDATE_DATE      	= X_LAST_UPDATE_DATE
320 , LAST_UPDATED_BY 		= X_LAST_UPDATED_BY
321 , LAST_UPDATE_LOGIN   		= X_LAST_UPDATE_LOGIN
322 , CHG_STATUS_TYPE_CODE 		= X_CHG_STATUS_TYPE_CODE
323 , DEFAULT_STATUS                = X_DEFAULT_STATUS
324 , WF_ITEM_TYPE			= X_WF_ITEM_TYPE
325 , WF_PROCESS  			= X_WF_PROCESS
326 , START_DATE_ACTIVE 		= X_START_DATE_ACTIVE
327 , END_DATE_ACTIVE   		= X_END_DATE_ACTIVE
328 , ATTRIBUTE_CATEGORY		= X_ATTRIBUTE_CATEGORY
329 , ATTRIBUTE1            	= X_ATTRIBUTE1
330 , ATTRIBUTE2			= X_ATTRIBUTE2
331 , ATTRIBUTE3    		= X_ATTRIBUTE3
332 , ATTRIBUTE4        		= X_ATTRIBUTE4
333 , ATTRIBUTE5            	= X_ATTRIBUTE5
334 , ATTRIBUTE6			= X_ATTRIBUTE6
335 , ATTRIBUTE7    		= X_ATTRIBUTE7
336 , ATTRIBUTE8        		= X_ATTRIBUTE8
337 , ATTRIBUTE9            	= X_ATTRIBUTE9
338 , ATTRIBUTE10           	= X_ATTRIBUTE10
339 , ATTRIBUTE11			= X_ATTRIBUTE11
340 , ATTRIBUTE12    		= X_ATTRIBUTE12
341 , ATTRIBUTE13        		= X_ATTRIBUTE13
342 , ATTRIBUTE14           	= X_ATTRIBUTE14
343 , ATTRIBUTE15           	= X_ATTRIBUTE15
344 where CHG_STATUS_CODE 		= X_CHG_STATUS_CODE
345 ;
346 
347   if (sql%notfound) then
348     raise no_data_found;
349   end if;
350 
351   update OKE_CHG_STATUSES_TL set
352   LAST_UPDATE_DATE      = X_LAST_UPDATE_DATE
353 , LAST_UPDATED_BY 	= X_LAST_UPDATED_BY
354 , LAST_UPDATE_LOGIN   	= X_LAST_UPDATE_LOGIN
355 , CHG_STATUS_NAME 	= X_CHG_STATUS_NAME
356 , DESCRIPTION       	= X_DESCRIPTION
357 , SOURCE_LANG 		= userenv('LANG')
358 where CHG_STATUS_CODE   = X_CHG_STATUS_CODE
359   and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
360 ;
361 
362   if (sql%notfound) then
363     raise no_data_found;
364   end if;
365 end UPDATE_ROW;
366 
367 procedure ADD_LANGUAGE
368 is
369 begin
370   delete from OKE_CHG_STATUSES_TL T
371   where not exists
372     (select NULL
373     from OKE_CHG_STATUSES_B B
374     where B.CHG_STATUS_CODE = T.CHG_STATUS_CODE
375     );
376 
377   update OKE_CHG_STATUSES_TL T set (
378       CHG_STATUS_NAME,
379       DESCRIPTION
380     ) = (select
381       B.CHG_STATUS_NAME,
382       B.DESCRIPTION
383     from OKE_CHG_STATUSES_TL B
384     where B.CHG_STATUS_CODE = T.CHG_STATUS_CODE
385     and B.LANGUAGE = T.SOURCE_LANG)
386   where (
387       T.CHG_STATUS_CODE,
388       T.LANGUAGE
389   ) in (select
390       SUBT.CHG_STATUS_CODE,
391       SUBT.LANGUAGE
392     from OKE_CHG_STATUSES_TL SUBB, OKE_CHG_STATUSES_TL SUBT
393     where SUBB.CHG_STATUS_CODE = SUBT.CHG_STATUS_CODE
394     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
395     and (SUBB.CHG_STATUS_NAME <> SUBT.CHG_STATUS_NAME
396       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
397       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
398       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
399   ));
400 
401   insert into OKE_CHG_STATUSES_TL (
402     LAST_UPDATE_DATE,
403     LAST_UPDATED_BY,
404     LAST_UPDATE_LOGIN,
405     CHG_STATUS_NAME,
406     DESCRIPTION,
407     CHG_STATUS_CODE,
408     CREATION_DATE,
409     CREATED_BY,
410     LANGUAGE,
411     SOURCE_LANG
412   ) select
413     B.LAST_UPDATE_DATE,
414     B.LAST_UPDATED_BY,
415     B.LAST_UPDATE_LOGIN,
416     B.CHG_STATUS_NAME,
417     B.DESCRIPTION,
418     B.CHG_STATUS_CODE,
419     B.CREATION_DATE,
420     B.CREATED_BY,
421     L.LANGUAGE_CODE,
422     B.SOURCE_LANG
423   from OKE_CHG_STATUSES_TL B, FND_LANGUAGES L
424   where L.INSTALLED_FLAG in ('I', 'B')
425   and B.LANGUAGE = userenv('LANG')
426   and not exists
427     (select NULL
428     from OKE_CHG_STATUSES_TL T
429     where T.CHG_STATUS_CODE = B.CHG_STATUS_CODE
430     and T.LANGUAGE = L.LANGUAGE_CODE);
431 end ADD_LANGUAGE;
432 
433 end OKE_CHG_STATUSES_PKG;