eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IEX_PROCESS_ACCOUNT_WINNERS dependencies on IEX_TERR_WINNERS_PUB

Line 56: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

52: PROCEDURE CheckCollectors(
53: x_errbuf OUT NOCOPY VARCHAR2,
54: x_retcode OUT NOCOPY VARCHAR2,
55: p_worker_id IN NUMBER,
56: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
57:
58:
59: PROCEDURE AssignSiteUseCollectors(
60: x_errbuf OUT NOCOPY VARCHAR2,

Line 62: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

58:
59: PROCEDURE AssignSiteUseCollectors(
60: x_errbuf OUT NOCOPY VARCHAR2,
61: x_retcode OUT NOCOPY VARCHAR2,
62: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
63:
64: PROCEDURE AssignPartyCollectors(
65: x_errbuf OUT NOCOPY VARCHAR2,
66: x_retcode OUT NOCOPY VARCHAR2,

Line 67: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

63:
64: PROCEDURE AssignPartyCollectors(
65: x_errbuf OUT NOCOPY VARCHAR2,
66: x_retcode OUT NOCOPY VARCHAR2,
67: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
68:
69: PROCEDURE AssignAccountCollectors(
70: x_errbuf OUT NOCOPY VARCHAR2,
71: x_retcode OUT NOCOPY VARCHAR2,

Line 72: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

68:
69: PROCEDURE AssignAccountCollectors(
70: x_errbuf OUT NOCOPY VARCHAR2,
71: x_retcode OUT NOCOPY VARCHAR2,
72: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
73:
74: PROCEDURE CreatePartyProfiles(
75: x_errbuf OUT NOCOPY VARCHAR2,
76: x_retcode OUT NOCOPY VARCHAR2,

Line 78: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

74: PROCEDURE CreatePartyProfiles(
75: x_errbuf OUT NOCOPY VARCHAR2,
76: x_retcode OUT NOCOPY VARCHAR2,
77: p_worker_id IN NUMBER,
78: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
79:
80: PROCEDURE CreateSiteUseProfiles(
81: x_errbuf OUT NOCOPY VARCHAR2,
82: x_retcode OUT NOCOPY VARCHAR2,

Line 84: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

80: PROCEDURE CreateSiteUseProfiles(
81: x_errbuf OUT NOCOPY VARCHAR2,
82: x_retcode OUT NOCOPY VARCHAR2,
83: p_worker_id IN NUMBER,
84: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
85:
86:
87: PROCEDURE Process_Account_Records(
88: x_errbuf OUT NOCOPY VARCHAR2,

Line 90: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS,

86:
87: PROCEDURE Process_Account_Records(
88: x_errbuf OUT NOCOPY VARCHAR2,
89: x_retcode OUT NOCOPY VARCHAR2,
90: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS,
91: p_assignlevel IN varchar2) -- Changed for bug 8708291 pnaveenk multi level strategy
92: IS
93:
94: l_limit_flag BOOLEAN := FALSE;

Line 105: IEX_TERR_WINNERS_PUB.Print_Debug('* iextpawb.pls::IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records() *');

101: -------------------------------------------------------------------------------
102:
103:
104: BEGIN
105: IEX_TERR_WINNERS_PUB.Print_Debug('*** iextpawb.pls::IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records() ***');
106:
107: --l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY'); -- commented for bug 8708291
108: l_Assignlevel := p_assignlevel; -- Added for bug 8708291 pnaveenk multi level strategy
109: FND_FILE.PUT_LINE(FND_FILE.LOG,'Process Accounts Program started');

Line 121: IEX_TERR_WINNERS_PUB.Print_Debug('bulk size='||l_var);

117: FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level := ' ||l_Assignlevel); -- changed by gnramasa on 29/08/2006 for bug # 5487449
118:
119: l_worker_id:=p_terr_globals.worker_id;
120: l_var :=p_terr_globals.bulk_size;
121: IEX_TERR_WINNERS_PUB.Print_Debug('bulk size='||l_var);
122:
123: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CheckCollectors');
124: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
125: if (x_retcode = 'E') then

Line 123: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CheckCollectors');

119: l_worker_id:=p_terr_globals.worker_id;
120: l_var :=p_terr_globals.bulk_size;
121: IEX_TERR_WINNERS_PUB.Print_Debug('bulk size='||l_var);
122:
123: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CheckCollectors');
124: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
125: if (x_retcode = 'E') then
126: IEX_TERR_WINNERS_PUB.Print_Debug('CheckCollectors Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
127: return;

Line 126: IEX_TERR_WINNERS_PUB.Print_Debug('CheckCollectors Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');

122:
123: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CheckCollectors');
124: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
125: if (x_retcode = 'E') then
126: IEX_TERR_WINNERS_PUB.Print_Debug('CheckCollectors Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
127: return;
128: end if;
129:
130: if (l_AssignLevel = 'PARTY') then

Line 131: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreatePartyProfiles');

127: return;
128: end if;
129:
130: if (l_AssignLevel = 'PARTY') then
131: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreatePartyProfiles');
132: CreatePartyProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
133: if (x_retcode = 'E') then
134: IEX_TERR_WINNERS_PUB.Print_Debug('CreatePartyProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
135: return;

Line 134: IEX_TERR_WINNERS_PUB.Print_Debug('CreatePartyProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');

130: if (l_AssignLevel = 'PARTY') then
131: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreatePartyProfiles');
132: CreatePartyProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
133: if (x_retcode = 'E') then
134: IEX_TERR_WINNERS_PUB.Print_Debug('CreatePartyProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
135: return;
136: end if;
137: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignPartyCollectors');
138: AssignPartyCollectors(x_errbuf, x_retcode, p_terr_globals);

Line 137: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignPartyCollectors');

133: if (x_retcode = 'E') then
134: IEX_TERR_WINNERS_PUB.Print_Debug('CreatePartyProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
135: return;
136: end if;
137: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignPartyCollectors');
138: AssignPartyCollectors(x_errbuf, x_retcode, p_terr_globals);
139: elsif (l_AssignLevel = 'ACCOUNT') then
140: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
141: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignAccountCollectors');

Line 141: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignAccountCollectors');

137: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignPartyCollectors');
138: AssignPartyCollectors(x_errbuf, x_retcode, p_terr_globals);
139: elsif (l_AssignLevel = 'ACCOUNT') then
140: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
141: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignAccountCollectors');
142: AssignAccountCollectors(x_errbuf, x_retcode, p_terr_globals);
143: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. End.
144: else
145: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreateSiteUseProfiles');

Line 145: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreateSiteUseProfiles');

141: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignAccountCollectors');
142: AssignAccountCollectors(x_errbuf, x_retcode, p_terr_globals);
143: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. End.
144: else
145: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreateSiteUseProfiles');
146: CreateSiteUseProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
147: if (x_retcode = 'E') then
148: IEX_TERR_WINNERS_PUB.Print_Debug('CreateSiteuseProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
149: return;

Line 148: IEX_TERR_WINNERS_PUB.Print_Debug('CreateSiteuseProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');

144: else
145: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreateSiteUseProfiles');
146: CreateSiteUseProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
147: if (x_retcode = 'E') then
148: IEX_TERR_WINNERS_PUB.Print_Debug('CreateSiteuseProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
149: return;
150: end if;
151: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignSiteUseCollectors ');
152: AssignSiteUseCollectors(x_errbuf, x_retcode, p_terr_globals);

Line 151: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignSiteUseCollectors ');

147: if (x_retcode = 'E') then
148: IEX_TERR_WINNERS_PUB.Print_Debug('CreateSiteuseProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
149: return;
150: end if;
151: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignSiteUseCollectors ');
152: AssignSiteUseCollectors(x_errbuf, x_retcode, p_terr_globals);
153: end if;
154: if (x_retcode = 'E') then
155: return;

Line 161: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');

157:
158: EXCEPTION
159:
160: WHEN others THEN
161: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
162: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
163: ' SQLERRM: ' || SQLERRM);
164: x_errbuf := SQLERRM;
165: x_retcode := SQLCODE;

Line 162: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

158: EXCEPTION
159:
160: WHEN others THEN
161: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
162: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
163: ' SQLERRM: ' || SQLERRM);
164: x_errbuf := SQLERRM;
165: x_retcode := SQLCODE;
166: RAISE;

Line 173: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)

169:
170: PROCEDURE AssignPartyCollectors(
171: x_errbuf OUT NOCOPY VARCHAR2,
172: x_retcode OUT NOCOPY VARCHAR2,
173: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
174: IS
175: -- Included 'Collections' Role Check
176: CURSOR c_UpdateProfile(c_worker_id number) IS
177: SELECT WIN.TRANS_OBJECT_id,

Line 259: IEX_TERR_WINNERS_PUB.Print_Debug('* Started Party Level Collector Assignment *');

255: BEGIN
256:
257: select to_char( sysdate, 'DD-Mon-YYYY HH24:MI:SS') into l_date_str from dual;
258: FND_FILE.PUT_LINE(FND_FILE.LOG,'---' || l_date_str || '--------------------------');
259: IEX_TERR_WINNERS_PUB.Print_Debug('*** Started Party Level Collector Assignment ***');
260: FND_FILE.PUT_LINE(FND_FILE.LOG,'*** Started Party Level Collector Assignment ***');
261:
262: --l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
263: FND_FILE.PUT_LINE(FND_FILE.LOG,'Territory Assignment Program started');

Line 270: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);

266: l_worker_id:=p_terr_globals.worker_id;
267: -- Bulk Read the Territory Assignments
268: l_var :=p_terr_globals.bulk_size;
269: l_max_fetches := p_terr_globals.cursor_limit;
270: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
271: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
272: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Party Profiles started ... ');
273: FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating Customer Party Profiles started ... ');
274:

Line 271: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);

267: -- Bulk Read the Territory Assignments
268: l_var :=p_terr_globals.bulk_size;
269: l_max_fetches := p_terr_globals.cursor_limit;
270: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
271: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
272: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Party Profiles started ... ');
273: FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating Customer Party Profiles started ... ');
274:
275: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.

Line 272: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Party Profiles started ... ');

268: l_var :=p_terr_globals.bulk_size;
269: l_max_fetches := p_terr_globals.cursor_limit;
270: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
271: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
272: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Party Profiles started ... ');
273: FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating Customer Party Profiles started ... ');
274:
275: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
276: OPEN c_UpdateProfile(l_worker_id);

Line 284: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');

280: l_customer_id, l_salesforce_id, l_sales_group_id, l_org_id, l_Res_type, l_Collector_ID
281: LIMIT l_max_fetches;
282: IF l_customer_id.count = 0 THEN
283:
284: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
285: FND_FILE.PUT_LINE(FND_FILE.LOG,'Update Completed. Exiting the update loop');
286: CLOSE C_UPDATEPROFILE;
287: EXIT;
288:

Line 291: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);

287: EXIT;
288:
289: ELSE
290:
291: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);
292: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Rows Fetched ' || l_customer_id.count);
293:
294: FORALL i in l_customer_id.first..l_customer_id.last
295: UPDATE HZ_CUSTOMER_PROFILES ACC

Line 310: IEX_TERR_WINNERS_PUB.Print_Debug('Total Customer Party Profile Rows Updated ' || l_customer_id.count);

306: AND ACC.SITE_USE_ID IS NULL
307: AND ACC.CUST_ACCOUNT_ID = -1
308: AND ACC.COLLECTOR_ID <> l_collector_id(i);
309: --Commit When the Bulk commit size is reached.
310: IEX_TERR_WINNERS_PUB.Print_Debug('Total Customer Party Profile Rows Updated ' || l_customer_id.count);
311: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Party Profile Rows Updated ' || l_customer_id.count);
312: COMMIT;
313: END IF;
314: EXCEPTION WHEN deadlock_detected THEN

Line 316: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during party bulk update.. Performing row update..');

312: COMMIT;
313: END IF;
314: EXCEPTION WHEN deadlock_detected THEN
315: BEGIN
316: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during party bulk update.. Performing row update..');
317: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deadlock encountered during party bulk update.. Performing row update..');
318: ROLLBACK;
319:
320: FOR i in l_first .. l_last LOOP

Line 340: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');

336: AND ACC.COLLECTOR_ID <> l_collector_id(i);
337:
338: EXCEPTION
339: WHEN OTHERS THEN
340: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
341: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
342: ' SQLERRM: ' || SQLERRM);
343: END;
344: END LOOP;

Line 341: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

337:
338: EXCEPTION
339: WHEN OTHERS THEN
340: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
341: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
342: ' SQLERRM: ' || SQLERRM);
343: END;
344: END LOOP;
345: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Party Profile Rows Updated ' ||l_cnt);

Line 349: IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);

345: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Party Profile Rows Updated ' ||l_cnt);
346: l_cnt := 0;
347: END;
348: WHEN OTHERS THEN
349: IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);
350: END;
351: END LOOP;
352: IF C_UPDATEPROFILE%ISOPEN THEN
353: CLOSE C_UPDATEPROFILE;

Line 356: IEX_TERR_WINNERS_PUB.Print_Debug('* Finished Party Level Collector Assignment *');

352: IF C_UPDATEPROFILE%ISOPEN THEN
353: CLOSE C_UPDATEPROFILE;
354: END IF;
355: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
356: IEX_TERR_WINNERS_PUB.Print_Debug('*** Finished Party Level Collector Assignment ***');
357:
358: l_customer_id.delete;
359: l_terr_id.delete;
360: l_customer_id := l_customer_id_empty;

Line 376: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');

372: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
373: RAISE;
374:
375: WHEN others THEN
376: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
377: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
378: ' SQLERRM: ' || SQLERRM);
379: x_errbuf := SQLERRM;
380: x_retcode := SQLCODE;

Line 377: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

373: RAISE;
374:
375: WHEN others THEN
376: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
377: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
378: ' SQLERRM: ' || SQLERRM);
379: x_errbuf := SQLERRM;
380: x_retcode := SQLCODE;
381: RAISE;

Line 388: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)

384: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
385: PROCEDURE AssignAccountCollectors(
386: x_errbuf OUT NOCOPY VARCHAR2,
387: x_retcode OUT NOCOPY VARCHAR2,
388: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
389: IS
390: -- Included 'Collections' Role Check
391: CURSOR c_UpdateProfile(c_worker_id number) IS
392: SELECT WIN.TRANS_OBJECT_id,

Line 474: IEX_TERR_WINNERS_PUB.Print_Debug('* Started Account Level Collector Assignment *');

470: BEGIN
471:
472: select to_char( sysdate, 'DD-Mon-YYYY HH24:MI:SS') into l_date_str from dual;
473: FND_FILE.PUT_LINE(FND_FILE.LOG,'---' || l_date_str || '--------------------------');
474: IEX_TERR_WINNERS_PUB.Print_Debug('*** Started Account Level Collector Assignment ***');
475: FND_FILE.PUT_LINE(FND_FILE.LOG,'*** Started Account Level Collector Assignment ***');
476: -- l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
477: FND_FILE.PUT_LINE(FND_FILE.LOG,'Territory Assignment Program started');
478: FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level when assigning collectors at Account Level := ' ||l_Assignlevel);

Line 484: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);

480: l_worker_id:=p_terr_globals.worker_id;
481: -- Bulk Read the Territory Assignments
482: l_var :=p_terr_globals.bulk_size;
483: l_max_fetches := p_terr_globals.cursor_limit;
484: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
485: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
486: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Account Profiles started ... ');
487: FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating Customer Account Profiles started ... ');
488:

Line 485: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);

481: -- Bulk Read the Territory Assignments
482: l_var :=p_terr_globals.bulk_size;
483: l_max_fetches := p_terr_globals.cursor_limit;
484: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
485: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
486: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Account Profiles started ... ');
487: FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating Customer Account Profiles started ... ');
488:
489: OPEN c_UpdateProfile(l_worker_id);

Line 486: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Account Profiles started ... ');

482: l_var :=p_terr_globals.bulk_size;
483: l_max_fetches := p_terr_globals.cursor_limit;
484: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
485: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
486: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Account Profiles started ... ');
487: FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating Customer Account Profiles started ... ');
488:
489: OPEN c_UpdateProfile(l_worker_id);
490: LOOP

Line 496: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');

492: FETCH c_UpdateProfile BULK COLLECT INTO
493: l_customer_id, l_salesforce_id, l_sales_group_id, l_org_id, l_Res_type, l_Collector_ID
494: LIMIT l_max_fetches;
495: IF l_customer_id.count = 0 THEN
496: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
497: FND_FILE.PUT_LINE(FND_FILE.LOG,'Update Completed. Exiting the update loop');
498: CLOSE C_UPDATEPROFILE;
499: EXIT;
500: ELSE

Line 501: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);

497: FND_FILE.PUT_LINE(FND_FILE.LOG,'Update Completed. Exiting the update loop');
498: CLOSE C_UPDATEPROFILE;
499: EXIT;
500: ELSE
501: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);
502: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Rows Fetched ' || l_customer_id.count);
503: FORALL i in l_customer_id.first..l_customer_id.last
504: UPDATE HZ_CUSTOMER_PROFILES ACC
505: SET object_version_number = nvl(object_version_number,0) + 1,

Line 519: IEX_TERR_WINNERS_PUB.Print_Debug('Total Customer Account Profile Rows Updated ' || l_customer_id.count);

515: AND ACC.SITE_USE_ID IS NULL
516: AND ACC.CUST_ACCOUNT_ID <> -1
517: AND ACC.COLLECTOR_ID <> l_collector_id(i);
518: --Commit When the Bulk commit size is reached.
519: IEX_TERR_WINNERS_PUB.Print_Debug('Total Customer Account Profile Rows Updated ' || l_customer_id.count);
520: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Account Pofile Rows Updated ' || l_customer_id.count);
521: COMMIT;
522: END IF;
523: EXCEPTION WHEN deadlock_detected THEN

Line 525: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');

521: COMMIT;
522: END IF;
523: EXCEPTION WHEN deadlock_detected THEN
524: BEGIN
525: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
526: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deadlock encountered during bulk update.. Performing row update..');
527: ROLLBACK;
528: FOR i in l_first .. l_last LOOP
529: BEGIN

Line 546: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');

542: AND ACC.SITE_USE_ID IS NULL
543: AND ACC.CUST_ACCOUNT_ID <> -1
544: AND ACC.COLLECTOR_ID <> l_collector_id(i);
545: EXCEPTION WHEN OTHERS THEN
546: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
547: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
548: ' SQLERRM: ' || SQLERRM);
549: END;
550: END LOOP;

Line 547: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

543: AND ACC.CUST_ACCOUNT_ID <> -1
544: AND ACC.COLLECTOR_ID <> l_collector_id(i);
545: EXCEPTION WHEN OTHERS THEN
546: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
547: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
548: ' SQLERRM: ' || SQLERRM);
549: END;
550: END LOOP;
551: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Account Profile Rows Updated ' ||l_cnt);

Line 555: IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);

551: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Account Profile Rows Updated ' ||l_cnt);
552: l_cnt := 0;
553: END;
554: WHEN OTHERS THEN
555: IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);
556: END;
557: END LOOP;
558: IF C_UPDATEPROFILE%ISOPEN THEN
559: CLOSE C_UPDATEPROFILE;

