[Home] [Help]
1: PACKAGE BODY AS_GAR_ACCOUNTS_PUB AS
2: /* $Header: asxgracb.pls 120.11.12000000.2 2007/05/05 08:30:51 annsrini ship $ */
3:
4: ---------------------------------------------------------------------------
5: -- Start of Comments
3:
4: ---------------------------------------------------------------------------
5: -- Start of Comments
6: ---------------------------------------------------------------------------
7: -- PACKAGE NAME: AS_GAR_ACCOUNTS_PUB
8: -- ---------------------------------------------------------------------
9: -- PURPOSE
10: -- --------
11: -- This package contains procedures to accomplish each of the following
45: p_trace_mode IN VARCHAR2,
46: p_worker_id IN VARCHAR2 ,
47: P_percent_analyzed IN NUMBER )
48: IS
49: l_terr_globals AS_GAR.TERR_GLOBALS;
50: l_msg_count NUMBER;
51: l_msg_data VARCHAR2(2000);
52: l_errbuf VARCHAR2(4000);
53: l_retcode VARCHAR2(255);
63: WHERE worker_id = c_worker_id
64: AND resource_type IN ('RS_PARTNER','RS_PARTY')
65: AND ROWNUM < 2;
66: BEGIN
67: AS_GAR.g_debug_flag := p_debug_mode;
68: IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
69: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
70:
71: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
64: AND resource_type IN ('RS_PARTNER','RS_PARTY')
65: AND ROWNUM < 2;
66: BEGIN
67: AS_GAR.g_debug_flag := p_debug_mode;
68: IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
69: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
70:
71: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
72: l_target_type := 'TOTAL';
65: AND ROWNUM < 2;
66: BEGIN
67: AS_GAR.g_debug_flag := p_debug_mode;
68: IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
69: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
70:
71: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
72: l_target_type := 'TOTAL';
73: ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
67: AS_GAR.g_debug_flag := p_debug_mode;
68: IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
69: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
70:
71: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
72: l_target_type := 'TOTAL';
73: ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
74: l_target_type := 'INCREMENTAL';
75: END If;
69: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
70:
71: IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
72: l_target_type := 'TOTAL';
73: ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
74: l_target_type := 'INCREMENTAL';
75: END If;
76:
77: -- Set the Global variables
74: l_target_type := 'INCREMENTAL';
75: END If;
76:
77: -- Set the Global variables
78: AS_GAR.INIT(
79: p_run_mode,
80: p_worker_id,
81: l_terr_globals);
82:
80: p_worker_id,
81: l_terr_globals);
82:
83: /* This inserts into account winners */
84: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
85: JTY_ASSIGN_BULK_PUB.GET_WINNERS
86: ( p_api_version_number => 1.0,
87: p_init_msg_list => FND_API.G_TRUE,
88: p_source_id => -1001,
95: x_msg_count => l_msg_count,
96: x_msg_data => l_msg_data,
97: ERRBUF => l_errbuf,
98: RETCODE => l_retcode);
99: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
100: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
101:
102: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
103: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
96: x_msg_data => l_msg_data,
97: ERRBUF => l_errbuf,
98: RETCODE => l_retcode);
99: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
100: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
101:
102: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
103: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
104: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
100: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
101:
102: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
103: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
104: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105: End If;
106:
107: COMMIT;
106:
107: COMMIT;
108:
109: -- Explode GROUPS if any inside winners
110: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
111: AS_GAR_ACCOUNTS_PUB.EXPLODE_GROUPS_ACCOUNTS(
112: x_errbuf => l_errbuf,
113: x_retcode => l_retcode,
114: p_terr_globals => l_terr_globals,
107: COMMIT;
108:
109: -- Explode GROUPS if any inside winners
110: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
111: AS_GAR_ACCOUNTS_PUB.EXPLODE_GROUPS_ACCOUNTS(
112: x_errbuf => l_errbuf,
113: x_retcode => l_retcode,
114: p_terr_globals => l_terr_globals,
115: x_return_status => l_return_status);
113: x_retcode => l_retcode,
114: p_terr_globals => l_terr_globals,
115: x_return_status => l_return_status);
116:
117: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
118: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
119:
120: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
121: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
114: p_terr_globals => l_terr_globals,
115: x_return_status => l_return_status);
116:
117: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
118: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
119:
120: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
121: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
122: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
118: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
119:
120: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
121: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
122: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123: End If;
124:
125: COMMIT;
124:
125: COMMIT;
126:
127: -- Explode TEAMS if any inside winners
128: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
129: AS_GAR_ACCOUNTS_PUB.EXPLODE_TEAMS_ACCOUNTS(
130: x_errbuf => l_errbuf,
131: x_retcode => l_retcode,
132: p_terr_globals => l_terr_globals,
125: COMMIT;
126:
127: -- Explode TEAMS if any inside winners
128: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
129: AS_GAR_ACCOUNTS_PUB.EXPLODE_TEAMS_ACCOUNTS(
130: x_errbuf => l_errbuf,
131: x_retcode => l_retcode,
132: p_terr_globals => l_terr_globals,
133: x_return_status => l_return_status);
131: x_retcode => l_retcode,
132: p_terr_globals => l_terr_globals,
133: x_return_status => l_return_status);
134:
135: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
136: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
137:
138: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
139: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
132: p_terr_globals => l_terr_globals,
133: x_return_status => l_return_status);
134:
135: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
136: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
137:
138: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
139: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
140: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
136: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
137:
138: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
139: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
140: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
141: End If;
142:
143: COMMIT;
142:
143: COMMIT;
144:
145: -- Set team leader for Accounts
146: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
147: AS_GAR_ACCOUNTS_PUB.SET_TEAM_LEAD_ACCOUNTS(
148: x_errbuf => l_errbuf,
149: x_retcode => l_retcode,
150: p_terr_globals => l_terr_globals,
143: COMMIT;
144:
145: -- Set team leader for Accounts
146: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
147: AS_GAR_ACCOUNTS_PUB.SET_TEAM_LEAD_ACCOUNTS(
148: x_errbuf => l_errbuf,
149: x_retcode => l_retcode,
150: p_terr_globals => l_terr_globals,
151: x_return_status => l_return_status);
149: x_retcode => l_retcode,
150: p_terr_globals => l_terr_globals,
151: x_return_status => l_return_status);
152:
153: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
154: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
155:
156: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
157: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
150: p_terr_globals => l_terr_globals,
151: x_return_status => l_return_status);
152:
153: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
154: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
155:
156: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
157: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
158: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
154: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
155:
156: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
157: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
158: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159: End If;
160:
161: -- Insert into Account Accesses from Winners
158: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159: End If;
160:
161: -- Insert into Account Accesses from Winners
162: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
163: AS_GAR_ACCOUNTS_PUB.INSERT_ACCESSES_ACCOUNTS(
164: x_errbuf => l_errbuf,
165: x_retcode => l_retcode,
166: p_terr_globals => l_terr_globals,
159: End If;
160:
161: -- Insert into Account Accesses from Winners
162: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
163: AS_GAR_ACCOUNTS_PUB.INSERT_ACCESSES_ACCOUNTS(
164: x_errbuf => l_errbuf,
165: x_retcode => l_retcode,
166: p_terr_globals => l_terr_globals,
167: x_return_status => l_return_status);
165: x_retcode => l_retcode,
166: p_terr_globals => l_terr_globals,
167: x_return_status => l_return_status);
168:
169: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
170: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
171:
172: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
173: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
166: p_terr_globals => l_terr_globals,
167: x_return_status => l_return_status);
168:
169: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
170: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
171:
172: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
173: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
174: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
170: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
171:
172: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
173: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
174: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175: End If;
176:
177: -- Insert into territory Accesses
174: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175: End If;
176:
177: -- Insert into territory Accesses
178: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
179: AS_GAR_ACCOUNTS_PUB.INSERT_TERR_ACCESSES_ACCOUNTS(
180: x_errbuf => l_errbuf,
181: x_retcode => l_retcode,
182: p_terr_globals => l_terr_globals,
175: End If;
176:
177: -- Insert into territory Accesses
178: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
179: AS_GAR_ACCOUNTS_PUB.INSERT_TERR_ACCESSES_ACCOUNTS(
180: x_errbuf => l_errbuf,
181: x_retcode => l_retcode,
182: p_terr_globals => l_terr_globals,
183: x_return_status => l_return_status);
181: x_retcode => l_retcode,
182: p_terr_globals => l_terr_globals,
183: x_return_status => l_return_status);
184:
185: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
186: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
187:
188: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
189: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
182: p_terr_globals => l_terr_globals,
183: x_return_status => l_return_status);
184:
185: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
186: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
187:
188: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
189: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
190: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
185: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
186: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
187:
188: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
189: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
190: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191: End If;
192:
193: -- Create External Sales Team records for Account (No partner processing for opptys and leads)
194: OPEN get_count(l_terr_globals.worker_id);
195: FETCH get_count INTO l_count;
196: CLOSE get_count;
197: IF l_count > 0 THEN -- If There are any PRM Resources assigned in JTY
198: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ' || AS_GAR.G_START);
199: AS_GAR_ACCOUNTS_PUB.SET_TEAM_LEAD_PRM_ACCOUNTS(
200: x_errbuf => l_errbuf,
201: x_retcode => l_retcode,
202: p_terr_globals => l_terr_globals,
195: FETCH get_count INTO l_count;
196: CLOSE get_count;
197: IF l_count > 0 THEN -- If There are any PRM Resources assigned in JTY
198: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ' || AS_GAR.G_START);
199: AS_GAR_ACCOUNTS_PUB.SET_TEAM_LEAD_PRM_ACCOUNTS(
200: x_errbuf => l_errbuf,
201: x_retcode => l_retcode,
202: p_terr_globals => l_terr_globals,
203: x_return_status => l_return_status);
201: x_retcode => l_retcode,
202: p_terr_globals => l_terr_globals,
203: x_return_status => l_return_status);
204:
205: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ' || AS_GAR.G_END);
206: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ' || AS_GAR.G_RETURN_STATUS || l_return_status);
207:
208: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
209: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ', l_errbuf, l_retcode);
202: p_terr_globals => l_terr_globals,
203: x_return_status => l_return_status);
204:
205: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ' || AS_GAR.G_END);
206: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ' || AS_GAR.G_RETURN_STATUS || l_return_status);
207:
208: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
209: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ', l_errbuf, l_retcode);
210: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
205: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ' || AS_GAR.G_END);
206: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ' || AS_GAR.G_RETURN_STATUS || l_return_status);
207:
208: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
209: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || 'PRM:: ', l_errbuf, l_retcode);
210: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211: End If;
212:
213: -- Insert into Account Accesses from Winners
210: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211: End If;
212:
213: -- Insert into Account Accesses from Winners
214: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ' || AS_GAR.G_START);
215: AS_GAR_ACCOUNTS_PUB.INSERT_ACCESSES_PRM_ACCOUNTS(
216: x_errbuf => l_errbuf,
217: x_retcode => l_retcode,
218: p_terr_globals => l_terr_globals,
211: End If;
212:
213: -- Insert into Account Accesses from Winners
214: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ' || AS_GAR.G_START);
215: AS_GAR_ACCOUNTS_PUB.INSERT_ACCESSES_PRM_ACCOUNTS(
216: x_errbuf => l_errbuf,
217: x_retcode => l_retcode,
218: p_terr_globals => l_terr_globals,
219: x_return_status => l_return_status);
217: x_retcode => l_retcode,
218: p_terr_globals => l_terr_globals,
219: x_return_status => l_return_status);
220:
221: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ' || AS_GAR.G_END);
222: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ' || AS_GAR.G_RETURN_STATUS || l_return_status);
223:
224: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
225: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ', l_errbuf, l_retcode);
218: p_terr_globals => l_terr_globals,
219: x_return_status => l_return_status);
220:
221: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ' || AS_GAR.G_END);
222: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ' || AS_GAR.G_RETURN_STATUS || l_return_status);
223:
224: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
225: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ', l_errbuf, l_retcode);
226: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ' || AS_GAR.G_END);
222: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ' || AS_GAR.G_RETURN_STATUS || l_return_status);
223:
224: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
225: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || 'PRM:: ', l_errbuf, l_retcode);
226: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227: End If;
228:
229: -- Insert into territory Accesses
226: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227: End If;
228:
229: -- Insert into territory Accesses
230: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ' || AS_GAR.G_START);
231: AS_GAR_ACCOUNTS_PUB.INSERT_TERR_ACCESSES_PRM_ACCS(
232: x_errbuf => l_errbuf,
233: x_retcode => l_retcode,
234: p_terr_globals => l_terr_globals,
227: End If;
228:
229: -- Insert into territory Accesses
230: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ' || AS_GAR.G_START);
231: AS_GAR_ACCOUNTS_PUB.INSERT_TERR_ACCESSES_PRM_ACCS(
232: x_errbuf => l_errbuf,
233: x_retcode => l_retcode,
234: p_terr_globals => l_terr_globals,
235: x_return_status => l_return_status);
233: x_retcode => l_retcode,
234: p_terr_globals => l_terr_globals,
235: x_return_status => l_return_status);
236:
237: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ' || AS_GAR.G_END);
238: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ' || AS_GAR.G_RETURN_STATUS || l_return_status);
239:
240: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
241: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ', l_errbuf, l_retcode);
234: p_terr_globals => l_terr_globals,
235: x_return_status => l_return_status);
236:
237: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ' || AS_GAR.G_END);
238: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ' || AS_GAR.G_RETURN_STATUS || l_return_status);
239:
240: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
241: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ', l_errbuf, l_retcode);
242: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
237: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ' || AS_GAR.G_END);
238: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ' || AS_GAR.G_RETURN_STATUS || l_return_status);
239:
240: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
241: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || 'PRM:: ', l_errbuf, l_retcode);
242: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
243: End If;
244:
245: END IF;
244:
245: END IF;
246:
247: -- Remove (soft delete) records in access table that are not qualified
248: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
249: AS_GAR_ACCOUNTS_PUB.Perform_Account_Cleanup(
250: x_errbuf => l_errbuf,
251: x_retcode => l_retcode,
252: p_terr_globals => l_terr_globals,
245: END IF;
246:
247: -- Remove (soft delete) records in access table that are not qualified
248: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
249: AS_GAR_ACCOUNTS_PUB.Perform_Account_Cleanup(
250: x_errbuf => l_errbuf,
251: x_retcode => l_retcode,
252: p_terr_globals => l_terr_globals,
253: x_return_status => l_return_status);
251: x_retcode => l_retcode,
252: p_terr_globals => l_terr_globals,
253: x_return_status => l_return_status);
254:
255: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
256: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
257:
258: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
259: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
252: p_terr_globals => l_terr_globals,
253: x_return_status => l_return_status);
254:
255: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
256: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
257:
258: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
259: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
260: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
256: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
257:
258: If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
259: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
260: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261: End If;
262:
263: -- BES enhancement
261: End If;
262:
263: -- BES enhancement
264:
265: l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
266: IF l_sub_exist = 'Y' THEN
267: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
268: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
269: AS_GAR.Raise_BE(l_terr_globals);
263: -- BES enhancement
264:
265: l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
266: IF l_sub_exist = 'Y' THEN
267: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
268: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
269: AS_GAR.Raise_BE(l_terr_globals);
270: END If;
271:
264:
265: l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
266: IF l_sub_exist = 'Y' THEN
267: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
268: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
269: AS_GAR.Raise_BE(l_terr_globals);
270: END If;
271:
272: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
265: l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
266: IF l_sub_exist = 'Y' THEN
267: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
268: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
269: AS_GAR.Raise_BE(l_terr_globals);
270: END If;
271:
272: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
273: EXCEPTION
268: AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
269: AS_GAR.Raise_BE(l_terr_globals);
270: END If;
271:
272: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
273: EXCEPTION
274: WHEN OTHERS THEN
275: AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
276: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
271:
272: AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
273: EXCEPTION
274: WHEN OTHERS THEN
275: AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
276: l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
277: END GAR_WRAPPER;
278:
279: /************************** End GAR Wrapper *****************************/
281: /************************** Start Explode Teams Accounts ******************/
282: PROCEDURE EXPLODE_TEAMS_ACCOUNTS(
283: x_errbuf OUT NOCOPY VARCHAR2,
284: x_retcode OUT NOCOPY VARCHAR2,
285: p_terr_globals IN AS_GAR.TERR_GLOBALS,
286: x_return_status OUT NOCOPY VARCHAR2)
287: IS
288:
289: /*-------------------------------------------------------------------------+
329: AND rsc.terr_rsc_id = acc.terr_rsc_id;
330:
331:
332: BEGIN
333: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
334: x_return_status := FND_API.G_RET_STS_SUCCESS;
335: l_request_id := p_terr_globals.request_id;
336: l_worker_id := p_terr_globals.worker_id;
337: l_resource_type := 'RS_TEAM';
345: FETCH c_get_res_type_count INTO l_res_type_count;
346: CLOSE c_get_res_type_count;
347: END IF;
348:
349: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
350: IF l_res_type_count > 0 THEN
351: /* Get resources within a resource team */
352: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
353: /** Note
348:
349: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
350: IF l_res_type_count > 0 THEN
351: /* Get resources within a resource team */
352: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
353: /** Note
354: Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
355: because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
356: **/
534: AND rt1.trans_object_id = t.trans_object_id
535: AND NVL(rt1.trans_detail_object_id,-1) =
536: NVL(t.trans_detail_object_id,-1));
537:
538: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
539: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
540:
541:
542: COMMIT;
535: AND NVL(rt1.trans_detail_object_id,-1) =
536: NVL(t.trans_detail_object_id,-1));
537:
538: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
539: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
540:
541:
542: COMMIT;
543:
543:
544: END IF; /* if l_res_type_count > 0 */
545: EXCEPTION
546: WHEN others THEN
547: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
548: x_errbuf := SQLERRM;
549: x_retcode := SQLCODE;
550: x_return_status := FND_API.G_RET_STS_ERROR;
551: END EXPLODE_TEAMS_ACCOUNTS;
554: /************************** Start Explode Groups Accounts ******************/
555: PROCEDURE EXPLODE_GROUPS_ACCOUNTS(
556: x_errbuf OUT NOCOPY VARCHAR2,
557: x_retcode OUT NOCOPY VARCHAR2,
558: p_terr_globals IN AS_GAR.TERR_GLOBALS,
559: x_return_status OUT NOCOPY VARCHAR2)
560: IS
561: -------------RS_GROUP---------
562: /*-------------------------------------------------------------------------+
611: FETCH c_get_res_type_count INTO l_res_type_count;
612: CLOSE c_get_res_type_count;
613: END IF;
614:
615: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
616: IF l_res_type_count > 0 THEN
617: /* Get resources within a resource group */
618: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
619: /** Note
614:
615: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
616: IF l_res_type_count > 0 THEN
617: /* Get resources within a resource group */
618: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
619: /** Note
620: Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
621: because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
622: **/
716: AND rt1.worker_id = t.worker_id
717: AND rt1.trans_object_id = t.trans_object_id
718: AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
719:
720: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
721: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
722:
723: COMMIT;
724: END IF; /* if l_res_type_count > 0 */
717: AND rt1.trans_object_id = t.trans_object_id
718: AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
719:
720: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
721: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
722:
723: COMMIT;
724: END IF; /* if l_res_type_count > 0 */
725:
724: END IF; /* if l_res_type_count > 0 */
725:
726: EXCEPTION
727: WHEN others THEN
728: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS, SQLERRM, TO_CHAR(SQLCODE));
729: x_errbuf := SQLERRM;
730: x_retcode := SQLCODE;
731: x_return_status := FND_API.G_RET_STS_ERROR;
732: END EXPLODE_GROUPS_ACCOUNTS;
752:
753: PROCEDURE SET_TEAM_LEAD_ACCOUNTS(
754: x_errbuf OUT NOCOPY VARCHAR2,
755: x_retcode OUT NOCOPY VARCHAR2,
756: p_terr_globals IN AS_GAR.TERR_GLOBALS,
757: x_return_status OUT NOCOPY VARCHAR2)
758: IS
759:
760: TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
797: WIN.full_access_flag;
798:
799:
800: BEGIN
801: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_START);
802: x_return_status := FND_API.G_RET_STS_SUCCESS;
803: l_worker_id:=p_terr_globals.worker_id;
804: l_var :=p_terr_globals.bulk_size;
805: l_MAX_fetches := p_terr_globals.cursor_limit;
810: l_salesforce_id.DELETE;
811: l_sales_group_id.DELETE;
812: l_faf.DELETE;
813: l_loop_count := l_loop_count + 1;
814: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'LOOPCOUNT :- ' || l_loop_count);
815:
816: --------------------------------
817: OPEN team_leader(l_worker_id);
818: FETCH team_leader BULK COLLECT INTO
828:
829: IF l_customer_id.COUNT < l_MAX_fetches THEN
830: l_limit_flag := TRUE;
831: END IF;
832: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
833:
834: IF l_customer_id.COUNT > 0 THEN
835: l_flag := TRUE;
836: l_first := l_customer_id.FIRST;
859: AND ACC.salesforce_id = l_salesforce_id(i)
860: AND ACC.sales_group_id = l_sales_group_id(i);
861: COMMIT;
862: l_attempts := 3;
863: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS ||AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
864: EXCEPTION
865: WHEN DEADLOCK_DETECTED THEN
866: BEGIN
867: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_DEADLOCK ||l_attempts);
863: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS ||AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
864: EXCEPTION
865: WHEN DEADLOCK_DETECTED THEN
866: BEGIN
867: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_DEADLOCK ||l_attempts);
868: ROLLBACK;
869: l_attempts := l_attempts +1;
870: IF l_attempts = 3 THEN
871: FOR i IN l_first .. l_last
888: AND ACC.salesforce_id = l_salesforce_id(i)
889: AND ACC.sales_group_id = l_sales_group_id(i);
890: EXCEPTION
891: WHEN OTHERS THEN
892: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
893: AS_GAR.LOG('CUSTOMER_ID/SALESFORCE_ID/SALESGROUP_ID/ORG_ID - ' || l_customer_id(i) || '/' || l_salesforce_id(i) || '/' || l_sales_group_id(i));
894: END;
895: END LOOP; -- for each record individually
896: COMMIT;
889: AND ACC.sales_group_id = l_sales_group_id(i);
890: EXCEPTION
891: WHEN OTHERS THEN
892: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
893: AS_GAR.LOG('CUSTOMER_ID/SALESFORCE_ID/SALESGROUP_ID/ORG_ID - ' || l_customer_id(i) || '/' || l_salesforce_id(i) || '/' || l_sales_group_id(i));
894: END;
895: END LOOP; -- for each record individually
896: COMMIT;
897: END IF;
896: COMMIT;
897: END IF;
898: END; -- end of deadlock exception
899: WHEN OTHERS THEN
900: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD, SQLERRM, TO_CHAR(SQLCODE));
901: x_errbuf := SQLERRM;
902: x_retcode := SQLCODE;
903: x_return_status := FND_API.G_RET_STS_ERROR;
904: END;
910: l_flag := FALSE;
911: END IF;
912: END LOOP; -- loop for more records within the bulk_size
913: END IF; --l_customer_id.count > 0
914: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
915: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_N_ROWS_PROCESSED || l_customer_id.COUNT);
916: END LOOP; -- loop for more bulk_size fetches
917: l_customer_id.DELETE;
918: l_salesforce_id.DELETE;
911: END IF;
912: END LOOP; -- loop for more records within the bulk_size
913: END IF; --l_customer_id.count > 0
914: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
915: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_N_ROWS_PROCESSED || l_customer_id.COUNT);
916: END LOOP; -- loop for more bulk_size fetches
917: l_customer_id.DELETE;
918: l_salesforce_id.DELETE;
919: l_sales_group_id.DELETE;
919: l_sales_group_id.DELETE;
920: l_faf.DELETE;
921: EXCEPTION
922: WHEN OTHERS THEN
923: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD, SQLERRM, TO_CHAR(SQLCODE));
924: x_errbuf := SQLERRM;
925: x_retcode := SQLCODE;
926: x_return_status := FND_API.G_RET_STS_ERROR;
927: END SET_TEAM_LEAD_ACCOUNTS;
932:
933: PROCEDURE INSERT_ACCESSES_ACCOUNTS(
934: x_errbuf OUT NOCOPY VARCHAR2,
935: x_retcode OUT NOCOPY VARCHAR2,
936: p_terr_globals IN AS_GAR.TERR_GLOBALS,
937: x_return_status OUT NOCOPY VARCHAR2)
938: IS
939: TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
940: TYPE faf_list is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
1034: | will always be null.
1035: | Try bulk inserting into accesses. If this fails, insert records one by one.
1036: |
1037: +-------------------------------------------------------------------------*/
1038: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
1039: x_return_status := FND_API.G_RET_STS_SUCCESS;
1040: l_worker_id:=p_terr_globals.worker_id;
1041: l_var :=p_terr_globals.bulk_size;
1042:
1053:
1054:
1055: if l_src_exists='Y' then
1056: OPEN ins_acc(l_worker_id); -- Open the salesforce role code cursor
1057: AS_GAR.LOG('---Opening ins_acc cursor--');
1058: else
1059: OPEN ins_acc2(l_worker_id); -- Open the null salesforce role code cursor
1060: AS_GAR.LOG('---Opening ins_acc2 cursor--');
1061: end if;
1056: OPEN ins_acc(l_worker_id); -- Open the salesforce role code cursor
1057: AS_GAR.LOG('---Opening ins_acc cursor--');
1058: else
1059: OPEN ins_acc2(l_worker_id); -- Open the null salesforce role code cursor
1060: AS_GAR.LOG('---Opening ins_acc2 cursor--');
1061: end if;
1062:
1063: LOOP
1064: IF (l_limit_flag) THEN EXIT; END IF;
1080: EXIT when ins_acc2%NOTFOUND;
1081: end if;
1082:
1083: l_loop_count := l_loop_count + 1;
1084: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'LOOPCOUNT :- ' || l_loop_count);
1085:
1086: if l_src_exists='Y' then
1087: FETCH ins_acc BULK COLLECT INTO
1088: l_salesforce_id, l_sales_group_id, l_person_id,
1112: IF l_last > l_customer_id.last THEN
1113: l_last := l_customer_id.last;
1114: END IF;
1115: BEGIN
1116: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' to '||l_last);
1117: FORALL i IN l_first .. l_last
1118: INSERT INTO AS_ACCESSES_ALL_ALL
1119: ( access_id
1120: ,access_type
1173: AND AA.sales_group_id = l_sales_group_id(i)
1174:
1175: )
1176: );
1177: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1178: COMMIT;
1179: EXCEPTION
1180: WHEN DUP_VAL_ON_INDEX THEN
1181: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1177: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1178: COMMIT;
1179: EXCEPTION
1180: WHEN DUP_VAL_ON_INDEX THEN
1181: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1182: FOR i IN l_first .. l_last LOOP
1183: BEGIN
1184: INSERT INTO AS_ACCESSES_ALL_ALL
1185: ( access_id
1245: END;
1246: END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
1247: COMMIT;
1248: WHEN OTHERS THEN
1249: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1250: x_errbuf := SQLERRM;
1251: x_retcode := SQLCODE;
1252: x_return_status := FND_API.G_RET_STS_ERROR;
1253: END;
1272: IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1273: IF ins_acc2%ISOPEN THEN CLOSE ins_acc2; END IF;
1274: EXCEPTION
1275: WHEN others THEN
1276: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC, SQLERRM, TO_CHAR(SQLCODE));
1277: x_errbuf := SQLERRM;
1278: x_retcode := SQLCODE;
1279: x_return_status := FND_API.G_RET_STS_ERROR;
1280: IF ins_acc%ISOPEN THEN CLOSE ins_acc; END IF;
1287:
1288: PROCEDURE INSERT_TERR_ACCESSES_ACCOUNTS(
1289: x_errbuf OUT NOCOPY VARCHAR2,
1290: x_retcode OUT NOCOPY VARCHAR2,
1291: p_terr_globals IN AS_GAR.TERR_GLOBALS,
1292: x_return_status OUT NOCOPY VARCHAR2)
1293: IS
1294: TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1295:
1331: | Almost the same as accesses, except the insertion is into as_territory_accesses
1332: | and there is no involvement of role.
1333: |
1334: +-------------------------------------------------------------------------*/
1335: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
1336: x_return_status := FND_API.G_RET_STS_SUCCESS;
1337: l_worker_id := p_terr_globals.worker_id;
1338: l_var := p_terr_globals.bulk_size;
1339: OPEN ins_tacc(l_worker_id);
1344: l_customer_id.DELETE;
1345: l_salesforce_id.DELETE;
1346: l_sales_group_id.DELETE;
1347: l_terr_id.DELETE;
1348: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'LOOPCOUNT :- ' || l_loop_count);
1349: BEGIN
1350:
1351: FETCH ins_tacc BULK COLLECT INTO l_terr_id,
1352: l_customer_id, l_salesforce_id, l_sales_group_id
1365: IF l_last > l_customer_id.LAST THEN
1366: l_last := l_customer_id.LAST;
1367: END IF;
1368: BEGIN
1369: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1370: FORALL i IN l_first .. l_last
1371: INSERT INTO AS_TERRITORY_ACCESSES
1372: ( access_id,
1373: territory_id,
1411: WHERE AST.access_id = A.access_id
1412: AND AST.territory_id = l_terr_id(i))
1413: ) V
1414: );
1415: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1416: COMMIT;
1417: EXCEPTION
1418: WHEN DUP_VAL_ON_INDEX THEN
1419: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1415: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1416: COMMIT;
1417: EXCEPTION
1418: WHEN DUP_VAL_ON_INDEX THEN
1419: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1420: FOR i IN l_first .. l_last LOOP
1421: BEGIN
1422: INSERT INTO AS_TERRITORY_ACCESSES
1423: ( access_id,
1467: WHEN Others THEN
1468: NULL;
1469: END;
1470: END LOOP;
1471: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1472: COMMIT;
1473: WHEN Others THEN
1474: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1475: x_errbuf := SQLERRM;
1470: END LOOP;
1471: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1472: COMMIT;
1473: WHEN Others THEN
1474: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1475: x_errbuf := SQLERRM;
1476: x_retcode := SQLCODE;
1477: x_return_status := FND_API.G_RET_STS_ERROR;
1478: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1485: END LOOP;
1486: END IF; --l_customer_id.count > 0
1487: EXCEPTION
1488: WHEN Others THEN
1489: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC , SQLERRM, TO_CHAR(SQLCODE));
1490: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1491: x_errbuf := SQLERRM;
1492: x_retcode := SQLCODE;
1493: x_return_status := FND_API.G_RET_STS_ERROR;
1497: l_salesforce_id.DELETE;
1498: l_sales_group_id.DELETE;
1499: l_terr_id.DELETE;
1500: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1501: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
1502: EXCEPTION
1503: WHEN others THEN
1504: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
1505: x_errbuf := SQLERRM;
1500: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1501: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
1502: EXCEPTION
1503: WHEN others THEN
1504: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
1505: x_errbuf := SQLERRM;
1506: x_retcode := SQLCODE;
1507: x_return_status := FND_API.G_RET_STS_ERROR;
1508: IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1530:
1531: PROCEDURE SET_TEAM_LEAD_PRM_ACCOUNTS(
1532: x_errbuf OUT NOCOPY VARCHAR2,
1533: x_retcode OUT NOCOPY VARCHAR2,
1534: p_terr_globals IN AS_GAR.TERR_GLOBALS,
1535: x_return_status OUT NOCOPY VARCHAR2)
1536: IS
1537:
1538: TYPE customer_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1595: WIN.full_access_flag;
1596:
1597:
1598: BEGIN
1599: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_START);
1600: x_return_status := FND_API.G_RET_STS_SUCCESS;
1601: l_worker_id:=p_terr_globals.worker_id;
1602: l_var :=p_terr_globals.bulk_size;
1603: l_MAX_fetches := p_terr_globals.cursor_limit;
1611: l_part_cont_party_id.DELETE;
1612: l_faf.DELETE;
1613:
1614: l_loop_count := l_loop_count + 1;
1615: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || 'LOOPCOUNT :- ' || l_loop_count);
1616:
1617: --------------------------------
1618: OPEN team_leader(l_worker_id);
1619: FETCH team_leader BULK COLLECT INTO
1629:
1630: IF l_customer_id.COUNT < l_MAX_fetches THEN
1631: l_limit_flag := TRUE;
1632: END IF;
1633: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
1634:
1635: IF l_customer_id.COUNT > 0 THEN
1636: l_flag := TRUE;
1637: l_first := l_customer_id.FIRST;
1662: AND (NVL(ACC.partner_customer_id,-777)= NVL(l_part_cust_id(i),-777)
1663: OR NVL(ACC.partner_cont_party_id,-777)=NVL(l_part_cont_party_id(i),-777));
1664: COMMIT;
1665: l_attempts := 3;
1666: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS ||AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
1667: EXCEPTION
1668: WHEN DEADLOCK_DETECTED THEN
1669: BEGIN
1670: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_DEADLOCK ||l_attempts);
1666: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS ||AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_BULK_UPD || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-'|| l_last);
1667: EXCEPTION
1668: WHEN DEADLOCK_DETECTED THEN
1669: BEGIN
1670: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_DEADLOCK ||l_attempts);
1671: ROLLBACK;
1672: l_attempts := l_attempts +1;
1673: IF l_attempts = 3 THEN
1674: FOR i IN l_first .. l_last
1693: AND (NVL(ACC.partner_customer_id,-777)= NVL(l_part_cust_id(i),-777)
1694: OR NVL(ACC.partner_cont_party_id,-777)=NVL(l_part_cont_party_id(i),-777));
1695: EXCEPTION
1696: WHEN OTHERS THEN
1697: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
1698: AS_GAR.LOG('CUSTOMER_ID/SALESFORCE_ID/SALESGROUP_ID/ORG_ID/PRM_CUST_ID/PRM_CUST_CONT_ID - ' || l_customer_id(i) || '/' || l_salesforce_id(i) || '/' || l_sales_group_id(i) || '/' || '/' || l_part_cust_id(i) || '/' || l_part_cont_party_id(i));
1699: END;
1700: END LOOP; -- for each record individually
1701: COMMIT;
1694: OR NVL(ACC.partner_cont_party_id,-777)=NVL(l_part_cont_party_id(i),-777));
1695: EXCEPTION
1696: WHEN OTHERS THEN
1697: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
1698: AS_GAR.LOG('CUSTOMER_ID/SALESFORCE_ID/SALESGROUP_ID/ORG_ID/PRM_CUST_ID/PRM_CUST_CONT_ID - ' || l_customer_id(i) || '/' || l_salesforce_id(i) || '/' || l_sales_group_id(i) || '/' || '/' || l_part_cust_id(i) || '/' || l_part_cont_party_id(i));
1699: END;
1700: END LOOP; -- for each record individually
1701: COMMIT;
1702: END IF;
1701: COMMIT;
1702: END IF;
1703: END; -- end of deadlock exception
1704: WHEN OTHERS THEN
1705: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_BULK_UPD, SQLERRM, TO_CHAR(SQLCODE));
1706: x_errbuf := SQLERRM;
1707: x_retcode := SQLCODE;
1708: x_return_status := FND_API.G_RET_STS_ERROR;
1709: END;
1715: l_flag := FALSE;
1716: END IF;
1717: END LOOP; -- loop for more records within the bulk_size
1718: END IF; --l_customer_id.count > 0
1719: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_END);
1720: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_N_ROWS_PROCESSED || l_customer_id.COUNT);
1721: END LOOP; -- loop for more bulk_size fetches
1722: l_customer_id.DELETE;
1723: l_salesforce_id.DELETE;
1716: END IF;
1717: END LOOP; -- loop for more records within the bulk_size
1718: END IF; --l_customer_id.count > 0
1719: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_END);
1720: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::' || AS_GAR.G_N_ROWS_PROCESSED || l_customer_id.COUNT);
1721: END LOOP; -- loop for more bulk_size fetches
1722: l_customer_id.DELETE;
1723: l_salesforce_id.DELETE;
1724: l_sales_group_id.DELETE;
1726: l_part_cont_party_id.DELETE;
1727: l_faf.DELETE;
1728: EXCEPTION
1729: WHEN OTHERS THEN
1730: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'PRM::', SQLERRM, TO_CHAR(SQLCODE));
1731: x_errbuf := SQLERRM;
1732: x_retcode := SQLCODE;
1733: x_return_status := FND_API.G_RET_STS_ERROR;
1734: END SET_TEAM_LEAD_PRM_ACCOUNTS;
1739:
1740: PROCEDURE INSERT_ACCESSES_PRM_ACCOUNTS(
1741: x_errbuf OUT NOCOPY VARCHAR2,
1742: x_retcode OUT NOCOPY VARCHAR2,
1743: p_terr_globals IN AS_GAR.TERR_GLOBALS,
1744: x_return_status OUT NOCOPY VARCHAR2)
1745: IS
1746: TYPE customer_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1747: TYPE org_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1823: | Re-Initialize variables and null out if necessary.
1824: | Try bulk inserting into accesses. If this fails, insert records one by one.
1825: |
1826: +-------------------------------------------------------------------------*/
1827: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::' || AS_GAR.G_START);
1828: x_return_status := FND_API.G_RET_STS_SUCCESS;
1829: l_worker_id:=p_terr_globals.worker_id;
1830: l_var :=p_terr_globals.bulk_size;
1831: OPEN ins_rs_partner(l_worker_id);
1843:
1844: EXIT WHEN ins_rs_partner%NOTFOUND;
1845:
1846: l_loop_count := l_loop_count + 1;
1847: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::' || 'LOOPCOUNT :- ' || l_loop_count);
1848:
1849: FETCH ins_rs_partner BULK COLLECT INTO
1850: l_salesforce_id, l_sales_group_id, l_person_id,
1851: l_customer_id,l_address_id,l_faf,l_org_id,l_resource_type,l_src
1868: IF l_last > l_customer_id.last THEN
1869: l_last := l_customer_id.last;
1870: END IF;
1871: BEGIN
1872: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::' || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' to '||l_last);
1873: FORALL i IN l_first .. l_last
1874: INSERT INTO AS_ACCESSES_ALL_ALL
1875: ( access_id
1876: ,access_type
1921: ,DECODE(l_resource_type(i),'RS_PARTNER',l_src(i),NULL)
1922: ,DECODE(l_resource_type(i),'RS_PARTY',l_src(i),NULL)
1923: FROM DUAL
1924: );
1925: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::' || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1926: COMMIT;
1927: EXCEPTION
1928: WHEN DUP_VAL_ON_INDEX THEN
1929: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::' || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1925: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::' || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
1926: COMMIT;
1927: EXCEPTION
1928: WHEN DUP_VAL_ON_INDEX THEN
1929: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::' || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
1930: FOR i IN l_first .. l_last LOOP
1931: BEGIN
1932: INSERT INTO AS_ACCESSES_ALL_ALL
1933: ( access_id
1986: END;
1987: END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
1988: COMMIT;
1989: WHEN OTHERS THEN
1990: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::' || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1991: x_errbuf := SQLERRM;
1992: x_retcode := SQLCODE;
1993: x_return_status := FND_API.G_RET_STS_ERROR;
1994: END;
2012: IF ins_rs_partner%ISOPEN THEN CLOSE ins_rs_partner; END IF;
2013:
2014: EXCEPTION
2015: WHEN others THEN
2016: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'PRM::', SQLERRM, TO_CHAR(SQLCODE));
2017: x_errbuf := SQLERRM;
2018: x_retcode := SQLCODE;
2019: x_return_status := FND_API.G_RET_STS_ERROR;
2020: IF ins_rs_partner%ISOPEN THEN CLOSE ins_rs_partner; END IF;
2026:
2027: PROCEDURE INSERT_TERR_ACCESSES_PRM_ACCS(
2028: x_errbuf OUT NOCOPY VARCHAR2,
2029: x_retcode OUT NOCOPY VARCHAR2,
2030: p_terr_globals IN AS_GAR.TERR_GLOBALS,
2031: x_return_status OUT NOCOPY VARCHAR2)
2032: IS
2033:
2034: TYPE num_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
2077: | Almost the same as accesses, except the insertion is into as_territory_accesses
2078: | and there is no involvement of role.
2079: |
2080: +-------------------------------------------------------------------------*/
2081: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_START);
2082: x_return_status := FND_API.G_RET_STS_SUCCESS;
2083: l_worker_id := p_terr_globals.worker_id;
2084: l_var := p_terr_globals.bulk_size;
2085: OPEN ins_tacc_rs_partner(l_worker_id);
2092: l_customer_id.DELETE;
2093: l_salesforce_id.DELETE;
2094: l_sales_group_id.DELETE;
2095:
2096: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || 'LOOPCOUNT :- ' || l_loop_count);
2097: BEGIN
2098: FETCH ins_tacc_rs_partner BULK COLLECT INTO l_terr_id,l_customer_id,l_salesforce_id,l_sales_group_id
2099: LIMIT l_MAX_fetches;
2100: -- Initialize variables
2111: IF l_last > l_terr_id.LAST THEN
2112: l_last := l_terr_id.LAST;
2113: END IF;
2114: BEGIN
2115: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
2116: FORALL i IN l_first .. l_last
2117: INSERT INTO AS_TERRITORY_ACCESSES
2118: ( access_id,
2119: territory_id,
2158: WHERE AST.access_id = A.access_id
2159: AND AST.territory_id = l_terr_id(i))
2160: ) V
2161: );
2162: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
2163: COMMIT;
2164: EXCEPTION
2165: WHEN DUP_VAL_ON_INDEX THEN
2166: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
2162: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
2163: COMMIT;
2164: EXCEPTION
2165: WHEN DUP_VAL_ON_INDEX THEN
2166: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
2167: FOR i IN l_first .. l_last LOOP
2168: BEGIN
2169: INSERT INTO AS_TERRITORY_ACCESSES
2170: ( access_id,
2215: WHEN OTHERS THEN
2216: NULL;
2217: END;
2218: END LOOP;
2219: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
2220: COMMIT;
2221: WHEN Others THEN
2222: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
2223: x_errbuf := SQLERRM;
2218: END LOOP;
2219: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED || SQL%ROWCOUNT);
2220: COMMIT;
2221: WHEN Others THEN
2222: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
2223: x_errbuf := SQLERRM;
2224: x_retcode := SQLCODE;
2225: x_return_status := FND_API.G_RET_STS_ERROR;
2226: IF ins_tacc_rs_partner%ISOPEN THEN CLOSE ins_tacc_rs_partner; END IF;
2233: END LOOP;
2234: END IF; --l_access_id.count > 0
2235: EXCEPTION
2236: WHEN Others THEN
2237: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' , SQLERRM, TO_CHAR(SQLCODE));
2238: IF ins_tacc_rs_partner%ISOPEN THEN CLOSE ins_tacc_rs_partner; END IF;
2239: x_errbuf := SQLERRM;
2240: x_retcode := SQLCODE;
2241: x_return_status := FND_API.G_RET_STS_ERROR;
2246: l_customer_id.DELETE;
2247: l_salesforce_id.DELETE;
2248: l_sales_group_id.DELETE;
2249: IF ins_tacc_rs_partner%ISOPEN THEN CLOSE ins_tacc_rs_partner; END IF;
2250: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_END);
2251: EXCEPTION
2252: WHEN others THEN
2253: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::', SQLERRM, TO_CHAR(SQLCODE));
2254: x_errbuf := SQLERRM;
2249: IF ins_tacc_rs_partner%ISOPEN THEN CLOSE ins_tacc_rs_partner; END IF;
2250: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::' || AS_GAR.G_END);
2251: EXCEPTION
2252: WHEN others THEN
2253: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'PRM::', SQLERRM, TO_CHAR(SQLCODE));
2254: x_errbuf := SQLERRM;
2255: x_retcode := SQLCODE;
2256: x_return_status := FND_API.G_RET_STS_ERROR;
2257: IF ins_tacc_rs_partner%ISOPEN THEN CLOSE ins_tacc_rs_partner; END IF;
2264:
2265: PROCEDURE Perform_Account_Cleanup(
2266: x_errbuf OUT NOCOPY VARCHAR2,
2267: x_retcode OUT NOCOPY VARCHAR2,
2268: p_terr_globals IN AS_GAR.TERR_GLOBALS,
2269: x_return_status OUT NOCOPY VARCHAR2)
2270: IS
2271:
2272: TYPE customer_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
2301: FROM JTF_TAE_1001_ACCOUNT_NM_TRANS
2302: WHERE worker_id=c_worker_id;
2303:
2304: BEGIN
2305: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_START);
2306:
2307: x_return_status := FND_API.G_RET_STS_SUCCESS;
2308: l_worker_id := p_terr_globals.worker_id;
2309: l_var := p_terr_globals.bulk_size;
2307: x_return_status := FND_API.G_RET_STS_SUCCESS;
2308: l_worker_id := p_terr_globals.worker_id;
2309: l_var := p_terr_globals.bulk_size;
2310: l_MAX_fetches := p_terr_globals.cursor_limit;
2311: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'Bulk Size'|| l_var || 'Cursor Limit' || l_MAX_fetches);
2312: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2313: OPEN del_acct_totalmode(l_worker_id);
2314: ELSE
2315: OPEN del_acct_newmode(l_worker_id);
2308: l_worker_id := p_terr_globals.worker_id;
2309: l_var := p_terr_globals.bulk_size;
2310: l_MAX_fetches := p_terr_globals.cursor_limit;
2311: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'Bulk Size'|| l_var || 'Cursor Limit' || l_MAX_fetches);
2312: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2313: OPEN del_acct_totalmode(l_worker_id);
2314: ELSE
2315: OPEN del_acct_newmode(l_worker_id);
2316: END IF;
2317: LOOP --{L1
2318: IF (l_limit_flag) THEN EXIT; END IF;
2319:
2320: l_loop_count := l_loop_count + 1;
2321: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'LOOPCOUNT :- ' ||l_loop_count);
2322: BEGIN
2323: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2324: EXIT WHEN del_acct_totalmode%NOTFOUND;
2325: FETCH del_acct_totalmode BULK COLLECT INTO l_customer_id
2319:
2320: l_loop_count := l_loop_count + 1;
2321: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'LOOPCOUNT :- ' ||l_loop_count);
2322: BEGIN
2323: IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
2324: EXIT WHEN del_acct_totalmode%NOTFOUND;
2325: FETCH del_acct_totalmode BULK COLLECT INTO l_customer_id
2326: LIMIT l_MAX_fetches;
2327: ELSE
2338: IF l_customer_id.COUNT < l_MAX_fetches THEN
2339: l_limit_flag := TRUE;
2340: END IF;
2341:
2342: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_START);
2343: IF l_customer_id.count > 0 THEN --{I1
2344: l_flag := TRUE;
2345: l_first := l_customer_id.first;
2346: l_last := l_first + l_var;
2343: IF l_customer_id.count > 0 THEN --{I1
2344: l_flag := TRUE;
2345: l_first := l_customer_id.first;
2346: l_last := l_first + l_var;
2347: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED ||
2348: l_customer_id.FIRST || '-' ||
2349: l_customer_id.LAST);
2350: WHILE l_flag LOOP --{L2 10K cust loop
2351: IF l_last > l_customer_id.LAST THEN
2356: IF (l_del_flag) THEN EXIT; END IF;
2357: l_del_flag := FALSE;
2358: WHILE l_attempts < 3 LOOP --{L4
2359: BEGIN
2360: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
2361: FORALL i in l_first..l_last
2362: UPDATE AS_ACCESSES_ALL_ALL ACC
2363: SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
2364: WHERE ACC.customer_id=l_customer_id(i)
2380: IF l_customer_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
2381: EXCEPTION
2382: WHEN DUP_VAL_ON_INDEX THEN
2383: BEGIN
2384: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_DEL || AS_GAR.G_START);
2385: FORALL i in l_first..l_last
2386: DELETE FROM AS_ACCESSES_ALL_ALL ACC
2387: WHERE ACC.customer_id=l_customer_id(i)
2388: AND NVL(ACC.freeze_flag,'N') = 'N'
2402: l_attempts := 3;
2403: IF l_customer_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
2404: EXCEPTION
2405: WHEN OTHERS THEN
2406: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_DEL, SQLERRM, TO_CHAR(SQLCODE));
2407: END;
2408: WHEN deadlock_detected THEN
2409: BEGIN --{I2
2410: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_UPD || AS_GAR.G_DEADLOCK || l_attempts);
2406: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_DEL, SQLERRM, TO_CHAR(SQLCODE));
2407: END;
2408: WHEN deadlock_detected THEN
2409: BEGIN --{I2
2410: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_UPD || AS_GAR.G_DEADLOCK || l_attempts);
2411: ROLLBACK;
2412: l_attempts := l_attempts +1;
2413: IF l_attempts = 3 THEN
2414: FOR i IN l_first .. l_last LOOP --{L5
2412: l_attempts := l_attempts +1;
2413: IF l_attempts = 3 THEN
2414: FOR i IN l_first .. l_last LOOP --{L5
2415: BEGIN
2416: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_UPD || AS_GAR.G_START);
2417: UPDATE AS_ACCESSES_ALL_ALL ACC
2418: SET object_version_number = NVL(object_version_number,0) + 1, ACC.DELETE_FLAG='Y'
2419: WHERE ACC.customer_id=l_customer_id(i)
2420: AND ACC.delete_flag is NULL
2430: COMMIT;
2431: EXCEPTION
2432: WHEN DUP_VAL_ON_INDEX THEN
2433: BEGIN
2434: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_DEL || AS_GAR.G_START);
2435: DELETE FROM AS_ACCESSES_ALL_ALL ACC
2436: WHERE ACC.customer_id=l_customer_id(i)
2437: AND NVL(ACC.freeze_flag,'N') = 'N'
2438: AND ACC.delete_flag is NULL
2447: COMMIT;
2448:
2449: EXCEPTION
2450: WHEN OTHERS THEN
2451: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_DEL || AS_GAR.G_GENERAL_EXCEPTION);
2452: AS_GAR.LOG('CUSTOMER_ID - ' || l_customer_id(i));
2453: END;
2454: END;
2455: END LOOP; --}L5
2448:
2449: EXCEPTION
2450: WHEN OTHERS THEN
2451: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_DEL || AS_GAR.G_GENERAL_EXCEPTION);
2452: AS_GAR.LOG('CUSTOMER_ID - ' || l_customer_id(i));
2453: END;
2454: END;
2455: END LOOP; --}L5
2456: COMMIT;
2458: END IF;
2459: l_attempts := 3;
2460: END; --}I2 end of deadlock exception
2461: WHEN OTHERS THEN
2462: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2463: IF del_acct_totalmode%ISOPEN THEN CLOSE del_acct_totalmode; END IF;
2464: IF del_acct_newmode%ISOPEN THEN CLOSE del_acct_newmode; END IF;
2465: x_errbuf := SQLERRM;
2466: x_retcode := SQLCODE;
2466: x_retcode := SQLCODE;
2467: x_return_status := FND_API.G_RET_STS_ERROR;
2468: END;
2469: END LOOP; --}L4 l_attempts loop 3 trys
2470: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-' || l_last);
2471: l_first := l_last + 1;
2472: l_last := l_first + l_var;
2473: IF l_first > l_customer_id.LAST THEN
2474: l_flag := FALSE;
2474: l_flag := FALSE;
2475: END IF;
2476: END LOOP; --}L2 while l_flag loop (10K cust loop)
2477: END IF;--}I1
2478: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
2479: COMMIT;
2480: EXCEPTION
2481: WHEN Others THEN
2482: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2478: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
2479: COMMIT;
2480: EXCEPTION
2481: WHEN Others THEN
2482: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2483: IF del_acct_totalmode%ISOPEN THEN CLOSE del_acct_totalmode; END IF;
2484: IF del_acct_newmode%ISOPEN THEN CLOSE del_acct_newmode; END IF;
2485: x_errbuf := SQLERRM;
2486: x_retcode := SQLCODE;
2490: IF del_acct_totalmode%ISOPEN THEN CLOSE del_acct_totalmode; END IF;
2491: IF del_acct_newmode%ISOPEN THEN CLOSE del_acct_newmode; END IF;
2492: EXCEPTION
2493: WHEN OTHERS THEN
2494: AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
2495: x_errbuf := SQLERRM;
2496: x_retcode := SQLCODE;
2497: x_return_status := FND_API.G_RET_STS_ERROR;
2498: END Perform_Account_Cleanup;
2498: END Perform_Account_Cleanup;
2499:
2500: /************************** End Account Cleanup ***********************/
2501:
2502: END AS_GAR_ACCOUNTS_PUB;
2503: