[Home] [Help]
PACKAGE BODY: APPS.PA_OPPORTUNITY_MGT_PVT
Source
1 PACKAGE BODY PA_OPPORTUNITY_MGT_PVT as
2 /* $Header: PAYOPVTB.pls 120.6 2006/05/18 05:56:42 vkadimes noship $ */
3
4 --
5 -- Procedure : debug
6 -- Purpose :
7 --
8 --
9 PROCEDURE debug(p_msg IN VARCHAR2) IS
10 BEGIN
11 --dbms_output.put_line('pa_opportunity_mgt_pvt'|| ' : ' || p_msg);
12
13 PA_DEBUG.WRITE(
14 x_module => 'pa.plsql.pa_opportunity_mgt_pvt',
15 x_msg => p_msg,
16 x_log_level => 3);
17 pa_debug.write_file('LOG', p_msg);
18 END debug;
19
20 --
21 -- Procedure : modify_project_attributes
22 -- Purpose :
23 --
24 --
25 PROCEDURE modify_project_attributes
26 ( p_project_id IN NUMBER ,
27 p_opportunity_value IN NUMBER ,
28 p_opp_value_currency_code IN VARCHAR2 ,
29 p_expected_approval_date IN DATE ,
30 p_update_project IN VARCHAR2 := 'N',
31 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
32 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
33 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
34 IS
35 CURSOR c1 IS
36 SELECT projfunc_currency_code, project_currency_code, org_id, probability_member_id
37 FROM pa_projects_all -- Bug#3807805 Modified pa_projects to pa_projects_all
38 WHERE project_id = p_project_id;
39
40 v_c1 c1%ROWTYPE;
41 l_opp_value_currency_code FND_CURRENCIES_VL.currency_code%TYPE;
42 l_conversion_date DATE;
43 l_date DATE;
44 l_default_rate_type VARCHAR2(30);
45 l_type VARCHAR2(30);
46 l_projfunc_opp_value NUMBER;
47 l_project_opp_value NUMBER;
48 l_status VARCHAR2(80);
49 l_dummy_number NUMBER;
50 --Bug: 4537865
51 l_new_msg_data VARCHAR2(2000);
52 l_tmp_number NUMBER;
53 l_new1_dummy_number NUMBER;
54 l_new2_dummy_number NUMBER;
55 --Bug: 4537865
56 l_msg_index_out NUMBER;
57 --Bug:4469336 Added for avoiding blind calls to pa_debug.write and pa_debug.write_file
58 l_debug_mode VARCHAR2(10);
59 --Bug:4469336
60
61 l_can_not_convert_currency EXCEPTION;
62 l_prob_exp_date_valid_error EXCEPTION;
63
64 BEGIN
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 --Bug:4469336 Added for avoiding blind calls to pa_debug.write and pa_debug.write_file
68 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
69 --Bug:4469336
70
71 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
72 IF l_debug_mode = 'Y' then
73
74 debug('p_project_id = '||p_project_id);
75 debug('p_opportunity_value = ' || p_opportunity_value);
76 debug('p_opp_value_currency_code = '|| p_opp_value_currency_code );
77 debug('p_expected_approval_date = '||p_expected_approval_date);
78 END IF;
79
80 OPEN c1;
81 FETCH c1 INTO v_c1;
82 CLOSE c1;
83
84 -- 2449770: Added cross field validation between probability and expected
85 -- approval date when creating projects.
86 IF p_update_project = 'N' AND
87 ((v_c1.probability_member_id IS NULL AND p_expected_approval_date IS NOT NULL) OR
88 (v_c1.probability_member_id IS NOT NULL AND p_expected_approval_date IS NULL)) AND
89 is_proj_opp_associated(p_project_id)= 'Y' THEN /* Added for bug 3330438 */
90 RAISE l_prob_exp_date_valid_error;
91 END IF;
92
93 -- 2331568: Added code below because check_currency API
94 -- fails if currency is null. Therefore, we bypass the call in the
95 -- case that currency is null.
96 IF p_opportunity_value IS NULL THEN
97
98 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
99
100 IF l_debug_mode = 'Y' then
101 debug('p_opportunity_value is null');
102 END IF;
103
104 l_projfunc_opp_value := NULL;
105 PA_PROJ_OPP_ATTRS_PKG.update_row (p_project_id => p_project_id,
106 p_opportunity_value => NULL,
107 p_opp_value_currency_code => NULL,
108 p_projfunc_opp_value => NULL,
109 p_projfunc_opp_rate_type => NULL,
110 p_projfunc_opp_rate_date => NULL,
111 p_project_opp_value => NULL,
112 p_project_opp_rate_type => NULL,
113 p_project_opp_rate_date => NULL,
114 x_return_status => x_return_status,
115 x_msg_count => x_msg_count,
116 x_msg_data => x_msg_data);
117 ELSIF p_opportunity_value = 0 THEN
118
119 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
120 IF l_debug_mode = 'Y' THEN
121 debug('p_opportunity_value = 0');
122 END IF;
123
124 l_projfunc_opp_value := 0;
125 PA_PROJ_OPP_ATTRS_PKG.update_row (p_project_id => p_project_id,
126 p_opportunity_value => 0,
127 p_opp_value_currency_code => p_opp_value_currency_code, --changed from NULL to p_opp_value_currency_code for BUg 4129683
128 p_projfunc_opp_value => 0,
129 p_projfunc_opp_rate_type => NULL,
130 p_projfunc_opp_rate_date => NULL,
131 p_project_opp_value => 0,
132 p_project_opp_rate_type => NULL,
133 p_project_opp_rate_date => NULL,
134 x_return_status => x_return_status,
135 x_msg_count => x_msg_count,
136 x_msg_data => x_msg_data);
137 ELSE
138
139 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
140 PA_OPPORTUNITY_MGT_PVT.validate_value_fields(p_opportunity_value => p_opportunity_value,
141 p_opp_value_currency_code => p_opp_value_currency_code,
142 p_projfunc_currency_code => v_c1.projfunc_currency_code,
143 p_project_currency_code => v_c1.project_currency_code,
144 x_return_status => x_return_status,
145 x_msg_count => x_msg_count,
146 x_msg_data => x_msg_data);
147
148 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
149 IF l_debug_mode = 'Y' THEN
150 debug('After validate_value_fields: x_return_status = ' || x_return_status);
151 END IF;
152
153 END IF;
154
155 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
156 PA_PROJECTS_MAINT_UTILS.check_currency_name_or_code (
157 p_agreement_currency => p_opp_value_currency_code,
158 p_agreement_currency_name => NULL,
159 p_check_id_flag => 'Y',
160 x_agreement_currency => l_opp_value_currency_code,
161 x_return_status => x_return_status,
162 x_error_msg_code => x_msg_data);
163
164 -- This if condition is Added for Bug 5214782
165 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
166 PA_UTILS.ADD_MESSAGE('PA', x_msg_data);
167 END IF ;
168 -- End of 5214782
169 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
170 IF l_debug_mode = 'Y' THEN
171 debug('l_opp_value_currency_code = ' || l_opp_value_currency_code);
172 debug('After check_currency_name_or_code: x_return_status = ' || x_return_status);
173 END IF;
174
175 END IF;
176
177 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
178 IF l_opp_value_currency_code = v_c1.projfunc_currency_code AND l_opp_value_currency_code = v_c1.project_currency_code THEN
179
180 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
181 IF l_debug_mode = 'Y' THEN
182 debug('SAME currency codes');
183 END IF;
184
185 l_projfunc_opp_value := p_opportunity_value;
186 PA_PROJ_OPP_ATTRS_PKG.update_row (p_project_id => p_project_id,
187 p_opportunity_value => p_opportunity_value,
188 p_opp_value_currency_code => l_opp_value_currency_code,
189 p_projfunc_opp_value => p_opportunity_value,
190 p_projfunc_opp_rate_type => NULL,
191 p_projfunc_opp_rate_date => NULL,
192 p_project_opp_value => p_opportunity_value,
193 p_project_opp_rate_type => NULL,
194 p_project_opp_rate_date => NULL,
195 x_return_status => x_return_status,
196 x_msg_count => x_msg_count,
197 x_msg_data => x_msg_data);
198 ELSIF PA_OPPORTUNITY_MGT_PVT.is_opp_multi_currency_setup = 'Y' THEN
199
200 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
201
202 IF l_debug_mode = 'Y' THEN
203 debug('is_opp_multi_currency_setup = Y');
204 debug('Need currency conversion');
205 debug('opp_value_currency_code = '|| l_opp_value_currency_code);
206 debug('projfunc_currency_code = '|| v_c1.projfunc_currency_code);
207 debug('project_currency_code = '|| v_c1.project_currency_code);
208 END IF;
209
210 -- Get default rate type.
211 PA_OPPORTUNITY_MGT_PVT.get_opp_multi_currency_setup(
212 x_default_rate_type => l_default_rate_type,
213 x_return_status => x_return_status,
214 x_msg_count => x_msg_count,
215 x_msg_data => x_msg_data);
216
217 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
218
219 IF l_debug_mode = 'Y' THEN
220 debug('l_default_rate_type = '|| l_default_rate_type);
221 END IF;
222
223 -- Get conversion date.
224 l_conversion_date := PA_UTILS2.get_pa_date(
225 p_ei_date => p_expected_approval_date,
226 p_gl_date => sysdate,
227 p_org_id => v_c1.org_id);
228
229 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
230
231 IF l_debug_mode = 'Y' THEN
232 debug('l_conversion_date = '|| l_conversion_date);
233 END IF;
234
235 -- 2410298: p_conversion_date and p_conversion_type are IN/OUT params.
236 l_date := l_conversion_date;
237 l_type := l_default_rate_type;
238 -- Convert p_opportunity_value to projfunc_currency_code.
239 -- Bug: 4537865
240 l_tmp_number := l_dummy_number ;
241 l_new1_dummy_number := l_tmp_number;
242 l_new2_dummy_number := l_tmp_number;
243 -- Bug: 4537865
244 PA_MULTI_CURRENCY.convert_amount(
245 p_from_currency => l_opp_value_currency_code,
246 p_to_currency => v_c1.projfunc_currency_code,
247 p_conversion_date => l_date,
248 p_conversion_type => l_type,
249 p_amount => p_opportunity_value,
250 p_user_validate_flag => 'Y',
251 p_handle_exception_flag => 'Y',
252 p_converted_amount => l_projfunc_opp_value,
253 p_denominator => l_dummy_number,
254 --p_numerator => l_dummy_number, Bug: 4537865
255 p_numerator => l_new1_dummy_number, --Bug: 4537865
256 --p_rate => l_dummy_number, Bug: 4537865
257 p_rate => l_new2_dummy_number, --Bug: 4537865
258 x_status => l_status);
259
260 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
261
262 IF l_debug_mode = 'Y' THEN
263 debug('l_status = '|| l_status);
264 END IF;
265
266 IF l_status IS NOT NULL AND l_status <> 'N' THEN
267
268 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
269
270 IF l_debug_mode = 'Y' THEN
271 debug('Raise l_can_not_convert_currency :1');
272 END IF;
273
274 PA_UTILS.add_message('PA', l_status);
275 RAISE l_can_not_convert_currency;
276 END IF;
277
278 -- Convert p_opportunity_value to project_currency_code.
279 l_date := l_conversion_date;
280 l_type := l_default_rate_type;
281 -- Bug: 4537865
282 l_new1_dummy_number := l_tmp_number;
283 l_new2_dummy_number := l_tmp_number;
284 -- Bug: 4537865
285
286 PA_MULTI_CURRENCY.convert_amount(
287 p_from_currency => l_opp_value_currency_code,
288 p_to_currency => v_c1.project_currency_code,
289 p_conversion_date => l_date,
290 p_conversion_type => l_type,
291 p_amount => p_opportunity_value,
292 p_user_validate_flag => 'Y',
293 p_handle_exception_flag => 'Y',
294 p_converted_amount => l_project_opp_value,
295 p_denominator => l_dummy_number,
296 --p_numerator => l_dummy_number, Bug: 4537865
297 p_numerator => l_new1_dummy_number, --Bug: 4537865
298 --p_rate => l_dummy_number, Bug: 4537865
299 p_rate => l_new2_dummy_number, --Bug: 4537865
300 x_status => l_status);
301
302 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
303
304 IF l_debug_mode = 'Y' THEN
305 debug('l_project_opp_value = '|| l_project_opp_value);
306 debug('l_status = '|| l_status);
307 END IF;
308
309 IF l_status IS NOT NULL AND l_status <> 'N' THEN
310
311 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
312 IF l_debug_mode = 'Y' THEN
313 debug('Raise l_can_not_convert_currency :2');
314 END IF;
315
316 PA_UTILS.add_message('PA', l_status);
317 RAISE l_can_not_convert_currency;
318 END IF;
319
320 PA_PROJ_OPP_ATTRS_PKG.update_row (p_project_id => p_project_id,
321 p_opportunity_value => p_opportunity_value,
322 p_opp_value_currency_code => l_opp_value_currency_code,
323 p_projfunc_opp_value => l_projfunc_opp_value,
324 p_projfunc_opp_rate_type => l_default_rate_type,
325 p_projfunc_opp_rate_date => l_conversion_date,
326 p_project_opp_value => l_project_opp_value,
327 p_project_opp_rate_type => l_default_rate_type,
328 p_project_opp_rate_date => l_conversion_date,
329 x_return_status => x_return_status,
330 x_msg_count => x_msg_count,
331 x_msg_data => x_msg_data);
332
333 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
334 IF l_debug_mode = 'Y' THEN
335 debug('After update_row');
336 END IF;
337 END IF;
338 END IF;
339 END IF;
340
341 EXCEPTION
342 WHEN l_can_not_convert_currency THEN
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 x_msg_data := 'PA_CAN_NOT_CONVERT_CURRENCY';
345 x_msg_count := FND_MSG_PUB.Count_Msg;
346 IF x_msg_count = 1 THEN
347 pa_interface_utils_pub.get_messages
348 (p_encoded => FND_API.G_TRUE,
349 p_msg_index => 1,
350 p_msg_count => x_msg_count,
351 p_msg_data => x_msg_data,
352 --p_data => x_msg_data, Bug: 4537865
353 p_data => l_new_msg_data, --Bug: 4537865
354 p_msg_index_out => l_msg_index_out );
355 --Bug: 4537865
356 x_msg_data := l_new_msg_data;
357 --Bug: 4537865
358 End IF;
359 WHEN l_prob_exp_date_valid_error THEN
360 PA_UTILS.add_message('PA', 'PA_PROB_EXP_DATE_VALID_ERROR');
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 x_msg_data := 'PA_OPP_MULTI_CURRENCY_ERROR';
363 x_msg_count := FND_MSG_PUB.Count_Msg;
364 IF x_msg_count = 1 THEN
365 pa_interface_utils_pub.get_messages
366 (p_encoded => FND_API.G_TRUE,
367 p_msg_index => 1,
368 p_msg_count => x_msg_count,
369 p_msg_data => x_msg_data,
370 --p_data => x_msg_data, * Bug: 4537865
371 p_data => l_new_msg_data, --Bug: 4537865
372 p_msg_index_out => l_msg_index_out );
373 --Bug: 4537865
374 x_msg_data := l_new_msg_data;
375 --Bug: 4537865
376 End IF;
377 WHEN OTHERS THEN
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
380 p_procedure_name => 'modify_project_attributes');
381 raise;
382
383 END modify_project_attributes;
384
385
386 --
387 -- Procedure : copy_project_attributes
388 -- Purpose :
389 --
390 --
391 PROCEDURE copy_project_attributes
392 ( p_source_project_id IN NUMBER ,
393 p_dest_project_id IN NUMBER ,
394 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
395 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
396 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
397 IS
398 CURSOR c1 IS
399 SELECT opportunity_value, opp_value_currency_code, projfunc_opp_value, projfunc_opp_rate_type, projfunc_opp_rate_date, project_opp_value, project_opp_rate_type, project_opp_rate_date
400 FROM pa_project_opp_attrs
401 WHERE project_id = p_source_project_id;
402
403 v_c1 c1%ROWTYPE;
404
405
406 BEGIN
407 x_return_status := FND_API.G_RET_STS_SUCCESS;
408
409
410 OPEN c1;
411 FETCH c1 INTO v_c1;
412 CLOSE c1;
413
414 PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_dest_project_id,
415 p_opportunity_value => v_c1.opportunity_value,
416 p_opp_value_currency_code => v_c1.opp_value_currency_code,
417 p_projfunc_opp_value => v_c1.projfunc_opp_value,
418 p_projfunc_opp_rate_type => v_c1.projfunc_opp_rate_type,
419 p_projfunc_opp_rate_date => v_c1.projfunc_opp_rate_date,
420 p_project_opp_value => v_c1.project_opp_value,
421 p_project_opp_rate_type => v_c1.project_opp_rate_type,
422 p_project_opp_rate_date => v_c1.project_opp_rate_date,
423 x_return_status => x_return_status,
424 x_msg_count => x_msg_count,
425 x_msg_data => x_msg_data);
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
430 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
431 p_procedure_name => 'copy_project_attributes');
432 raise;
433
434 END copy_project_attributes;
435
436
437 --
438 -- Procedure : create_project_attributes
439 -- Purpose :
440 --
441 --
442 PROCEDURE create_project_attributes
443 ( p_project_id IN NUMBER ,
444 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
445 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
446 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
447 IS
448 CURSOR c1 IS
449 SELECT project_value, projfunc_currency_code, project_currency_code, expected_approval_date, org_id
450 FROM pa_projects
451 WHERE project_id = p_project_id;
452
453 v_c1 c1%ROWTYPE;
454
455 l_default_rate_type VARCHAR2(30);
456 l_conversion_date DATE;
457 l_converted_amount NUMBER;
458 l_status VARCHAR2(80);
459 l_dummy_number NUMBER;
460 --Bug: 4537865
461 l_tmp_number NUMBER;
462 l_new1_dummy_number NUMBER;
463 l_new2_dummy_number NUMBER;
464 l_new_msg_data VARCHAR2(2000);
465 -- Bug: 4537865
466 l_msg_index_out NUMBER;
467
468 l_can_not_convert_currency EXCEPTION;
469
470 --Bug:4469336 Added for avoiding blind calls to pa_debug.write and pa_debug.write_file
471 l_debug_mode VARCHAR2(10);
472 --Bug:4469336
473
474 BEGIN
475 x_return_status := FND_API.G_RET_STS_SUCCESS;
476
477 --Bug:4469336 Added for avoiding blind calls to pa_debug.write and pa_debug.write_file
478 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
479 --Bug:4469336
480
481 OPEN c1;
482 FETCH c1 INTO v_c1;
483 CLOSE c1;
484
485 IF v_c1.project_value IS NULL THEN
486 PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_project_id,
487 p_opportunity_value => NULL,
488 p_opp_value_currency_code => NULL,
489 p_projfunc_opp_value => NULL,
490 p_projfunc_opp_rate_type => NULL,
491 p_projfunc_opp_rate_date => NULL,
492 p_project_opp_value => NULL,
493 p_project_opp_rate_type => NULL,
494 p_project_opp_rate_date => NULL,
495 x_return_status => x_return_status,
496 x_msg_count => x_msg_count,
497 x_msg_data => x_msg_data);
498 ELSIF v_c1.projfunc_currency_code = v_c1.project_currency_code THEN
499 PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_project_id,
500 p_opportunity_value => v_c1.project_value,
501 p_opp_value_currency_code => v_c1.projfunc_currency_code,
502 p_projfunc_opp_value => v_c1.project_value,
503 p_projfunc_opp_rate_type => NULL,
504 p_projfunc_opp_rate_date => NULL,
505 p_project_opp_value => v_c1.project_value,
506 p_project_opp_rate_type => NULL,
507 p_project_opp_rate_date => NULL,
508 x_return_status => x_return_status,
509 x_msg_count => x_msg_count,
510 x_msg_data => x_msg_data);
511 ELSIF PA_OPPORTUNITY_MGT_PVT.is_opp_multi_currency_setup = 'Y' THEN
512 -- Get default rate type.
513 PA_OPPORTUNITY_MGT_PVT.get_opp_multi_currency_setup(
514 x_default_rate_type => l_default_rate_type,
515 x_return_status => x_return_status,
516 x_msg_count => x_msg_count,
517 x_msg_data => x_msg_data);
518 l_conversion_date := PA_UTILS2.get_pa_date(
519 p_ei_date => v_c1.expected_approval_date,
520 p_gl_date => sysdate,
521 p_org_id => v_c1.org_id);
522 -- Convert pa_projects_all.project_value to prject_currency_code and
523 -- store it in pa_project_opp_attrs.project_opp_value.
524 -- Bug: 4537865
525
526 l_tmp_number := l_dummy_number ;
527 l_new1_dummy_number := l_tmp_number;
528 l_new2_dummy_number := l_tmp_number;
529 -- Bug: 4537865
530 PA_MULTI_CURRENCY.convert_amount(
531 p_from_currency => v_c1.projfunc_currency_code,
532 p_to_currency => v_c1.project_currency_code,
533 p_conversion_date => l_conversion_date,
534 p_conversion_type => l_default_rate_type,
535 p_amount => v_c1.project_value,
536 p_user_validate_flag => 'Y',
537 p_handle_exception_flag => 'Y',
538 p_converted_amount => l_converted_amount,
539 p_denominator => l_dummy_number,
540 --p_numerator => l_dummy_number, Bug: 4537865
541 p_numerator => l_new1_dummy_number, --Bug: 4537865
542 --p_rate => l_dummy_number, Bug: 4537865
543 p_rate => l_new2_dummy_number, --Bug: 4537865
544 x_status => l_status);
545
546 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
547 IF l_debug_mode = 'Y' THEN
548 debug('l_converted_amount = ' || l_converted_amount);
549 debug('l_status = '|| l_status);
550 END IF;
551
552 IF l_status IS NOT NULL AND l_status <> 'N' THEN
553 PA_UTILS.add_message('PA', l_status);
554 RAISE l_can_not_convert_currency;
555 END IF;
556
557 PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_project_id,
558 p_opportunity_value => v_c1.project_value,
559 p_opp_value_currency_code => v_c1.projfunc_currency_code,
560 p_projfunc_opp_value => v_c1.project_value,
561 p_projfunc_opp_rate_type => NULL,
562 p_projfunc_opp_rate_date => NULL,
563 p_project_opp_value => l_converted_amount,
564 p_project_opp_rate_type => l_default_rate_type,
565 p_project_opp_rate_date => l_conversion_date,
566 x_return_status => x_return_status,
567 x_msg_count => x_msg_count,
568 x_msg_data => x_msg_data);
569 ELSE
570 PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_project_id,
571 p_opportunity_value => v_c1.project_value,
572 p_opp_value_currency_code => v_c1.projfunc_currency_code,
573 p_projfunc_opp_value => v_c1.project_value,
574 p_projfunc_opp_rate_type => NULL,
575 p_projfunc_opp_rate_date => NULL,
576 p_project_opp_value => NULL,
577 p_project_opp_rate_type => NULL,
578 p_project_opp_rate_date => NULL,
579 x_return_status => x_return_status,
580 x_msg_count => x_msg_count,
581 x_msg_data => x_msg_data);
582 END IF;
583
584 EXCEPTION
585 WHEN l_can_not_convert_currency THEN
586 x_return_status := FND_API.G_RET_STS_ERROR;
587 x_msg_data := 'PA_CAN_NOT_CONVERT_CURRENCY';
588 x_msg_count := FND_MSG_PUB.Count_Msg;
589 IF x_msg_count = 1 THEN
590 pa_interface_utils_pub.get_messages
591 (p_encoded => FND_API.G_TRUE,
592 p_msg_index => 1,
593 p_msg_count => x_msg_count,
594 p_msg_data => x_msg_data,
595 --p_data => x_msg_data, * Bug: 4537865
596 p_data => l_new_msg_data, --Bug: 4537865
597 p_msg_index_out => l_msg_index_out );
598 --Bug: 4537865
599 x_msg_data := l_new_msg_data;
600 --Bug: 4537865
601 End IF;
602 WHEN OTHERS THEN
603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
605 p_procedure_name => 'create_project_attributes');
606 raise;
607
608 END create_project_attributes;
609
610
611 --
612 -- Procedure : delete_project_attributes
613 -- Purpose :
614 --
615 --
616 PROCEDURE delete_project_attributes
617 ( p_project_id IN NUMBER ,
618 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
619 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
620 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
621
622 IS
623 CURSOR c1 IS
624 SELECT object_id_from1 request_id
625 FROM pa_object_relationships
626 WHERE relationship_type = 'A'
627 AND object_type_from = 'PA_PROJECT_REQUESTS'
628 AND object_type_to = 'PA_PROJECTS'
629 AND object_id_to1 = p_project_id;
630
631 l_request_id NUMBER;
632
633 CURSOR c2 IS
634 SELECT object_relationship_id, record_version_number
635 FROM pa_object_relationships
636 WHERE ((object_type_from = 'PA_PROJECT_REQUESTS'
637 AND object_type_to = 'PA_PROJECTS'
638 AND object_id_from1 = l_request_id
639 AND object_id_to1 = p_project_id) OR
640 (object_type_from = 'PA_PROJECTS'
641 AND object_type_to = 'PA_PROJECT_REQUESTS'
642 AND object_id_from1 = p_project_id
643 AND object_id_to1 = l_request_id))
644 AND relationship_type = 'A'
645 AND relationship_subtype = 'PROJECT_REQUEST';
646
647 BEGIN
648 x_return_status := FND_API.G_RET_STS_SUCCESS;
649
650 -- Delete attributes from pa_proj_opportunity_attributes.
651 PA_PROJ_OPP_ATTRS_PKG.delete_row(p_project_id => p_project_id,
652 x_return_status => x_return_status,
653 x_msg_count => x_msg_count,
654 x_msg_data => x_msg_data);
655
656 -- If the project has a project request associated with it, then revert the status of the
657 -- project request to 'OPEN'.
658 -- Delete the object relationships between the project and the project request.
659 FOR v_c1 IN c1 LOOP
660 -- Change project request status.
661 PA_PROJECT_REQUEST_PKG.update_row(p_request_id => v_c1.request_id,
662 p_request_status_code => '121',
663 x_return_status => x_return_status,
664 x_msg_count => x_msg_count,
665 x_msg_data => x_msg_data);
666
667 -- Delete relationship.
668 l_request_id := v_c1.request_id;
669 FOR v_c2 IN c2 LOOP
670 PA_OBJECT_RELATIONSHIPS_PKG.delete_row (
671 p_object_relationship_id => v_c2.object_relationship_id,
672 p_object_type_from => NULL,
673 p_object_id_from1 => NULL,
674 p_object_id_from2 => NULL,
675 p_object_id_from3 => NULL,
676 p_object_id_from4 => NULL,
677 p_object_id_from5 => NULL,
678 p_object_type_to => NULL,
679 p_object_id_to1 => NULL,
680 p_object_id_to2 => NULL,
681 p_object_id_to3 => NULL,
682 p_object_id_to4 => NULL,
683 p_object_id_to5 => NULL,
684 p_record_version_number => v_c2.record_version_number,
685 p_pm_product_code => NULL,
686 x_return_status => x_return_status);
687 END LOOP;
688 END LOOP;
689
690 EXCEPTION
691 WHEN OTHERS THEN
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
694 p_procedure_name => 'delete_project_attributes');
695 raise;
696
697 END delete_project_attributes;
698
699
700 --
701 -- Procedure : validate_value_fields
702 -- Purpose :
703 --
704 --
705 PROCEDURE validate_value_fields
706 ( p_opportunity_value IN NUMBER := NULL,
707 p_opp_value_currency_code IN VARCHAR2 := NULL,
708 p_projfunc_currency_code IN VARCHAR2 ,
709 p_project_currency_code IN VARCHAR2 ,
710 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
711 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
712 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
713 IS
714 l_opp_value_currency_missing EXCEPTION;
715 l_opp_multi_currency_error EXCEPTION;
716 l_msg_index_out NUMBER;
717 --Bug: 4537865
718 l_new_msg_data VARCHAR2(2000);
719 --Bug: 4537865
720
721 BEGIN
722 x_return_status := FND_API.G_RET_STS_SUCCESS;
723
724 IF p_opportunity_value IS NULL THEN
725 RETURN;
726 ELSIF p_opp_value_currency_code IS NULL THEN
727 RAISE l_opp_value_currency_missing;
728 ELSIF p_opp_value_currency_code = p_projfunc_currency_code AND p_opp_value_currency_code = p_project_currency_code THEN
729 RETURN;
730 ELSIF PA_OPPORTUNITY_MGT_PVT.is_opp_multi_currency_setup = 'N' THEN
731 RAISE l_opp_multi_currency_error;
732 END IF;
733
734 EXCEPTION
735 WHEN l_opp_value_currency_missing THEN
736 PA_UTILS.add_message('PA', 'PA_OPP_VALUE_CURRENCY_MISSING');
737 x_return_status := FND_API.G_RET_STS_ERROR;
738 x_msg_data := 'PA_OPP_VALUE_CURRENCY_MISSING';
739 x_msg_count := FND_MSG_PUB.Count_Msg;
740 IF x_msg_count = 1 THEN
741 pa_interface_utils_pub.get_messages
742 (p_encoded => FND_API.G_TRUE,
743 p_msg_index => 1,
744 p_msg_count => x_msg_count,
745 p_msg_data => x_msg_data,
746 --p_data => x_msg_data, * Bug: 4537865
747 p_data => l_new_msg_data, --Bug: 4537865
748 p_msg_index_out => l_msg_index_out );
749 --Bug: 4537865
750 x_msg_data := l_new_msg_data;
751 --Bug: 4537865
752 End IF;
753 WHEN l_opp_multi_currency_error THEN
754 PA_UTILS.add_message('PA', 'PA_OPP_MULTI_CURRENCY_ERROR');
755 x_return_status := FND_API.G_RET_STS_ERROR;
756 x_msg_data := 'PA_OPP_MULTI_CURRENCY_ERROR';
757 x_msg_count := FND_MSG_PUB.Count_Msg;
758 IF x_msg_count = 1 THEN
759 pa_interface_utils_pub.get_messages
760 (p_encoded => FND_API.G_TRUE,
761 p_msg_index => 1,
762 p_msg_count => x_msg_count,
763 p_msg_data => x_msg_data,
764 --p_data => x_msg_data, * Bug: 4537865
765 p_data => l_new_msg_data, --Bug: 4537865
766 p_msg_index_out => l_msg_index_out );
767 --Bug: 4537865
768 x_msg_data := l_new_msg_data;
769 --Bug: 4537865
770 End IF;
771 WHEN OTHERS THEN
772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
774 p_procedure_name => 'validate_value_fields');
775 raise;
776
777 END validate_value_fields;
778
779
780 --
781 -- Procedure : get_opp_multi_currency_setup
782 -- Purpose :
783 --
784 --
785 PROCEDURE get_opp_multi_currency_setup
786 ( x_default_rate_type OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
787 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
788 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
789 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
790 IS
791 BEGIN
792
793 SELECT default_rate_type
794 INTO x_default_rate_type
795 FROM pa_implementations;
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
801 p_procedure_name => 'get_opp_multi_currency_setup');
802 raise;
803 END get_opp_multi_currency_setup;
804
805
806 --
807 -- Procedure : get_opp_multi_currency_setup (overloaded)
808 -- Purpose :
809 --
810 --
811 PROCEDURE get_opp_multi_currency_setup
812 ( p_org_id IN NUMBER ,
813 x_default_rate_type OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
814 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
815 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
816 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
817 IS
818 BEGIN
819
820 SELECT default_rate_type
821 INTO x_default_rate_type
822 FROM pa_implementations_all
823 WHERE org_id = p_org_id; --MOAC Changes: Bug 4363092: removed nvl usage with org_id
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
828 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
829 p_procedure_name => 'delete_project_attributes');
830 raise;
831 END get_opp_multi_currency_setup;
832
833
834 --
835 -- Function : is_opp_multi_currency_setup
836 -- Purpose :
837 --
838 --
839 FUNCTION is_opp_multi_currency_setup RETURN VARCHAR2
840
841 IS
842 CURSOR c1 IS
843 SELECT default_rate_type
844 FROM pa_implementations;
845
846 v_c1 c1%ROWTYPE;
847 l_result VARCHAR2(1);
848
849 --Bug:4469336 Added for avoiding blind calls to pa_debug.write and pa_debug.write_file
850 l_debug_mode VARCHAR2(10);
851 --Bug:4469336
852
853 BEGIN
854
855 --Bug:4469336 Added for avoiding blind calls to pa_debug.write and pa_debug.write_file
856 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
857 --Bug:4469336
858
859 OPEN c1;
860 FETCH c1 INTO v_c1;
861 IF c1%NOTFOUND THEN
862 l_result := 'N';
863 ELSE
864 IF v_c1.default_rate_type IS NULL THEN
865 l_result := 'N';
866 ELSE
867
868 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
869 IF l_debug_mode = 'Y' THEN
870 debug('default_rate_type = '|| v_c1.default_rate_type);
871 END IF;
872 l_result := 'Y';
873 END IF;
874 END IF;
875 CLOSE c1;
876
877 RETURN(l_result);
878
879 EXCEPTION
880 WHEN OTHERS THEN
881 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
882 p_procedure_name => 'is_opp_multi_currency_setup');
883 raise;
884
885 END is_opp_multi_currency_setup;
886
887
888 --
889 -- Function : is_opp_multi_currency_setup (overloaded)
890 -- Purpose :
891 --
892 --
893 FUNCTION is_opp_multi_currency_setup (p_org_id IN NUMBER) RETURN VARCHAR2
894
895 IS
896 CURSOR c1 IS
897 SELECT default_rate_type
898 FROM pa_implementations_all
899 WHERE org_id = p_org_id; --MOAC Changes: Bug 4363092: removed nvl usage with org_id
900
901 v_c1 c1%ROWTYPE;
902 l_result VARCHAR2(1);
903
904 --Bug:4469336 Added for avoiding blind calls to pa_debug.write and pa_debug.write_file
905 l_debug_mode VARCHAR2(10);
906 --Bug:4469336
907
908 BEGIN
909
910 --Bug:4469336 Added for avoiding blind calls to pa_debug.write and pa_debug.write_file
911 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
912 --Bug:4469336
913
914 OPEN c1;
915 FETCH c1 INTO v_c1;
916 IF c1%NOTFOUND THEN
917 l_result := 'N';
918 ELSE
919 IF v_c1.default_rate_type IS NULL THEN
920 l_result := 'N';
921 ELSE
922
923 --Bug:4469336. Added the if condition for avoiding blind calls to pa_debug.write and pa_debug.write_file
924 IF l_debug_mode = 'Y' then
925 debug('default_rate_type = '|| v_c1.default_rate_type);
926 end IF;
927
928 l_result := 'Y';
929 END IF;
930 END IF;
931 CLOSE c1;
932
933 RETURN(l_result);
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
938 p_procedure_name => 'is_opp_multi_currency_setup');
939 raise;
940
941 END is_opp_multi_currency_setup;
942
943
944 --
945 -- Function : is_proj_opp_associated
946 -- Purpose : Check whether a project has association with an opportunity.
947 --
948 --
949 FUNCTION is_proj_opp_associated(p_project_id IN NUMBER) RETURN VARCHAR2
950
951 IS
952 -- Start of changes for bug 4757078
953 /* CURSOR c1 IS
954 SELECT object_type_from, object_type_to
955 FROM pa_object_relationships
956 WHERE relationship_type = 'A'
957 AND relationship_subtype = 'PROJECT_REQUEST'
958 START WITH (object_type_from = 'PA_PROJECTS'
959 AND object_type_to = 'PA_PROJECT_REQUESTS'
960 AND object_id_from1 = p_project_id)
961 CONNECT BY (PRIOR object_id_to1 = object_id_from1
962 AND PRIOR object_id_from1 <> object_id_to1);*/
963
964 CURSOR c1 IS
965 SELECT object_type_from, object_type_to
966 FROM pa_object_relationships
967 START WITH (object_type_from = 'PA_PROJECTS'
968 AND object_type_to = 'PA_PROJECT_REQUESTS'
969 AND object_id_from1 = p_project_id)
970 CONNECT BY (PRIOR object_id_to1 = object_id_from1
971 AND PRIOR object_id_from1 <> object_id_to1
972 AND relationship_type = 'A'
973 AND relationship_subtype = 'PROJECT_REQUEST');
974
975 -- End of changes for bug 4757078
976
977 l_flag VARCHAR2(1);
978
979 BEGIN
980
981 l_flag := 'N';
982 FOR v_c1 IN c1 LOOP
983 IF v_c1.object_type_from = 'PA_PROJECT_REQUESTS' AND v_c1.object_type_to = 'AS_LEADS' THEN
984 l_flag := 'Y';
985 EXIT;
986 END IF;
987 END LOOP;
988
989 RETURN(l_flag);
990
991 EXCEPTION
992 WHEN OTHERS THEN
993 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_OPPORTUNITY_MGT_PVT',
994 p_procedure_name => 'is_proj_opp_associated');
995 raise;
996
997 END is_proj_opp_associated;
998
999
1000 END PA_OPPORTUNITY_MGT_PVT;