1 PACKAGE BODY PA_BILLING_PROCESS_PKG AS
2 /* $Header: PABIPROB.pls 120.6.12020000.4 2013/03/22 07:38:57 rvadali ship $ */
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 /*Bug:13600015 Added below lines */
17 v_inv_query1 VARCHAR2(2000);
18 v_inv_query2 VARCHAR2(150);
19 v_inv_query3 VARCHAR2(150);
20 v_inv_query4 VARCHAR2(150);
21 v_inv_query5 VARCHAR2(150);
22 v_inv_query6 VARCHAR2(150);
23 /*Bug13600015 End here */
24
25 CURSOR cproj IS
26 SELECT p.*
27 FROM pa_projects p, pa_project_types t
28 WHERE ((segment1 BETWEEN pfromproj AND ptoproj)
29 OR project_id =pproject_id)
30 AND p.project_type = t.project_type
31 AND t.project_type_class_code = 'CONTRACT'
32 AND t.direct_flag = 'Y' /*Added for bug 9359035*/
33 AND pa_project_utils.check_prj_stus_action_allowed(p.project_status_code,'GENERATE_REV') = 'Y'; /* Added for bug 8887579*/
34 /*
35 CURSOR ctask(cproject_id VARCHAR2) is
36 SELECT *
37 FROM pa_tasks a
38 WHERE project_id=cproject_id
39 AND (
40 (task_id=top_task_id
41 AND ready_to_distribute_flag ='Y'
42 )
43 OR (chargeable_flag ='Y'
44 AND EXISTS ( SELECT null
45 FROM pa_tasks b
46 WHERE b.task_id = a.top_task_id
47 AND b.ready_to_distribute_flag = 'Y'
48 )
49 )
50 ); commented for bug 8813330*/
51
52 CURSOR cspf(cproject_id VARCHAR2) is
53 SELECT /*+ INDEX(pf pa_summary_project_fundings_u1)*/ 1
54 FROM pa_summary_project_fundings spf
55 WHERE spf.project_id = cproject_id
56 AND nvl(spf.revproc_baselined_amount, 0) <> 0;
57
58
59 CURSOR cdri(cproject_id NUMBER) is
60 SELECT 1
61 FROM pa_draft_revenues dr
62 WHERE dr.project_id = cproject_id
63 AND dr.released_date IS NULL
64 AND dr.generation_error_flag = decode(pmass_gen, 1, 'Y',dr.generation_error_flag);
65
66 --Bug13600015 following lines are added
67 Type Cur_Type IS REF CURSOR;
68 v_inv_cursor cur_type;
69 rproj pa_projects_all%ROWTYPE;
70 --Bug13600015 End here
71 BEGIN
72 PA_MCB_INVOICE_PKG.log_message('... Enter the procedure pa_process_rev_adj');
73
74 --Bug 13600015 Below lines added
75 v_inv_query1 := 'SELECT p.*'||' FROM pa_projects p, pa_project_types t '||
76 'WHERE ((segment1 BETWEEN :pfrmprj AND :ptoprj )'||
77 ' OR project_id = :pprj_id)'||
78 ' AND p.project_type = t.project_type '||
79 ' AND t.project_type_class_code = ''CONTRACT'' '||
80 ' AND t.direct_flag = ''Y'' '||
81 ' AND pa_project_utils.check_prj_stus_action_allowed(p.project_status_code,''GENERATE_REV'') = ''Y'' ';
82
83 IF G_ProjectType_ID <> 0 THEN
84 v_inv_query2 := 'AND t.project_type_id = '|| G_ProjectType_ID;
85 v_inv_query1 := v_inv_query1 || v_inv_query2;
86 END IF;
87
88 IF G_Organization_ID <> 0 THEN
89 v_inv_query3 := ' AND p.carrying_out_organization_id = '|| G_ORGANIZATION_ID;
90 v_inv_query1 := v_inv_query1 || v_inv_query3;
91 END IF;
92
93 IF G_Customer_Id <> 0 THEN
94 v_inv_query4 := 'AND EXISTS ( SELECT NULL FROM pa_project_customers pc '||
95 'WHERE pc.project_id = p.project_id '||
96 'AND pc.customer_id = '|| G_Customer_Id || ')';
97 v_inv_query1 := v_inv_query1 || v_inv_query4;
98 END IF;
99
100 IF G_Agrmnt_ID <> 0 THEN
101 v_inv_query5 := 'AND EXISTS ( SELECT NULL FROM pa_agreements_all agr, '||
102 ' pa_summary_project_fundings spf '||
103 ' WHERE agr.agreement_id = spf.agreement_id '||
104 ' AND spf.project_id = p.project_id '||
105 ' AND nvl(spf.total_baselined_amount, 0) > 0 '||
106 ' AND agr.agreement_id = ' ||g_agrmnt_id||
107 ')';
108 v_inv_query1 := v_inv_query1 || v_inv_query5;
109 END IF;
110
111 IF G_MCB_FLAG <> 0 THEN
112 v_inv_query6 := 'AND p.multi_currency_billing_flag = ''Y'' ';
113 v_inv_query1 := v_inv_query1 || v_inv_query6;
114 END IF;
115 --Bug13600015 End here
116
117 IF pmass_gen =1 THEN
118 mass_gen := 'N';
119 ELSE
120 mass_gen := 'E';
121 END IF;
122
123 IF pacc_thru_dt IS NULL THEN
124 acc_thru_dt := SYSDATE;
125 ELSE
126 acc_thru_dt := pacc_thru_dt;
127 END IF;
128 IF pa_debug_mode = 'Y' THEN
129 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Start Projects Loop');
130 END IF;
134
131 --Bug13600015 Added below lines
132 PA_MCB_INVOICE_PKG.log_message('Dynamic Query '||v_inv_query1);
133 Open v_inv_cursor for v_inv_query1 using pfromproj, ptoproj, pproject_id;
135 --FOR Rproj IN Cproj
136 --Bug13600015 End here
137 LOOP
138 -- project loop
139 --Bug13600015 Below lines added
140 fetch v_inv_cursor into rproj;
141 EXIT WHEN v_inv_cursor%NOTFOUND;
142 --Bug13600015 End here
143
144 IF pa_debug_mode = 'Y' THEN
145 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Project Id :'||Rproj.project_id);
146 END IF;
147 projectstatus := FALSE;
148 OPEN cspf(Rproj.project_id);
149 FETCH cspf INTO temp;
150 IF cspf%FOUND THEN
151 projectstatus := TRUE;
152 IF pa_debug_mode = 'Y' THEN
153 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Funding available ');
154 END IF;
155 ELSE
156 IF pa_debug_mode = 'Y' THEN
157 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Funding not available');
158 END IF;
159 END IF;
160 CLOSE cspf;
161 IF projectstatus THEN
162 OPEN cdri(Rproj.project_id);
163 FETCH cdri INTO temp;
164 IF cdri%FOUND THEN
165 projectstatus := FALSE;
166 END IF;
167 CLOSE cdri;
168 END IF;
169 IF projectstatus THEN
170 status := FALSE;
171 distribution_rule := SUBSTR(Rproj.distribution_rule,1,4);
172 IF pa_debug_mode = 'Y' THEN
173 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Start task Loop');
174 END IF;
175 /* FOR Rtask IN ctask(Rproj.project_id)
176 LOOP commented for bug 8813330*/
177 -- task loop
178 /* IF pa_debug_mode = 'Y' THEN
179 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Task Id :'||Rtask.task_id);
180 END IF; commented for bug 8813330*/
181 /* IF NOT (Rproj.enable_top_task_inv_mth_flag = 'N'
182 AND Rproj.distribution_rule = 'EVENT/EVENT'
183 )
184 AND Rtask.chargeable_flag = 'Y' THEN commented for bug 8813330*/
185 -- for expenditures
186 IF pa_debug_mode = 'Y' THEN
187 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Checking for EIs');
188 END IF;
189 BEGIN
190 /* Hint added for bug 8644400, Modified below query for bug 8813330*/
191 SELECT 1
192 INTO temp
193 FROM dual
194 WHERE EXISTS(
195 SELECT /*+ INDEX(Rtask PA_TASKS_N8) INDEX(ei PA_EXPENDITURE_ITEMS_N9)*/ 1
196 FROM pa_expenditure_items_all ei,
197 pa_tasks Rtask
198 WHERE Rtask.project_id = Rproj.project_id
199 AND ei.project_id = Rproj.project_id /* 14727962 */
200 AND ei.task_id = Rtask.task_id
201 /*AND Rtask.chargeable_flag = 'Y' Commented for bug 9755401*/
202 AND ((Rproj.enable_top_task_inv_mth_flag = 'Y'
203 and (Rtask.revenue_accrual_method = 'WORK' or Rtask.invoice_method = 'WORK'))
204 OR
205 (Rproj.enable_top_task_inv_mth_flag = 'N'
206 and (Rproj.revenue_accrual_method = 'WORK' or Rproj.invoice_method = 'WORK')))
207 AND ei.cost_distributed_flag = 'Y'
208 AND ei.revenue_distributed_flag = 'N'
209 AND ei.expenditure_item_date <= acc_thru_dt
210 AND EXISTS( SELECT /*+ INDEX(crdl PA_CUST_REV_DIST_LINES_U1)*/ NULL
211 FROM pa_cust_rev_dist_lines crdl,pa_draft_revenues drx1
212 WHERE ei.project_id = crdl.project_id
213 AND (ei.expenditure_item_id = crdl.expenditure_item_id) /* 14727962 */
214 AND NVL(crdl.reversed_flag,'N') = 'N'
215 AND NVL(crdl.line_num_reversed,0) = 0
216 AND drx1.project_id = crdl.project_id
217 AND drx1.draft_revenue_num = crdl.draft_revenue_num
218 AND DECODE(drx1.generation_error_flag,mass_gen,decode(drx1.released_date,NULL,1,0),0)
219 = DECODE(drx1.released_date,NULL,1,0)
220 /* 14727962 */
221 UNION ALL
222 SELECT /*+ INDEX(crdl PA_CUST_REV_DIST_LINES_U1)*/ NULL
223 FROM pa_cust_rev_dist_lines crdl,pa_draft_revenues drx1
224 WHERE ei.project_id = crdl.project_id
225 AND (ei.adjusted_expenditure_item_id IS NOT NULL
226 AND ei.adjusted_expenditure_item_id = crdl.expenditure_item_id)
227 AND NVL(crdl.reversed_flag,'N') = 'N'
228 AND NVL(crdl.line_num_reversed,0) = 0
229 AND drx1.project_id = crdl.project_id
230 AND drx1.draft_revenue_num = crdl.draft_revenue_num
231 AND DECODE(drx1.generation_error_flag,mass_gen,decode(drx1.released_date,NULL,1,0),0)
232 = DECODE(drx1.released_date,NULL,1,0)
233
234 /* 14727962 */
235 UNION ALL
236 SELECT 1
237 FROM pa_expenditure_items ei2
238 WHERE ei2.project_id = ei.project_id
239 AND ei2.expenditure_item_id = ei.adjusted_expenditure_item_id
240 AND ei2.revenue_distributed_flag = 'Y'
241 AND ei2.raw_revenue = 0
242 )
243 );
244 IF pa_debug_mode = 'Y' THEN
245 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:EI found');
246 END IF;
247 status := TRUE;
248 EXCEPTION
249 WHEN NO_DATA_FOUND THEN
250 IF pa_debug_mode = 'Y' THEN
251 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:EI not found');
252 END IF;
253 status := FALSE;
254 END;
255 -- END IF; commented for bug 8813330
256 IF (NOT status ) THEN /* Modified for bug 8813330*/
260 END IF;
257 -- for events
258 IF pa_debug_mode = 'Y' THEN
259 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Check for Events');
261 BEGIN
262 /* Modified below query for bug 8813330*/
263 SELECT 1
264 INTO temp
265 FROM dual
266 WHERE
267 EXISTS(
268 SELECT 1
269 FROM pa_events ev,
270 pa_cust_event_rev_dist_lines erdl,
271 pa_draft_revenues drx,
272 pa_tasks Rtask
273 WHERE ev.project_id = Rtask.project_id
274 AND ev.task_id = Rtask.task_id
275 AND Rtask.project_id = Rproj.project_id
276 AND Rtask.task_id=Rtask.top_task_id
277 AND Rtask.ready_to_distribute_flag ='Y'
278 AND ((ev.revenue_distributed_flag = 'N'
279 AND ev.completion_date <= acc_thru_dt)
280 OR (distribution_rule = 'COST'
281 AND ev.revenue_distributed_flag = 'Y'
282 AND ev.completion_date > acc_thru_dt
283 )
284 )
285 AND nvl(ev.revenue_hold_flag, 'N') = 'N'
286 AND (decode(nvl(ev.bill_trans_rev_amount, 0), 0,
287 decode(nvl(ev.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
288 AND erdl.project_id = ev.project_id
289 AND erdl.event_num = ev.event_num
290 AND erdl.task_id = ev.task_id
291 AND nvl(erdl.reversed_flag,'N') = 'N'
292 AND erdl.line_num_reversed IS NULL
293 AND drx.project_id = erdl.project_id
294 AND drx.draft_revenue_num = erdl.draft_revenue_num
295 AND decode(drx.generation_error_flag,mass_gen,decode(drx.released_date,NULL,1,0),0)
296 = decode(drx.released_date,NULL,1,0)
297 );
298 IF pa_debug_mode = 'Y' THEN
299 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Event found');
300 END IF;
301 status := TRUE;
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN
304 IF pa_debug_mode = 'Y' THEN
305 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Event not found');
306 END IF;
307 status := FALSE;
308 END;
309 END IF;
310 /* IF Status THEN
311 if records found for that project for one task no need to check for other tasks
312 EXIT;
313 END IF;commented for bug 8873015 */
314 -- END LOOP; commented for bug 8813330 task loop
315 IF pa_debug_mode = 'Y' THEN
316 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:End task Loop');
317 END IF;
318 IF NOT status THEN
319 IF pa_debug_mode = 'Y' THEN
320 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:checking for Project level events');
321 END IF;
322 -- For project level events
323 BEGIN
324 SELECT 1
325 INTO temp
326 FROM dual
327 WHERE EXISTS(
328 SELECT 1
329 FROM pa_events ev,
330 pa_cust_event_rev_dist_lines erdl,
331 pa_draft_revenues drx
332 WHERE ev.project_id = Rproj.project_id
333 AND ev.task_id IS NULL
334 AND ((ev.revenue_distributed_flag = 'N'
335 AND ev.completion_date <= acc_thru_dt)
336 OR (distribution_rule = 'COST'
337 AND ev.revenue_distributed_flag = 'Y'
338 AND ev.completion_date > acc_thru_dt
339 )
340 )
341 AND nvl(ev.revenue_hold_flag, 'N') = 'N'
342 AND (decode(nvl(ev.bill_trans_rev_amount, 0), 0,
343 decode(nvl(ev.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
344 AND erdl.project_id = ev.project_id
345 AND erdl.task_id IS NULL
346 AND erdl.event_num = ev.event_num
347 AND nvl(erdl.reversed_flag,'N') = 'N'
348 AND erdl.line_num_reversed IS NULL
349 AND drx.project_id = erdl.project_id
350 AND drx.draft_revenue_num = erdl.draft_revenue_num
351 AND decode(drx.generation_error_flag,mass_gen,decode(drx.released_date,NULL,1,0),0)
352 = decode(drx.released_date,NULL,1,0)
353 );
354 IF pa_debug_mode = 'Y' THEN
355 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Project level Event found');
356 END IF;
357 status := TRUE;
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 IF pa_debug_mode = 'Y' THEN
361 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Project level Event not found');
362 END IF;
363 status := FALSE;
364 END;
365 END IF;
366 IF NOT status THEN
367 IF pa_debug_mode = 'Y' THEN
368 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Checking for Billing extensions');
369 END IF;
370 --Billing extension
371 BEGIN
372 SELECT 1
373 INTO temp
374 FROM dual
375 WHERE EXISTS(
376 SELECT 1
377 FROM pa_billing_extensions be,
378 pa_billing_assignments bea
379 WHERE bea.active_flag = 'Y'
380 AND bea.billing_extension_id = be.billing_extension_id
381 AND (bea.project_id = Rproj.project_id
382 OR bea.project_type = Rproj.project_type
383 OR bea.distribution_rule = Rproj.distribution_rule)
384 AND be.calling_process in ('Revenue','Both')
385 AND be.call_after_adj_flag = 'Y'
389 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Billing extensions Found');
386 AND be.trx_independent_flag = 'Y'
387 );
388 IF pa_debug_mode = 'Y' THEN
390 END IF;
391 status := TRUE;
392 EXCEPTION
393 WHEN NO_DATA_FOUND THEN
394 IF pa_debug_mode = 'Y' THEN
395 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Billing extensions Not Found');
396 END IF;
397 status := FALSE;
398 END;
399 END IF;
400 IF status THEN
401 IF pa_debug_mode = 'Y' THEN
402 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Inserting project id :'||Rproj.project_id);
403 END IF;
404 INSERT INTO PA_BILLING_PROCESS_GT(project_id,request_id,process)
405 VALUES(Rproj.project_id,g_request_id,'REV_ADJ');
406 --insert record
407 END IF;
408 END IF; -- If projectstatus then
409 END LOOP; -- project loop
410 close v_inv_cursor;
411 /* IF pa_debug_mode = 'Y' THEN
412 PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:End task Loop');
413 END IF; commented for bug 8813330 */
414 PA_MCB_INVOICE_PKG.log_message('... Leaving the procedure pa_process_rev_adj');
415 END PA_PROCESS_REV_ADJ;
416 END PA_BILLING_PROCESS_PKG;
[Home] [Help]