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 52: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

48: PROCEDURE CheckCollectors(
49: x_errbuf OUT NOCOPY VARCHAR2,
50: x_retcode OUT NOCOPY VARCHAR2,
51: p_worker_id IN NUMBER,
52: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
53:
54:
55: PROCEDURE AssignSiteUseCollectors(
56: x_errbuf OUT NOCOPY VARCHAR2,

Line 58: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

54:
55: PROCEDURE AssignSiteUseCollectors(
56: x_errbuf OUT NOCOPY VARCHAR2,
57: x_retcode OUT NOCOPY VARCHAR2,
58: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
59:
60: PROCEDURE AssignPartyCollectors(
61: x_errbuf OUT NOCOPY VARCHAR2,
62: x_retcode OUT NOCOPY VARCHAR2,

Line 63: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

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

Line 68: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

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

Line 74: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

70: PROCEDURE CreatePartyProfiles(
71: x_errbuf OUT NOCOPY VARCHAR2,
72: x_retcode OUT NOCOPY VARCHAR2,
73: p_worker_id IN NUMBER,
74: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
75:
76: PROCEDURE CreateSiteUseProfiles(
77: x_errbuf OUT NOCOPY VARCHAR2,
78: x_retcode OUT NOCOPY VARCHAR2,

Line 80: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);

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

Line 86: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)

82:
83: PROCEDURE Process_Account_Records(
84: x_errbuf OUT NOCOPY VARCHAR2,
85: x_retcode OUT NOCOPY VARCHAR2,
86: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
87: IS
88:
89: l_limit_flag BOOLEAN := FALSE;
90: l_max_fetches NUMBER := 10000;

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

96: -------------------------------------------------------------------------------
97:
98:
99: BEGIN
100: IEX_TERR_WINNERS_PUB.Print_Debug('*** iextpawb.pls::IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records() ***');
101:
102: l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
103: FND_FILE.PUT_LINE(FND_FILE.LOG,'Process Accounts Program started');
104: FND_FILE.PUT_LINE(FND_FILE.LOG,'Operating Unit Set : ' || MO_GLOBAL.GET_CURRENT_ORG_ID);

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

105: FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level, IEX: Territory Access Level (IEX_ACCESS_LEVEL) := ' ||l_Assignlevel); -- changed by gnramasa on 29/08/2006 for bug # 5487449
106:
107: l_worker_id:=p_terr_globals.worker_id;
108: l_var :=p_terr_globals.bulk_size;
109: IEX_TERR_WINNERS_PUB.Print_Debug('bulk size='||l_var);
110:
111: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CheckCollectors');
112: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
113: if (x_retcode = 'E') then

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

107: l_worker_id:=p_terr_globals.worker_id;
108: l_var :=p_terr_globals.bulk_size;
109: IEX_TERR_WINNERS_PUB.Print_Debug('bulk size='||l_var);
110:
111: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CheckCollectors');
112: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
113: if (x_retcode = 'E') then
114: IEX_TERR_WINNERS_PUB.Print_Debug('CheckCollectors Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
115: return;

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

110:
111: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CheckCollectors');
112: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
113: if (x_retcode = 'E') then
114: IEX_TERR_WINNERS_PUB.Print_Debug('CheckCollectors Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
115: return;
116: end if;
117:
118: if (l_AssignLevel = 'PARTY') then

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

115: return;
116: end if;
117:
118: if (l_AssignLevel = 'PARTY') then
119: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreatePartyProfiles');
120: CreatePartyProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
121: if (x_retcode = 'E') then
122: IEX_TERR_WINNERS_PUB.Print_Debug('CreatePartyProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
123: return;

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

118: if (l_AssignLevel = 'PARTY') then
119: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreatePartyProfiles');
120: CreatePartyProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
121: if (x_retcode = 'E') then
122: IEX_TERR_WINNERS_PUB.Print_Debug('CreatePartyProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
123: return;
124: end if;
125: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignPartyCollectors');
126: AssignPartyCollectors(x_errbuf, x_retcode, p_terr_globals);

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

121: if (x_retcode = 'E') then
122: IEX_TERR_WINNERS_PUB.Print_Debug('CreatePartyProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
123: return;
124: end if;
125: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignPartyCollectors');
126: AssignPartyCollectors(x_errbuf, x_retcode, p_terr_globals);
127: elsif (l_AssignLevel = 'ACCOUNT') then
128: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
129: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignAccountCollectors');

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

125: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignPartyCollectors');
126: AssignPartyCollectors(x_errbuf, x_retcode, p_terr_globals);
127: elsif (l_AssignLevel = 'ACCOUNT') then
128: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
129: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignAccountCollectors');
130: AssignAccountCollectors(x_errbuf, x_retcode, p_terr_globals);
131: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. End.
132: else
133: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreateSiteUseProfiles');

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

129: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignAccountCollectors');
130: AssignAccountCollectors(x_errbuf, x_retcode, p_terr_globals);
131: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. End.
132: else
133: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreateSiteUseProfiles');
134: CreateSiteUseProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
135: if (x_retcode = 'E') then
136: IEX_TERR_WINNERS_PUB.Print_Debug('CreateSiteuseProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
137: return;

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

132: else
133: IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreateSiteUseProfiles');
134: CreateSiteUseProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
135: if (x_retcode = 'E') then
136: IEX_TERR_WINNERS_PUB.Print_Debug('CreateSiteuseProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
137: return;
138: end if;
139: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignSiteUseCollectors ');
140: AssignSiteUseCollectors(x_errbuf, x_retcode, p_terr_globals);

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

135: if (x_retcode = 'E') then
136: IEX_TERR_WINNERS_PUB.Print_Debug('CreateSiteuseProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
137: return;
138: end if;
139: IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignSiteUseCollectors ');
140: AssignSiteUseCollectors(x_errbuf, x_retcode, p_terr_globals);
141: end if;
142: if (x_retcode = 'E') then
143: return;

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

145:
146: EXCEPTION
147:
148: WHEN others THEN
149: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
150: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
151: ' SQLERRM: ' || SQLERRM);
152: x_errbuf := SQLERRM;
153: x_retcode := SQLCODE;

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

146: EXCEPTION
147:
148: WHEN others THEN
149: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
150: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
151: ' SQLERRM: ' || SQLERRM);
152: x_errbuf := SQLERRM;
153: x_retcode := SQLCODE;
154: RAISE;

Line 161: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)

157:
158: PROCEDURE AssignPartyCollectors(
159: x_errbuf OUT NOCOPY VARCHAR2,
160: x_retcode OUT NOCOPY VARCHAR2,
161: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
162: IS
163: -- Included 'Collections' Role Check
164: CURSOR c_UpdateProfile(c_worker_id number) IS
165: SELECT WIN.TRANS_OBJECT_id,

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

239: -------------------------------------------------------------------------------
240:
241:
242: BEGIN
243: IEX_TERR_WINNERS_PUB.Print_Debug('*** Started Party Level Collector Assignment ***');
244:
245: l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
246: FND_FILE.PUT_LINE(FND_FILE.LOG,'Territory Assignment Program started');
247: FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level (Picked from IEX_ACCESS_LEVEL) := ' ||l_Assignlevel);

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

249: l_worker_id:=p_terr_globals.worker_id;
250: -- Bulk Read the Territory Assignments
251: l_var :=p_terr_globals.bulk_size;
252: l_max_fetches := p_terr_globals.cursor_limit;
253: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
254: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
255: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
256: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
257: OPEN c_UpdateProfile(l_worker_id);

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

250: -- Bulk Read the Territory Assignments
251: l_var :=p_terr_globals.bulk_size;
252: l_max_fetches := p_terr_globals.cursor_limit;
253: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
254: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
255: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
256: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
257: OPEN c_UpdateProfile(l_worker_id);
258: LOOP

Line 255: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');

251: l_var :=p_terr_globals.bulk_size;
252: l_max_fetches := p_terr_globals.cursor_limit;
253: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
254: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
255: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
256: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
257: OPEN c_UpdateProfile(l_worker_id);
258: LOOP
259: BEGIN

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

260: FETCH c_UpdateProfile BULK COLLECT INTO
261: l_customer_id, l_salesforce_id, l_sales_group_id, l_org_id, l_Res_type, l_Collector_ID
262: LIMIT l_max_fetches;
263: IF l_customer_id.count = 0 THEN
264: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
265: CLOSE C_UPDATEPROFILE;
266: EXIT;
267: ELSE
268: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);

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

264: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
265: CLOSE C_UPDATEPROFILE;
266: EXIT;
267: ELSE
268: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);
269: FORALL i in l_customer_id.first..l_customer_id.last
270: UPDATE HZ_CUSTOMER_PROFILES ACC
271: SET object_version_number = nvl(object_version_number,0) + 1,
272: ACC.LAST_UPDATE_DATE = SYSDATE,

Line 285: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);

281: AND ACC.SITE_USE_ID IS NULL
282: AND ACC.CUST_ACCOUNT_ID = -1
283: AND ACC.COLLECTOR_ID <> l_collector_id(i);
284: --Commit When the Bulk commit size is reached.
285: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);
286: COMMIT;
287: END IF;
288: EXCEPTION WHEN deadlock_detected THEN
289: BEGIN

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

286: COMMIT;
287: END IF;
288: EXCEPTION WHEN deadlock_detected THEN
289: BEGIN
290: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during party bulk update.. Performing row update..');
291: ROLLBACK;
292: FOR i in l_first .. l_last LOOP
293: BEGIN
294: UPDATE HZ_CUSTOMER_PROFILES ACC

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

305: AND ACC.SITE_USE_ID IS NULL
306: AND ACC.CUST_ACCOUNT_ID = -1
307: AND ACC.COLLECTOR_ID <> l_collector_id(i); EXCEPTION
308: WHEN OTHERS THEN
309: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
310: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
311: ' SQLERRM: ' || SQLERRM);
312: END;
313: END LOOP;

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

306: AND ACC.CUST_ACCOUNT_ID = -1
307: AND ACC.COLLECTOR_ID <> l_collector_id(i); EXCEPTION
308: WHEN OTHERS THEN
309: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
310: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
311: ' SQLERRM: ' || SQLERRM);
312: END;
313: END LOOP;
314: END;

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

312: END;
313: END LOOP;
314: END;
315: WHEN OTHERS THEN
316: IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);
317: END;
318: END LOOP;
319: IF C_UPDATEPROFILE%ISOPEN THEN
320: CLOSE C_UPDATEPROFILE;

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

319: IF C_UPDATEPROFILE%ISOPEN THEN
320: CLOSE C_UPDATEPROFILE;
321: END IF;
322: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
323: IEX_TERR_WINNERS_PUB.Print_Debug('*** Finished Party Level Collector Assignment ***');
324:
325: l_customer_id.delete;
326: l_terr_id.delete;
327: l_customer_id := l_customer_id_empty;

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

339: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
340: RAISE;
341:
342: WHEN others THEN
343: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
344: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
345: ' SQLERRM: ' || SQLERRM);
346: x_errbuf := SQLERRM;
347: x_retcode := SQLCODE;

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

340: RAISE;
341:
342: WHEN others THEN
343: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
344: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
345: ' SQLERRM: ' || SQLERRM);
346: x_errbuf := SQLERRM;
347: x_retcode := SQLCODE;
348: RAISE;

Line 355: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)

351: --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
352: PROCEDURE AssignAccountCollectors(
353: x_errbuf OUT NOCOPY VARCHAR2,
354: x_retcode OUT NOCOPY VARCHAR2,
355: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
356: IS
357: -- Included 'Collections' Role Check
358: CURSOR c_UpdateProfile(c_worker_id number) IS
359: SELECT WIN.TRANS_OBJECT_id,

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

433: -------------------------------------------------------------------------------
434:
435:
436: BEGIN
437: IEX_TERR_WINNERS_PUB.Print_Debug('*** Started Account Level Collector Assignment ***');
438:
439: l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
440: FND_FILE.PUT_LINE(FND_FILE.LOG,'Territory Assignment Program started');
441: FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level (Picked from IEX_ACCESS_LEVEL) := ' ||l_Assignlevel);

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

443: l_worker_id:=p_terr_globals.worker_id;
444: -- Bulk Read the Territory Assignments
445: l_var :=p_terr_globals.bulk_size;
446: l_max_fetches := p_terr_globals.cursor_limit;
447: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
448: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
449: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
450:
451: OPEN c_UpdateProfile(l_worker_id);

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

444: -- Bulk Read the Territory Assignments
445: l_var :=p_terr_globals.bulk_size;
446: l_max_fetches := p_terr_globals.cursor_limit;
447: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
448: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
449: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
450:
451: OPEN c_UpdateProfile(l_worker_id);
452: LOOP

Line 449: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');

445: l_var :=p_terr_globals.bulk_size;
446: l_max_fetches := p_terr_globals.cursor_limit;
447: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
448: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
449: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
450:
451: OPEN c_UpdateProfile(l_worker_id);
452: LOOP
453: BEGIN

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

454: FETCH c_UpdateProfile BULK COLLECT INTO
455: l_customer_id, l_salesforce_id, l_sales_group_id, l_org_id, l_Res_type, l_Collector_ID
456: LIMIT l_max_fetches;
457: IF l_customer_id.count = 0 THEN
458: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
459: CLOSE C_UPDATEPROFILE;
460: EXIT;
461: ELSE
462: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);

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

458: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
459: CLOSE C_UPDATEPROFILE;
460: EXIT;
461: ELSE
462: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);
463: FORALL i in l_customer_id.first..l_customer_id.last
464: UPDATE HZ_CUSTOMER_PROFILES ACC
465: SET object_version_number = nvl(object_version_number,0) + 1,
466: ACC.LAST_UPDATE_DATE = SYSDATE,

