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

Source


1 package body AMS_TCOP_PERIODS_PKG as
2 /* $Header: amsltcpb.pls 115.1 2003/10/17 11:53:42 mayjain noship $ */
3 procedure INSERT_ROW (
4 	X_ROWID IN OUT NOCOPY VARCHAR2,
5 	X_PERIOD_ID IN NUMBER,
6 	X_NO_OF_DAYS IN NUMBER,
7 	X_ENABLED_FLAG IN VARCHAR2,
8 	X_PERIOD_NAME IN VARCHAR2,
9 	X_DESCRIPTION in VARCHAR2,
10 	X_LAST_UPDATE_DATE in DATE,
11 	X_LAST_UPDATED_BY in NUMBER,
12 	X_CREATION_DATE in DATE,
13 	X_CREATED_BY in NUMBER,
14 	X_LAST_UPDATE_LOGIN in NUMBER,
15 	X_OBJECT_VERSION_NUMBER in NUMBER
16 ) is
17   cursor c is select ROWID from AMS_TCOP_FR_PERIODS_B
18     where PERIOD_ID = X_PERIOD_ID
19     ;
20   cursor ctl is select ROWID from AMS_TCOP_FR_PERIODS_TL
21     where PERIOD_ID = X_PERIOD_ID
22     ;
23 begin
24 	insert into AMS_TCOP_FR_PERIODS_B (
25 		PERIOD_ID,
26 		NO_OF_DAYS,
27 		ENABLED_FLAG,
28 		LAST_UPDATE_DATE,
29 		LAST_UPDATED_BY,
30 		CREATION_DATE,
31 		CREATED_BY,
32 		LAST_UPDATE_LOGIN,
33 		OBJECT_VERSION_NUMBER
34 	) values (
35 		X_PERIOD_ID,
36 		X_NO_OF_DAYS,
37 		X_ENABLED_FLAG,
38 		DECODE(X_LAST_UPDATE_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_LAST_UPDATE_DATE),
39 		DECODE(X_LAST_UPDATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATED_BY),
40 		DECODE(X_CREATION_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_CREATION_DATE),
41 		DECODE(X_CREATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_CREATED_BY),
42 		DECODE(X_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATE_LOGIN),
43 		X_OBJECT_VERSION_NUMBER
44 	);
45 
46   open c;
47   fetch c into X_ROWID;
48   if (c%notfound) then
49     close c;
50     raise no_data_found;
51   end if;
52   close c;
53 
54 	insert into AMS_TCOP_FR_PERIODS_TL (
55 		PERIOD_ID,
56 		PERIOD_NAME,
57 		DESCRIPTION,
58 		LANGUAGE,
59 		SOURCE_LANG,
60 		LAST_UPDATE_DATE,
61 		LAST_UPDATED_BY,
62 		CREATION_DATE,
63 		CREATED_BY,
64 		LAST_UPDATE_LOGIN
65 	)
66 	SELECT
67 	     X_PERIOD_ID
68 	    ,X_PERIOD_NAME
69 	    ,DECODE(X_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,X_DESCRIPTION)
70 	    ,l.language_code
71 	    ,USERENV('lang')
72 	    ,DECODE(X_LAST_UPDATE_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_LAST_UPDATE_DATE)
73 	    ,DECODE(X_LAST_UPDATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATED_BY)
74 	    ,DECODE(X_CREATION_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_CREATION_DATE)
75 	    ,DECODE(X_CREATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_CREATED_BY)
76 	    ,DECODE(X_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATE_LOGIN)
77   FROM fnd_languages l
78   WHERE l.installed_flag IN ('I', 'B')
79   AND NOT EXISTS
80     (SELECT NULL
81     FROM AMS_TCOP_FR_PERIODS_TL T
82     WHERE T.PERIOD_ID = X_PERIOD_ID
83     AND T.LANGUAGE = l.language_code);
84 
85   open ctl;
86   fetch ctl into X_ROWID;
87   if (ctl%notfound) then
88     close ctl;
89     raise no_data_found;
90   end if;
91   close ctl;
92 
93 end INSERT_ROW;
94 
95 
96 
97 
98 procedure LOCK_ROW (
99 	X_PERIOD_ID IN NUMBER,
100 	X_OBJECT_VERSION_NUMBER in NUMBER,
101 	X_NO_OF_DAYS IN NUMBER,
102 	X_ENABLED_FLAG IN VARCHAR2,
103 	X_PERIOD_NAME IN VARCHAR2,
104 	X_DESCRIPTION in VARCHAR2
105 ) is
106   cursor c is select
107 		OBJECT_VERSION_NUMBER,
108 		NO_OF_DAYS,
109 		ENABLED_FLAG,
110 		LAST_UPDATE_DATE,
111 		LAST_UPDATED_BY,
112 		CREATION_DATE,
113 		CREATED_BY,
114 		LAST_UPDATE_LOGIN
115     from AMS_TCOP_FR_PERIODS_B
116     where PERIOD_ID = X_PERIOD_ID
117     for update of PERIOD_ID nowait;
118   recinfo c%rowtype;
119 
120 
121 
122   CURSOR ctl IS SELECT
123       PERIOD_NAME,
124       DESCRIPTION,
125       DECODE(LANGUAGE, USERENV('lang'), 'Y', 'N') baselang
126     FROM AMS_TCOP_FR_PERIODS_TL
127     WHERE PERIOD_ID = X_PERIOD_ID
128     AND USERENV('lang') IN (LANGUAGE, source_lang)
129     FOR UPDATE OF PERIOD_ID NOWAIT;
130 
131 begin
132 
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 (    ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
142            OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
143       AND ((recinfo.NO_OF_DAYS = X_NO_OF_DAYS)
144            OR ((recinfo.NO_OF_DAYS is null) AND (X_NO_OF_DAYS is null)))
145       AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
146            OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
147   ) then
148     null;
149   else
150     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
151     app_exception.raise_exception;
152   end if;
153 
154   FOR tlinfo IN ctl LOOP
155   IF (tlinfo.BASELANG = 'Y') THEN
156       IF (    (tlinfo.PERIOD_NAME = X_PERIOD_NAME)
157           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
158                OR ((tlinfo.DESCRIPTION IS NULL) AND (X_DESCRIPTION IS NULL)))
159       ) THEN
160         NULL;
161       ELSE
162         Fnd_Message.set_name('FND', 'FORM_RECORD_CHANGED');
163         App_Exception.raise_exception;
164       END IF;
165     END IF;
166   END LOOP;
167 
168   return;
169 end LOCK_ROW;
170 
171 
172 
173 procedure UPDATE_ROW (
174 	X_PERIOD_ID IN NUMBER,
175 	X_OBJECT_VERSION_NUMBER in NUMBER,
176 	X_NO_OF_DAYS IN NUMBER,
177 	X_ENABLED_FLAG IN VARCHAR2,
178 	X_PERIOD_NAME IN VARCHAR2,
179 	X_DESCRIPTION in VARCHAR2,
180 	X_LAST_UPDATE_DATE in DATE,
181 	X_LAST_UPDATED_BY in NUMBER,
182 	X_LAST_UPDATE_LOGIN in NUMBER
183 ) is
184 begin
185   update AMS_TCOP_FR_PERIODS_B set
186 		OBJECT_VERSION_NUMBER	= X_OBJECT_VERSION_NUMBER
187 		,NO_OF_DAYS				= X_NO_OF_DAYS
188 		,ENABLED_FLAG				= X_ENABLED_FLAG
189 		,LAST_UPDATE_DATE			= X_LAST_UPDATE_DATE
190 		,LAST_UPDATED_BY			= X_LAST_UPDATED_BY
191 		,LAST_UPDATE_LOGIN		= X_LAST_UPDATE_LOGIN
192   where PERIOD_ID           = X_PERIOD_ID;
193 
194   if (sql%notfound) then
195     raise no_data_found;
196   end if;
197 
198   update AMS_TCOP_FR_PERIODS_TL set
199     PERIOD_NAME = X_PERIOD_NAME,
200     DESCRIPTION = X_DESCRIPTION,
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     SOURCE_LANG = USERENV('LANG')
205   where PERIOD_ID = X_PERIOD_ID
206   and USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
207 
208   if (sql%notfound) then
209     raise no_data_found;
210   end if;
211 
212 end UPDATE_ROW;
213 
214 
215 procedure DELETE_ROW (
216 	X_PERIOD_ID IN NUMBER
217 ) is
218 begin
219 	delete from AMS_TCOP_FR_PERIODS_TL
220 	where PERIOD_ID = X_PERIOD_ID;
221 
222 	if (sql%notfound) then
223 		raise no_data_found;
224 	end if;
225 
226 	delete from AMS_TCOP_FR_PERIODS_B
227 		where PERIOD_ID = X_PERIOD_ID;
228 
229 	if (sql%notfound) then
230 		raise no_data_found;
231 	end if;
232 
233 end DELETE_ROW;
234 
235 
236 
237 procedure  LOAD_ROW(
238 	X_PERIOD_ID IN NUMBER,
239 	X_NO_OF_DAYS IN NUMBER,
240 	X_ENABLED_FLAG IN VARCHAR2,
241 	X_PERIOD_NAME IN VARCHAR2,
242 	X_DESCRIPTION in VARCHAR2,
243 	X_OWNER in  VARCHAR2,
244 	X_CUSTOM_MODE in VARCHAR2
245 ) is
246 
247 l_user_id   number := 0;
248 l_last_updated_by number;
249 l_obj_verno  number;
250 l_dummy_char  varchar2(1);
251 l_row_id    varchar2(100);
252 
253 cursor c_obj_verno is
254   select OBJECT_VERSION_NUMBER,
255 	 last_updated_by
256   from   AMS_TCOP_FR_PERIODS_B
257   where  PERIOD_ID =  X_PERIOD_ID;
258 
259 cursor c_chk_prd_exists is
260   select 'x'
261   from   AMS_TCOP_FR_PERIODS_B
262   where  PERIOD_ID = X_PERIOD_ID;
263 
264 cursor ctl_chk_prd_exists is
265   select 'x'
266   from   AMS_TCOP_FR_PERIODS_TL
267   where  PERIOD_ID = X_PERIOD_ID;
268 
269 BEGIN
270 
271  if X_OWNER = 'SEED' then
272      l_user_id := 1;
273  elsif X_OWNER = 'ORACLE' then
274      l_user_id := 2;
275  elsif X_OWNER = 'SYSADMIN' THEN
276     l_user_id := 0;
277  end if;
278 
279  open c_chk_prd_exists;
280  fetch c_chk_prd_exists into l_dummy_char;
281  if c_chk_prd_exists%notfound
282  then
283     close c_chk_prd_exists;
284 
285     l_obj_verno := 1;
286 
287     AMS_TCOP_PERIODS_PKG.INSERT_ROW (
288 			X_ROWID			=>	l_row_id,
289 			X_PERIOD_ID		=>	X_PERIOD_ID,
290 			X_NO_OF_DAYS		=>	X_NO_OF_DAYS,
291 			X_ENABLED_FLAG		=>	X_ENABLED_FLAG,
292 			X_PERIOD_NAME		=>	X_PERIOD_NAME,
293 			X_DESCRIPTION		=>	X_DESCRIPTION,
294 			X_LAST_UPDATE_DATE	=>	sysdate,
295 			X_LAST_UPDATED_BY	=>	l_user_id,
296 			X_CREATION_DATE		=>	sysdate,
297 			X_CREATED_BY		=>	l_user_id,
298 			X_LAST_UPDATE_LOGIN	=>	0,
299 			X_OBJECT_VERSION_NUMBER =>	l_obj_verno
300 		);
301 else
302    close c_chk_prd_exists;
303 
304    open c_obj_verno;
305    fetch c_obj_verno into l_obj_verno,l_last_updated_by;
306    close c_obj_verno;
307 
308    if (l_last_updated_by in (1,2,0) OR
309        NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
310 
311        AMS_TCOP_PERIODS_PKG.UPDATE_ROW(
312 		X_PERIOD_ID		=>	X_PERIOD_ID,
313 		X_NO_OF_DAYS		=>	X_NO_OF_DAYS,
314 		X_ENABLED_FLAG		=>	X_ENABLED_FLAG,
315 		X_PERIOD_NAME		=>	X_PERIOD_NAME,
316 		X_DESCRIPTION		=>	X_DESCRIPTION,
317 		X_LAST_UPDATE_DATE      =>    SYSDATE,
318 		X_LAST_UPDATED_BY       =>    l_user_id,
319 		X_LAST_UPDATE_LOGIN     =>    0,
320 		X_OBJECT_VERSION_NUMBER =>    l_obj_verno + 1
321          );
322     end if;
323 end if;
324 
325 END LOAD_ROW;
326 
327 PROCEDURE TRANSLATE_ROW (
328 	X_PERIOD_ID IN NUMBER,
329 	X_PERIOD_NAME IN VARCHAR2,
330 	X_DESCRIPTION in VARCHAR2,
331 	X_OWNER IN VARCHAR2,
332 	X_CUSTOM_MODE IN VARCHAR2
333 ) IS
334     cursor c_last_updated_by is
335     select last_updated_by
336     from AMS_TCOP_FR_PERIODS_TL
337     where PERIOD_ID = X_PERIOD_ID
338     and  USERENV('LANG') = LANGUAGE;
339 
340     l_last_updated_by number;
341 
342 BEGIN
343 
344   open c_last_updated_by;
345   fetch c_last_updated_by into l_last_updated_by;
346   close c_last_updated_by;
347 
348 
349   if (l_last_updated_by in (1,2,0) OR
350        NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
351   -- Only update rows which have not been altered by user
352 
353      UPDATE AMS_TCOP_FR_PERIODS_TL
354      SET description = X_DESCRIPTION,
355       period_name = X_PERIOD_NAME,
356       source_lang = USERENV('LANG'),
357       last_update_date = SYSDATE,
358       last_updated_by = DECODE(X_OWNER, 'SEED', 1,
359 			       'ORACLE',2,
360 			       'SYSADMIN',0 , -1),
361       last_update_login = 0
362     WHERE PERIOD_ID = X_PERIOD_ID
363     AND USERENV('LANG') IN (LANGUAGE, source_lang);
364 
365   end if;
366 
367 END TRANSLATE_ROW;
368 
369 
370 PROCEDURE add_language
371 IS
372 BEGIN
373   DELETE FROM AMS_TCOP_FR_PERIODS_TL T
374   WHERE NOT EXISTS
375     (SELECT NULL
376      FROM AMS_TCOP_FR_PERIODS_B B
377      WHERE B.PERIOD_ID = T.PERIOD_ID
378     );
379 
380   UPDATE AMS_TCOP_FR_PERIODS_TL T SET (
381       PERIOD_NAME,
382       DESCRIPTION
383     ) =
384 	 (SELECT
385       T1.PERIOD_NAME,
386       T1.DESCRIPTION
387     FROM AMS_TCOP_FR_PERIODS_TL T1
388     WHERE T1.PERIOD_ID = T.PERIOD_ID
389     AND T1.LANGUAGE = T.SOURCE_LANG)
390   WHERE (
391       T.PERIOD_ID,
392       T.LANGUAGE
393    ) IN
394    (SELECT
395       subt.PERIOD_ID,
396       subt.LANGUAGE
397     FROM AMS_TCOP_FR_PERIODS_TL subb, AMS_TCOP_FR_PERIODS_TL subt
398     WHERE subb.PERIOD_ID = subt.PERIOD_ID
399     AND subb.LANGUAGE = subt.SOURCE_LANG
400     AND (subb.PERIOD_NAME <> subt.PERIOD_NAME
401       OR subb.DESCRIPTION <> subt.DESCRIPTION
402       OR (subb.DESCRIPTION IS NULL AND subt.DESCRIPTION IS NOT NULL)
403       OR (subb.DESCRIPTION IS NOT NULL AND subt.DESCRIPTION IS NULL)
404   ));
405 
406   INSERT INTO AMS_TCOP_FR_PERIODS_TL (
407 	PERIOD_ID,
408 	PERIOD_NAME,
409 	DESCRIPTION,
410 	CREATED_BY,
411 	CREATION_DATE,
412 	LAST_UPDATED_BY,
413 	LAST_UPDATE_DATE,
414 	LAST_UPDATE_LOGIN,
415 	LANGUAGE,
416 	SOURCE_LANG
417   )
418   SELECT /*+ ordered */
419 	b.PERIOD_ID,
420 	b.PERIOD_NAME,
421 	b.DESCRIPTION,
422 	b.CREATED_BY,
423 	b.CREATION_DATE,
424 	b.LAST_UPDATED_BY,
425 	b.LAST_UPDATE_DATE,
426 	b.LAST_UPDATE_LOGIN,
427 	l.language_code,
428 	b.SOURCE_LANG
429   FROM AMS_TCOP_FR_PERIODS_TL b, fnd_languages l
430   WHERE l.installed_flag IN ('I', 'B')
431   AND b.LANGUAGE = USERENV('lang')
432   AND NOT EXISTS
433     (SELECT NULL
434     FROM AMS_TCOP_FR_PERIODS_TL T
435     WHERE T.PERIOD_ID = b.PERIOD_ID
436     AND T.LANGUAGE = l.language_code);
437 END add_language;
438 
439 
440 end AMS_TCOP_PERIODS_PKG;