DBA Data[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;