1 package body GMS_AWARDS_PKG as
2 /* $Header: gmsawawb.pls 120.2.12010000.2 2008/10/30 09:09:17 rrambati ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_AWARD_ID in NUMBER,
6 X_AWARD_NUMBER in VARCHAR2,
7 X_AWARD_SHORT_NAME in VARCHAR2,
8 X_AWARD_FULL_NAME in VARCHAR2,
9 X_FUNDING_SOURCE_ID in NUMBER,
10 X_START_DATE_ACTIVE in DATE,
11 X_END_DATE_ACTIVE in DATE,
12 X_CLOSE_DATE in DATE,
13 X_FUNDING_SOURCE_AWARD_NUMBER in VARCHAR2,
14 X_AWARD_PURPOSE_CODE in VARCHAR2,
15 X_STATUS in VARCHAR2,
16 X_ALLOWABLE_SCHEDULE_ID in NUMBER,
17 X_IDC_SCHEDULE_ID in NUMBER,
18 X_REVENUE_DISTRIBUTION_RULE in VARCHAR2,
19 X_BILLING_FREQUENCY in VARCHAR2,
20 X_BILLING_CYCLE_ID IN NUMBER,
21 X_BILLING_OFFSET IN NUMBER,
22 X_BILLING_DISTRIBUTION_RULE in VARCHAR2,
23 X_BILLING_FORMAT in VARCHAR2,
24 X_BILLING_TERM in NUMBER,
25 X_AWARD_PROJECT_ID in NUMBER,
26 X_AGREEMENT_ID in NUMBER,
27 X_AWARD_TEMPLATE_FLAG in VARCHAR2,
28 X_PREAWARD_DATE in DATE,
29 X_AGENCY_SPECIFIC_FORM in VARCHAR2,
30 X_BILL_TO_CUSTOMER_ID in NUMBER,
31 X_TRANSACTION_NUMBER in VARCHAR2,
32 X_AMOUNT_TYPE in VARCHAR2,
33 X_BOUNDARY_CODE in VARCHAR2,
34 X_FUND_CONTROL_LEVEL_AWARD in VARCHAR2,
35 X_FUND_CONTROL_LEVEL_TASK in VARCHAR2,
36 X_FUND_CONTROL_LEVEL_RES_GRP in VARCHAR2,
37 X_FUND_CONTROL_LEVEL_RES in VARCHAR2,
38 X_ATTRIBUTE_CATEGORY in VARCHAR2,
39 X_ATTRIBUTE1 in VARCHAR2,
40 X_ATTRIBUTE2 in VARCHAR2,
41 X_ATTRIBUTE3 in VARCHAR2,
42 X_ATTRIBUTE4 in VARCHAR2,
43 X_ATTRIBUTE5 in VARCHAR2,
44 X_ATTRIBUTE6 in VARCHAR2,
45 X_ATTRIBUTE7 in VARCHAR2,
46 X_ATTRIBUTE8 in VARCHAR2,
47 X_ATTRIBUTE9 in VARCHAR2,
48 X_ATTRIBUTE10 in VARCHAR2,
49 X_ATTRIBUTE11 in VARCHAR2,
50 X_ATTRIBUTE12 in VARCHAR2,
51 X_ATTRIBUTE13 in VARCHAR2,
52 X_ATTRIBUTE14 in VARCHAR2,
53 X_ATTRIBUTE15 in VARCHAR2,
54 X_ATTRIBUTE16 in VARCHAR2,
55 X_ATTRIBUTE17 in VARCHAR2,
56 X_ATTRIBUTE18 in VARCHAR2,
57 X_ATTRIBUTE19 in VARCHAR2,
58 X_ATTRIBUTE20 in VARCHAR2,
59 X_ATTRIBUTE21 in VARCHAR2,
60 X_ATTRIBUTE22 in VARCHAR2,
61 X_ATTRIBUTE23 in VARCHAR2,
62 X_ATTRIBUTE24 in VARCHAR2,
63 X_ATTRIBUTE25 in VARCHAR2,
64 X_TEMPLATE_START_DATE_ACTIVE in DATE,
65 X_TEMPLATE_END_DATE_ACTIVE in DATE,
66 X_ORG_ID in NUMBER, /* award_organization_id is created as org_id is now used due to multi- org */
67 X_TYPE in VARCHAR2,
68 X_COST_IND_SCH_FIXED_DATE in DATE,
69 X_LABOR_INVOICE_FORMAT_ID in NUMBER,
70 X_NON_LABOR_INVOICE_FORMAT_ID in NUMBER,
71 X_MODE in VARCHAR2 default 'R',
72 X_BILL_TO_ADDRESS_ID in NUMBER,
73 X_SHIP_TO_ADDRESS_ID in NUMBER,
74 X_LOC_BILL_TO_ADDRESS_ID in NUMBER,
75 X_LOC_SHIP_TO_ADDRESS_ID in NUMBER,
76 X_AWARD_ORGANIZATION_ID in NUMBER,
77 X_HARD_LIMIT_FLAG in VARCHAR2 ,
78 X_INVOICE_LIMIT_FLAG in VARCHAR2 , /*Bug 6642901 */
79 X_PROPOSAL_ID IN NUMBER DEFAULT NULL,
80 X_BUDGET_WF_ENABLED_FLAG in VARCHAR2
81 ) is
82 cursor C is select ROWID from GMS_AWARDS
83 where AWARD_ID = X_AWARD_ID;
84 X_LAST_UPDATE_DATE DATE;
85 X_LAST_UPDATED_BY NUMBER;
86 X_LAST_UPDATE_LOGIN NUMBER;
87
88 /* Adding the x_org_id for populating the org_id of multi-org*/
89
90 -- X_ORG_ID NUMBER := to_number(substrb(userenv('CLIENT_INFO'),1,10));
91 /* Commented above line as the single-org customers are having trouble as the returned value was not null and doesn't have any value i.e blank spaces?
92 */
93 -- X_ORG_ID NUMBER := fnd_profile.value('ORG_ID');
94 begin
95 X_LAST_UPDATE_DATE := SYSDATE;
96 if(X_MODE = 'I') then
97 X_LAST_UPDATED_BY := 1;
98 X_LAST_UPDATE_LOGIN := 0;
99 elsif (X_MODE = 'R') then
100 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
101 if X_LAST_UPDATED_BY is NULL then
102 X_LAST_UPDATED_BY := -1;
103 end if;
104 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
105 if X_LAST_UPDATE_LOGIN is NULL then
106 X_LAST_UPDATE_LOGIN := -1;
107 end if;
108 else
109 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
110 app_exception.raise_exception;
111 end if;
112 insert into GMS_AWARDS (
113 AWARD_ID,
114 AWARD_NUMBER,
115 AWARD_SHORT_NAME,
116 AWARD_FULL_NAME,
117 FUNDING_SOURCE_ID,
118 START_DATE_ACTIVE,
119 END_DATE_ACTIVE,
120 CLOSE_DATE,
121 FUNDING_SOURCE_AWARD_NUMBER,
122 AWARD_PURPOSE_CODE,
123 STATUS,
124 ALLOWABLE_SCHEDULE_ID,
125 IDC_SCHEDULE_ID,
126 REVENUE_DISTRIBUTION_RULE,
127 BILLING_FREQUENCY,
128 BILLING_CYCLE_ID,
129 BILLING_OFFSET,
130 BILLING_DISTRIBUTION_RULE,
131 BILLING_FORMAT,
132 BILLING_TERM,
133 AWARD_PROJECT_ID,
134 AGREEMENT_ID,
135 AWARD_TEMPLATE_FLAG,
136 PREAWARD_DATE,
137 AGENCY_SPECIFIC_FORM,
138 BILL_TO_CUSTOMER_ID,
139 TRANSACTION_NUMBER,
140 AMOUNT_TYPE,
141 BOUNDARY_CODE,
142 FUND_CONTROL_LEVEL_AWARD,
143 FUND_CONTROL_LEVEL_TASK,
144 FUND_CONTROL_LEVEL_RES_GRP,
145 FUND_CONTROL_LEVEL_RES,
146 ATTRIBUTE_CATEGORY,
147 ATTRIBUTE1,
148 ATTRIBUTE2,
149 ATTRIBUTE3,
150 ATTRIBUTE4,
151 ATTRIBUTE5,
152 ATTRIBUTE6,
153 ATTRIBUTE7,
154 ATTRIBUTE8,
155 ATTRIBUTE9,
156 ATTRIBUTE10,
157 ATTRIBUTE11,
158 ATTRIBUTE12,
159 ATTRIBUTE13,
160 ATTRIBUTE14,
161 ATTRIBUTE15,
162 ATTRIBUTE16,
163 ATTRIBUTE17,
164 ATTRIBUTE18,
165 ATTRIBUTE19,
166 ATTRIBUTE20,
167 ATTRIBUTE21,
168 ATTRIBUTE22,
169 ATTRIBUTE23,
170 ATTRIBUTE24,
171 ATTRIBUTE25,
172 TEMPLATE_START_DATE_ACTIVE,
173 TEMPLATE_END_DATE_ACTIVE,
174 ORG_ID,
175 TYPE,
176 COST_IND_SCH_FIXED_DATE,
177 LABOR_INVOICE_FORMAT_ID,
178 NON_LABOR_INVOICE_FORMAT_ID,
179 CREATION_DATE,
180 CREATED_BY,
181 LAST_UPDATE_DATE,
182 LAST_UPDATED_BY,
183 LAST_UPDATE_LOGIN,
184 BILL_TO_ADDRESS_ID,
185 SHIP_TO_ADDRESS_ID,
186 LOC_BILL_TO_ADDRESS_ID,
187 LOC_SHIP_TO_ADDRESS_ID,
188 AWARD_ORGANIZATION_ID,
189 HARD_LIMIT_FLAG ,
190 INVOICE_LIMIT_FLAG , /*Bug 6642901 */
191 PROPOSAL_ID ,
192 BUDGET_WF_ENABLED_FLAG
193
194 ) values (
195 X_AWARD_ID,
196 X_AWARD_NUMBER,
197 X_AWARD_SHORT_NAME,
198 X_AWARD_FULL_NAME,
199 X_FUNDING_SOURCE_ID,
200 X_START_DATE_ACTIVE,
201 X_END_DATE_ACTIVE,
202 X_CLOSE_DATE,
203 X_FUNDING_SOURCE_AWARD_NUMBER,
204 X_AWARD_PURPOSE_CODE,
205 X_STATUS,
206 X_ALLOWABLE_SCHEDULE_ID,
207 X_IDC_SCHEDULE_ID,
208 X_REVENUE_DISTRIBUTION_RULE,
209 X_BILLING_FREQUENCY,
210 X_BILLING_CYCLE_ID,
211 X_BILLING_OFFSET,
212 X_BILLING_DISTRIBUTION_RULE,
213 X_BILLING_FORMAT,
214 X_BILLING_TERM,
215 X_AWARD_PROJECT_ID,
216 X_AGREEMENT_ID,
217 X_AWARD_TEMPLATE_FLAG,
218 X_PREAWARD_DATE,
219 X_AGENCY_SPECIFIC_FORM,
220 X_BILL_TO_CUSTOMER_ID,
221 X_TRANSACTION_NUMBER,
222 X_AMOUNT_TYPE,
223 X_BOUNDARY_CODE,
224 X_FUND_CONTROL_LEVEL_AWARD,
225 X_FUND_CONTROL_LEVEL_TASK,
226 X_FUND_CONTROL_LEVEL_RES_GRP,
227 X_FUND_CONTROL_LEVEL_RES,
228 X_ATTRIBUTE_CATEGORY,
229 X_ATTRIBUTE1,
230 X_ATTRIBUTE2,
231 X_ATTRIBUTE3,
232 X_ATTRIBUTE4,
233 X_ATTRIBUTE5,
234 X_ATTRIBUTE6,
235 X_ATTRIBUTE7,
236 X_ATTRIBUTE8,
237 X_ATTRIBUTE9,
238 X_ATTRIBUTE10,
239 X_ATTRIBUTE11,
240 X_ATTRIBUTE12,
241 X_ATTRIBUTE13,
242 X_ATTRIBUTE14,
243 X_ATTRIBUTE15,
244 X_ATTRIBUTE16,
245 X_ATTRIBUTE17,
246 X_ATTRIBUTE18,
247 X_ATTRIBUTE19,
248 X_ATTRIBUTE20,
249 X_ATTRIBUTE21,
250 X_ATTRIBUTE22,
251 X_ATTRIBUTE23,
252 X_ATTRIBUTE24,
253 X_ATTRIBUTE25,
254 X_TEMPLATE_START_DATE_ACTIVE,
255 X_TEMPLATE_END_DATE_ACTIVE,
256 X_ORG_ID,
257 X_TYPE,
258 X_COST_IND_SCH_FIXED_DATE,
259 X_LABOR_INVOICE_FORMAT_ID,
260 X_NON_LABOR_INVOICE_FORMAT_ID,
261 X_LAST_UPDATE_DATE,
262 X_LAST_UPDATED_BY,
263 X_LAST_UPDATE_DATE,
264 X_LAST_UPDATED_BY,
265 X_LAST_UPDATE_LOGIN,
266 X_BILL_TO_ADDRESS_ID,
267 X_SHIP_TO_ADDRESS_ID,
268 X_LOC_BILL_TO_ADDRESS_ID,
269 X_LOC_SHIP_TO_ADDRESS_ID,
270 X_AWARD_ORGANIZATION_ID,
271 X_HARD_LIMIT_FLAG ,
272 X_INVOICE_LIMIT_FLAG , /*Bug 6642901 */
273 X_PROPOSAL_ID,
274 X_BUDGET_WF_ENABLED_FLAG
275 );
276
277 open c;
278 fetch c into X_ROWID;
279 if (c%notfound) then
280 close c;
281 raise no_data_found;
282 end if;
283 close c;
284
285 end INSERT_ROW;
286
287 procedure LOCK_ROW (
288 X_AWARD_ID in NUMBER,
289 X_AWARD_NUMBER in VARCHAR2,
290 X_AWARD_SHORT_NAME in VARCHAR2,
291 X_AWARD_FULL_NAME in VARCHAR2,
292 X_FUNDING_SOURCE_ID in NUMBER,
293 X_START_DATE_ACTIVE in DATE,
294 X_END_DATE_ACTIVE in DATE,
295 X_CLOSE_DATE in DATE,
296 X_FUNDING_SOURCE_AWARD_NUMBER in VARCHAR2,
297 X_AWARD_PURPOSE_CODE in VARCHAR2,
298 X_STATUS in VARCHAR2,
299 X_ALLOWABLE_SCHEDULE_ID in NUMBER,
300 X_IDC_SCHEDULE_ID in NUMBER,
301 X_REVENUE_DISTRIBUTION_RULE in VARCHAR2,
302 X_BILLING_FREQUENCY in VARCHAR2,
303 X_BILLING_CYCLE_ID IN NUMBER,
304 X_BILLING_OFFSET IN NUMBER,
305 X_BILLING_DISTRIBUTION_RULE in VARCHAR2,
306 X_BILLING_FORMAT in VARCHAR2,
307 X_BILLING_TERM in NUMBER,
308 X_AWARD_PROJECT_ID in NUMBER,
309 X_AGREEMENT_ID in NUMBER,
310 X_AWARD_TEMPLATE_FLAG in VARCHAR2,
311 X_PREAWARD_DATE in DATE,
312 X_AGENCY_SPECIFIC_FORM in VARCHAR2,
313 X_BILL_TO_CUSTOMER_ID in NUMBER,
314 X_TRANSACTION_NUMBER in VARCHAR2,
315 X_AMOUNT_TYPE in VARCHAR2,
316 X_BOUNDARY_CODE in VARCHAR2,
317 X_FUND_CONTROL_LEVEL_AWARD in VARCHAR2,
318 X_FUND_CONTROL_LEVEL_TASK in VARCHAR2,
319 X_FUND_CONTROL_LEVEL_RES_GRP in VARCHAR2,
320 X_FUND_CONTROL_LEVEL_RES in VARCHAR2,
321 X_ATTRIBUTE_CATEGORY in VARCHAR2,
322 X_ATTRIBUTE1 in VARCHAR2,
323 X_ATTRIBUTE2 in VARCHAR2,
324 X_ATTRIBUTE3 in VARCHAR2,
325 X_ATTRIBUTE4 in VARCHAR2,
326 X_ATTRIBUTE5 in VARCHAR2,
327 X_ATTRIBUTE6 in VARCHAR2,
328 X_ATTRIBUTE7 in VARCHAR2,
329 X_ATTRIBUTE8 in VARCHAR2,
330 X_ATTRIBUTE9 in VARCHAR2,
331 X_ATTRIBUTE10 in VARCHAR2,
332 X_ATTRIBUTE11 in VARCHAR2,
333 X_ATTRIBUTE12 in VARCHAR2,
334 X_ATTRIBUTE13 in VARCHAR2,
335 X_ATTRIBUTE14 in VARCHAR2,
336 X_ATTRIBUTE15 in VARCHAR2,
337 X_ATTRIBUTE16 in VARCHAR2,
338 X_ATTRIBUTE17 in VARCHAR2,
339 X_ATTRIBUTE18 in VARCHAR2,
340 X_ATTRIBUTE19 in VARCHAR2,
341 X_ATTRIBUTE20 in VARCHAR2,
342 X_ATTRIBUTE21 in VARCHAR2,
343 X_ATTRIBUTE22 in VARCHAR2,
344 X_ATTRIBUTE23 in VARCHAR2,
345 X_ATTRIBUTE24 in VARCHAR2,
346 X_ATTRIBUTE25 in VARCHAR2,
347 X_TEMPLATE_START_DATE_ACTIVE in DATE,
348 X_TEMPLATE_END_DATE_ACTIVE in DATE,
349 X_ORG_ID in NUMBER,
350 X_TYPE in VARCHAR2,
351 X_COST_IND_SCH_FIXED_DATE in DATE,
352 X_LABOR_INVOICE_FORMAT_ID in NUMBER,
353 X_NON_LABOR_INVOICE_FORMAT_ID in NUMBER,
354 X_BILL_TO_ADDRESS_ID in NUMBER,
355 X_SHIP_TO_ADDRESS_ID in NUMBER,
356 X_LOC_BILL_TO_ADDRESS_ID in NUMBER,
357 X_LOC_SHIP_TO_ADDRESS_ID in NUMBER,
358 X_AWARD_ORGANIZATION_ID in NUMBER,
359 X_HARD_LIMIT_FLAG in VARCHAR2,
360 X_INVOICE_LIMIT_FLAG in VARCHAR2 /*Bug 6642901 */
361 ) is
362 cursor c1 is select
363 AWARD_NUMBER,
364 AWARD_SHORT_NAME,
365 AWARD_FULL_NAME,
366 FUNDING_SOURCE_ID,
367 START_DATE_ACTIVE,
368 END_DATE_ACTIVE,
369 CLOSE_DATE,
370 FUNDING_SOURCE_AWARD_NUMBER,
371 AWARD_PURPOSE_CODE,
372 STATUS,
373 ALLOWABLE_SCHEDULE_ID,
374 IDC_SCHEDULE_ID,
375 REVENUE_DISTRIBUTION_RULE,
376 BILLING_FREQUENCY,
377 BILLING_CYCLE_ID,
378 BILLING_OFFSET,
379 BILLING_DISTRIBUTION_RULE,
380 BILLING_FORMAT,
381 BILLING_TERM,
382 AWARD_PROJECT_ID,
383 AGREEMENT_ID,
384 AWARD_TEMPLATE_FLAG,
385 PREAWARD_DATE,
386 AGENCY_SPECIFIC_FORM,
387 BILL_TO_CUSTOMER_ID,
388 TRANSACTION_NUMBER,
389 AMOUNT_TYPE,
390 BOUNDARY_CODE,
391 FUND_CONTROL_LEVEL_AWARD,
392 FUND_CONTROL_LEVEL_TASK,
393 FUND_CONTROL_LEVEL_RES_GRP,
394 FUND_CONTROL_LEVEL_RES,
395 ATTRIBUTE_CATEGORY,
396 ATTRIBUTE1,
397 ATTRIBUTE2,
398 ATTRIBUTE3,
399 ATTRIBUTE4,
400 ATTRIBUTE5,
401 ATTRIBUTE6,
402 ATTRIBUTE7,
403 ATTRIBUTE8,
404 ATTRIBUTE9,
405 ATTRIBUTE10,
406 ATTRIBUTE11,
407 ATTRIBUTE12,
408 ATTRIBUTE13,
409 ATTRIBUTE14,
410 ATTRIBUTE15,
411 ATTRIBUTE16,
412 ATTRIBUTE17,
413 ATTRIBUTE18,
414 ATTRIBUTE19,
415 ATTRIBUTE20,
416 ATTRIBUTE21,
417 ATTRIBUTE22,
418 ATTRIBUTE23,
419 ATTRIBUTE24,
420 ATTRIBUTE25,
421 TEMPLATE_START_DATE_ACTIVE,
422 TEMPLATE_END_DATE_ACTIVE,
423 ORG_ID,
424 TYPE,
425 COST_IND_SCH_FIXED_DATE,
426 LABOR_INVOICE_FORMAT_ID,
427 NON_LABOR_INVOICE_FORMAT_ID,
428 BILL_TO_ADDRESS_ID,
429 SHIP_TO_ADDRESS_ID,
430 lOC_BILL_TO_ADDRESS_ID,
431 LOC_SHIP_TO_ADDRESS_ID,
432 AWARD_ORGANIZATION_ID,
433 HARD_LIMIT_FLAG,
434 INVOICE_LIMIT_FLAG /*Bug6642901 */
435 from GMS_AWARDS
436 where AWARD_ID = X_AWARD_ID
437 for update of AWARD_ID nowait;
438 tlinfo c1%rowtype;
439
440 begin
441 open c1;
442 fetch c1 into tlinfo;
443 if (c1%notfound) then
444 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
445 app_exception.raise_exception;
446 close c1;
447 return;
448 end if;
449 close c1;
450
451 if ( (tlinfo.AWARD_NUMBER = X_AWARD_NUMBER)
452 AND (tlinfo.AWARD_SHORT_NAME = X_AWARD_SHORT_NAME)
453 AND ((tlinfo.AWARD_FULL_NAME = X_AWARD_FULL_NAME)
454 OR ((tlinfo.AWARD_FULL_NAME is null)
455 AND (X_AWARD_FULL_NAME is null)))
456 AND ((tlinfo.FUNDING_SOURCE_ID = X_FUNDING_SOURCE_ID)
457 OR ((tlinfo.FUNDING_SOURCE_ID is null)
458 AND (X_FUNDING_SOURCE_ID is null)))
459 AND ((tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
460 OR ((tlinfo.START_DATE_ACTIVE is null)
461 AND (X_START_DATE_ACTIVE is null)))
462 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
463 OR ((tlinfo.END_DATE_ACTIVE is null)
464 AND (X_END_DATE_ACTIVE is null)))
465 AND ((tlinfo.CLOSE_DATE = X_CLOSE_DATE)
466 OR ((tlinfo.CLOSE_DATE is null)
467 AND (X_CLOSE_DATE is null)))
468 AND ((tlinfo.FUNDING_SOURCE_AWARD_NUMBER = X_FUNDING_SOURCE_AWARD_NUMBER)
469 OR ((tlinfo.FUNDING_SOURCE_AWARD_NUMBER is null)
470 AND (X_FUNDING_SOURCE_AWARD_NUMBER is null)))
471 AND ((tlinfo.AWARD_PURPOSE_CODE = X_AWARD_PURPOSE_CODE)
472 OR ((tlinfo.AWARD_PURPOSE_CODE is null)
473 AND (X_AWARD_PURPOSE_CODE is null)))
474 AND ((tlinfo.STATUS = X_STATUS)
475 OR ((tlinfo.STATUS is null)
476 AND (X_STATUS is null)))
477 AND ((tlinfo.ALLOWABLE_SCHEDULE_ID = X_ALLOWABLE_SCHEDULE_ID)
478 OR ((tlinfo.ALLOWABLE_SCHEDULE_ID is null)
479 AND (X_ALLOWABLE_SCHEDULE_ID is null)))
480 AND ((tlinfo.IDC_SCHEDULE_ID = X_IDC_SCHEDULE_ID)
481 OR ((tlinfo.IDC_SCHEDULE_ID is null)
482 AND (X_IDC_SCHEDULE_ID is null)))
483 AND ((tlinfo.REVENUE_DISTRIBUTION_RULE = X_REVENUE_DISTRIBUTION_RULE)
484 OR ((tlinfo.REVENUE_DISTRIBUTION_RULE is null)
485 AND (X_REVENUE_DISTRIBUTION_RULE is null)))
486 -- AND ((tlinfo.BILLING_FREQUENCY = X_BILLING_FREQUENCY)
487 -- OR ((tlinfo.BILLING_FREQUENCY is null)
488 -- AND (X_BILLING_FREQUENCY is null)))
489 AND ((tlinfo.BILLING_CYCLE_ID = X_BILLING_CYCLE_ID)
490 OR ((tlinfo.BILLING_CYCLE_ID is null)
491 AND (X_BILLING_CYCLE_ID is null)))
492 AND ((tlinfo.BILLING_OFFSET = X_BILLING_OFFSET)
496 OR ((tlinfo.BILLING_DISTRIBUTION_RULE is null)
493 OR ((tlinfo.BILLING_OFFSET is null)
494 AND (X_BILLING_OFFSET is null)))
495 AND ((tlinfo.BILLING_DISTRIBUTION_RULE = X_BILLING_DISTRIBUTION_RULE)
497 AND (X_BILLING_DISTRIBUTION_RULE is null)))
498 AND ((tlinfo.BILLING_FORMAT = X_BILLING_FORMAT)
499 OR ((tlinfo.BILLING_FORMAT is null)
500 AND (X_BILLING_FORMAT is null)))
501 AND ((tlinfo.BILLING_TERM = X_BILLING_TERM)
502 OR ((tlinfo.BILLING_TERM is null)
503 AND (X_BILLING_TERM is null)))
504 AND ((tlinfo.AWARD_PROJECT_ID = X_AWARD_PROJECT_ID)
505 OR ((tlinfo.AWARD_PROJECT_ID is null)
506 AND (X_AWARD_PROJECT_ID is null)))
507 AND ((tlinfo.AGREEMENT_ID = X_AGREEMENT_ID)
508 OR ((tlinfo.AGREEMENT_ID is null)
509 AND (X_AGREEMENT_ID is null)))
510 AND ((tlinfo.AWARD_TEMPLATE_FLAG = X_AWARD_TEMPLATE_FLAG)
511 OR ((tlinfo.AWARD_TEMPLATE_FLAG is null)
512 AND (X_AWARD_TEMPLATE_FLAG is null)))
513 AND ((tlinfo.PREAWARD_DATE = X_PREAWARD_DATE)
514 OR ((tlinfo.PREAWARD_DATE is null)
515 AND (X_PREAWARD_DATE is null)))
516 AND ((tlinfo.AGENCY_SPECIFIC_FORM = X_AGENCY_SPECIFIC_FORM)
517 OR ((tlinfo.AGENCY_SPECIFIC_FORM is null)
518 AND (X_AGENCY_SPECIFIC_FORM is null)))
519 AND ((tlinfo.BILL_TO_CUSTOMER_ID = X_BILL_TO_CUSTOMER_ID)
520 OR ((tlinfo.BILL_TO_CUSTOMER_ID is null)
521 AND (X_BILL_TO_CUSTOMER_ID is null)))
522 AND ((tlinfo.TRANSACTION_NUMBER = X_TRANSACTION_NUMBER)
523 OR ((tlinfo.TRANSACTION_NUMBER is null)
524 AND (X_TRANSACTION_NUMBER is null)))
525 AND ((tlinfo.AMOUNT_TYPE = X_AMOUNT_TYPE)
526 OR ((tlinfo.AMOUNT_TYPE is null)
527 AND (X_AMOUNT_TYPE is null)))
528 AND ((tlinfo.BOUNDARY_CODE = X_BOUNDARY_CODE)
529 OR ((tlinfo.BOUNDARY_CODE is null)
530 AND (X_BOUNDARY_CODE is null)))
531 AND ((tlinfo.FUND_CONTROL_LEVEL_AWARD = X_FUND_CONTROL_LEVEL_AWARD)
532 OR ((tlinfo.FUND_CONTROL_LEVEL_AWARD is null)
533 AND (X_FUND_CONTROL_LEVEL_AWARD is null)))
534 AND ((tlinfo.FUND_CONTROL_LEVEL_TASK = X_FUND_CONTROL_LEVEL_TASK)
535 OR ((tlinfo.FUND_CONTROL_LEVEL_TASK is null)
536 AND (X_FUND_CONTROL_LEVEL_TASK is null)))
537 AND ((tlinfo.FUND_CONTROL_LEVEL_RES_GRP = X_FUND_CONTROL_LEVEL_RES_GRP)
538 OR ((tlinfo.FUND_CONTROL_LEVEL_RES_GRP is null)
539 AND (X_FUND_CONTROL_LEVEL_RES_GRP is null)))
540 AND ((tlinfo.FUND_CONTROL_LEVEL_RES = X_FUND_CONTROL_LEVEL_RES)
541 OR ((tlinfo.FUND_CONTROL_LEVEL_RES is null)
542 AND (X_FUND_CONTROL_LEVEL_RES is null)))
543 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
544 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
545 AND (X_ATTRIBUTE_CATEGORY is null)))
546 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
547 OR ((tlinfo.ATTRIBUTE1 is null)
548 AND (X_ATTRIBUTE1 is null)))
549 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
550 OR ((tlinfo.ATTRIBUTE2 is null)
551 AND (X_ATTRIBUTE2 is null)))
552 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
553 OR ((tlinfo.ATTRIBUTE3 is null)
554 AND (X_ATTRIBUTE3 is null)))
555 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
556 OR ((tlinfo.ATTRIBUTE4 is null)
557 AND (X_ATTRIBUTE4 is null)))
558 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
559 OR ((tlinfo.ATTRIBUTE5 is null)
560 AND (X_ATTRIBUTE5 is null)))
561 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
562 OR ((tlinfo.ATTRIBUTE6 is null)
563 AND (X_ATTRIBUTE6 is null)))
564 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
565 OR ((tlinfo.ATTRIBUTE7 is null)
566 AND (X_ATTRIBUTE7 is null)))
567 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
568 OR ((tlinfo.ATTRIBUTE8 is null)
569 AND (X_ATTRIBUTE8 is null)))
570 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
571 OR ((tlinfo.ATTRIBUTE9 is null)
572 AND (X_ATTRIBUTE9 is null)))
573 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
574 OR ((tlinfo.ATTRIBUTE10 is null)
575 AND (X_ATTRIBUTE10 is null)))
576 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
577 OR ((tlinfo.ATTRIBUTE11 is null)
578 AND (X_ATTRIBUTE11 is null)))
579 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
580 OR ((tlinfo.ATTRIBUTE12 is null)
581 AND (X_ATTRIBUTE12 is null)))
582 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
583 OR ((tlinfo.ATTRIBUTE13 is null)
584 AND (X_ATTRIBUTE13 is null)))
585 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
586 OR ((tlinfo.ATTRIBUTE14 is null)
587 AND (X_ATTRIBUTE14 is null)))
588 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
589 OR ((tlinfo.ATTRIBUTE15 is null)
590 AND (X_ATTRIBUTE15 is null)))
591 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
592 OR ((tlinfo.ATTRIBUTE16 is null)
593 AND (X_ATTRIBUTE16 is null)))
594 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
595 OR ((tlinfo.ATTRIBUTE17 is null)
596 AND (X_ATTRIBUTE17 is null)))
597 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
598 OR ((tlinfo.ATTRIBUTE18 is null)
599 AND (X_ATTRIBUTE18 is null)))
600 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
601 OR ((tlinfo.ATTRIBUTE19 is null)
602 AND (X_ATTRIBUTE19 is null)))
603 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
607 OR ((tlinfo.ATTRIBUTE21 is null)
604 OR ((tlinfo.ATTRIBUTE20 is null)
605 AND (X_ATTRIBUTE20 is null)))
606 AND ((tlinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
608 AND (X_ATTRIBUTE21 is null)))
609 AND ((tlinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
610 OR ((tlinfo.ATTRIBUTE22 is null)
611 AND (X_ATTRIBUTE22 is null)))
612 AND ((tlinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
613 OR ((tlinfo.ATTRIBUTE23 is null)
614 AND (X_ATTRIBUTE23 is null)))
615 AND ((tlinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
616 OR ((tlinfo.ATTRIBUTE24 is null)
617 AND (X_ATTRIBUTE24 is null)))
618 AND ((tlinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
619 OR ((tlinfo.ATTRIBUTE25 is null)
620 AND (X_ATTRIBUTE25 is null)))
621 AND ((tlinfo.TEMPLATE_START_DATE_ACTIVE = X_TEMPLATE_START_DATE_ACTIVE)
622 OR ((tlinfo.TEMPLATE_START_DATE_ACTIVE is null)
623 AND (X_TEMPLATE_START_DATE_ACTIVE is null)))
624 AND ((tlinfo.TEMPLATE_END_DATE_ACTIVE = X_TEMPLATE_END_DATE_ACTIVE)
625 OR ((tlinfo.TEMPLATE_END_DATE_ACTIVE is null)
626 AND (X_TEMPLATE_END_DATE_ACTIVE is null)))
627 AND ((tlinfo.AWARD_ORGANIZATION_ID = X_AWARD_ORGANIZATION_ID )
628 OR ((tlinfo.AWARD_ORGANIZATION_ID is null)
629 AND ( X_AWARD_ORGANIZATION_ID is null)))
630 AND ((tlinfo.TYPE = X_TYPE)
631 OR ((tlinfo.TYPE is null)
632 AND (X_TYPE is null)))
633 AND ((tlinfo.COST_IND_SCH_FIXED_DATE = X_COST_IND_SCH_FIXED_DATE)
634 OR ((tlinfo.COST_IND_SCH_FIXED_DATE is null)
635 AND (X_COST_IND_SCH_FIXED_DATE is null)))
636 AND ((tlinfo.LABOR_INVOICE_FORMAT_ID = X_LABOR_INVOICE_FORMAT_ID)
637 OR ((tlinfo.LABOR_INVOICE_FORMAT_ID is null)
638 AND (X_LABOR_INVOICE_FORMAT_ID is null)))
639 AND ((tlinfo.NON_LABOR_INVOICE_FORMAT_ID = X_NON_LABOR_INVOICE_FORMAT_ID)
640 OR ((tlinfo.NON_LABOR_INVOICE_FORMAT_ID is null)
641 AND (X_NON_LABOR_INVOICE_FORMAT_ID is null)))
642 AND ((tlinfo.BILL_TO_ADDRESS_ID = X_BILL_TO_ADDRESS_ID)
643 OR ((tlinfo.BILL_TO_ADDRESS_ID is null)
644 AND (X_BILL_TO_ADDRESS_ID is null)))
645 AND ((tlinfo.SHIP_TO_ADDRESS_ID = X_SHIP_TO_ADDRESS_ID)
646 OR ((tlinfo.SHIP_TO_ADDRESS_ID is null)
647 AND (X_SHIP_TO_ADDRESS_ID is null)))
648 AND ((tlinfo.LOC_BILL_TO_ADDRESS_ID = X_LOC_BILL_TO_ADDRESS_ID)
649 OR ((tlinfo.LOC_BILL_TO_ADDRESS_ID is null)
650 AND (X_LOC_BILL_TO_ADDRESS_ID is null)))
651 AND ((tlinfo.LOC_SHIP_TO_ADDRESS_ID = X_LOC_SHIP_TO_ADDRESS_ID)
652 OR ((tlinfo.LOC_SHIP_TO_ADDRESS_ID is null)
653 AND (X_LOC_SHIP_TO_ADDRESS_ID is null)))
654 AND (tlinfo.ORG_ID = X_ORG_ID)
655 /*
656 AND ((tlinfo.HARD_LIMIT_FLAG = X_HARD_LIMIT_FLAG
657 OR ((tlinfo.HARD_LIMIT_FLAG is null)
658 AND (X_HARD_LIMIT_FLAG is null)))
659 */
660 ) then
661 null;
662 else
663 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
664 app_exception.raise_exception;
665 end if;
666 return;
667 end LOCK_ROW;
668
669 procedure UPDATE_ROW (
670 X_AWARD_ID in NUMBER,
671 X_AWARD_NUMBER in VARCHAR2,
672 X_AWARD_SHORT_NAME in VARCHAR2,
673 X_AWARD_FULL_NAME in VARCHAR2,
674 X_FUNDING_SOURCE_ID in NUMBER,
675 X_START_DATE_ACTIVE in DATE,
676 X_END_DATE_ACTIVE in DATE,
677 X_CLOSE_DATE in DATE,
678 X_FUNDING_SOURCE_AWARD_NUMBER in VARCHAR2,
679 X_AWARD_PURPOSE_CODE in VARCHAR2,
680 X_STATUS in VARCHAR2,
681 X_ALLOWABLE_SCHEDULE_ID in NUMBER,
682 X_IDC_SCHEDULE_ID in NUMBER,
683 X_REVENUE_DISTRIBUTION_RULE in VARCHAR2,
684 X_BILLING_FREQUENCY in VARCHAR2,
685 X_BILLING_CYCLE_ID IN NUMBER,
686 X_BILLING_OFFSET IN NUMBER,
687 X_BILLING_DISTRIBUTION_RULE in VARCHAR2,
688 X_BILLING_FORMAT in VARCHAR2,
689 X_BILLING_TERM in NUMBER,
690 X_AWARD_PROJECT_ID in NUMBER,
691 X_AGREEMENT_ID in NUMBER,
692 X_AWARD_TEMPLATE_FLAG in VARCHAR2,
693 X_PREAWARD_DATE in DATE,
694 X_AGENCY_SPECIFIC_FORM in VARCHAR2,
695 X_BILL_TO_CUSTOMER_ID in NUMBER,
696 X_TRANSACTION_NUMBER in VARCHAR2,
697 X_AMOUNT_TYPE in VARCHAR2,
698 X_BOUNDARY_CODE in VARCHAR2,
699 X_FUND_CONTROL_LEVEL_AWARD in VARCHAR2,
700 X_FUND_CONTROL_LEVEL_TASK in VARCHAR2,
701 X_FUND_CONTROL_LEVEL_RES_GRP in VARCHAR2,
702 X_FUND_CONTROL_LEVEL_RES in VARCHAR2,
703 X_ATTRIBUTE_CATEGORY in VARCHAR2,
704 X_ATTRIBUTE1 in VARCHAR2,
705 X_ATTRIBUTE2 in VARCHAR2,
706 X_ATTRIBUTE3 in VARCHAR2,
707 X_ATTRIBUTE4 in VARCHAR2,
708 X_ATTRIBUTE5 in VARCHAR2,
709 X_ATTRIBUTE6 in VARCHAR2,
710 X_ATTRIBUTE7 in VARCHAR2,
711 X_ATTRIBUTE8 in VARCHAR2,
712 X_ATTRIBUTE9 in VARCHAR2,
713 X_ATTRIBUTE10 in VARCHAR2,
714 X_ATTRIBUTE11 in VARCHAR2,
715 X_ATTRIBUTE12 in VARCHAR2,
716 X_ATTRIBUTE13 in VARCHAR2,
717 X_ATTRIBUTE14 in VARCHAR2,
718 X_ATTRIBUTE15 in VARCHAR2,
719 X_ATTRIBUTE16 in VARCHAR2,
720 X_ATTRIBUTE17 in VARCHAR2,
721 X_ATTRIBUTE18 in VARCHAR2,
722 X_ATTRIBUTE19 in VARCHAR2,
723 X_ATTRIBUTE20 in VARCHAR2,
724 X_ATTRIBUTE21 in VARCHAR2,
725 X_ATTRIBUTE22 in VARCHAR2,
726 X_ATTRIBUTE23 in VARCHAR2,
727 X_ATTRIBUTE24 in VARCHAR2,
728 X_ATTRIBUTE25 in VARCHAR2,
729 X_TEMPLATE_START_DATE_ACTIVE in DATE,
730 X_TEMPLATE_END_DATE_ACTIVE in DATE,
731 -- X_ORG_ID in NUMBER,
732 X_TYPE in VARCHAR2,
733 X_COST_IND_SCH_FIXED_DATE in DATE,
737 X_BILL_TO_ADDRESS_ID in NUMBER,
734 X_LABOR_INVOICE_FORMAT_ID in NUMBER,
735 X_NON_LABOR_INVOICE_FORMAT_ID in NUMBER,
736 X_MODE in VARCHAR2 default 'R',
738 X_SHIP_TO_ADDRESS_ID in NUMBER,
739 X_LOC_BILL_TO_ADDRESS_ID in NUMBER,
740 X_LOC_SHIP_TO_ADDRESS_ID in NUMBER,
741 X_AWARD_ORGANIZATION_ID in NUMBER,
742 X_HARD_LIMIT_FLAG in VARCHAR2 ,
743 X_INVOICE_LIMIT_FLAG in VARCHAR2 , /*Bug 6445688 */
744 X_BUDGET_WF_ENABLED_FLAG in VARCHAR2
745 ) is
746 X_LAST_UPDATE_DATE DATE;
747 X_LAST_UPDATED_BY NUMBER;
748 X_LAST_UPDATE_LOGIN NUMBER;
749 begin
750 X_LAST_UPDATE_DATE := SYSDATE;
751 if(X_MODE = 'I') then
752 X_LAST_UPDATED_BY := 1;
753 X_LAST_UPDATE_LOGIN := 0;
754 elsif (X_MODE = 'R') then
755 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
756 if X_LAST_UPDATED_BY is NULL then
757 X_LAST_UPDATED_BY := -1;
758 end if;
759 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
760 if X_LAST_UPDATE_LOGIN is NULL then
761 X_LAST_UPDATE_LOGIN := -1;
762 end if;
763 else
764 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
765 app_exception.raise_exception;
766 end if;
767 update GMS_AWARDS set
768 AWARD_NUMBER = X_AWARD_NUMBER,
769 AWARD_SHORT_NAME = X_AWARD_SHORT_NAME,
770 AWARD_FULL_NAME = X_AWARD_FULL_NAME,
771 FUNDING_SOURCE_ID = X_FUNDING_SOURCE_ID,
772 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
773 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
774 CLOSE_DATE = X_CLOSE_DATE,
775 FUNDING_SOURCE_AWARD_NUMBER = X_FUNDING_SOURCE_AWARD_NUMBER,
776 AWARD_PURPOSE_CODE = X_AWARD_PURPOSE_CODE,
777 STATUS = X_STATUS,
778 ALLOWABLE_SCHEDULE_ID = X_ALLOWABLE_SCHEDULE_ID,
779 IDC_SCHEDULE_ID = X_IDC_SCHEDULE_ID,
780 REVENUE_DISTRIBUTION_RULE = X_REVENUE_DISTRIBUTION_RULE,
781 BILLING_FREQUENCY = X_BILLING_FREQUENCY,
782 BILLING_CYCLE_ID = X_BILLING_CYCLE_ID,
783 BILLING_OFFSET = X_BILLING_OFFSET,
784 BILLING_DISTRIBUTION_RULE = X_BILLING_DISTRIBUTION_RULE,
785 BILLING_FORMAT = X_BILLING_FORMAT,
786 BILLING_TERM = X_BILLING_TERM,
787 AWARD_PROJECT_ID = X_AWARD_PROJECT_ID,
788 AGREEMENT_ID = X_AGREEMENT_ID,
789 AWARD_TEMPLATE_FLAG = X_AWARD_TEMPLATE_FLAG,
790 PREAWARD_DATE = X_PREAWARD_DATE,
791 AGENCY_SPECIFIC_FORM = X_AGENCY_SPECIFIC_FORM,
792 BILL_TO_CUSTOMER_ID = X_BILL_TO_CUSTOMER_ID,
793 TRANSACTION_NUMBER = X_TRANSACTION_NUMBER,
794 AMOUNT_TYPE = X_AMOUNT_TYPE,
795 BOUNDARY_CODE = X_BOUNDARY_CODE,
796 FUND_CONTROL_LEVEL_AWARD = X_FUND_CONTROL_LEVEL_AWARD,
797 FUND_CONTROL_LEVEL_TASK = X_FUND_CONTROL_LEVEL_TASK,
798 FUND_CONTROL_LEVEL_RES_GRP = X_FUND_CONTROL_LEVEL_RES_GRP,
799 FUND_CONTROL_LEVEL_RES = X_FUND_CONTROL_LEVEL_RES,
800 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
801 ATTRIBUTE1 = X_ATTRIBUTE1,
802 ATTRIBUTE2 = X_ATTRIBUTE2,
803 ATTRIBUTE3 = X_ATTRIBUTE3,
804 ATTRIBUTE4 = X_ATTRIBUTE4,
805 ATTRIBUTE5 = X_ATTRIBUTE5,
806 ATTRIBUTE6 = X_ATTRIBUTE6,
807 ATTRIBUTE7 = X_ATTRIBUTE7,
808 ATTRIBUTE8 = X_ATTRIBUTE8,
809 ATTRIBUTE9 = X_ATTRIBUTE9,
810 ATTRIBUTE10 = X_ATTRIBUTE10,
811 ATTRIBUTE11 = X_ATTRIBUTE11,
812 ATTRIBUTE12 = X_ATTRIBUTE12,
813 ATTRIBUTE13 = X_ATTRIBUTE13,
814 ATTRIBUTE14 = X_ATTRIBUTE14,
815 ATTRIBUTE15 = X_ATTRIBUTE15,
816 ATTRIBUTE16 = X_ATTRIBUTE16,
817 ATTRIBUTE17 = X_ATTRIBUTE17,
818 ATTRIBUTE18 = X_ATTRIBUTE18,
819 ATTRIBUTE19 = X_ATTRIBUTE19,
820 ATTRIBUTE20 = X_ATTRIBUTE20,
821 ATTRIBUTE21 = X_ATTRIBUTE21,
822 ATTRIBUTE22 = X_ATTRIBUTE22,
823 ATTRIBUTE23 = X_ATTRIBUTE23,
824 ATTRIBUTE24 = X_ATTRIBUTE24,
825 ATTRIBUTE25 = X_ATTRIBUTE25,
826 TEMPLATE_START_DATE_ACTIVE = X_TEMPLATE_START_DATE_ACTIVE,
827 TEMPLATE_END_DATE_ACTIVE = X_TEMPLATE_END_DATE_ACTIVE,
828 -- ORG_ID = X_ORG_ID,
829 TYPE = X_TYPE,
830 COST_IND_SCH_FIXED_DATE = X_COST_IND_SCH_FIXED_DATE,
831 LABOR_INVOICE_FORMAT_ID = X_LABOR_INVOICE_FORMAT_ID,
832 NON_LABOR_INVOICE_FORMAT_ID = X_NON_LABOR_INVOICE_FORMAT_ID,
833 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
834 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
835 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
836 BILL_TO_ADDRESS_ID = X_BILL_TO_ADDRESS_ID,
837 SHIP_TO_ADDRESS_ID = X_SHIP_TO_ADDRESS_ID,
838 LOC_BILL_TO_ADDRESS_ID = X_LOC_BILL_TO_ADDRESS_ID,
839 LOC_SHIP_TO_ADDRESS_ID = X_LOC_SHIP_TO_ADDRESS_ID,
840 AWARD_ORGANIZATION_ID = X_AWARD_ORGANIZATION_ID,
841 HARD_LIMIT_FLAG = X_HARD_LIMIT_FLAG ,
842 INVOICE_LIMIT_FLAG = X_INVOICE_LIMIT_FLAG , /*Bug 6445688 */
843 BUDGET_WF_ENABLED_FLAG = X_BUDGET_WF_ENABLED_FLAG
844 where AWARD_ID = X_AWARD_ID
845 ;
846 if (sql%notfound) then
847 raise no_data_found;
848 end if;
849 end UPDATE_ROW;
850
851 procedure DELETE_ROW (
852 X_AWARD_ID IN NUMBER,
853 x_award_project_id IN NUMBER,
854 x_agreement_id IN NUMBER,
855 x_Award_Template_flag IN VARCHAR2,
856 x_msg_count OUT NOCOPY NUMBER,
857 RETCODE OUT NOCOPY VARCHAR2,
858 ERRBUFF OUT NOCOPY VARCHAR2
859 ) is
860 begin
861 -- Added call to below API to fix bug 2355648
862 GMS_AWARD_DELETE_PKG.DELETE_AWARD_DETAIL(
863 p_award_id =>x_award_id ,
864 p_award_project_id =>x_award_project_id ,
865 p_agreement_id =>x_agreement_id ,
866 p_Award_Template_flag =>x_Award_Template_flag,
867 p_msg_count =>x_msg_count,
868 RETCODE =>RETCODE,
869 ERRBUFF =>ERRBUFF);
870
871
872 IF RETCODE <>'S' THEN
873 RETURN;
874 END IF;
875
876 delete from GMS_AWARDS
877 where AWARD_ID = X_AWARD_ID;
878 if (sql%notfound) then
879 raise no_data_found;
880 end if;
881 Exception
882
883 WHEN OTHERS THEN
884 --'S' for success, 'E' form exception for , and 'U' for Undefine Exception
885 retcode :='U';
886 errbuff := 'Error :'||substr(sqlerrm,1,200);
887 FND_MESSAGE.SET_NAME('GMS','GMS_UNEXPECTED_ERROR');
888 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_AWARDS_PKG: DELETE_ROW');
889 FND_MESSAGE.SET_TOKEN('OERRNO',SQLCODE);
890 FND_MESSAGE.SET_TOKEN('OERRM',SQLERRM);
891 FND_MSG_PUB.add;
892 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
893 p_data => errbuff );
894 end DELETE_ROW;
895
896 /* Added for Bug 2107671 - whenever Award Status is changed to "CLOSED" or "AT RISK"
897 Project Status Code corresponding to award_project should also be changed to
898 "CLOSED". This will ensure that these Awards are no considered during billing/revenue generation
899 process. For other Award Statuses award_project status will be "APPROVED"
900 */
901
902 Procedure UPDATE_AWARD_PROJECT_STATUS(
903 X_award_project_id IN NUMBER,
904 X_Status IN VARCHAR2
905 ) is
906 begin
907 update pa_projects
908 set project_status_code = nvl(X_Status,'CLOSED')
909 where project_id = X_award_project_id;
910 end UPDATE_AWARD_PROJECT_STATUS;
911
912 end GMS_AWARDS_PKG;