[Home] [Help]
PACKAGE BODY: APPS.PA_AGREEMENT_PVT
Source
1 package body PA_AGREEMENT_PVT as
2 /*$Header: PAAFAPVB.pls 120.9.12010000.3 2008/12/14 12:15:21 arbandyo ship $*/
3
4 --Global constants to be used in error messages
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_AGREEMENT_PVT';
6 G_AGREEMENT_CODE CONSTANT VARCHAR2(9) := 'AGREEMENT';
7
8 --PACKAGE GLOBAL to be used during updates ---------------------------
9 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
10 G_LOGIN_ID CONSTANT NUMBER := FND_GLOBAL.login_id;
11
12
13
14 -- ============================================================================
15 --
16 --Name: convert_ag_ref_to_id
17 --Type: Procedure
18 --Description: This procedure can be used to convert the agreement reference to
19 -- to an agreement id.
20 --
21 --Called subprograms:
22 -- pa_agreement_pvt.fetch_agreement_id
23 -- pa_interface_utils_pub.map_new_amg_msg
24 --
25 --
26 --History:
27 -- 25-MAR-2000 Rakesh Raghavan Created.
28 -- -----------------------------------------------------------------------------
29
30 PROCEDURE convert_ag_ref_to_id
31 (p_pm_agreement_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
32 ,p_af_agreement_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
33 ,p_out_agreement_id OUT NOCOPY NUMBER /*file.sql.39*/
34 ,p_RETURN_status OUT NOCOPY VARCHAR2 /*file.sql.39*/)
35 IS
36 CURSOR l_agreement_id_csr
37 IS
38 SELECT 'X'
39 FROM pa_agreements_all
40 where agreement_id = p_af_agreement_id;
41
42 l_api_name CONSTANT VARCHAR2(30) := 'Convert_ag_ref_to_id';
43 l_agreement_id NUMBER ;
44 l_dummy VARCHAR2(1);
45
46 BEGIN
47 --dbms_output.put_line(' Inside the pvt ag_ref_to_id');
48 p_return_status := FND_API.G_RET_STS_SUCCESS;
49
50
51 IF p_af_agreement_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
52 AND p_af_agreement_id IS NOT NULL
53 THEN
54
55 --check validity of this ID
56 OPEN l_agreement_id_csr;
57 FETCH l_agreement_id_csr INTO l_dummy;
58
59 IF l_agreement_id_csr%NOTFOUND
60 THEN
61 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
62 THEN
63 pa_interface_utils_pub.map_new_amg_msg
64 ( p_old_message_code => 'PA_INVALID_AGMT_ID'
65 ,p_msg_attribute => 'CHANGE'
66 ,p_resize_flag => 'N'
67 ,p_msg_context => 'GENERAL'
68 ,p_attribute1 => ''
69 ,p_attribute2 => ''
70 ,p_attribute3 => ''
71 ,p_attribute4 => ''
72 ,p_attribute5 => '');
73 END IF;
74 CLOSE l_agreement_id_csr;
75 RAISE FND_API.G_EXC_ERROR;
76 END IF;
77 CLOSE l_agreement_id_csr;
78 p_out_agreement_id := p_af_agreement_id;
79 ELSIF p_pm_agreement_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
80 AND p_pm_agreement_reference IS NOT NULL
81 THEN
82 --dbms_output.put_line('Calling fetch agreement id');
83 l_agreement_id := pa_agreement_pvt.fetch_agreement_id(p_pm_agreement_reference => p_pm_agreement_reference);
84 --dbms_output.put_line('Agreement id:'||nvl(to_char(l_agreement_id),'NULL'));
85 IF l_agreement_id IS NULL
86 THEN
87 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
88 THEN
89 FND_MESSAGE.SET_NAME('PA','PA_API_CONV_ERROR_AMG'); -- Bug 2257612
90 FND_MESSAGE.SET_TOKEN('ATTR_NAME','Pm Agreement Reference');
91 FND_MESSAGE.SET_TOKEN('ATTR_VALUE',p_pm_agreement_reference);
92 FND_MSG_PUB.add;
93 END IF;
94 RAISE FND_API.G_EXC_ERROR;
95 ELSE
96 p_out_agreement_id := l_agreement_id;
97 END IF;
98 ELSE
99 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
100 THEN
101 pa_interface_utils_pub.map_new_amg_msg
102 ( p_old_message_code => 'PA_AGMT_REF_AND_ID_MISS'
103 ,p_msg_attribute => 'CHANGE'
104 ,p_resize_flag => 'N'
105 ,p_msg_context => 'GENERAL'
106 ,p_attribute1 => ''
107 ,p_attribute2 => ''
108 ,p_attribute3 => ''
109 ,p_attribute4 => ''
110 ,p_attribute5 => '');
111 END IF;
112 RAISE FND_API.G_EXC_ERROR;
113 END IF; -- If p_af_agreement_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
114 EXCEPTION
115 WHEN FND_API.G_EXC_ERROR
116 THEN
117 ----dbms_output.put_line('handling an G_EXC_ERROR exception');
118 p_RETURN_status := FND_API.G_RET_STS_ERROR;
119
120 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
121 THEN
122 ----dbms_output.put_line('handling an G_EXC_UNEXPECTED_ERROR exception');
123 p_RETURN_status := FND_API.G_RET_STS_UNEXP_ERROR;
124
125 WHEN OTHERS
126 THEN
127 ----dbms_output.put_line('handling an OTHERS exception');
128 p_RETURN_status := FND_API.G_RET_STS_UNEXP_ERROR;
129 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
130 THEN
131 FND_MSG_PUB.add_exc_msg
132 ( p_pkg_name => G_PKG_NAME
133 , p_procedure_name => l_api_name );
134 END IF;
135 END convert_ag_ref_to_id;
136
137 -- ============================================================================
138 --
139 --Name: convert_fu_ref_to_id
140 --Type: Procedure
141 --Description: This procedure can be used to convert the funding reference to
142 -- to a funding id.
143 --
144 --Called subprograms:
145 -- pa_agreement_pvt.fetch_funding_id
146 -- pa_interface_utils_pub.map_new_amg_msg
147 --
148 --
149 --History:
150 -- 25-MAR-2000 Rakesh Raghavan Created.
151 -- -----------------------------------------------------------------------------
152 PROCEDURE convert_fu_ref_to_id
153 (p_pm_funding_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
154 ,p_af_funding_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
155 ,p_out_funding_id OUT NOCOPY NUMBER /*file.sql.39*/
156 ,p_RETURN_status OUT NOCOPY VARCHAR2 /*file.sql.39*/)
157 IS
158 CURSOR l_funding_id_csr
159 IS
160 SELECT 'X'
161 FROM pa_project_fundings
162 where project_funding_id = p_af_funding_id;
163
164 l_api_name CONSTANT VARCHAR2(30) := 'Convert_fu_ref_to_id';
165 l_agreement_id NUMBER ;
166 l_dummy VARCHAR2(1);
167 l_funding_id NUMBER ;
168 BEGIN
169 --dbms_output.put_line('Inside fetch agreement id');
170 p_RETURN_status := FND_API.G_RET_STS_SUCCESS;
171
172 IF p_af_funding_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
173 AND p_af_funding_id IS NOT NULL
174 THEN
175
176 --check validity of this ID
177 OPEN l_funding_id_csr;
178 FETCH l_funding_id_csr INTO l_dummy;
179
180 IF l_funding_id_csr%NOTFOUND
181 THEN
182 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
183 THEN
184 pa_interface_utils_pub.map_new_amg_msg
185 ( p_old_message_code => 'PA_INVALID_FUNDING_ID'
186 ,p_msg_attribute => 'CHANGE'
187 ,p_resize_flag => 'N'
188 ,p_msg_context => 'GENERAL'
189 ,p_attribute1 => ''
190 ,p_attribute2 => ''
191 ,p_attribute3 => ''
192 ,p_attribute4 => ''
193 ,p_attribute5 => '');
194 END IF;
195 CLOSE l_funding_id_csr;
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198 CLOSE l_funding_id_csr;
199 p_out_funding_id := p_af_funding_id;
200 ELSIF p_pm_funding_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
201 AND p_pm_funding_reference IS NOT NULL
202 THEN
203 l_funding_id := fetch_funding_id(p_pm_funding_reference => p_pm_funding_reference);
204 IF l_funding_id IS NULL
205 THEN
206 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
207 THEN
208 FND_MESSAGE.SET_NAME('PA','PA_API_CONV_ERROR_AMG'); -- Bug 2257612
209 FND_MESSAGE.SET_TOKEN('ATTR_NAME','Pm Funding Reference');
210 FND_MESSAGE.SET_TOKEN('ATTR_VALUE',p_pm_funding_reference);
211 FND_MSG_PUB.add;
212 END IF;
213 RAISE FND_API.G_EXC_ERROR;
214 ELSE
215 p_out_funding_id := l_funding_id;
216 END IF;
217 ELSE
218 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
219 THEN
220 pa_interface_utils_pub.map_new_amg_msg
221 ( p_old_message_code => 'PA_FUND_REF_AND_ID_MISS'
222 ,p_msg_attribute => 'CHANGE'
223 ,p_resize_flag => 'Y'
224 ,p_msg_context => 'GENERAL'
225 ,p_attribute1 => ''
226 ,p_attribute2 => ''
227 ,p_attribute3 => ''
228 ,p_attribute4 => ''
229 ,p_attribute5 => '');
230 END IF;
231 RAISE FND_API.G_EXC_ERROR;
232 END IF; -- If p_af_agreement_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
233 --dbms_output.put_line('Returning fetched funding id ='||nvl(to_char(p_out_funding_id),'NULL'));
234 EXCEPTION
235 WHEN FND_API.G_EXC_ERROR
236 THEN
237 ----dbms_output.put_line('handling an G_EXC_ERROR exception');
238 p_RETURN_status := FND_API.G_RET_STS_ERROR;
239
240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
241 THEN
242 ----dbms_output.put_line('handling an G_EXC_UNEXPECTED_ERROR exception');
243 p_RETURN_status := FND_API.G_RET_STS_UNEXP_ERROR;
244
245 WHEN OTHERS
246 THEN
247 ----dbms_output.put_line('handling an OTHERS exception');
248 p_RETURN_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
250 THEN
251 FND_MSG_PUB.add_exc_msg
252 ( p_pkg_name => G_PKG_NAME
253 , p_procedure_name => l_api_name );
254 END IF;
255
256 END convert_fu_ref_to_id;
257
258 -- ============================================================================
259 --
260 --Name: fetch_agreement_id
261 --Type: Function
262 --Description: This function can be used to fetch an agreement id when
263 -- provided with the agreement reference.
264 --
265 --Called subprograms:
266 -- None
267 --
268 --
269 --History:
270 -- 25-MAR-2000 Rakesh Raghavan Created.
271 -- -----------------------------------------------------------------------------
272
273 FUNCTION fetch_agreement_id
274 (p_pm_agreement_reference IN VARCHAR2)
275 RETURN NUMBER
276 IS
277 CURSOR c_agreements_csr IS
278 SELECT p.agreement_id
279 FROM pa_agreements_all p
280 WHERE p.pm_agreement_reference = p_pm_agreement_reference;
281
282 l_agreement_rec c_agreements_csr%ROWTYPE;
283
284 BEGIN
285
286 OPEN c_agreements_csr;
287 FETCH c_agreements_csr INTO l_agreement_rec;
288 IF c_agreements_csr%NOTFOUND THEN
289 CLOSE c_agreements_csr;
290 RETURN NULL;
291 ELSE
292 CLOSE c_agreements_csr;
293 RETURN(l_agreement_rec.agreement_id);
294 END IF;
295
296 END fetch_agreement_id;
297
298 -- ============================================================================
299 --
300 --Name: fetch_funding_id
301 --Type: Function
302 --Description: This function can be used to fetch an funding id when
303 -- provided with the funding reference.
304 --
305 --Called subprograms:
306 -- None
307 --
308 --
309 --History:
310 -- 25-MAR-2000 Rakesh Raghavan Created.
311 -- -----------------------------------------------------------------------------
312
313 FUNCTION fetch_funding_id
314 (p_pm_funding_reference IN VARCHAR2 )
315 RETURN NUMBER
316 IS
317 CURSOR c_fundings_csr IS
318 SELECT project_funding_id
319 FROM
320 pa_project_fundings
321 WHERE
322 pm_funding_reference = p_pm_funding_reference;
323
324 l_funding_rec c_fundings_csr%ROWTYPE;
325
326 BEGIN
327
328 OPEN c_fundings_csr;
329 FETCH c_fundings_csr INTO l_funding_rec;
330 IF c_fundings_csr%NOTFOUND THEN
331 CLOSE c_fundings_csr;
332 RETURN NULL;
333 ELSE
334 CLOSE c_fundings_csr;
335 RETURN(l_funding_rec.project_funding_id);
336 END IF;
337
338 END fetch_funding_id;
339
340
341 -- ============================================================================
342 --
343 --Name: check_create_agreement_ok
344 --Type: Function
345 --Description: This function can be used to check IF it is OK to create the
346 -- agreement.
347 --
348 --Called subprograms:
349 -- pa_agreement_utils.check_valid_customer
350 -- pa_agreement_utils.check_valid_type
351 -- pa_agreement_utils.check_valid_agreement_num
352 -- pa_agreement_utils.check_valid_term_id
353 -- pa_agreement_pvt.check_valid_template_flag
354 -- pa_agreement_pvt.check_valid_revenue_limit_flag
355 -- pa_agreement_utils.check_valid_owned_by_person_id
356 -- pa_agreement_utils.check_unique_agreement
357 --
358 --
359 --History:
360 -- 25-MAR-2000 Rakesh Raghavan Created.
361 -- 10-SEP-2001 Srividya Modified for MCB2.
362 -- -----------------------------------------------------------------------------
363
364 FUNCTION check_create_agreement_ok
365 (p_pm_agreement_reference IN VARCHAR2
366 ,p_customer_id IN NUMBER
367 ,p_agreement_type IN VARCHAR2
368 ,p_agreement_num IN VARCHAR2
369 ,p_term_id IN NUMBER
370 ,p_template_flag IN VARCHAR2
371 ,p_revenue_limit_flag IN VARCHAR2
372 ,p_owned_by_person_id IN NUMBER
373 ,p_owning_organization_id IN NUMBER default null
374 ,p_agreement_currency_code IN VARCHAR2 default null
375 ,p_invoice_limit_flag IN VARCHAR2 default null
376 /* Federal*/
377 ,p_start_date IN DATE DEFAULT NULL
378 ,p_end_date IN DATE DEFAULT NULL
379 ,p_advance_required IN VARCHAR2 DEFAULT NULL
380 ,p_billing_sequence IN Number DEFAULT NULL)
381 RETURN VARCHAR2
382 IS
383 -- LOCAL VARIABLES
384 l_RETURN VARCHAR2(1):='Y';
385
386 /* x_advance_flag boolean; Commented for bug 5743599*/
387 x_error_message varchar2(240);
388 x_status Number;
389
390 BEGIN
391 --dbms_output.put_line('Inside the private package');
392
393 -- VALIDATE THE PARAMETERS
394
395 -- Customer Number
396 --dbms_output.put_line('Check for Valid Customer Id');
397 IF pa_agreement_utils.check_valid_customer
398 (p_customer_id => p_customer_id) = 'N'
399 THEN
400 --dbms_output.put_line('Invalid Customer Id');
401 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
402 THEN
403 pa_interface_utils_pub.map_new_amg_msg
404 ( p_old_message_code => 'PA_INVALID_CUST'
405 ,p_msg_attribute => 'CHANGE'
406 ,p_resize_flag => 'N'
407 ,p_msg_context => 'AGREEMENT'
408 ,p_attribute1 => p_pm_agreement_reference
409 ,p_attribute2 => ''
410 ,p_attribute3 => ''
411 ,p_attribute4 => ''
412 ,p_attribute5 => '');
413 END IF;
414 l_RETURN:='N';
415 END IF;
416
417 -- Agreement Type
418 --dbms_output.put_line('Check for valid Agreement Type');
419 IF pa_agreement_utils.check_valid_type
420 (p_agreement_type => p_agreement_type) = 'N'
421 THEN
422 --dbms_output.put_line('Invalid Agreement Type');
423 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
424 THEN
425 pa_interface_utils_pub.map_new_amg_msg
426 ( p_old_message_code => 'PA_INVALID_TYPE'
427 ,p_msg_attribute => 'CHANGE'
428 ,p_resize_flag => 'N'
429 ,p_msg_context => 'AGREEMENT'
430 ,p_attribute1 => p_pm_agreement_reference
431 ,p_attribute2 => ''
432 ,p_attribute3 => ''
433 ,p_attribute4 => ''
434 ,p_attribute5 => '');
435 END IF;
436 l_RETURN:='N';
437 END IF;
438
439 /* NOT REQUIRED - TEST
440 -- Agreement Number
441 --dbms_output.put_line('Check for Agreement Number');
442 IF pa_agreement_utils.check_valid_agreement_num
443 (p_agreement_num => p_agreement_num) = 'N'
444 THEN
445 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
446 THEN
447 --dbms_output.put_line('Invalid Agreement Number');
448 pa_interface_utils_pub.map_new_amg_msg
449 ( p_old_message_code => 'PA_INVALID_AGMT_NUM'
450 ,p_msg_attribute => 'CHANGE'
451 ,p_resize_flag => 'N'
452 ,p_msg_context => 'AGREEMENT'
453 ,p_attribute1 => p_pm_agreement_reference
454 ,p_attribute2 => ''
455 ,p_attribute3 => ''
456 ,p_attribute4 => ''
457 ,p_attribute5 => '');
458 END IF;
459 l_RETURN:='N';
460 END IF;
461 */
462
463 -- Term Name
464 --dbms_output.put_line('Check for valid Term Name');
465 IF pa_agreement_utils.check_valid_term_id
466 (p_term_id => p_term_id) = 'N'
467 THEN
468 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
469 THEN
470 --dbms_output.put_line('Invalid Term Name');
471 pa_interface_utils_pub.map_new_amg_msg
472 ( p_old_message_code => 'PA_INVALID_TERM_NAME'
473 ,p_msg_attribute => 'CHANGE'
474 ,p_resize_flag => 'N'
475 ,p_msg_context => 'AGREEMENT'
476 ,p_attribute1 => p_pm_agreement_reference
477 ,p_attribute2 => ''
478 ,p_attribute3 => ''
479 ,p_attribute4 => ''
480 ,p_attribute5 => '');
481 END IF;
482 l_RETURN:='N';
483 END IF;
484
485
486 -- Template Flag
487 IF p_template_flag is not null THEN
488 --dbms_output.put_line('Check for valid Template Flag');
489 IF pa_agreement_pvt.check_yes_no
490 ( p_val => p_template_flag) = 'N'
491 THEN
492 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
493 THEN
494 --dbms_output.put_line('Invalid Template Flag');
495 pa_interface_utils_pub.map_new_amg_msg
496 ( p_old_message_code => 'PA_INVALID_TEMP_FLG'
497 ,p_msg_attribute => 'CHANGE'
498 ,p_resize_flag => 'N'
499 ,p_msg_context => 'AGREEMENT'
500 ,p_attribute1 => p_pm_agreement_reference
501 ,p_attribute2 => ''
502 ,p_attribute3 => ''
503 ,p_attribute4 => ''
504 ,p_attribute5 => '');
505 END IF;
506 l_RETURN:='N';
507 END IF;
508
509 END IF;
510 -- Revenue Limit Flag
511 --dbms_output.put_line('Check for valid Revenue Limit Flag');
512 IF pa_agreement_pvt.check_yes_no
513 (p_val => p_revenue_limit_flag) = 'N'
514 THEN
515 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
516 THEN
517 --dbms_output.put_line('Invalid Revenue Limit Flag');
518 pa_interface_utils_pub.map_new_amg_msg
519 ( p_old_message_code => 'PA_INVALID_REV_LT_FLG'
520 ,p_msg_attribute => 'CHANGE'
521 ,p_resize_flag => 'N'
522 ,p_msg_context => 'AGREEMENT'
523 ,p_attribute1 => p_pm_agreement_reference
524 ,p_attribute2 => ''
525 ,p_attribute3 => ''
526 ,p_attribute4 => ''
527 ,p_attribute5 => '');
528 END IF;
529 l_RETURN:='N';
530 END IF;
531
532
533 -- Owned By Person Id
534 --dbms_output.put_line('Check for valid owned by person id');
535 IF pa_agreement_utils.check_valid_owned_by_person_id
536 (p_owned_by_person_id => p_owned_by_person_id) = 'N'
537 THEN
538 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
539 THEN
540 --dbms_output.put_line('Invalid Owned By Person id');
541 pa_interface_utils_pub.map_new_amg_msg
542 ( p_old_message_code => 'PA_INVALID_OWND_BY_PRSN_ID'
543 ,p_msg_attribute => 'CHANGE'
544 ,p_resize_flag => 'N'
545 ,p_msg_context => 'AGREEMENT'
546 ,p_attribute1 => p_pm_agreement_reference
547 ,p_attribute2 => ''
548 ,p_attribute3 => ''
549 ,p_attribute4 => ''
550 ,p_attribute5 => '');
551 END IF;
552 l_RETURN:='N';
553 END IF;
554
555 -- Unique Agreement
556 --dbms_output.put_line('Check for Unique agreement');
557 IF pa_agreement_utils.check_unique_agreement
558 (p_agreement_num => p_agreement_num
559 ,p_agreement_type => p_agreement_type
560 ,p_customer_id => p_customer_id) = 'N'
561 THEN
562 --dbms_output.put_line('Agreement Not Unique');
563 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
564 THEN
565 pa_interface_utils_pub.map_new_amg_msg
566 ( p_old_message_code => 'PA_AGMT_NOT_UNIQUE'
567 ,p_msg_attribute => 'CHANGE'
568 ,p_resize_flag => 'N'
569 ,p_msg_context => 'AGREEMENT'
570 ,p_attribute1 => p_pm_agreement_reference
571 ,p_attribute2 => ''
572 ,p_attribute3 => ''
573 ,p_attribute4 => ''
574 ,p_attribute5 => '');
575 END IF;
576 l_RETURN:='N';
577 END IF;
578
579 /* MCB2 code begins */
580 -- valid owning organization id
581 --dbms_output.put_line('Check for valid owning orgn id ');
582
583 IF (p_owning_organization_id is not null )and
584 (p_owning_organization_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM )then /* Bug 2701579 */
585
586 IF pa_agreement_utils.check_valid_owning_orgn_id
587 (p_owning_organization_id => p_owning_organization_id
588 ) = 'N'
589 THEN
590 --dbms_output.put_line('Invalid owning organization_id ');
591 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
592 THEN
593 pa_interface_utils_pub.map_new_amg_msg
594 ( p_old_message_code => 'PA_OWNING_ORGN_ID_INVALID'
595 ,p_msg_attribute => 'CHANGE'
596 ,p_resize_flag => 'N'
597 ,p_msg_context => 'AGREEMENT'
598 ,p_attribute1 => p_pm_agreement_reference
599 ,p_attribute2 => ''
600 ,p_attribute3 => ''
601 ,p_attribute4 => ''
602 ,p_attribute5 => '');
603 END IF;
604 l_RETURN:='N';
605 END IF;
606 END IF;
607
608 IF (p_agreement_currency_code is not null ) AND
609 (p_agreement_currency_code <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) then /* Bug 2701579 */
610
611 IF pa_agreement_utils.check_valid_agr_curr_code
612 (p_agreement_currency_code => p_agreement_currency_code
613 ) = 'N'
614 THEN
615 --dbms_output.put_line('Invalid agreement_currency_code ');
616 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
617 THEN
618 pa_interface_utils_pub.map_new_amg_msg
619 ( p_old_message_code => 'PA_CURR_NOT_VALID'
620 ,p_msg_attribute => 'CHANGE'
621 ,p_resize_flag => 'N'
622 ,p_msg_context => 'AGREEMENT'
623 ,p_attribute1 => p_pm_agreement_reference
624 ,p_attribute2 => ''
625 ,p_attribute3 => ''
626 ,p_attribute4 => ''
627 ,p_attribute5 => '');
628 END IF;
629 l_RETURN:='N';
630 END IF;
631 END IF;
632
633 /*Federal*/
634
635 IF (p_start_date is not null ) AND
636 (p_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) AND
637 (p_end_date is not null) AND
638 (p_end_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) then
639
640 IF (p_start_date >= p_end_date)
641
642 THEN
643 --dbms_output.put_line('Invalid agreement_currency_code ');
644 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
645 THEN
646 pa_interface_utils_pub.map_new_amg_msg
647 ( p_old_message_code => 'PA_INVALID_START_DATE'
648 ,p_msg_attribute => 'CHANGE'
649 ,p_resize_flag => 'N'
650 ,p_msg_context => 'AGREEMENT'
651 ,p_attribute1 => p_pm_agreement_reference
652 ,p_attribute2 => ''
653 ,p_attribute3 => ''
654 ,p_attribute4 => ''
655 ,p_attribute5 => '');
656 END IF;
657 l_RETURN:='N';
658 END IF;
659 END IF;
660
661 /* IF (p_advance_required = 'Y') then
662
663 IF (FND_FUNCTION.TEST('PA_PAXINEAG_ADVREQ')) THEN
664
665 PA_ADVANCE_CLIENT_EXT.advance_required(p_customer_id,
666 x_advance_flag,
667 x_error_message,
668 x_status);
669
670 IF (x_status = 0 and x_advance_flag = TRUE) THEN
671 null;
672 ELSE
673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
674 THEN
675 pa_interface_utils_pub.map_new_amg_msg
676 ( p_old_message_code => 'PA_CLNT_ADV_CHECK'
677 ,p_msg_attribute => 'CHANGE'
678 ,p_resize_flag => 'N'
679 ,p_msg_context => 'AGREEMENT'
680 ,p_attribute1 => p_pm_agreement_reference
681 ,p_attribute2 => ''
682 ,p_attribute3 => ''
683 ,p_attribute4 => ''
684 ,p_attribute5 => '');
685 END IF;
686 l_RETURN:='N';
687 END IF; /* client advance flag value
688 ELSE
689 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
690 THEN
691 pa_interface_utils_pub.map_new_amg_msg
692 ( p_old_message_code => 'PA_CLNT_ADV_CHECK'
693 ,p_msg_attribute => 'CHANGE'
694 ,p_resize_flag => 'N'
695 ,p_msg_context => 'AGREEMENT'
696 ,p_attribute1 => p_pm_agreement_reference
697 ,p_attribute2 => ''
698 ,p_attribute3 => ''
699 ,p_attribute4 => ''
700 ,p_attribute5 => '');
701 END IF;
702 l_RETURN:='N';
703 END IF; /* Function security
704 END IF; /*Advance required Commented for bug 5743599*/
705
706
707 IF ((p_billing_sequence <=0 or p_billing_sequence > 99) and
708 p_billing_sequence <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
709 then
710
711 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
712 THEN
713 pa_interface_utils_pub.map_new_amg_msg
714 ( p_old_message_code => 'PA_INVALID_BILL_SEQ'
715 ,p_msg_attribute => 'CHANGE'
716 ,p_resize_flag => 'N'
717 ,p_msg_context => 'AGREEMENT'
718 ,p_attribute1 => p_pm_agreement_reference
719 ,p_attribute2 => ''
720 ,p_attribute3 => ''
721 ,p_attribute4 => ''
722 ,p_attribute5 => '');
723 END IF;
724 l_RETURN:='N';
725
726 END IF;
727
728 -- invoice Limit Flag
729 --dbms_output.put_line('Check for valid invoice Limit Flag');
730
731 IF (p_invoice_limit_flag is not null)
732 AND (p_invoice_limit_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) then /* Bug 2701579 */
733
734 IF pa_agreement_pvt.check_yes_no
735 (p_val => p_invoice_limit_flag) = 'N'
736 THEN
737
738 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
739 THEN
740 --dbms_output.put_line('Invalid invoice Limit Flag');
741 pa_interface_utils_pub.map_new_amg_msg
742 ( p_old_message_code => 'PA_INVALID_INV_LT_FLG'
743 ,p_msg_attribute => 'CHANGE'
744 ,p_resize_flag => 'N'
745 ,p_msg_context => 'AGREEMENT'
746 ,p_attribute1 => p_pm_agreement_reference
747 ,p_attribute2 => ''
748 ,p_attribute3 => ''
749 ,p_attribute4 => ''
750 ,p_attribute5 => '');
751 END IF;
752 l_RETURN:='N';
753 END IF;
754 END IF;
755
756 /* MCB2 code ends */
757 RETURN(l_RETURN);
758
759 END check_create_agreement_ok;
760
761 -- ============================================================================
762 --
763 --Name: check_update_agreement_ok
764 --Type: Function
765 --Description: This function can be used to check IF it is OK to update the
766 -- agreement.
767 --
768 --Called subprograms:
769 --
770 --
771 --
772 --History:
773 -- 25-MAR-2000 Rakesh Raghavan Created.
774 -- 10-SEP-2001 Srividya Modified for MCB2.
775 -- 25-JUL-2002 prajaram Bug 2442176: A new check is added
776 -- to see if the Agreement has funding
777 -- allocated and if so the currency is
778 -- not allowed to be changed.
779 -- -----------------------------------------------------------------------------
780 FUNCTION check_update_agreement_ok
781 (p_pm_agreement_reference IN VARCHAR2
782 ,p_agreement_id IN NUMBER
783 ,p_funding_id IN NUMBER
784 ,p_customer_id IN OUT NOCOPY NUMBER /*Bug 6602451*/
785 ,p_agreement_type IN OUT NOCOPY VARCHAR2 /*Bug 6602451*/
786 ,p_term_id IN OUT NOCOPY NUMBER /*Bug 6602451*/
787 ,p_template_flag IN VARCHAR2
788 ,p_revenue_limit_flag IN OUT NOCOPY VARCHAR2 /*Bug 6602451*/
789 ,p_owned_by_person_id IN OUT NOCOPY NUMBER /*Bug 6602451*/
790 ,p_owning_organization_id IN OUT NOCOPY NUMBER /*Bug 6602451*/
791 ,p_agreement_currency_code IN OUT NOCOPY VARCHAR2 /*Bug 6602451*/
792 ,p_invoice_limit_flag IN OUT NOCOPY VARCHAR2 /*Bug 6602451*/
793 /*Federal*/
794 ,p_start_date IN DATE DEFAULT NULL
795 ,p_end_date IN DATE DEFAULT NULL
796 ,p_advance_required IN VARCHAR2 DEFAULT NULL
797 ,p_billing_sequence IN Number DEFAULT NULL
798 ,p_amount IN NUMBER DEFAULT NULL)
799
800 RETURN VARCHAR2
801 IS
802 -- LOCAL VARIABLES
803 l_start_date DATE;
804 l_end_date DATE;
805 l_adv_req VARCHAR2(1);
806
807 cur_start_date DATE;
808 cur_end_date DATE;
809 cur_adv_req VARCHAR2(1);
810 cur_adv_amt NUMBER;
811 cur_agmt_amt NUMBER;
812 l_fund_count NUMBER;
813 --l_tot_fund NUMBER; Commented for bug 6853994
814 l_agm_amt NUMBER;
815 l_count NUMBER;
816
817 x_advance_flag boolean;
818 x_error_message varchar2(240);
819 x_status Number;
820
821 l_RETURN VARCHAR2(1) :='Y';
822
823 /* Cursor added for bug 6602451 */
824 CURSOR agrcur IS SELECT * FROM PA_AGREEMENTS_ALL WHERE AGREEMENT_ID = p_agreement_id;
825
826 BEGIN
827 --dbms_output.put_line('Inside: PA_AGREEMENT_PVT.CHECK_UPDATE_AGREEMENT_OK');
828 FOR l_agrcur IN agrcur LOOP /*Bug 6602451 */
829 -- VALIDATE THE INCOMING PARAMETERS
830 -- Customer Number
831 --dbms_output.put_line('Check for Valid Customer Id');
832 IF (p_customer_id IS NOT NULL AND p_customer_id <>
833 PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
834 AND p_customer_id <> l_agrcur.customer_id) THEN /* Bug 6602451 */
835 IF pa_agreement_utils.check_valid_customer
836 (p_customer_id => p_customer_id) = 'N'
837 THEN
838 --dbms_output.put_line('Invalid Customer Id');
839 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
840 THEN
841 pa_interface_utils_pub.map_new_amg_msg
842 ( p_old_message_code => 'PA_INVALID_CUST'
843 ,p_msg_attribute => 'CHANGE'
844 ,p_resize_flag => 'N'
845 ,p_msg_context => 'AGREEMENT'
846 ,p_attribute1 => p_pm_agreement_reference
847 ,p_attribute2 => ''
848 ,p_attribute3 => ''
849 ,p_attribute4 => ''
850 ,p_attribute5 => '');
851 END IF;
852 l_RETURN:='N';
853 END IF;
854 /* Added for bug 6602451 */
855 ELSE
856 p_customer_id := l_agrcur.customer_id;
857 END IF;
858
859 -- Agreement Type
860 --dbms_output.put_line('Check for valid Agreement Type');
861 IF (p_agreement_type IS NOT NULL AND p_agreement_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
862 AND p_agreement_type <> l_agrcur.agreement_type) THEN /* Bug 6602451 */
863 IF pa_agreement_utils.check_valid_type
864 (p_agreement_type => p_agreement_type) = 'N'
865 THEN
866 --dbms_output.put_line('Invalid Agreement Type');
867 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
868 THEN
869 pa_interface_utils_pub.map_new_amg_msg
870 ( p_old_message_code => 'PA_INVALID_TYPE'
871 ,p_msg_attribute => 'CHANGE'
872 ,p_resize_flag => 'N'
873 ,p_msg_context => 'AGREEMENT'
874 ,p_attribute1 => p_pm_agreement_reference
875 ,p_attribute2 => ''
876 ,p_attribute3 => ''
877 ,p_attribute4 => ''
878 ,p_attribute5 => '');
879 END IF;
880 l_RETURN:='N';
881 END IF;
882 /* Added for bug 6602451 */
883 ELSE
884 p_agreement_type := l_agrcur.agreement_type;
885 END IF;
886
887 /* NOT REQUIRED
888 -- Agreement Number
889 --dbms_output.put_line('Check for Agreement Number');
890 IF pa_agreement_utils.check_valid_agreement_num
891 (p_agreement_num => p_agreement_num) = 'N'
892 THEN
893 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
894 THEN
895 --dbms_output.put_line('Invalid Agreement Number');
896 pa_interface_utils_pub.map_new_amg_msg
897 ( p_old_message_code => 'PA_INVALID_AGMT_NUM'
898 ,p_msg_attribute => 'CHANGE'
899 ,p_resize_flag => 'N'
900 ,p_msg_context => 'AGREEMENT'
901 ,p_attribute1 => p_pm_agreement_reference
902 ,p_attribute2 => ''
903 ,p_attribute3 => ''
904 ,p_attribute4 => ''
905 ,p_attribute5 => '');
906 END IF;
907 l_RETURN:='N';
908 END IF;
909 */
910
911 -- Term Name
912 --dbms_output.put_line('Check for valid Term Name');
913 IF (p_term_id IS NOT NULL AND p_term_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
914 AND p_term_id <> l_agrcur.term_id) THEN /* Bug 6602451 */
915 IF pa_agreement_utils.check_valid_term_id
916 (p_term_id => p_term_id) = 'N'
917 THEN
918 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
919 THEN
920 --dbms_output.put_line('Invalid Term Name');
921 pa_interface_utils_pub.map_new_amg_msg
922 ( p_old_message_code => 'PA_INVALID_TERM_NAME'
923 ,p_msg_attribute => 'CHANGE'
924 ,p_resize_flag => 'N'
925 ,p_msg_context => 'AGREEMENT'
926 ,p_attribute1 => p_pm_agreement_reference
927 ,p_attribute2 => ''
928 ,p_attribute3 => ''
929 ,p_attribute4 => ''
930 ,p_attribute5 => '');
931 END IF;
932 l_RETURN:='N';
933 END IF;
934 /* Added for bug 6602451 */
935 ELSE
936 p_term_id := l_agrcur.term_id;
937 END IF;
938
939 /*
940 -- Template Flag
941 --dbms_output.put_line('Check for valid Template Flag');
942 IF pa_agreement_pvt.check_yes_no
943 ( p_val => p_template_flag) = 'N'
944 THEN
945 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
946 THEN
947 --dbms_output.put_line('Invalid Template Flag');
948 pa_interface_utils_pub.map_new_amg_msg
949 ( p_old_message_code => 'PA_INVALID_TEMP_FLG'
950 ,p_msg_attribute => 'CHANGE'
951 ,p_resize_flag => 'N'
952 ,p_msg_context => 'AGREEMENT'
953 ,p_attribute1 => p_pm_agreement_reference
954 ,p_attribute2 => ''
955 ,p_attribute3 => ''
956 ,p_attribute4 => ''
957 ,p_attribute5 => '');
958 END IF;
959 l_RETURN:='N';
960 END IF;
961
962 */
963 -- Revenue Limit Flag
964 --dbms_output.put_line('Check for valid Revenue Limit Flag');
965 IF (p_revenue_limit_flag IS NOT NULL AND p_revenue_limit_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
966 AND p_revenue_limit_flag <> l_agrcur.revenue_limit_flag) THEN /* Bug 6602451 */
967 IF pa_agreement_pvt.check_yes_no
968 (p_val => p_revenue_limit_flag) = 'N'
969 THEN
970 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
971 THEN
972 --dbms_output.put_line('Invalid Revenue Limit Flag');
973 pa_interface_utils_pub.map_new_amg_msg
974 ( p_old_message_code => 'PA_INVALID_REV_LT_FLG'
975 ,p_msg_attribute => 'CHANGE'
976 ,p_resize_flag => 'N'
977 ,p_msg_context => 'AGREEMENT'
978 ,p_attribute1 => p_pm_agreement_reference
979 ,p_attribute2 => ''
980 ,p_attribute3 => ''
981 ,p_attribute4 => ''
982 ,p_attribute5 => '');
983 END IF;
984 l_RETURN:='N';
985 END IF;
986 /* Added for bug 6602451 */
987 ELSE
988 p_revenue_limit_flag := l_agrcur.revenue_limit_flag;
989 END IF;
990
991 -- Owned By Person Id
992 --dbms_output.put_line('Check for valid owned by person id');
993 IF (p_owned_by_person_id IS NOT NULL AND p_owned_by_person_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
994 AND p_owned_by_person_id <> l_agrcur.owned_by_person_id) THEN /* Bug 6602451 */
995 IF pa_agreement_utils.check_valid_owned_by_person_id
996 (p_owned_by_person_id => p_owned_by_person_id) = 'N'
997 THEN
998 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
999 THEN
1000 --dbms_output.put_line('Invalid Owned By Person id');
1001 pa_interface_utils_pub.map_new_amg_msg
1002 ( p_old_message_code => 'PA_INVALID_OWND_BY_PRSN_ID'
1003 ,p_msg_attribute => 'CHANGE'
1004 ,p_resize_flag => 'N'
1005 ,p_msg_context => 'AGREEMENT'
1006 ,p_attribute1 => p_pm_agreement_reference
1007 ,p_attribute2 => ''
1008 ,p_attribute3 => ''
1009 ,p_attribute4 => ''
1010 ,p_attribute5 => '');
1011 END IF;
1012 l_RETURN:='N';
1013 END IF;
1014 /* Added for bug 6602451 */
1015 ELSE
1016 p_owned_by_person_id := l_agrcur.owned_by_person_id;
1017 END IF;
1018
1019 /* NOT REQUIRED
1020 -- Check Funding Revenue Amount
1021 IF pa_agreement_utils.check_fund_rev_amt
1022 ( p_funding_id => p_funding_id) = 'N'
1023 THEN
1024 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1025 THEN
1026 pa_interface_utils_pub.map_new_amg_msg
1027 ( p_old_message_code => 'PA_INVALID_FUND_REV_AMT'
1028 ,p_msg_attribute => 'CHANGE'
1029 ,p_resize_flag => 'N'
1030 ,p_msg_context => 'AGREEMENT'
1031 ,p_attribute1 => p_pm_agreement_reference
1032 ,p_attribute2 => ''
1033 ,p_attribute3 => ''
1034 ,p_attribute4 => ''
1035 ,p_attribute5 => '');
1036 END IF;
1037 l_RETURN:='N';
1038 END IF;
1039 */
1040
1041 /*Federal*/
1042
1043 SELECT start_date,
1044 expiration_date,
1045 nvl(advance_Required,'N'),
1046 advance_amount,
1047 amount
1048 INTO cur_start_date,
1049 cur_end_date,
1050 cur_adv_req,
1051 cur_adv_amt,
1052 cur_agmt_amt
1053 FROM pa_agreements_all
1054 WHERE agreement_id = p_agreement_id;
1055 /* Commented for bug 6853994
1056 SELECT sum(allocated_amount)
1057 INTO l_tot_fund
1058 FROM pa_project_fundings
1059 WHERE agreement_id = p_agreement_id;
1060 */
1061 IF (p_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) THEN
1062 l_start_date := cur_start_date;
1063 ELSE
1064 l_start_date := p_start_date;
1065 END IF;
1066
1067 IF (p_end_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) THEN
1068 l_end_date := cur_end_date;
1069 ELSE
1070 l_end_date := p_end_date;
1071 END IF;
1072
1073 IF (p_advance_required = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1074
1075 l_adv_req := cur_adv_req;
1076 ELSE
1077 l_adv_req := nvl(p_advance_required,'N');/*Bug 5747269 */
1078
1079 END IF;
1080
1081 IF (l_adv_req = 'Y') then
1082 l_agm_amt := cur_adv_amt;
1083 ELSE
1084 IF (p_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1085 l_agm_amt := cur_agmt_amt;
1086 ELSE
1087 l_agm_amt := p_amount;
1088 END IF;
1089 END IF;
1090
1091 IF (l_start_date >= l_end_date)
1092 THEN
1093 --dbms_output.put_line('Invalid agreement_currency_code ');
1094 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1095 THEN
1096 pa_interface_utils_pub.map_new_amg_msg
1097 ( p_old_message_code => 'PA_INVALID_START_DATE'
1098 ,p_msg_attribute => 'CHANGE'
1099 ,p_resize_flag => 'N'
1100 ,p_msg_context => 'AGREEMENT'
1101 ,p_attribute1 => p_pm_agreement_reference
1102 ,p_attribute2 => ''
1103 ,p_attribute3 => ''
1104 ,p_attribute4 => ''
1105 ,p_attribute5 => '');
1106 END IF;
1107 l_RETURN:='N';
1108 END IF;
1109
1110 /* Federal
1111 SELECT count(*)
1112 INTO l_count
1113 FROM pa_project_fundings fun,
1114 pa_events ev
1115 WHERE fun.agreement_id = p_agreement_id
1116 AND fun.agreement_id = ev.agreement_id
1117 AND fun.project_id = ev.project_id
1118 AND ev.completion_date not between l_start_date and l_end_date;
1119
1120
1121 IF (l_count >0)
1122 THEN
1123 --dbms_output.put_line('Invalid agreement_currency_code ');
1124 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1125 THEN
1126 pa_interface_utils_pub.map_new_amg_msg
1127 ( p_old_message_code => 'PA_INVALID_EVENT_DATE'
1128 ,p_msg_attribute => 'CHANGE'
1129 ,p_resize_flag => 'N'
1130 ,p_msg_context => 'AGREEMENT'
1131 ,p_attribute1 => p_pm_agreement_reference
1132 ,p_attribute2 => ''
1133 ,p_attribute3 => ''
1134 ,p_attribute4 => ''
1135 ,p_attribute5 => '');
1136 END IF;
1137 l_RETURN:='N';
1138 END IF; */
1139
1140 IF (cur_adv_req <> l_adv_req) then
1141
1142 SELECT COUNT(*)
1143 INTO l_fund_count
1144 FROM pa_project_fundings
1145 WHERE agreement_id = p_agreement_id;
1146
1147 IF l_fund_count >0 THEN
1148 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1149 THEN
1150 pa_interface_utils_pub.map_new_amg_msg
1151 ( p_old_message_code => 'PA_CLNT_ADV_CHECK'
1152 ,p_msg_attribute => 'CHANGE'
1153 ,p_resize_flag => 'N'
1154 ,p_msg_context => 'AGREEMENT'
1155 ,p_attribute1 => p_pm_agreement_reference
1156 ,p_attribute2 => ''
1157 ,p_attribute3 => ''
1158 ,p_attribute4 => ''
1159 ,p_attribute5 => '');
1160 END IF;
1161 l_RETURN:='N';
1162 ELSE
1163 IF FND_FUNCTION.TEST('PA_PAXINEAG_ADVREQ') THEN
1164
1165 PA_ADVANCE_CLIENT_EXT.advance_required(p_customer_id,
1166 x_advance_flag,
1167 x_error_message,
1168 x_status);
1169
1170 IF (x_status = 0 and x_advance_flag = TRUE) THEN
1171 null;
1172 ELSE
1173 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1174 THEN
1175 pa_interface_utils_pub.map_new_amg_msg
1176 ( p_old_message_code => 'PA_CLNT_ADV_CHECK'
1177 ,p_msg_attribute => 'CHANGE'
1178 ,p_resize_flag => 'N'
1179 ,p_msg_context => 'AGREEMENT'
1180 ,p_attribute1 => p_pm_agreement_reference
1181 ,p_attribute2 => ''
1182 ,p_attribute3 => ''
1183 ,p_attribute4 => ''
1184 ,p_attribute5 => '');
1185 END IF;
1186 l_RETURN:='N';
1187 END IF; /* value from client ext*/
1188 ELSE
1189 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1190 THEN
1191 pa_interface_utils_pub.map_new_amg_msg
1192 ( p_old_message_code => 'PA_CLNT_ADV_CHECK'
1193 ,p_msg_attribute => 'CHANGE'
1194 ,p_resize_flag => 'N'
1195 ,p_msg_context => 'AGREEMENT'
1196 ,p_attribute1 => p_pm_agreement_reference
1197 ,p_attribute2 => ''
1198 ,p_attribute3 => ''
1199 ,p_attribute4 => ''
1200 ,p_attribute5 => '');
1201 END IF;
1202 l_RETURN:='N'; /*Bug 5747269*/
1203 END IF; /* Function security */
1204 END IF; /* count >0 */
1205 END IF; /* curr and incoming value check*/
1206
1207 /* 5684469 changed and to OR in the p_billing_sequence */
1208
1209 IF ((p_billing_sequence <=0 or p_billing_sequence > 99)and
1210 p_billing_sequence <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)then
1211
1212 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1213 THEN
1214 pa_interface_utils_pub.map_new_amg_msg
1215 ( p_old_message_code => 'PA_INVALID_BILL_SEQ'
1216 ,p_msg_attribute => 'CHANGE'
1217 ,p_resize_flag => 'N'
1218 ,p_msg_context => 'AGREEMENT'
1219 ,p_attribute1 => p_pm_agreement_reference
1220 ,p_attribute2 => ''
1221 ,p_attribute3 => ''
1222 ,p_attribute4 => ''
1223 ,p_attribute5 => '');
1224 END IF;
1225 l_RETURN:='N';
1226
1227 END IF;
1228
1229 -- Check Revenue Limit
1230 IF (UPPER(p_revenue_limit_flag)='Y') THEN /* IF condition added for bug 2862024 */
1231
1232 IF pa_agreement_utils.check_revenue_limit
1233 (p_agreement_id => p_agreement_id)= 'N'
1234 THEN
1235 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1236 THEN
1237 pa_interface_utils_pub.map_new_amg_msg
1238 ( p_old_message_code => 'PA_INVALID_REVENUE_LIMIT'
1239 ,p_msg_attribute => 'CHANGE'
1240 ,p_resize_flag => 'N'
1241 ,p_msg_context => 'AGREEMENT'
1242 ,p_attribute1 => p_pm_agreement_reference
1243 ,p_attribute2 => ''
1244 ,p_attribute3 => ''
1245 ,p_attribute4 => ''
1246 ,p_attribute5 => '');
1247 END IF;
1248 l_RETURN:='N';
1249 END IF;
1250
1251 END IF;/* 2862024 */
1252 -- Check Fund Allocated
1253 /*IF pa_agreement_utils.check_fund_allocated
1254 (p_agreement_id => p_agreement_id)= 'N' Federal */
1255 /* Commeted for bug 6853994
1256 IF (l_agm_amt < l_tot_fund)-- bug 5685032 changed to < from <=
1257 THEN
1258 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1259 THEN
1260 pa_interface_utils_pub.map_new_amg_msg
1261 ( p_old_message_code => 'PA_INVD_FUND_ALLOC'
1262 ,p_msg_attribute => 'CHANGE'
1263 ,p_resize_flag => 'N'
1264 ,p_msg_context => 'AGREEMENT'
1265 ,p_attribute1 => p_pm_agreement_reference
1266 ,p_attribute2 => ''
1267 ,p_attribute3 => ''
1268 ,p_attribute4 => ''
1269 ,p_attribute5 => '');
1270 END IF;
1271 l_RETURN:='N';
1272 END IF; */
1273
1274 /* MCB2 code begins */
1275 -- valid owning organization id
1276 --dbms_output.put_line('Check for valid owning orgn id ');
1277 /* IF p_owning_organization_id is not null then Commenetd for Bug 6602451 and added the below IF condition*/
1278
1279 IF (p_owning_organization_id IS NOT NULL AND p_owning_organization_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1280 AND p_owning_organization_id <> l_agrcur.owning_organization_id) THEN /* Bug 6602451 */
1281 IF pa_agreement_utils.check_valid_owning_orgn_id
1282 (p_owning_organization_id => p_owning_organization_id
1283 ) = 'N'
1284 THEN
1285 --dbms_output.put_line('Invalid owning organization_id ');
1286 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1287 THEN
1288 pa_interface_utils_pub.map_new_amg_msg
1289 ( p_old_message_code => 'PA_OWNING_ORGN_ID_INVALID'
1290 ,p_msg_attribute => 'CHANGE'
1291 ,p_resize_flag => 'N'
1292 ,p_msg_context => 'AGREEMENT'
1293 ,p_attribute1 => p_pm_agreement_reference
1294 ,p_attribute2 => ''
1295 ,p_attribute3 => ''
1296 ,p_attribute4 => ''
1297 ,p_attribute5 => '');
1298 END IF;
1299 l_RETURN:='N';
1300 END IF;
1301 /* Added for bug 6602451 */
1302 ELSE
1303 p_owning_organization_id := l_agrcur.owning_organization_id;
1304 END IF;
1305
1306 /* IF p_agreement_currency_code is not null then Commenetd for Bug 6602451 and added the below IF condition*/
1307
1308 IF (p_agreement_currency_code IS NOT NULL AND p_agreement_currency_code <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1309 AND p_agreement_currency_code <> l_agrcur.agreement_currency_code) THEN /* Bug 6602451 */
1310 -- Start of Bugfix 2442176
1311 IF pa_agreement_utils.check_valid_agr_curr_code
1312 (p_agreement_currency_code => p_agreement_currency_code
1313 ) ='Y' THEN
1314 IF l_return = 'Y' THEN
1315 BEGIN
1316 SELECT 'N' INTO l_return
1317 FROM PA_AGREEMENTS_ALL
1318 WHERE AGREEMENT_ID = p_agreement_id
1319 AND (AGREEMENT_CURRENCY_CODE <> p_agreement_currency_code
1320 AND EXISTS ( SELECT *
1321 FROM PA_PROJECT_FUNDINGS
1322 WHERE AGREEMENT_ID= p_agreement_id
1323 AND nvl(ALLOCATED_AMOUNT,0) <> 0));
1324 --dbms_output.put_line('Invalid agreement_currency_code ');
1325 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1326 THEN
1327 pa_interface_utils_pub.map_new_amg_msg
1328 ( p_old_message_code => 'PA_CURR_NOT_VALID'
1329 ,p_msg_attribute => 'CHANGE'
1330 ,p_resize_flag => 'N'
1331 ,p_msg_context => 'AGREEMENT'
1332 ,p_attribute1 => p_pm_agreement_reference
1333 ,p_attribute2 => ''
1334 ,p_attribute3 => ''
1335 ,p_attribute4 => ''
1336 ,p_attribute5 => '');
1337 END IF;
1338 EXCEPTION
1339 WHEN NO_DATA_FOUND THEN
1340 l_return :='Y';
1341 END;
1342 end if;
1343 ELSE
1344 /* Comment for bug 2442176 : IF pa_agreement_utils.check_valid_agr_curr_code
1345 (p_agreement_currency_code => p_agreement_currency_code
1346 ) = 'N'
1347 THEN */
1348 -- End of Changes for Bug 2442176
1349
1350 --dbms_output.put_line('Invalid agreement_currency_code ');
1351 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1352 THEN
1353 pa_interface_utils_pub.map_new_amg_msg
1354 ( p_old_message_code => 'PA_CURR_NOT_VALID'
1355 ,p_msg_attribute => 'CHANGE'
1356 ,p_resize_flag => 'N'
1357 ,p_msg_context => 'AGREEMENT'
1358 ,p_attribute1 => p_pm_agreement_reference
1359 ,p_attribute2 => ''
1360 ,p_attribute3 => ''
1361 ,p_attribute4 => ''
1362 ,p_attribute5 => '');
1363 END IF;
1364 l_RETURN:='N';
1365 END IF;
1366 /* Added for bug 6602451 */
1367 ELSE
1368 p_agreement_currency_code := l_agrcur.agreement_currency_code;
1369 END IF;
1370
1371 -- invoice Limit Flag
1372 --dbms_output.put_line('Check for valid invoice Limit Flag');
1373 /* IF p_invoice_limit_flag is not null THEN Commenetd for Bug 6602451 and added the below IF condition*/
1374
1375 IF (p_invoice_limit_flag IS NOT NULL AND p_invoice_limit_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1376 AND p_invoice_limit_flag <> l_agrcur.invoice_limit_flag) THEN /* Bug 6602451 */
1377 IF pa_agreement_pvt.check_yes_no
1378 (p_val => p_invoice_limit_flag) = 'N' THEN
1379
1380 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1381 --dbms_output.put_line('Invalid invoice Limit Flag');
1382 pa_interface_utils_pub.map_new_amg_msg
1383 ( p_old_message_code => 'PA_INVALID_INV_LT_FLG'
1384 ,p_msg_attribute => 'CHANGE'
1385 ,p_resize_flag => 'N'
1386 ,p_msg_context => 'AGREEMENT'
1387 ,p_attribute1 => p_pm_agreement_reference
1388 ,p_attribute2 => ''
1389 ,p_attribute3 => ''
1390 ,p_attribute4 => ''
1391 ,p_attribute5 => '');
1392 END IF;
1393 l_RETURN:='N';
1394
1395 ELSIF UPPER(p_invoice_limit_flag)='Y' THEN /* Added for bug 2862024 */
1396 IF pa_agreement_utils.check_invoice_limit
1397 (p_agreement_id => p_agreement_id)= 'N' THEN
1398
1399 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1400 pa_interface_utils_pub.map_new_amg_msg
1401 ( p_old_message_code => 'PA_INVALID_INVOICE_LIMIT'
1402 ,p_msg_attribute => 'CHANGE'
1403 ,p_resize_flag => 'N'
1404 ,p_msg_context => 'AGREEMENT'
1405 ,p_attribute1 => p_pm_agreement_reference
1406 ,p_attribute2 => ''
1407 ,p_attribute3 => ''
1408 ,p_attribute4 => ''
1409 ,p_attribute5 => '');
1410 END IF;
1411 l_RETURN:='N';
1412 END IF;
1413 END IF; /* 2862024 */
1414 /* Added for bug 6602451 */
1415 ELSE
1416 p_invoice_limit_flag := l_agrcur.invoice_limit_flag;
1417 END IF;
1418 END LOOP;
1419 /* MCB2 code ends */
1420
1421 --dbms_output.put_line('Check Update Agreement OK retuning :' ||l_return);
1422 RETURN(l_RETURN);
1423
1424 END check_update_agreement_ok;
1425
1426 -- ============================================================================
1427 --
1428 --Name: check_delete_agreement_ok
1429 --Type: Function
1430 --Description: This function can be used to check IF it is OK to delete the
1431 -- agreement.
1432 --
1433 --Called subprograms:
1434 -- pa_project_utils.check_delete_agreement_ok
1435 --
1436 --
1437 --History:
1438 -- 25-MAR-2000 Rakesh Raghavan Created.
1439 -- -----------------------------------------------------------------------------
1440
1441 FUNCTION check_delete_agreement_ok
1442 (p_agreement_id IN NUMBER
1443 ,p_pm_agreement_reference IN VARCHAR2)
1444 RETURN VARCHAR2
1445 IS
1446
1447 -- LOCAL CURSORS
1448
1449 CURSOR l_funding_id_csr(p_agreement_id NUMBER)
1450 IS
1451 SELECT project_funding_id,pm_funding_reference,agreement_id, allocated_amount
1452 FROM pa_project_fundings f
1453 WHERE f.agreement_id = p_agreement_id;
1454
1455 -- LOCAL VARIABLES
1456 l_RETURN VARCHAR2(1) :='Y';
1457 l_funding_id_rec l_funding_id_csr%ROWTYPE;
1458 l_count NUMBER; /*Federal*/
1459 BEGIN
1460 /*if(check_delete_agreement_ok_fp(p_agreement_id)='N')then
1461 return ('N');
1462 else*/
1463
1464 OPEN l_funding_id_csr( p_agreement_id);
1465 LOOP
1466 FETCH l_funding_id_csr INTO l_funding_id_rec;
1467 IF l_funding_id_csr%NOTFOUND
1468 THEN
1469 EXIT;
1470 ELSE
1471 l_RETURN:= pa_agreement_pvt.check_delete_funding_ok
1472 (p_agreement_id => l_funding_id_rec.agreement_id
1473 ,p_funding_id => l_funding_id_rec.project_funding_id
1474 ,p_pm_funding_reference => l_funding_id_rec.pm_funding_reference);
1475 IF l_RETURN = 'N'
1476 THEN
1477 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1478 THEN
1479 pa_interface_utils_pub.map_new_amg_msg
1480 ( p_old_message_code => 'PA_CANT_DELETE_AGMT'
1481 ,p_msg_attribute => 'CHANGE'
1482 ,p_resize_flag => 'Y'
1483 ,p_msg_context => 'AGREEMENT'
1484 ,p_attribute1 => p_pm_agreement_reference
1485 ,p_attribute2 => ''
1486 ,p_attribute3 => ''
1487 ,p_attribute4 => ''
1488 ,p_attribute5 => '');
1489 END IF;
1490 EXIT;
1491 END IF;
1492 END IF;
1493 END LOOP;
1494 CLOSE l_funding_id_csr;
1495
1496
1497 /*Federal */
1498 SELECT count(*)
1499 INTO l_count
1500 FROM pa_agreements_all
1501 WHERE agreement_id = p_agreement_id
1502 and advance_amount >0;
1503
1504
1505 IF (l_count>0) THEN
1506 l_RETURN:='N';
1507 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1508 THEN
1509 pa_interface_utils_pub.map_new_amg_msg
1510 ( p_old_message_code => 'PA_RECP_DELETE_AGMT'
1511 ,p_msg_attribute => 'CHANGE'
1512 ,p_resize_flag => 'N' /*Bug 5909864 */
1513 ,p_msg_context => 'AGREEMENT'
1514 ,p_attribute1 => p_pm_agreement_reference
1515 ,p_attribute2 => ''
1516 ,p_attribute3 => ''
1517 ,p_attribute4 => ''
1518 ,p_attribute5 => '');
1519 END IF;
1520 END IF;
1521 /*
1522 select count(*)
1523 into l_count
1524 from pa_events e,
1525 pa_project_fundings f
1526 where e.project_id = f.project_id
1527 and e.agreement_id = f.agreement_id
1528 and f.agreement_id = p_agreement_id;
1529
1530 IF (l_count>0) THEN
1531 l_RETURN:='N';
1532 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1533 THEN
1534 pa_interface_utils_pub.map_new_amg_msg
1535 ( p_old_message_code => 'PA_EVENT_AGMT_DELETE'
1536 ,p_msg_attribute => 'CHANGE'
1537 ,p_resize_flag => 'Y'
1538 ,p_msg_context => 'AGREEMENT'
1539 ,p_attribute1 => p_pm_agreement_reference
1540 ,p_attribute2 => ''
1541 ,p_attribute3 => ''
1542 ,p_attribute4 => ''
1543 ,p_attribute5 => '');
1544 END IF;
1545 END IF;*/
1546
1547 RETURN(l_RETURN);
1548 --end if;
1549 END check_delete_agreement_ok;
1550
1551 -- ===========================================================================
1552 --
1553 --Name: check_funding_category
1554 --Type: Function
1555 --Description: This function can be used to check if the
1556 -- funding category is valid.
1557 --
1558 --Called subprograms:
1559 --
1560 --
1561 --
1562 --History:
1563 -- 11-JUN-2002 Raji Created - Bug 2403652.
1564 -- 26-JUL-02 Raji Modified for Bug 2483081
1565 -- ---------------------------------------------------------------------------
1566
1567 FUNCTION check_funding_category
1568 ( p_project_id IN NUMBER
1569 ,p_task_id IN NUMBER
1570 ,p_agreement_id IN NUMBER
1571 ,p_pm_funding_reference IN VARCHAR2
1572 ,p_funding_category IN VARCHAR2)
1573
1574 RETURN VARCHAR2
1575 IS
1576
1577 -- Local Variables
1578 l_funding_category VARCHAR2(30);
1579 l_return VARCHAR2(1) := 'Y';
1580
1581 BEGIN
1582 /* code change for bug 2868818*/
1583
1584 IF upper(p_funding_category) = 'REVALUATION'
1585 THEN
1586 pa_interface_utils_pub.map_new_amg_msg
1587 ( p_old_message_code => 'PA_INV_FUND_CAT'
1588 ,p_msg_attribute => 'CHANGE'
1589 ,p_resize_flag => 'N'
1590 ,p_msg_context => 'FUNDING'
1591 ,p_attribute1 => ''
1592 ,p_attribute2 => 'p_pm_funding_reference'
1593 ,p_attribute3 => ''
1594 ,p_attribute4 => ''
1595 ,p_attribute5 => '');
1596
1597 l_RETURN := 'N';
1598 RETURN(l_RETURN);
1599
1600 END IF;
1601
1602 /* end code change for bug 2868818*/
1603
1604 select meaning /* 2483081 */
1605 into l_funding_category
1606 from pa_lookups
1607 where lookup_code= p_funding_category
1608 and lookup_type= 'FUNDING CATEGORY TYPE';
1609
1610 -- Check if valid funding category
1611
1612 RETURN(l_RETURN);
1613 EXCEPTION
1614 WHEN NO_DATA_FOUND THEN
1615 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1616 THEN
1617 pa_interface_utils_pub.map_new_amg_msg
1618 ( p_old_message_code => 'PA_INV_FUND_CAT'
1619 ,p_msg_attribute => 'CHANGE'
1620 ,p_resize_flag => 'N'
1621 ,p_msg_context => 'FUNDING'
1622 ,p_attribute1 => ''
1623 ,p_attribute2 => p_pm_funding_reference
1624 ,p_attribute3 => ''
1625 ,p_attribute4 => ''
1626 ,p_attribute5 => '');
1627 END IF;
1628 l_RETURN := 'N';
1629 RETURN(l_RETURN);
1630 WHEN OTHERS THEN
1631 l_RETURN := 'N';
1632 RETURN(l_RETURN);
1633 END check_funding_category;
1634
1635
1636 -- ============================================================================
1637 --
1638 --Name: check_add_funding_ok
1639 --Type: Function
1640 --Description: This function can be used to check IF it is OK to add funding.
1641 --
1642 --Called subprograms:
1643 --
1644 --
1645 --
1646 --History:
1647 -- 25-MAR-2000 Rakesh Raghavan Created.
1648 -- 10-SEP-2001 Srividya Modified.
1649 -- Call to check valid project includes project_id also
1650 -- ADded mcb2 code
1651 -- 03-SEP-2008 jngeorge Bug 6600563: Added parameter p_calling_context
1652 -- -----------------------------------------------------------------------------
1653
1654 FUNCTION check_add_funding_ok
1655 (p_project_id IN NUMBER
1656 ,p_task_id IN NUMBER
1657 ,p_agreement_id IN NUMBER
1658 ,p_pm_funding_reference IN VARCHAR2
1659 ,p_funding_amt IN NUMBER
1660 ,p_customer_id IN NUMBER
1661 ,p_project_rate_type IN VARCHAR2 default null
1662 ,p_project_rate_date IN DATE default null
1663 ,p_project_exchange_rate IN NUMBER default null
1664 ,p_projfunc_rate_type IN VARCHAR2 default null
1665 ,p_projfunc_rate_date IN DATE default null
1666 ,p_projfunc_exchange_rate IN NUMBER default null -- Bug 6600563
1667 ,p_calling_context IN VARCHAR2 default null)
1668 RETURN VARCHAR2
1669 IS
1670 -- added to validate the funding amount
1671 -- LOCAL VARIABLES
1672
1673 l_RETURN VARCHAR2(1):='Y';
1674 l_funding_lvl VARCHAR2(1);
1675 l_valid_fund_amt VARCHAR2(1);
1676 l_rate_type VARCHAR2(1) := 'Y';
1677 l_old_message_code VARCHAR2(35);
1678 l_plan_type_id number;
1679 l_msg_count number;
1680 l_msg_data varchar2(2000);
1681 l_return_status varchar2(2000);
1682
1683 l_Enable_Top_Task_Cust_Flag VARCHAR2(1);
1684 l_Exist_Flag VARCHAR2(1);
1685
1686 l_Allowable_Funding_Level_Code pa_project_types_all.ALLOWABLE_FUNDING_LEVEL_CODE%type; /*Added for bug 3614374*/
1687
1688 l_Inv_Method_Override_Flag VARCHAR2(1); /*Added for Bug 5550709*/
1689
1690 BEGIN
1691 --dbms_output.put_line('Inside: PA_AGREEMENT_PVT.CHECK_ADD_FUNDING_OK');
1692
1693 -- VALIDATE THE INCOMING PARAMETERS
1694
1695 -- Check Valid Project
1696
1697 -- NOT REQUIRED
1698 --dbms_output.put_line('Check valid project');
1699 IF ( ( pa_agreement_utils.check_valid_project
1700 (p_customer_id => p_customer_id,
1701 p_project_id => p_project_id,
1702 p_agreement_id => p_agreement_id) = 'N' )/*Federal*/
1703 OR
1704 ( pa_agreement_utils.check_proj_agr_fund_ok
1705 (p_agreement_id => p_agreement_id,
1706 p_project_id => p_project_id) = 'N' ) /* added OR condition Bug2756047 */
1707 )
1708 THEN
1709 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1710 THEN
1711 pa_interface_utils_pub.map_new_amg_msg
1712 ( p_old_message_code => 'PA_INVALID_PROJECT'
1713 ,p_msg_attribute => 'CHANGE'
1714 ,p_resize_flag => 'N'
1715 ,p_msg_context => 'FUNDING'
1716 ,p_attribute1 => p_pm_funding_reference
1717 ,p_attribute2 => ''
1718 ,p_attribute3 => ''
1719 ,p_attribute4 => ''
1720 ,p_attribute5 => '');
1721 END IF;
1722 l_RETURN:='N';
1723 END IF;
1724 --dbms_output.put_line('Check valid customer');
1725 IF pa_agreement_utils.check_valid_customer
1726 (p_customer_id => p_customer_id) = 'N'
1727 THEN
1728 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1729 THEN
1730 pa_interface_utils_pub.map_new_amg_msg
1731 ( p_old_message_code => 'PA_INVALID_CUSTOMER'
1732 ,p_msg_attribute => 'CHANGE'
1733 ,p_resize_flag => 'N'
1734 ,p_msg_context => 'FUNDING'
1735 ,p_attribute1 => p_pm_funding_reference
1736 ,p_attribute2 => ''
1737 ,p_attribute3 => ''
1738 ,p_attribute4 => ''
1739 ,p_attribute5 => '');
1740 END IF;
1741 l_RETURN:='N';
1742 END IF;
1743
1744
1745 -- Check Valid Task
1746 IF p_task_id IS NOT NULL and p_task_id <> FND_API.G_MISS_NUM THEN
1747 --dbms_output.put_line('Check valid task');
1748 IF pa_agreement_utils.check_valid_task
1749 (p_project_id => p_project_id
1750 ,p_task_id => p_task_id) = 'N'
1751 THEN
1752 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1753 THEN
1754 --dbms_output.put_line('Invalid Task');
1755 pa_interface_utils_pub.map_new_amg_msg
1756 ( p_old_message_code => 'PA_INVD_TASK'
1757 ,p_msg_attribute => 'CHANGE'
1758 ,p_resize_flag => 'N'
1759 ,p_msg_context => 'FUNDING'
1760 ,p_attribute1 => ''
1761 ,p_attribute2 => p_pm_funding_reference
1762 ,p_attribute3 => ''
1763 ,p_attribute4 => ''
1764 ,p_attribute5 => '');
1765 END IF;
1766 l_RETURN:='N';
1767 END IF;
1768 END IF;
1769
1770 -- Following changes are made for FP_M : Top Task customer changes
1771 l_Enable_Top_Task_Cust_Flag := PA_Billing_Pub.Get_Top_Task_Customer_Flag (
1772 P_Project_ID => P_Project_ID );
1773
1774 /* Added the code for bug 5550709. Task level funding is required if customer at
1775 top task or invoice method by top task is enabled. */
1776 l_Inv_Method_Override_Flag := PA_Billing_Pub.Get_Inv_Method_Override_Flag (
1777 P_Project_ID => P_Project_ID );
1778
1779 IF p_task_id IS NULL and (l_Enable_Top_Task_Cust_Flag = 'Y' or
1780 l_Inv_Method_Override_Flag = 'Y') THEN
1781 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1782 THEN
1783 --dbms_output.put_line('Invalid Funding level');
1784 pa_interface_utils_pub.map_new_amg_msg
1785 ( p_old_message_code => 'PA_TASK_LEVEL_FUND_REQD'
1786 ,p_msg_attribute => 'CHANGE'
1787 ,p_resize_flag => 'N'
1788 ,p_msg_context => 'FUNDING'
1789 ,p_attribute1 => ''
1790 ,p_attribute2 => p_pm_funding_reference
1791 ,p_attribute3 => ''
1792 ,p_attribute4 => ''
1793 ,p_attribute5 => '');
1794 END IF;
1795 RETURN 'N';
1796 END IF;
1797
1798 -- Check for Top Task Customer existence if l_Enable_Top_Task_Cust_Flag = 'Y'
1799 --
1800 -- If Project is implemented with Top Task Customer enabled then make sure
1801 -- that the funding line's top task is associated to the customer which is same
1802 -- as that of agreement's customer.
1803 -- If not, then raise the error.
1804 IF l_Enable_Top_Task_Cust_Flag = 'Y'
1805 THEN
1806 begin
1807 Select 1 into l_Exist_Flag
1808 From PA_Tasks
1809 Where Project_ID = P_Project_ID
1810 AND Task_ID = P_Task_ID
1811 AND Customer_ID = P_Customer_ID
1812 AND Rownum < 2;
1813 Exception when no_data_found then
1814 PA_Interface_Utils_Pub.Map_New_AMG_Msg (
1815 P_Old_Message_Code => 'PA_INVALID_TASK_CUSTOMER',
1816 P_Msg_Attribute => 'CHANGE',
1817 P_Msg_Context => 'FUNDING',
1818 p_resize_flag => 'N', -- Fix for bug 3523077
1819 P_Attribute1 => '',
1820 P_Attribute2 => p_pm_funding_reference,
1821 P_Attribute3 => '',
1822 P_Attribute4 => '',
1823 P_Attribute5 => ''
1824 );
1825 Return 'N';
1826 end;
1827 END IF;
1828 -- End of changes made for FP_M : Top Task customer changes
1829
1830 -- Check Funding Level
1831 --dbms_output.put_line('Check Funding Level');
1832
1833 /* Added for bug 3614374*/
1834 /* The code validates the funding level when user tries to create an agreement
1835 * and add funding to the project.*/
1836
1837 Select ALLOWABLE_FUNDING_LEVEL_CODE
1838 Into l_Allowable_Funding_Level_Code
1839 From pa_project_types_all pt, pa_projects_all p
1840 Where p.project_id=p_project_id
1841 And p.project_type=pt.project_type
1842 AND nvl(p.org_id,-99) = nvl(pt.org_id,-99);
1843
1844 IF (p_task_id IS NULL and l_Allowable_Funding_Level_Code='T')
1845 OR (p_task_id IS NOT NULL and l_Allowable_Funding_Level_Code = 'P')
1846 THEN
1847 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1848 THEN
1849 pa_interface_utils_pub.map_new_amg_msg
1850 ( p_old_message_code => 'PA_INVD_FUNDING_LVL'
1851 ,p_msg_attribute => 'CHANGE'
1852 ,p_resize_flag => 'N'
1853 ,p_msg_context => 'FUNDING'
1854 ,p_attribute1 => ''
1855 ,p_attribute2 => p_pm_funding_reference
1856 ,p_attribute3 => ''
1857 ,p_attribute4 => ''
1858 ,p_attribute5 => '');
1859 END IF;
1860 l_RETURN:='N';
1861 END IF;
1862
1863 /* End of changes for bug 3614374*/
1864
1865
1866 /*added for finplan impact on billing*/
1867 /*pa_fin_plan_utils.Get_Appr_Rev_Plan_Type_Info (
1868 p_project_id =>p_project_id,
1869 x_plan_type_id => l_plan_type_id,
1870 x_return_status => l_return_status,
1871 x_msg_count => l_msg_count,
1872 x_msg_data => l_msg_data
1873 );
1874 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1875 THEN
1876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1877
1878 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1879 THEN
1880 RAISE FND_API.G_EXC_ERROR;
1881 END IF;
1882
1883 If l_plan_type_id is not null then
1884 l_funding_lvl := pa_agreement_utils.check_proj_task_lvl_funding_fp
1885 (p_project_id => p_project_id
1886 ,p_task_id => p_task_id
1887 ,p_agreement_id => p_agreement_id);
1888 else commented for bug 2729975*/
1889 l_funding_lvl := pa_agreement_utils.check_proj_task_lvl_funding
1890 (p_project_id => p_project_id
1891 ,p_task_id => p_task_id
1892 ,p_agreement_id => p_agreement_id);
1893 /*end if; commented for bug 2729975*/
1894
1895 IF l_funding_lvl = 'A'
1896 THEN
1897 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1898 THEN
1899 --dbms_output.put_line('1.Invalid Funding level');
1900 pa_interface_utils_pub.map_new_amg_msg
1901 ( p_old_message_code => 'PA_PROJ_FUND_NO_TASK_TRANS'
1902 ,p_msg_attribute => 'CHANGE'
1903 ,p_resize_flag => 'N'
1904 ,p_msg_context => 'FUNDING'
1905 ,p_attribute1 => ''
1906 ,p_attribute2 => p_pm_funding_reference
1907 ,p_attribute3 => ''
1908 ,p_attribute4 => ''
1909 ,p_attribute5 => '');
1910 END IF;
1911 l_RETURN:='N';
1912
1913 ELSIF l_funding_lvl = 'P'
1914 THEN
1915 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1916 THEN
1917 --dbms_output.put_line('Invalid Funding level PA_BU_PROJECT_ALLOC_ONLY');
1918 pa_interface_utils_pub.map_new_amg_msg
1919 ( p_old_message_code => 'PA_BU_PROJECT_ALLOC_ONLY'
1920 ,p_msg_attribute => 'CHANGE'
1921 ,p_resize_flag => 'N'
1922 ,p_msg_context => 'FUNDING'
1923 ,p_attribute1 => ''
1924 ,p_attribute2 => p_pm_funding_reference
1925 ,p_attribute3 => ''
1926 ,p_attribute4 => ''
1927 ,p_attribute5 => '');
1928 END IF;
1929 l_RETURN:='N';
1930
1931 ELSIF l_funding_lvl = 'T'
1932 THEN
1933 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1934 THEN
1935 --dbms_output.put_line('3. Invalid Funding level');
1936 pa_interface_utils_pub.map_new_amg_msg
1937 ( p_old_message_code => 'PA_BU_TASK_ALLOC_ONLY'
1938 ,p_msg_attribute => 'CHANGE'
1939 ,p_resize_flag => 'N'
1940 ,p_msg_context => 'FUNDING'
1941 ,p_attribute1 => ''
1942 ,p_attribute2 => p_pm_funding_reference
1943 ,p_attribute3 => ''
1944 ,p_attribute4 => ''
1945 ,p_attribute5 => '');
1946 END IF;
1947 l_RETURN:='N';
1948
1949 ELSIF l_funding_lvl = 'B'
1950 THEN
1951 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1952 THEN
1953 --dbms_output.put_line('4. Invalid Funding level');
1954 pa_interface_utils_pub.map_new_amg_msg
1955 ( p_old_message_code => 'PA_TASK_FUND_NO_PROJ_TRANS'
1956 ,p_msg_attribute => 'CHANGE'
1957 ,p_resize_flag => 'N'
1958 ,p_msg_context => 'FUNDING'
1959 ,p_attribute1 => ''
1960 ,p_attribute2 => p_pm_funding_reference
1961 ,p_attribute3 => ''
1962 ,p_attribute4 => ''
1963 ,p_attribute5 => '');
1964 END IF;
1965 l_RETURN:='N';
1966 END IF;
1967 /*added for finplan impact on billing bug 2729975*/
1968 pa_fin_plan_utils.Get_Appr_Rev_Plan_Type_Info (
1969 p_project_id =>p_project_id,
1970 x_plan_type_id => l_plan_type_id,
1971 x_return_status => l_return_status,
1972 x_msg_count => l_msg_count,
1973 x_msg_data => l_msg_data
1974 );
1975 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1976 THEN
1977 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1978
1979 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1980 THEN
1981 RAISE FND_API.G_EXC_ERROR;
1982 END IF;
1983
1984 If l_plan_type_id is not null then
1985 l_funding_lvl := pa_agreement_utils.check_proj_task_lvl_funding_fp
1986 (p_project_id => p_project_id
1987 ,p_task_id => p_task_id
1988 ,p_agreement_id => p_agreement_id);
1989
1990 IF l_funding_lvl='A' then
1991
1992 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1993 THEN
1994 --dbms_output.put_line('4. Invalid Funding level');
1995 pa_interface_utils_pub.map_new_amg_msg
1996 ( p_old_message_code => 'PA_FP_CHK_FUNDING_LVL'
1997 ,p_msg_attribute => 'CHANGE'
1998 ,p_resize_flag => 'N'
1999 ,p_msg_context => 'FUNDING'
2000 ,p_attribute1 => ''
2001 ,p_attribute2 => p_pm_funding_reference
2002 ,p_attribute3 => ''
2003 ,p_attribute4 => ''
2004 ,p_attribute5 => '');
2005 END IF;
2006 l_RETURN:='N';
2007 END IF;
2008 END IF;
2009 /*End of change for fin plan impact on billing*/
2010 -- Bug 6600563. Call to this API will be made only when
2011 -- p_calling_context is NULL. please refer to bug for details
2012 IF p_calling_context IS NULL THEN
2013 -- added to validate the funding amount
2014 l_valid_fund_amt := pa_agreement_pvt.validate_funding_amt(p_funding_amt => p_funding_amt
2015 ,p_agreement_id => p_agreement_id
2016 ,p_operation_flag => 'A'
2017 ,p_pm_funding_reference => p_pm_funding_reference
2018 ,p_funding_id => NULL ) ;
2019 --dbms_output.put_line('INSIDE PVT value of l_valid_funding_amt : =>'|| l_valid_fund_amt);
2020
2021 IF l_valid_fund_amt in ('Z','M') THEN
2022 --dbms_output.put_line('INSIDE PVTmessage should popup');
2023 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2024 THEN
2025 pa_interface_utils_pub.map_new_amg_msg
2026 ( p_old_message_code => 'PA_INVD_FUND_ALLOC'
2027 ,p_msg_attribute => 'CHANGE'
2028 ,p_resize_flag => 'N'
2029 ,p_msg_context => 'FUNDING'
2030 ,p_attribute1 => p_pm_funding_reference
2031 ,p_attribute2 => ''
2032 ,p_attribute3 => ''
2033 ,p_attribute4 => ''
2034 ,p_attribute5 => '');
2035 END IF;
2036 l_RETURN := 'N';
2037 END IF;
2038 END IF; -- Bug 6600563.
2039
2040 --dbms_output.put_line('Return:'||l_return);
2041
2042 RETURN(l_RETURN);
2043 END check_add_funding_ok;
2044
2045 -- ============================================================================
2046 --
2047 --Name: check_update_funding_ok
2048 --Type: Function
2049 --Description: This function can be used to check IF it is OK to update funding.
2050 --
2051 --Called subprograms:
2052 -- pa_project_utils.update_funding
2053 --
2054 --
2055 --History:
2056 -- 25-MAR-2000 Rakesh Raghavan Created.
2057 -- 10-SEP-2001 Srividya Modified.
2058 -- Call to check valid project includes project_id also
2059 -- ADded mcb2 code
2060 -- -----------------------------------------------------------------------------
2061
2062 FUNCTION check_update_funding_ok
2063 (p_project_id IN NUMBER
2064 ,p_task_id IN NUMBER
2065 ,p_agreement_id IN NUMBER
2066 ,p_customer_id IN NUMBER
2067 ,p_pm_funding_reference IN VARCHAR2
2068 ,p_funding_id IN NUMBER
2069 ,p_funding_amt IN NUMBER
2070 ,p_project_rate_type IN VARCHAR2 default null
2071 ,p_project_rate_date IN DATE default null
2072 ,p_project_exchange_rate IN NUMBER default null
2073 ,p_projfunc_rate_type IN VARCHAR2 default null
2074 ,p_projfunc_rate_date IN DATE default null
2075 ,p_projfunc_exchange_rate IN NUMBER default null)
2076
2077 RETURN VARCHAR2
2078 IS
2079 -- LOCAL VARIABLES
2080 l_RETURN VARCHAR2(1):='Y';
2081 l_check_proj_task_lvl_funding VARCHAR2(1) := Null;
2082 l_valid_fund_amt VARCHAR2(1):= 'Y';
2083 l_rate_type VARCHAR2(1) := 'Y';
2084 l_old_message_code VARCHAR2(35);
2085 l_plan_type_id number;
2086 l_return_status varchar2(2000);
2087 l_msg_count number;
2088 l_msg_data varchar2(2000);
2089
2090 l_Enable_Top_Task_Cust_Flag VARCHAR2(1);
2091 l_Exist_Flag VARCHAR2(1);
2092
2093 l_Inv_Method_Override_Flag VARCHAR2(1); /*Added for Bug 5550709 */
2094
2095 BEGIN
2096 --dbms_output.put_line('Inside: PA_AGREEMENT_PVT.CHECK_UPDATE_FUNDING_OK');
2097 --dbms_output.put_line('p_pm_funding_reference: '||nvl(p_pm_funding_reference,'NULL'));
2098 -- VALIDATE THE INCOMING PARAMETERS
2099
2100 -- Check Valid Project
2101
2102 IF pa_agreement_utils.check_budget_type (
2103 p_funding_id => p_funding_id) = 'N' THEN
2104
2105 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2106 THEN
2107 pa_interface_utils_pub.map_new_amg_msg
2108 ( p_old_message_code => 'PA_FUNDING_BASELINED'
2109 ,p_msg_attribute => 'CHANGE'
2110 ,p_resize_flag => 'N'
2111 ,p_msg_context => 'FUNDING'
2112 ,p_attribute1 => ''
2113 ,p_attribute2 => p_pm_funding_reference
2114 ,p_attribute3 => ''
2115 ,p_attribute4 => ''
2116 ,p_attribute5 => '');
2117 END IF;
2118 l_RETURN:='N';
2119 RETURN(l_RETURN);
2120 END IF;
2121
2122 /*For bug 3066711 Changed the message code */
2123
2124 /*Federal*/
2125 IF pa_agreement_utils.check_valid_project
2126 (p_customer_id => p_customer_id,
2127 p_project_id => p_project_id,
2128 p_agreement_id => p_agreement_id) = 'N'
2129 THEN
2130 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2131 THEN
2132 pa_interface_utils_pub.map_new_amg_msg
2133 ( p_old_message_code => 'PA_INVALID_PROJECT_ID'
2134 ,p_msg_attribute => 'CHANGE'
2135 ,p_resize_flag => 'N'
2136 ,p_msg_context => 'FUNDING'
2137 ,p_attribute1 => ''
2138 ,p_attribute2 => p_pm_funding_reference
2139 ,p_attribute3 => ''
2140 ,p_attribute4 => ''
2141 ,p_attribute5 => '');
2142 END IF;
2143 l_RETURN:='N';
2144 RETURN(l_RETURN);
2145 END IF;
2146
2147 IF pa_agreement_utils.check_valid_customer
2148 (p_customer_id => p_customer_id) = 'N'
2149 THEN
2150 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2151 THEN
2152 pa_interface_utils_pub.map_new_amg_msg
2153 ( p_old_message_code => 'PA_INVALID_CUST'
2154 ,p_msg_attribute => 'CHANGE'
2155 ,p_resize_flag => 'N'
2156 ,p_msg_context => 'FUNDING'
2157 ,p_attribute1 => ''
2158 ,p_attribute2 => p_pm_funding_reference
2159 ,p_attribute3 => ''
2160 ,p_attribute4 => ''
2161 ,p_attribute5 => '');
2162 END IF;
2163 l_RETURN:='N';
2164 RETURN(l_RETURN);
2165 END IF;
2166
2167 -- Check Valid Task
2168 IF p_task_id IS NOT NULL and p_task_id <> FND_API.G_MISS_NUM THEN
2169 IF pa_agreement_utils.check_valid_task
2170 (p_project_id => p_project_id
2171 ,p_task_id => p_task_id) = 'N'
2172 THEN
2173 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2174 THEN
2175 pa_interface_utils_pub.map_new_amg_msg
2176 ( p_old_message_code => 'PA_INVALID_TASK'
2177 ,p_msg_attribute => 'CHANGE'
2178 ,p_resize_flag => 'N'
2179 ,p_msg_context => 'FUNDING'
2180 ,p_attribute1 => ''
2181 ,p_attribute2 => p_pm_funding_reference
2182 ,p_attribute3 => ''
2183 ,p_attribute4 => ''
2184 ,p_attribute5 => '');
2185 END IF;
2186 l_RETURN:='N';
2187 RETURN(l_RETURN);
2188 END IF;
2189 END IF;
2190
2191 -- Following changes are made for FP_M : Top Task customer changes
2192 l_Enable_Top_Task_Cust_Flag := PA_Billing_Pub.Get_Top_Task_Customer_Flag (
2193 P_Project_ID => P_Project_ID );
2194
2195
2196 /* Added the code for bug 5550709. Task level funding is required if customer at
2197 top task or invoice method by top task is enabled. */
2198 l_Inv_Method_Override_Flag := PA_Billing_Pub.Get_Inv_Method_Override_Flag (
2199 P_Project_ID => P_Project_ID );
2200
2201 IF p_task_id IS NULL and (l_Enable_Top_Task_Cust_Flag = 'Y' or
2202 l_Inv_Method_Override_Flag = 'Y') THEN
2203 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2204 THEN
2205 --dbms_output.put_line('Invalid Funding level');
2206 pa_interface_utils_pub.map_new_amg_msg
2207 ( p_old_message_code => 'PA_TASK_LEVEL_FUND_REQD'
2208 ,p_msg_attribute => 'CHANGE'
2209 ,p_resize_flag => 'N'
2210 ,p_msg_context => 'FUNDING'
2211 ,p_attribute1 => ''
2212 ,p_attribute2 => p_pm_funding_reference
2213 ,p_attribute3 => ''
2214 ,p_attribute4 => ''
2215 ,p_attribute5 => '');
2216 END IF;
2217 RETURN 'N';
2218 END IF;
2219
2220 -- Check for Top Task Customer existence if l_Enable_Top_Task_Cust_Flag = 'Y'
2221 --
2222 -- If Project is implemented with Top Task Customer enabled then make sure
2223 -- that the funding line's top task is associated to the customer which is same
2224 -- as that of agreement's customer.
2225 -- If not, then raise the error.
2226
2227 IF l_Enable_Top_Task_Cust_Flag = 'Y'
2228 THEN
2229 begin
2230 Select 1 into l_Exist_Flag
2231 From PA_Tasks
2232 Where Project_ID = P_Project_ID
2233 AND Task_ID = P_Task_ID
2234 AND Customer_ID = P_Customer_ID
2235 AND Rownum < 2;
2236 Exception when no_data_found then
2237 PA_Interface_Utils_Pub.Map_New_AMG_Msg (
2238 P_Old_Message_Code => 'PA_INVALID_TASK_CUSTOMER',
2239 P_Msg_Attribute => 'CHANGE',
2240 p_Resize_Flag => 'N',
2241 P_Msg_Context => 'FUNDING',
2242 P_Attribute1 => '',
2243 P_Attribute2 => p_pm_funding_reference,
2244 P_Attribute3 => '',
2245 P_Attribute4 => '',
2246 P_Attribute5 => ''
2247 );
2248 Return 'N';
2249 end;
2250 END IF;
2251 -- End of changes made for FP_M : Top Task customer changes
2252
2253 -- Check Funding Level
2254 /*added for finplan impact on billing*/
2255 /*pa_fin_plan_utils.Get_Appr_Rev_Plan_Type_Info (
2256 p_project_id =>p_project_id,
2257 x_plan_type_id => l_plan_type_id,
2258 x_return_status => l_return_status,
2259 x_msg_count => l_msg_count,
2260 x_msg_data => l_msg_data
2261 );
2262 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2263 THEN
2264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2265
2266 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2267 THEN
2268 RAISE FND_API.G_EXC_ERROR;
2269 END IF;
2270
2271 If l_plan_type_id is not null then
2272 l_check_proj_task_lvl_funding := pa_agreement_utils.check_proj_task_lvl_funding_fp
2273 (p_project_id => p_project_id
2274 ,p_task_id => p_task_id
2275 ,p_agreement_id => p_agreement_id); commented for bug 2729975*/
2276 /*end of change for finplan impact on billing*/
2277 --else
2278 l_check_proj_task_lvl_funding := pa_agreement_utils.check_proj_task_lvl_funding
2279 (p_project_id => p_project_id
2280 ,p_task_id => p_task_id
2281 ,p_agreement_id => p_agreement_id);
2282 --end if;
2283 IF l_check_proj_task_lvl_funding = 'A'
2284 THEN
2285 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2286 THEN
2287 pa_interface_utils_pub.map_new_amg_msg
2288 ( p_old_message_code => 'PA_PROJ_FUND_NO_TASK_TRANS'
2289 ,p_msg_attribute => 'CHANGE'
2290 ,p_resize_flag => 'N'
2291 ,p_msg_context => 'FUNDING'
2292 ,p_attribute1 => ''
2293 ,p_attribute2 => p_pm_funding_reference
2294 ,p_attribute3 => ''
2295 ,p_attribute4 => ''
2296 ,p_attribute5 => '');
2297 END IF;
2298 l_RETURN:='N';
2299 RETURN(l_RETURN);
2300 ELSIF l_check_proj_task_lvl_funding = 'P'
2301 THEN
2302 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2303 THEN
2304 pa_interface_utils_pub.map_new_amg_msg
2305 ( p_old_message_code => 'PA_BU_PROJECT_ALLOC_ONLY'
2306 ,p_msg_attribute => 'CHANGE'
2307 ,p_resize_flag => 'N'
2308 ,p_msg_context => 'FUNDING'
2309 ,p_attribute1 => ''
2310 ,p_attribute2 => p_pm_funding_reference
2311 ,p_attribute3 => ''
2312 ,p_attribute4 => ''
2313 ,p_attribute5 => '');
2314 END IF;
2315 l_RETURN:='N';
2316 RETURN(l_RETURN);
2317 ELSIF l_check_proj_task_lvl_funding = 'T'
2318 THEN
2319 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2320 THEN
2321 pa_interface_utils_pub.map_new_amg_msg
2322 ( p_old_message_code => 'PA_BU_TASK_ALLOC_ONLY'
2323 ,p_msg_attribute => 'CHANGE'
2324 ,p_resize_flag => 'N'
2325 ,p_msg_context => 'FUNDING'
2326 ,p_attribute1 => ''
2327 ,p_attribute2 => p_pm_funding_reference
2328 ,p_attribute3 => ''
2329 ,p_attribute4 => ''
2330 ,p_attribute5 => '');
2331 END IF;
2332 l_RETURN:='N';
2333 RETURN(l_RETURN);
2334 ELSIF l_check_proj_task_lvl_funding = 'B'
2335 THEN
2336 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2337 THEN
2338 pa_interface_utils_pub.map_new_amg_msg
2339 ( p_old_message_code => 'PA_TASK_FUND_NO_PROJ_TRANS'
2340 ,p_msg_attribute => 'CHANGE'
2341 ,p_resize_flag => 'N'
2342 ,p_msg_context => 'FUNDING'
2343 ,p_attribute1 => ''
2344 ,p_attribute2 => p_pm_funding_reference
2345 ,p_attribute3 => ''
2346 ,p_attribute4 => ''
2347 ,p_attribute5 => '');
2348 END IF;
2349 l_RETURN:='N';
2350 RETURN(l_RETURN);
2351 END IF;
2352
2353 /*Added for bug 2729975*/
2354 pa_fin_plan_utils.Get_Appr_Rev_Plan_Type_Info (
2355 p_project_id =>p_project_id,
2356 x_plan_type_id => l_plan_type_id,
2357 x_return_status => l_return_status,
2358 x_msg_count => l_msg_count,
2359 x_msg_data => l_msg_data
2360 );
2361 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2362 THEN
2363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2364
2365 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2366 THEN
2367 RAISE FND_API.G_EXC_ERROR;
2368 END IF;
2369
2370 If l_plan_type_id is not null then
2371 l_check_proj_task_lvl_funding := pa_agreement_utils.check_proj_task_lvl_funding_fp
2372 (p_project_id => p_project_id
2373 ,p_task_id => p_task_id
2374 ,p_agreement_id => p_agreement_id);
2375 IF l_check_proj_task_lvl_funding='A' THEN
2376
2377 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2378 THEN
2379 pa_interface_utils_pub.map_new_amg_msg
2380 ( p_old_message_code => 'PA_FP_CHK_FUNDING_LVL'
2381 ,p_msg_attribute => 'CHANGE'
2382 ,p_resize_flag => 'N'
2383 ,p_msg_context => 'FUNDING'
2384 ,p_attribute1 => ''
2385 ,p_attribute2 => p_pm_funding_reference
2386 ,p_attribute3 => ''
2387 ,p_attribute4 => ''
2388 ,p_attribute5 => '');
2389 END IF;
2390 l_RETURN:='N';
2391 RETURN(l_RETURN);
2392 END IF;
2393
2394 END IF;
2395
2396 -- added to validate the funding amount
2397 l_valid_fund_amt := pa_agreement_pvt.validate_funding_amt(p_funding_amt => p_funding_amt
2398 ,p_agreement_id => p_agreement_id
2399 ,p_operation_flag => 'U'
2400 ,p_pm_funding_reference => p_pm_funding_reference
2401 ,p_funding_id => p_funding_id) ;
2402 IF l_valid_fund_amt in ('Z','M') THEN
2403 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2404 THEN
2405 pa_interface_utils_pub.map_new_amg_msg
2406 ( p_old_message_code => 'PA_INVD_FUND_ALLOC'
2407 ,p_msg_attribute => 'CHANGE'
2408 ,p_resize_flag => 'N'
2409 ,p_msg_context => 'FUNDING'
2410 ,p_attribute1 => p_pm_funding_reference
2411 ,p_attribute2 => ''
2412 ,p_attribute3 => ''
2413 ,p_attribute4 => ''
2414 ,p_attribute5 => '');
2415 END IF;
2416 l_RETURN := 'N';
2417 RETURN(l_RETURN);
2418 END IF;
2419 /* commented as check is covered in the previous loop
2420 ELSIF l_valid_fund_amt = 'M' THEN
2421 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2422 THEN
2423 pa_interface_utils_pub.map_new_amg_msg
2424 ( p_old_message_code => 'PA_INVD_FUND_ALLOC'
2425 ,p_msg_attribute => 'CHANGE'
2426 ,p_resize_flag => 'N'
2427 ,p_msg_context => 'AGREEMENT'
2428 ,p_attribute1 => p_pm_funding_reference
2429 ,p_attribute2 => ''
2430 ,p_attribute3 => ''
2431 ,p_attribute4 => ''
2432 ,p_attribute5 => '');
2433 END IF;
2434 l_RETURN:='N';
2435 RETURN(l_RETURN);
2436 END IF;
2437 -- commented as check is covered in the previous loop
2438 */
2439 -- END IF;
2440
2441 --added to validate the funding amount
2442 --dbms_output.put_line('Check_update_funding_ok returning:'||l_return);
2443 RETURN(l_RETURN);
2444
2445 END check_update_funding_ok;
2446
2447
2448 -- ============================================================================
2449 --
2450 --Name: check_delete_funding_ok
2451 --Type: Function
2452 --Description: This function can be used to delete funding.
2453 --
2454 --Called subprograms:
2455 -- pa_project_utils.delete_funding
2456 --
2457 --
2458 --History:
2459 -- 25-MAR-2000 Rakesh Raghavan Created.
2460 -- -----------------------------------------------------------------------------
2461
2462 FUNCTION check_delete_funding_ok
2463 (p_agreement_id IN NUMBER
2464 ,p_funding_id IN NUMBER
2465 ,p_pm_funding_reference IN VARCHAR2)
2466 RETURN VARCHAR2
2467 IS
2468 -- LOCAL VARIABLES
2469 l_RETURN VARCHAR2(1):='Y';
2470 l_valid_fund_amt VARCHAR2(1):='Y';
2471 l_funding_amt NUMBER := 0;
2472 BEGIN
2473 --dbms_output.put_line('Inside" PA_AGREEMENT_PVT.CHECK_DELETE_FUNDING_OK');
2474
2475 -- Check Budget Type
2476 --dbms_output.put_line('Calling: pa_agreement_utils.check_budget_type');
2477 IF pa_agreement_utils.check_budget_type
2478 (p_funding_id => p_funding_id) = 'N'
2479 THEN
2480 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2481 THEN
2482 pa_interface_utils_pub.map_new_amg_msg
2483 ( p_old_message_code => 'PA_INVD_BDGT_TYP_CODE'
2484 ,p_msg_attribute => 'CHANGE'
2485 ,p_resize_flag => 'Y'
2486 ,p_msg_context => 'FUNDING'
2487 ,p_attribute1 => ''
2488 ,p_attribute2 => p_pm_funding_reference
2489 ,p_attribute3 => ''
2490 ,p_attribute4 => ''
2491 ,p_attribute5 => '');
2492 END IF;
2493 l_RETURN:='N';
2494 END IF;
2495
2496 --dbms_output.put_line('Calling: pa_agreement_utils.check_revenue_limit');
2497 IF pa_agreement_utils.check_revenue_limit
2498 (p_agreement_id => p_agreement_id) = 'N'
2499 THEN
2500 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2501 THEN
2502 pa_interface_utils_pub.map_new_amg_msg
2503 ( p_old_message_code => 'PA_INVALID_REVENUE_LIMIT'
2504 ,p_msg_attribute => 'CHANGE'
2505 ,p_resize_flag => 'Y'
2506 ,p_msg_context => 'FUNDING'
2507 ,p_attribute1 => ''
2508 ,p_attribute2 => p_pm_funding_reference
2509 ,p_attribute3 => ''
2510 ,p_attribute4 => ''
2511 ,p_attribute5 => '');
2512 END IF;
2513 l_RETURN:='N';
2514 END IF;
2515 -- added to validate the funding amount
2516 IF p_funding_id IS NOT NULL THEN
2517 SELECT f.allocated_amount
2518 INTO l_funding_amt
2519 FROM pa_project_fundings f
2520 WHERE f.project_funding_id = p_funding_id;
2521 ELSIF p_pm_funding_reference IS NULL THEN
2522 SELECT f.allocated_amount
2523 INTO l_funding_amt
2524 FROM pa_project_fundings f
2525 WHERE f.pm_funding_reference = p_pm_funding_reference
2526 AND f.agreement_id = p_agreement_id;
2527 END IF;
2528 l_valid_fund_amt := pa_agreement_pvt.validate_funding_amt(p_funding_amt => l_funding_amt
2529 ,p_agreement_id => p_agreement_id
2530 ,p_operation_flag => 'D'
2531 ,p_pm_funding_reference => p_pm_funding_reference
2532 ,p_funding_id => p_funding_id ) ;
2533 IF l_valid_fund_amt = 'Z' THEN
2534 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2535 THEN
2536 pa_interface_utils_pub.map_new_amg_msg
2537 ( p_old_message_code => 'PA_INVD_FUND_ALLOC'
2538 ,p_msg_attribute => 'CHANGE'
2539 ,p_resize_flag => 'N'
2540 ,p_msg_context => 'AGREEMENT'
2541 ,p_attribute1 => p_pm_funding_reference
2542 ,p_attribute2 => ''
2543 ,p_attribute3 => ''
2544 ,p_attribute4 => ''
2545 ,p_attribute5 => '');
2546 END IF;
2547 l_RETURN := 'N';
2548 ELSIF l_valid_fund_amt = 'M' THEN
2549 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2550 THEN
2551 pa_interface_utils_pub.map_new_amg_msg
2552 ( p_old_message_code => 'PA_INVD_FUND_ALLOC'
2553 ,p_msg_attribute => 'CHANGE'
2554 ,p_resize_flag => 'N'
2555 ,p_msg_context => 'AGREEMENT'
2556 ,p_attribute1 => p_pm_funding_reference
2557 ,p_attribute2 => ''
2558 ,p_attribute3 => ''
2559 ,p_attribute4 => ''
2560 ,p_attribute5 => '');
2561 END IF;
2562 l_RETURN:='N';
2563 END IF;
2564
2565 -- END IF;
2566
2567 --added to validate the funding amount
2568
2569 RETURN(l_RETURN);
2570
2571 END check_delete_funding_ok;
2572
2573
2574 -- ============================================================================
2575 --
2576 --Name: validate_flex_fields
2577 --Type: Procedure
2578 --Description: This procedure can be used to validate flexfields.
2579 --
2580 --Called subprograms:
2581 -- None
2582 --
2583 --
2584 --History:
2585 -- 25-MAR-2000 Rakesh Raghavan Created.
2586 -- -----------------------------------------------------------------------------
2587 PROCEDURE validate_flex_fields(
2588 p_desc_flex_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2589 ,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2590 ,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2591 ,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2592 ,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2593 ,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2594 ,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2595 ,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2596 ,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2597 ,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2598 ,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2599 ,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2600 ,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2601 ,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2602 ,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2603 ,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2604 ,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2605 ,p_attribute16 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2606 ,p_attribute17 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2607 ,p_attribute18 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2608 ,p_attribute19 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2609 ,p_attribute20 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2610 ,p_attribute21 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2611 ,p_attribute22 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2612 ,p_attribute23 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2613 ,p_attribute24 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2614 ,p_attribute25 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2615 ,p_RETURN_msg OUT NOCOPY VARCHAR2 /*file.sql.39*/
2616 ,p_validate_status OUT NOCOPY VARCHAR2 /*file.sql.39*/)
2617 IS
2618 l_dummy VARCHAR2(1);
2619 l_r VARCHAR2(2000);
2620 BEGIN
2621
2622 -- DEFINE ID COLUMNS
2623 fnd_flex_descval.set_context_value(p_attribute_category);
2624 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1);
2625 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2);
2626 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3);
2627 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4);
2628 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5);
2629 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6);
2630 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7);
2631 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8);
2632 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9);
2633 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10);
2634 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_attribute11);
2635 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_attribute12);
2636 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_attribute13);
2637 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_attribute14);
2638 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_attribute15);
2639 fnd_flex_descval.set_column_value('ATTRIBUTE16', p_attribute16);
2640 fnd_flex_descval.set_column_value('ATTRIBUTE17', p_attribute17);
2641 fnd_flex_descval.set_column_value('ATTRIBUTE18', p_attribute18);
2642 fnd_flex_descval.set_column_value('ATTRIBUTE19', p_attribute19);
2643 fnd_flex_descval.set_column_value('ATTRIBUTE20', p_attribute20);
2644 fnd_flex_descval.set_column_value('ATTRIBUTE21', p_attribute21);
2645 fnd_flex_descval.set_column_value('ATTRIBUTE22', p_attribute22);
2646 fnd_flex_descval.set_column_value('ATTRIBUTE23', p_attribute23);
2647 fnd_flex_descval.set_column_value('ATTRIBUTE24', p_attribute24);
2648 fnd_flex_descval.set_column_value('ATTRIBUTE25', p_attribute25);
2649
2650 -- VALIDATE
2651 IF (fnd_flex_descval.validate_desccols( 'PA',p_desc_flex_name)) then
2652 p_RETURN_msg := 'VALID: ' || fnd_flex_descval.concatenated_ids;
2653 p_validate_status := 'Y';
2654 ELSE
2655 p_RETURN_msg := 'INVALID: ' || fnd_flex_descval.error_message;
2656 p_validate_status := 'N';
2657 END IF;
2658 END validate_flex_fields;
2659
2660
2661 -- ============================================================================
2662 --
2663 --Name: check_yes_no
2664 --Type: function
2665 --Description: This function will return 'Y' if the value passed is 'Y' or 'N' else return 'N'.
2666 --
2667 --Called subprograms:
2668 -- None
2669 --
2670 --
2671 --History:
2672 -- 25-MAR-2000 Rakesh Raghavan Created.
2673 -- -----------------------------------------------------------------------------
2674 FUNCTION check_yes_no
2675 (p_val VARCHAR2)
2676 RETURN VARCHAR2
2677 IS
2678 BEGIN
2679 IF p_val in ('Y','N','y','n')
2680 THEN
2681 RETURN 'Y';
2682 ELSE
2683 RETURN 'N';
2684 END IF;
2685 END check_yes_no;
2686
2687 -- ============================================================================
2688 --
2689 --Name: check_add_update
2690 --Type: function
2691 --Description: This function will return 'U' if the funding reference
2692 -- passed into it already exists else returns 'A' if the
2693 -- funding reference does not exist.
2694 --
2695 --Called subprograms:
2696 -- None
2697 --
2698 --
2699 --History:
2700 -- 25-MAR-2000 Rakesh Raghavan Created.
2701 -- -----------------------------------------------------------------------------
2702 FUNCTION check_add_update
2703 (p_funding_reference VARCHAR2)
2704 RETURN VARCHAR2
2705 IS
2706 CURSOR C1 is
2707 Select 1 from pa_project_fundings
2708 where pm_funding_reference = p_funding_reference;
2709
2710 l_temp NUMBER;
2711
2712 BEGIN
2713 --dbms_output.put_line('Inside: PA_AGREEMENT_PVT.CHECK_ADD_UPDATE');
2714 --dbms_output.put_line('p_funding_reference: '||nvl(p_funding_reference,'NULL'));
2715 OPEN C1;
2716 FETCH C1 into l_temp;
2717 IF C1%FOUND
2718 THEN
2719 --dbms_output.put_line('Returning: U');
2720 RETURN 'U';
2721 ELSE
2722 --dbms_output.put_line('Returning: A');
2723 RETURN 'A';
2724 END IF;
2725 CLOSE C1;
2726 END check_add_update;
2727
2728
2729 -- ============================================================================
2730 --
2731 --Name: Validate_funding_amt
2732 --Type: function
2733 --Description: This function will return 'Y' if the funding amonut is
2734 -- valid otherwise it will return 'Z' or 'M' or 'N'.
2735 -- 'Y'=> Valid Value for the allocated amount
2736 -- 'Z'=> Invalid Value (Total Allocated Amount is less than zero)
2737 -- 'M'=> Invalid Value (Total Allocated Amount is more that agreement amount)
2738 -- Funding amount will be considered valid If the sum of all the fundings are not
2739 -- negative and it is less that the agreenment amount
2740 --
2741 --Called subprograms:
2742 -- None
2743 --
2744 --
2745 --History:
2746 -- 30-Sep-2000 Nikhil Mishra Created.
2747 -- -----------------------------------------------------------------------------
2748 FUNCTION validate_funding_amt
2749 (p_funding_amt NUMBER
2750 ,p_agreement_id NUMBER
2751 ,p_operation_flag VARCHAR2
2752 ,p_funding_id NUMBER
2753 ,p_pm_funding_reference VARCHAR2)
2754 RETURN VARCHAR2
2755 IS
2756 -- LOCAL VARIABLES
2757 l_RETURN VARCHAR2(1):='Y';
2758 l_fun_sum NUMBER := 0;
2759 l_fun_amt NUMBER := 0;
2760 l_agr_amt NUMBER := 0;
2761
2762 BEGIN
2763 --dbms_output.put_line('INSIDE: => validate_funding_amt');
2764 IF p_funding_id IS NOT NULL THEN
2765 --dbms_output.put_line('INSIDE: => validate_funding_amt => Funding id is '|| to_char(p_funding_id));
2766 SELECT nvl(f.allocated_amount,0)
2767 INTO l_fun_amt
2768 FROM pa_project_fundings f
2769 WHERE f.project_funding_id = p_funding_id;
2770 END IF;
2771
2772 /*Federal*/
2773 SELECT decode(a.advance_required,'Y',nvl(a.advance_amount,0),nvl(a.amount,0))
2774 INTO l_agr_amt
2775 FROM pa_agreements_all a
2776 WHERE a.agreement_id = p_agreement_id;
2777 --dbms_output.put_line('INSIDE: => validate_funding_amt => Total Agreement Amt is '|| to_char(l_agr_amt));
2778
2779 SELECT nvl(SUM(f.allocated_amount),0)
2780 INTO l_fun_sum
2781 FROM pa_project_fundings f
2782 WHERE f.agreement_id = p_agreement_id;
2783
2784 --dbms_output.put_line('INSIDE: => validate_funding_amt => Total Funding Amt is '|| to_char(l_fun_sum));
2785
2786 IF p_operation_flag = 'A' THEN
2787 l_fun_sum := l_fun_sum + p_funding_amt;
2788 --dbms_output.put_line('INSIDE: => this is an add operation amount is '|| to_char(l_fun_sum));
2789 ELSIF p_operation_flag = 'U' THEN
2790 l_fun_sum := l_fun_sum + p_funding_amt - l_fun_amt ;
2791 ELSIF p_operation_flag = 'D' THEN
2792 l_fun_sum := l_fun_sum - l_fun_amt;
2793 END IF;
2794
2795 IF l_fun_sum < 0
2796 THEN
2797 --dbms_output.put_line('INSIDE: => less than zero');
2798 l_RETURN := 'Z';
2799 ELSIF l_fun_sum > l_agr_amt
2800 THEN l_RETURN := 'M';
2801 ELSIF l_fun_sum < l_agr_amt
2802 AND l_fun_sum >= 0
2803 THEN l_RETURN := 'Y';
2804 END IF;
2805 --dbms_output.put_line('INSIDE: => returning ' || l_RETURN);
2806 RETURN (l_RETURN);
2807
2808 --dbms_output.put_line('INSIDE: => Thats all');
2809
2810 EXCEPTION
2811 WHEN OTHERS THEN
2812 --dbms_output.put_line('INSIDE: => WHEN OTHERS');
2813 RETURN 'N';
2814
2815 END validate_funding_amt;
2816
2817 END PA_AGREEMENT_PVT;