[Home] [Help]
PACKAGE BODY: APPS.GMS_TRANSACTIONS_PUB
Source
1 PACKAGE BODY GMS_TRANSACTIONS_PUB AS
2 -- $Header: gmstpubb.pls 120.10 2011/11/24 06:09:51 speddi ship $
3
4 -- --------------------------------------------------------
5 -- Declare global variables.
6 -- -------------------------------------------------------
7
8 g_award_id number;
9 g_award_distribution_option varchar2(1);
10
11 INVALID_DATA EXCEPTION;
12
13 PROCEDURE set_award IS
14 BEGIN
15 select default_dist_award_id,award_distribution_option
16 into g_award_id,g_award_distribution_option
17 from gms_implementations;
18 EXCEPTION
19 WHEN OTHERS THEN
20 null ;
21 END set_award;
22
23
24 -- --------------------------------------
25 -- Sponsored projects need to have award
26 -- entered.
27 -- -------------------------------------
28 Function AWARD_REQUIRED(P_Task_Id IN NUMBER,
29 X_Outcome OUT NOCOPY VARCHAR2)
30 RETURN BOOLEAN IS
31 Sponsor_Flag VARCHAR2(150);
32 Begin
33
34 --Bug 9090618 added org_id join between p and gpt
35 Select nvl(gpt.sponsored_flag,'N')
36 into Sponsor_Flag
37 from pa_tasks t,
38 pa_projects_all p,
39 --gms_project_types gpt /*Commented for bug#12822370 */
40 pa_project_types_all gpt /*Added for bug#12822370 */
41 where t.task_id = P_Task_Id and
42 p.project_id = t.project_id and
43 gpt.project_type = p.project_type and
44 gpt.org_id = p.org_id;
45
46 If Sponsor_Flag = 'Y' then
47 RETURN TRUE;
48 Else
49 RETURN FALSE;
50 End If;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 -- X_Outcome := to_char(SQLCODE);
55 If P_Task_Id IS NOT NULL THEN
56 X_Outcome := 'GMS_INV_ITEM_TASK';
57 End If;
58 raise ;
59
60 --RETURN FALSE;
61
62 End AWARD_REQUIRED;
63 -- ============= END of AWARD_REQUIRED ===================
64
65 -- -------------------------------------------------------------
66 -- Common Table handler for Table GMS_transaction Interface all
67 -- -------------------------------------------------------------
68
69 PROCEDURE LOAD_GMS_XFACE_API ( p_rec gms_transaction_interface_all%ROWTYPE,
70 p_outcome OUT NOCOPY varchar2 ) is
71 x_rec gms_transaction_interface_all%ROWTYPE ;
72 p_err_code NUMBER ;
73 p_err_buf varchar2(2000) ;
74
75 BEGIN
76
77 x_rec := p_rec ;
78
79 IF x_rec.created_by is NULL THEN
80 x_rec.created_by := nvl(fnd_global.user_id,0) ;
81 END IF ;
82
83 IF x_rec.last_updated_by is NULL THEN
84 x_rec.last_updated_by := nvl(fnd_global.user_id,0) ;
85 END IF ;
86
87 IF x_rec.creation_date is NULL THEN
88 x_rec.creation_date := sysdate ;
89 END IF ;
90
91 IF x_rec.last_update_date is NULL THEN
92 x_rec.last_update_date := sysdate ;
93 END IF ;
94
95 -- Bug 3465939 :Modified code to insert original_encumbrance_item_id
96 -- passed by Oracle Labor distribution system/External system.
97
98 insert into gms_transaction_interface_all (
99 TXN_INTERFACE_ID,
100 BATCH_NAME,
101 TRANSACTION_SOURCE,
102 EXPENDITURE_ENDING_DATE,
103 EXPENDITURE_ITEM_DATE,
104 PROJECT_NUMBER,
105 TASK_NUMBER,
106 AWARD_ID,
107 EXPENDITURE_TYPE,
108 TRANSACTION_STATUS_CODE,
109 ORIG_TRANSACTION_REFERENCE,
110 ORG_ID,
111 SYSTEM_LINKAGE,
112 USER_TRANSACTION_SOURCE,
113 TRANSACTION_TYPE,
114 BURDENABLE_RAW_COST,
115 FUNDING_PATTERN_ID,
116 CREATED_BY,
117 CREATION_DATE,
118 LAST_UPDATED_BY,
119 LAST_UPDATE_DATE,
120 AWARD_NUMBER ,-- Fix for bug : 2439320
121 ORIGINAL_ENCUMBRANCE_ITEM_ID -- Bug 3465936
122 ) Values
123 (
124 x_rec.TXN_INTERFACE_ID,
125 x_rec.BATCH_NAME,
126 x_rec.TRANSACTION_SOURCE,
127 x_rec.EXPENDITURE_ENDING_DATE,
128 x_rec.EXPENDITURE_ITEM_DATE,
129 x_rec.PROJECT_NUMBER,
130 x_rec.TASK_NUMBER,
131 x_rec.AWARD_ID,
132 x_rec.EXPENDITURE_TYPE,
133 x_rec.TRANSACTION_STATUS_CODE,
134 x_rec.ORIG_TRANSACTION_REFERENCE,
135 x_rec.ORG_ID,
136 x_rec.SYSTEM_LINKAGE,
137 x_rec.USER_TRANSACTION_SOURCE,
138 x_rec.TRANSACTION_TYPE,
139 x_rec.BURDENABLE_RAW_COST,
140 x_rec.FUNDING_PATTERN_ID,
141 x_rec.CREATED_BY,
142 x_rec.CREATION_DATE,
143 x_rec.LAST_UPDATED_BY,
144 x_rec.LAST_UPDATE_DATE,
145 x_rec.AWARD_NUMBER ,-- Fix for bug : 2439320
146 x_rec.ORIGINAL_ENCUMBRANCE_ITEM_ID -- Bug 3465936
147 ) ;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
152 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_TRANSACTIONS_PUB : LOAD_GMS_XFACE_API',
153 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
154 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
155 x_err_code => p_err_code, x_err_buff => p_err_buf
156 ) ;
157 p_outcome := 'GMS_UNEXPECTED_ERROR';
158 END LOAD_GMS_XFACE_API ;
159
160 PROCEDURE UPDATE_GMS_XFACE_API ( p_rec gms_transaction_interface_all%ROWTYPE
161 , p_outcome OUT NOCOPY varchar2 ) is
162 x_rec gms_transaction_interface_all%ROWTYPE ;
163 p_err_code NUMBER ;
164 p_err_buf varchar2(2000) ;
165 BEGIN
166 x_rec := p_rec ;
167
168 IF x_rec.created_by is NULL THEN
169 x_rec.created_by := nvl(fnd_global.user_id,0) ;
170 END IF ;
171
172 IF x_rec.last_updated_by is NULL THEN
173 x_rec.last_updated_by := nvl(fnd_global.user_id,0) ;
174 END IF ;
175
176 IF x_rec.creation_date is NULL THEN
177 x_rec.creation_date := sysdate ;
178 END IF ;
179
180 IF x_rec.last_update_date is NULL THEN
181 x_rec.last_update_date := sysdate ;
182 END IF ;
183
184 -- Bug 3465939 :Modified code to update original_encumbrance_item_id
185 -- passed by Oracle Labor distribution system/External system.
186
187 UPDATE gms_transaction_interface_all
188 SET BATCH_NAME = x_rec.BATCH_NAME,
189 TRANSACTION_SOURCE = x_rec.TRANSACTION_SOURCE,
190 EXPENDITURE_ENDING_DATE = x_rec.EXPENDITURE_ENDING_DATE,
191 EXPENDITURE_ITEM_DATE = x_rec.EXPENDITURE_ITEM_DATE,
192 PROJECT_NUMBER = x_rec.PROJECT_NUMBER,
193 TASK_NUMBER = x_rec.TASK_NUMBER,
194 AWARD_ID = x_rec.AWARD_ID,
195 EXPENDITURE_TYPE = x_rec.EXPENDITURE_TYPE,
196 TRANSACTION_STATUS_CODE = x_rec.TRANSACTION_STATUS_CODE,
197 ORIG_TRANSACTION_REFERENCE = x_rec.ORIG_TRANSACTION_REFERENCE,
198 ORG_ID = x_rec.ORG_ID,
199 SYSTEM_LINKAGE = x_rec.SYSTEM_LINKAGE,
200 USER_TRANSACTION_SOURCE = x_rec.USER_TRANSACTION_SOURCE,
201 TRANSACTION_TYPE = x_rec.TRANSACTION_TYPE,
202 BURDENABLE_RAW_COST = x_rec.BURDENABLE_RAW_COST,
203 FUNDING_PATTERN_ID = x_rec.FUNDING_PATTERN_ID,
204 CREATED_BY = x_rec.CREATED_BY,
205 CREATION_DATE = x_rec.CREATION_DATE,
206 LAST_UPDATED_BY = x_rec.LAST_UPDATED_BY,
207 LAST_UPDATE_DATE = x_rec.LAST_UPDATE_DATE,
208 AWARD_NUMBER = x_rec.AWARD_NUMBER ,-- Fix for bug : 2439320
209 ORIGINAL_ENCUMBRANCE_ITEM_ID = x_rec.ORIGINAL_ENCUMBRANCE_ITEM_ID -- Bug 3465936
210 WHERE TXN_INTERFACE_ID = x_rec.TXN_INTERFACE_ID ;
211
212 EXCEPTION
213 When others then
214 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
215 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_TRANSACTIONS_PUB : UPDATE_GMS_XFACE_API',
216 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
217 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
218 x_err_code => p_err_code, x_err_buff => p_err_buf
219 ) ;
220 p_outcome := 'GMS_UNEXPECTED_ERROR';
221 END UPDATE_GMS_XFACE_API ;
222
223
224 PROCEDURE DELETE_GMS_XFACE_API ( p_rec gms_transaction_interface_all%ROWTYPE
225 , p_outcome OUT NOCOPY varchar2 ) is
226 x_rec gms_transaction_interface_all%ROWTYPE ;
227 p_err_code NUMBER ;
228 p_err_buf varchar2(2000) ;
229 BEGIN
230 x_rec := p_rec ;
231
232 delete from gms_transaction_interface_all
233 WHERE TXN_INTERFACE_ID = x_rec.TXN_INTERFACE_ID ;
234
235 EXCEPTION
236 When others then
237 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
238 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_TRANSACTIONS_PUB : DELETE_GMS_XFACE_API',
239 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
240 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
241 x_err_code => p_err_code, x_err_buff => p_err_buf
242 ) ;
243 p_outcome := 'GMS_UNEXPECTED_ERROR';
244 END DELETE_GMS_XFACE_API ;
245
246 -- -------------------------------------------------------------------
247 -- Validations for award distributions for default award -999
248 -- -------------------------------------------------------------------
249 PROCEDURE
250 DIST_AWARD_VALIDATIONS (P_project_id IN NUMBER
251 , P_task_id IN NUMBER
252 , P_award_id IN NUMBER
253 , P_expenditure_type IN VARCHAR2
254 , P_expenditure_item_date IN DATE
255 , x_err_code IN OUT NOCOPY NUMBER
256 , x_err_buff IN OUT NOCOPY VARCHAR2
257 , p_outcome OUT NOCOPY VARCHAR2) IS
258
259
260 -- =================================================================================
261 -- BUG: 3358176 Award Distribution not recognized for sub tasks when funding
262 -- pattern is defined at top task level.
263 -- top_task_id and pa_tasks join was added.
264 -- =================================================================================
265 CURSOR FUND_PATTERN_EXIST IS
266 select fp.funding_name,
267 fp.funding_pattern_id
268 from gms_funding_patterns_all fp,
269 pa_tasks t
270 where nvl(fp.retroactive_flag, 'N') = 'N'
271 and NVL(fp.status, 'N') = 'A'
272 and fp.project_id = p_project_id
273 and t.task_id = p_task_id
274 and fp.task_id = t.top_task_id
275 and P_expenditure_item_date between fp.start_date and NVL(fp.end_date, P_expenditure_item_date )
276 union
277 select gfpa.funding_name,
278 gfpa.funding_pattern_id
279 from gms_funding_patterns_all gfpa
280 where nvl(gfpa.retroactive_flag, 'N') = 'N'
281 and NVL(gfpa.status, 'N') = 'A'
282 and gfpa.project_id = p_project_id
283 and gfpa.task_id is null
284 and not exists (select '1' from gms_funding_patterns_all b, pa_tasks t
285 where gfpa.project_id = b.project_id
286 and nvl(b.status,'x') = 'A'
287 and t.task_id = p_task_id
288 and b.task_id = t.top_task_id)
289 and P_expenditure_item_date between start_date and NVL(end_date, P_expenditure_item_date )
290 order by 1;
291
292
293 x_funding_pattern_id NUMBER;
294 x_funding_name VARCHAR2(100);
295
296
297 CURSOR GET_FP_AWARDS(x_funding_pattern_id number) IS
298 Select a.Allowable_Schedule_Id,
299 nvl(a.Preaward_Date,a.START_DATE_ACTIVE) awd_Date,
300 a.End_Date_Active,
301 a.Close_Date,
302 a.Status
303 from GMS_AWARDS a,
304 gms_fp_distributions b
305 where a.award_id = b.award_id
306 and b.funding_pattern_id = x_funding_pattern_id;
307
308
309 X_Allowable_Schedule_Id NUMBER(15);
310 X_Preaward_Date DATE;
311 X_End_Date DATE;
312 X_Close_Date DATE;
313 X_Status VARCHAR2(30);
314
315
316
317 INVALID_FUNDING_PATTERN EXCEPTION;
318 INVALID_EXP_ITEM_DATE EXCEPTION;
319 INVALID_EXP_ITEM_DATE_1 EXCEPTION;
320 INVALID_EXP_ITEM_DATE_2 EXCEPTION;
321 INVALID_EXP_ITEM_DATE_3 EXCEPTION;
322
323 X_Failed_test BOOLEAN ;
324 X_Failed_test_1 BOOLEAN ;
325 X_Failed_test_2 BOOLEAN ;
326 X_Failed_test_3 BOOLEAN ;
327
328 x_error_program_name VARCHAR2 ( 30 );
329 x_error_procedure_name VARCHAR2 ( 30 );
330 x_error_stage VARCHAR2 ( 30 );
331
332 BEGIN
333 x_error_program_name := 'GMS_TRANSACTIONS_PUB';
334
335 x_error_procedure_name := 'DIST_AWARD_VALIDATIONS';
336 -- ------------------------------------------------------------------
337 -- Check if a funding pattern exists for the project,task combination
338 -- Check if the expenditure type exists in the allowed cost schedule
339 -- for the award.
340 -- ------------------------------------------------------------------
341 x_error_stage:= 'FUND_PATTERN_EXIST';
342
343 if PA_TRX_IMPORT.Get_GVal_ProjTskEi_Date ='N' then --Added for bug6931778
344 return;
345 end if;
346
347
348 OPEN FUND_PATTERN_EXIST ;
349
350 FETCH FUND_PATTERN_EXIST
351 into x_funding_name,
352 x_funding_pattern_id ;
353
354 IF FUND_PATTERN_EXIST%NOTFOUND THEN
355 CLOSE FUND_PATTERN_EXIST ;
356 RAISE INVALID_FUNDING_PATTERN;
357 END IF ;
358
359 CLOSE FUND_PATTERN_EXIST ;
360
361 X_failed_test := FALSE;
362 X_failed_test_1 := FALSE;
363 X_failed_test_2 := FALSE;
364 X_failed_test_3 := FALSE;
365
366 FOR FP_REC in FUND_PATTERN_EXIST LOOP
367
368 x_error_stage := 'EXP_ITEM_DATE_VALIDATION';
369 X_failed_test := FALSE;
370 X_failed_test_1 := FALSE;
371 X_failed_test_2 := FALSE;
372 X_failed_test_3 := FALSE;
373
374 FOR FP_AWARDS_REC in GET_FP_AWARDS(x_funding_pattern_id) LOOP
375 IF PA_TRX_IMPORT.Get_GVal_ProjTskEi_Date ='Y' THEN -- S.N Bug#4138033
376 IF (P_Expenditure_Item_Date < TRUNC(fp_awards_rec.awd_Date)) then
377 x_failed_test_1 := TRUE ;
378 EXIT ;
379 END IF;
380
381 IF (P_Expenditure_Item_Date > TRUNC(fp_awards_rec.End_Date_active)) then
382 x_failed_test_2 := TRUE ;
383 EXIT ;
384 END IF;
385 IF (fp_awards_rec.Close_Date < TRUNC(SYSDATE)) then
386 x_failed_test_3 := TRUE ;
387 EXIT ;
388 END IF;
389 END IF;
390 END LOOP;
391
392 IF X_failed_test_1 OR X_failed_test_2 OR X_failed_test_3 THEN
393 X_failed_test := TRUE ;
394 END IF ;
395
396 IF not X_failed_test THEN
397 EXIT ;
398 END IF ;
399
400 END LOOP;
401
402 IF X_failed_test_1 THEN
403 RAISE INVALID_EXP_ITEM_DATE_1;
404 END IF ;
405
406 IF X_failed_test_2 THEN
407 RAISE INVALID_EXP_ITEM_DATE_2;
408 END IF ;
409
410 IF X_failed_test_3 THEN
411 RAISE INVALID_EXP_ITEM_DATE_3;
412 END IF ;
413
414 x_err_code := 0;
415 EXCEPTION
416 WHEN INVALID_FUNDING_PATTERN THEN
417 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_INVALID_FUNDING_PATTERN',
418 x_err_code => x_err_code,
419 x_err_buff => x_err_buff) ;
420
421 P_outcome := FND_MESSAGE.GET;
422
423 WHEN INVALID_EXP_ITEM_DATE THEN
424 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_EXP_ITEM_DATE_INVALID',
425 x_err_code => x_err_code,
426 x_err_buff => x_err_buff) ;
427
428 P_outcome := FND_MESSAGE.GET;
429
430 WHEN INVALID_EXP_ITEM_DATE_1 THEN
431 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_EXP_ITEM_DT_BEFORE_AWD_ST',
432 x_err_code => x_err_code,
433 x_err_buff => x_err_buff) ;
434
435 P_outcome := FND_MESSAGE.GET;
436
437 WHEN INVALID_EXP_ITEM_DATE_2 THEN
438 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_EXP_ITEM_DT_AFTER_AWD_END',
439 x_err_code => x_err_code,
440 x_err_buff => x_err_buff) ;
441
442 P_outcome := FND_MESSAGE.GET;
443
444 WHEN INVALID_EXP_ITEM_DATE_3 THEN
445 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_AWARD_IS_CLOSED',
446 x_err_code => x_err_code,
447 x_err_buff => x_err_buff) ;
448
449 P_outcome := FND_MESSAGE.GET;
450
451 WHEN OTHERS THEN
452 gms_error_pkg.gms_message ( x_err_name=> 'GMS_UNEXPECTED_ERROR',
453 x_token_name1 => 'PROGRAM_NAME',
454 x_token_val1 => x_error_program_name || '.' || x_error_procedure_name || '.' || x_error_stage,
455 x_token_name2 => 'SQLCODE',
456 x_token_val2 => SQLCODE,
457 x_token_name3 => 'SQLERRM',
458 x_token_val3 => SQLERRM,
459 x_err_code => x_err_code,
460 x_err_buff => x_err_buff );
461
462 END DIST_AWARD_VALIDATIONS;
463 ----------------------------------------------------------------------
464 -- Please refer to package spec for detailed description of the
465 -- procedure.
466 ----------------------------------------------------------------------
467
468 PROCEDURE validate_transaction( P_project_id IN NUMBER
469 , P_task_id IN NUMBER
470 , P_award_id IN NUMBER
471 , P_expenditure_type IN VARCHAR2
472 , P_expenditure_item_date IN DATE
473 , P_calling_module IN VARCHAR2
474 , P_OUTCOME OUT NOCOPY VARCHAR2)
475 IS
476
477 CURSOR GET_VALID_AWARDS IS
478 Select Allowable_Schedule_Id,
479 nvl(Preaward_Date,START_DATE_ACTIVE),
480 End_Date_Active,
481 Close_Date,
482 Status
483 from GMS_AWARDS_all --- Added for Bug#13370217
484 where award_id = P_award_id;
485 --where award_id = to_number( P_award_id);
486
487 X_Allowable_Schedule_Id NUMBER(15);
488
489 X_Preaward_Date DATE;
490 X_End_Date DATE;
491 X_Close_Date DATE;
492
493 X_Status VARCHAR2(30);
494
495 /*Added for bug#12822370 */
496 l_org_id pa_implementations.org_id%type;
497
498 --
499 -- BUG: 3628884 Performance issue due to non mergable view.
500 --
501 CURSOR GET_FUNDING_AWARD IS
502 select aw.award_id award_id
503 from pa_tasks t ,
504 gms_installments ins,
505 gms_summary_project_fundings su,
506 gms_budget_versions bv,
507 gms_awards_all aw --- Added for Bug#13370217
508 where bv.budget_status_code = 'B'
509 and bv.project_id = P_Project_Id
510 and bv.award_id = P_award_id
511 and su.project_id = bv.project_id
512 and t.project_id = bv.project_id
513 and t.task_id = P_Task_Id
514 and ((su.task_id= t.task_id) or (su.task_id is null) or (su.task_id = t.top_task_id ) )
515 and ins.installment_id = su.installment_id
516 and ins.award_id = aw.award_id
517 and aw.award_id = P_award_id
518 and aw.status <> 'CLOSED'
519 and aw.award_template_flag = 'DEFERRED' ;
520
521 --Select award_id
522 --from GMS_AWARDS_BASIC_V
523 --where project_id = P_Project_Id
524 --and task_id = P_Task_Id
525 --and award_id = P_award_id;
526 --and award_id = to_number(P_award_id);
527
528 Funding_Award_Id NUMBER(15);
529
530 CURSOR GET_EXP_TYPE IS
531 Select Expenditure_Type
532 from GMS_ALLOWABLE_EXPENDITURES
533 where ALLOWABILITY_SCHEDULE_ID = X_Allowable_Schedule_Id and
534 EXPENDITURE_TYPE = P_expenditure_type;
535
536 St_Expenditure_Type VARCHAR2(30);
537
538
539
540
541 AWARD_IS_REQUIRED EXCEPTION;
542 NOT_FUNDING_AWARD EXCEPTION;
543 INVALID_AWARD_SCHEDULE EXCEPTION;
544 INVALID_EXP_TYPE EXCEPTION;
545 AWARD_NOT_ALLOWED EXCEPTION;
546 EXP_ITEM_DATE_INVALID EXCEPTION;
547 INVALID_AWARD EXCEPTION; --bug 2305262
548 EXP_ITEM_DATE_INVALID_1 EXCEPTION;
549 EXP_ITEM_DATE_INVALID_2 EXCEPTION;
550 EXP_ITEM_DATE_INVALID_3 EXCEPTION;
551 AWARD_NOT_ACTIVE EXCEPTION;
552 p_err_code NUMBER ;
553 p_err_buf varchar2(2000) ;
554 imp_award_id NUMBER;
555 BEGIN
556 /* Added for bug#12822370 start changes */
557
558 --select FND_GLOBAL.ORG_ID into l_org_id from dual;
559
560 --mo_global.set_policy_context('S',l_org_id);
561 /* Commented the changes for bug#13370217 */
562
563 /* Added for bug#12822370 End changes */
564
565 fnd_msg_pub.initialize;
566
567 P_outcome := NULL;
568
569 If (P_Calling_Module = 'PAVVIT' ) then
570 RETURN ;
571 END IF ;
572
573 if PA_TRX_IMPORT.Get_GVal_ProjTskEi_Date ='N' then --Added for bug6931778
574 return;
575 end if;
576
577 set_award;
578
579 if g_award_distribution_option = 'Y'THEN
580
581
582 IF P_AWARD_ID = g_award_id THEN
583 DIST_AWARD_VALIDATIONS (P_project_id
584 , P_task_id
585 , P_award_id
586 , P_expenditure_type
587 , P_expenditure_item_date
588 , p_err_code
589 , p_err_buf
590 , p_outcome );
591 RETURN ;
592 END IF ;
593 end if;
594 -- ===========================
595 -- Check if GMS is enabled
596 -- ===========================
597
598 IF not gms_install.enabled THEN
599 return ;
600 END IF ;
601
602 If NOT AWARD_REQUIRED(P_Task_Id,P_Outcome) then
603
604 If ( P_award_id is NOT NULL ) then
605 RAISE AWARD_NOT_ALLOWED;
606 ELSE
607 -- =====================================================
608 -- BUG : 3008734
609 -- GMS Validation failed for non sponsored project.
610 -- =====================================================
611 return ;
612 End If;
613
614 End If; --End for AWARD_REQUIRED Check
615
616 -- -------------------------------------
617 -- Award is REQUIRED..
618 -- -------------------------------------
619
620 /* If Award is required then Attribute1 should not be null */
621 Begin
622 If ( P_award_id is NULL) then
623 RAISE AWARD_IS_REQUIRED;
624 End If;
625 End;
626
627 -- ----------------------------
628 -- Check for valid award.
629 -- ---------------------------
630
631 Begin
632
633 open GET_VALID_AWARDS;
634 Fetch GET_VALID_AWARDS
635 into X_Allowable_Schedule_Id,
636 X_Preaward_Date,
637 X_End_Date,
638 X_Close_Date,
639 X_Status;
640
641 /* Check for Valid Award */
642 If GET_VALID_AWARDS%NOTFOUND THEN
643 RAISE INVALID_AWARD; -- bug 2305262
644 End If;
645
646 CLOSE GET_VALID_AWARDS;
647 End;
648 -- ======== End of valid award ============
649
650 --------------------------------------------------
651 /* Check for Valid Expenditure Item Date */
652 -- The following validation should NOT be performed if the
653 -- calling module is GMS-SSP since we don't have the expenditure_item_date
654 -- while calling this from SSP.
655
656
657 IF PA_TRX_IMPORT.Get_GVal_ProjTskEi_Date ='Y' THEN -- S.N Bug#4138033
658
659 If (P_Expenditure_Item_Date < TRUNC(X_Preaward_Date))then
660 RAISE EXP_ITEM_DATE_INVALID_1;
661 End If;
662
663 If (P_Expenditure_Item_Date > TRUNC(X_End_Date)) then
664 RAISE EXP_ITEM_DATE_INVALID_2;
665 End If;
666
667 If (X_Close_Date < TRUNC(SYSDATE)) then
668 RAISE EXP_ITEM_DATE_INVALID_3;
669 End If;
670
671 END IF; -- E.N Bug#4138033
672 -- ==== End of Expenditure Item Date check ==========
673
674 --------------------------------------------------
675 /* Check for Award Status */
676 If X_Status not in ('ACTIVE','AT_RISK') then
677 RAISE AWARD_NOT_ACTIVE;
678 End If;
679 -- ====== End of Award Status check =============
680
681 --------------------------------------------------
682 /* Check to see if Award is funding Project */
683 Begin
684
685 Open GET_FUNDING_AWARD;
686 Fetch GET_FUNDING_AWARD
687 into Funding_Award_Id;
688
689 If GET_FUNDING_AWARD%NOTFOUND THEN
690 RAISE NOT_FUNDING_AWARD;
691 End If;
692
693 CLOSE GET_FUNDING_AWARD;
694 End;
695 -- ========= End Of Award is funding Project check ===========
696
697
698 -------------------------------------------------------
699 /* Check for Valid Expenditure Type (Should be in the Allowability Schedule) */
700 Begin
701 open GET_EXP_TYPE;
702
703 Fetch GET_EXP_TYPE
704 into St_Expenditure_Type;
705
706 If GET_EXP_TYPE%NOTFOUND then
707 RAISE INVALID_EXP_TYPE;
708 End If;
709
710 CLOSE GET_EXP_TYPE;
711 End;
712 -- ======== End of Valid Expenditure Type check ==============
713
714 EXCEPTION
715 WHEN AWARD_IS_REQUIRED THEN
716 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_AWARD_REQUIRED',
717 x_err_code => p_err_code,
718 x_err_buff => p_err_buf) ;
719 P_outcome := FND_MESSAGE.GET;
720
721 -- ==============================================================
722 -- BUG: 1961436 (PA/GMS IMPORT DOES NOT GIVE SPECIFIC REASON CODE
723 -- FOR REJECTED INTERFACE TXNS.
724 -- ==============================================================
725 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB' ,'TXNVALID','APTXNIMP') THEN --bug:6817867
726 p_outcome := 'GMS_AWARD_REQUIRED' ;
727 END IF ;
728
729 WHEN NOT_FUNDING_AWARD THEN
730 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_NOT_FUNDING_AWARD',
731 x_err_code => p_err_code, x_err_buff => p_err_buf) ;
732 P_outcome := FND_MESSAGE.GET;
733
734 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB','PAXTTRXB','TXNVALID','APTXNIMP') THEN --bug:6817867
735 p_outcome := 'GMS_NOT_FUNDING_AWARD' ;
736 END IF ;
737
738
739 WHEN INVALID_AWARD THEN -- Change from INVALID_AWARD_SECHEDULE for bug 2305262
740 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_INVALID_AWARD',
741 x_err_code => p_err_code, x_err_buff => p_err_buf) ;
742 P_outcome := FND_MESSAGE.GET;
743
744 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB','PAXTTRXB','TXNVALID' ,'APTXNIMP') THEN --bug:6817867
745 p_outcome := 'GMS_INVALID_AWARD' ; -- Change from GMS_INV_AWARD_SCHEDULE bug 2305262
746 END IF ;
747
748 WHEN INVALID_EXP_TYPE THEN
749 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_INVALID_EXP_TYPE',
750 x_err_code => p_err_code, x_err_buff => p_err_buf) ;
751 P_outcome := FND_MESSAGE.GET;
752
753 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB','PAXTTRXB','TXNVALID','APTXNIMP') THEN --bug:6817867
754 p_outcome := 'GMS_INVALID_EXP_TYPE' ;
755 END IF ;
756
757
758 WHEN AWARD_NOT_ALLOWED THEN
759 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_NOT_A_SPONSORED_PROJECT',
760 x_err_code => p_err_code, x_err_buff => p_err_buf) ;
761 P_outcome := FND_MESSAGE.GET;
762
763 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB','PAXTTRXB','TXNVALID' ,'APTXNIMP') THEN --bug:6817867
764 p_outcome := 'GMS_NOT_A_SPONSORED_PROJECT' ;
765 END IF ;
766
767
768 WHEN EXP_ITEM_DATE_INVALID THEN
769 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_EXP_ITEM_DATE_INVALID',
770 x_err_code => p_err_code, x_err_buff => p_err_buf) ;
771 P_outcome := FND_MESSAGE.GET;
772
773 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB','PAXTTRXB','TXNVALID','APTXNIMP') THEN --bug:6817867
774 p_outcome := 'GMS_EXP_ITEM_DATE_INVALID' ;
775 END IF ;
776
777
778 WHEN EXP_ITEM_DATE_INVALID_1 THEN
779 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_EXP_ITEM_DT_BEFORE_AWD_ST',
780 x_err_code => p_err_code, x_err_buff => p_err_buf) ;
781 P_outcome := FND_MESSAGE.GET;
782
783 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB','PAXTTRXB','TXNVALID','APTXNIMP') THEN --bug:6817867
784 p_outcome := 'GMS_EXP_ITEM_DT_BEFORE_AWD_ST' ;
785 END IF ;
786
787
788 WHEN EXP_ITEM_DATE_INVALID_2 THEN
789 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_EXP_ITEM_DT_AFTER_AWD_END',
790 x_err_code => p_err_code, x_err_buff => p_err_buf) ;
791 P_outcome := FND_MESSAGE.GET;
792
793 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB','PAXTTRXB','TXNVALID','APTXNIMP') THEN --bug:6817867
794 p_outcome := 'GMS_EXP_ITEM_DT_AFTER_AWD_END' ;
795 END IF ;
796
797
798 WHEN EXP_ITEM_DATE_INVALID_3 THEN
799 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_AWARD_IS_CLOSED',
800 x_err_code => p_err_code, x_err_buff => p_err_buf) ;
801 P_outcome := FND_MESSAGE.GET;
802
803 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB','PAXTTRXB','TXNVALID','APTXNIMP') THEN --bug:6817867
804 p_outcome := 'GMS_AWARD_IS_CLOSED' ;
805 END IF ;
806
807
808 WHEN AWARD_NOT_ACTIVE THEN
809 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_AWARD_NOT_ACTIVE',
810 x_err_code => p_err_code, x_err_buff => p_err_buf
811 ) ;
812 P_outcome := FND_MESSAGE.GET;
813
814 IF P_calling_module in ( 'PSPLDCDB', 'PSPLDPGB','PSPENLNB','PAXTTRXB','TXNVALID','APTXNIMP') THEN --bug:6817867
815 p_outcome := 'GMS_AWARD_NOT_ACTIVE' ;
816 END IF ;
817
818
819 WHEN OTHERS THEN
820 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
821 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_TRANSACTIONS_PUB : VALIDATE_TRANSACTION',
822 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
823 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
824 x_err_code => p_err_code, x_err_buff => p_err_buf
825 ) ;
826 RAISE ;
827 END VALIDATE_TRANSACTION;
828 -- =============== End Of VALIDATE_TRANSACTION ===========================
829
830 FUNCTION IS_SPONSORED_PROJECT( x_project_id in NUMBER ) return BOOLEAN
831 is
832 cursor C_spon_project is
833 select pt.sponsored_flag
834 from pa_projects_all b,
835 --gms_project_types pt /*Commented for bug#12822370 */
836 pa_project_types_all pt /*Added for bug#12822370 */
837 where b.project_id = X_project_id
838 and b.project_type = pt.project_type
839 and pt.sponsored_flag = 'Y' ;
840
841 x_return BOOLEAN ;
842 x_flag varchar2(1) ;
843 BEGIN
844
845 x_return := FALSE ;
846
847 open C_spon_project ;
848 fetch C_spon_project into x_flag ;
849 close C_spon_project ;
850
851 IF nvl(x_flag, 'N') = 'Y' THEN
852 x_return := TRUE ;
853 END IF ;
854
855 return x_return ;
856
857 END IS_SPONSORED_PROJECT ;
858
859
860 PROCEDURE validate_award ( X_project_id IN NUMBER,
861 X_task_id IN NUMBER,
862 X_award_id IN NUMBER,
863 X_award_number IN VARCHAR2,
864 X_expenditure_type IN VARCHAR2,
865 X_expenditure_item_date IN DATE,
866 X_calling_module IN VARCHAR2,
867 X_status IN OUT NOCOPY VARCHAR2,
868 X_err_msg OUT NOCOPY VARCHAR2 ) is -- return boolean is
869
870 l_project_type_class_code varchar2(30);
871 l_row_found varchar2(1);
872 l_award_id NUMBER ;
873
874 cursor valid_award_csr is
875 select 'Y'
876 from dual
877 where exists
878 (select 1
879 from gms_awards
880 where award_number = X_award_number
881 and nvl(award_id,0) = nvl(l_award_id,0));
882
883
884 BEGIN
885
886 -- ==============================================================
887 -- Do not proceed if grants is not enabled for an implementation
888 -- Org.
889 -- ==============================================================
890 IF not gms_install.enabled then
891 return ;
892 END IF ;
893
894
895 -- ============================================
896 -- No need to proceed if project/award details
897 -- are null.
898 -- ============================================
899 IF x_project_id is NULL AND
900 x_award_id is NULL AND
901 x_award_number is NULL THEN
902
903 return ;
904 END IF ;
905
906 IF (x_award_number is not null and (x_project_id is null or x_task_id is null
907 or x_project_id= 0 or x_task_id = 0))
908 then
909 fnd_message.set_name('GMS','GMS_INVALID_PROJ_TASK_ID');
910 X_status := 'E';
911 X_err_msg := fnd_message.get;
912 return;
913
914 END IF;
915
916 -- =======================================================
917 -- List of validations done here
918 -- 1. Check for contract project. contract project shouldn't
919 -- entered if grants is enabled.
920 -- 2. Nonsponsored project having award should fail.
921 -- 3. Invalid award should stop here.
922 -- 4. Populate award id if required.
923 -- Award id passed null and award_number is not null.
924 -- 5. Sponsored project missing award should error out.
925 -- 6. Check expenditure type belongs to allowable exp's.
926 -- 7. Call gms standard validations defined in
927 -- gms_transaction_pub.
928 -- ================================================================
929
930 l_award_id := X_award_id ;
931
932 -- 1. Check for contract project. contract project shouldn't
933 -- entered if grants is enabled.
934
935 IF X_project_id is not NULL THEN
936 begin
937 select project_type_class_code
938 into l_project_type_class_code
939 from pa_project_types_all a,
940 pa_projects_all b
941 where a.project_type = b.project_type
942 and a.org_id = b.org_id /*For Bug 5414832*/
943 and b.project_id = X_project_id;
944
945 exception
946 when no_data_found then
947 fnd_message.set_name('GMS','GMS_INVALID_PROJ_TASK_ID');
948 X_status := 'E';
949 X_err_msg := fnd_message.get;
950 return;
951
952 END;
953
954 END IF ;
955
956 if l_project_type_class_code = 'CONTRACT' then
957
958 fnd_message.set_name('GMS','GMS_IP_INVALID_PROJ_TYPE');
959
960 X_status := 'E';
961 X_err_msg := fnd_message.get;
962
963 return;
964 end if;
965
966 IF is_sponsored_project (X_project_id) THEN
967
968 -- 5. Sponsored project missing award should error out.
969 IF X_award_number is NULL then
970 fnd_message.set_name('GMS','GMS_AWARD_REQUIRED');
971 X_status := 'E';
972 X_err_msg := fnd_message.get;
973 return;
974 END IF ;
975
976 ELSE
977
978 -- 2. Nonsponsored project having award should fail.
979 IF X_award_number is NOT NULL then
980 fnd_message.set_name('GMS','GMS_AWARD_NOT_ALLOWED');
981 X_status := 'E';
982 X_err_msg := fnd_message.get;
983 return;
984 ELSIF X_award_number is NULL then
985 return;
986 END IF ;
987
988 END IF ;
989
990 -- 3. Populate award id if required.
991 -- Award id passed null and award_number is not null.
992
993 l_award_id := X_award_id ;
994
995 if X_award_id is NULL and
996 X_award_number is not NULL then
997
998 begin
999 select award_id
1000 into l_award_id
1001 from gms_awards
1002 where award_number = X_award_number;
1003
1004 exception
1005 when no_data_found then
1006 fnd_message.set_name('GMS','GMS_INVALID_AWARD');
1007 X_status := 'E';
1008 X_err_msg := fnd_message.get;
1009 return;
1010 end;
1011
1012 end if;
1013
1014 -- 4. Invalid award should stop here.
1015
1016 open valid_award_csr;
1017 fetch valid_award_csr into l_row_found;
1018 close valid_award_csr;
1019
1020 if NVL(l_row_found,'N') <> 'Y' then
1021
1022 fnd_message.set_name('GMS','GMS_INVALID_AWARD');
1023
1024 X_status := 'E';
1025 X_err_msg := fnd_message.get;
1026
1027 return;
1028
1029 end if;
1030
1031 -- 7. Call gms standard validations defined in
1032 -- gms_transaction_pub.
1033
1034 gms_transactions_pub.validate_transaction(p_project_id => X_project_id,
1035 p_task_id => X_task_id,
1036 p_award_id => l_award_id,
1037 p_expenditure_type => X_expenditure_type,
1038 p_expenditure_item_date => X_expenditure_item_date,
1039 p_calling_module => X_calling_module,
1040 p_outcome => X_err_msg );
1041
1042 if X_err_msg is NOT NULL then
1043
1044 X_status := 'E';
1045 return;
1046
1047 end if;
1048
1049 return;
1050
1051 END validate_award ;
1052
1053 END GMS_TRANSACTIONS_PUB;