[Home] [Help]
PACKAGE BODY: APPS.PA_AGREEMENT_PUB
Source
1 package body PA_AGREEMENT_PUB as
2 /*$Header: PAAFAPBB.pls 120.11.12010000.2 2008/12/14 12:18:32 arbandyo 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 BEGIN
2029 -- Standard begin of API savepoint
2030
2031 SAVEPOINT add_funding_pub;
2032
2033
2034 -- Standard call to check for call compatibility.
2035
2036
2037 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
2038 p_api_version_number ,
2039 l_api_name ,
2040 G_PKG_NAME )
2041 THEN
2042
2043 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2044
2045 END IF;
2046 l_resp_id := FND_GLOBAL.Resp_id;
2047
2048 -- Actions performed using the APIs would be subject to
2049 -- function security. If the responsibility does not allow
2050 -- such functions to be executed, the API should not proceed further
2051 -- since the user does not have access to such functions
2052
2053 -- This call is added for patchset K project role based security check
2054
2055 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := p_pa_project_id;
2056
2057
2058 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
2059 (p_api_version_number => p_api_version_number,
2060 p_responsibility_id => l_resp_id,
2061 p_function_name => 'PA_AF_ADD_FUNDING',
2062 p_msg_count => l_msg_count,
2063 p_msg_data => l_msg_data,
2064 p_return_status => l_return_status,
2065 p_function_allowed => l_function_allowed
2066 );
2067
2068 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2069 THEN
2070 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2071
2072 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2073 THEN
2074 RAISE FND_API.G_EXC_ERROR;
2075 END IF;
2076 IF l_function_allowed = 'N' THEN
2077 pa_interface_utils_pub.map_new_amg_msg
2078 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
2079 ,p_msg_attribute => 'CHANGE'
2080 ,p_resize_flag => 'Y'
2081 ,p_msg_context => 'GENERAL'
2082 ,p_attribute1 => ''
2083 ,p_attribute2 => ''
2084 ,p_attribute3 => ''
2085 ,p_attribute4 => ''
2086 ,p_attribute5 => '');
2087 p_return_status := FND_API.G_RET_STS_ERROR;
2088 RAISE FND_API.G_EXC_ERROR;
2089 END IF;
2090
2091 -- Initialize the message table if requested.
2092
2093 IF FND_API.TO_BOOLEAN( p_init_msg_list )
2094 THEN
2095
2096 FND_MSG_PUB.initialize;
2097
2098 END IF;
2099
2100 -- Set API return status to success
2101
2102 p_return_status := FND_API.G_RET_STS_SUCCESS;
2103
2104 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
2105
2106
2107 -- Product Code
2108 IF (p_pm_product_code IS NULL)
2109 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2110 THEN
2111 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2112 THEN
2113 pa_interface_utils_pub.map_new_amg_msg
2114 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISS'
2115 ,p_msg_attribute => 'CHANGE'
2116 ,p_resize_flag => 'N'
2117 ,p_msg_context => 'GENERAL'
2118 ,p_attribute1 => ''
2119 ,p_attribute2 => ''
2120 ,p_attribute3 => ''
2121 ,p_attribute4 => ''
2122 ,p_attribute5 => '');
2123 END IF;
2124 p_return_status := FND_API.G_RET_STS_ERROR;
2125 RAISE FND_API.G_EXC_ERROR;
2126 END IF;
2127
2128 /* NOT REQUIRED
2129 -- Agreement Reference
2130 IF (p_pm_agreement_reference IS NULL)
2131 OR (p_pm_agreement_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2132 THEN
2133 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2134 THEN
2135 pa_interface_utils_pub.map_new_amg_msg
2136 ( p_old_message_code => 'PA_AGMT_REF_IS_MISS'
2137 ,p_msg_attribute => 'CHANGE'
2138 ,p_resize_flag => 'N'
2139 ,p_msg_context => 'GENERAL'
2140 ,p_attribute1 => ''
2141 ,p_attribute2 => ''
2142 ,p_attribute3 => ''
2143 ,p_attribute4 => ''
2144 ,p_attribute5 => '');
2145 END IF;
2146 p_return_status := FND_API.G_RET_STS_ERROR;
2147 RAISE FND_API.G_EXC_ERROR;
2148 END IF;
2149
2150
2151 -- Project Reference
2152 IF (p_pm_project_reference IS NULL)
2153 OR (p_pm_project_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2154 THEN
2155 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2156 THEN
2157 pa_interface_utils_pub.map_new_amg_msg
2158 ( p_old_message_code => 'PA_PROJ_REF_IS_MISS'
2159 ,p_msg_attribute => 'CHANGE'
2160 ,p_resize_flag => 'N'
2161 ,p_msg_context => 'GENERAL'
2162 ,p_attribute1 => ''
2163 ,p_attribute2 => ''
2164 ,p_attribute3 => ''
2165 ,p_attribute4 => ''
2166 ,p_attribute5 => '');
2167 END IF;
2168 p_return_status := FND_API.G_RET_STS_ERROR;
2169 RAISE FND_API.G_EXC_ERROR;
2170 END IF;
2171 */
2172
2173 -- Code added for Bug 2403652 starts here
2174
2175 -- Funding category
2176 /* Commented for 2483081 */
2177 /* IF (p_funding_category IS NULL)
2178 OR (p_funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2179 THEN
2180 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2181 THEN
2182 pa_interface_utils_pub.map_new_amg_msg
2183 ( p_old_message_code => 'PA_FUND_CAT_IS_MISS'
2184 ,p_msg_attribute => 'CHANGE'
2185 ,p_resize_flag => 'N'
2186 ,p_msg_context => 'GENERAL'
2187 ,p_attribute1 => ''
2188 ,p_attribute2 => ''
2189 ,p_attribute3 => ''
2190 ,p_attribute4 => ''
2191 ,p_attribute5 => '');
2192 END IF;
2193 p_return_status := FND_API.G_RET_STS_ERROR;
2194 RAISE FND_API.G_EXC_ERROR;
2195 END IF;
2196 */
2197
2198 -- Validate funding category
2199 /* Added IF condition for bug 2838872 */
2200 IF (p_funding_category IS NULL)
2201 OR (p_funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2202 THEN
2203 l_funding_category := 'ADDITIONAL';
2204 ELSE
2205 l_funding_category := p_funding_category;
2206 END IF;
2207
2208 IF pa_agreement_pvt.check_funding_category
2209 (p_pa_project_id
2210 ,p_pa_task_id
2211 ,p_agreement_id
2212 ,p_pm_funding_reference
2213 ,l_funding_category ) = 'N'
2214 THEN
2215 p_return_status := FND_API.G_RET_STS_ERROR;
2216 RAISE FND_API.G_EXC_ERROR;
2217 END IF;
2218
2219 -- Code added for Bug 2403652 ends here
2220
2221
2222 -- Funding Reference
2223 IF (p_pm_funding_reference IS NULL)
2224 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2225 THEN
2226 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2227 THEN
2228 pa_interface_utils_pub.map_new_amg_msg
2229 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
2230 ,p_msg_attribute => 'CHANGE'
2231 ,p_resize_flag => 'N'
2232 ,p_msg_context => 'GENERAL'
2233 ,p_attribute1 => ''
2234 ,p_attribute2 => ''
2235 ,p_attribute3 => ''
2236 ,p_attribute4 => ''
2237 ,p_attribute5 => '');
2238 END IF;
2239 p_return_status := FND_API.G_RET_STS_ERROR;
2240 RAISE FND_API.G_EXC_ERROR;
2241 END IF;
2242
2243 -- Project Id
2244 IF (p_pa_project_id IS NULL)
2245 OR (p_pa_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2246 THEN
2247 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2248 THEN
2249 pa_interface_utils_pub.map_new_amg_msg
2250 ( p_old_message_code => 'PA_PROJ_ID_IS_MISS'
2251 ,p_msg_attribute => 'CHANGE'
2252 ,p_resize_flag => 'N'
2253 ,p_msg_context => 'FUNDING'
2254 ,p_attribute1 => ''
2255 ,p_attribute2 => p_pm_funding_reference
2256 ,p_attribute3 => ''
2257 ,p_attribute4 => ''
2258 ,p_attribute5 => '');
2259 END IF;
2260 p_return_status := FND_API.G_RET_STS_ERROR;
2261 RAISE FND_API.G_EXC_ERROR;
2262 END IF;
2263
2264 -- Task Id
2265 /*
2266 IF (p_pa_task_id IS NULL)
2267 OR (p_pa_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2268 THEN
2269 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2270 THEN
2271 pa_interface_utils_pub.map_new_amg_msg
2272 ( p_old_message_code => 'PA_TASK_ID_IS_MISS'
2273 ,p_msg_attribute => 'CHANGE'
2274 ,p_resize_flag => 'N'
2275 ,p_msg_context => 'FUNDING'
2276 ,p_attribute1 => ''
2277 ,p_attribute2 => p_pm_funding_reference
2278 ,p_attribute3 => ''
2279 ,p_attribute4 => ''
2280 ,p_attribute5 => '');
2281 END IF;
2282 p_return_status := FND_API.G_RET_STS_ERROR;
2283 RAISE FND_API.G_EXC_ERROR;
2284 END IF;
2285 */
2286 -- Date Allocated
2287 IF (p_date_allocated IS NULL)
2288 OR (p_date_allocated = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
2289 THEN
2290 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2291 THEN
2292 pa_interface_utils_pub.map_new_amg_msg
2293 ( p_old_message_code => 'PA_DATE_ALLOC_IS_MISS'
2294 ,p_msg_attribute => 'CHANGE'
2295 ,p_resize_flag => 'N'
2296 ,p_msg_context => 'FUNDING'
2297 ,p_attribute1 => ''
2298 ,p_attribute2 => p_pm_funding_reference
2299 ,p_attribute3 => ''
2300 ,p_attribute4 => ''
2301 ,p_attribute5 => '');
2302 END IF;
2303 p_return_status := FND_API.G_RET_STS_ERROR;
2304 RAISE FND_API.G_EXC_ERROR;
2305 END IF;
2306
2307 -- Allocated Amount
2308 IF (p_allocated_amount IS NULL)
2309 OR (p_allocated_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2310 THEN
2311 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2312 THEN
2313 pa_interface_utils_pub.map_new_amg_msg
2314 ( p_old_message_code => 'PA_ALLOC_AMT_IS_MISS'
2315 ,p_msg_attribute => 'CHANGE'
2316 ,p_resize_flag => 'N'
2317 ,p_msg_context => 'FUNDING'
2318 ,p_attribute1 => ''
2319 ,p_attribute2 => p_pm_funding_reference
2320 ,p_attribute3 => ''
2321 ,p_attribute4 => ''
2322 ,p_attribute5 => '');
2323 END IF;
2324 p_return_status := FND_API.G_RET_STS_ERROR;
2325 RAISE FND_API.G_EXC_ERROR;
2326 -- ELSE
2327 -- select amount into l_nik from pa_agreements_all where agreement_id = p_agreement_id;
2328 END IF;
2329
2330 -- Agreement Id
2331 IF (p_agreement_id IS NULL)
2332 OR (p_agreement_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2333 THEN
2334 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2335 THEN
2336 pa_interface_utils_pub.map_new_amg_msg
2337 ( p_old_message_code => 'PA_AGMT_ID_IS_MISS'
2338 ,p_msg_attribute => 'CHANGE'
2339 ,p_resize_flag => 'N'
2340 ,p_msg_context => 'FUNDING'
2341 ,p_attribute1 => ''
2342 ,p_attribute2 => p_pm_funding_reference
2343 ,p_attribute3 => ''
2344 ,p_attribute4 => ''
2345 ,p_attribute5 => '');
2346 END IF;
2347 p_return_status := FND_API.G_RET_STS_ERROR;
2348 RAISE FND_API.G_EXC_ERROR;
2349 END IF;
2350
2351
2352 -- VALIDATE THE INCOMING PARAMETERS
2353 SELECT a. customer_id
2354 INTO l_customer_id
2355 FROM pa_agreements_all a
2356 WHERE a.agreement_id = p_agreement_id;
2357 IF pa_agreement_pvt.check_add_funding_ok
2358 (p_project_id => p_pa_project_id
2359 ,p_task_id => p_pa_task_id
2360 ,p_agreement_id => p_agreement_id
2361 ,p_pm_funding_reference => p_pm_funding_reference
2362 ,p_funding_amt => p_allocated_amount
2363 ,p_customer_id => l_customer_id
2364 /* MCB2 PARAMETERS BEGIN */
2365 ,p_project_rate_type => p_project_rate_type
2366 ,p_project_rate_date => p_project_rate_date
2367 ,p_project_exchange_rate => p_project_exchange_rate
2368 ,p_projfunc_rate_type => p_projfunc_rate_type
2369 ,p_projfunc_rate_date => p_projfunc_rate_date
2370 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate ) = 'N'
2371 /* MCB2 PARAMETERS END */
2372
2373 THEN
2374 p_return_status := FND_API.G_RET_STS_ERROR;
2375 RAISE FND_API.G_EXC_ERROR;
2376 END IF;
2377
2378
2379 -- Flex Field Validations
2380 IF (p_desc_flex_name IS NOT NULL)
2381 AND (p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2382 -- OR (p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2383 THEN
2384 pa_agreement_pvt.validate_flex_fields
2385 (p_desc_flex_name => p_desc_flex_name
2386 ,p_attribute_category => p_attribute_category
2387 ,p_attribute1 => p_attribute1
2388 ,p_attribute2 => p_attribute2
2389 ,p_attribute3 => p_attribute3
2390 ,p_attribute4 => p_attribute4
2391 ,p_attribute5 => p_attribute5
2392 /** ,p_attribute6 => p_attribute7 ** commented bug 2862922 **/
2393 ,p_attribute6 => p_attribute6 /** added bug 2862922 **/
2394 ,p_attribute7 => p_attribute7 /** added bug 2862922 **/
2395 ,p_attribute8 => p_attribute8
2396 ,p_attribute9 => p_attribute9
2397 ,p_attribute10 => p_attribute10
2398 ,p_return_msg => l_return_msg
2399 ,p_validate_status => l_validate_status
2400 );
2401 IF l_validate_status = 'N'
2402 THEN
2403 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2404 THEN
2405 pa_interface_utils_pub.map_new_amg_msg
2406 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
2407 ,p_msg_attribute => 'CHANGE'
2408 ,p_resize_flag => 'N'
2409 ,p_msg_context => 'FLEX'
2410 ,p_attribute1 => l_return_msg
2411 ,p_attribute2 => ''
2412 ,p_attribute3 => ''
2413 ,p_attribute4 => ''
2414 ,p_attribute5 => '');
2415 END IF;
2416 p_return_status := FND_API.G_RET_STS_ERROR;
2417 RAISE FND_API.G_EXC_ERROR;
2418 END IF;
2419 END IF;
2420
2421 /* NOT REQUIRED
2422 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
2423 pa_agreement_pvt.convert_ag_ref_to_id
2424 (p_pm_agreement_reference => p_pm_agreement_reference
2425 ,p_af_agreement_id => p_agreement_id
2426 ,p_out_agreement_id => l_out_agreement_id
2427 ,p_return_status => l_return_status);
2428 p_return_status := l_return_status;
2429
2430
2431 -- CONVERT PROJECT REFERENCE TO PROJECT ID
2432 pa_project_pvt.convert_pm_projref_to_id
2433 (p_pm_project_reference => p_pm_project_reference
2434 ,p_pa_project_id => p_project_id
2435 ,p_out_project_id => l_out_project_id
2436 ,p_return_status => l_return_status);
2437 p_return_status := l_return_status;
2438
2439 */
2440 /* NOT REQUIRED
2441 -- CONVERT FUNDING REFERENCE TO FUNDING ID
2442 pa_agreement_pvt.convert_fu_ref_to_id
2443 (p_pm_funding_reference => p_pm_funding_reference
2444 ,p_af_funding_id => p_funding_id
2445 ,p_out_funding_id => l_out_funding_id
2446 ,p_return_status => l_return_status);
2447 p_return_status := l_return_status;
2448 */
2449 --HERE
2450 --TO BE CORRECTED - BUDGET_TYPE_CODE - NIKHIL
2451 IF p_pa_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_pa_task_id IS NOT NULL THEN
2452 l_pa_task_id := p_pa_task_id;
2453 ELSIF p_pa_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM then
2454 l_pa_task_id := NULL;
2455 END IF;
2456
2457
2458 /* 2315767 Added Else condition for all the Flex Field Attributes
2459 so that proper value is passed if corresponding p_attribute's are not NULL
2460 or not the default PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR */
2461 IF p_desc_flex_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2462 l_desc_flex_name:= NULL;
2463 ELSE
2464 l_desc_flex_name := p_desc_flex_name;
2465 END IF;
2466 IF p_attribute_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2467 l_attribute_category := NULL;
2468 ELSE
2469 l_attribute_category := p_attribute_category;
2470 END IF;
2471 IF p_attribute1 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2472 l_attribute1 := NULL;
2473 ELSE
2474 l_attribute1 := p_attribute1;
2475 END IF;
2476 IF p_attribute2 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2477 l_attribute2 := NULL;
2478 ELSE
2479 l_attribute2 := p_attribute2;
2480 END IF;
2481 IF p_attribute3 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2482 l_attribute3 := NULL;
2483 ELSE
2484 l_attribute3 := p_attribute3;/* 2315767 Modified l_attribute4 to l_attribute3 */
2485 END IF;
2486 /* 2315767 Added code for attribute4 of flexfield */
2487 IF p_attribute4 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2488 l_attribute4 := NULL;
2489 ELSE
2490 l_attribute4 := p_attribute4;
2491 END IF;
2492 IF p_attribute5 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2493 l_attribute5 := NULL;
2494 ELSE
2495 l_attribute5 := p_attribute5;
2496 END IF;
2497 IF p_attribute6 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2498 l_attribute6 := NULL;
2499 ELSE
2500 l_attribute6 := p_attribute6;
2501 END IF;
2502 IF p_attribute7 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2503 l_attribute7 := NULL;
2504 ELSE
2505 l_attribute7 := p_attribute7;
2506 END IF;
2507 IF p_attribute8 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2508 l_attribute8 := NULL;
2509 ELSE
2510 l_attribute8 := p_attribute8;
2511 END IF;
2512 IF p_attribute9 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2513 l_attribute9 := NULL;
2514 ELSE
2515 l_attribute9 := p_attribute9;
2516 END IF;
2517 IF p_attribute10 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2518 l_attribute10 := NULL;
2519 ELSE
2520 l_attribute10 := p_attribute10;
2521 END IF;
2522
2523 /* Changes for bug 5554070-Start */
2524
2525 IF p_project_rate_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2526 l_project_rate_type := null;
2527 else
2528 l_project_rate_type :=p_project_rate_type;
2529 end if;
2530
2531 If p_projfunc_rate_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2532 l_projfunc_rate_type :=null;
2533 else
2534 l_projfunc_rate_type := p_projfunc_rate_type;
2535 end if;
2536
2537 If p_projfunc_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
2538 l_projfunc_rate_date :=null;
2539 else
2540 l_projfunc_rate_date := p_projfunc_rate_date;
2541 end if;
2542
2543 If p_project_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
2544 l_project_rate_date :=null;
2545 else
2546 l_project_rate_date := p_project_rate_date;
2547 end if;
2548
2549 /* Changes for bug 5554070-End */
2550
2551
2552
2553 -- ADD FUNDING
2554 pa_agreement_utils.create_funding(
2555 p_Rowid => l_row_id,
2556 p_Project_Funding_Id => p_funding_id,
2557 p_Last_Update_Date => SYSDATE,
2558 p_Last_Updated_By => G_USER_ID,
2559 p_Creation_Date => SYSDATE,
2560 p_Created_By => G_USER_ID,
2561 p_Last_Update_Login => G_LOGIN_ID,
2562 p_Agreement_Id => p_agreement_id,
2563 p_Project_Id => p_pa_project_id,
2564 p_Task_Id => l_pa_task_id,
2565 p_Allocated_Amount => p_allocated_amount,
2566 p_Date_Allocated => p_date_allocated,
2567 p_Attribute_Category => l_attribute_category,
2568 p_Attribute1 => l_attribute1,
2569 p_Attribute2 => l_attribute2,
2570 p_Attribute3 => l_attribute3,
2571 p_Attribute4 => l_attribute4,
2572 p_Attribute5 => l_attribute5,
2573 p_Attribute6 => l_attribute6,
2574 p_Attribute7 => l_attribute7,
2575 p_Attribute8 => l_attribute8,
2576 p_Attribute9 => l_attribute9,
2577 p_Attribute10 => l_attribute10,
2578 p_pm_funding_reference => p_pm_funding_reference,
2579 p_pm_product_code => p_pm_product_code,
2580 /* MCB2 PARAMETERS BEGIN 5554070 Chaged to new introduced variable */
2581 p_project_rate_type => l_project_rate_type,
2582 p_project_rate_date => l_project_rate_date,
2583 p_project_exchange_rate => p_project_exchange_rate,
2584 p_projfunc_rate_type => l_projfunc_rate_type,
2585 p_projfunc_rate_date => l_projfunc_rate_date,
2586 p_projfunc_exchange_rate => p_projfunc_exchange_rate,
2587 x_err_code => l_err_code,
2588 x_err_msg => l_err_msg,
2589 p_funding_category => l_funding_category /* For Bug 2244796 */
2590 );
2591
2592 /* MCB2 PARAMETERS END */
2593
2594
2595 IF l_err_code <> 0 THEN
2596
2597 /* Following code added for bug 2902096 .The l_resize_flag is set to 'Y' only for some
2598 error messages whose length of message_name will go above 30 if '_AMG' is added to it.
2599 These messages can be used straightaway without using the '_AMG' as the message text is same .*/
2600 /* Added some more error messages in the IF condition for bug 2967759 */
2601 l_resize_flag:='N';
2602 IF (l_err_msg in ('PA_USR_RATE_NOT_ALLOWED_FC_PC','PA_USR_RATE_NOT_ALLOWED_FC_PF',
2603 'PA_USR_RATE_NOT_ALLOWED_BC_PF','PA_NO_EXCH_RATE_EXISTS_BC_PF',
2604 'PA_NO_EXCH_RATE_EXISTS_FC_PC','PA_NO_EXCH_RATE_EXISTS_FC_PF')) THEN
2605 l_resize_flag:='Y';
2606 END IF;
2607
2608 /* End - Bug 2902096 */
2609
2610 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2611 THEN
2612 pa_interface_utils_pub.map_new_amg_msg
2613 ( p_old_message_code => l_err_msg
2614 ,p_msg_attribute => 'CHANGE'
2615 /* ,p_resize_flag => 'N' changed for bug 2902096 */
2616 ,p_resize_flag => l_resize_flag
2617 ,p_msg_context => 'FLEX'
2618 ,p_attribute1 => l_return_msg
2619 ,p_attribute2 => ''
2620 ,p_attribute3 => ''
2621 ,p_attribute4 => ''
2622 ,p_attribute5 => '');
2623 END IF;
2624 p_return_status := FND_API.G_RET_STS_ERROR;
2625 RAISE FND_API.G_EXC_ERROR;
2626
2627 ELSE
2628
2629 -- UPDATE SUMMARY PROJECT FUNDINGS
2630 pa_agreement_utils.summary_funding_insert_row
2631 (p_agreement_id => p_agreement_id
2632 ,p_project_id => p_pa_project_id
2633 ,p_task_id => p_pa_task_id
2634 ,p_login_id => G_LOGIN_ID
2635 ,p_user_id => G_USER_ID);
2636
2637 IF FND_API.to_boolean( p_commit ) THEN
2638 COMMIT;
2639 END IF;
2640
2641 END IF;
2642
2643 EXCEPTION
2644 WHEN FND_API.G_EXC_ERROR
2645 THEN
2646 ROLLBACK TO add_funding_pub;
2647 p_return_status := FND_API.G_RET_STS_ERROR;
2648 FND_MSG_PUB.Count_And_Get
2649 ( p_count => p_msg_count ,
2650 p_data => p_msg_data );
2651
2652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2653 THEN
2654 ROLLBACK TO add_funding_pub;
2655 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2656 FND_MSG_PUB.Count_And_Get
2657 ( p_count => p_msg_count ,
2658 p_data => p_msg_data );
2659
2660 WHEN OTHERS
2661 THEN
2662 ROLLBACK TO add_funding_pub;
2663 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2664 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2665 THEN
2666 FND_MSG_PUB.add_exc_msg
2667 ( p_pkg_name => G_PKG_NAME
2668 , p_procedure_name => l_api_name );
2669
2670 END IF;
2671 FND_MSG_PUB.Count_And_Get
2672 ( p_count => p_msg_count ,
2673 p_data => p_msg_data );
2674
2675 END add_funding;
2676
2677 -- ============================================================================
2678 --
2679 --Name: delete_funding
2680 --Type: Procedure
2681 --Description: This procedure can be used to delete funding for an existing for an
2682 -- agreement.
2683 --
2684 --Called subprograms:
2685 -- pa_interface_utils_pub.map_new_amg_msg
2686 -- pa_agreement_pub.check_delete_funding_ok
2687 -- pa_agreement_pvt.convert_fu_ref_to_id
2688 -- pa_agreement_utils.delete_agreement
2689 --
2690 --
2691 --
2692 --History:
2693 -- 25-MAR-2000 Rakesh Raghavan Created.
2694 -- ---------------------------------------------------------------------------
2695 PROCEDURE delete_funding
2696 (p_api_version_number IN NUMBER
2697 ,p_commit IN VARCHAR2
2698 ,p_init_msg_list IN VARCHAR2
2699 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
2700 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
2701 ,p_return_status OUT NOCOPY VARCHAR2/*File.sql.39*/
2702 ,p_pm_product_code IN VARCHAR2
2703 ,p_pm_funding_reference IN VARCHAR2
2704 ,p_funding_id IN NUMBER
2705 ,p_check_y_n IN VARCHAR2
2706 )
2707 IS
2708 -- Local variables
2709 l_msg_count NUMBER ;
2710 l_msg_data VARCHAR2(2000);
2711 l_function_allowed VARCHAR2(1);
2712 l_resp_id NUMBER := 0;
2713 l_funding_id NUMBER;
2714 l_out_funding_id NUMBER;
2715 l_agreement_id NUMBER;
2716 l_project_id NUMBER;
2717 l_task_id NUMBER;
2718 l_out_agreement_id NUMBER ;
2719 l_return_status VARCHAR2(1);
2720 l_api_name CONSTANT VARCHAR2(30):= 'delete_funding';
2721
2722 /* adding variables for bug 2868818*/
2723
2724 CURSOR c1
2725 IS
2726 SELECT f.funding_category
2727 FROM PA_PROJECT_FUNDINGS f
2728 WHERE f.pm_funding_reference = p_pm_funding_reference;
2729
2730 l_fund_rec1 c1%ROWTYPE;
2731 l_funding_category VARCHAR2(30);
2732
2733 /* end adding variables for bug 2868818*/
2734
2735 BEGIN
2736 -- Standard begin of API savepoint
2737
2738 SAVEPOINT delete_funding_pub;
2739
2740
2741 -- Standard call to check for call compatibility.
2742
2743
2744 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
2745 p_api_version_number ,
2746 l_api_name ,
2747 G_PKG_NAME )
2748 THEN
2749
2750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2751
2752 END IF;
2753 l_resp_id := FND_GLOBAL.Resp_id;
2754
2755
2756 -- This call is added for patchset K project role based security check
2757
2758 -- Get the project id from this project funding line
2759 l_project_id := pa_agreement_utils.get_project_id(p_funding_id => p_funding_id,
2760 p_funding_reference => p_pm_funding_reference);
2761
2762 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := l_project_id;
2763
2764 -- End of the security changes
2765
2766
2767 -- Actions performed using the APIs would be subject to
2768 -- function security. If the responsibility does not allow
2769 -- such functions to be executed, the API should not proceed further
2770 -- since the user does not have access to such functions
2771
2772
2773 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
2774 (p_api_version_number => p_api_version_number,
2775 p_responsibility_id => l_resp_id,
2776 p_function_name => 'PA_AF_DELETE_FUNDING',
2777 p_msg_count => l_msg_count,
2778 p_msg_data => l_msg_data,
2779 p_return_status => l_return_status,
2780 p_function_allowed => l_function_allowed
2781 );
2782
2783 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2784 THEN
2785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2786
2787 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2788 THEN
2789 RAISE FND_API.G_EXC_ERROR;
2790 END IF;
2791 IF l_function_allowed = 'N' THEN
2792 pa_interface_utils_pub.map_new_amg_msg
2793 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
2794 ,p_msg_attribute => 'CHANGE'
2795 ,p_resize_flag => 'Y'
2796 ,p_msg_context => 'GENERAL'
2797 ,p_attribute1 => ''
2798 ,p_attribute2 => ''
2799 ,p_attribute3 => ''
2800 ,p_attribute4 => ''
2801 ,p_attribute5 => '');
2802 p_return_status := FND_API.G_RET_STS_ERROR;
2803 RAISE FND_API.G_EXC_ERROR;
2804 END IF;
2805
2806 -- Initialize the message table if requested.
2807
2808 IF FND_API.TO_BOOLEAN( p_init_msg_list )
2809 THEN
2810
2811 FND_MSG_PUB.initialize;
2812
2813 END IF;
2814
2815 -- Set API return status to success
2816
2817 p_return_status := FND_API.G_RET_STS_SUCCESS;
2818
2819 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
2820
2821 -- Product Code
2822 IF (p_pm_product_code IS NULL)
2823 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2824 THEN
2825 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2826 THEN
2827 pa_interface_utils_pub.map_new_amg_msg
2828 ( p_old_message_code => 'PA_CUST_NUM_IS_MISS'
2829 ,p_msg_attribute => 'CHANGE'
2830 ,p_resize_flag => 'N'
2831 ,p_msg_context => 'GENERAL'
2832 ,p_attribute1 => ''
2833 ,p_attribute2 => ''
2834 ,p_attribute3 => ''
2835 ,p_attribute4 => ''
2836 ,p_attribute5 => '');
2837 END IF;
2838 p_return_status := FND_API.G_RET_STS_ERROR;
2839 RAISE FND_API.G_EXC_ERROR;
2840 END IF;
2841
2842 -- Funding Reference
2843 IF (p_pm_funding_reference IS NULL)
2844 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2845 THEN
2846 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2847 THEN
2848 pa_interface_utils_pub.map_new_amg_msg
2849 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
2850 ,p_msg_attribute => 'CHANGE'
2851 ,p_resize_flag => 'N'
2852 ,p_msg_context => 'GENERAL'
2853 ,p_attribute1 => ''
2854 ,p_attribute2 => ''
2855 ,p_attribute3 => ''
2856 ,p_attribute4 => ''
2857 ,p_attribute5 => '');
2858 END IF;
2859 p_return_status := FND_API.G_RET_STS_ERROR;
2860 RAISE FND_API.G_EXC_ERROR;
2861 END IF;
2862
2863 /* NOT REQUIRED
2864 -- Funding Id
2865 IF (p_funding_id IS NULL)
2866 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2867 THEN
2868 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2869 THEN
2870 pa_interface_utils_pub.map_new_amg_msg
2871 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
2872 ,p_msg_attribute => 'CHANGE'
2873 ,p_resize_flag => 'N'
2874 ,p_msg_context => 'FUNDING'
2875 ,p_attribute1 => ''
2876 ,p_attribute2 => p_pm_funding_reference
2877 ,p_attribute3 => ''
2878 ,p_attribute4 => ''
2879 ,p_attribute5 => '');
2880 END IF;
2881 p_return_status := FND_API.G_RET_STS_ERROR;
2882 RAISE FND_API.G_EXC_ERROR;
2883 END IF;
2884 */
2885 /* code change for bug 2868818*/
2886
2887 open c1;
2888 fetch c1 into l_fund_rec1;
2889 if c1%found then
2890 l_funding_category := l_fund_rec1.funding_category;
2891 end if;
2892 close c1;
2893
2894 IF l_funding_category = 'REVALUATION'
2895 THEN
2896 pa_interface_utils_pub.map_new_amg_msg
2897 ( p_old_message_code => 'PA_UPDATE_DELETE_REVAL'
2898 ,p_msg_attribute => 'CHANGE'
2899 ,p_resize_flag => 'N'
2900 ,p_msg_context => 'GENERAL'
2901 ,p_attribute1 => ''
2902 ,p_attribute2 => ''
2903 ,p_attribute3 => ''
2904 ,p_attribute4 => ''
2905 ,p_attribute5 => '');
2906
2907 p_return_status := FND_API.G_RET_STS_ERROR;
2908 RAISE FND_API.G_EXC_ERROR;
2909 END IF;
2910
2911 /* end code change for bug 2868818*/
2912
2913 -- - NIKHIL
2914 -- VALIDATE THE INCOMING PARAMETERS
2915 -- NIKHIL added if statement to make it a point while deleting funding for
2916 -- with an agreement it should not check for validity
2917 -- start
2918
2919 IF p_check_y_n = 'Y' THEN
2920 IF pa_agreement_pvt.check_delete_funding_ok
2921 (p_agreement_id => pa_agreement_utils.get_agreement_id( p_funding_id => p_funding_id
2922 ,p_funding_reference => p_pm_funding_reference)
2923 ,p_funding_id => pa_agreement_utils.get_funding_id(p_funding_reference => p_pm_funding_reference)
2924 ,p_pm_funding_reference => p_pm_funding_reference) = 'N'
2925 THEN
2926 p_return_status := FND_API.G_RET_STS_ERROR;
2927 RAISE FND_API.G_EXC_ERROR;
2928 END IF;
2929 END IF;
2930 -- End
2931 -- CONVERT FUNDING REFERENCE TO FUNDING ID
2932
2933 pa_agreement_pvt.convert_fu_ref_to_id
2934 (p_pm_funding_reference => p_pm_funding_reference
2935 ,p_af_funding_id => p_funding_id
2936 ,p_out_funding_id => l_out_funding_id
2937 ,p_return_status => l_return_status);
2938 p_return_status := l_return_status;
2939
2940
2941 -- GET THE VALUES OF AGREEMENT ID, FUNDING ID AND PROJECT ID BEFORE DELETING THE FUNDING
2942 l_agreement_id := pa_agreement_utils.get_agreement_id (p_funding_id => p_funding_id
2943 ,p_funding_reference => p_pm_funding_reference);
2944 l_project_id := pa_agreement_utils.get_project_id(p_funding_id => p_funding_id
2945 ,p_funding_reference => p_pm_funding_reference);
2946 l_task_id := pa_agreement_utils.get_task_id(p_funding_id => p_funding_id
2947 ,p_funding_reference => p_pm_funding_reference);
2948
2949
2950
2951 -- DELETE FUNDING
2952 pa_agreement_utils.delete_funding
2953 (p_project_funding_id =>l_out_funding_id);
2954
2955 -- TO BE CORRECTED - NIKHIL
2956 -- UPDATE SUMMARY PROJECT FUNDINGS
2957 pa_agreement_utils.summary_funding_delete_row
2958 (p_agreement_id => l_agreement_id
2959 ,p_project_id => l_project_id
2960 ,p_task_id => l_task_id
2961 ,p_login_id => G_LOGIN_ID
2962 ,p_user_id => G_USER_ID );
2963 IF FND_API.to_boolean( p_commit )
2964 THEN
2965 COMMIT;
2966 END IF;
2967
2968 EXCEPTION
2969 WHEN FND_API.G_EXC_ERROR
2970 THEN
2971 ROLLBACK TO delete_funding_pub;
2972 p_return_status := FND_API.G_RET_STS_ERROR;
2973 FND_MSG_PUB.Count_And_Get
2974 ( p_count => p_msg_count ,
2975 p_data => p_msg_data );
2976
2977 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2978 THEN
2979 ROLLBACK TO delete_funding_pub;
2980 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2981 FND_MSG_PUB.Count_And_Get
2982 ( p_count => p_msg_count ,
2983 p_data => p_msg_data );
2984
2985 WHEN ROW_ALREADY_LOCKED
2986 THEN
2987 ROLLBACK TO delete_funding_pub;
2988 p_return_status := FND_API.G_RET_STS_ERROR;
2989 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2990 THEN
2991 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED_T_AMG');
2992 FND_MESSAGE.SET_TOKEN('FUNDING',p_pm_funding_reference);
2993 FND_MSG_PUB.ADD;
2994 END IF;
2995 FND_MSG_PUB.Count_And_Get
2996 ( p_count => p_msg_count ,
2997 p_data => p_msg_data );
2998
2999 WHEN OTHERS
3000 THEN
3001 ROLLBACK TO delete_funding_pub;
3002 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3003 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3004 THEN
3005 FND_MSG_PUB.add_exc_msg
3006 ( p_pkg_name => G_PKG_NAME
3007 , p_procedure_name => l_api_name );
3008 END IF;
3009 FND_MSG_PUB.Count_And_Get
3010 ( p_count => p_msg_count ,
3011 p_data => p_msg_data );
3012
3013 END delete_funding;
3014
3015
3016 -- ============================================================================
3017 --
3018 --Name: update_funding
3019 --Type: Procedure
3020 --Description: This procedure can be used to update funding for an agreement.
3021 --
3022 --Called subprograms: XXX
3023 --
3024 --
3025 --
3026 --History:
3027 -- 25-MAR-2000 Rakesh Raghavan Created.
3028 -- 11-JUN-2002 Raji - code added for bug 2403652
3029 -- ---------------------------------------------------------------------------
3030
3031 PROCEDURE update_funding
3032 (p_api_version_number IN NUMBER
3033 ,p_commit IN VARCHAR2
3034 ,p_init_msg_list IN VARCHAR2
3035 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
3036 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
3037 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
3038 ,p_pm_product_code IN VARCHAR2
3039 ,p_pm_funding_reference IN VARCHAR2
3040 ,p_funding_id IN NUMBER
3041 ,p_project_id IN NUMBER
3042 ,p_task_id IN NUMBER
3043 ,p_agreement_id IN NUMBER
3044 ,p_allocated_amount IN NUMBER
3045 ,p_date_allocated IN DATE
3046 ,p_desc_flex_name IN VARCHAR2
3047 ,p_attribute_category IN VARCHAR2
3048 ,p_attribute1 IN VARCHAR2
3049 ,p_attribute2 IN VARCHAR2
3050 ,p_attribute3 IN VARCHAR2
3051 ,p_attribute4 IN VARCHAR2
3052 ,p_attribute5 IN VARCHAR2
3053 ,p_attribute6 IN VARCHAR2
3054 ,p_attribute7 IN VARCHAR2
3055 ,p_attribute8 IN VARCHAR2
3056 ,p_attribute9 IN VARCHAR2
3057 ,p_attribute10 IN VARCHAR2
3058 ,p_funding_id_out OUT NOCOPY NUMBER /*File.sql.39*/
3059 ,p_project_rate_type IN VARCHAR2 DEFAULT NULL
3060 ,p_project_rate_date IN DATE DEFAULT NULL
3061 ,p_project_exchange_rate IN NUMBER DEFAULT NULL
3062 ,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
3063 ,p_projfunc_rate_date IN DATE DEFAULT NULL
3064 ,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
3065 ,p_funding_category IN VARCHAR2 DEFAULT 'ADDITIONAL'
3066 /* Added for Bug 2512483 Default Value- For Bug 2244796 */
3067 )
3068 IS
3069 -- LOCAL VARIABLES
3070 l_msg_count NUMBER ;
3071 l_msg_data VARCHAR2(2000);
3072 l_function_allowed VARCHAR2(1);
3073 l_resp_id NUMBER := 0;
3074 l_return_msg VARCHAR2(2000);
3075 l_validate_status VARCHAR2(1);
3076 l_row_id VARCHAR2(2000);
3077 i NUMBER;
3078 l_funding_id NUMBER;
3079 l_out_funding_id NUMBER;
3080 l_out_agreement_id NUMBER ;
3081 l_return_status VARCHAR2(1);
3082 l_api_name CONSTANT VARCHAR2(30):= 'update_funding';
3083 l_resize_flag VARCHAR2(1); /* Added for bug 2902096 */
3084
3085 l_err_code number;
3086 l_err_msg VARCHAR2(50);
3087
3088 /* adding variables for bug 2868818*/
3089
3090 CURSOR c1
3091 IS
3092 SELECT f.funding_category
3093 FROM PA_PROJECT_FUNDINGS f
3094 WHERE f.pm_funding_reference = p_pm_funding_reference
3095 AND f.funding_category = 'REVALUATION';/* Added this condition for 3360593*/
3096
3097 l_fund_rec1 c1%ROWTYPE;
3098 l_funding_category VARCHAR2(30);
3099
3100 /* end adding variables for bug 2868818*/
3101 BEGIN
3102 -- Standard begin of API savepoint
3103 SAVEPOINT update_funding_pub;
3104
3105
3106 -- Standard call to check for call compatibility.
3107
3108
3109 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3110 p_api_version_number ,
3111 l_api_name ,
3112 G_PKG_NAME )
3113 THEN
3114
3115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3116
3117 END IF;
3118 l_resp_id := FND_GLOBAL.Resp_id;
3119
3120 -- This call is added for patchset K project role based security check
3121
3122 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := p_project_id;
3123
3124
3125 -- Actions performed using the APIs would be subject to
3126 -- function security. If the responsibility does not allow
3127 -- such functions to be executed, the API should not proceed further
3128 -- since the user does not have access to such functions
3129
3130
3131 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
3132 (p_api_version_number => p_api_version_number,
3133 p_responsibility_id => l_resp_id,
3134 p_function_name => 'PA_AF_UPDATE_FUNDING',
3135 p_msg_count => l_msg_count,
3136 p_msg_data => l_msg_data,
3137 p_return_status => l_return_status,
3138 p_function_allowed => l_function_allowed
3139 );
3140
3141 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3142 THEN
3143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3144
3145 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3146 THEN
3147 RAISE FND_API.G_EXC_ERROR;
3148 END IF;
3149 IF l_function_allowed = 'N' THEN
3150 pa_interface_utils_pub.map_new_amg_msg
3151 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
3152 ,p_msg_attribute => 'CHANGE'
3153 ,p_resize_flag => 'Y'
3154 ,p_msg_context => 'GENERAL'
3155 ,p_attribute1 => ''
3156 ,p_attribute2 => ''
3157 ,p_attribute3 => ''
3158 ,p_attribute4 => ''
3159 ,p_attribute5 => '');
3160 RAISE FND_API.G_EXC_ERROR;
3161 END IF;
3162
3163 -- Initialize the message table if requested.
3164
3165 IF FND_API.TO_BOOLEAN( p_init_msg_list )
3166 THEN
3167
3168 FND_MSG_PUB.initialize;
3169
3170 END IF;
3171
3172 -- Set API return status to success
3173
3174 p_return_status := FND_API.G_RET_STS_SUCCESS;
3175
3176 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
3177
3178 -- Product Code
3179 IF (p_pm_product_code IS NULL)
3180 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3181 THEN
3182 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3183 THEN
3184 pa_interface_utils_pub.map_new_amg_msg
3185 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISS'
3186 ,p_msg_attribute => 'CHANGE'
3187 ,p_resize_flag => 'N'
3188 ,p_msg_context => 'GENERAL'
3189 ,p_attribute1 => ''
3190 ,p_attribute2 => ''
3191 ,p_attribute3 => ''
3192 ,p_attribute4 => ''
3193 ,p_attribute5 => '');
3194 END IF;
3195 p_return_status := FND_API.G_RET_STS_ERROR;
3196 RAISE FND_API.G_EXC_ERROR;
3197 END IF;
3198
3199 /* Bug 5686790. If Miss char is passed then this check is not required, as
3200 customer doesn't want to change the funding category*/
3201
3202 IF (p_funding_category <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
3203 -- Code added for Bug 2403652 starts here
3204
3205 -- Funding category is NULL
3206 /* Added IF condition for bug 2838872 */
3207 IF (p_funding_category IS NULL)
3208 /*OR (p_funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3209 Bug 5686790 */
3210 THEN
3211 l_funding_category := 'ADDITIONAL';
3212 ELSE
3213 l_funding_category := p_funding_category;
3214 /***** IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3215 THEN
3216 pa_interface_utils_pub.map_new_amg_msg
3217 ( p_old_message_code => 'PA_FUND_CAT_IS_MISS'
3218 ,p_msg_attribute => 'CHANGE'
3219 ,p_resize_flag => 'N'
3220 ,p_msg_context => 'GENERAL'
3221 ,p_attribute1 => ''
3222 ,p_attribute2 => ''
3223 ,p_attribute3 => ''
3224 ,p_attribute4 => ''
3225 ,p_attribute5 => '');
3226 END IF;
3227 p_return_status := FND_API.G_RET_STS_ERROR;
3228 RAISE FND_API.G_EXC_ERROR;
3229 *** Commented for bug 2838872 ***/
3230 END IF;
3231
3232 -- Validate funding category
3233
3234 IF pa_agreement_pvt.check_funding_category
3235 (p_project_id
3236 ,p_task_id
3237 ,p_agreement_id
3238 ,p_pm_funding_reference
3239 ,upper(l_funding_category)) = 'N'--Added upper for 3360593
3240 THEN
3241 p_return_status := FND_API.G_RET_STS_ERROR;
3242 RAISE FND_API.G_EXC_ERROR;
3243 END IF;
3244 END IF; /* l_funding_category is mischar*/
3245
3246 -- Code added for Bug 2403652 ends here
3247
3248
3249 -- Funding Reference
3250 IF (p_pm_funding_reference IS NULL)
3251 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3252 THEN
3253 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3254 THEN
3255 pa_interface_utils_pub.map_new_amg_msg
3256 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
3257 ,p_msg_attribute => 'CHANGE'
3258 ,p_resize_flag => 'N'
3259 ,p_msg_context => 'GENERAL'
3260 ,p_attribute1 => ''
3261 ,p_attribute2 => ''
3262 ,p_attribute3 => ''
3263 ,p_attribute4 => ''
3264 ,p_attribute5 => '');
3265 END IF;
3266 p_return_status := FND_API.G_RET_STS_ERROR;
3267 RAISE FND_API.G_EXC_ERROR;
3268 END IF;
3269 /* NOT REQUIED
3270 -- Funding Id
3271 IF (p_funding_id IS NULL)
3272 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
3273 THEN
3274 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3275 THEN
3276 pa_interface_utils_pub.map_new_amg_msg
3277 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
3278 ,p_msg_attribute => 'CHANGE'
3279 ,p_resize_flag => 'N'
3280 ,p_msg_context => 'FUNDING'
3281 ,p_attribute1 => ''
3282 ,p_attribute2 => p_pm_funding_reference
3283 ,p_attribute3 => ''
3284 ,p_attribute4 => ''
3285 ,p_attribute5 => '');
3286 END IF;
3287 p_return_status := FND_API.G_RET_STS_ERROR;
3288 RAISE FND_API.G_EXC_ERROR;
3289 END IF;
3290 */
3291
3292 /* code change for bug 2868818*/
3293
3294 open c1;
3295 fetch c1 into l_fund_rec1;
3296 if c1%found then
3297 l_funding_category := l_fund_rec1.funding_category;
3298 end if;
3299 close c1;
3300
3301
3302 IF l_funding_category = 'REVALUATION'
3303 THEN
3304 pa_interface_utils_pub.map_new_amg_msg
3305 ( p_old_message_code => 'PA_UPDATE_DELETE_REVAL'
3306 ,p_msg_attribute => 'CHANGE'
3307 ,p_resize_flag => 'N'
3308 ,p_msg_context => 'GENERAL'
3309 ,p_attribute1 => ''
3310 ,p_attribute2 => ''
3311 ,p_attribute3 => ''
3312 ,p_attribute4 => ''
3313 ,p_attribute5 => '');
3314
3315 p_return_status := FND_API.G_RET_STS_ERROR;
3316 RAISE FND_API.G_EXC_ERROR;
3317 END IF;
3318
3319
3320 /* Moved the following function call from below to this place bug 2434153 **/
3321 -- CONVERT FUNDING REFERENCE TO FUNDING ID
3322 pa_agreement_pvt.convert_fu_ref_to_id
3323 (p_pm_funding_reference => p_pm_funding_reference
3324 ,p_af_funding_id => p_funding_id
3325 ,p_out_funding_id => l_out_funding_id
3326 ,p_return_status => l_return_status);
3327 p_return_status := l_return_status;
3328
3329 /* end code change for bug 2868818*/
3330 /** changed p_funding_id to l_out_funding_id in call to check_update_funding_ok bug 2434153 **/
3331 -- TO BE CORRECTED - NIKHIL
3332 -- VALIDATE THE INCOMING PARAMETERS
3333 IF pa_agreement_pvt.check_update_funding_ok
3334 (p_project_id => p_project_id
3335 ,p_task_id => p_task_id
3336 ,p_agreement_id => p_agreement_id
3337 ,p_customer_id => pa_agreement_utils.get_customer_id
3338 (p_funding_id => l_out_funding_id
3339 ,p_funding_reference => p_pm_funding_reference)
3340 ,p_pm_funding_reference => p_pm_funding_reference
3341 ,p_funding_id => l_out_funding_id
3342 ,p_funding_amt => p_allocated_amount
3343 /* MCB2 PARAMETERS BEGIN */
3344 ,p_project_rate_type => p_project_rate_type
3345 ,p_project_rate_date => p_project_rate_date
3346 ,p_project_exchange_rate => p_project_exchange_rate
3347 ,p_projfunc_rate_type => p_projfunc_rate_type
3348 ,p_projfunc_rate_date => p_projfunc_rate_date
3349 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate ) = 'N'
3350 /* MCB2 PARAMETERS END */
3351
3352 THEN
3353 p_return_status := FND_API.G_RET_STS_ERROR;
3354 RAISE FND_API.G_EXC_ERROR;
3355 END IF;
3356
3357
3358 -- Flex Field Validations
3359 IF (p_desc_flex_name IS NOT NULL)
3360 AND (p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3361 -- Nikhil OR (p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3362 THEN
3363 pa_agreement_pvt.validate_flex_fields
3364 (p_desc_flex_name => p_desc_flex_name
3365 ,p_attribute_category => p_attribute_category
3366 ,p_attribute1 => p_attribute1
3367 ,p_attribute2 => p_attribute2
3368 ,p_attribute3 => p_attribute3
3369 ,p_attribute4 => p_attribute4
3370 ,p_attribute5 => p_attribute5
3371 /** ,p_attribute6 => p_attribute7 ** commented bug 2862922 **/
3372 ,p_attribute6 => p_attribute6 /** added bug 2862922 **/
3373 ,p_attribute7 => p_attribute7 /** added bug 2862922 **/
3374 ,p_attribute8 => p_attribute8
3375 ,p_attribute9 => p_attribute9
3376 ,p_attribute10 => p_attribute10
3377 ,p_return_msg => l_return_msg
3378 ,p_validate_status => l_validate_status
3379 );
3380 IF l_validate_status = 'N'
3381 THEN
3382 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3383 THEN
3384 pa_interface_utils_pub.map_new_amg_msg
3385 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
3386 ,p_msg_attribute => 'CHANGE'
3387 ,p_resize_flag => 'N'
3388 ,p_msg_context => 'FLEX'
3389 ,p_attribute1 => l_return_msg
3390 ,p_attribute2 => ''
3391 ,p_attribute3 => ''
3392 ,p_attribute4 => ''
3393 ,p_attribute5 => '');
3394 END IF;
3395 p_return_status := FND_API.G_RET_STS_ERROR;
3396 RAISE FND_API.G_EXC_ERROR;
3397 END IF;
3398 END IF;
3399
3400 /* NOT REQUIRED
3401 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
3402 pa_agreement_pvt.convert_ag_ref_to_id
3403 (p_pm_ag_reference => p_agreement_in_rec.pm_agreement_reference
3404 ,p_af_agreement_id => pa_agreement_in_rec.agreement_id
3405 ,p_out_agreement_id => l_out_agreement_id
3406 ,p_return_status => l_return_status);
3407 p_return_status := l_return_status;
3408
3409 */
3410
3411 -- CONVERT FUNDING REFERENCE TO FUNDING ID
3412 /**** pa_agreement_pvt.convert_fu_ref_to_id
3413 (p_pm_funding_reference => p_pm_funding_reference
3414 ,p_af_funding_id => p_funding_id
3415 ,p_out_funding_id => l_out_funding_id
3416 ,p_return_status => l_return_status);
3417 p_return_status := l_return_status;
3418 ***** Commented bug 2434153 **/
3419
3420 /* Added for bug 5686790*/
3421
3422 IF (p_funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
3423 SELECT funding_category
3424 INTO l_funding_category
3425 FROM pa_project_fundings
3426 WHERE PROJECT_FUNDING_ID = l_out_funding_id;
3427 END IF;
3428
3429
3430 -- TO BE CORRECTED - NIKHIL
3431 -- UPDATE FUNDING
3432 pa_agreement_utils.update_funding
3433 (p_project_funding_id => l_out_funding_id
3434 ,p_last_update_date => SYSDATE
3435 ,p_last_updated_by => G_USER_ID
3436 ,p_last_update_login => G_LOGIN_ID
3437 ,p_agreement_id => p_agreement_id
3438 ,p_project_id => p_project_id
3439 ,p_task_id => p_task_id
3440 ,p_allocated_amount => p_allocated_amount
3441 ,p_date_allocated => p_date_allocated
3442 ,p_attribute_category => p_attribute_category
3443 ,p_attribute1 => p_attribute1
3444 ,p_attribute2 => p_attribute2
3445 ,p_attribute3 => p_attribute3
3446 ,p_attribute4 => p_attribute4
3447 ,p_attribute5 => p_attribute5
3448 ,p_attribute6 => p_attribute6
3449 ,p_attribute7 => p_attribute7
3450 ,p_attribute8 => p_attribute8
3451 ,p_attribute9 => p_attribute9
3452 ,p_attribute10 => p_attribute10
3453 ,p_pm_funding_reference => p_pm_funding_reference
3454 ,p_pm_product_code => p_pm_product_code
3455 /* MCB2 PARAMETERS BEGIN */
3456 ,p_project_rate_type => p_project_rate_type
3457 ,p_project_rate_date => p_project_rate_date
3458 ,p_project_exchange_rate => p_project_exchange_rate
3459 ,p_projfunc_rate_type => p_projfunc_rate_type
3460 ,p_projfunc_rate_date => p_projfunc_rate_date
3461 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate
3462 ,x_err_code => l_err_code
3463 ,x_err_msg => l_err_msg
3464 ,p_funding_category => l_funding_category /* For Bug 2244796 */
3465 );
3466 /* MCB2 PARAMETERS END */
3467
3468 IF l_err_code <> 0 THEN
3469
3470 /* Following code added for bug 2902096 .The l_resize_flag is set to 'Y' only for some
3471 error messages whose length of message_name will go above 30 if '_AMG' is added to it.
3472 These messages can be used straightaway without using the '_AMG' as the message text is same .*/
3473 /* Added some more error messages in the IF condition for bug 2967759 */
3474 l_resize_flag:='N';
3475
3476 IF (l_err_msg in ('PA_USR_RATE_NOT_ALLOWED_FC_PC','PA_USR_RATE_NOT_ALLOWED_FC_PF',
3477 'PA_USR_RATE_NOT_ALLOWED_BC_PF','PA_NO_EXCH_RATE_EXISTS_BC_PF',
3478 'PA_NO_EXCH_RATE_EXISTS_FC_PC','PA_NO_EXCH_RATE_EXISTS_FC_PF')) THEN
3479 l_resize_flag:='Y';
3480 END IF;
3481
3482
3483 /* End - Bug 2902096 */
3484
3485 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3486 THEN
3487 pa_interface_utils_pub.map_new_amg_msg
3488 ( p_old_message_code => l_err_msg
3489 ,p_msg_attribute => 'CHANGE'
3490 /* ,p_resize_flag => 'N' changed for bug 2902096 */
3491 ,p_resize_flag => l_resize_flag
3492 ,p_msg_context => 'FLEX'
3493 ,p_attribute1 => l_return_msg
3494 ,p_attribute2 => ''
3495 ,p_attribute3 => ''
3496 ,p_attribute4 => ''
3497 ,p_attribute5 => '');
3498 END IF;
3499 p_return_status := FND_API.G_RET_STS_ERROR;
3500 RAISE FND_API.G_EXC_ERROR;
3501
3502 ELSE
3503
3504
3505 -- UPDATE SUMMARY PROJECT FUNDINGS
3506 pa_agreement_utils.summary_funding_update_row
3507 (p_agreement_id => p_agreement_id
3508 ,p_project_id => p_project_id
3509 ,p_task_id => p_task_id
3510 ,p_login_id => G_LOGIN_ID
3511 ,p_user_id => G_USER_ID
3512 );
3513
3514
3515 IF FND_API.to_boolean( p_commit ) THEN
3516 COMMIT;
3517 END IF;
3518
3519 END IF;
3520
3521 EXCEPTION
3522
3523 WHEN FND_API.G_EXC_ERROR
3524 THEN
3525 p_return_status := FND_API.G_RET_STS_ERROR ;
3526 ROLLBACK TO update_funding_pub;
3527 FND_MSG_PUB.Count_And_Get
3528 ( p_count => p_msg_count ,
3529 p_data => p_msg_data );
3530
3531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3532 THEN
3533 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3534 ROLLBACK TO update_funding_pub;
3535 FND_MSG_PUB.Count_And_Get
3536 ( p_count => p_msg_count ,
3537 p_data => p_msg_data );
3538
3539 WHEN OTHERS
3540 THEN
3541 ROLLBACK TO update_funding_pub;
3542 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3543 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3544 THEN
3545 FND_MSG_PUB.add_exc_msg
3546 ( p_pkg_name => G_PKG_NAME
3547 , p_procedure_name => l_api_name );
3548 END IF;
3549 FND_MSG_PUB.Count_And_Get
3550 ( p_count => p_msg_count ,
3551 p_data => p_msg_data );
3552
3553 END update_funding;
3554
3555 -- ============================================================================
3556 --
3557 --Name: init_agreement
3558 --Type: Procedure
3559 --Description: This procedure can be used to initialize the global PL/SQL
3560 -- tables that are used by a LOAD/EXECUTE/FETCH cycle.
3561 --
3562 --Called subprograms: XXX
3563 --
3564 --
3565 --
3566 --History:
3567 -- 25-MAR-2000 Rakesh Raghavan Created.
3568 -- ---------------------------------------------------------------------------
3569
3570 PROCEDURE init_agreement
3571 IS
3572 BEGIN
3573
3574 -- Standard begin of API savepoint
3575
3576 SAVEPOINT init_agreement_pub;
3577
3578 -- Initialize global table and record types
3579
3580 G_agreement_in_rec := G_agreement_in_null_rec;
3581 G_funding_in_tbl.delete;
3582 G_funding_tbl_count := 0;
3583 G_agreement_out_rec := G_agreement_out_null_rec;
3584 G_funding_out_tbl.delete;
3585
3586 END init_agreement;
3587
3588 -- ============================================================================
3589 --
3590 --Name: load_agreement
3591 --Type: Procedure
3592 --Description: This procedure can be used to move the agreement related
3593 -- parameters from the client side to a record on the server side
3594 -- , where it will be used by a LOAD/EXECUTE/FETCH cycle.
3595 --
3596 --Called subprograms: XXX
3597 --
3598 --
3599 --
3600 --History:
3601 -- 25-MAR-2000 Rakesh Raghavan Created.
3602 -- ---------------------------------------------------------------------------
3603
3604
3605 PROCEDURE load_agreement
3606 (p_api_version_number IN NUMBER
3607 ,p_init_msg_list IN VARCHAR2
3608 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
3609 ,p_pm_agreement_reference IN VARCHAR2
3610 ,p_agreement_id IN NUMBER
3611 ,p_customer_id IN NUMBER
3612 ,p_customer_name IN VARCHAR2
3613 ,p_customer_num IN VARCHAR2
3614 ,p_agreement_num IN VARCHAR2
3615 ,p_agreement_type IN VARCHAR2
3616 ,p_amount IN NUMBER
3617 ,p_term_id IN NUMBER
3618 ,p_term_name IN VARCHAR2
3619 ,p_revenue_limit_flag IN VARCHAR2
3620 ,p_expiration_date IN DATE
3621 ,p_description IN VARCHAR2
3622 ,p_owned_by_person_id IN NUMBER
3623 ,p_owned_by_person_name IN VARCHAR2
3624 ,p_attribute_category IN VARCHAR2
3625 ,p_attribute1 IN VARCHAR2
3626 ,p_attribute2 IN VARCHAR2
3627 ,p_attribute3 IN VARCHAR2
3628 ,p_attribute4 IN VARCHAR2
3629 ,p_attribute5 IN VARCHAR2
3630 ,p_attribute6 IN VARCHAR2
3631 ,p_attribute7 IN VARCHAR2
3632 ,p_attribute8 IN VARCHAR2
3633 ,p_attribute9 IN VARCHAR2
3634 ,p_attribute10 IN VARCHAR2
3635 ,p_template_flag IN VARCHAR2
3636 ,p_desc_flex_name IN VARCHAR2
3637 ,p_owning_organization_id IN NUMBER
3638 ,p_agreement_currency_code IN VARCHAR2
3639 ,p_invoice_limit_flag IN VARCHAR2
3640 /*Federal*/
3641 ,p_customer_order_number IN VARCHAR2
3642 ,p_advance_required IN VARCHAR2
3643 ,p_start_date IN DATE
3644 ,p_billing_sequence IN NUMBER
3645 ,p_line_of_account IN VARCHAR2
3646 ,p_attribute11 IN VARCHAR2
3647 ,p_attribute12 IN VARCHAR2
3648 ,p_attribute13 IN VARCHAR2
3649 ,p_attribute14 IN VARCHAR2
3650 ,p_attribute15 IN VARCHAR2
3651 ,p_attribute16 IN VARCHAR2
3652 ,p_attribute17 IN VARCHAR2
3653 ,p_attribute18 IN VARCHAR2
3654 ,p_attribute19 IN VARCHAR2
3655 ,p_attribute20 IN VARCHAR2
3656 ,p_attribute21 IN VARCHAR2
3657 ,p_attribute22 IN VARCHAR2
3658 ,p_attribute23 IN VARCHAR2
3659 ,p_attribute24 IN VARCHAR2
3660 ,p_attribute25 IN VARCHAR2
3661 )
3662 IS
3663 -- LOCAL VARIABLES
3664 l_msg_count NUMBER ;
3665 l_msg_data VARCHAR2(2000);
3666 l_function_allowed VARCHAR2(1);
3667 l_resp_id NUMBER := 0;
3668 l_return_status VARCHAR2(1);
3669 l_api_name CONSTANT VARCHAR2(30):= 'load_agreement';
3670
3671 BEGIN
3672 -- Standard begin of API savepoint
3673
3674 SAVEPOINT load_agreement_pub;
3675
3676
3677 -- Standard call to check for call compatibility.
3678
3679
3680 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3681 p_api_version_number ,
3682 l_api_name ,
3683 G_PKG_NAME )
3684 THEN
3685
3686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3687
3688 END IF;
3689 l_resp_id := FND_GLOBAL.Resp_id;
3690
3691 -- Actions performed using the APIs would be subject to
3692 -- function security. If the responsibility does not allow
3693 -- such functions to be executed, the API should not proceed further
3694 -- since the user does not have access to such functions
3695
3696 /* NOT REQUIRED
3697
3698 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
3699 (p_api_version_number => p_api_version_number,
3700 p_responsibility_id => l_resp_id,
3701 p_function_name => 'PA_AF_LOAD_AGREEMENT',
3702 p_msg_count => l_msg_count,
3703 p_msg_data => l_msg_data,
3704 p_return_status => l_return_status,
3705 p_function_allowed => l_function_allowed
3706 );
3707
3708 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3709 THEN
3710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3711
3712 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3713 THEN
3714 RAISE FND_API.G_EXC_ERROR;
3715 END IF;
3716 IF l_function_allowed = 'N' THEN
3717 pa_interface_utils_pub.map_new_amg_msg
3718 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
3719 ,p_msg_attribute => 'CHANGE'
3720 ,p_resize_flag => 'Y'
3721 ,p_msg_context => 'GENERAL'
3722 ,p_attribute1 => ''
3723 ,p_attribute2 => ''
3724 ,p_attribute3 => ''
3725 ,p_attribute4 => ''
3726 ,p_attribute5 => '');
3727 p_return_status := FND_API.G_RET_STS_ERROR;
3728 RAISE FND_API.G_EXC_ERROR;
3729 END IF;
3730 */
3731 -- Initialize the message table if requested.
3732
3733 IF FND_API.TO_BOOLEAN( p_init_msg_list )
3734 THEN
3735
3736 FND_MSG_PUB.initialize;
3737
3738 END IF;
3739
3740 -- Set API return status to success
3741
3742 p_return_status := FND_API.G_RET_STS_SUCCESS;
3743
3744
3745 -- ASSIGN INCOMING PARAMETERS TO THE FIELDS OF THE GLOBAL AGREEMENT RECORD
3746
3747 G_agreement_in_rec.agreement_id := p_agreement_id;
3748 G_agreement_in_rec.agreement_num := p_agreement_num;
3749 G_agreement_in_rec.pm_agreement_reference := p_pm_agreement_reference;
3750 G_agreement_in_rec.customer_id := p_customer_id;
3751 G_agreement_in_rec.customer_num := p_customer_num;
3752 G_agreement_in_rec.agreement_type := p_agreement_type;
3753 G_agreement_in_rec.amount := p_amount;
3754 G_agreement_in_rec.term_id := p_term_id;
3755 G_agreement_in_rec.revenue_limit_flag := p_revenue_limit_flag;
3756 G_agreement_in_rec.expiration_date := p_expiration_date;
3757 G_agreement_in_rec.description := p_description;
3758 G_agreement_in_rec.owned_by_person_id := p_owned_by_person_id;
3759 G_agreement_in_rec.template_flag := p_template_flag;
3760 G_agreement_in_rec.desc_flex_name := p_desc_flex_name;
3761 G_agreement_in_rec.attribute_category := p_attribute_category;
3762 G_agreement_in_rec.attribute1 := p_attribute1;
3763 G_agreement_in_rec.attribute2 := p_attribute2;
3764 G_agreement_in_rec.attribute3 := p_attribute3;
3765 G_agreement_in_rec.attribute4 := p_attribute4;
3766 G_agreement_in_rec.attribute5 := p_attribute5;
3767 G_agreement_in_rec.attribute6 := p_attribute6;
3768 G_agreement_in_rec.attribute7 := p_attribute7;
3769 G_agreement_in_rec.attribute8 := p_attribute8;
3770 G_agreement_in_rec.attribute9 := p_attribute9;
3771 G_agreement_in_rec.attribute10 := p_attribute10;
3772 G_agreement_in_rec.owning_organization_id := p_owning_organization_id;
3773 G_agreement_in_rec.agreement_currency_code := p_agreement_currency_code;
3774 G_agreement_in_rec.invoice_limit_flag := p_invoice_limit_flag;
3775 /*Federal*/
3776 G_agreement_in_rec.customer_order_number := p_customer_order_number;
3777 G_agreement_in_rec.advance_required := p_advance_required;
3778 G_agreement_in_rec.start_date := p_start_date;
3779 G_agreement_in_rec.billing_sequence := p_billing_sequence;
3780 G_agreement_in_rec.line_of_account := p_line_of_account;
3781 G_agreement_in_rec.attribute11 := p_attribute11;
3782 G_agreement_in_rec.attribute12 := p_attribute12;
3783 G_agreement_in_rec.attribute13 := p_attribute13;
3784 G_agreement_in_rec.attribute14 := p_attribute14;
3785 G_agreement_in_rec.attribute15 := p_attribute15;
3786 G_agreement_in_rec.attribute16 := p_attribute16;
3787 G_agreement_in_rec.attribute17 := p_attribute17;
3788 G_agreement_in_rec.attribute18 := p_attribute18;
3789 G_agreement_in_rec.attribute19 := p_attribute19;
3790 G_agreement_in_rec.attribute20 := p_attribute20;
3791 G_agreement_in_rec.attribute21 := p_attribute21;
3792 G_agreement_in_rec.attribute22 := p_attribute22;
3793 G_agreement_in_rec.attribute23 := p_attribute23;
3794 G_agreement_in_rec.attribute24 := p_attribute24;
3795 G_agreement_in_rec.attribute25 := p_attribute25;
3796
3797
3798 EXCEPTION
3799
3800 WHEN FND_API.G_EXC_ERROR
3801 THEN
3802 ROLLBACK TO load_agreement_pub;
3803 p_return_status := FND_API.G_RET_STS_ERROR;
3804
3805
3806 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3807 THEN
3808 ROLLBACK TO load_agreement_pub;
3809 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3810
3811
3812 WHEN OTHERS
3813 THEN
3814 ROLLBACK TO load_agreement_pub;
3815 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3816
3817 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3818 THEN
3819 FND_MSG_PUB.add_exc_msg
3820 ( p_pkg_name => G_PKG_NAME
3821 , p_procedure_name => l_api_name );
3822 END IF;
3823
3824 END load_agreement;
3825
3826 -- ============================================================================
3827 --
3828 --Name: load_funding
3829 --Type: Procedure
3830 --Description: This procedure can be used to move the funding related
3831 -- parameters from the client side to a record on the server side
3832 -- , where it will be used by a LOAD/EXECUTE/FETCH cycle.
3833 --
3834 --Called subprograms: XXX
3835 --
3836 --
3837 --
3838 --History:
3839 -- 25-MAR-2000 Rakesh Raghavan Created.
3840 -- ---------------------------------------------------------------------------
3841
3842 PROCEDURE load_funding
3843 (p_api_version_number IN NUMBER
3844 ,p_init_msg_list IN VARCHAR2
3845 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
3846 ,p_pm_funding_reference IN VARCHAR2
3847 ,p_funding_id IN NUMBER
3848 ,p_agreement_id IN NUMBER
3849 ,p_project_id IN NUMBER
3850 ,p_task_id IN NUMBER
3851 ,p_allocated_amount IN NUMBER
3852 ,p_date_allocated IN DATE
3853 ,p_attribute_category IN VARCHAR2
3854 ,p_attribute1 IN VARCHAR2
3855 ,p_attribute2 IN VARCHAR2
3856 ,p_attribute3 IN VARCHAR2
3857 ,p_attribute4 IN VARCHAR2
3858 ,p_attribute5 IN VARCHAR2
3859 ,p_attribute6 IN VARCHAR2
3860 ,p_attribute7 IN VARCHAR2
3861 ,p_attribute8 IN VARCHAR2
3862 ,p_attribute9 IN VARCHAR2
3863 ,p_attribute10 IN VARCHAR2
3864 ,p_project_rate_type IN VARCHAR2 DEFAULT NULL
3865 ,p_project_rate_date IN DATE DEFAULT NULL
3866 ,p_project_exchange_rate IN NUMBER DEFAULT NULL
3867 ,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
3868 ,p_projfunc_rate_date IN DATE DEFAULT NULL
3869 ,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
3870 ,p_funding_category IN VARCHAR2 DEFAULT 'ADDITIONAL'
3871 /* Added for Bug 2483081 to include Default value - For Bug 2244796 */
3872 )
3873 IS
3874 -- LOCAL VARIABLES
3875 l_msg_count NUMBER ;
3876 l_msg_data VARCHAR2(2000);
3877 l_function_allowed VARCHAR2(1);
3878 l_resp_id NUMBER := 0;
3879 l_return_status VARCHAR2(1);
3880 l_api_name CONSTANT VARCHAR2(30):= 'load_funding';
3881 BEGIN
3882 -- Standard begin of API savepoint
3883
3884 SAVEPOINT load_funding_pub;
3885
3886
3887 -- Standard call to check for call compatibility.
3888
3889
3890 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3891 p_api_version_number ,
3892 l_api_name ,
3893 G_PKG_NAME )
3894 THEN
3895
3896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3897
3898 END IF;
3899 l_resp_id := FND_GLOBAL.Resp_id;
3900
3901 -- Actions performed using the APIs would be subject to
3902 -- function security. If the responsibility does not allow
3903 -- such functions to be executed, the API should not proceed further
3904 -- since the user does not have access to such functions
3905
3906 /* NOT REQUIRED
3907 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
3908 (p_api_version_number => p_api_version_number,
3909 p_responsibility_id => l_resp_id,
3910 p_function_name => 'PA_AF_LOAD_FUNDING',
3911 p_msg_count => l_msg_count,
3912 p_msg_data => l_msg_data,
3913 p_return_status => l_return_status,
3914 p_function_allowed => l_function_allowed
3915 );
3916
3917 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3918 THEN
3919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3920
3921 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3922 THEN
3923 RAISE FND_API.G_EXC_ERROR;
3924 END IF;
3925 IF l_function_allowed = 'N' THEN
3926 pa_interface_utils_pub.map_new_amg_msg
3927 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
3928 ,p_msg_attribute => 'CHANGE'
3929 ,p_resize_flag => 'Y'
3930 ,p_msg_context => 'GENERAL'
3931 ,p_attribute1 => ''
3932 ,p_attribute2 => ''
3933 ,p_attribute3 => ''
3934 ,p_attribute4 => ''
3935 ,p_attribute5 => '');
3936 p_return_status := FND_API.G_RET_STS_ERROR;
3937 RAISE FND_API.G_EXC_ERROR;
3938 END IF;
3939 */
3940 -- Initialize the message table if requested.
3941
3942 IF FND_API.TO_BOOLEAN( p_init_msg_list )
3943 THEN
3944
3945 FND_MSG_PUB.initialize;
3946
3947 END IF;
3948
3949 -- Set API return status to success
3950
3951 p_return_status := FND_API.G_RET_STS_SUCCESS;
3952
3953 -- ASSIGN A VALUE TO THE GLOBAL COUNTER FOR THIS TABLE
3954 G_funding_tbl_count := G_funding_tbl_count + 1;
3955
3956 -- ASSIGN INCOMING PARAMETERS TO THE GLOBAL TABLE FIELDS
3957 G_funding_in_tbl(G_funding_tbl_count).pm_funding_reference := p_pm_funding_reference;
3958 G_funding_in_tbl(G_funding_tbl_count).project_funding_id := p_funding_id;
3959 G_funding_in_tbl(G_funding_tbl_count).project_id := p_project_id;
3960 G_funding_in_tbl(G_funding_tbl_count).task_id := p_task_id;
3961 G_funding_in_tbl(G_funding_tbl_count).allocated_amount := p_allocated_amount;
3962 G_funding_in_tbl(G_funding_tbl_count).date_allocated := p_date_allocated;
3963 G_funding_in_tbl(G_funding_tbl_count).attribute_category := p_attribute_category;
3964 G_funding_in_tbl(G_funding_tbl_count).attribute1 := p_attribute1;
3965 G_funding_in_tbl(G_funding_tbl_count).attribute2 := p_attribute2;
3966 G_funding_in_tbl(G_funding_tbl_count).attribute3 := p_attribute3;
3967 G_funding_in_tbl(G_funding_tbl_count).attribute4 := p_attribute4;
3968 G_funding_in_tbl(G_funding_tbl_count).attribute5 := p_attribute5;
3969 G_funding_in_tbl(G_funding_tbl_count).attribute6 := p_attribute6;
3970 G_funding_in_tbl(G_funding_tbl_count).attribute7 := p_attribute7;
3971 G_funding_in_tbl(G_funding_tbl_count).attribute8 := p_attribute8;
3972 G_funding_in_tbl(G_funding_tbl_count).attribute9 := p_attribute9;
3973 G_funding_in_tbl(G_funding_tbl_count).attribute10 := p_attribute10;
3974 G_funding_in_tbl(G_funding_tbl_count).project_rate_type := p_project_rate_type;
3975 G_funding_in_tbl(G_funding_tbl_count).project_rate_date := p_project_rate_date;
3976 G_funding_in_tbl(G_funding_tbl_count).project_exchange_rate := p_project_exchange_rate;
3977 G_funding_in_tbl(G_funding_tbl_count).projfunc_rate_type := p_projfunc_rate_type;
3978 G_funding_in_tbl(G_funding_tbl_count).projfunc_rate_date := p_projfunc_rate_date;
3979 G_funding_in_tbl(G_funding_tbl_count).projfunc_exchange_rate := p_projfunc_exchange_rate;
3980 G_funding_in_tbl(G_funding_tbl_count).funding_category := p_funding_category; /* Added for Bug 2403652 */
3981
3982
3983 EXCEPTION
3984
3985 WHEN FND_API.G_EXC_ERROR
3986 THEN
3987 ROLLBACK TO load_funding_pub;
3988 p_return_status := FND_API.G_RET_STS_ERROR;
3989
3990 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3991 THEN
3992 ROLLBACK TO load_funding_pub;
3993 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3994
3995 WHEN OTHERS
3996 THEN
3997 ROLLBACK TO load_funding_pub;
3998 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3999 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4000 THEN
4001 FND_MSG_PUB.add_exc_msg
4002 ( p_pkg_name => G_PKG_NAME
4003 , p_procedure_name => l_api_name );
4004 END IF;
4005
4006 END load_funding;
4007
4008
4009 -- ============================================================================
4010 --
4011 --Name: execute_create_agreement
4012 --Type: Procedure
4013 --Description: This procedure can be used to create an agreement
4014 -- using global PL/SQL tables.
4015 --
4016 --Called subprograms: XXX
4017 --
4018 --
4019 --
4020 --History:
4021 -- 25-MAR-2000 Rakesh Raghavan Created.
4022 -- ---------------------------------------------------------------------------
4023
4024 PROCEDURE execute_create_agreement
4025 (p_api_version_number IN NUMBER
4026 ,p_commit IN VARCHAR2
4027 ,p_init_msg_list IN VARCHAR2
4028 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
4029 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
4030 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4031 ,p_pm_product_code IN VARCHAR2
4032 ,p_agreement_id_out OUT NOCOPY NUMBER
4033 ,p_customer_id_out OUT NOCOPY NUMBER
4034 )
4035 IS
4036 -- LOCAL VARIABLES
4037 l_msg_count NUMBER ;
4038 l_msg_data VARCHAR2(2000);
4039 l_function_allowed VARCHAR2(1);
4040 l_resp_id NUMBER := 0;
4041 l_return_status VARCHAR2(1);
4042 l_api_name CONSTANT VARCHAR2(30):= 'execute_create_agreement';
4043 BEGIN
4044 -- Standard begin of API savepoint
4045
4046 SAVEPOINT execute_create_agreement_pub;
4047
4048
4049 -- Standard call to check for call compatibility.
4050
4051
4052 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4053 p_api_version_number ,
4054 l_api_name ,
4055 G_PKG_NAME )
4056 THEN
4057
4058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4059
4060 END IF;
4061 l_resp_id := FND_GLOBAL.Resp_id;
4062
4063 -- Actions performed using the APIs would be subject to
4064 -- function security. If the responsibility does not allow
4065 -- such functions to be executed, the API should not proceed further
4066 -- since the user does not have access to such functions
4067
4068 /* NOT REQUIRED
4069 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4070 (p_api_version_number => p_api_version_number,
4071 p_responsibility_id => l_resp_id,
4072 p_function_name => 'PA_AF_EXECUTE_CREATE_AGREEMENT',
4073 p_msg_count => l_msg_count,
4074 p_msg_data => l_msg_data,
4075 p_return_status => l_return_status,
4076 p_function_allowed => l_function_allowed
4077 );
4078
4079 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4080 THEN
4081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4082
4083 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4084 THEN
4085 RAISE FND_API.G_EXC_ERROR;
4086 END IF;
4087 IF l_function_allowed = 'N' THEN
4088 pa_interface_utils_pub.map_new_amg_msg
4089 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4090 ,p_msg_attribute => 'CHANGE'
4091 ,p_resize_flag => 'Y'
4092 ,p_msg_context => 'GENERAL'
4093 ,p_attribute1 => ''
4094 ,p_attribute2 => ''
4095 ,p_attribute3 => ''
4096 ,p_attribute4 => ''
4097 ,p_attribute5 => '');
4098 p_return_status := FND_API.G_RET_STS_ERROR;
4099 RAISE FND_API.G_EXC_ERROR;
4100 END IF;
4101 */
4102 -- Initialize the message table if requested.
4103
4104 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4105 THEN
4106
4107 FND_MSG_PUB.initialize;
4108
4109 END IF;
4110
4111 -- Set API return status to success
4112
4113 p_return_status := FND_API.G_RET_STS_SUCCESS;
4114
4115
4116 -- CALL CREATE AGREEMENT
4117 pa_agreement_pub.create_agreement
4118 (p_api_version_number => p_api_version_number
4119 ,p_commit => p_commit
4120 ,p_init_msg_list => p_init_msg_list
4121 ,p_msg_count => p_msg_count
4122 ,p_msg_data => p_msg_data
4123 ,p_return_status => l_return_status
4124 ,p_pm_product_code => p_pm_product_code
4125 ,p_agreement_in_rec => G_agreement_in_rec
4126 ,p_agreement_out_rec => G_agreement_out_rec
4127 ,p_funding_in_tbl => G_funding_in_tbl
4128 ,p_funding_out_tbl => G_funding_out_tbl);
4129
4130 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4131 THEN
4132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4133 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4134 THEN
4135 RAISE FND_API.G_EXC_ERROR;
4136 END IF;
4137
4138 -- ASSIGN OUTGOING VALUES TO OUTGOING PARAMETERS
4139 IF G_agreement_out_rec.agreement_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4140 THEN
4141 p_agreement_id_out := NULL;
4142 ELSE
4143 p_agreement_id_out := G_agreement_out_rec.agreement_id;
4144 END IF;
4145
4146 IF G_agreement_out_rec.customer_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4147 THEN
4148 p_customer_id_out := NULL;
4149 ELSE
4150 p_customer_id_out := G_agreement_out_rec.customer_id;
4151 END IF;
4152
4153 IF FND_API.to_boolean( p_commit )
4154 THEN
4155 COMMIT;
4156 END IF;
4157
4158
4159 EXCEPTION
4160
4161 WHEN FND_API.G_EXC_ERROR
4162 THEN
4163 ROLLBACK TO execute_create_agreement_pub;
4164 p_return_status := FND_API.G_RET_STS_ERROR;
4165 FND_MSG_PUB.Count_And_Get
4166 ( p_count => p_msg_count ,
4167 p_data => p_msg_data );
4168
4169 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4170 THEN
4171 ROLLBACK TO execute_create_agreement_pub;
4172 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4173 FND_MSG_PUB.Count_And_Get
4174 ( p_count => p_msg_count ,
4175 p_data => p_msg_data );
4176
4177 WHEN OTHERS
4178 THEN
4179 ROLLBACK TO execute_create_agreement_pub;
4180 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4181 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4182 THEN
4183 FND_MSG_PUB.add_exc_msg
4184 ( p_pkg_name => G_PKG_NAME
4185 , p_procedure_name => l_api_name );
4186 END IF;
4187 FND_MSG_PUB.Count_And_Get
4188 ( p_count => p_msg_count ,
4189 p_data => p_msg_data );
4190
4191 END execute_create_agreement;
4192
4193
4194 -- ============================================================================
4195 --
4196 --Name: execute_update_agreement
4197 --Type: Procedure
4198 --Description: This procedure can be used to update an agreement
4199 -- using global PL/SQL tables.
4200 --
4201 --Called subprograms: XXX
4202 --
4203 --
4204 --
4205 --History:
4206 -- 25-MAR-2000 Rakesh Raghavan Created.
4207 -- ---------------------------------------------------------------------------
4208
4209 PROCEDURE execute_update_agreement
4210 (p_api_version_number IN NUMBER
4211 ,p_commit IN VARCHAR2
4212 ,p_init_msg_list IN VARCHAR2
4213 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
4214 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
4215 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4216 ,p_pm_product_code IN VARCHAR2
4217 )
4218 IS
4219 -- LOCAL VARIABLES
4220 l_msg_count NUMBER ;
4221 l_msg_data VARCHAR2(2000);
4222 l_function_allowed VARCHAR2(1);
4223 l_resp_id NUMBER := 0;
4224 l_return_status VARCHAR2(1);
4225 l_api_name CONSTANT VARCHAR2(30):= 'execute_update_agreement';
4226 BEGIN
4227 -- Standard begin of API savepoint
4228
4229 SAVEPOINT execute_update_agreement_pub;
4230
4231
4232 -- Standard call to check for call compatibility.
4233
4234
4235 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4236 p_api_version_number ,
4237 l_api_name ,
4238 G_PKG_NAME )
4239 THEN
4240
4241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4242
4243 END IF;
4244 l_resp_id := FND_GLOBAL.Resp_id;
4245
4246 -- Actions performed using the APIs would be subject to
4247 -- function security. If the responsibility does not allow
4248 -- such functions to be executed, the API should not proceed further
4249 -- since the user does not have access to such functions
4250
4251 /* NOT REQUIRED
4252 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4253 (p_api_version_number => p_api_version_number,
4254 p_responsibility_id => l_resp_id,
4255 p_function_name => 'PA_AF_EXECUTE_UPDATE_AGREEMENT',
4256 p_msg_count => l_msg_count,
4257 p_msg_data => l_msg_data,
4258 p_return_status => l_return_status,
4259 p_function_allowed => l_function_allowed
4260 );
4261
4262 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4263 THEN
4264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4265
4266 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4267 THEN
4268 RAISE FND_API.G_EXC_ERROR;
4269 END IF;
4270 IF l_function_allowed = 'N' THEN
4271 pa_interface_utils_pub.map_new_amg_msg
4272 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4273 ,p_msg_attribute => 'CHANGE'
4274 ,p_resize_flag => 'Y'
4275 ,p_msg_context => 'GENERAL'
4276 ,p_attribute1 => ''
4277 ,p_attribute2 => ''
4278 ,p_attribute3 => ''
4279 ,p_attribute4 => ''
4280 ,p_attribute5 => '');
4281 p_return_status := FND_API.G_RET_STS_ERROR;
4282 RAISE FND_API.G_EXC_ERROR;
4283 END IF;
4284 */
4285 -- Initialize the message table if requested.
4286
4287 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4288 THEN
4289
4290 FND_MSG_PUB.initialize;
4291
4292 END IF;
4293
4294 -- Set API return status to success
4295
4296 p_return_status := FND_API.G_RET_STS_SUCCESS;
4297
4298 -- CALL UPDATE AGREEMENT
4299 pa_agreement_pub.update_agreement
4300 (p_api_version_number => p_api_version_number
4301 ,p_commit => p_commit
4302 ,p_init_msg_list => p_init_msg_list
4303 ,p_msg_count => p_msg_count
4304 ,p_msg_data => p_msg_data
4305 ,p_return_status => p_return_status
4306 ,p_pm_product_code => p_pm_product_code
4307 ,p_agreement_in_rec => G_agreement_in_rec
4308 ,p_agreement_out_rec => G_agreement_out_rec
4309 ,p_funding_in_tbl => G_funding_in_tbl
4310 ,p_funding_out_tbl => G_funding_out_tbl);
4311
4312 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4313 THEN
4314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4315 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4316 THEN
4317 RAISE FND_API.G_EXC_ERROR;
4318 END IF;
4319
4320 IF FND_API.to_boolean( p_commit )
4321 THEN
4322 COMMIT;
4323 END IF;
4324
4325
4326 EXCEPTION
4327
4328 WHEN FND_API.G_EXC_ERROR
4329 THEN
4330 ROLLBACK TO execute_update_agreement_pub;
4331 p_return_status := FND_API.G_RET_STS_ERROR;
4332 FND_MSG_PUB.Count_And_Get
4333 ( p_count => p_msg_count ,
4334 p_data => p_msg_data );
4335
4336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4337 THEN
4338 ROLLBACK TO execute_update_agreement_pub;
4339 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4340 FND_MSG_PUB.Count_And_Get
4341 ( p_count => p_msg_count ,
4342 p_data => p_msg_data );
4343
4344 WHEN OTHERS
4345 THEN
4346 ROLLBACK TO execute_update_agreement_pub;
4347 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4348 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4349 THEN
4350 FND_MSG_PUB.add_exc_msg
4351 ( p_pkg_name => G_PKG_NAME
4352 , p_procedure_name => l_api_name
4353 , p_error_text => SUBSTR(SQLERRM, 1, 240) );
4354 FND_MSG_PUB.add;
4355 END IF;
4356 FND_MSG_PUB.Count_And_Get
4357 ( p_count => p_msg_count ,
4358 p_data => p_msg_data );
4359
4360 END execute_update_agreement;
4361
4362 -- ============================================================================
4363 --
4364 --Name: fetch_funding
4365 --Type: Procedure
4366 --Description: This procedure can be used to update an agreement
4367 -- using global PL/SQL tables.
4368 --
4369 --Called subprograms: XXX
4370 --
4371 --
4372 --
4373 --History:
4374 -- 25-MAR-2000 Rakesh Raghavan Created.
4375 -- ---------------------------------------------------------------------------
4376
4377 PROCEDURE fetch_funding
4378 (p_api_version_number IN NUMBER
4379 ,p_init_msg_list IN VARCHAR2
4380 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4381 ,p_funding_index IN NUMBER
4382 ,p_funding_id OUT NOCOPY NUMBER /*File.sql.39*/
4383 ,p_pm_funding_reference OUT NOCOPY VARCHAR2 /*File.sql.39*/
4384 )
4385 IS
4386 -- LOCAL VARIABLES
4387 l_msg_count NUMBER ;
4388 l_msg_data VARCHAR2(2000);
4389 l_function_allowed VARCHAR2(1);
4390 l_resp_id NUMBER := 0;
4391 l_return_status VARCHAR2(1);
4392 l_index NUMBER;
4393 l_api_name CONSTANT VARCHAR2(30):= 'fetch_funding';
4394 BEGIN
4395 -- Standard begin of API savepoint
4396
4397 SAVEPOINT fetch_funding_pub;
4398
4399
4400 -- Standard call to check for call compatibility.
4401
4402
4403 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4404 p_api_version_number ,
4405 l_api_name ,
4406 G_PKG_NAME )
4407 THEN
4408
4409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4410
4411 END IF;
4412 l_resp_id := FND_GLOBAL.Resp_id;
4413
4414 -- Actions performed using the APIs would be subject to
4415 -- function security. If the responsibility does not allow
4416 -- such functions to be executed, the API should not proceed further
4417 -- since the user does not have access to such functions
4418
4419 /* NOT REQUIRED
4420 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4421 (p_api_version_number => p_api_version_number,
4422 p_responsibility_id => l_resp_id,
4423 p_function_name => 'PA_AF_FETCH_FUNDING',
4424 p_msg_count => l_msg_count,
4425 p_msg_data => l_msg_data,
4426 p_return_status => l_return_status,
4427 p_function_allowed => l_function_allowed
4428 );
4429
4430 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4431 THEN
4432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4433
4434 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4435 THEN
4436 RAISE FND_API.G_EXC_ERROR;
4437 END IF;
4438 IF l_function_allowed = 'N' THEN
4439 pa_interface_utils_pub.map_new_amg_msg
4440 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4441 ,p_msg_attribute => 'CHANGE'
4442 ,p_resize_flag => 'Y'
4443 ,p_msg_context => 'GENERAL'
4444 ,p_attribute1 => ''
4445 ,p_attribute2 => ''
4446 ,p_attribute3 => ''
4447 ,p_attribute4 => ''
4448 ,p_attribute5 => '');
4449 p_return_status := FND_API.G_RET_STS_ERROR;
4450 RAISE FND_API.G_EXC_ERROR;
4451 END IF;
4452 */
4453 -- Initialize the message table if requested.
4454
4455 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4456 THEN
4457
4458 FND_MSG_PUB.initialize;
4459
4460 END IF;
4461
4462 -- Set API return status to success
4463
4464 p_return_status := FND_API.G_RET_STS_SUCCESS;
4465
4466 -- Check Funding index value, when they don't provide an index we will error out
4467 IF p_funding_index = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4468 OR p_funding_index IS NULL
4469 THEN
4470 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4471 THEN
4472 pa_interface_utils_pub.map_new_amg_msg
4473 ( p_old_message_code => 'PA_FUND_INDEX_NOT_PROV'
4474 ,p_msg_attribute => 'CHANGE'
4475 ,p_resize_flag => 'N'
4476 ,p_msg_context => 'GENERAL'
4477 ,p_attribute1 => ''
4478 ,p_attribute2 => ''
4479 ,p_attribute3 => ''
4480 ,p_attribute4 => ''
4481 ,p_attribute5 => '');
4482 END IF;
4483 p_return_status := FND_API.G_RET_STS_ERROR;
4484 RAISE FND_API.G_EXC_ERROR;
4485 ELSE
4486 l_index := p_funding_index;
4487 END IF;
4488 IF G_funding_out_tbl.exists(l_index)
4489 THEN
4490 -- assign global table fields to the outgoing parameter
4491 -- we don't want to return the big number G_PA_MISS_NUM
4492 IF G_funding_out_tbl(l_index).project_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4493 THEN
4494 p_funding_id := NULL;
4495 ELSE
4496 p_funding_id := G_funding_out_tbl(l_index).project_funding_id;
4497 END IF;
4498 p_return_status := G_funding_out_tbl(l_index).return_status;
4499 END IF;
4500
4501 EXCEPTION
4502
4503 WHEN FND_API.G_EXC_ERROR
4504 THEN
4505 ROLLBACK TO fetch_funding_pub;
4506 p_return_status := FND_API.G_RET_STS_ERROR;
4507
4508 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4509 THEN
4510 ROLLBACK TO fetch_funding_pub;
4511 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4512
4513 WHEN OTHERS
4514 THEN
4515 ROLLBACK TO fetch_funding_pub;
4516 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4517 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4518 THEN
4519 FND_MSG_PUB.add_exc_msg
4520 ( p_pkg_name => G_PKG_NAME
4521 , p_procedure_name => l_api_name );
4522 END IF;
4523
4524 END fetch_funding;
4525
4526 -- ============================================================================
4527 --
4528 --Name: clear_agreement
4529 --Type: Procedure
4530 --Description: This procedure can be used to clear the global PL/SQL
4531 -- tables that are used by a LOAD/EXECUTE/FETCH cycle.
4532 --
4533 --Called subprograms: XXX
4534 --
4535 --
4536 --
4537 --History:
4538 -- 25-MAR-2000 Rakesh Raghavan Created.
4539 -- ---------------------------------------------------------------------------
4540
4541 PROCEDURE clear_agreement
4542 IS
4543 -- LOCAL VARIABLES
4544 l_resp_id NUMBER := 0;
4545 l_return_status VARCHAR2(1);
4546 l_api_name CONSTANT VARCHAR2(30):= 'clear_agreement';
4547
4548 BEGIN
4549
4550 -- Standard begin of API savepoint
4551
4552 SAVEPOINT clear_agreement_pub;
4553
4554
4555 -- CALL THE INIT AGREEMENT PROCEDURE
4556 pa_agreement_pub.init_agreement;
4557
4558 END clear_agreement;
4559
4560 -- ============================================================================
4561 --
4562 --Name: check_delete_agreement_ok
4563 --Type: Procedure
4564 --Description: This procedure can be used to check whether it is OK
4565 -- to delete an agreement.
4566 --Called subprograms: XXX
4567 --
4568 --
4569 --
4570 --History:
4571 -- 25-MAR-2000 Rakesh Raghavan Created.
4572 -- ---------------------------------------------------------------------------
4573
4574 PROCEDURE check_delete_agreement_ok
4575 (p_api_version_number IN NUMBER
4576 ,p_commit IN VARCHAR2
4577 ,p_init_msg_list IN VARCHAR2
4578 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
4579 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
4580 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4581 ,p_pm_agreement_reference IN VARCHAR2
4582 ,p_agreement_id IN NUMBER
4583 ,p_del_agree_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
4584 )
4585 IS
4586 -- LOCAL VARIABLES
4587 l_msg_count NUMBER ;
4588 l_msg_data VARCHAR2(2000);
4589 l_function_allowed VARCHAR2(1);
4590 l_resp_id NUMBER := 0;
4591 l_out_agreement_id NUMBER ;
4592 l_return_status VARCHAR2(1);
4593 l_api_name CONSTANT VARCHAR2(30):= 'check_delete_agreement_ok';
4594
4595 BEGIN
4596
4597 -- Standard begin of API savepoint
4598
4599 SAVEPOINT check_delete_agreement_ok_pub;
4600
4601
4602 -- Standard call to check for call compatibility.
4603
4604
4605 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4606 p_api_version_number ,
4607 l_api_name ,
4608 G_PKG_NAME )
4609 THEN
4610
4611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4612
4613 END IF;
4614 l_resp_id := FND_GLOBAL.Resp_id;
4615
4616 -- Actions performed using the APIs would be subject to
4617 -- function security. If the responsibility does not allow
4618 -- such functions to be executed, the API should not proceed further
4619 -- since the user does not have access to such functions
4620
4621
4622 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4623 (p_api_version_number => p_api_version_number,
4624 p_responsibility_id => l_resp_id,
4625 p_function_name => 'PA_AF_DEL_AGMT_OK',
4626 p_msg_count => l_msg_count,
4627 p_msg_data => l_msg_data,
4628 p_return_status => l_return_status,
4629 p_function_allowed => l_function_allowed
4630 );
4631
4632 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4633 THEN
4634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4635
4636 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4637 THEN
4638 RAISE FND_API.G_EXC_ERROR;
4639 END IF;
4640 IF l_function_allowed = 'N' THEN
4641 pa_interface_utils_pub.map_new_amg_msg
4642 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4643 ,p_msg_attribute => 'CHANGE'
4644 ,p_resize_flag => 'Y'
4645 ,p_msg_context => 'GENERAL'
4646 ,p_attribute1 => ''
4647 ,p_attribute2 => ''
4648 ,p_attribute3 => ''
4649 ,p_attribute4 => ''
4650 ,p_attribute5 => '');
4651 p_return_status := FND_API.G_RET_STS_ERROR;
4652 RAISE FND_API.G_EXC_ERROR;
4653 END IF;
4654
4655 -- Initialize the message table if requested.
4656
4657 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4658 THEN
4659
4660 FND_MSG_PUB.initialize;
4661
4662 END IF;
4663
4664 -- Set API return status to success
4665
4666 p_return_status := FND_API.G_RET_STS_SUCCESS;
4667
4668
4669 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
4670
4671 -- Agreement Reference
4672 IF (p_pm_agreement_reference IS NULL)
4673 OR (p_pm_agreement_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
4674 THEN
4675 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4676 THEN
4677 pa_interface_utils_pub.map_new_amg_msg
4678 ( p_old_message_code => 'PA_AGMT_REF_IS_MISS'
4679 ,p_msg_attribute => 'CHANGE'
4680 ,p_resize_flag => 'N'
4681 ,p_msg_context => 'GENERAL'
4682 ,p_attribute1 => ''
4683 ,p_attribute2 => ''
4684 ,p_attribute3 => ''
4685 ,p_attribute4 => ''
4686 ,p_attribute5 => '');
4687 END IF;
4688 p_return_status := FND_API.G_RET_STS_ERROR;
4689 RAISE FND_API.G_EXC_ERROR;
4690 END IF;
4691 /* NOT REQUIRED
4692 -- Agreement Id
4693 IF (p_agreement_id IS NULL)
4694 OR (p_agreement_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
4695 THEN
4696 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4697 THEN
4698 pa_interface_utils_pub.map_new_amg_msg
4699 ( p_old_message_code => 'PA_AGMT_ID_IS_MISS'
4700 ,p_msg_attribute => 'CHANGE'
4701 ,p_resize_flag => 'N'
4702 ,p_msg_context => 'AGREEMENT'
4703 ,p_attribute1 => p_pm_agreement_reference
4704 ,p_attribute2 => ''
4705 ,p_attribute3 => ''
4706 ,p_attribute4 => ''
4707 ,p_attribute5 => '');
4708 END IF;
4709 p_return_status := FND_API.G_RET_STS_ERROR;
4710 RAISE FND_API.G_EXC_ERROR;
4711 END IF;
4712 */
4713 -- VALIDATE THE INCOMING PARAMETERS
4714
4715 -- TO BE CORRECTED - NIKHIL
4716 -- Agreement Reference
4717 IF pa_agreement_utils.check_valid_agreement_ref(p_agreement_reference => p_pm_agreement_reference) = 'N'
4718 THEN
4719 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4720 THEN
4721 pa_interface_utils_pub.map_new_amg_msg
4722 ( p_old_message_code => 'PA_INVD_AGMT_REF'
4723 ,p_msg_attribute => 'CHANGE'
4724 ,p_resize_flag => 'N'
4725 ,p_msg_context => 'GENERAL'
4726 ,p_attribute1 => ''
4727 ,p_attribute2 => ''
4728 ,p_attribute3 => ''
4729 ,p_attribute4 => ''
4730 ,p_attribute5 => '');
4731 END IF;
4732 p_return_status := FND_API.G_RET_STS_ERROR;
4733 RAISE FND_API.G_EXC_ERROR;
4734 END IF;
4735
4736 /* NOT REQUIRED
4737 -- Agreement Id
4738 IF pa_agreement_utils.check_valid_agreement_id(p_agreement_id => p_agreement_id) = 'N'
4739 THEN
4740 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4741 THEN
4742 pa_interface_utils_pub.map_new_amg_msg
4743 ( p_old_message_code => 'PA_INVD_AGMT_ID'
4744 ,p_msg_attribute => 'CHANGE'
4745 ,p_resize_flag => 'N'
4746 ,p_msg_context => 'AGREEMENT'
4747 ,p_attribute1 => p_pm_agreement_reference
4748 ,p_attribute2 => ''
4749 ,p_attribute3 => ''
4750 ,p_attribute4 => ''
4751 ,p_attribute5 => '');
4752 END IF;
4753 p_return_status := FND_API.G_RET_STS_ERROR;
4754 RAISE FND_API.G_EXC_ERROR;
4755 END IF;
4756 */
4757
4758 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
4759 pa_agreement_pvt.convert_ag_ref_to_id
4760 (p_pm_agreement_reference => p_pm_agreement_reference
4761 ,p_af_agreement_id => p_agreement_id
4762 ,p_out_agreement_id => l_out_agreement_id
4763 ,p_return_status => l_return_status);
4764 p_return_status := l_return_status;
4765
4766 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4767 THEN
4768 IF l_return_status = FND_API.G_RET_STS_ERROR
4769 THEN
4770 RAISE FND_API.G_EXC_ERROR;
4771 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4772 THEN
4773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4774 ELSE
4775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4776 END IF;
4777 END IF;
4778
4779 -- CHECK IF IT IS OK TO DELETE THE AGREEMENT
4780 p_del_agree_ok_flag := pa_agreement_pvt.check_delete_agreement_ok
4781 (p_agreement_id => l_out_agreement_id
4782 ,p_pm_agreement_reference => p_pm_agreement_reference);
4783
4784 IF FND_API.to_boolean( p_commit )
4785 THEN
4786 COMMIT;
4787 END IF;
4788
4789 EXCEPTION
4790 WHEN FND_API.G_EXC_ERROR
4791 THEN
4792 ROLLBACK TO check_delete_agreement_ok_pub;
4793 p_return_status := FND_API.G_RET_STS_ERROR;
4794 FND_MSG_PUB.Count_And_Get
4795 ( p_count => p_msg_count ,
4796 p_data => p_msg_data );
4797
4798 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4799 THEN
4800 ROLLBACK TO check_delete_agreement_ok_pub;
4801 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4802 FND_MSG_PUB.Count_And_Get
4803 ( p_count => p_msg_count ,
4804 p_data => p_msg_data );
4805
4806 WHEN OTHERS
4807 THEN
4808 ROLLBACK TO check_delete_agreement_ok_pub;
4809 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4810 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4811 THEN
4812 FND_MSG_PUB.add_exc_msg
4813 ( p_pkg_name => G_PKG_NAME
4814 , p_procedure_name => l_api_name );
4815
4816 END IF;
4817 FND_MSG_PUB.Count_And_Get
4818 ( p_count => p_msg_count ,
4819 p_data => p_msg_data );
4820
4821
4822 END check_delete_agreement_ok;
4823
4824 -- ============================================================================
4825 --
4826 --Name: check_add_funding_ok
4827 --Type: Procedure
4828 --Description: This procedure can be used to check whether it is OK
4829 -- to add a funding.
4830 --Called subprograms: XXX
4831 --
4832 --
4833 --
4834 --History:
4835 -- 25-MAR-2000 Rakesh Raghavan Created.
4836 -- ---------------------------------------------------------------------------
4837
4838 PROCEDURE check_add_funding_ok
4839 (p_api_version_number IN NUMBER
4840 ,p_commit IN VARCHAR2
4841 ,p_init_msg_list IN VARCHAR2
4842 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
4843 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
4844 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
4845 ,p_pm_agreement_reference IN VARCHAR2
4846 ,p_agreement_id IN NUMBER
4847 ,p_pm_funding_reference IN VARCHAR2
4848 ,p_task_id IN NUMBER
4849 ,p_project_id IN NUMBER
4850 ,p_add_funding_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
4851 ,p_funding_amt IN NUMBER
4852 ,p_project_rate_type IN VARCHAR2 DEFAULT NULL
4853 ,p_project_rate_date IN DATE DEFAULT NULL
4854 ,p_project_exchange_rate IN NUMBER DEFAULT NULL
4855 ,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
4856 ,p_projfunc_rate_date IN DATE DEFAULT NULL
4857 ,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
4858 )
4859 IS
4860 -- added for validating the funding amount
4861 -- LOCAL VARIABLES
4862 l_msg_count NUMBER ;
4863 l_msg_data VARCHAR2(2000);
4864 l_function_allowed VARCHAR2(1);
4865 l_resp_id NUMBER := 0;
4866 l_out_agreement_id NUMBER ;
4867 l_return_status VARCHAR2(1);
4868 l_api_name CONSTANT VARCHAR2(30):= 'check_add_funding_ok';
4869 l_customer_id NUMBER ;
4870 BEGIN
4871 -- Standard begin of API savepoint
4872
4873 SAVEPOINT check_add_funding_ok_pub;
4874
4875
4876 -- Standard call to check for call compatibility.
4877
4878
4879 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4880 p_api_version_number ,
4881 l_api_name ,
4882 G_PKG_NAME )
4883 THEN
4884
4885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4886
4887 END IF;
4888 l_resp_id := FND_GLOBAL.Resp_id;
4889
4890 -- This call is added for patchset K project role based security check
4891
4892 PA_INTERFACE_UTILS_PUB.G_PROJECt_ID := p_project_id;
4893
4894
4895
4896 -- Actions performed using the APIs would be subject to
4897 -- function security. If the responsibility does not allow
4898 -- such functions to be executed, the API should not proceed further
4899 -- since the user does not have access to such functions
4900
4901
4902 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
4903 (p_api_version_number => p_api_version_number,
4904 p_responsibility_id => l_resp_id,
4905 p_function_name => 'PA_AF_ADD_FUND_OK',
4906 p_msg_count => l_msg_count,
4907 p_msg_data => l_msg_data,
4908 p_return_status => l_return_status,
4909 p_function_allowed => l_function_allowed
4910 );
4911
4912 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4913 THEN
4914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4915
4916 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4917 THEN
4918 RAISE FND_API.G_EXC_ERROR;
4919 END IF;
4920 IF l_function_allowed = 'N' THEN
4921 pa_interface_utils_pub.map_new_amg_msg
4922 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
4923 ,p_msg_attribute => 'CHANGE'
4924 ,p_resize_flag => 'Y'
4925 ,p_msg_context => 'GENERAL'
4926 ,p_attribute1 => ''
4927 ,p_attribute2 => ''
4928 ,p_attribute3 => ''
4929 ,p_attribute4 => ''
4930 ,p_attribute5 => '');
4931 p_return_status := FND_API.G_RET_STS_ERROR;
4932 RAISE FND_API.G_EXC_ERROR;
4933 END IF;
4934
4935 -- Initialize the message table if requested.
4936
4937 IF FND_API.TO_BOOLEAN( p_init_msg_list )
4938 THEN
4939
4940 FND_MSG_PUB.initialize;
4941
4942 END IF;
4943
4944 -- Set API return status to success
4945
4946 p_return_status := FND_API.G_RET_STS_SUCCESS;
4947
4948 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
4949
4950 -- Agreement Reference
4951 IF (p_pm_agreement_reference IS NULL)
4952 OR (p_pm_agreement_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
4953 THEN
4954 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4955 THEN
4956 pa_interface_utils_pub.map_new_amg_msg
4957 ( p_old_message_code => 'PA_AGMT_REF_IS_MISS'
4958 ,p_msg_attribute => 'CHANGE'
4959 ,p_resize_flag => 'N'
4960 ,p_msg_context => 'GENERAL'
4961 ,p_attribute1 => ''
4962 ,p_attribute2 => ''
4963 ,p_attribute3 => ''
4964 ,p_attribute4 => ''
4965 ,p_attribute5 => '');
4966 END IF;
4967 p_return_status := FND_API.G_RET_STS_ERROR;
4968 RAISE FND_API.G_EXC_ERROR;
4969 END IF;
4970
4971 -- Project Id
4972 IF (p_project_id IS NULL)
4973 OR (p_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
4974 THEN
4975 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4976 THEN
4977 pa_interface_utils_pub.map_new_amg_msg
4978 ( p_old_message_code => 'PA_PROJ_ID_IS_MISS'
4979 ,p_msg_attribute => 'CHANGE'
4980 ,p_resize_flag => 'N'
4981 ,p_msg_context => 'FUNDING'
4982 ,p_attribute1 => ''
4983 ,p_attribute2 => p_pm_funding_reference
4984 ,p_attribute3 => ''
4985 ,p_attribute4 => ''
4986 ,p_attribute5 => '');
4987 END IF;
4988 p_return_status := FND_API.G_RET_STS_ERROR;
4989 RAISE FND_API.G_EXC_ERROR;
4990 END IF;
4991 /*
4992 -- Task Id
4993 IF (p_task_id IS NULL)
4994 OR (p_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
4995 THEN
4996 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4997 THEN
4998 pa_interface_utils_pub.map_new_amg_msg
4999 ( p_old_message_code => 'PA_TASK_ID_IS_MISS'
5000 ,p_msg_attribute => 'CHANGE'
5001 ,p_resize_flag => 'N'
5002 ,p_msg_context => 'FUNDING'
5003 ,p_attribute1 => ''
5004 ,p_attribute2 => p_pm_funding_reference
5005 ,p_attribute3 => ''
5006 ,p_attribute4 => ''
5007 ,p_attribute5 => '');
5008 END IF;
5009 p_return_status := FND_API.G_RET_STS_ERROR;
5010 RAISE FND_API.G_EXC_ERROR;
5011 END IF;
5012 */
5013 -- Funding Reference
5014 IF (p_pm_funding_reference IS NULL)
5015 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
5016 THEN
5017 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5018 THEN
5019 pa_interface_utils_pub.map_new_amg_msg
5020 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
5021 ,p_msg_attribute => 'CHANGE'
5022 ,p_resize_flag => 'N'
5023 ,p_msg_context => 'GENERAL'
5024 ,p_attribute1 => ''
5025 ,p_attribute2 => ''
5026 ,p_attribute3 => ''
5027 ,p_attribute4 => ''
5028 ,p_attribute5 => '');
5029 END IF;
5030 p_return_status := FND_API.G_RET_STS_ERROR;
5031 RAISE FND_API.G_EXC_ERROR;
5032 END IF;
5033
5034 /* NOT REQUIRED
5035 -- Funding Id
5036 IF (p_funding_id IS NULL)
5037 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
5038 THEN
5039 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5040 THEN
5041 pa_interface_utils_pub.map_new_amg_msg
5042 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
5043 ,p_msg_attribute => 'CHANGE'
5044 ,p_resize_flag => 'N'
5045 ,p_msg_context => 'GENERAL'
5046 ,p_attribute1 => ''
5047 ,p_attribute2 => ''
5048 ,p_attribute3 => ''
5049 ,p_attribute4 => ''
5050 ,p_attribute5 => '');
5051 END IF;
5052 p_return_status := FND_API.G_RET_STS_ERROR;
5053 RAISE FND_API.G_EXC_ERROR;
5054 END IF;
5055 */
5056
5057 -- VALIDATE THE INCOMING PARAMETERS
5058
5059 --TO BE CORRECTED - NIKHIL
5060 -- Agreement Reference
5061 IF pa_agreement_utils.check_valid_agreement_ref(p_agreement_reference => p_pm_agreement_reference) = 'N'
5062 THEN
5063 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5064 THEN
5065 pa_interface_utils_pub.map_new_amg_msg
5066 ( p_old_message_code => 'PA_INVD_AGMT_REF'
5067 ,p_msg_attribute => 'CHANGE'
5068 ,p_resize_flag => 'Y'
5069 ,p_msg_context => 'AGREEMENT'
5070 ,p_attribute1 => ''
5071 ,p_attribute2 => ''
5072 ,p_attribute3 => ''
5073 ,p_attribute4 => ''
5074 ,p_attribute5 => '');
5075 END IF;
5076 p_return_status := FND_API.G_RET_STS_ERROR;
5077 RAISE FND_API.G_EXC_ERROR;
5078 END IF;
5079 /* NOT REQUIRED
5080 -- Funding Reference
5081 IF pa_agreement_utils.check_valid_funding_ref(p_funding_reference => p_pm_funding_reference
5082 ,p_agreement_id => p_agreement_id) = 'N'
5083 THEN
5084 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5085 THEN
5086 pa_interface_utils_pub.map_new_amg_msg
5087 ( p_old_message_code => 'PA_INVD_FUND_REF'
5088 ,p_msg_attribute => 'CHANGE'
5089 ,p_resize_flag => 'N'
5090 ,p_msg_context => 'FUNDING'
5091 ,p_attribute1 => ''
5092 ,p_attribute2 => p_pm_funding_reference
5093 ,p_attribute3 => ''
5094 ,p_attribute4 => ''
5095 ,p_attribute5 => '');
5096 END IF;
5097 p_return_status := FND_API.G_RET_STS_ERROR;
5098 RAISE FND_API.G_EXC_ERROR;
5099 END IF;
5100 */
5101 -- CONVERT AGREEMENT REFERENCE TO AGREEMENT ID
5102 pa_agreement_pvt.convert_ag_ref_to_id
5103 (p_pm_agreement_reference => p_pm_agreement_reference
5104 ,p_af_agreement_id => p_agreement_id
5105 ,p_out_agreement_id => l_out_agreement_id
5106 ,p_return_status => l_return_status);
5107 p_return_status := l_return_status;
5108
5109 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
5110 THEN
5111 IF l_return_status = FND_API.G_RET_STS_ERROR
5112 THEN
5113 RAISE FND_API.G_EXC_ERROR;
5114 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
5115 THEN
5116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5117 ELSE
5118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5119 END IF;
5120 END IF;
5121
5122 -- TO BE CORRECTED - NIKHIL
5123 -- CHECK IF IT IS OK TO ADD FUNDING
5124
5125 SELECT a. customer_id
5126 INTO l_customer_id
5127 FROM pa_agreements_all a
5128 WHERE a.agreement_id = p_agreement_id;
5129
5130 p_add_funding_ok_flag := pa_agreement_pvt.check_add_funding_ok
5131 (p_project_id => p_project_id
5132 ,p_task_id => p_task_id
5133 ,p_agreement_id => l_out_agreement_id
5134 ,p_pm_funding_reference => p_pm_funding_reference
5135 ,p_funding_amt => p_funding_amt
5136 ,p_customer_id => l_customer_id
5137 /* MCB2 PARAMETERS BEGIN */
5138 ,p_project_rate_type => p_project_rate_type
5139 ,p_project_rate_date => p_project_rate_date
5140 ,p_project_exchange_rate => p_project_exchange_rate
5141 ,p_projfunc_rate_type => p_projfunc_rate_type
5142 ,p_projfunc_rate_date => p_projfunc_rate_date
5143 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate );
5144 /* MCB2 PARAMETERS END */
5145
5146 IF FND_API.to_boolean( p_commit )
5147 THEN
5148 COMMIT;
5149 END IF;
5150
5151 EXCEPTION
5152 WHEN FND_API.G_EXC_ERROR
5153 THEN
5154 ROLLBACK TO check_add_funding_ok_pub;
5155 p_return_status := FND_API.G_RET_STS_ERROR;
5156 FND_MSG_PUB.Count_And_Get
5157 ( p_count => p_msg_count ,
5158 p_data => p_msg_data );
5159
5160 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
5161 THEN
5162 ROLLBACK TO check_add_funding_ok_pub;
5163 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5164 FND_MSG_PUB.Count_And_Get
5165 ( p_count => p_msg_count ,
5166 p_data => p_msg_data );
5167
5168 WHEN OTHERS
5169 THEN
5170 ROLLBACK TO check_add_funding_ok_pub;
5171 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5172 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5173 THEN
5174 FND_MSG_PUB.add_exc_msg
5175 ( p_pkg_name => G_PKG_NAME
5176 , p_procedure_name => l_api_name );
5177
5178 END IF;
5179 FND_MSG_PUB.Count_And_Get
5180 ( p_count => p_msg_count ,
5181 p_data => p_msg_data );
5182
5183
5184 END check_add_funding_ok;
5185
5186 -- ============================================================================
5187 --
5188 --Name: check_delete_funding_ok
5189 --Type: Procedure
5190 --Description: This procedure can be used to check whether it is OK
5191 -- to delete a funding.
5192 --Called subprograms: XXX
5193 --
5194 --
5195 --
5196 --History:
5197 -- 25-MAR-2000 Rakesh Raghavan Created.
5198 -- ---------------------------------------------------------------------------
5199
5200 PROCEDURE check_delete_funding_ok
5201 (p_api_version_number IN NUMBER
5202 ,p_commit IN VARCHAR2
5203 ,p_init_msg_list IN VARCHAR2
5204 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
5205 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
5206 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
5207 ,p_pm_funding_reference IN VARCHAR2
5208 ,p_funding_id IN NUMBER
5209 ,p_del_funding_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
5210 )
5211 IS
5212 -- LOCAL VARIABLES
5213 l_msg_count NUMBER ;
5214 l_msg_data VARCHAR2(2000);
5215 l_function_allowed VARCHAR2(1);
5216 l_resp_id NUMBER := 0;
5217 l_out_agreement_id NUMBER ;
5218 l_return_status VARCHAR2(1);
5219 l_api_name CONSTANT VARCHAR2(30):= 'check_delete_funding_ok';
5220 l_project_id NUMBER;
5221
5222 BEGIN
5223 -- Standard begin of API savepoint
5224
5225 SAVEPOINT check_delete_funding_ok_pub;
5226
5227
5228 -- Standard call to check for call compatibility.
5229
5230
5231 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
5232 p_api_version_number ,
5233 l_api_name ,
5234 G_PKG_NAME )
5235 THEN
5236
5237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5238
5239 END IF;
5240 l_resp_id := FND_GLOBAL.Resp_id;
5241
5242 -- Get the project id from this project funding line
5243
5244 l_project_id := pa_agreement_utils.get_project_id(p_funding_id => p_funding_id,
5245 p_funding_reference => p_pm_funding_reference);
5246
5247 -- This call is added for patchset K project role based security check
5248
5249 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := l_project_id;
5250
5251 -- Actions performed using the APIs would be subject to
5252 -- function security. If the responsibility does not allow
5253 -- such functions to be executed, the API should not proceed further
5254 -- since the user does not have access to such functions
5255
5256
5257 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
5258 (p_api_version_number => p_api_version_number,
5259 p_responsibility_id => l_resp_id,
5260 p_function_name => 'PA_AF_DEL_FUND_OK',
5261 p_msg_count => l_msg_count,
5262 p_msg_data => l_msg_data,
5263 p_return_status => l_return_status,
5264 p_function_allowed => l_function_allowed
5265 );
5266
5267 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
5268 THEN
5269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5270
5271 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
5272 THEN
5273 RAISE FND_API.G_EXC_ERROR;
5274 END IF;
5275 IF l_function_allowed = 'N' THEN
5276 pa_interface_utils_pub.map_new_amg_msg
5277 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
5278 ,p_msg_attribute => 'CHANGE'
5279 ,p_resize_flag => 'Y'
5280 ,p_msg_context => 'GENERAL'
5281 ,p_attribute1 => ''
5282 ,p_attribute2 => ''
5283 ,p_attribute3 => ''
5284 ,p_attribute4 => ''
5285 ,p_attribute5 => '');
5286 p_return_status := FND_API.G_RET_STS_ERROR;
5287 RAISE FND_API.G_EXC_ERROR;
5288 END IF;
5289
5290 -- Initialize the message table if requested.
5291
5292 IF FND_API.TO_BOOLEAN( p_init_msg_list )
5293 THEN
5294
5295 FND_MSG_PUB.initialize;
5296
5297 END IF;
5298
5299 -- Set API return status to success
5300
5301 p_return_status := FND_API.G_RET_STS_SUCCESS;
5302
5303 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
5304
5305 -- Funding Reference
5306 IF (p_pm_funding_reference IS NULL)
5307 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
5308 THEN
5309 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5310 THEN
5311 pa_interface_utils_pub.map_new_amg_msg
5312 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
5313 ,p_msg_attribute => 'CHANGE'
5314 ,p_resize_flag => 'N'
5315 ,p_msg_context => 'GENERAL'
5316 ,p_attribute1 => ''
5317 ,p_attribute2 => ''
5318 ,p_attribute3 => ''
5319 ,p_attribute4 => ''
5320 ,p_attribute5 => '');
5321 END IF;
5322 p_return_status := FND_API.G_RET_STS_ERROR;
5323 RAISE FND_API.G_EXC_ERROR;
5324 END IF;
5325 /* NOT REQUIRED
5326 -- Funding Id
5327 IF (p_funding_id IS NULL)
5328 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
5329 THEN
5330 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5331 THEN
5332 pa_interface_utils_pub.map_new_amg_msg
5333 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
5334 ,p_msg_attribute => 'CHANGE'
5335 ,p_resize_flag => 'N'
5336 ,p_msg_context => 'GENERAL'
5337 ,p_attribute1 => ''
5338 ,p_attribute2 => ''
5339 ,p_attribute3 => ''
5340 ,p_attribute4 => ''
5341 ,p_attribute5 => '');
5342 END IF;
5343 p_return_status := FND_API.G_RET_STS_ERROR;
5344 RAISE FND_API.G_EXC_ERROR;
5345 END IF;
5346 */
5347
5348 -- VALIDATE THE INCOMING PARAMETERS
5349 --TO BE CORRECTED - NIKHIL
5350 -- Funding Reference
5351 IF pa_agreement_utils.check_valid_funding_ref
5352 (p_funding_reference => p_pm_funding_reference
5353 ,p_agreement_id => pa_agreement_utils.get_agreement_id(p_funding_id => p_funding_id
5354 ,p_funding_reference => p_pm_funding_reference)) = 'N'
5355 THEN
5356 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5357 THEN
5358 pa_interface_utils_pub.map_new_amg_msg
5359 ( p_old_message_code => 'PA_INVD_FUND_REF'
5360 ,p_msg_attribute => 'CHANGE'
5361 ,p_resize_flag => 'N'
5362 ,p_msg_context => 'AGREEMENT'
5363 ,p_attribute1 => ''
5364 ,p_attribute2 => ''
5365 ,p_attribute3 => ''
5366 ,p_attribute4 => ''
5367 ,p_attribute5 => '');
5368 END IF;
5369 p_return_status := FND_API.G_RET_STS_ERROR;
5370 RAISE FND_API.G_EXC_ERROR;
5371 END IF;
5372
5373 -- Funding Id
5374 IF pa_agreement_utils.check_valid_funding_id(p_agreement_id => pa_agreement_utils.get_agreement_id(p_funding_id => p_funding_id
5375 ,p_funding_reference => p_pm_funding_reference)
5376 ,p_funding_id => pa_agreement_utils.get_funding_id(p_funding_reference => p_pm_funding_reference)) = 'N'
5377 THEN
5378 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5379 THEN
5380 pa_interface_utils_pub.map_new_amg_msg
5381 ( p_old_message_code => 'PA_INVD_FUND_ID'
5382 ,p_msg_attribute => 'CHANGE'
5383 ,p_resize_flag => 'N'
5384 ,p_msg_context => 'FUNDING'
5385 ,p_attribute1 => ''
5386 ,p_attribute2 => 'p_pm_funding_reference'
5387 ,p_attribute3 => ''
5388 ,p_attribute4 => ''
5389 ,p_attribute5 => '');
5390 END IF;
5391 p_return_status := FND_API.G_RET_STS_ERROR;
5392 RAISE FND_API.G_EXC_ERROR;
5393 END IF;
5394
5395 -- CALL THE CHECK DELETE FUNDING OK PRIVATE PROCEDURE
5396
5397 p_del_funding_ok_flag := pa_agreement_pvt.check_delete_funding_ok
5398 (p_agreement_id => pa_agreement_utils.get_agreement_id(p_funding_id => p_funding_id
5399 ,p_funding_reference => p_pm_funding_reference)
5400 ,p_funding_id => pa_agreement_utils.get_funding_id(p_funding_reference => p_pm_funding_reference)
5401 ,p_pm_funding_reference => p_pm_funding_reference);
5402
5403
5404
5405 IF FND_API.to_boolean( p_commit )
5406 THEN
5407 COMMIT;
5408 END IF;
5409
5410 EXCEPTION
5411
5412 WHEN FND_API.G_EXC_ERROR
5413 THEN
5414 ROLLBACK TO check_delete_funding_ok_pub;
5415
5416 p_return_status := FND_API.G_RET_STS_ERROR;
5417
5418
5419 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
5420 THEN
5421 ROLLBACK TO check_delete_funding_ok_pub;
5422
5423 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5424
5425
5426 WHEN OTHERS
5427 THEN
5428 ROLLBACK TO check_delete_funding_ok_pub;
5429
5430 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5431
5432 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5433 THEN
5434 FND_MSG_PUB.add_exc_msg
5435 ( p_pkg_name => G_PKG_NAME
5436 , p_procedure_name => l_api_name );
5437
5438 END IF;
5439
5440
5441 END check_delete_funding_ok;
5442
5443 -- ============================================================================
5444 --
5445 --Name: check_update_funding_ok
5446 --Type: Procedure
5447 --Description: This procedure can be used to check whether it is OK
5448 -- to update a funding.
5449 --Called subprograms: XXX
5450 --
5451 --
5452 --
5453 --History:
5454 -- 25-MAR-2000 Rakesh Raghavan Created.
5455 -- ---------------------------------------------------------------------------
5456
5457 PROCEDURE check_update_funding_ok
5458 (p_api_version_number IN NUMBER
5459 ,p_commit IN VARCHAR2
5460 ,p_init_msg_list IN VARCHAR2
5461 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
5462 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
5463 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
5464 ,p_pm_product_code IN VARCHAR2
5465 ,p_pm_funding_reference IN VARCHAR2
5466 ,p_funding_id IN NUMBER
5467 ,p_pm_project_reference IN VARCHAR2
5468 ,p_project_id IN NUMBER
5469 ,p_pm_task_reference IN VARCHAR2
5470 ,p_task_id IN NUMBER
5471 ,p_pm_agreement_reference IN VARCHAR2
5472 ,p_agreement_id IN NUMBER
5473 ,p_allocated_amount IN NUMBER
5474 ,p_date_allocated IN DATE
5475 ,p_desc_flex_name IN VARCHAR2
5476 ,p_attribute_category IN VARCHAR2
5477 ,p_attribute1 IN VARCHAR2
5478 ,p_attribute2 IN VARCHAR2
5479 ,p_attribute3 IN VARCHAR2
5480 ,p_attribute4 IN VARCHAR2
5481 ,p_attribute5 IN VARCHAR2
5482 ,p_attribute6 IN VARCHAR2
5483 ,p_attribute7 IN VARCHAR2
5484 ,p_attribute8 IN VARCHAR2
5485 ,p_attribute9 IN VARCHAR2
5486 ,p_attribute10 IN VARCHAR2
5487 ,p_update_funding_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
5488 ,p_project_rate_type IN VARCHAR2 DEFAULT NULL
5489 ,p_project_rate_date IN DATE DEFAULT NULL
5490 ,p_project_exchange_rate IN NUMBER DEFAULT NULL
5491 ,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
5492 ,p_projfunc_rate_date IN DATE DEFAULT NULL
5493 ,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
5494 ,p_funding_category IN VARCHAR2 DEFAULT 'ADDITIONAL'
5495 /* Added for Bug 2483081 to include Default value - For Bug 2244796 */
5496 )
5497 IS
5498 -- LOCAL VARIABLES
5499 l_msg_count NUMBER ;
5500 l_msg_data VARCHAR2(2000);
5501 l_function_allowed VARCHAR2(1);
5502 l_resp_id NUMBER := 0;
5503 l_out_agreement_id NUMBER ;
5504 l_return_msg VARCHAR2(2000);
5505 l_validate_status VARCHAR2(1);
5506 l_return_status VARCHAR2(1);
5507 l_api_name CONSTANT VARCHAR2(30):= 'check_update_funding_ok';
5508 BEGIN
5509 -- Standard begin of API savepoint
5510
5511 SAVEPOINT check_update_funding_ok_pub;
5512
5513
5514 -- Standard call to check for call compatibility.
5515
5516
5517 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
5518 p_api_version_number ,
5519 l_api_name ,
5520 G_PKG_NAME )
5521 THEN
5522
5523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5524
5525 END IF;
5526 l_resp_id := FND_GLOBAL.Resp_id;
5527
5528 -- This call is added for patchset K project role based security check
5529
5530 PA_INTERFACE_UTILS_PUB.G_PROJECT_ID := p_project_id;
5531
5532
5533 -- Actions performed using the APIs would be subject to
5534 -- function security. If the responsibility does not allow
5535 -- such functions to be executed, the API should not proceed further
5536 -- since the user does not have access to such functions
5537
5538
5539 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
5540 (p_api_version_number => p_api_version_number,
5541 p_responsibility_id => l_resp_id,
5542 p_function_name => 'PA_AF_UPD_FUND_OK',
5543 p_msg_count => l_msg_count,
5544 p_msg_data => l_msg_data,
5545 p_return_status => l_return_status,
5546 p_function_allowed => l_function_allowed
5547 );
5548
5549 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
5550 THEN
5551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5552
5553 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
5554 THEN
5555 RAISE FND_API.G_EXC_ERROR;
5556 END IF;
5557 IF l_function_allowed = 'N' THEN
5558 pa_interface_utils_pub.map_new_amg_msg
5559 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
5560 ,p_msg_attribute => 'CHANGE'
5561 ,p_resize_flag => 'Y'
5562 ,p_msg_context => 'GENERAL'
5563 ,p_attribute1 => ''
5564 ,p_attribute2 => ''
5565 ,p_attribute3 => ''
5566 ,p_attribute4 => ''
5567 ,p_attribute5 => '');
5568 p_return_status := FND_API.G_RET_STS_ERROR;
5569 RAISE FND_API.G_EXC_ERROR;
5570 END IF;
5571
5572 -- Initialize the message table if requested.
5573
5574 IF FND_API.TO_BOOLEAN( p_init_msg_list )
5575 THEN
5576
5577 FND_MSG_PUB.initialize;
5578
5579 END IF;
5580
5581 -- Set API return status to success
5582
5583 p_return_status := FND_API.G_RET_STS_SUCCESS;
5584
5585 -- CHECK WHETHER MANDATORY INCOMING PARAMETERS EXIST
5586
5587 -- Funding Reference
5588 IF (p_pm_funding_reference IS NULL)
5589 OR (p_pm_funding_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
5590 THEN
5591 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5592 THEN
5593 pa_interface_utils_pub.map_new_amg_msg
5594 ( p_old_message_code => 'PA_FUND_REF_IS_MISS'
5595 ,p_msg_attribute => 'CHANGE'
5596 ,p_resize_flag => 'N'
5597 ,p_msg_context => 'GENERAL'
5598 ,p_attribute1 => ''
5599 ,p_attribute2 => ''
5600 ,p_attribute3 => ''
5601 ,p_attribute4 => ''
5602 ,p_attribute5 => '');
5603 END IF;
5604 p_return_status := FND_API.G_RET_STS_ERROR;
5605 RAISE FND_API.G_EXC_ERROR;
5606 END IF;
5607
5608 -- Funding Id
5609 IF (p_funding_id IS NULL)
5610 OR (p_funding_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
5611 THEN
5612 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5613 THEN
5614 pa_interface_utils_pub.map_new_amg_msg
5615 ( p_old_message_code => 'PA_FUND_ID_IS_MISS'
5616 ,p_msg_attribute => 'CHANGE'
5617 ,p_resize_flag => 'N'
5618 ,p_msg_context => 'GENERAL'
5619 ,p_attribute1 => ''
5620 ,p_attribute2 => ''
5621 ,p_attribute3 => ''
5622 ,p_attribute4 => ''
5623 ,p_attribute5 => '');
5624 END IF;
5625 p_return_status := FND_API.G_RET_STS_ERROR;
5626 RAISE FND_API.G_EXC_ERROR;
5627 END IF;
5628
5629 -- VALIDATE THE INCOMING PARAMETERS
5630
5631 -- Funding Reference
5632 IF pa_agreement_utils.check_valid_funding_ref(p_funding_reference => p_pm_funding_reference
5633 ,p_agreement_id => p_agreement_id) = 'N'
5634 THEN
5635 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5636 THEN
5637 pa_interface_utils_pub.map_new_amg_msg
5638 ( p_old_message_code => 'PA_INVD_FUND_REF'
5639 ,p_msg_attribute => 'CHANGE'
5640 ,p_resize_flag => 'Y'
5641 ,p_msg_context => 'AGREEMENT'
5642 ,p_attribute1 => ''
5643 ,p_attribute2 => ''
5644 ,p_attribute3 => ''
5645 ,p_attribute4 => ''
5646 ,p_attribute5 => '');
5647 END IF;
5648 p_return_status := FND_API.G_RET_STS_ERROR;
5649 RAISE FND_API.G_EXC_ERROR;
5650 END IF;
5651
5652 -- Agreement Id
5653 IF pa_agreement_utils.check_valid_agreement_id
5654 (p_agreement_id => p_agreement_id) = 'N'
5655 THEN
5656 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5657 THEN
5658 pa_interface_utils_pub.map_new_amg_msg
5659 ( p_old_message_code => 'PA_INVALID_FUNDING_ID'
5660 ,p_msg_attribute => 'CHANGE'
5661 ,p_resize_flag => 'Y'
5662 ,p_msg_context => 'AGREEMENT'
5663 ,p_attribute1 => ''
5664 ,p_attribute2 => ''
5665 ,p_attribute3 => ''
5666 ,p_attribute4 => ''
5667 ,p_attribute5 => '');
5668 END IF;
5669 p_return_status := FND_API.G_RET_STS_ERROR;
5670 RAISE FND_API.G_EXC_ERROR;
5671 END IF;
5672
5673 -- Flex Field Validations
5674 pa_agreement_pvt.validate_flex_fields
5675 (p_desc_flex_name => p_desc_flex_name
5676 ,p_attribute_category => p_attribute_category
5677 ,p_attribute1 => p_attribute1
5678 ,p_attribute2 => p_attribute2
5679 ,p_attribute3 => p_attribute3
5680 ,p_attribute4 => p_attribute4
5681 ,p_attribute5 => p_attribute5
5682 /** ,p_attribute6 => p_attribute7 ** commented bug 2862922 **/
5683 ,p_attribute6 => p_attribute6 /** added bug 2862922 **/
5684 ,p_attribute7 => p_attribute7 /** added bug 2862922 **/
5685 ,p_attribute8 => p_attribute8
5686 ,p_attribute9 => p_attribute9
5687 ,p_attribute10 => p_attribute10
5688 ,p_return_msg => l_return_msg
5689 ,p_validate_status => l_validate_status
5690 );
5691 IF l_validate_status = 'N'
5692 THEN
5693 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5694 THEN
5695 pa_interface_utils_pub.map_new_amg_msg
5696 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
5697 ,p_msg_attribute => 'CHANGE'
5698 ,p_resize_flag => 'N'
5699 ,p_msg_context => 'FLEX'
5700 ,p_attribute1 => l_return_msg
5701 ,p_attribute2 => ''
5702 ,p_attribute3 => ''
5703 ,p_attribute4 => ''
5704 ,p_attribute5 => '');
5705 END IF;
5706 p_return_status := FND_API.G_RET_STS_ERROR;
5707 RAISE FND_API.G_EXC_ERROR;
5708 END IF;
5709
5710
5711 -- CALL THE CHECK UPDATE FUNDING OK PRIVATE PROCEDURE
5712 p_update_funding_ok_flag := pa_agreement_pvt.check_update_funding_ok
5713 (p_project_id => p_project_id
5714 ,p_task_id => p_task_id
5715 ,p_agreement_id => p_agreement_id
5716 ,p_customer_id => pa_agreement_utils.get_customer_id
5717 (p_funding_id => p_funding_id
5718 ,p_funding_reference => p_pm_funding_reference)
5719 ,p_pm_funding_reference => p_pm_funding_reference
5720 ,p_funding_id => p_funding_id
5721 ,p_funding_amt => p_allocated_amount
5722 /* MCB2 PARAMETERS BEGIN */
5723 ,p_project_rate_type => p_project_rate_type
5724 ,p_project_rate_date => p_project_rate_date
5725 ,p_project_exchange_rate => p_project_exchange_rate
5726 ,p_projfunc_rate_type => p_projfunc_rate_type
5727 ,p_projfunc_rate_date => p_projfunc_rate_date
5728 ,p_projfunc_exchange_rate => p_projfunc_exchange_rate );
5729 /* MCB2 PARAMETERS END */
5730
5731 IF FND_API.to_boolean( p_commit )
5732 THEN
5733 COMMIT;
5734 END IF;
5735
5736 EXCEPTION
5737
5738 WHEN FND_API.G_EXC_ERROR
5739 THEN
5740 ROLLBACK TO check_update_funding_ok_pub;
5741
5742 p_return_status := FND_API.G_RET_STS_ERROR;
5743
5744
5745 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
5746 THEN
5747 ROLLBACK TO check_update_funding_ok_pub;
5748
5749 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5750
5751
5752 WHEN OTHERS
5753 THEN
5754 ROLLBACK TO check_update_funding_ok_pub;
5755
5756 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5757
5758 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5759 THEN
5760 FND_MSG_PUB.add_exc_msg
5761 ( p_pkg_name => G_PKG_NAME
5762 , p_procedure_name => l_api_name );
5763
5764 END IF;
5765
5766
5767 END check_update_funding_ok;
5768
5769
5770 PROCEDURE create_baseline_budget
5771 ( p_api_version_number IN NUMBER
5772 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5773 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
5774 ,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
5775 ,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
5776 ,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
5777 ,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5778 ,p_pm_budget_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5779 -- ,p_budget_version_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5780 ,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5781 ,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5782 -- ,p_budget_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR Always "AR"
5783 ,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5784 -- ,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5785 -- ,p_entry_method_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5786 -- ,p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5787 -- ,p_resource_list_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5788 ,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5789 ,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5790 ,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5791 ,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5792 ,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5793 ,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5794 ,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5795 ,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5796 ,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5797 ,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5798 ,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5799 ,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5800 ,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5801 ,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5802 ,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5803 ,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5804 )
5805 IS
5806 CURSOR res_info IS
5807 SELECT R1.resource_list_id resource_list_id,
5808 R1.name resource_list_name,
5809 M.resource_list_member_id resource_list_member_id
5810 FROM pa_resource_lists R1, pa_implementations I,
5811 pa_resource_list_members M
5812 WHERE R1.uncategorized_flag = 'Y'
5813 AND R1.business_group_id = I.business_group_id
5814 AND R1.resource_list_id = M.resource_list_id;
5815
5816
5817 CURSOR proj_dates IS
5818 SELECT start_date, completion_date,nvl(baseline_funding_flag,'N') baseline_funding_flag
5819 FROM pa_projects_all
5820 WHERE project_id = p_pa_project_id;
5821
5822 res_info_rec res_info%ROWTYPE;
5823 proj_dates_rec proj_dates%ROWTYPE;
5824
5825 l_project_id pa_projects_all.project_id%type;
5826 l_budget_version_id NUMBER;
5827 l_funding_level VARCHAR2(1);
5828 l_budget_entry_method_code VARCHAR2(30);
5829
5830 l_err_code NUMBER;
5831 l_resp_id NUMBER;
5832 l_err_stage VARCHAR2(120);
5833 l_status VARCHAR2(120);
5834 l_msg_count NUMBER;
5835 l_api_name VARCHAR2(50) := 'CREATE_BASELINE_BUDGET';
5836 l_err_stack VARCHAR2(250);
5837
5838 l_return_status VARCHAR2(1);
5839 l_workflow_started VARCHAR2(1);
5840
5841 -- CREATE DRAFT
5842
5843 CURSOR budget_version IS
5844 SELECT max(budget_version_id)
5845 FROM pa_budget_versions
5846 WHERE project_id = p_pa_project_id
5847 AND budget_type_code = 'AR'
5848 AND budget_status_code = 'W'
5849 AND version_number = 1;
5850
5851 -- CREATE FUNDING
5852 CURSOR funding_amount (p_resource_list_member_id number, p_start_date date, p_end_date date)
5853 is
5854 SELECT nvl(pf.task_id,0) pa_task_id,
5855 to_char(Null) pm_task_reference,
5856 to_char(Null) resource_alias,
5857 p_resource_list_member_id,
5858 DECODE(nvl(pf.task_id,0),0,p_start_date,t.start_date) budget_start_date,
5859 DECODE(nvl(pf.task_id,0),0,p_end_date,t.completion_date) budget_end_date,
5860 to_char(null) period_name,
5861 'Default Created by Projects AMG Agreement Funding' description,
5862 to_number(null) raw_cost,
5863 to_number(null) burdened_cost,
5864 sum(nvl(pf.projfunc_allocated_amount,0)) revenue,
5865 to_number(null) quantity,
5866 p_pm_product_code,
5867 p_pm_budget_reference,
5868 p_Attribute_Category,
5869 p_Attribute1,
5870 p_Attribute2,
5871 p_Attribute3,
5872 p_Attribute4,
5873 p_Attribute5,
5874 p_Attribute6,
5875 p_Attribute7,
5876 p_Attribute8,
5877 p_Attribute9,
5878 p_Attribute10,
5879 p_Attribute11,
5880 p_Attribute12,
5881 p_Attribute13,
5882 p_Attribute14,
5883 p_Attribute15,
5884 /* Bug 2866699 Added due to Fin plan impact */
5885 pf.PROJFUNC_CURRENCY_CODE,
5886 to_char(NULL),
5887 to_char(NULL),
5888 to_char(NULL),
5889 to_char(NULL),
5890 to_char(NULL),
5891 to_char(NULL),
5892 to_char(NULL),
5893 to_char(NULL),
5894 to_char(NULL),
5895 to_char(NULL),
5896 to_char(NULL),
5897 to_char(NULL),
5898 to_char(NULL),
5899 to_char(NULL),
5900 to_char(NULL),
5901 to_char(NULL),
5902 to_char(NULL)
5903 /* Bug 2866699 Added due to Fin plan impact ends here */
5904 FROM pa_project_fundings pf, pa_tasks t
5905 WHERE pf.project_id = p_pa_project_id
5906 AND pf.task_id = t.task_id(+)
5907 AND pf.budget_type_code in ('BASELINE', 'DRAFT')
5908 group by nvl(pf.task_id,0),
5909 pf.projfunc_currency_code, /*projfunc_currency_code added for bug 3078560 */
5910 DECODE(nvl(pf.task_id,0),0,p_start_date,t.start_date),
5911 DECODE(nvl(pf.task_id,0),0,p_end_date,t.completion_date);
5912 /* Modified the cursor for bug 3488706*/
5913
5914 l_budget_lines_in_tbl pa_budget_pub.budget_line_in_tbl_type;
5915
5916 l_budget_lines_out_tbl pa_budget_pub.budget_line_out_tbl_type;
5917
5918 l_budget_lines_in_rec pa_budget_pub.budget_line_in_rec_type;
5919
5920 i number := 1;
5921
5922 P_BUDGET_VERSION_NAME varchar2(10);
5923 BEGIN
5924
5925 SAVEPOINT Create_Budget_From_funding;
5926 -- Initializing the return status to success ! -- bug 3099706
5927 -- Set API return status to success
5928 p_return_status := FND_API.G_RET_STS_SUCCESS;
5929
5930 -- Standard call to check for call compatibility.
5931 IF FND_API.TO_BOOLEAN( p_init_msg_list )
5932 THEN
5933
5934 FND_MSG_PUB.initialize;
5935
5936 END IF;
5937
5938
5939 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
5940 p_api_version_number ,
5941 l_api_name ,
5942 G_PKG_NAME )
5943 THEN
5944
5945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5946
5947 END IF;
5948 l_resp_id := FND_GLOBAL.Resp_id;
5949
5950 Pa_project_pvt.Convert_pm_projref_to_id (
5951 p_pm_project_reference => p_pm_project_reference,
5952 p_pa_project_id => p_pa_project_id,
5953 p_out_project_id => l_project_id,
5954 p_return_status => l_return_status );
5955
5956 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
5957 THEN
5958 p_return_status := l_return_status;
5959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5960
5961 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
5962 THEN
5963 p_return_status := l_return_status;
5964 RAISE FND_API.G_EXC_ERROR;
5965
5966 END IF;
5967
5968 IF (PA_FUNDING_CORE.CHECK_PROJECT_TYPE(l_project_id)) = 'N' THEN
5969 p_return_status := FND_API.G_RET_STS_ERROR;
5970
5971 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
5972 THEN
5973 pa_interface_utils_pub.map_new_amg_msg
5974 ( p_old_message_code => 'PA_INVALID_PROJECT'
5975 ,p_msg_attribute => 'CHANGE'
5976 ,p_resize_flag => 'N'
5977 ,p_msg_context => 'CONTRACT'
5978 ,p_attribute1 => l_project_id
5979 ,p_attribute2 => ''
5980 ,p_attribute3 => ''
5981 ,p_attribute4 => ''
5982 ,p_attribute5 => '');
5983 END IF;
5984 END IF;
5985
5986
5987 OPEN res_info;
5988 FETCH res_info INTO res_info_rec;
5989 CLOSE res_info;
5990
5991
5992 OPEN proj_dates;
5993 FETCH proj_dates INTO proj_dates_rec;
5994 CLOSE proj_dates;
5995
5996 IF (proj_dates_rec.completion_date is null or proj_dates_rec.start_date is null) then
5997 -- Set API return status to success
5998 P_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5999
6000 pa_interface_utils_pub.map_new_amg_msg
6001 ( p_old_message_code => 'PA_BU_NO_PROJ_END_DATE'
6002 ,p_msg_attribute => 'CHANGE'
6003 ,p_resize_flag => 'N'
6004 ,p_msg_context => 'BUDG'
6005 ,p_attribute1 => l_project_id
6006 ,p_attribute2 => ''
6007 ,p_attribute3 => null
6008 ,p_attribute4 => ''
6009 ,p_attribute5 => '');
6010
6011 RAISE FND_API.G_EXC_ERROR;
6012 END IF;
6013
6014 IF proj_dates_rec.baseline_funding_flag = 'N' then
6015
6016
6017 P_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
6018
6019 pa_interface_utils_pub.map_new_amg_msg
6020 ( p_old_message_code => 'PA_NO_BASELINE_FUNDING'
6021 ,p_msg_attribute => 'CHANGE'
6022 ,p_resize_flag => 'N'
6023 ,p_msg_context => 'BUDG'
6024 ,p_attribute1 => l_project_id
6025 ,p_attribute2 => ''
6026 ,p_attribute3 => null
6027 ,p_attribute4 => ''
6028 ,p_attribute5 => '');
6029
6030 RAISE FND_API.G_EXC_ERROR;
6031
6032 END IF;
6033
6034
6035
6036 pa_billing_core.check_funding_level (
6037 x_project_id => l_project_id,
6038 x_funding_level => l_funding_level,
6039 x_err_code => l_err_code,
6040 x_err_stage => l_err_stage,
6041 x_err_stack => l_err_stack);
6042
6043 IF l_err_code <> 0 then
6044 pa_interface_utils_pub.map_new_amg_msg
6045 ( p_old_message_code => l_err_code
6046 ,p_msg_attribute => 'CHANGE'
6047 ,p_resize_flag => 'N'
6048 ,p_msg_context => 'BUDG'
6049 ,p_attribute1 => l_project_id
6050 ,p_attribute2 => ''
6051 ,p_attribute3 => l_funding_level
6052 ,p_attribute4 => ''
6053 ,p_attribute5 => '');
6054
6055 RAISE FND_API.G_EXC_ERROR;
6056
6057 END IF;
6058
6059
6060
6061 IF l_funding_level = 'P' then
6062 l_budget_entry_method_code := 'PA_PROJLVL_BASELINE';
6063 ELSIF l_funding_level = 'T' then
6064 l_budget_entry_method_code := 'PA_TASKLVL_BASELINE';
6065 END IF;
6066
6067
6068 OPen Funding_amount ( res_info_rec.resource_list_member_id,
6069 proj_dates_rec.start_date,
6070 proj_dates_rec.completion_date);
6071 loop
6072 fetch funding_amount into l_budget_lines_in_rec;
6073 if funding_amount%notfound then
6074 exit;
6075 end if;
6076 l_budget_lines_in_tbl(i) := l_budget_lines_in_rec;
6077 i := i + 1;
6078 end loop;
6079 close funding_amount;
6080
6081 -- Bug 3099706 : Set a variable to Y to indicate to pa_budget_pvt.validate_header_info
6082 -- API that the API is called from Agreement Pub during baselining of budget for an
6083 -- Autobaselined project. In this case, the called API needs to skip validations
6084 -- related to Autobaseline checking while creating a Draft Budget.
6085
6086 -- 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);
6087 PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB := 'Y';
6088
6089 -- 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);
6090 -- dbms_output.put_line('about to call create_draft_budget ... '||l_budget_entry_method_code||' '||l_project_id||' '||p_budget_version_name);
6091
6092 /* Commented for bug 4941046
6093 pa_budget_pub.create_draft_budget( p_api_version_number => p_api_version_number
6094 ,p_commit => FND_API.G_FALSE
6095 ,p_init_msg_list => FND_API.G_FALSE
6096 ,p_msg_count => p_msg_count
6097 ,p_msg_data => p_msg_data
6098 ,p_return_status => l_return_status
6099 ,p_pm_product_code => p_pm_product_code
6100 ,p_budget_version_name => p_budget_version_name
6101 ,p_pa_project_id => l_project_id
6102 ,p_pm_project_reference => p_pm_project_reference
6103 ,p_budget_type_code => 'AR'
6104 ,p_change_reason_code => Null
6105 ,p_description => 'Default Created by Projects AMG Agreement Funding'
6106 ,p_entry_method_code => l_budget_entry_method_code
6107 ,p_resource_list_name => res_info_rec.resource_list_name
6108 ,p_resource_list_id => res_info_rec.resource_list_id
6109 ,p_attribute_category => p_attribute_category
6110 ,p_attribute1 => p_attribute1
6111 ,p_attribute2 => p_attribute2
6112 ,p_attribute3 => p_attribute3
6113 ,p_attribute4 => p_attribute4
6114 ,p_attribute5 => p_attribute5
6115 ,p_attribute6 => p_attribute6
6116 ,p_attribute7 => p_attribute7
6117 ,p_attribute8 => p_attribute8
6118 ,p_attribute9 => p_attribute9
6119 ,p_attribute10 => p_attribute10
6120 ,p_attribute11 => p_attribute11
6121 ,p_attribute12 => p_attribute12
6122 ,p_attribute13 => p_attribute13
6123 ,p_attribute14 => p_attribute14
6124 ,p_attribute15 => p_attribute15
6125 ,p_budget_lines_in => l_budget_lines_in_tbl
6126 ,p_budget_lines_out => l_budget_lines_out_tbl);*/
6127
6128 --WRAPPER API CALL ADDED FOR BUG 4941046
6129 pa_fin_plan_utils.create_draft_budget_wrp( p_api_version_number => p_api_version_number
6130 ,p_commit => FND_API.G_FALSE
6131 ,p_init_msg_list => FND_API.G_FALSE
6132 ,p_msg_count => p_msg_count
6133 ,p_msg_data => p_msg_data
6134 ,p_return_status => l_return_status
6135 ,p_pm_product_code => p_pm_product_code
6136 ,p_budget_version_name => p_pm_budget_reference --Added for bug 4941046
6137 ,p_pa_project_id => l_project_id
6138 ,p_pm_project_reference => p_pm_project_reference
6139 ,p_budget_type_code => 'AR'
6140 ,p_change_reason_code => Null
6141 ,p_description => 'Default Created by Projects AMG Agreement Funding'
6142 ,p_entry_method_code => l_budget_entry_method_code
6143 ,p_resource_list_name => res_info_rec.resource_list_name
6144 ,p_resource_list_id => res_info_rec.resource_list_id
6145 ,p_attribute_category => p_attribute_category
6146 ,p_attribute1 => p_attribute1
6147 ,p_attribute2 => p_attribute2
6148 ,p_attribute3 => p_attribute3
6149 ,p_attribute4 => p_attribute4
6150 ,p_attribute5 => p_attribute5
6151 ,p_attribute6 => p_attribute6
6152 ,p_attribute7 => p_attribute7
6153 ,p_attribute8 => p_attribute8
6154 ,p_attribute9 => p_attribute9
6155 ,p_attribute10 => p_attribute10
6156 ,p_attribute11 => p_attribute11
6157 ,p_attribute12 => p_attribute12
6158 ,p_attribute13 => p_attribute13
6159 ,p_attribute14 => p_attribute14
6160 ,p_attribute15 => p_attribute15
6161 ,p_budget_lines_in => l_budget_lines_in_tbl
6162 ,p_budget_lines_out => l_budget_lines_out_tbl);
6163
6164 -- dbms_output.put_line('returned from create_draft ... status = '||l_return_status);
6165 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6166 THEN
6167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6168
6169 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
6170 THEN
6171 RAISE FND_API.G_EXC_ERROR;
6172 END IF;
6173
6174 -- dbms_output.put_line('about to call baseline_budget ... ');
6175 -- 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);
6176
6177 /* Commented for bug 4941046
6178 PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB := 'Y';
6179
6180 PA_BUDGET_PUB.BASELINE_BUDGET
6181 ( p_api_version_number => p_api_version_number
6182 ,p_commit => FND_API.G_FALSE
6183 ,p_init_msg_list => FND_API.G_FALSE
6184 ,p_msg_count => p_msg_count
6185 ,p_msg_data => p_msg_data
6186 ,p_return_status => l_return_status
6187 ,p_workflow_started => l_workflow_started
6188 ,p_pm_product_code => p_pm_product_code
6189 ,p_pa_project_id => l_project_id
6190 ,p_pm_project_reference => p_pm_project_reference
6191 ,p_budget_type_code => 'AR'
6192 ,p_mark_as_original => 'Y'
6193 );
6194 -- dbms_output.put_line('returned from BASELINE_BUDGET ... status = '||l_return_status);
6195
6196 IF (nvl(PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB,'N') = 'Y') THEN
6197 PA_FP_CONSTANTS_PKG.G_CALLED_FROM_AGREEMENT_PUB := 'N'; -- reset the value bug 3099706
6198 END IF;
6199
6200 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
6201 THEN
6202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6203
6204 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
6205 THEN
6206 RAISE FND_API.G_EXC_ERROR;
6207 END IF; End of comment for bug 4941046 */
6208
6209
6210 IF FND_API.to_boolean( p_commit )
6211 THEN
6212 COMMIT;
6213 END IF;
6214
6215 EXCEPTION
6216 WHEN FND_API.G_EXC_ERROR
6217 THEN
6218 ROLLBACK TO Create_Budget_From_funding;
6219 p_return_status := FND_API.G_RET_STS_ERROR;
6220 FND_MSG_PUB.Count_And_Get
6221 ( p_count => p_msg_count ,
6222 p_data => p_msg_data );
6223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
6224 THEN
6225 ROLLBACK TO Create_Budget_From_funding;
6226 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6227 FND_MSG_PUB.Count_And_Get
6228 ( p_count => p_msg_count ,
6229 p_data => p_msg_data );
6230
6231 WHEN OTHERS
6232 THEN
6233 ROLLBACK TO Create_Budget_From_funding;
6234 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6235 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6236 THEN
6237 FND_MSG_PUB.add_exc_msg
6238 ( p_pkg_name => G_PKG_NAME
6239 , p_procedure_name => l_api_name );
6240
6241 END IF;
6242 FND_MSG_PUB.Count_And_Get
6243 ( p_count => p_msg_count ,
6244 p_data => p_msg_data );
6245
6246
6247 END create_baseline_budget;
6248
6249
6250 end PA_AGREEMENT_PUB;