Line 479: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);

475: AND ACC.SITE_USE_ID IS NULL
476: AND ACC.CUST_ACCOUNT_ID <> -1
477: AND ACC.COLLECTOR_ID <> l_collector_id(i);
478: --Commit When the Bulk commit size is reached.
479: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);
480: COMMIT;
481: END IF;
482: EXCEPTION WHEN deadlock_detected THEN
483: BEGIN

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

480: COMMIT;
481: END IF;
482: EXCEPTION WHEN deadlock_detected THEN
483: BEGIN
484: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
485: ROLLBACK;
486: FOR i in l_first .. l_last LOOP
487: BEGIN
488: UPDATE HZ_CUSTOMER_PROFILES ACC

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

499: AND ACC.SITE_USE_ID IS NULL
500: AND ACC.CUST_ACCOUNT_ID <> -1
501: AND ACC.COLLECTOR_ID <> l_collector_id(i);
502: EXCEPTION WHEN OTHERS THEN
503: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
504: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
505: ' SQLERRM: ' || SQLERRM);
506: END;
507: END LOOP;

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

500: AND ACC.CUST_ACCOUNT_ID <> -1
501: AND ACC.COLLECTOR_ID <> l_collector_id(i);
502: EXCEPTION WHEN OTHERS THEN
503: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
504: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
505: ' SQLERRM: ' || SQLERRM);
506: END;
507: END LOOP;
508: END;

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