Line 562: IEX_TERR_WINNERS_PUB.Print_Debug('* Completed Account Level Collector Assignment *');

558: IF C_UPDATEPROFILE%ISOPEN THEN
559: CLOSE C_UPDATEPROFILE;
560: END IF;
561:
562: IEX_TERR_WINNERS_PUB.Print_Debug('*** Completed Account Level Collector Assignment ***');
563: l_loop_count := 0;
564:
565: l_customer_id.delete;
566: l_terr_id.delete;

Line 584: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');

580: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
581: RAISE;
582:
583: WHEN others THEN
584: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
585: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
586: ' SQLERRM: ' || SQLERRM);
587: x_errbuf := SQLERRM;
588: x_retcode := SQLCODE;

Line 585: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

581: RAISE;
582:
583: WHEN others THEN
584: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
585: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
586: ' SQLERRM: ' || SQLERRM);
587: x_errbuf := SQLERRM;
588: x_retcode := SQLCODE;
589: RAISE;

Line 597: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)

593:
594: PROCEDURE AssignSiteUseCollectors(
595: x_errbuf OUT NOCOPY VARCHAR2,
596: x_retcode OUT NOCOPY VARCHAR2,
597: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
598: IS
599: /*CURSOR c_UpdateProfile(c_worker_id number) IS
600: SELECT WIN.TRANS_OBJECT_id,
601: WIN.TRANS_DETAIL_OBJECT_ID,

Line 737: IEX_TERR_WINNERS_PUB.Print_Debug('* Started Site Level Collector Assignment *');

733:
734: BEGIN
735: select to_char( sysdate, 'DD-Mon-YYYY HH24:MI:SS') into l_date_str from dual;
736: FND_FILE.PUT_LINE(FND_FILE.LOG,'---' || l_date_str || '--------------------------');
737: IEX_TERR_WINNERS_PUB.Print_Debug('*** Started Site Level Collector Assignment ***');
738: FND_FILE.PUT_LINE(FND_FILE.LOG,'*** Started Site Level Collector Assignment ***');
739:
740: -- l_Assignlevel := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
741: l_worker_id :=p_terr_globals.worker_id;

Line 747: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size =' ||l_var);

743: l_max_fetches := p_terr_globals.cursor_limit;
744:
745:
746: FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level when assigning collectors at Bill To Level := ' ||l_Assignlevel);
747: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size =' ||l_var);
748: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size =' ||l_max_fetches);
749:
750: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
751: if (x_retcode = 'E') then

Line 748: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size =' ||l_max_fetches);

744:
745:
746: FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level when assigning collectors at Bill To Level := ' ||l_Assignlevel);
747: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size =' ||l_var);
748: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size =' ||l_max_fetches);
749:
750: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
751: if (x_retcode = 'E') then
752: IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');

Line 752: IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');

748: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size =' ||l_max_fetches);
749:
750: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
751: if (x_retcode = 'E') then
752: IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');
753: IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');
754: return;
755: end if;
756: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Site Profiles started ... ');

Line 753: IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');

749:
750: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
751: if (x_retcode = 'E') then
752: IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');
753: IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');
754: return;
755: end if;
756: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Site Profiles started ... ');
757: FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating Customer Site Profiles started ... ');

Line 756: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Site Profiles started ... ');

752: IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');
753: IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');
754: return;
755: end if;
756: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Customer Site Profiles started ... ');
757: FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating Customer Site Profiles started ... ');
758: --Bug4613487. Fix by lkkumar on 29-Sep-2005. Start.
759: OPEN c_UpdateProfile(l_worker_id);
760: LOOP

Line 767: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');

763: l_customer_id, l_partysite_id, l_salesforce_id, l_sales_group_id,
764: l_org_id, l_Res_type, l_Collector_ID,l_cust_account_id, l_siteuse_id
765: LIMIT l_max_fetches;
766: IF l_customer_id.count = 0 THEN
767: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
768: FND_FILE.PUT_LINE(FND_FILE.LOG,'Update Completed. Exiting the update loop');
769: CLOSE C_UPDATEPROFILE;
770: EXIT;
771: ELSE

Line 772: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);

768: FND_FILE.PUT_LINE(FND_FILE.LOG,'Update Completed. Exiting the update loop');
769: CLOSE C_UPDATEPROFILE;
770: EXIT;
771: ELSE
772: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);
773: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Rows Fetched ' || l_customer_id.count);
774:
775: FORALL i in l_customer_id.first..l_customer_id.last
776: UPDATE HZ_CUSTOMER_PROFILES ACC

Line 791: IEX_TERR_WINNERS_PUB.Print_Debug('Total Customer Site Profile Rows Updated ' || l_customer_id.count);

787: AND ACC.CUST_ACCOUNT_ID = l_cust_account_id(i)
788: AND ACC.SITE_USE_ID = l_siteuse_id(i)
789: AND ACC.COLLECTOR_ID <> l_collector_id(i);
790: --Commit When the Bulk commit size is reached.
791: IEX_TERR_WINNERS_PUB.Print_Debug('Total Customer Site Profile Rows Updated ' || l_customer_id.count);
792: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Site Profile Rows Updated ' || l_customer_id.count);
793: COMMIT;
794: END IF;
795: EXCEPTION WHEN deadlock_detected THEN

Line 797: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');

793: COMMIT;
794: END IF;
795: EXCEPTION WHEN deadlock_detected THEN
796: BEGIN
797: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
798: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deadlock encountered during bulk update.. Performing row update..');
799: ROLLBACK;
800: FOR i in l_first .. l_last LOOP
801: BEGIN

Line 819: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');

815: AND ACC.SITE_USE_ID = l_siteuse_id(i)
816: AND ACC.COLLECTOR_ID <> l_collector_id(i);
817: EXCEPTION
818: WHEN OTHERS THEN
819: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
820: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
821: ' SQLERRM: ' || SQLERRM);
822: END;
823: END LOOP;

Line 820: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

816: AND ACC.COLLECTOR_ID <> l_collector_id(i);
817: EXCEPTION
818: WHEN OTHERS THEN
819: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
820: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
821: ' SQLERRM: ' || SQLERRM);
822: END;
823: END LOOP;
824: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Site Profile Rows Updated ' ||l_cnt);

Line 828: IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);

824: FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Customer Site Profile Rows Updated ' ||l_cnt);
825: l_cnt := 0;
826: END;
827: WHEN OTHERS THEN
828: IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);
829: END;
830: END LOOP;
831: IF C_UPDATEPROFILE%ISOPEN THEN
832: CLOSE C_UPDATEPROFILE;

Line 835: IEX_TERR_WINNERS_PUB.Print_Debug('* Completed Site Level Collector Assignment *');

831: IF C_UPDATEPROFILE%ISOPEN THEN
832: CLOSE C_UPDATEPROFILE;
833: END IF;
834: --Bug4613487. Fix by lkkumar on 29-Sep-2005. End.
835: IEX_TERR_WINNERS_PUB.Print_Debug('*** Completed Site Level Collector Assignment ***');
836:
837: l_limit_flag := FALSE;
838: l_loop_count := 0;
839:

Line 859: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::AssignSiteUseCollectors');

855: x_retcode := FND_API.G_RET_STS_UNEXP_ERROR;
856: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
857: RAISE;
858: WHEN others THEN
859: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::AssignSiteUseCollectors');
860: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
861: ' SQLERRM: ' || SQLERRM);
862: x_errbuf := SQLERRM;
863: x_retcode := SQLCODE;

Line 860: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

856: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
857: RAISE;
858: WHEN others THEN
859: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::AssignSiteUseCollectors');
860: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
861: ' SQLERRM: ' || SQLERRM);
862: x_errbuf := SQLERRM;
863: x_retcode := SQLCODE;
864: RAISE;

Line 871: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS

867: PROCEDURE CheckCollectors(
868: x_errbuf OUT NOCOPY VARCHAR2,
869: x_retcode OUT NOCOPY VARCHAR2,
870: p_worker_id IN NUMBER,
871: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS
872:
873: l_missSF_id salesforce_id_list; -- Missed Sales Force ID
874: l_missSG_id sales_group_id_list; -- Missed Sales Group ID
875: l_missPer_ID person_id_list; -- Missed Person ID

Line 920: IEX_TERR_WINNERS_PUB.Print_Debug('*** Check Resources not in AR Collectors. LOOPING Count -> :'||l_loop_count);

916: if (l_limit_flag) then
917: EXIT;
918: End If;
919: l_loop_count := l_loop_count + 1;
920: IEX_TERR_WINNERS_PUB.Print_Debug('*** Check Resources not in AR Collectors. LOOPING Count -> :'||l_loop_count);
921:
922: --------------------------------
923: l_attempts := 1;
924: l_exceptions := FALSE;

Line 927: IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);

923: l_attempts := 1;
924: l_exceptions := FALSE;
925: WHILE l_attempts < 3 LOOP -- Bulk read Collectors. attempts < 3
926: BEGIN
927: IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);
928: OPEN c_MissedCollectors(p_worker_id);
929: FETCH c_MissedCollectors BULK COLLECT INTO
930: l_missSF_id, l_missSG_id, l_missOrg_ID, l_MissResType, l_missPer_ID
931: LIMIT l_max_fetches;

Line 937: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);

933: l_attempts := 3;
934: l_exceptions := FALSE;
935: EXCEPTION
936: WHEN Others THEN
937: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
938: l_attempts := l_attempts +1;
939: l_exceptions := TRUE;
940: if c_MissedCollectors%ISOPEN then
941: CLOSE c_MissedCollectors;

Line 950: IEX_TERR_WINNERS_PUB.Print_Debug('--- Read Missed Collectors End-Attempts: '||l_attempts);

946: RAISE;
947: end if;
948: END;
949: END LOOP; -- End Bulk read Sales Force ID. attempts < 3
950: IEX_TERR_WINNERS_PUB.Print_Debug('--- Read Missed Collectors End-Attempts: '||l_attempts);
951:
952: -- Initialize variables
953: if l_missSF_id.count < l_max_fetches then
954: l_limit_flag := TRUE;

Line 957: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start INSERT OF AR_COLLECTORS = . ' || l_missSF_id.count);

953: if l_missSF_id.count < l_max_fetches then
954: l_limit_flag := TRUE;
955: end if;
956:
957: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start INSERT OF AR_COLLECTORS = . ' || l_missSF_id.count);
958:
959: IF l_missSF_id.count > 0 THEN -- if l_SalesForce_id.count > 0
960:
961: l_attempts := 1;

Line 962: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');

958:
959: IF l_missSF_id.count > 0 THEN -- if l_SalesForce_id.count > 0
960:
961: l_attempts := 1;
962: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');
963:
964: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
965: BEGIN
966:

Line 967: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);

963:
964: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
965: BEGIN
966:
967: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
968:
969: FOR i in 1 .. l_missSF_id.count LOOP
970: BEGIN
971: IF (l_missResType(i) = 'RS_GROUP') THEN

Line 982: IEX_TERR_WINNERS_PUB.Print_Debug('After selecting Resource_name = '|| l_resource_name );

978: INTO l_resource_name, l_source_id
979: FROM jtf_rs_resource_extns_vl
980: WHERE resource_id = l_missSF_id(i);
981: END IF;
982: IEX_TERR_WINNERS_PUB.Print_Debug('After selecting Resource_name = '|| l_resource_name );
983:
984: INSERT INTO AR_COLLECTORS
985: (COLLECTOR_ID ,
986: LAST_UPDATED_BY ,

Line 1020: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);

1016: END;
1017: COMMIT;
1018: END LOOP;
1019: l_attempts := 3;
1020: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1021: EXCEPTION
1022: WHEN deadlock_detected THEN
1023: begin
1024: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);

Line 1024: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);

1020: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1021: EXCEPTION
1022: WHEN deadlock_detected THEN
1023: begin
1024: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
1025: rollback;
1026: l_attempts := l_attempts +1;
1027: if l_attempts = 3 then
1028: FOR i in 1 .. l_missSF_id.count LOOP /*Inside deadlock detected loop */

Line 1073: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');

1069: FND_FILE.PUT_LINE(FND_FILE.LOG, ' After Inserting in to the AR_COLLECTORS');
1070:
1071: EXCEPTION
1072: WHEN OTHERS THEN
1073: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
1074: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1075: ' SQLERRM: ' || SQLERRM);
1076: END;
1077: END LOOP; /* End Inside deadlock detected loop */

