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_HOLD_STATUSES_PKG

Source


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