[Home] [Help]
PACKAGE BODY: APPS.PA_BILLING
Source
1 PACKAGE BODY pa_billing AS
2 /* $Header: PAXIBILB.pls 120.5.12000000.3 2007/04/23 10:00:27 abjacob ship $ */
3
4 /* MCB related changes */
5
6 /* ATG changes : Added the format mask YYYY/MM/DD for the to_date conversion function */
7
8 FUNCTION GetPADate RETURN DATE
9 IS
10 BEGIN
11 RETURN ( TO_DATE(GlobVars.PaDate, 'YYYY/MM/DD') );
12 END;
13
14 FUNCTION GetInvoiceDate RETURN DATE
15 IS
16 BEGIN
17 RETURN ( TO_DATE(GlobVars.InvoiceDate, 'YYYY/MM/DD') );
18 END;
19
20 FUNCTION GetBillingAssignmentId RETURN NUMBER
21 IS
22 BEGIN
23 RETURN ( GlobVars.BillingAssignmentId );
24 END;
25 /* Till Here */
26
27 /* Start EPP Changes on 27-Dec-2001 */
28 FUNCTION GetGlDate RETURN DATE
29 IS
30 BEGIN
31 RETURN ( TO_DATE(GlobVars.GlDate, 'YYYY/MM/DD') );
32 END;
33 FUNCTION GetGlPeriodname RETURN VARCHAR2
34 IS
35 BEGIN
36 RETURN ( GlobVars.GlPeriodName );
37 END;
38 FUNCTION GetPaPeriodname RETURN VARCHAR2
39 IS
40 BEGIN
41 RETURN ( GlobVars.PaPeriodName );
42 END;
43
44 /* End of EPP Changes on 27-Dec-2001 */
45
46 /* Begin Retention Enhancements Changes on 28-mar-2002 */
47
48 FUNCTION GetBillThruDate RETURN VARCHAR2
49 IS
50 BEGIN
51 RETURN ( GlobVars.BillThruDate);
52 END;
53
54 /* End Retention Enhancements Changes on 28-mar-2002 */
55
56
57 FUNCTION GetReqId RETURN NUMBER
58 IS
59 BEGIN
60 RETURN ( GlobVars.ReqId );
61 END;
62
63 FUNCTION GetProjId RETURN NUMBER
64 IS
65 BEGIN
66 RETURN ( GlobVars.ProjectId );
67 END;
68
69 FUNCTION GetTaskId RETURN NUMBER
70 IS
71 BEGIN
72 RETURN ( GlobVars.TaskId );
73 END;
74
75 FUNCTION GetCallPlace RETURN VARCHAR2
76 IS
77 BEGIN
78 RETURN ( GlobVars.CallingPlace );
79 END;
80
81 FUNCTION GetCallProcess RETURN VARCHAR2
82 IS
83 BEGIN
84 RETURN ( GlobVars.CallingProcess );
85 END;
86
87 FUNCTION GetMassGen RETURN VARCHAR2
88 IS
89 BEGIN
90 RETURN ( GlobVars.MassGenFlag );
91 END;
92
93 FUNCTION GetBillingExtensionId RETURN NUMBER
94 IS
95 BEGIN
96 RETURN ( GlobVars.BillingExtensionId );
97 END;
98
99 procedure SetMassGen (x_Massgenflag VARCHAR2) is
100 BEGIN
101 GlobVars.MassGenFlag := x_Massgenflag ;
102 END;
103
104
105 procedure bill_ext_driver
106 ( x_project_id IN NUMBER,
107 x_calling_process IN VARCHAR2,
108 x_calling_place IN VARCHAR2,
109 x_rev_or_bill_date IN VARCHAR2,
110 x_request_id IN NUMBER,
111 x_error_message IN OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
112 c INTEGER;
113 row_processed INTEGER;
114 proc_stmt VARCHAR2(1000);
115 l_project_type pa_projects_all.project_type%type;
116 l_distribution_rule pa_projects_all.distribution_rule%type;
117 cursor get_procedure is
118 select be.procedure_name proc_name, bea.billing_assignment_id bea_id,
119 bea.billing_extension_id be_id, bea.top_task_id task_id,
120 decode(be.amount_reqd_flag, 'Y', nvl(bea.amount, 0), 0) amt,
121 decode(be.percentage_reqd_flag, 'Y', nvl(bea.percentage, 0), 0)
122 percent
123 from pa_billing_extensions be, pa_billing_assignments bea -- , Commented for bug 3643409
124 -- pa_projects p Commented for bug 3643409
125 where -- p.project_id = x_project_id Commented for bug 3643409
126 -- and Commented for bug 3643409
127 bea.active_flag = 'Y'
128 and bea.billing_extension_id = be.billing_extension_id
129 and (be.calling_process = x_calling_process
130 or be.calling_process = 'Both')
131 and (bea.project_id = X_project_id
132 or bea.project_type = l_project_type
133 or bea.distribution_rule = l_distribution_rule)
134 -- Added above two lines for bug 3643409
135 -- or bea.project_type = p.project_type Commented for bug 3643409
136 -- or bea.distribution_rule = p.distribution_rule) Commented for bug 3643409
137 and
138 (
139 ( x_calling_place = 'PRE' and nvl(be.pre_processing_flag,'N') = 'Y')
140 or ( x_calling_place = 'POST' and nvl(be.post_processing_flag,'N')= 'Y')
141 or ( x_calling_place = 'DEL' and nvl(be.call_before_del_flag,'N')= 'Y')
142 or ( x_calling_place = 'CANCEL' and nvl(be.call_after_cancel_inv_flag,'N')= 'Y')
143 or ( x_calling_place = 'WRITE-OFF' and nvl(be.call_after_woff_inv_flag,'N')= 'Y')
144 or ( x_calling_place = 'CONCESSION' and nvl(be.call_after_concession_inv_flag,'N')= 'Y') -- Added this line for Concession Invoice
145 or
146 (
147 ( x_calling_place = 'ADJ' and nvl(be.call_after_adj_flag,'N')= 'Y')
148 or ( x_calling_place = 'REG' and nvl(be.call_after_reg_flag,'N')= 'Y')
149 or ( x_calling_place = 'POST-REG' and nvl(be.call_post_reg_flag,'N')= 'Y')
150 and
151 ( nvl(be.trx_independent_flag, 'N') = 'Y'
152 or
153 ( x_calling_process in ('Invoice','Both')
154 AND EXISTS
155 (select NULL from pa_draft_invoices pdi
156 where pdi.project_id = x_project_id
157 and pdi.request_id = x_request_id
158 -- and pdi.invoice_line_type <> 'NET ZERO ADJUSTMENT'
159 and (( x_calling_place = 'ADJ'
160 and pdi.draft_invoice_num_credited is not null)
161 OR
162 ( x_calling_place IN ('REG' , 'POST-REG')
163 and pdi.draft_invoice_num_credited IS NULL)))
164 )
165 or
166 ( x_calling_process in ('Revenue','Both')
167 AND EXISTS
168 (select NULL from pa_draft_revenues pdr
169 where pdr.project_id = x_project_id
170 and pdr.request_id = x_request_id
171 and (( x_calling_place = 'ADJ'
172 and pdr.draft_revenue_num_credited is not null)
173 OR
174 ( x_calling_place IN ('REG','POST-REG')
175 and pdr.draft_revenue_num_credited IS NULL)))
176 )
177 )
178 )
179 )
180 order by be.processing_order, bea.billing_assignment_id;
181
182 fund_level VARCHAR2(10) := NULL;
183 NO_FUNDING EXCEPTION;
184
185 CURSOR each_task (X2_task_id NUMBER) IS
186 SELECT distinct
187 decode(fund_level,
188 'PROJECT', decode(X2_task_id, NULL, NULL, X2_task_id),
189 'TASK', t.top_task_id,
190 t.top_task_id) tpid
191 FROM pa_tasks t
192 WHERE t.project_id = X_project_id
193 AND t.task_id = nvl(X2_task_id, t.task_id)
194 AND t.ready_to_distribute_flag =
195 decode(x_calling_process, 'Revenue', 'Y', 'Both', 'Y',
196 t.ready_to_distribute_flag)
197 AND t.ready_to_bill_flag =
198 decode(x_calling_process, 'Invoice', 'Y', 'Both', 'Y',
199 t.ready_to_bill_flag);
200
201 task_rec each_task%ROWTYPE;
202
203 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
204
205 BEGIN
206 IF g1_debug_mode = 'Y' THEN
207 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.bill_ext_driver :');
208 END IF;
209 GlobVars.ProjectId := x_project_id;
210 GlobVars.ReqId := x_request_id;
211 GlobVars.CallingPlace := x_calling_place;
212 GlobVars.CallingProcess := x_calling_process;
213 GlobVars.AccrueThruDate := x_rev_or_bill_date;
214 IF g1_debug_mode = 'Y' THEN
215 PA_MCB_INVOICE_PKG.log_message('pa_billing.bill_ext_driver project id :'||to_char(GlobVars.ProjectId));
216 PA_MCB_INVOICE_PKG.log_message('pa_billing.bill_ext_driver Request id :'||to_char(GlobVars.ReqId));
217 PA_MCB_INVOICE_PKG.log_message('pa_billing.bill_ext_driver Calling place :'||GlobVars.CallingPlace);
218 PA_MCB_INVOICE_PKG.log_message('pa_billing.bill_ext_driver calling process :'||GlobVars.CallingProcess);
219 PA_MCB_INVOICE_PKG.log_message('pa_billing.bill_ext_driver accru thru date :'||GlobVars.AccrueThruDate);
220 END IF;
221
222 -- DBMS_OUTPUT.ENABLE(1000000);
223 fund_level := pa_billing_values.funding_level(X_project_id);
224 x_error_message := 'Error during opening the dbms_sql cursor.';
225 c := dbms_sql.open_cursor;
226
227 x_error_message := 'Error during fetching the get_procedure cursor.';
228
229 /* Added below select statement for bug 3643409 */
230 SELECT project_type,distribution_rule
231 INTO l_project_type,l_distribution_rule
232 FROM pa_projects_all
233 WHERE project_id = x_project_id;
234
235 FOR get_rec IN get_procedure LOOP
236 -- Loop for each assigned Billing Extension
237
238 BEGIN
239
240 x_error_message := 'Error while setting up proc_stmt.';
241 FOR task_rec IN each_task(get_rec.task_id) LOOP
242
243 -- Loop for each task level execution (in case of task funding)
244 BEGIN
245
246 GlobVars.BillingExtensionId := get_rec.be_id;
247 GlobVars.BillingAssignmentId := get_rec.bea_id;
248 GlobVars.TaskId := task_rec.tpid;
249
250
251 /* ATG changes : Added the format mask YYYY/MM/DD for the to_date conversion function */
252
253
254 /* Commented for bug 3560805
255 IF (task_rec.tpid IS NULL) THEN
256 -- This will be the case for Project Level funding and Project
257 -- Level assignment.
258 -- Do not have to change this stmt for MCB, because user is going to select from the
259 -- view pa_billing_extn_params_v ( all the newly added columns in pa_billing_assignments table)
260
261 proc_stmt := 'declare s varchar2(240):=null; begin ' ||
262 get_rec.proc_name || '(' || to_char(x_project_id) ||
263 ','''',''' || x_calling_process ||
264 ''',''' || x_calling_place || ''',fnd_number.canonical_to_number('''
265 || fnd_number.number_to_canonical(get_rec.amt) ||
266 '''),fnd_number.canonical_to_number(''' ||
267 fnd_number.number_to_canonical(get_rec.percent) || '''), to_date(''' ||
268 x_rev_or_bill_date || ''', , ''' || 'YYYY/MM/DD' ||'''),' || to_char(get_rec.bea_id) || ',' ||
269 to_char(get_rec.be_id) || ',' || to_char(x_request_id) ||
270 '); end;';
271 ELSE
272 -- This will be the case for either Task Level assignment
273 -- (one iteration) or task level funding and project level
274 -- assignment (one iteration per top task)
275
276 proc_stmt := 'declare s varchar2(240):=null; begin ' ||
277 get_rec.proc_name || '(' || to_char(x_project_id) ||
278 ',' || task_rec.tpid || ',''' || x_calling_process ||
279 ''',''' || x_calling_place || ''',fnd_number.canonical_to_number('''
280 || fnd_number.number_to_canonical(get_rec.amt) ||
281 '''),fnd_number.canonical_to_number(''' ||
282 fnd_number.number_to_canonical(get_rec.percent) || '''), to_date(''' ||
283 x_rev_or_bill_date || ''', ''' || 'YYYY/MM/DD' ||'''),' || to_char(get_rec.bea_id) || ',' ||
284 to_char(get_rec.be_id) || ',' || to_char(x_request_id) ||
285 '); end;';
286 END IF; */
287
288 proc_stmt := 'declare s varchar2(240):=null; begin ' ||
289 get_rec.proc_name || '(:project_id,:task_id,:calling_process,:calling_place,
290 :amt,:percent,:rev_or_bill_date,:bea_id,:be_id,:request_id); end;'; /* Added for 3560805*/
291
292 IF g1_debug_mode = 'Y' THEN
293 PA_MCB_INVOICE_PKG.log_message('pa_billing.bill_ext_driver: before executing building the Pl/Sql block :'||proc_stmt);
294 END IF;
295 x_error_message := 'Error during parsing the dynamic PL/SQL.';
296 dbms_sql.parse(c, proc_stmt, dbms_sql.native);
297
298 /* Start of 3560805*/
299
300 /* Release 12 : ATG changes : Added the date format for the variable x_rev_or_bill_date */
301
302 DBMS_SQL.BIND_VARIABLE(c, ':project_id', x_project_id);
303 DBMS_SQL.BIND_VARIABLE(c, ':task_id', task_rec.tpid);
304 DBMS_SQL.BIND_VARIABLE(c, ':calling_process',x_calling_process);
305 DBMS_SQL.BIND_VARIABLE(c, ':calling_place',x_calling_place);
306 DBMS_SQL.BIND_VARIABLE(c, ':amt',fnd_number.number_to_canonical(get_rec.amt));
307 DBMS_SQL.BIND_VARIABLE(c, ':percent',fnd_number.number_to_canonical(get_rec.percent));
308 DBMS_SQL.BIND_VARIABLE(c, ':rev_or_bill_date',TO_DATE(x_rev_or_bill_date,'YYYY/MM/DD'));
309 DBMS_SQL.BIND_VARIABLE(c, ':bea_id',get_rec.bea_id);
310 DBMS_SQL.BIND_VARIABLE(c, ':be_id',get_rec.be_id);
311 DBMS_SQL.BIND_VARIABLE(c, ':request_id',x_request_id);
312
313 /* End of 3560805*/
314
315 x_error_message := 'Error during executing the dynamic PL/SQL.';
316 row_processed := dbms_sql.execute(c);
317
318
319 IF g1_debug_mode = 'Y' THEN
320 PA_MCB_INVOICE_PKG.log_message('pa_billing.bill_ext_driver: After building the Pl/Sql block :');
321 END IF;
322 EXCEPTION
323 WHEN NO_FUNDING THEN
324 IF g1_debug_mode = 'Y' THEN
325 PA_MCB_INVOICE_PKG.log_message('pa_billing.bill_ext_driver: Inside the error :');
326 END IF;
327 X_error_message := 'There is no funding';
328 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
329 WHEN OTHERS THEN
330 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
331 IF g1_debug_mode = 'Y' THEN
332 PA_MCB_INVOICE_PKG.log_message('pa_billing.bill_ext_driver: Inside the others error :');
333 END IF;
334 dbms_sql.close_cursor(c);
335 RAISE;
336 END;
337
338 END LOOP;
339
340 END;
341
342 END LOOP;
343
344 x_error_message := 'Error during closing the dbms_sql cursor.';
345 dbms_sql.close_cursor(c);
346 if x_error_message = 'Error during closing the dbms_sql cursor.' then
347 x_error_message := 'OK';
348 end if;
349
350 IF g1_debug_mode = 'Y' THEN
351 PA_MCB_INVOICE_PKG.log_message('Exiting from pa_billing.bill_ext_driver :');
352 END IF;
353 EXCEPTION
354 WHEN OTHERS THEN
355 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
356 IF g1_debug_mode = 'Y' THEN
357 PA_MCB_INVOICE_PKG.log_message('Inside main others error pa_billing.bill_ext_driver :');
358 END IF;
359 RAISE;
360 end bill_ext_driver;
361
362
363
364 PROCEDURE ccrev( X_project_id IN NUMBER,
365 X_top_task_id IN NUMBER DEFAULT NULL,
366 X_calling_process IN VARCHAR2 DEFAULT NULL,
367 X_calling_place IN VARCHAR2 DEFAULT NULL,
368 X_amount IN NUMBER DEFAULT NULL,
369 X_percentage IN NUMBER DEFAULT NULL,
370 X_rev_or_bill_date IN DATE DEFAULT NULL,
371 X_billing_assignment_id IN NUMBER DEFAULT NULL,
372 X_billing_extension_id IN NUMBER DEFAULT NULL,
373 X_request_id IN NUMBER DEFAULT NULL
374 ) IS
375
376
377 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
378
379 budget_revenue REAL := 0;
380 budget_cost REAL := 0;
381 invoice_amount REAL := 0;
382 revenue_amount REAL := 0;
383 event_revenue REAL := 0;
384 event_invoice REAL := 0;
385 cost_amount REAL := 0;
386 revenue REAL := 0;
387 invoice REAL := 0;
388 Amount_Left REAL := 0;
389
390 event_description VARCHAR2(240);
391 --
392 -- The cost and revenue budget type codes used by the pa_billing_pub.get_budget_amount procedure
393 --
394 l_cost_budget_type_code VARCHAR2(30);
395 l_rev_budget_type_code VARCHAR2(30);
396 l_currency_code VARCHAR2(15);
397
398 l_status NUMBER;
399 l_error_message VARCHAR2(240);
400
401 ccrev_error EXCEPTION;
402
403 /* MCB related changes */
407 l_invproc_currency_code VARCHAR2(30);
404 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
405 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
406 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
408 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
409 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
410 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
411 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
412 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
413 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
414 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
415 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
416 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
417 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
418 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
419 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
420 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
421 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
422 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
423 l_return_status VARCHAR2(30);
424 l_msg_count NUMBER;
425 l_msg_data VARCHAR2(30);
426 /* Till Here */
427
428 /* Added for Fin Plan impact */
429 l_cost_plan_type_id NUMBER;
430 l_rev_plan_type_id NUMBER;
431 /* till here */
432
433
434 BEGIN
435 --
436 -- Modified to pass the cost budget and revenue budget type codes
437 --
438 l_status := 0;
439 l_error_message := NULL;
440 IF g1_debug_mode = 'Y' THEN
441 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.ccrev :');
442 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev Project Id :'||to_char(X_project_id));
443 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev Top Task Id :'||to_char(X_top_task_id));
444 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev Calling process :'||X_calling_process);
445 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev Calling place :'||X_calling_place);
446 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev Amount :'||to_char(X_amount));
447 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev Percentage :'||to_char(X_percentage));
448 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev rev_or_bill_date :'||to_char(X_rev_or_bill_date,'YYYY/MM/DD'));
449 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev billing_assignment_id :'||to_char(X_billing_assignment_id));
450 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev billing_extension_id :'||to_char(X_billing_extension_id));
451 PA_MCB_INVOICE_PKG.log_message('pa_billing.ccrev request_id :'||to_char(X_request_id));
452 END IF;
453 /*****
454 l_cost_budget_type_code := 'AC';
455 l_rev_budget_type_code := 'AR';
456 P_cost_budget_type_code => l_cost_budget_type_code,
457 P_rev_budget_type_code => l_rev_budget_type_code,
458 *****/
459 /* This is commented for MCB2 */
460 -- l_currency_code := pa_multi_currency_txn.get_proj_curr_code_sql(X_project_id);
461
462 /* MCB related changes */
463 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
464 p_project_id => X_project_id,
465 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
466 x_baseline_funding_flag => l_baseline_funding_flag,
467 x_revproc_currency_code => l_revproc_currency_code,
468 x_invproc_currency_type => l_invproc_currency_type,
469 x_invproc_currency_code => l_invproc_currency_code,
470 x_project_currency_code => l_project_currency_code,
471 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
472 x_project_bil_rate_type => l_project_bil_rate_type,
473 x_project_bil_rate_date => l_project_bil_rate_date,
474 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
475 x_projfunc_currency_code => l_projfunc_currency_code,
476 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
477 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
478 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
479 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
480 x_funding_rate_date_code => l_funding_rate_date_code,
481 x_funding_rate_type => l_funding_rate_type,
482 x_funding_rate_date => l_funding_rate_date,
483 x_funding_exchange_rate => l_funding_exchange_rate,
484 x_return_status => l_return_status,
485 x_msg_count => l_msg_count,
486 x_msg_data => l_msg_data);
487
488 l_currency_code := l_projfunc_currency_code;
489 /* Till Here */
490
491
492 IF g1_debug_mode = 'Y' THEN
493 PA_MCB_INVOICE_PKG.log_message('Before select of pa billing params v pa_billing.ccrev :');
494 END IF;
495 /* Added for bug 2649456.Not handling exception intentionaly because if it is coming,
496 it will be data issue */
497 BEGIN
501 EXCEPTION
498 SELECT default_cost_plan_type_id,default_rev_plan_type_id
499 INTO l_cost_plan_type_id,l_rev_plan_type_id
500 FROM pa_billing_extn_params_v;
502 WHEN OTHERS THEN
503 IF g1_debug_mode = 'Y' THEN
504 PA_MCB_INVOICE_PKG.log_message('Error from pa_billing_extn_params_v pa_billing.ccrev :'||SQLERRM);
505 END IF;
506 RAISE;
507 END;
508 /* till here */
509
510 IF g1_debug_mode = 'Y' THEN
511 PA_MCB_INVOICE_PKG.log_message('pa billing params v.cost_plan_type_id pa_billing.ccrev :'||l_cost_plan_type_id);
512 PA_MCB_INVOICE_PKG.log_message('pa billing params v.rev_plan_type_id pa_billing.ccrev :'||l_rev_plan_type_id);
513 PA_MCB_INVOICE_PKG.log_message('Before calling pa_billing_pub.get_budget_amount inside pa_billing.ccrev :');
514 END IF;
515 pa_billing_pub.get_budget_amount(
516 X2_project_id => X_project_id,
517 X2_task_id => X_top_task_id,
518 X2_revenue_amount => budget_revenue,
519 X2_cost_amount => budget_cost,
520 X_cost_budget_type_code => l_cost_budget_type_code,
521 X_rev_budget_type_code => l_rev_budget_type_code,
522 P_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
523 P_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
524 X_error_message => l_error_message,
525 X_status => l_status);
526
527 IF g1_debug_mode = 'Y' THEN
528 PA_MCB_INVOICE_PKG.log_message('After calling pa_billing_pub.get_budget_amount inside pa_billing.ccrev budget_revenue :'||to_char(budget_revenue));
529 PA_MCB_INVOICE_PKG.log_message('After calling pa_billing_pub.get_budget_amount inside pa_billing.ccrev budget_cost :'||to_char(budget_cost));
530 PA_MCB_INVOICE_PKG.log_message('After calling pa_billing_pub.get_budget_amount inside pa_billing.ccrev l_cost_budget_type_code :'||l_cost_budget_type_code);
531 PA_MCB_INVOICE_PKG.log_message('After calling pa_billing_pub.get_budget_amount inside pa_billing.ccrev budget_cost l_rev_budget_type_code :'||l_rev_budget_type_code);
532 END IF;
533 -- If get budget amount return an error its fatal.
534
535 IF l_status <> 0 THEN
536 raise ccrev_error;
537 END IF;
538
539 IF g1_debug_mode = 'Y' THEN
540 PA_MCB_INVOICE_PKG.log_message('Before calling pa_billing_amount.PotEventAmount inside pa_billing.ccrev :');
541 END IF;
542 pa_billing_amount.PotEventAmount(
543 X2_project_id => X_project_id,
544 X2_task_id => X_top_task_id,
545 X2_accrue_through_date => X_rev_or_bill_date,
546 X2_revenue_amount => event_revenue,
547 X2_invoice_amount => event_invoice);
548
549 IF g1_debug_mode = 'Y' THEN
550 PA_MCB_INVOICE_PKG.log_message('After calling pa_billing_amount.PotEventAmount inside pa_billing.ccrev event_revenue :'||to_char(event_revenue));
551 PA_MCB_INVOICE_PKG.log_message('Before calling pa_billing_amount.CostAmount inside pa_billing.ccrev event_invoice :'||to_char(event_invoice));
552 END IF;
553 pa_billing_amount.CostAmount(
554 X2_project_id => X_project_id,
555 X2_task_id => X_top_task_id,
556 X2_accrue_through_date => X_rev_or_bill_date,
557 X2_cost_amount => cost_amount);
558 IF g1_debug_mode = 'Y' THEN
559 PA_MCB_INVOICE_PKG.log_message('After pa_billing_amount.CostAmount inside pa_billing.ccrev cost_amount :'||to_char(cost_amount));
560 END IF;
561
562 Amount_Left := pa_billing_amount.LowestAmountLeft(
563 X_project_id,
564 X_top_task_id,
565 X_calling_process);
566
567 IF g1_debug_mode = 'Y' THEN
568 PA_MCB_INVOICE_PKG.log_message('After pa_billing_amount.LowestAmountLeft inside pa_billing.ccrev Amount_Left :'||to_char(Amount_Left));
569 END IF;
570 -- DBMS_OUTPUT.PUT('Revenue =');
571 -- DBMS_OUTPUT.PUT_LINE(Revenue);
572 -- DBMS_OUTPUT.PUT('Amount_Left=');
573 -- DBMS_OUTPUT.PUT_LINE(Amount_Left);
574 -- DBMS_OUTPUT.PUT('budget_cost=');
575 -- DBMS_OUTPUT.PUT_LINE(budget_cost);
576 -- DBMS_OUTPUT.PUT('budget_revenue=');
577 -- DBMS_OUTPUT.PUT_LINE(budget_revenue);
578 -- DBMS_OUTPUT.PUT('cost_amount=');
579 -- DBMS_OUTPUT.PUT_LINE(cost_amount);
580 -- DBMS_OUTPUT.PUT('revenue_amount=');
581 -- DBMS_OUTPUT.PUT_LINE(revenue_amount);
582 -- DBMS_OUTPUT.PUT('event_revenue=');
583 -- DBMS_OUTPUT.PUT_LINE(event_revenue);
584
585
586 IF (X_calling_process = 'Revenue') THEN
587 pa_billing_amount.RevenueAmount(
588 X2_project_id => x_project_id,
589 X2_task_id => X_top_task_id,
590 X2_revenue_amount => revenue_amount);
591
592 IF g1_debug_mode = 'Y' THEN
593 PA_MCB_INVOICE_PKG.log_message('After call of pa_billing_amount.RevenueAmount inside pa_billing.ccrev revenue_amount 1 :'||to_char(revenue_amount));
594 END IF;
595 IF (budget_cost <> 0) THEN
596 -- Take the lower of what you should insert based on cost-cost algorithm,
597 -- of revenue = (cost/budget_cost) * (budget_revenue - event_revenue)
598 -- - existing revenue.
599 -- and what you can insert based on the lowest hard limit of the projects
600 -- customers.
601
602 Revenue := Least( ( (nvl(cost_amount,0)/budget_cost)
603 * greatest( nvl(budget_revenue,0)
604 - nvl(event_revenue,0), 0
605 )
606 - (nvl(revenue_amount,0))
607 ) ,
608 Amount_Left
609 );
610
611 IF g1_debug_mode = 'Y' THEN
615 /* Changed the length of the format mask for amount_left column from 15 to 22
612 PA_MCB_INVOICE_PKG.log_message('Inside pa_billing.ccrev calculating Revenue ( Least) Revenue 2 :'||to_char(Revenue));
613 END IF;
614
616 to fix the bug 2124494 for MCB2 */
617 /* Changed the length of the format mask for all column from 15 to 22
618 to fix the bug 2162900 for MCB2 */
619 Event_Description := pa_billing_values.get_message('CCREV_DESCRIPTION')|| '(' ||
620 to_char(amount_left,fnd_currency.get_format_mask(l_currency_code,22))
621 || ' ,((' ||
622 to_char(cost_amount,fnd_currency.get_format_mask(l_currency_code,22))
623 || '/' ||
624 to_char(budget_cost,fnd_currency.get_format_mask(l_currency_code,22))
625 || ' * (' ||
626 to_char(budget_revenue,fnd_currency.get_format_mask(l_currency_code,22))
627 || ' - ' ||
628 to_char(nvl(event_revenue,0),fnd_currency.get_format_mask(l_currency_code,22))
629 || ')) - '||
630 to_char(nvl(revenue_amount,0),fnd_currency.get_format_mask(l_currency_code,22))
631 || ' ))';
632 IF g1_debug_mode = 'Y' THEN
633 PA_MCB_INVOICE_PKG.log_message('rev part Inside Revenue part pa_billing.ccrev Event desc :'||Event_Description);
634 PA_MCB_INVOICE_PKG.log_message('Rev part Before insert pa_billing.ccrev.insert_event 1 :'||to_char(Revenue));
635 END IF;
636 -- Modified to add new parameters for insert_event
637 pa_billing_pub.insert_event (
638 X_rev_amt => Revenue,
639 X_bill_amt => 0,
640 X_event_description => event_description,
641 X_audit_amount1 => amount_left,
642 X_audit_amount2 => revenue_amount,
643 X_audit_amount3 => budget_revenue,
644 X_audit_amount4 => event_revenue,
645 X_audit_amount5 => budget_cost,
646 X_audit_amount6 => cost_amount,
647 X_audit_cost_budget_type_code => l_cost_budget_type_code,
648 X_audit_rev_budget_type_code => l_rev_budget_type_code,
649 X_audit_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
650 X_audit_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
651 X_error_message => l_error_message,
652 X_status => l_status
653 );
654
655 IF g1_debug_mode = 'Y' THEN
656 PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event rev :'||to_char(revenue_amount));
657 PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event budget rev :'||to_char(budget_revenue));
658 PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event amt left :'||to_char(amount_left));
659 PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event evt rev :'||to_char(event_revenue));
660 PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event budget cost :'||to_char(budget_cost));
661 PA_MCB_INVOICE_PKG.log_message('rev part after insert pa_billing.ccrev.insert_event cst amt :'||to_char(cost_amount));
662 END IF;
663 IF l_status <> 0 THEN
664 raise ccrev_error;
665 END IF;
666
667 END IF;
668
669 ELSE
670 pa_billing_amount.InvoiceAmount(
671 X2_project_id => X_project_id,
672 X2_task_id => X_top_task_id,
673 X2_invoice_amount => invoice_amount);
674
675 IF g1_debug_mode = 'Y' THEN
676 PA_MCB_INVOICE_PKG.log_message('After the call of pa_billing_amount.InvoiceAmount inside pa_billing.ccrev :'||to_char(invoice_amount));
677 END IF;
678 IF (budget_cost <> 0) THEN
679 Invoice := Least( ( (nvl(cost_amount,0)/budget_cost)
680 * greatest( (nvl(budget_revenue,0)
681 - nvl(event_invoice,0)), 0)
682 ) - nvl(invoice_amount,0),
683 nvl(Amount_Left,0)
684 );
685
686 IF g1_debug_mode = 'Y' THEN
687 PA_MCB_INVOICE_PKG.log_message('Inside pa_billing.ccrev calculating Invoice (Least) :'||to_char(Invoice));
688 END IF;
689 /* Changed the length of the format mask for amount_left column from 15 to 22
690 to fix the bug 2124494 for MCB2 */
691 /* Changed the length of the format mask for all column from 15 to 22
692 to fix the bug 2162900 for MCB2 */
693 Event_Description := pa_billing_values.get_message('CCREV_DESCRIPTION')|| '(' ||
694 to_char(amount_left,fnd_currency.get_format_mask(l_currency_code,22))
695 || ' ,((' ||
696 to_char(cost_amount,fnd_currency.get_format_mask(l_currency_code,22))
697 || '/' ||
698 to_char(budget_cost,fnd_currency.get_format_mask(l_currency_code,22))
699 || ' * (' ||
700 to_char(budget_revenue,fnd_currency.get_format_mask(l_currency_code,22))
701 || ' - ' ||
702 to_char(nvl(event_invoice,0),fnd_currency.get_format_mask(l_currency_code,22))
703 || ')) - '||
704 to_char(nvl(invoice_amount,0),fnd_currency.get_format_mask(l_currency_code,22))
705 || ' ))';
706
707 IF g1_debug_mode = 'Y' THEN
708 PA_MCB_INVOICE_PKG.log_message('inv part Inside Revenue part pa_billing.ccrev Event desc :'||Event_Description);
709 PA_MCB_INVOICE_PKG.log_message('inv part before insert pa_billing.ccrev.insert_event inv 2 :'||to_char(Invoice));
710 END IF;
711 pa_billing_pub.insert_event (
712 X_rev_amt => 0,
713 X_bill_amt => Invoice,
714 X_event_description => Event_Description,
718 X_audit_amount4 => event_invoice,
715 X_audit_amount1 => amount_left,
716 X_audit_amount2 => invoice_amount,
717 X_audit_amount3 => budget_revenue,
719 X_audit_amount5 => budget_cost,
720 X_audit_amount6 => cost_amount,
721 X_audit_cost_budget_type_code => l_cost_budget_type_code,
722 X_audit_rev_budget_type_code => l_rev_budget_type_code,
723 X_audit_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
724 X_audit_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
725 X_error_message => l_error_message,
726 X_status => l_status
727 );
728
729 IF g1_debug_mode = 'Y' THEN
730 PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event inv 2 :'||to_char(invoice_amount));
731 PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event bud rev 2 :'||to_char(budget_revenue));
732 PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event amt lft 2 :'||to_char(amount_left));
733 PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event evt inv 2 :'||to_char(event_invoice));
734 PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event bd cst 2 :'||to_char(budget_cost));
735 PA_MCB_INVOICE_PKG.log_message('inv part after insert pa_billing.ccrev.insert_event cst amt 2 :'||to_char(cost_amount));
736 END IF;
737 IF l_status <> 0 THEN
738 raise ccrev_error;
739 END IF;
740
741 END IF;
742 END IF;
743
744 IF g1_debug_mode = 'Y' THEN
745 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing.ccrev :');
746 END IF;
747 EXCEPTION
748 WHEN ccrev_error THEN
749 NULL;
750 -- Modified so that this exception is reported but doesnot stop revenue
751 -- processing
752 -- RAISE_APPLICATION_ERROR(-20101,l_error_message);
753 WHEN OTHERS THEN
754 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
755 RAISE;
756
757 END ccrev;
758
759
760 PROCEDURE Delete_Automatic_Events ( X_Project_id NUMBER,
761 X_request_id NUMBER DEFAULT NULL,
762 X_rev_inv_num NUMBER DEFAULT NULL,
763 X_calling_process VARCHAR2) IS
764
765
766 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
767
768 BEGIN
769
770 -- Bug#1165176 Added condition line_num_reversed is null
771
772 IF g1_debug_mode = 'Y' THEN
773 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.Delete_Automatic_Events :');
774 END IF;
775 IF (X_calling_Process = 'Revenue') THEN
776 DELETE from pa_events v
777 WHERE v.project_id = X_project_id
778 AND v.request_id+0 = X_request_id
779 AND (v.project_id, nvl(v.task_id, -1), v.event_num) IN
780 (SELECT l.project_id, nvl(l.task_id, -1), l.event_num
781 FROM pa_cust_event_rev_dist_lines l
782 WHERE l.project_id = X_project_id
783 AND l.line_num_reversed is null
784 AND l.draft_revenue_num = X_rev_inv_num)
785 AND EXISTS
786 (SELECT vt.event_type
787 FROM pa_event_types vt
788 WHERE vt.event_type_classification||'' = 'AUTOMATIC'
789 AND vt.event_type = v.event_type)
790 AND v.calling_process = X_calling_process;
791 IF g1_debug_mode = 'Y' THEN
792 PA_MCB_INVOICE_PKG.log_message('Deleted Revenue pa_billing.Delete_Automatic_Events :');
793 END IF;
794 ELSE
795 -- DBMS_OUTPUT.PUT_LINE('Deleting Invoice Events');
796
797 DELETE FROM PA_EVENTS V
798 WHERE V.Project_ID = X_project_id
799 AND (nvl(V.Task_ID, -1), V.Event_Num) IN
800 (select nvl(dii.Event_Task_ID, -1), dii.Event_Num
801 from pa_draft_invoice_items dii, pa_draft_invoices di
802 where di.Project_ID = X_project_id
803 and di.draft_invoice_num = X_rev_inv_num
804 and dii.Project_ID = di.Project_ID
805 and dii.draft_invoice_num = di.draft_invoice_num
806 and nvl(di.write_off_flag, 'N') = 'N')
807 AND V.Bill_Amount <> 0
808 AND V.calling_process = X_calling_process;
809
810 IF g1_debug_mode = 'Y' THEN
811 PA_MCB_INVOICE_PKG.log_message('Deleted Invoice pa_billing.Delete_Automatic_Events :');
812 END IF;
813 -- This last part is to ensure that we delete only events that were created
814 -- by Invoice as per the adjustment model for Billing Extensions.
815
816 END IF;
817
818 -- commit;
819
820 IF g1_debug_mode = 'Y' THEN
821 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing.Delete_Automatic_Events :');
822 END IF;
823 END Delete_Automatic_Events;
824
825
826 PROCEDURE Call_Calc_Bill_Amount(
827 x_transaction_type in varchar2 default 'ACTUAL',
828 x_expenditure_item_id in number,
829 x_sys_linkage_function in varchar2,
830 x_amount in out NOCOPY number, /* This amount is treated as amount in T --File.Sql.39 bug 4440895
831 ransaction currency */
832 x_bill_rate_flag in out NOCOPY varchar2, --File.Sql.39 bug 4440895
836 x_markup_percentage out NOCOPY number, --File.Sql.39 bug 4440895
833 x_status in out NOCOPY number, --File.Sql.39 bug 4440895
834 x_bill_trans_currency_code out NOCOPY varchar2,/* The following four parameters are added for MCB2 */ --File.Sql.39 bug 4440895
835 x_bill_txn_bill_rate out NOCOPY number, --File.Sql.39 bug 4440895
837 x_rate_source_id out NOCOPY number ) IS --File.Sql.39 bug 4440895
838
839 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
840
841 BEGIN
842 /* Change the call and aded new paras in this procs. for MCB2 */
843 IF g1_debug_mode = 'Y' THEN
844 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.Call_Calc_Bill_Amount :');
845 END IF;
846 pa_client_extn_billing.Calc_Bill_Amount(
847 x_transaction_type => x_transaction_type,
848 x_expenditure_item_id => x_expenditure_item_id,
849 x_sys_linkage_function => x_sys_linkage_function,
850 x_amount => x_amount,
851 x_bill_rate_flag => x_bill_rate_flag,
852 x_status => x_status,
853 x_bill_trans_currency_code => x_bill_trans_currency_code,
854 x_bill_txn_bill_rate => x_bill_txn_bill_rate,
855 x_markup_percentage => x_markup_percentage,
856 x_rate_source_id => x_rate_source_id
857 );
858
859
860
861
862 /* Bug 1292444 Commented out this rounding as this is done when updating
863 pa_expenditure_items in pardfp.lpc. Rounding off tmount depending on the
864 currency
865 x_amount := pa_currency.round_currency_amt(x_amount);*/
866
867 IF g1_debug_mode = 'Y' THEN
868 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing.Call_Calc_Bill_Amount :');
869 END IF;
870 EXCEPTION WHEN OTHERS THEN
871 -- DBMS_OUTPUT.PUT(SQLERRM);
872 RAISE;
873
874 END Call_Calc_Bill_Amount;
875
876 PROCEDURE DUMMY IS
877 BEGIN
878 NULL;
879 END;
880
881 /*-----------------------------------------------------------------------------
882 | Procedure Check_Spf_Amounts checks the amounts in summary_project_fundings|
883 | Table. If there are discrepancies it updates the amounts |
884 | |
885 | Parameters are: |
886 | |
887 | X_Option : I - Update Only Invoice Amounts |
888 | R - Update Only Revenue Amounts |
889 | B - Update Both Revenue/Invoice Amounts |
890 | |
891 | X_proj_id : pa_projects.project_id |
892 | X_start_proj_num : Start project Number (pa_projects.segment1) |
893 | X_end_proj_num : End project Number (pa_projects.segment1) |
894 | |
895 | |
896 | Called from : PARGDR - Generate Draft Revenue |
897 | PAIGEN - Generate Draft Invoice |
898 | |
899 | Morg Orientation: Project Orientation. |
900 | |
901 | History: |
902 | 21-Mar-97 N. Chouhan Created |
903 | |
904 -----------------------------------------------------------------------------*/
905
906 PROCEDURE CHECK_SPF_AMOUNTS( X_option in varchar2,
907 X_proj_id in number,
908 X_start_proj_num in varchar2,
909 X_end_proj_num in varchar2) IS
910
911 l_project_id number;
912
913
914 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
915
916 /*--------------------------------------------------------------------------
917 | Cursor For Selecting AND Locking PA_PROJECTS TABLE |
918 --------------------------------------------------------------------------*/
919
920 /* CURSOR sel_proj is
921 SELECT project_id
922 FROM pa_projects
923 WHERE ( ( nvl(X_proj_id,0) <> 0
924 AND project_id = X_proj_id )
925 OR ( nvl(X_proj_id,0) = 0
926 AND segment1 between X_start_proj_num
927 and X_end_proj_num))
928 FOR UPDATE OF project_id; Commented for bug 3372249*/
929
930 /* Fix for bug 3372249 Starts here */
931
932 CURSOR sel_proj is
933 SELECT project_id
934 FROM pa_projects
935 WHERE project_id = X_proj_id
936 FOR UPDATE OF project_id;
937
938 CURSOR sel_proj_seg is
939 SELECT project_id
940 FROM pa_projects
944
941 WHERE segment1 between X_start_proj_num
942 and X_end_proj_num
943 FOR UPDATE OF project_id;
945 /* Fix for bug 3372249 Ends here */
946
947
948
949 /*--------------------------------------------------------------------------
950 | Cursor For Selecting record having 0 accrued revenue |
951 --------------------------------------------------------------------------*/
952 CURSOR spf_acc_0 is
953 SELECT pf.agreement_id, pf.project_id, pf.task_id
954 FROM pa_summary_project_fundings pf
955 WHERE (pf.revproc_accrued_amount <> 0 /* MCB related changes */
956 /* The following added to fix bug 2249216 */
957 OR pf.PROJFUNC_ACCRUED_AMOUNT <> 0
958 OR pf.PROJECT_ACCRUED_AMOUNT <> 0
959 OR pf.TOTAL_ACCRUED_AMOUNT <> 0)
960 /* END fix bug 2249216 */
961 AND pf.project_id = l_project_id
962 AND NOT EXISTS
963 ( SELECT null
964 FROM pa_draft_revenue_items dri,
965 pa_draft_revenues dr
966 WHERE dri.project_id = dr.project_id
967 AND dri.draft_revenue_num = dr.draft_revenue_num
968 AND ( nvl(pf.task_id,0) = 0
969 OR dri.task_id = pf.task_id )
970 AND dr.project_id = pf.project_id
971 AND dr.agreement_id+0 = pf.agreement_id);
972
973 /*--------------------------------------------------------------------------
974 | Cursor For Selecting record having bad accrued revenue data |
975 --------------------------------------------------------------------------*/
976 CURSOR spf_acc_amt is
977 SELECT pf.agreement_id, pf.project_id,
978 decode(p.project_level_funding_flag,'Y',0,pf.task_id) task_fund, /*Decode added for bug 3647592 */
979 sum(dri.amount) dri_amount, dri.revproc_currency_code,
980 sum(dri.projfunc_revenue_amount) dri_projfunc_amount,dri.projfunc_currency_code,
981 sum(dri.project_revenue_amount) dri_project_amount,dri.project_currency_code,
982 sum(dri.funding_revenue_amount) dri_funding_amount,dri.funding_currency_code
983 FROM pa_draft_revenue_items dri,
984 pa_draft_revenues dr,
985 pa_summary_project_fundings pf,
986 pa_projects p /* Added pa_projects for bug 3647592 */
987 WHERE dri.project_id = dr.project_id
988 AND dri.draft_revenue_num = dr.draft_revenue_num
989 AND ( (nvl(pf.task_id,0) = 0 AND nvl(p.project_level_funding_flag,'N')='Y')
990 OR dri.task_id = decode(p.project_level_funding_flag,'Y',0,pf.task_id) ) /* Added decode condition for bug 3647592 */
991 AND dr.project_id+0 = pf.project_id
992 AND dr.agreement_id = pf.agreement_id
993 AND pf.project_id = l_project_id
994 AND p.project_id = pf.project_id
995 AND exists (select 1 from pa_agreements paa where paa.agreement_id = pf.agreement_id
996 and dri.funding_currency_code = paa.agreement_currency_code) /* condition added for Bug 5956273*/
997
998 GROUP BY pf.agreement_id, pf.project_id, decode(p.project_level_funding_flag,'Y',0,pf.task_id),dri.revproc_currency_code,
999 dri.projfunc_currency_code,dri.project_currency_code,
1000 dri.funding_currency_code; /* MCB related changes */
1001
1002 /*--------------------------------------------------------------------------
1003 | Cursor For Selecting record having 0 billed amount |
1004 --------------------------------------------------------------------------*/
1005 CURSOR spf_bill_0 is
1006 SELECT pf.agreement_id, pf.project_id, pf.task_id
1007 FROM pa_summary_project_fundings pf
1008 WHERE (pf.invproc_billed_amount <> 0 /* MCB related changes */
1009 /* The following added to fix bug 2249216 */
1010 OR pf.PROJFUNC_BILLED_AMOUNT <> 0
1011 OR pf.PROJECT_BILLED_AMOUNT <> 0
1012 OR pf.TOTAL_BILLED_AMOUNT <> 0)
1013 /* END fix bug 2249216 */
1014 AND pf.project_id = l_project_id
1015 AND NOT EXISTS
1016 ( SELECT null
1017 FROM pa_draft_invoice_items dii,
1018 pa_draft_invoices di
1019 WHERE dii.project_id = di.project_id
1020 AND dii.draft_invoice_num = di.draft_invoice_num
1021 AND ( nvl(pf.task_id,0) = 0
1022 OR dii.task_id = pf.task_id )
1023 AND di.project_id = pf.project_id
1024 AND dii.invoice_line_type<>'RETENTION' /* added for bug 2822610 */
1025 AND di.agreement_id+0 = pf.agreement_id);
1026
1027 /*--------------------------------------------------------------------------
1028 | Cursor For Selecting record having bad bill amount data for Projects |
1029 | Funded at Project Level |
1030 --------------------------------------------------------------------------*/
1031 CURSOR spf_pl_bill_amt is
1032 SELECT pf.agreement_id, pf.project_id,
1033 sum(dii.amount) dii_amount,dii.invproc_currency_code,
1034 sum(dii.projfunc_bill_amount) dii_projfunc_amount,dii.projfunc_currency_code,
1038 pa_draft_invoices di,
1035 sum(dii.project_bill_amount) dii_project_amount,dii.project_currency_code,
1036 sum(dii.funding_bill_amount) dii_funding_amount,dii.funding_currency_code
1037 FROM pa_draft_invoice_items dii,
1039 pa_summary_project_fundings pf
1040 WHERE dii.project_id = di.project_id
1041 AND dii.draft_invoice_num = di.draft_invoice_num
1042 AND dii.invoice_line_type <> 'RETENTION'
1043 AND di.project_id+0 = pf.project_id
1044 AND di.agreement_id = pf.agreement_id
1045 AND nvl(pf.task_id, 0) = 0
1046 AND pf.project_id = l_project_id
1047 AND pf.total_baselined_amount > 0 /* 2094391 */
1048 AND exists (select 1 from pa_agreements paa where paa.agreement_id = pf.agreement_id
1049 and dii.funding_currency_code = paa.agreement_currency_code) /* condition added for Bug 5956273*/
1050 GROUP BY pf.agreement_id, pf.project_id
1051 ,dii.invproc_currency_code,dii.projfunc_currency_code,dii.project_currency_code
1052 , dii.funding_currency_code; /* MCB related changes */
1053
1054 /*--------------------------------------------------------------------------
1055 | Cursor For Selecting record having bad bill amount data for Projects |
1056 | Funded at Task Level |
1057 --------------------------------------------------------------------------*/
1058 CURSOR spf_tl_bill_amt is
1059 /* This new currency procs. is being used which covers the MCB2 as well as old functionality */
1060
1061 SELECT pf.agreement_id, pf.project_id, pf.task_id
1062 ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(sum(dii.amount * (1 -
1063 ( nvl(di.retention_percentage,0)/100 )) ), dii.invproc_currency_code) dii_amount,dii.invproc_currency_code,
1064 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(sum(dii.projfunc_bill_amount * (1 -
1065 ( nvl(di.retention_percentage,0)/100 )) ),dii.projfunc_currency_code) dii_projfunc_amount,dii.projfunc_currency_code,
1066 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(sum(dii.project_bill_amount * (1 -
1067 ( nvl(di.retention_percentage,0)/100 )) ),dii.project_currency_code) dii_project_amount,dii.project_currency_code,
1068 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(sum(dii.funding_bill_amount * (1 -
1069 ( nvl(di.retention_percentage,0)/100 )) ),dii.funding_currency_code) dii_funding_amount,dii.funding_currency_code
1070 FROM pa_draft_invoice_items dii,
1071 pa_draft_invoices di,
1072 pa_summary_project_fundings pf
1073 WHERE dii.project_id = di.project_id /* Bug#5081194 : Removed the +0 for perf issue */
1074 AND dii.draft_invoice_num+0 = di.draft_invoice_num
1075 AND pf.task_id = dii.task_id
1076 AND dii.invoice_line_type <> 'RETENTION'
1077 AND di.project_id = pf.project_id /* Bug#5081194 : Removed the +0 in di.project_id for perf issue */
1078 AND di.agreement_id = pf.agreement_id
1079 AND pf.project_id = l_project_id
1080 AND pf.project_id = dii.project_id /* Bug#5081194 : added this condition */
1081 AND pf.total_baselined_amount > 0 /* added for bug 3464050 */
1082 AND exists (select 1 from pa_agreements paa where paa.agreement_id = pf.agreement_id
1083 and dii.funding_currency_code = paa.agreement_currency_code) /* condition added for Bug 5956273*/
1084 GROUP BY pf.agreement_id, pf.project_id, pf.task_id
1085 ,dii.invproc_currency_code,dii.projfunc_currency_code,
1086 dii.project_currency_code,dii.funding_currency_code; /* MCB related changes */
1087
1088 BEGIN
1089 IF g1_debug_mode = 'Y' THEN
1090 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.CHECK_SPF_AMOUNTS :');
1091 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.CHECK_SPF_AMOUNTS X_option : '||X_option);
1092 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.CHECK_SPF_AMOUNTS X_proj_id : '||X_proj_id);
1093 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.CHECK_SPF_AMOUNTS X_start_proj_num : '||X_start_proj_num);
1094 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.CHECK_SPF_AMOUNTS X_end_proj_num : '||X_end_proj_num);
1095 END IF;
1096
1097 -- OPEN sel_proj; Commented for bug 3372249
1098
1099 /* Start of fix for bug 3372249 */
1100 IF(nvl(X_proj_id,0) <> 0) THEN
1101 OPEN sel_proj;
1102 ELSE
1103 OPEN sel_proj_seg;
1104 END IF;
1105 /* End of fix for bug 3372249 */
1106
1107 LOOP
1108
1109 -- FETCH sel_proj into l_project_id; Commented for bug 3372249
1110
1111 /* Start of fix for bug 3372249 */
1112 IF(nvl(X_proj_id,0) <> 0) THEN
1113 FETCH sel_proj into l_project_id;
1114 ELSE
1115 FETCH sel_proj_seg into l_project_id;
1116 END IF;
1117 /* End of fix for bug 3372249 */
1118
1119 IF g1_debug_mode = 'Y' THEN
1120 PA_MCB_INVOICE_PKG.log_message('Inside pa_billing.CHECK_SPF_AMOUNTS cursor sel_proj : ');
1121 END IF;
1122
1123 -- EXIT WHEN sel_proj%NOTFOUND; Commented for bug 3372249
1124 /* Start of fix for bug 3372249 */
1125 IF(nvl(X_proj_id,0) <> 0) THEN
1126 EXIT WHEN sel_proj%NOTFOUND;
1127 ELSE
1128 EXIT WHEN sel_proj_seg%NOTFOUND;
1129 END IF;
1130 /* End of fix for bug 3372249 */
1131
1132
1133 IF (X_option in ('R','B')) THEN
1134
1135 /*-----------------------------------------------------------------------
1139
1136 | Updating Total Accrued Revenue column which should be zero |
1137 -----------------------------------------------------------------------*/
1138 FOR acc_0_rec in spf_acc_0 LOOP
1140 IF g1_debug_mode = 'Y' THEN
1141 PA_MCB_INVOICE_PKG.log_message('Inside pa_billing.CHECK_SPF_AMOUNTS cursor spf_acc_0 : ');
1142 END IF;
1143 UPDATE pa_summary_project_fundings pf
1144 SET pf.total_accrued_amount = 0,
1145 pf.revproc_accrued_amount = 0, /* MCB related changes */
1146 pf.projfunc_accrued_amount = 0,
1147 pf.project_accrued_amount = 0
1148 WHERE pf.agreement_id = acc_0_rec.agreement_id
1149 AND pf.project_id = acc_0_rec.project_id
1150 AND nvl(pf.task_id,0) = nvl(acc_0_rec.task_id,0); /* MCB related changes */
1151
1152 END LOOP;
1153
1154
1155 /*-----------------------------------------------------------------------
1156 | Updating Total Accrued Revenue column |
1157 -----------------------------------------------------------------------*/
1158
1159 FOR acc_amt_rec in spf_acc_amt LOOP
1160 /* This new currency procs. is being used which covers the MCB2 as well as old functionality */
1161
1162 IF g1_debug_mode = 'Y' THEN
1163 PA_MCB_INVOICE_PKG.log_message('Inside pa_billing.CHECK_SPF_AMOUNTS cursor spf_acc_amt : ');
1164 END IF;
1165 UPDATE pa_summary_project_fundings pf
1166 SET pf.total_accrued_amount =
1167 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1168 (acc_amt_rec.dri_funding_amount,acc_amt_rec.funding_currency_code),
1169 pf.revproc_accrued_amount =
1170 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1171 (acc_amt_rec.dri_amount,acc_amt_rec.revproc_currency_code),
1172 pf.projfunc_accrued_amount =
1173 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1174 (acc_amt_rec.dri_projfunc_amount,acc_amt_rec.projfunc_currency_code),
1175 pf.project_accrued_amount =
1176 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1177 (acc_amt_rec.dri_project_amount,acc_amt_rec.project_currency_code)
1178 WHERE pf.agreement_id = acc_amt_rec.agreement_id
1179 AND pf.project_id = acc_amt_rec.project_id
1180 AND nvl(pf.task_id,0) = nvl(acc_amt_rec.task_fund,0); /* changed task_id to task_fund for bug 3647592 */ /* added semi-colon for bug 3717388*/
1181 /*** AND nvl(pf.total_baselined_amount,0) <>0; ***//* MCB related changes */
1182 /* AND nvl(pf.total_baselined_amount,0) >= 0; commented for bug 3717388 */ /* Changed condition bug 2842994 */
1183
1184 END LOOP;
1185
1186 END IF;
1187
1188
1189 IF (X_option in ('I','B')) THEN
1190
1191 /*-----------------------------------------------------------------------
1192 | Updating Zero Total Billed amount column. |
1193 -----------------------------------------------------------------------*/
1194
1195 FOR bill_0_rec in spf_bill_0 LOOP
1196
1197 IF g1_debug_mode = 'Y' THEN
1198 PA_MCB_INVOICE_PKG.log_message('Inside pa_billing.CHECK_SPF_AMOUNTS cursor spf_bill_0 : ');
1199 END IF;
1200 UPDATE pa_summary_project_fundings pf
1201 SET pf.total_billed_amount = 0,
1202 pf.invproc_billed_amount = 0, /* MCB related changes */
1203 pf.projfunc_billed_amount = 0,
1204 pf.project_billed_amount = 0
1205 WHERE pf.agreement_id = bill_0_rec.agreement_id
1206 AND pf.project_id = bill_0_rec.project_id
1207 AND nvl(pf.task_id,0) = nvl(bill_0_rec.task_id,0); /* MCB related changes */
1208
1209 END LOOP;
1210
1211
1212 /*-----------------------------------------------------------------------
1213 | Updating Total Billed Amount column for Project Level Funding |
1214 -----------------------------------------------------------------------*/
1215
1216 FOR pl_bill_amt_rec in spf_pl_bill_amt LOOP
1217 /* This new currency procs. is being used which covers the MCB2 as well as old functionality */
1218
1219 IF g1_debug_mode = 'Y' THEN
1220 PA_MCB_INVOICE_PKG.log_message('Inside pa_billing.CHECK_SPF_AMOUNTS cursor spf_pl_bill_amt : ');
1221 END IF;
1222 UPDATE pa_summary_project_fundings pf
1223 SET pf.total_billed_amount =
1224 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1225 (pl_bill_amt_rec.dii_funding_amount,pl_bill_amt_rec.funding_currency_code),
1226 pf.invproc_billed_amount =
1227 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1228 (pl_bill_amt_rec.dii_amount,pl_bill_amt_rec.invproc_currency_code),
1229 pf.projfunc_billed_amount =
1230 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1231 (pl_bill_amt_rec.dii_projfunc_amount,pl_bill_amt_rec.projfunc_currency_code),
1232 pf.project_billed_amount =
1233 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1234 (pl_bill_amt_rec.dii_project_amount,pl_bill_amt_rec.project_currency_code)
1235 WHERE pf.agreement_id = pl_bill_amt_rec.agreement_id
1236 AND pf.project_id = pl_bill_amt_rec.project_id /* MCB related changes */
1240
1237 AND nvl(pf.task_id,0) = 0; /* 2094391 */
1238
1239 END LOOP;
1241
1242 /*-----------------------------------------------------------------------
1243 | Updating Total Billed Amount column for Task Level Funding |
1244 -----------------------------------------------------------------------*/
1245
1246 FOR tl_bill_amt_rec in spf_tl_bill_amt LOOP
1247
1248 /* This new currency procs. is being used which covers the MCB2 as well as old functionality */
1249
1250 IF g1_debug_mode = 'Y' THEN
1251 PA_MCB_INVOICE_PKG.log_message('Inside pa_billing.CHECK_SPF_AMOUNTS cursor spf_tl_bill_amt : ');
1252 END IF;
1253
1254 UPDATE pa_summary_project_fundings pf
1255 SET pf.total_billed_amount =
1256 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1257 (tl_bill_amt_rec.dii_funding_amount,tl_bill_amt_rec.funding_currency_code),
1258 pf.invproc_billed_amount =
1259 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1260 (tl_bill_amt_rec.dii_amount,tl_bill_amt_rec.invproc_currency_code),
1261 pf.projfunc_billed_amount =
1262 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1263 (tl_bill_amt_rec.dii_projfunc_amount,tl_bill_amt_rec.projfunc_currency_code),
1264 pf.project_billed_amount =
1265 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT
1266 (tl_bill_amt_rec.dii_project_amount,tl_bill_amt_rec.project_currency_code)
1267 WHERE pf.agreement_id = tl_bill_amt_rec.agreement_id
1268 AND pf.project_id = tl_bill_amt_rec.project_id
1269 AND pf.task_id = tl_bill_amt_rec.task_id; /* MCB related changes */
1270
1271 END LOOP;
1272
1273 END IF;
1274
1275 END LOOP;
1276
1277 -- CLOSE sel_proj; Commented for bug 3372249
1278 /* Start of fix for bug 3372249 */
1279 IF(nvl(X_proj_id,0) <> 0) THEN
1280 CLOSE sel_proj;
1281 ELSE
1282 CLOSE sel_proj_seg;
1283 END IF;
1284 /* End of fix for bug 3372249 */
1285
1286 IF g1_debug_mode = 'Y' THEN
1287 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing.CHECK_SPF_AMOUNTS :');
1288 END IF;
1289 EXCEPTION
1290 WHEN OTHERS THEN
1291 -- CLOSE sel_proj; Commented for bug 3372249
1292 /* Start of fix for bug 3372249 */
1293 IF(nvl(X_proj_id,0) <> 0) THEN
1294 CLOSE sel_proj;
1295 ELSE
1296 CLOSE sel_proj_seg;
1297 END IF;
1298 /* End of fix for bug 3372249 */
1299 RAISE;
1300
1301
1302 END CHECK_SPF_AMOUNTS;
1303
1304 PROCEDURE Get_WriteOff_Revenue_Amount (p_project_id IN NUMBER DEFAULT NULL,
1305 p_task_id IN NUMBER DEFAULT NULL,
1306 p_agreement_id IN NUMBER DEFAULT NULL,
1307 p_funding_flag IN VARCHAR2 DEFAULT NULL,
1308 p_writeoff_amount IN OUT NOCOPY NUMBER, /* It is funding currency MCB */ --File.Sql.39 bug 4440895
1309 x_projfunc_writeoff_amount OUT NOCOPY NUMBER, /* MCB related changes */ --File.Sql.39 bug 4440895
1310 x_project_writeoff_amount OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1311 x_revproc_writeoff_amount OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1312 ) IS
1313 /* ------------------------------------------------------------------------
1314 || Procedure : Get_WriteOff_Revenue_Amount ||
1315 || Description : To get Revenue WriteOff Amount ||
1316 || Parameters : Project ID (IN) ||
1317 || Task ID (IN) ||
1318 || Agreement ID (IN) ||
1319 || Funding Flag (IN) ||
1320 || WriteOff Amount (IN) (OUT) ||
1321 || Projfunc Writeoff Amount (OUT) ||
1322 || Project Writeoff Amount (OUT) ||
1323 || Revproc Writeoff Amount (OUT) ||
1324 --------------------------------------------------------------------------*/
1325
1326
1327 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1328 l_writeoff_amount NUMBER := p_writeoff_amount ;
1329
1330 BEGIN
1331
1332 IF g1_debug_mode = 'Y' THEN
1333 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.Get_WriteOff_Revenue_Amount :');
1334 END IF;
1335 BEGIN
1336
1337 IF p_task_id IS NOT NULL THEN
1338
1339
1340 IF p_agreement_id IS NOT NULL THEN
1341
1342 /*
1343 | If the search has project id, task id, agreement id
1344 | Driving Path Events -> Event Types -> ERDL -> DR */
1345
1346
1347 SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
1348 SUM(NVL(ERDL.projfunc_revenue_amount,0)),SUM(NVL(ERDL.funding_revenue_amount,0))
1349 INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
1353 PA_EVENT_TYPES ET,
1350 x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
1351 FROM PA_CUST_EVENT_RDL_ALL ERDL,
1352 PA_DRAFT_REVENUES_ALL DR,
1354 PA_EVENTS E
1355 WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
1356 AND ERDL.PROJECT_ID=DR.PROJECT_ID
1357 AND E.EVENT_NUM = ERDL.EVENT_NUM
1358 AND E.TASK_ID = ERDL.TASK_ID
1359 AND ET.EVENT_TYPE = E.EVENT_TYPE
1360 AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
1361 AND E.PROJECT_ID = ERDL.PROJECT_ID
1362 AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL,
1363 NULL, E.PROJECT_ID)
1364 AND E.TASK_ID = p_task_id
1365 AND NVL(DR.AGREEMENT_ID,0) = NVL(p_agreement_id,DR.AGREEMENT_ID)
1366 AND E.PROJECT_ID = p_project_id
1367 AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM;
1368
1369 ELSE
1370
1371 /*
1372 | If the search has project id, task id, agreement id is null
1373 | Driving Path Events -> Event Types */
1374
1375 /* MCB related changes */
1376 SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
1377 SUM(NVL(ERDL.projfunc_revenue_amount,0)),
1378 DECODE(p_funding_flag,'Y',SUM(NVL(ERDL.funding_revenue_amount,0)),0)
1379 INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
1380 x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
1381 FROM PA_CUST_EVENT_RDL_ALL ERDL,
1382 PA_EVENT_TYPES ET,
1383 PA_EVENTS E
1384 WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
1385 AND E.EVENT_NUM = ERDL.EVENT_NUM
1386 AND E.TASK_ID = ERDL.TASK_ID
1387 AND ET.EVENT_TYPE = E.EVENT_TYPE
1388 AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
1389 AND E.PROJECT_ID = ERDL.PROJECT_ID
1390 AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL,
1391 NULL, E.PROJECT_ID)
1392 AND E.TASK_ID = p_task_id
1393 AND E.PROJECT_ID = p_project_id;
1394
1395 /* This select is commented for MCB2, the same objective is fulfill by the above select */
1396 /* SELECT SUM(NVL(E.revenue_amount,0)),SUM(NVL(E.project_revenue_amount,0)),
1397 SUM(NVL(E.projfunc_revenue_amount,0)),SUM(NVL(E.funding_revenue_amount,0))
1398 INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
1399 x_projfunc_writeoff_amount,p_writeoff_amount
1400 FROM PA_EVENT_TYPES ET,
1401 PA_EVENTS E
1402 WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
1403 AND ET.EVENT_TYPE = E.EVENT_TYPE
1404 AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
1405 AND E.TASK_ID = p_task_id
1406 AND E.PROJECT_ID = p_project_id;
1407 */
1408
1409 END IF;
1410
1411 ELSIF p_project_id IS NOT NULL THEN
1412
1413
1414 IF p_agreement_id IS NOT NULL THEN
1415
1416 /*
1417 | If Project id is not null, agreement id is not null
1418 | Driving Path Events -> Event Types -> ERDL -> DR */
1419
1420
1421 SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
1422 SUM(NVL(ERDL.projfunc_revenue_amount,0)),SUM(NVL(ERDL.funding_revenue_amount,0))
1423 INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
1424 x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
1425 FROM PA_CUST_EVENT_RDL_ALL ERDL,
1426 PA_DRAFT_REVENUES_ALL DR,
1427 PA_EVENT_TYPES ET,
1428 PA_EVENTS E
1429 WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
1430 AND ERDL.PROJECT_ID=DR.PROJECT_ID
1431 AND E.EVENT_NUM = ERDL.EVENT_NUM
1432 AND ET.EVENT_TYPE = E.EVENT_TYPE
1433 AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
1434 AND E.PROJECT_ID = ERDL.PROJECT_ID
1435 AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL, NULL, E.PROJECT_ID)
1436 AND NVL(DR.AGREEMENT_ID,0) = NVL(p_agreement_id,DR.AGREEMENT_ID)
1437 AND E.PROJECT_ID = p_project_id
1438 AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
1439 AND NVL(E.TASK_ID,0) = NVL(ERDL.TASK_ID,0); /* Added for bug 1504680 */
1440 ELSE
1441
1442 /*
1443 | If Project id is not null, agreement id is null
1444 | Driving Path Events -> Event Types */
1445
1446 /* MCB related changes */
1447 SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
1448 SUM(NVL(ERDL.projfunc_revenue_amount,0)),
1452 FROM PA_CUST_EVENT_RDL_ALL ERDL,
1449 DECODE(p_funding_flag,'Y',SUM(NVL(ERDL.funding_revenue_amount,0)),0)
1450 INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
1451 x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
1453 PA_EVENT_TYPES ET,
1454 PA_EVENTS E
1455 WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG ,'N') = 'Y'
1456 AND E.EVENT_NUM = ERDL.EVENT_NUM
1457 AND ET.EVENT_TYPE = E.EVENT_TYPE
1458 AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
1459 AND E.PROJECT_ID = ERDL.PROJECT_ID
1460 AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL, NULL, E.PROJECT_ID)
1461 AND NVL(E.TASK_ID,0) = NVL(ERDL.TASK_ID,0) /* Added for bug 1504680 */
1462 AND E.PROJECT_ID = p_project_id;
1463
1464 /* This select is commented for MCB2, the same objective is fulfill by the above select */
1465 /*
1466 SELECT SUM(NVL(E.revenue_amount,0)),SUM(NVL(E.project_revenue_amount,0)),
1467 SUM(NVL(E.projfunc_revenue_amount,0)),SUM(NVL(E.funding_revenue_amount,0))
1468 INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
1469 x_projfunc_writeoff_amount,p_writeoff_amount
1470 FROM PA_EVENT_TYPES ET,
1471 PA_EVENTS E
1472 WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
1473 AND ET.EVENT_TYPE = E.EVENT_TYPE
1474 AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
1475 AND E.PROJECT_ID = p_project_id; */
1476
1477 END IF;
1478
1479 ELSIF p_agreement_id IS NOT NULL THEN
1480
1481 /*
1482 | If Agreement id is not null, agreement id (might be null)
1483 | Driving path DR -> ERDL -> Events -> Event Types */
1484
1485
1486 SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
1487 SUM(NVL(ERDL.projfunc_revenue_amount,0)),SUM(NVL(ERDL.funding_revenue_amount,0))
1488 INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
1489 x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
1490 FROM PA_CUST_EVENT_RDL_ALL ERDL,
1491 PA_DRAFT_REVENUES_ALL DR,
1492 PA_EVENT_TYPES ET,
1493 PA_EVENTS E
1494 WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
1495 AND ERDL.PROJECT_ID=DR.PROJECT_ID
1496 AND E.EVENT_NUM = ERDL.EVENT_NUM
1497 AND ET.EVENT_TYPE = E.EVENT_TYPE
1498 AND ET.EVENT_TYPE_CLASSIFICATION ||''='WRITE OFF'
1499 AND E.PROJECT_ID = ERDL.PROJECT_ID
1500 AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL,NULL, E.PROJECT_ID)
1501 AND DR.AGREEMENT_ID = p_agreement_id
1502 AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
1503 AND NVL(E.TASK_ID,0) = NVL(ERDL.TASK_ID,0); /* Added for bug 1504680 */
1504
1505
1506 ELSE
1507 /*
1508 | If Project ID is null, Task ID is null, Agreement ID is null
1509 | Driving path Event Type -> Events -> ERDL -> DR */
1510
1511
1512 SELECT SUM(NVL(ERDL.amount,0)),SUM(NVL(ERDL.project_revenue_amount,0)),
1513 SUM(NVL(ERDL.projfunc_revenue_amount,0)),SUM(NVL(ERDL.funding_revenue_amount,0))
1514 INTO x_revproc_writeoff_amount,x_project_writeoff_amount,
1515 x_projfunc_writeoff_amount,p_writeoff_amount /* MCB related changes */
1516 FROM PA_CUST_EVENT_RDL_ALL ERDL,
1517 PA_DRAFT_REVENUES_ALL DR,
1518 PA_EVENT_TYPES ET,
1519 PA_EVENTS E
1520 WHERE NVL(E.REVENUE_DISTRIBUTED_FLAG , 'N') = 'Y'
1521 AND ERDL.PROJECT_ID=DR.PROJECT_ID
1522 AND E.EVENT_NUM = ERDL.EVENT_NUM
1523 AND ET.EVENT_TYPE = E.EVENT_TYPE
1524 AND ET.EVENT_TYPE_CLASSIFICATION ='WRITE OFF'
1525 AND E.PROJECT_ID = ERDL.PROJECT_ID
1526 AND ERDL.PROJECT_ID = DECODE(ET.EVENT_TYPE,NULL,
1527 NULL, E.PROJECT_ID)
1528 AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
1529 AND NVL(E.TASK_ID,0) = NVL(ERDL.TASK_ID,0); /* Added for bug 1504680 */
1530
1531 END IF;
1532
1533 IF g1_debug_mode = 'Y' THEN
1534 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing.Get_WriteOff_Revenue_Amount :');
1535 END IF;
1536 EXCEPTION
1537
1538 WHEN OTHERS THEN
1539 p_writeoff_amount := l_writeoff_amount; -- NOCOPY
1540 x_projfunc_writeoff_amount := NULL;
1541 x_project_writeoff_amount := NULL;
1542 x_revproc_writeoff_amount := NULL;
1543 RAISE;
1544 END;
1545
1546 END Get_WriteOff_Revenue_Amount;
1547
1548 PROCEDURE forecast_rev_billamount
1549 (NC in out NOCOPY number, --File.Sql.39 bug 4440895
1553 rows_this_time number,
1550 process_irs in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1551 process_bill_rate in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1552 message_code in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1554 error_code in out NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1555 reason in out NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1556 bill_amount in out NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1557 d_rule_decode in out NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1558 sl_function in out NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1559 ei_id in PA_PLSQL_DATATYPES.IdTabTyp,
1560 t_rev_irs_id in out NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1561 rev_comp_set_id in out NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1562 rev_amount in out NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1563 mcb_flag in out NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
1564 x_bill_trans_currency_code in out NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1565 x_bill_trans_bill_rate in out NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
1566 x_rate_source_id in out NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
1567 x_markup_percentage in out NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
1568 ) is
1569
1570 /*--------------------------------------------------------------------------------------
1571 declare all the memory variables.
1572 --------------------------------------------------------------------------------------*/
1573
1574
1575 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1576
1577 system_error EXCEPTION;
1578 amount number;
1579 rate_sch_rev_id number;
1580 compiled_set_id number;
1581 status number;
1582 stage number;
1583 bill_rate_flag varchar2(2);
1584 sys_linkage_func varchar2(30);
1585 insert_error_message boolean;
1586 fetched_amount boolean;
1587 l_ind_cost_acct number := NULL;
1588 l_ind_cost_denm number := NULL;
1589 labor_sch_type varchar2(2);
1590 j number;
1591 /* Bug# 2208288 */
1592 l_ind_cost_project number := NULL;
1593
1594 bill_trans_currency_code VARCHAR2(15);
1595 bill_trans_bill_rate NUMBER;
1596 rate_source_id NUMBER;
1597 markup_percentage NUMBER;
1598 l_mcb_cost_flag varchar2(50); /* Added for bug 2638840 */
1599
1600 BEGIN
1601
1602
1603 /*--------------------------------------------------------------------------------------
1604 initialize array index j to 1,
1605 initialize flags which determine whether irs, bill rate
1606 schedules need to be processed or not
1607 --------------------------------------------------------------------------------------*/
1608
1609 /* Indicator varoiables Bug# 634414 */
1610 NC := 1201;
1611 j := 1;
1612 process_irs:= 'N';
1613 process_bill_rate:= 'N';
1614 message_code:= 'No errors while processing IRS....';
1615 /*--------------------------------------------------------------------------------------
1616 loop until all 100 ei's are processed
1617 -------------------------------------------------------------------------------------*/
1618
1619 WHILE j <= rows_this_time LOOP
1620 /* Indicator Variables Bug#634414 */
1621 NC := 1202;
1622 l_mcb_cost_flag := NULL; /* Added for bug 2638840 */
1623
1624 error_code( j ) := 0;
1625 reason( j ) := NULL;
1626 rate_sch_rev_id := NULL;
1627 compiled_set_id := NULL;
1628 amount := NULL;
1629 insert_error_message := FALSE;
1630 fetched_amount := FALSE;
1631
1632 /*-------------------------------------------------------------------------------------
1633 Call a client extension to fetch the bill amount for the ei.
1634 This has to be done for Labor exp items which have WORK
1635 distribution rule for Revenue or Invoice.
1636 -------------------------------------------------------------------------------------*/
1637
1638 bill_amount( j ) := NULL;
1639 /* Indicator Variables Bug# 634414 */
1640 NC := 1203;
1641
1642 IF ( d_rule_decode( j ) = 1 AND
1643 sl_function( j ) < 2 ) THEN
1644
1645
1646 amount := NULL;
1647 status := 0;
1648 bill_rate_flag := ' ';
1649
1650 IF sl_function( j ) = 0 THEN
1651 sys_linkage_func := 'ST';
1652 ELSIF sl_function( j ) = 1 THEN
1653 sys_linkage_func := 'OT';
1654 ELSIF sl_function( j ) = 2 THEN
1655 sys_linkage_func := 'ER';
1656 ELSIF sl_function( j ) = 3 THEN
1657 sys_linkage_func := 'USG';
1658 ELSIF sl_function( j ) = 4 THEN
1659 sys_linkage_func := 'VI';
1660 ELSE
1661 sys_linkage_func := NULL;
1662 END IF;
1663 /* Indicator variables Bug# 634414 */
1664 NC := 1204;
1665 /* MCB Changes : Added the new param to the procedure Call_Calc_Bill_Amount
1666 */
1667 pa_billing.Call_Calc_Bill_Amount( 'ACTUAL',ei_id( j ),
1668 sys_linkage_func,
1669 amount,
1670 bill_rate_flag,
1674 rate_source_id,
1671 status,
1672 bill_trans_currency_code,
1673 bill_trans_bill_rate,
1675 markup_percentage);
1676 /* Indicator variables Bug# 634414 */
1677 NC := 1205;
1678 IF ( status = 0 and amount is null ) THEN
1679 null;
1680 ELSIF ( status = 0 and amount is not null ) THEN
1681 bill_amount( j ) := to_char(amount);
1682 fetched_amount := TRUE;
1683 /* Indicator variables bug# 634414 */
1684 /* MCB Changes : Copy the output variables from the procedure Call_Calc_Bill_Amount
1685 to array variables */
1686
1687
1688 x_bill_trans_currency_code(j) := bill_trans_currency_code;
1689 x_bill_trans_bill_rate(j) := to_char(bill_trans_bill_rate);
1690 x_rate_source_id(j) := rate_source_id;
1691 x_markup_percentage(j) := to_char(markup_percentage);
1692
1693 /* End MCB Changes */
1694
1695 process_irs:= 'Y';
1696 ELSIF ( status > 0 ) THEN
1697 fetched_amount := TRUE;
1698 reason( j ) := 'CALC_BILL_AMOUNT_EXT_FAIL';
1699 error_code( j ) := 1;
1700 bill_amount( j ) := NULL;
1701
1702 /* MCB Changes : Initialize the MCB related columns */
1703
1704 x_bill_trans_currency_code(j) := NULL;
1705 x_bill_trans_bill_rate(j) := NULL;
1706 x_rate_source_id(j) := NULL;
1707 x_markup_percentage(j) := NULL;
1708 ELSE
1709 RAISE system_error;
1710 END IF;
1711
1712 END IF;
1713
1714 /*-------------------------------------------------------------------------------------
1715
1716 For Revenue :
1717 -------------
1718 check whether revenue distribution is WORK, labor/non labor
1719 schedule type is Indirect, irs sch id exists and ei is labor/
1720 non labor. If all of this is true only then call the api to
1721 calculate the indirect cost for Revenue.
1722
1723 For Labor/non Labor expenditure items :
1724 -------------------------------------
1725 -------------------------------------------------------------------------------------*/
1726
1727 /* The host variable array t_lab_sch is not used because of the ORA-1458
1728 Error (Invalid Length inside a variable string). Instead the select
1729 statement below has been used for populating labor_sch_type .
1730 This is a workaround and needs to be removed in future the select
1731 below is unneccessary and will affect performance */
1732 /* Indicator variables Bug# 634414 */
1733 NC := 1206;
1734 select t.labor_sch_type
1735 into labor_sch_type
1736 from pa_tasks t, pa_expenditure_items_all e
1737 where t.task_id = e.task_id
1738 and e.expenditure_item_id = ei_id( j );
1739 /* Indicator variables Bug# 634414 */
1740 NC := 1207;
1741 IF ( d_rule_decode( j ) = 1 AND
1742 t_rev_irs_id( j ) IS NOT NULL AND
1743 labor_sch_type = 'I' ) AND
1744 NOT fetched_amount THEN
1745
1746
1747 /* Bug # 2208288 - Added the param l_ind_cost_project */
1748
1749 pa_cost_plus.get_exp_item_indirect_cost(
1750 ei_id( j ), 'R',
1751 amount,
1752 l_ind_cost_acct,
1753 l_ind_cost_denm,
1754 l_ind_cost_project,
1755 rate_sch_rev_id,
1756 compiled_set_id,
1757 status, stage );
1758
1759 /*-------------------------------------------------------------------------------------
1760
1761 Check for success/failure of the called api :
1762 ---------------------------------------------
1763
1764 check whether indirect amount and sch rev id were retrieved successfully,
1765 if yes then assign these values to the host array variables for indirect
1766 amount and rate sct rev id respectively, else set error code to 1 which
1767 stands for 'NO COMPILED MULTIPLIER'.
1768
1769 --------------------------------------------------------------------------------------*/
1770 /* Indicator Variables Bug# 634414 */
1771 NC := 1208;
1772 IF ( status = 100 and stage <> 400 ) THEN
1773 rev_comp_set_id( j ) := NULL;
1774 rev_amount( j ) := NULL;
1775 error_code( j ) := 1;
1776 message_code:= 'Error encountered during processing IRS....' ;
1777 insert_error_message := TRUE;
1778
1779 /*-----------------------------------------------------------------------------------------
1783 ELSIF ( status = 100 and stage = 400 ) THEN
1780 NO_COST_BASE case whereby raw_revenue amount should be populated with
1781 raw_cost.
1782 ----------------------------------------------------------------------------------------*/
1784
1785 rev_comp_set_id( j ):= 0;
1786 rev_amount( j ) := to_char(0);
1787 process_irs:= 'Y';
1788 /*-----------------------------------------------------------------------------------------
1789 If everything is retrieved as expected which means success.
1790 ----------------------------------------------------------------------------------------*/
1791 ELSIF ( rate_sch_rev_id IS NOT NULL AND
1792 compiled_set_id IS NOT NULL AND
1793 amount IS NOT NULL AND
1794 status = 0 ) THEN
1795
1796 rev_comp_set_id( j ):= compiled_set_id;
1797 /* MCB changes : If Multi currency billing enabled then take the denom cost otherwise take the amount */
1798
1799 IF (mcb_flag(j) = 'Y') THEN
1800 /* Commenting the following line for bug 2638840
1801 rev_amount( j ) := to_char(l_ind_cost_denm); */
1802
1803 /* Changes for bug 2638840 */
1804 /* Bug 2638840 : Get the BTC_COST_BASE_REV_CODE from pa_projects_all table */
1805 BEGIN
1806
1807 /* Added the following nvl so that code doesn't break even if upgrade script fails - For bug 2724185 */
1808
1809 select nvl(BTC_COST_BASE_REV_CODE,'EXP_TRANS_CURR')
1810 into l_mcb_cost_flag
1811 from pa_projects_all
1812 where project_id =(select project_id from pa_expenditure_items_all where expenditure_item_id=ei_id(j));
1813
1814 EXCEPTION
1815 WHEN NO_DATA_FOUND THEN
1816 IF g1_debug_mode = 'Y' THEN
1817 PA_MCB_INVOICE_PKG.log_message('forecast_rev_billamount: ' || 'No Data Found for the ei_id:' || ei_id(j));
1818 END IF;
1819 RAISE system_error;
1820 END;
1821
1822 IF g1_debug_mode = 'Y' THEN
1823 PA_MCB_INVOICE_PKG.log_message('forecast_rev_billamount: ' || 'BTC_COST_BASE_REV_CODE :' || l_mcb_cost_flag);
1824 PA_MCB_INVOICE_PKG.log_message('forecast_rev_billamount: ' || 'mcb_cost_bug l_ind_cost_denm ' || l_ind_cost_denm);
1825 PA_MCB_INVOICE_PKG.log_message('forecast_rev_billamount: ' || 'mcb_cost_bug l_ind_cost_acct ' || l_ind_cost_acct);
1826 PA_MCB_INVOICE_PKG.log_message('forecast_rev_billamount: ' || 'mcb_cost_bug amount ' || amount);
1827 PA_MCB_INVOICE_PKG.log_message('forecast_rev_billamount: ' || 'mcb_cost_bug l_indirect_cost_project ' || l_ind_cost_project);
1828 END IF;
1829 /* Bug 2638840 : Based on the BTC get the cost amount */
1830 IF (l_mcb_cost_flag = 'EXP_TRANS_CURR') THEN
1831
1832 rev_amount( j ) := to_char(l_ind_cost_denm);
1833
1834 ELSIF (l_mcb_cost_flag = 'EXP_FUNC_CURR') THEN
1835
1836 rev_amount( j ) := to_char(l_ind_cost_acct);
1837
1838 ELSIF (l_mcb_cost_flag = 'PROJ_FUNC_CURR') THEN
1839
1840 rev_amount( j ) := to_char(amount);
1841
1842 ELSIF (l_mcb_cost_flag = 'PROJECT_CURR') THEN
1843
1844 rev_amount( j ) := to_char(l_ind_cost_project);
1845
1846 END IF;
1847
1848
1849 IF g1_debug_mode = 'Y' THEN
1850 PA_MCB_INVOICE_PKG.log_message('forecast_rev_billamount: ' || 'mcb_cost_bug rev_amount ' || rev_amount(j));
1851 END IF;
1852 /* End of Changes for bug 2638840 */
1853 ELSE
1854 rev_amount( j ) := to_char(amount);
1855 END IF;
1856 process_irs:= 'Y';
1857 /*-----------------------------------------------------------------------------------------
1858 This case maynot arise, but has been added for safety reasons.
1859 --------------------------------------------------------------------------------------*/
1860 ELSE
1861
1862 RAISE system_error;
1863 END IF;
1864
1865 /*--------------------------------------------------------------------------------------------
1866 if no condition satisfies which indirectly means that we need to process
1867 for bill rate schedule.
1868 -------------------------------------------------------------------------------------------*/
1869 ELSE
1870 process_bill_rate:= 'Y';
1871 rev_comp_set_id( j ) := NULL;
1872 rev_amount( j ) := NULL;
1873
1874 END IF;
1875
1876
1877 /*--------------------------------------------------------------------------------------------
1878 Rejection code error message which would be eventually populated in
1879 pa_expenditure_items_all table.
1880 --------------------------------------------------------------------------------------------*/
1881 NC := 1209;
1882
1883 IF ( insert_error_message ) THEN
1884 IF (stage = 200) THEN
1885 reason( j ) := 'NO_IND_RATE_SCH_REVISION';
1886 ELSIF (stage = 300) THEN
1887 reason( j ) := 'NO_COST_PLUS_STRUCTURE';
1888 ELSIF (stage = 500) THEN
1889 reason( j ) := 'NO_ORGANIZATION';
1893 reason( j ) := 'NO_ACTIVE_COMPILED_SET';
1890 ELSIF (stage = 600) THEN
1891 reason( j ) := 'NO_COMPILED_MULTIPLIER';/* Bug 5884742`*/
1892 ELSIF (stage = 700) THEN
1894 ELSE
1895 reason( j ) := 'GET_INDIRECT_COST_FAIL';
1896 END IF;
1897 END IF;
1898
1899 j := j + 1;
1900
1901 NC := 1210;
1902
1903 END LOOP;
1904
1905 NC := 12100;
1906
1907 EXCEPTION
1908
1909 WHEN system_error THEN
1910 message_code:= 'ORA error encountered while processing pa_client_extn.calc_bill_amount';
1911
1912 WHEN OTHERS THEN
1913 NC := -999;
1914 message_code:= sqlerrm( sqlcode );
1915
1916 END forecast_rev_billamount;
1917
1918 ---******************* PROCEDURE Get_WriteOff_Rep_Revenue_Amt *******************---
1919 /* This Procedure is added by Manish Gupta on 05/08/03 for MRC Schema Changes */
1920
1921 PROCEDURE Get_WriteOff_Rep_Revenue_Amt (p_project_id IN NUMBER DEFAULT NULL,
1922 p_task_id IN NUMBER DEFAULT NULL,
1923 p_agreement_id IN NUMBER DEFAULT NULL,
1924 p_funding_flag IN VARCHAR2 DEFAULT NULL,
1925 px_writeoff_amount IN OUT NOCOPY NUMBER, /* It is funding currency MCB */ --File.Sql.39 bug 4440895
1926 x_rep_projfunc_writeoff_amt OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1927 ) IS
1928 /* ------------------------------------------------------------------------
1929 || Procedure : Get_WriteOff_Rep_Revenue_Amt ||
1930 || Description : To get Revenue WriteOff Amount for Reporting Currency ||
1931 || Parameters : Project ID (IN) ||
1932 || Task ID (IN) ||
1933 || Agreement ID (IN) ||
1934 || Funding Flag (IN) ||
1935 || WriteOff Amount (IN) (OUT) ||
1936 || Rep Projfunc Writeoff Amount (OUT) ||
1937 --------------------------------------------------------------------------*/
1938
1939 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1940 l_writeoff_amount NUMBER := px_writeoff_amount;
1941
1942 BEGIN
1943
1944 IF g1_debug_mode = 'Y' THEN
1945 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.Get_WriteOff_Rep_Revenue_Amt :');
1946 END IF;
1947
1948 BEGIN
1949 IF p_task_id IS NOT NULL THEN
1950
1951 IF p_agreement_id IS NOT NULL THEN
1952
1953 /*
1954 | If the search has project id, task id, agreement id
1955 | Driving Path Events -> Event Types -> ERDL -> DR */
1956
1957 Null;
1958
1959 ELSE
1960
1961 /*
1962 | If the search has project id, task id, agreement id is null
1963 | Driving Path Events -> Event Types */
1964
1965 /* MCB related changes */
1966 NULL;
1967 END IF;
1968
1969 ELSIF p_project_id IS NOT NULL THEN
1970
1971 IF p_agreement_id IS NOT NULL THEN
1972
1973 /*
1974 | If Project id is not null, agreement id is not null
1975 | Driving Path Events -> Event Types -> ERDL -> DR */
1976 NULL;
1977 ELSE
1978
1979 /*
1980 | If Project id is not null, agreement id is null
1981 | Driving Path Events -> Event Types */
1982
1983 /* MCB related changes */
1984 Null;
1985
1986 END IF;
1987
1988 ELSIF p_agreement_id IS NOT NULL THEN
1989
1990 /*
1991 | If Agreement id is not null, agreement id (might be null)
1992 | Driving path DR -> ERDL -> Events -> Event Types */
1993
1994 NULL;
1995 ELSE
1996 /*
1997 | If Project ID is null, Task ID is null, Agreement ID is null
1998 | Driving path Event Type -> Events -> ERDL -> DR */
1999 NULL;
2000
2001 END IF;
2002
2003 IF g1_debug_mode = 'Y' THEN
2004 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing.Get_WriteOff_Rep_Revenue_Amt :');
2005 END IF;
2006
2007 EXCEPTION
2008 WHEN OTHERS THEN
2009 px_writeoff_amount := l_writeoff_amount; -- NOCOPY
2010 x_rep_projfunc_writeoff_amt := null; -- NOCOPY
2011
2012 RAISE;
2013 END;
2014
2015 END Get_WriteOff_Rep_Revenue_Amt;
2016
2017 /* End of Addition for MRC Schema Changes */
2018 PROCEDURE Call_Calc_Non_Labor_Bill_Amt
2019 (
2020 x_transaction_type in varchar2 default 'ACTUAL',
2021 x_expenditure_item_id IN NUMBER,
2022 x_sys_linkage_function IN VARCHAR2,
2023 x_amount IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2024 x_expenditure_type IN VARCHAR2,
2025 x_non_labor_resource IN VARCHAR2,
2026 x_non_labor_res_org IN NUMBER,
2027 x_bill_rate_flag IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2028 x_status IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2032 x_rate_source_id OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
2029 x_bill_trans_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2030 x_bill_txn_bill_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2031 x_markup_percentage OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2033 IS
2034
2035 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2036
2037 l_amount NUMBER := x_amount;
2038 l_bill_rate_flag VARCHAR2(1) := x_bill_rate_flag;
2039
2040 BEGIN
2041 /* Change the call and aded new paras in this procs. for MCB2 */
2042 IF g1_debug_mode = 'Y' THEN
2043 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing.Call_Calc_Non_Labor_Bill_Amt :');
2044 END IF;
2045 PA_NON_LABOR_BILL_CLT_EXTN.Calc_Bill_Amount(
2046 x_transaction_type => x_transaction_type ,
2047 x_expenditure_item_id => x_expenditure_item_id ,
2048 x_sys_linkage_function => x_sys_linkage_function ,
2049 x_amount => x_amount ,
2050 x_expenditure_type => x_expenditure_type ,
2051 x_non_labor_resource => x_non_labor_resource ,
2052 x_non_labor_res_org => x_non_labor_res_org ,
2053 x_bill_rate_flag => x_bill_rate_flag ,
2054 x_status => x_status ,
2055 x_bill_trans_currency_code => x_bill_trans_currency_code,
2056 x_bill_txn_bill_rate => x_bill_txn_bill_rate ,
2057 x_markup_percentage => x_markup_percentage ,
2058 x_rate_source_id => x_rate_source_id
2059 );
2060
2061 IF g1_debug_mode = 'Y' THEN
2062 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing.Call_Calc_Non_Labor_Bill_Amt :');
2063 END IF;
2064 EXCEPTION WHEN OTHERS THEN
2065 -- DBMS_OUTPUT.PUT(SQLERRM);
2066 x_amount := l_amount; --NOCOPY
2067 x_bill_rate_flag := l_bill_rate_flag; --NOCOPY
2068 x_bill_trans_currency_code := NULL; --NOCOPY
2069 x_bill_txn_bill_rate := NUll; --NOCOPY
2070 x_markup_percentage := NULL; --NOCOPY
2071 x_rate_source_id := NULL; --NOCOPY
2072 RAISE;
2073
2074 END Call_Calc_Non_Labor_Bill_Amt;
2075
2076
2077 FUNCTION Validate_Task_Customer(
2078 p_project_id IN NUMBER
2079 , p_customer_id IN NUMBER
2080 , p_task_id IN NUMBER
2081 ) RETURN VARCHAR2 as
2082
2083 l_exist_flag varchar2(1);
2084
2085 Begin
2086
2087 /* Check whether the customer is associated with any of the top tasks */
2088
2089 Select 'Y'
2090 Into l_exist_flag
2091 From dual
2092 Where exists ( select null
2093 from pa_tasks
2094 where project_id = p_project_id
2095 and customer_id = p_customer_id
2096 and task_id = top_task_id
2097 and decode(p_task_id
2098 , null, top_task_id
2099 , p_task_id) = top_task_id
2100 );
2101
2102 Return l_exist_flag;
2103
2104 Exception When others Then
2105 Return 'N';
2106 End Validate_Task_Customer;
2107
2108 END pa_billing;