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

Source


1 PACKAGE BODY CN_CW_SETUP_TASKS_ALL_PKG as
2 /* $Header: cntcwstb.pls 120.0 2005/09/08 04:09 raramasa noship $ */
3 
4 procedure INSERT_ROW (
5   X_ROWID in out nocopy VARCHAR2,
6   X_SETUP_TASK_CODE in VARCHAR2,
7   X_WORKBENCH_ITEM_CODE in VARCHAR2,
8   X_SETUP_TASK_SEQUENCE in NUMBER,
9   X_SETUP_TASK_STATUS in VARCHAR2,
10   X_SETUP_TASK_TYPE in VARCHAR2,
11   X_SETUP_TASK_ACTION in VARCHAR2,
12   X_OBJECT_VERSION_NUMBER in NUMBER,
13   X_SETUP_TASK_NAME in VARCHAR2,
14   X_SETUP_TASK_DESCRIPTION in VARCHAR2,
15   X_CREATION_DATE in DATE,
16   X_CREATED_BY in NUMBER,
17   X_LAST_UPDATE_DATE in DATE,
18   X_LAST_UPDATED_BY in NUMBER,
19   X_LAST_UPDATE_LOGIN in NUMBER,
20   X_ORG_ID  in  NUMBER
21 ) is
22   cursor C is select ROWID from CN_CW_SETUP_TASKS_ALL_B
23     where SETUP_TASK_CODE = X_SETUP_TASK_CODE
24     AND ORG_ID  = X_ORG_ID
25     ;
26 begin
27   insert into CN_CW_SETUP_TASKS_ALL_B (
28     SETUP_TASK_CODE,
29     WORKBENCH_ITEM_CODE,
30     SETUP_TASK_SEQUENCE,
31     SETUP_TASK_STATUS,
32     SETUP_TASK_TYPE,
33     SETUP_TASK_ACTION,
34     OBJECT_VERSION_NUMBER,
35     CREATION_DATE,
36     CREATED_BY,
37     LAST_UPDATE_DATE,
38     LAST_UPDATED_BY,
39     LAST_UPDATE_LOGIN,
40     ORG_ID
41   ) values (
42     X_SETUP_TASK_CODE,
43     X_WORKBENCH_ITEM_CODE,
44     X_SETUP_TASK_SEQUENCE,
45     X_SETUP_TASK_STATUS,
46     X_SETUP_TASK_TYPE,
47     X_SETUP_TASK_ACTION,
48     X_OBJECT_VERSION_NUMBER,
49     X_CREATION_DATE,
50     X_CREATED_BY,
51     X_LAST_UPDATE_DATE,
52     X_LAST_UPDATED_BY,
53     X_LAST_UPDATE_LOGIN,
54     X_ORG_ID
55   );
56 
57   insert into CN_CW_SETUP_TASKS_ALL_TL (
58     SETUP_TASK_CODE,
59     SETUP_TASK_NAME,
60     SETUP_TASK_DESCRIPTION,
61     LAST_UPDATE_DATE,
62     LAST_UPDATED_BY,
63     LAST_UPDATE_LOGIN,
64     CREATED_BY,
65     CREATION_DATE,
66     LANGUAGE,
67     SOURCE_LANG,
68     ORG_ID
69   ) select
70     X_SETUP_TASK_CODE,
71     X_SETUP_TASK_NAME,
72     X_SETUP_TASK_DESCRIPTION,
73     X_LAST_UPDATE_DATE,
74     X_LAST_UPDATED_BY,
75     X_LAST_UPDATE_LOGIN,
76     X_CREATED_BY,
77     X_CREATION_DATE,
78     L.LANGUAGE_CODE,
79     userenv('LANG'),
80     X_ORG_ID
81   from FND_LANGUAGES L
82   where L.INSTALLED_FLAG in ('I', 'B')
83   and not exists
84     (select NULL
85     from CN_CW_SETUP_TASKS_ALL_TL T
86     where T.SETUP_TASK_CODE = X_SETUP_TASK_CODE
87     and T.ORG_ID    = X_ORG_ID
88     and T.LANGUAGE = L.LANGUAGE_CODE);
89 
90   open c;
91   fetch c into X_ROWID;
92   if (c%notfound) then
93     close c;
94     raise no_data_found;
95   end if;
96   close c;
97 
98 end INSERT_ROW;
99 
100 procedure LOCK_ROW (
101   X_SETUP_TASK_CODE in VARCHAR2,
102   X_WORKBENCH_ITEM_CODE in VARCHAR2,
103   X_SETUP_TASK_SEQUENCE in NUMBER,
104   X_SETUP_TASK_STATUS in VARCHAR2,
105   X_SETUP_TASK_TYPE in VARCHAR2,
106   X_SETUP_TASK_ACTION in VARCHAR2,
107   X_OBJECT_VERSION_NUMBER in NUMBER,
108   X_SETUP_TASK_NAME in VARCHAR2,
109   X_SETUP_TASK_DESCRIPTION in VARCHAR2,
110   X_ORG_ID  in  NUMBER
111 ) is
112   cursor c is select
113       WORKBENCH_ITEM_CODE,
114       SETUP_TASK_SEQUENCE,
115       SETUP_TASK_STATUS,
116       SETUP_TASK_TYPE,
117       SETUP_TASK_ACTION,
118       OBJECT_VERSION_NUMBER
119     from CN_CW_SETUP_TASKS_ALL_B
120     where SETUP_TASK_CODE = X_SETUP_TASK_CODE
121     and ORG_ID  = X_ORG_ID
122     for update of SETUP_TASK_CODE nowait;
123   recinfo c%rowtype;
124 
125   cursor c1 is select
126       SETUP_TASK_NAME,
127       SETUP_TASK_DESCRIPTION,
128       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
129     from CN_CW_SETUP_TASKS_ALL_TL
130     where SETUP_TASK_CODE = X_SETUP_TASK_CODE
131     and ORG_ID  = X_ORG_ID
132     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
133     for update of SETUP_TASK_CODE nowait;
134 begin
135   open c;
136   fetch c into recinfo;
137   if (c%notfound) then
138     close c;
139     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
140     app_exception.raise_exception;
141   end if;
142   close c;
143   if (    (recinfo.WORKBENCH_ITEM_CODE = X_WORKBENCH_ITEM_CODE)
144       AND (recinfo.SETUP_TASK_SEQUENCE = X_SETUP_TASK_SEQUENCE)
145       AND (recinfo.SETUP_TASK_STATUS = X_SETUP_TASK_STATUS)
146       AND ((recinfo.SETUP_TASK_TYPE = X_SETUP_TASK_TYPE)
147            OR ((recinfo.SETUP_TASK_TYPE is null) AND (X_SETUP_TASK_TYPE is null)))
148       AND ((recinfo.SETUP_TASK_ACTION = X_SETUP_TASK_ACTION)
149            OR ((recinfo.SETUP_TASK_ACTION is null) AND (X_SETUP_TASK_ACTION is null)))
150       AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
151            OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
152   ) then
153     null;
154   else
155     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156     app_exception.raise_exception;
157   end if;
158 
159   for tlinfo in c1 loop
160     if (tlinfo.BASELANG = 'Y') then
161       if (    (tlinfo.SETUP_TASK_NAME = X_SETUP_TASK_NAME)
162           AND (tlinfo.SETUP_TASK_DESCRIPTION = X_SETUP_TASK_DESCRIPTION)
163       ) then
164         null;
165       else
166         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
167         app_exception.raise_exception;
168       end if;
169     end if;
170   end loop;
171   return;
172 end LOCK_ROW;
173 
174 procedure UPDATE_ROW (
175   X_SETUP_TASK_CODE in VARCHAR2,
176   X_WORKBENCH_ITEM_CODE in VARCHAR2,
177   X_SETUP_TASK_SEQUENCE in NUMBER,
178   X_SETUP_TASK_STATUS in VARCHAR2,
179   X_SETUP_TASK_TYPE in VARCHAR2,
180   X_SETUP_TASK_ACTION in VARCHAR2,
181   X_OBJECT_VERSION_NUMBER in NUMBER,
182   X_SETUP_TASK_NAME in VARCHAR2,
183   X_SETUP_TASK_DESCRIPTION in VARCHAR2,
184   X_LAST_UPDATE_DATE in DATE,
185   X_LAST_UPDATED_BY in NUMBER,
186   X_LAST_UPDATE_LOGIN in NUMBER,
187   X_ORG_ID  in  NUMBER
188 ) is
189 begin
190   update CN_CW_SETUP_TASKS_ALL_B set
191     WORKBENCH_ITEM_CODE = X_WORKBENCH_ITEM_CODE,
192     SETUP_TASK_SEQUENCE = X_SETUP_TASK_SEQUENCE,
193     SETUP_TASK_STATUS = X_SETUP_TASK_STATUS,
194     SETUP_TASK_TYPE = X_SETUP_TASK_TYPE,
195     SETUP_TASK_ACTION = X_SETUP_TASK_ACTION,
196     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
197     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
198     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
199     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
200   where SETUP_TASK_CODE = X_SETUP_TASK_CODE
201   and ORG_ID  = X_ORG_ID;
202 
203   if (sql%notfound) then
204     raise no_data_found;
205   end if;
206 
207   update CN_CW_SETUP_TASKS_ALL_TL set
208     SETUP_TASK_NAME = X_SETUP_TASK_NAME,
209     SETUP_TASK_DESCRIPTION = X_SETUP_TASK_DESCRIPTION,
210     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
211     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
212     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
213     SOURCE_LANG = userenv('LANG')
214   where SETUP_TASK_CODE = X_SETUP_TASK_CODE
215   and ORG_ID  = X_ORG_ID
216   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
217 
218   if (sql%notfound) then
219     raise no_data_found;
220   end if;
221 end UPDATE_ROW;
222 
223 procedure DELETE_ROW (
224   X_SETUP_TASK_CODE in VARCHAR2,
225   X_ORG_ID  in  NUMBER
226 ) is
227 begin
228   delete from CN_CW_SETUP_TASKS_ALL_TL
229   where SETUP_TASK_CODE = X_SETUP_TASK_CODE
230   and ORG_ID  = X_ORG_ID;
231 
232   if (sql%notfound) then
233     raise no_data_found;
234   end if;
238   and ORG_ID  = X_ORG_ID;
235 
236   delete from CN_CW_SETUP_TASKS_ALL_B
237   where SETUP_TASK_CODE = X_SETUP_TASK_CODE
239 
240   if (sql%notfound) then
241     raise no_data_found;
242   end if;
243 end DELETE_ROW;
244 
245 PROCEDURE TRANSLATE_ROW
246   ( X_SETUP_TASK_CODE IN VARCHAR2,
247     X_SETUP_TASK_NAME IN VARCHAR2,
248     X_SETUP_TASK_DESCRIPTION IN VARCHAR2,
249     X_OWNER IN VARCHAR2
250     ) IS
251        user_id NUMBER;
252 BEGIN
253     -- Validate input data
254    IF (X_SETUP_TASK_CODE IS NULL)
255      OR (X_SETUP_TASK_NAME IS NULL) OR (X_SETUP_TASK_DESCRIPTION IS NULL) THEN
256       GOTO end_translate_row;
257    END IF;
258 
259    IF (X_OWNER IS NOT NULL) AND (X_OWNER = 'SEED') THEN
260       user_id := 1;
261     ELSE
262       user_id := 0;
263    END IF;
264    -- Update the translation
265    UPDATE CN_CW_SETUP_TASKS_ALL_TL  SET
266    SETUP_TASK_NAME = X_SETUP_TASK_NAME,
267    SETUP_TASK_DESCRIPTION=X_SETUP_TASK_DESCRIPTION,
268    last_update_date = sysdate,
269    last_updated_by = user_id,
270    last_update_login = 0,
271    source_lang = userenv('LANG')
272    WHERE SETUP_TASK_CODE = X_SETUP_TASK_CODE
273    AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
274 
275    << end_translate_row >>
276      NULL;
277 END TRANSLATE_ROW;
278 
279 PROCEDURE LOAD_ROW
280   ( X_SETUP_TASK_CODE IN VARCHAR2,
281     X_WORKBENCH_ITEM_CODE IN VARCHAR2,
282     X_SETUP_TASK_SEQUENCE IN NUMBER,
283     X_SETUP_TASK_STATUS IN VARCHAR2,
284     X_SETUP_TASK_TYPE IN VARCHAR2,
285     X_SETUP_TASK_ACTION IN VARCHAR2,
286     X_ORG_ID IN NUMBER,
287     X_SETUP_TASK_NAME IN VARCHAR2,
288     X_SETUP_TASK_DESCRIPTION IN VARCHAR2,
289     X_OWNER IN VARCHAR2)
290  IS
291  USER_ID NUMBER;
292  BEGIN
293    IF (X_SETUP_TASK_CODE IS NULL) OR (X_SETUP_TASK_NAME IS NULL) THEN
294        GOTO end_load_row;
295    END IF;
296    IF (X_OWNER IS NOT NULL) AND (X_OWNER = 'SEED') THEN
297       USER_ID := 1;
298     ELSE
299       USER_ID := 0;
300    END IF;
301    UPDATE CN_CW_SETUP_TASKS_ALL_B SET
302     WORKBENCH_ITEM_CODE = X_WORKBENCH_ITEM_CODE,
303     SETUP_TASK_SEQUENCE = X_SETUP_TASK_SEQUENCE,
304     SETUP_TASK_STATUS =  X_SETUP_TASK_STATUS,
305     SETUP_TASK_TYPE = X_SETUP_TASK_TYPE,
306     SETUP_TASK_ACTION = X_SETUP_TASK_ACTION,
307     LAST_UPDATE_DATE = SYSDATE,
308     LAST_UPDATED_BY = USER_ID,
309     LAST_UPDATE_LOGIN = 0
310    where SETUP_TASK_CODE = X_SETUP_TASK_CODE;
311 
312    IF (SQL%NOTFOUND)  THEN
313      -- Insert new record to _B table
314     INSERT INTO CN_CW_SETUP_TASKS_ALL_B (
315     SETUP_TASK_CODE,
316     WORKBENCH_ITEM_CODE,
317     SETUP_TASK_SEQUENCE,
318     SETUP_TASK_STATUS,
319     SETUP_TASK_TYPE,
320     SETUP_TASK_ACTION,
321     OBJECT_VERSION_NUMBER,
322     CREATION_DATE,
323     CREATED_BY,
324     LAST_UPDATE_DATE,
325     LAST_UPDATED_BY,
326     LAST_UPDATE_LOGIN,
327     ORG_ID) values (
328     X_SETUP_TASK_CODE,
329     X_WORKBENCH_ITEM_CODE,
330     X_SETUP_TASK_SEQUENCE,
331     X_SETUP_TASK_STATUS,
332     X_SETUP_TASK_TYPE,
333     X_SETUP_TASK_ACTION,
334     1,
335     SYSDATE,
336     USER_ID,
337     SYSDATE,
338     USER_ID,
339     0,
340     X_ORG_ID
341   );
342   END IF;
343 
344   UPDATE CN_CW_SETUP_TASKS_ALL_TL set
345     SETUP_TASK_NAME = X_SETUP_TASK_NAME,
346     SETUP_TASK_DESCRIPTION = X_SETUP_TASK_DESCRIPTION,
347     LAST_UPDATED_BY = USER_ID,
348     LAST_UPDATE_LOGIN = 0,
349     SOURCE_LANG = userenv('LANG')
350   where SETUP_TASK_CODE = X_SETUP_TASK_CODE
351   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
352 
353   IF (SQL%NOTFOUND) THEN
354   insert into CN_CW_SETUP_TASKS_ALL_TL (
355     SETUP_TASK_CODE,
356     SETUP_TASK_NAME,
357     SETUP_TASK_DESCRIPTION,
358     LAST_UPDATE_DATE,
359     LAST_UPDATED_BY,
360     LAST_UPDATE_LOGIN,
361     CREATED_BY,
362     CREATION_DATE,
363     LANGUAGE,
364     SOURCE_LANG,
365     ORG_ID
366   ) select
367     X_SETUP_TASK_CODE,
368     X_SETUP_TASK_NAME,
369     X_SETUP_TASK_DESCRIPTION,
370     SYSDATE,
371     USER_ID,
372     0,
373     USER_ID,
374     SYSDATE,
375     L.LANGUAGE_CODE,
376     userenv('LANG'),
377     X_ORG_ID
378   from FND_LANGUAGES L
379   where L.INSTALLED_FLAG in ('I', 'B')
383     where T.SETUP_TASK_CODE = X_SETUP_TASK_CODE
380   and not exists
381     (select NULL
382     from CN_CW_SETUP_TASKS_ALL_TL T
384     and T.LANGUAGE = L.LANGUAGE_CODE AND
385     ORG_ID=X_ORG_ID);
386   END IF;
387 
388 << end_load_row >>
389 NULL;
390 END LOAD_ROW;
391 
392 
393 procedure ADD_LANGUAGE
394 is
395 begin
396   delete from CN_CW_SETUP_TASKS_ALL_TL T
397   where not exists
398     (select NULL
399     from CN_CW_SETUP_TASKS_ALL_B B
400     where B.SETUP_TASK_CODE = T.SETUP_TASK_CODE
401     and B.ORG_ID  = T.ORG_ID
402     );
403 
404   update CN_CW_SETUP_TASKS_ALL_TL T set (
405       SETUP_TASK_NAME,
406       SETUP_TASK_DESCRIPTION
407     ) = (select
408       B.SETUP_TASK_NAME,
409       B.SETUP_TASK_DESCRIPTION
410     from CN_CW_SETUP_TASKS_ALL_TL B
411     where B.SETUP_TASK_CODE = T.SETUP_TASK_CODE
412     and ORG_ID  = T.ORG_ID
413     and B.LANGUAGE = T.SOURCE_LANG)
414   where (
415       T.SETUP_TASK_CODE,
416       T.ORG_ID,
417       T.LANGUAGE
418   ) in (select
419       SUBT.SETUP_TASK_CODE,
420       SUBT.ORG_ID,
421       SUBT.LANGUAGE
422     from CN_CW_SETUP_TASKS_ALL_TL SUBB, CN_CW_SETUP_TASKS_ALL_TL SUBT
423     where SUBB.SETUP_TASK_CODE = SUBT.SETUP_TASK_CODE
424     and SUBB.ORG_ID = SUBT.ORG_ID
425     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
426     and (SUBB.SETUP_TASK_NAME <> SUBT.SETUP_TASK_NAME
427       or SUBB.SETUP_TASK_DESCRIPTION <> SUBT.SETUP_TASK_DESCRIPTION
428   ));
429 
430   insert into CN_CW_SETUP_TASKS_ALL_TL (
431     SETUP_TASK_CODE,
432     SETUP_TASK_NAME,
433     SETUP_TASK_DESCRIPTION,
434     LAST_UPDATE_DATE,
435     LAST_UPDATED_BY,
436     LAST_UPDATE_LOGIN,
437     CREATED_BY,
438     CREATION_DATE,
439     LANGUAGE,
440     SOURCE_LANG,
441     ORG_ID
442   ) select /*+ ORDERED */
443     B.SETUP_TASK_CODE,
444     B.SETUP_TASK_NAME,
445     B.SETUP_TASK_DESCRIPTION,
446     B.LAST_UPDATE_DATE,
447     B.LAST_UPDATED_BY,
448     B.LAST_UPDATE_LOGIN,
449     B.CREATED_BY,
450     B.CREATION_DATE,
451     L.LANGUAGE_CODE,
452     B.SOURCE_LANG,
453     B.ORG_ID
454   from CN_CW_SETUP_TASKS_ALL_TL B, FND_LANGUAGES L
455   where L.INSTALLED_FLAG in ('I', 'B')
456   and B.LANGUAGE = userenv('LANG')
457   and not exists
458     (select NULL
459     from CN_CW_SETUP_TASKS_ALL_TL T
460     where T.SETUP_TASK_CODE = B.SETUP_TASK_CODE
461       and T.ORG_ID = B.ORG_ID
462     and T.LANGUAGE = L.LANGUAGE_CODE);
463 end ADD_LANGUAGE;
464 
465 PROCEDURE LOAD_SEED_ROW (
466 x_upload_mode in varchar2,
467 x_owner  in varchar2,
468 x_setup_task_code  in varchar2,
469 x_setup_task_name  in varchar2,
470 x_setup_task_description  in varchar2,
471 x_workbench_item_code  in varchar2,
472 x_setup_task_sequence  in varchar2,
473 x_setup_task_status  in varchar2,
474 x_setup_task_type  in varchar2,
475 x_setup_task_action  in varchar2,
476 x_org_id  in varchar2
477 )
478 IS
479 BEGIN
480      if (x_upload_mode = 'NLS') then
481        cn_cw_setup_tasks_all_pkg.translate_row
482         	(x_setup_task_code,
483     	     x_setup_task_name,
484         	 x_setup_task_description,
485         	 x_owner);
486      else
487        cn_cw_setup_tasks_all_pkg.load_row
488 				(x_setup_task_code,
489 				x_workbench_item_code,
490 				to_number(x_setup_task_sequence),
491 				x_setup_task_status,
492 				x_setup_task_type,
493 				x_setup_task_action,
494 				to_number(x_org_id),
495 				x_setup_task_name,
496 				x_setup_task_description,
497 				x_owner);
498      end if;
499 END LOAD_SEED_ROW;
500 
501 
502 end CN_CW_SETUP_TASKS_ALL_PKG;