506: END;
507: END LOOP;
508: END;
509: WHEN OTHERS THEN
510: IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);
511: END;
512: END LOOP;
513: IF C_UPDATEPROFILE%ISOPEN THEN
514: CLOSE C_UPDATEPROFILE;

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

513: IF C_UPDATEPROFILE%ISOPEN THEN
514: CLOSE C_UPDATEPROFILE;
515: END IF;
516:
517: IEX_TERR_WINNERS_PUB.Print_Debug('*** Completed Account Level Collector Assignment ***');
518: l_loop_count := 0;
519:
520: l_customer_id.delete;
521: l_terr_id.delete;

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

535: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
536: RAISE;
537:
538: WHEN others THEN
539: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
540: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
541: ' SQLERRM: ' || SQLERRM);
542: x_errbuf := SQLERRM;
543: x_retcode := SQLCODE;

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

536: RAISE;
537:
538: WHEN others THEN
539: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
540: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
541: ' SQLERRM: ' || SQLERRM);
542: x_errbuf := SQLERRM;
543: x_retcode := SQLCODE;
544: RAISE;

Line 552: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)

548:
549: PROCEDURE AssignSiteUseCollectors(
550: x_errbuf OUT NOCOPY VARCHAR2,
551: x_retcode OUT NOCOPY VARCHAR2,
552: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
553: IS
554: /*CURSOR c_UpdateProfile(c_worker_id number) IS
555: SELECT WIN.TRANS_OBJECT_id,
556: WIN.TRANS_DETAIL_OBJECT_ID,

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

685: l_Status BOOLEAN;
686: -------------------------------------------------------------------------------
687:
688: BEGIN
689: IEX_TERR_WINNERS_PUB.Print_Debug('*** Started Site Level Collector Assignment ***');
690:
691: l_Assignlevel := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
692: l_worker_id :=p_terr_globals.worker_id;
693: l_var :=p_terr_globals.bulk_size;

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

694: l_max_fetches := p_terr_globals.cursor_limit;
695:
696:
697: FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level (Picked from IEX_ACCESS_LEVEL) := ' ||l_Assignlevel);
698: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size =' ||l_var);
699: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size =' ||l_max_fetches);
700:
701: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
702: if (x_retcode = 'E') then

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

695:
696:
697: FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level (Picked from IEX_ACCESS_LEVEL) := ' ||l_Assignlevel);
698: IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size =' ||l_var);
699: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size =' ||l_max_fetches);
700:
701: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
702: if (x_retcode = 'E') then
703: IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');

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

699: IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size =' ||l_max_fetches);
700:
701: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
702: if (x_retcode = 'E') then
703: IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');
704: IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');
705: return;
706: end if;
707: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');

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

700:
701: CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
702: if (x_retcode = 'E') then
703: IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');
704: IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');
705: return;
706: end if;
707: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
708: --Bug4613487. Fix by lkkumar on 29-Sep-2005. Start.

Line 707: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');

703: IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');
704: IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');
705: return;
706: end if;
707: IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
708: --Bug4613487. Fix by lkkumar on 29-Sep-2005. Start.
709: OPEN c_UpdateProfile(l_worker_id);
710: LOOP
711: BEGIN

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

713: l_customer_id, l_partysite_id, l_salesforce_id, l_sales_group_id,
714: l_org_id, l_Res_type, l_Collector_ID,l_cust_account_id, l_siteuse_id
715: LIMIT l_max_fetches;
716: IF l_customer_id.count = 0 THEN
717: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
718: CLOSE C_UPDATEPROFILE;
719: EXIT;
720: ELSE
721: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);

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

717: IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
718: CLOSE C_UPDATEPROFILE;
719: EXIT;
720: ELSE
721: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);
722: FORALL i in l_customer_id.first..l_customer_id.last
723: UPDATE HZ_CUSTOMER_PROFILES ACC
724: SET object_version_number = nvl(object_version_number,0) + 1,
725: ACC.LAST_UPDATE_DATE = SYSDATE,

Line 738: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);

734: AND ACC.CUST_ACCOUNT_ID = l_cust_account_id(i)
735: AND ACC.SITE_USE_ID = l_siteuse_id(i)
736: AND ACC.COLLECTOR_ID <> l_collector_id(i);
737: --Commit When the Bulk commit size is reached.
738: IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);
739: COMMIT;
740: END IF;
741: EXCEPTION WHEN deadlock_detected THEN
742: BEGIN

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

739: COMMIT;
740: END IF;
741: EXCEPTION WHEN deadlock_detected THEN
742: BEGIN
743: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
744: ROLLBACK;
745: FOR i in l_first .. l_last LOOP
746: BEGIN
747: UPDATE HZ_CUSTOMER_PROFILES ACC

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

759: AND ACC.SITE_USE_ID = l_siteuse_id(i)
760: AND ACC.COLLECTOR_ID <> l_collector_id(i);
761: EXCEPTION
762: WHEN OTHERS THEN
763: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
764: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
765: ' SQLERRM: ' || SQLERRM);
766: END;
767: END LOOP;

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

760: AND ACC.COLLECTOR_ID <> l_collector_id(i);
761: EXCEPTION
762: WHEN OTHERS THEN
763: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
764: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
765: ' SQLERRM: ' || SQLERRM);
766: END;
767: END LOOP;
768: END;

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

766: END;
767: END LOOP;
768: END;
769: WHEN OTHERS THEN
770: IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);
771: END;
772: END LOOP;
773: IF C_UPDATEPROFILE%ISOPEN THEN
774: CLOSE C_UPDATEPROFILE;

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

773: IF C_UPDATEPROFILE%ISOPEN THEN
774: CLOSE C_UPDATEPROFILE;
775: END IF;
776: --Bug4613487. Fix by lkkumar on 29-Sep-2005. End.
777: IEX_TERR_WINNERS_PUB.Print_Debug('*** Completed Site Level Collector Assignment ***');
778:
779: l_limit_flag := FALSE;
780: l_loop_count := 0;
781:

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

797: x_retcode := FND_API.G_RET_STS_UNEXP_ERROR;
798: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
799: RAISE;
800: WHEN others THEN
801: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::AssignSiteUseCollectors');
802: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
803: ' SQLERRM: ' || SQLERRM);
804: x_errbuf := SQLERRM;
805: x_retcode := SQLCODE;

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

798: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
799: RAISE;
800: WHEN others THEN
801: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::AssignSiteUseCollectors');
802: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
803: ' SQLERRM: ' || SQLERRM);
804: x_errbuf := SQLERRM;
805: x_retcode := SQLCODE;
806: RAISE;

Line 813: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS

809: PROCEDURE CheckCollectors(
810: x_errbuf OUT NOCOPY VARCHAR2,
811: x_retcode OUT NOCOPY VARCHAR2,
812: p_worker_id IN NUMBER,
813: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS
814:
815: l_missSF_id salesforce_id_list; -- Missed Sales Force ID
816: l_missSG_id sales_group_id_list; -- Missed Sales Group ID
817: l_missPer_ID person_id_list; -- Missed Person ID

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

858: if (l_limit_flag) then
859: EXIT;
860: End If;
861: l_loop_count := l_loop_count + 1;
862: IEX_TERR_WINNERS_PUB.Print_Debug('*** Check Resources not in AR Collectors. LOOPING Count -> :'||l_loop_count);
863:
864: --------------------------------
865: l_attempts := 1;
866: l_exceptions := FALSE;

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

865: l_attempts := 1;
866: l_exceptions := FALSE;
867: WHILE l_attempts < 3 LOOP -- Bulk read Collectors. attempts < 3
868: BEGIN
869: IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);
870: OPEN c_MissedCollectors(p_worker_id);
871: FETCH c_MissedCollectors BULK COLLECT INTO
872: l_missSF_id, l_missSG_id, l_missOrg_ID, l_MissResType, l_missPer_ID
873: LIMIT l_max_fetches;

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

875: l_attempts := 3;
876: l_exceptions := FALSE;
877: EXCEPTION
878: WHEN Others THEN
879: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
880: l_attempts := l_attempts +1;
881: l_exceptions := TRUE;
882: if c_MissedCollectors%ISOPEN then
883: CLOSE c_MissedCollectors;

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

888: RAISE;
889: end if;
890: END;
891: END LOOP; -- End Bulk read Sales Force ID. attempts < 3
892: IEX_TERR_WINNERS_PUB.Print_Debug('--- Read Missed Collectors End-Attempts: '||l_attempts);
893:
894: -- Initialize variables
895: if l_missSF_id.count < l_max_fetches then
896: l_limit_flag := TRUE;

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

895: if l_missSF_id.count < l_max_fetches then
896: l_limit_flag := TRUE;
897: end if;
898:
899: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start INSERT OF AR_COLLECTORS = . ' || l_missSF_id.count);
900:
901: IF l_missSF_id.count > 0 THEN -- if l_SalesForce_id.count > 0
902:
903: l_attempts := 1;

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

900:
901: IF l_missSF_id.count > 0 THEN -- if l_SalesForce_id.count > 0
902:
903: l_attempts := 1;
904: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');
905:
906: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
907: BEGIN
908:

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

905:
906: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
907: BEGIN
908:
909: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
910:
911: FOR i in 1 .. l_missSF_id.count LOOP
912: BEGIN
913: IF (l_missResType(i) = 'RS_GROUP') THEN

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

920: INTO l_resource_name, l_source_id
921: FROM jtf_rs_resource_extns_vl
922: WHERE resource_id = l_missSF_id(i);
923: END IF;
924: IEX_TERR_WINNERS_PUB.Print_Debug('After selecting Resource_name = '|| l_resource_name );
925:
926: INSERT INTO AR_COLLECTORS
927: (COLLECTOR_ID ,
928: LAST_UPDATED_BY ,

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

958: END;
959: COMMIT;
960: END LOOP;
961: l_attempts := 3;
962: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
963: EXCEPTION
964: WHEN deadlock_detected THEN
965: begin
966: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);

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

962: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
963: EXCEPTION
964: WHEN deadlock_detected THEN
965: begin
966: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
967: rollback;
968: l_attempts := l_attempts +1;
969: if l_attempts = 3 then
970: FOR i in 1 .. l_missSF_id.count LOOP /*Inside deadlock detected loop */

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

1011: FND_FILE.PUT_LINE(FND_FILE.LOG, ' After Inserting in to the AR_COLLECTORS');
1012:
1013: EXCEPTION
1014: WHEN OTHERS THEN
1015: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
1016: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1017: ' SQLERRM: ' || SQLERRM);
1018: END;
1019: END LOOP; /* End Inside deadlock detected loop */

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

