1 PACKAGE BODY PA_EVENT_CORE AS
2 /* $Header: PAEVAPCB.pls 120.5.12010000.2 2008/08/22 16:09:02 mumohan ship $ */
3
4 /* The function returns y if
5 the given project is a valid project
6 else returns N*/
7
8 FUNCTION CHECK_VALID_PROJECT(
9 P_project_num IN VARCHAR2
10 ,P_project_id OUT NOCOPY NUMBER ) RETURN VARCHAR2 IS --File.Sql.39 bug 4440895
11
12 L_PROJECT_ID NUMBER ;
13
14 CURSOR SEL_PROJ_ID
15 IS
16 SELECT project_id
17 FROM pa_projects_basic_v
18 WHERE project_number=ltrim(rtrim(P_project_num))
19 AND project_type_class_code = 'CONTRACT'
20 AND template_flag <> 'Y'
21 AND pa_project_stus_utils.Is_Project_In_Purge_Status(project_status_code) <>'Y'
22 AND nvl(cc_prvdr_flag,'N') <> 'Y';
23
24 BEGIN
25
26 OPEN SEL_PROJ_ID;
27 FETCH SEL_PROJ_ID INTO L_PROJECT_ID;
28 CLOSE SEL_PROJ_ID;
29
30 IF L_PROJECT_ID IS NULL THEN
31 RETURN('N');
32 ELSE
33 P_PROJECT_ID :=L_PROJECT_ID;
34 RETURN('Y');
35 END IF;
36 Exception
37 When others then
38 p_project_id := NULL; -- NOCOPY
39 --This user defined exception is used to track the packages and procedures
40 --involved in that flow.
41 --this user defined exception will be handled in private body which shall again
42 --raise another user defined exception which will be handled in public body.
43 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
44 --only record the package name but also the procedure involved.
45 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
46 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_PROJECT->';
47 Raise util_excp;--Raising exception to handled in public body.
48
49 END CHECK_VALID_PROJECT;
50 --------------------------------------------------------------------------------------------------------------
51
52 /*This function returns 'n' if funding is at task level but
53 the event being inserted is at project level.it returns 'y' oterwise*/
54
55 FUNCTION CHECK_FUNDING(
56 P_project_id IN NUMBER
57 ,P_TASK_ID IN NUMBER) RETURN VARCHAR2 IS
58
59 l_funding_level VARCHAR2(1);
60
61 CURSOR funding_level
62 IS
63 SELECT project_level_funding_flag
64 FROM PA_PROJECTS
65 WHERE project_id = P_project_id;
66
67 BEGIN
68
69 OPEN funding_level;
70 FETCH funding_level INTO l_funding_level;
71 CLOSE funding_level ;
72
73 IF (nvl(l_funding_level,'Y')='N' and P_TASK_ID IS NULL) THEN
74 RETURN('N');
75 ELSE
76 RETURN('Y');
77 END IF;
78 Exception
79 When others then
80 --This user defined exception is used to track the packages and procedures
81 --involved in that flow.
82 --this user defined exception will be handled in private body which shall again
83 --raise another user defined exception which will be handled in public body.
84 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
85 --only record the package name but also the procedure involved.
86 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
87 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_FUNDING->';
88 Raise util_excp;--Raising exception to handled in public body.
89
90 END CHECK_FUNDING;
91 ---------------------------------------------------------------------------------------------------------
92
93 /*it validates that the task is a top task if provided.it also
94 returns the task_id as an out parameter to be used by
95 subsequent functions*/
96
97 FUNCTION CHECK_VALID_TASK(
98 P_project_id IN NUMBER
99 ,P_task_num IN VARCHAR2
100 ,P_task_id OUT NOCOPY NUMBER) RETURN VARCHAR2 IS --File.Sql.39 bug 4440895
101
102 l_task_id number;
103
104 CURSOR GET_TASK_ID
105 IS
106 SELECT TASK_ID
107 FROM pa_tasks_top_v
108 WHERE project_id =P_project_id
109 AND task_number =ltrim(rtrim(P_task_num));
110
111 BEGIN
112
113 IF P_task_num IS NOT NULL THEN /*If task id is provided*/
114
115 OPEN get_task_id;
116 FETCH get_task_id INTO l_task_id;
117
118 IF get_task_id%FOUND THEN
119 CLOSE get_task_id;
120 P_task_id :=l_task_id;
121 RETURN ('Y');
122 ELSE
123 CLOSE get_task_id;
124 RETURN ('N');
125 END IF;/*End of GET_TASK_ID%FOUND*/
126
127 ELSE
128
129 RETURN ('Y'); /*No task id is given,so no validation is required*/
130 END IF;/*End of P_task_num IS NOT NULL*/
131 Exception
132 When others then
133 p_task_id := NULL; --NOCOPY
134 --This user defined exception is used to track the packages and procedures
135 --involved in that flow.
136 --this user defined exception will be handled in private body which shall again
137 --raise another user defined exception which will be handled in public body.
138 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
139 --only record the package name but also the procedure involved.
140 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
141 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_TASK->';
142 Raise util_excp;--Raising exception to handled in public body.
143
144
145 END CHECK_VALID_TASK;
146 -----------------------------------------------------------------------------------------------------
147
148 /*Validates that the event number is unique for the
149 project in case of project level events .in case of task level
150 events the event number should be unique for the combination
151 of project and top task*/
152
153 FUNCTION CHECK_VALID_EVENT_NUM(
154 P_project_id IN NUMBER
155 ,P_task_id IN NUMBER
156 ,P_event_num IN NUMBER) RETURN VARCHAR2 IS
157
158 l_event_num number;
159
160 CURSOR get_proj_event_num IS
161 SELECT event_num
162 FROM pa_events
163 WHERE project_id=P_project_id
164 AND task_id IS NULL
165 AND event_num=P_event_num;
166
167 CURSOR get_task_event_num IS
168 SELECT event_num
169 FROM pa_events
170 WHERE project_id=P_project_id
171 AND task_id =P_task_id
172 AND event_num=P_event_num;
173
174 BEGIN
175
176 IF (P_EVENT_NUM <=0) THEN
177 RETURN('N');
178 END IF;
179
180 IF P_task_id IS NULL THEN
181
182 OPEN get_proj_event_num;
183 FETCH get_proj_event_num into l_event_num;
184
185 IF get_proj_event_num%FOUND THEN
186 CLOSE get_proj_event_num;
187 RETURN('N');
188 ELSE
189 CLOSE get_proj_event_num;
190 RETURN('Y');
191 END IF;
192
193 ELSE /*P_task_id IS NOT NULL*/
194
195 OPEN get_task_event_num;
196 FETCH get_task_event_num into l_event_num;
197
198 IF get_task_event_num%found THEN
199 CLOSE get_task_event_num;
200 RETURN('N');
201 ELSE
202 CLOSE get_task_event_num;
203 RETURN('Y');
204 END IF;
205
206 END IF;/*End of P_task_id IS NULL*/
207 Exception
208 When others then
209 --This user defined exception is used to track the packages and procedures
210 --involved in that flow.
211 --this user defined exception will be handled in private body which shall again
212 --raise another user defined exception which will be handled in public body.
213 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
214 --only record the package name but also the procedure involved.
215 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
216 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_EVENT_NUM->';
217 Raise util_excp;--Raising exception to handled in public body.
218
219
220 END CHECK_VALID_EVENT_NUM;
221 -------------------------------------------------------------------------------------------------
222
223 /*This function checks that
224 the event is of a valid event
225 type.it passes the event type
226 classification as an out parameter*/
227
228 FUNCTION CHECK_VALID_EVENT_TYPE(
229 P_event_type IN VARCHAR2
230 ,P_context IN VARCHAR2
231 ,P_event_type_classification OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS --File.Sql.39 bug 4440895
232
233 l_event_type_classification PA_EVENT_TYPES.EVENT_TYPE_CLASSIFICATION %TYPE;
234
235 CURSOR valid_event
236 IS
237 SELECT event_type_classification
238 FROM pa_event_types_lov_v
239 WHERE event_type=P_event_type;
240
241 CURSOR valid_delv_event
242 IS
243 SELECT event_type_classification
244 FROM pa_event_types_lov_v
245 WHERE event_type=P_event_type
246 AND event_type_classification = 'MANUAL';
247
248 BEGIN
249
250 IF P_context = 'D' Then
251 OPEN VALID_DELV_EVENT;
252 FETCH VALID_DELV_EVENT INTO l_event_type_classification;
253
254 IF VALID_DELV_EVENT%FOUND THEN
255 P_event_type_classification :=L_event_type_classification ;
256 CLOSE VALID_DELV_EVENT;
257 RETURN('Y');
258 ELSE
259 CLOSE VALID_DELV_EVENT;
260 RETURN('N');
261 END IF;
262 ELSE
263 OPEN VALID_EVENT;
264 FETCH VALID_EVENT INTO l_event_type_classification;
265
266 IF VALID_EVENT%FOUND THEN
267 P_event_type_classification :=L_event_type_classification ;
268 CLOSE VALID_EVENT;
269 RETURN('Y');
270 ELSE
271 CLOSE VALID_EVENT;
272 RETURN('N');
273 END IF;
274 END IF;
275 Exception
276 When others then
277 p_event_type_classification := NULL; --NOCOPY
278 --This user defined exception is used to track the packages and procedures
279 --involved in that flow.
280 --this user defined exception will be handled in private body which shall again
281 --raise another user defined exception which will be handled in public body.
282 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
283 --only record the package name but also the procedure involved.
284 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
285 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_EVENT_TYPE->';
286 Raise util_excp;--Raising exception to handled in public body.
287
288
289 END CHECK_VALID_EVENT_TYPE;
290 ---------------------------------------------------------------------------------------------------
291
292 /*It validates that the event organization
293 is an active and valid one*/
294
295 FUNCTION CHECK_VALID_EVENT_ORG(
296 P_event_org_name IN VARCHAR2
297 ,P_event_org_id OUT NOCOPY NUMBER) RETURN VARCHAR2 IS --File.Sql.39 bug 4440895
298
299 l_event_org_id NUMBER;
300
301 CURSOR valid_event_org IS
302 SELECT organization_id
303 FROM pa_organizations_event_v
304 WHERE name=P_event_org_name
305 AND TRUNC(SYSDATE) BETWEEN date_from AND nvl(date_to, TRUNC(SYSDATE));
306
307 BEGIN
308
309 OPEN valid_event_org;
310 FETCH valid_event_org INTO l_event_org_id;
311 CLOSE valid_event_org;
312
313 IF l_event_org_id IS NULL THEN
314 RETURN('N');
315 ELSE
316 P_event_org_id:=l_event_org_id;
317 RETURN('Y');
318 END IF;
319 Exception
320 When others then
321 p_event_org_id := NULL; --NOCOPY
322 --This user defined exception is used to track the packages and procedures
323 --involved in that flow.
324 --this user defined exception will be handled in private body which shall again
325 --raise another user defined exception which will be handled in public body.
326 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
327 --only record the package name but also the procedure involved.
328 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
329 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_EVENT_ORG->';
330 Raise util_excp;--Raising exception to handled in public body.
331
332
333 END CHECK_VALID_EVENT_ORG;
334 -------------------------------------------------------------------------------------------------------
335
336 FUNCTION CHECK_VALID_CURR(
337 P_bill_trans_curr IN VARCHAR2) RETURN VARCHAR2 IS
338
339 l_valid_bill_trans_code Pa_events.bill_trans_currency_code%TYPE;
340
341 CURSOR VALID_CURR
342 IS
343 SELECT 1
344 FROM fnd_currencies /* Changed vl into base for bug 4403197*/
345 WHERE nvl(enabled_flag, 'Y') = 'Y'
346 AND trunc(sysdate)
347 BETWEEN DECODE(TRUNC(start_date_active), null, TRUNC(SYSDATE), trunc(start_date_active))
348 AND decode (trunc(end_date_active), null, trunc(sysdate), trunc(end_date_active))
349 AND currency_code=p_bill_trans_curr;
350
351 BEGIN
352
353 OPEN VALID_CURR;
354 FETCH VALID_CURR INTO l_valid_bill_trans_code;
355
356 IF VALID_CURR%FOUND THEN
357 CLOSE VALID_CURR;
358 RETURN('Y');
359 ELSE
360 CLOSE VALID_CURR;
361 RETURN('N');
362 END IF;
363 Exception
364 When others then
365 --This user defined exception is used to track the packages and procedures
366 --involved in that flow.
367 --this user defined exception will be handled in private body which shall again
368 --raise another user defined exception which will be handled in public body.
369 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
370 --only record the package name but also the procedure involved.
371 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
372 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_CURR->';
373 Raise util_excp;--Raising exception to handled in public body.
374
375
376 END CHECK_VALID_CURR;
377 -------------------------------------------------------------------------------------------------------------
378
379 FUNCTION CHECK_VALID_FUND_RATE_TYPE(
383
380 P_fund_rate_type IN VARCHAR2,
381 x_fund_rate_type OUT NOCOPY VARCHAR2 -- Added for bug 3009307 --File.Sql.39 bug 4440895
382 ) RETURN VARCHAR2 IS
384 -- dummy number; Commented for bug 3009307
385 CURSOR FUND_RATE_TYPE
386 IS
387 -- Commented for bug 3009307 SELECT 1
388 SELECT conversion_type -- Added for bug 3009307
389 FROM pa_conversion_types_v
390 WHERE user_conversion_type = P_fund_rate_type;
391
392 BEGIN
393
394 OPEN FUND_RATE_TYPE;
395 -- Commented for bug 3009307 FETCH FUND_RATE_TYPE INTO dummy;
396 FETCH fund_rate_type
397 INTO x_fund_rate_type;
398
399 IF FUND_RATE_TYPE%FOUND THEN
400 CLOSE FUND_RATE_TYPE;
401 RETURN('Y');
402 ELSE
403 CLOSE FUND_RATE_TYPE;
404 RETURN('N');
405 END IF;
406 Exception
407 When others then
408 x_fund_rate_type := NULL; --NOCOPY
409 --This user defined exception is used to track the packages and procedures
410 --involved in that flow.
411 --this user defined exception will be handled in private body which shall again
412 --raise another user defined exception which will be handled in public body.
413 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
414 --only record the package name but also the procedure involved.
415 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
416 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_FUND_RATE_TYPE->';
417 Raise util_excp;--Raising exception to handled in public body.
418
419
420 END CHECK_VALID_FUND_RATE_TYPE;
421 --------------------------------------------------------------------------------------------------
422
423 /*It checks that the rate type
424 provided is a valid one*/
425
426 FUNCTION CHECK_VALID_PROJ_RATE_TYPE(
427 P_proj_rate_type IN VARCHAR2
428 ,P_bill_trans_currency_code IN VARCHAR2
429 ,P_project_currency_code IN VARCHAR2
430 ,P_proj_level_rt_dt_cod IN VARCHAR2
431 ,P_project_rate_date IN DATE
432 ,P_event_date IN DATE
433 ,x_proj_rate_type OUT NOCOPY VARCHAR2 -- Added for bug 3009307 --File.Sql.39 bug 4440895
434 ) RETURN VARCHAR2 IS
435
436 -- dummy number; Commented for bug 3009307
437
438 CURSOR PROJ_RATE_TYPE
439 IS
440 -- Commented for bug 3009307 SELECT 1
441 SELECT conversion_type
442 FROM pa_conversion_types_v
443 WHERE conversion_type <>'User'
444 AND (pa_multi_currency.is_user_rate_type_allowed(
445 p_bill_trans_currency_code,
446 p_project_currency_code,
447 decode(p_proj_level_rt_dt_cod, 'PA_INVOICE_DATE',
448 nvl(p_project_rate_date, p_event_date),
449 'FIXED_DATE', p_project_rate_date))= 'N')
450 AND user_conversion_type=P_proj_rate_type
451 UNION ALL
452 -- Commented for bug 3009307 SELECT 1
453 SELECT conversion_type
454 FROM pa_conversion_types_v
455 WHERE pa_multi_currency.is_user_rate_type_allowed(
456 p_bill_trans_currency_code,
457 p_project_currency_code,
458 decode(p_proj_level_rt_dt_cod, 'PA_INVOICE_DATE',
459 nvl(p_project_rate_date, p_event_date),
460 'FIXED_DATE', p_project_rate_date))= 'Y'
461 AND user_conversion_type=P_proj_rate_type;
462
463 BEGIN
464
465 OPEN proj_rate_type;
466 -- Commented for bug 3009307 FETCH proj_rate_type INTO dummy;
467 FETCH proj_rate_type
468 INTO x_proj_rate_type;
469
470 IF proj_rate_type%FOUND THEN
471 CLOSE proj_rate_type;
472 RETURN('Y');
473 ELSE
474 CLOSE proj_rate_type;
475 RETURN('N');
476 END IF;
477 Exception
478 When others then
479 x_proj_rate_type := NULL; -- NOCOPY
480 --This user defined exception is used to track the packages and procedures
481 --involved in that flow.
482 --this user defined exception will be handled in private body which shall again
483 --raise another user defined exception which will be handled in public body.
484 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
485 --only record the package name but also the procedure involved.
486 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
487 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_PROJ_RATE_TYPE->';
488 Raise util_excp;--Raising exception to handled in public body.
489
490
491 END CHECK_VALID_PROJ_RATE_TYPE;
492
493 --------------------------------------------------------------------------------------------
494 FUNCTION CHECK_VALID_PFC_RATE_TYPE(
495 P_pfc_rate_type IN VARCHAR2
496 ,P_bill_trans_currency_code IN VARCHAR2
497 ,P_proj_func_currency_code IN VARCHAR2
498 ,P_proj_level_func_rt_dt_cod IN VARCHAR2
499 ,P_proj_func_rate_date IN DATE
500 ,P_event_date IN DATE
501 ,x_pfc_rate_type OUT NOCOPY VARCHAR2 -- Added for bug 3009307 --File.Sql.39 bug 4440895
505
502 ) RETURN VARCHAR2 IS
503
504 -- Commented for bug 3009307 dummy number;
506 CURSOR PFC_RATE_TYPE
507 IS
508 -- Commented for bug 3009307 SELECT 1
509 SELECT conversion_type
510 FROM pa_conversion_types_v
511 WHERE conversion_type <>'User'
512 AND (pa_multi_currency.is_user_rate_type_allowed(
513 p_bill_trans_currency_code,
514 p_proj_func_currency_code,
515 decode(p_proj_level_func_rt_dt_cod, 'PA_INVOICE_DATE',
516 nvl(p_proj_func_rate_date, p_event_date),
517 'FIXED_DATE', p_proj_func_rate_date))= 'N')
518 AND user_conversion_type=P_pfc_rate_type
519 UNION ALL
520 -- Commented for bug 3009307 SELECT 1
521 SELECT conversion_type
522 FROM pa_conversion_types_v
523 WHERE pa_multi_currency.is_user_rate_type_allowed(
524 p_bill_trans_currency_code,
525 p_proj_func_currency_code,
526 decode(p_proj_level_func_rt_dt_cod,
527 'PA_INVOICE_DATE', nvl(p_proj_func_rate_date, p_event_date),
528 'FIXED_DATE', p_proj_func_rate_date))= 'Y'
529 AND user_conversion_type=P_pfc_rate_type;
530 BEGIN
531
532 OPEN pfc_rate_type;
533 -- Commented for bug 3009307 FETCH PFC_RATE_TYPE INTO dummy;
534 FETCH pfc_rate_type INTO x_pfc_rate_type;
535
536 IF PFC_RATE_TYPE%FOUND THEN
537 CLOSE PFC_RATE_TYPE;
538 RETURN('Y');
539 ELSE
540 CLOSE PFC_RATE_TYPE;
541 RETURN('N');
542 END IF;
543 Exception
544 When others then
545 x_pfc_rate_type := NULL; --NOCOPY
546 --This user defined exception is used to track the packages and procedures
547 --involved in that flow.
548 --this user defined exception will be handled in private body which shall again
549 --raise another user defined exception which will be handled in public body.
550 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
551 --only record the package name but also the procedure involved.
552 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
553 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_PFC_RATE_TYPE->';
554 Raise util_excp;--Raising exception to handled in public body.
555
556
557 END CHECK_VALID_PFC_RATE_TYPE;
558
559
560 ---------------------------------------------------------------------------------------------------------
561
562 FUNCTION CHECK_VALID_BILL_AMT(
563 P_event_type_classification IN VARCHAR2
564 ,P_bill_amt IN NUMBER) RETURN VARCHAR2 IS
565
566 BEGIN
567
568 IF P_event_type_classification IN ('DEFERRED REVENUE','INVOICE REDUCTION','SCHEDULED PAYMENTS') THEN
569 IF NVL(P_bill_amt,-1)>0 THEN
570 RETURN('Y');
571 ELSE
572 RETURN('N');
573 END IF;
574 END IF;
575
576 RETURN('Y');
577 Exception
578 When others then
579 --This user defined exception is used to track the packages and procedures
580 --involved in that flow.
581 --this user defined exception will be handled in private body which shall again
582 --raise another user defined exception which will be handled in public body.
583 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
584 --only record the package name but also the procedure involved.
585 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
586 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_BILL_AMT->';
587 Raise util_excp;--Raising exception to handled in public body.
588
589
590 END CHECK_VALID_BILL_AMT;
591 -------------------------------------------------------------------------------------------------------------
592
593 /*Validates the revenue
594 amount for revenue events*/
595
596 FUNCTION CHECK_VALID_REV_AMT(
597 P_event_type_classification IN VARCHAR2
598 ,P_rev_amt IN NUMBER) RETURN VARCHAR2 IS
599
600 BEGIN
601
602 IF P_event_type_classification IN ('WRITE OFF','WRITE ON') THEN
603 IF NVL(P_rev_amt,-1)>0 THEN
604 RETURN('Y');
605 ELSE
606 RETURN('N');
607 END IF;
608 END IF;
609 RETURN('Y');/*Not a revenue event*/
610
611 Exception
612 When others then
613 --This user defined exception is used to track the packages and procedures
614 --involved in that flow.
615 --this user defined exception will be handled in private body which shall again
616 --raise another user defined exception which will be handled in public body.
617 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
618 --only record the package name but also the procedure involved.
619 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
620 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_REV_AMT->';
621 Raise util_excp;--Raising exception to handled in public body.
622
623
624 END CHECK_VALID_REV_AMT;
628 /*This function checks if the
625 -------------------------------------------------------------------------------------------------------------
626
627
629 event has been processed i.e
630 either revenue generated or billed.
631 if the event has been processed it returns
632 'N' ,if the event is partially billed it returns 'P'.
633 If invoice was generated and then cancelled it returns 'C'.
634 If the event has never been processed it returns 'Y' */
635
636 FUNCTION CHECK_EVENT_PROCESSED(
637 P_event_id IN NUMBER) RETURN VARCHAR2 IS
638
639 CURSOR EVENT_PROCESSED IS
640 SELECT REVENUE_DISTRIBUTED_FLAG,BILLED_FLAG
641 FROM PA_EVENTS
642 WHERE EVENT_ID=P_event_id;
643
644 CURSOR EVENT_BILL_AMOUNT IS
645 SELECT SUM(NVL(AMOUNT,0))
646 FROM PA_DRAFT_INVOICE_ITEMS DI,PA_EVENTS EV,PA_DRAFT_INVOICES_ALL DIA
647 WHERE DI.PROJECT_ID=EV.PROJECT_ID
648 AND nvl(DI.TASK_ID,-1) =nvl(EV.TASK_ID,-1)
649 AND DI.EVENT_NUM = EV.EVENT_NUM
650 AND DI.PROJECT_ID=DIA.PROJECT_ID
651 AND DI.DRAFT_INVOICE_NUM=DIA.DRAFT_INVOICE_NUM
652 AND NVL(DIA.WRITE_OFF_FLAG,'N')<>'Y'
653 AND EV.EVENT_ID = P_event_id;
654
655 l_rev_flag VARCHAR2(1);
656 l_billed_flag VARCHAR2(1);
657 L_BILL_AMOUNT pa_draft_invoice_items.amount%type;
658 l_invoiced_flag VARCHAR2(1):= 'N';
659 BEGIN
660
661 OPEN EVENT_PROCESSED;
662 FETCH EVENT_PROCESSED INTO l_rev_flag,l_billed_flag;
663 CLOSE EVENT_PROCESSED;
664
665 IF (NVL(l_rev_flag,'N')='Y' AND NVL(l_billed_flag,'N')='Y') THEN /*The event has been processed */
666 RETURN('N');
667 ELSIF (NVL(l_billed_flag,'N')='Y') THEN
668 l_invoiced_flag := 'Y';
669 END IF;
670
671 IF l_invoiced_flag = 'N' THEN
672
673 DECLARE
674 dummy NUMBER;
675 BEGIN
676
677 SELECT 1
678 INTO dummy
679 FROM DUAL
680 WHERE EXISTS ( SELECT NULL
681 FROM PA_DRAFT_INVOICE_ITEMS DI,PA_EVENTS EV
682 WHERE DI.project_id=EV.project_id
683 AND nvl(DI.TASK_ID,-1) =nvl(EV.TASK_ID,-1)
684 AND DI.EVENT_NUM = EV.EVENT_NUM
685 AND EV.EVENT_ID = P_event_id);
686
687 OPEN EVENT_BILL_AMOUNT;
688 FETCH EVENT_BILL_AMOUNT INTO L_BILL_AMOUNT;
689
690 IF L_BILL_AMOUNT <> 0 THEN /*The event has been partially billed */
691 CLOSE EVENT_BILL_AMOUNT;
692 RETURN('P');
693 ELSE /*The invoice for the project has been cancelled */
694
695 CLOSE EVENT_BILL_AMOUNT;
696 RETURN('C');
697 END IF;
698
699 EXCEPTION
700 WHEN NO_DATA_FOUND THEN /*The event has not been billed */
701 l_invoiced_flag := 'N';
702 END;
703
704 END IF;
705
706 /* Code added and modified for bug 7110782 - starts */
707
708 /* Both invoiced and revenue distributed, event has been processed.
709 No update will be allowed */
710 IF l_invoiced_flag = 'Y' AND nvl(l_rev_flag, 'N') = 'Y' THEN
711 RETURN('N');
712 END IF;
713
714 /* Only invoiced and not revenue distributed.
715 Only update of bill_trans_rev_amount will be allowed */
716 IF l_invoiced_flag = 'Y' AND nvl(l_rev_flag, 'N') = 'N' THEN
717 RETURN('I');
718 END IF;
719
720 /* Only revenue distributed but not invoiced
721 Only update of bill_trans_bill_amount and bill_hold_flag will be allowed */
722 IF l_invoiced_flag = 'N' AND nvl(l_rev_flag, 'N') = 'Y' THEN
723 RETURN('R');
724 END IF;
725
726 /* Neither revenue distributed nor invoiced.Event is not processed. */
727 IF l_invoiced_flag = 'N' AND nvl(l_rev_flag, 'N') = 'N' THEN
728 RETURN('Y');
729 END IF;
730
731 /* Code added and modified for bug 7110782 - ends */
732
733 Exception
734 When others then
735 --This user defined exception is used to track the packages and procedures
736 --involved in that flow.
737 --this user defined exception will be handled in private body which shall again
738 --raise another user defined exception which will be handled in public body.
739 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
740 --only record the package name but also the procedure involved.
741 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
742 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_EVENT_PROCESSED->';
743 Raise util_excp;--Raising exception to handled in public body.
744
745
746 END CHECK_EVENT_PROCESSED;
747 -------------------------------------------------------------------------------------------------------
748
749 /*This function checks if the
750 organization provided is a valid one
751 returns Y if valid N otherwise*/
752
753 FUNCTION CHECK_VALID_INV_ORG(
754 P_inv_org_name IN VARCHAR2,
755 P_inv_org_id OUT NOCOPY NUMBER) RETURN VARCHAR2 IS --File.Sql.39 bug 4440895
756
757 l NUMBER;
758
759 CURSOR VALID_INV_ORG
760 IS
761 SELECT HOU.organization_id
762 FROM PA_IMPLEMENTATIONS I,HR_ORGANIZATION_UNITS HOU
766 BEGIN
763 WHERE HOU.BUSINESS_GROUP_ID=I.BUSINESS_GROUP_ID
764 AND HOU.NAME=P_inv_org_name;
765
767
768 OPEN VALID_INV_ORG;
769 FETCH VALID_INV_ORG INTO l;
770
771 IF VALID_INV_ORG%FOUND THEN
772 CLOSE VALID_INV_ORG;
773 P_inv_org_id :=l;
774 RETURN('Y');
775 ELSE
776 CLOSE VALID_INV_ORG;
777 RETURN('N');
778 END IF;
779
780 Exception
781 When others then
782 p_inv_org_id := NULL; -- NOCOPY
783 --This user defined exception is used to track the packages and procedures
784 --involved in that flow.
785 --this user defined exception will be handled in private body which shall again
786 --raise another user defined exception which will be handled in public body.
787 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
788 --only record the package name but also the procedure involved.
789 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
790 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_INV_ORG->';
791 Raise util_excp;--Raising exception to handled in public body.
792
793
794 END CHECK_VALID_INV_ORG;
795 ----------------------------------------------------------------------------------
796 FUNCTION CHECK_VALID_INV_ITEM(
797 P_inv_item_id IN NUMBER) RETURN VARCHAR2 IS
798
799 dummy number;
800
801 CURSOR VALID_INV_ITEM
802 IS
803 SELECT 1
804 FROM mtl_item_flexfields
805 WHERE item_id=P_inv_item_id
806 AND trunc(sysdate)
807 BETWEEN decode(trunc(start_date_active), null, trunc(sysdate), trunc(start_date_active))
808 AND decode (trunc(end_date_active), null, trunc(sysdate), trunc(end_date_active));
809
810 BEGIN
811
812 OPEN VALID_INV_ITEM;
813 FETCH VALID_INV_ITEM INTO dummy;
814
815 IF ( VALID_INV_ITEM%FOUND) THEN
816 CLOSE VALID_INV_ITEM;
817 RETURN('Y');
818 ELSE
819 CLOSE VALID_INV_ITEM;
820 RETURN('N');
821 END IF;
822
823 Exception
824 When others then
825 --This user defined exception is used to track the packages and procedures
826 --involved in that flow.
827 --this user defined exception will be handled in private body which shall again
828 --raise another user defined exception which will be handled in public body.
829 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
830 --only record the package name but also the procedure involved.
831 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
832 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_INV_ITEM->';
833 Raise util_excp;--Raising exception to handled in public body.
834
835
836 END CHECK_VALID_INV_ITEM;
837 --------------------------------------------------------------------------------------------------------------------------
838
839 /*THIs function validates the revenue amount for write-off events.
840 It gets the total accrued amount(1) and total invoiced amount(2) in
841 projfunc currency.next it converst the revenue amount of unprocessed
842 write-off events to projfunc currency(3).write-off events can be
843 entered only if revenue amount(in projfunc currency) is <(1-2-3).*/
844
845 FUNCTION CHECK_WRITE_OFF_AMT(
846 P_project_id IN NUMBER
847 ,P_task_id IN NUMBER
848 ,P_event_id IN NUMBER
849 ,P_rev_amt IN NUMBER
850 ,P_bill_trans_currency IN VARCHAR2
851 ,P_proj_func_currency IN VARCHAR2
852 ,P_proj_func_rate_type IN VARCHAR2
853 ,P_proj_func_rate IN NUMBER
854 ,P_proj_func_rate_date IN DATE
855 ,P_event_date IN DATE )RETURN VARCHAR2 IS
856
857
858 CURSOR proj_rev_bill_amount
859 IS
860 SELECT SUM(NVL(projfunc_accrued_amount,0)),SUM(NVL(projfunc_billed_amount,0))
861 FROM pa_summary_project_fundings
862 WHERE project_id= P_project_id
863 AND task_id IS NULL;
864
865 CURSOR task_rev_bill_amount
866 IS
867 SELECT SUM(NVL(projfunc_accrued_amount,0)),SUM(NVL(projfunc_billed_amount,0))
868 FROM pa_summary_project_fundings
869 WHERE project_id= P_project_id
870 AND task_id=P_task_id;
871 /*This is commented for performance reason*/
872 /* CURSOR PROJ_WRITE_OFF_AMOUNT
873 IS
874 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
875 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,event_date
876 FROM PA_EVENTS_V
877 WHERE event_type_classification='WRITE OFF'
878 AND NVL(revenue_distributed_flag,'N')='N'
879 AND event_date IS NOT NULL
880 AND NVL(event_id,-1)<>NVL(P_event_id,-2)
881 AND project_id= P_project_id; */
882
883 /* CURSOR TASK_WRITE_OFF_AMOUNT
884 IS
885 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
886 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,event_date
887 FROM PA_EVENTS_V
888 WHERE event_type_classification='WRITE OFF'
889 AND NVL(REVENUE_DISTRIBUTED_FLAG,'N')='N'
893 AND task_id=P_task_id;*/
890 AND event_date IS NOT NULL
891 AND project_id= P_project_id
892 AND NVL(event_id,-1)<>NVL(P_event_id,-2)
894
895 /* Commented the below and added new for perf bug 3604238
896 CURSOR proj_write_off_amount
897 IS
898 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
899 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,completion_date
900 FROM PA_EVENTS EV,PA_EVENT_TYPES EVT
901 WHERE EVT.event_type_classification='WRITE OFF'
902 AND EVT.event_type=EV.event_type
903 AND NVL(EV.revenue_distributed_flag,'N')='N'
904 AND EV.completion_date IS NOT NULL
905 AND NVL(EV.event_id,-1)<>NVL(P_event_id,-2)
906 AND EV.project_id= P_project_id; */
907
908 CURSOR proj_write_off_amount
909 IS
910 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
911 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,completion_date
912 FROM PA_EVENTS EV
913 WHERE EV.revenue_distributed_flag ='N'
914 AND EV.completion_date IS NOT NULL
915 AND NVL(EV.event_id,-1)<>NVL(P_event_id,-2)
916 AND exists (select 1 from PA_EVENT_TYPES EVT
917 where EVT.event_type_classification='WRITE OFF'
918 and EVT.event_type=EV.event_type )
919 AND EV.project_id= P_project_id;
920
921 CURSOR task_write_off_amount
922 IS
923 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
924 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,completion_date
925 FROM PA_EVENTS EV ,PA_EVENT_TYPES EVT
926 WHERE EVT.event_type_classification='WRITE OFF'
927 AND EVT.event_type=EV.event_type
928 AND NVL(EV.revenue_distributed_flag,'N')='N'
929 AND EV.completion_date IS NOT NULL
930 AND EV.project_id= P_project_id
931 AND NVL(EV.event_id,-1)<>NVL(P_event_id,-2)
932 AND EV.task_id=P_task_id;
933
934 l_accrued_amount NUMBER;
935 l_billed_amount NUMBER;
936 l_bill_trans_amount NUMBER;
937 l_bill_trans_currency_code PA_EVENTS.bill_trans_currency_code%TYPE; /*VARCHAR2(2000);*/
938 l_projfunc_currency_code PA_EVENTS.projfunc_currency_code%TYPE; /*VARCHAR2(2000);*/
939 l_projfunc_rate_type PA_EVENTS.projfunc_rate_type%TYPE; /*VARCHAR2(2000);*/
940 l_projfunc_rate_date DATE;
941 l_projfunc_exchange_rate NUMBER;
942 l_event_date DATE;
943 l_conv_date DATE;
944 l_projfunc_rev_amt NUMBER;
945 l_denominator NUMBER;
946 l_numerator NUMBER;
947 l_status VARCHAR2(2000);
948 l_sum_revenue NUMBER :=0;
949
950 BEGIN
951
952 /*l_sum_revenue gets the sum of revenue amounts of all unprocessed WRITE OFF events
953 in project functional currency.*/
954 IF P_task_id IS NULL THEN /*project level event is being inserted So check for only project funding*/
955 OPEN proj_rev_bill_amount;
956 FETCH proj_rev_bill_amount
957 INTO l_accrued_amount,l_billed_amount;
958
959 IF proj_rev_bill_amount%NOTFOUND THEN
960 CLOSE proj_rev_bill_amount;
961 -- RETURN('Y'); /*there is no funding*/
962 RETURN('N'); /*there is no funding*/
963
964 ELSE /*of proj_rev_bill_amount%NOTFOUND*/
965 OPEN proj_write_off_amount;
966
967 LOOP
968
969 FETCH proj_write_off_amount
970 INTO l_bill_trans_amount,l_bill_trans_currency_code,
971 l_projfunc_currency_code,l_projfunc_rate_type,
972 l_projfunc_rate_date,l_projfunc_exchange_rate,l_event_date;
973
974 EXIT WHEN proj_write_off_amount%NOTFOUND;
975
976 l_conv_date := NVL(l_projfunc_rate_date,l_event_date);
977 /* Calling convert amount proc to convert this amount in PFC */
978 PA_MULTI_CURRENCY.convert_amount(
979 P_FROM_CURRENCY => l_bill_trans_currency_code,
980 P_TO_CURRENCY => l_projfunc_currency_code,
981 P_CONVERSION_DATE => l_conv_date,
982 P_CONVERSION_TYPE => l_projfunc_rate_type,
983 P_AMOUNT => l_bill_trans_amount,
984 P_USER_VALIDATE_FLAG => 'Y',
985 P_HANDLE_EXCEPTION_FLAG => 'Y',
986 P_CONVERTED_AMOUNT => l_projfunc_rev_amt,
987 P_DENOMINATOR => l_denominator,
988 P_NUMERATOR => l_numerator,
989 P_RATE => l_projfunc_exchange_rate,
990 X_STATUS => l_status);
991 IF l_status IS NOT NULL THEN
992 CLOSE proj_write_off_amount;
993 CLOSE proj_rev_bill_amount;
994 RETURN(l_status);
995 ELSE
999 END IF;
996 l_sum_revenue :=NVL(l_sum_revenue,0)+NVL(l_projfunc_rev_amt,0);
997 /*This gives the total revenue amount of unprocessed
998 write-off events in project functional currency*/
1000 END LOOP;
1001 CLOSE proj_write_off_amount;
1002 CLOSE proj_rev_bill_amount;
1003 END IF; /*proj_rev_bill_amount%NOTFOUND*/
1004
1005 ELSE /*p_task_id NOT NULL*/
1006 /*Task level event is being inserted .So we have to check both
1007 project as well as task level funding*/
1008
1009 OPEN proj_rev_bill_amount;
1010 FETCH proj_rev_bill_amount
1011 INTO l_accrued_amount,l_billed_amount;
1012
1013 IF proj_rev_bill_amount%FOUND THEN /*There is project level funding*/
1014 OPEN proj_write_off_amount;
1015 LOOP
1016
1017 FETCH proj_write_off_amount
1018 INTO l_bill_trans_amount,l_bill_trans_currency_code,l_projfunc_currency_code,
1019 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate,
1020 l_event_date;
1021
1022 EXIT WHEN proj_write_off_amount%NOTFOUND;
1023
1024 l_conv_date := NVL(l_projfunc_rate_date,l_event_date);
1025 /* Calling convert amount proc to convert this amount in PFC */
1026 PA_MULTI_CURRENCY.convert_amount(
1027 P_FROM_CURRENCY => l_bill_trans_currency_code,
1028 P_TO_CURRENCY => l_projfunc_currency_code,
1029 P_CONVERSION_DATE => l_conv_date,
1030 P_CONVERSION_TYPE => l_projfunc_rate_type,
1031 P_AMOUNT => l_bill_trans_amount,
1032 P_USER_VALIDATE_FLAG => 'Y',
1033 P_HANDLE_EXCEPTION_FLAG => 'Y',
1034 P_CONVERTED_AMOUNT => l_projfunc_rev_amt,
1035 P_DENOMINATOR => l_denominator,
1036 P_NUMERATOR => l_numerator,
1037 P_RATE => l_projfunc_exchange_rate,
1038 X_STATUS => l_status);
1039 IF l_status IS NOT NULL THEN
1040 CLOSE proj_rev_bill_amount;
1041 CLOSE proj_write_off_amount;
1042 RETURN(l_status);
1043 ELSE
1044 l_sum_revenue :=NVL(l_sum_revenue,0)+NVL(l_projfunc_rev_amt,0);
1045 /*This gives the total revenue amount of unprocessed
1046 write-off events*/
1047 END IF;
1048 END LOOP;
1049 CLOSE proj_write_off_amount;
1050 CLOSE proj_rev_bill_amount;
1051
1052 ELSE /*proj_rev_bill_amount%FOUND*/
1053 CLOSE proj_rev_bill_amount;/*Close the cusrsor as it won't be used any more*/
1054
1055 OPEN task_rev_bill_amount ;
1056 FETCH task_rev_bill_amount
1057 INTO l_accrued_amount,l_billed_amount;
1058
1059 IF task_rev_bill_amount%NOTFOUND THEN
1060 CLOSE proj_rev_bill_amount;
1061 CLOSE task_rev_bill_amount;
1062 -- RETURN('Y'); /*there is no funding*/
1063 RETURN('N'); /*there is no funding*/
1064
1065 ELSE /*else of task_rev_bill_amount%NOTFOUND*/
1066
1067 OPEN task_write_off_amount;
1068 LOOP
1069
1070 FETCH task_write_off_amount
1071 INTO l_bill_trans_amount,l_bill_trans_currency_code,l_projfunc_currency_code,
1072 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate,l_event_date;
1073
1074 EXIT WHEN task_write_off_amount%NOTFOUND;
1075
1076 l_conv_date := NVL(l_projfunc_rate_date,l_event_date);
1077 /* Calling convert amount proc to convert this amount in PFC */
1078 PA_MULTI_CURRENCY.convert_amount(
1079 P_FROM_CURRENCY => l_bill_trans_currency_code,
1080 P_TO_CURRENCY => l_projfunc_currency_code,
1081 P_CONVERSION_DATE => l_conv_date,
1082 P_CONVERSION_TYPE => l_projfunc_rate_type,
1083 P_AMOUNT => l_bill_trans_amount,
1084 P_USER_VALIDATE_FLAG => 'Y',
1085 P_HANDLE_EXCEPTION_FLAG => 'Y',
1086 P_CONVERTED_AMOUNT => l_projfunc_rev_amt,
1087 P_DENOMINATOR => l_denominator,
1088 P_NUMERATOR => l_numerator,
1089 P_RATE => l_projfunc_exchange_rate,
1090 X_STATUS => l_status);
1091 IF l_status IS NOT NULL THEN
1092 CLOSE task_rev_bill_amount;
1093 CLOSE task_write_off_amount;
1094 RETURN(l_status);
1095 ELSE
1096 l_sum_revenue :=NVL(l_sum_revenue,0)+NVL(l_projfunc_rev_amt,0);
1097 /*This gives the total revenue amount of unprocessed
1098 write-off events*/
1102 CLOSE task_rev_bill_amount;
1099 END IF;
1100 END LOOP;
1101 CLOSE task_write_off_amount;
1103 END IF; /*END OF task_rev_bill_amount%NOTFOUND*/
1104 END IF; /*proj_rev_bill_amount%FOUND*/
1105 END IF;/*p_task_id NOT NULL*/
1106 /*END OF CALCULATION OF l_sum_revenue*/
1107
1108 /*Copying the input parameter into local variables*/
1109 l_bill_trans_amount :=P_rev_amt;
1110 l_bill_trans_currency_code := P_bill_trans_currency;
1111 l_projfunc_currency_code := P_proj_func_currency;
1112 l_projfunc_rate_type := P_proj_func_rate_type;
1113 l_projfunc_exchange_rate := P_proj_func_rate;
1114 l_projfunc_rate_date := P_proj_func_rate_date;
1115 l_event_date := P_event_date;
1116 l_projfunc_rev_amt := 0.00;
1117 /*Next convert the revenue amount of the event into projfunc currency*/
1118 l_conv_date := NVL(l_projfunc_rate_date,l_event_date);
1119 /* Calling convert amount proc to convert this amount in PFC */
1120 PA_MULTI_CURRENCY.convert_amount(
1121 P_FROM_CURRENCY => l_bill_trans_currency_code,
1122 P_TO_CURRENCY => l_projfunc_currency_code,
1123 P_CONVERSION_DATE => l_conv_date,
1124 P_CONVERSION_TYPE => l_projfunc_rate_type,
1125 P_AMOUNT => l_bill_trans_amount,
1126 P_USER_VALIDATE_FLAG => 'Y',
1127 P_HANDLE_EXCEPTION_FLAG => 'Y',
1128 P_CONVERTED_AMOUNT => l_projfunc_rev_amt,
1129 P_DENOMINATOR => l_denominator,
1130 P_NUMERATOR => l_numerator,
1131 P_RATE => l_projfunc_exchange_rate,
1132 X_STATUS => l_status);
1133 IF l_status IS NOT NULL THEN
1134 RETURN(l_status);
1135 END IF;
1136 /*l_projfunc_rev_amt contains the revenue amount of the event being inserted in projfunc curency*/
1137
1138 IF (l_projfunc_rev_amt <= (l_accrued_amount-l_billed_amount-l_sum_revenue)) THEN
1139 RETURN('Y');
1140 ELSE
1141 RETURN('N');
1142 END IF;
1143
1144 Exception
1145 When others then
1146 --This user defined exception is used to track the packages and procedures
1147 --involved in that flow.
1148 --this user defined exception will be handled in private body which shall again
1149 --raise another user defined exception which will be handled in public body.
1150 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
1151 --only record the package name but also the procedure involved.
1152 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
1153 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_WRITE_OFF_AMT->';
1154 Raise util_excp;--Raising exception to handled in public body.
1155
1156
1157 END CHECK_WRITE_OFF_AMT;
1158 --------------------------------------------------------------------------------------------
1159 -- Federal Uptake
1160 /*it validates that the agreement number, agreement type, customer number
1161 returns the agreement_id as an out parameter to be used by
1162 subsequent functions*/
1163
1164 FUNCTION CHECK_VALID_AGREEMENT (
1165 P_project_id IN NUMBER
1166 ,P_task_id IN NUMBER
1167 ,P_agreement_number IN VARCHAR2
1168 ,P_agreement_type IN VARCHAR2
1169 ,P_customer_number IN VARCHAR2
1170 ,P_agreement_id OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
1171
1172 l_agreement_id number;
1173
1174 CURSOR GET_AGREEMENT_ID
1175 IS
1176 SELECT AG.AGREEMENT_ID
1177 FROM pa_projects_all p,
1178 pa_agreements_all ag,
1179 hz_cust_accounts cust,
1180 Pa_summary_project_fundings fun
1181 WHERE p.project_id = P_project_id
1182 AND nvl(p.date_eff_funds_consumption, 'N') = 'Y'
1183 AND fun.project_id = p.project_id
1184 AND ag.agreement_id = fun.agreement_id
1185 And nvl(fun.task_id, nvl(P_task_id,-999)) = nvl(P_task_id,-999)
1186 AND cust.account_number = P_customer_number
1187 AND ag.customer_id = cust.cust_account_id
1188 AND ag.agreement_num = P_agreement_number
1189 AND ag.agreement_type = P_agreement_type
1190 AND fun.TOTAL_BASELINED_AMOUNT >0;
1191
1192 BEGIN
1193
1194 IF (P_agreement_number IS NOT NULL OR
1195 P_agreement_type IS NOT NULL OR
1196 P_customer_number IS NOT NULL ) THEN /*If agreement number is provided*/
1197
1198 OPEN get_agreement_id;
1199 FETCH get_agreement_id INTO l_agreement_id;
1200
1201 IF get_agreement_id%FOUND THEN
1202 CLOSE get_agreement_id;
1203 P_agreement_id :=l_agreement_id;
1204 RETURN ('Y');
1205 ELSE
1206 CLOSE get_agreement_id;
1207 RETURN ('N');
1208 END IF;/*End of GET_AGREEMENT_ID%FOUND*/
1209
1210 ELSE
1211
1212 RETURN ('Y'); /*No agreement number, agreement type,
1213 customer numberis given,so no validation is required*/
1214 END IF;/*End of P_agreement_number IS NOT NULL*/
1215 Exception
1216 When others then
1217 p_agreement_id := NULL; --NOCOPY
1218 --This user defined exception is used to track the packages and procedures
1219 --involved in that flow.
1220 --this user defined exception will be handled in private body which shall again
1221 --raise another user defined exception which will be handled in public body.
1222 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
1223 --only record the package name but also the procedure involved.
1224 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
1225 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_AGREEMENT->';
1226 Raise util_excp;--Raising exception to handled in public body.
1227
1228
1229 END CHECK_VALID_AGREEMENT;
1230 -----------------------------------------------------------------------------------------------------
1231 -- Federal Uptake
1232 FUNCTION CHECK_VALID_EVENT_DATE (
1233 P_event_date IN DATE
1234 ,P_agreement_id IN NUMBER ) RETURN VARCHAR2 IS
1235
1236 l_agmt_start_date DATE;
1237 l_agmt_end_date DATE;
1238
1239 CURSOR get_agmt_date
1240 IS
1241 SELECT start_date, expiration_date
1242 FROM pa_agreements_all
1243 WHERE agreement_id = P_agreement_id;
1244
1245 BEGIN
1246
1247 IF P_event_date IS NOT NULL THEN
1248 OPEN get_agmt_date;
1249 FETCH get_agmt_date INTO l_agmt_start_date, l_agmt_end_date ;
1250
1251 IF (P_event_date between NVL(l_agmt_start_date,(P_event_date - 1))
1252 and NVL(l_agmt_end_date ,(P_event_date + 1))) THEN
1253 CLOSE get_agmt_date;
1254 RETURN ('Y');
1255 ELSE
1256 CLOSE get_agmt_date;
1257 RETURN('N');
1258 END IF;
1259 ELSE
1260 RETURN ('Y'); /*No event date is given,so no validation is required*/
1261 END IF;/*End of P_event_date IS NOT NULL*/
1262 Exception
1263 When others then
1264 --This user defined exception is used to track the packages and procedures
1265 --involved in that flow.
1266 --this user defined exception will be handled in private body which shall again
1267 --raise another user defined exception which will be handled in public body.
1268 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
1269 --only record the package name but also the procedure involved.
1270 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
1271 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_EVENT_DATE->';
1272 Raise util_excp;--Raising exception to handled in public body.
1273
1274
1275 END CHECK_VALID_EVENT_DATE;
1276 ----------------------------------------------------------------------------------------------------
1277
1278
1279 END PA_EVENT_CORE;
[Home] [Help]