[Home] [Help]
PACKAGE BODY: APPS.AS_BUSINESS_EVENT_PVT
Source
1 PACKAGE BODY AS_BUSINESS_EVENT_PVT as
2 /* $Header: asxvbevb.pls 120.1 2005/06/14 01:33:53 appldev $ */
3
4 --
5 -- NAME
6 -- AS_BUSINESS_EVENT_PVT
7 --
8 -- HISTORY
9 -- 9/17/2003 SUMAHALI CREATED
10 --
11 --
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_BUSINESS_EVENT_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12):='asxvbevb.pls';
15
16 G_DUMMY_DATE CONSTANT DATE := to_date('11/11/9999', 'MM/DD/YYYY');
17
18 TYPE AS_EVENT_REC_T IS RECORD (
19 event_name VARCHAR2(240),
20 event_key VARCHAR2(240),
21 event_code VARCHAR2(1)
22 );
23 TYPE AS_EVENT_TABLE_T IS TABLE OF AS_EVENT_REC_T INDEX BY BINARY_INTEGER;
24
25
26 FUNCTION Event_data_delete
27 -- Rule function for event data deletions used as the last subscription to AS events
28 (p_subscription_guid IN RAW,
29 p_event IN OUT NOCOPY WF_EVENT_T)
30 RETURN VARCHAR2
31 IS
32 l_key VARCHAR2(240);
33 BEGIN
34 SAVEPOINT Event_data_delete;
35
36 l_key := p_event.GetEventKey();
37
38 DELETE FROM as_event_data
39 WHERE event_key = l_key;
40
41 RETURN 'SUCCESS';
42
43 EXCEPTION
44
45 WHEN NO_DATA_FOUND THEN
46 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(SQLCODE));
47 FND_MSG_PUB.ADD;
48
49 WF_CORE.CONTEXT('AS_BUSINESS_EVENT_PVT', 'EVENT_DATA_DELETE', p_event.getEventName(), p_subscription_guid);
50 WF_EVENT.setErrorInfo(p_event, 'WARNING');
51
52 RETURN 'WARNING';
53
54 WHEN OTHERS THEN
55 ROLLBACK TO Event_data_delete;
56
57 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(SQLCODE));
58 FND_MSG_PUB.ADD;
59
60 WF_CORE.CONTEXT('AS_BUSINESS_EVENT_PVT', 'EVENT_DATA_DELETE', p_event.getEventName(), p_subscription_guid);
61 WF_EVENT.setErrorInfo(p_event, 'ERROR');
62
63 RETURN 'ERROR';
64 END;
65
66 FUNCTION event(p_event_name IN VARCHAR2) RETURN VARCHAR2
67 -----------------------------------------------
68 -- Return event name if the entered event exist
69 -- Otherwise return NOTFOUND
70 -----------------------------------------------
71 IS
72 RetEvent VARCHAR2(240);
73 BEGIN
74 SELECT name INTO RetEvent
75 FROM wf_events
76 WHERE name = p_event_name;
77 IF SQL%NOTFOUND THEN
78 RetEvent := 'NOTFOUND';
79 END IF;
80 RETURN RetEvent;
81 END event;
82
83
84 FUNCTION exist_subscription(p_event_name IN VARCHAR2) RETURN VARCHAR2
85 -----------------------------------------------------------------------
86 -- Return 'Y' if the subscription exist
87 -- Otherwise it returns 'N'
88 -----------------------------------------------------------------------
89 IS
90 CURSOR cu0 IS
91 SELECT 'Y'
92 FROM wf_events eve,
93 wf_event_subscriptions sub
94 WHERE eve.name = p_event_name
95 AND eve.status = 'ENABLED'
96 AND eve.guid = sub.event_filter_guid
97 AND UPPER(sub.rule_function) = 'AS_BUSINESS_EVENT_PVT.EVENT_DATA_DELETE'
98 AND sub.status = 'ENABLED'
99 AND sub.source_type = 'LOCAL'
100 AND EXISTS (
101 SELECT 'X'
102 FROM wf_event_subscriptions sub1
103 WHERE sub1.event_filter_guid = eve.guid
104 AND UPPER(sub1.rule_function) <> 'AS_BUSINESS_EVENT_PVT.EVENT_DATA_DELETE'
105 AND sub1.status = 'ENABLED'
106 AND sub1.source_type = 'LOCAL')
107 ;
108
109
110 l_yn VARCHAR2(1);
111 BEGIN
112 OPEN cu0;
113 FETCH cu0 INTO l_yn;
114 IF cu0%NOTFOUND THEN
115 l_yn := 'N';
116 END IF;
117 CLOSE cu0;
118 RETURN l_yn;
119 END exist_subscription;
120
121
122 FUNCTION item_key(p_event_name IN VARCHAR2) RETURN VARCHAR2
123 -----------------------------------------------------
124 -- Return Item_Key according to As Event to be raised
125 -- Item_Key is <Event_Name>-AS_BUSINESS_EVENT_S.nextval
126 -----------------------------------------------------
127 IS
128 RetKey VARCHAR2(240);
129 BEGIN
130 SELECT p_event_name || AS_BUSINESS_EVENT_S.nextval INTO RetKey FROM DUAL;
131 RETURN RetKey;
132 END item_key;
133
134
135 PROCEDURE Copy_Event_Data(
136 p_old_event_key IN VARCHAR2,
137 p_new_event_key IN VARCHAR2
138 ) IS
139 BEGIN
140 insert into AS_EVENT_DATA (
141 EVENT_DATA_ID,
142 EVENT_KEY, OBJECT_STATE,
143 LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,
144 LAST_UPDATE_LOGIN,
145 CHAR01, CHAR02, CHAR03, CHAR04, CHAR05, CHAR06, CHAR07, CHAR08,
146 CHAR09, CHAR10, CHAR11, CHAR12, CHAR13, CHAR14, CHAR15, CHAR16,
147 CHAR17, CHAR18, CHAR19, CHAR20, CHAR21, CHAR22, CHAR23, CHAR24,
148 CHAR25, CHAR26, CHAR27, CHAR28, CHAR29, CHAR30, CHAR31, CHAR32,
149 CHAR33, CHAR34, CHAR35, CHAR36, CHAR37, CHAR38, CHAR39, CHAR40,
150 CHAR41, CHAR42, CHAR43, CHAR44, CHAR45, CHAR46, CHAR47, CHAR48,
151 CHAR49, CHAR50, CHAR51, CHAR52, CHAR53, CHAR54, CHAR55, CHAR56,
152 CHAR57, CHAR58, CHAR59, CHAR60, CHAR61, CHAR62, CHAR63, CHAR64,
153 CHAR65, CHAR66, CHAR67, CHAR68, CHAR69, CHAR70, CHAR71, CHAR72,
154 CHAR73, CHAR74, CHAR75, CHAR76, CHAR77, CHAR78, CHAR79, CHAR80,
155 NUM01, NUM02, NUM03, NUM04, NUM05, NUM06, NUM07, NUM08, NUM09,
156 NUM10, NUM11, NUM12, NUM13, NUM14, NUM15, NUM16, NUM17, NUM18,
157 NUM19, NUM20, NUM21, NUM22, NUM23, NUM24, NUM25, NUM26, NUM27,
158 NUM28, NUM29, NUM30,
159 DATE01, DATE02, DATE03, DATE04, DATE05, DATE06, DATE07, DATE08,
160 DATE09, DATE10, DATE11, DATE12, DATE13, DATE14, DATE15
161 )
162 select
163 AS_EVENT_DATA_S.nextval,
164 p_new_event_key, OBJECT_STATE,
165 SYSDATE, SYSDATE, CREATED_BY, LAST_UPDATED_BY,
166 LAST_UPDATE_LOGIN,
167 CHAR01, CHAR02, CHAR03, CHAR04, CHAR05, CHAR06, CHAR07, CHAR08,
168 CHAR09, CHAR10, CHAR11, CHAR12, CHAR13, CHAR14, CHAR15, CHAR16,
169 CHAR17, CHAR18, CHAR19, CHAR20, CHAR21, CHAR22, CHAR23, CHAR24,
170 CHAR25, CHAR26, CHAR27, CHAR28, CHAR29, CHAR30, CHAR31, CHAR32,
171 CHAR33, CHAR34, CHAR35, CHAR36, CHAR37, CHAR38, CHAR39, CHAR40,
172 CHAR41, CHAR42, CHAR43, CHAR44, CHAR45, CHAR46, CHAR47, CHAR48,
173 CHAR49, CHAR50, CHAR51, CHAR52, CHAR53, CHAR54, CHAR55, CHAR56,
174 CHAR57, CHAR58, CHAR59, CHAR60, CHAR61, CHAR62, CHAR63, CHAR64,
175 CHAR65, CHAR66, CHAR67, CHAR68, CHAR69, CHAR70, CHAR71, CHAR72,
176 CHAR73, CHAR74, CHAR75, CHAR76, CHAR77, CHAR78, CHAR79, CHAR80,
177 NUM01, NUM02, NUM03, NUM04, NUM05, NUM06, NUM07, NUM08, NUM09,
178 NUM10, NUM11, NUM12, NUM13, NUM14, NUM15, NUM16, NUM17, NUM18,
179 NUM19, NUM20, NUM21, NUM22, NUM23, NUM24, NUM25, NUM26, NUM27,
180 NUM28, NUM29, NUM30,
181 DATE01, DATE02, DATE03, DATE04, DATE05, DATE06, DATE07, DATE08,
182 DATE09, DATE10, DATE11, DATE12, DATE13, DATE14, DATE15
183 from AS_EVENT_DATA where event_key = p_old_event_key;
184 END Copy_Event_Data;
185
186
187 -- This function is for subscribing, for testing/debugging of business events,
188 -- to business events raised by Opportunity and customer sales team modules
189 -- which log data to as_event_data table. It copies the event_data to new rows
190 -- in the same as_event_data table so that the debug data can be seen after it
191 -- is automatically deleted. It creates a new event_key like 'debug<sequence>',
192 -- The first rows contain the event parameters one by one. The parameter name is
193 -- stored in CHAR01 and value in CHAR02. Two pseudo parameters EVENT_NAME and
194 -- EVENT_KEY are added. The subsequent rows contain as_event_data corresponding
195 -- to the event key received. It is the users responsibility to delete these
196 -- debug rows from the as_event_data table.
197 FUNCTION Test_event
198 -- Rule function for event data deletions used as the last subscription to AS events
199 (p_subscription_guid IN RAW,
200 p_event IN OUT NOCOPY WF_EVENT_T)
201 RETURN VARCHAR2
202 IS
203 l_api_name CONSTANT VARCHAR2(30) := 'Test_event';
204 l_inded NUMBER;
205 l_event_name VARCHAR2(240) := p_event.GetEventName();
206 l_key VARCHAR2(240) := p_event.GetEventKey();
207 l_new_key VARCHAR2(240);
208 l_param WF_PARAMETER_T;
209 l_parameters WF_PARAMETER_LIST_T := p_event.GetParameterList();
210 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
211 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Test_event';
212 BEGIN
213 SAVEPOINT Test_event;
214
215 -- Debug Message
216 IF l_debug THEN
217 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
218 'Private API: ' || l_api_name || ' start');
219 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
220 'Event Name = ' || l_event_name || ', key = ' || l_key);
221 END IF;
222
223 l_new_key := item_key( 'debug' );
224
225
226 FOR l_index IN 1..l_parameters.last LOOP
227 l_param := l_parameters(l_index);
228 insert into AS_EVENT_DATA (
229 EVENT_DATA_ID,
230 EVENT_KEY, OBJECT_STATE, CHAR01, CHAR02,
231 LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,
232 LAST_UPDATE_LOGIN
233 )
234 values (
235 AS_EVENT_DATA_S.nextval,
236 l_new_key, 'AAA', l_param.GetName(), l_param.GetValue(),
237 SYSDATE, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
238 FND_GLOBAL.CONC_LOGIN_ID
239 );
240 IF l_debug THEN
241 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
242 l_param.GetName() || ' : ' || l_param.GetValue()) ;
243 END IF;
244 END LOOP;
245
246 insert into AS_EVENT_DATA (
247 EVENT_DATA_ID,
248 EVENT_KEY, OBJECT_STATE, CHAR01, CHAR02,
249 LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,
250 LAST_UPDATE_LOGIN
251 )
252 values (
253 AS_EVENT_DATA_S.nextval,
254 l_new_key, 'AAA', 'EVENT_NAME', l_event_name,
255 SYSDATE, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
256 FND_GLOBAL.CONC_LOGIN_ID
257 );
258
259 insert into AS_EVENT_DATA (
260 EVENT_DATA_ID,
261 EVENT_KEY, OBJECT_STATE, CHAR01, CHAR02,
262 LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN
264 )
265 values (
266 AS_EVENT_DATA_S.nextval,
267 l_new_key, 'AAA', 'EVENT_KEY', l_key,
268 SYSDATE, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
269 FND_GLOBAL.CONC_LOGIN_ID
270 );
271
272 Copy_Event_Data(l_key, l_new_key);
273
274 -- Debug Message
275 IF l_debug THEN
276 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
277 'Private API: ' || l_api_name || ' end');
278 END IF;
279
280 RETURN 'SUCCESS';
281
282 EXCEPTION
283
284 WHEN NO_DATA_FOUND THEN
285 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(SQLCODE));
286 FND_MSG_PUB.ADD;
287
288 WF_CORE.CONTEXT('AS_BUSINESS_EVENT_PVT', 'TEST_EVENT', p_event.getEventName(), p_subscription_guid);
289 WF_EVENT.setErrorInfo(p_event, 'WARNING');
290
291 RETURN 'WARNING';
292
293 WHEN OTHERS THEN
294 ROLLBACK TO Test_event;
295
296 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(SQLCODE));
297 FND_MSG_PUB.ADD;
298
299 WF_CORE.CONTEXT('AS_BUSINESS_EVENT_PVT', 'TEST_EVENT', p_event.getEventName(), p_subscription_guid);
300 WF_EVENT.setErrorInfo(p_event, 'ERROR');
301
302 RETURN 'ERROR';
303 END Test_event;
304
305 PROCEDURE AddParamEnvToList
306 ------------------------------------------------------
307 -- Add Application-Context parameter to the enter list
308 ------------------------------------------------------
309 ( x_list IN OUT NOCOPY WF_PARAMETER_LIST_T,
310 p_user_id IN VARCHAR2 DEFAULT NULL,
311 p_resp_id IN VARCHAR2 DEFAULT NULL,
312 p_resp_appl_id IN VARCHAR2 DEFAULT NULL,
313 p_security_group_id IN VARCHAR2 DEFAULT NULL,
314 p_org_id IN VARCHAR2 DEFAULT NULL)
315 IS
316 l_user_id VARCHAR2(255) := p_user_id;
317 l_resp_appl_id VARCHAR2(255) := p_resp_appl_id;
318 l_resp_id VARCHAR2(255) := p_resp_id;
319 l_security_group_id VARCHAR2(255) := p_security_group_id;
320 l_org_id VARCHAR2(255) := p_org_id;
321 l_param WF_PARAMETER_T;
322 l_rang NUMBER;
323 BEGIN
324 l_rang := 0;
325
326 IF l_user_id IS NULL THEN
327 l_user_id := fnd_profile.value( 'USER_ID');
328 END IF;
329
330 l_param := WF_PARAMETER_T( NULL, NULL );
331 -- fill the parameters list
332 x_list.extend;
333 l_param.SetName( 'USER_ID' );
334 l_param.SetValue( l_user_id);
335 l_rang := l_rang + 1;
336 x_list(l_rang) := l_param;
337
338 IF l_resp_id IS NULL THEN
339 l_resp_id := fnd_profile.value( 'RESP_ID');
340 END IF;
341
342 l_param := WF_PARAMETER_T( NULL, NULL );
343 -- fill the parameters list
344 x_list.extend;
345 l_param.SetName( 'RESP_ID' );
346 l_param.SetValue( l_resp_id );
347 l_rang := l_rang + 1;
348 x_list(l_rang) := l_param;
349
350 IF l_resp_appl_id IS NULL THEN
351 l_resp_appl_id := fnd_profile.value( 'RESP_APPL_ID');
352 END IF;
353
354 l_param := WF_PARAMETER_T( NULL, NULL );
358 l_param.SetValue( l_resp_appl_id );
355 -- fill the parameters list
356 x_list.extend;
357 l_param.SetName( 'RESP_APPL_ID' );
359 l_rang := l_rang + 1;
360 x_list(l_rang) := l_param;
361
362 IF l_security_group_id IS NULL THEN
363 --l_security_group_id := fnd_profile.value( 'SECURITY_GROUP_ID');
364 /* BugNo: 3007012 */
365 l_security_group_id := fnd_global.security_group_id;
366 END IF;
367 l_param := WF_PARAMETER_T( NULL, NULL );
368 -- fill the parameters list
369 x_list.extend;
370 l_param.SetName( 'SECURITY_GROUP_ID' );
371 l_param.SetValue( l_security_group_id );
372 l_rang := l_rang + 1;
373 x_list(l_rang) := l_param;
374
375 IF l_org_id IS NULL THEN
376 l_org_id := fnd_profile.value( 'ORG_ID');
377 END IF;
378
379 l_param := WF_PARAMETER_T( NULL, NULL );
380 -- fill the parameters list
381 x_list.extend;
382 l_param.SetName( 'ORG_ID' );
383 l_param.SetValue(l_org_id );
384 l_rang := l_rang + 1;
385 x_list(l_rang) := l_param;
386
387 END;
388
389
390 PROCEDURE raise_event
391 ----------------------------------------------
392 -- Check if Event exist
393 -- Check if Event is like 'oracle.apps.as.%'
394 -- Get the item_key
395 -- Raise event
396 ----------------------------------------------
397 (p_event_name IN VARCHAR2,
398 p_event_key IN VARCHAR2,
399 p_data IN CLOB DEFAULT NULL,
400 p_parameters IN wf_parameter_list_t DEFAULT NULL)
401 IS
402 l_api_name CONSTANT VARCHAR2(30) := 'raise_event';
403 l_event VARCHAR2(240);
404 l_param WF_PARAMETER_T;
405 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
406 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.raise_event';
407 BEGIN
408
409 SAVEPOINT as_raise_event;
410
411 -- Debug Message
412 IF l_debug THEN
413 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
414 'Private API: ' || l_api_name || ' start');
415 END IF;
416
417 l_event := event(p_event_name);
418
419 IF l_event = 'NOTFOUND' THEN
420 FND_MESSAGE.SET_NAME( 'AS', 'AS_EVENTNOTFOUND');
421 FND_MESSAGE.SET_TOKEN( 'EVENT' ,p_event_name );
422 FND_MSG_PUB.ADD;
423
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425 END IF;
426
427 IF SUBSTR(l_event,1,15) <> 'oracle.apps.as.' THEN
428 FND_MESSAGE.SET_NAME( 'AS', 'AS_EVENTNOTAS');
429 FND_MESSAGE.SET_TOKEN( 'EVENT' ,p_event_name );
430 FND_MSG_PUB.ADD;
431
432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
433 END IF;
434
435 -- Debug Message
436 IF l_debug THEN
437 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
438 'Parameters For Event ' || l_event || ' : ' || p_event_key) ;
439 FOR l_index IN 1..p_parameters.last LOOP
440 l_param := p_parameters(l_index);
441 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
442 l_param.GetName() || ' : ' || l_param.GetValue()) ;
443 END LOOP;
444 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'END Parameters') ;
445 END IF;
446
447 Wf_Event.Raise
448 ( p_event_name => l_event,
449 p_event_key => p_event_key,
450 p_parameters => p_parameters,
451 p_event_data => p_data);
452
453 -- Debug Message
454 IF l_debug THEN
455 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
456 'Private API: ' || l_api_name || ' end');
457 END IF;
458 END raise_event;
459
460
461 -- Classifies an Opportunity Status Code and returns one of the following codes
462 -- O - Open
463 -- W - Won
464 -- L - Lost
465 -- C - Closed
466 -- I - Invalid status code
467 FUNCTION Classify_Status(
468 p_status IN VARCHAR2)
469 RETURN VARCHAR2
470 IS
471
472 Cursor c_status_flags(p_status VARCHAR2) IS
473 select enabled_flag, opp_flag, opp_open_status_flag, win_loss_indicator
474 from as_statuses_b
475 where status_code = p_status;
476
477 l_status_class VARCHAR2(1) := 'I' ;
478 l_enabled_flag VARCHAR2(1) ;
479 l_opp_flag VARCHAR2(1) ;
480 l_opp_open_status_flag VARCHAR2(1) ;
481 l_win_loss_indicator VARCHAR2(1) ;
482
483 BEGIN
484 OPEN c_status_flags(p_status);
485 FETCH c_status_flags INTO l_enabled_flag, l_opp_flag, l_opp_open_status_flag,
486 l_win_loss_indicator;
487
488 IF c_status_flags%FOUND AND l_enabled_flag = 'Y' AND l_opp_flag = 'Y' THEN
489 IF l_opp_open_status_flag = 'Y' THEN
490 l_status_class := 'O';
491 ELSE
492 IF l_win_loss_indicator = 'W' THEN
493 l_status_class := 'W';
494 ELSIF l_win_loss_indicator = 'L' THEN
495 l_status_class := 'L';
496 ELSE
497 l_status_class := 'C';
498 END IF;
499 END IF;
500 END IF;
501
505 END;
502 CLOSE c_status_flags;
503
504 RETURN l_status_class;
506
507
508 -- Takes Data snapshot of Opportunity header. Used before and after
509 -- header update. Take care to keep the function CompareOppSnapShots in sync
510 -- with this.
511 PROCEDURE OppDataSnapShot
512 (p_item_key IN VARCHAR2,
513 p_lead_id IN NUMBER,
514 p_indicator IN VARCHAR2) IS
515 BEGIN
516
517 insert into AS_EVENT_DATA (
518 EVENT_DATA_ID,
519 EVENT_KEY, OBJECT_STATE,
520 LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,
521 LAST_UPDATE_LOGIN,
522
523 NUM01, NUM02,
524 NUM03, NUM04,
525 NUM05,
526 NUM06, NUM07,
527 NUM08,
528 NUM09, NUM10,
529 NUM11, NUM12,
530 NUM13,
531 NUM14,
532 NUM15,
533 NUM16, NUM17,
534 NUM18,
535 NUM19,
536 NUM20,
537 NUM21,
538
539 DATE01, DATE02, DATE03,
540
541 CHAR01, CHAR02,
542 CHAR03, CHAR04,
543 CHAR05, CHAR06,
544 CHAR07,
545 CHAR08, CHAR09,
546 CHAR10, CHAR11,
547 CHAR12,
548 CHAR13,
549 CHAR14,
550 CHAR15, CHAR16,
551 CHAR17, CHAR18,
552 CHAR19, CHAR20,
553 CHAR21, CHAR22,
554 CHAR23, CHAR24,
555 CHAR25, CHAR26,
556 CHAR27, CHAR28,
557 CHAR29, CHAR30,
558 CHAR31,
559 CHAR32,
560 CHAR33,
561 CHAR34,
562 CHAR35,
563 CHAR36, CHAR37)
564 select
565 AS_EVENT_DATA_S.nextval,
566 p_item_key, p_indicator,
567 SYSDATE, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
568 FND_GLOBAL.CONC_LOGIN_ID,
569
570 -- NUM01 - NUM21
571 lead_id, customer_id,
572 address_id, owner_salesforce_id,
573 owner_sales_group_id,
574 sales_stage_id, win_probability,
575 customer_budget,
576 sales_methodology_id, total_amount,
577 last_updated_by, created_by,
578 close_competitor_id,
579 source_promotion_id,
580 end_user_customer_id,
581 end_user_address_id, org_id,
582 price_list_id,
583 incumbent_partner_resource_id,
584 incumbent_partner_party_id,
585 offer_id,
586
587 -- DATE01 - DATE03
588 last_update_date, creation_date,
589 decision_date,
590
591 -- CHAR01 - CHAR37
592 lead_number, status,
593 orig_system_reference, channel_code,
594 currency_code, close_reason,
595 close_competitor_code,
596 close_competitor, close_comment,
597 description, parent_project,
598 auto_assignment_type,
599 prm_assignment_type,
600 decision_timeframe_code,
601 attribute_category, attribute1,
602 attribute2, attribute3,
603 attribute4, attribute5,
604 attribute6, attribute7,
605 attribute8, attribute9,
606 attribute10, attribute11,
607 attribute12, attribute13,
608 attribute14, attribute15,
609 vehicle_response_code,
610 budget_status_code,
611 prm_exec_sponsor_flag,
612 prm_prj_lead_in_place_flag,
613 prm_ind_classification_code,
614 prm_lead_type, freeze_flag
615 from as_leads_all where lead_id = p_lead_id;
616
617 END OppDataSnapShot;
618
619
620 -- Takes Data snapshot of Opportunity Lines. Used before and after
621 -- line update. Take care to keep the function DiffOppLineSnapShots in sync
622 -- with this.
623 PROCEDURE OppLineDataSnapShot
624 (p_item_key IN VARCHAR2,
625 p_lead_id IN NUMBER,
626 p_indicator IN VARCHAR2) IS
627 BEGIN
628
629 insert into AS_EVENT_DATA (
630 EVENT_DATA_ID,
631 EVENT_KEY, OBJECT_STATE,
632 LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,
633 LAST_UPDATE_LOGIN,
634
635 NUM01, NUM02,
636 NUM03, NUM05,
637 NUM12,
638 NUM13, NUM14,
639 NUM15, NUM19,
640 NUM22, NUM23,
641 NUM24,
642
643 DATE01, DATE02,
644 DATE04, DATE05,
645 DATE06,
646
647 CHAR01, CHAR03,
648 CHAR06, CHAR07,
649 CHAR08, CHAR09,
650 CHAR10, CHAR11,
651 CHAR12, CHAR13,
652 CHAR14, CHAR15,
653 CHAR16, CHAR17,
654 CHAR18, CHAR19,
655 CHAR20, CHAR21,
656 CHAR22)
657 select
658 AS_EVENT_DATA_S.nextval,
659 p_item_key, p_indicator,
660 SYSDATE, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
661 FND_GLOBAL.CONC_LOGIN_ID,
662
663 -- NUM01-03, 05, 12-15, 19, 22-24
664 lead_line_id, lead_id,
665 last_updated_by, created_by,
666 inventory_item_id,
667 organization_id, quantity,
668 total_amount, org_id,
669 offer_id, source_promotion_id,
670 price_volume_margin,
671
672 -- DATE01-02, 04-06
673 last_update_date, creation_date,
674 ship_date, decision_date,
675 forecast_date,
676
677 -- CHAR01, 03, 06-22
678 status_code, uom_code,
682 attribute6, attribute7,
679 attribute_category, attribute1,
680 attribute2, attribute3,
681 attribute4, attribute5,
683 attribute8, attribute9,
684 attribute10, attribute11,
685 attribute12, attribute13,
686 attribute14, attribute15,
687 rolling_forecast_flag
688 from as_lead_lines where lead_id = p_lead_id;
689
690 END OppLineDataSnapShot;
691
692
693 -- Takes Data snapshot of SalesTeam. Used before and after SalesTeam
694 -- update. Take care to keep the function DiffSTeamSnapShots in sync
695 -- with this.
696 PROCEDURE OppSTeamDataSnapShot
697 (p_item_key IN VARCHAR2,
698 p_lead_id IN NUMBER,
699 p_indicator IN VARCHAR2) IS
700 BEGIN
701
702 insert into AS_EVENT_DATA (
703 EVENT_DATA_ID,
704 EVENT_KEY, OBJECT_STATE,
705 LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,
706 LAST_UPDATE_LOGIN,
707
708 NUM01, NUM02, NUM03, NUM04)
709 select
710 AS_EVENT_DATA_S.nextval,
711 p_item_key, p_indicator,
712 SYSDATE, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
713 FND_GLOBAL.CONC_LOGIN_ID,
714
715 -- NUM01-09
716 salesforce_id, sales_group_id, access_id, lead_id
717 from AS_SALES_TEAM_EMP_V where lead_id = p_lead_id;
718
719 END OppSTeamDataSnapShot;
720
721
722 -- Takes Data snapshot of SalesTeam. Used before and after SalesTeam
723 -- update. Take care to keep the function DiffSTeamSnapShots in sync
724 -- with this.
725 PROCEDURE CustSTeamDataSnapShot
726 (p_item_key IN VARCHAR2,
727 p_cust_id IN NUMBER,
728 p_indicator IN VARCHAR2) IS
729 BEGIN
730
731 insert into AS_EVENT_DATA (
732 EVENT_DATA_ID,
733 EVENT_KEY, OBJECT_STATE,
734 LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY,
735 LAST_UPDATE_LOGIN,
736
737 NUM01, NUM02, NUM03, NUM04, NUM05)
738 select
739 AS_EVENT_DATA_S.nextval,
740 p_item_key, p_indicator,
741 SYSDATE, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.USER_ID,
742 FND_GLOBAL.CONC_LOGIN_ID,
743
744 -- NUM01-09
745 salesforce_id, sales_group_id, access_id, customer_id, address_id
746 from AS_SALES_TEAM_EMP_V where customer_id = p_cust_id and lead_id IS NULL and
747 sales_lead_id IS NULL;
748
749 END CustSTeamDataSnapShot;
750
751
752 -- Compares New and old Opportunity Header SnapShots. Returns a combination of
753 -- one the following codes:
754 -- U - Opportunity Updated : Present if code is not N
755 -- W - Opportunity Won
756 -- L - Opportunity Lost
757 -- N - Opportunity not updated : No other code is combined with this
758 -- Take care to keep the procedure OppDataSnapShot in sync
759 -- with this.
760 FUNCTION DiffOppSnapShots(p_event_key IN VARCHAR2) RETURN VARCHAR2
761 IS
762
763 l_old_status VARCHAR(1);
764 l_new_status VARCHAR(1);
765 l_RetVal VARCHAR(4) := 'N';
766
767 Cursor c_opp_data(p_item_key VARCHAR2, p_indicator VARCHAR2) IS
768 select
769 NUM01, NUM02,
770 NUM03, NUM04,
771 NUM05,
772 NUM06, NUM07,
773 NUM08,
774 NUM09, NUM10,
775 NUM11, NUM12,
776 NUM13,
777 NUM14,
778 NUM15,
779 NUM16, NUM17,
780 NUM18,
781 NUM19,
782 NUM20,
783 NUM21,
784
785 DATE01, DATE02, DATE03,
786
787 CHAR01, CHAR02,
788 CHAR03, CHAR04,
789 CHAR05, CHAR06,
790 CHAR07,
791 CHAR08, CHAR09,
792 CHAR10, CHAR11,
793 CHAR12,
794 CHAR13,
795 CHAR14,
796 CHAR15, CHAR16,
797 CHAR17, CHAR18,
798 CHAR19, CHAR20,
799 CHAR21, CHAR22,
800 CHAR23, CHAR24,
801 CHAR25, CHAR26,
802 CHAR27, CHAR28,
803 CHAR29, CHAR30,
804 CHAR31,
805 CHAR32,
806 CHAR33,
807 CHAR34,
808 CHAR35,
809 CHAR36, CHAR37
810 from as_event_data
811 where event_key = p_item_key AND object_state = p_indicator;
812
813 l_old_rec c_opp_data%ROWTYPE;
814 l_new_rec c_opp_data%ROWTYPE;
815
816 BEGIN
817
818 OPEN c_opp_data(p_event_key, 'OLD');
819 FETCH c_opp_data INTO l_old_rec;
820 CLOSE c_opp_data;
821
822 OPEN c_opp_data(p_event_key, 'NEW');
823 FETCH c_opp_data INTO l_new_rec;
824 CLOSE c_opp_data;
825
826 IF
827 nvl(l_old_rec.NUM01, -99) <> nvl(l_new_rec.NUM01, -99) OR
828 nvl(l_old_rec.NUM02, -99) <> nvl(l_new_rec.NUM02, -99) OR
829 nvl(l_old_rec.NUM03, -99) <> nvl(l_new_rec.NUM03, -99) OR
830 nvl(l_old_rec.NUM04, -99) <> nvl(l_new_rec.NUM04, -99) OR
831 nvl(l_old_rec.NUM05, -99) <> nvl(l_new_rec.NUM05, -99) OR
832 nvl(l_old_rec.NUM06, -99) <> nvl(l_new_rec.NUM06, -99) OR
833 nvl(l_old_rec.NUM07, -99) <> nvl(l_new_rec.NUM07, -99) OR
834 nvl(l_old_rec.NUM08, -99) <> nvl(l_new_rec.NUM08, -99) OR
835 nvl(l_old_rec.NUM09, -99) <> nvl(l_new_rec.NUM09, -99) OR
836 nvl(l_old_rec.NUM10, -99) <> nvl(l_new_rec.NUM10, -99) OR
840 nvl(l_old_rec.NUM14, -99) <> nvl(l_new_rec.NUM14, -99) OR
837 nvl(l_old_rec.NUM11, -99) <> nvl(l_new_rec.NUM11, -99) OR
838 nvl(l_old_rec.NUM12, -99) <> nvl(l_new_rec.NUM12, -99) OR
839 nvl(l_old_rec.NUM13, -99) <> nvl(l_new_rec.NUM13, -99) OR
841 nvl(l_old_rec.NUM15, -99) <> nvl(l_new_rec.NUM15, -99) OR
842 nvl(l_old_rec.NUM16, -99) <> nvl(l_new_rec.NUM16, -99) OR
843 nvl(l_old_rec.NUM17, -99) <> nvl(l_new_rec.NUM17, -99) OR
844 nvl(l_old_rec.NUM18, -99) <> nvl(l_new_rec.NUM18, -99) OR
845 nvl(l_old_rec.NUM19, -99) <> nvl(l_new_rec.NUM19, -99) OR
846 nvl(l_old_rec.NUM20, -99) <> nvl(l_new_rec.NUM20, -99) OR
847 nvl(l_old_rec.NUM21, -99) <> nvl(l_new_rec.NUM21, -99) OR
848
849 --nvl(l_old_rec.DATE01, G_DUMMY_DATE) <> nvl(l_new_rec.DATE01, G_DUMMY_DATE) OR
850 nvl(l_old_rec.DATE02, G_DUMMY_DATE) <> nvl(l_new_rec.DATE02, G_DUMMY_DATE) OR
851 nvl(l_old_rec.DATE03, G_DUMMY_DATE) <> nvl(l_new_rec.DATE03, G_DUMMY_DATE) OR
852
853 nvl(l_old_rec.CHAR01, '_$') <> nvl(l_new_rec.CHAR01, '_$') OR
854 nvl(l_old_rec.CHAR02, '_$') <> nvl(l_new_rec.CHAR02, '_$') OR
855 nvl(l_old_rec.CHAR03, '_$') <> nvl(l_new_rec.CHAR03, '_$') OR
856 nvl(l_old_rec.CHAR04, '_$') <> nvl(l_new_rec.CHAR04, '_$') OR
857 nvl(l_old_rec.CHAR05, '_$') <> nvl(l_new_rec.CHAR05, '_$') OR
858 nvl(l_old_rec.CHAR06, '_$') <> nvl(l_new_rec.CHAR06, '_$') OR
859 nvl(l_old_rec.CHAR07, '_$') <> nvl(l_new_rec.CHAR07, '_$') OR
860 nvl(l_old_rec.CHAR08, '_$') <> nvl(l_new_rec.CHAR08, '_$') OR
861 nvl(l_old_rec.CHAR09, '_$') <> nvl(l_new_rec.CHAR09, '_$') OR
862 nvl(l_old_rec.CHAR10, '_$') <> nvl(l_new_rec.CHAR10, '_$') OR
863 nvl(l_old_rec.CHAR11, '_$') <> nvl(l_new_rec.CHAR11, '_$') OR
864 nvl(l_old_rec.CHAR12, '_$') <> nvl(l_new_rec.CHAR12, '_$') OR
865 nvl(l_old_rec.CHAR13, '_$') <> nvl(l_new_rec.CHAR13, '_$') OR
866 nvl(l_old_rec.CHAR14, '_$') <> nvl(l_new_rec.CHAR14, '_$') OR
867 nvl(l_old_rec.CHAR15, '_$') <> nvl(l_new_rec.CHAR15, '_$') OR
868 nvl(l_old_rec.CHAR16, '_$') <> nvl(l_new_rec.CHAR16, '_$') OR
869 nvl(l_old_rec.CHAR17, '_$') <> nvl(l_new_rec.CHAR17, '_$') OR
870 nvl(l_old_rec.CHAR18, '_$') <> nvl(l_new_rec.CHAR18, '_$') OR
871 nvl(l_old_rec.CHAR19, '_$') <> nvl(l_new_rec.CHAR19, '_$') OR
872 nvl(l_old_rec.CHAR20, '_$') <> nvl(l_new_rec.CHAR20, '_$') OR
873 nvl(l_old_rec.CHAR21, '_$') <> nvl(l_new_rec.CHAR21, '_$') OR
874 nvl(l_old_rec.CHAR22, '_$') <> nvl(l_new_rec.CHAR22, '_$') OR
875 nvl(l_old_rec.CHAR23, '_$') <> nvl(l_new_rec.CHAR23, '_$') OR
876 nvl(l_old_rec.CHAR24, '_$') <> nvl(l_new_rec.CHAR24, '_$') OR
877 nvl(l_old_rec.CHAR25, '_$') <> nvl(l_new_rec.CHAR25, '_$') OR
878 nvl(l_old_rec.CHAR26, '_$') <> nvl(l_new_rec.CHAR26, '_$') OR
879 nvl(l_old_rec.CHAR27, '_$') <> nvl(l_new_rec.CHAR27, '_$') OR
880 nvl(l_old_rec.CHAR28, '_$') <> nvl(l_new_rec.CHAR28, '_$') OR
881 nvl(l_old_rec.CHAR29, '_$') <> nvl(l_new_rec.CHAR29, '_$') OR
882 nvl(l_old_rec.CHAR30, '_$') <> nvl(l_new_rec.CHAR30, '_$') OR
883 nvl(l_old_rec.CHAR31, '_$') <> nvl(l_new_rec.CHAR31, '_$') OR
884 nvl(l_old_rec.CHAR32, '_$') <> nvl(l_new_rec.CHAR32, '_$') OR
885 nvl(l_old_rec.CHAR33, '_$') <> nvl(l_new_rec.CHAR33, '_$') OR
886 nvl(l_old_rec.CHAR34, '_$') <> nvl(l_new_rec.CHAR34, '_$') OR
887 nvl(l_old_rec.CHAR35, '_$') <> nvl(l_new_rec.CHAR35, '_$') OR
888 nvl(l_old_rec.CHAR36, '_$') <> nvl(l_new_rec.CHAR36, '_$') OR
889 nvl(l_old_rec.CHAR37, '_$') <> nvl(l_new_rec.CHAR37, '_$')
890 THEN
891 -- Check if it is opportunity is Won, Lost or Closed or
892 -- just updated and fire appropriate event.
893 l_RetVal := 'U';
894 l_old_status := Classify_Status(l_old_rec.CHAR02);
895 l_new_status := Classify_Status(l_new_rec.CHAR02);
896 IF l_old_status <> l_new_status THEN
897 IF (l_new_status = 'C' OR l_new_status = 'W' OR l_new_status = 'L') AND
898 l_old_status <> 'C' AND l_old_status <> 'W' AND l_old_status <> 'L'
899 THEN
900 l_RetVal := l_RetVal || 'C';
901 END IF;
902 IF l_new_status = 'W' OR l_new_status = 'L' THEN
903 l_RetVal := l_RetVal || l_new_status;
904 END IF;
905 END IF;
906 END IF;
907
908 RETURN l_RetVal;
909
910 END DiffOppSnapShots;
911
912
913 -- Compares New and old Opportunity Line SnapShots. If different returns Y
914 -- else returns N. IF p_delete_flag is TRUE then common records are deleted.
915 -- Take care to keep the procedure OppLineDataSnapShot in sync with this.
916 FUNCTION DiffOppLineSnapShots(p_event_key IN VARCHAR2,
917 p_delete_flag IN BOOLEAN) RETURN VARCHAR2
918 IS
919
920 l_RetVal VARCHAR(1) := 'N';
921
922 Cursor c_opp_line_old(p_item_key VARCHAR2, p_indicator VARCHAR2) IS
923 select
924 NUM01, NUM02,
925 NUM03, NUM05,
926 NUM12,
927 NUM13, NUM14,
928 NUM15, NUM19,
929 NUM22, NUM23,
930 NUM24,
931
932 DATE01, DATE02,
933 DATE04, DATE05,
934 DATE06,
935
936 CHAR01, CHAR03,
937 CHAR06, CHAR07,
938 CHAR08, CHAR09,
939 CHAR10, CHAR11,
940 CHAR12, CHAR13,
941 CHAR14, CHAR15,
942 CHAR16, CHAR17,
943 CHAR18, CHAR19,
947 where event_key = p_item_key AND object_state = p_indicator ORDER BY NUM01;
944 CHAR20, CHAR21,
945 CHAR22, rowid
946 from as_event_data
948 -- Order By is to do a sorted list comparison.
949
950 -- Same cursor as c_opp_line_old
951 Cursor c_opp_line_new(p_item_key VARCHAR2, p_indicator VARCHAR2) IS
952 select
953 NUM01, NUM02,
954 NUM03, NUM05,
955 NUM12,
956 NUM13, NUM14,
957 NUM15, NUM19,
958 NUM22, NUM23,
959 NUM24,
960
961 DATE01, DATE02,
962 DATE04, DATE05,
963 DATE06,
964
965 CHAR01, CHAR03,
966 CHAR06, CHAR07,
967 CHAR08, CHAR09,
968 CHAR10, CHAR11,
969 CHAR12, CHAR13,
970 CHAR14, CHAR15,
971 CHAR16, CHAR17,
972 CHAR18, CHAR19,
973 CHAR20, CHAR21,
974 CHAR22, rowid
975 from as_event_data
976 where event_key = p_item_key AND object_state = p_indicator ORDER BY NUM01;
977 -- Order By is to do a sorted list comparison.
978
979 l_old_rec c_opp_line_old%ROWTYPE;
980 l_new_rec c_opp_line_old%ROWTYPE; -- deliberately declared of type old
981 -- to get errors if both are not same.
982 l_old_line_id NUMBER;
983 l_new_line_id NUMBER;
984
985 BEGIN
986
987 OPEN c_opp_line_old(p_event_key, 'OLD');
988 OPEN c_opp_line_new(p_event_key, 'NEW');
989
990 l_old_line_id := 0;
991 l_new_line_id := 0;
992
993 -- Standard sorted list comparison algorithm
994 LOOP
995 IF l_old_line_id <= l_new_line_id THEN
996 FETCH c_opp_line_old INTO l_old_rec;
997 END IF;
998
999 IF l_new_line_id <= l_old_line_id THEN
1000 FETCH c_opp_line_new INTO l_new_rec;
1001 END IF;
1002
1003 l_old_line_id := l_old_rec.NUM01;
1004 l_new_line_id := l_new_rec.NUM01;
1005
1006 IF c_opp_line_old%NOTFOUND OR c_opp_line_new%NOTFOUND THEN
1007 IF c_opp_line_old%FOUND OR c_opp_line_new%FOUND THEN
1008 l_RetVal := 'Y';
1009 END IF;
1010 EXIT;
1011 END IF;
1012
1013 IF
1014 nvl(l_old_line_id, -99) <> nvl(l_new_line_id, -99) OR
1015 nvl(l_old_rec.NUM02, -99) <> nvl(l_new_rec.NUM02, -99) OR
1016 nvl(l_old_rec.NUM03, -99) <> nvl(l_new_rec.NUM03, -99) OR
1017 nvl(l_old_rec.NUM05, -99) <> nvl(l_new_rec.NUM05, -99) OR
1018 nvl(l_old_rec.NUM12, -99) <> nvl(l_new_rec.NUM12, -99) OR
1019 nvl(l_old_rec.NUM13, -99) <> nvl(l_new_rec.NUM13, -99) OR
1020 nvl(l_old_rec.NUM14, -99) <> nvl(l_new_rec.NUM14, -99) OR
1021 nvl(l_old_rec.NUM15, -99) <> nvl(l_new_rec.NUM15, -99) OR
1022 nvl(l_old_rec.NUM19, -99) <> nvl(l_new_rec.NUM19, -99) OR
1023 nvl(l_old_rec.NUM22, -99) <> nvl(l_new_rec.NUM22, -99) OR
1024 nvl(l_old_rec.NUM23, -99) <> nvl(l_new_rec.NUM23, -99) OR
1025 nvl(l_old_rec.NUM24, -99) <> nvl(l_new_rec.NUM24, -99) OR
1026
1027 --nvl(l_old_rec.DATE01, G_DUMMY_DATE) <> nvl(l_new_rec.DATE01, G_DUMMY_DATE) OR
1028 nvl(l_old_rec.DATE02, G_DUMMY_DATE) <> nvl(l_new_rec.DATE02, G_DUMMY_DATE) OR
1029 nvl(l_old_rec.DATE04, G_DUMMY_DATE) <> nvl(l_new_rec.DATE04, G_DUMMY_DATE) OR
1030 nvl(l_old_rec.DATE05, G_DUMMY_DATE) <> nvl(l_new_rec.DATE05, G_DUMMY_DATE) OR
1031 nvl(l_old_rec.DATE06, G_DUMMY_DATE) <> nvl(l_new_rec.DATE06, G_DUMMY_DATE) OR
1032
1033 nvl(l_old_rec.CHAR01, '_$') <> nvl(l_new_rec.CHAR01, '_$') OR
1034 nvl(l_old_rec.CHAR03, '_$') <> nvl(l_new_rec.CHAR03, '_$') OR
1035 nvl(l_old_rec.CHAR06, '_$') <> nvl(l_new_rec.CHAR06, '_$') OR
1036 nvl(l_old_rec.CHAR07, '_$') <> nvl(l_new_rec.CHAR07, '_$') OR
1037 nvl(l_old_rec.CHAR08, '_$') <> nvl(l_new_rec.CHAR08, '_$') OR
1038 nvl(l_old_rec.CHAR09, '_$') <> nvl(l_new_rec.CHAR09, '_$') OR
1039 nvl(l_old_rec.CHAR10, '_$') <> nvl(l_new_rec.CHAR10, '_$') OR
1040 nvl(l_old_rec.CHAR11, '_$') <> nvl(l_new_rec.CHAR11, '_$') OR
1041 nvl(l_old_rec.CHAR12, '_$') <> nvl(l_new_rec.CHAR12, '_$') OR
1042 nvl(l_old_rec.CHAR13, '_$') <> nvl(l_new_rec.CHAR13, '_$') OR
1043 nvl(l_old_rec.CHAR14, '_$') <> nvl(l_new_rec.CHAR14, '_$') OR
1044 nvl(l_old_rec.CHAR15, '_$') <> nvl(l_new_rec.CHAR15, '_$') OR
1045 nvl(l_old_rec.CHAR16, '_$') <> nvl(l_new_rec.CHAR16, '_$') OR
1046 nvl(l_old_rec.CHAR17, '_$') <> nvl(l_new_rec.CHAR17, '_$') OR
1047 nvl(l_old_rec.CHAR18, '_$') <> nvl(l_new_rec.CHAR18, '_$') OR
1048 nvl(l_old_rec.CHAR19, '_$') <> nvl(l_new_rec.CHAR19, '_$') OR
1049 nvl(l_old_rec.CHAR20, '_$') <> nvl(l_new_rec.CHAR20, '_$') OR
1050 nvl(l_old_rec.CHAR21, '_$') <> nvl(l_new_rec.CHAR21, '_$') OR
1051 nvl(l_old_rec.CHAR22, '_$') <> nvl(l_new_rec.CHAR22, '_$')
1052 THEN -- Both records do not match
1053 l_RetVal := 'Y';
1054 IF NOT p_delete_flag THEN
1055 EXIT;
1056 END IF;
1057 ELSE -- Both records match
1058 IF p_delete_flag THEN
1059 delete from AS_EVENT_DATA where rowid = l_old_rec.rowid;
1060 delete from AS_EVENT_DATA where rowid = l_new_rec.rowid;
1061 END IF;
1062 END IF;
1063 END LOOP;
1064
1065 CLOSE c_opp_line_old;
1066 CLOSE c_opp_line_new;
1067
1071
1068 RETURN l_RetVal;
1069
1070 END DiffOppLineSnapShots;
1072
1073 -- Compares New and old Sales Team SnapShots for both Opportunity and Customer.
1074 -- If different returns Y else returns N. IF p_delete_flag is TRUE then common
1075 -- records are deleted.
1076 -- Take care to keep the procedure OppSTeamDataSnapShot and
1077 -- CustSTeamDataSnapShot in sync with this.
1078 -- Because TAP deletes entires SalesTeam and recreates it, access_id and
1079 -- create and update dates are not used in comparison since it would change
1080 -- even without any other change.
1081 FUNCTION DiffSTeamSnapShots(p_event_key IN VARCHAR2,
1082 p_delete_flag IN BOOLEAN) RETURN VARCHAR2
1083 IS
1084
1085 l_RetVal VARCHAR(1) := 'N';
1086
1087 Cursor c_sales_team_old(p_item_key VARCHAR2, p_indicator VARCHAR2) IS
1088 select
1089 NUM01, NUM02, NUM03, NUM04, NUM05, rowid
1090 from as_event_data
1091 where event_key = p_item_key AND object_state = p_indicator
1092 ORDER BY NUM01, NUM02, NUM05;
1093 -- Order By is to do a sorted list comparison. Order by Salesforce id and
1094 -- Sales Group Id since access_id is not used.
1095
1096 -- Same cursor as c_sales_team_old
1097 Cursor c_sales_team_new(p_item_key VARCHAR2, p_indicator VARCHAR2) IS
1098 select
1099 NUM01, NUM02, NUM03, NUM04, NUM05, rowid
1100 from as_event_data
1101 where event_key = p_item_key AND object_state = p_indicator
1102 ORDER BY NUM01, NUM02, NUM05;
1103 -- Order By is to do a sorted list comparison.
1104
1105 l_old_rec c_sales_team_old%ROWTYPE;
1106 l_new_rec c_sales_team_old%ROWTYPE; -- deliberately declared of type old
1107 -- to get errors if both are not same.
1108 l_delCount NUMBER :=0 ;
1109 l_old_salesforce_id NUMBER;
1110 l_new_salesforce_id NUMBER;
1111 l_old_sales_group_id NUMBER;
1112 l_new_sales_group_id NUMBER;
1113 l_old_address_id NUMBER;
1114 l_new_address_id NUMBER;
1115
1116 BEGIN
1117
1118 OPEN c_sales_team_old(p_event_key, 'OLD');
1119 OPEN c_sales_team_new(p_event_key, 'NEW');
1120
1121 l_old_salesforce_id := 0;
1122 l_old_sales_group_id := 0;
1123 l_new_salesforce_id := 0;
1124 l_new_sales_group_id := 0;
1125
1126 -- Standard Sorted list comparison. The key is combo of Salesforce_id and
1127 -- sales_group_id
1128 LOOP
1129 IF l_old_salesforce_id < l_new_salesforce_id OR
1130 (l_old_salesforce_id = l_new_salesforce_id
1131 AND l_old_sales_group_id <= l_new_sales_group_id)THEN
1132 FETCH c_sales_team_old INTO l_old_rec;
1133 END IF;
1134
1135 IF l_new_salesforce_id < l_old_salesforce_id OR
1136 (l_new_salesforce_id = l_old_salesforce_id
1137 AND l_new_sales_group_id <= l_old_sales_group_id)THEN
1138 FETCH c_sales_team_new INTO l_new_rec;
1139 END IF;
1140
1141 l_old_salesforce_id := nvl(l_old_rec.NUM01, -99);
1142 l_old_sales_group_id := nvl(l_old_rec.NUM02, -99);
1143 l_old_address_id := nvl(l_old_rec.NUM05, -99);
1144 l_new_salesforce_id := nvl(l_new_rec.NUM01, -99);
1145 l_new_sales_group_id := nvl(l_new_rec.NUM02, -99);
1146 l_new_address_id := nvl(l_new_rec.NUM05, -99);
1147
1148 IF c_sales_team_old%NOTFOUND OR c_sales_team_new%NOTFOUND THEN
1149 IF c_sales_team_old%FOUND OR c_sales_team_new%FOUND THEN
1150 l_RetVal := 'Y';
1151 END IF;
1152 EXIT;
1153 END IF;
1154
1155 IF
1156 l_old_salesforce_id <> l_new_salesforce_id OR
1157 l_old_sales_group_id <> l_new_sales_group_id OR
1158 l_old_address_id <> l_new_address_id
1159 THEN -- Both records do not match
1160 l_RetVal := 'Y';
1161 IF NOT p_delete_flag THEN
1162 EXIT;
1163 END IF;
1164 ELSE -- Both records match
1165 IF p_delete_flag THEN
1166 delete from AS_EVENT_DATA where rowid = l_old_rec.rowid;
1167 delete from AS_EVENT_DATA where rowid = l_new_rec.rowid;
1168 END IF;
1169 END IF;
1170 END LOOP;
1171
1172 CLOSE c_sales_team_old;
1173 CLOSE c_sales_team_new;
1174
1175 RETURN l_RetVal;
1176
1177 END DiffSTeamSnapShots;
1178
1179
1180 -- Normally this is an subscriber of INT_OPPTY_UPDATE_EVENT. But it can be
1181 -- called directly if the last parameter is AS_BUSINESS_EVENT_PVT.DIRECT_CALL
1182 -- and is set to Y
1183 FUNCTION Raise_update_oppty_event (
1184 p_subscription_guid IN RAW,
1185 p_event IN OUT NOCOPY WF_EVENT_T
1186 )
1187 RETURN VARCHAR2
1188 IS
1189
1190 l_api_name CONSTANT VARCHAR2(30) := 'Raise_update_oppty_event';
1191 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1192 l_list WF_PARAMETER_LIST_T := p_event.GetParameterList();
1193 l_param WF_PARAMETER_T;
1194 l_event_list AS_EVENT_TABLE_T;
1195 l_event_rec AS_EVENT_REC_T;
1196 l_sub_exists VARCHAR2(1);
1197 l_event_name VARCHAR2(240);
1198 l_diff_result VARCHAR2(4);
1199 l_event_code VARCHAR2(1);
1200 l_i NUMBER;
1204 l_event_key VARCHAR2(240) := p_event.getEventKey();
1201 l_num_events NUMBER;
1202 l_raise_count NUMBER;
1203 l_upd_event_raised BOOLEAN;
1205 l_new_event_key VARCHAR2(240);
1206 l_direct_call VARCHAR2(1) := 'N';
1207 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Raise_update_oppty_event';
1208
1209 BEGIN
1210 SAVEPOINT Raise_update_oppty_event;
1211
1212 -- Debug Message
1213 IF l_debug THEN
1214 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1215 'Private API: ' || l_api_name || ' start');
1216 END IF;
1217
1218 l_num_events := 0;
1219 l_upd_event_raised := FALSE;
1220
1221 -- If DIRECT_CALL parameter is the last parameter remove it after noting
1222 -- its value
1223 l_i := l_list.last;
1224 IF l_i >= 1 THEN
1225 l_param := l_list(l_i);
1226 IF l_param.GetName() = DIRECT_CALL THEN
1227 l_direct_call := nvl(l_param.GetValue(), 'N');
1228 l_list.trim();
1229 END IF;
1230 END IF;
1231
1232 -- Debug Message
1233 IF l_debug THEN
1234 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1235 'Direct Call: ' || l_direct_call);
1236 END IF;
1237
1238 l_diff_result := DiffOppSnapShots(l_event_key) ;
1239
1240 -- Debug Message
1241 IF l_debug THEN
1242 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1243 'Return Value from DiffOppSnapShots: ' || l_diff_result);
1244 END IF;
1245
1246 IF l_diff_result <> 'N' THEN
1247 l_num_events := length(l_diff_result);
1248 END IF;
1249
1250 l_raise_count := 0;
1251
1252 FOR l_i IN 1..l_num_events LOOP
1253 l_event_code := substr(l_diff_result, l_i, 1);
1254 IF l_event_code = 'W' THEN
1255 l_event_name := OPPTY_WON_EVENT;
1256 ELSIF l_event_code = 'L' THEN
1257 l_event_name := OPPTY_LOST_EVENT;
1258 ELSIF l_event_code = 'C' THEN
1259 l_event_name := OPPTY_CLOSED_EVENT;
1260 ELSE
1261 l_event_name := OPPTY_UPDATE_EVENT;
1262 END IF;
1263
1264 -- Raise Event ONLY if a subscription to
1265 -- event exists.
1266 l_sub_exists := exist_subscription( l_event_name );
1267
1268 -- Debug Message
1269 IF l_debug THEN
1270 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1271 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription(' || l_event_name || '): ' || l_sub_exists);
1272 END IF;
1273
1274 IF l_sub_exists = 'Y' THEN
1275 IF l_event_code = 'U' THEN
1276 l_new_event_key := l_event_key;
1277 ELSE
1278 l_new_event_key := item_key(l_event_name);
1279 Copy_Event_Data(l_event_key, l_new_event_key);
1280 END IF;
1281
1282 -- Store Event to be raised. Event not raised here since that would
1283 -- delete the original event record if update event is raised.
1284 l_event_rec.event_name := l_event_name;
1285 l_event_rec.event_key := l_new_event_key;
1286 l_event_rec.event_code := l_event_code;
1287 l_raise_count := l_raise_count + 1;
1288 l_event_list(l_raise_count) := l_event_rec;
1289 END IF;
1290 END LOOP;
1291
1292 FOR l_i IN 1..l_raise_count LOOP
1293 l_event_rec := l_event_list(l_i);
1294 -- Debug Message
1295 IF l_debug THEN
1296 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1297 'Calling AS_BUSINESS_EVENT_PVT.raise_event');
1298 END IF;
1299
1300 raise_event(
1301 p_event_name => l_event_rec.event_name,
1302 p_event_key => l_event_rec.event_key,
1303 p_parameters => l_list );
1304
1305 IF l_event_rec.event_code = 'U' THEN
1306 l_upd_event_raised := TRUE;
1307 END IF;
1308 END LOOP;
1309
1310 -- If update_event is to be raised then raise it else delete the event_data
1311 IF NOT l_upd_event_raised THEN
1312 delete from AS_EVENT_DATA where event_key = l_event_key;
1313 END IF;
1314
1315 -- Debug Message
1316 IF l_debug THEN
1317 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1318 'Private API: ' || l_api_name || ' end');
1319 END IF;
1320
1321 RETURN 'SUCCESS';
1322
1323 EXCEPTION
1324
1325 WHEN OTHERS THEN
1326 ROLLBACK TO Raise_update_oppty_event;
1327
1328 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(SQLCODE));
1329 FND_MSG_PUB.ADD;
1330
1331 IF l_direct_call <> 'Y' THEN
1332 WF_CORE.CONTEXT('AS_BUSINESS_EVENT_PVT', 'RAISE_UPDATE_OPPTY_EVENT', p_event.getEventName(), p_subscription_guid);
1333 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1334 END IF;
1335
1336 RETURN 'ERROR';
1337 END Raise_update_oppty_event;
1338
1339
1340 PROCEDURE Before_Oppty_Update(
1344 l_api_name CONSTANT VARCHAR2(30) := 'Before_Oppty_Update';
1341 p_lead_id IN NUMBER,
1342 x_event_key OUT NOCOPY VARCHAR2
1343 ) IS
1345 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1346 l_raise_event VARCHAR2(1);
1347 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Before_Oppty_Update';
1348
1349 BEGIN
1350 SAVEPOINT Before_Oppty_Update;
1351
1352 -- Debug Message
1353 IF l_debug THEN
1354 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1355 'Private API: ' || l_api_name || ' start');
1356 END IF;
1357
1358 -- Begin Set l_raise_event = 'Y' if subscription exists to one of
1359 -- OPPTY UPDATE/CLOSED/WON/LOST Events.
1360 -- Raise Event ONLY if a subscription to
1361 -- event exists.
1362 l_raise_event := exist_subscription( OPPTY_UPDATE_EVENT );
1363
1364 -- Debug Message
1365 IF l_debug THEN
1366 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1367 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription: ' || l_raise_event);
1368 END IF;
1369
1370 IF l_raise_event <> 'Y' THEN
1371 -- Raise Event ONLY if a subscription to
1372 -- event exists.
1373 l_raise_event := exist_subscription( OPPTY_CLOSED_EVENT );
1374
1375 -- Debug Message
1376 IF l_debug THEN
1377 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1378 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription(CLOSED): ' || l_raise_event);
1379 END IF;
1380 END IF;
1381
1382 IF l_raise_event <> 'Y' THEN
1383 -- Raise Event ONLY if a subscription to
1384 -- event exists.
1385 l_raise_event := exist_subscription( OPPTY_WON_EVENT );
1386
1387 -- Debug Message
1388 IF l_debug THEN
1389 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1390 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription(WON): ' || l_raise_event);
1391 END IF;
1392 END IF;
1393
1394 IF l_raise_event <> 'Y' THEN
1395 -- Raise Event ONLY if a subscription to
1396 -- event exists.
1397 l_raise_event := exist_subscription( OPPTY_LOST_EVENT );
1398
1399 -- Debug Message
1400 IF l_debug THEN
1401 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1402 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription(LOST): ' || l_raise_event);
1403 END IF;
1404 END IF;
1405 -- End Set l_raise_event = 'Y' if subscription exists to one of
1406 -- OPPTY UPDATE/CLOSED/WON/LOST Events.
1407
1408 IF l_raise_event = 'Y' THEN
1409 x_event_key := item_key( OPPTY_UPDATE_EVENT );
1410
1411 IF p_lead_id IS NOT NULL THEN
1412 OppDataSnapShot(x_event_key, p_lead_id, 'OLD');
1413 END IF;
1414 ELSE
1415 x_event_key := NULL;
1416 END IF;
1417
1418 -- Debug Message
1419 IF l_debug THEN
1420 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1421 'Private API: ' || l_api_name || ' end');
1422 END IF;
1423 END Before_Oppty_Update;
1424
1425
1426 PROCEDURE Update_oppty_post_event(
1427 p_lead_id IN NUMBER,
1428 p_event_key IN VARCHAR2
1429 ) IS
1430
1431 l_api_name CONSTANT VARCHAR2(30) := 'Update_oppty_post_event';
1432 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1433 l_list WF_PARAMETER_LIST_T;
1434 l_param WF_PARAMETER_T;
1435 l_event WF_EVENT_T;
1436 l_status VARCHAR2(32);
1437 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Update_oppty_post_event';
1438
1439 BEGIN
1440 SAVEPOINT Update_oppty_post_event;
1441
1442 -- Debug Message
1443 IF l_debug THEN
1444 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1445 'Private API: ' || l_api_name || ' start');
1446 END IF;
1447
1448 -- Simple Check if it is an Opportunity Update Event
1449 IF INSTR(p_event_key, OPPTY_UPDATE_EVENT) <> 1 THEN
1450 FND_MESSAGE.SET_NAME('AS', 'AS_INVALID_EVENT_KEY');
1451 FND_MESSAGE.SET_TOKEN('KEY' , p_event_key);
1452 FND_MSG_PUB.ADD;
1453
1454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1455 END IF;
1456
1457 OppDataSnapShot(p_event_key, p_lead_id, 'NEW');
1458
1459 -- Debug Message
1460 IF l_debug THEN
1461 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1462 'Done Calling OppDataSnapShot');
1463 END IF;
1464
1465 -- initialization of object variables
1466 l_list := WF_PARAMETER_LIST_T();
1467
1468 -- Add Context values to the list
1469 AddParamEnvToList(l_list);
1470 l_param := WF_PARAMETER_T( NULL, NULL );
1471
1472 -- fill the parameters list
1476 l_list(l_list.last) := l_param;
1473 l_list.extend;
1474 l_param.SetName( 'LEAD_ID' );
1475 l_param.SetValue( p_lead_id );
1477
1478 -- Debug Message
1479 IF l_debug THEN
1480 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1481 'Calling AS_BUSINESS_EVENT_PVT.raise_event');
1482 END IF;
1483
1484 -- Raise Event to do diff and raise actual event(s)
1485 raise_event(
1486 p_event_name => INT_OPPTY_UPDATE_EVENT,
1487 p_event_key => p_event_key,
1488 p_parameters => l_list );
1489
1490 /* Comment the above call and uncomment the below to direclty call the
1491 method to raise events instead of raising an asynchronous event to do so
1492 l_list.extend;
1493 l_param.SetName( DIRECT_CALL );
1494 l_param.SetValue( 'Y' );
1495 l_list(l_list.last) := l_param;
1496 l_event := WF_EVENT_T(NULL, NULL, NULL, NULL, l_list, INT_OPPTY_UPDATE_EVENT,
1497 p_event_key, NULL, NULL, NULL, NULL, NULL, NULL);
1498 l_event.setParameterList(l_list);
1499
1500 l_status := Raise_update_oppty_event(NULL, l_event);
1501 */
1502
1503 l_list.DELETE;
1504
1505 -- Debug Message
1506 IF l_debug THEN
1507 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1508 'Private API: ' || l_api_name || ' end');
1509 END IF;
1510 END Update_oppty_post_event;
1511
1512
1513 -- Normally this is an subscriber of INT_OPP_LINES_UPDATE_EVENT. But it can be
1514 -- called directly if the last parameter is AS_BUSINESS_EVENT_PVT.DIRECT_CALL
1515 -- and is set to Y
1516 FUNCTION Raise_upd_opp_lines_evnt (
1517 p_subscription_guid IN RAW,
1518 p_event IN OUT NOCOPY WF_EVENT_T
1519 )
1520 RETURN VARCHAR2
1521 IS
1522
1523 l_api_name CONSTANT VARCHAR2(30) := 'Raise_upd_opp_lines_evnt';
1524 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1525 l_list WF_PARAMETER_LIST_T := p_event.GetParameterList();
1526 l_param WF_PARAMETER_T;
1527 l_raise_event VARCHAR2(1);
1528 l_oppline_changed VARCHAR2(1);
1529 l_i NUMBER;
1530 l_event_key VARCHAR2(240) := p_event.getEventKey();
1531 l_direct_call VARCHAR2(1) := 'N';
1532 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Raise_upd_opp_lines_evnt';
1533
1534 BEGIN
1535 SAVEPOINT Raise_upd_opp_lines_evnt;
1536
1537 -- Debug Message
1538 IF l_debug THEN
1539 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1540 'Private API: ' || l_api_name || ' start');
1541 END IF;
1542
1543 -- If DIRECT_CALL parameter is the last parameter remove it after noting
1544 -- its value
1545 l_i := l_list.last;
1546 IF l_i >= 1 THEN
1547 l_param := l_list(l_i);
1548 IF l_param.GetName() = DIRECT_CALL THEN
1549 l_direct_call := nvl(l_param.GetValue(), 'N');
1550 l_list.trim();
1551 END IF;
1552 END IF;
1553
1554 -- Debug Message
1555 IF l_debug THEN
1556 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1557 'Direct Call: ' || l_direct_call);
1558 END IF;
1559
1560 -- Raise Event ONLY if a subscription to
1561 -- event exists.
1562 l_raise_event := exist_subscription( OPP_LINES_UPDATE_EVENT );
1563
1564 -- Debug Message
1565 IF l_debug THEN
1566 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1567 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription: ' || l_raise_event);
1568 END IF;
1569
1570 IF l_raise_event = 'Y' THEN
1571 l_oppline_changed := DiffOppLineSnapShots(l_event_key, FALSE) ;
1572 l_raise_event := l_oppline_changed;
1573
1574 -- Debug Message
1575 IF l_debug THEN
1576 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1577 'Return Value from DiffOppLineSnapShots: ' || l_oppline_changed);
1578 END IF;
1579 END IF;
1580
1581 IF l_raise_event = 'Y' THEN
1582 -- Debug Message
1583 IF l_debug THEN
1584 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1585 'Calling AS_BUSINESS_EVENT_PVT.raise_event');
1586 END IF;
1587
1588 -- Raise Event
1589 raise_event(
1590 p_event_name => OPP_LINES_UPDATE_EVENT,
1591 p_event_key => l_event_key,
1592 p_parameters => l_list );
1593 ELSE
1594 delete from AS_EVENT_DATA where event_key = l_event_key;
1595 END IF;
1596
1597 -- Debug Message
1598 IF l_debug THEN
1599 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1600 'Private API: ' || l_api_name || ' end');
1601 END IF;
1602
1603 RETURN 'SUCCESS';
1604
1605 EXCEPTION
1606
1610 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(SQLCODE));
1607 WHEN OTHERS THEN
1608 ROLLBACK TO Raise_upd_opp_lines_evnt;
1609
1611 FND_MSG_PUB.ADD;
1612
1613 IF l_direct_call <> 'Y' THEN
1614 WF_CORE.CONTEXT('AS_BUSINESS_EVENT_PVT', 'RAISE_UPD_OPP_LINES_EVNT', p_event.getEventName(), p_subscription_guid);
1615 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1616 END IF;
1617
1618 RETURN 'ERROR';
1619 END Raise_upd_opp_lines_evnt;
1620
1621
1622 PROCEDURE Before_Opp_Lines_Update(
1623 p_lead_id IN NUMBER,
1624 x_event_key OUT NOCOPY VARCHAR2
1625 ) IS
1626 l_api_name CONSTANT VARCHAR2(30) := 'Before_Opp_Lines_Update';
1627 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1628 l_raise_event VARCHAR2(1);
1629 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Before_Opp_Lines_Update';
1630
1631 BEGIN
1632 SAVEPOINT Before_Opp_Lines_Update;
1633
1634 -- Debug Message
1635 IF l_debug THEN
1636 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1637 'Private API: ' || l_api_name || ' start');
1638 END IF;
1639
1640 -- Raise Event ONLY if a subscription to
1641 -- event exists.
1642 l_raise_event := exist_subscription( OPP_LINES_UPDATE_EVENT );
1643
1644 -- Debug Message
1645 IF l_debug THEN
1646 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1647 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription: ' || l_raise_event);
1648 END IF;
1649
1650 IF l_raise_event = 'Y' THEN
1651 x_event_key := item_key( OPP_LINES_UPDATE_EVENT );
1652
1653 IF p_lead_id IS NOT NULL THEN
1654 OppLineDataSnapShot(x_event_key, p_lead_id, 'OLD');
1655 END IF;
1656 ELSE
1657 x_event_key := NULL;
1658 END IF;
1659
1660 -- Debug Message
1661 IF l_debug THEN
1662 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1663 'Private API: ' || l_api_name || ' end');
1664 END IF;
1665 END Before_Opp_Lines_Update;
1666
1667
1668 PROCEDURE Upd_Opp_Lines_post_event(
1669 p_lead_id IN NUMBER,
1670 p_event_key IN VARCHAR2
1671 ) IS
1672
1673 l_api_name CONSTANT VARCHAR2(30) := 'Upd_Opp_Lines_post_event';
1674 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1675 l_list WF_PARAMETER_LIST_T;
1676 l_param WF_PARAMETER_T;
1677 l_event WF_EVENT_T;
1678 l_status VARCHAR2(32);
1679 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Upd_Opp_Lines_post_event';
1680
1681 BEGIN
1682 SAVEPOINT Upd_Opp_Lines_post_event;
1683
1684 -- Debug Message
1685 IF l_debug THEN
1686 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1687 'Private API: ' || l_api_name || ' start');
1688 END IF;
1689
1690 -- Simple Check if it is an Opportunity Lines Update Event
1691 IF INSTR(p_event_key, OPP_LINES_UPDATE_EVENT) <> 1 THEN
1692 FND_MESSAGE.SET_NAME('AS', 'AS_INVALID_EVENT_KEY');
1693 FND_MESSAGE.SET_TOKEN('KEY' , p_event_key);
1694 FND_MSG_PUB.ADD;
1695
1696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697 END IF;
1698
1699 OppLineDataSnapShot(p_event_key, p_lead_id, 'NEW');
1700
1701 -- Debug Message
1702 IF l_debug THEN
1703 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1704 'Done Calling OppLineDataSnapShot');
1705 END IF;
1706
1707 -- initialization of object variables
1708 l_list := WF_PARAMETER_LIST_T();
1709
1710 -- Add Context values to the list
1711 AddParamEnvToList(l_list);
1712 l_param := WF_PARAMETER_T( NULL, NULL );
1713
1714 -- fill the parameters list
1715 l_list.extend;
1716 l_param.SetName( 'LEAD_ID' );
1717 l_param.SetValue( p_lead_id );
1718 l_list(l_list.last) := l_param;
1719
1720 -- Debug Message
1721 IF l_debug THEN
1722 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1723 'Calling AS_BUSINESS_EVENT_PVT.raise_event');
1724 END IF;
1725
1726 -- Raise Event to do diff and raise actual event(s)
1727 raise_event(
1728 p_event_name => INT_OPP_LINES_UPDATE_EVENT,
1729 p_event_key => p_event_key,
1730 p_parameters => l_list );
1731
1732 /* Comment the above call and uncomment the below to direclty call the
1733 method to raise events instead of raising an asynchronous event to do so
1734 l_list.extend;
1735 l_param.SetName( DIRECT_CALL );
1736 l_param.SetValue( 'Y' );
1737 l_list(l_list.last) := l_param;
1738 l_event := WF_EVENT_T(NULL, NULL, NULL, NULL, l_list, INT_OPP_LINES_UPDATE_EVENT,
1739 p_event_key, NULL, NULL, NULL, NULL, NULL, NULL);
1740 l_event.setParameterList(l_list);
1741
1742 l_status := Raise_upd_opp_lines_evnt(NULL, l_event);
1743 */
1744
1745 -- Debug Message
1746 IF l_debug THEN
1750 END Upd_Opp_Lines_post_event;
1747 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1748 'Private API: ' || l_api_name || ' end');
1749 END IF;
1751
1752
1753 -- The below method is used to raise events for SalesTeam update of either
1754 -- opportunity or Customer. Normally this is an subscriber of
1755 -- INT_STEAM_UPDATE_EVENT. But it can be
1756 -- called directly if the last parameter is AS_BUSINESS_EVENT_PVT.DIRECT_CALL
1757 -- and is set to Y
1758 FUNCTION Raise_upd_STeam_evnt (
1759 p_subscription_guid IN RAW,
1760 p_event IN OUT NOCOPY WF_EVENT_T
1761 )
1762 RETURN VARCHAR2
1763 IS
1764
1765 l_api_name CONSTANT VARCHAR2(30) := 'Raise_upd_STeam_evnt';
1766 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1767 l_list WF_PARAMETER_LIST_T := p_event.GetParameterList();
1768 l_param WF_PARAMETER_T;
1769 l_raise_event VARCHAR2(1);
1770 l_steam_changed VARCHAR2(1);
1771 l_i NUMBER;
1772 l_event_name VARCHAR2(240);
1773 l_event_key VARCHAR2(240) := p_event.getEventKey();
1774 l_direct_call VARCHAR2(1) := 'N';
1775 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Raise_upd_STeam_evnt';
1776
1777 BEGIN
1778 SAVEPOINT Raise_upd_STeam_evnt;
1779
1780 -- Debug Message
1781 IF l_debug THEN
1782 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1783 'Private API: ' || l_api_name || ' start');
1784 END IF;
1785
1786 -- If DIRECT_CALL parameter is the last parameter remove it after noting
1787 -- its value
1788 l_i := l_list.last;
1789 IF l_i >= 1 THEN
1790 l_param := l_list(l_i);
1791 IF l_param.GetName() = DIRECT_CALL THEN
1792 l_direct_call := nvl(l_param.GetValue(), 'N');
1793 l_list.trim();
1794 END IF;
1795 END IF;
1796
1797 -- Derive Event Name from Event Key
1798 IF INSTR(l_event_key, OPP_STEAM_UPDATE_EVENT) = 1 THEN
1799 l_event_name := OPP_STEAM_UPDATE_EVENT;
1800 ELSE
1801 l_event_name := CUST_STEAM_UPDATE_EVENT;
1802 END IF;
1803
1804 -- Debug Message
1805 IF l_debug THEN
1806 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1807 'Direct Call: ' || l_direct_call);
1808 END IF;
1809
1810 -- Raise Event ONLY if a subscription to
1811 -- event exists.
1812 l_raise_event := exist_subscription( l_event_name );
1813
1814 -- Debug Message
1815 IF l_debug THEN
1816 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1817 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription: ' || l_raise_event);
1818 END IF;
1819
1820 IF l_raise_event = 'Y' THEN
1821 l_steam_changed := DiffSTeamSnapShots(l_event_key, FALSE) ;
1822 l_raise_event := l_steam_changed;
1823
1824 -- Debug Message
1825 IF l_debug THEN
1826 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1827 'Return Value from DiffSTeamSnapShots: ' || l_steam_changed);
1828 END IF;
1829 END IF;
1830
1831 IF l_raise_event = 'Y' THEN
1832 -- Debug Message
1833 IF l_debug THEN
1834 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1835 'Calling AS_BUSINESS_EVENT_PVT.raise_event');
1836 END IF;
1837
1838 -- Raise Event
1839 raise_event(
1840 p_event_name => l_event_name,
1841 p_event_key => l_event_key,
1842 p_parameters => l_list );
1843 ELSE
1844 delete from AS_EVENT_DATA where event_key = l_event_key;
1845 END IF;
1846
1847 -- Debug Message
1848 IF l_debug THEN
1849 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1850 'Private API: ' || l_api_name || ' end');
1851 END IF;
1852
1853 RETURN 'SUCCESS';
1854
1855 EXCEPTION
1856
1857 WHEN OTHERS THEN
1858 ROLLBACK TO Raise_upd_STeam_evnt;
1859
1860 FND_MESSAGE.Set_Name('AS', 'Error number ' || to_char(SQLCODE));
1861 FND_MSG_PUB.ADD;
1862
1863 IF l_direct_call <> 'Y' THEN
1864 WF_CORE.CONTEXT('AS_BUSINESS_EVENT_PVT', 'RAISE_UPD_STEAM_EVNT', p_event.getEventName(), p_subscription_guid);
1865 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1866 END IF;
1867
1868 RETURN 'ERROR';
1869 END Raise_upd_STeam_evnt;
1870
1871
1872 PROCEDURE Before_Opp_STeam_Update(
1873 p_lead_id IN NUMBER,
1874 x_event_key OUT NOCOPY VARCHAR2
1875 ) IS
1876 l_api_name CONSTANT VARCHAR2(30) := 'Before_Opp_STeam_Update';
1877 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1878 l_raise_event VARCHAR2(1);
1879 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Before_Opp_STeam_Update';
1880
1881 BEGIN
1882 SAVEPOINT Before_Opp_STeam_Update;
1883
1884 -- Debug Message
1885 IF l_debug THEN
1886 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1890 -- Raise Event ONLY if a subscription to
1887 'Private API: ' || l_api_name || ' start');
1888 END IF;
1889
1891 -- event exists.
1892 l_raise_event := exist_subscription( OPP_STEAM_UPDATE_EVENT );
1893
1894 -- Debug Message
1895 IF l_debug THEN
1896 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1897 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription: ' || l_raise_event);
1898 END IF;
1899
1900 IF l_raise_event = 'Y' THEN
1901 x_event_key := item_key( OPP_STEAM_UPDATE_EVENT );
1902
1903 IF p_lead_id IS NOT NULL THEN
1904 OppSTeamDataSnapShot(x_event_key, p_lead_id, 'OLD');
1905 END IF;
1906 ELSE
1907 x_event_key := NULL;
1908 END IF;
1909
1910 -- Debug Message
1911 IF l_debug THEN
1912 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1913 'Private API: ' || l_api_name || ' end');
1914 END IF;
1915 END Before_Opp_STeam_Update;
1916
1917
1918 PROCEDURE Upd_Opp_STeam_post_event(
1919 p_lead_id IN NUMBER,
1920 p_event_key IN VARCHAR2
1921 ) IS
1922
1923 l_api_name CONSTANT VARCHAR2(30) := 'Upd_Opp_STeam_post_event';
1924 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1925 l_list WF_PARAMETER_LIST_T;
1926 l_param WF_PARAMETER_T;
1927 l_event WF_EVENT_T;
1928 l_status VARCHAR2(32);
1929 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Upd_Opp_STeam_post_event';
1930
1931 BEGIN
1932 SAVEPOINT Upd_Opp_STeam_post_event;
1933
1934 -- Debug Message
1935 IF l_debug THEN
1936 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1937 'Private API: ' || l_api_name || ' start');
1938 END IF;
1939
1940 -- Simple Check if it is an Opportunity Sales Team Update Event
1941 IF INSTR(p_event_key, OPP_STEAM_UPDATE_EVENT) <> 1 THEN
1942 FND_MESSAGE.SET_NAME('AS', 'AS_INVALID_EVENT_KEY');
1943 FND_MESSAGE.SET_TOKEN('KEY' , p_event_key);
1944 FND_MSG_PUB.ADD;
1945
1946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1947 END IF;
1948
1949 OppSTeamDataSnapShot(p_event_key, p_lead_id, 'NEW');
1950
1951 -- Debug Message
1952 IF l_debug THEN
1953 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1954 'Done Calling OppSTeamDataSnapShot');
1955 END IF;
1956
1957 -- initialization of object variables
1958 l_list := WF_PARAMETER_LIST_T();
1959
1960 -- Add Context values to the list
1961 AddParamEnvToList(l_list);
1962 l_param := WF_PARAMETER_T( NULL, NULL );
1963
1964 -- fill the parameters list
1965 l_list.extend;
1966 l_param.SetName( 'LEAD_ID' );
1967 l_param.SetValue( p_lead_id );
1968 l_list(l_list.last) := l_param;
1969
1970 -- Debug Message
1971 IF l_debug THEN
1972 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1973 'Calling AS_BUSINESS_EVENT_PVT.raise_event');
1974 END IF;
1975
1976 -- Raise Event to do diff and raise actual event(s)
1977 raise_event(
1978 p_event_name => INT_STEAM_UPDATE_EVENT,
1979 p_event_key => p_event_key,
1980 p_parameters => l_list );
1981
1982 /* Comment the above call and uncomment the below to direclty call the
1983 method to raise events instead of raising an asynchronous event to do so
1984 l_list.extend;
1985 l_param.SetName( DIRECT_CALL );
1986 l_param.SetValue( 'Y' );
1987 l_list(l_list.last) := l_param;
1988 l_event := WF_EVENT_T(NULL, NULL, NULL, NULL, l_list, INT_STEAM_UPDATE_EVENT,
1989 p_event_key, NULL, NULL, NULL, NULL, NULL, NULL);
1990 l_event.setParameterList(l_list);
1991
1992 l_status := Raise_upd_STeam_evnt(NULL, l_event);
1993 */
1994
1995 -- Debug Message
1996 IF l_debug THEN
1997 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1998 'Private API: ' || l_api_name || ' end');
1999 END IF;
2000 END Upd_Opp_STeam_post_event;
2001
2002
2003 PROCEDURE Before_Cust_STeam_Update(
2004 p_cust_id IN NUMBER,
2005 x_event_key OUT NOCOPY VARCHAR2
2006 ) IS
2007 l_api_name CONSTANT VARCHAR2(30) := 'Before_Cust_STeam_Update';
2008 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2009 l_raise_event VARCHAR2(1);
2010 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Before_Cust_STeam_Update';
2011
2012 BEGIN
2013 SAVEPOINT Before_Cust_STeam_Update;
2014
2015 -- Debug Message
2016 IF l_debug THEN
2017 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2018 'Private API: ' || l_api_name || ' start');
2019 END IF;
2020
2021 -- Raise Event ONLY if a subscription to
2022 -- event exists.
2023 l_raise_event := exist_subscription( CUST_STEAM_UPDATE_EVENT );
2024
2025 -- Debug Message
2026 IF l_debug THEN
2027 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2028 'Return Value from AS_BUSINESS_EVENT_PVT.exist_subscription: ' || l_raise_event);
2029 END IF;
2030
2031 IF l_raise_event = 'Y' THEN
2032 x_event_key := item_key( CUST_STEAM_UPDATE_EVENT );
2033
2034 IF p_cust_id IS NOT NULL THEN
2035 CustSTeamDataSnapShot(x_event_key, p_cust_id, 'OLD');
2036 END IF;
2037 ELSE
2038 x_event_key := NULL;
2039 END IF;
2040
2041 -- Debug Message
2042 IF l_debug THEN
2043 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2044 'Private API: ' || l_api_name || ' end');
2045 END IF;
2046 END Before_Cust_STeam_Update;
2047
2048
2049 PROCEDURE Upd_Cust_STeam_post_event(
2050 p_cust_id IN NUMBER,
2051 p_event_key IN VARCHAR2
2052 ) IS
2053
2054 l_api_name CONSTANT VARCHAR2(30) := 'Upd_Cust_STeam_post_event';
2055 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2056 l_list WF_PARAMETER_LIST_T;
2057 l_param WF_PARAMETER_T;
2058 l_event WF_EVENT_T;
2059 l_status VARCHAR2(32);
2060 l_module CONSTANT VARCHAR2(255) := 'as.plsql.bevpv.Upd_Cust_STeam_post_event';
2061
2062 BEGIN
2063 SAVEPOINT Upd_Cust_STeam_post_event;
2064
2065 -- Debug Message
2066 IF l_debug THEN
2067 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2068 'Private API: ' || l_api_name || ' start');
2069 END IF;
2070
2071 -- Simple Check if it is an Customer Sales Team Update Event
2072 IF INSTR(p_event_key, CUST_STEAM_UPDATE_EVENT) <> 1 THEN
2073 FND_MESSAGE.SET_NAME('AS', 'AS_INVALID_EVENT_KEY');
2074 FND_MESSAGE.SET_TOKEN('KEY' , p_event_key);
2075 FND_MSG_PUB.ADD;
2076
2077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2078 END IF;
2079
2080 CustSTeamDataSnapShot(p_event_key, p_cust_id, 'NEW');
2081
2082 -- Debug Message
2083 IF l_debug THEN
2084 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2085 'Done Calling CustSTeamDataSnapShot');
2086 END IF;
2087
2088 -- initialization of object variables
2089 l_list := WF_PARAMETER_LIST_T();
2090
2091 -- Add Context values to the list
2092 AddParamEnvToList(l_list);
2093 l_param := WF_PARAMETER_T( NULL, NULL );
2094
2095 -- fill the parameters list
2096 l_list.extend;
2097 l_param.SetName( 'CUSTOMER_ID' );
2098 l_param.SetValue( p_cust_id );
2099 l_list(l_list.last) := l_param;
2100
2101 -- Debug Message
2102 IF l_debug THEN
2103 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2104 'Calling AS_BUSINESS_EVENT_PVT.raise_event');
2105 END IF;
2106
2107 -- Raise Event to do diff and raise actual event(s)
2108 raise_event(
2109 p_event_name => INT_STEAM_UPDATE_EVENT,
2110 p_event_key => p_event_key,
2111 p_parameters => l_list );
2112
2113 /* Comment the above call and uncomment the below to direclty call the
2114 method to raise events instead of raising an asynchronous event to do so
2115 l_list.extend;
2116 l_param.SetName( DIRECT_CALL );
2117 l_param.SetValue( 'Y' );
2118 l_list(l_list.last) := l_param;
2119 l_event := WF_EVENT_T(NULL, NULL, NULL, NULL, l_list, INT_STEAM_UPDATE_EVENT,
2120 p_event_key, NULL, NULL, NULL, NULL, NULL, NULL);
2121 l_event.setParameterList(l_list);
2122
2123 l_status := Raise_upd_STeam_evnt(NULL, l_event);
2124 */
2125
2126 -- Debug Message
2127 IF l_debug THEN
2128 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2129 'Private API: ' || l_api_name || ' end');
2130 END IF;
2131 END Upd_Cust_STeam_post_event;
2132
2133 END AS_BUSINESS_EVENT_PVT;