1012:
1013: EXCEPTION
1014: WHEN OTHERS THEN
1015: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
1016: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1017: ' SQLERRM: ' || SQLERRM);
1018: END;
1019: END LOOP; /* End Inside deadlock detected loop */
1020: COMMIT;

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

1021: end if;
1022: end; -- end of deadlock exception
1023:
1024: WHEN OTHERS THEN
1025: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1026: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1027: ' SQLERRM: ' || SQLERRM);
1028: x_errbuf := SQLERRM;
1029: x_retcode := SQLCODE;

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

1022: end; -- end of deadlock exception
1023:
1024: WHEN OTHERS THEN
1025: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1026: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1027: ' SQLERRM: ' || SQLERRM);
1028: x_errbuf := SQLERRM;
1029: x_retcode := SQLCODE;
1030: RAISE;

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

1031: END;
1032: END LOOP; /* Update While loop; l_attempts < 3 */
1033:
1034: END IF; --l_salesforce.count > 0
1035: IEX_TERR_WINNERS_PUB.Print_Debug('---Check Collectors Account.End-'|| l_missSF_id.count||' Rows Updated.');
1036: --------------------------------
1037: END LOOP; -- End Bulk read non-existent Collector ID. attempts < 3
1038:
1039: EXCEPTION

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

