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