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_PRIORITY_CODES_PKG

Source


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