1037: END LOOP; -- End Bulk read non-existent Collector ID. attempts < 3
1038:
1039: EXCEPTION
1040: WHEN others THEN
1041: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CheckCollectors');
1042: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1043: ' SQLERRM: ' || SQLERRM);
1044: x_errbuf := SQLERRM;
1045: x_retcode := SQLCODE;

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

1038:
1039: EXCEPTION
1040: WHEN others THEN
1041: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CheckCollectors');
1042: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1043: ' SQLERRM: ' || SQLERRM);
1044: x_errbuf := SQLERRM;
1045: x_retcode := SQLCODE;
1046: RAISE;

Line 1054: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS

1050: PROCEDURE CreatePartyProfiles(
1051: x_errbuf OUT NOCOPY VARCHAR2,
1052: x_retcode OUT NOCOPY VARCHAR2,
1053: p_worker_id IN NUMBER,
1054: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS
1055:
1056: l_missCustomer customer_id_list; -- Missed Customer Profiles
1057:
1058: l_max_fetches NUMBER;

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

1108: if (l_limit_flag) then
1109: EXIT;
1110: End If;
1111: l_loop_count := l_loop_count + 1;
1112: IEX_TERR_WINNERS_PUB.Print_Debug('*** Getting Parties with no profiles. LOOPING Count -> :'||l_loop_count);
1113:
1114: --------------------------------
1115: l_attempts := 1;
1116: l_exceptions := FALSE;

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

1115: l_attempts := 1;
1116: l_exceptions := FALSE;
1117: WHILE l_attempts < 3 LOOP -- Bulk read Party list. attempts < 3
1118: BEGIN
1119: IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);
1120: OPEN c_MissedProfiles(p_worker_id);
1121: FETCH c_MissedProfiles BULK COLLECT INTO l_missCustomer
1122: LIMIT l_max_fetches;
1123: CLOSE c_MissedProfiles;

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

