[Home] [Help]
PACKAGE BODY: APPS.AS_GAR_PROPOSALS_PUB
Source
1 PACKAGE BODY AS_GAR_PROPOSALS_PUB AS
2 /* $Header: asxgrppb.pls 120.11 2006/02/02 21:30 amagupta noship $ */
3 ---------------------------------------------------------------------------
4 -- Start of Comments
5 ---------------------------------------------------------------------------
6 -- PACKAGE NAME: AS_GAR_PROPOSALS_PUB
7 -- ---------------------------------------------------------------------
8 -- PURPOSE
9 -- --------
10 -- This package contains procedures to accomplish each of the following
11 -- tasks:
12 -- 1: Call the JTY API to process data from JTY trans tables and
13 -- populate JTY winners.
14 -- 2: Merge and insert records from winners into PRP_PROPOSAL_ACCESSES
15 -- 3: Soft Delete unwanted records from PRP_PROPOSAL_ACCESSES
16 --
17 -- NOTE: No Owner Assignment and Cleanup steps required for PROPOSALS
18 ---------------------------------------------------------------------------
19 /*-------------------------------------------------------------------------+
20 | PRIVATE CONSTANTS
21 +-------------------------------------------------------------------------*/
22 G_BUSINESS_EVENT CONSTANT VARCHAR2(60) := 'oracle.apps.as.tap.batch_mode';
23 DEADLOCK_DETECTED EXCEPTION;
24 PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
25 G_ENTITY CONSTANT VARCHAR2(17) := 'GAR::PROPOSALS::';
26 /*-------------------------------------------------------------------------*
27 | PRIVATE VARIABLES
28 *-------------------------------------------------------------------------*/
29 /*-------------------------------------------------------------------------*
30 | PRIVATE ROUTINES SPECIFICATION
31 *-------------------------------------------------------------------------*/
32 /*------------------------------------------------------------------------*
33 | PUBLIC ROUTINES
34 *------------------------------------------------------------------------*/
35
36 /************************** Start GAR Wrapper *****************************/
37 PROCEDURE GAR_WRAPPER(
38 errbuf OUT NOCOPY VARCHAR2,
39 retcode OUT NOCOPY VARCHAR2,
40 p_run_mode IN VARCHAR2,
41 p_debug_mode IN VARCHAR2,
42 p_trace_mode IN VARCHAR2,
43 p_worker_id IN VARCHAR2 ,
44 P_percent_analyzed IN NUMBER )
45 IS
46 l_terr_globals AS_GAR.TERR_GLOBALS;
47 l_msg_count NUMBER;
48 l_msg_data VARCHAR2(2000);
49 l_errbuf VARCHAR2(4000);
50 l_retcode VARCHAR2(255);
51 l_sub_exist VARCHAR2(1); -- ?
52 l_return_status VARCHAR2(1);
53 l_target_type VARCHAR2(15); -- ? not sure we need this
54 l_status BOOLEAN;
55 l_proc VARCHAR2(30):= 'GAR_WRAPPER::';
56 BEGIN
57 AS_GAR.g_debug_flag := p_debug_mode;
58 IF p_trace_mode = 'Y' THEN AS_GAR.SETTRACE; END IF;
59 AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_START);
60
61 IF p_run_mode = AS_GAR.G_TOTAL_MODE THEN
62 l_target_type := 'TOTAL';
63 ELSIF p_run_mode = AS_GAR.G_NEW_MODE THEN
64 l_target_type := 'INCREMENTAL';
65 END If;
66
67 -- Set the Global variables
68 AS_GAR.INIT(
69 p_run_mode,
70 p_worker_id,
71 l_terr_globals);
72
73 /* This inserts into proposal winners */
74 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_START);
75 JTY_ASSIGN_BULK_PUB.GET_WINNERS
76 ( p_api_version_number => 1.0,
77 p_init_msg_list => FND_API.G_TRUE,
78 p_source_id => -1001,
79 p_trans_id => -1106,
80 P_PROGRAM_NAME => 'SALES/PROPOSAL PROGRAM',
81 P_mode => l_target_type,
82 P_percent_analyzed => NVL(P_percent_analyzed,20),
83 p_worker_id => p_worker_id,
84 x_return_status => l_return_status,
85 x_msg_count => l_msg_count,
86 x_msg_data => l_msg_data,
87 ERRBUF => l_errbuf,
88 RETCODE => l_retcode);
89 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_END);
90 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW || AS_GAR.G_RETURN_STATUS || l_return_status);
91
92 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
93 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CW, l_errbuf, l_retcode);
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 End If;
96
97 COMMIT;
98
99 -- Explode GROUPS if any inside winners
100 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_START);
101 AS_GAR_PROPOSALS_PUB.EXPLODE_GROUPS_PROPOSALS(
102 x_errbuf => l_errbuf,
103 x_retcode => l_retcode,
104 p_terr_globals => l_terr_globals,
105 x_return_status => l_return_status);
106
107 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_END);
108 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS || AS_GAR.G_RETURN_STATUS || l_return_status);
109
110 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
111 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_GROUPS, l_errbuf, l_retcode);
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 End If;
114
115 COMMIT;
116
117 -- Explode TEAMS if any inside winners
118 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
119 AS_GAR_PROPOSALS_PUB.EXPLODE_TEAMS_PROPOSALS(
120 x_errbuf => l_errbuf,
121 x_retcode => l_retcode,
122 p_terr_globals => l_terr_globals,
123 x_return_status => l_return_status);
124
125 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_END);
126 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS || AS_GAR.G_RETURN_STATUS || l_return_status);
127
128 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
129 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CEX_TEAMS, l_errbuf, l_retcode);
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 End If;
132 COMMIT;
133 -- Set team leader for Accounts
134 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_START);
135 AS_GAR_PROPOSALS_PUB.SET_TEAM_LEAD_PROPOSALS(
136 x_errbuf => l_errbuf,
137 x_retcode => l_retcode,
138 p_terr_globals => l_terr_globals,
139 x_return_status => l_return_status);
140
141 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_END);
142 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD || AS_GAR.G_RETURN_STATUS || l_return_status);
143
144 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
145 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_STLEAD, l_errbuf, l_retcode);
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 End If;
148
149 -- Insert into proposal Accesses from Winners
150 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_START);
151 AS_GAR_PROPOSALS_PUB.INSERT_ACCESSES_PROPOSALS(
152 x_errbuf => l_errbuf,
153 x_retcode => l_retcode,
154 p_terr_globals => l_terr_globals,
155 x_return_status => l_return_status);
156
157 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_END);
158 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC || AS_GAR.G_RETURN_STATUS || l_return_status);
159
160 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
161 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSACC, l_errbuf, l_retcode);
162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
163 End If;
164
165 -- Insert into territory Accesses
166 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
167 AS_GAR_PROPOSALS_PUB.INSERT_TERR_ACCESSES_PROPOSALS(
168 x_errbuf => l_errbuf,
169 x_retcode => l_retcode,
170 p_terr_globals => l_terr_globals,
171 x_return_status => l_return_status);
172
173 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
174 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC || AS_GAR.G_RETURN_STATUS || l_return_status);
175
176 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
177 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_INSTERRACC, l_errbuf, l_retcode);
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 End If;
180
181
182 -- Remove (soft delete) records in access table that are not qualified
183 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_START);
184 AS_GAR_PROPOSALS_PUB.Perform_Proposal_Cleanup(
185 x_errbuf => l_errbuf,
186 x_retcode => l_retcode,
187 p_terr_globals => l_terr_globals,
188 x_return_status => l_return_status);
189
190 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_END);
191 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC || AS_GAR.G_RETURN_STATUS || l_return_status);
192
193 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
194 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_CALL_TO || AS_GAR.G_CC, l_errbuf, l_retcode);
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 End If;
197
198 -- BES enhancement
199
200 l_sub_exist := AS_GAR.exist_subscription(G_BUSINESS_EVENT);
201 IF l_sub_exist = 'Y' THEN
202 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_EXISTS);
203 AS_GAR.LOG(G_ENTITY || AS_GAR.G_CBE_RAISE);
204 AS_GAR.Raise_BE(l_terr_globals);
205 END If;
206
207 AS_GAR.LOG(G_ENTITY || l_proc || AS_GAR.G_END);
208 EXCEPTION
209 WHEN OTHERS THEN
210 AS_GAR.LOG_EXCEPTION(G_ENTITY, SQLERRM, TO_CHAR(SQLCODE));
211 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
212 END GAR_WRAPPER;
213
214 /************************** End GAR Wrapper *****************************/
215
216 /************************** Start Explode Teams PROPOSALS ******************/
217 PROCEDURE EXPLODE_TEAMS_PROPOSALS(
218 x_errbuf OUT NOCOPY VARCHAR2,
219 x_retcode OUT NOCOPY VARCHAR2,
220 p_terr_globals IN AS_GAR.TERR_GLOBALS,
221 x_return_status OUT NOCOPY VARCHAR2)
222 IS
223
224 /*-------------------------------------------------------------------------+
225 | LOGIC
226 |
227 | A RESOURCE team can be comprised OF resources who belong TO one OR more
228 | GROUPS OF resources.
229 | So get a LIST OF team members (OF TYPE employee OR partner OR parter contact
230 | AND play a ROLE OF salesrep ) AND get atleast one GROUP id that they belong TO
231 | WHERE they play a similar ROLE.
232 | UNION THE above WITH a LIST OF ALL members OF ALL GROUPS which BY themselves
233 | are a RESOURCE within a team.
234 | INSERT these members INTO winners IF they are NOT already IN winners.
235 +-------------------------------------------------------------------------*/
236
237 l_errbuf VARCHAR2(4000);
238 l_retcode VARCHAR2(255);
239 l_res_type_count NUMBER;
240 l_res_prop_count NUMBER;
241 l_resource_type VARCHAR2(10);
242 l_request_id NUMBER;
243 l_worker_id NUMBER;
244
245 CURSOR c_get_res_type_count(c_resource_type VARCHAR2, c_request_id NUMBER, c_worker_id NUMBER)
246 IS
247 SELECT count(*)
248 FROM JTF_TAE_1001_PROP_WINNERS
249 WHERE request_id = c_request_id
250 AND resource_type = c_resource_type
251 AND worker_id = c_worker_id
252 AND ROWNUM < 2;
253
254 CURSOR count_res_proposal
255 IS
256 SELECT count(*)
257 FROM JTF_TERR_RSC_ALL rsc,
258 JTF_TERR_DENORM_RULES_ALL rules,
259 JTF_TERR_RSC_ACCESS_ALL acc
260 WHERE rules.terr_id = rsc.terr_id
261 AND rsc.resource_type = 'RS_TEAM'
262 AND acc.access_type = 'PROPOSAL'
263 AND rules.source_id = -1001
264 AND rsc.terr_rsc_id = acc.terr_rsc_id;
265
266 BEGIN
267 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_START);
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269 l_request_id := p_terr_globals.request_id;
270 l_worker_id := p_terr_globals.worker_id;
271 l_resource_type := 'RS_TEAM';
272
273 OPEN count_res_proposal;
274 FETCH count_res_proposal INTO l_res_prop_count;
275 CLOSE count_res_proposal;
276
277 IF l_res_prop_count > 0 THEN
278 OPEN c_get_res_type_count(l_resource_type, l_request_id, l_worker_id);
279 FETCH c_get_res_type_count INTO l_res_type_count;
280 CLOSE c_get_res_type_count;
281 END IF;
282
283 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
284 IF l_res_type_count > 0 THEN
285 /* Get resources within a resource team */
286 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
287 /** Note
288 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
289 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
290 **/
291 INSERT INTO JTF_TAE_1001_PROP_WINNERS
292 (trans_object_id,
293 trans_detail_object_id,
294 terr_id,
295 resource_id,
296 resource_type,
297 group_id,
298 full_access_flag,
299 request_id,
300 program_application_id,
301 program_id,
302 program_update_date,
303 source_id,
304 trans_object_type_id,
305 last_update_date,
306 last_updated_by,
307 creation_date,
308 created_by,
309 last_update_login,
310 absolute_rank,
311 top_level_terr_id,
312 num_winners,
313 terr_rsc_id,
314 role,
315 primary_contact_flag,
316 person_id,
317 org_id,
318 worker_id)
319 SELECT T.trans_object_id,
320 T.trans_detail_object_id,
321 T.terr_id,
322 J.resource_id,
323 DECODE(J.resource_category,'PARTY','RS_PARTY',
324 'PARTNER','RS_PARTNER',
325 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
326 J.group_id,
327 T.full_access_flag,
328 T.request_id,
329 T.program_application_id,
330 T.program_id, T.program_update_date,
331 T.source_id,
332 T.trans_object_type_id,
333 SYSDATE,
334 T.last_updated_by,
335 SYSDATE,
336 T.created_by,
337 T.last_update_login,
338 T.absolute_rank,
339 T.top_level_terr_id,
340 T.num_winners,
341 T.terr_rsc_id,
342 T.role,
343 T.primary_contact_flag,
344 J.person_id,
345 T.org_id,
346 T.worker_id
347 FROM
348 JTF_TAE_1001_PROP_WINNERS T,
349 (
350 SELECT TM.team_resource_id resource_id,
351 TM.person_id person_id2,
352 MIN(G.group_id)group_id,
353 MIN(T.team_id) team_id,
354 TRES.category resource_category,
355 TRES.source_id person_id
356 FROM jtf_rs_team_members TM, jtf_rs_teams_b T,
357 jtf_rs_team_usages TU, jtf_rs_role_relations TRR,
358 jtf_rs_roles_b TR, jtf_rs_resource_extns TRES,
359 (
360 SELECT m.group_id group_id, m.resource_id resource_id
361 FROM jtf_rs_group_members m,
362 jtf_rs_groups_b g,
363 jtf_rs_group_usages u,
364 jtf_rs_role_relations rr,
365 jtf_rs_roles_b r,
366 jtf_rs_resource_extns res
367 WHERE m.group_id = g.group_id
368 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
369 AND NVL(g.end_date_active,SYSDATE)
370 AND u.group_id = g.group_id
371 AND u.usage IN ('SALES','PRM')
372 AND m.group_member_id = rr.role_resource_id
373 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
374 AND rr.delete_flag <> 'Y'
375 AND SYSDATE BETWEEN rr.start_date_active
376 AND NVL(rr.end_date_active,SYSDATE)
377 AND rr.role_id = r.role_id
378 AND r.role_type_code
379 IN ('SALES', 'TELESALES', 'FIELDSALES','PRM')
380 AND r.active_flag = 'Y'
381 AND res.resource_id = m.resource_id
382 AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
383 ) G
384 WHERE tm.team_id = t.team_id
385 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
386 AND NVL(t.end_date_active,SYSDATE)
387 AND tu.team_id = t.team_id
388 AND tu.usage IN ('SALES','PRM')
389 AND tm.team_member_id = trr.role_resource_id
390 AND tm.delete_flag <> 'Y'
391 AND tm.resource_type = 'INDIVIDUAL'
392 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
393 AND trr.delete_flag <> 'Y'
394 AND SYSDATE BETWEEN trr.start_date_active AND
395 NVL(trr.end_date_active,SYSDATE)
396 AND trr.role_id = tr.role_id
397 AND tr.role_type_code IN
398 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
399 AND tr.active_flag = 'Y'
400 AND tres.resource_id = tm.team_resource_id
401 AND tres.category IN ('EMPLOYEE','PARTY','PARTNER')
402 AND tm.team_resource_id = g.resource_id
403 GROUP BY tm.team_resource_id,
404 tm.person_id,
405 tres.category,
406 tres.source_id
407 UNION ALL
408 SELECT MIN(m.resource_id) resource_id,
409 MIN(m.person_id) person_id2, MIN(m.group_id) group_id,
410 MIN(jtm.team_id) team_id, res.category resource_category,
411 MIN(res.source_id) person_id
412 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
413 jtf_rs_group_usages u, jtf_rs_role_relations rr,
414 jtf_rs_roles_b r, jtf_rs_resource_extns res,
415 (
416 SELECT tm.team_resource_id group_id,
417 t.team_id team_id
418 FROM jtf_rs_team_members tm, jtf_rs_teams_b t,
419 jtf_rs_team_usages tu,jtf_rs_role_relations trr,
420 jtf_rs_roles_b tr, jtf_rs_resource_extns tres
421 WHERE tm.team_id = t.team_id
422 AND SYSDATE BETWEEN NVL(t.start_date_active,SYSDATE)
423 AND NVL(t.end_date_active,SYSDATE)
424 AND tu.team_id = t.team_id
425 AND tu.usage IN ('SALES','PRM')
426 AND tm.team_member_id = trr.role_resource_id
427 AND tm.delete_flag <> 'Y'
428 AND tm.resource_type = 'GROUP'
429 AND trr.role_resource_type = 'RS_TEAM_MEMBER'
430 AND trr.delete_flag <> 'Y'
431 AND SYSDATE BETWEEN trr.start_date_active
432 AND NVL(trr.end_date_active,SYSDATE)
433 AND trr.role_id = tr.role_id
434 AND tr.role_type_code IN
435 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
436 AND tr.active_flag = 'Y'
437 AND tres.resource_id = tm.team_resource_id
438 AND tres.category IN ('EMPLOYEE','PARTY','PARTNER')
439 ) jtm
440 WHERE m.group_id = g.group_id
441 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
442 AND NVL(g.end_date_active,SYSDATE)
443 AND u.group_id = g.group_id
444 AND u.usage IN ('SALES','PRM')
445 AND m.group_member_id = rr.role_resource_id
446 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
447 AND rr.delete_flag <> 'Y'
448 AND SYSDATE BETWEEN rr.start_date_active AND
449 NVL(rr.end_date_active,SYSDATE)
450 AND rr.role_id = r.role_id
451 AND r.role_type_code IN
452 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
453 AND r.active_flag = 'Y'
454 AND res.resource_id = m.resource_id
455 AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
456 AND jtm.group_id = g.group_id
457 GROUP BY m.resource_id, m.person_id,
458 jtm.team_id, res.category) J
459 WHERE j.team_id = t.resource_id
460 AND t.request_id = l_request_id
461 AND t.worker_id = l_worker_id
462 AND t.resource_type = 'RS_TEAM'
463 AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_PROP_WINNERS rt1
464 WHERE rt1.resource_id = j.resource_id
465 AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
466 AND rt1.request_id = t.request_id
467 AND rt1.worker_id = t.worker_id
468 AND rt1.trans_object_id = t.trans_object_id
469 AND NVL(rt1.trans_detail_object_id,-1) =
470 NVL(t.trans_detail_object_id,-1));
471
472 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);
473 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
474
475
476 COMMIT;
477
478 END IF; /* if l_res_type_count > 0 */
479 EXCEPTION
480 WHEN others THEN
481 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_TEAMS, SQLERRM, TO_CHAR(SQLCODE));
482 x_errbuf := SQLERRM;
483 x_retcode := SQLCODE;
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 RAISE;
486 END EXPLODE_TEAMS_PROPOSALS;
487 /************************** End Explode Teams PROPOSALS ******************/
488
489 /************************** Start Explode Groups PROPOSALS ******************/
490 PROCEDURE EXPLODE_GROUPS_PROPOSALS(
491 x_errbuf OUT NOCOPY VARCHAR2,
492 x_retcode OUT NOCOPY VARCHAR2,
493 p_terr_globals IN AS_GAR.TERR_GLOBALS,
494 x_return_status OUT NOCOPY VARCHAR2)
495 IS
496 -------------RS_GROUP---------
497 /*-------------------------------------------------------------------------+
498 | PROGRAM LOGIC
499 |
500 | FOR EACH GROUP listed AS a winner within winners, get THE members who play
501 | a sales ROLE AND are either an employee OR partner AND INSERT back INTO
502 | winners IF they are NOT already IN winners.
503 +-------------------------------------------------------------------------*/
504 l_errbuf VARCHAR2(4000);
505 l_retcode VARCHAR2(255);
506 l_res_type_count NUMBER;
507 l_res_prop_count NUMBER;
508 l_resource_type VARCHAR2(10);
509 l_request_id NUMBER;
510 l_worker_id NUMBER;
511
512 CURSOR c_get_res_type_count(c_resource_type VARCHAR2, c_request_id NUMBER, c_worker_id NUMBER)
513 IS
514 SELECT count(*)
515 FROM JTF_TAE_1001_PROP_WINNERS
516 WHERE request_id = c_request_id
517 AND resource_type = c_resource_type
518 AND worker_id = c_worker_id
519 AND ROWNUM < 2;
520
521
522 CURSOR count_res_proposal
523 IS
524 SELECT count(*)
525 FROM JTF_TERR_RSC_ALL rsc,
526 JTF_TERR_DENORM_RULES_ALL rules,
527 JTF_TERR_RSC_ACCESS_ALL acc
528 WHERE rules.terr_id = rsc.terr_id
529 AND rsc.resource_type = 'RS_GROUP'
530 AND acc.access_type = 'PROPOSAL'
531 AND rules.source_id = -1001
532 AND rsc.terr_rsc_id = acc.terr_rsc_id;
533
534 BEGIN
535 l_resource_type := 'RS_GROUP';
536 l_request_id := p_terr_globals.request_id;
537 l_worker_id := p_terr_globals.worker_id;
538
539 OPEN count_res_proposal;
540 FETCH count_res_proposal INTO l_res_prop_count;
541 CLOSE count_res_proposal;
542
543 IF l_res_prop_count > 0 THEN
544 OPEN c_get_res_type_count(l_resource_type, l_request_id, l_worker_id);
545 FETCH c_get_res_type_count INTO l_res_type_count;
546 CLOSE c_get_res_type_count;
547 END IF;
548
549 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_N_ROWS_PROCESSED || l_res_type_count);
550 IF l_res_type_count > 0 THEN
551 /* Get resources within a resource group */
552 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_START);
553 /** Note
554 Hard coding RS_EMPLOYEE INSTEAD OF resource_category IN following SQL
555 because JTA returns RS_EMPLOYEE AND NOT EMPLOYEE
556 **/
557
558 INSERT INTO JTF_TAE_1001_PROP_WINNERS
559 (trans_object_id,
560 trans_detail_object_id,
561 terr_id,
562 resource_id,
563 resource_type,
564 group_id,
565 full_access_flag,
566 request_id,
567 program_application_id,
568 program_id,
569 program_update_date,
570 source_id,
571 trans_object_type_id,
572 last_update_date,
573 last_updated_by,
574 creation_date,
575 created_by,
576 last_update_login,
577 absolute_rank,
578 top_level_terr_id,
579 num_winners,
580 terr_rsc_id,
581 role,
582 primary_contact_flag,
583 person_id,
584 org_id,
585 worker_id)
586 SELECT T.trans_object_id,
587 T.trans_detail_object_id,
588 T.terr_id,
589 J.resource_id,
590 DECODE(J.resource_category,'PARTY','RS_PARTY',
591 'PARTNER','RS_PARTNER',
592 'EMPLOYEE','RS_EMPLOYEE','UNKNOWN'),
593 J.group_id,
594 T.full_access_flag,
595 T.request_id,
596 T.program_application_id,
597 T.program_id,
598 T.program_update_date,
599 T.source_id,
600 T.trans_object_type_id,
601 SYSDATE,
602 T.last_updated_by,
603 SYSDATE,
604 T.created_by,
605 T.last_update_login,
606 T.absolute_rank,
607 T.top_level_terr_id,
608 T.num_winners,
609 T.terr_rsc_id,
610 T.role,
611 T.primary_contact_flag,
612 J.person_id,
613 T.org_id,
614 T.worker_id
615 FROM
616 JTF_TAE_1001_PROP_WINNERS t,
617 (
618 SELECT MIN(m.resource_id) resource_id,
619 res.CATEGORY resource_category,
620 m.group_id group_id, MIN(res.source_id) person_id
621 FROM jtf_rs_group_members m, jtf_rs_groups_b g,
622 jtf_rs_group_usages u, jtf_rs_role_relations rr,
623 jtf_rs_roles_b r, jtf_rs_resource_extns res
624 WHERE m.group_id = g.group_id
625 AND SYSDATE BETWEEN NVL(g.start_date_active,SYSDATE)
626 AND NVL(g.end_date_active,SYSDATE)
627 AND u.group_id = g.group_id
628 AND u.usage IN ('SALES','PRM')
629 AND m.group_member_id = rr.role_resource_id
630 AND rr.role_resource_type = 'RS_GROUP_MEMBER'
631 AND rr.role_id = r.role_id
632 AND rr.delete_flag <> 'Y'
633 AND SYSDATE BETWEEN rr.start_date_active AND
634 NVL(rr.end_date_active,SYSDATE)
635 AND r.role_type_code IN
636 ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
637 AND r.active_flag = 'Y'
638 AND res.resource_id = m.resource_id
639 AND res.category IN ('EMPLOYEE','PARTY','PARTNER')
640 GROUP BY m.group_member_id, m.resource_id, m.person_id,
641 m.group_id, res.category) j
642 WHERE j.group_id = t.resource_id
643 AND t.request_id = l_request_id
644 AND t.worker_id = l_worker_id
645 AND t.resource_type = 'RS_GROUP'
646 AND NOT EXISTS (SELECT 1 FROM JTF_TAE_1001_PROP_WINNERS rt1
647 WHERE rt1.resource_id = j.resource_id
648 AND NVL(rt1.group_id,-1) = NVL(j.group_id,-1)
649 AND rt1.request_id = t.request_id
650 AND rt1.worker_id = t.worker_id
651 AND rt1.trans_object_id = t.trans_object_id
652 AND NVL(rt1.trans_detail_object_id,-1) = NVL(t.trans_detail_object_id,-1));
653
654 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);
655 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS || AS_GAR.G_INS_WINNERS || AS_GAR.G_END);
656
657 COMMIT;
658 END IF; /* if l_res_type_count > 0 */
659
660 EXCEPTION
661 WHEN others THEN
662 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CEX_GROUPS, SQLERRM, TO_CHAR(SQLCODE));
663 x_errbuf := SQLERRM;
664 x_retcode := SQLCODE;
665 x_return_status := FND_API.G_RET_STS_ERROR;
666 RAISE;
667 END EXPLODE_GROUPS_PROPOSALS;
668 /************************** End Explode Groups PROPOSALS ******************/
669
670 /************************** Start Set Accounts Team Leader *****************/
671
672 /*-------------------------------------------------------------------------+
673 | PROGRAM LOGIC
674 |
675 | Winners table records are striped by worker id.
676 | All the logic pertains to what happens within a single worker.
677 | Get a list of resources who are marked as full access in winners but are
678 | not marked as full access in accesses (CURSOR team_leader).
679 | Loop within the worker for sets of records within winners ---?
680 | Bulk collect from team_leader cursor into array.
681 | Break up the array into batches based on global var bulk_size.
682 | For each batch:
683 | Try 3 times to bulk update acesses
684 | if all 3 attempts fail because of deadlock:
685 | Update on record at a time.
686 +-------------------------------------------------------------------------*/
687
688 PROCEDURE SET_TEAM_LEAD_PROPOSALS(
689 x_errbuf OUT NOCOPY VARCHAR2,
690 x_retcode OUT NOCOPY VARCHAR2,
691 p_terr_globals IN AS_GAR.TERR_GLOBALS,
692 x_return_status OUT NOCOPY VARCHAR2)
693 IS
694
695 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
696 TYPE faf_list is TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER;
697
698 l_proposal_id num_list;
699 l_resource_id num_list;
700 l_resource_group_id num_list;
701 l_faf faf_list;
702 l_var NUMBER;
703 l_worker_id NUMBER;
704 l_limit_flag BOOLEAN := FALSE;
705 l_MAX_fetches NUMBER := 10000;
706 l_loop_count NUMBER := 0;
707 l_flag BOOLEAN;
708 l_first NUMBER;
709 l_last NUMBER;
710 l_attempts NUMBER := 0;
711
712 CURSOR team_leader(c_worker_id number) IS
713 SELECT A.proposal_id, -- The use nested loop hint is removed in ACCOUNTS ONLY..
714 A.resource_id,
715 A.resource_group_id,
716 decode(NVL(WIN.full_access_flag,'N'),'Y','FULL','READ') full_access_flag
717 FROM PRP_PROPOSAL_ACCESSES A,
718 JTF_TAE_1001_PROP_WINNERS WIN
719 WHERE NVL(A.ACCESS_LEVEL,'N') <> decode(NVL(WIN.full_access_flag,'N'),'Y','FULL','READ')
720 AND WIN.SOURCE_ID = -1001
721 AND WIN.worker_id = c_worker_id
722 AND WIN.resource_type IN ('RS_EMPLOYEE','RS_PARTNER','RS_PARTY')
723 AND WIN.trans_object_id = A.proposal_id
724 AND WIN.resource_id = A.resource_id
725 AND NVL(WIN.group_id,-777) = NVL(A.resource_group_id,-777)
726 GROUP BY A.proposal_id,
727 A.resource_id,
728 A.resource_group_id,
729 WIN.full_access_flag;
730
731
732 BEGIN
733 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_START);
734 x_return_status := FND_API.G_RET_STS_SUCCESS;
735 l_worker_id:=p_terr_globals.worker_id;
736 l_var :=p_terr_globals.bulk_size;
737 l_MAX_fetches := p_terr_globals.cursor_limit;
738 LOOP -- For l_limit_flag
739 IF (l_limit_flag) THEN EXIT; END IF;
740
741 l_proposal_id.DELETE;
742 l_resource_id.DELETE;
743 l_resource_group_id.DELETE;
744 l_faf.DELETE;
745 l_loop_count := l_loop_count + 1;
746 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || 'LOOPCOUNT :- ' || l_loop_count);
747
748 --------------------------------
749 OPEN team_leader(l_worker_id);
750 FETCH team_leader BULK COLLECT INTO
751 l_proposal_id, l_resource_id, l_resource_group_id, l_faf
752 LIMIT l_MAX_fetches;
753 CLOSE team_leader;
754
755 -- Initialize variables
756 l_flag := TRUE;
757 l_first := 0;
758 l_last := 0;
759 l_attempts := 1;
760
761 IF l_proposal_id.COUNT < l_MAX_fetches THEN
762 l_limit_flag := TRUE;
763 END IF;
764 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
765
766 IF l_proposal_id.COUNT > 0 THEN
767 l_flag := TRUE;
768 l_first := l_proposal_id.FIRST;
769 l_last := l_first + l_var;
770 WHILE l_flag LOOP
771 IF l_last > l_proposal_id.LAST THEN
772 l_last := l_proposal_id.LAST;
773 END IF;
774 WHILE l_attempts < 3 LOOP
775 BEGIN
776 FORALL i IN l_first .. l_last
777 UPDATE PRP_PROPOSAL_ACCESSES ACC
778 SET object_version_number = NVL(object_version_number,0) + 1,
779 ACC.last_update_date = SYSDATE,
780 ACC.last_updated_by = p_terr_globals.user_id,
781 ACC.last_update_login = p_terr_globals.last_update_login,
782 ACC.ACCESS_LEVEL = l_faf(i)
783 WHERE ACC.proposal_id = l_proposal_id(i)
784 AND ACC.resource_id = l_resource_id(i)
785 AND NVL(ACC.resource_group_id,-777) = NVL(l_resource_group_id(i),-777);
786 COMMIT;
787 l_attempts := 3;
788 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);
789 EXCEPTION
790 WHEN DEADLOCK_DETECTED THEN
791 BEGIN
792 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_DEADLOCK ||l_attempts);
793 ROLLBACK;
794 l_attempts := l_attempts +1;
795 IF l_attempts = 3 THEN
796 FOR i IN l_first .. l_last
797 LOOP
798 BEGIN
799 UPDATE PRP_PROPOSAL_ACCESSES ACC
800 SET object_version_number = NVL(object_version_number,0) + 1,
801 ACC.last_update_date = SYSDATE,
802 ACC.last_updated_by = p_terr_globals.user_id,
803 ACC.last_update_login = p_terr_globals.last_update_login,
804 ACC.ACCESS_LEVEL = l_faf(i)
805 WHERE ACC.proposal_id = l_proposal_id(i)
806 AND ACC.resource_id = l_resource_id(i)
807 AND NVL(ACC.resource_group_id,-777) = NVL(l_resource_group_id(i),-777);
808 EXCEPTION
809 WHEN OTHERS THEN
810 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_IND_UPD || AS_GAR.G_GENERAL_EXCEPTION);
811 AS_GAR.LOG('PROPOSAL_ID/RESOURCE_id/SALESGROUP_ID/ORG_ID - ' || l_proposal_id(i) || '/' || l_resource_id(i) || '/' || l_resource_group_id(i));
812 END;
813 END LOOP; -- for each record individually
814 COMMIT;
815 END IF;
816 END; -- end of deadlock exception
817 WHEN OTHERS THEN
818 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_BULK_UPD, SQLERRM, TO_CHAR(SQLCODE));
819 x_errbuf := SQLERRM;
820 x_retcode := SQLCODE;
821 x_return_status := FND_API.G_RET_STS_ERROR;
822 END;
823 END LOOP; -- loop for 3 attempts
824 /* For the next batch of records by bulk_size */
825 l_first := l_last + 1;
826 l_last := l_first + l_var;
827 IF l_first > l_proposal_id.LAST THEN
828 l_flag := FALSE;
829 END IF;
830 END LOOP; -- loop for more records within the bulk_size
831 END IF; --l_proposal_id.count > 0
832 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_END);
833 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD || AS_GAR.G_N_ROWS_PROCESSED || l_proposal_id.COUNT);
834 END LOOP; -- loop for more bulk_size fetches
835 l_proposal_id.DELETE;
836 l_resource_id.DELETE;
837 l_resource_group_id.DELETE;
838 l_faf.DELETE;
839 EXCEPTION
840 WHEN OTHERS THEN
841 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_STLEAD, SQLERRM, TO_CHAR(SQLCODE));
842 x_errbuf := SQLERRM;
843 x_retcode := SQLCODE;
844 x_return_status := FND_API.G_RET_STS_ERROR;
845 END SET_TEAM_LEAD_PROPOSALS;
846
847 /************************** End Set PROPOSALS Team Leader *****************/
848
849 /************************** Start Insert Into Entity Accesses*************/
850
851 PROCEDURE INSERT_ACCESSES_PROPOSALS(
852 x_errbuf OUT NOCOPY VARCHAR2,
853 x_retcode OUT NOCOPY VARCHAR2,
854 p_terr_globals IN AS_GAR.TERR_GLOBALS,
855 x_return_status OUT NOCOPY VARCHAR2)
856 IS
857 TYPE num_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
858 TYPE faf_list is TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER;
859
860 l_proposal_id num_id_list;
861 l_resource_id num_id_list;
862 l_resource_group_id num_id_list;
863 l_faf faf_list;
864 l_var NUMBER;
865 l_worker_id NUMBER;
866 l_limit_flag BOOLEAN := FALSE;
867
868 l_MAX_fetches NUMBER := 10000;
869 l_loop_count NUMBER := 0;
870 l_flag BOOLEAN;
871 l_first NUMBER;
872 l_last NUMBER;
873 l_attempts NUMBER := 0;
874 l_src_exists VARCHAR2(1);
875
876
877 CURSOR ins_acc2(c_worker_id number) IS
878 SELECT W.resource_id,
879 W.group_id grp_id,
880 W.trans_object_id proposal_id,
881 DECODE(MAX(W.full_access_flag),'Y','FULL','READ') faf
882 FROM JTF_TAE_1001_PROP_WINNERS W
883 WHERE W.source_id = -1001
884 AND W.worker_id = c_worker_id
885 AND W.resource_type = 'RS_EMPLOYEE'
886 AND W.group_id IS NOT NULL --- Added to work around the JTY functionality which allows group_id to be NULL during setup of resources.
887 GROUP BY W.trans_object_id,
888 W.resource_id,
889 W.group_id;
890 BEGIN
891 /*-------------------------------------------------------------------------+
892 | PROGRAM LOGIC
893 |
894 | Re-Initialize variables and null out if necessary.
895 | Check to see if the profile "OS: proposal Sales Team Default Role Type" is
896 | set for atleast one user.
897 | If it is set, then open the cursor to get all the records that need to be
898 | inserted into accesses along with the default role for every user and bulk
899 | collect into an array.
900 | If it not set, then do the same, except that in this case, the default role
901 | will always be null.
902 | Try bulk inserting into accesses. If this fails, insert records one by one.
903 |
904 +-------------------------------------------------------------------------*/
905 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_START);
906 x_return_status := FND_API.G_RET_STS_SUCCESS;
907 l_worker_id:=p_terr_globals.worker_id;
908 l_var :=p_terr_globals.bulk_size;
909
910 OPEN ins_acc2(l_worker_id); -- Open the null salesforce role code cursor
911
912 LOOP
913 IF (l_limit_flag) THEN EXIT; END IF;
914 l_proposal_id.DELETE;
915 l_resource_id.DELETE;
916 l_resource_group_id.DELETE;
917 l_faf.DELETE;
918 l_loop_count := l_loop_count + 1;
919 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || 'LOOPCOUNT :- ' || l_loop_count);
920
921 FETCH ins_acc2 BULK COLLECT INTO
922 l_resource_id,l_resource_group_id,l_proposal_id,l_faf
923 LIMIT l_MAX_fetches;
924
925 -- Initialize variables
926 l_flag := TRUE;
927 l_first := 0;
928 l_last := 0;
929
930 IF l_proposal_id.count < l_MAX_fetches THEN
931 l_limit_flag := TRUE;
932 END IF;
933
934 IF l_proposal_id.count > 0 THEN
935 l_flag := TRUE;
936 l_first := l_proposal_id.first;
937 l_last := l_first + l_var;
938 WHILE l_flag LOOP
939 IF l_last > l_proposal_id.last THEN
940 l_last := l_proposal_id.last;
941 END IF;
942 BEGIN
943 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);
944 FORALL i IN l_first .. l_last
945 INSERT INTO PRP_PROPOSAL_ACCESSES
946 (
947 proposal_access_id
948 ,object_version_number
949 ,proposal_id
950 ,resource_id
951 ,access_level
952 ,resource_group_id
953 ,keep_flag
954 ,created_by
955 ,creation_date
956 ,last_updated_by
957 ,last_update_date
958 ,last_update_login
959 )
960 (
961 SELECT prp_proposal_accesses_s1.nextval -- Check With Sequence
962 ,1
963 ,l_proposal_id(i)
964 ,l_resource_id(i)
965 ,l_faf(i)
966 ,l_resource_group_id(i)
967 ,'N'
968 ,p_terr_globals.user_id
969 ,SYSDATE
970 ,p_terr_globals.user_id
971 ,SYSDATE
972 ,p_terr_globals.last_update_login
973 FROM DUAL
974 WHERE NOT EXISTS ( SELECT 'X'
975 FROM PRP_PROPOSAL_ACCESSES AA
976 WHERE AA.proposal_id = l_proposal_id(i)
977 AND AA.resource_id = l_resource_id(i)
978 AND NVL(AA.resource_group_id,-777)= NVL(l_resource_group_id(i),-777)
979 )
980 );
981 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);
982 COMMIT;
983 EXCEPTION
984 WHEN DUP_VAL_ON_INDEX THEN
985 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);
986 FOR i IN l_first .. l_last LOOP
987 BEGIN
988 INSERT INTO PRP_PROPOSAL_ACCESSES
989 (
990 proposal_access_id
991 ,object_version_number
992 ,proposal_id
993 ,resource_id
994 ,access_level
995 ,resource_group_id
996 ,keep_flag
997 ,created_by
998 ,creation_date
999 ,last_updated_by
1000 ,last_update_date
1001 ,last_update_login
1002 )
1003 (
1004 SELECT prp_proposal_accesses_s1.nextval -- Check With Sequence
1005 ,1
1006 ,l_proposal_id(i)
1007 ,l_resource_id(i)
1008 ,l_faf(i)
1009 ,l_resource_group_id(i)
1010 ,'N'
1011 ,p_terr_globals.user_id
1012 ,SYSDATE
1013 ,p_terr_globals.user_id
1014 ,SYSDATE
1015 ,p_terr_globals.last_update_login
1016 FROM DUAL
1017 WHERE NOT EXISTS ( SELECT 'X'
1018 FROM PRP_PROPOSAL_ACCESSES AA
1019 WHERE AA.proposal_id = l_proposal_id(i)
1020 AND AA.resource_id = l_resource_id(i)
1021 AND NVL(AA.resource_group_id,-777)= NVL(l_resource_group_id(i),-777)
1022 )
1023 );
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 NULL;
1027 END;
1028 END LOOP; /* loop for DUP_VAL_ON_INDEX individual insert */
1029 COMMIT;
1030 WHEN OTHERS THEN
1031 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1032 x_errbuf := SQLERRM;
1033 x_retcode := SQLCODE;
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035 RAISE;
1036 END;
1037 l_first := l_last + 1;
1038 l_last := l_first + l_var;
1039 IF l_first > l_proposal_id.last THEN
1040 l_flag := FALSE;
1041 END IF;
1042 END LOOP; /* l_flag loop */
1043 END IF; --l_proposal_id.count > 0
1044 END LOOP; -- loop for more bulk_size fetches
1045 l_proposal_id.DELETE;
1046 l_resource_id.DELETE;
1047 l_resource_group_id.DELETE;
1048 l_faf.DELETE;
1049 IF ins_acc2%ISOPEN THEN CLOSE ins_acc2; END IF;
1050 EXCEPTION
1051 WHEN others THEN
1052 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC, SQLERRM, TO_CHAR(SQLCODE));
1053 x_errbuf := SQLERRM;
1054 x_retcode := SQLCODE;
1055 x_return_status := FND_API.G_RET_STS_ERROR;
1056 IF ins_acc2%ISOPEN THEN CLOSE ins_acc2; END IF;
1057 RAISE;
1058 END INSERT_ACCESSES_PROPOSALS;
1059
1060 /************************** End Insert Into Entity Accesses*************/
1061
1062 /************************** Start Insert Into Terr Accesses*************/
1063
1064 PROCEDURE INSERT_TERR_ACCESSES_PROPOSALS(
1065 x_errbuf OUT NOCOPY VARCHAR2,
1066 x_retcode OUT NOCOPY VARCHAR2,
1067 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1068 x_return_status OUT NOCOPY VARCHAR2)
1069 IS
1070 TYPE num_id_list IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1071
1072 l_proposal_id num_id_list;
1073 l_resource_id num_id_list;
1074 l_resource_group_id num_id_list;
1075
1076 l_var NUMBER;
1077 l_limit_flag BOOLEAN := FALSE;
1078 l_worker_id NUMBER;
1079 l_MAX_fetches NUMBER := 10000;
1080 l_loop_count NUMBER := 0;
1081 l_flag BOOLEAN;
1082 l_first NUMBER;
1083 l_last NUMBER;
1084 l_terr_id num_id_list;
1085
1086 CURSOR ins_tacc(c_worker_id number) IS
1087 SELECT w.terr_id
1088 ,w.trans_object_id
1089 ,w.resource_id
1090 ,w.group_id
1091 FROM JTF_TAE_1001_PROP_WINNERS W
1092 WHERE W.source_id = -1001
1093 AND W.worker_id = c_worker_id
1094 AND W.resource_type = 'RS_EMPLOYEE'
1095 AND W.group_id IS NOT NULL --- Added to work around the JTY functionality which allows group_id to be NULL during setup of resources.
1096 GROUP BY W.terr_id,
1097 W.trans_object_id,
1098 W.resource_id,
1099 W.group_id;
1100
1101
1102 BEGIN
1103 /*-------------------------------------------------------------------------+
1104 | PROGRAM LOGIC
1105 |
1106 | Re-Initialize variables and null out if necessary.
1107 | Almost the same as accesses, except the insertion is into prp_territory_accesses
1108 | and there is no involvement of role.
1109 |
1110 +-------------------------------------------------------------------------*/
1111 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_START);
1112 x_return_status := FND_API.G_RET_STS_SUCCESS;
1113 l_worker_id := p_terr_globals.worker_id;
1114 l_var := p_terr_globals.bulk_size;
1115 OPEN ins_tacc(l_worker_id);
1116 LOOP
1117 IF (l_limit_flag) THEN EXIT; END IF;
1118 EXIT WHEN ins_tacc%NOTFOUND;
1119 l_loop_count := l_loop_count + 1;
1120 l_proposal_id.DELETE;
1121 l_resource_id.DELETE;
1122 l_resource_group_id.DELETE;
1123 l_terr_id.DELETE;
1124 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || 'LOOPCOUNT :- ' || l_loop_count);
1125 BEGIN
1126
1127 FETCH ins_tacc BULK COLLECT INTO l_terr_id,
1128 l_proposal_id, l_resource_id, l_resource_group_id
1129 LIMIT l_MAX_fetches;
1130 -- Initialize variables
1131 l_flag := TRUE;
1132 l_first := 0;
1133 l_last := 0;
1134
1135 IF l_terr_id.COUNT < l_MAX_fetches THEN l_limit_flag := TRUE; END IF;
1136 IF l_proposal_id.COUNT > 0 THEN
1137 l_flag := TRUE;
1138 l_first := l_proposal_id.first;
1139 l_last := l_first + l_var;
1140 WHILE l_flag LOOP
1141 IF l_last > l_proposal_id.last THEN
1142 l_last := l_proposal_id.last;
1143 END IF;
1144 BEGIN
1145 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);
1146 FORALL i IN l_first .. l_last
1147 INSERT INTO PRP_TERRITORY_ACCESSES
1148 ( proposal_access_id
1149 ,territory_id
1150 ,object_version_number
1151 ,created_by
1152 ,creation_date
1153 ,last_updated_by
1154 ,last_update_date
1155 ,last_update_login
1156 ,program_id
1157 ,program_login_id
1158 ,program_application_id
1159 ,request_id
1160 ,program_update_date
1161
1162 )
1163 (
1164 SELECT
1165 V.acc_id,
1166 l_terr_id(i),
1167 1,
1168 p_terr_globals.user_id,
1169 SYSDATE,
1170 p_terr_globals.user_id,
1171 SYSDATE,
1172 p_terr_globals.last_update_login,
1173 p_terr_globals.prog_id,
1174 p_terr_globals.prog_id,
1175 p_terr_globals.prog_appl_id,
1176 p_terr_globals.request_id,
1177 SYSDATE
1178 FROM
1179 ( SELECT DISTINCT a.proposal_access_id acc_id
1180 FROM PRP_PROPOSAL_ACCESSES A
1181 WHERE A.proposal_id=l_proposal_id(i)
1182 AND NVL(A.resource_group_id,-777)=NVL(l_resource_group_id(i),-777)
1183 AND A.resource_id=l_resource_id(i)
1184 AND NOT EXISTS
1185 (SELECT 'X'
1186 FROM PRP_TERRITORY_ACCESSES AST
1187 WHERE AST.proposal_access_id = A.proposal_access_id
1188 AND AST.territory_id = l_terr_id(i))
1189 ) V
1190 );
1191 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);
1192 COMMIT;
1193 EXCEPTION
1194 WHEN DUP_VAL_ON_INDEX THEN
1195 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);
1196 FOR i IN l_first .. l_last LOOP
1197 BEGIN
1198 INSERT INTO PRP_TERRITORY_ACCESSES
1199 ( proposal_access_id
1200 ,territory_id
1201 ,object_version_number
1202 ,created_by
1203 ,creation_date
1204 ,last_updated_by
1205 ,last_update_date
1206 ,last_update_login
1207 ,program_id
1208 ,program_login_id
1209 ,program_application_id
1210 ,request_id
1211 ,program_update_date
1212
1213 )
1214 (
1215 SELECT
1216 V.acc_id,
1217 l_terr_id(i),
1218 1,
1219 p_terr_globals.user_id,
1220 SYSDATE,
1221 p_terr_globals.user_id,
1222 SYSDATE,
1223 p_terr_globals.last_update_login,
1224 p_terr_globals.prog_id,
1225 p_terr_globals.prog_id,
1226 p_terr_globals.prog_appl_id,
1227 p_terr_globals.request_id,
1228 SYSDATE
1229 FROM
1230 ( SELECT DISTINCT a.proposal_access_id acc_id
1231 FROM PRP_PROPOSAL_ACCESSES A
1232 WHERE A.proposal_id=l_proposal_id(i)
1233 AND NVL(A.resource_group_id,-777)=NVL(l_resource_group_id(i),-777)
1234 AND A.resource_id=l_resource_id(i)
1235 AND NOT EXISTS
1236 (SELECT 'X'
1237 FROM PRP_TERRITORY_ACCESSES AST
1238 WHERE AST.proposal_access_id = A.proposal_access_id
1239 AND AST.territory_id = l_terr_id(i))
1240 ) V
1241 );
1242 EXCEPTION
1243 WHEN Others THEN
1244 NULL;
1245 END;
1246 END LOOP;
1247 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);
1248 COMMIT;
1249 WHEN Others THEN
1250 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_BULK_INS, SQLERRM, TO_CHAR(SQLCODE));
1251 x_errbuf := SQLERRM;
1252 x_retcode := SQLCODE;
1253 x_return_status := FND_API.G_RET_STS_ERROR;
1254 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1255 RAISE;
1256 END;
1257 l_first := l_last + 1;
1258 l_last := l_first + l_var;
1259 IF l_first > l_proposal_id.last THEN
1260 l_flag := FALSE;
1261 END IF;
1262 END LOOP;
1263 END IF; --l_proposal_id.count > 0
1264 EXCEPTION
1265 WHEN Others THEN
1266 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC , SQLERRM, TO_CHAR(SQLCODE));
1267 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1268 x_errbuf := SQLERRM;
1269 x_retcode := SQLCODE;
1270 x_return_status := FND_API.G_RET_STS_ERROR;
1271 RAISE;
1272 END;
1273 END LOOP; -- end loop for insert into territory accesses
1274 l_proposal_id.DELETE;
1275 l_resource_id.DELETE;
1276 l_resource_group_id.DELETE;
1277 l_terr_id.DELETE;
1278 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1279 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC || AS_GAR.G_END);
1280 EXCEPTION
1281 WHEN others THEN
1282 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSTERRACC, SQLERRM, TO_CHAR(SQLCODE));
1283 x_errbuf := SQLERRM;
1284 x_retcode := SQLCODE;
1285 x_return_status := FND_API.G_RET_STS_ERROR;
1286 IF ins_tacc%ISOPEN THEN CLOSE ins_tacc; END IF;
1287 RAISE;
1288 END INSERT_TERR_ACCESSES_PROPOSALS;
1289
1290 /************************** End Insert Into Terr Accesses*************/
1291
1292 /************************** Start Proposal Cleanup ***********************/
1293
1294 PROCEDURE Perform_Proposal_Cleanup(
1295 x_errbuf OUT NOCOPY VARCHAR2,
1296 x_retcode OUT NOCOPY VARCHAR2,
1297 p_terr_globals IN AS_GAR.TERR_GLOBALS,
1298 x_return_status OUT NOCOPY VARCHAR2)
1299 IS
1300
1301 TYPE num_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
1302 l_proposal_id num_id_list;
1303
1304
1305 l_flag BOOLEAN;
1306 l_first NUMBER;
1307 l_last NUMBER;
1308 l_var NUMBER;
1309 l_attempts NUMBER := 0;
1310
1311 l_worker_id NUMBER;
1312
1313 l_del_flag BOOLEAN:=FALSE;
1314 l_limit_flag BOOLEAN := FALSE;
1315 l_MAX_fetches NUMBER := 10000;
1316 l_loop_count NUMBER := 0;
1317 G_NUM_REC CONSTANT NUMBER:=10000;
1318 G_DEL_REC CONSTANT NUMBER:=10001;
1319
1320
1321
1322 CURSOR del_acct_totalmode(c_worker_id number) IS
1323 SELECT distinct trans_object_id
1324 FROM JTF_TAE_1001_PROP_TRANS
1325 WHERE worker_id=c_worker_id;
1326
1327 CURSOR del_acct_newmode(c_worker_id number) IS
1328 SELECT distinct trans_object_id
1329 FROM JTF_TAE_1001_PROP_NM_TRANS
1330 WHERE worker_id=c_worker_id;
1331
1332 BEGIN
1333 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_START);
1334 x_return_status := FND_API.G_RET_STS_SUCCESS;
1335 l_worker_id := p_terr_globals.worker_id;
1336 l_var := p_terr_globals.bulk_size;
1337 l_MAX_fetches := p_terr_globals.cursor_limit;
1338
1339 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1340 OPEN del_acct_totalmode(l_worker_id);
1341 ELSE
1342 OPEN del_acct_newmode(l_worker_id);
1343 END IF;
1344 LOOP --{L1
1345 IF (l_limit_flag) THEN EXIT; END IF;
1346
1347 l_loop_count := l_loop_count + 1;
1348 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || 'LOOPCOUNT :- ' ||l_loop_count);
1349 BEGIN
1350 IF p_terr_globals.run_mode = AS_GAR.G_TOTAL_MODE THEN
1351 EXIT WHEN del_acct_totalmode%NOTFOUND;
1352 FETCH del_acct_totalmode BULK COLLECT INTO l_proposal_id
1353 LIMIT l_MAX_fetches;
1354 ELSE
1355 EXIT WHEN del_acct_newmode%NOTFOUND;
1356 FETCH del_acct_newmode BULK COLLECT INTO l_proposal_id
1357 LIMIT l_MAX_fetches;
1358 END IF;
1359 -- Initialize variables (Ist Init)
1360 l_flag := TRUE;
1361 l_first := 0;
1362 l_last := 0;
1363 l_attempts := 1;
1364
1365 IF l_proposal_id.COUNT < l_MAX_fetches THEN
1366 l_limit_flag := TRUE;
1367 END IF;
1368
1369 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_START);
1370 IF l_proposal_id.count > 0 THEN --{I1
1371 l_flag := TRUE;
1372 l_first := l_proposal_id.first;
1373 l_last := l_first + l_var;
1374 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_N_ROWS_PROCESSED ||
1375 l_proposal_id.FIRST || '-' ||
1376 l_proposal_id.LAST);
1377 IF l_last > l_proposal_id.LAST THEN
1378 l_last := l_proposal_id.LAST;
1379 END IF;
1380 l_del_flag := FALSE;
1381 l_attempts := 1;
1382 LOOP --{L3 to update only 10k record at a time
1383 IF (l_del_flag) THEN EXIT; END IF;
1384 l_del_flag := FALSE;
1385 WHILE l_attempts < 3 LOOP --{L4
1386 BEGIN
1387 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);
1388 FORALL i in l_first..l_last
1389 DELETE PRP_PROPOSAL_ACCESSES ACC
1390 WHERE ACC.PROPOSAL_ID=l_proposal_id(i)
1391 AND ACC.KEEP_FLAG = 'N'
1392 AND NOT EXISTS (SELECT 'X'
1393 FROM JTF_TAE_1001_PROP_WINNERS W
1394 WHERE W.TRANS_OBJECT_ID = ACC.PROPOSAL_ID
1395 AND W.WORKER_ID = l_worker_id
1396 AND W.RESOURCE_ID = ACC.RESOURCE_ID
1397 AND NVL(W.GROUP_ID,-777)=NVL(ACC.RESOURCE_GROUP_ID,-777))
1398 AND ROWNUM < G_DEL_REC;
1399 COMMIT;
1400 l_attempts := 3;
1401 IF l_proposal_id.COUNT < G_NUM_REC THEN l_del_flag := TRUE; END IF;
1402 EXCEPTION
1403 WHEN deadlock_detected THEN
1404 BEGIN --{I2
1405 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);
1406 ROLLBACK;
1407 l_attempts := l_attempts +1;
1408 IF l_attempts = 3 THEN
1409 FOR i IN l_first .. l_last LOOP --{L5
1410 BEGIN
1411 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);
1412 DELETE PRP_PROPOSAL_ACCESSES ACC
1413 WHERE ACC.PROPOSAL_ID=l_proposal_id(i)
1414 AND ACC.KEEP_FLAG = 'N'
1415 AND NOT EXISTS (SELECT 'X'
1416 FROM JTF_TAE_1001_PROP_WINNERS W
1417 WHERE W.TRANS_OBJECT_ID = ACC.PROPOSAL_ID
1418 AND W.WORKER_ID = l_worker_id
1419 AND W.RESOURCE_ID = ACC.RESOURCE_ID
1420 AND NVL(W.GROUP_ID,-777)=NVL(ACC.RESOURCE_GROUP_ID,-777));
1421 COMMIT;
1422 END;
1423 END LOOP; --}L5
1424 COMMIT;
1425 l_del_flag := TRUE;
1426 END IF;
1427 END; --}I2 end of deadlock exception
1428 WHEN OTHERS THEN
1429 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1430 IF del_acct_totalmode%ISOPEN THEN CLOSE del_acct_totalmode; END IF;
1431 IF del_acct_newmode%ISOPEN THEN CLOSE del_acct_newmode; END IF;
1432 x_errbuf := SQLERRM;
1433 x_retcode := SQLCODE;
1434 x_return_status := FND_API.G_RET_STS_ERROR;
1435 RAISE;
1436 END;
1437 END LOOP; --}L4 l_attempts loop 3 trys
1438 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);
1439 l_first := l_last + 1;
1440 l_last := l_first + l_var;
1441 IF l_first > l_proposal_id.LAST THEN
1442 l_flag := FALSE;
1443 END IF;
1444 END LOOP; --}L2 while l_flag loop (10K cust loop)
1445 END IF;--}I1
1446 AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_END);
1447 COMMIT;
1448 EXCEPTION
1449 WHEN Others THEN
1450 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1451 IF del_acct_totalmode%ISOPEN THEN CLOSE del_acct_totalmode; END IF;
1452 IF del_acct_newmode%ISOPEN THEN CLOSE del_acct_newmode; END IF;
1453 x_errbuf := SQLERRM;
1454 x_retcode := SQLCODE;
1455 x_return_status := FND_API.G_RET_STS_ERROR;
1456 RAISE;
1457 END;
1458 END LOOP;--}L1
1459 IF del_acct_totalmode%ISOPEN THEN CLOSE del_acct_totalmode; END IF;
1460 IF del_acct_newmode%ISOPEN THEN CLOSE del_acct_newmode; END IF;
1461 EXCEPTION
1462 WHEN OTHERS THEN
1463 AS_GAR.LOG_EXCEPTION(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));
1464 x_errbuf := SQLERRM;
1465 x_retcode := SQLCODE;
1466 x_return_status := FND_API.G_RET_STS_ERROR;
1467 RAISE;
1468 END Perform_Proposal_Cleanup;
1469
1470 /************************** End proposal Cleanup ***********************/
1471
1472 END AS_GAR_PROPOSALS_PUB;