[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_REQUEST_PVT
Source
1 PACKAGE BODY PA_PROJECT_REQUEST_PVT as
2 /* $Header: PAYRPVTB.pls 120.8.12010000.3 2010/05/06 06:27:12 kkorrapo ship $ */
3
4 -- This procedure will validate the status of project request for project creation.
5 -- Users are not allowed to create a project from a project request having system
6 -- Status of 'PROJ_REQ_CLOSED' OR 'PROJ_REQ_CANCELED'.
7 --
8 -- Input parameters
9 -- Parameters Type
10 -- p_request_sys_status pa_project_statuses.project_system_status_code%TYPE
11 --
12 G_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
13 PROCEDURE debug(p_msg IN VARCHAR2) IS
14 l_debug_mode varchar2(1); -- Added for Bug 4469333
15 BEGIN
16 l_debug_mode := PA_PROJECT_REQUEST_PVT.G_DEBUG_MODE ; -- Added for Bug 4469333
17
18 IF l_debug_mode = 'Y' THEN -- IF Clause Included for Bug 4469333
19
20 --dbms_output.put_line('pa_project_request_pvt'|| ' : ' || p_msg);
21 PA_DEBUG.WRITE(
22 x_module => 'pa.plsql.pa_project_request_pvt',
23 x_msg => p_msg,
24 x_log_level => 6);
25 pa_debug.write_file('LOG', p_msg);
26 -- Added the following line in order to show log messages in Concurrent Program
27 FND_FILE.PUT_LINE(FND_FILE.LOG,p_msg);
28
29 END IF ; -- End of IF Clause started for Bug 4469333
30 END debug;
31
32
33 PROCEDURE create_project_validation
34 (p_request_sys_status IN pa_project_statuses.project_system_status_code%TYPE,
35 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
36 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
37 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
38 IS
39 create_proj_not_allowed EXCEPTION;
40 l_msg_index_out NUMBER;
41 -- added for Bug Fix: 4537865
42 l_new_msg_data VARCHAR2(2000);
43 -- added for Bug Fix: 4537865
44
45 BEGIN
46 x_return_status := FND_API.G_RET_STS_SUCCESS;
47
48 -- Check if the user is allowed to create project.
49 -- For any project request with a system status code of
50 -- 'PROJ_REQ_CANCELED' or 'PROJ_REQ_CLOSED',
51 -- user is not allowed to create project from it.
52
53 IF p_request_sys_status = 'PROJ_REQ_CANCELED' OR
54 p_request_sys_status = 'PROJ_REQ_CLOSED' THEN
55
56 RAISE create_proj_not_allowed;
57 END IF;
58
59 EXCEPTION
60 WHEN create_proj_not_allowed THEN
61 PA_UTILS.add_message(p_app_short_name => 'PA',
62 p_msg_name => 'PA_CANNOT_CREATE_PROJ');
63 x_return_status := FND_API.G_RET_STS_ERROR;
64 x_msg_count := FND_MSG_PUB.Count_Msg;
65 x_msg_data := 'PA_CANNOT_CREATE_PROJ';
66
67 IF x_msg_count = 1 THEN
68 pa_interface_utils_pub.get_messages
69 (p_encoded => FND_API.G_TRUE,
70 p_msg_index => 1,
71 p_msg_count => x_msg_count,
72 p_msg_data => x_msg_data,
73 --p_data => x_msg_data, * Commented for Bug: 4537865
74 p_data => l_new_msg_data, -- added for Bug Fix: 4537865
75 p_msg_index_out => l_msg_index_out );
76 -- added for Bug Fix: 4537865
77 x_msg_data := l_new_msg_data;
78 -- added for Bug Fix: 4537865
79 END IF;
80
81 WHEN OTHERS THEN
82 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
83 x_msg_count := FND_MSG_PUB.Count_Msg;
84 x_msg_data := substr(SQLERRM,1,240);
85 FND_MSG_PUB.add_exc_msg
86 ( p_pkg_name => 'PA_PROJECT_REQUEST_PVT',
87 p_procedure_name => 'create_project_validation');
88
89 IF x_msg_count = 1 THEN
90 pa_interface_utils_pub.get_messages
91 (p_encoded => FND_API.G_TRUE,
92 p_msg_index => 1,
93 p_msg_count => x_msg_count,
94 p_msg_data => x_msg_data,
95 --p_data => x_msg_data, * Commented for Bug: 4537865
96 p_data => l_new_msg_data, -- added for Bug Fix: 4537865
97 p_msg_index_out => l_msg_index_out );
98 -- added for Bug Fix: 4537865
99 x_msg_data := l_new_msg_data;
100 -- added for Bug Fix: 4537865
101 END IF;
102
103 RAISE; -- This is optional depending on the needs
104
105 END create_project_validation;
106
107
108 --
109 -- Procedure : get_object_info
110 -- Purpose : Get all the attributes of an object.
111 --
112 --
113 PROCEDURE get_object_info
114 ( p_object_type IN VARCHAR2 ,
115 p_object_id1 IN VARCHAR2 ,
116 p_object_id2 IN VARCHAR2 ,
117 p_object_id3 IN VARCHAR2 ,
118 p_object_id4 IN VARCHAR2 ,
119 p_object_id5 IN VARCHAR2 ,
120 x_object_name OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
121 x_object_number OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
122 x_object_type_name OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
123 x_object_subtype OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
124 x_status_name OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
125 x_description OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
126 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
127 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
128 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
129
130 IS
131
132 CURSOR lup IS
133 SELECT lookup_code, meaning
134 FROM pa_lookups
135 WHERE lookup_type = 'PROJ_REQ_OBJECT_TYPE'
136 AND lookup_code = p_object_type;
137
138 v_lup lup%ROWTYPE;
139
140 -- 2564086: Modified to select from pa_projects_all.
141 CURSOR prj IS
142 SELECT p.name, p.segment1, p.project_type, s.project_status_name, p.description
143 FROM pa_projects_all p, pa_project_statuses s
144 WHERE p.project_status_code = s.project_status_code
145 AND p.project_id = p_object_id1;
146
147 v_prj prj%ROWTYPE;
148
149 CURSOR prq IS
150 SELECT r.request_name, r.request_number, lup.meaning, s.project_status_name, r.description
151 FROM pa_project_requests r, pa_project_statuses s, pa_lookups lup
152 WHERE r.status_code = s.project_status_code
153 AND r.request_id = p_object_id1
154 AND r.request_type = lup.lookup_code
155 AND lup.lookup_type = 'PROJECT_REQUEST_TYPE';
156
157 v_prq prq%ROWTYPE;
158
159 CURSOR asl IS
160 SELECT a.lead_number, s.meaning, a.description
161 FROM as_leads a, as_statuses_tl s
162 WHERE a.status = s.status_code
163 AND a.lead_id = p_object_id1
164 AND s.LANGUAGE = userenv('LANG'); -- added for Bug 4099490
165
166 v_asl asl%ROWTYPE;
167
168 BEGIN
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170
171 x_object_name := NULL;
172 x_object_number := NULL;
173 x_object_type_name := NULL;
174 x_object_subtype := NULL;
175 x_status_name := NULL;
176 x_description := NULL;
177
178 OPEN lup;
179 FETCH lup INTO v_lup;
180 x_object_type_name := v_lup.meaning;
181 debug('x_object_type_name = '|| x_object_type_name);
182 CLOSE lup;
183
184 IF p_object_type = 'PA_PROJECTS' THEN
185 OPEN prj;
186 FETCH prj INTO v_prj;
187 IF prj%NOTFOUND THEN
188 RETURN;
189 ELSE
190 x_object_name := v_prj.name;
191 debug('x_object_name = '||x_object_name);
192 x_object_number := v_prj.segment1;
193 x_object_subtype := v_prj.project_type;
194 x_status_name := v_prj.project_status_name;
195 x_description := v_prj.description;
196 END IF;
197
198 ELSIF p_object_type = 'PA_PROJECT_REQUESTS' THEN
199 OPEN prq;
200 FETCH prq INTO v_prq;
201 IF prq%NOTFOUND THEN
202 RETURN;
203 ELSE
204 x_object_name := v_prq.request_name;
205 debug('x_object_name = '||x_object_name);
206 x_object_number := v_prq.request_number;
207 x_object_subtype := v_prq.meaning;
208 x_status_name := v_prq.project_status_name;
209 x_description := v_prq.description;
210 END IF;
211
212 ELSIF p_object_type = 'AS_LEADS' THEN
213 OPEN asl;
214 FETCH asl INTO v_asl;
215 IF asl%NOTFOUND THEN
216 RETURN;
217 ELSE
218 -- bug 6416428 - skkoppul : changed SUBSTR to SUBSTRB
219 x_object_name := SUBSTRB(v_asl.description, 1, 80);
220 debug('x_object_name = '||x_object_name);
221 x_object_number := v_asl.lead_number;
222 x_status_name := v_asl.meaning;
223 x_description := v_asl.description;
224 END IF;
225
226 END IF;
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
231 x_msg_count := 1;
232 x_msg_data := SQLERRM;
233 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_REQUEST_PVT',
234 p_procedure_name => 'get_object_info');
235 raise;
236
237 END get_object_info;
238
239
240 --
241 -- Procedure : populate_associations_temp
242 -- Purpose : Insert data into PA_ASSOCIATIONS_TEMP that is used to
243 -- display
244 -- the associations on the Relationships page.
245 --
246 --
247 PROCEDURE populate_associations_temp
248 ( p_object_type_from IN VARCHAR2,
249 p_object_id_from1 IN VARCHAR2,
250 p_object_id_from2 IN VARCHAR2,
251 p_object_id_from3 IN VARCHAR2,
252 p_object_id_from4 IN VARCHAR2,
253 p_object_id_from5 IN VARCHAR2,
254 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
255 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
256 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
257
258 IS
259 CURSOR c1 IS
260 SELECT object_type_to, object_id_to1, object_id_to2, object_id_to3, object_id_to4, object_id_to5
261 FROM pa_object_relationships
262 WHERE relationship_type = 'A'
263 AND relationship_subtype = 'PROJECT_REQUEST'
264 START WITH (object_type_from = p_object_type_from
265 AND object_id_from1 = p_object_id_from1)
266 CONNECT BY (PRIOR object_id_to1 = object_id_from1
267 AND PRIOR object_type_to = object_type_from
268 AND PRIOR object_id_from1 <> object_id_to1);
269
270 l_object_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
271 l_object_id1_tbl PA_PLSQL_DATATYPES.Char240TabTyp;
272 l_object_id2_tbl PA_PLSQL_DATATYPES.Char240TabTyp;
273 l_object_id3_tbl PA_PLSQL_DATATYPES.Char240TabTyp;
274 l_object_id4_tbl PA_PLSQL_DATATYPES.Char240TabTyp;
275 l_object_id5_tbl PA_PLSQL_DATATYPES.Char240TabTyp;
276 l_object_name_tbl PA_PLSQL_DATATYPES.Char80TabTyp;
277 l_object_number_tbl PA_PLSQL_DATATYPES.Char80TabTyp;
278 l_object_type_name_tbl PA_PLSQL_DATATYPES.Char80TabTyp;
279 l_object_subtype_tbl PA_PLSQL_DATATYPES.Char80TabTyp;
280 l_status_name_tbl PA_PLSQL_DATATYPES.Char80TabTyp;
281 l_description_tbl PA_PLSQL_DATATYPES.Char250TabTyp;
282
283 j NUMBER := 1;
284
285 BEGIN
286 debug('Entering populate_associations_temp');
287 x_return_status := FND_API.G_RET_STS_SUCCESS;
288
289 FOR v_c1 IN c1 LOOP
290 debug('Entering c1 loop');
291 debug('j = '||j);
292 l_object_type_tbl(j) := v_c1.object_type_to;
293 l_object_id1_tbl(j) := v_c1.object_id_to1;
294 l_object_id2_tbl(j) := v_c1.object_id_to2;
295 l_object_id3_tbl(j) := v_c1.object_id_to3;
296 l_object_id4_tbl(j) := v_c1.object_id_to4;
297 l_object_id5_tbl(j) := v_c1.object_id_to5;
298 debug('object_id1 = ' || v_c1.object_id_to1);
299
300 debug('Before calling get_object_info');
301 get_object_info(p_object_type => v_c1.object_type_to,
302 p_object_id1 => v_c1.object_id_to1,
303 p_object_id2 => v_c1.object_id_to2,
304 p_object_id3 => v_c1.object_id_to3,
305 p_object_id4 => v_c1.object_id_to4,
306 p_object_id5 => v_c1.object_id_to5,
307 x_object_name => l_object_name_tbl(j),
308 x_object_number => l_object_number_tbl(j),
309 x_object_type_name => l_object_type_name_tbl(j),
310 x_object_subtype => l_object_subtype_tbl(j),
311 x_status_name => l_status_name_tbl(j),
312 x_description => l_description_tbl(j),
313 x_return_status => x_return_status,
314 x_msg_count => x_msg_count,
315 x_msg_data => x_msg_data);
316
317 debug('After calling get_object_info');
318 j := j +1;
319
320 END LOOP;
321
322 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_object_type_tbl.COUNT > 0) THEN
323 PA_PROJ_REQ_ASSOCIATIONS_PKG.insert_rows(p_object_type_tbl => l_object_type_tbl,
324 p_object_id1_tbl => l_object_id1_tbl,
325 p_object_id2_tbl => l_object_id2_tbl,
326 p_object_id3_tbl => l_object_id3_tbl,
327 p_object_id4_tbl => l_object_id4_tbl,
328 p_object_id5_tbl => l_object_id5_tbl,
329 p_object_name_tbl => l_object_name_tbl,
330 p_object_number_tbl => l_object_number_tbl,
331 p_object_type_name_tbl => l_object_type_name_tbl,
332 p_object_subtype_tbl => l_object_subtype_tbl,
333 p_status_name_tbl => l_status_name_tbl,
334 p_description_tbl => l_description_tbl,
335 x_return_status => x_return_status,
336 x_msg_count => x_msg_count,
337 x_msg_data => x_msg_data);
338 END IF;
339
340 debug('Leaving populate_associations_temp');
341 EXCEPTION
342 WHEN OTHERS THEN
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344 x_msg_count := 1;
345 x_msg_data := SQLERRM;
346 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_REQUEST_PVT',
347 p_procedure_name => 'populate_associations_temp');
348 raise;
349
350 END populate_associations_temp;
351
352
353 PROCEDURE close_project_request
354 (p_request_id IN pa_project_requests.request_id%TYPE,
355 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
356 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
357 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
358 IS
359 close_req_not_allowed EXCEPTION;
360 l_msg_index_out NUMBER;
361 l_sys_status_code VARCHAR2(30);
362 -- added for Bug Fix: 4537865
363 l_new_msg_data VARCHAR2(2000);
364 -- added for Bug Fix: 4537865
365
366 cursor cur_status is
367 select sts.project_system_status_code
368 from pa_project_statuses sts, pa_project_requests req
369 where req.request_id = p_request_id
370 and sts.project_status_code = req.status_code;
371 BEGIN
372
373
374 -- Initialize the return status to success
375 x_return_status := FND_API.G_RET_STS_SUCCESS;
376
377 --Log Message
378 debug('Beginning of close_project_request');
379
380 -- Check if the user is allowed to close the project request.
381 -- For any project request with a status of 'CANCELED' or 'CLOSED',
382 -- user is not allowed to close it.
383
384 OPEN Cur_Status;
385 FETCH Cur_Status INTO l_sys_status_code;
386 CLOSE Cur_Status;
387
388 IF l_sys_status_code = 'PROJ_REQ_CANCELED' OR
389 l_sys_status_code = 'PROJ_REQ_CLOSED' THEN
390
391 RAISE close_req_not_allowed;
392 END IF;
393
394 --Log Message
395 debug('Calling PKG update_row');
396
397 -- Call the table handler
398
399 PA_PROJECT_REQUEST_PKG.update_row
400 ( p_request_id =>p_request_id,
401 p_request_status_code =>'122',
402 p_closed_date =>sysdate,
403 x_return_status =>x_return_status,
404 x_msg_count =>x_msg_count,
405 x_msg_data =>x_msg_data );
406
407
408 EXCEPTION
409 WHEN close_req_not_allowed THEN
410 PA_UTILS.add_message(p_app_short_name => 'PA',
411 p_msg_name => 'PA_CANNOT_CLOSE_REQ');
412 x_return_status := FND_API.G_RET_STS_ERROR;
413 x_msg_count := FND_MSG_PUB.Count_Msg;
414 x_msg_data := 'PA_CANNOT_CLOSE_REQ';
415
416 IF x_msg_count = 1 THEN
417 pa_interface_utils_pub.get_messages
418 (p_encoded => FND_API.G_TRUE,
419 p_msg_index => 1,
420 p_msg_count => x_msg_count,
421 p_msg_data => x_msg_data,
422 --p_data => x_msg_data, * Commented for Bug: 4537865
423 p_data => l_new_msg_data, -- added for Bug Fix: 4537865
424 p_msg_index_out => l_msg_index_out );
425 -- added for Bug Fix: 4537865
426 x_msg_data := l_new_msg_data;
427 -- added for Bug Fix: 4537865
428 END IF;
429
430
431 WHEN OTHERS THEN
432
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
434 x_msg_count := FND_MSG_PUB.Count_Msg;
435 x_msg_data := substr(SQLERRM,1,240);
436
437 -- Set the excetption Message and the stack
438 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_REQUEST_PVT.close_project_request'
439 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
440 IF x_msg_count = 1 THEN
441 pa_interface_utils_pub.get_messages
442 (p_encoded => FND_API.G_TRUE,
443 p_msg_index => 1,
444 p_msg_count => x_msg_count,
445 p_msg_data => x_msg_data,
446 --p_data => x_msg_data, * Commented for Bug: 4537865
447 p_data => l_new_msg_data, -- added for Bug Fix: 4537865
448 p_msg_index_out => l_msg_index_out );
449 -- added for Bug Fix: 4537865
450 x_msg_data := l_new_msg_data;
451 -- added for Bug Fix: 4537865
452 END IF;
453
454 RAISE; -- This is optional depending on the needs
455
456
457 END close_project_request;
458
459
460
461 --Procedure: get_quick_entry_defaults
462 --Purpose: Defaults the quick entry, when create a project from a selected request.
463 --Note: In parameter template_id is not used currently
464
465 PROCEDURE get_quick_entry_defaults (
466 p_request_id IN NUMBER,
467 p_template_id IN NUMBER,
468 x_field_names OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE, --File.Sql.39 bug 4440895
469 x_field_values OUT NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE, --File.Sql.39 bug 4440895
470 x_field_types OUT NOCOPY SYSTEM.PA_VARCHAR2_30_TBL_TYPE, --File.Sql.39 bug 4440895
471 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
472 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
473 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
474
475 -- Declare local variables
476 l_name VARCHAR(80);
477 l_segment1 VARCHAR(80);
478 l_description VARCHAR(300);
479 l_project_value NUMBER;
480 l_opp_value_currency_code VARCHAR(15);
481 l_expected_approval_date DATE ;
482 --This variable holds customer account name
483 l_customer_name VARCHAR(300);
484 l_country VARCHAR2(80) ;
485 l_state_region VARCHAR2(80) ;
486 l_city VARCHAR2(80);
487 l_customer_orgnization VARCHAR2(360);
488
489 l_lead_id NUMBER;
490 l_probability NUMBER;
491 -- added for Bug Fix: 4537865
492 l_two_probability NUMBER;
493 -- added for Bug Fix: 4537865
494 l_org_role_type VARCHAR2(80);
495 l_org_role_name VARCHAR2(80);
496 l_request_type VARCHAR2(80);
497 l_probability_member_id NUMBER;
498 l_probability_list_id NUMBER;
499 l_index NUMBER;
500 l_msg_index_out NUMBER;
501 l_org_role_id NUMBER;
502 l_opp_org_role_name VARCHAR2(80);
503
504 l_dest_value_pk2 NUMBER;
505 l_dest_value_pk3 NUMBER;
506 l_dest_value_pk4 NUMBER;
507 l_dest_value_pk5 NUMBER;
508 -- added for Bug Fix: 4537865
509 l_new_msg_data VARCHAR2(2000);
510 -- added for Bug Fix: 4537865
511 l_person_role_type_tab PA_PLSQL_DATATYPES.Char250TabTyp;
512 l_key_member_tab PA_PLSQL_DATATYPES.Char250TabTyp;
513
514 l_lead_number AS_LEADS_ALL.lead_number%TYPE;
515 l_lead_description AS_LEADS_ALL.description%TYPE;
516 l_request_type_meaning FND_LOOKUPS.meaning%TYPE;
517
518 -- Cursor to get the source lead_id for the passed in project request.
519
520 CURSOR cur_lead_id IS
521 SELECT object_id_to1
522 FROM pa_object_relationships
523 WHERE relationship_type = 'A'
524 AND relationship_subtype = 'PROJECT_REQUEST'
525 AND object_type_from = 'PA_PROJECT_REQUESTS'
526 AND object_id_from1 = p_request_id
527 AND object_type_to = 'AS_LEADS';
528
529 -- Cursor to get the probability value for a opportunity
530 -- 2418549: Should not default quick entry is the probability is disabled.
531 CURSOR cur_probability (p_lead_id NUMBER ) IS
532 SELECT
533 l.win_probability
534 FROM as_leads_all l, as_forecast_prob_all_vl p
535 WHERE l.lead_id = p_lead_id
536 AND l.win_probability = p.probability_value
537 AND (p.end_date_active IS NULL OR p.end_date_active >= SYSDATE);
538
539 -- Cursor to get the probability list id for a project template
540 CURSOR cur_probability_list (p_template_id NUMBER ) IS
541 SELECT probability_list_id
542 FROM pa_project_types_all t, pa_projects_all p
543 --Added the org_id join for bug 5561036
544 WHERE t.org_id = p.org_id
545 AND p.project_id = p_template_id
546 AND t.project_type = p.project_type;
547
548 -- Cursor to get the quick entry default values
549 -- Return only one record.
550 -- 2401402: Default country name instead of country code.
551 CURSOR cur_quick_entry_def IS
552 SELECT r.request_name name,
553 r.request_name segment1,
554 r.description description,
555 r.value project_value,
556 r.currency_code opp_value_currency_code,
557 r.expected_project_approval_date expected_approval_date,
558 p.party_name customer_name,--bug#9132476
559 ft.territory_short_name country,
560 lc.state state_region,
561 lc.city city,
562 p.party_name customer_orgnization,
563 r.request_type
564 FROM
565 pa_project_requests r,
566 hz_parties p,
567 hz_party_sites s,
568 --hz_cust_accounts a,--bug#9132476
569 hz_locations lc,
570 fnd_territories_vl ft
571 WHERE r.cust_party_id = p.party_id(+)
572 AND r.cust_party_site_id = s.party_site_id(+)
573 --AND r.cust_account_id = a.cust_account_id(+)--bug#9132476
574 AND s.location_id =lc.location_id(+)
575 AND lc.country = ft.territory_code(+)
576 AND r.request_id = p_request_id;
577
578 -- Cursor to get key members' mapped project person roles and
579 -- key member name. Could be multiple records
580
581 CURSOR cur_key_members IS
582 SELECT
583 rt.project_role_type,
584 rdv.resource_name
585 FROM
586 pa_project_role_types rt,
587 pa_proj_request_directory_v rdv
588 WHERE
589 rdv.request_id = p_request_id
590 AND rt.project_role_id =rdv.project_role_id
591 AND rdv.owner_flag = 'Y' -- Bug 6195865
592 ORDER BY rdv.resource_name ;
593
594 -- Cursor to get lead number and description for an opportunity
595 CURSOR cur_get_lead_info (p_lead_id NUMBER) IS
596 SELECT lead_number, description
597 FROM as_leads_all
598 WHERE lead_id = p_lead_id;
599
600 BEGIN
601 x_return_status := FND_API.G_RET_STS_SUCCESS;
602
603 debug('Start get_quick_entry_defaults');
604 OPEN cur_quick_entry_def;
605 FETCH cur_quick_entry_def
606 INTO
607 l_name,
608 l_segment1,
609 l_description ,
610 l_project_value ,
611 l_opp_value_currency_code,
612 l_expected_approval_date ,
613 l_customer_name ,
614 l_country ,
615 l_state_region ,
616 l_city ,
617 l_customer_orgnization,
618 l_request_type ;
619
620 debug('After fetch');
621 IF cur_quick_entry_def%NOTFOUND THEN
622 debug('After fetch: 1');
623 CLOSE cur_quick_entry_def;
624 ELSE
625 debug('After fetch: 2');
626 CLOSE cur_quick_entry_def;
627 END IF;
628
629 debug('After close');
630 x_field_names := SYSTEM.PA_VARCHAR2_30_TBL_TYPE('NAME');
631 x_field_values := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE(l_name);
632 x_field_types := SYSTEM.PA_VARCHAR2_30_TBL_TYPE(null);
633
634 debug('After init of tables');
635 x_field_names.extend(9);
636 x_field_values.extend(9);
637 x_field_types.extend(9);
638
639 debug('After extend of tables');
640 x_field_names(2) := 'SEGMENT1';
641 x_field_values(2) := l_segment1;
642 x_field_types(2) := null;
643 x_field_names(3) := 'DESCRIPTION';
644 x_field_values(3) := l_description;
645 x_field_types(3) := null;
646 x_field_names(4) := 'PROJECT_VALUE';
647 x_field_values(4) := l_project_value;
648 x_field_types(4) := null;
649 x_field_names(5) := 'OPP_VALUE_CURRENCY_CODE';
650 x_field_values(5) := l_opp_value_currency_code ;
651 x_field_types(5) := null;
652 x_field_names(6) := 'EXPECTED_APPROVAL_DATE';
653 x_field_values(6) := l_expected_approval_date ;
654 x_field_types(6) := null;
655 x_field_names(7) := 'CUSTOMER_NAME'; --customer account
656 x_field_values(7) := l_customer_name;
657 x_field_types(7) := null;
658 x_field_names(8) := 'COUNTRY';
659 x_field_values(8) := l_country;
660 x_field_types(8) := null;
661 x_field_names(9) := 'STATE_REGION';
662 x_field_values(9) := l_state_region;
663 x_field_types(9) := null;
664 x_field_names(10) := 'CITY';
665 x_field_values(10) := l_city;
666 x_field_types(10) := null;
667
668 OPEN cur_lead_id;
669 FETCH cur_lead_id
670 INTO l_lead_id;
671 CLOSE cur_lead_id;
672
673 Debug('l_lead_id = ' || l_lead_id);
674
675 OPEN cur_probability(l_lead_id);
676 FETCH cur_probability
677 INTO l_probability;
678 IF cur_probability%NOTFOUND THEN
679 CLOSE cur_probability;
680 l_probability := NULL;
681 ELSE
682 CLOSE cur_probability;
683 END IF;
684 debug('Before calling get mapped probability');
685 debug('Source Probability = ' || l_probability);
686
687 OPEN cur_probability_list(p_template_id);
688 FETCH cur_probability_list
689 INTO l_probability_list_id;
690 debug('proability_list_id = '|| l_probability_list_id);
691
692 --get mapped project probability
693 IF l_probability IS NOT NULL THEN
694 PA_MAPPING_PVT.get_dest_values(
695 p_value_map_def_type => 'PROBABILITY_OPP_PROJ',
696 p_def_subtype => l_request_type,
697 p_source_value => l_probability,
698 p_source_value_pk1 => NULL,
699 p_source_value_pk2 => NULL,
700 p_source_value_pk3 => NULL,
701 p_source_value_pk4 => NULL,
702 p_source_value_pk5 => NULL,
703 p_probability_list_id => l_probability_list_id,
704 --x_dest_value => l_probability, * Commented for Bug: 4537865
705 x_dest_value => l_two_probability, -- added for Bug: 4537865
706 x_dest_value_pk1 => l_probability_member_id,
707 x_dest_value_pk2 => l_dest_value_pk2 ,
708 x_dest_value_pk3 => l_dest_value_pk3,
709 x_dest_value_pk4 => l_dest_value_pk4,
710 x_dest_value_pk5 => l_dest_value_pk5,
711 x_return_status => x_return_status ,
712 x_msg_count => x_msg_count ,
713 x_msg_data => x_msg_data );
714 END IF;
715 -- added for Bug Fix: 4537865
716 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
717 l_probability := l_two_probability;
718 END IF;
719 -- added for Bug Fix: 4537865
720
721 debug('After calling get mapped probability');
722 debug('Mapped Project Probability = ' || l_probability);
723 debug('Mapped Project Probability Member ID = ' || l_probability_member_id);
724
725 --get opportunity organization role.
726
727 SELECT meaning
728 INTO l_opp_org_role_name
729 FROM pa_lookups
730 WHERE lookup_type='OPPORTUNITY_ORG_ROLE'
731 AND lookup_code = 'CUSTOMER';
732
733 debug('l_opp_org_role_name = ' || l_opp_org_role_name);
734
735 --get mapped organization role
736
737 PA_MAPPING_PVT.get_dest_values(
738 p_value_map_def_type => 'ORG_ROLE_OPP_PROJ',
739 p_def_subtype => l_request_type,
740 p_source_value => l_opp_org_role_name,
741 p_source_value_pk1 => NULL,
742 p_source_value_pk2 => NULL,
743 p_source_value_pk3 => NULL,
744 p_source_value_pk4 => NULL,
745 p_source_value_pk5 => NULL,
746 x_dest_value => l_org_role_name,
747 x_dest_value_pk1 => l_org_role_id,
748 x_dest_value_pk2 => l_dest_value_pk2 ,
749 x_dest_value_pk3 => l_dest_value_pk3,
750 x_dest_value_pk4 => l_dest_value_pk4,
751 x_dest_value_pk5 => l_dest_value_pk5,
752 x_return_status => x_return_status ,
753 x_msg_count => x_msg_count ,
754 x_msg_data => x_msg_data );
755
756 x_field_names.extend(1);
757 x_field_values.extend(1);
758 x_field_types.extend(1);
759
760 x_field_names(11) := 'PROBABILITY_MEMBER_ID';
761 x_field_values(11) := l_probability_member_id;
762 x_field_types(11) := null;
763
764 if (l_org_role_id is not null) then
765 BEGIN
766 select project_role_type
767 into l_org_role_type
768 from pa_project_role_types_vl
769 where project_role_id = l_org_role_id;
770 EXCEPTION WHEN NO_DATA_FOUND THEN
771 l_org_role_type := null;
772 debug('No org role type for role.');
773 END;
774
775 if (l_org_role_type is not null) then
776 x_field_names.extend(1);
777 x_field_values.extend(1);
778 x_field_types.extend(1);
779 x_field_names(12) := 'ORG_ROLE'; --ORGNIZATION_ROLE
780 x_field_values(12) := l_customer_orgnization;
781 x_field_types(12) := l_org_role_type;
782 end if;
783
784 end if;
785
786 /* Added if condition Bug 3632760 Need not create project team members by
787 * default in new ASN model*/
788 /* Removing if condition as part of opportunity owner mapping enhancement */
789 /* IF (FND_PROFILE.Value('AS_ACTIVATE_SALES_INTEROP') IS NULL) THEN */
790
791 OPEN cur_key_members;
792 FETCH cur_key_members
793 BULK COLLECT INTO
794 l_person_role_type_tab,
795 l_key_member_tab
796 LIMIT 150;
797 CLOSE cur_key_members ;
798
799 l_index := x_field_names.count + 1;
800 IF NVL(l_key_member_tab.COUNT,0) <> 0 THEN
801
802 x_field_names.extend(l_key_member_tab.COUNT);
803 x_field_values.extend(l_key_member_tab.COUNT);
804 x_field_types.extend(l_key_member_tab.COUNT);
805
806 FOR i IN l_key_member_tab.FIRST..l_key_member_tab.LAST LOOP
807 x_field_names(l_index) := 'KEY_MEMBER';
808 x_field_values(l_index) := l_key_member_tab(i);
809 x_field_types(l_index) := l_person_role_type_tab(i);
810 l_index := l_index +1;
811 END LOOP;
812 END IF;
813
814 /* END IF; */
815
816
817 l_key_member_tab.delete;
818 l_person_role_type_tab.delete;
819
820 -- Project Long Name changes
821 l_index := x_field_names.count + 1;
822 x_field_names.extend(1);
823 x_field_values.extend(1);
824 x_field_types.extend(1);
825
826 OPEN cur_get_lead_info(l_lead_id);
827 FETCH cur_get_lead_info
828 INTO l_lead_number, l_lead_description;
829
830 SELECT meaning
831 INTO l_request_type_meaning
832 FROM pa_lookups
833 WHERE lookup_type = 'PROJECT_REQUEST_TYPE'
834 AND lookup_code = l_request_type;
835
836 x_field_names(l_index) := 'LONG_NAME';
837 x_field_values(l_index):= l_request_type_meaning ||' '||
838 SUBSTR(l_lead_description, 1, 120)||' '||
839 l_lead_number;
840 x_field_types(l_index) := null;
841
842 EXCEPTION
843 WHEN OTHERS THEN
844
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
846 x_msg_count := FND_MSG_PUB.Count_Msg;
847 x_msg_data := substr(SQLERRM,1,240);
848
849 -- Set the excetption Message and the stack
850 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_REQUEST_PVT.get_quick_entry_defaults'
851 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
852 IF x_msg_count = 1 THEN
853 pa_interface_utils_pub.get_messages
854 (p_encoded => FND_API.G_TRUE,
855 p_msg_index => 1,
856 p_msg_count => x_msg_count,
857 p_msg_data => x_msg_data,
858 --p_data => x_msg_data, * Commented for Bug: 4537865
859 p_data => l_new_msg_data, -- added for Bug Fix: 4537865
860 p_msg_index_out => l_msg_index_out );
861 -- added for Bug Fix: 4537865
862 x_msg_data := l_new_msg_data;
863 -- added for Bug Fix: 4537865
864 END IF;
865
866 RAISE; -- This is optional depending on the needs
867
868 END get_quick_entry_defaults;
869
870 --Procedure: manage_project_requests
871 --Purpose: This procedure is called by concurrent program. It calls
872 --Procedure create_project_requests and update_projects.
873
874 PROCEDURE manage_project_requests
875 (p_run_mode IN VARCHAR2,
876 p_source_application_id IN NUMBER,
877 p_request_type IN VARCHAR2,
878 p_probability_from IN NUMBER,
879 p_probability_to IN NUMBER,
880 p_closed_date_within_days IN NUMBER,
881 p_status IN VARCHAR2,
882 p_sales_stage_id IN NUMBER,
883 p_value_from IN NUMBER,
884 p_value_to IN NUMBER,
885 p_currency_code IN VARCHAR2,
886 p_classification IN VARCHAR2,
887 p_calling_module IN VARCHAR2,
888 p_update_probability IN VARCHAR2,
889 p_update_value IN VARCHAR2,
890 p_update_exp_appr_date IN VARCHAR2,
891 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
892 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
893 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
894
895 l_is_profile_defined VARCHAR2(1) := NULL;
896 l_msg_index_out NUMBER;
897 incompatible_prog EXCEPTION;
898 -- added for Bug: 4537865
899 l_new_msg_data VARCHAR2(2000);
900 -- added for Bug: 4537865
901 BEGIN
902 Debug('Enter manage_project_requests');
903
904 x_return_status := FND_API.G_RET_STS_SUCCESS;
905
906 IF (FND_PROFILE.Value('AS_ACTIVATE_SALES_INTEROP') IS NULL) THEN
907 l_is_profile_defined := 'N' ;
908 ELSE
909 l_is_profile_defined := 'Y';
910 END IF;
911
912 Debug('l_is_profile_defined is [' || l_is_profile_defined||'] calling module ['||p_calling_module ||']');
913 -- Raise exception if
914 -- 1. If PRC: Manage Project Requests and Maintain Projects is called from OSO
915 -- 2. If PRC: Manage Project Request is run when ASN is installed
916
917 IF ( (p_calling_module = 'Oracle Sales Online' and l_is_profile_defined = 'Y' ) OR
918 (p_calling_module = 'Oracle Sales' and l_is_profile_defined = 'N' ) ) THEN
919
920 RAISE incompatible_prog ;
921
922 END IF;
923
924 IF p_run_mode = 'CREATE_REQUEST' OR
925 p_run_mode = 'CREATE_AND_UPDATE' THEN
926
927 --Call procedure create_project_requests
928 Debug('Call procedure create_project_requests ');
929 create_project_requests
930 (p_source_application_id ,
931 p_request_type ,
932 p_probability_from ,
933 p_probability_to ,
934 p_closed_date_within_days ,
935 p_status ,
936 p_sales_stage_id ,
937 p_value_from ,
938 p_value_to ,
939 p_currency_code ,
940 p_classification ,
941 l_is_profile_defined ,
942 x_return_status ,
943 x_msg_count ,
944 x_msg_data) ;
945 END IF;
946
947 IF p_run_mode = 'UPDATE_PROJECT' OR
948 p_run_mode = 'CREATE_AND_UPDATE' THEN
949
950 debug('Call procedure update_projects');
951 --Call procedure update_projects
952 update_projects
953 (p_source_application_id ,
954 p_request_type ,
955 p_probability_from ,
956 p_probability_to ,
957 p_closed_date_within_days ,
958 p_status ,
959 p_sales_stage_id ,
960 p_value_from ,
961 p_value_to ,
962 p_currency_code ,
963 p_classification ,
964 l_is_profile_defined ,
965 p_update_probability ,
966 p_update_value ,
967 p_update_exp_appr_date ,
968 x_return_status ,
969 x_msg_count ,
970 x_msg_data);
971 END IF;
972
973 EXCEPTION
974
975 WHEN incompatible_prog THEN
976
977 x_return_status := FND_API.G_RET_STS_ERROR;
978
979 IF (p_calling_module = 'Oracle Sales Online' and l_is_profile_defined = 'Y' ) THEN
980
981 PA_UTILS.add_message(p_app_short_name => 'PA',
982 p_msg_name => 'PA_ORG_SALE_INCOMPAT_PROGRAM');
983
984 x_return_status := FND_API.G_RET_STS_ERROR;
985 x_msg_count := FND_MSG_PUB.Count_Msg;
986 x_msg_data := ' PA_ORG_SALE_INCOMPAT_PROGRAM';
987
988 ELSIF (p_calling_module = 'Oracle Sales' and l_is_profile_defined = 'N' ) THEN
989
990 PA_UTILS.add_message(p_app_short_name => 'PA',
991 p_msg_name => 'PA_ORG_SALE_ONLINE_INCOMPAT');
992
993 x_return_status := FND_API.G_RET_STS_ERROR;
994 x_msg_count := FND_MSG_PUB.Count_Msg;
995 x_msg_data := 'PA_ORG_SALE_ONLINE_INCOMPAT';
996
997 END IF;
998
999 IF x_msg_count = 1 THEN
1000 pa_interface_utils_pub.get_messages
1001 (p_encoded => FND_API.G_FALSE,
1002 p_msg_index => 1,
1003 p_msg_count => x_msg_count,
1004 p_msg_data => x_msg_data,
1005 --p_data => x_msg_data, * commented for Bug: 4537865
1006 p_data => l_new_msg_data, -- added for Bug: 4537865
1007 p_msg_index_out => l_msg_index_out );
1008 -- added for Bug: 4537865
1009 x_msg_data := l_new_msg_data;
1010 -- added for Bug: 4537865
1011 END IF;
1012 debug('');
1013 debug('+-----------------------------------------------------------------------------+');
1014 debug('Exception '||x_msg_data);
1015 debug('+-----------------------------------------------------------------------------+');
1016 WHEN OTHERS THEN
1017 x_msg_count := 1;
1018 x_msg_data := sqlerrm;
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1020 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECT_REQUEST_PVT',
1021 p_procedure_name => 'manage_project_requests');
1022 RAISE;
1023
1024 END manage_project_requests;
1025
1026
1027 --Procedure: create_project_requests
1028 --Purpose: This procedure is called by manage_project_requests.
1029 -- It creats the project requests for the user specified
1030 -- opportunities
1031
1032 PROCEDURE create_project_requests
1033 (p_source_application_id IN NUMBER,
1034 p_request_type IN VARCHAR2,
1035 p_probability_from IN NUMBER,
1036 p_probability_to IN NUMBER,
1037 p_closed_date_within_days IN NUMBER,
1038 p_status IN VARCHAR2,
1039 p_sales_stage_id IN NUMBER,
1040 p_value_from IN NUMBER,
1041 p_value_to IN NUMBER,
1042 p_currency_code IN VARCHAR2,
1043 p_classification IN VARCHAR2,
1044 p_is_profile_defined IN VARCHAR2,
1045 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1046 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1047 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1048 IS
1049 -- Cursor to select all the opportunities based on the parameters entered
1050 -- by user for project requests creation.
1051 TYPE cur_sel_opportunities_typ IS REF CURSOR;
1052 cur_sel_opportunities cur_sel_opportunities_typ ;
1053
1054 stmt_class1 VARCHAR2(1000);
1055 stmt_class2 VARCHAR2(1000);
1056 stmt_class3 VARCHAR2(1000);
1057 stmt_class4 VARCHAR2(1000);
1058 stmt_class5 VARCHAR2(1000);
1059 stmt_categ1 VARCHAR2(1000);
1060 stmt_categ2 VARCHAR2(1000);
1061 stmt_categ3 VARCHAR2(1000);
1062 stmt_categ4 VARCHAR2(1000);
1063 stmt_categ5 VARCHAR2(1000);
1064
1065 stmt VARCHAR2(3200);
1066
1067
1068 -- cur_sel_opportunities_rec cur_sel_opp_temp%ROWTYPE;
1069 TYPE cur_sel_opportunities_rec_type IS RECORD
1070 ( request_name varchar2(300),
1071 description varchar2(240),
1072 cust_party_id number,
1073 cust_party_site_id number,
1074 value number,
1075 currency_code varchar2(15),
1076 expected_project_approval_date date,
1077 source_reference varchar2(30),
1078 lead_id number,
1079 source_org_id number,
1080 category varchar2(1000)
1081 );
1082
1083 cur_sel_opportunities_rec cur_sel_opportunities_rec_type;
1084
1085 CURSOR cur_sel_account_id (p_party_id NUMBER ) IS
1086 SELECT cust_account_id
1087 FROM hz_cust_accounts
1088 WHERE party_id = p_party_id;
1089
1090 CURSOR cur_report_info (p_request_id NUMBER ) IS
1091 SELECT
1092 p.party_name request_customer,
1093 l.country,
1094 l.state,
1095 l.city
1096 FROM
1097 pa_project_requests r,
1098 hz_parties p,
1099 hz_party_sites s,
1100 hz_locations l
1101 WHERE
1102 r.cust_party_id = p.party_id(+)
1103 AND r.cust_party_site_id = s.party_site_id(+)
1104 AND s.location_id =l.location_id(+)
1105 AND r.request_id =p_request_id;
1106 -- 2910113: Removed FOR UPDATE because the cursor triggers
1107 -- ORA-02014 error. Locking is not necessary for this cursor.
1108 -- FOR UPDATE
1109 -- End of 2910113
1110
1111 l_request_customer hz_parties.party_name%TYPE;
1112 l_country hz_locations.country%TYPE;
1113 l_state hz_locations.state%TYPE;
1114 l_city hz_locations.city%TYPE;
1115 l_account_id NUMBER;
1116 l_number_of_accounts NUMBER;
1117 l_cust_party_id NUMBER;
1118 l_msg_data VARCHAR2(2000);
1119 l_msg_count NUMBER;
1120 l_request_id NUMBER;
1121 l_request_number VARCHAR2(25);
1122 v_count BINARY_INTEGER := 0;
1123
1124 -- 2384213: p_classification is a concatinated string like
1125 -- 'interest_type_id/primary_interest_code_id/secondary_interest_id'. This string
1126 -- needs to be broken down into the following three local variables.
1127
1128 l_interest_type_id as_interests_all.interest_type_id%TYPE :=null;
1129 l_primary_interest_code_id as_interests_all.primary_interest_code_id%TYPE :=null;
1130 l_secondary_interest_code_id as_interests_all.secondary_interest_code_id%TYPE :=null;
1131 l_position_1 NUMBER;
1132 l_position_2 NUMBER;
1133
1134 BEGIN
1135 Debug('entered create project request');
1136
1137 stmt_class1 := 'SELECT DISTINCT lookups.meaning || '' '' || nvl(l.lead_number,'''') request_name,l.description description, '
1138 || 'l.customer_id cust_party_id,'
1139 || 'l.address_id cust_party_site_id,'
1140 || 'l.total_amount value,'
1141 || 'l.currency_code currency_code,'
1142 || 'l.decision_date expected_project_approval_date,l.lead_number source_reference,'
1143 || 'l.lead_id lead_id,'
1144 || 'l.org_id source_org_id,'
1145 || 'null as category ' ; /* removed h.category for bug 3744823 */
1146
1147 stmt_class2 := ' FROM as_leads l, as_interests i, pa_lookups lookups'
1148 || ' WHERE '
1149 || 'lookups.lookup_type = ''PROJECT_REQUEST_TYPE'' '
1150 || ' AND lookups.lookup_code = :1'
1151 || ' AND ('
1152 || ' l.win_probability >= nvl(:2, 0) OR'
1153 || ' (l.win_probability IS NULL AND :2 IS NULL)'
1154 ||' ) '
1155 ||' AND ( l.win_probability <= nvl(:3, 100) OR'
1156 ||' (l.win_probability IS NULL AND :3 IS NULL )'
1157 ||' ) '
1158 ||' AND l.status = nvl(:4,l.status) '
1159 ||' AND (l.decision_date >= sysdate - nvl(:5, 365000 ) OR'
1160 ||' l.decision_date IS NULL )';
1161
1162 stmt_class3 := ' AND ('
1163 ||' l.sales_stage_id = nvl(:6,l.sales_stage_id) OR'
1164 ||' (l.sales_stage_id IS NULL AND :6 IS NULL)'
1165 ||' )'
1166 ||'AND ( l.total_amount >= nvl(:7, l.total_amount ) OR'
1167 ||' (l.total_amount IS NULL AND :7 IS NULL)'
1168 ||' ) '
1169 ||'AND ( l.total_amount <=nvl(:8,l.total_amount ) OR '
1170 ||' (l.total_amount IS NULL AND :8 IS NULL)'
1171 ||' ) '
1172 ||'AND ( l.currency_code =nvl(:9, l.currency_code) OR'
1173 ||' (l.currency_code IS NULL AND :9 IS NULL)'
1174 ||' )';
1175
1176 stmt_class4 :=' AND ( '
1177 ||' i.interest_type_id = nvl(:10,i.interest_type_id) OR'
1178 ||' (i.interest_type_id IS NULL AND :10 IS NULL)'
1179 ||' )'
1180 ||'AND ( '
1181 ||' i.primary_interest_code_id = nvl(:11, i.primary_interest_code_id) OR'
1182 ||' (i.primary_interest_code_id IS NULL AND :11 IS NULL)'
1183 || ' )'
1184 ||'AND ( '
1185 ||' i.secondary_interest_code_id = nvl(:12, i.secondary_interest_code_id) OR'
1186 ||' (i.secondary_interest_code_id IS NULL AND :12 IS NULL)'
1187 ||' )'
1188 ||'AND l.lead_id = i.lead_id (+)';
1189
1190 --Modified stmt_Class5 for bug 5728842. Added o.object_type_to = 'PA_PROJECT_REQUESTS' condition.
1191 stmt_class5 :='AND l.lead_id NOT IN'
1192 ||' (SELECT l.lead_id'
1193 ||' FROM as_leads l,'
1194 ||' pa_object_relationships o,'
1195 ||' pa_project_requests r'
1196 ||' WHERE l.lead_id = o.object_id_from1'
1197 ||' AND o.object_id_to1 = r.request_id'
1198 ||' AND o.object_type_to = ''PA_PROJECT_REQUESTS'''
1199 ||' AND r.request_type = :13 '
1200 ||' AND o.relationship_type=''A'''
1201 ||' AND o.relationship_subtype =''PROJECT_REQUEST'''
1202 ||' AND object_type_from = ''AS_LEADS'')';
1203
1204
1205 stmt_categ1 := 'SELECT DISTINCT lookups.meaning || '' '' || nvl(l.lead_number,'''') request_name,'
1206 ||' l.description description ,'
1207 ||' l.customer_id cust_party_id, '
1208 ||' l.address_id cust_party_site_id,'
1209 ||' l.total_amount value,'
1210 ||' l.currency_code currency_code,'
1211 ||' l.decision_date expected_project_approval_date,'
1212 ||' l.lead_number source_reference,'
1213 ||' l.lead_id lead_id,'
1214 ||' l.org_id source_org_id,'
1215 ||' null as category ';
1216
1217 stmt_categ2 := ' FROM as_leads l'
1218 ||' , as_lead_lines_all ll'
1219 ||' , pa_lead_categories_v h'
1220 ||' , pa_lookups lookups '
1221 ||' WHERE '
1222 ||' lookups.lookup_type = ''PROJECT_REQUEST_TYPE'' '
1223 ||' AND lookups.lookup_code = :1 '
1224 ||' AND ll.product_category_id = h.category_id'
1225 ||' AND l.lead_id = ll.lead_id '
1226 ||' AND ('
1227 ||' l.win_probability >= nvl(:2, 0) OR'
1228 ||' (l.win_probability IS NULL AND :2 IS NULL)'
1229 ||' ) '
1230 ||' AND ('
1231 ||' l.win_probability <= nvl(:3, 100) OR'
1232 ||' (l.win_probability IS NULL AND :3 IS NULL )'
1233 ||' ) ';
1234
1235 stmt_categ3 := ' AND l.status = nvl(:4,l.status) '
1236 ||' AND (l.decision_date >= sysdate - nvl(:5, 365000 ) OR'
1237 ||' l.decision_date IS NULL )'
1238 ||' AND ('
1239 ||' l.sales_stage_id = nvl(:6,l.sales_stage_id) OR'
1240 ||' (l.sales_stage_id IS NULL AND :6 IS NULL)'
1241 ||' )'
1242 ||' AND ('
1243 ||' l.total_amount >= nvl(:7, l.total_amount ) OR'
1244 ||' (l.total_amount IS NULL AND :7 IS NULL)'
1245 ||' ) '
1246 ||' AND ('
1247 ||' l.total_amount <=nvl(:8,l.total_amount ) OR '
1248 ||' (l.total_amount IS NULL AND :8 IS NULL)'
1249 ||' ) ';
1250
1251 stmt_categ4 :=' AND ('
1252 ||' l.currency_code =nvl(:9, l.currency_code) OR'
1253 ||' (l.currency_code IS NULL AND :9 IS NULL)'
1254 ||' )'
1255 ||' AND ('
1256 ||' h.category = nvl(:10, h.category) OR'
1257 ||' ( h.category like :10||''/%'')'
1258 ||' ) ';
1259
1260 stmt_categ5 :=' AND l.lead_id NOT IN'
1261 ||' (SELECT l.lead_id'
1262 ||' FROM as_leads l,'
1263 ||' pa_object_relationships o,'
1264 ||' pa_project_requests r'
1265 ||' WHERE l.lead_id = o.object_id_from1'
1266 ||' AND o.object_id_to1 = r.request_id'
1267 ||' AND r.request_type =:11 '
1268 ||' AND o.relationship_type=''A'' '
1269 ||' AND o.relationship_subtype =''PROJECT_REQUEST'' '
1270 ||' AND object_type_from = ''AS_LEADS'')';
1271
1272 x_return_status := FND_API.G_RET_STS_SUCCESS;
1273
1274
1275 IF (p_is_profile_defined = 'N') THEN
1276 -- Process p_classification.
1277 l_position_1 := INSTR(p_classification, '/');
1278 l_position_2 := INSTR(p_classification, '/', 1, 2);
1279
1280 IF l_position_1 = 0 THEN
1281 l_interest_type_id := TO_NUMBER(p_classification);
1282 l_primary_interest_code_id := NULL;
1283 l_secondary_interest_code_id := NULL;
1284 ELSIF l_position_2 = 0 THEN
1285 l_interest_type_id := TO_NUMBER(SUBSTR(p_classification, 1, l_position_1-1));
1286 l_primary_interest_code_id := TO_NUMBER(SUBSTR(p_classification, l_position_1+1));
1287 l_secondary_interest_code_id := NULL;
1288 ELSE
1289 l_interest_type_id := TO_NUMBER(SUBSTR(p_classification, 1, l_position_1-1));
1290 l_primary_interest_code_id := TO_NUMBER(SUBSTR(p_classification, l_position_1+1, l_position_2-l_position_1-1));
1291 l_secondary_interest_code_id := TO_NUMBER(SUBSTR(p_classification, l_position_2+1));
1292 END IF;
1293
1294 Debug('l_interest_type_id = '|| l_interest_type_id);
1295 Debug('l_primary_interest_code_id = '|| l_primary_interest_code_id);
1296 Debug('l_secondary_interest_code_id = '|| l_secondary_interest_code_id);
1297
1298 END IF;
1299
1300 IF (p_is_profile_defined = 'N') THEN
1301 Debug(substr(stmt_class1,1,250));
1302 Debug(substr(stmt_class1,251,500));
1303 Debug(substr(stmt_class1,501,750));
1304 Debug(substr(stmt_class1,751,1000));
1305 Debug(substr(stmt_class1,1001,1250));
1306 Debug(substr(stmt_class1,1251,1500));
1307 Debug(substr(stmt_class1,1501,1750));
1308 Debug(substr(stmt_class1,1751,2000));
1309 Debug(substr(stmt_class2,1,250));
1310 Debug(substr(stmt_class2,251,500));
1311 Debug(substr(stmt_class2,501,750));
1312 Debug(substr(stmt_class2,751,1000));
1313 Debug(substr(stmt_class2,1001,1250));
1314 Debug(substr(stmt_class2,1251,1500));
1315 Debug(substr(stmt_class2,1501,1750));
1316 Debug(substr(stmt_class2,1751,2000));
1317 Debug(substr(stmt_class3,1,250));
1318 Debug(substr(stmt_class3,251,500));
1319 Debug(substr(stmt_class3,501,750));
1320 Debug(substr(stmt_class3,751,1000));
1321 Debug(substr(stmt_class3,1001,1250));
1322 Debug(substr(stmt_class3,1251,1500));
1323 Debug(substr(stmt_class3,1501,1750));
1324 Debug(substr(stmt_class3,1751,2000));
1325 Debug(substr(stmt_class4,1,250));
1326 Debug(substr(stmt_class4,251,500));
1327 Debug(substr(stmt_class4,501,750));
1328 Debug(substr(stmt_class4,751,1000));
1329 Debug(substr(stmt_class4,1001,1250));
1330 Debug(substr(stmt_class4,1251,1500));
1331 Debug(substr(stmt_class4,1501,1750));
1332 Debug(substr(stmt_class4,1751,2000));
1333 Debug(substr(stmt_class5,1,250));
1334 Debug(substr(stmt_class5,251,500));
1335 Debug(substr(stmt_class5,501,750));
1336 Debug(substr(stmt_class5,751,1000));
1337 Debug(substr(stmt_class5,1001,1250));
1338 Debug(substr(stmt_class5,1251,1500));
1339 Debug(substr(stmt_class5,1501,1750));
1340 Debug(substr(stmt_class5,1751,2000));
1341
1342
1343 stmt := stmt_class1 || stmt_class2||stmt_class3|| stmt_class4|| stmt_class5;
1344 Debug(':1 ['||p_request_type||'] :2 ['||p_probability_from||']:3 ['||p_probability_to||']:4 ['||p_status||']');
1345 Debug(':5 ['||p_closed_date_within_days||']:6 ['||p_sales_stage_id||']:7 ['||p_value_from||']:8 ['||p_value_to||']');
1346 Debug(':9 ['||p_currency_code||']:10 ['||l_interest_type_id||']:11 ['||l_primary_interest_code_id||']:12 ['||l_secondary_interest_code_id||']');
1347
1348 OPEN cur_sel_opportunities FOR stmt
1349 USING p_request_type, --:1
1350 p_probability_from,p_probability_from, --:2
1351 p_probability_to,p_probability_to, --:3
1352 p_status, --:4
1353 p_closed_date_within_days, --:5
1354 p_sales_stage_id,p_sales_stage_id, --:6
1355 p_value_from,p_value_from, --:7
1356 p_value_to,p_value_to, --:8
1357 p_currency_code,p_currency_code, --:9
1358 l_interest_type_id,l_interest_type_id, --:10
1359 l_primary_interest_code_id,l_primary_interest_code_id, --:11
1360 l_secondary_interest_code_id,l_secondary_interest_code_id, --:12
1361 p_request_type ; --:13
1362 ELSE
1363 stmt := stmt_categ1 ||stmt_categ2||stmt_categ3||stmt_categ4||stmt_categ5;
1364 Debug(substr(stmt_categ1,1,250));
1365 Debug(substr(stmt_categ1,251,500));
1366 Debug(substr(stmt_categ1,501,750));
1367 Debug(substr(stmt_categ1,751,1000));
1368 Debug(substr(stmt_categ1,1001,1250));
1369 Debug(substr(stmt_categ1,1251,1500));
1370 Debug(substr(stmt_categ1,1501,1750));
1371 Debug(substr(stmt_categ1,1751,2000));
1372 Debug(substr(stmt_categ2,1,250));
1373 Debug(substr(stmt_categ2,251,500));
1374 Debug(substr(stmt_categ2,501,750));
1375 Debug(substr(stmt_categ2,751,1000));
1376 Debug(substr(stmt_categ2,1001,1250));
1377 Debug(substr(stmt_categ2,1251,1500));
1378 Debug(substr(stmt_categ2,1501,1750));
1379 Debug(substr(stmt_categ2,1751,2000));
1380 Debug(substr(stmt_categ3,1,250));
1381 Debug(substr(stmt_categ3,251,500));
1382 Debug(substr(stmt_categ3,501,750));
1383 Debug(substr(stmt_categ3,751,1000));
1384 Debug(substr(stmt_categ3,1001,1250));
1385 Debug(substr(stmt_categ3,1251,1500));
1386 Debug(substr(stmt_categ3,1501,1750));
1387 Debug(substr(stmt_categ3,1751,2000));
1388 Debug(substr(stmt_categ4,1,250));
1389 Debug(substr(stmt_categ4,251,500));
1390 Debug(substr(stmt_categ4,501,750));
1391 Debug(substr(stmt_categ4,751,1000));
1392 Debug(substr(stmt_categ4,1001,1250));
1393 Debug(substr(stmt_categ4,1251,1500));
1394 Debug(substr(stmt_categ4,1501,1750));
1395 Debug(substr(stmt_categ4,1751,2000));
1396 Debug(substr(stmt_categ5,1,250));
1397 Debug(substr(stmt_categ5,251,500));
1398 Debug(substr(stmt_categ5,501,750));
1399 Debug(substr(stmt_categ5,751,1000));
1400 Debug(substr(stmt_categ5,1001,1250));
1401 Debug(substr(stmt_categ5,1251,1500));
1402 Debug(substr(stmt_categ5,1501,1750));
1403 Debug(substr(stmt_categ5,1751,2000));
1404
1405 Debug(':1 ['||p_request_type||'] :2 ['||p_probability_from||']:3 ['||p_probability_to||']:4 ['||p_status||']');
1406 Debug(':5 ['||p_closed_date_within_days||']:6 ['||p_sales_stage_id||']:7 ['||p_value_from||']:8 ['||p_value_to||']');
1407 Debug(':9 ['||p_currency_code||']:10 ['||p_classification||']');
1408
1409 OPEN cur_sel_opportunities FOR stmt
1410 USING p_request_type, --:1
1411 p_probability_from,p_probability_from, --:2
1412 p_probability_to,p_probability_to, --:3
1413 p_status, --:4
1414 p_closed_date_within_days, --:5
1415 p_sales_stage_id,p_sales_stage_id, --:6
1416 p_value_from,p_value_from, --:7
1417 p_value_to,p_value_to, --:8
1418 p_currency_code,p_currency_code, --:9
1419 p_classification,p_classification, --:10
1420 p_request_type ; --:11
1421
1422 END IF;
1423
1424 LOOP
1425 FETCH cur_sel_opportunities INTO cur_sel_opportunities_rec;
1426 EXIT WHEN cur_sel_opportunities%NOTFOUND;
1427 v_count := v_count + 1 ;
1428 Debug('PA_PROJECT_REQUEST_PVT.CREATE_PROJECT_REQUESTS: v_count = '|| v_count);
1429 Debug('Category ['||cur_sel_opportunities_rec.category||']' );
1430 l_cust_party_id := cur_sel_opportunities_rec.cust_party_id;
1431 Debug('l_cust_party_id: ' || l_cust_party_id);
1432
1433 --get customer account ID
1434 OPEN cur_sel_account_id (l_cust_party_id);
1435 FETCH cur_sel_account_id
1436 INTO l_account_id;
1437
1438 IF cur_sel_account_id%NOTFOUND THEN
1439 Debug('cur_sel_account_id%NOTFOUND');
1440
1441 CLOSE cur_sel_account_id;
1442 l_account_id:= NULL;
1443 ELSE
1444 CLOSE cur_sel_account_id;
1445 END IF;
1446
1447
1448 Debug('l_account_id: ' || l_account_id);
1449
1450 SELECT count(*)
1451 INTO l_number_of_accounts
1452 FROM hz_cust_accounts
1453 WHERE party_id = l_cust_party_id;
1454
1455 Debug('After select count(*)');
1456
1457 IF l_number_of_accounts >1 THEN
1458 l_account_id:= NULL;
1459 END IF;
1460
1461 Debug('l_account_id: ' || l_account_id);
1462
1463 --Call create project request public API
1464 Debug('Call create project request public API');
1465 PA_PROJECT_REQUEST_PUB.create_project_request
1466 ( p_request_name =>cur_sel_opportunities_rec.request_name,
1467 p_request_type =>p_request_type,
1468 p_request_status_code =>'121',
1469 p_request_status_name => null,
1470 p_description =>cur_sel_opportunities_rec.description,
1471 p_expected_proj_approval_date =>cur_sel_opportunities_rec.expected_project_approval_date,
1472 p_closed_date =>null,
1473 p_source_type =>'ORACLE_APPLICATION',
1474 p_application_id =>p_source_application_id,
1475 p_source_reference =>cur_sel_opportunities_rec.source_reference,
1476 p_source_id =>cur_sel_opportunities_rec.lead_id,
1477 p_source_object =>'AS_LEADS',
1478 p_value =>cur_sel_opportunities_rec.value,
1479 p_currency_code =>cur_sel_opportunities_rec.currency_code,
1480 p_cust_party_id =>cur_sel_opportunities_rec.cust_party_id,
1481 p_cust_party_name => null,
1482 p_cust_party_site_id =>cur_sel_opportunities_rec.cust_party_site_id,
1483 p_cust_party_site_name => null,
1484 p_cust_account_id =>l_account_id,
1485 p_cust_account_name => null,
1486 p_source_org_id => cur_sel_opportunities_rec.source_org_id,
1487 p_attribute_category => null,
1488 p_attribute1 => 'Test Project Request',
1489 p_attribute2 => null,
1490 p_attribute3 => null,
1491 p_attribute4 => null,
1492 p_attribute5 => null,
1493 p_attribute6 => null,
1494 p_attribute7 => null,
1495 p_attribute8 => null,
1496 p_attribute9 => null,
1497 p_attribute10 => null,
1498 p_attribute11 => null,
1499 p_attribute12 => null,
1500 p_attribute13 => null,
1501 p_attribute14 => null,
1502 p_attribute15 => null,
1503 p_create_rel_flag =>'Y',
1504 p_api_version => null,
1505 p_init_msg_list => null,
1506 p_commit => null ,
1507 p_validate_only => null ,
1508 p_max_msg_count => null ,
1509 x_request_id => l_request_id ,
1510 x_request_number => l_request_number,
1511 x_return_status => x_return_status,
1512 x_msg_count =>x_msg_count,
1513 x_msg_data =>x_msg_data );
1514 Debug('l_request_id= ' || l_request_id);
1515 Debug('l_request_number= ' || l_request_number);
1516
1517 Debug('Finish Create Request call');
1518 OPEN cur_report_info(l_request_id);
1519 FETCH cur_report_info
1520 INTO l_request_customer,
1521 l_country,
1522 l_state,
1523 l_city;
1524 CLOSE cur_report_info;
1525
1526 /* Added the If condition for bug 3951787*/
1527 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1528 PA_REQUESTS_CREATION_PKG.insert_row
1529 ( p_request_name =>cur_sel_opportunities_rec.request_name,
1530 p_request_number =>l_request_number,
1531 p_request_type => p_request_type,
1532 p_request_status_name =>'Open',
1533 p_request_customer =>l_request_customer,
1534 p_country =>l_country,
1535 p_state =>l_state,
1536 p_city =>l_city,
1537 p_value =>cur_sel_opportunities_rec.value,
1538 p_currency_code =>cur_sel_opportunities_rec.currency_code,
1539 p_expected_proj_approval_date =>cur_sel_opportunities_rec.expected_project_approval_date,
1540 p_source_reference =>cur_sel_opportunities_rec.source_reference,
1541 x_return_status =>x_return_status,
1542 x_msg_count =>x_msg_count,
1543 x_msg_data =>x_msg_data);
1544 END IF;
1545
1546 -- GET ERROR FROM ERROR STACK
1547 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
1548
1549 FND_MSG_PUB.get (
1550 p_encoded => FND_API.G_FALSE,
1551 p_msg_index => i,
1552 p_data => l_msg_data,
1553 p_msg_index_out => l_msg_count );
1554
1555 IF l_msg_data IS NOT NULL THEN
1556 --Insert the error into the temp table for the report display purpose.
1557
1558 PA_REQUESTS_CREATION_WARN_PKG.insert_row
1559 (p_request_name => cur_sel_opportunities_rec.request_name,
1560 p_warning => l_msg_data,
1561 x_return_status => x_return_status,
1562 x_msg_count => x_msg_count,
1563 x_msg_data => x_msg_data);
1564
1565 END IF;
1566 END LOOP; -- error message loop
1567
1568 IF v_count >=100 THEN
1569 COMMIT;
1570 v_count := 0;
1571 END IF;
1572 END LOOP;
1573 CLOSE cur_sel_opportunities;
1574
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 x_msg_count := 1;
1578 x_msg_data := sqlerrm;
1579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1580 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECT_REQUEST_PVT',
1581 p_procedure_name => 'create_project_requests');
1582 RAISE;
1583
1584 END create_project_requests;
1585
1586 --Procedure: update_projects
1587 --Purpose: This procedure is called by manage_project_requests.
1588 -- It updates the opportunity related project specified
1589 -- by users.
1590
1591
1592 PROCEDURE update_projects
1593 (p_source_application_id IN NUMBER,
1594 p_request_type IN VARCHAR2,
1595 p_probability_from IN NUMBER,
1596 p_probability_to IN NUMBER,
1597 p_closed_date_within_days IN NUMBER,
1598 p_status IN VARCHAR2,
1599 p_sales_stage_id IN NUMBER,
1600 p_value_from IN NUMBER,
1601 p_value_to IN NUMBER,
1602 p_currency_code IN VARCHAR2,
1603 p_classification IN VARCHAR2,
1604 p_is_profile_defined IN VARCHAR2,
1605 p_update_probability IN VARCHAR2,
1606 p_update_value IN VARCHAR2,
1607 p_update_exp_appr_date IN VARCHAR2,
1608 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1609 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1610 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1611
1612
1613 -- Cursor to select all the opportunities related with the created projects
1614 -- based on the parameters entered.
1615 TYPE cur_sel_opportunities_typ IS REF CURSOR;
1616 cur_sel_opportunities cur_sel_opportunities_typ ;
1617
1618 stmt_class1 VARCHAR2(1000);
1619 stmt_class2 VARCHAR2(1000);
1620 stmt_class3 VARCHAR2(1000);
1621 stmt_class4 VARCHAR2(1000);
1622 stmt_class5 VARCHAR2(1000);
1623 stmt_categ1 VARCHAR2(1000);
1624 stmt_categ2 VARCHAR2(1000);
1625 stmt_categ3 VARCHAR2(1000);
1626 stmt_categ4 VARCHAR2(1000);
1627 stmt_categ5 VARCHAR2(1000);
1628
1629 stmt VARCHAR2(3200);
1630
1631
1632 TYPE cur_sel_opportunities_rec_type IS RECORD
1633 ( value number,
1634 currency_code varchar2(15),
1635 expected_approval_date date,
1636 probability number,
1637 request_id number,
1638 lead_id number,
1639 category varchar2(1000)
1640 );
1641
1642 cur_sel_opportunities_rec cur_sel_opportunities_rec_type;
1643
1644 CURSOR cur_old_project_info (p_project_id NUMBER) IS
1645 SELECT
1646 name,
1647 segment1,
1648 s.project_status_name,
1649 m.probability_percentage,
1650 m.probability_list_id,
1651 p.probability_member_id,
1652 a.opportunity_value,
1653 a.opp_value_currency_code,
1654 p.expected_approval_date,
1655 p.project_value,
1656 p.record_version_number,
1657 p.org_id -- Added for Bug#3798344
1658 FROM
1659 pa_projects_all p,
1660 pa_project_statuses s ,
1661 pa_probability_members m ,
1662 pa_project_opp_attrs a
1663 WHERE p.project_status_code = s.project_status_code
1664 AND p.probability_member_id = m.probability_member_id(+)
1665 AND a.project_id = p.project_id
1666 AND p.project_id = p_project_id;
1667
1668
1669
1670 CURSOR cur_wf_ntf_info (p_project_id NUMBER) IS
1671 SELECT PPP.RESOURCE_SOURCE_ID,
1672 PPRT.PROJECT_ROLE_TYPE
1673 FROM PA_PROJECT_PARTIES PPP ,
1674 PA_PROJECT_ROLE_TYPES PPRT
1675 WHERE PPP.PROJECT_ID = p_project_id
1676 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
1677 AND (PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
1678 OR PPRT.PROJECT_ROLE_TYPE ='STAFFING OWNER')
1679 AND trunc(sysdate) BETWEEN trunc(PPP.start_date_active)
1680 AND NVL(trunc(PPP.end_date_active),sysdate);
1681
1682
1683 --Used when sending the notifications, each run of update projects has a unique group_id.
1684
1685 CURSOR cur_wf_ntf_info2 (p_group_id NUMBER) IS
1686 SELECT user_name,
1687 object_id1 project_id,
1688 object_id2 lead_id
1689 FROM pa_wf_ntf_performers
1690 WHERE group_id = p_group_id
1691 ORDER BY user_name,
1692 object_id1;
1693
1694
1695
1696 l_project_name pa_projects_all.name%TYPE;
1697 l_project_number pa_projects_all.segment1%TYPE;
1698 l_project_status_name pa_project_statuses.project_status_name%TYPE;
1699
1700 -- Old pipeline project info.
1701 l_project_value pa_projects_all.project_value%TYPE;
1702 l_probability pa_probability_members.probability_percentage%TYPE;
1703 l_probability_list_id pa_probability_members.probability_list_id%TYPE;
1704 l_probability_member_id pa_probability_members.probability_member_id%TYPE;
1705 l_opportunity_value pa_project_opp_attrs.opportunity_value%TYPE;
1706 l_opp_value_currency_code pa_project_opp_attrs.opp_value_currency_code%TYPE;
1707 l_expected_approval_date pa_projects_all.expected_approval_date%TYPE;
1708
1709 -- New pipeline project info.
1710 l_new_probability pa_probability_members.probability_percentage%TYPE;
1711 l_new_probability_member_id pa_probability_members.probability_member_id%TYPE;
1712 l_new_opportunity_value pa_project_opp_attrs.opportunity_value%TYPE;
1713 l_new_opp_value_currency_code pa_project_opp_attrs.opp_value_currency_code%TYPE;
1714 l_new_expected_approval_date pa_projects_all.expected_approval_date%TYPE;
1715
1716 l_recipient_tab pa_plsql_datatypes.Char240TabTyp;
1717 l_project_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1718 l_lead_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
1719 l_project_count_tab PA_PLSQL_DATATYPES.IdTabTyp;
1720
1721 l_record_version_number NUMBER;
1722 l_dest_value_pk2 NUMBER;
1723 l_dest_value_pk3 NUMBER;
1724 l_dest_value_pk4 NUMBER;
1725 l_dest_value_pk5 NUMBER;
1726 l_msg_data VARCHAR2(2000);
1727 l_msg_count NUMBER;
1728 l_project_id NUMBER;
1729 l_group_id NUMBER;
1730 TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
1731 l_last_recipient VARCHAR2(100);
1732 l_end_recipient VARCHAR2(100);
1733 l_recipient_user_name VARCHAR2(320); /* Modified length from 30 to 320 for bug 2933743 */
1734 l_recipient_display_name VARCHAR2(360); /* Modified length from 240 to 360 for bug 2933743 */
1735 l_view_upd_proj_url VARCHAR2(600);
1736 l_item_type pa_wf_processes.item_type%TYPE;
1737 l_item_key pa_wf_processes.item_key%TYPE;
1738 l_err_code NUMBER := 0;
1739 l_err_stage VARCHAR2(2000);
1740 l_err_stack VARCHAR2(2000);
1741 l_warning VARCHAR2(2000); -- added for bug3632727 - bug 4015199
1742 v_count BINARY_INTEGER := 0;
1743 l_probability_update VARCHAR2(1);
1744 l_opportunity_value_update VARCHAR2(1);
1745 l_approval_date_update VARCHAR2(1);
1746 l_previous_request_id NUMBER;
1747
1748 -- 2384213: p_classification is a concatinated string like
1749 -- 'interest_type_id/primary_interest_code_id/secondary_interest_id'. This string
1750 -- needs to be broken down into the following three local variables.
1751
1752 l_interest_type_id as_interests_all.interest_type_id%TYPE;
1753 l_primary_interest_code_id as_interests_all.primary_interest_code_id%TYPE;
1754 l_secondary_interest_code_id as_interests_all.secondary_interest_code_id%TYPE;
1755 l_position_1 NUMBER;
1756 l_position_2 NUMBER;
1757
1758 -- Added by Sachin for P1 bug 3765557
1759 l_guest_user pa_wf_ntf_performers.user_name%type;
1760
1761 BEGIN
1762 x_return_status := FND_API.G_RET_STS_SUCCESS;
1763 Debug('In procedure update_projects for classification ['||p_classification||']');
1764
1765 stmt_class1 := 'SELECT l.total_amount value, '
1766 || 'l.currency_code currency_code,'
1767 || 'l.decision_date expected_approval_date,'
1768 || 'l.win_probability probability, '
1769 || 'r.request_id request_id,'
1770 || 'l.lead_id lead_id,'
1771 || 'null as category ' ;
1772
1773 stmt_class2 := ' FROM as_leads l, as_interests i, pa_project_requests r, pa_object_relationships o,pa_project_statuses s '
1774 || ' WHERE '
1775 || ' (l.win_probability >= nvl(:1, 0) OR'
1776 || ' (l.win_probability IS NULL AND :1 IS NULL)'
1777 ||' ) '
1778 ||' AND ( l.win_probability <= nvl(:2, 100) OR'
1779 ||' (l.win_probability IS NULL AND :2 IS NULL )'
1780 ||' ) '
1781 ||' AND l.status = nvl(:3,l.status) '
1782 ||' AND (l.decision_date >= sysdate - nvl(:4, 365000 ) OR'
1783 ||' l.decision_date IS NULL )';
1784
1785 stmt_class3 := ' AND ('
1786 ||' l.sales_stage_id = nvl(:5,l.sales_stage_id) OR'
1787 ||' (l.sales_stage_id IS NULL AND :5 IS NULL)'
1788 ||' )'
1789 ||'AND ( l.total_amount >= nvl(:6, l.total_amount ) OR'
1790 ||' (l.total_amount IS NULL AND :6 IS NULL)'
1791 ||' ) '
1792 ||'AND ( l.total_amount <=nvl(:7,l.total_amount ) OR '
1793 ||' (l.total_amount IS NULL AND :7 IS NULL)'
1794 ||' ) '
1795 ||'AND ( l.currency_code =nvl(:8, l.currency_code) OR'
1796 ||' (l.currency_code IS NULL AND :8 IS NULL)'
1797 ||' )';
1798
1799 stmt_class4 :=' AND ( '
1800 ||' i.interest_type_id = nvl(:9,i.interest_type_id) OR'
1801 ||' (i.interest_type_id IS NULL AND :9 IS NULL)'
1802 ||' )'
1803 ||'AND ( '
1804 ||' i.primary_interest_code_id = nvl(:10, i.primary_interest_code_id) OR'
1805 ||' (i.primary_interest_code_id IS NULL AND :10 IS NULL)'
1806 || ' )'
1807 ||'AND ( '
1808 ||' i.secondary_interest_code_id = nvl(:11, i.secondary_interest_code_id) OR'
1809 ||' (i.secondary_interest_code_id IS NULL AND :11 IS NULL)'
1810 ||' )'
1811 ||'AND l.lead_id = o.object_id_from1';
1812
1813 stmt_class5 :=' AND o.object_id_to1 = r.request_id'
1814 ||' AND l.lead_id = i.lead_id (+)'
1815 ||' AND r.status_code =s.project_status_code'
1816 ||' AND s.project_system_status_code =''PROJ_REQ_CLOSED'' '
1817 ||' AND r.request_type =:12'
1818 ||' AND o.relationship_type=''A'' '
1819 ||' AND o.relationship_subtype =''PROJECT_REQUEST'' '
1820 ||' AND o.object_type_from = ''AS_LEADS'' '
1821 ||' AND o.object_type_to = ''PA_PROJECT_REQUESTS'' ';
1822
1823 stmt_categ1 := 'SELECT l.total_amount value, '
1824 || 'l.currency_code currency_code,'
1825 || 'l.decision_date expected_approval_date,'
1826 || 'l.win_probability probability,'
1827 || ' r.request_id request_id,'
1828 || 'l.lead_id lead_id,'
1829 || 'h.category category ' ;
1830
1831 stmt_categ2 := ' FROM as_leads l,as_lead_lines ll,pa_lead_categories_v h,pa_project_requests r, pa_object_relationships o,pa_project_statuses s '
1832 || ' WHERE '
1833 || 'll.product_category_id = h.category_id '
1834 || ' AND l.lead_id = ll.lead_id '
1835 || ' AND ('
1836 || ' l.win_probability >= nvl(:1, 0) OR'
1837 || ' (l.win_probability IS NULL AND :1 IS NULL)'
1838 ||' ) '
1839 ||' AND ( l.win_probability <= nvl(:2, 100) OR'
1840 ||' (l.win_probability IS NULL AND :2 IS NULL )'
1841 ||' ) '
1842 ||' AND l.status = nvl(:3,l.status) '
1843 ||' AND (l.decision_date >= sysdate - nvl(:4, 365000 ) OR'
1844 ||' l.decision_date IS NULL )';
1845
1846 stmt_categ3 := ' AND ('
1847 ||' l.sales_stage_id = nvl(:5,l.sales_stage_id) OR'
1848 ||' (l.sales_stage_id IS NULL AND :5 IS NULL)'
1849 ||' )'
1850 ||'AND ( l.total_amount >= nvl(:6, l.total_amount ) OR'
1851 ||' (l.total_amount IS NULL AND :6 IS NULL)'
1852 ||' ) '
1853 ||'AND ( l.total_amount <=nvl(:7,l.total_amount ) OR '
1854 ||' (l.total_amount IS NULL AND :7 IS NULL)'
1855 ||' ) '
1856 ||'AND ( l.currency_code =nvl(:8, l.currency_code) OR'
1857 ||' (l.currency_code IS NULL AND :8 IS NULL)'
1858 ||' )';
1859
1860 stmt_categ4 :=' AND ( '
1861 ||' h.category = nvl(:9, h.category) OR '
1862 ||' ( h.category like :9||''/%'') '
1863 ||' ) '
1864 ||' AND l.lead_id = o.object_id_from1'
1865 ||' AND o.object_id_to1 = r.request_id'
1866 ||' AND r.status_code =s.project_status_code'
1867 ||' AND s.project_system_status_code =''PROJ_REQ_CLOSED'' '
1868 ||' AND r.request_type =:10'
1869 ||' AND o.relationship_type=''A'' '
1870 ||' AND o.relationship_subtype =''PROJECT_REQUEST'' '
1871 ||' AND o.object_type_from = ''AS_LEADS'' '
1872 ||' AND o.object_type_to = ''PA_PROJECT_REQUESTS'' ';
1873
1874
1875 --Generate group_id for each run of update projects.
1876 --Used by workflow notification.
1877 SELECT pa_wf_ntf_performers_s.nextval
1878 INTO l_group_id
1879 FROM dual;
1880
1881 -- Added by Sachin for P1 bug 3765557
1882 --changed for P1 Bug6727240
1883 l_guest_user := null;
1884 SELECT SUBSTRB(FND_WEB_SEC.GET_GUEST_USERNAME_PWD,1,INSTRB(FND_WEB_SEC.GET_GUEST_USERNAME_PWD, '/' )-1)
1885 into l_guest_user
1886 from dual;
1887 --changed for P1 Bug6727240
1888 -- End bug 3765557
1889
1890 IF (p_is_profile_defined = 'N') THEN
1891 -- Process p_classification.
1892 l_position_1 := INSTR(p_classification, '/');
1893 l_position_2 := INSTR(p_classification, '/', 1, 2);
1894
1895 IF l_position_1 = 0 THEN
1896 l_interest_type_id := TO_NUMBER(p_classification);
1897 l_primary_interest_code_id := NULL;
1898 l_secondary_interest_code_id := NULL;
1899 ELSIF l_position_2 = 0 THEN
1900 l_interest_type_id := TO_NUMBER(SUBSTR(p_classification, 1, l_position_1-1));
1901 l_primary_interest_code_id := TO_NUMBER(SUBSTR(p_classification, l_position_1+1));
1902 l_secondary_interest_code_id := NULL;
1903 ELSE
1904 l_interest_type_id := TO_NUMBER(SUBSTR(p_classification, 1, l_position_1-1));
1905 l_primary_interest_code_id := TO_NUMBER(SUBSTR(p_classification, l_position_1+1, l_position_2-l_position_1-1));
1906 l_secondary_interest_code_id := TO_NUMBER(SUBSTR(p_classification, l_position_2+1));
1907 END IF;
1908
1909 debug('l_interest_type_id = '|| l_interest_type_id);
1910 debug('l_primary_interest_code_id = '|| l_primary_interest_code_id);
1911 debug('l_secondary_interest_code_id = '|| l_secondary_interest_code_id);
1912 END IF;
1913
1914 -- Loop through the project requests cursor and update projects.
1915 l_previous_request_id := NULL;
1916
1917 IF (p_is_profile_defined = 'N') THEN
1918 Debug(substr(stmt_class1,1,250));
1919 Debug(substr(stmt_class1,251,500));
1920 Debug(substr(stmt_class1,501,750));
1921 Debug(substr(stmt_class1,751,1000));
1922 Debug(substr(stmt_class1,1001,1250));
1923 Debug(substr(stmt_class1,1251,1500));
1924 Debug(substr(stmt_class1,1501,1750));
1925 Debug(substr(stmt_class1,1751,2000));
1926 Debug(substr(stmt_class2,1,250));
1927 Debug(substr(stmt_class2,251,500));
1928 Debug(substr(stmt_class2,501,750));
1929 Debug(substr(stmt_class2,751,1000));
1930 Debug(substr(stmt_class2,1001,1250));
1931 Debug(substr(stmt_class2,1251,1500));
1932 Debug(substr(stmt_class2,1501,1750));
1933 Debug(substr(stmt_class2,1751,2000));
1934 Debug(substr(stmt_class3,1,250));
1935 Debug(substr(stmt_class3,251,500));
1936 Debug(substr(stmt_class3,501,750));
1937 Debug(substr(stmt_class3,751,1000));
1938 Debug(substr(stmt_class3,1001,1250));
1939 Debug(substr(stmt_class3,1251,1500));
1940 Debug(substr(stmt_class3,1501,1750));
1941 Debug(substr(stmt_class3,1751,2000));
1942 Debug(substr(stmt_class4,1,250));
1943 Debug(substr(stmt_class4,251,500));
1944 Debug(substr(stmt_class4,501,750));
1945 Debug(substr(stmt_class4,751,1000));
1946 Debug(substr(stmt_class4,1001,1250));
1947 Debug(substr(stmt_class4,1251,1500));
1948 Debug(substr(stmt_class4,1501,1750));
1949 Debug(substr(stmt_class4,1751,2000));
1950 Debug(substr(stmt_class5,1,250));
1951 Debug(substr(stmt_class5,251,500));
1952 Debug(substr(stmt_class5,501,750));
1953 Debug(substr(stmt_class5,751,1000));
1954 Debug(substr(stmt_class5,1001,1250));
1955 Debug(substr(stmt_class5,1251,1500));
1956 Debug(substr(stmt_class5,1501,1750));
1957 Debug(substr(stmt_class5,1751,2000));
1958
1959
1960 stmt := stmt_class1 || stmt_class2||stmt_class3|| stmt_class4|| stmt_class5;
1961 Debug(':12['||p_request_type||']:1 ['||p_probability_from||']:2 ['||p_probability_to||']:3 ['||p_status||']');
1962 Debug(':4 ['||p_closed_date_within_days||']:5 ['||p_sales_stage_id||']:6 ['||p_value_from||']:7 ['||p_value_to||']');
1963 Debug(':8 ['||p_currency_code||']:9 ['||l_interest_type_id||']:10 ['||l_primary_interest_code_id||']:11 ['||l_secondary_interest_code_id||']');
1964
1965 OPEN cur_sel_opportunities FOR stmt
1966 USING p_probability_from,p_probability_from, --:1
1967 p_probability_to,p_probability_to, --:2
1968 p_status, --:3
1969 p_closed_date_within_days, --:4
1970 p_sales_stage_id,p_sales_stage_id, --:5
1971 p_value_from,p_value_from, --:6
1972 p_value_to,p_value_to, --:7
1973 p_currency_code,p_currency_code, --:8
1974 l_interest_type_id,l_interest_type_id, --:9
1975 l_primary_interest_code_id,l_primary_interest_code_id, --:10
1976 l_secondary_interest_code_id,l_secondary_interest_code_id, --:11
1977 p_request_type ; --:12
1978 ELSE
1979 stmt := stmt_categ1 ||stmt_categ2||stmt_categ3||stmt_categ4;
1980 Debug(substr(stmt_categ1,1,250));
1981 Debug(substr(stmt_categ1,251,500));
1982 Debug(substr(stmt_categ1,501,750));
1983 Debug(substr(stmt_categ1,751,1000));
1984 Debug(substr(stmt_categ1,1001,1250));
1985 Debug(substr(stmt_categ1,1251,1500));
1986 Debug(substr(stmt_categ1,1501,1750));
1987 Debug(substr(stmt_categ1,1751,2000));
1988 Debug(substr(stmt_categ2,1,250));
1989 Debug(substr(stmt_categ2,251,500));
1990 Debug(substr(stmt_categ2,501,750));
1991 Debug(substr(stmt_categ2,751,1000));
1992 Debug(substr(stmt_categ2,1001,1250));
1993 Debug(substr(stmt_categ2,1251,1500));
1994 Debug(substr(stmt_categ2,1501,1750));
1995 Debug(substr(stmt_categ2,1751,2000));
1996 Debug(substr(stmt_categ3,1,250));
1997 Debug(substr(stmt_categ3,251,500));
1998 Debug(substr(stmt_categ3,501,750));
1999 Debug(substr(stmt_categ3,751,1000));
2000 Debug(substr(stmt_categ3,1001,1250));
2001 Debug(substr(stmt_categ3,1251,1500));
2002 Debug(substr(stmt_categ3,1501,1750));
2003 Debug(substr(stmt_categ3,1751,2000));
2004 Debug(substr(stmt_categ4,1,250));
2005 Debug(substr(stmt_categ4,251,500));
2006 Debug(substr(stmt_categ4,501,750));
2007 Debug(substr(stmt_categ4,751,1000));
2008 Debug(substr(stmt_categ4,1001,1250));
2009 Debug(substr(stmt_categ4,1251,1500));
2010 Debug(substr(stmt_categ4,1501,1750));
2011 Debug(substr(stmt_categ4,1751,2000));
2012
2013 Debug(':10['||p_request_type||']:1 ['||p_probability_from||']:2 ['||p_probability_to||']:3 ['||p_status||']');
2014 Debug(':4 ['||p_closed_date_within_days||']:5 ['||p_sales_stage_id||']:6 ['||p_value_from||']:7 ['||p_value_to||']');
2015 Debug(':8 ['||p_currency_code||']:9 ['||p_classification||']');
2016
2017 OPEN cur_sel_opportunities FOR stmt
2018 USING p_probability_from,p_probability_from, --:1
2019 p_probability_to,p_probability_to, --:2
2020 p_status, --:3
2021 p_closed_date_within_days, --:4
2022 p_sales_stage_id,p_sales_stage_id, --:5
2023 p_value_from,p_value_from, --:6
2024 p_value_to,p_value_to, --:7
2025 p_currency_code,p_currency_code, --:8
2026 p_classification,p_classification, --:9
2027 p_request_type ; --:10
2028
2029 END IF;
2030
2031 Debug('Cursor has been successfully opened');
2032 Debug('Going into the loop');
2033
2034 LOOP
2035 FETCH cur_sel_opportunities INTO cur_sel_opportunities_rec;
2036 EXIT WHEN cur_sel_opportunities%NOTFOUND;
2037 -- debug('l_previous_request_id = '|| l_previous_request_id);
2038 -- debug('request_id = '||cur_sel_opportunities_rec.request_id);
2039
2040 -- Can not use DISTINCT on the cursor because SELECT FOR UPDATE. Therefore, needs to
2041 -- check duplicate request_id rows from the cursor. Only update once for a request_id.
2042
2043 IF ((l_previous_request_id IS NULL) OR (l_previous_request_id <> cur_sel_opportunities_rec.request_id)) THEN
2044 v_count := v_count +1;
2045 --debug('PA_PROJECT_REQUEST_PVT.UPDATE_PROJECTS: v_count = '|| v_count);
2046 --debug('Category ['||cur_sel_opportunities_rec.category||']' );
2047 l_previous_request_id := cur_sel_opportunities_rec.request_id;
2048
2049 --get the project ID for a passed in request ID, relationship direction: Request -> Project
2050
2051 SELECT object_id_to1
2052 INTO l_project_id
2053 FROM pa_object_relationships
2054 WHERE relationship_type='A'
2055 AND relationship_subtype ='PROJECT_REQUEST'
2056 AND object_type_from ='PA_PROJECT_REQUESTS'
2057 AND object_type_to = 'PA_PROJECTS'
2058 AND object_id_from1 = cur_sel_opportunities_rec.request_id;
2059
2060 debug('Updating this project, project_id = ' || l_project_id );
2061 debug('Old project info ********************');
2062 -- get old project info for the report purpose
2063 OPEN cur_old_project_info (l_project_id);
2064 FETCH cur_old_project_info
2065 INTO l_project_name,
2066 l_project_number,
2067 l_project_status_name,
2068 l_probability,
2069 l_probability_list_id,
2070 l_probability_member_id,
2071 l_opportunity_value,
2072 l_opp_value_currency_code,
2073 l_expected_approval_date,
2074 l_project_value,
2075 l_record_version_number,
2076 PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added for Bug#3798344
2077 CLOSE cur_old_project_info;
2078
2079 debug('l_project_name = ' || l_project_name);
2080 debug('l_project_number = ' || l_project_number);
2081 debug('l_project_status_name =' || l_project_status_name);
2082 debug('l_probability = '|| l_probability);
2083 debug('l_probability_member_id = '|| l_probability_member_id);
2084 debug('l_opportunity_value = ' || l_opportunity_value);
2085 debug('l_opp_value_currency_code = ' || l_opp_value_currency_code);
2086 debug('l_expected_approval_date = ' || l_expected_approval_date);
2087 debug('l_record_version_number= ' || l_record_version_number);
2088
2089 -- Check whether probability is to be updated.
2090 IF p_update_probability = 'Y' THEN
2091 -- Get the new project probability value
2092 PA_MAPPING_PVT.get_dest_values(
2093 p_value_map_def_type => 'PROBABILITY_OPP_PROJ',
2094 p_def_subtype => p_request_type,
2095 p_source_value => cur_sel_opportunities_rec.probability,
2096 p_source_value_pk1 => NULL,
2097 p_source_value_pk2 => NULL,
2098 p_source_value_pk3 => NULL,
2099 p_source_value_pk4 => NULL,
2100 p_source_value_pk5 => NULL,
2101 p_probability_list_id => l_probability_list_id,
2102 x_dest_value => l_new_probability,
2103 x_dest_value_pk1 => l_new_probability_member_id,
2104 x_dest_value_pk2 => l_dest_value_pk2 ,
2105 x_dest_value_pk3 => l_dest_value_pk3,
2106 x_dest_value_pk4 => l_dest_value_pk4,
2107 x_dest_value_pk5 => l_dest_value_pk5,
2108 x_return_status => x_return_status ,
2109 x_msg_count => x_msg_count ,
2110 x_msg_data => x_msg_data );
2111
2112 debug('Call get_dest_values for probabilty: ' || l_new_probability);
2113
2114 IF l_probability <> l_new_probability OR
2115 (l_new_probability IS NULL AND l_probability IS NOT NULL) OR
2116 (l_new_probability IS NOT NULL AND l_probability IS NULL) THEN
2117 l_probability_update := 'Y';
2118 debug('Probability is to be updated: l_new_probability_member_id = '|| l_new_probability_member_id);
2119 ELSE
2120 l_probability_update := 'N';
2121 l_new_probability := l_probability;
2122 l_new_probability_member_id := l_probability_member_id;
2123 END IF;
2124
2125 ELSE
2126 l_probability_update := 'N';
2127 l_new_probability := l_probability;
2128 l_new_probability_member_id := l_probability_member_id;
2129 END IF;
2130
2131 -- Check whether opportunity is to be updated.
2132 IF p_update_value ='Y' AND l_opportunity_value <> cur_sel_opportunities_rec.value THEN
2133 l_opportunity_value_update := 'Y';
2134 l_new_opportunity_value := cur_sel_opportunities_rec.value ;
2135 l_new_opp_value_currency_code := cur_sel_opportunities_rec.currency_code;
2136 -- need to pass the expected_approval_date for rate conversion.
2137 l_new_expected_approval_date := cur_sel_opportunities_rec.expected_approval_date;
2138
2139 debug('Value and Currency will be upated, new value = ' || l_new_opportunity_value || l_new_opp_value_currency_code );
2140
2141 ELSE
2142 l_opportunity_value_update := 'N';
2143 l_new_opportunity_value := l_opportunity_value;
2144 l_new_opp_value_currency_code := l_opp_value_currency_code;
2145 END IF;
2146
2147 -- Check whether expected approval date is to be updated.
2148 IF p_update_exp_appr_date ='Y' AND l_expected_approval_date <> cur_sel_opportunities_rec.expected_approval_date THEN
2149 l_approval_date_update :='Y';
2150 l_new_expected_approval_date := cur_sel_opportunities_rec.expected_approval_date;
2151 debug('Expected Approval Date will be updated, new date = ' || l_new_expected_approval_date);
2152
2153 ELSE
2154 l_approval_date_update :='N';
2155 l_new_expected_approval_date := l_expected_approval_date;
2156 END IF;
2157
2158 debug ('x_msg_count =' || x_msg_count);
2159 debug ('x_msg_data=' || x_msg_data);
2160 debug ('x_return_status = ' || x_return_status);
2161
2162 -- Update pipeline project info.
2163 IF l_probability_update = 'Y' OR l_opportunity_value_update = 'Y' OR l_approval_date_update = 'Y' THEN
2164
2165 -- Call project API to update project
2166 Debug('Begin calling update project API');
2167 Debug('l_new_probability_member_id= ' || l_new_probability_member_id);
2168 Debug('l_new_probability = ' || l_new_probability);
2169 Debug('l_new_opportunity_value = ' || l_new_opportunity_value);
2170 Debug('l_new_opp_value_currency_code = '|| l_new_opp_value_currency_code);
2171 Debug('l_new_expected_approval_date = ' || l_new_expected_approval_date);
2172 Debug('l_project_value = '|| l_project_value);
2173
2174 PA_PROJECTS_MAINT_PUB.UPDATE_PROJECT_PIPELINE_INFO(
2175 p_init_msg_list => FND_API.G_TRUE, -- Changed from G_FALSE for bug 3635099. -- Bug 4015199.
2176 p_commit => FND_API.G_FALSE,
2177 p_validate_only => FND_API.G_FALSE,
2178 p_project_id => l_project_id,
2179 p_probability_member_id => l_new_probability_member_id,
2180 p_probability_percentage => l_new_probability,
2181 p_project_value => l_project_value,
2182 p_opportunity_value => l_new_opportunity_value,
2183 p_opp_value_currency_code=> l_new_opp_value_currency_code,
2184 p_expected_approval_date => l_new_expected_approval_date,
2185 p_record_version_number => l_record_version_number,
2186 x_return_status => x_return_status,
2187 x_msg_count => x_msg_count,
2188 x_msg_data => x_msg_data);
2189
2190 Debug('After calling update project API');
2191 Debug('***** x_return_status = ' || x_return_status );
2192
2193 IF x_return_status = 'S' THEN
2194
2195 -- Insert notification related info into pa_wf_ntf_performers
2196 FOR cur_wf_ntf_info_rec in cur_wf_ntf_info(l_project_id) LOOP
2197
2198 --get user id for the project manager and staffing owners of this project
2199
2200 WF_DIRECTORY.getusername
2201 (p_orig_system => 'PER',
2202 p_orig_system_id => cur_wf_ntf_info_rec.resource_source_id,
2203 p_name => l_recipient_user_name,
2204 p_display_name => l_recipient_display_name);
2205
2206 /* start of bug 3632727 */ -- Bug 4015199
2207 if l_recipient_user_name IS NOT NULL then
2208 /* end of bug 3632727*/
2209
2210 INSERT INTO pa_wf_ntf_performers
2211 (wf_type_code,
2212 item_type,
2213 item_key,
2214 object_id1,
2215 object_id2,
2216 user_name,
2217 user_type,
2218 group_id)
2219 VALUES
2220 ('OM_UPDATE_PROJECTS',
2221 'PAYPRJNT',
2222 '-1',
2223 l_project_id,
2224 cur_sel_opportunities_rec.lead_id,
2225 l_recipient_user_name,
2226 cur_wf_ntf_info_rec.project_role_type,
2227 l_group_id) ;
2228 /* start of bug 3632727 */
2229 else --Bug 4015199
2230 -- populate into temp table
2231 l_warning := fnd_message.get_string('PA','PA_UNAME_NOT_ASSIGNED');
2232
2233 PA_PROJECTS_UPDATE_WARN_PKG.insert_row
2234 ( p_project_name =>l_project_name,
2235 p_warning => l_warning,
2236 x_return_status => x_return_status,
2237 x_msg_count => x_msg_count,
2238 x_msg_data => x_msg_data);
2239 end if; -- l_recipient_user_name IS NOT NULL
2240 /* end of bug 3632727 */
2241
2242 END LOOP;
2243
2244 -- Added by Sachin for P1 bug 3765557
2245 if l_guest_user is not null then
2246 INSERT INTO pa_wf_ntf_performers
2247 (wf_type_code,
2248 item_type,
2249 item_key,
2250 object_id1,
2251 object_id2,
2252 user_name,
2253 user_type,
2254 group_id)
2255 VALUES
2256 ('OM_UPDATE_PROJECTS',
2257 'PAYPRJNT',
2258 '-1',
2259 l_project_id,
2260 cur_sel_opportunities_rec.lead_id,
2261 l_guest_user,
2262 'GUEST',
2263 l_group_id) ;
2264 end if;
2265 -- End bug 3765557
2266
2267
2268 -- Insert into the temp table for report purpose
2269
2270 PA_PROJECTS_UPDATE_PKG.insert_row
2271 (p_project_name =>l_project_name,
2272 p_project_number =>l_project_number,
2273 p_project_status_name =>l_project_status_name,
2274 p_old_probability =>l_probability,
2275 p_new_probability =>l_new_probability,
2276 p_old_value =>l_opportunity_value,
2277 p_new_value =>l_new_opportunity_value,
2278 p_old_value_currency =>l_opp_value_currency_code,
2279 p_new_value_currency =>l_new_opp_value_currency_code,
2280 p_old_exp_proj_apprvl_date =>l_expected_approval_date,
2281 p_new_exp_proj_apprvl_date =>l_new_expected_approval_date,
2282 x_return_status =>x_return_status,
2283 x_msg_count =>x_msg_count,
2284 x_msg_data =>x_msg_data);
2285
2286
2287 ELSE -- Not Succeed
2288
2289 -- GET ERROR FROM ERROR STACK
2290 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
2291
2292 FND_MSG_PUB.get (
2293 p_encoded => FND_API.G_FALSE,
2294 p_msg_index => i,
2295 p_data => l_msg_data,
2296 p_msg_index_out => l_msg_count );
2297
2298 IF l_msg_data IS NOT NULL THEN
2299 --Insert the error into the temp table for the report display purpose.
2300 -- Insert error message to temp table
2301 PA_PROJECTS_UPDATE_WARN_PKG.insert_row
2302 ( p_project_name =>l_project_name,
2303 p_warning => l_msg_data,
2304 x_return_status => x_return_status,
2305 x_msg_count => x_msg_count,
2306 x_msg_data => x_msg_data);
2307 END IF;
2308 END LOOP;
2309 FND_MSG_PUB.Delete_Msg(); --Added for bug 4094370
2310 END IF;
2311 END IF; -- calling pipeline project updates
2312
2313 /* commented for bug 2968585
2314 IF v_count >=100 THEN
2315 COMMIT;
2316 v_count := 0;
2317 END IF;
2318 */
2319 END IF; -- end of checking whether the request_id is a duplicate.
2320
2321 END LOOP;
2322 CLOSE cur_sel_opportunities;
2323
2324 Commit; --added for bug 2968585
2325
2326
2327 --Send notifications to the project managers and staffing owners of the updated projects.
2328 l_recipient_tab.DELETE;
2329 l_project_id_tab.DELETE;
2330 l_lead_id_tab.DELETE;
2331 l_project_count_tab.DELETE;
2332
2333
2334 OPEN cur_wf_ntf_info2 (l_group_id);
2335 FETCH cur_wf_ntf_info2 BULK COLLECT INTO
2336 l_recipient_tab,
2337 l_project_id_tab,
2338 l_lead_id_tab ;
2339 CLOSE cur_wf_ntf_info2;
2340
2341 l_last_recipient := TEXT_DUMMY;
2342 l_item_type := 'PAYPRJNT';
2343
2344 debug('l_group_id = ' || l_group_id);
2345 debug('l_recipient_tab.FIRST = ' || l_recipient_tab.FIRST);
2346 debug('l_recipient_tab.LAST = ' || l_recipient_tab.LAST);
2347
2348 IF l_recipient_tab.count <> 0 THEN
2349
2350 FOR i IN l_recipient_tab.FIRST .. l_recipient_tab.LAST LOOP
2351
2352 -- debug('In Notification Loop');
2353 --debug('In LOOP, i =' || i);
2354
2355 IF (l_recipient_tab(i) <> l_last_recipient AND l_last_recipient <> TEXT_DUMMY) THEN
2356
2357 SELECT PA_PRM_WF_ITEM_KEY_S.nextval
2358 INTO l_item_key
2359 FROM DUAL;
2360
2361 debug('l_item_key(inside loop) = ' || l_item_key);
2362
2363
2364 -- Create the WF process
2365 WF_ENGINE.CreateProcess ( ItemType => l_item_type,
2366 ItemKey => l_item_key,
2367 process => 'PRC_PA_OM_UPDATE_PROJECTS');
2368
2369
2370 -- Setting the attribute value for recipient
2371 WF_ENGINE.SetItemAttrText
2372 ( itemtype => l_item_type,
2373 itemkey => l_item_key,
2374 aname => 'ATTR_NTF_RECIPIENT',
2375 avalue => l_recipient_tab(i-1)
2376 );
2377
2378 debug('Inside Loop, recipient = '|| l_recipient_tab(i-1));
2379 --Get the number of projects updated for this recipient
2380
2381 SELECT distinct object_id1
2382 BULK COLLECT INTO l_project_count_tab
2383 FROM pa_wf_ntf_performers
2384 WHERE group_id = l_group_id
2385 AND user_name = l_recipient_tab(i-1);
2386
2387 -- Setting the attribute value for recipient count
2388 WF_ENGINE.SetItemAttrNumber
2389 ( itemtype => l_item_type,
2390 itemkey => l_item_key,
2391 aname => 'ATTR_UPD_PROJ_COUNT',
2392 avalue => l_project_count_tab.count
2393 );
2394
2395 debug('Inside Loop, count of updated projects = ' || l_project_count_tab.count);
2396 l_view_upd_proj_url :=
2397 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275&akRegionCode=PA_UPDATED_PIPE_PROJ_LAYOUT&addBreadCrumb=RP'
2398 || '&paGroupId=' || l_group_id
2399 || '&paItemType=PAYPRJNT';
2400
2401 -- Setting the attribute value for updated projects URL
2402 WF_ENGINE.SetItemAttrText
2403 ( itemtype => l_item_type,
2404 itemkey => l_item_key,
2405 aname => 'ATTR_UPD_PROJ_URL_INFO',
2406 avalue => l_view_upd_proj_url
2407 );
2408
2409 -- Now start the WF process
2410 WF_ENGINE.StartProcess
2411 ( itemtype => l_item_type,
2412 itemkey => l_item_key );
2413
2414 -- Insert to PA tables wf process information.
2415
2416 PA_WORKFLOW_UTILS.Insert_WF_Processes
2417 (p_wf_type_code => 'OM_UPDATE_PROJECTS'
2418 ,p_item_type => l_item_type
2419 ,p_item_key => l_item_key
2420 ,p_entity_key1 => to_char(l_project_id_tab(i-1))
2421 ,p_entity_key2 => to_char(l_lead_id_tab(i-1))
2422 ,p_description => NULL
2423 ,p_err_code => l_err_code
2424 ,p_err_stage => l_err_stage
2425 ,p_err_stack => l_err_stack
2426 );
2427
2428 END IF;
2429 --asign the current recipient to the last recipient
2430 l_last_recipient := l_recipient_tab(i);
2431
2432 END LOOP;
2433 END IF;
2434
2435 debug('Exit Notification Loop');
2436
2437 IF l_recipient_tab.count <> 0 THEN
2438 -- Sent notification for the last recipient.
2439 SELECT PA_PRM_WF_ITEM_KEY_S.nextval
2440 INTO l_item_key
2441 FROM DUAL;
2442 debug('l_item_key(outside loop) = ' || l_item_key);
2443
2444 -- Create the WF process
2445 WF_ENGINE.CreateProcess ( ItemType => l_item_type,
2446 ItemKey => l_item_key,
2447 process => 'PRC_PA_OM_UPDATE_PROJECTS');
2448
2449
2450 -- Setting the attribute value for recipient
2451
2452 l_end_recipient := l_recipient_tab(l_recipient_tab.LAST);
2453
2454 WF_ENGINE.SetItemAttrText
2455 ( itemtype => l_item_type,
2456 itemkey => l_item_key,
2457 aname => 'ATTR_NTF_RECIPIENT',
2458 avalue => l_end_recipient
2459 );
2460
2461 debug('Outside Loop, recipient =' || l_recipient_tab(l_recipient_tab.LAST));
2462
2463 --Get the number of projects updated for the last recipient
2464
2465 SELECT distinct object_id1
2466 BULK COLLECT INTO l_project_count_tab
2467 FROM pa_wf_ntf_performers
2468 WHERE group_id = l_group_id
2469 AND user_name = l_end_recipient;
2470
2471 -- Setting the attribute value for recipient count
2472 WF_ENGINE.SetItemAttrNumber
2473 ( itemtype => l_item_type,
2474 itemkey => l_item_key,
2475 aname => 'ATTR_UPD_PROJ_COUNT',
2476 avalue => l_project_count_tab.COUNT
2477 );
2478
2479 debug('Outside Loop, count of updated projects = ' ||l_project_count_tab.COUNT);
2480 l_view_upd_proj_url :=
2481 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275&akRegionCode=PA_UPDATED_PIPE_PROJ_LAYOUT&addBreadCrumb=RP'
2482 || '&paGroupId=' || l_group_id
2483 || '&paItemType=PAYPRJNT';
2484
2485 -- Setting the attribute value for updated projects URL
2486 WF_ENGINE.SetItemAttrText
2487 ( itemtype => l_item_type,
2488 itemkey => l_item_key,
2489 aname => 'ATTR_UPD_PROJ_URL_INFO',
2490 avalue => l_view_upd_proj_url
2491 );
2492
2493 -- Now start the WF process
2494 WF_ENGINE.StartProcess
2495 ( itemtype => l_item_type,
2496 itemkey => l_item_key );
2497
2498 -- Insert to PA tables wf process information.
2499
2500 PA_WORKFLOW_UTILS.Insert_WF_Processes
2501 (p_wf_type_code => 'OM_UPDATE_PROJECTS'
2502 ,p_item_type => l_item_type
2503 ,p_item_key => l_item_key
2504 ,p_entity_key1 => to_char(l_project_id_tab.LAST)
2505 ,p_entity_key2 => to_char(l_lead_id_tab.LAST)
2506 ,p_description => NULL
2507 ,p_err_code => l_err_code
2508 ,p_err_stage => l_err_stage
2509 ,p_err_stack => l_err_stack
2510 );
2511
2512 END IF;
2513
2514 debug('End Procedure Update_Projects');
2515
2516 EXCEPTION
2517 WHEN OTHERS THEN
2518 x_msg_count := 1;
2519 x_msg_data := sqlerrm;
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECT_REQUEST_PVT',
2522 p_procedure_name => 'update_projects');
2523 RAISE;
2524
2525 END update_projects;
2526
2527
2528 --Procedure: post_create_project
2529 --Purpose: This procedure is to build the two ways relationship
2530 -- between the project request and the project created.
2531 -- And close the project request after the project is created.
2532
2533
2534 PROCEDURE post_create_project(p_request_id IN pa_project_requests.request_id%TYPE,
2535 p_project_id IN pa_projects_all.project_id%TYPE,
2536 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2537 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2538 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2539
2540 l_new_obj_rel_id PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
2541 l_new_obj_rel_id2 PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
2542
2543 BEGIN
2544 x_return_status := FND_API.G_RET_STS_SUCCESS;
2545
2546 -- Form the relationship: from the project request to the created project.
2547
2548 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
2549 p_user_id => FND_GLOBAL.USER_ID
2550 ,p_object_type_from => 'PA_PROJECT_REQUESTS'
2551 ,p_object_id_from1 => p_request_id
2552 ,p_object_id_from2 => NULL
2553 ,p_object_id_from3 => NULL
2554 ,p_object_id_from4 => NULL
2555 ,p_object_id_from5 => NULL
2556 ,p_object_type_to => 'PA_PROJECTS'
2557 ,p_object_id_to1 => p_project_id
2558 ,p_object_id_to2 => NULL
2559 ,p_object_id_to3 => NULL
2560 ,p_object_id_to4 => NULL
2561 ,p_object_id_to5 => NULL
2562 ,p_relationship_type => 'A'
2563 ,p_relationship_subtype => 'PROJECT_REQUEST'
2564 ,p_lag_day => NULL
2565 ,p_imported_lag => NULL
2566 ,p_priority => NULL
2567 ,p_pm_product_code => NULL
2568 ,x_object_relationship_id => l_new_obj_rel_id
2569 ,x_return_status => x_return_status
2570 );
2571
2572 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2573 -- Form the relationship: from the created project to the source request.
2574
2575 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
2576 p_user_id => FND_GLOBAL.USER_ID
2577 ,p_object_type_from => 'PA_PROJECTS'
2578 ,p_object_id_from1 => p_project_id
2579 ,p_object_id_from2 => NULL
2580 ,p_object_id_from3 => NULL
2581 ,p_object_id_from4 => NULL
2582 ,p_object_id_from5 => NULL
2583 ,p_object_type_to => 'PA_PROJECT_REQUESTS'
2584 ,p_object_id_to1 => p_request_id
2585 ,p_object_id_to2 => NULL
2586 ,p_object_id_to3 => NULL
2587 ,p_object_id_to4 => NULL
2588 ,p_object_id_to5 => NULL
2589 ,p_relationship_type => 'A'
2590 ,p_relationship_subtype => 'PROJECT_REQUEST'
2591 ,p_lag_day => NULL
2592 ,p_imported_lag => NULL
2593 ,p_priority => NULL
2594 ,p_pm_product_code => NULL
2595 ,x_object_relationship_id => l_new_obj_rel_id2
2596 ,x_return_status => x_return_status
2597 );
2598 END IF;
2599
2600 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2601
2602 -- Close the project request.
2603 close_project_request
2604 (p_request_id ,
2605 x_return_status ,
2606 x_msg_count ,
2607 x_msg_data );
2608 END IF;
2609
2610 EXCEPTION
2611 WHEN OTHERS THEN
2612 x_msg_count := 1;
2613 x_msg_data := sqlerrm;
2614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2615 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECT_REQUEST_PVT',
2616 p_procedure_name => 'post_create_project');
2617 RAISE;
2618 END post_create_project;
2619
2620
2621 PROCEDURE Req_Name_Duplicate(p_request_name IN VARCHAR2,
2622 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2623 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2624 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2625
2626 CURSOR C1 IS
2627 SELECT 'N'
2628 FROM pa_project_requests
2629 WHERE UPPER(REQUEST_NAME) = UPPER(P_REQUEST_NAME);
2630 l_duplicate VARCHAR2(1);
2631 l_msg_index_out NUMBER;
2632 dup_req_name_not_allowed EXCEPTION;
2633 -- added for 4537865
2634 l_new_msg_data VARCHAR2(2000);
2635 -- added for 4537865
2636
2637 BEGIN
2638 x_return_status := FND_API.G_RET_STS_SUCCESS;
2639
2640 OPEN C1;
2641 FETCH C1
2642 INTO l_duplicate;
2643 IF C1%FOUND THEN
2644 debug('PA_PROJECT_REQUEST_PVT.req_name_duplicate: found duplicate name');
2645 CLOSE C1;
2646 RAISE dup_req_name_not_allowed;
2647 END IF;
2648 CLOSE C1;
2649
2650 EXCEPTION
2651
2652 WHEN dup_req_name_not_allowed OR
2653 DUP_VAL_ON_INDEX THEN
2654 PA_UTILS.add_message(p_app_short_name => 'PA',
2655 p_msg_name => 'PA_ALL_DUPLICATE_NAME');
2656 x_return_status := FND_API.G_RET_STS_ERROR;
2657 x_msg_count := FND_MSG_PUB.Count_Msg;
2658 x_msg_data := ' PA_ALL_DUPLICATE_NAME';
2659
2660 IF x_msg_count = 1 THEN
2661 pa_interface_utils_pub.get_messages
2662 (p_encoded => FND_API.G_TRUE,
2663 p_msg_index => 1,
2664 p_msg_count => x_msg_count,
2665 p_msg_data => x_msg_data,
2666 --p_data => x_msg_data, * Commented for Bug: 4537865
2667 p_data => l_new_msg_data, -- added for 4537865
2668 p_msg_index_out => l_msg_index_out );
2669 -- added for 4537865
2670 x_msg_data := l_new_msg_data;
2671 -- added for 4537865
2672 END IF;
2673 WHEN OTHERS THEN
2674
2675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2676 x_msg_count := FND_MSG_PUB.Count_Msg;
2677 x_msg_data := substr(SQLERRM,1,240);
2678
2679 -- Set the excetption Message and the stack
2680 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_REQUEST_PVT.req_name_duplicate'
2681 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2682 IF x_msg_count = 1 THEN
2683 pa_interface_utils_pub.get_messages
2684 (p_encoded => FND_API.G_TRUE,
2685 p_msg_index => 1,
2686 p_msg_count => x_msg_count,
2687 p_msg_data => x_msg_data,
2688 --p_data => x_msg_data, * Commented for Bug: 4537865
2689 p_data => l_new_msg_data, -- added for 4537865
2690 p_msg_index_out => l_msg_index_out );
2691 -- added for 4537865
2692 x_msg_data := l_new_msg_data;
2693 -- added for 4537865
2694 END IF;
2695
2696 RAISE; -- This is optional depending on the needs
2697 END Req_Name_Duplicate;
2698
2699 END PA_PROJECT_REQUEST_PVT;