1124: l_attempts := 3;
1125: l_exceptions := FALSE;
1126: EXCEPTION
1127: WHEN Others THEN
1128: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
1129: l_attempts := l_attempts +1;
1130: l_exceptions := TRUE;
1131: if c_MissedProfiles%ISOPEN then
1132: CLOSE c_MissedProfiles;

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

1137: RAISE;
1138: end if;
1139: END;
1140: END LOOP; -- End Bulk read Party list. attempts < 3
1141: IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles. End -Attempts: '||l_attempts);
1142:
1143: -- Initialize variables
1144: if l_missCustomer.count < l_max_fetches then
1145: l_limit_flag := TRUE;

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

1144: if l_missCustomer.count < l_max_fetches then
1145: l_limit_flag := TRUE;
1146: end if;
1147:
1148: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start. Creating customer Profiles = . ' || l_missCustomer.count);
1149:
1150: IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1151:
1152: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');

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

1148: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start. Creating customer Profiles = . ' || l_missCustomer.count);
1149:
1150: IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1151:
1152: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');
1153: l_attempts := 1;
1154:
1155: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1156: BEGIN

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

1154:
1155: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1156: BEGIN
1157:
1158: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
1159:
1160: FOR i in 1 .. l_missCustomer.count LOOP
1161: --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. Start.
1162: BEGIN

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

1167: AND hcp.PARTY_ID = l_missCustomer(i)
1168: AND hcp.site_use_id is null;
1169: EXCEPTION WHEN NO_DATA_FOUND THEN
1170: BEGIN
1171: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1172: l_old_customer_profile_rec.party_id := l_missCustomer(i);
1173: l_old_customer_profile_rec.created_by_module := 'IEX';
1174: l_old_customer_profile_rec.site_use_id := NULL;
1175: l_old_customer_profile_rec.cust_account_id := NULL;

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

1180: x_return_status => l_return_status,
1181: x_msg_count => l_msg_count,
1182: x_msg_data => l_msg_data);
1183:
1184: IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || l_msg_count);
1185: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1186:
1187: EXCEPTION
1188: WHEN OTHERS THEN

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

1181: x_msg_count => l_msg_count,
1182: x_msg_data => l_msg_data);
1183:
1184: IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || l_msg_count);
1185: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1186:
1187: EXCEPTION
1188: WHEN OTHERS THEN
1189: FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );

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

1192: --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. END.
1193: END LOOP;
1194: COMMIT;
1195: l_attempts := 3;
1196: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1197: EXCEPTION
1198: WHEN deadlock_detected THEN
1199: begin
1200: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);

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

