[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
. select and store the BO object versions from BOD
. delete the duplicate rows entered by populate function in BOT. Bug#4957408
. identify and populate missing links in BOT
. for each BO per/org/percust/orgcust
. select all the root nodes from the BOT for a give BO.
. In the same select, identify the nodes for updateBO event based on BO Version comparision.
(short circuting)
. Populate the Global Temporary (GT) Table (BO_CODE, ROOT_NODE_ID, EVENT_ID)
with the above results.
Note: event_id cannot be populated for bulk events (G_BLK_EVT_RAISE = Y).
. Select all the Person BO records from the GT table for which event type is not identified
. After checking the completeness of those BOs, delete the rest of the records
for which the event type is not specified and not complete.
. Figure out the event type for the root nodes for which
event type is null (BO is complete) from GT. In the same SQL identify the
Last Update Date applicable for each root node.
Update the GT with event type based on the above query.
. Select all the Org BO records from the GT table for which event type is not identified
. After checking the completeness of those BOs, delete the rest of the records
for which the event type is not specified and not complete.
. Figure out the event type for the root nodes for which
event type is null (BO is complete) from GT. In the same SQL identify the
Last Update Date applicable for each root node.
Update the GT with event type based on the above query.
. Populate all those Org/Person parties in GT(BO_CODE, ROOT_NODE_ID, EVENT_ID),
that are not current present as Org/Person Cust BOs in GT; but,
. Select all the Person Cust BO records from the GT table for which event type is not identified
. After checking the completeness of those BOs, delete the rest of the records
for which the event type is not specified and not complete.
. Figure out the event type for the root nodes for which
event type is null (BO is complete) from GT. In the same SQL identify the
Last Update Date applicable for each root node.
Update the GT with event type based on the above query.
. Select all the Org Cust BO records from the GT table for which event type is not identified
. After checking the completeness of those BOs, delete the rest of the records
for which the event type is not specified and not complete.
. Figure out the event type for the root nodes for which
event type is null (BO is complete) from GT. In the same SQL identify the
Last Update Date applicable for each root node.
Update the GT with event type based on the above query.
. populate the event parameter payload with event id (CDH_EVENT_ID).
. If the events must be raised in Bulk,
. Identify how many types of events to be raised.
This is done by doing existence check on GT for a given BO and
event type flag combination. Generate as needed, event ids.
. For Person-Insert Raise One Bulk event
. For Person-Update Raise One Bulk event
. For Org-Insert Raise One Bulk event
. For Org-Update Raise One Bulk event
. For Person-Cust-Insert Raise One Bulk event
. For Person-Cust-Update Raise One Bulk event
. For Org-Cust-Insert Raise One Bulk event
. For Org-Cust-Update Raise One Bulk event
. For all the eight events above, Update entire BOT hierarchy with
appropriate event_id.
. commit
. identify all the Person/Org Child BOs in Person/Org Cust BOs and
update the records with appropriate child event ids. -- this step is not done
as child_event_id is populated at the time of populating BOT (per/Org EBO rec)
. If the event must be raised per each BO
. Select all the GT contents, ordered by root_node_id (a.k.a. party_id).
This would select the all the parties with different BOs together.
. Per each party
. if BO = Person
. Raise the create/update event for Person BO
. Update entire BOT hierarchy with event_id
. store the person bo event_id for later use as child_event_id (per_child_evt_id).
. if BO = Person Cust
. Raise the create/update event for Person Cust BO
. Update entire BOT hierarchy with event_id
. Update the Person as child record child_event_id with per_child_evt_id
. if BO = Org
. Raise the create/update event for Org BO
. Update entire BOT hierarchy with event_id
. store the org bo event_id for later use as child_event_id (org_child_evt_id).
. if BO = Org Cust
. Raise the create/update event for OrgCust BO
. Update entire BOT hierarchy with event_id
. Update the Person as child record child_event_id with org_child_evt_id
. commit; -- As event raising already took place, we need to comminucate to
. Delete the records that were not part of any event from BOT.
This is based on creation_date being less that than the start time of the conc program.
some definitions:
Two kinds of the root nodes (parties) that must be evaluated for
completness check and eventtype().
1. First timers: Party records that are processed for the first time.
In other words, those party records for which BO_VERSION_NUMBER is null
on hz_parties table.
2. Second timers: Party records that were processed earlier; but the
SELECT bo_version_number
FROM hz_bus_obj_definitions
WHERE business_object_code = cp_bo_code
AND root_node_flag = 'Y';
SELECT event_id, party_id, bo_code, event_type_flag
FROM hz_bes_gt
ORDER BY bo_code asc ;
SELECT event_id, party_id, bo_code, event_type_flag
FROM hz_bes_gt
ORDER BY event_id, bo_code desc ;
SELECT event_id, party_id, event_type_flag
FROM hz_bes_gt
WHERE bo_code = cp_bo_code
ORDER BY event_type_flag, event_id, party_id desc ;
/* Cusror to figure out what types of bulk update events to be raised
*/
CURSOR c_chk_insevt (cp_bo_code VARCHAR2) IS
SELECT 'Y'
FROM hz_bes_gt
WHERE BO_CODE = cp_bo_code
AND event_type_flag IS NULL
AND ROWNUM <2;
/* Cusror to figure out what types of bulk insert events to be raised
*/
CURSOR c_chk_updevt (cp_bo_code VARCHAR2) IS
SELECT 'Y'
FROM hz_bes_gt
WHERE BO_CODE = cp_bo_code
AND event_type_flag = 'U'
AND ROWNUM <2;
SELECT event_id CDH_EVENT_ID,
child_id CDH_OBJECT_ID,
parent_event_flag event_type
FROM hz_bus_obj_tracking
WHERE event_id = cp_evtid
AND CHILD_BO_CODE = cp_bo_code
AND parent_BO_CODE IS NULL
ORDER BY 2 desc;
l_perbo_u_evtid NUMBER := 0; -- per bo update event id
l_percustbo_u_evtid NUMBER := 0; -- per cust bo update event id
l_orgcustbo_u_evtid NUMBER := 0; -- org cust bo update event id
-- select the bo version numbers for all the business objects
-- This is done irrespective of if the BO is enabled or not.
LOG('get BO versions from BOD');
The duplicate rows must be deleted to proceed further.
*/
LOG('delete duplicate rows from BOT');
To delete such records, this creation_date column is used.
*/
LOG('populate missing links');
SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_orgbo_c_evtid FROM dual;
SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_orgbo_u_evtid FROM dual;
SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_perbo_c_evtid FROM dual;
SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_perbo_u_evtid FROM dual;
SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_percustbo_c_evtid FROM dual;
SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_percustbo_u_evtid FROM dual;
SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_orgcustbo_c_evtid FROM dual;
SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_orgcustbo_u_evtid FROM dual;
. select all the root nodes from the BOT for a give BO.
. In the same select, identify the nodes for updateBO event
based on BO Version comparision (short circuting).
. Populate the Global Temporary (GT) Table with the above results.
Populate the GT ((BO_CODE, ROOT_NODE_ID, EVENT_ID))with
1. potential candidates for event raising.
2. (short circuting) - identify all the parties for which an event was raised
earlier i.e., candidates for updateBO event (event_type_flag).
*/
LOG('insert into GT the root nodes with short circuting');
INSERT INTO hz_bes_gt (event_id, party_id, BO_CODE, event_type_flag)
SELECT
DECODE ( G_BLK_EVT_RAISE, 'Y',
CASE r.bo_code
WHEN G_PER_BO_CODE THEN
DECODE (r.event_type_flag, 'U', l_perbo_u_evtid, l_perbo_c_evtid)
WHEN G_ORG_BO_CODE THEN
DECODE (r.event_type_flag, 'U', l_orgbo_u_evtid, l_orgbo_c_evtid)
WHEN G_ORG_CUST_BO_CODE THEN
DECODE (r.event_type_flag, 'U', l_orgcustbo_u_evtid, l_orgcustbo_c_evtid)
WHEN G_PER_CUST_BO_CODE THEN
DECODE (r.event_type_flag, 'U', l_percustbo_u_evtid, l_percustbo_c_evtid)
ELSE NULL
END , hz_bus_obj_tracking_s.NEXTVAL),
r.party_id, r.bo_code, r.event_type_flag
FROM (
SELECT
child_id PARTY_ID, child_bo_code BO_CODE,
CASE t.child_bo_code
WHEN G_PER_BO_CODE THEN
DECODE(p.PERSON_BO_VERSION,G_PER_BO_VER,'U',NULL)
WHEN G_ORG_BO_CODE THEN
DECODE(p.ORG_BO_VERSION, G_ORG_BO_VER,'U',NULL)
WHEN G_ORG_CUST_BO_CODE THEN
DECODE(p.ORG_CUST_BO_VERSION,G_ORG_CUST_BO_VER,'U',NULL)
WHEN G_PER_CUST_BO_CODE THEN
DECODE(p.PERSON_CUST_BO_VERSION,G_PER_CUST_BO_VER,'U',NULL)
ELSE NULL
END event_type_flag
FROM hz_bus_obj_tracking t, hz_parties p
WHERE
t.child_bo_code IN
(G_PER_BO_CODE, G_ORG_BO_CODE, G_ORG_CUST_BO_CODE, G_PER_CUST_BO_CODE)
AND p.party_id = t.child_id
AND parent_bo_code IS NULL
AND t.child_entity_name = 'HZ_PARTIES'
AND t.event_id IS NULL) r;
. Select all the Person BO records from the GT table for which event type is not identified
. After checking the completeness of those BOs, delete the rest of the records
for which the event type is not specified and not complete.
. Figure out the event type for the root nodes for which
event type is null (BO is complete) from GT. In the same SQL identify the
Last Update Date applicable for each root node.
Update the GT with event type based on the above query.
*/
-- bo_complete_check() deletes all the incomplete Person BO records from GT
LOG('completness check for Person BO');
. Select all the Org BO records from the GT table for which event type is not identified
. After checking the completeness of those BOs, delete the rest of the records
for which the event type is not specified and not complete.
. Figure out the event type for the root nodes for which
event type is null (BO is complete) from GT. In the same SQL identify the
Last Update Date applicable for each root node.
Update the GT with event type based on the above query.
*/
-- bo_complete_check() deletes all the incomplete Org bo records from GT
LOG('completness check for Org BO');
LOG('insert (in GT) all those related PERSON/ORG CUST BO records that are not in GT');
INSERT ALL
WHEN (child_bo_code is null) THEN
INTO hz_bus_obj_tracking -- inserting person/org cust bo rec in BOT
(CHILD_ENTITY_NAME, CHILD_ID, CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, CREATION_DATE)
VALUES (
'HZ_PARTIES', party_id, 'U', 'Y',
l_cc_start_time, BO_CODE, l_cc_start_time)
INTO hz_bus_obj_tracking -- inserting person/org bo rec as child of person/org cust bo in BOT
(CHILD_ENTITY_NAME, CHILD_ID, CHILD_OPERATION_FLAG, POPULATED_FLAG,
LAST_UPDATE_DATE, CHILD_BO_CODE, CREATION_DATE,
PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, child_event_id)
VALUES (
'HZ_PARTIES', party_id, 'U', 'Y',
l_cc_start_time, C_BO_CODE, l_cc_start_time,
'HZ_PARTIES', party_id, BO_CODE, party_id)
WHEN (1=1) THEN
into HZ_BES_GT ( event_id, party_id, bo_code, event_type_flag)
VALUES (
DECODE (G_BLK_EVT_RAISE, 'Y',
CASE bo_code
WHEN G_ORG_CUST_BO_CODE THEN
DECODE (event_type_flag, 'U', l_orgcustbo_u_evtid, l_orgcustbo_c_evtid)
WHEN G_PER_CUST_BO_CODE THEN
DECODE (event_type_flag, 'U', l_percustbo_u_evtid, l_percustbo_c_evtid)
ELSE NULL
END , hz_bus_obj_tracking_s.NEXTVAL), party_id, bo_code, event_type_flag)
SELECT r.party_id, r.bo_code, r.c_bo_code,
CASE bo_code
WHEN G_ORG_CUST_BO_CODE THEN
DECODE(p.ORG_CUST_BO_VERSION,G_ORG_CUST_BO_VER,'U',NULL)
WHEN G_PER_CUST_BO_CODE THEN
DECODE(p.PERSON_CUST_BO_VERSION,G_PER_CUST_BO_VER,'U',NULL)
ELSE NULL
END event_type_flag,
bot.child_bo_code
FROM (
SELECT
t1.party_id party_id,
DECODE (t1.bo_code, 'PERSON', 'PERSON_CUST', 'ORG','ORG_CUST') BO_CODE,
t1.bo_code C_BO_CODE
FROM
hz_bes_gt t1
WHERE
t1.bo_code IN ('PERSON','ORG')
AND NOT EXISTS
( SELECT t2.party_id
FROM hz_bes_gt t2
WHERE t2.party_id = t1.party_id
AND t2.bo_code IN ('PERSON_CUST','ORG_CUST'))
AND EXISTS
( SELECT 1 FROM hz_cust_accounts WHERE party_id = t1.party_id)
) r, hz_parties p, HZ_BUS_OBJ_TRACKING bot
where
p.party_id = r.party_id
and bot.event_id(+) IS NULL
AND bot.CHILD_ENTITY_NAME (+) = 'HZ_PARTIES'
AND bot.CHILD_ID(+) = r.PARTY_ID
AND bot.CHILD_BO_CODE(+) = r.BO_CODE
AND bot.parent_bo_code(+) IS NULL ;
. Select all the Person Cust BO records from the GT table for which event type is not identified
. After checking the completeness of those BOs, delete the rest of the records
for which the event type is not specified and not complete.
. Figure out the event type for the root nodes for which
event type is null (BO is complete) from GT. In the same SQL identify the
Last Update Date applicable for each root node.
Update the GT with event type based on the above query.
*/
-- bo_complete_check() deletes all the incomplete Person Cust BO records from GT
LOG('completness check for Person Cust BO');
. Select all the Org Cust BO records from the GT table for which event type is not identified
. After checking the completeness of those BOs, delete the rest of the records
for which the event type is not specified and not complete.
. Figure out the event type for the root nodes for which
event type is null (BO is complete) from GT. In the same SQL identify the
Last Update Date applicable for each root node.
Update the GT with event type based on the above query.
*/
-- bo_complete_check() deletes all the incomplete Org Cust BO records from GT
LOG('completness check for Org Cust BO ');
l_paramlist.DELETE;
l_paramlist.DELETE;
l_paramlist.DELETE;
l_paramlist.DELETE;
l_paramlist.DELETE;
l_paramlist.DELETE;
l_paramlist.DELETE;
l_paramlist.DELETE;
LOG('Raise oracle.apps.ar.hz.PersonBO.update Event');
l_event_name := 'oracle.apps.ar.hz.PersonBO.update';
l_key := 'oracle.apps.ar.hz.PersonBO.update'||l_perbo_u_evtid;
l_paramlist.DELETE;
l_paramlist.DELETE;
LOG('Raise oracle.apps.ar.hz.OrgBO.update Event');
l_event_name := 'oracle.apps.ar.hz.OrgBO.update';
l_key := 'oracle.apps.ar.hz.OrgBO.update'||l_orgbo_u_evtid;
l_paramlist.DELETE;
l_paramlist.DELETE;
LOG('Raise oracle.apps.ar.hz.PersonCustBO.update Event');
l_event_name := 'oracle.apps.ar.hz.PersonCustBO.update';
l_key := 'oracle.apps.ar.hz.PersonCustBO.update'||l_percustbo_u_evtid;
l_paramlist.DELETE;
l_paramlist.DELETE;
LOG('Raise oracle.apps.ar.hz.OrgCustBO.update Event');
l_event_name := 'oracle.apps.ar.hz.OrgCustBO.update';
l_key := 'oracle.apps.ar.hz.OrgCustBO.update'||l_orgcustbo_u_evtid;
l_paramlist.DELETE;
l_paramlist.DELETE;
END IF; -- check to log Person.Update event objects end
END IF; -- check to log Org.Update event objects end
END IF; -- check to log OrgCust.Update event objects end
END IF; -- check to log PersonCust.Update event objects end
l_event_name := 'oracle.apps.ar.hz.PersonBO.update';
l_key := 'oracle.apps.ar.hz.PersonBO.update'||l_ids(i);
l_event_name := 'oracle.apps.ar.hz.OrgBO.update';
l_key := 'oracle.apps.ar.hz.OrgBO.update'||l_ids(i);
l_event_name := 'oracle.apps.ar.hz.PersonCustBO.update';
l_key := 'oracle.apps.ar.hz.PersonCustBO.update'||l_ids(i);
l_event_name := 'oracle.apps.ar.hz.OrgCustBO.update';
l_key := 'oracle.apps.ar.hz.OrgCustBO.update'||l_ids(i);
l_paramlist.DELETE;
l_paramlist.DELETE;