1 PACKAGE BODY HZ_BES_BO_RAISE_PKG AS
2 /*$Header: ARHBESRB.pls 120.9 2006/06/27 13:22:21 smattegu noship $ */
3 -- --------- ------ ------------------------------------------
4
5 -- Global Variable.
6 -- This global variable will read the profile that defines whether
7 -- event raising is to be done in bulk or not.
8 -- This profile is set at site level only. The value of the profile is set to Y.
9 -- This is to ensure that, out of the box, event raising is going to be in bulk.
10 G_BLK_EVT_RAISE CONSTANT VARCHAR2(1):=FND_PROFILE.VALUE('HZ_BO_EVENTS_FORMAT_BULK');
11 -- Private procedures
12 -- out() -- This is to write a mesg to out file.
13 -- log() -- This is to write a mesg to log file
14 -- outandlog() -- This is to write a mesg to both out and log files.
15 -- mesglog
16 -- mesgout
17 -- mesgoutlog
18 --
19 ----------------------------------------------
20 /**
21 * Procedure to write a message to the out file
22 **/
23 ----------------------------------------------
24 PROCEDURE out(
25 message IN VARCHAR2,
26 newline IN BOOLEAN DEFAULT TRUE) IS
27 BEGIN
28 IF (newline) THEN
29 FND_FILE.put_line(fnd_file.output,message);
30 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
31 ELSE
32 FND_FILE.put(fnd_file.output,message);
33 END IF;
34 END out;
35 ----------------------------------------------
36 /**
37 * Procedure to write text to the log file
38 **/
39 ----------------------------------------------
40 PROCEDURE log(
41 message IN VARCHAR2,
42 newline IN BOOLEAN DEFAULT TRUE) IS
43 l_prefix VARCHAR2(20) := 'BES_BO_RAISE';
44 BEGIN
45 /*
46 FND_FILE.LOG = 1 - means log file
47 FND_FILE.LOG = 2 - means out file
48 */
49 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
50 hz_utility_v2pub.DEBUG (
51 p_message=>message,
52 p_prefix=>l_prefix,
53 p_msg_level=>fnd_log.level_procedure);
54 END IF ;
55
56 IF newline THEN
57 FND_FILE.put_line(FND_FILE.LOG,message);
58 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
59 ELSE
60 FND_FILE.put_line(FND_FILE.LOG,message);
61 END IF;
62 END log;
63 ----------------------------------------------
64 /**
65 * Procedure to write a message to the out and log files
66 **/
67 ----------------------------------------------
68 PROCEDURE outandlog(
69 message IN VARCHAR2,
70 newline IN BOOLEAN DEFAULT TRUE) IS
71 BEGIN
72 out(message, newline);
73 log(message, newline);
74 END outandlog;
75
76 ----------------------------------------------
77 /**
78 * procedure to fetch messages of the stack and log the error
79
80 ----------------------------------------------
81
82 PROCEDURE logerr IS
83 l_msg_data VARCHAR2(2000);
84 BEGIN
85 FND_MSG_PUB.Reset;
86 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
87 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
88 END LOOP;
89 -- FND_MSG_PUB.Delete_Msg;
90 END logerr;
91 **/
92 ----------------------------------------------
93 /**
94 * Function to fetch messages of the stack and log the error
95 * Also returns the error
96 **/
97 ----------------------------------------------
98 FUNCTION logerror RETURN VARCHAR2 IS
99 l_msg_data VARCHAR2(2000);
100 BEGIN
101 FND_MSG_PUB.Reset;
102
103 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
104 l_msg_data := l_msg_data || ' ' || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
105 END LOOP;
106 log(l_msg_data);
107 RETURN l_msg_data;
108 END logerror;
109
110 ----------------------------------------------
111 /*
112 this procedure takes a message_name and enters into the message stack
113 and writes into the log file also.
114 */
115 ----------------------------------------------
116
117 PROCEDURE mesglog(
118 p_message IN VARCHAR2,
119 p_tkn1_name IN VARCHAR2 DEFAULT NULL,
120 p_tkn1_val IN VARCHAR2 DEFAULT NULL,
121 p_tkn2_name IN VARCHAR2 DEFAULT NULL,
122 p_tkn2_val IN VARCHAR2 DEFAULT NULL
123 ) IS
124 BEGIN
125 FND_MESSAGE.SET_NAME('AR', p_message);
126 IF (p_tkn1_name IS NOT NULL) THEN
127 fnd_message.set_token(p_tkn1_name, p_tkn1_val);
128 END IF;
129 IF (p_tkn2_name IS NOT NULL) THEN
130 fnd_message.set_token(p_tkn2_name, p_tkn2_val);
131 END IF;
132 FND_MSG_PUB.ADD;
133 FND_MSG_PUB.Reset;
134
135 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
136 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
137 END LOOP;
138
139 END mesglog;
140
141 ----------------------------------------------
142 /*
143 this procedure takes a message_name and enters into the message stack
144 and writes into the out file also.
145 */
146 ----------------------------------------------
147
148 PROCEDURE mesgout(
149 p_message IN VARCHAR2,
150 p_tkn1_name IN VARCHAR2 DEFAULT NULL,
151 p_tkn1_val IN VARCHAR2 DEFAULT NULL,
152 p_tkn2_name IN VARCHAR2 DEFAULT NULL,
153 p_tkn2_val IN VARCHAR2 DEFAULT NULL
154 ) IS
155 BEGIN
156 FND_MESSAGE.SET_NAME('AR', p_message);
157 IF (p_tkn1_name IS NOT NULL) THEN
158 fnd_message.set_token(p_tkn1_name, p_tkn1_val);
159 END IF;
160 IF (p_tkn2_name IS NOT NULL) THEN
161 fnd_message.set_token(p_tkn2_name, p_tkn2_val);
162 END IF;
163 FND_MSG_PUB.ADD;
164 FND_MSG_PUB.Reset;
165
166 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
167 out(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
168 END LOOP;
169
170 END mesgout;
171
172 ----------------------------------------------
173 /*
174 this procedure takes a message_name and enters into the message stack
175 and writes into the out and log file also.
176 */
177 ----------------------------------------------
178
179 PROCEDURE mesgoutlog(
180 p_message IN VARCHAR2,
181 p_tkn1_name IN VARCHAR2 DEFAULT NULL,
182 p_tkn1_val IN VARCHAR2 DEFAULT NULL,
183 p_tkn2_name IN VARCHAR2 DEFAULT NULL,
184 p_tkn2_val IN VARCHAR2 DEFAULT NULL
185 ) IS
186 BEGIN
187 FND_MESSAGE.SET_NAME('AR', p_message);
188 IF (p_tkn1_name IS NOT NULL) THEN
189 fnd_message.set_token(p_tkn1_name, p_tkn1_val);
190 END IF;
191 IF (p_tkn2_name IS NOT NULL) THEN
192 fnd_message.set_token(p_tkn2_name, p_tkn2_val);
193 END IF;
194 FND_MSG_PUB.ADD;
195 FND_MSG_PUB.Reset;
196
197 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
198 outandlog(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
199 END LOOP;
200
201 END mesgoutlog;
202 ---------------------------------------------------------------------
203 /*
204 Procedure name: bes_main()
205 Scope: Internal
206 Purpose: This is the umbrella procedure that is used to raise business events
207 for Org, Person, Org Customer and Person Customer business objects in TCA.
208 Called From: Concurrent Manager
209 Called By:
210 Paramaters - brief desc of each parameter:
211 In:
212 Out:
213 In-Out:
214 */
215 --------------------------------------------------------------------------
216 /* bes_main flow:
217 . record the start time of the concurrent program in a variable.
218 . figure out the profile HZ_EXECUTE_API_CALLOUTS value
219 . if the profile is set to v2/noevents then exit gracefully
220 . if the business objects for which there are enabled business events with
221 valid subscriptions (other than the seeded subscription) -- This step is optional in phase 1
222 . select and store the BO object versions from BOD
223 . delete the duplicate rows entered by populate function in BOT. Bug#4957408
224 . identify and populate missing links in BOT
225 . for each BO per/org/percust/orgcust
226 . select all the root nodes from the BOT for a give BO.
227 . In the same select, identify the nodes for updateBO event based on BO Version comparision.
228 (short circuting)
229 . Populate the Global Temporary (GT) Table (BO_CODE, ROOT_NODE_ID, EVENT_ID)
230 with the above results.
231 Note: event_id cannot be populated for bulk events (G_BLK_EVT_RAISE = Y).
232 . Select all the Person BO records from the GT table for which event type is not identified
233 . After checking the completeness of those BOs, delete the rest of the records
234 for which the event type is not specified and not complete.
235 . Figure out the event type for the root nodes for which
236 event type is null (BO is complete) from GT. In the same SQL identify the
237 Last Update Date applicable for each root node.
238 Update the GT with event type based on the above query.
239 . Select all the Org BO records from the GT table for which event type is not identified
240 . After checking the completeness of those BOs, delete the rest of the records
241 for which the event type is not specified and not complete.
242 . Figure out the event type for the root nodes for which
243 event type is null (BO is complete) from GT. In the same SQL identify the
244 Last Update Date applicable for each root node.
245 Update the GT with event type based on the above query.
246 . Populate all those Org/Person parties in GT(BO_CODE, ROOT_NODE_ID, EVENT_ID),
247 that are not current present as Org/Person Cust BOs in GT; but,
248 have an existing account into GT.
249 Note: event_id cannot be populated for bulk events (G_BLK_EVT_RAISE = Y).
250 Also populate BOT with two records per each one record that was entered into GT.
251 One record is for Per/Org Cust BO root node record.
252 The other record is for Per/Org BO as child and Per/Org Cust BO as parent.
253 . Select all the Person Cust BO records from the GT table for which event type is not identified
254 . After checking the completeness of those BOs, delete the rest of the records
255 for which the event type is not specified and not complete.
256 . Figure out the event type for the root nodes for which
257 event type is null (BO is complete) from GT. In the same SQL identify the
258 Last Update Date applicable for each root node.
259 Update the GT with event type based on the above query.
260 . Select all the Org Cust BO records from the GT table for which event type is not identified
261 . After checking the completeness of those BOs, delete the rest of the records
262 for which the event type is not specified and not complete.
263 . Figure out the event type for the root nodes for which
264 event type is null (BO is complete) from GT. In the same SQL identify the
265 Last Update Date applicable for each root node.
266 Update the GT with event type based on the above query.
267 . populate the event parameter payload with event id (CDH_EVENT_ID).
268 . If the events must be raised in Bulk,
269 . Identify how many types of events to be raised.
270 This is done by doing existence check on GT for a given BO and
271 event type flag combination. Generate as needed, event ids.
272 . For Person-Insert Raise One Bulk event
273 . For Person-Update Raise One Bulk event
274 . For Org-Insert Raise One Bulk event
275 . For Org-Update Raise One Bulk event
276 . For Person-Cust-Insert Raise One Bulk event
277 . For Person-Cust-Update Raise One Bulk event
278 . For Org-Cust-Insert Raise One Bulk event
279 . For Org-Cust-Update Raise One Bulk event
280 . For all the eight events above, Update entire BOT hierarchy with
281 appropriate event_id.
282 . commit
283 . identify all the Person/Org Child BOs in Person/Org Cust BOs and
284 update the records with appropriate child event ids. -- this step is not done
285 as child_event_id is populated at the time of populating BOT (per/Org EBO rec)
286 . If the event must be raised per each BO
287 . Select all the GT contents, ordered by root_node_id (a.k.a. party_id).
288 This would select the all the parties with different BOs together.
289 . Per each party
290 . if BO = Person
291 . Raise the create/update event for Person BO
292 . Update entire BOT hierarchy with event_id
293 . store the person bo event_id for later use as child_event_id (per_child_evt_id).
294 . if BO = Person Cust
295 . Raise the create/update event for Person Cust BO
296 . Update entire BOT hierarchy with event_id
297 . Update the Person as child record child_event_id with per_child_evt_id
298 . if BO = Org
299 . Raise the create/update event for Org BO
300 . Update entire BOT hierarchy with event_id
301 . store the org bo event_id for later use as child_event_id (org_child_evt_id).
302 . if BO = Org Cust
303 . Raise the create/update event for OrgCust BO
304 . Update entire BOT hierarchy with event_id
305 . Update the Person as child record child_event_id with org_child_evt_id
306 . commit; -- As event raising already took place, we need to comminucate to
307 V2 APIs that they must start writing fresh entity records in BOT.
308 This can be acheived with this commit.
309 . Write the BO version of BO (taken from BOD) to hz_parties table for all
310 parties that were in GT table.
311 . Delete the records that were not part of any event from BOT.
312 This is based on creation_date being less that than the start time of the conc program.
313
314 some definitions:
315 Two kinds of the root nodes (parties) that must be evaluated for
316 completness check and eventtype().
317 1. First timers: Party records that are processed for the first time.
318 In other words, those party records for which BO_VERSION_NUMBER is null
319 on hz_parties table.
320 2. Second timers: Party records that were processed earlier; but the
321 business object definition changed after that.
322 In other words, those party records for which BO_VERSION_NUMBER is different
323 from the current Business Object definition in the BOD table.
324 */
325
326 PROCEDURE bes_main (
327 errbuf OUT NOCOPY VARCHAR2,
328 retcode OUT NOCOPY VARCHAR2) IS
329
330 -- cursor for getting the BO version number for per/org/orgcust/percust BOs
331 CURSOR c_bo_ver (cp_bo_code VARCHAR2) IS
332 SELECT bo_version_number
333 FROM hz_bus_obj_definitions
334 WHERE business_object_code = cp_bo_code
335 AND root_node_flag = 'Y';
336
337 CURSOR c_bo_gt IS
338 SELECT event_id, party_id, bo_code, event_type_flag
339 FROM hz_bes_gt
340 ORDER BY bo_code asc ;
341
342 -- debug code start
343 CURSOR c_bo_gt_debug IS
344 SELECT event_id, party_id, bo_code, event_type_flag
345 FROM hz_bes_gt
346 ORDER BY event_id, bo_code desc ;
347
348 CURSOR c_bo_gt_debug2 (cp_bo_code VARCHAR2) IS
349 SELECT event_id, party_id, event_type_flag
350 FROM hz_bes_gt
351 WHERE bo_code = cp_bo_code
352 ORDER BY event_type_flag, event_id, party_id desc ;
353 -- debug code end
354
355 /* Cusror to figure out what types of bulk update events to be raised
356 */
357 CURSOR c_chk_insevt (cp_bo_code VARCHAR2) IS
358 SELECT 'Y'
359 FROM hz_bes_gt
360 WHERE BO_CODE = cp_bo_code
361 AND event_type_flag IS NULL
362 AND ROWNUM <2;
363
364 /* Cusror to figure out what types of bulk insert events to be raised
365 */
366 CURSOR c_chk_updevt (cp_bo_code VARCHAR2) IS
367 SELECT 'Y'
368 FROM hz_bes_gt
369 WHERE BO_CODE = cp_bo_code
370 AND event_type_flag = 'U'
371 AND ROWNUM <2;
372
373 -- This is the ER done as part of bug 4997605.
374 -- This is to print all the objects that were raised in bulk mode
375 CURSOR c_bot_log ( cp_evtid NUMBER, cp_bo_code VARCHAR2) IS
376 SELECT event_id CDH_EVENT_ID,
380 WHERE event_id = cp_evtid
377 child_id CDH_OBJECT_ID,
378 parent_event_flag event_type
379 FROM hz_bus_obj_tracking
381 AND CHILD_BO_CODE = cp_bo_code
382 AND parent_BO_CODE IS NULL
383 ORDER BY 2 desc;
384
385 -- local variables
386 -- to store the party ids from BOT(parent_id) for raising the create event.
387 l_cre_evt_ids NUMBER_TBLTYPE;
388 -- to store the party ids from BOT(parent_id) for raising the update event.
389 l_upd_evt_ids NUMBER_TBLTYPE;
390 -- to store the party ids and last update dates that are to be
391 -- processed by the coompletness() and evtType()
392 l_ids NUMBER_TBLTYPE;
393 l_pids NUMBER_TBLTYPE;
394 l_ids2 NUMBER_TBLTYPE;
395 l_evts VCHAR2_1_TBLTYPE;
396 l_bo_codes VCHAR2_30_TBLTYPE;
397 l_ct NUMBER := 0; -- temp variable to store count of number of records in GT
398 l_g_bulk_type BOOLEAN ;
399
400 -- to store the event_ids for BO events
401 l_perbo_c_evtid NUMBER := 0; -- per bo create event id
402 l_perbo_u_evtid NUMBER := 0; -- per bo update event id
403 l_orgbo_c_evtid NUMBER := 0; -- org bo create event id
404 l_orgbo_u_evtid NUMBER := 0; -- org bo create event id
405 l_percustbo_c_evtid NUMBER := 0; -- per cust bo create event id
406 l_percustbo_u_evtid NUMBER := 0; -- per cust bo update event id
407 l_orgcustbo_c_evtid NUMBER := 0; -- org cust bo create event id
408 l_orgcustbo_u_evtid NUMBER := 0; -- org cust bo update event id
409
410 -- to store the concurrent program start time
411 -- When storing the missing links in BOT, this is used as creation_date
412 l_cc_start_time DATE := SYSDATE;
413 -- The flush spurious records in BOT uses the following date
414 l_del_cutoff_dt DATE := l_cc_start_time -1;
415
416 -- when raising bulk events, flags are needed to know what events to raise.
417 -- the following 8 flags are used for that purpose.
418 l_p_ins_flag VARCHAR2(1);-- to raise person bo ins event or not
419 l_p_upd_flag VARCHAR2(1);-- to raise person bo upd event or not
420 l_o_ins_flag VARCHAR2(1);-- to raise org bo ins event or not
421 l_o_upd_flag VARCHAR2(1);-- to raise org bo upd event or not
422 l_pc_ins_flag VARCHAR2(1);-- to raise person cust bo ins event or not
423 l_pc_upd_flag VARCHAR2(1);-- to raise person cust bo upd event or not
424 l_oc_ins_flag VARCHAR2(1);-- to raise org cust bo ins event or not
425 l_oc_upd_flag VARCHAR2(1);-- to raise org cust bo upd event or not
426
427 -- following variables are needed for event raising
428 l_paramlist WF_PARAMETER_LIST_T;
429 --l_param WF_PARAMETER_T;
430 l_key VARCHAR2(240);
431 l_event_name VARCHAR2(240);
432
433
434 BEGIN
435 -- Initialize return status and message stack
436 FND_MSG_PUB.initialize;
437
438 retcode := 0; -- setting the return code to success
439 -- write debug mesg
440 LOG('bes_main(+)');
441
442 -- check if the profile is set to v2 and v3 or just v3 events.
443 -- in cases other than the above there is no need of any processing.
444 G_PROF_VAL := FND_PROFILE.value( 'HZ_EXECUTE_API_CALLOUTS');
445
446 IF G_PROF_VAL = 'N' OR G_PROF_VAL = 'Y' THEN
447 -- this means either no vents must be raised or only v2 events must be raised
448 -- hence, raise an error.
449 mesgoutlog('HZ_BES_BO_RAISE_PROFILE_ERROR');
450 RAISE FND_API.G_EXC_ERROR;
451 -- RETURN;
452 END IF;
453
454 -- check the bulk profile option
455 -- if the profile value is set to Y - then raise bulk events
456 -- if the profile value is set to N - then raise one event per object instance
457 IF G_BLK_EVT_RAISE = 'N' OR G_BLK_EVT_RAISE = 'Y' THEN
458 null;
459 else
460 mesgoutlog('HZ_BES_BO_FORMAT_PROF_ERROR');
461 RAISE FND_API.G_EXC_ERROR;
462 -- RETURN;
463 END IF;
464
465 IF G_BLK_EVT_RAISE = 'Y' THEN
466 l_g_bulk_type := TRUE;
467 ELSE
468 l_g_bulk_type := FALSE;
469 END IF;
470
471 -- . select and store the BO object versions from BOD
472 -- select the bo version numbers for all the business objects
473 -- This is done irrespective of if the BO is enabled or not.
474
475 LOG('get BO versions from BOD');
476 -- for person BO
477 OPEN c_bo_ver(G_PER_BO_CODE);
478 FETCH c_bo_ver INTO G_PER_BO_VER;
479 CLOSE c_bo_ver;
480 -- for org bo
481 OPEN c_bo_ver(G_ORG_BO_CODE);
482 FETCH c_bo_ver INTO G_ORG_BO_VER ;
483 CLOSE c_bo_ver;
484 -- for person customer BO
485 OPEN c_bo_ver(G_PER_CUST_BO_CODE);
486 FETCH c_bo_ver INTO G_PER_CUST_BO_VER ;
487 CLOSE c_bo_ver;
488 -- for org customer BO
489 OPEN c_bo_ver(G_ORG_CUST_BO_CODE);
490 FETCH c_bo_ver INTO G_ORG_CUST_BO_VER ;
491 CLOSE c_bo_ver;
492 -- for person customer BO
493 OPEN c_bo_ver(G_PER_CUST_BO_CODE);
494 FETCH c_bo_ver INTO G_PER_CUST_BO_VER ;
495 CLOSE c_bo_ver;
496 -- for org customer BO
497 OPEN c_bo_ver(G_ORG_CUST_BO_CODE);
498 FETCH c_bo_ver INTO G_ORG_CUST_BO_VER ;
499 CLOSE c_bo_ver;
500
501 /*
502 Changes for Bug#4957408.
503 It is possible for Shipping and Lead Import concurrent programs to
504 call and write the same TCA info using parallel running conc request threads.
505 This will result in duplicate rows in BOT (via populate func).
506 The duplicate rows must be deleted to proceed further.
507 */
508 LOG('delete duplicate rows from BOT');
509 HZ_BES_BO_UTIL_PKG.del_duplicate_rows;
510
511 /*
512 . identify and populate missing links in BOT
513 1. Figure out the start time of the concurrent program.
514 2. Provide the (start time of the concurrent_program -1)
515 as the creation_date for the missing links procedure.
516 This will be used as creation_date when BOT is populated by
517 missing links procedures.
521 */
518 This is needed because, V2 APIs, via populate functions
519 some times creates the records, that are not part of any event.
520 To delete such records, this creation_date column is used.
522 LOG('populate missing links');
523 HZ_BES_BO_UTIL_PKG.populate_missing_links(l_cc_start_time);
524
525 IF G_BLK_EVT_RAISE = 'Y' THEN
526 LOG('get event id sequences for bulk event raising');
527 -- select the sequence a.k.a event ids needed
528 SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_orgbo_c_evtid FROM dual;
529 SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_orgbo_u_evtid FROM dual;
530 SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_perbo_c_evtid FROM dual;
531 SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_perbo_u_evtid FROM dual;
532 SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_percustbo_c_evtid FROM dual;
533 SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_percustbo_u_evtid FROM dual;
534 SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_orgcustbo_c_evtid FROM dual;
535 SELECT hz_bus_obj_tracking_s.NEXTVAL INTO l_orgcustbo_u_evtid FROM dual;
536 END IF;
537
538 /*
539 . select all the root nodes from the BOT for a give BO.
540 . In the same select, identify the nodes for updateBO event
541 based on BO Version comparision (short circuting).
542 . Populate the Global Temporary (GT) Table with the above results.
543
544 Populate the GT ((BO_CODE, ROOT_NODE_ID, EVENT_ID))with
545 1. potential candidates for event raising.
546 2. (short circuting) - identify all the parties for which an event was raised
547 earlier i.e., candidates for updateBO event (event_type_flag).
548 */
549
550 LOG('insert into GT the root nodes with short circuting');
551 INSERT INTO hz_bes_gt (event_id, party_id, BO_CODE, event_type_flag)
552 SELECT
553 DECODE ( G_BLK_EVT_RAISE, 'Y',
554 CASE r.bo_code
555 WHEN G_PER_BO_CODE THEN
556 DECODE (r.event_type_flag, 'U', l_perbo_u_evtid, l_perbo_c_evtid)
557 WHEN G_ORG_BO_CODE THEN
558 DECODE (r.event_type_flag, 'U', l_orgbo_u_evtid, l_orgbo_c_evtid)
559 WHEN G_ORG_CUST_BO_CODE THEN
560 DECODE (r.event_type_flag, 'U', l_orgcustbo_u_evtid, l_orgcustbo_c_evtid)
561 WHEN G_PER_CUST_BO_CODE THEN
562 DECODE (r.event_type_flag, 'U', l_percustbo_u_evtid, l_percustbo_c_evtid)
563 ELSE NULL
564 END , hz_bus_obj_tracking_s.NEXTVAL),
565 r.party_id, r.bo_code, r.event_type_flag
566 FROM (
567 SELECT
568 child_id PARTY_ID, child_bo_code BO_CODE,
569 CASE t.child_bo_code
570 WHEN G_PER_BO_CODE THEN
571 DECODE(p.PERSON_BO_VERSION,G_PER_BO_VER,'U',NULL)
572 WHEN G_ORG_BO_CODE THEN
573 DECODE(p.ORG_BO_VERSION, G_ORG_BO_VER,'U',NULL)
574 WHEN G_ORG_CUST_BO_CODE THEN
575 DECODE(p.ORG_CUST_BO_VERSION,G_ORG_CUST_BO_VER,'U',NULL)
576 WHEN G_PER_CUST_BO_CODE THEN
577 DECODE(p.PERSON_CUST_BO_VERSION,G_PER_CUST_BO_VER,'U',NULL)
578 ELSE NULL
579 END event_type_flag
580 FROM hz_bus_obj_tracking t, hz_parties p
581 WHERE
582 t.child_bo_code IN
583 (G_PER_BO_CODE, G_ORG_BO_CODE, G_ORG_CUST_BO_CODE, G_PER_CUST_BO_CODE)
584 AND p.party_id = t.child_id
585 AND parent_bo_code IS NULL
586 AND t.child_entity_name = 'HZ_PARTIES'
587 AND t.event_id IS NULL) r;
588 COMMIT;
589 /*
590 -- debug code start
591 OPEN c_bo_gt_debug;
592 FETCH c_bo_gt_debug BULK COLLECT INTO l_ids, l_ids2, l_bo_codes, l_evts ;
593 CLOSE c_bo_gt_debug;
594 l_ct := l_ids2.COUNT;
595 LOG('count is:'||to_char(l_ct));
596 FOR i IN 1.. l_ct LOOP
597 LOG ('evt id:'||l_ids(i)||' party_id:'||l_ids2(i)||' bo_code:'||l_bo_codes(i)||' evtType:'||l_evts(i));
598 END LOOP;
599 -- debug code end
600 */
601 /*
602 . Select all the Person BO records from the GT table for which event type is not identified
603 . After checking the completeness of those BOs, delete the rest of the records
604 for which the event type is not specified and not complete.
605 . Figure out the event type for the root nodes for which
606 event type is null (BO is complete) from GT. In the same SQL identify the
607 Last Update Date applicable for each root node.
608 Update the GT with event type based on the above query.
609 */
610 -- bo_complete_check() deletes all the incomplete Person BO records from GT
611 LOG('completness check for Person BO');
612 HZ_BES_BO_SITE_UTIL_PKG.bo_complete_check(G_PER_BO_CODE);
613 /*
614 -- debug code start
615 OPEN c_bo_gt_debug2(G_PER_BO_CODE);
616 FETCH c_bo_gt_debug2 BULK COLLECT INTO l_ids, l_ids2, l_evts ;
617 CLOSE c_bo_gt_debug2;
618 l_ct := l_ids2.COUNT;
619 LOG('complete '||G_PER_BO_CODE||' BO count is:'||to_char(l_ct));
620 FOR i IN 1.. l_ct LOOP
621 LOG ('evt id:'||l_ids(i)||' party_id:'||l_ids2(i)||' evtType:'||l_evts(i));
622 END LOOP;
623 -- debug code end
624 */
625 -- bo_event_check() updates all the Person BO recs in GT
626 -- that are complete and are candiates for update event.
627 LOG('event type check for Person BO');
628 HZ_BES_BO_SITE_UTIL_PKG.bo_event_check(G_PER_BO_CODE);
629 /*
630
631 -- debug code start
632 OPEN c_bo_gt_debug2(G_PER_BO_CODE);
633 FETCH c_bo_gt_debug2 BULK COLLECT INTO l_ids, l_ids2, l_evts ;
634 CLOSE c_bo_gt_debug2;
635 l_ct := l_ids2.COUNT;
636 LOG(G_PER_BO_CODE||' BO count is:'||to_char(l_ct));
637 log('Following is the list of '||G_PER_BO_CODE||' objects with potential evt types');
638 FOR i IN 1.. l_ct LOOP
639 LOG ('evt id:'||l_ids(i)||' party_id:'||l_ids2(i)||' evtType:'||l_evts(i));
640 END LOOP;
641 -- debug code end
642 */
643 -- All the remaining person bo records in GT with NULL event_type_flag are
644 -- complete and candidates for create event
645
646 /*
647 -- for org bo
651 . Figure out the event type for the root nodes for which
648 . Select all the Org BO records from the GT table for which event type is not identified
649 . After checking the completeness of those BOs, delete the rest of the records
650 for which the event type is not specified and not complete.
652 event type is null (BO is complete) from GT. In the same SQL identify the
653 Last Update Date applicable for each root node.
654 Update the GT with event type based on the above query.
655 */
656
657 -- bo_complete_check() deletes all the incomplete Org bo records from GT
658 LOG('completness check for Org BO');
659 HZ_BES_BO_SITE_UTIL_PKG.bo_complete_check(G_ORG_BO_CODE);
660 /* -- debug code start
661 OPEN c_bo_gt_debug2(G_ORG_BO_CODE);
662 FETCH c_bo_gt_debug2 BULK COLLECT INTO l_ids, l_ids2, l_evts ;
663 CLOSE c_bo_gt_debug2;
664 l_ct := l_ids2.COUNT;
665 LOG('complete '||G_ORG_BO_CODE||' BO count is:'||to_char(l_ct));
666 FOR i IN 1.. l_ct LOOP
667 LOG ('evt id:'||l_ids(i)||' party_id:'||l_ids2(i)||' evtType:'||l_evts(i));
668 END LOOP;
669 -- debug code end
670 */
671 -- bo_event_check() updates all the Org bo recs in GT
672 -- that are complete and are candiates for update event.
673 LOG('event type check for Org BO');
674 HZ_BES_BO_SITE_UTIL_PKG.bo_event_check(G_ORG_BO_CODE);
675 /*
676 -- debug code start
677 OPEN c_bo_gt_debug2(G_ORG_BO_CODE);
678 FETCH c_bo_gt_debug2 BULK COLLECT INTO l_ids, l_ids2, l_evts ;
679 CLOSE c_bo_gt_debug2;
680 l_ct := l_ids2.COUNT;
681 LOG(G_ORG_BO_CODE||' BO count is:'||to_char(l_ct));
682 log('Following is the list of '||G_ORG_BO_CODE||' objects with potential evt types');
683 FOR i IN 1.. l_ct LOOP
684 LOG ('evt id:'||l_ids(i)||' party_id:'||l_ids2(i)||' evtType:'||l_evts(i));
685 END LOOP;
686 -- debug code end
687 */
688 -- All the remaining Org bo records in GT with NULL event_type_flag are
689 -- complete and candidates for create event
690
691 /*
692 . Populate all those Org/Person parties in GT(BO_CODE, ROOT_NODE_ID, EVENT_ID),
693 that are not current present as Org/Person Cust BOs in GT; but,
694 have an existing account.
695 Also populate BOT with two records per each one record that was entered into GT.
696 One record is for Per/Org Cust BO root node record.
697 The other record is for Per/Org BO as child and Per/Org Cust BO as parent.
698 */
699 -- the following insert statement writes two records
700 -- Person/Org Cust and Person/Org (as it's child) into BOT.
701 -- For this to happen, the following conditions must be met:
702 -- 1. There is no corresponding PERSON/ORG CUST record already in GT, BOT
703 -- 2. Account record exists for the party.
704 -- This will also inser Org/Person Cust Records in GT
705 -- While populating the records in GT, this SQL will figure out the
706 -- short circuting for based on the Bo version numbers for cust business object
707 -- from hz_parties table.
708 LOG('insert (in GT) all those related PERSON/ORG CUST BO records that are not in GT');
709 INSERT ALL
710 WHEN (child_bo_code is null) THEN
711 INTO hz_bus_obj_tracking -- inserting person/org cust bo rec in BOT
712 (CHILD_ENTITY_NAME, CHILD_ID, CHILD_OPERATION_FLAG, POPULATED_FLAG,
713 LAST_UPDATE_DATE, CHILD_BO_CODE, CREATION_DATE)
714 VALUES (
715 'HZ_PARTIES', party_id, 'U', 'Y',
716 l_cc_start_time, BO_CODE, l_cc_start_time)
717 INTO hz_bus_obj_tracking -- inserting person/org bo rec as child of person/org cust bo in BOT
718 (CHILD_ENTITY_NAME, CHILD_ID, CHILD_OPERATION_FLAG, POPULATED_FLAG,
719 LAST_UPDATE_DATE, CHILD_BO_CODE, CREATION_DATE,
720 PARENT_ENTITY_NAME, PARENT_ID, PARENT_BO_CODE, child_event_id)
721 VALUES (
722 'HZ_PARTIES', party_id, 'U', 'Y',
723 l_cc_start_time, C_BO_CODE, l_cc_start_time,
724 'HZ_PARTIES', party_id, BO_CODE, party_id)
725 WHEN (1=1) THEN
726 into HZ_BES_GT ( event_id, party_id, bo_code, event_type_flag)
727 VALUES (
728 DECODE (G_BLK_EVT_RAISE, 'Y',
729 CASE bo_code
730 WHEN G_ORG_CUST_BO_CODE THEN
731 DECODE (event_type_flag, 'U', l_orgcustbo_u_evtid, l_orgcustbo_c_evtid)
732 WHEN G_PER_CUST_BO_CODE THEN
733 DECODE (event_type_flag, 'U', l_percustbo_u_evtid, l_percustbo_c_evtid)
734 ELSE NULL
735 END , hz_bus_obj_tracking_s.NEXTVAL), party_id, bo_code, event_type_flag)
736 SELECT r.party_id, r.bo_code, r.c_bo_code,
737 CASE bo_code
738 WHEN G_ORG_CUST_BO_CODE THEN
739 DECODE(p.ORG_CUST_BO_VERSION,G_ORG_CUST_BO_VER,'U',NULL)
740 WHEN G_PER_CUST_BO_CODE THEN
741 DECODE(p.PERSON_CUST_BO_VERSION,G_PER_CUST_BO_VER,'U',NULL)
742 ELSE NULL
743 END event_type_flag,
744 bot.child_bo_code
745 FROM (
746 SELECT
747 t1.party_id party_id,
748 DECODE (t1.bo_code, 'PERSON', 'PERSON_CUST', 'ORG','ORG_CUST') BO_CODE,
749 t1.bo_code C_BO_CODE
750 FROM
751 hz_bes_gt t1
752 WHERE
753 t1.bo_code IN ('PERSON','ORG')
754 AND NOT EXISTS
755 ( SELECT t2.party_id
756 FROM hz_bes_gt t2
757 WHERE t2.party_id = t1.party_id
758 AND t2.bo_code IN ('PERSON_CUST','ORG_CUST'))
759 AND EXISTS
760 ( SELECT 1 FROM hz_cust_accounts WHERE party_id = t1.party_id)
761 ) r, hz_parties p, HZ_BUS_OBJ_TRACKING bot
762 where
763 p.party_id = r.party_id
764 and bot.event_id(+) IS NULL
765 AND bot.CHILD_ENTITY_NAME (+) = 'HZ_PARTIES'
766 AND bot.CHILD_ID(+) = r.PARTY_ID
767 AND bot.CHILD_BO_CODE(+) = r.BO_CODE
768 AND bot.parent_bo_code(+) IS NULL ;
769 COMMIT;
770
771 /* for Person Cust BO
772 . Select all the Person Cust BO records from the GT table for which event type is not identified
773 . After checking the completeness of those BOs, delete the rest of the records
774 for which the event type is not specified and not complete.
778 Update the GT with event type based on the above query.
775 . Figure out the event type for the root nodes for which
776 event type is null (BO is complete) from GT. In the same SQL identify the
777 Last Update Date applicable for each root node.
779 */
780 -- bo_complete_check() deletes all the incomplete Person Cust BO records from GT
781 LOG('completness check for Person Cust BO');
782 HZ_BES_BO_SITE_UTIL_PKG.bo_complete_check(G_PER_CUST_BO_CODE);
783 /*
784 -- debug code start
785 OPEN c_bo_gt_debug2(G_PER_CUST_BO_CODE);
786 FETCH c_bo_gt_debug2 BULK COLLECT INTO l_ids, l_ids2, l_evts ;
787 CLOSE c_bo_gt_debug2;
788 l_ct := l_ids2.COUNT;
789 LOG('complete '||G_PER_CUST_BO_CODE||' BO count is:'||to_char(l_ct));
790 FOR i IN 1.. l_ct LOOP
791 LOG ('evt id:'||l_ids(i)||' party_id:'||l_ids2(i)||' evtType:'||l_evts(i));
792 END LOOP;
793 -- debug code end
794 */
795 -- bo_event_check() updates all the Person Cust BO recs in GT
796 -- that are complete and are candiates for update event.
797 LOG('event type check for Person Cust BO ');
798 HZ_BES_BO_SITE_UTIL_PKG.bo_event_check(G_PER_CUST_BO_CODE);
799 /*
800 -- All the remaining Person Cust BO recs in GT with NULL event_type_flag are
801 -- complete and candidates for create event
802
803 -- debug code start
804 OPEN c_bo_gt_debug2(G_PER_CUST_BO_CODE);
805 FETCH c_bo_gt_debug2 BULK COLLECT INTO l_ids, l_ids2, l_evts ;
806 CLOSE c_bo_gt_debug2;
807 l_ct := l_ids2.COUNT;
808 LOG(G_PER_CUST_BO_CODE||' BO count is:'||to_char(l_ct));
809 log('Following is the list of '||G_PER_CUST_BO_CODE||' objects with potential evt types');
810 FOR i IN 1.. l_ct LOOP
811 LOG ('evt id:'||l_ids(i)||' party_id:'||l_ids2(i)||' evtType:'||l_evts(i));
812 END LOOP;
813 -- debug code end
814 */
815 /* for Org Cust BO
816 . Select all the Org Cust BO records from the GT table for which event type is not identified
817 . After checking the completeness of those BOs, delete the rest of the records
818 for which the event type is not specified and not complete.
819 . Figure out the event type for the root nodes for which
820 event type is null (BO is complete) from GT. In the same SQL identify the
821 Last Update Date applicable for each root node.
822 Update the GT with event type based on the above query.
823 */
824 -- bo_complete_check() deletes all the incomplete Org Cust BO records from GT
825 LOG('completness check for Org Cust BO ');
826 HZ_BES_BO_SITE_UTIL_PKG.bo_complete_check(G_ORG_CUST_BO_CODE);
827 /*
828 -- debug code start
829 OPEN c_bo_gt_debug2(G_ORG_CUST_BO_CODE);
830 FETCH c_bo_gt_debug2 BULK COLLECT INTO l_ids, l_ids2, l_evts ;
831 CLOSE c_bo_gt_debug2;
832 l_ct := l_ids2.COUNT;
833 LOG('complete '||G_ORG_CUST_BO_CODE||' BO count is:'||to_char(l_ct));
834 FOR i IN 1.. l_ct LOOP
835 LOG ('evt id:'||l_ids(i)||' party_id:'||l_ids2(i)||' evtType:'||l_evts(i));
836 END LOOP;
837 -- debug code end
838 */
839 -- bo_event_check() updates all the Org Cust BO recs in GT
840 -- that are complete and are candiates for update event.
841 LOG('event type check for Org Cust BO ');
842 HZ_BES_BO_SITE_UTIL_PKG.bo_event_check(G_ORG_CUST_BO_CODE);
843
844 -- All the remaining Org Cust BO records in GT with NULL event_type_flag are
845 -- complete and candidates for create event
846 /*
847 -- debug code start
848 OPEN c_bo_gt_debug2(G_ORG_CUST_BO_CODE);
849 FETCH c_bo_gt_debug2 BULK COLLECT INTO l_ids, l_ids2, l_evts ;
850 CLOSE c_bo_gt_debug2;
851 l_ct := l_ids2.COUNT;
852 LOG(G_ORG_CUST_BO_CODE||' BO count is:'||to_char(l_ct));
853 log('Following is the list of '||G_ORG_CUST_BO_CODE||' objects with potential evt types');
854 FOR i IN 1.. l_ct LOOP
855 LOG ('evt id:'||l_ids(i)||' party_id:'||l_ids2(i)||' evtType:'||l_evts(i));
856 END LOOP;
857 -- debug code end
858 */
859 -- Till now, all the BO records for which an event must be raised or identified
860 -- and marked. The main tasks remaing are raising the event(s) and house keeping.
861
862 -- prepare WF BES event parameter object
863 -- initialization of object variables
864 l_paramlist := WF_PARAMETER_LIST_T();
865 -- l_param := WF_PARAMETER_T( NULL, NULL );
866
867 -- fill the parameters list
868 -- l_paramlist.extend;
869
870 -- For raising events, the code forks from here on.
871
872 -- On branch for Bulk Events and the other for raising one event per object
873 IF l_g_bulk_type THEN
874 ---------------------------------------
875 -- Events must be raised in bulk
876 ---------------------------------------
877 LOG('Events must be raised in bulk.');
878 LOG('Figure out the number of events that must be raised');
879 -- raise the events in bulk
880 -- figure out the number of events that must be raised.
881 -- for person insert event
882 OPEN c_chk_insevt (G_PER_BO_CODE);
883 FETCH c_chk_insevt INTO l_p_ins_flag;
884 CLOSE c_chk_insevt;
885 -- for org insert event
886 OPEN c_chk_insevt (G_ORG_BO_CODE);
887 FETCH c_chk_insevt INTO l_o_ins_flag;
888 CLOSE c_chk_insevt;
889 -- for person cust insert event
890 OPEN c_chk_insevt (G_PER_CUST_BO_CODE);
891 FETCH c_chk_insevt INTO l_pc_ins_flag;
892 CLOSE c_chk_insevt;
893 -- for org cust insert event
894 OPEN c_chk_insevt (G_ORG_CUST_BO_CODE);
895 FETCH c_chk_insevt INTO l_oc_ins_flag;
896 CLOSE c_chk_insevt;
897
898 -- for person update event
899 OPEN c_chk_updevt (G_PER_BO_CODE);
900 FETCH c_chk_updevt INTO l_p_upd_flag;
901 CLOSE c_chk_updevt;
902 -- for org update event
903 OPEN c_chk_updevt (G_ORG_BO_CODE);
904 FETCH c_chk_updevt INTO l_o_upd_flag;
905 CLOSE c_chk_updevt;
906 -- for person cust update event
907 OPEN c_chk_updevt (G_PER_CUST_BO_CODE);
911 OPEN c_chk_updevt (G_ORG_CUST_BO_CODE);
908 FETCH c_chk_updevt INTO l_pc_upd_flag;
909 CLOSE c_chk_updevt;
910 -- for org cust update event
912 FETCH c_chk_updevt INTO l_oc_upd_flag;
913 CLOSE c_chk_updevt;
914
915
916 LOG('Updating BOT object hierarchy with event_id');
917 -- update the object hierarchy in BOT, with event id
918 HZ_BES_BO_UTIL_PKG.upd_bot_evtid_dt (
919 p_bulk_evt => l_g_bulk_type,
920 p_evt_id => NULL,
921 p_child_id => NULL ,
922 p_child_bo_code => NULL ,
923 p_creation_date => l_cc_start_time,
924 p_evt_type => NULL,
925 p_commit => FALSE,
926 p_per_ins_evt_id => l_perbo_c_evtid,
927 p_per_upd_evt_id => l_perbo_u_evtid,
928 p_org_ins_evt_id => l_orgbo_c_evtid,
929 p_org_upd_evt_id => l_orgbo_u_evtid,
930 p_perc_ins_evt_id => l_percustbo_c_evtid,
931 p_perc_upd_evt_id => l_percustbo_u_evtid,
932 p_orgc_ins_evt_id => l_orgcustbo_c_evtid,
933 p_orgc_upd_evt_id => l_orgcustbo_u_evtid);
934
935 IF l_p_ins_flag = 'Y' THEN
936 LOG('Raise oracle.apps.ar.hz.PersonBO.create Event');
937 LOG('CDH_EVENT_ID is: '||l_perbo_c_evtid);
938 -- Raise oracle.apps.ar.hz.PersonBO.create Event
939 l_event_name := 'oracle.apps.ar.hz.PersonBO.create';
940 l_key := 'oracle.apps.ar.hz.PersonBO.create'||l_perbo_c_evtid;
941 -- add parameters to list
942 wf_event.addParameterToList
943 (p_name => 'CDH_EVENT_ID',
944 p_value => l_perbo_c_evtid,
945 p_parameterlist => l_paramlist);
946 -- add Q_CORRELATION_ID parameter to the List
947 wf_event.addParameterToList
948 (p_name => 'Q_CORRELATION_ID',
949 p_value => l_event_name,
950 p_parameterlist => l_paramlist);
951
952 /*
953 l_param.SetName( 'CDH_EVENT_ID' );
954 l_param.SetValue(l_perbo_c_evtid);
955 l_paramlist(l_paramlist.last) := l_param;
956 */
957 BEGIN
958 HZ_EVENT_PKG.raise_event(
959 p_event_name => l_event_name,
960 p_event_key => l_key,
961 p_parameters => l_paramlist);
962 l_paramlist.DELETE;
963 EXCEPTION
964 WHEN OTHERS THEN
965 LOG(SQLERRM);
966 log('unable to raise event ||'||l_key);
967 l_paramlist.DELETE;
968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
969 END;
970 END IF;
971
972 IF l_o_ins_flag = 'Y' THEN
973 LOG('Raise oracle.apps.ar.hz.OrgBO.create Event');
974 LOG('CDH_EVENT_ID is: '||l_orgbo_c_evtid);
975 -- Raise oracle.apps.ar.hz.OrgBO.create Event
976 l_event_name := 'oracle.apps.ar.hz.OrgBO.create';
977 l_key := 'oracle.apps.ar.hz.OrgBO.create'||l_orgbo_c_evtid;
978 wf_event.addParameterToList
979 (p_name => 'CDH_EVENT_ID',
980 p_value => l_orgbo_c_evtid,
981 p_parameterlist => l_paramlist);
982 -- add Q_CORRELATION_ID parameter to the List
983 wf_event.addParameterToList
984 (p_name => 'Q_CORRELATION_ID',
985 p_value => l_event_name,
986 p_parameterlist => l_paramlist);
987 /*
988 l_param.SetName( 'CDH_EVENT_ID' );
989 l_param.SetValue(l_orgbo_c_evtid);
990 l_paramlist(l_paramlist.last) := l_param;
991 */
992 BEGIN
993 HZ_EVENT_PKG.raise_event(
994 p_event_name => l_event_name,
995 p_event_key => l_key,
996 p_parameters => l_paramlist);
997 l_paramlist.DELETE;
998 EXCEPTION
999 WHEN OTHERS THEN
1000 LOG(SQLERRM);
1001 log('unable to raise event ||'||l_key);
1002 l_paramlist.DELETE;
1003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004 END;
1005 END IF;
1006
1007 IF l_pc_ins_flag = 'Y' THEN
1008 LOG('Raise oracle.apps.ar.hz.PersonCustBO.create Event');
1009 LOG('CDH_EVENT_ID is: '||l_percustbo_c_evtid);
1010 -- Raise oracle.apps.ar.hz.PersonCustBO.create Event
1011 l_event_name := 'oracle.apps.ar.hz.PersonCustBO.create';
1012 l_key := 'oracle.apps.ar.hz.PersonCustBO.create'||l_percustbo_c_evtid;
1013 wf_event.addParameterToList
1014 (p_name => 'CDH_EVENT_ID',
1015 p_value => l_percustbo_c_evtid,
1016 p_parameterlist => l_paramlist);
1017 -- add Q_CORRELATION_ID parameter to the List
1018 wf_event.addParameterToList
1019 (p_name => 'Q_CORRELATION_ID',
1020 p_value => l_event_name,
1021 p_parameterlist => l_paramlist);
1022 /*
1023 l_param.SetName( 'CDH_EVENT_ID' );
1024 l_param.SetValue(l_percustbo_c_evtid);
1025 l_paramlist(l_paramlist.last) := l_param;
1026 */
1027 BEGIN
1028 HZ_EVENT_PKG.raise_event(
1029 p_event_name => l_event_name,
1030 p_event_key => l_key,
1031 p_parameters => l_paramlist);
1032 l_paramlist.DELETE;
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 LOG(SQLERRM);
1036 log('unable to raise event ||'||l_key);
1037 l_paramlist.DELETE;
1038 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1039 END;
1040 END IF;
1041
1042 IF l_oc_ins_flag = 'Y' THEN
1043 LOG('Raise oracle.apps.ar.hz.OrgCustBO.create Event');
1044 LOG('CDH_EVENT_ID is: '||l_orgcustbo_c_evtid);
1045 -- Raise oracle.apps.ar.hz.OrgCustBO.create Event
1046 l_event_name := 'oracle.apps.ar.hz.OrgCustBO.create';
1047 l_key := 'oracle.apps.ar.hz.OrgCustBO.create'||l_orgcustbo_c_evtid;
1048 wf_event.addParameterToList
1049 (p_name => 'CDH_EVENT_ID',
1050 p_value => l_orgcustbo_c_evtid,
1051 p_parameterlist => l_paramlist);
1055 p_value => l_event_name,
1052 -- add Q_CORRELATION_ID parameter to the List
1053 wf_event.addParameterToList
1054 (p_name => 'Q_CORRELATION_ID',
1056 p_parameterlist => l_paramlist);
1057 /*
1058 l_param.SetName( 'CDH_EVENT_ID' );
1059 l_param.SetValue(l_orgcustbo_c_evtid);
1060 l_paramlist(l_paramlist.last) := l_param;
1061 */
1062 BEGIN
1063 HZ_EVENT_PKG.raise_event(
1064 p_event_name => l_event_name,
1065 p_event_key => l_key,
1066 p_parameters => l_paramlist);
1067 l_paramlist.DELETE;
1068 EXCEPTION
1069 WHEN OTHERS THEN
1070 LOG(SQLERRM);
1071 log('unable to raise event ||'||l_key);
1072 l_paramlist.DELETE;
1073 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1074 END;
1075 END IF;
1076
1077 IF l_p_upd_flag = 'Y' THEN
1078 LOG('Raise oracle.apps.ar.hz.PersonBO.update Event');
1079 LOG('CDH_EVENT_ID is: '||l_perbo_u_evtid);
1080 -- Raise oracle.apps.ar.hz.PersonBO.update Event
1081 l_event_name := 'oracle.apps.ar.hz.PersonBO.update';
1082 l_key := 'oracle.apps.ar.hz.PersonBO.update'||l_perbo_u_evtid;
1083 wf_event.addParameterToList
1084 (p_name => 'CDH_EVENT_ID',
1085 p_value => l_perbo_u_evtid,
1086 p_parameterlist => l_paramlist);
1087 -- add Q_CORRELATION_ID parameter to the List
1088 wf_event.addParameterToList
1089 (p_name => 'Q_CORRELATION_ID',
1090 p_value => l_event_name,
1091 p_parameterlist => l_paramlist);
1092 /*
1093 l_param.SetName( 'CDH_EVENT_ID' );
1094 l_param.SetValue(l_perbo_u_evtid);
1095 l_paramlist(l_paramlist.last) := l_param;
1096 */
1097 BEGIN
1098 HZ_EVENT_PKG.raise_event(
1099 p_event_name => l_event_name,
1100 p_event_key => l_key,
1101 p_parameters => l_paramlist);
1102 l_paramlist.DELETE;
1103 EXCEPTION
1104 WHEN OTHERS THEN
1105 LOG(SQLERRM);
1106 log('unable to raise event ||'||l_key);
1107 l_paramlist.DELETE;
1108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1109 END;
1110 END IF;
1111
1112 IF l_o_upd_flag = 'Y' THEN
1113 LOG('Raise oracle.apps.ar.hz.OrgBO.update Event');
1114 LOG('CDH_EVENT_ID is: '||l_orgbo_u_evtid);
1115 -- Raise oracle.apps.ar.hz.OrgBO.update Event
1116 l_event_name := 'oracle.apps.ar.hz.OrgBO.update';
1117 l_key := 'oracle.apps.ar.hz.OrgBO.update'||l_orgbo_u_evtid;
1118 wf_event.addParameterToList
1119 (p_name => 'CDH_EVENT_ID',
1120 p_value => l_orgbo_u_evtid,
1121 p_parameterlist => l_paramlist);
1122 -- add Q_CORRELATION_ID parameter to the List
1123 wf_event.addParameterToList
1124 (p_name => 'Q_CORRELATION_ID',
1125 p_value => l_event_name,
1126 p_parameterlist => l_paramlist);
1127 /*
1128 l_param.SetName( 'CDH_EVENT_ID' );
1129 l_param.SetValue(l_orgbo_u_evtid);
1130 l_paramlist(l_paramlist.last) := l_param;
1131 */
1132
1133 BEGIN
1134 HZ_EVENT_PKG.raise_event(
1135 p_event_name => l_event_name,
1136 p_event_key => l_key,
1137 p_parameters => l_paramlist);
1138 l_paramlist.DELETE;
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 LOG(SQLERRM);
1142 log('unable to raise event ||'||l_key);
1143 l_paramlist.DELETE;
1144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1145 END;
1146 END IF;
1147
1148 IF l_pc_upd_flag = 'Y' THEN
1149 LOG('Raise oracle.apps.ar.hz.PersonCustBO.update Event');
1150 LOG('CDH_EVENT_ID is: '||l_percustbo_u_evtid);
1151 -- Raise oracle.apps.ar.hz.PersonCustBO.update Event
1152 l_event_name := 'oracle.apps.ar.hz.PersonCustBO.update';
1153 l_key := 'oracle.apps.ar.hz.PersonCustBO.update'||l_percustbo_u_evtid;
1154 wf_event.addParameterToList
1155 (p_name => 'CDH_EVENT_ID',
1156 p_value => l_percustbo_u_evtid,
1157 p_parameterlist => l_paramlist);
1158 -- add Q_CORRELATION_ID parameter to the List
1159 wf_event.addParameterToList
1160 (p_name => 'Q_CORRELATION_ID',
1161 p_value => l_event_name,
1162 p_parameterlist => l_paramlist);
1163 /*
1164 l_param.SetName( 'CDH_EVENT_ID' );
1165 l_param.SetValue(l_percustbo_u_evtid);
1166 l_paramlist(l_paramlist.last) := l_param;
1167 */
1168 BEGIN
1169 HZ_EVENT_PKG.raise_event(
1170 p_event_name => l_event_name,
1171 p_event_key => l_key,
1172 p_parameters => l_paramlist);
1173 l_paramlist.DELETE;
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 LOG(SQLERRM);
1177 log('unable to raise event ||'||l_key);
1178 l_paramlist.DELETE;
1179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1180 END;
1181 END IF;
1182
1183 IF l_oc_upd_flag = 'Y' THEN
1184 LOG('Raise oracle.apps.ar.hz.OrgCustBO.update Event');
1185 LOG('CDH_EVENT_ID is: '||l_orgcustbo_u_evtid);
1186 -- Raise oracle.apps.ar.hz.OrgCustBO.update Event
1187 l_event_name := 'oracle.apps.ar.hz.OrgCustBO.update';
1188 l_key := 'oracle.apps.ar.hz.OrgCustBO.update'||l_orgcustbo_u_evtid;
1189 wf_event.addParameterToList
1190 (p_name => 'CDH_EVENT_ID',
1191 p_value => l_orgcustbo_u_evtid,
1192 p_parameterlist => l_paramlist);
1193 -- add Q_CORRELATION_ID parameter to the List
1194 wf_event.addParameterToList
1195 (p_name => 'Q_CORRELATION_ID',
1196 p_value => l_event_name,
1200 l_param.SetValue(l_orgcustbo_u_evtid);
1197 p_parameterlist => l_paramlist);
1198 /*
1199 l_param.SetName( 'CDH_EVENT_ID' );
1201 l_paramlist(l_paramlist.last) := l_param;
1202 */
1203 BEGIN
1204 HZ_EVENT_PKG.raise_event(
1205 p_event_name => l_event_name,
1206 p_event_key => l_key,
1207 p_parameters => l_paramlist);
1208 l_paramlist.DELETE;
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211 LOG(SQLERRM);
1212 log('unable to raise event ||'||l_key);
1213 l_paramlist.DELETE;
1214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1215 END;
1216 END IF;
1217
1218 commit; -- explicit commit issued because BOT is stamped with event ids bfr
1219
1220 -- This is the ER done as part of bug 4997605.
1221 -- write the objects for which bulk event were raised in bulk
1222 IF l_p_ins_flag = 'Y' THEN
1223 OPEN c_bot_log (l_perbo_c_evtid, G_PER_BO_CODE);
1224 FETCH c_bot_log BULK COLLECT INTO l_ids ,l_pids , l_evts ;
1225 CLOSE c_bot_log;
1226 FOR i IN l_ids.FIRST..l_ids.LAST LOOP
1227 LOG ('evt id:'||l_ids(i)||' party_id:'||l_pids(i)||' BO Code:'||G_PER_BO_CODE||' evtType:'||l_evts(i));
1228 END LOOP;
1229 END IF; -- check to log person.Create event objects end
1230 IF l_o_ins_flag = 'Y' THEN
1231 OPEN c_bot_log (l_orgbo_c_evtid, G_ORG_BO_CODE);
1232 FETCH c_bot_log BULK COLLECT INTO l_ids ,l_pids , l_evts ;
1233 CLOSE c_bot_log;
1234 FOR i IN l_ids.FIRST..l_ids.LAST LOOP
1235 LOG ('evt id:'||l_ids(i)||' party_id:'||l_pids(i)||' BO Code:'||G_ORG_BO_CODE||' evtType:'||l_evts(i));
1236 END LOOP;
1237 END IF; -- check to log Org.Create event objects end
1238 IF l_pc_ins_flag = 'Y' THEN
1239 OPEN c_bot_log (l_percustbo_c_evtid, G_PER_CUST_BO_CODE );
1240 FETCH c_bot_log BULK COLLECT INTO l_ids ,l_pids , l_evts ;
1241 CLOSE c_bot_log;
1242 FOR i IN l_ids.FIRST..l_ids.LAST LOOP
1243 LOG ('evt id:'||l_ids(i)||' party_id:'||l_pids(i)||' BO Code:'||G_PER_CUST_BO_CODE ||' evtType:'||l_evts(i));
1244 END LOOP;
1245 END IF; -- check to log PersonCust.Create event objects end
1246 IF l_oc_ins_flag = 'Y' THEN
1247 OPEN c_bot_log (l_orgcustbo_c_evtid, G_ORG_CUST_BO_CODE);
1248 FETCH c_bot_log BULK COLLECT INTO l_ids ,l_pids , l_evts ;
1249 CLOSE c_bot_log;
1250 FOR i IN l_ids.FIRST..l_ids.LAST LOOP
1251 LOG ('evt id:'||l_ids(i)||' party_id:'||l_pids(i)||' BO Code:'||G_ORG_CUST_BO_CODE ||' evtType:'||l_evts(i));
1252 END LOOP;
1253 END IF; -- check to log OrgCust.Create event objects end
1254 IF l_p_upd_flag = 'Y' THEN
1255 OPEN c_bot_log (l_perbo_u_evtid, G_PER_BO_CODE);
1256 FETCH c_bot_log BULK COLLECT INTO l_ids ,l_pids , l_evts ;
1257 CLOSE c_bot_log;
1258 FOR i IN l_ids.FIRST..l_ids.LAST LOOP
1259 LOG ('evt id:'||l_ids(i)||' party_id:'||l_pids(i)||' BO Code:'||G_PER_BO_CODE||' evtType:'||l_evts(i));
1260 END LOOP;
1261 END IF; -- check to log Person.Update event objects end
1262 IF l_o_upd_flag = 'Y' THEN
1263 OPEN c_bot_log (l_orgbo_u_evtid, G_ORG_BO_CODE);
1264 FETCH c_bot_log BULK COLLECT INTO l_ids ,l_pids , l_evts ;
1265 CLOSE c_bot_log;
1266 FOR i IN l_ids.FIRST..l_ids.LAST LOOP
1267 LOG ('evt id:'||l_ids(i)||' party_id:'||l_pids(i)||' BO Code:'||G_ORG_BO_CODE||' evtType:'||l_evts(i));
1268 END LOOP;
1269 END IF; -- check to log Org.Update event objects end
1270 IF l_oc_upd_flag = 'Y' THEN
1271 OPEN c_bot_log (l_orgcustbo_u_evtid, G_ORG_CUST_BO_CODE);
1272 FETCH c_bot_log BULK COLLECT INTO l_ids ,l_pids , l_evts ;
1273 CLOSE c_bot_log;
1274 FOR i IN l_ids.FIRST..l_ids.LAST LOOP
1275 LOG ('evt id:'||l_ids(i)||' party_id:'||l_pids(i)||' BO Code:'||G_ORG_CUST_BO_CODE ||' evtType:'||l_evts(i));
1276 END LOOP;
1277 END IF; -- check to log OrgCust.Update event objects end
1278 IF l_pc_upd_flag = 'Y' THEN
1279 OPEN c_bot_log (l_percustbo_u_evtid, G_PER_CUST_BO_CODE );
1280 FETCH c_bot_log BULK COLLECT INTO l_ids ,l_pids , l_evts ;
1281 CLOSE c_bot_log;
1282 FOR i IN l_ids.FIRST..l_ids.LAST LOOP
1283 LOG ('evt id:'||l_ids(i)||' party_id:'||l_pids(i)||' BO Code:'||G_PER_CUST_BO_CODE ||' evtType:'||l_evts(i));
1284 END LOOP;
1285 END IF; -- check to log PersonCust.Update event objects end
1286
1287 ELSE
1288 ---------------------------------------
1289 -- raise the one event per object instance
1290 ---------------------------------------
1291 LOG('raise the one event per object instance');
1292 OPEN c_bo_gt;
1293 FETCH c_bo_gt BULK COLLECT INTO l_ids, l_pids, l_bo_codes, l_evts ;
1294 CLOSE c_bo_gt;
1295 l_ct := l_pids.COUNT;
1296 LOG('Total count in GT is:'||to_char(l_ct));
1297
1298 FOR i IN 1.. l_ct LOOP
1299 l_event_name := NULL;
1300 l_key := NULL;
1301 wf_event.addParameterToList
1302 (p_name => 'CDH_EVENT_ID',
1303 p_value => l_ids(i),
1304 p_parameterlist => l_paramlist);
1305 wf_event.addParameterToList
1306 (p_name => 'CDH_OBJECT_ID',
1307 p_value => l_pids(i),
1308 p_parameterlist => l_paramlist);
1309 /*
1310 l_param.SetName( 'CDH_EVENT_ID' );
1311 l_param.SetValue(l_ids(i));
1312 l_paramlist.extend;
1313 l_param.SetName( 'CDH_OBJECT_ID' );
1314 l_param.SetValue(l_pids(i));
1315 l_paramlist(l_paramlist.last) := l_param;
1316 */
1317 LOG('CDH_EVENT_ID is: '||l_ids(i));
1318 LOG('CDH_OBJECT_ID is: '||l_pids(i));
1319 -- LOG(' party_id is:'||l_pids(i));
1320 -- LOG(' event_id is:'||l_ids(i));
1321 CASE
1322 WHEN (l_bo_codes(i) = G_PER_BO_CODE ) AND ( l_evts(i) = 'U' ) THEN
1323 l_event_name := 'oracle.apps.ar.hz.PersonBO.update';
1324 l_key := 'oracle.apps.ar.hz.PersonBO.update'||l_ids(i);
1328 WHEN (l_bo_codes(i) = G_PER_CUST_BO_CODE ) AND ( l_evts(i) = 'U' ) THEN
1325 WHEN (l_bo_codes(i) = G_ORG_BO_CODE ) AND ( l_evts(i) = 'U' ) THEN
1326 l_event_name := 'oracle.apps.ar.hz.OrgBO.update';
1327 l_key := 'oracle.apps.ar.hz.OrgBO.update'||l_ids(i);
1329 l_event_name := 'oracle.apps.ar.hz.PersonCustBO.update';
1330 l_key := 'oracle.apps.ar.hz.PersonCustBO.update'||l_ids(i);
1331 WHEN (l_bo_codes(i) = G_ORG_CUST_BO_CODE ) AND ( l_evts(i) = 'U' ) THEN
1332 l_event_name := 'oracle.apps.ar.hz.OrgCustBO.update';
1333 l_key := 'oracle.apps.ar.hz.OrgCustBO.update'||l_ids(i);
1334 WHEN (l_bo_codes(i) = G_PER_BO_CODE ) AND ( l_evts(i) IS NULL ) THEN
1335 l_event_name := 'oracle.apps.ar.hz.PersonBO.create';
1336 l_key := 'oracle.apps.ar.hz.PersonBO.create'||l_ids(i);
1337 WHEN (l_bo_codes(i) = G_ORG_BO_CODE ) AND ( l_evts(i) IS NULL) THEN
1338 l_event_name := 'oracle.apps.ar.hz.OrgBO.create';
1339 l_key := 'oracle.apps.ar.hz.OrgBO.create'||l_ids(i);
1340 WHEN (l_bo_codes(i) = G_PER_CUST_BO_CODE ) AND ( l_evts(i) IS NULL ) THEN
1341 l_event_name := 'oracle.apps.ar.hz.PersonCustBO.create';
1342 l_key := 'oracle.apps.ar.hz.PersonCustBO.create'||l_ids(i);
1343 WHEN (l_bo_codes(i) = G_ORG_CUST_BO_CODE ) AND ( l_evts(i) IS NULL ) THEN
1344 l_event_name := 'oracle.apps.ar.hz.OrgCustBO.create';
1345 l_key := 'oracle.apps.ar.hz.OrgCustBO.create'||l_ids(i);
1346 ELSE
1347 LOG('invalid Bo and event_type_flag combination');
1348 LOG('BO code:'||l_bo_codes(i)||' evt type:'||l_evts(i));
1349 END CASE;
1350
1351 IF l_event_name IS NOT NULL THEN
1352
1353 LOG('Updating BOT object hierarchy with event_id');
1354 -- update the object hierarchy in BOT, with event id
1355 HZ_BES_BO_UTIL_PKG.upd_bot_evtid_dt (
1356 p_bulk_evt => l_g_bulk_type,
1357 p_evt_id => l_ids(i),
1358 p_child_id => l_pids(i),
1359 p_child_bo_code => l_bo_codes(i),
1360 p_creation_date => l_cc_start_time,
1361 p_evt_type => nvl(l_evts(i),'C'),
1362 p_commit => FALSE,
1363 p_per_ins_evt_id => NULL,
1364 p_per_upd_evt_id => NULL,
1365 p_org_ins_evt_id => NULL,
1366 p_org_upd_evt_id => NULL,
1367 p_perc_ins_evt_id => NULL,
1368 p_perc_upd_evt_id => NULL,
1369 p_orgc_ins_evt_id => NULL,
1370 p_orgc_upd_evt_id => NULL);
1371
1372 -- add Q_CORRELATION_ID parameter to the List
1373 wf_event.addParameterToList
1374 (p_name => 'Q_CORRELATION_ID',
1375 p_value => l_event_name,
1376 p_parameterlist => l_paramlist);
1377 LOG('Raise '||l_event_name);
1378 /*
1379 IF SUBSTR(l_event_name,1,18) <> 'oracle.apps.ar.hz.' THEN
1380 LOG(' not a tca event ');
1381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1382 END IF;
1383 */
1384 BEGIN
1385 HZ_EVENT_PKG.raise_event(
1386 p_event_name => l_event_name,
1387 p_event_key => l_key,
1388 p_parameters => l_paramlist);
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391 LOG(SQLERRM);
1392 log('unable to raise event ||'||l_key);
1393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1394 l_paramlist.DELETE;
1395 END;
1396 l_paramlist.DELETE;
1397
1398 commit; -- explicit commit issued because BOT is stamped with event ids bfr
1399 END IF; --chk for event name not null ends
1400
1401 END LOOP; -- end of loop for raising one event per object instance
1402 END IF; -- end of check to raise bulk event or not
1403
1404 LOG('Updating hz_parties for future short circuiting');
1405 LOG( 'Person Business Object bo_version_number is: '||G_PER_BO_VER);
1406 LOG( 'Organization Business Object bo_version_number is: '||G_ORG_BO_VER);
1407 LOG( 'Person Customer Business Object bo_version_number is: '||G_PER_CUST_BO_VER);
1408 LOG( 'Organization Customer Business Object bo_version_number is: '||G_ORG_CUST_BO_VER);
1409 -- update the hz_parties bo_version number - so that any future
1410 -- runs of this concurrent program can figure out the event type by short circuiting
1411 HZ_BES_BO_UTIL_PKG.upd_hzp_bo_ver (
1412 p_per_bo_ver => G_PER_BO_VER, -- for passing the person bo version number
1413 p_org_bo_ver => G_ORG_BO_VER, -- for passing the org bo version number
1414 p_perc_bo_ver => G_PER_CUST_BO_VER, -- for passing the person cust bo version number
1415 p_orgc_bo_ver => G_ORG_CUST_BO_VER);-- for passing the org cust bo version number
1416
1417 -- LOG('Delete the records that were not part of any event from BOT');
1418
1419 LOG('Concurrent Program completed successfully.');
1420 EXCEPTION
1421 WHEN FND_API.G_EXC_ERROR THEN
1422 outandlog('Error: Aborting concurrent program');
1423 retcode := 2;
1424 errbuf := errbuf || logerror;
1425 FND_FILE.close;
1426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1427 outandlog(SQLERRM);
1428 outandlog('Error: Aborting concurrent program');
1429 retcode := 2;
1430 errbuf := errbuf || logerror;
1431 FND_FILE.close;
1432 WHEN OTHERS THEN
1433 outandlog(SQLERRM);
1434 outandlog('Error: Aborting concurrent program');
1435 retcode := 2;
1436 errbuf := errbuf || logerror;
1437 FND_FILE.close;
1438 END; -- bes_main
1439
1440 -------------------------------------------------------------------------
1441 -------------------------------------------------------------------------
1442 END HZ_BES_BO_RAISE_PKG; -- pkg
[Home] [Help]