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

Source


1 package body XDP_WORKITEMS_PKG as
2 /* $Header: XDPWIB.pls 120.2 2005/07/15 06:31:42 appldev ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in OUT NOCOPY VARCHAR2,
5   X_WORKITEM_ID in NUMBER,
6   X_WORKITEM_NAME in VARCHAR2,
7   X_VERSION in VARCHAR2,
8   X_WI_TYPE_CODE in VARCHAR2,
9   X_VALID_DATE in DATE,
10   X_INVALID_DATE in DATE,
11   X_VALIDATION_ENABLED_FLAG in VARCHAR2,
12   X_VALIDATION_PROCEDURE in VARCHAR2,
13   X_FA_EXEC_MAP_PROC in VARCHAR2,
14   X_USER_WF_ITEM_TYPE in VARCHAR2,
15   X_USER_WF_ITEM_KEY_PREFIX in VARCHAR2,
16   X_USER_WF_PROCESS_NAME in VARCHAR2,
17   X_WF_EXEC_PROC in VARCHAR2,
18   X_TIME_ESTIMATE in NUMBER,
19   X_PROTECTED_FLAG in VARCHAR2,
20   X_ROLE_NAME in VARCHAR2,
21   X_DISPLAY_NAME in VARCHAR2,
22   X_DESCRIPTION in VARCHAR2,
23   X_CREATION_DATE in DATE,
24   X_CREATED_BY in NUMBER,
25   X_LAST_UPDATE_DATE in DATE,
26   X_LAST_UPDATED_BY in NUMBER,
27   X_LAST_UPDATE_LOGIN in NUMBER
28 ) is
29   cursor C is select ROWID from XDP_WORKITEMS
30     where WORKITEM_ID = X_WORKITEM_ID
31     ;
32 begin
33   insert into XDP_WORKITEMS (
34     WORKITEM_ID,
35     WORKITEM_NAME,
36     VERSION,
37     WI_TYPE_CODE,
38     VALID_DATE,
39     INVALID_DATE,
40     VALIDATION_ENABLED_FLAG,
41     VALIDATION_PROCEDURE,
42     FA_EXEC_MAP_PROC,
43     USER_WF_ITEM_TYPE,
44     USER_WF_ITEM_KEY_PREFIX,
45     USER_WF_PROCESS_NAME,
46     WF_EXEC_PROC,
47     TIME_ESTIMATE,
48     PROTECTED_FLAG,
49     ROLE_NAME,
50     CREATION_DATE,
51     CREATED_BY,
52     LAST_UPDATE_DATE,
53     LAST_UPDATED_BY,
54     LAST_UPDATE_LOGIN
55   ) values (
56     X_WORKITEM_ID,
57     X_WORKITEM_NAME,
58     X_VERSION,
59     X_WI_TYPE_CODE,
60     X_VALID_DATE,
61     X_INVALID_DATE,
62     X_VALIDATION_ENABLED_FLAG,
63     X_VALIDATION_PROCEDURE,
64     X_FA_EXEC_MAP_PROC,
65     X_USER_WF_ITEM_TYPE,
66     X_USER_WF_ITEM_KEY_PREFIX,
67     X_USER_WF_PROCESS_NAME,
68     X_WF_EXEC_PROC,
69     X_TIME_ESTIMATE,
70     X_PROTECTED_FLAG,
71     X_ROLE_NAME,
72     X_CREATION_DATE,
73     X_CREATED_BY,
74     X_LAST_UPDATE_DATE,
75     X_LAST_UPDATED_BY,
76     X_LAST_UPDATE_LOGIN
77   );
78 
79   insert into XDP_WORKITEMS_TL (
80     WORKITEM_ID,
81     DISPLAY_NAME,
82     DESCRIPTION,
83     CREATED_BY,
84     CREATION_DATE,
85     LAST_UPDATED_BY,
86     LAST_UPDATE_DATE,
87     LAST_UPDATE_LOGIN,
88     LANGUAGE,
89     SOURCE_LANG
90   ) select
91     X_WORKITEM_ID,
92     X_DISPLAY_NAME,
93     X_DESCRIPTION,
94     X_CREATED_BY,
95     X_CREATION_DATE,
96     X_LAST_UPDATED_BY,
97     X_LAST_UPDATE_DATE,
98     X_LAST_UPDATE_LOGIN,
99     L.LANGUAGE_CODE,
100     userenv('LANG')
101   from FND_LANGUAGES L
102   where L.INSTALLED_FLAG in ('I', 'B')
103   and not exists
104     (select NULL
105     from XDP_WORKITEMS_TL T
106     where T.WORKITEM_ID = X_WORKITEM_ID
107     and T.LANGUAGE = L.LANGUAGE_CODE);
108 
109   open c;
110   fetch c into X_ROWID;
111   if (c%notfound) then
112     close c;
113     raise no_data_found;
114   end if;
115   close c;
116 
117 end INSERT_ROW;
118 
119 procedure LOCK_ROW (
120   X_WORKITEM_ID in NUMBER,
121   X_WORKITEM_NAME in VARCHAR2,
122   X_VERSION in VARCHAR2,
123   X_WI_TYPE_CODE in VARCHAR2,
124   X_VALID_DATE in DATE,
125   X_INVALID_DATE in DATE,
126   X_VALIDATION_ENABLED_FLAG in VARCHAR2,
127   X_VALIDATION_PROCEDURE in VARCHAR2,
128   X_FA_EXEC_MAP_PROC in VARCHAR2,
129   X_USER_WF_ITEM_TYPE in VARCHAR2,
130   X_USER_WF_ITEM_KEY_PREFIX in VARCHAR2,
131   X_USER_WF_PROCESS_NAME in VARCHAR2,
132   X_WF_EXEC_PROC in VARCHAR2,
133   X_TIME_ESTIMATE in NUMBER,
134   X_PROTECTED_FLAG in VARCHAR2,
135   X_ROLE_NAME in VARCHAR2,
136   X_DISPLAY_NAME in VARCHAR2,
137   X_DESCRIPTION in VARCHAR2
138 ) is
139   cursor c is select
140       WORKITEM_NAME,
141       VERSION,
142       WI_TYPE_CODE,
143       VALID_DATE,
144       INVALID_DATE,
145       VALIDATION_ENABLED_FLAG,
146       VALIDATION_PROCEDURE,
147       FA_EXEC_MAP_PROC,
148       USER_WF_ITEM_TYPE,
149       USER_WF_ITEM_KEY_PREFIX,
150       USER_WF_PROCESS_NAME,
151       WF_EXEC_PROC,
152       TIME_ESTIMATE,
153       PROTECTED_FLAG,
154       ROLE_NAME
155     from XDP_WORKITEMS
156     where WORKITEM_ID = X_WORKITEM_ID
157     for update of WORKITEM_ID nowait;
158   recinfo c%rowtype;
159 
160   cursor c1 is select
161       DISPLAY_NAME,
162       DESCRIPTION,
163       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
164     from XDP_WORKITEMS_TL
165     where WORKITEM_ID = X_WORKITEM_ID
166     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
167     for update of WORKITEM_ID nowait;
168 begin
169   open c;
170   fetch c into recinfo;
171   if (c%notfound) then
172     close c;
173     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
174     app_exception.raise_exception;
175   end if;
176   close c;
177   if (    (recinfo.WORKITEM_NAME = X_WORKITEM_NAME)
178       AND (recinfo.VERSION = X_VERSION)
179   AND ((recinfo.VALIDATION_PROCEDURE = X_VALIDATION_PROCEDURE)
180            OR ((recinfo.VALIDATION_PROCEDURE is null) AND (X_VALIDATION_PROCEDURE is null)))
181       AND (recinfo.VALIDATION_ENABLED_FLAG = X_VALIDATION_ENABLED_FLAG)
182       AND (recinfo.WI_TYPE_CODE = X_WI_TYPE_CODE)
183       AND ((recinfo.VALID_DATE = X_VALID_DATE)
184            OR ((recinfo.VALID_DATE is null) AND (X_VALID_DATE is null)))
185       AND ((recinfo.INVALID_DATE = X_INVALID_DATE)
186            OR ((recinfo.INVALID_DATE is null) AND (X_INVALID_DATE is null)))
187       AND ((recinfo.FA_EXEC_MAP_PROC = X_FA_EXEC_MAP_PROC)
188            OR ((recinfo.FA_EXEC_MAP_PROC is null) AND (X_FA_EXEC_MAP_PROC is null)))
189       AND ((recinfo.USER_WF_ITEM_TYPE = X_USER_WF_ITEM_TYPE)
190            OR ((recinfo.USER_WF_ITEM_TYPE is null) AND (X_USER_WF_ITEM_TYPE is null)))
191       AND ((recinfo.USER_WF_ITEM_KEY_PREFIX = X_USER_WF_ITEM_KEY_PREFIX)
192            OR ((recinfo.USER_WF_ITEM_KEY_PREFIX is null) AND (X_USER_WF_ITEM_KEY_PREFIX is null)))
193       AND ((recinfo.USER_WF_PROCESS_NAME = X_USER_WF_PROCESS_NAME)
194            OR ((recinfo.USER_WF_PROCESS_NAME is null) AND (X_USER_WF_PROCESS_NAME is null)))
195       AND ((recinfo.WF_EXEC_PROC = X_WF_EXEC_PROC)
196            OR ((recinfo.WF_EXEC_PROC is null) AND (X_WF_EXEC_PROC is null)))
197       AND ((recinfo.TIME_ESTIMATE = X_TIME_ESTIMATE)
198            OR ((recinfo.TIME_ESTIMATE is null) AND (X_TIME_ESTIMATE is null)))
199       AND (recinfo.PROTECTED_FLAG = X_PROTECTED_FLAG)
200       AND ((recinfo.ROLE_NAME = X_ROLE_NAME)
201            OR ((recinfo.ROLE_NAME is null) AND (X_ROLE_NAME is null)))
202   ) then
203     null;
204   else
205     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
206     app_exception.raise_exception;
207   end if;
208 
209   for tlinfo in c1 loop
210     if (tlinfo.BASELANG = 'Y') then
211       if (    (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
212           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
213                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
214       ) then
215         null;
216       else
217         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
218         app_exception.raise_exception;
219       end if;
220     end if;
221   end loop;
222   return;
223 end LOCK_ROW;
224 
225 procedure UPDATE_ROW (
226   X_WORKITEM_ID in NUMBER,
227   X_WORKITEM_NAME in VARCHAR2,
228   X_VERSION in VARCHAR2,
229   X_WI_TYPE_CODE in VARCHAR2,
230   X_VALID_DATE in DATE,
231   X_INVALID_DATE in DATE,
232   X_VALIDATION_ENABLED_FLAG in VARCHAR2,
233   X_VALIDATION_PROCEDURE in VARCHAR2,
234   X_FA_EXEC_MAP_PROC in VARCHAR2,
235   X_USER_WF_ITEM_TYPE in VARCHAR2,
236   X_USER_WF_ITEM_KEY_PREFIX in VARCHAR2,
237   X_USER_WF_PROCESS_NAME in VARCHAR2,
238   X_WF_EXEC_PROC in VARCHAR2,
239   X_TIME_ESTIMATE in NUMBER,
240   X_PROTECTED_FLAG in VARCHAR2,
241   X_ROLE_NAME in VARCHAR2,
242   X_DISPLAY_NAME in VARCHAR2,
243   X_DESCRIPTION in VARCHAR2,
244   X_LAST_UPDATE_DATE in DATE,
245   X_LAST_UPDATED_BY in NUMBER,
246   X_LAST_UPDATE_LOGIN in NUMBER
247 ) is
248 begin
249   update XDP_WORKITEMS set
250     WORKITEM_NAME = X_WORKITEM_NAME,
251     VERSION = X_VERSION,
252     WI_TYPE_CODE = X_WI_TYPE_CODE,
253     VALID_DATE = X_VALID_DATE,
254     INVALID_DATE = X_INVALID_DATE,
255     VALIDATION_ENABLED_FLAG = X_VALIDATION_ENABLED_FLAG,
256     VALIDATION_PROCEDURE = X_VALIDATION_PROCEDURE,
257     FA_EXEC_MAP_PROC = X_FA_EXEC_MAP_PROC,
258     USER_WF_ITEM_TYPE = X_USER_WF_ITEM_TYPE,
259     USER_WF_ITEM_KEY_PREFIX = X_USER_WF_ITEM_KEY_PREFIX,
260     USER_WF_PROCESS_NAME = X_USER_WF_PROCESS_NAME,
261     WF_EXEC_PROC = X_WF_EXEC_PROC,
262     TIME_ESTIMATE = X_TIME_ESTIMATE,
263     PROTECTED_FLAG = X_PROTECTED_FLAG,
264     ROLE_NAME = X_ROLE_NAME,
265     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
266     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
267     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
268   where WORKITEM_ID = X_WORKITEM_ID;
269 
270   if (sql%notfound) then
271     raise no_data_found;
272   end if;
273 
274   update XDP_WORKITEMS_TL set
275     DISPLAY_NAME = X_DISPLAY_NAME,
276     DESCRIPTION = X_DESCRIPTION,
277     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
278     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
279     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
280     SOURCE_LANG = userenv('LANG')
281   where WORKITEM_ID = X_WORKITEM_ID
282   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
283 
284   if (sql%notfound) then
285     raise no_data_found;
286   end if;
287 end UPDATE_ROW;
288 
289 procedure DELETE_ROW (
290   X_WORKITEM_ID in NUMBER
291 ) is
292 begin
293   delete from XDP_WORKITEMS_TL
294   where WORKITEM_ID = X_WORKITEM_ID;
295 
296   if (sql%notfound) then
297     raise no_data_found;
298   end if;
299 
300   delete from XDP_WORKITEMS
301   where WORKITEM_ID = X_WORKITEM_ID;
302 
303   if (sql%notfound) then
304     raise no_data_found;
305   end if;
306 end DELETE_ROW;
307 
308 procedure ADD_LANGUAGE
309 is
310 begin
311   delete from XDP_WORKITEMS_TL T
312   where not exists
313     (select NULL
314     from XDP_WORKITEMS B
315     where B.WORKITEM_ID = T.WORKITEM_ID
316     );
317 
318   update XDP_WORKITEMS_TL T set (
319       DISPLAY_NAME,
320       DESCRIPTION
321     ) = (select
322       B.DISPLAY_NAME,
323       B.DESCRIPTION
324     from XDP_WORKITEMS_TL B
325     where B.WORKITEM_ID = T.WORKITEM_ID
326     and B.LANGUAGE = T.SOURCE_LANG)
327   where (
328       T.WORKITEM_ID,
329       T.LANGUAGE
330   ) in (select
331       SUBT.WORKITEM_ID,
332       SUBT.LANGUAGE
333     from XDP_WORKITEMS_TL SUBB, XDP_WORKITEMS_TL SUBT
334     where SUBB.WORKITEM_ID = SUBT.WORKITEM_ID
335     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
336     and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
337       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
338       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
339       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
340   ));
341 
342   insert into XDP_WORKITEMS_TL (
343     WORKITEM_ID,
344     DISPLAY_NAME,
345     DESCRIPTION,
346     CREATED_BY,
347     CREATION_DATE,
348     LAST_UPDATED_BY,
349     LAST_UPDATE_DATE,
350     LAST_UPDATE_LOGIN,
351     LANGUAGE,
352     SOURCE_LANG
353   ) select
354     B.WORKITEM_ID,
355     B.DISPLAY_NAME,
356     B.DESCRIPTION,
357     B.CREATED_BY,
358     B.CREATION_DATE,
359     B.LAST_UPDATED_BY,
360     B.LAST_UPDATE_DATE,
361     B.LAST_UPDATE_LOGIN,
362     L.LANGUAGE_CODE,
363     B.SOURCE_LANG
364   from XDP_WORKITEMS_TL B, FND_LANGUAGES L
365   where L.INSTALLED_FLAG in ('I', 'B')
366   and B.LANGUAGE = userenv('LANG')
367   and not exists
368     (select NULL
369     from XDP_WORKITEMS_TL T
370     where T.WORKITEM_ID = B.WORKITEM_ID
371     and T.LANGUAGE = L.LANGUAGE_CODE);
372 end ADD_LANGUAGE;
373 
374 procedure LOAD_ROW (
375   X_WORKITEM_ID in NUMBER,
376   X_WORKITEM_NAME in VARCHAR2,
377   X_VERSION in VARCHAR2,
378   X_WI_TYPE_CODE in VARCHAR2,
379   X_VALID_DATE in DATE,
380   X_INVALID_DATE in DATE,
381   X_VALIDATION_ENABLED_FLAG in VARCHAR2,
382   X_VALIDATION_PROCEDURE in VARCHAR2,
383   X_FA_EXEC_MAP_PROC in VARCHAR2,
384   X_USER_WF_ITEM_TYPE in VARCHAR2,
385   X_USER_WF_ITEM_KEY_PREFIX in VARCHAR2,
386   X_USER_WF_PROCESS_NAME in VARCHAR2,
387   X_WF_EXEC_PROC in VARCHAR2,
388   X_TIME_ESTIMATE in NUMBER,
389   X_PROTECTED_FLAG in VARCHAR2,
390   X_ROLE_NAME in VARCHAR2,
391   X_DISPLAY_NAME in VARCHAR2,
392   X_DESCRIPTION in VARCHAR2,
393   X_OWNER in VARCHAR2) IS
394 begin
395 
396   declare
397      user_id            number := 0;
398      row_id             varchar2(64);
399 
400   begin
401 
402      /* The following derivation has been replaced with the FND API.		dputhiye 15-JUL-2005. R12 ATG "Seed Version by Date" Uptake */
403      --if (X_OWNER = 'SEED') then
404      --   user_id := 1;
405      --end if;
406      user_id := fnd_load_util.owner_id(X_OWNER);
407 
408      XDP_WORKITEMS_PKG.UPDATE_ROW (
409   	X_WORKITEM_ID => X_WORKITEM_ID,
410   	X_WORKITEM_NAME => X_WORKITEM_NAME,
411   	X_VERSION => X_VERSION,
412   	X_WI_TYPE_CODE => X_WI_TYPE_CODE,
413   	X_VALID_DATE => X_VALID_DATE,
414   	X_INVALID_DATE => X_INVALID_DATE,
415         X_VALIDATION_ENABLED_FLAG =>X_VALIDATION_ENABLED_FLAG,
416         X_VALIDATION_PROCEDURE => X_VALIDATION_PROCEDURE,
417   	X_FA_EXEC_MAP_PROC => X_FA_EXEC_MAP_PROC,
418   	X_USER_WF_ITEM_TYPE => X_USER_WF_ITEM_TYPE,
419   	X_USER_WF_ITEM_KEY_PREFIX => X_USER_WF_ITEM_KEY_PREFIX,
420   	X_USER_WF_PROCESS_NAME => X_USER_WF_PROCESS_NAME,
421   	X_WF_EXEC_PROC => X_WF_EXEC_PROC,
422   	X_TIME_ESTIMATE => X_TIME_ESTIMATE,
423   	X_PROTECTED_FLAG => X_PROTECTED_FLAG,
424   	X_ROLE_NAME => X_ROLE_NAME,
425   	X_DISPLAY_NAME => X_DISPLAY_NAME,
426   	X_DESCRIPTION => X_DESCRIPTION,
427         X_LAST_UPDATE_DATE => sysdate,
428         X_LAST_UPDATED_BY => user_id,
429         X_LAST_UPDATE_LOGIN => 0);
430 
431     exception
432        when NO_DATA_FOUND then
433           XDP_WORKITEMS_PKG.INSERT_ROW (
434              	X_ROWID => row_id,
435   		X_WORKITEM_ID => X_WORKITEM_ID,
436   		X_WORKITEM_NAME => X_WORKITEM_NAME,
437   		X_VERSION => X_VERSION,
438   		X_WI_TYPE_CODE => X_WI_TYPE_CODE,
439   		X_VALID_DATE => X_VALID_DATE,
440   		X_INVALID_DATE => X_INVALID_DATE,
441                 X_VALIDATION_ENABLED_FLAG =>X_VALIDATION_ENABLED_FLAG,
442                 X_VALIDATION_PROCEDURE => X_VALIDATION_PROCEDURE,
443   		X_FA_EXEC_MAP_PROC => X_FA_EXEC_MAP_PROC,
444   		X_USER_WF_ITEM_TYPE => X_USER_WF_ITEM_TYPE,
445   		X_USER_WF_ITEM_KEY_PREFIX => X_USER_WF_ITEM_KEY_PREFIX,
446   		X_USER_WF_PROCESS_NAME => X_USER_WF_PROCESS_NAME,
447   		X_WF_EXEC_PROC => X_WF_EXEC_PROC,
448   		X_TIME_ESTIMATE => X_TIME_ESTIMATE,
449   		X_PROTECTED_FLAG => X_PROTECTED_FLAG,
450   		X_ROLE_NAME => X_ROLE_NAME,
451   		X_DISPLAY_NAME => X_DISPLAY_NAME,
452   		X_DESCRIPTION => X_DESCRIPTION,
453              	X_CREATION_DATE => sysdate,
454              	X_CREATED_BY => user_id,
455              	X_LAST_UPDATE_DATE => sysdate,
456              	X_LAST_UPDATED_BY => user_id,
457              	X_LAST_UPDATE_LOGIN => 0);
458    end;
459 end LOAD_ROW;
460 
461 procedure TRANSLATE_ROW (
462    X_WORKITEM_ID in NUMBER,
463    X_DISPLAY_NAME in VARCHAR2,
464    X_DESCRIPTION in VARCHAR2,
465    X_OWNER in VARCHAR2) IS
466 
467 begin
468 
469     -- only update rows that have not been altered by user
470 
471     update XDP_WORKITEMS_TL
472     set  description = X_DESCRIPTION,
473         display_name = X_DISPLAY_NAME,
474         source_lang = userenv('LANG'),
475         last_update_date = sysdate,
476         --last_updated_by = decode(X_OWNER, 'SEED', 1, 0),		/*dputhiye 15-JUL-2005. DECODE replaced with FND API.*/
477 	last_updated_by = fnd_load_util.owner_id(X_OWNER),
478         last_update_login = 0
479   where workitem_id = X_WORKITEM_ID
480     and userenv('LANG') in (language, source_lang);
481 
482 end TRANSLATE_ROW;
483 
484 
485 end XDP_WORKITEMS_PKG;