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_SERVICE_VAL_ACTS_PKG

Source


1 package body XDP_SERVICE_VAL_ACTS_PKG as
2 /* $Header: XDPSVACB.pls 120.1 2005/06/16 02:39:41 appldev  $ */
3 procedure INSERT_ROW (
4   X_ROWID                   in OUT NOCOPY VARCHAR2,
5   X_SERVICE_VAL_ACT_ID      in NUMBER,
6   X_ACTION_SOURCE           in VARCHAR2,
7   X_ACTION_CODE             in VARCHAR2,
8   X_ORGANIZATION_ID         in NUMBER ,
9   X_INVENTORY_ITEM_ID       in NUMBER,
10   X_VALID_DATE              in DATE,
11   X_INVALID_DATE            in DATE,
12   X_WI_MAPPING_PROC         in VARCHAR2,
13   X_WI_MAPPING_TYPE         in VARCHAR2,
14   X_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 ) is
21   cursor C is select ROWID from XDP_SERVICE_VAL_ACTS
22     where SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID
23     ;
24 begin
25   insert into XDP_SERVICE_VAL_ACTS (
26     SERVICE_VAL_ACT_ID,
27     VALID_DATE,
28     INVALID_DATE,
29     WI_MAPPING_PROC,
30     WI_MAPPING_TYPE,
31     ORGANIZATION_ID ,
32     INVENTORY_ITEM_ID ,
33     ACTION_SOURCE,
34     ACTION_CODE,
35     CREATION_DATE,
36     CREATED_BY,
37     LAST_UPDATE_DATE,
38     LAST_UPDATED_BY,
39     LAST_UPDATE_LOGIN
40   ) values (
41     X_SERVICE_VAL_ACT_ID,
42     X_VALID_DATE,
43     X_INVALID_DATE,
44     X_WI_MAPPING_PROC,
45     X_WI_MAPPING_TYPE,
46     X_ORGANIZATION_ID ,
47     X_INVENTORY_ITEM_ID ,
48     X_ACTION_SOURCE,
49     X_ACTION_CODE,
50     X_CREATION_DATE,
51     X_CREATED_BY,
52     X_LAST_UPDATE_DATE,
53     X_LAST_UPDATED_BY,
54     X_LAST_UPDATE_LOGIN
55   );
56 
57   insert into XDP_SERVICE_VAL_ACTS_TL (
58     SERVICE_VAL_ACT_ID,
59     DESCRIPTION,
60     CREATED_BY,
61     CREATION_DATE,
62     LAST_UPDATED_BY,
63     LAST_UPDATE_DATE,
64     LAST_UPDATE_LOGIN,
65     LANGUAGE,
66     SOURCE_LANG
67   ) select
68     X_SERVICE_VAL_ACT_ID,
69     X_DESCRIPTION,
70     X_CREATED_BY,
71     X_CREATION_DATE,
72     X_LAST_UPDATED_BY,
73     X_LAST_UPDATE_DATE,
74     X_LAST_UPDATE_LOGIN,
75     L.LANGUAGE_CODE,
76     userenv('LANG')
77   from FND_LANGUAGES L
78   where L.INSTALLED_FLAG in ('I', 'B')
79   and not exists
80     (select NULL
81     from XDP_SERVICE_VAL_ACTS_TL T
82     where T.SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID
83     and T.LANGUAGE = L.LANGUAGE_CODE);
84 
85   open c;
86   fetch c into X_ROWID;
87   if (c%notfound) then
88     close c;
89     raise no_data_found;
90   end if;
91   close c;
92 exception
93     when no_data_found then
94          raise_application_error(-20001,'No data found1 : '||sqlcode||' - '||sqlerrm);
95     when others then
96          raise_application_error(-20001,'others : '||sqlcode||' - '||sqlerrm);
97 end INSERT_ROW;
98 
99 procedure LOCK_ROW (
100   X_SERVICE_VAL_ACT_ID in NUMBER,
101   X_ACTION_SOURCE in VARCHAR2,
102   X_ACTION_CODE  in VARCHAR2,
103   X_ORGANIZATION_ID in NUMBER,
104   X_INVENTORY_ITEM_ID in NUMBER,
105   X_VALID_DATE in DATE,
106   X_INVALID_DATE in DATE,
107   X_WI_MAPPING_PROC in VARCHAR2,
108   X_WI_MAPPING_TYPE in VARCHAR2,
109   X_DESCRIPTION in VARCHAR2
110 ) is
111   cursor c is select
112       ORGANIZATION_ID,
113       INVENTORY_ITEM_ID ,
114       ACTION_SOURCE,
115       ACTION_CODE,
116       VALID_DATE,
117       INVALID_DATE,
118       WI_MAPPING_PROC,
119       WI_MAPPING_TYPE
120     from XDP_SERVICE_VAL_ACTS
121     where SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID
122     for update of SERVICE_VAL_ACT_ID nowait;
123   recinfo c%rowtype;
124 
125   cursor c1 is select
126       DESCRIPTION,
127       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
128     from XDP_SERVICE_VAL_ACTS_TL
129     where SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID
130     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
131     for update of SERVICE_VAL_ACT_ID nowait;
132 begin
133   open c;
134   fetch c into recinfo;
135   if (c%notfound) then
136     close c;
137     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
138     app_exception.raise_exception;
139   end if;
140   close c;
141   if (
142           (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
143       AND (recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
144       AND (recinfo.ACTION_SOURCE  = X_ACTION_SOURCE)
145       AND (recinfo.ACTION_CODE  = X_ACTION_CODE)
146       AND ((recinfo.VALID_DATE = X_VALID_DATE)
147            OR ((recinfo.VALID_DATE is null) AND (X_VALID_DATE is null)))
148       AND ((recinfo.INVALID_DATE = X_INVALID_DATE)
149            OR ((recinfo.INVALID_DATE is null) AND (X_INVALID_DATE is null)))
150       AND ((recinfo.WI_MAPPING_PROC = X_WI_MAPPING_PROC)
151            OR ((recinfo.WI_MAPPING_PROC is null) AND (X_WI_MAPPING_PROC is null)))
152       AND ((recinfo.WI_MAPPING_TYPE = X_WI_MAPPING_TYPE)
153            OR ((recinfo.WI_MAPPING_TYPE is null) AND (X_WI_MAPPING_TYPE is null)))
154   ) then
155     null;
156   else
157     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
158     app_exception.raise_exception;
159   end if;
160 
161   for tlinfo in c1 loop
162     if (tlinfo.BASELANG = 'Y') then
163       if (    ((tlinfo.DESCRIPTION = X_DESCRIPTION)
164                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
165       ) then
166         null;
167       else
168         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
169         app_exception.raise_exception;
170       end if;
171     end if;
172   end loop;
173   return;
174 end LOCK_ROW;
175 
176 procedure UPDATE_ROW (
177   X_SERVICE_VAL_ACT_ID in NUMBER,
178   X_ACTION_SOURCE in VARCHAR2,
179   X_ACTION_CODE  in  VARCHAR2,
180   X_ORGANIZATION_ID in NUMBER,
181   X_INVENTORY_ITEM_ID in NUMBER,
182   X_VALID_DATE in DATE,
183   X_INVALID_DATE in DATE,
184   X_WI_MAPPING_PROC in VARCHAR2,
185   X_WI_MAPPING_TYPE in VARCHAR2,
186   X_DESCRIPTION in VARCHAR2,
187   X_LAST_UPDATE_DATE in DATE,
188   X_LAST_UPDATED_BY in NUMBER,
189   X_LAST_UPDATE_LOGIN in NUMBER
190 ) is
191 begin
192   update XDP_SERVICE_VAL_ACTS set
193     ACTION_SOURCE = X_ACTION_SOURCE ,
194     ACTION_CODE = X_ACTION_CODE ,
195     ORGANIZATION_ID = X_ORGANIZATION_ID ,
196     INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID ,
197     VALID_DATE = X_VALID_DATE,
198     INVALID_DATE = X_INVALID_DATE,
199     WI_MAPPING_PROC = X_WI_MAPPING_PROC,
200     WI_MAPPING_TYPE = X_WI_MAPPING_TYPE,
201     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
202     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
203     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
204   where SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID;
205 
206   if (sql%notfound) then
207     raise no_data_found;
208   end if;
209 
210   update XDP_SERVICE_VAL_ACTS_TL set
211     DESCRIPTION = X_DESCRIPTION,
212     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
213     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
214     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
215     SOURCE_LANG = userenv('LANG')
216   where SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID
217   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
218 
219   if (sql%notfound) then
220     raise no_data_found;
221   end if;
222 end UPDATE_ROW;
223 
224 procedure DELETE_ROW (
225   X_SERVICE_VAL_ACT_ID in NUMBER
226 ) is
227 begin
228   delete from XDP_SERVICE_VAL_ACTS_TL
229   where SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID;
230 
231   if (sql%notfound) then
232     raise no_data_found;
233   end if;
234 
235   delete from XDP_SERVICE_VAL_ACTS
236   where SERVICE_VAL_ACT_ID = X_SERVICE_VAL_ACT_ID;
237 
238   if (sql%notfound) then
239     raise no_data_found;
240   end if;
241 end DELETE_ROW;
242 
243 procedure ADD_LANGUAGE
244 is
245 begin
246   delete from XDP_SERVICE_VAL_ACTS_TL T
247   where not exists
248     (select NULL
249     from XDP_SERVICE_VAL_ACTS B
250     where B.SERVICE_VAL_ACT_ID = T.SERVICE_VAL_ACT_ID
251     );
252 
253   update XDP_SERVICE_VAL_ACTS_TL T set (
254       DESCRIPTION
255     ) = (select
256       B.DESCRIPTION
257     from XDP_SERVICE_VAL_ACTS_TL B
258     where B.SERVICE_VAL_ACT_ID = T.SERVICE_VAL_ACT_ID
259     and B.LANGUAGE = T.SOURCE_LANG)
260   where (
261       T.SERVICE_VAL_ACT_ID,
262       T.LANGUAGE
263   ) in (select
264       SUBT.SERVICE_VAL_ACT_ID,
265       SUBT.LANGUAGE
266     from XDP_SERVICE_VAL_ACTS_TL SUBB, XDP_SERVICE_VAL_ACTS_TL SUBT
267     where SUBB.SERVICE_VAL_ACT_ID = SUBT.SERVICE_VAL_ACT_ID
268     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
269     and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
270       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
271       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
272   ));
273 
274   insert into XDP_SERVICE_VAL_ACTS_TL (
275     SERVICE_VAL_ACT_ID,
276     DESCRIPTION,
277     CREATED_BY,
278     CREATION_DATE,
279     LAST_UPDATED_BY,
280     LAST_UPDATE_DATE,
281     LAST_UPDATE_LOGIN,
282     LANGUAGE,
283     SOURCE_LANG
284   ) select
285     B.SERVICE_VAL_ACT_ID,
286     B.DESCRIPTION,
287     B.CREATED_BY,
288     B.CREATION_DATE,
289     B.LAST_UPDATED_BY,
290     B.LAST_UPDATE_DATE,
291     B.LAST_UPDATE_LOGIN,
292     L.LANGUAGE_CODE,
293     B.SOURCE_LANG
294   from XDP_SERVICE_VAL_ACTS_TL B, FND_LANGUAGES L
295   where L.INSTALLED_FLAG in ('I', 'B')
296   and B.LANGUAGE = userenv('LANG')
297   and not exists
298     (select NULL
299     from XDP_SERVICE_VAL_ACTS_TL T
300     where T.SERVICE_VAL_ACT_ID = B.SERVICE_VAL_ACT_ID
301     and T.LANGUAGE = L.LANGUAGE_CODE);
302 end ADD_LANGUAGE;
303 
304 procedure LOAD_ROW (
305   X_SERVICE_VAL_ACT_ID in NUMBER,
306   X_ACTION_SOURCE in VARCHAR2 ,
307   X_ACTION_CODE  in  VARCHAR2,
308   X_ORGANIZATION_ID in NUMBER,
309   X_INVENTORY_ITEM_ID in NUMBER,
310   X_VALID_DATE in DATE,
311   X_INVALID_DATE in DATE,
312   X_WI_MAPPING_PROC in VARCHAR2,
313   X_WI_MAPPING_TYPE in VARCHAR2,
314   X_DESCRIPTION in VARCHAR2,
315   X_OWNER in VARCHAR2) IS
316 begin
317 
318   declare
319      user_id            number := 0;
320      row_id             varchar2(64);
321 
322   begin
323 
324      if (X_OWNER = 'SEED') then
325         user_id := 1;
326      end if;
327 
328      XDP_SERVICE_VAL_ACTS_PKG.UPDATE_ROW (
329   	X_SERVICE_VAL_ACT_ID => X_SERVICE_VAL_ACT_ID,
330         X_ACTION_SOURCE  => X_ACTION_SOURCE,
331         X_ACTION_CODE  => X_ACTION_CODE,
332         X_ORGANIZATION_ID => X_ORGANIZATION_ID,
333         X_INVENTORY_ITEM_ID => X_INVENTORY_ITEM_ID,
334   	X_VALID_DATE => X_VALID_DATE,
335   	X_INVALID_DATE => X_INVALID_DATE,
336   	X_WI_MAPPING_PROC => X_WI_MAPPING_PROC,
337   	X_WI_MAPPING_TYPE => X_WI_MAPPING_TYPE,
338   	X_DESCRIPTION => X_DESCRIPTION,
339         X_LAST_UPDATE_DATE => sysdate,
340         X_LAST_UPDATED_BY => user_id,
341         X_LAST_UPDATE_LOGIN => 0);
342 
343     exception
344        when NO_DATA_FOUND then
345           XDP_SERVICE_VAL_ACTS_PKG.INSERT_ROW (
346              	X_ROWID => row_id,
347   		X_SERVICE_VAL_ACT_ID => X_SERVICE_VAL_ACT_ID,
348                 X_ACTION_SOURCE  => X_ACTION_SOURCE,
349                 X_ACTION_CODE  => X_ACTION_CODE,
350                 X_ORGANIZATION_ID => X_ORGANIZATION_ID,
351                 X_INVENTORY_ITEM_ID => X_INVENTORY_ITEM_ID,
352   		X_VALID_DATE => X_VALID_DATE,
353   		X_INVALID_DATE => X_INVALID_DATE,
354   		X_WI_MAPPING_PROC => X_WI_MAPPING_PROC,
355   		X_WI_MAPPING_TYPE => X_WI_MAPPING_TYPE,
356              	X_DESCRIPTION => X_DESCRIPTION,
357              	X_CREATION_DATE => sysdate,
358              	X_CREATED_BY => user_id,
359              	X_LAST_UPDATE_DATE => sysdate,
360              	X_LAST_UPDATED_BY => user_id,
361              	X_LAST_UPDATE_LOGIN => 0);
362    end;
363 end LOAD_ROW;
364 
365 procedure TRANSLATE_ROW (
366    X_SERVICE_VAL_ACT_ID in NUMBER,
367    X_DESCRIPTION in VARCHAR2,
368    X_OWNER in VARCHAR2) IS
369 
370 begin
371 
372     -- only update rows that have not been altered by user
373 
374     update XDP_SERVICE_VAL_ACTS_TL
375     set  description = X_DESCRIPTION,
376         source_lang = userenv('LANG'),
377         last_update_date = sysdate,
378         last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
379         last_update_login = 0
380   where service_val_act_id = X_SERVICE_VAL_ACT_ID
381     and userenv('LANG') in (language, source_lang);
382 
383 end TRANSLATE_ROW;
384 
385 
386 end XDP_SERVICE_VAL_ACTS_PKG;