4 /* The function returns y if
1 PACKAGE BODY PA_EVENT_CORE AS
2 /* $Header: PAEVAPCB.pls 120.9.12020000.3 2013/03/05 04:27:39 rvadali ship $ */
3
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.
89
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.
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
227
224 the event is of a valid event
225 type.it passes the event type
226 classification as an out parameter*/
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
363 Exception
360 CLOSE VALID_CURR;
361 RETURN('N');
362 END IF;
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(
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
383
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.
487 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_PROJ_RATE_TYPE->';
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->';
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
502 ) RETURN VARCHAR2 IS
503
504 -- Commented for bug 3009307 dummy number;
505
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;
612 When others then
609 RETURN('Y');/*Not a revenue event*/
610
611 Exception
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;
625 -------------------------------------------------------------------------------------------------------------
626
627
628 /*This function checks if the
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 ,BILL_TRANS_BILL_AMOUNT,BILL_TRANS_REV_AMOUNT /*Added for bug#9370006 */
642 FROM PA_EVENTS
643 WHERE EVENT_ID=P_event_id;
644 /*Bug 16245626 replaced di.task_id with di.event_task_id */
645
646 CURSOR EVENT_BILL_AMOUNT IS
647 SELECT SUM(NVL(AMOUNT,0))
648 FROM PA_DRAFT_INVOICE_ITEMS DI,PA_EVENTS EV,PA_DRAFT_INVOICES_ALL DIA
649 WHERE DI.PROJECT_ID=EV.PROJECT_ID
650 AND nvl(DI.EVENT_TASK_ID,-1) =nvl(EV.TASK_ID,-1)
651 AND DI.EVENT_NUM = EV.EVENT_NUM
652 AND DI.PROJECT_ID=DIA.PROJECT_ID
653 AND DI.DRAFT_INVOICE_NUM=DIA.DRAFT_INVOICE_NUM
654 AND NVL(DIA.WRITE_OFF_FLAG,'N')<>'Y'
655 AND EV.EVENT_ID = P_event_id;
656
657 l_rev_flag VARCHAR2(1);
658 l_billed_flag VARCHAR2(1);
659 L_BILL_AMOUNT pa_draft_invoice_items.amount%type;
660 l_invoiced_flag VARCHAR2(1):= 'N';
661 l_bill_trans_bill_amount pa_events.bill_trans_bill_amount%type; /* Added for bug#9370006 */
662 l_bill_trans_rev_amount pa_events.bill_trans_rev_amount%type; /* Added for bug#9370006 */
663 BEGIN
664
665 OPEN EVENT_PROCESSED;
666 FETCH EVENT_PROCESSED INTO l_rev_flag,l_billed_flag,l_bill_trans_bill_amount,l_bill_trans_rev_amount; /*
667 Modified for bug#9370006 */
668 CLOSE EVENT_PROCESSED;
669
670 IF (NVL(l_rev_flag,'N')='Y' AND NVL(l_billed_flag,'N')='Y') THEN /*The event has been processed */
671 RETURN('N');
672 ELSIF (NVL(l_billed_flag,'N')='Y') THEN
673 l_invoiced_flag := 'Y';
674 END IF;
675
676 IF l_invoiced_flag = 'N' THEN
677
678 DECLARE
679 dummy NUMBER;
680 BEGIN
681
682 SELECT 1
683 INTO dummy
684 FROM DUAL
685 WHERE EXISTS ( SELECT NULL
686 FROM PA_DRAFT_INVOICE_ITEMS DI,PA_EVENTS EV
687 WHERE DI.project_id=EV.project_id
688 AND nvl(DI.TASK_ID,-1) =nvl(EV.TASK_ID,-1)
689 AND DI.EVENT_NUM = EV.EVENT_NUM
690 AND EV.EVENT_ID = P_event_id);
691
692 OPEN EVENT_BILL_AMOUNT;
693 FETCH EVENT_BILL_AMOUNT INTO L_BILL_AMOUNT;
694
695 IF L_BILL_AMOUNT <> 0 THEN /*The event has been partially billed */
696 CLOSE EVENT_BILL_AMOUNT;
697 /* Added following if condition for bug 8485535*/
698 IF nvl(l_rev_flag, 'N') = 'Y' THEN
699 RETURN('P');
700 ELSE
701 RETURN('I');
702 END IF;
703 ELSE /*The invoice for the project has been cancelled */
704
705 CLOSE EVENT_BILL_AMOUNT;
706 /* Added following if condition for bug 8485535*/
707 IF nvl(l_rev_flag, 'N') = 'Y' THEN
708 RETURN('C');
709 ELSE
710 RETURN('Q');
711 END IF;
712 END IF;
713 EXCEPTION
714 WHEN NO_DATA_FOUND THEN /*The event has not been billed */
715 l_invoiced_flag := 'N';
716 END;
717
718 END IF;
719
720 /* Code added and modified for bug 7110782 - starts */
721
722 /* Both invoiced and revenue distributed, event has been processed.
723 No update will be allowed */
724 /* IF l_invoiced_flag = 'Y' AND nvl(l_rev_flag, 'N') = 'Y' THEN
725 RETURN('N');
726 END IF; commented for bug#9370006 */
727 /* Added the code for bug#9370006, starts here */
728 /* Neither revenue distributed nor invoiced.Event is not processed. */
729 IF l_invoiced_flag = 'N' AND nvl(l_rev_flag, 'N') = 'N' THEN -- Moved here Bug#14773611
730 RETURN('Y');
731 END IF;
732
733 IF (l_invoiced_flag = 'Y' OR (l_invoiced_flag = 'N' and l_bill_trans_bill_amount = 0)) AND
734 (nvl(l_rev_flag, 'N') = 'Y' OR (nvl(l_rev_flag, 'N') = 'N' and l_bill_trans_rev_amount = 0)) THEN
735 RETURN('N');
736 END IF;
737 /* Added the code for bug#9370006, ends here */
738
739 /* Only invoiced and not revenue distributed.
740 Only update of bill_trans_rev_amount will be allowed */
741 IF l_invoiced_flag = 'Y' AND nvl(l_rev_flag, 'N') = 'N' THEN
742 RETURN('I');
743 END IF;
744
745 /* Only revenue distributed but not invoiced
749 END IF;
746 Only update of bill_trans_bill_amount and bill_hold_flag will be allowed */
747 IF l_invoiced_flag = 'N' AND nvl(l_rev_flag, 'N') = 'Y' THEN
748 RETURN('R');
750
751 /* Code added and modified for bug 7110782 - ends */
752
753 Exception
754 When others then
755 --This user defined exception is used to track the packages and procedures
756 --involved in that flow.
757 --this user defined exception will be handled in private body which shall again
758 --raise another user defined exception which will be handled in public body.
759 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
760 --only record the package name but also the procedure involved.
761 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
762 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_EVENT_PROCESSED->';
763 Raise util_excp;--Raising exception to handled in public body.
764
765
766 END CHECK_EVENT_PROCESSED;
767 -------------------------------------------------------------------------------------------------------
768
769 /*This function checks if the
770 organization provided is a valid one
771 returns Y if valid N otherwise*/
772
773 FUNCTION CHECK_VALID_INV_ORG(
774 P_inv_org_name IN VARCHAR2,
775 P_inv_org_id OUT NOCOPY NUMBER) RETURN VARCHAR2 IS --File.Sql.39 bug 4440895
776
777 l NUMBER;
778
779 CURSOR VALID_INV_ORG
780 IS
781 SELECT HOU.organization_id
782 FROM PA_IMPLEMENTATIONS I,HR_ORGANIZATION_UNITS HOU
783 WHERE HOU.BUSINESS_GROUP_ID=I.BUSINESS_GROUP_ID
784 AND HOU.NAME=P_inv_org_name;
785
786 BEGIN
787
788 OPEN VALID_INV_ORG;
789 FETCH VALID_INV_ORG INTO l;
790
791 IF VALID_INV_ORG%FOUND THEN
792 CLOSE VALID_INV_ORG;
793 P_inv_org_id :=l;
794 RETURN('Y');
795 ELSE
796 CLOSE VALID_INV_ORG;
797 RETURN('N');
798 END IF;
799
800 Exception
801 When others then
802 p_inv_org_id := NULL; -- NOCOPY
803 --This user defined exception is used to track the packages and procedures
804 --involved in that flow.
805 --this user defined exception will be handled in private body which shall again
806 --raise another user defined exception which will be handled in public body.
807 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
808 --only record the package name but also the procedure involved.
809 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
810 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_INV_ORG->';
811 Raise util_excp;--Raising exception to handled in public body.
812
813
814 END CHECK_VALID_INV_ORG;
815 ----------------------------------------------------------------------------------
816 FUNCTION CHECK_VALID_INV_ITEM(
817 P_inv_item_id IN NUMBER) RETURN VARCHAR2 IS
818
819 dummy number;
820
821 CURSOR VALID_INV_ITEM
822 IS
823 SELECT 1
824 FROM mtl_item_flexfields
825 WHERE item_id=P_inv_item_id
826 AND trunc(sysdate)
827 BETWEEN decode(trunc(start_date_active), null, trunc(sysdate), trunc(start_date_active))
828 AND decode (trunc(end_date_active), null, trunc(sysdate), trunc(end_date_active));
829
830 BEGIN
831
832 OPEN VALID_INV_ITEM;
833 FETCH VALID_INV_ITEM INTO dummy;
834
835 IF ( VALID_INV_ITEM%FOUND) THEN
836 CLOSE VALID_INV_ITEM;
837 RETURN('Y');
838 ELSE
839 CLOSE VALID_INV_ITEM;
840 RETURN('N');
841 END IF;
842
843 Exception
844 When others then
845 --This user defined exception is used to track the packages and procedures
846 --involved in that flow.
847 --this user defined exception will be handled in private body which shall again
848 --raise another user defined exception which will be handled in public body.
849 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
850 --only record the package name but also the procedure involved.
851 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
852 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_INV_ITEM->';
853 Raise util_excp;--Raising exception to handled in public body.
854
855
856 END CHECK_VALID_INV_ITEM;
857 --------------------------------------------------------------------------------------------------------------------------
858
859 /*THIs function validates the revenue amount for write-off events.
860 It gets the total accrued amount(1) and total invoiced amount(2) in
861 projfunc currency.next it converst the revenue amount of unprocessed
862 write-off events to projfunc currency(3).write-off events can be
863 entered only if revenue amount(in projfunc currency) is <(1-2-3).*/
864
865 FUNCTION CHECK_WRITE_OFF_AMT(
866 P_project_id IN NUMBER
867 ,P_task_id IN NUMBER
868 ,P_event_id IN NUMBER
869 ,P_rev_amt IN NUMBER
870 ,P_bill_trans_currency IN VARCHAR2
871 ,P_proj_func_currency IN VARCHAR2
872 ,P_proj_func_rate_type IN VARCHAR2
873 ,P_proj_func_rate IN NUMBER
874 ,P_proj_func_rate_date IN DATE
875 ,P_event_date IN DATE )RETURN VARCHAR2 IS
876
877
878 CURSOR proj_rev_bill_amount
879 IS
880 SELECT SUM(NVL(projfunc_accrued_amount,0)),SUM(NVL(projfunc_billed_amount,0))
884
881 FROM pa_summary_project_fundings
882 WHERE project_id= P_project_id
883 AND task_id IS NULL;
885 CURSOR task_rev_bill_amount
886 IS
887 SELECT SUM(NVL(projfunc_accrued_amount,0)),SUM(NVL(projfunc_billed_amount,0))
888 FROM pa_summary_project_fundings
889 WHERE project_id= P_project_id
890 AND task_id=P_task_id;
891 /*This is commented for performance reason*/
892 /* CURSOR PROJ_WRITE_OFF_AMOUNT
893 IS
894 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
895 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,event_date
896 FROM PA_EVENTS_V
897 WHERE event_type_classification='WRITE OFF'
898 AND NVL(revenue_distributed_flag,'N')='N'
899 AND event_date IS NOT NULL
900 AND NVL(event_id,-1)<>NVL(P_event_id,-2)
901 AND project_id= P_project_id; */
902
903 /* CURSOR TASK_WRITE_OFF_AMOUNT
904 IS
905 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
906 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,event_date
907 FROM PA_EVENTS_V
908 WHERE event_type_classification='WRITE OFF'
909 AND NVL(REVENUE_DISTRIBUTED_FLAG,'N')='N'
910 AND event_date IS NOT NULL
911 AND project_id= P_project_id
912 AND NVL(event_id,-1)<>NVL(P_event_id,-2)
913 AND task_id=P_task_id;*/
914
915 /* Commented the below and added new for perf bug 3604238
916 CURSOR proj_write_off_amount
917 IS
918 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
919 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,completion_date
920 FROM PA_EVENTS EV,PA_EVENT_TYPES EVT
921 WHERE EVT.event_type_classification='WRITE OFF'
922 AND EVT.event_type=EV.event_type
923 AND NVL(EV.revenue_distributed_flag,'N')='N'
924 AND EV.completion_date IS NOT NULL
925 AND NVL(EV.event_id,-1)<>NVL(P_event_id,-2)
926 AND EV.project_id= P_project_id; */
927
928 CURSOR proj_write_off_amount
929 IS
930 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
931 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,completion_date
932 FROM PA_EVENTS EV
933 WHERE EV.revenue_distributed_flag ='N'
934 AND EV.completion_date IS NOT NULL
935 AND NVL(EV.event_id,-1)<>NVL(P_event_id,-2)
936 AND exists (select 1 from PA_EVENT_TYPES EVT
937 where EVT.event_type_classification='WRITE OFF'
938 and EVT.event_type=EV.event_type )
939 AND EV.project_id= P_project_id;
940
941 CURSOR task_write_off_amount
942 IS
943 SELECT NVL(bill_trans_rev_amount,0),bill_trans_currency_code,projfunc_currency_code,
944 projfunc_rate_type,projfunc_rate_date,projfunc_exchange_rate,completion_date
945 FROM PA_EVENTS EV ,PA_EVENT_TYPES EVT
946 WHERE EVT.event_type_classification='WRITE OFF'
947 AND EVT.event_type=EV.event_type
948 AND NVL(EV.revenue_distributed_flag,'N')='N'
949 AND EV.completion_date IS NOT NULL
950 AND EV.project_id= P_project_id
951 AND NVL(EV.event_id,-1)<>NVL(P_event_id,-2)
952 AND EV.task_id=P_task_id;
953
954 l_accrued_amount NUMBER;
955 l_billed_amount NUMBER;
956 l_bill_trans_amount NUMBER;
957 l_bill_trans_currency_code PA_EVENTS.bill_trans_currency_code%TYPE; /*VARCHAR2(2000);*/
958 l_projfunc_currency_code PA_EVENTS.projfunc_currency_code%TYPE; /*VARCHAR2(2000);*/
959 l_projfunc_rate_type PA_EVENTS.projfunc_rate_type%TYPE; /*VARCHAR2(2000);*/
960 l_projfunc_rate_date DATE;
961 l_projfunc_exchange_rate NUMBER;
962 l_event_date DATE;
963 l_conv_date DATE;
964 l_projfunc_rev_amt NUMBER;
965 l_denominator NUMBER;
966 l_numerator NUMBER;
967 l_status VARCHAR2(2000);
968 l_sum_revenue NUMBER :=0;
969
970 BEGIN
971
972 /*l_sum_revenue gets the sum of revenue amounts of all unprocessed WRITE OFF events
973 in project functional currency.*/
974 IF P_task_id IS NULL THEN /*project level event is being inserted So check for only project funding*/
975 OPEN proj_rev_bill_amount;
976 FETCH proj_rev_bill_amount
977 INTO l_accrued_amount,l_billed_amount;
978
979 IF proj_rev_bill_amount%NOTFOUND THEN
980 CLOSE proj_rev_bill_amount;
981 -- RETURN('Y'); /*there is no funding*/
982 RETURN('N'); /*there is no funding*/
983
984 ELSE /*of proj_rev_bill_amount%NOTFOUND*/
985 OPEN proj_write_off_amount;
986
987 LOOP
988
989 FETCH proj_write_off_amount
990 INTO l_bill_trans_amount,l_bill_trans_currency_code,
991 l_projfunc_currency_code,l_projfunc_rate_type,
992 l_projfunc_rate_date,l_projfunc_exchange_rate,l_event_date;
993
994 EXIT WHEN proj_write_off_amount%NOTFOUND;
995
996 l_conv_date := NVL(l_projfunc_rate_date,l_event_date);
997 /* Calling convert amount proc to convert this amount in PFC */
998 PA_MULTI_CURRENCY.convert_amount(
999 P_FROM_CURRENCY => l_bill_trans_currency_code,
1003 P_AMOUNT => l_bill_trans_amount,
1000 P_TO_CURRENCY => l_projfunc_currency_code,
1001 P_CONVERSION_DATE => l_conv_date,
1002 P_CONVERSION_TYPE => l_projfunc_rate_type,
1004 P_USER_VALIDATE_FLAG => 'Y',
1005 P_HANDLE_EXCEPTION_FLAG => 'Y',
1006 P_CONVERTED_AMOUNT => l_projfunc_rev_amt,
1007 P_DENOMINATOR => l_denominator,
1008 P_NUMERATOR => l_numerator,
1009 P_RATE => l_projfunc_exchange_rate,
1010 X_STATUS => l_status);
1011 IF l_status IS NOT NULL THEN
1012 CLOSE proj_write_off_amount;
1013 CLOSE proj_rev_bill_amount;
1014 RETURN(l_status);
1015 ELSE
1016 l_sum_revenue :=NVL(l_sum_revenue,0)+NVL(l_projfunc_rev_amt,0);
1017 /*This gives the total revenue amount of unprocessed
1018 write-off events in project functional currency*/
1019 END IF;
1020 END LOOP;
1021 CLOSE proj_write_off_amount;
1022 CLOSE proj_rev_bill_amount;
1023 END IF; /*proj_rev_bill_amount%NOTFOUND*/
1024
1025 ELSE /*p_task_id NOT NULL*/
1026 /*Task level event is being inserted .So we have to check both
1027 project as well as task level funding*/
1028
1029 OPEN proj_rev_bill_amount;
1030 FETCH proj_rev_bill_amount
1031 INTO l_accrued_amount,l_billed_amount;
1032
1033 IF l_accrued_amount IS NOT NULL THEN /*There is project level funding*/ /*Modified for bug 11664815*/
1034 OPEN proj_write_off_amount;
1035 LOOP
1036
1037 FETCH proj_write_off_amount
1038 INTO l_bill_trans_amount,l_bill_trans_currency_code,l_projfunc_currency_code,
1039 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate,
1040 l_event_date;
1041
1042 EXIT WHEN proj_write_off_amount%NOTFOUND;
1043
1044 l_conv_date := NVL(l_projfunc_rate_date,l_event_date);
1045 /* Calling convert amount proc to convert this amount in PFC */
1046 PA_MULTI_CURRENCY.convert_amount(
1047 P_FROM_CURRENCY => l_bill_trans_currency_code,
1048 P_TO_CURRENCY => l_projfunc_currency_code,
1049 P_CONVERSION_DATE => l_conv_date,
1050 P_CONVERSION_TYPE => l_projfunc_rate_type,
1051 P_AMOUNT => l_bill_trans_amount,
1052 P_USER_VALIDATE_FLAG => 'Y',
1053 P_HANDLE_EXCEPTION_FLAG => 'Y',
1054 P_CONVERTED_AMOUNT => l_projfunc_rev_amt,
1055 P_DENOMINATOR => l_denominator,
1056 P_NUMERATOR => l_numerator,
1057 P_RATE => l_projfunc_exchange_rate,
1058 X_STATUS => l_status);
1059 IF l_status IS NOT NULL THEN
1060 CLOSE proj_rev_bill_amount;
1061 CLOSE proj_write_off_amount;
1062 RETURN(l_status);
1063 ELSE
1064 l_sum_revenue :=NVL(l_sum_revenue,0)+NVL(l_projfunc_rev_amt,0);
1065 /*This gives the total revenue amount of unprocessed
1066 write-off events*/
1067 END IF;
1068 END LOOP;
1069 CLOSE proj_write_off_amount;
1070 CLOSE proj_rev_bill_amount;
1071
1072 ELSE /*proj_rev_bill_amount%FOUND*/
1073 CLOSE proj_rev_bill_amount;/*Close the cusrsor as it won't be used any more*/
1074
1075 OPEN task_rev_bill_amount ;
1076 FETCH task_rev_bill_amount
1077 INTO l_accrued_amount,l_billed_amount;
1078
1079 IF task_rev_bill_amount%NOTFOUND THEN
1080 CLOSE proj_rev_bill_amount;
1081 CLOSE task_rev_bill_amount;
1082 -- RETURN('Y'); /*there is no funding*/
1083 RETURN('N'); /*there is no funding*/
1084
1085 ELSE /*else of task_rev_bill_amount%NOTFOUND*/
1086
1087 OPEN task_write_off_amount;
1088 LOOP
1089
1090 FETCH task_write_off_amount
1091 INTO l_bill_trans_amount,l_bill_trans_currency_code,l_projfunc_currency_code,
1092 l_projfunc_rate_type,l_projfunc_rate_date,l_projfunc_exchange_rate,l_event_date;
1093
1094 EXIT WHEN task_write_off_amount%NOTFOUND;
1095
1096 l_conv_date := NVL(l_projfunc_rate_date,l_event_date);
1097 /* Calling convert amount proc to convert this amount in PFC */
1098 PA_MULTI_CURRENCY.convert_amount(
1099 P_FROM_CURRENCY => l_bill_trans_currency_code,
1100 P_TO_CURRENCY => l_projfunc_currency_code,
1101 P_CONVERSION_DATE => l_conv_date,
1102 P_CONVERSION_TYPE => l_projfunc_rate_type,
1103 P_AMOUNT => l_bill_trans_amount,
1104 P_USER_VALIDATE_FLAG => 'Y',
1105 P_HANDLE_EXCEPTION_FLAG => 'Y',
1106 P_CONVERTED_AMOUNT => l_projfunc_rev_amt,
1107 P_DENOMINATOR => l_denominator,
1111 IF l_status IS NOT NULL THEN
1108 P_NUMERATOR => l_numerator,
1109 P_RATE => l_projfunc_exchange_rate,
1110 X_STATUS => l_status);
1112 CLOSE task_rev_bill_amount;
1113 CLOSE task_write_off_amount;
1114 RETURN(l_status);
1115 ELSE
1116 l_sum_revenue :=NVL(l_sum_revenue,0)+NVL(l_projfunc_rev_amt,0);
1117 /*This gives the total revenue amount of unprocessed
1118 write-off events*/
1119 END IF;
1120 END LOOP;
1121 CLOSE task_write_off_amount;
1122 CLOSE task_rev_bill_amount;
1123 END IF; /*END OF task_rev_bill_amount%NOTFOUND*/
1124 END IF; /*proj_rev_bill_amount%FOUND*/
1125 END IF;/*p_task_id NOT NULL*/
1126 /*END OF CALCULATION OF l_sum_revenue*/
1127
1128 /*Copying the input parameter into local variables*/
1129 l_bill_trans_amount :=P_rev_amt;
1130 l_bill_trans_currency_code := P_bill_trans_currency;
1131 l_projfunc_currency_code := P_proj_func_currency;
1132 l_projfunc_rate_type := P_proj_func_rate_type;
1133 l_projfunc_exchange_rate := P_proj_func_rate;
1134 l_projfunc_rate_date := P_proj_func_rate_date;
1135 l_event_date := P_event_date;
1136 l_projfunc_rev_amt := 0.00;
1137 /*Next convert the revenue amount of the event into projfunc currency*/
1138 l_conv_date := NVL(l_projfunc_rate_date,l_event_date);
1139 /* Calling convert amount proc to convert this amount in PFC */
1140 PA_MULTI_CURRENCY.convert_amount(
1141 P_FROM_CURRENCY => l_bill_trans_currency_code,
1142 P_TO_CURRENCY => l_projfunc_currency_code,
1143 P_CONVERSION_DATE => l_conv_date,
1144 P_CONVERSION_TYPE => l_projfunc_rate_type,
1145 P_AMOUNT => l_bill_trans_amount,
1146 P_USER_VALIDATE_FLAG => 'Y',
1147 P_HANDLE_EXCEPTION_FLAG => 'Y',
1148 P_CONVERTED_AMOUNT => l_projfunc_rev_amt,
1149 P_DENOMINATOR => l_denominator,
1150 P_NUMERATOR => l_numerator,
1151 P_RATE => l_projfunc_exchange_rate,
1152 X_STATUS => l_status);
1153 IF l_status IS NOT NULL THEN
1154 RETURN(l_status);
1155 END IF;
1156 /*l_projfunc_rev_amt contains the revenue amount of the event being inserted in projfunc curency*/
1157
1158 IF (l_projfunc_rev_amt <= (l_accrued_amount-l_billed_amount-l_sum_revenue)) THEN
1159 RETURN('Y');
1160 ELSE
1161 RETURN('N');
1162
1163 END IF;
1164
1165 Exception
1166 When others then
1167 --This user defined exception is used to track the packages and procedures
1168 --involved in that flow.
1169 --this user defined exception will be handled in private body which shall again
1170 --raise another user defined exception which will be handled in public body.
1171 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
1172 --only record the package name but also the procedure involved.
1173 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
1174 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_WRITE_OFF_AMT->';
1175 Raise util_excp;--Raising exception to handled in public body.
1176
1177
1178 END CHECK_WRITE_OFF_AMT;
1179 --------------------------------------------------------------------------------------------
1180 -- Federal Uptake
1181 /*it validates that the agreement number, agreement type, customer number
1182 returns the agreement_id as an out parameter to be used by
1183 subsequent functions*/
1184
1185 FUNCTION CHECK_VALID_AGREEMENT (
1186 P_project_id IN NUMBER
1187 ,P_task_id IN NUMBER
1188 ,P_agreement_number IN VARCHAR2
1189 ,P_agreement_type IN VARCHAR2
1190 ,P_customer_number IN VARCHAR2
1191 ,P_agreement_id OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
1192
1193 l_agreement_id number;
1194
1195 CURSOR GET_AGREEMENT_ID
1196 IS
1197 SELECT AG.AGREEMENT_ID
1198 FROM pa_projects_all p,
1199 pa_agreements_all ag,
1200 hz_cust_accounts cust,
1201 Pa_summary_project_fundings fun
1202 WHERE p.project_id = P_project_id
1203 AND nvl(p.date_eff_funds_consumption, 'N') = 'Y'
1204 AND fun.project_id = p.project_id
1205 AND ag.agreement_id = fun.agreement_id
1206 And nvl(fun.task_id, nvl(P_task_id,-999)) = nvl(P_task_id,-999)
1207 AND cust.account_number = P_customer_number
1208 AND ag.customer_id = cust.cust_account_id
1209 AND ag.agreement_num = P_agreement_number
1210 AND ag.agreement_type = P_agreement_type
1211 AND fun.TOTAL_BASELINED_AMOUNT >0;
1212
1213 BEGIN
1214
1215 IF (P_agreement_number IS NOT NULL OR
1216 P_agreement_type IS NOT NULL OR
1217 P_customer_number IS NOT NULL ) THEN /*If agreement number is provided*/
1218
1219 OPEN get_agreement_id;
1220 FETCH get_agreement_id INTO l_agreement_id;
1221
1222 IF get_agreement_id%FOUND THEN
1223 CLOSE get_agreement_id;
1224 P_agreement_id :=l_agreement_id;
1225 RETURN ('Y');
1226 ELSE
1227 CLOSE get_agreement_id;
1228 RETURN ('N');
1229 END IF;/*End of GET_AGREEMENT_ID%FOUND*/
1230
1231 ELSE
1232
1233 RETURN ('Y'); /*No agreement number, agreement type,
1234 customer numberis given,so no validation is required*/
1235 END IF;/*End of P_agreement_number IS NOT NULL*/
1236 Exception
1237 When others then
1238 p_agreement_id := NULL; --NOCOPY
1239 --This user defined exception is used to track the packages and procedures
1240 --involved in that flow.
1241 --this user defined exception will be handled in private body which shall again
1242 --raise another user defined exception which will be handled in public body.
1243 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
1244 --only record the package name but also the procedure involved.
1245 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
1246 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_AGREEMENT->';
1247 Raise util_excp;--Raising exception to handled in public body.
1248
1249
1250 END CHECK_VALID_AGREEMENT;
1251 -----------------------------------------------------------------------------------------------------
1252 -- Federal Uptake
1253 FUNCTION CHECK_VALID_EVENT_DATE (
1254 P_event_date IN DATE
1255 ,P_agreement_id IN NUMBER ) RETURN VARCHAR2 IS
1256
1257 l_agmt_start_date DATE;
1258 l_agmt_end_date DATE;
1259
1260 CURSOR get_agmt_date
1261 IS
1262 SELECT start_date, expiration_date
1263 FROM pa_agreements_all
1264 WHERE agreement_id = P_agreement_id;
1265
1266 BEGIN
1267
1268 IF P_event_date IS NOT NULL THEN
1269 OPEN get_agmt_date;
1270 FETCH get_agmt_date INTO l_agmt_start_date, l_agmt_end_date ;
1271
1272 IF (P_event_date between NVL(l_agmt_start_date,(P_event_date - 1))
1273 and NVL(l_agmt_end_date ,(P_event_date + 1))) THEN
1274 CLOSE get_agmt_date;
1275 RETURN ('Y');
1276 ELSE
1277 CLOSE get_agmt_date;
1278 RETURN('N');
1279 END IF;
1280 ELSE
1281 RETURN ('Y'); /*No event date is given,so no validation is required*/
1282 END IF;/*End of P_event_date IS NOT NULL*/
1283 Exception
1284 When others then
1285 --This user defined exception is used to track the packages and procedures
1286 --involved in that flow.
1287 --this user defined exception will be handled in private body which shall again
1288 --raise another user defined exception which will be handled in public body.
1289 --At each of these places like CORE,PRIVATE and PUBLIC packages we shall not
1290 --only record the package name but also the procedure involved.
1291 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'CORE->';
1292 PA_EVENT_PUB.procedure_name := PA_EVENT_PUB.procedure_name ||'CHECK_VALID_EVENT_DATE->';
1293 Raise util_excp;--Raising exception to handled in public body.
1294
1295
1296 END CHECK_VALID_EVENT_DATE;
1297 ----------------------------------------------------------------------------------------------------
1298
1299
1300 END PA_EVENT_CORE;
[Home] [Help]