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_WORKBENCH_ITEMS_ALL_PKG

Source


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