1 PACKAGE BODY GMS_AWARD_PUB AS
2 -- $Header: gmsawpbb.pls 120.2.12010000.2 2008/10/30 11:27:11 rrambati ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMS_AWARD_PUB';
5 E_VER_MISMATCH EXCEPTION ;
6
7 G_msg_count NUMBER ;
8 G_msg_data varchar2(2000) ;
9 G_calling_module varchar2(30) ;
10 G_product_code varchar2(3) := 'GMS' ;
11 G_stage varchar2(80) ;
12
13 --
14 -- init_message_stack
15 -- init_message_stack Initialize the the error message PL/SQL
16 -- Table and set the context for the private Package.
17 -- This is called at the begining of the program unit.
18 --
19 -- +++++++++++++
20 PROCEDURE init_message_stack is
21 BEGIN
22 gms_award_pvt.init_message_stack ;
23
24 END init_message_stack ;
25
26 --
27 -- reset_message_flag
28 -- reset_message_flag set the context for the private Package.
29 -- This is called at the end of the program unit.
30 --
31 -- ++++++++++++
32 PROCEDURE reset_message_flag is
33 BEGIN
34 gms_award_pvt.reset_message_flag ;
35
36 END reset_message_flag ;
37
38 --
39 -- add_message_TO_STACK
40 -- add_message_to_stack - This is a private program unit
41 -- defined in this package to add error messages to
42 -- the PL/SQL message table.
43 -- This updates the G_msg_count after adding a message
44 -- to the message variable.
45 --
46
47 -- +++++++++++++
48 PROCEDURE add_message_to_stack( P_Label IN Varchar2,
49 P_token1 IN varchar2 default NULL,
50 P_val1 IN varchar2 default NULL,
51 P_token2 IN varchar2 default NULL,
52 P_val2 in varchar2 default NULL,
53 P_token3 IN varchar2 default NULL,
54 P_val3 in varchar2 default NULL ) is
55 BEGIN
56
57 gms_award_pvt.add_message_to_stack(P_Label, P_token1, P_val1, P_token2, P_val2, P_token3, P_val3 ) ;
58
59
60 END add_message_to_stack ;
61
62 -- SET_RETURN_STATUS
63 -- This routine sets the return status for the failures.
64 -- X_RETURN_STATUS : <S>uccess, [E] Business Rule Violation
65 -- U - Unexpected Error
66 -- P_TYPE := B - Business Validations, E- Exception
67 --
68
69 -- +++++++++++++
70 PROCEDURE set_return_status(X_return_status IN OUT NOCOPY VARCHAR2,
71 p_type in varchar2 DEFAULT 'B' ) is
72 begin
73 gms_award_pvt.set_return_status( X_return_status, p_type ) ;
74
75 END set_return_status ;
76
77 -- =====================================================
78 -- Utility Functions for creating an award
79 -- =====================================================
80
81 --
82 -- get_Funds_ctrl_code
83 -- This is a function defined to return
84 -- funds control code for the corresponding name.
85 -- The code values are as follows
86 -- B- ABSOLUTE
87 -- D- ADVISORY
88 -- N- NONE
89 --
90 FUNCTION get_Funds_ctrl_code( p_fctrl_name varchar2 )
91 return varchar2 is
92 l_dummy varchar2(1) ;
93 begin
94 IF p_fctrl_name = 'ABSOLUTE' THEN
95 l_dummy := 'B' ;
96 ELSIF p_fctrl_name = 'ADVISORY' THEN
97 l_dummy := 'D' ;
98 ELSIF p_fctrl_name = 'NONE' THEN
99 l_dummy := 'N' ;
100 END IF ;
101
102 return l_dummy ;
103 END get_Funds_ctrl_code ;
104
105 --
106 -- proc_set_record
107 -- This is a program unit defined to populate
108 -- record group for the parameters supplied
109 -- to the create_award API.
110 --
111 -- +++++++++++++
112 PROCEDURE proc_set_record (
113 AWARD_NUMBER IN VARCHAR2,
114 AWARD_SHORT_NAME IN VARCHAR2,
115 AWARD_FULL_NAME IN VARCHAR2,
116 FUNDING_SOURCE_ID IN NUMBER,
117 START_DATE_ACTIVE IN DATE,
118 END_DATE_ACTIVE IN DATE,
119 CLOSE_DATE IN DATE,
120 FUNDING_SOURCE_AWARD_NUMBER IN VARCHAR2,
121 AWARD_PURPOSE_CODE IN VARCHAR2,
122 STATUS IN VARCHAR2,
123 ALLOWABLE_SCHEDULE_ID IN NUMBER,
124 IDC_SCHEDULE_ID IN NUMBER,
125 REVENUE_DISTRIBUTION_RULE IN VARCHAR2,
126 BILLING_DISTRIBUTION_RULE IN VARCHAR2,
127 BILLING_FORMAT IN VARCHAR2,
128 BILLING_TERM IN NUMBER,
129 AWARD_PROJECT_ID IN NUMBER,
130 AGREEMENT_ID IN NUMBER,
131 AWARD_TEMPLATE_FLAG IN VARCHAR2,
132 PREAWARD_DATE IN DATE,
133 AWARD_MANAGER_ID IN NUMBER,
134 AGENCY_SPECIFIC_FORM IN VARCHAR2,
135 BILL_TO_CUSTOMER_ID IN NUMBER,
136 TRANSACTION_NUMBER IN VARCHAR2,
137 AMOUNT_TYPE IN VARCHAR2,
138 BOUNDARY_CODE IN VARCHAR2,
139 FUND_CONTROL_LEVEL_AWARD IN VARCHAR2,
140 FUND_CONTROL_LEVEL_TASK IN VARCHAR2,
141 FUND_CONTROL_LEVEL_RES_GRP IN VARCHAR2,
142 FUND_CONTROL_LEVEL_RES IN VARCHAR2,
143 ATTRIBUTE_CATEGORY IN VARCHAR2,
144 ATTRIBUTE1 IN VARCHAR2,
145 ATTRIBUTE2 IN VARCHAR2,
146 ATTRIBUTE3 IN VARCHAR2,
147 ATTRIBUTE4 IN VARCHAR2,
148 ATTRIBUTE5 IN VARCHAR2,
149 ATTRIBUTE6 IN VARCHAR2,
150 ATTRIBUTE7 IN VARCHAR2,
151 ATTRIBUTE8 IN VARCHAR2,
152 ATTRIBUTE9 IN VARCHAR2,
153 ATTRIBUTE10 IN VARCHAR2,
154 ATTRIBUTE11 IN VARCHAR2,
155 ATTRIBUTE12 IN VARCHAR2,
156 ATTRIBUTE13 IN VARCHAR2,
157 ATTRIBUTE14 IN VARCHAR2,
158 ATTRIBUTE15 IN VARCHAR2,
159 TEMPLATE_START_DATE_ACTIVE IN DATE,
160 TEMPLATE_END_DATE_ACTIVE IN DATE,
161 TYPE IN VARCHAR2,
162 ORG_ID IN NUMBER,
163 COST_IND_SCH_FIXED_DATE IN DATE,
164 LABOR_INVOICE_FORMAT_ID IN NUMBER,
165 NON_LABOR_INVOICE_FORMAT_ID IN NUMBER,
166 BILL_TO_ADDRESS_ID IN NUMBER,
167 SHIP_TO_ADDRESS_ID IN NUMBER,
168 LOC_BILL_TO_ADDRESS_ID IN NUMBER,
169 LOC_SHIP_TO_ADDRESS_ID IN NUMBER,
170 AWARD_ORGANIZATION_ID IN NUMBER,
171 HARD_LIMIT_FLAG IN VARCHAR2,
172 INVOICE_LIMIT_FLAG IN VARCHAR2, /*Bug 6642901*/
173 BILLING_OFFSET IN NUMBER,
174 BILLING_CYCLE_ID IN NUMBER,
175 BUDGET_WF_ENABLED_FLAG IN VARCHAR2,
176 PROPOSAL_ID IN NUMBER,
177 AWARD_REC IN OUT NOCOPY gms_awards_all%ROWTYPE ) is
178 BEGIN
179 award_rec.AWARD_NUMBER := AWARD_NUMBER ;
180 award_rec.AWARD_SHORT_NAME := AWARD_SHORT_NAME ;
181 award_rec.AWARD_FULL_NAME := AWARD_FULL_NAME ;
182 award_rec.FUNDING_SOURCE_ID := FUNDING_SOURCE_ID ;
183 award_rec.START_DATE_ACTIVE := START_DATE_ACTIVE ;
184 award_rec.END_DATE_ACTIVE := END_DATE_ACTIVE ;
185 award_rec.CLOSE_DATE := CLOSE_DATE ;
186 award_rec.FUNDING_SOURCE_AWARD_NUMBER := FUNDING_SOURCE_AWARD_NUMBER ;
187 award_rec.AWARD_PURPOSE_CODE := AWARD_PURPOSE_CODE ;
188 award_rec.STATUS := STATUS ;
189 award_rec.ALLOWABLE_SCHEDULE_ID := ALLOWABLE_SCHEDULE_ID ;
190 award_rec.IDC_SCHEDULE_ID := IDC_SCHEDULE_ID ;
191 award_rec.REVENUE_DISTRIBUTION_RULE := REVENUE_DISTRIBUTION_RULE ;
192 award_rec.BILLING_DISTRIBUTION_RULE := BILLING_DISTRIBUTION_RULE ;
193 award_rec.BILLING_FORMAT := BILLING_FORMAT ;
194 award_rec.BILLING_TERM := BILLING_TERM ;
195 award_rec.AWARD_PROJECT_ID := AWARD_PROJECT_ID ;
196 award_rec.AGREEMENT_ID := AGREEMENT_ID ;
197 award_rec.AWARD_TEMPLATE_FLAG := AWARD_TEMPLATE_FLAG ;
198 award_rec.PREAWARD_DATE := PREAWARD_DATE ;
199 award_rec.AWARD_MANAGER_ID := AWARD_MANAGER_ID ;
200 award_rec.AGENCY_SPECIFIC_FORM := AGENCY_SPECIFIC_FORM ;
201 award_rec.BILL_TO_CUSTOMER_ID := BILL_TO_CUSTOMER_ID ;
202 award_rec.TRANSACTION_NUMBER := TRANSACTION_NUMBER ;
203 award_rec.AMOUNT_TYPE := AMOUNT_TYPE ;
204 award_rec.BOUNDARY_CODE := BOUNDARY_CODE ;
205 award_rec.FUND_CONTROL_LEVEL_AWARD := FUND_CONTROL_LEVEL_AWARD ;
206 award_rec.FUND_CONTROL_LEVEL_TASK := FUND_CONTROL_LEVEL_TASK ;
207 award_rec.FUND_CONTROL_LEVEL_RES_GRP := FUND_CONTROL_LEVEL_RES_GRP ;
208 award_rec.FUND_CONTROL_LEVEL_RES := FUND_CONTROL_LEVEL_RES ;
209 award_rec.ATTRIBUTE_CATEGORY := ATTRIBUTE_CATEGORY ;
210 award_rec.ATTRIBUTE1 := ATTRIBUTE1 ;
211 award_rec.ATTRIBUTE2 := ATTRIBUTE2 ;
212 award_rec.ATTRIBUTE3 := ATTRIBUTE3 ;
213 award_rec.ATTRIBUTE4 := ATTRIBUTE4 ;
214 award_rec.ATTRIBUTE5 := ATTRIBUTE5 ;
215 award_rec.ATTRIBUTE6 := ATTRIBUTE6 ;
216 award_rec.ATTRIBUTE7 := ATTRIBUTE7 ;
217 award_rec.ATTRIBUTE8 := ATTRIBUTE8 ;
218 award_rec.ATTRIBUTE9 := ATTRIBUTE9 ;
219 award_rec.ATTRIBUTE10 := ATTRIBUTE10 ;
220 award_rec.ATTRIBUTE11 := ATTRIBUTE11 ;
221 award_rec.ATTRIBUTE12 := ATTRIBUTE12 ;
222 award_rec.ATTRIBUTE13 := ATTRIBUTE13 ;
223 award_rec.ATTRIBUTE14 := ATTRIBUTE14 ;
224 award_rec.ATTRIBUTE15 := ATTRIBUTE15 ;
225 award_rec.TEMPLATE_START_DATE_ACTIVE := TEMPLATE_START_DATE_ACTIVE ;
226 award_rec.TEMPLATE_END_DATE_ACTIVE := TEMPLATE_END_DATE_ACTIVE ;
227 award_rec.TYPE := TYPE ;
228 award_rec.ORG_ID := ORG_ID ;
229 award_rec.COST_IND_SCH_FIXED_DATE := COST_IND_SCH_FIXED_DATE ;
230 award_rec.LABOR_INVOICE_FORMAT_ID := LABOR_INVOICE_FORMAT_ID ;
231 award_rec.NON_LABOR_INVOICE_FORMAT_ID := NON_LABOR_INVOICE_FORMAT_ID ;
232 award_rec.BILL_TO_ADDRESS_ID := BILL_TO_ADDRESS_ID ;
233 award_rec.SHIP_TO_ADDRESS_ID := SHIP_TO_ADDRESS_ID ;
234 award_rec.LOC_BILL_TO_ADDRESS_ID := LOC_BILL_TO_ADDRESS_ID ;
235 award_rec.LOC_SHIP_TO_ADDRESS_ID := LOC_SHIP_TO_ADDRESS_ID ;
236 award_rec.AWARD_ORGANIZATION_ID := AWARD_ORGANIZATION_ID ;
237 award_rec.HARD_LIMIT_FLAG := HARD_LIMIT_FLAG ;
238 award_rec.INVOICE_LIMIT_FLAG := INVOICE_LIMIT_FLAG ; /*Bug 6642901*/
239 award_rec.BILLING_OFFSET := BILLING_OFFSET ;
240 award_rec.BILLING_CYCLE_ID := BILLING_CYCLE_ID ;
241 award_rec.BUDGET_WF_ENABLED_FLAG := BUDGET_WF_ENABLED_FLAG ;
242 award_rec.PROPOSAL_ID := PROPOSAL_ID ;
243 END proc_set_record ;
244 -- ========================================================================================
245 -- End of utility Functions/Procedures for award.
246 -- ========================================================================================
247
248 -- CREATE_AWARD
249 -- Create award is a public API provided to create award
250 -- into grants accounting. This is the API used to
251 -- transfer Legacy system data into grants accounting.
252 -- OUT NOCOPY Parameters meanings
253 -- P_MSG_COUNT : Holds no. of messages in the global
254 -- message table.
255 -- P_MSG_DATE : Holds the message code, if the API
256 -- returned only one error/warning message.
257 -- X_return_status : The indicator of success/Failure
258 -- S- Success, E- and U- Failure
259 -- p_award_id : The Award ID created.
260
261 -- ===============================================================================
262 -- CREATE_AWARD :
263 -- Create award has all the parameters that we have in gms_awards_all table.
264 -- The ID's in the table are replaced by corresponding value. Users must
265 -- provide decode values instead of code values for certain arguments.
266 -- X_return_status : S- Success,
267 -- E- Business Rule Validation error
268 -- U- Unexpected Error
269 -- P_API_VERSION_NUMBER : 1.0
270 -- ===============================================================================
271 -- +++++++++++++
272 PROCEDURE create_award(
273 X_MSG_COUNT OUT NOCOPY NUMBER,
274 X_MSG_DATA OUT NOCOPY VARCHAR2,
275 X_return_status OUT NOCOPY VARCHAR2,
276 X_AWARD_ID OUT NOCOPY NUMBER,
277 P_CALLING_MODULE IN VARCHAR2,
278 P_API_VERSION_NUMBER IN NUMBER,
279 P_LAST_UPDATE_DATE IN DATE,
280 P_LAST_UPDATED_BY IN NUMBER,
281 P_CREATED_BY IN NUMBER,
282 P_CREATION_DATE IN DATE,
283 P_LAST_UPDATE_LOGIN IN NUMBER,
284 P_AWARD_NUMBER IN VARCHAR2,
285 P_AWARD_SHORT_NAME IN VARCHAR2,
286 P_AWARD_FULL_NAME IN VARCHAR2,
287 P_AWARD_START_DATE IN DATE,
288 P_AWARD_END_DATE IN DATE,
289 P_AWARD_CLOSE_DATE IN DATE,
290 P_PREAWARD_DATE IN DATE,
291 P_AWARD_PURPOSE_CODE IN VARCHAR2,
292 P_AWARD_STATUS_CODE IN VARCHAR2,
293 P_AWARD_MANAGER_ID IN NUMBER,
294 P_AWARD_ORGANIZATION_ID IN NUMBER,
295 P_FUNDING_SOURCE_ID IN NUMBER,
296 P_FUNDING_SOURCE_AWARD_NUM IN VARCHAR2,
297 P_ALLOWABLE_SCHEDULE IN VARCHAR2,
298 P_INDIRECT_SCHEDULE IN VARCHAR2,
299 P_COST_IND_SCH_FIXED_DATE IN DATE,
300 P_REVENUE_DISTRIBUTION_RULE IN VARCHAR2,
301 P_BILLING_DISTRIBUTION_RULE IN VARCHAR2,
302 P_BILLING_FORMAT IN VARCHAR2,
303 P_BILLING_TERM_ID IN NUMBER,
304 P_AGENCY_FORM IN VARCHAR2,
305 P_BILL_TO_CUSTOMER_ID IN VARCHAR2,
306 P_LABOR_INVOICE_FORMAT_ID IN VARCHAR2,
307 P_NON_LABOR_INVOICE_FORMAT_ID IN VARCHAR2,
308 P_BILL_TO_ADDRESS_ID IN NUMBER,
309 P_SHIP_TO_ADDRESS_ID IN NUMBER,
310 P_LOC_BILL_TO_ADDRESS_ID IN NUMBER,
311 P_LOC_SHIP_TO_ADDRESS_ID IN NUMBER,
312 P_HARD_LIMIT_FLAG IN VARCHAR2,
313 P_INVOICE_LIMIT_FLAG IN VARCHAR2, /*Bug 6642901*/
314 P_BILLING_OFFSET IN NUMBER,
315 P_BILLING_CYCLE IN VARCHAR2,
316 P_TRANSACTION_NUM IN VARCHAR2,
317 P_AMOUNT_TYPE_CODE IN VARCHAR2,
318 P_BOUNDARY_CODE IN VARCHAR2,
319 P_FUNDS_CONTROL_AT_AWARD IN VARCHAR2,
320 P_FUNDS_CONTROL_AT_TASK IN VARCHAR2,
321 P_FUNDS_CONTROL_AT_RES_GROUP IN VARCHAR2,
322 P_FUNDS_CONTROL_AT_RES IN VARCHAR2,
323 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
324 P_ATTRIBUTE1 IN VARCHAR2,
325 P_ATTRIBUTE2 IN VARCHAR2,
326 P_ATTRIBUTE3 IN VARCHAR2,
327 P_ATTRIBUTE4 IN VARCHAR2,
328 P_ATTRIBUTE5 IN VARCHAR2,
329 P_ATTRIBUTE6 IN VARCHAR2,
330 P_ATTRIBUTE7 IN VARCHAR2,
331 P_ATTRIBUTE8 IN VARCHAR2,
332 P_ATTRIBUTE9 IN VARCHAR2,
333 P_ATTRIBUTE10 IN VARCHAR2,
334 P_ATTRIBUTE11 IN VARCHAR2,
335 P_ATTRIBUTE12 IN VARCHAR2,
336 P_ATTRIBUTE13 IN VARCHAR2,
337 P_ATTRIBUTE14 IN VARCHAR2,
338 P_ATTRIBUTE15 IN VARCHAR2,
339 P_AGREEMENT_TYPE IN VARCHAR2,
340 P_ORG_ID IN NUMBER,
341 P_WF_ENABLED_FLAG IN VARCHAR2,
342 P_PROPOSAL_ID IN NUMBER ) IS
343
344
345 l_api_name varchar2(30) := 'GMS_AWARD_PUB.CREATE_AWARD';
346 L_allow_schedule_id gms_allowability_schedules.allowability_schedule_id%TYPE ;
347 L_error BOOLEAN := FALSE ;
348
349 L_IDC_schedule_type pa_ind_rate_schedules.ind_rate_schedule_type%TYPE ;
350 L_ind_rate_sch_id pa_ind_rate_schedules.ind_rate_sch_id%TYPE ;
351
352 L_award_rec GMS_AWARDS_ALL%ROWTYPE ;
353 L_award_id GMS_AWARDS_ALL.AWARD_ID%TYPE ;
354
355 L_billing_cycle_id pa_billing_cycles.billing_cycle_id%TYPE ;
356 L_fctrl_award gms_awards_all.fund_control_level_award%TYPE;
357 L_fctrl_task gms_awards_all.fund_control_level_task%TYPE;
358 L_fctrl_RGP gms_awards_all.fund_control_level_res_grp%TYPE;
359 L_fctrl_res gms_awards_all.fund_control_level_res%TYPE;
360 L_row_id varchar2(45) ;
361 l_default_org_id GMS_AWARDS_ALL.ORG_ID%TYPE;
362 MO_ORG_INVALID_EXCEPTION EXCEPTION;
363
364
365 CURSOR C_BILL is
366 SELECT billing_cycle_id
367 FROM pa_billing_cycles
368 WHERE trunc(sysdate) between start_date_active
369 and NVL( end_date_active, sysdate )
370 and billing_cycle_name = P_BILLING_CYCLE ;
371
372 CURSOR C_allowable_sch is
373 SELECT allowability_schedule_id
374 FROM gms_allowability_schedules
375 WHERE allow_sch_name = P_ALLOWABLE_SCHEDULE ;
376
377 CURSOR C_IDC_SCH is
378 SELECT ind_rate_sch_id,
379 ind_rate_schedule_type
380 FROM pa_ind_rate_schedules
381 WHERE ind_rate_sch_name = P_INDIRECT_SCHEDULE ;
382
383 BEGIN
384 G_stage := 'CREATE_AWARD_BEGINS' ;
385 init_message_stack ;
386
387 savepoint CREATE_AWARD_SAVE ;
388 X_return_status := FND_API.G_RET_STS_SUCCESS;
389
390 G_stage := 'FND_API.Compatible_API_Call' ;
391
392 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
393 p_api_version_number ,
394 l_api_name ,
395 G_PKG_NAME )
396 THEN
397 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
398 RAISE e_ver_mismatch ;
399 END IF ;
400
401 --=============================
402 -- Shared Service Enhancement
403 --=============================
404 G_stage := 'MOAC INIT';
405
406 MO_GLOBAL.INIT('GMS');
407
408 l_default_org_id := MO_GLOBAL.get_valid_org(p_org_id);
409
410 IF l_default_org_id is null then
411 FND_MESSAGE.SET_NAME ('MO',' MO_ORG_INVALID');
412 RAISE MO_ORG_INVALID_EXCEPTION;
413 END IF;
414
415
416 -- ==============================
417 -- Convert Names/Decode into ID's.
418 -- ==============================
419 G_stage := 'ID-NAME-CONV' ;
420
421 IF P_ALLOWABLE_SCHEDULE is not NULL THEN
422 OPEN C_allowable_sch ;
423 FETCH C_allowable_sch into L_allow_schedule_id ;
424
425 IF C_allowable_sch%NOTFOUND THEN
426 add_message_to_stack( P_label => 'GMS_AWD_ACD_INVALID' ) ;
427 l_error := TRUE ;
428 END IF ;
429
430 CLOSE C_allowable_sch ;
431 END IF ;
432
433 IF P_INDIRECT_SCHEDULE is not NULL THEN
434 OPEN C_IDC_SCH ;
435 FETCH C_IDC_SCH into L_ind_rate_sch_id, L_IDC_schedule_type ;
436
437 IF C_IDC_SCH%NOTFOUND THEN
438 add_message_to_stack( P_label => 'GMS_IDC_SCH_INVALID' ) ;
439 l_error := TRUE ;
440 END IF ;
441
442 CLOSE C_IDC_SCH ;
443 END IF ;
444
445 IF P_billing_cycle is not NULL THEN
446 OPEN C_bill ;
447 FETCH C_bill into L_billing_cycle_id ;
448
449 IF C_BILL%NOTFOUND THEN
450 add_message_to_stack( P_label => 'GMS_BILL_CYC_INVALID' ) ;
451 l_error := TRUE ;
452 END IF ;
453
454 CLOSE C_BILL ;
455 END IF ;
456
457 IF NVL(P_FUNDS_CONTROL_AT_AWARD , 'ABSOLUTE') not in ( 'ABSOLUTE', 'ADVISORY', 'NONE' )
458 THEN
459 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_AWD_INVALID' ) ;
460 l_error := TRUE ;
461 END IF ;
462
463 IF NVL(P_FUNDS_CONTROL_AT_TASK , 'ABSOLUTE') not in ( 'ABSOLUTE', 'ADVISORY', 'NONE' )
464 THEN
465 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_TASK_INVALID' ) ;
466 l_error := TRUE ;
467 END IF ;
468
469 IF NVL(P_FUNDS_CONTROL_AT_RES_GROUP , 'ABSOLUTE') not in ( 'ABSOLUTE', 'ADVISORY', 'NONE' )
470 THEN
471 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_RGP_INVALID' ) ;
472 l_error := TRUE ;
473 END IF ;
474
475 IF NVL(P_FUNDS_CONTROL_AT_RES , 'ABSOLUTE') not in ( 'ABSOLUTE', 'ADVISORY', 'NONE' )
476 THEN
477 add_message_to_stack( P_label => 'GMS_FUNDS_CTRL_RES_INVALID' ) ;
478 l_error := TRUE ;
479 END IF ;
480
481 L_fctrl_award := get_funds_ctrl_code(P_FUNDS_CONTROL_AT_AWARD ) ;
482 L_fctrl_task := get_funds_ctrl_code(P_FUNDS_CONTROL_AT_TASK ) ;
483 L_fctrl_RGP := get_funds_ctrl_code(P_FUNDS_CONTROL_AT_RES_GROUP ) ;
484 L_fctrl_res := get_funds_ctrl_code(P_FUNDS_CONTROL_AT_RES ) ;
485
486 If l_error then
487 set_return_status(X_return_status, 'B' ) ;
488 END IF ;
489
490 G_stage := 'SET RECORD PARAM' ;
491
492 proc_set_record (
493 P_AWARD_NUMBER,
494 P_AWARD_SHORT_NAME,
495 P_AWARD_FULL_NAME,
496 P_FUNDING_SOURCE_ID,
497 P_AWARD_START_DATE,
498 P_AWARD_END_DATE,
499 P_AWARD_CLOSE_DATE,
500 P_FUNDING_SOURCE_AWARD_NUM,
501 P_AWARD_PURPOSE_CODE,
502 P_AWARD_STATUS_CODE,
503 L_allow_schedule_id,
504 L_ind_rate_sch_id,
505 P_REVENUE_DISTRIBUTION_RULE ,
506 P_BILLING_DISTRIBUTION_RULE ,
507 P_BILLING_FORMAT ,
508 P_BILLING_TERM_ID ,
509 NULL ,
510 NULL,
511 'DEFERRED',
512 P_PREAWARD_DATE,
513 P_AWARD_MANAGER_ID,
514 P_AGENCY_FORM,
515 P_BILL_TO_CUSTOMER_ID,
516 P_TRANSACTION_NUM ,
517 P_AMOUNT_TYPE_CODE,
518 P_BOUNDARY_CODE,
519 L_fctrl_award,
520 L_fctrl_task,
521 L_fctrl_rgp,
522 L_fctrl_res,
523 P_ATTRIBUTE_CATEGORY,
524 P_ATTRIBUTE1,
525 P_ATTRIBUTE2,
526 P_ATTRIBUTE3,
527 P_ATTRIBUTE4,
528 P_ATTRIBUTE5,
529 P_ATTRIBUTE6,
530 P_ATTRIBUTE7,
531 P_ATTRIBUTE8,
532 P_ATTRIBUTE9,
533 P_ATTRIBUTE10,
534 P_ATTRIBUTE11,
535 P_ATTRIBUTE12,
536 P_ATTRIBUTE13,
537 P_ATTRIBUTE14,
538 P_ATTRIBUTE15,
539 NULL,
540 NULL ,
541 P_AGREEMENT_TYPE ,
542 --P_ORG_ID ,
543 l_default_org_id,
544 P_COST_IND_SCH_FIXED_DATE ,
545 P_LABOR_INVOICE_FORMAT_ID ,
546 P_NON_LABOR_INVOICE_FORMAT_ID ,
547 P_BILL_TO_ADDRESS_ID ,
548 P_SHIP_TO_ADDRESS_ID ,
549 P_LOC_BILL_TO_ADDRESS_ID ,
550 P_LOC_SHIP_TO_ADDRESS_ID ,
551 P_AWARD_ORGANIZATION_ID,
552 P_HARD_LIMIT_FLAG ,
553 P_INVOICE_LIMIT_FLAG , /*Bug 6642901*/
554 P_BILLING_OFFSET ,
555 L_billing_cycle_id,
556 P_WF_ENABLED_FLAG ,
557 P_PROPOSAL_ID ,
558 L_AWARD_REC ) ;
559
560 l_award_rec.last_updated_by := NVL(P_LAST_UPDATED_BY, fnd_global.user_id) ;
561 l_award_rec.last_update_date := NVL(P_LAST_UPDATE_DATE, SYSDATE) ;
562 l_award_rec.last_updated_by := NVL(P_LAST_UPDATED_BY, fnd_global.user_id) ;
563 l_award_rec.created_by := NVL(P_CREATED_BY, fnd_global.user_id ) ;
564 l_award_rec.creation_date := NVL(P_CREATION_DATE, SYSDATE) ;
565 l_award_rec.last_update_login := NVL(P_LAST_UPDATE_LOGIN, fnd_global.user_id ) ;
566 l_award_rec.award_number := P_AWARD_NUMBER ;
567
568 G_stage := 'gms_award_pvt.create_award' ;
569 gms_award_pvt.create_award( X_msg_count,
570 X_MSG_DATA ,
571 X_return_status ,
572 L_ROW_ID ,
573 X_AWARD_ID ,
574 P_CALLING_MODULE,
575 P_API_VERSION_NUMBER,
576 L_AWARD_REC ) ;
577
578 reset_message_flag ;
579 EXCEPTION
580 WHEN e_ver_mismatch THEN
581 add_message_to_stack( P_label => 'GMS_API_VER_MISMATCH' ) ;
582 set_return_status(X_return_status, 'B' ) ;
583 X_msg_count := G_msg_count ;
584 X_msg_data := G_msg_data ;
585
586 WHEN fnd_api.g_exc_error THEN
587 ROLLBACK TO create_award_save ;
588 set_return_status(X_return_status, 'B' ) ;
589 X_msg_count := G_msg_count ;
590 X_msg_data := G_msg_data ;
591 WHEN others THEN
592 ROLLBACK TO create_award_save;
593 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
594 FND_MSG_PUB.add_exc_msg
595 ( p_pkg_name => G_PKG_NAME
596 , p_procedure_name => l_api_name );
597 FND_MSG_PUB.Count_And_Get
598 ( p_count => X_msg_count ,
599 p_data => X_msg_data );
600
601 END create_award ;
602
603 --===============================================================================
604 -- COPY_AWARD :
605 -- Copy award has all the parameters that we have in quick entry for award.
606 -- The ID's in the table are replaced by corresponding value. Users must
607 -- provide decode values instead of code values.
608 -- X_return_status : S- Success,
609 -- E- Business Rule Validation error
610 -- U- Unexpected Error
611 -- P_API_VERSION_NUMBER : 1.0
612 -- ===============================================================================
613 -- +++++++++++++
614 PROCEDURE copy_award(
615 X_MSG_COUNT OUT NOCOPY NUMBER,
616 X_MSG_DATA OUT NOCOPY VARCHAR2,
617 X_return_status OUT NOCOPY VARCHAR2,
618 X_AWARD_ID OUT NOCOPY NUMBER,
619 P_CALLING_MODULE IN VARCHAR2,
620 P_API_VERSION_NUMBER IN NUMBER,
621 P_AWARD_BASE_ID IN NUMBER,
622 P_AWARD_NUMBER IN VARCHAR2,
623 P_AWARD_SHORT_NAME IN VARCHAR2,
624 P_AWARD_FULL_NAME IN VARCHAR2,
625 P_AWARD_START_DATE IN DATE,
626 P_AWARD_END_DATE IN DATE,
627 P_AWARD_CLOSE_DATE IN DATE,
628 P_PREAWARD_DATE IN DATE,
629 P_AWARD_PURPOSE_CODE IN VARCHAR2,
630 P_AWARD_STATUS_CODE IN VARCHAR2,
631 P_AWARD_MANAGER_ID IN NUMBER,
632 P_AWARD_ORGANIZATION_ID IN NUMBER,
633 P_FUNDING_SOURCE_ID IN NUMBER,
634 P_FUNDING_SOURCE_AWARD_NUM IN VARCHAR2,
635 P_ALLOWABLE_SCHEDULE IN VARCHAR2,
636 P_INDIRECT_SCHEDULE IN VARCHAR2,
637 P_COST_IND_SCH_FIXED_DATE IN DATE,
638 P_REVENUE_DISTRIBUTION_RULE IN VARCHAR2,
639 P_BILLING_DISTRIBUTION_RULE IN VARCHAR2,
640 P_BILLING_TERM_ID IN NUMBER,
641 P_LABOR_INVOICE_FORMAT_ID IN VARCHAR2,
642 P_NON_LABOR_INVOICE_FORMAT_ID IN VARCHAR2,
643 P_BILLING_CYCLE IN VARCHAR2,
644 P_AMOUNT_TYPE_CODE IN VARCHAR2,
645 P_BOUNDARY_CODE IN VARCHAR2,
646 P_AGREEMENT_TYPE IN VARCHAR2,
647 P_PROPOSAL_ID IN NUMBER ) is
648 BEGIN
649 NULL ;
650 END copy_award ;
651
652
653 -- ===============================================================================
654 -- CREATE_AWARD_INSTALLMENT :
655 -- Create award installment has all the parameters that we have in gms_awards_installment table.
656 -- The ID's in the table are replaced by corresponding value. Users must
657 -- provide decode values instead of code values.
658 -- X_return_status : S- Success,
659 -- E- Business Rule Validation error
660 -- U- Unexpected Error
661 -- P_API_VERSION_NUMBER : 1.0
662 -- ===============================================================================
663 -- +++++++++++++
664 PROCEDURE CREATE_INSTALLMENT
665 (X_MSG_COUNT OUT NOCOPY NUMBER ,
666 X_MSG_DATA OUT NOCOPY VARCHAR2 ,
667 X_return_status OUT NOCOPY VARCHAR2 ,
668 X_INSTALLMENT_ID OUT NOCOPY NUMBER ,
669 P_CALLING_MODULE IN VARCHAR2 ,
670 P_API_VERSION_NUMBER IN NUMBER,
671 P_LAST_UPDATE_DATE IN DATE ,
672 P_LAST_UPDATED_BY IN NUMBER ,
673 P_CREATED_BY IN NUMBER ,
674 P_CREATION_DATE IN DATE ,
675 P_LAST_UPDATE_LOGIN IN NUMBER ,
676 P_AWARD_NUMBER IN VARCHAR2 ,
677 P_INSTALLMENT_NUMBER IN NUMBER ,
678 P_INSTALLMENT_TYPE_CODE IN VARCHAR2 ,
679 P_DESCRIPTION IN VARCHAR2 ,
680 P_ISSUE_DATE IN DATE ,
681 P_INSTALLMENT_START_DATE IN DATE ,
682 P_INSTALLMENT_END_DATE IN DATE ,
683 P_INSTALLMENT_CLOSE_DATE IN DATE ,
684 P_ACTIVE_FLAG IN VARCHAR2 ,
685 P_BILLABLE_FLAG IN VARCHAR2 ,
686 P_DIRECT_COST IN NUMBER ,
687 P_INDIRECT_COST IN NUMBER ,
688 P_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
689 P_ATTRIBUTE1 IN VARCHAR2 ,
690 P_ATTRIBUTE2 IN VARCHAR2 ,
691 P_ATTRIBUTE3 IN VARCHAR2 ,
692 P_ATTRIBUTE4 IN VARCHAR2 ,
693 P_ATTRIBUTE5 IN VARCHAR2 ,
694 P_ATTRIBUTE6 IN VARCHAR2 ,
695 P_ATTRIBUTE7 IN VARCHAR2 ,
696 P_ATTRIBUTE8 IN VARCHAR2 ,
697 P_ATTRIBUTE9 IN VARCHAR2 ,
698 P_ATTRIBUTE10 IN VARCHAR2 ,
699 P_ATTRIBUTE11 IN VARCHAR2 ,
700 P_ATTRIBUTE12 IN VARCHAR2 ,
701 P_ATTRIBUTE13 IN VARCHAR2 ,
702 P_ATTRIBUTE14 IN VARCHAR2 ,
703 P_ATTRIBUTE15 IN VARCHAR2 ,
704 P_PROPOSAL_ID IN NUMBER
705 ) IS
706 BEGIN
707 NULL ;
708 END create_installment ;
709
710
711 -- ==========================================================================================
712 -- Personal or Award Roles are user defined positions or functions that people perform in
713 -- activities funded by an award. Each Personnel or Award Role is linked to an individual
714 -- Award .
715 -- ==========================================================================================
716 -- +++++++++++++
717 PROCEDURE CREATE_PERSONNEL
718 (X_MSG_COUNT OUT NOCOPY NUMBER ,
719 X_MSG_DATA OUT NOCOPY VARCHAR2 ,
720 X_return_status OUT NOCOPY VARCHAR2 ,
721 X_PERSONNEL_ID OUT NOCOPY NUMBER ,
722 P_CALLING_MODULE IN VARCHAR2 ,
723 P_API_VERSION_NUMBER IN NUMBER ,
724 P_LAST_UPDATE_DATE IN DATE ,
725 P_LAST_UPDATED_BY IN NUMBER ,
726 P_CREATED_BY IN NUMBER ,
727 P_CREATION_DATE IN DATE ,
728 P_LAST_UPDATE_LOGIN IN NUMBER,
729 P_AWARD_NUMBER IN VARCHAR2 ,
730 P_AWARD_ROLE_CODE IN VARCHAR2 ,
731 P_PERSON_ID IN VARCHAR2 ,
732 P_START_DATE_ACTIVE IN DATE ,
733 P_END_DATE_ACTIVE IN DATE ,
734 P_REQUIRED_FLAG IN VARCHAR2
735 )IS
736 BEGIN
737 NULL ;
738 END create_personnel ;
739
740
741 -- ===========================================================================
742 -- Award terms and conditions are stipulated by the Grantor that are indicated
743 -- in an agreement or contract.
744 -- ===========================================================================
745 -- +++++++++++++
746 PROCEDURE CREATE_TERM_CONDITION
747 (X_MSG_COUNT OUT NOCOPY NUMBER ,
748 X_MSG_DATA OUT NOCOPY VARCHAR2 ,
749 X_return_status OUT NOCOPY VARCHAR2 ,
750 P_CALLING_MODULE IN VARCHAR2 ,
751 P_API_VERSION_NUMBER IN NUMBER ,
752 P_LAST_UPDATE_DATE IN DATE ,
753 P_LAST_UPDATED_BY IN NUMBER ,
754 P_CREATED_BY IN NUMBER ,
755 P_CREATION_DATE IN DATE ,
756 P_LAST_UPDATE_LOGIN IN NUMBER ,
757 P_AWARD_NUMBER IN VARCHAR2 ,
758 P_CATEGORY_NAME IN VARCHAR2 ,
759 P_TERM_ID IN NUMBER ,
760 P_OPERAND IN VARCHAR2 ,
761 P_VALUE IN NUMBER
762 ) is
763 BEGIN
764 NULL ;
765 END create_term_condition ;
766
767
768 -- =============================================================================
769 -- Reference Numbers are user defined values or characters assigned to an award
770 -- for identification purposes.
771 -- =============================================================================
772 PROCEDURE CREATE_REFERENCE_NUMBER
773 (X_MSG_COUNT OUT NOCOPY NUMBER ,
774 X_MSG_DATA OUT NOCOPY VARCHAR2 ,
775 X_return_status OUT NOCOPY VARCHAR2 ,
776 P_CALLING_MODULE IN VARCHAR2 ,
777 P_API_VERSION_NUMBER IN NUMBER ,
778 P_LAST_UPDATE_DATE IN DATE,
779 P_LAST_UPDATED_BY IN NUMBER ,
780 P_CREATED_BY IN NUMBER ,
781 P_CREATION_DATE IN DATE ,
782 P_LAST_UPDATE_LOGIN IN NUMBER ,
783 P_AWARD_NUMBER IN VARCHAR2 ,
784 P_REFERENCE_TYPE IN VARCHAR2 ,
785 P_REFERENCE_VALUE IN VARCHAR2 ,
786 P_REQUIRED_FLAG IN VARCHAR2
787 ) is
788 BEGIN
789 NULL ;
790 END create_reference_number ;
791
792
793 -- ==========================================================
794 -- Create Contacts
795 -- ==========================================================
796
797 PROCEDURE CREATE_CONTACT
798 (X_MSG_COUNT OUT NOCOPY NUMBER ,
799 X_MSG_DATA OUT NOCOPY VARCHAR2 ,
800 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
801 P_CALLING_MODULE IN VARCHAR2 ,
802 P_API_VERSION_NUMBER IN NUMBER ,
803 P_LAST_UPDATE_DATE IN DATE,
804 P_LAST_UPDATED_BY IN NUMBER ,
805 P_CREATED_BY IN NUMBER ,
806 P_CREATION_DATE IN DATE ,
807 P_LAST_UPDATE_LOGIN IN NUMBER ,
808 P_AWARD_NUMBER IN VARCHAR2 ,
809 P_CONTACT_ID IN NUMBER ,
810 P_PRIMARY_FLAG IN VARCHAR2 ,
811 P_USAGE_CODE IN VARCHAR2
812 ) IS
813 BEGIN
814 NULL ;
815 END create_contact ;
816
817 -- ==========================================================
818 -- Create Reports
819 -- ==========================================================
820 PROCEDURE CREATE_REPORT
821 (X_MSG_COUNT OUT NOCOPY NUMBER ,
822 X_MSG_DATA OUT NOCOPY VARCHAR2 ,
823 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
824 X_DEFAULT_REPORT_ID OUT NOCOPY NUMBER ,
825 P_CALLING_MODULE IN VARCHAR2 ,
826 P_API_VERSION_NUMBER IN NUMBER ,
827 P_LAST_UPDATE_DATE IN DATE,
828 P_LAST_UPDATED_BY IN NUMBER ,
829 P_CREATED_BY IN NUMBER ,
830 P_CREATION_DATE IN DATE ,
831 P_LAST_UPDATE_LOGIN IN NUMBER ,
832 P_AWARD_NUMBER IN VARCHAR2 ,
833 P_REPORT_NAME IN VARCHAR2 ,
834 P_FREQUENCY_CODE IN VARCHAR2 ,
835 P_DUE_WITHIN_DAYS IN NUMBER ,
836 P_SITE_USE_ID IN NUMBER ,
837 P_NUMBER_OF_COPIES IN NUMBER
838 ) IS
839 BEGIN
840 NULL ;
841 END create_report ;
842
843
844
845 END GMS_AWARD_PUB ;
[Home] [Help]