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

Source


1 package body IBC_FILE_EXTN_MAPPINGS_PKG as
2 /* $Header: ibctfexb.pls 120.5 2006/06/22 09:34:02 sharma ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out  NOCOPY VARCHAR2,
5   X_MAPPING_ID in  NUMBER,
6   X_CONTENT_TYPE_CODE in  VARCHAR2,
7   X_EXTENSION in  VARCHAR2,
8   X_DESCRIPTION in  VARCHAR2,
9   X_CREATION_DATE in   DATE,
10   X_CREATED_BY in   NUMBER,
11   X_LAST_UPDATE_DATE in   DATE,
12   X_LAST_UPDATED_BY in   NUMBER,
13   X_LAST_UPDATE_LOGIN in   NUMBER
14 ) is
15   cursor C is select ROWID from IBC_FILE_EXTN_MAPPINGS_B
16     where MAPPING_ID = X_MAPPING_ID
17     ;
18 begin
19   insert into IBC_FILE_EXTN_MAPPINGS_B (
20     MAPPING_ID,
21     CONTENT_TYPE_CODE,
22     CREATION_DATE,
23     CREATED_BY,
24     LAST_UPDATE_DATE,
25     LAST_UPDATED_BY,
26     LAST_UPDATE_LOGIN
27   ) values (
28     X_MAPPING_ID,
29     X_CONTENT_TYPE_CODE,
30     X_CREATION_DATE,
31     X_CREATED_BY,
32     X_LAST_UPDATE_DATE,
33     X_LAST_UPDATED_BY,
34     X_LAST_UPDATE_LOGIN
35   );
36 
37   insert into IBC_FILE_EXTN_MAPPINGS_TL (
38     LAST_UPDATE_DATE,
39     LAST_UPDATE_LOGIN,
40     LAST_UPDATED_BY,
41     MAPPING_ID,
42     EXTENSION,
43     DESCRIPTION,
44     CREATED_BY,
45     CREATION_DATE,
46     LANGUAGE,
47     SOURCE_LANG
48   ) select
49     X_LAST_UPDATE_DATE,
50     X_LAST_UPDATE_LOGIN,
51     X_LAST_UPDATED_BY,
52     X_MAPPING_ID,
53     X_EXTENSION,
54     X_DESCRIPTION,
55     X_CREATED_BY,
56     X_CREATION_DATE,
57     L.LANGUAGE_CODE,
58     userenv('LANG')
59   from FND_LANGUAGES L
60   where L.INSTALLED_FLAG in ('I', 'B')
61   and not exists
62     (select NULL
63     from IBC_FILE_EXTN_MAPPINGS_TL T
64     where T.MAPPING_ID = X_MAPPING_ID
65     and T.LANGUAGE = L.LANGUAGE_CODE);
66 
67   open c;
68   fetch c into X_ROWID;
69   if (c%notfound) then
70     close c;
71     raise no_data_found;
72   end if;
73   close c;
74 
75 end INSERT_ROW;
76 
77 procedure LOCK_ROW (
78   X_MAPPING_ID in   NUMBER,
79   X_CONTENT_TYPE_CODE in   VARCHAR2,
80   X_EXTENSION in   VARCHAR2,
81   X_DESCRIPTION in   VARCHAR2
82 ) is
83   cursor c is select
84       CONTENT_TYPE_CODE
85     from IBC_FILE_EXTN_MAPPINGS_B
86     where MAPPING_ID = X_MAPPING_ID
87     for update of MAPPING_ID nowait;
88   recinfo c%rowtype;
89 
90   cursor c1 is select
91       EXTENSION,
92       DESCRIPTION,
93       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
94     from IBC_FILE_EXTN_MAPPINGS_TL
95     where MAPPING_ID = X_MAPPING_ID
96     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
97     for update of MAPPING_ID nowait;
98 begin
99   open c;
100   fetch c into recinfo;
101   if (c%notfound) then
102     close c;
103     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
104     app_exception.raise_exception;
105   end if;
106   close c;
107   if (    (recinfo.CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE)
108   ) then
109     null;
110   else
111     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
112     app_exception.raise_exception;
113   end if;
114 
115   for tlinfo in c1 loop
116     if (tlinfo.BASELANG = 'Y') then
117       if (    (tlinfo.EXTENSION = X_EXTENSION)
118           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
119                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
120       ) then
121         null;
122       else
123         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124         app_exception.raise_exception;
125       end if;
126     end if;
127   end loop;
128   return;
129 end LOCK_ROW;
130 
131 procedure UPDATE_ROW (
132   X_MAPPING_ID in   NUMBER,
133   X_CONTENT_TYPE_CODE in   VARCHAR2,
134   X_EXTENSION in   VARCHAR2,
135   X_DESCRIPTION in   VARCHAR2,
136   X_LAST_UPDATE_DATE in   DATE,
137   X_LAST_UPDATED_BY in   NUMBER,
138   X_LAST_UPDATE_LOGIN in   NUMBER
139 ) is
140 begin
141   update IBC_FILE_EXTN_MAPPINGS_B set
142     CONTENT_TYPE_CODE = X_CONTENT_TYPE_CODE,
143     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
144     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
145     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
146   where MAPPING_ID = X_MAPPING_ID;
147 
148   if (sql%notfound) then
149     raise no_data_found;
150   end if;
151 
152   update IBC_FILE_EXTN_MAPPINGS_TL set
153     EXTENSION = X_EXTENSION,
154     DESCRIPTION = X_DESCRIPTION,
155     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
156     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
157     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
158     SOURCE_LANG = userenv('LANG')
159   where MAPPING_ID = X_MAPPING_ID
160   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
161 
162   if (sql%notfound) then
163     raise no_data_found;
164   end if;
165 end UPDATE_ROW;
166 
167 procedure DELETE_ROW (
168   X_MAPPING_ID in   NUMBER
169 ) is
170 begin
171   delete from IBC_FILE_EXTN_MAPPINGS_TL
172   where MAPPING_ID = X_MAPPING_ID;
173 
174   if (sql%notfound) then
175     raise no_data_found;
176   end if;
177 
178   delete from IBC_FILE_EXTN_MAPPINGS_B
179   where MAPPING_ID = X_MAPPING_ID;
180 
181   if (sql%notfound) then
182     raise no_data_found;
183   end if;
184 end DELETE_ROW;
185 
186 procedure ADD_LANGUAGE
187 is
188 begin
189   delete from IBC_FILE_EXTN_MAPPINGS_TL T
190   where not exists
191     (select NULL
192     from IBC_FILE_EXTN_MAPPINGS_B B
193     where B.MAPPING_ID = T.MAPPING_ID
194     );
195 
196   update IBC_FILE_EXTN_MAPPINGS_TL T set (
197       EXTENSION,
198       DESCRIPTION
199     ) = (select
200       B.EXTENSION,
201       B.DESCRIPTION
202     from IBC_FILE_EXTN_MAPPINGS_TL B
203     where B.MAPPING_ID = T.MAPPING_ID
204     and B.LANGUAGE = T.SOURCE_LANG)
205   where (
206       T.MAPPING_ID,
207       T.LANGUAGE
208   ) in (select
209       SUBT.MAPPING_ID,
210       SUBT.LANGUAGE
211     from IBC_FILE_EXTN_MAPPINGS_TL SUBB, IBC_FILE_EXTN_MAPPINGS_TL SUBT
212     where SUBB.MAPPING_ID = SUBT.MAPPING_ID
213     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
214     and (SUBB.EXTENSION <> SUBT.EXTENSION
215       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
216       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
217       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
218   ));
219 
220   insert into IBC_FILE_EXTN_MAPPINGS_TL (
221     LAST_UPDATE_DATE,
222     LAST_UPDATE_LOGIN,
223     LAST_UPDATED_BY,
224     MAPPING_ID,
225     EXTENSION,
226     DESCRIPTION,
227     CREATED_BY,
228     CREATION_DATE,
229     LANGUAGE,
230     SOURCE_LANG
231   ) select /*+ ORDERED */
232     B.LAST_UPDATE_DATE,
233     B.LAST_UPDATE_LOGIN,
234     B.LAST_UPDATED_BY,
235     B.MAPPING_ID,
236     B.EXTENSION,
237     B.DESCRIPTION,
238     B.CREATED_BY,
239     B.CREATION_DATE,
240     L.LANGUAGE_CODE,
241     B.SOURCE_LANG
242   from IBC_FILE_EXTN_MAPPINGS_TL B, FND_LANGUAGES L
243   where L.INSTALLED_FLAG in ('I', 'B')
244   and B.LANGUAGE = userenv('LANG')
245   and not exists
246     (select NULL
247     from IBC_FILE_EXTN_MAPPINGS_TL T
248     where T.MAPPING_ID = B.MAPPING_ID
249     and T.LANGUAGE = L.LANGUAGE_CODE);
250 end ADD_LANGUAGE;
251 
252 PROCEDURE LOAD_SEED_ROW(
253 	p_UPLOAD_MODE IN VARCHAR2,
254 	p_mapping_id   IN VARCHAR2,
255 	p_content_type_code  IN VARCHAR2,
256 	p_extension    IN VARCHAR2,
257 	p_description  IN VARCHAR2,
258 	p_OWNER	 IN VARCHAR2,
259 	p_LAST_UPDATE_DATE IN VARCHAR2 ) IS
260 BEGIN
261 	IF (p_UPLOAD_MODE = 'NLS') THEN
262 		IBC_FILE_EXTN_MAPPINGS_PKG.TRANSLATE_ROW (
263 			p_UPLOAD_MODE => p_UPLOAD_MODE,
264 			p_mapping_id	=> p_mapping_id,
265 			p_extension => p_extension,
266 			p_description => p_description,
267 			p_OWNER	=>p_OWNER,
268 			p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE );
269 	ELSE
270 		IBC_FILE_EXTN_MAPPINGS_PKG.LOAD_ROW (
271 			p_UPLOAD_MODE => p_UPLOAD_MODE,
272 			p_mapping_id	=> p_mapping_id,
273 			p_content_type_code	=> p_content_type_code,
274 			p_extension => p_extension,
275 			p_description => p_description,
276 			p_OWNER	=>p_OWNER,
277 			p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE );
278 	END IF;
279 END LOAD_SEED_ROW;
280 
281 PROCEDURE LOAD_ROW(
282 	p_UPLOAD_MODE IN VARCHAR2,
283 	p_mapping_id   IN VARCHAR2,
284 	p_content_type_code  IN VARCHAR2,
285 	p_extension    IN VARCHAR2,
286 	p_description  IN VARCHAR2,
287 	p_OWNER	 IN VARCHAR2,
288 	p_LAST_UPDATE_DATE IN VARCHAR2 ) IS
289 
290     l_user_id    NUMBER := 0;
291     l_row_id     VARCHAR2(64);
292     l_last_update_date DATE;
293 
294     db_user_id    NUMBER := 0;
295     db_last_update_date DATE;
296 
297 BEGIN
298 	--get last updated by user id
299 	l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
300 
301 	--translate data type VARCHAR2 to DATE for last_update_date
302 	l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
303 
304 	-- get updatedby  and update_date values if existing in db
305 	SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
306 	FROM IBC_FILE_EXTN_MAPPINGS_B
307 	WHERE  MAPPING_ID = p_mapping_id;
308 
309 	IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
310 		db_user_id, db_last_update_date, p_upload_mode )) THEN
311 
312 		IBC_FILE_EXTN_MAPPINGS_PKG.Update_row (
313 			x_mapping_id          =>TO_NUMBER(p_mapping_id)
314 		       ,x_content_type_code   =>p_content_type_code
315 		       ,x_extension           =>NVL(p_extension,Fnd_Api.G_MISS_CHAR)
316 		       ,x_description         =>NVL(p_description,Fnd_Api.G_MISS_CHAR)
317 		       ,x_last_update_date    =>l_last_update_date
318 		       ,x_last_updated_by     =>l_user_id
319 		       ,x_last_update_login   =>0);
320 	END IF;
321 
322   EXCEPTION
323     WHEN NO_DATA_FOUND THEN
324 
325 	IBC_FILE_EXTN_MAPPINGS_Pkg.insert_row (
326 		x_rowid  =>l_row_id,
327 		x_mapping_id      =>TO_NUMBER(p_mapping_id)
328 		,x_content_type_code   =>p_content_type_code
329 		,x_extension      =>NVL(p_extension,Fnd_Api.G_MISS_CHAR)
330 		,x_description    =>NVL(p_description,Fnd_Api.G_MISS_CHAR),
331 		x_creation_date =>l_last_update_date,
332 		x_created_by 	=>l_user_id,
333 		x_last_update_date    =>l_last_update_date,
334 		x_last_updated_by     =>l_user_id,
335 		x_last_update_login   =>0);
336 
337 END LOAD_ROW;
338 
339 
340 PROCEDURE TRANSLATE_ROW(
341 	p_UPLOAD_MODE IN VARCHAR2,
342 	p_mapping_id   IN VARCHAR2,
343 	p_extension    IN VARCHAR2,
344 	p_description  IN VARCHAR2,
345 	p_OWNER	 IN VARCHAR2,
346 	p_LAST_UPDATE_DATE IN VARCHAR2 ) IS
347 
348     l_user_id    NUMBER := 0;
349     l_last_update_date DATE;
350 
351     db_user_id    NUMBER := 0;
352     db_last_update_date DATE;
353 
354 BEGIN
355 	--get last updated by user id
356 	l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
357 
358 	--translate data type VARCHAR2 to DATE for last_update_date
359 	l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
360 
361 	-- get updatedby  and update_date values if existing in db
362 	SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
363 	FROM IBC_FILE_EXTN_MAPPINGS_TL
364 	WHERE  MAPPING_ID = p_mapping_id
365 	AND  USERENV('LANG') IN (LANGUAGE, source_lang);
366 
367 	IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
368 		db_user_id, db_last_update_date, p_upload_mode )) THEN
369 		  -- Only update rows which have not been altered by user
370 		  UPDATE IBC_FILE_EXTN_MAPPINGS_TL
371 		  SET description = p_description,
372 		      Extension =  p_Extension,
373 		      source_lang = USERENV('LANG'),
374 		      last_update_date = l_last_update_date,
375 		      last_updated_by = l_user_id,
376 		      last_update_login = 0
377 		  WHERE mapping_id = p_mapping_id
378 		  AND USERENV('LANG') IN (LANGUAGE, source_lang);
379 	END IF;
380 END TRANSLATE_ROW;
381 
382 END IBC_FILE_EXTN_MAPPINGS_PKG;