[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the parents duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: hz_contact_preferences,
child_bo: null, parent_bo: null, parent_entity_name: PS,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_PARTY_SITES
parent_bo aliased as child_bo: PS
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_PARTIES
grand_parent_bo aliased as parent_bo: Org
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_PARTY_SITES,
child_bo: PS, parent_bo: Org, parent_entity_name: HZ_PARTIES,
parent_id: 456
6. It is non-trivial to figure out the business object codes for both parent
and grand parent, grand parent identifier or grand parent entity name.
To do this, "inner select" uses case statement on parent_entity_name.
Some times, an embedded SQL is necessary to fgure out this.
Example:
Child is HZ_CONTACT_PREFERENCE.
Parent is HZ_CONTACT_POINTS and it's parent is Party.
To figure out the grand parent bo code, SQL is necessary to run against
hz_parties to figure out the party_type based on owner_table_id of the
hz_contact_points table.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG, LAST_UPDATE_DATE,
CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_ENTITY_NAME, PARENT_ID, CHILD_BO_CODE, PARENT_BO_CODE
ORDER BY LAST_UPDATE_DATE) as cprank
,CASE PARENT_ENTITY_NAME
WHEN 'HZ_CONTACT_POINTS' THEN -- identify GrandParentEntityName when CP is the parent of CPP
(SELECT
CASE OWNER_TABLE_NAME
WHEN 'HZ_PARTIES' THEN -- identify GrandParentEntityName when CP is the parent of CPP, Party is parent of CP
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
WHEN 'PERSON' THEN 'HZ_PARTIES'
WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
ELSE NULL
END
FROM hz_parties
WHERE party_id = owner_table_id)
WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentEntityName when CP is parent of CPP, PS is parent of CP
'HZ_PARTY_SITES'
ELSE NULL
END
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = PARENT_ID)
WHEN 'HZ_PARTIES' THEN -- identify GrandParentEntityName when Party is parent of CPP
NULL
WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentEntityName when PS is parent of CPP
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
WHEN 'PERSON' THEN 'HZ_PARTIES'
WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
ELSE NULL
END
FROM HZ_PARTIES
WHERE party_id = (SELECT ps.party_id
FROM HZ_PARTY_SITES ps
WHERE ps.party_site_id = PARENT_ID))
WHEN 'HZ_ORG_CONTACTS' THEN -- identify GrandParentEntityName when OrgContact is parent of CPP
'HZ_PARTIES'
ELSE
NULL
END parent_entity_name, -- this is the grand parent entity name of cont pref - written as parent entity
CASE PARENT_ENTITY_NAME
WHEN 'HZ_CONTACT_POINTS' THEN -- identify GrandParentEntityId when CP is parent of CPP
(SELECT
CASE OWNER_TABLE_NAME
WHEN 'HZ_PARTIES' THEN -- identify GrandParentEntityId when CP is parent of CPP, party is parentOf CP
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN OWNER_TABLE_ID
WHEN 'PERSON' THEN OWNER_TABLE_ID
WHEN 'PARTY_RELATIONSHIP' THEN
(SELECT oc.org_contact_id
FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
WHERE r.relationship_id = oc.party_relationship_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION'
AND r.party_id = p.party_id)
ELSE NULL
END
FROM hz_parties p
WHERE p.party_id = owner_table_id)
WHEN 'HZ_PARTY_SITES' THEN OWNER_TABLE_ID -- identify GrandParentEntityId when CP is parent of CPP, PS parentOf CP
ELSE NULL
END
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = PARENT_ID)
WHEN 'HZ_PARTIES' THEN -- identify GrandParentEntityId when Party is parent of CPP
NULL
WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentEntityId when PS is parent of CPP
(SELECT
CASE p.party_type
WHEN 'ORGANIZATION' THEN p.party_id -- identify GrandParentEntityId when PS is parent of CPP, Org parentOf PS
WHEN 'PERSON' THEN p.party_id -- identify GrandParentEntityId when PS is parent of CPP, Per parentOf PS
WHEN 'PARTY_RELATIONSHIP' THEN -- identify GrandParentEntityId when PS is parent of CPP, Rel parentOf PS
(SELECT oc.org_contact_id
FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
WHERE r.relationship_id = oc.party_relationship_id
AND r.party_id = p.party_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION')
ELSE NULL
END
FROM hz_parties p
WHERE p.party_id = (select ps.party_id
from HZ_PARTY_SITES ps
where ps.party_site_id = PARENT_ID))
WHEN 'HZ_ORG_CONTACTS' THEN -- identify GrandParentEntityId when OrgContact is parent of CPP
(SELECT r.object_id
FROM hz_relationships r, hz_org_contacts oc
WHERE oc.org_contact_id = PARENT_ID
AND oc.party_relationship_id = r.relationship_id
AND subject_type ='PERSON'
AND object_type = 'ORGANIZATION')
ELSE NULL
END parent_id, -- this is the grand parent id of cont pref - written as parent id
CASE PARENT_ENTITY_NAME -- this case is for identifying the grand parent BO
WHEN 'HZ_CONTACT_POINTS' THEN -- identify GrandParentBO when CP is parent of CPP
(SELECT
CASE OWNER_TABLE_NAME
WHEN 'HZ_PARTIES' THEN -- identify GrandParentBO when CP is parent of CPP, party is parentOf CP
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'ORG'
WHEN 'PERSON' THEN 'PERSON'
WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
ELSE NULL
END
FROM hz_parties
WHERE party_id = owner_table_id)
WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentBO when CP is parent of CPP, PS is parentOf CP
'PARTY_SITE'
ELSE NULL
END
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = PARENT_ID)
WHEN 'HZ_PARTIES' THEN -- identify GrandParentBO when Party is parent of CPP
NULL
WHEN 'HZ_PARTY_SITES' THEN -- identify GrandParentBO when PS is parent of CPP
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'ORG'-- identify GrandParentBO when PS is parent of CPP, Org is parent of PS
WHEN 'PERSON' THEN 'PERSON' -- identify GrandParentBO when PS is parent of CPP, Per is parent of PS
WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT' -- identify GrandParentBO when PS is parent of CPP, Rel is parent of PS
ELSE NULL
END
FROM hz_parties
WHERE party_id = (select party_id
from hz_party_sites
where party_site_id = PARENT_ID))
WHEN 'HZ_ORG_CONTACTS' THEN 'ORG' -- identify GrandParentBO when OrgContact is parent of CPP
ELSE
NULL
END parent_bo_code -- this is the grand parent bo, written as parent bo
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_CONTACT_PREFERENCES'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND cprank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the parents duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
6. It is non-trivial to figure out the business object codes for both parent
and grand parent, grand parent identifier or grand parent entity name.
To do this, "inner select" uses case statement on parent_entity_name.
Some times, an embedded SQL is necessary to fgure out this.
Example:
Child is HZ_PARTY_SITES_EXT_VL.
Parent is PS and it's parent is Party.
To figure out the grand parent bo code, SQL is necessary to run against
HZ_PARTIES to figure out the PARTY_TYPE based on PARTY_ID of the
HZ_PARTY_SITES table.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_ENTITY_NAME, PARENT_ID
ORDER BY LAST_UPDATE_DATE, child_id) as psxrank
,CASE PARENT_BO_CODE
WHEN 'PARTY_SITE' THEN
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
WHEN 'PERSON' THEN 'HZ_PARTIES'
WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
ELSE NULL
END
FROM HZ_PARTIES
WHERE party_id = (SELECT ps.party_id
FROM HZ_PARTY_SITES ps
WHERE ps.party_site_id = PARENT_ID))
ELSE NULL
END parent_entity_name -- this is grand parent tbl name of CP - written as parent entity name
,CASE PARENT_BO_CODE
WHEN 'PARTY_SITE' THEN
(SELECT
CASE p.party_type
WHEN 'ORGANIZATION' THEN p.party_id
WHEN 'PERSON' THEN p.party_id
WHEN 'PARTY_RELATIONSHIP' THEN
(SELECT oc.org_contact_id
FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
WHERE r.relationship_id = oc.party_relationship_id
AND r.party_id = p.party_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION')
ELSE NULL
END
FROM hz_parties p
WHERE p.party_id = (select ps.party_id
from HZ_PARTY_SITES ps
where ps.party_site_id = PARENT_ID))
ELSE NULL
END parent_id -- this is the grand parent id of ps extension - written as parent
,CASE PARENT_BO_CODE
WHEN 'PARTY_SITE' THEN
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'ORG'
WHEN 'PERSON' THEN 'PERSON'
WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
ELSE NULL
END
FROM hz_parties
WHERE party_id = (SELECT party_id
FROM HZ_PARTY_SITES
WHERE party_site_id = PARENT_ID))
ELSE NULL
END parent_bo_code -- this is the grand parent bo, written as parent
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_PARTY_SITES_EXT_VL'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND psxrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the parents duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,NULL parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
,NULL parent_bo_code -- this is the grand parent bo, written as parent
,NULL parent_id -- this is grand parent id of acct roles, written as parent id
,'U' child_operation_flag
,'Y' populated_flag
,t.LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY t.LAST_UPDATE_DATE, child_id) as ORGrank
FROM HZ_BUS_OBJ_TRACKING t
WHERE CHILD_ENTITY_NAME = 'HZ_ORG_PROFILES_EXT_VL'
AND PARENT_BO_CODE = 'ORG'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND ORGrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL ;
INSERT INTO HZ_BUS_OBJ_TRACKING (
CHILD_ID
,CHILD_BO_CODE
,CHILD_ENTITY_NAME
,CHILD_OPERATION_FLAG
,POPULATED_FLAG
,PARENT_ID
,PARENT_ENTITY_NAME
,PARENT_BO_CODE
,LAST_UPDATE_DATE
,CREATION_DATE)
SELECT DISTINCT
p.CHILD_ID
,p.CHILD_BO_CODE
,p.CHILD_ENTITY_NAME
,p.CHILD_OPERATION_FLAG
,p.POPULATED_FLAG
,p.PARENT_ID
,p.PARENT_ENTITY_NAME
,'PERSON_CONTACT' PARENT_BO_CODE
,p.LAST_UPDATE_DATE
,p.CREATION_DATE
FROM
(SELECT
CHILD_ID
,CHILD_BO_CODE
,CHILD_ENTITY_NAME
,CHILD_OPERATION_FLAG
,POPULATED_FLAG
,PARENT_ID
,PARENT_ENTITY_NAME
,LAST_UPDATE_DATE
,CREATION_DATE
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_PER_PROFILES_EXT_VL'
AND parent_bo_code = 'PERSON'
AND event_id IS NULL) p, hz_org_contacts oc, hz_relationships r
WHERE p.PARENT_ID = r.subject_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION'
AND r.relationship_id = oc.party_relationship_id
AND NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the records that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = p.CHILD_ENTITY_NAME
AND bot.CHILD_ID = p.child_id
AND bot.PARENT_ID = p.PARENT_ID
AND bot.PARENT_BO_CODE = 'PERSON_CONTACT');
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the parents duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_PER_PROFILES_EXT_VL,
child_bo: NULL, parent_bo: Person_Contact, parent_entity_name: HZ_PARTIES,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_PARTIES
parent_bo aliased as child_bo: Person_Contact
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_ORG_CONTACTS
grand_parent_bo aliased as parent_bo: Org_Contact
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_PARTITES,
child_bo: Person_Contact, parent_bo: Org_Contact, parent_entity_name: HZ_ORG_CONTACTS,
parent_id: 456
Note: Two separate SQLs insert statements are necessary as a person can exist
multiple times as an org_contact. So, when getting the org_contact_id,
SQL will return the multiple rows. This makes is impossible to write
it in one single SQL with CASE statements.
The following SQL is to generate all the parent and grand parent combination for
pp_extn entity which is a child of PERSON BO
*/
IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.DEBUG
(p_message=>'pop missing lnks for per prof ext',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure,
p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
p_module =>'ei_pext');
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,NULL parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
,NULL parent_bo_code -- this is the grand parent bo, written as parent
, NULL parent_id -- this is grand parent id of acct roles, written as parent id
,'U' child_operation_flag
,'Y' populated_flag
,t.LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
FROM HZ_BUS_OBJ_TRACKING t
WHERE CHILD_ENTITY_NAME = 'HZ_PER_PROFILES_EXT_VL'
AND PARENT_BO_CODE = 'PERSON'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND acrrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'HZ_ORG_CONTACTS' parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
,'ORG_CONTACT' parent_bo_code -- this is the grand parent bo, written as parent
, oc.org_contact_id parent_id -- this is grand parent id of acct roles, written as parent id
,'U' child_operation_flag
,'Y' populated_flag
,t.LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
FROM HZ_BUS_OBJ_TRACKING t, hz_relationships r, hz_org_contacts oc
WHERE r.subject_id = PARENT_ID
AND oc.party_relationship_id = r.relationship_id
AND r.subject_type ='PERSON'
AND r.object_type = 'ORGANIZATION'
AND CHILD_ENTITY_NAME = 'HZ_PER_PROFILES_EXT_VL'
AND PARENT_BO_CODE = 'PERSON_CONTACT'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND acrrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
INSERT INTO HZ_BUS_OBJ_TRACKING (
CHILD_ID
,CHILD_BO_CODE
,CHILD_ENTITY_NAME
,CHILD_OPERATION_FLAG
,POPULATED_FLAG
,PARENT_ID
,PARENT_ENTITY_NAME
,PARENT_BO_CODE
,LAST_UPDATE_DATE
,CREATION_DATE)
SELECT DISTINCT
p.CHILD_ID
,p.CHILD_BO_CODE
,p.CHILD_ENTITY_NAME
,p.CHILD_OPERATION_FLAG
,p.POPULATED_FLAG
,p.PARENT_ID
,p.PARENT_ENTITY_NAME
,'PERSON_CONTACT' PARENT_BO_CODE
,p.LAST_UPDATE_DATE
,p.CREATION_DATE
FROM
(SELECT
CHILD_ID
,CHILD_BO_CODE
,CHILD_ENTITY_NAME
,CHILD_OPERATION_FLAG
,POPULATED_FLAG
,PARENT_ID
,PARENT_ENTITY_NAME
,LAST_UPDATE_DATE
,CREATION_DATE
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_PERSON_PROFILES'
AND parent_bo_code = 'PERSON'
AND event_id IS NULL) p, hz_org_contacts oc, hz_relationships r
WHERE p.PARENT_ID = r.subject_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION'
AND r.relationship_id = oc.party_relationship_id
AND NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the records that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = p.CHILD_ENTITY_NAME
AND bot.CHILD_ID = p.child_id
AND bot.PARENT_ID = p.PARENT_ID
AND bot.PARENT_BO_CODE = 'PERSON_CONTACT');
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the parents duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_PERSON_PROFILES,
child_bo: NULL, parent_bo: Person_Contact, parent_entity_name: HZ_PARTIES,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_PARTIES
parent_bo aliased as child_bo: Person_Contact
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_ORG_CONTACTS
grand_parent_bo aliased as parent_bo: Org_Contact
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_PARTITES,
child_bo: Person_Contact, parent_bo: Org_Contact, parent_entity_name: HZ_ORG_CONTACTS,
parent_id: 456
Note: Two separate SQLs insert statements are necessary as a person can exist
multiple times as an org_contact. So, when getting the org_contact_id,
SQL will return the multiple rows. This makes is impossible to write
it in one single SQL with CASE statements.
The following SQL is to generate all the parent and grand parent combination for
person_profile entity which is a child of PERSON BO
*/
IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.DEBUG
(p_message=>'pop missing lnks for per prof',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure,
p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
p_module =>'ei_pp');
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,NULL parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
,NULL parent_bo_code -- this is the grand parent bo, written as parent
, NULL parent_id -- this is grand parent id of acct roles, written as parent id
,'U' child_operation_flag
,'Y' populated_flag
,t.LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY t.LAST_UPDATE_DATE, child_id) as pprank
FROM HZ_BUS_OBJ_TRACKING t
WHERE CHILD_ENTITY_NAME = 'HZ_PERSON_PROFILES'
AND PARENT_BO_CODE = 'PERSON'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND pprank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'HZ_ORG_CONTACTS' parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
,'ORG_CONTACT' parent_bo_code -- this is the grand parent bo, written as parent
, oc.org_contact_id parent_id -- this is grand parent id of acct roles, written as parent id
,'U' child_operation_flag
,'Y' populated_flag
,t.LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY t.LAST_UPDATE_DATE, child_id) as pprank
FROM HZ_BUS_OBJ_TRACKING t, hz_relationships r, hz_org_contacts oc
WHERE r.subject_id = PARENT_ID
AND oc.party_relationship_id = r.relationship_id
AND r.subject_type ='PERSON'
AND r.object_type = 'ORGANIZATION'
AND CHILD_ENTITY_NAME = 'HZ_PERSON_PROFILES'
AND PARENT_BO_CODE = 'PERSON_CONTACT'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND pprank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the parents duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_PARTY_SITE_USES,
child_bo: NULL, parent_bo: PS, parent_entity_name: HZ_PARTY_SITES,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_PARTY_SITES
parent_bo aliased as child_bo: PARTY_SITE
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_ORG_CONTACTS
grand_parent_bo aliased as parent_bo: Org_Contact
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_PARTY_SITES,
child_bo: PARTY_SITE, parent_bo: Org_Contact, parent_entity_name: HZ_ORG_CONTACTS,
parent_id: 456
6. It is non-trivial to figure out the business object codes for both parent
and grand parent, grand parent identifier or grand parent entity name.
To do this, "inner select" uses case statement on parent_entity_name.
Some times, an embedded SQL is necessary to fgure out this.
Example:
Child is HZ_PARTY_SITE_USES.
Parent is PARTY_SITE and it's parent is Org_Contact.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY LAST_UPDATE_DATE, child_id) as psurank
,CASE PARENT_BO_CODE
WHEN 'PARTY_SITE' THEN
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
WHEN 'PERSON' THEN 'HZ_PARTIES'
WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
ELSE NULL
END
FROM HZ_PARTIES
WHERE party_id = (SELECT ps.party_id
FROM HZ_PARTY_SITES ps
WHERE ps.party_site_id = PARENT_ID))
ELSE NULL
END parent_entity_name -- this is grand parent tbl name of Person extn - written as parent entity name
,CASE PARENT_BO_CODE
WHEN 'PARTY_SITE' THEN
(SELECT
CASE p.party_type
WHEN 'ORGANIZATION' THEN p.party_id
WHEN 'PERSON' THEN p.party_id
WHEN 'PARTY_RELATIONSHIP' THEN
(SELECT oc.org_contact_id
FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
WHERE r.relationship_id = oc.party_relationship_id
AND r.party_id = p.party_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION')
ELSE NULL
END
FROM hz_parties p
WHERE p.party_id = (select ps.party_id
from HZ_PARTY_SITES ps
where ps.party_site_id = PARENT_ID)
)
ELSE NULL
END parent_id -- this is the grand parent id of Person extn - written as parent
,CASE PARENT_BO_CODE
WHEN 'PARTY_SITE' THEN
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'ORG'
WHEN 'PERSON' THEN 'PERSON'
WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
ELSE NULL
END
FROM hz_parties
WHERE party_id = (SELECT party_id
FROM HZ_PARTY_SITES
WHERE party_site_id = PARENT_ID)
)
ELSE NULL
END parent_bo_code -- this is the grand parent bo, written as parent
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_PARTY_SITE_USES'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND psurank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_PARTY_SITES,
child_bo: PS, parent_bo: OrgContact, parent_entity_name: OC,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_ORG_CONTACTS
parent_bo aliased as child_bo: OrgContact
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_PARTIES
grand_parent_bo aliased as parent_bo: Org
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_ORG_CONTACTS,
child_bo: OrgContact, parent_bo: Org, parent_entity_name: HZ_PARTIES,
parent_id: 456
6. It is non-trivial to figure out the business object codes for both parent
and grand parent, grand parent identifier or grand parent entity name.
To do this, "inner select" uses case statement on parent_entity_name.
Some times, an embedded SQL is necessary to fgure out this.
Example:
Child is HZ_PARTY_SITES.
Parent is OrgContact and it's parent is an Org.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY LAST_UPDATE_DATE, child_id) as psrank
,CASE PARENT_BO_CODE
WHEN 'ORG' THEN NULL
WHEN 'PERSON' THEN NULL
WHEN 'ORG_CONTACT' THEN 'HZ_PARTIES'
ELSE NULL
END parent_entity_name -- this is grand parent tbl name of PS, written as parent entity name
,CASE PARENT_BO_CODE
WHEN 'ORG' THEN NULL
WHEN 'PERSON' THEN NULL
WHEN 'ORG_CONTACT' THEN
(SELECT r.object_id
FROM hz_relationships r, hz_org_contacts oc
WHERE oc.org_contact_id = PARENT_ID
AND oc.party_relationship_id = r.relationship_id
AND subject_type ='PERSON'
AND object_type = 'ORGANIZATION')
ELSE NULL
END parent_id -- this is grand parent id of PS, written as parent id
,CASE PARENT_BO_CODE
WHEN 'ORG' THEN NULL
WHEN 'PERSON' THEN NULL
WHEN 'ORG_CONTACT' THEN 'ORG'
ELSE NULL
END parent_bo_code -- this is the grand parent bo, written as parent
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_PARTY_SITES'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND psrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL ;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY LAST_UPDATE_DATE, child_id) as ocrank
, NULL parent_entity_name -- this is grand parent tbl name of PS, written as parent entity name
, NULL parent_id -- this is grand parent id of PS, written as parent id
, NULL parent_bo_code -- this is the grand parent bo, written as parent
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_ORG_CONTACTS'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND ocrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL ;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the parents duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
t.PARENT_ENTITY_NAME child_entity_name
,t.PARENT_ID child_id
,t.PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,t.LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_ENTITY_NAME, PARENT_ID
ORDER BY t.LAST_UPDATE_DATE, child_id) as locxrank
,'HZ_PARTY_SITES' parent_entity_name -- this is grand parent tbl name of CP - written as parent entity name
, 'PARTY_SITE' parent_bo_code -- this is the grand parent bo, written as parent
, ps.party_site_id parent_id
FROM HZ_BUS_OBJ_TRACKING t, hz_party_sites ps
WHERE t.CHILD_ENTITY_NAME = 'HZ_LOCATIONS_EXT_VL'
AND t.event_id IS NULL
AND t.parent_id = ps.location_id) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE
AND bot.parent_id = temp.parent_id)
AND locxrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the parents duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_LOCATIONS,
child_bo: LOCATION, parent_bo: PS, parent_entity_name: HZ_PARTY_SITES,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_PARTY_SITES
parent_bo aliased as child_bo: PARTY_SITE
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_ORG_CONTACTS
grand_parent_bo aliased as parent_bo: Org_Contact
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_PARTY_SITES,
child_bo: PARTY_SITE, parent_bo: Org_Contact, parent_entity_name: HZ_ORG_CONTACTS,
parent_id: 456
6. It is non-trivial to figure out the business object codes for both parent
and grand parent, grand parent identifier or grand parent entity name.
To do this, "inner select" uses case statement on parent_entity_name.
Some times, an embedded SQL is necessary to fgure out this.
Example:
Child is HZ_LOCATIONS.
Parent is PARTY_SITE and it's parent is Org_Contact.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY LAST_UPDATE_DATE, child_id) as psurank
,CASE PARENT_BO_CODE
WHEN 'PARTY_SITE' THEN
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'HZ_PARTIES'
WHEN 'PERSON' THEN 'HZ_PARTIES'
WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS'
ELSE NULL
END
FROM HZ_PARTIES
WHERE party_id = (SELECT ps.party_id
FROM HZ_PARTY_SITES ps
WHERE ps.party_site_id = PARENT_ID))
ELSE NULL
END parent_entity_name -- this is grand parent tbl name of Person extn - written as parent entity name
,CASE PARENT_BO_CODE
WHEN 'PARTY_SITE' THEN
(SELECT
CASE p.party_type
WHEN 'ORGANIZATION' THEN p.party_id
WHEN 'PERSON' THEN p.party_id
WHEN 'PARTY_RELATIONSHIP' THEN
(SELECT oc.org_contact_id
FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
WHERE r.relationship_id = oc.party_relationship_id
AND r.party_id = p.party_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION')
ELSE NULL
END
FROM hz_parties p
WHERE p.party_id = (select ps.party_id
from HZ_PARTY_SITES ps
where ps.party_site_id = PARENT_ID)
)
ELSE NULL
END parent_id -- this is the grand parent id of Person extn - written as parent
,CASE PARENT_BO_CODE
WHEN 'PARTY_SITE' THEN
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'ORG'
WHEN 'PERSON' THEN 'PERSON'
WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
ELSE NULL
END
FROM hz_parties
WHERE party_id = (SELECT party_id
FROM HZ_PARTY_SITES
WHERE party_site_id = PARENT_ID)
)
ELSE NULL
END parent_bo_code -- this is the grand parent bo, written as parent
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_LOCATIONS'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND psurank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL ;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_CUST_SITE_USES_ALL,
child_bo: CUST_ACCT_SITE_USE, parent_bo: CUST_ACCT_SITE, parent_entity_name: HZ_CUST_ACCT_SITES_ALL,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_CUST_ACCT_SITES_ALL
parent_bo aliased as child_bo: CUST_ACCT_SITE
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_CUST_ACCOUNTS
grand_parent_bo aliased as parent_bo: CUST_ACCT
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_CUST_ACCT_SITES_ALL,
child_bo: CUST_ACCT_SITE, parent_bo: CUST_ACCT, parent_entity_name: HZ_CUST_ACCOUNTS,
parent_id: 456
6. It is non-trivial to figure out the business object codes for both parent
and grand parent, grand parent identifier or grand parent entity name.
To do this, "inner select" uses case statement on parent_entity_name.
Some times, an embedded SQL is necessary to fgure out this.
Example:
Child is CUST_ACCT_SITE.
Parent is CUST_ACCT and it's parent is ORG_CUST.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'HZ_CUST_ACCOUNTS' parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
,'CUST_ACCT' parent_bo_code -- this is the grand parent bo, written as parent
, CUST_ACCOUNT_ID parent_id -- this is grand parent id of acct roles, written as parent id
,'U' child_operation_flag
,'Y' populated_flag
,t.LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
FROM HZ_BUS_OBJ_TRACKING t, HZ_CUST_ACCT_SITES_ALL s
WHERE t.CHILD_ENTITY_NAME = 'HZ_CUST_SITE_USES_ALL'
AND t.event_id IS NULL
AND t.parent_id = s.CUST_ACCT_SITE_ID) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND acrrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_CUST_ACCT_SITES_ALL,
child_bo: CUST_ACCT_SITE, parent_bo: CUST_ACCT, parent_entity_name: HZ_CUST_ACCOUNTS,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_CUST_ACCOUNTS
parent_bo aliased as child_bo: CUST_ACCT
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_PARTIES
grand_parent_bo aliased as parent_bo: ORG_CUST
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_CUST_ACCOUNTS,
child_bo: CUST_ACCT, parent_bo: ORG_CUST, pa1rent_entity_name: HZ_PARTIES,
parent_id: 456
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'HZ_PARTIES' parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
,DECODE(P.PARTY_TYPE, 'ORGANIZATION','ORG_CUST','PERSON','PERSON_CUST', NULL) parent_bo_code -- this is the grand parent bo, written as parent
, ac.party_id parent_id -- this is grand parent id of acct roles, written as parent id
,'U' child_operation_flag
,'Y' populated_flag
,t.LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
FROM HZ_BUS_OBJ_TRACKING t, HZ_CUST_ACCOUNTS ac, HZ_PARTIES p
WHERE t.CHILD_ENTITY_NAME = 'HZ_CUST_ACCT_SITES_ALL'
AND t.event_id IS NULL
AND t.parent_id = ac.CUST_ACCOUNT_ID
AND ac.party_id = p.party_id) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND acrrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_CUST_ACCOUNT_ROLES,
child_bo: CUST_ACCT_CONTACT, parent_bo: CUST_ACCT_SITE, parent_entity_name: HZ_CUST_ACCT_SITES_ALL,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_CUST_ACCT_SITES_ALL
parent_bo aliased as child_bo: CUST_ACCT_SITE
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_CUST_ACCOUNTS
grand_parent_bo aliased as parent_bo: CUST_ACCT
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_CUST_ACCT_SITES_ALL,
child_bo: CUST_ACCT_SITE, parent_bo: CUST_ACCT, parent_entity_name: HZ_CUST_ACCOUNTS,
parent_id: 456
6. It is non-trivial to figure out the business object codes for both parent
and grand parent, grand parent identifier or grand parent entity name.
To do this, "inner select" uses case statement on parent_entity_name.
Some times, an embedded SQL is necessary to fgure out this.
Example:
Child is HZ_CUST_ACCOUNT_ROLES.
Parent is CUST_ACCT_SITE and it's parent is CUST_ACCT.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY LAST_UPDATE_DATE, child_id) as acrrank
,CASE PARENT_BO_CODE
WHEN 'CUST_ACCT_SITE' THEN 'HZ_CUST_ACCOUNTS'
WHEN 'CUST_ACCT' THEN 'HZ_PARTIES'
ELSE NULL
END parent_entity_name -- this is grand parent tbl name of acct roles, written as parent entity name
,CASE PARENT_BO_CODE
WHEN 'CUST_ACCT_SITE' THEN
(SELECT CUST_ACCOUNT_ID
FROM HZ_CUST_ACCT_SITES_ALL
WHERE CUST_ACCT_SITE_ID = PARENT_ID)
WHEN 'CUST_ACCT' THEN
(SELECT PARTY_ID
FROM HZ_CUST_ACCOUNTS
WHERE CUST_ACCOUNT_ID = PARENT_ID)
ELSE NULL
END parent_id -- this is grand parent id of acct roles, written as parent id
,CASE PARENT_BO_CODE
WHEN 'CUST_ACCT_SITE' THEN 'CUST_ACCT'
WHEN 'CUST_ACCT' THEN
(SELECT
CASE p.party_type
WHEN 'ORGANIZATION' THEN 'ORG_CUST'
WHEN 'PERSON' THEN 'PERSON_CUST'
ELSE NULL
END
FROM hz_parties p, HZ_CUST_ACCOUNTS c
WHERE p.party_id = c.party_id
AND c.CUST_ACCOUNT_ID = PARENT_ID)
ELSE NULL
END parent_bo_code -- this is the grand parent bo, written as parent
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_CUST_ACCOUNT_ROLES'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND acrrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_CUST_ACCOUNTS,
child_bo: CUST_ACCT, parent_bo: ORG_CUST_ACCT, parent_entity_name: HZ_PARTIES,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_PARTIES
parent_bo aliased as child_bo: ORG_CUST
grand_parent_id aliased as parent id: null
grand_parent_entity_name aliased as parent_entity_name: null
grand_parent_bo aliased as parent_bo: null
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_PARTIES,
child_bo: ORG_CUST, parent_bo: NULL, pa1rent_entity_name: NULL,
parent_id: NULL
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,NULL parent_entity_name -- this is grand parent tbl name of acct site use, written as parent entity name
,NULL parent_bo_code -- this is the grand parent bo, written as parent
, NULL parent_id -- this is grand parent id of acct roles, written as parent id
,'U' child_operation_flag
,'Y' populated_flag
,t.LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY t.LAST_UPDATE_DATE, child_id) as acrrank
FROM HZ_BUS_OBJ_TRACKING t
WHERE t.CHILD_ENTITY_NAME = 'HZ_CUST_ACCOUNTS'
AND t.event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND acrrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
/* A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_CUSTOMER_PROFILES,
child_bo: CUST_PROFILE, parent_bo: CUST_ACCT_SITE_USE, parent_entity_name: HZ_CUST_ACCT_SITE_USES_ALL,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_CUST_ACCT_SITE_USES_ALL
parent_bo aliased as child_bo: CUST_ACCT_SITE_USE
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_CUST_ACCT_SITES_ALL
grand_parent_bo aliased as parent_bo: CUST_ACCT_SITE
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_CUST_ACCT_SITE_USES_ALL,
child_bo: CUST_ACCT_SITE_USE, parent_bo: CUST_ACCT_SITE, parent_entity_name: HZ_CUST_ACCT_SITES_ALL,
parent_id: 456
6. It is non-trivial to figure out the business object codes for both parent
and grand parent, grand parent identifier or grand parent entity name.
To do this, "inner select" uses case statement on parent_entity_name.
Some times, an embedded SQL is necessary to fgure out this.
Example:
Child is HZ_CUSTOMER_PROFILES.
Parent is CUST_ACCT_SITE_USE and it's parent is CUST_ACCT_SITE.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_BO_CODE, PARENT_ID
ORDER BY LAST_UPDATE_DATE, child_id) as acrrank
,CASE PARENT_BO_CODE
WHEN 'CUST_ACCT_SITE_USE' THEN 'HZ_CUST_ACCT_SITES_ALL'
WHEN 'CUST_ACCT' THEN 'HZ_PARTIES'
ELSE NULL
END parent_entity_name -- this is grand parent tbl name of acct roles, written as parent entity name
,CASE PARENT_BO_CODE
WHEN 'CUST_ACCT_SITE_USE' THEN
(SELECT CUST_ACCT_SITE_ID
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID = PARENT_ID)
WHEN 'CUST_ACCT' THEN
(SELECT PARTY_ID
FROM HZ_CUST_ACCOUNTS
WHERE CUST_ACCOUNT_ID = PARENT_ID)
ELSE NULL
END parent_id -- this is grand parent id of acct roles, written as parent id
,CASE PARENT_BO_CODE
WHEN 'CUST_ACCT_SITE_USE' THEN 'CUST_ACCT_SITE'
WHEN 'CUST_ACCT' THEN
(SELECT
CASE p.party_type
WHEN 'ORGANIZATION' THEN 'ORG_CUST'
WHEN 'PERSON' THEN 'PERSON_CUST'
ELSE NULL
END
FROM hz_parties p, HZ_CUST_ACCOUNTS c
WHERE p.party_id = c.party_id
AND c.CUST_ACCOUNT_ID = PARENT_ID)
ELSE NULL
END parent_bo_code -- this is the grand parent bo, written as parent
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_CUSTOMER_PROFILES'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND acrrank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL ;
A note on the Structure of the insert statement
1. The parent record is tracked by the insert statement.
2. The "inner select" acts as a data source for "outer select"
3. The "outer select" uses "select to exclude the parents
that were already written to BOT"
4. There may be duplicate rows for any given parent retured by the "inner select".
This is because, in a given execution of the following SQL,
there may be multiple children for a given parent.
Each child contributes in getting its parent. This is as per the design
of "inner select".
To avoid duplicate rows of a parent returned by the siblings,
the inner select ranks all the parents duplicate parents.
The "outer select" filters on parents with rank = 1.
This clause helps to filter out the duplicate parent rows,
before data was inserted by insert statement.
5. The "inner select" is operating on the child record and trying to identify
the parent and grand parent information.
The parent information of the child record will be child
(current/its) information for the parent record.
The grand parent information of the child record will be parent info
of the parent record.
Because of this reason, "inner select" statement aliases the columns.
example:
child record:
child_id: 123, child_entity_name: HZ_CONTACT_POINTS,
child_bo: Phone, parent_bo: PS, parent_entity_name: PS,
parent_id: 234
The "inner select" fetches above record from BOT and identifies
its parent, grand parent information and present it as follows:
parent_id aliased as child_id: 234
parent_entity_name aliased as child_entity_name: HZ_PARTY_SITES
parent_bo aliased as child_bo: PS
grand_parent_id aliased as parent id: 456
grand_parent_entity_name aliased as parent_entity_name: HZ_PARTIES
grand_parent_bo aliased as parent_bo: Org
Insert statement will take this result and write it as
child record:
child_id: 234, child_entity_name: HZ_PARTY_SITES,
child_bo: PS, parent_bo: Org, parent_entity_name: HZ_PARTIES,
parent_id: 456
6. It is non-trivial to figure out the business object codes for both parent
and grand parent, grand parent identifier or grand parent entity name.
To do this, "inner select" uses case statement on parent_entity_name.
Some times, an embedded SQL is necessary to fgure out this.
Example:
Child is HZ_CONTACT_POINTS.
Parent is PS and it's parent is Party.
To figure out the grand parent bo code, SQL is necessary to run against
HZ_PARTIES to figure out the PARTY_TYPE based on PARTY_ID of the
HZ_PARTY_SITES table.
*/
INSERT INTO HZ_BUS_OBJ_TRACKING
(CHILD_ENTITY_NAME, CHILD_ID,
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, CREATION_DATE)
SELECT CHILD_ENTITY_NAME, CHILD_ID, -- outer select
CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, PARENT_ENTITY_NAME,
PARENT_ID, PARENT_BO_CODE, G_CREATION_DATE CREATION_DATE
FROM
(SELECT -- inner select
PARENT_ENTITY_NAME child_entity_name
,PARENT_ID child_id
,PARENT_BO_CODE CHILD_BO_CODE
,'U' child_operation_flag
,'Y' populated_flag
,LAST_UPDATE_DATE
,RANK()
OVER (PARTITION BY PARENT_ENTITY_NAME, PARENT_ID
ORDER BY LAST_UPDATE_DATE, child_id) as cprank
,CASE PARENT_BO_CODE
WHEN 'ORG' THEN NULL -- identify the GrandParentEntityName when Org is parentBO of CP
WHEN 'PERSON' THEN NULL -- identify the GrandParentEntityName when Per is parentBO of CP
WHEN 'ORG_CONTACT' THEN -- identify the GrandParentEntityName when orgContact is parentBO of CP
'HZ_PARTIES'
WHEN 'PARTY_SITE' THEN -- identify the GrandParentEntityName when PS is parentBO of CP
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'HZ_PARTIES' -- identify the GrandParentEntityName when PS is parentBO of CP, when Org is parentOf PS
WHEN 'PERSON' THEN 'HZ_PARTIES' -- identify the GrandParentEntityName when PS is parentBO of CP, when Per is parentOf PS
WHEN 'PARTY_RELATIONSHIP' THEN 'HZ_ORG_CONTACTS' -- identify the GrandParentEntityName when PS is parentBO of CP, when orgContact is parentOf PS
ELSE NULL
END
FROM HZ_PARTIES
WHERE party_id = (SELECT ps.party_id
FROM HZ_PARTY_SITES ps
WHERE ps.party_site_id = PARENT_ID))
ELSE NULL
END parent_entity_name -- this is grand parent tbl name of CP - written as parent entity name
,CASE PARENT_BO_CODE -- to identify the grandParentEntityId of CP
WHEN 'ORG' THEN NULL -- to identify the grandParentEntityId of CP when Org is parentOf CP
WHEN 'PERSON' THEN NULL -- to identify the grandParentEntityId of CP when Per is parentOf CP
WHEN 'ORG_CONTACT' THEN -- to identify the grandParentEntityId of CP when Rel is parentOf CP
(SELECT r.object_id
FROM hz_relationships r, hz_org_contacts oc
WHERE oc.org_contact_id = PARENT_ID
AND oc.party_relationship_id = r.relationship_id
AND subject_type ='PERSON'
AND object_type = 'ORGANIZATION')
WHEN 'PARTY_SITE' THEN -- to identify the grandParentEntityId of CP when PS is parentOf CP
(SELECT
CASE p.party_type
WHEN 'ORGANIZATION' THEN p.party_id -- to identify the grandParentEntityId of CP when PS is parentOf CP, when Org is parentOF PS
WHEN 'PERSON' THEN p.party_id -- to identify the grandParentEntityId of CP when PS is parentOf CP, when Per is parentOF PS
WHEN 'PARTY_RELATIONSHIP' THEN -- to identify the grandParentEntityId of CP when PS is parentOf CP, when Rel is parentOF PS
(SELECT oc.org_contact_id
FROM hz_org_contacts oc, HZ_RELATIONSHIPS r
WHERE r.relationship_id = oc.party_relationship_id
AND r.party_id = p.party_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION')
ELSE NULL
END
FROM hz_parties p
WHERE p.party_id = (select ps.party_id
from HZ_PARTY_SITES ps
where ps.party_site_id = PARENT_ID))
ELSE NULL
END parent_id -- this is the grand parent id of cont pref - written as parent
,CASE PARENT_BO_CODE -- to identify the grandParentBO of CP
WHEN 'ORG' THEN NULL -- to identify the grandParentBO of CP when Org is Parent
WHEN 'PERSON' THEN NULL -- to identify the grandParentBO of CP when Per is Parent
WHEN 'ORG_CONTACT' THEN 'ORG' -- to identify the grandParentBO of CP when OrgConatct is Parent
WHEN 'PARTY_SITE' THEN -- to identify the grandParentBO of CP when PS is Parent
(SELECT
CASE party_type
WHEN 'ORGANIZATION' THEN 'ORG'
WHEN 'PERSON' THEN 'PERSON'
WHEN 'PARTY_RELATIONSHIP' THEN 'ORG_CONTACT'
ELSE NULL
END
FROM hz_parties
WHERE party_id = (SELECT party_id
FROM HZ_PARTY_SITES
WHERE party_site_id = PARENT_ID))
ELSE NULL
END parent_bo_code -- this is the grand parent bo, written as parent
FROM HZ_BUS_OBJ_TRACKING
WHERE CHILD_ENTITY_NAME = 'HZ_CONTACT_POINTS'
AND event_id IS NULL) temp
WHERE NOT EXISTS
(SELECT 1 FROM HZ_BUS_OBJ_TRACKING bot --select to exclude the parents that were already written to BOT
WHERE bot.event_id IS NULL
AND bot.CHILD_ENTITY_NAME = temp.CHILD_ENTITY_NAME
AND bot.CHILD_ID = temp.child_id
AND bot.CHILD_BO_CODE = temp.CHILD_BO_CODE)
AND cprank = 1
AND temp.child_id IS NOT NULL
AND temp.CHILD_ENTITY_NAME IS NOT NULL
AND temp.CHILD_BO_CODE IS NOT NULL;
Called By: This is called from BOD Update API.
Note: Based on the response from this function, BOD Update API will
decide whether to update the BO version of the top level BO.
*/
----------------------------------------------------------------------
procedure entity_in_bo (p_bo_code IN VARCHAR2,
p_ebo_code IN VARCHAR2,
p_child_bo_code IN VARCHAR2,
p_entity_name IN VARCHAR2,
x_return_status OUT NOCOPY BOOLEAN) IS
CURSOR c_chk_entity (c_bo_code IN VARCHAR2,
c_entity_name IN VARCHAR2,
c_node_path IN VARCHAR2) IS
SELECT 1
FROM (
SELECT BUSINESS_OBJECT_CODE bo_code, CHILD_BO_CODE,ENTITY_NAME,
sys_connect_by_path(BUSINESS_OBJECT_CODE, '/') node_path,
CONNECT_BY_ISLEAF isleaf
FROM hz_bus_obj_definitions
START WITH BUSINESS_OBJECT_CODE = c_bo_code
CONNECT BY PRIOR CHILD_BO_CODE = BUSINESS_OBJECT_CODE
)
WHERE
isleaf = 1
AND entity_name = c_entity_name
AND node_path LIKE c_node_path
AND ROWNUM <2;
and update the current record with BO codes etc.
This must be done in order to reach the root node of Org, Person,
Org Cust and Person Cust BOs.
Called From: This is called from HZ_BES_BO_RAISE_PKG
Called By: bes_main()
*/
-----------------------------------------------------------------
PROCEDURE populate_missing_links
( p_creation_date IN DATE )IS
l_debug_prefix VARCHAR2(30) := 'EXP_HZ_TBLS:'; -- explode hz tables
-- avoid passing the parameter for each insert procedure.
-- Each of the insert procedures, need to populate the BOT creation_date
-- with the value the p_creation_date value.
G_CREATION_DATE := p_creation_date;
/* A note on insert and update procedures.
Insert Procedures:
The functional reason for these insert procedures to exist is to identify
and track the parent of each child entity. The parent record is
needed in order for the following functions to work properly.
1. Business Object Completeness Check
2. To identify Business Object Event Type - whether to raise create or update event
3. To extract the Business Object
In all the three functionalities above will only have knowledge of
Business Object root node. To get to the leaf node, all the intermediate
nodes must be present in the BOT table. Hence this insert procedures.
Update Procedures:
In order to link the parent record and child record, it is mandatory for
the child record to contain the information for parent in addition to
having its (childs) information in it.
Only when both parent record (insert procedure) and child record contains
parent info (update procedure), it is possible to sucessfuly do
the three functions mentioned above.
*/
/*
Note 1.
ONLY following entities are exploded as part of the concurrent program.
Remaing entities are handled by the populate functions.
Note 2.
As populate function write the PARENT_BO_CODE as part of populating the
child record, there is no need of calling any update procedures
as described earlier. Only insert procedures are called.
1. HZ_CONTACT_PREFERENCES
2. HZ_PER_PROFILES_EXT_VL
3. HZ_PERSON_PROFILES
4. HZ_PARTY_SITE_USES
5. HZ_CONTACT_POINTS
6. HZ_PARTY_SITES_EXT_VL
7. HZ_LOCATIONS_EXT_VL
8. HZ_LOCATIONS
9. HZ_PARTY_SITES
10. HZ_ORG_CONTACTS
11. HZ_ORG_PROFILES_EXT_VL
12. HZ_CUST_ACCOUNT_ROLES
13. HZ_CUSTOMER_PROFILES
14. HZ_CUST_SITE_USES_ALL
15. HZ_CUST_ACCT_SITES_ALL
16. HZ_CUST_ACCOUNTS
*/
-- Insert the parent record
-- 1. HZ_CONTACT_PREFERENCES
/* LOG(
message => 'bfr calling ei_HZ_CONTACT_PREFERENCES()',
p_prefix =>l_debug_prefix,
p_module => l_module);
-- cursor to select the root node identifier (child_id) and the
-- appropriate event id.
CURSOR c_bulk_bo_gt (
cp_per_ins_evt_id IN NUMBER,
cp_per_upd_evt_id IN NUMBER,
cp_org_ins_evt_id IN NUMBER,
cp_org_upd_evt_id IN NUMBER,
cp_perc_ins_evt_id IN NUMBER,
cp_perc_upd_evt_id IN NUMBER,
cp_orgc_ins_evt_id IN NUMBER,
cp_orgc_upd_evt_id IN NUMBER) IS
SELECT t.child_id, tmp.bo_code,
CASE tmp.bo_code
WHEN 'PERSON' THEN
DECODE ( tmp.event_type_flag, 'U', cp_per_upd_evt_id, cp_per_ins_evt_id)
WHEN 'ORG' THEN
DECODE ( tmp.event_type_flag, 'U', cp_org_upd_evt_id, cp_org_ins_evt_id)
WHEN 'PERSON_CUST' THEN
DECODE ( tmp.event_type_flag, 'U', cp_perc_upd_evt_id, cp_perc_ins_evt_id)
WHEN 'ORG_CUST' THEN
DECODE ( tmp.event_type_flag, 'U', cp_orgc_upd_evt_id, cp_orgc_ins_evt_id)
END eventid
FROM HZ_BUS_OBJ_TRACKING t, hz_bes_gt tmp
WHERE t.child_id = tmp.party_id
AND t.child_bo_code = tmp.bo_code
AND t.child_entity_name = 'HZ_PARTIES'
AND t.parent_bo_code IS NULL
AND t.event_id IS NULL;
do forall update of on BOT using the retrun values
commit;
UPDATE HZ_BUS_OBJ_TRACKING
SET event_id = l_evtids(i)
WHERE ROWID IN (
SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
START WITH child_id = l_rtids(i)
AND child_entity_name = 'HZ_PARTIES'
AND parent_BO_CODE IS NULL
AND event_id IS NULL
AND CHILD_BO_CODE = l_bo_codes(i)
CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
AND PARENT_ID = PRIOR CHILD_ID
AND parent_bo_code = PRIOR child_bo_code) ;
UPDATE HZ_BUS_OBJ_TRACKING
SET event_id = p_evt_id
WHERE ROWID IN (
SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
START WITH child_id = p_child_id
AND child_entity_name = 'HZ_PARTIES'
AND parent_BO_CODE IS NULL
AND event_id IS NULL
AND CHILD_BO_CODE = p_child_bo_code
CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
AND PARENT_ID = PRIOR CHILD_ID
AND parent_bo_code = PRIOR child_bo_code) ;
p_creation_date IN DATE, -- used to update the creation_date column in BOT
p_evt_type IN VARCHAR2, -- this is to pass the event type (Bug4773627)
p_commit IN BOOLEAN, -- to control commit or rolllback when called from v3 api(Bug4957408)
p_per_ins_evt_id IN NUMBER,
p_per_upd_evt_id IN NUMBER,
p_org_ins_evt_id IN NUMBER,
p_org_upd_evt_id IN NUMBER,
p_perc_ins_evt_id IN NUMBER,
p_perc_upd_evt_id IN NUMBER,
p_orgc_ins_evt_id IN NUMBER,
p_orgc_upd_evt_id IN NUMBER) IS
-- cursor to select the root node identifier (child_id) and the
-- appropriate event id.
CURSOR c_bulk_bo_gt (
cp_per_ins_evt_id IN NUMBER,
cp_per_upd_evt_id IN NUMBER,
cp_org_ins_evt_id IN NUMBER,
cp_org_upd_evt_id IN NUMBER,
cp_perc_ins_evt_id IN NUMBER,
cp_perc_upd_evt_id IN NUMBER,
cp_orgc_ins_evt_id IN NUMBER,
cp_orgc_upd_evt_id IN NUMBER) IS
SELECT t.child_id, tmp.bo_code,
CASE tmp.bo_code
WHEN 'PERSON' THEN
DECODE ( tmp.event_type_flag, 'U', cp_per_upd_evt_id, cp_per_ins_evt_id)
WHEN 'ORG' THEN
DECODE ( tmp.event_type_flag, 'U', cp_org_upd_evt_id, cp_org_ins_evt_id)
WHEN 'PERSON_CUST' THEN
DECODE ( tmp.event_type_flag, 'U', cp_perc_upd_evt_id, cp_perc_ins_evt_id)
WHEN 'ORG_CUST' THEN
DECODE ( tmp.event_type_flag, 'U', cp_orgc_upd_evt_id, cp_orgc_ins_evt_id)
END eventid,
NVL(tmp.EVENT_TYPE_FLAG, 'C')
FROM HZ_BUS_OBJ_TRACKING t, hz_bes_gt tmp
WHERE t.child_id = tmp.party_id
AND t.child_bo_code = tmp.bo_code
AND t.child_entity_name = 'HZ_PARTIES'
AND t.parent_bo_code IS NULL
AND t.event_id IS NULL;
SELECT NVL(tmp.EVENT_TYPE_FLAG, 'C')
from hz_bes_gt tmp
where tmp.bo_code = cp_bo_code
and tmp.party_id = cp_rt_node_id
and tmp.event_id = cp_evt_id;
do forall update of on BOT using the retrun values
commit;
UPDATE HZ_BUS_OBJ_TRACKING
SET event_id = l_evtids(i),
creation_date = p_creation_date,
PARENT_EVENT_FLAG = nvl2(PARENT_BO_CODE, NULL, l_evtTypes(i))
WHERE event_id is null and ROWID IN (
SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
START WITH child_id = l_rtids(i)
AND child_entity_name = 'HZ_PARTIES'
AND parent_BO_CODE IS NULL
AND event_id IS NULL
AND CHILD_BO_CODE = l_bo_codes(i)
CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
AND PARENT_ID = PRIOR CHILD_ID
AND parent_bo_code = PRIOR child_bo_code) ;
UPDATE HZ_BUS_OBJ_TRACKING
SET event_id = p_evt_id,
creation_date = p_creation_date,
PARENT_EVENT_FLAG = nvl2(PARENT_BO_CODE, NULL, p_evt_type)
WHERE event_id is null and ROWID IN (
SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
START WITH child_id = p_child_id
AND child_entity_name = 'HZ_PARTIES'
AND parent_BO_CODE IS NULL
AND event_id IS NULL
AND CHILD_BO_CODE = p_child_bo_code
CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
AND PARENT_ID = PRIOR CHILD_ID
AND parent_bo_code = PRIOR child_bo_code
AND event_id is null) ;
UPDATE HZ_BUS_OBJ_TRACKING
SET PARENT_EVENT_FLAG = l_evtTypes(1)
where event_id = p_evt_id
AND parent_BO_CODE IS NULL
AND CHILD_BO_CODE = p_child_bo_code
AND child_entity_name = 'HZ_PARTIES'
AND child_id = p_child_id;
Purpose: This procedure will update the hz_parties table with all the
latest bo_version_numbers. This must be done to short circuit the
process for figuring out event type when the object is touched next time.
Called From: This is called from HZ_BES_BO_RAISE_PKG
Called By: bes_main()
Input parameters:
p_per_bo_ver IN NUMBER -- for passing the person bo version number
p_org_bo_ver IN NUMBER -- for passing the org bo version number
p_perc_bo_ver IN NUMBER -- for passing the person cust bo version number
p_orgc_bo_ver IN NUMBER -- for passing the org cust bo version number
*/
----------------------------------------------------------------------------
PROCEDURE upd_hzp_bo_ver (
p_per_bo_ver IN NUMBER, -- for passing the person bo version number
p_org_bo_ver IN NUMBER, -- for passing the org bo version number
p_perc_bo_ver IN NUMBER, -- for passing the person cust bo version number
p_orgc_bo_ver IN NUMBER) IS -- for passing the org cust bo version number
-- local variables
l_debug_prefix VARCHAR2 (10):= 'updhzp';
Select all the rows from GT, from the list, update the hz_parties table for all the
rows that do not have correct BO version number
*/
IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.DEBUG
(p_message=>'upd_hzp_bo_ver()+',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure,
p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
p_module =>'updp');
UPDATE HZ_PARTIES
SET PERSON_BO_VERSION = p_per_bo_ver
WHERE party_id IN
(SELECT t.party_id
FROM HZ_BES_GT t
WHERE t.BO_CODE = 'PERSON')
AND (PERSON_BO_VERSION <> p_per_bo_ver
OR PERSON_BO_VERSION IS NULL) ;
UPDATE HZ_PARTIES
SET ORG_BO_VERSION = p_org_bo_ver
WHERE party_id IN
(SELECT t.party_id
FROM HZ_BES_GT t
WHERE t.BO_CODE = 'ORG')
AND (ORG_BO_VERSION <>p_org_bo_ver
OR ORG_BO_VERSION IS NULL);
UPDATE HZ_PARTIES
SET PERSON_CUST_BO_VERSION = p_perc_bo_ver
WHERE party_id IN
(SELECT t.party_id
FROM HZ_BES_GT t
WHERE t.BO_CODE = 'PERSON_CUST')
AND (PERSON_CUST_BO_VERSION <> p_perc_bo_ver
OR PERSON_CUST_BO_VERSION IS NULL);
UPDATE HZ_PARTIES
SET ORG_CUST_BO_VERSION = p_orgc_bo_ver
WHERE party_id IN
(SELECT t.party_id
FROM HZ_BES_GT t
WHERE t.BO_CODE = 'ORG_CUST')
AND (ORG_CUST_BO_VERSION <>p_orgc_bo_ver
OR ORG_CUST_BO_VERSION IS NULL);
Purpose: This procedure will delete the records that were already processed
by subscriptions.
Input parameters:
p_cutoff_dt IN DATE);-- for passing the cutoff date for deleting the recs in BOT
To delete the records that were identified for deletion.
Who Calls this procedure:
Cleanse concurrent program will call this procedure.
Logic:
Delete all the processed records that for which event is raised
before given date.
*/
IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.DEBUG
(p_message=>'del_bot()+',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure,
p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
p_module =>'del_bot');
DELETE FROM HZ_BUS_OBJ_TRACKING
WHERE event_id IS NOT NULL
AND creation_date < p_cutoff_dt;
records that are to be deleted.
Identify such records and delete them from BOT.
To be done. Srikanth Jan 24th 2006.
*/
COMMIT;
Identify such records and delete them from BOT.
To be done. Srikanth Jan 24th 2006.
*/
COMMIT;
row in TCA Schema and delete them.
This case works as an umbrella case wherein, all those records that
were not identified by CASE1, CASE2 are caught here.
The reason to do this as the last option instead of first or the
only option is, by taking care of first two cases, many of the
effected records may be deleted. Leaving only a handful of records to
be deleted for CASE3. This might enhance the performance.
TO be Done - Srikanth Jan 24th 2006
*/
IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.DEBUG
(p_message=>'del_bot()-',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure,
p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
p_module =>'del_bot');
selects duplicate records from BOT that were not yet processed and
deletes one of the rows (row with max rowid).
*/
----------------------------------------------------------------------------
PROCEDURE del_duplicate_rows IS
-- local variables
l_debug_prefix VARCHAR2(40) := 'DEL_DUPLICATE_ROWS2';
DELETE FROM HZ_BUS_OBJ_TRACKING
WHERE ROWID IN (
SELECT bo_row
FROM (
SELECT rowid bo_row,
RANK() over (PARTITION BY child_id, child_entity_name,
child_bo_code, parent_entity_name, parent_id, parent_bo_code
ORDER BY rowid) ROWRANK
FROM HZ_BUS_OBJ_TRACKING a
WHERE a.event_id IS NULL)
WHERE ROWRANK >1);
will delete the entire hierarchy.
This procedure is called from
1. party purge concurrent program
2. account merge concurrent program with delete option.
In these two cases, as the entire party and its detailed records are
purged, there is no use of maintaining those records in the BOT.
If the purged records are left in BOT without deleting:
1. There is a chance that an event may be raised for already
purged record. Functionally, this would be incorrect.
2. The Raise Events concurrent program may error out
as it cannot find the party record in TCA Registry.
This is needed for updating the party BO_VERSION columns to
be updated after raising the event.
Called By:
1. party purge concurrent program
2. account merge concurrent program with delete option.
Input:
BO Code: PERSON for Person BO,
ORG for Organization BO,
PERSON_CUST for Person Customer BO
ORG_CUST for Organization Customer BO
Object Identifier: Root Object Id (party id).
*/
procedure del_obj_hierarchy
( P_OBJ_ID IN NUMBER) IS
cursor c1_ptype (cp_party_id in number) is
select party_type
from hz_parties
where party_id = cp_party_id;
delete the Person and Person Customer hierarchies for a given partyId
-- This is because, the person bo might have corresponding
-- Person Customer BO in BOT
For BO code Org or Org Customers
delete the Org and Org Customer hierarchies for a given partyId
-- This is because, the Org bo might have corresponding
-- Org Customer BO in BOT
*/
IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.DEBUG
(p_message=>'del_obj_hierarchy+',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure,
p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
p_module =>l_module);
DELETE FROM HZ_BUS_OBJ_TRACKING
WHERE ROWID IN (
SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
START WITH
event_id IS NULL AND child_id = P_OBJ_ID AND CHILD_BO_CODE = 'PERSON' AND
child_entity_name = 'HZ_PARTIES' AND parent_id IS NULL AND
PARENT_ENTITY_NAME is NULL
CONNECT BY
PARENT_BO_CODE = PRIOR CHILD_BO_CODE AND PARENT_ID = PRIOR CHILD_ID);
DELETE FROM HZ_BUS_OBJ_TRACKING
WHERE ROWID IN (
SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
START WITH
event_id IS NULL AND child_id = P_OBJ_ID AND CHILD_BO_CODE = 'PERSON_CUST' AND
child_entity_name = 'HZ_PARTIES' AND parent_id IS NULL AND
PARENT_ENTITY_NAME is NULL
CONNECT BY
PARENT_BO_CODE = PRIOR CHILD_BO_CODE AND PARENT_ID = PRIOR CHILD_ID);
DELETE FROM HZ_BUS_OBJ_TRACKING
WHERE ROWID IN (
SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
START WITH
event_id IS NULL AND child_id = P_OBJ_ID AND CHILD_BO_CODE = 'ORG' AND
child_entity_name = 'HZ_PARTIES' AND parent_id IS NULL AND
PARENT_ENTITY_NAME is NULL
CONNECT BY
PARENT_BO_CODE = PRIOR CHILD_BO_CODE AND PARENT_ID = PRIOR CHILD_ID);
DELETE FROM HZ_BUS_OBJ_TRACKING
WHERE ROWID IN (
SELECT ROWID FROM HZ_BUS_OBJ_TRACKING
START WITH
event_id IS NULL AND child_id = P_OBJ_ID AND CHILD_BO_CODE = 'ORG_CUST' AND
child_entity_name = 'HZ_PARTIES' AND parent_id IS NULL AND
PARENT_ENTITY_NAME is NULL
CONNECT BY
PARENT_BO_CODE = PRIOR CHILD_BO_CODE AND PARENT_ID = PRIOR CHILD_ID);
(p_message=>'No data to delete',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure,
p_module_prefix => 'HZ_BES_BO_UTIL_PKG',
p_module =>l_module);
will delete the entire hierarchy.
This procedure is called from
1. party merge concurrent program
2. account merge concurrent program
In these two cases, the entity ids are changed in TCA REgistry by
the above concurrent programs.
This procedure reflects the id changes in the BOT.
This changed ids will enable the BO extraction API to get to the
action types of the changed entities in BOT.
Note - this method does not handle RA_CUST_RECEIPT_METHODS and
IBY_FNDCPT_PAYER_ASSGN_INSTR_V.
This method only handles update of identifiers for HZ tables.
Called By:
1. party merge concurrent program
2. account merge concurrent program
Input:
request id: Concurrent Request Identifier
*/
procedure upd_entity_ids
( P_request_id IN NUMBER) IS
-- cursor declaration
CURSOR c_enty_ids (cp_request_id IN NUMBER) IS
SELECT l.FROM_ENTITY_ID, l.TO_ENTITY_ID, d.entity_name
FROM hz_merge_party_log l, hz_merge_dictionary d
WHERE l.request_id = cp_request_id
AND l.MERGE_DICT_ID = d.MERGE_DICT_ID
AND d.entity_name LIKE 'HZ%'
AND l.to_entity_id IS NOT null
AND l.FROM_ENTITY_ID <> l.TO_ENTITY_ID
ORDER BY 3 desc;
2. then update the bot table
*/
OPEN c_enty_ids (P_request_id );
UPDATE HZ_BUS_OBJ_TRACKING
SET CHILD_ID = l_to_ids(i)
WHERE event_id IS NULL
AND CHILD_ENTITY_NAME = l_entity_names(i)
AND CHILD_ID = l_from_ids(i);
UPDATE HZ_BUS_OBJ_TRACKING
SET PARENT_ID = l_to_ids(i)
WHERE event_id IS NULL
AND PARENT_ENTITY_NAME = l_entity_names(i)
AND PARENT_ID = l_from_ids(i);