Line 1074: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

1070:
1071: EXCEPTION
1072: WHEN OTHERS THEN
1073: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
1074: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1075: ' SQLERRM: ' || SQLERRM);
1076: END;
1077: END LOOP; /* End Inside deadlock detected loop */
1078: COMMIT;

Line 1083: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');

1079: end if;
1080: end; -- end of deadlock exception
1081:
1082: WHEN OTHERS THEN
1083: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1084: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1085: ' SQLERRM: ' || SQLERRM);
1086: x_errbuf := SQLERRM;
1087: x_retcode := SQLCODE;

Line 1084: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

1080: end; -- end of deadlock exception
1081:
1082: WHEN OTHERS THEN
1083: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1084: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1085: ' SQLERRM: ' || SQLERRM);
1086: x_errbuf := SQLERRM;
1087: x_retcode := SQLCODE;
1088: RAISE;

Line 1093: IEX_TERR_WINNERS_PUB.Print_Debug('---Check Collectors Account.End-'|| l_missSF_id.count||' Rows Updated.');

1089: END;
1090: END LOOP; /* Update While loop; l_attempts < 3 */
1091:
1092: END IF; --l_salesforce.count > 0
1093: IEX_TERR_WINNERS_PUB.Print_Debug('---Check Collectors Account.End-'|| l_missSF_id.count||' Rows Updated.');
1094: --------------------------------
1095: END LOOP; -- End Bulk read non-existent Collector ID. attempts < 3
1096:
1097: EXCEPTION

