1 PACKAGE BODY HZ_BES_BO_UTIL_PKG AS
2 /*$Header: ARHBESUB.pls 120.9.12000000.2 2007/07/13 23:43:24 awu ship $ */
3 ----------------------------------------------------------------------------
4 ----------------------------------------------------------------------------
5
6 g_debug_prefix CONSTANT VARCHAR2(30) := 'BOUTILPKG:';
7 -----------------------------------------------------------------
8 -- internal procedures
9 -----------------------------------------------------------------
10 /**
11 * Procedure to write text to the log file
12 **/
13 ----------------------------------------------
14 PROCEDURE log(
15 message IN VARCHAR2,
16 newline IN BOOLEAN DEFAULT TRUE,
17 p_prefix IN VARCHAR2,
18 p_module IN VARCHAR2 ) IS
19 BEGIN
20 /*
21 FND_FILE.LOG = 1 - means log file
22 FND_FILE.LOG = 2 - means out file
23 */
24 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
25 hz_utility_v2pub.DEBUG
26 (p_message=> message,
27 p_prefix=>p_prefix,
28 p_msg_level=>fnd_log.level_procedure,
29 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
30 p_module => p_module);
31 END IF;
32 IF newline THEN
33 FND_FILE.put_line(FND_FILE.LOG,message);
34 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
35 ELSE
36 FND_FILE.put_line(FND_FILE.LOG,message);
37 END IF;
38 END log;
39 -----------------------------------------------------------------
40 /*
41 the following procedures a.k.a explode procedures (exp_<table_name>
42 are meant to write the parent node, child BO information to BOT
43 <list the procedures here>
44 */
45 -----------------------------------------------------------------
46 /*
47 Procedure name: ei_hz_contact_preferences()
48 Scope: external
49 Purpose: This procedure two activities on BOT table.
50 Writes the parent node record for HZ_CONTACT_PREFERENCE.
51 Called From: This pkg
52 Called By: populate_missing_links()
53 */
54 -----------------------------------------------------------------
55 PROCEDURE ei_HZ_CONTACT_PREFERENCES IS
56 l_debug_prefix VARCHAR2(30) := 'EXP_CNT_PREF:';
57 BEGIN
58 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
59 hz_utility_v2pub.DEBUG
60 (p_message=>'ei_HZ_CONTACT_PREFERENCES()+',
61 p_prefix=>l_debug_prefix,
62 p_msg_level=>fnd_log.level_procedure,
63 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
64 p_module =>'ei_cpp');
65 END IF;
66 /*
67 Following entities are parents of HZ_CONTACT_PREFERENCE
68 1. HZ_CONTACT_POINTS (Ph, Web, EFT, Email, SMS, TLX, EDI)
69 2. HZ_PARTIES (Org, Person, OrgContact)
70 3. HZ_PARTY_SITES
71
72 Contact Point has following parents
73 1. HZ_PARTIES (Org, Person, OrgContact)
74 2. HZ_PARTY_SITES
75
76 Party Site has following parents
77 1. HZ_PARTIES (Org, Person, OrgContact)
78
79 Hence, contact preference can exist in twenty five different scenarios.
80 The different combinations of (parent, grand parent) are :
81 1. (Phone, Org) 2. (Web, Org) 3. (TLX, Org) 4. (Email, Org) 5. (EDI, Org)
82 6. (EFT, Org) 7. (Phone, Person) 8. (Web, Person) 9. (Email, Person)
83 10. (SMS, Person) 11. (Phone, OrgContact) 12. (Web, OrgContact)
84 13. (TLX, OrgContact) 14. (Email, OrgContact) 15. (SMS, OrgContact)
85 16. (Phone, PS) 17.(TLX, PS) 18. (Email, PS) 19. (Web, PS) 20. (Org, null)
86 21. (Person, null) 22. (OrgContact, null) 23. (PS, Org) 24. (PS, Person)
87 25. (PS, OrgContact)
88
89 The following SQL gets the Parent and Grand parent info of each contact preference
90 record that was not already processed in BOT.
91
92 Test Cases for the following SQL are:
93 Create HZ_CONTACT_PREFERENCE rec in BOT with (Parent, Grand Parent)
94 combinations existing in TCA data model
95 1. (any CP, Org) -- tested
96 2. (any CP, Person) -- tested
97 3. (any CP, OrgContact)
98 4. (any CP, PS) -- tested
99 5. (Org, null) -- tested
100 6. (Person, null) -- tested
101 7. (OrgContact, null)
102 8. (PS, Org) -- tested
103 9. (PS, Person) -- tested
104 10. (PS, OrgContact) -- tested
105 11. (any CP, PS, Person) -- tested
106 12. (any CP, PS, Org)
107 */
108
109 /* A note on the Structure of the insert statement
110 1. The parent record is tracked by the insert statement.
111 2. The "inner select" acts as a data source for "outer select"
112 3. The "outer select" uses "select to exclude the parents
113 that were already written to BOT"
114 4. There may be duplicate rows for any given parent retured by the "inner select".
115 This is because, in a given execution of the following SQL,
116 there may be multiple children for a given parent.
117
118 Each child contributes in getting its parent. This is as per the design
119 of "inner select".
120
121 To avoid duplicate rows of a parent returned by the siblings,
122 the inner select ranks all the parents duplicate parents.
123 The "outer select" filters on parents with rank = 1.
124 This clause helps to filter out the duplicate parent rows,
125 before data was inserted by insert statement.
126
127 5. The "inner select" is operating on the child record and trying to identify
128 the parent and grand parent information.
129 The parent information of the child record will be child
130 (current/its) information for the parent record.
131 The grand parent information of the child record will be parent info
132 of the parent record.
133 Because of this reason, "inner select" statement aliases the columns.
134
135 example:
136
137 child record:
138 child_id: 123, child_entity_name: hz_contact_preferences,
139 child_bo: null, parent_bo: null, parent_entity_name: PS,
140 parent_id: 234
141
142 The "inner select" fetches above record from BOT and identifies
143 its parent, grand parent information and present it as follows:
144
145 parent_id aliased as child_id: 234
146 parent_entity_name aliased as child_entity_name: HZ_PARTY_SITES
147 parent_bo aliased as child_bo: PS
148 grand_parent_id aliased as parent id: 456
149 grand_parent_entity_name aliased as parent_entity_name: HZ_PARTIES
150 grand_parent_bo aliased as parent_bo: Org
151
152 Insert statement will take this result and write it as
153 child record:
154 child_id: 234, child_entity_name: HZ_PARTY_SITES,
155 child_bo: PS, parent_bo: Org, parent_entity_name: HZ_PARTIES,
156 parent_id: 456
157 6. It is non-trivial to figure out the business object codes for both parent
158 and grand parent, grand parent identifier or grand parent entity name.
159 To do this, "inner select" uses case statement on parent_entity_name.
160 Some times, an embedded SQL is necessary to fgure out this.
161 Example:
162 Child is HZ_CONTACT_PREFERENCE.
163 Parent is HZ_CONTACT_POINTS and it's parent is Party.
164 To figure out the grand parent bo code, SQL is necessary to run against
165 hz_parties to figure out the party_type based on owner_table_id of the
166 hz_contact_points table.
167 */
168
169 INSERT INTO HZ_BUS_OBJ_TRACKING
170 (CHILD_ENTITY_NAME, CHILD_ID,
171 CHILD_OPERATION_FLAG, POPULATED_FLAG,
172 LAST_UPDATE_DATE, CHILD_BO_CODE,
173 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
174 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
175 CHILD_OPERATION_FLAG, POPULATED_FLAG, LAST_UPDATE_DATE,
176 CHILD_BO_CODE, PARENT_ENTITY_NAME,
177 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
178 FROM
179 (SELECT -- inner select
180 PARENT_ENTITY_NAME child_entity_name
181 ,PARENT_ID child_id
182 ,PARENT_BO_CODE CHILD_BO_CODE
183 ,'U' child_operation_flag
184 ,'Y' populated_flag
185 ,LAST_UPDATE_DATE
186 ,RANK()
187 OVER (PARTITION BY PARENT_ENTITY_NAME, PARENT_ID, CHILD_BO_CODE, PARENT_BO_CODE
188 ORDER BY LAST_UPDATE_DATE) as cprank
189 ,CASE PARENT_ENTITY_NAME
190 WHEN 'HZ_CONTACT_POINTS' THEN -- identify GrandParentEntityName when CP is the parent of CPP
191 (SELECT
192 CASE OWNER_TABLE_NAME
193 WHEN 'HZ_PARTIES' THEN -- identify GrandParentEntityName when CP is the parent of CPP, Party is parent of CP
194 (SELECT
195 CASE party_type
196 WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
197 WHEN 'PERSON' THEN 'HZ_PARTIES'
198 WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
199 ELSE NULL
200 END
201 FROM hz_parties
202 WHERE party_id = owner_table_id)
203 WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentEntityName when CP is parent of CPP, PS is parent of CP
204 'HZ_PARTY_SITES'
205 ELSE NULL
206 END
207 FROM HZ_CONTACT_POINTS
208 WHERE contact_point_id = PARENT_ID)
209 WHEN 'HZ_PARTIES' THEN -- identify GrandParentEntityName when Party is parent of CPP
210 NULL
211 WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentEntityName when PS is parent of CPP
212 (SELECT
213 CASE party_type
214 WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
215 WHEN 'PERSON' THEN 'HZ_PARTIES'
216 WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
217 ELSE NULL
218 END
219 FROM HZ_PARTIES
220 WHERE party_id = (SELECT ps.party_id
221 FROM HZ_PARTY_SITES ps
222 WHERE ps.party_site_id = PARENT_ID))
223 WHEN 'HZ_ORG_CONTACTS' THEN -- identify GrandParentEntityName when OrgContact is parent of CPP
224 'HZ_PARTIES'
225 ELSE
226 NULL
227 END parent_entity_name, -- this is the grand parent entity name of cont pref - written as parent entity
228 CASE PARENT_ENTITY_NAME
229 WHEN 'HZ_CONTACT_POINTS' THEN -- identify GrandParentEntityId when CP is parent of CPP
230 (SELECT
231 CASE OWNER_TABLE_NAME
232 WHEN 'HZ_PARTIES' THEN -- identify GrandParentEntityId when CP is parent of CPP, party is parentOf CP
233 (SELECT
234 CASE party_type
235 WHEN 'ORGANIZATION' THEN OWNER_TABLE_ID
236 WHEN 'PERSON' THEN OWNER_TABLE_ID
237 WHEN 'PARTY_RELATIONSHIP' THEN
238 (SELECT oc.org_contact_id
239 FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
240 WHERE r.relationship_id = oc.party_relationship_id
241 AND r.subject_type = 'PERSON'
242 AND r.object_type = 'ORGANIZATION'
243 AND r.party_id = p.party_id)
244 ELSE NULL
245 END
246 FROM hz_parties p
247 WHERE p.party_id = owner_table_id)
248 WHEN 'HZ_PARTY_SITES' THEN OWNER_TABLE_ID -- identify GrandParentEntityId when CP is parent of CPP, PS parentOf CP
249 ELSE NULL
250 END
251 FROM HZ_CONTACT_POINTS
252 WHERE contact_point_id = PARENT_ID)
253 WHEN 'HZ_PARTIES' THEN -- identify GrandParentEntityId when Party is parent of CPP
254 NULL
255 WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentEntityId when PS is parent of CPP
256 (SELECT
257 CASE p.party_type
258 WHEN 'ORGANIZATION' THEN p.party_id -- identify GrandParentEntityId when PS is parent of CPP, Org parentOf PS
259 WHEN 'PERSON' THEN p.party_id -- identify GrandParentEntityId when PS is parent of CPP, Per parentOf PS
260 WHEN 'PARTY_RELATIONSHIP' THEN -- identify GrandParentEntityId when PS is parent of CPP, Rel parentOf PS
261 (SELECT oc.org_contact_id
262 FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
263 WHERE r.relationship_id = oc.party_relationship_id
264 AND r.party_id = p.party_id
265 AND r.subject_type = 'PERSON'
266 AND r.object_type = 'ORGANIZATION')
267 ELSE NULL
268 END
269 FROM hz_parties p
270 WHERE p.party_id = (select ps.party_id
271 from HZ_PARTY_SITES ps
272 where ps.party_site_id = PARENT_ID))
273 WHEN 'HZ_ORG_CONTACTS' THEN -- identify GrandParentEntityId when OrgContact is parent of CPP
274 (SELECT r.object_id
275 FROM hz_relationships r, hz_org_contacts oc
276 WHERE oc.org_contact_id = PARENT_ID
277 AND oc.party_relationship_id = r.relationship_id
278 AND subject_type ='PERSON'
279 AND object_type = 'ORGANIZATION')
280 ELSE NULL
281 END parent_id, -- this is the grand parent id of cont pref - written as parent id
282 CASE PARENT_ENTITY_NAME -- this case is for identifying the grand parent BO
283 WHEN 'HZ_CONTACT_POINTS' THEN -- identify GrandParentBO when CP is parent of CPP
284 (SELECT
285 CASE OWNER_TABLE_NAME
286 WHEN 'HZ_PARTIES' THEN -- identify GrandParentBO when CP is parent of CPP, party is parentOf CP
287 (SELECT
288 CASE party_type
289 WHEN 'ORGANIZATION' THEN 'ORG'
290 WHEN 'PERSON' THEN 'PERSON'
291 WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
292 ELSE NULL
293 END
294 FROM hz_parties
295 WHERE party_id = owner_table_id)
296 WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentBO when CP is parent of CPP, PS is parentOf CP
297 'PARTY_SITE'
298 ELSE NULL
299 END
300 FROM HZ_CONTACT_POINTS
301 WHERE contact_point_id = PARENT_ID)
302 WHEN 'HZ_PARTIES' THEN -- identify GrandParentBO when Party is parent of CPP
303 NULL
304 WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentBO when PS is parent of CPP
305 (SELECT
306 CASE party_type
307 WHEN 'ORGANIZATION' THEN 'ORG'-- identify GrandParentBO when PS is parent of CPP, Org is parent of PS
308 WHEN 'PERSON' THEN 'PERSON' -- identify GrandParentBO when PS is parent of CPP, Per is parent of PS
309 WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT' -- identify GrandParentBO when PS is parent of CPP, Rel is parent of PS
310 ELSE NULL
311 END
312 FROM hz_parties
313 WHERE party_id = (select party_id
314 from hz_party_sites
315 where party_site_id = PARENT_ID))
316 WHEN 'HZ_ORG_CONTACTS' THEN 'ORG' -- identify GrandParentBO when OrgContact is parent of CPP
317 ELSE
318 NULL
319 END parent_bo_code -- this is the grand parent bo, written as parent bo
320 FROM HZ_BUS_OBJ_TRACKING
321 WHERE CHILD_ENTITY_NAME = 'HZ_CONTACT_PREFERENCES'
322 AND event_id IS NULL) temp
323 WHERE NOT EXISTS
324 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
325 WHERE bot.event_id IS NULL
326 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
327 AND bot.CHILD_ID = temp.child_id
328 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
329 AND cprank = 1
330 AND temp.child_id IS NOT NULL
331 AND temp.CHILD_ENTITY_NAME IS NOT NULL
332 AND temp.CHILD_BO_CODE IS NOT NULL;
333 -- save the records populated
334 COMMIT;
335 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
336 hz_utility_v2pub.DEBUG
337 (p_message=>'ei_HZ_CONTACT_PREFERENCES()-',
338 p_prefix=>l_debug_prefix,
339 p_msg_level=>fnd_log.level_procedure,
340 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
341 p_module =>'ei_cpp');
342 END IF;
343 EXCEPTION
344 WHEN OTHERS THEN
345 LOG( message => 'ei_HZ_CONTACT_PREFERENCES:'||SQLERRM,
346 p_prefix =>l_debug_prefix,
347 p_module =>'ei_cpp');
348 ROLLBACK;
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350 END ei_HZ_CONTACT_PREFERENCES;
351 -----------------------------------------------------------------
352 /*
353 Procedure name: ei_HZ_PARTY_SITES_EXT_VL()
354 Scope: external
355 Purpose: This procedure two activities on BOT table.
356 Writes the parent node record for HZ_PER_PROFILES_EXT_VL .
357 Called From: This pkg
358 Called By: populate_missing_links()
359 */
360 -----------------------------------------------------------------
361 PROCEDURE ei_HZ_PARTY_SITES_EXT_VL IS
362 l_debug_prefix VARCHAR2(30) := 'PS_EXT:';
363 BEGIN
364 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
365 hz_utility_v2pub.DEBUG
366 (p_message=>'ei_HZ_PARTY_SITES_EXT_VL()+',
367 p_prefix=>l_debug_prefix,
368 p_msg_level=>fnd_log.level_procedure,
369 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
370 p_module =>'ei_psext');
371 END IF;
372 /*
373 Following entities are parents of HZ_PARTY_SITES_EXT_VL
374 1. HZ_PARTY_SITES
375
376 Party Site has following parents
377 1. HZ_PARTIES (Org, Person, OrgContact)
378
379 Hence, HZ_PARTY_SITES_EXT_VL can exist in three different scenarios.
380 The different combinations of (parent, grand parent) are :
381 1. (PS, Org) 2. (PS, Person) 3. (PS, OrgContact)
382
383 The following SQL gets the Parent and Grand parent info of each HZ_PARTY_SITES_EXT_VL
384 record that was not already processed in BOT.
385
386 Test Cases for the following SQL are:
387 Create HZ_PARTY_SITES_EXT_VL rec in BOT with (Parent, Grand Parent)
388 combinations existing in TCA data model
389 1. (PS, Org) --
390 2. (PS, Person) --
391 3. (PS, OrgContact) --
392 */
393 /* A note on the Structure of the insert statement
394 1. The parent record is tracked by the insert statement.
395 2. The "inner select" acts as a data source for "outer select"
396 3. The "outer select" uses "select to exclude the parents
397 that were already written to BOT"
398 4. There may be duplicate rows for any given parent retured by the "inner select".
399 This is because, in a given execution of the following SQL,
400 there may be multiple children for a given parent.
401
402 Each child contributes in getting its parent. This is as per the design
403 of "inner select".
404
405 To avoid duplicate rows of a parent returned by the siblings,
406 the inner select ranks all the parents duplicate parents.
407 The "outer select" filters on parents with rank = 1.
408 This clause helps to filter out the duplicate parent rows,
409 before data was inserted by insert statement.
410
411 5. The "inner select" is operating on the child record and trying to identify
412 the parent and grand parent information.
413 The parent information of the child record will be child
414 (current/its) information for the parent record.
415 The grand parent information of the child record will be parent info
416 of the parent record.
417 Because of this reason, "inner select" statement aliases the columns.
418
419 6. It is non-trivial to figure out the business object codes for both parent
420 and grand parent, grand parent identifier or grand parent entity name.
421 To do this, "inner select" uses case statement on parent_entity_name.
422 Some times, an embedded SQL is necessary to fgure out this.
423 Example:
424 Child is HZ_PARTY_SITES_EXT_VL.
425 Parent is PS and it's parent is Party.
426 To figure out the grand parent bo code, SQL is necessary to run against
427 HZ_PARTIES to figure out the PARTY_TYPE based on PARTY_ID of the
428 HZ_PARTY_SITES table.
429 */
430
431 INSERT INTO HZ_BUS_OBJ_TRACKING
432 (CHILD_ENTITY_NAME, CHILD_ID,
433 CHILD_OPERATION_FLAG, POPULATED_FLAG,
434 LAST_UPDATE_DATE, CHILD_BO_CODE,
435 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
436 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
437 CHILD_OPERATION_FLAG, POPULATED_FLAG,
438 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
439 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
440 FROM
441 (SELECT -- inner select
442 PARENT_ENTITY_NAME child_entity_name
443 ,PARENT_ID child_id
444 ,PARENT_BO_CODE CHILD_BO_CODE
445 ,'U' child_operation_flag
446 ,'Y' populated_flag
447 ,LAST_UPDATE_DATE
448 ,RANK()
449 OVER (PARTITION BY PARENT_ENTITY_NAME, PARENT_ID
450 ORDER BY LAST_UPDATE_DATE, child_id) as psxrank
451 ,CASE PARENT_BO_CODE
452 WHEN 'PARTY_SITE' THEN
453 (SELECT
454 CASE party_type
455 WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
456 WHEN 'PERSON' THEN 'HZ_PARTIES'
457 WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
458 ELSE NULL
459 END
460 FROM HZ_PARTIES
461 WHERE party_id = (SELECT ps.party_id
462 FROM HZ_PARTY_SITES ps
463 WHERE ps.party_site_id = PARENT_ID))
464 ELSE NULL
465 END parent_entity_name -- this is grand parent tbl name of CP - written as parent entity name
466 ,CASE PARENT_BO_CODE
467 WHEN 'PARTY_SITE' THEN
468 (SELECT
469 CASE p.party_type
470 WHEN 'ORGANIZATION' THEN p.party_id
471 WHEN 'PERSON' THEN p.party_id
472 WHEN 'PARTY_RELATIONSHIP' THEN
473 (SELECT oc.org_contact_id
474 FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
475 WHERE r.relationship_id = oc.party_relationship_id
476 AND r.party_id = p.party_id
477 AND r.subject_type = 'PERSON'
478 AND r.object_type = 'ORGANIZATION')
479 ELSE NULL
480 END
481 FROM hz_parties p
482 WHERE p.party_id = (select ps.party_id
483 from HZ_PARTY_SITES ps
484 where ps.party_site_id = PARENT_ID))
485 ELSE NULL
486 END parent_id -- this is the grand parent id of ps extension - written as parent
487 ,CASE PARENT_BO_CODE
488 WHEN 'PARTY_SITE' THEN
489 (SELECT
490 CASE party_type
491 WHEN 'ORGANIZATION' THEN 'ORG'
492 WHEN 'PERSON' THEN 'PERSON'
493 WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
494 ELSE NULL
495 END
496 FROM hz_parties
497 WHERE party_id = (SELECT party_id
498 FROM HZ_PARTY_SITES
499 WHERE party_site_id = PARENT_ID))
500 ELSE NULL
501 END parent_bo_code -- this is the grand parent bo, written as parent
502 FROM HZ_BUS_OBJ_TRACKING
503 WHERE CHILD_ENTITY_NAME = 'HZ_PARTY_SITES_EXT_VL'
504 AND event_id IS NULL) temp
505 WHERE NOT EXISTS
506 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
507 WHERE bot.event_id IS NULL
508 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
509 AND bot.CHILD_ID = temp.child_id
510 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
511 AND psxrank = 1
512 AND temp.child_id IS NOT NULL
513 AND temp.CHILD_ENTITY_NAME IS NOT NULL
514 AND temp.CHILD_BO_CODE IS NOT NULL;
515 -- save the records populated
516 COMMIT;
517 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
518 hz_utility_v2pub.DEBUG
519 (p_message=>'ei_HZ_PARTY_SITES_EXT_VL()-',
520 p_prefix=>l_debug_prefix,
521 p_msg_level=>fnd_log.level_procedure,
522 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
523 p_module =>'ei_psext');
524 END IF;
525 EXCEPTION
526 WHEN OTHERS THEN
527 LOG( message => 'ei_HZ_PARTY_SITES_EXT_VL:'||SQLERRM,
528 p_prefix =>l_debug_prefix,
529 p_module =>'ei_psext');
530 ROLLBACK;
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
532 END ei_HZ_PARTY_SITES_EXT_VL;
533 -----------------------------------------------------------------
534 /*
535 Procedure name: ei_HZ_ORG_PROFILES_EXT_VL()
536 Scope: external
537 Purpose: This procedure two activities on BOT table.
538 Writes the parent node record for HZ_PER_PROFILES_EXT_VL .
539 Called From: This pkg
540 Called By: populate_missing_links()
541 */
542 -----------------------------------------------------------------
543 PROCEDURE ei_HZ_ORG_PROFILES_EXT_VL IS
544 l_debug_prefix VARCHAR2(30) := 'ORG_PROF_EXT:';
545 BEGIN
546 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
547 hz_utility_v2pub.DEBUG
548 (p_message=>'ei_HZ_ORG_PROFILES_EXT_VL()+',
549 p_prefix=>l_debug_prefix,
550 p_msg_level=>fnd_log.level_procedure,
551 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
552 p_module =>'ei_oext');
553 END IF;
554 /*
555 Following entities are parents of HZ_ORG_PROFILES_EXT_VL
556 1. HZ_PARTIES (Org)
557
558 The following SQL gets the Parent and Grand parent info of each HZ_ORG_PROFILES_EXT_VL
559 record that was not already processed in BOT.
560
561 Test Cases for the following SQL are:
562 Create HZ_ORG_PROFILES_EXT_VL rec in BOT with (Parent, Grand Parent)
563 combinations existing in TCA data model
564 1. (Org, null) --
565 */
566
567 /* A note on the Structure of the insert statement
568 1. The parent record is tracked by the insert statement.
569 2. The "inner select" acts as a data source for "outer select"
570 3. The "outer select" uses "select to exclude the parents
571 that were already written to BOT"
572 4. There may be duplicate rows for any given parent retured by the "inner select".
573 This is because, in a given execution of the following SQL,
574 there may be multiple children for a given parent.
575
576 Each child contributes in getting its parent. This is as per the design
577 of "inner select".
578
579 To avoid duplicate rows of a parent returned by the siblings,
580 the inner select ranks all the parents duplicate parents.
581 The "outer select" filters on parents with rank = 1.
582 This clause helps to filter out the duplicate parent rows,
583 before data was inserted by insert statement.
584
585 5. The "inner select" is operating on the child record and trying to identify
586 the parent and grand parent information.
587 The parent information of the child record will be child
588 (current/its) information for the parent record.
589 The grand parent information of the child record will be parent info
590 of the parent record.
591 Because of this reason, "inner select" statement aliases the columns.
592 */
593
594 INSERT INTO HZ_BUS_OBJ_TRACKING
595 (CHILD_ENTITY_NAME, CHILD_ID,
596 CHILD_OPERATION_FLAG, POPULATED_FLAG,
597 LAST_UPDATE_DATE, CHILD_BO_CODE,
598 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
599 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
600 CHILD_OPERATION_FLAG, POPULATED_FLAG,
601 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
602 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
603 FROM
604 (SELECT -- inner select
605 PARENT_ENTITY_NAME child_entity_name
606 ,PARENT_ID child_id
607 ,PARENT_BO_CODE CHILD_BO_CODE
608 ,NULL parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
609 ,NULL parent_bo_code -- this is the grand parent bo, written as parent
610 ,NULL parent_id -- this is grand parent id of acct roles, written as parent id
611 ,'U' child_operation_flag
612 ,'Y' populated_flag
613 ,t.LAST_UPDATE_DATE
614 ,RANK()
615 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
616 ORDER BY t.LAST_UPDATE_DATE, child_id) as ORGrank
617 FROM HZ_BUS_OBJ_TRACKING t
618 WHERE CHILD_ENTITY_NAME = 'HZ_ORG_PROFILES_EXT_VL'
619 AND PARENT_BO_CODE = 'ORG'
620 AND event_id IS NULL) temp
621 WHERE NOT EXISTS
622 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
623 WHERE bot.event_id IS NULL
624 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
625 AND bot.CHILD_ID = temp.child_id
626 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
627 AND ORGrank = 1
628 AND temp.child_id IS NOT NULL
629 AND temp.CHILD_ENTITY_NAME IS NOT NULL
630 AND temp.CHILD_BO_CODE IS NOT NULL ;
631 -- save the records populated
632 COMMIT;
633 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
634 hz_utility_v2pub.DEBUG
635 (p_message=>'ei_HZ_ORG_PROFILES_EXT_VL()-',
636 p_prefix=>l_debug_prefix,
637 p_msg_level=>fnd_log.level_procedure,
638 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
639 p_module =>'ei_oext');
640 END IF;
641 EXCEPTION
642 WHEN OTHERS THEN
643 LOG( message => 'ei_HZ_ORG_PROFILES_EXT_VL:'||SQLERRM,
644 p_prefix =>l_debug_prefix,
645 p_module =>'ei_oext');
646 ROLLBACK;
647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END ei_HZ_ORG_PROFILES_EXT_VL;
649 -----------------------------------------------------------------
650 /*
651 Procedure name: ei_HZ_PER_PROFILES_EXT_VL ()
652 Scope: external
653 Purpose: This procedure two activities on BOT table.
654 Writes the parent node record for HZ_PER_PROFILES_EXT_VL .
655 Called From: This pkg
656 Called By: populate_missing_links()
657 */
658 -----------------------------------------------------------------
659 PROCEDURE ei_HZ_PER_PROFILES_EXT_VL IS
660 l_debug_prefix VARCHAR2(30) := 'PER_PROF_EXT:';
661 BEGIN
662 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
663 hz_utility_v2pub.DEBUG
664 (p_message=>'ei_HZ_PER_PROFILES_EXT_VL()+',
665 p_prefix=>l_debug_prefix,
666 p_msg_level=>fnd_log.level_procedure,
667 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
668 p_module =>'ei_pext');
669 hz_utility_v2pub.DEBUG
670 (p_message=>'pop per prof ext for person sharing',
671 p_prefix=>l_debug_prefix,
672 p_msg_level=>fnd_log.level_procedure,
673 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
674 p_module =>'ei_pext');
675 END IF;
676 /*
677 The Person extenstion EO/API will write
678 only one (HZ_PER_PROFILES_EXT_VL, Person) record in the BOT.
679
680 Because of Person Sharing concept in TCA Business Event System,
681 entries for HZ_PER_PROFILES_EXT_VL in BOT must be duplicated
682 as children Person_Contact.
683
684 This must be done before trying to figure out the missing links for
685 HZ_PER_PROFILES_EXT_VL.
686
687 Example:
688
689 Following record is written by Person extenstion EO/V2 API
690 Child id: 123 Child_entity_name: HZ_PER_PROFILES_EXT_VL
691 Parent id: 456 parent_entity_name: HZ_PARTIES, parent_bo: Person
692
693 This SQL writes:
694 Child id: 123 Child_entity_name: HZ_PER_PROFILES_EXT_VL
695 Parent id: 456 parent_entity_name: HZ_PARTIES, parent_bo: Person_contact
696
697 SQL Flow:
698 This SQL first identifies all those person parties that are contacts of
699 any organization and are parents of the HZ_PER_PROFILES_EXT_VL records in BOT.
700
701 /*
702 The Person extenstion EO/API will write
703 only one (HZ_PER_PROFILES_EXT_VL, Person) record in the BOT.
704
705 Because of Person Sharing concept in TCA Business Event System,
706 entries for HZ_PER_PROFILES_EXT_VL in BOT must be duplicated
707 as children Person_Contact.
708
709 This must be done before trying to figure out the missing links for
710 HZ_PER_PROFILES_EXT_VL.
711
712 Example:
713
714 Following record is written by Person extenstion EO/V2 API
715 Child id: 123 Child_entity_name: HZ_PER_PROFILES_EXT_VL
716 Parent id: 456 parent_entity_name: HZ_PARTIES, parent_bo: Person
717
718 This SQL writes:
719 Child id: 123 Child_entity_name: HZ_PER_PROFILES_EXT_VL
720 Parent id: 456 parent_entity_name: HZ_PARTIES, parent_bo: Person_contact
721
722 SQL Flow:
723 This SQL first identifies all those person parties that are contacts of
724 any organization and are parents of the HZ_PER_PROFILES_EXT_VL records in BOT.
725
726 */
727 INSERT INTO HZ_BUS_OBJ_TRACKING (
728 CHILD_ID
729 ,CHILD_BO_CODE
730 ,CHILD_ENTITY_NAME
731 ,CHILD_OPERATION_FLAG
732 ,POPULATED_FLAG
733 ,PARENT_ID
734 ,PARENT_ENTITY_NAME
735 ,PARENT_BO_CODE
736 ,LAST_UPDATE_DATE
737 ,CREATION_DATE)
738 SELECT DISTINCT
739 p.CHILD_ID
740 ,p.CHILD_BO_CODE
741 ,p.CHILD_ENTITY_NAME
742 ,p.CHILD_OPERATION_FLAG
743 ,p.POPULATED_FLAG
744 ,p.PARENT_ID
745 ,p.PARENT_ENTITY_NAME
746 ,'PERSON_CONTACT' PARENT_BO_CODE
747 ,p.LAST_UPDATE_DATE
748 ,p.CREATION_DATE
749 FROM
750 (SELECT
751 CHILD_ID
752 ,CHILD_BO_CODE
753 ,CHILD_ENTITY_NAME
754 ,CHILD_OPERATION_FLAG
755 ,POPULATED_FLAG
756 ,PARENT_ID
757 ,PARENT_ENTITY_NAME
758 ,LAST_UPDATE_DATE
759 ,CREATION_DATE
760 FROM HZ_BUS_OBJ_TRACKING
761 WHERE CHILD_ENTITY_NAME = 'HZ_PER_PROFILES_EXT_VL'
762 AND parent_bo_code = 'PERSON'
763 AND event_id IS NULL) p, hz_org_contacts oc, hz_relationships r
764 WHERE p.PARENT_ID = r.subject_id
765 AND r.subject_type = 'PERSON'
766 AND r.object_type = 'ORGANIZATION'
767 AND r.relationship_id = oc.party_relationship_id
768 AND NOT EXISTS
769 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the records that were already written to BOT
770 WHERE bot.event_id IS NULL
771 AND bot.CHILD_ENTITY_NAME = p.CHILD_ENTITY_NAME
772 AND bot.CHILD_ID = p.child_id
773 AND bot.PARENT_ID = p.PARENT_ID
774 AND bot.PARENT_BO_CODE = 'PERSON_CONTACT');
775 COMMIT;
776
777 /*
778 Following entities are parents of HZ_PER_PROFILES_EXT_VL
779 1. HZ_PARTIES (Person, Person_Contact)
780
781 Hence, HZ_PER_PROFILES_EXT_VL can exist in six different scenarios.
782 The different combinations of (parent, grand parent) are :
783 1. (Person, null) 2. (Person_Contact, OrgContact)
784
785 The following SQL gets the Parent and Grand parent info of each HZ_PER_PROFILES_EXT_VL
786 record that was not already processed in BOT.
787
788 Test Cases for the following SQL are:
789 Create HZ_PER_PROFILES_EXT_VL rec in BOT with (Parent, Grand Parent)
790 combinations existing in TCA data model
791 1. (Person, null) -- Tested
792 2. (Person_Contact, OrgContact, Org) --
793 */
794
795 /* A note on the Structure of the insert statement
796 1. The parent record is tracked by the insert statement.
797 2. The "inner select" acts as a data source for "outer select"
798 3. The "outer select" uses "select to exclude the parents
799 that were already written to BOT"
800 4. There may be duplicate rows for any given parent retured by the "inner select".
801 This is because, in a given execution of the following SQL,
802 there may be multiple children for a given parent.
803
804 Each child contributes in getting its parent. This is as per the design
805 of "inner select".
806
807 To avoid duplicate rows of a parent returned by the siblings,
808 the inner select ranks all the parents duplicate parents.
809 The "outer select" filters on parents with rank = 1.
810 This clause helps to filter out the duplicate parent rows,
811 before data was inserted by insert statement.
812
813 5. The "inner select" is operating on the child record and trying to identify
814 the parent and grand parent information.
815 The parent information of the child record will be child
816 (current/its) information for the parent record.
817 The grand parent information of the child record will be parent info
818 of the parent record.
819 Because of this reason, "inner select" statement aliases the columns.
820
821 example:
822
823 child record:
824 child_id: 123, child_entity_name: HZ_PER_PROFILES_EXT_VL,
825 child_bo: NULL, parent_bo: Person_Contact, parent_entity_name: HZ_PARTIES,
826 parent_id: 234
827
828 The "inner select" fetches above record from BOT and identifies
829 its parent, grand parent information and present it as follows:
830
831 parent_id aliased as child_id: 234
832 parent_entity_name aliased as child_entity_name: HZ_PARTIES
833 parent_bo aliased as child_bo: Person_Contact
834 grand_parent_id aliased as parent id: 456
835 grand_parent_entity_name aliased as parent_entity_name: HZ_ORG_CONTACTS
836 grand_parent_bo aliased as parent_bo: Org_Contact
837
838 Insert statement will take this result and write it as
839 child record:
840 child_id: 234, child_entity_name: HZ_PARTITES,
841 child_bo: Person_Contact, parent_bo: Org_Contact, parent_entity_name: HZ_ORG_CONTACTS,
842 parent_id: 456
843
844 Note: Two separate SQLs insert statements are necessary as a person can exist
845 multiple times as an org_contact. So, when getting the org_contact_id,
846 SQL will return the multiple rows. This makes is impossible to write
847 it in one single SQL with CASE statements.
848
849 The following SQL is to generate all the parent and grand parent combination for
850 pp_extn entity which is a child of PERSON BO
851 */
852 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
853 hz_utility_v2pub.DEBUG
854 (p_message=>'pop missing lnks for per prof ext',
855 p_prefix=>l_debug_prefix,
856 p_msg_level=>fnd_log.level_procedure,
857 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
858 p_module =>'ei_pext');
859 END IF;
860 INSERT INTO HZ_BUS_OBJ_TRACKING
861 (CHILD_ENTITY_NAME, CHILD_ID,
862 CHILD_OPERATION_FLAG, POPULATED_FLAG,
863 LAST_UPDATE_DATE, CHILD_BO_CODE,
864 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
865 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
866 CHILD_OPERATION_FLAG, POPULATED_FLAG,
867 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
868 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
869 FROM
870 (SELECT -- inner select
871 PARENT_ENTITY_NAME child_entity_name
872 ,PARENT_ID child_id
873 ,PARENT_BO_CODE CHILD_BO_CODE
874 ,NULL parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
875 ,NULL parent_bo_code -- this is the grand parent bo, written as parent
876 , NULL parent_id -- this is grand parent id of acct roles, written as parent id
877 ,'U' child_operation_flag
878 ,'Y' populated_flag
879 ,t.LAST_UPDATE_DATE
880 ,RANK()
881 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
882 ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
883 FROM HZ_BUS_OBJ_TRACKING t
884 WHERE CHILD_ENTITY_NAME = 'HZ_PER_PROFILES_EXT_VL'
885 AND PARENT_BO_CODE = 'PERSON'
886 AND event_id IS NULL) temp
887 WHERE NOT EXISTS
888 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
889 WHERE bot.event_id IS NULL
890 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
891 AND bot.CHILD_ID = temp.child_id
892 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
893 AND acrrank = 1
894 AND temp.child_id IS NOT NULL
895 AND temp.CHILD_ENTITY_NAME IS NOT NULL
896 AND temp.CHILD_BO_CODE IS NOT NULL;
897 COMMIT;
898 /* The following SQL is to generate all the parent and grand parent combination for
899 pp_extn entity which is a child of PERSON_CONTACT BO
900 */
901
902 INSERT INTO HZ_BUS_OBJ_TRACKING
903 (CHILD_ENTITY_NAME, CHILD_ID,
904 CHILD_OPERATION_FLAG, POPULATED_FLAG,
905 LAST_UPDATE_DATE, CHILD_BO_CODE,
906 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
907 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
908 CHILD_OPERATION_FLAG, POPULATED_FLAG,
909 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
910 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
911 FROM
912 (SELECT -- inner select
913 PARENT_ENTITY_NAME child_entity_name
914 ,PARENT_ID child_id
915 ,PARENT_BO_CODE CHILD_BO_CODE
916 ,'HZ_ORG_CONTACTS' parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
917 ,'ORG_CONTACT' parent_bo_code -- this is the grand parent bo, written as parent
918 , oc.org_contact_id parent_id -- this is grand parent id of acct roles, written as parent id
919 ,'U' child_operation_flag
920 ,'Y' populated_flag
921 ,t.LAST_UPDATE_DATE
922 ,RANK()
923 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
924 ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
925 FROM HZ_BUS_OBJ_TRACKING t, hz_relationships r, hz_org_contacts oc
926 WHERE r.subject_id = PARENT_ID
927 AND oc.party_relationship_id = r.relationship_id
928 AND r.subject_type ='PERSON'
929 AND r.object_type = 'ORGANIZATION'
930 AND CHILD_ENTITY_NAME = 'HZ_PER_PROFILES_EXT_VL'
931 AND PARENT_BO_CODE = 'PERSON_CONTACT'
932 AND event_id IS NULL) temp
933 WHERE NOT EXISTS
934 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
935 WHERE bot.event_id IS NULL
936 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
937 AND bot.CHILD_ID = temp.child_id
938 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
939 AND acrrank = 1
940 AND temp.child_id IS NOT NULL
941 AND temp.CHILD_ENTITY_NAME IS NOT NULL
942 AND temp.CHILD_BO_CODE IS NOT NULL;
943 -- save the records populated
944 COMMIT;
945 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
946 hz_utility_v2pub.DEBUG
947 (p_message=>'ei_HZ_PER_PROFILES_EXT_VL()-',
948 p_prefix=>l_debug_prefix,
949 p_msg_level=>fnd_log.level_procedure,
950 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
951 p_module =>'ei_pext');
952 END IF;
953 EXCEPTION
954 WHEN OTHERS THEN
955 LOG( message => 'ei_HZ_PER_PROFILES_EXT_VL:'||SQLERRM,
956 p_prefix =>l_debug_prefix,
957 p_module =>'ei_pext');
958 ROLLBACK;
959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
960 END ei_HZ_PER_PROFILES_EXT_VL;
961 -----------------------------------------------------------------
962 /*
963 Procedure name: PROCEDURE ei_HZ_PERSON_PROFILES()
964 Purpose: Writes the parent node record for HZ_PERSON_PROFILES in BOT
965 Scope: internal
966 Called From: This pkg
967 Called By: populate_missing_links()
968 */
969 -----------------------------------------------------------------
970
971 PROCEDURE ei_HZ_PERSON_PROFILES IS
972
973 -- local variables
974 l_debug_prefix VARCHAR2(40) := 'EI_HZ_PERSON_PROFILES';
975
976 BEGIN
977 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
978 hz_utility_v2pub.DEBUG
979 (p_message=>'ei_HZ_PERSON_PROFILES+',
980 p_prefix=>l_debug_prefix,
981 p_msg_level=>fnd_log.level_procedure,
982 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
983 p_module =>'ei_pp');
984 hz_utility_v2pub.DEBUG
985 (p_message=>'pop per prof ext for person sharing',
986 p_prefix=>l_debug_prefix,
987 p_msg_level=>fnd_log.level_procedure,
988 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
989 p_module =>'ei_pp');
990 END IF;
991 /*
992 The Person Profile V2 API will write
993 only one (HZ_PERSON_PROFILES, Person) record in the BOT.
994
995 Because of Person Sharing concept in TCA Business Event System,
996 entries for HZ_PERSON_PROFILES in BOT must be duplicated
997 as children Person_Contact.
998
999 This must be done before trying to figure out the missing links for
1000 HZ_PERSON_PROFILES.
1001
1002 Example:
1003
1004 Following record is written by Person extenstion EO/V2 API
1005 Child id: 123 Child_entity_name: HZ_PERSON_PROFILES
1006 Parent id: 456 parent_entity_name: HZ_PARTIES, parent_bo: Person
1007
1008 This SQL writes:
1009 Child id: 123 Child_entity_name: HZ_PERSON_PROFILES
1010 Parent id: 456 parent_entity_name: HZ_PARTIES, parent_bo: Person_contact
1011
1012 SQL Flow:
1013 This SQL first identifies all those person parties that are contacts of
1014 any organization and are parents of the HZ_PERSON_PROFILES records in BOT.
1015 */
1016
1017 INSERT INTO HZ_BUS_OBJ_TRACKING (
1018 CHILD_ID
1019 ,CHILD_BO_CODE
1020 ,CHILD_ENTITY_NAME
1021 ,CHILD_OPERATION_FLAG
1022 ,POPULATED_FLAG
1023 ,PARENT_ID
1024 ,PARENT_ENTITY_NAME
1025 ,PARENT_BO_CODE
1026 ,LAST_UPDATE_DATE
1027 ,CREATION_DATE)
1028 SELECT DISTINCT
1029 p.CHILD_ID
1030 ,p.CHILD_BO_CODE
1031 ,p.CHILD_ENTITY_NAME
1032 ,p.CHILD_OPERATION_FLAG
1033 ,p.POPULATED_FLAG
1034 ,p.PARENT_ID
1035 ,p.PARENT_ENTITY_NAME
1036 ,'PERSON_CONTACT' PARENT_BO_CODE
1037 ,p.LAST_UPDATE_DATE
1038 ,p.CREATION_DATE
1039 FROM
1040 (SELECT
1041 CHILD_ID
1042 ,CHILD_BO_CODE
1043 ,CHILD_ENTITY_NAME
1044 ,CHILD_OPERATION_FLAG
1045 ,POPULATED_FLAG
1046 ,PARENT_ID
1047 ,PARENT_ENTITY_NAME
1048 ,LAST_UPDATE_DATE
1049 ,CREATION_DATE
1050 FROM HZ_BUS_OBJ_TRACKING
1051 WHERE CHILD_ENTITY_NAME = 'HZ_PERSON_PROFILES'
1052 AND parent_bo_code = 'PERSON'
1053 AND event_id IS NULL) p, hz_org_contacts oc, hz_relationships r
1054 WHERE p.PARENT_ID = r.subject_id
1055 AND r.subject_type = 'PERSON'
1056 AND r.object_type = 'ORGANIZATION'
1057 AND r.relationship_id = oc.party_relationship_id
1058 AND NOT EXISTS
1059 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the records that were already written to BOT
1060 WHERE bot.event_id IS NULL
1061 AND bot.CHILD_ENTITY_NAME = p.CHILD_ENTITY_NAME
1062 AND bot.CHILD_ID = p.child_id
1063 AND bot.PARENT_ID = p.PARENT_ID
1064 AND bot.PARENT_BO_CODE = 'PERSON_CONTACT');
1065 COMMIT;
1066
1067 /*
1068 Following entities are parents of HZ_PERSON_PROFILES
1069 1. HZ_PARTIES (Person, Person_Contact)
1070
1071 Hence, HZ_PERSON_PROFILES can exist in 2 different scenarios.
1072 The different combinations of (parent, grand parent) are :
1073 1. (Person, null) 2. (Person_Contact, OrgContact)
1074
1075 The following SQL gets the Parent and Grand parent info of each HZ_PERSON_PROFILES
1076 record that was not already processed in BOT.
1077
1078 Test Cases for the following SQL are:
1079 Create HZ_PERSON_PROFILES rec in BOT with (Parent, Grand Parent)
1080 combinations existing in TCA data model
1081 1. (Person, null) -- tested
1082 2. (Person_Contact, OrgContact, Org) -- tested
1083 */
1084
1085 /* A note on the Structure of the insert statement
1086 1. The parent record is tracked by the insert statement.
1087 2. The "inner select" acts as a data source for "outer select"
1088 3. The "outer select" uses "select to exclude the parents
1089 that were already written to BOT"
1090 4. There may be duplicate rows for any given parent retured by the "inner select".
1091 This is because, in a given execution of the following SQL,
1092 there may be multiple children for a given parent.
1093
1094 Each child contributes in getting its parent. This is as per the design
1095 of "inner select".
1096
1097 To avoid duplicate rows of a parent returned by the siblings,
1098 the inner select ranks all the parents duplicate parents.
1099 The "outer select" filters on parents with rank = 1.
1100 This clause helps to filter out the duplicate parent rows,
1101 before data was inserted by insert statement.
1102
1103 5. The "inner select" is operating on the child record and trying to identify
1104 the parent and grand parent information.
1105 The parent information of the child record will be child
1106 (current/its) information for the parent record.
1107 The grand parent information of the child record will be parent info
1108 of the parent record.
1109 Because of this reason, "inner select" statement aliases the columns.
1110
1111 example:
1112
1113 child record:
1114 child_id: 123, child_entity_name: HZ_PERSON_PROFILES,
1115 child_bo: NULL, parent_bo: Person_Contact, parent_entity_name: HZ_PARTIES,
1116 parent_id: 234
1117
1118 The "inner select" fetches above record from BOT and identifies
1119 its parent, grand parent information and present it as follows:
1120
1121 parent_id aliased as child_id: 234
1122 parent_entity_name aliased as child_entity_name: HZ_PARTIES
1123 parent_bo aliased as child_bo: Person_Contact
1124 grand_parent_id aliased as parent id: 456
1125 grand_parent_entity_name aliased as parent_entity_name: HZ_ORG_CONTACTS
1126 grand_parent_bo aliased as parent_bo: Org_Contact
1127
1128 Insert statement will take this result and write it as
1129 child record:
1130 child_id: 234, child_entity_name: HZ_PARTITES,
1131 child_bo: Person_Contact, parent_bo: Org_Contact, parent_entity_name: HZ_ORG_CONTACTS,
1132 parent_id: 456
1133
1134
1135 Note: Two separate SQLs insert statements are necessary as a person can exist
1136 multiple times as an org_contact. So, when getting the org_contact_id,
1137 SQL will return the multiple rows. This makes is impossible to write
1138 it in one single SQL with CASE statements.
1139
1140 The following SQL is to generate all the parent and grand parent combination for
1141 person_profile entity which is a child of PERSON BO
1142 */
1143 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1144 hz_utility_v2pub.DEBUG
1145 (p_message=>'pop missing lnks for per prof',
1146 p_prefix=>l_debug_prefix,
1147 p_msg_level=>fnd_log.level_procedure,
1148 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1149 p_module =>'ei_pp');
1150 END IF;
1151 INSERT INTO HZ_BUS_OBJ_TRACKING
1152 (CHILD_ENTITY_NAME, CHILD_ID,
1153 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1154 LAST_UPDATE_DATE, CHILD_BO_CODE,
1155 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
1156 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
1157 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1158 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
1159 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
1160 FROM
1161 (SELECT -- inner select
1162 PARENT_ENTITY_NAME child_entity_name
1163 ,PARENT_ID child_id
1164 ,PARENT_BO_CODE CHILD_BO_CODE
1165 ,NULL parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
1166 ,NULL parent_bo_code -- this is the grand parent bo, written as parent
1167 , NULL parent_id -- this is grand parent id of acct roles, written as parent id
1168 ,'U' child_operation_flag
1169 ,'Y' populated_flag
1170 ,t.LAST_UPDATE_DATE
1171 ,RANK()
1172 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
1173 ORDER BY t.LAST_UPDATE_DATE, child_id) as pprank
1174 FROM HZ_BUS_OBJ_TRACKING t
1175 WHERE CHILD_ENTITY_NAME = 'HZ_PERSON_PROFILES'
1176 AND PARENT_BO_CODE = 'PERSON'
1177 AND event_id IS NULL) temp
1178 WHERE NOT EXISTS
1179 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
1180 WHERE bot.event_id IS NULL
1181 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
1182 AND bot.CHILD_ID = temp.child_id
1183 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
1184 AND pprank = 1
1185 AND temp.child_id IS NOT NULL
1186 AND temp.CHILD_ENTITY_NAME IS NOT NULL
1187 AND temp.CHILD_BO_CODE IS NOT NULL;
1188 COMMIT;
1189
1190 /* The following SQL is to generate all the parent and grand parent combination for
1191 person_profile entity which is a child of PERSON_CONTACT BO
1192 */
1193
1194 INSERT INTO HZ_BUS_OBJ_TRACKING
1195 (CHILD_ENTITY_NAME, CHILD_ID,
1196 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1197 LAST_UPDATE_DATE, CHILD_BO_CODE,
1198 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
1199 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
1200 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1201 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
1202 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
1203 FROM
1204 (SELECT -- inner select
1205 PARENT_ENTITY_NAME child_entity_name
1206 ,PARENT_ID child_id
1207 ,PARENT_BO_CODE CHILD_BO_CODE
1208 ,'HZ_ORG_CONTACTS' parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
1209 ,'ORG_CONTACT' parent_bo_code -- this is the grand parent bo, written as parent
1210 , oc.org_contact_id parent_id -- this is grand parent id of acct roles, written as parent id
1211 ,'U' child_operation_flag
1212 ,'Y' populated_flag
1213 ,t.LAST_UPDATE_DATE
1214 ,RANK()
1215 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
1216 ORDER BY t.LAST_UPDATE_DATE, child_id) as pprank
1217 FROM HZ_BUS_OBJ_TRACKING t, hz_relationships r, hz_org_contacts oc
1218 WHERE r.subject_id = PARENT_ID
1219 AND oc.party_relationship_id = r.relationship_id
1220 AND r.subject_type ='PERSON'
1221 AND r.object_type = 'ORGANIZATION'
1222 AND CHILD_ENTITY_NAME = 'HZ_PERSON_PROFILES'
1223 AND PARENT_BO_CODE = 'PERSON_CONTACT'
1224 AND event_id IS NULL) temp
1225 WHERE NOT EXISTS
1226 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
1227 WHERE bot.event_id IS NULL
1228 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
1229 AND bot.CHILD_ID = temp.child_id
1230 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
1231 AND pprank = 1
1232 AND temp.child_id IS NOT NULL
1233 AND temp.CHILD_ENTITY_NAME IS NOT NULL
1234 AND temp.CHILD_BO_CODE IS NOT NULL;
1235 -- save the records populated
1236 COMMIT;
1237 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1238 hz_utility_v2pub.DEBUG
1239 (p_message=>'ei_HZ_PERSON_PROFILES-',
1240 p_prefix=>l_debug_prefix,
1241 p_msg_level=>fnd_log.level_procedure,
1242 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1243 p_module =>'ei_pp');
1244 END IF;
1245 EXCEPTION
1246 WHEN OTHERS THEN
1247 LOG( message => 'ei_HZ_PERSON_PROFILES:'||SQLERRM,
1248 p_prefix =>l_debug_prefix,
1249 p_module =>'ei_pp');
1250 ROLLBACK;
1251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1252 END ei_HZ_PERSON_PROFILES;
1253 -----------------------------------------------------------------
1254 /*
1255 Procedure name: PROCEDURE ei_HZ_PARTY_SITE_USES()
1256 Purpose: Writes the parent node record for HZ_PARTY_SITE_USES in BOT
1257 Scope: internal
1258 Called From: This pkg
1259 Called By: populate_missing_links()
1260 */
1261 -----------------------------------------------------------------
1262
1263 PROCEDURE ei_HZ_PARTY_SITE_USES IS
1264
1265 -- local variables
1266 l_debug_prefix VARCHAR2(40) := 'EI_HZ_PARTY_SITE_USES';
1267
1268 BEGIN
1269 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1270 hz_utility_v2pub.DEBUG
1271 (p_message=>'ei_HZ_PARTY_SITE_USES+',
1272 p_prefix=>l_debug_prefix,
1273 p_msg_level=>fnd_log.level_procedure,
1274 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1275 p_module =>'ei_psu');
1276 END IF;
1277 /*
1278 Following entities are parents of HZ_PARTY_SITE_USES
1279 1. HZ_PARTY_SITES (PARTY_SITE)
1280
1281 Hence, HZ_PARTY_SITE_USES can exist three different scenarios.
1282 The different combinations of (parent, grand parent) are :
1283 1. (PARTY_SITE, Person)
1284 2. (PARTY_SITE, Organization)
1285 3. (PARTY_SITE, OrgContact)
1286
1287 The following SQL gets the Parent and Grand parent info of each HZ_PARTY_SITE_USES
1288 record that was not already processed in BOT.
1289
1290 Test Cases for the following SQL are:
1291 Create HZ_PARTY_SITE_USES rec in BOT with (Parent, Grand Parent)
1292 combinations existing in TCA data model
1293 1. (PARTY_SITE, Person) -- tested
1294 2. (PARTY_SITE, Organization) -- tested
1295 3. (PARTY_SITE, OrgContact, Org) -- tested
1296 */
1297
1298 /* A note on the Structure of the insert statement
1299 1. The parent record is tracked by the insert statement.
1300 2. The "inner select" acts as a data source for "outer select"
1301 3. The "outer select" uses "select to exclude the parents
1302 that were already written to BOT"
1303 4. There may be duplicate rows for any given parent retured by the "inner select".
1304 This is because, in a given execution of the following SQL,
1305 there may be multiple children for a given parent.
1306
1307 Each child contributes in getting its parent. This is as per the design
1308 of "inner select".
1309
1310 To avoid duplicate rows of a parent returned by the siblings,
1311 the inner select ranks all the parents duplicate parents.
1312 The "outer select" filters on parents with rank = 1.
1313 This clause helps to filter out the duplicate parent rows,
1314 before data was inserted by insert statement.
1315
1316 5. The "inner select" is operating on the child record and trying to identify
1317 the parent and grand parent information.
1318 The parent information of the child record will be child
1319 (current/its) information for the parent record.
1320 The grand parent information of the child record will be parent info
1321 of the parent record.
1322 Because of this reason, "inner select" statement aliases the columns.
1323
1324 example:
1325
1326 child record:
1327 child_id: 123, child_entity_name: HZ_PARTY_SITE_USES,
1328 child_bo: NULL, parent_bo: PS, parent_entity_name: HZ_PARTY_SITES,
1329 parent_id: 234
1330
1331 The "inner select" fetches above record from BOT and identifies
1332 its parent, grand parent information and present it as follows:
1333
1334 parent_id aliased as child_id: 234
1335 parent_entity_name aliased as child_entity_name: HZ_PARTY_SITES
1336 parent_bo aliased as child_bo: PARTY_SITE
1337 grand_parent_id aliased as parent id: 456
1338 grand_parent_entity_name aliased as parent_entity_name: HZ_ORG_CONTACTS
1339 grand_parent_bo aliased as parent_bo: Org_Contact
1340
1341 Insert statement will take this result and write it as
1342 child record:
1343 child_id: 234, child_entity_name: HZ_PARTY_SITES,
1344 child_bo: PARTY_SITE, parent_bo: Org_Contact, parent_entity_name: HZ_ORG_CONTACTS,
1345 parent_id: 456
1346
1347 6. It is non-trivial to figure out the business object codes for both parent
1348 and grand parent, grand parent identifier or grand parent entity name.
1349 To do this, "inner select" uses case statement on parent_entity_name.
1350 Some times, an embedded SQL is necessary to fgure out this.
1351 Example:
1352 Child is HZ_PARTY_SITE_USES.
1353 Parent is PARTY_SITE and it's parent is Org_Contact.
1354 */
1355
1356 INSERT INTO HZ_BUS_OBJ_TRACKING
1357 (CHILD_ENTITY_NAME, CHILD_ID,
1358 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1359 LAST_UPDATE_DATE, CHILD_BO_CODE,
1360 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
1361 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
1362 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1363 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
1364 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
1365 FROM
1366 (SELECT -- inner select
1367 PARENT_ENTITY_NAME child_entity_name
1368 ,PARENT_ID child_id
1369 ,PARENT_BO_CODE CHILD_BO_CODE
1370 ,'U' child_operation_flag
1371 ,'Y' populated_flag
1372 ,LAST_UPDATE_DATE
1373 ,RANK()
1374 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
1375 ORDER BY LAST_UPDATE_DATE, child_id) as psurank
1376 ,CASE PARENT_BO_CODE
1377 WHEN 'PARTY_SITE' THEN
1378 (SELECT
1379 CASE party_type
1380 WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
1381 WHEN 'PERSON' THEN 'HZ_PARTIES'
1382 WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
1383 ELSE NULL
1384 END
1385 FROM HZ_PARTIES
1386 WHERE party_id = (SELECT ps.party_id
1387 FROM HZ_PARTY_SITES ps
1388 WHERE ps.party_site_id = PARENT_ID))
1389 ELSE NULL
1390 END parent_entity_name -- this is grand parent tbl name of Person extn - written as parent entity name
1391 ,CASE PARENT_BO_CODE
1392 WHEN 'PARTY_SITE' THEN
1393 (SELECT
1394 CASE p.party_type
1395 WHEN 'ORGANIZATION' THEN p.party_id
1396 WHEN 'PERSON' THEN p.party_id
1397 WHEN 'PARTY_RELATIONSHIP' THEN
1398 (SELECT oc.org_contact_id
1399 FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
1400 WHERE r.relationship_id = oc.party_relationship_id
1401 AND r.party_id = p.party_id
1402 AND r.subject_type = 'PERSON'
1403 AND r.object_type = 'ORGANIZATION')
1404 ELSE NULL
1405 END
1406 FROM hz_parties p
1407 WHERE p.party_id = (select ps.party_id
1408 from HZ_PARTY_SITES ps
1409 where ps.party_site_id = PARENT_ID)
1410 )
1411 ELSE NULL
1412 END parent_id -- this is the grand parent id of Person extn - written as parent
1413 ,CASE PARENT_BO_CODE
1414 WHEN 'PARTY_SITE' THEN
1415 (SELECT
1416 CASE party_type
1417 WHEN 'ORGANIZATION' THEN 'ORG'
1418 WHEN 'PERSON' THEN 'PERSON'
1419 WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
1420 ELSE NULL
1421 END
1422 FROM hz_parties
1423 WHERE party_id = (SELECT party_id
1424 FROM HZ_PARTY_SITES
1425 WHERE party_site_id = PARENT_ID)
1426 )
1427 ELSE NULL
1428 END parent_bo_code -- this is the grand parent bo, written as parent
1429 FROM HZ_BUS_OBJ_TRACKING
1430 WHERE CHILD_ENTITY_NAME = 'HZ_PARTY_SITE_USES'
1431 AND event_id IS NULL) temp
1432 WHERE NOT EXISTS
1433 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
1434 WHERE bot.event_id IS NULL
1435 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
1436 AND bot.CHILD_ID = temp.child_id
1437 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
1438 AND psurank = 1
1439 AND temp.child_id IS NOT NULL
1440 AND temp.CHILD_ENTITY_NAME IS NOT NULL
1441 AND temp.CHILD_BO_CODE IS NOT NULL;
1442 COMMIT;
1443 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1444 hz_utility_v2pub.DEBUG
1445 (p_message=>'ei_HZ_PARTY_SITE_USES-',
1446 p_prefix=>l_debug_prefix,
1447 p_msg_level=>fnd_log.level_procedure,
1448 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1449 p_module =>'ei_psu');
1450 END IF;
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 LOG( message => 'ei_HZ_PARTY_SITE_USES:'||SQLERRM,
1454 p_prefix =>l_debug_prefix,
1455 p_module =>'ei_psu');
1456 ROLLBACK;
1457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1458 END ei_HZ_PARTY_SITE_USES;
1459 -----------------------------------------------------------------
1460 /*
1461 Procedure name: PROCEDURE ei_HZ_PARTY_SITES()
1462 Purpose: Writes the parent node record for HZ_PARTY_SITES in BOT
1463 Scope: internal
1464 Called From: This pkg
1465 Called By: populate_missing_links()
1466 */
1467 -----------------------------------------------------------------
1468
1469 PROCEDURE ei_HZ_PARTY_SITES IS
1470
1471 -- local variables
1472 l_debug_prefix VARCHAR2(40) := 'EI_HZ_PARTY_SITES';
1473
1474 BEGIN
1475 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1476 hz_utility_v2pub.DEBUG
1477 (p_message=>'ei_HZ_PARTY_SITES+',
1478 p_prefix=>l_debug_prefix,
1479 p_msg_level=>fnd_log.level_procedure,
1480 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1481 p_module =>'ei_ps');
1482 END IF;
1483 /*
1484 Party Site has following parents
1485 1. HZ_PARTIES (Org, Person, OrgContact)
1486
1487 Hence, HZ_PARTY_SITES can exist in 3 different scenarios.
1488 The different combinations of (parent, grand parent) are :
1489 1. (Org, null) 2. (Person, null) 3. (OrgContact, Org)
1490
1491 The following SQL gets the Parent and Grand parent info of each HZ_PARTY_SITES
1492 record that was not already processed in BOT.
1493
1494 Test Cases for the following SQL are:
1495 Create HZ_PARTY_SITES rec in BOT with (Parent, Grand Parent)
1496 combinations existing in TCA data model
1497 1. (Org, null) -- tested
1498 2. (Person, null) -- tested
1499 3. (OrgContact, Org) -- tested
1500 */
1501
1502 /* A note on the Structure of the insert statement
1503 1. The parent record is tracked by the insert statement.
1504 2. The "inner select" acts as a data source for "outer select"
1505 3. The "outer select" uses "select to exclude the parents
1506 that were already written to BOT"
1507 4. There may be duplicate rows for any given parent retured by the "inner select".
1508 This is because, in a given execution of the following SQL,
1509 there may be multiple children for a given parent.
1510
1511 Each child contributes in getting its parent. This is as per the design
1512 of "inner select".
1513
1514 To avoid duplicate rows of a parent returned by the siblings,
1515 the inner select ranks all the duplicate parents.
1516 The "outer select" filters on parents with rank = 1.
1517 This clause helps to filter out the duplicate parent rows,
1518 before data was inserted by insert statement.
1519
1520 5. The "inner select" is operating on the child record and trying to identify
1521 the parent and grand parent information.
1522 The parent information of the child record will be child
1523 (current/its) information for the parent record.
1524 The grand parent information of the child record will be parent info
1525 of the parent record.
1526 Because of this reason, "inner select" statement aliases the columns.
1527
1528 example:
1529
1530 child record:
1531 child_id: 123, child_entity_name: HZ_PARTY_SITES,
1532 child_bo: PS, parent_bo: OrgContact, parent_entity_name: OC,
1533 parent_id: 234
1534
1535 The "inner select" fetches above record from BOT and identifies
1536 its parent, grand parent information and present it as follows:
1537
1538 parent_id aliased as child_id: 234
1539 parent_entity_name aliased as child_entity_name: HZ_ORG_CONTACTS
1540 parent_bo aliased as child_bo: OrgContact
1541 grand_parent_id aliased as parent id: 456
1542 grand_parent_entity_name aliased as parent_entity_name: HZ_PARTIES
1543 grand_parent_bo aliased as parent_bo: Org
1544
1545 Insert statement will take this result and write it as
1546 child record:
1547 child_id: 234, child_entity_name: HZ_ORG_CONTACTS,
1548 child_bo: OrgContact, parent_bo: Org, parent_entity_name: HZ_PARTIES,
1549 parent_id: 456
1550 6. It is non-trivial to figure out the business object codes for both parent
1551 and grand parent, grand parent identifier or grand parent entity name.
1552 To do this, "inner select" uses case statement on parent_entity_name.
1553 Some times, an embedded SQL is necessary to fgure out this.
1554 Example:
1555 Child is HZ_PARTY_SITES.
1556 Parent is OrgContact and it's parent is an Org.
1557 */
1558
1559 INSERT INTO HZ_BUS_OBJ_TRACKING
1560 (CHILD_ENTITY_NAME, CHILD_ID,
1561 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1562 LAST_UPDATE_DATE, CHILD_BO_CODE,
1563 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
1564 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
1565 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1566 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
1567 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
1568 FROM
1569 (SELECT -- inner select
1570 PARENT_ENTITY_NAME child_entity_name
1571 ,PARENT_ID child_id
1572 ,PARENT_BO_CODE CHILD_BO_CODE
1573 ,'U' child_operation_flag
1574 ,'Y' populated_flag
1575 ,LAST_UPDATE_DATE
1576 ,RANK()
1577 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
1578 ORDER BY LAST_UPDATE_DATE, child_id) as psrank
1579 ,CASE PARENT_BO_CODE
1580 WHEN 'ORG' THEN NULL
1581 WHEN 'PERSON' THEN NULL
1582 WHEN 'ORG_CONTACT' THEN 'HZ_PARTIES'
1583 ELSE NULL
1584 END parent_entity_name -- this is grand parent tbl name of PS, written as parent entity name
1585 ,CASE PARENT_BO_CODE
1586 WHEN 'ORG' THEN NULL
1587 WHEN 'PERSON' THEN NULL
1588 WHEN 'ORG_CONTACT' THEN
1589 (SELECT r.object_id
1590 FROM hz_relationships r, hz_org_contacts oc
1591 WHERE oc.org_contact_id = PARENT_ID
1592 AND oc.party_relationship_id = r.relationship_id
1593 AND subject_type ='PERSON'
1594 AND object_type = 'ORGANIZATION')
1595 ELSE NULL
1596 END parent_id -- this is grand parent id of PS, written as parent id
1597 ,CASE PARENT_BO_CODE
1598 WHEN 'ORG' THEN NULL
1599 WHEN 'PERSON' THEN NULL
1600 WHEN 'ORG_CONTACT' THEN 'ORG'
1601 ELSE NULL
1602 END parent_bo_code -- this is the grand parent bo, written as parent
1603 FROM HZ_BUS_OBJ_TRACKING
1604 WHERE CHILD_ENTITY_NAME = 'HZ_PARTY_SITES'
1605 AND event_id IS NULL) temp
1606 WHERE NOT EXISTS
1607 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
1608 WHERE bot.event_id IS NULL
1609 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
1610 AND bot.CHILD_ID = temp.child_id
1611 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
1612 AND psrank = 1
1613 AND temp.child_id IS NOT NULL
1614 AND temp.CHILD_ENTITY_NAME IS NOT NULL
1615 AND temp.CHILD_BO_CODE IS NOT NULL ;
1616 -- save the records populated
1617 COMMIT;
1618 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1619 hz_utility_v2pub.DEBUG
1620 (p_message=>'ei_HZ_PARTY_SITES-',
1621 p_prefix=>l_debug_prefix,
1622 p_msg_level=>fnd_log.level_procedure,
1623 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1624 p_module =>'ei_ps');
1625 END IF;
1626 EXCEPTION
1627 WHEN OTHERS THEN
1628 LOG( message => 'ei_HZ_PARTY_SITES:'||SQLERRM,
1629 p_prefix =>l_debug_prefix,
1630 p_module =>'ei_ps');
1631 ROLLBACK;
1632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1633 END ei_HZ_PARTY_SITES;
1634 -----------------------------------------------------------------
1635 /*
1636 Procedure name: PROCEDURE ei_HZ_ORG_CONTACTS()
1637 Purpose: Writes the parent node record for HZ_ORG_CONTACTS in BOT
1638 Scope: internal
1639 Called From: This pkg
1640 Called By: populate_missing_links()
1641 */
1642 -----------------------------------------------------------------
1643
1644 PROCEDURE ei_HZ_ORG_CONTACTS IS
1645
1646 -- local variables
1647 l_debug_prefix VARCHAR2(40) := 'EI_HZ_ORG_CONTACTS';
1648
1649 BEGIN
1650 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1651 hz_utility_v2pub.DEBUG
1652 (p_message=>'ei_HZ_ORG_CONTACTS+',
1653 p_prefix=>l_debug_prefix,
1654 p_msg_level=>fnd_log.level_procedure,
1655 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1656 p_module =>'ei_oc');
1657 END IF;
1658 /*
1659 Party Site has following parents
1660 1. HZ_PARTIES (Org)
1661
1662 The following SQL gets the Parent and Grand parent info of each HZ_ORG_CONTACTS
1663 record that was not already processed in BOT.
1664
1665 Test Cases for the following SQL are:
1666 Create HZ_ORG_CONTACTS rec in BOT with (Parent, Grand Parent)
1667 combinations existing in TCA data model
1668 1. (Org, null) -- tested
1669 */
1670
1671 /* A note on the Structure of the insert statement
1672 1. The parent record is tracked by the insert statement.
1673 2. The "inner select" acts as a data source for "outer select"
1674 3. The "outer select" uses "select to exclude the parents
1675 that were already written to BOT"
1676 4. There may be duplicate rows for any given parent retured by the "inner select".
1677 This is because, in a given execution of the following SQL,
1678 there may be multiple children for a given parent.
1679
1680 Each child contributes in getting its parent. This is as per the design
1681 of "inner select".
1682
1683 To avoid duplicate rows of a parent returned by the siblings,
1684 the inner select ranks all the duplicate parents.
1685 The "outer select" filters on parents with rank = 1.
1686 This clause helps to filter out the duplicate parent rows,
1687 before data was inserted by insert statement.
1688 */
1689
1690 INSERT INTO HZ_BUS_OBJ_TRACKING
1691 (CHILD_ENTITY_NAME, CHILD_ID,
1692 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1693 LAST_UPDATE_DATE, CHILD_BO_CODE,
1694 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
1695 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
1696 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1697 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
1698 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
1699 FROM
1700 (SELECT -- inner select
1701 PARENT_ENTITY_NAME child_entity_name
1702 ,PARENT_ID child_id
1703 ,PARENT_BO_CODE CHILD_BO_CODE
1704 ,'U' child_operation_flag
1705 ,'Y' populated_flag
1706 ,LAST_UPDATE_DATE
1707 ,RANK()
1708 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
1709 ORDER BY LAST_UPDATE_DATE, child_id) as ocrank
1710 , NULL parent_entity_name -- this is grand parent tbl name of PS, written as parent entity name
1711 , NULL parent_id -- this is grand parent id of PS, written as parent id
1712 , NULL parent_bo_code -- this is the grand parent bo, written as parent
1713 FROM HZ_BUS_OBJ_TRACKING
1714 WHERE CHILD_ENTITY_NAME = 'HZ_ORG_CONTACTS'
1715 AND event_id IS NULL) temp
1716 WHERE NOT EXISTS
1717 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
1718 WHERE bot.event_id IS NULL
1719 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
1720 AND bot.CHILD_ID = temp.child_id
1721 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
1722 AND ocrank = 1
1723 AND temp.child_id IS NOT NULL
1724 AND temp.CHILD_ENTITY_NAME IS NOT NULL
1725 AND temp.CHILD_BO_CODE IS NOT NULL ;
1726 -- save the records populated
1727 COMMIT;
1728 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1729 hz_utility_v2pub.DEBUG
1730 (p_message=>'ei_HZ_ORG_CONTACTS-',
1731 p_prefix=>l_debug_prefix,
1732 p_msg_level=>fnd_log.level_procedure,
1733 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1734 p_module =>'ei_oc');
1735 END IF;
1736 EXCEPTION
1737 WHEN OTHERS THEN
1738 LOG( message => 'ei_HZ_ORG_CONTACTS:'||SQLERRM,
1739 p_prefix =>l_debug_prefix,
1740 p_module =>'ei_oc');
1741 ROLLBACK;
1742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1743 END ei_HZ_ORG_CONTACTS;
1744 -----------------------------------------------------------------
1745 /*
1746 Procedure name: PROCEDURE ei_HZ_LOCATIONS_EXT()
1747 Purpose: Writes the parent node record for HZ_LOCATIONS_EXT_VL in BOT
1748 Scope: internal
1749 Called From: This pkg
1750 Called By: populate_missing_links()
1751 */
1752 -----------------------------------------------------------------
1753
1754 PROCEDURE ei_HZ_LOCATIONS_ext IS
1755
1756 -- local variables
1757 l_debug_prefix VARCHAR2(40) := 'ei_HZ_LOCATIONS_EXT';
1758
1759 BEGIN
1760 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1761 hz_utility_v2pub.DEBUG
1762 (p_message=>'ei_HZ_LOCATIONS_ext+',
1763 p_prefix=>l_debug_prefix,
1764 p_msg_level=>fnd_log.level_procedure,
1765 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1766 p_module =>'ei_locext');
1767 END IF;
1768 /*
1769 Following entities are parents of HZ_LOCATIONS_EXT_VL
1770 1. HZ_LOCATIONS (Location)
1771
1772 The TCA Extensibility EOs will not write the parent (as child) record in BOT.
1773
1774 The following SQL gets the Parent and Grand parent info of each HZ_LOCATIONS_EXT_VL
1775 record that was not already processed in BOT.
1776
1777 Because of the requirment to support Location Sharing, there could be
1778 multiple parents for a given Location record.
1779
1780 Test Cases for the following SQL are:
1781 Create HZ_LOCATIONS_EXT_VL rec in BOT with (Parent, Grand Parent)
1782 combinations existing in TCA data model
1783 1. (Location, Party Site 1) -- tested
1784 2. (Location, Party Site 2) -- tested
1785 */
1786
1787 /* A note on the Structure of the insert statement
1788 1. The parent record is tracked by the insert statement.
1789 2. The "inner select" acts as a data source for "outer select"
1790 3. The "outer select" uses "select to exclude the parents
1791 that were already written to BOT"
1792 4. There may be duplicate rows for any given parent retured by the "inner select".
1793 This is because, in a given execution of the following SQL,
1794 there may be multiple children for a given parent.
1795
1796 Each child contributes in getting its parent. This is as per the design
1797 of "inner select".
1798
1799 To avoid duplicate rows of a parent returned by the siblings,
1800 the inner select ranks all the parents duplicate parents.
1801 The "outer select" filters on parents with rank = 1.
1802 This clause helps to filter out the duplicate parent rows,
1803 before data was inserted by insert statement.
1804
1805 5. The "inner select" is operating on the child record and trying to identify
1806 the parent and grand parent information.
1807 The parent information of the child record will be child
1808 (current/its) information for the parent record.
1809 The grand parent information of the child record will be parent info
1810 of the parent record.
1811 Because of this reason, "inner select" statement aliases the columns.
1812 */
1813
1814 INSERT INTO HZ_BUS_OBJ_TRACKING
1815 (CHILD_ENTITY_NAME, CHILD_ID,
1816 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1817 LAST_UPDATE_DATE, CHILD_BO_CODE,
1818 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
1819 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
1820 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1821 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
1822 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
1823 FROM
1824 (SELECT -- inner select
1825 t.PARENT_ENTITY_NAME child_entity_name
1826 ,t.PARENT_ID child_id
1827 ,t.PARENT_BO_CODE CHILD_BO_CODE
1828 ,'U' child_operation_flag
1829 ,'Y' populated_flag
1830 ,t.LAST_UPDATE_DATE
1831 ,RANK()
1832 OVER (PARTITION BY PARENT_ENTITY_NAME, PARENT_ID
1833 ORDER BY t.LAST_UPDATE_DATE, child_id) as locxrank
1834 ,'HZ_PARTY_SITES' parent_entity_name -- this is grand parent tbl name of CP - written as parent entity name
1835 , 'PARTY_SITE' parent_bo_code -- this is the grand parent bo, written as parent
1836 , ps.party_site_id parent_id
1837 FROM HZ_BUS_OBJ_TRACKING t, hz_party_sites ps
1838 WHERE t.CHILD_ENTITY_NAME = 'HZ_LOCATIONS_EXT_VL'
1839 AND t.event_id IS NULL
1840 AND t.parent_id = ps.location_id) temp
1841 WHERE NOT EXISTS
1842 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
1843 WHERE bot.event_id IS NULL
1844 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
1845 AND bot.CHILD_ID = temp.child_id
1846 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE
1847 AND bot.parent_id = temp.parent_id)
1848 AND locxrank = 1
1849 AND temp.child_id IS NOT NULL
1850 AND temp.CHILD_ENTITY_NAME IS NOT NULL
1851 AND temp.CHILD_BO_CODE IS NOT NULL;
1852 -- save the records populated
1853 COMMIT;
1854 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1855 hz_utility_v2pub.DEBUG
1856 (p_message=>'ei_HZ_LOCATIONS_ext-',
1857 p_prefix=>l_debug_prefix,
1858 p_msg_level=>fnd_log.level_procedure,
1859 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1860 p_module =>'ei_locext');
1861 END IF;
1862 EXCEPTION
1863 WHEN OTHERS THEN
1864 LOG( message => 'ei_HZ_LOCATIONS_ext:'||SQLERRM,
1865 p_prefix =>l_debug_prefix,
1866 p_module =>'ei_locext');
1867 ROLLBACK;
1868 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1869 END ei_HZ_LOCATIONS_ext;
1870 -----------------------------------------------------------------
1871 /*
1872 Procedure name: PROCEDURE ei_HZ_LOCATIONS()
1873 Purpose: Writes the parent node record for HZ_LOCATIONS in BOT
1874 Scope: internal
1875 Called From: This pkg
1876 Called By: populate_missing_links()
1877 */
1878 -----------------------------------------------------------------
1879
1880 PROCEDURE ei_HZ_LOCATIONS IS
1881
1882 -- local variables
1883 l_debug_prefix VARCHAR2(40) := 'EI_HZ_LOCATIONS';
1884
1885 BEGIN
1886 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1887 hz_utility_v2pub.DEBUG
1888 (p_message=>'ei_HZ_LOCATIONS+',
1889 p_prefix=>l_debug_prefix,
1890 p_msg_level=>fnd_log.level_procedure,
1891 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
1892 p_module =>'ei_loc');
1893 END IF;
1894 /*
1895 Following entities are parents of HZ_LOCATIONS
1896 1. HZ_PARTY_SITES (PARTY_SITE)
1897
1898 Hence, HZ_LOCATIONS can exist three different scenarios.
1899 The different combinations of (parent, grand parent) are :
1900 1. (PARTY_SITE, Person)
1901 2. (PARTY_SITE, Organization)
1902 3. (PARTY_SITE, OrgContact)
1903
1904 The following SQL gets the Parent and Grand parent info of each HZ_LOCATIONS
1905 record that was not already processed in BOT.
1906
1907 Test Cases for the following SQL are:
1908 Create HZ_LOCATIONS rec in BOT with (Parent, Grand Parent)
1909 combinations existing in TCA data model
1910 1. (PARTY_SITE, Person) -- tested
1911 2. (PARTY_SITE, Organization) -- tested
1912 3. (PARTY_SITE, OrgContact, Org) --tested
1913 */
1914
1915 /* A note on the Structure of the insert statement
1916 1. The parent record is tracked by the insert statement.
1917 2. The "inner select" acts as a data source for "outer select"
1918 3. The "outer select" uses "select to exclude the parents
1919 that were already written to BOT"
1920 4. There may be duplicate rows for any given parent retured by the "inner select".
1921 This is because, in a given execution of the following SQL,
1922 there may be multiple children for a given parent.
1923
1924 Each child contributes in getting its parent. This is as per the design
1925 of "inner select".
1926
1927 To avoid duplicate rows of a parent returned by the siblings,
1928 the inner select ranks all the parents duplicate parents.
1929 The "outer select" filters on parents with rank = 1.
1930 This clause helps to filter out the duplicate parent rows,
1931 before data was inserted by insert statement.
1932
1933 5. The "inner select" is operating on the child record and trying to identify
1934 the parent and grand parent information.
1935 The parent information of the child record will be child
1936 (current/its) information for the parent record.
1937 The grand parent information of the child record will be parent info
1938 of the parent record.
1939 Because of this reason, "inner select" statement aliases the columns.
1940
1941 example:
1942
1943 child record:
1944 child_id: 123, child_entity_name: HZ_LOCATIONS,
1945 child_bo: LOCATION, parent_bo: PS, parent_entity_name: HZ_PARTY_SITES,
1946 parent_id: 234
1947
1948 The "inner select" fetches above record from BOT and identifies
1949 its parent, grand parent information and present it as follows:
1950
1951 parent_id aliased as child_id: 234
1952 parent_entity_name aliased as child_entity_name: HZ_PARTY_SITES
1953 parent_bo aliased as child_bo: PARTY_SITE
1954 grand_parent_id aliased as parent id: 456
1955 grand_parent_entity_name aliased as parent_entity_name: HZ_ORG_CONTACTS
1956 grand_parent_bo aliased as parent_bo: Org_Contact
1957
1958 Insert statement will take this result and write it as
1959 child record:
1960 child_id: 234, child_entity_name: HZ_PARTY_SITES,
1961 child_bo: PARTY_SITE, parent_bo: Org_Contact, parent_entity_name: HZ_ORG_CONTACTS,
1962 parent_id: 456
1963
1964 6. It is non-trivial to figure out the business object codes for both parent
1965 and grand parent, grand parent identifier or grand parent entity name.
1966 To do this, "inner select" uses case statement on parent_entity_name.
1967 Some times, an embedded SQL is necessary to fgure out this.
1968 Example:
1969 Child is HZ_LOCATIONS.
1970 Parent is PARTY_SITE and it's parent is Org_Contact.
1971 */
1972
1973 INSERT INTO HZ_BUS_OBJ_TRACKING
1974 (CHILD_ENTITY_NAME, CHILD_ID,
1975 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1976 LAST_UPDATE_DATE, CHILD_BO_CODE,
1977 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
1978 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
1979 CHILD_OPERATION_FLAG, POPULATED_FLAG,
1980 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
1981 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
1982 FROM
1983 (SELECT -- inner select
1984 PARENT_ENTITY_NAME child_entity_name
1985 ,PARENT_ID child_id
1986 ,PARENT_BO_CODE CHILD_BO_CODE
1987 ,'U' child_operation_flag
1988 ,'Y' populated_flag
1989 ,LAST_UPDATE_DATE
1990 ,RANK()
1991 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
1992 ORDER BY LAST_UPDATE_DATE, child_id) as psurank
1993 ,CASE PARENT_BO_CODE
1994 WHEN 'PARTY_SITE' THEN
1995 (SELECT
1996 CASE party_type
1997 WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
1998 WHEN 'PERSON' THEN 'HZ_PARTIES'
1999 WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
2000 ELSE NULL
2001 END
2002 FROM HZ_PARTIES
2003 WHERE party_id = (SELECT ps.party_id
2004 FROM HZ_PARTY_SITES ps
2005 WHERE ps.party_site_id = PARENT_ID))
2006 ELSE NULL
2007 END parent_entity_name -- this is grand parent tbl name of Person extn - written as parent entity name
2008 ,CASE PARENT_BO_CODE
2009 WHEN 'PARTY_SITE' THEN
2010 (SELECT
2011 CASE p.party_type
2012 WHEN 'ORGANIZATION' THEN p.party_id
2013 WHEN 'PERSON' THEN p.party_id
2014 WHEN 'PARTY_RELATIONSHIP' THEN
2015 (SELECT oc.org_contact_id
2016 FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
2017 WHERE r.relationship_id = oc.party_relationship_id
2018 AND r.party_id = p.party_id
2019 AND r.subject_type = 'PERSON'
2020 AND r.object_type = 'ORGANIZATION')
2021 ELSE NULL
2022 END
2023 FROM hz_parties p
2024 WHERE p.party_id = (select ps.party_id
2025 from HZ_PARTY_SITES ps
2026 where ps.party_site_id = PARENT_ID)
2027 )
2028 ELSE NULL
2029 END parent_id -- this is the grand parent id of Person extn - written as parent
2030 ,CASE PARENT_BO_CODE
2031 WHEN 'PARTY_SITE' THEN
2032 (SELECT
2033 CASE party_type
2034 WHEN 'ORGANIZATION' THEN 'ORG'
2035 WHEN 'PERSON' THEN 'PERSON'
2036 WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
2037 ELSE NULL
2038 END
2039 FROM hz_parties
2040 WHERE party_id = (SELECT party_id
2041 FROM HZ_PARTY_SITES
2042 WHERE party_site_id = PARENT_ID)
2043 )
2044 ELSE NULL
2045 END parent_bo_code -- this is the grand parent bo, written as parent
2046 FROM HZ_BUS_OBJ_TRACKING
2047 WHERE CHILD_ENTITY_NAME = 'HZ_LOCATIONS'
2048 AND event_id IS NULL) temp
2049 WHERE NOT EXISTS
2050 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
2051 WHERE bot.event_id IS NULL
2052 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
2053 AND bot.CHILD_ID = temp.child_id
2054 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
2055 AND psurank = 1
2056 AND temp.child_id IS NOT NULL
2057 AND temp.CHILD_ENTITY_NAME IS NOT NULL
2058 AND temp.CHILD_BO_CODE IS NOT NULL ;
2059 -- save the records populated
2060 COMMIT;
2061 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2062 hz_utility_v2pub.DEBUG
2063 (p_message=>'ei_HZ_LOCATIONS-',
2064 p_prefix=>l_debug_prefix,
2065 p_msg_level=>fnd_log.level_procedure,
2066 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2067 p_module =>'ei_loc');
2068 END IF;
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 LOG( message => 'ei_HZ_LOCATIONS:'||SQLERRM,
2072 p_prefix =>l_debug_prefix,
2073 p_module =>'ei_loc');
2074 ROLLBACK;
2075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2076 END ei_HZ_LOCATIONS;
2077 -----------------------------------------------------------------
2078 /*
2079 Procedure name: PROCEDURE ei_HZ_CUST_SITE_USES_ALL()
2080 Purpose: Writes the parent node record for HZ_CUST_SITE_USES_ALL in BOT
2081 Scope: internal
2082 Called From: This pkg
2083 Called By: populate_missing_links()
2084 */
2085 -----------------------------------------------------------------
2086
2087 PROCEDURE ei_HZ_CUST_SITE_USES_ALL IS
2088
2089 -- local variables
2090 l_debug_prefix VARCHAR2(40) := 'EI_HZ_CUST_SITE_USES_ALL';
2091
2092 BEGIN
2093 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2094 hz_utility_v2pub.DEBUG
2095 (p_message=>'ei_HZ_CUST_SITE_USES_ALL+',
2096 p_prefix=>l_debug_prefix,
2097 p_msg_level=>fnd_log.level_procedure,
2098 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2099 p_module =>'ei_actsiteuse');
2100 END IF;
2101 /*
2102 HZ_CUST_SITE_USES_ALL has following parent
2103 1. HZ_CUST_ACCT_SITES_ALL (CUST_ACCT_SITE)
2104
2105 Hence, HZ_CUST_SITE_USES_ALL can exist in 3 different scenarios.
2106 The different combinations of (parent, grand parent) are :
2107 1. (CUST_ACCT_SITE, CUST_ACCT)
2108 2. (CUST_ACCT, PERSON_CUST)
2109 3. (CUST_ACCT, ORG_CUST)
2110
2111 The following SQL gets the Parent and Grand parent info of each HZ_CUST_SITE_USES_ALL
2112 record that was not already processed in BOT.
2113
2114 Test Cases for the following SQL are:
2115 Create HZ_CUST_SITE_USES_ALL rec in BOT with (Parent, Grand Parent)
2116 combinations existing in TCA data model
2117 1. (CUST_ACCT_SITE, CUST_ACCT) -- tested
2118 2. (CUST_ACCT, PERSON_CUST) -- tested
2119 3. (CUST_ACCT, ORG_CUST) -- tested
2120 */
2121
2122 /* A note on the Structure of the insert statement
2123 1. The parent record is tracked by the insert statement.
2124 2. The "inner select" acts as a data source for "outer select"
2125 3. The "outer select" uses "select to exclude the parents
2126 that were already written to BOT"
2127 4. There may be duplicate rows for any given parent retured by the "inner select".
2128 This is because, in a given execution of the following SQL,
2129 there may be multiple children for a given parent.
2130
2131 Each child contributes in getting its parent. This is as per the design
2132 of "inner select".
2133
2134 To avoid duplicate rows of a parent returned by the siblings,
2135 the inner select ranks all the duplicate parents.
2136 The "outer select" filters on parents with rank = 1.
2137 This clause helps to filter out the duplicate parent rows,
2138 before data was inserted by insert statement.
2139
2140 5. The "inner select" is operating on the child record and trying to identify
2141 the parent and grand parent information.
2142 The parent information of the child record will be child
2143 (current/its) information for the parent record.
2144 The grand parent information of the child record will be parent info
2145 of the parent record.
2146 Because of this reason, "inner select" statement aliases the columns.
2147
2148 example:
2149
2150 child record:
2151 child_id: 123, child_entity_name: HZ_CUST_SITE_USES_ALL,
2152 child_bo: CUST_ACCT_SITE_USE, parent_bo: CUST_ACCT_SITE, parent_entity_name: HZ_CUST_ACCT_SITES_ALL,
2153 parent_id: 234
2154
2155 The "inner select" fetches above record from BOT and identifies
2156 its parent, grand parent information and present it as follows:
2157
2158 parent_id aliased as child_id: 234
2159 parent_entity_name aliased as child_entity_name: HZ_CUST_ACCT_SITES_ALL
2160 parent_bo aliased as child_bo: CUST_ACCT_SITE
2161 grand_parent_id aliased as parent id: 456
2162 grand_parent_entity_name aliased as parent_entity_name: HZ_CUST_ACCOUNTS
2163 grand_parent_bo aliased as parent_bo: CUST_ACCT
2164
2165 Insert statement will take this result and write it as
2166 child record:
2167 child_id: 234, child_entity_name: HZ_CUST_ACCT_SITES_ALL,
2168 child_bo: CUST_ACCT_SITE, parent_bo: CUST_ACCT, parent_entity_name: HZ_CUST_ACCOUNTS,
2169 parent_id: 456
2170 6. It is non-trivial to figure out the business object codes for both parent
2171 and grand parent, grand parent identifier or grand parent entity name.
2172 To do this, "inner select" uses case statement on parent_entity_name.
2173 Some times, an embedded SQL is necessary to fgure out this.
2174 Example:
2175 Child is CUST_ACCT_SITE.
2176 Parent is CUST_ACCT and it's parent is ORG_CUST.
2177 */
2178
2179 INSERT INTO HZ_BUS_OBJ_TRACKING
2180 (CHILD_ENTITY_NAME, CHILD_ID,
2181 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2182 LAST_UPDATE_DATE, CHILD_BO_CODE,
2183 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
2184 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
2185 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2186 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
2187 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
2188 FROM
2189 (SELECT -- inner select
2190 PARENT_ENTITY_NAME child_entity_name
2191 ,PARENT_ID child_id
2192 ,PARENT_BO_CODE CHILD_BO_CODE
2193 ,'HZ_CUST_ACCOUNTS' parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
2194 ,'CUST_ACCT' parent_bo_code -- this is the grand parent bo, written as parent
2195 , CUST_ACCOUNT_ID parent_id -- this is grand parent id of acct roles, written as parent id
2196 ,'U' child_operation_flag
2197 ,'Y' populated_flag
2198 ,t.LAST_UPDATE_DATE
2199 ,RANK()
2200 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
2201 ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
2202 FROM HZ_BUS_OBJ_TRACKING t, HZ_CUST_ACCT_SITES_ALL s
2203 WHERE t.CHILD_ENTITY_NAME = 'HZ_CUST_SITE_USES_ALL'
2204 AND t.event_id IS NULL
2205 AND t.parent_id = s.CUST_ACCT_SITE_ID) temp
2206 WHERE NOT EXISTS
2207 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
2208 WHERE bot.event_id IS NULL
2209 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
2210 AND bot.CHILD_ID = temp.child_id
2211 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
2212 AND acrrank = 1
2213 AND temp.child_id IS NOT NULL
2214 AND temp.CHILD_ENTITY_NAME IS NOT NULL
2215 AND temp.CHILD_BO_CODE IS NOT NULL;
2216 -- save the records populated
2217 COMMIT;
2218 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2219 hz_utility_v2pub.DEBUG
2220 (p_message=>'ei_HZ_CUST_SITE_USES_ALL-',
2221 p_prefix=>l_debug_prefix,
2222 p_msg_level=>fnd_log.level_procedure,
2223 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2224 p_module =>'ei_actsiteuse');
2225 END IF;
2226 EXCEPTION
2227 WHEN OTHERS THEN
2228 LOG( message => 'ei_HZ_CUST_SITE_USES_ALL:'||SQLERRM,
2229 p_prefix =>l_debug_prefix,
2230 p_module =>'ei_actsiteuse');
2231 ROLLBACK;
2232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2233 END ei_HZ_CUST_SITE_USES_ALL;
2234 -----------------------------------------------------------------
2235 /*
2236 Procedure name: PROCEDURE ei_HZ_CUST_ACCT_SITES_ALL()
2237 Purpose: Writes the parent node record for HZ_CUST_ACCT_SITES_ALL in BOT
2238 Scope: internal
2239 Called From: This pkg
2240 Called By: populate_missing_links()
2241 */
2242 -----------------------------------------------------------------
2243
2244 PROCEDURE ei_HZ_CUST_ACCT_SITES_ALL IS
2245
2246 -- local variables
2247 l_debug_prefix VARCHAR2(40) := 'EI_HZ_CUST_ACCT_SITES_ALL';
2248
2249 BEGIN
2250 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2251 hz_utility_v2pub.DEBUG
2252 (p_message=>'ei_HZ_CUST_ACCT_SITES_ALL+',
2253 p_prefix=>l_debug_prefix,
2254 p_msg_level=>fnd_log.level_procedure,
2255 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2256 p_module =>'ei_actsite');
2257 END IF;
2258 /*
2259 HZ_CUST_ACCT_SITES_ALL has following parent
2260 1. HZ_CUST_ACCOUNTS (CUST_ACCT)
2261
2262 Hence, HZ_CUST_ACCT_SITES_ALL can exist in 2 different scenarios.
2263 The different combinations of (parent, grand parent) are :
2264 1. (CUST_ACCT, PERSON_CUST) -- tested
2265 2. (CUST_ACCT, ORG_CUST) -- tested
2266
2267 The following SQL gets the Parent and Grand parent info of each HZ_CUST_ACCT_SITES_ALL
2268 record that was not already processed in BOT.
2269
2270 Test Cases for the following SQL are:
2271 Create HZ_CUST_ACCT_SITES_ALL rec in BOT with (Parent, Grand Parent)
2272 combinations existing in TCA data model
2273 1. (CUST_ACCT, PERSON_CUST) -- tested
2274 2. (CUST_ACCT, ORG_CUST) -- tested
2275 */
2276
2277 /* A note on the Structure of the insert statement
2278 1. The parent record is tracked by the insert statement.
2279 2. The "inner select" acts as a data source for "outer select"
2280 3. The "outer select" uses "select to exclude the parents
2281 that were already written to BOT"
2282 4. There may be duplicate rows for any given parent retured by the "inner select".
2283 This is because, in a given execution of the following SQL,
2284 there may be multiple children for a given parent.
2285
2286 Each child contributes in getting its parent. This is as per the design
2287 of "inner select".
2288
2289 To avoid duplicate rows of a parent returned by the siblings,
2290 the inner select ranks all the duplicate parents.
2291 The "outer select" filters on parents with rank = 1.
2292 This clause helps to filter out the duplicate parent rows,
2293 before data was inserted by insert statement.
2294
2295 5. The "inner select" is operating on the child record and trying to identify
2296 the parent and grand parent information.
2297 The parent information of the child record will be child
2298 (current/its) information for the parent record.
2299 The grand parent information of the child record will be parent info
2300 of the parent record.
2301 Because of this reason, "inner select" statement aliases the columns.
2302
2303 example:
2304
2305 child record:
2306 child_id: 123, child_entity_name: HZ_CUST_ACCT_SITES_ALL,
2307 child_bo: CUST_ACCT_SITE, parent_bo: CUST_ACCT, parent_entity_name: HZ_CUST_ACCOUNTS,
2308 parent_id: 234
2309
2310 The "inner select" fetches above record from BOT and identifies
2311 its parent, grand parent information and present it as follows:
2312
2313 parent_id aliased as child_id: 234
2314 parent_entity_name aliased as child_entity_name: HZ_CUST_ACCOUNTS
2315 parent_bo aliased as child_bo: CUST_ACCT
2316 grand_parent_id aliased as parent id: 456
2317 grand_parent_entity_name aliased as parent_entity_name: HZ_PARTIES
2318 grand_parent_bo aliased as parent_bo: ORG_CUST
2319
2320 Insert statement will take this result and write it as
2321 child record:
2322 child_id: 234, child_entity_name: HZ_CUST_ACCOUNTS,
2323 child_bo: CUST_ACCT, parent_bo: ORG_CUST, pa1rent_entity_name: HZ_PARTIES,
2324 parent_id: 456
2325 */
2326
2327 INSERT INTO HZ_BUS_OBJ_TRACKING
2328 (CHILD_ENTITY_NAME, CHILD_ID,
2329 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2330 LAST_UPDATE_DATE, CHILD_BO_CODE,
2331 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
2332 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
2333 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2334 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
2335 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
2336 FROM
2337 (SELECT -- inner select
2338 PARENT_ENTITY_NAME child_entity_name
2339 ,PARENT_ID child_id
2340 ,PARENT_BO_CODE CHILD_BO_CODE
2341 ,'HZ_PARTIES' parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
2342 ,DECODE(P.PARTY_TYPE, 'ORGANIZATION','ORG_CUST','PERSON','PERSON_CUST', NULL) parent_bo_code -- this is the grand parent bo, written as parent
2343 , ac.party_id parent_id -- this is grand parent id of acct roles, written as parent id
2344 ,'U' child_operation_flag
2345 ,'Y' populated_flag
2346 ,t.LAST_UPDATE_DATE
2347 ,RANK()
2348 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
2349 ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
2350 FROM HZ_BUS_OBJ_TRACKING t, HZ_CUST_ACCOUNTS ac, HZ_PARTIES p
2351 WHERE t.CHILD_ENTITY_NAME = 'HZ_CUST_ACCT_SITES_ALL'
2352 AND t.event_id IS NULL
2353 AND t.parent_id = ac.CUST_ACCOUNT_ID
2354 AND ac.party_id = p.party_id) temp
2355 WHERE NOT EXISTS
2356 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
2357 WHERE bot.event_id IS NULL
2358 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
2359 AND bot.CHILD_ID = temp.child_id
2360 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
2361 AND acrrank = 1
2362 AND temp.child_id IS NOT NULL
2363 AND temp.CHILD_ENTITY_NAME IS NOT NULL
2364 AND temp.CHILD_BO_CODE IS NOT NULL;
2365 -- save the records populated
2366 COMMIT;
2367 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2368 hz_utility_v2pub.DEBUG
2369 (p_message=>'ei_HZ_CUST_ACCT_SITES_ALL-',
2370 p_prefix=>l_debug_prefix,
2371 p_msg_level=>fnd_log.level_procedure,
2372 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2373 p_module =>'ei_actsite');
2374 END IF;
2375 EXCEPTION
2376 WHEN OTHERS THEN
2377 ROLLBACK;
2378 LOG( message => 'ei_HZ_CUST_ACCT_SITES_ALL:'||SQLERRM,
2379 p_prefix =>l_debug_prefix,
2380 p_module =>'ei_actsite');
2381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2382 END ei_HZ_CUST_ACCT_SITES_ALL;
2383 -----------------------------------------------------------------
2384 /*
2385 Procedure name: PROCEDURE ei_HZ_CUST_ACCOUNT_ROLES()
2386 Purpose: Writes the parent node record for HZ_CUST_ACCOUNT_ROLES in BOT
2387 Scope: internal
2388 Called From: This pkg
2389 Called By: populate_missing_links()
2390 */
2391 -----------------------------------------------------------------
2392
2393 PROCEDURE ei_HZ_CUST_ACCOUNT_ROLES IS
2394
2395 -- local variables
2396 l_debug_prefix VARCHAR2(40) := 'EI_HZ_CUST_ACCOUNT_ROLES';
2397
2398 BEGIN
2399 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2400 hz_utility_v2pub.DEBUG
2401 (p_message=>'ei_HZ_CUST_ACCOUNT_ROLES+',
2402 p_prefix=>l_debug_prefix,
2403 p_msg_level=>fnd_log.level_procedure,
2404 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2405 p_module =>'ei_actroles');
2406 END IF;
2407 /*
2408 Party Site has following parents
2409 1. HZ_CUST_ACCT_SITES_ALL (CUST_ACCT_SITE)
2410 2. HZ_CUST_ACCOUNTS (CUST_ACCT)
2411
2412 Hence, HZ_CUST_ACCOUNT_ROLES can exist in 3 different scenarios.
2413 The different combinations of (parent, grand parent) are :
2414 1. (CUST_ACCT_SITE, CUST_ACCT)
2415 2. (CUST_ACCT, PERSON_CUST)
2416 3. (CUST_ACCT, ORG_CUST)
2417
2418 The following SQL gets the Parent and Grand parent info of each HZ_CUST_ACCOUNT_ROLES
2419 record that was not already processed in BOT.
2420
2421 Test Cases for the following SQL are:
2422 Create HZ_CUST_ACCOUNT_ROLES rec in BOT with (Parent, Grand Parent)
2423 combinations existing in TCA data model
2424 1. (CUST_ACCT_SITE, CUST_ACCT) -- tested
2425 2. (CUST_ACCT, PERSON_CUST) -- tested
2426 3. (CUST_ACCT, ORG_CUST) -- tested
2427 */
2428
2429 /* A note on the Structure of the insert statement
2430 1. The parent record is tracked by the insert statement.
2431 2. The "inner select" acts as a data source for "outer select"
2432 3. The "outer select" uses "select to exclude the parents
2433 that were already written to BOT"
2434 4. There may be duplicate rows for any given parent retured by the "inner select".
2435 This is because, in a given execution of the following SQL,
2436 there may be multiple children for a given parent.
2437
2438 Each child contributes in getting its parent. This is as per the design
2439 of "inner select".
2440
2441 To avoid duplicate rows of a parent returned by the siblings,
2442 the inner select ranks all the duplicate parents.
2443 The "outer select" filters on parents with rank = 1.
2444 This clause helps to filter out the duplicate parent rows,
2445 before data was inserted by insert statement.
2446
2447 5. The "inner select" is operating on the child record and trying to identify
2448 the parent and grand parent information.
2449 The parent information of the child record will be child
2450 (current/its) information for the parent record.
2451 The grand parent information of the child record will be parent info
2452 of the parent record.
2453 Because of this reason, "inner select" statement aliases the columns.
2454
2455 example:
2456
2457 child record:
2458 child_id: 123, child_entity_name: HZ_CUST_ACCOUNT_ROLES,
2459 child_bo: CUST_ACCT_CONTACT, parent_bo: CUST_ACCT_SITE, parent_entity_name: HZ_CUST_ACCT_SITES_ALL,
2460 parent_id: 234
2461
2462 The "inner select" fetches above record from BOT and identifies
2463 its parent, grand parent information and present it as follows:
2464
2465 parent_id aliased as child_id: 234
2466 parent_entity_name aliased as child_entity_name: HZ_CUST_ACCT_SITES_ALL
2467 parent_bo aliased as child_bo: CUST_ACCT_SITE
2468 grand_parent_id aliased as parent id: 456
2469 grand_parent_entity_name aliased as parent_entity_name: HZ_CUST_ACCOUNTS
2470 grand_parent_bo aliased as parent_bo: CUST_ACCT
2471
2472 Insert statement will take this result and write it as
2473 child record:
2474 child_id: 234, child_entity_name: HZ_CUST_ACCT_SITES_ALL,
2475 child_bo: CUST_ACCT_SITE, parent_bo: CUST_ACCT, parent_entity_name: HZ_CUST_ACCOUNTS,
2476 parent_id: 456
2477 6. It is non-trivial to figure out the business object codes for both parent
2478 and grand parent, grand parent identifier or grand parent entity name.
2479 To do this, "inner select" uses case statement on parent_entity_name.
2480 Some times, an embedded SQL is necessary to fgure out this.
2481 Example:
2482 Child is HZ_CUST_ACCOUNT_ROLES.
2483 Parent is CUST_ACCT_SITE and it's parent is CUST_ACCT.
2484 */
2485
2486 INSERT INTO HZ_BUS_OBJ_TRACKING
2487 (CHILD_ENTITY_NAME, CHILD_ID,
2488 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2489 LAST_UPDATE_DATE, CHILD_BO_CODE,
2490 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
2491 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
2492 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2493 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
2494 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
2495 FROM
2496 (SELECT -- inner select
2497 PARENT_ENTITY_NAME child_entity_name
2498 ,PARENT_ID child_id
2499 ,PARENT_BO_CODE CHILD_BO_CODE
2500 ,'U' child_operation_flag
2501 ,'Y' populated_flag
2502 ,LAST_UPDATE_DATE
2503 ,RANK()
2504 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
2505 ORDER BY LAST_UPDATE_DATE, child_id) as acrrank
2506 ,CASE PARENT_BO_CODE
2507 WHEN 'CUST_ACCT_SITE' THEN 'HZ_CUST_ACCOUNTS'
2508 WHEN 'CUST_ACCT' THEN 'HZ_PARTIES'
2509 ELSE NULL
2510 END parent_entity_name -- this is grand parent tbl name of acct roles, written as parent entity name
2511 ,CASE PARENT_BO_CODE
2512 WHEN 'CUST_ACCT_SITE' THEN
2513 (SELECT CUST_ACCOUNT_ID
2514 FROM HZ_CUST_ACCT_SITES_ALL
2515 WHERE CUST_ACCT_SITE_ID = PARENT_ID)
2516 WHEN 'CUST_ACCT' THEN
2517 (SELECT PARTY_ID
2518 FROM HZ_CUST_ACCOUNTS
2519 WHERE CUST_ACCOUNT_ID = PARENT_ID)
2520 ELSE NULL
2521 END parent_id -- this is grand parent id of acct roles, written as parent id
2522 ,CASE PARENT_BO_CODE
2523 WHEN 'CUST_ACCT_SITE' THEN 'CUST_ACCT'
2524 WHEN 'CUST_ACCT' THEN
2525 (SELECT
2526 CASE p.party_type
2527 WHEN 'ORGANIZATION' THEN 'ORG_CUST'
2528 WHEN 'PERSON' THEN 'PERSON_CUST'
2529 ELSE NULL
2530 END
2531 FROM hz_parties p, HZ_CUST_ACCOUNTS c
2532 WHERE p.party_id = c.party_id
2533 AND c.CUST_ACCOUNT_ID = PARENT_ID)
2534 ELSE NULL
2535 END parent_bo_code -- this is the grand parent bo, written as parent
2536 FROM HZ_BUS_OBJ_TRACKING
2537 WHERE CHILD_ENTITY_NAME = 'HZ_CUST_ACCOUNT_ROLES'
2538 AND event_id IS NULL) temp
2539 WHERE NOT EXISTS
2540 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
2541 WHERE bot.event_id IS NULL
2542 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
2543 AND bot.CHILD_ID = temp.child_id
2544 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
2545 AND acrrank = 1
2546 AND temp.child_id IS NOT NULL
2547 AND temp.CHILD_ENTITY_NAME IS NOT NULL
2548 AND temp.CHILD_BO_CODE IS NOT NULL;
2549 -- save the records populated
2550 COMMIT;
2551 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2552 hz_utility_v2pub.DEBUG
2553 (p_message=>'ei_HZ_CUST_ACCOUNT_ROLES-',
2554 p_prefix=>l_debug_prefix,
2555 p_msg_level=>fnd_log.level_procedure,
2556 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2557 p_module =>'ei_actroles');
2558 END IF;
2559 EXCEPTION
2560 WHEN OTHERS THEN
2561 ROLLBACK;
2562 LOG( message => 'ei_HZ_CUST_ACCOUNT_ROLES:'||SQLERRM,
2563 p_prefix =>l_debug_prefix,
2564 p_module =>'ei_actroles');
2565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2566 END ei_HZ_CUST_ACCOUNT_ROLES;
2567 -----------------------------------------------------------------
2568 /*
2569 Procedure name: PROCEDURE ei_HZ_CUST_ACCOUNTS()
2570 Purpose: Writes the parent node record for HZ_CUST_ACCOUNTS in BOT
2571 Scope: internal
2572 Called From: This pkg
2573 Called By: populate_missing_links()
2574 */
2575 -----------------------------------------------------------------
2576
2577 PROCEDURE ei_HZ_CUST_ACCOUNTS IS
2578
2579 -- local variables
2580 l_debug_prefix VARCHAR2(40) := 'EI_HZ_CUST_ACCOUNTS';
2581
2582 BEGIN
2583 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2584 hz_utility_v2pub.DEBUG
2585 (p_message=>'ei_HZ_CUST_ACCOUNTS+',
2586 p_prefix=>l_debug_prefix,
2587 p_msg_level=>fnd_log.level_procedure,
2588 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2589 p_module =>'ei_act');
2590 END IF;
2591 /*
2592 HZ_CUST_ACCOUNTS has following parents
2593 1. HZ_PARTIES (ORG_CUST)
2594 2. HZ_PARTIES (PERSON_CUST)
2595
2596 Hence, HZ_CUST_ACCOUNTS can exist in 2 different scenarios.
2597 The different combinations of (parent, grand parent) are :
2598 1. (ORG_CUST, NULL)
2599 2. (PERSON_CUST, NULL)
2600
2601 The following SQL gets the Parent and Grand parent info of each HZ_CUST_ACCOUNTS
2602 record that was not already processed in BOT.
2603
2604 Test Cases for the following SQL are:
2605 Create HZ_CUST_ACCOUNTS rec in BOT with (Parent, Grand Parent)
2606 combinations existing in TCA data model
2607 1. (ORG_CUST, NULL) -- tested
2608 2. (PERSON_CUST, NULL) -- tested
2609 */
2610
2611 /* A note on the Structure of the insert statement
2612 1. The parent record is tracked by the insert statement.
2613 2. The "inner select" acts as a data source for "outer select"
2614 3. The "outer select" uses "select to exclude the parents
2615 that were already written to BOT"
2616 4. There may be duplicate rows for any given parent retured by the "inner select".
2617 This is because, in a given execution of the following SQL,
2618 there may be multiple children for a given parent.
2619
2620 Each child contributes in getting its parent. This is as per the design
2621 of "inner select".
2622
2623 To avoid duplicate rows of a parent returned by the siblings,
2624 the inner select ranks all the duplicate parents.
2625 The "outer select" filters on parents with rank = 1.
2626 This clause helps to filter out the duplicate parent rows,
2627 before data was inserted by insert statement.
2628
2629 5. The "inner select" is operating on the child record and trying to identify
2630 the parent and grand parent information.
2631 The parent information of the child record will be child
2632 (current/its) information for the parent record.
2633 The grand parent information of the child record will be parent info
2634 of the parent record.
2635 Because of this reason, "inner select" statement aliases the columns.
2636
2637 example:
2638
2639 child record:
2640 child_id: 123, child_entity_name: HZ_CUST_ACCOUNTS,
2641 child_bo: CUST_ACCT, parent_bo: ORG_CUST_ACCT, parent_entity_name: HZ_PARTIES,
2642 parent_id: 234
2643
2644 The "inner select" fetches above record from BOT and identifies
2645 its parent, grand parent information and present it as follows:
2646
2647 parent_id aliased as child_id: 234
2648 parent_entity_name aliased as child_entity_name: HZ_PARTIES
2649 parent_bo aliased as child_bo: ORG_CUST
2650 grand_parent_id aliased as parent id: null
2651 grand_parent_entity_name aliased as parent_entity_name: null
2652 grand_parent_bo aliased as parent_bo: null
2653
2654 Insert statement will take this result and write it as
2655 child record:
2656 child_id: 234, child_entity_name: HZ_PARTIES,
2657 child_bo: ORG_CUST, parent_bo: NULL, pa1rent_entity_name: NULL,
2658 parent_id: NULL
2659 */
2660
2661 INSERT INTO HZ_BUS_OBJ_TRACKING
2662 (CHILD_ENTITY_NAME, CHILD_ID,
2663 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2664 LAST_UPDATE_DATE, CHILD_BO_CODE,
2665 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
2666 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
2667 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2668 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
2669 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
2670 FROM
2671 (SELECT -- inner select
2672 PARENT_ENTITY_NAME child_entity_name
2673 ,PARENT_ID child_id
2674 ,PARENT_BO_CODE CHILD_BO_CODE
2675 ,NULL parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
2676 ,NULL parent_bo_code -- this is the grand parent bo, written as parent
2677 , NULL parent_id -- this is grand parent id of acct roles, written as parent id
2678 ,'U' child_operation_flag
2679 ,'Y' populated_flag
2680 ,t.LAST_UPDATE_DATE
2681 ,RANK()
2682 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
2683 ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
2684 FROM HZ_BUS_OBJ_TRACKING t
2685 WHERE t.CHILD_ENTITY_NAME = 'HZ_CUST_ACCOUNTS'
2686 AND t.event_id IS NULL) temp
2687 WHERE NOT EXISTS
2688 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
2689 WHERE bot.event_id IS NULL
2690 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
2691 AND bot.CHILD_ID = temp.child_id
2692 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
2693 AND acrrank = 1
2694 AND temp.child_id IS NOT NULL
2695 AND temp.CHILD_ENTITY_NAME IS NOT NULL
2696 AND temp.CHILD_BO_CODE IS NOT NULL;
2697 -- save the records populated
2698 COMMIT;
2699 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2700 hz_utility_v2pub.DEBUG
2701 (p_message=>'ei_HZ_CUST_ACCOUNTS-',
2702 p_prefix=>l_debug_prefix,
2703 p_msg_level=>fnd_log.level_procedure,
2704 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2705 p_module =>'ei_act');
2706 END IF;
2707 EXCEPTION
2708 WHEN OTHERS THEN
2709 LOG( message => 'ei_HZ_CUST_ACCOUNTS:'||SQLERRM,
2710 p_prefix =>l_debug_prefix,
2711 p_module =>'ei_act');
2712 ROLLBACK;
2713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2714 END ei_HZ_CUST_ACCOUNTS;
2715 -----------------------------------------------------------------
2716 /*
2717 Procedure name: PROCEDURE ei_HZ_CUSTOMER_PROFILES()
2718 Purpose: Writes the parent node record for HZ_CUSTOMER_PROFILES in BOT
2719 Scope: internal
2720 Called From: This pkg
2721 Called By: populate_missing_links()
2722 */
2723 -----------------------------------------------------------------
2724
2725 PROCEDURE ei_HZ_CUSTOMER_PROFILES IS
2726
2727 -- local variables
2728 l_debug_prefix VARCHAR2(40) := 'EI_HZ_CUSTOMER_PROFILES';
2729
2730 BEGIN
2731 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2732 hz_utility_v2pub.DEBUG
2733 (p_message=>'ei_HZ_CUSTOMER_PROFILES+',
2734 p_prefix=>l_debug_prefix,
2735 p_msg_level=>fnd_log.level_procedure,
2736 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2737 p_module =>'ei_custprof');
2738 END IF;
2739 /*
2740 Party Site has following parents
2741 1. HZ_CUST_ACCT_SITE_USES_ALL (CUST_ACCT_SITE_USE)
2742 2. HZ_CUST_ACCOUNTS (CUST_ACCT)
2743
2744 Hence, HZ_CUSTOMER_PROFILES can exist in 4 different scenarios.
2745 The different combinations of (parent, grand parent) are :
2746 1. (CUST_ACCT_SITE_USE, CUST_ACCT_SITE)
2747 2. (CUST_ACCT, PERSON_CUST)
2748 3. (CUST_ACCT, ORG_CUST)
2749 4. (CUST_ACCT_SITE,CUST_ACCT)
2750
2751 The following SQL gets the Parent and Grand parent info of each HZ_CUSTOMER_PROFILES
2752 record that was not already processed in BOT.
2753
2754 Test Cases for the following SQL are:
2755 Create HZ_CUSTOMER_PROFILES rec in BOT with (Parent, Grand Parent)
2756 combinations existing in TCA data model
2757 1. (CUST_ACCT_SITE_USE, CUST_ACCT_SITE) -- tested
2758 2. (CUST_ACCT, PERSON_CUST) -- tested
2759 3. (CUST_ACCT, ORG_CUST) -- tested
2760 4. (CUST_ACCT_SITE,CUST_ACCT) -- tested
2761
2762 */
2763
2764 /* A note on the Structure of the insert statement
2765 1. The parent record is tracked by the insert statement.
2766 2. The "inner select" acts as a data source for "outer select"
2767 3. The "outer select" uses "select to exclude the parents
2768 that were already written to BOT"
2769 4. There may be duplicate rows for any given parent retured by the "inner select".
2770 This is because, in a given execution of the following SQL,
2771 there may be multiple children for a given parent.
2772
2773 Each child contributes in getting its parent. This is as per the design
2774 of "inner select".
2775
2776 To avoid duplicate rows of a parent returned by the siblings,
2777 the inner select ranks all the duplicate parents.
2778 The "outer select" filters on parents with rank = 1.
2779 This clause helps to filter out the duplicate parent rows,
2780 before data was inserted by insert statement.
2781
2782 5. The "inner select" is operating on the child record and trying to identify
2783 the parent and grand parent information.
2784 The parent information of the child record will be child
2785 (current/its) information for the parent record.
2786 The grand parent information of the child record will be parent info
2787 of the parent record.
2788 Because of this reason, "inner select" statement aliases the columns.
2789
2790 example:
2791
2792 child record:
2793 child_id: 123, child_entity_name: HZ_CUSTOMER_PROFILES,
2794 child_bo: CUST_PROFILE, parent_bo: CUST_ACCT_SITE_USE, parent_entity_name: HZ_CUST_ACCT_SITE_USES_ALL,
2795 parent_id: 234
2796
2797 The "inner select" fetches above record from BOT and identifies
2798 its parent, grand parent information and present it as follows:
2799
2800 parent_id aliased as child_id: 234
2801 parent_entity_name aliased as child_entity_name: HZ_CUST_ACCT_SITE_USES_ALL
2802 parent_bo aliased as child_bo: CUST_ACCT_SITE_USE
2803 grand_parent_id aliased as parent id: 456
2804 grand_parent_entity_name aliased as parent_entity_name: HZ_CUST_ACCT_SITES_ALL
2805 grand_parent_bo aliased as parent_bo: CUST_ACCT_SITE
2806
2807 Insert statement will take this result and write it as
2808 child record:
2809 child_id: 234, child_entity_name: HZ_CUST_ACCT_SITE_USES_ALL,
2810 child_bo: CUST_ACCT_SITE_USE, parent_bo: CUST_ACCT_SITE, parent_entity_name: HZ_CUST_ACCT_SITES_ALL,
2811 parent_id: 456
2812 6. It is non-trivial to figure out the business object codes for both parent
2813 and grand parent, grand parent identifier or grand parent entity name.
2814 To do this, "inner select" uses case statement on parent_entity_name.
2815 Some times, an embedded SQL is necessary to fgure out this.
2816 Example:
2817 Child is HZ_CUSTOMER_PROFILES.
2818 Parent is CUST_ACCT_SITE_USE and it's parent is CUST_ACCT_SITE.
2819 */
2820
2821 INSERT INTO HZ_BUS_OBJ_TRACKING
2822 (CHILD_ENTITY_NAME, CHILD_ID,
2823 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2824 LAST_UPDATE_DATE, CHILD_BO_CODE,
2825 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
2826 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
2827 CHILD_OPERATION_FLAG, POPULATED_FLAG,
2828 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
2829 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
2830 FROM
2831 (SELECT -- inner select
2832 PARENT_ENTITY_NAME child_entity_name
2833 ,PARENT_ID child_id
2834 ,PARENT_BO_CODE CHILD_BO_CODE
2835 ,'U' child_operation_flag
2836 ,'Y' populated_flag
2837 ,LAST_UPDATE_DATE
2838 ,RANK()
2839 OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
2840 ORDER BY LAST_UPDATE_DATE, child_id) as acrrank
2841 ,CASE PARENT_BO_CODE
2842 WHEN 'CUST_ACCT_SITE_USE' THEN 'HZ_CUST_ACCT_SITES_ALL'
2843 WHEN 'CUST_ACCT' THEN 'HZ_PARTIES'
2844 ELSE NULL
2845 END parent_entity_name -- this is grand parent tbl name of acct roles, written as parent entity name
2846 ,CASE PARENT_BO_CODE
2847 WHEN 'CUST_ACCT_SITE_USE' THEN
2848 (SELECT CUST_ACCT_SITE_ID
2849 FROM HZ_CUST_SITE_USES_ALL
2850 WHERE SITE_USE_ID = PARENT_ID)
2851 WHEN 'CUST_ACCT' THEN
2852 (SELECT PARTY_ID
2853 FROM HZ_CUST_ACCOUNTS
2854 WHERE CUST_ACCOUNT_ID = PARENT_ID)
2855 ELSE NULL
2856 END parent_id -- this is grand parent id of acct roles, written as parent id
2857 ,CASE PARENT_BO_CODE
2858 WHEN 'CUST_ACCT_SITE_USE' THEN 'CUST_ACCT_SITE'
2859 WHEN 'CUST_ACCT' THEN
2860 (SELECT
2861 CASE p.party_type
2862 WHEN 'ORGANIZATION' THEN 'ORG_CUST'
2863 WHEN 'PERSON' THEN 'PERSON_CUST'
2864 ELSE NULL
2865 END
2866 FROM hz_parties p, HZ_CUST_ACCOUNTS c
2867 WHERE p.party_id = c.party_id
2868 AND c.CUST_ACCOUNT_ID = PARENT_ID)
2869 ELSE NULL
2870 END parent_bo_code -- this is the grand parent bo, written as parent
2871 FROM HZ_BUS_OBJ_TRACKING
2872 WHERE CHILD_ENTITY_NAME = 'HZ_CUSTOMER_PROFILES'
2873 AND event_id IS NULL) temp
2874 WHERE NOT EXISTS
2875 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
2876 WHERE bot.event_id IS NULL
2877 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
2878 AND bot.CHILD_ID = temp.child_id
2879 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
2880 AND acrrank = 1
2881 AND temp.child_id IS NOT NULL
2882 AND temp.CHILD_ENTITY_NAME IS NOT NULL
2883 AND temp.CHILD_BO_CODE IS NOT NULL ;
2884 -- save the records populated
2885 COMMIT;
2886 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2887 hz_utility_v2pub.DEBUG
2888 (p_message=>'ei_HZ_CUSTOMER_PROFILES-',
2889 p_prefix=>l_debug_prefix,
2890 p_msg_level=>fnd_log.level_procedure,
2891 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2892 p_module =>'ei_custprof');
2893 END IF;
2894 EXCEPTION
2895 WHEN OTHERS THEN
2896 LOG( message => 'ei_HZ_CUSTOMER_PROFILES:'||SQLERRM,
2897 p_prefix =>l_debug_prefix,
2898 p_module => 'ei_custprof');
2899 ROLLBACK;
2900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2901 END ei_HZ_CUSTOMER_PROFILES;
2902 -----------------------------------------------------------------
2903 /*
2904 Procedure name: PROCEDURE ei_HZ_CONTACT_POINTS()
2905 Purpose: Writes the parent node record for HZ_CONTACT_POINTS in BOT
2906 Scope: internal
2907 Called From: This pkg
2908 Called By: populate_missing_links()
2909 */
2910 -----------------------------------------------------------------
2911
2912 PROCEDURE ei_HZ_CONTACT_POINTS IS
2913
2914 -- local variables
2915 l_debug_prefix VARCHAR2(40) := 'EI_HZ_CONTACT_POINTS';
2916 l_module VARCHAR2(30) := 'ei_cp';
2917 BEGIN
2918 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2919 hz_utility_v2pub.DEBUG
2920 (p_message=>'ei_HZ_CONTACT_POINTS+',
2921 p_prefix=>l_debug_prefix,
2922 p_msg_level=>fnd_log.level_procedure,
2923 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
2924 p_module =>'ei_cp');
2925 END IF;
2926
2927 /*
2928 Following entities are parents of HZ_CONTACT_POINTS
2929 1. HZ_PARTIES (Org, Person, OrgContact)
2930 2. HZ_PARTY_SITES
2931
2932 Party Site has following parents
2933 1. HZ_PARTIES (Org, Person, OrgContact)
2934
2935 Hence, contact point can exist in six different scenarios.
2936 The different combinations of (parent, grand parent) are :
2937 1. (Org, null) 2. (Person, null) 3. (OrgContact, Org) 4. (PS, Org) 5. (PS, Person)
2938 6. (PS, OrgContact)
2939
2940 The following SQL gets the Parent and Grand parent info of each HZ_CONTACT_POINTS
2941 record that was not already processed in BOT.
2942
2943 Test Cases for the following SQL are:
2944 Create HZ_CONTACT_POINTS rec in BOT with (Parent, Grand Parent)
2945 combinations existing in TCA data model
2946 1. (Org, null) -- tested
2947 2. (Person, null) -- tested
2948 3. (OrgContact, Org) -- tested
2949 4. (PS, Org) -- tested
2950 5. (PS, Person) -- tested
2951 6. (PS, OrgContact) -- tested
2952
2953 A note on the Structure of the insert statement
2954 1. The parent record is tracked by the insert statement.
2955 2. The "inner select" acts as a data source for "outer select"
2956 3. The "outer select" uses "select to exclude the parents
2957 that were already written to BOT"
2958 4. There may be duplicate rows for any given parent retured by the "inner select".
2959 This is because, in a given execution of the following SQL,
2960 there may be multiple children for a given parent.
2961
2962 Each child contributes in getting its parent. This is as per the design
2963 of "inner select".
2964
2965 To avoid duplicate rows of a parent returned by the siblings,
2966 the inner select ranks all the parents duplicate parents.
2967 The "outer select" filters on parents with rank = 1.
2968 This clause helps to filter out the duplicate parent rows,
2969 before data was inserted by insert statement.
2970
2971 5. The "inner select" is operating on the child record and trying to identify
2972 the parent and grand parent information.
2973 The parent information of the child record will be child
2974 (current/its) information for the parent record.
2975 The grand parent information of the child record will be parent info
2976 of the parent record.
2977 Because of this reason, "inner select" statement aliases the columns.
2978
2979 example:
2980
2981 child record:
2982 child_id: 123, child_entity_name: HZ_CONTACT_POINTS,
2983 child_bo: Phone, parent_bo: PS, parent_entity_name: PS,
2984 parent_id: 234
2985
2986 The "inner select" fetches above record from BOT and identifies
2987 its parent, grand parent information and present it as follows:
2988
2989 parent_id aliased as child_id: 234
2990 parent_entity_name aliased as child_entity_name: HZ_PARTY_SITES
2991 parent_bo aliased as child_bo: PS
2992 grand_parent_id aliased as parent id: 456
2993 grand_parent_entity_name aliased as parent_entity_name: HZ_PARTIES
2994 grand_parent_bo aliased as parent_bo: Org
2995
2996 Insert statement will take this result and write it as
2997 child record:
2998 child_id: 234, child_entity_name: HZ_PARTY_SITES,
2999 child_bo: PS, parent_bo: Org, parent_entity_name: HZ_PARTIES,
3000 parent_id: 456
3001 6. It is non-trivial to figure out the business object codes for both parent
3002 and grand parent, grand parent identifier or grand parent entity name.
3003 To do this, "inner select" uses case statement on parent_entity_name.
3004 Some times, an embedded SQL is necessary to fgure out this.
3005 Example:
3006 Child is HZ_CONTACT_POINTS.
3007 Parent is PS and it's parent is Party.
3008 To figure out the grand parent bo code, SQL is necessary to run against
3009 HZ_PARTIES to figure out the PARTY_TYPE based on PARTY_ID of the
3010 HZ_PARTY_SITES table.
3011 */
3012
3013 INSERT INTO HZ_BUS_OBJ_TRACKING
3014 (CHILD_ENTITY_NAME, CHILD_ID,
3015 CHILD_OPERATION_FLAG, POPULATED_FLAG,
3016 LAST_UPDATE_DATE, CHILD_BO_CODE,
3017 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
3018 SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
3019 CHILD_OPERATION_FLAG, POPULATED_FLAG,
3020 LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
3021 PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
3022 FROM
3023 (SELECT -- inner select
3024 PARENT_ENTITY_NAME child_entity_name
3025 ,PARENT_ID child_id
3026 ,PARENT_BO_CODE CHILD_BO_CODE
3027 ,'U' child_operation_flag
3028 ,'Y' populated_flag
3029 ,LAST_UPDATE_DATE
3030 ,RANK()
3031 OVER (PARTITION BY PARENT_ENTITY_NAME, PARENT_ID
3032 ORDER BY LAST_UPDATE_DATE, child_id) as cprank
3033 ,CASE PARENT_BO_CODE
3034 WHEN 'ORG' THEN NULL -- identify the GrandParentEntityName when Org is parentBO of CP
3035 WHEN 'PERSON' THEN NULL -- identify the GrandParentEntityName when Per is parentBO of CP
3036 WHEN 'ORG_CONTACT' THEN -- identify the GrandParentEntityName when orgContact is parentBO of CP
3037 'HZ_PARTIES'
3038 WHEN 'PARTY_SITE' THEN -- identify the GrandParentEntityName when PS is parentBO of CP
3039 (SELECT
3040 CASE party_type
3041 WHEN 'ORGANIZATION' THEN 'HZ_PARTIES' -- identify the GrandParentEntityName when PS is parentBO of CP, when Org is parentOf PS
3042 WHEN 'PERSON' THEN 'HZ_PARTIES' -- identify the GrandParentEntityName when PS is parentBO of CP, when Per is parentOf PS
3043 WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS' -- identify the GrandParentEntityName when PS is parentBO of CP, when orgContact is parentOf PS
3044 ELSE NULL
3045 END
3046 FROM HZ_PARTIES
3047 WHERE party_id = (SELECT ps.party_id
3048 FROM HZ_PARTY_SITES ps
3049 WHERE ps.party_site_id = PARENT_ID))
3050 ELSE NULL
3051 END parent_entity_name -- this is grand parent tbl name of CP - written as parent entity name
3052 ,CASE PARENT_BO_CODE -- to identify the grandParentEntityId of CP
3053 WHEN 'ORG' THEN NULL -- to identify the grandParentEntityId of CP when Org is parentOf CP
3054 WHEN 'PERSON' THEN NULL -- to identify the grandParentEntityId of CP when Per is parentOf CP
3055 WHEN 'ORG_CONTACT' THEN -- to identify the grandParentEntityId of CP when Rel is parentOf CP
3056 (SELECT r.object_id
3057 FROM hz_relationships r, hz_org_contacts oc
3058 WHERE oc.org_contact_id = PARENT_ID
3059 AND oc.party_relationship_id = r.relationship_id
3060 AND subject_type ='PERSON'
3061 AND object_type = 'ORGANIZATION')
3062 WHEN 'PARTY_SITE' THEN -- to identify the grandParentEntityId of CP when PS is parentOf CP
3063 (SELECT
3064 CASE p.party_type
3065 WHEN 'ORGANIZATION' THEN p.party_id -- to identify the grandParentEntityId of CP when PS is parentOf CP, when Org is parentOF PS
3066 WHEN 'PERSON' THEN p.party_id -- to identify the grandParentEntityId of CP when PS is parentOf CP, when Per is parentOF PS
3067 WHEN 'PARTY_RELATIONSHIP' THEN -- to identify the grandParentEntityId of CP when PS is parentOf CP, when Rel is parentOF PS
3068 (SELECT oc.org_contact_id
3069 FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
3070 WHERE r.relationship_id = oc.party_relationship_id
3071 AND r.party_id = p.party_id
3072 AND r.subject_type = 'PERSON'
3073 AND r.object_type = 'ORGANIZATION')
3074 ELSE NULL
3075 END
3076 FROM hz_parties p
3077 WHERE p.party_id = (select ps.party_id
3078 from HZ_PARTY_SITES ps
3079 where ps.party_site_id = PARENT_ID))
3080 ELSE NULL
3081 END parent_id -- this is the grand parent id of cont pref - written as parent
3082 ,CASE PARENT_BO_CODE -- to identify the grandParentBO of CP
3083 WHEN 'ORG' THEN NULL -- to identify the grandParentBO of CP when Org is Parent
3084 WHEN 'PERSON' THEN NULL -- to identify the grandParentBO of CP when Per is Parent
3085 WHEN 'ORG_CONTACT' THEN 'ORG' -- to identify the grandParentBO of CP when OrgConatct is Parent
3086 WHEN 'PARTY_SITE' THEN -- to identify the grandParentBO of CP when PS is Parent
3087 (SELECT
3088 CASE party_type
3089 WHEN 'ORGANIZATION' THEN 'ORG'
3090 WHEN 'PERSON' THEN 'PERSON'
3091 WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
3092 ELSE NULL
3093 END
3094 FROM hz_parties
3095 WHERE party_id = (SELECT party_id
3096 FROM HZ_PARTY_SITES
3097 WHERE party_site_id = PARENT_ID))
3098 ELSE NULL
3099 END parent_bo_code -- this is the grand parent bo, written as parent
3100 FROM HZ_BUS_OBJ_TRACKING
3101 WHERE CHILD_ENTITY_NAME = 'HZ_CONTACT_POINTS'
3102 AND event_id IS NULL) temp
3103 WHERE NOT EXISTS
3104 (SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
3105 WHERE bot.event_id IS NULL
3106 AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
3107 AND bot.CHILD_ID = temp.child_id
3108 AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
3109 AND cprank = 1
3110 AND temp.child_id IS NOT NULL
3111 AND temp.CHILD_ENTITY_NAME IS NOT NULL
3112 AND temp.CHILD_BO_CODE IS NOT NULL;
3113 -- save the records populated
3114 COMMIT;
3115 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3116 hz_utility_v2pub.DEBUG
3117 (p_message=>'ei_HZ_CONTACT_POINTS-',
3118 p_prefix=>l_debug_prefix,
3119 p_msg_level=>fnd_log.level_procedure,
3120 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3121 p_module =>'ei_cp');
3122 END IF;
3123
3124 EXCEPTION
3125 WHEN OTHERS THEN
3126 LOG( message => 'ei_HZ_CONTACT_POINTS:'||SQLERRM,
3127 p_prefix =>l_debug_prefix,
3128 p_module => l_module);
3129 ROLLBACK;
3130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3131 END ei_HZ_CONTACT_POINTS;
3132 ------------------------------------------------------------------------------
3133 PROCEDURE set_prof_var IS
3134 BEGIN
3135 IF G_BO_PROF_VAL IS NULL THEN
3136 G_BO_PROF_VAL := FND_PROFILE.value('HZ_EXECUTE_API_CALLOUTS');
3137 END IF;
3138 EXCEPTION
3139 WHEN others THEN
3140 hz_utility_v2pub.debug(p_message=>sqlerrm,
3141 p_prefix=>g_debug_prefix,
3142 p_msg_level=>fnd_log.level_procedure);
3143 END set_prof_var; -- set_prof_var
3144
3145
3146 FUNCTION get_prof_val RETURN VARCHAR2 IS
3147 BEGIN
3148 RETURN FND_PROFILE.value( 'HZ_EXECUTE_API_CALLOUTS');
3149 EXCEPTION
3150 WHEN others THEN
3151 hz_utility_v2pub.debug(p_message=>sqlerrm,
3152 p_prefix=>g_debug_prefix,
3153 p_msg_level=>fnd_log.level_procedure);
3154 END get_prof_val; -- get_prof_val
3155 ----------------------------------------------------------------------
3156 /*
3157 Procedure name: entity_in_bo
3158 Scope: external
3159 Purpose: Given a Top level BO (Org, Person, Org Cust, Person Cust),
3160 an EBO and child entity name, this function will return TRUE if teh EBO
3161 and entity combination is mandatory child of the BO.
3162 Called From: BOD package
3163 Called By: This is called from BOD Update API.
3164 Note: Based on the response from this function, BOD Update API will
3165 decide whether to update the BO version of the top level BO.
3166 */
3167 ----------------------------------------------------------------------
3168 procedure entity_in_bo (p_bo_code IN VARCHAR2,
3169 p_ebo_code IN VARCHAR2,
3170 p_child_bo_code IN VARCHAR2,
3171 p_entity_name IN VARCHAR2,
3172 x_return_status OUT NOCOPY BOOLEAN) IS
3173
3174 CURSOR c_chk_entity (c_bo_code IN VARCHAR2,
3175 c_entity_name IN VARCHAR2,
3176 c_node_path IN VARCHAR2) IS
3177
3178 SELECT 1
3179 FROM (
3180 SELECT BUSINESS_OBJECT_CODE bo_code, CHILD_BO_CODE,ENTITY_NAME,
3181 sys_connect_by_path(BUSINESS_OBJECT_CODE, '/') node_path,
3182 CONNECT_BY_ISLEAF isleaf
3183 FROM hz_bus_obj_definitions
3184 START WITH BUSINESS_OBJECT_CODE = c_bo_code
3185 CONNECT BY PRIOR CHILD_BO_CODE = BUSINESS_OBJECT_CODE
3186 )
3187 WHERE
3188 isleaf = 1
3189 AND entity_name = c_entity_name
3190 AND node_path LIKE c_node_path
3191 AND ROWNUM <2;
3192
3193 -- local variables
3194 l_num NUMBER;
3195 l_node VARCHAR2(500);
3196 l_debug_prefix VARCHAR2(30) := 'idntfyEntyInBO:';
3197 l_module VARCHAR2(30) := 'entity_in_bo';
3198 l_cbo_null_flag BOOLEAN := FALSE;
3199 l_samebo_flag BOOLEAN := FALSE;
3200
3201 BEGIN
3202 -- check to see all the following parameters are passed or not.
3203 -- p_bo_code - this is high level BO object PERSON, PERSON_CUST, ORG, ORG_CUST
3204 -- p_ebo_code -- this is the EBO code.
3205 -- p_child_bo_code -- this is the EBO code. This is not mandatory
3206 -- p_entity_name -- this is the entity name
3207 /*
3208 logic:
3209 Idea here is to check if the entire object hierarchy exists in a given high
3210 level Business Object (PERSON, PERSON_CUST, ORG, ORG_CUST).
3211 This can only be checked if all the available nodes in the hierarchy are given.
3212
3213 Example - A
3214 Object hieararchy given is, Org_Contact->Party_Site->Location->HZ_LOCATIONS_EXT_VL
3215 If the entire hierarchy must be validated for high level Business Object BO_CODE passed.
3216 If the BO_CODE is Person, this function must return FALSE.
3217 Example - B
3218 Object hieararchy given is, Person->HZ_EDUCATION
3219
3220 -- flow
3221 1. check if all the mandatory parameters are passed
3222 2. check to see if the EBO and CHILD_BO combination exists in the BO_CODE.
3223 2.1 if the combination exists then
3224 check if CHILD_BO, ENTITY_NAME combo exists in BO_CODE
3225 IF TRUE - RETURN TRUE. IF FALSE, RETURN FALSE.
3226 2.2 if the combination does not exist, return FALSE
3227 3. if the CHILD_BO is null, check if EBO, ENTITY_NAME combo exists in BO_CODE
3228 3.1 if the combination exists then, RETURN TRUE.
3229 3.2 if the combination does not exist, return FALSE
3230 */
3231
3232 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3233 hz_utility_v2pub.DEBUG
3234 (p_message=>'entity_in_bo()+',
3235 p_prefix=>l_debug_prefix,
3236 p_msg_level=>fnd_log.level_procedure,
3237 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3238 p_module =>l_module);
3239 hz_utility_v2pub.DEBUG
3240 (p_message=>'bfr validating parameters',
3241 p_prefix=>l_debug_prefix,
3242 p_msg_level=>fnd_log.level_procedure,
3243 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3244 p_module =>l_module);
3245 END IF;
3246 -- P_BO_CODE, p_ebo_code, p_entity_name must be not null
3247 IF (p_bo_code IS NULL) THEN
3248 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3249 hz_utility_v2pub.DEBUG
3250 (p_message=>'p_bo_code mandatory param is null',
3251 p_prefix=>l_debug_prefix,
3252 p_msg_level=>fnd_log.level_procedure,
3253 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3254 p_module =>l_module);
3255 hz_utility_v2pub.DEBUG
3256 (p_message=>'entity_in_bo()-',
3257 p_prefix=>l_debug_prefix,
3258 p_msg_level=>fnd_log.level_procedure,
3259 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3260 p_module =>l_module);
3261 END IF;
3262 RAISE FND_API.G_EXC_ERROR;
3263 END IF;
3264 IF (p_ebo_code IS NULL) THEN
3265 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3266 hz_utility_v2pub.DEBUG
3267 (p_message=>'p_ebo_code mandatory param is null',
3268 p_prefix=>l_debug_prefix,
3269 p_msg_level=>fnd_log.level_procedure,
3270 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3271 p_module =>l_module);
3272 hz_utility_v2pub.DEBUG
3273 (p_message=>'entity_in_bo()-',
3274 p_prefix=>l_debug_prefix,
3275 p_msg_level=>fnd_log.level_procedure,
3276 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3277 p_module =>l_module);
3278 END IF;
3279 RAISE FND_API.G_EXC_ERROR;
3280 END IF;
3281 IF (p_entity_name IS NULL) THEN
3282 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3283 hz_utility_v2pub.DEBUG
3284 (p_message=>'p_entity_name mandatory param is null',
3285 p_prefix=>l_debug_prefix,
3286 p_msg_level=>fnd_log.level_procedure,
3287 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3288 p_module =>l_module);
3289 hz_utility_v2pub.DEBUG
3290 (p_message=>'entity_in_bo()-',
3291 p_prefix=>l_debug_prefix,
3292 p_msg_level=>fnd_log.level_procedure,
3293 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3294 p_module =>l_module);
3295 END IF;
3296 RAISE FND_API.G_EXC_ERROR;
3297 END IF;
3298 /*
3299 -- construct the node path
3300 Entity_name is not part of the node path.
3301 If high level BO code and p_ebo_code are same - mention any one of them only.
3302 Handle the case where p_child_bo_code is null.
3303 */
3304
3305 IF p_bo_code = p_ebo_code THEN
3306 l_samebo_flag := TRUE;
3307 END IF;
3308 IF p_child_bo_code IS NULL THEN
3309 l_cbo_null_flag := TRUE;
3310 END IF;
3311
3312 -- WHEN HIGH LEVEL bo AND EBO_CODE ARE SAME AND CHILD_BO_CODE IS NULL
3313 IF l_samebo_flag AND l_cbo_null_flag THEN
3314 l_node := '/'||p_bo_code;
3315 END IF;
3316 -- WHEN HIGH LEVEL BO AND EBO_CODE ARE different AND CHILD_BO_CODE IS NULL
3317 IF ((NOT(l_samebo_flag)) AND l_cbo_null_flag) THEN
3318 l_node := '/'||p_bo_code||'%/'||p_ebo_code;
3319 END IF;
3320 -- WHEN HIGH LEVEL bo AND EBO_CODE ARE SAME AND CHILD_BO_CODE IS NOT NULL
3321 IF (l_samebo_flag AND (NOT(l_cbo_null_flag))) THEN
3322 l_node := '/'||p_bo_code||'%/'||p_child_bo_code;
3323 END IF;
3324 -- WHEN HIGH LEVEL BO AND EBO_CODE ARE different AND CHILD_BO_CODE IS NOT NULL
3325 IF ((NOT(l_samebo_flag)) AND (NOT(l_cbo_null_flag))) THEN
3326 l_node := '/'||p_bo_code||'%/'||p_ebo_code||'/'||p_child_bo_code;
3327 END IF;
3328 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3329 hz_utility_v2pub.DEBUG
3330 (p_message=>'node path is:'||l_node,
3331 p_prefix=>l_debug_prefix,
3332 p_msg_level=>fnd_log.level_procedure,
3333 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3334 p_module =>l_module);
3335 END IF;
3336 -- OPEN c_chk_entity (p_bo_code, p_ebo_code, p_child_bo_code, p_entity_name);
3337 OPEN c_chk_entity (p_bo_code, p_entity_name, l_node);
3338 FETCH c_chk_entity INTO l_num;
3339 CLOSE c_chk_entity;
3340 IF l_num = 1 THEN
3341 x_return_status := TRUE;
3342 -- RETURN TRUE;
3343 ELSE
3344 x_return_status := FALSE;
3345 -- RETURN FALSE;
3346 END IF;
3347 EXCEPTION
3348 WHEN OTHERS THEN
3349 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3350 hz_utility_v2pub.DEBUG
3351 (p_message=>sqlerrm,
3352 p_prefix=>l_debug_prefix,
3353 p_msg_level=>fnd_log.level_procedure,
3354 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3355 p_module =>l_module);
3356 hz_utility_v2pub.DEBUG
3357 (p_message=>'entity_in_bo()-',
3358 p_prefix=>l_debug_prefix,
3359 p_msg_level=>fnd_log.level_procedure,
3360 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3361 p_module =>l_module);
3362 END IF;
3363 END entity_in_bo;
3364 -----------------------------------------------------------------
3365 /*
3366 Procedure name: populate_missing_links ()
3367 Scope: external
3368 Purpose: This is an umbrella procedure to call all the
3369 explode entity procedures that populate the parent record
3370 and update the current record with BO codes etc.
3371 This must be done in order to reach the root node of Org, Person,
3372 Org Cust and Person Cust BOs.
3373 Called From: This is called from HZ_BES_BO_RAISE_PKG
3374 Called By: bes_main()
3375 */
3376 -----------------------------------------------------------------
3377 PROCEDURE populate_missing_links
3378 ( p_creation_date IN DATE )IS
3379 l_debug_prefix VARCHAR2(30) := 'EXP_HZ_TBLS:'; -- explode hz tables
3380 l_module VARCHAR2(30) := 'pop_missing_lnks';
3381 BEGIN
3382 LOG( message => 'populate_missing_links()+',
3383 p_prefix =>l_debug_prefix,
3384 p_module => l_module);
3385 -- set the global variable with the p_creation_date. This is to
3386 -- avoid passing the parameter for each insert procedure.
3387 -- Each of the insert procedures, need to populate the BOT creation_date
3388 -- with the value the p_creation_date value.
3389 G_CREATION_DATE := p_creation_date;
3390 /* A note on insert and update procedures.
3391
3392 Insert Procedures:
3393 The functional reason for these insert procedures to exist is to identify
3394 and track the parent of each child entity. The parent record is
3395 needed in order for the following functions to work properly.
3396 1. Business Object Completeness Check
3397 2. To identify Business Object Event Type - whether to raise create or update event
3398 3. To extract the Business Object
3399
3400 In all the three functionalities above will only have knowledge of
3401 Business Object root node. To get to the leaf node, all the intermediate
3402 nodes must be present in the BOT table. Hence this insert procedures.
3403
3404 Update Procedures:
3405 In order to link the parent record and child record, it is mandatory for
3406 the child record to contain the information for parent in addition to
3407 having its (childs) information in it.
3408
3409 Only when both parent record (insert procedure) and child record contains
3410 parent info (update procedure), it is possible to sucessfuly do
3411 the three functions mentioned above.
3412
3413 */
3414 /*
3415 Note 1.
3416 ONLY following entities are exploded as part of the concurrent program.
3417 Remaing entities are handled by the populate functions.
3418 Note 2.
3419 As populate function write the PARENT_BO_CODE as part of populating the
3420 child record, there is no need of calling any update procedures
3421 as described earlier. Only insert procedures are called.
3422
3423 1. HZ_CONTACT_PREFERENCES
3424 2. HZ_PER_PROFILES_EXT_VL
3425 3. HZ_PERSON_PROFILES
3426 4. HZ_PARTY_SITE_USES
3427 5. HZ_CONTACT_POINTS
3428 6. HZ_PARTY_SITES_EXT_VL
3429 7. HZ_LOCATIONS_EXT_VL
3430 8. HZ_LOCATIONS
3431 9. HZ_PARTY_SITES
3432 10. HZ_ORG_CONTACTS
3433 11. HZ_ORG_PROFILES_EXT_VL
3434 12. HZ_CUST_ACCOUNT_ROLES
3435 13. HZ_CUSTOMER_PROFILES
3436 14. HZ_CUST_SITE_USES_ALL
3437 15. HZ_CUST_ACCT_SITES_ALL
3438 16. HZ_CUST_ACCOUNTS
3439 */
3440 -- Insert the parent record
3441 -- 1. HZ_CONTACT_PREFERENCES
3442 /* LOG(
3443 message => 'bfr calling ei_HZ_CONTACT_PREFERENCES()',
3444 p_prefix =>l_debug_prefix,
3445 p_module => l_module);
3446 */
3447 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3448 hz_utility_v2pub.DEBUG
3449 (p_message=>'bfr calling ei_HZ_CONTACT_PREFERENCES()',
3450 p_prefix=>l_debug_prefix,
3451 p_msg_level=>fnd_log.level_procedure,
3452 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3453 p_module =>'pop_missing_lnks');
3454 END IF;
3455
3456 ei_HZ_CONTACT_PREFERENCES;
3457 -- 2. HZ_PER_PROFILES_EXT_VL
3458 /* LOG(
3459 message => 'bfr calling ei_HZ_PER_PROFILES_EXT_VL()',
3460 p_prefix =>l_debug_prefix,
3461 p_module => l_module);
3462 */
3463 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3464 hz_utility_v2pub.DEBUG
3465 (p_message=>'bfr calling ei_HZ_PER_PROFILES_EXT_VL()',
3466 p_prefix=>l_debug_prefix,
3467 p_msg_level=>fnd_log.level_procedure,
3468 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3469 p_module =>'pop_missing_lnks');
3470 END IF;
3471
3472 ei_HZ_PER_PROFILES_EXT_VL;
3473 -- 3. HZ_PERSON_PROFILES
3474 /* LOG(
3475 message => 'bfr calling ei_HZ_PERSON_PROFILES()',
3476 p_prefix =>l_debug_prefix,
3477 p_module => l_module);
3478 */
3479 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3480 hz_utility_v2pub.DEBUG
3481 (p_message=>'bfr calling ei_HZ_PERSON_PROFILES()',
3482 p_prefix=>l_debug_prefix,
3483 p_msg_level=>fnd_log.level_procedure,
3484 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3485 p_module =>'pop_missing_lnks');
3486 END IF;
3487
3488 ei_HZ_PERSON_PROFILES;
3489 -- 4. HZ_PARTY_SITE_USES
3490 /* LOG(
3491 message => 'bfr calling ei_HZ_PARTY_SITE_USES()',
3492 p_prefix =>l_debug_prefix,
3493 p_module => l_module);
3494 */
3495 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3496 hz_utility_v2pub.DEBUG
3497 (p_message=>'bfr calling ei_HZ_PARTY_SITE_USES()',
3498 p_prefix=>l_debug_prefix,
3499 p_msg_level=>fnd_log.level_procedure,
3500 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3501 p_module =>'pop_missing_lnks');
3502 END IF;
3503
3504 ei_HZ_PARTY_SITE_USES;
3505 -- 5. HZ_CONTACT_POINTS
3506 /* LOG(
3507 message => 'bfr calling ei_HZ_CONTACT_POINTS()',
3508 p_prefix =>l_debug_prefix,
3509 p_module => l_module);
3510 */
3511 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3512 hz_utility_v2pub.DEBUG
3513 (p_message=>'bfr calling ei_HZ_CONTACT_POINTS()',
3514 p_prefix=>l_debug_prefix,
3515 p_msg_level=>fnd_log.level_procedure,
3516 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3517 p_module =>'pop_missing_lnks');
3518 END IF;
3519
3520 ei_HZ_CONTACT_POINTS;
3521 -- 6. HZ_PARTY_SITES_EXT_VL
3522 /* LOG(
3523 message => 'bfr calling ei_HZ_PARTY_SITES_EXT_VL()',
3524 p_prefix =>l_debug_prefix,
3525 p_module => l_module);
3526 */
3527 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3528 hz_utility_v2pub.DEBUG
3529 (p_message=>'bfr calling ei_HZ_PARTY_SITES_EXT_VL()',
3530 p_prefix=>l_debug_prefix,
3531 p_msg_level=>fnd_log.level_procedure,
3532 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3533 p_module =>'pop_missing_lnks');
3534 END IF;
3535
3536 ei_HZ_PARTY_SITES_EXT_VL;
3537 -- 7. HZ_LOCATIONS_EXT_VL
3538 /* LOG(
3539 message => 'bfr calling ei_HZ_LOCATIONS_EXT()',
3540 p_prefix =>l_debug_prefix,
3541 p_module => l_module);
3542 */
3543 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3544 hz_utility_v2pub.DEBUG
3545 (p_message=>'bfr calling ei_HZ_LOCATIONS_EXT()',
3546 p_prefix=>l_debug_prefix,
3547 p_msg_level=>fnd_log.level_procedure,
3548 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3549 p_module =>'pop_missing_lnks');
3550 END IF;
3551
3552 ei_HZ_LOCATIONS_EXT;
3553 -- 8. HZ_LOCATIONS
3554 /* LOG(
3555 message => 'bfr calling ei_HZ_LOCATIONS()',
3556 p_prefix =>l_debug_prefix,
3557 p_module => l_module);
3558 */
3559 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3560 hz_utility_v2pub.DEBUG
3561 (p_message=>'bfr calling ei_HZ_LOCATIONS()',
3562 p_prefix=>l_debug_prefix,
3563 p_msg_level=>fnd_log.level_procedure,
3564 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3565 p_module =>'pop_missing_lnks');
3566 END IF;
3567
3568 ei_HZ_LOCATIONS;
3569 -- 9. HZ_PARTY_SITES
3570 /* LOG(
3571 message => 'bfr calling ei_HZ_PARTY_SITES()',
3572 p_prefix =>l_debug_prefix,
3573 p_module => l_module);
3574 */
3575 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3576 hz_utility_v2pub.DEBUG
3577 (p_message=>'bfr calling ei_HZ_PARTY_SITES()',
3578 p_prefix=>l_debug_prefix,
3579 p_msg_level=>fnd_log.level_procedure,
3580 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3581 p_module =>'pop_missing_lnks');
3582 END IF;
3583
3584 ei_HZ_PARTY_SITES;
3585 -- 10. HZ_ORG_CONTACTS
3586 /* LOG(
3587 message => 'bfr calling ei_HZ_ORG_CONTACTS()',
3588 p_prefix =>l_debug_prefix,
3589 p_module => l_module);
3590 */
3591 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3592 hz_utility_v2pub.DEBUG
3593 (p_message=>'bfr calling ei_HZ_ORG_CONTACTS()',
3594 p_prefix=>l_debug_prefix,
3595 p_msg_level=>fnd_log.level_procedure,
3596 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3597 p_module =>'pop_missing_lnks');
3598 END IF;
3599
3600 ei_HZ_ORG_CONTACTS;
3601 -- 11. HZ_ORG_PROFILES_EXT_VL
3602 /* LOG(
3603 message => 'bfr calling ei_HZ_ORG_PROFILES_EXT_VL()',
3604 p_prefix =>l_debug_prefix,
3605 p_module => l_module);
3606 */
3607 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3608 hz_utility_v2pub.DEBUG
3609 (p_message=>'bfr calling ei_HZ_ORG_PROFILES_EXT_VL()',
3610 p_prefix=>l_debug_prefix,
3611 p_msg_level=>fnd_log.level_procedure,
3612 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3613 p_module =>'pop_missing_lnks');
3614 END IF;
3615
3616 ei_HZ_ORG_PROFILES_EXT_VL;
3617 -- 12. HZ_CUST_ACCOUNT_ROLES
3618 /* LOG(
3619 message => 'bfr calling ei_HZ_CUST_ACCOUNT_ROLES()',
3620 p_prefix =>l_debug_prefix,
3621 p_module => l_module);
3622 */
3623 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3624 hz_utility_v2pub.DEBUG
3625 (p_message=>'bfr calling ei_HZ_CUST_ACCOUNT_ROLES()',
3626 p_prefix=>l_debug_prefix,
3627 p_msg_level=>fnd_log.level_procedure,
3628 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3629 p_module =>'pop_missing_lnks');
3630 END IF;
3631
3632 ei_HZ_CUST_ACCOUNT_ROLES;
3633 -- 13. HZ_CUSTOMER_PROFILES
3634 /* LOG(
3635 message => 'bfr calling ei_HZ_CUSTOMER_PROFILES()',
3636 p_prefix =>l_debug_prefix,
3637 p_module => l_module);
3638 */
3639 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3640 hz_utility_v2pub.DEBUG
3641 (p_message=>'bfr calling ei_HZ_CUSTOMER_PROFILES()',
3642 p_prefix=>l_debug_prefix,
3643 p_msg_level=>fnd_log.level_procedure,
3644 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3645 p_module =>'pop_missing_lnks');
3646 END IF;
3647
3648 ei_HZ_CUSTOMER_PROFILES;
3649 -- 14. HZ_CUST_SITE_USES_ALL
3650 /* LOG(
3651 message => 'bfr calling ei_HZ_CUST_SITE_USES_ALL()',
3652 p_prefix =>l_debug_prefix,
3653 p_module => l_module);
3654 */
3655 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3656 hz_utility_v2pub.DEBUG
3657 (p_message=>'bfr calling ei_HZ_CUST_SITE_USES_ALL()',
3658 p_prefix=>l_debug_prefix,
3659 p_msg_level=>fnd_log.level_procedure,
3660 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3661 p_module =>'pop_missing_lnks');
3662 END IF;
3663
3664 ei_HZ_CUST_SITE_USES_ALL;
3665 -- 15. HZ_CUST_ACCT_SITES_ALL
3666 /*
3667 LOG(
3668 message => 'bfr calling ei_HZ_CUST_ACCT_SITES_ALL()',
3669 p_prefix =>l_debug_prefix,
3670 p_module => l_module);
3671 */
3672 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3673 hz_utility_v2pub.DEBUG
3674 (p_message=>'bfr calling ei_HZ_CUST_ACCT_SITES_ALL()',
3675 p_prefix=>l_debug_prefix,
3676 p_msg_level=>fnd_log.level_procedure,
3677 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3678 p_module =>'pop_missing_lnks');
3679 END IF;
3680
3681 ei_HZ_CUST_ACCT_SITES_ALL;
3682 -- 16. HZ_CUST_ACCOUNTS
3683 /*
3684 LOG(
3685 message => 'bfr calling ei_HZ_CUST_ACCOUNTS()',
3686 p_prefix =>l_debug_prefix,
3687 p_module => l_module);
3688 */
3689 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3690 hz_utility_v2pub.DEBUG
3691 (p_message=>'bfr calling ei_HZ_CUST_ACCOUNTS()',
3692 p_prefix=>l_debug_prefix,
3693 p_msg_level=>fnd_log.level_procedure,
3694 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3695 p_module =>'pop_missing_lnks');
3696 END IF;
3697
3698 ei_HZ_CUST_ACCOUNTS;
3699 COMMIT;
3700 LOG( message => 'populate_missing_links()-',
3701 p_prefix =>l_debug_prefix,
3702 p_module => l_module);
3703 EXCEPTION
3704 WHEN OTHERS THEN
3705 LOG( message => 'populate_missing_links:'||SQLERRM,
3706 p_prefix =>l_debug_prefix,
3707 p_module => l_module);
3708 ROLLBACK;
3709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3710 END populate_missing_links;
3711 ------------------------------------------------------------------------
3712 /*
3713 */
3714 PROCEDURE upd_bot_evt_id (
3715 p_bulk_evt in BOOLEAN, -- whether bulk event was raised (TRUE) or not (FALSE)
3716 p_evt_id IN NUMBER, -- only used for one event per object instance
3717 p_child_id IN NUMBER, -- only used for one event per object instance
3718 p_child_bo_code IN VARCHAR2, -- only used for one event per object instance
3719 p_per_ins_evt_id IN NUMBER,
3720 p_per_upd_evt_id IN NUMBER,
3721 p_org_ins_evt_id IN NUMBER,
3722 p_org_upd_evt_id IN NUMBER,
3723 p_perc_ins_evt_id IN NUMBER,
3724 p_perc_upd_evt_id IN NUMBER,
3725 p_orgc_ins_evt_id IN NUMBER,
3726 p_orgc_upd_evt_id IN NUMBER) IS
3727
3728 -- cursor to select the root node identifier (child_id) and the
3729 -- appropriate event id.
3730 CURSOR c_bulk_bo_gt (
3731 cp_per_ins_evt_id IN NUMBER,
3732 cp_per_upd_evt_id IN NUMBER,
3733 cp_org_ins_evt_id IN NUMBER,
3734 cp_org_upd_evt_id IN NUMBER,
3735 cp_perc_ins_evt_id IN NUMBER,
3736 cp_perc_upd_evt_id IN NUMBER,
3737 cp_orgc_ins_evt_id IN NUMBER,
3738 cp_orgc_upd_evt_id IN NUMBER) IS
3739 SELECT t.child_id, tmp.bo_code,
3740 CASE tmp.bo_code
3741 WHEN 'PERSON' THEN
3742 DECODE ( tmp.event_type_flag, 'U', cp_per_upd_evt_id, cp_per_ins_evt_id)
3743 WHEN 'ORG' THEN
3744 DECODE ( tmp.event_type_flag, 'U', cp_org_upd_evt_id, cp_org_ins_evt_id)
3745 WHEN 'PERSON_CUST' THEN
3746 DECODE ( tmp.event_type_flag, 'U', cp_perc_upd_evt_id, cp_perc_ins_evt_id)
3747 WHEN 'ORG_CUST' THEN
3748 DECODE ( tmp.event_type_flag, 'U', cp_orgc_upd_evt_id, cp_orgc_ins_evt_id)
3749 END eventid
3750 FROM HZ_BUS_OBJ_TRACKING t, hz_bes_gt tmp
3751 WHERE t.child_id = tmp.party_id
3752 AND t.child_bo_code = tmp.bo_code
3753 AND t.child_entity_name = 'HZ_PARTIES'
3754 AND t.parent_bo_code IS NULL
3755 AND t.event_id IS NULL;
3756
3757 -- local variables
3758 l_debug_prefix VARCHAR2 (10):= 'updbot';
3759 l_rtids NUMBER_TBLTYPE;
3760 l_evtids NUMBER_TBLTYPE;
3761 l_bo_codes VCHAR2_30_TBLTYPE;
3762
3763 BEGIN
3764 /* Logic flow
3765 Check if the VBulk event was raised or not.
3766 If the bulk event is raised
3767 open c_bulk_bo_gt with all 8 event id params
3768 do forall update of on BOT using the retrun values
3769 commit;
3770 If the individual event is raised
3771 */
3772 hz_utility_v2pub.DEBUG
3773 (p_message=>'upd_bot_evt_id()+',
3774 p_prefix=>l_debug_prefix,
3775 p_msg_level=>fnd_log.level_procedure);
3776 IF p_bulk_evt THEN
3777 -- The event was raised in bulk i.e., One event was raised for a set of
3778 -- Business Objects of the same type with same operation.
3779 OPEN c_bulk_bo_gt(
3780 p_per_ins_evt_id,
3781 p_per_upd_evt_id,
3782 p_org_ins_evt_id,
3783 p_org_upd_evt_id,
3784 p_perc_ins_evt_id,
3785 p_perc_upd_evt_id,
3786 p_orgc_ins_evt_id,
3787 p_orgc_upd_evt_id );
3788 FETCH c_bulk_bo_gt BULK COLLECT INTO l_rtids, l_bo_codes, l_evtids;
3789 CLOSE c_bulk_bo_gt;
3790 FORALL i IN l_rtids.FIRST..l_rtids.LAST
3791 UPDATE HZ_BUS_OBJ_TRACKING
3792 SET event_id = l_evtids(i)
3793 WHERE ROWID IN (
3794 SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
3795 START WITH child_id = l_rtids(i)
3796 AND child_entity_name = 'HZ_PARTIES'
3797 AND parent_BO_CODE IS NULL
3798 AND event_id IS NULL
3799 AND CHILD_BO_CODE = l_bo_codes(i)
3800 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
3801 AND PARENT_ID = PRIOR CHILD_ID
3802 AND parent_bo_code = PRIOR child_bo_code) ;
3803 ELSE
3804 -- one event per object instance was raised.
3805 UPDATE HZ_BUS_OBJ_TRACKING
3806 SET event_id = p_evt_id
3807 WHERE ROWID IN (
3808 SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
3809 START WITH child_id = p_child_id
3810 AND child_entity_name = 'HZ_PARTIES'
3811 AND parent_BO_CODE IS NULL
3812 AND event_id IS NULL
3813 AND CHILD_BO_CODE = p_child_bo_code
3814 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
3815 AND PARENT_ID = PRIOR CHILD_ID
3816 AND parent_bo_code = PRIOR child_bo_code) ;
3817 END IF;
3818 -- commit the changes
3819 COMMIT;
3820 hz_utility_v2pub.DEBUG
3821 (p_message=>'upd_bot_evt_id()-',
3822 p_prefix=>l_debug_prefix,
3823 p_msg_level=>fnd_log.level_procedure);
3824 EXCEPTION
3825 WHEN OTHERS THEN
3826 ROLLBACK;
3827 hz_utility_v2pub.DEBUG
3828 (p_message=>SQLERRM,
3829 p_prefix=>l_debug_prefix,
3830 p_msg_level=>fnd_log.level_procedure);
3831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3832 END upd_bot_evt_id;
3833 -------------------------------------------------------------------------------
3834 PROCEDURE upd_bot_evtid_dt (
3835 p_bulk_evt in BOOLEAN, -- whether bulk event was raised (TRUE) or not (FALSE)
3836 p_evt_id IN NUMBER, -- only used for one event per object instance
3837 p_child_id IN NUMBER, -- only used for one event per object instance
3838 p_child_bo_code IN VARCHAR2, -- only used for one event per object instance
3839 p_creation_date IN DATE, -- used to update the creation_date column in BOT
3840 p_evt_type IN VARCHAR2, -- this is to pass the event type (Bug4773627)
3841 p_commit IN BOOLEAN, -- to control commit or rolllback when called from v3 api(Bug4957408)
3842 p_per_ins_evt_id IN NUMBER,
3843 p_per_upd_evt_id IN NUMBER,
3844 p_org_ins_evt_id IN NUMBER,
3845 p_org_upd_evt_id IN NUMBER,
3846 p_perc_ins_evt_id IN NUMBER,
3847 p_perc_upd_evt_id IN NUMBER,
3848 p_orgc_ins_evt_id IN NUMBER,
3849 p_orgc_upd_evt_id IN NUMBER) IS
3850
3851 -- cursor to select the root node identifier (child_id) and the
3852 -- appropriate event id.
3853 CURSOR c_bulk_bo_gt (
3854 cp_per_ins_evt_id IN NUMBER,
3855 cp_per_upd_evt_id IN NUMBER,
3856 cp_org_ins_evt_id IN NUMBER,
3857 cp_org_upd_evt_id IN NUMBER,
3858 cp_perc_ins_evt_id IN NUMBER,
3859 cp_perc_upd_evt_id IN NUMBER,
3860 cp_orgc_ins_evt_id IN NUMBER,
3861 cp_orgc_upd_evt_id IN NUMBER) IS
3862 SELECT t.child_id, tmp.bo_code,
3863 CASE tmp.bo_code
3864 WHEN 'PERSON' THEN
3865 DECODE ( tmp.event_type_flag, 'U', cp_per_upd_evt_id, cp_per_ins_evt_id)
3866 WHEN 'ORG' THEN
3867 DECODE ( tmp.event_type_flag, 'U', cp_org_upd_evt_id, cp_org_ins_evt_id)
3868 WHEN 'PERSON_CUST' THEN
3869 DECODE ( tmp.event_type_flag, 'U', cp_perc_upd_evt_id, cp_perc_ins_evt_id)
3870 WHEN 'ORG_CUST' THEN
3871 DECODE ( tmp.event_type_flag, 'U', cp_orgc_upd_evt_id, cp_orgc_ins_evt_id)
3872 END eventid,
3873 NVL(tmp.EVENT_TYPE_FLAG, 'C')
3874 FROM HZ_BUS_OBJ_TRACKING t, hz_bes_gt tmp
3875 WHERE t.child_id = tmp.party_id
3876 AND t.child_bo_code = tmp.bo_code
3877 AND t.child_entity_name = 'HZ_PARTIES'
3878 AND t.parent_bo_code IS NULL
3879 AND t.event_id IS NULL;
3880 /*
3881 -- To support population of event flag in case of raising (Bug4773627)
3882 -- one event per BO instance or from raising events from V3 a.k.a Logical APIs.
3883 CURSOR c_get_evttype (
3884 cp_evt_id IN NUMBER,
3885 cp_rt_node_id IN NUMBER,
3886 cp_bo_code IN VARCHAR2) IS
3887 SELECT NVL(tmp.EVENT_TYPE_FLAG, 'C')
3888 from hz_bes_gt tmp
3889 where tmp.bo_code = cp_bo_code
3890 and tmp.party_id = cp_rt_node_id
3891 and tmp.event_id = cp_evt_id;
3892 */
3893 -- local variables
3894 l_debug_prefix VARCHAR2 (10):= 'updbot';
3895 l_rtids NUMBER_TBLTYPE;
3896 l_evtids NUMBER_TBLTYPE;
3897 l_bo_codes VCHAR2_30_TBLTYPE;
3898 l_evtTypes VCHAR2_30_TBLTYPE; -- added to support Bug4773627
3899
3900 BEGIN
3901 /* Logic flow
3902 Check if the VBulk event was raised or not.
3903 If the bulk event is raised
3904 open c_bulk_bo_gt with all 8 event id params
3905 do forall update of on BOT using the retrun values
3906 commit;
3907 If the individual event is raised
3908 */
3909 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3910 hz_utility_v2pub.DEBUG
3911 (p_message=>'upd_bot_evtid_dt()+',
3912 p_prefix=>l_debug_prefix,
3913 p_msg_level=>fnd_log.level_procedure,
3914 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3915 p_module =>'updbot');
3916 END IF;
3917
3918 IF p_bulk_evt THEN
3919 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3920 hz_utility_v2pub.DEBUG
3921 (p_message=>'for bulk event',
3922 p_prefix=>l_debug_prefix,
3923 p_msg_level=>fnd_log.level_procedure,
3924 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3925 p_module =>'updbot');
3926 END IF;
3927 -- The event was raised in bulk i.e., One event was raised for a set of
3928 -- Business Objects of the same type with same operation.
3929 OPEN c_bulk_bo_gt(
3930 p_per_ins_evt_id,
3931 p_per_upd_evt_id,
3932 p_org_ins_evt_id,
3933 p_org_upd_evt_id,
3934 p_perc_ins_evt_id,
3935 p_perc_upd_evt_id,
3936 p_orgc_ins_evt_id,
3937 p_orgc_upd_evt_id );
3938 FETCH c_bulk_bo_gt BULK COLLECT INTO l_rtids, l_bo_codes, l_evtids, l_evtTypes;
3939 CLOSE c_bulk_bo_gt;
3940 FORALL i IN l_rtids.FIRST..l_rtids.LAST
3941 UPDATE HZ_BUS_OBJ_TRACKING
3942 SET event_id = l_evtids(i),
3943 creation_date = p_creation_date,
3944 PARENT_EVENT_FLAG = nvl2(PARENT_BO_CODE, NULL, l_evtTypes(i))
3945 WHERE event_id is null and ROWID IN (
3946 SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
3947 START WITH child_id = l_rtids(i)
3948 AND child_entity_name = 'HZ_PARTIES'
3949 AND parent_BO_CODE IS NULL
3950 AND event_id IS NULL
3951 AND CHILD_BO_CODE = l_bo_codes(i)
3952 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
3953 AND PARENT_ID = PRIOR CHILD_ID
3954 AND parent_bo_code = PRIOR child_bo_code) ;
3955 ELSE
3956 -- one event per object instance was raised.
3957 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3958 hz_utility_v2pub.DEBUG
3959 (p_message=>'for one event per object instance',
3960 p_prefix=>l_debug_prefix,
3961 p_msg_level=>fnd_log.level_procedure,
3962 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
3963 p_module =>'updbot');
3964 END IF;
3965 UPDATE HZ_BUS_OBJ_TRACKING
3966 SET event_id = p_evt_id,
3967 creation_date = p_creation_date,
3968 PARENT_EVENT_FLAG = nvl2(PARENT_BO_CODE, NULL, p_evt_type)
3969 WHERE event_id is null and ROWID IN (
3970 SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
3971 START WITH child_id = p_child_id
3972 AND child_entity_name = 'HZ_PARTIES'
3973 AND parent_BO_CODE IS NULL
3974 AND event_id IS NULL
3975 AND CHILD_BO_CODE = p_child_bo_code
3976 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
3977 AND PARENT_ID = PRIOR CHILD_ID
3978 AND parent_bo_code = PRIOR child_bo_code
3979 AND event_id is null) ;
3980 /*
3981 OPEN c_get_evttype ( p_evt_id, p_child_id, p_child_bo_code);
3982 FETCH c_get_evttype BULK COLLECT INTO l_evtTypes;
3983 CLOSE c_get_evttype ;
3984 -- To support population of event flag in case of raising (Bug4773627)
3985 -- one event per BO instance or raising events from V3 a.k.a Logical APIs.
3986 UPDATE HZ_BUS_OBJ_TRACKING
3987 SET PARENT_EVENT_FLAG = l_evtTypes(1)
3988 where event_id = p_evt_id
3989 AND parent_BO_CODE IS NULL
3990 AND CHILD_BO_CODE = p_child_bo_code
3991 AND child_entity_name = 'HZ_PARTIES'
3992 AND child_id = p_child_id;
3993 */
3994 END IF;
3995 if p_commit then
3996 -- commit the changes only when conc program calls
3997 COMMIT;
3998 end if;
3999 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4000 hz_utility_v2pub.DEBUG
4001 (p_message=>'upd_bot_evtid_dt()-',
4002 p_prefix=>l_debug_prefix,
4003 p_msg_level=>fnd_log.level_procedure,
4004 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4005 p_module =>'updbot');
4006 END IF;
4007 EXCEPTION
4008 WHEN OTHERS THEN
4009 LOG( message => 'upd_bot_evtid_dt:'||SQLERRM,
4010 p_prefix =>l_debug_prefix,
4011 p_module =>'updbot');
4012 if p_commit then
4013 -- rollback the changes only when conc program calls
4014 ROLLBACK;
4015 end if;
4016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4017 END upd_bot_evtid_dt;
4018 ------------------------------------------------------------------------------
4019
4020 /*
4021 Procedure name: upd_hzp_bo_ver ()
4022 Scope: external
4023 Purpose: This procedure will update the hz_parties table with all the
4024 latest bo_version_numbers. This must be done to short circuit the
4025 process for figuring out event type when the object is touched next time.
4026 Called From: This is called from HZ_BES_BO_RAISE_PKG
4027 Called By: bes_main()
4028 Input parameters:
4029 p_per_bo_ver IN NUMBER -- for passing the person bo version number
4030 p_org_bo_ver IN NUMBER -- for passing the org bo version number
4031 p_perc_bo_ver IN NUMBER -- for passing the person cust bo version number
4032 p_orgc_bo_ver IN NUMBER -- for passing the org cust bo version number
4033 */
4034 ----------------------------------------------------------------------------
4035 PROCEDURE upd_hzp_bo_ver (
4036 p_per_bo_ver IN NUMBER, -- for passing the person bo version number
4037 p_org_bo_ver IN NUMBER, -- for passing the org bo version number
4038 p_perc_bo_ver IN NUMBER, -- for passing the person cust bo version number
4039 p_orgc_bo_ver IN NUMBER) IS -- for passing the org cust bo version number
4040 -- local variables
4041 l_debug_prefix VARCHAR2 (10):= 'updhzp';
4042
4043 BEGIN
4044 /* Logic flow
4045 Select all the rows from GT, from the list, update the hz_parties table for all the
4046 rows that do not have correct BO version number
4047 */
4048 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4049 hz_utility_v2pub.DEBUG
4050 (p_message=>'upd_hzp_bo_ver()+',
4051 p_prefix=>l_debug_prefix,
4052 p_msg_level=>fnd_log.level_procedure,
4053 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4054 p_module =>'updp');
4055 END IF;
4056
4057 UPDATE HZ_PARTIES
4058 SET PERSON_BO_VERSION = p_per_bo_ver
4059 WHERE party_id IN
4060 (SELECT t.party_id
4061 FROM HZ_BES_GT t
4062 WHERE t.BO_CODE = 'PERSON')
4063 AND (PERSON_BO_VERSION <> p_per_bo_ver
4064 OR PERSON_BO_VERSION IS NULL) ;
4065
4066 UPDATE HZ_PARTIES
4067 SET ORG_BO_VERSION = p_org_bo_ver
4068 WHERE party_id IN
4069 (SELECT t.party_id
4070 FROM HZ_BES_GT t
4071 WHERE t.BO_CODE = 'ORG')
4072 AND (ORG_BO_VERSION <>p_org_bo_ver
4073 OR ORG_BO_VERSION IS NULL);
4074
4075 UPDATE HZ_PARTIES
4076 SET PERSON_CUST_BO_VERSION = p_perc_bo_ver
4077 WHERE party_id IN
4078 (SELECT t.party_id
4079 FROM HZ_BES_GT t
4080 WHERE t.BO_CODE = 'PERSON_CUST')
4081 AND (PERSON_CUST_BO_VERSION <> p_perc_bo_ver
4082 OR PERSON_CUST_BO_VERSION IS NULL);
4083
4084 UPDATE HZ_PARTIES
4085 SET ORG_CUST_BO_VERSION = p_orgc_bo_ver
4086 WHERE party_id IN
4087 (SELECT t.party_id
4088 FROM HZ_BES_GT t
4089 WHERE t.BO_CODE = 'ORG_CUST')
4090 AND (ORG_CUST_BO_VERSION <>p_orgc_bo_ver
4091 OR ORG_CUST_BO_VERSION IS NULL);
4092
4093 -- commit the changes
4094 COMMIT;
4095 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4096 hz_utility_v2pub.DEBUG
4097 (p_message=>'upd_hzp_bo_ver()-',
4098 p_prefix=>l_debug_prefix,
4099 p_msg_level=>fnd_log.level_procedure,
4100 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4101 p_module =>'updp');
4102 END IF;
4103 EXCEPTION
4104 WHEN OTHERS THEN
4105 LOG( message => 'upd_hzp_bo_ver:'||SQLERRM,
4106 p_prefix =>l_debug_prefix,
4107 p_module =>'updp');
4108 ROLLBACK;
4109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4110 END upd_hzp_bo_ver;
4111 ----------------------------------------------------------------------------
4112 /*
4113 Procedure name: del_bot()
4114 Scope: external
4115 Purpose: This procedure will delete the records that were already processed
4116 by subscriptions.
4117 Input parameters:
4118 p_cutoff_dt IN DATE);-- for passing the cutoff date for deleting the recs in BOT
4119 */
4120 ----------------------------------------------------------------------------
4121 PROCEDURE del_bot (p_cutoff_dt IN DATE)IS
4122 l_debug_prefix VARCHAR2(20) := 'del_bot';
4123 BEGIN
4124 /*
4125 Goal:
4126 To delete the records that were identified for deletion.
4127 Who Calls this procedure:
4128 Cleanse concurrent program will call this procedure.
4129 Logic:
4130 Delete all the processed records that for which event is raised
4131 before given date.
4132
4133 */
4134 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4135 hz_utility_v2pub.DEBUG
4136 (p_message=>'del_bot()+',
4137 p_prefix=>l_debug_prefix,
4138 p_msg_level=>fnd_log.level_procedure,
4139 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4140 p_module =>'del_bot');
4141 hz_utility_v2pub.DEBUG
4142 (p_message=>'cutoff date is:'||TO_CHAR(p_cutoff_dt),
4143 p_prefix=>l_debug_prefix,
4144 p_msg_level=>fnd_log.level_procedure,
4145 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4146 p_module =>'del_bot');
4147 END IF;
4148
4149 DELETE FROM HZ_BUS_OBJ_TRACKING
4150 WHERE event_id IS NOT NULL
4151 AND creation_date < p_cutoff_dt;
4152
4153 -- commit the changes
4154 COMMIT;
4155 /* CASE 1: In cases, where Party Purge Conc Program purges the
4156 records, BOT table will some time have entire hierarchy of
4157 records that are to be deleted.
4158 Identify such records and delete them from BOT.
4159 To be done. Srikanth Jan 24th 2006.
4160 */
4161 COMMIT;
4162 /* CASE 2: In cases where Account Merge Conc Program purges the
4163 Account hieararchy from TCA Schema and not from BOT,
4164 These records have to be removed from BOT.
4165 Identify such records and delete them from BOT.
4166 To be done. Srikanth Jan 24th 2006.
4167 */
4168 COMMIT;
4169 /*CASE 3: In cases where Party Merge Conc Program
4170 merges the underlying entities of a party into another party
4171 and BOT only has those effected entities and not the parent
4172 then, CASE1 AND CASE2 cannot catch such orphan records.
4173
4174 Identify all such records from BOT that do not have corresponding
4175 row in TCA Schema and delete them.
4176
4177 This case works as an umbrella case wherein, all those records that
4178 were not identified by CASE1, CASE2 are caught here.
4179
4180 The reason to do this as the last option instead of first or the
4181 only option is, by taking care of first two cases, many of the
4182 effected records may be deleted. Leaving only a handful of records to
4183 be deleted for CASE3. This might enhance the performance.
4184 TO be Done - Srikanth Jan 24th 2006
4185 */
4186 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4187 hz_utility_v2pub.DEBUG
4188 (p_message=>'del_bot()-',
4189 p_prefix=>l_debug_prefix,
4190 p_msg_level=>fnd_log.level_procedure,
4191 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4192 p_module =>'del_bot');
4193 END IF;
4194
4195 EXCEPTION
4196 WHEN OTHERS THEN
4197 LOG( message => 'del_bot:'||SQLERRM,
4198 p_prefix =>l_debug_prefix,
4199 p_module =>'delbot');
4200 ROLLBACK;
4201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4202 END del_bot;
4203 ----------------------------------------------------------------------------
4204 /*
4205 This is an overloaded procedure.
4206 Procedure: del_duplicate_rows
4207 Scope: external - called only by the concurrent program.
4208 Purpose: This procedure
4209 selects duplicate records from BOT that were not yet processed and
4210 deletes one of the rows (row with max rowid).
4211 */
4212 ----------------------------------------------------------------------------
4213 PROCEDURE del_duplicate_rows IS
4214
4215 -- local variables
4216 l_debug_prefix VARCHAR2(40) := 'DEL_DUPLICATE_ROWS2';
4217 l_module VARCHAR2(30) := 'del_dup2';
4218
4219 BEGIN
4220 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4221 hz_utility_v2pub.DEBUG
4222 (p_message=>'del_duplicate_rows2+',
4223 p_prefix=>l_debug_prefix,
4224 p_msg_level=>fnd_log.level_procedure,
4225 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4226 p_module =>'del_dup');
4227 END IF;
4228
4229 DELETE FROM HZ_BUS_OBJ_TRACKING
4230 WHERE ROWID IN (
4231 SELECT bo_row
4232 FROM (
4233 SELECT rowid bo_row,
4234 RANK() over (PARTITION BY child_id, child_entity_name,
4235 child_bo_code, parent_entity_name, parent_id, parent_bo_code
4236 ORDER BY rowid) ROWRANK
4237 FROM HZ_BUS_OBJ_TRACKING a
4238 WHERE a.event_id IS NULL)
4239 WHERE ROWRANK >1);
4240 COMMIT;
4241 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4242 hz_utility_v2pub.DEBUG
4243 (p_message=>'del_duplicate_rows2-',
4244 p_prefix=>l_debug_prefix,
4245 p_msg_level=>fnd_log.level_procedure,
4246 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4247 p_module =>'del_dup');
4248 END IF;
4249
4250
4251 EXCEPTION
4252 WHEN NO_DATA_FOUND THEN
4253 -- no need of any action.
4254 NULL;
4255 END del_duplicate_rows;
4256 ----------------------------------------------------------------------------
4257 /*
4258 Procedure name: del_obj_hierarchy()
4259 Scope: external
4260 Purpose: Given a root object identifier, this procedure
4261 will delete the entire hierarchy.
4262 This procedure is called from
4263 1. party purge concurrent program
4264 2. account merge concurrent program with delete option.
4265
4266 In these two cases, as the entire party and its detailed records are
4267 purged, there is no use of maintaining those records in the BOT.
4268 If the purged records are left in BOT without deleting:
4269 1. There is a chance that an event may be raised for already
4270 purged record. Functionally, this would be incorrect.
4271 2. The Raise Events concurrent program may error out
4272 as it cannot find the party record in TCA Registry.
4273 This is needed for updating the party BO_VERSION columns to
4274 be updated after raising the event.
4275
4276 Called By:
4277 1. party purge concurrent program
4278 2. account merge concurrent program with delete option.
4279
4280 Input:
4281 BO Code: PERSON for Person BO,
4282 ORG for Organization BO,
4283 PERSON_CUST for Person Customer BO
4284 ORG_CUST for Organization Customer BO
4285 Object Identifier: Root Object Id (party id).
4286
4287 */
4288 procedure del_obj_hierarchy
4289 ( P_OBJ_ID IN NUMBER) IS
4290
4291 cursor c1_ptype (cp_party_id in number) is
4292 select party_type
4293 from hz_parties
4294 where party_id = cp_party_id;
4295
4296 -- local variables
4297 l_debug_prefix VARCHAR2(20) := 'del_obj:';
4298 l_module VARCHAR2(30) := 'del_obj_hierarchy';
4299 l_bo_code VARCHAR2(20);
4300
4301
4302 BEGIN
4303
4304 /*
4305 Flow:
4306 Figure out the party type based on partyId
4307 For BO code Person or Person Customers
4308 delete the Person and Person Customer hierarchies for a given partyId
4309 -- This is because, the person bo might have corresponding
4310 -- Person Customer BO in BOT
4311 For BO code Org or Org Customers
4312 delete the Org and Org Customer hierarchies for a given partyId
4313 -- This is because, the Org bo might have corresponding
4314 -- Org Customer BO in BOT
4315 */
4316 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4317 hz_utility_v2pub.DEBUG
4318 (p_message=>'del_obj_hierarchy+',
4319 p_prefix=>l_debug_prefix,
4320 p_msg_level=>fnd_log.level_procedure,
4321 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4322 p_module =>l_module);
4323 END IF;
4324
4325 OPEN c1_ptype (P_OBJ_ID );
4326 FETCH c1_ptype INTO l_bo_code;
4327 CLOSE c1_ptype;
4328 IF l_BO_CODE = 'PERSON' THEN
4329 -- for Person
4330 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4331 hz_utility_v2pub.DEBUG
4332 (p_message=>'deleting '||l_BO_CODE||' HIERARCHY corresponds to partyId:'||P_OBJ_ID,
4333 p_prefix=>l_debug_prefix,
4334 p_msg_level=>fnd_log.level_procedure,
4335 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4336 p_module =>l_module);
4337 END IF;
4338 DELETE FROM HZ_BUS_OBJ_TRACKING
4339 WHERE ROWID IN (
4340 SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
4341 START WITH
4342 event_id IS NULL AND child_id = P_OBJ_ID AND CHILD_BO_CODE = 'PERSON' AND
4343 child_entity_name = 'HZ_PARTIES' AND parent_id IS NULL AND
4344 PARENT_ENTITY_NAME is NULL
4345 CONNECT BY
4346 PARENT_BO_CODE = PRIOR CHILD_BO_CODE AND PARENT_ID = PRIOR CHILD_ID);
4347 -- for Person Customer
4348 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4349 hz_utility_v2pub.DEBUG
4350 (p_message=>'deleting '||l_bo_code||' Customer HIERARCHY corresponds to partyId:'||P_OBJ_ID,
4351 p_prefix=>l_debug_prefix,
4352 p_msg_level=>fnd_log.level_procedure,
4353 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4354 p_module =>l_module);
4355 END IF;
4356 DELETE FROM HZ_BUS_OBJ_TRACKING
4357 WHERE ROWID IN (
4358 SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
4359 START WITH
4360 event_id IS NULL AND child_id = P_OBJ_ID AND CHILD_BO_CODE = 'PERSON_CUST' AND
4361 child_entity_name = 'HZ_PARTIES' AND parent_id IS NULL AND
4362 PARENT_ENTITY_NAME is NULL
4363 CONNECT BY
4364 PARENT_BO_CODE = PRIOR CHILD_BO_CODE AND PARENT_ID = PRIOR CHILD_ID);
4365 ELSIF l_bo_code = 'ORGANIZATION' THEN
4366 -- for Org
4367 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4368 hz_utility_v2pub.DEBUG
4369 (p_message=>'deleting '||l_bo_code||' HIERARCHY corresponds to partyId:'||P_OBJ_ID,
4370 p_prefix=>l_debug_prefix,
4371 p_msg_level=>fnd_log.level_procedure,
4372 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4373 p_module =>l_module);
4374 END IF;
4375 DELETE FROM HZ_BUS_OBJ_TRACKING
4376 WHERE ROWID IN (
4377 SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
4378 START WITH
4379 event_id IS NULL AND child_id = P_OBJ_ID AND CHILD_BO_CODE = 'ORG' AND
4380 child_entity_name = 'HZ_PARTIES' AND parent_id IS NULL AND
4381 PARENT_ENTITY_NAME is NULL
4382 CONNECT BY
4383 PARENT_BO_CODE = PRIOR CHILD_BO_CODE AND PARENT_ID = PRIOR CHILD_ID);
4384 -- for Org Customer
4385 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4386 hz_utility_v2pub.DEBUG
4387 (p_message=>'deleting '||l_bo_code||' Customer HIERARCHY corresponds to partyId:'||P_OBJ_ID,
4388 p_prefix=>l_debug_prefix,
4389 p_msg_level=>fnd_log.level_procedure,
4390 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4391 p_module =>l_module);
4392 END IF;
4393 DELETE FROM HZ_BUS_OBJ_TRACKING
4394 WHERE ROWID IN (
4395 SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
4396 START WITH
4397 event_id IS NULL AND child_id = P_OBJ_ID AND CHILD_BO_CODE = 'ORG_CUST' AND
4398 child_entity_name = 'HZ_PARTIES' AND parent_id IS NULL AND
4399 PARENT_ENTITY_NAME is NULL
4400 CONNECT BY
4401 PARENT_BO_CODE = PRIOR CHILD_BO_CODE AND PARENT_ID = PRIOR CHILD_ID);
4402 ELSE
4403 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4404 hz_utility_v2pub.DEBUG
4405 (p_message=>'incorrect BO Code:'||l_bo_code,
4406 p_prefix=>l_debug_prefix,
4407 p_msg_level=>fnd_log.level_procedure,
4408 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4409 p_module =>l_module);
4410 END IF;
4411 END IF;
4412 /*
4413 Not issuing the commit stmt after deleting the records.
4414 This is because, commit is handled by the caller of this procedure.
4415 */
4416 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4417 hz_utility_v2pub.DEBUG
4418 (p_message=>'del_obj_hierarchy-',
4419 p_prefix=>l_debug_prefix,
4420 p_msg_level=>fnd_log.level_procedure,
4421 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4422 p_module =>l_module);
4423 END IF;
4424 EXCEPTION
4425 WHEN NO_DATA_FOUND THEN
4426 -- no need of any action.
4427 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4428 hz_utility_v2pub.DEBUG
4429 (p_message=>'No data to delete',
4430 p_prefix=>l_debug_prefix,
4431 p_msg_level=>fnd_log.level_procedure,
4432 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4433 p_module =>l_module);
4434 END IF;
4435 END del_obj_hierarchy;
4436 ----------------------------------------------------------------------------
4437 /*
4438 Procedure name: upd_entity_ids()
4439 Scope: external
4440 Purpose: Given a root object identifier, this procedure
4441 will delete the entire hierarchy.
4442 This procedure is called from
4443 1. party merge concurrent program
4444 2. account merge concurrent program
4445
4446 In these two cases, the entity ids are changed in TCA REgistry by
4447 the above concurrent programs.
4448 This procedure reflects the id changes in the BOT.
4449 This changed ids will enable the BO extraction API to get to the
4450 action types of the changed entities in BOT.
4451
4452 Note - this method does not handle RA_CUST_RECEIPT_METHODS and
4453 IBY_FNDCPT_PAYER_ASSGN_INSTR_V.
4454 This method only handles update of identifiers for HZ tables.
4455 Called By:
4456 1. party merge concurrent program
4457 2. account merge concurrent program
4458
4459 Input:
4460 request id: Concurrent Request Identifier
4461 */
4462 procedure upd_entity_ids
4463 ( P_request_id IN NUMBER) IS
4464
4465 -- cursor declaration
4466 CURSOR c_enty_ids (cp_request_id IN NUMBER) IS
4467 SELECT l.FROM_ENTITY_ID, l.TO_ENTITY_ID, d.entity_name
4468 FROM hz_merge_party_log l, hz_merge_dictionary d
4469 WHERE l.request_id = cp_request_id
4470 AND l.MERGE_DICT_ID = d.MERGE_DICT_ID
4471 AND d.entity_name LIKE 'HZ%'
4472 AND l.to_entity_id IS NOT null
4473 AND l.FROM_ENTITY_ID <> l.TO_ENTITY_ID
4474 ORDER BY 3 desc;
4475
4476 -- local variables
4477 l_debug_prefix VARCHAR2(20) := 'upd_ids:';
4478 l_module VARCHAR2(30) := 'upd_entity_ids';
4479
4480 l_from_ids NUMBER_TBLTYPE;
4481 l_to_ids NUMBER_TBLTYPE;
4482 l_entity_names VCHAR2_30_TBLTYPE;
4483
4484 BEGIN
4485 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4486 hz_utility_v2pub.DEBUG
4487 (p_message=>'upd_entity_ids+',
4488 p_prefix=>l_debug_prefix,
4489 p_msg_level=>fnd_log.level_procedure,
4490 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4491 p_module =>l_module);
4492 END IF;
4493
4494 /* Flow
4495 1. identify the fromId, ToId and entittyName from merge log table.
4496 2. then update the bot table
4497 */
4498
4499 OPEN c_enty_ids (P_request_id );
4500 FETCH c_enty_ids BULK COLLECT INTO l_from_ids, l_to_ids, l_entity_names;
4501 CLOSE c_enty_ids;
4502
4503 FORALL i IN l_from_ids.FIRST..l_from_ids.LAST
4504 UPDATE HZ_BUS_OBJ_TRACKING
4505 SET CHILD_ID = l_to_ids(i)
4506 WHERE event_id IS NULL
4507 AND CHILD_ENTITY_NAME = l_entity_names(i)
4508 AND CHILD_ID = l_from_ids(i);
4509
4510 FORALL i IN l_from_ids.FIRST..l_from_ids.LAST
4511 UPDATE HZ_BUS_OBJ_TRACKING
4512 SET PARENT_ID = l_to_ids(i)
4513 WHERE event_id IS NULL
4514 AND PARENT_ENTITY_NAME = l_entity_names(i)
4515 AND PARENT_ID = l_from_ids(i);
4516
4517 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4518 hz_utility_v2pub.DEBUG
4519 (p_message=>'upd_entity_ids-',
4520 p_prefix=>l_debug_prefix,
4521 p_msg_level=>fnd_log.level_procedure,
4522 p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
4523 p_module =>l_module);
4524 END IF;
4525 END upd_entity_ids;
4526 ----------------------------------------------------------------------------
4527 ----------------------------------------------------------------------------
4528 END HZ_BES_BO_UTIL_PKG; -- pkg
[Home] [Help]