1 PACKAGE BODY HZ_BES_BO_GEN_PKG AS
2 /*$Header: ARHBESGB.pls 120.5 2006/06/28 07:11:20 smattegu noship $ */
3
4 --
5 -- Purpose:
6 -- To generate the Site specific package (HZ_BES_BO_SITE_UTIL_PKG) body
7 -- that satifies the need to check the completeness and event type
8 -- of the business object.
9 -- Note:
10 -- As the Completeness and Event Type checks are needed for only the
11 -- for PERSON, ORGANIZATION, PERSON CUSTOMER and ORGANIZATION CUSTOMER
12 -- business objects, any reference to BOs or All BOs implies, only the
13 -- above four business objects.
14 --
15
16 -- declaration of private global varibles
17 --------------------------------------
18 g_indent CONSTANT VARCHAR2(2) := ' ';
19
20 G_PER_BO_CODE CONSTANT VARCHAR2(20):= HZ_BES_BO_RAISE_PKG.G_PER_BO_CODE;
21 G_ORG_BO_CODE CONSTANT VARCHAR2(20):= HZ_BES_BO_RAISE_PKG.G_ORG_BO_CODE;
22 G_PER_CUST_BO_CODE CONSTANT VARCHAR2(20):= HZ_BES_BO_RAISE_PKG.G_PER_CUST_BO_CODE;
23 G_ORG_CUST_BO_CODE CONSTANT VARCHAR2(20):= HZ_BES_BO_RAISE_PKG.G_ORG_CUST_BO_CODE;
24
25 --------------------------------------
26 -- forward referencing of private procedures
27 /*
28 */
29
30 /*
31 **************************************************************************
32 helper procedures and functions that facilitate in generating a plsql pkg
33 **************************************************************************
34 */
35 ----------------------------------------------
36 /**
37 * Procedure to write a message to the out file
38 **/
39 ----------------------------------------------
40 PROCEDURE out(
41 message IN VARCHAR2,
42 newline IN BOOLEAN DEFAULT TRUE) IS
43 BEGIN
44 IF (newline) THEN
45 FND_FILE.put_line(fnd_file.output,message);
46 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
47 ELSE
48 FND_FILE.put(fnd_file.output,message);
49 END IF;
50 END out;
51 ----------------------------------------------
52 ----------------------------------------------
53 /**
54 * Procedure to write text to the log file
55 **/
56 ----------------------------------------------
57 PROCEDURE log(
58 message IN VARCHAR2,
59 newline IN BOOLEAN DEFAULT TRUE) IS
60 l_prefix VARCHAR2(20) := 'BES_BO_RAISE';
61 BEGIN
62 /*
63 FND_FILE.LOG = 1 - means log file
64 FND_FILE.LOG = 2 - means out file
65 */
66 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
67 hz_utility_v2pub.DEBUG (
68 p_message=>message,
69 p_prefix=>l_prefix,
70 p_msg_level=>fnd_log.level_procedure);
71 END IF ;
72
73 IF newline THEN
74 FND_FILE.put_line(FND_FILE.LOG,message);
75 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
76 ELSE
77 FND_FILE.put_line(FND_FILE.LOG,message);
78 END IF;
79 END log;
80 ----------------------------------------------
81 /**
82 * Procedure to write a message to the out and log files
83 **/
84 ----------------------------------------------
85 PROCEDURE outandlog(
86 message IN VARCHAR2,
87 newline IN BOOLEAN DEFAULT TRUE) IS
88 BEGIN
89 out(message, newline);
90 log(message, newline);
91 END outandlog;
92
93 ----------------------------------------------
94 /**
95 * procedure to fetch messages of the stack and log the error
96
97 ----------------------------------------------
98
99 PROCEDURE logerr IS
100 l_msg_data VARCHAR2(2000);
101 BEGIN
102 FND_MSG_PUB.Reset;
103 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
104 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
105 END LOOP;
106 -- FND_MSG_PUB.Delete_Msg;
107 END logerr;
108 **/
109 ----------------------------------------------
110 /**
111 * Function to fetch messages of the stack and log the error
112 * Also returns the error
113 **/
114 ----------------------------------------------
115 FUNCTION logerror RETURN VARCHAR2 IS
116 l_msg_data VARCHAR2(2000);
117 BEGIN
118 FND_MSG_PUB.Reset;
119
120 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
121 l_msg_data := l_msg_data || ' ' || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
122 END LOOP;
123 log(l_msg_data);
124 RETURN l_msg_data;
125 END logerror;
126
127 ----------------------------------------------
128 /*
129 this procedure takes a message_name and enters into the message stack
130 and writes into the log file also.
131 */
132 ----------------------------------------------
133
134 PROCEDURE mesglog(
135 p_message IN VARCHAR2,
136 p_tkn1_name IN VARCHAR2 DEFAULT NULL,
137 p_tkn1_val IN VARCHAR2 DEFAULT NULL,
138 p_tkn2_name IN VARCHAR2 DEFAULT NULL,
139 p_tkn2_val IN VARCHAR2 DEFAULT NULL
140 ) IS
141 BEGIN
142 FND_MESSAGE.SET_NAME('AR', p_message);
143 IF (p_tkn1_name IS NOT NULL) THEN
144 fnd_message.set_token(p_tkn1_name, p_tkn1_val);
145 END IF;
146 IF (p_tkn2_name IS NOT NULL) THEN
147 fnd_message.set_token(p_tkn2_name, p_tkn2_val);
148 END IF;
149 FND_MSG_PUB.ADD;
150 FND_MSG_PUB.Reset;
151
152 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
153 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
154 END LOOP;
155
156 END mesglog;
157
158 ----------------------------------------------
159 /*
160 this procedure takes a message_name and enters into the message stack
161 and writes into the out file also.
162 */
163 ----------------------------------------------
164
165 PROCEDURE mesgout(
166 p_message IN VARCHAR2,
167 p_tkn1_name IN VARCHAR2 DEFAULT NULL,
168 p_tkn1_val IN VARCHAR2 DEFAULT NULL,
169 p_tkn2_name IN VARCHAR2 DEFAULT NULL,
170 p_tkn2_val IN VARCHAR2 DEFAULT NULL
171 ) IS
172 BEGIN
173 FND_MESSAGE.SET_NAME('AR', p_message);
174 IF (p_tkn1_name IS NOT NULL) THEN
175 fnd_message.set_token(p_tkn1_name, p_tkn1_val);
176 END IF;
177 IF (p_tkn2_name IS NOT NULL) THEN
178 fnd_message.set_token(p_tkn2_name, p_tkn2_val);
179 END IF;
180 FND_MSG_PUB.ADD;
181 FND_MSG_PUB.Reset;
182
183 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
184 out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
185 END LOOP;
186
187 END mesgout;
188
189 ----------------------------------------------
190 /*
191 this procedure takes a message_name and enters into the message stack
192 and writes into the out and log file also.
193 */
194 ----------------------------------------------
195
196 PROCEDURE mesgoutlog(
197 p_message IN VARCHAR2,
198 p_tkn1_name IN VARCHAR2 DEFAULT NULL,
199 p_tkn1_val IN VARCHAR2 DEFAULT NULL,
200 p_tkn2_name IN VARCHAR2 DEFAULT NULL,
201 p_tkn2_val IN VARCHAR2 DEFAULT NULL
202 ) IS
203 BEGIN
204 FND_MESSAGE.SET_NAME('AR', p_message);
205 IF (p_tkn1_name IS NOT NULL) THEN
206 fnd_message.set_token(p_tkn1_name, p_tkn1_val);
207 END IF;
208 IF (p_tkn2_name IS NOT NULL) THEN
209 fnd_message.set_token(p_tkn2_name, p_tkn2_val);
210 END IF;
211 FND_MSG_PUB.ADD;
212 FND_MSG_PUB.Reset;
213
214 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
215 outandlog(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
216 END LOOP;
217
218 END mesgoutlog;
219 ---------------------------------------------------------------------
220
221 --------------------------------------
222 -- This would write a line in the buffer.
223 -- This would also introduce a new line char at the end of line.
224 PROCEDURE l(
225 str IN VARCHAR2
226 ) IS
227 BEGIN
228 HZ_GEN_PLSQL.add_line(str);
229 END l;
230 --------------------------------------
231 -- This would write a line preceeded by an indent and line ends with
232 -- a new line char.
233 PROCEDURE li(
234 str IN VARCHAR2
235 ) IS
236 BEGIN
237 HZ_GEN_PLSQL.add_line(g_indent||str);
238 END li;
239 --------------------------------------
240 -- This would write a line preceeded by two indentations and line ends with
241 -- a new line char.
242 PROCEDURE l2i(
243 str IN VARCHAR2
244 ) IS
245 BEGIN
246 HZ_GEN_PLSQL.add_line(g_indent||g_indent||str);
247 END l2i;
248 --------------------------------------
249
250 -- This would write the line in the buffer WITHOUT NEW LINE CHAR at
251 -- the end of line.
252 PROCEDURE ll(
253 str IN VARCHAR2
254 ) IS
255 BEGIN
256 HZ_GEN_PLSQL.add_line(str, false);
257 END ll;
258 --------------------------------------
259 -- This would write a line by preceeding with an indent and NO NEW LINE char
260 -- at the end.
261 PROCEDURE lli(
262 str IN VARCHAR2
263 ) IS
264 BEGIN
265 HZ_GEN_PLSQL.add_line(g_indent||str, false);
266 END lli;
267 --------------------------------------
268 -- This would write a line by preceeding with two indentations
269 -- and NO NEW LINE char at the end.
270 PROCEDURE ll2i(
271 str IN VARCHAR2
272 ) IS
273 BEGIN
274 HZ_GEN_PLSQL.add_line(g_indent||g_indent||str, false);
275 END ll2i;
276
277 /*
278 Procedure name: genPkgBdyHdr()
279 Scope: Internal
280 Purpose: This procedure writes the pckage header for
281 HZ_BES_BO_SITE_UTIL_PKG package body.
282 Called From: This package
283 Called By: genPkgBdyHdr()
284 Paramaters - brief desc of each parameter:
285 In:
286 Out:
287 In-Out:
288 */
289
290 PROCEDURE genPkgBdyHdr (
291 p_package_name IN VARCHAR2
292 ) IS
293 l_prefix VARCHAR2(15) := 'GENPKGHDR:';
294 l_schema_name VARCHAR2(30);
295 l_tmp NUMBER;
296
297 BEGIN -- gen_pkg_body
298 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
299 hz_utility_v2pub.DEBUG (
300 p_message=>'genPkgBdyHdr()+',
301 p_prefix=>l_prefix,
302 p_msg_level=>fnd_log.level_procedure);
303 END IF ;
304 -- for any reason if the
305 -- new a package body object package body exists and is invalid, the create or replace is not working.
306 -- then we may have to do the following:
307
308 BEGIN
309 -- get the schema name that owns the pkg bdy
310 SELECT ORACLE_USERNAME
311 INTO l_schema_name
312 FROM FND_ORACLE_USERID WHERE
313 READ_ONLY_FLAG = 'U';
314
315 -- get the schema name that owns the pkg bdy
316
317 SELECT 1
318 INTO l_tmp
319 FROM ALL_PLSQL_OBJECT_SETTINGS
320 WHERE NAME = 'HZ_BES_BO_SITE_UTIL_PKG'
321 AND TYPE = 'PACKAGE BODY' AND OWNER = l_schema_name ;
322
323 IF l_tmp = 1 THEN
324 execute immediate 'DROP PACKAGE BODY HZ_BES_BO_SITE_UTIL_PKG';
325 END IF;
326 EXCEPTION
327 WHEN NO_DATA_FOUND THEN
328 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
329 hz_utility_v2pub.DEBUG (
330 p_message=>'pkg body does not exist.',
331 p_prefix=>l_prefix,
332 p_msg_level=>fnd_log.level_procedure);
333 END IF ;
334 WHEN OTHERS THEN
335 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
336 hz_utility_v2pub.DEBUG (
337 p_message=>sqlerrm,
338 p_prefix=>l_prefix,
339 p_msg_level=>fnd_log.level_procedure);
340 END IF ;
341 END;
342 HZ_GEN_PLSQL.new(p_package_name, 'PACKAGE BODY');
343 l('CREATE OR REPLACE PACKAGE BODY '||p_package_name||' AS');
344 l('');
345 l('/*=======================================================================+');
346 l(' | Copyright (c) 2006 Oracle Corporation Redwood Shores, California, USA|');
347 l(' | All rights reserved. |');
348 l(' +=======================================================================+');
349 l(' | NAME '||p_package_name);
350 l(' |');
351 l(' | DESCRIPTION');
355 l(' |');
352 l(' | This package body is generated by HZ_BES_GEN_PKG. ');
353 l(' | This package contains site specific completeness check and event type ');
354 l(' | check for PERSON, ORG, PERSON CUSTOMER and ORG CUSTOMER Business objects');
356 l(' | HISTORY');
357 l(' | '||TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS')||' Generated.');
358 l(' |');
359 l(' *=======================================================================*/');
360 l(' ');
361
362 if fnd_log.level_procedure>=fnd_log.g_current_runtime_level then
363 hz_utility_v2pub.debug(p_message=>'genPkgBdyHdr()-',
364 p_prefix=>l_prefix,
365 p_msg_level=>fnd_log.level_procedure);
366 end if;
367
368 END genPkgBdyHdr;
369 --------------------------------------
370 PROCEDURE genPkgHdr (
371 p_package_name IN VARCHAR2
372 ) IS
373 l_prefix VARCHAR2(15) := 'GENPKGHDR:';
374 l_schema_name VARCHAR2(30);
375 l_tmp NUMBER;
376
377 BEGIN -- gen_pkg_hdr
378 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
379 hz_utility_v2pub.DEBUG (
380 p_message=>'genPkgHdr()+',
381 p_prefix=>l_prefix,
382 p_msg_level=>fnd_log.level_procedure);
383 END IF ;
384
385 HZ_GEN_PLSQL.new(p_package_name, 'PACKAGE');
386 l('CREATE OR REPLACE PACKAGE '||p_package_name||' AS');
387 l('');
388 l('/*=======================================================================+');
389 l(' | Copyright (c) 2006 Oracle Corporation Redwood Shores, California, USA|');
390 l(' | All rights reserved. |');
391 l(' +=======================================================================+');
392 l(' | NAME '||p_package_name);
393 l(' |');
394 l(' | DESCRIPTION');
395 l(' | This package is generated by HZ_BES_GEN_PKG. ');
396 l(' |');
397 l(' | HISTORY');
398 l(' | '||TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS')||' Generated.');
399 l(' |');
400 l(' *=======================================================================*/');
401 l(' ');
402
403 if fnd_log.level_procedure>=fnd_log.g_current_runtime_level then
404 hz_utility_v2pub.debug(p_message=>'genPkgHdr()-',
405 p_prefix=>l_prefix,
406 p_msg_level=>fnd_log.level_procedure);
407 end if;
408
409 END genPkgHdr;
410 --------------------------------------
411
412 /*
413 Procedure name: genPkgBdyTail()
414 Scope: Internal
415 Purpose: This procedure writes the end section for
416 HZ_BES_BO_SITE_UTIL_PKG package body.
417 Called From: This package
418 Called By: genPkgBdyTail()
419 Paramaters - brief desc of each parameter:
420 In: p_package_name
421 Out:
422 In-Out:
423 */
424
425 --------------------------------------
426 PROCEDURE genPkgBdyTail (
427 p_package_name IN VARCHAR2
428 ) IS
429 l_debug_prefix VARCHAR2(15) := 'PKGTAIL:';
430 BEGIN
431
432 if fnd_log.level_procedure>=fnd_log.g_current_runtime_level then
433 hz_utility_v2pub.debug(p_message=>'genPkgBdyTail()+',
434 p_prefix=>l_debug_prefix,
435 p_msg_level=>fnd_log.level_procedure);
436 end if;
437
438
439 l('END '||p_package_name||';');
440 -- compile the package.
441 HZ_GEN_PLSQL.compile_code;
442
443 if fnd_log.level_procedure>=fnd_log.g_current_runtime_level then
444 hz_utility_v2pub.debug(p_message=>'genPkgBdyTail()-',
445 p_prefix=>l_debug_prefix,
446 p_msg_level=>fnd_log.level_procedure);
447 end if;
448
449
450 END genPkgBdyTail;
451 --------------------------------------
452
453 /*
454 purpose of procBegin() is to generate the procedure begin section.
455 */
456 --------------------------------------
457 PROCEDURE procBegin (
458 p_procName IN VARCHAR2,
459 p_comment IN VARCHAR2
460 ) IS
461 BEGIN
462 li('--------------------------------------');
463 li('/**');
464 li(' * PROCEDURE '||p_procName);
465 li(' *');
466 li(' * DESCRIPTION');
467 li(' * '||p_comment);
468 li(' *');
469 li(' */');
470 l(' ');
471 li('PROCEDURE '||p_procName||' IS');
472 END procBegin;
473 --------------------------------------
474
475 /*
476 purpose of procBegin() is to generate the procedure begin section.
477 */
478 --------------------------------------
479 PROCEDURE procBegin (
480 p_procName IN VARCHAR2,
481 p_comment IN VARCHAR2,
482 p_param1_name IN VARCHAR2
483 ) IS
484 BEGIN
485 li('--------------------------------------');
486 li('/**');
487 li(' * PROCEDURE '||p_procName);
488 li(' *');
489 li(' * DESCRIPTION');
490 li(' * '||p_comment);
491 li(' *');
492 li(' */');
493 l(' ');
494 li('PROCEDURE '||p_procName||' (');
495 lli(p_param1_name||' IN VARCHAR2');
496 -- lli(p_param2_name);
497 lli(' ) IS');
498 END procBegin;
499 --------------------------------------
500 /*
501 purpose of procEnd() to generate the procedure end section.
502 */
503 --------------------------------------
504 PROCEDURE procEnd (
505 p_procName IN VARCHAR2
506 ) IS
507 BEGIN
508 l(' ');
509 li('END '||p_procName||';');
510 END procEnd;
511 --------------------------------------
512 /*
516 --------------------------------------
513 purpose of writeDebugMesg() is to generate the debug messages if-end if section
514 */
515
517 PROCEDURE writeDebugMesg(
518 p_msg IN VARCHAR2,
519 p_prefix IN VARCHAR2) IS
520 BEGIN
521 l(' ');
522 l2i('IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN');
523 l(' ');
524 l2i(' HZ_UTILITY_V2PUB.DEBUG(');
525 l2i(' p_message=>'''||p_msg||''',');
526 l2i(' p_prefix=>'''||p_prefix||''',');
527 l2i(' p_msg_level=>fnd_log.level_procedure);');
528 l2i('END IF;');
529 l(' ');
530 END writeDebugMesg;
531 --------------------------------------
532
533
534 /*
535 **************************************************************************
536 functional procedures and functions that are needed
537 **************************************************************************
538 */
539 ---------------------------------------------------------------------------
540 /*
541 Procedure Name: genBOSQL
542 Purpose: This procedure generates the SQL needed to check the
543 Completeness of the business object and writes to the buffer.
544 This SQL thus generated will be teh cursor for actual completeness check procedure.
545 Flow for generating the SQL:
546 . Select all the user mandated node for a given BO
547 . Write the procedure body until cursor definition
548 . Write the root node SQL
549 . For all the child nodes
550 . Figure out the number or right paranthesis for the parent and write them
551 . Write AND EXISTS
552 . Identify the exact SQL for the current node and write it.
553 . Write the necessary right paranthesis remaining
554 . Complete the sql stmt with ;
555
556 */
557 PROCEDURE genBOSQL
558 ( P_BO_CODE IN VARCHAR2,
559 P_SQL_FOR IN VARCHAR2, -- conveys if the sql is for completeness check or to figure out the event type.
560 P_STATUS OUT NOCOPY BOOLEAN)
561 IS
562 -- cursor to get the user mandated hierarchy for a given BO
563 -- This cursor result set is used to identify the nodes in the order so
564 -- that the SQL can be generated.
565 CURSOR c_bo (c_p_bo_code IN VARCHAR2) IS
566 SELECT
567 -- ROWNUM,
568 lvl, bo_code, root_node_flag rnf, ENTITY_NAME,
569 DECODE(lvl,1,
570 DECODE(entity_name,'HZ_PARTIES',NULL,SUBSTRB(node_path,2,LENGTH(node_path))),
571 SUBSTRB(node_path, INSTR(node_path,'/', -1, 2)+1,
572 (INSTR(node_path,'/', -1, 1)-INSTR(node_path,'/', -1, 2)-1))) parent_node
573 FROM
574 (SELECT
575 sys_connect_by_path(BUSINESS_OBJECT_CODE, '/') node_path,
576 LEVEL lvl, CONNECT_BY_ISLEAF isleaf,
577 BUSINESS_OBJECT_CODE bo_code, CHILD_BO_CODE,ENTITY_NAME, root_node_flag
578 FROM hz_bus_obj_definitions
579 START WITH BUSINESS_OBJECT_CODE = c_p_bo_code AND
580 user_mandated_flag = 'Y'
581 CONNECT BY PRIOR CHILD_BO_CODE = BUSINESS_OBJECT_CODE AND
582 user_mandated_flag = 'Y'
583 ORDER BY LEVEL ASC)
584 WHERE isleaf = 1
585 ORDER BY node_path ASC, rnf desc;
586
587 -- local variables
588 /*
589 l_rownum NUMBER_COLUMN;
590 l_lvl NUMBER_COLUMN;
591 l_bo_code BO_CODE;
592 l_entity_name ENTITY_NAME;
593 l_parent_node BO_CODE;
594 l_rnf root_node_flag;
595 */
596 l_debug_prefix VARCHAR2(30) := 'GENSQL:';
597 l_ex VARCHAR2(14) := ' AND EXISTS ';
598 l_rp VARCHAR2(3) := ' ) ';
599 l_node_tbl NODE_TBL_TYPE;
600 l_node_count NUMBER;
601 l_rp_tbl RP_TBL_TYPE;
602 l_rpc NUMBER := 0;
603 l_rp_ct NUMBER := 0;
604 l_var NUMBER := 0;
605 l_gpvar VARCHAR2(80);
606 l_chk_node NUMBER;
607
608 BEGIN
609
610 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
611 hz_utility_v2pub.debug(p_message=>'GENBOSQL()+',
612 p_prefix=>l_debug_prefix,
613 p_msg_level=>fnd_log.level_procedure);
614 END IF;
615 -- set the retun status to false. If the SQL generation is successfull, this
616 -- will set to TRUE
617 P_STATUS := FALSE;
618 OPEN c_bo(p_bo_code);
619 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
620 hz_utility_v2pub.debug(
621 p_message=>'cursor opened',
622 p_prefix=>l_debug_prefix,
623 p_msg_level=>fnd_log.level_procedure);
624 END IF;
625 -- read the entire hierarchy of the BO into a collection
626 FETCH c_bo BULK COLLECT INTO l_node_tbl;
627 CLOSE c_bo;
628
629 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
630 hz_utility_v2pub.debug(
631 p_message=>'bulk collected and cursor closed',
632 p_prefix=>l_debug_prefix,
633 p_msg_level=>fnd_log.level_procedure);
634 END IF;
635 l_node_count := l_node_tbl.COUNT;
636 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
637 hz_utility_v2pub.debug(
638 p_message=>'number of nodes in the tree are:'||l_node_count,
639 p_prefix=>l_debug_prefix,
640 p_msg_level=>fnd_log.level_procedure);
641 END IF;
642 FOR i IN l_node_tbl.FIRST..l_node_tbl.LAST
643 LOOP
644 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
645 hz_utility_v2pub.debug(
646 p_message=>'--node '||i||':BO:'||l_node_tbl(i).bo_code||':ent:'||l_node_tbl(i).entity_name||':parent:'||l_node_tbl(i).parent_bo_code,
647 p_prefix=>l_debug_prefix,
648 p_msg_level=>fnd_log.level_procedure);
649 END IF;
650 END LOOP;
651
652 -- for the level 1 node, write the SQL statement to buffer.
656 WHEN 'PERSON' THEN
653 -- For all the BOs with entity name as HZ_PARTIES at level 1 would be the root node.
654
655 CASE l_node_tbl(1).BO_CODE
657 IF (P_SQL_FOR = 'EVENT') THEN
658 li(G_EVT_RT_NODE_0||''''||l_node_tbl(1).BO_CODE||'''');
659 li(G_EVT_RT_NODE_1);
660 li(G_EVT_RT_NODE_2||''''||l_node_tbl(1).BO_CODE||'''');
661 li(G_EVT_RT_NODE_3||''''||l_node_tbl(1).BO_CODE||'''');
662 li(G_EVT_RT_NODE_4);
663 ELSE
664 li(G_RT_NODE_1_PO||''''||l_node_tbl(1).BO_CODE||'''');
665 li(G_RT_NODE_2_PO||'''PERSON_CUST''');
666 li(G_RT_NODE_3_PO||''''||l_node_tbl(1).BO_CODE||'''');
667 li(G_RT_NODE_4_PO);
668 li(G_RT_NODE_2);
669 li(G_RT_NODE_BOCODE||''''||l_node_tbl(1).BO_CODE||'''');
670 /*
671 li(G_RT_NODE_1);
672 li(G_RT_NODE_BOCODE2||''''||l_node_tbl(1).BO_CODE||''',''PERSON_CUST'')');
673 li(G_RT_NODE_2);
674 li(G_RT_NODE_BOCODE||''''||l_node_tbl(1).BO_CODE||'''');
675 */
676 END IF;
677 WHEN 'ORG' THEN
678 IF (P_SQL_FOR = 'EVENT') THEN
679 li(G_EVT_RT_NODE_0||''''||l_node_tbl(1).BO_CODE||'''');
680 li(G_EVT_RT_NODE_1);
681 li(G_EVT_RT_NODE_2||''''||l_node_tbl(1).BO_CODE||'''');
682 li(G_EVT_RT_NODE_3||''''||l_node_tbl(1).BO_CODE||'''');
683 li(G_EVT_RT_NODE_4);
684 ELSE
685 li(G_RT_NODE_1_PO||''''||l_node_tbl(1).BO_CODE||'''');
686 li(G_RT_NODE_2_PO||'''ORG_CUST''');
687 li(G_RT_NODE_3_PO||''''||l_node_tbl(1).BO_CODE||'''');
688 li(G_RT_NODE_4_PO);
689 li(G_RT_NODE_2);
690 li(G_RT_NODE_BOCODE||''''||l_node_tbl(1).BO_CODE||'''');
691
692 /*
693 li(G_RT_NODE_1);
694 li(G_RT_NODE_BOCODE2||''''||l_node_tbl(1).BO_CODE||''',''ORG_CUST'')');
695 li(G_RT_NODE_2);
696 li(G_RT_NODE_BOCODE||''''||l_node_tbl(1).BO_CODE||'''');
697 */
698 END IF;
699 WHEN 'PERSON_CUST' THEN
700 IF (P_SQL_FOR = 'EVENT') THEN
701 li(G_EVT_RT_NODE_0||''''||l_node_tbl(1).BO_CODE||'''');
702 li(G_EVT_RT_NODE_1);
703 li(G_EVT_RT_NODE_2||''''||l_node_tbl(1).BO_CODE||'''');
704 li(G_EVT_RT_NODE_3||''''||l_node_tbl(1).BO_CODE||'''');
705 li(G_EVT_RT_NODE_4);
706 ELSE
707 li(G_RT_NODE_1);
708 li(G_RT_NODE_BOCODE||''''||l_node_tbl(1).BO_CODE||'''');
709 li(G_RT_NODE_2);
710 li(G_RT_NODE_BOCODE||''''||l_node_tbl(1).BO_CODE||'''');
711 END IF;
712 WHEN 'ORG_CUST' THEN
713 IF (P_SQL_FOR = 'EVENT') THEN
714 li(G_EVT_RT_NODE_0||''''||l_node_tbl(1).BO_CODE||'''');
715 li(G_EVT_RT_NODE_1);
716 li(G_EVT_RT_NODE_2||''''||l_node_tbl(1).BO_CODE||'''');
717 li(G_EVT_RT_NODE_3||''''||l_node_tbl(1).BO_CODE||'''');
721 li(G_RT_NODE_BOCODE||''''||l_node_tbl(1).BO_CODE||'''');
718 li(G_EVT_RT_NODE_4);
719 ELSE
720 li(G_RT_NODE_1);
722 li(G_RT_NODE_2);
723 li(G_RT_NODE_BOCODE||''''||l_node_tbl(1).BO_CODE||'''');
724 END IF;
725 ELSE
726 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
727 hz_utility_v2pub.debug(
728 p_message=>'invalid first node a.k.a invalid root_node node ',
729 p_prefix=>l_debug_prefix,
730 p_msg_level=>fnd_log.level_procedure);
731 END IF;
732 END CASE;
733 /*
734 Now figuring out when to write right parenthesis.
735
736 First Record selected will always be root node of one of the four
737 business objects (Org, Person, Org Customer and Person Customer).
738
739 As there are no predecessors to first record, no need to write
740 the right paranthesis.
741
742 For the second record in collection, there is no need to do elaborate checking.
743 This is because, ALWAYS, previous record is the root node of the current record.
744
745 So for both first and second records, the only operatio that must be done is
746 to figure out the correct SQL.
747
748 From 3rd row onwards, figure out whether to write the right paranthesis
749 for the previous record or not. Example - at 3rd node, you will figure out
750 whether to write the right paranthesis for 2nd node or not.
751 If it must be written, how many rigt paranthesis must be written.
752 Then write them.
753 Figure out the correct SQL and write them to buffer.
754 If right paranthesis must not be written,
755 store it in hash map.
756 write the correct SQL to the buffer.
757 Note - the collection, which is called hash map here contains the
758 following pieces of information.
759 1. node for which the right paranthesis is stored
760 2. BO of the node
761 3. Parent BO of the node
762 4. right paranthesis
763 */
764
765 FOR i IN 2..l_node_count LOOP
766 IF i >2 THEN
767 -- for the second node in the hierarchy, there is no need to
768 -- figure out right paranthesis. This is because, There are atleast two
769 -- mandatory nodes in any object. Org (hz_parties, org profiles).
770 -- Person (hz_parties, person profiles). Org Cust (hz_parties, one acct, one org)
771 -- Person Cust (hz_parties, one acct, one person)
772 -- So, the first node in an hierarchy never needs a right paranthesis at it's end.
773 -- Hence, when processing second node SQL, we do not need to process the
774 -- first node right paranthisis. Hence, we skip part to figure out the
775 -- right paranthesis for the second node and go directly to figuring out the
776 -- SQL for second node.
777 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
778 hz_utility_v2pub.debug(
779 p_message=>'the node that is being processed is: '||i,
780 p_prefix=>l_debug_prefix,
781 p_msg_level=>fnd_log.level_procedure);
782 END IF;
783 -- figuring out the right paranthesis
784 /* Some of the definitions that are useful to know to
785 code this section are:
786 Parent:
787 1. Prev rec is parent of current rec if it's BO
788 is the current nodes parent BO.
789 2. Prev rec is parent of the current rec if prev and current records
790 belongs to the same BO and has same parent BO, but prev record
791 is the root node.
792 Sibling:
793 1. Prev and Current recs belong to same BO and parent BO, and the
794 prev rec is NOT the root node.
795 2. Prev belongs to the parent BO of the current record, but it (prev rec)
796 is NOT the root node.
797 3. Prev and current recs belong to different BOs, but has same
798 parent BO and the prev rec is the root node.
799
800 If and when previous record is either type1 or type 2 parent of the
801 current record, then store the right paranthesis in hash map.
802 If the previoous record is one of the siblings,
803 then right paranthesis will be written.
804 If the previous record is neither the parent nor the sibling, then
805 write all the right paranthesis stored so far in the hasp map.
806 */
807 CASE
808 ------------------------------------
809 -- type 1 parent -- Prev rec is parent of current rec .
810 ------------------------------------
811 WHEN ((l_node_tbl(i-1).BO_CODE = l_node_tbl(i).PARENT_BO_CODE) AND
812 (l_node_tbl(i-1).RNF = 'Y')) THEN
813 -- Build hashmap containing
814 -- right paranthesis, bo name (curr.parent_node),
815 -- grand parent node (prev.parent_node)
816 -- As a speacial case, skip the node that represents the org or person
817 -- in the context of org or person customer as the parent.
818 -- This is needed as a performance improvement.
819 -- without this performance improvement, there will be two party_id bind variables
820 -- and the query generated is not correlated. Hence, CBO chooses
821 -- expensive execution path.
822
823 IF ((l_node_tbl(i-1).PARENT_BO_CODE = 'ORG_CUST' OR
824 l_node_tbl(i-1).PARENT_BO_CODE = 'PERSON_CUST') AND
825 (l_node_tbl(i-1).BO_CODE = 'ORG' OR
826 l_node_tbl(i-1).BO_CODE = 'PERSON') AND
827 (l_node_tbl(i-1).ENTITY_NAME = 'HZ_PARTIES') AND
828 (l_node_tbl(i-1).RNF = 'Y')) THEN
829 NULL;
830 ELSE
831 l_rpc := l_rpc +1;
832 l_rp_tbl(l_rpc).node := (i-1); --right paranthesis is ALWAYS stored for prev node
833 l_rp_tbl(l_rpc).rp := l_rp;
834 l_rp_tbl(l_rpc).BO_CODE := l_node_tbl(i).PARENT_BO_CODE;
835 l_rp_tbl(l_rpc).PARENT_BO_CODE := l_node_tbl(i-1).PARENT_BO_CODE;
836 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
837 hz_utility_v2pub.debug(
841 hz_utility_v2pub.debug(
838 p_message=>'The prev node is type 1 parent of the current node '||i,
839 p_prefix=>l_debug_prefix,
840 p_msg_level=>fnd_log.level_procedure);
842 p_message=>'storing the right paranthesis. details of the hashmap',
843 p_prefix=>l_debug_prefix,
844 p_msg_level=>fnd_log.level_procedure);
845 hz_utility_v2pub.debug(
846 p_message=>'rec:'||l_rpc||'BOCODE:'||l_node_tbl(i).PARENT_BO_CODE||':parent node:'||(i-1)||':PARENTBO:'||l_node_tbl(i-1).PARENT_BO_CODE,
847 p_prefix=>l_debug_prefix,
848 p_msg_level=>fnd_log.level_procedure);
849 END IF;
850 END IF; -- end of if stmt for skipping the ) collection
851 ------------------------------------
852 /*
853 2. Prev rec is parent of the current rec if prev and current records
854 belongs to the same BO and has same parent BO, but prev record
855 is the root node.
856 */
857 -- type 2 parent
858 ------------------------------------
859 WHEN ((l_node_tbl(i).PARENT_BO_CODE = l_node_tbl(i-1).PARENT_BO_CODE) AND
860 (l_node_tbl(i-1).BO_CODE = l_node_tbl(i).BO_CODE) AND
861 (l_node_tbl(i-1).RNF = 'Y')) THEN
862 -- Build hashmap containing
863 -- right paranthesis, bo name (curr.bo_node),
864 -- grand parent node (curr.parent_node)
865 -- As a speacial case, skip the node that represents the org or person
866 -- in the context of org or person customer as the parent.
867 -- This is needed as a performance improvement.
868 IF ((l_node_tbl(i-1).PARENT_BO_CODE = 'ORG_CUST' OR
869 l_node_tbl(i-1).PARENT_BO_CODE = 'PERSON_CUST') AND
870 (l_node_tbl(i-1).BO_CODE = 'ORG' OR
871 l_node_tbl(i-1).BO_CODE = 'PERSON') AND
872 (l_node_tbl(i-1).ENTITY_NAME = 'HZ_PARTIES') AND
873 (l_node_tbl(i-1).RNF = 'Y')) THEN
874 NULL;
875 ELSE
876 l_rpc := l_rpc +1;
877 l_rp_tbl(l_rpc).node := (i-1); -- right paranthesis is stored for prev node -ALWAYS
878 l_rp_tbl(l_rpc).rp := l_rp;
879 l_rp_tbl(l_rpc).BO_CODE := l_node_tbl(i).BO_CODE;
880 l_rp_tbl(l_rpc).PARENT_BO_CODE := l_node_tbl(i).PARENT_BO_CODE;
881 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
882 hz_utility_v2pub.debug(
883 p_message=>'The prev node is type 2 parent of the current node '||i,
884 p_prefix=>l_debug_prefix,
885 p_msg_level=>fnd_log.level_procedure);
886 hz_utility_v2pub.debug(
887 p_message=>'storing the right paranthesis. details of the hashmap',
888 p_prefix=>l_debug_prefix,
889 p_msg_level=>fnd_log.level_procedure);
890 hz_utility_v2pub.debug(
891 p_message=>'rec:'||l_rpc||'BOCODE:'||l_node_tbl(i).BO_CODE||':parentnode: '||(i-1)||':PARENTBO:'||l_node_tbl(i).PARENT_BO_CODE,
892 p_prefix=>l_debug_prefix,
893 p_msg_level=>fnd_log.level_procedure);
894 END IF;
895 END IF;-- end of if stmt for skipping the ) collection for typ2 parent
896 ---------------------------------------
897 /*
898 1. Prev and Current recs belong to same BO and parent BO, and the
899 prev rec is NOT the root node.
900 -- type 1 sibling
901 For the siblings, we will not collect the right paranthesis.
902 */
903 ---------------------------------------
904 WHEN ((l_node_tbl(i-1).BO_CODE = l_node_tbl(i).PARENT_BO_CODE) AND
905 (l_node_tbl(i-1).RNF = 'N' )) THEN
906 l2i(l_rp||'--'||l_node_tbl(i-1).entity_name);
907 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
908 hz_utility_v2pub.debug(
909 p_message=>'for node: '||i|| 'is the type 1 sibling of:'||l_node_tbl(i).PARENT_BO_CODE,
910 p_prefix=>l_debug_prefix,
911 p_msg_level=>fnd_log.level_procedure);
912 END IF;
913 ---------------------------------------
914 /*
915 2. Prev belongs to the parent BO of the current record, but it (prev rec)
916 is NOT the root node.
917
918 -- type 2 sibling
919 */
920 ---------------------------------------
921 WHEN ((l_node_tbl(i).PARENT_BO_CODE = l_node_tbl(i-1).PARENT_BO_CODE) AND
922 (l_node_tbl(i-1).BO_CODE = l_node_tbl(i).BO_CODE) AND
923 (l_node_tbl(i-1).RNF = 'N')) THEN
924 l2i(l_rp||'--'||l_node_tbl(i-1).entity_name);
925 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
926 hz_utility_v2pub.debug(
927 p_message=>'node: '||i|| 'is the type 2 sibling of node:'||(i-1)||'and its parent bo is:'||l_node_tbl(i).PARENT_BO_CODE,
928 p_prefix=>l_debug_prefix,
929 p_msg_level=>fnd_log.level_procedure);
930 END IF;
931 ---------------------------------------
932 /*
933 3. Prev and current recs belong to different BOs, but has same
934 parent BO and the prev rec is the root node.
935
936 -- type 3 sibling
937 */
938 ---------------------------------------
939
940 WHEN ((l_node_tbl(i).PARENT_BO_CODE = l_node_tbl(i-1).PARENT_BO_CODE) AND
941 (l_node_tbl(i-1).BO_CODE <> l_node_tbl(i).BO_CODE) AND
942 (l_node_tbl(i-1).RNF = 'Y')) THEN
943 l2i(l_rp||'--'||l_node_tbl(i-1).entity_name);
944 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
945 hz_utility_v2pub.debug(
946 p_message=>'node: '||i||' is the type 3 sibling of:'||(i-1)||' and its parent bo is:'||l_node_tbl(i).PARENT_BO_CODE,
947 p_prefix=>l_debug_prefix,
948 p_msg_level=>fnd_log.level_procedure);
949 END IF;
950 -- when neither parent nor sibling
951 ELSE
952 -- this section of code is executed when we need to write the
953 -- right paranthesis when the prev rec is neither parent nor sibling.
957 -- 1. previous node is niether parent nor sibling of the current nodes
954 -- Before dumping the right paranthesis that were stored in the hashmap,
955 -- write a right paranthesis for the previous node.
956 -- This is needed because:
958 -- 2. the hashmap does not contain the right paranthesis for the previous
959 -- node.
960 -- 3. every sql segment for any given entity barring the first node
961 -- in node_tbl will have left parnthesis in its global variable
962 l2i(l_rp||' --'||l_node_tbl(i-1).entity_name);
963
964 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
965 hz_utility_v2pub.debug(
966 p_message=>'previous node is neither the parent nor sibling of node:'||i,
967 p_prefix=>l_debug_prefix,
968 p_msg_level=>fnd_log.level_procedure);
969 hz_utility_v2pub.debug(
970 p_message=>'node: '||i|| 'is not a parent or sibling of node:'||(i-1)||' and its parent bo:'||l_node_tbl(i).PARENT_BO_CODE,
971 p_prefix=>l_debug_prefix,
972 p_msg_level=>fnd_log.level_procedure);
973 hz_utility_v2pub.debug(
974 p_message=>'number of right paranthesis collected so far:'||l_rpc,
975 p_prefix=>l_debug_prefix,
976 p_msg_level=>fnd_log.level_procedure);
977 END IF;
978 -- now, start writing the stored right paranthesis to the buffer.
979 l_gpvar := NULL; -- temporarily stores grand-parent of the prev rec
980 l_rp_ct := l_rpc;
981 l_var := 1; -- initializing the l_var such that the code will loop
982 -- through all the rows in the right paranthesis hashmap.
983 l_chk_node := 0; -- initializing this temp var
984 LOOP
985 l_chk_node := l_rp_tbl(l_rp_ct).node;
986
987 IF l_gpvar IS NULL THEN
988 -- This will run only the first time when traversing the hierarchy
989 -- and it is identified that the previous record is neither a sibling nor
990 -- a parent.
991 -- now, check to see if the node stored in the hash map is
992 -- in any way releated (type 1 or 2 parent)to that of
993 -- the previous record in the node hierrachy. If so, write the right paranthesis.
994
995 -- Here, checking the previous node because, writing of right paranthesis
996 -- is always for previous record.
997
998 IF (l_node_tbl(i-1).bo_code = l_rp_tbl(l_rp_ct).bo_code) OR
999 (l_node_tbl(i-1).PARENT_BO_CODE = l_rp_tbl(l_rp_ct).bo_code)
1000 THEN
1001 l2i(l_rp||' --'||l_node_tbl(i-1).bo_code); -- writing right parantheis to buffer
1002 -- once the row from current collection (l_rp_tbl) is deleted,
1003 -- then we loose the parent BO of the row. we need this to
1004 -- tie this (going to be deleted rec) to the previous rec in
1005 -- collection to compare and see if it must be deleted or not.
1006 -- So, l_gpvar acts as a temp store for storing the parent BO
1007 l_gpvar := l_rp_tbl(l_rp_ct).PARENT_BO_CODE;
1008 l_rp_tbl.DELETE(l_rp_ct);
1009 l_rp_ct := l_rp_ct-1;
1010 END IF;
1011 ELSE
1012
1013 IF l_gpvar = l_rp_tbl(l_rp_ct).bo_code THEN
1014
1015 -- This piece of code ensures that all the right paranthesis are
1016 -- written to buffer for all collected nodes until,
1017 -- any one of the previously stored node (in l_rp_tbl) is
1018 -- type1 or type2 parent of the current node (in l_node_tbl)
1019
1020 -- before writing the right paranthesis,
1021 -- check if (l_rp_tbl) collection rec getting deleted is not the
1022 -- the parent of current node (in l_node_tbl) that is being processed.
1023 -- If it is, do not delete the record from (l_rp_tbl) collection
1024 -- and exit from this loop. This is because, current node, being
1025 -- a child of the node in l_rp_tbl collection, it must not write
1026 -- the rparanthesis.
1027 /*
1028 */
1029 EXIT WHEN (((l_node_tbl(i).PARENT_BO_CODE = l_node_tbl(l_chk_node).PARENT_BO_CODE) AND
1030 (l_node_tbl(i).BO_CODE = l_node_tbl(l_chk_node).BO_CODE) AND
1031 (l_node_tbl(l_chk_node).RNF = 'Y')) OR
1032 ((l_node_tbl(l_chk_node).BO_CODE = l_node_tbl(i).PARENT_BO_CODE) AND
1033 (l_node_tbl(l_chk_node).RNF = 'Y'))
1034 );
1035 l2i(l_rp||' --'||l_gpvar); -- writing right parantheis to buffer
1036 l_gpvar := l_rp_tbl(l_rp_ct).PARENT_BO_CODE;
1037 l_rp_tbl.DELETE(l_rp_ct);
1038 l_rp_ct := l_rp_ct-1;
1039
1040 END IF;
1041 END IF;
1042 EXIT WHEN l_var > l_rp_ct;
1043 END LOOP;
1044 -- to ensure not to over write the node info in l_rp_tbl collection,
1045 -- set the counter to the last available record in l_rp_tbl table.
1046 l_rpc := l_rp_tbl.count;
1047 END CASE; -- end of CASE for figuring out if previous record is current recs
1048 -- parent or sibling, to write the right paranthesis.
1049 END IF; -- check for >2 node or not
1050
1051 -- write the 'AND EXISTS' and the appropriate sql for the current node.
1052 CASE l_node_tbl(i).entity_name
1053 WHEN 'HZ_CERTIFICATIONS' THEN
1054 -- can have Org or Person as Parent
1055 IF (l_node_tbl(i).BO_CODE = 'ORG' OR l_node_tbl(i).BO_CODE = 'PERSON') THEN
1056 IF P_SQL_FOR = 'EVENT' THEN
1057 l2i(G_HZ_CERT_P);
1058 l2i(G_LUD);
1059 ELSE
1060 l2i(G_HZ_CERT_P);
1061 END IF;
1062 ELSE
1063 IF P_SQL_FOR = 'EVENT' THEN
1064 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1065 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1066 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1067 fnd_msg_pub.ADD;
1071 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1068 RAISE FND_API.G_EXC_ERROR;
1069 ELSE
1070 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1072 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1073 fnd_msg_pub.ADD;
1074 RAISE FND_API.G_EXC_ERROR;
1075 END IF;
1076 END IF;
1077 WHEN 'HZ_CITIZENSHIP' THEN
1078 -- can have Person as parent
1079 IF (l_node_tbl(i).BO_CODE = 'PERSON') THEN
1080 IF P_SQL_FOR = 'EVENT' THEN
1081 l2i(G_HZ_CITIZEN_P);
1082 l2i(G_LUD);
1083 ELSE
1084 l2i(G_HZ_CITIZEN_P);
1085 END IF;
1086 ELSE
1087 IF P_SQL_FOR = 'EVENT' THEN
1088 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1089 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1090 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1091 fnd_msg_pub.ADD;
1092 RAISE FND_API.G_EXC_ERROR;
1093 ELSE
1094 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1095 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1096 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1097 fnd_msg_pub.ADD;
1098 RAISE FND_API.G_EXC_ERROR;
1099 END IF;
1100 END IF;
1101 WHEN 'HZ_CODE_ASSIGNMENTS' THEN
1102 -- can have Org or Person as Parent
1103 IF (l_node_tbl(i).BO_CODE = 'ORG' OR l_node_tbl(i).BO_CODE = 'PERSON') THEN
1104 IF P_SQL_FOR = 'EVENT' THEN
1105 l2i(G_HZ_CODE_ASSIGN_P);
1106 l2i(G_LUD);
1107 ELSE
1108 l2i(G_HZ_CODE_ASSIGN_P);
1109 END IF;
1110 ELSE
1111 IF P_SQL_FOR = 'EVENT' THEN
1112 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1113 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1114 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1115 fnd_msg_pub.ADD;
1116 RAISE FND_API.G_EXC_ERROR;
1117 ELSE
1118 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1119 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1120 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1121 fnd_msg_pub.ADD;
1122 RAISE FND_API.G_EXC_ERROR;
1123 END IF;
1124 END IF;
1125 WHEN 'HZ_CONTACT_POINTS' THEN
1126 -- contact points can have Org, Person, Contact, PS
1127 CASE l_node_tbl(i).PARENT_BO_CODE
1128 WHEN 'ORG' THEN
1129 -- contact point types are EDI, EFT, EMAIL, PHONE, WEB, TLX
1130 CASE l_node_tbl(i).BO_CODE
1131 WHEN 'EFT' THEN
1132 IF P_SQL_FOR = 'EVENT' THEN
1133 ll2i(G_CP_P1_ET1);
1134 l2i(G_CP_EFT);
1135 ELSE
1136 ll2i(G_CP_P1);
1137 l2i(G_CP_EFT);
1138 END IF;
1139 WHEN 'EDI' THEN
1140 IF P_SQL_FOR = 'EVENT' THEN
1141 ll2i(G_CP_P1_ET1);
1142 l2i(G_CP_EDI);
1143 ELSE
1144 ll2i(G_CP_P1);
1145 l2i(G_CP_EDI);
1146 END IF;
1147 WHEN 'EMAIL' THEN
1148 IF P_SQL_FOR = 'EVENT' THEN
1149 ll2i(G_CP_P1_ET1);
1150 l2i(G_CP_EMAIL);
1151 ELSE
1152 ll2i(G_CP_P1);
1153 l2i(G_CP_EMAIL);
1154 END IF;
1155 WHEN 'PHONE' THEN
1156 IF P_SQL_FOR = 'EVENT' THEN
1157 ll2i(G_CP_P1_ET1);
1158 l2i(G_CP_PHONE);
1159 ELSE
1160 ll2i(G_CP_P1);
1161 l2i(G_CP_PHONE);
1162 END IF;
1163 WHEN 'WEB' THEN
1164 IF P_SQL_FOR = 'EVENT' THEN
1165 ll2i(G_CP_P1_ET1);
1166 l2i(G_CP_WEB);
1167 ELSE
1168 ll2i(G_CP_P1);
1169 l2i(G_CP_WEB);
1170 END IF;
1171 WHEN 'TLX' THEN
1172 IF P_SQL_FOR = 'EVENT' THEN
1173 ll2i(G_CP_P1_ET1);
1174 l2i(G_CP_TLX);
1175 ELSE
1176 ll2i(G_CP_P1);
1177 l2i(G_CP_TLX);
1178 END IF;
1179 ELSE
1180 IF P_SQL_FOR = 'EVENT' THEN
1181 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_BOCODE');
1182 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1183 fnd_message.set_token('BO_CODE' ,l_node_tbl(i).BO_CODE);
1184 fnd_msg_pub.ADD;
1185 RAISE FND_API.G_EXC_ERROR;
1186 ELSE
1187 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_BOCODE');
1188 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1189 fnd_message.set_token('BO_CODE' ,l_node_tbl(i).BO_CODE);
1190 fnd_msg_pub.ADD;
1191 RAISE FND_API.G_EXC_ERROR;
1192 END IF;
1193 END CASE;
1194 WHEN 'PERSON' THEN
1195 -- contact point types are EMAIL, PHONE, WEB, SMS
1196 CASE l_node_tbl(i).BO_CODE
1197 WHEN 'EMAIL' THEN
1198 IF P_SQL_FOR = 'EVENT' THEN
1199 ll2i(G_CP_P1_ET1);
1200 l2i(G_CP_EMAIL);
1201 ELSE
1202 ll2i(G_CP_P1);
1203 l2i(G_CP_EMAIL);
1204 END IF;
1205 WHEN 'PHONE' THEN
1206 IF P_SQL_FOR = 'EVENT' THEN
1207 ll2i(G_CP_P1_ET1);
1208 l2i(G_CP_PHONE);
1209 ELSE
1210 ll2i(G_CP_P1);
1211 l2i(G_CP_PHONE);
1212 END IF;
1213 WHEN 'WEB' THEN
1214 IF P_SQL_FOR = 'EVENT' THEN
1215 ll2i(G_CP_P1_ET1);
1216 l2i(G_CP_WEB);
1217 ELSE
1218 ll2i(G_CP_P1);
1219 l2i(G_CP_WEB);
1220 END IF;
1221 WHEN 'SMS' THEN
1222 IF P_SQL_FOR = 'EVENT' THEN
1223 ll2i(G_CP_P1_ET1);
1227 l2i(G_CP_SMS);
1224 l2i(G_CP_SMS);
1225 ELSE
1226 ll2i(G_CP_P1);
1228 END IF;
1229 ELSE
1230 IF P_SQL_FOR = 'EVENT' THEN
1231 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_BOCODE');
1232 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1233 fnd_message.set_token('BO_CODE' ,l_node_tbl(i).BO_CODE);
1234 fnd_msg_pub.ADD;
1235 RAISE FND_API.G_EXC_ERROR;
1236 ELSE
1237 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_BOCODE');
1238 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1239 fnd_message.set_token('BO_CODE' ,l_node_tbl(i).BO_CODE);
1240 fnd_msg_pub.ADD;
1241 RAISE FND_API.G_EXC_ERROR;
1242 END IF;
1243 END CASE;
1244 WHEN 'ORG_CONTACT' THEN
1245 -- contact point types are EMAIL, PHONE, WEB, TLX
1246 CASE l_node_tbl(i).BO_CODE
1247 WHEN 'PHONE' THEN
1248 IF P_SQL_FOR = 'EVENT' THEN
1249 ll2i(G_CP_REL1_ET1);
1250 ll2i(G_CP_REL1_ET2);
1251 l2i(G_CP_PHONE);
1252 ELSE
1253 ll2i(G_CP_REL1);
1254 l2i(G_CP_PHONE);
1255 END IF;
1256 WHEN 'TLX' THEN
1257 IF P_SQL_FOR = 'EVENT' THEN
1258 ll2i(G_CP_REL1_ET1);
1259 ll2i(G_CP_REL1_ET2);
1260 l2i(G_CP_TLX);
1261 ELSE
1262 ll2i(G_CP_REL1);
1263 l2i(G_CP_TLX);
1264 END IF;
1265 WHEN 'EMAIL' THEN
1266 IF P_SQL_FOR = 'EVENT' THEN
1267 ll2i(G_CP_REL1_ET1);
1268 ll2i(G_CP_REL1_ET2);
1269 l2i(G_CP_EMAIL);
1270 ELSE
1271 ll2i(G_CP_REL1);
1272 l2i(G_CP_EMAIL);
1273 END IF;
1274 WHEN 'WEB' THEN
1275 IF P_SQL_FOR = 'EVENT' THEN
1276 ll2i(G_CP_REL1_ET1);
1277 ll2i(G_CP_REL1_ET2);
1278 l2i(G_CP_WEB);
1279 ELSE
1280 ll2i(G_CP_REL1);
1281 l2i(G_CP_WEB);
1282 END IF;
1283 WHEN 'SMS' THEN
1284 IF P_SQL_FOR = 'EVENT' THEN
1285 ll2i(G_CP_REL1_ET1);
1286 ll2i(G_CP_REL1_ET2);
1287 l2i(G_CP_SMS);
1288 ELSE
1289 ll2i(G_CP_REL1);
1290 l2i(G_CP_SMS);
1291 END IF;
1292 ELSE
1293 IF P_SQL_FOR = 'EVENT' THEN
1294 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_BOCODE');
1295 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1296 fnd_message.set_token('BO_CODE' ,l_node_tbl(i).BO_CODE);
1297 fnd_msg_pub.ADD;
1298 RAISE FND_API.G_EXC_ERROR;
1299 ELSE
1300 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_BOCODE');
1301 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1302 fnd_message.set_token('BO_CODE' ,l_node_tbl(i).BO_CODE);
1303 fnd_msg_pub.ADD;
1304 RAISE FND_API.G_EXC_ERROR;
1305 END IF;
1306 END CASE;
1307 WHEN 'PARTY_SITE' THEN
1308 -- contact point types are EMAIL, PHONE, WEB, TLX
1309 CASE l_node_tbl(i).BO_CODE
1310 WHEN 'PHONE' THEN
1311 IF P_SQL_FOR = 'EVENT' THEN
1312 ll2i(G_CP_PS1_ET1);
1313 l2i(G_CP_PHONE);
1314 ELSE
1315 ll2i(G_CP_PS1);
1316 l2i(G_CP_PHONE);
1317 END IF;
1318 WHEN 'TLX' THEN
1319 IF P_SQL_FOR = 'EVENT' THEN
1320 ll2i(G_CP_PS1_ET1);
1321 l2i(G_CP_TLX);
1322 ELSE
1323 ll2i(G_CP_PS1);
1324 l2i(G_CP_TLX);
1325 END IF;
1326 WHEN 'EMAIL' THEN
1327 IF P_SQL_FOR = 'EVENT' THEN
1328 ll2i(G_CP_PS1_ET1);
1329 l2i(G_CP_EMAIL);
1330 ELSE
1331 ll2i(G_CP_PS1);
1332 l2i(G_CP_EMAIL);
1333 END IF;
1334 WHEN 'WEB' THEN
1335 IF P_SQL_FOR = 'EVENT' THEN
1336 ll2i(G_CP_PS1_ET1);
1337 l2i(G_CP_WEB);
1338 ELSE
1339 ll2i(G_CP_PS1);
1340 l2i(G_CP_WEB);
1341 END IF;
1342 ELSE
1343 IF P_SQL_FOR = 'EVENT' THEN
1344 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_BOCODE');
1345 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1346 fnd_message.set_token('BO_CODE' ,l_node_tbl(i).BO_CODE);
1347 fnd_msg_pub.ADD;
1348 RAISE FND_API.G_EXC_ERROR;
1349 ELSE
1350 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_BOCODE');
1351 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1352 fnd_message.set_token('BO_CODE' ,l_node_tbl(i).BO_CODE);
1353 fnd_msg_pub.ADD;
1354 RAISE FND_API.G_EXC_ERROR;
1355 END IF;
1356 END CASE;
1357 ELSE
1358 IF P_SQL_FOR = 'EVENT' THEN
1359 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1360 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1361 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1362 fnd_msg_pub.ADD;
1363 RAISE FND_API.G_EXC_ERROR;
1364 ELSE
1365 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1366 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1367 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1368 fnd_msg_pub.ADD;
1369 RAISE FND_API.G_EXC_ERROR;
1373 -- contact preference can have Org, Person, Contact, PS, CP as parents
1370 END IF;
1371 END CASE; -- CP
1372 WHEN 'HZ_CONTACT_PREFERENCES' THEN
1374 CASE l_node_tbl(i).BO_CODE
1375 WHEN 'ORG' THEN
1376 IF P_SQL_FOR = 'EVENT' THEN
1377 l2i(G_HZ_CONT_PREF_P);
1378 l2i(G_LUD);
1379 ELSE
1380 l2i(G_HZ_CONT_PREF_P);
1381 END IF;
1382 WHEN 'PERSON' THEN
1383 IF P_SQL_FOR = 'EVENT' THEN
1384 l2i(G_HZ_CONT_PREF_P);
1385 l2i(G_LUD);
1386 ELSE
1387 l2i(G_HZ_CONT_PREF_P);
1388 END IF;
1389 WHEN 'ORG_CONTACT' THEN
1390 IF P_SQL_FOR = 'EVENT' THEN
1391 l2i(G_HZ_CONT_PREF_REL_ET1);
1392 l2i(G_HZ_CONT_PREF_REL_ET2);
1393 ELSE
1394 l2i(G_HZ_CONT_PREF_REL);
1395 END IF;
1396 WHEN 'PARTY_SITE' THEN
1397 IF P_SQL_FOR = 'EVENT' THEN
1398 l2i(G_HZ_CONT_PREF_PS);
1399 l2i(G_LUD);
1400 ELSE
1401 l2i(G_HZ_CONT_PREF_PS);
1402 END IF;
1403 WHEN 'PHONE' THEN
1404 IF P_SQL_FOR = 'EVENT' THEN
1405 l2i(G_HZ_CONT_PREF_CP);
1406 l2i(G_LUD);
1407 ELSE
1408 l2i(G_HZ_CONT_PREF_CP);
1409 END IF;
1410 WHEN 'EMAIL' THEN
1411 IF P_SQL_FOR = 'EVENT' THEN
1412 l2i(G_HZ_CONT_PREF_CP);
1413 l2i(G_LUD);
1414 ELSE
1415 l2i(G_HZ_CONT_PREF_CP);
1416 END IF;
1417 WHEN 'WEB' THEN
1418 IF P_SQL_FOR = 'EVENT' THEN
1419 l2i(G_HZ_CONT_PREF_CP);
1420 l2i(G_LUD);
1421 ELSE
1422 l2i(G_HZ_CONT_PREF_CP);
1423 END IF;
1424 WHEN 'TLX' THEN
1425 IF P_SQL_FOR = 'EVENT' THEN
1426 l2i(G_HZ_CONT_PREF_CP);
1427 l2i(G_LUD);
1428 ELSE
1429 l2i(G_HZ_CONT_PREF_CP);
1430 END IF;
1431 WHEN 'SMS' THEN
1432 IF P_SQL_FOR = 'EVENT' THEN
1433 l2i(G_HZ_CONT_PREF_CP);
1434 l2i(G_LUD);
1435 ELSE
1436 l2i(G_HZ_CONT_PREF_CP);
1437 END IF;
1438 WHEN 'EDI' THEN
1439 IF P_SQL_FOR = 'EVENT' THEN
1440 l2i(G_HZ_CONT_PREF_CP);
1441 l2i(G_LUD);
1442 ELSE
1443 l2i(G_HZ_CONT_PREF_CP);
1444 END IF;
1445 WHEN 'EFT' THEN
1446 IF P_SQL_FOR = 'EVENT' THEN
1447 l2i(G_HZ_CONT_PREF_CP);
1448 l2i(G_LUD);
1449 ELSE
1450 l2i(G_HZ_CONT_PREF_CP);
1451 END IF;
1452 ELSE
1453 IF P_SQL_FOR = 'EVENT' THEN
1454 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1455 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1456 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1457 fnd_msg_pub.ADD;
1458 RAISE FND_API.G_EXC_ERROR;
1459 ELSE
1460 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1461 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1462 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1463 fnd_msg_pub.ADD;
1464 RAISE FND_API.G_EXC_ERROR;
1465 END IF;
1466 END CASE; --cpp
1467 WHEN 'HZ_CREDIT_RATINGS' THEN
1468 -- CR is only for Org
1469 IF l_node_tbl(i).BO_CODE = 'ORG' THEN
1470 IF P_SQL_FOR = 'EVENT' THEN
1471 l2i(G_HZ_CREDIT_RATINGS_P);
1472 l2i(G_LUD);
1473 ELSE
1474 l2i(G_HZ_CREDIT_RATINGS_P);
1475 END IF;
1476 ELSE
1477 IF P_SQL_FOR = 'EVENT' THEN
1478 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1479 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1480 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1481 fnd_msg_pub.ADD;
1482 RAISE FND_API.G_EXC_ERROR;
1483 ELSE
1484 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1485 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1486 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1487 fnd_msg_pub.ADD;
1488 RAISE FND_API.G_EXC_ERROR;
1489 END IF;
1490 END IF;
1491 WHEN 'HZ_CUST_ACCOUNT_ROLES' THEN
1492 -- HZ_CUST_ACCOUNT_ROLES Can have Account, Account Site as parent
1493 CASE l_node_tbl(i).PARENT_BO_CODE
1494 WHEN 'CUST_ACCT' THEN
1495 IF P_SQL_FOR = 'EVENT' THEN
1496 l2i(G_HZ_CUST_ACCT_ROLES_A);
1497 l2i(G_LUD);
1498 ELSE
1499 l2i(G_HZ_CUST_ACCT_ROLES_A);
1500 END IF;
1501 WHEN 'CUST_ACCT_SITE' THEN
1502 IF P_SQL_FOR = 'EVENT' THEN
1503 l2i(G_HZ_CUST_ACCT_ROLES_AS);
1504 l2i(G_LUD);
1505 ELSE
1506 l2i(G_HZ_CUST_ACCT_ROLES_AS);
1507 END IF;
1508 ELSE
1509 IF P_SQL_FOR = 'EVENT' THEN
1510 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1511 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1512 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1513 fnd_msg_pub.ADD;
1514 RAISE FND_API.G_EXC_ERROR;
1515 ELSE
1519 fnd_msg_pub.ADD;
1516 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1517 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1518 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1520 RAISE FND_API.G_EXC_ERROR;
1521 END IF;
1522 END CASE; -- cust_acct_roles
1523 WHEN 'HZ_CUST_ACCOUNTS' THEN
1524 --HZ_CUST_ACCOUNTS can have only Org or Person as parent
1525 IF (l_node_tbl(i).PARENT_BO_CODE = 'ORG_CUST' OR l_node_tbl(i).PARENT_BO_CODE = 'PERSON_CUST') THEN
1526 IF P_SQL_FOR = 'EVENT' THEN
1527 l2i(G_HZ_CUST_ACCTS_P);
1528 l2i(G_LUD);
1529 ELSE
1530 l2i(G_HZ_CUST_ACCTS_P);
1531 END IF;
1532 ELSE
1533 IF P_SQL_FOR = 'EVENT' THEN
1534 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1535 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1536 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1537 fnd_msg_pub.ADD;
1538 RAISE FND_API.G_EXC_ERROR;
1539 ELSE
1540 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1541 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1542 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1543 fnd_msg_pub.ADD;
1544 RAISE FND_API.G_EXC_ERROR;
1545 END IF;
1546 END IF; -- cust_acct
1547 WHEN 'HZ_CUST_ACCT_RELATE_ALL' THEN
1548 -- HZ_CUST_ACCT_RELATE_ALL can only have cust account as parent
1549 IF l_node_tbl(i).BO_CODE = 'CUST_ACCT' THEN
1550 IF P_SQL_FOR = 'EVENT' THEN
1551 l2i(G_HZ_CUST_ACCT_REL_P);
1552 l2i(G_LUD);
1553 ELSE
1554 l2i(G_HZ_CUST_ACCT_REL_P);
1555 END IF;
1556 ELSE
1557 IF P_SQL_FOR = 'EVENT' THEN
1558 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1559 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1560 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1561 fnd_msg_pub.ADD;
1562 RAISE FND_API.G_EXC_ERROR;
1563 ELSE
1564 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1565 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1566 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1567 fnd_msg_pub.ADD;
1568 RAISE FND_API.G_EXC_ERROR;
1569 END IF;
1570 END IF; -- cust_acct_relate_all
1571 WHEN 'HZ_CUST_ACCT_SITES_ALL' THEN
1572 -- HZ_CUST_ACCT_SITES_ALL have Cust Account as parent
1573 IF l_node_tbl(i).PARENT_BO_CODE = 'CUST_ACCT' THEN
1574 IF P_SQL_FOR = 'EVENT' THEN
1575 l2i(G_HZ_CUST_ACCT_SITES_A);
1576 l2i(G_LUD);
1577 ELSE
1578 l2i(G_HZ_CUST_ACCT_SITES_A);
1579 END IF;
1580 ELSE
1581 IF P_SQL_FOR = 'EVENT' THEN
1582 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1583 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1584 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1585 fnd_msg_pub.ADD;
1586 RAISE FND_API.G_EXC_ERROR;
1587 ELSE
1588 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1589 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1590 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1591 fnd_msg_pub.ADD;
1592 RAISE FND_API.G_EXC_ERROR;
1593 END IF;
1594 END IF; -- cust_acct_sites_all
1595 WHEN 'HZ_CUST_PROFILE_AMTS' THEN
1596 -- HZ_CUST_PROFILE_AMTS Can have Account Profile as parent
1597 IF l_node_tbl(i).BO_CODE = 'CUST_PROFILE' THEN
1598 IF P_SQL_FOR = 'EVENT' THEN
1599 l2i(G_HZ_CUST_PROF_AMTS_AP);
1600 l2i(G_LUD);
1601 ELSE
1602 l2i(G_HZ_CUST_PROF_AMTS_AP);
1603 END IF;
1604 ELSE
1605 IF P_SQL_FOR = 'EVENT' THEN
1606 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1607 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1608 fnd_msg_pub.ADD;
1609 RAISE FND_API.G_EXC_ERROR;
1610 ELSE
1611 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1612 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1613 fnd_msg_pub.ADD;
1614 RAISE FND_API.G_EXC_ERROR;
1615 END IF;
1616 END IF; -- cust_profile_amts
1617 WHEN 'HZ_CUST_SITE_USES_ALL' THEN
1618 -- HZ_CUST_SITE_USES_ALL can only have Account Site as parent
1619 IF l_node_tbl(i).PARENT_BO_CODE = 'CUST_ACCT_SITE' THEN
1620 -- IF l_node_tbl(i).BO_CODE = 'CUST_ACCT_SITE' THEN
1621 IF P_SQL_FOR = 'EVENT' THEN
1622 l2i(G_HZ_CUST_SITE_USES_AS);
1623 l2i(G_LUD);
1624 ELSE
1625 l2i(G_HZ_CUST_SITE_USES_AS);
1626 END IF;
1627 ELSE
1628 IF P_SQL_FOR = 'EVENT' THEN
1629 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1630 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1631 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1632 fnd_msg_pub.ADD;
1633 RAISE FND_API.G_EXC_ERROR;
1634 ELSE
1635 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1636 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1637 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1638 fnd_msg_pub.ADD;
1639 RAISE FND_API.G_EXC_ERROR;
1640 END IF;
1641 END IF; -- cust_site_uses
1642 WHEN 'HZ_CUSTOMER_PROFILES' THEN
1643 -- HZ_CUSTOMER_PROFILES Can have Cust Account, Account Site Use as parents
1644 CASE l_node_tbl(i).PARENT_BO_CODE
1648 l2i(G_LUD);
1645 WHEN 'CUST_ACCT' THEN
1646 IF P_SQL_FOR = 'EVENT' THEN
1647 l2i(G_HZ_CUST_PROF_A);
1649 ELSE
1650 l2i(G_HZ_CUST_PROF_A);
1651 END IF;
1652 WHEN 'CUST_ACCT_SITE_USE' THEN
1653 IF P_SQL_FOR = 'EVENT' THEN
1654 l2i(G_HZ_CUST_PROF_ASU);
1655 l2i(G_LUD);
1656 ELSE
1657 l2i(G_HZ_CUST_PROF_ASU);
1658 END IF;
1659 ELSE
1660 IF P_SQL_FOR = 'EVENT' THEN
1661 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1662 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1663 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1664 fnd_msg_pub.ADD;
1665 RAISE FND_API.G_EXC_ERROR;
1666 ELSE
1667 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1668 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1669 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1670 fnd_msg_pub.ADD;
1671 RAISE FND_API.G_EXC_ERROR;
1672 END IF;
1673 END CASE; -- cust_prof
1674 WHEN 'HZ_EDUCATION' THEN
1675 -- HZ_EDUCATION Can have Person as parent
1676 IF l_node_tbl(i).BO_CODE = 'PERSON' THEN
1677 IF P_SQL_FOR = 'EVENT' THEN
1678 l2i(G_HZ_EDU_P);
1679 l2i(G_LUD);
1680 ELSE
1681 l2i(G_HZ_EDU_P);
1682 END IF;
1683 ELSE
1684 IF P_SQL_FOR = 'EVENT' THEN
1685 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1686 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1687 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1688 fnd_msg_pub.ADD;
1689 RAISE FND_API.G_EXC_ERROR;
1690 ELSE
1691 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1692 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1693 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1694 fnd_msg_pub.ADD;
1695 RAISE FND_API.G_EXC_ERROR;
1696 END IF;
1697 END IF; -- edu
1698 WHEN 'HZ_EMPLOYMENT_HISTORY' THEN
1699 -- HZ_EMPLOYMENT_HISTORY Can have Person as parent
1700 IF l_node_tbl(i).PARENT_BO_CODE = 'PERSON' THEN
1701 IF P_SQL_FOR = 'EVENT' THEN
1702 l2i(G_HZ_EMP_HIST_P);
1703 l2i(G_LUD);
1704 ELSE
1705 l2i(G_HZ_EMP_HIST_P);
1706 END IF;
1707 ELSE
1708 IF P_SQL_FOR = 'EVENT' THEN
1709 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1710 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1711 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1712 fnd_msg_pub.ADD;
1713 RAISE FND_API.G_EXC_ERROR;
1714 ELSE
1715 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1716 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1717 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1718 fnd_msg_pub.ADD;
1719 RAISE FND_API.G_EXC_ERROR;
1720 END IF;
1721 END IF; -- emp_hist
1722 WHEN 'HZ_FINANCIAL_NUMBERS' THEN
1723 -- HZ_FINANCIAL_NUMBERS can have financial reports as parent
1724 IF l_node_tbl(i).BO_CODE = 'FIN_REPORT' THEN
1725 IF P_SQL_FOR = 'EVENT' THEN
1726 l2i(G_HZ_FIN_NUM_FR);
1727 l2i(G_LUD);
1728 ELSE
1729 l2i(G_HZ_FIN_NUM_FR);
1730 END IF;
1731 ELSE
1732 IF P_SQL_FOR = 'EVENT' THEN
1733 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1734 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1735 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1736 fnd_msg_pub.ADD;
1737 RAISE FND_API.G_EXC_ERROR;
1738 ELSE
1739 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1740 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1741 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1742 fnd_msg_pub.ADD;
1743 RAISE FND_API.G_EXC_ERROR;
1744 END IF;
1745 END IF; -- fin_num
1746 WHEN 'HZ_FINANCIAL_PROFILE' THEN
1747 -- HZ_FINANCIAL_PROFILE Can have Person,Org as parent
1748 -- can have Org or Person as Parent
1749 IF (l_node_tbl(i).BO_CODE = 'ORG' OR l_node_tbl(i).BO_CODE = 'PERSON') THEN
1750 IF P_SQL_FOR = 'EVENT' THEN
1751 l2i(G_HZ_FIN_PROF_P);
1752 l2i(G_LUD);
1753 ELSE
1754 l2i(G_HZ_FIN_PROF_P);
1755 END IF;
1756 ELSE
1757 IF P_SQL_FOR = 'EVENT' THEN
1758 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1759 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1760 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1761 fnd_msg_pub.ADD;
1762 RAISE FND_API.G_EXC_ERROR;
1763 ELSE
1764 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1765 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1766 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1767 fnd_msg_pub.ADD;
1768 RAISE FND_API.G_EXC_ERROR;
1769 END IF;
1770 END IF; -- fin_prof
1771 WHEN 'HZ_FINANCIAL_REPORTS' THEN
1772 -- HZ_FINANCIAL_REPORTS can only have Org as parent
1773 IF l_node_tbl(i).PARENT_BO_CODE = 'ORG' THEN
1774 IF P_SQL_FOR = 'EVENT' THEN
1775 l2i(G_HZ_FIN_REP_P);
1776 l2i(G_LUD);
1777 ELSE
1778 l2i(G_HZ_FIN_REP_P);
1779 END IF;
1780 ELSE
1781 IF P_SQL_FOR = 'EVENT' THEN
1782 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1783 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1784 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1788 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1785 fnd_msg_pub.ADD;
1786 RAISE FND_API.G_EXC_ERROR;
1787 ELSE
1789 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1790 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1791 fnd_msg_pub.ADD;
1792 RAISE FND_API.G_EXC_ERROR;
1793 END IF;
1794 END IF; -- fin_rep
1795 WHEN 'HZ_LOCATIONS' THEN
1796 -- HZ_LOCATIONS can only have PS as parent
1797 IF l_node_tbl(i).PARENT_BO_CODE = 'PARTY_SITE' THEN
1798 -- IF l_node_tbl(i).BO_CODE = 'PARTY_SITE' THEN
1799 IF P_SQL_FOR = 'EVENT' THEN
1800 l2i(G_HZ_LOC_PS);
1801 l2i(G_LUD);
1802 ELSE
1803 l2i(G_HZ_LOC_PS);
1804 END IF;
1805 ELSE
1806 IF P_SQL_FOR = 'EVENT' THEN
1807 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1808 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1809 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1810 fnd_msg_pub.ADD;
1811 RAISE FND_API.G_EXC_ERROR;
1812 ELSE
1813 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1814 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1815 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1816 fnd_msg_pub.ADD;
1817 RAISE FND_API.G_EXC_ERROR;
1818 END IF;
1819 END IF; -- loc
1820 WHEN 'HZ_ORG_CONTACT_ROLES' THEN
1821 -- HZ_ORG_CONTACT_ROLES can only have Org Contact as parent
1822 -- for Org Contact as parent
1823 IF l_node_tbl(i).BO_CODE = 'ORG_CONTACT' THEN
1824 IF P_SQL_FOR = 'EVENT' THEN
1825 l2i(G_HZ_ORG_CONT_ROLE_OC);
1826 l2i(G_LUD);
1827 ELSE
1828 l2i(G_HZ_ORG_CONT_ROLE_OC);
1829 END IF;
1830 ELSE
1831 IF P_SQL_FOR = 'EVENT' THEN
1832 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1833 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1834 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1835 fnd_msg_pub.ADD;
1836 RAISE FND_API.G_EXC_ERROR;
1837 ELSE
1838 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1839 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1840 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1841 fnd_msg_pub.ADD;
1842 RAISE FND_API.G_EXC_ERROR;
1843 END IF;
1844 END IF; -- cont_roles
1845 WHEN 'HZ_ORG_CONTACTS' THEN
1846 -- HZ_ORG_CONTACTS Can have Org, Cust Acct Contact as parent
1847 CASE l_node_tbl(i).PARENT_BO_CODE
1848 WHEN 'ORG' THEN
1849 IF P_SQL_FOR = 'EVENT' THEN
1850 l2i(G_HZ_ORG_CONT_P1);
1851 l2i(G_HZ_ORG_CONT_P1_ORG);
1852 l2i(G_HZ_ORG_CONT_P2_ET1);
1853 -- l2i(G_HZ_ORG_CONT_P3);
1854 ELSE
1855 l2i(G_HZ_ORG_CONT_P1);
1856 l2i(G_HZ_ORG_CONT_P1_ORG);
1857 l2i(G_HZ_ORG_CONT_P2);
1858 -- l2i(G_HZ_ORG_CONT_P3);
1859 END IF;
1860 WHEN 'PERSON' THEN
1861 IF P_SQL_FOR = 'EVENT' THEN
1862 l2i(G_HZ_ORG_CONT_P1);
1863 l2i(G_HZ_ORG_CONT_P1_PER);
1864 l2i(G_HZ_ORG_CONT_P2_ET1);
1865 -- l2i(G_HZ_ORG_CONT_P3);
1866 ELSE
1867 l2i(G_HZ_ORG_CONT_P1);
1868 l2i(G_HZ_ORG_CONT_P1_PER);
1869 l2i(G_HZ_ORG_CONT_P2);
1870 -- l2i(G_HZ_ORG_CONT_P3);
1871 END IF;
1872 /*
1873 WHEN 'CUST_ACCT_CONTACT' THEN
1874 IF P_SQL_FOR = 'EVENT' THEN
1875 l2i(G_HZ_ORG_CONT_AC1);
1876 l2i(G_HZ_ORG_CONT_AC2_ET1);
1877 l2i(G_HZ_ORG_CONT_AC3);
1878 l2i(G_HZ_ORG_CONT_AC4);
1879 ELSE
1880 l2i(G_HZ_ORG_CONT_AC1);
1881 l2i(G_HZ_ORG_CONT_AC2);
1882 l2i(G_HZ_ORG_CONT_AC3);
1883 l2i(G_HZ_ORG_CONT_AC4);
1884 END IF;
1885 */
1886 ELSE
1887 IF P_SQL_FOR = 'EVENT' THEN
1888 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1889 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1890 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1891 fnd_msg_pub.ADD;
1892 RAISE FND_API.G_EXC_ERROR;
1893 ELSE
1894 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1895 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1896 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1897 fnd_msg_pub.ADD;
1898 RAISE FND_API.G_EXC_ERROR;
1899 END IF;
1900 END CASE; -- org_contact
1901 WHEN 'HZ_ORGANIZATION_PROFILES' THEN
1902 -- HZ_ORGANIZATION_PROFILES can only have Org as parent
1903 IF l_node_tbl(i).BO_CODE = 'ORG' THEN
1904 IF P_SQL_FOR = 'EVENT' THEN
1905 l2i(G_HZ_ORG_PROF_P);
1906 l2i(G_LUD);
1907 ELSE
1908 l2i(G_HZ_ORG_PROF_P);
1909 END IF;
1910 ELSE
1911 IF P_SQL_FOR = 'EVENT' THEN
1912 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1913 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1914 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1915 fnd_msg_pub.ADD;
1916 RAISE FND_API.G_EXC_ERROR;
1917 ELSE
1918 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1919 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1920 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
1921 fnd_msg_pub.ADD;
1922 RAISE FND_API.G_EXC_ERROR;
1923 END IF;
1924 END IF; -- org_profiles
1925 WHEN 'HZ_PARTIES' THEN
1926 -- HZ_PARTIES is the parent - in the context of Person and Org
1930 IF P_SQL_FOR = 'EVENT' THEN
1927 -- P_PARTY_ID is the parameter that would be passed by the caller
1928 CASE l_node_tbl(i).PARENT_BO_CODE
1929 WHEN 'ORG' THEN
1931 l2i(G_HZ_PARTIES);
1932 l2i(G_LUD);
1933 ELSE
1934 l2i(G_HZ_PARTIES);
1935 END IF;
1936 WHEN 'PERSON' THEN
1937 IF P_SQL_FOR = 'EVENT' THEN
1938 l2i(G_HZ_PARTIES);
1939 l2i(G_LUD);
1940 ELSE
1941 l2i(G_HZ_PARTIES);
1942 END IF;
1943 -- HZ_PARTIES is the child enetiy for Person Customer BO
1944 -- P_PARTY_ID is the parameter that would be passed by the caller
1945 WHEN 'PERSON_CUST' THEN
1946 -- As a speacial case, skip the node that represents the org or person
1947 -- in the context of org or person customer as the parent.
1948 -- This is needed as a performance improvement.
1949 /* IF P_SQL_FOR = 'EVENT' THEN
1950 l2i(G_HZ_PARTIES_PCUST);
1951 l2i(G_LUD);
1952 ELSE
1953 l2i(G_HZ_PARTIES_PCUST);
1954 END IF;
1955 */
1956 NULL;
1957 -- HZ_PARTIES is the child enetiy for Org Customer BO
1958 -- P_PARTY_ID is the parameter that would be passed by the caller
1959 WHEN 'ORG_CUST' THEN
1960 -- As a speacial case, skip the node that represents the org or person
1961 -- in the context of org or person customer as the parent.
1962 -- This is needed as a performance improvement.
1963 /* IF P_SQL_FOR = 'EVENT' THEN
1964 l2i(G_HZ_PARTIES_OCUST);
1965 l2i(G_LUD);
1966 ELSE
1967 l2i(G_HZ_PARTIES_OCUST);
1968 END IF;
1969 */
1970 NULL;
1971 -- HZ_PARTIES (person) as the child of Org Contact
1972 -- for Org Contact as the parent
1973 WHEN 'ORG_CONTACT' THEN
1974 IF P_SQL_FOR = 'EVENT' THEN
1975 l2i(G_HZ_PARTIES_OC_ET1);
1976 ELSE
1977 l2i(G_HZ_PARTIES_OC);
1978 END IF;
1979 ELSE
1980 IF P_SQL_FOR = 'EVENT' THEN
1981 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
1982 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1983 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1984 fnd_msg_pub.ADD;
1985 RAISE FND_API.G_EXC_ERROR;
1986 ELSE
1987 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
1988 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
1989 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
1990 fnd_msg_pub.ADD;
1991 RAISE FND_API.G_EXC_ERROR;
1992 END IF;
1993 END CASE; -- parties
1994 WHEN 'HZ_PARTY_PREFERENCES' THEN
1995 -- HZ_PARTY_PREFERENCES Org and Person as parents
1996 IF(l_node_tbl(i).BO_CODE = 'ORG' OR l_node_tbl(i).BO_CODE = 'PERSON') THEN
1997 IF P_SQL_FOR = 'EVENT' THEN
1998 l2i(G_HZ_PARTY_PREF_P);
1999 l2i(G_LUD);
2000 ELSE
2001 l2i(G_HZ_PARTY_PREF_P);
2002 END IF;
2003 ELSE
2004 IF P_SQL_FOR = 'EVENT' THEN
2005 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2006 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2007 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2008 fnd_msg_pub.ADD;
2009 RAISE FND_API.G_EXC_ERROR;
2010 ELSE
2011 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2012 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2013 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2014 fnd_msg_pub.ADD;
2015 RAISE FND_API.G_EXC_ERROR;
2016 END IF;
2017 END IF; -- party_pref
2018 WHEN 'HZ_PARTY_SITE_USES' THEN
2019 -- HZ_PARTY_SITE_USES can have only Party Site as parent
2020 IF l_node_tbl(i).BO_CODE = 'PARTY_SITE' THEN
2021 IF P_SQL_FOR = 'EVENT' THEN
2022 l2i(G_HZ_PS_USE_PS);
2023 l2i(G_LUD);
2024 ELSE
2025 l2i(G_HZ_PS_USE_PS);
2026 END IF;
2027 ELSE
2028 IF P_SQL_FOR = 'EVENT' THEN
2029 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2030 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2031 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2032 fnd_msg_pub.ADD;
2033 RAISE FND_API.G_EXC_ERROR;
2034 ELSE
2035 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2036 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2037 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2038 fnd_msg_pub.ADD;
2039 RAISE FND_API.G_EXC_ERROR;
2040 END IF;
2041 END IF; -- party_site_use
2042 WHEN 'HZ_PARTY_SITES' THEN
2043 -- HZ_PARTY_SITES Can have Org, Person, Contact and Account Site as parents
2044 CASE l_node_tbl(i).PARENT_BO_CODE
2045 WHEN 'ORG' THEN
2046 IF P_SQL_FOR = 'EVENT' THEN
2047 l2i(G_HZ_PS_P);
2048 l2i(G_LUD);
2049 ELSE
2050 l2i(G_HZ_PS_P);
2051 END IF;
2052 WHEN 'PERSON' THEN
2053 IF P_SQL_FOR = 'EVENT' THEN
2054 l2i(G_HZ_PS_P);
2055 l2i(G_LUD);
2056 ELSE
2057 l2i(G_HZ_PS_P);
2058 END IF;
2059 WHEN 'ORG_CONTACT' THEN
2060 IF P_SQL_FOR = 'EVENT' THEN
2061 l2i(G_HZ_PS_OC_ET1);
2062 ELSE
2063 l2i(G_HZ_PS_OC);
2064 END IF;
2065 /*
2066 WHEN 'CUST_ACCT_SITE' THEN
2067 IF P_SQL_FOR = 'EVENT' THEN
2068 l2i(G_HZ_PS_AS);
2069 l2i(G_LUD);
2070 ELSE
2071 l2i(G_HZ_PS_AS);
2072 END IF;
2073 */
2074 ELSE
2075 IF P_SQL_FOR = 'EVENT' THEN
2079 fnd_msg_pub.ADD;
2076 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2077 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2078 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
2080 RAISE FND_API.G_EXC_ERROR;
2081 ELSE
2082 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2083 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2084 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
2085 fnd_msg_pub.ADD;
2086 RAISE FND_API.G_EXC_ERROR;
2087 END IF;
2088 END CASE; -- PS
2089 WHEN 'HZ_PARTY_USG_ASSIGNMENTS' THEN
2090 -- can have Org or Person as Parent
2091 IF (l_node_tbl(i).BO_CODE = 'ORG' OR l_node_tbl(i).BO_CODE = 'PERSON') THEN
2092 IF P_SQL_FOR = 'EVENT' THEN
2093 l2i(G_HZ_PARTY_USG_ASSIN_P);
2094 l2i(G_LUD);
2095 ELSE
2096 l2i(G_HZ_PARTY_USG_ASSIN_P);
2097 END IF;
2098 ELSE
2099 IF P_SQL_FOR = 'EVENT' THEN
2100 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2101 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2102 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2103 fnd_msg_pub.ADD;
2104 RAISE FND_API.G_EXC_ERROR;
2105 ELSE
2106 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2107 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2108 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2109 fnd_msg_pub.ADD;
2110 RAISE FND_API.G_EXC_ERROR;
2111 END IF;
2112 END IF; -- end of 'HZ_PARTY_USG_ASSIGNMENTS'
2113 WHEN 'HZ_PERSON_INTEREST' THEN
2114 -- HZ_PERSON_INTEREST can only have Person as parent
2115 IF l_node_tbl(i).BO_CODE = 'PERSON' THEN
2116 IF P_SQL_FOR = 'EVENT' THEN
2117 l2i(G_HZ_PER_INT_P);
2118 l2i(G_LUD);
2119 ELSE
2120 l2i(G_HZ_PER_INT_P);
2121 END IF;
2122 ELSE
2123 IF P_SQL_FOR = 'EVENT' THEN
2124 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2125 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2126 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2127 fnd_msg_pub.ADD;
2128 RAISE FND_API.G_EXC_ERROR;
2129 ELSE
2130 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2131 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2132 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2133 fnd_msg_pub.ADD;
2134 RAISE FND_API.G_EXC_ERROR;
2135 END IF;
2136 END IF; -- per_int
2137 WHEN 'HZ_PERSON_LANGUAGE' THEN
2138 -- HZ_PERSON_LANGUAGE can only have Person as parent
2139 IF l_node_tbl(i).BO_CODE = 'PERSON' THEN
2140 IF P_SQL_FOR = 'EVENT' THEN
2141 l2i(G_HZ_PER_LANG_P);
2142 l2i(G_LUD);
2143 ELSE
2144 l2i(G_HZ_PER_LANG_P);
2145 END IF;
2146 ELSE
2147 IF P_SQL_FOR = 'EVENT' THEN
2148 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2149 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2150 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2151 fnd_msg_pub.ADD;
2152 RAISE FND_API.G_EXC_ERROR;
2153 ELSE
2154 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2155 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2156 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2157 fnd_msg_pub.ADD;
2158 RAISE FND_API.G_EXC_ERROR;
2159 END IF;
2160 END IF; --PL
2161 WHEN 'HZ_PERSON_PROFILES' THEN
2162 -- HZ_PERSON_PROFILES can only have Person or Person Contact as parent
2163 IF ((l_node_tbl(i).BO_CODE = 'PERSON') OR
2164 (l_node_tbl(i).BO_CODE = 'PERSON_CONTACT')) THEN
2165 IF P_SQL_FOR = 'EVENT' THEN
2166 l2i(G_HZ_PER_PROF_P);
2167 l2i(G_LUD);
2168 ELSE
2169 l2i(G_HZ_PER_PROF_P);
2170 END IF;
2171 ELSE
2172 IF P_SQL_FOR = 'EVENT' THEN
2173 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2174 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2175 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2176 fnd_msg_pub.ADD;
2177 RAISE FND_API.G_EXC_ERROR;
2178 ELSE
2179 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2180 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2181 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2182 fnd_msg_pub.ADD;
2183 RAISE FND_API.G_EXC_ERROR;
2184 END IF;
2185 END IF; -- per_prof
2186 WHEN 'HZ_RELATIONSHIPS' THEN
2187 -- HZ_RELATIONSHIPS can have Org, Person as parents
2188 CASE l_node_tbl(i).BO_CODE
2189 WHEN 'ORG' THEN
2190 IF P_SQL_FOR = 'EVENT' THEN
2191 l2i(G_HZ_REL_P);
2192 l2i(G_LUD);
2193 ELSE
2194 l2i(G_HZ_REL_P);
2195 END IF;
2196 WHEN 'PERSON' THEN
2197 IF P_SQL_FOR = 'EVENT' THEN
2198 l2i(G_HZ_REL_P);
2199 l2i(G_LUD);
2200 ELSE
2201 l2i(G_HZ_REL_P);
2202 END IF;
2203 WHEN 'ORG_CONTACT' THEN
2204 IF P_SQL_FOR = 'EVENT' THEN
2205 l2i(G_HZ_REL_OC);
2206 l2i(G_LUD);
2207 ELSE
2208 l2i(G_HZ_REL_OC);
2209 END IF;
2210 ELSE
2211 IF P_SQL_FOR = 'EVENT' THEN
2212 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2213 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2214 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2215 fnd_msg_pub.ADD;
2216 RAISE FND_API.G_EXC_ERROR;
2217 ELSE
2221 fnd_msg_pub.ADD;
2218 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2219 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2220 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2222 RAISE FND_API.G_EXC_ERROR;
2223 END IF;
2224 END CASE; -- rel
2225 WHEN 'HZ_ROLE_RESPONSIBILITY' THEN
2226 -- HZ_ROLE_RESPONSIBILITY can only have customer account contact as parent
2227 IF l_node_tbl(i).BO_CODE = 'CUST_ACCT_CONTACT' THEN
2228 IF P_SQL_FOR = 'EVENT' THEN
2229 l2i(G_HZ_ROLE_RESP_AC);
2230 l2i(G_LUD);
2231 ELSE
2232 l2i(G_HZ_ROLE_RESP_AC);
2233 END IF;
2234 ELSE
2235 IF P_SQL_FOR = 'EVENT' THEN
2236 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2237 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2238 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2239 fnd_msg_pub.ADD;
2240 RAISE FND_API.G_EXC_ERROR;
2241 ELSE
2242 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2243 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2244 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).BO_CODE);
2245 fnd_msg_pub.ADD;
2246 RAISE FND_API.G_EXC_ERROR;
2247 END IF;
2248 END IF; -- role_resp
2249 WHEN 'HZ_WORK_CLASS' THEN
2250 -- HZ_WORK_CLASS can only have Employement History as parent
2251 IF l_node_tbl(i).PARENT_BO_CODE = 'EMP_HIST' THEN
2252 IF P_SQL_FOR = 'EVENT' THEN
2253 l2i(G_HZ_WORK_CLASS_EH);
2254 l2i(G_LUD);
2255 ELSE
2256 l2i(G_HZ_WORK_CLASS_EH);
2257 END IF;
2258 ELSE
2259 IF P_SQL_FOR = 'EVENT' THEN
2260 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2261 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2262 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
2263 fnd_msg_pub.ADD;
2264 RAISE FND_API.G_EXC_ERROR;
2265 ELSE
2266 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2267 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2268 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
2269 fnd_msg_pub.ADD;
2270 RAISE FND_API.G_EXC_ERROR;
2271 END IF;
2272 END IF; -- wrk_class
2273 WHEN 'RA_CUST_RECEIPT_METHODS' THEN
2274 -- RA_CUST_RECEIPT_METHODS can have Cust Account, Account Site Use as parents
2275 CASE l_node_tbl(i).PARENT_BO_CODE
2276 WHEN 'CUST_ACCT' THEN
2277 IF P_SQL_FOR = 'EVENT' THEN
2278 l2i(G_CUST_RECEIPT_METHODS_AC);
2279 l2i(G_LUD);
2280 ELSE
2281 l2i(G_CUST_RECEIPT_METHODS_AC);
2282 END IF;
2283 WHEN 'CUST_ACCT_SITE_USE' THEN
2284 IF P_SQL_FOR = 'EVENT' THEN
2285 l2i(G_CUST_RECEIPT_METHODS_ASU);
2286 l2i(G_LUD);
2287 ELSE
2288 l2i(G_CUST_RECEIPT_METHODS_ASU);
2289 END IF;
2290 ELSE
2291 IF P_SQL_FOR = 'EVENT' THEN
2292 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2293 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2294 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
2295 fnd_msg_pub.ADD;
2296 RAISE FND_API.G_EXC_ERROR;
2297 ELSE
2298 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2299 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2300 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
2301 fnd_msg_pub.ADD;
2302 RAISE FND_API.G_EXC_ERROR;
2303 END IF;
2304 END CASE; -- ra_cust
2305 -- For Bank Account Use
2306 WHEN 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V' THEN
2307 -- IBY_FNDCPT_PAYER_ASSGN_INSTR_V can have Cust Account, Account Site Use as parents
2308 CASE l_node_tbl(i).PARENT_BO_CODE
2309 WHEN 'CUST_ACCT' THEN
2310 IF P_SQL_FOR = 'EVENT' THEN
2311 l2i(G_BANK_ACCT_USE_AC);
2312 l2i(G_LUD);
2313 ELSE
2314 l2i(G_BANK_ACCT_USE_AC);
2315 END IF;
2316 WHEN 'CUST_ACCT_SITE_USE' THEN
2317 IF P_SQL_FOR = 'EVENT' THEN
2318 l2i(G_BANK_ACCT_USE_ASU);
2319 l2i(G_LUD);
2320 ELSE
2321 l2i(G_BANK_ACCT_USE_ASU);
2322 END IF;
2323 ELSE
2324 IF P_SQL_FOR = 'EVENT' THEN
2325 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_PARENT');
2326 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2327 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
2328 fnd_msg_pub.ADD;
2329 RAISE FND_API.G_EXC_ERROR;
2330 ELSE
2331 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_PARENT');
2332 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2333 fnd_message.set_token('PARENT_BO_CODE' ,l_node_tbl(i).PARENT_BO_CODE);
2334 fnd_msg_pub.ADD;
2335 RAISE FND_API.G_EXC_ERROR;
2336 END IF;
2337 END CASE; -- bank_assign
2338 ELSE
2339 IF P_SQL_FOR = 'EVENT' THEN
2340 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_BAD_ENTITY');
2341 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2342 fnd_msg_pub.ADD;
2343 RAISE FND_API.G_EXC_ERROR;
2344 ELSE
2345 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_BAD_ENTITY');
2346 fnd_message.set_token('ENTITY_NAME' ,l_node_tbl(i).ENTITY_NAME);
2347 fnd_msg_pub.ADD;
2348 RAISE FND_API.G_EXC_ERROR;
2349 END IF;
2350 /*
2351 */
2355 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2352 END CASE; -- end of CASE for figuring out the entity name to get right SQL
2353 END LOOP; -- end of looping through hierarchy of collection.
2354
2356 hz_utility_v2pub.debug(
2357 p_message=>'all the sql segments were identified',
2358 p_prefix=>l_debug_prefix,
2359 p_msg_level=>fnd_log.level_procedure);
2360 END IF;
2361 -- writing one right paranthesis for the last SQL.
2362 -- this is because, each sql has left paranthesis as part of its global var
2363 l2i(l_rp||' --for last sql segment');
2364
2365 -- Writing the remaining right paranthesis in the hashmap
2366 -- loop through the hashmap and write the remaining.
2367 IF l_rp_tbl.COUNT >0 THEN
2368 FOR i IN l_rp_tbl.FIRST .. l_rp_tbl.LAST LOOP
2369 l2i(l_rp||'--'||l_rp_tbl(i).BO_CODE);
2370 END LOOP;
2371 END IF;
2372
2373 IF (P_SQL_FOR = 'COMPLETE') THEN
2374 l2i(l_rp||'-- for not in clause in delete statement');
2375 ELSE
2376 l2i(l_rp||'-- for in clause in update statement');
2377 END IF;
2378
2379 /* -- If the object for which completeness procedure is being generated is,
2380 -- Person Customer or Org Customer BO, then write an extra right paranthesis.
2381 -- This extra paranthesis is needed because, the hz_party sql section for
2382 -- Org or Person - as child of per or Org customer O, starts with a left paranthesis.
2383
2384 IF ((P_BO_CODE = 'ORG_CUST') OR (P_BO_CODE = 'PERSON_CUST')) THEN
2385 l2i(l_rp||'-- for org or person');
2386 END IF;
2387
2388 */
2389 -- Write ; at the end of select statemenet.
2390 -- this is needed as so far, only cursor sql statement is generated.
2391 l2i('; -- cursor for '||P_SQL_FOR||' SQL');
2392
2393 COMMIT;
2394 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2395 hz_utility_v2pub.debug(p_message=>'GENBOSQL()-',
2396 p_prefix=>l_debug_prefix,
2397 p_msg_level=>fnd_log.level_procedure);
2398 END IF;
2399 P_STATUS := TRUE; -- this means sql generation is successfull
2400 /*
2401 EXCEPTION
2402 WHEN others THEN
2403 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2404 hz_utility_v2pub.debug(
2405 p_message=>'in excp block of genBOSQL()'||SQLERRM,
2406 p_prefix=>l_debug_prefix,
2407 p_msg_level=>fnd_log.level_procedure);
2408 END IF;
2409 P_STATUS := FALSE;
2410 */
2411 END; -- genBOSQL()
2412 ----------------------------------------------------------------------------
2413 /* PROCEDURE: genCompletenessProc
2414 Purpose: This procedure is to generate the completeness procedure for all
2415 the high level business objects - ORG, PERSON, ORG_CUST, PERSON_CUST.
2416 Called from: This package
2417 Called by: gen_pkg_body()
2418
2419 */
2420 PROCEDURE genCompletenessProc IS
2421
2422 -- local variable for procedure name
2423 l_procName VARCHAR2(30) := 'BO_COMPLETE_CHECK';
2424 l_comment VARCHAR2(100) := 'To determine completeness of BOs. Non-complete BOs will be deleted';
2425 l_msg VARCHAR2(150);
2426 l_prefix VARCHAR2(15) := 'GENCOMPSQL'; -- holds the debug prefix for this procedure
2427 l_gen_prefix VARCHAR2(15) := 'COMPSQL'; -- holds the debug prefix for generated procedure
2428 l_ret_status BOOLEAN := TRUE;
2429 l_sql_for VARCHAR2(30) := 'COMPLETE';
2430 l_param_name VARCHAR2(30) := 'P_BO_CODE';
2431
2432 BEGIN
2433 if fnd_log.level_procedure>=fnd_log.g_current_runtime_level then
2434 hz_utility_v2pub.debug(p_message=>'genCompletenessProc ()+',
2435 p_prefix=>l_prefix,
2436 p_msg_level=>fnd_log.level_procedure);
2437 end if;
2438 /*
2439 This procedure will take P_BO_CODE as a parameter.
2440 For each BO, a separate delete statement must be generated.
2441 Flow:
2442 . generate procedure header by calling procBegin()
2443 . generate debug mesg
2444 . get BO Codes
2445 . generate if statement to check if the parameter passed = G_PER_BO_CODE
2446 . generate delete statements (by calling genBOSQL(G_PER_BO_CODE));
2447 . if previous delete statement generation is successfull
2448 . generate commit statement
2449 . generate debug mesg
2450 . if previous delete statement generation is un-successfull
2451 . debug mesg
2452 . generate debug mesg
2453 . raise exception
2454 . generate if statement to check if the parameter passed = G_ORG_BO_CODE
2455 . generate delete statements (by calling genBOSQL(G_ORG_BO_CODE));
2456 . if previous delete statement generation is successfull
2457 . generate commit statement
2458 . generate debug mesg
2459 . if previous delete statement generation is un-successfull
2460 . debug mesg
2461 . generate debug mesg
2462 . raise exception
2463 . generate if statement to check if the parameter passed = G_PER_CUST_BO_CODE
2464 . generate delete statements (by calling genBOSQL(G_PER_CUST_BO_CODE));
2465 . if previous delete statement generation is successfull
2466 . generate commit statement
2467 . generate debug mesg
2468 . if previous delete statement generation is un-successfull
2469 . debug mesg
2470 . generate debug mesg
2471 . raise exception
2472 . generate if statement to check if the parameter passed = G_ORG_CUST_BO_CODE
2473 . generate delete statements (by calling genBOSQL(G_ORG_CUST_BO_CODE));
2474 . if previous delete statement generation is successfull
2475 . generate commit statement
2476 . generate debug mesg
2477 . if previous delete statement generation is un-successfull
2478 . debug mesg
2479 . generate debug mesg
2480 . raise exception
2481 . generate procedure tail by calling procEnd()
2482 */
2486 li('-- local variables');
2483 -- To write the procedure header
2484 procBegin (l_procName, l_comment, l_param_name);
2485 li(' ');
2487 li(' ');
2488 l('BEGIN');
2489 -- write the code to get BO codes.
2490 l_msg := l_procName||'()+';
2491 writeDebugMesg(l_msg, l_gen_prefix);
2492
2493 li('-- delete root nodes for BOs that are not complete');
2494 l2i('-- delete statement for '||G_PER_BO_CODE||' BO');
2495 li(' ');
2496 -- generate delete statement for Person BO
2497 li('IF P_BO_CODE ='''||G_PER_BO_CODE||''' THEN');
2498 -- generate delete statements by calling
2499 genBOSQL(G_PER_BO_CODE, l_sql_for, l_ret_status);
2500 IF l_ret_status THEN
2501 -- generate a commit statement after each delete stmt
2502 l2i(' COMMIT; -- commiting the deletes done so far. ');
2503 l_msg := 'completeness del stmt executed for '||G_PER_BO_CODE||' BO';
2504 writeDebugMesg(l_msg, l_gen_prefix);
2505 li('END IF; -- end of check for '||G_PER_BO_CODE);
2506 ELSE
2507 l_msg := 'Unable to generate the completeness() del stmt for PERSON BO';
2508 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2509 hz_utility_v2pub.debug(
2510 p_message=>l_msg,
2511 p_prefix=>l_prefix,
2512 p_msg_level=>fnd_log.level_procedure);
2513 END IF;
2514 -- raise to calling program, in this case BOD API
2515 l_msg := 'error generating completeness del stmt for '||G_PER_BO_CODE||' BO';
2516 writeDebugMesg(l_msg||sqlerrm, l_gen_prefix);
2517 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_DEL_ERROR');
2518 fnd_message.set_token('BO_CODE', G_PER_BO_CODE);
2519 fnd_msg_pub.ADD;
2520 RAISE FND_API.G_EXC_ERROR;
2521 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2522 END IF;
2523 li(' ');
2524 l2i('-- delete statement for '||G_ORG_BO_CODE||' BO');
2525 -- generate delete statement for Org BO
2526 li('IF P_BO_CODE ='''||G_ORG_BO_CODE||''' THEN');
2527 li(' ');
2528 genBOSQL(G_ORG_BO_CODE, l_sql_for, l_ret_status);
2529 IF l_ret_status THEN
2530 -- generate a commit statement after each delete stmt
2531 l2i(' COMMIT; -- commiting the deletes done so far.');
2532 l_msg := 'completeness del stmt executed for '||G_ORG_BO_CODE||' BO';
2533 writeDebugMesg(l_msg, l_gen_prefix);
2534 li('END IF; -- end of check for '||G_ORG_BO_CODE);
2535 ELSE
2536 l_msg := 'Unable to generate the completeness() del stmt for '||G_ORG_BO_CODE||' BO';
2537 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2538 hz_utility_v2pub.debug(
2539 p_message=>l_msg,
2540 p_prefix=>l_prefix,
2541 p_msg_level=>fnd_log.level_procedure);
2542 END IF;
2543 -- raise to calling program, in this case BOD API
2544 l_msg := 'error generating completeness del stmt for '||G_ORG_BO_CODE||' ';
2545 writeDebugMesg(l_msg||sqlerrm, l_gen_prefix);
2546 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_DEL_ERROR');
2547 fnd_message.set_token('BO_CODE', G_ORG_BO_CODE);
2548 fnd_msg_pub.ADD;
2549 RAISE FND_API.G_EXC_ERROR;
2550 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2551 END IF;
2552 li(' ');
2553 l2i('-- delete statement for '||G_PER_CUST_BO_CODE||' BO');
2554 -- generate delete statement for Person Cust BO
2555 li('IF P_BO_CODE ='''||G_PER_CUST_BO_CODE||''' THEN');
2556 li(' ');
2557 genBOSQL(G_PER_CUST_BO_CODE, l_sql_for, l_ret_status);
2558 IF l_ret_status THEN
2559 -- generate a commit statement after each delete stmt
2560 l2i(' COMMIT; -- commiting the deletes done so far.');
2561 l_msg := 'completeness del stmt executed for '||G_PER_CUST_BO_CODE||' BO';
2562 writeDebugMesg(l_msg, l_gen_prefix);
2563 li('END IF; -- end of check for '||G_PER_CUST_BO_CODE);
2564 ELSE
2565 l_msg := 'Unable to generate the completeness() del stmt for '||G_PER_CUST_BO_CODE||' BO';
2566 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2567 hz_utility_v2pub.debug(
2568 p_message=>l_msg,
2569 p_prefix=>l_prefix,
2570 p_msg_level=>fnd_log.level_procedure);
2571 END IF;
2572 -- raise to calling program, in this case BOD API
2573 l_msg := 'error generating completeness del stmt for '||G_PER_CUST_BO_CODE||' ';
2574 writeDebugMesg(l_msg||sqlerrm, l_gen_prefix);
2575 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_DEL_ERROR');
2576 fnd_message.set_token('BO_CODE', G_PER_CUST_BO_CODE);
2577 fnd_msg_pub.ADD;
2578 RAISE FND_API.G_EXC_ERROR;
2579 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2580 END IF;
2581 li(' ');
2582 l2i('-- delete statement for '||G_ORG_CUST_BO_CODE||' BO');
2583 -- generate delete statement for Org Cust BO
2584 li('IF P_BO_CODE ='''||G_ORG_CUST_BO_CODE||''' THEN');
2585 li(' ');
2586 genBOSQL(G_ORG_CUST_BO_CODE, l_sql_for, l_ret_status);
2587 IF l_ret_status THEN
2588 -- generate a commit statement after each delete stmt
2589 l2i(' COMMIT; -- commiting the deletes done so far.');
2590 l_msg := 'completeness del stmt executed for '||G_ORG_CUST_BO_CODE||' BO';
2591 writeDebugMesg(l_msg, l_gen_prefix);
2592 li('END IF; -- end of check for '||G_PER_CUST_BO_CODE);
2593 ELSE
2594 l_msg := 'Unable to generate the completeness() del stmt for '||G_ORG_CUST_BO_CODE||' BO';
2595 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2596 hz_utility_v2pub.debug(
2597 p_message=>l_msg,
2598 p_prefix=>l_prefix,
2599 p_msg_level=>fnd_log.level_procedure);
2600 END IF;
2601 -- raise to calling program, in this case BOD API
2602 l_msg := 'error generating completeness del stmt for '||G_ORG_CUST_BO_CODE||' ';
2603 writeDebugMesg(l_msg||sqlerrm, l_gen_prefix);
2604 fnd_message.set_name('AR', 'HZ_BES_BO_COMPLETE_DEL_ERROR');
2605 fnd_message.set_token('BO_CODE', G_ORG_CUST_BO_CODE);
2606 fnd_msg_pub.ADD;
2607 RAISE FND_API.G_EXC_ERROR;
2608 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2609 END IF;
2610 l_msg := l_procName||'()-';
2614 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2611 writeDebugMesg(l_msg, l_gen_prefix);
2612
2613 procEnd(l_procName);
2615 hz_utility_v2pub.DEBUG (
2616 p_message=>'genCompletenessProc()-',
2617 p_prefix=>l_prefix,
2618 p_msg_level=>fnd_log.level_procedure);
2619 END IF;
2620 END genCompletenessProc;
2621
2622 ----------------------------------------------------------------------------
2623 /* PROCEDURE: genEvtTypeProc
2624 Purpose: This procedure is to generate the event type check procedure(s) for
2625 high level business objects - ORG, PERSON, ORG_CUST, PERSON_CUST.
2626 Called from: This package
2627 Called by: gen_pkg_body()
2628
2629 */
2630 PROCEDURE genEvtTypeProc IS
2631 -- local variable for procedure name
2632 l_procName VARCHAR2(30) := 'BO_EVENT_CHECK';
2633 l_comment VARCHAR2(100) := 'To determine event type of BOs.';
2634 l_msg VARCHAR2(150);
2635 l_prefix VARCHAR2(15) := 'GENEVTSQL'; -- holds the debug prefix for this procedure
2636 l_gen_prefix VARCHAR2(15) := 'EVTSQL'; -- holds the debug prefix for generated proc
2637 l_ret_status BOOLEAN := TRUE;
2638 l_sql_for VARCHAR2(30) := 'EVENT';
2639 l_param_name VARCHAR2(30) := 'P_BO_CODE';
2640
2641 BEGIN
2642 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2643 hz_utility_v2pub.debug(p_message=>'genEvtTypeProc()+',
2644 p_prefix=>l_prefix,
2645 p_msg_level=>fnd_log.level_procedure);
2646 END IF;
2647
2648 /*
2649 This procedure will generate BO_EVT_CHECK() procedure in HZ_BES_BO_SITE_UTIL_PKG
2650 package. The BO_EVT_CHECK() procedure will take P_BO_CODE as a parameter.
2651 For each BO, a separate Update statement must be generated. Each of this update
2652 statement will update all those rows that are candidates for Update event.
2653 This update statement will go after all those records for which the event type
2654 must be identified.
2655 Flow:
2656 . generate procedure header by calling procBegin()
2657 . generate debug mesg
2658 . get BO Codes
2659 . generate if condition on P_BO_CODE = PERSON
2660 . For PERSON BO generate the update statement
2661 . add debug mesg
2662 . generate COMMIT statement
2663 . add debug mesg
2664 . generate else if condition on P_BO_CODE = ORG
2665 . For ORG BO generate the update statement
2666 . add debug mesg
2667 . generate COMMIT statement
2668 . add debug mesg
2669 . generate else if condition on P_BO_CODE = PERSON_CUST
2670 . For PERSON_CUST BO generate the update statement
2671 . add debug mesg
2672 . generate COMMIT statement
2673 . add debug mesg
2674 . generate else if condition on P_BO_CODE = ORG_CUST
2675 . For ORG_CUST BO generate the update statement
2676 . add debug mesg
2677 . generate COMMIT statement
2678 . add debug mesg
2679 . generate else condition
2680 . add debug mesg
2681 . generate raise exception statement.
2682 . generate end if statement
2683 . generate the procedure tail
2684
2685 */
2686 -- To write the procedure header
2687 procBegin (l_procName, l_comment, l_param_name);
2688 li(' ');
2689 l('BEGIN');
2690 -- write the code to get BO codes.
2691 l_msg := l_procName||'()+';
2692 writeDebugMesg(l_msg, l_gen_prefix);
2693 -- generate delete statements by calling
2694 li('-- update nodes for BOs that are already complete');
2695 li('-- update statement for '||G_PER_BO_CODE||' BO');
2696 li(' ');
2697 -- generate update statement for Person BO
2698 li('IF P_BO_CODE ='''||G_PER_BO_CODE||''' THEN');
2699 genBOSQL(G_PER_BO_CODE, l_sql_for, l_ret_status);
2700 IF l_ret_status THEN
2701 -- generate a commit statement after each delete stmt
2702 li(' COMMIT; -- commiting the updates done so far for '||G_PER_BO_CODE);
2703 l_msg := 'evt type update stmt executed for '||G_PER_BO_CODE||' BO';
2704 writeDebugMesg(l_msg, l_gen_prefix);
2705 li('END IF; -- end of check for '||G_PER_BO_CODE);
2706 ELSE
2707 l_msg := 'Unable to generate the evt type upd stmt for PERSON BO';
2708 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2709 hz_utility_v2pub.debug(
2710 p_message=>l_msg,
2711 p_prefix=>l_prefix,
2712 p_msg_level=>fnd_log.level_procedure);
2713 END IF;
2714 -- raise to calling program, in this case BOD API
2715 l_msg := 'error generating evt type upd stmt for '||G_PER_BO_CODE||' BO';
2716 writeDebugMesg(l_msg||sqlerrm, l_gen_prefix);
2717 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_UPDATE_ERROR');
2718 fnd_message.set_token('BO_CODE', G_PER_BO_CODE);
2719 fnd_msg_pub.ADD;
2720 RAISE FND_API.G_EXC_ERROR;
2721 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2722 END IF;
2723 -- generate update statement for Org BO
2724 li(' ');
2725 li('IF P_BO_CODE ='''||G_ORG_BO_CODE||''' THEN');
2726 genBOSQL(G_ORG_BO_CODE, l_sql_for, l_ret_status);
2727 IF l_ret_status THEN
2728 -- generate a commit statement after each delete stmt
2729 li(' COMMIT; -- commiting the updates done so far for '||G_ORG_BO_CODE);
2730 l_msg := 'evt type update stmt executed for '||G_ORG_BO_CODE||' BO';
2731 writeDebugMesg(l_msg, l_gen_prefix);
2732 li('END IF; -- end of check for '||G_ORG_BO_CODE);
2733 ELSE
2737 p_message=>l_msg,
2734 l_msg := 'Unable to generate the evt type upd stmt for Org BO';
2735 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2736 hz_utility_v2pub.debug(
2738 p_prefix=>l_prefix,
2739 p_msg_level=>fnd_log.level_procedure);
2740 END IF;
2741 -- raise to calling program, in this case BOD API
2742 l_msg := 'error generating evt type upd stmt for '||G_ORG_BO_CODE||' BO';
2743 writeDebugMesg(l_msg||sqlerrm, l_gen_prefix);
2744 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_UPDATE_ERROR');
2745 fnd_message.set_token('BO_CODE', G_ORG_BO_CODE);
2746 fnd_msg_pub.ADD;
2747 RAISE FND_API.G_EXC_ERROR;
2748 END IF;
2749 -- generate update statement for Person Customer BO
2750 li('IF P_BO_CODE ='''||G_PER_CUST_BO_CODE||''' THEN');
2751 genBOSQL(G_PER_CUST_BO_CODE, l_sql_for, l_ret_status);
2752 IF l_ret_status THEN
2753 -- generate a commit statement after each delete stmt
2754 li(' COMMIT; -- commiting the updates done so far for '||G_PER_CUST_BO_CODE);
2755 l_msg := 'evt type update stmt executed for '||G_PER_CUST_BO_CODE||' BO';
2756 writeDebugMesg(l_msg, l_gen_prefix);
2757 li('END IF; -- end of check for '||G_PER_CUST_BO_CODE);
2758 ELSE
2759 l_msg := 'Unable to generate the evt type upd stmt for Person Customer BO';
2760 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2761 hz_utility_v2pub.debug(
2762 p_message=>l_msg,
2763 p_prefix=>l_prefix,
2764 p_msg_level=>fnd_log.level_procedure);
2765 END IF;
2766 -- raise to calling program, in this case BOD API
2767 l_msg := 'error generating evt type upd stmt for '||G_PER_CUST_BO_CODE||' BO';
2768 writeDebugMesg(l_msg||sqlerrm, l_gen_prefix);
2769 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_UPDATE_ERROR');
2770 fnd_message.set_token('BO_CODE', G_PER_CUST_BO_CODE);
2771 fnd_msg_pub.ADD;
2772 RAISE FND_API.G_EXC_ERROR;
2773 END IF;
2774 -- generate update statement for Org Customer BO
2775 li('IF P_BO_CODE ='''||G_ORG_CUST_BO_CODE||''' THEN');
2776 genBOSQL(G_ORG_CUST_BO_CODE, l_sql_for, l_ret_status);
2777 IF l_ret_status THEN
2778 -- generate a commit statement after each delete stmt
2779 li(' COMMIT; -- commiting the updates done so far for '||G_ORG_CUST_BO_CODE);
2780 l_msg := 'evt type update stmt executed for '||G_ORG_CUST_BO_CODE||' BO';
2781 writeDebugMesg(l_msg, l_gen_prefix);
2782 li('END IF; -- end of check for '||G_ORG_CUST_BO_CODE);
2783 ELSE
2784 l_msg := 'Unable to generate the evt type upd stmt for Person Customer BO';
2785 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2786 hz_utility_v2pub.debug(
2787 p_message=>l_msg,
2788 p_prefix=>l_prefix,
2789 p_msg_level=>fnd_log.level_procedure);
2790 END IF;
2791 -- raise to calling program, in this case BOD API
2792 l_msg := 'error generating evt type upd stmt for '||G_ORG_CUST_BO_CODE||' BO';
2793 writeDebugMesg(l_msg||sqlerrm, l_gen_prefix);
2794 fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_UPDATE_ERROR');
2795 fnd_message.set_token('BO_CODE', G_ORG_CUST_BO_CODE);
2796 fnd_msg_pub.ADD;
2797 RAISE FND_API.G_EXC_ERROR;
2798 END IF;
2799 l_msg := l_procName||'()-';
2800 writeDebugMesg(l_msg, l_gen_prefix);
2801
2802 procEnd(l_procName);
2803 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2804 hz_utility_v2pub.DEBUG (
2805 p_message=>'genEvtTypeProc()-',
2806 p_prefix=>l_prefix,
2807 p_msg_level=>fnd_log.level_procedure);
2808 END IF;
2809 END genEvtTypeProc;
2810
2811
2812 ----------------------------------------------------------------------------
2813 /* PROCEDURE: gen_pkg_body
2814 Purpose: this is the umbrella procedure which gets called
2815 from the BOD API.
2816 This procedure is used to create the entire package and compile it.
2817 Called from: Both BOD update and create APIs will calls this.
2818 Called by: bo_gen_main()
2819
2820 */
2821 PROCEDURE gen_pkg_body
2822 (P_STATUS OUT NOCOPY BOOLEAN)
2823 IS
2824 l_debug_prefix VARCHAR2(15) := 'GENPKG:';
2825 l_pkg_name VARCHAR2(30) := 'HZ_BES_BO_SITE_UTIL_PKG';
2826 BEGIN -- gen_pkg_body
2827 if fnd_log.level_procedure>=fnd_log.g_current_runtime_level then
2828 hz_utility_v2pub.debug(p_message=>'gen_pkg_body()+',
2829 p_prefix=>l_debug_prefix,
2830 p_msg_level=>fnd_log.level_procedure);
2831 end if;
2832
2833 /*
2834 Flow:
2835 . Generate the genPkgBdyHdr()
2836 . Generate the completeness procedure()
2837 . Generate the event type procedure()
2838 . Generate the pkg body() and compile
2839 */
2840 P_STATUS := FALSE; -- assigning the retun status to error.
2841 genPkgBdyHdr(l_pkg_name);
2842 genCompletenessProc();
2843 genEvtTypeProc();
2844 genPkgBdyTail(l_pkg_name);
2845
2846 COMMIT;
2847 P_STATUS := TRUE; -- assigning the return status to success
2848 if fnd_log.level_procedure>=fnd_log.g_current_runtime_level then
2849 hz_utility_v2pub.debug(p_message=>'gen_pkg_body()-',
2850 p_prefix=>l_debug_prefix,
2851 p_msg_level=>fnd_log.level_procedure);
2852 END IF;
2853
2854 END ; -- gen_pkg_body()
2855 ------------------------------------------------------------------------------
2856
2857 PROCEDURE gen_pkg_main (
2858 errbuf OUT NOCOPY VARCHAR2,
2859 retcode OUT NOCOPY VARCHAR2) IS
2860 -- local variables
2861 l_ret_status BOOLEAN;
2862
2863 BEGIN
2864
2865 gen_pkg_body(l_ret_status);
2866
2867 IF l_ret_status THEN
2868 retcode := 0; -- setting the return code to success
2869 ELSE
2870 retcode := 2;
2871 END IF;
2872
2873 EXCEPTION
2877 errbuf := errbuf || logerror;
2874 WHEN FND_API.G_EXC_ERROR THEN
2875 outandlog('Error: Aborting concurrent program');
2876 retcode := 2;
2878 FND_FILE.close;
2879 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2880 outandlog('Error: Aborting concurrent program');
2881 retcode := 2;
2882 errbuf := errbuf || logerror;
2883 FND_FILE.close;
2884 WHEN OTHERS THEN
2885 outandlog('Error: Aborting concurrent program');
2886 retcode := 2;
2887 errbuf := errbuf || logerror;
2888 FND_FILE.close;
2889 END gen_pkg_main;
2890
2891 END HZ_BES_BO_GEN_PKG; -- end of pkg body
[Home] [Help]