[Home] [Help]
82: /* till here */
83:
84: BEGIN
85: IF g1_debug_mode = 'Y' THEN
86: PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.calc_pct_comp_amt :');
87: END IF;
88:
89:
90: /** gets project currency code This is commented because now PFC and Project currency can be diffrent for MCB2 **/
119:
120: l_currency_code := l_projfunc_currency_code;
121:
122: IF g1_debug_mode = 'Y' THEN
123: PA_MCB_INVOICE_PKG.log_message('pa_bill_pct.calc_pct_comp_amt currency :'||l_currency_code);
124: END IF;
125: /** gets the cost and revenue budget amounts **/
126:
127: IF g1_debug_mode = 'Y' THEN
124: END IF;
125: /** gets the cost and revenue budget amounts **/
126:
127: IF g1_debug_mode = 'Y' THEN
128: PA_MCB_INVOICE_PKG.log_message('Before pa_billing_extn_params_v select pa_bill_pct.calc_pct_comp_amt :');
129: END IF;
130: /* Added for bug 2649456.Not handling exception intentionaly because if it is coming,
131: it will be data issue */
132: BEGIN
135: FROM pa_billing_extn_params_v;
136: EXCEPTION
137: WHEN OTHERS THEN
138: IF g1_debug_mode = 'Y' THEN
139: PA_MCB_INVOICE_PKG.log_message('Error from pa_billing_extn_params_v pa_bill_pct.calc_pct_comp_amt :'||SQLERRM);
140: END IF;
141: RAISE;
142: END;
143: /* till here */
142: END;
143: /* till here */
144:
145: IF g1_debug_mode = 'Y' THEN
146: PA_MCB_INVOICE_PKG.log_message('pa_bill_pct.calc_pct_comp_amt cost_plan_type_id :'||l_cost_plan_type_id);
147: PA_MCB_INVOICE_PKG.log_message('pa_bill_pct.calc_pct_comp_amt rev_plan_type_id :' ||l_rev_plan_type_id);
148: END IF;
149:
150: l_status := 0;
143: /* till here */
144:
145: IF g1_debug_mode = 'Y' THEN
146: PA_MCB_INVOICE_PKG.log_message('pa_bill_pct.calc_pct_comp_amt cost_plan_type_id :'||l_cost_plan_type_id);
147: PA_MCB_INVOICE_PKG.log_message('pa_bill_pct.calc_pct_comp_amt rev_plan_type_id :' ||l_rev_plan_type_id);
148: END IF;
149:
150: l_status := 0;
151: l_error_message := NULL;
157: X_rev_plan_type_id => l_rev_plan_type_id,
158: X_error_message => l_error_message,
159: X_status => l_status);
160: IF g1_debug_mode = 'Y' THEN
161: PA_MCB_INVOICE_PKG.log_message('After call of get_rev_budget_amount inside pa_bill_pct.calc_pct_comp_amt :'||to_char(budget_revenue));
162: END IF;
163:
164: -- If get budget amount return an error its fatal.
165:
169:
170: /** Get the event amounts generated by events other than Percent complete **/
171:
172: IF g1_debug_mode = 'Y' THEN
173: PA_MCB_INVOICE_PKG.log_message('Before calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt :');
174: END IF;
175: PA_BILL_PCT.PotEventAmount(
176: X2_project_id => X_project_id,
177: X2_task_id => X_top_task_id,
179: X2_revenue_amount => event_revenue,
180: X2_invoice_amount => event_invoice);
181:
182: IF g1_debug_mode = 'Y' THEN
183: PA_MCB_INVOICE_PKG.log_message('After calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt event revenue :'||to_char(event_revenue));
184: PA_MCB_INVOICE_PKG.log_message('After calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt event invoice :'||to_char(event_invoice));
185: END IF;
186: /** Get the amount left based on the hard limit set for the projects
187: customers **/
180: X2_invoice_amount => event_invoice);
181:
182: IF g1_debug_mode = 'Y' THEN
183: PA_MCB_INVOICE_PKG.log_message('After calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt event revenue :'||to_char(event_revenue));
184: PA_MCB_INVOICE_PKG.log_message('After calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt event invoice :'||to_char(event_invoice));
185: END IF;
186: /** Get the amount left based on the hard limit set for the projects
187: customers **/
188:
186: /** Get the amount left based on the hard limit set for the projects
187: customers **/
188:
189: IF g1_debug_mode = 'Y' THEN
190: PA_MCB_INVOICE_PKG.log_message('before calling pa_billing_amount.LowestAmountLeft inside pa_bill_pct.calc_pct_comp_amt :');
191: END IF;
192: Amount_Left := pa_billing_amount.LowestAmountLeft(
193: X_project_id,
194: X_top_task_id,
194: X_top_task_id,
195: X_calling_process);
196:
197: IF g1_debug_mode = 'Y' THEN
198: PA_MCB_INVOICE_PKG.log_message('After calling PotEventAmount inside pa_bill_pct.calc_pct_comp_amt Amount_Left :'||to_char(Amount_Left));
199: END IF;
200: /** Get the Percent complete for the project / top task **/
201:
202: IF g1_debug_mode = 'Y' THEN
199: END IF;
200: /** Get the Percent complete for the project / top task **/
201:
202: IF g1_debug_mode = 'Y' THEN
203: PA_MCB_INVOICE_PKG.log_message('before calling GetPercentComplete inside pa_bill_pct.calc_pct_comp_amt Percent_Complete :');
204: END IF;
205: Percent_Complete := PA_BILL_PCT.GetPercentComplete(
206: X_project_id ,
207: X_top_task_id,
209: );
210:
211:
212: IF g1_debug_mode = 'Y' THEN
213: PA_MCB_INVOICE_PKG.log_message('After calling GetPercentComplete inside pa_bill_pct.calc_pct_comp_amt Percent_Complete :'||to_char(Percent_Complete));
214: END IF;
215: IF Percent_Complete > 100 THEN
216: Percent_Complete := 100;
217: END IF;
233:
234: IF (X_calling_process = 'Revenue') THEN
235:
236: IF g1_debug_mode = 'Y' THEN
237: PA_MCB_INVOICE_PKG.log_message('before calling RevenueAmount inside pa_bill_pct.calc_pct_comp_amt RevenueAmount :');
238: END IF;
239: PA_BILL_PCT.RevenueAmount(
240: X2_project_id => x_project_id,
241: X2_task_id => X_top_task_id,
241: X2_task_id => X_top_task_id,
242: X2_revenue_amount => revenue_amount);
243:
244: IF g1_debug_mode = 'Y' THEN
245: PA_MCB_INVOICE_PKG.log_message('After calling RevenueAmount inside pa_bill_pct.calc_pct_comp_amt RevenueAmount :'||to_char(revenue_amount));
246: END IF;
247: -- IF (budget_cost <> 0) THEN
248: -- Revenue is the Least of
249: -- revenue = percent_complete * (budget_revenue - event_revenue)
268: /* Addded for bug 4719700 END */
269:
270:
271: IF g1_debug_mode = 'Y' THEN
272: PA_MCB_INVOICE_PKG.log_message('Calculating Revenue inside pa_bill_pct.calc_pct_comp_amt (Least) Revenue :'||to_char(Revenue));
273: END IF;
274: /* Changed the length of the format mask for amount_left column from 15 to 22
275: to fix the bug 2124494 for MCB2 */
276: /* Changed the length of the format mask for all column from 15 to 22
291:
292: /** public api to insert event **/
293:
294: IF g1_debug_mode = 'Y' THEN
295: PA_MCB_INVOICE_PKG.log_message('rev part inside pa_bill_pct.calc_pct_comp_amt event desc :'||Event_Description);
296: PA_MCB_INVOICE_PKG.log_message('rev part Before calling insert_event inside pa_bill_pct.calc_pct_comp_amt Revenue :');
297: END IF;
298: pa_billing_pub.insert_event (
299: X_rev_amt => Revenue,
292: /** public api to insert event **/
293:
294: IF g1_debug_mode = 'Y' THEN
295: PA_MCB_INVOICE_PKG.log_message('rev part inside pa_bill_pct.calc_pct_comp_amt event desc :'||Event_Description);
296: PA_MCB_INVOICE_PKG.log_message('rev part Before calling insert_event inside pa_bill_pct.calc_pct_comp_amt Revenue :');
297: END IF;
298: pa_billing_pub.insert_event (
299: X_rev_amt => Revenue,
300: X_bill_amt => 0,
312: X_status => l_status
313: );
314:
315: IF g1_debug_mode = 'Y' THEN
316: PA_MCB_INVOICE_PKG.log_message('Rev part After calling insert_event inside pa_bill_pct.calc_pct_comp_amt Revenue -> status :'||l_status);
317: END IF;
318: IF l_status <> 0 THEN
319: raise pct_error;
320: END IF;
322: -- END IF;
323:
324: ELSE
325: IF g1_debug_mode = 'Y' THEN
326: PA_MCB_INVOICE_PKG.log_message('before calling InvoiceAmount inside pa_bill_pct.calc_pct_comp_amt InvoiceAmount :');
327: END IF;
328: PA_BILL_PCT.InvoiceAmount(
329: X2_project_id => X_project_id,
330: X2_task_id => X_top_task_id,
330: X2_task_id => X_top_task_id,
331: X2_invoice_amount => invoice_amount);
332:
333: IF g1_debug_mode = 'Y' THEN
334: PA_MCB_INVOICE_PKG.log_message('After calling InvoiceAmount inside pa_bill_pct.calc_pct_comp_amt InvoiceAmount :'||to_char(invoice_amount));
335: END IF;
336: -- IF (budget_cost <> 0) THEN
337: /*Invoice := Least( (( nvl(Percent_Complete,0) * 0.01)
338: * greatest( (nvl(budget_revenue,0)
351: /* Changes for 4719700 -End */
352:
353:
354: IF g1_debug_mode = 'Y' THEN
355: PA_MCB_INVOICE_PKG.log_message('calculating Invoice Amount ( with least) inside pa_bill_pct.calc_pct_comp_amt Invoice :'||to_char(Invoice));
356: END IF;
357: /* Changed the length of the format mask for amount_left column from 15 to 22
358: to fix the bug 2124494 for MCB2 */
359: /* Changed the length of the format mask for all column from 15 to 22
372: to_char(nvl(invoice_amount,0),fnd_currency.get_format_mask(l_currency_code,22))
373: || ' ))';
374:
375: IF g1_debug_mode = 'Y' THEN
376: PA_MCB_INVOICE_PKG.log_message('Inv part inside pa_bill_pct.calc_pct_comp_amt event desc :'||Event_Description);
377: PA_MCB_INVOICE_PKG.log_message('Inv part Before insert event inside pa_bill_pct.calc_pct_comp_amt Invoice :');
378: END IF;
379: pa_billing_pub.insert_event (
380: X_rev_amt => 0,
373: || ' ))';
374:
375: IF g1_debug_mode = 'Y' THEN
376: PA_MCB_INVOICE_PKG.log_message('Inv part inside pa_bill_pct.calc_pct_comp_amt event desc :'||Event_Description);
377: PA_MCB_INVOICE_PKG.log_message('Inv part Before insert event inside pa_bill_pct.calc_pct_comp_amt Invoice :');
378: END IF;
379: pa_billing_pub.insert_event (
380: X_rev_amt => 0,
381: X_bill_amt => Invoice,
393: X_status => l_status
394: );
395:
396: IF g1_debug_mode = 'Y' THEN
397: PA_MCB_INVOICE_PKG.log_message('Inv partinsert event inside pa_bill_pct.calc_pct_comp_amt Invoice -> status :'||l_status);
398: END IF;
399: IF l_status <> 0 THEN
400: raise pct_error;
401: END IF;
402: -- END IF;
403: END IF;
404:
405: IF g1_debug_mode = 'Y' THEN
406: PA_MCB_INVOICE_PKG.log_message('Exiting pa_bill_pct.calc_pct_comp_amt :');
407: END IF;
408: EXCEPTION
409: WHEN pct_error THEN
410: IF g1_debug_mode = 'Y' THEN
407: END IF;
408: EXCEPTION
409: WHEN pct_error THEN
410: IF g1_debug_mode = 'Y' THEN
411: PA_MCB_INVOICE_PKG.log_message('Inside pct_error inside pa_bill_pct.calc_pct_comp_amt');
412: END IF;
413: NULL;
414: -- Modified so that this exception is reported but doesnot stop revenue
415: -- processing
416: -- RAISE_APPLICATION_ERROR(-20101,l_error_message);
417: WHEN OTHERS THEN
418: -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
419: IF g1_debug_mode = 'Y' THEN
420: PA_MCB_INVOICE_PKG.log_message('Inside when other of pa_bill_pct.calc_pct_comp_amt');
421: END IF;
422: RAISE;
423:
424: END calc_pct_comp_amt;
502:
503: BEGIN
504:
505: IF g1_debug_mode = 'Y' THEN
506: PA_MCB_INVOICE_PKG.log_message('Enetering pa_bill_pct.PotEventAmount ');
507: END IF;
508: /** Sum of all event amounts other than events created by percent complete **/
509: /*The following sql has been commented for MCB2 */
510: /* SELECT sum(decode(et.event_type_classification,
587: END IF;
588:
589: l_projfunc_amount_sum := NVL(l_projfunc_amount_sum,0) + NVL(l_converted_amount,0);
590: IF g1_debug_mode = 'Y' THEN
591: PA_MCB_INVOICE_PKG.log_message('The calculated amount after convert amount inside pa_bill_pct.PotEventAmount : '||to_char(l_projfunc_amount_sum)||'calling process '||l_calling_process);
592: END IF;
593: End Loop;
594: Close pctfunc_rev_inv_amt;
595:
601: X2_invoice_amount := l_projfunc_amount_sum;
602: END IF;
603:
604: IF g1_debug_mode = 'Y' THEN
605: PA_MCB_INVOICE_PKG.log_message('Exiting from pa_bill_pct.PotEventAmount ');
606: END IF;
607: /* Added the below for NOCOPY mandate */
608: EXCEPTION WHEN OTHERS THEN
609: X2_revenue_amount := 0;
678:
679: BEGIN
680:
681: IF g1_debug_mode = 'Y' THEN
682: PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'Entering pa_bill_pct.RevenuAmount ');
683: END IF;
684: -- Percent Complete Revenue that has been accrued.
685: /* change this column from amount to projfunc_revenue_amount for MCB2 */
686: SELECT sum(nvl(dri.projfunc_revenue_amount,0))
704: and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt')
705: OR dri.revenue_source like 'Expenditure%');
706:
707: IF g1_debug_mode = 'Y' THEN
708: PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'The accrued_pctrev inside pa_bill_pct.RevenuAmount: '||to_char(accrued_pctrev));
709: END IF;
710: -- Percent Complete revenue that has not been created as events
711: -- but not accrued yet.
712: -- This could be due to unauthorized task or an erroring request.
786: Close pctfunc_revenue;
787: pending_pctrev := l_projfunc_rev_amount_sum;
788:
789: IF g1_debug_mode = 'Y' THEN
790: PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'The pending_pctrev : inside pa_bill_pct.RevenuAmount '||to_char(pending_pctrev ));
791: END IF;
792: X2_revenue_amount := nvl(accrued_pctrev,0) + nvl(pending_pctrev,0);
793:
794: IF g1_debug_mode = 'Y' THEN
791: END IF;
792: X2_revenue_amount := nvl(accrued_pctrev,0) + nvl(pending_pctrev,0);
793:
794: IF g1_debug_mode = 'Y' THEN
795: PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'Exiting pa_bill_pct.RevenuAmount ');
796: END IF;
797:
798: /* Added the below for NOCOPY mandate */
799: EXCEPTION WHEN OTHERS THEN
872: and nvl(pdii.task_id,0) = nvl(e.task_id,0));
873:
874: BEGIN
875: IF g1_debug_mode = 'Y' THEN
876: PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.InvoiceAmount ');
877: END IF;
878:
879: -- Percent Complete Invoice Amount that has been created as an event,
880: -- but not billed yet.
958: pending_ccinv := l_projfunc_bill_amount_sum;
959:
960:
961: IF g1_debug_mode = 'Y' THEN
962: PA_MCB_INVOICE_PKG.log_message('The pending_ccinv inside pa_bill_pct.InvoiceAmount: '||to_char(pending_ccinv));
963: END IF;
964: IF (X2_task_id IS NULL) THEN
965:
966: -- Percent Complete Invoice Amount that has been billed, or originates from
987: and erdl.draft_invoice_num = dii.draft_invoice_num
988: and erdl.draft_invoice_item_line_num = dii.line_num));
989:
990: IF g1_debug_mode = 'Y' THEN
991: PA_MCB_INVOICE_PKG.log_message('The billed_ccinv for task id is null inside pa_bill_pct.InvoiceAmount: '||to_char(billed_ccinv));
992: END IF;
993: X2_invoice_amount := nvl(pending_ccinv,0) + nvl(billed_ccinv,0);
994:
995: ELSE
1007: AND t.top_task_id = X2_task_id
1008: AND rdl.draft_invoice_num IS NOT NULL;
1009:
1010: IF g1_debug_mode = 'Y' THEN
1011: PA_MCB_INVOICE_PKG.log_message('The task_billed_ccinv for task id is not null inside pa_bill_pct.InvoiceAmount: '||to_char(task_billed_ccinv));
1012: END IF;
1013: /* Change this column from amount to projfunc_bill_amount for MCB2 */
1014: SELECT sum(nvl(pdii.projfunc_bill_amount,0))
1015: INTO task_billed_ev_ccinv
1027: and pdii.event_task_id = e.task_id
1028: and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt');
1029:
1030: IF g1_debug_mode = 'Y' THEN
1031: PA_MCB_INVOICE_PKG.log_message('The task_billed_ev_ccinv for task id is not null inside pa_bill_pct.InvoiceAmount: '||to_char(task_billed_ev_ccinv));
1032: END IF;
1033: X2_invoice_amount := nvl(task_billed_ccinv,0) + nvl(task_billed_ev_ccinv,0) + nvl(pending_ccinv,0);
1034:
1035: END IF;
1034:
1035: END IF;
1036:
1037: IF g1_debug_mode = 'Y' THEN
1038: PA_MCB_INVOICE_PKG.log_message('Exiting pa_bill_pct.InvoiceAmount: ');
1039: END IF;
1040: /* Added the below for NOCOPY mandate */
1041: EXCEPTION WHEN OTHERS THEN
1042: X2_invoice_amount := NULL;
1097: BEGIN
1098:
1099: /** get the most recent percent complete before the accru thru date **/
1100:
1101: -- PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.GetPercentComplete: ');
1102: OPEN pct;
1103:
1104: FETCH pct INTO percent_comp;
1105:
1102: OPEN pct;
1103:
1104: FETCH pct INTO percent_comp;
1105:
1106: -- PA_MCB_INVOICE_PKG.log_message('Inside the loop of pa_bill_pct.GetPercentComplete: ');
1107: CLOSE pct;
1108:
1109: --PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.GetPercentComplete: ');
1110: RETURN percent_comp;
1105:
1106: -- PA_MCB_INVOICE_PKG.log_message('Inside the loop of pa_bill_pct.GetPercentComplete: ');
1107: CLOSE pct;
1108:
1109: --PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.GetPercentComplete: ');
1110: RETURN percent_comp;
1111:
1112: EXCEPTION
1113: WHEN OTHERS THEN
1159: /* till here */
1160:
1161: BEGIN
1162: IF g1_debug_mode = 'Y' THEN
1163: PA_MCB_INVOICE_PKG.log_message(' Entering pa_bill_pct.get_rev_budget_amount: ');
1164: END IF;
1165: X_status := 0;
1166: X_error_message := NULL;
1167: BEGIN
1178: FROM pa_billing_extensions
1179: WHERE billing_extension_id=pa_billing.GetBillingExtensionId;
1180:
1181: IF g1_debug_mode = 'Y' THEN
1182: PA_MCB_INVOICE_PKG.log_message(' getting l_rev_budget_type_code inside pa_bill_pct.get_rev_budget_amount: '||l_rev_budget_type_code);
1183: END IF;
1184: END IF;
1185:
1186:
1244: AND budget_status_code = 'B'
1245: AND current_flag = 'Y';
1246:
1247: IF g1_debug_mode = 'Y' THEN
1248: PA_MCB_INVOICE_PKG.log_message(' getting l_rev_budget_version_id inside pa_bill_pct.get_rev_budget_amount: '||l_rev_budget_version_id);
1249: END IF;
1250: EXCEPTION
1251: WHEN NO_DATA_FOUND THEN
1252: raise rev_budget_not_baselined;
1272:
1273: X2_revenue_amount := pa_currency.round_currency_amt(l_revenue_total);
1274:
1275: IF g1_debug_mode = 'Y' THEN
1276: PA_MCB_INVOICE_PKG.log_message(' getting l_revenue_total inside pa_bill_pct.get_rev_budget_amount: '||to_char(l_revenue_total));
1277: END IF;
1278: X_rev_budget_type_code := l_rev_budget_type_code;
1279: X_rev_plan_type_id := l_rev_plan_type_id; /* Added for Fin plan impact */
1280:
1280:
1281: -- If any exception then raise it to the calling pl/sql block
1282: --
1283: IF g1_debug_mode = 'Y' THEN
1284: PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.get_rev_budget_amount: ');
1285: END IF;
1286: EXCEPTION
1287: WHEN invalid_rev_budget_code THEN
1288: status := pa_billing_values.get_message('INVALID_REV_BUDGET_TYPE');