1 PACKAGE BODY PA_BILLING_PROCESS_PKG AS
2 /* $Header: PABIPROB.pls 120.0.12010000.1 2008/11/12 12:58:50 nkapling noship $ */
3
4 PROCEDURE PA_PROCESS_REV_ADJ (pproject_id IN NUMBER,
5 pfromproj IN VARCHAR2,
6 ptoproj IN VARCHAR2,
7 pmass_gen IN NUMBER,
8 pacc_thru_dt IN DATE) is
9
10 mass_gen VARCHAR2(1);
11 acc_thru_dt DATE;
12 projectstatus BOOLEAN;
13 status BOOLEAN;
14 temp NUMBER(1);
15 distribution_rule VARCHAR2(4);
16 CURSOR cproj IS
17 SELECT p.*
18 FROM pa_projects p, pa_project_types t
19 WHERE ((segment1 BETWEEN pfromproj AND ptoproj)
20 OR project_id =pproject_id)
21 AND p.project_type = t.project_type
22 AND t.project_type_class_code = 'CONTRACT';
23
24 CURSOR ctask(cproject_id VARCHAR2) is
25 SELECT *
26 FROM pa_tasks a
27 WHERE project_id=cproject_id
28 AND (
29 (task_id=top_task_id
30 AND ready_to_distribute_flag ='Y'
31 )
32 OR (chargeable_flag ='Y'
33 AND EXISTS ( SELECT null
34 FROM pa_tasks b
35 WHERE b.task_id = a.top_task_id
36 AND b.ready_to_distribute_flag = 'Y'
37 )
38 )
39 );
40
41 CURSOR cspf(cproject_id VARCHAR2) is
42 SELECT /*+ INDEX(pf pa_summary_project_fundings_u1)*/ 1
43 FROM pa_summary_project_fundings spf
44 WHERE spf.project_id = cproject_id
45 AND nvl(spf.revproc_baselined_amount, 0) <> 0;
46
47
48 CURSOR cdri(cproject_id NUMBER) is
49 SELECT 1
50 FROM pa_draft_revenues dr
51 WHERE dr.project_id = cproject_id
52 AND dr.released_date IS NULL
53 AND dr.generation_error_flag = decode(pmass_gen, 1, 'Y',dr.generation_error_flag);
54
55
56 BEGIN
57 PA_MCB_INVOICE_PKG.log_message('... Enter the procedure pa_process_rev_adj');
58 IF pmass_gen =1 THEN
59 mass_gen := 'N';
60 ELSE
61 mass_gen := 'E';
62 END IF;
63
64 IF pacc_thru_dt IS NULL THEN
65 acc_thru_dt := SYSDATE;
66 ELSE
67 acc_thru_dt := pacc_thru_dt;
68 END IF;
69 IF pa_debug_mode = 'Y' THEN
70 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Start Projects Loop');
71 END IF;
72 FOR Rproj IN Cproj
73 LOOP
74 -- project loop
75 IF pa_debug_mode = 'Y' THEN
76 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Project Id :'||Rproj.project_id);
77 END IF;
78 projectstatus := FALSE;
79 OPEN cspf(Rproj.project_id);
80 FETCH cspf INTO temp;
81 IF cspf%FOUND THEN
82 projectstatus := TRUE;
83 IF pa_debug_mode = 'Y' THEN
84 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Funding available ');
85 END IF;
86 ELSE
87 IF pa_debug_mode = 'Y' THEN
88 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Funding not available');
89 END IF;
90 END IF;
91 CLOSE cspf;
92 IF projectstatus THEN
93 OPEN cdri(Rproj.project_id);
94 FETCH cdri INTO temp;
95 IF cdri%FOUND THEN
96 projectstatus := FALSE;
97 END IF;
98 CLOSE cdri;
99 END IF;
100 IF projectstatus THEN
101 status := FALSE;
102 distribution_rule := SUBSTR(Rproj.distribution_rule,1,4);
103 IF pa_debug_mode = 'Y' THEN
104 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Start task Loop');
105 END IF;
106 FOR Rtask IN ctask(Rproj.project_id)
107 LOOP
108 -- task loop
109 IF pa_debug_mode = 'Y' THEN
110 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Task Id :'||Rtask.task_id);
111 END IF;
112 IF NOT (Rproj.enable_top_task_inv_mth_flag = 'N'
113 AND Rproj.distribution_rule = 'EVENT/EVENT'
114 )
115 AND Rtask.chargeable_flag = 'Y' THEN
116 -- for expenditures
117 IF pa_debug_mode = 'Y' THEN
118 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Checking for EIs');
119 END IF;
120 BEGIN
121 SELECT 1
122 INTO temp
123 FROM dual
124 WHERE EXISTS(
125 SELECT 1
126 FROM pa_expenditure_items_all ei
127 WHERE ei.project_id = Rtask.project_id
128 AND ei.task_id = Rtask.task_id
129 AND ei.cost_distributed_flag = 'Y'
130 AND ei.revenue_distributed_flag = 'N'
131 AND ei.expenditure_item_date <= acc_thru_dt
132 AND EXISTS( SELECT NULL
133 FROM pa_cust_rev_dist_lines crdl,pa_draft_revenues drx1
134 WHERE ei.project_id = crdl.project_id
135 AND ((ei.expenditure_item_id = crdl.expenditure_item_id)
136 OR (ei.adjusted_expenditure_item_id IS NOT NULL
137 AND ei.adjusted_expenditure_item_id = crdl.expenditure_item_id))
138 AND NVL(crdl.reversed_flag,'N') = 'N'
139 AND NVL(crdl.line_num_reversed,0) = 0
140 AND drx1.project_id = crdl.project_id
141 AND drx1.draft_revenue_num = crdl.draft_revenue_num
142 AND DECODE(drx1.generation_error_flag,mass_gen,decode(drx1.released_date,NULL,1,0),0)
143 = DECODE(drx1.released_date,NULL,1,0)
144 UNION ALL
145 SELECT 1
146 FROM pa_expenditure_items ei2
147 WHERE ei2.project_id = ei.project_id
148 AND ei2.expenditure_item_id = ei.adjusted_expenditure_item_id
149 AND ei2.revenue_distributed_flag = 'Y'
150 AND ei2.raw_revenue = 0
151 )
152 );
153 IF pa_debug_mode = 'Y' THEN
154 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:EI found');
155 END IF;
156 status := TRUE;
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 IF pa_debug_mode = 'Y' THEN
160 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:EI not found');
161 END IF;
162 status := FALSE;
163 END;
164 END IF;
165 IF (NOT status AND Rtask.task_id=Rtask.top_task_id) THEN
166 -- for events
167 IF pa_debug_mode = 'Y' THEN
168 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Check for Events');
169 END IF;
170 BEGIN
171 SELECT 1
172 INTO temp
173 FROM dual
174 WHERE
175 EXISTS(
176 SELECT 1
177 FROM pa_events ev,
178 pa_cust_event_rev_dist_lines erdl,
179 pa_draft_revenues drx
180 WHERE ev.project_id = Rtask.project_id
181 AND ev.task_id = Rtask.task_id
182 AND ((ev.revenue_distributed_flag = 'N'
183 AND ev.completion_date <= acc_thru_dt)
184 OR (distribution_rule = 'COST'
185 AND ev.revenue_distributed_flag = 'Y'
186 AND ev.completion_date > acc_thru_dt
187 )
188 )
189 AND nvl(ev.revenue_hold_flag, 'N') = 'N'
190 AND (decode(nvl(ev.bill_trans_rev_amount, 0), 0,
191 decode(nvl(ev.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
192 AND erdl.project_id = ev.project_id
193 AND erdl.event_num = ev.event_num
194 AND erdl.task_id = ev.task_id
195 AND nvl(erdl.reversed_flag,'N') = 'N'
196 AND erdl.line_num_reversed IS NULL
197 AND drx.project_id = erdl.project_id
198 AND drx.draft_revenue_num = erdl.draft_revenue_num
199 AND decode(drx.generation_error_flag,mass_gen,decode(drx.released_date,NULL,1,0),0)
200 = decode(drx.released_date,NULL,1,0)
201 );
202 IF pa_debug_mode = 'Y' THEN
203 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Event found');
204 END IF;
205 status := TRUE;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 IF pa_debug_mode = 'Y' THEN
209 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Event not found');
210 END IF;
211 status := FALSE;
212 END;
213 END IF;
214 IF Status THEN
215 -- if records found for that project for one task no need to check for other tasks
216 EXIT;
217 END IF;
218 END LOOP; -- task loop
219 IF pa_debug_mode = 'Y' THEN
220 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:End task Loop');
221 END IF;
222 IF NOT status THEN
223 IF pa_debug_mode = 'Y' THEN
224 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:checking for Project level events');
225 END IF;
226 -- For project level events
227 BEGIN
228 SELECT 1
229 INTO temp
230 FROM dual
231 WHERE EXISTS(
232 SELECT 1
233 FROM pa_events ev,
234 pa_cust_event_rev_dist_lines erdl,
235 pa_draft_revenues drx
236 WHERE ev.project_id = Rproj.project_id
237 AND ev.task_id IS NULL
238 AND ((ev.revenue_distributed_flag = 'N'
239 AND ev.completion_date <= acc_thru_dt)
240 OR (distribution_rule = 'COST'
241 AND ev.revenue_distributed_flag = 'Y'
242 AND ev.completion_date > acc_thru_dt
243 )
244 )
245 AND nvl(ev.revenue_hold_flag, 'N') = 'N'
246 AND (decode(nvl(ev.bill_trans_rev_amount, 0), 0,
247 decode(nvl(ev.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
248 AND erdl.project_id = ev.project_id
249 AND erdl.task_id IS NULL
250 AND erdl.event_num = ev.event_num
251 AND nvl(erdl.reversed_flag,'N') = 'N'
252 AND erdl.line_num_reversed IS NULL
253 AND drx.project_id = erdl.project_id
254 AND drx.draft_revenue_num = erdl.draft_revenue_num
255 AND decode(drx.generation_error_flag,mass_gen,decode(drx.released_date,NULL,1,0),0)
256 = decode(drx.released_date,NULL,1,0)
257 );
258 IF pa_debug_mode = 'Y' THEN
259 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Project level Event found');
260 END IF;
261 status := TRUE;
262 EXCEPTION
263 WHEN NO_DATA_FOUND THEN
264 IF pa_debug_mode = 'Y' THEN
265 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Project level Event not found');
266 END IF;
267 status := FALSE;
268 END;
269 END IF;
270 IF NOT status THEN
271 IF pa_debug_mode = 'Y' THEN
272 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Checking for Billing extensions');
273 END IF;
274 --Billing extension
275 BEGIN
276 SELECT 1
277 INTO temp
278 FROM dual
279 WHERE EXISTS(
280 SELECT 1
281 FROM pa_billing_extensions be,
282 pa_billing_assignments bea
283 WHERE bea.active_flag = 'Y'
284 AND bea.billing_extension_id = be.billing_extension_id
285 AND (bea.project_id = Rproj.project_id
286 OR bea.project_type = Rproj.project_type
287 OR bea.distribution_rule = Rproj.distribution_rule)
288 AND be.calling_process in ('Revenue','Both')
289 AND be.call_after_adj_flag = 'Y'
290 AND be.trx_independent_flag = 'Y'
291 );
292 IF pa_debug_mode = 'Y' THEN
293 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Billing extensions Found');
294 END IF;
295 status := TRUE;
296 EXCEPTION
297 WHEN NO_DATA_FOUND THEN
298 IF pa_debug_mode = 'Y' THEN
299 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Billing extensions Not Found');
300 END IF;
301 status := FALSE;
302 END;
303 END IF;
304 IF status THEN
305 IF pa_debug_mode = 'Y' THEN
306 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Inserting project id :'||Rproj.project_id);
307 END IF;
308 INSERT INTO PA_BILLING_PROCESS_GT(project_id,request_id,process)
309 VALUES(Rproj.project_id,g_request_id,'REV_ADJ');
310 --insert record
311 END IF;
312 END IF; -- If projectstatus then
313 END LOOP; -- project loop
314 IF pa_debug_mode = 'Y' THEN
315 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:End task Loop');
316 END IF;
317 PA_MCB_INVOICE_PKG.log_message('... Leaving the procedure pa_process_rev_adj');
318 END PA_PROCESS_REV_ADJ;
319 END PA_BILLING_PROCESS_PKG;
[Home] [Help]