[Home] [Help]
PACKAGE BODY: APPS.AR_CMGT_DP_TABLE_HANDLER
Source
1 Package BODY AR_CMGT_DP_TABLE_HANDLER AS
2 /* $Header: ARCMGDTB.pls 120.14 2006/06/29 17:34:17 bsarkar noship $ */
3
4 procedure INSERT_ROW
5 ( p_data_point_name IN VARCHAR2,
6 p_description IN VARCHAR2,
7 p_data_point_category IN VARCHAR2,
8 p_user_defined_flag IN VARCHAR2,
9 p_scorable_flag IN VARCHAR2,
10 p_display_on_checklist IN VARCHAR2,
11 p_created_by IN NUMBER,
12 p_last_updated_by IN NUMBER,
13 p_last_update_login IN NUMBER,
14 p_data_point_id IN NUMBER,
15 p_return_data_type IN VARCHAR2,
16 p_return_date_format IN VARCHAR2,
17 p_application_id IN NUMBER,
18 p_parent_data_point_id IN NUMBER,
19 p_enabled_flag IN VARCHAR2,
20 p_package_name IN VARCHAR2,
21 p_function_name IN VARCHAR2,
22 p_data_point_sub_category IN VARCHAR2,
23 p_data_point_code IN VARCHAR2
24 ) AS
25 BEGIN
26 INSERT INTO AR_CMGT_DATA_POINTS_B
27 ( data_point_id,
28 data_point_category,
29 user_defined_flag,
30 scorable_flag,
31 display_on_checklist_flag,
32 last_updated_by,
33 last_update_date,
34 created_by,
35 creation_date,
36 last_update_login,
37 return_data_type,
38 return_date_format,
39 enabled_flag,
40 application_id,
41 parent_data_point_id,
42 package_name,
43 function_name,
44 data_point_sub_category,
45 data_point_code ) values
46 ( p_data_point_id,
47 p_data_point_category,
48 p_user_defined_flag,
49 p_scorable_flag,
50 p_display_on_checklist,
51 p_last_updated_by,
52 sysdate,
53 p_created_by,
54 sysdate,
55 p_last_update_login,
56 p_return_data_type,
57 p_return_date_format,
58 p_enabled_flag,
59 p_application_id,
60 p_parent_data_point_id,
61 p_package_name,
62 p_function_name,
63 p_data_point_sub_category,
64 p_data_point_code
65 );
66
67 INSERT INTO AR_CMGT_DATA_POINTS_TL
68 ( data_point_id,
69 data_point_name,
70 description,
71 LANGUAGE,
72 source_lang,
73 last_updated_by,
74 last_update_date,
75 created_by,
76 creation_date,
77 last_update_login,
78 application_id) select
79 p_data_point_id,
80 p_data_point_name,
81 p_description,
82 l.language_code,
83 userenv('LANG'),
84 p_last_updated_by,
85 sysdate,
86 p_created_by,
87 sysdate,
88 p_last_update_login,
89 p_application_id
90 from fnd_languages l
91 where l.installed_flag in ('B','I')
92 and not exists (select NULL
93 from AR_CMGT_DATA_POINTS_TL t
94 where T.data_point_id = p_data_point_id
95 and T.LANGUAGE = L.LANGUAGE_CODE);
96
97
98 END;
99
100 PROCEDURE insert_adp_row(
101 p_data_point_code IN VARCHAR2,
102 p_data_point_name IN VARCHAR2,
103 p_description IN VARCHAR2,
104 p_data_point_sub_category IN VARCHAR2,
105 p_data_point_category IN VARCHAR2,
106 p_user_defined_flag IN VARCHAR2,
107 p_scorable_flag IN VARCHAR2,
108 p_display_on_checklist IN VARCHAR2,
109 p_created_by IN NUMBER,
110 p_last_updated_by IN NUMBER,
111 p_last_update_login IN NUMBER,
112 p_data_point_id IN NUMBER,
113 p_application_id IN NUMBER,
114 p_parent_data_point_id IN NUMBER,
115 p_enabled_flag IN VARCHAR2,
116 p_package_name IN VARCHAR2,
117 p_function_name IN VARCHAR2,
118 p_function_type IN VARCHAR2,
119 p_return_data_type IN VARCHAR2,
120 p_return_date_format IN VARCHAR2)
121 IS
122 BEGIN
123
124 INSERT INTO AR_CMGT_DATA_POINTS_B
125 ( data_point_id,
126 data_point_sub_category,
127 data_point_category,
128 user_defined_flag,
129 scorable_flag,
130 display_on_checklist_flag,
131 last_updated_by,
132 last_update_date,
133 created_by,
134 creation_date,
135 last_update_login,
136 application_id,
137 parent_data_point_id,
138 enabled_flag,
139 package_name,
140 function_name,
141 function_type,
142 return_data_type,
143 return_date_format,
144 data_point_code)
145 VALUES
146 ( p_data_point_id,
147 p_data_point_sub_category,
148 p_data_point_category,
149 p_user_defined_flag,
150 p_scorable_flag,
151 p_display_on_checklist,
152 p_last_updated_by,
153 sysdate,
154 p_created_by,
155 sysdate,
156 p_last_update_login,
157 p_application_id,
158 p_parent_data_point_id,
159 p_enabled_flag,
160 p_package_name,
161 p_function_name,
162 p_function_type,
163 p_return_data_type,
164 p_return_date_format,
165 p_data_point_code
166 );
167
168 INSERT INTO AR_CMGT_DATA_POINTS_TL
169 ( data_point_id,
170 data_point_name,
171 description,
172 LANGUAGE,
173 source_lang,
174 last_updated_by,
175 last_update_date,
176 created_by,
177 creation_date,
178 last_update_login,
179 application_id)
180 select
181 p_data_point_id,
182 p_data_point_name,
183 p_description,
184 l.language_code,
185 userenv('LANG'),
186 p_last_updated_by,
187 sysdate,
188 p_created_by,
189 sysdate,
190 p_last_update_login,
191 p_application_id
192 from fnd_languages l
193 where l.installed_flag in ('B','I')
194 and not exists (select NULL
195 from AR_CMGT_DATA_POINTS_TL t
196 where T.data_point_id = p_data_point_id
197 and T.LANGUAGE = L.LANGUAGE_CODE);
198
199
200 END;
201
202 procedure UPDATE_ROW
203 ( p_data_point_id IN NUMBER,
204 p_data_point_name IN VARCHAR2,
205 p_description IN VARCHAR2,
206 p_data_point_category IN VARCHAR2,
207 p_user_defined_flag IN VARCHAR2,
208 p_scorable_flag IN VARCHAR2,
209 p_display_on_checklist IN VARCHAR2,
210 p_application_id IN NUMBER,
211 p_parent_data_point_id IN NUMBER,
212 p_enabled_flag IN VARCHAR2,
213 p_package_name IN VARCHAR2,
214 p_function_name IN VARCHAR2,
215 p_data_point_sub_category IN VARCHAR2,
216 p_return_data_type IN VARCHAR2,
217 p_return_date_format IN VARCHAR2,
218 p_last_updated_by IN NUMBER,
219 p_last_update_login IN NUMBER,
220 p_data_point_code IN VARCHAR2) AS
221
222 BEGIN
223
224 update ar_cmgt_data_points_b
225 set data_point_category = p_data_point_category,
226 user_defined_flag = p_USER_DEFINED_FLAG,
227 scorable_flag = p_SCORABLE_FLAG,
228 display_on_checklist_flag = p_DISPLAY_ON_CHECKLIST,
229 application_id = p_application_id,
230 enabled_flag = p_enabled_flag,
231 package_name = p_package_name,
232 function_name = p_function_name,
233 data_point_sub_category = p_data_point_sub_category,
234 return_data_type = p_return_data_type,
235 return_date_format = p_return_date_format,
236 last_update_date = sysdate,
237 last_updated_by = p_last_updated_by,
238 last_update_login = p_last_update_login,
239 data_point_code = p_data_point_code
240 where data_point_id = p_data_point_id;
241
242 if (sql%notfound) then
243 raise no_data_found;
244 end if;
245 update ar_cmgt_data_points_tl
246 set data_point_name = p_DATA_POINT_NAME,
247 description = p_DESCRIPTION,
248 application_id = p_application_id,
249 last_update_date = sysdate,
250 last_updated_by = p_last_updated_by,
251 last_update_login = p_last_update_login,
252 source_lang = userenv('LANG')
253 WHERE data_point_id = p_data_point_id
254 AND userenv('LANG') in (language, source_lang);
255
256 if sql%notfound
257 then
258 raise no_data_found;
259 end if;
260
261
262 END;
263
264 PROCEDURE update_adp_row(
265 p_data_point_code IN VARCHAR2,
266 p_data_point_name IN VARCHAR2,
267 p_description IN VARCHAR2,
268 p_data_point_sub_category IN VARCHAR2,
269 p_scorable_flag IN VARCHAR2,
270 p_data_point_id IN NUMBER,
271 p_application_id IN NUMBER,
272 p_parent_data_point_id IN NUMBER,
273 p_enabled_flag IN VARCHAR2,
274 p_package_name IN VARCHAR2,
275 p_function_name IN VARCHAR2,
276 p_function_type IN VARCHAR2,
277 p_return_data_type IN VARCHAR2,
278 p_return_date_format IN VARCHAR2,
279 p_last_updated_by IN NUMBER,
280 p_last_update_login IN NUMBER )
281 IS
282 BEGIN
283 update ar_cmgt_data_points_b
284 set scorable_flag = p_SCORABLE_FLAG,
285 last_update_date = sysdate,
286 application_id = p_application_id,
287 data_point_sub_category = p_data_point_sub_category,
288 parent_data_point_id = p_parent_data_point_id,
289 enabled_flag = p_enabled_flag,
290 package_name = p_package_name,
291 function_name = p_function_name,
292 function_type = p_function_type,
293 last_updated_by = p_last_updated_by,
294 last_update_login = p_last_update_login,
295 return_data_type = p_return_data_type,
296 return_date_format = p_return_date_format,
297 data_point_code = p_data_point_code
298 where data_point_id = p_data_point_id;
299
300 if (sql%notfound) then
301 raise no_data_found;
302 end if;
303 update ar_cmgt_data_points_tl
304 set data_point_name = p_DATA_POINT_NAME,
305 description = p_description,
306 application_id = p_application_id,
307 last_update_date = sysdate,
308 source_lang = userenv('LANG')
309 WHERE data_point_id = p_data_point_id
310 AND userenv('LANG') in (language, source_lang);
311
312 if sql%notfound
313 then
314 raise no_data_found;
315 end if;
316
317 end;
318
319 procedure DELETE_ROW (
320 p_data_point_id in NUMBER
321 ) is
322 begin
323 delete from ar_cmgt_data_points_b
324 where data_point_id = p_data_point_id;
325
326 if (sql%notfound) then
327 raise no_data_found;
328 end if;
329
330 delete from ar_cmgt_data_points_tl
331 where data_point_id = p_data_point_id;
332
333 if (sql%notfound) then
334 raise no_data_found;
335 end if;
336 end DELETE_ROW;
337
338 procedure ADD_LANGUAGE
339 is
340 begin
341 delete from AR_CMGT_DATA_POINTS_TL T
342 where not exists
343 (select NULL
344 from AR_CMGT_DATA_POINTS_B B
345 where B.DATA_POINT_ID = T.DATA_POINT_ID
346 );
347
348 update AR_CMGT_DATA_POINTS_TL T set (
349 data_point_NAME,
350 DESCRIPTION
351 ) = (select
352 B.data_point_NAME,
353 B.DESCRIPTION
354 from AR_CMGT_DATA_POINTS_TL B
355 where B.data_point_id = T.data_point_id
356 and B.LANGUAGE = T.SOURCE_LANG)
357 where (
358 T.data_point_ID,
359 T.LANGUAGE
360 ) in (select
361 SUBT.data_point_ID,
362 SUBT.LANGUAGE
363 from AR_CMGT_DATA_POINTS_TL SUBB, AR_CMGT_DATA_POINTS_TL SUBT
364 where SUBB.data_point_id = SUBT.data_point_ID
365 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
366 and (SUBB.DATA_POINT_NAME <> SUBT.DATA_POINT_NAME
367 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
368 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
369 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
370 ));
371
372 INSERT INTO AR_CMGT_DATA_POINTS_TL
373 ( data_point_id,
374 data_point_name,
375 description,
376 LANGUAGE,
377 application_id,
378 source_lang,
379 last_updated_by,
380 last_update_date,
381 created_by,
382 creation_date,
383 last_update_login ) select
384 t.data_point_id,
385 t.data_point_name,
386 t.description,
387 l.language_code,
388 t.application_id,
389 t.source_lang,
390 t.last_updated_by,
391 t.last_update_date,
392 t.created_by,
393 t.creation_date,
394 t.last_update_login
395 FROM ar_cmgt_data_points_tl t, fnd_languages l
396 WHERE l.installed_flag in ( 'I', 'B')
397 AND t.language = userenv('LANG')
398 AND not exists ( select NULL FROM
399 ar_cmgt_data_points_tl t1
400 where t1.data_point_id = t.data_point_id
401 and t1.language = l.language_code);
402 end ADD_LANGUAGE;
403
404 procedure TRANSLATE_ROW (
405 P_DATA_POINT_ID IN NUMBER,
406 P_DESCRIPTION IN VARCHAR2,
407 P_DATA_POINT_NAME IN VARCHAR2,
408 P_OWNER IN VARCHAR2) IS
409 begin
410
411 -- only update rows that have not been altered by user
412
413 update AR_CMGT_DATA_POINTS_TL
414 set description = p_description,
415 DATA_POINT_NAME = p_data_point_name,
416 source_lang = userenv('LANG'),
417 last_update_date = sysdate,
418 last_updated_by = decode(P_OWNER, 'SEED', 1, 0)
419 where data_point_id = p_data_point_id
420 and userenv('LANG') in (language, source_lang);
421
422 end TRANSLATE_ROW;
423
424 procedure LOAD_ROW
425 ( p_data_point_id IN VARCHAR2,
426 p_data_point_name IN VARCHAR2,
427 p_description IN VARCHAR2,
428 p_data_point_category IN VARCHAR2,
429 p_user_defined_flag IN VARCHAR2,
430 p_scorable_flag IN VARCHAR2,
431 p_display_on_checklist IN VARCHAR2,
432 p_application_id IN NUMBER,
433 p_parent_data_point_id IN NUMBER,
434 p_enabled_flag IN VARCHAR2,
435 p_package_name IN VARCHAR2,
436 p_function_name IN VARCHAR2,
437 p_data_point_sub_category IN VARCHAR2,
438 p_return_data_type IN VARCHAR2,
439 p_return_date_format IN VARCHAR2,
443 p_data_point_code IN VARCHAR2
440 p_created_by IN NUMBER,
441 p_last_updated_by IN NUMBER,
442 p_last_update_login IN NUMBER,
444 ) AS
445
446
447 BEGIN
448 UPDATE_ROW
449 ( p_data_point_id => p_data_point_id,
450 p_data_point_name => p_data_point_name,
451 p_description => p_description,
452 p_data_point_category => p_data_point_category,
453 p_user_defined_flag => p_user_defined_flag,
454 p_scorable_flag => p_scorable_flag,
455 p_display_on_checklist => p_display_on_checklist,
456 p_application_id => p_application_id,
457 p_parent_data_point_id => p_parent_data_point_id,
458 p_enabled_flag => p_enabled_flag,
459 p_package_name => p_package_name,
460 p_function_name => p_function_name,
461 p_data_point_sub_category => p_data_point_sub_category,
462 p_return_data_type => p_return_data_type,
463 p_return_date_format => p_return_date_format,
464 p_last_updated_by => p_last_updated_by,
465 p_last_update_login => p_last_update_login,
466 p_data_point_code => p_data_point_code);
467
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 INSERT_ROW
471 ( p_data_point_name => p_data_point_name,
472 p_description => p_description,
473 p_data_point_category => p_data_point_category,
474 p_user_defined_flag => p_user_defined_flag,
475 p_scorable_flag => p_scorable_flag,
476 p_display_on_checklist => p_display_on_checklist,
477 p_created_by => p_created_by,
478 p_last_updated_by => p_last_updated_by,
479 p_last_update_login => p_last_update_login,
480 p_data_point_id => p_data_point_id,
481 p_return_data_type => p_return_data_type,
482 p_return_date_format => p_return_date_format,
483 p_application_id => p_application_id,
484 p_parent_data_point_id => p_parent_data_point_id,
485 p_enabled_flag => p_enabled_flag,
486 p_package_name => p_package_name,
487 p_function_name => p_function_name,
488 p_data_point_sub_category => p_data_point_sub_category,
489 p_data_point_code => p_data_point_code);
490
491 END;
492
493 procedure TRANSLATE_ADP_ROW (
494 p_data_point_code IN VARCHAR2,
495 P_DESCRIPTION IN VARCHAR2,
496 P_DATA_POINT_NAME IN VARCHAR2,
497 P_APPLICATION_ID IN NUMBER,
498 P_OWNER IN VARCHAR2) IS
499 begin
500
501 -- only update rows that have not been altered by user
502
503 update AR_CMGT_DATA_POINTS_TL
504 set description = p_description,
505 data_point_name = p_data_point_name,
506 source_lang = userenv('LANG'),
507 last_update_date = sysdate,
508 last_updated_by = decode(P_OWNER, 'SEED', 1, 0)
509 where data_point_id = (SELECT data_point_id from ar_cmgt_data_points_b
510 where data_point_code = p_data_point_code
511 and application_id = p_application_id )
512 and application_id = p_application_id
513 and userenv('LANG') in (language, source_lang);
514
515 end TRANSLATE_ADP_ROW;
516
517
518 procedure LOAD_ADP_ROW
519 ( p_data_point_code IN VARCHAR2,
520 p_data_point_name IN VARCHAR2,
521 p_description IN VARCHAR2,
522 p_data_point_category IN VARCHAR2,
523 p_user_defined_flag IN VARCHAR2,
524 p_scorable_flag IN VARCHAR2,
525 p_display_on_checklist IN VARCHAR2,
526 p_application_id IN NUMBER,
527 p_parent_data_point_code IN VARCHAR2,
528 p_enabled_flag IN VARCHAR2,
529 p_package_name IN VARCHAR2,
530 p_function_name IN VARCHAR2,
531 p_function_type IN VARCHAR2,
532 p_data_point_sub_category IN VARCHAR2,
533 p_return_data_type IN VARCHAR2,
534 p_return_date_format IN VARCHAR2,
535 p_created_by IN NUMBER,
536 p_last_updated_by IN NUMBER,
537 p_last_update_login IN NUMBER
538 ) IS
539
540 l_data_point_id NUMBER;
541 l_parent_data_point_id NUMBER;
542 sqlException EXCEPTION;
543 BEGIN
544 -- Get the parent data point id
545 IF p_parent_data_point_code IS NOT NULL
546 THEN
547 BEGIN
548 SELECT data_point_id
549 INTO l_parent_data_point_id
550 FROM ar_cmgt_data_points_b
551 WHERE data_point_code = p_parent_data_point_code
552 AND application_id = p_application_id;
553
554 EXCEPTION
555 WHEN NO_DATA_FOUND THEN
556 l_parent_data_point_id := NULL;
557 END;
558 END IF;
559 BEGIN
560 SELECT DATA_POINT_ID
561 INTO l_data_point_id
562 FROM ar_cmgt_data_points_b
563 WHERE data_point_code = p_data_point_code
564 AND application_id = p_application_id;
565
566 update_adp_row(
567 p_data_point_code => p_data_point_code,
568 p_data_point_name => p_data_point_name,
569 p_description => p_description,
570 p_data_point_sub_category => p_data_point_sub_category,
571 p_scorable_flag => p_scorable_flag,
572 p_data_point_id => l_data_point_id,
576 p_package_name => p_package_name,
573 p_application_id => p_application_id,
574 p_parent_data_point_id => l_parent_data_point_id,
575 p_enabled_flag => p_enabled_flag,
577 p_function_name => p_function_name,
578 p_function_type => p_function_type,
579 p_return_data_type => p_return_data_type,
580 p_return_date_format => p_return_date_format,
581 p_last_updated_by => p_last_updated_by,
582 p_last_update_login => p_last_update_login);
583 EXCEPTION
584 WHEN NO_DATA_FOUND THEN
585 SELECT ar_cmgt_data_points_s.nextval
586 INTO l_data_point_id
587 FROM dual;
588
589 INSERT_adp_ROW
590 ( p_data_point_code => p_data_point_code,
591 p_data_point_name => p_data_point_name,
592 p_description => p_description,
593 p_data_point_sub_category => p_data_point_sub_category,
594 p_data_point_category => p_data_point_category,
595 p_user_defined_flag => p_user_defined_flag,
596 p_scorable_flag => p_scorable_flag,
597 p_display_on_checklist => p_display_on_checklist,
598 p_created_by => p_created_by,
599 p_last_updated_by => p_last_updated_by,
600 p_last_update_login => p_last_update_login,
601 p_data_point_id => l_data_point_id,
602 p_application_id => p_application_id,
603 p_parent_data_point_id => l_parent_data_point_id,
604 p_enabled_flag => p_enabled_flag,
605 p_package_name => p_package_name,
606 p_function_name => p_function_name,
607 p_function_type => p_function_type,
608 p_return_data_type => p_return_data_type,
609 p_return_date_format => p_return_date_format);
610
611 END;
612 EXCEPTION
613 WHEN OTHERS THEN
614 raise;
615
616
617 END;
618
619
620 END AR_CMGT_DP_TABLE_HANDLER;