[Home] [Help]
PACKAGE BODY: APPS.AR_BPA_DATASRC_VIEWS_PKG
Source
1 package body AR_BPA_DATASRC_VIEWS_PKG as
2 /* $Header: ARBPDSVB.pls 120.2 2005/10/30 04:13:28 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_DATA_SOURCE_ID in NUMBER,
6 X_OBJECT_TYPE in VARCHAR2,
7 X_OBJECT_NAME in VARCHAR2,
8 X_VO_USAGE_NAME in VARCHAR2,
9 X_VO_USAGE_FULL_NAME in VARCHAR2,
10 X_VO_INIT_SEQUENCE in NUMBER,
11 X_DISPLAY_LEVEL in VARCHAR2,
12 X_LINK_TO_DATA_SOURCE_ID in NUMBER,
13 X_INVOICED_LINE_ACCTG_LEVEL in VARCHAR2,
14 X_TAX_SOURCE_FLAG in VARCHAR2,
15 X_SOURCE_LINE_TYPE in VARCHAR2,
16 X_ENABLED_FLAG in VARCHAR2,
17 X_SEEDED_FLAG in VARCHAR2,
18 X_APPLICATION_ID in NUMBER,
19 X_DATASRC_VIEW_NAME in VARCHAR2,
20 X_DATASRC_VIEW_DESC in VARCHAR2,
21 X_CREATION_DATE in DATE,
22 X_CREATED_BY in NUMBER,
23 X_LAST_UPDATE_DATE in DATE,
24 X_LAST_UPDATED_BY in NUMBER,
25 X_LAST_UPDATE_LOGIN in NUMBER
26 ) is
27 cursor C is select ROWID from AR_BPA_DATA_SOURCES_B
28 where DATA_SOURCE_ID = X_DATA_SOURCE_ID
29 ;
30 begin
31 insert into AR_BPA_DATA_SOURCES_B (
32 OBJECT_TYPE,
33 OBJECT_NAME,
34 VO_USAGE_NAME,
35 VO_USAGE_FULL_NAME,
36 VO_INIT_SEQUENCE,
37 DISPLAY_LEVEL,
38 LINK_TO_DATA_SOURCE_ID,
39 INVOICED_LINE_ACCTG_LEVEL,
40 TAX_SOURCE_FLAG,
41 SOURCE_LINE_TYPE,
42 ENABLED_FLAG,
43 SEEDED_FLAG,
44 DATA_SOURCE_ID,
45 APPLICATION_ID,
46 CREATION_DATE,
47 CREATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN
51 ) values (
52 X_OBJECT_TYPE,
53 X_OBJECT_NAME,
54 X_VO_USAGE_NAME,
55 X_VO_USAGE_FULL_NAME,
56 X_VO_INIT_SEQUENCE,
57 X_DISPLAY_LEVEL,
58 X_LINK_TO_DATA_SOURCE_ID,
59 X_INVOICED_LINE_ACCTG_LEVEL,
60 X_TAX_SOURCE_FLAG,
61 X_SOURCE_LINE_TYPE,
62 X_ENABLED_FLAG,
63 X_SEEDED_FLAG,
64 X_DATA_SOURCE_ID,
65 X_APPLICATION_ID,
66 X_CREATION_DATE,
67 X_CREATED_BY,
68 X_LAST_UPDATE_DATE,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_LOGIN
71 );
72
73 insert into AR_BPA_DATA_SOURCES_TL (
74 DATA_SOURCE_ID,
75 DATASRC_VIEW_NAME,
76 DATASRC_VIEW_DESC,
77 CREATED_BY,
78 CREATION_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_DATE,
81 LAST_UPDATE_LOGIN,
82 LANGUAGE,
83 SOURCE_LANG
84 ) select
85 X_DATA_SOURCE_ID,
86 X_DATASRC_VIEW_NAME,
87 X_DATASRC_VIEW_DESC,
88 X_CREATED_BY,
89 X_CREATION_DATE,
90 X_LAST_UPDATED_BY,
91 X_LAST_UPDATE_DATE,
92 X_LAST_UPDATE_LOGIN,
93 L.LANGUAGE_CODE,
94 userenv('LANG')
95 from FND_LANGUAGES L
96 where L.INSTALLED_FLAG in ('I', 'B')
97 and not exists
98 (select NULL
99 from AR_BPA_DATA_SOURCES_TL T
100 where T.DATA_SOURCE_ID = X_DATA_SOURCE_ID
101 and T.LANGUAGE = L.LANGUAGE_CODE);
102
103 open c;
104 fetch c into X_ROWID;
105 if (c%notfound) then
106 close c;
107 raise no_data_found;
108 end if;
109 close c;
110
111 end INSERT_ROW;
112
113 procedure LOCK_ROW (
114 X_DATA_SOURCE_ID in NUMBER,
115 X_OBJECT_TYPE in VARCHAR2,
116 X_OBJECT_NAME in VARCHAR2,
117 X_VO_USAGE_NAME in VARCHAR2,
118 X_VO_USAGE_FULL_NAME in VARCHAR2,
119 X_VO_INIT_SEQUENCE in NUMBER,
120 X_DISPLAY_LEVEL in VARCHAR2,
121 X_LINK_TO_DATA_SOURCE_ID in NUMBER,
122 X_INVOICED_LINE_ACCTG_LEVEL in VARCHAR2,
123 X_TAX_SOURCE_FLAG in VARCHAR2,
124 X_SOURCE_LINE_TYPE in VARCHAR2,
125 X_ENABLED_FLAG in VARCHAR2,
126 X_SEEDED_FLAG in VARCHAR2,
127 X_APPLICATION_ID in NUMBER,
128 X_DATASRC_VIEW_NAME in VARCHAR2,
129 X_DATASRC_VIEW_DESC in VARCHAR2
130 ) is
131 cursor c is select
132 OBJECT_TYPE,
133 OBJECT_NAME,
134 VO_USAGE_NAME,
135 VO_USAGE_FULL_NAME,
136 VO_INIT_SEQUENCE,
137 DISPLAY_LEVEL,
138 LINK_TO_DATA_SOURCE_ID,
139 INVOICED_LINE_ACCTG_LEVEL,
140 TAX_SOURCE_FLAG,
141 SOURCE_LINE_TYPE,
142 ENABLED_FLAG,
143 SEEDED_FLAG,
144 APPLICATION_ID
145 from AR_BPA_DATA_SOURCES_B
146 where DATA_SOURCE_ID = X_DATA_SOURCE_ID
147 for update of DATA_SOURCE_ID nowait;
148 recinfo c%rowtype;
149
150 cursor c1 is select
151 DATASRC_VIEW_NAME,
152 DATASRC_VIEW_DESC,
153 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
154 from AR_BPA_DATA_SOURCES_TL
155 where DATA_SOURCE_ID = X_DATA_SOURCE_ID
156 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
157 for update of DATA_SOURCE_ID nowait;
158 begin
159 open c;
160 fetch c into recinfo;
161 if (c%notfound) then
162 close c;
163 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
164 app_exception.raise_exception;
165 end if;
166 close c;
167 if ( ((recinfo.OBJECT_TYPE = X_OBJECT_TYPE)
168 OR ((recinfo.OBJECT_TYPE is null) AND (X_OBJECT_TYPE is null)))
169 AND ((recinfo.OBJECT_NAME = X_OBJECT_NAME)
170 OR ((recinfo.OBJECT_NAME is null) AND (X_OBJECT_NAME is null)))
171 AND ((recinfo.VO_USAGE_NAME = X_VO_USAGE_NAME)
172 OR ((recinfo.VO_USAGE_NAME is null) AND (X_VO_USAGE_NAME is null)))
173 AND ((recinfo.VO_USAGE_FULL_NAME = X_VO_USAGE_FULL_NAME)
174 OR ((recinfo.VO_USAGE_FULL_NAME is null) AND (X_VO_USAGE_FULL_NAME is null)))
175 AND (recinfo.VO_INIT_SEQUENCE = X_VO_INIT_SEQUENCE)
176 AND (recinfo.DISPLAY_LEVEL = X_DISPLAY_LEVEL)
177 AND ((recinfo.LINK_TO_DATA_SOURCE_ID = X_LINK_TO_DATA_SOURCE_ID)
178 OR ((recinfo.LINK_TO_DATA_SOURCE_ID is null) AND (X_LINK_TO_DATA_SOURCE_ID is null)))
179 AND ((recinfo.INVOICED_LINE_ACCTG_LEVEL = X_INVOICED_LINE_ACCTG_LEVEL)
180 OR ((recinfo.INVOICED_LINE_ACCTG_LEVEL is null) AND (X_INVOICED_LINE_ACCTG_LEVEL is null)))
181 AND ((recinfo.TAX_SOURCE_FLAG = X_TAX_SOURCE_FLAG)
182 OR ((recinfo.TAX_SOURCE_FLAG is null) AND (X_TAX_SOURCE_FLAG is null)))
183 AND ((recinfo.SOURCE_LINE_TYPE = X_SOURCE_LINE_TYPE)
184 OR ((recinfo.SOURCE_LINE_TYPE is null) AND (X_SOURCE_LINE_TYPE is null)))
185 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
186 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
187 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
188 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
189 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
190 ) then
191 null;
192 else
193 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
194 app_exception.raise_exception;
195 end if;
196
197 for tlinfo in c1 loop
198 if (tlinfo.BASELANG = 'Y') then
199 if ( (tlinfo.DATASRC_VIEW_NAME = X_DATASRC_VIEW_NAME)
200 AND ((tlinfo.DATASRC_VIEW_DESC = X_DATASRC_VIEW_DESC)
201 OR ((tlinfo.DATASRC_VIEW_DESC is null) AND (X_DATASRC_VIEW_DESC is null)))
202 ) then
203 null;
204 else
205 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
206 app_exception.raise_exception;
207 end if;
208 end if;
209 end loop;
210 return;
211 end LOCK_ROW;
212
213 procedure UPDATE_ROW (
214 X_DATA_SOURCE_ID in NUMBER,
215 X_OBJECT_TYPE in VARCHAR2,
216 X_OBJECT_NAME in VARCHAR2,
217 X_VO_USAGE_NAME in VARCHAR2,
218 X_VO_USAGE_FULL_NAME in VARCHAR2,
219 X_VO_INIT_SEQUENCE in NUMBER,
220 X_DISPLAY_LEVEL in VARCHAR2,
221 X_LINK_TO_DATA_SOURCE_ID in NUMBER,
222 X_INVOICED_LINE_ACCTG_LEVEL in VARCHAR2,
223 X_TAX_SOURCE_FLAG in VARCHAR2,
224 X_SOURCE_LINE_TYPE in VARCHAR2,
225 X_ENABLED_FLAG in VARCHAR2,
226 X_SEEDED_FLAG in VARCHAR2,
227 X_APPLICATION_ID in NUMBER,
228 X_DATASRC_VIEW_NAME in VARCHAR2,
229 X_DATASRC_VIEW_DESC in VARCHAR2,
230 X_LAST_UPDATE_DATE in DATE,
231 X_LAST_UPDATED_BY in NUMBER,
232 X_LAST_UPDATE_LOGIN in NUMBER
233 ) is
234 begin
235 update AR_BPA_DATA_SOURCES_B set
236 OBJECT_TYPE = X_OBJECT_TYPE,
237 OBJECT_NAME = X_OBJECT_NAME,
238 VO_USAGE_NAME = X_VO_USAGE_NAME,
239 VO_USAGE_FULL_NAME = X_VO_USAGE_FULL_NAME,
240 VO_INIT_SEQUENCE = X_VO_INIT_SEQUENCE,
241 DISPLAY_LEVEL = X_DISPLAY_LEVEL,
242 LINK_TO_DATA_SOURCE_ID = X_LINK_TO_DATA_SOURCE_ID,
243 INVOICED_LINE_ACCTG_LEVEL = X_INVOICED_LINE_ACCTG_LEVEL,
244 TAX_SOURCE_FLAG = X_TAX_SOURCE_FLAG,
245 SOURCE_LINE_TYPE = X_SOURCE_LINE_TYPE,
246 ENABLED_FLAG = X_ENABLED_FLAG,
247 SEEDED_FLAG = X_SEEDED_FLAG,
248 APPLICATION_ID = X_APPLICATION_ID,
249 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
250 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
251 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
252 where DATA_SOURCE_ID = X_DATA_SOURCE_ID;
253
254 if (sql%notfound) then
255 raise no_data_found;
256 end if;
257
258 update AR_BPA_DATA_SOURCES_TL set
259 DATASRC_VIEW_NAME = X_DATASRC_VIEW_NAME,
260 DATASRC_VIEW_DESC = X_DATASRC_VIEW_DESC,
261 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
262 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
264 SOURCE_LANG = userenv('LANG')
265 where DATA_SOURCE_ID = X_DATA_SOURCE_ID
266 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
267
268 if (sql%notfound) then
269 raise no_data_found;
270 end if;
271 end UPDATE_ROW;
272
273 procedure DELETE_ROW (
274 X_DATA_SOURCE_ID in NUMBER
275 ) is
276 begin
277 /* First delete from Item table. then delete from the view item GT table */
278 delete from AR_BPA_ITEMS_TL
279 where item_id in (select item_id from ar_bpa_items_b
280 where data_source_id = X_DATA_SOURCE_ID);
281
282 delete from AR_BPA_ITEMS_B
283 where DATA_SOURCE_ID = X_DATA_SOURCE_ID;
284
285 delete from AR_BPA_DATA_SOURCES_TL
286 where DATA_SOURCE_ID = X_DATA_SOURCE_ID;
287
288 if (sql%notfound) then
289 raise no_data_found;
290 end if;
291
292 delete from AR_BPA_DATA_SOURCES_B
293 where DATA_SOURCE_ID = X_DATA_SOURCE_ID;
294
295 if (sql%notfound) then
296 raise no_data_found;
297 end if;
298 end DELETE_ROW;
299
300 procedure ADD_LANGUAGE
301 is
302 begin
303 delete from AR_BPA_DATA_SOURCES_TL T
304 where not exists
305 (select NULL
306 from AR_BPA_DATA_SOURCES_B B
307 where B.DATA_SOURCE_ID = T.DATA_SOURCE_ID
308 );
309
310 update AR_BPA_DATA_SOURCES_TL T set (
311 DATASRC_VIEW_NAME,
312 DATASRC_VIEW_DESC
313 ) = (select
314 B.DATASRC_VIEW_NAME,
315 B.DATASRC_VIEW_DESC
316 from AR_BPA_DATA_SOURCES_TL B
317 where B.DATA_SOURCE_ID = T.DATA_SOURCE_ID
318 and B.LANGUAGE = T.SOURCE_LANG)
319 where (
320 T.DATA_SOURCE_ID,
321 T.LANGUAGE
322 ) in (select
323 SUBT.DATA_SOURCE_ID,
324 SUBT.LANGUAGE
325 from AR_BPA_DATA_SOURCES_TL SUBB, AR_BPA_DATA_SOURCES_TL SUBT
326 where SUBB.DATA_SOURCE_ID = SUBT.DATA_SOURCE_ID
327 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
328 and (SUBB.DATASRC_VIEW_NAME <> SUBT.DATASRC_VIEW_NAME
329 or SUBB.DATASRC_VIEW_DESC <> SUBT.DATASRC_VIEW_DESC
330 or (SUBB.DATASRC_VIEW_DESC is null and SUBT.DATASRC_VIEW_DESC is not null)
331 or (SUBB.DATASRC_VIEW_DESC is not null and SUBT.DATASRC_VIEW_DESC is null)
332 ));
333
334 insert into AR_BPA_DATA_SOURCES_TL (
335 DATA_SOURCE_ID,
336 DATASRC_VIEW_NAME,
337 DATASRC_VIEW_DESC,
338 CREATED_BY,
339 CREATION_DATE,
340 LAST_UPDATED_BY,
341 LAST_UPDATE_DATE,
342 LAST_UPDATE_LOGIN,
343 LANGUAGE,
344 SOURCE_LANG
345 ) select
346 B.DATA_SOURCE_ID,
347 B.DATASRC_VIEW_NAME,
348 B.DATASRC_VIEW_DESC,
349 B.CREATED_BY,
350 B.CREATION_DATE,
351 B.LAST_UPDATED_BY,
352 B.LAST_UPDATE_DATE,
353 B.LAST_UPDATE_LOGIN,
354 L.LANGUAGE_CODE,
355 B.SOURCE_LANG
356 from AR_BPA_DATA_SOURCES_TL B, FND_LANGUAGES L
357 where L.INSTALLED_FLAG in ('I', 'B')
358 and B.LANGUAGE = userenv('LANG')
359 and not exists
360 (select NULL
361 from AR_BPA_DATA_SOURCES_TL T
362 where T.DATA_SOURCE_ID = B.DATA_SOURCE_ID
363 and T.LANGUAGE = L.LANGUAGE_CODE);
364 end ADD_LANGUAGE;
365
366 procedure TRANSLATE_ROW (
367 X_DATA_SOURCE_ID in NUMBER,
368 X_DATASRC_VIEW_NAME in VARCHAR2,
369 X_DATASRC_VIEW_DESC in VARCHAR2,
370 X_OWNER in VARCHAR2) IS
371 begin
372
373 update AR_BPA_DATA_SOURCES_TL
374 set DATASRC_VIEW_NAME = X_DATASRC_VIEW_NAME,
375 DATASRC_VIEW_DESC = X_DATASRC_VIEW_DESC,
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 DATA_SOURCE_ID = X_DATA_SOURCE_ID
381 and userenv('LANG') in (language, source_lang);
382
383 end TRANSLATE_ROW;
384
385 procedure LOAD_ROW (
386 X_DATA_SOURCE_ID in NUMBER,
387 X_OBJECT_TYPE in VARCHAR2,
388 X_OBJECT_NAME in VARCHAR2,
389 X_VO_USAGE_NAME in VARCHAR2,
390 X_VO_USAGE_FULL_NAME in VARCHAR2,
391 X_VO_INIT_SEQUENCE in NUMBER,
392 X_DISPLAY_LEVEL in VARCHAR2,
393 X_LINK_TO_DATA_SOURCE_ID in NUMBER,
394 X_INVOICED_LINE_ACCTG_LEVEL in VARCHAR2,
395 X_TAX_SOURCE_FLAG in VARCHAR2,
396 X_SOURCE_LINE_TYPE in VARCHAR2,
397 X_ENABLED_FLAG in VARCHAR2,
398 X_SEEDED_FLAG in VARCHAR2,
399 X_APPLICATION_ID in NUMBER,
400 X_DATASRC_VIEW_NAME in VARCHAR2,
401 X_DATASRC_VIEW_DESC in VARCHAR2,
402 X_OWNER IN VARCHAR2
403 ) IS
404 begin
405 declare
409 if (X_OWNER = 'SEED') then
406 user_id number := 0;
407 row_id varchar2(64);
408 begin
410 user_id := 1;
411 end if;
412
413 AR_BPA_DATASRC_VIEWS_PKG.UPDATE_ROW (
414 X_DATA_SOURCE_ID => X_DATA_SOURCE_ID,
415 X_OBJECT_TYPE => X_OBJECT_TYPE,
416 X_OBJECT_NAME => X_OBJECT_NAME,
417 X_VO_USAGE_NAME => X_VO_USAGE_NAME,
418 X_VO_USAGE_FULL_NAME => X_VO_USAGE_FULL_NAME,
419 X_VO_INIT_SEQUENCE => X_VO_INIT_SEQUENCE ,
420 X_DISPLAY_LEVEL => X_DISPLAY_LEVEL,
421 X_LINK_TO_DATA_SOURCE_ID => X_LINK_TO_DATA_SOURCE_ID,
422 X_INVOICED_LINE_ACCTG_LEVEL => X_INVOICED_LINE_ACCTG_LEVEL,
423 X_TAX_SOURCE_FLAG => X_TAX_SOURCE_FLAG,
424 X_SOURCE_LINE_TYPE => X_SOURCE_LINE_TYPE,
425 X_ENABLED_FLAG => X_ENABLED_FLAG ,
426 X_SEEDED_FLAG => X_SEEDED_FLAG,
427 X_APPLICATION_ID => X_APPLICATION_ID ,
428 X_DATASRC_VIEW_NAME => X_DATASRC_VIEW_NAME,
429 X_DATASRC_VIEW_DESC => X_DATASRC_VIEW_DESC ,
430 X_LAST_UPDATE_DATE => sysdate,
431 X_LAST_UPDATED_BY => user_id,
432 X_LAST_UPDATE_LOGIN => 0);
433 exception
434 when NO_DATA_FOUND then
435 AR_BPA_DATASRC_VIEWS_PKG.INSERT_ROW (
436 X_ROWID => row_id,
437 X_DATA_SOURCE_ID => X_DATA_SOURCE_ID,
438 X_OBJECT_TYPE => X_OBJECT_TYPE,
439 X_OBJECT_NAME => X_OBJECT_NAME,
440 X_VO_USAGE_NAME => X_VO_USAGE_NAME,
441 X_VO_USAGE_FULL_NAME => X_VO_USAGE_FULL_NAME,
442 X_VO_INIT_SEQUENCE => X_VO_INIT_SEQUENCE ,
443 X_DISPLAY_LEVEL => X_DISPLAY_LEVEL,
444 X_LINK_TO_DATA_SOURCE_ID => X_LINK_TO_DATA_SOURCE_ID,
445 X_INVOICED_LINE_ACCTG_LEVEL => X_INVOICED_LINE_ACCTG_LEVEL,
446 X_TAX_SOURCE_FLAG => X_TAX_SOURCE_FLAG,
447 X_SOURCE_LINE_TYPE => X_SOURCE_LINE_TYPE,
448 X_ENABLED_FLAG => X_ENABLED_FLAG ,
449 X_SEEDED_FLAG => X_SEEDED_FLAG,
450 X_APPLICATION_ID => X_APPLICATION_ID ,
451 X_DATASRC_VIEW_NAME => X_DATASRC_VIEW_NAME,
452 X_DATASRC_VIEW_DESC => X_DATASRC_VIEW_DESC ,
453 X_CREATION_DATE => sysdate,
454 X_CREATED_BY => user_id,
455 X_LAST_UPDATE_DATE => sysdate,
456 X_LAST_UPDATED_BY => user_id,
457 X_LAST_UPDATE_LOGIN => 0);
458 end;
459 end LOAD_ROW;
460
461 end AR_BPA_DATASRC_VIEWS_PKG;