[Home] [Help]
PACKAGE BODY: APPS.GMS_AWARD_PVT
Source
1 PACKAGE BODY GMS_AWARD_PVT AS
2 -- $Header: gmsawpvb.pls 120.7.12020000.4 2013/03/05 18:56:13 arbandyo ship $
3
4 -- To check on, whether to print debug messages in log file or not
5 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
6
7 G_award_rec GMS_AWARDS_ALL%ROWTYPE ;
8 g_api_version_number CONSTANT NUMBER := 1.0;
9 g_pkg_name CONSTANT VARCHAR2(30) := 'GMS_AWARD_PVT';
10 e_ver_mismatch EXCEPTION ;
11
12 G_personnel_rec GMS_PERSONNEL%ROWTYPE ;
13 G_reference_number_rec GMS_REFERENCE_NUMBERS%ROWTYPE ;
14 G_contact_rec GMS_AWARDS_CONTACTS%ROWTYPE ;
15 G_report_rec GMS_DEFAULT_REPORTS%ROWTYPE ;
16 G_notification_rec GMS_NOTIFICATIONS%ROWTYPE ;
17
18 G_gmsimpl_rec GMS_IMPLEMENTATIONS_ALL%ROWTYPE ;
19 G_term_condition_rec GMS_AWARDS_TERMS_CONDITIONS%ROWTYPE ;
20 G_installment_rec GMS_INSTALLMENTS%ROWTYPE ;
21 G_msg_count NUMBER ;
22
23 G_msg_data varchar2(2000) ;
24 G_calling_module varchar2(30) ;
25 G_product_code varchar2(3) := 'GMS' ;
26 G_stage varchar2(80) ;
27
28 G_pub_msg_int BOOLEAN ;
29
30 -- ================
31 -- Contacts details used to create
32 -- billing contacts.
33 -- ================
34
35 TYPE billing_contact_type is RECORD
36 (
37 customer_id NUMBER,
38 bill_to_address_id NUMBER,
39 ship_to_address_id NUMBER,
40 bill_to_contact_id NUMBER,
41 ship_to_contact_id NUMBER
42 ) ;
43
44 G_bill_contact_rec billing_contact_type ;
45
46
47 -- ==============
48 -- get_implementation_record is a program unit created
49 -- to populate implementation record.
50 -- ==============
51 PROCEDURE get_implementation_record is
52 BEGIN
53 -- +++ FETCH Implementations Record +++
54 IF g_award_rec.org_id is NULL THEN
55 select *
56 into G_gmsimpl_rec
57 from gms_implementations_all
58 where org_id is NULL ;
59
60 ELSE
61 select *
62 into G_gmsimpl_rec
63 from gms_implementations_all
64 where org_id = g_award_rec.org_id ;
65 END IF ;
66 END get_implementation_record ;
67
68 -- ===================================================
69 -- Utility Functions
70 -- ===================================================
71 PROCEDURE reset_message_flag is
72 begin
73 G_pub_msg_int := FALSE ;
74 END reset_message_flag;
75
76 -- =======
77 -- init_message_stack
78 -- Call FND_MSG_PUB to initialize the message stack.
79 -- =======
80
81 PROCEDURE init_message_stack is
82 begin
83 IF G_pub_msg_int THEN
84 NULL ;
85 ELSE
86 FND_MSG_PUB.Initialize;
87 G_pub_msg_int := TRUE ;
88 END IF ;
89 END init_message_stack ;
90
91 -- =======
92 -- add_message_to_stack
93 -- This procedure add messages to the stack table.
94 -- =======
95
96 PROCEDURE add_message_to_stack( P_Label IN Varchar2,
97 P_token1 IN varchar2 DEFAULT NULL,
98 P_val1 IN varchar2 DEFAULT NULL,
99 P_token2 IN varchar2 DEFAULT NULL,
100 P_val2 in varchar2 DEFAULT NULL,
101 P_token3 IN varchar2 DEFAULT NULL,
102 P_val3 in varchar2 DEFAULT NULL ) is
103 L_return_status varchar2(2000) ;
104 BEGIN
105
106 IF P_label is not NULL THEN
107 fnd_message.set_name( 'GMS', P_Label ) ;
108 ELSE
109 return ;
110 END IF ;
111
112 IF P_token1 is not NULL then
113 fnd_message.set_token(P_token1, P_val1 ) ;
114 END IF ;
115
116 IF P_token2 is not NULL then
117 fnd_message.set_token(P_token2, P_val2 ) ;
118 END IF ;
119
120 IF P_token3 is not NULL then
121 fnd_message.set_token(P_token3, P_val3 ) ;
122 END IF ;
123
124 fnd_msg_pub.add ;
125
126 FND_MSG_PUB.Count_And_Get
127 ( p_count => G_msg_count ,
128 p_data => G_msg_data );
129
130 IF L_DEBUG = 'Y' THEN
131 gms_error_pkg.gms_debug('Stage :'||G_stage||' Label :'||p_label, 'C');
132 END IF;
133
134
135 IF FND_MSG_PUB.check_msg_level( NVL(G_msg_count,0) ) THEN
136 Raise FND_API.G_EXC_ERROR ;
137 ELSE
138 NULL ;
139 END IF;
140
141 END add_message_to_stack ;
142
143 -- -----------------------------------------------------------
144 -- X_RETURN_STATUS : <S>uccess, [E] Business Rule Violation
145 -- U - Unexpected Error
146 -- P_TYPE := B - Business Validations, E- Exception
147 -- ----------------------------------------------------------
148
149 PROCEDURE set_return_status(X_return_status IN OUT NOCOPY VARCHAR2,
150 p_type in varchar2 DEFAULT 'B' ) is
151 begin
152
153 IF p_type = 'B' THEN
154
155 IF NVL(X_return_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS then
156 X_return_status := FND_API.G_RET_STS_ERROR ;
157 END IF ;
158 ELSE
159
160 IF NVL(X_return_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS then
161 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
162 END IF ;
163 END IF ;
164 END set_return_status ;
165
166 -- ==========================
167 -- End of the utility functions.
168 -- ============================
169
170
171
172 -- --------------------------------------------------
173 -- Define local procedure references to create_award
174 -- --------------------------------------------------
175 PROCEDURE create_agreement ( p_agreement_id OUT NOCOPY NUMBER ) ;
176 PROCEDURE validate_award ( X_return_status IN OUT NOCOPY varchar2) ;
177 PROCEDURE create_award_project ( X_return_status IN OUT NOCOPY varchar2,
178 p_setup_award_project_id NUMBER) ;
179 PROCEDURE insert_award_record ( X_return_status in out NOCOPY varchar2) ;
180
181 PROCEDURE validate_award_quick_entry ( X_return_status IN OUT NOCOPY varchar2 ) ;
182
183 -- ===============================================================================
184 -- CREATE AWARD BEGINS HERE
185 --
186 -- Procedure : Create Award
187 -- Calling Module : gms_award_pub.create_award
188 -- Proposal
189 -- Thi API creates an award project and award.
190 -- ===============================================================================
191 PROCEDURE create_award( x_msg_count IN OUT NOCOPY NUMBER ,
192 x_msg_data IN OUT NOCOPY varchar2,
193 X_return_status IN OUT NOCOPY varchar2,
194 X_row_id OUT NOCOPY VARCHAR2,
195 X_award_id OUT NOCOPY NUMBER ,
196 p_calling_module IN VARCHAR2 ,
197 p_api_version_number IN NUMBER ,
198 p_award_rec IN GMS_AWARDS_ALL%ROWTYPE )
199 IS
200 L_contact_rec GMS_AWARDS_CONTACTS%ROWTYPE ;
201 L_personnel_rec gms_personnel%ROWTYPE ;
202
203 L_setup_award_project_id NUMBER ;
204
205
206 L_org_id NUMBER ;
207
208 L_api_name varchar2(30) := 'GMS_AWARD_PVT.CREATE_AWARD';
209 L_row_id varchar2(45) ;
210
211 l_validate BOOLEAN := FALSE ;
212
213 -- ---------
214 -- Cursor decalred to find out NOCOPY award project exists for a
215 -- operating unit.
216 -- ---------
217
218 -- Bug Fix 3056424
219 -- Modifying the following cursor to pick up the award project template
220 -- whose value is still having -999 after multi org conversion.
221 -- Please refer to bug 2447491 for further information on this issue.
222 -- The segment1 can be either having org_id or -999 concatenated.
223 -- So the where clause is rewritten to reflect the same.
224 -- End of fix 3056424.
225
226 CURSOR c_awd_project is
227 Select project_id
228 from PA_PROJECTS_ALL
229 where project_type = 'AWARD_PROJECT'
230 and template_flag = 'Y'
231 and (segment1 = 'AWD_PROJ_-999'
232 or segment1 = 'AWD_PROJ_'||TO_CHAR(L_org_id))
233 and rownum = 1;
234
235 --Bug : 3455542 : Added by Sanjay Banerjee
236 --Rewriting the cursor to use HZ tables instead of RA Tables :TCA Changes
237 CURSOR C_verify_con_exists(p_customer_id number) IS
238 SELECT 'X'
239 FROM Hz_party_sites party_site,
240 Hz_locations loc,
241 Hz_cust_acct_sites_all acct_site,
242 hz_cust_site_uses su
243 WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
244 AND acct_site.party_site_id = party_site.party_site_id
245 AND loc.location_id = party_site.location_id
246 AND acct_site.cust_account_id = p_customer_id
247 AND Nvl(su.Status, 'A') = 'A'
248 AND su.Site_Use_Code IN ( 'BILL_TO', 'SHIP_TO')
249 AND su.primary_flag = 'Y'
250 AND su.Contact_Id IS NOT NULL;
251 l_con_exists VARCHAR2(1);
252
253 -- ============
254 -- Procedure Created to set the default values
255 -- ============
256
257 PROCEDURE set_award_default_values ( X_return_status IN OUT NOCOPY varchar2 ) IS
258 BEGIN
259 g_award_rec.revenue_distribution_rule := NVL(g_award_rec.revenue_distribution_rule,'COST' ) ;
260 g_award_rec.billing_distribution_rule := NVL(g_award_rec.billing_distribution_rule,'COST' ) ;
261 g_award_rec.amount_type := NVL(g_award_rec.amount_type,'PJTD' ) ;
262 g_award_rec.boundary_code := NVL(g_award_rec.boundary_code,'J' ) ;
263 g_award_rec.fund_control_level_award := NVL(g_award_rec.fund_control_level_award,'B' ) ;
264 g_award_rec.fund_control_level_task := NVL(g_award_rec.fund_control_level_task,'D' ) ;
265 g_award_rec.fund_control_level_res_grp:= NVL(g_award_rec.fund_control_level_res_grp,'D' ) ;
266 g_award_rec.fund_control_level_res := NVL(g_award_rec.fund_control_level_res,'D' ) ;
267 g_award_rec.hard_limit_flag := NVL(g_award_rec.hard_limit_flag,'N' ) ;
268 g_award_rec.billing_offset := NVL(g_award_rec.billing_offset ,'0' ) ;
269 g_award_rec.billing_format := NVL(g_award_rec.billing_format ,'NO_PRINT' ) ;
270 g_award_rec.budget_wf_enabled_flag := NVL(g_award_rec.budget_wf_enabled_flag,'N' ) ;
271 g_award_rec.hard_limit_flag := NVL(g_award_rec.hard_limit_flag,'N' ) ;
272 g_award_rec.invoice_limit_flag := NVL(g_award_rec.invoice_limit_flag,'N' ) ; /*Bug 6642901*/
273
274 IF g_award_rec.award_template_flag = 'DEFERRED' then
275 g_award_rec.template_start_date_active := NULL ;
276 g_award_rec.template_end_date_active := NULL ;
277 END IF ;
278
279 END set_award_default_values ;
280
281 -- +++++++++++++++++
282 --
283 -- Populates the billing contacts record
284 -- group here.
285 --
286 PROCEDURE get_award_contacts( X_return_status IN OUT NOCOPY varchar2 ) is
287
288 l_customer_id NUMBER ;
289 l_bill_to_adr NUMBER ;
290 l_ship_to_adr NUMBER ;
291 l_bill_to_cont NUMBER ;
292 l_ship_to_cont NUMBER ;
293 l_error BOOLEAN ;
294
295 l_usage_code varchar2(10) ;
296
297 --
298 -- Cursor to fetch the contact details.
299 --TCA Enhancement : Replaced ra_address_all, ra_site_uses
300 CURSOR C_cust_info is
301 SELECT acct_site.cust_acct_site_id,
302 su.Contact_Id
303 FROM hz_cust_acct_sites_all acct_site,
304 hz_party_sites party_site,
305 hz_locations loc,
306 Hz_cust_site_uses su
307 Where acct_site.cust_acct_site_id = su.cust_acct_site_id
308 And acct_site.cust_account_id = l_Customer_Id
309 And acct_site.party_site_id = party_site.party_site_id
310 And loc.location_id = party_site.location_id
311 And Nvl(su.Status, 'A') = 'A'
312 And su.Site_Use_Code = l_usage_code
313 And su.primary_flag = 'Y' ;
314 BEGIN
315 IF g_award_rec.billing_format = 'LOC' THEN
316 l_customer_id := g_award_rec.bill_to_customer_id ;
317 l_bill_to_adr := g_award_rec.loc_bill_to_address_id ;
318 l_ship_to_adr := g_award_rec.loc_ship_to_address_id ;
319 ELSE
320 l_customer_id := g_award_rec.funding_source_id ;
321 l_bill_to_adr := g_award_rec.bill_to_address_id ;
322 l_ship_to_adr := g_award_rec.ship_to_address_id ;
323 END IF ;
324
325 l_usage_code := 'BILL_TO' ;
326
327 open c_cust_info ;
328 fetch c_cust_info into l_bill_to_adr, l_bill_to_cont ;
329
330 IF c_cust_info%NOTFOUND THEN
331
332 -- MSG : GMS_NO_BILL_TO_ADDRESS
333 -- MSG : GMS_NO_BILL_TO_CONTACT
334 -- -----------------------------
335 add_message_to_stack( P_label => 'GMS_NO_BILL_TO_ADDRESS' ) ;
336 -- add_message_to_stack( P_label => 'GMS_NO_BILL_TO_CONTACT' ) ;
337 set_return_status( X_return_status, 'B') ;
338 l_error := TRUE ;
339 END IF ;
340 close c_cust_info ;
341
342 l_usage_code := 'SHIP_TO' ;
343
344 open c_cust_info ;
345 fetch c_cust_info into l_ship_to_adr, l_ship_to_cont ;
346
347 IF c_cust_info%NOTFOUND THEN
348
349 -- MSG : GMS_NO_SHIP_TO_ADDRESS
350 -- MSG : GMS_NO_SHIP_TO_CONTACT
351 -- ------------------------------
352 add_message_to_stack( P_label => 'GMS_NO_SHIP_TO_ADDRESS' ) ;
353 -- add_message_to_stack( P_label => 'GMS_NO_SHIP_TO_CONTACT' ) ;
354 set_return_status( X_return_status, 'B') ;
355 l_error := TRUE ;
356 END IF ;
357 close c_cust_info ;
358
359 IF L_error THEN
360 set_return_status( X_return_status, 'B') ;
361 ELSE
362 --columns are used in creating gms_awards_contacts records.
363 G_bill_contact_rec.bill_to_contact_id := l_bill_to_cont ;
364 G_bill_contact_rec.ship_to_contact_id := l_ship_to_cont ;
365 G_bill_contact_rec.customer_id := l_customer_id ;
366 G_bill_contact_rec.bill_to_address_id := l_bill_to_adr ;
367 G_bill_contact_rec.ship_to_address_id := l_ship_to_adr ;
368 --Bug : 3455542 : Added by Sanjay Banerjee
369 --Following address columns are always ment for 'funding source'
370 --customers ( for both LOC and Non-LOC ). Please do not copy LOC
371 --address on these columns. Otherwise funding_source_id will go
372 --out of sync with bill_to_address_id and ship_to_address_id.
373
374 --columns are used while creating record in gms_awards table.
375 IF g_award_rec.billing_format = 'LOC' THEN
376
377 --now we need to fill correct address for 'funding source'
378 l_customer_id := g_award_rec.bill_to_customer_id ; /* g_award_rec.funding_source_id Bug 14499461*/
379 l_bill_to_adr := g_award_rec.loc_bill_to_address_id ; /*g_award_rec.bill_to_address_id Bug 14499461*/
380 l_ship_to_adr := g_award_rec.loc_ship_to_address_id ; /* g_award_rec.ship_to_address_id Bug 14499461*/
381
382 l_usage_code := 'BILL_TO' ;
383
384 open c_cust_info ;
385 fetch c_cust_info into l_bill_to_adr, l_bill_to_cont ;
386
387 IF c_cust_info%NOTFOUND THEN
388
389 add_message_to_stack( P_label => 'GMS_NO_BILL_TO_ADDRESS' ) ;
390 set_return_status( X_return_status, 'B') ;
391 l_error := TRUE ;
392 END IF ;
393 close c_cust_info ;
394
395 l_usage_code := 'SHIP_TO' ;
396
397 open c_cust_info ;
398 fetch c_cust_info into l_ship_to_adr, l_ship_to_cont ;
399
400 IF c_cust_info%NOTFOUND THEN
401
402 add_message_to_stack( P_label => 'GMS_NO_SHIP_TO_ADDRESS' ) ;
403 set_return_status( X_return_status, 'B') ;
404 l_error := TRUE ;
405 END IF ;
406 close c_cust_info ;
407
408 IF L_error THEN
409 set_return_status( X_return_status, 'B') ;
410 END IF;
411
412 END IF ; /* LOC */
413
414 g_award_rec.bill_to_address_id := l_bill_to_adr ;
415 g_award_rec.ship_to_address_id := l_ship_to_adr ;
416
417 END IF ;
418
419 END get_award_contacts ;
420 -- ===== End of proc_get_contacts ====
421
422 --
423 -- Check Not Null columns values here.
424 --
425 PROCEDURE verify_award_required_columns( X_return_status IN OUT NOCOPY varchar2 ) IS
426
427 l_error BOOLEAN ;
428 BEGIN
429 l_error := FALSE ;
430
431 IF g_award_rec.award_template_flag is NULL THEN
432 -- =============
433 -- MSG: AWARD_TERMPLATE_UNDEFINED
434 -- --------------------------------------
435 add_message_to_stack( P_label => 'GMS_AWD_TEMPLATE_FLAG_NULL' ) ;
436 l_error := TRUE ;
437
438 END IF ;
439
440 IF g_award_rec.award_short_name is NULL THEN
441
442 -- ---------------------------------
443 -- MSG: AWARD_SHORT_NAME_NULL
444 -- ---------------------------------
445 l_error := TRUE ;
446 add_message_to_stack( P_label => 'GMS_AWD_SHORT_NAME_NULL' ) ;
447 END IF ;
448
449 IF L_error THEN
450 set_return_status(X_return_status, 'B') ;
451 END IF ;
452
453 IF g_award_rec.award_template_flag <> 'DEFERRED' THEN
454 return ;
455 END IF ;
456
457 IF g_award_rec.award_full_name is NULL THEN
458 -- ------------------------------
459 -- MSG: AWARD_FULL_NAME_NULL
460 -- ------------------------------
461 add_message_to_stack( P_label => 'GMS_AWD_FULL_NAME_NULL' ) ;
462 l_error := TRUE ;
463 END IF ;
464
465 IF g_award_rec.funding_source_id is NULL THEN
466 -- ------------------------------
467 -- MSG: FUNDING_SOURCE_ID_NULL
468 -- ------------------------------
469 add_message_to_stack( P_label => 'GMS_AWD_FUND_SOURCE_NULL' ) ;
470 l_error := TRUE ;
471 END IF ;
472
473 IF g_award_rec.start_date_active is NULL THEN
474 -- ------------------------------
475 -- MSG: START_DATE_ACTIVE_IS_NULL
476 -- ------------------------------
477 add_message_to_stack( P_label => 'GMS_AWD_START_DT_NULL' ) ;
478 l_error := TRUE ;
479 END IF ;
480
481 IF g_award_rec.end_date_active is NULL THEN
482 -- ------------------------------
483 -- MSG: END_DATE_ACTIVE_IS_NULL
484 -- ------------------------------
485 add_message_to_stack( P_label => 'GMS_AWD_END_DT_NULL' ) ;
486 l_error := TRUE ;
487 END IF ;
488
489 IF g_award_rec.close_date is NULL THEN
490 -- ------------------------------
491 -- MSG: CLOSE_DATE_ACTIVE_IS_NULL
492 -- ------------------------------
493 add_message_to_stack( P_label => 'GMS_AWD_CLOSE_DT_NULL' ) ;
494 l_error := TRUE ;
495 END IF ;
496
497 IF g_award_rec.funding_source_award_number is NULL THEN
498 -- ------------------------------
499 -- MSG: FUNDING_SOURCE_AWD_NULL
500 -- ------------------------------
501 add_message_to_stack( P_label => 'GMS_AWD_FUND_SOURCE_NUM_NULL' ) ;
502 l_error := TRUE ;
503 END IF ;
504
505 IF g_award_rec.award_purpose_code is NULL THEN
506 -- ------------------------------
507 -- MSG: AWARD_PURPOSE_CODE_NULL
508 -- ------------------------------
509 add_message_to_stack( P_label => 'GMS_AWD_PURPOSE_CODE_NULL' ) ;
510 l_error := TRUE ;
511 END IF ;
512
513 IF g_award_rec.status is NULL THEN
514 -- ------------------------------
515 -- MSG: AWARD_STATUS_NULL
516 -- ------------------------------
517 add_message_to_stack( P_label => 'GMS_AWD_STATUS_NULL' ) ;
518 l_error := TRUE ;
519 END IF ;
520
521 IF g_award_rec.allowable_schedule_id is NULL THEN
522 -- ------------------------------
523 -- MSG: ALLOWABILITY_SCH_NULL
524 -- ------------------------------
525 add_message_to_stack( P_label => 'GMS_AWD_ALLOWABLE_SCH_NULL' ) ;
526 l_error := TRUE ;
527 END IF ;
528
529 IF g_award_rec.idc_schedule_id is NULL THEN
530 -- ------------------------------
531 -- MSG: INDIRECT_SCH_NULL
532 -- ------------------------------
533 add_message_to_stack( P_label => 'GMS_AWD_INDIRECT_SCH_NULL' ) ;
534 l_error := TRUE ;
535 END IF ;
536
537 IF g_award_rec.revenue_distribution_rule is NULL THEN
538 -- ------------------------------
539 -- MSG: REV_DIST_RULE_NULL
540 -- ------------------------------
541 add_message_to_stack( P_label => 'GMS_AWD_REV_DIST_NULL' ) ;
542 l_error := TRUE ;
543 END IF ;
544
545 IF g_award_rec.billing_distribution_rule is NULL THEN
546 -- ------------------------------
547 -- MSG: WBILL_DIST_NULL
548 -- ------------------------------
549 add_message_to_stack( P_label => 'GMS_AWD_BILL_DIST_NULL' ) ;
550 l_error := TRUE ;
551 END IF ;
552
553 IF g_award_rec.award_manager_id is NULL THEN
554 -- ------------------------------
555 -- MSG: AWARD_MANAGER_NULL
556 -- ------------------------------
557 add_message_to_stack( P_label => 'GMS_AWD_MANAGER_NULL' ) ;
558 l_error := TRUE ;
559 END IF ;
560
561 IF g_award_rec.amount_type is NULL THEN
562 -- ------------------------------
563 -- MSG: AMOUNT_TYPE_NULL
564 -- ------------------------------
565 l_error := TRUE ;
566 add_message_to_stack( P_label => 'GMS_AWD_AMOUNT_TYPE_NULL' ) ;
567 END IF ;
568
569 IF g_award_rec.boundary_code is NULL THEN
570 -- ------------------------------
571 -- MSG: BOUNDARY_CODE_NULL
572 -- ------------------------------
573 add_message_to_stack( P_label => 'GMS_AWD_BOUNDARY_CODE_NULL' ) ;
574 l_error := TRUE ;
575 END IF ;
576
577 IF g_award_rec.type is NULL THEN
578 -- ------------------------------
579 -- MSG: TYPE_NULL
580 -- ------------------------------
581 add_message_to_stack( P_label => 'GMS_AWD_TYPE_NULL' ) ;
582 l_error := TRUE ;
583 END IF ;
584
585 IF g_award_rec.award_organization_id is NULL THEN
586 -- ------------------------------
587 -- MSG: AWARD_ORG_NULL
588 -- ------------------------------
589 add_message_to_stack( P_label => 'GMS_AWD_ORGANIZATION_NULL' ) ;
590 l_error := TRUE ;
591 END IF ;
592
593 IF g_award_rec.billing_cycle_id is NULL THEN
594 -- ------------------------------
595 -- MSG: BILLING_CYCLE_NULL
596 -- ------------------------------
597 add_message_to_stack( P_label => 'GMS_AWD_BILL_CYCLE_NULL' ) ;
598 l_error := TRUE ;
599 END IF ;
600
601 IF g_award_rec.billing_term is NULL THEN
602 -- ------------------------------
603 -- MSG: BILLING_TERM_NULL
604 -- ------------------------------
605 add_message_to_stack( P_label => 'GMS_AWD_BILL_TERM_NULL' ) ;
606 l_error := TRUE ;
607 END IF ;
608
609 IF g_award_rec.labor_invoice_format_id is NULL THEN
610 -- ------------------------------
611 -- MSG: LABOR_INV_FORMAT_NULL
612 -- ------------------------------
613 add_message_to_stack( P_label => 'GMS_LABOR_INV_FORMAT_NULL' ) ;
614 l_error := TRUE ;
615 END IF ;
616
617 IF g_award_rec.non_labor_invoice_format_id is NULL THEN
618 -- ------------------------------
619 -- MSG: NON_LABOR_INV_FORMAT_NULL
620 -- ------------------------------
621 add_message_to_stack( P_label => 'GMS_NONLABOR_INV_FORMAT_NULL' ) ;
622 l_error := TRUE ;
623 END IF ;
624
625 IF L_error THEN
626 set_return_status(X_return_status, 'B') ;
627 END IF ;
628 END verify_award_required_columns ;
629
630 -- ======
631 -- End of verify_award_required_columns
632 -- ======
633
634 BEGIN
635 -- Initialize the message stack.
636 -- -----------------------------
637 init_message_stack;
638
639 G_msg_count := x_msg_count ;
640 G_msg_data := x_MSG_DATA ;
641 G_calling_module := P_CALLING_MODULE ;
642
643 -- ============
644 -- Initialize the return status.
645 -- ============
646
647 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
648 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
649
650 X_return_status := FND_API.G_RET_STS_SUCCESS ;
651
652 END IF ;
653
654
655 SAVEPOINT save_award_pvt ;
656
657 -- ===================================================
658 -- Need to set global variables to use PA public APIs.
659 -- ===================================================
660
661 G_stage := 'pa_interface_utils_pub.set_global_info';
662
663 /* pa_interface_utils_pub.set_global_info(p_api_version_number => 1.0,
664 p_responsibility_id => FND_GLOBAL.resp_id,
665 p_user_id => FND_GLOBAL.user_id,
666 p_resp_appl_id => FND_GLOBAL.resp_appl_id, -- Bug 2534915
667 p_msg_count => x_msg_count,
668 p_msg_data =>x_msg_data,
669 p_return_status => x_return_status);
670
671 IF x_return_status <> 'S' THEN
672
673 add_message_to_stack( P_label => 'GMS_SET_GLOBAL_INFO_FAILED');
674 set_return_status ( X_return_status, 'U') ;
675
676 END IF;
677
678 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
679 RAISE FND_API.G_EXC_ERROR;
680 END IF ; */
681
682
683 G_stage := 'FND_API.Compatible_API_Call' ;
684
685 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
686 p_api_version_number ,
687 l_api_name ,
688 G_pkg_name )
689 THEN
690 RAISE e_ver_mismatch ;
691 END IF ;
692
693 -- Set the record to the global variable
694 --
695 G_stage := 'proc_set_record(P_AWARD_REC)' ;
696
697 G_award_rec := p_award_rec ;
698
699 -- =======
700 -- Fetch gms implementations record.
701 -- =======
702 G_stage := 'get_implementation_record' ;
703 get_implementation_record ;
704
705 G_stage := 'set_award_default_values' ;
706 set_award_default_values ( X_return_status ) ;
707
708 -- =============
709 -- Determine the award project ID
710 -- =============
711 L_org_id := g_award_rec.org_id ;
712 G_stage := 'Award Project Check' ;
713
714 open c_awd_project ;
715 fetch c_awd_project into l_setup_award_project_id ;
716
717 IF c_awd_project%NOTFOUND THEN
718 -- MSG : AWARD_PROJECT_NOT_FOUND
719 -- raise exit process
720
721 add_message_to_stack( P_label => 'GMS_AWD_PRJ_MISSING' ) ;
722 set_return_status ( X_return_status, 'B') ;
723 close c_awd_project ;
724 Raise fnd_api.g_exc_error ;
725
726 END IF ;
727
728 close c_awd_project ;
729
730 -- =============
731 -- Determine the Bill/SHIP contact details
732 -- =============
733 G_stage := 'Proc_get_contacts ' ;
734 get_award_contacts ( X_return_status ) ;
735
736 -- =============
737 -- Check The required Columns
738 -- =============
739 G_stage := 'Proc_check_required ' ;
740 verify_award_required_columns( X_return_status ) ;
741
742 -- =============
743 -- Award Quick Entry field validations.
744 -- =============
745 G_stage := 'Proc_quick_entry_checks ' ;
746 validate_award_quick_entry( X_return_status ) ;
747
748 G_stage := 'Proc_validate_award ' ;
749 validate_award( X_return_status ) ;
750
751 -- =================
752 -- Make sure that X_return_status is success before continue.
753 -- =================
754 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
755
756 Raise fnd_api.g_exc_error ;
757 END IF ;
758
759 -- ********* ERROR Return Here ********
760 -- =======
761 -- All the validations are done.
762 -- Creating the records in the tables.
763 -- =======
764
765 G_stage := 'Create_award_project ' ;
766 create_award_project ( X_return_status, l_setup_award_project_id ) ;
767
768 -- =================
769 -- Make sure that X_return_status is success before continue.
770 -- =================
771 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
772
773 -- ********* ERROR Return Here ********
774 Raise fnd_api.g_exc_error ;
775 END IF ;
776
777 G_stage := 'Insert_award ' ;
778 insert_award_record ( X_return_status ) ;
779
780 -- =================
781 -- Make sure that X_return_status is success before continue.
782 -- =================
783 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
784
785 -- ********* ERROR Return Here ********
786 Raise fnd_api.g_exc_error ;
787 END IF ;
788
789 -- ===============================================
790 -- Create Award Manger Personnel
791 -- ===============================================
792 IF X_return_status = FND_API.G_RET_STS_SUCCESS THEN
793
794 gms_award_manager_pkg.insert_award_manager_id ( g_award_rec.award_id,
795 g_award_rec.award_manager_id,
796 g_award_rec.start_date_active ) ;
797 END IF ;
798
799 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
800 Raise fnd_api.g_exc_error ;
801 END IF ;
802
803 -- =============
804 -- Creating Billing /Shipping contacts for award creation in progress.
805 -- =============
806
807 --Bug : 3455542 : Added by Sanjay Banerjee
808 -- GMS_CON_CONTACT_ID_NULL : Please enter a Contact ID for this award
809 -- Above error should be raised ONLY when BILL_TO and SHIP_TO both addresses
810 -- are blank. Also, there's no point calling create_contact when contact_id
811 -- itself is blank.
812 --
813 OPEN C_verify_con_exists(G_bill_contact_rec.customer_id);
814 FETCH C_verify_con_exists
815 INTO l_con_exists;
816 CLOSE C_verify_con_exists;
817
818 L_contact_rec.award_id := g_award_rec.award_id ;
819 L_contact_rec.contact_id := G_bill_contact_rec.bill_to_contact_id ;
820 L_contact_rec.customer_id := G_bill_contact_rec.customer_id ;
821 L_contact_rec.primary_flag := 'Y' ;
822 L_contact_rec.usage_code := 'BILL_TO' ;
823 L_contact_rec.last_update_date := g_award_rec.last_update_date ;
824 L_contact_rec.last_updated_by := g_award_rec.last_updated_by ;
825 L_contact_rec.creation_date := g_award_rec.creation_date ;
826 L_contact_rec.created_by := g_award_rec.created_by ;
827 L_contact_rec.last_update_login := g_award_rec.last_update_login ;
828
829 G_stage := 'Create_contact BILL_TO ' ;
830
831 --Bug : 3455542 : Added by Sanjay Banerjee
832 --Call this procedure ONLY if :
833 -- 1. BILL_TO and SHIP_TO both are missing
834 -- AND
835 -- 2. Contact_Id Is NOT NULL
836
837 IF ( l_con_exists IS NOT NULL AND L_contact_rec.contact_id IS NOT NULL ) OR
838 ( l_con_exists IS NULL AND L_contact_rec.contact_id IS NULL )
839 THEN
840
841 create_contact( x_msg_count
842 , x_msg_data
843 , x_return_status
844 , l_row_id
845 , p_calling_module
846 , p_api_version_number
847 , TRUE
848 , l_contact_rec
849 );
850
851 -- =================
852 -- Make sure that X_return_status is success before continue.
853 -- =================
854 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
855 -- ********* ERROR Return Here ********
856 Raise fnd_api.g_exc_error ;
857 END IF;
858 END IF;
859
860 L_contact_rec.contact_id := G_bill_contact_rec.ship_to_contact_id ;
861 L_contact_rec.customer_id := G_bill_contact_rec.customer_id ;
862 L_contact_rec.primary_flag := 'Y' ;
863 L_contact_rec.usage_code := 'SHIP_TO' ;
864
865 G_stage := 'Create_contact SHIP_TO ' ;
866
867 --Bug : 3455542 : Added by Sanjay Banerjee
868 IF ( l_con_exists IS NOT NULL AND L_contact_rec.contact_id IS NOT NULL ) OR
869 ( l_con_exists IS NULL AND L_contact_rec.contact_id IS NULL )
870 THEN
871
872 create_contact( x_msg_count
873 , x_msg_data
874 , x_return_status
875 , l_row_id
876 , p_calling_module
877 , p_api_version_number
878 , TRUE
879 , l_contact_rec
880 );
881
882 -- =================
883 -- Make sure that X_return_status is success before continue.
884 -- =================
885 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
886
887 -- ********* ERROR Return Here ********
888 Raise fnd_api.g_exc_error ;
889 END IF ;
890 END IF ;
891 reset_message_flag ;
892 x_award_id := G_award_rec.award_id ;
893
894
895 G_stage := 'Award Created Successfully' ;
896 EXCEPTION
897 WHEN E_VER_MISMATCH THEN
898 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
899 p_token1 => 'SUPVER',
900 P_VAL1 => g_api_version_number) ;
901 set_return_status(X_return_status, 'B' ) ;
902 x_msg_count := G_msg_count ;
903 x_msg_data := G_msg_data ;
904
905 WHEN FND_API.G_EXC_ERROR THEN
906 ROLLBACK TO save_award_pvt ;
907 set_return_status(X_return_status, 'B' ) ;
908 x_msg_count := G_msg_count ;
909 x_msg_data := G_msg_data ;
910 WHEN OTHERS THEN
911 ROLLBACK TO save_award_pvt;
912 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913
914 FND_MSG_PUB.add_exc_msg
915 ( p_pkg_name => G_PKG_NAME
916 , p_procedure_name => l_api_name );
917 FND_MSG_PUB.Count_And_Get
918 ( p_count => x_msg_count ,
919 p_data => x_msg_data );
920
921 END create_award ;
922
923 -- ===================End Of Create award Here ======================
924 --
925 -- Quick Entry Column Validations.
926 -- Award form has quick entry columns, These columns must have values.
927 -- We check here quick entry columns to make sure these columns has
928 -- data values.
929 --
930 PROCEDURE validate_award_quick_entry( X_return_status IN OUT NOCOPY varchar2 ) is
931
932 l_error BOOLEAN := FALSE ;
933 x_dummy NUMBER ;
934 l_sch_type varchar2(1) ;
935
936 -- +++++++ LOV Validations +++++++
937 -- Funding Source LOV validations.
938 -- TCA Changes : Replacing ra_customers with HZ tables
939 CURSOR c_fund_src is
940 SELECT cust_account_id
941 FROM hz_cust_accounts
942 WHERE status = 'A'
943 AND cust_account_id = g_award_rec.funding_source_id ;
944
945 -- Lookup LOV validations
946 --
947 CURSOR c_lookups( p_lookup_type varchar2, p_code varchar2) is
948 SELECT 1
949 FROM gms_lookups
950 WHERE lookup_type = p_lookup_type
951 and lookup_code = p_code ;
952
953 -- Allowability Schedule LOV Validation
954 CURSOR C_allowable_sch is
955 SELECT 1
956 FROM gms_allowability_schedules
957 WHERE allowability_schedule_id = g_award_rec.allowable_schedule_id ;
958
959 CURSOR C_idc_cost_sch is
960 SELECT ind_rate_schedule_type
961 FROM pa_ind_rate_schedules
962 WHERE ind_rate_sch_id = g_award_rec.idc_schedule_id
963 and trunc(sysdate) between start_date_active and NVL(end_date_active, (SYSDATE+1)) ;
964
965 cursor C_billing_term is
966 SELECT 1
967 FROM ra_terms
968 WHERE term_id = g_award_rec.billing_term ;
969
970
971 cursor C_invoice_format(x_formatID NUMBER,x_format varchar2 ) is
972 SELECT 1
973 FROM pa_invoice_groups inv_grp,
974 pa_invoice_formats inv_fmt
975 WHERE inv_grp.invoice_group_id = inv_fmt.invoice_group_id
976 and inv_fmt.invoice_format_id = x_formatID
977 and inv_grp.invoice_format_type = x_format ;
978
979 cursor C_awd_org is
980 SELECT 1
981 FROM pa_organizations_lov_v
982 WHERE code = g_award_rec.award_organization_id ;
983
984 cursor C_billing_cycle is
985 SELECT 1
986 FROM pa_billing_cycles
987 WHERE trunc(sysdate) between start_date_active and NVL( end_date_active, sysdate )
988 and billing_cycle_id = g_award_rec.billing_cycle_id ;
989
990 BEGIN
991
992 -- =====
993 -- Validate award_template_flag
994 -- =====
995 IF g_award_rec.award_template_flag not in ('DEFERRED', 'IMMEDIATE' ) then
996
997 -- =============
998 -- MSG: AWARD_TERMPLATE_INVALID
999 -- --------------------------------------
1000 add_message_to_stack( P_label => 'GMS_AWD_TEMPLATE_INVALID' ) ;
1001 l_error := TRUE ;
1002
1003 -- Serious error ...
1004 END IF ;
1005
1006 --
1007 -- Validate Funding source columns.
1008 --
1009
1010 IF g_award_rec.funding_source_id is not NULL then
1011 open C_FUND_SRC ;
1012 fetch C_FUND_SRC into x_dummy ;
1013
1014 IF C_FUND_SRC%NOTFOUND THEN
1015 add_message_to_stack( P_label => 'GMS_FUND_SOURC_INVALID' ) ;
1016 l_error := TRUE ;
1017 END IF ;
1018
1019 CLOSE C_FUND_SRC ;
1020 END IF ;
1021
1022
1023 --
1024 -- Validate award purpose code.
1025 --
1026
1027 IF g_award_rec.award_purpose_code is not NULL then
1028 open C_lookups( 'AWARD_PURPOSE_CODE', g_award_rec.award_purpose_code ) ;
1029 fetch C_lookups into x_dummy ;
1030
1031 IF C_lookups%NOTFOUND THEN
1032 add_message_to_stack( P_label => 'GMS_AWD_PURPOSE_CD_INVALID' ) ;
1033 l_error := TRUE ;
1034 END IF ;
1035
1036 CLOSE C_lookups ;
1037 END IF ;
1038
1039 --
1040 -- validate award status
1041 --
1042
1043 IF g_award_rec.status is not NULL then
1044 open C_lookups( 'AWARD_STATUS', g_award_rec.status ) ;
1045 fetch C_lookups into x_dummy ;
1046
1047 IF C_lookups%NOTFOUND THEN
1048 add_message_to_stack( P_label => 'GMS_AWD_STATUS_INVALID' ) ;
1049 l_error := TRUE ;
1050 END IF ;
1051
1052 CLOSE C_lookups ;
1053 END IF ;
1054
1055 -- ------------------------------------------------
1056 -- Amount type boundary code validations.
1057 -- ------------------------------------------------
1058 IF g_award_rec.amount_type is not NULL then
1059
1060 open C_lookups( 'AMOUNT_TYPE', g_award_rec.amount_type ) ;
1061 fetch C_lookups into x_dummy ;
1062
1063 IF C_lookups%NOTFOUND THEN
1064 add_message_to_stack( P_label => 'GMS_AWD_AMOUNT_TYPE_INVALID' ) ;
1065 l_error := TRUE ;
1066 END IF ;
1067
1068 CLOSE C_lookups ;
1069
1070 IF g_award_rec.amount_type = 'YTD' THEN
1071 IF NVL(g_award_rec.boundary_code,'P') not in ( 'P','Y' ) THEN
1072 add_message_to_stack( P_label => 'GMS_BOUNDARY_CD_YTD_INVALID' ) ;
1073 l_error := TRUE ;
1074 END IF ;
1075 ELSIF g_award_rec.amount_type = 'PTD' THEN
1076 IF NVL(g_award_rec.boundary_code,'P') <> 'P' THEN
1077 add_message_to_stack( P_label => 'GMS_BOUNDARY_CD_PTD_INVALID' ) ;
1078 l_error := TRUE ;
1079 END IF ;
1080 END IF ;
1081
1082 END IF ;
1083
1084 --
1085 -- validate allowable expenditures
1086 --
1087 IF g_award_rec.allowable_schedule_id is not NULL then
1088 open C_allowable_sch ;
1089 fetch C_allowable_sch into x_dummy ;
1090 IF C_allowable_sch%NOTFOUND THEN
1091 add_message_to_stack( P_label => 'GMS_AWD_ALLOWABLE_SCH_INVALID' ) ;
1092 l_error := TRUE ;
1093 END IF ;
1094 CLOSE C_allowable_sch ;
1095 END IF ;
1096
1097 --
1098 -- validate indirect cost schedule.
1099 --
1100
1101 IF g_award_rec.idc_schedule_id is not NULL then
1102 open C_idc_cost_sch ;
1103 fetch C_idc_cost_sch into l_sch_type ;
1104 IF C_idc_cost_sch%NOTFOUND THEN
1105 add_message_to_stack( P_label => 'GMS_IDC_SCH_INVALID' ) ;
1106 l_error := TRUE ;
1107 END IF ;
1108 CLOSE C_idc_cost_sch ;
1109 END IF ;
1110
1111 -- The provisional Schedule should have IDC_fixed_date NULL.
1112 IF NVL(l_sch_type,'F') = 'P' THEN
1113 IF g_award_rec.cost_ind_sch_fixed_date is not NULL THEN
1114 add_message_to_stack( P_label => 'GMS_IDC_FIXED_DATE_NOT_NULL' ) ;
1115 l_error := TRUE ;
1116 END IF ;
1117 END IF ;
1118
1119 /* Removed validation as requested by Ashish
1120 -- The firm Schedule should have IDC_fixed_date.
1121 IF NVL(l_sch_type,'F') = 'F' THEN
1122 IF g_award_rec.cost_ind_sch_fixed_date is NULL THEN
1123 add_message_to_stack( P_label => 'GMS_IDC_FIXED_DATE_NULL' ) ;
1124 l_error := TRUE ;
1125 END IF ;
1126 END IF ;
1127 */
1128 --
1129 -- Validate billing terms
1130 --
1131 IF g_award_rec.billing_term is not NULL then
1132 open C_billing_term ;
1133 fetch C_billing_term into x_dummy ;
1134 IF C_billing_term%NOTFOUND THEN
1135 add_message_to_stack( P_label => 'GMS_BILL_TERM_INVALID' ) ;
1136 l_error := TRUE ;
1137 END IF ;
1138 CLOSE C_billing_term ;
1139 END IF ;
1140
1141 --
1142 -- validate labor invoice formats
1143 --
1144 IF g_award_rec.labor_invoice_format_id is not NULL then
1145 open C_invoice_format(g_award_rec.labor_invoice_format_id, 'LABOR') ;
1146 fetch C_invoice_format into x_dummy ;
1147 IF C_invoice_format%NOTFOUND THEN
1148 add_message_to_stack( P_label => 'GMS_LABOR_INV_FMT_INVALID' ) ;
1149 l_error := TRUE ;
1150 END IF ;
1151 CLOSE C_invoice_format ;
1152 END IF ;
1153
1154 --
1155 -- validate non labor invoice formats.
1156 --
1157 IF g_award_rec.non_labor_invoice_format_id is not NULL then
1158 open C_invoice_format(g_award_rec.non_labor_invoice_format_id, 'NON-LABOR') ;
1159 fetch C_invoice_format into x_dummy ;
1160 IF C_invoice_format%NOTFOUND THEN
1161 add_message_to_stack( P_label => 'GMS_NONLABOR_INV_FMT_INVALID' ) ;
1162 l_error := TRUE ;
1163 END IF ;
1164 CLOSE C_invoice_format ;
1165 END IF ;
1166
1167 --
1168 -- validate award organization
1169 --
1170
1171 IF g_award_rec.award_organization_id is not NULL then
1172 open C_awd_org ;
1173 fetch C_awd_org into x_dummy ;
1174 IF C_awd_org%NOTFOUND THEN
1175 add_message_to_stack( P_label => 'GMS_AWD_ORG_INVALID' ) ;
1176 l_error := TRUE ;
1177 END IF ;
1178 CLOSE C_awd_org ;
1179 END IF ;
1180
1181 --
1182 -- validate billing cycle.
1183 --
1184
1185 IF g_award_rec.billing_cycle_id is not NULL then
1186 open C_billing_cycle ;
1187 fetch C_billing_cycle into x_dummy ;
1188
1189 IF C_billing_cycle%NOTFOUND THEN
1190 add_message_to_stack( P_label => 'GMS_BILL_CYCLE_INVALID' ) ;
1191 l_error := TRUE ;
1192 END IF ;
1193
1194 CLOSE C_billing_cycle ;
1195
1196 END IF ;
1197
1198 -- ===============================
1199 -- Validate FlexFields
1200 -- ===============================
1201 IF g_award_rec.attribute_category is not NULL THEN
1202
1203 fnd_flex_descval.set_context_value(g_award_rec.attribute_category) ;
1204
1205 fnd_flex_descval.set_column_value('ATTRIBUTE1',g_award_rec.attribute1) ;
1206 fnd_flex_descval.set_column_value('ATTRIBUTE2',g_award_rec.attribute2) ;
1207 fnd_flex_descval.set_column_value('ATTRIBUTE3',g_award_rec.attribute3) ;
1208 fnd_flex_descval.set_column_value('ATTRIBUTE4',g_award_rec.attribute4) ;
1209 fnd_flex_descval.set_column_value('ATTRIBUTE5',g_award_rec.attribute5) ;
1210 fnd_flex_descval.set_column_value('ATTRIBUTE6',g_award_rec.attribute6) ;
1211 fnd_flex_descval.set_column_value('ATTRIBUTE7',g_award_rec.attribute7) ;
1212 fnd_flex_descval.set_column_value('ATTRIBUTE8',g_award_rec.attribute8) ;
1213 fnd_flex_descval.set_column_value('ATTRIBUTE9',g_award_rec.attribute9) ;
1214 fnd_flex_descval.set_column_value('ATTRIBUTE10',g_award_rec.attribute10) ;
1215 fnd_flex_descval.set_column_value('ATTRIBUTE11',g_award_rec.attribute11) ;
1216 fnd_flex_descval.set_column_value('ATTRIBUTE12',g_award_rec.attribute12) ;
1217 fnd_flex_descval.set_column_value('ATTRIBUTE13',g_award_rec.attribute13) ;
1218 fnd_flex_descval.set_column_value('ATTRIBUTE14',g_award_rec.attribute14) ;
1219 fnd_flex_descval.set_column_value('ATTRIBUTE15',g_award_rec.attribute15) ;
1220 fnd_flex_descval.set_column_value('ATTRIBUTE16',g_award_rec.attribute16) ;
1221 fnd_flex_descval.set_column_value('ATTRIBUTE17',g_award_rec.attribute17) ;
1222 fnd_flex_descval.set_column_value('ATTRIBUTE18',g_award_rec.attribute18) ;
1223 fnd_flex_descval.set_column_value('ATTRIBUTE19',g_award_rec.attribute19) ;
1224 fnd_flex_descval.set_column_value('ATTRIBUTE20',g_award_rec.attribute20) ;
1225 fnd_flex_descval.set_column_value('ATTRIBUTE21',g_award_rec.attribute21) ;
1226 fnd_flex_descval.set_column_value('ATTRIBUTE22',g_award_rec.attribute22) ;
1227 fnd_flex_descval.set_column_value('ATTRIBUTE23',g_award_rec.attribute23) ;
1228 fnd_flex_descval.set_column_value('ATTRIBUTE24',g_award_rec.attribute24) ;
1229 fnd_flex_descval.set_column_value('ATTRIBUTE25',g_award_rec.attribute25) ;
1230
1231 IF (FND_FLEX_DESCVAL.validate_desccols ('GMS' ,'GMS_AWARDS_DESC_FLEX')) then
1232 -- Validation Passed
1233 NULL ;
1234 ELSE
1235 add_message_to_stack( P_label => 'GMS_AWD_FLEX_INVALID' ) ;
1236 fnd_msg_pub.add_exc_msg(p_pkg_name => 'GMS_AWARD_PVT',
1237 p_procedure_name => 'CREATE_AWARD',
1238 p_error_text => substr(FND_FLEX_DESCVAL.error_message,1,240)) ;
1239 l_error := TRUE ;
1240
1241 END IF ;
1242
1243 END IF ;
1244
1245 -- ------------------
1246 -- End of flex fields validations.
1247 -- ------------------
1248
1249
1250 IF l_error THEN
1251 set_return_status(X_return_status,'B') ;
1252 END IF ;
1253
1254 -- +++++++++++ billing dist rule validation ++++++++++
1255
1256 END validate_award_quick_entry ;
1257
1258 -- ======= End Of validate_award_quick_entry ======
1259
1260 --
1261 -- award automatic numbering generates award number automatic.
1262 -- following function returns the valid award number.
1263 --
1264
1265 FUNCTION func_get_award_num return number IS
1266 l_dummy NUMBER ;
1267 begin
1268
1269 SELECT nvl(next_unique_identifier,0)
1270 into l_dummy
1271 FROM gms_unique_identifier_control
1272 WHERE table_name = 'GMS_AWARDS'
1273 FOR update of next_unique_identifier;
1274
1275 UPDATE gms_unique_identifier_control
1276 SET next_unique_identifier = l_dummy + 1,
1277 last_update_date = trunc(sysdate) ,
1278 last_updated_by = 0
1279 WHERE table_name = 'GMS_AWARDS';
1280
1281 return l_dummy ;
1282
1283 END func_get_award_num ;
1284
1285 -- ==== End Of func_get_award_num ====
1286
1287
1288 -- +++++++++++++++++
1289 -- Validate Generic award validations. Here we validate
1290 -- Dates agains PA and GL periods
1291 -- Multiple columns related validations.
1292 -- Some Lov Validations
1293 -- Unique Coolumn Value validations.
1294 --
1295 PROCEDURE validate_award( X_return_status IN out NOCOPY varchar2) IS
1296
1297 x_dummy number := 0;
1298 x_agreement_type pa_agreement_types.agreement_type%TYPE ;
1299 x_awd_snm gms_awards_all.award_short_name%TYPE;
1300 L_error BOOLEAN := FALSE ;
1301
1302 Project_Check NUMBER(2) := 0;
1303
1304 CURSOR C1 is
1305 select decode(NAME, g_award_rec.award_number,1,2)
1306 from PA_PROJECTS
1307 where ( NAME = g_award_rec.award_number
1308 OR SEGMENT1 = g_award_rec.award_number) ;
1309
1310 cursor C_AWDNUM_DUP is
1311 select award_id
1312 from gms_awards_all
1313 where award_number = g_award_rec.award_number ;
1314
1315 cursor C_AWDSNM_DUP is
1316 select award_id
1317 from gms_awards_all
1318 where award_SHORT_NAME = g_award_rec.award_short_name ;
1319
1320 /* Bug 2534936
1321
1322 cursor C_awd_manager is
1323 SELECT 1, gmsp.start_date_active, end_date_active
1324 FROM per_assignments_f pera,
1325 gms_personnel gmsp
1326 WHERE pera.person_id = g_award_rec.award_manager_id
1327 and trunc(sysdate) between pera.effective_start_date
1328 and pera.effective_end_date
1329 and pera.primary_flag = 'Y'
1330 and pera.person_id = gmsp.person_id
1331 and gmsp.award_role = 'AM'
1332 and SYSDATE BETWEEN NVL (Start_Date_Active, SYSDATE-1)
1333 AND NVL (End_Date_Active, SYSDATE+1)
1334 and pera.assignment_type = 'E' ;
1335
1336 */
1337
1338 CURSOR c_awd_manager IS
1339 SELECT 1
1340 FROM pa_implementations i,
1341 pa_employees e,
1342 per_assignments_f a
1343 WHERE e.business_group_id = i.business_group_id
1344 AND e.person_id = g_award_rec.award_manager_id
1345 AND a.person_id = e.person_id
1346 AND trunc(sysdate) BETWEEN a.effective_start_date AND a.effective_end_date
1347 AND a.primary_flag = 'Y'
1348 -- AND a.assignment_type = 'E'; -- Bug 10124847
1349 AND a.assignment_type in ('C','E');
1350
1351 cursor c_pa_period(x_date date) is
1352 select count(*) from pa_periods pap
1353 where x_date between pap.start_date and pap.end_date ;
1354
1355 cursor c_gl_period(x_date date) is
1356 select 1
1357 from gl_period_statuses gps
1358 where x_date between gps.start_date
1359 and gps.end_date
1360 and gps.application_id = 101
1361 and gps.set_of_books_id = (select set_of_books_id
1362 from pa_implementations);
1363
1364 /* Bug# 3985020 : Commented
1365 select count(*) from gl_period_statuses gps
1366 where x_date between gps.start_date and gps.end_date
1367 and gps.application_id = 101
1368 and gps.set_of_books_id in (select set_of_books_id from pa_implementations_all);
1369 */
1370
1371 cursor C_agreement is
1372 SELECT agreement_type
1373 FROM pa_agreement_types
1374 WHERE agreement_type = g_award_rec.type ;
1375
1376 -- Debashis. Added exists clause. Removed rownum.
1377 CURSOR C_fnd_user is
1378 select 1 from dual where exists (
1379 select user_id
1380 from fnd_user
1381 where employee_id = g_award_rec.award_manager_id);
1382 -- and rownum = 1 ;
1383 BEGIN
1384 -- +++ Check Automatic Number validations +++
1385 IF G_gmsimpl_rec.user_defined_award_num_code = 'A'
1386 and g_award_rec.award_number is not NULL THEN
1387
1388 -- ERROR Automatic Numbering issue.
1389 -- Question Do we need to override
1390 -- This or raise an error.
1391 add_message_to_stack( P_label => 'GMS_AWD_NUMBER_NOT_NULL' ) ;
1392 l_error := TRUE ;
1393
1394 ELSIF G_gmsimpl_rec.user_defined_award_num_code = 'A'
1395 AND g_award_rec.award_number is NULL THEN
1396
1397 g_award_rec.award_number := func_get_award_num ;
1398 END IF ;
1399
1400 IF g_award_rec.award_number is NULL THEN
1401
1402 -- ------------------------------
1403 -- MSG: AWARD_NUMBER_NULL
1404 -- ------------------------------
1405 l_error := TRUE ;
1406 add_message_to_stack( P_label => 'GMS_AWD_NUMBER_NULL' ) ;
1407 END IF ;
1408
1409 BEGIN
1410 IF g_award_rec.award_number is not NULL THEN
1411
1412 IF G_gmsimpl_rec.user_defined_award_num_code = 'M'
1413 and G_gmsimpl_rec.manual_award_num_type = 'NUMERIC'
1414 THEN
1415 g_award_rec.award_number := to_number(g_award_rec.award_number) ;
1416 END IF ;
1417
1418 END IF ;
1419 EXCEPTION
1420 when VALUE_ERROR THEN
1421 -- ERROR ( Award Number should be NUMERIC.
1422 add_message_to_stack( P_label => 'GMS_AWD_NUMBER_NOT_NUMERIC' ) ;
1423 l_error := TRUE ;
1424 END ;
1425
1426
1427 -- ========================
1428 -- Award number is invalid. So we can not
1429 -- continue award number further checks.
1430 -- ========================
1431 IF not l_error then
1432 open C1 ;
1433 fetch C1 into project_check ;
1434 close C1 ;
1435
1436 If Project_Check = 1 then
1437 add_message_to_stack( P_label => 'GMS_AWD_PRJNAME_EXISTS' ) ;
1438 l_error := TRUE ;
1439 ELSIF Project_Check = 2 THEN
1440
1441 add_message_to_stack( P_label => 'GMS_AWD_PRJNUM_EXISTS' ) ;
1442 l_error := TRUE ;
1443 END IF ;
1444 END IF ;
1445
1446 -- +++++++++++ award Number Validations ++++++++++
1447 open C_AWDNUM_DUP ;
1448 fetch C_AWDNUM_DUP into x_dummy ;
1449 close C_AWDNUM_DUP ;
1450
1451 IF x_dummy > 0 THEN
1452 -- ERROR (GMS_AWD_NUMBER_DUP) ;
1453 -- MSG : Duplicate Award Number
1454 -- ----------------------------
1455 add_message_to_stack( P_label => 'GMS_AWD_NUMBER_DUP' ) ;
1456 l_error := TRUE ;
1457 END IF ;
1458 x_dummy := 0 ;
1459
1460 --open C_AWDSNM_DUP ;
1461 --fetch C_AWDSNM_DUP into x_AWD_SNM ;
1462 --IF C_AWDSNM_DUP%FOUND THEN
1463 -- ERROR (GMS_AWD_NUMBER_DUP) ;
1464 -- MSG : Duplicate Award Number
1465 -- ----------------------------
1466 -- add_message_to_stack( P_label => 'GMS_AWD_SHORT_NAME_DUP' ) ;
1467 -- l_error := TRUE ;
1468 --END IF ;
1469 --close C_AWDSNM_DUP ;
1470
1471
1472 -- ++++++++++ Validate LOV data ++++++++++++++++
1473 IF g_award_rec.award_manager_id is not NULL then
1474 open C_awd_manager ;
1475 fetch C_awd_manager into x_dummy; -- Bug 2534936
1476
1477 IF C_awd_manager%NOTFOUND THEN
1478 add_message_to_stack( P_label => 'GMS_AWD_MANAGER_INVALID' ) ;
1479 l_error := TRUE ;
1480 END IF ;
1481 CLOSE C_awd_manager ;
1482 END IF ;
1483
1484 -- ==================
1485 -- Award manager must be FND_USER for budget_wf_enabled_flag
1486 -- ================
1487
1488 IF G_award_rec.budget_wf_enabled_flag = 'Y' THEN
1489 open c_fnd_user ;
1490 fetch c_fnd_user into x_dummy ;
1491 IF c_fnd_user%NOTFOUND then
1492 add_message_to_stack( P_label => 'GMS_AWD_MGR_NOT_FND_USER' ) ;
1493 l_error := TRUE ;
1494 end if ;
1495 close c_fnd_user ;
1496 END IF ;
1497
1498 IF G_award_rec.budget_wf_enabled_flag NOT IN ( 'Y', 'N' ) THEN
1499 add_message_to_stack( P_label => 'GMS_AWD_WORKFLOW_FLAG_INVALID') ;
1500 l_error := TRUE ;
1501 END IF ;
1502
1503
1504 IF g_award_rec.type is not NULL then
1505 open C_agreement ;
1506 fetch C_agreement into x_agreement_type ;
1507 IF C_agreement%NOTFOUND THEN
1508 add_message_to_stack( P_label => 'GMS_AGREEMENT_TYPE_INVALID' ) ;
1509 l_error := TRUE ;
1510 END IF ;
1511 CLOSE C_agreement ;
1512 END IF ;
1513
1514
1515 IF g_award_rec.billing_format = 'LOC' and
1516 g_award_rec.bill_to_customer_id is NULL
1517 THEN
1518
1519 -- ERROR 'Bill to customer ID is missing.
1520 add_message_to_stack( P_label => 'GMS_NO_BILL_TO_CUST_LOC' ) ;
1521 l_error := TRUE ;
1522 END IF ;
1523
1524 -- =======
1525 -- Billing Format Validations
1526 -- =======
1527 IF NVL(g_award_rec.billing_format,'LOC') NOT IN
1528 ( 'NO_PRINT', 'PRINT_INVOICE', 'LOC', 'AGENCY','EDI' ) THEN
1529 add_message_to_stack( P_label => 'GMS_BILLING_FORMAT_INVALID' ) ;
1530 l_error := TRUE ;
1531 END IF ;
1532
1533 IF NVL(g_award_rec.billing_format,'EDI') <> 'EDI'
1534 and g_award_rec.transaction_number is not NULL THEN
1535 add_message_to_stack( P_label => 'GMS_TRANS_NUMBER_NULL' ) ;
1536 l_error := TRUE ;
1537 END IF ;
1538
1539 IF NVL(g_award_rec.billing_format,'LOC') <> 'LOC'
1540 and g_award_rec.bill_to_customer_id is not NULL THEN
1541 add_message_to_stack( P_label => 'GMS_BILL_TO_CUSTLOC_NULL' ) ;
1542 l_error := TRUE ;
1543 END IF ;
1544
1545 IF NVL(g_award_rec.billing_format,'AGENCY') <> 'AGENCY'
1546 and g_award_rec.agency_specific_form is not NULL THEN
1547 add_message_to_stack( P_label => 'GMS_AGENCY_FORM_NULL' ) ;
1548 l_error := TRUE ;
1549 END IF ;
1550
1551 -- ++++++++++ Check PA periods ++++++++++++++++++
1552 open C_pa_period(g_award_rec.start_date_active) ;
1553 fetch C_pa_period into x_dummy ;
1554 close c_pa_period ;
1555
1556 IF x_dummy = 0 THEN
1557 -- ERROR GMS_AWD_DATE_EXZ_PA_DATE
1558 add_message_to_stack( P_label => 'GMS_AWD_START_DATE_NOT_PAPRD' ) ;
1559 l_error := TRUE ;
1560 END IF ;
1561 x_dummy := 0 ;
1562
1563 open C_pa_period(g_award_rec.end_date_active) ;
1564 fetch C_pa_period into x_dummy ;
1565 close c_pa_period ;
1566 IF x_dummy = 0 THEN
1567 add_message_to_stack( P_label => 'GMS_AWD_END_DATE_NOT_PAPRD' ) ;
1568 l_error := TRUE ;
1569 END IF ;
1570
1571 x_dummy := 0 ;
1572 /* commented the close_date validation against open pa periods for bug 7603285*/
1573 /*open C_pa_period(g_award_rec.close_date) ;
1574 fetch C_pa_period into x_dummy ;
1575 close c_pa_period ;
1576 IF x_dummy = 0 THEN
1577 add_message_to_stack( P_label => 'GMS_AWD_CLOSE_DATE_NOT_PAPRD' ) ;
1578 l_error := TRUE ;
1579 END IF ;
1580
1581 x_dummy := 0 ; */
1582
1583
1584 IF g_award_rec.preaward_date is not NULL THEN
1585
1586 open C_pa_period(g_award_rec.preaward_date) ;
1587 fetch C_pa_period into x_dummy ;
1588 close c_pa_period ;
1589 IF x_dummy = 0 THEN
1590 --ERROR GMS_AWD_DATE_EXZ_PA_DATE
1591 add_message_to_stack( P_label => 'GMS_AWD_PRE_DATE_NOT_PAPRD' ) ;
1592 l_error := TRUE ;
1593 END IF ;
1594
1595 open c_gl_period(g_award_rec.preaward_date) ;
1596 fetch c_gl_period into x_dummy ;
1597 close c_gl_period ;
1598
1599 --Bug# 3985020 : Added NVL as aggregate function count was removed.
1600 IF nvl(x_dummy,0) = 0 THEN
1601 --ERROR GMS_AWD_DATE_EXZ_GL_DAT
1602 add_message_to_stack( P_label => 'GMS_AWD_PRE_DATE_NOT_GL' ) ;
1603 l_error := TRUE ;
1604 END IF ;
1605 x_dummy := 0 ;
1606 END IF ;
1607
1608 -- ++++++++++ Check GL periods ++++++++++++++++++
1609
1610 open c_gl_period(g_award_rec.start_date_active) ;
1611 fetch c_gl_period into x_dummy ;
1612 close c_gl_period ;
1613 --Bug# 3985020 : Added NVL as aggregate function count was removed.
1614 IF nvl(x_dummy,0) = 0 THEN
1615 --ERROR GMS_AWD_DATE_EXZ_GL_DAT
1616 add_message_to_stack( P_label => 'GMS_AWD_START_DATE_NOT_GL' ) ;
1617 l_error := TRUE ;
1618 END IF ;
1619 x_dummy := 0 ;
1620
1621 open C_gl_period(g_award_rec.end_date_active) ;
1622 fetch C_gl_period into x_dummy ;
1623 close c_gl_period ;
1624 --Bug# 3985020 : Added NVL as aggregate function count was removed.
1625 IF nvl(x_dummy,0) = 0 THEN
1626 --ERROR GMS_AWD_DATE_EXZ_GL_DAT
1627 add_message_to_stack( P_label => 'GMS_AWD_END_DATE_NOT_GL' ) ;
1628 l_error := TRUE ;
1629 END IF ;
1630 x_dummy := 0 ;
1631
1632 /* commented the close_date validation against open GL periods for bug 7603285*/
1633 /*open c_gl_period(g_award_rec.close_date) ;
1634 fetch c_gl_period into x_dummy ;
1635 close c_gl_period ;
1636 --Bug# 3985020 : Added NVL as aggregate function count was removed.
1637 IF nvl(x_dummy,0) = 0 THEN
1638 --ERROR GMS_AWD_DATE_EXZ_GL_DAT
1639 add_message_to_stack( P_label => 'GMS_AWD_CLOSE_DATE_NOT_GL' ) ;
1640 l_error := TRUE ;
1641 END IF ;
1642 x_dummy := 0 ;*/
1643
1644 IF NVL(g_award_rec.fund_control_level_award,'B') not in ('B','D','N' )
1645 THEN
1646 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_AWD_INVALID' ) ;
1647 l_error := TRUE ;
1648 END IF ;
1649
1650 IF NVL(g_award_rec.fund_control_level_task, 'B' ) not in ('B','D','N' )
1651 THEN
1652 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_TASK_INVALID' ) ;
1653 l_error := TRUE ;
1654 END IF ;
1655
1656 IF NVL(g_award_rec.fund_control_level_res_grp, 'B' ) not in ('B','D','N' )
1657 THEN
1658 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_RGP_INVALID' ) ;
1659 l_error := TRUE ;
1660 END IF ;
1661
1662 IF NVL(g_award_rec.fund_control_level_res, 'B' ) not in ('B','D','N' )
1663 THEN
1664 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_RES_INVALID' ) ;
1665 l_error := TRUE ;
1666 END IF ;
1667
1668 IF g_award_rec.billing_distribution_rule not in ('COST', 'EVENT' ) THEN
1669 add_message_to_stack( P_label => 'GMS_BILL_DIST_RULE_INVALID' ) ;
1670 l_error := TRUE ;
1671 END IF ;
1672
1673 IF g_award_rec.revenue_distribution_rule not in ('COST', 'EVENT' ) THEN
1674 add_message_to_stack( P_label => 'GMS_REV_DIST_RULE_INVALID' ) ;
1675 l_error := TRUE ;
1676 END IF ;
1677
1678 IF g_award_rec.billing_distribution_rule = 'COST'
1679 and g_award_rec.revenue_distribution_rule = 'EVENT' THEN
1680
1681 -- ERROR ( GMS_DISTRIBUTION_RULE_CONFLICT )
1682 add_message_to_stack( P_label => 'GMS_DISTRIBUTION_RULE_CONFLICT' ) ;
1683 l_error := TRUE ;
1684 END IF ;
1685
1686 -- +++++++++++ Date Validations +++++++++++++++
1687
1688 IF g_award_rec.preaward_date is not NULL
1689 and g_award_rec.start_date_active is not NULL
1690 and g_award_rec.start_date_active < g_award_rec.preaward_date THEN
1691
1692 -- ERROR ( GMS_START_DATE_AFTER_PREAWARD )
1693 add_message_to_stack( P_label => 'GMS_START_DATE_AFTER_PREAWARD' ) ;
1694 l_error := TRUE ;
1695 END IF ;
1696
1697 IF g_award_rec.start_date_active is not NULL
1698 and g_award_rec.end_date_active is not NULL
1699 and g_award_rec.start_date_active > g_award_rec.end_date_active THEN
1700
1701 -- ERROR ( GMS_END_DATE_BEFORE_START_DATE )
1702 add_message_to_stack( P_label => 'GMS_END_DATE_BEFORE_START_DATE' ) ;
1703 l_error := TRUE ;
1704 END IF ;
1705
1706
1707 IF g_award_rec.end_date_active is not NULL
1708 and g_award_rec.close_date is not NULL
1709 and g_award_rec.end_date_active > g_award_rec.close_date THEN
1710
1711 -- ERROR ( GMS_CLOSE_DATE_BEFORE_END_DATE )
1712 add_message_to_stack( P_label => 'GMS_CLOSE_DATE_BEFORE_END_DATE' ) ;
1713 l_error := TRUE ;
1714
1715 END IF ;
1716
1717 IF g_award_rec.billing_offset < 0 THEN
1718 add_message_to_stack( P_label => 'GMS_BILL_OFFSET_INVALID' ) ;
1719 l_error := TRUE ;
1720 END IF ;
1721 IF l_error THEN
1722 set_return_status(X_return_status,'B') ;
1723 END IF ;
1724
1725 END validate_award ;
1726
1727 -- ====================
1728 -- create_agreement
1729 -- Following procedure create an agreement for award creation in
1730 -- progress.
1731 --Shared Service Enhancement : Added ORG_ID in the pa_agreements_pkg.insert_row
1732 -- ====================
1733 PROCEDURE create_agreement(p_agreement_id OUT NOCOPY NUMBER ) is
1734
1735 L_row_id varchar2(30) ;
1736 L_agreement_id NUMBER ;
1737 BEGIN
1738
1739 PA_AGREEMENTS_PKG.INSERT_ROW(
1740 X_ROWID => L_Row_Id,
1741 X_AGREEMENT_ID => L_Agreement_Id,
1742 X_CUSTOMER_ID => g_award_rec.funding_source_id, --G_bill_contact_rec.customer_id, bug 3076921
1743 X_AGREEMENT_NUM => g_award_rec.award_number,
1744 X_AGREEMENT_TYPE => g_award_rec.type,
1745 X_LAST_UPDATE_DATE => sysdate,
1746 X_LAST_UPDATED_BY => g_award_rec.last_updated_by,
1747 X_CREATION_DATE => sysdate,
1748 X_CREATED_BY => g_award_rec.created_by,
1749 X_LAST_UPDATE_LOGIN => g_award_rec.last_update_login,
1750 X_OWNED_BY_PERSON_ID => g_award_rec.award_manager_id,
1751 X_TERM_ID => g_award_rec.billing_term,
1752 X_REVENUE_LIMIT_FLAG => nvl(g_award_rec.hard_limit_flag, 'N'), -- Bug 2464841 : Changed 'Y'to'N'
1753 X_AMOUNT => 0,
1754 X_DESCRIPTION => NULL,
1755 X_EXPIRATION_DATE => g_award_rec.close_date,
1756 X_ATTRIBUTE_CATEGORY => NULL,
1757 X_ATTRIBUTE1 => NULL,
1758 X_ATTRIBUTE2 => NULL,
1759 X_ATTRIBUTE3 => NULL,
1760 X_ATTRIBUTE4 => NULL,
1761 X_ATTRIBUTE5 => NULL,
1762 X_ATTRIBUTE6 => NULL,
1763 X_ATTRIBUTE7 => NULL,
1764 X_ATTRIBUTE8 => NULL,
1765 X_ATTRIBUTE9 => NULL,
1766 X_ATTRIBUTE10 => NULL,
1767 X_TEMPLATE_FLAG => NULL,
1768 X_PM_AGREEMENT_REFERENCE => NULL,
1769 X_PM_PRODUCT_CODE => NULL,
1770 -- Bug 2464841 : Added parameters for 11.5 PA-J certification.
1771 X_OWNING_ORGANIZATION_ID => NULL,
1772 X_AGREEMENT_CURRENCY_CODE => pa_currency.get_currency_code,
1773 X_INVOICE_LIMIT_FLAG => nvl(g_award_rec.invoice_limit_flag, 'N'), /*Bug 6642901*/
1774 X_ORG_ID => g_award_rec.org_id
1775 );
1776
1777 P_Agreement_Id := L_Agreement_Id;
1778 END create_agreement;
1779
1780 -- +++++++++++++++++
1781 PROCEDURE insert_award_record ( X_return_status in out NOCOPY varchar2) IS
1782
1783 l_row_id varchar2(50) ;
1784 L_agreement_id NUMBER ;
1785 BEGIN
1786 select gms_awards_s.NEXTVAL
1787 into g_award_rec.award_id
1788 from DUAL ;
1789
1790 create_agreement(L_agreement_id ) ;
1791
1792 g_award_rec.agreement_id := L_agreement_id ;
1793
1794 INSERT into gms_awards_all
1795 (
1796 AWARD_ID ,
1797 AWARD_NUMBER ,
1798 LAST_UPDATE_DATE ,
1799 LAST_UPDATED_BY ,
1800 CREATION_DATE ,
1801 CREATED_BY ,
1802 LAST_UPDATE_LOGIN ,
1803 AWARD_SHORT_NAME ,
1804 AWARD_FULL_NAME ,
1805 FUNDING_SOURCE_ID ,
1806 START_DATE_ACTIVE ,
1807 END_DATE_ACTIVE ,
1808 CLOSE_DATE ,
1809 FUNDING_SOURCE_AWARD_NUMBER ,
1810 AWARD_PURPOSE_CODE ,
1811 STATUS ,
1812 ALLOWABLE_SCHEDULE_ID ,
1813 IDC_SCHEDULE_ID ,
1814 REVENUE_DISTRIBUTION_RULE ,
1815 BILLING_FREQUENCY ,
1816 BILLING_DISTRIBUTION_RULE ,
1817 BILLING_FORMAT ,
1818 BILLING_TERM ,
1819 AWARD_PROJECT_ID ,
1820 AGREEMENT_ID ,
1821 AWARD_TEMPLATE_FLAG ,
1822 PREAWARD_DATE ,
1823 AWARD_MANAGER_ID ,
1824 REQUEST_ID ,
1825 PROGRAM_APPLICATION_ID ,
1826 PROGRAM_ID ,
1827 PROGRAM_UPDATE_DATE ,
1828 AGENCY_SPECIFIC_FORM ,
1829 BILL_TO_CUSTOMER_ID ,
1830 TRANSACTION_NUMBER ,
1831 AMOUNT_TYPE ,
1832 BOUNDARY_CODE ,
1833 FUND_CONTROL_LEVEL_AWARD ,
1834 FUND_CONTROL_LEVEL_TASK ,
1835 FUND_CONTROL_LEVEL_RES_GRP ,
1836 FUND_CONTROL_LEVEL_RES ,
1837 ATTRIBUTE_CATEGORY ,
1838 ATTRIBUTE1 ,
1839 ATTRIBUTE2 ,
1840 ATTRIBUTE3 ,
1841 ATTRIBUTE4 ,
1842 ATTRIBUTE5 ,
1843 ATTRIBUTE6 ,
1844 ATTRIBUTE7 ,
1845 ATTRIBUTE8 ,
1846 ATTRIBUTE9 ,
1847 ATTRIBUTE10 ,
1848 ATTRIBUTE11 ,
1849 ATTRIBUTE12 ,
1850 ATTRIBUTE13 ,
1851 ATTRIBUTE14 ,
1852 ATTRIBUTE15 ,
1853 ATTRIBUTE16 ,
1854 ATTRIBUTE17 ,
1855 ATTRIBUTE18 ,
1856 ATTRIBUTE19 ,
1857 ATTRIBUTE20 ,
1858 ATTRIBUTE21 ,
1859 ATTRIBUTE22 ,
1860 ATTRIBUTE23 ,
1861 ATTRIBUTE24 ,
1862 ATTRIBUTE25 ,
1863 TEMPLATE_START_DATE_ACTIVE ,
1864 TEMPLATE_END_DATE_ACTIVE ,
1865 TYPE ,
1866 ORG_ID ,
1867 COST_IND_SCH_FIXED_DATE ,
1868 LABOR_INVOICE_FORMAT_ID ,
1869 NON_LABOR_INVOICE_FORMAT_ID ,
1870 BILL_TO_ADDRESS_ID ,
1871 SHIP_TO_ADDRESS_ID ,
1872 LOC_BILL_TO_ADDRESS_ID ,
1873 LOC_SHIP_TO_ADDRESS_ID ,
1874 AWARD_ORGANIZATION_ID ,
1875 HARD_LIMIT_FLAG ,
1876 INVOICE_LIMIT_FLAG , /*Bug 6642901*/
1877 BILLING_OFFSET ,
1878 BILLING_CYCLE_ID ,
1879 PROPOSAL_ID ,
1880 BUDGET_WF_ENABLED_FLAG
1881 )
1882 values
1883 (
1884 g_award_rec.AWARD_ID ,
1885 g_award_rec.AWARD_NUMBER ,
1886 g_award_rec.LAST_UPDATE_DATE ,
1887 g_award_rec.LAST_UPDATED_BY ,
1888 g_award_rec.CREATION_DATE ,
1889 g_award_rec.CREATED_BY ,
1890 g_award_rec.LAST_UPDATE_LOGIN ,
1891 g_award_rec.AWARD_SHORT_NAME ,
1892 g_award_rec.AWARD_FULL_NAME ,
1893 g_award_rec.FUNDING_SOURCE_ID ,
1894 g_award_rec.START_DATE_ACTIVE ,
1895 g_award_rec.END_DATE_ACTIVE ,
1896 g_award_rec.CLOSE_DATE ,
1897 g_award_rec.FUNDING_SOURCE_AWARD_NUMBER ,
1898 g_award_rec.AWARD_PURPOSE_CODE ,
1899 g_award_rec.STATUS ,
1900 g_award_rec.ALLOWABLE_SCHEDULE_ID ,
1901 g_award_rec.IDC_SCHEDULE_ID ,
1902 g_award_rec.REVENUE_DISTRIBUTION_RULE ,
1903 g_award_rec.BILLING_FREQUENCY ,
1904 g_award_rec.BILLING_DISTRIBUTION_RULE ,
1905 g_award_rec.BILLING_FORMAT ,
1906 g_award_rec.BILLING_TERM ,
1907 g_award_rec.AWARD_PROJECT_ID ,
1908 g_award_rec.AGREEMENT_ID ,
1909 g_award_rec.AWARD_TEMPLATE_FLAG ,
1910 g_award_rec.PREAWARD_DATE ,
1911 g_award_rec.AWARD_MANAGER_ID ,
1912 g_award_rec.REQUEST_ID ,
1913 g_award_rec.PROGRAM_APPLICATION_ID ,
1914 g_award_rec.PROGRAM_ID ,
1915 g_award_rec.PROGRAM_UPDATE_DATE ,
1916 g_award_rec.AGENCY_SPECIFIC_FORM ,
1917 g_award_rec.BILL_TO_CUSTOMER_ID ,
1918 g_award_rec.TRANSACTION_NUMBER ,
1919 g_award_rec.AMOUNT_TYPE ,
1920 g_award_rec.BOUNDARY_CODE ,
1921 g_award_rec.FUND_CONTROL_LEVEL_AWARD ,
1922 g_award_rec.FUND_CONTROL_LEVEL_TASK ,
1923 g_award_rec.FUND_CONTROL_LEVEL_RES_GRP ,
1924 g_award_rec.FUND_CONTROL_LEVEL_RES ,
1925 g_award_rec.ATTRIBUTE_CATEGORY ,
1926 g_award_rec.ATTRIBUTE1 ,
1927 g_award_rec.ATTRIBUTE2 ,
1928 g_award_rec.ATTRIBUTE3 ,
1929 g_award_rec.ATTRIBUTE4 ,
1930 g_award_rec.ATTRIBUTE5 ,
1931 g_award_rec.ATTRIBUTE6 ,
1932 g_award_rec.ATTRIBUTE7 ,
1933 g_award_rec.ATTRIBUTE8 ,
1934 g_award_rec.ATTRIBUTE9 ,
1935 g_award_rec.ATTRIBUTE10 ,
1936 g_award_rec.ATTRIBUTE11 ,
1937 g_award_rec.ATTRIBUTE12 ,
1938 g_award_rec.ATTRIBUTE13 ,
1939 g_award_rec.ATTRIBUTE14 ,
1940 g_award_rec.ATTRIBUTE15 ,
1941 g_award_rec.ATTRIBUTE16 ,
1942 g_award_rec.ATTRIBUTE17 ,
1943 g_award_rec.ATTRIBUTE18 ,
1944 g_award_rec.ATTRIBUTE19 ,
1945 g_award_rec.ATTRIBUTE20 ,
1946 g_award_rec.ATTRIBUTE21 ,
1947 g_award_rec.ATTRIBUTE22 ,
1948 g_award_rec.ATTRIBUTE23 ,
1949 g_award_rec.ATTRIBUTE24 ,
1950 g_award_rec.ATTRIBUTE25 ,
1951 g_award_rec.TEMPLATE_START_DATE_ACTIVE ,
1952 g_award_rec.TEMPLATE_END_DATE_ACTIVE ,
1953 g_award_rec.TYPE ,
1954 g_award_rec.ORG_ID ,
1955 g_award_rec.COST_IND_SCH_FIXED_DATE ,
1956 g_award_rec.LABOR_INVOICE_FORMAT_ID ,
1957 g_award_rec.NON_LABOR_INVOICE_FORMAT_ID ,
1958 g_award_rec.BILL_TO_ADDRESS_ID ,
1959 g_award_rec.SHIP_TO_ADDRESS_ID ,
1960 g_award_rec.LOC_BILL_TO_ADDRESS_ID ,
1961 g_award_rec.LOC_SHIP_TO_ADDRESS_ID ,
1962 g_award_rec.AWARD_ORGANIZATION_ID ,
1963 g_award_rec.HARD_LIMIT_FLAG ,
1964 g_award_rec.INVOICE_LIMIT_FLAG , /*Bug 6642901*/
1965 g_award_rec.BILLING_OFFSET ,
1966 g_award_rec.BILLING_CYCLE_ID ,
1967 g_award_rec.proposal_id ,
1968 g_award_rec.BUDGET_WF_ENABLED_FLAG
1969 );
1970 END insert_award_record ;
1971 --**************************************************************
1972 -- Bug Fix for Bug 3076921
1973 -- The following procedure verifies the existence of a structure
1974 -- for the award project template. If it exists, the same project structure is
1975 -- used to copy to the newly created award project, If not the following procedure
1976 -- creates a structure for the award project template.
1977 -- The structure for the award project template is mandatory from PA.K onwards
1978 -- as project creates structure for every project template and uses the same
1979 -- to create a structure while creating a new project, which is copied from
1980 -- the template.
1981 --***************************************************************
1982 PROCEDURE CREATE_AWD_PROJ_TEMPLATE_STRUC(X_award_project_id IN Number,
1983 X_return_status OUT NOCOPY VARCHAR2 ) IS
1984
1985 l_struct_exists varchar2(1) := 'N';
1986 l_awd_proj_temp pa_projects_all%rowtype;
1987 l_msg_count NUMBER;
1988 l_msg_data VARCHAR2(2000);
1989 l_return_status VARCHAR2(1);
1990
1991
1992 CURSOR c_awd_proj_temp is
1993 Select * from pa_projects_all
1994 where project_id = x_award_project_id;
1995
1996 CURSOR c_struc_exists is
1997 Select 'Y' from pa_proj_elements
1998 where project_id = x_award_project_id;
1999
2000 Begin
2001 -- Verify whether a structure is already existing for the award project template.
2002
2003 Open c_struc_exists;
2004 Fetch c_struc_exists into l_struct_exists;
2005 Close c_struc_exists;
2006
2007 If l_struct_exists = 'N' then
2008
2009 -- Fetch the award project record.
2010 open c_awd_proj_temp;
2011 fetch c_awd_proj_temp into l_awd_proj_temp;
2012 close c_awd_proj_temp;
2013
2014 -- Create structure for the award project template.
2015
2016 PA_PROJ_TASK_STRUC_PUB.CREATE_DEFAULT_STRUCTURE(
2017 p_dest_project_id => x_award_project_id
2018 ,p_dest_project_name => l_awd_proj_temp.name
2019 ,p_dest_project_number => l_awd_proj_temp.segment1
2020 ,p_dest_description => l_awd_proj_temp.description
2021 ,p_struc_type => 'FINANCIAL' --creating only financial structure
2022 ,x_msg_count => l_msg_count
2023 ,x_msg_data => l_msg_data
2024 ,x_return_status => l_return_status );
2025
2026
2027 IF l_Return_Status <> 'S' then
2028 RAISE FND_API.G_EXC_ERROR;
2029 END IF;
2030
2031 -- Create Options for the award project template.
2032 insert into pa_project_options
2033 (project_id,
2034 option_code,
2035 last_update_date,
2036 last_updated_by,
2037 creation_date,
2038 created_by,
2039 last_update_login)
2040 select
2041 x_award_project_id,
2042 option_code,
2043 SYSDATE,
2044 fnd_global.user_id,
2045 SYSDATE ,
2046 fnd_global.user_id,
2047 fnd_global.login_id
2048 from pa_options
2049 where option_code not in ( 'STRUCTURES', 'STRUCTURES_SS' );
2050
2051 -- Create structure for the award project template's task.
2052
2053 PA_PROJ_TASK_STRUC_PUB.CREATE_DEFAULT_TASK_STRUCTURE(
2054 p_project_id => x_award_project_id
2055 ,p_struc_type => 'FINANCIAL'
2056 ,x_msg_count => l_msg_count
2057 ,x_msg_data => l_msg_data
2058 ,x_return_status => l_return_status );
2059
2060 end if;
2061
2062 EXCEPTION
2063 WHEN FND_API.G_EXC_ERROR then
2064 X_return_status := l_return_status;
2065
2066 WHEN OTHERS then
2067 X_return_status := l_return_status;
2068
2069 End CREATE_AWD_PROJ_TEMPLATE_STRUC;
2070
2071 --**************************************************************
2072 -- Bug FIx 3076921
2073 -- For the PA.K rollup patch certification we started making use of the customer account relationship feature.
2074 -- 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
2075 -- column of the pa_project_customers.
2076 -- We will not update teh record with the latest, by overriding the existing customer_id.
2077 -- For this the columns bill_to_customer_id and ship_to_customer_id need to be defined as overridable.
2078 -- This change can be done in the implementaitons form, but that forces us to come up with a data fix
2079 -- for the existing implementations. So adding that check before creating an award. Thus we dont need any
2080 -- data fix script and all the changes will be centralized in the multi funding package.
2081
2082 Procedure MARK_FIELDS_AS_OVERRIDABLE(x_award_project_id IN NUMBER,
2083 x_field_name IN VARCHAR2,
2084 x_return_status OUT NOCOPY VARCHAR2) IS
2085 CURSOR c_bill_to_customer_overridable IS
2086 SELECT project_id
2087 FROM pa_project_copy_overrides
2088 WHERE project_id = x_award_project_id
2089 AND field_name = x_field_name;
2090
2091 l_project_id NUMBER;
2092 l_msg_count NUMBER;
2093 l_msg_data VARCHAR2(2000);
2094 l_return_status VARCHAR2(1);
2095
2096 BEGIN
2097
2098 OPEN c_bill_to_customer_overridable;
2099 FETCH c_bill_to_customer_overridable INTO l_project_id;
2100 CLOSE c_bill_to_customer_overridable;
2101
2102 IF l_project_id IS NULL AND x_field_name = 'BILL_TO_CUSTOMER' THEN
2103 PA_PROJ_TEMPLATE_SETUP_PUB.ADD_QUICK_ENTRY_FIELD( p_api_version => 1.0,
2104 p_init_msg_list => FND_API.G_TRUE,
2105 p_commit => FND_API.G_FALSE,
2106 p_validate_only => FND_API.G_FALSE,
2107 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2108 p_calling_module => 'FORM',
2109 p_debug_mode => 'N',
2110 p_max_msg_count => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2111 p_project_id => x_award_project_id,
2112 p_sort_order => 70,
2113 p_field_name => 'BILL_TO_CUSTOMER',
2114 p_field_meaning => 'Bill To Customer Name',
2115 p_specification => 'Primary',
2116 p_limiting_value => 'Primary',
2117 p_prompt => 'Bill To Customer Name',
2118 p_required_flag => 'N',
2119 x_return_status => l_return_status,
2120 x_msg_count => l_msg_count,
2121 x_msg_data => l_msg_data);
2122
2123
2124 IF l_Return_Status < 'S' then
2125 RAISE FND_API.G_EXC_ERROR;
2126 END IF;
2127
2128 END IF;
2129
2130
2131 IF l_project_id IS NULL AND x_field_name = 'SHIP_TO_CUSTOMER' THEN
2132 PA_PROJ_TEMPLATE_SETUP_PUB.ADD_QUICK_ENTRY_FIELD( p_api_version => 1.0,
2133 p_init_msg_list => FND_API.G_TRUE,
2134 p_commit => FND_API.G_FALSE,
2135 p_validate_only => FND_API.G_FALSE,
2136 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2137 p_calling_module => 'FORM',
2138 p_debug_mode => 'N',
2139 p_max_msg_count => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2140 p_project_id => x_award_project_id,
2141 p_sort_order => 80,
2142 p_field_name => 'SHIP_TO_CUSTOMER',
2143 p_field_meaning => 'Ship To Customer Name',
2144 p_specification => 'Primary',
2145 p_limiting_value => 'Primary',
2146 p_prompt => 'Ship To Customer Name',
2147 p_required_flag => 'N',
2148 x_return_status => l_return_status,
2149 x_msg_count => l_msg_count,
2150 x_msg_data => l_msg_data);
2151
2152
2153 IF l_Return_Status < 'S' then
2154 RAISE FND_API.G_EXC_ERROR;
2155 END IF;
2156
2157 END IF;
2158 EXCEPTION
2159 WHEN FND_API.G_EXC_ERROR then
2160 X_Return_Status := l_return_status;
2161
2162 WHEN OTHERS then
2163 X_Return_Status := l_return_status;
2164 END MARK_FIELDS_AS_OVERRIDABLE;
2165 -- End of Bug Fix for Bug 3076921
2166
2167 -- +++++++++++++++++
2168 PROCEDURE create_award_project ( X_return_status IN out NOCOPY varchar2,
2169 p_setup_award_project_id IN NUMBER) is
2170
2171 L_Err_Code VARCHAR2(1) := NULL;
2172 L_Project_IN_REC PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
2173 L_Project_OUT_REC PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE;
2174 L_Key_Members_IN_REC PA_PROJECT_PUB.PROJECT_ROLE_REC_TYPE;
2175 L_Tasks_IN_REC PA_PROJECT_PUB.TASK_IN_REC_TYPE;
2176
2177 L_Key_Members_IN_TBL PA_PROJECT_PUB.PROJECT_ROLE_TBL_TYPE;
2178 L_Class_Categories_IN_TBL PA_PROJECT_PUB.CLASS_CATEGORY_TBL_TYPE;
2179 L_Tasks_In_TBL PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
2180 L_Tasks_Out_TBL PA_PROJECT_PUB.TASK_OUT_TBL_TYPE;
2181 L_default_org_id NUMBER ;
2182
2183 L_Workflow_Started varchar2(1) ;
2184
2185 /*** Bug 3576717 **/
2186 L_Deliverable_IN_TBL PA_PROJECT_PUB.DELIVERABLE_IN_TBL_TYPE;
2187 L_Deliverable_Action_IN_TBL PA_PROJECT_PUB.ACTION_IN_TBL_TYPE;
2188
2189 -- BUG 3650374
2190 --L_Deliverable_OUT_TBL PA_PROJECT_PUB.DELIVERABLE_OUT_TBL_TYPE;
2191 --L_Deliverable_Action_OUT_TBL PA_PROJECT_PUB.ACTION_OUT_TBL_TYPE;
2192
2193 Begin
2194 -- Bug Fix for Bug 3076921
2195 -- Need to verify and create a structure for the award project template.
2196 -- by calling the CREATE_AWD_PROJ_TEMPLATE_STRUC.
2197
2198 CREATE_AWD_PROJ_TEMPLATE_STRUC(x_award_project_id => p_setup_award_project_id,
2199 x_return_status => x_return_status);
2200
2201 IF X_Return_Status <> 'S' THEN
2202 RETURN;
2203 END IF;
2204
2205 MARK_FIELDS_AS_OVERRIDABLE(x_award_project_id => p_setup_award_project_id,
2206 x_field_name => 'BILL_TO_CUSTOMER',
2207 x_return_status => x_return_status );
2208
2209 IF X_Return_Status <> 'S' THEN
2210 RETURN;
2211 END IF;
2212
2213 MARK_FIELDS_AS_OVERRIDABLE(x_award_project_id =>p_setup_award_project_id ,
2214 x_field_name => 'SHIP_TO_CUSTOMER',
2215 x_return_status => x_return_status );
2216
2217 IF X_Return_Status <> 'S' THEN
2218 RETURN;
2219 END IF;
2220
2221 -- End of Bug Fix for Bug 3076921
2222 L_default_org_id := g_award_rec.org_id ;
2223 G_Product_Code := 'GMS';
2224
2225 L_Project_IN_REC.PM_PROJECT_REFERENCE := g_award_rec.award_number ;
2226 L_Project_IN_REC.PA_PROJECT_NUMBER := g_award_rec.award_number ;
2227 L_Project_IN_REC.PROJECT_NAME := g_award_rec.award_number ;
2228 L_Project_IN_REC.CREATED_FROM_PROJECT_ID := p_setup_award_project_id;
2229 L_Project_IN_REC.CARRYING_OUT_ORGANIZATION_ID := g_award_rec.award_organization_id;
2230 L_Project_IN_REC.START_DATE := g_award_rec.start_date_active;
2231 L_Project_IN_REC.COMPLETION_DATE := g_award_rec.end_date_active ;
2232
2233 L_Project_IN_REC.DISTRIBUTION_RULE := 'EVENT/EVENT';
2234 L_Project_IN_REC.PROJECT_RELATIONSHIP_CODE := 'PRIMARY';
2235 L_Project_IN_REC.PROJECT_CURRENCY_CODE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
2236
2237 L_Project_IN_REC.CUSTOMER_ID := g_award_rec.funding_source_id ; --bug 3076921
2238 -- Bug Fix 3076921. Load the Table
2239 L_PROJECT_IN_REC.BILL_TO_CUSTOMER_ID := G_bill_contact_rec.customer_id;
2240 L_PROJECT_IN_REC.SHIP_TO_CUSTOMER_ID := G_bill_contact_rec.customer_id;
2241
2242
2243 L_Key_Members_IN_REC.PERSON_ID := g_award_rec.award_manager_id ;
2244 L_Key_Members_IN_REC.PROJECT_ROLE_TYPE := 'PROJECT MANAGER';
2245 L_Key_Members_IN_REC.START_DATE := g_award_rec.start_date_active; -- Bug 2534936
2246 L_Key_Members_IN_REC.END_DATE := g_award_rec.end_date_active; -- Bug 2534936
2247
2248 L_Tasks_IN_REC.task_name := g_award_rec.award_number||'-'||'Tsk1'; --L_Task_Name;
2249 L_Tasks_IN_REC.TASK_START_DATE := g_award_rec.start_date_active;
2250 L_Tasks_IN_REC.TASK_COMPLETION_DATE := g_award_rec.end_date_active;
2251 L_Tasks_IN_REC.pa_task_number := g_award_rec.award_number||'-'||'T1'; --L_Task_Number;
2252 L_Tasks_IN_REC.cost_ind_rate_sch_id := g_award_rec.IDC_Schedule_Id;
2253 L_Tasks_IN_REC.pm_task_reference := g_award_rec.award_number;
2254 L_Tasks_IN_REC.chargeable_flag := 'N';
2255
2256 L_Tasks_In_TBL(1) := L_Tasks_IN_REC;
2257 L_Key_Members_IN_TBL(1) := L_Key_Members_IN_REC;
2258
2259 PA_PROJECT_PUB.CREATE_PROJECT(p_api_version_number => 1.0,
2260 p_init_msg_list => 'F',
2261 p_msg_count => G_msg_count,
2262 p_msg_data => G_Msg_Data,
2263 p_return_status => X_Return_Status,
2264 p_project_in => L_Project_IN_REC,
2265 p_project_out => L_Project_OUT_REC,
2266 p_pm_product_code => G_Product_Code,
2267 p_key_members => L_Key_Members_IN_TBL,
2268 p_class_categories => L_Class_Categories_IN_TBL,
2269 p_tasks_in => L_Tasks_IN_TBL,
2270 p_tasks_out => L_Tasks_OUT_TBL,
2271 p_workflow_started => L_Workflow_Started,
2272 P_commit => FND_API.G_FALSE,
2273 /** Bug 3576717 **/
2274 P_deliverables_in => L_Deliverable_IN_TBL,
2275 --P_deliverables_out => L_Deliverable_OUT_TBL, (3650374)
2276 P_deliverable_actions_in => L_Deliverable_Action_IN_TBL
2277 --P_deliverable_actions_out=> L_Deliverable_Action_OUT_TBL (3650374)
2278 );
2279 IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS then
2280
2281 -- -------------------------------------------------
2282 -- Create award project failed So return to the create
2283 -- award. Failure status is allready set by
2284 -- project pub API.
2285 -- -------------------------------------------------
2286 return ;
2287 ELSE
2288 L_Err_Code := X_Return_Status;
2289 g_award_rec.award_project_id := L_Project_OUT_REC.PA_PROJECT_ID;
2290 END IF;
2291
2292 --
2293 -- Update projects for additional informations.
2294
2295 --
2296
2297 Update PA_PROJECTS_ALL
2298 set cost_ind_rate_sch_id = g_award_rec.IDC_Schedule_Id,
2299 cost_ind_sch_fixed_date = g_award_rec.cost_ind_sch_fixed_date,
2300 labor_invoice_format_id = g_award_rec.Labor_Invoice_Format_Id,
2301 non_labor_invoice_format_Id = g_award_rec.Non_Labor_Invoice_Format_Id,
2302 name = g_award_rec.award_number,
2303 segment1 = g_award_rec.award_number,
2304 billing_cycle_id = g_award_rec.Billing_Cycle_Id,
2305 billing_offset = NVL(g_award_rec.Billing_Offset,0) ,
2306 last_update_date = sysdate,
2307 last_updated_by = fnd_global.user_id,
2308 last_update_login = fnd_global.login_id
2309 where project_id = g_award_rec.award_project_id ;
2310
2311 --
2312 -- Update project customers.
2313 --
2314
2315 update PA_PROJECT_Customers
2316 set BILL_TO_ADDRESS_ID = NVL(g_award_rec.bill_To_Address_Id, bill_TO_ADDRESS_ID ),
2317 SHIP_TO_ADDRESS_ID = NVL(g_award_rec.Ship_To_Address_Id, SHIP_TO_ADDRESS_ID )
2318 ,LAST_UPDATE_DATE = SYSDATE
2319 ,LAST_UPDATED_BY = fnd_global.user_id
2320 ,LAST_UPDATE_LOGIN = fnd_global.login_id
2321 where project_id = g_award_rec.award_project_id
2322 and customer_id = L_Project_IN_REC.CUSTOMER_ID ;
2323
2324 END CREATE_AWARD_PROJECT;
2325
2326 -------------------------------------------------------------------------
2327 -- CREATE AWARD_PROJECT ENDS HERE
2328 -------------------------------------------------------------------------
2329
2330
2331 -- ===============================================================================
2332 -- COPY_AWARD :
2333 -- Copy award has all the parameters that we have in quick entry for award.
2334 -- The ID's in the table are replaced by corresponding value. Users must
2335 -- provide decode values instead of code values.
2336 -- P_return_status : S- Success,
2337 -- E- Business Rule Validation error
2338 -- U- Unexpected Error
2339 -- P_API_VERSION_NUMBER : 1.0
2340 -- ===============================================================================
2341 -- How Copy award works
2342 -- Copy award has two calling points
2343 -- 1. Public API
2344 -- 2. Oracle internal sources
2345 -- Copy award rely on independent components of the award api.
2346 -- The independant components are as follows :
2347 -- A. Create_award
2348 -- B. Create_contacts
2349 -- C. Create_personnel
2350 -- D. Create_reference_numbers
2351 -- E. Create_notifications.
2352 -- F. create_terms_and_conditions.
2353 -- Copy AWARD integrate other components together and create award and
2354 -- other dependent child records.
2355 -- The parameters passed may be null. Only parameters that has value
2356 -- will be overwritten to the base award columns.
2357 -- ================================================================================
2358 PROCEDURE COPY_AWARD(
2359 X_MSG_COUNT IN OUT NOCOPY NUMBER,
2360 X_MSG_DATA IN OUT NOCOPY VARCHAR2,
2361 X_return_status IN OUT NOCOPY VARCHAR2,
2362 P_AWARD_NUMBER IN OUT NOCOPY VARCHAR2 ,
2363 X_AWARD_ID OUT NOCOPY NUMBER,
2364 P_CALLING_MODULE IN VARCHAR2,
2365 P_API_VERSION_NUMBER IN NUMBER,
2366 P_AWARD_BASE_ID IN NUMBER,
2367 P_AWARD_SHORT_NAME IN VARCHAR2 DEFAULT NULL,
2368 P_AWARD_FULL_NAME IN VARCHAR2 DEFAULT NULL,
2369 P_AWARD_START_DATE IN DATE DEFAULT NULL,
2370 P_AWARD_END_DATE IN DATE DEFAULT NULL,
2371 P_AWARD_CLOSE_DATE IN DATE DEFAULT NULL,
2372 P_PREAWARD_DATE IN DATE DEFAULT NULL,
2373 P_AWARD_PURPOSE_CODE IN VARCHAR2 DEFAULT NULL,
2374 P_AWARD_STATUS_CODE IN VARCHAR2 DEFAULT NULL,
2375 P_AWARD_MANAGER_ID IN NUMBER DEFAULT NULL,
2376 P_AWARD_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
2377 P_FUNDING_SOURCE_ID IN NUMBER DEFAULT NULL,
2378 P_FUNDING_SOURCE_AWARD_NUM IN VARCHAR2 DEFAULT NULL,
2379 P_ALLOWABLE_SCHEDULE_ID IN VARCHAR2 DEFAULT NULL,
2380 P_INDIRECT_SCHEDULE_ID IN VARCHAR2 DEFAULT NULL,
2381 P_COST_IND_SCH_FIXED_DATE IN DATE DEFAULT NULL,
2382 P_REVENUE_DISTRIBUTION_RULE IN VARCHAR2 DEFAULT NULL,
2383 P_BILLING_DISTRIBUTION_RULE IN VARCHAR2 DEFAULT NULL,
2384 P_BILLING_TERM_ID IN NUMBER DEFAULT NULL,
2385 P_LABOR_INVOICE_FORMAT_ID IN VARCHAR2 DEFAULT NULL,
2386 P_NON_LABOR_INVOICE_FORMAT_ID IN VARCHAR2 DEFAULT NULL,
2387 P_BILLING_CYCLE_ID IN VARCHAR2 DEFAULT NULL,
2388 P_AMOUNT_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
2389 P_BOUNDARY_CODE IN VARCHAR2 DEFAULT NULL,
2390 P_AGREEMENT_TYPE IN VARCHAR2 DEFAULT NULL,
2391 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
2392 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
2393 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
2394 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
2395 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
2396 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
2397 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
2398 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
2399 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
2400 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
2401 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
2402 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
2403 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
2404 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
2405 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
2406 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
2407 P_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
2408 P_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
2409 P_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
2410 P_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
2411 P_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
2412 P_ATTRIBUTE21 IN VARCHAR2 DEFAULT NULL,
2413 P_ATTRIBUTE22 IN VARCHAR2 DEFAULT NULL,
2414 P_ATTRIBUTE23 IN VARCHAR2 DEFAULT NULL,
2415 P_ATTRIBUTE24 IN VARCHAR2 DEFAULT NULL,
2416 P_ATTRIBUTE25 IN VARCHAR2 DEFAULT NULL,
2417 P_PROPOSAL_ID IN NUMBER DEFAULT NULL) IS
2418
2419 L_api_name varchar2(30) := 'GMS_AWARD_PVT.COPY_AWARD';
2420 l_award_rec gms_awards_all%ROWTYPE ;
2421
2422 l_awards_contacts gms_awards_contacts%ROWTYPE ;
2423 l_report_rec gms_default_reports%ROWTYPE ;
2424 l_refnum_rec gms_reference_numbers%ROWTYPE ;
2425 l_personnel_rec gms_personnel%ROWTYPE ;
2426 l_termscond_rec gms_awards_terms_conditions%ROWTYPE ;
2427
2428 l_base_fund_src_id gms_awards_all.funding_source_id%TYPE ;
2429
2430 l_default_report_id NUMBER ;
2431 l_term_id NUMBER ;
2432
2433 l_validate BOOLEAN := FALSE ;
2434 l_row_id varchar2(45) ;
2435
2436 -- =========
2437 -- c_award
2438 -- Fetch the source award record.
2439 -- This will give us the details of the rest of the columns
2440 -- to copy award.
2441 -- =========
2442 cursor c_award is
2443 select *
2444 from gms_awards_all
2445 where award_id = p_award_base_id ;
2446
2447 -- ======
2448 -- gms_awards_contacts
2449 -- Copy Contacts cursor.
2450 -- ======
2451 -- Bug 2244805. Copy all contacts not just primary contacts
2452 cursor c_award_contacts is
2453 select *
2454 from gms_awards_contacts
2455 where award_id = p_award_base_id;
2456 -- and primary_flag <> 'Y' Bug 2244805
2457 -- and l_base_fund_src_id = g_award_rec.funding_source_id ; -- Bug 2244805
2458
2459 -- ======
2460 -- c_default_reports
2461 -- Copu Reports cursor.
2462 -- ======
2463
2464 cursor c_default_reports is
2465 select *
2466 from gms_default_reports
2467 where award_id = p_award_base_id
2468 and l_base_fund_src_id = g_award_rec.funding_source_id ;
2469
2470 -- ======
2471 -- c_gms_personnel
2472 -- Copy c_gms_personnel cursor
2473 -- ======
2474 cursor c_gms_personnel is
2475 select *
2476 from gms_personnel
2477 where award_id = p_award_base_id
2478 and award_role <> 'AM';
2479
2480 -- ======
2481 -- c_reference_numbers
2482 -- Copy c_reference_numbers cursor.
2483 -- ======
2484 cursor c_reference_numbers is
2485 select *
2486 from gms_reference_numbers
2487 where award_id = p_award_base_id;
2488
2489 -- ======
2490 -- c_terms_conditions
2491 -- Copy Terms and conditions cursor.
2492 -- ======
2493 cursor c_terms_conditions is
2494 select *
2495 from gms_awards_terms_conditions
2496 where award_id = p_award_base_id;
2497
2498 -- ========================================
2499 -- copy award Main Logic.
2500 -- ========================================
2501 BEGIN
2502 -- Initialize the message stack.
2503 -- -----------------------------
2504 init_message_stack ;
2505
2506 G_msg_count := X_msg_count ;
2507 G_msg_data := X_MSG_DATA ;
2508 G_calling_module := P_CALLING_MODULE ;
2509
2510 -- ============
2511 -- Initialize the return status.
2512 -- ============
2513 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
2514 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
2515
2516 X_return_status := FND_API.G_RET_STS_SUCCESS ;
2517
2518 END IF ;
2519
2520 SAVEPOINT copy_award_pvt ;
2521
2522 G_stage := 'FND_API.Compatible_API_Call' ;
2523
2524 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
2525 p_api_version_number ,
2526 l_api_name ,
2527 G_pkg_name )
2528 THEN
2529 RAISE e_ver_mismatch ;
2530 END IF ;
2531
2532 G_stage := 'AWARD RECORD' ;
2533
2534
2535 -- ======
2536 -- fetch Base record here.
2537 -- ======
2538
2539 OPEN C_AWARD ;
2540 FETCH C_AWARD into L_AWARD_REC ;
2541 close c_award ;
2542
2543 l_base_fund_src_id := l_award_rec.funding_source_id ;
2544 -- ======
2545 -- Override Columns
2546 -- ======
2547
2548 --
2549 -- Currently we only create award and not
2550 -- template. So this is understood
2551 -- award_template_flag must be DEFERRED
2552 --
2553 l_award_rec.award_template_flag := 'DEFERRED' ;
2554 l_award_rec.award_id := NULL ;
2555 l_award_rec.award_project_id := NULL ;
2556
2557
2558 -- ========
2559 -- Override logic
2560 -- We look for not null arguments and
2561 -- copy them to base award columns.
2562 -- Calling program will provide us columns
2563 -- that should be copied to base award columns
2564 -- ========
2565
2566 -- IF p_award_number is not NULL then -- Bug 2652716
2567 l_award_rec.award_number := p_award_number ;
2568 -- end if ;
2569
2570 if P_AWARD_SHORT_NAME is not null then
2571 l_award_rec.award_short_name := P_AWARD_SHORT_NAME ;
2572 end if ;
2573
2574 IF P_AWARD_FULL_NAME is not null then
2575 l_award_rec.AWARD_FULL_NAME := P_AWARD_FULL_NAME ;
2576 end if ;
2577
2578 IF P_AWARD_START_DATE is not null then
2579 l_award_rec.start_date_active := P_AWARD_START_DATE ;
2580 end if ;
2581
2582 IF P_AWARD_END_DATE is not null then
2583 l_award_rec.end_date_active := P_AWARD_END_DATE ;
2584 end if ;
2585
2586 IF P_AWARD_CLOSE_DATE is not NULL then
2587 l_award_rec.CLOSE_DATE := P_AWARD_CLOSE_DATE ;
2588 end if ;
2589 IF P_PREAWARD_DATE is not NULL then
2590 l_award_rec.PREAWARD_DATE := P_PREAWARD_DATE ;
2591 end if ;
2592
2593 IF P_AWARD_PURPOSE_CODE is not null then
2594 l_award_rec.AWARD_PURPOSE_CODE := P_AWARD_PURPOSE_CODE ;
2595 end if ;
2596 IF P_AWARD_STATUS_CODE is not NULL then
2597 l_award_rec.STATUS := P_AWARD_STATUS_CODE ;
2598 end if ;
2599
2600 if P_AWARD_MANAGER_ID is not null then
2601 l_award_rec.AWARD_MANAGER_ID := P_AWARD_MANAGER_ID ;
2602 end if ;
2603
2604 IF P_AWARD_ORGANIZATION_ID is not null then
2605 l_award_rec.AWARD_ORGANIZATION_ID := P_AWARD_ORGANIZATION_ID ;
2606 end if ;
2607
2608 IF P_FUNDING_SOURCE_ID is not null then
2609 l_award_rec.FUNDING_SOURCE_ID := P_FUNDING_SOURCE_ID ;
2610 end if ;
2611
2612 IF P_FUNDING_SOURCE_AWARD_NUM is not null then
2613 l_award_rec.funding_source_award_number := P_FUNDING_SOURCE_AWARD_NUM ;
2614 end if ;
2615
2616 IF P_ALLOWABLE_SCHEDULE_ID is not null then
2617 l_award_rec.allowable_schedule_id := P_ALLOWABLE_SCHEDULE_ID ;
2618 end if ;
2619
2620 IF P_INDIRECT_SCHEDULE_ID is not null then
2621 l_award_rec.idc_schedule_id := P_INDIRECT_SCHEDULE_ID ;
2622 end if ;
2623
2624 IF P_COST_IND_SCH_FIXED_DATE is not null then
2625 l_award_rec.COST_IND_SCH_FIXED_DATE := P_COST_IND_SCH_FIXED_DATE ;
2626 end if ;
2627
2628 IF P_REVENUE_DISTRIBUTION_RULE is not null then
2629 l_award_rec.REVENUE_DISTRIBUTION_RULE := P_REVENUE_DISTRIBUTION_RULE ;
2630 end if ;
2631
2632 IF P_BILLING_DISTRIBUTION_RULE is not null then
2633 l_award_rec.BILLING_DISTRIBUTION_RULE := P_BILLING_DISTRIBUTION_RULE ;
2634 end if ;
2635
2636 IF P_BILLING_TERM_ID is not null then
2637 l_award_rec.BILLING_TERM := P_BILLING_TERM_ID ;
2638 end if ;
2639
2640 IF P_LABOR_INVOICE_FORMAT_ID is not null then
2641 l_award_rec.LABOR_INVOICE_FORMAT_ID := P_LABOR_INVOICE_FORMAT_ID ;
2642 end if ;
2643
2644 IF P_NON_LABOR_INVOICE_FORMAT_ID is not null then
2645 l_award_rec.NON_LABOR_INVOICE_FORMAT_ID := P_NON_LABOR_INVOICE_FORMAT_ID ;
2646 end if ;
2647
2648 if P_BILLING_CYCLE_ID is not null then
2649 l_award_rec.BILLING_CYCLE_ID := P_BILLING_CYCLE_ID ;
2650 end if ;
2651
2652 IF P_AMOUNT_TYPE_CODE is not null then
2653 l_award_rec.AMOUNT_TYPE := P_AMOUNT_TYPE_CODE ;
2654 end if ;
2655
2656 IF P_BOUNDARY_CODE is not null then
2657 l_award_rec.BOUNDARY_CODE := P_BOUNDARY_CODE ;
2658 end if ;
2659
2660 IF P_AGREEMENT_TYPE is not null then
2661 l_award_rec.TYPE := P_AGREEMENT_TYPE ;
2662 end if ;
2663
2664 IF P_PROPOSAL_ID is not null then
2665 l_award_rec.PROPOSAL_ID := P_PROPOSAL_ID ;
2666 end if ;
2667
2668 -- =====
2669 -- Override columns done.
2670 -- =====
2671
2672 -- ==========================
2673 -- Populate flexfields column
2674 -- ==========================
2675 IF P_ATTRIBUTE_CATEGORY is not null then
2676 l_award_rec.ATTRIBUTE_CATEGORY := P_ATTRIBUTE_CATEGORY ;
2677 END IF ;
2678
2679 IF p_attribute1 is not null then
2680 l_award_rec.attribute1 := P_ATTRIBUTE1 ;
2681 end if ;
2682
2683 IF p_attribute2 is not null then
2684 l_award_rec.attribute2 := P_ATTRIBUTE2 ;
2685 end if ;
2686
2687 IF p_attribute3 is not null then
2688 l_award_rec.attribute3 := P_ATTRIBUTE3 ;
2689 end if ;
2690
2691 IF p_attribute4 is not null then
2692 l_award_rec.attribute4 := P_ATTRIBUTE4 ;
2693 end if ;
2694
2695 IF p_attribute5 is not null then
2696 l_award_rec.attribute5 := P_ATTRIBUTE5 ;
2697 end if ;
2698
2699 IF p_attribute6 is not null then
2700 l_award_rec.attribute6 := P_ATTRIBUTE6 ;
2701 end if ;
2702
2703 IF p_attribute7 is not null then
2704 l_award_rec.attribute7 := P_ATTRIBUTE7 ;
2705 end if ;
2706
2707 IF p_attribute8 is not null then
2708 l_award_rec.attribute8 := P_ATTRIBUTE8 ;
2709 end if ;
2710
2711 IF p_attribute9 is not null then
2712 l_award_rec.attribute9 := P_ATTRIBUTE9 ;
2713 end if ;
2714
2715 IF p_attribute10 is not null then
2716 l_award_rec.attribute10 := P_ATTRIBUTE10 ;
2717 end if ;
2718
2719 IF p_attribute11 is not null then
2720 l_award_rec.attribute11 := P_ATTRIBUTE11 ;
2721 end if ;
2722
2723 IF p_attribute12 is not null then
2724 l_award_rec.attribute12 := P_ATTRIBUTE12 ;
2725 end if ;
2726
2727 IF p_attribute13 is not null then
2728 l_award_rec.attribute13 := P_ATTRIBUTE13 ;
2729 end if ;
2730
2731 IF p_attribute14 is not null then
2732 l_award_rec.attribute14 := P_ATTRIBUTE14 ;
2733 end if ;
2734
2735 IF p_attribute15 is not null then
2736 l_award_rec.attribute15 := P_ATTRIBUTE15 ;
2737 end if ;
2738 IF p_attribute16 is not null then
2739 l_award_rec.attribute16 := P_ATTRIBUTE16 ;
2740 end if ;
2741
2742 IF p_attribute17 is not null then
2743 l_award_rec.attribute17 := P_ATTRIBUTE17 ;
2744 end if ;
2745 IF p_attribute18 is not null then
2746 l_award_rec.attribute18 := P_ATTRIBUTE18 ;
2747 end if ;
2748 IF p_attribute19 is not null then
2749 l_award_rec.attribute19 := P_ATTRIBUTE19 ;
2750 end if ;
2751 IF p_attribute20 is not null then
2752 l_award_rec.attribute20 := P_ATTRIBUTE20 ;
2753 end if ;
2754 IF p_attribute21 is not null then
2755 l_award_rec.attribute21 := P_ATTRIBUTE21 ;
2756 end if ;
2757 IF p_attribute22 is not null then
2758 l_award_rec.attribute22 := P_ATTRIBUTE22 ;
2759 end if ;
2760 IF p_attribute23 is not null then
2761 l_award_rec.attribute23 := P_ATTRIBUTE23 ;
2762 end if ;
2763 IF p_attribute24 is not null then
2764 l_award_rec.attribute24 := P_ATTRIBUTE24 ;
2765 end if ;
2766 IF p_attribute25 is not null then
2767 l_award_rec.attribute25 := P_ATTRIBUTE25 ;
2768 end if ;
2769
2770
2771 --
2772 -- Create award 1st.
2773 -- We use our create_award program unit
2774 -- to create award .
2775 --
2776
2777 G_stage := 'gms_award_pvt.create_award' ;
2778
2779 create_award( X_msg_count,
2780 X_MSG_DATA ,
2781 X_return_status ,
2782 L_ROW_ID ,
2783 X_AWARD_ID ,
2784 P_CALLING_MODULE,
2785 P_API_VERSION_NUMBER,
2786 L_AWARD_REC ) ;
2787
2788 -- =================
2789 -- Make sure that X_return_status is success before continue.
2790 -- =================
2791 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2792
2793 -- ********* ERROR Return Here ********
2794 Raise fnd_api.g_exc_error ;
2795 END IF ;
2796
2797 p_award_number := g_award_rec.award_number ;
2798
2799 G_stage := 'Copy Contacts begins ' ;
2800
2801 -- ========
2802 -- Copy Award Contacts Now
2803 -- ========
2804
2805 -- Bug 2244805 Added if condition and delete statement
2806 -- Requirement: If funding source has changed from funding source in base award
2807 -- then award contacts (bill to and ship to only) should be taken from receivables.
2808 -- If funding source has not changed from funding source in base award, then simply
2809 -- copy ALL contacts from the base award (not from receivables)
2810 --
2811 -- The bill to and ship to contacts are created (based on default contacts in receivables)
2812 -- during create_award. Therefore, if the funding source has changed, then there is no need
2813 -- to copy any more contacts. If on the other hand, the funding source has not changed from
2814 -- that existing in the base award, we first delete the default contacts created from
2815 -- receivables by create_award and then copy contacts existing in the base award.
2816
2817 --Bug : 3455542 : Commented by Sanjay Banerjee
2818 --create_award procedure is alredy creating contacts, removing these contacts and re-creating
2819 --does not make sense. Also, create_contact is not just a copy procedure, it does the validation too.
2820 --We need to create contacts based on the funding_source given. Even if the funding source is same,
2821 --as before, we have to query to get the latest bill_to and ship_to address_ids.
2822 --
2823 /*****
2824 if (l_base_fund_src_id = g_award_rec.funding_source_id) then
2825
2826 delete from gms_awards_contacts
2827 where award_id = g_award_rec.award_id;
2828
2829 for l_rec in c_award_contacts
2830 LOOP
2831 l_awards_contacts.award_id := g_award_rec.award_id ;
2832 l_awards_contacts.contact_id := l_rec.contact_id ;
2833 l_awards_contacts.primary_flag := l_rec.primary_flag ;
2834 l_awards_contacts.customer_id := l_rec.customer_id ;
2835 l_awards_contacts.usage_code := l_rec.usage_code ;
2836 l_awards_contacts.last_update_date := SYSDATE ;
2837 l_awards_contacts.last_updated_by := l_rec.last_updated_by ;
2838 l_awards_contacts.creation_date := SYSDATE ;
2839 l_awards_contacts.created_by := l_rec.created_by ;
2840 l_awards_contacts.last_update_login := l_rec.last_update_login ;
2841
2842 -- Create Contacts ...
2843 --- ================
2844 G_stage := 'Copy Contacts :'||l_rec.contact_id||' '||l_rec.customer_id||' '
2845 ||l_rec.usage_code ;
2846 create_contact ( x_msg_count,
2847 x_msg_data,
2848 x_return_status,
2849 l_row_id,
2850 p_calling_module,
2851 p_api_version_number,
2852 l_validate,
2853 l_awards_contacts);
2854
2855 -- =================
2856 -- Make sure that X_return_status is success before continue.
2857 -- =================
2858 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2859 Raise fnd_api.g_exc_error ;
2860 END IF ;
2861
2862 END LOOP ;
2863 end if;
2864 *****/
2865 -- =========
2866 -- Copy Default Reports
2867 -- =========
2868 G_stage := 'Copy Default reports begins' ;
2869
2870 for L_REC in c_default_reports
2871 LOOP
2872 l_report_rec := l_rec ;
2873 l_report_rec.default_report_id := l_rec.default_report_id ;
2874 l_report_rec.report_template_id := l_rec.report_template_id ;
2875 l_report_rec.award_id := g_award_rec.award_id ;
2876 l_report_rec.last_update_date := l_rec.last_update_date ;
2877 l_report_rec.last_updated_by := l_rec.last_updated_by ;
2878 l_report_rec.creation_date := l_rec.creation_date ;
2879 l_report_rec.created_by := l_rec.created_by ;
2880 l_report_rec.last_update_login := l_rec.last_update_login ;
2881 l_report_rec.frequency := l_rec.frequency ;
2882 l_report_rec.due_within_days := l_rec.due_within_days ;
2883 l_report_rec.site_use_id := l_rec.site_use_id ;
2884 l_report_rec.copy_number := l_rec.copy_number;
2885 l_report_rec.request_id := l_rec.request_id ;
2886 l_report_rec.program_application_id := l_rec.program_application_id;
2887 l_report_rec.program_id := l_rec.program_id ;
2888 l_report_rec.program_update_date := l_rec.program_update_date ;
2889 l_report_rec.attribute_category := l_rec.attribute_category ;
2890 l_report_rec.attribute1 := l_rec.attribute1 ;
2891 l_report_rec.attribute2 := l_rec.attribute2 ;
2892 l_report_rec.attribute3 := l_rec.attribute3 ;
2893 l_report_rec.attribute4 := l_rec.attribute4 ;
2894 l_report_rec.attribute5 := l_rec.attribute5 ;
2895 l_report_rec.attribute6 := l_rec.attribute6 ;
2896 l_report_rec.attribute7 := l_rec.attribute7 ;
2897 l_report_rec.attribute8 := l_rec.attribute8 ;
2898 l_report_rec.attribute9 := l_rec.attribute9 ;
2899 l_report_rec.attribute10 := l_rec.attribute10 ;
2900 l_report_rec.attribute11 := l_rec.attribute11 ;
2901 l_report_rec.attribute12 := l_rec.attribute12 ;
2902 l_report_rec.attribute13 := l_rec.attribute13 ;
2903 l_report_rec.attribute14 := l_rec.attribute14 ;
2904 l_report_rec.attribute15 := l_rec.attribute15 ;
2905
2906 create_report ( x_msg_count,
2907 x_msg_data,
2908 x_return_status,
2909 l_default_report_id,
2910 l_row_id,
2911 p_calling_module,
2912 p_api_version_number,
2913 l_validate,
2914 l_report_rec ) ;
2915
2916 G_stage := 'Copy Reports :'||l_default_report_id ;
2917
2918 -- =================
2919 -- Make sure that X_return_status is success before continue.
2920 -- =================
2921 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2922 Raise fnd_api.g_exc_error ;
2923 END IF ;
2924
2925 END LOOP ;
2926
2927 G_stage := 'Copy Personnel starts here' ;
2928
2929 -- =====
2930 -- Copy Personnel Details
2931 -- =====
2932 FOR L_REC in c_gms_personnel LOOP
2933
2934 l_personnel_rec.award_id := g_award_rec.award_id ;
2935 l_personnel_rec.person_id := l_rec.person_id ;
2936 l_personnel_rec.award_role := l_rec.award_role ;
2937 l_personnel_rec.last_update_date := SYSDATE ;
2938 l_personnel_rec.last_updated_by := l_rec.last_updated_by ;
2939 l_personnel_rec.creation_date := l_rec.creation_date ;
2940 l_personnel_rec.created_by := l_rec.created_by ;
2941 l_personnel_rec.last_update_login := l_rec.last_update_login ;
2942 l_personnel_rec.start_date_active := l_rec.start_date_active ;
2943 l_personnel_rec.end_date_active := l_rec.end_date_active ;
2944 l_personnel_rec.personnel_id := l_rec.personnel_id ;
2945 l_personnel_rec.required_flag := l_rec.required_flag ;
2946 --l_personnel_rec.project_party_id := l_rec.project_party_id ;
2947
2948 G_stage := 'Copy Personnel :'||l_rec.person_id||' '||l_rec.award_role ;
2949
2950 create_personnel ( x_msg_count,
2951 x_msg_data,
2952 x_return_status,
2953 l_row_id,
2954 p_calling_module,
2955 p_api_version_number,
2956 l_validate,
2957 l_personnel_rec ) ;
2958
2959 -- =================
2960 -- Make sure that X_return_status is success before continue.
2961 -- =================
2962 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2963 Raise fnd_api.g_exc_error ;
2964 END IF ;
2965
2966 END LOOP ;
2967
2968 -- =====
2969 -- Copy Reference Numbers
2970 -- =====
2971
2972 g_stage := 'Copy Reference Numbers begins here ' ;
2973
2974 FOR l_rec in c_reference_numbers LOOP
2975
2976 l_refnum_rec.award_id := g_award_rec.award_id ;
2977 l_refnum_rec.type := l_rec.type ;
2978 l_refnum_rec.last_update_date := l_rec.last_update_date ;
2979 l_refnum_rec.last_updated_by := l_rec.last_updated_by ;
2980 l_refnum_rec.creation_date := l_rec.creation_date ;
2981 l_refnum_rec.created_by := l_rec.created_by ;
2982 l_refnum_rec.last_update_login := l_rec.last_update_login ;
2983 l_refnum_rec.value := l_rec.value ;
2984 l_refnum_rec.required_flag := l_rec.required_flag ;
2985
2986 G_stage := 'Copy reference Number :'||l_refnum_rec.type||' '||l_refnum_rec.value ;
2987
2988 create_reference_number ( x_msg_count,
2989 x_msg_data,
2990 x_return_status,
2991 l_row_id,
2992 p_calling_module,
2993 p_api_version_number,
2994 l_validate,
2995 l_refnum_rec ) ;
2996 -- =================
2997 -- Make sure that X_return_status is success before continue.
2998 -- =================
2999 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
3000 Raise fnd_api.g_exc_error ;
3001 END IF ;
3002
3003 END LOOP ;
3004
3005 -- =====
3006 -- Copy Terms and conditions.
3007 -- =====
3008
3009 FOR l_rec in c_terms_conditions LOOP
3010 l_termscond_rec.award_id := g_award_rec.award_id ;
3011 l_termscond_rec.category_id := l_rec.category_id ;
3012 l_termscond_rec.term_id := l_rec.term_id ;
3013 l_termscond_rec.last_update_date := l_rec.last_update_date ;
3014 l_termscond_rec.last_updated_by := l_rec.last_updated_by ;
3015 l_termscond_rec.creation_date := l_rec.creation_date ;
3016 l_termscond_rec.created_by := l_rec.created_by ;
3017 l_termscond_rec.last_update_login := l_rec.last_update_login ;
3018 l_termscond_rec.operand := l_rec.operand ;
3019 l_termscond_rec.value := l_rec.value ;
3020
3021 create_term_condition ( x_msg_count,
3022 x_msg_data,
3023 x_return_status,
3024 -- Removed this parameter as we don't return this value.
3025 -- l_term_id,
3026 l_row_id,
3027 p_calling_module,
3028 p_api_version_number,
3029 l_validate,
3030 l_termscond_rec ) ;
3031 -- =================
3032 -- Make sure that X_return_status is success before continue.
3033 -- =================
3034 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
3035 Raise fnd_api.g_exc_error ;
3036 END IF ;
3037
3038 END LOOP ;
3039
3040 --
3041 -- Create Personnel Records.
3042 --
3043 --
3044
3045 -- =================
3046 -- Make sure that X_return_status is success before continue.
3047 -- =================
3048 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
3049 Raise fnd_api.g_exc_error ;
3050 END IF ;
3051
3052 reset_message_flag ;
3053
3054 G_stage := 'Award Copied Successfully' ;
3055 EXCEPTION
3056 WHEN E_VER_MISMATCH THEN
3057 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
3058 p_token1 => 'SUPVER',
3059 P_VAL1 => g_api_version_number) ;
3060 set_return_status(X_return_status, 'B' ) ;
3061 x_msg_count := G_msg_count ;
3062 x_msg_data := G_msg_data ;
3063
3064 WHEN FND_API.G_EXC_ERROR THEN
3065 ROLLBACK TO copy_award_pvt ;
3066 set_return_status(X_return_status, 'B' ) ;
3067 x_msg_count := G_msg_count ;
3068 x_msg_data := G_msg_data ;
3069 WHEN OTHERS THEN
3070 ROLLBACK TO copy_award_pvt ;
3071 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3072 FND_MSG_PUB.add_exc_msg ( p_pkg_name => G_PKG_NAME
3073 , p_procedure_name => l_api_name );
3074 FND_MSG_PUB.Count_And_Get
3075 ( p_count => x_msg_count ,
3076 p_data => x_msg_data );
3077
3078 END COPY_AWARD ;
3079
3080 -- +++++++++++++++++
3081
3082
3083 -- ===========================================================
3084 -- Create Installments.
3085 -- ===========================================================
3086
3087 PROCEDURE CREATE_INSTALLMENT
3088 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
3089 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
3090 X_return_status IN OUT NOCOPY VARCHAR2 ,
3091 X_ROW_ID OUT NOCOPY VARCHAR2 ,
3092 X_INSTALLMENT_ID OUT NOCOPY NUMBER ,
3093 P_CALLING_MODULE IN VARCHAR2 ,
3094 P_API_VERSION_NUMBER IN NUMBER,
3095 P_validate IN BOOLEAN DEFAULT TRUE ,
3096 P_INSTALLMENT_REC IN GMS_INSTALLMENTS%ROWTYPE
3097 ) IS
3098
3099 l_award_start_date_active DATE ;
3100 l_award_end_date_active DATE ;
3101 l_award_close_date DATE ;
3102 l_dummy VARCHAR2(1) ;
3103 l_rowid VARCHAR2(45) ;
3104 l_api_name VARCHAR2(30) := 'CREATE_INSTALLMENT';
3105
3106
3107 CURSOR l_installment_type_csr (p_installment_type IN VARCHAR2 ) IS
3108 SELECT 'X'
3109 FROM gms_lookups
3110 WHERE lookup_type = 'INSTALLMENT_TYPE'
3111 AND lookup_code = p_installment_type ;
3112
3113 -- This is to check the uniqueness of the reference number for that award.
3114 CURSOR l_installment_num_csr(p_installment_num IN VARCHAR2 ,p_award_id IN NUMBER ) IS
3115 SELECT 'X'
3116 FROM gms_installments
3117 WHERE installment_num = p_installment_num
3118 AND award_id = p_award_id ;
3119
3120 CURSOR l_award_rec_csr(p_award_id IN NUMBER ) IS
3121 SELECT start_date_active , end_date_active,close_date
3122 FROM gms_awards_all
3123 WHERE award_id = p_award_id ;
3124
3125
3126 PROCEDURE check_installment_required (p_validate IN BOOLEAN ,
3127 X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3128 l_error BOOLEAN ;
3129
3130 BEGIN
3131 IF not p_validate then
3132 return ;
3133 end if ;
3134
3135 l_error := FALSE ;
3136
3137 IF G_installment_rec.Award_Id IS NULL THEN
3138 -- ------------------------------
3139 -- MSG: AWARD_ID_NULL
3140 -- ------------------------------
3141 l_error := TRUE ;
3142 add_message_to_stack( P_label => 'GMS_AWD_ID_MISSING' ) ;
3143
3144 END IF ;
3145
3146 IF g_installment_rec.installment_num IS NULL THEN
3147 -- ------------------------------
3148 -- MSG: INSTALLEMNT_NUM_IS_NULL
3149 -- ------------------------------
3150 add_message_to_stack( P_label => 'GMS_INST_NUMBER_NULL' ) ;
3151 l_error := TRUE ;
3152 END IF ;
3153
3154 IF g_installment_rec.type IS NULL THEN
3155 -- ------------------------------
3156 -- MSG: INSTALLMENT_TYPE_IS_NULL
3157 -- ------------------------------
3158 add_message_to_stack( P_label => 'GMS_AWD_INST_TYPE_MISSING' ) ;
3159 l_error := TRUE ;
3160 END IF ;
3161
3162 IF g_installment_rec.start_date_active IS NULL THEN
3163 -- ------------------------------
3164 -- MSG: INSTALLMENT_START_DATE_ACTIVE IS NULL
3165 -- ------------------------------
3166 add_message_to_stack( P_label => 'GMS_INST_START_DATE_NULL' ) ;
3167 l_error := TRUE ;
3168 END IF ;
3169
3170 IF g_installment_rec.end_date_active IS NULL THEN
3171 -- ------------------------------
3172 -- MSG: INSTALLMENT_END_DATE_ACTIVE IS NULL
3173 -- ------------------------------
3174 add_message_to_stack( P_label => 'GMS_INST_END_DATE_NULL' ) ;
3175 l_error := TRUE ;
3176 END IF ;
3177
3178 IF g_installment_rec.close_date IS NULL THEN
3179 -- ------------------------------
3180 -- MSG: INSTALLMENT_CLOSE_DATE IS NULL
3181 -- -----------------------------
3182 add_message_to_stack( P_label => 'GMS_INST_CLOSE_DATE_NULL' ) ;
3183 l_error := TRUE ;
3184 END IF ;
3185
3186 IF g_installment_rec.direct_cost IS NOT NULL AND
3187 g_installment_rec.direct_cost <= 0 THEN
3188 add_message_to_stack( P_label => 'GMS_INST_DIR_COST_NOT_GT_ZERO' ) ;
3189 l_error := TRUE ;
3190 END IF ;
3191
3192 -- This validation is NOT required as -ve values also are accepted for indirect_cost.
3193 /* IF g_installment_rec.indirect_cost IS NOT NULL AND
3194 g_installment_rec.indirect_cost <= 0 THEN
3195 add_message_to_stack( P_label => 'GMS_INST_IND_COST_NOT_GT_ZERO' ) ;
3196 l_error := TRUE ;
3197 END IF ; */
3198
3199 -- ===============================
3200 -- Validate FlexFields
3201 -- ===============================
3202 IF g_installment_rec.attribute_category is not NULL THEN
3203
3204 fnd_flex_descval.set_context_value(g_installment_rec.attribute_category) ;
3205
3206 fnd_flex_descval.set_column_value('ATTRIBUTE1',g_installment_rec.attribute1) ;
3207 fnd_flex_descval.set_column_value('ATTRIBUTE2',g_installment_rec.attribute2) ;
3208 fnd_flex_descval.set_column_value('ATTRIBUTE3',g_installment_rec.attribute3) ;
3209 fnd_flex_descval.set_column_value('ATTRIBUTE4',g_installment_rec.attribute4) ;
3210 fnd_flex_descval.set_column_value('ATTRIBUTE5',g_installment_rec.attribute5) ;
3211 fnd_flex_descval.set_column_value('ATTRIBUTE6',g_installment_rec.attribute6) ;
3212 fnd_flex_descval.set_column_value('ATTRIBUTE7',g_installment_rec.attribute7) ;
3213 fnd_flex_descval.set_column_value('ATTRIBUTE8',g_installment_rec.attribute8) ;
3214 fnd_flex_descval.set_column_value('ATTRIBUTE9',g_installment_rec.attribute9) ;
3215 fnd_flex_descval.set_column_value('ATTRIBUTE10',g_installment_rec.attribute10) ;
3216 fnd_flex_descval.set_column_value('ATTRIBUTE11',g_installment_rec.attribute11) ;
3217 fnd_flex_descval.set_column_value('ATTRIBUTE12',g_installment_rec.attribute12) ;
3218 fnd_flex_descval.set_column_value('ATTRIBUTE13',g_installment_rec.attribute13) ;
3219 fnd_flex_descval.set_column_value('ATTRIBUTE14',g_installment_rec.attribute14) ;
3220 fnd_flex_descval.set_column_value('ATTRIBUTE15',g_installment_rec.attribute15) ;
3221
3222 IF (FND_FLEX_DESCVAL.validate_desccols ('GMS' ,'GMS_AWARDS_DESC_FLEX')) then
3223 -- Validation Passed
3224 NULL ;
3225 ELSE
3226 add_message_to_stack( P_label => 'GMS_AWD_FLEX_INVALID' ) ;
3227 fnd_msg_pub.add_exc_msg(p_pkg_name => 'GMS_AWARD_PVT',
3228 p_procedure_name => 'CREATE_INSTALLMENT',
3229 p_error_text => substr(FND_FLEX_DESCVAL.error_message,1,240)) ;
3230 l_error := TRUE ;
3231
3232 END IF ;
3233
3234 END IF ;
3235
3236 -- ------------------------------
3237 -- End of flex fields validations.
3238 -- ------------------------------
3239
3240
3241 IF L_error THEN
3242 set_return_status ( X_return_status, 'B') ;
3243 END IF ;
3244 END check_installment_required ;
3245
3246 BEGIN
3247
3248 -- Initialize the message stack.
3249 -- -----------------------------
3250 init_message_stack;
3251
3252 G_msg_count := X_msg_count ;
3253 G_msg_data := X_MSG_DATA ;
3254 G_calling_module := P_CALLING_MODULE ;
3255
3256 -- ============
3257 -- Initialize the return status.
3258 -- ============
3259 IF NVL(x_return_status , FND_API.G_RET_STS_SUCCESS) NOT IN
3260 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
3261 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3262 END IF ;
3263
3264 SAVEPOINT create_installment_pvt ;
3265
3266 G_stage := 'FND_API.Compatible_API_Call' ;
3267
3268 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3269 p_api_version_number ,
3270 l_api_name ,
3271 G_pkg_name ) THEN
3272 RAISE e_ver_mismatch ;
3273 END IF ;
3274
3275
3276 G_installment_rec := p_installment_rec ;
3277
3278 check_installment_required(p_validate,x_return_status) ;
3279
3280 IF G_installment_rec.award_id IS NOT NULL THEN
3281 OPEN l_award_rec_csr(G_installment_rec.award_id ) ;
3282 FETCH l_award_rec_csr
3283 INTO l_award_start_date_active , l_award_end_date_active, l_award_close_date ;
3284
3285 IF l_award_rec_csr%NOTFOUND THEN
3286 add_message_to_stack( P_label => 'GMS_AWD_NOT_EXISTS' ) ;
3287 set_return_status ( X_return_status, 'B') ;
3288 END IF ;
3289 CLOSE l_award_rec_csr ;
3290
3291 OPEN l_installment_num_csr (G_installment_rec.installment_num,G_installment_rec.award_id );
3292 FETCH l_installment_num_csr INTO l_dummy ;
3293
3294 -- This is to check the uniqueness of the reference number for that award.
3295 -- Changed as part of testing.
3296
3297 IF l_installment_num_csr%FOUND THEN
3298 add_message_to_stack( P_label => 'GMS_INST_NUMBER_INVALID' ) ;
3299 set_return_status ( X_return_status, 'B') ;
3300 END IF ;
3301 CLOSE l_installment_num_csr ;
3302
3303 END IF ;
3304
3305 OPEN l_installment_type_csr (G_installment_rec.type );
3306 FETCH l_installment_type_csr INTO l_dummy ;
3307 IF l_installment_type_csr%NOTFOUND THEN
3308 add_message_to_stack( P_label => 'GMS_INST_TYPE_INVALID' ) ;
3309 set_return_status ( X_return_status, 'B') ;
3310 END IF ;
3311 CLOSE l_installment_type_csr ;
3312
3313
3314
3315 G_stage := 'FND_API.Verify_Instal_start_date_with_Award_start_date';
3316
3317 IF G_installment_rec.start_date_active IS NOT NULL
3318 and l_award_start_date_active IS NOT NULL
3319 and G_installment_rec.start_date_active < l_award_start_date_active THEN
3320 add_message_to_stack( P_label => 'GMS_INS_ST_DATE_BF_AWD_ST_DATE') ;
3321 set_return_status ( X_return_status, 'B') ;
3322 END IF ;
3323
3324
3325 G_stage := 'FND_API.Verify_Instal_start_date_with_Instal_end_date';
3326
3327 IF G_installment_rec.start_date_active IS NOT NULL
3328 and G_installment_rec.end_date_active IS NOT NULL
3329 and G_installment_rec.start_date_active > G_installment_rec.end_date_active THEN
3330 add_message_to_stack( P_label => 'GMS_INST_ENDATE_BEF_INS_STDATE');
3331 set_return_status ( X_return_status, 'B') ;
3332 END IF ;
3333
3334 G_stage := 'FND_API.Verify_Instal_end_date_with_Award_end_date';
3335
3336 IF G_installment_rec.start_date_active IS NOT NULL
3337 and G_installment_rec.end_date_active IS NOT NULL
3338 and G_installment_rec.end_date_active > l_award_end_date_active THEN
3339 add_message_to_stack( P_label =>'GMS_INS_ENDATE_AFTER_AWENDATE');
3340 set_return_status ( X_return_status, 'B') ;
3341 END IF ;
3342
3343 G_stage := 'FND_API.Verify_Instal_end_date_with_Instal_close_date';
3344
3345 IF G_installment_rec.end_date_active IS NOT NULL
3346 and G_installment_rec.close_date IS NOT NULL
3347 and G_installment_rec.end_date_active > G_installment_rec.close_date THEN
3348 add_message_to_stack( P_label => 'GMS_INS_CLOSEDATE_BEF_ENDDATE') ;
3349 set_return_status ( X_return_status, 'B') ;
3350 END IF ;
3351
3352 G_stage := 'FND_API.Verify_Instal_close_date_with_Award_close_date';
3353
3354 IF G_installment_rec.close_date IS NOT NULL
3355 and l_award_close_date is NOT NULL
3356 and G_installment_rec.close_date > l_award_close_date THEN
3357 add_message_to_stack( P_label => 'GMS_INS_CL_DATE_>_AWD_CL_DATE') ;
3358 set_return_status ( X_return_status, 'B') ;
3359 END IF ;
3360
3361 -- If the return_status <> g_ret_sts_success then we don't proceed with the Inserts.
3362
3363 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
3364 RAISE FND_API.G_EXC_ERROR;
3365 END IF ;
3366
3367
3368 SELECT gms_installments_s.nextval
3369 INTO G_installment_rec.installment_id
3370 FROM dual;
3371
3372 G_stage := 'FND_API.Create_Installment' ;
3373
3374 gms_installments_pkg.insert_row(
3375 X_ROWID => l_rowid,
3376 X_INSTALLMENT_ID => G_installment_rec.installment_id,
3377 X_INSTALLMENT_NUM => G_installment_rec.installment_num,
3378 X_AWARD_ID => G_installment_rec.award_id,
3379 X_START_DATE_ACTIVE => G_installment_rec.start_date_active,
3380 X_END_DATE_ACTIVE => G_installment_rec.end_date_active,
3381 X_CLOSE_DATE => G_installment_rec.close_date,
3382 X_DIRECT_COST => G_installment_rec.direct_cost,
3383 X_INDIRECT_COST => G_installment_rec.indirect_cost,
3384 X_ACTIVE_FLAG => G_installment_rec.active_flag,
3385 X_BILLABLE_FLAG => G_installment_rec.billable_flag,
3386 X_TYPE => G_installment_rec.type,
3387 X_ISSUE_DATE => G_installment_rec.issue_date,
3388 X_DESCRIPTION => G_installment_rec.description,
3389 X_ATTRIBUTE_CATEGORY =>G_installment_rec.attribute_category,
3390 X_ATTRIBUTE1 =>G_installment_rec.attribute1,
3391 X_ATTRIBUTE2 =>G_installment_rec.attribute2,
3392 X_ATTRIBUTE3 =>G_installment_rec.attribute3,
3393 X_ATTRIBUTE4 =>G_installment_rec.attribute4,
3394 X_ATTRIBUTE5 =>G_installment_rec.attribute5,
3395 X_ATTRIBUTE6 =>G_installment_rec.attribute6,
3396 X_ATTRIBUTE7 =>G_installment_rec.attribute7,
3397 X_ATTRIBUTE8 =>G_installment_rec.attribute8,
3398 X_ATTRIBUTE9 =>G_installment_rec.attribute9,
3399 X_ATTRIBUTE10 =>G_installment_rec.attribute10,
3400 X_ATTRIBUTE11 =>G_installment_rec.attribute11,
3401 X_ATTRIBUTE12 =>G_installment_rec.attribute12,
3402 X_ATTRIBUTE13 =>G_installment_rec.attribute13,
3403 X_ATTRIBUTE14 =>G_installment_rec.attribute14,
3404 X_ATTRIBUTE15 =>G_installment_rec.attribute15,
3405 X_MODE => 'R' );
3406
3407 G_stage := 'FND_API.Create_Notification' ;
3408
3409 IF G_installment_rec.active_flag = 'Y' THEN
3410 gms_wf_pkg.init_installment_wf(x_installment_id=> G_installment_rec.installment_id ,
3411 x_award_id => G_installment_rec.award_id);
3412 END IF ;
3413
3414 -- ===================================================
3415 -- Fix for bug 2231131. - Installment Id not returned.
3416 -- ===================================================
3417
3418 X_INSTALLMENT_ID := G_installment_rec.installment_id ;
3419
3420 G_installment_rec := NULL ; -- Resetting the record varible.
3421
3422 G_stage := 'Installment Created Successfully' ;
3423
3424 EXCEPTION
3425 WHEN E_VER_MISMATCH THEN
3426 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
3427 p_token1 => 'SUPVER',
3428 P_VAL1 => g_api_version_number) ;
3429 set_return_status(X_return_status, 'B' ) ;
3430 x_msg_count := G_msg_count ;
3431 x_msg_data := G_msg_data ;
3432
3433 WHEN FND_API.G_EXC_ERROR THEN
3434 G_installment_rec := NULL ; -- Resetting the record vaarible.
3435 ROLLBACK TO create_installment_pvt ;
3436 set_return_status(X_return_status, 'B' ) ;
3437 x_msg_count := G_msg_count ;
3438 x_msg_data := G_msg_data ;
3439 WHEN OTHERS THEN
3440 G_installment_rec := NULL ; -- Resetting the record vaarible.
3441 ROLLBACK TO create_installment_pvt ;
3442 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3443
3444 FND_MSG_PUB.add_exc_msg
3445 ( p_pkg_name => G_PKG_NAME
3446 , p_procedure_name => l_api_name );
3447
3448 FND_MSG_PUB.Count_And_Get
3449 ( p_count => x_msg_count ,
3450 p_data => x_msg_data );
3451
3452
3453
3454 END CREATE_INSTALLMENT ;
3455
3456 -- ----------------------------------------------------------------------------
3457 -- This procedure will create Award Personnel which is used to name and describe
3458 -- the award roles and to specify the effective dates.
3459 -- ----------------------------------------------------------------------------
3460
3461 PROCEDURE CREATE_PERSONNEL
3462 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
3463 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
3464 X_return_status IN OUT NOCOPY VARCHAR2 ,
3465 X_ROW_ID OUT NOCOPY VARCHAR2 ,
3466 P_CALLING_MODULE IN VARCHAR2 ,
3467 P_API_VERSION_NUMBER IN NUMBER ,
3468 P_validate IN BOOLEAN DEFAULT TRUE ,
3469 P_PERSONNEL_REC IN GMS_PERSONNEL%ROWTYPE
3470 ) IS
3471
3472 l_api_name VARCHAR2(30) := 'CREATE_PERSONNEL';
3473 l_start_date_active gms_personnel.start_date_active%TYPE := NULL ;
3474 l_end_date_active gms_personnel.end_date_active%TYPE := NULL ;
3475 l_award_project_id gms_awards_all.award_project_id%TYPE := NULL ;
3476 l_award_template_flag gms_awards_all.award_template_flag%TYPE := NULL ;
3477 l_budget_wf_enabled_flag gms_awards_all.budget_wf_enabled_flag%TYPE := NULL ;
3478 l_rowid varchar2(45) ;
3479
3480
3481 l_error_code VARCHAR2(2000) ;
3482 l_error_stage VARCHAR2(2000) ;
3483 l_dummy VARCHAR2(1) ;
3484
3485 -- This Cursor is used to validate whether the specific person_id (employee_id)
3486 -- is a SYSTEM user or not by checking whether he has any valid assignments between
3487 -- the specific dates.
3488
3489 CURSOR l_full_name_csr(p_person_id IN NUMBER ) IS
3490 SELECT 'X'
3491 from pa_employees p ,
3492 fnd_user u
3493 WHERE
3494 EXISTS (SELECT null FROM per_assignments_f a
3495 WHERE p.person_id = a.person_id
3496 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
3497 AND a.primary_flag = 'Y'
3498 --AND a.assignment_type = 'E') --Bug 10124847
3499 AND a.assignment_type in ('C','E'))
3500 AND p.person_id = u.employee_id(+)
3501 AND p.person_id = p_person_id ;
3502
3503 -- To validate the incoming award_role parameter.
3504
3505 CURSOR l_award_role_csr(p_award_role IN VARCHAR2 ) IS
3506 SELECT 'X'
3507 FROM gms_lookups g
3508 WHERE lookup_type = 'AWARD_ROLE'
3509 AND g.lookup_code = p_award_role
3510 AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active,trunc(sysdate)) ;
3511
3512 CURSOR l_rec_csr(p_award_id IN NUMBER ) IS
3513 SELECT start_date_active,budget_wf_enabled_flag , award_template_flag,
3514 award_project_id , end_date_active
3515 FROM gms_awards_all
3516 WHERE award_id = p_award_id ;
3517
3518 -- ===============================================================
3519
3520 PROCEDURE Verify_dates( p_validate IN BOOLEAN ,
3521 X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3522 BEGIN
3523 IF not p_validate then
3524 return ;
3525 end if ;
3526
3527 -- verify date range
3528 IF ( G_personnel_rec.start_date_active IS NOT NULL AND
3529 G_personnel_rec.end_date_active IS NOT NULL AND
3530 G_personnel_rec.start_date_active > G_personnel_rec.end_date_active ) THEN
3531
3532 add_message_to_stack( P_label => ('GMS_PER_DATES_INVALID') );
3533 set_return_status ( X_return_status, 'B') ;
3534 END IF;
3535 END Verify_dates ;
3536
3537 -- -------------------------------------------------------------------------------
3538
3539 PROCEDURE Verify_User_Status (p_validate IN BOOLEAN , X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3540
3541 CURSOR l_valid_user_csr IS
3542 SELECT user_id
3543 FROM fnd_user
3544 WHERE employee_id = G_personnel_rec.person_id ;
3545
3546 l_user_id NUMBER ;
3547
3548 BEGIN
3549 IF not p_validate then
3550 return ;
3551 end if ;
3552
3553 IF l_budget_wf_enabled_flag = 'Y' THEN
3554
3555 OPEN l_valid_user_csr ;
3556 FETCH l_valid_user_csr INTO l_user_id ;
3557 IF l_valid_user_csr%NOTFOUND THEN
3558 IF G_personnel_rec.award_role ='AM' then
3559 add_message_to_stack( P_label => ('GMS_FND_USER_NOT_CREATED'));
3560 set_return_status ( X_return_status, 'B') ;
3561 ELSE
3562 add_message_to_stack( P_label => ('GMS_WARN_NOT_FND_USER'));
3563 set_return_status ( X_return_status, 'B') ;
3564 END IF ;
3565 END IF ; -- End if for l_valid_user_csr
3566
3567 CLOSE l_valid_user_csr ;
3568
3569 END IF ; -- End if for l_budget_wf_enabled_flag
3570 END Verify_User_Status ;
3571
3572 -- ==============================================================================================
3573
3574 -- The purpose of this validation is that when we are creating another manager
3575 -- for the award the dates should not overlap. We can create more than one manager
3576 -- without overlaping the dates.
3577
3578 PROCEDURE Verify_Award_Manager_Dates(p_validate IN BOOLEAN , X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3579
3580 CURSOR l_award_manager_csr is
3581 SELECT start_date_active, end_date_active
3582 FROM gms_personnel
3583 WHERE award_id = G_personnel_rec.award_id
3584 AND award_role = 'AM'
3585 ORDER BY start_date_active;
3586
3587 BEGIN
3588 IF not p_validate then
3589 RETURN ;
3590 END IF ;
3591
3592 -- =================================================
3593 -- Only award Manager validations are required here.
3594 -- =================================================
3595 IF G_personnel_rec.award_role <> 'AM' then
3596 return ;
3597 END IF ;
3598
3599 -- ===============================================
3600 -- Award Manager validations starts here.
3601 -- ==============================================
3602
3603 FOR rec_award_manager IN l_award_manager_csr LOOP
3604 IF rec_award_manager.end_date_active IS NULL THEN
3605
3606 IF G_personnel_rec.end_date_active IS NULL THEN
3607 add_message_to_stack( P_label => ('GMS_AW_INVALID_MANAGER_DATES') );
3608 set_return_status ( X_return_status, 'B') ;
3609 END IF; -- end if for G_personnel_rec.end_date_active
3610
3611 END IF; -- End if for rec_award_manager.end_date_active
3612
3613 IF (G_personnel_rec.start_date_active >= rec_award_manager.start_date_active
3614 AND G_personnel_rec.start_date_active <= nvl(rec_award_manager.end_date_active
3615 , G_personnel_rec.end_date_active))
3616 OR (G_personnel_rec.end_date_active <= nvl(rec_award_manager.end_date_active,
3617 G_personnel_rec.end_date_active)
3618 AND G_personnel_rec.end_date_active >= rec_award_manager.start_date_active)
3619 OR (G_personnel_rec.start_date_active <= rec_award_manager.start_date_active
3620 AND G_personnel_rec.end_date_active >= nvl(rec_award_manager.end_date_active, G_personnel_rec.end_date_active)) THEN
3621
3622 add_message_to_stack( P_label => ('GMS_AW_INVALID_MANAGER_DATES') );
3623 set_return_status ( X_return_status, 'B') ;
3624
3625 END IF; -- end if for G_personnel_rec.start_date_active
3626
3627 END LOOP;
3628
3629 END Verify_Award_Manager_Dates ;
3630
3631 -- -------------------------------------------------------------------------------
3632
3633 PROCEDURE check_personnel_required (p_validate IN BOOLEAN ,X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3634
3635 l_error BOOLEAN ;
3636 BEGIN
3637 IF not p_validate then
3638 return ;
3639 end if ;
3640
3641 l_error := FALSE ;
3642
3643 IF G_personnel_rec.award_id IS NULL THEN
3644 l_error := TRUE ;
3645 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
3646 END IF ;
3647
3648 IF G_personnel_rec.person_id IS NULL THEN
3649 l_error := TRUE ;
3650 add_message_to_stack( P_label => 'GMS_PERSON_ID_NULL' ) ;
3651 END IF ;
3652
3653 IF G_personnel_rec.award_role IS NULL THEN
3654 l_error := TRUE ;
3655 add_message_to_stack( P_label => 'GMS_AWD_ROLE_NULL' ) ;
3656 END IF ;
3657
3658
3659 IF l_error THEN
3660 set_return_status ( X_return_status, 'B') ;
3661 END IF ;
3662 END check_personnel_required ;
3663 -- ------------------------------------------------------------------------------------------
3664
3665 PROCEDURE Create_Pa_Key_Member ( p_validate IN BOOLEAN , X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3666
3667 v_null_number NUMBER;
3668 v_null_char VARCHAR2(255);
3669 v_null_date DATE;
3670 x_msg_count NUMBER;
3671 x_msg_data VARCHAR2(255);
3672 x_role_type_id NUMBER;
3673 l_wf_type VARCHAR2(250);
3674 l_wf_item_type VARCHAR2(250);
3675 l_wf_process VARCHAR2(250);
3676 l_assignment_id NUMBER;
3677 l_party_id NUMBER;
3678
3679 BEGIN
3680
3681 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY
3682 ( P_API_VERSION => 1.0
3683 , P_INIT_MSG_LIST => NULL
3684 , P_COMMIT => NULL
3685 , P_VALIDATE_ONLY => NULL
3686 , P_VALIDATION_LEVEL => 100
3687 , P_DEBUG_MODE => 'N'
3688 , P_OBJECT_ID => l_award_project_id
3689 , P_OBJECT_TYPE => 'PA_PROJECTS'
3690 , P_PROJECT_ROLE_ID => 1
3691 , P_PROJECT_ROLE_TYPE => 'PROJECT MANAGER'
3692 , P_RESOURCE_TYPE_ID => 101
3693 , P_RESOURCE_SOURCE_ID => G_personnel_rec.person_id
3694 , P_RESOURCE_NAME => v_null_char
3695 , P_START_DATE_ACTIVE => G_personnel_rec.start_date_active
3696 , P_SCHEDULED_FLAG => 'N'
3697 , P_CALLING_MODULE =>'FORM'
3698 , P_PROJECT_ID => l_award_project_id
3699 , P_PROJECT_END_DATE => l_end_date_active
3700 , P_END_DATE_ACTIVE => G_personnel_rec.end_date_active
3701 , X_PROJECT_PARTY_ID => l_party_id
3702 , X_RESOURCE_ID => v_null_number
3703 , X_WF_TYPE => l_wf_type
3704 , X_WF_ITEM_TYPE => l_wf_item_type
3705 , X_WF_PROCESS => l_wf_process
3706 , X_ASSIGNMENT_ID => l_assignment_id
3707 , X_RETURN_STATUS => x_return_status
3708 , X_MSG_COUNT => x_msg_count
3709 , X_MSG_DATA => x_msg_data
3710 );
3711
3712
3713 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3714 set_return_status ( X_return_status, 'B') ;
3715
3716 END IF ;
3717 END Create_Pa_Key_Member ;
3718 -- -----------------------------------------------------------------------------------------------
3719
3720
3721 BEGIN
3722 -- ----------------------------
3723 -- Initialize the message stack.
3724 -- -----------------------------
3725 init_message_stack;
3726
3727 G_msg_count := x_msg_count ;
3728 G_msg_data := x_MSG_DATA ;
3729 G_calling_module := P_CALLING_MODULE ;
3730
3731 -- =============================
3732 -- Initialize the return status.
3733 -- =============================
3734 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) NOT IN
3735 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
3736 X_return_status := FND_API.G_RET_STS_SUCCESS ;
3737 END IF ;
3738
3739 SAVEPOINT create_personnel_pvt ;
3740
3741 G_stage := 'FND_API.Compatible_API_Call' ;
3742
3743 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3744 p_api_version_number ,
3745 l_api_name ,
3746 G_pkg_name )
3747 THEN
3748 RAISE e_ver_mismatch ;
3749 END IF ;
3750
3751 G_personnel_rec := P_personnel_rec ;
3752
3753 G_stage := 'proc_check_required' ;
3754
3755 check_personnel_required (p_validate , X_return_status ) ;
3756
3757 IF p_validate THEN
3758
3759 OPEN l_full_name_csr(G_personnel_rec.person_id );
3760 FETCH l_full_name_csr into l_dummy ;
3761
3762 IF l_full_name_csr%NOTFOUND THEN
3763 add_message_to_stack( P_label => 'GMS_AWD_PERSON_ID_INVALID' ) ;
3764 set_return_status ( X_return_status, 'B') ;
3765 END IF ;
3766 CLOSE l_full_name_csr ;
3767
3768 OPEN l_award_role_csr(G_personnel_rec.award_role );
3769 FETCH l_award_role_csr into l_dummy ;
3770
3771 IF l_award_role_csr%NOTFOUND THEN
3772 add_message_to_stack( P_label => 'GMS_AWD_ROLE_INVALID' ) ;
3773 set_return_status ( X_return_status, 'B') ;
3774 END IF ;
3775 CLOSE l_award_role_csr ;
3776 END IF ; -- end if for p_validate .
3777
3778 IF G_personnel_rec.award_id IS NOT NULL THEN
3779 OPEN l_rec_csr(G_personnel_rec.award_id ) ;
3780 FETCH l_rec_csr INTO l_start_date_active,l_budget_wf_enabled_flag ,
3781 l_award_template_flag , l_award_project_id , l_end_date_active ;
3782
3783 IF l_rec_csr%NOTFOUND THEN
3784 add_message_to_stack( P_label => 'GMS_AWD_NOT_EXISTS' ) ;
3785 set_return_status ( X_return_status, 'B') ;
3786 END IF ;
3787 CLOSE l_rec_csr ;
3788 END IF ;
3789
3790 -- =========================================================
3791 -- Make sure that X_return_status is success before continue.
3792 -- =========================================================
3793 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
3794 Raise fnd_api.g_exc_error ;
3795 END IF ;
3796
3797 -- ==================================================================
3798 -- If the Start_Date_Active is NULL for PERSONNEL record then take the
3799 -- Award_Start_Date_Active as the PERSONNEL Start_Date_Active .
3800 -- ==================================================================
3801
3802 If G_personnel_rec.start_date_active IS NULL THEN
3803 G_personnel_rec.start_date_active := l_start_date_active ;
3804 END IF ;
3805
3806 G_stage := 'FND_API.Check_Start_Date_Active' ;
3807 Verify_dates(p_validate , X_return_status ) ;
3808
3809 G_stage := 'FND_API.Verify_Award_Manager_Dates' ;
3810 Verify_Award_Manager_Dates (p_validate , X_return_status) ;
3811
3812 G_stage := 'FND_API.Verify_User_Status' ;
3813 Verify_User_Status(p_validate , X_return_status) ;
3814
3815
3816 IF G_personnel_rec.award_role = 'AM'AND l_award_template_flag = 'DEFERRED' THEN
3817
3818 G_stage := 'FND_API.Create_Pa_Key_Member' ;
3819 Create_Pa_Key_Member( p_validate , X_return_status ) ;
3820 END IF ;
3821
3822 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
3823 RAISE FND_API.G_EXC_ERROR;
3824 END IF ;
3825
3826
3827 SELECT gms_personnel_s.nextval
3828 INTO G_personnel_rec.personnel_id
3829 FROM DUAL ;
3830
3831 G_stage := 'FND_API.Create_Personnel_Record' ;
3832 gms_personnel_pkg.insert_row
3833 ( x_rowid => L_rowid,
3834 x_personnel_id => G_personnel_rec.personnel_id,
3835 x_award_id => G_personnel_rec.award_id,
3836 x_person_id => G_personnel_rec.person_id,
3837 x_award_role => G_personnel_rec.award_role,
3838 x_start_date_active => G_personnel_rec.start_date_active,
3839 x_end_date_active => G_personnel_rec.end_date_active,
3840 x_required_flag => G_personnel_rec.required_flag,
3841 x_mode => 'R'
3842 );
3843
3844 G_stage := 'FND_API.Create_Person_Events' ;
3845 gms_notification_pkg.crt_default_person_events
3846 ( p_award_id => G_personnel_rec.award_id,
3847 p_person_id => G_personnel_rec.person_id,
3848 x_err_code => l_error_code,
3849 x_err_stage => l_error_stage
3850 );
3851 G_personnel_rec := NULL ; -- Resetting the record vaarible.
3852
3853
3854 G_stage := 'Personnel Created Successfully' ;
3855 EXCEPTION
3856 WHEN E_VER_MISMATCH THEN
3857 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
3858 p_token1 => 'SUPVER',
3859 P_VAL1 => g_api_version_number) ;
3860 set_return_status(X_return_status, 'B' ) ;
3861 x_msg_count := G_msg_count ;
3862 x_msg_data := G_msg_data ;
3863
3864 WHEN FND_API.G_EXC_ERROR THEN
3865 G_personnel_rec := NULL ; -- Resetting the record vaarible.
3866 ROLLBACK TO create_personnel_pvt ;
3867 set_return_status(X_return_status, 'B' ) ;
3868 x_msg_count := G_msg_count ;
3869 x_msg_data := G_msg_data ;
3870 WHEN OTHERS THEN
3871 G_personnel_rec := NULL ; -- Resetting the record vaarible.
3872 ROLLBACK TO create_personnel_pvt;
3873 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3874
3875 FND_MSG_PUB.add_exc_msg
3876 ( p_pkg_name => G_PKG_NAME
3877 , p_procedure_name => l_api_name );
3878 FND_MSG_PUB.Count_And_Get
3879 ( p_count => x_msg_count ,
3880 p_data => x_msg_data );
3881
3882
3883
3884 END CREATE_PERSONNEL ;
3885
3886
3887 -- ========================================================================
3888 -- Create Terms and conditions.
3889 -- ========================================================================
3890 PROCEDURE CREATE_TERM_CONDITION
3891 (X_MSG_COUNT IN OUT NOCOPY NUMBER ,
3892 X_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
3893 X_return_status IN OUT NOCOPY VARCHAR2 ,
3894 X_ROW_ID OUT NOCOPY VARCHAR2 ,
3895 P_CALLING_MODULE IN VARCHAR2 ,
3896 P_API_VERSION_NUMBER IN NUMBER ,
3897 P_validate IN BOOLEAN DEFAULT TRUE ,
3898 P_AWARD_TERM_CONDITION_REC IN GMS_AWARDS_TERMS_CONDITIONS%ROWTYPE
3899 ) IS
3900
3901 l_award_start_date_active DATE ;
3902 l_award_end_date_active DATE ;
3903 l_api_name VARCHAR2(30) := 'CREATE_TERM_CONDITION';
3904 l_dummy VARCHAR2(1) ;
3905 l_rowid varchar2(45) ;
3906
3907 -- =======================================================================
3908 -- This is to check the uniqueness of the award_id + term_id + category_id
3909 -- before creating the term and condition .
3910 -- =======================================================================
3911
3912 CURSOR l_check_duplicate_csr IS
3913 SELECT 'X'
3914 FROM gms_awards_terms_conditions
3915 WHERE award_id = G_term_condition_rec.award_id
3916 AND category_id = G_term_condition_rec.category_id
3917 AND term_id = G_term_condition_rec.term_id ;
3918
3919 -- ===========================================================================================
3920 -- This cursor is used to verify whether the incoming category exists or not( LOV validation ).
3921 -- ===========================================================================================
3922
3923 CURSOR l_category_id_csr IS
3924 SELECT 'X'
3925 FROM gms_tc_categories
3926 WHERE category_id = G_term_condition_rec.category_id ;
3927
3928 -- ===========================================================================================
3929 -- This cursor is used to verify whether the incoming term_id exists or not for the specific
3930 -- category_id ( LOV validation ).
3931 -- ===========================================================================================
3932 CURSOR l_term_id_csr IS
3933 SELECT 'X'
3934 FROM gms_terms_conditions tc1
3935 WHERE tc1.category_id = G_term_condition_rec.category_id
3936 and term_id not in (select term_id from gms_terms_conditions tc
3937 where
3938 (
3939 (tc.start_date_active > l_award_start_date_active
3940 and tc.start_date_active > l_award_end_date_active
3941 and tc.start_date_active is not null and tc.end_date_active is not null)
3942 or
3943 (tc.end_date_active < l_award_start_date_active
3944 and tc.end_date_active < l_award_end_date_active
3945 and tc.start_date_active is not null
3946 and tc.end_date_active is not null)
3947 or
3948 ( tc.start_date_active is null
3949 and tc.end_date_active < l_award_start_date_active
3950 and tc.end_date_active is not null)
3951 or
3952 (tc.end_date_active is null
3953 and tc.start_date_active > l_award_end_date_active
3954 and tc.start_date_active is not null)
3955 )
3956 ) ;
3957 -- ========================================================================
3958 -- This cursor is used to retrived the start_date and end_date for an Award.
3959 -- ========================================================================
3960
3961 CURSOR l_award_rec_csr IS
3962 SELECT start_date_active,end_date_active
3963 FROM gms_awards_all
3964 WHERE award_id = G_term_condition_rec.award_id ;
3965
3966 -- =======================================================================================
3967 -- This procedure will verify whehter all the NOT NULL columns have values or not.
3968 -- This needs to be checked here as we don't do any validation in the PUB.
3969 -- If the call is coming from PVT i.e p_validate is FALSE then we don't do this validation.
3970 -- =======================================================================================
3971
3972 PROCEDURE check_term_cond_required (p_validate IN BOOLEAN ,
3973 x_return_status IN OUT NOCOPY VARCHAR2 ) IS
3974 l_error BOOLEAN ;
3975
3976 BEGIN
3977 IF not p_validate then
3978 return ;
3979 end if ;
3980
3981 l_error := FALSE ;
3982
3983 IF G_term_condition_rec.Award_Id IS NULL THEN
3984 l_error := TRUE ;
3985 add_message_to_stack( P_label => 'GMS_AWD_ID_MISSING' ) ;
3986
3987 END IF ;
3988
3989 IF G_term_condition_rec.category_id IS NULL THEN
3990 add_message_to_stack( P_label => 'GMS_TERM_CON_CATEGORY_NULL' ) ;
3991 l_error := TRUE ;
3992 END IF ;
3993 IF G_term_condition_rec.term_id IS NULL THEN
3994 add_message_to_stack( P_label => 'GMS_TERM_ID_NULL' ) ;
3995 l_error := TRUE ;
3996 END IF ;
3997
3998 IF L_error THEN
3999 set_return_status ( X_return_status, 'B') ;
4000 END IF ;
4001 END check_term_cond_required ;
4002 -- --------------------------------------------------------------------------
4003
4004 BEGIN
4005
4006 -- =============================
4007 -- Initialize the message stack.
4008 -- =============================
4009
4010 init_message_stack;
4011
4012 G_msg_count := X_msg_count ;
4013 G_msg_data := X_MSG_DATA ;
4014 G_calling_module := P_CALLING_MODULE ;
4015
4016 -- ============================
4017 -- Initialize the return status.
4018 -- ============================
4019 IF NVL(x_return_status , FND_API.G_RET_STS_SUCCESS) NOT IN
4020 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4021 x_return_status := FND_API.G_RET_STS_SUCCESS ;
4022 END IF ;
4023
4024 SAVEPOINT create_award_term_cond_pvt ;
4025
4026 G_stage := 'FND_API.Compatible_API_Call' ;
4027
4028 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4029 p_api_version_number ,
4030 l_api_name ,
4031 G_pkg_name ) THEN
4032 RAISE e_ver_mismatch ;
4033 END IF ;
4034
4035 G_term_condition_rec := p_award_term_condition_rec ;
4036 G_stage := 'proc_check_required_for_term_condition' ;
4037
4038 check_term_cond_required (p_validate , x_return_status ) ;
4039
4040 IF G_term_condition_rec.award_id IS NOT NULL THEN
4041 OPEN l_award_rec_csr ;
4042 FETCH l_award_rec_csr INTO l_award_start_date_active,l_award_end_date_active ;
4043 IF l_award_rec_csr%NOTFOUND THEN
4044 add_message_to_stack( P_label => 'GMS_AWD_NOT_EXISTS' ) ;
4045 set_return_status ( x_return_status, 'B') ;
4046 END IF ;
4047 CLOSE l_award_rec_csr ;
4048 END IF ;
4049
4050 IF p_validate THEN -- i.e the call is from PUB package .
4051 -- The following are all LOV validations.
4052 OPEN l_category_id_csr ;
4053 FETCH l_category_id_csr INTO l_dummy ;
4054 IF l_category_id_csr%NOTFOUND THEN
4055 add_message_to_stack( P_label => 'GMS_AWD_CATEGORY_NAME_INVALID' ) ;
4056 set_return_status ( x_return_status, 'B') ;
4057 END IF ;
4058 CLOSE l_category_id_csr ;
4059
4060 OPEN l_term_id_csr ;
4061 FETCH l_term_id_csr INTO l_dummy ;
4062 IF l_term_id_csr%NOTFOUND THEN
4063 add_message_to_stack( P_label => 'GMS_TERM_NAME_INVALID' ) ;
4064 set_return_status ( x_return_status, 'B') ;
4065 END IF ;
4066 CLOSE l_term_id_csr ;
4067 -- =============================================================================
4068 -- This is to check the uniqueness of the award_id + term_id + category_id before
4069 -- creating the term and condition .
4070 -- =============================================================================
4071
4072 OPEN l_check_duplicate_csr ;
4073 FETCH l_check_duplicate_csr INTO l_dummy ;
4074 IF l_check_duplicate_csr%FOUND THEN
4075 add_message_to_stack( P_label => 'GMS_AWARD_TERM_CATEGORY_DUP' ) ;
4076 set_return_status ( x_return_status, 'B') ;
4077 END IF ;
4078 CLOSE l_check_duplicate_csr ;
4079
4080 END IF ; -- end if for p_validate.
4081
4082 IF NVL(x_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
4083 RAISE fnd_api.g_exc_error ;
4084 END IF ; -- end if for nvl(x_return_status) .
4085
4086 G_stage := 'FND_API.Creating_Award_term_condition_record ' ;
4087 gms_awards_tc_pkg.insert_row
4088 ( X_ROWID => l_rowid,
4089 X_AWARD_ID => G_term_condition_rec.award_id,
4090 X_CATEGORY_ID => G_term_condition_rec.category_id,
4091 X_TERM_ID => G_term_condition_rec.term_id,
4092 X_OPERAND => G_term_condition_rec.operand,
4093 X_VALUE => G_term_condition_rec.value,
4094 X_MODE => 'R'
4095 ) ;
4096 G_term_condition_rec := NULL ; -- Resetting the record vaarible.
4097
4098 G_stage := 'FND_API.Succefully_created_award_term_condition' ;
4099
4100
4101 EXCEPTION
4102 WHEN E_VER_MISMATCH THEN
4103 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
4104 p_token1 => 'SUPVER',
4105 P_VAL1 => g_api_version_number) ;
4106 set_return_status(x_return_status, 'B' ) ;
4107 X_msg_count := G_msg_count ;
4108 X_msg_data := G_msg_data ;
4109
4110 WHEN FND_API.G_EXC_ERROR THEN
4111 G_term_condition_rec := NULL ; -- Resetting the record vaarible.
4112 ROLLBACK TO create_award_term_cond_pvt ;
4113 set_return_status(x_return_status, 'B' ) ;
4114 X_msg_count := G_msg_count ;
4115 X_msg_data := G_msg_data ;
4116 WHEN OTHERS THEN
4117 G_term_condition_rec := NULL ; -- Resetting the record vaarible.
4118 ROLLBACK TO create_award_term_cond_pvt ;
4119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4120
4121 FND_MSG_PUB.add_exc_msg
4122 ( p_pkg_name => G_PKG_NAME
4123 , p_procedure_name => l_api_name );
4124 FND_MSG_PUB.Count_And_Get
4125 ( p_count => X_msg_count ,
4126 p_data => X_msg_data );
4127
4128
4129 end CREATE_TERM_CONDITION ;
4130
4131 -- ========================================================================
4132 -- This procedure will create references as needed for each Award withe
4133 -- effective Start Date and End Dates .
4134 -- ========================================================================
4135 PROCEDURE CREATE_REFERENCE_NUMBER
4136 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
4137 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
4138 X_return_status IN OUT NOCOPY VARCHAR2 ,
4139 X_ROW_ID OUT NOCOPY VARCHAR2 ,
4140 P_CALLING_MODULE IN VARCHAR2 ,
4141 P_API_VERSION_NUMBER IN NUMBER ,
4142 P_validate IN BOOLEAN DEFAULT TRUE ,
4143 P_REFERENCE_NUMBER_REC IN GMS_REFERENCE_NUMBERS%ROWTYPE
4144 ) IS
4145
4146 -- This Cursor is used to validate the incoming TYPE parameter
4147 -- with the lookup_code. It it doesn't exist then we raise an error
4148 -- and exit the procedure .
4149
4150 CURSOR l_lookup_csr IS
4151 SELECT 'X'
4152 FROM gms_lookups
4153 WHERE lookup_type = 'REFERENCE_NUMBER'
4154 AND lookup_code = G_reference_number_rec.type ;
4155
4156 -- ==============================================================================
4157 -- This is to check whether the award_id and reference_type combination is unique .
4158 -- Here reference_type corresponds to the Lookup_code in the gms_looups table.
4159 -- ==============================================================================
4160
4161 CURSOR l_duplicate_ref_type IS
4162 SELECT 'X'
4163 FROM gms_reference_numbers
4164 WHERE award_id = G_reference_number_rec.award_id
4165 AND type = G_reference_number_rec.type ;
4166
4167 l_api_name VARCHAR2(30) := 'CREATE_PERSONNEL';
4168 l_dummy VARCHAR2(1) ;
4169 l_rowid varchar2(45) ;
4170 -- ========================================================================
4171 -- This procedure will check all the required values .
4172 -- ========================================================================
4173
4174 PROCEDURE check_reference_required (p_validate IN BOOLEAN ,
4175 X_return_status IN OUT NOCOPY VARCHAR2 ) IS
4176 l_error BOOLEAN ;
4177
4178 BEGIN
4179 IF not p_validate then
4180 return ;
4181 end if ;
4182
4183 l_error := FALSE ;
4184
4185 IF G_reference_number_rec.Award_Id IS NULL THEN
4186 l_error := TRUE ;
4187 add_message_to_stack( P_label => 'GMS_AWD_ID_MISSING' ) ;
4188
4189 END IF ;
4190
4191 IF g_reference_number_rec.type IS NULL THEN
4192 add_message_to_stack( P_label => 'GMS_REF_TYPE_NULL' ) ;
4193 l_error := TRUE ;
4194 END IF ;
4195
4196 IF L_error THEN
4197 set_return_status ( X_return_status, 'B') ;
4198 END IF ;
4199 END check_reference_required ;
4200 -- -------------------------------------
4201
4202 BEGIN
4203
4204 -- Initialize the message stack.
4205 -- -----------------------------
4206 init_message_stack;
4207
4208 G_msg_count := x_msg_count ;
4209 G_msg_data := x_MSG_DATA ;
4210 G_calling_module := P_CALLING_MODULE ;
4211
4212 -- =============================
4213 -- Initialize the return status.
4214 -- =============================
4215 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) NOT IN
4216 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4217 X_return_status := FND_API.G_RET_STS_SUCCESS ;
4218 END IF ;
4219
4220 SAVEPOINT create_reference_number_pvt ;
4221
4222 G_stage := 'FND_API.Compatible_API_Call' ;
4223
4224 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4225 p_api_version_number ,
4226 l_api_name ,
4227 G_pkg_name ) THEN
4228 RAISE e_ver_mismatch ;
4229 END IF ;
4230
4231 G_reference_number_rec := p_reference_number_rec ;
4232
4233 G_stage := 'proc_check_required' ;
4234 check_reference_required (p_validate , X_return_status ) ;
4235
4236 IF p_validate THEN -- i.e the call is from PUB package .
4237 -- Following are all LOV validations.
4238
4239 OPEN l_lookup_csr ;
4240 FETCH l_lookup_csr INTO l_dummy ;
4241 IF l_lookup_csr%NOTFOUND THEN
4242 add_message_to_stack( P_label => 'GMS_AWD_INVALID_REFERENCE_TYPE' ) ;
4243 set_return_status ( X_return_status, 'B') ;
4244 END IF ;
4245 CLOSE l_lookup_csr ;
4246
4247 -- ==============================================================================
4248 -- This is to check whether the award_id and reference_type combination is unique .
4249 -- ==============================================================================
4250
4251 OPEN l_duplicate_ref_type ;
4252 FETCH l_duplicate_ref_type INTO l_dummy ;
4253 IF l_duplicate_ref_type%FOUND THEN
4254 add_message_to_stack( P_label => 'GMS_DUP_REFERENCE_TYPE' ) ;
4255 set_return_status ( X_return_status, 'B') ;
4256 END IF ;
4257 CLOSE l_duplicate_ref_type ;
4258 END IF ; -- end if for p_validate.
4259
4260 -- ==============================================================================
4261 -- Here we check whether the return_staus <> FND_API.G_RET_STS_SUCCESS because of
4262 -- any above validations . If it is that means some error has happened and we don't
4263 -- insert the record but the control go to exception section.
4264 -- ==============================================================================
4265
4266 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
4267 RAISE fnd_api.g_exc_error ;
4268 END IF ; -- end if for nvl(X_return_status) .
4269
4270 -- Creating the Reference_number Record .
4271
4272 G_stage := 'FND_API.Creating_reference_number' ;
4273 gms_reference_numbers_pkg.insert_row
4274 ( x_rowid => l_rowid,
4275 x_award_id => G_reference_number_rec.award_id,
4276 x_type => G_reference_number_rec.type,
4277 x_value => G_reference_number_rec.value,
4278 x_required_flag => G_reference_number_rec.required_flag,
4279 x_mode => 'R'
4280 );
4281 G_reference_number_rec := NULL ; -- Resetting the record vaarible.
4282
4283
4284 G_stage := 'FND_API.Succefully_created_reference_number ' ;
4285
4286 EXCEPTION
4287 WHEN E_VER_MISMATCH THEN
4288 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
4289 p_token1 => 'SUPVER',
4290 P_VAL1 => g_api_version_number) ;
4291 set_return_status(X_return_status, 'B' ) ;
4292 x_msg_count := G_msg_count ;
4293 x_msg_data := G_msg_data ;
4294
4295 WHEN FND_API.G_EXC_ERROR THEN
4296 G_reference_number_rec := NULL ; -- Resetting the record vaarible.
4297 ROLLBACK TO create_reference_number_pvt ;
4298 set_return_status(X_return_status, 'B' ) ;
4299 x_msg_count := G_msg_count ;
4300 x_msg_data := G_msg_data ;
4301 WHEN OTHERS THEN
4302 G_reference_number_rec := NULL ; -- Resetting the record vaarible.
4303 ROLLBACK TO create_reference_number_pvt ;
4304 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4305
4306 FND_MSG_PUB.add_exc_msg
4307 ( p_pkg_name => G_PKG_NAME
4308 , p_procedure_name => l_api_name );
4309 FND_MSG_PUB.Count_And_Get
4310 ( p_count => x_msg_count ,
4311 p_data => x_msg_data );
4312
4313 END CREATE_REFERENCE_NUMBER ;
4314
4315 -- ========================================================================
4316 -- Create Contact
4317 -- ========================================================================
4318
4319 PROCEDURE CREATE_CONTACT
4320 (X_MSG_COUNT IN OUT NOCOPY NUMBER ,
4321 X_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
4322 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2 ,
4323 X_ROW_ID OUT NOCOPY VARCHAR2 ,
4324 P_CALLING_MODULE IN VARCHAR2 ,
4325 P_API_VERSION_NUMBER IN NUMBER ,
4326 P_VALIDATE IN BOOLEAN default TRUE,
4327 P_CONTACT_REC IN GMS_AWARDS_CONTACTS%ROWTYPE
4328 )
4329 IS
4330 --TCA enhancement : Changing RA tables with HZ tables
4331 CURSOR l_valid_award_csr IS
4332 SELECT award_id,award_project_id
4333 FROM gms_awards_all
4334 WHERE award_id = G_contact_rec.award_id;
4335
4336 CURSOR l_valid_contact_csr IS
4337 SELECT 'X'
4338 FROM gms_awards_all ga,
4339 Hz_cust_account_roles acct_roles
4340 WHERE ga.award_id = G_contact_rec.award_id
4341 AND decode(ga.billing_format,'LOC',ga.bill_to_customer_id,ga.funding_source_id)=acct_roles.cust_account_id
4342 AND acct_roles.cust_account_role_id = G_contact_rec.contact_id;
4343
4344
4345 CURSOR l_valid_usage_csr IS
4346 SELECT 'X'
4347 FROM hz_cust_site_uses a,
4348 Hz_cust_acct_sites b,
4349 ar_lookups c,
4350 gms_awards_all ga
4351 WHERE a.cust_acct_site_id = b.cust_acct_site_id
4352 AND b.cust_account_id = decode(ga.billing_format,'LOC',ga.bill_to_customer_id,ga.funding_source_id)
4353 AND c.lookup_type = 'SITE_USE_CODE'
4354 AND c.lookup_code = g_contact_rec.usage_code;
4355
4356 CURSOR l_dup_usage_csr IS
4357 SELECT 'X'
4358 FROM gms_awards_contacts
4359 WHERE award_id = G_contact_rec.award_id
4360 AND contact_id = G_contact_rec.contact_id -- Bug 2672027
4361 AND customer_id = G_contact_rec.customer_id
4362 AND usage_code = G_contact_rec.usage_code;
4363
4364 l_api_name varchar2(30) := 'CREATE_CONTACT' ;
4365 l_award_project_id NUMBER ;
4366 l_award_id NUMBER ;
4367 l_rowid VARCHAR2(45) ;
4368 l_contact VARCHAR2(1);
4369 l_usage VARCHAR2(1);
4370 l_dup_usage VARCHAR2(1);
4371
4372 BEGIN
4373
4374 -- =============================
4375 -- Initialize the Message Stack.
4376 -- =============================
4377
4378 init_message_stack;
4379
4380 G_msg_count := x_msg_count ;
4381 G_msg_data := x_MSG_DATA ;
4382 G_calling_module := P_CALLING_MODULE ;
4383
4384 -- =============================
4385 -- Initialize the return status.
4386 -- =============================
4387
4388 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
4389 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4390 X_return_status := FND_API.G_RET_STS_SUCCESS ;
4391 END IF ;
4392
4393 -- =========================
4394 -- Establish the Save Point.
4395 -- =========================
4396
4397 SAVEPOINT create_contact_pvt ;
4398
4399 -- ==============================================================
4400 -- Compare the caller version number to the API version number in
4401 -- order to detect incompatible API calls.
4402 -- ==============================================================
4403
4404 G_stage := 'FND_API.Compatible_API_Call' ;
4405
4406 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4407 p_api_version_number ,
4408 l_api_name ,
4409 G_pkg_name )
4410 THEN
4411 RAISE e_ver_mismatch ;
4412 END IF ;
4413
4414 -- =============================================
4415 -- Check for required columns for Create Contact
4416 -- =============================================
4417
4418 G_stage := 'procedure_check_contact_required_columns' ;
4419
4420 G_contact_rec := P_contact_rec;
4421
4422 IF G_contact_rec.award_id IS NULL THEN
4423 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
4424 set_return_status ( X_return_status, 'B') ;
4425 END IF ;
4426
4427 IF G_contact_rec.contact_id IS NULL THEN
4428 add_message_to_stack( P_label => 'GMS_CON_CONTACT_ID_NULL' ) ;
4429 set_return_status ( X_return_status, 'B') ;
4430 END IF ;
4431
4432 IF G_contact_rec.customer_id IS NULL THEN
4433 add_message_to_stack( P_label => 'GMS_CON_CUSTOMER_ID_NULL' ) ;
4434 set_return_status ( X_return_status, 'B') ;
4435 END IF ;
4436
4437 IF G_contact_rec.usage_code IS NULL THEN
4438 add_message_to_stack( P_label => 'GMS_CON_USAGE_CODE_NULL' ) ;
4439 set_return_status ( X_return_status, 'B') ;
4440 END IF ;
4441
4442 IF G_contact_rec.primary_flag IS NULL THEN
4443 add_message_to_stack( P_label => 'GMS_CON_PRIMARY_FLAG_NULL' ) ;
4444 set_return_status ( X_return_status, 'B') ;
4445 END IF ;
4446
4447 IF G_contact_rec.primary_flag NOT IN ('Y','N') THEN
4448 add_message_to_stack( P_label => 'GMS_CON_INVALID_PRIMARY_FLAG' ) ;
4449 set_return_status ( X_return_status, 'B') ;
4450 END IF ;
4451
4452 -- ===================================================================
4453 -- Need to make sure that the return status is success from the above
4454 -- validations. There is no point in doing further validations as the
4455 -- required columns donot have values. So we raise an Error.
4456 -- ===================================================================
4457
4458 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4459 RAISE FND_API.G_EXC_ERROR;
4460 END IF ;
4461
4462 -- ======================================================================
4463 -- Verify the contacts for validity by cross checking with funding source
4464 -- ======================================================================
4465
4466 G_stage := 'create_contact.Verify_Award';
4467
4468 OPEN l_valid_award_csr ;
4469 FETCH l_valid_award_csr INTO l_award_id,l_award_project_id;
4470
4471 IF l_award_id IS NULL THEN
4472 add_message_to_stack( P_label => 'GMS_FND_INVALID_AWARD' ) ;
4473 set_return_status ( X_return_status, 'B') ;
4474 END IF;
4475
4476 CLOSE l_valid_award_csr ;
4477
4478 G_stage := 'create_contact.Verify_Contact;' ;
4479
4480 OPEN l_valid_contact_csr;
4481 FETCH l_valid_contact_csr INTO l_contact;
4482
4483 IF l_valid_contact_csr%NOTFOUND THEN
4484 add_message_to_stack( P_label => 'GMS_CON_CONTACT_INVLAID' ) ;
4485 set_return_status ( X_return_status, 'B') ;
4486 END IF;
4487
4488 CLOSE l_valid_contact_csr;
4489
4490 -- ============================================================
4491 -- Verify the usage for validity by cross checking with lookups
4492 -- ===========================================================
4493
4494 G_stage := 'create_contact.Verify_Usage;' ;
4495
4496 OPEN l_valid_usage_csr;
4497 FETCH l_valid_usage_csr INTO l_usage;
4498
4499 IF l_valid_usage_csr%NOTFOUND THEN
4500 add_message_to_stack( P_label => 'GMS_CON_USAGE_INVALID' ) ;
4501 set_return_status ( X_return_status, 'B') ;
4502 END IF;
4503
4504 CLOSE l_valid_usage_csr;
4505
4506 -- ============================================================================
4507 -- Verify the duplicate usage of contacts for a given award, customer and usage.
4508 -- ============================================================================
4509
4510 G_stage := 'create_contact.Verify_Duplicate_Usage;' ;
4511
4512 OPEN l_dup_usage_csr ;
4513 FETCH l_dup_usage_csr INTO l_dup_usage;
4514
4515 IF l_dup_usage_csr%FOUND THEN
4516 add_message_to_stack( P_label => 'GMS_CON_AWD_USAGE_DUP' ) ;
4517 set_return_status ( X_return_status, 'B') ;
4518 END IF;
4519
4520 CLOSE l_dup_usage_csr ;
4521
4522
4523 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4524 RAISE FND_API.G_EXC_ERROR;
4525 END IF ;
4526
4527
4528 -- ===========================================================================================
4529 -- Updating pa_project_contacts for Award Project customer id for primary bill to and ship to
4530 -- ===========================================================================================
4531
4532 IF (G_contact_rec.primary_flag = 'Y') THEN
4533 IF (G_contact_rec.usage_code = 'BILL_TO') or (G_contact_rec.usage_code = 'SHIP_TO' ) THEN
4534 IF l_award_project_id IS NOT NULL THEN
4535
4536 UPDATE pa_project_contacts
4537 SET contact_id = DECODE(project_contact_type_code,
4538 'BILLING', (G_contact_rec.Contact_id),
4539 'SHIPPING',(G_contact_rec.Contact_id),
4540 contact_id ),
4541 last_update_date = SYSDATE,
4542 last_updated_by = fnd_global.user_id,
4543 last_update_login = fnd_global.login_id
4544 WHERE project_id = l_award_project_id
4545 AND customer_id = G_contact_rec.customer_id;
4546 END IF;
4547 END IF;
4548 END IF;
4549
4550 -- ========================================
4551 -- Calling Table Handler to Insert the Row.
4552 -- ========================================
4553
4554 G_stage := 'gms_awards_contacts_pkg.insert_row' ;
4555
4556 gms_awards_contacts_pkg.insert_row
4557 ( x_rowid => L_rowid,
4558 x_award_id => G_contact_rec.award_id,
4559 x_customer_id => G_contact_rec.customer_id,
4560 x_contact_id => G_contact_rec.contact_id,
4561 x_mode => 'R',
4562 x_primary_flag => G_contact_rec.primary_flag,
4563 x_usage_code => G_contact_rec.usage_code
4564 );
4565
4566 G_stage := 'contact Created Successfully' ;
4567
4568 EXCEPTION
4569
4570 WHEN E_VER_MISMATCH THEN
4571 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
4572 p_token1 => 'SUPVER',
4573 P_VAL1 => g_api_version_number) ;
4574 set_return_status(X_return_status, 'B' ) ;
4575 x_msg_count := G_msg_count ;
4576 x_msg_data := G_msg_data ;
4577
4578 WHEN FND_API.G_EXC_ERROR THEN
4579 ROLLBACK TO create_contact_pvt ;
4580 set_return_status(X_return_status, 'B' ) ;
4581 x_msg_count := G_msg_count ;
4582 x_msg_data := G_msg_data ;
4583 WHEN OTHERS THEN
4584 ROLLBACK TO create_contact_pvt;
4585 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4586
4587 FND_MSG_PUB.add_exc_msg
4588 ( p_pkg_name => G_PKG_NAME
4589 , p_procedure_name => l_api_name );
4590 FND_MSG_PUB.Count_And_Get
4591 ( p_count => x_msg_count ,
4592 p_data => x_msg_data );
4593
4594 END CREATE_CONTACT ;
4595
4596 -- ========================================================================
4597 -- Create Report
4598 -- ========================================================================
4599
4600 PROCEDURE CREATE_REPORT
4601 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
4602 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
4603 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2 ,
4604 X_DEFAULT_REPORT_ID IN OUT NOCOPY NUMBER ,
4605 X_ROW_ID OUT NOCOPY VARCHAR2 ,
4606 P_CALLING_MODULE IN VARCHAR2 ,
4607 P_API_VERSION_NUMBER IN NUMBER ,
4608 P_VALIDATE IN BOOLEAN default TRUE,
4609 P_REPORT_REC IN GMS_DEFAULT_REPORTS%ROWTYPE
4610 )
4611 IS
4612
4613 CURSOR l_dup_reports_csr IS
4614 SELECT 'X'
4615 FROM gms_default_reports
4616 WHERE award_id = G_report_rec.award_id
4617 AND report_template_id = G_report_rec.report_template_id;
4618
4619 CURSOR l_valid_frequency_csr IS
4620 SELECT 'X'
4621 FROM gms_lookups
4622 WHERE lookup_type = 'REPORT_FREQUENCY'
4623 AND lookup_code = G_report_rec.frequency;
4624
4625 CURSOR l_valid_site_code_csr IS
4626 SELECT 'X'
4627 FROM hz_cust_site_uses a,
4628 Hz_cust_acct_sites b,
4629 ar_lookups c,
4630 gms_awards_all d
4631 WHERE a.cust_acct_site_id = b. cust_acct_site_id
4632 AND b. cust_account_id = d.funding_source_id
4633 AND d.award_id = G_report_rec.award_id
4634 AND c.lookup_type = 'SITE_USE_CODE'
4635 AND c.lookup_code = a.site_use_code
4636 AND a.site_use_id = G_report_rec.site_use_id;
4637
4638 l_api_name VARCHAR2(30) := 'CREATE_REPORT' ;
4639 l_error_code VARCHAR2(2000) ;
4640 l_error_stage VARCHAR2(2000) ;
4641 l_rowid VARCHAR2(45) ;
4642 l_dup_report VARCHAR2(1);
4643 l_valid_frequency VARCHAR2(1);
4644 l_valid_site_code VARCHAR2(1);
4645
4646
4647 BEGIN
4648
4649 -- =============================
4650 -- Initialize the Message Stack.
4651 -- =============================
4652
4653 init_message_stack;
4654
4655 G_msg_count := x_msg_count ;
4656 G_msg_data := x_MSG_DATA ;
4657 G_calling_module := P_CALLING_MODULE ;
4658
4659 -- ============================
4660 -- Initialize the return status.
4661 -- ============================
4662
4663 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
4664 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4665 X_return_status := FND_API.G_RET_STS_SUCCESS ;
4666 END IF ;
4667
4668 -- =========================
4669 -- Establish the Save Point.
4670 -- =========================
4671
4672 SAVEPOINT create_report_pvt ;
4673
4674 -- ==============================================================
4675 -- Compare the caller version number to the API version number in
4676 -- order to detect incompatible API calls.
4677 -- ==============================================================
4678
4679 G_stage := 'FND_API.Compatible_API_Call' ;
4680
4681 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4682 p_api_version_number ,
4683 l_api_name ,
4684 G_pkg_name )
4685 THEN
4686 RAISE e_ver_mismatch ;
4687 END IF ;
4688
4689 -- ============================================
4690 -- Check for required columns for Create Report
4691 -- ============================================
4692
4693 G_stage := 'procedure_check_report_required_columns' ;
4694
4695 G_report_rec := P_report_rec;
4696
4697 IF G_report_rec.award_id IS NULL THEN
4698 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
4699 set_return_status ( X_return_status, 'B') ;
4700 END IF ;
4701
4702 IF G_report_rec.report_template_id IS NULL THEN
4703 add_message_to_stack( P_label => 'GMS_REPORT_ID_NULL' ) ;
4704 set_return_status ( X_return_status, 'B') ;
4705 END IF ;
4706
4707 IF G_report_rec.frequency IS NULL THEN
4708 add_message_to_stack( P_label => 'GMS_REP_FREQUENCY_NULL' ) ;
4709 set_return_status ( X_return_status, 'B') ;
4710 END IF ;
4711
4712 IF G_report_rec.due_within_days IS NULL THEN
4713 add_message_to_stack( P_label => 'GMS_REP_DUE_DAYS_NULL');
4714 set_return_status ( X_return_status, 'B') ;
4715 END IF ;
4716
4717 IF (G_report_rec.due_within_days < 0) THEN
4718 add_message_to_stack( P_label => 'GMS_REP_DUE_DAYS_NEG');
4719 set_return_status ( X_return_status, 'B') ;
4720 END IF ;
4721
4722 IF G_report_rec.copy_number IS NULL THEN
4723 add_message_to_stack( P_label => 'GMS_REP_COPY_NUM_NULL' ) ;
4724 set_return_status ( X_return_status, 'B') ;
4725 END IF ;
4726
4727 IF (G_report_rec.copy_number < 0) THEN
4728 add_message_to_stack( P_label => 'GMS_REP_COPY_NUM_NEG');
4729 set_return_status ( X_return_status, 'B') ;
4730 END IF ;
4731
4732 -- ===================================================================
4733 -- Need to make sure that the return status is success from the above
4734 -- validations. There is no point in doing further validations as the
4735 -- required columns donot have values. So we raise an Error.
4736 -- ===================================================================
4737
4738 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4739 RAISE FND_API.G_EXC_ERROR;
4740 END IF ;
4741
4742 -- =================================
4743 -- Verify the frequency for validity
4744 -- =================================
4745
4746 G_stage := 'create_report.Verify_frequency' ;
4747
4748 OPEN l_valid_frequency_csr ;
4749 FETCH l_valid_frequency_csr INTO l_valid_frequency;
4750
4751 IF l_valid_frequency_csr%NOTFOUND THEN
4752 add_message_to_stack( P_label => 'GMS_REP_FREQUENCY_INVALID' ) ;
4753 set_return_status ( X_return_status, 'B') ;
4754 END IF;
4755
4756 CLOSE l_valid_frequency_csr ;
4757
4758
4759 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4760 RAISE FND_API.G_EXC_ERROR;
4761 END IF ;
4762
4763 -- =====================================
4764 -- Verify the Site Use Code for validity
4765 -- =====================================
4766
4767 G_stage := 'create_report.Verify_site_code';
4768
4769 IF G_report_rec.site_use_id IS NOT NULL THEN
4770
4771 OPEN l_valid_site_code_csr ;
4772 FETCH l_valid_site_code_csr INTO l_valid_site_code;
4773
4774 IF l_valid_site_code_csr%NOTFOUND THEN
4775 add_message_to_stack( P_label => 'GMS_REP_SITE_USE_ID_INVALID' ) ;
4776 set_return_status ( X_return_status, 'B') ;
4777 END IF;
4778
4779 CLOSE l_valid_site_code_csr ;
4780
4781 END IF;
4782
4783 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4784 RAISE FND_API.G_EXC_ERROR;
4785 END IF ;
4786
4787 -- ==============================================
4788 -- Verify the duplicate reports for a given award
4789 -- ==============================================
4790
4791 G_stage := 'create_report.Verify_duplicate_reports' ;
4792
4793 OPEN l_dup_reports_csr ;
4794 FETCH l_dup_reports_csr INTO l_dup_report;
4795
4796 IF l_dup_reports_csr%FOUND THEN
4797 add_message_to_stack( P_label => 'GMS_REP_AWD_REPORT_DUP' ) ;
4798 set_return_status ( X_return_status, 'B') ;
4799 END IF;
4800
4801 CLOSE l_dup_reports_csr ;
4802
4803
4804 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4805 RAISE FND_API.G_EXC_ERROR;
4806 END IF ;
4807
4808 -- ==================================================
4809 -- Sequence number is required for default report id.
4810 -- ==================================================
4811
4812 G_stage := 'gms_default_reports_pkg.insert_row' ;
4813
4814 SELECT gms_default_reports_s.nextval
4815 INTO G_report_rec.default_report_id
4816 FROM dual ;
4817
4818 -- ========================================
4819 -- Calling Table Handler to Insert the Row.
4820 -- ========================================
4821
4822 gms_default_reports_pkg.insert_row(
4823 X_ROWID => l_rowid,
4824 X_DEFAULT_REPORT_ID => G_report_rec.default_report_id,
4825 X_REPORT_TEMPLATE_ID => G_report_rec.report_template_id,
4826 X_AWARD_ID => G_report_rec.award_id,
4827 X_FREQUENCY => G_report_rec.frequency,
4828 X_DUE_WITHIN_DAYS => G_report_rec.due_within_days,
4829 X_SITE_USE_ID => G_report_rec.site_use_id,
4830 X_COPY_NUMBER => G_report_rec.copy_number,
4831 X_ATTRIBUTE_CATEGORY => '',
4832 X_ATTRIBUTE1 => '',
4833 X_ATTRIBUTE2 => '',
4834 X_ATTRIBUTE3 => '',
4835 X_ATTRIBUTE4 => '',
4836 X_ATTRIBUTE5 => '',
4837 X_ATTRIBUTE6 => '',
4838 X_ATTRIBUTE7 => '',
4839 X_ATTRIBUTE8 => '',
4840 X_ATTRIBUTE9 => '',
4841 X_ATTRIBUTE10 => '',
4842 X_ATTRIBUTE11 => '',
4843 X_ATTRIBUTE12 => '',
4844 X_ATTRIBUTE13 => '',
4845 X_ATTRIBUTE14 => '',
4846 X_ATTRIBUTE15 => '',
4847 X_MODE => 'R'
4848 );
4849
4850 -- =====================================
4851 -- Creating Notification for the report.
4852 -- =====================================
4853
4854 G_stage := 'gms_notification_pkg.crt_default_report_events';
4855
4856 -- ========================================
4857 -- Calling Table Handler to Insert the Row.
4858 -- ========================================
4859
4860 gms_notification_pkg.crt_default_report_events(
4861 P_AWARD_ID => G_report_rec.award_id,
4862 P_REPORT_TEMPLATE_ID => G_report_rec.report_template_id,
4863 x_err_code =>l_error_code,
4864 x_err_stage =>l_error_stage
4865 );
4866
4867 IF l_error_code <> 0 THEN
4868
4869 -- ==========================================================================================
4870 -- Using l_error_stage returned by the above call,so as not to mask up the returned message.
4871 -- ==========================================================================================
4872
4873 add_message_to_stack( P_label => l_error_stage );
4874 set_return_status ( X_return_status, 'B') ;
4875
4876 END IF;
4877
4878 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4879 RAISE FND_API.G_EXC_ERROR;
4880 END IF ;
4881
4882 X_DEFAULT_REPORT_ID := G_report_rec.default_report_id ;
4883 G_stage := 'Report Created Successfully' ;
4884
4885 EXCEPTION
4886
4887 WHEN E_VER_MISMATCH THEN
4888 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
4889 p_token1 => 'SUPVER',
4890 P_VAL1 => g_api_version_number) ;
4891 set_return_status(X_return_status, 'B' ) ;
4892 x_msg_count := G_msg_count ;
4893 x_msg_data := G_msg_data ;
4894
4895 WHEN FND_API.G_EXC_ERROR THEN
4896 ROLLBACK TO create_report_pvt ;
4897 set_return_status(X_return_status, 'B' ) ;
4898 x_msg_count := G_msg_count ;
4899 x_msg_data := G_msg_data ;
4900 WHEN OTHERS THEN
4901 ROLLBACK TO create_report_pvt;
4902 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4903
4904 FND_MSG_PUB.add_exc_msg
4905 ( p_pkg_name => G_PKG_NAME
4906 , p_procedure_name => l_api_name );
4907 FND_MSG_PUB.Count_And_Get
4908 ( p_count => x_msg_count ,
4909 p_data => x_msg_data );
4910
4911 end CREATE_REPORT ;
4912
4913 -- ========================================================================
4914 -- Create Notification
4915 -- ========================================================================
4916
4917 PROCEDURE CREATE_NOTIFICATION
4918 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
4919 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
4920 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2 ,
4921 X_ROW_ID OUT NOCOPY VARCHAR2 ,
4922 P_CALLING_MODULE IN VARCHAR2 ,
4923 P_API_VERSION_NUMBER IN NUMBER ,
4924 P_VALIDATE IN BOOLEAN default TRUE,
4925 P_NOTIFICATION_REC IN GMS_NOTIFICATIONS%ROWTYPE
4926 )
4927 IS
4928
4929 CURSOR l_dup_event_csr IS
4930 SELECT 'X'
4931 FROM gms_notifications
4932 WHERE award_id = G_notification_rec.award_id
4933 AND event_type = G_notification_rec.event_type
4934 AND user_id = G_notification_rec.user_id;
4935
4936 CURSOR l_default_report_csr(x_report_template_id NUMBER) IS
4937 SELECT 'X'
4938 FROM gms_default_reports
4939 WHERE award_id = G_notification_rec.award_id
4940 AND report_template_id = x_report_template_id;
4941
4942 CURSOR l_report_csr(x_report_template_id NUMBER) IS
4943 SELECT 'X'
4944 FROM gms_reports gr,
4945 gms_installments gi
4946 WHERE gi.award_id = G_notification_rec.award_id
4947 AND gi.installment_id = gr.installment_id
4948 AND gr.report_template_id = x_report_template_id;
4949
4950 l_api_name varchar2(30) := 'CREATE_NOTIFICATION' ;
4951 l_error_code VARCHAR2(2000) ;
4952 l_error_stage VARCHAR2(2000) ;
4953 l_rowid varchar2(45) ;
4954 l_dup_event VARCHAR2(1);
4955 l_default_report VARCHAR2(1);
4956 l_report VARCHAR2(1);
4957 l_report_template_id NUMBER;
4958 l_length NUMBER;
4959
4960 BEGIN
4961 init_message_stack;
4962
4963 G_msg_count := x_msg_count ;
4964 G_msg_data := x_MSG_DATA ;
4965 G_calling_module := P_CALLING_MODULE ;
4966
4967 -- ============
4968 -- Initialize the return status.
4969 -- ============
4970
4971 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
4972 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4973 X_return_status := FND_API.G_RET_STS_SUCCESS ;
4974 END IF ;
4975
4976 SAVEPOINT create_notification_pvt ;
4977
4978 G_stage := 'FND_API.Compatible_API_Call' ;
4979
4980 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4981 p_api_version_number ,
4982 l_api_name ,
4983 G_pkg_name )
4984 THEN
4985 RAISE e_ver_mismatch ;
4986 END IF ;
4987
4988 G_notification_rec := P_notification_rec;
4989
4990 -- ==================================================
4991 -- Check for required columns for Create Notification
4992 -- ==================================================
4993
4994 G_stage := 'procedure_check_notification_required_columns' ;
4995
4996 IF G_notification_rec.award_id IS NULL THEN
4997 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
4998 set_return_status ( X_return_status, 'B') ;
4999 END IF ;
5000
5001 IF G_notification_rec.event_type IS NULL THEN
5002 add_message_to_stack( P_label => 'GMS_NTF_EVENT_TYPE_NULL' ) ;
5003 set_return_status ( X_return_status, 'B') ;
5004 END IF ;
5005
5006 IF G_notification_rec.user_id IS NULL THEN
5007 add_message_to_stack( P_label => 'GMS_NTF_USER_ID_NULL' ) ;
5008 set_return_status ( X_return_status, 'B') ;
5009 END IF ;
5010
5011 -- ===================================================================
5012 -- Need to make sure that the return status is success from the above
5013 -- validations. There is no point in doing further validations as the
5014 -- required columns donot have values. So we raise an Error.
5015 -- ===================================================================
5016
5017 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5018 RAISE FND_API.G_EXC_ERROR;
5019 END IF ;
5020
5021 -- =============================================
5022 -- Verify the duplicate events for a given award
5023 -- =============================================
5024
5025 G_stage := 'create_notification.Verify_duplicate_events' ;
5026
5027 OPEN l_dup_event_csr ;
5028 FETCH l_dup_event_csr INTO l_dup_event;
5029
5030 IF l_dup_event_csr%FOUND THEN
5031 add_message_to_stack( P_label => 'GMS_NTF_AWD _EVENT_DUP' ) ;
5032 set_return_status ( X_return_status, 'B') ;
5033 END IF;
5034 CLOSE l_dup_event_csr ;
5035
5036
5037 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5038 RAISE FND_API.G_EXC_ERROR;
5039 END IF ;
5040
5041
5042 G_stage := 'create_notification.Verify_report_existence' ;
5043
5044
5045 -- ==============================================================================
5046 -- Verify the existence of report for a given award if the event type is a report
5047 -- ==============================================================================
5048
5049 IF substr(G_notification_rec.event_type,1,6) = 'REPORT' THEN
5050
5051 l_length := length(G_notification_rec.event_type);
5052 l_report_template_id := substr(G_notification_rec.event_type,7,l_length);
5053
5054 OPEN l_default_report_csr(l_report_template_id) ;
5055 FETCH l_default_report_csr INTO l_default_report;
5056
5057 IF l_default_report_csr%NOTFOUND THEN
5058 OPEN l_report_csr(l_report_template_id) ;
5059 FETCH l_report_csr INTO l_report;
5060 IF l_report_csr%NOTFOUND THEN
5061 add_message_to_stack( P_label => 'GMS_NTF_AWD_REP_NOT_EXIST' ) ;
5062 set_return_status ( X_return_status, 'B') ;
5063 END IF;
5064 END IF;
5065 CLOSE l_default_report_csr ;
5066
5067 IF l_report_csr%ISOPEN THEN
5068 CLOSE l_dup_event_csr ;
5069 END IF;
5070
5071 END IF;
5072 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5073 RAISE FND_API.G_EXC_ERROR;
5074 END IF ;
5075
5076 G_stage := 'gms_notification_pkg.insert_row' ;
5077
5078 gms_notification_pkg.insert_row(
5079 X_ROWID => l_rowid,
5080 X_AWARD_ID => G_notification_rec.award_id,
5081 X_EVENT_TYPE => G_notification_rec.event_type,
5082 X_USER_ID => G_notification_rec.user_id
5083 );
5084
5085
5086 G_stage := 'Notification Created Successfully' ;
5087
5088 EXCEPTION
5089 WHEN E_VER_MISMATCH THEN
5090 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
5091 p_token1 => 'SUPVER',
5092 P_VAL1 => g_api_version_number) ;
5093 set_return_status(X_return_status, 'B' ) ;
5094 x_msg_count := G_msg_count ;
5095 x_msg_data := G_msg_data ;
5096
5097 WHEN FND_API.G_EXC_ERROR THEN
5098 ROLLBACK TO create_notification_pvt ;
5099 set_return_status(X_return_status, 'B' ) ;
5100 x_msg_count := G_msg_count ;
5101 x_msg_data := G_msg_data ;
5102 WHEN OTHERS THEN
5103 ROLLBACK TO create_notification_pvt;
5104 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5105
5106 FND_MSG_PUB.add_exc_msg
5107 ( p_pkg_name => G_PKG_NAME
5108 , p_procedure_name => l_api_name );
5109 FND_MSG_PUB.Count_And_Get
5110 ( p_count => x_msg_count ,
5111 p_data => x_msg_data );
5112
5113 END CREATE_NOTIFICATION ;
5114
5115 -- ========================================================================
5116 -- Add Funding
5117 -- ========================================================================
5118
5119 PROCEDURE ADD_FUNDING
5120 (X_MSG_COUNT IN OUT NOCOPY NUMBER ,
5121 X_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
5122 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2 ,
5123 X_GMS_PROJECT_FUNDING_ID IN OUT NOCOPY NUMBER ,
5124 X_ROW_ID OUT NOCOPY VARCHAR2 ,
5125 P_CALLING_MODULE IN VARCHAR2 ,
5126 P_API_VERSION_NUMBER IN NUMBER ,
5127 P_AWARD_ID IN NUMBER,
5128 P_INSTALLMENT_ID IN NUMBER,
5129 P_PROJECT_ID IN NUMBER,
5130 P_TASK_ID IN NUMBER,
5131 P_AMOUNT IN NUMBER,
5132 P_FUNDING_DATE IN DATE
5133 )
5134 IS
5135
5136 CURSOR l_valid_award_csr IS
5137 SELECT *
5138 FROM gms_awards_all
5139 WHERE award_id = P_AWARD_ID;
5140
5141 CURSOR l_valid_installment_csr IS
5142 SELECT *
5143 FROM gms_installments
5144 WHERE award_id = P_AWARD_ID
5145 AND installment_id = P_INSTALLMENT_ID;
5146
5147 CURSOR l_valid_project_csr IS
5148 SELECT p.project_status_code,
5149 pt.project_type_class_code,
5150 pt.sponsored_flag,
5151 p.template_flag,
5152 p.start_date,
5153 p.closed_date
5154 FROM pa_projects_all p,
5155 pa_project_types_all pt
5156 WHERE p.project_id = P_PROJECT_ID
5157 AND pt.project_type = p.project_type
5158 And p.org_id = pt.org_id /* For Bug 5414832*/
5159 AND pt.sponsored_flag='Y' ;
5160
5161
5162 CURSOR l_valid_task_csr IS
5163 SELECT 'X'
5164 FROM pa_tasks
5165 WHERE project_id = P_project_id
5166 AND task_id = P_task_id
5167 AND task_id = top_task_id;
5168
5169 CURSOR l_invalid_funding_level_csr IS
5170 SELECT 'X'
5171 FROM gms_installments i,
5172 gms_summary_project_fundings f
5173 WHERE i.award_id = p_award_id
5174 AND f.installment_id = i.installment_id
5175 AND project_id = p_project_id
5176 AND ((task_id is null and p_task_id is not null) OR
5177 (task_id is not null and p_task_id is null));
5178
5179 CURSOR l_existing_funding_amount_csr IS
5180 SELECT task_id,total_funding_amount
5181 FROM gms_summary_project_fundings gspf
5182 WHERE installment_id = P_installment_id
5183 AND project_id = P_project_id
5184 AND NVL(task_id,-99) = NVL(P_task_id,-99);
5185
5186
5187 l_api_name VARCHAR2(30) := 'ADD_FUNDING' ;
5188 l_error_code VARCHAR2(2000) ;
5189 l_app_name VARCHAR2(10) ;
5190 l_error_stage VARCHAR2(2000) ;
5191 l_return_code VARCHAR2(1);
5192 l_errbuf VARCHAR2(2000);
5193 l_rowid VARCHAR2(45) ;
5194 l_funding_level VARCHAR2(1) := 'P' ;
5195 l_project_status_code VARCHAR2(60);
5196 l_project_type_class_code VARCHAR2(60);
5197 l_sponsored_flag VARCHAR2(1);
5198 l_template_flag VARCHAR2(1);
5199 l_project_start_date DATE;
5200 l_project_closed_date DATE;
5201 l_valid_task VARCHAR2(1);
5202 l_invalid_funding_level VARCHAR2(1);
5203 l_task_id NUMBER;
5204 l_existing_funding_amount NUMBER;
5205 l_total_installment_amount NUMBER;
5206 l_total_funding_amount NUMBER;
5207 l_project_funding_id NUMBER;
5208 l_installment_rec GMS_INSTALLMENTS%ROWTYPE;
5209 l_award_rec GMS_AWARDS_ALL%ROWTYPE;
5210
5211
5212
5213
5214 BEGIN
5215 init_message_stack;
5216
5217 G_msg_count := X_MSG_COUNT ;
5218 G_msg_data := X_MSG_DATA ;
5219 G_calling_module := P_CALLING_MODULE ;
5220
5221 -- ============================
5222 -- Initialize the return status.
5223 -- ============================
5224
5225 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
5226 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
5227 X_return_status := FND_API.G_RET_STS_SUCCESS ;
5228 END IF ;
5229
5230 SAVEPOINT add_funding_pvt ;
5231
5232 -- ===================================================
5233 -- Need to set global variables to use PA public APIs.
5234 -- ===================================================
5235
5236 G_stage := 'pa_interface_utils_pub.set_global_info';
5237
5238 pa_interface_utils_pub.set_global_info(p_api_version_number => 1.0,
5239 p_responsibility_id => FND_GLOBAL.resp_id,
5240 p_user_id => FND_GLOBAL.user_id,
5241 p_resp_appl_id => FND_GLOBAL.resp_appl_id, -- Bug 2534915
5242 p_msg_count => x_msg_count,
5243 p_msg_data =>x_msg_data,
5244 p_return_status => x_return_status);
5245
5246 IF x_return_status <> 'S' THEN
5247
5248 add_message_to_stack( P_label => 'GMS_SET_GLOBAL_INFO_FAILED');
5249 set_return_status ( X_return_status, 'U') ;
5250
5251 END IF;
5252
5253 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5254 RAISE FND_API.G_EXC_ERROR;
5255 END IF ;
5256
5257 G_stage := 'FND_API.Compatible_API_Call' ;
5258
5259 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
5260 p_api_version_number ,
5261 l_api_name ,
5262 G_pkg_name )
5263 THEN
5264 RAISE e_ver_mismatch ;
5265 END IF ;
5266
5267 -- ===================================================================
5268 -- Check for required columns for Add Funding
5269 -- Award Id should NOT be NULL.
5270 -- Installment Id should NOT be NULL.
5271 -- Project Id should NOT be NULL.
5272 -- Amount should NOT be NULL.
5273 -- Amount should NOT be NEGATIVE.
5274 -- Project Funding Date should NOT be NULL.
5275 -- ===================================================================
5276
5277 IF P_AWARD_ID IS NULL THEN
5278 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
5279 set_return_status ( X_return_status, 'B') ;
5280 END IF ;
5281
5282 IF P_INSTALLMENT_ID IS NULL THEN
5283 add_message_to_stack( P_label => 'GMS_FND_INST_ID_NULL' ) ;
5284 set_return_status ( X_return_status, 'B') ;
5285 END IF ;
5286
5287 IF P_PROJECT_ID IS NULL THEN
5288 add_message_to_stack( P_label => 'GMS_FND_PROJECT_ID_NULL' ) ;
5289 set_return_status ( X_return_status, 'B') ;
5290 END IF ;
5291
5292 IF P_AMOUNT IS NULL THEN
5293 add_message_to_stack( P_label => 'GMS_FND_AMOUNT_NULL' ) ;
5294 set_return_status ( X_return_status, 'B') ;
5295 END IF ;
5296 --Commented the below if condition for the bug # 16016703
5297 /*IF P_AMOUNT < 0 THEN
5298 add_message_to_stack( P_label => 'GMS_FND_AMOUNT_NEG' ) ;
5299 set_return_status ( X_return_status, 'B') ;
5300 END IF ;*/
5301
5302 IF P_FUNDING_DATE IS NULL THEN
5303 add_message_to_stack( P_label => 'GMS_FND_DATE_NULL' ) ;
5304 set_return_status ( X_return_status, 'B') ;
5305 END IF ;
5306
5307 -- ===================================================================
5308 -- Need to make sure that the return status is success from the above
5309 -- validations. There is no point in doing further validations as the
5310 -- required columns donot have values. So we raise an Error.
5311 -- ===================================================================
5312
5313 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5314 RAISE FND_API.G_EXC_ERROR;
5315 END IF ;
5316
5317 -- ===================================================================
5318 -- Check for the Award Validity
5319 --
5320 -- We check whether the award is an existing award or not. We dont need
5321 -- to validate the award status. Funding is done from an award with any
5322 -- status like Closed,At Risk, On Hold, Active etc.
5323 -- ===================================================================
5324
5325 G_stage := 'add_funding.Verify_Award';
5326
5327 OPEN l_valid_award_csr ;
5328 FETCH l_valid_award_csr INTO l_award_rec;
5329
5330 IF l_valid_award_csr%NOTFOUND THEN
5331 add_message_to_stack( P_label => 'GMS_AWD_NOT_EXISTS' ) ;
5332 set_return_status ( X_return_status, 'B') ;
5333 END IF;
5334
5335 CLOSE l_valid_award_csr ;
5336
5337 -- ===================================================================
5338 -- Check for the Installment Validity
5339 -- We check for the installment existance in the database, so it is a
5340 -- valid installment, and we check the active flag also, as project
5341 -- funding can be done from active installments only.
5342 -- ===================================================================
5343
5344 G_stage := 'add_funding.Verify_Installment';
5345
5346 OPEN l_valid_installment_csr ;
5347 FETCH l_valid_installment_csr INTO l_installment_rec;
5348
5349 IF l_valid_installment_csr%NOTFOUND THEN
5350 add_message_to_stack( P_label => 'GMS_FND_INSTALL_ID_INVALID' ) ;
5351 set_return_status ( X_return_status, 'B') ;
5352 END IF;
5353
5354 CLOSE l_valid_installment_csr ;
5355
5356 IF l_installment_rec.active_flag <> 'Y' THEN
5357 add_message_to_stack( P_label => 'GMS_FND_INSTALL_INACTIVE' ) ;
5358 set_return_status ( X_return_status, 'B') ;
5359 END IF;
5360
5361
5362 -- ===================================================================
5363 -- Check for the Project Validity
5364 -- We do the following validations to make sure that the project is a
5365 -- valid one for project funding.
5366 -- The project should be defined in the system.
5367 -- The Project Type Class should be INDIRECT or CAPITAL
5368 -- The Project Status should NOT be CLOSED or UNAPPROVED
5369 -- The Project should not end before the Installment Start Date
5370 -- The Project should not start after the Installment End Date
5371 -- ===================================================================
5372
5373 G_stage := 'add_funding.Verify_project';
5374
5375 OPEN l_valid_project_csr;
5376 FETCH l_valid_project_csr INTO l_project_status_code,
5377 l_project_type_class_code,
5378 l_sponsored_flag,
5379 l_template_flag,
5380 l_project_start_date,
5381 l_project_closed_date;
5382
5383 IF l_valid_project_csr%NOTFOUND THEN
5384 add_message_to_stack( P_label => 'GMS_FND_PROJECT_ID_INVALID' ) ;
5385 set_return_status ( X_return_status, 'B') ;
5386 END IF;
5387
5388 CLOSE l_valid_project_csr ;
5389
5390 IF l_project_status_code IN ('CLOSED','UNAPPROVED') THEN
5391 add_message_to_stack( P_label => 'GMS_FND_PROJ_STATUS_INVALID' ) ;
5392 set_return_status ( X_return_status, 'B') ;
5393 END IF;
5394
5395 IF l_project_type_class_code NOT IN ('INDIRECT', 'CAPITAL') THEN
5396 add_message_to_stack( P_label => 'GMS_FND_PROJ_TYPE_INVALID' ) ;
5397 set_return_status ( X_return_status, 'B') ;
5398 END IF;
5399
5400 IF l_sponsored_flag <> 'Y' THEN
5401 add_message_to_stack( P_label => 'GMS_FND_NOT_SPONSORED_PROJ' ) ;
5402 set_return_status ( X_return_status, 'B') ;
5403 END IF;
5404
5405 IF l_template_flag = 'Y' THEN
5406 add_message_to_stack( P_label => 'GMS_FND_PROJ_TEMPLATE' ) ;
5407 set_return_status ( X_return_status, 'B') ;
5408 END IF;
5409
5410 IF NVL(l_project_closed_date,l_installment_rec.close_date) < l_installment_rec.start_date_active THEN
5411 add_message_to_stack( P_label => 'GMS_FND_PROJ_CLOSED' ) ;
5412 set_return_status ( X_return_status, 'B') ;
5413 END IF;
5414
5415 IF l_project_start_date > l_installment_rec.end_date_active THEN
5416 add_message_to_stack( P_label => 'GMS_FND_PROJ_NOT_STARTED' ) ;
5417 set_return_status ( X_return_status, 'B') ;
5418 END IF;
5419
5420
5421 -- ==============================================================================
5422 -- Determining the Funding Level
5423 -- We select task id, funding amount from gms_summary_project_fundings table
5424 -- We default the Project Funding Level to 'P' means Project Level.
5425 -- If the cursor did not find any record, then it would be 'F' meaning First Time
5426 -- time funding. If the select returns a Task Id then it would be Task Level
5427 -- Funding.
5428 -- ==============================================================================
5429
5430 G_stage := 'Add_funding.Getting Funding Level' ;
5431
5432 OPEN l_existing_funding_amount_csr;
5433 FETCH l_existing_funding_amount_csr INTO l_task_id,
5434 l_existing_funding_amount;
5435 IF l_existing_funding_amount_csr%NOTFOUND THEN
5436 l_funding_level := 'F';
5437 END IF;
5438
5439 CLOSE l_existing_funding_amount_csr;
5440
5441 IF l_task_id IS NOT NULL THEN
5442 l_funding_level := 'T';
5443 END IF;
5444
5445
5446 -- ============================================================
5447 -- Check for Task Id required or not. If the funding level is T
5448 -- Task Id becomes mandatory. If the funding level is P task Id
5449 -- should be null. If funding level is F meaning this is the
5450 -- first time funding so we need not worry even if task id passed
5451 -- or null
5452 -- ============================================================
5453
5454 -- Bug 2381094
5455
5456 G_stage := 'Add_funding.Validating Funding Level' ;
5457
5458 OPEN l_invalid_funding_level_csr;
5459 FETCH l_invalid_funding_level_csr INTO l_invalid_funding_level;
5460
5461 IF l_invalid_funding_level_csr%FOUND THEN
5462
5463 if P_task_id IS NOT NULL THEN
5464 add_message_to_stack( P_label => 'GMS_FND_PROJ_LVL_FUND');
5465 else
5466 add_message_to_stack( P_label => 'GMS_FND_TASK_LVL_FUND');
5467 end if;
5468
5469 set_return_status ( X_return_status, 'B') ;
5470
5471 END IF;
5472
5473 CLOSE l_invalid_funding_level_csr;
5474
5475
5476 -- =================================================================
5477 -- Check for the Task validity. Task need to be validated
5478 -- only when the task id is required, i.e if the funding
5479 -- level is in 'P' or 'F' then we need task id also. If it is
5480 -- passed then we need to validate it.
5481 -- ==================================================================
5482
5483 IF l_funding_level <> 'P' and p_task_id IS NOT NULL then
5484
5485 G_stage := 'add_funding.Verify_Task';
5486
5487 OPEN l_valid_task_csr ;
5488 FETCH l_valid_task_csr INTO l_valid_task;
5489
5490 IF l_valid_task_csr%NOTFOUND THEN
5491 add_message_to_stack( P_label => 'GMS_FND_TASK_INVALID' ) ;
5492 set_return_status ( X_return_status, 'B') ;
5493 END IF;
5494
5495 CLOSE l_valid_task_csr ;
5496
5497 END IF;
5498
5499 -- ===================================================================
5500 -- Check for the Project Funding Date Validity
5501 -- The Project FUnding Date should NOT be NULL.
5502 -- This is done at the beginning.
5503 -- The Project Funding Date should be with Pre Award Date,if exists or
5504 -- Award Start Date and Award Close Date.
5505 -- ===================================================================
5506
5507 IF (P_FUNDING_DATE < NVL(l_award_rec.preaward_date,l_award_rec.start_date_active)) OR
5508 (P_FUNDING_DATE > l_award_rec.close_date) THEN
5509 add_message_to_stack( P_label => 'GMS_FND_FUND_DATE_INVALID' ) ;
5510 set_return_status ( X_return_status, 'B') ;
5511 END IF;
5512
5513 -- ==================================================================
5514 -- Amount Check
5515 --
5516 -- The Amount check will be done in the following ways
5517 -- 1) The Funding Amount should not be negative
5518 -- This is done at the beginning
5519 -- 2) The total funding Amount for all the projects and
5520 -- Tasks for the given Installment should not exceed
5521 -- Total Installment Amount
5522 -- 3) The total funding amount for the project and task and
5523 -- Installment should not go below the budgeted amount
5524 --
5525 -- We are not doing the budget amount validation as
5526 -- ADD_FUNDING is supposed to increase the funding amount
5527 -- always. When negative amounts are considered fro Project
5528 -- Funding then we need to do the above validation also.
5529 -- ==================================================================
5530
5531 -- ============================================
5532 -- Validate the amount with installment amount
5533 -- ============================================
5534
5535 G_stage := 'Add_funding.Verify_amount_with_installment' ;
5536
5537 l_total_installment_amount := l_installment_rec.direct_cost + l_installment_rec.indirect_cost;
5538
5539 l_total_funding_amount := NVL(l_existing_funding_amount,0) + P_Amount;
5540
5541 IF l_total_funding_amount > l_total_installment_amount THEN
5542 add_message_to_stack( P_label => 'GMS_FUNDING_AMOUNT_EXCEEDED' ) ;
5543 set_return_status ( X_return_status, 'B') ;
5544 END IF ;
5545
5546 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5547 RAISE FND_API.G_EXC_ERROR;
5548 END IF ;
5549
5550 -- ======================================================
5551 -- Sequence number is required for gms_project_funding_id
5552 -- ======================================================
5553
5554 G_stage := 'gms_project_fundings_pkg.insert_row' ;
5555
5556 SELECT gms_project_fundings_s.nextval
5557 INTO x_gms_project_funding_id
5558 FROM dual;
5559
5560 GMS_PROJECT_FUNDINGS_PKG.INSERT_ROW(
5561 X_ROWID => l_rowid,
5562 X_GMS_PROJECT_FUNDING_ID => x_gms_project_funding_id,
5563 X_PROJECT_FUNDING_ID => l_project_funding_id,
5564 X_PROJECT_ID => P_project_id,
5565 X_TASK_ID => P_task_id,
5566 X_INSTALLMENT_ID => P_installment_id,
5567 X_FUNDING_AMOUNT => P_amount,
5568 X_DATE_ALLOCATED => P_funding_date,
5569 X_MODE => 'R'
5570 );
5571
5572 -- ================================================================
5573 -- GMS_SUMMARY_PROJECT_FUNDINGS need to be updated with this amount
5574 -- if this project and task and installment combination exists or
5575 -- this need to be inserted.
5576 -- ================================================================
5577
5578 G_stage := 'gms_summary_project_fundings.create_funding' ;
5579
5580 GMS_SUMM_FUNDING_PKG.CREATE_GMS_SUMMARY_FUNDING(
5581 X_INSTALLMENT_ID => P_installment_id,
5582 X_PROJECT_ID => P_project_id,
5583 X_TASK_ID => P_task_id,
5584 X_FUNDING_AMOUNT => P_amount,
5585 RETCODE => l_return_code,
5586 ERRBUF => l_errbuf
5587 );
5588
5589
5590 IF l_return_code <> 'S' THEN
5591
5592 add_message_to_stack( P_label => 'GMS_FND_SUMMARY_CRT_FAILED');
5593 set_return_status ( X_return_status, 'B') ;
5594
5595 END IF;
5596
5597 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5598 RAISE FND_API.G_EXC_ERROR;
5599 END IF ;
5600
5601 -- ============================================================
5602 -- PA_PROJECT_FUNDINGS and PA_SUMMARY_PROJECT_FUNDINGS
5603 -- need to be updated with this information. The revenue budget
5604 -- for the award project need to be re built with the updated
5605 -- amounts.
5606 -- =============================================================
5607
5608 G_stage := 'gms_multi_funding.create_award_funding' ;
5609
5610 GMS_MULTI_FUNDING.CREATE_AWARD_FUNDING(
5611 X_INSTALLMENT_ID => P_installment_id,
5612 X_ALLOCATED_AMOUNT => P_amount,
5613 X_DATE_ALLOCATED => P_funding_date,
5614 X_GMS_PROJECT_FUNDING_ID => x_gms_project_funding_id,
5615 X_PROJECT_FUNDING_ID => l_project_funding_id,
5616 X_APP_SHORT_NAME => l_app_name,
5617 X_MSG_COUNT => x_msg_count,
5618 ERRBUF => l_errbuf,
5619 RETCODE => l_return_code
5620 );
5621
5622 IF l_return_code <> 'S' THEN
5623
5624 add_message_to_stack( P_LABEL => 'GMS_FND_AWD_FND_FAILED');
5625 set_return_status ( X_RETURN_STATUS, 'B') ;
5626
5627 END IF;
5628
5629 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5630 RAISE FND_API.G_EXC_ERROR;
5631 END IF ;
5632
5633 G_stage := 'At the End of the ADD_FUNDING';
5634
5635 EXCEPTION
5636 WHEN E_VER_MISMATCH THEN
5637 add_message_to_stack(
5638 P_LABEL => 'GMS_API_VER_MISMATCH',
5639 P_TOKEN1 => 'SUPVER',
5640 P_VAL1 => g_api_version_number) ;
5641 set_return_status(X_RETURN_STATUS, 'B' ) ;
5642 x_msg_count := G_msg_count ;
5643 x_msg_data := G_msg_data ;
5644
5645 WHEN FND_API.G_EXC_ERROR THEN
5646 ROLLBACK TO add_funding_pvt ;
5647 set_return_status(X_RETURN_STATUS, 'B' ) ;
5648 x_msg_count := G_msg_count ;
5649 x_msg_data := G_msg_data ;
5650 WHEN OTHERS THEN
5651 ROLLBACK TO add_funding_pvt;
5652 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5653
5654 FND_MSG_PUB.add_exc_msg
5655 ( P_PKG_NAME => G_PKG_NAME
5656 , P_PROCEDURE_NAME => l_api_name );
5657 FND_MSG_PUB.Count_And_Get
5658 ( P_COUNT => x_msg_count ,
5659 P_DATA => x_msg_data );
5660
5661 END ADD_FUNDING;
5662 END GMS_AWARD_PVT ;