[Home] [Help]
PACKAGE BODY: APPS.GMS_AWARD_PVT
Source
1 PACKAGE BODY GMS_AWARD_PVT AS
2 -- $Header: gmsawpvb.pls 120.4.12010000.3 2008/12/02 14:42:31 rrambati 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.funding_source_id ;
379 l_bill_to_adr := g_award_rec.bill_to_address_id ;
380 l_ship_to_adr := g_award_rec.ship_to_address_id ;
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';
1349
1350 cursor c_pa_period(x_date date) is
1351 select count(*) from pa_periods pap
1352 where x_date between pap.start_date and pap.end_date ;
1353
1354 cursor c_gl_period(x_date date) is
1355 select 1
1356 from gl_period_statuses gps
1357 where x_date between gps.start_date
1358 and gps.end_date
1359 and gps.application_id = 101
1360 and gps.set_of_books_id = (select set_of_books_id
1361 from pa_implementations);
1362
1363 /* Bug# 3985020 : Commented
1364 select count(*) from gl_period_statuses gps
1365 where x_date between gps.start_date and gps.end_date
1366 and gps.application_id = 101
1367 and gps.set_of_books_id in (select set_of_books_id from pa_implementations_all);
1368 */
1369
1370 cursor C_agreement is
1371 SELECT agreement_type
1372 FROM pa_agreement_types
1373 WHERE agreement_type = g_award_rec.type ;
1374
1375 -- Debashis. Added exists clause. Removed rownum.
1376 CURSOR C_fnd_user is
1377 select 1 from dual where exists (
1378 select user_id
1379 from fnd_user
1380 where employee_id = g_award_rec.award_manager_id);
1381 -- and rownum = 1 ;
1382 BEGIN
1383 -- +++ Check Automatic Number validations +++
1384 IF G_gmsimpl_rec.user_defined_award_num_code = 'A'
1385 and g_award_rec.award_number is not NULL THEN
1386
1387 -- ERROR Automatic Numbering issue.
1388 -- Question Do we need to override
1389 -- This or raise an error.
1390 add_message_to_stack( P_label => 'GMS_AWD_NUMBER_NOT_NULL' ) ;
1391 l_error := TRUE ;
1392
1393 ELSIF G_gmsimpl_rec.user_defined_award_num_code = 'A'
1394 AND g_award_rec.award_number is NULL THEN
1395
1396 g_award_rec.award_number := func_get_award_num ;
1397 END IF ;
1398
1399 IF g_award_rec.award_number is NULL THEN
1400
1401 -- ------------------------------
1402 -- MSG: AWARD_NUMBER_NULL
1403 -- ------------------------------
1404 l_error := TRUE ;
1405 add_message_to_stack( P_label => 'GMS_AWD_NUMBER_NULL' ) ;
1406 END IF ;
1407
1408 BEGIN
1409 IF g_award_rec.award_number is not NULL THEN
1410
1411 IF G_gmsimpl_rec.user_defined_award_num_code = 'M'
1412 and G_gmsimpl_rec.manual_award_num_type = 'NUMERIC'
1413 THEN
1414 g_award_rec.award_number := to_number(g_award_rec.award_number) ;
1415 END IF ;
1416
1417 END IF ;
1418 EXCEPTION
1419 when VALUE_ERROR THEN
1420 -- ERROR ( Award Number should be NUMERIC.
1421 add_message_to_stack( P_label => 'GMS_AWD_NUMBER_NOT_NUMERIC' ) ;
1422 l_error := TRUE ;
1423 END ;
1424
1425
1426 -- ========================
1427 -- Award number is invalid. So we can not
1428 -- continue award number further checks.
1429 -- ========================
1430 IF not l_error then
1431 open C1 ;
1432 fetch C1 into project_check ;
1433 close C1 ;
1434
1435 If Project_Check = 1 then
1436 add_message_to_stack( P_label => 'GMS_AWD_PRJNAME_EXISTS' ) ;
1437 l_error := TRUE ;
1438 ELSIF Project_Check = 2 THEN
1439
1440 add_message_to_stack( P_label => 'GMS_AWD_PRJNUM_EXISTS' ) ;
1441 l_error := TRUE ;
1442 END IF ;
1443 END IF ;
1444
1445 -- +++++++++++ award Number Validations ++++++++++
1446 open C_AWDNUM_DUP ;
1447 fetch C_AWDNUM_DUP into x_dummy ;
1448 close C_AWDNUM_DUP ;
1449
1450 IF x_dummy > 0 THEN
1451 -- ERROR (GMS_AWD_NUMBER_DUP) ;
1452 -- MSG : Duplicate Award Number
1453 -- ----------------------------
1454 add_message_to_stack( P_label => 'GMS_AWD_NUMBER_DUP' ) ;
1455 l_error := TRUE ;
1456 END IF ;
1457 x_dummy := 0 ;
1458
1459 --open C_AWDSNM_DUP ;
1460 --fetch C_AWDSNM_DUP into x_AWD_SNM ;
1461 --IF C_AWDSNM_DUP%FOUND THEN
1462 -- ERROR (GMS_AWD_NUMBER_DUP) ;
1463 -- MSG : Duplicate Award Number
1464 -- ----------------------------
1465 -- add_message_to_stack( P_label => 'GMS_AWD_SHORT_NAME_DUP' ) ;
1466 -- l_error := TRUE ;
1467 --END IF ;
1468 --close C_AWDSNM_DUP ;
1469
1470
1471 -- ++++++++++ Validate LOV data ++++++++++++++++
1472 IF g_award_rec.award_manager_id is not NULL then
1473 open C_awd_manager ;
1474 fetch C_awd_manager into x_dummy; -- Bug 2534936
1475
1476 IF C_awd_manager%NOTFOUND THEN
1477 add_message_to_stack( P_label => 'GMS_AWD_MANAGER_INVALID' ) ;
1478 l_error := TRUE ;
1479 END IF ;
1480 CLOSE C_awd_manager ;
1481 END IF ;
1482
1483 -- ==================
1484 -- Award manager must be FND_USER for budget_wf_enabled_flag
1485 -- ================
1486
1487 IF G_award_rec.budget_wf_enabled_flag = 'Y' THEN
1488 open c_fnd_user ;
1489 fetch c_fnd_user into x_dummy ;
1490 IF c_fnd_user%NOTFOUND then
1491 add_message_to_stack( P_label => 'GMS_AWD_MGR_NOT_FND_USER' ) ;
1492 l_error := TRUE ;
1493 end if ;
1494 close c_fnd_user ;
1495 END IF ;
1496
1497 IF G_award_rec.budget_wf_enabled_flag NOT IN ( 'Y', 'N' ) THEN
1498 add_message_to_stack( P_label => 'GMS_AWD_WORKFLOW_FLAG_INVALID') ;
1499 l_error := TRUE ;
1500 END IF ;
1501
1502
1503 IF g_award_rec.type is not NULL then
1504 open C_agreement ;
1505 fetch C_agreement into x_agreement_type ;
1506 IF C_agreement%NOTFOUND THEN
1507 add_message_to_stack( P_label => 'GMS_AGREEMENT_TYPE_INVALID' ) ;
1508 l_error := TRUE ;
1509 END IF ;
1510 CLOSE C_agreement ;
1511 END IF ;
1512
1513
1514 IF g_award_rec.billing_format = 'LOC' and
1515 g_award_rec.bill_to_customer_id is NULL
1516 THEN
1517
1518 -- ERROR 'Bill to customer ID is missing.
1519 add_message_to_stack( P_label => 'GMS_NO_BILL_TO_CUST_LOC' ) ;
1520 l_error := TRUE ;
1521 END IF ;
1522
1523 -- =======
1524 -- Billing Format Validations
1525 -- =======
1526 IF NVL(g_award_rec.billing_format,'LOC') NOT IN
1527 ( 'NO_PRINT', 'PRINT_INVOICE', 'LOC', 'AGENCY','EDI' ) THEN
1528 add_message_to_stack( P_label => 'GMS_BILLING_FORMAT_INVALID' ) ;
1529 l_error := TRUE ;
1530 END IF ;
1531
1532 IF NVL(g_award_rec.billing_format,'EDI') <> 'EDI'
1533 and g_award_rec.transaction_number is not NULL THEN
1534 add_message_to_stack( P_label => 'GMS_TRANS_NUMBER_NULL' ) ;
1535 l_error := TRUE ;
1536 END IF ;
1537
1538 IF NVL(g_award_rec.billing_format,'LOC') <> 'LOC'
1539 and g_award_rec.bill_to_customer_id is not NULL THEN
1540 add_message_to_stack( P_label => 'GMS_BILL_TO_CUSTLOC_NULL' ) ;
1541 l_error := TRUE ;
1542 END IF ;
1543
1544 IF NVL(g_award_rec.billing_format,'AGENCY') <> 'AGENCY'
1545 and g_award_rec.agency_specific_form is not NULL THEN
1546 add_message_to_stack( P_label => 'GMS_AGENCY_FORM_NULL' ) ;
1547 l_error := TRUE ;
1548 END IF ;
1549
1550 -- ++++++++++ Check PA periods ++++++++++++++++++
1551 open C_pa_period(g_award_rec.start_date_active) ;
1552 fetch C_pa_period into x_dummy ;
1553 close c_pa_period ;
1554
1555 IF x_dummy = 0 THEN
1556 -- ERROR GMS_AWD_DATE_EXZ_PA_DATE
1557 add_message_to_stack( P_label => 'GMS_AWD_START_DATE_NOT_PAPRD' ) ;
1558 l_error := TRUE ;
1559 END IF ;
1560 x_dummy := 0 ;
1561
1562 open C_pa_period(g_award_rec.end_date_active) ;
1563 fetch C_pa_period into x_dummy ;
1564 close c_pa_period ;
1565 IF x_dummy = 0 THEN
1566 add_message_to_stack( P_label => 'GMS_AWD_END_DATE_NOT_PAPRD' ) ;
1567 l_error := TRUE ;
1568 END IF ;
1569
1570 x_dummy := 0 ;
1571 /* commented the close_date validation against open pa periods for bug 7603285*/
1572 /*open C_pa_period(g_award_rec.close_date) ;
1573 fetch C_pa_period into x_dummy ;
1574 close c_pa_period ;
1575 IF x_dummy = 0 THEN
1576 add_message_to_stack( P_label => 'GMS_AWD_CLOSE_DATE_NOT_PAPRD' ) ;
1577 l_error := TRUE ;
1578 END IF ;
1579
1580 x_dummy := 0 ; */
1581
1582
1583 IF g_award_rec.preaward_date is not NULL THEN
1584
1585 open C_pa_period(g_award_rec.preaward_date) ;
1586 fetch C_pa_period into x_dummy ;
1587 close c_pa_period ;
1588 IF x_dummy = 0 THEN
1589 --ERROR GMS_AWD_DATE_EXZ_PA_DATE
1590 add_message_to_stack( P_label => 'GMS_AWD_PRE_DATE_NOT_PAPRD' ) ;
1591 l_error := TRUE ;
1592 END IF ;
1593
1594 open c_gl_period(g_award_rec.preaward_date) ;
1595 fetch c_gl_period into x_dummy ;
1596 close c_gl_period ;
1597
1598 --Bug# 3985020 : Added NVL as aggregate function count was removed.
1599 IF nvl(x_dummy,0) = 0 THEN
1600 --ERROR GMS_AWD_DATE_EXZ_GL_DAT
1601 add_message_to_stack( P_label => 'GMS_AWD_PRE_DATE_NOT_GL' ) ;
1602 l_error := TRUE ;
1603 END IF ;
1604 x_dummy := 0 ;
1605 END IF ;
1606
1607 -- ++++++++++ Check GL periods ++++++++++++++++++
1608
1609 open c_gl_period(g_award_rec.start_date_active) ;
1610 fetch c_gl_period into x_dummy ;
1611 close c_gl_period ;
1612 --Bug# 3985020 : Added NVL as aggregate function count was removed.
1613 IF nvl(x_dummy,0) = 0 THEN
1614 --ERROR GMS_AWD_DATE_EXZ_GL_DAT
1615 add_message_to_stack( P_label => 'GMS_AWD_START_DATE_NOT_GL' ) ;
1616 l_error := TRUE ;
1617 END IF ;
1618 x_dummy := 0 ;
1619
1620 open C_gl_period(g_award_rec.end_date_active) ;
1621 fetch C_gl_period into x_dummy ;
1622 close c_gl_period ;
1623 --Bug# 3985020 : Added NVL as aggregate function count was removed.
1624 IF nvl(x_dummy,0) = 0 THEN
1625 --ERROR GMS_AWD_DATE_EXZ_GL_DAT
1626 add_message_to_stack( P_label => 'GMS_AWD_END_DATE_NOT_GL' ) ;
1627 l_error := TRUE ;
1628 END IF ;
1629 x_dummy := 0 ;
1630
1631 /* commented the close_date validation against open GL periods for bug 7603285*/
1632 /*open c_gl_period(g_award_rec.close_date) ;
1633 fetch c_gl_period into x_dummy ;
1634 close c_gl_period ;
1635 --Bug# 3985020 : Added NVL as aggregate function count was removed.
1636 IF nvl(x_dummy,0) = 0 THEN
1637 --ERROR GMS_AWD_DATE_EXZ_GL_DAT
1638 add_message_to_stack( P_label => 'GMS_AWD_CLOSE_DATE_NOT_GL' ) ;
1639 l_error := TRUE ;
1640 END IF ;
1641 x_dummy := 0 ;*/
1642
1643 IF NVL(g_award_rec.fund_control_level_award,'B') not in ('B','D','N' )
1644 THEN
1645 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_AWD_INVALID' ) ;
1646 l_error := TRUE ;
1647 END IF ;
1648
1649 IF NVL(g_award_rec.fund_control_level_task, 'B' ) not in ('B','D','N' )
1650 THEN
1651 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_TASK_INVALID' ) ;
1652 l_error := TRUE ;
1653 END IF ;
1654
1655 IF NVL(g_award_rec.fund_control_level_res_grp, 'B' ) not in ('B','D','N' )
1656 THEN
1657 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_RGP_INVALID' ) ;
1658 l_error := TRUE ;
1659 END IF ;
1660
1661 IF NVL(g_award_rec.fund_control_level_res, 'B' ) not in ('B','D','N' )
1662 THEN
1663 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_RES_INVALID' ) ;
1664 l_error := TRUE ;
1665 END IF ;
1666
1667 IF g_award_rec.billing_distribution_rule not in ('COST', 'EVENT' ) THEN
1668 add_message_to_stack( P_label => 'GMS_BILL_DIST_RULE_INVALID' ) ;
1669 l_error := TRUE ;
1670 END IF ;
1671
1672 IF g_award_rec.revenue_distribution_rule not in ('COST', 'EVENT' ) THEN
1673 add_message_to_stack( P_label => 'GMS_REV_DIST_RULE_INVALID' ) ;
1674 l_error := TRUE ;
1675 END IF ;
1676
1677 IF g_award_rec.billing_distribution_rule = 'COST'
1678 and g_award_rec.revenue_distribution_rule = 'EVENT' THEN
1679
1680 -- ERROR ( GMS_DISTRIBUTION_RULE_CONFLICT )
1681 add_message_to_stack( P_label => 'GMS_DISTRIBUTION_RULE_CONFLICT' ) ;
1682 l_error := TRUE ;
1683 END IF ;
1684
1685 -- +++++++++++ Date Validations +++++++++++++++
1686
1687 IF g_award_rec.preaward_date is not NULL
1688 and g_award_rec.start_date_active is not NULL
1689 and g_award_rec.start_date_active < g_award_rec.preaward_date THEN
1690
1691 -- ERROR ( GMS_START_DATE_AFTER_PREAWARD )
1692 add_message_to_stack( P_label => 'GMS_START_DATE_AFTER_PREAWARD' ) ;
1693 l_error := TRUE ;
1694 END IF ;
1695
1696 IF g_award_rec.start_date_active is not NULL
1697 and g_award_rec.end_date_active is not NULL
1698 and g_award_rec.start_date_active > g_award_rec.end_date_active THEN
1699
1700 -- ERROR ( GMS_END_DATE_BEFORE_START_DATE )
1701 add_message_to_stack( P_label => 'GMS_END_DATE_BEFORE_START_DATE' ) ;
1702 l_error := TRUE ;
1703 END IF ;
1704
1705
1706 IF g_award_rec.end_date_active is not NULL
1707 and g_award_rec.close_date is not NULL
1708 and g_award_rec.end_date_active > g_award_rec.close_date THEN
1709
1710 -- ERROR ( GMS_CLOSE_DATE_BEFORE_END_DATE )
1711 add_message_to_stack( P_label => 'GMS_CLOSE_DATE_BEFORE_END_DATE' ) ;
1712 l_error := TRUE ;
1713
1714 END IF ;
1715
1716 IF g_award_rec.billing_offset < 0 THEN
1717 add_message_to_stack( P_label => 'GMS_BILL_OFFSET_INVALID' ) ;
1718 l_error := TRUE ;
1719 END IF ;
1720 IF l_error THEN
1721 set_return_status(X_return_status,'B') ;
1722 END IF ;
1723
1724 END validate_award ;
1725
1726 -- ====================
1727 -- create_agreement
1728 -- Following procedure create an agreement for award creation in
1729 -- progress.
1730 --Shared Service Enhancement : Added ORG_ID in the pa_agreements_pkg.insert_row
1731 -- ====================
1732 PROCEDURE create_agreement(p_agreement_id OUT NOCOPY NUMBER ) is
1733
1734 L_row_id varchar2(30) ;
1735 L_agreement_id NUMBER ;
1736 BEGIN
1737
1738 PA_AGREEMENTS_PKG.INSERT_ROW(
1739 X_ROWID => L_Row_Id,
1740 X_AGREEMENT_ID => L_Agreement_Id,
1741 X_CUSTOMER_ID => g_award_rec.funding_source_id, --G_bill_contact_rec.customer_id, bug 3076921
1742 X_AGREEMENT_NUM => g_award_rec.award_number,
1743 X_AGREEMENT_TYPE => g_award_rec.type,
1744 X_LAST_UPDATE_DATE => sysdate,
1745 X_LAST_UPDATED_BY => g_award_rec.last_updated_by,
1746 X_CREATION_DATE => sysdate,
1747 X_CREATED_BY => g_award_rec.created_by,
1748 X_LAST_UPDATE_LOGIN => g_award_rec.last_update_login,
1749 X_OWNED_BY_PERSON_ID => g_award_rec.award_manager_id,
1750 X_TERM_ID => g_award_rec.billing_term,
1751 X_REVENUE_LIMIT_FLAG => nvl(g_award_rec.hard_limit_flag, 'N'), -- Bug 2464841 : Changed 'Y'to'N'
1752 X_AMOUNT => 0,
1753 X_DESCRIPTION => NULL,
1754 X_EXPIRATION_DATE => g_award_rec.close_date,
1755 X_ATTRIBUTE_CATEGORY => NULL,
1756 X_ATTRIBUTE1 => NULL,
1757 X_ATTRIBUTE2 => NULL,
1758 X_ATTRIBUTE3 => NULL,
1759 X_ATTRIBUTE4 => NULL,
1760 X_ATTRIBUTE5 => NULL,
1761 X_ATTRIBUTE6 => NULL,
1762 X_ATTRIBUTE7 => NULL,
1763 X_ATTRIBUTE8 => NULL,
1764 X_ATTRIBUTE9 => NULL,
1765 X_ATTRIBUTE10 => NULL,
1766 X_TEMPLATE_FLAG => NULL,
1767 X_PM_AGREEMENT_REFERENCE => NULL,
1768 X_PM_PRODUCT_CODE => NULL,
1769 -- Bug 2464841 : Added parameters for 11.5 PA-J certification.
1770 X_OWNING_ORGANIZATION_ID => NULL,
1771 X_AGREEMENT_CURRENCY_CODE => pa_currency.get_currency_code,
1772 X_INVOICE_LIMIT_FLAG => nvl(g_award_rec.invoice_limit_flag, 'N'), /*Bug 6642901*/
1773 X_ORG_ID => g_award_rec.org_id
1774 );
1775
1776 P_Agreement_Id := L_Agreement_Id;
1777 END create_agreement;
1778
1779 -- +++++++++++++++++
1780 PROCEDURE insert_award_record ( X_return_status in out NOCOPY varchar2) IS
1781
1782 l_row_id varchar2(50) ;
1783 L_agreement_id NUMBER ;
1784 BEGIN
1785 select gms_awards_s.NEXTVAL
1786 into g_award_rec.award_id
1787 from DUAL ;
1788
1789 create_agreement(L_agreement_id ) ;
1790
1791 g_award_rec.agreement_id := L_agreement_id ;
1792
1793 INSERT into gms_awards_all
1794 (
1795 AWARD_ID ,
1796 AWARD_NUMBER ,
1797 LAST_UPDATE_DATE ,
1798 LAST_UPDATED_BY ,
1799 CREATION_DATE ,
1800 CREATED_BY ,
1801 LAST_UPDATE_LOGIN ,
1802 AWARD_SHORT_NAME ,
1803 AWARD_FULL_NAME ,
1804 FUNDING_SOURCE_ID ,
1805 START_DATE_ACTIVE ,
1806 END_DATE_ACTIVE ,
1807 CLOSE_DATE ,
1808 FUNDING_SOURCE_AWARD_NUMBER ,
1809 AWARD_PURPOSE_CODE ,
1810 STATUS ,
1811 ALLOWABLE_SCHEDULE_ID ,
1812 IDC_SCHEDULE_ID ,
1813 REVENUE_DISTRIBUTION_RULE ,
1814 BILLING_FREQUENCY ,
1815 BILLING_DISTRIBUTION_RULE ,
1816 BILLING_FORMAT ,
1817 BILLING_TERM ,
1818 AWARD_PROJECT_ID ,
1819 AGREEMENT_ID ,
1820 AWARD_TEMPLATE_FLAG ,
1821 PREAWARD_DATE ,
1822 AWARD_MANAGER_ID ,
1823 REQUEST_ID ,
1824 PROGRAM_APPLICATION_ID ,
1825 PROGRAM_ID ,
1826 PROGRAM_UPDATE_DATE ,
1827 AGENCY_SPECIFIC_FORM ,
1828 BILL_TO_CUSTOMER_ID ,
1829 TRANSACTION_NUMBER ,
1830 AMOUNT_TYPE ,
1831 BOUNDARY_CODE ,
1832 FUND_CONTROL_LEVEL_AWARD ,
1833 FUND_CONTROL_LEVEL_TASK ,
1834 FUND_CONTROL_LEVEL_RES_GRP ,
1835 FUND_CONTROL_LEVEL_RES ,
1836 ATTRIBUTE_CATEGORY ,
1837 ATTRIBUTE1 ,
1838 ATTRIBUTE2 ,
1839 ATTRIBUTE3 ,
1840 ATTRIBUTE4 ,
1841 ATTRIBUTE5 ,
1842 ATTRIBUTE6 ,
1843 ATTRIBUTE7 ,
1844 ATTRIBUTE8 ,
1845 ATTRIBUTE9 ,
1846 ATTRIBUTE10 ,
1847 ATTRIBUTE11 ,
1848 ATTRIBUTE12 ,
1849 ATTRIBUTE13 ,
1850 ATTRIBUTE14 ,
1851 ATTRIBUTE15 ,
1852 ATTRIBUTE16 ,
1853 ATTRIBUTE17 ,
1854 ATTRIBUTE18 ,
1855 ATTRIBUTE19 ,
1856 ATTRIBUTE20 ,
1857 ATTRIBUTE21 ,
1858 ATTRIBUTE22 ,
1859 ATTRIBUTE23 ,
1860 ATTRIBUTE24 ,
1861 ATTRIBUTE25 ,
1862 TEMPLATE_START_DATE_ACTIVE ,
1863 TEMPLATE_END_DATE_ACTIVE ,
1864 TYPE ,
1865 ORG_ID ,
1866 COST_IND_SCH_FIXED_DATE ,
1867 LABOR_INVOICE_FORMAT_ID ,
1868 NON_LABOR_INVOICE_FORMAT_ID ,
1869 BILL_TO_ADDRESS_ID ,
1870 SHIP_TO_ADDRESS_ID ,
1871 LOC_BILL_TO_ADDRESS_ID ,
1872 LOC_SHIP_TO_ADDRESS_ID ,
1873 AWARD_ORGANIZATION_ID ,
1874 HARD_LIMIT_FLAG ,
1875 INVOICE_LIMIT_FLAG , /*Bug 6642901*/
1876 BILLING_OFFSET ,
1877 BILLING_CYCLE_ID ,
1878 PROPOSAL_ID ,
1879 BUDGET_WF_ENABLED_FLAG
1880 )
1881 values
1882 (
1883 g_award_rec.AWARD_ID ,
1884 g_award_rec.AWARD_NUMBER ,
1885 g_award_rec.LAST_UPDATE_DATE ,
1886 g_award_rec.LAST_UPDATED_BY ,
1887 g_award_rec.CREATION_DATE ,
1888 g_award_rec.CREATED_BY ,
1889 g_award_rec.LAST_UPDATE_LOGIN ,
1890 g_award_rec.AWARD_SHORT_NAME ,
1891 g_award_rec.AWARD_FULL_NAME ,
1892 g_award_rec.FUNDING_SOURCE_ID ,
1893 g_award_rec.START_DATE_ACTIVE ,
1894 g_award_rec.END_DATE_ACTIVE ,
1895 g_award_rec.CLOSE_DATE ,
1896 g_award_rec.FUNDING_SOURCE_AWARD_NUMBER ,
1897 g_award_rec.AWARD_PURPOSE_CODE ,
1898 g_award_rec.STATUS ,
1899 g_award_rec.ALLOWABLE_SCHEDULE_ID ,
1900 g_award_rec.IDC_SCHEDULE_ID ,
1901 g_award_rec.REVENUE_DISTRIBUTION_RULE ,
1902 g_award_rec.BILLING_FREQUENCY ,
1903 g_award_rec.BILLING_DISTRIBUTION_RULE ,
1904 g_award_rec.BILLING_FORMAT ,
1905 g_award_rec.BILLING_TERM ,
1906 g_award_rec.AWARD_PROJECT_ID ,
1907 g_award_rec.AGREEMENT_ID ,
1908 g_award_rec.AWARD_TEMPLATE_FLAG ,
1909 g_award_rec.PREAWARD_DATE ,
1910 g_award_rec.AWARD_MANAGER_ID ,
1911 g_award_rec.REQUEST_ID ,
1912 g_award_rec.PROGRAM_APPLICATION_ID ,
1913 g_award_rec.PROGRAM_ID ,
1914 g_award_rec.PROGRAM_UPDATE_DATE ,
1915 g_award_rec.AGENCY_SPECIFIC_FORM ,
1916 g_award_rec.BILL_TO_CUSTOMER_ID ,
1917 g_award_rec.TRANSACTION_NUMBER ,
1918 g_award_rec.AMOUNT_TYPE ,
1919 g_award_rec.BOUNDARY_CODE ,
1920 g_award_rec.FUND_CONTROL_LEVEL_AWARD ,
1921 g_award_rec.FUND_CONTROL_LEVEL_TASK ,
1922 g_award_rec.FUND_CONTROL_LEVEL_RES_GRP ,
1923 g_award_rec.FUND_CONTROL_LEVEL_RES ,
1924 g_award_rec.ATTRIBUTE_CATEGORY ,
1925 g_award_rec.ATTRIBUTE1 ,
1926 g_award_rec.ATTRIBUTE2 ,
1927 g_award_rec.ATTRIBUTE3 ,
1928 g_award_rec.ATTRIBUTE4 ,
1929 g_award_rec.ATTRIBUTE5 ,
1930 g_award_rec.ATTRIBUTE6 ,
1931 g_award_rec.ATTRIBUTE7 ,
1932 g_award_rec.ATTRIBUTE8 ,
1933 g_award_rec.ATTRIBUTE9 ,
1934 g_award_rec.ATTRIBUTE10 ,
1935 g_award_rec.ATTRIBUTE11 ,
1936 g_award_rec.ATTRIBUTE12 ,
1937 g_award_rec.ATTRIBUTE13 ,
1938 g_award_rec.ATTRIBUTE14 ,
1939 g_award_rec.ATTRIBUTE15 ,
1940 g_award_rec.ATTRIBUTE16 ,
1941 g_award_rec.ATTRIBUTE17 ,
1942 g_award_rec.ATTRIBUTE18 ,
1943 g_award_rec.ATTRIBUTE19 ,
1944 g_award_rec.ATTRIBUTE20 ,
1945 g_award_rec.ATTRIBUTE21 ,
1946 g_award_rec.ATTRIBUTE22 ,
1947 g_award_rec.ATTRIBUTE23 ,
1948 g_award_rec.ATTRIBUTE24 ,
1949 g_award_rec.ATTRIBUTE25 ,
1950 g_award_rec.TEMPLATE_START_DATE_ACTIVE ,
1951 g_award_rec.TEMPLATE_END_DATE_ACTIVE ,
1952 g_award_rec.TYPE ,
1953 g_award_rec.ORG_ID ,
1954 g_award_rec.COST_IND_SCH_FIXED_DATE ,
1955 g_award_rec.LABOR_INVOICE_FORMAT_ID ,
1956 g_award_rec.NON_LABOR_INVOICE_FORMAT_ID ,
1957 g_award_rec.BILL_TO_ADDRESS_ID ,
1958 g_award_rec.SHIP_TO_ADDRESS_ID ,
1959 g_award_rec.LOC_BILL_TO_ADDRESS_ID ,
1960 g_award_rec.LOC_SHIP_TO_ADDRESS_ID ,
1961 g_award_rec.AWARD_ORGANIZATION_ID ,
1962 g_award_rec.HARD_LIMIT_FLAG ,
1963 g_award_rec.INVOICE_LIMIT_FLAG , /*Bug 6642901*/
1964 g_award_rec.BILLING_OFFSET ,
1965 g_award_rec.BILLING_CYCLE_ID ,
1966 g_award_rec.proposal_id ,
1967 g_award_rec.BUDGET_WF_ENABLED_FLAG
1968 );
1969 END insert_award_record ;
1970 --**************************************************************
1971 -- Bug Fix for Bug 3076921
1972 -- The following procedure verifies the existence of a structure
1973 -- for the award project template. If it exists, the same project structure is
1974 -- used to copy to the newly created award project, If not the following procedure
1975 -- creates a structure for the award project template.
1976 -- The structure for the award project template is mandatory from PA.K onwards
1977 -- as project creates structure for every project template and uses the same
1978 -- to create a structure while creating a new project, which is copied from
1979 -- the template.
1980 --***************************************************************
1981 PROCEDURE CREATE_AWD_PROJ_TEMPLATE_STRUC(X_award_project_id IN Number,
1982 X_return_status OUT NOCOPY VARCHAR2 ) IS
1983
1984 l_struct_exists varchar2(1) := 'N';
1985 l_awd_proj_temp pa_projects_all%rowtype;
1986 l_msg_count NUMBER;
1987 l_msg_data VARCHAR2(2000);
1988 l_return_status VARCHAR2(1);
1989
1990
1991 CURSOR c_awd_proj_temp is
1992 Select * from pa_projects_all
1993 where project_id = x_award_project_id;
1994
1995 CURSOR c_struc_exists is
1996 Select 'Y' from pa_proj_elements
1997 where project_id = x_award_project_id;
1998
1999 Begin
2000 -- Verify whether a structure is already existing for the award project template.
2001
2002 Open c_struc_exists;
2003 Fetch c_struc_exists into l_struct_exists;
2004 Close c_struc_exists;
2005
2006 If l_struct_exists = 'N' then
2007
2008 -- Fetch the award project record.
2009 open c_awd_proj_temp;
2010 fetch c_awd_proj_temp into l_awd_proj_temp;
2011 close c_awd_proj_temp;
2012
2013 -- Create structure for the award project template.
2014
2015 PA_PROJ_TASK_STRUC_PUB.CREATE_DEFAULT_STRUCTURE(
2016 p_dest_project_id => x_award_project_id
2017 ,p_dest_project_name => l_awd_proj_temp.name
2018 ,p_dest_project_number => l_awd_proj_temp.segment1
2019 ,p_dest_description => l_awd_proj_temp.description
2020 ,p_struc_type => 'FINANCIAL' --creating only financial structure
2021 ,x_msg_count => l_msg_count
2022 ,x_msg_data => l_msg_data
2023 ,x_return_status => l_return_status );
2024
2025
2026 IF l_Return_Status <> 'S' then
2027 RAISE FND_API.G_EXC_ERROR;
2028 END IF;
2029
2030 -- Create Options for the award project template.
2031 insert into pa_project_options
2032 (project_id,
2033 option_code,
2034 last_update_date,
2035 last_updated_by,
2036 creation_date,
2037 created_by,
2038 last_update_login)
2039 select
2040 x_award_project_id,
2041 option_code,
2042 SYSDATE,
2043 fnd_global.user_id,
2044 SYSDATE ,
2045 fnd_global.user_id,
2046 fnd_global.login_id
2047 from pa_options
2048 where option_code not in ( 'STRUCTURES', 'STRUCTURES_SS' );
2049
2050 -- Create structure for the award project template's task.
2051
2052 PA_PROJ_TASK_STRUC_PUB.CREATE_DEFAULT_TASK_STRUCTURE(
2053 p_project_id => x_award_project_id
2054 ,p_struc_type => 'FINANCIAL'
2055 ,x_msg_count => l_msg_count
2056 ,x_msg_data => l_msg_data
2057 ,x_return_status => l_return_status );
2058
2059 end if;
2060
2061 EXCEPTION
2062 WHEN FND_API.G_EXC_ERROR then
2063 X_return_status := l_return_status;
2064
2065 WHEN OTHERS then
2066 X_return_status := l_return_status;
2067
2068 End CREATE_AWD_PROJ_TEMPLATE_STRUC;
2069
2070 --**************************************************************
2071 -- Bug FIx 3076921
2072 -- For the PA.K rollup patch certification we started making use of the customer account relationship feature.
2073 -- 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
2074 -- column of the pa_project_customers.
2075 -- We will not update teh record with the latest, by overriding the existing customer_id.
2076 -- For this the columns bill_to_customer_id and ship_to_customer_id need to be defined as overridable.
2077 -- This change can be done in the implementaitons form, but that forces us to come up with a data fix
2078 -- for the existing implementations. So adding that check before creating an award. Thus we dont need any
2079 -- data fix script and all the changes will be centralized in the multi funding package.
2080
2081 Procedure MARK_FIELDS_AS_OVERRIDABLE(x_award_project_id IN NUMBER,
2082 x_field_name IN VARCHAR2,
2083 x_return_status OUT NOCOPY VARCHAR2) IS
2084 CURSOR c_bill_to_customer_overridable IS
2085 SELECT project_id
2086 FROM pa_project_copy_overrides
2087 WHERE project_id = x_award_project_id
2088 AND field_name = x_field_name;
2089
2090 l_project_id NUMBER;
2091 l_msg_count NUMBER;
2092 l_msg_data VARCHAR2(2000);
2093 l_return_status VARCHAR2(1);
2094
2095 BEGIN
2096
2097 OPEN c_bill_to_customer_overridable;
2098 FETCH c_bill_to_customer_overridable INTO l_project_id;
2099 CLOSE c_bill_to_customer_overridable;
2100
2101 IF l_project_id IS NULL AND x_field_name = 'BILL_TO_CUSTOMER' THEN
2102 PA_PROJ_TEMPLATE_SETUP_PUB.ADD_QUICK_ENTRY_FIELD( p_api_version => 1.0,
2103 p_init_msg_list => FND_API.G_TRUE,
2104 p_commit => FND_API.G_FALSE,
2105 p_validate_only => FND_API.G_FALSE,
2106 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2107 p_calling_module => 'FORM',
2108 p_debug_mode => 'N',
2109 p_max_msg_count => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2110 p_project_id => x_award_project_id,
2111 p_sort_order => 70,
2112 p_field_name => 'BILL_TO_CUSTOMER',
2113 p_field_meaning => 'Bill To Customer Name',
2114 p_specification => 'Primary',
2115 p_limiting_value => 'Primary',
2116 p_prompt => 'Bill To Customer Name',
2117 p_required_flag => 'N',
2118 x_return_status => l_return_status,
2119 x_msg_count => l_msg_count,
2120 x_msg_data => l_msg_data);
2121
2122
2123 IF l_Return_Status < 'S' then
2124 RAISE FND_API.G_EXC_ERROR;
2125 END IF;
2126
2127 END IF;
2128
2129
2130 IF l_project_id IS NULL AND x_field_name = 'SHIP_TO_CUSTOMER' THEN
2131 PA_PROJ_TEMPLATE_SETUP_PUB.ADD_QUICK_ENTRY_FIELD( p_api_version => 1.0,
2132 p_init_msg_list => FND_API.G_TRUE,
2133 p_commit => FND_API.G_FALSE,
2134 p_validate_only => FND_API.G_FALSE,
2135 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2136 p_calling_module => 'FORM',
2137 p_debug_mode => 'N',
2138 p_max_msg_count => PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2139 p_project_id => x_award_project_id,
2140 p_sort_order => 80,
2141 p_field_name => 'SHIP_TO_CUSTOMER',
2142 p_field_meaning => 'Ship To Customer Name',
2143 p_specification => 'Primary',
2144 p_limiting_value => 'Primary',
2145 p_prompt => 'Ship To Customer Name',
2146 p_required_flag => 'N',
2147 x_return_status => l_return_status,
2148 x_msg_count => l_msg_count,
2149 x_msg_data => l_msg_data);
2150
2151
2152 IF l_Return_Status < 'S' then
2153 RAISE FND_API.G_EXC_ERROR;
2154 END IF;
2155
2156 END IF;
2157 EXCEPTION
2158 WHEN FND_API.G_EXC_ERROR then
2159 X_Return_Status := l_return_status;
2160
2161 WHEN OTHERS then
2162 X_Return_Status := l_return_status;
2163 END MARK_FIELDS_AS_OVERRIDABLE;
2164 -- End of Bug Fix for Bug 3076921
2165
2166 -- +++++++++++++++++
2167 PROCEDURE create_award_project ( X_return_status IN out NOCOPY varchar2,
2168 p_setup_award_project_id IN NUMBER) is
2169
2170 L_Err_Code VARCHAR2(1) := NULL;
2171 L_Project_IN_REC PA_PROJECT_PUB.PROJECT_IN_REC_TYPE;
2172 L_Project_OUT_REC PA_PROJECT_PUB.PROJECT_OUT_REC_TYPE;
2173 L_Key_Members_IN_REC PA_PROJECT_PUB.PROJECT_ROLE_REC_TYPE;
2174 L_Tasks_IN_REC PA_PROJECT_PUB.TASK_IN_REC_TYPE;
2175
2176 L_Key_Members_IN_TBL PA_PROJECT_PUB.PROJECT_ROLE_TBL_TYPE;
2177 L_Class_Categories_IN_TBL PA_PROJECT_PUB.CLASS_CATEGORY_TBL_TYPE;
2178 L_Tasks_In_TBL PA_PROJECT_PUB.TASK_IN_TBL_TYPE;
2179 L_Tasks_Out_TBL PA_PROJECT_PUB.TASK_OUT_TBL_TYPE;
2180 L_default_org_id NUMBER ;
2181
2182 L_Workflow_Started varchar2(1) ;
2183
2184 /*** Bug 3576717 **/
2185 L_Deliverable_IN_TBL PA_PROJECT_PUB.DELIVERABLE_IN_TBL_TYPE;
2186 L_Deliverable_Action_IN_TBL PA_PROJECT_PUB.ACTION_IN_TBL_TYPE;
2187
2188 -- BUG 3650374
2189 --L_Deliverable_OUT_TBL PA_PROJECT_PUB.DELIVERABLE_OUT_TBL_TYPE;
2190 --L_Deliverable_Action_OUT_TBL PA_PROJECT_PUB.ACTION_OUT_TBL_TYPE;
2191
2192 Begin
2193 -- Bug Fix for Bug 3076921
2194 -- Need to verify and create a structure for the award project template.
2195 -- by calling the CREATE_AWD_PROJ_TEMPLATE_STRUC.
2196
2197 CREATE_AWD_PROJ_TEMPLATE_STRUC(x_award_project_id => p_setup_award_project_id,
2198 x_return_status => x_return_status);
2199
2200 IF X_Return_Status <> 'S' THEN
2201 RETURN;
2202 END IF;
2203
2204 MARK_FIELDS_AS_OVERRIDABLE(x_award_project_id => p_setup_award_project_id,
2205 x_field_name => 'BILL_TO_CUSTOMER',
2206 x_return_status => x_return_status );
2207
2208 IF X_Return_Status <> 'S' THEN
2209 RETURN;
2210 END IF;
2211
2212 MARK_FIELDS_AS_OVERRIDABLE(x_award_project_id =>p_setup_award_project_id ,
2213 x_field_name => 'SHIP_TO_CUSTOMER',
2214 x_return_status => x_return_status );
2215
2216 IF X_Return_Status <> 'S' THEN
2217 RETURN;
2218 END IF;
2219
2220 -- End of Bug Fix for Bug 3076921
2221 L_default_org_id := g_award_rec.org_id ;
2222 G_Product_Code := 'GMS';
2223
2224 L_Project_IN_REC.PM_PROJECT_REFERENCE := g_award_rec.award_number ;
2225 L_Project_IN_REC.PA_PROJECT_NUMBER := g_award_rec.award_number ;
2226 L_Project_IN_REC.PROJECT_NAME := g_award_rec.award_number ;
2227 L_Project_IN_REC.CREATED_FROM_PROJECT_ID := p_setup_award_project_id;
2228 L_Project_IN_REC.CARRYING_OUT_ORGANIZATION_ID := g_award_rec.award_organization_id;
2229 L_Project_IN_REC.START_DATE := g_award_rec.start_date_active;
2230 L_Project_IN_REC.COMPLETION_DATE := g_award_rec.end_date_active ;
2231
2232 L_Project_IN_REC.DISTRIBUTION_RULE := 'EVENT/EVENT';
2233 L_Project_IN_REC.PROJECT_RELATIONSHIP_CODE := 'PRIMARY';
2234 L_Project_IN_REC.PROJECT_CURRENCY_CODE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
2235
2236 L_Project_IN_REC.CUSTOMER_ID := g_award_rec.funding_source_id ; --bug 3076921
2237 -- Bug Fix 3076921. Load the Table
2238 L_PROJECT_IN_REC.BILL_TO_CUSTOMER_ID := G_bill_contact_rec.customer_id;
2239 L_PROJECT_IN_REC.SHIP_TO_CUSTOMER_ID := G_bill_contact_rec.customer_id;
2240
2241
2242 L_Key_Members_IN_REC.PERSON_ID := g_award_rec.award_manager_id ;
2243 L_Key_Members_IN_REC.PROJECT_ROLE_TYPE := 'PROJECT MANAGER';
2244 L_Key_Members_IN_REC.START_DATE := g_award_rec.start_date_active; -- Bug 2534936
2245 L_Key_Members_IN_REC.END_DATE := g_award_rec.end_date_active; -- Bug 2534936
2246
2247 L_Tasks_IN_REC.task_name := g_award_rec.award_number||'-'||'Tsk1'; --L_Task_Name;
2248 L_Tasks_IN_REC.TASK_START_DATE := g_award_rec.start_date_active;
2249 L_Tasks_IN_REC.TASK_COMPLETION_DATE := g_award_rec.end_date_active;
2250 L_Tasks_IN_REC.pa_task_number := g_award_rec.award_number||'-'||'T1'; --L_Task_Number;
2251 L_Tasks_IN_REC.cost_ind_rate_sch_id := g_award_rec.IDC_Schedule_Id;
2252 L_Tasks_IN_REC.pm_task_reference := g_award_rec.award_number;
2253 L_Tasks_IN_REC.chargeable_flag := 'N';
2254
2255 L_Tasks_In_TBL(1) := L_Tasks_IN_REC;
2256 L_Key_Members_IN_TBL(1) := L_Key_Members_IN_REC;
2257
2258 PA_PROJECT_PUB.CREATE_PROJECT(p_api_version_number => 1.0,
2259 p_init_msg_list => 'F',
2260 p_msg_count => G_msg_count,
2261 p_msg_data => G_Msg_Data,
2262 p_return_status => X_Return_Status,
2263 p_project_in => L_Project_IN_REC,
2264 p_project_out => L_Project_OUT_REC,
2265 p_pm_product_code => G_Product_Code,
2266 p_key_members => L_Key_Members_IN_TBL,
2267 p_class_categories => L_Class_Categories_IN_TBL,
2268 p_tasks_in => L_Tasks_IN_TBL,
2269 p_tasks_out => L_Tasks_OUT_TBL,
2270 p_workflow_started => L_Workflow_Started,
2271 P_commit => FND_API.G_FALSE,
2272 /** Bug 3576717 **/
2273 P_deliverables_in => L_Deliverable_IN_TBL,
2274 --P_deliverables_out => L_Deliverable_OUT_TBL, (3650374)
2275 P_deliverable_actions_in => L_Deliverable_Action_IN_TBL
2276 --P_deliverable_actions_out=> L_Deliverable_Action_OUT_TBL (3650374)
2277 );
2278 IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS then
2279
2280 -- -------------------------------------------------
2281 -- Create award project failed So return to the create
2282 -- award. Failure status is allready set by
2283 -- project pub API.
2284 -- -------------------------------------------------
2285 return ;
2286 ELSE
2287 L_Err_Code := X_Return_Status;
2288 g_award_rec.award_project_id := L_Project_OUT_REC.PA_PROJECT_ID;
2289 END IF;
2290
2291 --
2292 -- Update projects for additional informations.
2293
2294 --
2295
2296 Update PA_PROJECTS_ALL
2297 set cost_ind_rate_sch_id = g_award_rec.IDC_Schedule_Id,
2298 cost_ind_sch_fixed_date = g_award_rec.cost_ind_sch_fixed_date,
2299 labor_invoice_format_id = g_award_rec.Labor_Invoice_Format_Id,
2300 non_labor_invoice_format_Id = g_award_rec.Non_Labor_Invoice_Format_Id,
2301 name = g_award_rec.award_number,
2302 segment1 = g_award_rec.award_number,
2303 billing_cycle_id = g_award_rec.Billing_Cycle_Id,
2304 billing_offset = NVL(g_award_rec.Billing_Offset,0) ,
2305 last_update_date = sysdate,
2306 last_updated_by = fnd_global.user_id,
2307 last_update_login = fnd_global.login_id
2308 where project_id = g_award_rec.award_project_id ;
2309
2310 --
2311 -- Update project customers.
2312 --
2313
2314 update PA_PROJECT_Customers
2315 set BILL_TO_ADDRESS_ID = NVL(g_award_rec.bill_To_Address_Id, bill_TO_ADDRESS_ID ),
2316 SHIP_TO_ADDRESS_ID = NVL(g_award_rec.Ship_To_Address_Id, SHIP_TO_ADDRESS_ID )
2317 ,LAST_UPDATE_DATE = SYSDATE
2318 ,LAST_UPDATED_BY = fnd_global.user_id
2319 ,LAST_UPDATE_LOGIN = fnd_global.login_id
2320 where project_id = g_award_rec.award_project_id
2321 and customer_id = L_Project_IN_REC.CUSTOMER_ID ;
2322
2323 END CREATE_AWARD_PROJECT;
2324
2325 -------------------------------------------------------------------------
2326 -- CREATE AWARD_PROJECT ENDS HERE
2327 -------------------------------------------------------------------------
2328
2329
2330 -- ===============================================================================
2331 -- COPY_AWARD :
2332 -- Copy award has all the parameters that we have in quick entry for award.
2333 -- The ID's in the table are replaced by corresponding value. Users must
2334 -- provide decode values instead of code values.
2335 -- P_return_status : S- Success,
2336 -- E- Business Rule Validation error
2337 -- U- Unexpected Error
2338 -- P_API_VERSION_NUMBER : 1.0
2339 -- ===============================================================================
2340 -- How Copy award works
2341 -- Copy award has two calling points
2342 -- 1. Public API
2343 -- 2. Oracle internal sources
2344 -- Copy award rely on independent components of the award api.
2345 -- The independant components are as follows :
2346 -- A. Create_award
2347 -- B. Create_contacts
2348 -- C. Create_personnel
2349 -- D. Create_reference_numbers
2350 -- E. Create_notifications.
2351 -- F. create_terms_and_conditions.
2352 -- Copy AWARD integrate other components together and create award and
2353 -- other dependent child records.
2354 -- The parameters passed may be null. Only parameters that has value
2355 -- will be overwritten to the base award columns.
2356 -- ================================================================================
2357 PROCEDURE COPY_AWARD(
2358 X_MSG_COUNT IN OUT NOCOPY NUMBER,
2359 X_MSG_DATA IN OUT NOCOPY VARCHAR2,
2360 X_return_status IN OUT NOCOPY VARCHAR2,
2361 P_AWARD_NUMBER IN OUT NOCOPY VARCHAR2 ,
2362 X_AWARD_ID OUT NOCOPY NUMBER,
2363 P_CALLING_MODULE IN VARCHAR2,
2364 P_API_VERSION_NUMBER IN NUMBER,
2365 P_AWARD_BASE_ID IN NUMBER,
2366 P_AWARD_SHORT_NAME IN VARCHAR2 DEFAULT NULL,
2367 P_AWARD_FULL_NAME IN VARCHAR2 DEFAULT NULL,
2368 P_AWARD_START_DATE IN DATE DEFAULT NULL,
2369 P_AWARD_END_DATE IN DATE DEFAULT NULL,
2370 P_AWARD_CLOSE_DATE IN DATE DEFAULT NULL,
2371 P_PREAWARD_DATE IN DATE DEFAULT NULL,
2372 P_AWARD_PURPOSE_CODE IN VARCHAR2 DEFAULT NULL,
2373 P_AWARD_STATUS_CODE IN VARCHAR2 DEFAULT NULL,
2374 P_AWARD_MANAGER_ID IN NUMBER DEFAULT NULL,
2375 P_AWARD_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
2376 P_FUNDING_SOURCE_ID IN NUMBER DEFAULT NULL,
2377 P_FUNDING_SOURCE_AWARD_NUM IN VARCHAR2 DEFAULT NULL,
2378 P_ALLOWABLE_SCHEDULE_ID IN VARCHAR2 DEFAULT NULL,
2379 P_INDIRECT_SCHEDULE_ID IN VARCHAR2 DEFAULT NULL,
2380 P_COST_IND_SCH_FIXED_DATE IN DATE DEFAULT NULL,
2381 P_REVENUE_DISTRIBUTION_RULE IN VARCHAR2 DEFAULT NULL,
2382 P_BILLING_DISTRIBUTION_RULE IN VARCHAR2 DEFAULT NULL,
2383 P_BILLING_TERM_ID IN NUMBER DEFAULT NULL,
2384 P_LABOR_INVOICE_FORMAT_ID IN VARCHAR2 DEFAULT NULL,
2385 P_NON_LABOR_INVOICE_FORMAT_ID IN VARCHAR2 DEFAULT NULL,
2386 P_BILLING_CYCLE_ID IN VARCHAR2 DEFAULT NULL,
2387 P_AMOUNT_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
2388 P_BOUNDARY_CODE IN VARCHAR2 DEFAULT NULL,
2389 P_AGREEMENT_TYPE IN VARCHAR2 DEFAULT NULL,
2390 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
2391 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
2392 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
2393 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
2394 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
2395 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
2396 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
2397 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
2398 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
2399 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
2400 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
2401 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
2402 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
2403 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
2404 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
2405 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
2406 P_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
2407 P_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
2408 P_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
2409 P_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
2410 P_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
2411 P_ATTRIBUTE21 IN VARCHAR2 DEFAULT NULL,
2412 P_ATTRIBUTE22 IN VARCHAR2 DEFAULT NULL,
2413 P_ATTRIBUTE23 IN VARCHAR2 DEFAULT NULL,
2414 P_ATTRIBUTE24 IN VARCHAR2 DEFAULT NULL,
2415 P_ATTRIBUTE25 IN VARCHAR2 DEFAULT NULL,
2416 P_PROPOSAL_ID IN NUMBER DEFAULT NULL) IS
2417
2418 L_api_name varchar2(30) := 'GMS_AWARD_PVT.COPY_AWARD';
2419 l_award_rec gms_awards_all%ROWTYPE ;
2420
2421 l_awards_contacts gms_awards_contacts%ROWTYPE ;
2422 l_report_rec gms_default_reports%ROWTYPE ;
2423 l_refnum_rec gms_reference_numbers%ROWTYPE ;
2424 l_personnel_rec gms_personnel%ROWTYPE ;
2425 l_termscond_rec gms_awards_terms_conditions%ROWTYPE ;
2426
2427 l_base_fund_src_id gms_awards_all.funding_source_id%TYPE ;
2428
2429 l_default_report_id NUMBER ;
2430 l_term_id NUMBER ;
2431
2432 l_validate BOOLEAN := FALSE ;
2433 l_row_id varchar2(45) ;
2434
2435 -- =========
2436 -- c_award
2437 -- Fetch the source award record.
2438 -- This will give us the details of the rest of the columns
2439 -- to copy award.
2440 -- =========
2441 cursor c_award is
2442 select *
2443 from gms_awards_all
2444 where award_id = p_award_base_id ;
2445
2446 -- ======
2447 -- gms_awards_contacts
2448 -- Copy Contacts cursor.
2449 -- ======
2450 -- Bug 2244805. Copy all contacts not just primary contacts
2451 cursor c_award_contacts is
2452 select *
2453 from gms_awards_contacts
2454 where award_id = p_award_base_id;
2455 -- and primary_flag <> 'Y' Bug 2244805
2456 -- and l_base_fund_src_id = g_award_rec.funding_source_id ; -- Bug 2244805
2457
2458 -- ======
2459 -- c_default_reports
2460 -- Copu Reports cursor.
2461 -- ======
2462
2463 cursor c_default_reports is
2464 select *
2465 from gms_default_reports
2466 where award_id = p_award_base_id
2467 and l_base_fund_src_id = g_award_rec.funding_source_id ;
2468
2469 -- ======
2470 -- c_gms_personnel
2471 -- Copy c_gms_personnel cursor
2472 -- ======
2473 cursor c_gms_personnel is
2474 select *
2475 from gms_personnel
2476 where award_id = p_award_base_id
2477 and award_role <> 'AM';
2478
2479 -- ======
2480 -- c_reference_numbers
2481 -- Copy c_reference_numbers cursor.
2482 -- ======
2483 cursor c_reference_numbers is
2484 select *
2485 from gms_reference_numbers
2486 where award_id = p_award_base_id;
2487
2488 -- ======
2489 -- c_terms_conditions
2490 -- Copy Terms and conditions cursor.
2491 -- ======
2492 cursor c_terms_conditions is
2493 select *
2494 from gms_awards_terms_conditions
2495 where award_id = p_award_base_id;
2496
2497 -- ========================================
2498 -- copy award Main Logic.
2499 -- ========================================
2500 BEGIN
2501 -- Initialize the message stack.
2502 -- -----------------------------
2503 init_message_stack ;
2504
2505 G_msg_count := X_msg_count ;
2506 G_msg_data := X_MSG_DATA ;
2507 G_calling_module := P_CALLING_MODULE ;
2508
2509 -- ============
2510 -- Initialize the return status.
2511 -- ============
2512 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
2513 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
2514
2515 X_return_status := FND_API.G_RET_STS_SUCCESS ;
2516
2517 END IF ;
2518
2519 SAVEPOINT copy_award_pvt ;
2520
2521 G_stage := 'FND_API.Compatible_API_Call' ;
2522
2523 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
2524 p_api_version_number ,
2525 l_api_name ,
2526 G_pkg_name )
2527 THEN
2528 RAISE e_ver_mismatch ;
2529 END IF ;
2530
2531 G_stage := 'AWARD RECORD' ;
2532
2533
2534 -- ======
2535 -- fetch Base record here.
2536 -- ======
2537
2538 OPEN C_AWARD ;
2539 FETCH C_AWARD into L_AWARD_REC ;
2540 close c_award ;
2541
2542 l_base_fund_src_id := l_award_rec.funding_source_id ;
2543 -- ======
2544 -- Override Columns
2545 -- ======
2546
2547 --
2548 -- Currently we only create award and not
2549 -- template. So this is understood
2550 -- award_template_flag must be DEFERRED
2551 --
2552 l_award_rec.award_template_flag := 'DEFERRED' ;
2553 l_award_rec.award_id := NULL ;
2554 l_award_rec.award_project_id := NULL ;
2555
2556
2557 -- ========
2558 -- Override logic
2559 -- We look for not null arguments and
2560 -- copy them to base award columns.
2561 -- Calling program will provide us columns
2562 -- that should be copied to base award columns
2563 -- ========
2564
2565 -- IF p_award_number is not NULL then -- Bug 2652716
2566 l_award_rec.award_number := p_award_number ;
2567 -- end if ;
2568
2569 if P_AWARD_SHORT_NAME is not null then
2570 l_award_rec.award_short_name := P_AWARD_SHORT_NAME ;
2571 end if ;
2572
2573 IF P_AWARD_FULL_NAME is not null then
2574 l_award_rec.AWARD_FULL_NAME := P_AWARD_FULL_NAME ;
2575 end if ;
2576
2577 IF P_AWARD_START_DATE is not null then
2578 l_award_rec.start_date_active := P_AWARD_START_DATE ;
2579 end if ;
2580
2581 IF P_AWARD_END_DATE is not null then
2582 l_award_rec.end_date_active := P_AWARD_END_DATE ;
2583 end if ;
2584
2585 IF P_AWARD_CLOSE_DATE is not NULL then
2586 l_award_rec.CLOSE_DATE := P_AWARD_CLOSE_DATE ;
2587 end if ;
2588 IF P_PREAWARD_DATE is not NULL then
2589 l_award_rec.PREAWARD_DATE := P_PREAWARD_DATE ;
2590 end if ;
2591
2592 IF P_AWARD_PURPOSE_CODE is not null then
2593 l_award_rec.AWARD_PURPOSE_CODE := P_AWARD_PURPOSE_CODE ;
2594 end if ;
2595 IF P_AWARD_STATUS_CODE is not NULL then
2596 l_award_rec.STATUS := P_AWARD_STATUS_CODE ;
2597 end if ;
2598
2599 if P_AWARD_MANAGER_ID is not null then
2600 l_award_rec.AWARD_MANAGER_ID := P_AWARD_MANAGER_ID ;
2601 end if ;
2602
2603 IF P_AWARD_ORGANIZATION_ID is not null then
2604 l_award_rec.AWARD_ORGANIZATION_ID := P_AWARD_ORGANIZATION_ID ;
2605 end if ;
2606
2607 IF P_FUNDING_SOURCE_ID is not null then
2608 l_award_rec.FUNDING_SOURCE_ID := P_FUNDING_SOURCE_ID ;
2609 end if ;
2610
2611 IF P_FUNDING_SOURCE_AWARD_NUM is not null then
2612 l_award_rec.funding_source_award_number := P_FUNDING_SOURCE_AWARD_NUM ;
2613 end if ;
2614
2615 IF P_ALLOWABLE_SCHEDULE_ID is not null then
2616 l_award_rec.allowable_schedule_id := P_ALLOWABLE_SCHEDULE_ID ;
2617 end if ;
2618
2619 IF P_INDIRECT_SCHEDULE_ID is not null then
2620 l_award_rec.idc_schedule_id := P_INDIRECT_SCHEDULE_ID ;
2621 end if ;
2622
2623 IF P_COST_IND_SCH_FIXED_DATE is not null then
2624 l_award_rec.COST_IND_SCH_FIXED_DATE := P_COST_IND_SCH_FIXED_DATE ;
2625 end if ;
2626
2627 IF P_REVENUE_DISTRIBUTION_RULE is not null then
2628 l_award_rec.REVENUE_DISTRIBUTION_RULE := P_REVENUE_DISTRIBUTION_RULE ;
2629 end if ;
2630
2631 IF P_BILLING_DISTRIBUTION_RULE is not null then
2632 l_award_rec.BILLING_DISTRIBUTION_RULE := P_BILLING_DISTRIBUTION_RULE ;
2633 end if ;
2634
2635 IF P_BILLING_TERM_ID is not null then
2636 l_award_rec.BILLING_TERM := P_BILLING_TERM_ID ;
2637 end if ;
2638
2639 IF P_LABOR_INVOICE_FORMAT_ID is not null then
2640 l_award_rec.LABOR_INVOICE_FORMAT_ID := P_LABOR_INVOICE_FORMAT_ID ;
2641 end if ;
2642
2643 IF P_NON_LABOR_INVOICE_FORMAT_ID is not null then
2644 l_award_rec.NON_LABOR_INVOICE_FORMAT_ID := P_NON_LABOR_INVOICE_FORMAT_ID ;
2645 end if ;
2646
2647 if P_BILLING_CYCLE_ID is not null then
2648 l_award_rec.BILLING_CYCLE_ID := P_BILLING_CYCLE_ID ;
2649 end if ;
2650
2651 IF P_AMOUNT_TYPE_CODE is not null then
2652 l_award_rec.AMOUNT_TYPE := P_AMOUNT_TYPE_CODE ;
2653 end if ;
2654
2655 IF P_BOUNDARY_CODE is not null then
2656 l_award_rec.BOUNDARY_CODE := P_BOUNDARY_CODE ;
2657 end if ;
2658
2659 IF P_AGREEMENT_TYPE is not null then
2660 l_award_rec.TYPE := P_AGREEMENT_TYPE ;
2661 end if ;
2662
2663 IF P_PROPOSAL_ID is not null then
2664 l_award_rec.PROPOSAL_ID := P_PROPOSAL_ID ;
2665 end if ;
2666
2667 -- =====
2668 -- Override columns done.
2669 -- =====
2670
2671 -- ==========================
2672 -- Populate flexfields column
2673 -- ==========================
2674 IF P_ATTRIBUTE_CATEGORY is not null then
2675 l_award_rec.ATTRIBUTE_CATEGORY := P_ATTRIBUTE_CATEGORY ;
2676 END IF ;
2677
2678 IF p_attribute1 is not null then
2679 l_award_rec.attribute1 := P_ATTRIBUTE1 ;
2680 end if ;
2681
2682 IF p_attribute2 is not null then
2683 l_award_rec.attribute2 := P_ATTRIBUTE2 ;
2684 end if ;
2685
2686 IF p_attribute3 is not null then
2687 l_award_rec.attribute3 := P_ATTRIBUTE3 ;
2688 end if ;
2689
2690 IF p_attribute4 is not null then
2691 l_award_rec.attribute4 := P_ATTRIBUTE4 ;
2692 end if ;
2693
2694 IF p_attribute5 is not null then
2695 l_award_rec.attribute5 := P_ATTRIBUTE5 ;
2696 end if ;
2697
2698 IF p_attribute6 is not null then
2699 l_award_rec.attribute6 := P_ATTRIBUTE6 ;
2700 end if ;
2701
2702 IF p_attribute7 is not null then
2703 l_award_rec.attribute7 := P_ATTRIBUTE7 ;
2704 end if ;
2705
2706 IF p_attribute8 is not null then
2707 l_award_rec.attribute8 := P_ATTRIBUTE8 ;
2708 end if ;
2709
2710 IF p_attribute9 is not null then
2711 l_award_rec.attribute9 := P_ATTRIBUTE9 ;
2712 end if ;
2713
2714 IF p_attribute10 is not null then
2715 l_award_rec.attribute10 := P_ATTRIBUTE10 ;
2716 end if ;
2717
2718 IF p_attribute11 is not null then
2719 l_award_rec.attribute11 := P_ATTRIBUTE11 ;
2720 end if ;
2721
2722 IF p_attribute12 is not null then
2723 l_award_rec.attribute12 := P_ATTRIBUTE12 ;
2724 end if ;
2725
2726 IF p_attribute13 is not null then
2727 l_award_rec.attribute13 := P_ATTRIBUTE13 ;
2728 end if ;
2729
2730 IF p_attribute14 is not null then
2731 l_award_rec.attribute14 := P_ATTRIBUTE14 ;
2732 end if ;
2733
2734 IF p_attribute15 is not null then
2735 l_award_rec.attribute15 := P_ATTRIBUTE15 ;
2736 end if ;
2737 IF p_attribute16 is not null then
2738 l_award_rec.attribute16 := P_ATTRIBUTE16 ;
2739 end if ;
2740
2741 IF p_attribute17 is not null then
2742 l_award_rec.attribute17 := P_ATTRIBUTE17 ;
2743 end if ;
2744 IF p_attribute18 is not null then
2745 l_award_rec.attribute18 := P_ATTRIBUTE18 ;
2746 end if ;
2747 IF p_attribute19 is not null then
2748 l_award_rec.attribute19 := P_ATTRIBUTE19 ;
2749 end if ;
2750 IF p_attribute20 is not null then
2751 l_award_rec.attribute20 := P_ATTRIBUTE20 ;
2752 end if ;
2753 IF p_attribute21 is not null then
2754 l_award_rec.attribute21 := P_ATTRIBUTE21 ;
2755 end if ;
2756 IF p_attribute22 is not null then
2757 l_award_rec.attribute22 := P_ATTRIBUTE22 ;
2758 end if ;
2759 IF p_attribute23 is not null then
2760 l_award_rec.attribute23 := P_ATTRIBUTE23 ;
2761 end if ;
2762 IF p_attribute24 is not null then
2763 l_award_rec.attribute24 := P_ATTRIBUTE24 ;
2764 end if ;
2765 IF p_attribute25 is not null then
2766 l_award_rec.attribute25 := P_ATTRIBUTE25 ;
2767 end if ;
2768
2769
2770 --
2771 -- Create award 1st.
2772 -- We use our create_award program unit
2773 -- to create award .
2774 --
2775
2776 G_stage := 'gms_award_pvt.create_award' ;
2777
2778 create_award( X_msg_count,
2779 X_MSG_DATA ,
2780 X_return_status ,
2781 L_ROW_ID ,
2782 X_AWARD_ID ,
2783 P_CALLING_MODULE,
2784 P_API_VERSION_NUMBER,
2785 L_AWARD_REC ) ;
2786
2787 -- =================
2788 -- Make sure that X_return_status is success before continue.
2789 -- =================
2790 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2791
2792 -- ********* ERROR Return Here ********
2793 Raise fnd_api.g_exc_error ;
2794 END IF ;
2795
2796 p_award_number := g_award_rec.award_number ;
2797
2798 G_stage := 'Copy Contacts begins ' ;
2799
2800 -- ========
2801 -- Copy Award Contacts Now
2802 -- ========
2803
2804 -- Bug 2244805 Added if condition and delete statement
2805 -- Requirement: If funding source has changed from funding source in base award
2806 -- then award contacts (bill to and ship to only) should be taken from receivables.
2807 -- If funding source has not changed from funding source in base award, then simply
2808 -- copy ALL contacts from the base award (not from receivables)
2809 --
2810 -- The bill to and ship to contacts are created (based on default contacts in receivables)
2811 -- during create_award. Therefore, if the funding source has changed, then there is no need
2812 -- to copy any more contacts. If on the other hand, the funding source has not changed from
2813 -- that existing in the base award, we first delete the default contacts created from
2814 -- receivables by create_award and then copy contacts existing in the base award.
2815
2816 --Bug : 3455542 : Commented by Sanjay Banerjee
2817 --create_award procedure is alredy creating contacts, removing these contacts and re-creating
2818 --does not make sense. Also, create_contact is not just a copy procedure, it does the validation too.
2819 --We need to create contacts based on the funding_source given. Even if the funding source is same,
2820 --as before, we have to query to get the latest bill_to and ship_to address_ids.
2821 --
2822 /*****
2823 if (l_base_fund_src_id = g_award_rec.funding_source_id) then
2824
2825 delete from gms_awards_contacts
2826 where award_id = g_award_rec.award_id;
2827
2828 for l_rec in c_award_contacts
2829 LOOP
2830 l_awards_contacts.award_id := g_award_rec.award_id ;
2831 l_awards_contacts.contact_id := l_rec.contact_id ;
2832 l_awards_contacts.primary_flag := l_rec.primary_flag ;
2833 l_awards_contacts.customer_id := l_rec.customer_id ;
2834 l_awards_contacts.usage_code := l_rec.usage_code ;
2835 l_awards_contacts.last_update_date := SYSDATE ;
2836 l_awards_contacts.last_updated_by := l_rec.last_updated_by ;
2837 l_awards_contacts.creation_date := SYSDATE ;
2838 l_awards_contacts.created_by := l_rec.created_by ;
2839 l_awards_contacts.last_update_login := l_rec.last_update_login ;
2840
2841 -- Create Contacts ...
2842 --- ================
2843 G_stage := 'Copy Contacts :'||l_rec.contact_id||' '||l_rec.customer_id||' '
2844 ||l_rec.usage_code ;
2845 create_contact ( x_msg_count,
2846 x_msg_data,
2847 x_return_status,
2848 l_row_id,
2849 p_calling_module,
2850 p_api_version_number,
2851 l_validate,
2852 l_awards_contacts);
2853
2854 -- =================
2855 -- Make sure that X_return_status is success before continue.
2856 -- =================
2857 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2858 Raise fnd_api.g_exc_error ;
2859 END IF ;
2860
2861 END LOOP ;
2862 end if;
2863 *****/
2864 -- =========
2865 -- Copy Default Reports
2866 -- =========
2867 G_stage := 'Copy Default reports begins' ;
2868
2869 for L_REC in c_default_reports
2870 LOOP
2871 l_report_rec := l_rec ;
2872 l_report_rec.default_report_id := l_rec.default_report_id ;
2873 l_report_rec.report_template_id := l_rec.report_template_id ;
2874 l_report_rec.award_id := g_award_rec.award_id ;
2875 l_report_rec.last_update_date := l_rec.last_update_date ;
2876 l_report_rec.last_updated_by := l_rec.last_updated_by ;
2877 l_report_rec.creation_date := l_rec.creation_date ;
2878 l_report_rec.created_by := l_rec.created_by ;
2879 l_report_rec.last_update_login := l_rec.last_update_login ;
2880 l_report_rec.frequency := l_rec.frequency ;
2881 l_report_rec.due_within_days := l_rec.due_within_days ;
2882 l_report_rec.site_use_id := l_rec.site_use_id ;
2883 l_report_rec.copy_number := l_rec.copy_number;
2884 l_report_rec.request_id := l_rec.request_id ;
2885 l_report_rec.program_application_id := l_rec.program_application_id;
2886 l_report_rec.program_id := l_rec.program_id ;
2887 l_report_rec.program_update_date := l_rec.program_update_date ;
2888 l_report_rec.attribute_category := l_rec.attribute_category ;
2889 l_report_rec.attribute1 := l_rec.attribute1 ;
2890 l_report_rec.attribute2 := l_rec.attribute2 ;
2891 l_report_rec.attribute3 := l_rec.attribute3 ;
2892 l_report_rec.attribute4 := l_rec.attribute4 ;
2893 l_report_rec.attribute5 := l_rec.attribute5 ;
2894 l_report_rec.attribute6 := l_rec.attribute6 ;
2895 l_report_rec.attribute7 := l_rec.attribute7 ;
2896 l_report_rec.attribute8 := l_rec.attribute8 ;
2897 l_report_rec.attribute9 := l_rec.attribute9 ;
2898 l_report_rec.attribute10 := l_rec.attribute10 ;
2899 l_report_rec.attribute11 := l_rec.attribute11 ;
2900 l_report_rec.attribute12 := l_rec.attribute12 ;
2901 l_report_rec.attribute13 := l_rec.attribute13 ;
2902 l_report_rec.attribute14 := l_rec.attribute14 ;
2903 l_report_rec.attribute15 := l_rec.attribute15 ;
2904
2905 create_report ( x_msg_count,
2906 x_msg_data,
2907 x_return_status,
2908 l_default_report_id,
2909 l_row_id,
2910 p_calling_module,
2911 p_api_version_number,
2912 l_validate,
2913 l_report_rec ) ;
2914
2915 G_stage := 'Copy Reports :'||l_default_report_id ;
2916
2917 -- =================
2918 -- Make sure that X_return_status is success before continue.
2919 -- =================
2920 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2921 Raise fnd_api.g_exc_error ;
2922 END IF ;
2923
2924 END LOOP ;
2925
2926 G_stage := 'Copy Personnel starts here' ;
2927
2928 -- =====
2929 -- Copy Personnel Details
2930 -- =====
2931 FOR L_REC in c_gms_personnel LOOP
2932
2933 l_personnel_rec.award_id := g_award_rec.award_id ;
2934 l_personnel_rec.person_id := l_rec.person_id ;
2935 l_personnel_rec.award_role := l_rec.award_role ;
2936 l_personnel_rec.last_update_date := SYSDATE ;
2937 l_personnel_rec.last_updated_by := l_rec.last_updated_by ;
2938 l_personnel_rec.creation_date := l_rec.creation_date ;
2939 l_personnel_rec.created_by := l_rec.created_by ;
2940 l_personnel_rec.last_update_login := l_rec.last_update_login ;
2941 l_personnel_rec.start_date_active := l_rec.start_date_active ;
2942 l_personnel_rec.end_date_active := l_rec.end_date_active ;
2943 l_personnel_rec.personnel_id := l_rec.personnel_id ;
2944 l_personnel_rec.required_flag := l_rec.required_flag ;
2945 --l_personnel_rec.project_party_id := l_rec.project_party_id ;
2946
2947 G_stage := 'Copy Personnel :'||l_rec.person_id||' '||l_rec.award_role ;
2948
2949 create_personnel ( x_msg_count,
2950 x_msg_data,
2951 x_return_status,
2952 l_row_id,
2953 p_calling_module,
2954 p_api_version_number,
2955 l_validate,
2956 l_personnel_rec ) ;
2957
2958 -- =================
2959 -- Make sure that X_return_status is success before continue.
2960 -- =================
2961 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2962 Raise fnd_api.g_exc_error ;
2963 END IF ;
2964
2965 END LOOP ;
2966
2967 -- =====
2968 -- Copy Reference Numbers
2969 -- =====
2970
2971 g_stage := 'Copy Reference Numbers begins here ' ;
2972
2973 FOR l_rec in c_reference_numbers LOOP
2974
2975 l_refnum_rec.award_id := g_award_rec.award_id ;
2976 l_refnum_rec.type := l_rec.type ;
2977 l_refnum_rec.last_update_date := l_rec.last_update_date ;
2978 l_refnum_rec.last_updated_by := l_rec.last_updated_by ;
2979 l_refnum_rec.creation_date := l_rec.creation_date ;
2980 l_refnum_rec.created_by := l_rec.created_by ;
2981 l_refnum_rec.last_update_login := l_rec.last_update_login ;
2982 l_refnum_rec.value := l_rec.value ;
2983 l_refnum_rec.required_flag := l_rec.required_flag ;
2984
2985 G_stage := 'Copy reference Number :'||l_refnum_rec.type||' '||l_refnum_rec.value ;
2986
2987 create_reference_number ( x_msg_count,
2988 x_msg_data,
2989 x_return_status,
2990 l_row_id,
2991 p_calling_module,
2992 p_api_version_number,
2993 l_validate,
2994 l_refnum_rec ) ;
2995 -- =================
2996 -- Make sure that X_return_status is success before continue.
2997 -- =================
2998 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
2999 Raise fnd_api.g_exc_error ;
3000 END IF ;
3001
3002 END LOOP ;
3003
3004 -- =====
3005 -- Copy Terms and conditions.
3006 -- =====
3007
3008 FOR l_rec in c_terms_conditions LOOP
3009 l_termscond_rec.award_id := g_award_rec.award_id ;
3010 l_termscond_rec.category_id := l_rec.category_id ;
3011 l_termscond_rec.term_id := l_rec.term_id ;
3012 l_termscond_rec.last_update_date := l_rec.last_update_date ;
3013 l_termscond_rec.last_updated_by := l_rec.last_updated_by ;
3014 l_termscond_rec.creation_date := l_rec.creation_date ;
3015 l_termscond_rec.created_by := l_rec.created_by ;
3016 l_termscond_rec.last_update_login := l_rec.last_update_login ;
3017 l_termscond_rec.operand := l_rec.operand ;
3018 l_termscond_rec.value := l_rec.value ;
3019
3020 create_term_condition ( x_msg_count,
3021 x_msg_data,
3022 x_return_status,
3023 -- Removed this parameter as we don't return this value.
3024 -- l_term_id,
3025 l_row_id,
3026 p_calling_module,
3027 p_api_version_number,
3028 l_validate,
3029 l_termscond_rec ) ;
3030 -- =================
3031 -- Make sure that X_return_status is success before continue.
3032 -- =================
3033 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
3034 Raise fnd_api.g_exc_error ;
3035 END IF ;
3036
3037 END LOOP ;
3038
3039 --
3040 -- Create Personnel Records.
3041 --
3042 --
3043
3044 -- =================
3045 -- Make sure that X_return_status is success before continue.
3046 -- =================
3047 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
3048 Raise fnd_api.g_exc_error ;
3049 END IF ;
3050
3051 reset_message_flag ;
3052
3053 G_stage := 'Award Copied Successfully' ;
3054 EXCEPTION
3055 WHEN E_VER_MISMATCH THEN
3056 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
3057 p_token1 => 'SUPVER',
3058 P_VAL1 => g_api_version_number) ;
3059 set_return_status(X_return_status, 'B' ) ;
3060 x_msg_count := G_msg_count ;
3061 x_msg_data := G_msg_data ;
3062
3063 WHEN FND_API.G_EXC_ERROR THEN
3064 ROLLBACK TO copy_award_pvt ;
3065 set_return_status(X_return_status, 'B' ) ;
3066 x_msg_count := G_msg_count ;
3067 x_msg_data := G_msg_data ;
3068 WHEN OTHERS THEN
3069 ROLLBACK TO copy_award_pvt ;
3070 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3071 FND_MSG_PUB.add_exc_msg ( p_pkg_name => G_PKG_NAME
3072 , p_procedure_name => l_api_name );
3073 FND_MSG_PUB.Count_And_Get
3074 ( p_count => x_msg_count ,
3075 p_data => x_msg_data );
3076
3077 END COPY_AWARD ;
3078
3079 -- +++++++++++++++++
3080
3081
3082 -- ===========================================================
3083 -- Create Installments.
3084 -- ===========================================================
3085
3086 PROCEDURE CREATE_INSTALLMENT
3087 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
3088 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
3089 X_return_status IN OUT NOCOPY VARCHAR2 ,
3090 X_ROW_ID OUT NOCOPY VARCHAR2 ,
3091 X_INSTALLMENT_ID OUT NOCOPY NUMBER ,
3092 P_CALLING_MODULE IN VARCHAR2 ,
3093 P_API_VERSION_NUMBER IN NUMBER,
3094 P_validate IN BOOLEAN DEFAULT TRUE ,
3095 P_INSTALLMENT_REC IN GMS_INSTALLMENTS%ROWTYPE
3096 ) IS
3097
3098 l_award_start_date_active DATE ;
3099 l_award_end_date_active DATE ;
3100 l_award_close_date DATE ;
3101 l_dummy VARCHAR2(1) ;
3102 l_rowid VARCHAR2(45) ;
3103 l_api_name VARCHAR2(30) := 'CREATE_INSTALLMENT';
3104
3105
3106 CURSOR l_installment_type_csr (p_installment_type IN VARCHAR2 ) IS
3107 SELECT 'X'
3108 FROM gms_lookups
3109 WHERE lookup_type = 'INSTALLMENT_TYPE'
3110 AND lookup_code = p_installment_type ;
3111
3112 -- This is to check the uniqueness of the reference number for that award.
3113 CURSOR l_installment_num_csr(p_installment_num IN VARCHAR2 ,p_award_id IN NUMBER ) IS
3114 SELECT 'X'
3115 FROM gms_installments
3116 WHERE installment_num = p_installment_num
3117 AND award_id = p_award_id ;
3118
3119 CURSOR l_award_rec_csr(p_award_id IN NUMBER ) IS
3120 SELECT start_date_active , end_date_active,close_date
3121 FROM gms_awards_all
3122 WHERE award_id = p_award_id ;
3123
3124
3125 PROCEDURE check_installment_required (p_validate IN BOOLEAN ,
3126 X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3127 l_error BOOLEAN ;
3128
3129 BEGIN
3130 IF not p_validate then
3131 return ;
3132 end if ;
3133
3134 l_error := FALSE ;
3135
3136 IF G_installment_rec.Award_Id IS NULL THEN
3137 -- ------------------------------
3138 -- MSG: AWARD_ID_NULL
3139 -- ------------------------------
3140 l_error := TRUE ;
3141 add_message_to_stack( P_label => 'GMS_AWD_ID_MISSING' ) ;
3142
3143 END IF ;
3144
3145 IF g_installment_rec.installment_num IS NULL THEN
3146 -- ------------------------------
3147 -- MSG: INSTALLEMNT_NUM_IS_NULL
3148 -- ------------------------------
3149 add_message_to_stack( P_label => 'GMS_INST_NUMBER_NULL' ) ;
3150 l_error := TRUE ;
3151 END IF ;
3152
3153 IF g_installment_rec.type IS NULL THEN
3154 -- ------------------------------
3155 -- MSG: INSTALLMENT_TYPE_IS_NULL
3156 -- ------------------------------
3157 add_message_to_stack( P_label => 'GMS_AWD_INST_TYPE_MISSING' ) ;
3158 l_error := TRUE ;
3159 END IF ;
3160
3161 IF g_installment_rec.start_date_active IS NULL THEN
3162 -- ------------------------------
3163 -- MSG: INSTALLMENT_START_DATE_ACTIVE IS NULL
3164 -- ------------------------------
3165 add_message_to_stack( P_label => 'GMS_INST_START_DATE_NULL' ) ;
3166 l_error := TRUE ;
3167 END IF ;
3168
3169 IF g_installment_rec.end_date_active IS NULL THEN
3170 -- ------------------------------
3171 -- MSG: INSTALLMENT_END_DATE_ACTIVE IS NULL
3172 -- ------------------------------
3173 add_message_to_stack( P_label => 'GMS_INST_END_DATE_NULL' ) ;
3174 l_error := TRUE ;
3175 END IF ;
3176
3177 IF g_installment_rec.close_date IS NULL THEN
3178 -- ------------------------------
3179 -- MSG: INSTALLMENT_CLOSE_DATE IS NULL
3180 -- -----------------------------
3181 add_message_to_stack( P_label => 'GMS_INST_CLOSE_DATE_NULL' ) ;
3182 l_error := TRUE ;
3183 END IF ;
3184
3185 IF g_installment_rec.direct_cost IS NOT NULL AND
3186 g_installment_rec.direct_cost <= 0 THEN
3187 add_message_to_stack( P_label => 'GMS_INST_DIR_COST_NOT_GT_ZERO' ) ;
3188 l_error := TRUE ;
3189 END IF ;
3190
3191 -- This validation is NOT required as -ve values also are accepted for indirect_cost.
3192 /* IF g_installment_rec.indirect_cost IS NOT NULL AND
3193 g_installment_rec.indirect_cost <= 0 THEN
3194 add_message_to_stack( P_label => 'GMS_INST_IND_COST_NOT_GT_ZERO' ) ;
3195 l_error := TRUE ;
3196 END IF ; */
3197
3198 -- ===============================
3199 -- Validate FlexFields
3200 -- ===============================
3201 IF g_installment_rec.attribute_category is not NULL THEN
3202
3203 fnd_flex_descval.set_context_value(g_installment_rec.attribute_category) ;
3204
3205 fnd_flex_descval.set_column_value('ATTRIBUTE1',g_installment_rec.attribute1) ;
3206 fnd_flex_descval.set_column_value('ATTRIBUTE2',g_installment_rec.attribute2) ;
3207 fnd_flex_descval.set_column_value('ATTRIBUTE3',g_installment_rec.attribute3) ;
3208 fnd_flex_descval.set_column_value('ATTRIBUTE4',g_installment_rec.attribute4) ;
3209 fnd_flex_descval.set_column_value('ATTRIBUTE5',g_installment_rec.attribute5) ;
3210 fnd_flex_descval.set_column_value('ATTRIBUTE6',g_installment_rec.attribute6) ;
3211 fnd_flex_descval.set_column_value('ATTRIBUTE7',g_installment_rec.attribute7) ;
3212 fnd_flex_descval.set_column_value('ATTRIBUTE8',g_installment_rec.attribute8) ;
3213 fnd_flex_descval.set_column_value('ATTRIBUTE9',g_installment_rec.attribute9) ;
3214 fnd_flex_descval.set_column_value('ATTRIBUTE10',g_installment_rec.attribute10) ;
3215 fnd_flex_descval.set_column_value('ATTRIBUTE11',g_installment_rec.attribute11) ;
3216 fnd_flex_descval.set_column_value('ATTRIBUTE12',g_installment_rec.attribute12) ;
3217 fnd_flex_descval.set_column_value('ATTRIBUTE13',g_installment_rec.attribute13) ;
3218 fnd_flex_descval.set_column_value('ATTRIBUTE14',g_installment_rec.attribute14) ;
3219 fnd_flex_descval.set_column_value('ATTRIBUTE15',g_installment_rec.attribute15) ;
3220
3221 IF (FND_FLEX_DESCVAL.validate_desccols ('GMS' ,'GMS_AWARDS_DESC_FLEX')) then
3222 -- Validation Passed
3223 NULL ;
3224 ELSE
3225 add_message_to_stack( P_label => 'GMS_AWD_FLEX_INVALID' ) ;
3226 fnd_msg_pub.add_exc_msg(p_pkg_name => 'GMS_AWARD_PVT',
3227 p_procedure_name => 'CREATE_INSTALLMENT',
3228 p_error_text => substr(FND_FLEX_DESCVAL.error_message,1,240)) ;
3229 l_error := TRUE ;
3230
3231 END IF ;
3232
3233 END IF ;
3234
3235 -- ------------------------------
3236 -- End of flex fields validations.
3237 -- ------------------------------
3238
3239
3240 IF L_error THEN
3241 set_return_status ( X_return_status, 'B') ;
3242 END IF ;
3243 END check_installment_required ;
3244
3245 BEGIN
3246
3247 -- Initialize the message stack.
3248 -- -----------------------------
3249 init_message_stack;
3250
3251 G_msg_count := X_msg_count ;
3252 G_msg_data := X_MSG_DATA ;
3253 G_calling_module := P_CALLING_MODULE ;
3254
3255 -- ============
3256 -- Initialize the return status.
3257 -- ============
3258 IF NVL(x_return_status , FND_API.G_RET_STS_SUCCESS) NOT IN
3259 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
3260 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3261 END IF ;
3262
3263 SAVEPOINT create_installment_pvt ;
3264
3265 G_stage := 'FND_API.Compatible_API_Call' ;
3266
3267 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3268 p_api_version_number ,
3269 l_api_name ,
3270 G_pkg_name ) THEN
3271 RAISE e_ver_mismatch ;
3272 END IF ;
3273
3274
3275 G_installment_rec := p_installment_rec ;
3276
3277 check_installment_required(p_validate,x_return_status) ;
3278
3279 IF G_installment_rec.award_id IS NOT NULL THEN
3280 OPEN l_award_rec_csr(G_installment_rec.award_id ) ;
3281 FETCH l_award_rec_csr
3282 INTO l_award_start_date_active , l_award_end_date_active, l_award_close_date ;
3283
3284 IF l_award_rec_csr%NOTFOUND THEN
3285 add_message_to_stack( P_label => 'GMS_AWD_NOT_EXISTS' ) ;
3286 set_return_status ( X_return_status, 'B') ;
3287 END IF ;
3288 CLOSE l_award_rec_csr ;
3289
3290 OPEN l_installment_num_csr (G_installment_rec.installment_num,G_installment_rec.award_id );
3291 FETCH l_installment_num_csr INTO l_dummy ;
3292
3293 -- This is to check the uniqueness of the reference number for that award.
3294 -- Changed as part of testing.
3295
3296 IF l_installment_num_csr%FOUND THEN
3297 add_message_to_stack( P_label => 'GMS_INST_NUMBER_INVALID' ) ;
3298 set_return_status ( X_return_status, 'B') ;
3299 END IF ;
3300 CLOSE l_installment_num_csr ;
3301
3302 END IF ;
3303
3304 OPEN l_installment_type_csr (G_installment_rec.type );
3305 FETCH l_installment_type_csr INTO l_dummy ;
3306 IF l_installment_type_csr%NOTFOUND THEN
3307 add_message_to_stack( P_label => 'GMS_INST_TYPE_INVALID' ) ;
3308 set_return_status ( X_return_status, 'B') ;
3309 END IF ;
3310 CLOSE l_installment_type_csr ;
3311
3312
3313
3314 G_stage := 'FND_API.Verify_Instal_start_date_with_Award_start_date';
3315
3316 IF G_installment_rec.start_date_active IS NOT NULL
3317 and l_award_start_date_active IS NOT NULL
3318 and G_installment_rec.start_date_active < l_award_start_date_active THEN
3319 add_message_to_stack( P_label => 'GMS_INS_ST_DATE_BF_AWD_ST_DATE') ;
3320 set_return_status ( X_return_status, 'B') ;
3321 END IF ;
3322
3323
3324 G_stage := 'FND_API.Verify_Instal_start_date_with_Instal_end_date';
3325
3326 IF G_installment_rec.start_date_active IS NOT NULL
3327 and G_installment_rec.end_date_active IS NOT NULL
3328 and G_installment_rec.start_date_active > G_installment_rec.end_date_active THEN
3329 add_message_to_stack( P_label => 'GMS_INST_ENDATE_BEF_INS_STDATE');
3330 set_return_status ( X_return_status, 'B') ;
3331 END IF ;
3332
3333 G_stage := 'FND_API.Verify_Instal_end_date_with_Award_end_date';
3334
3335 IF G_installment_rec.start_date_active IS NOT NULL
3336 and G_installment_rec.end_date_active IS NOT NULL
3337 and G_installment_rec.end_date_active > l_award_end_date_active THEN
3338 add_message_to_stack( P_label =>'GMS_INS_ENDATE_AFTER_AWENDATE');
3339 set_return_status ( X_return_status, 'B') ;
3340 END IF ;
3341
3342 G_stage := 'FND_API.Verify_Instal_end_date_with_Instal_close_date';
3343
3344 IF G_installment_rec.end_date_active IS NOT NULL
3345 and G_installment_rec.close_date IS NOT NULL
3346 and G_installment_rec.end_date_active > G_installment_rec.close_date THEN
3347 add_message_to_stack( P_label => 'GMS_INS_CLOSEDATE_BEF_ENDDATE') ;
3348 set_return_status ( X_return_status, 'B') ;
3349 END IF ;
3350
3351 G_stage := 'FND_API.Verify_Instal_close_date_with_Award_close_date';
3352
3353 IF G_installment_rec.close_date IS NOT NULL
3354 and l_award_close_date is NOT NULL
3355 and G_installment_rec.close_date > l_award_close_date THEN
3356 add_message_to_stack( P_label => 'GMS_INS_CL_DATE_>_AWD_CL_DATE') ;
3357 set_return_status ( X_return_status, 'B') ;
3358 END IF ;
3359
3360 -- If the return_status <> g_ret_sts_success then we don't proceed with the Inserts.
3361
3362 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
3363 RAISE FND_API.G_EXC_ERROR;
3364 END IF ;
3365
3366
3367 SELECT gms_installments_s.nextval
3368 INTO G_installment_rec.installment_id
3369 FROM dual;
3370
3371 G_stage := 'FND_API.Create_Installment' ;
3372
3373 gms_installments_pkg.insert_row(
3374 X_ROWID => l_rowid,
3375 X_INSTALLMENT_ID => G_installment_rec.installment_id,
3376 X_INSTALLMENT_NUM => G_installment_rec.installment_num,
3377 X_AWARD_ID => G_installment_rec.award_id,
3378 X_START_DATE_ACTIVE => G_installment_rec.start_date_active,
3379 X_END_DATE_ACTIVE => G_installment_rec.end_date_active,
3380 X_CLOSE_DATE => G_installment_rec.close_date,
3381 X_DIRECT_COST => G_installment_rec.direct_cost,
3382 X_INDIRECT_COST => G_installment_rec.indirect_cost,
3383 X_ACTIVE_FLAG => G_installment_rec.active_flag,
3384 X_BILLABLE_FLAG => G_installment_rec.billable_flag,
3385 X_TYPE => G_installment_rec.type,
3386 X_ISSUE_DATE => G_installment_rec.issue_date,
3387 X_DESCRIPTION => G_installment_rec.description,
3388 X_ATTRIBUTE_CATEGORY =>G_installment_rec.attribute_category,
3389 X_ATTRIBUTE1 =>G_installment_rec.attribute1,
3390 X_ATTRIBUTE2 =>G_installment_rec.attribute2,
3391 X_ATTRIBUTE3 =>G_installment_rec.attribute3,
3392 X_ATTRIBUTE4 =>G_installment_rec.attribute4,
3393 X_ATTRIBUTE5 =>G_installment_rec.attribute5,
3394 X_ATTRIBUTE6 =>G_installment_rec.attribute6,
3395 X_ATTRIBUTE7 =>G_installment_rec.attribute7,
3396 X_ATTRIBUTE8 =>G_installment_rec.attribute8,
3397 X_ATTRIBUTE9 =>G_installment_rec.attribute9,
3398 X_ATTRIBUTE10 =>G_installment_rec.attribute10,
3399 X_ATTRIBUTE11 =>G_installment_rec.attribute11,
3400 X_ATTRIBUTE12 =>G_installment_rec.attribute12,
3401 X_ATTRIBUTE13 =>G_installment_rec.attribute13,
3402 X_ATTRIBUTE14 =>G_installment_rec.attribute14,
3403 X_ATTRIBUTE15 =>G_installment_rec.attribute15,
3404 X_MODE => 'R' );
3405
3406 G_stage := 'FND_API.Create_Notification' ;
3407
3408 IF G_installment_rec.active_flag = 'Y' THEN
3409 gms_wf_pkg.init_installment_wf(x_installment_id=> G_installment_rec.installment_id ,
3410 x_award_id => G_installment_rec.award_id);
3411 END IF ;
3412
3413 -- ===================================================
3414 -- Fix for bug 2231131. - Installment Id not returned.
3415 -- ===================================================
3416
3417 X_INSTALLMENT_ID := G_installment_rec.installment_id ;
3418
3419 G_installment_rec := NULL ; -- Resetting the record varible.
3420
3421 G_stage := 'Installment Created Successfully' ;
3422
3423 EXCEPTION
3424 WHEN E_VER_MISMATCH THEN
3425 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
3426 p_token1 => 'SUPVER',
3427 P_VAL1 => g_api_version_number) ;
3428 set_return_status(X_return_status, 'B' ) ;
3429 x_msg_count := G_msg_count ;
3430 x_msg_data := G_msg_data ;
3431
3432 WHEN FND_API.G_EXC_ERROR THEN
3433 G_installment_rec := NULL ; -- Resetting the record vaarible.
3434 ROLLBACK TO create_installment_pvt ;
3435 set_return_status(X_return_status, 'B' ) ;
3436 x_msg_count := G_msg_count ;
3437 x_msg_data := G_msg_data ;
3438 WHEN OTHERS THEN
3439 G_installment_rec := NULL ; -- Resetting the record vaarible.
3440 ROLLBACK TO create_installment_pvt ;
3441 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3442
3443 FND_MSG_PUB.add_exc_msg
3444 ( p_pkg_name => G_PKG_NAME
3445 , p_procedure_name => l_api_name );
3446
3447 FND_MSG_PUB.Count_And_Get
3448 ( p_count => x_msg_count ,
3449 p_data => x_msg_data );
3450
3451
3452
3453 END CREATE_INSTALLMENT ;
3454
3455 -- ----------------------------------------------------------------------------
3456 -- This procedure will create Award Personnel which is used to name and describe
3457 -- the award roles and to specify the effective dates.
3458 -- ----------------------------------------------------------------------------
3459
3460 PROCEDURE CREATE_PERSONNEL
3461 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
3462 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
3463 X_return_status IN OUT NOCOPY VARCHAR2 ,
3464 X_ROW_ID OUT NOCOPY VARCHAR2 ,
3465 P_CALLING_MODULE IN VARCHAR2 ,
3466 P_API_VERSION_NUMBER IN NUMBER ,
3467 P_validate IN BOOLEAN DEFAULT TRUE ,
3468 P_PERSONNEL_REC IN GMS_PERSONNEL%ROWTYPE
3469 ) IS
3470
3471 l_api_name VARCHAR2(30) := 'CREATE_PERSONNEL';
3472 l_start_date_active gms_personnel.start_date_active%TYPE := NULL ;
3473 l_end_date_active gms_personnel.end_date_active%TYPE := NULL ;
3474 l_award_project_id gms_awards_all.award_project_id%TYPE := NULL ;
3475 l_award_template_flag gms_awards_all.award_template_flag%TYPE := NULL ;
3476 l_budget_wf_enabled_flag gms_awards_all.budget_wf_enabled_flag%TYPE := NULL ;
3477 l_rowid varchar2(45) ;
3478
3479
3480 l_error_code VARCHAR2(2000) ;
3481 l_error_stage VARCHAR2(2000) ;
3482 l_dummy VARCHAR2(1) ;
3483
3484 -- This Cursor is used to validate whether the specific person_id (employee_id)
3485 -- is a SYSTEM user or not by checking whether he has any valid assignments between
3486 -- the specific dates.
3487
3488 CURSOR l_full_name_csr(p_person_id IN NUMBER ) IS
3489 SELECT 'X'
3490 from pa_employees p ,
3491 fnd_user u
3492 WHERE
3493 EXISTS (SELECT null FROM per_assignments_f a
3494 WHERE p.person_id = a.person_id
3495 AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
3496 AND a.primary_flag = 'Y'
3497 AND a.assignment_type = 'E')
3498 AND p.person_id = u.employee_id(+)
3499 AND p.person_id = p_person_id ;
3500
3501 -- To validate the incoming award_role parameter.
3502
3503 CURSOR l_award_role_csr(p_award_role IN VARCHAR2 ) IS
3504 SELECT 'X'
3505 FROM gms_lookups g
3506 WHERE lookup_type = 'AWARD_ROLE'
3507 AND g.lookup_code = p_award_role
3508 AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active,trunc(sysdate)) ;
3509
3510 CURSOR l_rec_csr(p_award_id IN NUMBER ) IS
3511 SELECT start_date_active,budget_wf_enabled_flag , award_template_flag,
3512 award_project_id , end_date_active
3513 FROM gms_awards_all
3514 WHERE award_id = p_award_id ;
3515
3516 -- ===============================================================
3517
3518 PROCEDURE Verify_dates( p_validate IN BOOLEAN ,
3519 X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3520 BEGIN
3521 IF not p_validate then
3522 return ;
3523 end if ;
3524
3525 -- verify date range
3526 IF ( G_personnel_rec.start_date_active IS NOT NULL AND
3527 G_personnel_rec.end_date_active IS NOT NULL AND
3528 G_personnel_rec.start_date_active > G_personnel_rec.end_date_active ) THEN
3529
3530 add_message_to_stack( P_label => ('GMS_PER_DATES_INVALID') );
3531 set_return_status ( X_return_status, 'B') ;
3532 END IF;
3533 END Verify_dates ;
3534
3535 -- -------------------------------------------------------------------------------
3536
3537 PROCEDURE Verify_User_Status (p_validate IN BOOLEAN , X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3538
3539 CURSOR l_valid_user_csr IS
3540 SELECT user_id
3541 FROM fnd_user
3542 WHERE employee_id = G_personnel_rec.person_id ;
3543
3544 l_user_id NUMBER ;
3545
3546 BEGIN
3547 IF not p_validate then
3548 return ;
3549 end if ;
3550
3551 IF l_budget_wf_enabled_flag = 'Y' THEN
3552
3553 OPEN l_valid_user_csr ;
3554 FETCH l_valid_user_csr INTO l_user_id ;
3555 IF l_valid_user_csr%NOTFOUND THEN
3556 IF G_personnel_rec.award_role ='AM' then
3557 add_message_to_stack( P_label => ('GMS_FND_USER_NOT_CREATED'));
3558 set_return_status ( X_return_status, 'B') ;
3559 ELSE
3560 add_message_to_stack( P_label => ('GMS_WARN_NOT_FND_USER'));
3561 set_return_status ( X_return_status, 'B') ;
3562 END IF ;
3563 END IF ; -- End if for l_valid_user_csr
3564
3565 CLOSE l_valid_user_csr ;
3566
3567 END IF ; -- End if for l_budget_wf_enabled_flag
3568 END Verify_User_Status ;
3569
3570 -- ==============================================================================================
3571
3572 -- The purpose of this validation is that when we are creating another manager
3573 -- for the award the dates should not overlap. We can create more than one manager
3574 -- without overlaping the dates.
3575
3576 PROCEDURE Verify_Award_Manager_Dates(p_validate IN BOOLEAN , X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3577
3578 CURSOR l_award_manager_csr is
3579 SELECT start_date_active, end_date_active
3580 FROM gms_personnel
3581 WHERE award_id = G_personnel_rec.award_id
3582 AND award_role = 'AM'
3583 ORDER BY start_date_active;
3584
3585 BEGIN
3586 IF not p_validate then
3587 RETURN ;
3588 END IF ;
3589
3590 -- =================================================
3591 -- Only award Manager validations are required here.
3592 -- =================================================
3593 IF G_personnel_rec.award_role <> 'AM' then
3594 return ;
3595 END IF ;
3596
3597 -- ===============================================
3598 -- Award Manager validations starts here.
3599 -- ==============================================
3600
3601 FOR rec_award_manager IN l_award_manager_csr LOOP
3602 IF rec_award_manager.end_date_active IS NULL THEN
3603
3604 IF G_personnel_rec.end_date_active IS NULL THEN
3605 add_message_to_stack( P_label => ('GMS_AW_INVALID_MANAGER_DATES') );
3606 set_return_status ( X_return_status, 'B') ;
3607 END IF; -- end if for G_personnel_rec.end_date_active
3608
3609 END IF; -- End if for rec_award_manager.end_date_active
3610
3611 IF (G_personnel_rec.start_date_active >= rec_award_manager.start_date_active
3612 AND G_personnel_rec.start_date_active <= nvl(rec_award_manager.end_date_active
3613 , G_personnel_rec.end_date_active))
3614 OR (G_personnel_rec.end_date_active <= nvl(rec_award_manager.end_date_active,
3615 G_personnel_rec.end_date_active)
3616 AND G_personnel_rec.end_date_active >= rec_award_manager.start_date_active)
3617 OR (G_personnel_rec.start_date_active <= rec_award_manager.start_date_active
3618 AND G_personnel_rec.end_date_active >= nvl(rec_award_manager.end_date_active, G_personnel_rec.end_date_active)) THEN
3619
3620 add_message_to_stack( P_label => ('GMS_AW_INVALID_MANAGER_DATES') );
3621 set_return_status ( X_return_status, 'B') ;
3622
3623 END IF; -- end if for G_personnel_rec.start_date_active
3624
3625 END LOOP;
3626
3627 END Verify_Award_Manager_Dates ;
3628
3629 -- -------------------------------------------------------------------------------
3630
3631 PROCEDURE check_personnel_required (p_validate IN BOOLEAN ,X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3632
3633 l_error BOOLEAN ;
3634 BEGIN
3635 IF not p_validate then
3636 return ;
3637 end if ;
3638
3639 l_error := FALSE ;
3640
3641 IF G_personnel_rec.award_id IS NULL THEN
3642 l_error := TRUE ;
3643 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
3644 END IF ;
3645
3646 IF G_personnel_rec.person_id IS NULL THEN
3647 l_error := TRUE ;
3648 add_message_to_stack( P_label => 'GMS_PERSON_ID_NULL' ) ;
3649 END IF ;
3650
3651 IF G_personnel_rec.award_role IS NULL THEN
3652 l_error := TRUE ;
3653 add_message_to_stack( P_label => 'GMS_AWD_ROLE_NULL' ) ;
3654 END IF ;
3655
3656
3657 IF l_error THEN
3658 set_return_status ( X_return_status, 'B') ;
3659 END IF ;
3660 END check_personnel_required ;
3661 -- ------------------------------------------------------------------------------------------
3662
3663 PROCEDURE Create_Pa_Key_Member ( p_validate IN BOOLEAN , X_return_status IN OUT NOCOPY VARCHAR2 ) IS
3664
3665 v_null_number NUMBER;
3666 v_null_char VARCHAR2(255);
3667 v_null_date DATE;
3668 x_msg_count NUMBER;
3669 x_msg_data VARCHAR2(255);
3670 x_role_type_id NUMBER;
3671 l_wf_type VARCHAR2(250);
3672 l_wf_item_type VARCHAR2(250);
3673 l_wf_process VARCHAR2(250);
3674 l_assignment_id NUMBER;
3675 l_party_id NUMBER;
3676
3677 BEGIN
3678
3679 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY
3680 ( P_API_VERSION => 1.0
3681 , P_INIT_MSG_LIST => NULL
3682 , P_COMMIT => NULL
3683 , P_VALIDATE_ONLY => NULL
3684 , P_VALIDATION_LEVEL => 100
3685 , P_DEBUG_MODE => 'N'
3686 , P_OBJECT_ID => l_award_project_id
3687 , P_OBJECT_TYPE => 'PA_PROJECTS'
3688 , P_PROJECT_ROLE_ID => 1
3689 , P_PROJECT_ROLE_TYPE => 'PROJECT MANAGER'
3690 , P_RESOURCE_TYPE_ID => 101
3691 , P_RESOURCE_SOURCE_ID => G_personnel_rec.person_id
3692 , P_RESOURCE_NAME => v_null_char
3693 , P_START_DATE_ACTIVE => G_personnel_rec.start_date_active
3694 , P_SCHEDULED_FLAG => 'N'
3695 , P_CALLING_MODULE =>'FORM'
3696 , P_PROJECT_ID => l_award_project_id
3697 , P_PROJECT_END_DATE => l_end_date_active
3698 , P_END_DATE_ACTIVE => G_personnel_rec.end_date_active
3699 , X_PROJECT_PARTY_ID => l_party_id
3700 , X_RESOURCE_ID => v_null_number
3701 , X_WF_TYPE => l_wf_type
3702 , X_WF_ITEM_TYPE => l_wf_item_type
3703 , X_WF_PROCESS => l_wf_process
3704 , X_ASSIGNMENT_ID => l_assignment_id
3705 , X_RETURN_STATUS => x_return_status
3706 , X_MSG_COUNT => x_msg_count
3707 , X_MSG_DATA => x_msg_data
3708 );
3709
3710
3711 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3712 set_return_status ( X_return_status, 'B') ;
3713
3714 END IF ;
3715 END Create_Pa_Key_Member ;
3716 -- -----------------------------------------------------------------------------------------------
3717
3718
3719 BEGIN
3720 -- ----------------------------
3721 -- Initialize the message stack.
3722 -- -----------------------------
3723 init_message_stack;
3724
3725 G_msg_count := x_msg_count ;
3726 G_msg_data := x_MSG_DATA ;
3727 G_calling_module := P_CALLING_MODULE ;
3728
3729 -- =============================
3730 -- Initialize the return status.
3731 -- =============================
3732 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) NOT IN
3733 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
3734 X_return_status := FND_API.G_RET_STS_SUCCESS ;
3735 END IF ;
3736
3737 SAVEPOINT create_personnel_pvt ;
3738
3739 G_stage := 'FND_API.Compatible_API_Call' ;
3740
3741 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3742 p_api_version_number ,
3743 l_api_name ,
3744 G_pkg_name )
3745 THEN
3746 RAISE e_ver_mismatch ;
3747 END IF ;
3748
3749 G_personnel_rec := P_personnel_rec ;
3750
3751 G_stage := 'proc_check_required' ;
3752
3753 check_personnel_required (p_validate , X_return_status ) ;
3754
3755 IF p_validate THEN
3756
3757 OPEN l_full_name_csr(G_personnel_rec.person_id );
3758 FETCH l_full_name_csr into l_dummy ;
3759
3760 IF l_full_name_csr%NOTFOUND THEN
3761 add_message_to_stack( P_label => 'GMS_AWD_PERSON_ID_INVALID' ) ;
3762 set_return_status ( X_return_status, 'B') ;
3763 END IF ;
3764 CLOSE l_full_name_csr ;
3765
3766 OPEN l_award_role_csr(G_personnel_rec.award_role );
3767 FETCH l_award_role_csr into l_dummy ;
3768
3769 IF l_award_role_csr%NOTFOUND THEN
3770 add_message_to_stack( P_label => 'GMS_AWD_ROLE_INVALID' ) ;
3771 set_return_status ( X_return_status, 'B') ;
3772 END IF ;
3773 CLOSE l_award_role_csr ;
3774 END IF ; -- end if for p_validate .
3775
3776 IF G_personnel_rec.award_id IS NOT NULL THEN
3777 OPEN l_rec_csr(G_personnel_rec.award_id ) ;
3778 FETCH l_rec_csr INTO l_start_date_active,l_budget_wf_enabled_flag ,
3779 l_award_template_flag , l_award_project_id , l_end_date_active ;
3780
3781 IF l_rec_csr%NOTFOUND THEN
3782 add_message_to_stack( P_label => 'GMS_AWD_NOT_EXISTS' ) ;
3783 set_return_status ( X_return_status, 'B') ;
3784 END IF ;
3785 CLOSE l_rec_csr ;
3786 END IF ;
3787
3788 -- =========================================================
3789 -- Make sure that X_return_status is success before continue.
3790 -- =========================================================
3791 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
3792 Raise fnd_api.g_exc_error ;
3793 END IF ;
3794
3795 -- ==================================================================
3796 -- If the Start_Date_Active is NULL for PERSONNEL record then take the
3797 -- Award_Start_Date_Active as the PERSONNEL Start_Date_Active .
3798 -- ==================================================================
3799
3800 If G_personnel_rec.start_date_active IS NULL THEN
3801 G_personnel_rec.start_date_active := l_start_date_active ;
3802 END IF ;
3803
3804 G_stage := 'FND_API.Check_Start_Date_Active' ;
3805 Verify_dates(p_validate , X_return_status ) ;
3806
3807 G_stage := 'FND_API.Verify_Award_Manager_Dates' ;
3808 Verify_Award_Manager_Dates (p_validate , X_return_status) ;
3809
3810 G_stage := 'FND_API.Verify_User_Status' ;
3811 Verify_User_Status(p_validate , X_return_status) ;
3812
3813
3814 IF G_personnel_rec.award_role = 'AM'AND l_award_template_flag = 'DEFERRED' THEN
3815
3816 G_stage := 'FND_API.Create_Pa_Key_Member' ;
3817 Create_Pa_Key_Member( p_validate , X_return_status ) ;
3818 END IF ;
3819
3820 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
3821 RAISE FND_API.G_EXC_ERROR;
3822 END IF ;
3823
3824
3825 SELECT gms_personnel_s.nextval
3826 INTO G_personnel_rec.personnel_id
3827 FROM DUAL ;
3828
3829 G_stage := 'FND_API.Create_Personnel_Record' ;
3830 gms_personnel_pkg.insert_row
3831 ( x_rowid => L_rowid,
3832 x_personnel_id => G_personnel_rec.personnel_id,
3833 x_award_id => G_personnel_rec.award_id,
3834 x_person_id => G_personnel_rec.person_id,
3835 x_award_role => G_personnel_rec.award_role,
3836 x_start_date_active => G_personnel_rec.start_date_active,
3837 x_end_date_active => G_personnel_rec.end_date_active,
3838 x_required_flag => G_personnel_rec.required_flag,
3839 x_mode => 'R'
3840 );
3841
3842 G_stage := 'FND_API.Create_Person_Events' ;
3843 gms_notification_pkg.crt_default_person_events
3844 ( p_award_id => G_personnel_rec.award_id,
3845 p_person_id => G_personnel_rec.person_id,
3846 x_err_code => l_error_code,
3847 x_err_stage => l_error_stage
3848 );
3849 G_personnel_rec := NULL ; -- Resetting the record vaarible.
3850
3851
3852 G_stage := 'Personnel Created Successfully' ;
3853 EXCEPTION
3854 WHEN E_VER_MISMATCH THEN
3855 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
3856 p_token1 => 'SUPVER',
3857 P_VAL1 => g_api_version_number) ;
3858 set_return_status(X_return_status, 'B' ) ;
3859 x_msg_count := G_msg_count ;
3860 x_msg_data := G_msg_data ;
3861
3862 WHEN FND_API.G_EXC_ERROR THEN
3863 G_personnel_rec := NULL ; -- Resetting the record vaarible.
3864 ROLLBACK TO create_personnel_pvt ;
3865 set_return_status(X_return_status, 'B' ) ;
3866 x_msg_count := G_msg_count ;
3867 x_msg_data := G_msg_data ;
3868 WHEN OTHERS THEN
3869 G_personnel_rec := NULL ; -- Resetting the record vaarible.
3870 ROLLBACK TO create_personnel_pvt;
3871 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3872
3873 FND_MSG_PUB.add_exc_msg
3874 ( p_pkg_name => G_PKG_NAME
3875 , p_procedure_name => l_api_name );
3876 FND_MSG_PUB.Count_And_Get
3877 ( p_count => x_msg_count ,
3878 p_data => x_msg_data );
3879
3880
3881
3882 END CREATE_PERSONNEL ;
3883
3884
3885 -- ========================================================================
3886 -- Create Terms and conditions.
3887 -- ========================================================================
3888 PROCEDURE CREATE_TERM_CONDITION
3889 (X_MSG_COUNT IN OUT NOCOPY NUMBER ,
3890 X_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
3891 X_return_status IN OUT NOCOPY VARCHAR2 ,
3892 X_ROW_ID OUT NOCOPY VARCHAR2 ,
3893 P_CALLING_MODULE IN VARCHAR2 ,
3894 P_API_VERSION_NUMBER IN NUMBER ,
3895 P_validate IN BOOLEAN DEFAULT TRUE ,
3896 P_AWARD_TERM_CONDITION_REC IN GMS_AWARDS_TERMS_CONDITIONS%ROWTYPE
3897 ) IS
3898
3899 l_award_start_date_active DATE ;
3900 l_award_end_date_active DATE ;
3901 l_api_name VARCHAR2(30) := 'CREATE_TERM_CONDITION';
3902 l_dummy VARCHAR2(1) ;
3903 l_rowid varchar2(45) ;
3904
3905 -- =======================================================================
3906 -- This is to check the uniqueness of the award_id + term_id + category_id
3907 -- before creating the term and condition .
3908 -- =======================================================================
3909
3910 CURSOR l_check_duplicate_csr IS
3911 SELECT 'X'
3912 FROM gms_awards_terms_conditions
3913 WHERE award_id = G_term_condition_rec.award_id
3914 AND category_id = G_term_condition_rec.category_id
3915 AND term_id = G_term_condition_rec.term_id ;
3916
3917 -- ===========================================================================================
3918 -- This cursor is used to verify whether the incoming category exists or not( LOV validation ).
3919 -- ===========================================================================================
3920
3921 CURSOR l_category_id_csr IS
3922 SELECT 'X'
3923 FROM gms_tc_categories
3924 WHERE category_id = G_term_condition_rec.category_id ;
3925
3926 -- ===========================================================================================
3927 -- This cursor is used to verify whether the incoming term_id exists or not for the specific
3928 -- category_id ( LOV validation ).
3929 -- ===========================================================================================
3930 CURSOR l_term_id_csr IS
3931 SELECT 'X'
3932 FROM gms_terms_conditions tc1
3933 WHERE tc1.category_id = G_term_condition_rec.category_id
3934 and term_id not in (select term_id from gms_terms_conditions tc
3935 where
3936 (
3937 (tc.start_date_active > l_award_start_date_active
3938 and tc.start_date_active > l_award_end_date_active
3939 and tc.start_date_active is not null and tc.end_date_active is not null)
3940 or
3941 (tc.end_date_active < l_award_start_date_active
3942 and tc.end_date_active < l_award_end_date_active
3943 and tc.start_date_active is not null
3944 and tc.end_date_active is not null)
3945 or
3946 ( tc.start_date_active is null
3947 and tc.end_date_active < l_award_start_date_active
3948 and tc.end_date_active is not null)
3949 or
3950 (tc.end_date_active is null
3951 and tc.start_date_active > l_award_end_date_active
3952 and tc.start_date_active is not null)
3953 )
3954 ) ;
3955 -- ========================================================================
3956 -- This cursor is used to retrived the start_date and end_date for an Award.
3957 -- ========================================================================
3958
3959 CURSOR l_award_rec_csr IS
3960 SELECT start_date_active,end_date_active
3961 FROM gms_awards_all
3962 WHERE award_id = G_term_condition_rec.award_id ;
3963
3964 -- =======================================================================================
3965 -- This procedure will verify whehter all the NOT NULL columns have values or not.
3966 -- This needs to be checked here as we don't do any validation in the PUB.
3967 -- If the call is coming from PVT i.e p_validate is FALSE then we don't do this validation.
3968 -- =======================================================================================
3969
3970 PROCEDURE check_term_cond_required (p_validate IN BOOLEAN ,
3971 x_return_status IN OUT NOCOPY VARCHAR2 ) IS
3972 l_error BOOLEAN ;
3973
3974 BEGIN
3975 IF not p_validate then
3976 return ;
3977 end if ;
3978
3979 l_error := FALSE ;
3980
3981 IF G_term_condition_rec.Award_Id IS NULL THEN
3982 l_error := TRUE ;
3983 add_message_to_stack( P_label => 'GMS_AWD_ID_MISSING' ) ;
3984
3985 END IF ;
3986
3987 IF G_term_condition_rec.category_id IS NULL THEN
3988 add_message_to_stack( P_label => 'GMS_TERM_CON_CATEGORY_NULL' ) ;
3989 l_error := TRUE ;
3990 END IF ;
3991 IF G_term_condition_rec.term_id IS NULL THEN
3992 add_message_to_stack( P_label => 'GMS_TERM_ID_NULL' ) ;
3993 l_error := TRUE ;
3994 END IF ;
3995
3996 IF L_error THEN
3997 set_return_status ( X_return_status, 'B') ;
3998 END IF ;
3999 END check_term_cond_required ;
4000 -- --------------------------------------------------------------------------
4001
4002 BEGIN
4003
4004 -- =============================
4005 -- Initialize the message stack.
4006 -- =============================
4007
4008 init_message_stack;
4009
4010 G_msg_count := X_msg_count ;
4011 G_msg_data := X_MSG_DATA ;
4012 G_calling_module := P_CALLING_MODULE ;
4013
4014 -- ============================
4015 -- Initialize the return status.
4016 -- ============================
4017 IF NVL(x_return_status , FND_API.G_RET_STS_SUCCESS) NOT IN
4018 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4019 x_return_status := FND_API.G_RET_STS_SUCCESS ;
4020 END IF ;
4021
4022 SAVEPOINT create_award_term_cond_pvt ;
4023
4024 G_stage := 'FND_API.Compatible_API_Call' ;
4025
4026 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4027 p_api_version_number ,
4028 l_api_name ,
4029 G_pkg_name ) THEN
4030 RAISE e_ver_mismatch ;
4031 END IF ;
4032
4033 G_term_condition_rec := p_award_term_condition_rec ;
4034 G_stage := 'proc_check_required_for_term_condition' ;
4035
4036 check_term_cond_required (p_validate , x_return_status ) ;
4037
4038 IF G_term_condition_rec.award_id IS NOT NULL THEN
4039 OPEN l_award_rec_csr ;
4040 FETCH l_award_rec_csr INTO l_award_start_date_active,l_award_end_date_active ;
4041 IF l_award_rec_csr%NOTFOUND THEN
4042 add_message_to_stack( P_label => 'GMS_AWD_NOT_EXISTS' ) ;
4043 set_return_status ( x_return_status, 'B') ;
4044 END IF ;
4045 CLOSE l_award_rec_csr ;
4046 END IF ;
4047
4048 IF p_validate THEN -- i.e the call is from PUB package .
4049 -- The following are all LOV validations.
4050 OPEN l_category_id_csr ;
4051 FETCH l_category_id_csr INTO l_dummy ;
4052 IF l_category_id_csr%NOTFOUND THEN
4053 add_message_to_stack( P_label => 'GMS_AWD_CATEGORY_NAME_INVALID' ) ;
4054 set_return_status ( x_return_status, 'B') ;
4055 END IF ;
4056 CLOSE l_category_id_csr ;
4057
4058 OPEN l_term_id_csr ;
4059 FETCH l_term_id_csr INTO l_dummy ;
4060 IF l_term_id_csr%NOTFOUND THEN
4061 add_message_to_stack( P_label => 'GMS_TERM_NAME_INVALID' ) ;
4062 set_return_status ( x_return_status, 'B') ;
4063 END IF ;
4064 CLOSE l_term_id_csr ;
4065 -- =============================================================================
4066 -- This is to check the uniqueness of the award_id + term_id + category_id before
4067 -- creating the term and condition .
4068 -- =============================================================================
4069
4070 OPEN l_check_duplicate_csr ;
4071 FETCH l_check_duplicate_csr INTO l_dummy ;
4072 IF l_check_duplicate_csr%FOUND THEN
4073 add_message_to_stack( P_label => 'GMS_AWARD_TERM_CATEGORY_DUP' ) ;
4074 set_return_status ( x_return_status, 'B') ;
4075 END IF ;
4076 CLOSE l_check_duplicate_csr ;
4077
4078 END IF ; -- end if for p_validate.
4079
4080 IF NVL(x_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
4081 RAISE fnd_api.g_exc_error ;
4082 END IF ; -- end if for nvl(x_return_status) .
4083
4084 G_stage := 'FND_API.Creating_Award_term_condition_record ' ;
4085 gms_awards_tc_pkg.insert_row
4086 ( X_ROWID => l_rowid,
4087 X_AWARD_ID => G_term_condition_rec.award_id,
4088 X_CATEGORY_ID => G_term_condition_rec.category_id,
4089 X_TERM_ID => G_term_condition_rec.term_id,
4090 X_OPERAND => G_term_condition_rec.operand,
4091 X_VALUE => G_term_condition_rec.value,
4092 X_MODE => 'R'
4093 ) ;
4094 G_term_condition_rec := NULL ; -- Resetting the record vaarible.
4095
4096 G_stage := 'FND_API.Succefully_created_award_term_condition' ;
4097
4098
4099 EXCEPTION
4100 WHEN E_VER_MISMATCH THEN
4101 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
4102 p_token1 => 'SUPVER',
4103 P_VAL1 => g_api_version_number) ;
4104 set_return_status(x_return_status, 'B' ) ;
4105 X_msg_count := G_msg_count ;
4106 X_msg_data := G_msg_data ;
4107
4108 WHEN FND_API.G_EXC_ERROR THEN
4109 G_term_condition_rec := NULL ; -- Resetting the record vaarible.
4110 ROLLBACK TO create_award_term_cond_pvt ;
4111 set_return_status(x_return_status, 'B' ) ;
4112 X_msg_count := G_msg_count ;
4113 X_msg_data := G_msg_data ;
4114 WHEN OTHERS THEN
4115 G_term_condition_rec := NULL ; -- Resetting the record vaarible.
4116 ROLLBACK TO create_award_term_cond_pvt ;
4117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4118
4119 FND_MSG_PUB.add_exc_msg
4120 ( p_pkg_name => G_PKG_NAME
4121 , p_procedure_name => l_api_name );
4122 FND_MSG_PUB.Count_And_Get
4123 ( p_count => X_msg_count ,
4124 p_data => X_msg_data );
4125
4126
4127 end CREATE_TERM_CONDITION ;
4128
4129 -- ========================================================================
4130 -- This procedure will create references as needed for each Award withe
4131 -- effective Start Date and End Dates .
4132 -- ========================================================================
4133 PROCEDURE CREATE_REFERENCE_NUMBER
4134 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
4135 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
4136 X_return_status IN OUT NOCOPY VARCHAR2 ,
4137 X_ROW_ID OUT NOCOPY VARCHAR2 ,
4138 P_CALLING_MODULE IN VARCHAR2 ,
4139 P_API_VERSION_NUMBER IN NUMBER ,
4140 P_validate IN BOOLEAN DEFAULT TRUE ,
4141 P_REFERENCE_NUMBER_REC IN GMS_REFERENCE_NUMBERS%ROWTYPE
4142 ) IS
4143
4144 -- This Cursor is used to validate the incoming TYPE parameter
4145 -- with the lookup_code. It it doesn't exist then we raise an error
4146 -- and exit the procedure .
4147
4148 CURSOR l_lookup_csr IS
4149 SELECT 'X'
4150 FROM gms_lookups
4151 WHERE lookup_type = 'REFERENCE_NUMBER'
4152 AND lookup_code = G_reference_number_rec.type ;
4153
4154 -- ==============================================================================
4155 -- This is to check whether the award_id and reference_type combination is unique .
4156 -- Here reference_type corresponds to the Lookup_code in the gms_looups table.
4157 -- ==============================================================================
4158
4159 CURSOR l_duplicate_ref_type IS
4160 SELECT 'X'
4161 FROM gms_reference_numbers
4162 WHERE award_id = G_reference_number_rec.award_id
4163 AND type = G_reference_number_rec.type ;
4164
4165 l_api_name VARCHAR2(30) := 'CREATE_PERSONNEL';
4166 l_dummy VARCHAR2(1) ;
4167 l_rowid varchar2(45) ;
4168 -- ========================================================================
4169 -- This procedure will check all the required values .
4170 -- ========================================================================
4171
4172 PROCEDURE check_reference_required (p_validate IN BOOLEAN ,
4173 X_return_status IN OUT NOCOPY VARCHAR2 ) IS
4174 l_error BOOLEAN ;
4175
4176 BEGIN
4177 IF not p_validate then
4178 return ;
4179 end if ;
4180
4181 l_error := FALSE ;
4182
4183 IF G_reference_number_rec.Award_Id IS NULL THEN
4184 l_error := TRUE ;
4185 add_message_to_stack( P_label => 'GMS_AWD_ID_MISSING' ) ;
4186
4187 END IF ;
4188
4189 IF g_reference_number_rec.type IS NULL THEN
4190 add_message_to_stack( P_label => 'GMS_REF_TYPE_NULL' ) ;
4191 l_error := TRUE ;
4192 END IF ;
4193
4194 IF L_error THEN
4195 set_return_status ( X_return_status, 'B') ;
4196 END IF ;
4197 END check_reference_required ;
4198 -- -------------------------------------
4199
4200 BEGIN
4201
4202 -- Initialize the message stack.
4203 -- -----------------------------
4204 init_message_stack;
4205
4206 G_msg_count := x_msg_count ;
4207 G_msg_data := x_MSG_DATA ;
4208 G_calling_module := P_CALLING_MODULE ;
4209
4210 -- =============================
4211 -- Initialize the return status.
4212 -- =============================
4213 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) NOT IN
4214 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4215 X_return_status := FND_API.G_RET_STS_SUCCESS ;
4216 END IF ;
4217
4218 SAVEPOINT create_reference_number_pvt ;
4219
4220 G_stage := 'FND_API.Compatible_API_Call' ;
4221
4222 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4223 p_api_version_number ,
4224 l_api_name ,
4225 G_pkg_name ) THEN
4226 RAISE e_ver_mismatch ;
4227 END IF ;
4228
4229 G_reference_number_rec := p_reference_number_rec ;
4230
4231 G_stage := 'proc_check_required' ;
4232 check_reference_required (p_validate , X_return_status ) ;
4233
4234 IF p_validate THEN -- i.e the call is from PUB package .
4235 -- Following are all LOV validations.
4236
4237 OPEN l_lookup_csr ;
4238 FETCH l_lookup_csr INTO l_dummy ;
4239 IF l_lookup_csr%NOTFOUND THEN
4240 add_message_to_stack( P_label => 'GMS_AWD_INVALID_REFERENCE_TYPE' ) ;
4241 set_return_status ( X_return_status, 'B') ;
4242 END IF ;
4243 CLOSE l_lookup_csr ;
4244
4245 -- ==============================================================================
4246 -- This is to check whether the award_id and reference_type combination is unique .
4247 -- ==============================================================================
4248
4249 OPEN l_duplicate_ref_type ;
4250 FETCH l_duplicate_ref_type INTO l_dummy ;
4251 IF l_duplicate_ref_type%FOUND THEN
4252 add_message_to_stack( P_label => 'GMS_DUP_REFERENCE_TYPE' ) ;
4253 set_return_status ( X_return_status, 'B') ;
4254 END IF ;
4255 CLOSE l_duplicate_ref_type ;
4256 END IF ; -- end if for p_validate.
4257
4258 -- ==============================================================================
4259 -- Here we check whether the return_staus <> FND_API.G_RET_STS_SUCCESS because of
4260 -- any above validations . If it is that means some error has happened and we don't
4261 -- insert the record but the control go to exception section.
4262 -- ==============================================================================
4263
4264 IF NVL(X_return_status,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
4265 RAISE fnd_api.g_exc_error ;
4266 END IF ; -- end if for nvl(X_return_status) .
4267
4268 -- Creating the Reference_number Record .
4269
4270 G_stage := 'FND_API.Creating_reference_number' ;
4271 gms_reference_numbers_pkg.insert_row
4272 ( x_rowid => l_rowid,
4273 x_award_id => G_reference_number_rec.award_id,
4274 x_type => G_reference_number_rec.type,
4275 x_value => G_reference_number_rec.value,
4276 x_required_flag => G_reference_number_rec.required_flag,
4277 x_mode => 'R'
4278 );
4279 G_reference_number_rec := NULL ; -- Resetting the record vaarible.
4280
4281
4282 G_stage := 'FND_API.Succefully_created_reference_number ' ;
4283
4284 EXCEPTION
4285 WHEN E_VER_MISMATCH THEN
4286 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
4287 p_token1 => 'SUPVER',
4288 P_VAL1 => g_api_version_number) ;
4289 set_return_status(X_return_status, 'B' ) ;
4290 x_msg_count := G_msg_count ;
4291 x_msg_data := G_msg_data ;
4292
4293 WHEN FND_API.G_EXC_ERROR THEN
4294 G_reference_number_rec := NULL ; -- Resetting the record vaarible.
4295 ROLLBACK TO create_reference_number_pvt ;
4296 set_return_status(X_return_status, 'B' ) ;
4297 x_msg_count := G_msg_count ;
4298 x_msg_data := G_msg_data ;
4299 WHEN OTHERS THEN
4300 G_reference_number_rec := NULL ; -- Resetting the record vaarible.
4301 ROLLBACK TO create_reference_number_pvt ;
4302 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4303
4304 FND_MSG_PUB.add_exc_msg
4305 ( p_pkg_name => G_PKG_NAME
4306 , p_procedure_name => l_api_name );
4307 FND_MSG_PUB.Count_And_Get
4308 ( p_count => x_msg_count ,
4309 p_data => x_msg_data );
4310
4311 END CREATE_REFERENCE_NUMBER ;
4312
4313 -- ========================================================================
4314 -- Create Contact
4315 -- ========================================================================
4316
4317 PROCEDURE CREATE_CONTACT
4318 (X_MSG_COUNT IN OUT NOCOPY NUMBER ,
4319 X_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
4320 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2 ,
4321 X_ROW_ID OUT NOCOPY VARCHAR2 ,
4322 P_CALLING_MODULE IN VARCHAR2 ,
4323 P_API_VERSION_NUMBER IN NUMBER ,
4324 P_VALIDATE IN BOOLEAN default TRUE,
4325 P_CONTACT_REC IN GMS_AWARDS_CONTACTS%ROWTYPE
4326 )
4327 IS
4328 --TCA enhancement : Changing RA tables with HZ tables
4329 CURSOR l_valid_award_csr IS
4330 SELECT award_id,award_project_id
4331 FROM gms_awards_all
4332 WHERE award_id = G_contact_rec.award_id;
4333
4334 CURSOR l_valid_contact_csr IS
4335 SELECT 'X'
4336 FROM gms_awards_all ga,
4337 Hz_cust_account_roles acct_roles
4338 WHERE ga.award_id = G_contact_rec.award_id
4339 AND decode(ga.billing_format,'LOC',ga.bill_to_customer_id,ga.funding_source_id)=acct_roles.cust_account_id
4340 AND acct_roles.cust_account_role_id = G_contact_rec.contact_id;
4341
4342
4343 CURSOR l_valid_usage_csr IS
4344 SELECT 'X'
4345 FROM hz_cust_site_uses a,
4346 Hz_cust_acct_sites b,
4347 ar_lookups c,
4348 gms_awards_all ga
4349 WHERE a.cust_acct_site_id = b.cust_acct_site_id
4350 AND b.cust_account_id = decode(ga.billing_format,'LOC',ga.bill_to_customer_id,ga.funding_source_id)
4351 AND c.lookup_type = 'SITE_USE_CODE'
4352 AND c.lookup_code = g_contact_rec.usage_code;
4353
4354 CURSOR l_dup_usage_csr IS
4355 SELECT 'X'
4356 FROM gms_awards_contacts
4357 WHERE award_id = G_contact_rec.award_id
4358 AND contact_id = G_contact_rec.contact_id -- Bug 2672027
4359 AND customer_id = G_contact_rec.customer_id
4360 AND usage_code = G_contact_rec.usage_code;
4361
4362 l_api_name varchar2(30) := 'CREATE_CONTACT' ;
4363 l_award_project_id NUMBER ;
4364 l_award_id NUMBER ;
4365 l_rowid VARCHAR2(45) ;
4366 l_contact VARCHAR2(1);
4367 l_usage VARCHAR2(1);
4368 l_dup_usage VARCHAR2(1);
4369
4370 BEGIN
4371
4372 -- =============================
4373 -- Initialize the Message Stack.
4374 -- =============================
4375
4376 init_message_stack;
4377
4378 G_msg_count := x_msg_count ;
4379 G_msg_data := x_MSG_DATA ;
4380 G_calling_module := P_CALLING_MODULE ;
4381
4382 -- =============================
4383 -- Initialize the return status.
4384 -- =============================
4385
4386 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
4387 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4388 X_return_status := FND_API.G_RET_STS_SUCCESS ;
4389 END IF ;
4390
4391 -- =========================
4392 -- Establish the Save Point.
4393 -- =========================
4394
4395 SAVEPOINT create_contact_pvt ;
4396
4397 -- ==============================================================
4398 -- Compare the caller version number to the API version number in
4399 -- order to detect incompatible API calls.
4400 -- ==============================================================
4401
4402 G_stage := 'FND_API.Compatible_API_Call' ;
4403
4404 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4405 p_api_version_number ,
4406 l_api_name ,
4407 G_pkg_name )
4408 THEN
4409 RAISE e_ver_mismatch ;
4410 END IF ;
4411
4412 -- =============================================
4413 -- Check for required columns for Create Contact
4414 -- =============================================
4415
4416 G_stage := 'procedure_check_contact_required_columns' ;
4417
4418 G_contact_rec := P_contact_rec;
4419
4420 IF G_contact_rec.award_id IS NULL THEN
4421 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
4422 set_return_status ( X_return_status, 'B') ;
4423 END IF ;
4424
4425 IF G_contact_rec.contact_id IS NULL THEN
4426 add_message_to_stack( P_label => 'GMS_CON_CONTACT_ID_NULL' ) ;
4427 set_return_status ( X_return_status, 'B') ;
4428 END IF ;
4429
4430 IF G_contact_rec.customer_id IS NULL THEN
4431 add_message_to_stack( P_label => 'GMS_CON_CUSTOMER_ID_NULL' ) ;
4432 set_return_status ( X_return_status, 'B') ;
4433 END IF ;
4434
4435 IF G_contact_rec.usage_code IS NULL THEN
4436 add_message_to_stack( P_label => 'GMS_CON_USAGE_CODE_NULL' ) ;
4437 set_return_status ( X_return_status, 'B') ;
4438 END IF ;
4439
4440 IF G_contact_rec.primary_flag IS NULL THEN
4441 add_message_to_stack( P_label => 'GMS_CON_PRIMARY_FLAG_NULL' ) ;
4442 set_return_status ( X_return_status, 'B') ;
4443 END IF ;
4444
4445 IF G_contact_rec.primary_flag NOT IN ('Y','N') THEN
4446 add_message_to_stack( P_label => 'GMS_CON_INVALID_PRIMARY_FLAG' ) ;
4447 set_return_status ( X_return_status, 'B') ;
4448 END IF ;
4449
4450 -- ===================================================================
4451 -- Need to make sure that the return status is success from the above
4452 -- validations. There is no point in doing further validations as the
4453 -- required columns donot have values. So we raise an Error.
4454 -- ===================================================================
4455
4456 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4457 RAISE FND_API.G_EXC_ERROR;
4458 END IF ;
4459
4460 -- ======================================================================
4461 -- Verify the contacts for validity by cross checking with funding source
4462 -- ======================================================================
4463
4464 G_stage := 'create_contact.Verify_Award';
4465
4466 OPEN l_valid_award_csr ;
4467 FETCH l_valid_award_csr INTO l_award_id,l_award_project_id;
4468
4469 IF l_award_id IS NULL THEN
4470 add_message_to_stack( P_label => 'GMS_FND_INVALID_AWARD' ) ;
4471 set_return_status ( X_return_status, 'B') ;
4472 END IF;
4473
4474 CLOSE l_valid_award_csr ;
4475
4476 G_stage := 'create_contact.Verify_Contact;' ;
4477
4478 OPEN l_valid_contact_csr;
4479 FETCH l_valid_contact_csr INTO l_contact;
4480
4481 IF l_valid_contact_csr%NOTFOUND THEN
4482 add_message_to_stack( P_label => 'GMS_CON_CONTACT_INVLAID' ) ;
4483 set_return_status ( X_return_status, 'B') ;
4484 END IF;
4485
4486 CLOSE l_valid_contact_csr;
4487
4488 -- ============================================================
4489 -- Verify the usage for validity by cross checking with lookups
4490 -- ===========================================================
4491
4492 G_stage := 'create_contact.Verify_Usage;' ;
4493
4494 OPEN l_valid_usage_csr;
4495 FETCH l_valid_usage_csr INTO l_usage;
4496
4497 IF l_valid_usage_csr%NOTFOUND THEN
4498 add_message_to_stack( P_label => 'GMS_CON_USAGE_INVALID' ) ;
4499 set_return_status ( X_return_status, 'B') ;
4500 END IF;
4501
4502 CLOSE l_valid_usage_csr;
4503
4504 -- ============================================================================
4505 -- Verify the duplicate usage of contacts for a given award, customer and usage.
4506 -- ============================================================================
4507
4508 G_stage := 'create_contact.Verify_Duplicate_Usage;' ;
4509
4510 OPEN l_dup_usage_csr ;
4511 FETCH l_dup_usage_csr INTO l_dup_usage;
4512
4513 IF l_dup_usage_csr%FOUND THEN
4514 add_message_to_stack( P_label => 'GMS_CON_AWD_USAGE_DUP' ) ;
4515 set_return_status ( X_return_status, 'B') ;
4516 END IF;
4517
4518 CLOSE l_dup_usage_csr ;
4519
4520
4521 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4522 RAISE FND_API.G_EXC_ERROR;
4523 END IF ;
4524
4525
4526 -- ===========================================================================================
4527 -- Updating pa_project_contacts for Award Project customer id for primary bill to and ship to
4528 -- ===========================================================================================
4529
4530 IF (G_contact_rec.primary_flag = 'Y') THEN
4531 IF (G_contact_rec.usage_code = 'BILL_TO') or (G_contact_rec.usage_code = 'SHIP_TO' ) THEN
4532 IF l_award_project_id IS NOT NULL THEN
4533
4534 UPDATE pa_project_contacts
4535 SET contact_id = DECODE(project_contact_type_code,
4536 'BILLING', (G_contact_rec.Contact_id),
4537 'SHIPPING',(G_contact_rec.Contact_id),
4538 contact_id ),
4539 last_update_date = SYSDATE,
4540 last_updated_by = fnd_global.user_id,
4541 last_update_login = fnd_global.login_id
4542 WHERE project_id = l_award_project_id
4543 AND customer_id = G_contact_rec.customer_id;
4544 END IF;
4545 END IF;
4546 END IF;
4547
4548 -- ========================================
4549 -- Calling Table Handler to Insert the Row.
4550 -- ========================================
4551
4552 G_stage := 'gms_awards_contacts_pkg.insert_row' ;
4553
4554 gms_awards_contacts_pkg.insert_row
4555 ( x_rowid => L_rowid,
4556 x_award_id => G_contact_rec.award_id,
4557 x_customer_id => G_contact_rec.customer_id,
4558 x_contact_id => G_contact_rec.contact_id,
4559 x_mode => 'R',
4560 x_primary_flag => G_contact_rec.primary_flag,
4561 x_usage_code => G_contact_rec.usage_code
4562 );
4563
4564 G_stage := 'contact Created Successfully' ;
4565
4566 EXCEPTION
4567
4568 WHEN E_VER_MISMATCH THEN
4569 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
4570 p_token1 => 'SUPVER',
4571 P_VAL1 => g_api_version_number) ;
4572 set_return_status(X_return_status, 'B' ) ;
4573 x_msg_count := G_msg_count ;
4574 x_msg_data := G_msg_data ;
4575
4576 WHEN FND_API.G_EXC_ERROR THEN
4577 ROLLBACK TO create_contact_pvt ;
4578 set_return_status(X_return_status, 'B' ) ;
4579 x_msg_count := G_msg_count ;
4580 x_msg_data := G_msg_data ;
4581 WHEN OTHERS THEN
4582 ROLLBACK TO create_contact_pvt;
4583 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4584
4585 FND_MSG_PUB.add_exc_msg
4586 ( p_pkg_name => G_PKG_NAME
4587 , p_procedure_name => l_api_name );
4588 FND_MSG_PUB.Count_And_Get
4589 ( p_count => x_msg_count ,
4590 p_data => x_msg_data );
4591
4592 END CREATE_CONTACT ;
4593
4594 -- ========================================================================
4595 -- Create Report
4596 -- ========================================================================
4597
4598 PROCEDURE CREATE_REPORT
4599 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
4600 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
4601 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2 ,
4602 X_DEFAULT_REPORT_ID IN OUT NOCOPY NUMBER ,
4603 X_ROW_ID OUT NOCOPY VARCHAR2 ,
4604 P_CALLING_MODULE IN VARCHAR2 ,
4605 P_API_VERSION_NUMBER IN NUMBER ,
4606 P_VALIDATE IN BOOLEAN default TRUE,
4607 P_REPORT_REC IN GMS_DEFAULT_REPORTS%ROWTYPE
4608 )
4609 IS
4610
4611 CURSOR l_dup_reports_csr IS
4612 SELECT 'X'
4613 FROM gms_default_reports
4614 WHERE award_id = G_report_rec.award_id
4615 AND report_template_id = G_report_rec.report_template_id;
4616
4617 CURSOR l_valid_frequency_csr IS
4618 SELECT 'X'
4619 FROM gms_lookups
4620 WHERE lookup_type = 'REPORT_FREQUENCY'
4621 AND lookup_code = G_report_rec.frequency;
4622
4623 CURSOR l_valid_site_code_csr IS
4624 SELECT 'X'
4625 FROM hz_cust_site_uses a,
4626 Hz_cust_acct_sites b,
4627 ar_lookups c,
4628 gms_awards_all d
4629 WHERE a.cust_acct_site_id = b. cust_acct_site_id
4630 AND b. cust_account_id = d.funding_source_id
4631 AND d.award_id = G_report_rec.award_id
4632 AND c.lookup_type = 'SITE_USE_CODE'
4633 AND c.lookup_code = a.site_use_code
4634 AND a.site_use_id = G_report_rec.site_use_id;
4635
4636 l_api_name VARCHAR2(30) := 'CREATE_REPORT' ;
4637 l_error_code VARCHAR2(2000) ;
4638 l_error_stage VARCHAR2(2000) ;
4639 l_rowid VARCHAR2(45) ;
4640 l_dup_report VARCHAR2(1);
4641 l_valid_frequency VARCHAR2(1);
4642 l_valid_site_code VARCHAR2(1);
4643
4644
4645 BEGIN
4646
4647 -- =============================
4648 -- Initialize the Message Stack.
4649 -- =============================
4650
4651 init_message_stack;
4652
4653 G_msg_count := x_msg_count ;
4654 G_msg_data := x_MSG_DATA ;
4655 G_calling_module := P_CALLING_MODULE ;
4656
4657 -- ============================
4658 -- Initialize the return status.
4659 -- ============================
4660
4661 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
4662 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4663 X_return_status := FND_API.G_RET_STS_SUCCESS ;
4664 END IF ;
4665
4666 -- =========================
4667 -- Establish the Save Point.
4668 -- =========================
4669
4670 SAVEPOINT create_report_pvt ;
4671
4672 -- ==============================================================
4673 -- Compare the caller version number to the API version number in
4674 -- order to detect incompatible API calls.
4675 -- ==============================================================
4676
4677 G_stage := 'FND_API.Compatible_API_Call' ;
4678
4679 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4680 p_api_version_number ,
4681 l_api_name ,
4682 G_pkg_name )
4683 THEN
4684 RAISE e_ver_mismatch ;
4685 END IF ;
4686
4687 -- ============================================
4688 -- Check for required columns for Create Report
4689 -- ============================================
4690
4691 G_stage := 'procedure_check_report_required_columns' ;
4692
4693 G_report_rec := P_report_rec;
4694
4695 IF G_report_rec.award_id IS NULL THEN
4696 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
4697 set_return_status ( X_return_status, 'B') ;
4698 END IF ;
4699
4700 IF G_report_rec.report_template_id IS NULL THEN
4701 add_message_to_stack( P_label => 'GMS_REPORT_ID_NULL' ) ;
4702 set_return_status ( X_return_status, 'B') ;
4703 END IF ;
4704
4705 IF G_report_rec.frequency IS NULL THEN
4706 add_message_to_stack( P_label => 'GMS_REP_FREQUENCY_NULL' ) ;
4707 set_return_status ( X_return_status, 'B') ;
4708 END IF ;
4709
4710 IF G_report_rec.due_within_days IS NULL THEN
4711 add_message_to_stack( P_label => 'GMS_REP_DUE_DAYS_NULL');
4712 set_return_status ( X_return_status, 'B') ;
4713 END IF ;
4714
4715 IF (G_report_rec.due_within_days < 0) THEN
4716 add_message_to_stack( P_label => 'GMS_REP_DUE_DAYS_NEG');
4717 set_return_status ( X_return_status, 'B') ;
4718 END IF ;
4719
4720 IF G_report_rec.copy_number IS NULL THEN
4721 add_message_to_stack( P_label => 'GMS_REP_COPY_NUM_NULL' ) ;
4722 set_return_status ( X_return_status, 'B') ;
4723 END IF ;
4724
4725 IF (G_report_rec.copy_number < 0) THEN
4726 add_message_to_stack( P_label => 'GMS_REP_COPY_NUM_NEG');
4727 set_return_status ( X_return_status, 'B') ;
4728 END IF ;
4729
4730 -- ===================================================================
4731 -- Need to make sure that the return status is success from the above
4732 -- validations. There is no point in doing further validations as the
4733 -- required columns donot have values. So we raise an Error.
4734 -- ===================================================================
4735
4736 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4737 RAISE FND_API.G_EXC_ERROR;
4738 END IF ;
4739
4740 -- =================================
4741 -- Verify the frequency for validity
4742 -- =================================
4743
4744 G_stage := 'create_report.Verify_frequency' ;
4745
4746 OPEN l_valid_frequency_csr ;
4747 FETCH l_valid_frequency_csr INTO l_valid_frequency;
4748
4749 IF l_valid_frequency_csr%NOTFOUND THEN
4750 add_message_to_stack( P_label => 'GMS_REP_FREQUENCY_INVALID' ) ;
4751 set_return_status ( X_return_status, 'B') ;
4752 END IF;
4753
4754 CLOSE l_valid_frequency_csr ;
4755
4756
4757 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4758 RAISE FND_API.G_EXC_ERROR;
4759 END IF ;
4760
4761 -- =====================================
4762 -- Verify the Site Use Code for validity
4763 -- =====================================
4764
4765 G_stage := 'create_report.Verify_site_code';
4766
4767 IF G_report_rec.site_use_id IS NOT NULL THEN
4768
4769 OPEN l_valid_site_code_csr ;
4770 FETCH l_valid_site_code_csr INTO l_valid_site_code;
4771
4772 IF l_valid_site_code_csr%NOTFOUND THEN
4773 add_message_to_stack( P_label => 'GMS_REP_SITE_USE_ID_INVALID' ) ;
4774 set_return_status ( X_return_status, 'B') ;
4775 END IF;
4776
4777 CLOSE l_valid_site_code_csr ;
4778
4779 END IF;
4780
4781 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4782 RAISE FND_API.G_EXC_ERROR;
4783 END IF ;
4784
4785 -- ==============================================
4786 -- Verify the duplicate reports for a given award
4787 -- ==============================================
4788
4789 G_stage := 'create_report.Verify_duplicate_reports' ;
4790
4791 OPEN l_dup_reports_csr ;
4792 FETCH l_dup_reports_csr INTO l_dup_report;
4793
4794 IF l_dup_reports_csr%FOUND THEN
4795 add_message_to_stack( P_label => 'GMS_REP_AWD_REPORT_DUP' ) ;
4796 set_return_status ( X_return_status, 'B') ;
4797 END IF;
4798
4799 CLOSE l_dup_reports_csr ;
4800
4801
4802 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4803 RAISE FND_API.G_EXC_ERROR;
4804 END IF ;
4805
4806 -- ==================================================
4807 -- Sequence number is required for default report id.
4808 -- ==================================================
4809
4810 G_stage := 'gms_default_reports_pkg.insert_row' ;
4811
4812 SELECT gms_default_reports_s.nextval
4813 INTO G_report_rec.default_report_id
4814 FROM dual ;
4815
4816 -- ========================================
4817 -- Calling Table Handler to Insert the Row.
4818 -- ========================================
4819
4820 gms_default_reports_pkg.insert_row(
4821 X_ROWID => l_rowid,
4822 X_DEFAULT_REPORT_ID => G_report_rec.default_report_id,
4823 X_REPORT_TEMPLATE_ID => G_report_rec.report_template_id,
4824 X_AWARD_ID => G_report_rec.award_id,
4825 X_FREQUENCY => G_report_rec.frequency,
4826 X_DUE_WITHIN_DAYS => G_report_rec.due_within_days,
4827 X_SITE_USE_ID => G_report_rec.site_use_id,
4828 X_COPY_NUMBER => G_report_rec.copy_number,
4829 X_ATTRIBUTE_CATEGORY => '',
4830 X_ATTRIBUTE1 => '',
4831 X_ATTRIBUTE2 => '',
4832 X_ATTRIBUTE3 => '',
4833 X_ATTRIBUTE4 => '',
4834 X_ATTRIBUTE5 => '',
4835 X_ATTRIBUTE6 => '',
4836 X_ATTRIBUTE7 => '',
4837 X_ATTRIBUTE8 => '',
4838 X_ATTRIBUTE9 => '',
4839 X_ATTRIBUTE10 => '',
4840 X_ATTRIBUTE11 => '',
4841 X_ATTRIBUTE12 => '',
4842 X_ATTRIBUTE13 => '',
4843 X_ATTRIBUTE14 => '',
4844 X_ATTRIBUTE15 => '',
4845 X_MODE => 'R'
4846 );
4847
4848 -- =====================================
4849 -- Creating Notification for the report.
4850 -- =====================================
4851
4852 G_stage := 'gms_notification_pkg.crt_default_report_events';
4853
4854 -- ========================================
4855 -- Calling Table Handler to Insert the Row.
4856 -- ========================================
4857
4858 gms_notification_pkg.crt_default_report_events(
4859 P_AWARD_ID => G_report_rec.award_id,
4860 P_REPORT_TEMPLATE_ID => G_report_rec.report_template_id,
4861 x_err_code =>l_error_code,
4862 x_err_stage =>l_error_stage
4863 );
4864
4865 IF l_error_code <> 0 THEN
4866
4867 -- ==========================================================================================
4868 -- Using l_error_stage returned by the above call,so as not to mask up the returned message.
4869 -- ==========================================================================================
4870
4871 add_message_to_stack( P_label => l_error_stage );
4872 set_return_status ( X_return_status, 'B') ;
4873
4874 END IF;
4875
4876 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
4877 RAISE FND_API.G_EXC_ERROR;
4878 END IF ;
4879
4880 X_DEFAULT_REPORT_ID := G_report_rec.default_report_id ;
4881 G_stage := 'Report Created Successfully' ;
4882
4883 EXCEPTION
4884
4885 WHEN E_VER_MISMATCH THEN
4886 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
4887 p_token1 => 'SUPVER',
4888 P_VAL1 => g_api_version_number) ;
4889 set_return_status(X_return_status, 'B' ) ;
4890 x_msg_count := G_msg_count ;
4891 x_msg_data := G_msg_data ;
4892
4893 WHEN FND_API.G_EXC_ERROR THEN
4894 ROLLBACK TO create_report_pvt ;
4895 set_return_status(X_return_status, 'B' ) ;
4896 x_msg_count := G_msg_count ;
4897 x_msg_data := G_msg_data ;
4898 WHEN OTHERS THEN
4899 ROLLBACK TO create_report_pvt;
4900 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4901
4902 FND_MSG_PUB.add_exc_msg
4903 ( p_pkg_name => G_PKG_NAME
4904 , p_procedure_name => l_api_name );
4905 FND_MSG_PUB.Count_And_Get
4906 ( p_count => x_msg_count ,
4907 p_data => x_msg_data );
4908
4909 end CREATE_REPORT ;
4910
4911 -- ========================================================================
4912 -- Create Notification
4913 -- ========================================================================
4914
4915 PROCEDURE CREATE_NOTIFICATION
4916 (x_MSG_COUNT IN OUT NOCOPY NUMBER ,
4917 x_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
4918 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2 ,
4919 X_ROW_ID OUT NOCOPY VARCHAR2 ,
4920 P_CALLING_MODULE IN VARCHAR2 ,
4921 P_API_VERSION_NUMBER IN NUMBER ,
4922 P_VALIDATE IN BOOLEAN default TRUE,
4923 P_NOTIFICATION_REC IN GMS_NOTIFICATIONS%ROWTYPE
4924 )
4925 IS
4926
4927 CURSOR l_dup_event_csr IS
4928 SELECT 'X'
4929 FROM gms_notifications
4930 WHERE award_id = G_notification_rec.award_id
4931 AND event_type = G_notification_rec.event_type
4932 AND user_id = G_notification_rec.user_id;
4933
4934 CURSOR l_default_report_csr(x_report_template_id NUMBER) IS
4935 SELECT 'X'
4936 FROM gms_default_reports
4937 WHERE award_id = G_notification_rec.award_id
4938 AND report_template_id = x_report_template_id;
4939
4940 CURSOR l_report_csr(x_report_template_id NUMBER) IS
4941 SELECT 'X'
4942 FROM gms_reports gr,
4943 gms_installments gi
4944 WHERE gi.award_id = G_notification_rec.award_id
4945 AND gi.installment_id = gr.installment_id
4946 AND gr.report_template_id = x_report_template_id;
4947
4948 l_api_name varchar2(30) := 'CREATE_NOTIFICATION' ;
4949 l_error_code VARCHAR2(2000) ;
4950 l_error_stage VARCHAR2(2000) ;
4951 l_rowid varchar2(45) ;
4952 l_dup_event VARCHAR2(1);
4953 l_default_report VARCHAR2(1);
4954 l_report VARCHAR2(1);
4955 l_report_template_id NUMBER;
4956 l_length NUMBER;
4957
4958 BEGIN
4959 init_message_stack;
4960
4961 G_msg_count := x_msg_count ;
4962 G_msg_data := x_MSG_DATA ;
4963 G_calling_module := P_CALLING_MODULE ;
4964
4965 -- ============
4966 -- Initialize the return status.
4967 -- ============
4968
4969 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
4970 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
4971 X_return_status := FND_API.G_RET_STS_SUCCESS ;
4972 END IF ;
4973
4974 SAVEPOINT create_notification_pvt ;
4975
4976 G_stage := 'FND_API.Compatible_API_Call' ;
4977
4978 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
4979 p_api_version_number ,
4980 l_api_name ,
4981 G_pkg_name )
4982 THEN
4983 RAISE e_ver_mismatch ;
4984 END IF ;
4985
4986 G_notification_rec := P_notification_rec;
4987
4988 -- ==================================================
4989 -- Check for required columns for Create Notification
4990 -- ==================================================
4991
4992 G_stage := 'procedure_check_notification_required_columns' ;
4993
4994 IF G_notification_rec.award_id IS NULL THEN
4995 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
4996 set_return_status ( X_return_status, 'B') ;
4997 END IF ;
4998
4999 IF G_notification_rec.event_type IS NULL THEN
5000 add_message_to_stack( P_label => 'GMS_NTF_EVENT_TYPE_NULL' ) ;
5001 set_return_status ( X_return_status, 'B') ;
5002 END IF ;
5003
5004 IF G_notification_rec.user_id IS NULL THEN
5005 add_message_to_stack( P_label => 'GMS_NTF_USER_ID_NULL' ) ;
5006 set_return_status ( X_return_status, 'B') ;
5007 END IF ;
5008
5009 -- ===================================================================
5010 -- Need to make sure that the return status is success from the above
5011 -- validations. There is no point in doing further validations as the
5012 -- required columns donot have values. So we raise an Error.
5013 -- ===================================================================
5014
5015 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5016 RAISE FND_API.G_EXC_ERROR;
5017 END IF ;
5018
5019 -- =============================================
5020 -- Verify the duplicate events for a given award
5021 -- =============================================
5022
5023 G_stage := 'create_notification.Verify_duplicate_events' ;
5024
5025 OPEN l_dup_event_csr ;
5026 FETCH l_dup_event_csr INTO l_dup_event;
5027
5028 IF l_dup_event_csr%FOUND THEN
5029 add_message_to_stack( P_label => 'GMS_NTF_AWD _EVENT_DUP' ) ;
5030 set_return_status ( X_return_status, 'B') ;
5031 END IF;
5032 CLOSE l_dup_event_csr ;
5033
5034
5035 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5036 RAISE FND_API.G_EXC_ERROR;
5037 END IF ;
5038
5039
5040 G_stage := 'create_notification.Verify_report_existence' ;
5041
5042
5043 -- ==============================================================================
5044 -- Verify the existence of report for a given award if the event type is a report
5045 -- ==============================================================================
5046
5047 IF substr(G_notification_rec.event_type,1,6) = 'REPORT' THEN
5048
5049 l_length := length(G_notification_rec.event_type);
5050 l_report_template_id := substr(G_notification_rec.event_type,7,l_length);
5051
5052 OPEN l_default_report_csr(l_report_template_id) ;
5053 FETCH l_default_report_csr INTO l_default_report;
5054
5055 IF l_default_report_csr%NOTFOUND THEN
5056 OPEN l_report_csr(l_report_template_id) ;
5057 FETCH l_report_csr INTO l_report;
5058 IF l_report_csr%NOTFOUND THEN
5059 add_message_to_stack( P_label => 'GMS_NTF_AWD_REP_NOT_EXIST' ) ;
5060 set_return_status ( X_return_status, 'B') ;
5061 END IF;
5062 END IF;
5063 CLOSE l_default_report_csr ;
5064
5065 IF l_report_csr%ISOPEN THEN
5066 CLOSE l_dup_event_csr ;
5067 END IF;
5068
5069 END IF;
5070 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5071 RAISE FND_API.G_EXC_ERROR;
5072 END IF ;
5073
5074 G_stage := 'gms_notification_pkg.insert_row' ;
5075
5076 gms_notification_pkg.insert_row(
5077 X_ROWID => l_rowid,
5078 X_AWARD_ID => G_notification_rec.award_id,
5079 X_EVENT_TYPE => G_notification_rec.event_type,
5080 X_USER_ID => G_notification_rec.user_id
5081 );
5082
5083
5084 G_stage := 'Notification Created Successfully' ;
5085
5086 EXCEPTION
5087 WHEN E_VER_MISMATCH THEN
5088 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH',
5089 p_token1 => 'SUPVER',
5090 P_VAL1 => g_api_version_number) ;
5091 set_return_status(X_return_status, 'B' ) ;
5092 x_msg_count := G_msg_count ;
5093 x_msg_data := G_msg_data ;
5094
5095 WHEN FND_API.G_EXC_ERROR THEN
5096 ROLLBACK TO create_notification_pvt ;
5097 set_return_status(X_return_status, 'B' ) ;
5098 x_msg_count := G_msg_count ;
5099 x_msg_data := G_msg_data ;
5100 WHEN OTHERS THEN
5101 ROLLBACK TO create_notification_pvt;
5102 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5103
5104 FND_MSG_PUB.add_exc_msg
5105 ( p_pkg_name => G_PKG_NAME
5106 , p_procedure_name => l_api_name );
5107 FND_MSG_PUB.Count_And_Get
5108 ( p_count => x_msg_count ,
5109 p_data => x_msg_data );
5110
5111 END CREATE_NOTIFICATION ;
5112
5113 -- ========================================================================
5114 -- Add Funding
5115 -- ========================================================================
5116
5117 PROCEDURE ADD_FUNDING
5118 (X_MSG_COUNT IN OUT NOCOPY NUMBER ,
5119 X_MSG_DATA IN OUT NOCOPY VARCHAR2 ,
5120 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2 ,
5121 X_GMS_PROJECT_FUNDING_ID IN OUT NOCOPY NUMBER ,
5122 X_ROW_ID OUT NOCOPY VARCHAR2 ,
5123 P_CALLING_MODULE IN VARCHAR2 ,
5124 P_API_VERSION_NUMBER IN NUMBER ,
5125 P_AWARD_ID IN NUMBER,
5126 P_INSTALLMENT_ID IN NUMBER,
5127 P_PROJECT_ID IN NUMBER,
5128 P_TASK_ID IN NUMBER,
5129 P_AMOUNT IN NUMBER,
5130 P_FUNDING_DATE IN DATE
5131 )
5132 IS
5133
5134 CURSOR l_valid_award_csr IS
5135 SELECT *
5136 FROM gms_awards_all
5137 WHERE award_id = P_AWARD_ID;
5138
5139 CURSOR l_valid_installment_csr IS
5140 SELECT *
5141 FROM gms_installments
5142 WHERE award_id = P_AWARD_ID
5143 AND installment_id = P_INSTALLMENT_ID;
5144
5145 CURSOR l_valid_project_csr IS
5146 SELECT p.project_status_code,
5147 pt.project_type_class_code,
5148 pt.sponsored_flag,
5149 p.template_flag,
5150 p.start_date,
5151 p.closed_date
5152 FROM pa_projects_all p,
5153 pa_project_types_all pt
5154 WHERE p.project_id = P_PROJECT_ID
5155 AND pt.project_type = p.project_type
5156 And p.org_id = pt.org_id /* For Bug 5414832*/
5157 AND pt.sponsored_flag='Y' ;
5158
5159
5160 CURSOR l_valid_task_csr IS
5161 SELECT 'X'
5162 FROM pa_tasks
5163 WHERE project_id = P_project_id
5164 AND task_id = P_task_id
5165 AND task_id = top_task_id;
5166
5167 CURSOR l_invalid_funding_level_csr IS
5168 SELECT 'X'
5169 FROM gms_installments i,
5170 gms_summary_project_fundings f
5171 WHERE i.award_id = p_award_id
5172 AND f.installment_id = i.installment_id
5173 AND project_id = p_project_id
5174 AND ((task_id is null and p_task_id is not null) OR
5175 (task_id is not null and p_task_id is null));
5176
5177 CURSOR l_existing_funding_amount_csr IS
5178 SELECT task_id,total_funding_amount
5179 FROM gms_summary_project_fundings gspf
5180 WHERE installment_id = P_installment_id
5181 AND project_id = P_project_id
5182 AND NVL(task_id,-99) = NVL(P_task_id,-99);
5183
5184
5185 l_api_name VARCHAR2(30) := 'ADD_FUNDING' ;
5186 l_error_code VARCHAR2(2000) ;
5187 l_app_name VARCHAR2(10) ;
5188 l_error_stage VARCHAR2(2000) ;
5189 l_return_code VARCHAR2(1);
5190 l_errbuf VARCHAR2(2000);
5191 l_rowid VARCHAR2(45) ;
5192 l_funding_level VARCHAR2(1) := 'P' ;
5193 l_project_status_code VARCHAR2(60);
5194 l_project_type_class_code VARCHAR2(60);
5195 l_sponsored_flag VARCHAR2(1);
5196 l_template_flag VARCHAR2(1);
5197 l_project_start_date DATE;
5198 l_project_closed_date DATE;
5199 l_valid_task VARCHAR2(1);
5200 l_invalid_funding_level VARCHAR2(1);
5201 l_task_id NUMBER;
5202 l_existing_funding_amount NUMBER;
5203 l_total_installment_amount NUMBER;
5204 l_total_funding_amount NUMBER;
5205 l_project_funding_id NUMBER;
5206 l_installment_rec GMS_INSTALLMENTS%ROWTYPE;
5207 l_award_rec GMS_AWARDS_ALL%ROWTYPE;
5208
5209
5210
5211
5212 BEGIN
5213 init_message_stack;
5214
5215 G_msg_count := X_MSG_COUNT ;
5216 G_msg_data := X_MSG_DATA ;
5217 G_calling_module := P_CALLING_MODULE ;
5218
5219 -- ============================
5220 -- Initialize the return status.
5221 -- ============================
5222
5223 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) not in
5224 ( FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR ) THEN
5225 X_return_status := FND_API.G_RET_STS_SUCCESS ;
5226 END IF ;
5227
5228 SAVEPOINT add_funding_pvt ;
5229
5230 -- ===================================================
5231 -- Need to set global variables to use PA public APIs.
5232 -- ===================================================
5233
5234 G_stage := 'pa_interface_utils_pub.set_global_info';
5235
5236 pa_interface_utils_pub.set_global_info(p_api_version_number => 1.0,
5237 p_responsibility_id => FND_GLOBAL.resp_id,
5238 p_user_id => FND_GLOBAL.user_id,
5239 p_resp_appl_id => FND_GLOBAL.resp_appl_id, -- Bug 2534915
5240 p_msg_count => x_msg_count,
5241 p_msg_data =>x_msg_data,
5242 p_return_status => x_return_status);
5243
5244 IF x_return_status <> 'S' THEN
5245
5246 add_message_to_stack( P_label => 'GMS_SET_GLOBAL_INFO_FAILED');
5247 set_return_status ( X_return_status, 'U') ;
5248
5249 END IF;
5250
5251 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5252 RAISE FND_API.G_EXC_ERROR;
5253 END IF ;
5254
5255 G_stage := 'FND_API.Compatible_API_Call' ;
5256
5257 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
5258 p_api_version_number ,
5259 l_api_name ,
5260 G_pkg_name )
5261 THEN
5262 RAISE e_ver_mismatch ;
5263 END IF ;
5264
5265 -- ===================================================================
5266 -- Check for required columns for Add Funding
5267 -- Award Id should NOT be NULL.
5268 -- Installment Id should NOT be NULL.
5269 -- Project Id should NOT be NULL.
5270 -- Amount should NOT be NULL.
5271 -- Amount should NOT be NEGATIVE.
5272 -- Project Funding Date should NOT be NULL.
5273 -- ===================================================================
5274
5275 IF P_AWARD_ID IS NULL THEN
5276 add_message_to_stack( P_label => 'GMS_AWD_ID_NULL' ) ;
5277 set_return_status ( X_return_status, 'B') ;
5278 END IF ;
5279
5280 IF P_INSTALLMENT_ID IS NULL THEN
5281 add_message_to_stack( P_label => 'GMS_FND_INST_ID_NULL' ) ;
5282 set_return_status ( X_return_status, 'B') ;
5283 END IF ;
5284
5285 IF P_PROJECT_ID IS NULL THEN
5286 add_message_to_stack( P_label => 'GMS_FND_PROJECT_ID_NULL' ) ;
5287 set_return_status ( X_return_status, 'B') ;
5288 END IF ;
5289
5290 IF P_AMOUNT IS NULL THEN
5291 add_message_to_stack( P_label => 'GMS_FND_AMOUNT_NULL' ) ;
5292 set_return_status ( X_return_status, 'B') ;
5293 END IF ;
5294
5295 IF P_AMOUNT < 0 THEN
5296 add_message_to_stack( P_label => 'GMS_FND_AMOUNT_NEG' ) ;
5297 set_return_status ( X_return_status, 'B') ;
5298 END IF ;
5299
5300 IF P_FUNDING_DATE IS NULL THEN
5301 add_message_to_stack( P_label => 'GMS_FND_DATE_NULL' ) ;
5302 set_return_status ( X_return_status, 'B') ;
5303 END IF ;
5304
5305 -- ===================================================================
5306 -- Need to make sure that the return status is success from the above
5307 -- validations. There is no point in doing further validations as the
5308 -- required columns donot have values. So we raise an Error.
5309 -- ===================================================================
5310
5311 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5312 RAISE FND_API.G_EXC_ERROR;
5313 END IF ;
5314
5315 -- ===================================================================
5316 -- Check for the Award Validity
5317 --
5318 -- We check whether the award is an existing award or not. We dont need
5319 -- to validate the award status. Funding is done from an award with any
5320 -- status like Closed,At Risk, On Hold, Active etc.
5321 -- ===================================================================
5322
5323 G_stage := 'add_funding.Verify_Award';
5324
5325 OPEN l_valid_award_csr ;
5326 FETCH l_valid_award_csr INTO l_award_rec;
5327
5328 IF l_valid_award_csr%NOTFOUND THEN
5329 add_message_to_stack( P_label => 'GMS_AWD_NOT_EXISTS' ) ;
5330 set_return_status ( X_return_status, 'B') ;
5331 END IF;
5332
5333 CLOSE l_valid_award_csr ;
5334
5335 -- ===================================================================
5336 -- Check for the Installment Validity
5337 -- We check for the installment existance in the database, so it is a
5338 -- valid installment, and we check the active flag also, as project
5339 -- funding can be done from active installments only.
5340 -- ===================================================================
5341
5342 G_stage := 'add_funding.Verify_Installment';
5343
5344 OPEN l_valid_installment_csr ;
5345 FETCH l_valid_installment_csr INTO l_installment_rec;
5346
5347 IF l_valid_installment_csr%NOTFOUND THEN
5348 add_message_to_stack( P_label => 'GMS_FND_INSTALL_ID_INVALID' ) ;
5349 set_return_status ( X_return_status, 'B') ;
5350 END IF;
5351
5352 CLOSE l_valid_installment_csr ;
5353
5354 IF l_installment_rec.active_flag <> 'Y' THEN
5355 add_message_to_stack( P_label => 'GMS_FND_INSTALL_INACTIVE' ) ;
5356 set_return_status ( X_return_status, 'B') ;
5357 END IF;
5358
5359
5360 -- ===================================================================
5361 -- Check for the Project Validity
5362 -- We do the following validations to make sure that the project is a
5363 -- valid one for project funding.
5364 -- The project should be defined in the system.
5365 -- The Project Type Class should be INDIRECT or CAPITAL
5366 -- The Project Status should NOT be CLOSED or UNAPPROVED
5367 -- The Project should not end before the Installment Start Date
5368 -- The Project should not start after the Installment End Date
5369 -- ===================================================================
5370
5371 G_stage := 'add_funding.Verify_project';
5372
5373 OPEN l_valid_project_csr;
5374 FETCH l_valid_project_csr INTO l_project_status_code,
5375 l_project_type_class_code,
5376 l_sponsored_flag,
5377 l_template_flag,
5378 l_project_start_date,
5379 l_project_closed_date;
5380
5381 IF l_valid_project_csr%NOTFOUND THEN
5382 add_message_to_stack( P_label => 'GMS_FND_PROJECT_ID_INVALID' ) ;
5383 set_return_status ( X_return_status, 'B') ;
5384 END IF;
5385
5386 CLOSE l_valid_project_csr ;
5387
5388 IF l_project_status_code IN ('CLOSED','UNAPPROVED') THEN
5389 add_message_to_stack( P_label => 'GMS_FND_PROJ_STATUS_INVALID' ) ;
5390 set_return_status ( X_return_status, 'B') ;
5391 END IF;
5392
5393 IF l_project_type_class_code NOT IN ('INDIRECT', 'CAPITAL') THEN
5394 add_message_to_stack( P_label => 'GMS_FND_PROJ_TYPE_INVALID' ) ;
5395 set_return_status ( X_return_status, 'B') ;
5396 END IF;
5397
5398 IF l_sponsored_flag <> 'Y' THEN
5399 add_message_to_stack( P_label => 'GMS_FND_NOT_SPONSORED_PROJ' ) ;
5400 set_return_status ( X_return_status, 'B') ;
5401 END IF;
5402
5403 IF l_template_flag = 'Y' THEN
5404 add_message_to_stack( P_label => 'GMS_FND_PROJ_TEMPLATE' ) ;
5405 set_return_status ( X_return_status, 'B') ;
5406 END IF;
5407
5408 IF NVL(l_project_closed_date,l_installment_rec.close_date) < l_installment_rec.start_date_active THEN
5409 add_message_to_stack( P_label => 'GMS_FND_PROJ_CLOSED' ) ;
5410 set_return_status ( X_return_status, 'B') ;
5411 END IF;
5412
5413 IF l_project_start_date > l_installment_rec.end_date_active THEN
5414 add_message_to_stack( P_label => 'GMS_FND_PROJ_NOT_STARTED' ) ;
5415 set_return_status ( X_return_status, 'B') ;
5416 END IF;
5417
5418
5419 -- ==============================================================================
5420 -- Determining the Funding Level
5421 -- We select task id, funding amount from gms_summary_project_fundings table
5422 -- We default the Project Funding Level to 'P' means Project Level.
5423 -- If the cursor did not find any record, then it would be 'F' meaning First Time
5424 -- time funding. If the select returns a Task Id then it would be Task Level
5425 -- Funding.
5426 -- ==============================================================================
5427
5428 G_stage := 'Add_funding.Getting Funding Level' ;
5429
5430 OPEN l_existing_funding_amount_csr;
5431 FETCH l_existing_funding_amount_csr INTO l_task_id,
5432 l_existing_funding_amount;
5433 IF l_existing_funding_amount_csr%NOTFOUND THEN
5434 l_funding_level := 'F';
5435 END IF;
5436
5437 CLOSE l_existing_funding_amount_csr;
5438
5439 IF l_task_id IS NOT NULL THEN
5440 l_funding_level := 'T';
5441 END IF;
5442
5443
5444 -- ============================================================
5445 -- Check for Task Id required or not. If the funding level is T
5446 -- Task Id becomes mandatory. If the funding level is P task Id
5447 -- should be null. If funding level is F meaning this is the
5448 -- first time funding so we need not worry even if task id passed
5449 -- or null
5450 -- ============================================================
5451
5452 -- Bug 2381094
5453
5454 G_stage := 'Add_funding.Validating Funding Level' ;
5455
5456 OPEN l_invalid_funding_level_csr;
5457 FETCH l_invalid_funding_level_csr INTO l_invalid_funding_level;
5458
5459 IF l_invalid_funding_level_csr%FOUND THEN
5460
5461 if P_task_id IS NOT NULL THEN
5462 add_message_to_stack( P_label => 'GMS_FND_PROJ_LVL_FUND');
5463 else
5464 add_message_to_stack( P_label => 'GMS_FND_TASK_LVL_FUND');
5465 end if;
5466
5467 set_return_status ( X_return_status, 'B') ;
5468
5469 END IF;
5470
5471 CLOSE l_invalid_funding_level_csr;
5472
5473
5474 -- =================================================================
5475 -- Check for the Task validity. Task need to be validated
5476 -- only when the task id is required, i.e if the funding
5477 -- level is in 'P' or 'F' then we need task id also. If it is
5478 -- passed then we need to validate it.
5479 -- ==================================================================
5480
5481 IF l_funding_level <> 'P' and p_task_id IS NOT NULL then
5482
5483 G_stage := 'add_funding.Verify_Task';
5484
5485 OPEN l_valid_task_csr ;
5486 FETCH l_valid_task_csr INTO l_valid_task;
5487
5488 IF l_valid_task_csr%NOTFOUND THEN
5489 add_message_to_stack( P_label => 'GMS_FND_TASK_INVALID' ) ;
5490 set_return_status ( X_return_status, 'B') ;
5491 END IF;
5492
5493 CLOSE l_valid_task_csr ;
5494
5495 END IF;
5496
5497 -- ===================================================================
5498 -- Check for the Project Funding Date Validity
5499 -- The Project FUnding Date should NOT be NULL.
5500 -- This is done at the beginning.
5501 -- The Project Funding Date should be with Pre Award Date,if exists or
5502 -- Award Start Date and Award Close Date.
5503 -- ===================================================================
5504
5505 IF (P_FUNDING_DATE < NVL(l_award_rec.preaward_date,l_award_rec.start_date_active)) OR
5506 (P_FUNDING_DATE > l_award_rec.close_date) THEN
5507 add_message_to_stack( P_label => 'GMS_FND_FUND_DATE_INVALID' ) ;
5508 set_return_status ( X_return_status, 'B') ;
5509 END IF;
5510
5511 -- ==================================================================
5512 -- Amount Check
5513 --
5514 -- The Amount check will be done in the following ways
5515 -- 1) The Funding Amount should not be negative
5516 -- This is done at the beginning
5517 -- 2) The total funding Amount for all the projects and
5518 -- Tasks for the given Installment should not exceed
5519 -- Total Installment Amount
5520 -- 3) The total funding amount for the project and task and
5521 -- Installment should not go below the budgeted amount
5522 --
5523 -- We are not doing the budget amount validation as
5524 -- ADD_FUNDING is supposed to increase the funding amount
5525 -- always. When negative amounts are considered fro Project
5526 -- Funding then we need to do the above validation also.
5527 -- ==================================================================
5528
5529 -- ============================================
5530 -- Validate the amount with installment amount
5531 -- ============================================
5532
5533 G_stage := 'Add_funding.Verify_amount_with_installment' ;
5534
5535 l_total_installment_amount := l_installment_rec.direct_cost + l_installment_rec.indirect_cost;
5536
5537 l_total_funding_amount := NVL(l_existing_funding_amount,0) + P_Amount;
5538
5539 IF l_total_funding_amount > l_total_installment_amount THEN
5540 add_message_to_stack( P_label => 'GMS_FUNDING_AMOUNT_EXCEEDED' ) ;
5541 set_return_status ( X_return_status, 'B') ;
5542 END IF ;
5543
5544 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5545 RAISE FND_API.G_EXC_ERROR;
5546 END IF ;
5547
5548 -- ======================================================
5549 -- Sequence number is required for gms_project_funding_id
5550 -- ======================================================
5551
5552 G_stage := 'gms_project_fundings_pkg.insert_row' ;
5553
5554 SELECT gms_project_fundings_s.nextval
5555 INTO x_gms_project_funding_id
5556 FROM dual;
5557
5558 GMS_PROJECT_FUNDINGS_PKG.INSERT_ROW(
5559 X_ROWID => l_rowid,
5560 X_GMS_PROJECT_FUNDING_ID => x_gms_project_funding_id,
5561 X_PROJECT_FUNDING_ID => l_project_funding_id,
5562 X_PROJECT_ID => P_project_id,
5563 X_TASK_ID => P_task_id,
5564 X_INSTALLMENT_ID => P_installment_id,
5565 X_FUNDING_AMOUNT => P_amount,
5566 X_DATE_ALLOCATED => P_funding_date,
5567 X_MODE => 'R'
5568 );
5569
5570 -- ================================================================
5571 -- GMS_SUMMARY_PROJECT_FUNDINGS need to be updated with this amount
5572 -- if this project and task and installment combination exists or
5573 -- this need to be inserted.
5574 -- ================================================================
5575
5576 G_stage := 'gms_summary_project_fundings.create_funding' ;
5577
5578 GMS_SUMM_FUNDING_PKG.CREATE_GMS_SUMMARY_FUNDING(
5579 X_INSTALLMENT_ID => P_installment_id,
5580 X_PROJECT_ID => P_project_id,
5581 X_TASK_ID => P_task_id,
5582 X_FUNDING_AMOUNT => P_amount,
5583 RETCODE => l_return_code,
5584 ERRBUF => l_errbuf
5585 );
5586
5587
5588 IF l_return_code <> 'S' THEN
5589
5590 add_message_to_stack( P_label => 'GMS_FND_SUMMARY_CRT_FAILED');
5591 set_return_status ( X_return_status, 'B') ;
5592
5593 END IF;
5594
5595 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5596 RAISE FND_API.G_EXC_ERROR;
5597 END IF ;
5598
5599 -- ============================================================
5600 -- PA_PROJECT_FUNDINGS and PA_SUMMARY_PROJECT_FUNDINGS
5601 -- need to be updated with this information. The revenue budget
5602 -- for the award project need to be re built with the updated
5603 -- amounts.
5604 -- =============================================================
5605
5606 G_stage := 'gms_multi_funding.create_award_funding' ;
5607
5608 GMS_MULTI_FUNDING.CREATE_AWARD_FUNDING(
5609 X_INSTALLMENT_ID => P_installment_id,
5610 X_ALLOCATED_AMOUNT => P_amount,
5611 X_DATE_ALLOCATED => P_funding_date,
5612 X_GMS_PROJECT_FUNDING_ID => x_gms_project_funding_id,
5613 X_PROJECT_FUNDING_ID => l_project_funding_id,
5614 X_APP_SHORT_NAME => l_app_name,
5615 X_MSG_COUNT => x_msg_count,
5616 ERRBUF => l_errbuf,
5617 RETCODE => l_return_code
5618 );
5619
5620 IF l_return_code <> 'S' THEN
5621
5622 add_message_to_stack( P_LABEL => 'GMS_FND_AWD_FND_FAILED');
5623 set_return_status ( X_RETURN_STATUS, 'B') ;
5624
5625 END IF;
5626
5627 IF NVL(X_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS then
5628 RAISE FND_API.G_EXC_ERROR;
5629 END IF ;
5630
5631 G_stage := 'At the End of the ADD_FUNDING';
5632
5633 EXCEPTION
5634 WHEN E_VER_MISMATCH THEN
5635 add_message_to_stack(
5636 P_LABEL => 'GMS_API_VER_MISMATCH',
5637 P_TOKEN1 => 'SUPVER',
5638 P_VAL1 => g_api_version_number) ;
5639 set_return_status(X_RETURN_STATUS, 'B' ) ;
5640 x_msg_count := G_msg_count ;
5641 x_msg_data := G_msg_data ;
5642
5643 WHEN FND_API.G_EXC_ERROR THEN
5644 ROLLBACK TO add_funding_pvt ;
5645 set_return_status(X_RETURN_STATUS, 'B' ) ;
5646 x_msg_count := G_msg_count ;
5647 x_msg_data := G_msg_data ;
5648 WHEN OTHERS THEN
5649 ROLLBACK TO add_funding_pvt;
5650 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5651
5652 FND_MSG_PUB.add_exc_msg
5653 ( P_PKG_NAME => G_PKG_NAME
5654 , P_PROCEDURE_NAME => l_api_name );
5655 FND_MSG_PUB.Count_And_Get
5656 ( P_COUNT => x_msg_count ,
5657 P_DATA => x_msg_data );
5658
5659 END ADD_FUNDING;
5660 END GMS_AWARD_PVT ;