1196: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1197: EXCEPTION
1198: WHEN deadlock_detected THEN
1199: begin
1200: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
1201: rollback;
1202: l_attempts := l_attempts +1;
1203: if l_attempts = 3 then
1204: FOR i in 1 .. l_missCustomer.count LOOP /*Inside deadlock detected loop */

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

1211: AND hcp.PARTY_ID = l_missCustomer(i)
1212: AND hcp.site_use_id is null;
1213: EXCEPTION WHEN NO_DATA_FOUND THEN
1214: BEGIN
1215: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1216: l_old_customer_profile_rec.party_id := l_missCustomer(i);
1217: l_old_customer_profile_rec.created_by_module := 'IEX';
1218:
1219: HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (

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

1222: x_return_status => l_return_status,
1223: x_msg_count => l_msg_count,
1224: x_msg_data => l_msg_data);
1225:
1226: IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || l_msg_count);
1227: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1228:
1229: EXCEPTION
1230: WHEN OTHERS THEN

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

1223: x_msg_count => l_msg_count,
1224: x_msg_data => l_msg_data);
1225:
1226: IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || l_msg_count);
1227: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1228:
1229: EXCEPTION
1230: WHEN OTHERS THEN
1231: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');

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

1227: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1228:
1229: EXCEPTION
1230: WHEN OTHERS THEN
1231: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1232: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1233: ' SQLERRM: ' || SQLERRM);
1234: END;
1235: END;

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

1228:
1229: EXCEPTION
1230: WHEN OTHERS THEN
1231: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1232: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1233: ' SQLERRM: ' || SQLERRM);
1234: END;
1235: END;
1236: --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. End.

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

1240: end if;
1241: end; -- end of deadlock exception
1242:
1243: WHEN OTHERS THEN
1244: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1245: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1246: ' SQLERRM: ' || SQLERRM);
1247: x_errbuf := SQLERRM;
1248: x_retcode := SQLCODE;

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

1241: end; -- end of deadlock exception
1242:
1243: WHEN OTHERS THEN
1244: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1245: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1246: ' SQLERRM: ' || SQLERRM);
1247: x_errbuf := SQLERRM;
1248: x_retcode := SQLCODE;
1249: RAISE;

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

1250: END;
1251: END LOOP; /* Update While loop; l_attempts < 3 */
1252:
1253: END IF; --l_missCustomer .count > 0
1254: IEX_TERR_WINNERS_PUB.Print_Debug('---Create Customer Profile.End-'|| l_missCustomer.count||' Rows Updated.');
1255: --------------------------------
1256: END LOOP; -- End Bulk read non-existent Customer Profiles attempts < 3
1257:
1258: EXCEPTION

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

1256: END LOOP; -- End Bulk read non-existent Customer Profiles attempts < 3
1257:
1258: EXCEPTION
1259: WHEN others THEN
1260: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1261: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1262: ' SQLERRM: ' || SQLERRM);
1263: x_errbuf := SQLERRM;
1264: x_retcode := SQLCODE;

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

1257:
1258: EXCEPTION
1259: WHEN others THEN
1260: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1261: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1262: ' SQLERRM: ' || SQLERRM);
1263: x_errbuf := SQLERRM;
1264: x_retcode := SQLCODE;
1265: RAISE;

Line 1273: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS

1269: PROCEDURE CreateSiteUseProfiles(
1270: x_errbuf OUT NOCOPY VARCHAR2,
1271: x_retcode OUT NOCOPY VARCHAR2,
1272: p_worker_id IN NUMBER,
1273: p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS
1274:
1275: l_missCustomer customer_id_list; -- Missed Customer Profiles
1276: l_missSiteUse site_use_id_list;
1277: l_missAccount cust_account_id_list;

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

1332: if (l_limit_flag) then
1333: EXIT;
1334: End If;
1335: l_loop_count := l_loop_count + 1;
1336: IEX_TERR_WINNERS_PUB.Print_Debug('*** Getting Parties with no profiles. LOOPING Count -> :'||l_loop_count);
1337:
1338: --------------------------------
1339: l_attempts := 1;
1340: l_exceptions := FALSE;

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

1339: l_attempts := 1;
1340: l_exceptions := FALSE;
1341: WHILE l_attempts < 3 LOOP -- Bulk read Party list. attempts < 3
1342: BEGIN
1343: IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);
1344: OPEN c_MissedProfiles(p_worker_id);
1345: FETCH c_MissedProfiles BULK COLLECT INTO l_missCustomer,
1346: l_missSiteUse, l_missAccount LIMIT l_max_fetches;
1347: CLOSE c_MissedProfiles;

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

1348: l_attempts := 3;
1349: l_exceptions := FALSE;
1350: EXCEPTION
1351: WHEN Others THEN
1352: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
1353: l_attempts := l_attempts +1;
1354: l_exceptions := TRUE;
1355: if c_MissedProfiles%ISOPEN then
1356: CLOSE c_MissedProfiles;

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

1361: RAISE;
1362: end if;
1363: END;
1364: END LOOP; -- End Bulk read Party list. attempts < 3
1365: IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles.End-Attempts: '||l_attempts);
1366:
1367: -- Initialize variables
1368: if l_missCustomer.count < l_max_fetches then
1369: l_limit_flag := TRUE;

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

