[Home] [Help]
PACKAGE BODY: APPS.GMS_MULTI_FUNDING
Source
1 PACKAGE BODY GMS_MULTI_FUNDING AS
2 /* $Header: gmsmfapb.pls 120.2.12010000.2 2008/10/30 12:30:33 rrambati ship $ */
3
4 --Variables will help debugging when exception occurs
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMS_MULTI_FUNDING';
6 G_Stage VARCHAR2(500) := NULL;
7
8 FUNCTION AWARD_PROJECT_NOT_EXISTS(X_Award_Id IN NUMBER,
9 X_Err_Code OUT NOCOPY VARCHAR2,
10 X_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
11 Award_Id_Check NUMBER(15) := 0;
12 --p_msg_count NUMBER;
13 BEGIN
14 BEGIN
15 SELECT a.project_id --Award_Project_Id
16 INTO Award_Id_Check
17 FROM gms_awards b,
18 pa_projects a
19 WHERE b.award_id = X_Award_Id
20 AND a.project_id = b.award_project_id;
21
22 X_Err_Code := 'E';
23 X_Err_Stage := 'There is already an Award Project existing for the Award '||to_char(X_Award_Id);
24
25 X_Err_Stage := 'GMS_AWD_PRJ_EXISTS_FOR_AWARD';
26 FND_MESSAGE.SET_NAME('GMS','GMS_AWD_PRJ_EXISTS_FOR_AWARD');
27 FND_MESSAGE.SET_TOKEN('AWARD_ID',to_char(X_Award_Id));
28 FND_MSG_PUB.add;
29 FND_MSG_PUB.Count_And_Get
30 (p_count => p_msg_count,
31 p_data => X_Err_Stage );
32
33 RETURN FALSE;
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 X_Err_Code := 'S';
37 RETURN TRUE;
38 WHEN TOO_MANY_ROWS THEN
39 X_Err_Code := 'U';
40 X_Err_Stage := 'There is more than one Award Project for the Award Id '||to_char(X_Award_Id);
41
42 X_Err_Stage := 'GMS_MANY_AWD_PRJ_FOR_AWARD';
43 FND_MESSAGE.SET_NAME('GMS','GMS_MANY_AWD_PRJ_FOR_AWARD');
44 FND_MESSAGE.SET_TOKEN('AWARD_ID',to_char(X_Award_Id));
45 FND_MSG_PUB.add;
46 FND_MSG_PUB.Count_And_Get
47 (p_count => p_msg_count,
48 p_data => X_Err_Stage );
49 RETURN FALSE;
50 END;
51
52 END AWARD_PROJECT_NOT_EXISTS;
53
54 FUNCTION PROJ_NAME_NUM_UNIQUE(X_Award_Project_Number IN VARCHAR2,
55 X_Award_Project_Name IN VARCHAR2,
56 X_Err_Code OUT NOCOPY VARCHAR2,
57 X_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
58 Project_Check NUMBER(2) := 0;
59 --p_msg_count NUMBER;
60 BEGIN
61 BEGIN
62 SELECT 1
63 INTO Project_Check
64 FROM PA_PROJECTS
65 WHERE NAME = X_Award_Project_Name;
66
67 IF Project_Check = 1 THEN
68 X_Err_Code := 'E';
69 X_Err_Stage := 'There is already an Award Project existing by the name '||X_Award_Project_Name;
70
71 X_Err_Stage := 'GMS_AWD_PRJNAME_EXISTS';
72 FND_MESSAGE.SET_NAME('GMS','GMS_AWD_PRJNAME_EXISTS');
73 FND_MESSAGE.SET_TOKEN('AWARD_PROJECT_NAME',X_Award_Project_Name);
74 FND_MSG_PUB.add;
75 FND_MSG_PUB.Count_And_Get
76 (p_count => p_msg_count,
77 p_data => X_Err_Stage );
78
79 RETURN FALSE;
80 END IF;
81 EXCEPTION
82 WHEN TOO_MANY_ROWS THEN
83 X_Err_Code := 'U';
84 X_Err_Stage := 'There is more than one Project already existing by the Name '||X_Award_Project_Name;
85
86 X_Err_Stage := 'GMS_AWD_PRJNAME_NOT_UNIQUE';
87 FND_MESSAGE.SET_NAME('GMS','GMS_AWD_PRJNAME_NOT_UNIQUE');
88 FND_MESSAGE.SET_TOKEN('AWARD_PROJECT_NAME',X_Award_Project_Name);
89 FND_MSG_PUB.add;
90 FND_MSG_PUB.Count_And_Get
91 (p_count => p_msg_count,
92 p_data => X_Err_Stage );
93
94 RETURN FALSE;
95 WHEN NO_DATA_FOUND THEN
96 BEGIN
97 SELECT 1
98 INTO Project_Check
99 FROM PA_PROJECTS
100 WHERE SEGMENT1 = X_Award_Project_Number;
101
102 IF Project_Check = 1 THEN
103 X_Err_Code := 'E';
104 X_Err_Stage := 'There is already an Award Project existing by the number'||X_Award_Project_Number;
105
106 X_Err_Stage := 'GMS_AWD_PRJNUM_EXISTS';
107 FND_MESSAGE.SET_NAME('GMS','GMS_AWD_PRJNUM_EXISTS');
108 FND_MESSAGE.SET_TOKEN('AWARD_PROJECT_NUMBER',X_Award_Project_Number);
109 FND_MSG_PUB.add;
110 FND_MSG_PUB.Count_And_Get
111 (p_count => p_msg_count,
112 p_data => X_Err_Stage );
113
114 RETURN FALSE;
115 END IF;
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 X_Err_Code := 'S';
119 RETURN TRUE;
120 WHEN TOO_MANY_ROWS THEN
121 X_Err_Code := 'U';
122 X_Err_Stage := 'There is more than one Project already existing by the Number'||X_Award_Project_Number;
123
124 X_Err_Stage := 'GMS_AWD_PRJNUM_NOT_UNIQUE';
125 FND_MESSAGE.SET_NAME('GMS','GMS_AWD_PRJNUM_NOT_UNIQUE');
126 FND_MESSAGE.SET_TOKEN('AWARD_PROJECT_NUMBER',X_Award_Project_Number);
127 FND_MSG_PUB.add;
131 RETURN FALSE;
128 FND_MSG_PUB.Count_And_Get
129 (p_count => p_msg_count,
130 p_data => X_Err_Stage );
132 END;
133 END;
134
135 END PROJ_NAME_NUM_UNIQUE;
136
137 PROCEDURE UPDATE_GMS_AWARDS(X_Award_Id IN NUMBER
138 ,X_Agreement_Id IN NUMBER
139 ,X_Award_Project_Id IN NUMBER) IS
140 BEGIN
141 UPDATE GMS_AWARDS
142 SET (AWARD_PROJECT_ID,
143 AGREEMENT_ID,
144 LAST_UPDATE_DATE,
145 LAST_UPDATED_BY,
146 LAST_UPDATE_LOGIN) =
147 (SELECT X_Award_Project_Id,
148 X_Agreement_Id,
149 SYSDATE,
150 fnd_global.user_id,
151 fnd_global.login_id
152 FROM dual)
153 WHERE AWARD_ID = X_Award_Id;
154
155 END UPDATE_GMS_AWARDS;
156
157 PROCEDURE UPDATE_PROJECT_ADD_INFO(X_Project_Id IN NUMBER,
158 X_IDC_Schedule_Id IN NUMBER,
159 X_IDC_Schedule_Fixed_Date IN DATE,
160 X_Labor_Invoice_Format_Id IN NUMBER,
161 X_Non_Labor_Invoice_Format_Id IN NUMBER,
162 X_Billing_Cycle_Id IN NUMBER,
163 X_Billing_Offset IN NUMBER,
164 X_Err_Code OUT NOCOPY VARCHAR2,
165 X_Err_Stage OUT NOCOPY VARCHAR2) IS
166 BEGIN
167 --dbms_output.put_line('Inside UPDATE Project Info');
168 UPDATE PA_PROJECTS_ALL
169 SET cost_ind_rate_sch_id = X_IDC_Schedule_Id,
170 cost_ind_sch_fixed_date = X_IDC_Schedule_Fixed_Date,
171 labor_invoice_format_id = X_Labor_Invoice_Format_Id,
172 non_labor_invoice_format_Id = X_Non_Labor_Invoice_Format_Id,
173 billing_cycle_id = X_Billing_Cycle_Id,
174 billing_offset = X_Billing_Offset,
175 last_UPDATE_date = sysdate,
176 last_UPDATEd_by = fnd_global.user_id,
177 last_UPDATE_login = fnd_global.login_id
178 WHERE project_id = X_Project_Id;
179
180 X_Err_Code := 'S';
181
182 IF SQL%NOTFOUND THEN
183 X_Err_Code := 'E';
184 FND_MESSAGE.SET_NAME('GMS','GMS_NO_PROJECT_UPDATED');
185 FND_MSG_PUB.add;
186 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
187 p_data => X_Err_Stage);
188 END IF;
189
190
191 -- Bug 3319191 : UPDATE Indirect schedule on task corresponding to
192 -- Award Project when Indirect schedule is changed
193 -- at award level.
194
195 UPDATE pa_tasks pt
196 SET cost_ind_rate_sch_id= X_IDC_Schedule_Id,
197 cost_ind_sch_fixed_date=X_IDC_Schedule_Fixed_Date
198 WHERE project_id = X_project_id;
199
200 --dbms_output.put_line('After UPDATE Project Info');
201 EXCEPTION
202 WHEN OTHERS THEN
203 FND_MSG_PUB.add_exc_msg
204 ( p_pkg_name => 'GMS_MULTI_FUNDING'
205 ,p_procedure_name => 'UPDATE_PROJECT_ADD_INFO'
206 );
207 x_err_stage := SQLERRM||' at stage='||g_stage||' '||X_Err_Stage;
208 RAISE;
209
210 END UPDATE_PROJECT_ADD_INFO;
211
212 -- Bug Fix for Bug 3002270
213 -- The following procedure verifies the existence of a structure
214 -- for the award project template. IF it exists, the same project structure is
215 -- used to copy to the newly created award project, IF not the following procedure
216 -- creates a structure for the award project template.
217
218 -- The structure for the award project template is mandatory FROM PA.K onwards
219 -- as project creates structure for every project template and uses the same
220 -- to create a structure while creating a new project, which is copied FROM
221 -- the template.
222
223 PROCEDURE CREATE_AWD_PROJ_TEMPLATE_STRUC(x_award_project_id IN Number
224 ,X_Err_Code OUT NOCOPY VARCHAR2
225 ,X_Err_Stage OUT NOCOPY VARCHAR2) IS
226
227 l_struct_exists varchar2(1) := 'N';
228 l_awd_proj_temp pa_projects_all%rowtype;
229 l_msg_count NUMBER;
230 l_msg_data VARCHAR2(2000);
231 l_return_status VARCHAR2(1);
232
233
234 CURSOR c_awd_proj_temp is
235 Select * FROM pa_projects_all
236 WHERE project_id = x_award_project_id;
237
238 CURSOR c_struc_exists is
239 Select 'Y' FROM pa_proj_elements
240 WHERE project_id = x_award_project_id;
241
242 BEGIN
243 --Verify whether a structure is already existing for the award project template.
244
245 OPEN c_struc_exists;
246 FETCH c_struc_exists INTO l_struct_exists;
247 CLOSE c_struc_exists;
248
249 IF l_struct_exists = 'N' THEN
250
251 -- Fetch the award project record.
252 OPEN c_awd_proj_temp;
253 FETCH c_awd_proj_temp INTO l_awd_proj_temp;
254 CLOSE c_awd_proj_temp;
255
256 -- Create structure for the award project template.
257 PA_PROJ_TASK_STRUC_PUB.CREATE_DEFAULT_STRUCTURE(
258 p_dest_project_id => x_award_project_id
259 ,p_dest_project_name => l_awd_proj_temp.name
260 ,p_dest_project_number => l_awd_proj_temp.segment1
261 ,p_dest_description => l_awd_proj_temp.description
262 ,p_struc_type => 'FINANCIAL' --creating only financial structure
263 ,x_msg_count => l_msg_count
267 IF l_Return_Status <> 'S' THEN
264 ,x_msg_data => l_msg_data
265 ,x_return_status => l_return_status );
266
268 X_Err_Stage := l_Msg_Data;
269 X_Err_Code := l_Return_Status;
270 RAISE FND_API.G_EXC_ERROR;
271 END IF;
272
273 -- Create Options for the award project template.
274 /* bug 5282308 - commented the insert */
275 /*
276 INSERT INTO pa_project_options
277 (project_id,
278 option_code,
279 last_UPDATE_date,
280 last_UPDATEd_by,
281 creation_date,
282 created_by,
283 last_UPDATE_login)
284 SELECT x_award_project_id,
285 option_code,
286 SYSDATE,
287 fnd_global.user_id,
288 SYSDATE ,
289 fnd_global.user_id,
290 fnd_global.login_id
291 FROM pa_options
292 WHERE option_code NOT IN ( 'STRUCTURES', 'STRUCTURES_SS' );
293 */
294
295 --Create structure for the award project template's task.
296 PA_PROJ_TASK_STRUC_PUB.CREATE_DEFAULT_TASK_STRUCTURE(
297 p_project_id => x_award_project_id
298 ,p_struc_type => 'FINANCIAL'
299 ,x_msg_count => l_msg_count
300 ,x_msg_data => l_msg_data
301 ,x_return_status => l_return_status );
302
303 END IF;
304
305 EXCEPTION
306 WHEN FND_API.G_EXC_ERROR THEN
307 X_ERR_CODE := l_return_status;
308 X_ERR_STAGE := l_msg_data;
309
310 WHEN OTHERS THEN
311 X_ERR_CODE := l_return_status;
312 x_err_stage := SQLERRM||' at stage='||g_stage||' '||X_Err_Stage;
313 X_ERR_STAGE := l_msg_data;
314 Raise;
315
316 END CREATE_AWD_PROJ_TEMPLATE_STRUC;
317
318 -- END of Bug Fix for Bug 3002270
319
320 -- Bug FIx 3049266
321 -- For the PA.K rollup patch certification we started making use of the customer account relationship feature.
322 -- From now on we will store the bill_to_customer_id i.e LOC customer id of an award in the bill_to_customer_id
323 -- column of the pa_project_customers.
324 -- We will not UPDATE teh record with the latest, by overriding the existing customer_id.
325 -- For this the columns bill_to_customer_id and ship_to_customer_id need to be defined as overridable.
326 -- This change can be done in the implementaitons form, but that forces us to come up with a data fix
327 -- for the existing implementations. So adding that check before creating an award. Thus we dont need any
328 -- data fix script and all the changes will be centralized in the multi funding package.
329
330 PROCEDURE MARK_FIELDS_AS_OVERRIDABLE(x_award_project_id IN NUMBER,
331 x_field_name IN VARCHAR2,
332 x_err_code OUT NOCOPY VARCHAR2,
333 x_err_stage OUT NOCOPY VARCHAR2) IS
334
335 CURSOR c_bill_to_customer_overridable IS
336 SELECT project_id
337 FROM pa_project_copy_overrides
338 WHERE project_id = x_award_project_id
339 AND field_name = x_field_name;
340
341 l_project_id NUMBER;
342 x_msg_count NUMBER;
343 x_msg_data VARCHAR2(2000);
344 x_return_status VARCHAR2(1);
345
346 BEGIN
347
348 OPEN c_bill_to_customer_overridable;
349 FETCH c_bill_to_customer_overridable INTO l_project_id;
350 CLOSE c_bill_to_customer_overridable;
351 X_err_code := 'S';
352
353 IF l_project_id IS NULL AND x_field_name = 'BILL_TO_CUSTOMER' THEN
354 PA_PROJ_TEMPLATE_SETUP_PUB.ADD_QUICK_ENTRY_FIELD( p_api_version => 1.0,
355 p_init_msg_list => FND_API.G_TRUE,
356 p_commit => FND_API.G_FALSE,
357 p_validate_only => FND_API.G_FALSE,
358 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
359 p_calling_module => 'FORM',
360 p_debug_mode => 'N',
361 p_max_msg_count => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
362 p_project_id => x_award_project_id,
363 p_sort_order => 70,
364 p_field_name => 'BILL_TO_CUSTOMER',
365 p_field_meaning => 'Bill To Customer Name',
366 p_specification => 'Primary',
367 p_limiting_value => 'Primary',
368 p_prompt => 'Bill To Customer Name',
369 p_required_flag => 'N',
370 x_return_status => x_return_status,
371 x_msg_count => x_msg_count,
372 x_msg_data => x_msg_data);
373
374 --To be more precise, call appropriate exception handler
375 IF x_Return_Status <> 'S' THEN
376 X_Err_Code := x_Return_Status;
377 X_Err_Stage := x_Msg_Data;
378 RAISE FND_API.G_EXC_ERROR;
379 END IF;
380
381 END IF;
382
383 IF l_project_id IS NULL AND x_field_name = 'SHIP_TO_CUSTOMER' THEN
384 PA_PROJ_TEMPLATE_SETUP_PUB.ADD_QUICK_ENTRY_FIELD( p_api_version => 1.0,
385 p_init_msg_list => FND_API.G_TRUE,
386 p_commit => FND_API.G_FALSE,
387 p_validate_only => FND_API.G_FALSE,
388 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
389 p_calling_module => 'FORM',
390 p_debug_mode => 'N',
391 p_max_msg_count => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
395 p_field_meaning => 'Ship To Customer Name',
392 p_project_id => x_award_project_id,
393 p_sort_order => 80,
394 p_field_name => 'SHIP_TO_CUSTOMER',
396 p_specification => 'Primary',
397 p_limiting_value => 'Primary',
398 p_prompt => 'Ship To Customer Name',
399 p_required_flag => 'N',
400 x_return_status => x_return_status,
401 x_msg_count => x_msg_count,
402 x_msg_data => x_msg_data);
403
404 --To be more precise, call appropriate exception handler
405 IF x_Return_Status <> 'S' THEN
406 X_Err_Code := x_Return_Status;
407 X_Err_Stage := x_Msg_Data;
408 RAISE FND_API.G_EXC_ERROR;
409 END IF;
410
411 END IF;
412 END;
413
414 PROCEDURE INSERT_AWARD_PROJECT(X_Customer_Id IN NUMBER,
415 X_Bill_to_customer_id IN NUMBER,
416 X_Award_Project_Name IN VARCHAR2,
417 X_Award_Project_Number IN VARCHAR2,
418 X_Award_Id IN NUMBER,
419 X_Carrying_Out_Organization_Id IN NUMBER,
420 X_IDC_Schedule_Id IN NUMBER,
421 X_IDC_Schedule_Fixed_Date IN DATE,
422 X_Labor_Invoice_Format_Id IN NUMBER,
423 X_Non_Labor_Invoice_Format_Id IN NUMBER,
424 X_Start_Date IN DATE,
425 X_End_Date IN DATE,
426 X_Close_Date IN DATE,
427 X_Person_Id IN NUMBER,
428 X_Billing_Frequency IN VARCHAR2,
429 X_Billing_cycle_id IN NUMBER,
430 X_Billing_offset IN NUMBER,
431 X_Award_Project_Id OUT NOCOPY NUMBER,
432 X_Bill_To_Address_Id OUT NOCOPY NUMBER,
433 X_Ship_To_Address_Id OUT NOCOPY NUMBER,
434 X_App_Short_Name OUT NOCOPY VARCHAR2,
435 X_Err_Code OUT NOCOPY VARCHAR2,
436 X_Err_Stage OUT NOCOPY VARCHAR2) IS
437
438 X_Product_Code VARCHAR2(30) ;
439 X_Msg_Data VARCHAR2(2000) ;
440 X_Index NUMBER(15) ;
441 X_Text VARCHAR2(2000) := NULL;
442 --X_App_Short_Name VARCHAR2(30) ;
443
444 l_bill_to_address_id NUMBER(15) ;
445 l_ship_to_address_id NUMBER(15) ;
446 l_bill_to_contact_id NUMBER(15) ;
447 l_ship_to_contact_id NUMBER(15) ;
448 l_err_code NUMBER ;
449 l_err_stage VARCHAR2(200) ;
450 l_err_stack VARCHAR2(200) ;
451 P_Return_Status VARCHAR2(1) ;
452 X_Created_From_Project_Id NUMBER(15) := 1045;
453 l_output_tax_code VARCHAR2(300) ;
454 l_retention_tax_code VARCHAR2(300) ;
455 St_Award_Project_Id NUMBER(15) ;
456
457 -- Bug fix 1563183
458 --Changes done inv_currency_code as suggested by sakthi
459 l_project_currency_code varchar2(10) := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
460
461 St_Billing_Cycle NUMBER(15);
462 St_Billing_Offset NUMBER(15);
463
464 X_Project_IN_REC PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
465 X_Project_OUT_REC PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE;
466 X_Key_Members_IN_REC PA_PROJECT_PUB.PROJECT_ROLE_REC_TYPE;
467 X_Key_Members_IN_TBL PA_PROJECT_PUB.PROJECT_ROLE_TBL_TYPE;
468 X_Class_Categories_IN_TBL PA_PROJECT_PUB.CLASS_CATEGORY_TBL_TYPE;
469 X_Tasks_IN_REC PA_PROJECT_PUB.TASK_IN_REC_TYPE;
470 X_Tasks_In_TBL PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
471 X_Tasks_Out_TBL PA_PROJECT_PUB.TASK_OUT_TBL_TYPE;
472
473 X_Workflow_Started VARCHAR2(1);
474
475 /*** Bug 3576717 **/
476 X_Deliverable_IN_TBL PA_PROJECT_PUB.DELIVERABLE_IN_TBL_TYPE;
477
478 -- Bug 3650374
479 --X_Deliverable_OUT_TBL PA_PROJECT_PUB.DELIVERABLE_OUT_TBL_TYPE;
480 --X_Deliverable_Action_OUT_TBL PA_PROJECT_PUB.ACTION_OUT_TBL_TYPE;
481
482 X_Deliverable_Action_IN_TBL PA_PROJECT_PUB.ACTION_IN_TBL_TYPE;
483
484 x_default_org_id VARCHAR2(15);
485 --Start Bug Fix 1656812
486 X_Manager_Start_Date DATE;
487 X_Manager_End_Date DATE;
488
489 -- Bug Fix 2994625
490 -- PA.K roll up has additional parameters in get_customer_info
491 -- declaring local variables for the same.
492 --
493 -- Need to pass NULL for the ship to customer as we did not mark it overridable
494 -- in the award project tempalte. Because of this Bug 3049266 is happening.
495 -- So passing NULL value to skip the error causing validation in the create_project procedure
496 -- in the pa_project_pub API.
497
498 l_bill_to_customer_id PA_PROJECT_CUSTOMERS.BILL_TO_CUSTOMER_ID%TYPE := X_BILL_TO_CUSTOMER_ID ;
499 l_ship_to_customer_id PA_PROJECT_CUSTOMERS.SHIP_TO_CUSTOMER_ID%TYPE := X_BILL_TO_CUSTOMER_ID ;
500
501 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_AWARD_PROJECT';
502
503 Cursor manager_active IS
504 SELECT START_DATE_ACTIVE,
505 END_DATE_ACTIVE
506 FROM gms_personnel
510 --For Bug 3229539 : Commented the below check as it does not eturn any rows for future award start date
507 WHERE award_id = X_Award_Id
508 AND award_role ='AM'; --For Bug 3229539
509
511 -- AND SYSDATE
512 -- BETWEEN NVL (Start_Date_Active, SYSDATE-1)
513 -- AND NVL (End_Date_Active, SYSDATE+1);
514 --END Bug fix 1656812
515
516
517 BEGIN
518 G_Stage := '(500:Select from gms_implementations)';
519 ---------------------------------------------------
520 /* Getting Org Id */
521
522 /* Getting the Default Organization Id */
523
524 select
525 to_char(nvl(org_id,-999))
526 INTO
527 x_default_org_id
528 FROM
529 gms_implementations;
530
531 --Shared Service Enhancement :
532 --Setting Org Context
533 MO_GLOBAL.SET_POLICY_CONTEXT('S',x_default_org_id);
534 --End of Shared Service Enhancement
535
536 -------------------------------------------------
537
538 --Start Bug fix 1656812
539 open manager_active;
540 fetch manager_active
541 INTO X_Manager_Start_Date, X_Manager_End_Date;
542 close manager_active;
543 --END Bug Fix 1656812
544
545
546 --dbms_output.put_line('Inside Insert Award Project ');
547 /* Get Billing Cycle FROM Billing Frequency
548 BEGIN
549 St_Billing_Offset := 0;
550 SELECT decode(X_Billing_Frequency,'ANNUALLY',365,'DAILY',1,'MONTHLY',30,'QUARTERLY',91,'WEEKLY',7)
551 INTO St_Billing_Cycle
552 FROM dual;
553 --dbms_output.put_line('B Cycle '||St_Billing_Cycle);
554 --dbms_output.put_line('B Offset '||St_Billing_Offset);
555 END;
556 */
557
558 G_Stage := '(510:Select from pa_projects)';
559 St_Billing_Offset := nvl(X_billing_offset,0) ;
560 /* Get X_Created_From_Project_Id */
561
562 BEGIN
563
564 /* Bug Fix 2447491:
565 ** After upgrading to multi org FROM non multi org, the award project
566 ** template will still have segment1 'AWD_PROJ_-999. To use the template
567 ** the code is modified.
568 **/
569 SELECT project_id
570 INTO X_Created_From_Project_Id
571 FROM PA_PROJECTS
572 WHERE project_type = 'AWARD_PROJECT'
573 AND template_flag = 'Y'
574 AND (segment1 = 'AWD_PROJ_'||x_default_org_id
575 OR segment1 = 'AWD_PROJ_-999');
576
577 X_Err_Code := 'S';
578 EXCEPTION
579 WHEN OTHERS THEN
580 X_Err_Code := 'U';
581 X_App_Short_Name := 'GMS';
582 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
583 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_AWARD_PROJECT');
584 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
585 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
586 FND_MSG_PUB.add;
587 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
588 p_data => X_Err_Stage);
589 RAISE FND_API.G_EXC_ERROR;
590 END;
591
592 G_Stage := '(520:Calling create_awd_proj_template_struc)';
593
594 -- Bug Fix for Bug 3002270
595 -- Need to verify and create a structure for the award project template.
596 -- by calling the CREATE_AWD_PROJ_TEMPLATE_STRUC.
597
598 CREATE_AWD_PROJ_TEMPLATE_STRUC
599 (x_award_project_id => X_Created_From_Project_Id
600 ,X_Err_Code => p_Return_Status
601 ,X_Err_Stage => x_err_stage);
602
603 IF p_Return_Status <> 'S' THEN
604 X_Err_Code := p_return_status;
605 X_Err_Stage := X_Err_Stage;
606 RAISE FND_API.G_EXC_ERROR;
607 END IF;
608
609 -- END of Bug Fix for Bug 3002270
610
611 -- Bug Fix for Bug 3049266
612 -- Need to mark the columns BILL_TO_CUSTOMER and SHIP_TO_CUSTOMER as overridable
613 -- The following procedure will check if these columns are overridable or not.
614 -- IF not THEN the corresponding record will be inserted INTO pa_project_copy_overrides
615 -- table.
616
617 G_Stage := '(530:Calling mark_fields_as_overridable)';
618 MARK_FIELDS_AS_OVERRIDABLE
619 (x_award_project_id => x_created_FROM_project_id
620 ,x_field_name => 'BILL_TO_CUSTOMER'
621 ,x_err_code => x_err_code
622 ,x_err_stage => x_err_stage);
623
624 IF x_err_code <> 'S' THEN
625 X_Err_Code := X_err_code;
626 X_Err_Stage := X_Err_Stage;
627 RAISE FND_API.G_EXC_ERROR;
628 END IF;
629
630 G_Stage := '(540:Calling mark_fields_as_overridable)';
631 MARK_FIELDS_AS_OVERRIDABLE
632 (x_award_project_id => x_created_FROM_project_id
633 ,x_field_name => 'SHIP_TO_CUSTOMER'
634 ,x_err_code => x_err_code
635 ,x_err_stage => x_err_stage);
636
637 --To be more precise, call appropriate exception handler
638 IF x_err_code <> 'S' THEN
639 X_Err_Code := X_err_code;
640 X_Err_Stage := X_Err_Stage;
641 RAISE FND_API.G_EXC_ERROR;
642 END IF;
643
644 G_Stage := '(550:Before Initializing the Tables)';
645 /* Initializing the Tables */
646 X_Product_Code := 'GMS';
647 X_Project_IN_REC.PM_PROJECT_REFERENCE := X_Award_Project_Number;
648 X_Project_IN_REC.PA_PROJECT_NUMBER := X_Award_Project_Number;
652 X_Project_IN_REC.CUSTOMER_ID := X_Customer_Id;
649 X_Project_IN_REC.PROJECT_NAME := X_Award_Project_Name;
650 X_Project_IN_REC.CREATED_FROM_PROJECT_ID := X_Created_From_Project_Id;
651 X_Project_IN_REC.CARRYING_OUT_ORGANIZATION_ID := X_Carrying_Out_Organization_Id;
653 X_Project_IN_REC.START_DATE := X_Start_Date;
654 X_Project_IN_REC.COMPLETION_DATE := X_End_Date;
655 X_Project_IN_REC.DISTRIBUTION_RULE := 'EVENT/EVENT';
656 X_Project_IN_REC.PROJECT_RELATIONSHIP_CODE := 'PRIMARY';
657 X_Project_IN_REC.OUTPUT_TAX_CODE := l_output_tax_code;
658 X_Project_IN_REC.RETENTION_TAX_CODE := l_retention_tax_code;
659 X_Project_IN_REC.LONG_NAME := X_Award_Project_Name; -- added Bug 2716865
660 X_Project_IN_REC.DESCRIPTION := null; -- added Bug 2716865
661
662 --Bug fix 1563183
663 --Changes done inv_currency_code as suggested by sakthi
664 X_Project_IN_REC.PROJECT_CURRENCY_CODE := l_project_currency_code;
665
666 X_Key_Members_IN_REC.PERSON_ID := X_Person_Id;
667 X_Key_Members_IN_REC.PROJECT_ROLE_TYPE := 'PROJECT MANAGER';
668 X_Key_Members_IN_REC.START_DATE := X_Manager_Start_Date; --Bug fix 1656812
669 X_Key_Members_IN_REC.END_DATE := X_Manager_End_Date; --Bug fix 1656812
670
671 X_Key_Members_IN_TBL(1) := X_Key_Members_IN_REC;
672
673 X_Tasks_IN_REC.task_name := X_Award_Project_Number||'-'||'Tsk1'; --X_Task_Name;
674 X_Tasks_IN_REC.TASK_START_DATE := X_Start_Date;
675 X_Tasks_IN_REC.TASK_COMPLETION_DATE := X_End_Date;
676 X_Tasks_IN_REC.pa_task_number := X_Award_Project_Number||'-'||'T1'; --X_Task_Number;
677 X_Tasks_IN_REC.cost_ind_rate_sch_id := X_IDC_Schedule_Id;
678 X_Tasks_IN_REC.pm_task_reference := X_Award_Project_Number;
679 X_Tasks_IN_REC.chargeable_flag := 'N';
680 X_Tasks_In_TBL(1) := X_Tasks_IN_REC;
681
682 ---------------------------------------------------------------------------------------
683
684 /* API To get customer info */
685
686 -- Bug 1672982
687 -- Bug Fix 2994625. PA.K roll up patch has get_customer_info with additional parameters.
688 -- Adding these additional parameters to the call.
689
690 G_Stage := '(560:Calling pa_customer_info.get_customer_info)';
691 PA_CUSTOMER_INFO.GET_CUSTOMER_INFO(
692 X_PROJECT_ID => NULL,
693 X_CUSTOMER_ID => X_Customer_Id,
694 X_BILL_TO_CUSTOMER_ID => l_bill_to_customer_id,
695 X_SHIP_TO_CUSTOMER_ID => l_ship_to_customer_id,
696 X_BILL_TO_ADDRESS_ID => l_bill_to_address_id,
697 X_SHIP_TO_ADDRESS_ID => l_ship_to_address_id,
698 X_BILL_TO_CONTACT_ID => l_bill_to_contact_id,
699 X_SHIP_TO_CONTACT_ID => l_ship_to_contact_id,
700 X_ERR_CODE => l_err_code,
701 X_ERR_STAGE => l_err_stage,
702 X_ERR_STACK => l_err_stack );
703
704 --dbms_output.put_line('err code '||l_err_code||' err stage '||l_err_stage);
705 --dbms_output.put_line('Got here !!!!!!!!!!!!!');
706 /* Call PROJECT API to create Award Project and One Top Task */
707
708 /* Passing out NOCOPY Bill_To_Address_Id and Ship_To_Address_Id */
709 X_Bill_To_Address_Id := l_bill_to_address_id;
710 X_Ship_To_Address_Id := l_ship_to_address_id;
711
712 -- Bug Fix 2994625. Load the Table
713 X_PROJECT_IN_REC.BILL_TO_CUSTOMER_ID := X_BILL_TO_CUSTOMER_ID;
714 X_PROJECT_IN_REC.SHIP_TO_CUSTOMER_ID := X_BILL_TO_CUSTOMER_ID;
715
716 G_Stage := '(570:Calling pa_project_pub.create_project)';
717 PA_PROJECT_PUB.CREATE_PROJECT(p_api_version_number => 1.0,
718 p_init_msg_list => 'T',
719 p_msg_count => p_msg_count,
720 p_msg_data => X_Msg_Data,
721 p_return_status => P_Return_Status,
722 p_project_in => X_Project_IN_REC,
723 p_project_out => X_Project_OUT_REC,
724 p_pm_product_code => X_Product_Code,
725 p_key_members => X_Key_Members_IN_TBL,
726 p_class_categories => X_Class_Categories_IN_TBL,
727 p_tasks_in => X_Tasks_IN_TBL,
728 p_tasks_out => X_Tasks_OUT_TBL,
729 p_workflow_started => X_Workflow_Started,
730 p_commit => FND_API.G_FALSE,
731 /** Bug 3576717 **/
732 P_deliverables_in => X_Deliverable_IN_TBL,
733 --P_deliverables_out => X_Deliverable_OUT_TBL, (3650374)
734 P_deliverable_actions_in => X_Deliverable_Action_IN_TBL
735 --P_deliverable_actions_out => X_Deliverable_Action_OUT_TBL (3650374)
736 );
737 --dbms_output.put_line('AJ03 - PA Project PUB after ');
738 --dbms_output.put_line('Return Status '||P_Return_Status);
739
740 G_Stage := '(580:After pa_project_pub.create_project call)';
741
745 X_Err_Stage := X_Msg_Data;
742 X_Err_Code := P_Return_Status;
743
744 IF P_Return_Status <> 'S' THEN
746 RAISE FND_API.G_EXC_ERROR;
747 ELSE
748 X_Award_Project_Id := X_Project_OUT_REC.PA_PROJECT_ID;
749 END IF;
750
751 --dbms_output.put_line('Project Id '||X_Project_OUT_REC.pa_project_id);
752
753 St_Award_Project_Id := X_Project_OUT_REC.pa_project_id;
754
755 --dbms_output.put_line('Project Id is '||St_Award_Project_Id);
756 --dbms_output.put_line('Schedule Id is '||X_IDC_Schedule_Id);
757 --dbms_output.put_line('Date is '||to_char(X_IDC_Schedule_Fixed_Date));
758 --dbms_output.put_line('Labor Inv Fmt Id '||X_Labor_Invoice_Format_Id);
759 --dbms_output.put_line('Non LAbor Inv Fmt Id '||X_Non_Labor_INvoice_Format_Id);
760 --dbms_output.put_line('St_Billing Cycle '||X_Billing_Cycle_Id);
761 --dbms_output.put_line('St_Billing Offset '||X_Billing_Offset);
762
763 G_Stage := '(590:Calling update_project_add_info)';
764 update_project_add_info(St_Award_Project_Id,
765 X_IDC_Schedule_Id,
766 X_IDC_Schedule_Fixed_Date,
767 X_Labor_Invoice_Format_Id,
768 X_Non_Labor_Invoice_Format_Id,
769 X_Billing_Cycle_id,
770 X_Billing_Offset,
771 P_Return_Status,
772 X_Msg_Data);
773
774 X_Err_Code := P_Return_Status;
775 IF P_Return_Status <> 'S' THEN
776 X_Err_Stage := X_Msg_Data;
777 RAISE FND_API.G_EXC_ERROR;
778 END IF;
779
780 --dbms_output.put_line('Project_Id is '||to_char(X_Project_OUT_REC.PA_PROJECT_ID) );
781 --dbms_output.put_line('Return Code is '||P_Return_Status);
782
783
784 EXCEPTION
785 WHEN FND_API.G_EXC_ERROR THEN
786 X_Award_Project_Id := -1;
787 --dbms_output.put_line('The Count From API is '||X_Msg_Count);
788 --dbms_output.put_line('Message Name is '||X_Text);
789 -- X_Err_Code := P_Return_Status;
790 -- X_Err_Stage := X_Text;
791 -- X_Err_Stage := X_Msg_Data;
792 RETURN;
793
794 -- Added when OTHERS exception for Bug:2662848
795 WHEN OTHERS THEN
796 x_err_code := 'U';
797 FND_MSG_PUB.add_exc_msg
798 ( p_pkg_name => G_PKG_NAME
799 ,p_procedure_name => l_api_name
800 ,p_error_text => substrb(SQLERRM||' at stage='||g_stage||' ',1,240)
801 );
802 FND_MSG_PUB.Count_And_Get
803 ( p_count => p_msg_count
804 ,p_data => X_Err_Stage
805 );
806 x_err_stage := SQLERRM||' at stage='||g_stage;
807 RAISE; --keeping as is
808
809 END INSERT_AWARD_PROJECT;
810
811 PROCEDURE GET_PROJ_START_AND_END_DATE(X_Installment_Id IN NUMBER,
812 X_Award_Id OUT NOCOPY NUMBER,
813 X_Award_Project_Id OUT NOCOPY NUMBER,
814 X_Project_Start_Date OUT NOCOPY DATE,
815 X_Project_End_Date OUT NOCOPY DATE,
816 X_Agreement_Id OUT NOCOPY NUMBER,
817 X_Err_Code OUT NOCOPY VARCHAR2,
818 X_Err_Stage OUT NOCOPY VARCHAR2) IS
819 Store_Project_Id NUMBER(15);
820 Store_Project_Start_Date DATE;
821 Store_Project_End_Date DATE;
822 Store_Award_Id NUMBER(15);
823 Store_Agreement_Id NUMBER(15);
824
825 --p_msg_count NUMBER;
826 BEGIN
827 SELECT
828 a.project_id, --Award Project Id for which funding is to be created
829 a.start_date,
830 nvl(a.completion_date,sysdate),
831 b.award_id,
832 b.agreement_id
833 INTO
834 Store_Project_Id,
835 Store_Project_Start_Date,
836 Store_Project_End_Date,
837 Store_Award_Id,
838 Store_Agreement_Id
839 FROM
840 PA_PROJECTS a,
841 GMS_AWARDS b,
842 GMS_INSTALLMENTS c
843 WHERE
844 c.installment_id = X_Installment_Id and
845 b.Award_Id = c.Award_Id and
846 a.project_id = b.award_project_id;
847
848 X_Err_Code := 'S';
849 X_Award_Id := Store_Award_Id;
850 X_Award_Project_Id := Store_Project_Id;
851 X_Project_Start_Date := Store_Project_Start_Date;
852 X_Project_End_Date := Store_Project_End_Date ;
853 X_Agreement_Id := Store_Agreement_Id;
854
855 EXCEPTION
856 WHEN NO_DATA_FOUND THEN
857 X_Err_Code := 'E';
858 X_Err_Stage := 'No Award Project Id found for the Installment '||to_char(X_Installment_Id);
859
860 X_Err_Stage := 'GMS_NO_AWDPRJ_ID_FOR_INST_ID';
861 FND_MESSAGE.SET_NAME('GMS','GMS_NO_AWDPRJ_ID_FOR_INST_ID');
862 FND_MESSAGE.SET_TOKEN('INSTALLMENT_ID',to_char(X_Installment_Id));
863 FND_MSG_PUB.add;
864 FND_MSG_PUB.Count_And_Get
865 ( p_count => p_msg_count,
869 X_Err_Code := 'U';
866 p_data => X_Err_Stage);
867
868 WHEN OTHERS THEN
870 X_Err_Stage := SQLERRM;
871
872 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
873 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
874 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: PROJ_START_AND_END_DATE');
875 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
876 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
877 FND_MSG_PUB.add;
878 FND_MSG_PUB.Count_And_Get
879 ( p_count => p_msg_count,
880 p_data => X_Err_Stage );
881
882 END GET_PROJ_START_AND_END_DATE;
883
884 PROCEDURE INSERT_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id IN NUMBER,
885 X_Agreement_Id IN NUMBER,
886 X_Award_Project_Id IN NUMBER,
887 X_Allocated_Amount IN NUMBER,
888 X_Date_Allocated IN DATE,
889 X_Err_Code OUT NOCOPY VARCHAR2,
890 X_Err_Stage OUT NOCOPY VARCHAR2) IS
891
892 --p_msg_count NUMBER;
893
894 x_currency_code pa_project_fundings.funding_currency_code%type := pa_currency.get_currency_code; -- Bug 2475640 : Added for 11.5 PA-J certification.
895
896 BEGIN
897 INSERT INTO PA_PROJECT_FUNDINGS(PROJECT_FUNDING_ID,
898 AGREEMENT_ID,
899 PROJECT_ID,
900 BUDGET_TYPE_CODE,
901 ALLOCATED_AMOUNT,
902 DATE_ALLOCATED,
903 -- Bug 2475640 : Added for 11.5 PA-J certification.
904 FUNDING_CURRENCY_CODE,
905 PROJECT_CURRENCY_CODE,
906 PROJFUNC_CURRENCY_CODE,
907 INVPROC_CURRENCY_CODE,
908 REVPROC_CURRENCY_CODE,
909 PROJECT_ALLOCATED_AMOUNT,
910 PROJFUNC_ALLOCATED_AMOUNT,
911 INVPROC_ALLOCATED_AMOUNT,
912 REVPROC_ALLOCATED_AMOUNT,
913 -- Bug 2475640 changes End
914 LAST_UPDATE_DATE,
915 LAST_UPDATED_BY,
916 CREATION_DATE,
917 CREATED_BY,
918 LAST_UPDATE_LOGIN)
919 VALUES(X_Project_Funding_Id,
920 X_Agreement_Id,
921 X_Award_Project_Id,
922 'DRAFT',
923 X_Allocated_Amount,
924 X_Date_Allocated,
925 -- Bug 2475640 : Added for 11.5 PA-J certification.
926 x_currency_code,
927 x_currency_code,
928 x_currency_code,
929 x_currency_code,
930 x_currency_code,
931 X_Allocated_Amount,
932 X_Allocated_Amount,
933 X_Allocated_Amount,
934 X_Allocated_Amount,
935 -- Bug 2475640 Changes End
936 SYSDATE,
937 fnd_global.user_id,
938 SYSDATE ,
939 fnd_global.user_id,
940 fnd_global.login_id);
941
942 -- Bug 2475640 : Added Following UPDATE statement to UPDATE funding_category
943
944 UPDATE pa_project_fundings proj
945 SET funding_category = 'ORIGINAL'
946 WHERE proj.project_funding_id IN (SELECT min(project_funding_id)
947 FROM pa_project_fundings
948 WHERE project_id = X_award_project_id
949 AND agreement_id = X_agreement_id
950 GROUP BY agreement_id,project_id,NVL(task_id,0))
951 AND proj.project_funding_id = X_Project_Funding_Id
952 AND funding_category is null;
953
954
955 UPDATE pa_project_fundings
956 SET funding_category='ADDITIONAL'
957 WHERE funding_category is null
958 AND project_funding_id = X_Project_Funding_Id;
959
960
961 X_Err_Code := 'S';
962 EXCEPTION
963 WHEN DUP_VAL_ON_INDEX THEN
964 X_Err_Code := 'U';
965 X_Err_Stage := SQLERRM;
966
967 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
968 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
969 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_DETAIL_PROJECT_FUNDING - 1');
970 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
971 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
972 FND_MSG_PUB.add;
973 FND_MSG_PUB.Count_And_Get
974 ( p_count => p_msg_count ,
975 p_data => X_Err_Stage );
976 WHEN OTHERS THEN
977 X_Err_Code := 'U';
978 X_Err_Stage := SQLERRM;
979 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
980 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
981 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_DETAIL_PROJECT_FUNDING - 2');
982 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
983 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
984 FND_MSG_PUB.add;
985 FND_MSG_PUB.Count_And_Get
986 ( p_count => p_msg_count ,
987 p_data => X_Err_Stage );
988
989 END INSERT_DETAIL_PROJECT_FUNDING;
990
991 FUNCTION ROW_EXISTS_IN_PA_SUMM_FUNDING(X_Agreement_Id IN NUMBER,
992 X_Award_Project_Id IN NUMBER,
993 X_Err_Code OUT NOCOPY VARCHAR2,
994 X_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN
995 IS
996 Summary_Funding_Check NUMBER(15) := 0;
997 --p_msg_count NUMBER;
998 BEGIN
999 BEGIN
1000 SELECT 1
1001 INTO Summary_Funding_Check
1002 FROM PA_SUMMARY_PROJECT_FUNDINGS
1003 WHERE Agreement_Id = X_Agreement_Id
1004 AND Project_Id = X_Award_Project_Id;
1005
1006 X_Err_Code := 'S';
1007 RETURN TRUE;
1008 EXCEPTION
1009 WHEN NO_DATA_FOUND THEN
1010 X_Err_Code := 'S';
1011 RETURN FALSE;
1012 WHEN TOO_MANY_ROWS THEN
1013 X_Err_Code := 'E';
1014 X_Err_Stage := 'There is more than one row for the Agreement '||to_char(X_Agreement_Id)||' and the Award Project '||to_char(X_Award_Project_Id);
1015 X_Err_Stage := 'GMS_SUMM_FUNDING_NOT_UNIQUE';
1016 FND_MESSAGE.SET_NAME('GMS','GMS_SUMM_FUNDING_NOT_UNIQUE');
1017 FND_MESSAGE.SET_TOKEN('AGREEMENT_ID',X_Agreement_Id);
1018 FND_MESSAGE.SET_TOKEN('AWARD_PROJECT_ID',X_Award_Project_Id);
1019 FND_MSG_PUB.add;
1020 FND_MSG_PUB.Count_And_Get
1021 ( p_count => p_msg_count ,
1022 p_data => X_Err_Stage );
1023 RETURN FALSE;
1024 WHEN OTHERS THEN
1025 X_Err_Code := 'U';
1026 X_Err_Stage := (SQLCODE||' '||SQLERRM);
1027
1028 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
1029 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
1030 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: ROW_EXISTS_IN_PA_SUMM_FUNDING');
1031 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
1032 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
1033 FND_MSG_PUB.add;
1034 FND_MSG_PUB.Count_And_Get
1035 ( p_count => p_msg_count ,
1036 p_data => X_Err_Stage );
1037 RETURN FALSE;
1038 END;
1039 END ROW_EXISTS_IN_PA_SUMM_FUNDING;
1040
1041 PROCEDURE GET_TOTAL_FUNDING_BUDGET(X_Award_Project_Id IN NUMBER,
1042 X_Total_Funding_Budget OUT NOCOPY NUMBER,
1043 X_Err_Code OUT NOCOPY VARCHAR2,
1044 X_Err_Stage OUT NOCOPY VARCHAR2) IS
1045
1046 St_Total_Baselined_Amount NUMBER(22,5) := 0;
1047 St_Total_Unbaselined_Amount NUMBER(22,5) := 0;
1048 --p_msg_count NUMBER;
1049
1050 BEGIN
1051 BEGIN
1052 SELECT
1053 nvl(sum(nvl(total_unbaselined_amount,0)),0),
1054 nvl(sum(nvl(total_baselined_amount,0)),0)
1055 INTO
1056 St_Total_Unbaselined_Amount ,
1057 St_Total_Baselined_Amount
1058 FROM
1059 PA_SUMMARY_PROJECT_FUNDINGS
1060 WHERE
1061 Project_Id = X_Award_Project_Id;
1062 END;
1063 X_Err_Code := 'S';
1064 X_Total_Funding_Budget := St_Total_Unbaselined_Amount + St_Total_Baselined_Amount;
1065 EXCEPTION
1066 WHEN NO_DATA_FOUND THEN
1067 X_Err_Code := 'S';
1068 WHEN OTHERS THEN
1069 X_Err_Code := 'U';
1070 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
1071 FND_MSG_PUB.add;
1072 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
1073 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: GET_TOTAL_FUNDING_BUDGET');
1074 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
1075 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
1076 FND_MSG_PUB.add;
1077 FND_MSG_PUB.Count_And_Get
1078 ( p_count => p_msg_count ,
1079 p_data => X_Err_Stage );
1080 END GET_TOTAL_FUNDING_BUDGET;
1081
1082 PROCEDURE GET_TOTAL_FUNDING_AMOUNT(X_Agreement_Id IN NUMBER,
1083 X_Award_Project_Id IN NUMBER,
1084 X_Total_Unbaselined_Amount OUT NOCOPY NUMBER,
1085 X_Total_Baselined_Amount OUT NOCOPY NUMBER,
1086 X_Err_Code OUT NOCOPY VARCHAR2,
1087 X_Err_Stage OUT NOCOPY VARCHAR2) IS
1088 St_Total_Unbaselined_Amount NUMBER(22,5) := 0;
1089 St_Total_Baselined_Amount NUMBER(22,5) := 0;
1090 -- p_msg_count NUMBER;
1091 BEGIN
1092 SELECT nvl(total_unbaselined_amount,0),
1093 nvl(total_baselined_amount,0)
1094 INTO St_Total_Unbaselined_Amount ,
1095 St_Total_Baselined_Amount
1096 FROM PA_SUMMARY_PROJECT_FUNDINGS
1097 WHERE Agreement_id = X_Agreement_Id
1098 AND Project_Id = X_Award_Project_Id;
1099
1100 X_Err_Code := 'S';
1101 X_Total_Unbaselined_Amount := St_Total_Unbaselined_Amount;
1102 X_Total_Baselined_Amount := St_Total_Baselined_Amount;
1103 EXCEPTION
1104 WHEN NO_DATA_FOUND THEN
1105 X_Err_Code := 'E';
1106 X_Err_Stage := 'No row found in PA_SUMMARY_PROJECT_FUNDING for Project '||to_char(X_Award_Project_Id)||' and Agreement '||to_char(X_Agreement_Id);
1107
1108 FND_MESSAGE.SET_NAME('GMS','GMS_NO_PA_SUMM_FUNDING');
1109 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: GET_TOTAL_FUNDING_AMOUNT');
1110 FND_MESSAGE.SET_TOKEN('AGREEMENT_ID',X_Agreement_Id);
1111 FND_MESSAGE.SET_TOKEN('AWARD_PROJECT_ID',X_Award_Project_Id);
1112 FND_MSG_PUB.add;
1113 FND_MSG_PUB.Count_And_Get
1114 ( p_count => p_msg_count,
1115 p_data => X_Err_Stage );
1116
1117 WHEN OTHERS THEN
1118
1119 X_Err_Code := 'U';
1120 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
1121 -- FND_MSG_PUB.add;
1122 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
1123 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: GET_TOTAL_FUNDING_BUDGET');
1124 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
1125 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
1126 FND_MSG_PUB.add;
1127 FND_MSG_PUB.Count_And_Get
1128 ( p_count => p_msg_count ,
1129 p_data => X_Err_Stage );
1130
1131 END GET_TOTAL_FUNDING_AMOUNT;
1132
1133 PROCEDURE UPDATE_PA_SUMM_PROJECT_FUNDING(X_Agreement_Id IN NUMBER,
1134 X_Award_Project_Id IN NUMBER,
1135 X_Total_Unbaselined_Amount IN NUMBER,
1136 X_Err_Code OUT NOCOPY VARCHAR2,
1137 X_Err_Stage OUT NOCOPY VARCHAR2) IS
1138 --p_msg_count NUMBER;
1139 BEGIN
1140 UPDATE PA_SUMMARY_PROJECT_FUNDINGS
1141 SET TOTAL_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
1142 -- Bug 2475640 : Added for 11.5 PA-J certification.
1143 PROJECT_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
1144 PROJFUNC_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
1145 INVPROC_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
1146 REVPROC_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
1147 -- Bug 2475640 changes End
1148 LAST_UPDATE_DATE = SYSDATE,
1149 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1150 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1151 WHERE AGREEMENT_ID = X_Agreement_Id
1152 AND PROJECT_ID = X_Award_Project_Id;
1153
1154 X_Err_Code := 'S';
1155
1156 IF SQL%NOTFOUND THEN
1157 X_Err_Code := 'E';
1158 X_Err_Stage := 'Could not find a row to UPDATE for Agreement ' ||to_char(X_Agreement_Id)||' and Project '||to_char(X_Award_Project_Id);
1159 X_Err_Stage := 'GMS_NO_PA_SUMM_FUNDING';
1160 --FND_MSG_PUB.add;
1161 FND_MESSAGE.SET_NAME('GMS','GMS_NO_PA_SUMM_FUNDING');
1162 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_PA_SUMM_PROJECT_FUNDING');
1163 FND_MESSAGE.SET_TOKEN('AGREEMENT_ID',X_Agreement_Id);
1164 FND_MESSAGE.SET_TOKEN('AWARD_PROJECT_ID',X_Award_Project_Id);
1165 FND_MSG_PUB.add;
1166 FND_MSG_PUB.Count_And_Get
1167 ( p_count => p_msg_count ,
1168 p_data => X_Err_Stage );
1169 END IF;
1170
1171 END UPDATE_PA_SUMM_PROJECT_FUNDING;
1172
1173 PROCEDURE INSERT_SUMMARY_PROJECT_FUNDING(X_Agreement_Id IN NUMBER,
1174 X_Award_Project_Id IN NUMBER,
1175 X_Total_Unbaselined_Amount IN NUMBER,
1176 X_Err_Code OUT NOCOPY VARCHAR2,
1177 X_Err_Stage OUT NOCOPY VARCHAR2) IS
1178 --p_msg_count NUMBER;
1179
1180
1181 x_currency_code pa_summary_project_fundings.funding_currency_code%type := pa_currency.get_currency_code; -- Bug 2475640 : Added for 11.5 PA-J certification.
1182
1183 BEGIN
1184 INSERT INTO
1185 PA_SUMMARY_PROJECT_FUNDINGS(AGREEMENT_ID,
1186 PROJECT_ID,
1187 TOTAL_UNBASELINED_AMOUNT,
1188 -- Bug 2475640 : Added for 11.5 PA-J certification.
1189 FUNDING_CURRENCY_CODE,
1190 PROJECT_CURRENCY_CODE,
1191 PROJFUNC_CURRENCY_CODE,
1192 INVPROC_CURRENCY_CODE,
1193 REVPROC_CURRENCY_CODE,
1194 PROJECT_UNBASELINED_AMOUNT,
1195 PROJFUNC_UNBASELINED_AMOUNT,
1196 INVPROC_UNBASELINED_AMOUNT,
1197 REVPROC_UNBASELINED_AMOUNT,
1198 -- Bug 2475640 Changes END
1199 LAST_UPDATE_DATE,
1200 LAST_UPDATED_BY,
1201 CREATION_DATE,
1202 CREATED_BY,
1203 LAST_UPDATE_LOGIN)
1204 VALUES(X_Agreement_Id,
1205 X_Award_Project_Id,
1206 X_Total_Unbaselined_Amount,
1207 -- Bug 2475640 : Added for 11.5 PA-J certification.
1208 x_currency_code,
1209 x_currency_code,
1210 x_currency_code,
1211 x_currency_code,
1212 x_currency_code,
1213 X_Total_Unbaselined_Amount,
1214 X_Total_Unbaselined_Amount,
1215 X_Total_Unbaselined_Amount,
1216 X_Total_Unbaselined_Amount,
1217 -- Bug 2475640 Changes END
1218 SYSDATE,
1219 fnd_global.user_id,
1220 SYSDATE ,
1221 fnd_global.user_id,
1222 fnd_global.login_id);
1223 X_Err_Code := 'S';
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 X_Err_Code := 'U';
1227 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
1228 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
1229 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_SUMMARY_PROJECT_FUNDING ');
1230 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE); -- OERRNO should be SQLCODE
1231 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM); -- OERRM should have been SQLERRM
1232 FND_MSG_PUB.add;
1233 FND_MSG_PUB.Count_And_Get
1234 ( p_count => p_msg_count ,
1235 p_data => X_Err_Stage );
1236
1237 END INSERT_SUMMARY_PROJECT_FUNDING;
1238
1239 FUNCTION DRAFT_BUDGET_EXISTS(X_Award_Project_Id IN NUMBER,
1240 X_Err_Code OUT NOCOPY VARCHAR2,
1241 X_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
1242 Draft_Budget_Check NUMBER(15) := 0;
1243 --p_msg_count NUMBER;
1244 BEGIN
1245 BEGIN
1246 SELECT 1
1247 INTO
1248 Draft_Budget_Check
1249 FROM PA_BUDGET_VERSIONS
1250 WHERE
1251 Project_Id = X_Award_Project_Id and
1252 budget_type_code = 'AR' and
1253 budget_status_code in ('W' , 'S');
1254
1255 X_Err_Code := 'S';
1256 RETURN TRUE;
1257 EXCEPTION
1258 WHEN NO_DATA_FOUND THEN
1259 X_Err_Code := 'S';
1260 RETURN FALSE;
1261 WHEN TOO_MANY_ROWS THEN
1262 X_Err_Code := 'E';
1263 X_Err_Stage := 'There is more than one draft Budget for the Project '||to_char(X_Award_Project_Id);
1264 --dbms_output.put_line(X_Err_Stage);
1265 FND_MESSAGE.SET_NAME('GMS','GMS_DRAFT_REV_BDGT_EXISTS');
1266 FND_MESSAGE.SET_TOKEN('AWARD_PROJECT_ID',X_Award_Project_Id);
1267 FND_MSG_PUB.add;
1268 FND_MSG_PUB.Count_And_Get
1269 ( p_count => p_msg_count ,
1270 p_data => X_Err_Stage );
1271
1272 RETURN FALSE;
1273 WHEN OTHERS THEN
1274 X_Err_Code := 'U';
1275 X_Err_Stage := (SQLCODE||' '||SQLERRM) ;
1276 --dbms_output.put_line('Others :'||X_Err_Stage);
1277 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
1278 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
1279 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: DRAFT_BUDGET_EXISTS ');
1280 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
1281 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
1282 FND_MSG_PUB.add;
1283 FND_MSG_PUB.Count_And_Get
1284 ( p_count => p_msg_count ,
1285 p_data => X_Err_Stage );
1286 RETURN FALSE;
1287 END;
1288 END DRAFT_BUDGET_EXISTS;
1289
1290
1291 PROCEDURE UPDATE_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id IN NUMBER,
1292 X_Old_Allocated_Amount IN NUMBER,
1293 X_New_Allocated_Amount IN NUMBER,
1294 X_Old_Date_Allocated IN DATE,
1295 X_New_Date_Allocated IN DATE,
1296 X_Err_Code OUT NOCOPY VARCHAR2,
1297 X_Err_Stage OUT NOCOPY VARCHAR2) IS
1298 St_Budget_Type_Code VARCHAR2(30);
1299
1300 --p_msg_count NUMBER;
1301 BEGIN
1302 BEGIN
1303 Select
1304 BUDGET_TYPE_CODE
1305 INTO
1306 St_Budget_Type_Code
1307 FROM
1308 PA_PROJECT_FUNDINGS
1309 WHERE
1310 PROJECT_FUNDING_ID = X_Project_Funding_Id;
1311 X_Err_Code := 'S';
1312 EXCEPTION
1313 WHEN NO_DATA_FOUND THEN
1314 X_Err_Code := 'E';
1315 X_Err_Stage := ('No row Found for Project_funding_id '||X_Project_Funding_Id);
1316 X_Err_Stage := 'GMS_NO_ROW_FOR_PA_FUNDING_ID';
1317 FND_MESSAGE.SET_NAME('GMS','GMS_NO_ROW_FOR_PA_FUNDING');
1318 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_DETAIL_PROJECT_FUNDING - 1');
1319 FND_MESSAGE.SET_TOKEN('PROJECT_FUNDING_ID',to_char(X_Project_Funding_Id));
1320 FND_MSG_PUB.add;
1321 FND_MSG_PUB.Count_And_Get
1322 ( p_count => p_msg_count ,
1323 p_data => X_Err_Stage );
1324 RETURN;
1325 END ;
1326
1327 --IF St_Budget_Type_Code = 'DRAFT' THEN
1328 IF X_Old_Allocated_Amount <> X_New_Allocated_Amount THEN
1329
1330 UPDATE PA_PROJECT_FUNDINGS
1331 SET ALLOCATED_AMOUNT = X_New_Allocated_Amount,
1332 -- Bug 2475640 : Added for 11.5 PA-J certification.
1333 PROJECT_ALLOCATED_AMOUNT = X_New_Allocated_Amount,
1334 PROJFUNC_ALLOCATED_AMOUNT = X_New_Allocated_Amount,
1335 INVPROC_ALLOCATED_AMOUNT = X_New_Allocated_Amount,
1336 REVPROC_ALLOCATED_AMOUNT = X_New_Allocated_Amount,
1337 -- Bug 2475640 Changes End
1338 LAST_UPDATE_DATE = SYSDATE ,
1339 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1340 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1341 WHERE PROJECT_FUNDING_ID = X_Project_Funding_Id;
1342
1343 X_Err_Code := 'S';
1344
1345 IF SQL%NOTFOUND THEN
1346 X_Err_Code := 'E';
1347 X_Err_Stage := 'Could not find a row to UPDATE for Project Funding '||to_char(X_Project_Funding_Id) ;
1348 X_Err_Stage := 'GMS_NO_ROW_FOR_PA_FUNDING_ID';
1349 FND_MESSAGE.SET_NAME('GMS','GMS_NO_ROW_FOR_PA_FUNDING');
1350 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_DETAIL_PROJECT_FUNDING - 2');
1351 FND_MESSAGE.SET_TOKEN('PROJECT_FUNDING_ID',to_char(X_Project_Funding_Id));
1352 FND_MSG_PUB.add;
1353 FND_MSG_PUB.Count_And_Get
1354 ( p_count => p_msg_count ,
1355 p_data => X_Err_Stage );
1356 RETURN;
1357 END IF;
1358 END IF;
1359 IF X_Old_Date_Allocated <> X_New_Date_Allocated THEN
1360
1361 UPDATE PA_PROJECT_FUNDINGS
1362 SET DATE_ALLOCATED = X_New_Date_Allocated,
1363 LAST_UPDATE_DATE = SYSDATE ,
1364 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1365 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1366 WHERE PROJECT_FUNDING_ID = X_Project_Funding_Id;
1367
1368 X_Err_Code := 'S';
1369
1370 IF SQL%NOTFOUND THEN
1371 X_Err_Code := 'E';
1372 X_Err_Stage := 'Could not find a row to UPDATE for Project Funding '||to_char(X_Project_Funding_Id) ;
1373 X_Err_Stage := 'GMS_NO_ROW_FOR_PA_FUNDING_ID';
1374 FND_MESSAGE.SET_NAME('GMS','GMS_NO_ROW_FOR_PA_FUNDING');
1375 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_DETAIL_PROJECT_FUNDING - 3');
1376 FND_MESSAGE.SET_TOKEN('PROJECT_FUNDING_ID',to_char(X_Project_Funding_Id));
1377 FND_MSG_PUB.add;
1378 FND_MSG_PUB.Count_And_Get
1379 ( p_count => p_msg_count ,
1380 p_data => X_Err_Stage );
1381 END IF;
1382 END IF;
1383 --ELSE
1384 -- X_Err_Code := 'E';
1385 -- X_Err_Stage := 'The corresponding PA_PROJECT_FUNDING row no longer has a BUDGET TYPE of Draft';
1386 --END IF;
1387
1388 -- Added Exception for Bug:2662848
1389 Exception
1390 WHEN OTHERS THEN
1391 X_Err_Code := 'U';
1392 X_Err_Stage := SQLERRM||' at stage='||g_stage||' '||X_Err_Stage;
1393
1394 FND_MSG_PUB.add_exc_msg
1395 ( p_pkg_name => 'GMS_MULTI_FUNDING'
1396 ,p_procedure_name => 'UPDATE_DETAIL_PROJECT_FUNDING'
1397 );
1398 FND_MSG_PUB.Count_And_Get
1399 ( p_count => p_msg_count
1400 ,p_data => X_Err_Stage
1401 );
1402 RAISE;
1403 END UPDATE_DETAIL_PROJECT_FUNDING;
1404
1405 PROCEDURE DELETE_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id IN NUMBER,
1406 X_Err_Code OUT NOCOPY VARCHAR2,
1407 X_Err_Stage OUT NOCOPY VARCHAR2) IS
1408 --p_msg_count NUMBER;
1409 BEGIN
1410 delete
1411 FROM
1412 PA_PROJECT_FUNDINGS
1413 WHERE
1414 PROJECT_FUNDING_ID = X_Project_Funding_Id;
1415 -- and BUDGET_TYPE_CODE = 'DRAFT';
1416 IF SQL%ROWCOUNT = 0 THEN
1417 X_Err_Code := 'E';
1418 X_Err_Stage := 'GMS_NO_ROW_FOR_PA_FUNDING_ID';
1419 FND_MESSAGE.SET_NAME('GMS','GMS_NO_ROW_FOR_PA_FUNDING');
1420 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: DELETE_DETAIL_PROJECT_FUNDING');
1421 FND_MESSAGE.SET_TOKEN('PROJECT_FUNDING_ID',to_char(X_Project_Funding_Id));
1422 FND_MSG_PUB.add;
1423 FND_MSG_PUB.Count_And_Get
1424 ( p_count => p_msg_count ,
1425 p_data => X_Err_Stage );
1426 END IF;
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 FND_MSG_PUB.add_exc_msg
1430 ( p_pkg_name => 'GMS_MULTI_FUNDING'
1431 ,p_procedure_name => 'DELETE_DETAIL_PROJECT_FUNDING'
1432 );
1433 FND_MSG_PUB.Count_And_Get
1434 ( p_count => p_msg_count
1435 ,p_data => X_Err_Stage
1436 );
1437 RAISE;
1438
1439 END DELETE_DETAIL_PROJECT_FUNDING;
1440
1441 PROCEDURE DELETE_SUMMARY_PROJECT_FUNDING(X_Agreement_Id IN NUMBER,
1442 X_Award_Project_Id IN NUMBER,
1443 X_Err_Code OUT NOCOPY VARCHAR2,
1444 X_Err_Stage OUT NOCOPY VARCHAR2) IS
1445 --p_msg_count NUMBER;
1446 BEGIN
1447 delete
1448 FROM
1449 PA_SUMMARY_PROJECT_FUNDINGS
1450 WHERE
1451 AGREEMENT_ID = X_Agreement_Id and
1452 PROJECT_ID = X_Award_Project_Id;
1453 IF SQL%ROWCOUNT = 0 THEN
1454 X_Err_Code := 'E';
1455 X_Err_Stage := 'There were no rows deleted FROM PA_SUMMARY_PROJECT_FUNDINGS';
1456 X_Err_Stage := 'GMS_NO_SUMM_FUNDING_DELETED';
1457 FND_MESSAGE.SET_NAME('GMS','GMS_NO_SUMM_FUNDING_DELETED');
1458 FND_MSG_PUB.add;
1459 FND_MSG_PUB.Count_And_Get
1460 ( p_count => p_msg_count ,
1461 p_data => X_Err_Stage );
1462 END IF;
1463 END DELETE_SUMMARY_PROJECT_FUNDING;
1464
1465 PROCEDURE DELETE_BASELINED_VERSIONS(X_Award_Project_Id IN NUMBER,
1466 X_Err_Code OUT NOCOPY VARCHAR2,
1467 X_Err_Stage OUT NOCOPY VARCHAR2) IS
1468 --p_msg_count NUMBER;
1469
1470 CURSOR GET_BUDGET_VERSION_CSR(X_Award_Project_Id NUMBER) IS
1471 SELECT
1472 BUDGET_VERSION_ID
1473 FROM
1474 PA_BUDGET_VERSIONS
1475 WHERE
1476 PROJECT_ID = X_Award_Project_Id and
1477 BUDGET_TYPE_CODE = 'AR';
1478
1479 X_Budget_Version_Id NUMBER;
1480
1481 CURSOR GET_RESOURCE_ASSIGNMENT_CSR(X_Budget_Version_Id NUMBER) IS
1482 SELECT
1483 RESOURCE_ASSIGNMENT_ID
1484 FROM
1485 PA_RESOURCE_ASSIGNMENTS
1486 WHERE
1487 BUDGET_VERSION_ID = X_Budget_Version_Id;
1488
1489 X_Resource_Assignment_Id NUMBER;
1490 /* BUG NO.1545351 Start */
1491 CURSOR GET_RESOURCE_LIST_ASSIGN_CSR(X_Award_Project_Id NUMBER ) IS
1492 SELECT
1493 RESOURCE_LIST_ASSIGNMENT_ID
1494 FROM
1495 pa_resource_list_assignments
1496 WHERE
1497 project_id = X_Award_Project_Id ;
1498 /* BUG NO.1545351 END */
1499
1500
1501 BEGIN
1502 FOR GBV_RECORD IN GET_BUDGET_VERSION_CSR(X_Award_Project_Id) LOOP
1503 FOR GRA_RECORD IN GET_RESOURCE_ASSIGNMENT_CSR(GBV_RECORD.BUDGET_VERSION_ID) LOOP
1504 BEGIN
1505 Delete FROM PA_BUDGET_LINES
1506 WHERE
1507 RESOURCE_ASSIGNMENT_ID = GRA_RECORD.RESOURCE_ASSIGNMENT_ID;
1508 END;
1509 END LOOP;
1510 DELETE FROM PA_RESOURCE_ASSIGNMENTS
1511 WHERE
1512 BUDGET_VERSION_ID = GBV_RECORD.BUDGET_VERSION_ID;
1513 END LOOP;
1514 /* BUG NO.1545351 Start */
1515 FOR RESOURCE_LIST_RECORD IN GET_RESOURCE_LIST_ASSIGN_CSR(X_Award_Project_Id )
1516 LOOP
1517
1518 DELETE FROM pa_resource_list_uses
1519 WHERE
1520 resource_list_assignment_id =RESOURCE_LIST_RECORD.resource_list_assignment_id;
1521
1522 END LOOP;
1523
1524 DELETE FROM pa_resource_list_assignments
1525 WHERE PROJECT_ID=X_Award_Project_Id ;
1526 /* BUG NO.1545351 END */
1527 DELETE
1528 FROM
1529 PA_BUDGET_VERSIONS
1530 WHERE
1531 PROJECT_ID = X_Award_Project_Id and
1532 BUDGET_TYPE_CODE = 'AR';
1533 IF SQL%ROWCOUNT = 0 THEN
1534 X_Err_Code := 'E';
1535 X_Err_Stage := ('No rows delete FROM PA_BUDGET_VERSIONS for Project Id '||X_Award_Project_Id);
1536 X_Err_Stage := 'GMS_NO_BUD_VERS_DELETED';
1537 FND_MESSAGE.SET_NAME('GMS','GMS_NO_BUD_VERS_DELETED');
1538 FND_MESSAGE.SET_TOKEN('AWARD_PROJECT_ID',to_char(X_Award_Project_Id) );
1539 FND_MSG_PUB.add;
1540 FND_MSG_PUB.Count_And_Get
1541 ( p_count => p_msg_count ,
1542 p_data => X_Err_Stage );
1543 END IF;
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546 FND_MSG_PUB.add_exc_msg
1547 ( p_pkg_name => 'GMS_MULTI_FUNDING'
1548 ,p_procedure_name => 'DELETE_BASELINED_VERSIONS'
1549 );
1550 RAISE;
1551 END DELETE_BASELINED_VERSIONS;
1552
1553 PROCEDURE CREATE_AGREEMENT(X_Row_Id OUT NOCOPY VARCHAR2,
1554 X_Agreement_Id OUT NOCOPY NUMBER,
1555 X_Customer_Id IN NUMBER,
1556 X_Agreement_Num IN VARCHAR2,
1557 X_Agreement_Type IN VARCHAR2,
1558 X_Revenue_Limit_Flag IN VARCHAR2 DEFAULT 'N',-- Bug 1841288 : Changed 'Y' to 'N'
1559 X_Invoice_Limit_Flag IN VARCHAR2 DEFAULT 'N', /*Bug 6642901*/
1560 X_Owned_By_Person_Id IN NUMBER,
1561 X_Term_Id IN NUMBER,
1562 X_Close_Date IN DATE,
1563 X_Org_Id IN NUMBER, --Shared Service Enhancement
1564 RETCODE OUT NOCOPY VARCHAR2,
1565 ERRBUF OUT NOCOPY VARCHAR2) IS
1566 St_Agreement_Id NUMBER(15);
1567 St_Row_Id VARCHAR2(30);
1568 --p_msg_count NUMBER;
1569 l_org_id NUMBER := X_Org_Id; -- Shared Service Enhancement
1570 BEGIN
1571
1572 -- Bug 1672982
1573
1574 PA_AGREEMENTS_PKG.INSERT_ROW(
1575
1576 X_ROWID => St_Row_Id,
1577 X_AGREEMENT_ID => St_Agreement_Id,
1578 X_CUSTOMER_ID => X_Customer_Id,
1579 X_AGREEMENT_NUM => X_Agreement_Num,
1580 X_AGREEMENT_TYPE => X_Agreement_Type,
1581 X_LAST_UPDATE_DATE => sysdate,
1582 X_LAST_UPDATED_BY => fnd_global.user_id,
1583 X_CREATION_DATE => sysdate,
1584 X_CREATED_BY => fnd_global.user_id,
1585 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
1586 X_OWNED_BY_PERSON_ID => X_Owned_By_Person_Id,
1587 X_TERM_ID => X_Term_Id,
1588 X_REVENUE_LIMIT_FLAG => nvl(X_Revenue_Limit_Flag, 'N'),-- Bug 1841288 : Changed 'Y'to'N'
1589 X_AMOUNT => 0,
1590 X_DESCRIPTION => NULL,
1591 X_EXPIRATION_DATE => X_Close_Date,
1592 X_ATTRIBUTE_CATEGORY => NULL,
1593 X_ATTRIBUTE1 => NULL,
1594 X_ATTRIBUTE2 => NULL,
1595 X_ATTRIBUTE3 => NULL,
1596 X_ATTRIBUTE4 => NULL,
1597 X_ATTRIBUTE5 => NULL,
1598 X_ATTRIBUTE6 => NULL,
1599 X_ATTRIBUTE7 => NULL,
1600 X_ATTRIBUTE8 => NULL,
1601 X_ATTRIBUTE9 => NULL,
1602 X_ATTRIBUTE10 => NULL,
1603 X_TEMPLATE_FLAG => NULL,
1604 X_PM_AGREEMENT_REFERENCE => NULL,
1605 X_PM_PRODUCT_CODE => NULL,
1606 -- Bug 2475640 : Added parameters for 11.5 PA-J certification.
1607 X_OWNING_ORGANIZATION_ID => NULL,
1608 X_AGREEMENT_CURRENCY_CODE => pa_currency.get_currency_code,
1609 X_INVOICE_LIMIT_FLAG => nvl(X_Invoice_Limit_Flag, 'N'),
1610 /*Passed value of X_Invoice_Limit_flag rather than X_Revenue_Limit_Flag for bug 6642901 */
1611 X_ORG_ID => l_org_id
1612 );
1613
1614 X_Row_Id := St_Row_Id;
1615 X_Agreement_Id := St_Agreement_Id;
1616 RETCODE := 'S';
1617
1618 EXCEPTION
1619 WHEN OTHERS THEN
1620
1621 X_Agreement_Id := -1;
1622 RETCODE := 'U';
1623 ERRBUF := SQLERRM;
1624 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
1625 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: CREATE_AGREEMENT');
1626 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
1627 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
1628 FND_MSG_PUB.add;
1629 FND_MSG_PUB.Count_And_Get
1630 ( p_count => p_msg_count ,
1631 p_data => ERRBUF );
1632
1633 RETURN;
1634 END CREATE_AGREEMENT;
1635
1636 PROCEDURE DELETE_AGREEMENT(X_Agreement_Id IN NUMBER,
1637 RETCODE OUT NOCOPY VARCHAR2,
1638 ERRBUF OUT NOCOPY VARCHAR2) IS
1639 X_Row_Id VARCHAR2(30);
1640 Check_Funding_Exists NUMBER := 0;
1641 --p_msg_count NUMBER;
1642
1643 BEGIN
1644 BEGIN
1645 SELECT
1646 rowid
1647 INTO
1648 X_Row_Id
1649 FROM
1650 PA_AGREEMENTS
1651 WHERE
1652 AGREEMENT_ID = X_Agreement_Id;
1653 EXCEPTION
1654 WHEN NO_DATA_FOUND THEN
1655 RETCODE := 'E';
1656 ERRBUF := 'No Agreement Found with Id '||to_char(X_Agreement_Id) ;
1657 FND_MESSAGE.SET_NAME('GMS','GMS_AGREEMENT_NOT_FOUND');
1658 FND_MESSAGE.SET_TOKEN('AGREEMENT_ID',to_char(X_Agreement_Id) );
1659 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING : DELETE_AGREEMENT');
1660 FND_MSG_PUB.add;
1661 FND_MSG_PUB.Count_And_Get
1662 ( p_count => p_msg_count ,
1663 p_data => ERRBUF );
1664
1665 WHEN OTHERS THEN
1666 RETCODE := 'U';
1667 ERRBUF := SQLCODE||' '||SQLERRM;
1668 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
1669 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: DELETE_AGREEMENT');
1670 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
1671 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
1672 FND_MSG_PUB.add;
1673 FND_MSG_PUB.Count_And_Get
1674 ( p_count => p_msg_count ,
1675 p_data => ERRBUF );
1676 END;
1677
1678 BEGIN
1679 SELECT COUNT(X_Agreement_Id)
1680 INTO
1681 Check_Funding_Exists
1682 FROM PA_SUMMARY_PROJECT_FUNDINGS
1683 WHERE AGREEMENT_ID = X_Agreement_Id;
1684 IF Check_Funding_Exists >= 1 THEN
1685 RETCODE := 'E';
1686 ERRBUF := 'Cannot delete Agreement while Funding Exists ';
1687
1688 FND_MESSAGE.SET_NAME('GMS','GMS_FUND_EXISTS_FOR_AGMT');
1689 FND_MSG_PUB.add;
1690 FND_MSG_PUB.Count_And_Get
1691 ( p_count => p_msg_count ,
1692 p_data => ERRBUF );
1693
1694 Elsif Check_Funding_Exists = 0 THEN
1695 RETCODE := 'S';
1696 PA_AGREEMENTS_PKG.DELETE_ROW( X_ROWID => X_Row_Id); -- Bug 1672982
1697 END IF;
1698 END;
1699 -- Added Exception for Bug:2662848
1700 EXCEPTION
1701 WHEN OTHERS THEN
1702 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1703 FND_MSG_PUB.add_exc_msg
1704 ( p_pkg_name => 'GMS_MULTI_FUNDING'
1705 ,p_procedure_name => 'DELETE_AGREEMENT'
1706 );
1707 END IF;
1708 RAISE;
1709 END DELETE_AGREEMENT;
1710
1711
1712 FUNCTION ALLOW_REV_LIMIT_FLAG_UPDATE(X_Agreement_Id IN NUMBER,
1713 X_Err_Code OUT NOCOPY VARCHAR2,
1714 X_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
1715 St_Award_Project_Id NUMBER;
1716 St_Invoice_Num_Count NUMBER;
1717 St_Revenue_Num_Count NUMBER;
1718 St_award_id NUMBER;
1719 x_total_funding_amount NUMBER;
1720 x_total_revenue_amount NUMBER;
1721 x_total_billed_amount NUMBER;
1722 x_bill_amount NUMBER;
1723 x_revenue_amount NUMBER;
1724
1725 St_revenue_distribution_rule VARCHAR2(10);
1726 St_billing_distribution_rule VARCHAR2(10);
1727
1728 BEGIN
1729 BEGIN
1730 Select
1731 Award_Project_Id,
1732 award_id,
1733 revenue_distribution_rule,
1734 billing_distribution_rule
1735 INTO
1736 St_Award_Project_Id,
1737 St_award_id,
1738 St_revenue_distribution_rule,
1739 St_billing_distribution_rule
1740 FROM
1741 GMS_AWARDS_ALL
1742 WHERE
1743 AGREEMENT_ID = X_Agreement_Id
1744 and award_template_flag = 'DEFERRED';
1745
1746 EXCEPTION
1747 WHEN NO_DATA_FOUND THEN
1748 X_Err_Code := 'E';
1749 X_Err_Stage := 'GMS_AWARD_FOR_AGR_NOT_FOUND';
1750 FND_MESSAGE.SET_NAME('GMS','GMS_AWARD_FOR_AGR_NOT_FOUND');
1751 FND_MSG_PUB.add;
1752 FND_MSG_PUB.Count_And_Get
1753 ( p_count => p_msg_count ,
1754 p_data => X_Err_Stage );
1755 RETURN FALSE;
1756 END;
1757 BEGIN
1758 /***************Commented out NOCOPY for bug 1393568 ***********************
1759
1760 Select
1761 count(draft_invoice_num)
1762 INTO
1763 St_Invoice_Num_Count
1764 FROM
1765 PA_DRAFT_INVOICES
1766 WHERE
1767 project_id = St_Award_Project_Id
1768 and agreement_id = X_Agreement_Id;
1769
1770 Select
1771 count(draft_revenue_num)
1772 INTO
1773 St_Revenue_Num_Count
1774 FROM
1775 PA_DRAFT_REVENUES
1776 WHERE
1777 project_id = St_Award_Project_Id
1778 and agreement_id = X_Agreement_Id;
1779 END;
1780
1781 IF (St_Invoice_Num_Count > 0 OR St_Revenue_Num_Count > 0) THEN
1782 X_Err_Code := 'E';
1783 X_Err_Stage := 'GMS_REVENUE_OR_INVOICES_EXIST';
1784 FND_MESSAGE.SET_NAME('GMS','GMS_REVENUE_OR_INVOICES_EXIST');
1785 FND_MSG_PUB.add;
1786 FND_MSG_PUB.Count_And_Get
1787 ( p_count => p_msg_count ,
1788 p_data => X_Err_Stage );
1789
1790 RETURN FALSE;
1791 ELSE
1792 X_Err_Code := 'S';
1793 RETURN TRUE;
1794 END IF;
1795 ***************END of comment for bug 1393568 *****************************/
1796
1797 SELECT sum(nvl(total_funding_amount,0)),
1798 sum(nvl(total_revenue_amount,0)),
1799 sum(nvl(total_billed_amount,0))
1800 INTO x_total_funding_amount,
1801 x_total_revenue_amount,
1802 x_total_billed_amount
1803 FROM gms_summary_project_fundings gspf,
1804 gms_installments ins
1805 WHERE ins.installment_id = gspf.installment_id
1806 and ins.award_id = St_award_id;
1807
1808 IF St_revenue_distribution_rule = 'COST' and St_billing_distribution_rule = 'COST' THEN
1809 if ((x_total_revenue_amount > x_total_funding_amount) or (x_total_billed_amount > x_total_funding_amount)) THEN
1810 X_Err_Code := 'E';
1811 X_Err_Stage := 'GMS_REVENUE_OR_INVOICES_EXIST';
1812 FND_MESSAGE.SET_NAME('GMS','GMS_REVENUE_OR_INVOICES_EXIST');
1813 FND_MSG_PUB.add;
1814 FND_MSG_PUB.Count_And_Get
1815 ( p_count => p_msg_count ,
1816 p_data => X_Err_Stage );
1817
1818 return false;
1819 else
1820 return true;
1821 end if;
1822 Elsif (St_revenue_distribution_rule = 'COST' and St_billing_distribution_rule = 'EVENT') or
1823 (St_revenue_distribution_rule = 'EVENT' and St_billing_distribution_rule = 'EVENT') THEN
1824 SELECT sum(bill_amount),
1825 sum(revenue_amount)
1826 INTO x_bill_amount, x_revenue_amount
1827 FROM pa_events a,gms_awards_all b
1828 WHERE a.project_id = b.award_project_id
1829 and b.award_id = St_award_id;
1830 if ((x_revenue_amount > x_total_funding_amount)/* or (x_bill_amount > x_total_funding_amount)*/) THEN
1831 X_Err_Code := 'E';
1832 /* Bug 1711701 */
1833 /* Changed the Message Name FROM 'GMS_REVENUE_OR_INVOICES_EXIST' to 'GMS_CANNOT_UPDATE_FLAG'
1834 as message Text of previous message got changed in 11i */
1835
1836 /* X_Err_Stage := 'GMS_CANNOT_UPDATE_FLAG';
1837 FND_MESSAGE.SET_NAME('GMS','GMS_CANNOT_UPDATE_FLAG'); Commented for bug 6642901 and replaced with 2 new messages specific to revenue and invoice.*/
1838 X_Err_Stage := 'GMS_CANNOT_UPD_REV_FLAG';
1839 FND_MESSAGE.SET_NAME('GMS','GMS_CANNOT_UPD_REV_FLAG');
1840 FND_MSG_PUB.add;
1841 FND_MSG_PUB.Count_And_Get
1842 ( p_count => p_msg_count ,
1843 p_data => X_Err_Stage );
1844
1845 return false;
1846 elsif (x_bill_amount > x_total_funding_amount) THEN
1847 X_Err_Code := 'E';
1848 X_Err_Stage := 'GMS_CANNOT_UPD_INV_FLAG';
1849 FND_MESSAGE.SET_NAME('GMS','GMS_CANNOT_UPD_INV_FLAG');
1850 FND_MSG_PUB.add;
1851 FND_MSG_PUB.Count_And_Get
1852 ( p_count => p_msg_count ,
1853 p_data => X_Err_Stage);
1854 return false;
1855 else
1856 return true;
1857
1858 end if;
1859 END if;
1860 -- --dbms_output.put_line('At end of ALLOW_REV_LIMIT_FLAG_UPDATE');
1861 END;
1862 Exception
1863 WHEN OTHERS THEN
1864 X_Err_Code := 'U';
1865 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
1866 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: ALLOW_REV_LIMIT_FLAG_UPDATE');
1867 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
1868 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
1869 FND_MSG_PUB.add;
1870 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
1871 p_data => X_Err_Stage);
1872 RAISE FND_API.G_EXC_ERROR;
1873
1874 END ALLOW_REV_LIMIT_FLAG_UPDATE;
1875
1876 PROCEDURE UPDATE_AGREEMENT(X_Agreement_Id IN NUMBER,
1877 X_Agreement_Num IN VARCHAR2 DEFAULT NULL,
1878 X_Agreement_Type IN VARCHAR2 DEFAULT NULL,
1879 X_Revenue_Limit_Flag IN VARCHAR2 DEFAULT NULL,
1880 X_Invoice_Limit_Flag IN VARCHAR2 DEFAULT NULL, /*Bug 6642901*/
1881 X_Customer_Id IN NUMBER DEFAULT NULL,
1882 X_Owned_By_Person_Id IN NUMBER DEFAULT NULL,
1883 X_Term_Id IN NUMBER DEFAULT NULL,
1884 X_Amount IN NUMBER DEFAULT 0,
1885 X_Close_Date IN DATE DEFAULT NULL,
1886 RETCODE OUT NOCOPY VARCHAR2,
1887 ERRBUF OUT NOCOPY VARCHAR2) IS
1888 Store_Row_Id VARCHAR2(30);
1889 Store_Term_Id NUMBER(15);
1890 Store_Customer_Id NUMBER(15);
1891 Store_Owned_By_Person_Id NUMBER(15);
1892 Store_Agreement_Num VARCHAR2(30);
1893 Store_Expiration_Date DATE;
1894 Store_Agreement_Type VARCHAR2(30);
1895 Store_Revenue_Limit_Flag VARCHAR2(1);
1896 Store_Invoice_Limit_Flag VARCHAR2(1); /*Bug 6642901*/
1897 Store_Amount NUMBER(22,5);
1898 Store_agreement_currency_code PA_AGREEMENTS.agreement_currency_code%type; -- Bug 2475640
1899
1900 --p_msg_count NUMBER;
1901
1902 BEGIN
1903 BEGIN
1904 SELECT
1905 ROWID,
1906 NVL(X_Term_Id,TERM_ID),
1907 NVL(X_Customer_Id,CUSTOMER_ID),
1908 NVL(X_Owned_By_Person_Id,OWNED_BY_PERSON_ID),
1909 NVL(X_Agreement_Num,AGREEMENT_NUM),
1910 NVL(X_Close_Date,EXPIRATION_DATE),
1911 NVL(X_Agreement_Type,AGREEMENT_TYPE),
1912 NVL(X_Revenue_Limit_Flag, REVENUE_LIMIT_FLAG),
1913 NVL(X_Invoice_Limit_Flag, INVOICE_LIMIT_FLAG), /*Bug 6642901*/
1914 nvl(AMOUNT,0),
1915 agreement_currency_code -- Bug 2475640 : Added for 11.5 PA-J certification.
1916 INTO
1917 Store_Row_Id,
1918 Store_Term_Id,
1919 Store_Customer_Id,
1920 Store_Owned_By_Person_Id,
1921 Store_Agreement_Num,
1922 Store_Expiration_Date,
1923 Store_Agreement_Type,
1924 Store_Revenue_Limit_Flag,
1925 Store_Invoice_Limit_Flag, /*Bug 6642901*/
1926 Store_Amount,
1927 Store_agreement_currency_code -- Bug 2475640 : Added for 11.5 PA-J certification.
1928 FROM
1929 PA_AGREEMENTS
1930 WHERE
1931 AGREEMENT_ID = X_Agreement_Id;
1932 EXCEPTION
1933 WHEN NO_DATA_FOUND THEN
1934 RETCODE := 'E';
1935 ERRBUF := 'No Agreement Found with Id '||to_char(X_Agreement_Id) ;
1936
1937 FND_MESSAGE.SET_NAME('GMS','GMS_AGREEMENT_NOT_FOUND');
1938 FND_MESSAGE.SET_TOKEN('AGREEMENT_ID',to_char(X_Agreement_Id) );
1939 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING : UPDATE_AGREEMENT');
1940 FND_MSG_PUB.add;
1941 FND_MSG_PUB.Count_And_Get
1942 ( p_count => p_msg_count ,
1943 p_data => ERRBUF );
1944 RAISE FND_API.G_EXC_ERROR;
1945 WHEN OTHERS THEN
1946 RETCODE := 'U';
1947 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
1948 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_AGREEMENT');
1949 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
1950 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
1951 FND_MSG_PUB.add;
1952 FND_MSG_PUB.Count_And_Get
1953 ( p_count => p_msg_count ,
1957
1954 p_data => ERRBUF );
1955 RAISE FND_API.G_EXC_ERROR;
1956 END;
1958
1959 Store_Amount := (Store_Amount + X_Amount);
1960
1961 -- Bug 1672982
1962
1963 PA_AGREEMENTS_PKG.UPDATE_ROW (
1964 X_ROWID => Store_Row_Id,
1965 X_AGREEMENT_ID => X_AGREEMENT_ID,
1966 X_CUSTOMER_ID => Store_Customer_Id,
1967 X_AGREEMENT_NUM => Store_Agreement_Num,
1968 X_AGREEMENT_TYPE => Store_Agreement_Type,
1969 X_LAST_UPDATE_DATE => SYSDATE,
1970 X_LAST_UPDATED_BY => fnd_global.user_id,
1971 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
1972 X_OWNED_BY_PERSON_ID => Store_Owned_By_Person_Id,
1973 X_TERM_ID => Store_Term_Id,
1974 X_REVENUE_LIMIT_FLAG => Store_Revenue_Limit_Flag,
1975 X_AMOUNT => Store_Amount,
1976 X_DESCRIPTION => NULL,
1977 X_EXPIRATION_DATE => Store_Expiration_Date,
1978 X_ATTRIBUTE_CATEGORY => NULL,
1979 X_ATTRIBUTE1 => NULL,
1980 X_ATTRIBUTE2 => NULL,
1981 X_ATTRIBUTE3 => NULL,
1982 X_ATTRIBUTE4 => NULL,
1983 X_ATTRIBUTE5 => NULL,
1984 X_ATTRIBUTE6 => NULL,
1985 X_ATTRIBUTE7 => NULL,
1986 X_ATTRIBUTE8 => NULL,
1987 X_ATTRIBUTE9 => NULL,
1988 X_ATTRIBUTE10 => NULL,
1989 X_TEMPLATE_FLAG => NULL,
1990 X_PM_AGREEMENT_REFERENCE => NULL,
1991 X_PM_PRODUCT_CODE => NULL,
1992 -- Bug 2475640 : Added parameters for 11.5 PA-J certification.
1993 X_OWNING_ORGANIZATION_ID => NULL,
1994 X_AGREEMENT_CURRENCY_CODE => Store_agreement_currency_code,
1995 X_INVOICE_LIMIT_FLAG => Store_Invoice_Limit_Flag
1996 /*Replaced Store_Revenue_Limit_Flag with Store_Invoice_Limit_Flag for bug 6642901*/
1997
1998 );
1999
2000
2001 RETCODE := 'S';
2002 EXCEPTION
2003 WHEN FND_API.G_EXC_ERROR THEN
2004 RETURN;
2005
2006 -- Added when others exception for Bug:2662848
2007 WHEN OTHERS THEN
2008 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2009 FND_MSG_PUB.add_exc_msg
2010 ( p_pkg_name => 'GMS_MULTI_FUNDING'
2011 ,p_procedure_name => 'UPDATE_AGREEMENT'
2012 );
2013 END IF;
2014 RAISE;
2015 END UPDATE_AGREEMENT;
2016
2017 --This Procedure creates a Budget Type for the Award whenever the Award is created
2018 -- this procedure is not used in 11i as there is no award_budget_type -- Suresh
2019 PROCEDURE INSERT_AWARD_BUDGET_TYPE(X_Budget_Type_Code IN VARCHAR2,
2020 X_Budget_Type IN VARCHAR2,
2021 X_Start_Date IN DATE,
2022 X_End_Date IN DATE,
2023 X_Predefined_Flag IN VARCHAR2,
2024 X_Accumulation_Flag IN VARCHAR2,
2025 X_Award_Flag IN VARCHAR2,
2026 X_Err_Code OUT NOCOPY VARCHAR2,
2027 X_Err_Stage OUT NOCOPY VARCHAR2) IS
2028 BEGIN
2029 INSERT INTO PA_BUDGET_TYPES(BUDGET_TYPE_CODE,
2030 LAST_UPDATE_DATE,
2031 LAST_UPDATED_BY,
2032 CREATION_DATE,
2033 CREATED_BY,
2034 LAST_UPDATE_LOGIN ,
2035 BUDGET_TYPE,
2036 START_DATE_ACTIVE,
2037 BUDGET_AMOUNT_CODE,
2038 PREDEFINED_FLAG,
2039 ACCUMULATION_FLAG,
2040 END_DATE_ACTIVE)
2041 --AWARD_FLAG )--11i change
2042 VALUES(X_Budget_Type_Code,
2043 SYSDATE,
2044 fnd_global.user_id,
2045 SYSDATE,
2046 fnd_global.user_id,
2047 fnd_global.login_id,
2048 X_Budget_Type,
2049 X_Start_Date,
2050 'C',
2051 X_Predefined_Flag,
2052 X_Accumulation_Flag,
2053 X_End_Date);
2054 --X_Award_Flag); --11i CHANGE
2055 X_Err_Code := 'S';
2056 EXCEPTION
2057 WHEN DUP_VAL_ON_INDEX THEN
2058 X_Err_Code := 'E';
2059 X_Err_Stage := 'Budget Type already exists ';
2060 FND_MESSAGE.SET_NAME('GMS','GMS_BUD_TYP_EXISTS');
2061 FND_MSG_PUB.add;
2062 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2063 p_data => X_Err_Stage );
2064 WHEN OTHERS THEN
2065 X_Err_Code := 'U';
2066 X_Err_Stage := SQLCODE||' '||SQLERRM;
2067 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
2068 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_AWARD_BUDGET_TYPE');
2069 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
2070 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
2071 FND_MSG_PUB.add;
2072 FND_MSG_PUB.Count_And_Get(p_count =>p_msg_count,
2073 p_data =>X_Err_Stage );
2074
2075 END INSERT_AWARD_BUDGET_TYPE;
2076
2077 -- this procedure is not used in 11i as there is no award_budget_type -- Suresh
2078 PROCEDURE UPDATE_AWARD_BUDGET_TYPE(X_Budget_Type_Code IN VARCHAR2,
2079 X_Budget_Type IN VARCHAR2,
2080 X_Start_Date IN DATE,
2084 BEGIN
2081 X_End_Date IN DATE,
2082 X_Err_Code OUT NOCOPY VARCHAR2,
2083 X_Err_Stage OUT NOCOPY VARCHAR2) IS
2085 UPDATE PA_BUDGET_TYPES
2086 SET Budget_Type = X_Budget_Type
2087 /* Start_Date_Active = X_Start_Date,
2088 End_Date_Active = X_End_Date */
2089 WHERE
2090 Budget_Type_Code = X_Budget_Type_Code;
2091 X_Err_Code := 'S';
2092 IF SQL%NOTFOUND THEN
2093 X_Err_Code := 'E';
2094 X_Err_Stage := 'UPDATE FAILED: No Budget_Type found with Budget Type Code '||X_Budget_Type_Code;
2095 FND_MESSAGE.SET_NAME('GMS','GMS_BUD_TYP_NOT_FOUND');
2096 FND_MESSAGE.SET_TOKEN('BUDGET_TYPE_CODE',X_Budget_Type_Code);
2097 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_AWARD_BUDGET_TYPE');
2098 FND_MSG_PUB.add;
2099 FND_MSG_PUB.Count_And_Get(p_count =>p_msg_count,
2100 p_data =>X_Err_Stage );
2101
2102 END IF;
2103
2104 END UPDATE_AWARD_BUDGET_TYPE;
2105
2106 -- this procedure is not used in 11i as there is no award_budget_type -- Suresh
2107 PROCEDURE DELETE_AWARD_BUDGET_TYPE(X_Budget_Type_Code IN VARCHAR2,
2108 X_Err_Code OUT NOCOPY VARCHAR2,
2109 X_Err_Stage OUT NOCOPY VARCHAR2) IS
2110 BEGIN
2111 DELETE FROM PA_BUDGET_TYPES
2112 WHERE
2113 BUDGET_TYPE_CODE = X_Budget_Type_Code;
2114 X_Err_Code := 'S';
2115 IF SQL%ROWCOUNT = 0 THEN
2116 X_Err_Code := 'E' ;
2117 X_Err_Stage := 'DELETE of BUDGET_TYPE Failed: No Budget Type found with Budget Type Code '||X_Budget_Type_Code;
2118 FND_MESSAGE.SET_NAME('GMS','GMS_BUD_TYP_NOT_FOUND');
2119 FND_MESSAGE.SET_TOKEN('BUDGET_TYPE_CODE',X_Budget_Type_Code);
2120 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: DELETE_AWARD_BUDGET_TYPE');
2121 FND_MSG_PUB.add;
2122 FND_MSG_PUB.Count_And_Get(p_count =>p_msg_count,
2123 p_data =>X_Err_Stage );
2124 END IF;
2125
2126 END DELETE_AWARD_BUDGET_TYPE;
2127
2128
2129 PROCEDURE GET_CUSTOMER_INFO(X_Customer_Id IN NUMBER,
2130 X_Bill_To_Address_Id OUT NOCOPY NUMBER,
2131 X_Ship_To_Address_Id OUT NOCOPY NUMBER,
2132 X_Bill_To_Contact_Id OUT NOCOPY NUMBER,
2133 X_Ship_To_Contact_Id OUT NOCOPY NUMBER,
2134 X_Err_Code OUT NOCOPY VARCHAR2,
2135 X_Err_Stage OUT NOCOPY VARCHAR2) IS
2136
2137 St_Bill_To_Address_Id Number;
2138 St_Ship_To_Address_Id Number;
2139 St_Bill_To_Contact_Id NUMBER;
2140 St_Ship_To_Contact_Id NUMBER;
2141
2142 BEGIN
2143
2144 BEGIN
2145 --Shared Service Enhancement
2146 /********
2147 Replacing the Ra_addresseses and RA_Site_uses with HZ Tables
2148 Select a.Address_id
2149 Into St_Bill_To_Address_Id
2150 From Ra_Addresses a,
2151 Ra_Site_Uses su
2152 Where a.Address_Id = su.Address_id
2153 And a.Customer_Id = X_Customer_Id
2154 And Nvl(su.Status, 'A') = 'A'
2155 And su.Site_Use_Code = 'BILL_TO'
2156 And su.primary_flag = 'Y' ;
2157 ****************/
2158 SELECT su.cust_acct_site_id
2159 INTO St_Bill_To_Address_Id
2160 FROM HZ_CUST_ACCT_SITES ACCT_SITE,
2161 HZ_CUST_SITE_USES SU
2162 WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
2163 AND acct_site.cust_account_id = X_Customer_Id
2164 AND Nvl(su.Status, 'A') = 'A'
2165 AND su.Site_Use_Code = 'BILL_TO'
2166 AND su.primary_flag = 'Y' ;
2167
2168 X_Err_Code := 'S';
2169
2170 EXCEPTION
2171 WHEN NO_DATA_FOUND THEN
2172 X_Err_Code := 'E';
2173 X_Err_Stage := 'PA_NO_BILL_TO_ADDRESS' ;
2174 FND_MESSAGE.SET_NAME('PA','PA_NO_BILL_TO_ADDRESS');
2175 FND_MSG_PUB.add;
2176 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2177 p_data => X_Err_Stage);
2178 return;
2179 WHEN OTHERS THEN
2180 X_Err_Code := 'U';
2181 X_Err_Stage := SQLERRM;
2182
2183 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
2184 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
2185 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: PROJ_START_AND_END_DATE');
2186 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
2187 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
2188 FND_MSG_PUB.add;
2189 FND_MSG_PUB.Count_And_Get
2190 ( p_count => p_msg_count ,
2191 p_data => X_Err_Stage );
2192
2193 END ;
2194
2195 BEGIN
2196
2197 --Shared Service Enhancement
2198 /************
2199 Replacing the RA tables with HZ tables
2200 Select a.Address_id
2201 Into St_Ship_To_Address_Id
2202 From Ra_Addresses a,
2203 Ra_Site_Uses su
2204 Where a.Address_Id = su.Address_id
2205 And a.Customer_Id = X_Customer_Id
2206 And Nvl(su.Status, 'A') = 'A'
2207 And su.Site_Use_Code = 'SHIP_TO'
2208 And su.primary_flag = 'Y' ;
2209 ****************/
2210 SELECT su.cust_acct_site_id
2211 INTO St_Ship_To_Address_Id
2212 FROM hz_cust_acct_sites acct_site,
2213 Hz_cust_site_Uses su
2217 AND su.Site_Use_Code = 'SHIP_TO'
2214 WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
2215 AND acct_site.cust_account_id = X_Customer_Id
2216 AND Nvl(su.Status, 'A') = 'A'
2218 AND su.primary_flag = 'Y' ;
2219
2220
2221 X_Err_Code := 'S';
2222
2223 EXCEPTION
2224 WHEN NO_DATA_FOUND THEN
2225 X_Err_Code := 'E';
2226 X_Err_Stage := 'PA_NO_SHIP_TO_ADDRESS' ;
2227 FND_MESSAGE.SET_NAME('PA','PA_NO_SHIP_TO_ADDRESS');
2228 FND_MSG_PUB.add;
2229 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2230 p_data => X_Err_Stage);
2231 return ;
2232 WHEN OTHERS THEN
2233 X_Err_Code := 'U';
2234 X_Err_Stage := SQLERRM;
2235
2236 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
2237 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
2238 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: PROJ_START_AND_END_DATE');
2239 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
2240 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
2241 FND_MSG_PUB.add;
2242 FND_MSG_PUB.Count_And_Get
2243 ( p_count => p_msg_count ,
2244 p_data => X_Err_Stage );
2245 END ;
2246
2247 X_Bill_To_Address_Id := St_Bill_To_Address_Id ;
2248 X_Ship_To_Address_Id := St_Ship_To_Address_Id ;
2249
2250
2251 /* Getting the Ship_To_Contact for the Customer */
2252
2253 BEGIN
2254 --Shared Service Enhancement : Replaced ra_contacts with HZ Table
2255 Select su.Contact_Id
2256 Into X_Ship_To_Contact_Id
2257 From hz_cust_acct_sites acct_site,
2258 Hz_cust_site_Uses su
2259 Where acct_site.cust_acct_site_id = su.cust_acct_site_id
2260 And acct_site.cust_account_id = X_Customer_Id
2261 And Nvl(su.Status, 'A') = 'A'
2262 And su.Site_Use_Code = 'SHIP_TO'
2263 And su.primary_flag = 'Y' ;
2264
2265 X_Err_Code := 'S';
2266
2267 EXCEPTION
2268 WHEN NO_DATA_FOUND THEN
2269 X_Err_Code := 'E';
2270 X_Err_Stage := 'PA_NO_SHIP_TO_CONTACT' ;
2271 FND_MESSAGE.SET_NAME('PA','PA_NO_SHIP_TO_CONTACT');
2272 FND_MSG_PUB.add;
2273 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2274 p_data => X_Err_Stage);
2275 return;
2276 WHEN OTHERS THEN
2277 X_Err_Code := 'U';
2278 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
2279 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
2280 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING : GET_CUSTOMER_INFO');
2281 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
2282 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
2283 FND_MSG_PUB.add;
2284 FND_MSG_PUB.Count_And_Get
2285 ( p_count => p_msg_count ,
2286 p_data => X_Err_Stage );
2287 return;
2288
2289 END;
2290
2291 /* Get Bill To Contact Id for the Customer */
2292
2293 BEGIN
2294 --Shared Service Enhancement :Replaced the RA_Contacts with HZ tables.
2295 Select su.Contact_Id
2296 Into X_Ship_To_Contact_Id
2297 From hz_cust_acct_sites acct_site,
2298 Hz_cust_site_Uses su
2299 Where acct_site.cust_acct_site_id = su.cust_acct_site_id
2300 And acct_site.cust_account_id = X_Customer_Id
2301 And Nvl(su.Status, 'A') = 'A'
2302 And su.Site_Use_Code = 'BILL_TO'
2303 And su.primary_flag = 'Y' ;
2304 X_Err_Code := 'S';
2305 EXCEPTION
2306 When NO_DATA_FOUND THEN
2307 X_Err_Code := 'E';
2308 X_Err_Stage := 'PA_NO_BILL_TO_CONTACT' ;
2309 FND_MESSAGE.SET_NAME('PA','PA_NO_BILL_TO_CONTACT');
2310 FND_MSG_PUB.add;
2311 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2312 p_data => X_Err_Stage);
2313 return;
2314 WHEN OTHERS THEN
2315 X_Err_Code := 'U';
2316 X_Err_Stage := SQLERRM;
2317
2318 X_Err_Stage := 'GMS_UNEXPECTED_ERROR';
2319 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
2320 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING : GET_CUSTOMER_INFO');
2321 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
2322 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
2323 FND_MSG_PUB.add;
2324 FND_MSG_PUB.Count_And_Get
2325 ( p_count => p_msg_count ,
2326 p_data => X_Err_Stage );
2327 return;
2328
2329 END ;
2330 -- Added Exception for Bug:2662848
2331 EXCEPTION
2332 WHEN OTHERS THEN
2333 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2334 FND_MSG_PUB.add_exc_msg
2335 ( p_pkg_name => 'GMS_MULTI_FUNDING'
2336 ,p_procedure_name => 'GET_CUSTOMER_INFO'
2337 );
2338 END IF;
2339 RAISE;
2340 END GET_CUSTOMER_INFO;
2341
2342 -- Bug Fix 2994625
2343 -- Added a new parameter bill_to_customer_id to create contacts for LOC customer.
2344
2345 PROCEDURE MODIFY_GMS_CONTACTS(X_Award_Id IN NUMBER,
2346 X_Award_Project_Id IN NUMBER,
2347 X_Customer_Id IN NUMBER,
2351 X_Err_Code OUT NOCOPY VARCHAR2,
2348 X_Bill_to_customer_id IN NUMBER,
2349 X_Awd_Templ_Bill_Cont_Id IN NUMBER DEFAULT NULL,
2350 X_Awd_Templ_Ship_Cont_Id IN NUMBER DEFAULT NULL,
2352 X_Err_Stage OUT NOCOPY VARCHAR2) IS
2353 St_Bill_To_Contact_Id NUMBER;
2354 St_Ship_To_Contact_Id NUMBER;
2355 X_Cust_Bill_Cont_Exists NUMBER := -99;
2356 X_Cust_Ship_Cont_Exists NUMBER := -99;
2357 BEGIN
2358
2359 BEGIN
2360 Select
2361 contact_id
2362 INTO
2363 St_Bill_To_Contact_Id
2364 FROM
2365 PA_PROJECT_CONTACTS
2366 WHERE project_id = X_Award_Project_Id
2367 and customer_id = X_Customer_Id
2368 and project_contact_type_code = 'BILLING';
2369 X_Err_Code := 'S';
2370
2371 EXCEPTION
2372 WHEN NO_DATA_FOUND THEN
2373 X_Err_Code := 'E';
2374 X_Err_Stage := 'GMS_NO_AWARD_CONTACTS';
2375 FND_MESSAGE.SET_NAME('GMS','GMS_NO_AWARD_CONTACTS');
2376 FND_MSG_PUB.add;
2377 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2378 p_data => X_Err_Stage);
2379 return;
2380 END;
2381
2382 BEGIN
2383 Select
2384 contact_id
2385 INTO
2386 St_Ship_To_Contact_Id
2387 FROM
2388 PA_PROJECT_CONTACTS
2389 WHERE project_id = X_Award_Project_Id
2390 and customer_id = X_Customer_Id
2391 and project_contact_type_code = 'SHIPPING';
2392 X_Err_Code := 'S';
2393
2394 EXCEPTION
2395 WHEN NO_DATA_FOUND THEN
2396 X_Err_Code := 'S';
2397 NULL;
2398
2399 END;
2400
2401 --Updating the Rows for the Existing Customer's Primary BILL_TO and SHIP_TO contacts
2402 --setting the Primary Flag = 'N'. Changed it to actually delete the primary BILLTO
2403 --SHIP TO contacts
2404 BEGIN
2405
2406 delete FROM GMS_AWARDS_CONTACTS
2407 WHERE
2408 award_id = X_Award_Id
2409 and usage_code in ('BILL_TO','SHIP_TO')
2410 and primary_flag = 'Y';
2411
2412 /* UPDATE GMS_AWARDS_CONTACTS
2413 set primary_flag = 'N'
2414 WHERE
2415 award_id = X_Award_Id
2416 and usage_code in ('BILL_TO','SHIP_TO')
2417 and primary_flag = 'Y';
2418 */
2419
2420 END;
2421
2422 --===========================================================================
2423 /* Inserting Bill_To_Contact */
2424 BEGIN
2425
2426 --------------------------------------------------------------------------------
2427 /* Overriding the Ship_To_Contact_Id obtained FROM PA_PROJECT_CONTACTS with the one obtained FROM Award Template */
2428
2429 IF X_Awd_Templ_Bill_Cont_Id IS NOT NULL THEN
2430 ST_Bill_To_Contact_Id := X_Awd_Templ_Bill_Cont_Id;
2431 END IF;
2432 --------------------------------------------------------------------------------
2433
2434
2435 IF St_Bill_To_Contact_Id is NOT NULL THEN
2436
2437 BEGIN
2438 SELECT 1
2439 INTO
2440 X_Cust_Bill_Cont_Exists
2441 FROM
2442 GMS_AWARDS_CONTACTS
2443 WHERE
2444 award_id = X_Award_id
2445 and customer_id = X_Customer_Id
2446 and contact_id = St_Bill_To_Contact_Id
2447 and usage_code = 'BILL_TO';
2448 EXCEPTION
2449 WHEN NO_DATA_FOUND THEN
2450 NULL;
2451 END;
2452
2453
2454 IF X_Cust_Bill_Cont_Exists = -99 THEN
2455 INSERT INTO GMS_AWARDS_CONTACTS(AWARD_ID
2456 ,CONTACT_ID
2457 ,CUSTOMER_ID
2458 ,USAGE_CODE
2459 ,PRIMARY_FLAG
2460 ,CREATION_DATE
2461 ,CREATED_BY
2462 ,LAST_UPDATE_DATE
2463 ,LAST_UPDATED_BY
2464 ,LAST_UPDATE_LOGIN)
2465 VALUES(X_Award_Id
2466 ,St_Bill_To_Contact_Id
2467 ,NVL(X_Bill_to_Customer_id,X_Customer_Id)
2468 ,'BILL_TO'
2469 ,'Y'
2470 ,SYSDATE
2471 ,fnd_global.user_id
2472 ,SYSDATE
2473 ,fnd_global.user_id
2474 ,fnd_global.login_id);
2475
2476 Elsif X_Cust_Bill_Cont_Exists = 1 THEN
2477 UPDATE GMS_AWARDS_CONTACTS
2478 set
2479 PRIMARY_FLAG = 'Y'
2480 WHERE
2481 award_id = X_Award_Id
2482 and customer_id = NVL(X_Bill_to_Customer_id,X_Customer_Id)
2483 and contact_id = St_Bill_To_Contact_Id
2484 and usage_code = 'BILL_TO';
2485 END IF;
2486
2487 END IF;
2488 X_Err_Code := 'S';
2489
2490 --------------------------------------------------------------------------------
2491 /* Overriding the Ship_To_Contact_Id obtained FROM PA_PROJECT_CONTACTS with the one obtained FROM Award Template */
2492
2493 IF X_Awd_Templ_Ship_Cont_Id IS NOT NULL THEN
2494 St_Ship_To_Contact_Id := X_Awd_Templ_Ship_Cont_Id;
2495 END IF;
2496 --------------------------------------------------------------------------------
2497
2498 IF St_Ship_To_Contact_Id is NOT NULL THEN
2499
2500 BEGIN
2501 SELECT 1
2502 INTO
2503 X_Cust_Ship_Cont_Exists
2504 FROM
2505 GMS_AWARDS_CONTACTS
2506 WHERE
2507 award_id = X_Award_id
2508 and customer_id = X_Customer_Id
2509 and contact_id = St_Ship_To_Contact_Id
2510 and usage_code = 'SHIP_TO';
2511 EXCEPTION
2512 WHEN NO_DATA_FOUND THEN
2513 NULL;
2514 END;
2515
2516 IF X_Cust_Ship_Cont_Exists = -99 THEN
2517 INSERT INTO GMS_AWARDS_CONTACTS(AWARD_ID
2518 ,CONTACT_ID
2519 ,CUSTOMER_ID
2520 ,USAGE_CODE
2521 ,PRIMARY_FLAG
2522 ,CREATION_DATE
2523 ,CREATED_BY
2524 ,LAST_UPDATE_DATE
2525 ,LAST_UPDATED_BY
2526 ,LAST_UPDATE_LOGIN)
2527 VALUES(X_Award_Id
2528 ,St_Ship_To_Contact_Id
2529 ,NVL(X_Bill_to_customer_id,X_Customer_Id)
2530 ,'SHIP_TO'
2531 ,'Y'
2532 ,SYSDATE
2533 ,fnd_global.user_id
2534 ,SYSDATE
2535 ,fnd_global.user_id
2536 ,fnd_global.login_id);
2537 Elsif X_Cust_Bill_Cont_Exists = 1 THEN
2538
2539 UPDATE GMS_AWARDS_CONTACTS
2540 set
2541 PRIMARY_FLAG = 'Y'
2542 WHERE
2543 award_id = X_Award_Id
2544 and customer_id = X_Customer_Id
2545 and contact_id = St_Ship_To_Contact_Id
2546 and usage_code = 'SHIP_TO';
2547 END IF;
2548
2549 END IF;
2550
2551 X_Err_Code := 'S';
2552
2553 EXCEPTION
2554 WHEN TOO_MANY_ROWS THEN
2555 X_Err_Code := 'E';
2556 FND_MESSAGE.SET_NAME('GMS','GMS_MULTI_AWD_CONTACTS_FOUND');
2557 FND_MSG_PUB.add;
2558 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2559 p_data => X_Err_Stage);
2560 RETURN;
2561 END;
2562
2563 EXCEPTION
2564 WHEN OTHERS THEN
2565 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2566 FND_MSG_PUB.add_exc_msg
2567 ( p_pkg_name => 'GMS_MULTI_FUNDING'
2568 ,p_procedure_name => 'MODIFY_GMS_CONTACTS'
2569 );
2570 END IF;
2571 RAISE;
2572 --=======================================================
2573 END MODIFY_GMS_CONTACTS;
2574
2575 PROCEDURE CREATE_AWARD_PROJECT( X_Project_Name IN VARCHAR2,
2576 X_Project_Number IN VARCHAR2,
2577 X_Customer_Id IN NUMBER,
2578 X_Bill_to_Customer_Id IN NUMBER,
2579 X_Carrying_Out_Organization_Id IN NUMBER,
2580 X_Award_Id IN NUMBER,
2581 X_IDC_Schedule_Id IN NUMBER,
2582 X_IDC_Schedule_Fixed_Date IN DATE,
2583 X_Labor_Invoice_Format_Id IN NUMBER,
2584 X_Non_Labor_Invoice_Format_Id IN NUMBER,
2585 X_Person_Id IN NUMBER,
2586 X_Term_Id IN NUMBER,
2587 X_Start_Date IN DATE,
2588 X_End_Date IN DATE,
2589 X_Close_Date IN DATE,
2590 X_Agreement_Type IN VARCHAR2,
2591 X_Revenue_Limit_Flag IN VARCHAR2,
2592 X_Invoice_Limit_Flag IN VARCHAR2, /*Bug 6642901*/
2593 X_Billing_Frequency IN VARCHAR2,
2597 X_Ship_To_Address_Id_IN IN NUMBER,
2594 X_billing_cycle_id IN NUMBER,
2595 X_Billing_offset IN NUMBER,
2596 X_Bill_To_Address_Id_IN IN NUMBER,
2598 X_Bill_To_Contact_Id_IN IN NUMBER,
2599 X_Ship_To_Contact_Id_IN IN NUMBER,
2600 X_output_tax_code IN VARCHAR2,
2601 X_retention_tax_code IN VARCHAR2,
2602 X_ORG_ID IN NUMBER, --Shared Service Enhancement
2603 X_Award_Project_Id OUT NOCOPY NUMBER,
2604 X_Agreement_Id OUT NOCOPY NUMBER,
2605 X_Bill_To_Address_Id_OUT OUT NOCOPY NUMBER,
2606 X_Ship_To_Address_Id_OUT OUT NOCOPY NUMBER,
2607 X_App_Short_Name OUT NOCOPY VARCHAR2,
2608 X_Msg_Count OUT NOCOPY NUMBER,
2609 RETCODE OUT NOCOPY VARCHAR2,
2610 ERRBUF OUT NOCOPY VARCHAR2)IS
2611
2612 X_Row_Id VARCHAR2(30) := '';
2613
2614 X_Awd_Proj_Name VARCHAR2(30);
2615 X_Awd_Proj_Number VARCHAR2(25) ;
2616
2617 X_Err_Code VARCHAR2(1) := NULL;
2618 X_Err_Stage VARCHAR2(2000) := NULL;
2619
2620 Store_Project_Id NUMBER := NULL;
2621 Store_Bill_To_Addr_Id NUMBER := NULL;
2622 Store_Ship_To_Addr_Id NUMBER := NULL;
2623 Store_Agreement_Id NUMBER(15) := NULL;
2624
2625 --This variable will help debugging when exception occurs
2626 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_AWARD_PROJECT';
2627
2628 BEGIN
2629
2630 G_stage := '(100:Initialize)';
2631
2632 --dbms_output.put_line('AJ01:just after begin');
2633 FND_MSG_PUB.Initialize;
2634
2635 --dbms_output.put_line('AJ02:just after FND_MSG_PUB');
2636
2637 X_Awd_Proj_Name := X_Project_Name; --||'-A';
2638 X_Awd_Proj_Number := X_Project_Number; --||'-A';
2639
2640 G_stage := '(110:Calling award_project_not_exists)';
2641
2642 ---Shared Service Enhancement
2643 --Initialising the context
2644 MO_GLOBAL.SET_POLICY_CONTEXT('S',X_ORG_ID);
2645 --End of Shared Service
2646 -- Check to see if Award Project already exists for Agreement specified -
2647 IF AWARD_PROJECT_NOT_EXISTS(X_Award_Id,
2648 X_Err_Code,
2649 X_Err_Stage) THEN
2650
2651 G_Stage := '(120:Calling proj_name_num_unique)';
2652
2653 --dbms_output.put_line('After Award Project Exists Check'||X_Err_Stage);
2654 --Check to see if Name of Award Project is Unique
2655 IF PROJ_NAME_NUM_UNIQUE(X_Awd_Proj_Number,
2656 X_Awd_Proj_Name,
2657 X_Err_Code,
2658 X_Err_Stage) THEN
2659
2660 G_Stage := '(130:Calling insert_award_project)';
2661 --dbms_output.put_line('After Project Name Number Unique Check'||X_Err_Stage);
2662 --dbms_output.put_line('AJ03:b4 INSERT_AWARD_PROJECT');
2663 INSERT_AWARD_PROJECT(X_Customer_Id,
2664 X_Bill_to_customer_id,
2665 X_Awd_Proj_Name,
2666 X_Awd_Proj_Number,
2667 X_Award_Id,
2668 X_Carrying_Out_Organization_Id,
2669 X_IDC_Schedule_Id,
2670 X_IDC_Schedule_Fixed_Date,
2671 X_Labor_Invoice_Format_Id,
2672 X_Non_Labor_Invoice_Format_Id,
2673 X_Start_Date,
2674 X_End_Date,
2675 X_Close_Date,
2676 X_Person_Id,
2677 X_Billing_Frequency,
2678 X_Billing_Cycle_Id,
2679 X_Billing_Offset,
2680 Store_Project_Id,
2681 Store_Bill_To_Addr_Id,
2682 Store_Ship_To_Addr_Id,
2683 X_App_Short_Name,
2684 X_Err_Code,
2685 X_Err_Stage);
2686 --dbms_output.put_line('AJ03:b4 INSERT_AWARD_PROJECT');
2687 --dbms_output.put_line('After Insert Project'||X_Err_Stage);
2688 IF X_Err_Code <> 'S' THEN
2689 X_Award_Project_Id := -1;
2690 RAISE FND_API.G_EXC_ERROR;
2691 ELSE
2692 RETCODE := X_Err_Code;
2693 X_Award_Project_Id := Store_Project_Id;
2694 IF X_Bill_To_Address_Id_IN is NULL THEN
2695 X_Bill_To_Address_Id_OUT := Store_Bill_To_Addr_Id;
2696 ELSE
2697 X_Bill_To_Address_Id_OUT := NULL;
2698 END IF;
2699
2700 IF X_Ship_To_Address_Id_IN is NULL THEN
2701 X_Ship_To_Address_Id_OUT := Store_Ship_To_Addr_Id;
2702 ELSE
2703 X_Ship_To_Address_Id_OUT := NULL;
2704 END IF;
2705
2706 END IF;
2707
2708 ELSE
2709 IF X_Err_Code <> 'S' THEN
2710 X_App_Short_Name := 'GMS';
2711 RAISE FND_API.G_EXC_ERROR;
2712 ELSE
2713 RETCODE := X_Err_Code;
2714 END IF;
2715 END IF;
2716 ELSE
2717 IF X_Err_Code <> 'S' THEN
2718 X_App_Short_Name := 'GMS';
2719 RAISE FND_API.G_EXC_ERROR;
2720 ELSE
2721 RETCODE := X_Err_Code;
2722 END IF;
2723 END IF;
2724
2725 ---------------------------------------------------------------------------
2726 /* Updating PA_PROJECT_CUSTOMERS with the Bill_To_Address_Id and Ship_To_Address_Id being used in Award Template */
2727
2728 G_Stage := '(140:Updating pa_project_customers)';
2729 IF X_Bill_To_Address_Id_IN IS NOT NULL THEN
2730 UPDATE PA_PROJECT_CUSTOMERS
2731 set BILL_TO_ADDRESS_ID = X_Bill_To_Address_Id_IN
2732 ,LAST_UPDATE_DATE = SYSDATE
2733 ,LAST_UPDATED_BY = fnd_global.user_id
2734 ,LAST_UPDATE_LOGIN = fnd_global.login_id
2735 WHERE project_id = Store_Project_Id
2736 and customer_id = X_Customer_Id;
2737 END IF;
2738
2739 G_Stage := '(150:Updating pa_project_customers)';
2740 IF X_Ship_To_Address_Id_IN IS NOT NULL THEN
2741 UPDATE PA_PROJECT_CUSTOMERS
2742 set SHIP_TO_ADDRESS_ID = X_Ship_To_Address_Id_IN
2743 ,LAST_UPDATE_DATE = SYSDATE
2744 ,LAST_UPDATED_BY = fnd_global.user_id
2745 ,LAST_UPDATE_LOGIN = fnd_global.login_id
2746 WHERE project_id = Store_Project_Id
2747 and customer_id = X_Customer_Id;
2748 END IF;
2749 ---------------------------------------------------------------------------
2750
2751 ---------------------------------------------------------------------------
2752 G_Stage := '(160:Updating pa_project_contacts)';
2753 /* Updating PA_PROJECT_CONTACTS with the contacts got FROM Award Template */
2754 IF X_Bill_To_Contact_Id_IN IS NOT NULL THEN
2755
2756 UPDATE PA_PROJECT_CONTACTS
2757 set contact_id = X_Bill_To_Contact_Id_IN
2758 ,LAST_UPDATE_DATE = SYSDATE
2759 ,LAST_UPDATED_BY = fnd_global.user_id
2760 ,LAST_UPDATE_LOGIN = fnd_global.login_id
2761 WHERE project_id = Store_Project_Id
2762 and customer_id = X_Customer_Id
2763 and project_contact_type_code = 'BILLING';
2764
2765 END IF;
2766
2767 G_Stage := '(170:Updating pa_project_contacts)';
2768 IF X_Ship_To_Contact_Id_IN IS NOT NULL THEN
2769
2770 UPDATE PA_PROJECT_CONTACTS
2771 set contact_id = X_Ship_To_Contact_Id_IN
2772 ,LAST_UPDATE_DATE = SYSDATE
2773 ,LAST_UPDATED_BY = fnd_global.user_id
2774 ,LAST_UPDATE_LOGIN = fnd_global.login_id
2775 WHERE project_id = Store_Project_Id
2776 and customer_id = X_Customer_Id
2777 and project_contact_type_code = 'SHIPPING';
2778
2779 END IF;
2780 ------------------------------------------------------------------------
2781
2782 ------------------------------------------------------------------------
2783 G_Stage := '(180:Calling modify_gms_contacts)';
2784 /* Create GMS Contacts */
2785 MODIFY_GMS_CONTACTS(X_Award_Id,
2786 Store_Project_Id,
2787 X_Customer_Id,
2788 X_Bill_to_Customer_id,
2789 X_Bill_To_Contact_Id_IN,
2790 X_Ship_To_Contact_Id_IN,
2791 X_Err_Code,
2792 X_Err_Stage);
2793 IF X_Err_Code <> 'S' THEN
2794 X_App_Short_Name := 'GMS';
2795 RAISE FND_API.G_EXC_ERROR;
2796 ELSE
2797 RETCODE := X_Err_Code;
2798 END IF;
2799
2800 ---------------------------------------
2801
2802
2803 /* Create Award Budget Type */
2804 -- this procedure is not used in 11i as there is no award_budget_type -- Suresh
2805 /*
2806 INSERT_AWARD_BUDGET_TYPE(X_Budget_Type_Code => to_char(X_Award_Id),
2807 X_Budget_Type => X_Project_Name,
2808 X_Start_Date => to_date('01011951','DDMMYYYY'),
2809 X_End_Date => NULL,
2810 X_Predefined_Flag => 'N',
2811 X_Accumulation_Flag => 'N',
2812 X_Award_Flag => 'Y',
2813 X_Err_Code => X_Err_Code,
2814 X_Err_Stage => X_Err_Stage) ;
2815
2816 IF X_Err_Code <> 'S' THEN
2817 X_App_Short_Name := 'GMS';
2818 RAISE FND_API.G_EXC_ERROR;
2819 ELSE
2820 RETCODE := X_Err_Code;
2821 END IF;
2822 */
2823
2824 G_Stage := '(190:Calling gms_multi_funding.create_agreement)';
2825 /* Create an Agreement for the Award */
2826 GMS_MULTI_FUNDING.CREATE_AGREEMENT(X_Row_Id,
2827 Store_Agreement_Id,
2828 X_Customer_Id,
2829 X_Awd_Proj_Number,
2830 X_Agreement_Type,
2831 X_Revenue_Limit_Flag,
2832 X_Invoice_Limit_Flag, /*Bug 6642901*/
2833 X_Person_Id,
2834 X_Term_Id,
2835 X_Close_Date,
2836 X_ORG_ID, --Shared Service Enhancement
2837 X_Err_Code,
2838 X_Err_Stage) ;
2839 IF X_Err_Code <> 'S' THEN
2840 X_Agreement_Id := -1;
2841 RAISE FND_API.G_EXC_ERROR;
2842 ELSE
2843 RETCODE := X_Err_Code;
2844 X_Agreement_Id := Store_Agreement_Id;
2845 END IF;
2846
2847
2848 G_Stage := '(200:Calling update_gms_awards)';
2849 /* UPDATE GMS_AWARDS with the Agreement_Id and Award_Project_Id */
2850 UPDATE_GMS_AWARDS(X_Award_Id,
2851 Store_Agreement_Id,
2852 Store_Project_Id);
2853
2854 EXCEPTION
2855 WHEN FND_API.G_EXC_ERROR THEN
2856 --dbms_output.put_line('P MSG COUNT '||p_msg_count);
2857 X_Msg_Count := p_msg_count;
2858 RETCODE := X_Err_Code;
2859 ERRBUF := X_Err_Stage;
2860 --dbms_output.put_line('Returning after Exception');
2861 --dbms_output.put_line('projID:' || Store_Project_Id);
2862 --dbms_output.put_line('agreementiD:' || Store_Agreement_Id);
2863 --dbms_output.put_line('ret:' || X_Err_Code);
2864 --dbms_output.put_line('error:' || X_Err_Stage);
2865
2866 -- Added When OTHERS exception for Bug:2662848
2867 WHEN OTHERS THEN
2868 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2869 X_Award_Project_Id := -1;
2870 X_Agreement_Id := -1;
2871 X_Bill_To_Address_Id_OUT := null;
2872 X_Ship_To_Address_Id_OUT := null;
2873
2874 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2875 FND_MSG_PUB.add_exc_msg
2876 ( p_pkg_name => G_PKG_NAME
2877 ,p_procedure_name => l_api_name
2878 ,p_error_text => substrb(SQLERRM||G_Stage,1,240)
2879 );
2880 END IF;
2881 FND_MSG_PUB.Count_And_Get
2882 ( p_count => x_msg_count
2883 ,p_data => X_Err_Stage
2884 );
2885 ERRBUF := 'At stage='||g_stage||' '||X_Err_Stage;
2886
2887 --Please do not "RAISE" here
2888 --Instead make sure by checkng RETCODE after the call
2889
2890 END CREATE_AWARD_PROJECT;
2891
2892 PROCEDURE UPDATE_KEY_MEMBERS(X_Award_Project_Id IN NUMBER,
2893 X_Person_Id_Old IN NUMBER,
2894 X_Person_Id_New IN NUMBER,
2895 X_Start_Date IN DATE,
2896 X_End_Date IN DATE,
2897 X_Err_Code OUT NOCOPY VARCHAR2,
2898 X_Err_Stage OUT NOCOPY VARCHAR2) IS
2899 St_Start_Date DATE;
2900 St_End_Date DATE;
2901 BEGIN
2902
2903 BEGIN
2904 Select
2905 start_date_active,
2906 end_date_active
2907 INTO
2908 St_Start_Date,
2909 St_End_Date
2910 FROM PA_PROJECT_PLAYERS
2911 WHERE PROJECT_ID = X_Award_Project_Id
2912 and PERSON_ID = X_Person_Id_Old
2913 and PROJECT_ROLE_TYPE = 'PROJECT MANAGER';
2914
2915 X_Err_Code := 'S';
2916
2917 EXCEPTION
2918 WHEN NO_DATA_FOUND THEN
2919 X_Err_Code := 'E';
2920 FND_MESSAGE.SET_NAME('GMS','GMS_PROJ_MANAGER_NOT_FOUND');
2921 FND_MSG_PUB.add;
2922 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2923 p_data => X_Err_Stage);
2924 RETURN;
2925 WHEN TOO_MANY_ROWS THEN
2926 X_Err_Code := 'E';
2927 FND_MESSAGE.SET_NAME('GMS','GMS_MULTI_PROJ_MANAGER_FOUND');
2928 FND_MSG_PUB.add;
2929 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2930 p_data => X_Err_Stage);
2931 RETURN;
2932
2933 END;
2934
2935 IF ((X_Person_Id_Old <> X_Person_Id_New) OR
2936 (St_Start_Date <> X_Start_Date) OR
2937 (St_End_Date <> X_End_Date) OR
2938 (St_End_Date IS NULL )
2939 ) THEN
2940 /* Commented out NOCOPY for bug fix 1533504 as this a view now in 11i
2941 UPDATE pa_project_players
2942 set person_id = X_Person_Id_New,
2943 start_date_active = X_Start_Date,
2944 end_date_active = X_End_Date
2945 WHERE
2946 project_id = X_Award_Project_Id
2947 and person_id = X_Person_Id_Old
2948 and project_role_type = 'PROJECT MANAGER';
2949 */
2950
2951 UPDATE pa_project_parties
2952 set resource_source_id = X_Person_Id_New,
2953 start_date_active = X_Start_Date,
2954 end_date_active = X_End_Date
2955 WHERE
2956 project_id = X_Award_Project_Id
2957 and resource_source_id = X_Person_Id_Old
2958 and project_role_id in (SELECT project_role_id FROM pa_project_role_types
2959 WHERE project_role_type = 'PROJECT MANAGER');
2960
2961 X_Err_Code := 'S';
2962
2963 IF SQL%NOTFOUND THEN
2964 X_Err_Code := 'E';
2965 FND_MESSAGE.SET_NAME('GMS','GMS_PROJ_MANAGER_NOT_FOUND');
2966 FND_MSG_PUB.add;
2967 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
2968 p_data => X_Err_Stage);
2969 RETURN;
2970 END IF;
2971
2972
2973 ELSE
2974
2975 X_Err_Code := 'S';
2976
2977 END IF;
2978
2979 -- Added Exception for Bug:2662848
2980 Exception
2981 When OTHERS THEN
2982 RAISE;
2983 END UPDATE_KEY_MEMBERS;
2984
2985
2986 PROCEDURE update_award_project
2987 ( x_award_id IN NUMBER
2988 ,x_award_project_id IN NUMBER
2989 ,x_agreement_id IN NUMBER
2990 ,x_project_number IN VARCHAR2
2991 ,x_project_name IN VARCHAR2
2992 ,X_Customer_Id IN NUMBER
2993 ,X_Bill_to_Customer_Id IN NUMBER
2994 ,X_Carrying_Out_Organization_Id IN NUMBER
2995 ,X_IDC_Schedule_Id IN NUMBER
2996 ,X_IDC_Schedule_Fixed_Date IN DATE
2997 ,X_Labor_Invoice_Format_Id IN NUMBER
2998 ,X_Non_Labor_Invoice_Format_Id IN NUMBER
2999 ,X_Person_Id_Old IN NUMBER
3000 ,X_Person_Id_New IN NUMBER
3001 ,X_Term_Id IN NUMBER
3002 ,X_Start_Date IN DATE
3003 ,X_End_Date IN DATE
3004 ,X_Close_Date IN DATE
3005 ,X_Agreement_Type IN VARCHAR2
3006 ,X_Revenue_Limit_Flag IN VARCHAR2
3007 ,X_Invoice_Limit_Flag IN VARCHAR2 /*Bug 6642901*/
3008 ,X_Billing_Frequency IN VARCHAR2
3009 ,X_Billing_cycle_Id IN NUMBER
3010 ,X_Billing_Offset IN NUMBER
3011 ,X_Bill_To_Address_Id_IN IN NUMBER
3012 ,X_Ship_To_Address_Id_IN IN NUMBER
3013 ,X_output_tax_code IN VARCHAR2
3014 ,X_retention_tax_code IN VARCHAR2
3015 ,X_Bill_To_Address_Id_OUT OUT NOCOPY NUMBER
3016 ,X_Ship_To_Address_Id_OUT OUT NOCOPY NUMBER
3017 ,X_App_Short_Name OUT NOCOPY VARCHAR2
3018 ,X_Msg_Count OUT NOCOPY NUMBER
3019 ,retcode OUT NOCOPY VARCHAR2
3020 ,errbuf OUT NOCOPY VARCHAR2
3021 ) IS
3022
3023 X_Product_Code VARCHAR2(30);
3024
3025 X_Err_Code VARCHAR2(1);
3026 X_Err_Stage VARCHAR2(2000);
3027 X_Text VARCHAR2(200);
3028 x_err_stack VARCHAR2(2000);
3029 x_return_status VARCHAR2(1);
3030 x_msg_data VARCHAR2(2000);
3031
3032 X_Index NUMBER(15);
3033
3034 X_Created_From_Project_Id NUMBER;
3035
3036 X_Project_IN_REC PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
3037 X_Project_OUT_REC PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE;
3038 X_Key_Members_IN_REC PA_PROJECT_PUB.PROJECT_ROLE_REC_TYPE;
3039 X_Key_Members_IN_TBL PA_PROJECT_PUB.PROJECT_ROLE_TBL_TYPE;
3040 X_Class_Categories_IN_TBL PA_PROJECT_PUB.CLASS_CATEGORY_TBL_TYPE;
3041 X_Tasks_IN_REC PA_PROJECT_PUB.TASK_IN_REC_TYPE;
3042 X_Tasks_In_TBL PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
3043 X_Tasks_Out_TBL PA_PROJECT_PUB.TASK_OUT_TBL_TYPE;
3044
3045 --Bug 3576717
3046 X_Deliverable_IN_TBL PA_PROJECT_PUB.DELIVERABLE_IN_TBL_TYPE;
3047
3048 -- Bug 3650374
3049 --X_Deliverable_OUT_TBL PA_PROJECT_PUB.DELIVERABLE_OUT_TBL_TYPE;
3050 --X_Deliverable_Action_OUT_TBL PA_PROJECT_PUB.ACTION_OUT_TBL_TYPE;
3051
3052 X_Deliverable_Action_IN_TBL PA_PROJECT_PUB.ACTION_IN_TBL_TYPE;
3053
3054 X_Awd_Proj_Name VARCHAR2(240);
3055 X_Awd_Proj_Number VARCHAR2(30);
3056
3057 --St_Award_Project_Id NUMBER(15);
3058 St_Billing_Offset NUMBER(15);
3059 St_Billing_Cycle NUMBER(15);
3060 St_Revenue_Limit_Flag VARCHAR2(1);
3061 St_Invoice_Limit_Flag VARCHAR2(1); /*Bug 6642901*/
3062 X_Task_ID_To_UPDATE NUMBER(15);
3063 X_Task_Proj_Compl_Date DATE;
3064 X_Task_Proj_Start_Date DATE;
3065
3066 X_Task_ID_OUT NUMBER(15);
3067 X_Task_PM_Reference_OUT VARCHAR2(30);
3068
3069 St_Bill_To_Address_Id NUMBER(15);
3070 St_Ship_To_Address_Id NUMBER(15);
3071 X_Bill_To_Contact_Id NUMBER(15);
3072 X_Ship_To_Contact_Id NUMBER(15);
3073
3074 X_Bill_To_Address_PASSED NUMBER(15);
3075 X_Ship_To_Address_PASSED NUMBER(15);
3076
3077 X_Workflow_Started VARCHAR2(1); -- R11 Changes
3078 x_default_org_id VARCHAR2(15);
3079
3080 --Bug Fix 2994625
3081
3082 -- Bug Fix 3062140
3083 -- The declaration of l_ship_to_customer_id is causing ora 6502
3084 -- The issue is it is declared as %type that means it is number 15 and it is
3085 -- initialized with g_pa_miss_num which is initialized to a really big number
3086 -- so stopped initializing to the g_pa_miss_num.
3087
3088 l_bill_to_customer_id pa_project_customers.bill_to_customer_id%TYPE := X_bill_to_customer_id;
3089 l_ship_to_customer_id pa_project_customers.ship_to_customer_id%TYPE := X_bill_to_customer_id ;
3090
3091 --END Of fix 3062140
3092
3093 l_err_code NUMBER;
3094 bill_to_contact_exists number;
3095 ship_to_contact_exists number;
3096
3097 --This will help debugging when exception occurs
3098 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_AWARD_PROJECT';
3099
3100 l_org_id NUMBER; --Shared Service Enhancement
3101
3102 BEGIN
3103
3104 G_Stage := '(300:Calling fnd_msg_pub.initialize)';
3105 FND_MSG_PUB.Initialize;
3106 ---Shared Service Enhancement
3107 --Get the org_id from award_project_id being passed
3108 --Set the ORg context
3109 SELECT org_id
3110 INTO l_org_id
3111 FROM gms_awards_all
3112 WHERE award_id = X_award_id;
3113
3114 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);
3115 --End of Shared Service enhancement
3116
3117 --Getting Org Id
3118 SELECT TO_CHAR(NVL(org_id,-999))
3119 INTO x_default_org_id
3120 FROM gms_implementations;
3121
3122 G_Stage := '(310:select from pa_projects)';
3123
3124 --Get X_Created_From_Project_Id
3125 --Bug Fix 2447491:
3126 --After upgrading to multi org FROM non multi org, the award project
3127 --template will still have segment1 'AWD_PROJ_-999. To use the template
3128 --the code is modified.
3129
3130 BEGIN
3131
3132 SELECT project_id
3133 INTO X_Created_From_Project_Id
3134 FROM PA_PROJECTS
3135 WHERE project_type = 'AWARD_PROJECT'
3136 AND template_flag = 'Y'
3137 AND (segment1 = 'AWD_PROJ_'||x_default_org_id
3138 OR
3139 segment1 = 'AWD_PROJ_-999'
3140 );
3141
3142 X_Err_Code := 'S';
3143 EXCEPTION
3144 WHEN OTHERS THEN
3145
3146 X_Err_Code := FND_API.G_RET_STS_UNEXP_ERROR; --'U';
3147 X_Err_Stage := SQLERRM;
3148 X_App_Short_Name := 'GMS';
3149 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
3150 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_AWARD_PROJECT');
3151 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
3152 FND_MESSAGE.SET_TOKEN('OERRRM',SQLERRM);
3153 FND_MSG_PUB.add;
3154 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
3155 p_data => X_Err_Stage);
3156 RAISE FND_API.G_EXC_ERROR;
3157 END;
3158
3159 X_Awd_Proj_Name := X_Project_Name;
3160 X_Awd_Proj_Number := X_Project_Number;
3161
3162 G_Stage := '(320:Select from pa_tasks)';
3163
3164 --Getting the Only Task ID and its Completion Date (also the Project Completion Date)
3165 --of this Award Project to UPDATE Task Information
3166
3167 BEGIN
3168
3169 SELECT task_id, trunc(start_date), trunc(completion_date)
3170 INTO X_Task_ID_To_UPDATE, X_Task_Proj_Start_Date, X_Task_Proj_Compl_Date
3171 FROM pa_tasks
3172 WHERE project_id = X_Award_Project_Id;
3173
3174 EXCEPTION
3175 WHEN OTHERS THEN
3176
3177 X_Err_Code := 'U';
3178 X_Err_Stage := SQLERRM;
3179 X_App_Short_Name := 'GMS';
3180
3181 RAISE;
3182 END;
3183 -- For Bug 3061336 : Moved the get_customer_info up to get the correct values of bill_to_customer_id
3184 -- and ship_to_customer_id
3185 -- Commented for Bug 1656812
3186 -- Bug Fix 2994625
3187 -- This will blindly returns all the details for one customer.
3188 -- Where as we need the billing address and contact FROM LOC if it exists
3189 -- and rest of the details FROM the funding source.
3190 -- We can use pa_project_customer_get_customer_info which exactly does that.
3191
3192 G_Stage := '(330:Calling pa_customer_info.get_customer_info)';
3193 pa_customer_info.get_customer_info
3194 ( x_project_id => null
3195 ,x_customer_id => x_customer_id
3196 ,x_bill_to_customer_id => l_bill_to_customer_id
3197 ,x_ship_to_customer_id => l_ship_to_customer_id
3198 ,x_bill_to_address_id => st_bill_to_address_id
3199 ,x_ship_to_address_id => st_ship_to_address_id
3200 ,x_bill_to_contact_id => X_bill_to_contact_id
3201 ,x_ship_to_contact_id => X_ship_to_contact_id
3202 ,x_err_code => l_err_code
3203 ,x_err_stage => X_err_stage
3204 ,x_err_stack => X_err_stack
3205 );
3206
3207 --Initializing the Tables
3208 X_Product_Code := 'GMS';
3209 X_Project_IN_REC.PA_PROJECT_ID := X_Award_Project_Id;
3210 X_Project_IN_REC.PM_PROJECT_REFERENCE := X_Awd_Proj_Number;
3211
3212 --dbms_output.put_line('Project Number before for UPDATE is '||X_Awd_Proj_Number);
3213
3214 X_Project_IN_REC.PA_PROJECT_NUMBER := X_Awd_Proj_Number;
3215
3216 --dbms_output.put_line('Project Number after for UPDATE is '||X_Project_IN_REC.PA_PROJECT_NUMBER);
3217
3218 X_Project_IN_REC.PROJECT_NAME := X_Awd_Proj_Name;
3219 X_Project_IN_REC.CREATED_FROM_PROJECT_ID := X_Created_From_Project_Id;
3220 X_Project_IN_REC.CARRYING_OUT_ORGANIZATION_ID := X_Carrying_Out_Organization_Id;
3221 X_Project_IN_REC.CUSTOMER_ID := X_Customer_Id;
3222 X_Project_IN_REC.DISTRIBUTION_RULE := 'EVENT/EVENT';
3223 X_Project_IN_REC.PROJECT_RELATIONSHIP_CODE := 'PRIMARY';
3224 X_Project_IN_REC.OUTPUT_TAX_CODE := x_output_tax_code;
3225 X_Project_IN_REC.RETENTION_TAX_CODE := x_retention_tax_code;
3226
3227 /*=======================================================================================================
3228 The Key Member information will be UPDATEd separately inorder to prevent Multiple Project Managers FROM
3229 being created.
3230
3231 X_Key_Members_IN_REC.PERSON_ID := X_Person_Id;
3232 X_Key_Members_IN_REC.PROJECT_ROLE_TYPE := 'PROJECT MANAGER';
3233 X_Key_Members_IN_REC.START_DATE := X_Start_Date;
3234 X_Key_Members_IN_REC.END_DATE := X_End_Date;
3235
3236
3237 X_Key_Members_IN_TBL(1) := X_Key_Members_IN_REC;
3238 =========================================================================================================
3239 */
3240
3241 --NOTE: Column requires change should be passed to these APIs.
3242 -- e.g. With respect to Task changes, task_name, number or
3243 -- references always remain the same. Only passing task_id
3244 -- and other information requested for a change.
3245
3246 --Bug 3598343
3247 --introduced by sanbaner
3248 X_Tasks_IN_REC.pa_task_id := X_Task_ID_To_UPDATE ;
3249
3250 --commented by sanbaner
3251 --X_Tasks_IN_REC.task_name := X_Awd_Proj_Number||'-'||'Tsk1'; --X_Task_Name;
3252 --X_Tasks_IN_REC.pa_task_number := X_Awd_Proj_Number||'-'||'T1'; --X_Task_Number;
3253 --X_Tasks_IN_REC.pm_task_reference := X_Awd_Proj_Number;
3254
3255 X_Tasks_IN_REC.cost_ind_rate_sch_id := X_IDC_Schedule_Id;
3256 X_Tasks_IN_REC.TASK_START_DATE := X_Start_Date;
3257 X_Tasks_IN_REC.TASK_COMPLETION_DATE := X_End_Date;
3258
3259
3260 --dbms_output.put_line('First I got here !!!');
3261
3262 /*-------- First Call to UPDATE_PROJECT ----------*/
3263 X_Project_IN_REC.START_DATE := LEAST(X_Start_Date,X_Task_Proj_Start_Date);
3264 X_Project_IN_REC.COMPLETION_DATE := GREATEST(X_End_Date,X_Task_Proj_Compl_Date);
3265
3266 --dbms_output.put_line('Project+++++ Number '||X_Project_IN_REC.PA_PROJECT_NUMBER);
3267
3268 /** Bug 3547727 : Start
3269 ** Task was not passed. PA_PROJECT_PUB.UPDATE_PROJECT will raise an error PA_TASK_REF_AND_ID_MISSING
3270 ** when task_id and task_reference both are NULL
3271 **/
3272 X_Tasks_IN_TBL(1) := X_Tasks_IN_REC;
3273
3274 /** Bug 3547727 : End
3275 **/
3276
3277 x_project_in_rec.bill_to_customer_id := l_bill_to_customer_id;
3278 x_project_in_rec.ship_to_customer_id := l_ship_to_customer_id; --Modified for bug 3061336
3279
3280 x_project_in_rec.bill_to_address_id := X_Bill_To_Address_Id_IN; --Added for bug 3977859
3281 x_project_in_rec.ship_to_address_id := X_Ship_To_Address_Id_IN; --Added for bug 3977859
3282
3286 ,p_init_msg_list => 'T'
3283 G_Stage := '(340:Calling pa_project_pub.update_project)';
3284 PA_PROJECT_PUB.UPDATE_PROJECT
3285 ( p_api_version_number => 1.0
3287 ,p_msg_count => p_msg_count
3288 ,p_msg_data => X_Err_Stage
3289 ,p_return_status => X_Err_Code
3290 ,p_project_in => X_Project_IN_REC
3291 ,p_project_out => X_Project_OUT_REC
3292 ,p_pm_product_code => X_Product_Code
3293 ,p_key_members => X_Key_Members_IN_TBL --Key Members will be UPDATEd separately
3294 ,p_class_categories => X_Class_Categories_IN_TBL
3295 ,p_tasks_in => X_Tasks_IN_TBL
3296 ,p_tasks_out => X_Tasks_OUT_TBL
3297 ,p_workflow_started => X_Workflow_Started
3298 --Bug 3576717
3299 ,p_deliverables_in => X_Deliverable_IN_TBL
3300 --,p_deliverables_out => X_Deliverable_OUT_TBL (3650374)
3301 ,p_deliverable_actions_in => X_Deliverable_Action_IN_TBL
3302 --,p_deliverable_actions_out => X_Deliverable_Action_OUT_TBL (3650374)
3303 );
3304
3305 --dbms_output.put_line('THEN I got here !!!');
3306 IF X_Err_Code <> 'S' THEN
3307 RAISE FND_API.G_EXC_ERROR;
3308 ELSE
3309 RETCODE := X_Err_Code;
3310 END IF;
3311 --Bug 3598343 : Commented
3312 --Not required as pa_project_pub.update_project is already doing.
3313 /******
3314
3315 G_Stage := '(350:calling pa_project_pub.update_task)';
3316 -- UPDATE Task Information to take care of any Project Incompatibility
3317 PA_PROJECT_PUB.UPDATE_TASK
3318 (p_api_version_number => 1.0,
3319 p_pa_project_id => X_Award_Project_Id,
3320 p_pa_task_id => X_Task_Id_To_UPDATE,
3321 p_init_msg_list => 'T',
3322 p_msg_count => p_msg_count,
3323 p_msg_data => X_Err_Stage,
3324 p_return_status => X_Err_Code,
3325 p_pm_project_reference => X_Awd_Proj_Number,
3326 p_pm_task_reference => X_Awd_Proj_Number,
3327 p_pm_product_code => X_Product_Code,
3328 p_task_number => X_Awd_Proj_Number||'-'||'T1',
3329 p_task_name => X_Awd_Proj_Number||'-'||'Tsk1',
3330 p_task_start_date => X_Start_Date,
3331 p_task_completion_date => X_End_Date,
3332 p_out_pa_task_id => X_Task_ID_OUT,
3333 p_out_pm_task_reference => X_Task_PM_Reference_OUT);
3334
3335 IF X_Err_Code <> 'S' THEN
3336 FND_MESSAGE.PARSE_ENCODED
3337 ( encoded_message => X_Err_Stage,
3338 APP_SHORT_NAME => X_App_Short_Name,
3339 MESSAGE_NAME => X_Text
3340 );
3341
3342 RAISE FND_API.G_EXC_ERROR;
3343 ELSE
3344 RETCODE := X_Err_Code;
3345 END IF;
3346 *****/
3347
3348 /* ----------------Second Call to UPDATE_PROJECT-----------------------*/
3349
3350 X_Project_IN_REC.START_DATE := X_Start_Date;
3351 X_Project_IN_REC.COMPLETION_DATE := X_End_Date;
3352
3353 -- commented by syb
3354 --
3355 -- G_Stage := '(360:Calling pa_project_pub.update_project)';
3356 -- PA_PROJECT_PUB.UPDATE_PROJECT
3357 -- ( p_api_version_number => 1.0,
3358 -- p_init_msg_list => 'T',
3359 -- p_msg_count => p_msg_count,
3360 -- p_msg_data => X_Err_Stage,
3361 -- p_return_status => X_Err_Code,
3362 -- p_project_in => X_Project_IN_REC,
3363 -- p_project_out => X_Project_OUT_REC,
3364 -- p_pm_product_code => X_Product_Code,
3365 -- p_key_members => X_Key_Members_IN_TBL, --Key Members will be UPDATEd separately
3366 -- p_class_categories => X_Class_Categories_IN_TBL,
3367 -- p_tasks_in => X_Tasks_IN_TBL,
3368 -- p_tasks_out => X_Tasks_OUT_TBL,
3369 -- p_workflow_started => X_Workflow_Started,
3370 -- --Bug 3576717
3371 -- p_deliverables_in => X_Deliverable_IN_TBL,
3372 -- p_deliverables_out => X_Deliverable_OUT_TBL,
3373 -- p_deliverable_actions_in => X_Deliverable_Action_IN_TBL,
3374 -- p_deliverable_actions_out=> X_Deliverable_Action_OUT_TBL
3375 -- );
3376
3377 --dbms_output.put_line('THEN I got here !!!');
3378 IF X_Err_Code <> 'S' THEN
3379 RAISE FND_API.G_EXC_ERROR;
3380 ELSE
3381 RETCODE := X_Err_Code;
3382 END IF;
3383 -----------------------------------------------------------------------------------------------
3384
3385
3386 --dbms_output.put_line('The Project that got created is 1 '||to_char(X_Project_OUT_REC.pa_project_id));
3387 --dbms_output.put_line('The Project that got created is 2'||to_char(X_Award_Project_Id));
3388
3389 --St_Award_Project_Id := X_Award_Project_Id;
3390
3391 G_Stage := '(370:Calling update_project_add_info)';
3395 X_IDC_Schedule_Fixed_Date,
3392 UPDATE_PROJECT_ADD_INFO
3393 ( X_Award_Project_Id,
3394 X_IDC_Schedule_Id,
3396 X_Labor_Invoice_Format_Id,
3397 X_Non_Labor_Invoice_Format_Id,
3398 X_Billing_Cycle_Id,
3399 X_Billing_Offset,
3400 X_Err_Code,
3401 X_Err_Stage
3402 );
3403 IF X_Err_Code <> 'S' THEN
3404 X_App_Short_Name := 'GMS';
3405 RAISE FND_API.G_EXC_ERROR;
3406 ELSE
3407 RETCODE := X_Err_Code;
3408 END IF;
3409
3410 -- Commented for Bug 1656812
3411 /* UPDATE_KEY_MEMBERS
3412 (X_Award_Project_Id,
3413 X_Person_Id_Old,
3414 X_Person_Id_New,
3415 X_Start_Date,
3416 X_End_Date,
3417 X_Err_Code,
3418 X_Err_Stage);
3419 IF X_Err_Code <> 'S' THEN
3420 X_App_Short_Name := 'GMS';
3421 RAISE FND_API.G_EXC_ERROR;
3422 ELSE
3423 RETCODE := X_Err_Code;
3424 END IF;
3425 --dbms_output.put_line('Successfully UPDATEd Key Members ');
3426 */
3427 /*****************
3428 For bug 3061336 :Commenting the code . Moved the code up
3429 -- Commented for Bug 1656812
3430 -- Bug Fix 2994625
3431 -- This will blindly returns all the details for one customer.
3432 -- Where as we need the billing address and contact FROM LOC if it exists
3433 -- and rest of the details FROM the funding source.
3434 -- We can use pa_project_customer_get_customer_info which exactly does that.
3435
3436 pa_customer_info.get_customer_info
3437 ( X_PROJECT_ID => NULL,
3438 X_CUSTOMER_ID => X_Customer_Id,
3439 X_BILL_TO_CUSTOMER_ID => l_bill_to_customer_id,
3440 X_SHIP_TO_CUSTOMER_ID => l_ship_to_customer_id,
3441 X_BILL_TO_ADDRESS_ID => St_Bill_To_Address_Id,
3442 X_SHIP_TO_ADDRESS_ID => St_Bill_To_Address_Id,
3443 X_BILL_TO_CONTACT_ID => X_bill_to_contact_id,
3444 X_SHIP_TO_CONTACT_ID => X_ship_to_contact_id,
3445 X_ERR_CODE => l_err_code,
3446 X_ERR_STAGE => X_err_stage,
3447 X_ERR_STACK => X_err_stack );
3448 ***/
3449 /*
3450 GET_CUSTOMER_INFO(X_Customer_Id,
3451 St_Bill_To_Address_Id,
3452 St_Ship_To_Address_Id,
3453 X_Bill_To_Contact_Id,
3454 X_Ship_To_Contact_Id,
3455 X_Err_Code,
3456 X_Err_Stage);
3457 IF X_Err_Code <> 'S' THEN
3458 X_App_Short_Name := 'GMS';
3459 RAISE FND_API.G_EXC_ERROR;
3460 ELSE
3461 RETCODE := X_Err_Code;
3462 END IF;
3463
3464 IF X_Bill_To_Address_Id_IN IS NULL THEN
3465 X_Bill_To_Address_Id_OUT := St_Bill_To_Address_Id;
3466 ELSE
3467 X_Bill_To_Address_Id_OUT := NULL;
3468 END IF;
3469
3470 IF X_Ship_To_Address_Id_IN IS NULL THEN
3471 X_Ship_To_Address_Id_OUT := St_Ship_To_Address_Id;
3472 ELSE
3473 X_Ship_To_Address_Id_OUT := NULL;
3474 END IF;
3475
3476 IF X_Bill_To_Address_Id_IN IS NOT NULL THEN
3477 X_Bill_To_Address_PASSED := X_Bill_To_Address_Id_IN;
3478 ELSE
3479 X_Bill_To_Address_PASSED := St_Bill_To_Address_Id;
3480 END IF;
3481
3482 IF X_Ship_To_Address_Id_IN IS NOT NULL THEN
3483 X_Ship_To_Address_PASSED := X_Ship_To_Address_Id_IN;
3484 ELSE
3485 X_Ship_To_Address_PASSED := St_Ship_To_Address_Id;
3486 END IF;
3487 */
3488
3489 /* Namburi
3490 UPDATE_PROJ_CUST_CONTACTS(X_Award_Project_Id,
3491 X_Customer_Id,
3492 X_Bill_To_Address_PASSED,
3493 X_Ship_To_Address_PASSED,
3494 X_Bill_To_Contact_Id,
3495 X_Ship_To_Contact_Id,
3496 X_Err_Code,
3497 X_Err_Stage);
3498 IF X_Err_Code <> 'S' THEN
3499 X_App_Short_Name := 'GMS';
3500 RAISE FND_API.G_EXC_ERROR;
3501 ELSE
3502 RETCODE := X_Err_Code;
3503 END IF;
3504 */
3505 -- Bug Fix 2994625
3506 -- Create contacts in the pa_project_contacts by calling pa_customer_info.create_customer_contacts
3507 -- instead of inserting directly INTO the tables
3508 G_Stage := '(380:select from pa_project_contacts)';
3509 BEGIN
3510 Select
3511 contact_id
3512 INTO
3513 Bill_To_Contact_exists
3514 FROM
3515 PA_PROJECT_CONTACTS
3516 WHERE project_id = X_Award_Project_Id
3517 and customer_id = X_Customer_Id
3518 and project_contact_type_code = 'BILLING';
3519 EXCEPTION
3520 WHEN NO_DATA_FOUND THEN
3521 NULL;
3522 END;
3523
3524 G_Stage := '(390:Calling pa_customers_contacts_pub.create_customer_contact)';
3525 IF bill_to_contact_exists IS NULL THEN
3526
3527 pa_customers_contacts_pub.create_customer_contact
3528 ( p_api_version => 1.0
3529 ,p_init_msg_list => FND_API.G_TRUE
3530 ,p_commit => FND_API.G_FALSE
3531 ,p_validate_only => FND_API.G_FALSE
3532 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3533 ,p_calling_module => 'FORM'
3534 ,p_debug_mode => 'N'
3535 ,p_max_msg_count => FND_API.G_MISS_NUM
3536 ,p_project_id => X_AWARD_PROJECT_ID
3537 ,p_customer_id => X_CUSTOMER_ID
3538 ,p_bill_ship_customer_id => l_BILL_TO_CUSTOMER_ID
3539 ,p_contact_id => X_bill_to_contact_id
3540 ,p_contact_name => FND_API.G_MISS_CHAR
3541 ,p_project_contact_type_code => 'BILLING'
3542 ,p_project_contact_type_name => FND_API.G_MISS_CHAR
3543 ,x_return_status => X_return_status
3544 ,x_msg_count => x_msg_count
3545 ,x_msg_data => x_msg_data);
3546 END IF;
3547
3548 BEGIN
3549 G_Stage := '(400:Select from pa_project_contacts)';
3550 Select contact_id
3551 INTO Ship_To_Contact_exists
3552 FROM PA_PROJECT_CONTACTS
3553 WHERE project_id = X_Award_Project_Id
3554 and customer_id = X_Customer_Id
3555 and project_contact_type_code = 'SHIPPING';
3556 EXCEPTION
3557 WHEN NO_DATA_FOUND THEN
3558 NULL;
3559 END;
3560
3561 G_Stage := '(410:calling pa_customers_contacts_pub.create_customer_contact)';
3562 IF ship_to_contact_exists is NULL THEN
3563
3564 pa_customers_contacts_pub.create_customer_contact
3565 (p_api_version => 1.0
3566 ,p_init_msg_list => FND_API.G_TRUE
3567 ,p_commit => FND_API.G_FALSE
3568 ,p_validate_only => FND_API.G_FALSE
3569 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3570 ,p_calling_module => 'FORM'
3571 ,p_debug_mode => 'N'
3572 ,p_max_msg_count => FND_API.G_MISS_NUM
3573 ,p_project_id => X_AWARD_PROJECT_ID
3574 ,p_customer_id => X_CUSTOMER_ID
3575 ,p_bill_ship_customer_id => l_SHIP_TO_CUSTOMER_ID
3576 ,p_contact_id => X_ship_to_contact_id
3577 ,p_contact_name => FND_API.G_MISS_CHAR
3578 ,p_project_contact_type_code => 'SHIPPING'
3579 ,p_project_contact_type_name => FND_API.G_MISS_CHAR
3580 ,x_return_status => X_return_status
3581 ,x_msg_count => x_msg_count
3582 ,x_msg_data => x_msg_data);
3583
3584 END IF;
3585
3586 G_Stage := '(420:Calling modify_gms_contacts)';
3587 modify_gms_contacts(X_Award_Id,
3588 X_Award_Project_Id,
3589 X_Customer_Id,
3590 X_Bill_to_customer_id,
3591 NULL,
3592 NULL,
3593 X_Err_Code,
3594 X_Err_Stage);
3595
3596 IF X_Err_Code <> 'S' THEN
3597 X_App_Short_Name := 'GMS';
3598 RAISE FND_API.G_EXC_ERROR;
3599 ELSE
3600 RETCODE := X_Err_Code;
3601 END IF;
3602
3603 -- Award budget type not used in 11i. Hence the following call is commented
3604 /*
3605 UPDATE_AWARD_BUDGET_TYPE(X_Budget_Type_Code => to_char(X_Award_Id),
3606 X_Budget_Type => X_Project_Name,
3607 X_Start_Date => X_Start_Date, -- Start Date and
3608 X_End_Date => X_End_Date, -- END Date are no longer being used in
3609 X_Err_Code => X_Err_Code, -- UPDATE of Budget Type
3610 X_Err_Stage => X_Err_Stage) ;
3611 IF X_Err_Code <> 'S' THEN
3612 X_App_Short_Name := 'GMS';
3613 RAISE FND_API.G_EXC_ERROR;
3614 ELSE
3615 RETCODE := X_Err_Code;
3616 END IF;
3617 */
3618
3619 --dbms_output.put_line('Successfully UPDATEd AWARD BUDGET TYPE ');
3620
3621 /*-------------------------Check for Revenue Limit Flag UPDATE Allowability---------------*/
3622 /* Bug 1841288 : Commenting out NOCOPY below code as all the hard_limit_flag validation is already been done
3623 in check_funding_limit procedure of GMSAWEAW.fmb.
3624
3625
3626 BEGIN
3627 select
3628 revenue_limit_flag
3629 INTO
3630 St_Revenue_Limit_Flag
3631 FROM
3632 PA_AGREEMENTS
3633 WHERE
3634 agreement_id = X_Agreement_Id;
3635 END ;
3636 --dbms_output.put_line('Got after SELECT of Revenue Limit Flag ');
3637
3638 IF ALLOW_REV_LIMIT_FLAG_UPDATE(X_Agreement_Id,
3639 X_Err_Code,
3640 X_Err_Stage) THEN
3641
3642 IF ((X_Revenue_Limit_Flag IS NOT NULL) AND (St_Revenue_Limit_Flag <> X_Revenue_Limit_Flag)) THEN
3643 RETCODE := X_Err_Code;
3644 END IF;
3645
3646 ELSE
3647
3648 IF (( X_Revenue_Limit_Flag IS NOT NULL) AND (St_Revenue_Limit_Flag <> X_Revenue_Limit_Flag)) THEN
3649 RAISE FND_API.G_EXC_ERROR;
3650 ELSE
3651 RETCODE := X_Err_Code;
3652 END IF;
3653
3654 END IF;
3655 */
3656 -- END of modifications for bug 1841288
3657 /*-----------------------------------------------------------------------------------------*/
3658 G_Stage := '(430:CALLING GMS_MULTI_FUNDING.UPDATE_AGREEMENT)';
3659 GMS_MULTI_FUNDING.UPDATE_AGREEMENT(X_Agreement_Id => X_Agreement_Id,
3660 X_Agreement_Num => X_Awd_Proj_Number,
3661 X_Agreement_Type => X_Agreement_Type,
3662 X_Revenue_Limit_Flag => X_Revenue_Limit_Flag,
3663 X_Invoice_Limit_Flag => X_Invoice_Limit_Flag, /*Bug 6642901*/
3667 X_Amount => 0,
3664 X_Customer_Id => X_Customer_Id,
3665 X_Owned_By_Person_Id => X_Person_Id_New,
3666 X_Term_Id => X_Term_Id,
3668 X_Close_Date => X_Close_Date,
3669 RETCODE => X_Err_Code,
3670 ERRBUF => X_Err_Stage);
3671
3672 --dbms_output.put_line('After UPDATE Agreement - Code '||X_Err_Code);
3673
3674 IF X_Err_Code <> 'S' THEN
3675 X_App_Short_Name := 'GMS';
3676 RAISE FND_API.G_EXC_ERROR;
3677 ELSE
3678 RETCODE := X_Err_Code;
3679 END IF;
3680
3681 EXCEPTION
3682 WHEN FND_API.G_EXC_ERROR THEN
3683 X_Msg_Count := p_msg_count;
3684 RETCODE := X_Err_Code;
3685 ERRBUF := X_Err_Stage||' at '||g_stage;
3686 -- Added when others exception for Bug:2662848
3687 WHEN OTHERS THEN
3688 RETCODE := 'U';
3689 FND_MSG_PUB.add_exc_msg
3690 ( p_pkg_name => G_PKG_NAME
3691 ,p_procedure_name => l_api_name
3692 ,p_error_text => substrb(SQLERRM||' at stage='||g_stage||' ',1,240)
3693 );
3694 FND_MSG_PUB.Count_And_Get
3695 ( p_count => p_msg_count,
3696 p_data => X_Err_Stage);
3697 ERRBUF := 'At stage='||g_stage||' '||X_Err_Stage;
3698
3699 --Calling program should make sure by checkng RETCODE after the call
3700 END update_award_project;
3701
3702 PROCEDURE DELETE_AWARD_PROJECT(X_Award_Id IN NUMBER,
3703 X_Award_Project_Id IN NUMBER,
3704 X_Agreement_Id IN NUMBER,
3705 X_App_Short_Name OUT NOCOPY VARCHAR2,
3706 X_Msg_Count OUT NOCOPY NUMBER,
3707 RETCODE OUT NOCOPY VARCHAR2,
3708 ERRBUF OUT NOCOPY VARCHAR2) IS
3709 X_Awd_Proj_Number VARCHAR2(30);
3710
3711 X_Product_Code VARCHAR2(30);
3712
3713 X_Err_Code VARCHAR2(1);
3714 X_Err_Stage VARCHAR2(2000);
3715 X_Text VARCHAR2(200);
3716 --X_Msg_Count NUMBER;
3717
3718 --This variable will help debugging when exception occurs
3719
3720 BEGIN
3721 G_Stage := '(600:SELECT PA_PROJECTS)';
3722 BEGIN
3723 SELECT
3724 segment1
3725 INTO
3726 X_Awd_Proj_Number
3727 FROM
3728 PA_PROJECTS
3729 WHERE
3730 PROJECT_ID = X_Award_Project_Id;
3731 END;
3732 X_Product_Code := 'GMS';
3733
3734 G_Stage := '(610:CALLING PA_PROJECT_PUB.DELETE_PROJECT)';
3735 PA_PROJECT_PUB.DELETE_PROJECT( P_API_VERSION_NUMBER => 1.0,
3736 p_init_msg_list => 'T',
3737 P_MSG_COUNT => p_msg_count,
3738 P_MSG_DATA => X_Err_Stage,
3739 P_RETURN_STATUS => X_Err_Code,
3740 P_PM_PRODUCT_CODE => X_Product_Code,
3741 P_PM_PROJECT_REFERENCE => X_Awd_Proj_Number,
3742 P_PA_PROJECT_ID => X_Award_Project_Id );
3743
3744 IF X_Err_Code <> 'S' THEN
3745 /* FND_MESSAGE.PARSE_ENCODED (encoded_message => X_Err_Stage,
3746 APP_SHORT_NAME => X_App_Short_Name,
3747 MESSAGE_NAME => X_Text );
3748
3749 X_Err_Stage := X_Text;
3750 */
3751 RAISE FND_API.G_EXC_ERROR;
3752 ELSE
3753 RETCODE := X_Err_Code;
3754 END IF;
3755
3756
3757 /* Delete AWARD_BUDGET_TYPE */
3758 -- this procedure is not used in 11i as there is no award_budget_type -- Suresh
3759 /*
3760 DELETE_AWARD_BUDGET_TYPE(to_char(X_Award_Id),
3761 X_Err_Code ,
3762 X_Err_Stage);
3763
3764 IF X_Err_Code <> 'S' THEN
3765 X_App_Short_Name := 'GMS';
3766 RAISE FND_API.G_EXC_ERROR;
3767 ELSE
3768 RETCODE := X_Err_Code;
3769 END IF;
3770 */
3771
3772
3773 G_Stage := '(620:CALLING GMS_MULTI_FUNDING.DELETE_AGREEMENT)';
3774 /* Deleting Agreement associated with Award Project */
3775 GMS_MULTI_FUNDING.DELETE_AGREEMENT
3776 (X_Agreement_Id,
3777 X_Err_Code,
3778 X_Err_Stage);
3779
3780 IF X_Err_Code <> 'S' THEN
3781 X_App_Short_Name := 'GMS';
3782 RAISE FND_API.G_EXC_ERROR;
3783 ELSE
3784 RETCODE := X_Err_Code;
3785 END IF;
3786
3787 EXCEPTION
3788 WHEN FND_API.G_EXC_ERROR THEN
3789 X_Msg_Count := p_msg_count;
3790 RETCODE := X_Err_Code;
3791 ERRBUF := 'At stage='||g_stage||' '||X_Err_Stage;
3792 WHEN OTHERS THEN
3793 RETCODE := 'U';
3794 FND_MSG_PUB.add_exc_msg
3795 ( p_pkg_name => 'GMS_MULTI_FUNDING'
3796 ,p_procedure_name => 'DELETE_AWARD_PROJECT'
3797 ,p_error_text => substrb(SQLERRM||' (stage='||g_stage||')',1,240)
3798 );
3799 FND_MSG_PUB.Count_And_Get
3800 ( p_count => p_msg_count,
3801 p_data => X_Err_Stage);
3802 ERRBUF := 'At stage='||g_stage||' '||X_Err_Stage;
3803 END DELETE_AWARD_PROJECT;
3804
3805 PROCEDURE CREATE_AWARD_PROJECT_BUDGET( X_Award_Project_Id IN NUMBER,
3806 X_Budget_Amount IN NUMBER,
3807 X_Start_Date IN DATE,
3808 X_End_Date IN DATE,
3809 X_App_Short_Name OUT NOCOPY VARCHAR2,
3810 RETCODE OUT NOCOPY VARCHAR2,
3811 ERRBUF OUT NOCOPY VARCHAR2) IS
3812 X_Err_Code VARCHAR2(1) := NULL;
3813 X_Err_Stage VARCHAR2(200) := NULL;
3814
3815 X_Award_Id NUMBER(15);
3816 X_Budget_Version_Id NUMBER(15);
3817 X_Resource_Assignment_Id NUMBER(15);
3818 X_Resource_List_Id NUMBER(15);
3819 X_Resource_List_Member_Id NUMBER(15);
3820
3821 X_Msg_data VARCHAR2(2000);
3822 X_Msg_Count NUMBER;
3823 X_Text VARCHAR2(30);
3824 X_Index NUMBER(15);
3825
3826 X_Budget_Lines_IN_REC PA_BUDGET_PUB.BUDGET_LINE_IN_REC_TYPE;
3827 X_Budget_Lines_IN_TBL PA_BUDGET_PUB.BUDGET_LINE_IN_TBL_TYPE;
3828 X_Budget_Lines_OUT_TBL PA_BUDGET_PUB.BUDGET_LINE_OUT_TBL_TYPE;
3829 X_Product_Code VARCHAR2(30);
3830
3831 X_Workflow_Started VARCHAR2(1);
3832
3833 --Added these variables to track when an API fails
3834 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_AWARD_PROJECT_BUDGET';
3835
3836 BEGIN
3837 G_Stage := '(650:SELECT FROM GMS_AWARDS)';
3838 --Get Award Id (PM_PROJECT_REFERENCE) of the Award Project //
3839 BEGIN
3840 SELECT Award_Id
3841 INTO X_Award_Id
3842 FROM gms_awards
3843 WHERE award_project_id = X_Award_Project_Id;
3844
3845 G_Stage := '(660:SELECT FROM RESOURCE_LIST)';
3846 --Get Resource List Id, Resource_List_Member_Id for Resource = 'Uncategorized' //
3847 SELECT A.RESOURCE_LIST_MEMBER_ID, B.RESOURCE_LIST_ID
3848 INTO X_Resource_List_Member_Id, X_Resource_List_Id
3849 FROM PA_RESOURCE_LIST_MEMBERS A,
3850 PA_RESOURCE_LISTS B,
3851 PA_IMPLEMENTATIONS PI -- Bug 2108191
3852 WHERE B.uncategorized_flag = 'Y'
3853 AND B.resource_list_id = a.resource_list_id
3854 AND B.business_group_id = PI.business_group_id
3855 AND NVL(A.migration_code,'M') ='M'
3856 AND NVL(B.migration_code,'M') ='M' ; -- Bug 2108191
3857 -- rownum = 1;
3858
3859 X_Err_Code := 'S';
3860 EXCEPTION
3861 WHEN NO_DATA_FOUND THEN
3862 X_Err_Code := 'E';
3863 X_Err_Stage := 'Award, Resource_List_Member_Id or Resource_List_Id Not Found ';
3864 FND_MESSAGE.SET_NAME('GMS','GMS_AWD_RESL_RESLM_NOT_FOUND');
3865 FND_MSG_PUB.add;
3866 FND_MSG_PUB.Count_And_Get(p_count => p_msg_count,
3867 p_data => X_Err_Stage);
3868 RAISE FND_API.G_EXC_ERROR;
3869 END;
3870
3871 --dbms_output.put_line('After Resource List and List Member Select');
3872 --dbms_output.put_line('The Resource List ID is : '||to_char(X_Resource_List_Id));
3873 --dbms_output.put_line('The Resource List Member ID is : '||to_char(X_Resource_List_Member_Id));
3874
3875 BEGIN
3876 G_Stage := '(670:CALLING DRAFT_BUDGET_EXISTS)';
3877 IF DRAFT_BUDGET_EXISTS(X_Award_Project_Id,
3878 X_Err_Code,
3879 X_Err_Stage) THEN
3880
3881 G_Stage := '(680:CALLING PA_BUDGET_PUB.DELETE_DRAFT_BUDGET)';
3882 --dbms_output.put_line('Before Delete Draft Budget API');
3883 PA_BUDGET_PUB.DELETE_DRAFT_BUDGET
3884 (p_api_version_number => 1.0,
3885 p_init_msg_list => 'T',
3886 p_pa_project_id => X_Award_Project_Id,
3887 p_pm_project_reference => to_char(X_Award_Id),
3888 p_pm_product_code => 'GMS',
3889 p_budget_type_code => 'AR',
3890 p_msg_count => p_msg_count,
3891 p_msg_data => X_Err_Stage,
3892 p_return_status => X_Err_Code);
3893
3894 G_Stage := '(690:AFTER PA_BUDGET_PUB.DELETE_DRAFT_BUDGET CALL)';
3895 IF X_Err_Code <> 'S' THEN
3896 /* FND_MESSAGE.PARSE_ENCODED (encoded_message => X_Err_Stage,
3897 APP_SHORT_NAME => X_App_Short_Name,
3898 MESSAGE_NAME => X_Text );
3899 X_Err_Stage := X_Text;
3900 */
3901 RAISE FND_API.G_EXC_ERROR;
3902 ELSE
3903 RETCODE := X_Err_Code;
3904 END IF;
3905
3906 --dbms_output.put_line('Return Status After Delete is '||X_Err_Code);
3907 ELSE
3908 IF X_Err_Code <> 'S' THEN
3909 RAISE FND_API.G_EXC_ERROR;
3910 ELSE
3911 RETCODE := X_Err_Code;
3912 END IF;
3913
3914 END IF;
3915
3916 ----------------------------------------------------------------------------------------------
3917 /* Call Budget API to create Draft Budget */
3918 X_Product_Code := 'GMS';
3919 X_Budget_Lines_IN_REC.resource_list_member_id := X_Resource_List_Member_Id;
3920 X_Budget_Lines_IN_REC.budget_start_date := X_Start_Date;
3921 X_Budget_Lines_IN_REC.budget_end_date := X_End_Date;
3922 X_Budget_Lines_IN_REC.revenue := X_Budget_Amount;
3923 X_Budget_Lines_IN_REC.quantity := NULL;
3924
3925 -- Bug 3174375 : Cannot create award ...
3926 X_Budget_Lines_IN_REC.pm_product_code := 'GMS';
3927
3928 X_Budget_Lines_IN_TBL(1) := X_Budget_Lines_IN_REC;
3929 --dbms_output.put_line('Before Create Budget API inside Create_Award_Project_Budget');
3930 --dbms_output.put_line('Awd Proj Id '||to_char(X_Award_Project_Id)||' Product Code '||X_Product_Code
3931 --||'Award Id '||to_char(X_Award_Id)
3932 --||'Revenue Bud Amount '||to_char(X_Budget_Lines_IN_REC.revenue)
3933 --||'Start_Date'||to_char(X_Budget_Lines_IN_REC.budget_start_date)
3934 --||'Resource List ID '||X_Resource_List_Id
3935 --||'End_Date '||to_char(X_Budget_Lines_IN_REC.budget_end_date));
3936
3937 G_Stage := '(700:CALLING PA_BUDGET_PUB.CREATE_DRAFT_BUDGET)';
3938 --dbms_output.put_line('Getting INTO THE PA API');
3939 PA_BUDGET_PUB.CREATE_DRAFT_BUDGET
3940 (p_api_version_number => 1.0,
3941 p_init_msg_list => 'T',
3942 p_msg_count => p_msg_count,
3943 p_msg_data => X_Err_Stage,
3944 p_return_status => X_Err_Code,
3945 p_pm_product_code => X_Product_Code,
3946 p_pa_project_id => X_Award_Project_Id,
3947 p_pm_project_reference => to_char(X_Award_Id),
3948 p_budget_type_code => 'AR',
3949 p_entry_method_code => 'AWARD_PROJECT_REVENUE',
3950 p_resource_list_id => X_Resource_List_Id,
3951 /* p_change_reason_code => 'Scope Change', */
3952 p_budget_lines_in => X_Budget_Lines_IN_TBL,
3953 p_budget_lines_out => X_Budget_Lines_OUT_TBL
3954 );
3955 IF X_Err_Code <> 'S' THEN
3956 /* FND_MESSAGE.PARSE_ENCODED (encoded_message => X_Err_Stage,
3957 APP_SHORT_NAME => X_App_Short_Name,
3958 MESSAGE_NAME => X_Text );
3959
3960 X_Err_Stage := X_Text;
3961 */
3962
3963 RAISE FND_API.G_EXC_ERROR;
3964 ELSE
3965 RETCODE := X_Err_Code;
3966 END IF;
3967
3968 --dbms_output.put_line('After BUDGET API : '||X_Msg_data);
3969 --dbms_output.put_line('After BUDGET API : '||X_Err_Stage);
3970
3971 G_Stage := '(710:CALLING PA_BUDGET_PUB.BASELINE_BUDGET)';
3972 /* Call API to Baseline Budget */
3973 PA_BUDGET_PUB.BASELINE_BUDGET
3974 (p_api_version_number => 1.0,
3975 p_init_msg_list => 'T',
3976 p_msg_count => p_msg_count,
3977 p_msg_data => X_Err_Stage,
3978 p_return_status => X_Err_Code,
3979 p_pm_product_code => X_Product_Code,
3980 p_pa_project_id => X_Award_Project_Id,
3981 p_pm_project_reference => to_char(X_Award_Id),
3982 p_budget_type_code => 'AR',
3983 p_workflow_started => X_Workflow_Started
3984 );
3985
3986 IF X_Err_Code <> 'S' THEN
3987 /* FND_MESSAGE.PARSE_ENCODED (encoded_message => X_Err_Stage,
3988 APP_SHORT_NAME => X_App_Short_Name,
3989 MESSAGE_NAME => X_Text );
3990
3991 X_Err_Stage := X_Text;
3992 */
3993 RAISE FND_API.G_EXC_ERROR;
3994 ELSE
3995 RETCODE := X_Err_Code;
3996 END IF;
3997
3998 --dbms_output.put_line('After Baseline');
3999 --dbms_output.put_line('After Baseline GET : '||X_Err_Stage);
4000
4001 --------------------------------------------------------------------------------------------------
4002
4003 --dbms_output.put_line('At the Very END of CREATE AWARD BUDGET');
4004 END;
4005 EXCEPTION
4006 WHEN FND_API.G_EXC_ERROR THEN
4007 RETCODE := X_Err_Code;
4008 ERRBUF := X_Err_Stage;
4009
4010 -- Added when OTHERS exception for Bug:2662848
4011 WHEN OTHERS THEN
4012 RETCODE := 'U';
4013 FND_MSG_PUB.add_exc_msg
4014 ( p_pkg_name => G_PKG_NAME
4015 ,p_procedure_name => l_api_name
4016 ,p_error_text => substrb(SQLERRM||' at stage='||g_stage||' ',1,240)
4017 );
4018 FND_MSG_PUB.Count_And_Get
4019 ( p_count => p_msg_count,
4020 p_data => X_Err_Stage);
4021 ERRBUF := SQLERRM||' at stage='||g_stage||' '||X_Err_Stage;
4022 RAISE; --not required but keeping for now
4023 END CREATE_AWARD_PROJECT_BUDGET;
4024
4025 PROCEDURE CREATE_AWARD_FUNDING( X_Installment_Id IN NUMBER,
4026 X_Allocated_Amount IN NUMBER,
4027 X_Date_Allocated IN DATE,
4028 X_GMS_Project_Funding_Id IN NUMBER,
4029 X_Project_Funding_Id OUT NOCOPY NUMBER,
4030 X_App_Short_Name OUT NOCOPY VARCHAR2,
4031 X_Msg_Count OUT NOCOPY NUMBER,
4032 RETCODE OUT NOCOPY VARCHAR2,
4033 ERRBUF OUT NOCOPY VARCHAR2) IS
4034 X_Err_Code VARCHAR2(1);
4035 X_Err_Stage VARCHAR2(200);
4036 X_Text VARCHAR2(200);
4037
4038 St_Project_Funding_Id NUMBER(15);
4039
4040 X_Award_Id NUMBER(15);
4041 X_Award_Project_Id NUMBER(15);
4042 X_Agreement_Id NUMBER(15);
4043 X_Project_Start_Date DATE;
4044 X_Project_End_Date DATE;
4045 X_Total_Unbaselined_Amount NUMBER(22,5) := 0;
4046 X_Total_Baselined_Amount NUMBER(22,5) := 0;
4047 X_Total_Funding_Budget NUMBER(22,5) := 0;
4048
4049 --Added these variables to track when an API fails
4050 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_AWARD_FUNDING';
4051
4052 BEGIN
4053 G_Stage := '(1000:CALLING FND_MSG_PUB.INITIALIZE)';
4054 FND_MSG_PUB.Initialize;
4055 --Get next sequence for PA_PROJECT_FUNDING//
4056 BEGIN
4057 Select
4058 PA_PROJECT_FUNDINGS_S.NEXTVAL
4059 INTO
4060 St_Project_Funding_Id
4061 FROM
4062 DUAL;
4063 END ;
4064
4065 BEGIN
4066 --Get Award Project Id for the Award of which this Agreement is part of
4067 --dbms_output.put_line('Before Get Project Start END Date');
4068
4069 G_Stage := '(1010:CALLING GET_PROJ_START_AND_END_DATE)';
4070 GET_PROJ_START_AND_END_DATE(X_Installment_Id,
4071 X_Award_Id,
4072 X_Award_Project_Id,
4073 X_Project_Start_Date,
4074 X_Project_End_Date,
4075 X_Agreement_Id,
4076 X_Err_Code,
4077 X_Err_Stage);
4078
4079 IF X_Err_Code <> 'S' THEN
4080 RAISE FND_API.G_EXC_ERROR;
4081 ELSE
4085
4082 RETCODE := X_Err_Code ;
4083 END IF;
4084 END;
4086 BEGIN
4087 /* UPDATE Award Project's Agreement with the new Amount */
4088 --dbms_output.put_line('Amount before UPDATE agreement '||X_Allocated_Amount);
4089
4090 G_Stage := '(1020:CALLING GMS_MULTI_FUNDING.UPDATE_AGREEMENT)';
4091 GMS_MULTI_FUNDING.UPDATE_AGREEMENT(X_Agreement_Id => X_Agreement_Id,
4092 X_Agreement_Num => NULL,
4093 X_Agreement_Type => NULL,
4094 X_Revenue_Limit_Flag => NULL,
4095 X_Invoice_Limit_Flag => NULL, /*Bug 6642901*/
4096 X_Customer_Id => NULL,
4097 X_Owned_By_Person_Id => NULL,
4098 X_Term_Id => NULL,
4099 X_Amount => X_Allocated_Amount,
4100 X_Close_Date => NULL,
4101 RETCODE => X_Err_Code,
4102 ERRBUF => X_Err_Stage);
4103
4104 IF X_Err_Code <> 'S' THEN
4105 RAISE FND_API.G_EXC_ERROR;
4106 ELSE
4107 RETCODE := X_Err_Code ;
4108 END IF;
4109 END;
4110
4111 G_Stage := '(1030:CALLING INSERT_DETAIL_PROJECT_FUNDING)';
4112 --Insert row INTO PA_PROJECT_FUNDING for Award Project //
4113 --dbms_output.put_line('Before Insert INTO PA Project Fundings');
4114 BEGIN
4115 INSERT_DETAIL_PROJECT_FUNDING(St_Project_Funding_Id,
4116 X_Agreement_Id,
4117 X_Award_Project_Id,
4118 X_Allocated_Amount,
4119 X_Date_Allocated,
4120 X_Err_Code,
4121 X_Err_Stage);
4122
4123 IF X_Err_Code <> 'S' THEN
4124 RAISE FND_API.G_EXC_ERROR;
4125 ELSE
4126 RETCODE := X_Err_Code ;
4127 END IF;
4128 END;
4129
4130 --dbms_output.put_line('Before UPDATE GMS PROJECT FUNDINGS');
4131 BEGIN
4132 --UPDATE GMS_PROJECT_FUNDING with X_Project_Funding_Id and PASS IT OUT NOCOPY
4133 BEGIN
4134 UPDATE GMS_PROJECT_FUNDINGS
4135 SET PROJECT_FUNDING_ID = St_Project_Funding_Id
4136 WHERE GMS_PROJECT_FUNDING_ID = X_GMS_Project_Funding_Id;
4137 END;
4138 X_Project_Funding_Id := St_Project_Funding_Id;
4139
4140 --dbms_output.put_line('Before Row Exists');
4141 END;
4142
4143 G_Stage := '(1040:CALLING ROW_EXISTS_IN_PA_SUMM_FUNDING)';
4144 BEGIN
4145 IF ROW_EXISTS_IN_PA_SUMM_FUNDING
4146 (X_Agreement_Id,
4147 X_Award_Project_Id,
4148 X_Err_Code,
4149 X_Err_Stage) THEN
4150 BEGIN
4151 --dbms_output.put_line('Before Get Total Funding Amt');
4152 G_Stage := '(1040:CALLING GET_TOTAL_FUNDING_AMOUNT)';
4153 GET_TOTAL_FUNDING_AMOUNT
4154 (X_Agreement_Id,
4155 X_Award_Project_Id,
4156 X_Total_Unbaselined_Amount,
4157 X_Total_Baselined_Amount,
4158 X_Err_Code,
4159 X_Err_Stage) ;
4160 IF X_Err_Code <> 'S' THEN
4161 RAISE FND_API.G_EXC_ERROR;
4162 ELSE
4163 RETCODE := X_Err_Code ;
4164 END IF;
4165 X_Total_Unbaselined_Amount := X_Total_Unbaselined_Amount + X_Allocated_Amount;
4166
4167
4168 G_Stage := '(1050:CALLING UPDATE_PA_SUMM_PROJECT_FUNDING)';
4169 --dbms_output.put_line('The Total Summary Funding Amount is : '||to_char(X_Total_Unbaselined_Amount));
4170 --dbms_output.put_line('Before UPDATE Summary Funding');
4171 UPDATE_PA_SUMM_PROJECT_FUNDING
4172 (X_Agreement_Id,
4173 X_Award_Project_Id,
4174 X_Total_Unbaselined_Amount,
4175 X_Err_Code,
4176 X_Err_Stage);
4177 IF X_Err_Code <> 'S' THEN
4178 RAISE FND_API.G_EXC_ERROR;
4179 ELSE
4180 RETCODE := X_Err_Code ;
4181 END IF;
4182 END;
4183 ELSE
4184
4185 G_Stage := '(1060:CALLING INSERT_SUMMARY_PROJECT_FUNDING)';
4186 IF X_Err_Code = 'S' THEN
4187
4188 X_Total_Unbaselined_Amount := X_Allocated_Amount;
4189 --dbms_output.put_line('The Total Summary Funding Amount is : '||to_char(X_Total_Unbaselined_Amount));
4190 --dbms_output.put_line('Before Insert Summary Funding');
4191 INSERT_SUMMARY_PROJECT_FUNDING
4192 (X_Agreement_Id,
4193 X_Award_Project_Id,
4194 X_Total_Unbaselined_Amount,
4195 X_Err_Code,
4196 X_Err_Stage);
4197
4198 IF X_Err_Code <> 'S' THEN
4199 RAISE FND_API.G_EXC_ERROR;
4200 ELSE
4201 RETCODE := X_Err_Code ;
4202 END IF;
4203 ELSE
4204 RAISE FND_API.G_EXC_ERROR;
4205 END IF;
4206
4207 END IF;
4208 END;
4209 G_Stage := '(1070:CALLING GET_TOTAL_FUNDING_BUDGET)';
4210 BEGIN
4211 /* Getting the total summary funding for the Award Project (Baselined+Unbaselined) */
4212 GET_TOTAL_FUNDING_BUDGET(X_Award_Project_Id,
4213 X_Total_Funding_Budget,
4214 X_Err_Code,
4215 X_Err_Stage);
4216 IF X_Err_Code <> 'S' THEN
4217 RAISE FND_API.G_EXC_ERROR;
4218 ELSE
4219 RETCODE := X_Err_Code ;
4220 END IF;
4221
4222 /* Deletes any existing Draft Revenue Budget and Creates a new Draft Revenue Budget */
4223
4224 G_Stage := '(1080:CALLING CREATE_AWARD_PROJECT_BUDGET)';
4225 --dbms_output.put_line('The Total Revenue Budget Amount is : '||to_char(X_Total_Funding_Budget));
4226 --dbms_output.put_line('Before Creation of Award Project Budget ');
4227 CREATE_AWARD_PROJECT_BUDGET(X_Award_Project_Id,
4228 X_Total_Funding_Budget,
4229 X_Project_Start_Date,
4230 X_Project_End_Date,
4231 X_App_Short_Name,
4232 X_Err_Code,
4233 X_Err_Stage);
4234
4235 IF X_Err_Code <> 'S' THEN
4236 RAISE FND_API.G_EXC_ERROR;
4237 ELSE
4238 RETCODE := X_Err_Code ;
4239 END IF;
4240
4241
4242 END;
4243
4244 EXCEPTION
4245 WHEN FND_API.G_EXC_ERROR THEN
4246 X_Msg_Count := p_msg_count;
4247 RETCODE := X_Err_Code;
4248 ERRBUF := X_Err_Stage;
4249 WHEN OTHERS THEN
4250 RETCODE := 'U';
4251 FND_MSG_PUB.add_exc_msg
4252 ( p_pkg_name => G_PKG_NAME
4253 ,p_procedure_name => l_api_name
4254 ,p_error_text => substrb(SQLERRM||' at stage='||g_stage||' ',1,240)
4255 );
4256 FND_MSG_PUB.Count_And_Get
4257 ( p_count => p_msg_count,
4258 p_data => X_Err_Stage);
4259 ERRBUF := SQLERRM||' at stage='||g_stage||' '||X_Err_Stage;
4260
4261 END CREATE_AWARD_FUNDING;
4262
4263 PROCEDURE UPDATE_AWARD_FUNDING(X_Project_Funding_Id IN NUMBER,
4264 X_Installment_Id IN NUMBER,
4265 X_Old_Allocated_Amount IN NUMBER,
4266 X_New_Allocated_Amount IN NUMBER,
4267 X_Old_Date_Allocated IN DATE,
4268 X_New_Date_Allocated IN DATE,
4269 X_App_Short_Name OUT NOCOPY VARCHAR2,
4270 X_Msg_Count OUT NOCOPY NUMBER,
4271 RETCODE OUT NOCOPY VARCHAR2,
4272 ERRBUF OUT NOCOPY VARCHAR2) IS
4273 X_Err_Code VARCHAR2(1);
4274 X_Err_Stage VARCHAR2(300);
4275 X_Text VARCHAR2(30);
4276
4277
4278 X_Award_Id NUMBER(15);
4279 X_Award_Project_Id NUMBER(15);
4280 X_Agreement_Id NUMBER(15);
4281 X_Project_Start_Date DATE;
4282 X_Project_End_Date DATE;
4283 X_Total_Unbaselined_Amount NUMBER(22,5) := 0;
4284 X_Total_Baselined_Amount NUMBER(22,5) := 0;
4285 X_Total_Funding_Budget NUMBER(22,5) := 0;
4286 X_Agreement_Amt NUMBER(22,5) := 0;
4287
4288 --Added these variables to track when an API fails
4289 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_AWARD_FUNDING';
4290
4291 BEGIN
4292 G_Stage := '(1100:CALLING GET_PROJ_START_AND_END_DATE)';
4293 BEGIN
4294 --Get Award Project Id and Award Start Date for the Award of which this Agreement is part of .//
4295 --The Award Project Id is used to UPDATE the correct row in PA_SUMMARY_FUNDINGS table //
4296 GET_PROJ_START_AND_END_DATE(X_Installment_Id,
4297 X_Award_Id,
4298 X_Award_Project_Id,
4299 X_Project_Start_Date,
4300 X_Project_End_Date,
4301 X_Agreement_Id,
4302 X_Err_Code,
4303 X_Err_Stage);
4304 IF X_Err_Code <> 'S' THEN
4305 RAISE FND_API.G_EXC_ERROR;
4306 END IF;
4307 END;
4308
4309 G_Stage := '(1110:CALLING GMS_MULTI_FUNDING.UPDATE_AGREEMENT)';
4310 BEGIN
4311 /* UPDATE Award Project's Agreement with the new Amount */
4312 X_Agreement_Amt := (X_New_Allocated_Amount - X_Old_Allocated_Amount);
4313 GMS_MULTI_FUNDING.UPDATE_AGREEMENT(X_Agreement_Id => X_Agreement_Id,
4314 X_Agreement_Num => NULL,
4315 X_Agreement_Type => NULL,
4316 X_Revenue_Limit_Flag => NULL,
4317 X_Invoice_Limit_Flag => NULL, /*Bug 6642901*/
4318 X_Customer_Id => NULL,
4319 X_Owned_By_Person_Id => NULL,
4320 X_Term_Id => NULL,
4321 X_Amount => X_Agreement_Amt,
4322 X_Close_Date => NULL,
4323 RETCODE => X_Err_Code,
4324 ERRBUF => X_Err_Stage);
4325
4326 IF X_Err_Code <> 'S' THEN
4327 RAISE FND_API.G_EXC_ERROR;
4328 END IF;
4329 END;
4330
4331 G_Stage := '(1120:CALLING UPDATE_DETAIL_PROJECT_FUNDING)';
4332 BEGIN
4333
4334 --UPDATEs row INTO PA_PROJECT_FUNDING with the new Allocated Amount if the status is still DRAFT //
4335 UPDATE_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id,
4336 X_Old_Allocated_Amount,
4337 X_New_Allocated_Amount,
4338 X_Old_Date_Allocated,
4339 X_New_Date_Allocated,
4340 X_Err_Code,
4341 X_Err_Stage);
4342 IF X_Err_Code <> 'S' THEN
4343 RAISE FND_API.G_EXC_ERROR;
4344 END IF;
4345
4346 G_Stage := '(1130:CALLING GET_TOTAL_FUNDING_AMOUNT)';
4347 GET_TOTAL_FUNDING_AMOUNT(X_Agreement_Id,
4348 X_Award_Project_Id,
4349 X_Total_Unbaselined_Amount,
4350 X_Total_Baselined_Amount,
4351 X_Err_Code,
4352 X_Err_Stage) ;
4353 IF X_Err_Code <> 'S' THEN
4354 RAISE FND_API.G_EXC_ERROR;
4355 END IF;
4356
4357 X_Total_Unbaselined_Amount := X_Total_Unbaselined_Amount + (X_New_Allocated_Amount -
4358 X_Old_Allocated_Amount);
4359
4360 G_Stage := '(1140:CALLING UPDATE_PA_SUMM_PROJECT_FUNDING)';
4361 --dbms_output.put_line('TOtal UNbaselined Amt Before UPDATE_PA_SUMM_FUNDING '||X_Total_Unbaselined_Amount);
4362 UPDATE_PA_SUMM_PROJECT_FUNDING(X_Agreement_Id,
4363 X_Award_Project_Id,
4364 X_Total_Unbaselined_Amount,
4365 X_Err_Code,
4366 X_Err_Stage);
4367 IF X_Err_Code <> 'S' THEN
4368 RAISE FND_API.G_EXC_ERROR;
4369 END IF;
4370
4371 G_Stage := '(1150:CALLING GET_TOTAL_FUNDING_BUDGET)';
4372 /* Getting the total Funding(Baselined + Unbaselined) for the Project */
4373 GET_TOTAL_FUNDING_BUDGET(X_Award_Project_Id,
4374 X_Total_Funding_Budget,
4375 X_Err_Code,
4376 X_Err_Stage);
4377 IF X_Err_Code <> 'S' THEN
4381
4378 RAISE FND_API.G_EXC_ERROR;
4379 END IF;
4380 --dbms_output.put_line('Total FUNDING BUDGET BEFORE CREATE_AWARD_PROJECT_BUDGET '||X_Total_Funding_Budget);
4382
4383 G_Stage := '(1160:CALLING CREATE_AWARD_PROJECT_BUDGET)';
4384 -- Deletes any existing draft revenue budget and creates new draft Revenue Budget
4385 CREATE_AWARD_PROJECT_BUDGET( X_Award_Project_Id,
4386 X_Total_Funding_Budget,
4387 X_Project_Start_Date,
4388 X_Project_End_Date,
4389 X_App_Short_Name,
4390 X_Err_Code,
4391 X_Err_Stage);
4392
4393 --dbms_output.put_line('Got OUT NOCOPY of the CREATE_AWARD_PROJECT_BUDGET');
4394
4395 IF X_Err_Code <> 'S' THEN
4396 RAISE FND_API.G_EXC_ERROR;
4397 END IF;
4398
4399 END;
4400 EXCEPTION
4401 WHEN FND_API.G_EXC_ERROR THEN
4402 X_Msg_Count := p_msg_count;
4403 ERRBUF := X_Err_Stage;
4404 RETCODE := X_Err_Code;
4405
4406 -- Added when others exception for Bug:2662848
4407 WHEN OTHERS THEN
4408 RETCODE := 'U';
4409 FND_MSG_PUB.add_exc_msg
4410 ( p_pkg_name => G_PKG_NAME
4411 ,p_procedure_name => l_api_name
4412 ,p_error_text => substrb(SQLERRM||' at stage='||g_stage||' ',1,240)
4413 );
4414 FND_MSG_PUB.Count_And_Get
4415 ( p_count => p_msg_count,
4416 p_data => X_Err_Stage);
4417 ERRBUF := SQLERRM||' at stage='||g_stage||' '||X_Err_Stage;
4418 END UPDATE_AWARD_FUNDING;
4419
4420 PROCEDURE DELETE_AWARD_FUNDING(X_Project_Funding_Id IN NUMBER,
4421 X_Installment_Id IN NUMBER,
4422 X_Allocated_Amount IN NUMBER,
4423 X_App_Short_Name OUT NOCOPY VARCHAR2,
4424 X_Msg_Count OUT NOCOPY NUMBER,
4425 RETCODE OUT NOCOPY VARCHAR2,
4426 ERRBUF OUT NOCOPY VARCHAR2)IS
4427 X_Err_Code VARCHAR2(1);
4428 X_Err_Stage VARCHAR2(200);
4429 X_Text VARCHAR2(200);
4430 --X_Msg_Count NUMBER;
4431
4432 X_Award_Id NUMBER(15);
4433 X_Award_Project_Id NUMBER(15);
4434 X_Project_Start_Date DATE;
4435 X_Project_End_Date DATE;
4436 X_Agreement_Id NUMBER(15);
4437 X_Total_Unbaselined_Amount NUMBER(22,5) := 0;
4438 X_Total_Baselined_Amount NUMBER(22,5) := 0;
4439 X_Total_Funding_Budget NUMBER(22,5) := 0;
4440
4441 -- Bug 2270436 : Added function pa_funding_exists
4442 -- This function checks for existence of any record in pa_project_fundings before
4443 -- deleting records FROM pa_summary_project_fundings
4444
4445 FUNCTION pa_funding_exists (p_agreement_id NUMBER, p_award_project_id NUMBER)
4446 RETURN BOOLEAN
4447 IS
4448 CURSOR c_funding_exists
4449 IS
4450 SELECT 1
4451 FROM DUAL
4452 WHERE EXISTS (SELECT 1
4453 FROM pa_project_fundings
4454 WHERE agreement_id = p_agreement_id
4455 AND project_id = p_award_project_id);
4456
4457 x_dummy NUMBER := 0;
4458 BEGIN
4459 OPEN c_funding_exists;
4460 FETCH c_funding_exists INTO x_dummy;
4461 CLOSE c_funding_exists;
4462
4463 IF x_dummy <> 0
4464 THEN
4465 RETURN TRUE;
4466 ELSE
4467 RETURN FALSE;
4468 END IF;
4469 END;
4470
4471
4472
4473 BEGIN
4474 G_Stage := '(800:CALLING GET_PROJ_START_AND_END_DATE)';
4475 BEGIN
4476 --Get Award Project Id for the Award of which this Agreement is part of // -
4477 GET_PROJ_START_AND_END_DATE(X_Installment_Id,
4478 X_Award_Id,
4479 X_Award_Project_Id,
4480 X_Project_Start_Date,
4481 X_Project_End_Date,
4482 X_Agreement_Id,
4483 X_Err_Code,
4484 X_Err_Stage);
4485 IF X_Err_Code <> 'S' THEN
4486 RAISE FND_API.G_EXC_ERROR;
4487 END IF;
4488 END;
4489
4490 G_Stage := '(810:CALLING GMS_MULTI_FUNDING.UPDATE_AGREEMENT)';
4491 BEGIN
4492 /* UPDATE Award Project's Agreement with the new Amount */
4493
4494 GMS_MULTI_FUNDING.UPDATE_AGREEMENT(X_Agreement_Id => X_Agreement_Id,
4495 X_Agreement_Num => NULL,
4496 X_Agreement_Type => NULL,
4497 X_Revenue_Limit_Flag => NULL,
4498 X_Invoice_Limit_Flag => NULL, /*Bug 6642901*/
4499 X_Customer_Id => NULL,
4500 X_Owned_By_Person_Id => NULL,
4501 X_Term_Id => NULL,
4502 X_Amount => (-1*X_Allocated_Amount),
4503 X_Close_Date => NULL,
4504 RETCODE => X_Err_Code,
4505 ERRBUF => X_Err_Stage);
4506
4507 IF X_Err_Code <> 'S' THEN
4508 RAISE FND_API.G_EXC_ERROR;
4509 END IF;
4510 END;
4511 G_Stage := '(820:CALLING DELETE_DETAIL_PROJECT_FUNDING)';
4512 BEGIN
4513 --Delete row FROM PA_PROJECT_FUNDING for Award Project //
4514 DELETE_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id,
4515 X_Err_Code,
4516 X_Err_Stage);
4517 IF X_Err_Code <> 'S' THEN
4518 RAISE FND_API.G_EXC_ERROR;
4519 END IF;
4520
4521 G_Stage := '(830:CALLING GET_TOTAL_FUNDING_AMOUNT)';
4522 GET_TOTAL_FUNDING_AMOUNT(X_Agreement_Id,
4523 X_Award_Project_Id,
4524 X_Total_Unbaselined_Amount,
4525 X_Total_Baselined_Amount,
4526 X_Err_Code,
4527 X_Err_Stage) ;
4528 IF X_Err_Code <> 'S' THEN
4529 RAISE FND_API.G_EXC_ERROR;
4530 END IF;
4531
4532 X_Total_Unbaselined_Amount := X_Total_Unbaselined_Amount - X_Allocated_Amount;
4533
4534 G_Stage := '(840:CALLING DELETE_SUMMARY_PROJECT_FUNDING)';
4535 IF ((X_Total_Baselined_Amount + X_Total_Unbaselined_Amount = 0)
4536 and not pa_funding_exists(X_Agreement_Id,X_Award_Project_Id) -- Bug 2270436
4537 )
4538 THEN
4539 G_Stage := '(850:CALLING DELETE_SUMMARY_PROJECT_FUNDING)';
4540 DELETE_SUMMARY_PROJECT_FUNDING(X_Agreement_Id,
4541 X_Award_Project_Id,
4542 X_Err_Code,
4543 X_Err_Stage);
4544 IF X_Err_Code <> 'S' THEN
4545 RAISE FND_API.G_EXC_ERROR;
4546 END IF;
4547
4548 G_Stage := '(860:CALLING GET_TOTAL_FUNDING_BUDGET)';
4549 /* Getting the total Funding(Baselined + Unbaselined) for the Project */
4550 GET_TOTAL_FUNDING_BUDGET(X_Award_Project_Id,
4551 X_Total_Funding_Budget,
4552 X_Err_Code,
4553 X_Err_Stage);
4554 IF X_Err_Code <> 'S' THEN
4555 RAISE FND_API.G_EXC_ERROR;
4556 END IF;
4557
4558 G_Stage := '(870:CALLING DRAFT_BUDGET_EXISTS)';
4559 IF X_Total_Funding_Budget = 0 THEN
4560
4561 IF DRAFT_BUDGET_EXISTS(X_Award_Project_Id,
4562 X_Err_Code,
4563 X_Err_Stage) THEN
4564
4565 G_Stage := '(880:CALLING PA_BUDGET_PUB.DELETE_DRAFT_BUDGET)';
4566 PA_BUDGET_PUB.DELETE_DRAFT_BUDGET
4567 (p_api_version_number => 1.0,
4568 p_pa_project_id => X_Award_Project_Id,
4569 p_pm_project_reference => to_char(X_Award_Id),
4570 p_pm_product_code => 'GMS',
4571 p_budget_type_code => 'AR',
4572 p_msg_count => X_Msg_Count,
4573 p_msg_data => X_Err_Stage,
4574 p_return_status => X_Err_Code);
4575 IF X_Err_Code <> 'S' THEN
4576 /* FND_MESSAGE.PARSE_ENCODED (encoded_message => X_Err_Stage,
4577 APP_SHORT_NAME => X_App_Short_Name,
4578 MESSAGE_NAME => X_Text );
4579 X_Err_Stage := X_Text;
4580 */
4581 RAISE FND_API.G_EXC_ERROR;
4582 END IF;
4583 END IF;
4584
4585 G_Stage := '(890:CALLING DELETE_BASELINED_VERSIONS)';
4586 /* Delete all the Existing Baselined Budgets also */
4587 DELETE_BASELINED_VERSIONS
4588 (X_Award_Project_Id,
4589 X_Err_Code,
4590 X_Err_Stage);
4591 IF X_Err_Code <> 'S' THEN
4592 RAISE FND_API.G_EXC_ERROR;
4593 END IF;
4594
4595 END IF;
4596
4597 ELSE
4598 G_Stage := '(900:CALLING UPDATE_PA_SUMM_PROJECT_FUNDING)';
4599 UPDATE_PA_SUMM_PROJECT_FUNDING(X_Agreement_Id,
4600 X_Award_Project_Id,
4601 X_Total_Unbaselined_Amount,
4602 X_Err_Code,
4603 X_Err_Stage);
4604 IF X_Err_Code <> 'S' THEN
4605 RAISE FND_API.G_EXC_ERROR;
4606 END IF;
4607 G_Stage := '(910:CALLING UPDATE_PA_SUMM_PROJECT_FUNDING)';
4608 GET_TOTAL_FUNDING_BUDGET(X_Award_Project_Id,
4609 X_Total_Funding_Budget,
4610 X_Err_Code,
4611 X_Err_Stage);
4612 IF X_Err_Code <> 'S' THEN
4613 RAISE FND_API.G_EXC_ERROR;
4614 END IF;
4615 G_Stage := '(920:CALLING CREATE_AWARD_PROJECT_BUDGET)';
4616 CREATE_AWARD_PROJECT_BUDGET(X_Award_Project_Id,
4617 X_Total_Funding_Budget,
4618 X_Project_Start_Date,
4619 X_Project_End_Date,
4620 X_App_Short_Name,
4621 X_Err_Code,
4622 X_Err_Stage);
4623 IF X_Err_Code <> 'S' THEN
4624 RAISE FND_API.G_EXC_ERROR;
4625 END IF;
4626 END IF;
4627 END;
4628 EXCEPTION
4629 WHEN FND_API.G_EXC_ERROR THEN
4630 X_Msg_Count := p_msg_count;
4631 ERRBUF := 'At stage='||g_stage||' '||X_Err_Stage;
4632 RETCODE := X_Err_Code;
4633 -- Added when OTHERS exception for Bug:2662848
4634 WHEN OTHERS THEN
4635 RETCODE := 'U';
4636 FND_MSG_PUB.add_exc_msg
4637 ( p_pkg_name => 'GMS_MULTI_FUNDING'
4638 , p_procedure_name => 'DELETE_AWARD_FUNDING');
4639 FND_MSG_PUB.Count_And_Get
4640 ( p_count => p_msg_count,
4641 p_data => X_Err_Stage);
4642 ERRBUF := 'At stage='||g_stage||' '||X_Err_Stage;
4643
4644 RAISE; --This raise is not required but keeing as is for now.
4645
4646 END DELETE_AWARD_FUNDING;
4647
4648 END GMS_MULTI_FUNDING;