Line 1099: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CheckCollectors');

1095: END LOOP; -- End Bulk read non-existent Collector ID. attempts < 3
1096:
1097: EXCEPTION
1098: WHEN others THEN
1099: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CheckCollectors');
1100: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1101: ' SQLERRM: ' || SQLERRM);
1102: x_errbuf := SQLERRM;
1103: x_retcode := SQLCODE;

Line 1100: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

1096:
1097: EXCEPTION
1098: WHEN others THEN
1099: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CheckCollectors');
1100: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1101: ' SQLERRM: ' || SQLERRM);
1102: x_errbuf := SQLERRM;
1103: x_retcode := SQLCODE;
1104: RAISE;

Line 1112: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS

1108: PROCEDURE CreatePartyProfiles(
1109: x_errbuf OUT NOCOPY VARCHAR2,
1110: x_retcode OUT NOCOPY VARCHAR2,
1111: p_worker_id IN NUMBER,
1112: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS
1113:
1114: l_missCustomer customer_id_list; -- Missed Customer Profiles
1115:
1116: l_max_fetches NUMBER;

Line 1173: IEX_TERR_WINNERS_PUB.Print_Debug('*** Getting Parties with no profiles. LOOPING Count -> :'||l_loop_count);

1169: if (l_limit_flag) then
1170: EXIT;
1171: End If;
1172: l_loop_count := l_loop_count + 1;
1173: IEX_TERR_WINNERS_PUB.Print_Debug('*** Getting Parties with no profiles. LOOPING Count -> :'||l_loop_count);
1174: --------------------------------
1175: l_attempts := 1;
1176: l_exceptions := FALSE;
1177: WHILE l_attempts < 3 LOOP -- Bulk read Party list. attempts < 3

Line 1179: IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);

