[Home] [Help]
PACKAGE BODY: APPS.OE_DEFAULTING_FWK_PUB
Source
1 PACKAGE BODY OE_Defaulting_Fwk_PUB AS
2 /* $Header: OEXDFWKB.pls 120.2 2006/09/15 17:22:43 chhung noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OE_Defaulting_Fwk_PUB';
7 g_login_seq VARCHAR2(15) := abs(FND_GLOBAL.login_id);
8 g_schema VARCHAR2(30);
9 g_conc_mode VARCHAR2(1);
10
11 -- 5529963 : from ad_ddl.build_package spec. max_line_size is up tp 256 char
12 g_max_line_size NUMBER := 256;
13
14 line_number NUMBER := 0;
15 -- Global Cache Table defined for Performance issue we want to genarate PkgBdy once
16 TYPE g_cache_pkgbdy_rec_Type IS RECORD
17 ( entity_id number default null,
18 package_spec varchar2(1) default null,
19 package_body varchar2(1) default null);
20
21 TYPE g_PkgBdy_Tbl_Type IS TABLE OF g_cache_pkgbdy_rec_Type
22 INDEX BY BINARY_INTEGER;
23
24 g_PkgBdy_tbl g_PkgBdy_Tbl_Type;
25
26 -- LOCAL PROCEDURES
27 -------------------------------------------------------------------------
28 PROCEDURE Put_Line
29 (Text Varchar2)
30 IS
31 --
32 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
33 --
34 BEGIN
35
36 if g_conc_mode is null then
37
38 if nvl(fnd_profile.value('CONC_REQUEST_ID'),0) <> 0 then
39 g_conc_mode := 'Y';
40 else
41 g_conc_mode := 'N';
42 end if;
43
44 end if;
45
46 if g_conc_mode = 'Y' then
47 FND_FILE.PUT_LINE(FND_FILE.LOG, Text);
48 end if;
49
50 END Put_Line;
51
52 PROCEDURE Init_Applsys_Schema
53 IS
54 l_app_info BOOLEAN;
55 l_status VARCHAR2(30);
56 l_industry VARCHAR2(30);
57 --
58 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
59 --
60 BEGIN
61
62 if g_schema is null then
63
64 l_app_info := FND_INSTALLATION.GET_APP_INFO
65 ('FND',l_status, l_industry, g_schema);
66
67 end if;
68
69 END;
70
71 -------------------------------------------------------------------------
72 PROCEDURE New_Line
73 IS
74 --
75 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
76 --
77 BEGIN
78
79 line_number := line_number + 1;
80 ad_ddl.build_package(' ',line_number);
81
82 END New_Line;
83
84 -------------------------------------------------------------------------
85 PROCEDURE Comment
86 ( p_comment IN VARCHAR2
87 , p_level IN NUMBER default 1
88 )
89 IS
90 --
91 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
92 --
93 BEGIN
94
95 Text('-- '||p_comment,p_level);
96
97 END Comment;
98
99 -------------------------------------------------------------------------------
100 PROCEDURE Text
101 ( p_string IN VARCHAR2
102 , p_level IN NUMBER default 1
103 )
104 IS
105 --
106 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
107 --
108 BEGIN
109
110 line_number := line_number + 1;
111 /* replaced with the next line for bug fix 3179429
112 ad_ddl.build_package(LPAD(p_string,p_level*2+LENGTH(p_string)),line_number);
113 */
114 ad_ddl.build_package(LPAD(' ', p_level*2)||p_string, line_number);
115
116 END text;
117
118 -------------------------------------------------------------------------------
119 PROCEDURE Parameter
120 ( p_param IN VARCHAR2
121 , p_mode IN VARCHAR2 := 'IN'
122 , p_type IN VARCHAR2 := 'NUMBER'
123 , p_level IN NUMBER default 1
124 , p_rpad IN NUMBER := 30
125 , p_first IN BOOLEAN := FALSE
126 )
127 IS
128 l_first varchar2(1);
129 l_prefix VARCHAR2(10);
130 --
131 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
132 --
133 BEGIN
134
135 IF rtrim(p_mode) = 'IN' THEN
136 l_prefix := ' p_';
137 ELSE
138 l_prefix := ' x_';
139 END IF;
140
141 IF p_first THEN
142 l_first := '(';
143 ELSE
144 l_first := ',';
145 END IF;
146
147 Text( l_first||l_prefix||RPAD(p_param,p_rpad)||RPAD(p_mode,4)||p_type
148 , p_level
149 );
150
151 END Parameter;
152
153 -------------------------------------------------------------------------------
154 PROCEDURE Pkg_End
155 ( p_pkg_name IN VARCHAR2
156 , p_pkg_type IN VARCHAR2
157 )
158 IS
159
160 l_is_pkg_body VARCHAR2(30);
161 n NUMBER := 0;
162 CURSOR errors IS
163 SELECT line, text
164 FROM user_errors
165 WHERE name = upper(p_pkg_name)
166 AND type = decode(p_pkg_type,'SPEC','PACKAGE',
167 'BODY','PACKAGE BODY');
168 --
169 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
170 --
171 BEGIN
172
173 -- end statement.
174 Text('END '||p_pkg_name||';',0);
175
176 -- Show errors.
177 IF p_pkg_type = 'BODY' THEN
178 l_is_pkg_body := 'TRUE';
179 ELSE
180 l_is_pkg_body := 'FALSE';
181 END IF;
182
183 PUT_LINE(
184 'Call AD_DDL to create '||p_pkg_type||' of package '||p_pkg_name);
185
186
187 ad_ddl.create_package(applsys_schema => g_schema
188 ,application_short_name => 'ONT'
189 ,package_name => p_pkg_name
190 ,is_package_body => l_is_pkg_body
191 ,lb => 1
192 ,ub => line_number);
193
194 -- if there were any errors when creating this package, print out
195 -- the errors in the log file
196 FOR error IN errors LOOP
197 if n= 0 then
198 PUT_LINE('ERROR in creating PACKAGE '||p_pkg_type||' :'||p_pkg_name);
199 end if;
200 PUT_LINE(
201 'LINE :'||error.line||' '||substr(error.text,1,200));
202 n := 1;
203 END LOOP;
204
205 -- if there was an error in compiling the package, raise
206 -- an error
207 if n > 0 then
208 RAISE FND_API.G_EXC_ERROR;
209 end if;
210
211 exception
212 when FND_API.G_EXC_ERROR then
213 raise FND_API.G_EXC_ERROR;
214 when others THEN
215 raise_application_error(-20000,SQLERRM||' '||ad_ddl.error_buf);
216 -- PUT_LINE('Iam into exception' ||ad_ddl.error_buf);
217 -- RAISE FND_API.G_EXC_ERROR;
218
219 END Pkg_End;
220
221 -------------------------------------------------------------------------------
222 -- Generates the Package Header for the package SPEC AND BODY
223
224 PROCEDURE Pkg_Header
225 ( p_pkg_name IN VARCHAR2
226 , p_pkg_type IN VARCHAR2
227 )
228 IS
229 header_string VARCHAR2(200);
230 --
231 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
232 --
233 BEGIN
234
235 -- Initialize line number
236 line_number := 0;
237
238 -- Define package.
239
240 IF p_pkg_type = 'BODY' THEN
241 Text ('CREATE or REPLACE PACKAGE BODY '||
242 p_pkg_name|| ' AS',0);
243 ELSE
244 Text ('CREATE or REPLACE PACKAGE '||
245 p_pkg_name|| ' AUTHID CURRENT_USER AS',0);
246 END IF;
247
248 -- $Header clause.
249 header_string := 'Header: OEXDFWKB.pls 115.0 '||sysdate||' 23:23:31 appldev ship ';
250 Text('/* $'||header_string||'$ */',0);
251 New_Line;
252
253 -- Copyright section.
254
255 Comment ( '',0 );
256 Comment (
257 'Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA',0);
258 Comment ( 'All rights reserved.',0);
259 Comment ( '',0);
260 Comment ( 'FILENAME',0);
261 Comment ( '',0);
262 Comment ( ' '||p_pkg_name,0);
263 Comment ( '',0);
264 Comment ( 'DESCRIPTION',0);
265 Comment ( '',0);
266 Comment ( ' '||INITCAP(p_pkg_type)||' of package '
267 ||p_pkg_name,0);
268 Comment ( '',0);
269 Comment ('NOTES',0);
270 Comment ( '',0);
271 Comment ('HISTORY',0);
272 Comment ( '',0);
273 Comment ( TO_CHAR(SYSDATE)||' Created',0);
274 Comment ( '',0);
275 New_Line;
276
277 -- Global constant holding package name.
278
279 IF p_pkg_type = 'BODY' THEN
280 Comment ( 'Global constant holding the package name',0);
281 Text (RPAD('G_PKG_NAME',30)||'CONSTANT '||
282 'VARCHAR2(30) := '''||p_pkg_name||''';',0);
283 New_Line;
284 END IF;
285
286 END Pkg_Header;
287
288 -------------------------------------------------------------------------------
289 PROCEDURE Assign
290 ( p_left IN VARCHAR2
291 , p_right IN VARCHAR2
292 , p_level IN NUMBER := 1
293 , p_rpad IN NUMBER := 30
294 )
295 IS
296 l_rpad NUMBER:=p_rpad;
297 --
298 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
299 --
300 BEGIN
301
302 IF p_rpad = -1 OR
303 p_rpad < LENGTH(p_left)
304 THEN
305 l_rpad := LENGTH(p_left);
306 END IF;
307
308 Text( RPAD(p_left,l_rpad)||' := '||p_right||';'
309 , p_level
310 );
311
312 END Assign;
313
314
315 --------------------------------------------------------------------------
316 -- PUBLIC PROCEDURES
317 --------------------------------------------------------------------------
318
319 --------------------------------------------------------------------------
320 -- PROCEDURE Create_Entity_Def_handler
321 -- This procedure generates the defaulting handler for the Rule based
322 -- Defaulting Framework.
323 -- Called by concurrent program: DEFGEN
324 --
325 -- ARGUMENTS:
326 -- retcode => parameter passed to concurrent program
327 -- errbuf => parameter passed to concurrent program
328 -- p_application_id => entity application ID (for Order Entry, = 300)
329 -- p_database_object_name => database object name stored in OE_AK_OBJECTS_EXT table
330 -- (for Order Line, = 'OE_AK_ORDER_LINES_V')
331 --------------------------------------------------------------------------
332
333 PROCEDURE Create_Entity_Def_handler
334 (
335 retcode OUT NOCOPY VARCHAR2,
336
337 errbuf OUT NOCOPY VARCHAR2,
338
339 p_application_id IN VARCHAR2,
340 p_database_object_name IN VARCHAR2,
341 p_attribute_code IN VARCHAR2 DEFAULT NULL,
342 p_generate_all IN VARCHAR2 DEFAULT 'Y'
343 )
344 IS
345
346 -- table declaration
347 TYPE obj_attr_tbl_type IS TABLE OF OE_DEF_AK_ATTR_EXT_V%ROWTYPE
348 INDEX BY BINARY_INTEGER;
349
350 l_obj_attr_tbl obj_attr_tbl_type;
351
352 -- variables declaration
353 J NUMBER;
354 l_entity_code VARCHAR2(15);
355 l_app_short_name VARCHAR2(3);
356 l_application_id NUMBER;
357 l_column_name VARCHAR2(30);
358 l_status VARCHAR2(10);
359 l_pkg_name VARCHAR2(30);
360 l_related_pkg VARCHAR2(30);
361 l_generated_pkg VARCHAR2(30);
362 l_attribute_code VARCHAR2(30);
363 l_defaulting_api VARCHAR2(61);
364 l_defaulting_api_pkg VARCHAR2(30);
365 l_defaulting_api_proc VARCHAR2(30);
366 l_validation_api VARCHAR2(61);
367 l_validation_api_pkg VARCHAR2(30);
368 l_validation_api_proc VARCHAR2(30);
369 l_depend_api VARCHAR2(61);
370 l_dependent_api_pkg VARCHAR2(30);
371 l_dependent_api_proc VARCHAR2(30);
372 l_security_api_pkg VARCHAR2(30);
373 l_security_api_proc VARCHAR2(30);
374 l_security_api VARCHAR2(61);
375 l_buffer VARCHAR2(20);
376 l_defaulting_condn_ref_flag VARCHAR2(1);
377 l_defaulting_enabled_flag VARCHAR2(1);
378 l_data_type VARCHAR2(30);
379 l_related_entity_code VARCHAR2(15);
380 l_related_database_object_name VARCHAR2(30);
381 l_uk_name VARCHAR2(30);
382 l_fk_name VARCHAR2(30);
383 l_entity_id NUMBER;
384
385 -- CURSOR to SELECT the application name
386
387 CURSOR APP
388 is
389 SELECT substr(rtrim(APPLICATION_SHORT_NAME),1,3)
390 FROM fnd_application
391 WHERE application_id = p_application_id;
392
393 CURSOR OBJ (p_database_object_name varchar2,p_application_id number)
394 is
395 SELECT ENTITY_CODE
396 FROM OE_DEF_AK_OBJ_EXT_V
397 WHERE database_object_name = p_database_object_name
398 AND application_id = p_application_id;
399
400
401 -- This CURSOR SELECTs all the related views for the current entity.
402 -- This will be used when the defaulting source is FROM a "Related Record"
403
404 CURSOR FKEY (p_database_object_name varchar2,p_application_id number)
405 IS
406 SELECT distinct fk.unique_key_name
407 ,uk.database_object_name uk_database_object_name
408 ,fk.foreign_key_name
409 ,obj.entity_code uk_entity_code
410 , obj.defaulting_enabled_flag uk_obj_defaulting_enabled
411 FROM AK_FOREIGN_KEYS fk, AK_UNIQUE_KEYS uk, OE_AK_OBJECTS_EXT obj
412 WHERE fk.database_object_name= p_database_object_name
413 AND fk.application_id= p_application_id
414 AND fk.unique_key_name = uk.unique_key_name
415 AND fk.application_id = uk.application_id
416 AND uk.database_object_name = obj.database_object_name
417 AND obj.application_id = p_application_id;
418
419 -- This CURSOR fetches all the attributes for the entity for which
420 -- defaulting can be done.
421 -- Note that only the attributes that are of 3rd normal form attributes
422 -- are being defaulted.
423
424 CURSOR OAORDER(p_database_object_name varchar2,p_application_id number)
425 is
426 SELECT ak.column_name,
427 oe.attribute_code,
428 ak.data_type,
429 ak.defaulting_api_pkg,
430 ak.defaulting_api_proc,
431 ak.validation_api_pkg,
432 ak.validation_api_proc,
433 oe.dependent_api_pkg,
434 oe.dependent_api_proc,
435 oe.security_api_pkg,
436 oe.security_api_proc,
437 oe.defaulting_condn_ref_flag,
438 oe.defaulting_enabled_flag
439 FROM OE_AK_OBJ_ATTR_EXT OE
440 , AK_OBJECT_ATTRIBUTES_VL ak
441 WHERE oe.database_object_name = p_database_object_name
442 AND oe.attribute_application_id = p_application_id
443 AND ak.database_object_name = oe.database_object_name
444 AND ak.attribute_code = oe.attribute_code
445 AND ak.attribute_application_id = oe.attribute_application_id
446 ORDER BY oe.defaulting_sequence, ak.attribute_label_long;
447
448 -- This CURSOR SELECTs the attribute code FROM ak unique keys.
449 -- This will be used only when the primary key is comprised of a single column.
450 -- If the primary key has more than one column, then in the later part of the
451 -- code we loop through to get all the keys.
452
453 CURSOR PKGSTATUS(cp_pkg_name VARCHAR2)
454 IS
455 SELECT NVL(status,'INVALID')
456 --FROM dba_objects
457 FROM user_objects
458 WHERE object_name = UPPER(cp_pkg_name);
459
460 l_attr_str VARCHAR2(50);
461 --
462 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
463 --
464 BEGIN
465
466 Init_Applsys_Schema;
467
468 open APP;
469 fetch APP into l_app_short_name;
470 close APP;
471
472 open OBJ (p_database_object_name, p_application_id);
473 fetch OBJ into l_entity_code;
474 close OBJ;
475
476 -- Performance Bug 1678746:
477 -- IF p_attribute_code is passed, then generate defaulting handler
478 -- package only for this attribute else generate for the entity
479 -- and all its attributes.
480
481 IF p_attribute_code IS NOT NULL THEN
482 PUT_LINE('Create defaulting handler for attribute: '
483 ||p_attribute_code);
484 Create_Obj_Attr_Def_handler
485 (p_application_id => p_application_id,
486 p_database_object_name => p_database_object_name,
487 p_attribute_code => p_attribute_code,
488 p_entity_code => l_entity_code,
489 p_generation_level => 'BODY_ONLY',
490 x_defaulting_api_pkg => l_generated_pkg );
491 PUT_LINE('Attribute handler package generated :'||l_generated_pkg);
492 RETURN;
493 END IF;
494
495 -- Construct the pkg name(eg ONT_Header_Def_Hdlr)
496 l_application_id := p_application_id;
497
498 l_pkg_name := l_app_short_name||'_'||l_entity_code||'_Def'||'_Hdlr';
499
500 -------------------------------------------------------------------------------
501 -- (1) GENERATE THE SPECS OF UTIL PACKAGES FOR RELATED ENTITITES
502 -- e.g. ONT_PRICE_LIST_DEF_UTIL
503 -- NOTE: The spec of ALL related entities should be generated BEFORE the
504 -- package body as there maybe calls to other entity utilities
505 -------------------------------------------------------------------------------
506
507 FOR fkey_view in FKEY (p_database_object_name ,p_application_id )
508 LOOP
509
510 l_related_database_object_name := fkey_view.uk_database_object_name;
511 l_uk_name := fkey_view.unique_key_name;
512 l_fk_name := fkey_view.foreign_key_name;
513 l_related_entity_code := fkey_view.uk_entity_code;
514
515 -- This will never fetch null entity code because the oe_def_ak_fkeys_v
516 -- will fetch only the entities WHERE entity_code is not null.
517 l_related_pkg := l_app_short_name||'_'||l_related_entity_code||'_Def'||'_Util';
518
519 OPEN PKGSTATUS(l_related_pkg );
520 FETCH PKGSTATUS INTO l_status;
521
522 if PKGSTATUS%NOTFOUND then
523 l_status := 'INVALID';
524 end if;
525 CLOSE PKGSTATUS;
526
527 -- For the related entities,
528 -- a) if p_generate_all= 'Y', generate util packages for all related entities
529 -- or b) if p_generate_all = 'N', then generate util package for related
530 -- entity only if the util package does not exist OR is invalid
531 -- Workaround for bug 1699929: For HEADER and LINE entities, do not generate
532 -- the spec as it will invalidate OE_HEADER_UTIL and OE_LINE_UTIL packages
533 IF (l_status = 'INVALID'
534 OR p_generate_all = 'Y')
535 AND (p_application_id <> 660
536 OR l_related_entity_code NOT IN ('HEADER','LINE') )
537 THEN
538
539 select entity_id into l_entity_id
540 from oe_ak_objects_ext
541 where DATABASE_OBJECT_NAME =l_related_database_object_name;
542
543 PUT_LINE(
544 '--------------------------------');
545 PUT_LINE('entity id :'||l_entity_id);
546 if g_PkgBdy_tbl.exists(l_entity_id) then
547 PUT_LINE('cache exists');
548 PUT_LINE('package spec :'||g_PkgBdy_tbl(l_entity_id).package_spec);
549 PUT_LINE('package body :'||g_PkgBdy_tbl(l_entity_id).package_body);
550 else
551 PUT_LINE('cache does not exist');
552 end if;
553
554 if (not g_PkgBdy_tbl.exists(l_entity_id) or nvl(g_PkgBdy_tbl(l_entity_id).package_spec,'X') <> 'S')
555 then
556
557 PUT_LINE(
558 'Create caching/util package for related entity: '||l_related_entity_code);
559 Create_Entity_Def_Util_handler
560 ( p_application_id =>p_application_id
561 , p_database_object_name => l_related_database_object_name
562 , p_entity_code => l_related_entity_code
563 , p_application_short_name => l_app_short_name
564 , p_obj_defaulting_enabled => fkey_view.uk_obj_defaulting_enabled
565 , p_generation_level => 'SPEC_ONLY'
566 );
567 g_PkgBdy_tbl(l_entity_id).package_spec := 'S';
568 g_PkgBdy_tbl(l_entity_id).entity_id := l_entity_id;
569 end if;
570
571
572 END IF;
573
574 END LOOP;
575
576
577 -------------------------------------------------------------------------------
578 -- (2) GENERATE THE BODY OF UTIL PACKAGES FOR RELATED ENTITITES
579 -- e.g. ONT_PRICE_LIST_DEF_UTIL
580 -------------------------------------------------------------------------------
581
582 FOR fkey_view in FKEY (p_database_object_name ,p_application_id )
583 LOOP
584
585 l_related_database_object_name := fkey_view.uk_database_object_name;
586 l_uk_name := fkey_view.unique_key_name;
587 l_fk_name := fkey_view.foreign_key_name;
588 l_related_entity_code := fkey_view.uk_entity_code;
589
590 -- This will never fetch null entity code because the oe_def_ak_fkeys_v
591 -- will fetch only the entities WHERE entity_code is not null.
592 l_related_pkg := l_app_short_name||'_'||l_related_entity_code||'_Def'||'_Util';
593
594 OPEN PKGSTATUS(l_related_pkg );
595 FETCH PKGSTATUS INTO l_status;
596
597 if PKGSTATUS%NOTFOUND then
598 l_status := 'INVALID';
599 end if;
600 CLOSE PKGSTATUS;
601
602 -- For the related entities,
603 -- a) if p_generate_all= 'Y', generate util packages for all related entities
604 -- or b) if p_generate_all = 'N', then generate util package for related
605 -- entity only if the util package does not exist OR is invalid
606 IF l_status = 'INVALID'
607 OR p_generate_all = 'Y'
608 THEN
609 select entity_id into l_entity_id
610 from oe_ak_objects_ext
611 where DATABASE_OBJECT_NAME =l_related_database_object_name;
612
613 PUT_LINE(
614 '--------------------------------');
615 PUT_LINE('entity id :'||l_entity_id);
616 if g_PkgBdy_tbl.exists(l_entity_id) then
617 PUT_LINE('cache exists');
618 PUT_LINE('package spec :'||g_PkgBdy_tbl(l_entity_id).package_spec);
619 PUT_LINE('package body :'||g_PkgBdy_tbl(l_entity_id).package_body);
620 else
621 PUT_LINE('cache does not exist');
622 end if;
623
624 if ( not g_PkgBdy_tbl.exists(l_entity_id) or nvl(g_PkgBdy_tbl(l_entity_id).package_body,'X') <> 'B') then
625
626 PUT_LINE(
627 'Create caching/util package for related entity: '||l_related_entity_code);
628
629 Create_Entity_Def_Util_handler
630 ( p_application_id =>p_application_id
631 , p_database_object_name => l_related_database_object_name
632 , p_entity_code => l_related_entity_code
633 , p_application_short_name => l_app_short_name
634 , p_obj_defaulting_enabled => fkey_view.uk_obj_defaulting_enabled
635 , p_generation_level => 'BODY_ONLY'
636 );
637 g_PkgBdy_tbl(l_entity_id).entity_id := l_entity_id;
638 g_PkgBdy_tbl(l_entity_id).package_body := 'B';
639 end if;
640 -- populate cache
641
642 END IF;
643
644 END LOOP;
645
646
647 -------------------------------------------------------------------------------
648 -- (3) GENERATE THE SPEC AND BODY OF UTIL PACKAGE FOR CURRENT ENTITY
649 -- e.g. ONT_LINE_DEF_UTIL
650 -- Workaround for bug 1699929: For HEADER and LINE entities, generate the packages
651 -- after the attribute defaulting packages as Create_Obj_Attr_Def_handler
652 -- calls the entity util packages dynamically (OE_HEADER_UTIL and OE_LINE_UTIL)
653 -- dynamically to get the attribute constant values. And since these UTIL
654 -- packages call the def util package (ONT_LINE_DEF_UTIL etc.), they
655 -- are invalidated and dynamic calls fail with a coredump due to an RDBMS issue.
656 -------------------------------------------------------------------------------
657
658 IF p_application_id <> 660
659 OR l_entity_code NOT IN ('HEADER','LINE') THEN
660
661 select entity_id into l_entity_id
662 from oe_ak_objects_ext
663 where DATABASE_OBJECT_NAME =p_database_object_name;
664
665 PUT_LINE(
666 '--------------------------------');
667 PUT_LINE('entity id :'||l_entity_id);
668 if g_PkgBdy_tbl.exists(l_entity_id) then
669 PUT_LINE('cache exists');
670 PUT_LINE('package spec :'||g_PkgBdy_tbl(l_entity_id).package_spec);
671 PUT_LINE('package body :'||g_PkgBdy_tbl(l_entity_id).package_body);
672 else
673 PUT_LINE('cache does not exist');
674 end if;
675
676 if ( not g_PkgBdy_tbl.exists(l_entity_id) or nvl(g_PkgBdy_tbl(l_entity_id).package_body,'X') <> 'B'
677 or nvl(g_PkgBdy_tbl(l_entity_id).package_spec,'X') <> 'S') then
678
679 PUT_LINE(
680 'Create caching/util package for current entity '||l_entity_code);
681 Create_Entity_Def_Util_handler
682 (p_application_id
683 ,p_database_object_name
684 ,l_entity_code
685 , l_app_short_name);
686 g_PkgBdy_tbl(l_entity_id).entity_id := l_entity_id;
687 g_PkgBdy_tbl(l_entity_id).package_spec := 'S';
688 g_PkgBdy_tbl(l_entity_id).package_body := 'B';
689
690 end if;
691
692 END IF;
693
694 -------------------------------------------------------------------------------
695 -- (4) GENERATE THE ATTRIBUTE DEFAULTING PACKAGES e.g. ONT_D2_PAYMENT_TERM_ID
696 -- As these packages contain calls to the util packages, these should be
697 -- generated after the util package
698 -------------------------------------------------------------------------------
699
700 -- Loop to get all the attributes for the entity that are to be defaulted.
701 -- Get the name of the defaulting pkg name,validation pkg name AND
702 -- the dependent pkg name.
703
704
705 j := 0;
706
707 FOR obj_attr in OAORDER(p_database_object_name ,p_application_id )
708 LOOP
709
710 -- Retrieve all the values into a PL/SQL table
711 l_obj_attr_tbl(j).column_name := obj_attr.column_name;
712 l_obj_attr_tbl(j).attribute_code := obj_attr.attribute_code;
713 l_obj_attr_tbl(j).data_type := obj_attr.data_type;
714 l_obj_attr_tbl(j).defaulting_api_pkg := obj_attr.defaulting_api_pkg;
715 l_obj_attr_tbl(j).defaulting_api_proc := obj_attr.defaulting_api_proc;
716 l_obj_attr_tbl(j).validation_api_pkg := obj_attr.validation_api_pkg;
717 l_obj_attr_tbl(j).validation_api_proc := obj_attr.validation_api_proc;
718 l_obj_attr_tbl(j).dependent_api_pkg := obj_attr.dependent_api_pkg;
719 l_obj_attr_tbl(j).dependent_api_proc := obj_attr.dependent_api_proc;
720 l_obj_attr_tbl(j).security_api_pkg := obj_attr.security_api_pkg;
721 l_obj_attr_tbl(j).security_api_proc := obj_attr.security_api_proc;
722 l_obj_attr_tbl(j).defaulting_condn_ref_flag := obj_attr.defaulting_condn_ref_flag;
723 l_obj_attr_tbl(j).defaulting_enabled_flag := obj_attr.defaulting_enabled_flag;
724
725 -- For all the attributes that can be defaulted,
726 -- a) if p_generate_all= 'Y', generate all attribute handlers
727 -- or b) if p_generate_all = 'N', then generate attributes handlers only if
728 -- there is NO defaulting API registered in the AK dictionary
729
730 IF upper(obj_attr.defaulting_enabled_flag) = 'Y' THEN
731
732 IF (p_generate_all = 'Y')
733 OR (p_generate_all = 'N' AND
734 ( obj_attr.defaulting_api_pkg is null
735 OR obj_attr.defaulting_api_proc is null)
736 )
737 THEN
738
739 PUT_LINE(
740 '------------------------------------------------------------------------------');
741 PUT_LINE(
742 'Create defaulting handler for attribute: '||obj_attr.attribute_code);
743 Create_Obj_Attr_Def_handler
744 ( p_application_id => p_application_id,
745 p_database_object_name => p_database_object_name,
746 p_attribute_code => obj_attr.attribute_code,
747 p_entity_code => l_entity_code,
748 x_defaulting_api_pkg => l_generated_pkg );
749 PUT_LINE(
750 '------------------------------------------------------------------------------');
751
752 l_obj_attr_tbl(j).defaulting_api_pkg := l_generated_pkg;
753 l_obj_attr_tbl(j).defaulting_api_proc := 'Get_Default_Value';
754
755 END IF;
756
757 END IF; -- if attribute is defaultable
758
759 j := j + 1;
760
761 END LOOP;
762
763 -------------------------------------------------------------------------------
764 -- Workaround for bug 1699929: For HEADER and LINE entities, generate the
765 -- entity def util packages after the attribute packages are generated
766 -- Generating BODY ONLY as specs are in the source area - OEXRHDRS.pls
767 -- , OEXRLINS.pls
768 ------------------------------------------------------------------------------
769 IF p_application_id = 660
770 AND l_entity_code IN ('HEADER','LINE') THEN
771
772 select entity_id into l_entity_id
773 from oe_ak_objects_ext
774 where DATABASE_OBJECT_NAME =p_database_object_name;
775
776 PUT_LINE(
777 '--------------------------------');
778 PUT_LINE('entity id :'||l_entity_id);
779 if g_PkgBdy_tbl.exists(l_entity_id) then
780 PUT_LINE('cache exists');
781 PUT_LINE('package spec :'||g_PkgBdy_tbl(l_entity_id).package_spec);
782 PUT_LINE('package body :'||g_PkgBdy_tbl(l_entity_id).package_body);
783 else
784 PUT_LINE('cache does not exist');
785 end if;
786
787 if ( not g_PkgBdy_tbl.exists(l_entity_id) or nvl(g_PkgBdy_tbl(l_entity_id).package_body,'X') <> 'B') then
788 PUT_LINE(
789 'Create caching/util package for current entity '||l_entity_code);
790 Create_Entity_Def_Util_Handler
791 ( p_application_id =>p_application_id
792 , p_database_object_name => p_database_object_name
793 , p_entity_code => l_entity_code
794 , p_application_short_name => l_app_short_name
795 , p_generation_level => 'BODY_ONLY'
796 );
797 g_PkgBdy_tbl(l_entity_id).entity_id := l_entity_id;
798 g_PkgBdy_tbl(l_entity_id).package_body := 'B';
799 end if;
800
801 END IF;
802
803 -------------------------------------------------------------------------------
804 -- (5) WRITING OUT THE BODY OF THE handler PACKAGE e.g ONT_LINE_DEF_HDLR
805 -------------------------------------------------------------------------------
806
807 PUT_LINE(
808 '------------------------------------------------------------------------------');
809 PUT_LINE(
810 'Create defaulting handler package for entity '||l_entity_code);
811
812 Pkg_Header(p_pkg_name => l_pkg_name,
813 p_pkg_type => 'BODY');
814
815 Text ('g_entity_code varchar2(15) := '''||
816 l_entity_code||''';',1);
817
818 Text ('g_database_object_name varchar2(30) :='''||p_database_object_name||''';',1);
819 New_Line;
820
821 -- Procedure to default record.
822 Comment ('Default_Record',0);
823 Text ('PROCEDURE Default_Record',0);
824 Text ('( p_x_rec IN OUT NOCOPY '||
825 p_database_object_name||'%ROWTYPE');
826 IF l_entity_code IN ('HEADER', 'LINE') THEN
827 Parameter('initial_rec ','IN',
828 p_database_object_name||'%ROWTYPE ',0);
829 END IF;
830 Parameter('in_old_rec ','IN',
831 p_database_object_name||'%ROWTYPE ',0);
832 Parameter('iteration','IN','NUMBER default 1',0);
833 Text (')',0);
834 Text ('IS',0);
835 Text ('l_action NUMBER;',0);
836 Text ('l_attr VARCHAR2(200);',0); --added for bug 4002431
837 Text ('BEGIN',0);
838 New_Line;
839 Text ('oe_debug_pub.ADD(''Enter '||l_app_short_name||'_'||l_entity_code||
840 '_Def_Hdlr.Default_Record'');',0);
841 New_Line;
842 Text('IF p_iteration =1 THEN',0);
843 Text('OE_'||l_entity_code||'_Security.G_Is_Caller_Defaulting := ''Y'';',0);
844 Assign ('g_record','p_x_rec',1,-1);
845 Text('END IF;',0);
846 New_Line;
847
848 Comment ('if max. iteration is reached exit',0);
849 Text ('IF p_iteration > ONT_DEF_UTIL.G_MAX_DEF_ITERATIONS THEN',0);
850 Text ('FND_MESSAGE.SET_NAME('||''''||'ONT'||''''||','||''''||'OE_DEF_MAX_ITERATIONS'||''''||');',2);
851 Text ('OE_MSG_PUB.ADD;',2);
852 Text ('RAISE FND_API.G_EXC_ERROR;',2);
853 Text ('END IF;',0);
854 New_Line;
855
856 -- Default missing attributes.
857 Comment ('Default missing attributes',0);
858
859 FOR J IN 0..l_obj_attr_tbl.COUNT -1 LOOP
860
861 l_column_name := l_obj_attr_tbl(j).column_name ;
862 l_attribute_code := l_obj_attr_tbl(j).attribute_code ;
863 l_data_type := l_obj_attr_tbl(j).data_type ;
864 l_defaulting_api_pkg := l_obj_attr_tbl(j).defaulting_api_pkg ;
865 l_defaulting_api_proc := l_obj_attr_tbl(j).defaulting_api_proc ;
866 l_validation_api_pkg := l_obj_attr_tbl(j).validation_api_pkg ;
867 l_validation_api_proc := l_obj_attr_tbl(j).validation_api_proc ;
868 l_dependent_api_pkg := l_obj_attr_tbl(j).dependent_api_pkg ;
869 l_dependent_api_proc := l_obj_attr_tbl(j).dependent_api_proc ;
870 l_security_api_pkg := l_obj_attr_tbl(j).security_api_pkg ;
871 l_security_api_proc := l_obj_attr_tbl(j).security_api_proc ;
872 l_defaulting_condn_ref_flag := l_obj_attr_tbl(j).defaulting_condn_ref_flag ;
873 l_defaulting_enabled_flag := l_obj_attr_tbl(j).defaulting_enabled_flag ;
874 Text ('l_attr:= '''||l_attribute_code||''';',0); --bug 4002431
875 if substr(l_data_type,1,4) = 'DATE' then
876 l_buffer := 'FND_API.G_MISS_DATE';
877 elsif substr(l_data_type,1,3) = 'NUM'
878 or substr(l_data_type,1,3) = 'INT' then
879 l_buffer := 'FND_API.G_MISS_NUM';
880 else
881 l_buffer := 'FND_API.G_MISS_CHAR';
882 end if;
883
884 -- ATTRIBUTE IS NOT DEFAULTABLE, RETURN NULL IF ATTRIBUTE IS MISSING
885 IF upper(l_defaulting_enabled_flag) <> 'Y' THEN
886
887 New_Line;
888 Text(
889 'IF g_record.'||l_column_name||' = '||l_buffer||' THEN',0);
890 Text('-- Attribute is NOT defaulting enabled, return NULL if MISSING',1);
891 Text('g_record.'||l_column_name||' := NULL;',1);
892 Text('END IF;',0);
893
894
895 -- ATTRIBUTE IS DEFAULTABLE, GENERATE OR USE AK DEFAULT ATTRIBUTE handler
896 ELSIF upper(l_defaulting_enabled_flag) = 'Y' THEN
897
898 l_defaulting_api := l_defaulting_api_pkg||'.'||l_defaulting_api_proc;
899
900 -- If there is no validation api registered in the AK make it NONE
901
902 IF (l_validation_api_pkg is null) OR (l_validation_api_proc is null) THEN
903 l_validation_api := null;
904 else
905 l_validation_api := l_validation_api_pkg||'.'||l_validation_api_proc;
906 end if;
907
908 -- If there is no dependent api registered in the AK make it NONE
909
910 IF (l_dependent_api_pkg is null ) OR (l_dependent_api_proc is null ) THEN
911 l_depend_api := null;
912 else
913 l_depend_api := l_dependent_api_pkg||'.'||l_dependent_api_proc;
914 end if;
915
916 -- If there is no security api registered in the AK make it NONE
917
918 IF (l_security_api_pkg is null ) OR (l_security_api_proc is null ) THEN
919 l_security_api := null;
920 else
921 l_security_api := l_security_api_pkg||'.'||l_security_api_proc;
922 end if;
923
924 -- Generate procedure code.
925 New_Line;
926
927 Text(
928 'IF g_record.'||l_column_name||' = '||l_buffer||' THEN',0);
929 Comment ('Get the defaulting api registered '||
930 'in the AK AND default',0);
931
932 Text ('l_attr:=l_attr||'' 1'';',2);
933 Assign(
934 'g_record.'||l_column_name,l_defaulting_api||'(g_record)',2,-1);
935 Text ('l_attr:=l_attr||'' 2'';',2);
936 -----------------------------------------------------------------------
937 -- BEGIN Fix for bug 1343621
938 -- if the new defaulted value is null, then retain the old value
939 -- Initially, this will be implemented only for the following fields:
940 -- Order Type, Salesperson, Price List, Customer PO
941 --
942 -- Fix Bug 1757278:
943 -- Customer PO number column name corrected - it should be
944 -- CUST_PO_NUMBER, not CUSTOMER_PO_NUMBER
945 --
946 -----------------------------------------------------------------------
947
948 IF l_column_name IN ('ORDER_TYPE_ID','SALESREP_ID'
949 ,'PRICE_LIST_ID','CUST_PO_NUMBER'
950 ,'SOLD_TO_ORG_ID'
951 -- QUOTING change
952 ,'TRANSACTION_PHASE_CODE'
953 )
954 THEN
955
956 Text(
957 ' IF g_record.'||l_column_name||' IS NULL ',0);
958 Text(
959 ' AND p_in_old_rec.'||l_column_name||' <> '||l_buffer||' THEN ',0);
960 Text(
961 ' g_record.'||l_column_name||' := p_in_old_rec.'||l_column_name||';',0);
962 Text(
963 ' END IF;',0);
964
965 END IF;
966
967 -----------------------------------------------------------------------
968 -- END Fix for bug 1343621
969 -----------------------------------------------------------------------
970
971 -- If there is a security pkg registered for this attribute, then
972 -- call security if the value on old record is different FROM the new
973 -- defaulted value.
974 if (l_security_api is not null) then
975 Text (
976 '-- For UPDATE operations, check security if new defaulted value is not equal to old value',1);
977 Text ('IF g_record.operation = OE_GLOBALS.G_OPR_UPDATE THEN',1);
978 Text ('l_attr:=l_attr||'' 3'';',2); --bug 4002431
979 Text ('IF NOT OE_GLOBALS.Equal(g_record.'||
980 lower(l_column_name)||', p_in_old_rec.'||
981 lower(l_column_name)||') THEN',2);
982 Text ('IF '||l_security_api||
983 '(p_record => g_record, x_on_operation_action => l_action) = OE_PC_GLOBALS.YES THEN',3);
984 Text (
985 '-- Raise error if security returns YES, operation IS CONSTRAINED',3);
986 Text(' RAISE FND_API.G_EXC_ERROR;',3);
987 Text('END IF;',3);
988 Text('OE_GLOBALS.G_ATTR_UPDATED_BY_DEF := ''Y'';',3);
989 Text('END IF;',2);
990 Text('END IF;',1);
991 else
992 Text(
993 '-- There is no security api registered in the AK dictionary ',0);
994 end if;
995
996 -- if validation api is registered in AK, then validate value if not null
997 if (l_validation_api is not null)
998 -- QUOTING - bug identified during quote testing that clear dependents
999 -- does not fire if validation api is not registered.
1000 or (l_depend_api is not null)
1001 then
1002
1003 Text(
1004 'IF g_record.'||l_column_name||' IS NOT NULL THEN',1);
1005 Text ('l_attr:=l_attr||'' 4'';',2);
1006 -- bug identified during quote testing
1007 if (l_validation_api is not null) then
1008 Text (
1009 '-- Validate defaulted value if not null',2);
1010 Text('IF '||l_validation_api
1011 ||'(g_record.'||l_column_name||') THEN ',2);
1012 end if;
1013 if (l_depend_api is not null) then
1014 Text (
1015 '-- if valid, clear dependent attributes',3);
1016 IF l_entity_code IN ('HEADER', 'LINE') THEN
1017 --Text(l_depend_api||'(g_record);',3);
1018 Text(l_depend_api||'(p_initial_rec, p_in_old_rec, g_record);',3);
1019 else
1020 Text(l_depend_api||'(g_record);',3);
1021 end if;
1022 else
1023 Text(
1024 '-- There is no dependent api registered in the AK dictionary ',3);
1025 Text('NULL;',3);
1026 Text ('l_attr:=l_attr||'' 5'';',3); --bug 4002431
1027 end if;
1028 -- bug identified during quote testing
1029 if (l_validation_api is not null) then
1030 Text('ELSE',2);
1031 Text('g_record.'||l_column_name||' := NULL;',3);
1032 Text ('l_attr:=l_attr||'' 6'';',3); --bug 4002431
1033 Text('END IF;',2);
1034 end if;
1035 Text('END IF;',1);
1036 else
1037 Text(
1038 '-- There is no validation api registered in the AK dictionary ',3);
1039 end if;
1040
1041 Text('END IF;',0);
1042
1043 END IF; -- END OF CHECK TO SEE IF ATTRIBUTE IS DEFAULTING ENABLED
1044
1045 END LOOP;
1046
1047
1048 -- Now loop through all the attributes to check if there are any missing values
1049 -- for the attributes. If there are any missing values that could be defaulted,
1050 -- call the Defaulting procedure repeatedly till the values are defaulted or
1051 -- till the maximum iteration is reached.
1052
1053 New_Line;
1054 Comment ('CHeck if there are any missing values for attrs',2);
1055 Comment ('If there are any missing call Default_Record again '||
1056 'AND repeat till all the values ',2);
1057 Comment ('are defaulted or till the max. iterations are reached',2);
1058
1059 New_Line;
1060 Text(' IF( ' ,2);
1061
1062 FOR J IN 0..l_obj_attr_tbl.COUNT -1 LOOP
1063
1064 l_data_type := l_obj_attr_tbl(j).data_type ;
1065 l_attribute_code := l_obj_attr_tbl(j).attribute_code ;
1066 l_column_name := l_obj_attr_tbl(j).column_name ;
1067
1068 if substr(l_data_type,1,4) = 'DATE' then
1069 l_buffer := 'FND_API.G_MISS_DATE';
1070 elsif substr(l_data_type,1,3) = 'NUM'
1071 or substr(l_data_type,1,3) = 'INT' then
1072 l_buffer := 'FND_API.G_MISS_NUM';
1073 else
1074 l_buffer := 'FND_API.G_MISS_CHAR';
1075 end if;
1076
1077 if j =0 then
1078 Text(' (g_record.'||l_column_name||' =' ||l_buffer||') ',2);
1079 else
1080 Text(' OR (g_record.'||l_column_name||' = '||l_buffer||') ',2);
1081 end if;
1082
1083 END LOOP;
1084
1085 Text(') THEN ' ,2);
1086 Text(l_pkg_name||'.Default_Record(',2);
1087 Text(' p_x_rec => g_record,',2);
1088 if l_entity_code in ('HEADER','LINE') then
1089 Text(' p_initial_rec => p_initial_rec,',2);
1090 end if;
1091 Text(' p_in_old_rec => p_in_old_rec,',2);
1092 Text(' p_iteration => p_iteration+1 );',2);
1093 Text('END IF;',2);
1094 New_Line;
1095
1096 Text('IF p_iteration =1 THEN',0);
1097 Text('OE_'||l_entity_code||'_Security.'||
1098 'G_Is_Caller_Defaulting := ''N'';',0);
1099 Assign ('p_x_rec','g_record',1,-1);
1100 Text('END IF;',0);
1101 New_Line;
1102
1103 Text ('oe_debug_pub.ADD(''Exit '||l_app_short_name||'_'||l_entity_code||
1104 '_Def_Hdlr.Default_Record'');',0);
1105 New_Line;
1106
1107 Text('EXCEPTION',0);
1108 New_Line;
1109 Text('WHEN FND_API.G_EXC_ERROR THEN',1);
1110 Text('OE_'||l_entity_code||'_Security.'||
1111 'G_Is_Caller_Defaulting := ''N'';',2);
1112 Text('RAISE FND_API.G_EXC_ERROR;',2);
1113 Text('WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN',1);
1114 Text('OE_'||l_entity_code||'_Security.'||
1115 'G_Is_Caller_Defaulting := ''N'';',2);
1116 Text('RAISE FND_API.G_EXC_UNEXPECTED_ERROR;',2);
1117 Text('WHEN OTHERS THEN',1);
1118 Text('IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)',2);
1119 Text('THEN',2);
1120 Text('OE_MSG_PUB.Add_Exc_Msg',3);
1121 Text('( G_PKG_NAME',3);
1122 --Text(',''Default_Record''',3);
1123 Text(',''Default_Record: ''||l_attr',3); --bug 4002431
1124 Text(');',3);
1125 Text('END IF;',2);
1126 Text('OE_'||l_entity_code||'_Security.G_Is_Caller_Defaulting := ''N'';',2);
1127 Text('RAISE FND_API.G_EXC_UNEXPECTED_ERROR;',2);
1128 New_Line;
1129 Text('END Default_Record;',0);
1130 New_Line;
1131
1132 Pkg_End (l_pkg_name,'BODY');
1133
1134 PUT_LINE(
1135 '------------------------------------------------------------------------------');
1136
1137 retcode := 0;
1138
1139 EXCEPTION
1140
1141 WHEN FND_API.G_EXC_ERROR THEN
1142 retcode := 2;
1143 errbuf := 'Please check the log file for error messages';
1144
1145 WHEN OTHERS THEN
1146 PUT_LINE( 'Error in creating entity def hdlr '||sqlerrm);
1147 retcode := 2;
1148 errbuf := sqlerrm;
1149
1150 END Create_Entity_Def_handler;
1151 -------------------------------------------------------------------------------
1152
1153
1154 PROCEDURE Return_String(p_data_type IN VARCHAR2)
1155 IS
1156 --
1157 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1158 --
1159 BEGIN
1160 Text( 'if l_return_value is not null then ',2);
1161 if p_data_type='NUMBER' then
1162 Text( ' RETURN to_number(l_return_value);',3);
1163 elsif p_data_type='DATE' then
1164 Text( ' RETURN to_date(l_return_value,''RRRR/MM/DD HH24:MI:SS'');',3);
1165 else
1166 Text( ' RETURN (l_return_value);',3);
1167 end if;
1168 Text( 'end if; ',2);
1169 New_Line ;
1170 END;
1171
1172 -- Procedure to create the object attribute handler
1173 -- This procedure generates the attribute defaulting handler for the Rule based
1174 -- Defaulting Framework.
1175 -- This procedure will be called separately to generate a particular attribute
1176 -- handler or it cwn be called by the entity handler to generate all the
1177 -- attribute handlers for a particular entity.
1178
1179 PROCEDURE Create_Obj_Attr_Def_handler
1180 ( p_application_id IN VARCHAR2,
1181 p_database_object_name IN VARCHAR2,
1182 p_attribute_code IN VARCHAR2,
1183 p_entity_code IN VARCHAR2,
1184 p_generation_level IN VARCHAR2 DEFAULT 'FULL',
1185 x_defaulting_api_pkg OUT NOCOPY VARCHAR2
1186
1187 )
1188 IS
1189
1190 l_defaulting_api VARCHAR2(61);
1191 l_package VARCHAR2(30);
1192 l_function VARCHAR2(30);
1193 l_app_short_name VARCHAR2(3);
1194 l_attr_code VARCHAR2(30);
1195 l_data_type VARCHAR2(30);
1196 l_column_name VARCHAR2(30);
1197 l_seq_no NUMBER(4);
1198 l_attr_id VARCHAR2(200);
1199 l_sql_string VARCHAR2(1000);
1200 l_value NUMBER;
1201
1202 -- CURSOR to get the name of the defaulting api pkgs.
1203 CURSOR OA(p_database_object_name varchar2,p_application_id number,
1204 p_attribute_code varchar2)
1205 is
1206 SELECT DEFAULTING_API_PKG,
1207 DEFAULTING_API_PROC,NVL(DATA_TYPE,'CHAR'),
1208 COLUMN_NAME
1209 FROM OE_DEF_AK_ATTR_EXT_V
1210 WHERE DATABASE_OBJECT_NAME = p_database_object_name
1211 AND attribute_application_id = p_application_id
1212 AND attribute_code = p_attribute_code;
1213 CURSOR APP is
1214 SELECT substr(rtrim(APPLICATION_SHORT_NAME),1,3)
1215 FROM fnd_application
1216 WHERE application_id = p_application_id;
1217 CURSOR ENT_SEQ (p_entity_code varchar2)
1218 IS
1219 SELECT entity_id FROM OE_DEF_AK_OBJ_EXT_V
1220 WHERE entity_code = p_entity_code;
1221
1222 l_condition_id NUMBER;
1223 l_attr_def_condn_id NUMBER;
1224 l_elem_attribute_code VARCHAR2(30);
1225 l_value_op VARCHAR2(15);
1226 l_value_string VARCHAR2(255);
1227 l_src_type VARCHAR2(30);
1228 l_src_api_pkg VARCHAR2(30);
1229 l_src_api_fn VARCHAR2(2000);
1230 l_src_profile_option VARCHAR2(30);
1231 l_src_constant_value VARCHAR2(240);
1232 l_src_system_variable_expr VARCHAR2(240);
1233 l_group_number NUMBER;
1234 l_old_group_number NUMBER;
1235 l_src_database_object_name VARCHAR2(240);
1236 l_src_attribute_code VARCHAR2(240);
1237 l_src_column_name VARCHAR2(30);
1238 l_related_entity_code VARCHAR2(30);
1239 l_uk_attribute VARCHAR2(30);
1240 l_fk_attribute VARCHAR2(30);
1241 l_pk_attribute VARCHAR2(30); -- 2218044
1242 l_src_data_type VARCHAR2(30); -- 3081991
1243 l_count NUMBER;
1244 l_rule_id NUMBER;
1245
1246 CURSOR C_CONDNS IS
1247 SELECT condition_id, attr_def_condition_id
1248 FROM OE_DEF_ATTR_CONDNS
1249 WHERE DATABASE_OBJECT_NAME = p_database_object_name
1250 AND ATTRIBUTE_CODE = p_attribute_code
1251 AND ENABLED_FLAG = 'Y'
1252 ORDER BY PRECEDENCE;
1253
1254 CURSOR C_CONDN_ELEMS IS
1255 SELECT group_number, attribute_code, value_op, value_string
1256 FROM OE_DEF_CONDN_ELEMS
1257 WHERE CONDITION_ID = l_condition_id
1258 ORDER BY GROUP_NUMBER;
1259
1260 CURSOR C_DEF_RULES IS
1261 SELECT attr_def_rule_id
1262 ,src_type
1263 ,src_api_pkg
1264 ,src_api_fn
1265 ,src_profile_option
1266 ,src_constant_value
1267 ,src_system_variable_expr
1268 ,src_database_object_name
1269 ,src_attribute_code
1270 FROM OE_DEF_ATTR_DEF_RULES
1271 WHERE ATTR_DEF_CONDITION_ID = l_attr_def_condn_id
1272 ORDER BY SEQUENCE_NO;
1273
1274 CURSOR C_UK_COLS IS
1275 SELECT ua.column_name, fa.column_name
1276 FROM AK_UNIQUE_KEYS uk, AK_UNIQUE_KEY_COLUMNS uc
1277 , AK_FOREIGN_KEYS fk, AK_FOREIGN_KEY_COLUMNS fc
1278 , AK_OBJECT_ATTRIBUTES ua, AK_OBJECT_ATTRIBUTES fa
1279 WHERE uk.database_object_name = l_src_database_object_name
1280 AND uk.unique_key_name = uc.unique_key_name
1281 AND fk.database_object_name = p_database_object_name
1282 AND fk.unique_key_name = uk.unique_key_name
1283 AND fc.foreign_key_name = fk.foreign_key_name
1284 AND uc.unique_key_sequence = fc.foreign_key_sequence
1285 AND ua.database_object_name = l_src_database_object_name
1286 AND ua.attribute_code = uc.attribute_code
1287 AND fa.database_object_name = p_database_object_name
1288 AND fa.attribute_code = fc.attribute_code
1289 ORDER BY uc.unique_key_sequence;
1290
1291 CURSOR C_PK_COLS IS -- 2218044
1292 SELECT ua.column_name
1293 FROM AK_UNIQUE_KEYS uk,
1294 AK_UNIQUE_KEY_COLUMNS uc,
1295 AK_OBJECT_ATTRIBUTES ua
1296 WHERE uk.database_object_name = p_database_object_name
1297 AND ua.database_object_name = p_database_object_name
1298 AND uk.unique_key_name = uc.unique_key_name
1299 AND uc.attribute_code = ua.attribute_code
1300 AND uc.attribute_application_id = ua.attribute_application_id
1301 AND ua.database_object_name = p_database_object_name
1302 ORDER BY uc.unique_key_sequence;
1303
1304 --
1305 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1306 --
1307 BEGIN
1308
1309 Init_Applsys_Schema;
1310
1311 open APP;
1312 fetch APP into l_app_short_name;
1313 close APP;
1314
1315 open ENT_SEQ (p_entity_code);
1316 fetch ENT_SEQ into l_seq_no;
1317 close ENT_SEQ;
1318
1319 open OA(p_database_object_name ,p_application_id , p_attribute_code );
1320 fetch OA into l_package,l_function,l_data_type,l_column_name;
1321
1322 if OA%notfound
1323 or l_package is null then
1324 -- l_attr_code may not exceed 20 characters.
1325 l_attr_code := substr(p_attribute_code,1,20);
1326 if substr(l_attr_code,length(l_attr_code)) = '_' then
1327 l_attr_code := substr(l_attr_code,1,length(l_attr_code) -1);
1328 end if;
1329 l_package := l_app_short_name||'_D'||l_seq_no||'_'||l_attr_code;
1330 l_function :='Get_Default_Value';
1331 end if;
1332
1333 close OA;
1334
1335 l_defaulting_api := l_package||'.'||l_function;
1336 x_defaulting_api_pkg := l_package;
1337
1338
1339 ----------------------------------------------------------------------------
1340 -- WRITING OUT THE SPEC
1341 ----------------------------------------------------------------------------
1342
1343 if p_generation_level = 'BODY_ONLY' then
1344 goto START_OF_BODY;
1345 end if;
1346
1347 Pkg_Header( p_pkg_name => l_package,
1348 p_pkg_type => 'SPEC');
1349
1350 Text('FUNCTION Get_Default_Value(p_'||LOWER(p_entity_code)||'_rec IN '
1351 || p_database_object_name||' %ROWTYPE ',1);
1352 Text(') RETURN '|| l_data_type||';',1);
1353 New_Line ;
1354
1355 Pkg_End (l_package,'SPEC');
1356
1357
1358 <<START_OF_BODY>>
1359 ---------------------------------------------------------------------------
1360 -- WRITING OUT THE BODY
1361 ---------------------------------------------------------------------------
1362
1363 if p_generation_level = 'SPEC_ONLY' then
1364 RETURN;
1365 end if;
1366
1367 Pkg_Header( p_pkg_name => l_package,
1368 p_pkg_type => 'BODY');
1369
1370 -- Function Get_Default_Value
1371 New_Line ;
1372 Text('FUNCTION Get_Default_Value(p_'||LOWER(p_entity_code)||'_rec IN '
1373 || p_database_object_name||'%ROWTYPE ',0);
1374 Text(') RETURN '|| l_data_type||' IS ', 1);
1375 IF l_data_type = 'DATE' THEN
1376 Text('l_return_value DATE;',1);
1377 ELSE
1378 Text('l_return_value VARCHAR2(2000);',1);
1379 END IF;
1380 Text('l_rule_id NUMBER;',1);
1381 Text('BEGIN',0);
1382 New_Line;
1383
1384 -- Added 09-DEC-2002
1385 -- Supress defaulting of blanket number for items that are not of type
1386 -- 'STANDARD' or 'KIT'
1387 IF p_attribute_code = 'BLANKET_NUMBER'
1388 AND p_database_object_name = 'OE_AK_ORDER_LINES_V'
1389 THEN
1390 Text('IF p_line_rec.item_type_code NOT IN (''STANDARD'',''KIT'') THEN',2);
1391 Text(' RETURN NULL;',2);
1392 Text('END IF;',2);
1393 END IF;
1394
1395
1396 -- BEGIN LOOP TO CONSTRUCT PL/SQL LOGIC FOR DEFAULTING CONDITIONS
1397
1398 OPEN C_CONDNS;
1399 LOOP
1400 FETCH C_CONDNS INTO l_condition_id, l_attr_def_condn_id;
1401 EXIT WHEN (C_CONDNS%NOTFOUND);
1402
1403 IF l_condition_id = 0 THEN
1404 GOTO ADD_RULES;
1405 END IF;
1406
1407 l_old_group_number := -1;
1408 OPEN C_CONDN_ELEMS;
1409 LOOP
1410 FETCH C_CONDN_ELEMS INTO l_group_number, l_elem_attribute_code
1411 , l_value_op, l_value_string;
1412 EXIT WHEN (C_CONDN_ELEMS%NOTFOUND);
1413
1414 /* =========== Code appended for the bug 3081991 =========== */
1415
1416 select ada.data_type
1417 into l_src_data_type
1418 from OE_DEF_AK_ATTR_EXT_V ada
1419 where ada.DATABASE_OBJECT_NAME = p_database_object_name
1420 and ada.ATTRIBUTE_APPLICATION_ID = p_application_id
1421 and ada.ATTRIBUTE_CODE = l_elem_attribute_code;
1422
1423 IF l_src_data_type = 'DATE' THEN
1424 l_value_string := ('TO_DATE('''||l_value_string||''', '||'''RRRR/MM/DD HH24:MI:SS'''||')');
1425 END IF;
1426
1427 IF l_old_group_number <> l_group_number THEN
1428 -- Construct an OR condition if group number changes
1429 IF l_old_group_number = -1 THEN
1430 IF l_src_data_type = 'DATE' THEN
1431 Text('IF (p_'||LOWER(p_entity_code)||'_rec.'||l_elem_attribute_code||' '||l_value_op||' '||l_value_string||'',2);
1432 ELSE
1433 Text('IF (p_'||LOWER(p_entity_code)||'_rec.'||l_elem_attribute_code||' '||l_value_op||' '''||l_value_string||'''',2);
1434 END IF;
1435 ELSE
1436 IF l_src_data_type = 'DATE' THEN
1437 Text(' )',2);
1438 Text('OR (p_'||LOWER(p_entity_code)||'_rec.'||l_elem_attribute_code||' '||l_value_op||' '||l_value_string||'',2);
1439 ELSE
1440 Text(' )',2);
1441 Text('OR (p_'||LOWER(p_entity_code)||'_rec.'||l_elem_attribute_code||' '||l_value_op||' '''||l_value_string||'''',2);
1442 END IF;
1443 END IF;
1444 ELSE
1445 -- Construct an AND condition if group number is same
1446 IF l_src_data_type = 'DATE' THEN
1447 Text(' AND p_'||LOWER(p_entity_code)||'_rec.'||l_elem_attribute_code||' '||l_value_op||' '||l_value_string||'',2);
1448 ELSE
1449 Text(' AND p_'||LOWER(p_entity_code)||'_rec.'||l_elem_attribute_code||' '||l_value_op||' '''||l_value_string||'''',2);
1450 END IF;
1451 END IF;
1452
1453 /* =========== Appended till here =========== */
1454
1455 l_old_group_number := l_group_number;
1456
1457 END LOOP;
1458 CLOSE C_CONDN_ELEMS;
1459 Text(' ) THEN',2);
1460
1461 -- BEGIN LOOP TO CONSTRUCT PL/SQL LOGIC FOR RULES
1462
1463 <<ADD_RULES>>
1464 OPEN C_DEF_RULES;
1465 LOOP
1466 FETCH C_DEF_RULES INTO l_rule_id
1467 , l_src_type
1468 , l_src_api_pkg
1469 , l_src_api_fn
1470 , l_src_profile_option
1471 , l_src_constant_value
1472 , l_src_system_variable_expr
1473 , l_src_database_object_name
1474 , l_src_attribute_code;
1475 EXIT WHEN (C_DEF_RULES%NOTFOUND);
1476
1477 Text('l_rule_id := '||l_rule_id||';',2);
1478 IF l_src_type = 'RELATED_RECORD' THEN
1479
1480 SELECT upper(entity_code)
1481 INTO l_related_entity_code
1482 FROM OE_AK_OBJECTS_EXT
1483 WHERE DATABASE_OBJECT_NAME = l_src_database_object_name;
1484
1485 SELECT column_name
1486 INTO l_src_column_name
1487 FROM AK_OBJECT_ATTRIBUTES
1488 WHERE DATABASE_OBJECT_NAME = l_src_database_object_name
1489 AND ATTRIBUTE_CODE = l_src_attribute_code;
1490
1491 Text('IF '||l_app_short_name||'_'||l_related_entity_code
1492 ||'_Def_Util.Sync_'||l_related_entity_code||'_Cache',2);
1493
1494 l_count := 1;
1495 OPEN C_UK_COLS;
1496 LOOP
1497 FETCH C_UK_COLS INTO l_uk_attribute, l_fk_attribute;
1498 EXIT WHEN (C_UK_COLS%NOTFOUND);
1499 IF l_count = 1 THEN
1500 Text('(p_'||l_uk_attribute||' => p_'||LOWER(p_entity_code)||'_rec.'
1501 ||l_fk_attribute,2);
1502 ELSE
1503 -- ER: 1840556
1504 IF l_src_database_object_name = 'OE_AK_INVENTORY_ITEMS_V'
1505 AND l_fk_attribute = 'SHIP_FROM_ORG_ID'
1506 THEN
1507 -- Bug 2422910: Item Validation Org System Parameter should be
1508 -- be passed to the item caching API if ship from is null
1509 -- OR missing on the line.
1510 Text(',p_'||l_uk_attribute||' => REPLACE(nvl(p_'||LOWER(p_entity_code)||'_rec.'
1511 ||l_fk_attribute||',FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,OE_SYS_Parameters.Value(''MASTER_ORGANIZATION_ID''))',2);
1512 ELSE
1513 Text(',p_'||l_uk_attribute||' => p_'||LOWER(p_entity_code)||'_rec.'
1514 ||l_fk_attribute,2);
1515 END IF;
1516 END IF;
1517 l_count := 2;
1518 END LOOP;
1519 Text(') = 1 THEN',2);
1520 CLOSE C_UK_COLS;
1521
1522 Text('l_return_value := '||l_app_short_name||'_'||l_related_entity_code
1523 ||'_Def_Util.g_cached_record.'||l_src_column_name||';',2);
1524
1525 Text('END IF;',2);
1526
1527 ELSIF l_src_type = 'SAME_RECORD' THEN
1528
1529 SELECT column_name
1530 INTO l_src_column_name
1531 FROM AK_OBJECT_ATTRIBUTES
1532 WHERE DATABASE_OBJECT_NAME = p_database_object_name
1533 AND ATTRIBUTE_CODE = l_src_attribute_code;
1534
1535 Text('l_return_value := p_'||LOWER(p_entity_code)||'_rec.'||l_src_column_name||';',2);
1536
1537 IF l_src_attribute_code = p_attribute_code THEN -- 2218044
1538 Text('IF '||l_app_short_name||'_'||p_entity_code
1539 ||'_Def_Util.Sync_'||p_entity_code||'_Cache',2);
1540
1541 l_count := 1;
1542 OPEN C_PK_COLS;
1543 LOOP
1544 FETCH C_PK_COLS INTO l_pk_attribute;
1545 EXIT WHEN (C_PK_COLS%NOTFOUND);
1546 IF l_count = 1 THEN
1547 Text('(p_'||l_pk_attribute||' => p_'||LOWER(p_entity_code)||'_rec.'
1548 ||l_pk_attribute,2);
1549 ELSE
1550 Text(',p_'||l_pk_attribute||' => p_'||LOWER(p_entity_code)||'_rec.'
1551 ||l_pk_attribute,2);
1552 END IF;
1553 l_count := 2;
1554 END LOOP;
1555 Text(') = 1 THEN',2);
1556 CLOSE C_PK_COLS;
1557
1558 Text('l_return_value := '||l_app_short_name||'_'||p_entity_code
1559 ||'_Def_Util.g_cached_record.'||l_src_column_name||';',3);
1560 Text('ELSE', 2);
1561 Text('l_return_value := NULL;', 3);
1562 Text('END IF;',2);
1563 END IF; -- 2218044
1564
1565 ELSIF l_src_type = 'CONSTANT' OR l_src_type = p_attribute_code THEN
1566 IF l_data_type = 'DATE' THEN -- 2358338
1567 Text('l_return_value := TO_DATE('''||REPLACE(l_src_constant_value, '''', '''''')||''', ''RRRR/MM/DD HH24:MI:SS'');',2); -- 2222482
1568 ELSE
1569 -- 5529963 : 4 = space length => 2*level 2
1570 IF ( (4 + length('l_return_value := '''||REPLACE(l_src_constant_value, '''', '''''')||''';') ) <=
1571 g_max_line_size ) THEN
1572 Text('l_return_value := '''||REPLACE(l_src_constant_value, '''', '''''')||''';',2); -- 2222482
1573 ELSE
1574 Text('l_return_value := ', 2);
1575 Text(''''||REPLACE(l_src_constant_value, '''', '''''')||''';', 2);
1576 END IF;
1577 END IF; -- 2358338
1578 ELSIF l_src_type = 'SYSTEM' THEN
1579
1580 IF l_data_type = 'DATE' THEN
1581 Text( 'l_return_value := '||l_app_short_name||
1582 '_Def_Util.Get_Expression_Value_Date',2);
1583 Text( ' (p_expression_string => '''||REPLACE(l_src_system_variable_expr, '''', '''''')||''');',3); -- 2222482
1584 ELSE
1585 Text( 'l_return_value := '||l_app_short_name||
1586 '_Def_Util.Get_Expression_Value_Varchar2',2);
1587 Text( ' (p_expression_string => '''||REPLACE(l_src_system_variable_expr, '''', '''''')||''');',3); -- 2222482
1588 END IF;
1589
1590 ELSIF l_src_type = 'PROFILE_OPTION' THEN
1591
1592 -- Fix bug 1756855: Convert from canonical to number format for
1593 -- defaults of number fields from profile options
1594 IF l_data_type = 'NUMBER' THEN
1595 Text( 'l_return_value := fnd_number.canonical_to_number',2);
1596 Text( '(FND_PROFILE.VALUE('''||l_src_profile_option||''')); ',3);
1597 ELSE
1598 Text('l_return_value := FND_PROFILE.VALUE('''||
1599 l_src_profile_option||''');',2);
1600 END IF;
1601
1602 ELSIF l_src_type = 'API' THEN
1603
1604 IF l_src_database_object_name IS NULL THEN
1605 l_src_database_object_name := 'null';
1606 ELSE
1607 l_src_database_object_name := ''''||l_src_database_object_name||'''';
1608 END IF;
1609
1610 IF l_src_attribute_code IS NULL THEN
1611 l_src_attribute_code := 'null';
1612 ELSE
1613 l_src_attribute_code := ''''||l_src_attribute_code||'''';
1614 END IF;
1615
1616 Text('l_return_value := '||l_src_api_pkg||'.'||l_src_api_fn,2);
1617 Text(' (p_database_object_name => '||l_src_database_object_name,2);
1618 Text(' ,p_attribute_code => '||l_src_attribute_code||');',2);
1619
1620 ELSIF l_src_type = 'WAD_ATTR' THEN
1621
1622 Text( 'l_return_value := '||l_app_short_name||'_Def_Util.Get_Attr_Default_Varchar2',
1623 2);
1624 Text( '(p_attribute_code => g_attribute_code,',3);
1625 Text( ' p_application_id => g_application_id);',3);
1626
1627 ELSIF l_src_type = 'WAD_OBJATTR' THEN
1628
1629 Text( 'l_return_value := '||l_app_short_name||'_Def_Util.Get_ObjAttr_Default_Varchar
1630 2',2);
1631 Text( '(p_attribute_code => g_attribute_code,',3);
1632 Text( ' p_database_object_name => g_database_object_name,',3);
1633 Text( ' p_application_id => g_application_id);',3);
1634
1635 END IF;
1636
1637 Text('IF l_return_value IS NOT NULL THEN',2);
1638 Text(' GOTO RETURN_VALUE;',2);
1639 Text('END IF;',1); -- End if l_return_value is not null
1640
1641 END LOOP;
1642 IF C_DEF_RULES%ROWCOUNT = 0 THEN -- 2216700: If no rules are defined for this def. condition
1643 Text('NULL;', 2);
1644 END IF;
1645 CLOSE C_DEF_RULES;
1646
1647 IF l_condition_id <> 0 THEN
1648 Text('END IF;',1); -- End if for the condition elements logic
1649 END IF;
1650 New_Line;
1651
1652 END LOOP;
1653 CLOSE C_CONDNS;
1654 Text('<<RETURN_VALUE>>');
1655 Text('RETURN l_return_value;',1);
1656 New_Line;
1657
1658 Text('EXCEPTION',0);
1659 Text('WHEN OTHERS THEN',0);
1660 Text(' ONT_Def_Util.Add_Invalid_Rule_Message',0);
1661 Text(' ( p_attribute_code => '''||p_attribute_code||'''',0);
1662 Text(' , p_rule_id => l_rule_id',0);
1663 Text(' );',0);
1664 Text(' RETURN NULL;',0);
1665 Text('END Get_Default_Value;',0);
1666
1667 Pkg_End(l_package,'BODY');
1668
1669 EXCEPTION
1670 WHEN OTHERS THEN
1671 PUT_LINE('Error :'||substr(sqlerrm,1,200));
1672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1673 END Create_Obj_Attr_Def_handler;
1674
1675 -- Procedure to create the object attribute handler
1676 -- This procedure generates the attribute defaulting handler for the Rule based
1677 -- Defaulting Framework.
1678 -- This procedure will be called separately to generate a particular attribute
1679 -- handler or it cwn be called by the entity handler to generate all the
1680 -- attribute handlers for a particular entity.
1681
1682 PROCEDURE Create_Obj_Attr_Def_handler
1683 ( p_application_id IN VARCHAR2,
1684 p_database_object_name IN VARCHAR2,
1685 p_attribute_code IN VARCHAR2 ,
1686 p_entity_code IN VARCHAR2,
1687 x_defaulting_api_pkg OUT NOCOPY VARCHAR2
1688
1689 )
1690 IS
1691 l_defaulting_api VARCHAR2(61);
1692 l_package VARCHAR2(30);
1693 l_function VARCHAR2(30);
1694 l_app_short_name VARCHAR2(3);
1695 l_attr_code VARCHAR2(30);
1696 l_data_type VARCHAR2(30);
1697 l_column_name VARCHAR2(30);
1698 l_seq_no NUMBER(4);
1699 l_attr_id VARCHAR2(200);
1700 l_sql_string VARCHAR2(1000);
1701 l_value NUMBER;
1702 -- CURSOR to get the name of the defaulting api pkgs.
1703 CURSOR OA(p_database_object_name varchar2,p_application_id number,
1704 p_attribute_code varchar2)
1705 is
1706 SELECT DEFAULTING_API_PKG,
1707 DEFAULTING_API_PROC,NVL(DATA_TYPE,'CHAR'),
1708 COLUMN_NAME
1709 FROM OE_DEF_AK_ATTR_EXT_V
1710 WHERE DATABASE_OBJECT_NAME = p_database_object_name
1711 AND attribute_application_id = p_application_id
1712 AND attribute_code = p_attribute_code;
1713 CURSOR APP is
1714 SELECT substr(rtrim(APPLICATION_SHORT_NAME),1,3)
1715 FROM fnd_application
1716 WHERE application_id = p_application_id;
1717 CURSOR ENT_SEQ (p_entity_code varchar2)
1718 IS
1719 SELECT entity_id FROM OE_DEF_AK_OBJ_EXT_V
1720 WHERE entity_code = p_entity_code;
1721 --
1722 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1723 --
1724 BEGIN
1725
1726 Init_Applsys_Schema;
1727
1728 open APP;
1729 fetch APP into l_app_short_name;
1730 close APP;
1731
1732 open ENT_SEQ (p_entity_code);
1733 fetch ENT_SEQ into l_seq_no;
1734 close ENT_SEQ;
1735
1736 open OA(p_database_object_name ,p_application_id , p_attribute_code );
1737 fetch OA into l_package,l_function,l_data_type,l_column_name;
1738
1739 if OA%notfound
1740 or l_package is null then
1741 -- l_attr_code may not exceed 20 characters.
1742 l_attr_code := substr(p_attribute_code,1,20);
1743 if substr(l_attr_code,length(l_attr_code)) = '_' then
1744 l_attr_code := substr(l_attr_code,1,length(l_attr_code) -1);
1745 end if;
1746 l_package := l_app_short_name||'_D'||l_seq_no||'_'||l_attr_code;
1747 /*
1748 if length(l_package) > 30 then
1749 l_package := substr(l_package,1,30);
1750 if substr(l_package,length(l_package)) = '_' then
1751 l_package := substr(l_package,1,length(l_package) -1);
1752 end if;
1753 end if;
1754 */
1755 l_function :='Get_Default_Value';
1756 end if;
1757
1758 close OA;
1759
1760
1761 l_defaulting_api := l_package||'.'||l_function;
1762
1763 x_defaulting_api_pkg := l_package;
1764
1765 -- generate the attribute id string
1766 -- (ASSUMPTION: for each attribute, there is a unique constant defined in the
1767 -- entity's util package. For e.g. for the attribute accounting_rule_id
1768 -- on the entity LINE, the constant would be OE_LINE_UTIL.G_ACCOUNTING_RULE)
1769
1770 IF l_column_name like '%_ID' THEN
1771 l_attr_id := 'OE_'||upper(p_entity_code)||'_UTIL.G_'||substr(l_column_name,1,length(l_column_name)-3);
1772 -- OPTION is a restricted word hence retain OPTION_FLAG
1773 -- to construct the attribute id string
1774 ELSIF (( l_column_name like '%_CODE' OR l_column_name like '%_FLAG')
1775 AND l_column_name <> 'OPTION_FLAG')THEN
1776 l_attr_id := 'OE_'||upper(p_entity_code)||'_UTIL.G_'||substr(l_column_name,1,length(l_column_name)-5);
1777 ELSE
1778 l_attr_id := 'OE_'||upper(p_entity_code)||'_UTIL.G_'||l_column_name;
1779 END IF;
1780
1781 l_sql_string :=
1782 'declare '||
1783 'begin '||
1784 ':l_value := '||l_attr_id||'; end;' ;
1785
1786 BEGIN
1787 PUT_LINE( 'Using constant '||l_attr_id);
1788 EXECUTE IMMEDIATE
1789 'declare '||
1790 'begin '||
1791 ':l_value := '||l_attr_id||'; end;'
1792 USING OUT l_value;
1793
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 BEGIN
1797 l_attr_id := 'OE_'||upper(p_entity_code)||'_UTIL.G_'||l_column_name;
1798 PUT_LINE( 'Prev. constant not valid. Using constant '||l_attr_id);
1799 EXECUTE IMMEDIATE
1800 'declare '||
1801 'begin '||
1802 ':l_value := '||l_attr_id||'; end;'
1803 USING OUT l_value;
1804
1805 EXCEPTION
1806 WHEN OTHERS THEN
1807 PUT_LINE( 'ERROR: Not a Valid Constant; '||sqlerrm);
1808 RAISE FND_API.G_EXC_ERROR;
1809 END;
1810 END;
1811
1812
1813 -------------------------------------------------------------------------------
1814 -- WRITING OUT THE SPEC
1815 -------------------------------------------------------------------------------
1816
1817 Pkg_Header( p_pkg_name => l_package,
1818 p_pkg_type => 'SPEC');
1819
1820 -- Global Declarations section of the SPEC file
1821 Text('g_database_object_name varchar2(30) :='''||p_database_object_name||''';',1);
1822 Text('g_attribute_code varchar2(30) :='''||p_attribute_code||''';',1);
1823 Text('g_column_name varchar2(30) :='''||l_column_name||''';',1);
1824 Text('g_application_id NUMBER :='||p_application_id||';',1);
1825 New_Line ;
1826
1827 -- Function Get_Default_Value
1828 Text('FUNCTION Get_Default_Value(p_'||LOWER(p_entity_code)||'_rec IN '|| p_database_object_name||' %ROWTYPE ',1);
1829 Text(') RETURN '|| l_data_type||';',1);
1830 New_Line ;
1831
1832 Pkg_End (l_package,'SPEC');
1833
1834
1835 -------------------------------------------------------------------------------
1836 -- WRITING OUT THE BODY
1837 -------------------------------------------------------------------------------
1838
1839 -- Write out header section of the Package BODY
1840
1841 Pkg_Header( p_pkg_name => l_package,
1842 p_pkg_type => 'BODY');
1843
1844 -- Function Get_Default_Value
1845 New_Line ;
1846 Text('FUNCTION Get_Default_Value(p_'||LOWER(p_entity_code)||'_rec IN '
1847 || p_database_object_name||'%ROWTYPE ',0);
1848 Text(') RETURN '|| l_data_type||' IS ', 1);
1849
1850 -- Declarations
1851 Text('l_return_value VARCHAR2(2000);',1);
1852 IF l_data_type = 'DATE' THEN
1853 Text('l_return_date DATE;',1);
1854 END IF;
1855 Text('l_src_type VARCHAR2(30);',1);
1856 Text('l_index NUMBER;',1);
1857 Text('l_start_index_tbl OE_GLOBALS.NUMBER_TBL_Type;',1);
1858 Text('l_stop_index_tbl OE_GLOBALS.NUMBER_TBL_Type;',1);
1859 Text('l_rule_rec ONT_Def_Util.Attr_Def_Rule_REC_Type;',1);
1860
1861 Text('BEGIN',0);
1862 New_Line ;
1863
1864 -- THe function that is generated is "Get_Valid_Defaulting_Rules".
1865 -- This function determines the valid defaulting condition based on the
1866 -- sequence that is determined when the conditions are initially set-up
1867 Comment ('Get the rules associated with valid defaulting conditions',0);
1868 Text(l_app_short_name||'_'||p_entity_code||'_Def_Util'||'.Get_Valid_Defaulting_Rules',0);
1869 Text('(p_attr_code => g_attribute_code,',1);
1870 Text(' p_attr_id => '||l_attr_id||',',1);
1871 Text(' p_'||LOWER(p_entity_code)||'_rec => p_'||LOWER(p_entity_code)||'_rec,',1);
1872 Text(' x_rules_start_index_tbl => l_start_index_tbl,',1);
1873 Text(' x_rules_stop_index_tbl => l_stop_index_tbl);',1);
1874 New_Line ;
1875
1876
1877 -- GET THE DEFAULT SOURCES
1878 Text('FOR I IN 1..l_start_index_tbl.COUNT LOOP ',0);
1879 New_Line ;
1880 Text('IF l_start_index_tbl(I) <> -1 THEN',0);
1881 New_Line ;
1882 Text(' FOR l_index IN l_start_index_tbl(I)..l_stop_index_tbl(I) LOOP ',0);
1883 New_Line ;
1884 Text(' l_rule_rec := '||l_app_short_name||'_'||p_entity_code||'_Def_Util.g_attr_rules_cache(l_index);',1);
1885 Text(' l_src_type := l_rule_rec.SRC_TYPE;',1);
1886 New_Line ;
1887 Text(' BEGIN',0);
1888 New_Line ;
1889
1890 -- IF DEFAULT SOURCE IS RELATED RECORD
1891
1892 Text( 'IF l_src_type = ''RELATED_RECORD'' THEN ',1);
1893 New_Line ;
1894 IF l_data_type = 'DATE' THEN
1895 Text( 'l_return_date := '||l_app_short_name||'_'||p_entity_code||
1896 '_Def_Util.Get_Foreign_Attr_Val_Date',2);
1897 Text( '(p_foreign_attr_code => l_rule_rec.src_attribute_code,',3);
1898 Text( ' p_record => p_'||LOWER(p_entity_code)||'_rec,',3);
1899 Text( ' p_foreign_database_object_name => l_rule_rec.src_database_object_name);',3);
1900 Text(' if l_return_date is not null then',3);
1901 Text(' RETURN l_return_date;',3);
1902 Text(' end if;',3);
1903 New_Line;
1904 ELSE
1905 Text( 'l_return_value := '||l_app_short_name||'_'||p_entity_code||
1906 '_Def_Util.Get_Foreign_Attr_Val_Varchar2',2);
1907 Text( '(p_foreign_attr_code => l_rule_rec.src_attribute_code,',3);
1908 Text( ' p_record => p_'||LOWER(p_entity_code)||'_rec,',3);
1909 Text( ' p_foreign_database_object_name => l_rule_rec.src_database_object_name);',3);
1910 Return_String(l_data_type);
1911 END IF;
1912
1913 -- IF DEFAULT SOURCE IS SAME RECORD
1914
1915 Text( 'ELSIF l_src_type = ''SAME_RECORD'' THEN ',1);
1916 New_Line ;
1917 IF l_data_type = 'DATE' THEN
1918 Text( 'l_return_date := '||l_app_short_name||'_'||LOWER(p_entity_code)
1919 ||'_Def_Util.Get_Attr_Val_Date',2);
1920 Text( '(p_attr_code => l_rule_rec.src_attribute_code,',3);
1921 Text( ' p_record=> p_'||LOWER(p_entity_code)||'_rec);',3);
1922 Text(' if l_return_date is not null then',3);
1923 Text(' RETURN l_return_date;',3);
1924 Text(' end if;',3);
1925 New_Line;
1926 ELSE
1927 Text( 'l_return_value := '||l_app_short_name||'_'||LOWER(p_entity_code)
1928 ||'_Def_Util.Get_Attr_Val_Varchar2',2);
1929 Text( '(p_attr_code => l_rule_rec.src_attribute_code,',3);
1930 Text( ' p_record=> p_'||LOWER(p_entity_code)||'_rec);',3);
1931 Return_String(l_data_type);
1932 END IF;
1933
1934 -- IF DEFAULT SOURCE IS CONSTANT VALUE
1935
1936 Text( 'ELSIF l_src_type = ''CONSTANT'' OR',1);
1937 Text( ' l_src_type = '''||p_attribute_code||''' THEN',1);
1938 New_Line ;
1939 Text( 'l_return_value := l_rule_rec.src_constant_value;',2);
1940 Return_String(l_data_type);
1941
1942 -- IF DEFAULT SOURCE IS SYSTEM EXPRESSION
1943
1944 Text( 'ELSIF l_src_type = ''SYSTEM'' THEN ',1);
1945 New_Line;
1946 IF l_data_type = 'DATE' THEN
1947 Text( 'l_return_date := '||l_app_short_name||
1948 '_Def_Util.Get_Expression_Value_Date',2);
1949 Text( ' (p_expression_string => l_rule_rec.src_system_variable_expr);',3);
1950 Text( ' if l_return_date is not null then ',2);
1951 Text( ' RETURN l_return_date;',3);
1952 Text( ' end if; ',2);
1953 New_Line;
1954 ELSE
1955 Text( 'l_return_value := '||l_app_short_name||
1956 '_Def_Util.Get_Expression_Value_Varchar2',2);
1957 Text( ' (p_expression_string =>l_rule_rec.src_system_variable_expr);',3);
1958 Return_String(l_data_type);
1959 END IF;
1960
1961 -- IF DEFAULT SOURCE IS PROFILE OPTION
1962
1963 Text( 'ELSIF l_src_type = ''PROFILE_OPTION'' THEN ',1);
1964 New_Line ;
1965 Text( 'l_return_value := FND_PROFILE.VALUE',2);
1966 Text( '(l_rule_rec.SRC_PROFILE_OPTION); ',3);
1967 Return_String(l_data_type);
1968
1969 -- IF DEFAULT SOURCE IS PL/SQL API
1970
1971 Text(' ELSIF l_src_type = ''API'' THEN ',1);
1972 New_Line ;
1973 IF l_data_type = 'DATE' THEN
1974 Text( 'l_return_date := '||l_app_short_name||'_Def_Util.Get_API_Value_Date',2);
1975 ELSE
1976 Text( 'l_return_value := '||l_app_short_name||'_Def_Util.Get_API_Value_Varchar2',2);
1977 END IF;
1978 Text( '(p_api_name => l_rule_rec.SRC_API_NAME,',3);
1979 Text( ' p_database_object_name => l_rule_rec.SRC_DATABASE_OBJECT_NAME,',3);
1980 Text( ' p_attribute_code => l_rule_rec.src_ATTRIBUTE_CODE);',3);
1981 IF l_data_type = 'DATE' THEN
1982 Text( ' if l_return_date is not null then ',2);
1983 Text( ' RETURN l_return_date;',3);
1984 Text( ' end if; ',2);
1985 New_Line;
1986 ELSE
1987 Return_String(l_data_type);
1988 END IF;
1989
1990 -- IF DEFAULT SOURCE IS WAD-ATTRIBUTE DEFAULT
1991
1992 Text( 'ELSIF l_src_type = ''WAD_ATTR'' THEN ',1);
1993 New_Line ;
1994 Text( 'l_return_value := '||l_app_short_name||'_Def_Util.Get_Attr_Default_Varchar2',2);
1995 Text( '(p_attribute_code => g_attribute_code,',3);
1996 Text( ' p_application_id => g_application_id);',3);
1997 Return_String(l_data_type);
1998
1999 -- IF DEFAULT SOURCE IS WAD-OBJECT ATTRIBUTE DEFAULT
2000
2001 Text( 'ELSIF l_src_type = ''WAD_OBJATTR'' THEN ',1);
2002 New_Line ;
2003 Text( 'l_return_value := '||l_app_short_name||'_Def_Util.Get_ObjAttr_Default_Varchar2',2);
2004 Text( '(p_attribute_code => g_attribute_code,',3);
2005 Text( ' p_database_object_name => g_database_object_name,',3);
2006 Text( ' p_application_id => g_application_id);',3);
2007 Return_String(l_data_type);
2008
2009 /* SEQUENCE values, DATABASE default, Form PARAMETER are not supported
2010 default sources for initial R11.5.1
2011
2012 -- if the default source type is a sequence then invoke
2013 -- the function OE_Def_Util.Get_Sequence_Value to get the default.
2014 Text( 'ELSIF l_src_type = ''SEQUENCE'' THEN ',1);
2015 New_Line ;
2016 Text( 'l_return_value := ONT_Def_Util.Get_Sequence_Value',2);
2017 Text( '(p_sequence_name => l_rule_rec.src_sequence_name);',3);
2018 Return_String(l_data_type);
2019
2020 -- if the default source type is FROM the database call the
2021 -- function Get_Database_Default_value to get the default value.
2022 Text( 'ELSIF l_src_type = ''DATABASE'' THEN ',1);
2023 New_Line ;
2024 Text( 'l_return_value := '||l_app_short_name||'_Def_Util.Get_Database_Default_Varchar2',2);
2025 Text( '(p_column_name => g_column_name,',3);
2026 Text( ' p_table_name => l_rule_rec.src_database_object_name);',3);
2027 Return_String(l_data_type);
2028
2029 -- if the default source type is FROM a parameter then invoke
2030 -- function Get_Parameter_value to get the default value.
2031 Text( 'ELSIF l_src_type = ''PARAMETER'' THEN ',1);
2032 New_Line ;
2033 Text( 'l_return_value := '||l_app_short_name||'_Def_Util.GET_PARAMETER_VALUE',2);
2034 Text( '(l_rule_rec.SRC_PARAMETER_NAME);',3);
2035 Return_String(l_data_type);
2036 */
2037
2038 Text( ' END IF; ',1);
2039 New_Line ;
2040
2041 Text(' EXCEPTION',0);
2042 Text(' WHEN OTHERS THEN',0);
2043 Text(' ONT_Def_Util.Add_Invalid_Rule_Message',0);
2044 Text(' ( g_attribute_code',0);
2045 Text(' , l_rule_rec.src_type',0);
2046 Text(' , l_rule_rec.src_api_name',0);
2047 Text(' , l_rule_rec.src_database_object_name',0);
2048 Text(' , l_rule_rec.src_attribute_code',0);
2049 Text(' , l_rule_rec.src_constant_value',0);
2050 Text(' , l_rule_rec.src_profile_option',0);
2051 Text(' , l_rule_rec.src_system_variable_expr',0);
2052 Text(' , l_rule_rec.src_sequence_name ',0);
2053 Text(' );',0);
2054 Text(' END;',0);
2055 New_Line ;
2056 Text(' END LOOP; ',0);
2057 New_Line ;
2058 Text('END IF; ',0);
2059 New_Line ;
2060 Text('END LOOP; ',0);
2061 New_Line ;
2062 Text('RETURN NULL; ',1);
2063
2064 New_Line ;
2065 Text( 'EXCEPTION',0);
2066 Text( 'WHEN OTHERS THEN',0);
2067 Text( ' OE_MSG_PUB.Add_Exc_Msg',0);
2068 Text( ' ( G_PKG_NAME',0);
2069 Text( ' , ''Get_Default_Value''',0);
2070 Text( ' );',0);
2071 Text(' RAISE FND_API.G_EXC_UNEXPECTED_ERROR;',0);
2072 Text( 'END Get_Default_Value; ',0);
2073 New_Line ;
2074 Pkg_End (l_package,'BODY');
2075
2076 UPDATE AK_OBJECT_ATTRIBUTES
2077 SET defaulting_api_pkg= l_package,
2078 defaulting_api_proc = 'Get_Default_Value'
2079 WHERE attribute_code = p_attribute_code
2080 AND database_object_name=p_database_object_name
2081 AND attribute_application_id= p_application_id;
2082 commit;
2083
2084 x_defaulting_api_pkg := l_package;
2085
2086 END Create_Obj_Attr_Def_handler ;
2087 -------------------------------------------------------------------------------
2088 -- PROCEDURE Create_Entity_Def_Util_handler
2089 --
2090 -- This procedure generates the defaulting utility handler for the
2091 -- entity.
2092 -- This procedure will be invoked when the entity handler is being created and
2093 -- a reference is made to the utility package of a related entity.
2094 --
2095 -- ARGUMENTS:
2096 -- p_application_id: Application ID (660 for 'Oracle Order Management')
2097 -- p_database_object_name: AK base view for the related entity
2098 -- (OE_AK_SOLD_TO_ORGS_V for 'Customer')
2099 -- p_entity_code: Short entity code for the related entity stored
2100 -- in OE_AK_OBJECTS_EXT table
2101 -- (SOLD_TO_ORG for 'Customer')
2102 -- p_application_short_name: Application Short Name
2103 -- ('ONT' for 'Oracle Order Management')
2104 -- p_obj_defaulting_enabled : 'Y' if defaulting rules can be defined
2105 -- for the related entity's attributes also.
2106 -- if 'N', the rules AND conditions caching
2107 -- utilities need not be generated
2108 -- p_generation_level : create only the package specification if 'SPEC_ONLY'
2109 -- create only the package body if 'BODY_ONLY'
2110 -- create both spec AND body if 'FULL'
2111 ----------------------------------------------------------------------------
2112 PROCEDURE Create_Entity_Def_Util_handler
2113 (
2114 p_application_id IN VARCHAR2 ,
2115 p_database_object_name IN VARCHAR2 ,
2116 p_entity_code IN VARCHAR2 ,
2117 p_application_short_name IN VARCHAR2 ,
2118 p_obj_defaulting_enabled IN VARCHAR2 DEFAULT 'Y',
2119 p_generation_level IN VARCHAR2 DEFAULT 'FULL'
2120 )
2121 IS
2122
2123 -- table declaration
2124 TYPE obj_attr_tbl_type IS TABLE OF OE_DEF_AK_ATTR_EXT_V%ROWTYPE
2125 INDEX BY BINARY_INTEGER;
2126
2127 TYPE foreign_keys_tbl_type IS TABLE OF OE_DEF_AK_FKEYS_V%ROWTYPE
2128 INDEX BY BINARY_INTEGER;
2129
2130 TYPE col_rec_type IS RECORD
2131 ( attribute_code VARCHAR2(30)
2132 , column_name VARCHAR2(30)
2133 , data_type VARCHAR2(30)
2134 );
2135
2136 TYPE col_tbl_type IS TABLE OF col_rec_type INDEX BY BINARY_INTEGER;
2137
2138 l_ak_obj_attr_tbl obj_attr_tbl_type;
2139 l_fkey_tbl foreign_keys_tbl_type;
2140 l_ukey_attr_tbl col_tbl_type;
2141 l_fkey_attr_tbl col_tbl_type;
2142
2143 -- variables declaration
2144 l_buffer VARCHAR2(20);
2145 l_entity_code VARCHAR2(15);
2146 l_app_short_name VARCHAR2(3);
2147 l_pkg_name VARCHAR2(30);
2148 l_attribute_code VARCHAR2(30);
2149 l_column_name VARCHAR2(30);
2150 l_database_object_name VARCHAR2(30);
2151 l_uk_database_object_name VARCHAR2(30);
2152 l_fk_database_object_name VARCHAR2(30);
2153 J NUMBER;
2154 K NUMBER;
2155 U NUMBER;
2156 F NUMBER;
2157 l_related_pkg_name VARCHAR2(30);
2158 l_related_datatype VARCHAR2(30);
2159 l_uk_name VARCHAR2(30);
2160 l_fk_name VARCHAR2(30);
2161 l_uk_attribute VARCHAR2(30);
2162 l_fk_attribute VARCHAR2(30);
2163 l_uk_column VARCHAR2(30);
2164 l_fk_column VARCHAR2(30);
2165 l_missing_data_str VARCHAR2(30);
2166 l_date_field_exists VARCHAR2(1);
2167
2168
2169 CURSOR OASV (p_database_object_name varchar2,p_application_id number)
2170 is
2171 SELECT aoj.attribute_code,NVL(aoj.data_type,'VARCHAR2')data_type,
2172 aoj.column_name
2173 FROM AK_OBJECT_ATTRIBUTES_VL aoj
2174 WHERE aoj.database_object_name = p_database_object_name
2175 AND aoj.attribute_application_id = p_application_id
2176 AND aoj.column_name is not null;
2177
2178 CURSOR PKEY (l_database_object_name varchar2,p_application_id number)
2179 IS
2180 SELECT unique_key_name
2181 FROM AK_UNIQUE_KEYS
2182 WHERE database_object_name=l_database_object_name
2183 AND application_id=p_application_id
2184 AND rownum = 1;
2185
2186 CURSOR UKEY_COL(l_uk_name varchar2,p_application_id number,
2187 l_uk_database_object_name varchar2)
2188 IS
2189 SELECT attribute_code,column_name,
2190 unique_key_sequence,data_type
2191 FROM OE_DEF_AK_UKEY_COLS_V
2192 WHERE unique_key_name= l_uk_name
2193 AND database_object_name = l_uk_database_object_name
2194 ORDER BY unique_key_sequence;
2195
2196 CURSOR FKEY_COL(l_fk_name varchar2,p_application_id number,
2197 l_database_object_name varchar2)
2198 IS
2199 SELECT b.attribute_code, b.column_name,
2200 foreign_key_sequence, b.data_type
2201 FROM ak_object_attributes_vl b,ak_foreign_key_columns a
2202 WHERE a.foreign_key_name= l_fk_name
2203 AND b.database_object_name = l_database_object_name
2204 AND b.attribute_application_id = a.attribute_application_id
2205 AND b.attribute_code = a.attribute_code
2206 ORDER BY foreign_key_sequence;
2207
2208 CURSOR FKEY(l_database_object_name varchar2,p_application_id number)
2209 IS
2210 SELECT unique_key_name,uk_database_object_name,foreign_key_name,
2211 fk_entity_code
2212 FROM OE_DEF_AK_FKEYS_V
2213 WHERE fk_database_object_name=l_database_object_name
2214 AND application_id=p_application_id;
2215
2216 CURSOR FKEY_DATE(l_database_object_name varchar2,p_application_id number)
2217 IS
2218 SELECT unique_key_name,uk_database_object_name
2219 , foreign_key_name, fk_entity_code
2220 FROM OE_DEF_AK_FKEYS_V fk
2221 WHERE fk_database_object_name=l_database_object_name
2222 AND application_id=p_application_id
2223 AND exists (SELECT 'Y'
2224 FROM ak_object_attributes_vl oa
2225 WHERE oa.database_object_name = fk.uk_database_object_name
2226 AND oa.data_type = 'DATE'
2227 );
2228 --
2229 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2230 --
2231 BEGIN
2232
2233 Init_Applsys_Schema;
2234
2235 l_app_short_name := p_application_short_name;
2236 l_database_object_name := p_database_object_name;
2237
2238 -- Generate the util package
2239
2240 l_pkg_name := l_app_short_name||'_'||p_entity_code||'_Def'||'_Util';
2241
2242
2243 --------------------------------------------------------------------
2244 -- WRITE OUT THE SPEC FOR THE UTIL PACKAGE e.g. ONT_HEADER_DEF_UTIL
2245 --------------------------------------------------------------------
2246
2247 if p_generation_level = 'BODY_ONLY' then
2248 goto START_OF_BODY;
2249 end if;
2250
2251 Pkg_Header( p_pkg_name => l_pkg_name,
2252 p_pkg_type => 'SPEC');
2253
2254
2255 Text('g_cached_record '||l_database_object_name||'%ROWTYPE;',1);
2256
2257 IF upper(p_obj_defaulting_enabled) = 'Y' THEN
2258 Text('g_attr_rules_cache ONT_DEF_UTIL.Attr_Def_Rule_Tbl_Type;',1);
2259 END IF;
2260
2261 -- FUNCTION Get_Attr_Val_Varchar2
2262
2263 New_Line;
2264 Text ('FUNCTION Get_Attr_Val_Varchar2',0);
2265 Parameter('attr_code ','IN','VARCHAR2',0,30,TRUE);
2266 Parameter('record','IN',
2267 l_database_object_name||'%ROWTYPE ',0);
2268 Text (') RETURN VARCHAR2;',0);
2269
2270 -- FUNCTION Get_Attr_Val_Date
2271
2272 New_Line;
2273 Text ('FUNCTION Get_Attr_Val_Date',0);
2274 Parameter('attr_code ','IN','VARCHAR2',0,30,TRUE);
2275 Parameter('record','IN',
2276 l_database_object_name||'%ROWTYPE ',0);
2277 Text (') RETURN DATE;',0);
2278
2279
2280 -- FUNCTION Sync_<Entity>_Cache
2281
2282 New_Line;
2283 Text('FUNCTION Sync_'||p_entity_code||'_Cache',0);
2284 FOR pkey_name in PKEY (l_database_object_name ,p_application_id )
2285 LOOP
2286 l_uk_name := pkey_name.unique_key_name;
2287 l_uk_database_object_name := p_database_object_name;
2288 -- Cache the primary key columns for this entity in l_ukey_attr_tbl
2289 l_ukey_attr_tbl.DELETE;
2290 u:=0;
2291 FOR ukey_cols in UKEY_COL (l_uk_name ,p_application_id
2292 , l_uk_database_object_name )
2293 LOOP
2294 if u=0 then
2295 Parameter
2296 (ukey_cols.column_name,'IN',ukey_cols.data_type,0,30,TRUE);
2297 else
2298 Parameter
2299 (ukey_cols.column_name,'IN',ukey_cols.data_type,0,30);
2300 end if;
2301 l_ukey_attr_tbl(u).attribute_code := ukey_cols.attribute_code;
2302 l_ukey_attr_tbl(u).column_name := ukey_cols.column_name;
2303 l_ukey_attr_tbl(u).data_type := ukey_cols.data_type;
2304 u := u+1;
2305 END LOOP;
2306 END LOOP;
2307 Text (') RETURN NUMBER;',0);
2308 New_Line;
2309
2310 -- Following functions are NEEDED ONLY IF attributes for this object can be
2311 -- defaulted i.e. defaulting_enabled_flag = 'Y' for this entity on the
2312 -- oe_ak_objects_ext table
2313
2314 IF upper(p_obj_defaulting_enabled) = 'Y' THEN
2315
2316 -- FUNCTION Get_Foreign_Attr_Val_Varchar2
2317
2318 New_Line;
2319 Text ('FUNCTION Get_Foreign_Attr_Val_Varchar2',0);
2320 Parameter('foreign_attr_code ','IN','VARCHAR2',0,30,TRUE);
2321 Parameter('record','IN',
2322 l_database_object_name||'%ROWTYPE ',0,30);
2323 Parameter('foreign_database_object_name ','IN','VARCHAR2',0);
2324 Text (') RETURN VARCHAR2;',0);
2325
2326 -- FUNCTION Get_Foreign_Attr_Val_Date
2327
2328 New_Line;
2329 Text ('FUNCTION Get_Foreign_Attr_Val_Date',0);
2330 Parameter('foreign_attr_code ','IN','VARCHAR2',0,30,TRUE);
2331 Parameter('record','IN',
2332 l_database_object_name||'%ROWTYPE ',0,30);
2333 Parameter('foreign_database_object_name ','IN','VARCHAR2',0);
2334 Text (') RETURN DATE;',0);
2335
2336 -- PROCEDURE Clear_<Entity>_Cache
2337
2338 New_Line;
2339 Text ('PROCEDURE Clear_'||p_entity_code||'_Cache;',0);
2340
2341 -- PROCEDURE Get_Valid_Defaulting_Rules
2342
2343 New_Line;
2344 Text ('PROCEDURE Get_Valid_Defaulting_Rules',0);
2345 Parameter('attr_code ','IN','VARCHAR2',0,30,TRUE);
2346 Parameter('attr_id ','IN','NUMBER',0,30,FALSE);
2347 Parameter(LOWER(p_entity_code)||'_rec','IN',
2348 l_database_object_name||'%ROWTYPE',0);
2349 Parameter('rules_start_index_tbl ','OUT','OE_GLOBALS.NUMBER_TBL_Type',0,30,FALSE);
2350 Parameter('rules_stop_index_tbl ','OUT','OE_GLOBALS.NUMBER_TBL_Type',0,30,FALSE);
2351 Text (');',0);
2352
2353 -- FUNCTION Validate_Defaulting_Condition
2354
2355 New_Line;
2356 Text ('FUNCTION Validate_Defaulting_Condition',0);
2357 Parameter('condition_id ','IN','NUMBER',0,30,TRUE);
2358 Parameter(LOWER(p_entity_code)||'_rec','IN',
2359 l_database_object_name||'%ROWTYPE ',0);
2360 Text (') RETURN BOOLEAN;',0);
2361
2362 END IF;
2363
2364 New_Line;
2365
2366 Pkg_End (l_pkg_name,'SPEC');
2367
2368
2369 <<START_OF_BODY>>
2370 -----------------------------------------------------------------------------
2371 -- WRITE OUT THE BODY FOR THE UTIL PACKAGE e.g. ONT_HEADER_DEF_UTIL
2372 -----------------------------------------------------------------------------
2373
2374 if p_generation_level = 'SPEC_ONLY' then
2375 RETURN;
2376 end if;
2377
2378 Pkg_Header( p_pkg_name => l_pkg_name,
2379 p_pkg_type => 'BODY');
2380
2381 New_Line;
2382 Text ('g_database_object_name varchar2(30) :='''||l_database_object_name||''';',1);
2383
2384 if upper(p_obj_defaulting_enabled) <> 'Y' then
2385 goto END_OF_BODY_GLOBALS;
2386 end if;
2387
2388 New_Line;
2389 Text ('TYPE Condition_Rec_Type IS RECORD (',1);
2390 Text ('condition_id NUMBER,',1);
2391 Text ('group_number NUMBER,',1);
2392 Text ('attribute_code VARCHAR2(30),',1);
2393 Text ('value_op VARCHAR2(15),',1);
2394 Text ('value_string VARCHAR2(255));',1);
2395 New_Line;
2396
2397 Text ('TYPE Condition_Tbl_Type IS TABLE OF Condition_Rec_Type',1);
2398 Text ('INDEX BY BINARY_INTEGER;',1);
2399 Text ('g_conditions_tbl_cache Condition_Tbl_Type;',1);
2400 New_Line;
2401 Text ('g_attr_condns_cache ONT_DEF_UTIL.Attr_Condn_Tbl_Type;',1);
2402
2403 <<END_OF_BODY_GLOBALS>>
2404
2405 New_Line;
2406 New_Line;
2407
2408 -----------------------------------------------------------------------
2409 -- FUNCTION Get_Attr_Val_Varchar2
2410 -- This function returns the value of a given attribute on this entity
2411 -- from the record passed to it
2412 -----------------------------------------------------------------------
2413
2414 PUT_LINE( ' Create FUNCTION Get_Attr_Val_Varchar2');
2415
2416 Text ('FUNCTION Get_Attr_Val_Varchar2',0);
2417 Parameter('attr_code ','IN','VARCHAR2',0,30,TRUE);
2418 Parameter('record','IN',
2419 l_database_object_name||'%ROWTYPE ',0);
2420 Text (') RETURN VARCHAR2',0);
2421 Text ('IS',0);
2422 Text ('BEGIN',0);
2423 New_Line;
2424
2425 l_ak_obj_attr_tbl.DELETE;
2426 j :=0;
2427 FOR l_attr_rec in OASV(p_database_object_name ,p_application_id )
2428 LOOP
2429 l_ak_obj_attr_tbl(j).attribute_code := l_attr_rec.attribute_code;
2430 l_ak_obj_attr_tbl(j).column_name := l_attr_rec.column_name;
2431 l_ak_obj_attr_tbl(j).data_type := l_attr_rec.data_type;
2432 j := j+1;
2433 END LOOP;
2434
2435 IF l_ak_obj_attr_tbl.COUNT = 0 THEN
2436 PUT_LINE
2437 (' ERROR: NO ATTRIBUTES DEFINED FOR THIS ENTITY');
2438 RAISE FND_API.G_EXC_ERROR;
2439 END IF;
2440
2441 j:= 0;
2442 FOR J IN 0..l_ak_obj_attr_tbl.COUNT -1 LOOP
2443
2444 l_attribute_code := l_ak_obj_attr_tbl(j).attribute_code ;
2445 l_column_name := l_ak_obj_attr_tbl(j).column_name ;
2446
2447 IF l_ak_obj_attr_tbl(j).data_type = 'NUMBER' THEN
2448 l_missing_data_str := 'FND_API.G_MISS_NUM';
2449 ELSIF l_ak_obj_attr_tbl(j).data_type = 'VARCHAR2' THEN
2450 l_missing_data_str := 'FND_API.G_MISS_CHAR';
2451 ELSIF l_ak_obj_attr_tbl(j).data_type = 'DATE' THEN
2452 l_missing_data_str := 'FND_API.G_MISS_DATE';
2453 END IF;
2454
2455 if j=0 then
2456 Text ('IF p_attr_code =('||''''||l_attribute_code||''''||') THEN',0);
2457 else
2458 Text ('ELSIF p_attr_code =('||''''||l_attribute_code||''''||') THEN',0);
2459 end if;
2460
2461 Text ('IF NVL(p_record.'||l_column_name||', '||l_missing_data_str||
2462 ') <> '||l_missing_data_str||' THEN',1);
2463 Text ('RETURN p_record.'||l_column_name||';',1);
2464 Text ('ELSE',1);
2465 Text ('RETURN NULL; ',1);
2466 Text ('END IF;',1);
2467
2468 END LOOP;
2469
2470 Text ('ELSE',0);
2471 Text ('RETURN NULL; ',0);
2472 Text ('END IF;',0);
2473 Text ('END Get_Attr_Val_Varchar2;',0);
2474
2475 New_Line;
2476 New_Line;
2477
2478
2479 -----------------------------------------------------------------------
2480 -- FIX BUG 1548433 - Added function Get_Attr_Val_Date
2481 -- FUNCTION Get_Attr_Val_Date. This function will return a
2482 -- DATE default so that the time component is NOT lost.
2483 -- This function returns the value of a given attribute on this entity
2484 -- from the record passed to it
2485 -----------------------------------------------------------------------
2486 PUT_LINE( ' Create FUNCTION Get_Attr_Val_Date');
2487
2488
2489 Text ('FUNCTION Get_Attr_Val_Date',0);
2490 Parameter('attr_code ','IN','VARCHAR2',0,30,TRUE);
2491 Parameter('record','IN',
2492 l_database_object_name||'%ROWTYPE ',0);
2493 Text (') RETURN DATE',0);
2494 Text ('IS',0);
2495 Text ('BEGIN',0);
2496 New_Line;
2497
2498 j:= 0;
2499 l_date_field_exists := 'N';
2500
2501 -- First, set up the if loop for all date attributes on this entity
2502 FOR J IN 0..l_ak_obj_attr_tbl.COUNT -1 LOOP
2503
2504 IF l_ak_obj_attr_tbl(j).data_type = 'DATE' THEN
2505 l_attribute_code := l_ak_obj_attr_tbl(j).attribute_code ;
2506 l_column_name := l_ak_obj_attr_tbl(j).column_name ;
2507 l_missing_data_str := 'FND_API.G_MISS_DATE';
2508 if l_date_field_exists = 'N' then
2509 Text ('IF p_attr_code =('||''''||l_attribute_code||''''||') THEN',0);
2510 else
2511 Text ('ELSIF p_attr_code =('||''''||l_attribute_code||''''||') THEN',0);
2512 end if;
2513 Text ('IF NVL(p_record.'||l_column_name||', '||l_missing_data_str||
2514 ') <> '||l_missing_data_str||' THEN',2);
2515 Text ('RETURN p_record.'||l_column_name||';',2);
2516 Text ('ELSE',2);
2517 Text ('RETURN NULL; ',2);
2518 Text ('END IF;',2);
2519 l_date_field_exists := 'Y';
2520 END IF;
2521
2522 END LOOP;
2523
2524 -- Next, loop through all non-date attributes on this entity and return
2525 -- to_date of the value in that attribute
2526 FOR J IN 0..l_ak_obj_attr_tbl.COUNT -1 LOOP
2527
2528 IF l_ak_obj_attr_tbl(j).data_type <> 'DATE' THEN
2529 l_attribute_code := l_ak_obj_attr_tbl(j).attribute_code ;
2530 l_column_name := l_ak_obj_attr_tbl(j).column_name ;
2531 IF l_ak_obj_attr_tbl(j).data_type = 'NUMBER' THEN
2532 l_missing_data_str := 'FND_API.G_MISS_NUM';
2533 ELSIF l_ak_obj_attr_tbl(j).data_type = 'VARCHAR2' THEN
2534 l_missing_data_str := 'FND_API.G_MISS_CHAR';
2535 END IF;
2536 if l_date_field_exists = 'N' then
2537 Text ('IF p_attr_code =('||''''||l_attribute_code||''''||') THEN',0);
2538 else
2539 Text ('ELSIF p_attr_code =('||''''||l_attribute_code||''''||') THEN',0);
2540 end if;
2541 Text ('IF NVL(p_record.'||l_column_name||', '||l_missing_data_str||
2542 ') <> '||l_missing_data_str||' THEN',2);
2543 Text ('RETURN to_date(p_record.'||l_column_name||',''RRRR/MM/DD HH24:MI:SS'');',2);
2544 Text ('ELSE',2);
2545 Text ('RETURN NULL; ',2);
2546 Text ('END IF;',2);
2547 l_date_field_exists := 'Y';
2548 END IF;
2549
2550 END LOOP;
2551
2552 Text ('ELSE',0);
2553 Text ('RETURN NULL; ',0);
2554 Text ('END IF;',0);
2555 New_Line;
2556 Text ('END Get_Attr_Val_Date;',0);
2557 New_Line;
2558 New_Line;
2559
2560
2561 -- Cache the primary key columns for this entity in l_ukey_attr_tbl
2562 -- Will be needed in generation of following procedures/functions
2563 FOR pkey_name in PKEY (l_database_object_name ,p_application_id )
2564 LOOP
2565 l_uk_name := pkey_name.unique_key_name;
2566 l_uk_database_object_name := p_database_object_name;
2567 l_ukey_attr_tbl.DELETE;
2568 u:=0;
2569 FOR ukey_cols in UKEY_COL (l_uk_name ,p_application_id
2570 , l_uk_database_object_name )
2571 LOOP
2572 l_ukey_attr_tbl(u).attribute_code := ukey_cols.attribute_code;
2573 l_ukey_attr_tbl(u).column_name := ukey_cols.column_name;
2574 l_ukey_attr_tbl(u).data_type := ukey_cols.data_type;
2575 u := u+1;
2576 END LOOP;
2577 END LOOP;
2578
2579 -------------------------------------------------------------------------------
2580 -- PROCEDURE Clear_<Entity>_Cache
2581 -- Function to clear cache.
2582 -- Assign all the attributes of the cached record to null;
2583 -------------------------------------------------------------------------------
2584
2585 PUT_LINE( ' Create PROCEDURE Clear_'||p_entity_code||'_Cache');
2586
2587 Text ('PROCEDURE Clear_'||p_entity_code||'_Cache',1);
2588 Text ('IS ',1);
2589 Text ('BEGIN ',1);
2590
2591 FOR u in 0..l_ukey_attr_tbl.COUNT -1 LOOP
2592 l_uk_column := l_ukey_attr_tbl(U).column_name;
2593 Text ('g_cached_record.'||l_uk_column||' := null;',1);
2594 END LOOP;
2595
2596 Text (' END Clear_'||p_entity_code||'_Cache;',1);
2597 New_Line;
2598 New_Line;
2599
2600
2601 -------------------------------------------------------------------------------
2602 -- FUNCTION Sync_'||p_entity_code||'_Cache
2603 -- Function to Synchronize cache.If the cached record is not equal to the
2604 -- current record , clear the cache AND load the cache with the new record.
2605 -------------------------------------------------------------------------------
2606
2607 PUT_LINE(' Create FUNCTION Sync_'||p_entity_code||'_Cache');
2608
2609 l_uk_database_object_name := p_database_object_name;
2610
2611 Text('FUNCTION Sync_'||p_entity_code||'_Cache',0);
2612 FOR u in 0..l_ukey_attr_tbl.COUNT -1 LOOP
2613 l_uk_attribute := l_ukey_attr_tbl(U).attribute_code;
2614 l_uk_column := l_ukey_attr_tbl(U).column_name;
2615 -- first column
2616 if u=0 then
2617 Parameter
2618 (l_uk_column,'IN', l_ukey_attr_tbl(U).data_type,0,30,TRUE);
2619 else
2620 Parameter
2621 (l_uk_column,'IN', l_ukey_attr_tbl(U).data_type,0,30);
2622 end if;
2623 END LOOP;
2624
2625 New_Line;
2626
2627 New_Line;
2628 Text (') RETURN NUMBER',0);
2629
2630 -- Write out the local variables declared in this function
2631 Text ('IS',0);
2632
2633 Text ('CURSOR cache IS ',0);
2634 Text ('SELECT * FROM '||p_database_object_name,1);
2635 FOR U in 0..l_ukey_attr_tbl.COUNT -1 LOOP
2636 l_uk_attribute := l_ukey_attr_tbl(U).attribute_code;
2637 l_uk_column := l_ukey_attr_tbl(U).column_name;
2638 if u = 0 then
2639 Text ('WHERE '||l_uk_column||' = p_'||l_uk_column,1);
2640 else
2641 Text ('AND '||l_uk_column||' = p_'||l_uk_column,1);
2642 end if;
2643 END LOOP;
2644 Text (';',1);
2645 Text ('BEGIN',0);
2646 New_Line;
2647
2648 FOR U in 0..l_ukey_attr_tbl.COUNT -1 LOOP
2649
2650 l_uk_column := l_ukey_attr_tbl(U).column_name;
2651
2652 if substr(l_ukey_attr_tbl(U).data_type,1,4) = 'DATE' then
2653 l_buffer := 'FND_API.G_MISS_DATE';
2654 elsif substr(l_ukey_attr_tbl(U).data_type,1,3) = 'NUM'
2655 or substr(l_ukey_attr_tbl(U).data_type,1,3) = 'INT' then
2656 l_buffer := 'FND_API.G_MISS_NUM';
2657 else
2658 l_buffer := 'FND_API.G_MISS_CHAR';
2659 end if;
2660
2661 if u = 0 then
2662 Text('IF (NVL(p_'||l_uk_column||','||l_buffer||
2663 ') = '||l_buffer||') ',0);
2664 else
2665 Text('OR (NVL(p_'||l_uk_column||','||l_buffer||
2666 ') = '||l_buffer||') ',0);
2667 end if;
2668
2669 END LOOP;
2670 Text('THEN',0);
2671 Text ('RETURN 0 ;',1);
2672
2673 -- Cache the record
2674 FOR U in 0..l_ukey_attr_tbl.COUNT -1 LOOP
2675 l_uk_column := l_ukey_attr_tbl(U).column_name;
2676
2677 if substr(l_ukey_attr_tbl(U).data_type,1,4) = 'DATE' then
2678 l_buffer := 'FND_API.G_MISS_DATE';
2679 elsif substr(l_ukey_attr_tbl(U).data_type,1,3) = 'NUM'
2680 or substr(l_ukey_attr_tbl(U).data_type,1,3) = 'INT' then
2681 l_buffer := 'FND_API.G_MISS_NUM';
2682 else
2683 l_buffer := 'FND_API.G_MISS_CHAR';
2684 end if;
2685
2686 if u = 0 then
2687 Text
2688 ('ELSIF (NVL(g_cached_record.'||l_uk_column||','||l_buffer||
2689 ') <> p_'||l_uk_column||') ',0);
2690 else
2691 Text
2692 ('OR (NVL(g_cached_record.'||l_uk_column||','||l_buffer||
2693 ') <> p_'||l_uk_column||') ',0);
2694 end if;
2695 END LOOP;
2696 Text('THEN',0);
2697 Text ('Clear_'||p_entity_code||'_Cache;',1);
2698 Text ('Open cache;',1);
2699 Text ('FETCH cache into g_cached_record;',1);
2700 Text ('IF cache%NOTFOUND THEN',1); -- 2218044
2701 Text ('RETURN 0;',2);
2702 Text ('END IF;',1); -- 2218044 end
2703 Text ('Close cache;',1);
2704 Text ('RETURN 1 ;',1);
2705 Text ('END IF;',0);
2706 New_Line;
2707
2708 Text ('RETURN 1 ;',1);
2709 Text ('EXCEPTION',0);
2710 Text ('WHEN OTHERS THEN ',1);
2711 Text ('RETURN 0 ;',1);
2712 Text ('END Sync_'||p_entity_code||'_Cache;',0);
2713 New_Line;
2714 New_Line;
2715
2716 -- END OF FUNCTION Sync_'||p_entity_code||'_Cache
2717
2718 -- if object CANNOT be defaulted, then skip the rest of the procedures
2719 -- AND go to the end
2720 if upper(p_obj_defaulting_enabled) <> 'Y' then
2721 goto END_OF_BODY;
2722 end if;
2723
2724 -------------------------------------------------------------------------------
2725 -- FUNCTION Get_Foreign_Attr_Val_Varchar2
2726 -- This Function is used to get the value when the default source_type
2727 -- is a related record. Based on the foreign key relationship, it gets the value
2728 -- FROM the related entity.
2729 -------------------------------------------------------------------------------
2730
2731 PUT_LINE( ' Create FUNCTION Get_Foreign_Attr_Val_Varchar2');
2732
2733 Text ('FUNCTION Get_Foreign_Attr_Val_Varchar2',0);
2734 Parameter('foreign_attr_code ','IN','VARCHAR2',0,30,TRUE);
2735 Parameter('record','IN',
2736 l_database_object_name||'%ROWTYPE ',0,30);
2737 Parameter('foreign_database_object_name ','IN','VARCHAR2',0);
2738 Text (') RETURN VARCHAR2',0);
2739 New_Line;
2740 Text ('IS',0);
2741
2742 j :=0;
2743 l_fkey_tbl.DELETE;
2744
2745 -- Retrieve all the objects to which this object has a foreign key
2746 FOR fkey_view in FKEY (l_database_object_name ,p_application_id )
2747 LOOP
2748 l_fkey_tbl(j).uk_database_object_name := fkey_view.uk_database_object_name;
2749 l_fkey_tbl(j).unique_key_name := fkey_view.unique_key_name;
2750 l_fkey_tbl(j).foreign_key_name := fkey_view.foreign_key_name;
2751 l_fkey_tbl(j).fk_entity_code := fkey_view.fk_entity_code;
2752 j := j+1;
2753 END LOOP;
2754
2755 New_Line;
2756 Text ('BEGIN',0);
2757 New_Line;
2758
2759 IF l_fkey_tbl.COUNT = 0 THEN
2760
2761 PUT_LINE(' No foreign keys defined for this entity');
2762
2763 ELSIF l_fkey_tbl.COUNT <> 0 then
2764
2765 j:= 0;
2766
2767 FOR J in 0..l_fkey_tbl.COUNT -1 LOOP
2768
2769 l_uk_name := l_fkey_tbl(j).unique_key_name;
2770 l_fk_name := l_fkey_tbl(j).foreign_key_name;
2771 l_uk_database_object_name := l_fkey_tbl(j).uk_database_object_name;
2772 l_entity_code := l_fkey_tbl(j).fk_entity_code;
2773 l_related_pkg_name := l_app_short_name||'_'||l_entity_code||'_Def'||'_Util';
2774
2775 l_ukey_attr_tbl.DELETE;
2776 U :=0;
2777 -- Get all the unique key columns e.g. ORGANIZATION_ID on OE_AK_SOLD_TO_ORGS_V
2778 FOR ukey_cols in UKEY_COL (l_uk_name ,p_application_id , l_uk_database_object_name )
2779 LOOP
2780 l_ukey_attr_tbl(u).attribute_code := ukey_cols.attribute_code;
2781 l_ukey_attr_tbl(u).column_name := ukey_cols.column_name;
2782 l_ukey_attr_tbl(u).data_type := ukey_cols.data_type;
2783 u := u+1;
2784 END LOOP;
2785
2786 IF l_ukey_attr_tbl.COUNT =0 THEN
2787 -- THere are no unique keys defined in the AK for this entity. so exit
2788 PUT_LINE(' ERROR: No unique key columns on the key:'
2789 ||l_uk_name);
2790 RAISE FND_API.G_EXC_ERROR;
2791 END IF;
2792
2793 l_fkey_attr_tbl.DELETE;
2794 F :=0;
2795 -- Get all the foreign key columns e.g. SOLD_TO_ORG_ID on OE_AK_ORDER_LINES_V
2796 FOR fkey_cols in FKEY_COL (l_fk_name ,p_application_id ,l_database_object_name )
2797 LOOP
2798 l_fkey_attr_tbl(f).attribute_code := fkey_cols.attribute_code;
2799 l_fkey_attr_tbl(f).column_name := fkey_cols.column_name;
2800 l_fkey_attr_tbl(f).data_type := fkey_cols.data_type;
2801 f := f+1;
2802 END LOOP;
2803
2804 IF l_fkey_attr_tbl.COUNT = 0 THEN
2805 PUT_LINE(' ERROR: No foreign key columns on the key:'
2806 ||l_fk_name);
2807 RAISE FND_API.G_EXC_ERROR;
2808 END IF;
2809
2810 if j=0 then
2811 Text (' IF (p_foreign_database_object_name = '||''''
2812 ||l_uk_database_object_name||''''||') THEN',0);
2813 else
2814 Text (' ELSIF (p_foreign_database_object_name = '||''''
2815 ||l_uk_database_object_name||''''||') THEN',0);
2816 end if;
2817
2818 IF l_ukey_attr_tbl.COUNT = 1 THEN
2819
2820 l_uk_attribute := l_ukey_attr_tbl(0).attribute_code;
2821 l_fk_attribute := l_fkey_attr_tbl(0).attribute_code;
2822 l_uk_column := l_ukey_attr_tbl(0).column_name;
2823 l_fk_column := l_fkey_attr_tbl(0).column_name;
2824 IF l_fkey_attr_tbl(0).data_type = 'NUMBER' THEN
2825 l_missing_data_str := 'FND_API.G_MISS_NUM';
2826 ELSIF l_fkey_attr_tbl(0).data_type = 'VARCHAR2' THEN
2827 l_missing_data_str := 'FND_API.G_MISS_CHAR';
2828 ELSIF l_fkey_attr_tbl(0).data_type = 'DATE' THEN
2829 l_missing_data_str := 'FND_API.G_MISS_DATE';
2830 END IF;
2831 -- Fix Bug 1549538: Return NULL if the foreign key column is null
2832 -- or missing
2833 Text('IF NVL(p_record.'||l_fk_column||','||l_missing_data_str||') = '
2834 ||l_missing_data_str||' THEN ',2);
2835 Text('RETURN NULL;',3);
2836 Text('END IF;',2);
2837 Text('IF '||l_related_pkg_name||'.Sync_'||l_entity_code||'_Cache',2);
2838 Text('(p_'||l_uk_column||' => p_record.'||l_fk_column||') = 1 then ',3);
2839 Text('RETURN '||l_related_pkg_name||'.Get_Attr_Val_Varchar2',2);
2840 Text('(p_foreign_attr_code,'||l_related_pkg_name||'.g_cached_record); ',3);
2841 Text('END IF;');
2842
2843 ELSE
2844
2845 U:= 0;
2846 FOR U in 0..l_ukey_attr_tbl.COUNT -1 LOOP
2847
2848 l_fk_attribute := l_fkey_attr_tbl(U).attribute_code;
2849 l_fk_column := l_fkey_attr_tbl(U).column_name;
2850 IF l_fkey_attr_tbl(U).data_type = 'NUMBER' THEN
2851 l_missing_data_str := 'FND_API.G_MISS_NUM';
2852 ELSIF l_fkey_attr_tbl(U).data_type = 'VARCHAR2' THEN
2853 l_missing_data_str := 'FND_API.G_MISS_CHAR';
2854 ELSIF l_fkey_attr_tbl(U).data_type = 'DATE' THEN
2855 l_missing_data_str := 'FND_API.G_MISS_DATE';
2856 END IF;
2857
2858 -- Fix Bug 1549538: Return NULL if any one of the foreign key
2859 -- columns is null or missing
2860 if u = 0 then
2861 Text('IF ( NVL(p_record.'||l_fk_column||','||l_missing_data_str||') = '
2862 ||l_missing_data_str||' )',2);
2863 else
2864 Text(' OR ( NVL(p_record.'||l_fk_column||','||l_missing_data_str||') = '
2865 ||l_missing_data_str||' )',2);
2866 end if;
2867 if u = l_ukey_attr_tbl.COUNT -1 then
2868 Text('THEN',2);
2869 Text('RETURN NULL;',2);
2870 Text('END IF;',2);
2871 end if;
2872
2873 END LOOP;
2874
2875 FOR U in 0..l_ukey_attr_tbl.COUNT -1 LOOP
2876
2877 l_uk_attribute := l_ukey_attr_tbl(U).attribute_code;
2878 l_fk_attribute := l_fkey_attr_tbl(U).attribute_code;
2879 l_uk_column := l_ukey_attr_tbl(U).column_name;
2880 l_fk_column := l_fkey_attr_tbl(U).column_name;
2881
2882 if u = 0 then
2883 Text('IF '||l_related_pkg_name||'.Sync_'||l_entity_code||'_Cache(p_'||l_uk_column
2884 ||' => p_record.'||l_fk_column||',',2);
2885 elsif u = l_ukey_attr_tbl.COUNT -1 then
2886 Text('p_'||l_uk_column||' => p_record.'||l_fk_column||') =1 then ',2);
2887 Text('RETURN '||l_related_pkg_name||'.Get_Attr_Val_Varchar2(p_foreign_attr_code,'
2888 ||l_related_pkg_name||'.g_cached_record); ',2);
2889 Text('END IF;');
2890 else
2891 Text('p_'||l_uk_column||' => p_record.'||l_fk_column||', ',2);
2892 end if;
2893 END LOOP;
2894
2895 END IF;
2896
2897 if j = l_fkey_tbl.COUNT -1 then
2898 Text ('END IF;',0);
2899 end if;
2900
2901 New_Line;
2902 END LOOP;
2903
2904 END IF; -- end if foreign keys exist
2905
2906 Text (' RETURN NULL;',2);
2907 Text ('END Get_Foreign_Attr_Val_Varchar2;',0);
2908 New_Line;
2909 -- END OF FUNCTION Get_Foreign_Attr_Val_Varchar2
2910
2911
2912 -----------------------------------------------------------------------
2913 -- FIX BUG 1548433 - Added function Get_Foreign_Attr_Val_Date
2914 -- FUNCTION Get_Foreign_Attr_Val_Date. This function will return a
2915 -- DATE default so that the time component is NOT lost.
2916 -- This Function is used to get the value when the default source_type
2917 -- is a related record. Based on the foreign key relationship, it gets
2918 -- the value FROM the related entity.
2919 -----------------------------------------------------------------------
2920
2921 PUT_LINE( ' Create FUNCTION Get_Foreign_Attr_Val_Date');
2922
2923 Text ('FUNCTION Get_Foreign_Attr_Val_Date',0);
2924 Parameter('foreign_attr_code ','IN','VARCHAR2',0,30,TRUE);
2925 Parameter('record','IN',
2926 l_database_object_name||'%ROWTYPE ',0,30);
2927 Parameter('foreign_database_object_name ','IN','VARCHAR2',0);
2928 Text (') RETURN DATE',0);
2929 New_Line;
2930 Text ('IS',0);
2931 Text ('BEGIN',0);
2932 New_Line;
2933
2934 IF l_fkey_tbl.COUNT = 0 THEN
2935
2936 PUT_LINE(' No related objects exist');
2937
2938 ELSIF l_fkey_tbl.COUNT <> 0 then
2939
2940 j:= 0;
2941
2942 FOR J in 0..l_fkey_tbl.COUNT -1 LOOP
2943
2944 l_uk_database_object_name := l_fkey_tbl(j).uk_database_object_name;
2945 l_uk_name := l_fkey_tbl(j).unique_key_name;
2946 l_fk_name := l_fkey_tbl(j).foreign_key_name;
2947 l_entity_code := l_fkey_tbl(j).fk_entity_code;
2948 l_related_pkg_name := l_app_short_name||'_'||l_entity_code||'_Def'||'_Util';
2949
2950 l_ukey_attr_tbl.DELETE;
2951 U :=0;
2952 -- Get all the unique key columns e.g. ORGANIZATION_ID on OE_AK_SOLD_TO_ORGS_V
2953 FOR ukey_cols in UKEY_COL (l_uk_name ,p_application_id , l_uk_database_object_name )
2954 LOOP
2955 l_ukey_attr_tbl(u).attribute_code := ukey_cols.attribute_code;
2956 l_ukey_attr_tbl(u).column_name := ukey_cols.column_name;
2957 l_ukey_attr_tbl(u).data_type := ukey_cols.data_type;
2958 u := u+1;
2959 END LOOP;
2960
2961 IF l_ukey_attr_tbl.COUNT =0 THEN
2962 -- THere are no unique keys defined in the AK for this entity. so exit
2963 PUT_LINE(' ERROR: No unique key columns on the key:'
2964 ||l_uk_name);
2965 RAISE FND_API.G_EXC_ERROR;
2966 END IF;
2967
2968 l_fkey_attr_tbl.DELETE;
2969 F :=0;
2970 -- Get all the foreign key columns e.g. SOLD_TO_ORG_ID on OE_AK_ORDER_LINES_V
2971 FOR fkey_cols in FKEY_COL (l_fk_name ,p_application_id ,l_database_object_name )
2972 LOOP
2973 l_fkey_attr_tbl(f).attribute_code := fkey_cols.attribute_code;
2974 l_fkey_attr_tbl(f).column_name := fkey_cols.column_name;
2975 l_fkey_attr_tbl(f).data_type := fkey_cols.data_type;
2976 f := f+1;
2977 END LOOP;
2978
2979 IF l_fkey_attr_tbl.COUNT = 0 THEN
2980 PUT_LINE(' ERROR: No foreign key columns on the key:'
2981 ||l_fk_name);
2982 RAISE FND_API.G_EXC_ERROR;
2983 END IF;
2984
2985 if j=0 then
2986 Text (' IF (p_foreign_database_object_name = '||''''
2987 ||l_uk_database_object_name||''''||') THEN',0);
2988 else
2989 Text (' ELSIF (p_foreign_database_object_name = '||''''
2990 ||l_uk_database_object_name||''''||') THEN',0);
2991 end if;
2992
2993 IF l_ukey_attr_tbl.COUNT = 1 THEN
2994
2995 l_uk_attribute := l_ukey_attr_tbl(0).attribute_code;
2996 l_fk_attribute := l_fkey_attr_tbl(0).attribute_code;
2997 l_uk_column := l_ukey_attr_tbl(0).column_name;
2998 l_fk_column := l_fkey_attr_tbl(0).column_name;
2999 IF l_fkey_attr_tbl(0).data_type = 'NUMBER' THEN
3000 l_missing_data_str := 'FND_API.G_MISS_NUM';
3001 ELSIF l_fkey_attr_tbl(0).data_type = 'VARCHAR2' THEN
3002 l_missing_data_str := 'FND_API.G_MISS_CHAR';
3003 ELSIF l_fkey_attr_tbl(0).data_type = 'DATE' THEN
3004 l_missing_data_str := 'FND_API.G_MISS_DATE';
3005 END IF;
3006 Text('IF NVL(p_record.'||l_fk_column||','||l_missing_data_str||') = '
3007 ||l_missing_data_str||' THEN ',2);
3008 Text('RETURN NULL;',3);
3009 Text('END IF;',2);
3010 Text('IF '||l_related_pkg_name||'.Sync_'||l_entity_code||'_Cache',2);
3011 Text('(p_'||l_uk_column||' => p_record.'||l_fk_column||') = 1 then ',3);
3012 Text('RETURN '||l_related_pkg_name||'.Get_Attr_Val_Date(p_foreign_attr_code,'
3013 ||l_related_pkg_name||'.g_cached_record); ',2);
3014 Text('END IF;');
3015
3016 ELSE
3017
3018 U:= 0;
3019 FOR U in 0..l_ukey_attr_tbl.COUNT -1 LOOP
3020
3021 l_fk_attribute := l_fkey_attr_tbl(U).attribute_code;
3022 l_fk_column := l_fkey_attr_tbl(U).column_name;
3023 IF l_fkey_attr_tbl(U).data_type = 'NUMBER' THEN
3024 l_missing_data_str := 'FND_API.G_MISS_NUM';
3025 ELSIF l_fkey_attr_tbl(U).data_type = 'VARCHAR2' THEN
3026 l_missing_data_str := 'FND_API.G_MISS_CHAR';
3027 ELSIF l_fkey_attr_tbl(U).data_type = 'DATE' THEN
3028 l_missing_data_str := 'FND_API.G_MISS_DATE';
3029 END IF;
3030
3031 if u = 0 then
3032 Text('IF ( NVL(p_record.'||l_fk_column||','||l_missing_data_str||') = '
3033 ||l_missing_data_str||' )',2);
3034 else
3035 Text(' OR ( NVL(p_record.'||l_fk_column||','||l_missing_data_str||') = '
3036 ||l_missing_data_str||' )',2);
3037 end if;
3038 if u = l_ukey_attr_tbl.COUNT -1 then
3039 Text('THEN',2);
3040 Text('RETURN NULL;',2);
3041 Text('END IF;',2);
3042 end if;
3043
3044 END LOOP;
3045
3046 FOR U in 0..l_ukey_attr_tbl.COUNT -1 LOOP
3047
3048 l_uk_attribute := l_ukey_attr_tbl(U).attribute_code;
3049 l_fk_attribute := l_fkey_attr_tbl(U).attribute_code;
3050 l_uk_column := l_ukey_attr_tbl(U).column_name;
3051 l_fk_column := l_fkey_attr_tbl(U).column_name;
3052
3053 if u = 0 then
3054 Text('IF '||l_related_pkg_name||'.Sync_'||l_entity_code||'_Cache(p_'
3055 ||l_uk_column||' => p_record.'||l_fk_column||',',2);
3056 elsif u = l_ukey_attr_tbl.COUNT -1 then
3057 Text('p_'||l_uk_column||' => p_record.'||l_fk_column||') =1 then ',2);
3058 Text('RETURN '||l_related_pkg_name||'.Get_Attr_Val_Date(p_foreign_attr_code,'
3059 ||l_related_pkg_name||'.g_cached_record); ',2);
3060 Text('END IF;');
3061 else
3062 Text('p_'||l_uk_column||' => p_record.'||l_fk_column||', ',2);
3063 end if;
3064 END LOOP;
3065
3066 END IF;
3067
3068 if j = l_fkey_tbl.COUNT -1 then
3069 Text ('END IF;',0);
3070 end if;
3071
3072 New_Line;
3073 END LOOP;
3074
3075 end if;
3076
3077 Text (' RETURN NULL;',2);
3078 Text ('END Get_Foreign_Attr_Val_Date;',0);
3079 New_Line;
3080
3081 -------------------------------------------------------------------------------
3082
3083 New_Line;
3084 -- Function to get the current condition index
3085 -- Validate_defaulting_condition calls this function to get the condition
3086 -- AND cache it .
3087
3088 PUT_LINE( ' Create FUNCTION Get_Condition_Index_In_Cache');
3089 Text ('FUNCTION Get_Condition_Index_In_Cache',0);
3090 Parameter('condition_id ','IN', 'NUMBER',0,30,TRUE);
3091 Text (') RETURN NUMBER',0);
3092 Text ('IS ',0);
3093 Text ('BEGIN ',0);
3094 New_Line;
3095 Text ('FOR i in 0..g_conditions_tbl_cache.COUNT -1 LOOP ',1);
3096 Text ('if (g_conditions_tbl_cache(i).condition_id = p_condition_id ) then ',2);
3097 Text ('RETURN i; ',3);
3098 Text ('END IF; ',2);
3099 Text ('END LOOP; ',1);
3100 Text ('RETURN -1; ',1);
3101 Text ('END Get_Condition_Index_In_Cache; ',0);
3102
3103 -- End Get_Condition_in_cache
3104 -------------------------------------------------------------------------------
3105
3106 -- FUnction to validate Defaulting Condition
3107 --
3108 PUT_LINE( ' Create FUNCTION Validate_Defaulting_Condition');
3109 Text ('FUNCTION Validate_Defaulting_Condition',0);
3110 Parameter('condition_id ','IN','NUMBER',0,30,TRUE);
3111 Parameter(LOWER(p_entity_code)||'_rec','IN',
3112 l_database_object_name||'%ROWTYPE ',0);
3113 Text (') RETURN BOOLEAN',0);
3114 Text ('IS ',0);
3115
3116 -- CURSOR to get all the defaulting condition elements for the condition
3117
3118 Text ('CURSOR CONDNS IS ',1);
3119 Text ('SELECT condition_id,group_number,attribute_code,',1);
3120 Text ('value_op,value_string',2);
3121 Text ('FROM OE_DEF_CONDN_ELEMS',1);
3122 Text ('WHERE condition_id = p_condition_id',1);
3123 Text ('ORDER BY group_number;',1);
3124 New_Line;
3125 Text ('I NUMBER; ',1);
3126 Text ('l_column_value VARCHAR2(255); ',1);
3127 Text ('l_start_index NUMBER; ',1);
3128 Text ('l_stop_index NUMBER ; ',1);
3129 Text ('l_curr_group NUMBER; ',1);
3130 Text ('l_group_result BOOLEAN; ',1);
3131 Text ('l_element_result BOOLEAN; ',1);
3132 Text ('BEGIN ',0);
3133 New_Line;
3134
3135 -- Get the condition for the first time AND cache it.
3136
3137 Assign ('l_start_index','Get_Condition_Index_In_Cache(p_condition_id)',1,-1);
3138 Text ('IF (l_start_index = -1) THEN ',0);
3139 Text ('l_stop_index := g_conditions_tbl_cache.COUNT; ',1);
3140 Assign ('l_start_index','l_stop_index',1,-1);
3141 Assign ('i','l_start_index',1,-1);
3142 Text ('FOR condns_rec IN CONDNS LOOP ',1);
3143 Assign ('g_conditions_tbl_cache(i).condition_id','condns_rec.condition_id',2,-1);
3144 Assign ('g_conditions_tbl_cache(i).group_number','condns_rec.group_number',2,-1);
3145 Assign ('g_conditions_tbl_cache(i).attribute_code','condns_rec.attribute_code',2,-1);
3146 Assign ('g_conditions_tbl_cache(i).value_op','condns_rec.value_op',2,-1);
3147 Assign ('g_conditions_tbl_cache(i).value_string','condns_rec.value_string',2,-1);
3148 Assign ('i','i+1',1,-1);
3149 Text ('END LOOP; ',1);
3150 --Text ('l_stop_index := i; ',1);
3151
3152
3153 -- There is no condition, hence the condition is not valid
3154
3155 Text ('IF (i = l_start_index) THEN ',0);
3156 Text ('Return FALSE; ',2);
3157 Text ('END IF; ',1);
3158 Text ('END IF; ',1);
3159 New_Line;
3160 New_Line;
3161
3162 -- Evaluate the condition now
3163
3164 Assign ('i','0',1,-1);
3165 Assign ('l_curr_group','g_conditions_tbl_cache(l_start_index).group_number',1,-1);
3166 Assign ('l_group_result','TRUE',1,-1);
3167 Assign ('l_element_result','FALSE',1,-1);
3168 New_Line;
3169
3170 --loop till all the conditions are done.
3171
3172 Text (' IF g_conditions_tbl_cache.COUNT <> 0 then ',0);
3173 Text ('FOR J in l_start_index ..g_conditions_tbl_cache.COUNT -1 LOOP ',0);
3174 Text ('IF (g_conditions_tbl_cache(j).condition_id <> p_condition_id) THEN',1);
3175 Text ('EXIT;',2);
3176 Text ('END IF;',1);
3177 New_Line;
3178
3179 -- Is there a group change?
3180 -- Every group is ORed. If one group evaluates to true we can exit.
3181
3182 Text ('IF (l_curr_group <> g_conditions_tbl_cache(j).group_number) THEN',1);
3183 Text ('IF (l_group_result = TRUE) THEN',2);
3184 Text ('EXIT;',3);
3185 Text ('ELSE',2);
3186 Text ('l_group_result := TRUE;',3);
3187 Text ('END IF;',2);
3188 Text ('END IF;',1);
3189 New_Line;
3190 Text ('l_element_result := '||l_app_short_name||'_Def_Util.Validate_Value(g_conditions_tbl_cache(j).value_string,',1);
3191 Text('g_conditions_tbl_cache(j).value_op,Get_Attr_Val_Varchar2(g_conditions_tbl_cache(j).attribute_code,p_'||LOWER(p_entity_code)||'_rec ));',1);
3192
3193 -- IF there is no group change, the results are ANDed
3194
3195 Text ('l_group_result := l_group_result AND l_element_result;',2);
3196 Text ('END LOOP;',0);
3197 Text ('ELSE',0);
3198 Text ('l_group_result := FALSE;',1);
3199 Text ('END IF;',1);
3200 Text ('RETURN l_group_result;',1);
3201 Text ('END Validate_Defaulting_Condition;',0);
3202 New_Line;
3203 -------------------------------------------------------------------------------
3204
3205
3206 -- Function to Update_Attr_Rules_Cache
3207
3208 PUT_LINE( ' Create PROCEDURE Update_Attr_Rules_Cache');
3209 New_Line;
3210 Text('PROCEDURE Update_Attr_Rules_Cache',0);
3211 Text(' ( p_condn_index IN NUMBER',0);
3212 Text(' )',0);
3213 Text('IS',0);
3214 Text('l_index NUMBER := 0;',0);
3215 Text('l_start_index NUMBER := 0;',0);
3216 Text('l_attribute_code VARCHAR2(30);',0);
3217 Text('l_condition_id NUMBER;',0);
3218 Text(' CURSOR DEFSRC IS SELECT',0);
3219 Text(' R.SEQUENCE_NO,',0);
3220 Text(' R.SRC_TYPE,',0);
3221 Text(' R.SRC_ATTRIBUTE_CODE,',0);
3222 Text(' R.SRC_DATABASE_OBJECT_NAME,',0);
3223 Text(' R.SRC_PARAMETER_NAME,',0);
3224 Text(' R.SRC_SYSTEM_VARIABLE_EXPR,',0);
3225 Text(' R.SRC_PROFILE_OPTION,',0);
3226 Text(' R.SRC_API_PKG||''.''||R.SRC_API_FN SRC_API_NAME,',0);
3227 Text(' R.SRC_CONSTANT_VALUE,',0);
3228 Text(' R.SRC_SEQUENCE_NAME',0);
3229 Text(' FROM OE_DEF_ATTR_DEF_RULES R, OE_DEF_ATTR_CONDNS C',0);
3230 Text(' WHERE R.database_object_name = g_database_object_name',0);
3231 Text(' AND R.attribute_code = l_attribute_code',0);
3232 Text(' AND C.database_object_name = g_database_object_name',0);
3233 Text(' AND C.attribute_code = l_attribute_code',0);
3234 Text(' AND R.attr_def_condition_id = C.attr_def_condition_id',0);
3235 Text(' AND C.CONDITION_ID = l_condition_id',0);
3236 Text(' AND C.ENABLED_FLAG = ''Y''',0);
3237 Text(' ORDER BY SEQUENCE_NO;',0);
3238 Text('BEGIN',0);
3239
3240 New_Line;
3241 Text(' l_attribute_code := g_attr_condns_cache(p_condn_index).attribute_code;',0);
3242 Text(' l_condition_id := g_attr_condns_cache(p_condn_index).condition_id;',0);
3243 Text(' l_start_index := g_attr_rules_cache.COUNT + 1;',0);
3244
3245 New_Line;
3246 Text(' FOR DEFSRC_rec IN DEFSRC LOOP',0);
3247 Text(' l_index := g_attr_rules_cache.COUNT + 1; ',0);
3248 Text(' g_attr_rules_cache(l_index).SRC_TYPE ',0);
3249 Text(' := DEFSRC_rec.SRC_TYPE; ',0);
3250 Text(' g_attr_rules_cache(l_index).SRC_ATTRIBUTE_CODE ',0);
3251 Text(' := DEFSRC_rec.SRC_ATTRIBUTE_CODE; ',0);
3252 Text(' g_attr_rules_cache(l_index).SRC_DATABASE_OBJECT_NAME ',0);
3253 Text(' := DEFSRC_rec.SRC_DATABASE_OBJECT_NAME; ',0);
3254 Text(' g_attr_rules_cache(l_index).SRC_PARAMETER_NAME ',0);
3255 Text(' := DEFSRC_rec.SRC_PARAMETER_NAME; ',0);
3256 Text(' g_attr_rules_cache(l_index).SRC_SYSTEM_VARIABLE_EXPR ',0);
3257 Text(' := DEFSRC_rec.SRC_SYSTEM_VARIABLE_EXPR; ',0);
3258 Text(' g_attr_rules_cache(l_index).SRC_PROFILE_OPTION',0);
3259 Text(' := DEFSRC_rec.SRC_PROFILE_OPTION; ',0);
3260 Text(' g_attr_rules_cache(l_index).SRC_API_NAME',0);
3261 Text(' := DEFSRC_rec.SRC_API_NAME; ',0);
3262 Text(' g_attr_rules_cache(l_index).SRC_CONSTANT_VALUE',0);
3263 Text(' := DEFSRC_rec.SRC_CONSTANT_VALUE; ',0);
3264 Text(' g_attr_rules_cache(l_index).SRC_SEQUENCE_NAME',0);
3265 Text(' := DEFSRC_rec.SRC_SEQUENCE_NAME; ',0);
3266 Text(' END LOOP;',0);
3267 New_Line;
3268
3269 Text(' IF l_index > 0 THEN',0);
3270 Text(' g_attr_condns_cache(p_condn_index).rules_start_index := l_start_index;',0);
3271 Text(' g_attr_condns_cache(p_condn_index).rules_stop_index := l_index;',0);
3272 Text(' ELSE',0);
3273 Text(' g_attr_condns_cache(p_condn_index).rules_start_index := -1;',0);
3274 Text(' END IF;',0);
3275 New_Line;
3276
3277 Text('EXCEPTION',0);
3278 Text(' WHEN OTHERS THEN',0);
3279 Text(' IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)',0);
3280 Text(' THEN',0);
3281 Text(' OE_MSG_PUB.Add_Exc_Msg',0);
3282 Text(' ( G_PKG_NAME ,',0);
3283 Text(' ''Update_Attr_Rules_Cache: ''||l_attribute_code',0);
3284 Text(' );',0);
3285 Text(' END IF;',0);
3286 Text(' RAISE FND_API.G_EXC_UNEXPECTED_ERROR;',0);
3287 Text('END Update_Attr_Rules_Cache;',0);
3288 New_Line;
3289
3290 /*-------------------------------------------------------------------------------*/
3291 -- PROCEDURE Get_Valid_Defaulting_Rules
3292 -- This procedure is called FROM each defaulting attribute handler function
3293 -- to retrieve the rules associated with that attribute for the defaulting
3294 -- conditions that are valid for the current entity record
3295 -- The indexes of the rules in the cache are returned in the rules_start_index_Tbl
3296 -- AND rules_stop_index_tbl
3297
3298 PUT_LINE( ' Create PROCEDURE Get_Valid_Defaulting_Rules');
3299 New_Line;
3300 Text ('PROCEDURE Get_Valid_Defaulting_Rules',0);
3301 Parameter('attr_code ','IN','VARCHAR2',0,30,TRUE);
3302 Parameter('attr_id ','IN','NUMBER',0,30,FALSE);
3303 Parameter(LOWER(p_entity_code)||'_rec','IN',
3304 l_database_object_name||'%ROWTYPE',0);
3305 Parameter('rules_start_index_tbl ','OUT','OE_GLOBALS.NUMBER_TBL_Type',0,30,FALSE);
3306 Parameter('rules_stop_index_tbl ','OUT','OE_GLOBALS.NUMBER_TBL_Type',0,30,FALSE);
3307 Text (') IS',0);
3308 Text('l_condn_index NUMBER; ',0);
3309 Text('l_index NUMBER := 0; ',0);
3310 Text('l_valid_condn_index_tbl OE_GLOBALS.Number_TBL_Type; ',0);
3311 Text('condns_cached BOOLEAN := FALSE;',0);
3312 Text('num_attr_condns NUMBER := 0;',0);
3313 Text('CURSOR ATTRC IS ',0);
3314 Text(' SELECT condition_id ',0);
3315 Text(' FROM OE_DEF_ATTR_CONDNS ',0);
3316 Text(' WHERE attribute_code = p_attr_code ',0);
3317 Text(' AND database_object_name = g_database_object_name ',0);
3318 Text(' AND enabled_flag = ''Y''',0);
3319 Text(' ORDER BY precedence;',0);
3320 Text('BEGIN ',0);
3321 New_Line;
3322
3323 Text(' l_condn_index := p_attr_id * ONT_Def_Util.G_MAX_ATTR_CONDNS;',0);
3324 New_Line;
3325
3326 Text(' -- Check in the cache',0);
3327 Text(' WHILE g_attr_condns_cache.EXISTS(l_condn_index) LOOP',0);
3328 Text(' condns_cached := TRUE;',0);
3329 Text(' IF g_attr_condns_cache(l_condn_index).conditions_defined = ''N'' THEN',0);
3330 Text(' EXIT;',0);
3331 Text(' ELSE',0);
3332 Text(' IF (g_attr_condns_cache(l_condn_index).condition_id = 0 OR',0);
3333 Text(' Validate_Defaulting_Condition',0);
3334 Text(' (g_attr_condns_cache(l_condn_index).condition_id,p_'
3335 ||lower(p_entity_code)||'_rec)= TRUE) THEN ',0);
3336 Text(' l_index := l_index + 1;',0);
3337 Text(' l_valid_condn_index_tbl(l_index) := l_condn_index;',0);
3338 Text(' END IF;',0);
3339 Text(' END IF;',0);
3340 Text(' l_condn_index := l_condn_index + 1;',0);
3341 Text(' END LOOP;',0);
3342 New_Line;
3343
3344 Text(' -- If the conditions were cached for this attribute, ',0);
3345 Text(' -- then return rules for valid conditions',0);
3346 Text(' IF condns_cached THEN',0);
3347 New_Line;
3348 Text(' GOTO Return_Rules;',0);
3349 New_Line;
3350 Text(' -- If the conditions were NOT cached for this attribute,',0);
3351 Text(' -- then cache them AND get the conditions that are valid',0);
3352 Text(' -- for the current record',0);
3353 Text(' ELSE',0);
3354 Text(' FOR c_rec IN ATTRC LOOP ',0);
3355 Text(' -- Put it in the cache',0);
3356 Text(' g_attr_condns_cache(l_condn_index).attribute_code',0);
3357 Text(' := p_attr_code;',0);
3358 Text(' g_attr_condns_cache(l_condn_index).condition_id',0);
3359 Text(' := c_rec.condition_id;',0);
3360 Text(' g_attr_condns_cache(l_condn_index).conditions_defined',0);
3361 Text(' := ''Y'';',0);
3362 Text(' IF (c_rec.condition_id = 0 OR',0);
3363 Text(' Validate_Defaulting_Condition',0);
3364 Text(' (c_rec.condition_id,p_'||lower(p_entity_code)||'_rec)= TRUE) THEN ',0);
3365 Text(' l_index := l_index + 1;',0);
3366 Text(' l_valid_condn_index_tbl(l_index) := l_condn_index;',0);
3367 Text(' END IF;',0);
3368 Text(' l_condn_index := l_condn_index + 1;',0);
3369 Text(' num_attr_condns := num_attr_condns + 1;',0);
3370 Text(' END LOOP;',0);
3371 New_Line;
3372
3373 Text(' -- No defaulting conditions defined for this attribute,',0);
3374 Text(' -- insert a new record in the cache with conditions_defined = ''N''',0);
3375 Text(' IF num_attr_condns = 0 THEN',0);
3376 Text(' g_attr_condns_cache(l_condn_index).attribute_code',0);
3377 Text(' := p_attr_code;',0);
3378 Text(' g_attr_condns_cache(l_condn_index).conditions_defined',0);
3379 Text(' := ''N'';',0);
3380 Text(' END IF;',0);
3381 New_Line;
3382
3383 Text(' END IF;',0);
3384 New_Line;
3385
3386 Text(' <<Return_Rules>>',0);
3387 Text('FOR I IN 1..l_index LOOP',0);
3388 -- Cache the rules if not already cached for this condition
3389 Text(' IF g_attr_condns_cache(l_valid_condn_index_tbl(I)).rules_start_index IS NULL THEN',0);
3390 Text(' Update_Attr_Rules_Cache(l_valid_condn_index_tbl(I));',0);
3391 Text(' END IF;',0);
3392 -- Populate rules for this condition
3393 Text(' x_rules_start_index_tbl(I) := g_attr_condns_cache(l_valid_condn_index_tbl(I)).rules_start_index;',0);
3394 Text(' x_rules_stop_index_tbl(I) := g_attr_condns_cache(l_valid_condn_index_tbl(I)).rules_stop_index;',0);
3395 Text('END LOOP;',0);
3396 New_Line;
3397
3398 Text('EXCEPTION',0);
3399 Text(' WHEN OTHERS THEN',0);
3400 Text(' IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)',0);
3401 Text(' THEN',0);
3402 Text(' OE_MSG_PUB.Add_Exc_Msg',0);
3403 Text(' ( G_PKG_NAME ,',0);
3404 Text(' ''Get_Valid_Defaulting_Rules :''||p_attr_code',0);
3405 Text(' );',0);
3406 Text(' END IF;',0);
3407 Text(' RAISE FND_API.G_EXC_UNEXPECTED_ERROR;',0);
3408 Text('END Get_Valid_Defaulting_Rules;',0);
3409 New_Line;
3410 New_Line;
3411
3412 <<END_OF_BODY>>
3413
3414 Pkg_End(l_pkg_name,'BODY');
3415
3416 END Create_Entity_Def_Util_handler;
3417
3418
3419 --------------------------------------------------------------------
3420 PROCEDURE Create_OE_Def_Hdlr
3421 (
3422 p_application_id IN VARCHAR2 ,
3423 p_database_object_name IN VARCHAR2 ,
3424 p_entity_code IN VARCHAR2
3425 )
3426 IS
3427
3428 -- Table declarations
3429 TYPE obj_attr_tbl_type IS TABLE OF OE_DEF_AK_ATTR_EXT_V%ROWTYPE
3430 INDEX BY BINARY_INTEGER;
3431 l_data_attr_tbl obj_attr_tbl_type;
3432
3433 -- variable declarations
3434
3435 l_entity_code VARCHAR2(15);
3436 l_app_short_name VARCHAR2(3);
3437 l_pkg_name VARCHAR2(30);
3438 l_attribute_code VARCHAR2(30);
3439 l_column_name VARCHAR2(30);
3440 l_database_object_name VARCHAR2(30);
3441 J NUMBER;
3442
3443 CURSOR APP
3444 is
3445 SELECT substr(rtrim(APPLICATION_SHORT_NAME),1,3)
3446 FROM fnd_application
3447 WHERE application_id = p_application_id;
3448
3449 CURSOR OASS(p_database_object_name varchar2)
3450 is
3451 SELECT oeatt.attribute_code, akatt.column_name
3452 FROM OE_AK_OBJ_ATTR_EXT oeatt
3453 , AK_OBJECT_ATTRIBUTES akatt
3454 WHERE oeatt.database_object_name = p_database_object_name
3455 AND oeatt.attribute_application_id=p_application_id
3456 AND oeatt.attribute_code = akatt.attribute_code
3457 AND oeatt.database_object_name = akatt.database_object_name
3458 AND oeatt.attribute_application_id= akatt.attribute_application_id
3459 ORDER BY akatt.column_name;
3460 -- AND NVL(data_storage_type,OE_DEF_UTIL.NONE)='3RD NORMAL';
3461
3462 --
3463 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3464 --
3465 BEGIN
3466
3467 OPEN APP;
3468 FETCH APP INTO l_app_short_name;
3469 CLOSE APP;
3470
3471 l_database_object_name := p_database_object_name;
3472 -- Generate the util package
3473 l_pkg_name := 'OE_Default_'||INITCAP(p_entity_code);
3474
3475 PUT_LINE( 'Create OE entity defaulting package ' ||l_pkg_name);
3476
3477
3478 -- Write out the header to the Spec file.
3479
3480 Pkg_Header( p_pkg_name => l_pkg_name,
3481 p_pkg_type => 'SPEC');
3482
3483 --
3484 Text ('PROCEDURE Attributes',0);
3485 Parameter(INITCAP(p_entity_code)||'_rec','IN',
3486 'OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30,TRUE);
3487 Parameter('old_'||INITCAP(p_entity_code)||'_rec','IN',
3488 'OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30);
3489 Parameter('itteration ','IN','NUMBER := 1',0,30);
3490 Parameter(INITCAP(p_entity_code)||'_rec','OUT',
3491 'OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30);
3492 Text (') ;',0);
3493
3494 New_Line;
3495
3496 Text ('PROCEDURE Copy_API_Rec_To_Rowtype_Rec',0);
3497 Parameter('api_rec','IN',
3498 'OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30,TRUE);
3499 Parameter('rowtype_rec','OUT',
3500 l_database_object_name||'%ROWTYPE ',0,30);
3501 Text (') ;',0);
3502 New_Line;
3503 Text ('PROCEDURE Copy_Rowtype_Rec_To_API_Rec',0);
3504 Parameter('rowtype_rec','IN',
3505 l_database_object_name||'%ROWTYPE ',0,30,TRUE);
3506 Parameter('api_rec','OUT',
3507 'OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30);
3508 Text (') ;',0);
3509
3510 Pkg_End( p_pkg_name => l_pkg_name,
3511 p_pkg_type => 'SPEC');
3512
3513
3514 Pkg_Header( p_pkg_name => l_pkg_name,
3515 p_pkg_type => 'BODY');
3516
3517 Text ('PROCEDURE Attributes',0);
3518 Parameter(INITCAP(p_entity_code)||'_rec','IN',
3519 'OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30,TRUE);
3520 Parameter('old_'||INITCAP(p_entity_code)||'_rec','IN',
3521 'OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30);
3522 Parameter('itteration ','IN','NUMBER := 1',0,30);
3523 Parameter(INITCAP(p_entity_code)||'_rec','OUT',
3524 'OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30);
3525 Text (') ',0);
3526
3527 New_Line;
3528 Text ('IS ',0);
3529 Text ('l_in_rec '||p_database_object_name||'%ROWTYPE;',1);
3530 Text ('l_in_old_rec '||p_database_object_name||'%ROWTYPE;',1);
3531 Text ('l_out_rec '||p_database_object_name||'%ROWTYPE;',1);
3532 Text ('BEGIN ',0);
3533 Comment ('Due to incompatibilities in the record type structure',0);
3534 Comment ('copy the data to a rowtype record format',0);
3535 Text (' Copy_API_Rec_To_Rowtype_Rec(p_api_rec => p_'||LOWER(p_entity_code)||'_rec,',2);
3536 Text (' x_rowtype_rec => l_in_rec);',4);
3537 Text (' Copy_API_Rec_To_Rowtype_Rec(p_api_rec => p_old_'||LOWER(p_entity_code)||'_rec,',2);
3538 Text (' x_rowtype_rec => l_in_old_rec);',4);
3539 Text (' x_'||lower(p_entity_code)||'_rec := p_'||lower(p_entity_code)||'_rec;',2);
3540 Comment ('call the default handler framework to default the missing attributes',0);
3541 Text (l_app_short_name||'_'||p_entity_code||'_Def_Hdlr.Default_Record(l_in_rec, l_in_old_rec, p_itteration,l_out_rec);',2);
3542 Comment ('copy the data back to a format that is compatible with the API architecture',0);
3543 Text ('Copy_RowType_Rec_to_API_Rec(l_out_rec,x_'||LOWER(p_entity_code)||'_rec);',2);
3544 Text ('x_'||LOWER(p_entity_code)||'_rec.db_flag := p_'||LOWER(p_entity_code)||'_rec.db_flag;',0);
3545 Text ('x_'||LOWER(p_entity_code)||'_rec.return_status := p_'||LOWER(p_entity_code)||'_rec.return_status;',0);
3546 Text ('x_'||LOWER(p_entity_code)||'_rec.operation := p_'||LOWER(p_entity_code)||'_rec.operation;',0);
3547 Text ('return;',2);
3548 Text ('End Attributes;',0);
3549 New_Line;
3550 New_Line;
3551
3552
3553 Comment ('Procedure to copy the data to a rowtype record format so that we can call the Default handler package.',0);
3554 Text ('PROCEDURE Copy_API_Rec_To_Rowtype_Rec',0);
3555 Parameter('api_rec','IN',
3556 'OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30,TRUE);
3557 Parameter('rowtype_rec','OUT',
3558 p_database_object_name||'%ROWTYPE ',0,30);
3559 Text (') ',0);
3560
3561 New_Line;
3562 Text ('IS ',0);
3563 Text ('BEGIN ',0);
3564
3565
3566 j :=0;
3567
3568 FOR l_attr_rec in OASS(p_database_object_name )
3569 LOOP
3570
3571 l_data_attr_tbl(j).attribute_code := l_attr_rec.attribute_code;
3572 l_data_attr_tbl(j).column_name := l_attr_rec.column_name;
3573
3574 j := j+1;
3575
3576 END LOOP;
3577
3578 FOR J IN 0..l_data_attr_tbl.COUNT -1 LOOP
3579
3580 l_attribute_code := l_data_attr_tbl(j).attribute_code ;
3581 l_column_name := l_data_attr_tbl(j).column_name ;
3582
3583 Text ('x_rowtype_rec.'||l_column_name||' := p_api_rec.'||l_column_name||';',1);
3584
3585 END LOOP;
3586 Text ('END Copy_API_Rec_To_Rowtype_Rec;',0);
3587 New_Line;
3588 New_Line;
3589
3590
3591 Text ('PROCEDURE Copy_Rowtype_Rec_To_API_Rec',0);
3592 Parameter('rowtype_rec','IN',
3593 p_database_object_name||'%ROWTYPE ',0,30,TRUE);
3594 Parameter('api_rec',' OUT',
3595 ' OE_Order_PUB.'||INITCAP(p_entity_code)||'_Rec_Type ',0,30);
3596 Text (') ',0);
3597
3598 New_Line;
3599 Text ('IS ',0);
3600 Text ('BEGIN ',0);
3601
3602
3603 j :=0;
3604
3605
3606 FOR J IN 0..l_data_attr_tbl.COUNT -1 LOOP
3607
3608 l_attribute_code := l_data_attr_tbl(j).attribute_code ;
3609 l_column_name := l_data_attr_tbl(j).column_name ;
3610
3611 Text ('x_api_rec.'||l_column_name||' := p_rowtype_rec.'||l_column_name||';',1);
3612
3613 END LOOP;
3614 Text ('END Copy_Rowtype_Rec_To_API_Rec;',0);
3615 New_Line;
3616
3617 Pkg_End( p_pkg_name => l_pkg_name,
3618 p_pkg_type => 'BODY');
3619
3620 PUT_LINE( 'Created '||l_pkg_name||' successfully');
3621
3622 EXCEPTION
3623
3624 WHEN OTHERS THEN
3625 PUT_LINE( 'ERROR when creating '||l_pkg_name||' :'||sqlerrm);
3626
3627 END Create_OE_Def_Hdlr ;
3628
3629 END OE_Defaulting_Fwk_PUB;