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