1175: l_attempts := 1;
1176: l_exceptions := FALSE;
1177: WHILE l_attempts < 3 LOOP -- Bulk read Party list. attempts < 3
1178: BEGIN
1179: IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);
1180: OPEN c_MissedProfiles(p_worker_id);
1181: FETCH c_MissedProfiles BULK COLLECT INTO l_missCustomer
1182: LIMIT l_max_fetches;
1183: CLOSE c_MissedProfiles;

Line 1188: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);

1184: l_attempts := 3;
1185: l_exceptions := FALSE;
1186: EXCEPTION
1187: WHEN Others THEN
1188: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
1189: l_attempts := l_attempts +1;
1190: l_exceptions := TRUE;
1191: if c_MissedProfiles%ISOPEN then
1192: CLOSE c_MissedProfiles;

Line 1201: IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles. End -Attempts: '||l_attempts);

1197: RAISE;
1198: end if;
1199: END;
1200: END LOOP; -- End Bulk read Party list. attempts < 3
1201: IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles. End -Attempts: '||l_attempts);
1202:
1203: -- Initialize variables
1204: if l_missCustomer.count < l_max_fetches then
1205: l_limit_flag := TRUE;

Line 1208: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start. Creating customer Profiles = . ' || l_missCustomer.count);

1204: if l_missCustomer.count < l_max_fetches then
1205: l_limit_flag := TRUE;
1206: end if;
1207:
1208: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start. Creating customer Profiles = . ' || l_missCustomer.count);
1209: FND_FILE.PUT_LINE(FND_FILE.LOG,'--- Start. Creating customer Profiles = . ' || l_missCustomer.count);
1210:
1211: IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1212:

