[Home] [Help]
PACKAGE BODY: APPS.PA_RES_FORMATS_PVT
Source
1 package body pa_res_formats_pvt as
2 /* $Header: PARFMTVB.pls 120.0 2005/05/30 09:50:49 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY ROWID,
5 P_RES_FORMAT_ID in NUMBER,
6 P_RESOURCE_FORMAT_SEQ in NUMBER,
7 P_RESOURCE_CLASS_ID in NUMBER,
8 P_RES_TYPE_ID in NUMBER,
9 P_RES_TYPE_ENABLED_FLAG in VARCHAR2,
10 P_RESOURCE_TYPE_DISP_CHARS in NUMBER,
11 P_ORGN_ENABLED_FLAG in VARCHAR2,
12 P_ORGN_DISP_CHARS in NUMBER,
13 P_FIN_CAT_ENABLED_FLAG in VARCHAR2,
14 P_FIN_CAT_DISP_CHARS in NUMBER,
15 P_INCURRED_BY_ENABLED_FLAG in VARCHAR2,
16 P_INCURRED_BY_DISP_CHARS in NUMBER,
17 P_SUPPLIER_ENABLED_FLAG in VARCHAR2,
18 P_SUPPLIER_DISP_CHARS in NUMBER,
19 P_ROLE_ENABLED_FLAG in VARCHAR2,
20 P_ROLE_DISP_CHARS in NUMBER,
21 P_RESOURCE_CLASS_FLAG in VARCHAR2,
22 P_NAME in VARCHAR2,
23 P_DESCRIPTION in VARCHAR2,
24 P_CREATION_DATE in DATE ,
25 P_CREATED_BY in NUMBER ,
26 P_LAST_UPDATE_DATE in DATE ,
27 P_LAST_UPDATED_BY in NUMBER ,
28 P_LAST_UPDATE_LOGIN in NUMBER
29 ) is
30
31 l_res_format_id pa_res_formats_b.res_format_id%type;
32
33
34 cursor C is select ROWID from pa_res_formats_b
35 where res_format_id = l_res_format_id;
36 begin
37
38 select nvl(P_RES_FORMAT_ID,PA_RES_FORMATS_S.nextval)
39 into l_res_format_id
40 from dual;
41
42 insert into pa_res_formats_b (
43 RES_FORMAT_ID ,
44 RESOURCE_FORMAT_SEQ ,
45 RESOURCE_CLASS_ID ,
46 RES_TYPE_ID ,
47 RES_TYPE_ENABLED_FLAG ,
48 RESOURCE_TYPE_DISP_CHARS ,
49 ORGN_ENABLED_FLAG ,
50 ORGN_DISP_CHARS ,
51 FIN_CAT_ENABLED_FLAG ,
52 FIN_CAT_DISP_CHARS ,
53 INCURRED_BY_ENABLED_FLAG ,
54 INCURRED_BY_DISP_CHARS ,
55 SUPPLIER_ENABLED_FLAG ,
56 SUPPLIER_DISP_CHARS ,
57 ROLE_ENABLED_FLAG ,
58 ROLE_DISP_CHARS ,
59 RESOURCE_CLASS_FLAG ,
60 CREATION_DATE ,
61 CREATED_BY ,
62 LAST_UPDATE_DATE ,
63 LAST_UPDATED_BY ,
64 LAST_UPDATE_LOGIN
65 ) values (
66 L_RES_FORMAT_ID ,
67 P_RESOURCE_FORMAT_SEQ ,
68 P_RESOURCE_CLASS_ID ,
69 P_RES_TYPE_ID ,
70 P_RES_TYPE_ENABLED_FLAG ,
71 P_RESOURCE_TYPE_DISP_CHARS ,
72 P_ORGN_ENABLED_FLAG ,
73 P_ORGN_DISP_CHARS ,
74 P_FIN_CAT_ENABLED_FLAG ,
75 P_FIN_CAT_DISP_CHARS ,
76 P_INCURRED_BY_ENABLED_FLAG ,
77 P_INCURRED_BY_DISP_CHARS ,
78 P_SUPPLIER_ENABLED_FLAG ,
79 P_SUPPLIER_DISP_CHARS ,
80 P_ROLE_ENABLED_FLAG ,
81 P_ROLE_DISP_CHARS ,
82 P_RESOURCE_CLASS_FLAG ,
83 P_CREATION_DATE ,
84 P_CREATED_BY ,
85 P_LAST_UPDATE_DATE ,
86 P_LAST_UPDATED_BY ,
87 P_LAST_UPDATE_LOGIN
88 );
89
90 insert into pa_res_formats_tl (
91 LAST_UPDATE_LOGIN,
92 CREATION_DATE,
93 CREATED_BY,
94 LAST_UPDATE_DATE,
95 LAST_UPDATED_BY,
96 RES_FORMAT_ID,
97 NAME,
98 DESCRIPTION,
99 LANGUAGE,
100 SOURCE_LANG
101 ) select
102 P_LAST_UPDATE_LOGIN,
103 P_CREATION_DATE,
104 P_CREATED_BY,
105 P_LAST_UPDATE_DATE,
106 P_LAST_UPDATED_BY,
107 L_RES_FORMAT_ID,
108 P_NAME,
109 P_DESCRIPTION,
110 L.LANGUAGE_CODE,
111 userenv('LANG')
112 from FND_LANGUAGES L
113 where L.INSTALLED_FLAG in ('I', 'B')
114 and not exists
115 (select NULL
116 from pa_res_formats_tl T
117 where T.RES_FORMAT_ID = L_RES_FORMAT_ID
118 and T.LANGUAGE = L.LANGUAGE_CODE);
119
120 open c;
121 fetch c into X_ROWID;
122 if (c%notfound) then
123 close c;
124 raise no_data_found;
125 end if;
126 close c;
127
128 end INSERT_ROW;
129
130 procedure LOCK_ROW (
131 P_RES_FORMAT_ID in NUMBER
132 ) is
133 cursor c is select
134 RESOURCE_FORMAT_SEQ ,
135 RESOURCE_CLASS_ID ,
136 RES_TYPE_ID ,
137 RES_TYPE_ENABLED_FLAG ,
138 RESOURCE_TYPE_DISP_CHARS ,
139 ORGN_ENABLED_FLAG ,
140 ORGN_DISP_CHARS ,
141 FIN_CAT_ENABLED_FLAG ,
142 FIN_CAT_DISP_CHARS ,
143 INCURRED_BY_ENABLED_FLAG ,
144 INCURRED_BY_DISP_CHARS ,
145 SUPPLIER_ENABLED_FLAG ,
146 SUPPLIER_DISP_CHARS ,
147 ROLE_ENABLED_FLAG ,
148 ROLE_DISP_CHARS ,
149 RESOURCE_CLASS_FLAG
150 from pa_res_formats_b
151 where RES_FORMAT_ID = P_RES_FORMAT_ID
152 for update of RES_FORMAT_ID nowait;
153 recinfo c%rowtype;
154
155 cursor c1 is select
156 NAME,
157 DESCRIPTION,
158 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
159 from pa_res_formats_tl
160 where RES_FORMAT_ID = P_RES_FORMAT_ID
161 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
162 for update of RES_FORMAT_ID nowait;
163 begin
164
165 open c;
166 fetch c into recinfo;
167 if (c%notfound) then
168 close c;
169 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
170 app_exception.raise_exception;
171 end if;
172 close c;
173
174 return;
175
176 end LOCK_ROW;
177
178 procedure UPDATE_ROW (
179 P_RES_FORMAT_ID in NUMBER,
180 P_RESOURCE_FORMAT_SEQ in NUMBER,
181 P_RESOURCE_CLASS_ID in NUMBER,
182 P_RES_TYPE_ID in NUMBER,
183 P_RES_TYPE_ENABLED_FLAG in VARCHAR2,
184 P_RESOURCE_TYPE_DISP_CHARS in NUMBER,
185 P_ORGN_ENABLED_FLAG in VARCHAR2,
186 P_ORGN_DISP_CHARS in NUMBER,
187 P_FIN_CAT_ENABLED_FLAG in VARCHAR2,
188 P_FIN_CAT_DISP_CHARS in NUMBER,
189 P_INCURRED_BY_ENABLED_FLAG in VARCHAR2,
190 P_INCURRED_BY_DISP_CHARS in NUMBER,
191 P_SUPPLIER_ENABLED_FLAG in VARCHAR2,
192 P_SUPPLIER_DISP_CHARS in NUMBER,
193 P_ROLE_ENABLED_FLAG in VARCHAR2,
194 P_ROLE_DISP_CHARS in NUMBER,
195 P_RESOURCE_CLASS_FLAG in VARCHAR2,
196 P_NAME in VARCHAR2,
197 P_DESCRIPTION in VARCHAR2,
198 P_LAST_UPDATE_DATE in DATE ,
199 P_LAST_UPDATED_BY in NUMBER ,
200 P_LAST_UPDATE_LOGIN in NUMBER
201 ) is
202 begin
203 update pa_res_formats_b set
204 RESOURCE_FORMAT_SEQ = P_RESOURCE_FORMAT_SEQ,
205 RESOURCE_CLASS_ID = P_RESOURCE_CLASS_ID,
206 RES_TYPE_ID = P_RES_TYPE_ID,
207 RES_TYPE_ENABLED_FLAG = P_RES_TYPE_ENABLED_FLAG,
208 RESOURCE_TYPE_DISP_CHARS = P_RESOURCE_TYPE_DISP_CHARS,
209 ORGN_ENABLED_FLAG = P_ORGN_ENABLED_FLAG,
210 ORGN_DISP_CHARS = P_ORGN_DISP_CHARS,
211 FIN_CAT_ENABLED_FLAG = P_FIN_CAT_ENABLED_FLAG,
212 FIN_CAT_DISP_CHARS = P_FIN_CAT_DISP_CHARS,
213 INCURRED_BY_ENABLED_FLAG = P_INCURRED_BY_ENABLED_FLAG,
214 INCURRED_BY_DISP_CHARS = P_INCURRED_BY_DISP_CHARS,
215 SUPPLIER_ENABLED_FLAG = P_SUPPLIER_ENABLED_FLAG,
216 SUPPLIER_DISP_CHARS = P_SUPPLIER_DISP_CHARS,
217 ROLE_ENABLED_FLAG = P_ROLE_ENABLED_FLAG,
218 ROLE_DISP_CHARS = P_ROLE_DISP_CHARS,
219 RESOURCE_CLASS_FLAG = P_RESOURCE_CLASS_FLAG,
220 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
221 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
222 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
223 where res_format_id = P_RES_FORMAT_ID;
224
225 if (sql%notfound) then
226 raise no_data_found;
227 end if;
228
229 update pa_res_formats_tl set
230 NAME = P_NAME,
231 DESCRIPTION = P_DESCRIPTION,
232 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
233 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
234 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
235 SOURCE_LANG = userenv('LANG')
236 where RES_FORMAT_ID = P_RES_FORMAT_ID
237 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
238
239 if (sql%notfound) then
240 raise no_data_found;
241 end if;
242 end UPDATE_ROW;
243
244 procedure DELETE_ROW (
245 P_RES_FORMAT_ID in NUMBER
246 ) is
247 begin
248 delete from pa_res_formats_tl
249 where RES_FORMAT_ID = P_RES_FORMAT_ID;
250
251 if (sql%notfound) then
252 raise no_data_found;
253 end if;
254
255 delete from pa_res_formats_b
256 where RES_FORMAT_ID = P_RES_FORMAT_ID;
257
258 if (sql%notfound) then
259 raise no_data_found;
260 end if;
261 end DELETE_ROW;
262
263 procedure ADD_LANGUAGE
264 is
265 begin
266 delete from pa_res_formats_tl T
267 where not exists
268 (select NULL
269 from PA_RES_FORMATS_B B
270 where B.RES_FORMAT_ID = T.res_format_id
271 );
272
273 update pa_res_formats_tl T set (
274 NAME,
275 DESCRIPTION
276 ) = (select
277 B.NAME,
278 B.DESCRIPTION
279 from pa_res_formats_tl B
280 where B.RES_FORMAT_ID = T.RES_FORMAT_ID
281 and B.LANGUAGE = T.SOURCE_LANG)
282 where (
283 T.RES_FORMAT_ID,
284 T.LANGUAGE
285 ) in (select
286 SUBT.RES_FORMAT_ID,
287 SUBT.LANGUAGE
288 from pa_res_formats_tl SUBB, pa_res_formats_tl SUBT
289 where SUBB.RES_FORMAT_ID = SUBT.RES_FORMAT_ID
290 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
291 and (SUBB.NAME <> SUBT.NAME
292 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
293 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
294 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
295 ));
296
297 insert into pa_res_formats_tl (
298 LAST_UPDATE_LOGIN,
299 CREATION_DATE,
300 CREATED_BY,
301 LAST_UPDATE_DATE,
302 LAST_UPDATED_BY,
303 res_format_id,
304 NAME,
305 DESCRIPTION,
306 LANGUAGE,
307 SOURCE_LANG
308 ) select
309 B.LAST_UPDATE_LOGIN,
310 B.CREATION_DATE,
311 B.CREATED_BY,
312 B.LAST_UPDATE_DATE,
313 B.LAST_UPDATED_BY,
314 B.RES_FORMAT_ID,
315 B.NAME,
316 B.DESCRIPTION,
317 L.LANGUAGE_CODE,
318 B.SOURCE_LANG
319 from pa_res_formats_tl B, FND_LANGUAGES L
320 where L.INSTALLED_FLAG in ('I', 'B')
321 and B.LANGUAGE = userenv('LANG')
322 and not exists
323 (select NULL
324 from pa_res_formats_tl T
325 where T.RES_FORMAT_ID = B.RES_FORMAT_ID
326 and T.LANGUAGE = L.LANGUAGE_CODE);
327 end ADD_LANGUAGE;
328
329 procedure TRANSLATE_ROW(
330 P_RES_FORMAT_ID in NUMBER ,
331 P_OWNER in VARCHAR2 ,
332 P_NAME in VARCHAR2 ,
333 P_DESCRIPTION in VARCHAR2
334 ) is
335 begin
336
337 update pa_res_formats_tl set
338 NAME = P_NAME,
339 DESCRIPTION = P_DESCRIPTION,
340 LAST_UPDATE_DATE = sysdate,
341 LAST_UPDATED_BY = decode(P_OWNER, 'SEED', 1, 0),
342 LAST_UPDATE_LOGIN = 0,
343 SOURCE_LANG = userenv('LANG') --For bug 4129599
344 where res_format_id = P_RES_FORMAT_ID
345 /*Bug4129599- Changes Start */
346 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
347 -- (select LANGUAGE_CODE from FND_LANGUAGES where INSTALLED_FLAG = 'B');
348 /*Bug4129599- Changes End */
349
350 if (sql%notfound) then
351 raise no_data_found;
352 end if;
353
354 end TRANSLATE_ROW;
355
356 procedure LOAD_ROW(
357 P_RES_FORMAT_ID in NUMBER,
358 P_RESOURCE_FORMAT_SEQ in NUMBER,
359 P_RESOURCE_CLASS_ID in NUMBER,
360 P_RES_TYPE_ID in NUMBER,
361 P_RES_TYPE_ENABLED_FLAG in VARCHAR2,
362 P_RESOURCE_TYPE_DISP_CHARS in NUMBER,
363 P_ORGN_ENABLED_FLAG in VARCHAR2,
364 P_ORGN_DISP_CHARS in NUMBER,
365 P_FIN_CAT_ENABLED_FLAG in VARCHAR2,
366 P_FIN_CAT_DISP_CHARS in NUMBER,
367 P_INCURRED_BY_ENABLED_FLAG in VARCHAR2,
368 P_INCURRED_BY_DISP_CHARS in NUMBER,
369 P_SUPPLIER_ENABLED_FLAG in VARCHAR2,
370 P_SUPPLIER_DISP_CHARS in NUMBER,
371 P_ROLE_ENABLED_FLAG in VARCHAR2,
372 P_ROLE_DISP_CHARS in NUMBER,
373 P_RESOURCE_CLASS_FLAG in VARCHAR2,
374 P_NAME in VARCHAR2,
375 P_DESCRIPTION in VARCHAR2,
376 P_OWNER in VARCHAR2
377 ) is
378
379 user_id NUMBER;
380 l_rowid VARCHAR2(64);
381
382 begin
383
384 if (P_OWNER = 'SEED')then
385 user_id := 1;
386 else
387 user_id :=0;
388 end if;
389
390 pa_res_formats_pvt.UPDATE_ROW (
391 P_RES_FORMAT_ID => P_RES_FORMAT_ID ,
395 P_RES_TYPE_ENABLED_FLAG => P_RES_TYPE_ENABLED_FLAG,
392 P_RESOURCE_FORMAT_SEQ => P_RESOURCE_FORMAT_SEQ,
393 P_RESOURCE_CLASS_ID => P_RESOURCE_CLASS_ID,
394 P_RES_TYPE_ID => P_RES_TYPE_ID,
396 P_RESOURCE_TYPE_DISP_CHARS => P_RESOURCE_TYPE_DISP_CHARS,
397 P_ORGN_ENABLED_FLAG => P_ORGN_ENABLED_FLAG,
398 P_ORGN_DISP_CHARS => P_ORGN_DISP_CHARS,
399 P_FIN_CAT_ENABLED_FLAG => P_FIN_CAT_ENABLED_FLAG,
400 P_FIN_CAT_DISP_CHARS => P_FIN_CAT_DISP_CHARS,
401 P_INCURRED_BY_ENABLED_FLAG => P_INCURRED_BY_ENABLED_FLAG,
402 P_INCURRED_BY_DISP_CHARS => P_INCURRED_BY_DISP_CHARS,
403 P_SUPPLIER_ENABLED_FLAG => P_SUPPLIER_ENABLED_FLAG,
404 P_SUPPLIER_DISP_CHARS => P_SUPPLIER_DISP_CHARS,
405 P_ROLE_ENABLED_FLAG => P_ROLE_ENABLED_FLAG,
406 P_ROLE_DISP_CHARS => P_ROLE_DISP_CHARS,
407 P_RESOURCE_CLASS_FLAG => P_RESOURCE_CLASS_FLAG,
408 P_NAME => P_NAME ,
409 P_DESCRIPTION => P_DESCRIPTION ,
410 P_LAST_UPDATE_DATE => sysdate ,
411 P_LAST_UPDATED_BY => user_id ,
412 P_LAST_UPDATE_LOGIN => 0 );
413
414 EXCEPTION
415 WHEN no_data_found then
416 pa_res_formats_pvt.INSERT_ROW (
417 X_ROWID => l_rowid ,
418 P_RES_FORMAT_ID => P_RES_FORMAT_ID ,
419 P_RESOURCE_FORMAT_SEQ => P_RESOURCE_FORMAT_SEQ,
420 P_RESOURCE_CLASS_ID => P_RESOURCE_CLASS_ID,
421 P_RES_TYPE_ID => P_RES_TYPE_ID,
422 P_RES_TYPE_ENABLED_FLAG => P_RES_TYPE_ENABLED_FLAG,
423 P_RESOURCE_TYPE_DISP_CHARS => P_RESOURCE_TYPE_DISP_CHARS,
424 P_ORGN_ENABLED_FLAG => P_ORGN_ENABLED_FLAG,
425 P_ORGN_DISP_CHARS => P_ORGN_DISP_CHARS,
426 P_FIN_CAT_ENABLED_FLAG => P_FIN_CAT_ENABLED_FLAG,
427 P_FIN_CAT_DISP_CHARS => P_FIN_CAT_DISP_CHARS,
428 P_INCURRED_BY_ENABLED_FLAG => P_INCURRED_BY_ENABLED_FLAG,
429 P_INCURRED_BY_DISP_CHARS => P_INCURRED_BY_DISP_CHARS,
430 P_SUPPLIER_ENABLED_FLAG => P_SUPPLIER_ENABLED_FLAG,
431 P_SUPPLIER_DISP_CHARS => P_SUPPLIER_DISP_CHARS,
432 P_ROLE_ENABLED_FLAG => P_ROLE_ENABLED_FLAG,
433 P_ROLE_DISP_CHARS => P_ROLE_DISP_CHARS,
434 P_RESOURCE_CLASS_FLAG => P_RESOURCE_CLASS_FLAG,
435 P_NAME => P_NAME ,
436 P_DESCRIPTION => P_DESCRIPTION ,
437 P_CREATION_DATE => sysdate ,
438 P_CREATED_BY => user_id ,
439 P_LAST_UPDATE_DATE => sysdate ,
440 P_LAST_UPDATED_BY => user_id ,
441 P_LAST_UPDATE_LOGIN => 0 );
442 end LOAD_ROW;
443
444 end pa_res_formats_pvt;