1368: if l_missCustomer.count < l_max_fetches then
1369: l_limit_flag := TRUE;
1370: end if;
1371:
1372: IEX_TERR_WINNERS_PUB.Print_Debug('--- Start. Create = . ' || l_missCustomer.count);
1373:
1374: IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1375:
1376: l_attempts := 1;

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

1373:
1374: IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1375:
1376: l_attempts := 1;
1377: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');
1378:
1379: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1380: BEGIN
1381:

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

1378:
1379: WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1380: BEGIN
1381:
1382: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
1383:
1384: FOR i in 1 .. l_missCustomer.count LOOP
1385: BEGIN
1386: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );

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

1382: IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
1383:
1384: FOR i in 1 .. l_missCustomer.count LOOP
1385: BEGIN
1386: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1387:
1388: l_old_customer_profile_rec.party_id := l_missCustomer(i);
1389: l_old_customer_profile_rec.cust_account_id := l_missAccount(i);
1390: l_old_customer_profile_rec.site_use_id := l_missSiteUse(i);

Line 1400: 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);

1396: x_return_status => l_return_status,
1397: x_msg_count => l_msg_count,
1398: x_msg_data => l_msg_data);
1399:
1400: 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);
1401: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1402:
1403: EXCEPTION
1404: WHEN OTHERS THEN

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

1397: x_msg_count => l_msg_count,
1398: x_msg_data => l_msg_data);
1399:
1400: 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);
1401: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1402:
1403: EXCEPTION
1404: WHEN OTHERS THEN
1405: FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );

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

1406: END;
1407: COMMIT;
1408: END LOOP;
1409: l_attempts := 3;
1410: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1411: EXCEPTION
1412: WHEN deadlock_detected THEN
1413: begin
1414: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);

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

1410: IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1411: EXCEPTION
1412: WHEN deadlock_detected THEN
1413: begin
1414: IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
1415: rollback;
1416: l_attempts := l_attempts +1;
1417: if l_attempts = 3 then
1418: FOR i in 1 .. l_missCustomer.count LOOP /*Inside deadlock detected loop */

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

1417: if l_attempts = 3 then
1418: FOR i in 1 .. l_missCustomer.count LOOP /*Inside deadlock detected loop */
1419: BEGIN
1420:
1421: IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1422:
1423: l_old_customer_profile_rec.party_id := l_missCustomer(i);
1424: l_old_customer_profile_rec.site_use_id := l_missSiteUse(i);
1425: l_old_customer_profile_rec.created_by_module := 'IEX';

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

1431: x_return_status => l_return_status,
1432: x_msg_count => l_msg_count,
1433: x_msg_data => l_msg_data);
1434:
1435: IEX_TERR_WINNERS_PUB.Print_Debug(' Return after create profile API ' || l_return_status ||
1436: ' msg_count ' || l_msg_count || ' msg_data ' || l_msg_data);
1437: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1438:
1439: EXCEPTION

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

1433: x_msg_data => l_msg_data);
1434:
1435: IEX_TERR_WINNERS_PUB.Print_Debug(' Return after create profile API ' || l_return_status ||
1436: ' msg_count ' || l_msg_count || ' msg_data ' || l_msg_data);
1437: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1438:
1439: EXCEPTION
1440: WHEN OTHERS THEN
1441: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');

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

1437: IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1438:
1439: EXCEPTION
1440: WHEN OTHERS THEN
1441: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1442: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1443: ' SQLERRM: ' || SQLERRM);
1444: END;
1445: END LOOP; /* End Inside deadlock detected loop */

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

1438:
1439: EXCEPTION
1440: WHEN OTHERS THEN
1441: IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1442: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1443: ' SQLERRM: ' || SQLERRM);
1444: END;
1445: END LOOP; /* End Inside deadlock detected loop */
1446: COMMIT;

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

1447: end if;
1448: end; -- end of deadlock exception
1449:
1450: WHEN OTHERS THEN
1451: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1452: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1453: ' SQLERRM: ' || SQLERRM);
1454: x_errbuf := SQLERRM;
1455: x_retcode := SQLCODE;

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

1448: end; -- end of deadlock exception
1449:
1450: WHEN OTHERS THEN
1451: IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1452: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1453: ' SQLERRM: ' || SQLERRM);
1454: x_errbuf := SQLERRM;
1455: x_retcode := SQLCODE;
1456: RAISE;

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

1457: END;
1458: END LOOP; /* Update While loop; l_attempts < 3 */
1459:
1460: END IF; --l_missCustomer .count > 0
1461: IEX_TERR_WINNERS_PUB.Print_Debug('---Create SiteUse Profile.End-'|| l_missCustomer.count||' Rows Updated.');
1462: --------------------------------
1463: END LOOP; -- End Bulk read non-existent Customer Profiles attempts < 3
1464:
1465: EXCEPTION

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

1463: END LOOP; -- End Bulk read non-existent Customer Profiles attempts < 3
1464:
1465: EXCEPTION
1466: WHEN others THEN
1467: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1468: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1469: ' SQLERRM: ' || SQLERRM);
1470: x_errbuf := SQLERRM;
1471: x_retcode := SQLCODE;

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

1464:
1465: EXCEPTION
1466: WHEN others THEN
1467: IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1468: IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1469: ' SQLERRM: ' || SQLERRM);
1470: x_errbuf := SQLERRM;
1471: x_retcode := SQLCODE;
1472: RAISE;