Line 1213: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');

1209: FND_FILE.PUT_LINE(FND_FILE.LOG,'--- Start. Creating customer Profiles = . ' || l_missCustomer.count);
1210:
1211: IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1212:
1213: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');
1214: l_attempts := 1;
1215:
1216: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1217: BEGIN

Line 1219: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);

1215:
1216: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1217: BEGIN
1218:
1219: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
1220:
1221: FOR i in 1 .. l_missCustomer.count LOOP
1222: --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. Start.
1223: BEGIN

Line 1232: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );

1228: AND hcp.PARTY_ID = l_missCustomer(i)
1229: AND hcp.site_use_id is null;
1230: EXCEPTION WHEN NO_DATA_FOUND THEN
1231: BEGIN
1232: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1233: l_old_customer_profile_rec.party_id := l_missCustomer(i);
1234: l_old_customer_profile_rec.created_by_module := 'IEX';
1235: l_old_customer_profile_rec.site_use_id := NULL;
1236: l_old_customer_profile_rec.cust_account_id := NULL;

Line 1245: IEX_TERR_WINNERS_PUB.Print_Debug('Return data after create profile API ' || l_return_status || l_msg_count);

1241: x_return_status => l_return_status,
1242: x_msg_count => l_msg_count,
1243: x_msg_data => l_msg_data);
1244:
1245: IEX_TERR_WINNERS_PUB.Print_Debug('Return data after create profile API ' || l_return_status || l_msg_count);
1246: IEX_TERR_WINNERS_PUB.Print_Debug('Created Profile Id ' || l_customer_profile_id);
1247:
1248: EXCEPTION
1249: WHEN OTHERS THEN

Line 1246: IEX_TERR_WINNERS_PUB.Print_Debug('Created Profile Id ' || l_customer_profile_id);

1242: x_msg_count => l_msg_count,
1243: x_msg_data => l_msg_data);
1244:
1245: IEX_TERR_WINNERS_PUB.Print_Debug('Return data after create profile API ' || l_return_status || l_msg_count);
1246: IEX_TERR_WINNERS_PUB.Print_Debug('Created Profile Id ' || l_customer_profile_id);
1247:
1248: EXCEPTION
1249: WHEN OTHERS THEN
1250: FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );

Line 1257: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);

