[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;
500:
501: BEGIN
502:
503: IF g1_debug_mode = 'Y' THEN
504: PA_MCB_INVOICE_PKG.log_message('Enetering pa_bill_pct.PotEventAmount ');
505: END IF;
506: /** Sum of all event amounts other than events created by percent complete **/
507: /*The following sql has been commented for MCB2 */
508: /* SELECT sum(decode(et.event_type_classification,
585: END IF;
586:
587: l_projfunc_amount_sum := NVL(l_projfunc_amount_sum,0) + NVL(l_converted_amount,0);
588: IF g1_debug_mode = 'Y' THEN
589: 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);
590: END IF;
591: End Loop;
592: Close pctfunc_rev_inv_amt;
593:
599: X2_invoice_amount := l_projfunc_amount_sum;
600: END IF;
601:
602: IF g1_debug_mode = 'Y' THEN
603: PA_MCB_INVOICE_PKG.log_message('Exiting from pa_bill_pct.PotEventAmount ');
604: END IF;
605: /* Added the below for NOCOPY mandate */
606: EXCEPTION WHEN OTHERS THEN
607: X2_revenue_amount := 0;
676:
677: BEGIN
678:
679: IF g1_debug_mode = 'Y' THEN
680: PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'Entering pa_bill_pct.RevenuAmount ');
681: END IF;
682: -- Percent Complete Revenue that has been accrued.
683: /* change this column from amount to projfunc_revenue_amount for MCB2 */
684: SELECT sum(nvl(dri.projfunc_revenue_amount,0))
702: and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt')
703: OR dri.revenue_source like 'Expenditure%');
704:
705: IF g1_debug_mode = 'Y' THEN
706: PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'The accrued_pctrev inside pa_bill_pct.RevenuAmount: '||to_char(accrued_pctrev));
707: END IF;
708: -- Percent Complete revenue that has not been created as events
709: -- but not accrued yet.
710: -- This could be due to unauthorized task or an erroring request.
784: Close pctfunc_revenue;
785: pending_pctrev := l_projfunc_rev_amount_sum;
786:
787: IF g1_debug_mode = 'Y' THEN
788: PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'The pending_pctrev : inside pa_bill_pct.RevenuAmount '||to_char(pending_pctrev ));
789: END IF;
790: X2_revenue_amount := nvl(accrued_pctrev,0) + nvl(pending_pctrev,0);
791:
792: IF g1_debug_mode = 'Y' THEN
789: END IF;
790: X2_revenue_amount := nvl(accrued_pctrev,0) + nvl(pending_pctrev,0);
791:
792: IF g1_debug_mode = 'Y' THEN
793: PA_MCB_INVOICE_PKG.log_message('RevenueAmount: ' || 'Exiting pa_bill_pct.RevenuAmount ');
794: END IF;
795:
796: /* Added the below for NOCOPY mandate */
797: EXCEPTION WHEN OTHERS THEN
870: and nvl(pdii.task_id,0) = nvl(e.task_id,0));
871:
872: BEGIN
873: IF g1_debug_mode = 'Y' THEN
874: PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.InvoiceAmount ');
875: END IF;
876:
877: -- Percent Complete Invoice Amount that has been created as an event,
878: -- but not billed yet.
956: pending_ccinv := l_projfunc_bill_amount_sum;
957:
958:
959: IF g1_debug_mode = 'Y' THEN
960: PA_MCB_INVOICE_PKG.log_message('The pending_ccinv inside pa_bill_pct.InvoiceAmount: '||to_char(pending_ccinv));
961: END IF;
962: IF (X2_task_id IS NULL) THEN
963:
964: -- Percent Complete Invoice Amount that has been billed, or originates from
985: and erdl.draft_invoice_num = dii.draft_invoice_num
986: and erdl.draft_invoice_item_line_num = dii.line_num));
987:
988: IF g1_debug_mode = 'Y' THEN
989: PA_MCB_INVOICE_PKG.log_message('The billed_ccinv for task id is null inside pa_bill_pct.InvoiceAmount: '||to_char(billed_ccinv));
990: END IF;
991: X2_invoice_amount := nvl(pending_ccinv,0) + nvl(billed_ccinv,0);
992:
993: ELSE
1005: AND t.top_task_id = X2_task_id
1006: AND rdl.draft_invoice_num IS NOT NULL;
1007:
1008: IF g1_debug_mode = 'Y' THEN
1009: 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));
1010: END IF;
1011: /* Change this column from amount to projfunc_bill_amount for MCB2 */
1012: SELECT sum(nvl(pdii.projfunc_bill_amount,0))
1013: INTO task_billed_ev_ccinv
1025: and pdii.event_task_id = e.task_id
1026: and be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt');
1027:
1028: IF g1_debug_mode = 'Y' THEN
1029: 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));
1030: END IF;
1031: X2_invoice_amount := nvl(task_billed_ccinv,0) + nvl(task_billed_ev_ccinv,0) + nvl(pending_ccinv,0);
1032:
1033: END IF;
1032:
1033: END IF;
1034:
1035: IF g1_debug_mode = 'Y' THEN
1036: PA_MCB_INVOICE_PKG.log_message('Exiting pa_bill_pct.InvoiceAmount: ');
1037: END IF;
1038: /* Added the below for NOCOPY mandate */
1039: EXCEPTION WHEN OTHERS THEN
1040: X2_invoice_amount := NULL;
1095: BEGIN
1096:
1097: /** get the most recent percent complete before the accru thru date **/
1098:
1099: -- PA_MCB_INVOICE_PKG.log_message('Entering pa_bill_pct.GetPercentComplete: ');
1100: OPEN pct;
1101:
1102: FETCH pct INTO percent_comp;
1103:
1100: OPEN pct;
1101:
1102: FETCH pct INTO percent_comp;
1103:
1104: -- PA_MCB_INVOICE_PKG.log_message('Inside the loop of pa_bill_pct.GetPercentComplete: ');
1105: CLOSE pct;
1106:
1107: --PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.GetPercentComplete: ');
1108: RETURN percent_comp;
1103:
1104: -- PA_MCB_INVOICE_PKG.log_message('Inside the loop of pa_bill_pct.GetPercentComplete: ');
1105: CLOSE pct;
1106:
1107: --PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.GetPercentComplete: ');
1108: RETURN percent_comp;
1109:
1110: EXCEPTION
1111: WHEN OTHERS THEN
1157: /* till here */
1158:
1159: BEGIN
1160: IF g1_debug_mode = 'Y' THEN
1161: PA_MCB_INVOICE_PKG.log_message(' Entering pa_bill_pct.get_rev_budget_amount: ');
1162: END IF;
1163: X_status := 0;
1164: X_error_message := NULL;
1165: BEGIN
1176: FROM pa_billing_extensions
1177: WHERE billing_extension_id=pa_billing.GetBillingExtensionId;
1178:
1179: IF g1_debug_mode = 'Y' THEN
1180: 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);
1181: END IF;
1182: END IF;
1183:
1184:
1242: AND budget_status_code = 'B'
1243: AND current_flag = 'Y';
1244:
1245: IF g1_debug_mode = 'Y' THEN
1246: 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);
1247: END IF;
1248: EXCEPTION
1249: WHEN NO_DATA_FOUND THEN
1250: raise rev_budget_not_baselined;
1270:
1271: X2_revenue_amount := pa_currency.round_currency_amt(l_revenue_total);
1272:
1273: IF g1_debug_mode = 'Y' THEN
1274: PA_MCB_INVOICE_PKG.log_message(' getting l_revenue_total inside pa_bill_pct.get_rev_budget_amount: '||to_char(l_revenue_total));
1275: END IF;
1276: X_rev_budget_type_code := l_rev_budget_type_code;
1277: X_rev_plan_type_id := l_rev_plan_type_id; /* Added for Fin plan impact */
1278:
1278:
1279: -- If any exception then raise it to the calling pl/sql block
1280: --
1281: IF g1_debug_mode = 'Y' THEN
1282: PA_MCB_INVOICE_PKG.log_message(' Exiting pa_bill_pct.get_rev_budget_amount: ');
1283: END IF;
1284: EXCEPTION
1285: WHEN invalid_rev_budget_code THEN
1286: status := pa_billing_values.get_message('INVALID_REV_BUDGET_TYPE');