1 PACKAGE BODY pa_efc_bil AS
2 /* $Header: PAEFCBLB.pls 120.2.12020000.2 2012/07/19 09:33:22 admarath ship $ */
3 PROCEDURE Get_B_Ub_Rev_Inv_Amts(p_project_id IN NUMBER,
4 p_task_id IN NUMBER,
5 p_agreement_id IN NUMBER,
6 p_baselined IN OUT NOCOPY NUMBER,
7 p_ubaselined IN OUT NOCOPY NUMBER,
8 p_billed IN OUT NOCOPY NUMBER,
9 p_accr_rev IN OUT NOCOPY NUMBER,
10 p_adjust_amt OUT NOCOPY NUMBER,
11 p_rev_limit_flag IN VARCHAR2) IS
12 pl_baselined NUMBER:=0;
13 pl_unbaselined NUMBER:=0;
14 pl_billed NUMBER:=0;
15 pl_accr_rev NUMBER:=0;
16 pl_adjusted NUMBER:=0;
17
18 -- this cursor will bring the BASELINE and DRAFT amounts
19
20 CURSOR spf_bas_amt(pl_project_id NUMBER,
21 pl_task_id NUMBER,
22 pl_agreement_id NUMBER) IS
23 SELECT f.budget_type_code budget_type_code,
24 NVL(SUM(f.allocated_amount),0) tot_amt
25 FROM pa_project_fundings f
26 WHERE f.project_id = pl_project_id
27 AND f.agreement_id = pl_agreement_id
28 AND nvl(f.task_id,-99) = nvl(pl_task_id,-99)
29 GROUP BY f.budget_type_code;
30
31 BEGIN
32
33 -- get the baselined amount and unbaselined amount
34
35 FOR rec_spf_bas_amt IN spf_bas_amt(p_project_id,
36 p_task_id,
37 p_agreement_id) LOOP
38
39 IF rec_spf_bas_amt.budget_type_code = 'BASELINE' THEN
40
41 pl_baselined := rec_spf_bas_amt.tot_amt;
42
43 /* The budget_type_code 'ORIGINAL' should not be considered hence below change */
44
45 ELSIF rec_spf_bas_amt.budget_type_code = 'DRAFT' THEN /* added draft for 2001272 */
46
47 pl_unbaselined := rec_spf_bas_amt.tot_amt;
48
49 END IF;
50
51 END LOOP;
52
53
54
55 IF NVL(p_accr_rev,0) <> 0 THEN
56
57 -- Get the total accrued revenue amount
58
59 SELECT NVL(SUM(dri.amount),0) dri_amount
60 INTO pl_accr_rev
61 FROM pa_draft_revenue_items dri,
62 pa_draft_revenues_all dr
63 WHERE dri.project_id = dr.project_id
64 AND dri.draft_revenue_num = dr.draft_revenue_num
65 AND ( NVL(p_task_id,0) = 0
66 OR dri.task_id = p_task_id )
67 AND dr.project_id = p_project_id
68 AND dr.agreement_id = p_agreement_id;
69
70 END IF;
71
72 IF NVL(p_billed,0) <> 0 AND NVL(p_task_id,0) =0 THEN
73
74 -- get the total accrued revenue for project level funding
75
76 SELECT sum(dii.amount) dii_amount
77 INTO pl_billed
78 FROM pa_draft_invoice_items dii,
79 pa_draft_invoices_all di
80 WHERE dii.project_id = di.project_id
81 AND dii.draft_invoice_num = di.draft_invoice_num
82 AND di.project_id = p_project_id
83 AND di.agreement_id = p_agreement_id
84 AND dii.invoice_line_type <> 'RETENTION';
85
86 ELSIF NVL(p_billed,0) <> 0 AND NVL(p_task_id,0) <> 0 THEN
87
88 -- get the total accrued revenue for task level funding
89
90 SELECT round(sum(dii.amount * (1 -
91 ( nvl(di.retention_percentage,0)/100 )) ),2) dii_amount
92 INTO pl_billed
93 FROM pa_draft_invoice_items dii,
94 pa_draft_invoices_all di
95 WHERE dii.project_id = di.project_id
96 AND dii.draft_invoice_num = di.draft_invoice_num
97 AND dii.task_id = p_task_id
98 AND dii.invoice_line_type <> 'RETENTION'
99 AND di.project_id = p_project_id
100 AND di.agreement_id = p_agreement_id;
101
102 END IF;
103
104 /* Commented for bug 2000454
105 This is commented because it is possible in projects to have accrued and billed amounts
106 greater than zero even if baselined amount is zero for the agreement. This is possible becasuse
107 of a bug in agreement form which allows to reduce funding below amount accrued or billed against
108 this agreement.
109
110
111 IF NVL(pl_baselined,0) = 0 THEN
112
113 -- All case if there is no baselined amount, billed and accrued amount should be zero
114
115 pl_billed := 0;
116 pl_accr_rev := 0;
117
118 END IF;
119 */
120
121 IF NVL(p_rev_limit_flag,'N') = 'Y' THEN
122
123
124 IF NVL(pl_baselined,0) <> 0 AND
125 NVL(pl_baselined,0) < GREATEST(NVL(pl_billed,0),NVL(pl_accr_rev,0)) THEN
126
127 pl_adjusted := GREATEST(NVL(pl_billed,0),NVL(pl_accr_rev,0))- NVL(pl_baselined,0);
128 pl_baselined := GREATEST(NVL(pl_billed,0),NVL(pl_accr_rev,0));
129
130
131 END IF;
132
133 END IF;
134
135 p_baselined := pl_baselined;
136 p_ubaselined := pl_unbaselined;
137 p_billed := pl_billed;
138 p_accr_rev := pl_accr_rev;
139 p_adjust_amt := pl_adjusted;
140
141
142 END Get_B_Ub_Rev_Inv_Amts;
143 /*---------------------------------------------------------------------------
144 | Procedure to update the adjusted amount in project funding table |
145 | and summary project funding efc table |
146 ----------------------------------------------------------------------------*/
147
148 PROCEDURE Update_Adjusted_Amount (p_project_id IN NUMBER,
149 p_agreement_id IN NUMBER,
150 p_task_id IN NUMBER,
151 p_adjusted IN NUMBER) IS
152
153 -- get the latest funding id to update the adjusted amount
154
155 CURSOR cur_adj IS
156 SELECT f.project_funding_id project_funding_id
157 FROM pa_project_fundings f
158 WHERE f.project_id = p_project_id
159 AND f.agreement_id = p_agreement_id
160 AND NVL(f.task_id,-99)= NVL(p_task_id,-99)
161 AND f.budget_type_code = 'BASELINE'
162 ORDER BY creation_date DESC;
163
164 rec_adj cur_adj%ROWTYPE;
165 update_flag BOOLEAN:= FALSE;
166
167 BEGIN
168 BEGIN
169 OPEN cur_adj;
170
171 LOOP
172 FETCH cur_adj INTO rec_adj;
173 EXIT WHEN cur_adj%NOTFOUND;
174
175 -- update the Project Funding record
176
177 UPDATE pa_project_fundings pf
178 SET pf.allocated_amount=(pf.allocated_amount+p_adjusted)
179 WHERE pf.project_funding_id = rec_adj.project_funding_id;
180
181 -- update the Project Funding EFC record
182
183 UPDATE pa_project_fundings_efc pfefc
184 SET pfefc.adjusted_amount=p_adjusted
185 WHERE pfefc.project_funding_id = rec_adj.project_funding_id;
186
187 update_flag := TRUE;
188
189 EXIT WHEN (update_flag);
190 END LOOP;
191
192 CLOSE cur_adj;
193 END;
194
195 BEGIN
196
197 -- update the Summary Project Funding EFC record
198
199 UPDATE pa_summary_proj_fundings_efc
200 SET adjusted_amount = p_adjusted
201 WHERE project_id = p_project_id
202 AND NVL(task_id,-99) = NVL(p_task_id,-99)
203 AND agreement_id = p_agreement_id;
204
205 END;
206
207 END Update_Adjusted_Amount ;
208
209 FUNCTION sum_mc_cust_rdl_erdl( p_project_id IN NUMBER,
210 p_draft_revenue_num IN NUMBER,
211 p_draft_revenue_item_line_num IN NUMBER,
212 p_set_of_books_id IN NUMBER) RETURN NUMBER IS
213 rdl_amt NUMBER;
214 erdl_amt NUMBER;
215 BEGIN
216
217 RETURN (nvl(rdl_amt,0) + nvl(erdl_amt,0));
218
219 EXCEPTION WHEN OTHERS THEN
220 RAISE;
221
222 END sum_mc_cust_rdl_erdl;
223
224
225 FUNCTION SUM_MC_CUST_RDL_ERDL2
226 (x_project_id IN NUMBER,
227 x_draft_revenue_num IN NUMBER,
228 x_set_of_books_id IN NUMBER)
229 RETURN NUMBER
230 IS
231 rdl_amt NUMBER;
232 erdl_amt NUMBER;
233 BEGIN
234 RETURN (nvl(rdl_amt,0) + nvl(erdl_amt,0));
235 EXCEPTION WHEN OTHERS THEN
236 RAISE;
237 END sum_mc_cust_rdl_erdl2;
238
239
240 END pa_efc_bil;
[Home] [Help]