1253: --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. END.
1254: END LOOP;
1255: COMMIT;
1256: l_attempts := 3;
1257: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1258: FND_FILE.PUT_LINE(FND_FILE.LOG,'Records Updated: ' || l_first || '-'|| l_last);
1259: EXCEPTION
1260: WHEN deadlock_detected THEN
1261: begin

Line 1262: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);

1258: FND_FILE.PUT_LINE(FND_FILE.LOG,'Records Updated: ' || l_first || '-'|| l_last);
1259: EXCEPTION
1260: WHEN deadlock_detected THEN
1261: begin
1262: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
1263: rollback;
1264: l_attempts := l_attempts +1;
1265: if l_attempts = 3 then
1266: FOR i in 1 .. l_missCustomer.count LOOP /*Inside deadlock detected loop */

Line 1277: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );

1273: AND hcp.PARTY_ID = l_missCustomer(i)
1274: AND hcp.site_use_id is null;
1275: EXCEPTION WHEN NO_DATA_FOUND THEN
1276: BEGIN
1277: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1278: l_old_customer_profile_rec.party_id := l_missCustomer(i);
1279: l_old_customer_profile_rec.created_by_module := 'IEX';
1280:
1281: HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (

Line 1288: IEX_TERR_WINNERS_PUB.Print_Debug('Return data after create profile API ' || l_return_status || l_msg_count);

1284: x_return_status => l_return_status,
1285: x_msg_count => l_msg_count,
1286: x_msg_data => l_msg_data);
1287:
1288: IEX_TERR_WINNERS_PUB.Print_Debug('Return data after create profile API ' || l_return_status || l_msg_count);
1289: IEX_TERR_WINNERS_PUB.Print_Debug('Created Profile Id ' || l_customer_profile_id);
1290:
1291: EXCEPTION
1292: WHEN OTHERS THEN

Line 1289: IEX_TERR_WINNERS_PUB.Print_Debug('Created Profile Id ' || l_customer_profile_id);

1285: x_msg_count => l_msg_count,
1286: x_msg_data => l_msg_data);
1287:
1288: IEX_TERR_WINNERS_PUB.Print_Debug('Return data after create profile API ' || l_return_status || l_msg_count);
1289: IEX_TERR_WINNERS_PUB.Print_Debug('Created Profile Id ' || l_customer_profile_id);
1290:
1291: EXCEPTION
1292: WHEN OTHERS THEN
1293: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');

Line 1293: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');

1289: IEX_TERR_WINNERS_PUB.Print_Debug('Created Profile Id ' || l_customer_profile_id);
1290:
1291: EXCEPTION
1292: WHEN OTHERS THEN
1293: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1294: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1295: ' SQLERRM: ' || SQLERRM);
1296: END;
1297: END;

Line 1294: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

1290:
1291: EXCEPTION
1292: WHEN OTHERS THEN
1293: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1294: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1295: ' SQLERRM: ' || SQLERRM);
1296: END;
1297: END;
1298: --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. End.

Line 1306: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');

1302: end if;
1303: end; -- end of deadlock exception
1304:
1305: WHEN OTHERS THEN
1306: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1307: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1308: ' SQLERRM: ' || SQLERRM);
1309: x_errbuf := SQLERRM;
1310: x_retcode := SQLCODE;

Line 1307: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

1303: end; -- end of deadlock exception
1304:
1305: WHEN OTHERS THEN
1306: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1307: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1308: ' SQLERRM: ' || SQLERRM);
1309: x_errbuf := SQLERRM;
1310: x_retcode := SQLCODE;
1311: RAISE;

Line 1316: IEX_TERR_WINNERS_PUB.Print_Debug('---Create Customer Profile.End-'|| l_missCustomer.count||' Rows Updated.');

1312: END;
1313: END LOOP; /* Update While loop; l_attempts < 3 */
1314:
1315: END IF; --l_missCustomer .count > 0
1316: IEX_TERR_WINNERS_PUB.Print_Debug('---Create Customer Profile.End-'|| l_missCustomer.count||' Rows Updated.');
1317: --------------------------------
1318: END LOOP; -- End Bulk read non-existent Customer Profiles attempts < 3
1319:
1320: EXCEPTION

Line 1322: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');

1318: END LOOP; -- End Bulk read non-existent Customer Profiles attempts < 3
1319:
1320: EXCEPTION
1321: WHEN others THEN
1322: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1323: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1324: ' SQLERRM: ' || SQLERRM);
1325: x_errbuf := SQLERRM;
1326: x_retcode := SQLCODE;

Line 1323: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

1319:
1320: EXCEPTION
1321: WHEN others THEN
1322: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1323: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1324: ' SQLERRM: ' || SQLERRM);
1325: x_errbuf := SQLERRM;
1326: x_retcode := SQLCODE;
1327: RAISE;

Line 1335: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS

1331: PROCEDURE CreateSiteUseProfiles(
1332: x_errbuf OUT NOCOPY VARCHAR2,
1333: x_retcode OUT NOCOPY VARCHAR2,
1334: p_worker_id IN NUMBER,
1335: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS
1336:
1337: l_missCustomer customer_id_list; -- Missed Customer Profiles
1338: l_missSiteUse site_use_id_list;
1339: l_missAccount cust_account_id_list;

Line 1401: IEX_TERR_WINNERS_PUB.Print_Debug('*** Getting Parties with no profiles. LOOPING Count -> :'||l_loop_count);

1397: if (l_limit_flag) then
1398: EXIT;
1399: End If;
1400: l_loop_count := l_loop_count + 1;
1401: IEX_TERR_WINNERS_PUB.Print_Debug('*** Getting Parties with no profiles. LOOPING Count -> :'||l_loop_count);
1402:
1403: --------------------------------
1404: l_attempts := 1;
1405: l_exceptions := FALSE;

Line 1408: IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);

1404: l_attempts := 1;
1405: l_exceptions := FALSE;
1406: WHILE l_attempts < 3 LOOP -- Bulk read Party list. attempts < 3
1407: BEGIN
1408: IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);
1409: OPEN c_MissedProfiles(p_worker_id);
1410: FETCH c_MissedProfiles BULK COLLECT INTO l_missCustomer,
1411: l_missSiteUse, l_missAccount LIMIT l_max_fetches;
1412: CLOSE c_MissedProfiles;

Line 1417: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);

1413: l_attempts := 3;
1414: l_exceptions := FALSE;
1415: EXCEPTION
1416: WHEN Others THEN
1417: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
1418: l_attempts := l_attempts +1;
1419: l_exceptions := TRUE;
1420: if c_MissedProfiles%ISOPEN then
1421: CLOSE c_MissedProfiles;

