1 PACKAGE BODY GMS_AWARDS_BOUNDARY_DATES_CHK AS
2 -- $Header: gmsawvdb.pls 120.6 2007/02/07 05:09:31 asubrama ship $
3
4 Procedure validate_start_date( P_AWARD_ID IN NUMBER,
5 P_START_DATE IN DATE,
6 X_MESSAGE OUT NOCOPY VARCHAR2) IS
7
8 CURSOR budget_lines_csr IS
9 SELECT 1
10 FROM gms_budget_versions pbv,
11 gms_resource_assignments pra,
12 gms_budget_lines pbl
13 WHERE pbv.budget_version_id = pra.budget_version_id
14 AND pbv.award_id = P_AWARD_ID
15 AND pra.resource_assignment_id = pbl.resource_assignment_id
16 AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y'))
17 and pbl.start_date < P_START_DATE
18 and P_START_DATE NOT BETWEEN pbl.start_date AND pbl.end_date--Condition added for Bug 5402500
19 and pbl.burdened_cost IS NOT null;--Condition added for Bug 5402500
20
21 CURSOR exp_items_csr IS
22 SELECT 1
23 FROM DUAL
24 WHERE EXISTS
25 (SELECT 'X'
26 FROM gms_award_distributions adl,
27 pa_expenditure_items_all exp
28 WHERE adl.expenditure_item_id = exp.expenditure_item_id
29 AND adl.document_type = 'EXP'
30 AND adl.adl_status = 'A'
31 AND adl.award_id = P_AWARD_ID
32 AND exp.expenditure_item_date < P_START_DATE) ;
33
34 CURSOR enc_items_csr IS
35 SELECT 1
36 FROM DUAL
37 WHERE EXISTS
38 (SELECT 'X'
39 FROM gms_award_distributions adl,
40 gms_encumbrance_items_all enc
41 WHERE adl.expenditure_item_id = enc.encumbrance_item_id
42 AND adl.document_type = 'ENC'
43 AND adl.adl_status = 'A'
44 AND adl.award_id = P_AWARD_ID
45 -- ==============================================================
46 -- = Bug Fix 3543931
47 -- = Award end date validations :
48 -- = Allow to change before fully liquidated encumbrances.
49 -- = ============================================================
50 AND NVL(enc.net_zero_adjustment_flag,'N') <> 'Y'
51 AND nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
52 AND adl.line_num_reversed is null --Bug 5726575
53 AND enc.encumbrance_item_date < P_START_DATE ) ;
54
55 -- ==============================================================
56 -- = Bug Fix 3543931
57 -- = Award end date validations :
58 -- = Allow to change before fully liquidated encumbrances.
59 -- = ============================================================
60 CURSOR enc_items_csr2 IS
61 SELECT 1
62 FROM DUAL
63 WHERE EXISTS
64 (SELECT 'X'
65 FROM gms_award_distributions adl1,
66 gms_encumbrance_items_all enc1,
67 gms_encumbrance_items_all enc2,
68 gms_award_distributions adl2
69 WHERE adl1.expenditure_item_id = enc1.encumbrance_item_id
70 AND adl1.document_type = 'ENC'
71 AND adl1.adl_status = 'A'
72 AND nvl(adl1.reversed_flag, 'N') = 'N' --Bug 5726575
73 AND adl1.line_num_reversed is null --Bug 5726575
74 AND adl1.award_id = P_AWARD_ID
75 AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
76 AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
77 AND enc2.adjusted_encumbrance_item_id = enc1.encumbrance_item_id
78 AND adl2.expenditure_item_id = enc2.encumbrance_item_id
79 AND adl2.document_type = 'ENC'
80 AND adl2.adl_status = 'A'
81 AND nvl(adl2.reversed_flag, 'N') = 'N' --Bug 5726575
82 AND adl2.line_num_reversed is null --Bug 5726575
83 AND adl2.award_id = P_AWARD_ID
84 AND adl2.fc_status <> adl1.fc_status
85 AND ( enc1.encumbrance_item_date < P_START_DATE OR
86 enc2.encumbrance_item_date < P_START_DATE ) ) ;
87
88 CURSOR po_items_csr IS
89 SELECT 1
90 FROM DUAL
91 WHERE EXISTS
92 (SELECT 'X'
93 FROM gms_award_distributions adl,
94 po_distributions_all po
95 WHERE adl.po_distribution_id = po.po_distribution_id
96 AND adl.adl_status = 'A'
97 AND adl.award_id = P_AWARD_ID
98 AND adl.award_set_id = po.award_id -- Bug 3985177
99 AND adl.adl_line_num = 1 -- Bug 3985177
100 AND adl.document_type = 'PO' -- Bug 3985177
101 AND po.expenditure_item_date < P_START_DATE );
102
103 CURSOR ap_items_csr IS
104 SELECT 1
105 FROM DUAL
106 WHERE EXISTS
107 (SELECT 'X'
108 FROM gms_award_distributions adl,
109 ap_invoice_distributions_all ap
110 WHERE adl.invoice_distribution_id = ap.invoice_distribution_id
111 AND adl.adl_status = 'A'
112 AND adl.award_id = P_AWARD_ID
113 AND ap.expenditure_item_date < P_START_DATE );
114
115 CURSOR req_items_csr IS
116 SELECT 1
117 FROM DUAL
118 WHERE EXISTS
119 (SELECT 'X'
120 FROM gms_award_distributions adl,
121 po_req_distributions_all req
122 WHERE adl.distribution_id = req.distribution_id
123 AND adl.adl_status = 'A'
124 AND adl.award_id = P_AWARD_ID
125 AND req.expenditure_item_date < P_START_DATE) ;
126
127 l_budget_lines NUMBER ;
128 l_exp_items NUMBER ;
129 l_enc_items NUMBER ;
130 l_po_items NUMBER ;
131 l_ap_items NUMBER ;
132 l_req_items NUMBER ;
133 l_document_type VARCHAR2(100) ;
134 x_err_code NUMBER;
135 x_err_stage VARCHAR2(4000);
136
137 BEGIN
138 l_budget_lines := 0;
139 l_exp_items := 0;
140 l_enc_items := 0;
141 l_po_items := 0;
142 l_ap_items := 0;
143 l_req_items := 0;
144 l_document_type := NULL;
145
146 OPEN budget_lines_csr;
147 FETCH budget_lines_csr INTO l_budget_lines;
148 IF budget_lines_csr%FOUND THEN
149 CLOSE budget_lines_csr;
150 X_MESSAGE := 'GMS_BUD_EXISTS';
151 return;
152 END IF;
153 CLOSE budget_lines_csr;
154
155 OPEN exp_items_csr;
156 FETCH exp_items_csr INTO l_exp_items;
157 IF exp_items_csr%FOUND THEN
158 CLOSE exp_items_csr;
159 X_MESSAGE := 'GMS_EXP_EXISTS';
160 return;
161 END IF;
162 CLOSE exp_items_csr;
163
164 OPEN po_items_csr;
165 FETCH po_items_csr INTO l_po_items;
166 IF po_items_csr%FOUND THEN
167 CLOSE po_items_csr;
168 X_MESSAGE := 'GMS_PO_EXISTS';
169 return;
170 END IF;
171 CLOSE po_items_csr;
172
173 OPEN ap_items_csr;
174 FETCH ap_items_csr INTO l_ap_items;
175 IF ap_items_csr%FOUND THEN
176 CLOSE ap_items_csr;
177 X_MESSAGE := 'GMS_AP_EXISTS';
178 return;
179 END IF;
180 CLOSE ap_items_csr;
181
182 OPEN req_items_csr;
183 FETCH req_items_csr INTO l_req_items;
184 IF req_items_csr%FOUND THEN
185 CLOSE req_items_csr;
186 X_MESSAGE := 'GMS_REQ_EXISTS';
187 return;
188 END IF;
189 CLOSE req_items_csr;
190
191 --- S.N. Bug# 4138033
192 -- Moved this code to here as
193 -- the existense of encumbrances needs to be verified if at all there are no
194 -- other transactions for the award such as actuals/po/req/ etc
195 -- before the new start date.
196
197 OPEN enc_items_csr;
198 FETCH enc_items_csr INTO l_enc_items;
199 IF enc_items_csr%FOUND THEN
200 CLOSE enc_items_csr;
201 X_MESSAGE := 'GMS_ENC_EXISTS';
202 return;
203 END IF;
204 CLOSE enc_items_csr;
205
206 -- = Bug Fix 3543931
207 OPEN enc_items_csr2;
208 FETCH enc_items_csr2 INTO l_enc_items;
209 IF enc_items_csr2%FOUND THEN
210 CLOSE enc_items_csr2;
211 X_MESSAGE := 'GMS_ENC_EXISTS';
212 return;
213 END IF;
214 CLOSE enc_items_csr2;
215 --- E.N. Bug# 4138033
216 END validate_start_date;
217
218
219 Procedure validate_end_date( P_AWARD_ID IN NUMBER,
220 P_END_DATE IN DATE,
221 X_MESSAGE OUT NOCOPY VARCHAR2) IS
222
223 CURSOR budget_lines_csr IS
224 SELECT 1
225 FROM gms_budget_versions pbv,
226 gms_resource_assignments pra,
227 gms_budget_lines pbl
228 WHERE pbv.budget_version_id = pra.budget_version_id
229 AND pbv.award_id = P_AWARD_ID
230 AND pra.resource_assignment_id = pbl.resource_assignment_id
231 AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y'))
232 and pbl.end_date > P_END_DATE
233 and P_END_DATE NOT BETWEEN pbl.start_date AND pbl.end_date--Condition added for Bug 5411155
234 and pbl.burdened_cost IS NOT null;--Condition added for Bug 5411155
235
236
237 CURSOR exp_items_csr IS
238 SELECT 1
239 FROM DUAL
240 WHERE EXISTS
241 (SELECT 'X'
242 FROM gms_award_distributions adl,
243 pa_expenditure_items_all exp
244 WHERE adl.expenditure_item_id = exp.expenditure_item_id
245 AND adl.document_type = 'EXP'
246 AND adl.adl_status = 'A'
247 AND adl.award_id = P_AWARD_ID
248 AND exp.expenditure_item_date > P_END_DATE) ;
249
250 CURSOR enc_items_csr IS
251 SELECT 1
252 FROM DUAL
253 WHERE EXISTS
254 (SELECT 'X'
255 FROM gms_award_distributions adl,
256 gms_encumbrance_items_all enc
257 WHERE adl.expenditure_item_id = enc.encumbrance_item_id
258 AND adl.document_type = 'ENC'
259 AND adl.adl_status = 'A'
260 AND adl.award_id = P_AWARD_ID
261 -- ==============================================================
262 -- = Bug Fix 3543931
263 -- = Award end date validations :
264 -- = Allow to change before fully liquidated encumbrances.
265 -- = ============================================================
266 AND NVL(enc.net_zero_adjustment_flag,'N') <> 'Y'
267 AND nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
268 AND adl.line_num_reversed is null --Bug 5726575
269 AND enc.encumbrance_item_date > P_END_DATE ) ;
270
271 -- ==============================================================
272 -- = Bug Fix 3543931
273 -- = Award end date validations :
274 -- = Allow to change before fully liquidated encumbrances.
275 -- = ============================================================
276 CURSOR enc_items_csr2 IS
277 SELECT 1
278 FROM DUAL
279 WHERE EXISTS
280 (SELECT 'X'
281 FROM gms_award_distributions adl1,
282 gms_encumbrance_items_all enc1,
283 gms_encumbrance_items_all enc2,
284 gms_award_distributions adl2
285 WHERE adl1.expenditure_item_id = enc1.encumbrance_item_id
286 AND adl1.document_type = 'ENC'
287 AND adl1.adl_status = 'A'
288 AND nvl(adl1.reversed_flag, 'N') = 'N' --Bug 5726575
289 AND adl1.line_num_reversed is null --Bug 5726575
290 AND adl1.award_id = P_AWARD_ID
291 AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
292 AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
293 AND enc2.adjusted_encumbrance_item_id = enc1.encumbrance_item_id
294 AND adl2.expenditure_item_id = enc2.encumbrance_item_id
295 AND adl2.document_type = 'ENC'
296 AND adl2.adl_status = 'A'
297 AND nvl(adl2.reversed_flag, 'N') = 'N' --Bug 5726575
298 AND adl2.line_num_reversed is null --Bug 5726575
299 AND adl2.award_id = P_AWARD_ID
300 AND adl2.fc_status <> adl1.fc_status
301 AND ( enc1.encumbrance_item_date > P_END_DATE OR
302 enc2.encumbrance_item_date > P_END_DATE ) ) ;
303
304 CURSOR po_items_csr IS
305 SELECT 1
306 FROM DUAL
307 WHERE EXISTS
308 (SELECT 'X'
309 FROM gms_award_distributions adl,
310 po_distributions_all po
311 WHERE adl.po_distribution_id = po.po_distribution_id
312 AND adl.adl_status = 'A'
313 AND adl.award_id = P_AWARD_ID
314 AND adl.award_set_id = po.award_id -- Bug 3985177
315 AND adl.adl_line_num = 1 -- Bug 3985177
316 AND adl.document_type = 'PO' -- Bug 3985177
317 AND po.expenditure_item_date > P_END_DATE );
318
319 CURSOR ap_items_csr IS
320 SELECT 1
321 FROM DUAL
322 WHERE EXISTS
323 (SELECT 'X'
324 FROM gms_award_distributions adl,
325 ap_invoice_distributions_all ap
326 WHERE adl.invoice_distribution_id = ap.invoice_distribution_id
327 AND adl.adl_status = 'A'
328 AND adl.award_id = P_AWARD_ID
329 AND ap.expenditure_item_date > P_END_DATE );
330
331 CURSOR req_items_csr IS
332 SELECT 1
333 FROM DUAL
334 WHERE EXISTS
335 (SELECT 'X'
336 FROM gms_award_distributions adl,
337 po_req_distributions_all req
338 WHERE adl.distribution_id = req.distribution_id
339 AND adl.adl_status = 'A'
340 AND adl.award_id = P_AWARD_ID
341 AND req.expenditure_item_date > P_END_DATE) ;
342
343 l_budget_lines NUMBER ;
344 l_exp_items NUMBER ;
345 l_enc_items NUMBER ;
346 l_po_items NUMBER ;
347 l_ap_items NUMBER ;
351 x_err_stage VARCHAR2(4000);
348 l_req_items NUMBER ;
349 l_document_type VARCHAR2(100) ;
350 x_err_code NUMBER;
352
353 BEGIN
354
355 l_budget_lines := 0;
356 l_exp_items := 0;
357 l_enc_items := 0;
358 l_po_items := 0;
359 l_ap_items := 0;
360 l_req_items := 0;
361 l_document_type := NULL;
362
363 OPEN budget_lines_csr;
364 FETCH budget_lines_csr INTO l_budget_lines;
365 IF budget_lines_csr%FOUND THEN
366 CLOSE budget_lines_csr;
367 X_MESSAGE := 'GMS_BUD_EXISTS';
368 return;
369 END IF;
370 CLOSE budget_lines_csr;
371
372 OPEN exp_items_csr;
373 FETCH exp_items_csr INTO l_exp_items;
374 IF exp_items_csr%FOUND THEN
375 CLOSE exp_items_csr;
376 X_MESSAGE := 'GMS_EXP_EXISTS';
377 return;
378 END IF;
379 CLOSE exp_items_csr;
380
381 OPEN po_items_csr;
382 FETCH po_items_csr INTO l_po_items;
383 IF po_items_csr%FOUND THEN
384 CLOSE po_items_csr;
385 X_MESSAGE := 'GMS_PO_EXISTS';
386 return;
387 END IF;
388 CLOSE po_items_csr;
389
390 OPEN ap_items_csr;
391 FETCH ap_items_csr INTO l_ap_items;
392 IF ap_items_csr%FOUND THEN
393 CLOSE ap_items_csr;
394 X_MESSAGE := 'GMS_AP_EXISTS';
395 return;
396 END IF;
397 CLOSE ap_items_csr;
398
399 OPEN req_items_csr;
400 FETCH req_items_csr INTO l_req_items;
401 IF req_items_csr%FOUND THEN
402 CLOSE req_items_csr;
403 X_MESSAGE := 'GMS_REQ_EXISTS';
404 return;
405 END IF;
406 CLOSE req_items_csr;
407
408 --- S.N. Bug# 4138033
409 -- Moved this code to here as
410 -- the existense of encumbrances needs to be verified if at all there are no
411 -- other transactions for the award such as actuals/po/req/ etc
412 -- after the new close date.
413
414 OPEN enc_items_csr;
415 FETCH enc_items_csr INTO l_enc_items;
416 IF enc_items_csr%FOUND THEN
417 CLOSE enc_items_csr;
418 X_MESSAGE := 'GMS_ENC_EXISTS';
419 return;
420 END IF;
421 CLOSE enc_items_csr;
422
423 -- = Bug Fix 3543931
424 OPEN enc_items_csr2;
425 FETCH enc_items_csr2 INTO l_enc_items;
426 IF enc_items_csr2%FOUND THEN
427 CLOSE enc_items_csr2;
428 X_MESSAGE := 'GMS_ENC_EXISTS';
429 return;
430 END IF;
431 CLOSE enc_items_csr2;
432 --- E.N. Bug# 4138033
433
434 END validate_end_date;
435 -- ------------------------------------------------------------------------------------+
436 -- Added for Bug:2269791 (CHANGING INSTALLMENT DATE WHEN BASELINED BUDGET EXISTS)
437
438 -- This procedure will loop thru all the active installments of the Award whose ID is
439 -- passed in and validate the installment dates and funding amounts against all
440 -- the Award Budgets (both draft and current).
441
442
443 procedure validate_installment (x_award_id in NUMBER)
444 is
445
446 cursor draft_budget_csr (p_award_id in NUMBER)
447 is
448 select budget_version_id, project_id
449 from gms_budget_versions
450 where award_id = p_award_id
451 and budget_status_code in ('W','S');
452
453 cursor baselined_budget_csr (p_award_id in NUMBER)
454 is
455 select budget_version_id, project_id
456 from gms_budget_versions
457 where award_id = p_award_id
458 and budget_status_code = 'B'
459 and current_flag = 'Y';
460
461 cursor budget_lines_csr (p_budget_version_id in NUMBER)
462 is
463 select gra.resource_list_member_id,
464 gra.task_id,
465 gbl.start_date,
466 gbl.end_date
467 from gms_resource_assignments gra,
468 gms_budget_lines gbl
469 where gra.resource_assignment_id = gbl.resource_assignment_id
470 and gra.budget_version_id = p_budget_version_id;
471
472 l_return_status NUMBER ;
473
474 Begin
475 l_return_status := 0;
476 for baselined_budget_rec in baselined_budget_csr ( p_award_id => x_award_id)
477 loop
478 for budget_lines_rec in budget_lines_csr ( p_budget_version_id => baselined_budget_rec.budget_version_id)
479 loop
480 gms_budget_pub.validate_budget( x_budget_version_id => baselined_budget_rec.budget_version_id,
481 x_award_id => x_award_id,
482 x_project_id => baselined_budget_rec.project_id,
483 x_task_id => budget_lines_rec.task_id,
484 x_resource_list_member_id => budget_lines_rec.resource_list_member_id,
485 x_start_date => budget_lines_rec.start_date,
486 x_end_date =>budget_lines_rec.end_date,
487 x_return_status => l_return_status);
491 end loop;
488 if l_return_status <> 0 then
489 app_exception.raise_exception;
490 end if;
492 end loop;
493
494 for draft_budget_rec in draft_budget_csr (p_award_id => x_award_id)
495 loop
496 for budget_lines_rec in budget_lines_csr ( p_budget_version_id => draft_budget_rec.budget_version_id)
497 loop
498 gms_budget_pub.validate_budget( x_budget_version_id => draft_budget_rec.budget_version_id,
499 x_award_id => x_award_id,
500 x_project_id => draft_budget_rec.project_id,
501 x_task_id => budget_lines_rec.task_id,
502 x_resource_list_member_id => budget_lines_rec.resource_list_member_id,
503 x_start_date => budget_lines_rec.start_date,
504 x_end_date =>budget_lines_rec.end_date,
505 x_return_status => l_return_status);
506 if l_return_status <> 0 then
507 app_exception.raise_exception;
508 end if;
509 end loop; -- budget lines loop
510 end loop; -- budget loop
511 end validate_installment;
512
513 --- S.C Bug# 4138033
514 -- Added the parameter P_TASK_ID for the procedures validate_proj_start_date,
515 -- validate_proj_completion_date to enable validation for the task level if the task id is passed.
516 -- P_START_DATE and P_COMPLETION_DATE will be project/task start and completeion dates
517 -- If task id is not null then the P_START_DATE and P_COMPLETION_DATE will be for task
518 -- otherwise they represent the start and completion dates of Project.
519 --- E.C Bug# 4138033
520
521 Procedure validate_proj_start_date( P_PROJECT_ID IN NUMBER,
522 P_START_DATE IN DATE,
523 X_MESSAGE OUT NOCOPY VARCHAR2,
524 P_TASK_ID IN PA_TASKS.TASK_ID%TYPE DEFAULT NULL) IS /* Bug# 4138033 */
525
526
527 CURSOR budget_lines_csr IS
528 SELECT 1
529 FROM gms_budget_versions pbv,
530 gms_resource_assignments pra,
531 gms_budget_lines pbl
532 WHERE pbv.budget_version_id = pra.budget_version_id
533 AND pbv.project_id = P_PROJECT_ID
534 AND pra.resource_assignment_id = pbl.resource_assignment_id
535 AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y'))
536 /*Code change for bug 5470902 : Start */
537 --AND pbl.start_date < P_START_DATE;
538 AND pbl.end_date < P_START_DATE
539 AND pbl.burdened_cost IS NOT null;
540 /*Code change for bug 5470902 : End */
541
542 /* Bug# 4138033, apart from adding the task id condition, removed the joing with pa_task
543 as the project_id is available on expenditure items itself */
544 CURSOR exp_items_csr IS
545 SELECT 1
546 FROM DUAL
547 WHERE EXISTS
548 (SELECT 'X'
549 FROM pa_expenditure_items_all exp
550 /* pa_tasks tsk 4138033 */
551 WHERE /*exp.task_id = tsk.task_id
552 AND */ exp.project_id = P_PROJECT_ID
553 and exp.task_id = nvl(P_TASK_ID, exp.task_id)
554 AND exp.expenditure_item_date < P_START_DATE) ;
555
556 CURSOR enc_items_csr IS
557 SELECT 1
558 FROM DUAL
559 WHERE EXISTS
560 (SELECT 'X'
561 FROM gms_encumbrance_items_all enc,
562 pa_tasks tsk
563 WHERE enc.task_id = tsk.task_id
564 AND tsk.project_id = P_PROJECT_ID
565 AND tsk.task_id = nvl(P_TASK_ID, tsk.task_id)
566 -- ==============================================================
567 -- = Bug Fix 3543931
568 -- = Award end date validations :
569 -- = Allow to change before fully liquidated encumbrances.
570 -- = ============================================================
571 AND NVL(enc.net_zero_adjustment_flag,'N') <> 'Y'
572 AND enc.encumbrance_item_date < P_START_DATE ) ;
573
574
575 -- ==============================================================
576 -- = Bug Fix 3543931
577 -- = Award end date validations :
578 -- = Allow to change before fully liquidated encumbrances.
579 -- = ============================================================
580 CURSOR enc_items_csr2 IS
581 SELECT 1
582 FROM DUAL
583 WHERE EXISTS
584 (SELECT 'X'
585 FROM gms_encumbrance_items_all enc1,
586 gms_encumbrance_items_all enc2,
587 pa_tasks tsk
588 WHERE enc1.task_id = tsk.task_id
589 AND enc2.adjusted_encumbrance_item_id = enc1.encumbrance_item_id
590 AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
591 AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
592 AND enc2.enc_distributed_flag <> enc1.enc_distributed_flag
593 AND tsk.project_id = P_PROJECT_ID
597
594 AND tsk.task_id = NVL(P_TASK_ID, tsk.task_id)
595 AND ( enc1.encumbrance_item_date < P_START_DATE OR
596 enc2.encumbrance_item_date < P_START_DATE ) ) ;
598 CURSOR po_items_csr IS
599 SELECT 1
600 FROM DUAL
601 WHERE EXISTS
602 (SELECT 'X'
603 FROM po_distributions_all po
604 WHERE po.project_id = P_PROJECT_ID
605 AND po.task_id = nvl(P_TASK_ID, po.task_id)
606 AND po.expenditure_item_date < P_START_DATE) ;
607
608 --Bug 3985177 : Removed pa_tasks join in po_items_csr
609
610 CURSOR ap_items_csr IS
611 SELECT 1
612 FROM DUAL
613 WHERE EXISTS
614 (SELECT 'X'
615 FROM ap_invoice_distributions_all ap
616 WHERE ap.project_id = P_PROJECT_ID
617 AND ap.task_id = nvl(P_TASK_ID, ap.task_id)
618 AND ap.expenditure_item_date < P_START_DATE) ;
619
620 --Bug 3985177 : Removed pa_tasks join in ap_items_csr
621
622 CURSOR req_items_csr IS
623 SELECT 1
624 FROM DUAL
625 WHERE EXISTS
626 (SELECT 'X'
627 FROM po_req_distributions_all req
628 WHERE req.project_id = P_PROJECT_ID
629 AND req.task_id = nvl(P_TASK_ID, req.task_id)
630 AND req.expenditure_item_date < P_START_DATE) ;
631
632 --Bug 3985177 : Removed pa_tasks join in req_items_csr
633
634 CURSOR budget_lines_exist_csr IS
635 SELECT 1
636 FROM DUAL
637 WHERE EXISTS
638 ( SELECT 1
639 FROM gms_budget_versions pbv,
640 gms_resource_assignments pra,
641 gms_budget_lines pbl
642 WHERE pbv.budget_version_id = pra.budget_version_id
643 AND pbv.project_id = P_PROJECT_ID
644 AND pra.resource_assignment_id = pbl.resource_assignment_id
645 AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y')));
646
647
648 CURSOR txn_exists_csr IS
649 SELECT 1
650 FROM DUAL
651 WHERE EXISTS
652 (SELECT 1
653 FROM gms_award_distributions adl
654 WHERE adl.project_id = P_PROJECT_ID
655 AND adl.task_id = nvl(P_TASK_ID, adl.task_id));
656
657
658 l_budget_lines NUMBER ;
659 l_exp_items NUMBER ;
660 l_enc_items NUMBER ;
661 l_po_items NUMBER ;
662 l_ap_items NUMBER ;
663 l_req_items NUMBER ;
664 l_txn_exists NUMBER ;
665 l_document_type VARCHAR2(100) ;
666 x_err_code NUMBER;
667 x_err_stage VARCHAR2(4000);
668
669 BEGIN
670
671 l_budget_lines := 0;
672 l_exp_items := 0;
673 l_enc_items := 0;
674 l_po_items := 0;
675 l_ap_items := 0;
676 l_req_items := 0;
677 l_txn_exists := 0;
678 l_document_type := NULL;
679
680 -- If the project start date is nullified we need to see if it is used any where in the system.
681 -- If used then we donot allow the nullification.
682
683 IF P_START_DATE IS NULL THEN
684 OPEN budget_lines_exist_csr;
685 FETCH budget_lines_exist_csr INTO l_budget_lines;
686 IF budget_lines_exist_csr%FOUND THEN
687 CLOSE budget_lines_exist_csr;
688 X_MESSAGE := 'GMS_BUD_EXISTS';
689 return;
690 END IF;
691 CLOSE budget_lines_exist_csr;
692 END IF;
693
694 IF P_START_DATE IS NULL THEN
695 OPEN txn_exists_csr;
696 FETCH txn_exists_csr INTO l_txn_exists;
697 IF txn_exists_csr%FOUND THEN
698 CLOSE txn_exists_csr;
699 X_MESSAGE := 'GMS_TXN_EXISTS';
700 return;
701 END IF;
702 CLOSE txn_exists_csr;
703 END IF;
704
705 IF P_TASK_ID IS NULL THEN
706 OPEN budget_lines_csr;
707 FETCH budget_lines_csr INTO l_budget_lines;
708 IF budget_lines_csr%FOUND THEN
709 CLOSE budget_lines_csr;
710 X_MESSAGE := 'GMS_BUD_EXISTS';
711 return;
712 END IF;
713 CLOSE budget_lines_csr;
714 END IF;
715
716 OPEN exp_items_csr;
717 FETCH exp_items_csr INTO l_exp_items;
718 IF exp_items_csr%FOUND THEN
719 CLOSE exp_items_csr;
720 X_MESSAGE := 'GMS_EXP_EXISTS';
721 return;
722 END IF;
723 CLOSE exp_items_csr;
724
725 OPEN po_items_csr;
726 FETCH po_items_csr INTO l_po_items;
727 IF po_items_csr%FOUND THEN
728 CLOSE po_items_csr;
729 X_MESSAGE := 'GMS_PO_EXISTS';
730 return;
731 END IF;
732 CLOSE po_items_csr;
733
734 OPEN ap_items_csr;
735 FETCH ap_items_csr INTO l_ap_items;
736 IF ap_items_csr%FOUND THEN
737 CLOSE ap_items_csr;
741 CLOSE ap_items_csr;
738 X_MESSAGE := 'GMS_AP_EXISTS';
739 return;
740 END IF;
742
743 OPEN req_items_csr;
744 FETCH req_items_csr INTO l_req_items;
745 IF req_items_csr%FOUND THEN
746 CLOSE req_items_csr;
747 X_MESSAGE := 'GMS_REQ_EXISTS';
748 return;
749 END IF;
750 CLOSE req_items_csr;
751
752 --- S.N. Bug# 4138033
753 -- Moved this code to here as
754 -- the existense of encumbrances needs to be verified if at all there are no
755 -- other transactions for the project/task such as actuals/po/req/ etc
756 -- after the new close date.
757
758 OPEN enc_items_csr;
759 FETCH enc_items_csr INTO l_enc_items;
760 IF enc_items_csr%FOUND THEN
761 CLOSE enc_items_csr;
762 X_MESSAGE := 'GMS_ENC_EXISTS';
763 return;
764 END IF;
765 CLOSE enc_items_csr;
766
767 -- = Bug Fix 3543931
768 OPEN enc_items_csr2;
769 FETCH enc_items_csr2 INTO l_enc_items;
770 IF enc_items_csr2%FOUND THEN
771 CLOSE enc_items_csr2;
772 X_MESSAGE := 'GMS_ENC_EXISTS';
773 return;
774 END IF;
775 CLOSE enc_items_csr2;
776 --- E.N Bug# 4138033
777
778 END validate_proj_start_date;
779
780
781 Procedure validate_proj_completion_date( P_PROJECT_ID IN NUMBER,
782 P_COMPLETION_DATE IN DATE,
783 X_MESSAGE OUT NOCOPY VARCHAR2,
784 P_TASK_ID IN PA_TASKS.TASK_ID%TYPE DEFAULT NULL) IS /* Bug# 4138033 */
785
786 CURSOR budget_lines_csr IS
787 SELECT 1
788 FROM gms_budget_versions pbv,
789 gms_resource_assignments pra,
790 gms_budget_lines pbl
791 WHERE pbv.budget_version_id = pra.budget_version_id
792 AND pbv.project_id = P_PROJECT_ID
793 AND pra.resource_assignment_id = pbl.resource_assignment_id
794 AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y'))
795 AND pbl.start_date > P_COMPLETION_DATE
796 AND pbl.burdened_cost IS NOT null; /*Code change for bug 5470902 */
797
798 CURSOR exp_items_csr IS
799 SELECT 1
800 FROM DUAL
801 WHERE EXISTS
802 (SELECT 'X'
803 FROM pa_expenditure_items_all exp
804 /* pa_tasks tsk */
805 WHERE /* exp.task_id = tsk.task_id
806 AND */ exp.project_id = P_PROJECT_ID
807 AND exp.task_id = nvl(P_TASK_ID, exp.task_id)
808 AND exp.expenditure_item_date > P_COMPLETION_DATE) ;
809
810 CURSOR enc_items_csr IS
811 SELECT 1
812 FROM DUAL
813 WHERE EXISTS
814 (SELECT 'X'
815 FROM gms_encumbrance_items_all enc,
816 pa_tasks tsk
817 WHERE enc.task_id = tsk.task_id
818 AND tsk.project_id = P_PROJECT_ID
819 AND tsk.task_id = nvl(P_TASK_ID, tsk.task_id)
820 -- ==============================================================
821 -- = Bug Fix 3543931
822 -- = Award end date validations :
823 -- = Allow to change before fully liquidated encumbrances.
824 -- = ============================================================
825 AND NVL(enc.net_zero_adjustment_flag,'N') <> 'Y'
826 AND enc.encumbrance_item_date > P_COMPLETION_DATE ) ;
827
828 -- ==============================================================
829 -- = Bug Fix 3543931
830 -- = Award end date validations :
831 -- = Allow to change before fully liquidated encumbrances.
832 -- = ============================================================
833 CURSOR enc_items_csr2 IS
834 SELECT 1
835 FROM DUAL
836 WHERE EXISTS
837 (SELECT 'X'
838 FROM gms_encumbrance_items_all enc1,
839 gms_encumbrance_items_all enc2,
840 pa_tasks tsk
841 WHERE enc1.task_id = tsk.task_id
842 AND enc2.adjusted_encumbrance_item_id = enc1.encumbrance_item_id
843 AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
844 AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
845 AND enc2.enc_distributed_flag <> enc1.enc_distributed_flag
846 AND tsk.project_id = P_PROJECT_ID
847 AND tsk.task_id = nvl(P_TASK_ID, tsk.task_id)
848 AND ( enc1.encumbrance_item_date > P_COMPLETION_DATE OR
849 enc2.encumbrance_item_date > P_COMPLETION_DATE ) ) ;
850
851 CURSOR po_items_csr IS
852 SELECT 1
853 FROM DUAL
854 WHERE EXISTS
855 (SELECT 'X'
856 FROM po_distributions_all po
857 WHERE po.project_id = P_PROJECT_ID
858 AND po.task_id = nvl(P_TASK_ID, po.task_id)
859 AND po.expenditure_item_date > P_COMPLETION_DATE) ;
860
861 --Bug 3985177 : Removed pa_tasks join in po_items_csr
865 FROM DUAL
862
863 CURSOR ap_items_csr IS
864 SELECT 1
866 WHERE EXISTS
867 (SELECT 'X'
868 FROM ap_invoice_distributions_all ap
869 WHERE ap.project_id = P_PROJECT_ID
870 AND ap.task_id = nvl(P_TASK_ID, ap.task_id)
871 AND ap.expenditure_item_date > P_COMPLETION_DATE) ;
872
873 --Bug 3985177 : Removed pa_tasks join in ap_items_csr
874
875 CURSOR req_items_csr IS
876 SELECT 1
877 FROM DUAL
878 WHERE EXISTS
879 (SELECT 'X'
880 FROM po_req_distributions_all req
881 WHERE req.project_id = P_PROJECT_ID
882 AND req.task_id = nvl(P_TASK_ID, req.task_id)
883 AND req.expenditure_item_date > P_COMPLETION_DATE) ;
884
885 --Bug 3985177 : Removed pa_tasks join in req_items_csr
886
887 l_budget_lines NUMBER ;
888 l_exp_items NUMBER ;
889 l_enc_items NUMBER ;
890 l_po_items NUMBER ;
891 l_ap_items NUMBER ;
892 l_req_items NUMBER ;
893 l_document_type VARCHAR2(100) ;
894 x_err_code NUMBER;
895 x_err_stage VARCHAR2(4000);
896
897 BEGIN
898
899 l_budget_lines := 0;
900 l_exp_items := 0;
901 l_enc_items := 0;
902 l_po_items := 0;
903 l_ap_items := 0;
904 l_req_items := 0;
905 l_document_type := NULL;
906
907 IF P_TASK_ID IS NULL THEN
908 OPEN budget_lines_csr;
909 FETCH budget_lines_csr INTO l_budget_lines;
910 IF budget_lines_csr%FOUND THEN
911 CLOSE budget_lines_csr;
912 X_MESSAGE := 'GMS_BUD_EXISTS';
913 return;
914 END IF;
915 CLOSE budget_lines_csr;
916 END IF;
917
918 OPEN exp_items_csr;
919 FETCH exp_items_csr INTO l_exp_items;
920 IF exp_items_csr%FOUND THEN
921 CLOSE exp_items_csr;
922 X_MESSAGE := 'GMS_EXP_EXISTS';
923 return;
924 END IF;
925 CLOSE exp_items_csr;
926
927 OPEN po_items_csr;
928 FETCH po_items_csr INTO l_po_items;
929 IF po_items_csr%FOUND THEN
930 CLOSE po_items_csr;
931 X_MESSAGE := 'GMS_PO_EXISTS';
932 return;
933 END IF;
934 CLOSE po_items_csr;
935
936 OPEN ap_items_csr;
937 FETCH ap_items_csr INTO l_ap_items;
938 IF ap_items_csr%FOUND THEN
939 CLOSE ap_items_csr;
940 X_MESSAGE := 'GMS_AP_EXISTS';
941 return;
942 END IF;
943 CLOSE ap_items_csr;
944
945 OPEN req_items_csr;
946 FETCH req_items_csr INTO l_req_items;
947 IF req_items_csr%FOUND THEN
948 CLOSE req_items_csr;
949 X_MESSAGE := 'GMS_REQ_EXISTS';
950 return;
951 END IF;
952 CLOSE req_items_csr;
953
954 --- S.N. Bug# 4138033
955 -- Moved this code to here as
956 -- the existense of encumbrances needs to be verified if at all there are no
957 -- other transactions for the project/task such as actuals/po/req/ etc
958 -- after the new close date.
959
960 OPEN enc_items_csr;
961 FETCH enc_items_csr INTO l_enc_items;
962 IF enc_items_csr%FOUND THEN
963 CLOSE enc_items_csr;
964 X_MESSAGE := 'GMS_ENC_EXISTS';
965 return;
966 END IF;
967 CLOSE enc_items_csr;
968
969 -- = Bug Fix 3543931
970 OPEN enc_items_csr2;
971 FETCH enc_items_csr2 INTO l_enc_items;
972 IF enc_items_csr2%FOUND THEN
973 CLOSE enc_items_csr2;
974 X_MESSAGE := 'GMS_ENC_EXISTS';
975 return;
976 END IF;
977 CLOSE enc_items_csr2;
978
979 --- E.N. Bug# 4138033
980
981 END validate_Proj_completion_date;
982
983 END GMS_AWARDS_BOUNDARY_DATES_CHK;
[Home] [Help]