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

Source


1 package body AMV_C_CATEGORIES_PKG as
2 /* $Header: amvtcatb.pls 120.2 2006/02/03 16:12:29 mkettle ship $ */
3 procedure LOAD_ROW (
4   X_CHANNEL_CATEGORY_ID in VARCHAR2,
5   X_OBJECT_VERSION_NUMBER in VARCHAR2,
6   X_APPLICATION_ID in VARCHAR2,
7   X_CHANNEL_CATEGORY_ORDER in VARCHAR2,
8   X_PARENT_CHANNEL_CATEGORY_ID in VARCHAR2,
9   X_CHANNEL_COUNT in VARCHAR2,
10   X_CHANNEL_CATEGORY_NAME in VARCHAR2,
11   X_DESCRIPTION in VARCHAR2,
12   X_OWNER in VARCHAR2)
13 is
14 l_user_id          	number := 0;
15 l_application_id   	number := 0;
16 l_channel_category_id   number := 0;
17 l_channel_category_order   number := 0;
18 l_parent_channel_category_id   number := 0;
19 l_channel_count   number := 0;
20 l_object_version_number number := 0;
21 l_row_id           	varchar2(64);
22 
23  CURSOR Check_Lub (v_ch_cat_id NUMBER) IS
24   select last_updated_by
25   from AMV_C_CATEGORIES_B
26   where channel_category_id = v_ch_cat_id;
27 
28  l_lub NUMBER;
29  l_upd VARCHAR2(5);
30 
31 
32 begin
33 	if (X_OWNER = 'SEED') then
34 		l_user_id := 1;
35 	end if;
36 	l_channel_category_id := to_number(x_channel_category_id);
37 	l_application_id := to_number(x_application_id);
38 	l_object_version_number := to_number(x_object_version_number);
39 	l_channel_category_order := to_number(x_channel_category_order);
40 	l_parent_channel_category_id := to_number(x_parent_channel_category_id);
41 	l_channel_count := to_number(x_channel_count);
42 
43     l_lub := null;
44     l_upd := 'TRUE';
45     OPEN  Check_Lub(x_channel_category_id);
46     FETCH Check_Lub INTO l_lub;
47     CLOSE Check_Lub;
48     IF l_lub IS NOT NULL AND l_lub > 1000 THEN
49       -- Row already exists in Customer Env
50       -- and has been Customized
51       l_upd := 'FALSE';
52     END IF;
53 
54     IF l_upd = 'TRUE' THEN
55 
56       AMV_C_CATEGORIES_PKG.UPDATE_ROW (
57 	   	X_CHANNEL_CATEGORY_ID   => l_channel_category_id,
58   		X_APPLICATION_ID => l_application_id,
59 	   	X_OBJECT_VERSION_NUMBER => l_object_version_number,
60   		X_CHANNEL_CATEGORY_ORDER => l_channel_category_order,
61   		X_PARENT_CHANNEL_CATEGORY_ID => l_parent_channel_category_id,
62   		X_CHANNEL_COUNT => l_channel_count,
63 	  	X_CHANNEL_CATEGORY_NAME => x_channel_category_name,
64 		X_DESCRIPTION       => x_description,
65 		X_LAST_UPDATE_DATE  => sysdate,
66 		X_LAST_UPDATED_BY   => l_user_id,
67 		X_LAST_UPDATE_LOGIN => 0
68 		);
69     END IF;
70 
71 exception
72 	when NO_DATA_FOUND then
73 		AMV_C_CATEGORIES_PKG.INSERT_ROW (
74 			X_ROWID             => l_row_id,
75 			X_CHANNEL_CATEGORY_ID   => l_channel_category_id,
76   			X_APPLICATION_ID => l_application_id,
77 			X_OBJECT_VERSION_NUMBER => l_object_version_number,
78   			X_CHANNEL_CATEGORY_ORDER => l_channel_category_order,
79   			X_PARENT_CHANNEL_CATEGORY_ID => l_parent_channel_category_id,
80   			X_CHANNEL_COUNT => l_channel_count,
81 			X_CHANNEL_CATEGORY_NAME => x_channel_category_name,
82 			X_DESCRIPTION       => x_description,
83 			X_CREATION_DATE     => sysdate,
84 			X_CREATED_BY        => l_user_id,
85 			X_LAST_UPDATE_DATE  => sysdate,
86 			X_LAST_UPDATED_BY   => l_user_id,
87 			X_LAST_UPDATE_LOGIN => 0
88 			);
89 end LOAD_ROW;
90 
91 procedure TRANSLATE_ROW (
92   X_CHANNEL_CATEGORY_ID in NUMBER,
93   X_CHANNEL_CATEGORY_NAME in VARCHAR2,
94   X_DESCRIPTION in VARCHAR2,
95   X_OWNER in VARCHAR2)
96 is
97 begin
98 	update AMV_C_CATEGORIES_TL set
99 		CHANNEL_CATEGORY_NAME = x_channel_category_name,
100 		DESCRIPTION       = x_description,
101 		LAST_UPDATE_DATE  = sysdate,
102 		LAST_UPDATED_BY   = decode(x_owner, 'SEED', 1, 0),
103 		LAST_UPDATE_LOGIN = 0,
104 	SOURCE_LANG = userenv('LANG')
105 	where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
106 	and CHANNEL_CATEGORY_ID = x_channel_category_id
107 	and last_updated_by < 1000;
108 end TRANSLATE_ROW;
109 
110 procedure INSERT_ROW (
111   X_ROWID in out NOCOPY VARCHAR2,
112   X_CHANNEL_CATEGORY_ID in NUMBER,
113   X_APPLICATION_ID in NUMBER,
114   X_OBJECT_VERSION_NUMBER in NUMBER,
115   X_CHANNEL_CATEGORY_ORDER in NUMBER,
116   X_PARENT_CHANNEL_CATEGORY_ID in NUMBER,
117   X_CHANNEL_COUNT in NUMBER,
118   X_CHANNEL_CATEGORY_NAME in VARCHAR2,
119   X_DESCRIPTION in VARCHAR2,
120   X_CREATION_DATE in DATE,
121   X_CREATED_BY in NUMBER,
122   X_LAST_UPDATE_DATE in DATE,
123   X_LAST_UPDATED_BY in NUMBER,
124   X_LAST_UPDATE_LOGIN in NUMBER
125 ) is
126   cursor C is select ROWID from AMV_C_CATEGORIES_B
127     where CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID
128     ;
129 begin
130   insert into AMV_C_CATEGORIES_B (
131     APPLICATION_ID,
132     CHANNEL_CATEGORY_ID,
133     OBJECT_VERSION_NUMBER,
134     CHANNEL_CATEGORY_ORDER,
135     PARENT_CHANNEL_CATEGORY_ID,
136     CHANNEL_COUNT,
137     CREATION_DATE,
138     CREATED_BY,
139     LAST_UPDATE_DATE,
140     LAST_UPDATED_BY,
141     LAST_UPDATE_LOGIN
142   ) values (
143     X_APPLICATION_ID,
144     X_CHANNEL_CATEGORY_ID,
145     X_OBJECT_VERSION_NUMBER,
146     X_CHANNEL_CATEGORY_ORDER,
147     X_PARENT_CHANNEL_CATEGORY_ID,
148     X_CHANNEL_COUNT,
149     X_CREATION_DATE,
150     X_CREATED_BY,
151     X_LAST_UPDATE_DATE,
152     X_LAST_UPDATED_BY,
153     X_LAST_UPDATE_LOGIN
154   );
155 
156   insert into AMV_C_CATEGORIES_TL (
157     CHANNEL_CATEGORY_ID,
158     LAST_UPDATE_DATE,
159     LAST_UPDATED_BY,
160     CREATION_DATE,
161     CREATED_BY,
162     LAST_UPDATE_LOGIN,
163     CHANNEL_CATEGORY_NAME,
164     DESCRIPTION,
165     LANGUAGE,
166     SOURCE_LANG
167   ) select
168     X_CHANNEL_CATEGORY_ID,
169     X_LAST_UPDATE_DATE,
170     X_LAST_UPDATED_BY,
171     X_CREATION_DATE,
172     X_CREATED_BY,
173     X_LAST_UPDATE_LOGIN,
174     X_CHANNEL_CATEGORY_NAME,
175     X_DESCRIPTION,
176     L.LANGUAGE_CODE,
177     userenv('LANG')
178   from FND_LANGUAGES L
179   where L.INSTALLED_FLAG in ('I', 'B')
180   and not exists
181     (select NULL
182     from AMV_C_CATEGORIES_TL T
183     where T.CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID
184     and T.LANGUAGE = L.LANGUAGE_CODE);
185 
186   open c;
187   fetch c into X_ROWID;
188   if (c%notfound) then
189     close c;
190     raise no_data_found;
191   end if;
192   close c;
193 
194 end INSERT_ROW;
195 
196 procedure LOCK_ROW (
197   X_CHANNEL_CATEGORY_ID in NUMBER,
198   X_APPLICATION_ID in NUMBER,
199   X_OBJECT_VERSION_NUMBER in NUMBER,
200   X_CHANNEL_CATEGORY_ORDER in NUMBER,
201   X_PARENT_CHANNEL_CATEGORY_ID in NUMBER,
202   X_CHANNEL_COUNT in NUMBER,
203   X_CHANNEL_CATEGORY_NAME in VARCHAR2,
204   X_DESCRIPTION in VARCHAR2
205 ) is
206   cursor c is select
207       APPLICATION_ID,
208       OBJECT_VERSION_NUMBER,
209       CHANNEL_CATEGORY_ORDER,
210       PARENT_CHANNEL_CATEGORY_ID,
211       CHANNEL_COUNT
212     from AMV_C_CATEGORIES_B
213     where CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID
214     for update of CHANNEL_CATEGORY_ID nowait;
215   recinfo c%rowtype;
216 
217   cursor c1 is select
218       CHANNEL_CATEGORY_NAME,
219       DESCRIPTION,
220       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
221     from AMV_C_CATEGORIES_TL
222     where CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID
223     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
224     for update of CHANNEL_CATEGORY_ID nowait;
225 begin
226   open c;
227   fetch c into recinfo;
228   if (c%notfound) then
229     close c;
230     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
231     app_exception.raise_exception;
232   end if;
233   close c;
234   if (    ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
235            OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
236       AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
237       AND (recinfo.CHANNEL_CATEGORY_ORDER = X_CHANNEL_CATEGORY_ORDER)
238       AND ((recinfo.PARENT_CHANNEL_CATEGORY_ID = X_PARENT_CHANNEL_CATEGORY_ID)
239            OR ((recinfo.PARENT_CHANNEL_CATEGORY_ID is null) AND (X_PARENT_CHANNEL_CATEGORY_ID is null)))
240       AND ((recinfo.CHANNEL_COUNT = X_CHANNEL_COUNT)
241            OR ((recinfo.CHANNEL_COUNT is null) AND (X_CHANNEL_COUNT is null)))
242   ) then
243     null;
244   else
245     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
246     app_exception.raise_exception;
247   end if;
248 
249   for tlinfo in c1 loop
250     if (tlinfo.BASELANG = 'Y') then
251       if (    (tlinfo.CHANNEL_CATEGORY_NAME = X_CHANNEL_CATEGORY_NAME)
252           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
253                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
254       ) then
255         null;
256       else
257         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
258         app_exception.raise_exception;
259       end if;
260     end if;
261   end loop;
262   return;
263 end LOCK_ROW;
264 
265 procedure UPDATE_B_ROW (
266   X_CHANNEL_CATEGORY_ID in NUMBER,
267   X_APPLICATION_ID in NUMBER,
268   X_OBJECT_VERSION_NUMBER in NUMBER,
269   X_CHANNEL_CATEGORY_ORDER in NUMBER,
270   X_PARENT_CHANNEL_CATEGORY_ID in NUMBER,
271   X_CHANNEL_COUNT in NUMBER,
272   X_LAST_UPDATE_DATE in DATE,
273   X_LAST_UPDATED_BY in NUMBER,
274   X_LAST_UPDATE_LOGIN in NUMBER
275 ) is
276 begin
277   update AMV_C_CATEGORIES_B set
278     APPLICATION_ID = X_APPLICATION_ID,
279     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
280     CHANNEL_CATEGORY_ORDER = X_CHANNEL_CATEGORY_ORDER,
281     PARENT_CHANNEL_CATEGORY_ID = X_PARENT_CHANNEL_CATEGORY_ID,
282     CHANNEL_COUNT = X_CHANNEL_COUNT,
283     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
284     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
285     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
286   where CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID;
287 
288   if (sql%notfound) then
289     raise no_data_found;
290   end if;
291 
292 end UPDATE_B_ROW;
293 
294 procedure UPDATE_TL_ROW (
295   X_CHANNEL_CATEGORY_ID in NUMBER,
296   X_CHANNEL_CATEGORY_NAME in VARCHAR2,
297   X_DESCRIPTION in VARCHAR2,
298   X_LAST_UPDATE_DATE in DATE,
299   X_LAST_UPDATED_BY in NUMBER,
300   X_LAST_UPDATE_LOGIN in NUMBER
301 ) is
302 begin
303 
304   update AMV_C_CATEGORIES_TL set
305     CHANNEL_CATEGORY_NAME = X_CHANNEL_CATEGORY_NAME,
306     DESCRIPTION = X_DESCRIPTION,
307     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
308     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
309     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
310     SOURCE_LANG = userenv('LANG')
311   where CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID
312   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
313 
314   if (sql%notfound) then
315     raise no_data_found;
316   end if;
317 end UPDATE_TL_ROW;
318 
319 procedure UPDATE_ROW (
320   X_CHANNEL_CATEGORY_ID in NUMBER,
321   X_APPLICATION_ID in NUMBER,
322   X_OBJECT_VERSION_NUMBER in NUMBER,
323   X_CHANNEL_CATEGORY_ORDER in NUMBER,
324   X_PARENT_CHANNEL_CATEGORY_ID in NUMBER,
325   X_CHANNEL_COUNT in NUMBER,
326   X_CHANNEL_CATEGORY_NAME in VARCHAR2,
327   X_DESCRIPTION in VARCHAR2,
328   X_LAST_UPDATE_DATE in DATE,
329   X_LAST_UPDATED_BY in NUMBER,
330   X_LAST_UPDATE_LOGIN in NUMBER
331 ) is
332 begin
333   update AMV_C_CATEGORIES_B set
334     APPLICATION_ID = X_APPLICATION_ID,
335     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
339     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
336     CHANNEL_CATEGORY_ORDER = X_CHANNEL_CATEGORY_ORDER,
337     PARENT_CHANNEL_CATEGORY_ID = X_PARENT_CHANNEL_CATEGORY_ID,
338     CHANNEL_COUNT = X_CHANNEL_COUNT,
340     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
341     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
342   where CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID;
343 
344   if (sql%notfound) then
345     raise no_data_found;
346   end if;
347 
348   update AMV_C_CATEGORIES_TL set
349     CHANNEL_CATEGORY_NAME = X_CHANNEL_CATEGORY_NAME,
350     DESCRIPTION = X_DESCRIPTION,
351     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
352     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
353     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
354     SOURCE_LANG = userenv('LANG')
355   where CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID
356   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
357 
358   if (sql%notfound) then
359     raise no_data_found;
360   end if;
361 end UPDATE_ROW;
362 
363 procedure DELETE_ROW (
364   X_CHANNEL_CATEGORY_ID in NUMBER
365 ) is
366 begin
367   delete from AMV_C_CATEGORIES_TL
368   where CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID;
369 
370   if (sql%notfound) then
371     raise no_data_found;
372   end if;
373 
374   delete from AMV_C_CATEGORIES_B
375   where CHANNEL_CATEGORY_ID = X_CHANNEL_CATEGORY_ID;
376 
377   if (sql%notfound) then
378     raise no_data_found;
379   end if;
380 
381 end DELETE_ROW;
382 
383 procedure ADD_LANGUAGE
384 is
385 begin
386   delete from AMV_C_CATEGORIES_TL T
387   where not exists
388     (select NULL
389     from AMV_C_CATEGORIES_B B
390     where B.CHANNEL_CATEGORY_ID = T.CHANNEL_CATEGORY_ID
391     );
392 
393   update AMV_C_CATEGORIES_TL T set (
394       CHANNEL_CATEGORY_NAME,
395       DESCRIPTION
396     ) = (select
397       B.CHANNEL_CATEGORY_NAME,
398       B.DESCRIPTION
399     from AMV_C_CATEGORIES_TL B
400     where B.CHANNEL_CATEGORY_ID = T.CHANNEL_CATEGORY_ID
401     and B.LANGUAGE = T.SOURCE_LANG)
402   where (
403       T.CHANNEL_CATEGORY_ID,
404       T.LANGUAGE
405   ) in (select
406       SUBT.CHANNEL_CATEGORY_ID,
407       SUBT.LANGUAGE
408     from AMV_C_CATEGORIES_TL SUBB, AMV_C_CATEGORIES_TL SUBT
409     where SUBB.CHANNEL_CATEGORY_ID = SUBT.CHANNEL_CATEGORY_ID
410     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
411     and (SUBB.CHANNEL_CATEGORY_NAME <> SUBT.CHANNEL_CATEGORY_NAME
412       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
413       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
414       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
415   ));
416 
417   insert into AMV_C_CATEGORIES_TL (
418     CHANNEL_CATEGORY_ID,
419     LAST_UPDATE_DATE,
420     LAST_UPDATED_BY,
421     CREATION_DATE,
422     CREATED_BY,
423     LAST_UPDATE_LOGIN,
424     CHANNEL_CATEGORY_NAME,
425     DESCRIPTION,
426     LANGUAGE,
427     SOURCE_LANG
428   ) select
429     B.CHANNEL_CATEGORY_ID,
430     B.LAST_UPDATE_DATE,
431     B.LAST_UPDATED_BY,
432     B.CREATION_DATE,
433     B.CREATED_BY,
434     B.LAST_UPDATE_LOGIN,
435     B.CHANNEL_CATEGORY_NAME,
436     B.DESCRIPTION,
437     L.LANGUAGE_CODE,
438     B.SOURCE_LANG
439   from AMV_C_CATEGORIES_TL B, FND_LANGUAGES L
440   where L.INSTALLED_FLAG in ('I', 'B')
441   and B.LANGUAGE = userenv('LANG')
442   and not exists
443     (select NULL
444     from AMV_C_CATEGORIES_TL T
445     where T.CHANNEL_CATEGORY_ID = B.CHANNEL_CATEGORY_ID
446     and T.LANGUAGE = L.LANGUAGE_CODE);
447 end ADD_LANGUAGE;
448 
449 end AMV_C_CATEGORIES_PKG;