[Home] [Help]
PACKAGE BODY: APPS.PA_PAXMGURA_XMLP_PKG
Source
1 PACKAGE BODY PA_PAXMGURA_XMLP_PKG AS
2 /* $Header: PAXMGURAB.pls 120.0.12010000.2 2008/12/12 11:20:17 dbudhwar ship $ */
3 DATE1 DATE;
4
5 DATE2 DATE;
6
7 DATE3 DATE;
8
9 FUNCTION FINAL_BUCKET4FORMULA(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER) RETURN NUMBER IS
10 BEGIN
11 RETURN CALC_BUCKET4(EVENT_INVOICED_AMOUNT,COST_WORK_AMOUNT,BUCKET1,BUCKET2,BUCKET3,BUCKET4);
12 END;
13 FUNCTION FINAL_BUCKET3FORMULA(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER) RETURN NUMBER IS
14 BEGIN
15 RETURN CALC_BUCKET3(EVENT_INVOICED_AMOUNT,COST_WORK_AMOUNT,BUCKET1,BUCKET2,BUCKET3,BUCKET4);
16 END;
17 FUNCTION FINAL_BUCKET2FORMULA(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER) RETURN NUMBER IS
18 BEGIN
19 RETURN CALC_BUCKET2(EVENT_INVOICED_AMOUNT,COST_WORK_AMOUNT,BUCKET1,BUCKET2,BUCKET3,BUCKET4);
20 END;
21 FUNCTION FINAL_BUCKET1FORMULA(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER,INVOICE_REDUCTION IN NUMBER,RETENTION IN NUMBER) RETURN NUMBER IS
22 BEGIN
23 RETURN CALC_BUCKET1(EVENT_INVOICED_AMOUNT,COST_WORK_AMOUNT,BUCKET1,BUCKET2,BUCKET3,BUCKET4,INVOICE_REDUCTION,RETENTION);
24 END;
25 FUNCTION TOTALFORMULA(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER,INVOICE_REDUCTION IN NUMBER,RETENTION IN NUMBER) RETURN NUMBER IS
26 BEGIN
27 RETURN CALC_TOTAL_BUCKETS(EVENT_INVOICED_AMOUNT,COST_WORK_AMOUNT,BUCKET1,BUCKET2,BUCKET3,BUCKET4,INVOICE_REDUCTION,RETENTION);
28 END;
29
30 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
31 XX NUMBER;
32 BEGIN
33 RETURN (TRUE);
34 END BEFOREPFORM;
35
36 FUNCTION AFTERREPORT RETURN BOOLEAN IS
37 BEGIN
38 ROLLBACK;
39 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
40 RETURN (TRUE);
41 END AFTERREPORT;
42
43 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
44 NDF VARCHAR2(80);
45 BEGIN
46 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
47 P_ORG_ID := ORG_ID;
48 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
49 /*SRW.USER_EXIT('FND GETPROFILE
50 NAME="PA_RULE_BASED_OPTIMIZER"
51 FIELD=":p_rule_optimizer"
52 PRINT_ERROR="N"')*/NULL;
53 P_DEBUG_MODE := FND_PROFILE.VALUE('PA_DEBUG_MODE');
54
55 /* Added for bug 7115658 */
56 IF proj is null THEN
57 IF from_project_number is null then
58 begin
59 select min(p.segment1) into from_project_number
60 from pa_projects_all p, pa_project_types_all pt
61 where p.project_type = pt.project_type
62 and pt.project_type_class_code = 'CONTRACT';
63 exception
64 when no_data_found then
65 null;
66 when others then
67 /*srw.message(2,'From Project Number ' || sqlerrm)*/ null;
68 raise_application_error(-20101,null);/*srw.program_abort;*/null;
69 end;
70 END IF;
71
72
73 IF to_project_number is null then
74 begin
75 select max(p.segment1) into to_project_number
76 from pa_projects_all p, pa_project_types_all pt
77 where p.project_type = pt.project_type
78 and pt.project_type_class_code = 'CONTRACT';
79 exception
80 when no_data_found then
81 null;
82 when others then
83 /* srw.message(2,'to Project Number ' || sqlerrm) */ null;
84 raise_application_error(-20101,null);/*srw.program_abort;*/null;
85 end;
86 END IF;
87 END IF;
88 /* End of code for bug 7115658 */
89
90
91 SELECT_DATES;
92 POPULATE;
93 IF NOT GET_COMPANY_NAME THEN
94 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
95 END IF;
96 GET_COLUMN_HEADINGS;
97 BEGIN
98 SELECT
99 SUBSTR(MEANING,5,13)
100 INTO NDF
101 FROM
102 PA_LOOKUPS
103 WHERE LOOKUP_CODE = 'NO_DATA_FOUND'
104 AND LOOKUP_TYPE = 'MESSAGE';
105 C_NO_DATA_FOUND := NDF;
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 C_NO_DATA_FOUND := 'No Data Found';
109 WHEN OTHERS THEN
110 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
111 END;
112 RETURN (TRUE);
113 EXCEPTION
114 WHEN OTHERS THEN
115 ROLLBACK;
116 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
117 RETURN (TRUE);
118 END BEFOREREPORT;
119
120 FUNCTION CF_CURRENCY_CODEFORMULA RETURN VARCHAR2 IS
121 BEGIN
122 RETURN (PA_MULTI_CURRENCY.GET_ACCT_CURRENCY_CODE);
123 END CF_CURRENCY_CODEFORMULA;
124
125 FUNCTION AFTERPFORM RETURN BOOLEAN IS
126 BEGIN
127 RETURN (TRUE);
128 END AFTERPFORM;
129
130 /* PROCEDURE UPDATE_EIS IS
131 DATE1 VARCHAR2(9);
132 DATE2 VARCHAR2(9);
133 DATE3 VARCHAR2(9);
134 BEGIN
135 SELECT
136 ( NVL(DATE_FROM
137 ,SYSDATE) - BUCKET_SIZE1 ),
138 ( NVL(DATE_FROM
139 ,SYSDATE) - ( BUCKET_SIZE2 + BUCKET_SIZE1 ) ),
140 ( NVL(DATE_FROM
141 ,SYSDATE) - ( BUCKET_SIZE3 + BUCKET_SIZE2 + BUCKET_SIZE1 ) )
142 INTO DATE1,DATE2,DATE3
143 FROM
144 SYS.DUAL;
145 UPDATE
146 PA_UNBILLED_REC_REPORTING TU
147 SET
148 (EI_BUCKET1,EI_BUCKET2,EI_BUCKET3,EI_BUCKET4) = (SELECT
149 NVL(TU.EI_BUCKET1
150 ,0) + NVL(SUM(DECODE(LEAST(DECODE(AGE
151 ,'GL_DATE'
152 ,PDI1.GL_DATE
153 ,PDI1.PA_DATE)
154 ,DATE1)
155 ,DATE1
156 ,DECODE(PDI1.RELEASED_DATE
157 ,NULL
158 ,PDII.PROJFUNC_BILL_AMOUNT
159 ,0)
160 ,0))
161 ,0),
162 NVL(TU.EI_BUCKET2
163 ,0) + NVL(SUM(DECODE(LEAST(DECODE(AGE
164 ,'GL_DATE'
165 ,PDI1.GL_DATE
166 ,PDI1.PA_DATE)
167 ,TO_DATE(DATE1
168 ,'YYYY/MM/DD') - 1)
169 ,DECODE(AGE
170 ,'GL_DATE'
171 ,PDI1.GL_DATE
172 ,PDI1.PA_DATE)
173 ,DECODE(LEAST(DECODE(AGE
174 ,'GL_DATE'
175 ,PDI1.GL_DATE
176 ,PDI1.PA_DATE)
177 ,DATE2)
178 ,DATE2
179 ,DECODE(PDI1.RELEASED_DATE
180 ,NULL
181 ,PDII.PROJFUNC_BILL_AMOUNT
182 ,0)
183 ,0)
184 ,0))
185 ,0),
186 NVL(TU.EI_BUCKET3
187 ,0) + NVL(SUM(DECODE(LEAST(DECODE(AGE
188 ,'GL_DATE'
189 ,PDI1.GL_DATE
190 ,PDI1.PA_DATE)
191 ,TO_DATE(DATE2
192 ,'YYYY/MM/DD') - 1)
193 ,DECODE(AGE
194 ,'GL_DATE'
195 ,PDI1.GL_DATE
196 ,PDI1.PA_DATE)
197 ,DECODE(LEAST(DECODE(AGE
198 ,'GL_DATE'
199 ,PDI.GL_DATE
200 ,PDI1.PA_DATE)
201 ,DATE3)
202 ,DATE3
203 ,DECODE(PDI1.RELEASED_DATE
204 ,NULL
205 ,PDII.PROJFUNC_BILL_AMOUNT
206 ,0)
207 ,0)
208 ,0))
209 ,0),
210 NVL(TU.EI_BUCKET4
211 ,0) + NVL(SUM(DECODE(LEAST(DECODE(AGE
212 ,'GL_DATE'
213 ,PDI1.GL_DATE
214 ,PDI1.PA_DATE)
215 ,TO_DATE(DATE3
216 ,'YYYY/MM/DD') - 1)
217 ,DECODE(AGE
218 ,'GL_DATE'
219 ,PDI1.GL_DATE
220 ,PDI1.PA_DATE)
221 ,DECODE(PDI1.RELEASED_DATE
222 ,NULL
223 ,PDII.PROJFUNC_BILL_AMOUNT
224 ,0)
225 ,0))
226 ,0)
227 FROM
228 PA_PROJECTS P,
229 PA_PROJECT_PLAYERS PL,
230 PA_DRAFT_INVOICES PDI,
231 PA_DRAFT_INVOICES PDI1,
232 PA_DRAFT_INVOICE_ITEMS PDII
233 WHERE P.CARRYING_OUT_ORGANIZATION_ID between NVL(NULL
234 ,0)
235 AND NVL(NULL
236 ,999999999999999)
237 AND P.PROJECT_ID between NVL(PROJ
238 ,0)
239 AND NVL(PROJ
240 ,999999999999999)
241 AND PL.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
242 AND NVL(NULL
243 ,SYSDATE) between PL.START_DATE_ACTIVE
244 AND NVL(PL.END_DATE_ACTIVE
245 ,NVL(NULL
246 ,SYSDATE + 1))
247 AND PL.PERSON_ID between NVL(NULL
248 ,0)
249 AND NVL(NULL
250 ,999999999999999)
251 AND P.PROJECT_ID = PL.PROJECT_ID
252 AND PDI.PROJECT_ID = P.PROJECT_ID
253 AND PDI1.PROJECT_ID = PDI.PROJECT_ID
254 AND PDI1.DRAFT_INVOICE_NUM_CREDITED is not null
255 AND PDI1.DRAFT_INVOICE_NUM_CREDITED = PDI.DRAFT_INVOICE_NUM
256 AND PDII.PROJECT_ID = PDI1.PROJECT_ID
257 AND PDII.DRAFT_INVOICE_NUM = PDI1.DRAFT_INVOICE_NUM
258 AND PDII.DRAFT_INVOICE_NUM = PDI1.DRAFT_INVOICE_NUM
259 AND DECODE(AGE
260 ,'GL_DATE'
261 ,PDI.GL_DATE
262 ,PDI.PA_DATE) <= NVL(DATE_FROM
263 ,SYSDATE)
264 AND TU.PROJECT_ID = P.PROJECT_ID);
265 END UPDATE_EIS; */
266
267 FUNCTION G_PROJECTGROUPFILTER(FINAL_BUCKET1 IN NUMBER
268 ,FINAL_BUCKET2 IN NUMBER
269 ,FINAL_BUCKET3 IN NUMBER
270 ,FINAL_BUCKET4 IN NUMBER) RETURN BOOLEAN IS
271 BEGIN
272 IF (FINAL_BUCKET1 = 0 AND FINAL_BUCKET2 = 0 AND FINAL_BUCKET3 = 0 AND FINAL_BUCKET4 = 0) THEN
273 RETURN (FALSE);
274 ELSE
275 RETURN (TRUE);
276 END IF;
277 END G_PROJECTGROUPFILTER;
278
279 FUNCTION C_COLHEAD1_P RETURN VARCHAR2 IS
280 BEGIN
281 RETURN C_COLHEAD1;
282 END C_COLHEAD1_P;
283
284 FUNCTION C_COLHEAD2_P RETURN VARCHAR2 IS
285 BEGIN
286 RETURN C_COLHEAD2;
287 END C_COLHEAD2_P;
288
289 FUNCTION C_COLHEAD3_P RETURN VARCHAR2 IS
290 BEGIN
291 RETURN C_COLHEAD3;
292 END C_COLHEAD3_P;
293
294 FUNCTION C_COLHEAD4_P RETURN VARCHAR2 IS
295 BEGIN
296 RETURN C_COLHEAD4;
297 END C_COLHEAD4_P;
298
299 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
300 BEGIN
301 RETURN C_COMPANY_NAME_HEADER;
302 END C_COMPANY_NAME_HEADER_P;
303
304 FUNCTION C_NO_DATA_FOUND_P RETURN VARCHAR2 IS
305 BEGIN
306 RETURN C_NO_DATA_FOUND;
307 END C_NO_DATA_FOUND_P;
308
309 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
310 L_NAME HR_ORGANIZATION_UNITS.NAME%TYPE;
311 BEGIN
312 SELECT
313 GL.NAME
314 INTO L_NAME
315 FROM
316 GL_SETS_OF_BOOKS GL,
317 PA_IMPLEMENTATIONS PI
318 WHERE GL.SET_OF_BOOKS_ID = PI.SET_OF_BOOKS_ID;
319 C_COMPANY_NAME_HEADER := L_NAME;
320 RETURN (TRUE);
321 EXCEPTION
322 WHEN OTHERS THEN
323 RETURN (FALSE);
324 END GET_COMPANY_NAME;
325
326 PROCEDURE GET_COLUMN_HEADINGS IS
327 COLHEAD1 VARCHAR2(15);
328 COLHEAD2 VARCHAR2(15);
329 COLHEAD3 VARCHAR2(15);
330 COLHEAD4 VARCHAR2(15);
331 BEGIN
332 SELECT
333 LPAD('0-' || TO_CHAR(BUCKET_SIZE1) || ' days'
334 ,14),
335 LPAD(TO_CHAR(BUCKET_SIZE1 + 1) || '-' || TO_CHAR(BUCKET_SIZE1 + BUCKET_SIZE2) || ' days'
336 ,14),
337 LPAD(TO_CHAR(BUCKET_SIZE1 + BUCKET_SIZE2 + 1) || '-' || TO_CHAR(BUCKET_SIZE1 + BUCKET_SIZE2 + BUCKET_SIZE3) || ' days'
338 ,14),
339 LPAD(TO_CHAR(BUCKET_SIZE1 + BUCKET_SIZE2 + BUCKET_SIZE3 + 1) || '+ days'
340 ,14)
341 INTO COLHEAD1,COLHEAD2,COLHEAD3,COLHEAD4
342 FROM
343 SYS.DUAL;
344 C_COLHEAD1 := COLHEAD1;
345 C_COLHEAD2 := COLHEAD2;
346 C_COLHEAD3 := COLHEAD3;
347 C_COLHEAD4 := COLHEAD4;
348 END GET_COLUMN_HEADINGS;
349
350 PROCEDURE SELECT_DATES IS
351 BEGIN
352 SELECT
353 ( NVL(DATE_FROM
354 ,SYSDATE) - BUCKET_SIZE1 ),
355 ( NVL(DATE_FROM
356 ,SYSDATE) - ( BUCKET_SIZE2 + BUCKET_SIZE1 ) ),
357 ( NVL(DATE_FROM
358 ,SYSDATE) - ( BUCKET_SIZE3 + BUCKET_SIZE2 + BUCKET_SIZE1 ) )
359 INTO DATE1,DATE2,DATE3
360 FROM
361 SYS.DUAL;
362 END SELECT_DATES;
363
364 PROCEDURE INSERT_EIS IS
365 CURSOR C1 IS
366 SELECT
367 DISTINCT
368 P.PROJECT_ID
369 FROM
370 PA_PROJECTS P,
371 PA_PROJECT_PLAYERS PL
372 WHERE P.CARRYING_OUT_ORGANIZATION_ID BETWEEN NVL(P_ORG_ID
373 ,0)
374 AND NVL(P_ORG_ID
375 ,999999999999999)
376 AND P.PROJECT_ID BETWEEN NVL(PROJ
377 ,0)
378 AND NVL(PROJ
379 ,999999999999999)
380 AND PL.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
381 AND NVL(DATE_FROM
382 ,SYSDATE) BETWEEN PL.START_DATE_ACTIVE
383 AND NVL(PL.END_DATE_ACTIVE
384 ,NVL(DATE_FROM
385 ,SYSDATE + 1))
386 AND PL.PERSON_ID BETWEEN NVL(PROJECT_MANAGER_ID
387 ,0)
388 AND NVL(PROJECT_MANAGER_ID
389 ,999999999999999)
390 AND P.PROJECT_ID = PL.PROJECT_ID;
391 BEGIN
392 FOR c1rec IN C1 LOOP
393 INSERT INTO PA_UNBILLED_REC_REPORTING
394 (PROJECT_ID
395 ,EI_BUCKET1
396 ,EI_BUCKET2
397 ,EI_BUCKET3
398 ,EI_BUCKET4)
399 SELECT
400 T.PROJECT_ID,
401 SUM(DECODE(LEAST(DECODE(AGE
402 ,'EXPENDITURE_ITEM_DATE'
403 ,PAI.EXPENDITURE_ITEM_DATE
404 ,'GL_DATE'
405 ,PDR.GL_DATE
406 ,PDR.PA_DATE)
407 ,DATE1)
408 ,DATE1
409 ,DECODE(PCR.PROJFUNC_BILL_AMOUNT
410 ,PCR.AMOUNT
411 ,DECODE(PDI.RELEASED_DATE
412 ,NULL
413 ,PCR.AMOUNT
414 ,0)
415 ,PCR.AMOUNT)
416 ,0)),
417 SUM(DECODE(LEAST(DECODE(AGE
418 ,'EXPENDITURE_ITEM_DATE'
419 ,PAI.EXPENDITURE_ITEM_DATE
420 ,'GL_DATE'
421 ,PDR.GL_DATE
422 ,PDR.PA_DATE)
423 ,DATE1 - 1)
424 ,DECODE(AGE
425 ,'EXPENDITURE_ITEM_DATE'
426 ,PAI.EXPENDITURE_ITEM_DATE
427 ,'GL_DATE'
428 ,PDR.GL_DATE
429 ,PDR.PA_DATE)
430 ,DECODE(LEAST(DECODE(AGE
431 ,'EXPENDITURE_ITEM_DATE'
432 ,PAI.EXPENDITURE_ITEM_DATE
433 ,'GL_DATE'
434 ,PDR.GL_DATE
435 ,PDR.PA_DATE)
436 ,DATE2)
437 ,DATE2
438 ,DECODE(PCR.PROJFUNC_BILL_AMOUNT
439 ,PCR.AMOUNT
440 ,DECODE(PDI.RELEASED_DATE
441 ,NULL
442 ,PCR.AMOUNT
443 ,0)
444 ,PCR.AMOUNT)
445 ,0)
446 ,0)),
447 SUM(DECODE(LEAST(DECODE(AGE
448 ,'EXPENDITURE_ITEM_DATE'
449 ,PAI.EXPENDITURE_ITEM_DATE
450 ,'GL_DATE'
451 ,PDR.GL_DATE
452 ,PDR.PA_DATE)
453 ,DATE2 - 1)
454 ,DECODE(AGE
455 ,'EXPENDITURE_ITEM_DATE'
456 ,PAI.EXPENDITURE_ITEM_DATE
457 ,'GL_DATE'
458 ,PDR.GL_DATE
459 ,PDR.PA_DATE)
460 ,DECODE(LEAST(DECODE(AGE
461 ,'EXPENDITURE_ITEM_DATE'
462 ,PAI.EXPENDITURE_ITEM_DATE
463 ,'GL_DATE'
464 ,PDR.GL_DATE
465 ,PDR.PA_DATE)
466 ,DATE3)
467 ,DATE3
468 ,DECODE(PCR.PROJFUNC_BILL_AMOUNT
469 ,PCR.AMOUNT
470 ,DECODE(PDI.RELEASED_DATE
471 ,NULL
472 ,PCR.AMOUNT
473 ,0)
474 ,PCR.AMOUNT)
475 ,0)
476 ,0)),
477 SUM(DECODE(LEAST(DECODE(AGE
478 ,'EXPENDITURE_ITEM_DATE'
479 ,PAI.EXPENDITURE_ITEM_DATE
480 ,'GL_DATE'
481 ,PDR.GL_DATE
482 ,PDR.PA_DATE)
483 ,DATE3 - 1)
484 ,DECODE(AGE
485 ,'EXPENDITURE_ITEM_DATE'
486 ,PAI.EXPENDITURE_ITEM_DATE
487 ,'GL_DATE'
488 ,PDR.GL_DATE
489 ,PDR.PA_DATE)
490 ,DECODE(PCR.PROJFUNC_BILL_AMOUNT
491 ,PCR.AMOUNT
492 ,DECODE(PDI.RELEASED_DATE
493 ,NULL
494 ,PCR.AMOUNT
495 ,0)
496 ,PCR.AMOUNT)
497 ,0))
498 FROM
499 PA_TASKS T,
500 PA_EXPENDITURE_ITEMS_ALL PAI,
501 PA_CUST_REV_DIST_LINES PCR,
502 PA_DRAFT_INVOICES PDI,
503 PA_DRAFT_REVENUES PDR
504 WHERE T.PROJECT_ID = C1REC.PROJECT_ID
505 AND PDR.PROJECT_ID = C1REC.PROJECT_ID
506 AND T.TASK_ID = PAI.TASK_ID
507 AND PAI.EXPENDITURE_ITEM_ID = PCR.EXPENDITURE_ITEM_ID
508 AND PCR.PROJECT_ID = PDR.PROJECT_ID
509 AND PCR.DRAFT_REVENUE_NUM = PDR.DRAFT_REVENUE_NUM
510 AND PDR.RELEASED_DATE IS NOT NULL
511 AND PCR.DRAFT_INVOICE_NUM = pdi.draft_invoice_num (+)
512 AND PCR.PROJECT_ID = pdi.project_id (+)
513 AND PCR.FUNCTION_CODE not in ( 'LRB' , 'LRL' , 'URB' , 'URL' )
514 AND DECODE(AGE
515 ,'EXPENDITURE_ITEM_DATE'
516 ,PAI.EXPENDITURE_ITEM_DATE
517 ,'GL_DATE'
518 ,PDR.GL_DATE
519 ,PDR.PA_DATE) <= NVL(DATE_FROM
520 ,SYSDATE)
521 GROUP BY
522 T.PROJECT_ID;
523 END LOOP;
524 END INSERT_EIS;
525
526 /* PROCEDURE INSERT_EVENTS IS
527 BEGIN
528 INSERT INTO PA_UNBILLED_REC_REPORTING
529 (PROJECT_ID
530 ,EVENT_BUCKET1
531 ,EVENT_BUCKET2
532 ,EVENT_BUCKET3
533 ,EVENT_BUCKET4)
534 SELECT
535 P.PROJECT_ID,
536 SUM(DECODE(LEAST(DECODE(AGE
537 ,'EXPENDITURE_ITEM_DATE'
538 ,PE.COMPLETION_DATE
539 ,'GL_DATE'
540 ,PDR.GL_DATE
541 ,PDR.PA_DATE)
542 ,DATE1)
543 ,DATE1
544 ,NVL(PCR.AMOUNT
545 ,0)
546 ,0)),
547 SUM(DECODE(LEAST(DECODE(AGE
548 ,'EXPENDITURE_ITEM_DATE'
549 ,PE.COMPLETION_DATE
550 ,'GL_DATE'
551 ,PDR.GL_DATE
552 ,PDR.PA_DATE)
553 ,DATE1 - 1)
554 ,DECODE(AGE
555 ,'EXPENDITURE_ITEM_DATE'
556 ,PE.COMPLETION_DATE
557 ,'GL_DATE'
558 ,PDR.GL_DATE
559 ,PDR.PA_DATE)
560 ,DECODE(LEAST(DECODE(AGE
561 ,'EXPENDITURE_ITEM_DATE'
562 ,PE.COMPLETION_DATE
563 ,'GL_DATE'
564 ,PDR.GL_DATE
565 ,PDR.PA_DATE)
566 ,DATE2)
567 ,DATE2
568 ,NVL(PCR.AMOUNT
569 ,0)
570 ,0)
571 ,0)),
572 SUM(DECODE(LEAST(DECODE(AGE
573 ,'EXPENDITURE_ITEM_DATE'
574 ,PE.COMPLETION_DATE
575 ,'GL_DATE'
576 ,PDR.GL_DATE
577 ,PDR.PA_DATE)
578 ,DATE2 - 1)
579 ,DECODE(AGE
580 ,'EXPENDITURE_ITEM_DATE'
581 ,PE.COMPLETION_DATE
582 ,'GL_DATE'
583 ,PDR.GL_DATE
584 ,PDR.PA_DATE)
585 ,DECODE(LEAST(DECODE(AGE
586 ,'EXPENDITURE_ITEM_DATE'
587 ,PE.COMPLETION_DATE
588 ,'GL_DATE'
589 ,PDR.GL_DATE
590 ,PDR.PA_DATE)
591 ,DATE3)
592 ,DATE3
593 ,NVL(PCR.AMOUNT
594 ,0)
595 ,0)
596 ,0)),
597 SUM(DECODE(LEAST(DECODE(AGE
598 ,'EXPENDITURE_ITEM_DATE'
599 ,PE.COMPLETION_DATE
600 ,'GL_DATE'
601 ,PDR.GL_DATE
602 ,PDR.PA_DATE)
603 ,DATE3 - 1)
604 ,DECODE(AGE
605 ,'EXPENDITURE_ITEM_DATE'
606 ,PE.COMPLETION_DATE
607 ,'GL_DATE'
608 ,PDR.GL_DATE
609 ,PDR.PA_DATE)
610 ,NVL(PCR.AMOUNT
611 ,0)
612 ,0))
613 FROM
614 PA_PROJECTS P,
615 PA_PROJECT_PLAYERS PL,
616 PA_EVENTS PE,
617 PA_EVENT_TYPES PET,
618 PA_DRAFT_REVENUES PDR,
619 PA_CUST_EVENT_REV_DIST_LINES PCR
620 WHERE P.CARRYING_OUT_ORGANIZATION_ID between NVL(P_ORG_ID
621 ,0)
622 AND NVL(P_ORG_ID
623 ,999999999999999)
624 AND P.PROJECT_ID between NVL(PROJ
625 ,0)
626 AND NVL(PROJ
627 ,999999999999999)
628 AND PL.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
629 AND NVL(DATE_FROM
630 ,SYSDATE) between PL.START_DATE_ACTIVE
631 AND NVL(PL.END_DATE_ACTIVE
632 ,NVL(DATE_FROM
633 ,SYSDATE + 1))
634 AND PL.PERSON_ID between NVL(PROJECT_MANAGER_ID
635 ,0)
636 AND NVL(PROJECT_MANAGER_ID
637 ,999999999999999)
638 AND P.PROJECT_ID = PL.PROJECT_ID
639 AND PE.REVENUE_DISTRIBUTED_FLAG || '' = 'Y'
640 AND PE.PROJECT_ID = P.PROJECT_ID
641 AND PCR.PROJECT_ID = PDR.PROJECT_ID
642 AND PCR.DRAFT_REVENUE_NUM = PDR.DRAFT_REVENUE_NUM
643 AND PDR.RELEASED_DATE IS NOT NULL
644 AND NOT EXISTS (
645 SELECT
646 'x'
647 FROM
648 PA_DRAFT_INVOICES PDI
649 WHERE PCR.PROJECT_ID = PDI.PROJECT_ID
650 AND PCR.DRAFT_INVOICE_NUM = PDI.DRAFT_INVOICE_NUM
651 AND PDI.RELEASED_DATE IS NOT NULL )
652 AND PE.EVENT_TYPE = PET.EVENT_TYPE
653 AND PET.EVENT_TYPE_CLASSIFICATION || '' in ( 'WRITE ON' , 'MANUAL' , 'AUTOMATIC' )
654 AND PE.REVENUE_AMOUNT is not null
655 AND PE.PROJECT_ID = PCR.PROJECT_ID
656 AND NVL(PE.TASK_ID
657 ,-1) = NVL(PCR.TASK_ID
658 ,-1)
659 AND PE.EVENT_NUM = PCR.EVENT_NUM
660 AND not exists (
661 SELECT
662 'x'
663 FROM
664 PA_UNBILLED_REC_REPORTING T1
665 WHERE T1.PROJECT_ID = P.PROJECT_ID )
666 AND DECODE(AGE
667 ,'EXPENDITURE_ITEM_DATE'
668 ,PE.COMPLETION_DATE
669 ,'GL_DATE'
670 ,PDR.GL_DATE
671 ,PDR.PA_DATE) <= NVL(DATE_FROM
672 ,SYSDATE)
673 GROUP BY
674 P.PROJECT_ID;
675 END INSERT_EVENTS;*/
676
677 PROCEDURE UPDATE_EVENTS IS
678 BEGIN
679 UPDATE
680 PA_UNBILLED_REC_REPORTING TU
681 SET
682 (EVENT_BUCKET1,EVENT_BUCKET2,EVENT_BUCKET3,EVENT_BUCKET4) = (SELECT
683 SUM(DECODE(LEAST(DECODE(AGE
684 ,'EXPENDITURE_ITEM_DATE'
685 ,PE.COMPLETION_DATE
686 ,'GL_DATE'
687 ,PDR.GL_DATE
688 ,PDR.PA_DATE)
689 ,DATE1)
690 ,DATE1
691 ,NVL(PCR.AMOUNT
692 ,0)
693 ,0)),
694 SUM(DECODE(LEAST(DECODE(AGE
695 ,'EXPENDITURE_ITEM_DATE'
696 ,PE.COMPLETION_DATE
697 ,'GL_DATE'
698 ,PDR.GL_DATE
699 ,PDR.PA_DATE)
700 ,DATE1 - 1)
701 ,DECODE(AGE
702 ,'EXPENDITURE_ITEM_DATE'
703 ,PE.COMPLETION_DATE
704 ,'GL_DATE'
705 ,PDR.GL_DATE
706 ,PDR.PA_DATE)
707 ,DECODE(LEAST(DECODE(AGE
708 ,'EXPENDITURE_ITEM_DATE'
709 ,PE.COMPLETION_DATE
710 ,'GL_DATE'
711 ,PDR.GL_DATE
712 ,PDR.PA_DATE)
713 ,DATE2)
714 ,DATE2
715 ,NVL(PCR.AMOUNT
716 ,0)
717 ,0)
718 ,0)),
719 SUM(DECODE(LEAST(DECODE(AGE
720 ,'EXPENDITURE_ITEM_DATE'
721 ,PE.COMPLETION_DATE
722 ,'GL_DATE'
723 ,PDR.GL_DATE
724 ,PDR.PA_DATE)
725 ,DATE2 - 1)
726 ,DECODE(AGE
727 ,'EXPENDITURE_ITEM_DATE'
728 ,PE.COMPLETION_DATE
729 ,'GL_DATE'
730 ,PDR.GL_DATE
731 ,PDR.PA_DATE)
732 ,DECODE(LEAST(DECODE(AGE
733 ,'EXPENDITURE_ITEM_DATE'
734 ,PE.COMPLETION_DATE
735 ,'GL_DATE'
736 ,PDR.GL_DATE
737 ,PDR.PA_DATE)
738 ,DATE3)
739 ,DATE3
740 ,NVL(PCR.AMOUNT
741 ,0)
742 ,0)
743 ,0)),
744 SUM(DECODE(LEAST(DECODE(AGE
745 ,'EXPENDITURE_ITEM_DATE'
746 ,PE.COMPLETION_DATE
747 ,'GL_DATE'
748 ,PDR.GL_DATE
749 ,PDR.PA_DATE)
750 ,DATE3 - 1)
751 ,DECODE(AGE
752 ,'EXPENDITURE_ITEM_DATE'
753 ,PE.COMPLETION_DATE
754 ,'GL_DATE'
755 ,PDR.GL_DATE
756 ,PDR.PA_DATE)
757 ,NVL(PCR.AMOUNT
758 ,0)
759 ,0))
760 FROM
761 PA_PROJECTS P,
762 PA_PROJECT_PLAYERS PL,
763 PA_EVENTS PE,
764 PA_EVENT_TYPES PET,
765 PA_DRAFT_REVENUES PDR,
766 PA_CUST_EVENT_REV_DIST_LINES PCR
767 WHERE P.CARRYING_OUT_ORGANIZATION_ID between NVL(P_ORG_ID
768 ,0)
769 AND NVL(P_ORG_ID
770 ,999999999999999)
771 AND P.PROJECT_ID between NVL(PROJ
772 ,0)
773 AND NVL(PROJ
774 ,999999999999999)
775 AND PL.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
776 AND NVL(DATE_FROM
777 ,SYSDATE) between PL.START_DATE_ACTIVE
778 AND NVL(PL.END_DATE_ACTIVE
779 ,NVL(DATE_FROM
780 ,SYSDATE + 1))
781 AND PL.PERSON_ID between NVL(PROJECT_MANAGER_ID
782 ,0)
783 AND NVL(PROJECT_MANAGER_ID
784 ,999999999999999)
785 AND P.PROJECT_ID = PL.PROJECT_ID
786 AND PE.REVENUE_DISTRIBUTED_FLAG || '' = 'Y'
787 AND PE.PROJECT_ID = P.PROJECT_ID
788 AND PCR.PROJECT_ID = PDR.PROJECT_ID
789 AND PCR.DRAFT_REVENUE_NUM = PDR.DRAFT_REVENUE_NUM
790 AND PDR.RELEASED_DATE IS NOT NULL
791 AND DECODE(AGE
792 ,'EXPENDITURE_ITEM_DATE'
793 ,PE.COMPLETION_DATE
794 ,'GL_DATE'
795 ,PDR.GL_DATE
796 ,PDR.PA_DATE) <= NVL(DATE_FROM
797 ,SYSDATE)
798 AND NOT EXISTS (
799 SELECT
800 'x'
801 FROM
802 PA_DRAFT_INVOICES PDI
803 WHERE PCR.PROJECT_ID = PDI.PROJECT_ID
804 AND PCR.DRAFT_INVOICE_NUM = PDI.DRAFT_INVOICE_NUM
805 AND PDI.RELEASED_DATE IS NOT NULL )
806 AND PE.EVENT_TYPE = PET.EVENT_TYPE
807 AND PET.EVENT_TYPE_CLASSIFICATION in ( 'WRITE ON' , 'MANUAL' , 'AUTOMATIC' , 'WRITE OFF' )
808 AND PE.REVENUE_AMOUNT is not null
809 AND PE.PROJECT_ID = PCR.PROJECT_ID
810 AND NVL(PE.TASK_ID
811 ,-1) = NVL(PCR.TASK_ID
812 ,-1)
813 AND PE.EVENT_NUM = PCR.EVENT_NUM
814 AND P.PROJECT_ID = TU.PROJECT_ID);
815 END UPDATE_EVENTS;
816
817 PROCEDURE UPDATE_FOR_CONCESSION IS
818 L_COUNT NUMBER;
819 BEGIN
820 SELECT
821 count(*)
822 INTO L_COUNT
823 FROM
824 DUAL
825 WHERE EXISTS (
826 SELECT
827 1
828 FROM
829 PA_PROJECTS P,
830 PA_PROJECT_PLAYERS PL,
831 PA_DRAFT_INVOICES PDI,
832 PA_DRAFT_INVOICE_ITEMS PDII
833 WHERE P.CARRYING_OUT_ORGANIZATION_ID between NVL(P_ORG_ID
834 ,0)
835 AND NVL(P_ORG_ID
836 ,999999999999999)
837 AND P.PROJECT_ID between NVL(PROJ
838 ,0)
839 AND NVL(PROJ
840 ,999999999999999)
841 AND PL.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
842 AND NVL(DATE_FROM
843 ,SYSDATE) between PL.START_DATE_ACTIVE
844 AND NVL(PL.END_DATE_ACTIVE
845 ,NVL(DATE_FROM
846 ,SYSDATE + 1))
847 AND PL.PERSON_ID between NVL(PROJECT_MANAGER_ID
848 ,0)
849 AND NVL(PROJECT_MANAGER_ID
850 ,999999999999999)
851 AND P.PROJECT_ID = PL.PROJECT_ID
852 AND PDI.PROJECT_ID = P.PROJECT_ID
853 AND PDII.PROJECT_ID = PDI.PROJECT_ID
854 AND PDI.DRAFT_INVOICE_NUM = PDII.DRAFT_INVOICE_NUM
855 AND PDI.RELEASED_DATE is NOT NULL
856 AND DECODE(AGE
857 ,'EXPENDITURE_ITEM_DATE'
858 ,PDI.INVOICE_DATE
859 ,'GL_DATE'
860 ,PDI.GL_DATE
861 ,PDI.PA_DATE) <= NVL(DATE_FROM
862 ,SYSDATE)
863 AND PDI.CONCESSION_FLAG = 'Y'
864 AND PDII.INVOICE_LINE_TYPE <> 'RETENTION' );
865 IF L_COUNT = 0 THEN
866 NULL;
867 ELSE
868 UPDATE
869 PA_UNBILLED_REC_REPORTING TU
870 SET
871 (EVENT_BUCKET1,EVENT_BUCKET2,EVENT_BUCKET3,EVENT_BUCKET4) = (SELECT
872 NVL(EVENT_BUCKET1
873 ,0) + SUM(DECODE(LEAST(DECODE(AGE
874 ,'EXPENDITURE_ITEM_DATE'
875 ,PDI.INVOICE_DATE
876 ,'GL_DATE'
877 ,PDI.GL_DATE
878 ,PDI.PA_DATE)
879 ,DATE1)
880 ,DATE1
881 ,NVL(PDII.PROJFUNC_BILL_AMOUNT
882 ,0)
883 ,0)) * - 1,
884 NVL(EVENT_BUCKET2
885 ,0) + SUM(DECODE(LEAST(DECODE(AGE
886 ,'EXPENDITURE_ITEM_DATE'
887 ,PDI.INVOICE_DATE
888 ,'GL_DATE'
889 ,PDI.GL_DATE
890 ,PDI.PA_DATE)
891 ,DATE1 - 1)
892 ,DECODE(AGE
893 ,'EXPENDITURE_ITEM_DATE'
894 ,PDI.INVOICE_DATE
895 ,'GL_DATE'
896 ,PDI.GL_DATE
897 ,PDI.PA_DATE)
898 ,DECODE(LEAST(DECODE(AGE
899 ,'EXPENDITURE_ITEM_DATE'
900 ,PDI.INVOICE_DATE
901 ,'GL_DATE'
902 ,PDI.GL_DATE
903 ,PDI.PA_DATE)
904 ,DATE2)
905 ,DATE2
906 ,NVL(PDII.PROJFUNC_BILL_AMOUNT
907 ,0)
908 ,0)
909 ,0)) * - 1,
910 NVL(EVENT_BUCKET3
911 ,0) + SUM(DECODE(LEAST(DECODE(AGE
912 ,'EXPENDITURE_ITEM_DATE'
913 ,PDI.INVOICE_DATE
914 ,'GL_DATE'
915 ,PDI.GL_DATE
916 ,PDI.PA_DATE)
917 ,DATE2 - 1)
918 ,DECODE(AGE
919 ,'EXPENDITURE_ITEM_DATE'
920 ,PDI.INVOICE_DATE
921 ,'GL_DATE'
922 ,PDI.GL_DATE
923 ,PDI.PA_DATE)
924 ,DECODE(LEAST(DECODE(AGE
925 ,'EXPENDITURE_ITEM_DATE'
926 ,PDI.INVOICE_DATE
927 ,'GL_DATE'
928 ,PDI.GL_DATE
929 ,PDI.PA_DATE)
930 ,DATE3)
931 ,DATE3
932 ,NVL(PDII.PROJFUNC_BILL_AMOUNT
933 ,0)
934 ,0)
935 ,0)) * - 1,
936 NVL(EVENT_BUCKET4
937 ,0) + SUM(DECODE(LEAST(DECODE(AGE
938 ,'EXPENDITURE_ITEM_DATE'
939 ,PDI.INVOICE_DATE
940 ,'GL_DATE'
941 ,PDI.GL_DATE
942 ,PDI.PA_DATE)
943 ,DATE3 - 1)
944 ,DECODE(AGE
945 ,'EXPENDITURE_ITEM_DATE'
946 ,PDI.INVOICE_DATE
947 ,'GL_DATE'
948 ,PDI.GL_DATE
949 ,PDI.PA_DATE)
950 ,NVL(PDII.PROJFUNC_BILL_AMOUNT
951 ,0)
952 ,0)) * - 1
953 FROM
954 PA_PROJECTS P,
955 PA_PROJECT_PLAYERS PL,
956 PA_DRAFT_INVOICES PDI,
957 PA_DRAFT_INVOICE_ITEMS PDII
958 WHERE P.CARRYING_OUT_ORGANIZATION_ID between NVL(P_ORG_ID
959 ,0)
960 AND NVL(P_ORG_ID
961 ,999999999999999)
962 AND P.PROJECT_ID between NVL(PROJ
963 ,0)
964 AND NVL(PROJ
965 ,999999999999999)
966 AND PL.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
967 AND NVL(DATE_FROM
968 ,SYSDATE) between PL.START_DATE_ACTIVE
969 AND NVL(PL.END_DATE_ACTIVE
970 ,NVL(DATE_FROM
971 ,SYSDATE + 1))
972 AND PL.PERSON_ID between NVL(PROJECT_MANAGER_ID
973 ,0)
974 AND NVL(PROJECT_MANAGER_ID
975 ,999999999999999)
976 AND P.PROJECT_ID = PL.PROJECT_ID
977 AND PDI.PROJECT_ID = P.PROJECT_ID
978 AND PDII.PROJECT_ID = PDI.PROJECT_ID
979 AND PDI.DRAFT_INVOICE_NUM = PDII.DRAFT_INVOICE_NUM
980 AND PDI.RELEASED_DATE is NOT NULL
981 AND DECODE(AGE
982 ,'EXPENDITURE_ITEM_DATE'
983 ,PDI.INVOICE_DATE
984 ,'GL_DATE'
985 ,PDI.GL_DATE
986 ,PDI.PA_DATE) <= NVL(DATE_FROM
987 ,SYSDATE)
988 AND PDI.CONCESSION_FLAG = 'Y'
989 AND PDII.INVOICE_LINE_TYPE <> 'RETENTION'
990 AND P.PROJECT_ID = TU.PROJECT_ID);
991 END IF;
992 END UPDATE_FOR_CONCESSION;
993
994 PROCEDURE UPDATE_INVOICE_EVENTS IS
995 BEGIN
996 UPDATE
997 PA_UNBILLED_REC_REPORTING TU
998 SET
999 EVENT_INV_AMOUNT = (SELECT
1000 SUM(TO_NUMBER(DECODE(PET.EVENT_TYPE_CLASSIFICATION
1001 ,'WRITE OFF'
1002 ,TO_CHAR(NVL(PE.REVENUE_AMOUNT
1003 ,0))
1004 ,TO_CHAR(NVL(PDII.PROJFUNC_BILL_AMOUNT
1005 ,0)))))
1006 FROM
1007 PA_PROJECTS P,
1008 PA_PROJECT_PLAYERS PL,
1009 PA_EVENT_TYPES PET,
1010 PA_EVENTS PE,
1011 PA_DRAFT_INVOICES PDI,
1012 PA_DRAFT_INVOICE_ITEMS PDII
1013 WHERE PDI.RELEASED_DATE IS NOT NULL
1014 AND PDI.PROJECT_ID = PDII.PROJECT_ID
1015 AND PDI.PROJECT_ID = P.PROJECT_ID
1016 AND PDI.DRAFT_INVOICE_NUM = PDII.DRAFT_INVOICE_NUM
1017 AND PDII.PROJECT_ID = PE.PROJECT_ID
1018 AND PDI.CANCELED_FLAG is null
1019 AND PDI.CANCEL_CREDIT_MEMO_FLAG is null
1020 AND NVL(PDII.EVENT_TASK_ID
1021 ,-1) = NVL(PE.TASK_ID
1022 ,-1)
1023 AND PDII.EVENT_NUM = PE.EVENT_NUM
1024 AND P.CARRYING_OUT_ORGANIZATION_ID between NVL(P_ORG_ID
1025 ,0)
1026 AND NVL(P_ORG_ID
1027 ,999999999999999)
1028 AND P.PROJECT_ID between NVL(PROJ
1029 ,0)
1030 AND NVL(PROJ
1031 ,999999999999999)
1032 AND PL.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
1033 AND NVL(DATE_FROM
1034 ,SYSDATE) between PL.START_DATE_ACTIVE
1035 AND NVL(PL.END_DATE_ACTIVE
1036 ,NVL(DATE_FROM
1037 ,SYSDATE + 1))
1038 AND PL.PERSON_ID between NVL(PROJECT_MANAGER_ID
1039 ,0)
1040 AND NVL(PROJECT_MANAGER_ID
1041 ,999999999999999)
1042 AND P.PROJECT_ID = PL.PROJECT_ID
1043 AND PE.EVENT_TYPE = PET.EVENT_TYPE
1044 AND PET.EVENT_TYPE_CLASSIFICATION || '' in ( 'WRITE OFF' , 'SCHEDULED PAYMENTS' , 'MANUAL' , 'DEFERRED REVENUE' , 'AUTOMATIC' )
1045 AND P.PROJECT_ID = PE.PROJECT_ID
1046 AND ( ( PET.EVENT_TYPE_CLASSIFICATION || '' in ( 'SCHEDULED PAYMENTS' , 'MANUAL' , 'DEFERRED REVENUE' , 'AUTOMATIC' )
1047 AND EXISTS (
1048 SELECT
1049 'event accepted'
1050 FROM
1051 PA_DRAFT_INVOICES PDI,
1052 PA_DRAFT_INVOICE_ITEMS PDII
1053 WHERE PDI.RELEASED_DATE IS NOT NULL
1054 AND PDI.PROJECT_ID = PDII.PROJECT_ID
1055 AND PDI.PROJECT_ID = P.PROJECT_ID
1056 AND PDI.DRAFT_INVOICE_NUM = PDII.DRAFT_INVOICE_NUM
1057 AND PDII.PROJECT_ID = PE.PROJECT_ID
1058 AND PDI.CANCELED_FLAG is null
1059 AND PDI.CANCEL_CREDIT_MEMO_FLAG is null
1060 AND NVL(PDII.EVENT_TASK_ID
1061 ,-1) = NVL(PE.TASK_ID
1062 ,-1)
1063 AND PDII.EVENT_NUM = PE.EVENT_NUM ) )
1064 OR ( PET.EVENT_TYPE_CLASSIFICATION || '' = 'WRITE OFF' )
1065 AND EXISTS (
1066 SELECT
1067 'event accepted'
1068 FROM
1069 PA_DRAFT_REVENUES PDR,
1070 PA_DRAFT_INVOICES PDI,
1071 PA_CUST_EVENT_REV_DIST_LINES PCR
1072 WHERE PDR.RELEASED_DATE IS NOT NULL
1073 AND PDR.PROJECT_ID = PCR.PROJECT_ID
1074 AND PCR.DRAFT_INVOICE_NUM = pdi.draft_invoice_num (+)
1075 AND PCR.PROJECT_ID = pdi.project_id (+)
1076 AND DECODE(PDI.RELEASED_DATE
1077 ,NULL
1078 ,1
1079 ,0) = 1
1080 AND PDR.PROJECT_ID = P.PROJECT_ID
1081 AND PDR.DRAFT_REVENUE_NUM = PCR.DRAFT_REVENUE_NUM
1082 AND PCR.PROJECT_ID = PE.PROJECT_ID
1083 AND NVL(PCR.TASK_ID
1084 ,-1) = NVL(PE.TASK_ID
1085 ,-1)
1086 AND PCR.EVENT_NUM = PE.EVENT_NUM ) )
1087 AND P.PROJECT_ID = TU.PROJECT_ID
1088 AND PE.COMPLETION_DATE <= NVL(DATE_FROM
1089 ,SYSDATE));
1090 END UPDATE_INVOICE_EVENTS;
1091
1092 PROCEDURE UPDATE_INVOICE_REDUCTION IS
1093 BEGIN
1094 UPDATE
1095 PA_UNBILLED_REC_REPORTING TU
1096 SET
1097 COST_WORK_AMOUNT = (SELECT
1098 SUM(PCR.PROJFUNC_BILL_AMOUNT)
1099 FROM
1100 PA_PROJECTS P,
1101 PA_PROJECT_PLAYERS PL,
1102 PA_CUST_REV_DIST_LINES PCR,
1103 PA_DRAFT_INVOICES PDI
1104 WHERE P.CARRYING_OUT_ORGANIZATION_ID between NVL(P_ORG_ID
1105 ,0)
1106 AND NVL(P_ORG_ID
1107 ,999999999999999)
1108 AND P.PROJECT_ID between NVL(PROJ
1109 ,0)
1110 AND NVL(PROJ
1111 ,999999999999999)
1112 AND PL.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
1113 AND NVL(DATE_FROM
1114 ,SYSDATE) between PL.START_DATE_ACTIVE
1115 AND NVL(PL.END_DATE_ACTIVE
1116 ,NVL(DATE_FROM
1117 ,SYSDATE + 1))
1118 AND PL.PERSON_ID between NVL(PROJECT_MANAGER_ID
1119 ,0)
1120 AND NVL(PROJECT_MANAGER_ID
1121 ,999999999999999)
1122 AND P.PROJECT_ID = PL.PROJECT_ID
1123 AND P.PROJECT_ID = PDI.PROJECT_ID
1124 AND PCR.PROJECT_ID = PDI.PROJECT_ID
1125 AND PCR.DRAFT_INVOICE_NUM = PDI.DRAFT_INVOICE_NUM
1126 AND PDI.RELEASED_DATE IS NOT NULL
1127 AND PCR.PROJFUNC_BILL_AMOUNT <> PCR.AMOUNT
1128 AND P.PROJECT_ID = TU.PROJECT_ID);
1129 END UPDATE_INVOICE_REDUCTION;
1130
1131 PROCEDURE UPDATE_RETENTION IS
1132 BEGIN
1133 UPDATE
1134 PA_UNBILLED_REC_REPORTING TU
1135 SET
1136 INVOICE_REDUCTION = (SELECT
1137 SUM(PE.PROJFUNC_BILL_AMOUNT)
1138 FROM
1139 PA_PROJECTS P,
1140 PA_PROJECT_PLAYERS PL,
1141 PA_EVENT_TYPES PET,
1142 PA_EVENTS PE
1143 WHERE P.CARRYING_OUT_ORGANIZATION_ID between NVL(P_ORG_ID
1144 ,0)
1145 AND NVL(P_ORG_ID
1146 ,999999999999999)
1147 AND P.PROJECT_ID between NVL(PROJ
1148 ,0)
1149 AND NVL(PROJ
1150 ,999999999999999)
1151 AND PL.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
1152 AND NVL(DATE_FROM
1153 ,SYSDATE) between PL.START_DATE_ACTIVE
1154 AND NVL(PL.END_DATE_ACTIVE
1155 ,NVL(DATE_FROM
1156 ,SYSDATE + 1))
1157 AND PL.PERSON_ID between NVL(PROJECT_MANAGER_ID
1158 ,0)
1159 AND NVL(PROJECT_MANAGER_ID
1160 ,999999999999999)
1161 AND P.PROJECT_ID = PL.PROJECT_ID
1162 AND P.PROJECT_ID = PE.PROJECT_ID
1163 AND PE.EVENT_TYPE = PET.EVENT_TYPE
1164 AND PET.EVENT_TYPE_CLASSIFICATION || '' = 'INVOICE REDUCTION'
1165 AND ( PE.PROJECT_ID , NVL(PE.TASK_ID
1166 ,-1) , PE.EVENT_NUM ) in (
1167 SELECT
1168 PDII.PROJECT_ID,
1169 NVL(PDII.EVENT_TASK_ID
1170 ,-1),
1171 PDII.EVENT_NUM
1172 FROM
1173 PA_DRAFT_INVOICES PDI,
1174 PA_DRAFT_INVOICE_ITEMS PDII
1175 WHERE PDI.RELEASED_DATE IS NOT NULL
1176 AND PDI.PROJECT_ID = PDII.PROJECT_ID
1177 AND PDI.PROJECT_ID = P.PROJECT_ID
1178 AND PDI.CANCELED_FLAG is null
1179 AND PDI.CANCEL_CREDIT_MEMO_FLAG is null
1180 AND PDI.DRAFT_INVOICE_NUM = PDII.DRAFT_INVOICE_NUM )
1181 AND P.PROJECT_ID = TU.PROJECT_ID
1182 AND PE.COMPLETION_DATE <= NVL(DATE_FROM
1183 ,SYSDATE));
1184 END UPDATE_RETENTION;
1185
1186 FUNCTION CALC_BUCKET1(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER,INVOICE_REDUCTION IN NUMBER,RETENTION IN NUMBER) RETURN NUMBER IS
1187 SUB_AMOUNT NUMBER := EVENT_INVOICED_AMOUNT + COST_WORK_AMOUNT;
1188 TEMP_BUCKET1 NUMBER;
1189 FINAL_BUCKET1 NUMBER;
1190 BEGIN
1191 SELECT
1192 BUCKET1 - DECODE(SIGN(DECODE(SIGN(DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1193 ,-1
1194 ,0
1195 ,SUB_AMOUNT - BUCKET4) - BUCKET3)
1196 ,-1
1197 ,0
1198 ,DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1199 ,-1
1200 ,0
1201 ,SUB_AMOUNT - BUCKET4) - BUCKET3) - BUCKET2)
1202 ,-1
1203 ,0
1204 ,DECODE(SIGN(DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1205 ,-1
1206 ,0
1207 ,SUB_AMOUNT - BUCKET4) - BUCKET3)
1208 ,-1
1209 ,0
1210 ,DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1211 ,-1
1212 ,0
1213 ,SUB_AMOUNT - BUCKET4) - BUCKET3) - BUCKET2)
1214 INTO TEMP_BUCKET1
1215 FROM
1216 SYS.DUAL;
1217 FINAL_BUCKET1 := TEMP_BUCKET1 + INVOICE_REDUCTION + RETENTION;
1218 RETURN FINAL_BUCKET1;
1219 END CALC_BUCKET1;
1220
1221 FUNCTION CALC_BUCKET2(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER) RETURN NUMBER IS
1222 SUB_AMOUNT NUMBER := EVENT_INVOICED_AMOUNT + COST_WORK_AMOUNT;
1223 FINAL_BUCKET2 NUMBER;
1224 BEGIN
1225 SELECT
1226 DECODE(SIGN(BUCKET2 - DECODE(SIGN(DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1227 ,-1
1228 ,0
1229 ,SUB_AMOUNT - BUCKET4) - BUCKET3)
1230 ,-1
1231 ,0
1232 ,DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1233 ,-1
1234 ,0
1235 ,SUB_AMOUNT - BUCKET4) - BUCKET3))
1236 ,-1
1237 ,0
1238 ,BUCKET2 - DECODE(SIGN(DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1239 ,-1
1240 ,0
1241 ,SUB_AMOUNT - BUCKET4) - BUCKET3)
1242 ,-1
1243 ,0
1244 ,DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1245 ,-1
1246 ,0
1247 ,SUB_AMOUNT - BUCKET4) - BUCKET3))
1248 INTO FINAL_BUCKET2
1249 FROM
1250 SYS.DUAL;
1251 RETURN FINAL_BUCKET2;
1252 END CALC_BUCKET2;
1253
1254 FUNCTION CALC_BUCKET3(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER) RETURN NUMBER IS
1255 SUB_AMOUNT NUMBER := EVENT_INVOICED_AMOUNT + COST_WORK_AMOUNT;
1256 FINAL_BUCKET3 NUMBER;
1257 BEGIN
1258 SELECT
1259 DECODE(SIGN(BUCKET3 - DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1260 ,-1
1261 ,0
1262 ,SUB_AMOUNT - BUCKET4))
1263 ,-1
1264 ,0
1265 ,BUCKET3 - DECODE(SIGN(SUB_AMOUNT - BUCKET4)
1266 ,-1
1267 ,0
1268 ,SUB_AMOUNT - BUCKET4))
1269 INTO FINAL_BUCKET3
1270 FROM
1271 DUAL;
1272 RETURN FINAL_BUCKET3;
1273 END CALC_BUCKET3;
1274
1275 FUNCTION CALC_BUCKET4(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER) RETURN NUMBER IS
1276 SUB_AMOUNT NUMBER := EVENT_INVOICED_AMOUNT + COST_WORK_AMOUNT;
1277 FINAL_BUCKET4 NUMBER;
1278 BEGIN
1279 SELECT
1280 DECODE(SIGN(BUCKET4 - SUB_AMOUNT)
1281 ,-1
1282 ,0
1283 ,BUCKET4 - SUB_AMOUNT)
1284 INTO FINAL_BUCKET4
1285 FROM
1286 DUAL;
1287 RETURN FINAL_BUCKET4;
1288 END CALC_BUCKET4;
1289
1290 FUNCTION CALC_TOTAL_BUCKETS(EVENT_INVOICED_AMOUNT IN NUMBER,COST_WORK_AMOUNT IN NUMBER,BUCKET1 IN NUMBER,BUCKET2 IN NUMBER,BUCKET3 IN NUMBER,BUCKET4 IN NUMBER,INVOICE_REDUCTION IN NUMBER,RETENTION IN NUMBER) RETURN NUMBER IS
1291 BEGIN
1292 RETURN CALC_BUCKET1(EVENT_INVOICED_AMOUNT,COST_WORK_AMOUNT,BUCKET1,BUCKET2,BUCKET3,BUCKET4,INVOICE_REDUCTION,RETENTION) +
1293 CALC_BUCKET2(EVENT_INVOICED_AMOUNT,COST_WORK_AMOUNT,BUCKET1,BUCKET2,BUCKET3,BUCKET4) +
1294 CALC_BUCKET3(EVENT_INVOICED_AMOUNT,COST_WORK_AMOUNT,BUCKET1,BUCKET2,BUCKET3,BUCKET4) + CALC_BUCKET4(EVENT_INVOICED_AMOUNT,COST_WORK_AMOUNT,BUCKET1,BUCKET2,BUCKET3,BUCKET4);
1295 END CALC_TOTAL_BUCKETS;
1296
1297 PROCEDURE INIT_PA_UNBILLED_REC_REPORTING IS
1298 CURSOR C IS
1299 SELECT
1300 PROJECT_ID
1301 FROM
1302 PA_PROJECTS
1303 WHERE PROJECT_ID between NVL(PROJ
1304 ,0)
1305 AND NVL(PROJ
1306 ,999999999999999);
1307 BEGIN
1308 INSERT INTO PA_UNBILLED_REC_REPORTING
1309 (PROJECT_ID)
1310 SELECT
1311 P.PROJECT_ID
1312 FROM
1313 PA_PROJECTS P
1314 WHERE P.PROJECT_ID between NVL(PROJ
1315 ,0)
1316 AND NVL(PROJ
1317 ,999999999999999)
1318 AND not exists (
1319 SELECT
1320 'xyz'
1321 FROM
1322 PA_UNBILLED_REC_REPORTING TU
1323 WHERE TU.PROJECT_ID = P.PROJECT_ID );
1324 END INIT_PA_UNBILLED_REC_REPORTING;
1325
1326 PROCEDURE POPULATE IS
1327 BEGIN
1328 SELECT_DATES;
1329 INSERT_EIS;
1330 INIT_PA_UNBILLED_REC_REPORTING;
1331 UPDATE_EVENTS;
1332 UPDATE_FOR_CONCESSION;
1333 UPDATE_INVOICE_EVENTS;
1334 UPDATE_INVOICE_REDUCTION;
1335 UPDATE_RETENTION;
1336 END POPULATE;
1337 END PA_PAXMGURA_XMLP_PKG ;
1338
1339