[Home] [Help]
PACKAGE BODY: APPS.IEX_TERR_PUB
Source
1 PACKAGE BODY IEX_TERR_PUB AS
2 /* $Header: iexkterb.pls 120.10.12020000.2 2012/12/06 12:49:50 schekuri ship $ */
3
4
5 --
6 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
7 -- directory of SQL Navigator
8 --
9 -- Purpose: Briefly explain the functionality of the package body
10 --
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- --------- ------ ------------------------------------------
14 -- Enter procedure, function bodies as shown below
15 -- Trace HZ: Turn On File Debug
16 --- HZ: API Debug File Directory
17 --- HZ: API Debug File Name
18 ---
19 --- HZ_BUSINESS_EVENT_V2PVT check package of all events
20
21 FUNCTION isRefreshProgramsRunning RETURN BOOLEAN IS
22 CURSOR C1 IS
23 select request_id
24 from AR_CONC_PROCESS_REQUESTS
25 where CONCURRENT_PROGRAM_NAME in ('ARSUMREF','IEX_POPULATE_UWQ_SUM');
26 l_request_id number;
27 BEGIN
28
29 OPEN C1;
30
31 FETCH C1 INTO l_request_id;
32
33 IF C1%NOTFOUND THEN
34 return false;
35 ELSE
36 return true;
37 END IF;
38
39 CLOSE C1;
40
41 END isRefreshProgramsRunning;
42
43 /** subscription function example
44 * oracle.apps.ar.hz.Party.create
45 **/
46
47 FUNCTION party_check
48 ( p_subscription_guid in raw,
49 p_event in out NOCOPY wf_event_t)
50 return varchar2
51
52 is
53 l_key varchar2(240) := p_event.GetEventKey();
54 x_return_status VARCHAR2(10) ;
55 x_msg_count NUMBER;
56 x_msg_data VARCHAR2(2000);
57 exc EXCEPTION;
58
59 l_party_id NUMBER;
60
61 begin
62 x_return_status := 'S';
63 -- put custom code
64 -- this is just an example
65 -- writes into the log file
66 logmessage ('party_check: ' || 'EVENT NAME =>'||p_event.getEventName());
67 l_party_id := p_event.GetValueForParameter('PARTY_ID');
68 logmessage ('party_check: ' || 'PARTY_ID =>' || l_party_id);
69 --PG_DEBUG := 0;
70 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
71 iex_debug_pub.logmessage ('party_check: ' || 'EVENT NAME =>'||p_event.getEventName());
72 iex_debug_pub.logmessage ('party_check: ' || 'PARTY_ID =>' || l_party_id);
73 END IF;
74
75 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
76 iex_debug_pub.logmessage (' Start IEX Summarty Synchronization ');
77 END IF;
78
79 IF NOT isRefreshProgramsRunning THEN
80 x_return_status := SYNC_TCA_SUMMARY(p_party_id =>l_party_id,p_level=>'PARTY');
81 IF x_return_status <> 'S' THEN
82 RAISE EXC;
83 END IF;
84 ELSE
85 IF pg_debug <=10
86 THEN
87 iex_debug_pub.LogMessage('IEX_TERR_PUB.party_check Skipped ' );
88 END IF;
89 END IF;
90
91 RETURN 'SUCCESS';
92
93 EXCEPTION
94 WHEN EXC THEN
95 logmessage ('exception exc party_check: ' || 'EVENT NAME =>'||p_event.getEventName());
96 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
97 iex_debug_pub.logmessage ('party_check: ' || 'raised exe error');
98 END IF;
99 WF_CORE.CONTEXT('IEX_TERR_PUB', 'party_check', p_event.getEventName(), p_subscription_guid);
100 WF_EVENT.setErrorInfo(p_event, 'ERROR');
101 RETURN 'ERROR';
102 WHEN OTHERS THEN
103 logmessage ('exception others party_check: ' || 'EVENT NAME =>'||p_event.getEventName());
104 WF_CORE.CONTEXT('IEX_TERR_PUB', 'party_check', p_event.getEventName(), p_subscription_guid);
105 WF_EVENT.setErrorInfo(p_event, 'ERROR');
106 RETURN 'ERROR';
107
108 END party_check;
109
110
111
112 /** subscription function example
113 * oracle.apps.ar.hz.PartySite.create
114 **/
115 FUNCTION partysite_check
116 ( p_subscription_guid in raw,
117 p_event in out NOCOPY wf_event_t)
118 return varchar2
119 is
120 l_key varchar2(240) := p_event.GetEventKey();
121 x_return_status VARCHAR2(10) ;
122 x_msg_count NUMBER;
123 x_msg_data VARCHAR2(2000);
124 exc EXCEPTION;
125
126 l_partysite_id NUMBER;
127
128 begin
129 x_return_status := 'S';
130 -- put custom code
131 -- this is just an example
132 -- writes into the log file
133 logmessage ('partysite_check: ' || 'EVENT NAME =>'||p_event.getEventName());
134 l_partysite_id := p_event.GetValueForParameter('PARTY_SITE_ID');
135 logmessage ('partysite_check: ' || 'PARTY_SITE_ID =>' || l_partysite_id);
136 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
137 iex_debug_pub.logmessage ('partysite_check: ' || 'EVENT NAME =>'||p_event.getEventName());
138 iex_debug_pub.logmessage ('partysite_check: ' || 'PARTY_SITE_ID =>' || l_partysite_id);
139 END IF;
140
141 IF x_return_status <> 'S' THEN
142 RAISE EXC;
143 END IF;
144
145 RETURN 'SUCCESS';
146
147 EXCEPTION
148 WHEN EXC THEN
149 logmessage ('exception exc partysite_check: ' || 'EVENT NAME =>'||p_event.getEventName());
150 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
151 iex_debug_pub.logmessage ('partysite_check: ' || 'raised exe error');
152 END IF;
153 WF_CORE.CONTEXT('IEX_TERR_PUB', 'partysite_check', p_event.getEventName(), p_subscription_guid);
154 WF_EVENT.setErrorInfo(p_event, 'ERROR');
155 RETURN 'ERROR';
156 WHEN OTHERS THEN
157 logmessage ('exception others partysite_check: ' || 'EVENT NAME =>'||p_event.getEventName());
158 WF_CORE.CONTEXT('IEX_TERR_PUB', 'partysite_check', p_event.getEventName(), p_subscription_guid);
159 WF_EVENT.setErrorInfo(p_event, 'ERROR');
160 RETURN 'ERROR';
161
162
163 END partysite_check;
164
165
166 /** subscription function example
167 * oracle.apps.ar.hz.PartySiteUse.create
168 **/
169 FUNCTION partysiteuse_check
170 ( p_subscription_guid in raw,
171 p_event in out NOCOPY wf_event_t)
172 return varchar2
173 is
174 l_key varchar2(240) := p_event.GetEventKey();
175 x_return_status VARCHAR2(10) ;
176 x_msg_count NUMBER;
177 x_msg_data VARCHAR2(2000);
178 exc EXCEPTION;
179
180 l_partysiteuse_id NUMBER;
181
182 begin
183 x_return_status := 'S';
184 -- put custom code
185 -- this is just an example
186 -- writes into the log file
187 logmessage ('partysiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
188 l_partysiteuse_id := p_event.GetValueForParameter('PARTY_SITE_USE_ID');
189 --PG_DEBUG := 0;
190
191 logmessage ('partysiteuse_check: ' || 'PARTY_SITE_USE_ID =>' || l_partysiteuse_id);
192
193 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
194 iex_debug_pub.logmessage ('partysiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
195 iex_debug_pub.logmessage ('partysiteuse_check: ' || 'PARTY_SITE_USE_ID =>' || l_partysiteuse_id);
196 END IF;
197
198 IF x_return_status <> 'S' THEN
199 RAISE EXC;
200 END IF;
201
202 RETURN 'SUCCESS';
203
204 EXCEPTION
205 WHEN EXC THEN
206 logmessage ('Exception exc partysiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
207 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
208 iex_debug_pub.logmessage ('partysiteuse_check: ' || 'raised exe error');
209 END IF;
210 WF_CORE.CONTEXT('IEX_TERR_PUB', 'partysiteuse_check', p_event.getEventName(), p_subscription_guid);
211 WF_EVENT.setErrorInfo(p_event, 'ERROR');
212 RETURN 'ERROR';
213 WHEN OTHERS THEN
214 logmessage ('Exception Others partysiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
215 WF_CORE.CONTEXT('IEX_TERR_PUB', 'partysite_check', p_event.getEventName(), p_subscription_guid);
216 WF_EVENT.setErrorInfo(p_event, 'ERROR');
217 RETURN 'ERROR';
218
219
220 END partysiteuse_check;
221
222
223 /** subscription function example
224 * oracle.apps.ar.hz.location.create
225 **/
226 FUNCTION location_check
227 ( p_subscription_guid in raw,
228 p_event in out NOCOPY wf_event_t)
229 return varchar2
230 is
231 l_key varchar2(240) := p_event.GetEventKey();
232 x_return_status VARCHAR2(10) ;
233 x_msg_count NUMBER;
234 x_msg_data VARCHAR2(2000);
235 exc EXCEPTION;
236
237 l_location_id NUMBER;
238
239 begin
240 x_return_status := 'S';
241 -- put custom code
242 -- this is just an example
243 -- writes into the log file
244 logmessage ('location_check: ' || 'EVENT NAME =>'||p_event.getEventName());
245 l_location_id := p_event.GetValueForParameter('LOCATION_ID');
246 --PG_DEBUG := 0;
247 logmessage ('location_check: ' || 'LOCATION_ID =>' || l_location_id);
248
249 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
250 iex_debug_pub.logmessage ('location_check: ' || 'EVENT NAME =>'||p_event.getEventName());
251 iex_debug_pub.logmessage ('location_check: ' || 'PARTY_SITE_USE_ID =>' || l_location_id);
252 END IF;
253
254 IF x_return_status <> 'S' THEN
255 RAISE EXC;
256 END IF;
257
258 RETURN 'SUCCESS';
259
260 EXCEPTION
261 WHEN EXC THEN
262 logmessage ('Exception exc location_check: ' || 'EVENT NAME =>'||p_event.getEventName());
263 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
264 iex_debug_pub.logmessage ('location_check: ' || 'raised exe error');
265 END IF;
266 WF_CORE.CONTEXT('IEX_TERR_PUB', 'location_check', p_event.getEventName(), p_subscription_guid);
267 WF_EVENT.setErrorInfo(p_event, 'ERROR');
268 RETURN 'ERROR';
269 WHEN OTHERS THEN
270 logmessage ('Exception Others location_check: ' || 'EVENT NAME =>'||p_event.getEventName());
271 WF_CORE.CONTEXT('IEX_TERR_PUB', 'location_check', p_event.getEventName(), p_subscription_guid);
272 WF_EVENT.setErrorInfo(p_event, 'ERROR');
273 RETURN 'ERROR';
274
275 END location_check;
276
277 /** subscription function example
278 * oracle.apps.ar.hz.CustAccount.create
279 **/
280 FUNCTION account_check
281 ( p_subscription_guid in raw,
282 p_event in out NOCOPY wf_event_t)
283 return varchar2
284 is
285 l_key varchar2(240) := p_event.GetEventKey();
286 x_return_status VARCHAR2(10);
287 x_msg_count NUMBER;
288 x_msg_data VARCHAR2(2000);
289 exc EXCEPTION;
290
291 l_custaccount_id NUMBER;
292 l_profile_id NUMBER;
293 l_prof_amt_id VARCHAR2(100);
294 l_party_id NUMBER;
295 l_siteuse_id NUMBER;
296
297 l_rowid ROWID;
298 l_return_status VARCHAR2(10);
299 l_object_version_number NUMBER;
300 l_last_update_date DATE;
301 l_debug_level NUMBER;
302 l_debug VARCHAR2(1);
303
304 x_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
305 l_trans_rec JTY_ASSIGN_REALTIME_PUB.bulk_trans_id_type;
306 l_count NUMBER;
307 l_rsc_name VARCHAR2(240);
308 BEGIN
309 x_return_status := 'S';
310 l_debug_level := NVL(TO_NUMBER(FND_PROFILE.VALUE('IEX_DEBUG_LEVEL')),20);
311 -- put custom code
312 -- this is just an example
313 -- writes into the log file
314 logmessage ('Account Check: ' || 'EVENT NAME =>'||p_event.getEventName());
315 l_custaccount_id := p_event.GetValueForParameter('CUST_ACCOUNT_ID');
316 l_profile_id := p_event.GetValueForParameter('CUST_ACCOUNT_PROFILE_ID');
317 l_prof_amt_id := p_event.GetValueForParameter('P_CREATE_PROFILE_AMT');
318
319 BEGIN
320 SELECT party_id INTO l_party_id
321 FROM hz_cust_accounts
322 WHERE cust_account_id = l_custaccount_id;
323 EXCEPTION WHEN OTHERS THEN
324 logmessage('Error while selecting the Party_id' || SQLERRM);
325 END;
326
327 --PG_DEBUG := 0;
328 logmessage ('Account Check: ' || 'PARTY_ID =>' || l_party_id);
329 logmessage ('Account Check: ' || 'CUST_ACCOUNT_ID =>' || l_custAccount_id);
330 logmessage ('Account Check: ' || 'CUST_ACCOUNT_PROFILE_ID =>' || l_profile_id);
331 logmessage ('Account Check: ' || 'P_CREATE_PROFILE_AMT =>' ||l_prof_amt_id);
332
333
334 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
335 iex_debug_pub.logmessage ('account_check: ' || 'EVENT NAME =>'||p_event.getEventName());
336 iex_debug_pub.logmessage ('account_check: ' || 'PARTY_ID =>' || l_party_id);
337 iex_debug_pub.logmessage ('account_check: ' || 'CUST_ACCOUNT_ID =>' || l_custaccount_id);
338 iex_debug_pub.logmessage ('account_check: ' || 'CUST_ACCOUNT_PROFILE_ID =>' || l_profile_id);
339 iex_debug_pub.logmessage ('account_check: ' || 'P_CREATE_PROFILE_AMT =>' ||l_prof_amt_id);
340 END IF;
341
342
343 logmessage ('account_check: ' || ' custaccount_id = '|| l_custaccount_id ||
344 ', l_siteuse_id = ' || l_siteuse_id || ', party_id ' || l_party_id);
345
346
347 IF NVL(FND_PROFILE.VALUE('IEX_ENABLE_CUST_ONLINE_TAP'),'N') <> 'Y' THEN
348 BEGIN
349 INSERT INTO IEX_CHANGED_ACCOUNTS_ALL
350 (
351 OBJECT_VERSION_NUMBER
352 ,PARTY_ID
353 ,ACCOUNT_ID
354 ,SITE_USE_ID
355 ,LAST_UPDATE_DATE
356 ,LAST_UPDATED_BY
357 ,CREATION_DATE
358 ,CREATED_BY
359 ,LAST_UPDATE_LOGIN
360 ,CHANGE_TYPE
361 ,PROCESSED_FLAG
362 )
363 VALUES
364 (
365 1
366 ,l_party_id
367 ,l_custaccount_id
368 ,null
369 ,sysdate
370 ,FND_GLOBAL.user_id
371 ,sysdate
372 ,FND_GLOBAL.user_id
373 ,FND_GLOBAL.login_id
374 ,'ACCOUNT'
375 ,'N'
376 );
377 -- COMMIT; -- Commented for bug#7678917 by PNAVEENK on 5-1-2009
378 EXCEPTION WHEN OTHERS THEN
379 logmessage('Error while creating in IEX_CHANGED_ACCOUNTS_ALL') ;
380 END;
381
382 ELSE
383 logmessage ('Account Check: ' || ' Calling Territory, l_party_id = ' || l_party_id);
384 IF (l_debug_level <=10) THEN
385 l_debug := 'Y';
386 ELSE
387 l_debug := 'N';
388 END IF;
389 BEGIN
390 l_trans_rec.trans_object_id1 := jtf_terr_number_list(l_party_id);
391 l_trans_rec.trans_object_id2 := jtf_terr_number_list(l_custaccount_id);
392 l_trans_rec.trans_object_id3 := jtf_terr_number_list(null);
393 l_trans_rec.trans_object_id4 := jtf_terr_number_list(null);
394 l_trans_rec.trans_object_id5 := jtf_terr_number_list(null);
395 l_trans_rec.txn_date := jtf_terr_date_list(null);
396
397 JTY_ASSIGN_REALTIME_PUB.get_winners(
398 p_api_version_number => 1.0,
399 p_init_msg_list => FND_API.G_FALSE,
400 p_source_id => -1600,
401 p_trans_id => -1601,
402 p_mode => 'REAL TIME:RESOURCE',
403 p_param_passing_mechanism => 'PBR',
404 p_program_name => 'COLLECTIONS/CUSTOMER PROGRAM',
405 p_trans_rec => l_trans_rec,
406 p_name_value_pair => null,
407 p_role => null,
408 p_resource_type => null,
409 x_return_status => x_return_status,
410 x_msg_count => x_msg_count,
411 x_msg_data => x_msg_data,
412 x_winners_rec => x_winners_rec
413 );
414 logmessage('Get Winners Returned with Status : ' || x_return_status);
415 EXCEPTION WHEN OTHERS THEN
416 logmessage('Error while updating the profile ' || SQLERRM);
417 END;
418 BEGIN
419 l_count := x_winners_rec.terr_id.COUNT;
420 logmessage('Total Winners selected ' || l_count);
421 IF (l_count > 0) THEN
422 logmessage('Start updating the Customer Profiles Table' );
423
424 FOR i IN x_winners_rec.terr_id.FIRST .. x_winners_rec.terr_id.LAST LOOP
425 BEGIN
426 UPDATE HZ_CUSTOMER_PROFILES ACC
427 SET object_version_number = nvl(object_version_number,0) + 1,
428 ACC.LAST_UPDATE_DATE = SYSDATE,
429 ACC.LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
430 ACC.LAST_UPDATE_LOGIN = FND_GLOBAL.login_id ,
431 ACC.PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
432 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
433 ACC.COLLECTOR_ID = x_winners_rec.resource_id(i)
434 WHERE ACC.PARTY_ID = x_winners_rec.trans_object_id(i)
435 AND ACC.SITE_USE_ID IS NULL
436 AND ACC.CUST_ACCOUNT_ID = l_custaccount_id
437 AND ACC.COLLECTOR_ID <> x_winners_rec.resource_id(i);
438 EXCEPTION WHEN OTHERS THEN
439 logmessage('Error occured while updating the customer profiles' || SQLERRM);
440 END;
441 BEGIN
442 SELECT resource_name
443 INTO l_rsc_name
444 FROM jtf_rs_resource_extns_vl
445 WHERE resource_id = x_winners_rec.resource_id(i);
446 EXCEPTION WHEN OTHERS THEN
447 NULL;
448 END;
449 logmessage('Trans Object ID : ' || x_winners_rec.trans_object_id(i) ||
450 ' Trans Detail Object ID : ' || x_winners_rec.trans_detail_object_id(i) ||
451 ' Terr ID : ' || x_winners_rec.terr_id(i) || ' Terr Name : ' || x_winners_rec.terr_name(i) ||
452 ' Resource ID : ' || x_winners_rec.resource_id(i) || ' Resource Name : ' || l_rsc_name ||
453 ' Role ID : ' || x_winners_rec.role_id(i) || ' Resource Type : ' || x_winners_rec.resource_type(i) ||
454 ' Full Access Flag : ' || x_winners_rec.full_access_flag(i));
455 END LOOP;
456 ELSE
457 logmessage('No Winners selected... No rows Updated');
458 END IF;
459 EXCEPTION WHEN OTHERS THEN
460 logmessage('Error While updating the Customer Profiles Table' || SQLERRM);
461 END;
462
463 END IF;
464
465 IF x_return_status <> 'S' THEN
466 RAISE EXC;
467 END IF;
468
469 IF NOT isRefreshProgramsRunning THEN
470 x_return_status := SYNC_TCA_SUMMARY(p_account_id => l_custaccount_id,p_level=>'ACCOUNT');
471
472 IF x_return_status <> 'S' THEN
473 RAISE EXC;
474 END IF;
475 ELSE
476 IF pg_debug <=10
477 THEN
478 iex_debug_pub.LogMessage('IEX_TERR_PUB.account_check Skipped ' );
479 END IF;
480 END IF;
481
482 RETURN 'SUCCESS';
483
484 EXCEPTION
485 WHEN EXC THEN
486 logmessage ('Exception exc account_check: ' || 'EVENT NAME =>'||p_event.getEventName());
487 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
488 iex_debug_pub.logmessage ('account_check: ' || 'raised exe error');
489 END IF;
490 WF_CORE.CONTEXT('IEX_TERR_PUB', 'account_check', p_event.getEventName(), p_subscription_guid);
491 WF_EVENT.setErrorInfo(p_event, 'ERROR');
492 RETURN 'ERROR';
493 WHEN OTHERS THEN
494 logmessage ('Exception Others account_check: ' || 'EVENT NAME =>'||p_event.getEventName());
495 WF_CORE.CONTEXT('IEX_TERR_PUB', 'account_check', p_event.getEventName(), p_subscription_guid);
496 WF_EVENT.setErrorInfo(p_event, 'ERROR');
497 RETURN 'ERROR';
498
499 END account_check;
500
501 /** subscription function example
502 *
503 **/
504 FUNCTION profile_check
505 ( p_subscription_guid in raw,
506 p_event in out NOCOPY wf_event_t)
507 return varchar2
508 is
509 l_key varchar2(240) := p_event.GetEventKey();
510 x_return_status VARCHAR2(10):='S';
511 x_msg_count NUMBER;
512 x_msg_data VARCHAR2(2000);
513 exc EXCEPTION;
514
515 l_profile_id NUMBER;
516 l_prof_amt VARCHAR2(100);
517
518 l_party_id NUMBER;
519 l_siteuse_id NUMBER;
520 l_custaccount_id NUMBER;
521 l_partysite_id NUMBER;
522 l_collector_id NUMBER;
523 begin
524 l_profile_id := p_event.GetValueForParameter('CUST_ACCOUNT_PROFILE_ID');
525
526 logMessage ('profile_check: ' || 'EVENT NAME =>'||p_event.getEventName());
527 logMessage ('profile_check: ' || 'CUST_ACCOUNT_PROFILE_ID =>' || l_profile_id);
528
529 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
530 iex_debug_pub.logmessage ('profile_check: ' || 'EVENT NAME =>'||p_event.getEventName());
531 iex_debug_pub.logmessage ('profile_check: ' || 'CUST_ACCOUNT_PROFILE_ID =>' || l_profile_id);
532 END IF;
533
534 BEGIN
535 IF NOT isRefreshProgramsRunning THEN
536
537 SELECT cust_account_id, site_use_id, party_id,collector_id
538 into l_custaccount_id, l_siteuse_id, l_party_id ,l_collector_id
539 from hz_customer_profiles where CUST_ACCOUNT_PROFILE_id = l_profile_id;
540
541 logMessage ('profile_check: ' || 'l_custaccount_id =>'||l_custaccount_id);
542 logMessage ('profile_check: ' || 'l_siteuse_id =>' || l_siteuse_id);
543 logMessage ('profile_check: ' || 'l_collector_id =>' || l_collector_id);
544
545 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
546 iex_debug_pub.logmessage ('profile_check: ' || 'l_custaccount_id =>'||l_custaccount_id);
547 iex_debug_pub.logmessage ('profile_check: ' || 'l_siteuse_id =>' || l_siteuse_id);
548 iex_debug_pub.logmessage ('profile_check: ' || 'l_collector_id =>' || l_collector_id);
549 END IF;
550
551 x_return_status := SYNC_TCA_SUMMARY
552 ( p_party_id => l_party_id,
553 p_account_id => l_custaccount_id,
554 p_site_use_id => l_siteuse_id,
555 p_collector_id => l_collector_id,
556 p_level => 'PROFILE');
557
558
559
560 IF x_return_status <> 'S' THEN
561 RAISE EXC;
562 END IF;
563 ELSE
564 IF pg_debug <=10
565 THEN
566 iex_debug_pub.LogMessage('IEX_TERR_PUB.profile_check Skipped ' );
567 END IF;
568 END IF;
569
570
571 EXCEPTION
572 WHEN others then
573 iex_debug_pub.logmessage (' Error in profile_check: ' || 'P_CUST_ACCOUNT_PROFILE_ID =>' ||l_profile_id);
574 logmessage ('Exception occurred profile_check: ' || 'P_CUST_ACCOUNT_PROFILE_IDt =>'|| l_profile_id);
575 END;
576
577 RETURN 'SUCCESS';
578
579 EXCEPTION
580 WHEN EXC THEN
581 logmessage ('exception in profile_check: ' || 'EVENT NAME =>'||p_event.getEventName());
582 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
583 iex_debug_pub.logmessage ('profile_check: ' || 'raised exe error');
584 END IF;
585 WF_CORE.CONTEXT('IEX_TERR_PUB', 'profile_check', p_event.getEventName(), p_subscription_guid);
586 WF_EVENT.setErrorInfo(p_event, 'ERROR');
587 RETURN 'ERROR';
588 WHEN OTHERS THEN
589 logmessage ('exception others profile_check: ' || 'EVENT NAME =>'||p_event.getEventName());
590 WF_CORE.CONTEXT('IEX_TERR_PUB', 'profile_check', p_event.getEventName(), p_subscription_guid);
591 WF_EVENT.setErrorInfo(p_event, 'ERROR');
592 RETURN 'ERROR';
593
594
595 END profile_check;
596
597 /** subscription function example
598 * oracle.apps.ar.hz.CustProfileAmt.create
599 **/
600 FUNCTION profileamt_check
601 ( p_subscription_guid in raw,
602 p_event in out NOCOPY wf_event_t)
603 return varchar2
604 is
605 l_key varchar2(240) := p_event.GetEventKey();
606 x_return_status VARCHAR2(10) ;
607 x_msg_count NUMBER;
608 x_msg_data VARCHAR2(2000);
609 exc EXCEPTION;
610
611 l_prof_amt VARCHAR2(100);
612
613 begin
614 x_return_status := 'S';
615 -- put custom code
616 -- this is just an example
617 -- writes into the log file
618 logmessage ('profileamt_check: ' || 'EVENT NAME =>'||p_event.getEventName());
619
620 l_prof_amt := p_event.GetValueForParameter('CUST_ACCT_PROFILE_AMT_ID');
621 logmessage ('profileamt_check: ' || 'CUST_ACCT_PROFILE_AMT_ID =>'|| l_prof_amt);
622
623 PG_DEBUG := 0;
624
625 --IF PG_DEBUG < 10 THEN
626 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
627 iex_debug_pub.logmessage ('profileamt_check: ' || 'EVENT NAME =>'||p_event.getEventName());
628 iex_debug_pub.logmessage ('profileamt_check: ' || 'CUST_ACCT_PROFILE_AMT_ID =>' || l_prof_amt);
629 END IF;
630
631 IF x_return_status <> 'S' THEN
632 RAISE EXC;
633 END IF;
634
635 RETURN 'SUCCESS';
636
637 EXCEPTION
638 WHEN EXC THEN
639 logmessage ('exception exc profileamt_check: ' || 'EVENT NAME =>'||p_event.getEventName());
640 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
641 iex_debug_pub.logmessage ('profileamt_check: ' || 'raised exe error');
642 END IF;
643 WF_CORE.CONTEXT('IEX_TERR_PUB', 'profileamt_check', p_event.getEventName(), p_subscription_guid);
644 WF_EVENT.setErrorInfo(p_event, 'ERROR');
645 RETURN 'ERROR';
646 WHEN OTHERS THEN
647 logmessage ('exception others profileamt_check: ' || 'EVENT NAME =>'||p_event.getEventName());
648 WF_CORE.CONTEXT('IEX_TERR_PUB', 'profileamt_check', p_event.getEventName(), p_subscription_guid);
649 WF_EVENT.setErrorInfo(p_event, 'ERROR');
650 RETURN 'ERROR';
651
652
653 END profileamt_check;
654
655
656 /** subscription function example
657 * for CustAcctSite.create
658 **/
659 FUNCTION accountsite_check
660 ( p_subscription_guid in raw,
661 p_event in out NOCOPY wf_event_t)
662 return varchar2
663 is
664 l_key varchar2(240) := p_event.GetEventKey();
665 x_return_status VARCHAR2(10) ;
666 x_msg_count NUMBER;
667 x_msg_data VARCHAR2(2000);
668 exc EXCEPTION;
669
670 l_acct_site_id NUMBER;
671
672 begin
673 x_return_status := 'S';
674 -- this is just an example
675 -- writes into the log file
676 logmessage ('acctsite_check: ' || 'EVENT NAME =>'||p_event.getEventName());
677 l_acct_site_id := p_event.GetValueForParameter('CUST_ACCT_SITE_ID');
678 logmessage ('acctsite_check: ' || 'ACCOUNT_SITE_ID =>' || l_acct_site_id);
679
680 --IF PG_DEBUG < 10 THEN
681 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
682 iex_debug_pub.logmessage ('acctsite_check: ' || 'EVENT NAME =>'||p_event.getEventName());
683 iex_debug_pub.logmessage ('acctsite_check: ' || 'ACCOUNT_SITE_ID =>' || l_acct_site_id );
684 END IF;
685
686 IF x_return_status <> 'S' THEN
687 RAISE EXC;
688 END IF;
689 RETURN 'SUCCESS';
690
691 EXCEPTION
692 WHEN EXC THEN
693 logmessage ('Exception exc acctsite_check: ' || 'EVENT NAME =>'||p_event.getEventName());
694 -- IF PG_DEBUG < 10 THEN
695 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
696 iex_debug_pub.logmessage ('acctsite_check: ' || 'raised exe error');
697 END IF;
698 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'acctsite_check', p_event.getEventName(), p_subscription_guid);
699 WF_EVENT.setErrorInfo(p_event, 'ERROR');
700 RETURN 'ERROR';
701 WHEN OTHERS THEN
702 logmessage ('Exception others acctsite_check: ' || 'EVENT NAME =>'||p_event.getEventName());
703 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'acctsite_check', p_event.getEventName(), p_subscription_guid);
704 WF_EVENT.setErrorInfo(p_event, 'ERROR');
705 RETURN 'ERROR';
706
707 END accountsite_check;
708
709
710 /** subscription function example
711 * for oracle.apps.ar.hz.CustAcctSiteUse.create
712 **/
713 FUNCTION accountsiteuse_check
714 ( p_subscription_guid in raw,
715 p_event in out NOCOPY wf_event_t)
716 return varchar2
717 is
718 l_key varchar2(240) := p_event.GetEventKey();
719 x_return_status VARCHAR2(10);
720 x_msg_count NUMBER;
721 x_msg_data VARCHAR2(2000);
722 exc EXCEPTION;
723
724 l_acctsiteuse_id NUMBER;
725 l_profile_id NUMBER;
726 l_profile VARCHAR2(100);
727 l_profile_amt VARCHAR2(100);
728 l_party_id NUMBER;
729 l_party_site_id NUMBER;
730 l_cust_account_id NUMBER;
731 l_debug_level NUMBER;
732 l_debug VARCHAR2(1);
733
734 l_rowid ROWID;
735 l_return_status VARCHAR2(10);
736 l_object_version_number NUMBER;
737 l_last_update_date DATE;
738 l_msg_count NUMBER;
739 l_msg_data VARCHAR2(200);
740
741
742
743 begin
744 x_return_status := 'S';
745 l_debug_level := NVL(TO_NUMBER(FND_PROFILE.VALUE('IEX_DEBUG_LEVEL')),20);
746 -- put custom code
747 -- this is just an example
748 -- writes into the log file
749
750 -- l_param.SetName( 'SITE_USE_ID' );
751 -- l_param.SetName( 'CUST_ACCOUNT_PROFILE_ID' );
752
753 -- l_param.SetName( 'P_CREATE_PROFILE' );
754 --- l_param.SetName( 'P_CREATE_PROFILE_AMT' );
755
756 logmessage ('acctsiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
757 l_acctsiteuse_id := p_event.GetValueForParameter('SITE_USE_ID');
758 l_profile_id := p_event.GetValueForParameter('CUST_ACCOUNT_PROFILE_ID');
759 l_profile := p_event.GetValueForParameter('P_CREATE_PROFILE');
760 l_profile_amt := p_event.GetValueForParameter('P_CREATE_PROFILE_AMT');
761
762 logmessage ('acctsiteuse_check: ' || 'SITE_USE_ID =>' || l_acctsiteuse_id);
763 logmessage ('acctsiteuse_check: ' || 'CUST_ACCOUNT_PROFILE_ID =>' || l_profile_id);
764 logmessage ('acctsiteuse_check: ' || 'P_CREATE_PROFILE =>' || l_profile);
765 logmessage ('acctsiteuse_check: ' || 'P_CREATE_PROFILE_AMT =>' || l_profile_amt);
766
767
768 logmessage ('acctsiteuse_check: ' || ' l_partysite_id = ' || l_party_site_id);
769
770 /* Let us do site level, Territory Assignment */
771 IF (l_debug_level <=10) THEN
772 l_debug := 'Y';
773 ELSE
774 l_debug := 'N';
775 END IF;
776 --Bug4957592. Fix By LKKUMAR on 17-Jan-2006. Start.
777 /*
778 IEX_TERRITORY_ASSIGNMENT.ASSIGN_TERRITORY
779 (p_api_version => 1.0,
780 p_calling_mode => 'X',
781 p_debug => l_debug,
782 p_filter_mode => 'BILLTOSITE',
783 p_selection_mode => 'Specific',
784 p_filter_id => l_acctsiteuse_id,
785 x_return_status => l_return_status,
786 x_msg_count => l_msg_count,
787 x_msg_data => l_msg_data);
788 */
789 --Bug4957592. Fix By LKKUMAR on 17-Jan-2006. End.
790
791 logmessage ('acctsiteuse_check: ' || ' x_return_status => '|| l_return_status );
792 logmessage ('acctsiteuse_check: ' || ' x_msg_count => ' || l_msg_count);
793 logmessage ('acctsiteuse_check: ' || ' x_msg_data => ' || l_msg_data);
794
795 --IF PG_DEBUG < 10 THEN
796 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
797 iex_debug_pub.logmessage ('acctsiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
798 iex_debug_pub.logmessage ('acctsiteuse_check: ' || '_ID =>' || l_acctsiteuse_id );
799 END IF;
800
801 IF x_return_status <> 'S' THEN
802 RAISE EXC;
803 END IF;
804 RETURN 'SUCCESS';
805
806 EXCEPTION
807 WHEN EXC THEN
808 logmessage ('Exception exc acctsiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
809 -- IF PG_DEBUG < 10 THEN
810 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
811 iex_debug_pub.logmessage ('acctsiteuse_check: ' || 'raised exe error');
812 END IF;
813 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'acctsiteuse_check', p_event.getEventName(), p_subscription_guid);
814 WF_EVENT.setErrorInfo(p_event, 'ERROR');
815 RETURN 'ERROR';
816 WHEN OTHERS THEN
817 logmessage ('Exception others acctsiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
818 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'acctsiteuse_check', p_event.getEventName(), p_subscription_guid);
819 WF_EVENT.setErrorInfo(p_event, 'ERROR');
820 RETURN 'ERROR';
821
822 END accountsiteuse_check;
823
824 /** subscription function example
825 * for oracle.apps.ar.hz.CustAcctSiteUse.create
826 **/
827 FUNCTION finprofile_check
828 ( p_subscription_guid in raw,
829 p_event in out NOCOPY wf_event_t)
830 return varchar2
831 is
832 l_key varchar2(240) := p_event.GetEventKey();
833 x_return_status VARCHAR2(10) ;
834 x_msg_count NUMBER;
835 x_msg_data VARCHAR2(2000);
836 exc EXCEPTION;
837
838 l_finprofile_id NUMBER;
839
840 begin
841 x_return_status := 'S';
842 -- put custom code
843 -- this is just an example
844 -- writes into the log file
845
846 -- l_param.SetName( 'SITE_USE_ID' );
847 -- l_param.SetName( 'CUST_ACCOUNT_PROFILE_ID' );
848
849 -- l_param.SetName( 'P_CREATE_PROFILE' );
850 --- l_param.SetName( 'P_CREATE_PROFILE_AMT' );
851
852 logmessage ('acctsiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
853 l_finprofile_id := p_event.GetValueForParameter('FINANCIAL_PROFILE_ID');
854 logmessage ('acctsiteuse_check: ' || 'FINANCIAL_PROFILE_ID =>' || l_finprofile_id);
855
856 --IF PG_DEBUG < 10 THEN
857 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
858 iex_debug_pub.logmessage ('acctsiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
859 iex_debug_pub.logmessage ('acctsiteuse_check: ' || '_ID =>' || l_finprofile_id );
860 END IF;
861
862 IF x_return_status <> 'S' THEN
863 RAISE EXC;
864 END IF;
865 RETURN 'SUCCESS';
866
867 EXCEPTION
868 WHEN EXC THEN
869 logmessage ('Exception exc acctsiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
870 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
871 iex_debug_pub.logmessage ('finprofile_check: ' || 'raised exe error');
872 END IF;
873 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'finprofile_check', p_event.getEventName(), p_subscription_guid);
874 WF_EVENT.setErrorInfo(p_event, 'ERROR');
875 RETURN 'ERROR';
876 WHEN OTHERS THEN
877 logmessage ('Exception others acctsiteuse_check: ' || 'EVENT NAME =>'||p_event.getEventName());
878 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'finprofile_check', p_event.getEventName(), p_subscription_guid);
879 WF_EVENT.setErrorInfo(p_event, 'ERROR');
880 RETURN 'ERROR';
881
882 END finprofile_check;
883
884 --IEX Summary Table Synchronization Start.
885 FUNCTION SYNC_TCA_SUMMARY(
886 p_party_id in number default null,
887 p_account_id in number default null,
888 p_site_use_id in number default null,
889 p_collector_id in number default null,
890 p_level in varchar2)
891 return varchar2 IS
892
893 l_sql varchar2(3000);
894 l_where varchar2(240);
895 Type refCur is Ref Cursor;
896 l_curs refCur;
897
898 l_party_name hz_parties.party_name%type;
899 l_account_name hz_cust_accounts.account_name%type;
900 l_address1 varchar2(240);
901 l_city varchar2(240);
902 l_state varchar2(240);
903 l_county varchar2(240);
904 l_country varchar2(240);
905 l_province varchar2(240);
906 l_postal_code varchar2(240);
907 l_phone_country_code varchar2(240);
908 l_phone_area_code varchar2(240);
909 l_phone_number varchar2(240);
910 l_phone_extension varchar2(240);
911 l_party_id number;
912 l_resource_name varchar2(240);
913
914 l_resource_id ar_collectors.resource_id%TYPE;
915 l_resource_type ar_collectors.resource_type%TYPE;
916 l_ieu_param_pk_col varchar2(20); --Added for bug#6833110 by PNAVEENK on 29-Aug-2008
917
918
919 BEGIN
920 IF p_level in ('PARTY','ACCOUNT') THEN
921 l_sql := 'SELECT ' ||
922 ' party.party_name party_name, ' ||
923 ' acc.account_name account_name, ' ||
924 ' party.address1 address1, ' ||
925 ' party.city city, ' ||
926 ' party.state state, ' ||
927 ' party.county county, ' ||
928 ' fnd_terr.territory_short_name country, ' ||
929 ' party.province province, ' ||
930 ' party.postal_code postal_code, ' ||
931 ' phone.phone_country_code phone_country_code, ' ||
932 ' phone.phone_area_code phone_area_code, ' ||
933 ' phone.phone_number phone_number, ' ||
934 ' phone.phone_extension phone_extension ' ||
935 ' FROM ' ||
936 ' hz_cust_accounts acc, ' ||
937 ' hz_parties party, ' ||
938 ' fnd_territories_tl fnd_terr, ' ||
939 ' hz_contact_points phone ' ||
940 ' WHERE ' ||
941 ' acc.party_id = party.party_id ' ||
942 ' AND phone.owner_table_id(+) = party.party_id ' ||
943 ' AND phone.owner_table_name(+) = ''HZ_PARTIES'' ' ||
944 ' AND phone.contact_point_type(+) = ''PHONE'' ' ||
945 ' AND phone.primary_by_purpose(+) = ''Y'' ' ||
946 ' AND phone.contact_point_purpose(+) = ''COLLECTIONS'' ' ||
947 ' AND phone.phone_line_type(+) NOT IN(''PAGER'', ''FAX'') ' ||
948 ' AND phone.status(+) = ''A'' ' ||
949 ' AND nvl(phone.do_not_use_flag(+), ''N'') = ''N'' ' ||
950 ' AND party.country = fnd_terr.territory_code(+) ' ||
951 ' AND fnd_terr.LANGUAGE(+) = userenv(''LANG'') ' ;
952
953 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
954 iex_debug_pub.logmessage ('Started TCA IEX Summarty Synchronization for level ' || p_level);
955 END IF;
956
957 -- Bug #6251613 bibeura 11-Dec-2007 Used Bind Variables for Dynamic SQL to avoid performance problem
958 IF (p_level = 'PARTY') THEN
959 l_sql := l_sql || ' AND party.party_id = :1 ';
960 l_party_id := p_party_id;
961 ELSIF (p_level = 'ACCOUNT') THEN
962 l_sql := l_sql || ' AND acc.cust_account_id = :1 '; -- changed for bug 9106462 PNAVEENK
963
964 BEGIN
965 SELECT PARTY_ID INTO l_party_id from
966 HZ_CUST_ACCOUNTS WHERE
967 CUST_ACCOUNT_ID = p_party_id;
968 EXCEPTION WHEN OTHERS THEN
969 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
970 iex_debug_pub.logmessage ('IEX Summarty Synchronization , Error occurred in party_id select ' || sqlerrm);
971 END IF;
972 END;
973
974 END IF;
975 IF (p_level = 'PARTY') THEN
976 OPEN l_curs FOR l_sql using p_party_id;
977 ELSIF (p_level = 'ACCOUNT') THEN
978 OPEN l_curs FOR l_sql using p_account_id;
979 END IF;
980
981 LOOP
982 FETCH l_curs INTO
983 l_party_name,
984 l_account_name,
985 l_address1,
986 l_city,
987 l_state,
988 l_county,
989 l_country,
990 l_province,
991 l_postal_code,
992 l_phone_country_code,
993 l_phone_area_code,
994 l_phone_number,
995 l_phone_extension;
996 EXIT;
997 END LOOP;
998 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
999 iex_debug_pub.logmessage ('IEX Summarty Synchronization , fetching values ' );
1000 END IF;
1001
1002 BEGIN
1003 UPDATE IEX_DLN_UWQ_SUMMARY SET
1004 party_name = l_party_name,
1005 account_name = l_account_name,
1006 address1 = l_address1,
1007 city = l_city,
1008 state = l_state,
1009 county = l_county,
1010 country = l_country,
1011 province = l_province,
1012 postal_code = l_postal_code,
1013 phone_country_code = l_phone_country_code,
1014 phone_area_code = l_phone_area_code,
1015 phone_number = l_phone_number,
1016 phone_extension = l_phone_extension
1017 WHERE party_id = l_party_id;
1018 EXCEPTION WHEN OTHERS THEN
1019 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1020 iex_debug_pub.logmessage ('IEX Summarty Synchronization error occurred while updating ' || sqlerrm );
1021 END IF;
1022 END;
1023
1024 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1025 iex_debug_pub.logmessage ('IEX Summary Synchronization , Completed Update. ' );
1026 END IF;
1027
1028 ELSE
1029
1030 --Added for bug#6833110 by PNAVEENK on 29-Aug-2008
1031 -- start for bug 9034873 PNAVEENK
1032 /* IF p_level = 'PARTY' THEN
1033 l_ieu_param_pk_col := 'PARTY_ID';
1034 ELSIF p_level = 'ACCOUNT' THEN
1035 l_ieu_param_pk_col := 'CUST_ACCOUNT_ID';
1036 ELSIF p_level = 'BILL_TO' THEN
1037 l_ieu_param_pk_col := 'CUSTOMER_SITE_USE_ID';
1038 END IF; */
1039 --End for bug#6833110
1040 IF P_ACCOUNT_ID = -1 then
1041 l_ieu_param_pk_col := 'PARTY_ID';
1042 ELSIF P_SITE_USE_ID is null then
1043 l_ieu_param_pk_col := 'CUST_ACCOUNT_ID';
1044 ELSE
1045 l_ieu_param_pk_col := 'CUSTOMER_SITE_USE_ID';
1046 END IF;
1047
1048 -- end for bug 9034873
1049 SELECT resource_id,resource_type
1050 INTO l_resource_id,l_resource_type
1051 FROM ar_collectors
1052 WHERE collector_id = p_collector_id;
1053
1054 select resource_name into l_resource_name
1055 FROM jtf_rs_resource_extns_vl
1056 WHERE resource_id = l_resource_id;
1057
1058
1059 logMessage ('IEX Summary Synchronization ==> P_COLLECTOR_ID : ' || P_COLLECTOR_ID );
1060 logMessage ('IEX Summary Synchronization ==> L_RESOURCE_ID : ' || L_RESOURCE_ID );
1061 logMessage ('IEX Summary Synchronization ==> L_RESOURCE_TYPE : '|| L_RESOURCE_TYPE );
1062 logMessage ('IEX Summary Synchronization ==> P_PARTY_ID : ' || P_PARTY_ID );
1063 logMessage ('IEX Summary Synchronization ==> P_ACCOUNT_ID : ' || P_ACCOUNT_ID );
1064 logMessage ('IEX Summary Synchronization ==> P_SITE_USE_ID : '|| P_SITE_USE_ID );
1065 logMessage ('IEX Summary Synchronization ==> l_resource_name : '||l_resource_name);
1066 logMessage ('IEX Summary Synchronization ==> l_ieu_param_pk_col : '|| l_ieu_param_pk_col );
1067 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1068 iex_debug_pub.logmessage ('IEX Summary Synchronization ==> P_COLLECTOR_ID : ' || P_COLLECTOR_ID );
1069 iex_debug_pub.logmessage ('IEX Summary Synchronization ==> L_RESOURCE_ID : ' || L_RESOURCE_ID );
1070 iex_debug_pub.logmessage ('IEX Summary Synchronization ==> L_RESOURCE_TYPE : '|| L_RESOURCE_TYPE );
1071 iex_debug_pub.logmessage ('IEX Summary Synchronization ==> P_PARTY_ID : ' || P_PARTY_ID );
1072 iex_debug_pub.logmessage ('IEX Summary Synchronization ==> P_ACCOUNT_ID : ' || P_ACCOUNT_ID );
1073 iex_debug_pub.logmessage ('IEX Summary Synchronization ==> P_SITE_USE_ID : '|| P_SITE_USE_ID );
1074 END IF;
1075
1076 UPDATE IEX_DLN_UWQ_SUMMARY IDS
1077 SET COLLECTOR_ID = P_COLLECTOR_ID,
1078 COLLECTOR_RESOURCE_ID = L_RESOURCE_ID ,
1079 COLLECTOR_RES_TYPE = L_RESOURCE_TYPE,
1080 COLLECTOR_RESOURCE_NAME = l_resource_name
1081 WHERE COLLECTOR_ID <> P_COLLECTOR_ID
1082 AND IDS.PARTY_ID = P_PARTY_ID
1083 AND NVL(IDS.CUST_ACCOUNT_ID,1) = NVL(P_ACCOUNT_ID,NVL(IDS.CUST_ACCOUNT_ID,1))
1084 AND NVL(IDS.SITE_USE_ID,1) = NVL(P_SITE_USE_ID,NVL(IDS.SITE_USE_ID,1))
1085 AND IEU_PARAM_PK_COL = l_ieu_param_pk_col; --Added for bug#6833110 by PNAVEENK on 29-Aug-2008
1086
1087 END IF;
1088
1089 --COMMIT; -- Commented for bug#7678917 by PNAVEENK on 5-1-2009
1090
1091 RETURN 'S';
1092
1093 EXCEPTION WHEN OTHERS THEN
1094 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1095 iex_debug_pub.logmessage ('IEX Summarty Synchronization error occurred' || sqlerrm );
1096 END IF;
1097 RETURN 'F';
1098 END SYNC_TCA_SUMMARY;
1099
1100 --IEX Summary Table Synchronization End.
1101
1102 PROCEDURE logMessage (p_text in varchar2) IS
1103 begin
1104 HZ_UTILITY_V2PUB.debug( 'IEX_TERR_PUB ' || p_text );
1105 --insert into temp_log values(p_text);
1106 -- commit; --Commented for bug#15932134 schekuri 06-Dec-2012
1107 end;
1108
1109 BEGIN
1110
1111 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
1112 -- Enter further code below as specified in the Package spec.
1113 END;