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