[Home] [Help]
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)
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
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)
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)
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
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)
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)
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
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)
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
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
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)
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
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)
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)
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)
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
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
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)
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
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)
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)
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)
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)
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
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)
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)
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
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)
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)
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
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)
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)
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
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
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)
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
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)
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)
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
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)
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)
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
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)
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)
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
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)
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)
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
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)
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)
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
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)
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)
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
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)
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
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)
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
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
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
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
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 (
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
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 (
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
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
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
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,
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
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
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
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
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
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
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
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
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
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);
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);