[Home] [Help]
PACKAGE BODY: APPS.ASN_MIG_SALES_CREDITS_PVT
Source
1 PACKAGE BODY asn_mig_sales_credits_pvt AS
2 /* $Header: asnvmscb.pls 120.1 2007/10/03 09:38:18 snsarava ship $ */
3
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- asn_mig_sales_credits_pvt
10 --
11 -- PURPOSE
12 -- This package contains migration related code for sales credits.
13 --
14 -- NOTES
15 --
16 -- HISTORY
17 -- gasriniv 25/10/2004 Changes made for ASN.B support
18 -- Changes made to Mig_Multi_SalesRep_Opp_sub
19 -- 1)One opportunity can have multiple lines
20 -- but only on credit recievers per line
21 -- 2)all non revenue credit percentages should be made 100%
22 -- 3)Salesrep recieving credit should be there in the sales team
23 -- 4)remove duplicate non-quota credit reciever for the same line for the
24 -- same credit type for the same opporutunity
25 -- 5)set the default_from_owner_flag on the sales line if rep recieving
26 -- credit is the owner
27 -- gasriniv 16/11/2004 BUG FIX 4010812
28 -- fixed cursor c_add_sales_team to add distinct clause
29 -- gasriniv 31/12/2004 Add new requirment for deleting 0 credit lines
30 -- Changed the logic from creating new opporutunty if there are multiple
31 -- sales credits to creating a new line if there are multiple sales credits
32 -- gasriniv 14/01/2005 Added update of WHO columns
33 -- Removed check for open status flag while updating forecast date to null
34 -- gasriniv 25/01/2005 Cloned the delete of duplicate so that it is fired for all opportunites
35 -- BUG FIX 4139294
36 -- gasriniv 01/02/2005 Default Best Forecast Worst columns for non revenue credits also bug#4151483
37 -- Update full access flag in as_accesses_all for this opportunity
38 -- bug#4150276 and as per wenxia's email 28 Jan 2005 18:30:21 -0800
39 -- gasriniv 02/02/2005 Added logic to merged duplicate credits if they exists for all opportunities
40 -- **********************************************************************************************************
41
42 G_PKG_NAME CONSTANT VARCHAR2(30):='asn_mig_sales_credits_pvt';
43 G_FILE_NAME CONSTANT VARCHAR2(12):='asnvmscb.pls';
44
45 --
46 --
47 --
48 PROCEDURE Mig_SlsCred_Owner_Main
49 (
50 errbuf OUT NOCOPY VARCHAR2,
51 retcode OUT NOCOPY NUMBER,
52 p_num_workers IN NUMBER,
53 p_commit_flag IN VARCHAR2,
54 p_debug_flag IN VARCHAR2
55 )
56 IS
57 l_api_name CONSTANT VARCHAR2(30) :=
58 'Mig_SlsCred_Owner_Main';
59 l_module_name CONSTANT VARCHAR2(256) :=
60 'asn.plsql.asn_mig_sales_credits_pvt.Mig_SlsCred_Owner_Main';
61 l_msg_count NUMBER;
62 l_msg_data VARCHAR2(2000);
63 l_req_id NUMBER;
64 l_request_data VARCHAR2(30);
65 l_max_num_rows NUMBER;
66 l_rows_per_worker NUMBER;
67 l_start_id NUMBER;
68 l_end_id NUMBER;
69
70 CURSOR c1 IS SELECT as_leads_s.nextval FROM dual;
71
72 BEGIN
73
74 --
75 -- If this is first time parent is called, then split the rows
76 -- among workers and put the parent in paused state
77 --
78 IF (fnd_conc_global.request_data IS NULL) THEN
79
80 -- Log
81 IF (p_debug_flag = 'Y' AND
82 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
83 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
84 'Start:' || 'p_num_workers=' || p_num_workers ||
85 ',p_commit_flag=' || p_commit_flag ||
86 ',p_debug_flag=' || p_debug_flag);
87 END IF;
88
89 -- Log
90 IF (p_debug_flag = 'Y' AND
91 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
92 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
93 'Disable AS_SALES_CREDITS_BIUD trigger');
94 END IF;
95
96 --
97 -- Get maximum number of possible rows in as_leads_all
98 --
99 OPEN c1;
100 FETCH c1 INTO l_max_num_rows;
101 CLOSE c1;
102
103 --
104 -- Compute row range to be assigned to each worker
105 --
106 l_rows_per_worker := ROUND(l_max_num_rows/p_num_workers) + 1;
107
108 --
109 -- Assign rows to each worker
110 --
111
112 -- Initialize start ID value
113 l_start_id := 0;
114 FOR i IN 1..p_num_workers LOOP
115
116 -- Initialize end ID value
117 l_end_id := l_start_id + l_rows_per_worker;
118
119 -- Submit the request
120 l_req_id :=
121 fnd_request.submit_request
122 (
123 application => 'ASN',
124 program => 'ASN_MIG_SLSCRED_OWNER_SUB_EXE',
125 description => null,
126 start_time => sysdate,
127 sub_request => true,
128 argument1 => l_start_id,
129 argument2 => l_end_id,
130 argument3 => p_commit_flag,
131 argument4 => p_debug_flag,
132 argument5 => CHR(0),
133 argument6 => CHR(0),
134 argument7 => CHR(0),
135 argument8 => CHR(0),
136 argument9 => CHR(0),
137 argument10 => CHR(0),
138 argument11 => CHR(0),
139 argument12 => CHR(0),
140 argument13 => CHR(0),
141 argument14 => CHR(0),
142 argument15 => CHR(0),
143 argument16 => CHR(0),
144 argument17 => CHR(0),
145 argument18 => CHR(0),
146 argument19 => CHR(0),
147 argument20 => CHR(0),
148 argument21 => CHR(0),
149 argument22 => CHR(0),
150 argument23 => CHR(0),
151 argument24 => CHR(0),
152 argument25 => CHR(0),
153 argument26 => CHR(0),
154 argument27 => CHR(0),
155 argument28 => CHR(0),
156 argument29 => CHR(0),
157 argument30 => CHR(0),
158 argument31 => CHR(0),
159 argument32 => CHR(0),
160 argument33 => CHR(0),
161 argument34 => CHR(0),
162 argument35 => CHR(0),
163 argument36 => CHR(0),
164 argument37 => CHR(0),
165 argument38 => CHR(0),
166 argument39 => CHR(0),
167 argument40 => CHR(0),
168 argument41 => CHR(0),
169 argument42 => CHR(0),
170 argument43 => CHR(0),
171 argument44 => CHR(0),
172 argument45 => CHR(0),
173 argument46 => CHR(0),
174 argument47 => CHR(0),
175 argument48 => CHR(0),
176 argument49 => CHR(0),
177 argument50 => CHR(0),
178 argument51 => CHR(0),
179 argument52 => CHR(0),
180 argument53 => CHR(0),
181 argument54 => CHR(0),
182 argument55 => CHR(0),
183 argument56 => CHR(0),
184 argument57 => CHR(0),
185 argument58 => CHR(0),
186 argument59 => CHR(0),
187 argument60 => CHR(0),
188 argument61 => CHR(0),
189 argument62 => CHR(0),
190 argument63 => CHR(0),
191 argument64 => CHR(0),
192 argument65 => CHR(0),
193 argument66 => CHR(0),
194 argument67 => CHR(0),
195 argument68 => CHR(0),
196 argument69 => CHR(0),
197 argument70 => CHR(0),
198 argument71 => CHR(0),
199 argument72 => CHR(0),
200 argument73 => CHR(0),
201 argument74 => CHR(0),
202 argument75 => CHR(0),
203 argument76 => CHR(0),
204 argument77 => CHR(0),
205 argument78 => CHR(0),
206 argument79 => CHR(0),
207 argument80 => CHR(0),
208 argument81 => CHR(0),
209 argument82 => CHR(0),
210 argument83 => CHR(0),
211 argument84 => CHR(0),
212 argument85 => CHR(0),
213 argument86 => CHR(0),
214 argument87 => CHR(0),
215 argument88 => CHR(0),
216 argument89 => CHR(0),
217 argument90 => CHR(0),
218 argument91 => CHR(0),
219 argument92 => CHR(0),
220 argument93 => CHR(0),
221 argument94 => CHR(0),
222 argument95 => CHR(0),
223 argument96 => CHR(0),
224 argument97 => CHR(0),
225 argument98 => CHR(0),
226 argument99 => CHR(0),
227 argument100 => CHR(0)
228 );
229
230 --
231 -- If request submission failed, exit with error.
232 --
233 IF (l_req_id = 0) THEN
234
235 errbuf := fnd_message.get;
236 retcode := 2;
237 RETURN;
238
239 END IF;
240
241 -- Set start ID value
242 l_start_id := l_end_id + 1;
243
244 END LOOP; -- end i
245
246 --
247 -- After submitting request for all workers, put the parent
248 -- in paused state. When all children are done, the parent
249 -- would be called again, and then it will terminate
250 --
251 fnd_conc_global.set_req_globals
252 (
253 conc_status => 'PAUSED',
254 request_data => to_char(l_req_id) --,
255 -- conc_restart_time => to_char(sysdate),
256 -- release_sub_request => 'N'
257 );
258
259 ELSE
260
261 -- Log
262 IF (p_debug_flag = 'Y' AND
263 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
265 'Re-entering:' || 'p_num_workers=' || p_num_workers ||
266 ',p_commit_flag=' || p_commit_flag ||
267 ',p_debug_flag='||p_debug_flag);
268 END IF;
269
270 -- Log
271 IF (p_debug_flag = 'Y' AND
272 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
273 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
274 'Enable AS_SALES_CREDITS_BIUD trigger');
275 END IF;
276
277 errbuf := 'Migration completed';
278 retcode := 0;
279
280 -- Log
281 IF (p_debug_flag = 'Y' AND
282 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
283 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
284 'Done:' || 'p_num_workers=' || p_num_workers ||
285 ',p_commit_flag=' || p_commit_flag ||
286 ',p_debug_flag='||p_debug_flag);
287 END IF;
288
289 END IF;
290
291 EXCEPTION
292
293 WHEN OTHERS THEN
294 ROLLBACK;
295
296 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
297
298 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
299 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
300 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
301 FND_MESSAGE.Set_Token('REASON', SQLERRM);
302 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name, true);
303 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
304 l_api_name||':'||sqlcode||':'||sqlerrm);
305 END IF;
306 END Mig_SlsCred_Owner_Main;
307
308
309 --
310 --
311 --
312 PROCEDURE Mig_SlsCred_Owner_Sub
313 (
314 errbuf OUT NOCOPY VARCHAR2,
315 retcode OUT NOCOPY NUMBER,
316 p_start_id IN VARCHAR2,
317 p_end_id IN VARCHAR2,
318 p_commit_flag IN VARCHAR2,
319 p_debug_flag IN VARCHAR2
320 )
321 IS
322 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
323 TYPE Var30Tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
324
325 l_api_name CONSTANT VARCHAR2(30) :=
326 'Mig_SlsCred_Owner_Sub';
327 l_module_name CONSTANT VARCHAR2(256) :=
328 'asn.plsql.asn_mig_sales_credits_pvt.Mig_SlsCred_Owner_Sub';
329
330 l_credit_type_id NUMBER;
331
332 l_sales_credit_ids NumTab;
333 l_lead_ids NumTab;
334 l_customer_ids NumTab;
335 l_person_ids NumTab;
336 l_open_flags Var30Tab;
337 l_owner_salesforce_ids NumTab;
338 l_owner_sales_group_ids NumTab;
339 l_salesforce_ids NumTab;
340 l_sales_group_ids NumTab;
341 l_ranks NumTab;
342
343 CURSOR c1(pc_credit_type_id NUMBER,
344 pc_start_id NUMBER,
345 pc_end_id NUMBER) IS
346 SELECT
347 SCD.sales_credit_id
348 ,SCD.lead_id
349 ,SCD.customer_id
350 ,SCD.employee_person_id
351 ,SCD.opp_open_status_flag
352 ,SCD.owner_salesforce_id
353 ,SCD.owner_sales_group_id
354 ,SCD.salesforce_id
355 ,SCD.sales_group_id
356 ,RANK () OVER (PARTITION BY SCD.lead_id ORDER BY SCD.sales_credit_id) RK
357 FROM
358 as_sales_credits_denorm SCD
359 WHERE
360 SCD.lead_id BETWEEN pc_start_id AND pc_end_id
361 AND SCD.credit_type_id = pc_credit_type_id
362 AND SCD.salesforce_id IS NOT NULL
363 AND SCD.sales_group_id IS NOT NULL
364 AND SCD.partner_customer_id IS NULL
365 AND NOT EXISTS (SELECT 1 FROM as_sales_credits SC2
366 WHERE SC2.lead_id = SCD.lead_id
367 AND SC2.credit_type_id = pc_credit_type_id
368 AND SC2.sales_credit_id <> SCD.sales_credit_id
369 AND (SC2.salesforce_id <> SCD.salesforce_id
370 OR SC2.salesgroup_id <> SCD.sales_group_id))
371 AND (SCD.salesforce_id <> SCD.owner_salesforce_id
372 OR SCD.sales_group_id <> SCD.owner_sales_group_id
373 OR SCD.owner_salesforce_id IS NULL
374 OR SCD.owner_sales_group_id IS NULL);
375
376 BEGIN
377
378 -- Log
379 IF (p_debug_flag = 'Y' AND
380 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
381 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
382 'Start:' || 'p_start_id=' || p_start_id ||
383 ',p_end_id='||p_end_id ||
384 ',p_debug_flag='||p_debug_flag);
385 END IF;
386
387 --
388 -- Get the value for the Quota (or Revenue) sales credit type id from profile
389 -- 'OS: Forecast Sales Credit Type' (AS_FORECAST_CREDIT_TYPE_ID)
390 --
391 l_credit_type_id :=
392 FND_PROFILE.Value_Specific('AS_FORECAST_CREDIT_TYPE_ID', null, null, null);
393
394 -- Log
395 IF (p_debug_flag = 'Y' AND
396 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
397 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
398 'l_credit_type_id=' || l_credit_type_id);
399 END IF;
400
401 --
402 -- Get all rows in as_sales_credits that have one salesrep for the
403 -- opportunity, but the salesrep is not the owner
404 --
405
406 -- Log
407 IF (p_debug_flag = 'Y' AND
408 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
409 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
410 'Opening cursor');
411 END IF;
412
413 -- Open cursor
414 OPEN c1(l_credit_type_id, p_start_id, p_end_id);
415
416 IF (p_debug_flag = 'Y' AND
417 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
418 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
419 'Opened cursor');
420 END IF;
421
422 -- Start loop
423 LOOP
424
425 -- Log
426 IF (p_debug_flag = 'Y' AND
427 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
428 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
429 'Inside loop');
430 END IF;
431
432 -- Fetch rows
433 FETCH c1 BULK COLLECT INTO l_sales_credit_ids
434 ,l_lead_ids
435 ,l_customer_ids
436 ,l_person_ids
437 ,l_open_flags
438 ,l_owner_salesforce_ids
439 ,l_owner_sales_group_ids
440 ,l_salesforce_ids
441 ,l_sales_group_ids
442 ,l_ranks LIMIT 10000;
443
444 -- Log
445 IF (p_debug_flag = 'Y' AND
446 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
447 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
448 'After fetch. Num rows:' ||
449 l_sales_credit_ids.COUNT || ':');
450 END IF;
451
452 EXIT WHEN l_sales_credit_ids.COUNT <= 0;
453
454 -- Log
455 IF (p_debug_flag = 'Y' AND
456 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
457 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
458 'After exit and processing number of rows =' ||
459 c1%ROWCOUNT);
460 END IF;
461
462 --
463 -- Update owner of the opportunity from the sales credit
464 --
465
466 -- Log
467 IF (p_debug_flag = 'Y' AND
468 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
469 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
470 'Updating owner in as_leads_all');
471 END IF;
472
473 FORALL i IN l_sales_credit_ids.FIRST..l_sales_credit_ids.LAST
474 UPDATE
475 as_leads_all ALA
476 SET
477 ALA.owner_salesforce_id = l_salesforce_ids(i)
478 ,ALA.owner_sales_group_id = l_sales_group_ids(i) ,
479 last_updated_by = FND_GLOBAL.user_id,
480 last_update_date = sysdate,
481 last_update_login = FND_GLOBAL.conc_login_id
482 WHERE
483 ALA.lead_id = l_lead_ids(i)
484 AND l_ranks(i) = 1
485 AND (ALA.owner_salesforce_id <> l_salesforce_ids(i)
486 OR ALA.owner_sales_group_id <> l_sales_group_ids(i)
487 OR ALA.owner_salesforce_id IS NULL
488 OR ALA.owner_sales_group_id IS NULL);
489
490 -- Log
491 IF (p_debug_flag = 'Y' AND
492 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
493 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
494 'Updated owner in as_leads_all: number of rows =' ||
495 sql%ROWCOUNT);
496 END IF;
497
498 --
499 -- Update as_accesses_all to have owner flag reset for the person
500 -- previously marked as owner
501 --
502
503 -- Log
504 IF (p_debug_flag = 'Y' AND
505 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
506 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
507 'Updating as_accesses_all to have owner flag reset');
508 END IF;
509
510 FORALL i IN l_sales_credit_ids.FIRST..l_sales_credit_ids.LAST
511 UPDATE
512 as_accesses_all ACS
513 SET
514 owner_flag = 'N',
515 last_updated_by = FND_GLOBAL.user_id,
516 last_update_date = sysdate,
517 last_update_login = FND_GLOBAL.conc_login_id
518 WHERE
519 ACS.lead_id = l_lead_ids(i)
520 AND l_ranks(i) = 1
521 AND ACS.owner_flag = 'Y';
522
523 -- Log
524 IF (p_debug_flag = 'Y' AND
525 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
526 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
527 'Updated as_accesses_all to have owner flag reset = ' ||
528 sql%ROWCOUNT);
529 END IF;
530
531 --
532 -- Update as_accesses_all to have owner flag set for the new owner
533 --
534
535 -- Log
536 IF (p_debug_flag = 'Y' AND
537 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
538 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
539 'Updating as_accesses_all to have owner flag set for new owner');
540 END IF;
541
542 FORALL i IN l_sales_credit_ids.FIRST..l_sales_credit_ids.LAST
543 UPDATE
544 as_accesses_all ACS
545 SET
546 owner_flag = 'Y'
547 ,freeze_flag = 'Y' ,
548 last_updated_by = FND_GLOBAL.user_id,
549 last_update_date = sysdate,
550 last_update_login = FND_GLOBAL.conc_login_id
551 WHERE
552 ACS.lead_id = l_lead_ids(i)
553 AND l_ranks(i) = 1
554 AND (ACS.owner_flag = 'N'
555 OR ACS.owner_flag IS NULL)
556 AND ACS.salesforce_id = l_salesforce_ids(i)
557 AND ACS.sales_group_id = l_sales_group_ids(i);
558
559 -- Log
560 IF (p_debug_flag = 'Y' AND
561 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
562 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
563 'Updated as_accesses_all to have owner flag set for new owner = ' ||
564 sql%ROWCOUNT);
565 END IF;
566
567 --
568 -- Insert into as_accesses_all if the new owner does not exist in the
569 -- sales team
570 --
571
572 -- Log
573 IF (p_debug_flag = 'Y' AND
574 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
575 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
576 'Inserting into as_accesses_all');
577 END IF;
578
579 FORALL i IN l_sales_credit_ids.FIRST..l_sales_credit_ids.LAST
580 INSERT INTO
581 as_accesses_all
582 (
583 access_id
584 ,last_update_date
585 ,last_updated_by
586 ,creation_date
587 ,created_by
588 ,last_update_login
589 ,reassign_flag
590 ,team_leader_flag
591 ,customer_id
592 ,salesforce_id
593 ,person_id
594 ,partner_customer_id
595 ,lead_id
596 ,sales_group_id
597 ,partner_cont_party_id
598 ,owner_flag
599 ,created_by_tap_flag
600 ,open_flag
601 ,freeze_flag
602 ,org_id
603 ,object_version_number
604 )
605 SELECT
606 AS_ACCESSES_S.nextval
607 ,sysdate
608 ,FND_GLOBAL.USER_ID
609 ,sysdate
610 ,FND_GLOBAL.USER_ID
611 ,FND_GLOBAL.CONC_LOGIN_ID
612 ,NULL
613 ,'Y'
614 ,l_customer_ids(i)
615 ,l_salesforce_ids(i)
616 ,l_person_ids(i)
617 ,NULL
618 ,l_lead_ids(i)
619 ,l_sales_group_ids(i)
620 ,NULL
621 ,'Y'
622 ,'N'
623 ,l_open_flags(i)
624 ,'Y'
625 ,NULL
626 ,1
627 FROM
628 dual
629 WHERE
630 l_ranks(i) = 1
631 AND NOT EXISTS (SELECT 1 FROM as_accesses_all ACS
632 WHERE ACS.lead_id IS NOT NULL
633 AND ACS.lead_id = l_lead_ids(i)
634 AND l_ranks(i) = 1
635 AND ACS.salesforce_id = l_salesforce_ids(i)
636 AND ACS.sales_group_id = l_sales_group_ids(i));
637
638
639 -- Log
640 IF (p_debug_flag = 'Y' AND
641 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
642 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
643 'Inserted into as_accesses_all = ' ||
644 sql%ROWCOUNT);
645 END IF;
646
647 -- Commit
648 IF (p_commit_flag = 'Y') THEN
649
650 -- Log
651 IF (p_debug_flag = 'Y' AND
652 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
653 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
654 'Committing');
655 END IF;
656
657 COMMIT;
658
659 ELSE
660
661 -- Log
662 IF (p_debug_flag = 'Y' AND
663 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
665 'Rolling back');
666 END IF;
667
668 ROLLBACK;
669
670 END IF;
671
672 END LOOP;
673
674 CLOSE c1;
675
676 -- Log
677 IF (p_debug_flag = 'Y' AND
678 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
679 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
680 'Done:' || 'p_start_id=' || p_start_id ||
681 ',p_end_id='||p_end_id ||
682 ',p_debug_flag='||p_debug_flag);
683 END IF;
684
685 EXCEPTION
686
687 WHEN OTHERS THEN
688 ROLLBACK;
689
690 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
691
692 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
693 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
694 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
695 FND_MESSAGE.Set_Token('REASON', SQLERRM);
696 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name, true);
697 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
698 l_api_name||':'||sqlcode||':'||sqlerrm);
699
700 END IF;
701
702 END Mig_SlsCred_Owner_Sub;
703
704
705 PROCEDURE Link_to_Partners(
706 p_orig_lead_id IN NUMBER, p_lead_id IN NUMBER,
707 p_debug_flag IN VARCHAR2)
708 IS
709 l_module_name CONSTANT VARCHAR2(256) :=
710 'asn.plsql.asn_mig_sales_credits_pvt.Link_to_Partners';
711
712 l_lead_workflow_rec pv_assign_util_pvt.lead_workflow_rec_type;
713 l_assignment_rec pv_assign_util_pvt.ASSIGNMENT_REC_TYPE;
714 l_party_notify_rec pv_assign_util_pvt.party_notify_rec_type;
715 l_assignment_id number;
716 l_party_notification_id number;
717 l_orig_itemKey varchar2(30);
718 l_itemKey varchar2(30);
719
720 l_user_id number := fnd_global.user_id();
721
722 l_return_status varchar2(1);
723 l_msg_count number;
724 l_msg_data varchar2(2000);
725
726 CURSOR lc_get_lwf(pc_lead_id number) is
727 SELECT wf_item_type, wf_item_key, wf_status, matched_due_date,
728 offered_due_date, bypass_cm_ok_flag, routing_status, routing_type
729 FROM pv_lead_workflows
730 WHERE lead_id = pc_lead_id and latest_routing_flag = 'Y'
731 AND entity = 'OPPORTUNITY';
732
733 CURSOR lc_get_la(pc_itemtype varchar2, pc_itemkey varchar2) is
734 SELECT partner_id, assign_sequence, lead_id, status, status_date,
735 wf_item_type, wf_item_key, source_type, related_party_id,
736 partner_access_code, reason_code, related_party_access_code,
737 lead_assignment_id
738 FROM pv_lead_assignments
739 WHERE wf_item_type = pc_itemtype AND wf_item_key = pc_itemkey;
740
741 CURSOR lc_get_pn(pc_assignment_id NUMBER) IS
742 SELECT notification_type, lead_assignment_id, user_id, user_name,
743 resource_id, decision_maker_flag, resource_response, response_date
744 FROM pv_party_notifications WHERE lead_assignment_id = pc_assignment_id;
745
746 BEGIN
747
748 IF (p_debug_flag = 'Y' AND
749 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
751 'Start Link_to_Partners');
752 END IF;
753
754 OPEN lc_get_lwf(pc_lead_id => p_orig_lead_id);
755 FETCH lc_get_lwf INTO
756 l_lead_workflow_rec.wf_item_type, l_orig_itemkey,
757 l_lead_workflow_rec.wf_status, l_lead_workflow_rec.matched_due_date,
758 l_lead_workflow_rec.offered_due_date, l_lead_workflow_rec.bypass_cm_ok_flag,
759 l_lead_workflow_rec.routing_status, l_lead_workflow_rec.routing_type;
760
761 IF lc_get_lwf%found THEN
762
763 l_lead_workflow_rec.lead_id := p_lead_id;
764 l_lead_workflow_rec.created_by := l_user_id;
765 l_lead_workflow_rec.last_updated_by := l_user_id;
766 l_lead_workflow_rec.entity := 'OPPORTUNITY';
767 l_lead_workflow_rec.latest_routing_flag := 'Y';
768
769 PV_ASSIGN_UTIL_PVT.Create_lead_workflow_row (
770 p_api_version_number => 1.0,
771 p_init_msg_list => FND_API.G_TRUE,
772 p_commit => FND_API.G_FALSE,
773 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
774 p_workflow_rec => l_lead_workflow_rec,
775 x_ItemKey => l_itemkey,
776 x_return_status => l_return_status,
777 x_msg_count => l_msg_count,
778 x_msg_data => l_msg_data);
779
780 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
781 RAISE FND_API.G_EXC_ERROR;
782 end if;
783
784 IF (p_debug_flag = 'Y' AND
785 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
786 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
787 'Lead workflow id: ' || l_itemkey);
788 END IF;
789
790 FOR c1 IN lc_get_la(pc_itemtype => l_lead_workflow_rec.wf_item_type,
791 pc_itemkey => l_orig_itemkey)
792 LOOP
793 l_assignment_rec.lead_id := p_lead_id;
794 l_assignment_rec.related_party_id := c1.related_party_id;
795 l_assignment_rec.related_party_access_code := c1.related_party_access_code;
796 l_assignment_rec.partner_id := c1.partner_id;
797 l_assignment_rec.assign_sequence := c1.assign_sequence;
798 l_assignment_rec.source_type := c1.source_type;
799 l_assignment_rec.reason_code := c1.reason_code;
800 l_assignment_rec.object_version_number := 0;
801 l_assignment_rec.status_date := c1.status_date;
802 l_assignment_rec.status := c1.status;
803 l_assignment_rec.partner_access_code := c1.partner_access_code;
804 l_assignment_rec.wf_item_type := c1.wf_item_Type;
805 l_assignment_rec.wf_item_key := l_itemKey;
806
807 pv_assign_util_pvt.Create_lead_assignment_row (
808 p_api_version_number => 1.0,
809 p_init_msg_list => FND_API.G_FALSE,
810 p_commit => FND_API.G_FALSE,
811 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
812 p_assignment_rec => l_assignment_rec,
813 x_lead_assignment_id => l_assignment_id,
814 x_return_status => l_return_status,
815 x_msg_count => l_msg_count,
816 x_msg_data => l_msg_data);
817
818 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
819 RAISE FND_API.G_EXC_ERROR;
820 END IF;
821
822 IF (p_debug_flag = 'Y' AND
823 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
824 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
825 'Lead assignment id: ' || l_assignment_id);
826 END IF;
827
828 FOR c2 IN lc_get_pn(pc_assignment_id => c1.lead_assignment_id) LOOP
829
830 l_party_notify_rec.WF_ITEM_TYPE := l_assignment_rec.wf_item_type;
831 l_party_notify_rec.WF_ITEM_KEY := l_assignment_rec.wf_item_key;
832 l_party_notify_rec.LEAD_ASSIGNMENT_ID := l_assignment_id;
833 l_party_notify_rec.NOTIFICATION_TYPE := c2.notification_type;
834 l_party_notify_rec.RESOURCE_ID := c2.resource_id;
835 l_party_notify_rec.USER_ID := c2.user_id;
836 l_party_notify_rec.USER_NAME := c2.user_name;
837 l_party_notify_rec.RESOURCE_RESPONSE := c2.resource_response;
838 l_party_notify_rec.RESPONSE_DATE := c2.response_date;
839 l_party_notify_rec.DECISION_MAKER_FLAG := c2.decision_maker_flag;
840
841 pv_assign_util_pvt.create_party_notification(
842 p_api_version_number => 1.0
843 ,p_init_msg_list => FND_API.G_FALSE
844 ,p_commit => FND_API.G_FALSE
845 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
846 ,P_party_notify_Rec => l_party_notify_rec
847 ,x_party_notification_id => l_party_notification_id
848 ,x_return_status => l_return_status
849 ,x_msg_count => l_msg_count
850 ,x_msg_data => l_msg_data);
851
852 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
853 RAISE FND_API.G_EXC_ERROR;
854 END IF;
855 IF (p_debug_flag = 'Y' AND
856 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
857 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
858 'Party Notification id: ' || l_party_notification_id);
859 END IF;
860
861 END LOOP;
862 END LOOP;
863
864 END IF;
865 CLOSE lc_get_lwf;
866
867 IF (p_debug_flag = 'Y' AND
868 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
869 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
870 'End Link_to_Partners');
871 END IF;
872 END Link_to_Partners;
873
874
875 -- Step 3.f.ii Updates Sales Credits so that only the quota credits of a
876 -- single Sales Rep are retained and changed to 100%. The line amounts are
877 -- changed to the Sales Credit amounts.
878 PROCEDURE Update_sc_for_rep (
879 p_lead_id IN NUMBER,
880 p_sf_id IN NUMBER,
881 p_sg_id IN NUMBER,
882 p_credit_type_id IN NUMBER,
883 p_identity_sf_id IN NUMBER,
884 p_debug_flag IN VARCHAR2,
885 x_return_status OUT NOCOPY VARCHAR2,
886 x_msg_count OUT NOCOPY NUMBER,
887 x_msg_data OUT NOCOPY VARCHAR2
888 )
889 IS
890 l_module_name CONSTANT VARCHAR2(256) :=
891 'asn.plsql.asn_mig_sales_credits_pvt.Update_sc_for_rep';
892
893 l_sc_tbl AS_OPPORTUNITY_PUB.Sales_Credit_Tbl_Type;
894 l_sc_out_tbl AS_OPPORTUNITY_PUB.Sales_Credit_Out_Tbl_Type;
895
896 l_sc_amount NUMBER;
897
898 l_ll_tbl AS_OPPORTUNITY_PUB.Line_Tbl_Type;
899 l_ll_tbl_count NUMBER;
900 l_ll_out_tbl AS_OPPORTUNITY_PUB.Line_Out_Tbl_Type;
901
902 l_header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type;
903
904 CURSOR c_rep_quota_credits(p_lead_id NUMBER, p_credit_type_id NUMBER,
905 p_sf_id NUMBER, p_sg_id NUMBER) IS
906 SELECT * FROM as_sales_credits
907 WHERE lead_id = p_lead_id
908 AND credit_type_id = p_credit_type_id
909 AND nvl(salesforce_id, -37) = nvl(p_sf_id, -37)
910 AND nvl(salesgroup_id, -37) = nvl(p_sg_id, -37)
911 ORDER BY lead_line_id;
912
913 l_sc_select_rec c_rep_quota_credits%ROWTYPE;
914 l_sc_next_select_rec c_rep_quota_credits%ROWTYPE;
915
916 CURSOR c_lead_line(p_lead_line_id NUMBER, p_sc_amount NUMBER) IS
917 SELECT *
918 FROM as_lead_lines
919 WHERE lead_line_id = p_lead_line_id AND total_amount <> p_sc_amount;
920
921 BEGIN
922
923 IF (p_debug_flag = 'Y' AND
924 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
925 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
926 'Begin Update_sc_for_rep');
927 END IF;
928
929 x_return_status := FND_API.G_RET_STS_SUCCESS;
930
931 -- Update Sales Credits to only those which the rep
932 -- is getting.
933 l_sc_amount := 0;
934 l_ll_tbl.DELETE;
935 l_ll_tbl_count := 0;
936 OPEN c_rep_quota_credits(p_lead_id,
937 p_credit_type_id, p_sf_id,
938 p_sg_id);
939 FETCH c_rep_quota_credits INTO l_sc_next_select_rec;
940 WHILE c_rep_quota_credits%FOUND LOOP
941 l_sc_select_rec := l_sc_next_select_rec;
942 -- Prefetching to detect last row
943 FETCH c_rep_quota_credits INTO l_sc_next_select_rec;
944 l_sc_amount := l_sc_amount +
945 l_sc_select_rec.credit_amount;
946
947 -- If the next sales credit is not for the same line(duplicate) or if
948 -- we have reached the last record then add it to the list of Sales
949 -- Credits.
950 IF c_rep_quota_credits%NOTFOUND OR
951 (l_sc_select_rec.lead_line_id
952 <> l_sc_next_select_rec.lead_line_id) THEN
953 l_sc_tbl.DELETE;
954 -- last_update_date should be passed as such to Update API's.
955 -- Passing sysdate will result in an error asking to requery
956 -- the (Dirty) Record
957 l_sc_tbl(1).last_update_date := l_sc_select_rec.last_update_date;
958 l_sc_tbl(1).last_updated_by := FND_GLOBAL.user_id;
959 l_sc_tbl(1).creation_Date := l_sc_select_rec.creation_Date;
960 l_sc_tbl(1).created_by := FND_GLOBAL.user_id;
961 l_sc_tbl(1).last_update_login := FND_GLOBAL.conc_login_id;
962 l_sc_tbl(1).request_id := FND_GLOBAL.conc_request_id;
963 l_sc_tbl(1).program_application_id := FND_GLOBAL.prog_appl_id;
964 l_sc_tbl(1).program_id := FND_GLOBAL.conc_program_id;
965 l_sc_tbl(1).program_update_date := sysdate;
966 l_sc_tbl(1).sales_credit_id := l_sc_select_rec.sales_credit_id;
967 l_sc_tbl(1).original_sales_credit_id := l_sc_select_rec.original_sales_credit_id;
968 l_sc_tbl(1).lead_id := l_sc_select_rec.lead_id;
969 l_sc_tbl(1).lead_line_id := l_sc_select_rec.lead_line_id;
970 l_sc_tbl(1).salesforce_id := l_sc_select_rec.salesforce_id;
971 l_sc_tbl(1).person_id := l_sc_select_rec.person_id;
972 l_sc_tbl(1).salesgroup_id := l_sc_select_rec.salesgroup_id;
973 l_sc_tbl(1).partner_customer_id := l_sc_select_rec.partner_customer_id;
974 l_sc_tbl(1).partner_address_id := l_sc_select_rec.partner_address_id;
975 l_sc_tbl(1).revenue_amount := l_sc_select_rec.revenue_amount;
976 l_sc_tbl(1).revenue_percent := l_sc_select_rec.revenue_percent;
977 l_sc_tbl(1).quota_credit_amount := l_sc_select_rec.quota_credit_amount;
978 l_sc_tbl(1).quota_credit_percent := l_sc_select_rec.quota_credit_percent;
979 l_sc_tbl(1).MANAGER_REVIEW_FLAG := l_sc_select_rec.MANAGER_REVIEW_FLAG;
980 l_sc_tbl(1).MANAGER_REVIEW_DATE := l_sc_select_rec.MANAGER_REVIEW_DATE;
981 l_sc_tbl(1).credit_type_id := l_sc_select_rec.credit_type_id;
982 l_sc_tbl(1).credit_amount := l_sc_amount;
983 l_sc_tbl(1).credit_percent := 100;
984 l_sc_tbl(1).attribute_category := l_sc_select_rec.attribute_category;
985 l_sc_tbl(1).attribute1 := l_sc_select_rec.attribute1;
986 l_sc_tbl(1).attribute2 := l_sc_select_rec.attribute2;
987 l_sc_tbl(1).attribute3 := l_sc_select_rec.attribute3;
988 l_sc_tbl(1).attribute4 := l_sc_select_rec.attribute4;
989 l_sc_tbl(1).attribute5 := l_sc_select_rec.attribute5;
990 l_sc_tbl(1).attribute6 := l_sc_select_rec.attribute6;
991 l_sc_tbl(1).attribute7 := l_sc_select_rec.attribute7;
992 l_sc_tbl(1).attribute8 := l_sc_select_rec.attribute8;
993 l_sc_tbl(1).attribute9 := l_sc_select_rec.attribute9;
994 l_sc_tbl(1).attribute10 := l_sc_select_rec.attribute10;
995 l_sc_tbl(1).attribute11 := l_sc_select_rec.attribute11;
996 l_sc_tbl(1).attribute12 := l_sc_select_rec.attribute12;
997 l_sc_tbl(1).attribute13 := l_sc_select_rec.attribute13;
998 l_sc_tbl(1).attribute14 := l_sc_select_rec.attribute14;
999 l_sc_tbl(1).attribute15 := l_sc_select_rec.attribute15;
1000
1001 AS_OPPORTUNITY_PUB.Modify_Sales_Credits(
1002 p_api_version_number => 2.0,
1003 p_init_msg_list => FND_API.G_FALSE,
1004 p_commit => FND_API.G_FALSE,
1005 p_validation_level => 90,
1006 p_identity_salesforce_id => p_identity_sf_id,
1007 p_sales_credit_tbl => l_sc_tbl,
1008 p_check_access_flag => 'N',
1009 p_admin_flag => 'N',
1010 p_admin_group_id => NULL,
1011 p_partner_cont_party_id => NULL,
1012 x_sales_credit_out_tbl => l_sc_out_tbl,
1013 x_return_status => x_return_status,
1014 x_msg_count => x_msg_count,
1015 x_msg_data => x_msg_data
1016 );
1017
1018 -- There will be atmost one looping of the below FOR LOOP
1019 FOR ll_select_rec IN
1020 c_lead_line(l_sc_tbl(1).lead_line_id, l_sc_amount)
1021 LOOP
1022 l_ll_tbl_count := l_ll_tbl_count + 1;
1023
1024 -- last_update_date should be passed as such to Update API's.
1025 -- Passing sysdate will result in an error asking to requery
1026 -- the (Dirty) Record
1027 l_ll_tbl(l_ll_tbl_count).last_update_date := ll_select_rec.last_update_date;
1028 l_ll_tbl(l_ll_tbl_count).last_updated_by := FND_GLOBAL.user_id;
1029 l_ll_tbl(l_ll_tbl_count).creation_Date := ll_select_rec.creation_Date;
1030 l_ll_tbl(l_ll_tbl_count).created_by := ll_select_rec.created_by;
1031 l_ll_tbl(l_ll_tbl_count).last_update_login := FND_GLOBAL.conc_login_id;
1032 l_ll_tbl(l_ll_tbl_count).request_id := FND_GLOBAL.conc_request_id;
1033 l_ll_tbl(l_ll_tbl_count).program_application_id := FND_GLOBAL.prog_appl_id;
1034 l_ll_tbl(l_ll_tbl_count).program_id := FND_GLOBAL.conc_program_id;
1035 l_ll_tbl(l_ll_tbl_count).program_update_date := sysdate;
1036 l_ll_tbl(l_ll_tbl_count).lead_id := ll_select_rec.lead_id;
1037 l_ll_tbl(l_ll_tbl_count).lead_line_id := ll_select_rec.lead_line_id;
1038 l_ll_tbl(l_ll_tbl_count).original_lead_line_id := ll_select_rec.original_lead_line_id;
1039 l_ll_tbl(l_ll_tbl_count).interest_type_id := ll_select_rec.interest_type_id;
1040 l_ll_tbl(l_ll_tbl_count).interest_status_code := ll_select_rec.interest_status_code;
1041 l_ll_tbl(l_ll_tbl_count).primary_interest_code_id := ll_select_rec.primary_interest_code_id;
1042 l_ll_tbl(l_ll_tbl_count).secondary_interest_code_id := ll_select_rec.secondary_interest_code_id;
1043 l_ll_tbl(l_ll_tbl_count).inventory_item_id := ll_select_rec.inventory_item_id;
1044 l_ll_tbl(l_ll_tbl_count).organization_id := ll_select_rec.organization_id;
1045 l_ll_tbl(l_ll_tbl_count).uom_code := ll_select_rec.uom_code;
1046 l_ll_tbl(l_ll_tbl_count).quantity := ll_select_rec.quantity;
1047 l_ll_tbl(l_ll_tbl_count).ship_date := ll_select_rec.ship_date;
1048 l_ll_tbl(l_ll_tbl_count).total_amount := l_sc_amount;
1049 l_ll_tbl(l_ll_tbl_count).sales_stage_id := ll_select_rec.sales_stage_id;
1050 l_ll_tbl(l_ll_tbl_count).win_probability := ll_select_rec.win_probability;
1051 l_ll_tbl(l_ll_tbl_count).status_code := ll_select_rec.status_code;
1052 l_ll_tbl(l_ll_tbl_count).decision_date := ll_select_rec.decision_date;
1053 l_ll_tbl(l_ll_tbl_count).channel_code := ll_select_rec.channel_code;
1054 l_ll_tbl(l_ll_tbl_count).price := ll_select_rec.price;
1055 l_ll_tbl(l_ll_tbl_count).price_volume_margin := ll_select_rec.price_volume_margin;
1056 l_ll_tbl(l_ll_tbl_count).quoted_line_flag := ll_select_rec.quoted_line_flag;
1057 l_ll_tbl(l_ll_tbl_count).Source_Promotion_Id := ll_select_rec.Source_Promotion_Id;
1058 l_ll_tbl(l_ll_tbl_count).forecast_date := ll_select_rec.forecast_date;
1059 l_ll_tbl(l_ll_tbl_count).rolling_forecast_flag := ll_select_rec.rolling_forecast_flag;
1060 l_ll_tbl(l_ll_tbl_count).Offer_Id := ll_select_rec.Offer_Id;
1061 l_ll_tbl(l_ll_tbl_count).ORG_ID := ll_select_rec.ORG_ID;
1062 l_ll_tbl(l_ll_tbl_count).product_category_id := ll_select_rec.product_category_id;
1063 l_ll_tbl(l_ll_tbl_count).product_cat_set_id := ll_select_rec.product_cat_set_id;
1064 l_ll_tbl(l_ll_tbl_count).attribute_category := ll_select_rec.attribute_category;
1065 l_ll_tbl(l_ll_tbl_count).attribute1 := ll_select_rec.attribute1;
1066 l_ll_tbl(l_ll_tbl_count).attribute2 := ll_select_rec.attribute2;
1067 l_ll_tbl(l_ll_tbl_count).attribute3 := ll_select_rec.attribute3;
1068 l_ll_tbl(l_ll_tbl_count).attribute4 := ll_select_rec.attribute4;
1069 l_ll_tbl(l_ll_tbl_count).attribute5 := ll_select_rec.attribute5;
1070 l_ll_tbl(l_ll_tbl_count).attribute6 := ll_select_rec.attribute6;
1071 l_ll_tbl(l_ll_tbl_count).attribute7 := ll_select_rec.attribute7;
1072 l_ll_tbl(l_ll_tbl_count).attribute8 := ll_select_rec.attribute8;
1073 l_ll_tbl(l_ll_tbl_count).attribute9 := ll_select_rec.attribute9;
1074 l_ll_tbl(l_ll_tbl_count).attribute10 := ll_select_rec.attribute10;
1075 l_ll_tbl(l_ll_tbl_count).attribute11 := ll_select_rec.attribute11;
1076 l_ll_tbl(l_ll_tbl_count).attribute12 := ll_select_rec.attribute12;
1077 l_ll_tbl(l_ll_tbl_count).attribute13 := ll_select_rec.attribute13;
1078 l_ll_tbl(l_ll_tbl_count).attribute14 := ll_select_rec.attribute14;
1079 l_ll_tbl(l_ll_tbl_count).attribute15 := ll_select_rec.attribute15;
1080 END LOOP;
1081 l_sc_amount := 0;
1082 END IF;
1083 END LOOP;
1084 CLOSE c_rep_quota_credits;
1085
1086 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1087 RAISE FND_API.G_EXC_ERROR;
1088 END IF;
1089
1090 -- Update Lead Line Amounts
1091 l_header_rec.lead_id := p_lead_id;
1092
1093 IF l_ll_tbl_count > 0 THEN
1094 AS_OPPORTUNITY_PUB.Update_Opp_Lines(
1095 p_api_version_number => 2.0,
1096 p_init_msg_list => FND_API.G_FALSE,
1097 p_commit => FND_API.G_FALSE,
1098 p_validation_level => 90,
1099 p_identity_salesforce_id => p_identity_sf_id,
1100 p_line_tbl => l_ll_tbl,
1101 p_header_rec => l_header_rec,
1102 p_check_access_flag => 'N',
1103 p_admin_flag => 'N',
1104 p_admin_group_id => NULL,
1105 p_partner_cont_party_id => NULL,
1106 x_line_out_tbl => l_ll_out_tbl,
1107 x_return_status => x_return_status,
1108 x_msg_count => x_msg_count,
1109 x_msg_data => x_msg_data
1110 );
1111 END IF;
1112
1113 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1114 RAISE FND_API.G_EXC_ERROR;
1115 END IF;
1116
1117 IF (p_debug_flag = 'Y' AND
1118 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1119 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1120 'End Update_sc_for_rep');
1121 END IF;
1122
1123 EXCEPTION
1124 WHEN OTHERS then
1125 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1126 x_return_status := FND_API.G_RET_STS_ERROR;
1127 END IF;
1128 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1129 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1130 'In When others (Update_sc_for_rep). lead_id: '
1131 || p_lead_id || ' Exception SQlerr is : ' ||
1132 substr(SQLERRM, 1, 1950));
1133 END IF;
1134
1135 End Update_sc_for_rep;
1136
1137
1138 --
1139 --
1140 --
1141 -- Step 3.f.ii Updates Sales Credits so that only the quota credits of a
1142 -- single Sales Rep are retained and changed to 100%. The line amounts are
1143 -- changed to the Sales Credit amounts.
1144 PROCEDURE Update_sc_for_rep_line (
1145 p_lead_id IN NUMBER,
1146 p_lead_line_id IN NUMBER,
1147 p_sf_id IN NUMBER,
1148 p_sg_id IN NUMBER,
1149 p_credit_type_id IN NUMBER,
1150 p_identity_sf_id IN NUMBER,
1151 p_debug_flag IN VARCHAR2,
1152 x_return_status OUT NOCOPY VARCHAR2,
1153 x_msg_count OUT NOCOPY NUMBER,
1154 x_msg_data OUT NOCOPY VARCHAR2
1155 )
1156 IS
1157 l_module_name CONSTANT VARCHAR2(256) :=
1158 'asn.plsql.asn_mig_sales_credits_pvt.Update_sc_for_rep';
1159
1160 l_sc_tbl AS_OPPORTUNITY_PUB.Sales_Credit_Tbl_Type;
1161 l_sc_out_tbl AS_OPPORTUNITY_PUB.Sales_Credit_Out_Tbl_Type;
1162
1163 l_sc_amount NUMBER;
1164
1165 l_ll_tbl AS_OPPORTUNITY_PUB.Line_Tbl_Type;
1166 l_ll_tbl_count NUMBER;
1167 l_ll_out_tbl AS_OPPORTUNITY_PUB.Line_Out_Tbl_Type;
1168
1169 l_header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type;
1170
1171 CURSOR c_rep_quota_credits(p_lead_id NUMBER,p_lead_line_id NUMBER, p_credit_type_id NUMBER,
1172 p_sf_id NUMBER, p_sg_id NUMBER) IS
1173 SELECT * FROM as_sales_credits
1174 WHERE lead_id = p_lead_id
1175 AND lead_line_id = p_lead_line_id
1176 AND credit_type_id = p_credit_type_id
1177 AND nvl(salesforce_id, -37) = nvl(p_sf_id, -37)
1178 AND nvl(salesgroup_id, -37) = nvl(p_sg_id, -37)
1179 ORDER BY lead_line_id;
1180
1181 l_sc_select_rec c_rep_quota_credits%ROWTYPE;
1182 l_sc_next_select_rec c_rep_quota_credits%ROWTYPE;
1183
1184 CURSOR c_lead_line(p_lead_line_id NUMBER, p_sc_amount NUMBER) IS
1185 SELECT *
1186 FROM as_lead_lines
1187 WHERE lead_line_id = p_lead_line_id AND total_amount <> p_sc_amount;
1188
1189 BEGIN
1190
1191 IF (p_debug_flag = 'Y' AND
1192 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1193 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1194 'Begin Update_sc_for_rep');
1195 END IF;
1196
1197 x_return_status := FND_API.G_RET_STS_SUCCESS;
1198
1199 -- Update Sales Credits to only those which the rep
1200 -- is getting.
1201 l_sc_amount := 0;
1202 l_ll_tbl.DELETE;
1203 l_ll_tbl_count := 0;
1204 OPEN c_rep_quota_credits(p_lead_id,p_lead_line_id,
1205 p_credit_type_id, p_sf_id,
1206 p_sg_id);
1207 FETCH c_rep_quota_credits INTO l_sc_next_select_rec;
1208 WHILE c_rep_quota_credits%FOUND LOOP
1209 l_sc_select_rec := l_sc_next_select_rec;
1210 -- Prefetching to detect last row
1211 FETCH c_rep_quota_credits INTO l_sc_next_select_rec;
1212 l_sc_amount := l_sc_amount +
1213 l_sc_select_rec.credit_amount;
1214
1215 -- If the next sales credit is not for the same line(duplicate) or if
1216 -- we have reached the last record then add it to the list of Sales
1217 -- Credits.
1218 IF c_rep_quota_credits%NOTFOUND OR
1219 (l_sc_select_rec.lead_line_id
1220 <> l_sc_next_select_rec.lead_line_id) THEN
1221 l_sc_tbl.DELETE;
1222 -- last_update_date should be passed as such to Update API's.
1223 -- Passing sysdate will result in an error asking to requery
1224 -- the (Dirty) Record
1225 l_sc_tbl(1).last_update_date := l_sc_select_rec.last_update_date;
1226 l_sc_tbl(1).last_updated_by := FND_GLOBAL.user_id;
1227 l_sc_tbl(1).creation_Date := l_sc_select_rec.creation_Date;
1228 l_sc_tbl(1).created_by := FND_GLOBAL.user_id;
1229 l_sc_tbl(1).last_update_login := FND_GLOBAL.conc_login_id;
1230 l_sc_tbl(1).request_id := FND_GLOBAL.conc_request_id;
1231 l_sc_tbl(1).program_application_id := FND_GLOBAL.prog_appl_id;
1232 l_sc_tbl(1).program_id := FND_GLOBAL.conc_program_id;
1233 l_sc_tbl(1).program_update_date := sysdate;
1234 l_sc_tbl(1).sales_credit_id := l_sc_select_rec.sales_credit_id;
1235 l_sc_tbl(1).original_sales_credit_id := l_sc_select_rec.original_sales_credit_id;
1236 l_sc_tbl(1).lead_id := l_sc_select_rec.lead_id;
1237 l_sc_tbl(1).lead_line_id := l_sc_select_rec.lead_line_id;
1238 l_sc_tbl(1).salesforce_id := l_sc_select_rec.salesforce_id;
1239 l_sc_tbl(1).person_id := l_sc_select_rec.person_id;
1240 l_sc_tbl(1).salesgroup_id := l_sc_select_rec.salesgroup_id;
1241 l_sc_tbl(1).partner_customer_id := l_sc_select_rec.partner_customer_id;
1242 l_sc_tbl(1).partner_address_id := l_sc_select_rec.partner_address_id;
1243 l_sc_tbl(1).revenue_amount := l_sc_select_rec.revenue_amount;
1244 l_sc_tbl(1).revenue_percent := l_sc_select_rec.revenue_percent;
1245 l_sc_tbl(1).quota_credit_amount := l_sc_select_rec.quota_credit_amount;
1246 l_sc_tbl(1).quota_credit_percent := l_sc_select_rec.quota_credit_percent;
1247 l_sc_tbl(1).MANAGER_REVIEW_FLAG := l_sc_select_rec.MANAGER_REVIEW_FLAG;
1248 l_sc_tbl(1).MANAGER_REVIEW_DATE := l_sc_select_rec.MANAGER_REVIEW_DATE;
1249 l_sc_tbl(1).credit_type_id := l_sc_select_rec.credit_type_id;
1250 l_sc_tbl(1).credit_amount := l_sc_amount;
1251 l_sc_tbl(1).credit_percent := 100;
1252 l_sc_tbl(1).attribute_category := l_sc_select_rec.attribute_category;
1253 l_sc_tbl(1).attribute1 := l_sc_select_rec.attribute1;
1254 l_sc_tbl(1).attribute2 := l_sc_select_rec.attribute2;
1255 l_sc_tbl(1).attribute3 := l_sc_select_rec.attribute3;
1256 l_sc_tbl(1).attribute4 := l_sc_select_rec.attribute4;
1257 l_sc_tbl(1).attribute5 := l_sc_select_rec.attribute5;
1258 l_sc_tbl(1).attribute6 := l_sc_select_rec.attribute6;
1259 l_sc_tbl(1).attribute7 := l_sc_select_rec.attribute7;
1260 l_sc_tbl(1).attribute8 := l_sc_select_rec.attribute8;
1261 l_sc_tbl(1).attribute9 := l_sc_select_rec.attribute9;
1262 l_sc_tbl(1).attribute10 := l_sc_select_rec.attribute10;
1263 l_sc_tbl(1).attribute11 := l_sc_select_rec.attribute11;
1264 l_sc_tbl(1).attribute12 := l_sc_select_rec.attribute12;
1265 l_sc_tbl(1).attribute13 := l_sc_select_rec.attribute13;
1266 l_sc_tbl(1).attribute14 := l_sc_select_rec.attribute14;
1267 l_sc_tbl(1).attribute15 := l_sc_select_rec.attribute15;
1268
1269 AS_OPPORTUNITY_PUB.Modify_Sales_Credits(
1270 p_api_version_number => 2.0,
1271 p_init_msg_list => FND_API.G_FALSE,
1272 p_commit => FND_API.G_FALSE,
1273 p_validation_level => 90,
1274 p_identity_salesforce_id => p_identity_sf_id,
1275 p_sales_credit_tbl => l_sc_tbl,
1276 p_check_access_flag => 'N',
1277 p_admin_flag => 'N',
1278 p_admin_group_id => NULL,
1279 p_partner_cont_party_id => NULL,
1280 x_sales_credit_out_tbl => l_sc_out_tbl,
1281 x_return_status => x_return_status,
1282 x_msg_count => x_msg_count,
1283 x_msg_data => x_msg_data
1284 );
1285
1286 -- There will be atmost one looping of the below FOR LOOP
1287 FOR ll_select_rec IN
1288 c_lead_line(l_sc_tbl(1).lead_line_id, l_sc_amount)
1289 LOOP
1290 l_ll_tbl_count := l_ll_tbl_count + 1;
1291
1292 -- last_update_date should be passed as such to Update API's.
1293 -- Passing sysdate will result in an error asking to requery
1294 -- the (Dirty) Record
1295 l_ll_tbl(l_ll_tbl_count).last_update_date := ll_select_rec.last_update_date;
1296 l_ll_tbl(l_ll_tbl_count).last_updated_by := FND_GLOBAL.user_id;
1297 l_ll_tbl(l_ll_tbl_count).creation_Date := ll_select_rec.creation_Date;
1298 l_ll_tbl(l_ll_tbl_count).created_by := ll_select_rec.created_by;
1299 l_ll_tbl(l_ll_tbl_count).last_update_login := FND_GLOBAL.conc_login_id;
1300 l_ll_tbl(l_ll_tbl_count).request_id := FND_GLOBAL.conc_request_id;
1301 l_ll_tbl(l_ll_tbl_count).program_application_id := FND_GLOBAL.prog_appl_id;
1302 l_ll_tbl(l_ll_tbl_count).program_id := FND_GLOBAL.conc_program_id;
1303 l_ll_tbl(l_ll_tbl_count).program_update_date := sysdate;
1304 l_ll_tbl(l_ll_tbl_count).lead_id := ll_select_rec.lead_id;
1305 l_ll_tbl(l_ll_tbl_count).lead_line_id := ll_select_rec.lead_line_id;
1306 l_ll_tbl(l_ll_tbl_count).original_lead_line_id := ll_select_rec.original_lead_line_id;
1307 l_ll_tbl(l_ll_tbl_count).interest_type_id := ll_select_rec.interest_type_id;
1308 l_ll_tbl(l_ll_tbl_count).interest_status_code := ll_select_rec.interest_status_code;
1309 l_ll_tbl(l_ll_tbl_count).primary_interest_code_id := ll_select_rec.primary_interest_code_id;
1310 l_ll_tbl(l_ll_tbl_count).secondary_interest_code_id := ll_select_rec.secondary_interest_code_id;
1311 l_ll_tbl(l_ll_tbl_count).inventory_item_id := ll_select_rec.inventory_item_id;
1312 l_ll_tbl(l_ll_tbl_count).organization_id := ll_select_rec.organization_id;
1313 l_ll_tbl(l_ll_tbl_count).uom_code := ll_select_rec.uom_code;
1314 l_ll_tbl(l_ll_tbl_count).quantity := ll_select_rec.quantity;
1315 l_ll_tbl(l_ll_tbl_count).ship_date := ll_select_rec.ship_date;
1316 l_ll_tbl(l_ll_tbl_count).total_amount := l_sc_amount;
1317 l_ll_tbl(l_ll_tbl_count).sales_stage_id := ll_select_rec.sales_stage_id;
1318 l_ll_tbl(l_ll_tbl_count).win_probability := ll_select_rec.win_probability;
1319 l_ll_tbl(l_ll_tbl_count).status_code := ll_select_rec.status_code;
1320 l_ll_tbl(l_ll_tbl_count).decision_date := ll_select_rec.decision_date;
1321 l_ll_tbl(l_ll_tbl_count).channel_code := ll_select_rec.channel_code;
1322 l_ll_tbl(l_ll_tbl_count).price := ll_select_rec.price;
1323 l_ll_tbl(l_ll_tbl_count).price_volume_margin := ll_select_rec.price_volume_margin;
1324 l_ll_tbl(l_ll_tbl_count).quoted_line_flag := ll_select_rec.quoted_line_flag;
1325 l_ll_tbl(l_ll_tbl_count).Source_Promotion_Id := ll_select_rec.Source_Promotion_Id;
1326 l_ll_tbl(l_ll_tbl_count).forecast_date := ll_select_rec.forecast_date;
1327 l_ll_tbl(l_ll_tbl_count).rolling_forecast_flag := ll_select_rec.rolling_forecast_flag;
1328 l_ll_tbl(l_ll_tbl_count).Offer_Id := ll_select_rec.Offer_Id;
1329 l_ll_tbl(l_ll_tbl_count).ORG_ID := ll_select_rec.ORG_ID;
1330 l_ll_tbl(l_ll_tbl_count).product_category_id := ll_select_rec.product_category_id;
1331 l_ll_tbl(l_ll_tbl_count).product_cat_set_id := ll_select_rec.product_cat_set_id;
1332 l_ll_tbl(l_ll_tbl_count).attribute_category := ll_select_rec.attribute_category;
1333 l_ll_tbl(l_ll_tbl_count).attribute1 := ll_select_rec.attribute1;
1334 l_ll_tbl(l_ll_tbl_count).attribute2 := ll_select_rec.attribute2;
1335 l_ll_tbl(l_ll_tbl_count).attribute3 := ll_select_rec.attribute3;
1336 l_ll_tbl(l_ll_tbl_count).attribute4 := ll_select_rec.attribute4;
1337 l_ll_tbl(l_ll_tbl_count).attribute5 := ll_select_rec.attribute5;
1338 l_ll_tbl(l_ll_tbl_count).attribute6 := ll_select_rec.attribute6;
1339 l_ll_tbl(l_ll_tbl_count).attribute7 := ll_select_rec.attribute7;
1340 l_ll_tbl(l_ll_tbl_count).attribute8 := ll_select_rec.attribute8;
1341 l_ll_tbl(l_ll_tbl_count).attribute9 := ll_select_rec.attribute9;
1342 l_ll_tbl(l_ll_tbl_count).attribute10 := ll_select_rec.attribute10;
1343 l_ll_tbl(l_ll_tbl_count).attribute11 := ll_select_rec.attribute11;
1344 l_ll_tbl(l_ll_tbl_count).attribute12 := ll_select_rec.attribute12;
1345 l_ll_tbl(l_ll_tbl_count).attribute13 := ll_select_rec.attribute13;
1346 l_ll_tbl(l_ll_tbl_count).attribute14 := ll_select_rec.attribute14;
1347 l_ll_tbl(l_ll_tbl_count).attribute15 := ll_select_rec.attribute15;
1348 END LOOP;
1349 l_sc_amount := 0;
1350 END IF;
1351 END LOOP;
1352 CLOSE c_rep_quota_credits;
1353
1354 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1355 RAISE FND_API.G_EXC_ERROR;
1356 END IF;
1357
1358 -- Update Lead Line Amounts
1359 l_header_rec.lead_id := p_lead_id;
1360
1361 IF l_ll_tbl_count > 0 THEN
1362 AS_OPPORTUNITY_PUB.Update_Opp_Lines(
1363 p_api_version_number => 2.0,
1364 p_init_msg_list => FND_API.G_FALSE,
1365 p_commit => FND_API.G_FALSE,
1366 p_validation_level => 90,
1367 p_identity_salesforce_id => p_identity_sf_id,
1368 p_line_tbl => l_ll_tbl,
1369 p_header_rec => l_header_rec,
1370 p_check_access_flag => 'N',
1371 p_admin_flag => 'N',
1372 p_admin_group_id => NULL,
1373 p_partner_cont_party_id => NULL,
1374 x_line_out_tbl => l_ll_out_tbl,
1375 x_return_status => x_return_status,
1376 x_msg_count => x_msg_count,
1377 x_msg_data => x_msg_data
1378 );
1379 END IF;
1380
1381 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1382 RAISE FND_API.G_EXC_ERROR;
1383 END IF;
1384
1385 IF (p_debug_flag = 'Y' AND
1386 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1387 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1388 'End Update_sc_for_rep');
1389 END IF;
1390
1391 EXCEPTION
1392 WHEN OTHERS then
1393 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1394 x_return_status := FND_API.G_RET_STS_ERROR;
1395 END IF;
1396 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1397 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
1398 'In When others (Update_sc_for_rep). lead_id: '
1399 || p_lead_id || ' Exception SQlerr is : ' ||
1400 substr(SQLERRM, 1, 1950));
1401 END IF;
1402
1403 End Update_sc_for_rep_line;
1404
1405 PROCEDURE Copy_Opportunity_Line
1406 ( p_api_version_number IN NUMBER,
1407 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1408 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1409 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1410 p_lead_id IN NUMBER,
1411 p_forecast_credit_type_id IN NUMBER,
1412 p_win_probability IN NUMBER,
1413 p_win_loss_indicator IN VARCHAR2,
1414 p_forecast_rollup_flag IN VARCHAR2,
1415 p_lead_line_id IN NUMBER,
1416 p_sales_credit_amount IN NUMBER,
1417 p_identity_salesforce_id IN NUMBER,
1418 p_salesgroup_id IN NUMBER := NULL,
1419 x_return_status OUT NOCOPY VARCHAR2,
1420 x_msg_count OUT NOCOPY NUMBER,
1421 x_msg_data OUT NOCOPY VARCHAR2,
1422 x_lead_line_id OUT NOCOPY NUMBER
1423 )
1424 IS
1425 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Opportunity_Line';
1426 l_api_version_number CONSTANT NUMBER := 2.0;
1427 l_index NUMBER;
1428 l_rowid ROWID;
1429 l_lead_line_id NUMBER;
1430 l_sales_credit_id NUMBER;
1431 l_lead_competitor_id NUMBER;
1432 l_close_competitor_id NUMBER;
1433 l_lead_competitor_prod_id NUMBER;
1434 l_lead_decision_factor_id NUMBER;
1435 l_new_sales_methodology_id NUMBER;
1436
1437
1438
1439 l_customer_id NUMBER;
1440 l_new_status VARCHAR2(30);
1441 l_default_status VARCHAR2(30) := fnd_profile.value('AS_OPP_STATUS');
1442 l_new_total_amount NUMBER;
1443 l_tot_revenue_opp_forecast_amt NUMBER := FND_API.G_MISS_NUM; -- Added for ASNB
1444 l_sales_credit_rec AS_OPPORTUNITY_PUB.Sales_Credit_Rec_type;
1445
1446 l_forecast_credit_type_id NUMBER := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
1447 l_val NUMBER;
1448 l_date DATE;
1449 l_temp_lead_id NUMBER;
1450 l_cre_st_for_sc_flag VARCHAR2(1) := 'N';
1451 l_insert BOOLEAN;
1452 l_new_sales_credit_amount NUMBER;
1453 l_temp_bool BOOLEAN;
1454
1455 CURSOR c_customer(c_lead_id NUMBER) IS
1456 SELECT customer_id
1457 FROM AS_LEADS_ALL
1458 WHERE lead_id = c_lead_id;
1459
1460 CURSOR c_lines(c_lead_id NUMBER,c_lead_line_id NUMBER) IS
1461 SELECT *
1462 FROM AS_LEAD_LINES_ALL
1463 WHERE lead_id = c_lead_id
1464 AND lead_line_id = c_lead_line_id;
1465
1466 CURSOR c_sales_credits(c_lead_id NUMBER, c_lead_line_id NUMBER , c_salesforce_id NUMBER ,c_salesgroup_id NUMBER ) IS
1467 SELECT *
1468 FROM AS_SALES_CREDITS
1469 WHERE lead_id = c_lead_id
1470 AND lead_line_id = c_lead_line_id
1471 AND ( salesforce_id = c_salesforce_id and salesgroup_id = c_salesgroup_id and credit_type_id = p_forecast_credit_type_id )
1472 AND rowNum < 2
1473 UNION
1474 SELECT *
1475 FROM AS_SALES_CREDITS
1476 WHERE lead_id = c_lead_id
1477 AND lead_line_id = c_lead_line_id
1478 AND credit_type_id <> p_forecast_credit_type_id ;
1479
1480 CURSOR c_competitor_products (c_lead_line_id NUMBER) IS
1481 SELECT *
1482 FROM AS_LEAD_COMP_PRODUCTS
1483 WHERE lead_line_id = c_lead_line_id;
1484
1485 CURSOR c_decision_factors(c_lead_line_id NUMBER) IS
1486 SELECT *
1487 FROM AS_LEAD_DECISION_FACTORS
1488 WHERE lead_line_id = c_lead_line_id;
1489
1490 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1491
1492 BEGIN
1493 -- Standard Start of API savepoint
1494 SAVEPOINT COPY_OPPORTUNITY_PVT;
1495
1496 -- Standard call to check for call compatibility.
1497 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1498 p_api_version_number,
1499 l_api_name,
1500 G_PKG_NAME)
1501 THEN
1502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1503 END IF;
1504
1505
1506 -- Initialize message list if p_init_msg_list is set to TRUE.
1507 IF FND_API.to_Boolean( p_init_msg_list )
1508 THEN
1509 FND_MSG_PUB.initialize;
1510 END IF;
1511
1512
1513 -- Debug Message
1514 IF l_debug THEN
1515 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1516 'Private API: ' || l_api_name || ' start');
1517 END IF;
1518
1519
1520 -- Initialize API return status to SUCCESS
1521 x_return_status := FND_API.G_RET_STS_SUCCESS;
1522
1523
1524 -- Copy Opportunity Lines and line details - Sales Credits,
1525 -- Competitor Products and Decision Factors
1526 --
1527
1528 FOR lr IN c_lines(p_lead_id , p_lead_line_id) LOOP
1529 l_lead_line_id := null;
1530
1531 -- Copy lines
1532 AS_LEAD_LINES_PKG.Insert_Row(
1533 px_LEAD_LINE_ID => l_LEAD_LINE_ID,
1534 p_LAST_UPDATE_DATE => SYSDATE,
1535 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1536 p_CREATION_DATE => SYSDATE,
1537 p_CREATED_BY => FND_GLOBAL.USER_ID,
1538 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1539 p_REQUEST_ID => lr.REQUEST_ID,
1540 p_PROGRAM_APPLICATION_ID => lr.PROGRAM_APPLICATION_ID,
1541 p_PROGRAM_ID => lr.PROGRAM_ID,
1542 p_PROGRAM_UPDATE_DATE => lr.PROGRAM_UPDATE_DATE,
1543 p_LEAD_ID => p_lead_id,
1544 p_INTEREST_TYPE_ID => lr.INTEREST_TYPE_ID,
1545 p_PRIMARY_INTEREST_CODE_ID => lr.PRIMARY_INTEREST_CODE_ID,
1546 p_SECONDARY_INTEREST_CODE_ID => lr.SECONDARY_INTEREST_CODE_ID,
1547 p_INTEREST_STATUS_CODE => lr.INTEREST_STATUS_CODE,
1548 p_INVENTORY_ITEM_ID => lr.INVENTORY_ITEM_ID,
1549 p_ORGANIZATION_ID => lr.ORGANIZATION_ID,
1550 p_UOM_CODE => lr.UOM_CODE,
1551 p_QUANTITY => lr.QUANTITY,
1552 p_TOTAL_AMOUNT => p_sales_credit_amount,
1553 p_SALES_STAGE_ID => lr.SALES_STAGE_ID,
1554 p_WIN_PROBABILITY => lr.WIN_PROBABILITY,
1555 p_DECISION_DATE => lr.DECISION_DATE,
1556 p_ORG_ID => lr.ORG_ID,
1557 p_ATTRIBUTE_CATEGORY => lr.ATTRIBUTE_CATEGORY,
1558 p_ATTRIBUTE1 => lr.ATTRIBUTE1,
1559 p_ATTRIBUTE2 => lr.ATTRIBUTE2,
1560 p_ATTRIBUTE3 => lr.ATTRIBUTE3,
1561 p_ATTRIBUTE4 => lr.ATTRIBUTE4,
1562 p_ATTRIBUTE5 => lr.ATTRIBUTE5,
1563 p_ATTRIBUTE6 => lr.ATTRIBUTE6,
1564 p_ATTRIBUTE7 => lr.ATTRIBUTE7,
1565 p_ATTRIBUTE8 => lr.ATTRIBUTE8,
1566 p_ATTRIBUTE9 => lr.ATTRIBUTE9,
1567 p_ATTRIBUTE10 => lr.ATTRIBUTE10,
1568 p_ATTRIBUTE11 => lr.ATTRIBUTE11,
1569 p_ATTRIBUTE12 => lr.ATTRIBUTE12,
1570 p_ATTRIBUTE13 => lr.ATTRIBUTE13,
1571 p_ATTRIBUTE14 => lr.ATTRIBUTE14,
1572 p_ATTRIBUTE15 => lr.ATTRIBUTE15,
1573 p_STATUS_CODE => lr.STATUS_CODE,
1574 p_CHANNEL_CODE => lr.CHANNEL_CODE,
1575 p_QUOTED_LINE_FLAG => lr.QUOTED_LINE_FLAG,
1576 p_PRICE => lr.PRICE,
1577 p_PRICE_VOLUME_MARGIN => lr.PRICE_VOLUME_MARGIN,
1578 p_SHIP_DATE => lr.SHIP_DATE,
1579 p_FORECAST_DATE => lr.FORECAST_DATE,
1580 p_ROLLING_FORECAST_FLAG => lr.ROLLING_FORECAST_FLAG,
1581 p_SOURCE_PROMOTION_ID => lr.SOURCE_PROMOTION_ID,
1582 p_OFFER_ID => lr.OFFER_ID,
1583 p_PRODUCT_CATEGORY_ID => lr.PRODUCT_CATEGORY_ID,
1584 p_PRODUCT_CAT_SET_ID => lr.PRODUCT_CAT_SET_ID);
1585
1586 IF l_lead_line_id is null THEN
1587 IF l_debug THEN
1588 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1589 'Private API: as_lead_lines_pkg.insert_row fail');
1590 END IF;
1591 RAISE FND_API.G_EXC_ERROR;
1592 ELSE
1593 IF l_debug THEN
1594 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1595 ' Private API: as_lead_lines_pkg.insert_row '|| l_lead_line_id);
1596 END IF;
1597 END IF;
1598
1599 -- Copy Sales Credits
1600 FOR scr IN c_sales_credits(p_lead_id, lr.lead_line_id ,p_identity_salesforce_id,p_salesgroup_id) LOOP
1601
1602 l_sales_credit_id := null;
1603
1604 -- removing condition for defaulting only for forecast_credit types
1605 -- bug#4151483
1606 l_new_sales_credit_amount := p_sales_credit_amount;
1607 l_temp_bool := AS_OPP_SALES_CREDIT_PVT.Apply_Forecast_Defaults(P_win_probability,
1608 P_win_loss_indicator, 'N', -11, P_win_probability,
1609 P_win_loss_indicator, P_forecast_rollup_flag,
1610 l_new_sales_credit_amount, 'ON-INSERT',
1611 l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
1612 l_sales_credit_rec.OPP_FORECAST_AMOUNT,
1613 l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT);
1614
1615
1616
1617 AS_SALES_CREDITS_PKG.Insert_Row(
1618 px_SALES_CREDIT_ID => l_SALES_CREDIT_ID,
1619 p_LAST_UPDATE_DATE => SYSDATE,
1620 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1621 p_CREATION_DATE => SYSDATE,
1622 p_CREATED_BY => FND_GLOBAL.USER_ID,
1623 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1624 p_REQUEST_ID => scr.REQUEST_ID,
1625 p_PROGRAM_APPLICATION_ID => scr.PROGRAM_APPLICATION_ID,
1626 p_PROGRAM_ID => scr.PROGRAM_ID,
1627 p_PROGRAM_UPDATE_DATE => scr.PROGRAM_UPDATE_DATE,
1628 p_LEAD_ID => P_LEAD_ID,
1629 p_LEAD_LINE_ID => l_LEAD_LINE_ID,
1630 p_SALESFORCE_ID => scr.SALESFORCE_ID,
1631 p_PERSON_ID => scr.PERSON_ID,
1632 p_SALESGROUP_ID => scr.SALESGROUP_ID,
1633 p_PARTNER_CUSTOMER_ID => scr.PARTNER_CUSTOMER_ID,
1634 p_PARTNER_ADDRESS_ID => scr.PARTNER_ADDRESS_ID,
1635 p_REVENUE_AMOUNT => scr.REVENUE_AMOUNT,
1636 p_REVENUE_PERCENT => scr.REVENUE_PERCENT,
1637 p_QUOTA_CREDIT_AMOUNT => scr.QUOTA_CREDIT_AMOUNT,
1638 p_QUOTA_CREDIT_PERCENT => scr.QUOTA_CREDIT_PERCENT,
1639 p_ATTRIBUTE_CATEGORY => scr.ATTRIBUTE_CATEGORY,
1640 p_ATTRIBUTE1 => scr.ATTRIBUTE1,
1641 p_ATTRIBUTE2 => scr.ATTRIBUTE2,
1642 p_ATTRIBUTE3 => scr.ATTRIBUTE3,
1643 p_ATTRIBUTE4 => scr.ATTRIBUTE4,
1644 p_ATTRIBUTE5 => scr.ATTRIBUTE5,
1645 p_ATTRIBUTE6 => scr.ATTRIBUTE6,
1646 p_ATTRIBUTE7 => scr.ATTRIBUTE7,
1647 p_ATTRIBUTE8 => scr.ATTRIBUTE8,
1648 p_ATTRIBUTE9 => scr.ATTRIBUTE9,
1649 p_ATTRIBUTE10 => scr.ATTRIBUTE10,
1650 p_ATTRIBUTE11 => scr.ATTRIBUTE11,
1651 p_ATTRIBUTE12 => scr.ATTRIBUTE12,
1652 p_ATTRIBUTE13 => scr.ATTRIBUTE13,
1653 p_ATTRIBUTE14 => scr.ATTRIBUTE14,
1654 p_ATTRIBUTE15 => scr.ATTRIBUTE15,
1655 p_MANAGER_REVIEW_FLAG => scr.MANAGER_REVIEW_FLAG,
1656 p_MANAGER_REVIEW_DATE => scr.MANAGER_REVIEW_DATE,
1657 p_ORIGINAL_SALES_CREDIT_ID => scr.ORIGINAL_SALES_CREDIT_ID,
1658 p_CREDIT_PERCENT => 100,
1659 p_CREDIT_AMOUNT => l_new_sales_credit_amount,
1660 p_CREDIT_TYPE_ID => scr.CREDIT_TYPE_ID,
1661 -- The following fields are not passed before ASNB
1662 p_OPP_WORST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
1663 p_OPP_FORECAST_AMOUNT => l_sales_credit_rec.OPP_FORECAST_AMOUNT,
1664 p_OPP_BEST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT,
1665 P_DEFAULTED_FROM_OWNER_FLAG =>scr.DEFAULTED_FROM_OWNER_FLAG -- Added for ASNB
1666 );
1667
1668 IF l_sales_credit_id is null THEN
1669 IF l_debug THEN
1670 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1671 'Private API: as_sales_credits_pkg.insert_row fail');
1672 END IF;
1673
1674 RAISE FND_API.G_EXC_ERROR;
1675 ELSE
1676 IF l_debug THEN
1677 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1678 'Private API: as_sales_credits_pkg.insert_row '|| l_sales_credit_id);
1679 END IF;
1680 END IF;
1681
1682 END LOOP; -- SC loop
1683
1684
1685
1686
1687 -- Copy Competitor Products
1688 FOR cpdr IN c_competitor_products(lr.lead_line_id) LOOP
1689 l_lead_competitor_prod_id := NULL;
1690 -- Invoke table handler(AS_LEAD_COMP_PRODUCTS_PKG.Insert_Row)
1691 AS_LEAD_COMP_PRODUCTS_PKG.Insert_Row(
1692 p_ATTRIBUTE15 => cpdr.ATTRIBUTE15,
1693 p_ATTRIBUTE14 => cpdr.ATTRIBUTE14,
1694 p_ATTRIBUTE13 => cpdr.ATTRIBUTE13,
1695 p_ATTRIBUTE12 => cpdr.ATTRIBUTE12,
1696 p_ATTRIBUTE11 => cpdr.ATTRIBUTE11,
1697 p_ATTRIBUTE10 => cpdr.ATTRIBUTE10,
1698 p_ATTRIBUTE9 => cpdr.ATTRIBUTE9,
1699 p_ATTRIBUTE8 => cpdr.ATTRIBUTE8,
1700 p_ATTRIBUTE7 => cpdr.ATTRIBUTE7,
1701 p_ATTRIBUTE6 => cpdr.ATTRIBUTE6,
1702 p_ATTRIBUTE4 => cpdr.ATTRIBUTE4,
1703 p_ATTRIBUTE5 => cpdr.ATTRIBUTE5,
1704 p_ATTRIBUTE2 => cpdr.ATTRIBUTE2,
1705 p_ATTRIBUTE3 => cpdr.ATTRIBUTE3,
1706 p_ATTRIBUTE1 => cpdr.ATTRIBUTE1,
1707 p_ATTRIBUTE_CATEGORY => cpdr.ATTRIBUTE_CATEGORY,
1708 p_PROGRAM_ID => cpdr.PROGRAM_ID,
1709 p_PROGRAM_UPDATE_DATE => cpdr.PROGRAM_UPDATE_DATE,
1710 p_PROGRAM_APPLICATION_ID => cpdr.PROGRAM_APPLICATION_ID,
1711 p_REQUEST_ID => cpdr.REQUEST_ID,
1712 p_WIN_LOSS_STATUS => cpdr.WIN_LOSS_STATUS,
1713 p_COMPETITOR_PRODUCT_ID => cpdr.COMPETITOR_PRODUCT_ID,
1714 p_LEAD_LINE_ID => l_LEAD_LINE_ID,
1715 p_LEAD_ID => P_LEAD_ID,
1716 px_LEAD_COMPETITOR_PROD_ID => l_LEAD_COMPETITOR_PROD_ID,
1717 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1718 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1719 p_LAST_UPDATE_DATE => SYSDATE,
1720 p_CREATED_BY => FND_GLOBAL.USER_ID,
1721 p_CREATION_DATE => SYSDATE);
1722
1723 IF l_lead_competitor_prod_id is null THEN
1724 IF l_debug THEN
1725 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1726 'Private API: as_lead_comp_products_pkg.insert_row fail');
1727 END IF;
1728
1729 RAISE FND_API.G_EXC_ERROR;
1730 ELSE
1731 IF l_debug THEN
1732 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1733 'Private API: as_lead_comp_products_pkg.insert_row '|| l_lead_competitor_prod_id);
1734 END IF;
1735
1736 END IF;
1737 END LOOP; -- CPD loop
1738
1739 -- Copy Decision Factors
1740 FOR dfcr IN c_decision_factors(lr.lead_line_id) LOOP
1741 l_lead_decision_factor_id := NULL;
1742 AS_LEAD_DECISION_FACTORS_PKG.Insert_Row(
1743 p_ATTRIBUTE15 => dfcr.ATTRIBUTE15,
1744 p_ATTRIBUTE14 => dfcr.ATTRIBUTE14,
1745 p_ATTRIBUTE13 => dfcr.ATTRIBUTE13,
1746 p_ATTRIBUTE12 => dfcr.ATTRIBUTE12,
1747 p_ATTRIBUTE11 => dfcr.ATTRIBUTE11,
1748 p_ATTRIBUTE10 => dfcr.ATTRIBUTE10,
1749 p_ATTRIBUTE9 => dfcr.ATTRIBUTE9,
1750 p_ATTRIBUTE8 => dfcr.ATTRIBUTE8,
1751 p_ATTRIBUTE7 => dfcr.ATTRIBUTE7,
1752 p_ATTRIBUTE6 => dfcr.ATTRIBUTE6,
1753 p_ATTRIBUTE5 => dfcr.ATTRIBUTE5,
1754 p_ATTRIBUTE4 => dfcr.ATTRIBUTE4,
1755 p_ATTRIBUTE3 => dfcr.ATTRIBUTE3,
1756 p_ATTRIBUTE2 => dfcr.ATTRIBUTE2,
1757 p_ATTRIBUTE1 => dfcr.ATTRIBUTE1,
1758 p_ATTRIBUTE_CATEGORY => dfcr.ATTRIBUTE_CATEGORY,
1759 p_PROGRAM_UPDATE_DATE => dfcr.PROGRAM_UPDATE_DATE,
1760 p_PROGRAM_ID => dfcr.PROGRAM_ID,
1761 p_PROGRAM_APPLICATION_ID => dfcr.PROGRAM_APPLICATION_ID,
1762 p_REQUEST_ID => dfcr.REQUEST_ID,
1763 p_DECISION_RANK => dfcr.DECISION_RANK,
1764 p_DECISION_PRIORITY_CODE => dfcr.DECISION_PRIORITY_CODE,
1765 p_DECISION_FACTOR_CODE => dfcr.DECISION_FACTOR_CODE,
1766 px_LEAD_DECISION_FACTOR_ID => l_LEAD_DECISION_FACTOR_ID,
1767 p_LEAD_LINE_ID => l_LEAD_LINE_ID,
1768 p_CREATE_BY => FND_GLOBAL.USER_ID,
1769 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1770 p_LAST_UPDATE_DATE => SYSDATE,
1771 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1772 p_CREATION_DATE => SYSDATE);
1773
1774 IF l_lead_decision_factor_id is null THEN
1775 IF l_debug THEN
1776 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1777 'Private API: as_lead_decision_factors_pkg.insert_row fail');
1778 END IF;
1779
1780 RAISE FND_API.G_EXC_ERROR;
1781 ELSE
1782 IF l_debug THEN
1783 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1784 'Private API: as_lead_decision_factors_pkg.insert_row '|| l_lead_decision_factor_id );
1785 END IF;
1786 END IF;
1787 END LOOP; -- DFC loop
1788
1789 END LOOP; -- line loop
1790
1791
1792
1793
1794 EXCEPTION
1795 WHEN FND_API.G_EXC_ERROR THEN
1796 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1797 P_API_NAME => L_API_NAME
1798 ,P_PKG_NAME => G_PKG_NAME
1799 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1800 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1801 ,X_MSG_COUNT => X_MSG_COUNT
1802 ,X_MSG_DATA => X_MSG_DATA
1803 ,X_RETURN_STATUS => X_RETURN_STATUS);
1804
1805 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1806 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1807 P_API_NAME => L_API_NAME
1808 ,P_PKG_NAME => G_PKG_NAME
1809 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1810 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1811 ,X_MSG_COUNT => X_MSG_COUNT
1812 ,X_MSG_DATA => X_MSG_DATA
1813 ,X_RETURN_STATUS => X_RETURN_STATUS);
1814
1815 WHEN OTHERS THEN
1816 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1817 P_API_NAME => L_API_NAME
1818 ,P_PKG_NAME => G_PKG_NAME
1819 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1820 ,P_SQLCODE => SQLCODE
1821 ,P_SQLERRM => SQLERRM
1822 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1823 ,X_MSG_COUNT => X_MSG_COUNT
1824 ,X_MSG_DATA => X_MSG_DATA
1825 ,X_RETURN_STATUS => X_RETURN_STATUS);
1826
1827 END Copy_Opportunity_Line;
1828
1829 --
1830 --
1831 PROCEDURE Mig_Multi_SalesRep_Opp_Main
1832 (
1833 errbuf OUT NOCOPY VARCHAR2,
1834 retcode OUT NOCOPY NUMBER,
1835 p_num_workers IN NUMBER,
1836 p_commit_flag IN VARCHAR2,
1837 p_debug_flag IN VARCHAR2
1838 )
1839 IS
1840 l_api_name CONSTANT VARCHAR2(30) :=
1841 'Mig_Multi_SalesRep_Opp_Main';
1842 l_module_name CONSTANT VARCHAR2(256) :=
1843 'asn.plsql.asn_mig_sales_credits_pvt.Mig_Multi_SalesRep_Opp_Main';
1844 l_msg_count NUMBER;
1845 l_msg_data VARCHAR2(2000);
1846 l_req_id NUMBER;
1847 l_request_data VARCHAR2(30);
1848 l_max_num_rows NUMBER;
1849 l_rows_per_worker NUMBER;
1850 l_start_id NUMBER;
1851 l_end_id NUMBER;
1852 l_batch_size CONSTANT NUMBER := 10000;
1853
1854 CURSOR c1 IS SELECT as_leads_s.nextval FROM dual;
1855
1856 BEGIN
1857
1858 --
1859 -- If this is first time parent is called, then split the rows
1860 -- among workers and put the parent in paused state
1861 --
1862 IF (fnd_conc_global.request_data IS NULL) THEN
1863
1864 -- Log
1865 IF (p_debug_flag = 'Y' AND
1866 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1867 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1868 'Start:' || 'p_num_workers=' || p_num_workers ||
1869 ',p_commit_flag=' || p_commit_flag ||
1870 ',p_debug_flag=' || p_debug_flag);
1871 END IF;
1872
1873 -- Log
1874 IF (p_debug_flag = 'Y' AND
1875 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1876 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
1877 'Disable AS_SALES_CREDITS_BIUD trigger');
1878 END IF;
1879
1880 --
1881 -- Get maximum number of possible rows in as_leads_all
1882 --
1883 OPEN c1;
1884 FETCH c1 INTO l_max_num_rows;
1885 CLOSE c1;
1886
1887 --
1888 -- Compute row range to be assigned to each worker
1889 --
1890 l_rows_per_worker := ROUND(l_max_num_rows/p_num_workers) + 1;
1891
1892 --
1893 -- Assign rows to each worker
1894 --
1895
1896 -- Initialize start ID value
1897 l_start_id := 0;
1898 FOR i IN 1..p_num_workers LOOP
1899
1900 -- Initialize end ID value
1901 l_end_id := l_start_id + l_rows_per_worker;
1902
1903 -- Submit the request
1904 l_req_id :=
1905 fnd_request.submit_request
1906 (
1907 application => 'ASN',
1908 program => 'ASN_MIG_MULTI_SR_OPP_SUB_PRG',
1909 description => null,
1910 start_time => sysdate,
1911 sub_request => true,
1912 argument1 => l_start_id,
1913 argument2 => l_end_id,
1914 argument3 => p_commit_flag,
1915 argument4 => l_batch_size,
1916 argument5 => p_debug_flag,
1917 argument6 => CHR(0),
1918 argument7 => CHR(0),
1919 argument8 => CHR(0),
1920 argument9 => CHR(0),
1921 argument10 => CHR(0),
1922 argument11 => CHR(0),
1923 argument12 => CHR(0),
1924 argument13 => CHR(0),
1925 argument14 => CHR(0),
1926 argument15 => CHR(0),
1927 argument16 => CHR(0),
1928 argument17 => CHR(0),
1929 argument18 => CHR(0),
1930 argument19 => CHR(0),
1931 argument20 => CHR(0),
1932 argument21 => CHR(0),
1933 argument22 => CHR(0),
1934 argument23 => CHR(0),
1935 argument24 => CHR(0),
1936 argument25 => CHR(0),
1937 argument26 => CHR(0),
1938 argument27 => CHR(0),
1939 argument28 => CHR(0),
1940 argument29 => CHR(0),
1941 argument30 => CHR(0),
1942 argument31 => CHR(0),
1943 argument32 => CHR(0),
1944 argument33 => CHR(0),
1945 argument34 => CHR(0),
1946 argument35 => CHR(0),
1947 argument36 => CHR(0),
1948 argument37 => CHR(0),
1949 argument38 => CHR(0),
1950 argument39 => CHR(0),
1951 argument40 => CHR(0),
1952 argument41 => CHR(0),
1953 argument42 => CHR(0),
1954 argument43 => CHR(0),
1955 argument44 => CHR(0),
1956 argument45 => CHR(0),
1957 argument46 => CHR(0),
1958 argument47 => CHR(0),
1959 argument48 => CHR(0),
1960 argument49 => CHR(0),
1961 argument50 => CHR(0),
1962 argument51 => CHR(0),
1963 argument52 => CHR(0),
1964 argument53 => CHR(0),
1965 argument54 => CHR(0),
1966 argument55 => CHR(0),
1967 argument56 => CHR(0),
1968 argument57 => CHR(0),
1969 argument58 => CHR(0),
1970 argument59 => CHR(0),
1971 argument60 => CHR(0),
1972 argument61 => CHR(0),
1973 argument62 => CHR(0),
1974 argument63 => CHR(0),
1975 argument64 => CHR(0),
1976 argument65 => CHR(0),
1977 argument66 => CHR(0),
1978 argument67 => CHR(0),
1979 argument68 => CHR(0),
1980 argument69 => CHR(0),
1981 argument70 => CHR(0),
1982 argument71 => CHR(0),
1983 argument72 => CHR(0),
1984 argument73 => CHR(0),
1985 argument74 => CHR(0),
1986 argument75 => CHR(0),
1987 argument76 => CHR(0),
1988 argument77 => CHR(0),
1989 argument78 => CHR(0),
1990 argument79 => CHR(0),
1991 argument80 => CHR(0),
1992 argument81 => CHR(0),
1993 argument82 => CHR(0),
1994 argument83 => CHR(0),
1995 argument84 => CHR(0),
1996 argument85 => CHR(0),
1997 argument86 => CHR(0),
1998 argument87 => CHR(0),
1999 argument88 => CHR(0),
2000 argument89 => CHR(0),
2001 argument90 => CHR(0),
2002 argument91 => CHR(0),
2003 argument92 => CHR(0),
2004 argument93 => CHR(0),
2005 argument94 => CHR(0),
2006 argument95 => CHR(0),
2007 argument96 => CHR(0),
2008 argument97 => CHR(0),
2009 argument98 => CHR(0),
2010 argument99 => CHR(0),
2011 argument100 => CHR(0)
2012 );
2013
2014 --
2015 -- If request submission failed, exit with error.
2016 --
2017 IF (l_req_id = 0) THEN
2018
2019 errbuf := fnd_message.get;
2020 retcode := 2;
2021 RETURN;
2022
2023 END IF;
2024
2025 -- Set start ID value
2026 l_start_id := l_end_id + 1;
2027
2028 END LOOP; -- end i
2029
2030 --
2031 -- After submitting request for all workers, put the parent
2032 -- in paused state. When all children are done, the parent
2033 -- would be called again, and then it will terminate
2034 --
2035 fnd_conc_global.set_req_globals
2036 (
2037 conc_status => 'PAUSED',
2038 request_data => to_char(l_req_id) --,
2039 -- conc_restart_time => to_char(sysdate),
2040 -- release_sub_request => 'N'
2041 );
2042
2043 ELSE
2044
2045 -- Log
2046 IF (p_debug_flag = 'Y' AND
2047 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2048 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
2049 'Re-entering:' || 'p_num_workers=' || p_num_workers ||
2050 ',p_commit_flag=' || p_commit_flag ||
2051 ',p_debug_flag='||p_debug_flag);
2052 END IF;
2053
2054 -- Log
2055 IF (p_debug_flag = 'Y' AND
2056 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2057 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
2058 'Enable AS_SALES_CREDITS_BIUD trigger');
2059 END IF;
2060
2061 errbuf := 'Migration completed';
2062 retcode := 0;
2063
2064 -- Log
2065 IF (p_debug_flag = 'Y' AND
2066 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2067 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
2068 'Done:' || 'p_num_workers=' || p_num_workers ||
2069 ',p_commit_flag=' || p_commit_flag ||
2070 ',p_debug_flag='||p_debug_flag);
2071 END IF;
2072
2073 END IF;
2074
2075 EXCEPTION
2076
2077 WHEN OTHERS THEN
2078 ROLLBACK;
2079
2080 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2081
2082 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2083 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2084 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2085 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2086 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, l_module_name, true);
2087 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2088 l_api_name||':'||sqlcode||':'||sqlerrm);
2089 END IF;
2090
2091 END Mig_Multi_SalesRep_Opp_Main;
2092
2093 PROCEDURE Mig_Multi_SalesRep_Opp_sub (
2094 errbuf OUT NOCOPY VARCHAR2,
2095 retcode OUT NOCOPY NUMBER,
2096 p_start_id IN VARCHAR2,
2097 p_end_id IN VARCHAR2,
2098 p_commit_flag IN VARCHAR2,
2099 p_batch_size IN NUMBER,
2100 p_debug_flag IN VARCHAR2
2101 )
2102 IS
2103
2104 l_module_name CONSTANT VARCHAR2(256) :=
2105 'asn.plsql.asn_mig_sales_credits_pvt.Mig_Multi_SalesRep_Opp_sub';
2106
2107 l_org_owner_sf_id NUMBER;
2108 l_org_owner_sg_id NUMBER;
2109 l_org_owner_person_id NUMBER;
2110 l_open_flag VARCHAR2(1);
2111 l_steam_sf_id NUMBER;
2112 l_steam_sg_id NUMBER;
2113 l_steam_owner_flag VARCHAR2(1);
2114 l_found_steam BOOLEAN;
2115
2116 l_forecast_credit_type_id NUMBER;
2117 l_new_lead_id NUMBER;
2118 l_first_sf_id NUMBER;
2119 l_first_sg_id NUMBER;
2120 l_access_id NUMBER;
2121 l_sf_id NUMBER;
2122 l_sg_id NUMBER;
2123 l_user_id NUMBER;
2124 l_uncommitted_opps NUMBER := 0;
2125 l_i NUMBER;
2126 l_found BOOLEAN;
2127 l_found_owner BOOLEAN;
2128 l_proceed_with_opp BOOLEAN;
2129 l_total_percent NUMBER;
2130 l_total_credit NUMBER;
2131 l_line_amount NUMBER;
2132
2133 l_ll_tbl AS_OPPORTUNITY_PUB.Line_Tbl_Type;
2134 l_ll_tbl_count NUMBER;
2135 l_ll_out_tbl AS_OPPORTUNITY_PUB.Line_Out_Tbl_Type;
2136
2137 l_obstacle_tbl AS_OPPORTUNITY_PUB.Obstacle_Tbl_Type;
2138 l_obstacle_tbl_count NUMBER;
2139 l_obstacle_out_tbl AS_OPPORTUNITY_PUB.Obstacle_Out_Tbl_Type;
2140
2141 l_lead_line_id NUMBER;
2142 l_new_lead_line_id NUMBER;
2143 l_note_id NUMBER;
2144 l_new_note_context_id NUMBER;
2145 l_lead_opp_id NUMBER;
2146
2147 l_header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type;
2148
2149 l_return_status VARCHAR2(16);
2150 l_msg_count NUMBER;
2151 l_msg_data VARCHAR2(1024);
2152
2153 l_error_count NUMBER;
2154 l_error_msg VARCHAR2(1024);
2155
2156 l_access_pk_id NUMBER;
2157
2158 TYPE srepgrp_tbl_type IS TABLE OF varchar2(100) INDEX BY BINARY_INTEGER;
2159 l_srepgrp_tbl srepgrp_tbl_type;
2160
2161 l_index_number number(15);
2162 l_process_lead_first varchar2(1);
2163 l_first_rep_group varchar2(100);
2164 l_rep_exist_in_line number;
2165
2166
2167 TYPE NUMBER_TT IS TABLE OF NUMBER;
2168 v_deleted_sfids NUMBER_TT;
2169 v_deleted_sgids NUMBER_TT;
2170
2171
2172 -- Added for ASN.B migration changes
2173 -- Cursor to get all leads.
2174 CURSOR c_leads_in_range(p_credit_type_id NUMBER, p_start_id NUMBER,
2175 p_end_id NUMBER) IS
2176 SELECT distinct lead_id
2177 FROM as_sales_credits
2178 WHERE lead_id BETWEEN p_start_id AND p_end_id;
2179
2180
2181 -- Selects Opps which have Lines with multiple quota Sales Credits.
2182 -- Also includes Opportunities with one or more partner quota credits.
2183 -- This is achieved by sum(decode... in the GROUP BY
2184 -- Only if one line has multiple sales credits will it be selected.
2185 CURSOR c_multicredit_opps(p_lead_id NUMBER,p_credit_type_id NUMBER) IS
2186 SELECT lead_id ,lead_line_id from
2187 (SELECT lead_id,lead_line_id,count(1) numofsalescredit ,
2188 SUM(decode(partner_customer_id, NULL, 0, 1)) isPartnerCredit
2189 FROM as_sales_credits
2190 WHERE lead_id = p_lead_id
2191 AND credit_type_id = p_credit_type_id
2192 GROUP BY lead_id, lead_line_id
2193 ) inlinetab
2194 where isPartnerCredit > 0 or numofsalescredit> 1 order by lead_line_id asc;
2195
2196 CURSOR c_lead(p_lead_id NUMBER) IS
2197 SELECT lead.description, lead.customer_id, lead.address_id, lead.owner_salesforce_id,
2198 lead.owner_sales_group_id, lead.status ,lead.win_probability, status.win_loss_indicator,
2199 status.forecast_rollup_flag ,status.OPP_OPEN_STATUS_FLAG
2200 FROM as_leads_all lead, as_statuses_vl status
2201 WHERE lead_id = p_lead_id
2202 AND lead.status = status.status_code(+);
2203
2204 -- Ordering by preferred candidates for owner in Sales Team
2205 CURSOR c_salesteam(p_lead_id NUMBER) IS
2206 SELECT access_id, salesforce_id, sales_group_id, owner_flag
2207 FROM as_accesses_all
2208 WHERE lead_id = p_lead_id AND partner_customer_id IS NULL
2209 AND partner_cont_party_id IS NULL
2210 ORDER BY nvl(owner_flag, 'N') DESC,
2211 nvl(team_leader_flag, 'N') DESC,
2212 nvl(freeze_flag, 'N') DESC;
2213
2214 CURSOR c_person_id(p_salesforce_id NUMBER) IS
2215 SELECT employee_person_id FROM as_salesforce_v
2216 WHERE salesforce_id = p_salesforce_id;
2217
2218 CURSOR c_partnerqcredits(p_lead_id NUMBER, p_credit_type_id NUMBER)
2219 IS
2220 SELECT salesforce_id FROM as_sales_credits
2221 WHERE lead_id = p_lead_id AND credit_type_id = p_credit_type_id
2222 AND partner_customer_id IS NOT NULL;
2223
2224 -- get those credit revievers who belong to leads lines which
2225 -- have more than one credit lines
2226 CURSOR c_credit_receivers(p_lead_id NUMBER, p_lead_line_id NUMBER,p_credit_type_id NUMBER) IS
2227 SELECT salesforce_id, salesgroup_id ,sum(credit_amount) credit_amount
2228 FROM as_sales_credits
2229 WHERE lead_id = p_lead_id
2230 AND lead_line_id = p_lead_line_id
2231 AND credit_type_id = p_credit_type_id
2232 and exists (select 'x'
2233 FROM as_sales_credits
2234 WHERE lead_id = p_lead_id
2235 AND lead_line_id = p_lead_line_id
2236 AND credit_type_id = p_credit_type_id
2237 GROUP BY lead_id, lead_line_id
2238 HAVING count(*) > 1)
2239 GROUP BY salesforce_id, salesgroup_id ;
2240
2241
2242 CURSOR c_lead_denorm_credits(p_lead_id NUMBER, p_credit_type_id NUMBER) IS
2243 SELECT salesforce_id, sales_group_id, employee_person_id, opp_open_status_flag
2244 FROM as_sales_credits_denorm
2245 WHERE lead_id = p_lead_id AND credit_type_id = p_credit_type_id
2246 AND partner_customer_id IS NULL;
2247
2248 CURSOR c_bad_opp(p_lead_id NUMBER, p_credit_type_id NUMBER) IS
2249 SELECT sc.lead_line_id, sum(sc.credit_percent) total_percent,
2250 sum(sc.credit_amount) total_credit,
2251 max(ll.total_amount) line_amount
2252 FROM as_sales_credits sc, as_lead_lines ll
2253 WHERE sc.lead_id = p_lead_id
2254 AND sc.credit_type_id = p_credit_type_id
2255 AND ll.lead_line_id(+) = sc.lead_line_id
2256 GROUP BY sc.lead_line_id
2257 HAVING (sum(sc.credit_percent) <> 100
2258 OR sum(sc.credit_amount) <> max(ll.total_amount));
2259
2260 CURSOR c_lead_opp_links(p_lead_id NUMBER) IS
2261 SELECT * FROM as_sales_lead_opportunity
2262 WHERE opportunity_id = p_lead_id;
2263
2264 CURSOR c_get_access_id IS
2265 SELECT AS_ACCESSES_S.NEXTVAL
2266 FROM SYS.DUAL;
2267
2268 -- Add person to sales team if he is recieving credits
2269 -- not checking for address or sales role as this is not
2270 -- enterable field in ASN UI
2271 CURSOR c_add_sales_team (p_lead_id NUMBER , p_credit_type_id NUMBER) IS
2272 SELECT DISTINCT opps.lead_id, opps.customer_id, opps.address_id,
2273 ascr.salesforce_id, ascr.person_id,
2274 ascr.SALESGROUP_ID
2275 FROM as_leads_all opps, as_sales_credits ascr
2276 WHERE opps.lead_id = ascr.lead_id
2277 AND opps.lead_id = p_lead_id
2278 --AND ascr.credit_type_id = p_credit_type_id --- both quota and non-revenue credit receivers should be in the sales team
2279 AND NOT EXISTS (
2280 SELECT 'x'
2281 FROM as_accesses_all acc
2282 WHERE opps.lead_id = acc.lead_id
2283 AND acc.SALESFORCE_ID = ascr.SALESFORCE_ID
2284 and NVL(acc.SALES_GROUP_ID,-99) = NVL(ascr.SALESGROUP_ID,-99));
2285
2286 --Code added for ASN MIGRATION PERFORMANCE ---Start
2287 l_MAX_fetches NUMBER := 10000;
2288 TYPE num_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
2289 l_lead_id num_list;
2290
2291 TYPE char_list is TABLE of VARCHAR2(4000) INDEX BY BINARY_INTEGER;
2292 TYPE date_list is TABLE of DATE INDEX BY BINARY_INTEGER;
2293
2294 l_description char_list;
2295 l_customer_id num_list;
2296 l_address_id num_list;
2297 l_org_owner_sf_id1 num_list;
2298 l_org_owner_sg_id1 num_list;
2299 l_status char_list;
2300 l_win_probability num_list;
2301 l_win_loss_indicator char_list;
2302 l_forecast_rollup_flag char_list;
2303 l_open_status_flag char_list;
2304 --l_org_owner_person_id num_list;
2305 l_employee_person_id num_list;
2306
2307 l_lead_id_multicredit num_list;
2308 l_lead_line_id_multicredit num_list;
2309 l_sf_id1 num_list;
2310
2311 l_opps_lead_id num_list;
2312 l_opp_rec_lead_id num_list;
2313 l_opp_rec_customer_id num_list;
2314 l_opp_rec_address_id num_list;
2315 l_opp_rec_salesforce_id num_list;
2316 l_opp_rec_person_id num_list;
2317 l_opp_rec_SALESGROUP_ID num_list;
2318 --l_access_pk_id num_list;
2319
2320 l_bulk_errors NUMBER;
2321 l_bulk_errors_idx NUMBER;
2322
2323 BEGIN
2324
2325 -- Log
2326 IF (p_debug_flag = 'Y' AND
2327 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2328 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
2329 'Begin OSO->ASN Multiple Sales Credits Opportunity Data Migration.');
2330 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,
2331 'Start:' || 'p_start_id=' || p_start_id ||
2332 ',p_end_id='||p_end_id ||
2333 ',p_debug_flag='||p_debug_flag);
2334 END IF;
2335
2336 l_user_id := FND_GLOBAL.user_id;
2337
2338 IF l_user_id IS NULL THEN
2339 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2340 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module_name,
2341 'Error: Global User Id is not set');
2342 END IF;
2343 RETURN;
2344 END IF;
2345
2346 -- Step 1. Initialize p_credit_type_id (l_forecast_credit_type_id in code)
2347 l_forecast_credit_type_id := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
2348 IF l_forecast_credit_type_id IS NULL THEN
2349 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2350 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module_name,
2351 'Error: Profile AS_FORECAST_CREDIT_TYPE_ID is not set');
2352 END IF;
2353 RETURN;
2354 END IF;
2355
2356 -- Log
2357 IF (p_debug_flag = 'Y' AND
2358 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2359 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2360 'l_forecast_credit_type_id =' || l_forecast_credit_type_id);
2361 END IF;
2362
2363 -- Step 1.a. Set profile options to avoid errors during Copy Opp.
2364 FND_PROFILE.PUT('AS_COMPETITOR_REQUIRED', 'N');
2365 FND_PROFILE.PUT('AS_OPP_SOURCE_CODE_REQUIRED', 'N');
2366 FND_PROFILE.PUT('AS_OPP_ADDRESS_REQUIRED', 'N');
2367 FND_PROFILE.PUT('AS_ENABLE_OPP_ONLINE_TAP', 'N');
2368 FND_PROFILE.PUT('AS_ALLOW_UPDATE_FROZEN_OPP', 'Y');
2369 FND_PROFILE.PUT('AS_MAX_DAY_CLOSE_OPPORTUNITY', 1000000);
2370 -- To avoid API_NO_ACC_MGR_PRIVILEGE error in AS_ACCESS_PVT when copying
2371 -- Sales Team with 'AM' as role.
2372 FND_PROFILE.PUT('AS_CUST_ACCESS', 'F');
2373
2374
2375 -- Added for ASN.B migration changes
2376 -- Go thru all leads getting credit , as we need to update the sales team to sync
2377 -- with the persons gettting credit as well update the non revenue to 100%
2378
2379 OPEN c_leads_in_range(l_forecast_credit_type_id, p_start_id, p_end_id);
2380 LOOP
2381
2382 FETCH c_leads_in_range BULK COLLECT INTO l_lead_id LIMIT l_MAX_fetches;
2383
2384 -- Step 2. Identify Opportunities with multiple Sales Reps getting quota
2385 -- Sales Credits
2386 BEGIN
2387
2388 savepoint CURR_OPP;
2389 l_process_lead_first := 'Y';
2390
2391 l_org_owner_person_id := NULL;
2392
2393 FOR I IN l_lead_id.first..l_lead_id.last LOOP
2394
2395 OPEN c_lead(l_lead_id(i));
2396 FETCH c_lead INTO l_description(i), l_customer_id(i), l_address_id(i),
2397 l_org_owner_sf_id1(i), l_org_owner_sg_id1(i), l_status(i),l_win_probability(i),
2398 l_win_loss_indicator(i),l_forecast_rollup_flag(i),l_open_status_flag(i);
2399 l_proceed_with_opp := c_lead%FOUND;
2400 CLOSE c_lead;
2401
2402 If l_org_owner_sf_id1.count >0 then
2403 OPEN c_person_id(l_org_owner_sf_id1(i));
2404 FETCH c_person_id INTO l_employee_person_id(i);
2405 CLOSE c_person_id;
2406 end if;
2407 END LOOP;
2408
2409 FORALL I IN l_lead_id.first..l_lead_id.last
2410 update as_lead_lines_all
2411 set forecast_date = NULL, rolling_forecast_flag = 'N' ,
2412 last_updated_by = FND_GLOBAL.user_id,
2413 last_update_date = sysdate,
2414 last_update_login = FND_GLOBAL.conc_login_id
2415 where lead_id = l_lead_id(i) and rolling_forecast_flag = 'Y';
2416
2417 --Place for Bad Data --- Start
2418 FOR I IN l_lead_id.first..l_lead_id.last
2419 LOOP
2420 -- Check for bad data
2421 OPEN c_bad_opp(l_lead_id(i), l_forecast_credit_type_id);
2422 FETCH c_bad_opp INTO l_lead_line_id, l_total_percent,
2423 l_total_credit, l_line_amount;
2424 l_proceed_with_opp := c_bad_opp%NOTFOUND;
2425 CLOSE c_bad_opp;
2426 IF NOT l_proceed_with_opp THEN
2427 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2428 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module_name,
2429 'Error: Skipping Opp Id: ' || l_lead_id(i)
2430 || 'with bad Sales Credits. For Line Id: '
2431 || l_lead_line_id || ', Total Credit Percent: '
2432 || l_total_percent || ' <> 100 OR Total Credit Amount: '
2433 || l_total_credit || ' <> Line amount: ' || l_line_amount);
2434 END IF;
2435 RETURN;
2436 END IF;
2437 END LOOP;
2438 --Place for Bad Data --- End
2439
2440 FOR I IN l_lead_id.first..l_lead_id.last LOOP
2441 IF (p_debug_flag = 'Y' AND
2442 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2443 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2444 'processing lead_id '||l_lead_id(i));
2445 END IF;
2446 END LOOP;
2447
2448 -- make sure all non quota credits are 100% for the existing opportunity
2449 FORALL I IN l_lead_id.first..l_lead_id.last
2450 update as_sales_Credits ascr
2451 set CREDIT_PERCENT = 100 ,
2452 CREDIT_AMOUNT = (select total_amount
2453 from as_lead_lines oppl
2454 where oppl.lead_id = ascr.lead_id
2455 and oppl.lead_line_id = ascr.lead_line_id ),
2456 last_updated_by = FND_GLOBAL.user_id,
2457 last_update_date = sysdate,
2458 last_update_login = FND_GLOBAL.conc_login_id
2459 where ascr.lead_id = l_lead_id(i) and NVL(CREDIT_PERCENT,0) <> 100
2460 and CREDIT_TYPE_ID in
2461 ( select SALES_CREDIT_TYPE_ID
2462 from oe_sales_credit_types
2463 where QUOTA_FLAG = 'N');
2464
2465 -- delete duplicate non quota credits
2466 -- This is repeated inside the loop to
2467 -- ensure that if partner migration causes
2468 -- duplicate it is removed again .
2469 FORALL I IN l_lead_id.first..l_lead_id.last
2470 DELETE FROM as_sales_credits where sales_credit_id IN
2471 (SELECT sales_credit_id
2472 FROM as_sales_credits ascr,
2473 (
2474 SELECT lead_id,lead_line_id,
2475 salesforce_id,salesgroup_id,
2476 credit_type_id,
2477 max(sales_credit_id) maxid
2478 FROM as_sales_credits ascr1
2479 WHERE ascr1.lead_id = l_lead_id(i)
2480 AND ascr1.credit_type_id in
2481 ( SELECT sales_credit_type_id
2482 FROM oe_sales_credit_types
2483 WHERE quota_flag = 'N')
2484 GROUP BY lead_id,lead_line_id,salesforce_id,salesgroup_id,credit_type_id
2485 HAVING COUNT(sales_credit_id) > 1
2486 ) duplines
2487 WHERE ascr.lead_id = duplines.lead_id
2488 AND ascr.lead_line_id = duplines.lead_line_id
2489 AND ascr.salesforce_id = duplines.salesforce_id
2490 AND ascr.salesgroup_id = duplines.salesgroup_id
2491 AND ascr.credit_type_id = duplines.credit_type_id
2492 AND ascr.SALES_CREDIT_ID <> maxid);
2493
2494 -- Update full access flag in as_accesses_all for this opportunity
2495 -- bug#4150276 and as per wenxia's email 28 Jan 2005 18:30:21 -0800
2496 FORALL I IN l_lead_id.first..l_lead_id.last
2497 UPDATE as_accesses_all acc
2498 SET acc.team_leader_flag = 'Y',
2499 last_updated_by = FND_GLOBAL.user_id,
2500 last_update_date = sysdate,
2501 last_update_login = FND_GLOBAL.conc_login_id
2502 WHERE acc.lead_id is not null
2503 AND acc.lead_id = l_lead_id(i)
2504 AND nvl(acc.team_leader_flag,'N') <> 'Y'
2505 AND (
2506 EXISTS
2507 ( SELECT 1
2508 FROM as_sales_credits asc1
2509 WHERE asc1.lead_id = acc.lead_id
2510 AND asc1.salesforce_id = acc.salesforce_id
2511 AND asc1.salesgroup_id = acc.sales_group_id )
2512 OR acc.owner_flag = 'Y');
2513
2514 -- delete 0% quota credits
2515 FORALL I IN l_lead_id.first..l_lead_id.last
2516 DELETE FROM as_sales_credits
2517 WHERE lead_id = l_lead_id(i)
2518 AND credit_type_id = l_forecast_credit_type_id
2519 AND NVL(CREDIT_PERCENT,0) = 0 ;
2520
2521
2522 FOR J IN l_lead_id.first..l_lead_id.last LOOP
2523 l_process_lead_first := 'Y';
2524 l_org_owner_sf_id :=l_org_owner_sf_id1(j);
2525 l_org_owner_person_id := l_employee_person_id(j);
2526 l_org_owner_sg_id :=l_org_owner_sg_id1(j);
2527
2528 FOR multicredit_opps_rec IN c_multicredit_opps(l_lead_id(j),l_forecast_credit_type_id)
2529 LOOP
2530
2531 IF (p_debug_flag = 'Y' AND
2532 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2533 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2534 'processing lead_id line id'||multicredit_opps_rec.lead_line_id);
2535 END IF;
2536
2537 IF (p_debug_flag = 'Y' AND
2538 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2539 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2540 'Processing Opp Id: ' || l_lead_id(j));
2541 END IF;
2542
2543 IF l_process_lead_first = 'Y' THEN
2544 l_process_lead_first := 'N';
2545
2546 OPEN c_partnerqcredits(l_lead_id(j), l_forecast_credit_type_id);
2547 FETCH c_partnerqcredits INTO l_sf_id;
2548 l_found := c_partnerqcredits%FOUND;
2549 CLOSE c_partnerqcredits;
2550
2551 IF l_found THEN
2552 -- Step 3.b.i If the opportunity owner is NULL then assign a
2553 -- a person from the Sales Team as the owner.
2554 IF l_org_owner_sf_id1(j) IS NULL THEN
2555
2556 l_found_owner := FALSE;
2557 -- First query Sales Team and check if owner is present
2558 OPEN c_salesteam(l_lead_id(j));
2559 FETCH c_salesteam INTO l_access_id, l_steam_sf_id,
2560 l_steam_sg_id, l_steam_owner_flag;
2561 l_found_steam := c_salesteam%FOUND;
2562 CLOSE c_salesteam;
2563
2564
2565 IF l_found_steam AND l_steam_owner_flag = 'Y' THEN
2566 l_org_owner_sf_id := l_steam_sf_id;
2567 l_org_owner_sg_id := l_steam_sg_id;
2568 l_found_owner := TRUE;
2569 END IF;
2570
2571 -- Then try to assign a quota credit holder from the
2572 -- Sales Credits Denorm Table
2573 IF NOT l_found_owner THEN
2574
2575 OPEN c_lead_denorm_credits(l_lead_id(j),
2576 l_forecast_credit_type_id);
2577 FETCH c_lead_denorm_credits
2578 INTO l_sf_id, l_sg_id,
2579 l_org_owner_person_id, l_open_flag;
2580 l_found_owner := c_lead_denorm_credits%FOUND;
2581 CLOSE c_lead_denorm_credits;
2582
2583 IF l_found_owner THEN
2584
2585 l_org_owner_sf_id := l_sf_id;
2586 l_org_owner_sg_id := l_sg_id;
2587 -- Reset current owner flag.
2588 UPDATE as_accesses_all
2589 SET owner_flag = 'N' ,
2590 last_updated_by = FND_GLOBAL.user_id,
2591 last_update_date = sysdate,
2592 last_update_login = FND_GLOBAL.conc_login_id
2593 WHERE lead_id = l_lead_id(j) AND owner_flag = 'Y';
2594
2595 -- Reassign owner flag;
2596 UPDATE as_accesses_all
2597 SET owner_flag = 'Y', team_leader_flag = 'Y',
2598 freeze_flag = 'Y',
2599 last_updated_by = FND_GLOBAL.user_id,
2600 last_update_date = sysdate,
2601 last_update_login = FND_GLOBAL.conc_login_id
2602 WHERE lead_id = l_lead_id(j) AND
2603 salesforce_id = l_org_owner_sf_id AND
2604 nvl(sales_group_id, -37) = nvl(l_org_owner_sg_id, -37);
2605
2606 IF SQL%NOTFOUND THEN
2607
2608 INSERT INTO
2609 as_accesses_all
2610 (
2611 access_id
2612 ,last_update_date
2613 ,last_updated_by
2614 ,creation_date
2615 ,created_by
2616 ,last_update_login
2617 ,reassign_flag
2618 ,team_leader_flag
2619 ,customer_id
2620 ,salesforce_id
2621 ,person_id
2622 ,partner_customer_id
2623 ,lead_id
2624 ,sales_group_id
2625 ,partner_cont_party_id
2626 ,owner_flag
2627 ,created_by_tap_flag
2628 ,open_flag
2629 ,freeze_flag
2630 ,org_id
2631 ,object_version_number
2632 )
2633 VALUES(
2634 AS_ACCESSES_S.nextval
2635 ,sysdate
2636 ,FND_GLOBAL.user_id
2637 ,sysdate
2638 ,FND_GLOBAL.user_id
2639 ,FND_GLOBAL.conc_login_id
2640 ,NULL
2641 ,'Y'
2642 ,l_customer_id(j)
2643 ,l_org_owner_sf_id
2644 ,l_org_owner_person_id
2645 ,NULL
2646 ,l_lead_id(j)
2647 ,l_org_owner_sg_id
2648 ,NULL
2649 ,'Y'
2650 ,'N'
2651 ,l_open_flag
2652 ,'Y'
2653 ,NULL
2654 ,1
2655 );
2656 END IF;
2657 END IF;
2658 END IF;
2659
2660
2661 IF NOT l_found_owner THEN
2662 -- If No owner in SalesTeam found and No
2663 -- quota credit holder found in Sales Credits
2664 -- Denorm table then pick someone from the
2665 -- Sales Team as the owner
2666
2667 IF l_found_steam THEN
2668
2669 l_found_owner := TRUE;
2670 l_org_owner_sf_id := l_steam_sf_id;
2671 l_org_owner_sg_id := l_steam_sg_id;
2672
2673 UPDATE AS_ACCESSES_ALL
2674 SET owner_flag = 'Y', team_leader_flag = 'Y',
2675 freeze_flag = 'Y',
2676 last_updated_by = FND_GLOBAL.user_id,
2677 last_update_date = sysdate,
2678 last_update_login = FND_GLOBAL.conc_login_id
2679 WHERE access_id = l_access_id;
2680 END IF;
2681 END IF;
2682
2683 IF l_found_owner THEN
2684
2685 UPDATE as_leads_all
2686 SET owner_salesforce_id = l_org_owner_sf_id,
2687 owner_sales_group_id = l_org_owner_sg_id,
2688 last_updated_by = FND_GLOBAL.user_id,
2689 last_update_date = sysdate,
2690 last_update_login = FND_GLOBAL.conc_login_id
2691 WHERE lead_id = l_lead_id(j);
2692
2693 IF (p_debug_flag = 'Y' AND
2694 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2695 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2696 'Successfully Assigned Salesforce Id: '
2697 || l_org_owner_sf_id || ' SlsGrp Id: '
2698 || l_org_owner_sg_id
2699 || ' For NULL owner in Opp Header');
2700 END IF;
2701 ELSE
2702
2703 -- Log error message and continue to next Opportunity
2704 -- if owner could not be assigned.
2705 IF l_org_owner_sf_id IS NULL THEN
2706 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2707 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, l_module_name,
2708 'Error: Cannot reassign Partner Credits for Opportunity Id'
2709 || l_lead_id(j)
2710 || '. It does not have an owner or an employee in the SalesTeam who can be assigned as the owner');
2711 END IF;
2712 RAISE FND_API.G_EXC_ERROR;
2713 END IF;
2714 END IF;
2715 END IF;
2716
2717 -- Step 3.b.ii Reassign partner quota credits to Opp owner
2718 UPDATE as_sales_credits
2719 SET salesforce_id = l_org_owner_sf_id,
2720 salesgroup_id = l_org_owner_sg_id,
2721 person_id = l_org_owner_person_id,
2722 partner_customer_id = NULL, partner_address_id = NULL,
2723 last_updated_by = FND_GLOBAL.user_id,
2724 last_update_date = sysdate,
2725 last_update_login = FND_GLOBAL.conc_login_id
2726 WHERE lead_id = l_lead_id(j)
2727 AND credit_type_id = l_forecast_credit_type_id
2728 AND partner_customer_id IS NOT NULL;
2729 END IF;
2730 END IF;
2731
2732
2733 -- Step 3.c Get the different salesrep and sales group on the
2734 -- opportunity in this line and loop
2735 l_first_sf_id := -37;
2736
2737 --loop thru thoses lines for this opportunity which have more then one credit recievers
2738
2739 FOR credit_receiver_rec IN
2740 c_credit_receivers(l_lead_id(j),multicredit_opps_rec.lead_line_id, l_forecast_credit_type_id)
2741 LOOP
2742
2743 l_sf_id := credit_receiver_rec.salesforce_id;
2744 l_sg_id := credit_receiver_rec.salesgroup_id;
2745
2746 IF (p_debug_flag = 'Y' AND
2747 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2748 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2749 'Processing SalesForceId: ' || l_sf_id ||
2750 ', SalesGroupId: ' || l_sg_id);
2751 END IF;
2752
2753
2754 -- Check if we are processing the first line for the first time for this lead
2755 -- if so then this salesrep id is our first salesrep id and he will given
2756 -- priority in all lines from here
2757 IF l_first_sf_id = -37 THEN
2758 l_first_sf_id := l_sf_id;
2759 l_first_sg_id := l_sg_id;
2760
2761 IF (p_debug_flag = 'Y' AND
2762 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2763 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2764 'firstsf-37 lprocess lead first Y '||multicredit_opps_rec.lead_line_id||'-' ||l_sf_id);
2765 END IF;
2766
2767 ELSE
2768
2769 -- Step 3.e For each subsequent salesreps (p_sf_id(i)) and
2770 -- salesgroups (p_sg_id(i)), do
2771 -- Step 3.e.i Copy Opportunity
2772
2773 Copy_Opportunity_Line
2774 ( p_api_version_number => 2.0,
2775 p_init_msg_list => FND_API.G_FALSE,
2776 p_commit => FND_API.G_FALSE,
2777 p_validation_level => 90,
2778 p_lead_id => l_lead_id(j),
2779 p_forecast_credit_type_id => l_forecast_credit_type_id,
2780 p_win_probability => l_win_probability(j),
2781 p_win_loss_indicator => l_win_loss_indicator(j),
2782 p_forecast_rollup_flag => l_forecast_rollup_flag(j),
2783 p_lead_line_id => multicredit_opps_rec.lead_line_id ,
2784 p_sales_credit_amount => credit_receiver_rec.credit_amount,
2785 p_identity_salesforce_id => credit_receiver_rec.salesforce_id,
2786 p_salesgroup_id => credit_receiver_rec.salesgroup_id,
2787 x_return_status => l_return_status,
2788 x_msg_count => l_msg_count,
2789 x_msg_data => l_msg_data,
2790 x_lead_line_id => l_new_lead_line_id
2791 );
2792
2793
2794
2795 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2796 RAISE FND_API.G_EXC_ERROR;
2797 END IF;
2798
2799 IF (p_debug_flag = 'Y' AND
2800 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2801 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2802 'Got Copied Opp Id: ' || l_new_lead_id);
2803 END IF;
2804
2805
2806
2807 END IF;
2808 END LOOP;
2809
2810
2811 -- Step 4. Process Original Opportunity
2812 IF l_first_sf_id <> -37 THEN
2813
2814 Update_sc_for_rep_line (l_lead_id(j),multicredit_opps_rec.lead_line_id, l_first_sf_id, l_first_sg_id,
2815 l_forecast_credit_type_id, l_org_owner_sf_id,
2816 p_debug_flag, l_return_status, l_msg_count, l_msg_data);
2817
2818 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2819 RAISE FND_API.G_EXC_ERROR;
2820 END IF;
2821 END IF;
2822
2823
2824 END LOOP;
2825
2826
2827 -- for the existing opportunity check and make sure that all peopl recieving sales credits
2828 -- are there in the sales team.
2829 FOR opp_rec IN c_add_sales_team(l_lead_id(j) ,l_forecast_credit_type_id) LOOP
2830 OPEN c_get_access_id;
2831 FETCH c_get_access_id INTO l_access_pk_id;
2832 CLOSE c_get_access_id;
2833
2834 INSERT INTO AS_ACCESSES_ALL
2835 (ACCESS_ID,
2836 ACCESS_TYPE,
2837 SALESFORCE_ID,
2838 SALES_GROUP_ID,
2839 PERSON_ID,
2840 CUSTOMER_ID,
2841 ADDRESS_ID,
2842 LEAD_ID,
2843 FREEZE_FLAG,
2844 REASSIGN_FLAG,
2845 TEAM_LEADER_FLAG,
2846 LAST_UPDATE_DATE,
2847 LAST_UPDATED_BY,
2848 CREATION_DATE,
2849 CREATED_BY,
2850 LAST_UPDATE_LOGIN,
2851 PROGRAM_APPLICATION_ID,
2852 PROGRAM_ID,
2853 PROGRAM_UPDATE_DATE,
2854 object_version_number,
2855 OPEN_FLAG)
2856 VALUES
2857 (l_access_pk_id,
2858 'X',
2859 opp_rec.salesforce_id,
2860 opp_rec.salesgroup_id,
2861 opp_rec.person_id,
2862 opp_rec.customer_id,
2863 opp_rec.address_id,
2864 opp_rec.lead_id,
2865 'Y',
2866 'N',
2867 'Y',
2868 SYSDATE,
2869 FND_GLOBAL.USER_ID,
2870 SYSDATE,
2871 FND_GLOBAL.USER_ID,
2872 FND_GLOBAL.CONC_LOGIN_ID,
2873 FND_GLOBAL.PROG_APPL_ID,
2874 FND_GLOBAL.CONC_PROGRAM_ID,
2875 SYSDATE,
2876 1.0,
2877 l_open_status_flag(j));
2878 END LOOP;
2879
2880 -- Reassign partner non-quota credits to Opp owner
2881 IF l_org_owner_sf_id IS NOT NULL and l_org_owner_sg_id IS NOT NULL THEN
2882 UPDATE as_sales_credits
2883 SET salesforce_id = l_org_owner_sf_id,
2884 salesgroup_id = l_org_owner_sg_id,
2885 person_id = l_org_owner_person_id,
2886 partner_customer_id = NULL, partner_address_id = NULL,
2887 last_updated_by = FND_GLOBAL.user_id,
2888 last_update_date = sysdate,
2889 last_update_login = FND_GLOBAL.conc_login_id
2890 WHERE lead_id = l_lead_id(j)
2891 AND credit_type_id <> l_forecast_credit_type_id
2892 AND partner_customer_id IS NOT NULL;
2893 END IF;
2894
2895 END LOOP;
2896
2897 -- make sure all sales credit line of the owner of the opp
2898 -- has the DEFAULTED_FROM_OWNER_FLAG flag set
2899 FORALL I IN l_lead_id.first..l_lead_id.last
2900 update as_sales_Credits ascr
2901 set DEFAULTED_FROM_OWNER_FLAG = 'Y',
2902 last_updated_by = FND_GLOBAL.user_id,
2903 last_update_date = sysdate,
2904 last_update_login = FND_GLOBAL.conc_login_id
2905 where ascr.lead_id = l_lead_id(i)
2906 and NVL(DEFAULTED_FROM_OWNER_FLAG,'N') <> 'Y'
2907 and (SALESFORCE_ID ,SALESGROUP_ID) in
2908 (SELECT owner_salesforce_id,owner_sales_group_id
2909 FROM as_leads_all ala
2910 WHERE ala.lead_id = l_lead_id(i) )
2911 and credit_type_id = l_forecast_credit_type_id;
2912
2913 -- Reassign partner non-quota credits to Opp owner
2914 --added inside loop above.
2915
2916 -- delete duplicate non quota credits
2917 FORALL I IN l_lead_id.first..l_lead_id.last
2918 DELETE FROM as_sales_credits where sales_credit_id IN
2919 (SELECT sales_credit_id
2920 FROM as_sales_credits ascr,
2921 (
2922 SELECT lead_id,lead_line_id,
2923 salesforce_id,salesgroup_id,
2924 credit_type_id,
2925 max(sales_credit_id) maxid
2926 FROM as_sales_credits ascr1
2927 WHERE ascr1.lead_id = l_lead_id(i)
2928 AND ascr1.credit_type_id in
2929 ( SELECT sales_credit_type_id
2930 FROM oe_sales_credit_types
2931 WHERE quota_flag = 'N')
2932 GROUP BY lead_id,lead_line_id,salesforce_id,salesgroup_id,credit_type_id
2933 HAVING COUNT(sales_credit_id) > 1
2934 ) duplines
2935 WHERE ascr.lead_id = duplines.lead_id
2936 AND ascr.lead_line_id = duplines.lead_line_id
2937 AND ascr.salesforce_id = duplines.salesforce_id
2938 AND ascr.salesgroup_id = duplines.salesgroup_id
2939 AND ascr.credit_type_id = duplines.credit_type_id
2940 AND ascr.SALES_CREDIT_ID <> maxid);
2941
2942 FOR I IN l_lead_id.first..l_lead_id.last LOOP
2943 IF (p_debug_flag = 'Y' AND
2944 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2945 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2946 'Successfully processed Opp Id: ' || l_lead_id(i));
2947 END IF;
2948 l_uncommitted_opps := l_uncommitted_opps + 1;
2949 IF l_uncommitted_opps >= p_batch_size THEN
2950 IF p_commit_flag = 'Y' THEN
2951 IF (p_debug_flag = 'Y' AND
2952 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2953 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2954 'Calling Commit after processing ' || l_uncommitted_opps || ' Opportunities');
2955 END IF;
2956 COMMIT;
2957 ELSE
2958 ROLLBACK;
2959 END IF;
2960 l_uncommitted_opps := 0;
2961 END IF;
2962 END LOOP;
2963
2964 EXCEPTION
2965 WHEN NO_DATA_FOUND THEN
2966 Rollback to CURR_OPP;
2967
2968 IF (p_debug_flag = 'Y' AND
2969 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2970 l_bulk_errors := SQL%BULK_EXCEPTIONS.COUNT;
2971 FOR i IN 1..l_bulk_errors LOOP
2972 l_bulk_errors_idx :=SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
2973 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
2974 'Ignoring Non Existent Opp Id: ' || l_lead_id(l_bulk_errors_idx));
2975 COMMIT;
2976 END LOOP;
2977 END IF;
2978 WHEN OTHERS then
2979 Rollback to CURR_OPP;
2980
2981 l_bulk_errors := SQL%BULK_EXCEPTIONS.COUNT;
2982 FOR i IN 1..l_bulk_errors LOOP
2983 l_bulk_errors_idx :=SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
2984
2985 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2986 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2987 'Error Processing Opp Id : ' || l_lead_id(l_bulk_errors_idx));
2988
2989 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2990 'Begin Error Info, lead_id: ' || l_lead_id(l_bulk_errors_idx));
2991
2992 fnd_msg_pub.count_and_get( p_encoded => 'F'
2993 ,p_count => l_error_count
2994 ,p_data => l_error_msg);
2995
2996 l_i := 0;
2997
2998 IF l_error_count > 0 THEN
2999 IF l_error_count > 10 THEN
3000 l_i := l_error_count - 10;
3001 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
3002 'Last 10 API Messages, lead_id:' || l_lead_id(l_bulk_errors_idx));
3003
3004 ELSE
3005 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3006 l_module_name,
3007 'API Messages, lead_id:' || l_lead_id(l_bulk_errors_idx));
3008 END IF;
3009 END IF;
3010
3011 WHILE l_i < l_error_count LOOP
3012
3013 l_i := l_i + 1;
3014 l_error_msg := fnd_msg_pub.get(p_msg_index => l_i,
3015 p_encoded => 'F');
3016 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
3017 l_module_name, '(lead_id: ' || l_lead_id(l_bulk_errors_idx) ||
3018 '): ' || substr(l_error_msg,1,1950));
3019
3020 END LOOP;
3021
3022 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
3023 'SQL Error Msg, lead_id: ' || l_lead_id(l_bulk_errors_idx) || ': '
3024 || substr(SQLERRM, 1, 1950));
3025
3026 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
3027 'End Error Info, lead_id: ' || l_lead_id(l_bulk_errors_idx));
3028
3029 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
3030 'ERROR PROCESSING Opp Id: ' || l_lead_id(l_bulk_errors_idx));
3031
3032 END IF;
3033
3034 END LOOP;
3035 END;
3036
3037 EXIT WHEN c_leads_in_range%NOTFOUND;
3038 END LOOP;
3039 CLOSE c_leads_in_range;
3040
3041 --Commit;
3042 IF (p_commit_flag = 'Y') THEN
3043 -- Log
3044 IF (p_debug_flag = 'Y' AND
3045 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3046 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
3047 'Committing');
3048 END IF;
3049
3050 COMMIT;
3051 ELSE
3052 -- Log
3053 IF (p_debug_flag = 'Y' AND
3054 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3055 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
3056 'Rolling back');
3057 END IF;
3058 ROLLBACK;
3059 END IF;
3060
3061 IF (p_debug_flag = 'Y' AND
3062 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3063 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name, 'End of OSO->ASN Multiple Sales Credits Opportunity Data Migration.');
3064 END IF;
3065
3066 End Mig_Multi_SalesRep_Opp_sub;
3067
3068 END asn_mig_sales_credits_pvt;