[Home] [Help]
PACKAGE BODY: APPS.PA_AGREEMENT_PUB
Source
1 package body PA_AGREEMENT_PUB as
2 /*$Header: PAAFAPBB.pls 120.14.12020000.4 2013/04/23 08:45:33 rdegala ship $*/
3
4 --Global constants to be used in error messages
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_AGREEMENT_PUB';
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 --Name: create_agreement
15 --Type: Procedure
16 --Description: This procedure can be used to create an agreement for an
17 -- existing project or template.
18 --
19 --Called subprograms:
20 -- pa_interface_utils_pub.map_new_amg_msg
21 -- pa_agreement_pub.check_create_agreement_ok
22 -- pa_agreement_utils.validate_flex_fields
23 -- pa_agreement_pvt.convert_ag_ref_to_id
24 -- pa_agreement_utils.create_agreement
25 -- pa_agreement_pub.add_funding
26 --
27 --
28 --
29 --History:
30 -- 25-MAR-2000 Rakesh Raghavan Created.
31 -- 10-SEP-2001 Srividya MCB2 code changes
32 -- 17-JAN-2002 Amit Bug 2180350. Changed from OR
33 -- to AND in the if condition of
34 -- update_agreement procedure.
35 -- ---------------------------------------------------------------------------
36
37 PROCEDURE create_agreement
38 (p_api_version_number IN NUMBER --:=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
39 ,p_commit IN VARCHAR2 --:= FND_API.G_FALSE
40 ,p_init_msg_list IN VARCHAR2 --:= FND_API.G_FALSE
41 ,p_msg_count OUT NOCOPY NUMBER /*file.sql.39*/
42 ,p_msg_data OUT NOCOPY VARCHAR2 /*file.sql.39*/
43 ,p_return_status OUT NOCOPY VARCHAR2
44 ,p_pm_product_code IN VARCHAR2 --:= PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
45 ,p_agreement_in_rec IN Agreement_Rec_In_Type
46 ,p_agreement_out_rec OUT NOCOPY Agreement_Rec_Out_Type /*file.sql.39*/
47 ,p_funding_in_tbl IN funding_in_tbl_type
48 ,p_funding_out_tbl OUT NOCOPY funding_out_tbl_type /*File.sql.39*/
49 )
50 IS
51 -- Local variables
52 l_msg_count NUMBER ;
53 l_msg_data VARCHAR2(2000);
54 l_function_allowed VARCHAR2(1);
55 l_resp_id NUMBER := 0;
56 l_row_id VARCHAR2(2000);
57 l_return_msg VARCHAR2(2000);
58 l_validate_status VARCHAR2(1);
59 i NUMBER;
60 l_funding_in_rec funding_rec_in_type;
61 l_funding_id NUMBER;
62 l_out_agreement_id NUMBER ;
63 l_return_status VARCHAR2(1);
64 l_api_name CONSTANT VARCHAR2(30):= 'create_agreement';
65 l_agreement_in_rec Agreement_Rec_In_Type;
66 l_valid_currency VARCHAR2(1); /* Bug 2437469 */
67 l_old_fund_curr_code VARCHAR2(15); /* Bug 2437469 */
68 l_invproc_currency_type VARCHAR2(30); /* Bug 2437469 */
69 x_advance_flag boolean; /*Added for bug 5743599*/
70 x_status Number; /*Added for bug 5743599*/
71 x_error_message varchar2(240); /*Added for bug 5743599*/
72 l_advance_flag varchar2(2); /*Added for bug 5743599*/
73
74 BEGIN
75
76 -- Standard begin of API savepoint
77
78 SAVEPOINT create_agreement_pub;
79
80 -- Standard call to check for call compatibility.
81
82
83 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
84 p_api_version_number ,
85 l_api_name ,
86 G_PKG_NAME )
87 THEN
88
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90
91 END IF;
92 l_resp_id := FND_GLOBAL.Resp_id;
93
94 -- Actions performed using the APIs would be subject to
95 -- function security. If the responsibility does not allow
96 -- such functions to be executed, the API should not proceed further
97 -- since the user does not have access to such functions
98
99
100
101 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
102 (p_api_version_number => p_api_version_number,
103 p_responsibility_id => l_resp_id,
104 p_function_name => 'PA_AF_CREATE_AGREEMENT',
105 p_msg_count => l_msg_count,
106 p_msg_data => l_msg_data,
107 p_return_status => l_return_status,
108 p_function_allowed => l_function_allowed
109 );
110
111 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
112 THEN
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114
115 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
116 THEN
117 RAISE FND_API.G_EXC_ERROR;
118 END IF;
119
120
121 IF l_function_allowed = 'N' THEN
122 pa_interface_utils_pub.map_new_amg_msg
123 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
124 ,p_msg_attribute => 'CHANGE'
125 ,p_resize_flag => 'Y'
126 ,p_msg_context => 'GENERAL'
127 ,p_attribute1 => ''
128 ,p_attribute2 => ''
129 ,p_attribute3 => ''
130 ,p_attribute4 => ''
131 ,p_attribute5 => '');
132 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
133 RAISE FND_API.G_EXC_ERROR;
134 END IF;
135
136 -- Initialize the message table if requested.
137
138 IF FND_API.TO_BOOLEAN( p_init_msg_list )
139 THEN
140
141 FND_MSG_PUB.initialize;
142
143 END IF;
144
145 -- Set API return status to success
146
147 p_return_status := FND_API.G_RET_STS_SUCCESS;
148 p_agreement_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
149
150 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
151
152 -- Product Code
153 IF (p_pm_product_code IS NULL)
154 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
155 THEN
156 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
157 THEN
158 pa_interface_utils_pub.map_new_amg_msg
159 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISS'
160 ,p_msg_attribute => 'CHANGE'
161 ,p_resize_flag => 'N'
162 ,p_msg_context => 'GENERAL'
163 ,p_attribute1 => ''
164 ,p_attribute2 => ''
165 ,p_attribute3 => ''
166 ,p_attribute4 => ''
167 ,p_attribute5 => '');
168 END IF;
169 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
170 p_return_status := FND_API.G_RET_STS_ERROR;
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173
174 -- Agreement Reference
175 IF (p_agreement_in_rec.pm_agreement_reference IS NULL)
176 OR (p_agreement_in_rec.pm_agreement_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
177 THEN
178 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
179 THEN
180 pa_interface_utils_pub.map_new_amg_msg
181 ( p_old_message_code => 'PA_AGMT_REF_IS_MISS'
182 ,p_msg_attribute => 'CHANGE'
183 ,p_resize_flag => 'N'
184 ,p_msg_context => 'GENERAL'
185 ,p_attribute1 => ''
186 ,p_attribute2 => ''
187 ,p_attribute3 => ''
188 ,p_attribute4 => ''
189 ,p_attribute5 => '');
190 END IF;
191 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
192 p_return_status := FND_API.G_RET_STS_ERROR;
193 RAISE FND_API.G_EXC_ERROR;
194 END IF;
195
196 -- Agreement Number
197 IF (p_agreement_in_rec.agreement_num IS NULL)
198 OR (p_agreement_in_rec.agreement_num = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
199 THEN
200 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
201 THEN
202 pa_interface_utils_pub.map_new_amg_msg
203 ( p_old_message_code => 'PA_AGMT_NUM_IS_MISS'
204 ,p_msg_attribute => 'CHANGE'
205 ,p_resize_flag => 'N'
206 ,p_msg_context => 'AGREEMENT'
207 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
208 ,p_attribute2 => ''
209 ,p_attribute3 => ''
210 ,p_attribute4 => ''
211 ,p_attribute5 => '');
212 END IF;
213 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
214 p_return_status := FND_API.G_RET_STS_ERROR;
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217
218 -- Customer Id
219
220 IF (p_agreement_in_rec.customer_id IS NULL)
221 OR (p_agreement_in_rec.customer_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
222 THEN
223 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
224 THEN
225 pa_interface_utils_pub.map_new_amg_msg
226 ( p_old_message_code => 'PA_CUST_ID_IS_MISS'
227 ,p_msg_attribute => 'CHANGE'
228 ,p_resize_flag => 'N'
229 ,p_msg_context => 'AGREEMENT'
230 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
231 ,p_attribute2 => ''
232 ,p_attribute3 => ''
233 ,p_attribute4 => ''
234 ,p_attribute5 => '');
235 END IF;
236 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
237 p_return_status := FND_API.G_RET_STS_ERROR;
238 RAISE FND_API.G_EXC_ERROR;
239 END IF;
240
241 -- Customer Number
242 IF (p_agreement_in_rec.customer_num IS NULL)
243 OR (p_agreement_in_rec.customer_num = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
244 THEN
245 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
246 THEN
247 pa_interface_utils_pub.map_new_amg_msg
248 ( p_old_message_code => 'PA_CUST_NUM_IS_MISS'
249 ,p_msg_attribute => 'CHANGE'
250 ,p_resize_flag => 'N'
251 ,p_msg_context => 'AGREEMENT'
252 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
253 ,p_attribute2 => ''
254 ,p_attribute3 => ''
255 ,p_attribute4 => ''
256 ,p_attribute5 => '');
257 END IF;
258 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
259 p_return_status := FND_API.G_RET_STS_ERROR;
260 RAISE FND_API.G_EXC_ERROR;
261 END IF;
262
263 -- Agreement Type
264 IF (p_agreement_in_rec.agreement_type IS NULL)
265 OR (p_agreement_in_rec.agreement_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
266 THEN
267 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
268 THEN
269 pa_interface_utils_pub.map_new_amg_msg
270 ( p_old_message_code => 'PA_AGMT_TYPE_IS_MISS'
271 ,p_msg_attribute => 'CHANGE'
272 ,p_resize_flag => 'N'
273 ,p_msg_context => 'AGREEMENT'
274 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
275 ,p_attribute2 => ''
276 ,p_attribute3 => ''
277 ,p_attribute4 => ''
278 ,p_attribute5 => '');
279 END IF;
280 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
281 p_return_status := FND_API.G_RET_STS_ERROR;
282 RAISE FND_API.G_EXC_ERROR;
283 END IF;
284
285 -- Term Id
286 IF (p_agreement_in_rec.term_id IS NULL)
287 OR (p_agreement_in_rec.term_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
288 THEN
289 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
290 THEN
291 pa_interface_utils_pub.map_new_amg_msg
292 ( p_old_message_code => 'PA_TERM_ID_IS_MISS'
293 ,p_msg_attribute => 'CHANGE'
294 ,p_resize_flag => 'N'
295 ,p_msg_context => 'AGREEMENT'
296 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
297 ,p_attribute2 => ''
298 ,p_attribute3 => ''
299 ,p_attribute4 => ''
300 ,p_attribute5 => '');
301 END IF;
302 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
303 p_return_status := FND_API.G_RET_STS_ERROR;
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
306 /*
307 -- Template Flag
308 IF (p_agreement_in_rec.template_flag IS NULL)
309 OR (p_agreement_in_rec.template_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
310 THEN
311 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
312 THEN
313 pa_interface_utils_pub.map_new_amg_msg
314 ( p_old_message_code => 'PA_TEMP_FLG_IS_MISS'
315 ,p_msg_attribute => 'CHANGE'
316 ,p_resize_flag => 'N'
317 ,p_msg_context => 'AGREEMENT'
318 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
319 ,p_attribute2 => ''
320 ,p_attribute3 => ''
321 ,p_attribute4 => ''
322 ,p_attribute5 => '');
323 END IF;
324 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
325 p_return_status := FND_API.G_RET_STS_ERROR;
326 RAISE FND_API.G_EXC_ERROR;
327 END IF;
328 */
329 -- Revenue Limit Flag
330 IF (p_agreement_in_rec.revenue_limit_flag IS NULL)
331 OR (p_agreement_in_rec.revenue_limit_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
332 THEN
333 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
334 THEN
335 pa_interface_utils_pub.map_new_amg_msg
336 ( p_old_message_code => 'PA_REV_LT_FLG_IS_MISS'
337 ,p_msg_attribute => 'CHANGE'
338 ,p_resize_flag => 'N'
339 ,p_msg_context => 'AGREEMENT'
340 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
341 ,p_attribute2 => ''
342 ,p_attribute3 => ''
343 ,p_attribute4 => ''
344 ,p_attribute5 => '');
345 END IF;
346 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
347 p_return_status := FND_API.G_RET_STS_ERROR;
348 RAISE FND_API.G_EXC_ERROR;
349 END IF;
350
351 -- Owned By Person Id
352 IF (p_agreement_in_rec.owned_by_person_id IS NULL)
353 OR (p_agreement_in_rec.owned_by_person_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
354 THEN
355 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
356 THEN
357 pa_interface_utils_pub.map_new_amg_msg
358 ( p_old_message_code => 'PA_OWND_BY_PRSN_ID_MISS'
359 ,p_msg_attribute => 'CHANGE'
360 ,p_resize_flag => 'N'
361 ,p_msg_context => 'AGREEMENT'
362 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
363 ,p_attribute2 => ''
364 ,p_attribute3 => ''
365 ,p_attribute4 => ''
366 ,p_attribute5 => '');
367 END IF;
368 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
369 p_return_status := FND_API.G_RET_STS_ERROR;
370 RAISE FND_API.G_EXC_ERROR;
371 END IF;
372
373 -- Amount
374 IF (p_agreement_in_rec.amount IS NULL)
375 OR (p_agreement_in_rec.amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
376 THEN
377 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
378 THEN
379 pa_interface_utils_pub.map_new_amg_msg
380 ( p_old_message_code => 'PA_ALLOC_AMT_IS_MISS_AMG'
381 ,p_msg_attribute => 'CHANGE'
382 ,p_resize_flag => 'N'
383 ,p_msg_context => 'AGREEMENT'
384 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
385 ,p_attribute2 => ''
386 ,p_attribute3 => ''
387 ,p_attribute4 => ''
388 ,p_attribute5 => '');
389 END IF;
390 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
391 p_return_status := FND_API.G_RET_STS_ERROR;
392 RAISE FND_API.G_EXC_ERROR;
393 END IF;
394
395 -- VALIDATE THE INCOMING PARAMETERS
396 IF pa_agreement_pvt.check_create_agreement_ok
397 (p_pm_agreement_reference => p_agreement_in_rec.pm_agreement_reference
398 ,p_customer_id => p_agreement_in_rec.customer_id
399 ,p_agreement_type => p_agreement_in_rec.agreement_type
400 ,p_agreement_num => p_agreement_in_rec.agreement_num
401 ,p_term_id => p_agreement_in_rec.term_id
402 ,p_template_flag => p_agreement_in_rec.template_flag
403 ,p_revenue_limit_flag => p_agreement_in_rec.revenue_limit_flag
404 ,p_owned_by_person_id => p_agreement_in_rec.owned_by_person_id
405 /* MCB2 params begin */
406 ,p_owning_organization_id => p_agreement_in_rec.owning_organization_id
407 ,p_agreement_currency_code => p_agreement_in_rec.agreement_currency_code
408 ,p_invoice_limit_flag => p_agreement_in_rec.invoice_limit_flag
409 /* MCB2 params end */ /*federal*/
410 ,p_start_date => p_agreement_in_rec.start_date
411 ,p_end_date => p_agreement_in_rec.expiration_date
412 ,p_advance_required => p_agreement_in_rec.advance_required
413 ,p_billing_sequence => p_agreement_in_rec.billing_sequence
414 ) = 'N'
415 THEN
416 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
417 p_return_status := FND_API.G_RET_STS_ERROR;
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420
421 -- Flex Field Validations
422
423 IF (p_agreement_in_rec.desc_flex_name IS NOT NULL)
424
425 AND (p_agreement_in_rec.desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
426 THEN
427 /*Changed for federal*/
428 pa_agreement_pvt.validate_flex_fields
429 (p_desc_flex_name => p_agreement_in_rec.desc_flex_name
430 ,p_attribute_category => p_agreement_in_rec.attribute_category
431 ,p_attribute1 => p_agreement_in_rec.attribute1
432 ,p_attribute2 => p_agreement_in_rec.attribute2
433 ,p_attribute3 => p_agreement_in_rec.attribute3
434 ,p_attribute4 => p_agreement_in_rec.attribute4
435 ,p_attribute5 => p_agreement_in_rec.attribute5
436 ,p_attribute6 => p_agreement_in_rec.attribute6
437 ,p_attribute7 => p_agreement_in_rec.attribute7
438 ,p_attribute8 => p_agreement_in_rec.attribute8
439 ,p_attribute9 => p_agreement_in_rec.attribute9
440 ,p_attribute10 => p_agreement_in_rec.attribute10
441 ,p_attribute11 => p_agreement_in_rec.attribute11
442 ,p_attribute12 => p_agreement_in_rec.attribute12
443 ,p_attribute13 => p_agreement_in_rec.attribute13
444 ,p_attribute14 => p_agreement_in_rec.attribute14
445 ,p_attribute15 => p_agreement_in_rec.attribute15
446 ,p_attribute16 => p_agreement_in_rec.attribute16
447 ,p_attribute17 => p_agreement_in_rec.attribute17
448 ,p_attribute18 => p_agreement_in_rec.attribute18
449 ,p_attribute19 => p_agreement_in_rec.attribute19
450 ,p_attribute20 => p_agreement_in_rec.attribute20
451 ,p_attribute21 => p_agreement_in_rec.attribute21
452 ,p_attribute22 => p_agreement_in_rec.attribute22
453 ,p_attribute23 => p_agreement_in_rec.attribute23
454 ,p_attribute24 => p_agreement_in_rec.attribute24
455 ,p_attribute25 => p_agreement_in_rec.attribute25
456 ,p_return_msg => l_return_msg
457 ,p_validate_status => l_validate_status);
458
459 IF l_validate_status = 'N'
460 THEN
461 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
462 THEN
463 pa_interface_utils_pub.map_new_amg_msg
464 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
465 ,p_msg_attribute => 'CHANGE'
466 ,p_resize_flag => 'N'
467 ,p_msg_context => 'FLEX'
468 ,p_attribute1 => l_return_msg
469 ,p_attribute2 => ''
470 ,p_attribute3 => ''
471 ,p_attribute4 => ''
472 ,p_attribute5 => '');
473 END IF;
474 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
475 p_return_status := FND_API.G_RET_STS_ERROR;
476 RAISE FND_API.G_EXC_ERROR;
477 END IF;
478 END IF;
479
480 /* NOT REQUIRED
481 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
482 pa_agreement_pvt.convert_ag_ref_to_id
483 (p_pm_agreement_reference => p_agreement_in_rec.pm_agreement_reference
484 ,p_af_agreement_id => p_agreement_in_rec.agreement_id
485 ,p_out_agreement_id => l_out_agreement_id
486 ,p_return_status => l_return_status);
487 p_agreement_out_rec.return_status := l_return_status;
488 p_return_status := l_return_status;
489
490 IF l_return_status = FND_API.G_RET_STS_ERROR
491 THEN
492 RAISE FND_API.G_EXC_ERROR;
493 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
494 THEN
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 ELSE
497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498 END IF;
499 */
500
501 l_agreement_in_rec := p_agreement_in_rec;
502
503 IF l_agreement_in_rec.expiration_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
504 l_agreement_in_rec.expiration_date := NULL;
505 END IF;
506
507 IF l_agreement_in_rec.description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
508 l_agreement_in_rec.description := NULL;
509 END IF;
510
511 IF l_agreement_in_rec.desc_flex_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
512 l_agreement_in_rec.desc_flex_name := NULL;
513 END IF;
514
515 IF l_agreement_in_rec.attribute_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
516 l_agreement_in_rec.attribute_category := NULL;
517 END IF;
518 IF l_agreement_in_rec.attribute1 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
519 l_agreement_in_rec.attribute1 := NULL;
520 END IF;
521
522 IF l_agreement_in_rec.attribute2 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
523 l_agreement_in_rec.attribute2 := NULL;
524 END IF;
525 IF l_agreement_in_rec.template_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
526 l_agreement_in_rec.template_flag := NULL;
527 END IF;
528
529 IF l_agreement_in_rec.attribute3 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
530 l_agreement_in_rec.attribute3 := NULL;
531 END IF;
532 IF l_agreement_in_rec.attribute4 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
533 l_agreement_in_rec.attribute4 := NULL;
534 END IF;
535 IF l_agreement_in_rec.attribute5 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
536 l_agreement_in_rec.attribute5 := NULL;
537 END IF;
538 IF l_agreement_in_rec.attribute6 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
539 l_agreement_in_rec.attribute6 := NULL;
540 END IF;
541 IF l_agreement_in_rec.attribute7 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
542 l_agreement_in_rec.attribute7 := NULL;
543 END IF;
544 IF l_agreement_in_rec.attribute8 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
545 l_agreement_in_rec.attribute8 := NULL;
546 END IF;
547 IF l_agreement_in_rec.attribute9 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
548 l_agreement_in_rec.attribute9 := NULL;
549 END IF;
550 IF l_agreement_in_rec.attribute10 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
551 l_agreement_in_rec.attribute10 := NULL;
552 END IF;
553
554 IF l_agreement_in_rec.template_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
555 l_agreement_in_rec.template_flag := NULL;
556 END IF;
557
558 /* Start Bug 2701579 Initialized owning_organization_id,invoice_limit_flag
559 and agreement_currency_code */
560 IF l_agreement_in_rec.owning_organization_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
561 l_agreement_in_rec.owning_organization_id := NULL;
562 END IF;
563
564 IF l_agreement_in_rec.invoice_limit_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
565 l_agreement_in_rec.invoice_limit_flag := 'N';
566 END IF;
567
568 IF l_agreement_in_rec.agreement_currency_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
569 SELECT pa_currency.get_currency_code
570 INTO l_agreement_in_rec.agreement_currency_code from dual;
571 END IF;
572 /* End- Bug 2701579 */
573
574 /*Federal*/
575
576 IF l_agreement_in_rec.customer_order_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
577 l_agreement_in_rec.customer_order_number := NULL;
578 END IF;
579
580 /* Code commented for bug 5743599 syarts
581 IF l_agreement_in_rec.Advance_required = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
582 l_agreement_in_rec.Advance_required := 'N';
583 END IF;
584 Code commented for bug 5743599 ends */
585
586 /* Code Added for bug 5743599 starts */
587
588 PA_ADVANCE_CLIENT_EXT.advance_required(p_agreement_in_rec.customer_id,
589 x_advance_flag,
590 x_error_message,
591 x_status);
592
593 IF (x_status = 0 ) THEN
594 IF (x_advance_flag = TRUE) then
595 l_advance_flag := 'Y';
596 ELSE
597 l_advance_flag := 'N';
598 END IF;
599 ELSE
600 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
601 THEN
602 pa_interface_utils_pub.map_new_amg_msg
603 ( p_old_message_code => 'PA_CLNT_ADV_CHECK'
604 ,p_msg_attribute => 'CHANGE'
605 ,p_resize_flag => 'N'
606 ,p_msg_context => 'AGREEMENT'
607 ,p_attribute1 => p_agreement_in_rec.pm_agreement_reference
608 ,p_attribute2 => ''
609 ,p_attribute3 => ''
610 ,p_attribute4 => ''
611 ,p_attribute5 => '');
612 END IF;
613 p_return_status := FND_API.G_RET_STS_ERROR;
614 RAISE FND_API.G_EXC_ERROR;
615 END IF;
616
617 IF (l_agreement_in_rec.Advance_required IS NOT NULL and
618 l_agreement_in_rec.Advance_required <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) then
619
620 IF (FND_FUNCTION.TEST('PA_PAXINEAG_ADVREQ')) THEN
621 null;
622 ELSE
623 l_agreement_in_rec.Advance_required := l_advance_flag;
624 END IF;
625 ELSE
626 l_agreement_in_rec.Advance_required := l_advance_flag;
627 END IF;
628
629 /* Code Added for bug 5743599 ends */
630
631 IF l_agreement_in_rec.start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE Then
632 l_agreement_in_rec.start_date := NULL;
633 END IF;
634
635 IF l_agreement_in_rec.Billing_sequence = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
636 l_agreement_in_rec.Billing_sequence := NUll;
637 END IF;
638
639 IF l_agreement_in_rec.line_of_account = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
640 l_agreement_in_rec.line_of_account := NULL;
641 END IF;
642
643 IF l_agreement_in_rec.attribute11 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
644 l_agreement_in_rec.attribute11 := NULL;
645 END IF;
646
647 IF l_agreement_in_rec.attribute12 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
648 l_agreement_in_rec.attribute12 := NULL;
649 END IF;
650
651 IF l_agreement_in_rec.attribute13 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
652 l_agreement_in_rec.attribute13 := NULL;
653 END IF;
654
655 IF l_agreement_in_rec.attribute14 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
656 l_agreement_in_rec.attribute14 := NULL;
657 END IF;
658
659 IF l_agreement_in_rec.attribute15 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
660 l_agreement_in_rec.attribute15 := NULL;
661 END IF;
662
663 IF l_agreement_in_rec.attribute16 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
664 l_agreement_in_rec.attribute16 := NULL;
665 END IF;
666
667 IF l_agreement_in_rec.attribute17 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
668 l_agreement_in_rec.attribute17 := NULL;
669 END IF;
670
671 IF l_agreement_in_rec.attribute18 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
672 l_agreement_in_rec.attribute18 := NULL;
673 END IF;
674
675 IF l_agreement_in_rec.attribute19 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
676 l_agreement_in_rec.attribute19 := NULL;
677 END IF;
678
679 IF l_agreement_in_rec.attribute20 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
680 l_agreement_in_rec.attribute20 := NULL;
681 END IF;
682
683 IF l_agreement_in_rec.attribute21 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
684 l_agreement_in_rec.attribute21 := NULL;
685 END IF;
686
687 IF l_agreement_in_rec.attribute22 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
688 l_agreement_in_rec.attribute22 := NULL;
689 END IF;
690
691 IF l_agreement_in_rec.attribute23 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
692 l_agreement_in_rec.attribute23 := NULL;
693 END IF;
694
695 IF l_agreement_in_rec.attribute24 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
696 l_agreement_in_rec.attribute24 := NULL;
697 END IF;
698
699 IF l_agreement_in_rec.attribute25 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
700 l_agreement_in_rec.attribute25 := NULL;
701 END IF;
702
703 -- CREATE AGREEMENT
704 pa_agreement_utils.create_agreement
705 (p_rowid => l_row_id
706 ,p_agreement_id => l_out_agreement_id
707 ,p_customer_id => l_agreement_in_rec.customer_id
708 ,p_agreement_num => l_agreement_in_rec.agreement_num
709 ,p_agreement_type => l_agreement_in_rec.agreement_type
710 ,p_last_update_date => SYSDATE
711 ,p_last_updated_by => G_USER_ID
712 ,p_creation_date => SYSDATE
713 ,p_created_by => G_USER_ID
714 ,p_last_update_login => G_LOGIN_ID
715 ,p_owned_by_person_id => l_agreement_in_rec.owned_by_person_id
716 ,p_term_id => l_agreement_in_rec.term_id
717 ,p_revenue_limit_flag => l_agreement_in_rec.revenue_limit_flag
718 ,p_amount => l_agreement_in_rec.amount
719 ,p_description => l_agreement_in_rec.description
720 ,p_expiration_date => l_agreement_in_rec.expiration_date
721 ,p_attribute_category => l_agreement_in_rec.attribute_category
722 ,p_attribute1 => l_agreement_in_rec.attribute1
723 ,p_attribute2 => l_agreement_in_rec.attribute2
724 ,p_attribute3 => l_agreement_in_rec.attribute3
725 ,p_attribute4 => l_agreement_in_rec.attribute4
726 ,p_attribute5 => l_agreement_in_rec.attribute5
727 ,p_attribute6 => l_agreement_in_rec.attribute6
728 ,p_attribute7 => l_agreement_in_rec.attribute7
729 ,p_attribute8 => l_agreement_in_rec.attribute8
730 ,p_attribute9 => l_agreement_in_rec.attribute9
731 ,p_attribute10 => l_agreement_in_rec.attribute10
732 ,p_template_flag => l_agreement_in_rec.template_flag
733 ,p_pm_agreement_reference => l_agreement_in_rec.pm_agreement_reference
734 ,p_pm_product_code => p_pm_product_code
735 /* MCB2 params begin */
736 ,p_owning_organization_id => l_agreement_in_rec.owning_organization_id
737 ,p_agreement_currency_code => l_agreement_in_rec.agreement_currency_code
738 ,p_invoice_limit_flag => l_agreement_in_rec.invoice_limit_flag
739 /*Federal*/
740 ,p_customer_order_number=> l_agreement_in_rec.customer_order_number
741 ,p_advance_required => l_agreement_in_rec.advance_required
742 ,p_start_date => l_agreement_in_rec.start_date
743 ,p_billing_sequence => l_agreement_in_rec.billing_sequence
744 ,p_line_of_account => l_agreement_in_rec.line_of_account
745 ,p_attribute11 => l_agreement_in_rec.attribute11
746 ,p_attribute12 => l_agreement_in_rec.attribute12
747 ,p_attribute13 => l_agreement_in_rec.attribute13
748 ,p_attribute14 => l_agreement_in_rec.attribute14
749 ,p_attribute15 => l_agreement_in_rec.attribute15
750 ,p_attribute16 => l_agreement_in_rec.attribute16
751 ,p_attribute17 => l_agreement_in_rec.attribute17
752 ,p_attribute18 => l_agreement_in_rec.attribute18
753 ,p_attribute19 => l_agreement_in_rec.attribute19
754 ,p_attribute20 => l_agreement_in_rec.attribute20
755 ,p_attribute21 => l_agreement_in_rec.attribute21
756 ,p_attribute22 => l_agreement_in_rec.attribute22
757 ,p_attribute23 => l_agreement_in_rec.attribute23
758 ,p_attribute24 => l_agreement_in_rec.attribute24
759 ,p_attribute25 => l_agreement_in_rec.attribute25);
760 /* MCB2 params end */
761
762 p_agreement_out_rec.agreement_id:=l_out_agreement_id; /* Bug 2440551 */
763
764 -- ADD FUNDING
765 i := p_funding_in_tbl.first;
766
767 WHILE i IS NOT NULL LOOP
768 --Move the incoming record to a local record
769 l_funding_in_rec := p_funding_in_tbl(i);
770 /* NOT REQUIRED
771 --Get the unique Funding ID for this funding
772 l_funding_id := p_funding_out_tbl(i).project_funding_id;
773 */
774 /* Added the below check to create an agreement even when no funding is there. Bug 5734567*/
775 if (l_funding_in_rec.project_id is NULL
776 or l_funding_in_rec.date_allocated is NULL
777 or l_funding_in_rec.allocated_amount is NULL
778 --or l_funding_in_rec.funding_category is NULL /* Commented for the bug 6780803 */
779 or l_funding_in_rec.project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
780 or l_funding_in_rec.date_allocated = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
781 or l_funding_in_rec.allocated_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
782 --or l_funding_in_rec.funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) /* Commented for the bug 6780803 */
783 then
784 --dbms_output.put_line('Funding not entered. ');
785 exit;
786 end if;
787 /* End of Changes for Bug 5734567 */
788
789
790 /* Bug 2437469- BEGIN */
791
792 SELECT invproc_currency_type INTO l_invproc_currency_type
793 FROM pa_projects_all
794 WHERE project_id=l_funding_in_rec.project_id;
795
796 IF l_invproc_currency_type='FUNDING_CURRENCY'
797 THEN
798 BEGIN
799 l_valid_currency:='Y';
800 SELECT distinct funding_currency_code INTO l_old_fund_curr_code
801 FROM pa_summary_project_fundings
802 WHERE project_id=l_funding_in_rec.project_id
803 and not (total_baselined_amount = 0
804 and total_unbaselined_amount = 0); /* Added for bug 6510026 */
805
806 IF (l_old_fund_curr_code<>l_agreement_in_rec.agreement_currency_code)
807 THEN
808 l_valid_currency:='N';
809 END IF;
810
811 EXCEPTION
812 WHEN TOO_MANY_ROWS THEN
813 l_valid_currency:='N';
814 WHEN NO_DATA_FOUND THEN
815 Null;
816 WHEN OTHERS THEN
817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
818 END;
819
820 IF l_valid_currency='N' THEN
821 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
822 THEN
823 pa_interface_utils_pub.map_new_amg_msg
824 ( p_old_message_code => 'PA_CURR_NOT_VALID'
825 ,p_msg_attribute => 'CHANGE'
826 ,p_resize_flag => 'N'
827 ,p_msg_context => 'AGREEMENT'
828 ,p_attribute1 => l_agreement_in_rec.pm_agreement_reference
829 ,p_attribute2 => ''
830 ,p_attribute3 => ''
831 ,p_attribute4 => ''
832 ,p_attribute5 => '');
833 END IF;
834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
835 END IF;
836 END IF;
837
838 /* Bug 2437469- END */
839
840
841 pa_agreement_pub.add_funding
842 (p_api_version_number => p_api_version_number
843 ,p_commit => p_commit
844 ,p_init_msg_list => p_init_msg_list
845 ,p_msg_count => p_msg_count
846 ,p_msg_data => p_msg_data
847 ,p_return_status => l_return_status
848 ,p_pm_product_code => p_pm_product_code
849 ,p_pm_funding_reference => l_funding_in_rec.pm_funding_reference
850 ,p_funding_id => l_funding_in_rec.project_funding_id
851 ,p_pa_project_id => l_funding_in_rec.project_id
852 ,p_pa_task_id => l_funding_in_rec.task_id
853 ,p_agreement_id => l_out_agreement_id
854 ,p_allocated_amount => l_funding_in_rec.allocated_amount
855 ,p_date_allocated => l_funding_in_rec.date_allocated
856 ,p_desc_flex_name => l_funding_in_rec.desc_flex_name
857 ,p_attribute_category => l_funding_in_rec.attribute_category
858 ,p_attribute1 => l_funding_in_rec.attribute1
859 ,p_attribute2 => l_funding_in_rec.attribute2
860 ,p_attribute3 => l_funding_in_rec.attribute3
861 ,p_attribute4 => l_funding_in_rec.attribute4
862 ,p_attribute5 => l_funding_in_rec.attribute5
863 ,p_attribute6 => l_funding_in_rec.attribute6
864 ,p_attribute7 => l_funding_in_rec.attribute7
865 ,p_attribute8 => l_funding_in_rec.attribute8
866 ,p_attribute9 => l_funding_in_rec.attribute9
867 ,p_attribute10 => l_funding_in_rec.attribute10
868 ,p_funding_id_out => p_funding_out_tbl(i).project_funding_id
869 /* MCB2 params begin */
870 ,p_project_rate_type => l_funding_in_rec.project_rate_type
871 ,p_project_rate_date => l_funding_in_rec.project_rate_date
872 ,p_project_exchange_rate => l_funding_in_rec.project_exchange_rate
873 ,p_projfunc_rate_type => l_funding_in_rec.projfunc_rate_type
874 ,p_projfunc_rate_date => l_funding_in_rec.projfunc_rate_date
875 ,p_projfunc_exchange_rate => l_funding_in_rec.projfunc_exchange_rate
876 /* MCB2 params end */
877 ,p_funding_category => l_funding_in_rec.funding_category /* For Bug2244796 */
878 );
879
880
881 -- Assign the return_status to the fudning out record
882 p_funding_out_tbl(i).return_status := l_return_status;
883
884 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
885 THEN
886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
887
888 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
889 THEN
890 -- p_multiple_funding_msg := 'F';
891 RAISE FND_API.G_EXC_ERROR;
892 END IF;
893
894 --Move to next funding in funding pl/sql table
895 i := p_funding_in_tbl.next(i);
896
897 END LOOP;
898
899 IF FND_API.to_boolean( p_commit )
900 THEN
901 COMMIT;
902 END IF;
903
904 --END IF;
905
906 EXCEPTION
907 WHEN FND_API.G_EXC_ERROR
908 THEN
909 ROLLBACK TO create_agreement_pub;
910 p_return_status := FND_API.G_RET_STS_ERROR;
911 FND_MSG_PUB.Count_And_Get
912 ( p_count => p_msg_count ,
913 p_data => p_msg_data );
914
915 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
916 THEN
917 ROLLBACK TO create_agreement_pub;
918 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
919 FND_MSG_PUB.Count_And_Get
920 ( p_count => p_msg_count ,
921 p_data => p_msg_data );
922
923 WHEN OTHERS
924 THEN
925 ROLLBACK TO create_agreement_pub;
926 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
927 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
928 THEN
929 FND_MSG_PUB.add_exc_msg
930 ( p_pkg_name => G_PKG_NAME
931 , p_procedure_name => l_api_name );
932
933 END IF;
934 FND_MSG_PUB.Count_And_Get
935 ( p_count => p_msg_count ,
936 p_data => p_msg_data );
937
938
939 END create_agreement;
940
941
942 -- =============================================================================
943 --
944 --Name: delete_agreement
945 --Type: Procedure
946 --Description: This procedure can be used to delete an agreement for an
947 -- existing project or template.
948 --
949 --Called subprograms:
950 -- pa_interface_utils_pub.map_new_amg_msg
951 -- pa_agreement_pub.check_delete_agreement_ok
952 -- pa_agreement_pvt.convert_ag_ref_to_id
953 -- pa_agreement_utils.delete_agreement
954
955 --
956 --
957 --
958 --History:
959 -- 25-MAR-2000 Rakesh Raghavan Created.
960 -- ---------------------------------------------------------------------------
961
962 PROCEDURE delete_agreement
963 (p_api_version_number IN NUMBER
964 ,p_commit IN VARCHAR2
965 ,p_init_msg_list IN VARCHAR2
966 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
967 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
968 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
969 ,p_pm_product_code IN VARCHAR2
970 ,p_pm_agreement_reference IN VARCHAR2
971 ,p_agreement_id IN NUMBER
972 )
973 IS
974 -- Local Cursors
975 CURSOR l_funding_id_csr(p_agreement_id NUMBER)
976 IS
977 SELECT project_funding_id,pm_funding_reference,agreement_id
978 FROM pa_project_fundings f
979 WHERE f.agreement_id = p_agreement_id;
980 -- Local variables
981 l_msg_count NUMBER ;
982 l_msg_data VARCHAR2(2000);
983 l_function_allowed VARCHAR2(1);
984 l_resp_id NUMBER := 0;
985 l_api_name CONSTANT VARCHAR2(30):= 'delete_agreement';
986 l_return_status VARCHAR2(1);
987 l_funding_id NUMBER;
988 l_out_agreement_id NUMBER ;
989 l_funding_id_rec l_funding_id_csr%ROWTYPE;
990 BEGIN
991 -- Standard begin of API savepoint
992
993 SAVEPOINT delete_agreement_pub;
994
995
996 -- Standard call to check for call compatibility.
997
998
999 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
1000 p_api_version_number ,
1001 l_api_name ,
1002 G_PKG_NAME )
1003 THEN
1004
1005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006
1007 END IF;
1008 l_resp_id := FND_GLOBAL.Resp_id;
1009
1010 -- Actions performed using the APIs would be subject to
1011 -- function security. If the responsibility does not allow
1012 -- such functions to be executed, the API should not proceed further
1013 -- since the user does not have access to such functions
1014
1015
1016 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
1017 (p_api_version_number => p_api_version_number,
1018 p_responsibility_id => l_resp_id,
1019 p_function_name => 'PA_AF_DELETE_AGREEMENT',
1020 p_msg_count => l_msg_count,
1021 p_msg_data => l_msg_data,
1022 p_return_status => l_return_status,
1023 p_function_allowed => l_function_allowed
1024 );
1025
1026 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1027 THEN
1028 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1029
1030 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1031 THEN
1032 RAISE FND_API.G_EXC_ERROR;
1033 END IF;
1034 IF l_function_allowed = 'N' THEN
1035 pa_interface_utils_pub.map_new_amg_msg
1036 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
1037 ,p_msg_attribute => 'CHANGE'
1038 ,p_resize_flag => 'Y'
1039 ,p_msg_context => 'GENERAL'
1040 ,p_attribute1 => ''
1041 ,p_attribute2 => ''
1042 ,p_attribute3 => ''
1043 ,p_attribute4 => ''
1044 ,p_attribute5 => '');
1045 p_return_status := FND_API.G_RET_STS_ERROR;
1046 RAISE FND_API.G_EXC_ERROR;
1047 END IF;
1048
1049 -- Initialize the message table if requested.
1050
1051 IF FND_API.TO_BOOLEAN( p_init_msg_list )
1052 THEN
1053
1054 FND_MSG_PUB.initialize;
1055
1056 END IF;
1057
1058 -- Set API return status to success
1059
1060 p_return_status := FND_API.G_RET_STS_SUCCESS;
1061
1062
1063 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
1064
1065 -- Product Code
1066 IF (p_pm_product_code IS NULL)
1067 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1068 THEN
1069 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1070 THEN
1071 pa_interface_utils_pub.map_new_amg_msg
1072 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISS'
1073 ,p_msg_attribute => 'CHANGE'
1074 ,p_resize_flag => 'N'
1075 ,p_msg_context => 'GENERAL'
1076 ,p_attribute1 => ''
1077 ,p_attribute2 => ''
1078 ,p_attribute3 => ''
1079 ,p_attribute4 => ''
1080 ,p_attribute5 => '');
1081 END IF;
1082 p_return_status := FND_API.G_RET_STS_ERROR;
1083 RAISE FND_API.G_EXC_ERROR;
1084 END IF;
1085
1086 -- Agreement Reference
1087 IF (p_pm_agreement_reference IS NULL)
1088 OR (p_pm_agreement_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1089 THEN
1090 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1091 THEN
1092 pa_interface_utils_pub.map_new_amg_msg
1093 ( p_old_message_code => 'PA_AGMT_REF_IS_MISS'
1094 ,p_msg_attribute => 'CHANGE'
1095 ,p_resize_flag => 'N'
1096 ,p_msg_context => 'GENERAL'
1097 ,p_attribute1 => ''
1098 ,p_attribute2 => ''
1099 ,p_attribute3 => ''
1100 ,p_attribute4 => ''
1101 ,p_attribute5 => '');
1102 END IF;
1103 p_return_status := FND_API.G_RET_STS_ERROR;
1104 RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106 /* NOT REQUIRED
1107 -- Agreement Id
1108 IF (p_agreement_id IS NULL)
1109 OR (p_agreement_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1110 THEN
1111 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1112 THEN
1113 pa_interface_utils_pub.map_new_amg_msg
1114 ( p_old_message_code => 'PA_AGMT_ID_IS_MISS'
1115 ,p_msg_attribute => 'CHANGE'
1116 ,p_resize_flag => 'N'
1117 ,p_msg_context => 'AGREEMENT'
1118 ,p_attribute1 => p_pm_agreement_reference
1119 ,p_attribute2 => ''
1120 ,p_attribute3 => ''
1121 ,p_attribute4 => ''
1122 ,p_attribute5 => '');
1123 END IF;
1124 p_return_status := FND_API.G_RET_STS_ERROR;
1125 RAISE FND_API.G_EXC_ERROR;
1126 END IF;
1127 */
1128 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
1129 pa_agreement_pvt.convert_ag_ref_to_id
1130 (p_pm_agreement_reference => p_pm_agreement_reference
1131 ,p_af_agreement_id => p_agreement_id
1132 ,p_out_agreement_id => l_out_agreement_id
1133 ,p_return_status => l_return_status);
1134 p_return_status := l_return_status;
1135 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1136 THEN
1137 IF l_return_status = FND_API.G_RET_STS_ERROR
1138 THEN
1139 RAISE FND_API.G_EXC_ERROR;
1140 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1141 THEN
1142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1143 ELSE
1144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1145 END IF;
1146 END IF;
1147
1148 -- VALIDATE THE INCOMING PARAMETERS
1149 IF pa_agreement_pvt.check_delete_agreement_ok
1150 (p_agreement_id => p_agreement_id
1151 ,p_pm_agreement_reference => p_pm_agreement_reference) = 'N'
1152 THEN
1153 p_return_status := FND_API.G_RET_STS_ERROR;
1154 RAISE FND_API.G_EXC_ERROR;
1155 END IF;
1156
1157 -- DELETE CORRESPONDING FUNDINGS FOR THE AGREEMENT
1158 OPEN l_funding_id_csr( l_out_agreement_id);
1159 LOOP
1160 FETCH l_funding_id_csr INTO l_funding_id_rec;
1161 IF l_funding_id_csr%NOTFOUND
1162 THEN
1163 EXIT;
1164 ELSE
1165 pa_agreement_pub.delete_funding
1166 ( p_api_version_number => p_api_version_number
1167 ,p_commit => p_commit
1168 ,p_init_msg_list => p_init_msg_list
1169 ,p_msg_count => p_msg_count
1170 ,p_msg_data => p_msg_data
1171 ,p_return_status => p_return_status
1172 ,p_pm_product_code => p_pm_product_code
1173 ,p_pm_funding_reference => l_funding_id_rec.pm_funding_reference
1174 ,p_funding_id => l_funding_id
1175 ,p_check_y_n => 'N' );
1176 IF p_return_status <> FND_API.G_RET_STS_SUCCESS
1177 THEN
1178 IF p_return_status = FND_API.G_RET_STS_ERROR
1179 THEN
1180 RAISE FND_API.G_EXC_ERROR;
1181 ELSIF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1182 THEN
1183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1184 ELSE
1185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1186 END IF;
1187 END IF;
1188 END IF;
1189 END LOOP;
1190 CLOSE l_funding_id_csr;
1191
1192 -- DELETE AGREEMENT
1193
1194 pa_agreement_utils.delete_agreement
1195 (p_agreement_id => l_out_agreement_id);
1196
1197 IF FND_API.to_boolean( p_commit )
1198 THEN
1199 COMMIT;
1200 END IF;
1201
1202 EXCEPTION
1203 WHEN FND_API.G_EXC_ERROR
1204 THEN
1205 ROLLBACK TO delete_agreement_pub;
1206 p_return_status := FND_API.G_RET_STS_ERROR;
1207 FND_MSG_PUB.Count_And_Get
1208 ( p_count => p_msg_count ,
1209 p_data => p_msg_data );
1210
1211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1212 THEN
1213 ROLLBACK TO delete_agreement_pub;
1214 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1215 FND_MSG_PUB.Count_And_Get
1216 ( p_count => p_msg_count ,
1217 p_data => p_msg_data );
1218
1219 WHEN ROW_ALREADY_LOCKED
1220 THEN
1221 ROLLBACK TO delete_agreement_pub;
1222 p_return_status := FND_API.G_RET_STS_ERROR;
1223 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1224 THEN
1225 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED_P_AMG');
1226 FND_MESSAGE.SET_TOKEN('AGREEMENT',p_pm_agreement_reference);
1227 FND_MSG_PUB.ADD;
1228 END IF;
1229 FND_MSG_PUB.Count_And_Get
1230 ( p_count => p_msg_count ,
1231 p_data => p_msg_data );
1232
1233 WHEN OTHERS
1234 THEN
1235 ROLLBACK TO delete_agreement_pub;
1236 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1237 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1238 THEN
1239 FND_MSG_PUB.add_exc_msg
1240 ( p_pkg_name => G_PKG_NAME
1241 , p_procedure_name => l_api_name );
1242
1243 END IF;
1244 FND_MSG_PUB.Count_And_Get
1245 ( p_count => p_msg_count ,
1246 p_data => p_msg_data );
1247
1248
1249 END delete_agreement;
1250
1251 -- ============================================================================
1252 --
1253 --Name: update_agreement
1254 --Type: Procedure
1255 --Description: This procedure can be used to update an agreement for an
1256 -- existing project or template.
1257 --
1258 --Called subprograms:
1259 -- pa_interface_utils_pub.map_new_amg_msg
1260 -- pa_agreement_pub.check_update_agreement_ok
1261 -- pa_agreement_utils.validate_flex_fields
1262 -- pa_agreement_pvt.convert_ag_ref_to_id
1263 -- pa_agreement_utils.update_agreement
1264 -- pa_agreement_pub.add_funding
1265 --
1266 --
1267 --
1268 --History:
1269 -- 25-MAR-2000 Rakesh Raghavan Created.
1270 -- 10-SEP-2001 Srividya MCB2 code changes
1271 -- ---------------------------------------------------------------------------
1272
1273 PROCEDURE update_agreement
1274 (p_api_version_number IN NUMBER
1275 ,p_commit IN VARCHAR2
1276 ,p_init_msg_list IN VARCHAR2
1277 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
1278 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
1279 ,p_return_status OUT NOCOPY VARCHAR2
1280 ,p_pm_product_code IN VARCHAR2
1281 ,p_agreement_in_rec IN Agreement_Rec_In_Type
1282 ,p_agreement_out_rec OUT NOCOPY Agreement_Rec_Out_Type /*File.sql.39*/
1283 ,p_funding_in_tbl IN funding_in_tbl_type
1284 ,p_funding_out_tbl OUT NOCOPY funding_out_tbl_type /*File.sql.39*/
1285 )
1286 IS
1287 -- Local variables
1288 l_msg_count NUMBER ;
1289 l_msg_data VARCHAR2(2000);
1290 l_function_allowed VARCHAR2(1);
1291 l_resp_id NUMBER := 0;
1292 l_out_agreement_id NUMBER ;
1293 l_return_status VARCHAR2(1);
1294 l_api_name CONSTANT VARCHAR2(30):= 'update_agreement';
1295 i NUMBER;
1296 l_funding_in_rec funding_rec_in_type;
1297 l_return_msg VARCHAR2(2000);
1298 l_row_id VARCHAR2(2000);
1299 l_validate_status VARCHAR2(1);
1300 l_funding_id NUMBER;
1301 l_agreement_in_rec Agreement_Rec_In_Type; /* Added for Bug 3511175 */
1302 /* This l_agreement_in_rec will be used throughout this function
1303 in place of p_agreement_in_rec */
1304
1305 BEGIN
1306 -- Standard begin of API savepoint
1307
1308 SAVEPOINT update_agreement_pub;
1309
1310 l_agreement_in_rec:=p_agreement_in_rec;
1311 -- Standard call to check for call compatibility.
1312
1313
1314 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
1315 p_api_version_number ,
1316 l_api_name ,
1317 G_PKG_NAME )
1318 THEN
1319
1320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1321
1322 END IF;
1323 l_resp_id := FND_GLOBAL.Resp_id;
1324
1325 -- Actions performed using the APIs would be subject to
1326 -- function security. If the responsibility does not allow
1327 -- such functions to be executed, the API should not proceed further
1328 -- since the user does not have access to such functions
1329
1330
1331 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
1332 (p_api_version_number => p_api_version_number,
1333 p_responsibility_id => l_resp_id,
1334 p_function_name => 'PA_AF_UPDATE_AGREEMENT',
1335 p_msg_count => l_msg_count,
1336 p_msg_data => l_msg_data,
1337 p_return_status => l_return_status,
1338 p_function_allowed => l_function_allowed
1339 );
1340
1341 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1342 THEN
1343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1344
1345 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1346 THEN
1347 RAISE FND_API.G_EXC_ERROR;
1348 END IF;
1349 IF l_function_allowed = 'N' THEN
1350 pa_interface_utils_pub.map_new_amg_msg
1351 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
1352 ,p_msg_attribute => 'CHANGE'
1353 ,p_resize_flag => 'Y'
1354 ,p_msg_context => 'GENERAL'
1355 ,p_attribute1 => ''
1356 ,p_attribute2 => ''
1357 ,p_attribute3 => ''
1358 ,p_attribute4 => ''
1359 ,p_attribute5 => '');
1360 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
1361 RAISE FND_API.G_EXC_ERROR;
1362 END IF;
1363
1364 -- Initialize the message table if requested.
1365
1366 IF FND_API.TO_BOOLEAN( p_init_msg_list )
1367 THEN
1368
1369 FND_MSG_PUB.initialize;
1370
1371 END IF;
1372
1373 -- Set API return status to success
1374
1375 p_return_status := FND_API.G_RET_STS_SUCCESS;
1376 p_agreement_out_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1377
1378
1379 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
1380
1381 -- Product Code
1382 IF (p_pm_product_code IS NULL)
1383 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1384 THEN
1385 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1386 THEN
1387 pa_interface_utils_pub.map_new_amg_msg
1388 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISS'
1389 ,p_msg_attribute => 'CHANGE'
1390 ,p_resize_flag => 'N'
1391 ,p_msg_context => 'GENERAL'
1392 ,p_attribute1 => ''
1393 ,p_attribute2 => ''
1394 ,p_attribute3 => ''
1395 ,p_attribute4 => ''
1396 ,p_attribute5 => '');
1397 END IF;
1398 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
1399 p_return_status := FND_API.G_RET_STS_ERROR;
1400 RAISE FND_API.G_EXC_ERROR;
1401 END IF;
1402
1403 /*Commented the validation for non mandatory parameter 'pm_agreement_reference' for bug 6601566
1404 -- Agreement Reference
1405 IF (l_agreement_in_rec.pm_agreement_reference IS NULL)
1406 OR (l_agreement_in_rec.pm_agreement_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1407 THEN
1408 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1409 THEN
1410 pa_interface_utils_pub.map_new_amg_msg
1411 ( p_old_message_code => 'PA_AGMT_REF_IS_MISS'
1412 ,p_msg_attribute => 'CHANGE'
1413 ,p_resize_flag => 'N'
1414 ,p_msg_context => 'GENERAL'
1415 ,p_attribute1 => ''
1416 ,p_attribute2 => ''
1417 ,p_attribute3 => ''
1418 ,p_attribute4 => ''
1419 ,p_attribute5 => '');
1420 END IF;
1421 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
1422 p_return_status := FND_API.G_RET_STS_ERROR;
1423 RAISE FND_API.G_EXC_ERROR;
1424 END IF;
1425 Commenting ends here */
1426
1427 /* Commented the validations for non mandatory parameters for bug 6602451
1428 -- Customer Id
1429 IF (l_agreement_in_rec.customer_ID IS NULL)
1430 OR (l_agreement_in_rec.customer_ID = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1431 THEN
1432 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1433 THEN
1434 pa_interface_utils_pub.map_new_amg_msg
1435 ( p_old_message_code => 'PA_CUST_ID_IS_MISS'
1436 ,p_msg_attribute => 'CHANGE'
1437 ,p_resize_flag => 'N'
1438 ,p_msg_context => 'AGREEMENT'
1439 ,p_attribute1 => l_agreement_in_rec.pm_agreement_reference
1440 ,p_attribute2 => ''
1441 ,p_attribute3 => ''
1442 ,p_attribute4 => ''
1443 ,p_attribute5 => '');
1444 END IF;
1445 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
1446 p_return_status := FND_API.G_RET_STS_ERROR;
1447 RAISE FND_API.G_EXC_ERROR;
1448 END IF;
1449
1450 -- Customer Number
1451 IF (l_agreement_in_rec.customer_num IS NULL)
1452 OR (l_agreement_in_rec.customer_num = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1453 THEN
1454 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1455 THEN
1456 pa_interface_utils_pub.map_new_amg_msg
1457 ( p_old_message_code => 'PA_CUST_NUM_IS_MISS'
1458 ,p_msg_attribute => 'CHANGE'
1459 ,p_resize_flag => 'N'
1460 ,p_msg_context => 'AGREEMENT'
1461 ,p_attribute1 => l_agreement_in_rec.pm_agreement_reference
1462 ,p_attribute2 => ''
1463 ,p_attribute3 => ''
1464 ,p_attribute4 => ''
1465 ,p_attribute5 => '');
1466 END IF;
1467 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
1468 p_return_status := FND_API.G_RET_STS_ERROR;
1469 RAISE FND_API.G_EXC_ERROR;
1470 END IF;
1471
1472 -- Agreement Type
1473 IF (l_agreement_in_rec.agreement_type IS NULL)
1474 OR (l_agreement_in_rec.agreement_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1475 THEN
1476 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1477 THEN
1478 pa_interface_utils_pub.map_new_amg_msg
1479 ( p_old_message_code => 'PA_AGMT_TYPE_IS_MISS'
1480 ,p_msg_attribute => 'CHANGE'
1481 ,p_resize_flag => 'N'
1482 ,p_msg_context => 'AGREEMENT'
1483 ,p_attribute1 => l_agreement_in_rec.pm_agreement_reference
1484 ,p_attribute2 => ''
1485 ,p_attribute3 => ''
1486 ,p_attribute4 => ''
1487 ,p_attribute5 => '');
1488 END IF;
1489 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
1490 p_return_status := FND_API.G_RET_STS_ERROR;
1491 RAISE FND_API.G_EXC_ERROR;
1492 END IF;
1493
1494 -- Agreement Number
1495 IF (l_agreement_in_rec.agreement_num IS NULL)
1496 OR (l_agreement_in_rec.agreement_num = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1497 THEN
1498 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1499 THEN
1500 pa_interface_utils_pub.map_new_amg_msg
1501 ( p_old_message_code => 'PA_AGMT_NUM_IS_MISS'
1502 ,p_msg_attribute => 'CHANGE'
1503 ,p_resize_flag => 'N'
1504 ,p_msg_context => 'AGREEMENT'
1505 ,p_attribute1 => l_agreement_in_rec.pm_agreement_reference
1506 ,p_attribute2 => ''
1507 ,p_attribute3 => ''
1508 ,p_attribute4 => ''
1509 ,p_attribute5 => '');
1510 END IF;
1511 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
1512 p_return_status := FND_API.G_RET_STS_ERROR;
1513 RAISE FND_API.G_EXC_ERROR;
1514 END IF;
1515 Code commented ends here */
1516
1517 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
1518 pa_agreement_pvt.convert_ag_ref_to_id
1519 (p_pm_agreement_reference => l_agreement_in_rec.pm_agreement_reference
1520 ,p_af_agreement_id => l_agreement_in_rec.agreement_id
1521 ,p_out_agreement_id => l_out_agreement_id
1522 ,p_return_status => l_return_status);
1523 p_agreement_out_rec.return_status := l_return_status;
1524 p_return_status := l_return_status;
1525
1526
1527 /*Commented code for bug 7110396
1528 /* Start Bug 3511175 Initialized owning_organization_id,invoice_limit_flag
1529 and agreement_currency_code
1530 IF l_agreement_in_rec.owning_organization_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
1531 l_agreement_in_rec.owning_organization_id := NULL;
1532 END IF;
1533
1534 IF l_agreement_in_rec.invoice_limit_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1535 l_agreement_in_rec.invoice_limit_flag := 'N';
1536 END IF;
1537
1538 IF l_agreement_in_rec.agreement_currency_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Then
1539 SELECT pa_currency.get_currency_code
1540 INTO l_agreement_in_rec.agreement_currency_code from dual;
1541 END IF;
1542
1543 End- Bug 3511175 */
1544
1545
1546
1547
1548 -- VALIDATE THE INCOMING PARAMETERS
1549 /* This part of code moved from while loop to here as check for project funding id is commented
1550 out in check_update_agreement_ok */
1551 IF pa_agreement_pvt.check_update_agreement_ok
1552 (p_pm_agreement_reference => l_agreement_in_rec.pm_agreement_reference
1553 ,p_agreement_id => l_out_agreement_id
1554 ,p_funding_id => NULL
1555 ,p_customer_id => l_agreement_in_rec.customer_id
1556 ,p_agreement_type => l_agreement_in_rec.agreement_type
1557 ,p_term_id => l_agreement_in_rec.term_id
1558 ,p_template_flag => l_agreement_in_rec.template_flag
1559 ,p_revenue_limit_flag => l_agreement_in_rec.revenue_limit_flag
1560 ,p_owned_by_person_id => l_agreement_in_rec.owned_by_person_id
1561 /* MCB2 params begin */
1562 ,p_owning_organization_id => l_agreement_in_rec.owning_organization_id
1563 ,p_agreement_currency_code => l_agreement_in_rec.agreement_currency_code
1564 ,p_invoice_limit_flag => l_agreement_in_rec.invoice_limit_flag
1565 /* MCB2 params end */
1566 /*Federal*/
1567 ,p_start_date => p_agreement_in_rec.start_date
1568 ,p_end_date => p_agreement_in_rec.expiration_date
1569 ,p_advance_required => p_agreement_in_rec.advance_required
1570 ,p_billing_sequence => p_agreement_in_rec.billing_sequence
1571 ,p_amount => p_agreement_in_rec.amount
1572 ) = 'N'
1573 THEN
1574 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
1575 p_return_status := FND_API.G_RET_STS_ERROR;
1576 RAISE FND_API.G_EXC_ERROR;
1577 END IF;
1578 i := p_funding_in_tbl.first;
1579 WHILE i IS NOT NULL LOOP
1580 --Move the incoming record to a local record
1581 l_funding_in_rec := p_funding_in_tbl(i);
1582
1583 --Move to next funding in funding pl/sql table
1584 i := p_funding_in_tbl.next(i);
1585
1586 /* Added the below check to update an agreement even when no funding is there. Bug 5734567 */
1587 if (l_funding_in_rec.project_id is NULL
1588 or l_funding_in_rec.date_allocated is NULL
1589 or l_funding_in_rec.allocated_amount is NULL
1590 --or l_funding_in_rec.funding_category is NULL /* Commented for the bug 6780803 */
1591 or l_funding_in_rec.project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1592 or l_funding_in_rec.date_allocated = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1593 or l_funding_in_rec.allocated_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1594 --or l_funding_in_rec.funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) /* Commented for the bug 6780803 */
1595 then
1596 --dbms_output.put_line('Funding not entered. ');
1597 exit;
1598 end if;
1599 /* End of Changes for Bug 5734567 */
1600
1601 END LOOP;
1602
1603
1604 -- Flex Field Validations
1605
1606 /** Bug 2180350 - Replaced OR with AND in the following IF conmdition **/
1607
1608 IF (l_agreement_in_rec.desc_flex_name IS NOT NULL)
1609 AND (l_agreement_in_rec.desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1610 THEN
1611 pa_agreement_pvt.validate_flex_fields
1612 (p_desc_flex_name => l_agreement_in_rec.desc_flex_name
1613 ,p_attribute_category => l_agreement_in_rec.attribute_category
1614 ,p_attribute1 => l_agreement_in_rec.attribute1
1615 ,p_attribute2 => l_agreement_in_rec.attribute2
1616 ,p_attribute3 => l_agreement_in_rec.attribute3
1617 ,p_attribute4 => l_agreement_in_rec.attribute4
1618 ,p_attribute5 => l_agreement_in_rec.attribute5
1619 ,p_attribute6 => l_agreement_in_rec.attribute6
1620 ,p_attribute7 => l_agreement_in_rec.attribute7
1621 ,p_attribute8 => l_agreement_in_rec.attribute8
1622 ,p_attribute9 => l_agreement_in_rec.attribute9
1623 ,p_attribute10 => l_agreement_in_rec.attribute10
1624 ,p_attribute11 => p_agreement_in_rec.attribute11
1625 ,p_attribute12 => p_agreement_in_rec.attribute12
1626 ,p_attribute13 => p_agreement_in_rec.attribute13
1627 ,p_attribute14 => p_agreement_in_rec.attribute14
1628 ,p_attribute15 => p_agreement_in_rec.attribute15
1629 ,p_attribute16 => p_agreement_in_rec.attribute16
1630 ,p_attribute17 => p_agreement_in_rec.attribute17
1631 ,p_attribute18 => p_agreement_in_rec.attribute18
1632 ,p_attribute19 => p_agreement_in_rec.attribute19
1633 ,p_attribute20 => p_agreement_in_rec.attribute20
1634 ,p_attribute21 => p_agreement_in_rec.attribute21
1635 ,p_attribute22 => p_agreement_in_rec.attribute22
1636 ,p_attribute23 => p_agreement_in_rec.attribute23
1637 ,p_attribute24 => p_agreement_in_rec.attribute24
1638 ,p_attribute25 => p_agreement_in_rec.attribute25
1639 ,p_return_msg => l_return_msg
1640 ,p_validate_status => l_validate_status
1641 );
1642 IF l_validate_status = 'N'
1643 THEN
1644 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1645 THEN
1646 pa_interface_utils_pub.map_new_amg_msg
1647 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
1648 ,p_msg_attribute => 'CHANGE'
1649 ,p_resize_flag => 'N'
1650 ,p_msg_context => 'FLEX'
1651 ,p_attribute1 => l_return_msg
1652 ,p_attribute2 => ''
1653 ,p_attribute3 => ''
1654 ,p_attribute4 => ''
1655 ,p_attribute5 => '');
1656 END IF;
1657 p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
1658 p_return_status := FND_API.G_RET_STS_ERROR;
1659 RAISE FND_API.G_EXC_ERROR;
1660 END IF;
1661 END IF;
1662
1663
1664 -- UPDATE AGREEMENT
1665 pa_agreement_utils.update_agreement
1666 (p_agreement_id => l_out_agreement_id
1667 ,p_customer_id => l_agreement_in_rec.customer_id
1668 ,p_agreement_num => l_agreement_in_rec.agreement_num
1669 ,p_agreement_type => l_agreement_in_rec.agreement_type
1670 ,p_last_update_date => SYSDATE
1671 ,p_last_updated_by => G_USER_ID
1672 ,p_last_update_login => G_LOGIN_ID
1673 ,p_owned_by_person_id => l_agreement_in_rec.owned_by_person_id
1674 ,p_term_id => l_agreement_in_rec.term_id
1675 ,p_revenue_limit_flag => l_agreement_in_rec.revenue_limit_flag
1676 ,p_amount => l_agreement_in_rec.amount
1677 ,p_description => l_agreement_in_rec.description
1678 ,p_expiration_date => l_agreement_in_rec.expiration_date
1679 ,p_attribute_category => l_agreement_in_rec.attribute_category
1680 ,p_attribute1 => l_agreement_in_rec.attribute1
1681 ,p_attribute2 => l_agreement_in_rec.attribute2
1682 ,p_attribute3 => l_agreement_in_rec.attribute3
1683 ,p_attribute4 => l_agreement_in_rec.attribute4
1684 ,p_attribute5 => l_agreement_in_rec.attribute5
1685 ,p_attribute6 => l_agreement_in_rec.attribute6
1686 ,p_attribute7 => l_agreement_in_rec.attribute7
1687 ,p_attribute8 => l_agreement_in_rec.attribute8
1688 ,p_attribute9 => l_agreement_in_rec.attribute9
1689 ,p_attribute10 => l_agreement_in_rec.attribute10
1690 ,p_template_flag => l_agreement_in_rec.template_flag
1691 ,p_pm_agreement_reference => l_agreement_in_rec.pm_agreement_reference
1692 ,p_pm_product_code => p_pm_product_code
1693 /* MCB2 params begin */
1694 ,p_owning_organization_id => l_agreement_in_rec.owning_organization_id
1695 ,p_agreement_currency_code => l_agreement_in_rec.agreement_currency_code
1696 ,p_invoice_limit_flag => l_agreement_in_rec.invoice_limit_flag
1697 /* MCB2 params end */
1698 /*Federal*/
1699 ,p_customer_order_number=> l_agreement_in_rec.customer_order_number
1700 ,p_advance_required => l_agreement_in_rec.advance_required
1701 ,p_start_date => l_agreement_in_rec.start_date
1702 ,p_billing_sequence => l_agreement_in_rec.billing_sequence
1703 ,p_line_of_account => l_agreement_in_rec.line_of_account
1704 ,p_attribute11 => l_agreement_in_rec.attribute11
1705 ,p_attribute12 => l_agreement_in_rec.attribute12
1706 ,p_attribute13 => l_agreement_in_rec.attribute13
1707 ,p_attribute14 => l_agreement_in_rec.attribute14
1708 ,p_attribute15 => l_agreement_in_rec.attribute15
1709 ,p_attribute16 => l_agreement_in_rec.attribute16
1710 ,p_attribute17 => l_agreement_in_rec.attribute17
1711 ,p_attribute18 => l_agreement_in_rec.attribute18
1712 ,p_attribute19 => l_agreement_in_rec.attribute19
1713 ,p_attribute20 => l_agreement_in_rec.attribute20
1714 ,p_attribute21 => l_agreement_in_rec.attribute21
1715 ,p_attribute22 => l_agreement_in_rec.attribute22
1716 ,p_attribute23 => l_agreement_in_rec.attribute23
1717 ,p_attribute24 => l_agreement_in_rec.attribute24
1718 ,p_attribute25 => l_agreement_in_rec.attribute25
1719 );
1720
1721 i := p_funding_in_tbl.first;
1722 WHILE i IS NOT NULL LOOP
1723 --Move the incoming record to a local record
1724 l_funding_in_rec := p_funding_in_tbl(i);
1725 --Get the unique Funding ID for this funding
1726 /* NOT REQUIRED
1727 l_funding_id := p_funding_out_tbl(i).project_funding_id;
1728 */
1729
1730 /* Added the below check to update an agreement even when no funding is there. Bug 5734567 */
1731 if (l_funding_in_rec.project_id is NULL
1732 or l_funding_in_rec.date_allocated is NULL
1733 or l_funding_in_rec.allocated_amount is NULL
1734 --or l_funding_in_rec.funding_category is NULL /* Commented for the bug 6780803 */
1735 or l_funding_in_rec.project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1736 or l_funding_in_rec.date_allocated = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1737 or l_funding_in_rec.allocated_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1738 --or l_funding_in_rec.funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) /* Commented for the bug 6780803 */
1739 then
1740 --dbms_output.put_line('Funding not entered. ');
1741 exit;
1742 end if;
1743 /* End of Changes for Bug 5734567 */
1744
1745 IF pa_agreement_pvt.check_add_update(p_funding_reference => l_funding_in_rec.pm_funding_reference)='U'
1746 THEN
1747 -- UPDATE FUNDING
1748 pa_agreement_pub.update_funding
1749 (p_api_version_number => p_api_version_number
1750 ,p_commit => p_commit
1751 ,p_init_msg_list => p_init_msg_list
1752 ,p_msg_count => p_msg_count
1753 ,p_msg_data => p_msg_data
1754 ,p_return_status => l_return_status
1755 ,p_pm_product_code => p_pm_product_code
1756 ,p_pm_funding_reference => l_funding_in_rec.pm_funding_reference
1757 ,p_funding_id => l_funding_in_rec.project_funding_id
1758 ,p_project_id => l_funding_in_rec.project_id
1759 ,p_task_id => l_funding_in_rec.task_id
1760 ,p_agreement_id => l_out_agreement_id
1761 ,p_allocated_amount => l_funding_in_rec.allocated_amount
1762 ,p_date_allocated => l_funding_in_rec.date_allocated
1763 ,p_desc_flex_name => l_funding_in_rec.desc_flex_name
1764 ,p_attribute_category => l_funding_in_rec.attribute_category
1765 ,p_attribute1 => l_funding_in_rec.attribute1
1766 ,p_attribute2 => l_funding_in_rec.attribute2
1767 ,p_attribute3 => l_funding_in_rec.attribute3
1768 ,p_attribute4 => l_funding_in_rec.attribute4
1769 ,p_attribute5 => l_funding_in_rec.attribute5
1770 ,p_attribute6 => l_funding_in_rec.attribute6
1771 ,p_attribute7 => l_funding_in_rec.attribute7
1772 ,p_attribute8 => l_funding_in_rec.attribute8
1773 ,p_attribute9 => l_funding_in_rec.attribute9
1774 ,p_attribute10 => l_funding_in_rec.attribute10
1775 ,p_funding_id_out => p_funding_out_tbl(i).project_funding_id
1776 /* MCB2 params begin */
1777 ,p_project_rate_type => l_funding_in_rec.project_rate_type
1778 ,p_project_rate_date => l_funding_in_rec.project_rate_date
1779 ,p_project_exchange_rate => l_funding_in_rec.project_exchange_rate
1780 ,p_projfunc_rate_type => l_funding_in_rec.projfunc_rate_type
1781 ,p_projfunc_rate_date => l_funding_in_rec.projfunc_rate_date
1782 ,p_projfunc_exchange_rate => l_funding_in_rec.projfunc_exchange_rate
1783 /* MCB2 params end */
1784 ,p_funding_category => l_funding_in_rec.funding_category /* For Bug2244796 */
1785 );
1786
1787 -- Assign the return_status to the task out record
1788 p_funding_out_tbl(i).return_status := l_return_status;
1789
1790 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1791 THEN
1792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1793
1794 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1795 THEN
1796 -- p_multiple_funding_msg := 'F';
1797 RAISE FND_API.G_EXC_ERROR;
1798 END IF;
1799
1800 ELSIF pa_agreement_pvt.check_add_update(p_funding_reference => l_funding_in_rec.pm_funding_reference)='A'
1801 THEN
1802 -- ADD FUNDING
1803 pa_agreement_pub.add_funding
1804 (p_api_version_number => p_api_version_number
1805 ,p_commit => p_commit
1806 ,p_init_msg_list => p_init_msg_list
1807 ,p_msg_count => p_msg_count
1808 ,p_msg_data => p_msg_data
1809 ,p_return_status => l_return_status
1810 ,p_pm_product_code => p_pm_product_code
1811 ,p_pm_funding_reference => l_funding_in_rec.pm_funding_reference
1812 ,p_funding_id => l_funding_in_rec.project_funding_id
1813 ,p_pa_project_id => l_funding_in_rec.project_id
1814 ,p_pa_task_id => l_funding_in_rec.task_id
1815 ,p_agreement_id => l_out_agreement_id
1816 ,p_allocated_amount => l_funding_in_rec.allocated_amount
1817 ,p_date_allocated => l_funding_in_rec.date_allocated
1818 ,p_desc_flex_name => l_funding_in_rec.desc_flex_name
1819 ,p_attribute_category => l_funding_in_rec.attribute_category
1820 ,p_attribute1 => l_funding_in_rec.attribute1
1821 ,p_attribute2 => l_funding_in_rec.attribute2
1822 ,p_attribute3 => l_funding_in_rec.attribute3
1823 ,p_attribute4 => l_funding_in_rec.attribute4
1824 ,p_attribute5 => l_funding_in_rec.attribute5
1825 ,p_attribute6 => l_funding_in_rec.attribute6
1826 ,p_attribute7 => l_funding_in_rec.attribute7
1827 ,p_attribute8 => l_funding_in_rec.attribute8
1828 ,p_attribute9 => l_funding_in_rec.attribute9
1829 ,p_attribute10 => l_funding_in_rec.attribute10
1830 ,p_funding_id_out => p_funding_out_tbl(i).project_funding_id
1831 /* MCB2 params begin */
1832 ,p_project_rate_type => l_funding_in_rec.project_rate_type
1833 ,p_project_rate_date => l_funding_in_rec.project_rate_date
1834 ,p_project_exchange_rate => l_funding_in_rec.project_exchange_rate
1835 ,p_projfunc_rate_type => l_funding_in_rec.projfunc_rate_type
1836 ,p_projfunc_rate_date => l_funding_in_rec.projfunc_rate_date
1837 ,p_projfunc_exchange_rate => l_funding_in_rec.projfunc_exchange_rate
1838 /* MCB2 params end */
1839 ,p_funding_category => l_funding_in_rec.funding_category /* For Bug2244796 */
1840 );
1841 -- Assign the return_status to the task out record
1842 p_funding_out_tbl(i).return_status := l_return_status;
1843
1844 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1845 THEN
1846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1847
1848 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1849 THEN
1850 -- p_multiple_funding_msg := 'F';
1851 RAISE FND_API.G_EXC_ERROR;
1852 END IF;
1853
1854 END IF;
1855
1856 --Move to next funding in funding pl/sql table
1857 i := p_funding_in_tbl.next(i);
1858
1859 END LOOP;
1860
1861 IF FND_API.to_boolean( p_commit )
1862 THEN
1863 COMMIT;
1864 END IF;
1865
1866 EXCEPTION
1867 WHEN FND_API.G_EXC_ERROR
1868 THEN
1869 p_return_status := FND_API.G_RET_STS_ERROR ;
1870 ROLLBACK TO update_agreement_pub;
1871 FND_MSG_PUB.Count_And_Get
1872 ( p_count => p_msg_count ,
1873 p_data => p_msg_data );
1874
1875
1876 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1877 THEN
1878 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1879 ROLLBACK TO update_agreement_pub;
1880 FND_MSG_PUB.Count_And_Get
1881 ( p_count => p_msg_count ,
1882 p_data => p_msg_data );
1883
1884 WHEN NO_DATA_FOUND
1885 THEN
1886 pa_interface_utils_pub.map_new_amg_msg
1887 ( p_old_message_code => 'PA_NO_DATA_FOUND'
1888 ,p_msg_attribute => 'CHANGE'
1889 ,p_resize_flag => 'N'
1890 ,p_msg_context => 'GENERAL'
1891 ,p_attribute1 => ''
1892 ,p_attribute2 => ''
1893 ,p_attribute3 => ''
1894 ,p_attribute4 => ''
1895 ,p_attribute5 => '');
1896
1897 WHEN ROW_ALREADY_LOCKED THEN
1898 ROLLBACK TO update_project_pub;
1899 p_return_status := FND_API.G_RET_STS_ERROR;
1900 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1901 THEN
1902 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED_P_AMG');
1903 FND_MESSAGE.SET_TOKEN('AGREEMENT',l_agreement_in_rec.pm_agreement_reference);
1904 FND_MESSAGE.SET_TOKEN('FUNDING',l_funding_in_rec.pm_funding_reference);
1905 FND_MSG_PUB.ADD;
1906 END IF;
1907
1908 FND_MSG_PUB.Count_And_Get
1909 ( p_count => p_msg_count ,
1910 p_data => p_msg_data );
1911
1912 WHEN OTHERS
1913 THEN
1914 ROLLBACK TO update_agreement_pub;
1915 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1916 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1917 THEN
1918 FND_MSG_PUB.add_exc_msg
1919 ( p_pkg_name => G_PKG_NAME
1920 , p_procedure_name => l_api_name );
1921 END IF;
1922 FND_MSG_PUB.Count_And_Get
1923 ( p_count => p_msg_count ,
1924 p_data => p_msg_data );
1925
1926
1927 END update_agreement;
1928
1929
1930 -- ============================================================================
1931 --
1932 --Name: add_funding
1933 --Type: Procedure
1934 --Description: This procedure can be used to create funding for an existing for an
1935 -- agreement.
1936 --
1937 --Called subprograms:
1938 -- pa_interface_utils_pub.map_new_amg_msg
1939 -- pa_agreement_pub.check_add_funding_ok
1940 -- pa_agreement_utils.validate_flex_fields
1941 -- pa_agreement_pvt.convert_fu_ref_to_id
1942 -- pa_agreement_utils.add_funding
1943 --
1944 --
1945 --
1946 --History:
1947 -- 25-MAR-2000 Rakesh Raghavan Created.
1948 -- 11-JUN-2002 Raji - Modified for Bug 2403652
1949 -- ---------------------------------------------------------------------------
1950
1951 PROCEDURE add_funding
1952 (p_api_version_number IN NUMBER
1953 ,p_commit IN VARCHAR2
1954 ,p_init_msg_list IN VARCHAR2
1955 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
1956 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
1957 ,p_return_status OUT NOCOPY VARCHAR2/*File.sql.39*/
1958 ,p_pm_product_code IN VARCHAR2
1959 ,p_pm_funding_reference IN VARCHAR2
1960 ,p_funding_id IN OUT NOCOPY NUMBER /*File.sql.39*/
1961 ,p_pa_project_id IN NUMBER
1962 ,p_pa_task_id IN NUMBER
1963 ,p_agreement_id IN NUMBER
1964 ,p_allocated_amount IN NUMBER
1965 ,p_date_allocated IN DATE
1966 ,p_desc_flex_name IN VARCHAR2
1967 ,p_attribute_category IN VARCHAR2
1968 ,p_attribute1 IN VARCHAR2
1969 ,p_attribute2 IN VARCHAR2
1970 ,p_attribute3 IN VARCHAR2
1971 ,p_attribute4 IN VARCHAR2
1972 ,p_attribute5 IN VARCHAR2
1973 ,p_attribute6 IN VARCHAR2
1974 ,p_attribute7 IN VARCHAR2
1975 ,p_attribute8 IN VARCHAR2
1976 ,p_attribute9 IN VARCHAR2
1977 ,p_attribute10 IN VARCHAR2
1978 ,p_funding_id_out OUT NOCOPY NUMBER /*File.sql.39*/
1979 ,p_project_rate_type IN VARCHAR2 DEFAULT NULL
1980 ,p_project_rate_date IN DATE DEFAULT NULL
1981 ,p_project_exchange_rate IN NUMBER DEFAULT NULL
1982 ,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
1983 ,p_projfunc_rate_date IN DATE DEFAULT NULL
1984 ,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
1985 ,p_funding_category IN VARCHAR2 DEFAULT 'ADDITIONAL'
1986 /* Added for Bug 2483081 to include Default Value- For Bug 2244796 */
1987 )
1988 IS
1989 -- Local variables
1990 l_msg_count NUMBER ;
1991 l_validate_status VARCHAR2(1);
1992 l_msg_data VARCHAR2(2000);
1993 l_function_allowed VARCHAR2(1);
1994 l_resp_id NUMBER := 0;
1995 l_return_status VARCHAR2(1);
1996 l_out_agreement_id NUMBER ;
1997 l_out_funding_id NUMBER;
1998 l_customer_id NUMBER;
1999 l_return_msg VARCHAR2(2000);
2000 l_row_id VARCHAR2(2000);
2001 l_api_name CONSTANT VARCHAR2(30):= 'add_funding';
2002 l_pa_task_id NUMBER;
2003 l_desc_flex_name VARCHAR2(240);
2004 l_attribute_category VARCHAR2(30);
2005 l_attribute1 VARCHAR2(150);
2006 l_attribute2 VARCHAR2(150);
2007 l_attribute3 VARCHAR2(150);
2008 l_attribute4 VARCHAR2(150);
2009 l_attribute5 VARCHAR2(150);
2010 l_attribute6 VARCHAR2(150);
2011 l_attribute7 VARCHAR2(150);
2012 l_attribute8 VARCHAR2(150);
2013 l_attribute9 VARCHAR2(150);
2014 l_attribute10 VARCHAR2(150);
2015 l_resize_flag VARCHAR2(1); /* Added for bug 2902096 */
2016 l_funding_category VARCHAR2(30); /* Added for bug 2838872 */
2017
2018 /*Start of bug 5554070*/
2019 l_project_rate_type PA_PROJECT_FUNDINGS.PROJECT_RATE_TYPE%type;
2020 l_projfunc_rate_type PA_PROJECT_FUNDINGS.PROJFUNC_RATE_TYPE%type;
2021 l_projfunc_rate_date PA_PROJECT_FUNDINGS.PROJFUNC_RATE_DATE%type;
2022 l_project_rate_date PA_PROJECT_FUNDINGS.PROJECT_RATE_DATE%type;
2023 /*End of bug 5554070*/
2024
2025 l_err_code number;
2026 l_err_msg VARCHAR2(50);
2027
2028 /* Bug 13434695 */
2029
2030 l_rev_limit_flag VARCHAR2(1);
2031 l_inv_limit_flag VARCHAR2(1);
2032 l_accrued_amt NUMBER(22,5);
2033 l_billed_amt NUMBER(22,5);
2034 l_total_funding NUMBER(22,5);
2035
2036 CURSOR l_limit_flag_csr(p_agreement_id NUMBER)
2037 IS
2038 SELECT a.revenue_limit_flag,a.invoice_limit_flag
2039 FROM pa_agreements_all a
2040 WHERE a.agreement_id = p_agreement_id;
2041
2042 /*CURSOR l_amounts_csr(p_agreement_id NUMBER)
2043 IS
2044 SELECT f.projfunc_accrued_amount,f.projfunc_billed_amount,
2045 (f.projfunc_baselined_amount+f.projfunc_unbaselined_amount) total_funding
2046 FROM pa_summary_project_fundings f
2047 WHERE f.agreement_id = p_agreement_id;
2048 Rewritten for bug 14696063 */
2049 CURSOR l_amounts_csr(p_agreement_id NUMBER,p_project_id NUMBER, p_task_id NUMBER)
2050 IS
2051 SELECT f.total_accrued_amount,f.total_billed_amount,
2052 (f.total_baselined_amount+f.total_unbaselined_amount) total_funding
2053 FROM pa_summary_project_fundings f
2054 WHERE f.agreement_id = p_agreement_id
2055 AND f.project_id = p_project_id
2056 AND NVL(f.task_id,-1)=NVL(p_task_id,-1);/*Re written for bug 14696063*/
2057 /* Bug 13434695 */
2058
2059
2060 BEGIN
2061 -- Standard begin of API savepoint
2062
2063 SAVEPOINT add_funding_pub;
2064
2065
2066 -- Standard call to check for call compatibility.
2067
2068
2069 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
2070 p_api_version_number ,
2071 l_api_name ,
2072 G_PKG_NAME )
2073 THEN
2074
2075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2076
2077 END IF;
2078 l_resp_id := FND_GLOBAL.Resp_id;
2079
2080 -- Actions performed using the APIs would be subject to
2081 -- function security. If the responsibility does not allow
2082 -- such functions to be executed, the API should not proceed further
2083 -- since the user does not have access to such functions
2084
2085 -- This call is added for patchset K project role based security check
2086
2087 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := p_pa_project_id;
2088
2089
2090 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
2091 (p_api_version_number => p_api_version_number,
2092 p_responsibility_id => l_resp_id,
2093 p_function_name => 'PA_AF_ADD_FUNDING',
2094 p_msg_count => l_msg_count,
2095 p_msg_data => l_msg_data,
2096 p_return_status => l_return_status,
2097 p_function_allowed => l_function_allowed
2098 );
2099
2100 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2101 THEN
2102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2103
2104 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2105 THEN
2106 RAISE FND_API.G_EXC_ERROR;
2107 END IF;
2108 IF l_function_allowed = 'N' THEN
2109 pa_interface_utils_pub.map_new_amg_msg
2110 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
2111 ,p_msg_attribute => 'CHANGE'
2112 ,p_resize_flag => 'Y'
2113 ,p_msg_context => 'GENERAL'
2114 ,p_attribute1 => ''
2115 ,p_attribute2 => ''
2116 ,p_attribute3 => ''
2117 ,p_attribute4 => ''
2118 ,p_attribute5 => '');
2119 p_return_status := FND_API.G_RET_STS_ERROR;
2120 RAISE FND_API.G_EXC_ERROR;
2121 END IF;
2122
2123 -- Initialize the message table if requested.
2124
2125 IF FND_API.TO_BOOLEAN( p_init_msg_list )
2126 THEN
2127
2128 FND_MSG_PUB.initialize;
2129
2130 END IF;
2131
2132 -- Set API return status to success
2133
2134 p_return_status := FND_API.G_RET_STS_SUCCESS;
2135
2136 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
2137
2138
2139 -- Product Code
2140 IF (p_pm_product_code IS NULL)
2141 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2142 THEN
2143 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2144 THEN
2145 pa_interface_utils_pub.map_new_amg_msg
2146 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISS'
2147 ,p_msg_attribute => 'CHANGE'
2148 ,p_resize_flag => 'N'
2149 ,p_msg_context => 'GENERAL'
2150 ,p_attribute1 => ''
2151 ,p_attribute2 => ''
2152 ,p_attribute3 => ''
2153 ,p_attribute4 => ''
2154 ,p_attribute5 => '');
2155 END IF;
2156 p_return_status := FND_API.G_RET_STS_ERROR;
2157 RAISE FND_API.G_EXC_ERROR;
2158 END IF;
2159
2160 /* NOT REQUIRED
2161 -- Agreement Reference
2162 IF (p_pm_agreement_reference IS NULL)
2163 OR (p_pm_agreement_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2164 THEN
2165 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2166 THEN
2167 pa_interface_utils_pub.map_new_amg_msg
2168 ( p_old_message_code => 'PA_AGMT_REF_IS_MISS'
2169 ,p_msg_attribute => 'CHANGE'
2170 ,p_resize_flag => 'N'
2171 ,p_msg_context => 'GENERAL'
2172 ,p_attribute1 => ''
2173 ,p_attribute2 => ''
2174 ,p_attribute3 => ''
2175 ,p_attribute4 => ''
2176 ,p_attribute5 => '');
2177 END IF;
2178 p_return_status := FND_API.G_RET_STS_ERROR;
2179 RAISE FND_API.G_EXC_ERROR;
2180 END IF;
2181
2182
2183 -- Project Reference
2184 IF (p_pm_project_reference IS NULL)
2185 OR (p_pm_project_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2186 THEN
2187 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2188 THEN
2189 pa_interface_utils_pub.map_new_amg_msg
2190 ( p_old_message_code => 'PA_PROJ_REF_IS_MISS'
2191 ,p_msg_attribute => 'CHANGE'
2192 ,p_resize_flag => 'N'
2193 ,p_msg_context => 'GENERAL'
2194 ,p_attribute1 => ''
2195 ,p_attribute2 => ''
2196 ,p_attribute3 => ''
2197 ,p_attribute4 => ''
2198 ,p_attribute5 => '');
2199 END IF;
2200 p_return_status := FND_API.G_RET_STS_ERROR;
2201 RAISE FND_API.G_EXC_ERROR;
2202 END IF;
2203 */
2204
2205 -- Code added for Bug 2403652 starts here
2206
2207 -- Funding category
2208 /* Commented for 2483081 */
2209 /* IF (p_funding_category IS NULL)
2210 OR (p_funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2211 THEN
2212 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2213 THEN
2214 pa_interface_utils_pub.map_new_amg_msg
2215 ( p_old_message_code => 'PA_FUND_CAT_IS_MISS'
2216 ,p_msg_attribute => 'CHANGE'
2217 ,p_resize_flag => 'N'
2218 ,p_msg_context => 'GENERAL'
2219 ,p_attribute1 => ''
2220 ,p_attribute2 => ''
2221 ,p_attribute3 => ''
2222 ,p_attribute4 => ''
2223 ,p_attribute5 => '');
2224 END IF;
2225 p_return_status := FND_API.G_RET_STS_ERROR;
2226 RAISE FND_API.G_EXC_ERROR;
2227 END IF;
2228 */
2229
2230 -- Validate funding category
2231 /* Added IF condition for bug 2838872 */
2232 IF (p_funding_category IS NULL)
2233 OR (p_funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2234 THEN
2235 l_funding_category := 'ADDITIONAL';
2236 ELSE
2237 l_funding_category := p_funding_category;
2238 END IF;
2239
2240 IF pa_agreement_pvt.check_funding_category
2241 (p_pa_project_id
2242 ,p_pa_task_id
2243 ,p_agreement_id
2244 ,p_pm_funding_reference
2245 ,l_funding_category ) = 'N'
2246 THEN
2247 p_return_status := FND_API.G_RET_STS_ERROR;
2248 RAISE FND_API.G_EXC_ERROR;
2249 END IF;
2250
2251 -- Code added for Bug 2403652 ends here
2252
2253
2254 -- Funding Reference
2255 IF (p_pm_funding_reference IS NULL)
2256 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2257 THEN
2258 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2259 THEN
2260 pa_interface_utils_pub.map_new_amg_msg
2261 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
2262 ,p_msg_attribute => 'CHANGE'
2263 ,p_resize_flag => 'N'
2264 ,p_msg_context => 'GENERAL'
2265 ,p_attribute1 => ''
2266 ,p_attribute2 => ''
2267 ,p_attribute3 => ''
2268 ,p_attribute4 => ''
2269 ,p_attribute5 => '');
2270 END IF;
2271 p_return_status := FND_API.G_RET_STS_ERROR;
2272 RAISE FND_API.G_EXC_ERROR;
2273 END IF;
2274
2275 -- Project Id
2276 IF (p_pa_project_id IS NULL)
2277 OR (p_pa_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2278 THEN
2279 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2280 THEN
2281 pa_interface_utils_pub.map_new_amg_msg
2282 ( p_old_message_code => 'PA_PROJ_ID_IS_MISS'
2283 ,p_msg_attribute => 'CHANGE'
2284 ,p_resize_flag => 'N'
2285 ,p_msg_context => 'FUNDING'
2286 ,p_attribute1 => ''
2287 ,p_attribute2 => p_pm_funding_reference
2288 ,p_attribute3 => ''
2289 ,p_attribute4 => ''
2290 ,p_attribute5 => '');
2291 END IF;
2292 p_return_status := FND_API.G_RET_STS_ERROR;
2293 RAISE FND_API.G_EXC_ERROR;
2294 END IF;
2295
2296 -- Task Id
2297 /*
2298 IF (p_pa_task_id IS NULL)
2299 OR (p_pa_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2300 THEN
2301 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2302 THEN
2303 pa_interface_utils_pub.map_new_amg_msg
2304 ( p_old_message_code => 'PA_TASK_ID_IS_MISS'
2305 ,p_msg_attribute => 'CHANGE'
2306 ,p_resize_flag => 'N'
2307 ,p_msg_context => 'FUNDING'
2308 ,p_attribute1 => ''
2309 ,p_attribute2 => p_pm_funding_reference
2310 ,p_attribute3 => ''
2311 ,p_attribute4 => ''
2312 ,p_attribute5 => '');
2313 END IF;
2314 p_return_status := FND_API.G_RET_STS_ERROR;
2315 RAISE FND_API.G_EXC_ERROR;
2316 END IF;
2317 */
2318 -- Date Allocated
2319 IF (p_date_allocated IS NULL)
2320 OR (p_date_allocated = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
2321 THEN
2322 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2323 THEN
2324 pa_interface_utils_pub.map_new_amg_msg
2325 ( p_old_message_code => 'PA_DATE_ALLOC_IS_MISS'
2326 ,p_msg_attribute => 'CHANGE'
2327 ,p_resize_flag => 'N'
2328 ,p_msg_context => 'FUNDING'
2329 ,p_attribute1 => ''
2330 ,p_attribute2 => p_pm_funding_reference
2331 ,p_attribute3 => ''
2332 ,p_attribute4 => ''
2333 ,p_attribute5 => '');
2334 END IF;
2335 p_return_status := FND_API.G_RET_STS_ERROR;
2336 RAISE FND_API.G_EXC_ERROR;
2337 END IF;
2338
2339 -- Allocated Amount
2340 IF (p_allocated_amount IS NULL)
2341 OR (p_allocated_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2342 THEN
2343 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2344 THEN
2345 pa_interface_utils_pub.map_new_amg_msg
2346 ( p_old_message_code => 'PA_ALLOC_AMT_IS_MISS'
2347 ,p_msg_attribute => 'CHANGE'
2348 ,p_resize_flag => 'N'
2349 ,p_msg_context => 'FUNDING'
2350 ,p_attribute1 => ''
2351 ,p_attribute2 => p_pm_funding_reference
2352 ,p_attribute3 => ''
2353 ,p_attribute4 => ''
2354 ,p_attribute5 => '');
2355 END IF;
2356 p_return_status := FND_API.G_RET_STS_ERROR;
2357 RAISE FND_API.G_EXC_ERROR;
2358 -- ELSE
2359 -- select amount into l_nik from pa_agreements_all where agreement_id = p_agreement_id;
2360 END IF;
2361
2362 -- Agreement Id
2363 IF (p_agreement_id IS NULL)
2364 OR (p_agreement_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2365 THEN
2366 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2367 THEN
2368 pa_interface_utils_pub.map_new_amg_msg
2369 ( p_old_message_code => 'PA_AGMT_ID_IS_MISS'
2370 ,p_msg_attribute => 'CHANGE'
2371 ,p_resize_flag => 'N'
2372 ,p_msg_context => 'FUNDING'
2373 ,p_attribute1 => ''
2374 ,p_attribute2 => p_pm_funding_reference
2375 ,p_attribute3 => ''
2376 ,p_attribute4 => ''
2377 ,p_attribute5 => '');
2378 END IF;
2379 p_return_status := FND_API.G_RET_STS_ERROR;
2380 RAISE FND_API.G_EXC_ERROR;
2381 END IF;
2382
2383 /* Bug 13434695*/
2384 Open l_limit_flag_csr(p_agreement_id);
2385 Fetch l_limit_flag_csr into l_rev_limit_flag,l_inv_limit_flag;
2386 Close l_limit_flag_csr;
2387 /*Open l_amounts_csr(p_agreement_id); commented for bug 14696063 */
2388 Open l_amounts_csr(p_agreement_id,p_pa_project_id,p_pa_task_id);/*Added project id and task id for bug 14696063*/
2389 Fetch l_amounts_csr into l_accrued_amt,l_billed_amt,l_total_funding;
2390 Close l_amounts_csr;
2391
2392 IF (( (l_total_funding+p_allocated_amount) < l_accrued_amt and l_rev_limit_flag = 'Y') or
2393 ((l_total_funding+p_allocated_amount) < l_billed_amt and l_inv_limit_flag = 'Y'))
2394 THEN
2395
2396 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2397 THEN
2398 pa_interface_utils_pub.map_new_amg_msg
2399 ( p_old_message_code => 'PA_BU_AMT_ALLOC_LT_AMT_ACCRUED'
2400 ,p_msg_attribute => 'CHANGE'
2401 ,p_resize_flag => 'N'
2402 ,p_msg_context => 'FUNDING'
2403 ,p_attribute1 => ''
2404 ,p_attribute2 => p_pm_funding_reference
2405 ,p_attribute3 => ''
2406 ,p_attribute4 => ''
2407 ,p_attribute5 => '');
2408 END IF;
2409 p_return_status := FND_API.G_RET_STS_ERROR;
2410 RAISE FND_API.G_EXC_ERROR;
2411 END IF;
2412
2413 /* Bug 13434695*/
2414
2415 -- VALIDATE THE INCOMING PARAMETERS
2416 SELECT a. customer_id
2417 INTO l_customer_id
2418 FROM pa_agreements_all a
2419 WHERE a.agreement_id = p_agreement_id;
2420 IF pa_agreement_pvt.check_add_funding_ok
2421 (p_project_id => p_pa_project_id
2422 ,p_task_id => p_pa_task_id
2423 ,p_agreement_id => p_agreement_id
2424 ,p_pm_funding_reference => p_pm_funding_reference
2425 ,p_funding_amt => p_allocated_amount
2426 ,p_customer_id => l_customer_id
2427 /* MCB2 PARAMETERS BEGIN */
2428 ,p_project_rate_type => p_project_rate_type
2429 ,p_project_rate_date => p_project_rate_date
2430 ,p_project_exchange_rate => p_project_exchange_rate
2431 ,p_projfunc_rate_type => p_projfunc_rate_type
2432 ,p_projfunc_rate_date => p_projfunc_rate_date
2433 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate ) = 'N'
2434 /* MCB2 PARAMETERS END */
2435
2436 THEN
2437 p_return_status := FND_API.G_RET_STS_ERROR;
2438 RAISE FND_API.G_EXC_ERROR;
2439 END IF;
2440
2441
2442 -- Flex Field Validations
2443 IF (p_desc_flex_name IS NOT NULL)
2444 AND (p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2445 -- OR (p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2446 THEN
2447 pa_agreement_pvt.validate_flex_fields
2448 (p_desc_flex_name => p_desc_flex_name
2449 ,p_attribute_category => p_attribute_category
2450 ,p_attribute1 => p_attribute1
2451 ,p_attribute2 => p_attribute2
2452 ,p_attribute3 => p_attribute3
2453 ,p_attribute4 => p_attribute4
2454 ,p_attribute5 => p_attribute5
2455 /** ,p_attribute6 => p_attribute7 ** commented bug 2862922 **/
2456 ,p_attribute6 => p_attribute6 /** added bug 2862922 **/
2457 ,p_attribute7 => p_attribute7 /** added bug 2862922 **/
2458 ,p_attribute8 => p_attribute8
2459 ,p_attribute9 => p_attribute9
2460 ,p_attribute10 => p_attribute10
2461 ,p_return_msg => l_return_msg
2462 ,p_validate_status => l_validate_status
2463 );
2464 IF l_validate_status = 'N'
2465 THEN
2466 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2467 THEN
2468 pa_interface_utils_pub.map_new_amg_msg
2469 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
2470 ,p_msg_attribute => 'CHANGE'
2471 ,p_resize_flag => 'N'
2472 ,p_msg_context => 'FLEX'
2473 ,p_attribute1 => l_return_msg
2474 ,p_attribute2 => ''
2475 ,p_attribute3 => ''
2476 ,p_attribute4 => ''
2477 ,p_attribute5 => '');
2478 END IF;
2479 p_return_status := FND_API.G_RET_STS_ERROR;
2480 RAISE FND_API.G_EXC_ERROR;
2481 END IF;
2482 END IF;
2483
2484 /* NOT REQUIRED
2485 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
2486 pa_agreement_pvt.convert_ag_ref_to_id
2487 (p_pm_agreement_reference => p_pm_agreement_reference
2488 ,p_af_agreement_id => p_agreement_id
2489 ,p_out_agreement_id => l_out_agreement_id
2490 ,p_return_status => l_return_status);
2491 p_return_status := l_return_status;
2492
2493
2494 -- CONVERT PROJECT REFERENCE TO PROJECT ID
2495 pa_project_pvt.convert_pm_projref_to_id
2496 (p_pm_project_reference => p_pm_project_reference
2497 ,p_pa_project_id => p_project_id
2498 ,p_out_project_id => l_out_project_id
2499 ,p_return_status => l_return_status);
2500 p_return_status := l_return_status;
2501
2502 */
2503 /* NOT REQUIRED
2504 -- CONVERT FUNDING REFERENCE TO FUNDING ID
2505 pa_agreement_pvt.convert_fu_ref_to_id
2506 (p_pm_funding_reference => p_pm_funding_reference
2507 ,p_af_funding_id => p_funding_id
2508 ,p_out_funding_id => l_out_funding_id
2509 ,p_return_status => l_return_status);
2510 p_return_status := l_return_status;
2511 */
2512 --HERE
2513 --TO BE CORRECTED - BUDGET_TYPE_CODE - NIKHIL
2514 IF p_pa_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_pa_task_id IS NOT NULL THEN
2515 l_pa_task_id := p_pa_task_id;
2516 ELSIF p_pa_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM then
2517 l_pa_task_id := NULL;
2518 END IF;
2519
2520
2521 /* 2315767 Added Else condition for all the Flex Field Attributes
2522 so that proper value is passed if corresponding p_attribute's are not NULL
2523 or not the default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR */
2524 IF p_desc_flex_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2525 l_desc_flex_name:= NULL;
2526 ELSE
2527 l_desc_flex_name := p_desc_flex_name;
2528 END IF;
2529 IF p_attribute_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2530 l_attribute_category := NULL;
2531 ELSE
2532 l_attribute_category := p_attribute_category;
2533 END IF;
2534 IF p_attribute1 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2535 l_attribute1 := NULL;
2536 ELSE
2537 l_attribute1 := p_attribute1;
2538 END IF;
2539 IF p_attribute2 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2540 l_attribute2 := NULL;
2541 ELSE
2542 l_attribute2 := p_attribute2;
2543 END IF;
2544 IF p_attribute3 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2545 l_attribute3 := NULL;
2546 ELSE
2547 l_attribute3 := p_attribute3;/* 2315767 Modified l_attribute4 to l_attribute3 */
2548 END IF;
2549 /* 2315767 Added code for attribute4 of flexfield */
2550 IF p_attribute4 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2551 l_attribute4 := NULL;
2552 ELSE
2553 l_attribute4 := p_attribute4;
2554 END IF;
2555 IF p_attribute5 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2556 l_attribute5 := NULL;
2557 ELSE
2558 l_attribute5 := p_attribute5;
2559 END IF;
2560 IF p_attribute6 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2561 l_attribute6 := NULL;
2562 ELSE
2563 l_attribute6 := p_attribute6;
2564 END IF;
2565 IF p_attribute7 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2566 l_attribute7 := NULL;
2567 ELSE
2568 l_attribute7 := p_attribute7;
2569 END IF;
2570 IF p_attribute8 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2571 l_attribute8 := NULL;
2572 ELSE
2573 l_attribute8 := p_attribute8;
2574 END IF;
2575 IF p_attribute9 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2576 l_attribute9 := NULL;
2577 ELSE
2578 l_attribute9 := p_attribute9;
2579 END IF;
2580 IF p_attribute10 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2581 l_attribute10 := NULL;
2582 ELSE
2583 l_attribute10 := p_attribute10;
2584 END IF;
2585
2586 /* Changes for bug 5554070-Start */
2587
2588 IF p_project_rate_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2589 l_project_rate_type := null;
2590 else
2591 l_project_rate_type :=p_project_rate_type;
2592 end if;
2593
2594 If p_projfunc_rate_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2595 l_projfunc_rate_type :=null;
2596 else
2597 l_projfunc_rate_type := p_projfunc_rate_type;
2598 end if;
2599
2600 If p_projfunc_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
2601 l_projfunc_rate_date :=null;
2602 else
2603 l_projfunc_rate_date := p_projfunc_rate_date;
2604 end if;
2605
2606 If p_project_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
2607 l_project_rate_date :=null;
2608 else
2609 l_project_rate_date := p_project_rate_date;
2610 end if;
2611
2612 /* Changes for bug 5554070-End */
2613
2614
2615
2616 -- ADD FUNDING
2617 pa_agreement_utils.create_funding(
2618 p_Rowid => l_row_id,
2619 p_Project_Funding_Id => p_funding_id,
2620 p_Last_Update_Date => SYSDATE,
2621 p_Last_Updated_By => G_USER_ID,
2622 p_Creation_Date => SYSDATE,
2623 p_Created_By => G_USER_ID,
2624 p_Last_Update_Login => G_LOGIN_ID,
2625 p_Agreement_Id => p_agreement_id,
2626 p_Project_Id => p_pa_project_id,
2627 p_Task_Id => l_pa_task_id,
2628 p_Allocated_Amount => p_allocated_amount,
2629 p_Date_Allocated => p_date_allocated,
2630 p_Attribute_Category => l_attribute_category,
2631 p_Attribute1 => l_attribute1,
2632 p_Attribute2 => l_attribute2,
2633 p_Attribute3 => l_attribute3,
2634 p_Attribute4 => l_attribute4,
2635 p_Attribute5 => l_attribute5,
2636 p_Attribute6 => l_attribute6,
2637 p_Attribute7 => l_attribute7,
2638 p_Attribute8 => l_attribute8,
2639 p_Attribute9 => l_attribute9,
2640 p_Attribute10 => l_attribute10,
2641 p_pm_funding_reference => p_pm_funding_reference,
2642 p_pm_product_code => p_pm_product_code,
2643 /* MCB2 PARAMETERS BEGIN 5554070 Chaged to new introduced variable */
2644 p_project_rate_type => l_project_rate_type,
2645 p_project_rate_date => l_project_rate_date,
2646 p_project_exchange_rate => p_project_exchange_rate,
2647 p_projfunc_rate_type => l_projfunc_rate_type,
2648 p_projfunc_rate_date => l_projfunc_rate_date,
2649 p_projfunc_exchange_rate => p_projfunc_exchange_rate,
2650 x_err_code => l_err_code,
2651 x_err_msg => l_err_msg,
2652 p_funding_category => l_funding_category /* For Bug 2244796 */
2653 );
2654
2655 /* MCB2 PARAMETERS END */
2656
2657
2658 IF l_err_code <> 0 THEN
2659
2660 /* Following code added for bug 2902096 .The l_resize_flag is set to 'Y' only for some
2661 error messages whose length of message_name will go above 30 if '_AMG' is added to it.
2662 These messages can be used straightaway without using the '_AMG' as the message text is same .*/
2663 /* Added some more error messages in the IF condition for bug 2967759 */
2664 l_resize_flag:='N';
2665 IF (l_err_msg in ('PA_USR_RATE_NOT_ALLOWED_FC_PC','PA_USR_RATE_NOT_ALLOWED_FC_PF',
2666 'PA_USR_RATE_NOT_ALLOWED_BC_PF','PA_NO_EXCH_RATE_EXISTS_BC_PF',
2667 'PA_NO_EXCH_RATE_EXISTS_FC_PC','PA_NO_EXCH_RATE_EXISTS_FC_PF')) THEN
2668 l_resize_flag:='Y';
2669 END IF;
2670
2671 /* End - Bug 2902096 */
2672
2673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2674 THEN
2675 pa_interface_utils_pub.map_new_amg_msg
2676 ( p_old_message_code => l_err_msg
2677 ,p_msg_attribute => 'CHANGE'
2678 /* ,p_resize_flag => 'N' changed for bug 2902096 */
2679 ,p_resize_flag => l_resize_flag
2680 ,p_msg_context => 'FLEX'
2681 ,p_attribute1 => l_return_msg
2682 ,p_attribute2 => ''
2683 ,p_attribute3 => ''
2684 ,p_attribute4 => ''
2685 ,p_attribute5 => '');
2686 END IF;
2687 p_return_status := FND_API.G_RET_STS_ERROR;
2688 RAISE FND_API.G_EXC_ERROR;
2689
2690 ELSE
2691
2692 -- UPDATE SUMMARY PROJECT FUNDINGS
2693 pa_agreement_utils.summary_funding_insert_row
2694 (p_agreement_id => p_agreement_id
2695 ,p_project_id => p_pa_project_id
2696 ,p_task_id => p_pa_task_id
2697 ,p_login_id => G_LOGIN_ID
2698 ,p_user_id => G_USER_ID);
2699
2700 IF FND_API.to_boolean( p_commit ) THEN
2701 COMMIT;
2702 END IF;
2703
2704 END IF;
2705
2706 EXCEPTION
2707 WHEN FND_API.G_EXC_ERROR
2708 THEN
2709 ROLLBACK TO add_funding_pub;
2710 p_return_status := FND_API.G_RET_STS_ERROR;
2711 FND_MSG_PUB.Count_And_Get
2712 ( p_count => p_msg_count ,
2713 p_data => p_msg_data );
2714
2715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2716 THEN
2717 ROLLBACK TO add_funding_pub;
2718 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2719 FND_MSG_PUB.Count_And_Get
2720 ( p_count => p_msg_count ,
2721 p_data => p_msg_data );
2722
2723 WHEN OTHERS
2724 THEN
2725 ROLLBACK TO add_funding_pub;
2726 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2727 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2728 THEN
2729 FND_MSG_PUB.add_exc_msg
2730 ( p_pkg_name => G_PKG_NAME
2731 , p_procedure_name => l_api_name );
2732
2733 END IF;
2734 FND_MSG_PUB.Count_And_Get
2735 ( p_count => p_msg_count ,
2736 p_data => p_msg_data );
2737
2738 END add_funding;
2739
2740 -- ============================================================================
2741 --
2742 --Name: delete_funding
2743 --Type: Procedure
2744 --Description: This procedure can be used to delete funding for an existing for an
2745 -- agreement.
2746 --
2747 --Called subprograms:
2748 -- pa_interface_utils_pub.map_new_amg_msg
2749 -- pa_agreement_pub.check_delete_funding_ok
2750 -- pa_agreement_pvt.convert_fu_ref_to_id
2751 -- pa_agreement_utils.delete_agreement
2752 --
2753 --
2754 --
2755 --History:
2756 -- 25-MAR-2000 Rakesh Raghavan Created.
2757 -- ---------------------------------------------------------------------------
2758 PROCEDURE delete_funding
2759 (p_api_version_number IN NUMBER
2760 ,p_commit IN VARCHAR2
2761 ,p_init_msg_list IN VARCHAR2
2762 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
2763 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
2764 ,p_return_status OUT NOCOPY VARCHAR2/*File.sql.39*/
2765 ,p_pm_product_code IN VARCHAR2
2766 ,p_pm_funding_reference IN VARCHAR2
2767 ,p_funding_id IN NUMBER
2768 ,p_check_y_n IN VARCHAR2
2769 )
2770 IS
2771 -- Local variables
2772 l_msg_count NUMBER ;
2773 l_msg_data VARCHAR2(2000);
2774 l_function_allowed VARCHAR2(1);
2775 l_resp_id NUMBER := 0;
2776 l_funding_id NUMBER;
2777 l_out_funding_id NUMBER;
2778 l_agreement_id NUMBER;
2779 l_project_id NUMBER;
2780 l_task_id NUMBER;
2781 l_out_agreement_id NUMBER ;
2782 l_return_status VARCHAR2(1);
2783 l_api_name CONSTANT VARCHAR2(30):= 'delete_funding';
2784
2785 /* adding variables for bug 2868818*/
2786
2787 CURSOR c1
2788 IS
2789 SELECT f.funding_category
2790 FROM PA_PROJECT_FUNDINGS f
2791 WHERE f.pm_funding_reference = p_pm_funding_reference;
2792
2793 l_fund_rec1 c1%ROWTYPE;
2794 l_funding_category VARCHAR2(30);
2795
2796 /* end adding variables for bug 2868818*/
2797
2798 BEGIN
2799 -- Standard begin of API savepoint
2800
2801 SAVEPOINT delete_funding_pub;
2802
2803
2804 -- Standard call to check for call compatibility.
2805
2806
2807 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
2808 p_api_version_number ,
2809 l_api_name ,
2810 G_PKG_NAME )
2811 THEN
2812
2813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2814
2815 END IF;
2816 l_resp_id := FND_GLOBAL.Resp_id;
2817
2818
2819 -- This call is added for patchset K project role based security check
2820
2821 -- Get the project id from this project funding line
2822 l_project_id := pa_agreement_utils.get_project_id(p_funding_id => p_funding_id,
2823 p_funding_reference => p_pm_funding_reference);
2824
2825 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := l_project_id;
2826
2827 -- End of the security changes
2828
2829
2830 -- Actions performed using the APIs would be subject to
2831 -- function security. If the responsibility does not allow
2832 -- such functions to be executed, the API should not proceed further
2833 -- since the user does not have access to such functions
2834
2835
2836 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
2837 (p_api_version_number => p_api_version_number,
2838 p_responsibility_id => l_resp_id,
2839 p_function_name => 'PA_AF_DELETE_FUNDING',
2840 p_msg_count => l_msg_count,
2841 p_msg_data => l_msg_data,
2842 p_return_status => l_return_status,
2843 p_function_allowed => l_function_allowed
2844 );
2845
2846 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2847 THEN
2848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2849
2850 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2851 THEN
2852 RAISE FND_API.G_EXC_ERROR;
2853 END IF;
2854 IF l_function_allowed = 'N' THEN
2855 pa_interface_utils_pub.map_new_amg_msg
2856 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
2857 ,p_msg_attribute => 'CHANGE'
2858 ,p_resize_flag => 'Y'
2859 ,p_msg_context => 'GENERAL'
2860 ,p_attribute1 => ''
2861 ,p_attribute2 => ''
2862 ,p_attribute3 => ''
2863 ,p_attribute4 => ''
2864 ,p_attribute5 => '');
2865 p_return_status := FND_API.G_RET_STS_ERROR;
2866 RAISE FND_API.G_EXC_ERROR;
2867 END IF;
2868
2869 -- Initialize the message table if requested.
2870
2871 IF FND_API.TO_BOOLEAN( p_init_msg_list )
2872 THEN
2873
2874 FND_MSG_PUB.initialize;
2875
2876 END IF;
2877
2878 -- Set API return status to success
2879
2880 p_return_status := FND_API.G_RET_STS_SUCCESS;
2881
2882 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
2883
2884 -- Product Code
2885 IF (p_pm_product_code IS NULL)
2886 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2887 THEN
2888 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2889 THEN
2890 pa_interface_utils_pub.map_new_amg_msg
2891 ( p_old_message_code => 'PA_CUST_NUM_IS_MISS'
2892 ,p_msg_attribute => 'CHANGE'
2893 ,p_resize_flag => 'N'
2894 ,p_msg_context => 'GENERAL'
2895 ,p_attribute1 => ''
2896 ,p_attribute2 => ''
2897 ,p_attribute3 => ''
2898 ,p_attribute4 => ''
2899 ,p_attribute5 => '');
2900 END IF;
2901 p_return_status := FND_API.G_RET_STS_ERROR;
2902 RAISE FND_API.G_EXC_ERROR;
2903 END IF;
2904
2905 -- Funding Reference
2906 IF (p_pm_funding_reference IS NULL)
2907 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2908 THEN
2909 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2910 THEN
2911 pa_interface_utils_pub.map_new_amg_msg
2912 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
2913 ,p_msg_attribute => 'CHANGE'
2914 ,p_resize_flag => 'N'
2915 ,p_msg_context => 'GENERAL'
2916 ,p_attribute1 => ''
2917 ,p_attribute2 => ''
2918 ,p_attribute3 => ''
2919 ,p_attribute4 => ''
2920 ,p_attribute5 => '');
2921 END IF;
2922 p_return_status := FND_API.G_RET_STS_ERROR;
2923 RAISE FND_API.G_EXC_ERROR;
2924 END IF;
2925
2926 /* NOT REQUIRED
2927 -- Funding Id
2928 IF (p_funding_id IS NULL)
2929 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2930 THEN
2931 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2932 THEN
2933 pa_interface_utils_pub.map_new_amg_msg
2934 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
2935 ,p_msg_attribute => 'CHANGE'
2936 ,p_resize_flag => 'N'
2937 ,p_msg_context => 'FUNDING'
2938 ,p_attribute1 => ''
2939 ,p_attribute2 => p_pm_funding_reference
2940 ,p_attribute3 => ''
2941 ,p_attribute4 => ''
2942 ,p_attribute5 => '');
2943 END IF;
2944 p_return_status := FND_API.G_RET_STS_ERROR;
2945 RAISE FND_API.G_EXC_ERROR;
2946 END IF;
2947 */
2948 /* code change for bug 2868818*/
2949
2950 open c1;
2951 fetch c1 into l_fund_rec1;
2952 if c1%found then
2953 l_funding_category := l_fund_rec1.funding_category;
2954 end if;
2955 close c1;
2956
2957 IF l_funding_category = 'REVALUATION'
2958 THEN
2959 pa_interface_utils_pub.map_new_amg_msg
2960 ( p_old_message_code => 'PA_UPDATE_DELETE_REVAL'
2961 ,p_msg_attribute => 'CHANGE'
2962 ,p_resize_flag => 'N'
2963 ,p_msg_context => 'GENERAL'
2964 ,p_attribute1 => ''
2965 ,p_attribute2 => ''
2966 ,p_attribute3 => ''
2967 ,p_attribute4 => ''
2968 ,p_attribute5 => '');
2969
2970 p_return_status := FND_API.G_RET_STS_ERROR;
2971 RAISE FND_API.G_EXC_ERROR;
2972 END IF;
2973
2974 /* end code change for bug 2868818*/
2975
2976 -- - NIKHIL
2977 -- VALIDATE THE INCOMING PARAMETERS
2978 -- NIKHIL added if statement to make it a point while deleting funding for
2979 -- with an agreement it should not check for validity
2980 -- start
2981
2982 IF p_check_y_n = 'Y' THEN
2983 IF pa_agreement_pvt.check_delete_funding_ok
2984 (p_agreement_id => pa_agreement_utils.get_agreement_id( p_funding_id => p_funding_id
2985 ,p_funding_reference => p_pm_funding_reference)
2986 ,p_funding_id => pa_agreement_utils.get_funding_id(p_funding_reference => p_pm_funding_reference)
2987 ,p_pm_funding_reference => p_pm_funding_reference) = 'N'
2988 THEN
2989 p_return_status := FND_API.G_RET_STS_ERROR;
2990 RAISE FND_API.G_EXC_ERROR;
2991 END IF;
2992 END IF;
2993 -- End
2994 -- CONVERT FUNDING REFERENCE TO FUNDING ID
2995
2996 pa_agreement_pvt.convert_fu_ref_to_id
2997 (p_pm_funding_reference => p_pm_funding_reference
2998 ,p_af_funding_id => p_funding_id
2999 ,p_out_funding_id => l_out_funding_id
3000 ,p_return_status => l_return_status);
3001 p_return_status := l_return_status;
3002
3003
3004 -- GET THE VALUES OF AGREEMENT ID, FUNDING ID AND PROJECT ID BEFORE DELETING THE FUNDING
3005 l_agreement_id := pa_agreement_utils.get_agreement_id (p_funding_id => p_funding_id
3006 ,p_funding_reference => p_pm_funding_reference);
3007 l_project_id := pa_agreement_utils.get_project_id(p_funding_id => p_funding_id
3008 ,p_funding_reference => p_pm_funding_reference);
3009 l_task_id := pa_agreement_utils.get_task_id(p_funding_id => p_funding_id
3010 ,p_funding_reference => p_pm_funding_reference);
3011
3012
3013
3014 -- DELETE FUNDING
3015 pa_agreement_utils.delete_funding
3016 (p_project_funding_id =>l_out_funding_id);
3017
3018 -- TO BE CORRECTED - NIKHIL
3019 -- UPDATE SUMMARY PROJECT FUNDINGS
3020 pa_agreement_utils.summary_funding_delete_row
3021 (p_agreement_id => l_agreement_id
3022 ,p_project_id => l_project_id
3023 ,p_task_id => l_task_id
3024 ,p_login_id => G_LOGIN_ID
3025 ,p_user_id => G_USER_ID );
3026 IF FND_API.to_boolean( p_commit )
3027 THEN
3028 COMMIT;
3029 END IF;
3030
3031 EXCEPTION
3032 WHEN FND_API.G_EXC_ERROR
3033 THEN
3034 ROLLBACK TO delete_funding_pub;
3035 p_return_status := FND_API.G_RET_STS_ERROR;
3036 FND_MSG_PUB.Count_And_Get
3037 ( p_count => p_msg_count ,
3038 p_data => p_msg_data );
3039
3040 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3041 THEN
3042 ROLLBACK TO delete_funding_pub;
3043 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3044 FND_MSG_PUB.Count_And_Get
3045 ( p_count => p_msg_count ,
3046 p_data => p_msg_data );
3047
3048 WHEN ROW_ALREADY_LOCKED
3049 THEN
3050 ROLLBACK TO delete_funding_pub;
3051 p_return_status := FND_API.G_RET_STS_ERROR;
3052 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3053 THEN
3054 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED_T_AMG');
3055 FND_MESSAGE.SET_TOKEN('FUNDING',p_pm_funding_reference);
3056 FND_MSG_PUB.ADD;
3057 END IF;
3058 FND_MSG_PUB.Count_And_Get
3059 ( p_count => p_msg_count ,
3060 p_data => p_msg_data );
3061
3062 WHEN OTHERS
3063 THEN
3064 ROLLBACK TO delete_funding_pub;
3065 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3066 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3067 THEN
3068 FND_MSG_PUB.add_exc_msg
3069 ( p_pkg_name => G_PKG_NAME
3070 , p_procedure_name => l_api_name );
3071 END IF;
3072 FND_MSG_PUB.Count_And_Get
3073 ( p_count => p_msg_count ,
3074 p_data => p_msg_data );
3075
3076 END delete_funding;
3077
3078
3079 -- ============================================================================
3080 --
3081 --Name: update_funding
3082 --Type: Procedure
3083 --Description: This procedure can be used to update funding for an agreement.
3084 --
3085 --Called subprograms: XXX
3086 --
3087 --
3088 --
3089 --History:
3090 -- 25-MAR-2000 Rakesh Raghavan Created.
3091 -- 11-JUN-2002 Raji - code added for bug 2403652
3092 -- ---------------------------------------------------------------------------
3093
3094 PROCEDURE update_funding
3095 (p_api_version_number IN NUMBER
3096 ,p_commit IN VARCHAR2
3097 ,p_init_msg_list IN VARCHAR2
3098 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
3099 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
3100 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
3101 ,p_pm_product_code IN VARCHAR2
3102 ,p_pm_funding_reference IN VARCHAR2
3103 ,p_funding_id IN NUMBER
3104 ,p_project_id IN NUMBER
3105 ,p_task_id IN NUMBER
3106 ,p_agreement_id IN NUMBER
3107 ,p_allocated_amount IN NUMBER
3108 ,p_date_allocated IN DATE
3109 ,p_desc_flex_name IN VARCHAR2
3110 ,p_attribute_category IN VARCHAR2
3111 ,p_attribute1 IN VARCHAR2
3112 ,p_attribute2 IN VARCHAR2
3113 ,p_attribute3 IN VARCHAR2
3114 ,p_attribute4 IN VARCHAR2
3115 ,p_attribute5 IN VARCHAR2
3116 ,p_attribute6 IN VARCHAR2
3117 ,p_attribute7 IN VARCHAR2
3118 ,p_attribute8 IN VARCHAR2
3119 ,p_attribute9 IN VARCHAR2
3120 ,p_attribute10 IN VARCHAR2
3121 ,p_funding_id_out OUT NOCOPY NUMBER /*File.sql.39*/
3122 ,p_project_rate_type IN VARCHAR2 DEFAULT NULL
3123 ,p_project_rate_date IN DATE DEFAULT NULL
3124 ,p_project_exchange_rate IN NUMBER DEFAULT NULL
3125 ,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
3126 ,p_projfunc_rate_date IN DATE DEFAULT NULL
3127 ,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
3128 ,p_funding_category IN VARCHAR2 DEFAULT 'ADDITIONAL'
3129 /* Added for Bug 2512483 Default Value- For Bug 2244796 */
3130 )
3131 IS
3132 -- LOCAL VARIABLES
3133 l_msg_count NUMBER ;
3134 l_msg_data VARCHAR2(2000);
3135 l_function_allowed VARCHAR2(1);
3136 l_resp_id NUMBER := 0;
3137 l_return_msg VARCHAR2(2000);
3138 l_validate_status VARCHAR2(1);
3139 l_row_id VARCHAR2(2000);
3140 i NUMBER;
3141 l_funding_id NUMBER;
3142 l_out_funding_id NUMBER;
3143 l_out_agreement_id NUMBER ;
3144 l_return_status VARCHAR2(1);
3145 l_api_name CONSTANT VARCHAR2(30):= 'update_funding';
3146 l_resize_flag VARCHAR2(1); /* Added for bug 2902096 */
3147
3148 l_err_code number;
3149 l_err_msg VARCHAR2(50);
3150
3151 /* adding variables for bug 2868818*/
3152
3153 CURSOR c1
3154 IS
3155 SELECT f.funding_category
3156 FROM PA_PROJECT_FUNDINGS f
3157 WHERE f.pm_funding_reference = p_pm_funding_reference
3158 AND f.funding_category = 'REVALUATION';/* Added this condition for 3360593*/
3159
3160 l_fund_rec1 c1%ROWTYPE;
3161 l_funding_category VARCHAR2(30);
3162
3163 /* end adding variables for bug 2868818*/
3164
3165 /* Bug 13434695 */
3166
3167 l_rev_limit_flag VARCHAR2(1);
3168 l_inv_limit_flag VARCHAR2(1);
3169 l_accrued_amt NUMBER(22,5);
3170 l_billed_amt NUMBER(22,5);
3171 l_total_funding NUMBER(22,5);
3172 l_projfunc_allocated_amount NUMBER(22,5);
3173
3174 CURSOR l_limit_flag_csr(p_agreement_id NUMBER)
3175 IS
3176 SELECT revenue_limit_flag,invoice_limit_flag
3177 FROM pa_agreements_all a
3178 WHERE a.agreement_id = p_agreement_id;
3179
3180 /*CURSOR l_amounts_csr(p_agreement_id NUMBER)
3181 IS
3182 SELECT f.projfunc_accrued_amount,f.projfunc_billed_amount,
3183 (f.projfunc_baselined_amount+f.projfunc_unbaselined_amount) total_funding,
3184 p.projfunc_allocated_amount
3185 FROM pa_summary_project_fundings f,pa_project_fundings p
3186 WHERE f.agreement_id = p_agreement_id
3187 AND p.project_funding_id = p_funding_id; commented for bug 14696063 */
3188
3189 /* Bug 13434695 */
3190 CURSOR l_amounts_csr(p_agreement_id NUMBER)
3191 IS
3192 SELECT f.total_accrued_amount,f.total_billed_amount,
3193 (f.total_baselined_amount+f.total_unbaselined_amount) total_funding,
3194 p.allocated_amount
3195 FROM pa_summary_project_fundings f,pa_project_fundings p
3196 where F.AGREEMENT_ID =p_agreement_id
3197 and F.AGREEMENT_ID=P.AGREEMENT_ID
3198 and P.PROJECT_ID=F.PROJECT_ID
3199 and NVL(f.TASK_ID,-1)=NVL(p.TASK_ID,-1)
3200 AND p.project_funding_id = p_funding_id;
3201 /*Above cursor re-written for bug 14696063 * Added task id and project id in where clause*/
3202 BEGIN
3203 -- Standard begin of API savepoint
3204 SAVEPOINT update_funding_pub;
3205
3206
3207 -- Standard call to check for call compatibility.
3208
3209
3210 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3211 p_api_version_number ,
3212 l_api_name ,
3213 G_PKG_NAME )
3214 THEN
3215
3216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3217
3218 END IF;
3219 l_resp_id := FND_GLOBAL.Resp_id;
3220
3221 -- This call is added for patchset K project role based security check
3222
3223 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := p_project_id;
3224
3225
3226 -- Actions performed using the APIs would be subject to
3227 -- function security. If the responsibility does not allow
3228 -- such functions to be executed, the API should not proceed further
3229 -- since the user does not have access to such functions
3230
3231
3232 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
3233 (p_api_version_number => p_api_version_number,
3234 p_responsibility_id => l_resp_id,
3235 p_function_name => 'PA_AF_UPDATE_FUNDING',
3236 p_msg_count => l_msg_count,
3237 p_msg_data => l_msg_data,
3238 p_return_status => l_return_status,
3239 p_function_allowed => l_function_allowed
3240 );
3241
3242 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3243 THEN
3244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3245
3246 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3247 THEN
3248 RAISE FND_API.G_EXC_ERROR;
3249 END IF;
3250 IF l_function_allowed = 'N' THEN
3251 pa_interface_utils_pub.map_new_amg_msg
3252 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
3253 ,p_msg_attribute => 'CHANGE'
3254 ,p_resize_flag => 'Y'
3255 ,p_msg_context => 'GENERAL'
3256 ,p_attribute1 => ''
3257 ,p_attribute2 => ''
3258 ,p_attribute3 => ''
3259 ,p_attribute4 => ''
3260 ,p_attribute5 => '');
3261 RAISE FND_API.G_EXC_ERROR;
3262 END IF;
3263
3264 -- Initialize the message table if requested.
3265
3266 IF FND_API.TO_BOOLEAN( p_init_msg_list )
3267 THEN
3268
3269 FND_MSG_PUB.initialize;
3270
3271 END IF;
3272
3273 -- Set API return status to success
3274
3275 p_return_status := FND_API.G_RET_STS_SUCCESS;
3276
3277 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
3278
3279 -- Product Code
3280 IF (p_pm_product_code IS NULL)
3281 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3282 THEN
3283 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3284 THEN
3285 pa_interface_utils_pub.map_new_amg_msg
3286 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISS'
3287 ,p_msg_attribute => 'CHANGE'
3288 ,p_resize_flag => 'N'
3289 ,p_msg_context => 'GENERAL'
3290 ,p_attribute1 => ''
3291 ,p_attribute2 => ''
3292 ,p_attribute3 => ''
3293 ,p_attribute4 => ''
3294 ,p_attribute5 => '');
3295 END IF;
3296 p_return_status := FND_API.G_RET_STS_ERROR;
3297 RAISE FND_API.G_EXC_ERROR;
3298 END IF;
3299
3300 /* Bug 5686790. If Miss char is passed then this check is not required, as
3301 customer doesn't want to change the funding category*/
3302
3303 IF (p_funding_category <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
3304 -- Code added for Bug 2403652 starts here
3305
3306 -- Funding category is NULL
3307 /* Added IF condition for bug 2838872 */
3308 IF (p_funding_category IS NULL)
3309 /*OR (p_funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3310 Bug 5686790 */
3311 THEN
3312 l_funding_category := 'ADDITIONAL';
3313 ELSE
3314 l_funding_category := p_funding_category;
3315 /***** IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3316 THEN
3317 pa_interface_utils_pub.map_new_amg_msg
3318 ( p_old_message_code => 'PA_FUND_CAT_IS_MISS'
3319 ,p_msg_attribute => 'CHANGE'
3320 ,p_resize_flag => 'N'
3321 ,p_msg_context => 'GENERAL'
3322 ,p_attribute1 => ''
3323 ,p_attribute2 => ''
3324 ,p_attribute3 => ''
3325 ,p_attribute4 => ''
3326 ,p_attribute5 => '');
3327 END IF;
3328 p_return_status := FND_API.G_RET_STS_ERROR;
3329 RAISE FND_API.G_EXC_ERROR;
3330 *** Commented for bug 2838872 ***/
3331 END IF;
3332
3333 -- Validate funding category
3334
3335 IF pa_agreement_pvt.check_funding_category
3336 (p_project_id
3337 ,p_task_id
3338 ,p_agreement_id
3339 ,p_pm_funding_reference
3340 ,upper(l_funding_category)) = 'N'--Added upper for 3360593
3341 THEN
3342 p_return_status := FND_API.G_RET_STS_ERROR;
3343 RAISE FND_API.G_EXC_ERROR;
3344 END IF;
3345 END IF; /* l_funding_category is mischar*/
3346
3347 -- Code added for Bug 2403652 ends here
3348
3349
3350 -- Funding Reference
3351 IF (p_pm_funding_reference IS NULL)
3352 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3353 THEN
3354 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3355 THEN
3356 pa_interface_utils_pub.map_new_amg_msg
3357 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
3358 ,p_msg_attribute => 'CHANGE'
3359 ,p_resize_flag => 'N'
3360 ,p_msg_context => 'GENERAL'
3361 ,p_attribute1 => ''
3362 ,p_attribute2 => ''
3363 ,p_attribute3 => ''
3364 ,p_attribute4 => ''
3365 ,p_attribute5 => '');
3366 END IF;
3367 p_return_status := FND_API.G_RET_STS_ERROR;
3368 RAISE FND_API.G_EXC_ERROR;
3369 END IF;
3370 /* NOT REQUIED
3371 -- Funding Id
3372 IF (p_funding_id IS NULL)
3373 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
3374 THEN
3375 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3376 THEN
3377 pa_interface_utils_pub.map_new_amg_msg
3378 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
3379 ,p_msg_attribute => 'CHANGE'
3380 ,p_resize_flag => 'N'
3381 ,p_msg_context => 'FUNDING'
3382 ,p_attribute1 => ''
3383 ,p_attribute2 => p_pm_funding_reference
3384 ,p_attribute3 => ''
3385 ,p_attribute4 => ''
3386 ,p_attribute5 => '');
3387 END IF;
3388 p_return_status := FND_API.G_RET_STS_ERROR;
3389 RAISE FND_API.G_EXC_ERROR;
3390 END IF;
3391 */
3392
3393 /* code change for bug 2868818*/
3394
3395 /* Bug 13434695 */
3396
3397 Open l_limit_flag_csr(p_agreement_id);
3398 Fetch l_limit_flag_csr into l_rev_limit_flag,l_inv_limit_flag;
3399 Close l_limit_flag_csr;
3400
3401 Open l_amounts_csr(p_agreement_id);
3402 Fetch l_amounts_csr into l_accrued_amt,l_billed_amt,l_total_funding,
3403 l_projfunc_allocated_amount;
3404 Close l_amounts_csr;
3405
3406 IF (( (l_total_funding+p_allocated_amount-l_projfunc_allocated_amount) < l_accrued_amt and l_rev_limit_flag = 'Y') or
3407 ((l_total_funding+p_allocated_amount - l_projfunc_allocated_amount) < l_billed_amt and l_inv_limit_flag = 'Y')) THEN
3408
3409 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3410 THEN
3411 pa_interface_utils_pub.map_new_amg_msg
3412 (p_old_message_code => 'PA_BU_AMT_ALLOC_LT_AMT_ACCRUED'
3413 ,p_msg_attribute => 'CHANGE'
3414 ,p_resize_flag => 'N'
3415 ,p_msg_context => 'FUNDING'
3416 ,p_attribute1 => ''
3417 ,p_attribute2 => p_pm_funding_reference
3418 ,p_attribute3 => ''
3419 ,p_attribute4 => ''
3420 ,p_attribute5 => '' );
3421 END IF;
3422 p_return_status := FND_API.G_RET_STS_ERROR;
3423 RAISE FND_API.G_EXC_ERROR;
3424 END IF;
3425
3426 /* Bug 13434695 */
3427
3428 open c1;
3429 fetch c1 into l_fund_rec1;
3430 if c1%found then
3431 l_funding_category := l_fund_rec1.funding_category;
3432 end if;
3433 close c1;
3434
3435
3436 IF l_funding_category = 'REVALUATION'
3437 THEN
3438 pa_interface_utils_pub.map_new_amg_msg
3439 ( p_old_message_code => 'PA_UPDATE_DELETE_REVAL'
3440 ,p_msg_attribute => 'CHANGE'
3441 ,p_resize_flag => 'N'
3442 ,p_msg_context => 'GENERAL'
3443 ,p_attribute1 => ''
3444 ,p_attribute2 => ''
3445 ,p_attribute3 => ''
3446 ,p_attribute4 => ''
3447 ,p_attribute5 => '');
3448
3449 p_return_status := FND_API.G_RET_STS_ERROR;
3450 RAISE FND_API.G_EXC_ERROR;
3451 END IF;
3452
3453
3454 /* Moved the following function call from below to this place bug 2434153 **/
3455 -- CONVERT FUNDING REFERENCE TO FUNDING ID
3456 pa_agreement_pvt.convert_fu_ref_to_id
3457 (p_pm_funding_reference => p_pm_funding_reference
3458 ,p_af_funding_id => p_funding_id
3459 ,p_out_funding_id => l_out_funding_id
3460 ,p_return_status => l_return_status);
3461 p_return_status := l_return_status;
3462
3463 /* end code change for bug 2868818*/
3464 /** changed p_funding_id to l_out_funding_id in call to check_update_funding_ok bug 2434153 **/
3465 -- TO BE CORRECTED - NIKHIL
3466 -- VALIDATE THE INCOMING PARAMETERS
3467 IF pa_agreement_pvt.check_update_funding_ok
3468 (p_project_id => p_project_id
3469 ,p_task_id => p_task_id
3470 ,p_agreement_id => p_agreement_id
3471 ,p_customer_id => pa_agreement_utils.get_customer_id
3472 (p_funding_id => l_out_funding_id
3473 ,p_funding_reference => p_pm_funding_reference)
3474 ,p_pm_funding_reference => p_pm_funding_reference
3475 ,p_funding_id => l_out_funding_id
3476 ,p_funding_amt => p_allocated_amount
3477 /* MCB2 PARAMETERS BEGIN */
3478 ,p_project_rate_type => p_project_rate_type
3479 ,p_project_rate_date => p_project_rate_date
3480 ,p_project_exchange_rate => p_project_exchange_rate
3481 ,p_projfunc_rate_type => p_projfunc_rate_type
3482 ,p_projfunc_rate_date => p_projfunc_rate_date
3483 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate ) = 'N'
3484 /* MCB2 PARAMETERS END */
3485
3486 THEN
3487 p_return_status := FND_API.G_RET_STS_ERROR;
3488 RAISE FND_API.G_EXC_ERROR;
3489 END IF;
3490
3491
3492 -- Flex Field Validations
3493 IF (p_desc_flex_name IS NOT NULL)
3494 AND (p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3495 -- Nikhil OR (p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3496 THEN
3497 pa_agreement_pvt.validate_flex_fields
3498 (p_desc_flex_name => p_desc_flex_name
3499 ,p_attribute_category => p_attribute_category
3500 ,p_attribute1 => p_attribute1
3501 ,p_attribute2 => p_attribute2
3502 ,p_attribute3 => p_attribute3
3503 ,p_attribute4 => p_attribute4
3504 ,p_attribute5 => p_attribute5
3505 /** ,p_attribute6 => p_attribute7 ** commented bug 2862922 **/
3506 ,p_attribute6 => p_attribute6 /** added bug 2862922 **/
3507 ,p_attribute7 => p_attribute7 /** added bug 2862922 **/
3508 ,p_attribute8 => p_attribute8
3509 ,p_attribute9 => p_attribute9
3510 ,p_attribute10 => p_attribute10
3511 ,p_return_msg => l_return_msg
3512 ,p_validate_status => l_validate_status
3513 );
3514 IF l_validate_status = 'N'
3515 THEN
3516 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3517 THEN
3518 pa_interface_utils_pub.map_new_amg_msg
3519 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
3520 ,p_msg_attribute => 'CHANGE'
3521 ,p_resize_flag => 'N'
3522 ,p_msg_context => 'FLEX'
3523 ,p_attribute1 => l_return_msg
3524 ,p_attribute2 => ''
3525 ,p_attribute3 => ''
3526 ,p_attribute4 => ''
3527 ,p_attribute5 => '');
3528 END IF;
3529 p_return_status := FND_API.G_RET_STS_ERROR;
3530 RAISE FND_API.G_EXC_ERROR;
3531 END IF;
3532 END IF;
3533
3534 /* NOT REQUIRED
3535 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
3536 pa_agreement_pvt.convert_ag_ref_to_id
3537 (p_pm_ag_reference => p_agreement_in_rec.pm_agreement_reference
3538 ,p_af_agreement_id => pa_agreement_in_rec.agreement_id
3539 ,p_out_agreement_id => l_out_agreement_id
3540 ,p_return_status => l_return_status);
3541 p_return_status := l_return_status;
3542
3543 */
3544
3545 -- CONVERT FUNDING REFERENCE TO FUNDING ID
3546 /**** pa_agreement_pvt.convert_fu_ref_to_id
3547 (p_pm_funding_reference => p_pm_funding_reference
3548 ,p_af_funding_id => p_funding_id
3549 ,p_out_funding_id => l_out_funding_id
3550 ,p_return_status => l_return_status);
3551 p_return_status := l_return_status;
3552 ***** Commented bug 2434153 **/
3553
3554 /* Added for bug 5686790*/
3555
3556 IF (p_funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
3557 SELECT funding_category
3558 INTO l_funding_category
3559 FROM pa_project_fundings
3560 WHERE PROJECT_FUNDING_ID = l_out_funding_id;
3561 END IF;
3562
3563
3564 -- TO BE CORRECTED - NIKHIL
3565 -- UPDATE FUNDING
3566 pa_agreement_utils.update_funding
3567 (p_project_funding_id => l_out_funding_id
3568 ,p_last_update_date => SYSDATE
3569 ,p_last_updated_by => G_USER_ID
3570 ,p_last_update_login => G_LOGIN_ID
3571 ,p_agreement_id => p_agreement_id
3572 ,p_project_id => p_project_id
3573 ,p_task_id => p_task_id
3574 ,p_allocated_amount => p_allocated_amount
3575 ,p_date_allocated => p_date_allocated
3576 ,p_attribute_category => p_attribute_category
3577 ,p_attribute1 => p_attribute1
3578 ,p_attribute2 => p_attribute2
3579 ,p_attribute3 => p_attribute3
3580 ,p_attribute4 => p_attribute4
3581 ,p_attribute5 => p_attribute5
3582 ,p_attribute6 => p_attribute6
3583 ,p_attribute7 => p_attribute7
3584 ,p_attribute8 => p_attribute8
3585 ,p_attribute9 => p_attribute9
3586 ,p_attribute10 => p_attribute10
3587 ,p_pm_funding_reference => p_pm_funding_reference
3588 ,p_pm_product_code => p_pm_product_code
3589 /* MCB2 PARAMETERS BEGIN */
3590 ,p_project_rate_type => p_project_rate_type
3591 ,p_project_rate_date => p_project_rate_date
3592 ,p_project_exchange_rate => p_project_exchange_rate
3593 ,p_projfunc_rate_type => p_projfunc_rate_type
3594 ,p_projfunc_rate_date => p_projfunc_rate_date
3595 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate
3596 ,x_err_code => l_err_code
3597 ,x_err_msg => l_err_msg
3598 ,p_funding_category => l_funding_category /* For Bug 2244796 */
3599 );
3600 /* MCB2 PARAMETERS END */
3601
3602 IF l_err_code <> 0 THEN
3603
3604 /* Following code added for bug 2902096 .The l_resize_flag is set to 'Y' only for some
3605 error messages whose length of message_name will go above 30 if '_AMG' is added to it.
3606 These messages can be used straightaway without using the '_AMG' as the message text is same .*/
3607 /* Added some more error messages in the IF condition for bug 2967759 */
3608 l_resize_flag:='N';
3609
3610 IF (l_err_msg in ('PA_USR_RATE_NOT_ALLOWED_FC_PC','PA_USR_RATE_NOT_ALLOWED_FC_PF',
3611 'PA_USR_RATE_NOT_ALLOWED_BC_PF','PA_NO_EXCH_RATE_EXISTS_BC_PF',
3612 'PA_NO_EXCH_RATE_EXISTS_FC_PC','PA_NO_EXCH_RATE_EXISTS_FC_PF')) THEN
3613 l_resize_flag:='Y';
3614 END IF;
3615
3616
3617 /* End - Bug 2902096 */
3618
3619 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3620 THEN
3621 pa_interface_utils_pub.map_new_amg_msg
3622 ( p_old_message_code => l_err_msg
3623 ,p_msg_attribute => 'CHANGE'
3624 /* ,p_resize_flag => 'N' changed for bug 2902096 */
3625 ,p_resize_flag => l_resize_flag
3626 ,p_msg_context => 'FLEX'
3627 ,p_attribute1 => l_return_msg
3628 ,p_attribute2 => ''
3629 ,p_attribute3 => ''
3630 ,p_attribute4 => ''
3631 ,p_attribute5 => '');
3632 END IF;
3633 p_return_status := FND_API.G_RET_STS_ERROR;
3634 RAISE FND_API.G_EXC_ERROR;
3635
3636 ELSE
3637
3638
3639 -- UPDATE SUMMARY PROJECT FUNDINGS
3640 pa_agreement_utils.summary_funding_update_row
3641 (p_agreement_id => p_agreement_id
3642 ,p_project_id => p_project_id
3643 ,p_task_id => p_task_id
3644 ,p_login_id => G_LOGIN_ID
3645 ,p_user_id => G_USER_ID
3646 );
3647
3648
3649 IF FND_API.to_boolean( p_commit ) THEN
3650 COMMIT;
3651 END IF;
3652
3653 END IF;
3654
3655 EXCEPTION
3656
3657 WHEN FND_API.G_EXC_ERROR
3658 THEN
3659 p_return_status := FND_API.G_RET_STS_ERROR ;
3660 ROLLBACK TO update_funding_pub;
3661 FND_MSG_PUB.Count_And_Get
3662 ( p_count => p_msg_count ,
3663 p_data => p_msg_data );
3664
3665 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3666 THEN
3667 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3668 ROLLBACK TO update_funding_pub;
3669 FND_MSG_PUB.Count_And_Get
3670 ( p_count => p_msg_count ,
3671 p_data => p_msg_data );
3672
3673 WHEN OTHERS
3674 THEN
3675 ROLLBACK TO update_funding_pub;
3676 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3677 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3678 THEN
3679 FND_MSG_PUB.add_exc_msg
3680 ( p_pkg_name => G_PKG_NAME
3681 , p_procedure_name => l_api_name );
3682 END IF;
3683 FND_MSG_PUB.Count_And_Get
3684 ( p_count => p_msg_count ,
3685 p_data => p_msg_data );
3686
3687 END update_funding;
3688
3689 -- ============================================================================
3690 --
3691 --Name: init_agreement
3692 --Type: Procedure
3693 --Description: This procedure can be used to initialize the global PL/SQL
3694 -- tables that are used by a LOAD/EXECUTE/FETCH cycle.
3695 --
3696 --Called subprograms: XXX
3697 --
3698 --
3699 --
3700 --History:
3701 -- 25-MAR-2000 Rakesh Raghavan Created.
3702 -- ---------------------------------------------------------------------------
3703
3704 PROCEDURE init_agreement
3705 IS
3706 BEGIN
3707
3708 -- Standard begin of API savepoint
3709
3710 SAVEPOINT init_agreement_pub;
3711
3712 -- Initialize global table and record types
3713
3714 G_agreement_in_rec := G_agreement_in_null_rec;
3715 G_funding_in_tbl.delete;
3716 G_funding_tbl_count := 0;
3717 G_agreement_out_rec := G_agreement_out_null_rec;
3718 G_funding_out_tbl.delete;
3719
3720 END init_agreement;
3721
3722 -- ============================================================================
3723 --
3724 --Name: load_agreement
3725 --Type: Procedure
3726 --Description: This procedure can be used to move the agreement related
3727 -- parameters from the client side to a record on the server side
3728 -- , where it will be used by a LOAD/EXECUTE/FETCH cycle.
3729 --
3730 --Called subprograms: XXX
3731 --
3732 --
3733 --
3734 --History:
3735 -- 25-MAR-2000 Rakesh Raghavan Created.
3736 -- ---------------------------------------------------------------------------
3737
3738
3739 PROCEDURE load_agreement
3740 (p_api_version_number IN NUMBER
3741 ,p_init_msg_list IN VARCHAR2
3742 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
3743 ,p_pm_agreement_reference IN VARCHAR2
3744 ,p_agreement_id IN NUMBER
3745 ,p_customer_id IN NUMBER
3746 ,p_customer_name IN VARCHAR2
3747 ,p_customer_num IN VARCHAR2
3748 ,p_agreement_num IN VARCHAR2
3749 ,p_agreement_type IN VARCHAR2
3750 ,p_amount IN NUMBER
3751 ,p_term_id IN NUMBER
3752 ,p_term_name IN VARCHAR2
3753 ,p_revenue_limit_flag IN VARCHAR2
3754 ,p_expiration_date IN DATE
3755 ,p_description IN VARCHAR2
3756 ,p_owned_by_person_id IN NUMBER
3757 ,p_owned_by_person_name IN VARCHAR2
3758 ,p_attribute_category IN VARCHAR2
3759 ,p_attribute1 IN VARCHAR2
3760 ,p_attribute2 IN VARCHAR2
3761 ,p_attribute3 IN VARCHAR2
3762 ,p_attribute4 IN VARCHAR2
3763 ,p_attribute5 IN VARCHAR2
3764 ,p_attribute6 IN VARCHAR2
3765 ,p_attribute7 IN VARCHAR2
3766 ,p_attribute8 IN VARCHAR2
3767 ,p_attribute9 IN VARCHAR2
3768 ,p_attribute10 IN VARCHAR2
3769 ,p_template_flag IN VARCHAR2
3770 ,p_desc_flex_name IN VARCHAR2
3771 ,p_owning_organization_id IN NUMBER
3772 ,p_agreement_currency_code IN VARCHAR2
3773 ,p_invoice_limit_flag IN VARCHAR2
3774 /*Federal*/
3775 ,p_customer_order_number IN VARCHAR2
3776 ,p_advance_required IN VARCHAR2
3777 ,p_start_date IN DATE
3778 ,p_billing_sequence IN NUMBER
3779 ,p_line_of_account IN VARCHAR2
3780 ,p_attribute11 IN VARCHAR2
3781 ,p_attribute12 IN VARCHAR2
3782 ,p_attribute13 IN VARCHAR2
3783 ,p_attribute14 IN VARCHAR2
3784 ,p_attribute15 IN VARCHAR2
3785 ,p_attribute16 IN VARCHAR2
3786 ,p_attribute17 IN VARCHAR2
3787 ,p_attribute18 IN VARCHAR2
3788 ,p_attribute19 IN VARCHAR2
3789 ,p_attribute20 IN VARCHAR2
3790 ,p_attribute21 IN VARCHAR2
3791 ,p_attribute22 IN VARCHAR2
3792 ,p_attribute23 IN VARCHAR2
3793 ,p_attribute24 IN VARCHAR2
3794 ,p_attribute25 IN VARCHAR2
3795 )
3796 IS
3797 -- LOCAL VARIABLES
3798 l_msg_count NUMBER ;
3799 l_msg_data VARCHAR2(2000);
3800 l_function_allowed VARCHAR2(1);
3801 l_resp_id NUMBER := 0;
3802 l_return_status VARCHAR2(1);
3803 l_api_name CONSTANT VARCHAR2(30):= 'load_agreement';
3804
3805 BEGIN
3806 -- Standard begin of API savepoint
3807
3808 SAVEPOINT load_agreement_pub;
3809
3810
3811 -- Standard call to check for call compatibility.
3812
3813
3814 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3815 p_api_version_number ,
3816 l_api_name ,
3817 G_PKG_NAME )
3818 THEN
3819
3820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3821
3822 END IF;
3823 l_resp_id := FND_GLOBAL.Resp_id;
3824
3825 -- Actions performed using the APIs would be subject to
3826 -- function security. If the responsibility does not allow
3827 -- such functions to be executed, the API should not proceed further
3828 -- since the user does not have access to such functions
3829
3830 /* NOT REQUIRED
3831
3832 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
3833 (p_api_version_number => p_api_version_number,
3834 p_responsibility_id => l_resp_id,
3835 p_function_name => 'PA_AF_LOAD_AGREEMENT',
3836 p_msg_count => l_msg_count,
3837 p_msg_data => l_msg_data,
3838 p_return_status => l_return_status,
3839 p_function_allowed => l_function_allowed
3840 );
3841
3842 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3843 THEN
3844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3845
3846 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3847 THEN
3848 RAISE FND_API.G_EXC_ERROR;
3849 END IF;
3850 IF l_function_allowed = 'N' THEN
3851 pa_interface_utils_pub.map_new_amg_msg
3852 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
3853 ,p_msg_attribute => 'CHANGE'
3854 ,p_resize_flag => 'Y'
3855 ,p_msg_context => 'GENERAL'
3856 ,p_attribute1 => ''
3857 ,p_attribute2 => ''
3858 ,p_attribute3 => ''
3859 ,p_attribute4 => ''
3860 ,p_attribute5 => '');
3861 p_return_status := FND_API.G_RET_STS_ERROR;
3862 RAISE FND_API.G_EXC_ERROR;
3863 END IF;
3864 */
3865 -- Initialize the message table if requested.
3866
3867 IF FND_API.TO_BOOLEAN( p_init_msg_list )
3868 THEN
3869
3870 FND_MSG_PUB.initialize;
3871
3872 END IF;
3873
3874 -- Set API return status to success
3875
3876 p_return_status := FND_API.G_RET_STS_SUCCESS;
3877
3878
3879 -- ASSIGN INCOMING PARAMETERS TO THE FIELDS OF THE GLOBAL AGREEMENT RECORD
3880
3881 G_agreement_in_rec.agreement_id := p_agreement_id;
3882 G_agreement_in_rec.agreement_num := p_agreement_num;
3883 G_agreement_in_rec.pm_agreement_reference := p_pm_agreement_reference;
3884 G_agreement_in_rec.customer_id := p_customer_id;
3885 G_agreement_in_rec.customer_num := p_customer_num;
3886 G_agreement_in_rec.agreement_type := p_agreement_type;
3887 G_agreement_in_rec.amount := p_amount;
3888 G_agreement_in_rec.term_id := p_term_id;
3889 G_agreement_in_rec.revenue_limit_flag := p_revenue_limit_flag;
3890 G_agreement_in_rec.expiration_date := p_expiration_date;
3891 G_agreement_in_rec.description := p_description;
3892 G_agreement_in_rec.owned_by_person_id := p_owned_by_person_id;
3893 G_agreement_in_rec.template_flag := p_template_flag;
3894 G_agreement_in_rec.desc_flex_name := p_desc_flex_name;
3895 G_agreement_in_rec.attribute_category := p_attribute_category;
3896 G_agreement_in_rec.attribute1 := p_attribute1;
3897 G_agreement_in_rec.attribute2 := p_attribute2;
3898 G_agreement_in_rec.attribute3 := p_attribute3;
3899 G_agreement_in_rec.attribute4 := p_attribute4;
3900 G_agreement_in_rec.attribute5 := p_attribute5;
3901 G_agreement_in_rec.attribute6 := p_attribute6;
3902 G_agreement_in_rec.attribute7 := p_attribute7;
3903 G_agreement_in_rec.attribute8 := p_attribute8;
3904 G_agreement_in_rec.attribute9 := p_attribute9;
3905 G_agreement_in_rec.attribute10 := p_attribute10;
3906 G_agreement_in_rec.owning_organization_id := p_owning_organization_id;
3907 G_agreement_in_rec.agreement_currency_code := p_agreement_currency_code;
3908 G_agreement_in_rec.invoice_limit_flag := p_invoice_limit_flag;
3909 /*Federal*/
3910 G_agreement_in_rec.customer_order_number := p_customer_order_number;
3911 G_agreement_in_rec.advance_required := p_advance_required;
3912 G_agreement_in_rec.start_date := p_start_date;
3913 G_agreement_in_rec.billing_sequence := p_billing_sequence;
3914 G_agreement_in_rec.line_of_account := p_line_of_account;
3915 G_agreement_in_rec.attribute11 := p_attribute11;
3916 G_agreement_in_rec.attribute12 := p_attribute12;
3917 G_agreement_in_rec.attribute13 := p_attribute13;
3918 G_agreement_in_rec.attribute14 := p_attribute14;
3919 G_agreement_in_rec.attribute15 := p_attribute15;
3920 G_agreement_in_rec.attribute16 := p_attribute16;
3921 G_agreement_in_rec.attribute17 := p_attribute17;
3922 G_agreement_in_rec.attribute18 := p_attribute18;
3923 G_agreement_in_rec.attribute19 := p_attribute19;
3924 G_agreement_in_rec.attribute20 := p_attribute20;
3925 G_agreement_in_rec.attribute21 := p_attribute21;
3926 G_agreement_in_rec.attribute22 := p_attribute22;
3927 G_agreement_in_rec.attribute23 := p_attribute23;
3928 G_agreement_in_rec.attribute24 := p_attribute24;
3929 G_agreement_in_rec.attribute25 := p_attribute25;
3930
3931
3932 EXCEPTION
3933
3934 WHEN FND_API.G_EXC_ERROR
3935 THEN
3936 ROLLBACK TO load_agreement_pub;
3937 p_return_status := FND_API.G_RET_STS_ERROR;
3938
3939
3940 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3941 THEN
3942 ROLLBACK TO load_agreement_pub;
3943 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3944
3945
3946 WHEN OTHERS
3947 THEN
3948 ROLLBACK TO load_agreement_pub;
3949 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3950
3951 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3952 THEN
3953 FND_MSG_PUB.add_exc_msg
3954 ( p_pkg_name => G_PKG_NAME
3955 , p_procedure_name => l_api_name );
3956 END IF;
3957
3958 END load_agreement;
3959
3960 -- ============================================================================
3961 --
3962 --Name: load_funding
3963 --Type: Procedure
3964 --Description: This procedure can be used to move the funding related
3965 -- parameters from the client side to a record on the server side
3966 -- , where it will be used by a LOAD/EXECUTE/FETCH cycle.
3967 --
3968 --Called subprograms: XXX
3969 --
3970 --
3971 --
3972 --History:
3973 -- 25-MAR-2000 Rakesh Raghavan Created.
3974 -- ---------------------------------------------------------------------------
3975
3976 PROCEDURE load_funding
3977 (p_api_version_number IN NUMBER
3978 ,p_init_msg_list IN VARCHAR2
3979 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
3980 ,p_pm_funding_reference IN VARCHAR2
3981 ,p_funding_id IN NUMBER
3982 ,p_agreement_id IN NUMBER
3983 ,p_project_id IN NUMBER
3984 ,p_task_id IN NUMBER
3985 ,p_allocated_amount IN NUMBER
3986 ,p_date_allocated IN DATE
3987 ,p_attribute_category IN VARCHAR2
3988 ,p_attribute1 IN VARCHAR2
3989 ,p_attribute2 IN VARCHAR2
3990 ,p_attribute3 IN VARCHAR2
3991 ,p_attribute4 IN VARCHAR2
3992 ,p_attribute5 IN VARCHAR2
3993 ,p_attribute6 IN VARCHAR2
3994 ,p_attribute7 IN VARCHAR2
3995 ,p_attribute8 IN VARCHAR2
3996 ,p_attribute9 IN VARCHAR2
3997 ,p_attribute10 IN VARCHAR2
3998 ,p_project_rate_type IN VARCHAR2 DEFAULT NULL
3999 ,p_project_rate_date IN DATE DEFAULT NULL
4000 ,p_project_exchange_rate IN NUMBER DEFAULT NULL
4001 ,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
4002 ,p_projfunc_rate_date IN DATE DEFAULT NULL
4003 ,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
4004 ,p_funding_category IN VARCHAR2 DEFAULT 'ADDITIONAL'
4005 /* Added for Bug 2483081 to include Default value - For Bug 2244796 */
4006 )
4007 IS
4008 -- LOCAL VARIABLES
4009 l_msg_count NUMBER ;
4010 l_msg_data VARCHAR2(2000);
4011 l_function_allowed VARCHAR2(1);
4012 l_resp_id NUMBER := 0;
4013 l_return_status VARCHAR2(1);
4014 l_api_name CONSTANT VARCHAR2(30):= 'load_funding';
4015 BEGIN
4016 -- Standard begin of API savepoint
4017
4018 SAVEPOINT load_funding_pub;
4019
4020
4021 -- Standard call to check for call compatibility.
4022
4023
4024 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4025 p_api_version_number ,
4026 l_api_name ,
4027 G_PKG_NAME )
4028 THEN
4029
4030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4031
4032 END IF;
4033 l_resp_id := FND_GLOBAL.Resp_id;
4034
4035 -- Actions performed using the APIs would be subject to
4036 -- function security. If the responsibility does not allow
4037 -- such functions to be executed, the API should not proceed further
4038 -- since the user does not have access to such functions
4039
4040 /* NOT REQUIRED
4041 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4042 (p_api_version_number => p_api_version_number,
4043 p_responsibility_id => l_resp_id,
4044 p_function_name => 'PA_AF_LOAD_FUNDING',
4045 p_msg_count => l_msg_count,
4046 p_msg_data => l_msg_data,
4047 p_return_status => l_return_status,
4048 p_function_allowed => l_function_allowed
4049 );
4050
4051 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4052 THEN
4053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4054
4055 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4056 THEN
4057 RAISE FND_API.G_EXC_ERROR;
4058 END IF;
4059 IF l_function_allowed = 'N' THEN
4060 pa_interface_utils_pub.map_new_amg_msg
4061 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4062 ,p_msg_attribute => 'CHANGE'
4063 ,p_resize_flag => 'Y'
4064 ,p_msg_context => 'GENERAL'
4065 ,p_attribute1 => ''
4066 ,p_attribute2 => ''
4067 ,p_attribute3 => ''
4068 ,p_attribute4 => ''
4069 ,p_attribute5 => '');
4070 p_return_status := FND_API.G_RET_STS_ERROR;
4071 RAISE FND_API.G_EXC_ERROR;
4072 END IF;
4073 */
4074 -- Initialize the message table if requested.
4075
4076 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4077 THEN
4078
4079 FND_MSG_PUB.initialize;
4080
4081 END IF;
4082
4083 -- Set API return status to success
4084
4085 p_return_status := FND_API.G_RET_STS_SUCCESS;
4086
4087 -- ASSIGN A VALUE TO THE GLOBAL COUNTER FOR THIS TABLE
4088 G_funding_tbl_count := G_funding_tbl_count + 1;
4089
4090 -- ASSIGN INCOMING PARAMETERS TO THE GLOBAL TABLE FIELDS
4091 G_funding_in_tbl(G_funding_tbl_count).pm_funding_reference := p_pm_funding_reference;
4092 G_funding_in_tbl(G_funding_tbl_count).project_funding_id := p_funding_id;
4093 G_funding_in_tbl(G_funding_tbl_count).project_id := p_project_id;
4094 G_funding_in_tbl(G_funding_tbl_count).task_id := p_task_id;
4095 G_funding_in_tbl(G_funding_tbl_count).allocated_amount := p_allocated_amount;
4096 G_funding_in_tbl(G_funding_tbl_count).date_allocated := p_date_allocated;
4097 G_funding_in_tbl(G_funding_tbl_count).attribute_category := p_attribute_category;
4098 G_funding_in_tbl(G_funding_tbl_count).attribute1 := p_attribute1;
4099 G_funding_in_tbl(G_funding_tbl_count).attribute2 := p_attribute2;
4100 G_funding_in_tbl(G_funding_tbl_count).attribute3 := p_attribute3;
4101 G_funding_in_tbl(G_funding_tbl_count).attribute4 := p_attribute4;
4102 G_funding_in_tbl(G_funding_tbl_count).attribute5 := p_attribute5;
4103 G_funding_in_tbl(G_funding_tbl_count).attribute6 := p_attribute6;
4104 G_funding_in_tbl(G_funding_tbl_count).attribute7 := p_attribute7;
4105 G_funding_in_tbl(G_funding_tbl_count).attribute8 := p_attribute8;
4106 G_funding_in_tbl(G_funding_tbl_count).attribute9 := p_attribute9;
4107 G_funding_in_tbl(G_funding_tbl_count).attribute10 := p_attribute10;
4108 G_funding_in_tbl(G_funding_tbl_count).project_rate_type := p_project_rate_type;
4109 G_funding_in_tbl(G_funding_tbl_count).project_rate_date := p_project_rate_date;
4110 G_funding_in_tbl(G_funding_tbl_count).project_exchange_rate := p_project_exchange_rate;
4111 G_funding_in_tbl(G_funding_tbl_count).projfunc_rate_type := p_projfunc_rate_type;
4112 G_funding_in_tbl(G_funding_tbl_count).projfunc_rate_date := p_projfunc_rate_date;
4113 G_funding_in_tbl(G_funding_tbl_count).projfunc_exchange_rate := p_projfunc_exchange_rate;
4114 G_funding_in_tbl(G_funding_tbl_count).funding_category := p_funding_category; /* Added for Bug 2403652 */
4115
4116
4117 EXCEPTION
4118
4119 WHEN FND_API.G_EXC_ERROR
4120 THEN
4121 ROLLBACK TO load_funding_pub;
4122 p_return_status := FND_API.G_RET_STS_ERROR;
4123
4124 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4125 THEN
4126 ROLLBACK TO load_funding_pub;
4127 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4128
4129 WHEN OTHERS
4130 THEN
4131 ROLLBACK TO load_funding_pub;
4132 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4133 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4134 THEN
4135 FND_MSG_PUB.add_exc_msg
4136 ( p_pkg_name => G_PKG_NAME
4137 , p_procedure_name => l_api_name );
4138 END IF;
4139
4140 END load_funding;
4141
4142
4143 -- ============================================================================
4144 --
4145 --Name: execute_create_agreement
4146 --Type: Procedure
4147 --Description: This procedure can be used to create an agreement
4148 -- using global PL/SQL tables.
4149 --
4150 --Called subprograms: XXX
4151 --
4152 --
4153 --
4154 --History:
4155 -- 25-MAR-2000 Rakesh Raghavan Created.
4156 -- ---------------------------------------------------------------------------
4157
4158 PROCEDURE execute_create_agreement
4159 (p_api_version_number IN NUMBER
4160 ,p_commit IN VARCHAR2
4161 ,p_init_msg_list IN VARCHAR2
4162 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
4163 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
4164 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4165 ,p_pm_product_code IN VARCHAR2
4166 ,p_agreement_id_out OUT NOCOPY NUMBER
4167 ,p_customer_id_out OUT NOCOPY NUMBER
4168 )
4169 IS
4170 -- LOCAL VARIABLES
4171 l_msg_count NUMBER ;
4172 l_msg_data VARCHAR2(2000);
4173 l_function_allowed VARCHAR2(1);
4174 l_resp_id NUMBER := 0;
4175 l_return_status VARCHAR2(1);
4176 l_api_name CONSTANT VARCHAR2(30):= 'execute_create_agreement';
4177 BEGIN
4178 -- Standard begin of API savepoint
4179
4180 SAVEPOINT execute_create_agreement_pub;
4181
4182
4183 -- Standard call to check for call compatibility.
4184
4185
4186 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4187 p_api_version_number ,
4188 l_api_name ,
4189 G_PKG_NAME )
4190 THEN
4191
4192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4193
4194 END IF;
4195 l_resp_id := FND_GLOBAL.Resp_id;
4196
4197 -- Actions performed using the APIs would be subject to
4198 -- function security. If the responsibility does not allow
4199 -- such functions to be executed, the API should not proceed further
4200 -- since the user does not have access to such functions
4201
4202 /* NOT REQUIRED
4203 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4204 (p_api_version_number => p_api_version_number,
4205 p_responsibility_id => l_resp_id,
4206 p_function_name => 'PA_AF_EXECUTE_CREATE_AGREEMENT',
4207 p_msg_count => l_msg_count,
4208 p_msg_data => l_msg_data,
4209 p_return_status => l_return_status,
4210 p_function_allowed => l_function_allowed
4211 );
4212
4213 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4214 THEN
4215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4216
4217 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4218 THEN
4219 RAISE FND_API.G_EXC_ERROR;
4220 END IF;
4221 IF l_function_allowed = 'N' THEN
4222 pa_interface_utils_pub.map_new_amg_msg
4223 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4224 ,p_msg_attribute => 'CHANGE'
4225 ,p_resize_flag => 'Y'
4226 ,p_msg_context => 'GENERAL'
4227 ,p_attribute1 => ''
4228 ,p_attribute2 => ''
4229 ,p_attribute3 => ''
4230 ,p_attribute4 => ''
4231 ,p_attribute5 => '');
4232 p_return_status := FND_API.G_RET_STS_ERROR;
4233 RAISE FND_API.G_EXC_ERROR;
4234 END IF;
4235 */
4236 -- Initialize the message table if requested.
4237
4238 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4239 THEN
4240
4241 FND_MSG_PUB.initialize;
4242
4243 END IF;
4244
4245 -- Set API return status to success
4246
4247 p_return_status := FND_API.G_RET_STS_SUCCESS;
4248
4249
4250 -- CALL CREATE AGREEMENT
4251 pa_agreement_pub.create_agreement
4252 (p_api_version_number => p_api_version_number
4253 ,p_commit => p_commit
4254 ,p_init_msg_list => p_init_msg_list
4255 ,p_msg_count => p_msg_count
4256 ,p_msg_data => p_msg_data
4257 ,p_return_status => l_return_status
4258 ,p_pm_product_code => p_pm_product_code
4259 ,p_agreement_in_rec => G_agreement_in_rec
4260 ,p_agreement_out_rec => G_agreement_out_rec
4261 ,p_funding_in_tbl => G_funding_in_tbl
4262 ,p_funding_out_tbl => G_funding_out_tbl);
4263
4264 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4265 THEN
4266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4267 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4268 THEN
4269 RAISE FND_API.G_EXC_ERROR;
4270 END IF;
4271
4272 -- ASSIGN OUTGOING VALUES TO OUTGOING PARAMETERS
4273 IF G_agreement_out_rec.agreement_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4274 THEN
4275 p_agreement_id_out := NULL;
4276 ELSE
4277 p_agreement_id_out := G_agreement_out_rec.agreement_id;
4278 END IF;
4279
4280 IF G_agreement_out_rec.customer_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4281 THEN
4282 p_customer_id_out := NULL;
4283 ELSE
4284 p_customer_id_out := G_agreement_out_rec.customer_id;
4285 END IF;
4286
4287 IF FND_API.to_boolean( p_commit )
4288 THEN
4289 COMMIT;
4290 END IF;
4291
4292
4293 EXCEPTION
4294
4295 WHEN FND_API.G_EXC_ERROR
4296 THEN
4297 ROLLBACK TO execute_create_agreement_pub;
4298 p_return_status := FND_API.G_RET_STS_ERROR;
4299 FND_MSG_PUB.Count_And_Get
4300 ( p_count => p_msg_count ,
4301 p_data => p_msg_data );
4302
4303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4304 THEN
4305 ROLLBACK TO execute_create_agreement_pub;
4306 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4307 FND_MSG_PUB.Count_And_Get
4308 ( p_count => p_msg_count ,
4309 p_data => p_msg_data );
4310
4311 WHEN OTHERS
4312 THEN
4313 ROLLBACK TO execute_create_agreement_pub;
4314 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4315 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4316 THEN
4317 FND_MSG_PUB.add_exc_msg
4318 ( p_pkg_name => G_PKG_NAME
4319 , p_procedure_name => l_api_name );
4320 END IF;
4321 FND_MSG_PUB.Count_And_Get
4322 ( p_count => p_msg_count ,
4323 p_data => p_msg_data );
4324
4325 END execute_create_agreement;
4326
4327
4328 -- ============================================================================
4329 --
4330 --Name: execute_update_agreement
4331 --Type: Procedure
4332 --Description: This procedure can be used to update an agreement
4333 -- using global PL/SQL tables.
4334 --
4335 --Called subprograms: XXX
4336 --
4337 --
4338 --
4339 --History:
4340 -- 25-MAR-2000 Rakesh Raghavan Created.
4341 -- ---------------------------------------------------------------------------
4342
4343 PROCEDURE execute_update_agreement
4344 (p_api_version_number IN NUMBER
4345 ,p_commit IN VARCHAR2
4346 ,p_init_msg_list IN VARCHAR2
4347 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
4348 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
4349 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4350 ,p_pm_product_code IN VARCHAR2
4351 )
4352 IS
4353 -- LOCAL VARIABLES
4354 l_msg_count NUMBER ;
4355 l_msg_data VARCHAR2(2000);
4356 l_function_allowed VARCHAR2(1);
4357 l_resp_id NUMBER := 0;
4358 l_return_status VARCHAR2(1);
4359 l_api_name CONSTANT VARCHAR2(30):= 'execute_update_agreement';
4360 BEGIN
4361 -- Standard begin of API savepoint
4362
4363 SAVEPOINT execute_update_agreement_pub;
4364
4365
4366 -- Standard call to check for call compatibility.
4367
4368
4369 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4370 p_api_version_number ,
4371 l_api_name ,
4372 G_PKG_NAME )
4373 THEN
4374
4375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4376
4377 END IF;
4378 l_resp_id := FND_GLOBAL.Resp_id;
4379
4380 -- Actions performed using the APIs would be subject to
4381 -- function security. If the responsibility does not allow
4382 -- such functions to be executed, the API should not proceed further
4383 -- since the user does not have access to such functions
4384
4385 /* NOT REQUIRED
4386 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4387 (p_api_version_number => p_api_version_number,
4388 p_responsibility_id => l_resp_id,
4389 p_function_name => 'PA_AF_EXECUTE_UPDATE_AGREEMENT',
4390 p_msg_count => l_msg_count,
4391 p_msg_data => l_msg_data,
4392 p_return_status => l_return_status,
4393 p_function_allowed => l_function_allowed
4394 );
4395
4396 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4397 THEN
4398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4399
4400 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4401 THEN
4402 RAISE FND_API.G_EXC_ERROR;
4403 END IF;
4404 IF l_function_allowed = 'N' THEN
4405 pa_interface_utils_pub.map_new_amg_msg
4406 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4407 ,p_msg_attribute => 'CHANGE'
4408 ,p_resize_flag => 'Y'
4409 ,p_msg_context => 'GENERAL'
4410 ,p_attribute1 => ''
4411 ,p_attribute2 => ''
4412 ,p_attribute3 => ''
4413 ,p_attribute4 => ''
4414 ,p_attribute5 => '');
4415 p_return_status := FND_API.G_RET_STS_ERROR;
4416 RAISE FND_API.G_EXC_ERROR;
4417 END IF;
4418 */
4419 -- Initialize the message table if requested.
4420
4421 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4422 THEN
4423
4424 FND_MSG_PUB.initialize;
4425
4426 END IF;
4427
4428 -- Set API return status to success
4429
4430 p_return_status := FND_API.G_RET_STS_SUCCESS;
4431
4432 -- CALL UPDATE AGREEMENT
4433 pa_agreement_pub.update_agreement
4434 (p_api_version_number => p_api_version_number
4435 ,p_commit => p_commit
4436 ,p_init_msg_list => p_init_msg_list
4437 ,p_msg_count => p_msg_count
4438 ,p_msg_data => p_msg_data
4439 ,p_return_status => p_return_status
4440 ,p_pm_product_code => p_pm_product_code
4441 ,p_agreement_in_rec => G_agreement_in_rec
4442 ,p_agreement_out_rec => G_agreement_out_rec
4443 ,p_funding_in_tbl => G_funding_in_tbl
4444 ,p_funding_out_tbl => G_funding_out_tbl);
4445
4446 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4447 THEN
4448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4449 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4450 THEN
4451 RAISE FND_API.G_EXC_ERROR;
4452 END IF;
4453
4454 IF FND_API.to_boolean( p_commit )
4455 THEN
4456 COMMIT;
4457 END IF;
4458
4459
4460 EXCEPTION
4461
4462 WHEN FND_API.G_EXC_ERROR
4463 THEN
4464 ROLLBACK TO execute_update_agreement_pub;
4465 p_return_status := FND_API.G_RET_STS_ERROR;
4466 FND_MSG_PUB.Count_And_Get
4467 ( p_count => p_msg_count ,
4468 p_data => p_msg_data );
4469
4470 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4471 THEN
4472 ROLLBACK TO execute_update_agreement_pub;
4473 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4474 FND_MSG_PUB.Count_And_Get
4475 ( p_count => p_msg_count ,
4476 p_data => p_msg_data );
4477
4478 WHEN OTHERS
4479 THEN
4480 ROLLBACK TO execute_update_agreement_pub;
4481 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4482 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4483 THEN
4484 FND_MSG_PUB.add_exc_msg
4485 ( p_pkg_name => G_PKG_NAME
4486 , p_procedure_name => l_api_name
4487 , p_error_text => SUBSTR(SQLERRM, 1, 240) );
4488 FND_MSG_PUB.add;
4489 END IF;
4490 FND_MSG_PUB.Count_And_Get
4491 ( p_count => p_msg_count ,
4492 p_data => p_msg_data );
4493
4494 END execute_update_agreement;
4495
4496 -- ============================================================================
4497 --
4498 --Name: fetch_funding
4499 --Type: Procedure
4500 --Description: This procedure can be used to update an agreement
4501 -- using global PL/SQL tables.
4502 --
4503 --Called subprograms: XXX
4504 --
4505 --
4506 --
4507 --History:
4508 -- 25-MAR-2000 Rakesh Raghavan Created.
4509 -- ---------------------------------------------------------------------------
4510
4511 PROCEDURE fetch_funding
4512 (p_api_version_number IN NUMBER
4513 ,p_init_msg_list IN VARCHAR2
4514 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4515 ,p_funding_index IN NUMBER
4516 ,p_funding_id OUT NOCOPY NUMBER /*File.sql.39*/
4517 ,p_pm_funding_reference OUT NOCOPY VARCHAR2 /*File.sql.39*/
4518 )
4519 IS
4520 -- LOCAL VARIABLES
4521 l_msg_count NUMBER ;
4522 l_msg_data VARCHAR2(2000);
4523 l_function_allowed VARCHAR2(1);
4524 l_resp_id NUMBER := 0;
4525 l_return_status VARCHAR2(1);
4526 l_index NUMBER;
4527 l_api_name CONSTANT VARCHAR2(30):= 'fetch_funding';
4528 BEGIN
4529 -- Standard begin of API savepoint
4530
4531 SAVEPOINT fetch_funding_pub;
4532
4533
4534 -- Standard call to check for call compatibility.
4535
4536
4537 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4538 p_api_version_number ,
4539 l_api_name ,
4540 G_PKG_NAME )
4541 THEN
4542
4543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4544
4545 END IF;
4546 l_resp_id := FND_GLOBAL.Resp_id;
4547
4548 -- Actions performed using the APIs would be subject to
4549 -- function security. If the responsibility does not allow
4550 -- such functions to be executed, the API should not proceed further
4551 -- since the user does not have access to such functions
4552
4553 /* NOT REQUIRED
4554 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4555 (p_api_version_number => p_api_version_number,
4556 p_responsibility_id => l_resp_id,
4557 p_function_name => 'PA_AF_FETCH_FUNDING',
4558 p_msg_count => l_msg_count,
4559 p_msg_data => l_msg_data,
4560 p_return_status => l_return_status,
4561 p_function_allowed => l_function_allowed
4562 );
4563
4564 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4565 THEN
4566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4567
4568 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4569 THEN
4570 RAISE FND_API.G_EXC_ERROR;
4571 END IF;
4572 IF l_function_allowed = 'N' THEN
4573 pa_interface_utils_pub.map_new_amg_msg
4574 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4575 ,p_msg_attribute => 'CHANGE'
4576 ,p_resize_flag => 'Y'
4577 ,p_msg_context => 'GENERAL'
4578 ,p_attribute1 => ''
4579 ,p_attribute2 => ''
4580 ,p_attribute3 => ''
4581 ,p_attribute4 => ''
4582 ,p_attribute5 => '');
4583 p_return_status := FND_API.G_RET_STS_ERROR;
4584 RAISE FND_API.G_EXC_ERROR;
4585 END IF;
4586 */
4587 -- Initialize the message table if requested.
4588
4589 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4590 THEN
4591
4592 FND_MSG_PUB.initialize;
4593
4594 END IF;
4595
4596 -- Set API return status to success
4597
4598 p_return_status := FND_API.G_RET_STS_SUCCESS;
4599
4600 -- Check Funding index value, when they don't provide an index we will error out
4601 IF p_funding_index = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4602 OR p_funding_index IS NULL
4603 THEN
4604 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4605 THEN
4606 pa_interface_utils_pub.map_new_amg_msg
4607 ( p_old_message_code => 'PA_FUND_INDEX_NOT_PROV'
4608 ,p_msg_attribute => 'CHANGE'
4609 ,p_resize_flag => 'N'
4610 ,p_msg_context => 'GENERAL'
4611 ,p_attribute1 => ''
4612 ,p_attribute2 => ''
4613 ,p_attribute3 => ''
4614 ,p_attribute4 => ''
4615 ,p_attribute5 => '');
4616 END IF;
4617 p_return_status := FND_API.G_RET_STS_ERROR;
4618 RAISE FND_API.G_EXC_ERROR;
4619 ELSE
4620 l_index := p_funding_index;
4621 END IF;
4622 IF G_funding_out_tbl.exists(l_index)
4623 THEN
4624 -- assign global table fields to the outgoing parameter
4625 -- we don't want to return the big number G_PA_MISS_NUM
4626 IF G_funding_out_tbl(l_index).project_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4627 THEN
4628 p_funding_id := NULL;
4629 ELSE
4630 p_funding_id := G_funding_out_tbl(l_index).project_funding_id;
4631 END IF;
4632 p_return_status := G_funding_out_tbl(l_index).return_status;
4633 END IF;
4634
4635 EXCEPTION
4636
4637 WHEN FND_API.G_EXC_ERROR
4638 THEN
4639 ROLLBACK TO fetch_funding_pub;
4640 p_return_status := FND_API.G_RET_STS_ERROR;
4641
4642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4643 THEN
4644 ROLLBACK TO fetch_funding_pub;
4645 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4646
4647 WHEN OTHERS
4648 THEN
4649 ROLLBACK TO fetch_funding_pub;
4650 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4651 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4652 THEN
4653 FND_MSG_PUB.add_exc_msg
4654 ( p_pkg_name => G_PKG_NAME
4655 , p_procedure_name => l_api_name );
4656 END IF;
4657
4658 END fetch_funding;
4659
4660 -- ============================================================================
4661 --
4662 --Name: clear_agreement
4663 --Type: Procedure
4664 --Description: This procedure can be used to clear the global PL/SQL
4665 -- tables that are used by a LOAD/EXECUTE/FETCH cycle.
4666 --
4667 --Called subprograms: XXX
4668 --
4669 --
4670 --
4671 --History:
4672 -- 25-MAR-2000 Rakesh Raghavan Created.
4673 -- ---------------------------------------------------------------------------
4674
4675 PROCEDURE clear_agreement
4676 IS
4677 -- LOCAL VARIABLES
4678 l_resp_id NUMBER := 0;
4679 l_return_status VARCHAR2(1);
4680 l_api_name CONSTANT VARCHAR2(30):= 'clear_agreement';
4681
4682 BEGIN
4683
4684 -- Standard begin of API savepoint
4685
4686 SAVEPOINT clear_agreement_pub;
4687
4688
4689 -- CALL THE INIT AGREEMENT PROCEDURE
4690 pa_agreement_pub.init_agreement;
4691
4692 END clear_agreement;
4693
4694 -- ============================================================================
4695 --
4696 --Name: check_delete_agreement_ok
4697 --Type: Procedure
4698 --Description: This procedure can be used to check whether it is OK
4699 -- to delete an agreement.
4700 --Called subprograms: XXX
4701 --
4702 --
4703 --
4704 --History:
4705 -- 25-MAR-2000 Rakesh Raghavan Created.
4706 -- ---------------------------------------------------------------------------
4707
4708 PROCEDURE check_delete_agreement_ok
4709 (p_api_version_number IN NUMBER
4710 ,p_commit IN VARCHAR2
4711 ,p_init_msg_list IN VARCHAR2
4712 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
4713 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
4714 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4715 ,p_pm_agreement_reference IN VARCHAR2
4716 ,p_agreement_id IN NUMBER
4717 ,p_del_agree_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
4718 )
4719 IS
4720 -- LOCAL VARIABLES
4721 l_msg_count NUMBER ;
4722 l_msg_data VARCHAR2(2000);
4723 l_function_allowed VARCHAR2(1);
4724 l_resp_id NUMBER := 0;
4725 l_out_agreement_id NUMBER ;
4726 l_return_status VARCHAR2(1);
4727 l_api_name CONSTANT VARCHAR2(30):= 'check_delete_agreement_ok';
4728
4729 BEGIN
4730
4731 -- Standard begin of API savepoint
4732
4733 SAVEPOINT check_delete_agreement_ok_pub;
4734
4735
4736 -- Standard call to check for call compatibility.
4737
4738
4739 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4740 p_api_version_number ,
4741 l_api_name ,
4742 G_PKG_NAME )
4743 THEN
4744
4745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4746
4747 END IF;
4748 l_resp_id := FND_GLOBAL.Resp_id;
4749
4750 -- Actions performed using the APIs would be subject to
4751 -- function security. If the responsibility does not allow
4752 -- such functions to be executed, the API should not proceed further
4753 -- since the user does not have access to such functions
4754
4755
4756 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4757 (p_api_version_number => p_api_version_number,
4758 p_responsibility_id => l_resp_id,
4759 p_function_name => 'PA_AF_DEL_AGMT_OK',
4760 p_msg_count => l_msg_count,
4761 p_msg_data => l_msg_data,
4762 p_return_status => l_return_status,
4763 p_function_allowed => l_function_allowed
4764 );
4765
4766 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4767 THEN
4768 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4769
4770 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4771 THEN
4772 RAISE FND_API.G_EXC_ERROR;
4773 END IF;
4774 IF l_function_allowed = 'N' THEN
4775 pa_interface_utils_pub.map_new_amg_msg
4776 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4777 ,p_msg_attribute => 'CHANGE'
4778 ,p_resize_flag => 'Y'
4779 ,p_msg_context => 'GENERAL'
4780 ,p_attribute1 => ''
4781 ,p_attribute2 => ''
4782 ,p_attribute3 => ''
4783 ,p_attribute4 => ''
4784 ,p_attribute5 => '');
4785 p_return_status := FND_API.G_RET_STS_ERROR;
4786 RAISE FND_API.G_EXC_ERROR;
4787 END IF;
4788
4789 -- Initialize the message table if requested.
4790
4791 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4792 THEN
4793
4794 FND_MSG_PUB.initialize;
4795
4796 END IF;
4797
4798 -- Set API return status to success
4799
4800 p_return_status := FND_API.G_RET_STS_SUCCESS;
4801
4802
4803 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
4804
4805 -- Agreement Reference
4806 IF (p_pm_agreement_reference IS NULL)
4807 OR (p_pm_agreement_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
4808 THEN
4809 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4810 THEN
4811 pa_interface_utils_pub.map_new_amg_msg
4812 ( p_old_message_code => 'PA_AGMT_REF_IS_MISS'
4813 ,p_msg_attribute => 'CHANGE'
4814 ,p_resize_flag => 'N'
4815 ,p_msg_context => 'GENERAL'
4816 ,p_attribute1 => ''
4817 ,p_attribute2 => ''
4818 ,p_attribute3 => ''
4819 ,p_attribute4 => ''
4820 ,p_attribute5 => '');
4821 END IF;
4822 p_return_status := FND_API.G_RET_STS_ERROR;
4823 RAISE FND_API.G_EXC_ERROR;
4824 END IF;
4825 /* NOT REQUIRED
4826 -- Agreement Id
4827 IF (p_agreement_id IS NULL)
4828 OR (p_agreement_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
4829 THEN
4830 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4831 THEN
4832 pa_interface_utils_pub.map_new_amg_msg
4833 ( p_old_message_code => 'PA_AGMT_ID_IS_MISS'
4834 ,p_msg_attribute => 'CHANGE'
4835 ,p_resize_flag => 'N'
4836 ,p_msg_context => 'AGREEMENT'
4837 ,p_attribute1 => p_pm_agreement_reference
4838 ,p_attribute2 => ''
4839 ,p_attribute3 => ''
4840 ,p_attribute4 => ''
4841 ,p_attribute5 => '');
4842 END IF;
4843 p_return_status := FND_API.G_RET_STS_ERROR;
4844 RAISE FND_API.G_EXC_ERROR;
4845 END IF;
4846 */
4847 -- VALIDATE THE INCOMING PARAMETERS
4848
4849 -- TO BE CORRECTED - NIKHIL
4850 -- Agreement Reference
4851 IF pa_agreement_utils.check_valid_agreement_ref(p_agreement_reference => p_pm_agreement_reference) = 'N'
4852 THEN
4853 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4854 THEN
4855 pa_interface_utils_pub.map_new_amg_msg
4856 ( p_old_message_code => 'PA_INVD_AGMT_REF'
4857 ,p_msg_attribute => 'CHANGE'
4858 ,p_resize_flag => 'N'
4859 ,p_msg_context => 'GENERAL'
4860 ,p_attribute1 => ''
4861 ,p_attribute2 => ''
4862 ,p_attribute3 => ''
4863 ,p_attribute4 => ''
4864 ,p_attribute5 => '');
4865 END IF;
4866 p_return_status := FND_API.G_RET_STS_ERROR;
4867 RAISE FND_API.G_EXC_ERROR;
4868 END IF;
4869
4870 /* NOT REQUIRED
4871 -- Agreement Id
4872 IF pa_agreement_utils.check_valid_agreement_id(p_agreement_id => p_agreement_id) = 'N'
4873 THEN
4874 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4875 THEN
4876 pa_interface_utils_pub.map_new_amg_msg
4877 ( p_old_message_code => 'PA_INVD_AGMT_ID'
4878 ,p_msg_attribute => 'CHANGE'
4879 ,p_resize_flag => 'N'
4880 ,p_msg_context => 'AGREEMENT'
4881 ,p_attribute1 => p_pm_agreement_reference
4882 ,p_attribute2 => ''
4883 ,p_attribute3 => ''
4884 ,p_attribute4 => ''
4885 ,p_attribute5 => '');
4886 END IF;
4887 p_return_status := FND_API.G_RET_STS_ERROR;
4888 RAISE FND_API.G_EXC_ERROR;
4889 END IF;
4890 */
4891
4892 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
4893 pa_agreement_pvt.convert_ag_ref_to_id
4894 (p_pm_agreement_reference => p_pm_agreement_reference
4895 ,p_af_agreement_id => p_agreement_id
4896 ,p_out_agreement_id => l_out_agreement_id
4897 ,p_return_status => l_return_status);
4898 p_return_status := l_return_status;
4899
4900 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4901 THEN
4902 IF l_return_status = FND_API.G_RET_STS_ERROR
4903 THEN
4904 RAISE FND_API.G_EXC_ERROR;
4905 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4906 THEN
4907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4908 ELSE
4909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4910 END IF;
4911 END IF;
4912
4913 -- CHECK IF IT IS OK TO DELETE THE AGREEMENT
4914 p_del_agree_ok_flag := pa_agreement_pvt.check_delete_agreement_ok
4915 (p_agreement_id => l_out_agreement_id
4916 ,p_pm_agreement_reference => p_pm_agreement_reference);
4917
4918 IF FND_API.to_boolean( p_commit )
4919 THEN
4920 COMMIT;
4921 END IF;
4922
4923 EXCEPTION
4924 WHEN FND_API.G_EXC_ERROR
4925 THEN
4926 ROLLBACK TO check_delete_agreement_ok_pub;
4927 p_return_status := FND_API.G_RET_STS_ERROR;
4928 FND_MSG_PUB.Count_And_Get
4929 ( p_count => p_msg_count ,
4930 p_data => p_msg_data );
4931
4932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4933 THEN
4934 ROLLBACK TO check_delete_agreement_ok_pub;
4935 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4936 FND_MSG_PUB.Count_And_Get
4937 ( p_count => p_msg_count ,
4938 p_data => p_msg_data );
4939
4940 WHEN OTHERS
4941 THEN
4942 ROLLBACK TO check_delete_agreement_ok_pub;
4943 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4944 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4945 THEN
4946 FND_MSG_PUB.add_exc_msg
4947 ( p_pkg_name => G_PKG_NAME
4948 , p_procedure_name => l_api_name );
4949
4950 END IF;
4951 FND_MSG_PUB.Count_And_Get
4952 ( p_count => p_msg_count ,
4953 p_data => p_msg_data );
4954
4955
4956 END check_delete_agreement_ok;
4957
4958 -- ============================================================================
4959 --
4960 --Name: check_add_funding_ok
4961 --Type: Procedure
4962 --Description: This procedure can be used to check whether it is OK
4963 -- to add a funding.
4964 --Called subprograms: XXX
4965 --
4966 --
4967 --
4968 --History:
4969 -- 25-MAR-2000 Rakesh Raghavan Created.
4970 -- ---------------------------------------------------------------------------
4971
4972 PROCEDURE check_add_funding_ok
4973 (p_api_version_number IN NUMBER
4974 ,p_commit IN VARCHAR2
4975 ,p_init_msg_list IN VARCHAR2
4976 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
4977 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
4978 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4979 ,p_pm_agreement_reference IN VARCHAR2
4980 ,p_agreement_id IN NUMBER
4981 ,p_pm_funding_reference IN VARCHAR2
4982 ,p_task_id IN NUMBER
4983 ,p_project_id IN NUMBER
4984 ,p_add_funding_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
4985 ,p_funding_amt IN NUMBER
4986 ,p_project_rate_type IN VARCHAR2 DEFAULT NULL
4987 ,p_project_rate_date IN DATE DEFAULT NULL
4988 ,p_project_exchange_rate IN NUMBER DEFAULT NULL
4989 ,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
4990 ,p_projfunc_rate_date IN DATE DEFAULT NULL
4991 ,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
4992 )
4993 IS
4994 -- added for validating the funding amount
4995 -- LOCAL VARIABLES
4996 l_msg_count NUMBER ;
4997 l_msg_data VARCHAR2(2000);
4998 l_function_allowed VARCHAR2(1);
4999 l_resp_id NUMBER := 0;
5000 l_out_agreement_id NUMBER ;
5001 l_return_status VARCHAR2(1);
5002 l_api_name CONSTANT VARCHAR2(30):= 'check_add_funding_ok';
5003 l_customer_id NUMBER ;
5004 BEGIN
5005 -- Standard begin of API savepoint
5006
5007 SAVEPOINT check_add_funding_ok_pub;
5008
5009
5010 -- Standard call to check for call compatibility.
5011
5012
5013 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
5014 p_api_version_number ,
5015 l_api_name ,
5016 G_PKG_NAME )
5017 THEN
5018
5019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5020
5021 END IF;
5022 l_resp_id := FND_GLOBAL.Resp_id;
5023
5024 -- This call is added for patchset K project role based security check
5025
5026 PA_INTERFACE_UTILS_PUB.G_PROJECt_ID := p_project_id;
5027
5028
5029
5030 -- Actions performed using the APIs would be subject to
5031 -- function security. If the responsibility does not allow
5032 -- such functions to be executed, the API should not proceed further
5033 -- since the user does not have access to such functions
5034
5035
5036 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
5037 (p_api_version_number => p_api_version_number,
5038 p_responsibility_id => l_resp_id,
5039 p_function_name => 'PA_AF_ADD_FUND_OK',
5040 p_msg_count => l_msg_count,
5041 p_msg_data => l_msg_data,
5042 p_return_status => l_return_status,
5043 p_function_allowed => l_function_allowed
5044 );
5045
5046 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
5047 THEN
5048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5049
5050 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
5051 THEN
5052 RAISE FND_API.G_EXC_ERROR;
5053 END IF;
5054 IF l_function_allowed = 'N' THEN
5055 pa_interface_utils_pub.map_new_amg_msg
5056 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
5057 ,p_msg_attribute => 'CHANGE'
5058 ,p_resize_flag => 'Y'
5059 ,p_msg_context => 'GENERAL'
5060 ,p_attribute1 => ''
5061 ,p_attribute2 => ''
5062 ,p_attribute3 => ''
5063 ,p_attribute4 => ''
5064 ,p_attribute5 => '');
5065 p_return_status := FND_API.G_RET_STS_ERROR;
5066 RAISE FND_API.G_EXC_ERROR;
5067 END IF;
5068
5069 -- Initialize the message table if requested.
5070
5071 IF FND_API.TO_BOOLEAN( p_init_msg_list )
5072 THEN
5073
5074 FND_MSG_PUB.initialize;
5075
5076 END IF;
5077
5078 -- Set API return status to success
5079
5080 p_return_status := FND_API.G_RET_STS_SUCCESS;
5081
5082 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
5083
5084 -- Agreement Reference
5085 IF (p_pm_agreement_reference IS NULL)
5086 OR (p_pm_agreement_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
5087 THEN
5088 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5089 THEN
5090 pa_interface_utils_pub.map_new_amg_msg
5091 ( p_old_message_code => 'PA_AGMT_REF_IS_MISS'
5092 ,p_msg_attribute => 'CHANGE'
5093 ,p_resize_flag => 'N'
5094 ,p_msg_context => 'GENERAL'
5095 ,p_attribute1 => ''
5096 ,p_attribute2 => ''
5097 ,p_attribute3 => ''
5098 ,p_attribute4 => ''
5099 ,p_attribute5 => '');
5100 END IF;
5101 p_return_status := FND_API.G_RET_STS_ERROR;
5102 RAISE FND_API.G_EXC_ERROR;
5103 END IF;
5104
5105 -- Project Id
5106 IF (p_project_id IS NULL)
5107 OR (p_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
5108 THEN
5109 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5110 THEN
5111 pa_interface_utils_pub.map_new_amg_msg
5112 ( p_old_message_code => 'PA_PROJ_ID_IS_MISS'
5113 ,p_msg_attribute => 'CHANGE'
5114 ,p_resize_flag => 'N'
5115 ,p_msg_context => 'FUNDING'
5116 ,p_attribute1 => ''
5117 ,p_attribute2 => p_pm_funding_reference
5118 ,p_attribute3 => ''
5119 ,p_attribute4 => ''
5120 ,p_attribute5 => '');
5121 END IF;
5122 p_return_status := FND_API.G_RET_STS_ERROR;
5123 RAISE FND_API.G_EXC_ERROR;
5124 END IF;
5125 /*
5126 -- Task Id
5127 IF (p_task_id IS NULL)
5128 OR (p_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
5129 THEN
5130 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5131 THEN
5132 pa_interface_utils_pub.map_new_amg_msg
5133 ( p_old_message_code => 'PA_TASK_ID_IS_MISS'
5134 ,p_msg_attribute => 'CHANGE'
5135 ,p_resize_flag => 'N'
5136 ,p_msg_context => 'FUNDING'
5137 ,p_attribute1 => ''
5138 ,p_attribute2 => p_pm_funding_reference
5139 ,p_attribute3 => ''
5140 ,p_attribute4 => ''
5141 ,p_attribute5 => '');
5142 END IF;
5143 p_return_status := FND_API.G_RET_STS_ERROR;
5144 RAISE FND_API.G_EXC_ERROR;
5145 END IF;
5146 */
5147 -- Funding Reference
5148 IF (p_pm_funding_reference IS NULL)
5149 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
5150 THEN
5151 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5152 THEN
5153 pa_interface_utils_pub.map_new_amg_msg
5154 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
5155 ,p_msg_attribute => 'CHANGE'
5156 ,p_resize_flag => 'N'
5157 ,p_msg_context => 'GENERAL'
5158 ,p_attribute1 => ''
5159 ,p_attribute2 => ''
5160 ,p_attribute3 => ''
5161 ,p_attribute4 => ''
5162 ,p_attribute5 => '');
5163 END IF;
5164 p_return_status := FND_API.G_RET_STS_ERROR;
5165 RAISE FND_API.G_EXC_ERROR;
5166 END IF;
5167
5168 /* NOT REQUIRED
5169 -- Funding Id
5170 IF (p_funding_id IS NULL)
5171 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
5172 THEN
5173 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5174 THEN
5175 pa_interface_utils_pub.map_new_amg_msg
5176 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
5177 ,p_msg_attribute => 'CHANGE'
5178 ,p_resize_flag => 'N'
5179 ,p_msg_context => 'GENERAL'
5180 ,p_attribute1 => ''
5181 ,p_attribute2 => ''
5182 ,p_attribute3 => ''
5183 ,p_attribute4 => ''
5184 ,p_attribute5 => '');
5185 END IF;
5186 p_return_status := FND_API.G_RET_STS_ERROR;
5187 RAISE FND_API.G_EXC_ERROR;
5188 END IF;
5189 */
5190
5191 -- VALIDATE THE INCOMING PARAMETERS
5192
5193 --TO BE CORRECTED - NIKHIL
5194 -- Agreement Reference
5195 IF pa_agreement_utils.check_valid_agreement_ref(p_agreement_reference => p_pm_agreement_reference) = 'N'
5196 THEN
5197 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5198 THEN
5199 pa_interface_utils_pub.map_new_amg_msg
5200 ( p_old_message_code => 'PA_INVD_AGMT_REF'
5201 ,p_msg_attribute => 'CHANGE'
5202 ,p_resize_flag => 'Y'
5203 ,p_msg_context => 'AGREEMENT'
5204 ,p_attribute1 => ''
5205 ,p_attribute2 => ''
5206 ,p_attribute3 => ''
5207 ,p_attribute4 => ''
5208 ,p_attribute5 => '');
5209 END IF;
5210 p_return_status := FND_API.G_RET_STS_ERROR;
5211 RAISE FND_API.G_EXC_ERROR;
5212 END IF;
5213 /* NOT REQUIRED
5214 -- Funding Reference
5215 IF pa_agreement_utils.check_valid_funding_ref(p_funding_reference => p_pm_funding_reference
5216 ,p_agreement_id => p_agreement_id) = 'N'
5217 THEN
5218 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5219 THEN
5220 pa_interface_utils_pub.map_new_amg_msg
5221 ( p_old_message_code => 'PA_INVD_FUND_REF'
5222 ,p_msg_attribute => 'CHANGE'
5223 ,p_resize_flag => 'N'
5224 ,p_msg_context => 'FUNDING'
5225 ,p_attribute1 => ''
5226 ,p_attribute2 => p_pm_funding_reference
5227 ,p_attribute3 => ''
5228 ,p_attribute4 => ''
5229 ,p_attribute5 => '');
5230 END IF;
5231 p_return_status := FND_API.G_RET_STS_ERROR;
5232 RAISE FND_API.G_EXC_ERROR;
5233 END IF;
5234 */
5235 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
5236 pa_agreement_pvt.convert_ag_ref_to_id
5237 (p_pm_agreement_reference => p_pm_agreement_reference
5238 ,p_af_agreement_id => p_agreement_id
5239 ,p_out_agreement_id => l_out_agreement_id
5240 ,p_return_status => l_return_status);
5241 p_return_status := l_return_status;
5242
5243 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
5244 THEN
5245 IF l_return_status = FND_API.G_RET_STS_ERROR
5246 THEN
5247 RAISE FND_API.G_EXC_ERROR;
5248 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
5249 THEN
5250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5251 ELSE
5252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5253 END IF;
5254 END IF;
5255
5256 -- TO BE CORRECTED - NIKHIL
5257 -- CHECK IF IT IS OK TO ADD FUNDING
5258
5259 SELECT a. customer_id
5260 INTO l_customer_id
5261 FROM pa_agreements_all a
5262 WHERE a.agreement_id = p_agreement_id;
5263
5264 p_add_funding_ok_flag := pa_agreement_pvt.check_add_funding_ok
5265 (p_project_id => p_project_id
5266 ,p_task_id => p_task_id
5267 ,p_agreement_id => l_out_agreement_id
5268 ,p_pm_funding_reference => p_pm_funding_reference
5269 ,p_funding_amt => p_funding_amt
5270 ,p_customer_id => l_customer_id
5271 /* MCB2 PARAMETERS BEGIN */
5272 ,p_project_rate_type => p_project_rate_type
5273 ,p_project_rate_date => p_project_rate_date
5274 ,p_project_exchange_rate => p_project_exchange_rate
5275 ,p_projfunc_rate_type => p_projfunc_rate_type
5276 ,p_projfunc_rate_date => p_projfunc_rate_date
5277 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate );
5278 /* MCB2 PARAMETERS END */
5279
5280 IF FND_API.to_boolean( p_commit )
5281 THEN
5282 COMMIT;
5283 END IF;
5284
5285 EXCEPTION
5286 WHEN FND_API.G_EXC_ERROR
5287 THEN
5288 ROLLBACK TO check_add_funding_ok_pub;
5289 p_return_status := FND_API.G_RET_STS_ERROR;
5290 FND_MSG_PUB.Count_And_Get
5291 ( p_count => p_msg_count ,
5292 p_data => p_msg_data );
5293
5294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
5295 THEN
5296 ROLLBACK TO check_add_funding_ok_pub;
5297 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5298 FND_MSG_PUB.Count_And_Get
5299 ( p_count => p_msg_count ,
5300 p_data => p_msg_data );
5301
5302 WHEN OTHERS
5303 THEN
5304 ROLLBACK TO check_add_funding_ok_pub;
5305 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5306 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5307 THEN
5308 FND_MSG_PUB.add_exc_msg
5309 ( p_pkg_name => G_PKG_NAME
5310 , p_procedure_name => l_api_name );
5311
5312 END IF;
5313 FND_MSG_PUB.Count_And_Get
5314 ( p_count => p_msg_count ,
5315 p_data => p_msg_data );
5316
5317
5318 END check_add_funding_ok;
5319
5320 -- ============================================================================
5321 --
5322 --Name: check_delete_funding_ok
5323 --Type: Procedure
5324 --Description: This procedure can be used to check whether it is OK
5325 -- to delete a funding.
5326 --Called subprograms: XXX
5327 --
5328 --
5329 --
5330 --History:
5331 -- 25-MAR-2000 Rakesh Raghavan Created.
5332 -- ---------------------------------------------------------------------------
5333
5334 PROCEDURE check_delete_funding_ok
5335 (p_api_version_number IN NUMBER
5336 ,p_commit IN VARCHAR2
5337 ,p_init_msg_list IN VARCHAR2
5338 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
5339 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
5340 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
5341 ,p_pm_funding_reference IN VARCHAR2
5342 ,p_funding_id IN NUMBER
5343 ,p_del_funding_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
5344 )
5345 IS
5346 -- LOCAL VARIABLES
5347 l_msg_count NUMBER ;
5348 l_msg_data VARCHAR2(2000);
5349 l_function_allowed VARCHAR2(1);
5350 l_resp_id NUMBER := 0;
5351 l_out_agreement_id NUMBER ;
5352 l_return_status VARCHAR2(1);
5353 l_api_name CONSTANT VARCHAR2(30):= 'check_delete_funding_ok';
5354 l_project_id NUMBER;
5355
5356 BEGIN
5357 -- Standard begin of API savepoint
5358
5359 SAVEPOINT check_delete_funding_ok_pub;
5360
5361
5362 -- Standard call to check for call compatibility.
5363
5364
5365 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
5366 p_api_version_number ,
5367 l_api_name ,
5368 G_PKG_NAME )
5369 THEN
5370
5371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5372
5373 END IF;
5374 l_resp_id := FND_GLOBAL.Resp_id;
5375
5376 -- Get the project id from this project funding line
5377
5378 l_project_id := pa_agreement_utils.get_project_id(p_funding_id => p_funding_id,
5379 p_funding_reference => p_pm_funding_reference);
5380
5381 -- This call is added for patchset K project role based security check
5382
5383 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := l_project_id;
5384
5385 -- Actions performed using the APIs would be subject to
5386 -- function security. If the responsibility does not allow
5387 -- such functions to be executed, the API should not proceed further
5388 -- since the user does not have access to such functions
5389
5390
5391 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
5392 (p_api_version_number => p_api_version_number,
5393 p_responsibility_id => l_resp_id,
5394 p_function_name => 'PA_AF_DEL_FUND_OK',
5395 p_msg_count => l_msg_count,
5396 p_msg_data => l_msg_data,
5397 p_return_status => l_return_status,
5398 p_function_allowed => l_function_allowed
5399 );
5400
5401 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
5402 THEN
5403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5404
5405 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
5406 THEN
5407 RAISE FND_API.G_EXC_ERROR;
5408 END IF;
5409 IF l_function_allowed = 'N' THEN
5410 pa_interface_utils_pub.map_new_amg_msg
5411 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
5412 ,p_msg_attribute => 'CHANGE'
5413 ,p_resize_flag => 'Y'
5414 ,p_msg_context => 'GENERAL'
5415 ,p_attribute1 => ''
5416 ,p_attribute2 => ''
5417 ,p_attribute3 => ''
5418 ,p_attribute4 => ''
5419 ,p_attribute5 => '');
5420 p_return_status := FND_API.G_RET_STS_ERROR;
5421 RAISE FND_API.G_EXC_ERROR;
5422 END IF;
5423
5424 -- Initialize the message table if requested.
5425
5426 IF FND_API.TO_BOOLEAN( p_init_msg_list )
5427 THEN
5428
5429 FND_MSG_PUB.initialize;
5430
5431 END IF;
5432
5433 -- Set API return status to success
5434
5435 p_return_status := FND_API.G_RET_STS_SUCCESS;
5436
5437 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
5438
5439 -- Funding Reference
5440 IF (p_pm_funding_reference IS NULL)
5441 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
5442 THEN
5443 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5444 THEN
5445 pa_interface_utils_pub.map_new_amg_msg
5446 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
5447 ,p_msg_attribute => 'CHANGE'
5448 ,p_resize_flag => 'N'
5449 ,p_msg_context => 'GENERAL'
5450 ,p_attribute1 => ''
5451 ,p_attribute2 => ''
5452 ,p_attribute3 => ''
5453 ,p_attribute4 => ''
5454 ,p_attribute5 => '');
5455 END IF;
5456 p_return_status := FND_API.G_RET_STS_ERROR;
5457 RAISE FND_API.G_EXC_ERROR;
5458 END IF;
5459 /* NOT REQUIRED
5460 -- Funding Id
5461 IF (p_funding_id IS NULL)
5462 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
5463 THEN
5464 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5465 THEN
5466 pa_interface_utils_pub.map_new_amg_msg
5467 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
5468 ,p_msg_attribute => 'CHANGE'
5469 ,p_resize_flag => 'N'
5470 ,p_msg_context => 'GENERAL'
5471 ,p_attribute1 => ''
5472 ,p_attribute2 => ''
5473 ,p_attribute3 => ''
5474 ,p_attribute4 => ''
5475 ,p_attribute5 => '');
5476 END IF;
5477 p_return_status := FND_API.G_RET_STS_ERROR;
5478 RAISE FND_API.G_EXC_ERROR;
5479 END IF;
5480 */
5481
5482 -- VALIDATE THE INCOMING PARAMETERS
5483 --TO BE CORRECTED - NIKHIL
5484 -- Funding Reference
5485 IF pa_agreement_utils.check_valid_funding_ref
5486 (p_funding_reference => p_pm_funding_reference
5487 ,p_agreement_id => pa_agreement_utils.get_agreement_id(p_funding_id => p_funding_id
5488 ,p_funding_reference => p_pm_funding_reference)) = 'N'
5489 THEN
5490 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5491 THEN
5492 pa_interface_utils_pub.map_new_amg_msg
5493 ( p_old_message_code => 'PA_INVD_FUND_REF'
5494 ,p_msg_attribute => 'CHANGE'
5495 ,p_resize_flag => 'N'
5496 ,p_msg_context => 'AGREEMENT'
5497 ,p_attribute1 => ''
5498 ,p_attribute2 => ''
5499 ,p_attribute3 => ''
5500 ,p_attribute4 => ''
5501 ,p_attribute5 => '');
5502 END IF;
5503 p_return_status := FND_API.G_RET_STS_ERROR;
5504 RAISE FND_API.G_EXC_ERROR;
5505 END IF;
5506
5507 -- Funding Id
5508 IF pa_agreement_utils.check_valid_funding_id(p_agreement_id => pa_agreement_utils.get_agreement_id(p_funding_id => p_funding_id
5509 ,p_funding_reference => p_pm_funding_reference)
5510 ,p_funding_id => pa_agreement_utils.get_funding_id(p_funding_reference => p_pm_funding_reference)) = 'N'
5511 THEN
5512 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5513 THEN
5514 pa_interface_utils_pub.map_new_amg_msg
5515 ( p_old_message_code => 'PA_INVD_FUND_ID'
5516 ,p_msg_attribute => 'CHANGE'
5517 ,p_resize_flag => 'N'
5518 ,p_msg_context => 'FUNDING'
5519 ,p_attribute1 => ''
5520 ,p_attribute2 => 'p_pm_funding_reference'
5521 ,p_attribute3 => ''
5522 ,p_attribute4 => ''
5523 ,p_attribute5 => '');
5524 END IF;
5525 p_return_status := FND_API.G_RET_STS_ERROR;
5526 RAISE FND_API.G_EXC_ERROR;
5527 END IF;
5528
5529 -- CALL THE CHECK DELETE FUNDING OK PRIVATE PROCEDURE
5530
5531 p_del_funding_ok_flag := pa_agreement_pvt.check_delete_funding_ok
5532 (p_agreement_id => pa_agreement_utils.get_agreement_id(p_funding_id => p_funding_id
5533 ,p_funding_reference => p_pm_funding_reference)
5534 ,p_funding_id => pa_agreement_utils.get_funding_id(p_funding_reference => p_pm_funding_reference)
5535 ,p_pm_funding_reference => p_pm_funding_reference);
5536
5537
5538
5539 IF FND_API.to_boolean( p_commit )
5540 THEN
5541 COMMIT;
5542 END IF;
5543
5544 EXCEPTION
5545
5546 WHEN FND_API.G_EXC_ERROR
5547 THEN
5548 ROLLBACK TO check_delete_funding_ok_pub;
5549
5550 p_return_status := FND_API.G_RET_STS_ERROR;
5551
5552
5553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
5554 THEN
5555 ROLLBACK TO check_delete_funding_ok_pub;
5556
5557 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5558
5559
5560 WHEN OTHERS
5561 THEN
5562 ROLLBACK TO check_delete_funding_ok_pub;
5563
5564 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5565
5566 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5567 THEN
5568 FND_MSG_PUB.add_exc_msg
5569 ( p_pkg_name => G_PKG_NAME
5570 , p_procedure_name => l_api_name );
5571
5572 END IF;
5573
5574
5575 END check_delete_funding_ok;
5576
5577 -- ============================================================================
5578 --
5579 --Name: check_update_funding_ok
5580 --Type: Procedure
5581 --Description: This procedure can be used to check whether it is OK
5582 -- to update a funding.
5583 --Called subprograms: XXX
5584 --
5585 --
5586 --
5587 --History:
5588 -- 25-MAR-2000 Rakesh Raghavan Created.
5589 -- ---------------------------------------------------------------------------
5590
5591 PROCEDURE check_update_funding_ok
5592 (p_api_version_number IN NUMBER
5593 ,p_commit IN VARCHAR2
5594 ,p_init_msg_list IN VARCHAR2
5595 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
5596 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
5597 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
5598 ,p_pm_product_code IN VARCHAR2
5599 ,p_pm_funding_reference IN VARCHAR2
5600 ,p_funding_id IN NUMBER
5601 ,p_pm_project_reference IN VARCHAR2
5602 ,p_project_id IN NUMBER
5603 ,p_pm_task_reference IN VARCHAR2
5604 ,p_task_id IN NUMBER
5605 ,p_pm_agreement_reference IN VARCHAR2
5606 ,p_agreement_id IN NUMBER
5607 ,p_allocated_amount IN NUMBER
5608 ,p_date_allocated IN DATE
5609 ,p_desc_flex_name IN VARCHAR2
5610 ,p_attribute_category IN VARCHAR2
5611 ,p_attribute1 IN VARCHAR2
5612 ,p_attribute2 IN VARCHAR2
5613 ,p_attribute3 IN VARCHAR2
5614 ,p_attribute4 IN VARCHAR2
5615 ,p_attribute5 IN VARCHAR2
5616 ,p_attribute6 IN VARCHAR2
5617 ,p_attribute7 IN VARCHAR2
5618 ,p_attribute8 IN VARCHAR2
5619 ,p_attribute9 IN VARCHAR2
5620 ,p_attribute10 IN VARCHAR2
5621 ,p_update_funding_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
5622 ,p_project_rate_type IN VARCHAR2 DEFAULT NULL
5623 ,p_project_rate_date IN DATE DEFAULT NULL
5624 ,p_project_exchange_rate IN NUMBER DEFAULT NULL
5625 ,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
5626 ,p_projfunc_rate_date IN DATE DEFAULT NULL
5627 ,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
5628 ,p_funding_category IN VARCHAR2 DEFAULT 'ADDITIONAL'
5629 /* Added for Bug 2483081 to include Default value - For Bug 2244796 */
5630 )
5631 IS
5632 -- LOCAL VARIABLES
5633 l_msg_count NUMBER ;
5634 l_msg_data VARCHAR2(2000);
5635 l_function_allowed VARCHAR2(1);
5636 l_resp_id NUMBER := 0;
5637 l_out_agreement_id NUMBER ;
5638 l_return_msg VARCHAR2(2000);
5639 l_validate_status VARCHAR2(1);
5640 l_return_status VARCHAR2(1);
5641 l_api_name CONSTANT VARCHAR2(30):= 'check_update_funding_ok';
5642 BEGIN
5643 -- Standard begin of API savepoint
5644
5645 SAVEPOINT check_update_funding_ok_pub;
5646
5647
5648 -- Standard call to check for call compatibility.
5649
5650
5651 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
5652 p_api_version_number ,
5653 l_api_name ,
5654 G_PKG_NAME )
5655 THEN
5656
5657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5658
5659 END IF;
5660 l_resp_id := FND_GLOBAL.Resp_id;
5661
5662 -- This call is added for patchset K project role based security check
5663
5664 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := p_project_id;
5665
5666
5667 -- Actions performed using the APIs would be subject to
5668 -- function security. If the responsibility does not allow
5669 -- such functions to be executed, the API should not proceed further
5670 -- since the user does not have access to such functions
5671
5672
5673 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
5674 (p_api_version_number => p_api_version_number,
5675 p_responsibility_id => l_resp_id,
5676 p_function_name => 'PA_AF_UPD_FUND_OK',
5677 p_msg_count => l_msg_count,
5678 p_msg_data => l_msg_data,
5679 p_return_status => l_return_status,
5680 p_function_allowed => l_function_allowed
5681 );
5682
5683 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
5684 THEN
5685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5686
5687 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
5688 THEN
5689 RAISE FND_API.G_EXC_ERROR;
5690 END IF;
5691 IF l_function_allowed = 'N' THEN
5692 pa_interface_utils_pub.map_new_amg_msg
5693 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
5694 ,p_msg_attribute => 'CHANGE'
5695 ,p_resize_flag => 'Y'
5696 ,p_msg_context => 'GENERAL'
5697 ,p_attribute1 => ''
5698 ,p_attribute2 => ''
5699 ,p_attribute3 => ''
5700 ,p_attribute4 => ''
5701 ,p_attribute5 => '');
5702 p_return_status := FND_API.G_RET_STS_ERROR;
5703 RAISE FND_API.G_EXC_ERROR;
5704 END IF;
5705
5706 -- Initialize the message table if requested.
5707
5708 IF FND_API.TO_BOOLEAN( p_init_msg_list )
5709 THEN
5710
5711 FND_MSG_PUB.initialize;
5712
5713 END IF;
5714
5715 -- Set API return status to success
5716
5717 p_return_status := FND_API.G_RET_STS_SUCCESS;
5718
5719 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
5720
5721 -- Funding Reference
5722 IF (p_pm_funding_reference IS NULL)
5723 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
5724 THEN
5725 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5726 THEN
5727 pa_interface_utils_pub.map_new_amg_msg
5728 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
5729 ,p_msg_attribute => 'CHANGE'
5730 ,p_resize_flag => 'N'
5731 ,p_msg_context => 'GENERAL'
5732 ,p_attribute1 => ''
5733 ,p_attribute2 => ''
5734 ,p_attribute3 => ''
5735 ,p_attribute4 => ''
5736 ,p_attribute5 => '');
5737 END IF;
5738 p_return_status := FND_API.G_RET_STS_ERROR;
5739 RAISE FND_API.G_EXC_ERROR;
5740 END IF;
5741
5742 -- Funding Id
5743 IF (p_funding_id IS NULL)
5744 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
5745 THEN
5746 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5747 THEN
5748 pa_interface_utils_pub.map_new_amg_msg
5749 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
5750 ,p_msg_attribute => 'CHANGE'
5751 ,p_resize_flag => 'N'
5752 ,p_msg_context => 'GENERAL'
5753 ,p_attribute1 => ''
5754 ,p_attribute2 => ''
5755 ,p_attribute3 => ''
5756 ,p_attribute4 => ''
5757 ,p_attribute5 => '');
5758 END IF;
5759 p_return_status := FND_API.G_RET_STS_ERROR;
5760 RAISE FND_API.G_EXC_ERROR;
5761 END IF;
5762
5763 -- VALIDATE THE INCOMING PARAMETERS
5764
5765 -- Funding Reference
5766 IF pa_agreement_utils.check_valid_funding_ref(p_funding_reference => p_pm_funding_reference
5767 ,p_agreement_id => p_agreement_id) = 'N'
5768 THEN
5769 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5770 THEN
5771 pa_interface_utils_pub.map_new_amg_msg
5772 ( p_old_message_code => 'PA_INVD_FUND_REF'
5773 ,p_msg_attribute => 'CHANGE'
5774 ,p_resize_flag => 'Y'
5775 ,p_msg_context => 'AGREEMENT'
5776 ,p_attribute1 => ''
5777 ,p_attribute2 => ''
5778 ,p_attribute3 => ''
5779 ,p_attribute4 => ''
5780 ,p_attribute5 => '');
5781 END IF;
5782 p_return_status := FND_API.G_RET_STS_ERROR;
5783 RAISE FND_API.G_EXC_ERROR;
5784 END IF;
5785
5786 -- Agreement Id
5787 IF pa_agreement_utils.check_valid_agreement_id
5788 (p_agreement_id => p_agreement_id) = 'N'
5789 THEN
5790 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5791 THEN
5792 pa_interface_utils_pub.map_new_amg_msg
5793 ( p_old_message_code => 'PA_INVALID_FUNDING_ID'
5794 ,p_msg_attribute => 'CHANGE'
5795 ,p_resize_flag => 'Y'
5796 ,p_msg_context => 'AGREEMENT'
5797 ,p_attribute1 => ''
5798 ,p_attribute2 => ''
5799 ,p_attribute3 => ''
5800 ,p_attribute4 => ''
5801 ,p_attribute5 => '');
5802 END IF;
5803 p_return_status := FND_API.G_RET_STS_ERROR;
5804 RAISE FND_API.G_EXC_ERROR;
5805 END IF;
5806
5807 -- Flex Field Validations
5808 pa_agreement_pvt.validate_flex_fields
5809 (p_desc_flex_name => p_desc_flex_name
5810 ,p_attribute_category => p_attribute_category
5811 ,p_attribute1 => p_attribute1
5812 ,p_attribute2 => p_attribute2
5813 ,p_attribute3 => p_attribute3
5814 ,p_attribute4 => p_attribute4
5815 ,p_attribute5 => p_attribute5
5816 /** ,p_attribute6 => p_attribute7 ** commented bug 2862922 **/
5817 ,p_attribute6 => p_attribute6 /** added bug 2862922 **/
5818 ,p_attribute7 => p_attribute7 /** added bug 2862922 **/
5819 ,p_attribute8 => p_attribute8
5820 ,p_attribute9 => p_attribute9
5821 ,p_attribute10 => p_attribute10
5822 ,p_return_msg => l_return_msg
5823 ,p_validate_status => l_validate_status
5824 );
5825 IF l_validate_status = 'N'
5826 THEN
5827 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5828 THEN
5829 pa_interface_utils_pub.map_new_amg_msg
5830 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
5831 ,p_msg_attribute => 'CHANGE'
5832 ,p_resize_flag => 'N'
5833 ,p_msg_context => 'FLEX'
5834 ,p_attribute1 => l_return_msg
5835 ,p_attribute2 => ''
5836 ,p_attribute3 => ''
5837 ,p_attribute4 => ''
5838 ,p_attribute5 => '');
5839 END IF;
5840 p_return_status := FND_API.G_RET_STS_ERROR;
5841 RAISE FND_API.G_EXC_ERROR;
5842 END IF;
5843
5844
5845 -- CALL THE CHECK UPDATE FUNDING OK PRIVATE PROCEDURE
5846 p_update_funding_ok_flag := pa_agreement_pvt.check_update_funding_ok
5847 (p_project_id => p_project_id
5848 ,p_task_id => p_task_id
5849 ,p_agreement_id => p_agreement_id
5850 ,p_customer_id => pa_agreement_utils.get_customer_id
5851 (p_funding_id => p_funding_id
5852 ,p_funding_reference => p_pm_funding_reference)
5853 ,p_pm_funding_reference => p_pm_funding_reference
5854 ,p_funding_id => p_funding_id
5855 ,p_funding_amt => p_allocated_amount
5856 /* MCB2 PARAMETERS BEGIN */
5857 ,p_project_rate_type => p_project_rate_type
5858 ,p_project_rate_date => p_project_rate_date
5859 ,p_project_exchange_rate => p_project_exchange_rate
5860 ,p_projfunc_rate_type => p_projfunc_rate_type
5861 ,p_projfunc_rate_date => p_projfunc_rate_date
5862 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate );
5863 /* MCB2 PARAMETERS END */
5864
5865 IF FND_API.to_boolean( p_commit )
5866 THEN
5867 COMMIT;
5868 END IF;
5869
5870 EXCEPTION
5871
5872 WHEN FND_API.G_EXC_ERROR
5873 THEN
5874 ROLLBACK TO check_update_funding_ok_pub;
5875
5876 p_return_status := FND_API.G_RET_STS_ERROR;
5877
5878
5879 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
5880 THEN
5881 ROLLBACK TO check_update_funding_ok_pub;
5882
5883 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5884
5885
5886 WHEN OTHERS
5887 THEN
5888 ROLLBACK TO check_update_funding_ok_pub;
5889
5890 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5891
5892 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5893 THEN
5894 FND_MSG_PUB.add_exc_msg
5895 ( p_pkg_name => G_PKG_NAME
5896 , p_procedure_name => l_api_name );
5897
5898 END IF;
5899
5900
5901 END check_update_funding_ok;
5902
5903
5904 PROCEDURE create_baseline_budget
5905 ( p_api_version_number IN NUMBER
5906 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5907 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5908 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
5909 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
5910 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
5911 ,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5912 ,p_pm_budget_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5913 -- ,p_budget_version_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5914 ,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5915 ,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5916 -- ,p_budget_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Always "AR"
5917 ,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5918 -- ,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5919 -- ,p_entry_method_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5920 -- ,p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5921 -- ,p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5922 ,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5923 ,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5924 ,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5925 ,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5926 ,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5927 ,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5928 ,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5929 ,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5930 ,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5931 ,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5932 ,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5933 ,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5934 ,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5935 ,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5936 ,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5937 ,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5938 ,p_create_new_curr_working_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR /* Added for bug#9892202 */
5939 ,p_replace_current_working_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR /* Added for bug#9892202 */
5940 )
5941 IS
5942 CURSOR res_info IS
5943 SELECT R1.resource_list_id resource_list_id,
5944 R1.name resource_list_name,
5945 M.resource_list_member_id resource_list_member_id
5946 FROM pa_resource_lists R1, pa_implementations I,
5947 pa_resource_list_members M
5948 WHERE R1.uncategorized_flag = 'Y'
5949 AND R1.business_group_id = I.business_group_id
5950 AND R1.resource_list_id = M.resource_list_id;
5951
5952
5953 CURSOR proj_dates IS
5954 SELECT start_date, completion_date,nvl(baseline_funding_flag,'N') baseline_funding_flag
5955 FROM pa_projects_all
5956 WHERE project_id = p_pa_project_id;
5957
5958 res_info_rec res_info%ROWTYPE;
5959 proj_dates_rec proj_dates%ROWTYPE;
5960
5961 l_project_id pa_projects_all.project_id%type;
5962 l_budget_version_id NUMBER;
5963 l_funding_level VARCHAR2(1);
5964 l_budget_entry_method_code VARCHAR2(30);
5965
5966 l_err_code NUMBER;
5967 l_resp_id NUMBER;
5968 l_err_stage VARCHAR2(120);
5969 l_status VARCHAR2(120);
5970 l_msg_count NUMBER;
5971 l_api_name VARCHAR2(50) := 'CREATE_BASELINE_BUDGET';
5972 l_err_stack VARCHAR2(250);
5973
5974 l_return_status VARCHAR2(1);
5975 l_workflow_started VARCHAR2(1);
5976
5977 -- CREATE DRAFT
5978
5979 CURSOR budget_version IS
5980 SELECT max(budget_version_id)
5981 FROM pa_budget_versions
5982 WHERE project_id = p_pa_project_id
5983 AND budget_type_code = 'AR'
5984 AND budget_status_code = 'W'
5985 AND version_number = 1;
5986
5987 -- CREATE FUNDING
5988 CURSOR funding_amount (p_resource_list_member_id number, p_start_date date, p_end_date date)
5989 is
5990 SELECT nvl(pf.task_id,0) pa_task_id,
5991 to_char(Null) pm_task_reference,
5992 to_char(Null) resource_alias,
5993 p_resource_list_member_id,
5994 NULL cost_code, --Added for Bug 16705902
5995 NULL cbs_element_id, --Added for Bug 16705902
5996 DECODE(nvl(pf.task_id,0),0,p_start_date,t.start_date) budget_start_date,
5997 DECODE(nvl(pf.task_id,0),0,p_end_date,t.completion_date) budget_end_date,
5998 to_char(null) period_name,
5999 'Default Created by Projects AMG Agreement Funding' description,
6000 to_number(null) raw_cost,
6001 to_number(null) burdened_cost,
6002 sum(nvl(pf.projfunc_allocated_amount,0)) revenue,
6003 to_number(null) quantity,
6004 p_pm_product_code,
6005 p_pm_budget_reference,
6006 p_Attribute_Category,
6007 p_Attribute1,
6008 p_Attribute2,
6009 p_Attribute3,
6010 p_Attribute4,
6011 p_Attribute5,
6012 p_Attribute6,
6013 p_Attribute7,
6014 p_Attribute8,
6015 p_Attribute9,
6016 p_Attribute10,
6017 p_Attribute11,
6018 p_Attribute12,
6019 p_Attribute13,
6020 p_Attribute14,
6021 p_Attribute15,
6022 /* Bug 2866699 Added due to Fin plan impact */
6023 pf.PROJFUNC_CURRENCY_CODE,
6024 to_char(NULL),
6025 to_char(NULL),
6026 to_char(NULL),
6027 to_char(NULL),
6028 to_char(NULL),
6029 to_char(NULL),
6030 to_char(NULL),
6031 to_char(NULL),
6032 to_char(NULL),
6033 to_char(NULL),
6034 to_char(NULL),
6035 to_char(NULL),
6036 to_char(NULL),
6037 to_char(NULL),
6038 to_char(NULL),
6039 to_char(NULL),
6040 to_char(NULL)
6041 /* Bug 2866699 Added due to Fin plan impact ends here */
6042 FROM pa_project_fundings pf, pa_tasks t
6043 WHERE pf.project_id = p_pa_project_id
6044 AND pf.task_id = t.task_id(+)
6045 AND pf.budget_type_code in ('BASELINE', 'DRAFT')
6046 group by nvl(pf.task_id,0),
6047 pf.projfunc_currency_code, /*projfunc_currency_code added for bug 3078560 */
6048 DECODE(nvl(pf.task_id,0),0,p_start_date,t.start_date),
6049 DECODE(nvl(pf.task_id,0),0,p_end_date,t.completion_date);
6050 /* Modified the cursor for bug 3488706*/
6051
6052 l_budget_lines_in_tbl pa_budget_pub.budget_line_in_tbl_type;
6053
6054 l_budget_lines_out_tbl pa_budget_pub.budget_line_out_tbl_type;
6055
6056 l_budget_lines_in_rec pa_budget_pub.budget_line_in_rec_type;
6057
6058 i number := 1;
6059
6060 P_BUDGET_VERSION_NAME varchar2(10);
6061 BEGIN
6062
6063 SAVEPOINT Create_Budget_From_funding;
6064 -- Initializing the return status to success ! -- bug 3099706
6065 -- Set API return status to success
6066 p_return_status := FND_API.G_RET_STS_SUCCESS;
6067
6068 -- Standard call to check for call compatibility.
6069 IF FND_API.TO_BOOLEAN( p_init_msg_list )
6070 THEN
6071
6072 FND_MSG_PUB.initialize;
6073
6074 END IF;
6075
6076
6077 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
6078 p_api_version_number ,
6079 l_api_name ,
6080 G_PKG_NAME )
6081 THEN
6082
6083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6084
6085 END IF;
6086 l_resp_id := FND_GLOBAL.Resp_id;
6087
6088 Pa_project_pvt.Convert_pm_projref_to_id (
6089 p_pm_project_reference => p_pm_project_reference,
6090 p_pa_project_id => p_pa_project_id,
6091 p_out_project_id => l_project_id,
6092 p_return_status => l_return_status );
6093
6094 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6095 THEN
6096 p_return_status := l_return_status;
6097 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6098
6099 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
6100 THEN
6101 p_return_status := l_return_status;
6102 RAISE FND_API.G_EXC_ERROR;
6103
6104 END IF;
6105
6106 IF (PA_FUNDING_CORE.CHECK_PROJECT_TYPE(l_project_id)) = 'N' THEN
6107 p_return_status := FND_API.G_RET_STS_ERROR;
6108
6109 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
6110 THEN
6111 pa_interface_utils_pub.map_new_amg_msg
6112 ( p_old_message_code => 'PA_INVALID_PROJECT'
6113 ,p_msg_attribute => 'CHANGE'
6114 ,p_resize_flag => 'N'
6115 ,p_msg_context => 'CONTRACT'
6116 ,p_attribute1 => l_project_id
6117 ,p_attribute2 => ''
6118 ,p_attribute3 => ''
6119 ,p_attribute4 => ''
6120 ,p_attribute5 => '');
6121 END IF;
6122 END IF;
6123
6124
6125 OPEN res_info;
6126 FETCH res_info INTO res_info_rec;
6127 CLOSE res_info;
6128
6129
6130 OPEN proj_dates;
6131 FETCH proj_dates INTO proj_dates_rec;
6132 CLOSE proj_dates;
6133
6134 IF (proj_dates_rec.completion_date is null or proj_dates_rec.start_date is null) then
6135 -- Set API return status to success
6136 P_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
6137
6138 pa_interface_utils_pub.map_new_amg_msg
6139 ( p_old_message_code => 'PA_BU_NO_PROJ_END_DATE'
6140 ,p_msg_attribute => 'CHANGE'
6141 ,p_resize_flag => 'N'
6142 ,p_msg_context => 'BUDG'
6143 ,p_attribute1 => l_project_id
6144 ,p_attribute2 => ''
6145 ,p_attribute3 => null
6146 ,p_attribute4 => ''
6147 ,p_attribute5 => '');
6148
6149 RAISE FND_API.G_EXC_ERROR;
6150 END IF;
6151
6152 IF proj_dates_rec.baseline_funding_flag = 'N' then
6153
6154
6155 P_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
6156
6157 pa_interface_utils_pub.map_new_amg_msg
6158 ( p_old_message_code => 'PA_NO_BASELINE_FUNDING'
6159 ,p_msg_attribute => 'CHANGE'
6160 ,p_resize_flag => 'N'
6161 ,p_msg_context => 'BUDG'
6162 ,p_attribute1 => l_project_id
6163 ,p_attribute2 => ''
6164 ,p_attribute3 => null
6165 ,p_attribute4 => ''
6166 ,p_attribute5 => '');
6167
6168 RAISE FND_API.G_EXC_ERROR;
6169
6170 END IF;
6171
6172
6173
6174 pa_billing_core.check_funding_level (
6175 x_project_id => l_project_id,
6176 x_funding_level => l_funding_level,
6177 x_err_code => l_err_code,
6178 x_err_stage => l_err_stage,
6179 x_err_stack => l_err_stack);
6180
6181 IF l_err_code <> 0 then
6182 pa_interface_utils_pub.map_new_amg_msg
6183 ( p_old_message_code => l_err_code
6184 ,p_msg_attribute => 'CHANGE'
6185 ,p_resize_flag => 'N'
6186 ,p_msg_context => 'BUDG'
6187 ,p_attribute1 => l_project_id
6188 ,p_attribute2 => ''
6189 ,p_attribute3 => l_funding_level
6190 ,p_attribute4 => ''
6191 ,p_attribute5 => '');
6192
6193 RAISE FND_API.G_EXC_ERROR;
6194
6195 END IF;
6196
6197
6198
6199 IF l_funding_level = 'P' then
6200 l_budget_entry_method_code := 'PA_PROJLVL_BASELINE';
6201 ELSIF l_funding_level = 'T' then
6202 l_budget_entry_method_code := 'PA_TASKLVL_BASELINE';
6203 END IF;
6204
6205
6206 OPen Funding_amount ( res_info_rec.resource_list_member_id,
6207 proj_dates_rec.start_date,
6208 proj_dates_rec.completion_date);
6209 loop
6210 fetch funding_amount into l_budget_lines_in_rec;
6211 if funding_amount%notfound then
6212 exit;
6213 end if;
6214 l_budget_lines_in_tbl(i) := l_budget_lines_in_rec;
6215 i := i + 1;
6216 end loop;
6217 close funding_amount;
6218
6219 -- Bug 3099706 : Set a variable to Y to indicate to pa_budget_pvt.validate_header_info
6220 -- API that the API is called from Agreement Pub during baselining of budget for an
6221 -- Autobaselined project. In this case, the called API needs to skip validations
6222 -- related to Autobaseline checking while creating a Draft Budget.
6223
6224 -- dbms_output.put_line('Before setting the value of PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB = '|| PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB);
6225 PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB := 'Y';
6226
6227 -- dbms_output.put_line('AFTER setting the value of PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB = '|| PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB);
6228 -- dbms_output.put_line('about to call create_draft_budget ... '||l_budget_entry_method_code||' '||l_project_id||' '||p_budget_version_name);
6229
6230 /* Commented for bug 4941046
6231 pa_budget_pub.create_draft_budget( p_api_version_number => p_api_version_number
6232 ,p_commit => FND_API.G_FALSE
6233 ,p_init_msg_list => FND_API.G_FALSE
6234 ,p_msg_count => p_msg_count
6235 ,p_msg_data => p_msg_data
6236 ,p_return_status => l_return_status
6237 ,p_pm_product_code => p_pm_product_code
6238 ,p_budget_version_name => p_budget_version_name
6239 ,p_pa_project_id => l_project_id
6240 ,p_pm_project_reference => p_pm_project_reference
6241 ,p_budget_type_code => 'AR'
6242 ,p_change_reason_code => Null
6243 ,p_description => 'Default Created by Projects AMG Agreement Funding'
6244 ,p_entry_method_code => l_budget_entry_method_code
6245 ,p_resource_list_name => res_info_rec.resource_list_name
6246 ,p_resource_list_id => res_info_rec.resource_list_id
6247 ,p_attribute_category => p_attribute_category
6248 ,p_attribute1 => p_attribute1
6249 ,p_attribute2 => p_attribute2
6250 ,p_attribute3 => p_attribute3
6251 ,p_attribute4 => p_attribute4
6252 ,p_attribute5 => p_attribute5
6253 ,p_attribute6 => p_attribute6
6254 ,p_attribute7 => p_attribute7
6255 ,p_attribute8 => p_attribute8
6256 ,p_attribute9 => p_attribute9
6257 ,p_attribute10 => p_attribute10
6258 ,p_attribute11 => p_attribute11
6259 ,p_attribute12 => p_attribute12
6260 ,p_attribute13 => p_attribute13
6261 ,p_attribute14 => p_attribute14
6262 ,p_attribute15 => p_attribute15
6263 ,p_budget_lines_in => l_budget_lines_in_tbl
6264 ,p_budget_lines_out => l_budget_lines_out_tbl);*/
6265
6266 --WRAPPER API CALL ADDED FOR BUG 4941046
6267 pa_fin_plan_utils.create_draft_budget_wrp( p_api_version_number => p_api_version_number
6268 ,p_commit => FND_API.G_FALSE
6269 ,p_init_msg_list => FND_API.G_FALSE
6270 ,p_msg_count => p_msg_count
6271 ,p_msg_data => p_msg_data
6272 ,p_return_status => l_return_status
6273 ,p_pm_product_code => p_pm_product_code
6274 ,p_budget_version_name => p_pm_budget_reference --Added for bug 4941046
6275 ,p_pa_project_id => l_project_id
6276 ,p_pm_project_reference => p_pm_project_reference
6277 ,p_budget_type_code => 'AR'
6278 ,p_change_reason_code => Null
6279 ,p_description => 'Default Created by Projects AMG Agreement Funding'
6280 ,p_entry_method_code => l_budget_entry_method_code
6281 ,p_resource_list_name => res_info_rec.resource_list_name
6282 ,p_resource_list_id => res_info_rec.resource_list_id
6283 ,p_attribute_category => p_attribute_category
6284 ,p_attribute1 => p_attribute1
6285 ,p_attribute2 => p_attribute2
6286 ,p_attribute3 => p_attribute3
6287 ,p_attribute4 => p_attribute4
6288 ,p_attribute5 => p_attribute5
6289 ,p_attribute6 => p_attribute6
6290 ,p_attribute7 => p_attribute7
6291 ,p_attribute8 => p_attribute8
6292 ,p_attribute9 => p_attribute9
6293 ,p_attribute10 => p_attribute10
6294 ,p_attribute11 => p_attribute11
6295 ,p_attribute12 => p_attribute12
6296 ,p_attribute13 => p_attribute13
6297 ,p_attribute14 => p_attribute14
6298 ,p_attribute15 => p_attribute15
6299 ,p_budget_lines_in => l_budget_lines_in_tbl
6300 ,p_budget_lines_out => l_budget_lines_out_tbl
6301 ,p_create_new_curr_working_flag => p_create_new_curr_working_flag /* Added for bug#9892202 */
6302 ,p_replace_current_working_flag => p_replace_current_working_flag); /* Added for bug#9892202 */
6303
6304 -- dbms_output.put_line('returned from create_draft ... status = '||l_return_status);
6305 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6306 THEN
6307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6308
6309 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
6310 THEN
6311 RAISE FND_API.G_EXC_ERROR;
6312 END IF;
6313
6314 -- dbms_output.put_line('about to call baseline_budget ... ');
6315 -- dbms_output.put_line('Before setting the value of PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB = '|| PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB);
6316
6317 /* Commented for bug 4941046
6318 PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB := 'Y';
6319
6320 PA_BUDGET_PUB.BASELINE_BUDGET
6321 ( p_api_version_number => p_api_version_number
6322 ,p_commit => FND_API.G_FALSE
6323 ,p_init_msg_list => FND_API.G_FALSE
6324 ,p_msg_count => p_msg_count
6325 ,p_msg_data => p_msg_data
6326 ,p_return_status => l_return_status
6327 ,p_workflow_started => l_workflow_started
6328 ,p_pm_product_code => p_pm_product_code
6329 ,p_pa_project_id => l_project_id
6330 ,p_pm_project_reference => p_pm_project_reference
6331 ,p_budget_type_code => 'AR'
6332 ,p_mark_as_original => 'Y'
6333 );
6334 -- dbms_output.put_line('returned from BASELINE_BUDGET ... status = '||l_return_status);
6335
6336 IF (nvl(PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB,'N') = 'Y') THEN
6337 PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB := 'N'; -- reset the value bug 3099706
6338 END IF;
6339
6340 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6341 THEN
6342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6343
6344 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
6345 THEN
6346 RAISE FND_API.G_EXC_ERROR;
6347 END IF; End of comment for bug 4941046 */
6348
6349
6350 IF FND_API.to_boolean( p_commit )
6351 THEN
6352 COMMIT;
6353 END IF;
6354
6355 EXCEPTION
6356 WHEN FND_API.G_EXC_ERROR
6357 THEN
6358 ROLLBACK TO Create_Budget_From_funding;
6359 p_return_status := FND_API.G_RET_STS_ERROR;
6360 FND_MSG_PUB.Count_And_Get
6361 ( p_count => p_msg_count ,
6362 p_data => p_msg_data );
6363 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
6364 THEN
6365 ROLLBACK TO Create_Budget_From_funding;
6366 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6367 FND_MSG_PUB.Count_And_Get
6368 ( p_count => p_msg_count ,
6369 p_data => p_msg_data );
6370
6371 WHEN OTHERS
6372 THEN
6373 ROLLBACK TO Create_Budget_From_funding;
6374 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6375 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6376 THEN
6377 FND_MSG_PUB.add_exc_msg
6378 ( p_pkg_name => G_PKG_NAME
6379 , p_procedure_name => l_api_name );
6380
6381 END IF;
6382 FND_MSG_PUB.Count_And_Get
6383 ( p_count => p_msg_count ,
6384 p_data => p_msg_data );
6385
6386
6387 END create_baseline_budget;
6388
6389
6390 end PA_AGREEMENT_PUB;