4 -- HISTORY
1 PACKAGE BODY AS_PIPELINE_MOVE_PUB as
2 /* $Header: asxppmvb.pls 120.0 2005/06/02 17:23:29 appldev noship $ */
3 --
5 -- 02/27/01 ACNG Created.
6 -- NOTES
7 -- The main package for the concurrent program "Pipeline movement"
8 --
9 /************************************************************/
10 /* This script is used to move sales credits and access */
11 /* records from one salesforce to another salesforce */
12 /* Input required : User login (from which user ) */
13 /* group number (from which salesgroup) */
14 /* User login ( to which user ) */
15 /* group number ( to which salesgroup) */
16 /* win probability (win probability range) */
17 /* decision date (close date range ) */
18 /* status (statuses ) */
19 /************************************************************/
20 /********************************************************************************/
21 /* Instruction to run this SQL script, parameters are in sequence */
22 /* 1) from_user_name (move from which salesforce) */
23 /* 2) to_user_name (move to which salesforce) */
24 /* 3) from_group_num (move from group where the salesforce belongs to) */
25 /* 4) to_group_num (move to group where the salesforce belongs to) */
26 /* 5) from_win_prob (move sales credits with win prob range starts from) */
27 /* Default value = 0 if no input from user */
28 /* 6) to_win_prob (move sales credits with win prob range ends at) */
29 /* Default value = 100 if no input from user */
30 /* 7) from_close_date (decision date range starts from) */
31 /* Please input the date as the following format */
32 /* e.g.: 01-JAN-1999 */
33 /* Default value = 01-JAN-1900 */
34 /* 8) to_close_date (decision date range ends at) */
35 /* Please input the date as the following format */
36 /* e.g.: 01-JAN-1999 */
37 /* Default value = 01-JAN-4712 */
38 /* 9) Statuses : all leads with these statuses will be moved */
39 /* Please input a list of statuses separated by comma (,) */
40 /* e.g.: won,preliminary */
41 /* Default value = all statuses if no input from user */
42 /********************************************************************************/
43 /*-------------------------------------------------------------------------*
44 | PRIVATE ROUTINE
45 | Pipeline_Movement
46 |
47 | PURPOSE
48 | The main program for pipeline movement.
49 | NOTES
50 |
51 | HISTORY
52 | 02/27/01 ACNG Created
53 *-------------------------------------------------------------------------*/
54 PROCEDURE Pipeline_Movement(
55 ERRBUF OUT NOCOPY VARCHAR2,
56 RETCODE OUT NOCOPY VARCHAR2,
57 p_from_user IN VARCHAR2,
58 p_from_grp IN VARCHAR2,
59 p_to_user IN VARCHAR2,
60 p_to_grp IN VARCHAR2,
61 p_from_win_prob IN NUMBER := NULL,
62 p_to_win_prob IN NUMBER := NULL,
63 p_from_close_date IN DATE := NULL,
64 p_to_close_date IN DATE := NULL,
65 p_status IN VARCHAR2 := NULL )
66 IS
67 /******** DECLARE ALL CURSORS ***************************/
68 /*
69 CURSOR FIND_LEADS(from_win_prob NUMBER,
70 to_win_prob NUMBER,
71 from_close_date DATE,
72 to_close_date DATE,
73 from_sf_id NUMBER,
74 from_sg_id NUMBER,
78 FROM AS_LEADS_ALL LD
75 in_status VARCHAR)
76 IS
77 SELECT LD.LEAD_ID LEAD_ID
79 WHERE LD.DECISION_DATE BETWEEN from_close_date AND to_close_date
80 AND LD.WIN_PROBABILITY BETWEEN from_win_prob AND to_win_prob
81 AND STATUS || '' IN in_status
82 AND EXISTS
83 (SELECT 1
84 FROM AS_ACCESSES_ALL ACC
85 WHERE ACC.LEAD_ID = LD.LEAD_ID
86 AND ACC.SALESFORCE_ID = from_sf_id
87 AND ACC.SALES_GROUP_ID = from_sg_id);
88 */
89 TYPE FIND_LEADS is REF CURSOR;
90 l_credit_type_id NUMBER;
91 status_str VARCHAR2(1000);
92 status_tokenized VARCHAR2(1000);
93 from_person NUMBER;
94 to_person NUMBER;
95 from_res NUMBER;
96 to_res NUMBER;
97 from_group_id NUMBER;
98 to_group_id NUMBER;
99 from_is_group_correct NUMBER;
100 to_is_group_correct NUMBER;
101 is_owner NUMBER;
102 is_in_sales_credits NUMBER;
103 is_in_sales_team NUMBER;
104 from_win_prob NUMBER;
105 to_win_prob NUMBER;
106 from_close_date DATE;
107 to_close_date DATE;
108 curs FIND_LEADS;
109 sqlstr VARCHAR2(2000);
110 L_LEAD_ID NUMBER;
111
112
113 l_salesforceid number;
114 l_salesgroupid number;
115 l_personid number;
116
117
118 CURSOR SALES_CR (l_lead_id number,l_salesforceid number,l_salesgroupid number,l_personid number) IS
119 SELECT LEAD_LINE_ID,
120 CREDIT_TYPE_ID,
121 SUM(CREDIT_AMOUNT) CR_AMT ,
122 SUM(CREDIT_PERCENT) CR_PCT
123 FROM AS_SALES_CREDITS ASSC
124 WHERE ASSC.LEAD_ID = l_lead_id
125 AND ASSC.SALESFORCE_ID = l_salesforceid
126 AND ASSC.SALESGROUP_ID = l_salesgroupid
127 AND ASSC.PERSON_ID = l_personid
128 GROUP BY LEAD_LINE_ID,CREDIT_TYPE_ID;
129
130 BEGIN
131 BEGIN
132 SELECT RES.SOURCE_ID,RES.RESOURCE_ID INTO from_person,from_res FROM JTF_RS_RESOURCE_EXTNS RES, FND_USER USR WHERE RES.SOURCE_ID = USR.EMPLOYEE_ID AND RES.CATEGORY = 'EMPLOYEE' AND USR.USER_NAME = p_from_user;
133 SELECT RES.SOURCE_ID,RES.RESOURCE_ID INTO to_person,to_res FROM JTF_RS_RESOURCE_EXTNS RES, FND_USER USR WHERE RES.SOURCE_ID = USR.EMPLOYEE_ID AND RES.CATEGORY = 'EMPLOYEE' AND USR.USER_NAME = p_to_user;
134 SELECT GROUP_ID INTO from_group_id FROM JTF_RS_GROUPS_B WHERE GROUP_NUMBER = p_from_grp;
135 SELECT GROUP_ID INTO to_group_id FROM JTF_RS_GROUPS_B WHERE GROUP_NUMBER = p_to_grp;
136 SELECT COUNT(1) INTO from_is_group_correct FROM JTF_RS_GROUP_MEMBERS WHERE RESOURCE_ID = from_res AND GROUP_ID = from_group_id AND DELETE_FLAG = 'N';
137 SELECT COUNT(1) INTO to_is_group_correct FROM JTF_RS_GROUP_MEMBERS WHERE RESOURCE_ID = to_res AND GROUP_ID = to_group_id AND DELETE_FLAG = 'N';
138 EXCEPTION
139 WHEN OTHERS THEN
140 ERRBUF := 'Error at Pipeline Movement '||SQLERRM||' - (Error in group info)';
141 END;
142 IF from_is_group_correct = 0 OR to_is_group_correct = 0 THEN
143 BEGIN
144 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: From Salesforce/salesgroup do not match.');
145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
146 END;
147 END IF;
148 IF from_res = to_res AND from_group_id = to_group_id THEN
149 BEGIN
150 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: From Resource And To Resource cannot be identical.');
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
152 END;
153 END IF;
154 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start pipeline movement');
155
156 from_win_prob := NVL(p_from_win_prob,0);
157 to_win_prob := NVL(p_to_win_prob,100);
158 from_close_date := NVL(p_from_close_date,to_date('01/01/1900','DD/MM/YYYY'));
159 to_close_date := NVL(p_to_close_date,to_date('01/01/4712','DD/MM/YYYY'));
160
161 -- ******* GET SALESREP INFO BASED ON INPUT **************************
162 -- ****** MANIPULATE STATUS ******************************************
163 IF NVL(length(p_status),0) = 0 OR p_status IS NULL OR UPPER(TRIM(p_status)) = 'ALL' THEN
164 status_str := '(SELECT DISTINCT UPPER(status_code) FROM as_statuses_b WHERE enabled_flag = ''Y'' AND opp_flag = ''Y'')';
165 ELSE
166 BEGIN
167 /* Status filter earlier code
168 status_str := '''(''';
169 SELECT REPLACE(UPPER(TRIM(p_status)),',',''',''') INTO status_tokenized FROM DUAL;
170 status_str := status_str || status_tokenized || ''')''';
171 status_str := '(''';
172 SELECT REPLACE(UPPER(TRIM(p_status)),',',''',''') INTO status_tokenized FROM DUAL;
173 status_str := status_str || status_tokenized || ''')';
174 --DBMS_OUTPUT.PUT_LINE(status_str);
175 */
176 SELECT '(TRIM(''' || REPLACE(REPLACE(UPPER(TRIM(p_status)),',','''),TRIM('''),' ','') || '''))' INTO status_str FROM DUAL;
177 EXCEPTION
178 WHEN OTHERS THEN
179 ERRBUF := 'Error at Pipeline Movement '||SQLERRM||' - (Error in determining input value of STATUS)';
180 END;
181 END IF;
182 FND_FILE.PUT_LINE(FND_FILE.LOG,'Status : ' || status_str);
183 BEGIN
184 -- ** LOOP THRO ALL LEADS THAT SATISFY INPUT CRITERIA *
185 -- dbms_output.put_line('Status is:'|| status_str);
186 sqlstr := ' SELECT LD.LEAD_ID lead_id ';
187 sqlstr := sqlstr || ' FROM AS_LEADS_ALL LD ';
188 sqlstr := sqlstr || ' WHERE LD.DECISION_DATE BETWEEN :from_close_date AND :to_close_date ';
189 sqlstr := sqlstr || ' AND LD.WIN_PROBABILITY BETWEEN :from_win_prob AND :to_win_prob ';
190 sqlstr := sqlstr || ' AND UPPER(STATUS) IN ';
191 sqlstr := sqlstr || status_str ;
192 sqlstr := sqlstr || ' AND EXISTS ';
193 sqlstr := sqlstr || ' (SELECT 1';
194 sqlstr := sqlstr || ' FROM AS_ACCESSES_ALL ACC ';
195 sqlstr := sqlstr || ' WHERE ACC.LEAD_ID = LD.LEAD_ID ';
196 sqlstr := sqlstr || ' AND ACC.SALESFORCE_ID = :from_sf_id ';
197 sqlstr := sqlstr || ' AND ACC.SALES_GROUP_ID = :from_sg_id) ';
198 OPEN curs for sqlstr using from_close_date ,to_close_date ,from_win_prob ,to_win_prob ,from_res ,from_group_id ;
202 --* UPDATE SALES CREDITS **
199 LOOP
200 FETCH curs INTO L_LEAD_ID ;
201 EXIT WHEN curs%NOTFOUND ;
203 --** CASE 1 - SR2 already exists in as_sales_credits **
204 --** add up the salescredits for SR1 to SR2, update SR2's salescredits and delete the salescredits for SR1 **
205 --dbms_output.put_line('CASE 1 - SR2 already exists in as_sales_credits');
206
207 -- find r2 sales credit exist
208 --dbms_output.put_line(to_char(l_lead_id) || '-' || to_char(from_res) || '-' || to_char(from_group_id)|| '-' || to_char(from_person));
209
210 l_salesforceid := from_res;
211 l_salesgroupid := from_group_id;
212 l_personid := from_person;
213
214 FOR SC_REC IN SALES_CR(L_LEAD_ID,l_salesforceid,l_salesgroupid,l_personid) LOOP
215
216 --dbms_output.put_line('Lead id is:'|| L_LEAD_ID);
217
218 --dbms_output.put_line(to_char(sc_rec.credit_type_id) || '-' || to_char(sc_rec.cr_amt) || '-' || to_char(sc_rec.cr_pct));
219
220 SELECT COUNT(*) INTO is_in_sales_credits
221 FROM AS_SALES_CREDITS SC
222 WHERE SC.LEAD_ID = L_LEAD_ID
223 AND SC.LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
224 AND SC.SALESFORCE_ID = to_res
225 AND SC.SALESGROUP_ID = to_group_id
226 AND SC.CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID;
227 IF is_in_sales_credits > 0 THEN
228 BEGIN
229 -- update salescredits for SR2
230 UPDATE AS_SALES_CREDITS ASSC
231 SET object_version_number = nvl(object_version_number,0) + 1, ASSC.CREDIT_AMOUNT = ASSC.CREDIT_AMOUNT + SC_REC.CR_AMT,
232 ASSC.CREDIT_PERCENT = ASSC.CREDIT_PERCENT + SC_REC.CR_PCT
233 WHERE ASSC.LEAD_ID = L_LEAD_ID
234 AND ASSC.LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
235 AND ASSC.SALESFORCE_ID = to_res
236 AND ASSC.SALESGROUP_ID = to_group_id
237 AND ASSC.PERSON_ID = to_person
238 AND ASSC.CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID
239 AND ROWID = (SELECT MIN(ROWID)
240 FROM AS_SALES_CREDITS z
241 WHERE z.LEAD_ID = L_LEAD_ID
242 AND z.LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
243 AND z.SALESFORCE_ID = ASSC.SALESFORCE_ID
244 AND z.SALESGROUP_ID = ASSC.SALESGROUP_ID
245 AND z.PERSON_ID = ASSC.PERSON_ID
246 AND z.CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID);
247 END;
248 ELSE
249 BEGIN
250 --** CASE 2 - SR2 does not exist in as_sales_credits **
251 UPDATE AS_SALES_CREDITS
252 SET object_version_number = nvl(object_version_number,0) + 1, SALESFORCE_ID = to_res,
253 PERSON_ID = to_person,
254 SALESGROUP_ID = to_group_id
255 WHERE LEAD_ID = L_LEAD_ID
256 AND LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
257 AND SALESFORCE_ID = from_res
258 AND PERSON_ID = from_person
259 AND SALESGROUP_ID = from_group_id
260 AND CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID;
261 END;
262 END IF;
263 --dbms_output.put_line('After updating sales credit:'|| SQL%ROWCOUNT);
264 -- delete salescredits for SR1
265 DELETE FROM AS_SALES_CREDITS
266 WHERE LEAD_ID = L_LEAD_ID
267 AND LEAD_LINE_ID = SC_REC.LEAD_LINE_ID
268 AND SALESFORCE_ID = from_res
269 AND SALESGROUP_ID = from_group_id
270 AND CREDIT_TYPE_ID = SC_REC.CREDIT_TYPE_ID;
271 --dbms_output.put_line('After deleteing sales credit:'|| SQL%ROWCOUNT);
272 END LOOP;
273 --* UPDATE LEADS *
277 IF is_owner = 1 THEN
274 SELECT COUNT(*) INTO is_owner FROM AS_LEADS_ALL L WHERE L.LEAD_ID = L_LEAD_ID AND L.OWNER_SALESFORCE_ID = from_res AND L.OWNER_SALES_GROUP_ID = from_group_id;
275 SELECT COUNT(*) INTO is_in_sales_team FROM AS_ACCESSES_ALL ACC WHERE ACC.LEAD_ID = L_LEAD_ID AND ACC.SALESFORCE_ID = to_res AND ACC.SALES_GROUP_ID = to_group_id;
276 --* CASE 1 - IF SR1 was owner *
278 BEGIN
279 UPDATE AS_LEADS_ALL L
280 SET object_version_number = nvl(object_version_number,0) + 1, L.OWNER_SALESFORCE_ID = to_res,
281 L.OWNER_SALES_GROUP_ID = to_group_id
282 WHERE L.LEAD_ID = L_LEAD_ID
283 AND L.OWNER_SALESFORCE_ID = from_res
284 AND L.OWNER_SALES_GROUP_ID = from_group_id;
285 --* UPDATE SALES TEAM *
286 --* CASE 1 - SR1/GRP1 moved to SR2/GRP2 *
287 If is_in_sales_team = 1 THEN --* CASE 1A - SR2/GRP2 is already in SALES TEAM *
288 BEGIN
289 -- delete SR1/GR1 from SALES TEAM
290 DELETE FROM AS_ACCESSES_ALL ACC
291 WHERE ACC.LEAD_ID = L_LEAD_ID
292 AND ACC.SALESFORCE_ID = from_res
293 AND ACC.SALES_GROUP_ID = from_group_id
297 SET object_version_number = nvl(object_version_number,0) + 1, OWNER_FLAG = 'Y',
294 AND ACC.PERSON_ID = from_person ;
295 -- IF SR1 was the owner then give full access and ownership TO SR2
296 UPDATE AS_ACCESSES_ALL ACC
298 TEAM_LEADER_FLAG = 'Y'
299 WHERE ACC.LEAD_ID = L_LEAD_ID
300 AND ACC.SALESFORCE_ID = to_res
301 AND ACC.SALES_GROUP_ID = to_group_id
302 AND ACC.PERSON_ID = to_person ;
303 END;
304 ELSE --* CASE 1B - SR2/GRP2 is NOT in SALES TEAM *
305 BEGIN
306 UPDATE AS_ACCESSES_ALL ACC
307 SET object_version_number = nvl(object_version_number,0) + 1, SALESFORCE_ID = to_res,
308 SALES_GROUP_ID = to_group_id,
309 PERSON_ID = to_person,
310 OWNER_FLAG = 'Y',
311 TEAM_LEADER_FLAG = 'Y'
312 WHERE ACC.LEAD_ID = L_LEAD_ID
313 AND ACC.SALESFORCE_ID = from_res
314 AND ACC.SALES_GROUP_ID = from_group_id
315 AND ACC.PERSON_ID = from_person ;
316 END;
317 END IF;
318 END;
319 ELSE --- SR1 is NOT the owner
320 BEGIN
321 --- We will NTO be updating AS_LEADS_ALL
322 --* UPDATE SALES TEAM *
323 --* CASE 1 - SR1/GRP1 moved to SR2/GRP2 *
324 If is_in_sales_team = 1 THEN --* CASE 1A - SR2/GRP2 is already in SALES TEAM *
325 BEGIN
326 -- delete SR1/GR1 from SALES TEAM
327 DELETE FROM AS_ACCESSES_ALL ACC
328 WHERE ACC.LEAD_ID = L_LEAD_ID
329 AND ACC.SALESFORCE_ID = from_res
330 AND ACC.SALES_GROUP_ID = from_group_id
331 AND ACC.PERSON_ID = from_person ;
332 -- IF SR1 was the owner then give full access and ownership TO SR2
333 -- No need to do this here since SR1 was not the owner.
334 END;
335 ELSE --** CASE 1B - SR2/GRP2 is NOT in SALES TEAM *
336 BEGIN
337 UPDATE AS_ACCESSES_ALL ACC
338 SET object_version_number = nvl(object_version_number,0) + 1, SALESFORCE_ID = to_res,
339 SALES_GROUP_ID = to_group_id,
340 PERSON_ID = to_person
341 WHERE ACC.LEAD_ID = L_LEAD_ID
342 AND ACC.SALESFORCE_ID = from_res
343 AND ACC.SALES_GROUP_ID = from_group_id
344 AND ACC.PERSON_ID = from_person ;
345 END;
346 END IF;
347 END;
348 END IF;
349 END LOOP; -- End Loop for Leads
350 END;
351 FND_FILE.PUT_LINE(FND_FILE.LOG,'Finish pipeline movement');
352 --dbms_output.put_line('Finish pipeline movement');
353 COMMIT;
354 EXCEPTION
355 WHEN OTHERS THEN
356 ERRBUF := 'Error at Pipeline Movement'||SQLERRM;
357 --dbms_output.put_line('Error at Pipeline Movement'||SQLERRM);
358 END PIPELINE_MOVEMENT;
359 /*-------------------------------------------------------------------------*
360 | PRIVATE ROUTINE
361 | Pipeline_Movement
362 |
363 | PURPOSE
364 | The main program for pipeline movement. (sales credits only)
365 | NOTES
366 |
367 | HISTORY
368 | 02/27/01 ACNG Created
369 *-------------------------------------------------------------------------*/
370 PROCEDURE Pipeline_SC_Movement(
371 ERRBUF OUT NOCOPY VARCHAR2,
372 RETCODE OUT NOCOPY VARCHAR2,
373 p_from_user IN VARCHAR2,
374 p_from_grp IN VARCHAR2,
375 p_to_user IN VARCHAR2,
376 p_to_grp IN VARCHAR2,
377 p_from_win_prob IN NUMBER := NULL,
378 p_to_win_prob IN NUMBER := NULL,
379 p_from_close_date IN DATE := NULL,
380 p_to_close_date IN DATE := NULL,
381 p_status IN VARCHAR2 := NULL )
382 IS
383 from_sf_id NUMBER;
384 from_sg_id NUMBER;
385 from_person NUMBER;
386 to_sf_id NUMBER;
387 to_sg_id NUMBER;
388 to_person NUMBER;
389 from_win_prob NUMBER;
390 to_win_prob NUMBER;
391 from_close_date DATE;
392 to_close_date DATE;
393 group_ok NUMBER := 0;
394 error_flag VARCHAR2(1) := 'N';
395 cursor get_id(x_user VARCHAR2) is
396 select res.source_id, res.resource_id
397 from JTF_RS_RESOURCE_EXTNS res, FND_USER usr
398 where res.source_id = usr.employee_id
399 and res.category = 'EMPLOYEE'
400 and usr.user_name = x_user;
401 cursor get_sg_id(x_sg_num VARCHAR2) is
402 select group_id
403 from JTF_RS_GROUPS_B
404 where group_number = x_sg_num;
405 cursor check_grp_id(x_sf_id NUMBER, x_grp_id NUMBER) is
406 select count(1)
407 from JTF_RS_GROUP_MEMBERS
408 where resource_id = x_sf_id
409 and group_id = x_grp_id;
410 TYPE status_rec IS RECORD
411 ( status VARCHAR2(30) );
412 TYPE status_tbl IS TABLE OF status_rec INDEX BY BINARY_INTEGER;
413 allstatus VARCHAR2(1000);
414 st_len NUMBER;
415 st_count NUMBER := 1;
416 ld_status status_tbl;
417 i_count NUMBER := 1;
418 BEGIN
419 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start pipeline movement');
420 -- get salesforce_id from user_name --
421 open get_id(p_from_user);
422 fetch get_id into from_person, from_sf_id;
423 close get_id;
424 -- get salesforce_id from user_name --
425 open get_id(p_to_user);
426 fetch get_id into to_person, to_sf_id;
427 close get_id;
428 -- get sales_group_id from group_name --
429 open get_sg_id(p_from_grp);
430 fetch get_sg_id into from_sg_id;
434 fetch get_sg_id into to_sg_id;
431 close get_sg_id;
432 -- get sales_group_id from group_name --
433 open get_sg_id(p_to_grp);
435 close get_sg_id;
436 open check_grp_id(from_sf_id, from_sg_id);
437 fetch check_grp_id into group_ok;
438 close check_grp_id;
439 if(group_ok = 0) then
440 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: From Salesforce/salesgroup do not match.');
441 error_flag := 'Y';
442 end if;
443 group_ok := 0;
444 open check_grp_id(to_sf_id, to_sg_id);
445 fetch check_grp_id into group_ok;
446 close check_grp_id;
447 if(group_ok = 0) then
448 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: To Salesforce/salesgroup do not match.');
449 error_flag := 'Y';
450 end if;
451 group_ok := 0;
452 from_win_prob := nvl(p_from_win_prob,0);
453 to_win_prob := nvl(p_to_win_prob,100);
454 from_close_date := nvl(p_from_close_date,to_date('01/01/1900','DD/MM/YYYY'));
455 to_close_date := nvl(p_to_close_date,to_date('01/01/4712','DD/MM/YYYY'));
456 /*
457 dbms_output.put_line('User : '||p_from_user);
458 dbms_output.put_line('Person : '||from_person);
459 dbms_output.put_line('Id : '||from_sf_id);
460 dbms_output.put_line('SG : '||from_sg_id);
461 dbms_output.put_line('User : '||p_to_user);
462 dbms_output.put_line('Person : '||to_person);
463 dbms_output.put_line('Id : '||to_sf_id);
464 dbms_output.put_line('SG : '||to_sg_id);
465 dbms_output.put_line('Win Prob : '||from_win_prob);
466 dbms_output.put_line('Win Prob : '||to_win_prob);
467 dbms_output.put_line('Close Dt : '||to_char(from_close_date,'DD/MM/YYYY'));
468 dbms_output.put_line('Close Dt : '||to_char(to_close_date,'DD/MM/YYYY'));
469 */
470 -- If user put 'ALL' for the first status, then --
471 -- no where filter condition for status --
472 allstatus := upper(ltrim(rtrim(p_status)));
473 st_len := nvl(length(allstatus),0);
474 -- nothing type in for status, default to 'ALL'
475 if(st_len = 0) then
476 ld_status(st_count).status := 'ALL';
477 FND_FILE.PUT_LINE(FND_FILE.LOG,'Status : ALL');
478 else
479 -- specify only one status, maybe 'ALL', maybe others
480 if(instr(allstatus,',') = 0) then
481 ld_status(st_count).status := allstatus;
482 FND_FILE.PUT_LINE(FND_FILE.LOG,'Status '||st_count||' : '||ld_status(st_count).status);
483 -- more than 1 status specified
484 else
485 ld_status(st_count).status := ltrim(rtrim(substr(allstatus,1,instr(allstatus,',')-1)));
486 FND_FILE.PUT_LINE(FND_FILE.LOG,'Status '||st_count||' : '||ld_status(st_count).status);
487 --dbms_output.put_line('Status : '||ld_status(st_count).status);
488 allstatus := ltrim(rtrim(substr(allstatus,instr(allstatus,',')+1,st_len)));
489 st_count := st_count + 1;
490 while (instr(allstatus,',') <> 0) loop
491 ld_status(st_count).status := ltrim(rtrim(substr(allstatus,1,instr(allstatus,',')-1)));
492 FND_FILE.PUT_LINE(FND_FILE.LOG,'Status '||st_count||' : '||ld_status(st_count).status);
493 --dbms_output.put_line('Status '||st_count||' : '||ld_status(st_count).status);
494 allstatus := ltrim(rtrim(substr(allstatus,instr(allstatus,',')+1,st_len)));
495 st_count := st_count + 1;
496 end loop;
497 ld_status(st_count).status := ltrim(rtrim(allstatus));
498 FND_FILE.PUT_LINE(FND_FILE.LOG,'Status '||st_count||' : '||ld_status(st_count).status);
499 --dbms_output.put_line('last Status : '||ld_status(st_count).status);
500 --dbms_output.put_line('Total status : '||st_count);
501 end if;
502 end if;
503 --DBMS_OUTPUT.PUT_LINE('I AM OUTSIDE');
504 IF(error_flag <> 'Y') THEN
505 IF(rtrim(ltrim(ld_status(1).status)) = 'ALL') THEN
506 FND_FILE.PUT_LINE(FND_FILE.LOG,'Win Prob : '||from_win_prob||'-'||to_win_prob);
507 FND_FILE.PUT_LINE(FND_FILE.LOG,'Close Date : '||from_close_date||'-'||to_close_date);
508 FND_FILE.PUT_LINE(FND_FILE.LOG,'From SF/SG : '||from_sf_id||'-'||from_sg_id);
509 FND_FILE.PUT_LINE(FND_FILE.LOG,'To SF/SG : '||to_sf_id||'-'||to_sg_id);
510 update AS_SALES_CREDITS sc
511 set object_version_number = nvl(object_version_number,0) + 1, salesforce_id = to_sf_id,
512 person_id = to_person,
513 salesgroup_id = to_sg_id
514 where exists
515 ( select ld.lead_id
516 from AS_LEADS_ALL ld
517 where ld.win_probability between from_win_prob and to_win_prob
518 and ld.decision_date between from_close_date and to_close_date
519 and ld.lead_id = sc.lead_id )
520 and sc.salesforce_id = from_sf_id
521 and sc.salesgroup_id = from_sg_id
522 and sc.person_id = from_person;
523 ELSE
524 FND_FILE.PUT_LINE(FND_FILE.LOG,'Win Prob : '||from_win_prob||'-'||to_win_prob);
525 FND_FILE.PUT_LINE(FND_FILE.LOG,'Close Date : '||from_close_date||'-'||to_close_date);
526 FND_FILE.PUT_LINE(FND_FILE.LOG,'From SF/SG : '||from_sf_id||'-'||from_sg_id);
527 FND_FILE.PUT_LINE(FND_FILE.LOG,'To SF/SG : '||to_sf_id||'-'||to_sg_id);
528 while (i_count <= st_count) loop
529 update AS_SALES_CREDITS sc
530 set object_version_number = nvl(object_version_number,0) + 1, salesforce_id = to_sf_id,
531 person_id = to_person,
532 salesgroup_id = to_sg_id
533 where exists
534 ( select ld.lead_id
535 from AS_LEADS_ALL ld
536 where ld.win_probability between from_win_prob and to_win_prob
537 and ld.decision_date between from_close_date and to_close_date
538 and ld.status = ld_status(i_count).status
539 and ld.lead_id = sc.lead_id )
540 and sc.salesforce_id = from_sf_id
541 and sc.salesgroup_id = from_sg_id
542 and sc.person_id = from_person;
543 i_count := i_count + 1;
547 FND_FILE.PUT_LINE(FND_FILE.LOG,'Finish pipeline movement');
544 end loop;
545 END IF;
546 END IF;
548 EXCEPTION
549 WHEN others THEN
550 ERRBUF := 'Error at Pipeline Movement'||SQLERRM;
551 END Pipeline_SC_Movement;
552 END AS_PIPELINE_MOVE_PUB;
[Home] [Help]