Line 1430: IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles.End-Attempts: '||l_attempts);

1426: RAISE;
1427: end if;
1428: END;
1429: END LOOP; -- End Bulk read Party list. attempts < 3
1430: IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles.End-Attempts: '||l_attempts);
1431:
1432: -- Initialize variables
1433: if l_missCustomer.count < l_max_fetches then
1434: l_limit_flag := TRUE;

Line 1437: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start. Create = . ' || l_missCustomer.count);

1433: if l_missCustomer.count < l_max_fetches then
1434: l_limit_flag := TRUE;
1435: end if;
1436:
1437: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start. Create = . ' || l_missCustomer.count);
1438:
1439: IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1440:
1441: l_attempts := 1;

Line 1442: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');

1438:
1439: IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1440:
1441: l_attempts := 1;
1442: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');
1443:
1444: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1445: BEGIN
1446:

Line 1447: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);

1443:
1444: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1445: BEGIN
1446:
1447: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
1448:
1449: FOR i in 1 .. l_missCustomer.count LOOP
1450: BEGIN
1451: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );

Line 1451: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );

1447: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
1448:
1449: FOR i in 1 .. l_missCustomer.count LOOP
1450: BEGIN
1451: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1452:
1453: l_old_customer_profile_rec.party_id := l_missCustomer(i);
1454: l_old_customer_profile_rec.cust_account_id := l_missAccount(i);
1455: l_old_customer_profile_rec.site_use_id := l_missSiteUse(i);

Line 1465: IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || ' msg_count ' || l_msg_count || ' msg_data ' || l_msg_data);

1461: x_return_status => l_return_status,
1462: x_msg_count => l_msg_count,
1463: x_msg_data => l_msg_data);
1464:
1465: IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || ' msg_count ' || l_msg_count || ' msg_data ' || l_msg_data);
1466: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1467:
1468: EXCEPTION
1469: WHEN OTHERS THEN

Line 1466: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);

1462: x_msg_count => l_msg_count,
1463: x_msg_data => l_msg_data);
1464:
1465: IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || ' msg_count ' || l_msg_count || ' msg_data ' || l_msg_data);
1466: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1467:
1468: EXCEPTION
1469: WHEN OTHERS THEN
1470: FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );

Line 1475: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);

1471: END;
1472: COMMIT;
1473: END LOOP;
1474: l_attempts := 3;
1475: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1476: EXCEPTION
1477: WHEN deadlock_detected THEN
1478: begin
1479: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);

Line 1479: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);

1475: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1476: EXCEPTION
1477: WHEN deadlock_detected THEN
1478: begin
1479: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
1480: rollback;
1481: l_attempts := l_attempts +1;
1482: if l_attempts = 3 then
1483: FOR i in 1 .. l_missCustomer.count LOOP /*Inside deadlock detected loop */

Line 1486: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );

1482: if l_attempts = 3 then
1483: FOR i in 1 .. l_missCustomer.count LOOP /*Inside deadlock detected loop */
1484: BEGIN
1485:
1486: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1487:
1488: l_old_customer_profile_rec.party_id := l_missCustomer(i);
1489: l_old_customer_profile_rec.site_use_id := l_missSiteUse(i);
1490: l_old_customer_profile_rec.created_by_module := 'IEX';

Line 1500: IEX_TERR_WINNERS_PUB.Print_Debug(' Return after create profile API ' || l_return_status ||

1496: x_return_status => l_return_status,
1497: x_msg_count => l_msg_count,
1498: x_msg_data => l_msg_data);
1499:
1500: IEX_TERR_WINNERS_PUB.Print_Debug(' Return after create profile API ' || l_return_status ||
1501: ' msg_count ' || l_msg_count || ' msg_data ' || l_msg_data);
1502: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1503:
1504: EXCEPTION

Line 1502: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);

1498: x_msg_data => l_msg_data);
1499:
1500: IEX_TERR_WINNERS_PUB.Print_Debug(' Return after create profile API ' || l_return_status ||
1501: ' msg_count ' || l_msg_count || ' msg_data ' || l_msg_data);
1502: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1503:
1504: EXCEPTION
1505: WHEN OTHERS THEN
1506: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');

Line 1506: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');

1502: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1503:
1504: EXCEPTION
1505: WHEN OTHERS THEN
1506: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1507: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1508: ' SQLERRM: ' || SQLERRM);
1509: END;
1510: END LOOP; /* End Inside deadlock detected loop */

Line 1507: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

1503:
1504: EXCEPTION
1505: WHEN OTHERS THEN
1506: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1507: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1508: ' SQLERRM: ' || SQLERRM);
1509: END;
1510: END LOOP; /* End Inside deadlock detected loop */
1511: COMMIT;

Line 1516: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');

1512: end if;
1513: end; -- end of deadlock exception
1514:
1515: WHEN OTHERS THEN
1516: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1517: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1518: ' SQLERRM: ' || SQLERRM);
1519: x_errbuf := SQLERRM;
1520: x_retcode := SQLCODE;

Line 1517: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||

1513: end; -- end of deadlock exception
1514:
1515: WHEN OTHERS THEN
1516: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1517: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1518: ' SQLERRM: ' || SQLERRM);
1519: x_errbuf := SQLERRM;
1520: x_retcode := SQLCODE;
1521: RAISE;

Line 1526: IEX_TERR_WINNERS_PUB.Print_Debug('---Create SiteUse Profile.End-'|| l_missCustomer.count||' Rows Updated.');

1522: END;
1523: END LOOP; /* Update While loop; l_attempts < 3 */
1524:
1525: END IF; --l_missCustomer .count > 0
1526: IEX_TERR_WINNERS_PUB.Print_Debug('---Create SiteUse Profile.End-'|| l_missCustomer.count||' Rows Updated.');
1527: FND_FILE.PUT_LINE(FND_FILE.LOG,'---Create SiteUse Profile.End-'|| l_missCustomer.count||' Rows Updated.');
1528: --------------------------------
1529: END LOOP; -- End Bulk read non-existent Customer Profiles attempts < 3
1530:

Line 1534: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');

1530:
1531:
1532: EXCEPTION
1533: WHEN others THEN
1534: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1535: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
1536: x_errbuf := SQLERRM;
1537: x_retcode := SQLCODE;
1538: RAISE;

Line 1535: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);

1531:
1532: EXCEPTION
1533: WHEN others THEN
1534: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1535: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
1536: x_errbuf := SQLERRM;
1537: x_retcode := SQLCODE;
1538: RAISE;
1539: END CreateSiteUseProfiles;