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_COMM_ACTIONS_PKG

Source


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