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