[Home] [Help]
PACKAGE BODY: APPS.ASO_DEPENDENCY_UTIL
Source
1 PACKAGE BODY ASO_DEPENDENCY_UTIL AS
2 /* $Header: asovdpub.pls 120.1 2005/06/29 12:41:35 appldev noship $ */
3 -- Package name : ASO_DEPENDENCY_UTIL
4 -- Purpose :
5 -- History :
6 -- NOTE :
7 -- End of Comments
8
9 --CREATE OR REPLACE
10 --PACKAGE BODY ASO_DEPENDENCY_UTIL AS
11
12 -- Global constant holding the package name
13
14 G_PKG_NAME CONSTANT VARCHAR2(240):='ASO_DEPENDENCY_UTIL';
15 line_number NUMBER := 0;
16 g_min NUMBER;
17 g_conc_mode VARCHAR2(1);
18 G_APPLICATION_ID NUMBER := 697;
19
20
21 -- need to find OUT NOCOPY /* file.sql.39 change */ which log files it puts in.
22 PROCEDURE Put_Line
23 (Text Varchar2)
24 IS
25 l_bol boolean := true;
26 BEGIN
27
28
29 if g_conc_mode is null then
30
31 if nvl(fnd_profile.value('CONC_REQUEST_ID'),0) <> 0 then
32 g_conc_mode := 'Y';
33 else
34 g_conc_mode := 'N';
35 end if;
36
37 end if;
38
39 if g_conc_mode = 'Y' then
40 FND_FILE.PUT_LINE(FND_FILE.LOG, Text);
41 end if;
42
43 l_bol := true;
44 ASO_UPGRADE_PVT.Add_Message(
45 p_module_name => 'ASO',
46 p_error_level => 'INFORMATION',
47 p_error_text => Text,
48 p_source_name => 'asovdpub.pls'
49 );
50
51 END Put_Line;
52
53 PROCEDURE Init_Applsys_Schema
54 IS
55 l_app_info BOOLEAN;
56 l_status VARCHAR2(30);
57 l_industry VARCHAR2(30);
58 BEGIN
59
60 IF g_schema is null THEN
61 l_app_info := FND_INSTALLATION.GET_APP_INFO('FND',l_status, l_industry, g_schema);
62 END IF;
63
64 END Init_Applsys_Schema;
65
66 PROCEDURE New_Line
67 IS
68 BEGIN
69
70 line_number := line_number + 1;
71 ad_ddl.build_package(' ',line_number);
72
73 END New_Line;
74
75 PROCEDURE Text
76 ( p_string IN VARCHAR2
77 , p_level IN NUMBER default 1
78 )
79 IS
80 BEGIN
81
82 line_number := line_number + 1;
83 ad_ddl.build_package(LPAD(p_string,p_level*2+LENGTH(p_string)),line_number);
84
85 END text;
86
87 PROCEDURE Comment
88 ( p_comment IN VARCHAR2
89 , p_level IN NUMBER default 1
90 )
91 IS
92 BEGIN
93
94 Text('-- '||p_comment,p_level);
95
96 END Comment;
97
98 PROCEDURE Parameter
99 ( p_param IN VARCHAR2
100 , p_mode IN VARCHAR2 := 'IN'
101 , p_type IN VARCHAR2 := 'NUMBER'
102 , p_level IN NUMBER default 1
103 , p_def_flag IN BOOLEAN := FALSE
104 , p_def_str IN VARCHAR2 := ''
105 , p_rpad IN NUMBER := 30
106 , p_first IN BOOLEAN := FALSE
107 )
108 IS
109 l_first varchar2(1);
110 l_prefix VARCHAR2(10);
111 BEGIN
112
113 IF rtrim(p_mode) = 'IN' THEN
114 l_prefix := ' P_';
115 ELSE
116 l_prefix := ' X_';
117 END IF;
118
119 IF p_first THEN
120 l_first := '(';
121 ELSE
122 l_first := ',';
123 END IF;
124
125 -- PUT_LINE(l_prefix||p_param);
126 Text( l_first||l_prefix||RPAD(UPPER(p_param),p_rpad)||RPAD(UPPER(p_mode),4)||UPPER(p_type)
127 , p_level
128 );
129 -- PUT_LINE(l_first||l_prefix||RPAD(UPPER(p_param),p_rpad)||RPAD(UPPER(p_mode),4)||UPPER(p_type));
130 IF p_def_flag THEN
131 Text( LPAD(' ',p_rpad+6)||p_def_str, p_level );
132 END IF;
133
134 END Parameter;
135
136 -------------------------------------------------------------------------------
137 PROCEDURE Pkg_End
138 ( p_pkg_name IN VARCHAR2
139 , p_pkg_type IN VARCHAR2
140 )
141 IS
142 l_is_pkg_body VARCHAR2(30);
143 n NUMBER := 0;
144 CURSOR errors IS
145 SELECT line, text
146 FROM user_errors
147 WHERE name = upper(p_pkg_name)
148 AND type = decode(p_pkg_type,'SPEC','PACKAGE',
149 'BODY','PACKAGE BODY');
150 BEGIN
151
152 -- end statement.
153 Text('END '||p_pkg_name||';',0);
154
155 -- Show errors.
156 IF p_pkg_type = 'BODY' THEN
157 l_is_pkg_body := 'TRUE';
158 ELSE
159 l_is_pkg_body := 'FALSE';
160 END IF;
161
162 --PUT_LINE(
163 --'Call AD_DDL to create '||p_pkg_type||' of package '||p_pkg_name);
164
165
166 ad_ddl.create_package(
167 applsys_schema => g_schema,
168 application_short_name => 'ASO',
169 package_name => p_pkg_name,
170 is_package_body => l_is_pkg_body,
171 lb => 1,
172 ub => line_number);
173
174 -- if there were any errors when creating this package, print out
175 -- the errors in the log file
176 FOR error IN errors LOOP
177 if n= 0 then
178 PUT_LINE('ERROR in creating PACKAGE '||p_pkg_type||' :'||p_pkg_name);
179 end if;
180 PUT_LINE(
181 'LINE :'||error.line||' '||substr(error.text,1,200));
182 n := 1;
183 END LOOP;
184
185 -- if there was an error in compiling the package, raise
186 -- an error
187 if n > 0 then
188 RAISE FND_API.G_EXC_ERROR;
189 end if;
190
191 exception
192 when FND_API.G_EXC_ERROR then
193 raise FND_API.G_EXC_ERROR;
194 when others THEN
195 raise_application_error(-20000,SQLERRM||' '||ad_ddl.error_buf);
196
197 END Pkg_End;
198
199 -------------------------------------------------------------------------------
200 -- Generates the Package Header for the package SPEC AND BODY
201
202 PROCEDURE Pkg_Header
203 ( p_pkg_name IN VARCHAR2
204 , p_pkg_type IN VARCHAR2
205 )
206 IS
207 header_string VARCHAR2(200);
208 BEGIN
209
210 -- Initialize line number
211 line_number := 0;
212
213 -- Define package.
214
215 IF p_pkg_type = 'BODY' THEN
216 Text ('CREATE or REPLACE PACKAGE BODY '|| p_pkg_name|| ' AS',0);
217 ELSE
218 Text ('CREATE or REPLACE PACKAGE '|| p_pkg_name|| ' AUTHID CURRENT_USER AS',0);
219 END IF;
220
221 -- $Header clause.
222 header_string := 'Header: asodphdb.pls 115.0 '||TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS') ||' appldev ship ';
223 Text('/* $'||header_string||'$ */',0);
224 New_Line;
225
226 -- Copyright section.
227
228 Comment ( '',0 );
229 Comment ('Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA',0);
230 Comment ( 'All rights reserved.',0);
231 Comment ( '',0);
232 Comment ( 'FILENAME',0);
233 Comment ( '',0);
234 Comment ( ' '||p_pkg_name,0);
235 Comment ( '',0);
236 Comment ( 'DESCRIPTION',0);
237 Comment ( '',0);
238 Comment ( ' '||INITCAP(p_pkg_type)||' of package '||p_pkg_name,0);
239 Comment ( '',0);
240 Comment ('NOTES',0);
241 Comment ( '',0);
242 Comment ('HISTORY',0);
243 Comment ( '',0);
244 Comment ( TO_CHAR(SYSDATE)||' Created',0);
245 Comment ( '',0);
246 New_Line;
247
248 -- Global constant holding package name.
249
250 IF p_pkg_type = 'BODY' THEN
251 Comment ( 'Global constant holding the package name',0);
252 Text (RPAD('G_PKG_NAME',30)||'CONSTANT '||
253 'VARCHAR2(30) := '''||p_pkg_name||''';',0);
254 New_Line;
255 END IF;
256
257 END Pkg_Header;
258
259 PROCEDURE Api_Body_Start
260 IS
261 BEGIN
262
263 NEW_LINE;
264
265 -- Construct the prelude logic
266 Comment(' Initialize message list if p_init_msg_list is set to TRUE', 1);
267 Text('IF fnd_api.to_boolean (', 1);
268 Text(' p_init_msg_list', 1);
269 Text(' )', 1);
270 Text('THEN', 1);
271 Text(' fnd_msg_pub.initialize;', 1);
272 Text('END IF;', 1);
273
274 NEW_LINE;
275
276 Comment(' Initialize API return status to success ', 1);
277 Text('x_return_status := fnd_api.g_ret_sts_success;', 1);
278
279 NEW_LINE;
280
281 Comment('--- ', 1);
282 Comment('API body ', 1);
283 Comment('--- ', 1);
284
285 NEW_LINE;
286
287
288 END Api_Body_Start;
289
290 PROCEDURE Api_Body_End
291 IS
292 BEGIN
293
294 NEW_LINE;
295
296 -- Construct the end of the package body
297
298 Comment('--- ', 1);
299 Comment(' End of API body ', 1);
300 Comment('--- ', 1);
301
302 Comment('Standard Call to get message count ', 1);
303
304 Text('fnd_msg_pub.count_and_get ( ', 1);
305 Text(' p_count => x_msg_count, ', 1);
306 Text(' p_data => x_msg_data ', 1);
307 Text(' ); ', 1);
308
309 Text('EXCEPTION ', 0);
310 Text(' WHEN broadcast_exception THEN', 0);
311 Text(' x_return_status := FND_API.G_RET_STS_ERROR;', 0);
312 NEW_LINE;
313
314 END Api_Body_End;
315
316 PROCEDURE Convert_Hash_To_Array
317 ( p_hash_table IN Boolean_Tbl_Type
318 , x_array_table OUT NOCOPY /* file.sql.39 change */ Number_Tbl_Type
319 )
320 IS
321 l_hash_index NUMBER;
322 l_array_index NUMBER;
323 BEGIN
324
325 l_hash_index := p_hash_table.FIRST;
326 l_array_index := 1;
327
328 WHILE l_hash_index IS NOT NULL LOOP
329
330 IF p_hash_table(l_hash_index) = TRUE THEN
331 x_array_table(l_array_index) := l_hash_index;
332 l_array_index := l_array_index + 1;
333 END IF;
334 l_hash_index := p_hash_table.NEXT(l_hash_index);
335
336 END LOOP;
337
338 END Convert_Hash_To_Array;
339
340 PROCEDURE Convert_Array_To_Hash
341 ( p_array_table IN Number_Tbl_Type ,
342 x_hash_table OUT NOCOPY /* file.sql.39 change */ Boolean_Tbl_Type
343 )
344 IS
345 BEGIN
346
347 /* p_array_table should contain records with continous index */
348 FOR i IN p_array_table.FIRST..p_array_table.LAST LOOP
349 x_hash_table(p_array_table(i)) := TRUE;
350 END LOOP;
351
352
353 END Convert_Array_To_Hash;
354
355 PROCEDURE Mark_Dependent
356 ( p_trigger_attr_id IN NUMBER,
357 x_dep_attr_tbl OUT NOCOPY /* file.sql.39 change */ Number_Tbl_Type
358 )
359 IS
360 l_src_index NUMBER;
361 l_g_dep_index NUMBER;
362 l_dep_index NUMBER;
363 l_out_index NUMBER;
364 l_dep_attr_tbl Boolean_Tbl_Type;
365 l_src_attr_tbl Boolean_Tbl_Type;
366 l_examined_attr_tbl Boolean_Tbl_Type;
367 l_out_attr_tbl Boolean_Tbl_Type;
368 l_root_scan BOOLEAN;
369 BEGIN
370
371
372 -- Populating the source hash table with the trigger attribute id
373
374 l_src_attr_tbl(p_trigger_attr_id) := TRUE;
375
376 -- While the source hash table is not empty
377
378 WHILE l_src_attr_tbl.COUNT <> 0 LOOP
379
380 l_src_index := l_src_attr_tbl.FIRST;
381
382 -- For each trigger attribute in the source hash table
383 WHILE l_src_index IS NOT NULL LOOP
384
385 PUT_LINE(' ');
386 PUT_LINE('Trigger attribute: ' || TO_CHAR(l_src_index));
387
388 -- Mark attributes that have been examined.
389 l_examined_attr_tbl(l_src_index) := TRUE;
390 l_g_dep_index := l_src_index * G_MAX;
391
392 -- For each Associate in the global dependency hash table
393 -- associated with this trigger attribute.
394 WHILE ( g_dep_tbl.EXISTS(l_g_dep_index)
395 AND l_g_dep_index < (l_src_index+1) * G_MAX
396 )
397 LOOP
398 -- put it in dependent attribute table
399 l_dep_attr_tbl(g_dep_tbl(l_g_dep_index).attribute) := TRUE;
400 PUT_LINE('Dependent attribute: ' || TO_CHAR(g_dep_tbl(l_g_dep_index).attribute));
401 l_g_dep_index := l_g_dep_index + 1;
402 END LOOP; -- next g_dep_tbl entry
403
404 l_src_index := l_src_attr_tbl.NEXT(l_src_index);
405
406 END LOOP; -- next source attribute
407
408 -- Clear source hash table.
409 l_src_attr_tbl.DELETE;
410
411 -- Check dependent attributes. If they have been already
412 -- examined then no need to re-check them.
413 l_dep_index := l_dep_attr_tbl.FIRST;
414
415 WHILE l_dep_index IS NOT NULL LOOP
416
417 -- Merge current list of dependent attributes into output hash table
418 l_out_attr_tbl(l_dep_index) := TRUE;
419
420 -- Populate the source hash table with attributes
421 -- from current list of dependent attributes but
422 -- not in the examined list
423 IF NOT l_examined_attr_tbl.EXISTS(l_dep_index) THEN
424 l_src_attr_tbl(l_dep_index) := TRUE;
425 END IF;
426
427 l_dep_index := l_dep_attr_tbl.NEXT(l_dep_index);
428
429 END LOOP;
430
431 -- Clear dependent hash table.
432 l_dep_attr_tbl.DELETE;
433
434 END LOOP; -- next set of source attributes from previous round of scan.
435
436 --Convert the output hash table to output list
437 convert_hash_to_array(
438 p_hash_table => l_out_attr_tbl,
439 x_array_table => x_dep_attr_tbl);
440
441
442 END Mark_Dependent;
443
444 FUNCTION Get_Min_Attribute_ID
445 ( P_DATABASE_OBJECT_NAME VARCHAR2 )
446 RETURN NUMBER
447 IS
448 CURSOR get_min_attr_id IS
449 SELECT min(attribute_id)
450 FROM oe_ak_obj_attr_ext
451 WHERE database_object_name = p_database_object_name
452 AND attribute_application_id = G_APPLICATION_ID;
453
454 l_min_attr_id NUMBER;
455 BEGIN
456
457 /* Get min(TRIGGER_ATTRIBUTE_ID) from ASO_DEPENDENCY_MAPPINGS */
458
459 OPEN get_min_attr_id;
460 FETCH get_min_attr_id INTO l_min_attr_id;
461 CLOSE get_min_attr_id;
462
463 RETURN l_min_attr_id;
464
465 --RETURN 10000;
466
467 END Get_Min_Attribute_ID;
468
469 FUNCTION Get_Dependent_Attributes_Count
470 ( P_DATABASE_OBJECT_NAME VARCHAR2 )
471 RETURN NUMBER
472 IS
473
474 CURSOR get_dep_attr_num IS
475 SELECT count(distinct dependent_attribute_id)
476 FROM aso_dependency_mappings
477 WHERE database_object_name = p_database_object_name;
478
479 l_dep_attr_count NUMBER;
480
481 BEGIN
482
483 /* Get distinct(DEPENDENT_ATTRIBUTE_ID) from ASO_DEPENDENCY_MAPPINGS */
484
485 OPEN get_dep_attr_num;
486 FETCH get_dep_attr_num INTO l_dep_attr_count;
487 CLOSE get_dep_attr_num;
488
489 RETURN l_dep_attr_count;
490 --RETURN 10;
491
492 END Get_Dependent_Attributes_Count;
493
494 PROCEDURE Build_Dependencies_Table
495 ( P_DATABASE_OBJECT_NAME VARCHAR2 )
496 IS
497
498 i NUMBER;
499 normalized_trigger_id NUMBER;
500 normalized_dependent_id NUMBER;
501 starting_point NUMBER;
502 l_dep_index NUMBER;
503
504 l_dep_attr_tbl Number_Tbl_Type;
505
506 CURSOR c_trigger_attributes IS
507 SELECT DISTINCT a.trigger_attribute_id,b.attribute_code
508 FROM ASO_DEPENDENCY_MAPPINGS a,oe_ak_obj_attr_ext b
509 WHERE a.trigger_attribute_id = b.attribute_id
510 AND a.database_object_name = p_database_object_name;
511
512 CURSOR c_dependent_attributes(lc_trigger_attribute_id NUMBER) IS
513 SELECT a.dependent_attribute_id,b.attribute_code
514 FROM ASO_DEPENDENCY_MAPPINGS a,oe_ak_obj_attr_ext b
515 WHERE a.dependent_attribute_id = b.attribute_id
516 AND a.trigger_attribute_id = lc_trigger_attribute_id
517 AND a.enabled_flag = 'Y';
518 BEGIN
519
520 g_min := Get_Min_Attribute_ID(p_database_object_name);
521 G_Max := Get_Dependent_Attributes_Count(p_database_object_name);
522 PUT_LINE('G_Min: '||TO_CHAR(g_min));
523 PUT_LINE('G_Max: '||TO_CHAR(G_Max));
524 put_line(' ');
525
526 put_line('Trigger Attribute');
527 put_line('******* Direct Dependent_attribute');
528 put_line(' ');
529
530 -- populate g_dep_tbl based on TRIGGER_ATTRIBUTE_ID
531 FOR l_trigger_attr_rec IN c_trigger_attributes LOOP
532
533 put_line(' ');
534 put_line('--------------------');
535 put_line(l_trigger_attr_rec.attribute_code);
536
537 normalized_trigger_id := l_trigger_attr_rec.trigger_attribute_id - g_min;
538 starting_point := normalized_trigger_id * G_Max;
539
540 i := 0;
541 FOR l_dependent_attr_rec IN c_dependent_attributes(l_trigger_attr_rec.trigger_attribute_id)
542 LOOP
543 normalized_dependent_id := l_dependent_attr_rec.dependent_attribute_id - g_min;
544
545 g_dep_tbl(starting_point + i).attribute
546 := normalized_dependent_id;
547
548 put_line('-> '||l_dependent_attr_rec.attribute_code);
549 put_line('g_dep_tbl('||TO_CHAR(starting_point + i)
550 ||').attribute = '||TO_CHAR(normalized_dependent_id));
551 i := i + 1;
552 END LOOP;
553
554 -- call Mark_Dependencies
555 Mark_Dependent (
556 p_trigger_attr_id => normalized_trigger_id,
557 x_dep_attr_tbl => l_dep_attr_tbl);
558
559 put_line(' ');
560 put_line('******* Chained Dependent_attribute');
561 put_line(' ');
562 IF l_dep_attr_tbl.COUNT <> 0
563 THEN
564 -- Populate g_dep_chain_tbl, which stores chained dependent attributes
565 i := 0;
566 l_dep_index := l_dep_attr_tbl.FIRST;
567
568 WHILE l_dep_index IS NOT NULL
569 LOOP
570
571 g_dep_chain_tbl(starting_point + i).attribute
572 := l_dep_attr_tbl(l_dep_index);
573 put_line('-> '||TO_CHAR(l_dep_attr_tbl(l_dep_index)));
574
575 i := i + 1;
576 l_dep_index := l_dep_attr_tbl.NEXT(l_dep_index);
577
578 END LOOP;
579
580 END IF;
581
582 END LOOP;
583
584 /* here is just a hack */
585 /*
586 g_dep_tbl((10000 - g_min) * G_MAX).attribute := 1;
587 g_dep_tbl((10000 - g_min) * G_MAX+1).attribute := 2;
588
589 g_dep_tbl((10001 - g_min) * G_MAX).attribute := 0;
590 g_dep_tbl((10001 - g_min) * G_MAX+1).attribute := 2;
591 */
592
593 END Build_Dependencies_Table;
594
595
596
597 PROCEDURE Make_Dependency_Engine_Body (
598 Errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
599 RetCode OUT NOCOPY /* file.sql.39 change */ NUMBER,
600 p_database_object_name IN VARCHAR2,
601 p_primary_key_name IN VARCHAR2,
602 p_last_update_date_name IN VARCHAR2)
603 IS
604 l_attribute_code VARCHAR2(30);
605 l_first_loop BOOLEAN := TRUE;
606 l_miss_str VARCHAR2(30);
607 l_pkg_name VARCHAR2(30);
608 l_dep_attr_tbl Number_Tbl_Type;
609 l_string VARCHAR2(2000);
610 i NUMBER;
611 l_entity_name VARCHAR2(15);
612 l_ak_view_name VARCHAR2(30);
613 normalized_trigger_id NUMBER;
614 normalized_dependent_id NUMBER;
615 starting_point NUMBER;
616 l_ck_cursor_name VARCHAR2(30);
617 l_ac_cursor_name VARCHAR2(30);
618 l_attribute_name VARCHAR2(30);
619 lv_ak_var_name VARCHAR2(30);
620 l_trigger_code VARCHAR2(30);
621 l_dependent_code VARCHAR2(30);
622
623 CURSOR c_trigger_attribute IS
624 SELECT DISTINCT a.attribute_code, a.attribute_id, c.column_name
625 FROM oe_ak_obj_attr_ext a, ASO_DEPENDENCY_MAPPINGS b, ak_object_attributes c
626 WHERE b.TRIGGER_ATTRIBUTE_ID = a.attribute_id
627 AND a.attribute_application_id = G_APPLICATION_ID
628 AND a.database_object_name = p_database_object_name
629 AND b.enabled_flag = 'Y'
630 AND a.database_object_name = c.database_object_name
631 AND a.attribute_code = c.attribute_code
632 AND a.attribute_application_id = c.attribute_application_id;
633
634 CURSOR c_all_trigger_attribute IS
635 SELECT DISTINCT a.attribute_code, a.attribute_id, c.column_name
636 FROM oe_ak_obj_attr_ext a, ASO_DEPENDENCY_MAPPINGS b, ak_object_attributes c
637 WHERE b.TRIGGER_ATTRIBUTE_ID = a.attribute_id
638 AND a.attribute_application_id = G_APPLICATION_ID
639 AND a.database_object_name = p_database_object_name
640 AND a.database_object_name = c.database_object_name
641 AND a.attribute_code = c.attribute_code
642 AND a.attribute_application_id = c.attribute_application_id
643 ORDER BY a.attribute_code;
644
645 CURSOR c_data_type(lc_attribute_code VARCHAR2) IS
646 SELECT DECODE(data_type, 'NUMBER', 'FND_API.G_MISS_NUM',
647 'VARCHAR2', 'FND_API.G_MISS_CHAR',
648 'DATE', 'FND_API.G_MISS_DATE',
649 'FND_API.G_MISS_CHAR')
650 FROM ak_attributes
651 WHERE attribute_code = lc_attribute_code
652 AND attribute_application_id = G_APPLICATION_ID;
653
654 CURSOR c_attribute_code(lc_attribute_id NUMBER) IS
655 SELECT attribute_code
656 FROM oe_ak_obj_attr_ext
657 WHERE attribute_id = lc_attribute_id;
658
659 CURSOR c_attribute_column_name(lc_attribute_code VARCHAR2) IS
660 SELECT column_name
661 FROM ak_object_attributes
662 WHERE database_object_name = p_database_object_name
663 AND attribute_code = lc_attribute_code;
664
665 CURSOR c_attributes IS
666 SELECT a.attribute_code, a.attribute_id, b.column_name,
667 DECODE(data_type, 'NUMBER', 'FND_API.G_MISS_NUM',
668 'VARCHAR2', 'FND_API.G_MISS_CHAR',
669 'DATE', 'FND_API.G_MISS_DATE',
670 'FND_API.G_MISS_CHAR') miss_str
671 FROM oe_ak_obj_attr_ext a, ak_object_attributes b, ak_attributes c
672 WHERE a.attribute_application_id = G_APPLICATION_ID
673 AND a.database_object_name = p_database_object_name
674 AND a.database_object_name = b.database_object_name
675 AND a.attribute_application_id = b.attribute_application_id
676 AND a.attribute_code = b.attribute_code
677 AND b.attribute_code = c.attribute_code
678 and b.attribute_application_id = b.attribute_application_id
679 ORDER BY ATTRIBUTE_ID;
680
681 CURSOR c_entity_name is
682 SELECT entity_code
683 FROM oe_ak_objects_ext
684 WHERE database_object_name = p_database_object_name;
685
686 BEGIN
687
688 PUT_LINE('p_database_object_name: ' || p_database_object_name);
689 PUT_LINE('p_primary_key_name: ' || p_primary_key_name);
690 PUT_LINE('p_last_update_date_name: ' || p_last_update_date_name);
691
692 -- build the dependencies index table
693 Build_Dependencies_Table(
694 p_database_object_name
695 );
696
697
698 -- initialize the application schema information
699 Init_Applsys_Schema;
700
701 -- Get the G_MISS value of this trigger
702 OPEN c_entity_name;
703 FETCH c_entity_name INTO l_entity_name;
704 CLOSE c_entity_name;
705
706 l_pkg_name := 'ASO_'||l_entity_name||'_DEP_HDLR';
707 l_ak_view_name := p_database_object_name;
708
709 -- Construct the package header
710
711 Pkg_Header(
712 p_pkg_name => l_pkg_name,
713 p_pkg_type => 'BODY');
714
715
716 NEW_LINE;
717
718 -- Construct the global variables
719 FOR l_attribute_rec IN c_attributes LOOP
720 l_string := 'G_'||l_attribute_rec.attribute_code;
721 l_string := RPAD(l_string,35)||' CONSTANT NUMBER:= '||l_attribute_rec.attribute_id||';';
722 Text(l_string, 0);
723 --Text('G_'||l_attribute_rec.attribute_code||' CONSTANT NUMBER:= '||l_attribute_rec.attribute_id||';', 1);
724 END LOOP;
725
726 New_Line;
727
728 IF ( p_database_object_name = 'ASO_AK_QUOTE_HEADER_V'
729 OR p_database_object_name = 'ASO_AK_QUOTE_LINE_V')
730 THEN
731 --
732 -- Construct Get_Dependent_Attributes_Sets
733 --
734 -- Construct the procedure header
735 Text('PROCEDURE Get_Dependent_Attributes_Sets',0);
736 Parameter('init_msg_list','IN',
737 'VARCHAR2',1,TRUE,':= fnd_api.g_false',30,TRUE);
738 -- One output table for each trigger attribute
739 FOR l_trigger_attr_rec IN c_all_trigger_attribute
740 LOOP
741 Parameter(l_trigger_attr_rec.attribute_code||'_TBL',
742 'OUT', 'ASO_DEFAULTING_INT.attribute_Ids_Tbl_Type',1);
743 END LOOP;
744 Parameter('return_status', 'OUT', 'VARCHAR2',1);
745 Parameter('msg_count', 'OUT', 'NUMBER',1);
746 Parameter('msg_data', 'OUT', 'VARCHAR2',1);
747 Text(') ',0);
748 Text('IS', 0);
749 New_Line;
750
751 -- Construct the Local variables
752 Text('l_api_name CONSTANT VARCHAR2 ( 50 ) := ''Get_Dependent_Attributes_Sets'';', 1);
753 Text('broadcast_exception EXCEPTION;', 1);
754 New_Line;
755
756 Text('BEGIN', 0);
757
758 Api_Body_Start;
759
760 -- fill output table
761 FOR l_trigger_attr_rec IN c_trigger_attribute
762 LOOP
763
764 normalized_trigger_id := l_trigger_attr_rec.attribute_id - g_min;
765 starting_point := normalized_trigger_id * G_Max;
766
767 -- Construct the block for all chained dependent attributes for this trigger
768 i := 0;
769 WHILE ( g_dep_chain_tbl.EXISTS(starting_point + i)
770 AND i < G_Max
771 )
772 LOOP
773 OPEN c_attribute_code(g_dep_chain_tbl(starting_point + i).attribute + g_min);
774 FETCH c_attribute_code INTO l_attribute_code;
775 CLOSE c_attribute_code;
776
777 Text('x_'||LOWER(l_trigger_attr_rec.attribute_code)
778 ||'_tbl('||TO_CHAR(i+1)||') := '||'G_'||l_attribute_code||';',1);
779
780 i := i + 1;
781 END LOOP;
782
783 END LOOP;
784
785
786 Api_Body_End;
787
788 Text('END Get_Dependent_Attributes_Sets;',0);
789 NEW_LINE;
790 NEW_LINE;
791
792 -- if quoting entities
793 END IF;
794
795 --
796 -- Construct Get_Dependent_Attributes_Proc
797 --
798 -- Construct the procedure header
799 Text('PROCEDURE Get_Dependent_Attributes_Proc',0);
800 Parameter('init_msg_list','IN',
801 'VARCHAR2',1,TRUE,':= fnd_api.g_false',30,TRUE);
802 Parameter('trigger_record','IN',
803 l_ak_view_name||'%ROWTYPE',1);
804 Parameter('TRIGGERS_ID_TBL','IN',
805 'ASO_DEFAULTING_INT.attribute_Ids_Tbl_Type',1,
806 TRUE,':= ASO_DEFAULTING_INT.G_MISS_ATTRIBUTE_IDS_TBL');
807 Parameter('control_record','IN','ASO_DEFAULTING_INT.Control_Rec_Type',1,
808 TRUE,':= ASO_DEFAULTING_INT.G_MISS_CONTROL_REC');
809 Parameter('dependent_record', 'OUT', l_ak_view_name||'%ROWTYPE',1);
810 Parameter('return_status', 'OUT', 'VARCHAR2',1);
811 Parameter('msg_count', 'OUT', 'NUMBER',1);
812 Parameter('msg_data', 'OUT', 'VARCHAR2',1);
813 Text(') ',0);
814 Text('IS', 0);
815 New_Line;
816
817 -- Construct the cursor
818
819 l_ac_cursor_name := 'c_attribute_code';
820 Text('CURSOR '||l_ac_cursor_name||' (lc_attribute_id NUMBER)', 1);
821 Text('IS', 1);
822 Text(' SELECT attribute_code', 1);
823 Text(' FROM oe_ak_obj_attr_ext', 1);
824 Text(' WHERE attribute_id = lc_attribute_id;', 1);
825 NEW_LINE;
826
827 l_ck_cursor_name := 'c_ak_'||l_entity_name;
828 Text('CURSOR '||l_ck_cursor_name||' (lc_'||p_primary_key_name||' NUMBER)', 1);
829 Text('IS', 1);
830 Text(' SELECT ', 1);
831
832 i := 0;
833 FOR l_attribute_rec IN c_attributes LOOP
834
835 IF i= 0 THEN
836 Text(' '||l_attribute_rec.column_name,1);
837 ELSE
838 Text(' ,'||l_attribute_rec.column_name,1);
839 END IF;
840
841 i := i + 1;
842
843 END LOOP;
844
845
846 Text(' FROM '||l_ak_view_name, 1);
847 Text(' WHERE '||p_primary_key_name||' = lc_'||p_primary_key_name||';', 1);
848
849 NEW_LINE;
850
851 lv_ak_var_name := 'lv_ak_'||l_entity_name;
852 Text(lv_ak_var_name||' '||l_ck_cursor_name||'%ROWTYPE;', 1);
853 Text('l_api_name CONSTANT VARCHAR2 ( 50 ) := ''Get_Dependent_Attributes_Proc'';', 1);
854 Text('l_triggers_id_tbl ASO_DEFAULTING_INT.attribute_Ids_Tbl_Type;', 1);
855 Text('l_count NUMBER;', 1);
856 Text('l_attribute_code VARCHAR2(30);', 1);
857 Text('l_record_found BOOLEAN := TRUE;', 1);
858 Text('broadcast_exception EXCEPTION;', 1);
859 New_Line;
860
861 Text('BEGIN', 0);
862
863 Api_Body_Start;
864
865 Text('IF ASO_DEBUG_PUB.G_DEBUG_FLAG = ''Y''', 1);
866 Text('THEN ', 1);
867 FOR l_attribute_rec IN c_attributes LOOP
868 l_attribute_name := l_attribute_rec.column_name;
869 Text(' ASO_DEBUG_PUB.ADD(''p_trigger_record.'||
870 l_attribute_name||'= ''||p_trigger_record.'||l_attribute_name||', 1, ''N'');', 1);
871 END LOOP;
872 Text('END IF;', 1);
873 NEW_LINE;
874
875 COMMENT('Get records from database', 1);
876 Text('OPEN '||l_ck_cursor_name||'(p_trigger_record.'||p_primary_key_name||');', 1);
877 Text('FETCH '||l_ck_cursor_name||' INTO '||lv_ak_var_name||';', 1);
878 Text('IF '||l_ck_cursor_name||'%NOTFOUND', 1);
879 Text('THEN', 1);
880 Text(' l_record_found := FALSE;', 1);
881 Text('END IF; ', 1);
882 Text('CLOSE '||l_ck_cursor_name||';', 1);
883 Text('x_dependent_record := p_trigger_record;', 1);
884 NEW_LINE;
885
886 Text('IF l_record_found = TRUE', 1);
887 Text('THEN', 1);
888 NEW_LINE;
889 Text(' IF ASO_DEBUG_PUB.G_DEBUG_FLAG = ''Y''', 1);
890 Text(' THEN ', 1);
891 FOR l_attribute_rec IN c_attributes LOOP
892 l_attribute_name := l_attribute_rec.column_name;
893 Text(' ASO_DEBUG_PUB.ADD('''||lv_ak_var_name||'.'||
894 l_attribute_name||'= ''||'||lv_ak_var_name||'.'||l_attribute_name||', 1, ''N'');', 1);
895 END LOOP;
896 Text(' END IF;', 1);
897 NEW_LINE;
898
899 COMMENT(' Initialize the G_MISS fields from the database', 1);
900 FOR l_attribute_rec IN c_attributes LOOP
901 l_miss_str := l_attribute_rec.miss_str;
902 l_attribute_name := l_attribute_rec.column_name;
903
904 -- skip primary key since it must not be g_miss.
905 IF (l_attribute_name <> p_primary_key_name)
906 THEN
907 Text(' IF x_dependent_record.'||l_attribute_name||' = '||l_miss_str, 1);
908 Text(' THEN', 1);
909 Text(' x_dependent_record.'||l_attribute_name||' := '
910 ||lv_ak_var_name||'.'||l_attribute_name||';', 1);
911 Text(' END IF;', 1);
912 NEW_LINE;
913 END IF;
914
915 END LOOP;
916 NEW_LINE;
917 Text('END IF;');
918 NEW_LINE;
919
920 Text('l_triggers_id_tbl := p_triggers_id_tbl;', 1);
921 NEW_LINE;
922 Text('IF (ASO_DEBUG_PUB.G_DEBUG_FLAG = ''Y'' AND l_triggers_id_tbl.count > 0)', 1);
923 Text('THEN ', 1);
924 Text(' ASO_DEBUG_PUB.ADD(''Get_Dependent_Attributes_Proc: Initial Trigger Attributes'', 1, ''N'');', 1);
925 NEW_LINE;
926 Text(' l_count := l_triggers_id_tbl.FIRST;');
927 Text(' WHILE l_count IS NOT NULL LOOP');
928 NEW_LINE;
929 Text(' OPEN c_attribute_code(l_count);');
930 Text(' FETCH c_attribute_code INTO l_attribute_code;');
931 Text(' CLOSE c_attribute_code;');
932 NEW_LINE;
933 Text(' aso_debug_pub.add(l_attribute_code,1,''N'');');
934 NEW_LINE;
935 Text(' l_count := l_triggers_id_tbl.NEXT(l_count);');
936 NEW_LINE;
937 Text(' END LOOP; ');
938 Text('END IF;', 1);
939 NEW_LINE;
940
941 -- building the block for generating trigger list if not passed.
942 COMMENT('If trigger attributes list passed, compose trigger list by comparing values', 1);
943 COMMENT('of passed attributes whith those in database.', 1);
944 Text('IF (l_triggers_id_tbl.count = 0 AND l_record_found = TRUE)', 1);
945 Text('THEN', 1);
946 NEW_LINE;
947 COMMENT(' Check whether the database record has been changed.', 1);
948 Text(' IF (p_control_record.last_update_date is NULL OR', 1);
949 Text(' p_control_record.last_update_date = FND_API.G_MISS_Date ) ', 1);
950 Text(' THEN', 1);
951 Text(' IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN', 1);
952 Text(' FND_MESSAGE.Set_Name(''ASO'', ''ASO_API_MISSING_COLUMN'');', 1);
953 Text(' FND_MESSAGE.Set_Token(''COLUMN'', ''Last_Update_Date'', FALSE);', 1);
954 Text(' FND_MSG_PUB.ADD;', 1);
955 Text(' END IF;', 1);
956 Text(' RAISE broadcast_exception;', 1);
957 Text(' END IF;', 1);
958 Text(' IF (p_control_record.last_update_date <> '||lv_ak_var_name||'.'||p_last_update_date_name||')', 1);
959 Text(' THEN', 1);
960 Text(' IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN', 1);
961 Text(' FND_MESSAGE.Set_Name(''ASO'', ''ASO_API_RECORD_CHANGED'');', 1);
962 Text(' FND_MESSAGE.Set_Token(''INFO'', ''quote'', FALSE);', 1);
963 Text(' FND_MSG_PUB.ADD;', 1);
964 Text(' END IF;', 1);
965 Text(' RAISE broadcast_exception;', 1);
966 Text(' END IF;', 1);
967 NEW_LINE;
968
969 COMMENT('compose trigger id list', 1);
970 FOR l_trigger_attr_rec IN c_trigger_attribute LOOP
971 l_attribute_name := l_trigger_attr_rec.column_name;
972 Text(' IF ( (x_dependent_record.'||l_attribute_name||' IS NOT NULL', 1);
973 Text(' AND '||lv_ak_var_name||'.'||l_attribute_name||' IS NOT NULL', 1);
974 Text(' AND x_dependent_record.'||l_attribute_name||' <> '||lv_ak_var_name||'.'||l_attribute_name||')', 1);
975 Text(' OR', 1);
976 Text(' (x_dependent_record.'||l_attribute_name||' IS NOT NULL ', 1);
977 Text(' AND '||lv_ak_var_name||'.'||l_attribute_name||' IS NULL)', 1);
978 Text(' OR', 1);
979 Text(' (x_dependent_record.'||l_attribute_name||' IS NULL', 1);
980 Text(' AND '||lv_ak_var_name||'.'||l_attribute_name||' IS NOT NULL) ', 1);
981 Text(' )', 1);
982 Text(' THEN', 1);
983 Text(' ', 1);
984 Text(' l_triggers_id_tbl(G_'||l_trigger_attr_rec.attribute_code||') := 1;', 1);
985 Text(' ', 1);
986 Text(' END IF;', 1);
987 NEW_LINE;
988 END LOOP;
989
990 -- for l_triggers_id_tbl.count = 0
991 Text('END IF;', 1);
992 NEW_LINE;
993 Text('IF (ASO_DEBUG_PUB.G_DEBUG_FLAG = ''Y'' AND l_triggers_id_tbl.count > 0)', 1);
994 Text('THEN ', 1);
995 Text(' ASO_DEBUG_PUB.ADD(''Get_Dependent_Attributes_Proc: Final Trigger Attributes'', 1, ''N'');', 1);
996 NEW_LINE;
997 Text(' l_count := l_triggers_id_tbl.FIRST;');
998 Text(' WHILE l_count IS NOT NULL LOOP');
999 NEW_LINE;
1000 Text(' OPEN c_attribute_code(l_count);');
1001 Text(' FETCH c_attribute_code INTO l_attribute_code;');
1002 Text(' CLOSE c_attribute_code;');
1003 NEW_LINE;
1004 Text(' aso_debug_pub.add(l_attribute_code,1,''N'');');
1005 NEW_LINE;
1006 Text(' l_count := l_triggers_id_tbl.NEXT(l_count);');
1007 NEW_LINE;
1008 Text(' END LOOP; ');
1009 Text('END IF;', 1);
1010 NEW_LINE;
1011
1012 -- adding the block for g_miss
1013 COMMENT('Put g_miss for dependent attributes for each trigger', 1);
1014 Text('IF (l_triggers_id_tbl.count <> 0)', 1);
1015 Text('THEN', 1);
1016 FOR l_trigger_attr_rec IN c_trigger_attribute LOOP
1017 l_trigger_code := l_trigger_attr_rec.attribute_code;
1018 normalized_trigger_id := l_trigger_attr_rec.attribute_id - g_min;
1019 starting_point := normalized_trigger_id * G_Max;
1020
1021 NEW_LINE;
1022 Text(' IF l_triggers_id_tbl.exists(G_'||l_trigger_code||')', 1);
1023 Text(' THEN', 1);
1024
1025 NEW_LINE;
1026 i := 0;
1027 WHILE ( g_dep_chain_tbl.EXISTS(starting_point + i)
1028 AND i < G_Max
1029 )
1030 LOOP
1031 OPEN c_attribute_code(g_dep_chain_tbl(starting_point + i).attribute + g_min);
1032 FETCH c_attribute_code INTO l_attribute_code;
1033 CLOSE c_attribute_code;
1034
1035 OPEN c_attribute_column_name(l_attribute_code);
1036 FETCH c_attribute_column_name INTO l_attribute_name;
1037 CLOSE c_attribute_column_name;
1038
1039 -- Get the G_MISS value of this trigger
1040 OPEN c_data_type(l_attribute_code);
1041 FETCH c_data_type INTO l_miss_str;
1042 CLOSE c_data_type;
1043
1044 Text(' IF p_control_record.override_trigger_flag = FND_API.G_TRUE', 1);
1045 Text(' OR', 1);
1046 Text(' (p_control_record.override_trigger_flag = FND_API.G_FALSE', 1);
1047 Text(' AND NOT l_triggers_id_tbl.exists(G_'||l_attribute_code||')', 1);
1048 Text(' )', 1);
1049 Text(' THEN', 1);
1050 Text(' x_dependent_record.'||l_attribute_name||' := '||l_miss_str||';', 1);
1051 Text(' END IF;', 1);
1052 NEW_LINE;
1053 i := i + 1;
1054 END LOOP;
1055 -- if trigger exist
1056 Text(' END IF;', 1);
1057 NEW_LINE;
1058 END LOOP;
1059
1060 -- l_triggers_id_tbl.count <> 0
1061 Text('END IF;', 1);
1062
1063 Api_Body_End;
1064
1065 Text('END Get_Dependent_Attributes_Proc;',0);
1066
1067 Pkg_End (l_pkg_name,'BODY');
1068
1069 retcode := 0;
1070 EXCEPTION
1071
1072 WHEN FND_API.G_EXC_ERROR THEN
1073 PUT_LINE('Please check the log file for error messages');
1074 retcode := 2;
1075 errbuf := 'Please check the log file for error messages';
1076
1077 WHEN OTHERS THEN
1078 PUT_LINE( 'Error in creating entity def hdlr '||sqlerrm);
1079 retcode := 2;
1080 errbuf := sqlerrm;
1081
1082 END Make_Dependency_Engine_Body;
1083
1084 PROCEDURE Attribute_Code_To_Id
1085 ( P_ATTRIBUTE_CODES_TBL IN ASO_DEFAULTING_INT.attribute_Codes_Tbl_Type
1086 , P_DATABASE_OBJECT_NAME IN VARCHAR2
1087 , X_ATTRIBUTE_IDS_TBL OUT NOCOPY /* file.sql.39 change */ ASO_DEFAULTING_INT.attribute_Ids_Tbl_Type
1088 )
1089 IS
1090
1091 l_count NUMBER;
1092 l_attribute_id NUMBER;
1093
1094 CURSOR c_attribute_id (
1095 lc_attribute_code VARCHAR2
1096 ) IS
1097 SELECT attribute_id
1098 FROM oe_ak_obj_attr_ext
1099 WHERE attribute_code = lc_attribute_code
1100 AND database_object_name = p_database_object_name
1101 AND attribute_application_id = G_APPLICATION_ID;
1102 BEGIN
1103
1104 l_count := p_attribute_codes_tbl.FIRST;
1105
1106 -- For each trigger attribute code
1107 WHILE l_count IS NOT NULL LOOP
1108
1109 OPEN c_attribute_id(p_attribute_codes_tbl(l_count));
1110 FETCH c_attribute_id INTO l_attribute_id;
1111 CLOSE c_attribute_id;
1112
1113 x_attribute_ids_tbl(l_attribute_id) := 1;
1114
1115 l_count := p_attribute_codes_tbl.NEXT(l_count);
1116
1117 END LOOP; -- next source attribute
1118
1119 END Attribute_Code_To_Id;
1120
1121 END ASO_DEPENDENCY_UTIL;