[Home] [Help]
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
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:
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
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.
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
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)
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'
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
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)
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
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
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
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
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
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
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
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');
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)
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:
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
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)
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) --
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.
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,
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
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
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
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
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');
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;
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
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)
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:
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
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
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
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
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
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()
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: */
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:
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
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');
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
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)
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
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
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
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
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');
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;
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
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'
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
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)
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:
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
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
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
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
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
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:
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
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.
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,
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
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
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
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
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
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);
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
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');
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,
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);
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
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');
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,