1 PACKAGE BODY PA_PLAN_REVENUE AS
2 -- $Header: PAXPLRTB.pls 120.19 2012/03/16 05:39:14 a5sharma ship $
3 l_no_cost EXCEPTION;
4 l_no_revenue EXCEPTION;
5 l_no_bill_rate EXCEPTION;
6 l_insufficeient_param EXCEPTION;
7 l_more_than_one_row_excep EXCEPTION;
8 l_no_Override_rate_cost EXCEPTION;
9 l_cost_api EXCEPTION;
10 l_bill_api EXCEPTION;
11 l_rate_based_no_quantity EXCEPTION;
12 l_invalid_currency EXCEPTION;
13 l_invalid_currency_cost EXCEPTION;
14 l_invalid_currency_bill EXCEPTION;
15 l_Get_planning_Rates_api EXCEPTION;
16 l_Get_plan_actual_Rates EXCEPTION; /* Added to handel when others in Get_plan_actual_Rates proc. */
17
18 g_success CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
19 g_error CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
20 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
21 g_module_name VARCHAR2(100) := 'pa.plsql.PA_PLAN_REVENUE';
22 g_expenditure_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
23 g_uom_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
24 g_count_init NUMBER:=0;
25
26
27 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
28 /* PRIVATE PROCEDURE : Get_Res_Class_Hierarchy_Rate to get the rates based on the Resource Hierarcy*/
29 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
30
31 PROCEDURE Get_Res_Class_Hierarchy_Rate(
32 p_res_class_rate_sch_id IN NUMBER,
33 p_item_date IN DATE ,
34 p_org_id IN NUMBER,
35 p_resource_class_code IN VARCHAR2,
36 p_res_class_org_id IN NUMBER, /*p_project_organz_id revenue and nvl (p_override_to_organz_id ,p_incurred_by_organz_id for Costing */
37 x_rate OUT NOCOPY NUMBER,
38 x_markup_percentage OUT NOCOPY NUMBER,
39 x_uom OUT NOCOPY VARCHAR2,
40 x_rate_currency_code OUT NOCOPY VARCHAR2,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2) AS
44 Cursor c_rule is
45 SELECT b.rate,b.markup_percentage,b.bill_rate_unit,b.rate_currency_code
46 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
47 WHERE sch.bill_rate_sch_id = p_res_class_rate_sch_id
48 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
49 AND sch.schedule_type = 'RESOURCE_CLASS'
50 AND trunc(p_item_date) BETWEEN trunc(b.start_date_active) AND trunc(NVL(b.end_date_active ,p_item_date))
51 AND b.res_class_organization_id = p_res_class_org_id
52 AND b.resource_class_code = p_resource_class_code;
53
54 /* Below cursor will get the rate and markup for the schedule if no rate exists for
55 the Organization and resource Class in the above cursor*/
56
57 cursor c_parent_rule (p_proj_org_version_id IN NUMBER) is
58 SELECT b.rate,b.markup_percentage,b.bill_rate_unit,b.rate_currency_code
59 From pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b,
60 ( select organization_id_parent PARENT_ORGANIZATION_ID,level parent_level
61 from per_org_structure_elements
62 where org_structure_version_id = p_proj_org_version_id
63 connect by prior organization_id_parent=organization_id_child
64 and prior org_structure_version_id = org_structure_version_id
65 start with organization_id_child=p_res_class_org_id
66 and org_structure_version_id=p_proj_org_version_id) org
67 Where sch.bill_rate_sch_id = p_res_class_rate_sch_id
68 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
69 AND sch.schedule_type = 'RESOURCE_CLASS'
70 AND trunc(p_item_date) BETWEEN trunc(b.start_date_active) AND trunc(NVL(b.end_date_active, p_item_date))
71 AND b.res_class_organization_id = org.PARENT_ORGANIZATION_ID
72 AND b.resource_class_code = p_resource_class_code
73 order by org.parent_level ;
74
75 l_rate NUMBER:=NULL;
76 l_markup_percentage NUMBER:=NULL;
77 l_rate_currency_code VARCHAR2(30) :=NULL;
78 l_uom VARCHAR2(30):=NULL;
79 l_true BOOLEAN:= FALSE;
80 l_x_return_status VARCHAR2(20):= g_success;
81 l_insufficient_param EXCEPTION;
82 l_no_rate EXCEPTION;
83 l_PROJ_ORG_STRUCT_VERSION_ID pa_implementations_all.PROJ_ORG_STRUCTURE_VERSION_ID%TYPE;
84
85 BEGIN
86 /*Checking all the mandatory Parameters */
87
88 IF g1_debug_mode = 'Y' THEN
89 pa_debug.g_err_stage := 'p_res_class_rate_sch_id:'||p_res_class_rate_sch_id||'p_res_class_org_id :'||p_res_class_org_id||'p_org_id:'||p_org_id ;
90 pa_debug.write('PA_PLAN_REVENUE.Get_Res_Class_Hierarchy_Rate: ' || g_module_name,pa_debug.g_err_stage,3);
91 END IF;
92 IF g1_debug_mode = 'Y' THEN
93 pa_debug.g_err_stage := 'p_resource_class_code:'||p_resource_class_code||'p_item_date :'||p_item_date ;
94 pa_debug.write('PA_PLAN_REVENUE.Get_Res_Class_Hierarchy_Rate: ' || g_module_name,pa_debug.g_err_stage,3);
95 END IF;
96
97 IF p_res_class_rate_sch_id IS NULL OR p_item_date IS NULL
98 OR p_resource_class_code IS NULL THEN
99 raise l_insufficient_param;
100 END IF;
101 open c_rule ;
102 fetch c_rule
103 into l_rate,l_markup_percentage,l_uom,l_rate_currency_code;
104 begin
105 select i. PROJ_ORG_STRUCTURE_VERSION_ID
106 into l_PROJ_ORG_STRUCT_VERSION_ID
107 from pa_implementations_all i
108 where NVL(i.org_id,-99) = NVL(p_org_id,-99);
109 exception
110 when no_data_found then
111 null;
112 end;
113 /* If Not found for the direct resourceOrganization Id go for Climbing the Hierarchy */
114 IF c_rule%NOTFOUND THEN
115 FOR r_parent_rule IN c_parent_rule(l_PROJ_ORG_STRUCT_VERSION_ID) LOOP
116 -- Checking if the cursor is returning more than one row then exit getting the first row only
117 IF (l_true) THEN
118 EXIT;
119 ELSE
120 l_rate:=r_parent_rule.rate;
121 l_markup_percentage := r_parent_rule.markup_percentage;
122 l_uom := r_parent_rule.bill_rate_unit;
123 l_rate_currency_code := r_parent_rule.rate_currency_code;
124 l_true := TRUE;
125 END IF;
126 END LOOP;
127
128 END IF;
129 close c_rule;
130 IF l_rate IS NULL AND l_markup_percentage IS NULL THEN
131 RAISE l_no_rate;
132 END IF;
133 x_rate :=l_rate;
134 x_markup_percentage :=l_markup_percentage ;
135 x_uom :=l_uom;
136 x_rate_currency_code :=l_rate_currency_code;
137 x_return_status :=l_x_return_status;
138
139 EXCEPTION
140 WHEN l_insufficient_param THEN
141 x_rate :=NULL;
142 x_markup_percentage :=NULL;
143 x_uom :=NULL;
144 x_rate_currency_code :=NULL;
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
146 x_msg_count := 1;
147 x_msg_data := 'PA_FCST_INSUFFICIENT_PARA';
148 WHEN l_no_rate THEN
149 x_rate :=NULL;
150 x_markup_percentage :=NULL;
151 x_uom :=NULL;
152 x_rate_currency_code :=NULL;
153 x_return_status := g_error;
154 x_msg_count := 1;
155 x_msg_data := 'PA_RES_NO_BILL_MARKUP';
156
157 WHEN OTHERS THEN
158 if c_rule%isopen then
159 close c_rule;
160 end if;
161 x_rate :=NULL;
162 x_markup_percentage :=NULL;
163 x_uom :=NULL;
164 x_rate_currency_code :=NULL;
165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
166 x_msg_count := 1;
167 x_msg_data := SUBSTR(SQLERRM,1,250);
168 RAISE;
169 END Get_Res_Class_Hierarchy_Rate;
170
171
172
173 PROCEDURE Get_exp_type_uom AS
174
175 cursor temp is
176 select expenditure_type,unit_of_measure
177 from pa_expenditure_types;
178
179 BEGIN
180 OPEN temp;
181 FETCH temp BULK COLLECT INTO g_expenditure_type_tbl,g_uom_tbl;
182 CLOSE temp;
183
184
185 g_count_init:= g_count_init+1;
186
187 IF g1_debug_mode = 'Y' THEN
188 pa_debug.g_err_stage:='Before count of g_count_init'|| g_count_init||'g_expenditure_type_tbl.COUNT'||g_expenditure_type_tbl.COUNT;
189 pa_debug.write('Get_exp_type_uom: ' || g_module_name,pa_debug.g_err_stage,2);
190 END IF;
191 END;
192
193 -- This procedure will calculate the raw revenue and bill amount from one of the 12 criterias on the basis
194
195
196
197 PROCEDURE Get_planning_Rates (
198 p_project_id IN NUMBER,
199 p_task_id IN NUMBER DEFAULT NULL,
200 p_top_task_id IN NUMBER DEFAULT NULL, /* for costing top task Id */
201 p_person_id IN NUMBER,
202 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
203 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
204 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
205 p_planning_resource_format IN VARCHAR2, /* resource format required for Costing */
206 p_use_planning_rates_flag IN VARCHAR2 DEFAULT 'N', /* Rate using Actual Rates */
207 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
208 p_uom IN VARCHAR2, /* Planning UOM */
209 p_system_linkage IN VARCHAR2,
210 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
211 p_rev_res_class_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Resource Class*/
212 p_cost_res_class_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on Resource Class*/
213 p_rev_task_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
214 p_rev_proj_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
215 --p_cost_nl_rate_sch_id IN NUMBER DEFAULT NULL,
216 p_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Job*/
217 p_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Emp*/
218 /* added for iteration2*/
219 p_plan_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on job for planning*/
220 p_plan_cost_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on job for planning*/
221 p_plan_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on emp for planning*/
222 p_plan_cost_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For cost Rate Calculations based on emp for planning*/
223 p_plan_rev_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on non labor for planning*/
224 p_plan_cost_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on non labor for planning*/
225 p_plan_burden_cost_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on burdening for planning*/
226 p_calculate_mode IN VARCHAR2 DEFAULT 'COST_REVENUE' ,/* useed for calculating either only Cost(COST),only Revenue(REVENUE) or both Cost and Revenue(COST_REVENUE) */
227 /* end here */
228 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
229 p_cost_rate_multiplier IN NUMBER DEFAULT NULL ,
230 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
231 p_quantity IN NUMBER, /* required param for People/Equipment Class */
232 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
233 p_cost_sch_type IN VARCHAR2 , /* Costing Schedule Type'COST' / 'REVENUE' / 'INVOICE' */
234 p_labor_sch_type IN VARCHAR2, /* Revenue Labor Schedule Type B/I */
235 p_non_labor_sch_type IN VARCHAR2 , /* Revenue Non_Labor Schedule Type B/I */
236 p_labor_schdl_discnt IN NUMBER DEFAULT NULL,
237 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL,
238 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL,
239 p_labor_schdl_fixed_date IN DATE DEFAULT NULL,
240 p_assignment_id IN NUMBER DEFAULT NULL, /* used as p_item_id in the internal APIs */
241 p_project_org_id IN NUMBER, /* Project Org Id */
242 p_project_type IN VARCHAR2,
243 p_expenditure_type IN VARCHAR2,
244 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
245 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
246 p_override_to_organz_id IN NUMBER, /* Override Org Id */
247 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
248 p_assignment_precedes_task IN VARCHAR2, /* Added for Asgmt overide */
249 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
250 p_task_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at Project Level */
251 p_project_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at task Level */
252 p_nlr_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
253 p_project_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at project Level */
254 p_task_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at task Level */
255 p_project_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at project Level */
256 p_task_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at task Level */
257 p_inventory_item_id IN NUMBER DEFAULT NULL, /* Passed for Inventoty Items */
258 p_BOM_resource_Id IN NUMBER DEFAULT NULL, /* Passed for BOM Resource Id */
259 P_mfc_cost_type_id IN NUMBER DEFAULT 0, /* Manufacturing cost api */
260 P_item_category_id IN NUMBER DEFAULT NULL, /* Manufacturing cost api */
261 p_mfc_cost_source IN NUMBER DEFAULT 1,
262 p_cost_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to costing internal api.*/
263 p_revenue_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to billing internal api.*/
264 p_override_burden_cost_rate IN NUMBER DEFAULT NULL, /*override burden multiplier and p_raw_cost is not null calculate x_burden_cost */
265 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
266 p_txn_currency_code IN VARCHAR2 DEFAULT NULL, /* if not null, amounts to be returned in this currency only else in x_txn_curr_code*/
267 p_raw_cost IN NUMBER, /*If p_raw_cost is only passed,return the burden multiplier, burden_cost */
268 p_burden_cost IN NUMBER DEFAULT NULL,
269 p_raw_revenue IN NUMBER DEFAULT NULL,
270 p_billability_flag IN VARCHAR2 DEFAULT 'Y', /* Added rate calculation honoring billability flag */
271 p_budget_version_id IN NUMBER DEFAULT NULL, /*Added for CBS*/
272 x_bill_rate OUT NOCOPY NUMBER,
273 x_cost_rate OUT NOCOPY NUMBER,
274 x_burden_cost_rate OUT NOCOPY NUMBER,
275 x_burden_multiplier OUT NOCOPY NUMBER,
276 x_raw_cost OUT NOCOPY NUMBER,
277 x_burden_cost OUT NOCOPY NUMBER,
278 x_raw_revenue OUT NOCOPY NUMBER,
279 x_bill_markup_percentage OUT NOCOPY NUMBER,
280 x_cost_txn_curr_code OUT NOCOPY VARCHAR2,
281 x_rev_txn_curr_code OUT NOCOPY VARCHAR2,
282 x_raw_cost_rejection_code OUT NOCOPY VARCHAR2,
283 x_burden_cost_rejection_code OUT NOCOPY VARCHAR2,
284 x_revenue_rejection_code OUT NOCOPY VARCHAR2,
285 x_cost_ind_compiled_set_id OUT NOCOPY NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_data OUT NOCOPY VARCHAR2,
288 x_msg_count OUT NOCOPY NUMBER
289 )
290 IS
291
292 l_x_return_status VARCHAR2(1):=g_success;
293 l_txn_bill_rate NUMBER:=NULL;
294 l_txn_cost_rate NUMBER:=NULL;
295 l_i_txn_bill_rate NUMBER:=NULL;
296 l_i_txn_cost_rate NUMBER:=NULL;
297 l_i_txn_burden_cost_rate NUMBER :=NUll;
298 l_i_txn_burden_multiplier NUMBER;
299 l_txn_burden_cost_rate NUMBER:=NULL;
300 l_txn_burden_multiplier NUMBER;
301 l_raw_cost NUMBER;
302 l_burden_cost NUMBER;
303 l_raw_revenue NUMBER;
304 l_txn_raw_cost NUMBER;
305 l_txn_burden_cost NUMBER;
306 l_txn_raw_revenue NUMBER;
307 l_bill_markup pa_bill_rates_all.markup_percentage%TYPE:=NULL;
308 l_txn_bill_markup pa_bill_rates_all.markup_percentage%TYPE:=NULL;
309 l_txn_cost_markup pa_bill_rates_all.markup_percentage%TYPE:=NULL;
310 l_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE:=NULL;
311 l_cost_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE:=NULL;
312 l_rev_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE:=NULL;
313 l_raw_cost_rejection_code VARCHAR2(30);
314 l_burden_cost_rejection_code VARCHAR2(30);
315 l_revenue_rejection_code VARCHAR2(30);
316 l_cost_ind_compiled_set_id NUMBER;
317 l_burd_organization_id NUMBER;
318 l_x_msg_data VARCHAR2(1000);
319 l_x_msg_count NUMBER;
320 l_schedule_type VARCHAR2(30);
321 l_burd_sch_id NUMBER;
322 l_burd_sch_rev_id NUMBER;
323 l_burd_sch_fixed_date DATE;
324 l_burd_sch_cost_base VARCHAR2(50);
325 l_burd_sch_cp_structure VARCHAR2(50);
326 l_cost_return_status VARCHAR2(1):=g_success;
327 l_cost_msg_data VARCHAR2(1000);
328 l_rev_res_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE:=NULL;
329 l_cost_res_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE:=NULL;
330 l_expenditure_org_id NUMBER;
331 l_calculate_mode VARCHAR2(30);
332
333 l_resource_class_flag pa_resource_lists_all_bg.RESOURCE_CLASS_FLAG%TYPE := 'Y'; --CBS
334 BEGIN
335
336 -- Initializing return status with success so that if some unexpected error comes
337 -- , we change its status from succes to error so that we can take necessary step to rectify the problem
338 l_x_return_status := g_success;
339 PA_DEBUG.init_err_stack( 'PA_PLAN_REVENUE.Get_planning_rates');
340 IF g1_debug_mode = 'Y' THEN
341 pa_debug.g_err_stage:='Input parameters:-> p_project_id :'||p_project_id ||'p_task_id :'||p_task_id;
342 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
343 END IF;
344
345 IF g1_debug_mode = 'Y' THEN
346 pa_debug.g_err_stage:='Input parameters:-> p_person_id :'||p_person_id||'p_job_id :'||p_job_id;
347 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
348 END IF;
349 IF g1_debug_mode = 'Y' THEN
350 pa_debug.g_err_stage:='Input parameters:-> p_resource_class :'||p_resource_class ||'p_use_planning_rates_flag :'||p_use_planning_rates_flag;
351 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
352 END IF;
353 IF g1_debug_mode = 'Y' THEN
354 pa_debug.g_err_stage:='Input parameters:-> p_rate_based_flag:'||p_rate_based_flag||'p_uom :'||p_uom;
355 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
356 END IF;
357
358 IF g1_debug_mode = 'Y' THEN
359 pa_debug.g_err_stage:='Input parameters:-> p_rev_res_class_rate_sch_id :'||p_rev_res_class_rate_sch_id ||'p_cost_res_class_rate_sch_id :'||p_cost_res_class_rate_sch_id;
360 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
361 END IF;
362
363 IF g1_debug_mode = 'Y' THEN
364 pa_debug.g_err_stage:='Input parameters:-> p_rev_task_nl_rate_sch_id :'||p_rev_task_nl_rate_sch_id||'p_rev_proj_nl_rate_sch_id :'||p_rev_proj_nl_rate_sch_id;
365 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
366 END IF;
367
368 IF g1_debug_mode = 'Y' THEN
369 pa_debug.g_err_stage:='Input parameters:-> p_rev_job_rate_sch_id :'||p_rev_job_rate_sch_id ||'p_rev_emp_rate_sch_id :'||p_rev_emp_rate_sch_id;
370 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
371 END IF;
372
373 IF g1_debug_mode = 'Y' THEN
374 pa_debug.g_err_stage:='Input parameters:-> p_plan_rev_job_rate_sch_id :'||p_plan_rev_job_rate_sch_id||'p_plan_cost_job_rate_sch_id :'||p_plan_cost_job_rate_sch_id;
375 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
376 END IF;
377
378 IF g1_debug_mode = 'Y' THEN
379 pa_debug.g_err_stage:='Input parameters:-> p_plan_rev_nlr_rate_sch_id :'||p_plan_rev_nlr_rate_sch_id ||'p_plan_cost_nlr_rate_sch_id :'||p_plan_cost_nlr_rate_sch_id;
380 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
381 END IF;
382
383 IF g1_debug_mode = 'Y' THEN
384 pa_debug.g_err_stage:='Input parameters:-> p_plan_burden_cost_sch_id :'||p_plan_burden_cost_sch_id ||'p_calculate_mode :'||p_calculate_mode;
385 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
386 END IF;
387
388 IF g1_debug_mode = 'Y' THEN
389 pa_debug.g_err_stage:='Input parameters:-> p_quantity :'||p_quantity ||'p_labor_sch_type :'||p_labor_sch_type;
390 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
391 END IF;
392
393 IF g1_debug_mode = 'Y' THEN
394 pa_debug.g_err_stage:='Input parameters:-> p_non_labor_sch_type :'||p_non_labor_sch_type ||'p_expenditure_type :'||p_expenditure_type;
395 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
396 END IF;
397
398
399 IF g1_debug_mode = 'Y' THEN
400 pa_debug.g_err_stage:='Input parameters:-> p_cost_override_rate :'||p_cost_override_rate ||'p_raw_cost :'||p_raw_cost;
401 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
402 END IF;
403
404 IF g1_debug_mode = 'Y' THEN
405 pa_debug.g_err_stage:='Input parameters:-> p_revenue_override_rate :'||p_revenue_override_rate ||'p_override_currency_code :'||p_override_currency_code;
406 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
407 END IF;
408
409 IF g1_debug_mode = 'Y' THEN
410 pa_debug.g_err_stage:='Input parameters:-> p_burden_cost :'||p_burden_cost ||'p_raw_revenue :'||p_raw_revenue ;
411 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
412 END IF;
413
414
415 IF g1_debug_mode = 'Y' THEN
416 pa_debug.g_err_stage:='Validating input parameters';
417 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
418 END IF;
419
420
421 /* Change to honour billability flag */
422 l_calculate_mode := p_calculate_mode;
423 IF p_billability_flag = 'N' THEN
424 IF l_calculate_mode='REVENUE' THEN
425 RETURN;
426 ELSE
427 l_calculate_mode := 'COST';
428 END IF;
429 END IF;
430 /* End of changes to honour billability flag */
431
432 /* If p_raw_cost ,p_burden_cost and p_raw_revenue is passed then
433 the API should pass the same value as it is without any further calculation */
434 IF p_raw_cost IS NOT NULL AND p_burden_cost IS NOT NULL AND p_raw_revenue IS NOT NULL THEN
435 IF p_override_currency_code IS NULL THEN
436 IF g1_debug_mode = 'Y' THEN
437 pa_debug.g_err_stage:='Validating Get_planning_rates :pass p_override_currency_code for Override amounts';
438 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
439 END IF;
440 RAISE l_invalid_currency ;
441 ELSE
442 l_txn_raw_cost :=p_raw_cost;
443 l_txn_burden_cost :=p_burden_cost;
444 l_txn_raw_revenue :=p_raw_revenue ;
445 IF p_quantity<>0 THEN
446 l_txn_cost_rate :=p_raw_cost/NVL(p_quantity,1);
447 l_txn_burden_cost_rate :=p_burden_cost/NVL(p_quantity,1);
448 l_txn_bill_rate :=p_raw_revenue /NVL(p_quantity,1);
449 END IF;
450 l_cost_txn_curr_code :=p_override_currency_code;
451 l_rev_txn_curr_code :=p_override_currency_code;
452 --l_txn_burden_multiplier :=null;
453 IF l_txn_raw_cost<>0 THEN
454 l_txn_burden_multiplier :=(l_txn_burden_cost/l_txn_raw_cost);
455 END IF;
456 l_raw_cost_rejection_code :=NULL;
457 l_burden_cost_rejection_code :=NULL;
458 l_revenue_rejection_code :=NULL;
459 l_cost_ind_compiled_set_id :=NULL;
460 END IF;
461
462 ELSE
463
464 /* Validating Override Currency Code to be not null if any override attribute is passed */
465 IF ((p_raw_cost IS NOT NULL OR p_raw_revenue IS NOT NULL)
466 OR (p_cost_override_rate IS NOT NULL OR p_revenue_override_rate IS NOT NULL)) THEN
467 IF p_override_currency_code IS NULL THEN
468 IF g1_debug_mode = 'Y' THEN
469 pa_debug.g_err_stage:='Validating Get_planning_rates :p_override_currency_code cannot be null,if p_raw_cost,p_raw_revenue,p_cost_override_rate,p_revenue_override_rate is passed';
470 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
471 END IF;
472 RAISE l_invalid_currency;
473 END IF;
474 END IF;
475
476 /* Check for using Actual Calculation Flow for Planning Transactions */
477 IF p_use_planning_rates_flag = 'N' THEN
478 IF g1_debug_mode = 'Y' THEN
479 pa_debug.g_err_stage:='Calling the Get_plan_actual_Rates ';
480 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
481 END IF;
482 BEGIN
483 /* Calling the internal Api to get the actual Rates , this API is one to one mapped with the main API for Actuals*/
484
485
486 Get_plan_actual_Rates (
487 p_project_id =>p_project_id ,
488 p_task_id =>p_task_id ,
489 p_top_task_id =>p_top_task_id ,
490 p_person_id =>p_person_id ,
491 p_job_id =>p_job_id ,
492 p_bill_job_grp_id =>p_bill_job_grp_id ,
493 p_resource_class =>p_resource_class ,
494 p_planning_resource_format =>p_planning_resource_format ,
495 p_rate_based_flag =>p_rate_based_flag ,
496 p_uom =>p_uom ,
497 p_system_linkage =>p_system_linkage ,
498 p_project_organz_id =>p_project_organz_id ,
499 p_rev_task_nl_rate_sch_id =>p_rev_task_nl_rate_sch_id ,
500 p_rev_proj_nl_rate_sch_id =>p_rev_proj_nl_rate_sch_id ,
501 p_rev_job_rate_sch_id =>p_rev_job_rate_sch_id ,
502 p_rev_emp_rate_sch_id =>p_rev_emp_rate_sch_id ,
503 p_calculate_mode =>l_calculate_mode ,
504 p_mcb_flag =>p_mcb_flag ,
505 p_bill_rate_multiplier =>p_bill_rate_multiplier ,
506 p_quantity =>p_quantity ,
507 p_item_date =>p_item_date ,
508 p_labor_sch_type =>p_labor_sch_type ,
509 p_labor_schdl_discnt =>p_labor_schdl_discnt ,
510 p_labor_bill_rate_org_id =>p_labor_bill_rate_org_id ,
511 p_labor_std_bill_rate_schdl =>p_labor_std_bill_rate_schdl ,
512 p_labor_schdl_fixed_date =>p_labor_schdl_fixed_date ,
513 p_cost_sch_type =>p_cost_sch_type ,
514 p_cost_rate_multiplier =>p_cost_rate_multiplier ,
515 p_assignment_id =>p_assignment_id ,
516 p_project_org_id =>p_project_org_id ,
517 p_project_type =>p_project_type ,
518 p_expenditure_type =>p_expenditure_type ,
519 p_incurred_by_organz_id =>p_incurred_by_organz_id ,
520 p_override_to_organz_id =>p_override_to_organz_id ,
521 p_expenditure_org_id =>p_expenditure_org_id ,
522 p_assignment_precedes_task =>p_assignment_precedes_task ,
523 p_planning_transaction_id =>p_planning_transaction_id ,
524 p_task_bill_rate_org_id =>p_task_bill_rate_org_id ,
525 p_project_bill_rate_org_id =>p_project_bill_rate_org_id ,
526 p_non_labor_resource =>p_non_labor_resource ,
527 p_nlr_organization_id =>p_nlr_organization_id ,
528 p_non_labor_sch_type =>p_non_labor_sch_type ,
529 p_project_sch_date =>p_project_sch_date ,
530 p_task_sch_date =>p_task_sch_date ,
531 p_project_sch_discount =>p_project_sch_discount ,
532 p_task_sch_discount =>p_task_sch_discount ,
533 p_inventory_item_id =>p_inventory_item_id ,
534 p_BOM_resource_Id =>p_BOM_resource_Id ,
535 P_mfc_cost_type_id =>P_mfc_cost_type_id ,
536 p_mfc_cost_source =>p_mfc_cost_source ,
537 P_item_category_id =>P_item_category_id ,
538 p_cost_override_rate =>p_cost_override_rate ,
539 p_revenue_override_rate =>p_revenue_override_rate ,
540 p_override_burden_cost_rate =>p_override_burden_cost_rate ,
541 p_override_currency_code =>p_override_currency_code ,
542 p_txn_currency_code =>p_txn_currency_code ,
543 p_raw_cost =>p_raw_cost ,
544 p_burden_cost =>p_burden_cost ,
545 p_raw_revenue =>p_raw_revenue ,
546 x_bill_rate =>l_txn_bill_rate ,
547 x_cost_rate =>l_txn_cost_rate ,
548 x_burden_cost_rate =>l_txn_burden_cost_rate ,
549 x_burden_multiplier =>l_txn_burden_multiplier ,
550 x_raw_cost =>l_raw_cost ,
551 x_burden_cost =>l_burden_cost ,
552 x_raw_revenue =>l_raw_revenue ,
553 x_bill_markup_percentage =>l_bill_markup ,
554 x_cost_txn_curr_code =>l_cost_txn_curr_code ,
555 x_rev_txn_curr_code =>l_rev_txn_curr_code ,
556 x_raw_cost_rejection_code =>l_raw_cost_rejection_code ,
557 x_burden_cost_rejection_code =>l_burden_cost_rejection_code ,
558 x_revenue_rejection_code =>l_revenue_rejection_code ,
559 x_cost_ind_compiled_set_id =>l_cost_ind_compiled_set_id ,
560 x_return_status =>l_x_return_status ,
561 x_msg_data =>l_x_msg_data ,
562 x_msg_count =>l_x_msg_count
563 );
564
565
566
567 IF g1_debug_mode = 'Y' THEN
568 pa_debug.g_err_stage:='Called the Get_plan_actual_Rates:l_return_status'||l_x_return_status||'x_msg_data'||l_x_msg_data||'x_cost_rej_code'||l_raw_cost_rejection_code;
569 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
570 END IF;
571 IF g1_debug_mode = 'Y' THEN
572 pa_debug.g_err_stage:='Called the Get_plan_actual_Rates:l_burd_cost_rate'||l_txn_burden_cost_rate||'l_raw_cost'||l_raw_cost||'l_cost_rate'||l_txn_cost_rate||'l_cost_curr_code'||l_cost_txn_curr_code ;
573 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
574 END IF;
575 IF g1_debug_mode = 'Y' THEN
576 pa_debug.g_err_stage:='Called the Get_plan_actual_Rates:l_txn_bill_rate'||l_txn_bill_rate||'l_bill_markup'||l_bill_markup ;
577 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
578 END IF;
579 IF g1_debug_mode = 'Y' THEN
580 pa_debug.g_err_stage:='Called the Get_plan_actual_Rates:l_txn_bill_rate'||l_txn_bill_rate||'l_raw_revenue'||l_raw_revenue ||'rev_rej_code'||l_revenue_rejection_code||'curr_code'||l_rev_txn_curr_code ;
581 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
582 END IF;
583
584 EXCEPTION
585 WHEN OTHERS THEN
586 /* this will be called since the values of l_raw_cost_rejection_code,l_burden_cost_rejection_code are assigned before any
587 exception occurs in the parent procedure definition */
588 IF l_raw_cost_rejection_code IS NOT NULL OR l_burden_cost_rejection_code IS NOT NULL THEN
589 l_x_msg_data := 'PA_COST1.Get_Plan_Actual_Cost_Rates:' || SUBSTR(SQLERRM,1,250);
590 ELSE
591 l_x_msg_data := 'pa_plan_revenue.Get_Plan_Actual_Rev_Rates :' || SUBSTR(SQLERRM,1,250);
592 END IF;
593 l_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
594 l_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
595 l_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
596 IF g1_debug_mode = 'Y' THEN
597 pa_debug.g_err_stage:=' Get_plan_actual_Rates_api is throwing When Others';
598 pa_debug.write('Get_planning_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
599 END IF;
600 RAISE l_Get_planning_Rates_api;
601 END;
602 ELSE
603
604 -- Call pa_plan_revenue.Get_plan_plan_rates (parameters);
605
606 IF g1_debug_mode = 'Y' THEN
607 pa_debug.g_err_stage:='Before Calling the Get_plan_plan_Rates:p_use_planning_rates_flag '||p_use_planning_rates_flag ;
608 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
609 END IF;
610 BEGIN
611 Get_plan_plan_Rates (
612 p_project_id =>p_project_id ,
613 p_task_id =>p_task_id ,
614 p_top_task_id =>p_top_task_id ,
615 p_person_id =>p_person_id ,
616 p_job_id =>p_job_id ,
617 p_bill_job_grp_id =>p_bill_job_grp_id ,
618 p_resource_class =>p_resource_class ,
619 p_rate_based_flag =>p_rate_based_flag ,
620 p_uom =>p_uom ,
621 p_system_linkage =>p_system_linkage ,
622 p_project_organz_id =>p_project_organz_id ,
623 p_plan_rev_job_rate_sch_id =>p_plan_rev_job_rate_sch_id ,
624 p_plan_cost_job_rate_sch_id =>p_plan_cost_job_rate_sch_id ,
625 p_plan_rev_emp_rate_sch_id =>p_plan_rev_emp_rate_sch_id ,
626 p_plan_cost_emp_rate_sch_id =>p_plan_cost_emp_rate_sch_id ,
627 p_plan_rev_nlr_rate_sch_id =>p_plan_rev_nlr_rate_sch_id ,
628 p_plan_cost_nlr_rate_sch_id =>p_plan_cost_nlr_rate_sch_id ,
629 p_plan_burden_cost_sch_id =>p_plan_burden_cost_sch_id ,
630 p_calculate_mode =>l_calculate_mode ,
631 p_mcb_flag =>p_mcb_flag ,
632 p_cost_rate_multiplier =>p_cost_rate_multiplier ,
633 p_bill_rate_multiplier =>p_bill_rate_multiplier ,
634 p_quantity =>p_quantity ,
635 p_item_date =>p_item_date ,
636 p_project_org_id =>p_project_org_id ,
637 p_project_type => p_project_type ,
638 p_expenditure_type =>p_expenditure_type ,
639 p_non_labor_resource =>p_non_labor_resource ,
640 p_incurred_by_organz_id =>p_incurred_by_organz_id ,
641 p_override_to_organz_id =>p_override_to_organz_id ,
642 p_expenditure_org_id =>p_expenditure_org_id ,
643 p_planning_transaction_id =>p_planning_transaction_id ,
644 p_nlr_organization_id =>p_nlr_organization_id ,
645 p_inventory_item_id =>p_inventory_item_id ,
646 p_BOM_resource_Id =>p_BOM_resource_Id ,
647 P_mfc_cost_type_id =>P_mfc_cost_type_id ,
648 p_mfc_cost_source =>p_mfc_cost_source ,
649 P_item_category_id =>P_item_category_id ,
650 p_cost_override_rate =>p_cost_override_rate ,
651 p_revenue_override_rate =>p_revenue_override_rate ,
652 p_override_burden_cost_rate =>p_override_burden_cost_rate ,
653 p_override_currency_code =>p_override_currency_code ,
654 p_txn_currency_code =>p_txn_currency_code ,
655 p_raw_cost =>p_raw_cost ,
656 p_burden_cost =>p_burden_cost ,
657 p_raw_revenue =>p_raw_revenue ,
658 x_bill_rate =>l_txn_bill_rate ,
659 x_cost_rate =>l_txn_cost_rate ,
660 x_burden_cost_rate =>l_txn_burden_cost_rate ,
661 x_burden_multiplier =>l_txn_burden_multiplier ,
662 x_raw_cost =>l_raw_cost ,
663 x_burden_cost =>l_burden_cost ,
664 x_raw_revenue =>l_raw_revenue ,
665 x_bill_markup_percentage =>l_bill_markup ,
666 x_cost_txn_curr_code =>l_cost_txn_curr_code ,
667 x_rev_txn_curr_code =>l_rev_txn_curr_code ,
668 x_raw_cost_rejection_code =>l_raw_cost_rejection_code ,
669 x_burden_cost_rejection_code =>l_burden_cost_rejection_code ,
670 x_revenue_rejection_code =>l_revenue_rejection_code ,
671 x_cost_ind_compiled_set_id =>l_cost_ind_compiled_set_id ,
672 x_return_status =>l_x_return_status ,
673 x_msg_data =>l_x_msg_data ,
674 x_msg_count =>l_x_msg_count
675 );
676
677 IF g1_debug_mode = 'Y' THEN
678 pa_debug.g_err_stage:='Called the Get_plan_plan_Rates:l_return_status'||l_x_return_status||'x_msg_data'||l_x_msg_data;
679 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
680 END IF;
681 IF g1_debug_mode = 'Y' THEN
682 pa_debug.g_err_stage:='Called the Get_plan_plan_Rates:x_cost_rej_code'||l_raw_cost_rejection_code||'l_burden_cost_rate'||l_txn_burden_cost_rate;
683 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
684 END IF;
685 IF g1_debug_mode = 'Y' THEN
686 pa_debug.g_err_stage:='Called the Get_plan_plan_Rates:l_raw_cost'||l_raw_cost||'l_cost_rate'||l_txn_cost_rate||'l_cost_curr_code'||l_cost_txn_curr_code;
687 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
688 END IF;
689 IF g1_debug_mode = 'Y' THEN
690 pa_debug.g_err_stage:='Called the Get_plan_plan_Rates:l_txn_bill_rate'||l_txn_bill_rate||'l_bill_markup'||l_bill_markup||'l_txn_bill_rate'||l_txn_bill_rate;
691 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
692 END IF;
693 IF g1_debug_mode = 'Y' THEN
694 pa_debug.g_err_stage:='Called the Get_plan_plan_Rates:l_raw_revenue'||l_raw_revenue ||'rev_rej_code'||l_revenue_rejection_code||'revcurr_code'||l_rev_txn_curr_code;
695 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
696 END IF;
697
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 IF l_raw_cost_rejection_code IS NOT NULL OR l_burden_cost_rejection_code IS NOT NULL THEN
702 l_x_msg_data := 'PA_COST1.Get_Plan_actual_Cost_Rates:' || SUBSTR(SQLERRM,1,250);
703 ELSE
704 l_x_msg_data := 'pa_plan_revenue.Get_Plan_plan_Rev_Rates :' || SUBSTR(SQLERRM,1,250);
705 END IF;
706 l_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
707 l_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
708 l_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
709 IF g1_debug_mode = 'Y' THEN
710 pa_debug.g_err_stage:=' Get_plan_plan_Rates_api is throwing When Others';
711 pa_debug.write('Get_planning_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
712 END IF;
713 RAISE l_Get_planning_Rates_api;
714 END;
715
716 END IF;/* End of check for calling for Actual or Planning rates Flow */
717
718 IF g1_debug_mode = 'Y' THEN
719 pa_debug.g_err_stage:='validating for Calling the Get_plan_res_class_rates ';
720 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
721 END IF;
722 /* Calling the Resource Class Schedule if Rate is not computed based on the Actual Enginee */
723 /* here the l_schedule type is set based on l_raw_cost IS NULL OR l_raw_revenue IS NULL and also based
724 on the p_calculate_mode also its decided whether to call the ressource class code to get the cost and revenue */
725 IF ( l_raw_cost IS NULL OR l_burden_cost IS NULL OR l_raw_revenue IS NULL) THEN
726 IF ( l_raw_cost IS NULL
727 AND l_raw_revenue IS NULL) THEN
728 IF l_calculate_mode='COST_REVENUE' THEN
729 l_schedule_type:=NULL;
730 ELSE
731 l_schedule_type:=l_calculate_mode;
732 END IF;
733 ELSIF ( l_raw_cost IS NULL ) THEN
734 IF l_calculate_mode<>'REVENUE' THEN
735 l_schedule_type:='COST';
736 else
737 l_schedule_type:='REVENUE';
738 END IF;
739 ELSIF ( l_raw_revenue IS NULL ) THEN
740 IF l_calculate_mode<>'COST' THEN
741 l_schedule_type:='REVENUE';
742 else
743 l_schedule_type:='COST';
744 END IF;
745 END IF;
746 IF g1_debug_mode = 'Y' THEN
747 pa_debug.g_err_stage:='Before Calling the Get_plan_res_class_rates:->l_schedule_type'||l_schedule_type||' l_raw_revenue'|| l_raw_revenue||'l_raw_cost'||l_raw_cost;
748 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
749 END IF;
750
751 /* don't remove the NVLs, the idea is to pass the override rate or any derive rates to the output of the main api call */
752 /* The overrides should be otherway round, If the overrides present use the overrides else rates present
753 l_i_txn_cost_rate :=NVL(l_txn_cost_rate,p_cost_override_rate);
754 l_i_txn_bill_rate :=NVL(l_txn_bill_rate,p_revenue_override_rate);
755 l_i_txn_burden_cost_rate :=NVL(l_txn_burden_cost_rate,p_override_burden_cost_rate);
756 */
757 l_i_txn_cost_rate :=NVL(p_cost_override_rate,l_txn_cost_rate);
758 l_i_txn_bill_rate :=NVL(p_revenue_override_rate,l_txn_bill_rate);
759 l_i_txn_burden_cost_rate :=NVL(p_override_burden_cost_rate,l_txn_burden_cost_rate);
760 l_i_txn_burden_multiplier :=l_txn_burden_multiplier;
761 /*CBS Changes*/
762 IF p_budget_version_id IS NOT null
763 then
764 BEGIN
765 SELECT NVL(res.RESOURCE_CLASS_FLAG,'Y') INTO l_resource_class_flag
766 FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT, pa_resource_lists_all_bg res
767 WHERE BV.BUDGET_VERSION_ID = p_budget_version_id
768 AND OPT.PROJECT_ID = BV.PROJECT_ID
769 AND OPT.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
770 AND OPT.FIN_PLAN_VERSION_ID = p_budget_version_id
771 AND BV.PROJECT_ID = p_project_id
772 AND res.resource_list_id = DECODE(BV.VERSION_TYPE,'COST', OPT.COST_RESOURCE_LIST_ID,
773 'REVENUE',OPT.REVENUE_RESOURCE_LIST_ID,
774 'ALL',OPT.ALL_RESOURCE_LIST_ID);
775 EXCEPTION
776 WHEN OTHERS THEN
777 l_resource_class_flag := 'Y';
778 END;
779 END IF;
780 IF p_budget_version_id IS NOT NULL AND l_resource_class_flag = 'N'
781 THEN
782 IF ((l_schedule_type='COST' and l_raw_cost IS NULL)
783 OR (l_schedule_type='REVENUE' and l_raw_revenue IS NULL)
784 OR l_schedule_type IS NULL)
785 THEN
786 BEGIN
787 Get_plan_default_rates (
788 p_project_type =>p_project_type
789 ,p_budget_version_id =>p_budget_version_id
790 ,p_use_planning_rates_flag =>p_use_planning_rates_flag
791 ,p_rate_based_flag =>p_rate_based_flag
792 ,p_quantity =>p_quantity
793 ,p_item_date =>p_item_date
794 ,p_schedule_type =>l_schedule_type
795 ,p_project_org_id =>p_project_org_id
796 ,p_expenditure_org_id =>p_expenditure_org_id --l_expenditure_org_id
797 ,p_nlr_organization_id =>p_nlr_organization_id
798 ,p_override_burden_cost_rate =>l_i_txn_burden_cost_rate
799 ,p_override_txn_currency_code =>p_override_currency_code
800 ,p_burden_cost =>NVL(l_burden_cost,p_burden_cost)
801 ,p_incurred_by_organz_id =>p_incurred_by_organz_id
802 ,p_system_linkage =>p_system_linkage
803 ,p_expenditure_type =>p_expenditure_type
804 ,p_project_id =>p_project_id
805 ,p_task_id =>p_task_id
806 ,p_plan_burden_cost_sch_id =>p_plan_burden_cost_sch_id
807 ,p_raw_cost =>NVL(l_raw_cost,p_raw_cost)
808 ,p_override_trxn_cost_rate =>l_i_txn_cost_rate
809 ,p_override_trxn_bill_rate =>l_i_txn_bill_rate
810 ,p_raw_revenue =>NVL(l_raw_revenue,p_raw_revenue)
811 ,p_override_to_organz_id =>p_override_to_organz_id
812 ,x_bill_rate =>l_txn_bill_rate
813 ,x_cost_rate =>l_txn_cost_rate
814 ,x_burden_cost_rate =>l_txn_burden_cost_rate
815 ,x_raw_cost =>l_txn_raw_cost
816 ,x_burden_cost =>l_txn_burden_cost
817 ,x_raw_revenue =>l_txn_raw_revenue
818 ,x_bill_markup_percentage =>l_txn_bill_markup
819 ,x_cost_markup_percentage =>l_txn_cost_markup
820 ,x_burden_multiplier =>l_txn_burden_multiplier
821 ,x_cost_txn_curr_code =>l_cost_res_txn_curr_code
822 ,x_rev_txn_curr_code =>l_rev_res_txn_curr_code
823 ,x_raw_cost_rejection_code =>l_raw_cost_rejection_code
824 ,x_burden_cost_rejection_code =>l_burden_cost_rejection_code
825 ,x_revenue_rejection_code =>l_revenue_rejection_code
826 ,x_cost_ind_compiled_set_id =>l_cost_ind_compiled_set_id
827 ,x_return_status =>l_x_return_status
828 ,x_msg_count =>l_x_msg_count
829 ,x_msg_data =>l_x_msg_data);
830 EXCEPTION
831 WHEN OTHERS THEN
832 l_x_msg_data := 'pa_plan_revenue.Get_plan_res_class_rates:' || SUBSTR(SQLERRM,1,250);
833 l_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
834 l_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
835 l_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
836 IF g1_debug_mode = 'Y' THEN
837 pa_debug.g_err_stage:=' Get_plan_res_class_rates_api is throwing When Others';
838 pa_debug.write('Get_planing_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
839 END IF;
840 RAISE l_Get_planning_Rates_api;
841 END;
842 ELSE
843 l_txn_raw_cost := l_raw_cost;
844 l_txn_burden_cost := l_burden_cost;
845 l_txn_raw_revenue := l_raw_revenue;
846 END IF;
847 ELSE
848
849 IF ( (l_schedule_type='COST' and l_raw_cost IS NULL )
850 OR (l_schedule_type='REVENUE' and l_raw_revenue IS NULL )
851 OR l_schedule_type IS NULL ) THEN
852 IF g1_debug_mode = 'Y' THEN
853 pa_debug.g_err_stage:='Now Calling the Get_plan_res_class_rates:->l_schedule_type'||l_schedule_type||' l_raw_revenue'|| l_raw_revenue||'l_raw_cost'||l_raw_cost;
854 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
855 END IF;
856 BEGIN
857 Get_plan_res_class_rates (
858 p_project_type =>p_project_type
859 ,p_project_id =>p_project_id
860 ,p_task_id =>p_task_id
861 ,p_person_id =>p_person_id
862 ,p_job_id =>p_job_id
863 ,p_resource_class =>p_resource_class
864 ,p_use_planning_rates_flag =>p_use_planning_rates_flag
865 ,p_rate_based_flag =>p_rate_based_flag
866 ,p_uom =>p_uom
867 ,p_project_organz_id =>p_project_organz_id
868 ,p_rev_res_class_rate_sch_id =>p_rev_res_class_rate_sch_id
869 ,p_cost_res_class_rate_sch_id =>p_cost_res_class_rate_sch_id
870 ,p_plan_burden_cost_sch_id =>p_plan_burden_cost_sch_id
871 ,p_cost_rate_multiplier =>p_cost_rate_multiplier
872 ,p_bill_rate_multiplier =>p_bill_rate_multiplier
873 ,p_quantity =>p_quantity
874 ,p_item_date =>p_item_date
875 ,p_schedule_type =>l_schedule_type
876 ,p_project_org_id =>p_project_org_id
877 ,p_incurred_by_organz_id =>p_incurred_by_organz_id
878 ,p_override_to_organz_id =>p_override_to_organz_id
879 ,p_expenditure_org_id =>p_expenditure_org_id --l_expenditure_org_id
880 ,p_nlr_organization_id =>p_nlr_organization_id
881 ,p_override_trxn_cost_rate =>l_i_txn_cost_rate
882 ,p_override_burden_cost_rate =>l_i_txn_burden_cost_rate
883 ,p_override_trxn_bill_rate =>l_i_txn_bill_rate
884 ,p_override_txn_currency_code =>p_override_currency_code
885 ,p_txn_currency_code =>NVL(l_cost_txn_curr_code,p_txn_currency_code)--4194214
886 ,p_raw_cost =>NVL(l_raw_cost,p_raw_cost)
887 ,p_burden_cost =>NVL(l_burden_cost,p_burden_cost)
888 ,p_raw_revenue =>NVL(l_raw_revenue,p_raw_revenue)
889 ,p_system_linkage =>p_system_linkage
890 ,p_expenditure_type =>p_expenditure_type
891 ,x_bill_rate =>l_txn_bill_rate
892 ,x_cost_rate =>l_txn_cost_rate
893 ,x_burden_cost_rate =>l_txn_burden_cost_rate
894 ,x_raw_cost =>l_txn_raw_cost
895 ,x_burden_cost =>l_txn_burden_cost
896 ,x_raw_revenue =>l_txn_raw_revenue
897 ,x_bill_markup_percentage =>l_txn_bill_markup
898 ,x_cost_markup_percentage =>l_txn_cost_markup
899 ,x_burden_multiplier =>l_txn_burden_multiplier
900 ,x_cost_txn_curr_code =>l_cost_res_txn_curr_code
901 ,x_rev_txn_curr_code =>l_rev_res_txn_curr_code
902 ,x_raw_cost_rejection_code =>l_raw_cost_rejection_code
903 ,x_burden_cost_rejection_code =>l_burden_cost_rejection_code
904 ,x_revenue_rejection_code =>l_revenue_rejection_code
905 ,x_cost_ind_compiled_set_id =>l_cost_ind_compiled_set_id
906 ,x_return_status =>l_x_return_status
907 ,x_msg_count =>l_x_msg_count
908 ,x_msg_data =>l_x_msg_data);
909
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 l_x_msg_data := 'pa_plan_revenue.Get_plan_res_class_rates:' || SUBSTR(SQLERRM,1,250);
914 l_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
915 l_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
916 l_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
917 IF g1_debug_mode = 'Y' THEN
918 pa_debug.g_err_stage:=' Get_plan_res_class_rates_api is throwing When Others';
919 pa_debug.write('Get_planing_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
920 END IF;
921 RAISE l_Get_planning_Rates_api;
922 END;
923 /*++++++++++++++++++++++++++++++++++++++++++++++++++*/
924 IF g1_debug_mode = 'Y' THEN
925 pa_debug.g_err_stage:='after Get_plan_res_class_rates :return_status'||l_x_return_status||'msgdata'||l_x_msg_data||'rawcost_rej_code'||l_raw_cost_rejection_code;
926 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
927 END IF;
928 IF g1_debug_mode = 'Y' THEN
929 pa_debug.g_err_stage:='after Get_plan_res_class_rates :x_revenue_rejection_code'||l_revenue_rejection_code||'l_txn_raw_cost'||l_txn_raw_cost;
930 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
931 END IF;
932 IF g1_debug_mode = 'Y' THEN
933 pa_debug.g_err_stage:='after Get_plan_res_class_rates :l_txn_burden_cost'||l_txn_burden_cost||'l_txn_raw_revenue'||l_txn_raw_revenue;
934 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
935 END IF;
936 ELSE
937 l_txn_raw_cost := l_raw_cost;
938 l_txn_burden_cost := l_burden_cost ;
939 l_txn_raw_revenue := l_raw_revenue;
940
941 END IF;/* IF ( (l_schedule_type='COST' and l_raw_cost IS NULL )OR (l_schedule_type='REVENUE' and l_raw_revenue IS NULL )*/
942 END IF;/*CBS Changes End*/
943 /* If Revenue or Cost is there that means rates must be associated to if computed by API
944 else pass the same cost and the override rate to the out parameters */
945
946 ELSE
947 l_txn_raw_cost := l_raw_cost;
948 l_txn_burden_cost := l_burden_cost ;
949 l_txn_raw_revenue := l_raw_revenue;
950 END IF;/* End of check for calling Resourtce Calss schedule */
951
952 END IF;/* IF p_raw_cost IS NOT NULL AND p_burden_cost IS NOT NULL AND p_raw_revenue IS NOT NULL THEN */
953 /* Assigning All the out parameters for the procedure */
954
955 IF g1_debug_mode = 'Y' THEN
956 pa_debug.g_err_stage:='Setting all the out parameters for thr procedure';
957 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
958 END IF;
959
960 x_bill_rate :=NVL(l_i_txn_bill_rate,l_txn_bill_rate);--4108291
961 x_cost_rate :=NVL(l_i_txn_cost_rate,l_txn_cost_rate) ;--4108291
962 x_burden_cost_rate :=NVL(l_i_txn_burden_cost_rate,l_txn_burden_cost_rate) ;
963 x_burden_multiplier :=NVL(l_i_txn_burden_multiplier,l_txn_burden_multiplier) ;--4108291
964 x_raw_cost :=l_txn_raw_cost;
965 x_burden_cost :=l_txn_burden_cost ;
966 x_raw_revenue :=l_txn_raw_revenue ;
967 x_bill_markup_percentage :=NVL(l_bill_markup,l_txn_bill_markup);
968 IF l_txn_raw_cost IS NOT NULL THEN
969 x_cost_txn_curr_code :=NVL(l_cost_txn_curr_code,l_cost_res_txn_curr_code) ;
970 END IF;
971 IF l_txn_raw_revenue IS NOT NULL THEN
972 x_rev_txn_curr_code :=NVL(l_rev_txn_curr_code,l_rev_res_txn_curr_code) ;
973 END IF;
974 x_raw_cost_rejection_code :=l_raw_cost_rejection_code ;
975 x_burden_cost_rejection_code :=l_burden_cost_rejection_code ;
976 x_revenue_rejection_code :=l_revenue_rejection_code;
977 x_cost_ind_compiled_set_id :=l_cost_ind_compiled_set_id ;
978 x_return_status :=l_x_return_status;
979 x_msg_data :=l_x_msg_data;
980 x_msg_count :=l_x_msg_count;
981 PA_DEBUG.reset_err_stack;
982 EXCEPTION
983 WHEN l_rate_based_no_quantity THEN
984 IF g1_debug_mode = 'Y' THEN
985 pa_debug.g_err_stage:='Quantity is required for a rate based transaction ';
986 pa_debug.write('Get_planning_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
987 END IF;
988 x_return_status := g_ERROR;
989 x_msg_count := 1;
990 x_msg_data := 'PA_EX_QTY_EXIST';
991 x_revenue_rejection_code := 'PA_EX_QTY_EXIST';
992 x_raw_cost_rejection_code := 'PA_EX_QTY_EXIST';
993 x_raw_revenue := NULL;
994 x_raw_cost := NULL;
995 x_bill_rate := NULL;
996 x_cost_rate := NULL;
997 x_cost_txn_curr_code := NULL ;
998 x_rev_txn_curr_code := NULL ;
999
1000 PA_DEBUG.reset_err_stack;
1001 WHEN l_invalid_currency THEN
1002 IF g1_debug_mode = 'Y' THEN
1003 pa_debug.g_err_stage:='Currecny Override is not entered for the ammounts entered';
1004 pa_debug.write('Get_planning_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
1005 END IF;
1006 x_return_status := g_ERROR;
1007 x_msg_count := 1;
1008 x_msg_data := 'PA_INVALID_DENOM_CURRENCY';
1009 x_revenue_rejection_code := 'PA_INVALID_DENOM_CURRENCY';
1010 x_raw_cost_rejection_code := 'PA_INVALID_DENOM_CURRENCY';
1011 x_raw_revenue := NULL ;
1012 x_raw_cost := NULL ;
1013 x_bill_rate := NULL ;
1014 x_cost_rate := NULL ;
1015 x_cost_txn_curr_code := NULL ;
1016 x_rev_txn_curr_code := NULL ;
1017
1018 PA_DEBUG.reset_err_stack;
1019 WHEN l_Get_planning_Rates_api THEN
1020 IF g1_debug_mode = 'Y' THEN
1021 pa_debug.g_err_stage:='plan_actual_Rates r Plan_plan_rates r res_class_rates throwing When Others:p_project_id'||p_project_id||'p_task_id'||p_task_id;
1022 pa_debug.write('Get_planning_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
1023 END IF;
1024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1025 x_msg_count := 1;
1026 x_msg_data := l_x_msg_data ;
1027 x_revenue_rejection_code := l_revenue_rejection_code;
1028 x_raw_cost_rejection_code := l_raw_cost_rejection_code;
1029 x_burden_cost_rejection_code:=l_burden_cost_rejection_code;
1030 x_raw_revenue := NULL;
1031 x_raw_cost := NULL;
1032 x_bill_rate := NULL;
1033 x_cost_rate := NULL;
1034 x_cost_txn_curr_code := NULL;
1035 x_rev_txn_curr_code := NULL;
1036 x_bill_markup_percentage := NULL;
1037
1038 PA_DEBUG.reset_err_stack;
1039
1040 WHEN OTHERS THEN
1041 IF g1_debug_mode = 'Y' THEN
1042 pa_debug.g_err_stage:='In the when others of Get_Planning_Rates:p_project_id'||p_project_id||'p_task_id'||p_task_id;
1043 pa_debug.write('Get_planing_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
1044 END IF;
1045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1046 x_msg_count := 1;
1047 x_msg_data := 'Get_Planning_Rates'||SUBSTR(SQLERRM,1,250);
1048 x_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
1049 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
1050 x_burden_cost_rejection_code:=SUBSTR(SQLERRM,1,30);
1051 x_raw_revenue := NULL;
1052 x_raw_cost := NULL;
1053 x_bill_rate := NULL;
1054 x_cost_rate := NULL;
1055 x_cost_txn_curr_code := NULL;
1056 x_rev_txn_curr_code := NULL;
1057 x_bill_markup_percentage := NULL;
1058
1059 PA_DEBUG.reset_err_stack;
1060
1061 END Get_Planning_Rates;
1062
1063
1064 --
1065 -- Procedure : Get_plan_actual_Rates
1066 -- Purpose : This is an internal procedure for calculating the bill rate and raw revenue from one of
1067 -- the given criteria's on the basis of passed parameters for the 'ACTUAL RATES' of Planning Transaction
1068 -- Parameters :
1069 --
1070
1071
1072 PROCEDURE Get_plan_actual_Rates (
1073 p_project_id IN NUMBER,
1074 p_task_id IN NUMBER DEFAULT NULL,
1075 p_top_task_id IN NUMBER DEFAULT NULL, /* for costing top task Id */
1076 p_person_id IN NUMBER,
1077 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
1078 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
1079 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
1080 p_planning_resource_format IN VARCHAR2 , /* resource format required for Costing */
1081 p_use_planning_rates_flag IN VARCHAR2 DEFAULT 'N', /* Rate using Actual Rates */
1082 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
1083 p_uom IN VARCHAR2, /* Planning UOM */
1084 p_system_linkage IN VARCHAR2,
1085 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
1086 p_rev_task_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
1087 p_rev_proj_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
1088 p_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Job*/
1089 p_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Emp*/
1090 p_calculate_mode IN VARCHAR2 DEFAULT 'COST_REVENUE' ,/* useed for calculating either only Cost(COST),only Revenue(REVENUE) or both Cost and Revenue(COST_REVENUE) */
1091 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
1092 p_cost_rate_multiplier IN NUMBER DEFAULT NULL ,
1093 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
1094 p_quantity IN NUMBER, /* required param for People/Equipment Class */
1095 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
1096 p_cost_sch_type IN VARCHAR2 , /* Costing Schedule Type'COST' / 'REVENUE' / 'INVOICE' */
1097 p_labor_sch_type IN VARCHAR2, /* Revenue Labor Schedule Type B/I */
1098 p_non_labor_sch_type IN VARCHAR2 , /* Revenue Non_Labor Schedule Type B/I */
1099 p_labor_schdl_discnt IN NUMBER DEFAULT NULL,
1100 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL,
1101 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL,
1102 p_labor_schdl_fixed_date IN DATE DEFAULT NULL,
1103 p_assignment_id IN NUMBER DEFAULT NULL, /* used as p_item_id in the internal APIs */
1104 p_project_org_id IN NUMBER, /* Project Org Id */
1105 p_project_type IN VARCHAR2,
1106 p_expenditure_type IN VARCHAR2,
1107 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
1108 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
1109 p_override_to_organz_id IN NUMBER, /* Override Org Id */
1110 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
1111 p_assignment_precedes_task IN VARCHAR2, /* Added for Asgmt overide */
1112 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
1113 p_task_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at Project Level */
1114 p_project_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at task Level */
1115 p_nlr_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
1116 p_project_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at project Level */
1117 p_task_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at task Level */
1118 p_project_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at project Level */
1119 p_task_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at task Level */
1120 p_inventory_item_id IN NUMBER DEFAULT NULL, /* Passed for Inventoty Items */
1121 p_BOM_resource_Id IN NUMBER DEFAULT NULL, /* Passed for BOM Resource Id */
1122 P_mfc_cost_type_id IN NUMBER DEFAULT 0, /* Manufacturing cost api */
1123 P_item_category_id IN NUMBER DEFAULT NULL, /* Manufacturing cost api */
1124 p_mfc_cost_source IN NUMBER DEFAULT 1,
1125 p_cost_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to costing internal api.*/
1126 p_revenue_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to billing internal api.*/
1127 p_override_burden_cost_rate IN NUMBER DEFAULT NULL,
1128 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
1129 p_txn_currency_code IN VARCHAR2 DEFAULT NULL, /* if not null, amounts to be returned in this currency only else in x_txn_curr_code*/
1130 p_raw_cost IN NUMBER, /*If p_raw_cost is only passed,return the burden multiplier, burden_cost */
1131 p_burden_cost IN NUMBER DEFAULT NULL,
1132 p_raw_revenue IN NUMBER DEFAULT NULL,
1133 x_bill_rate OUT NOCOPY NUMBER,
1134 x_cost_rate OUT NOCOPY NUMBER,
1135 x_burden_cost_rate OUT NOCOPY NUMBER,
1136 x_burden_multiplier OUT NOCOPY NUMBER,
1137 x_raw_cost OUT NOCOPY NUMBER,
1138 x_burden_cost OUT NOCOPY NUMBER,
1139 x_raw_revenue OUT NOCOPY NUMBER,
1140 x_bill_markup_percentage OUT NOCOPY NUMBER,
1141 x_cost_txn_curr_code OUT NOCOPY VARCHAR2,
1142 x_rev_txn_curr_code OUT NOCOPY VARCHAR2,
1143 x_raw_cost_rejection_code OUT NOCOPY VARCHAR2,
1144 x_burden_cost_rejection_code OUT NOCOPY VARCHAR2,
1145 x_revenue_rejection_code OUT NOCOPY VARCHAR2,
1146 x_cost_ind_compiled_set_id OUT NOCOPY NUMBER,
1147 x_return_status OUT NOCOPY VARCHAR2,
1148 x_msg_data OUT NOCOPY VARCHAR2,
1149 x_msg_count OUT NOCOPY NUMBER
1150 )
1151 IS
1152 l_raw_cost NUMBER :=NULL;
1153 l_burden_cost NUMBER :=NULL;
1154 l_raw_revenue NUMBER:=NULL;
1155 l_x_return_status VARCHAR2(2):= g_success;
1156 l_cost_msg_count NUMBER;
1157 l_cost_msg_data VARCHAR2(1000);
1158 l_bill_msg_count NUMBER;
1159 l_bill_msg_data VARCHAR2(1000);
1160 l_called_process VARCHAR2(40);
1161 l_txn_curr_code VARCHAR2(30);
1162 l_trxn_curr_code VARCHAR2(30);
1163 l_cost_txn_curr_code VARCHAR2(30);
1164 l_rev_txn_curr_code VARCHAR2(30);
1165 l_rev_curr_code VARCHAR2(30);
1166 l_txn_cost NUMBER:=NULL; /* to store the value of p_burden_cost or p_raw_cost */
1167 l_proj_nl_bill_rate_sch_id NUMBER;
1168 l_task_nl_bill_rate_sch_id NUMBER;
1169 l_txn_cost_rate NUMBER;
1170 l_txn_raw_cost_rate NUMBER;
1171 l_txn_burden_cost_rate NUMBER;
1172 l_txn_bill_rate NUMBER;
1173 l_txn_bill_markup NUMBER:=NULL;
1174 l_txn_raw_cost NUMBER;
1175 l_txn_burden_cost NUMBER;
1176 l_txn_raw_revenue NUMBER;
1177 l_sl_function NUMBER ;
1178 l_exp_func_Curr_code VARCHAR2(30);
1179 l_raw_cost_rate NUMBER ;
1180 l_burden_cost_rate NUMBER ;
1181 l_bill_rate NUMBER:=NULL;
1182 l_burden_multiplier NUMBER;
1183 l_raw_cost_rejection_code VARCHAR2(1000);
1184 l_burden_cost_rejection_code VARCHAR2(1000);
1185 l_cost_ind_compiled_set_id NUMBER;
1186 l_proj_cost_job_id NUMBER;
1187 l_expenditure_org_id NUMBER;
1188
1189
1190
1191 BEGIN
1192 l_raw_revenue := p_raw_revenue;
1193 l_raw_cost := p_raw_cost;
1194 l_burden_cost := p_burden_cost;
1195 IF upper(p_resource_class)='PEOPLE' THEN
1196 l_expenditure_org_id :=nvl(p_incurred_by_organz_id, p_override_to_organz_id );
1197 ELSE
1198 l_expenditure_org_id :=nvl(p_nlr_organization_id,p_override_to_organz_id );
1199 END IF;
1200
1201 IF p_system_linkage='BTC' THEN
1202 l_txn_cost := p_burden_cost;
1203 ELSE
1204 l_txn_cost := p_raw_cost;
1205 END IF;
1206 IF ((p_raw_cost IS NULL OR p_burden_cost IS NULL)
1207 AND p_calculate_mode IN ('COST','COST_REVENUE')) THEN
1208 IF g1_debug_mode = 'Y' THEN
1209 pa_debug.g_err_stage:='Before Calling PA_COST1.Get_Plan_Actual_Cost_Rates';
1210 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1211 END IF;
1212
1213 BEGIN
1214 PA_COST1.Get_Plan_Actual_Cost_Rates
1215 (p_calling_mode =>'ACTUAL_RATES'
1216 ,p_project_type =>p_project_type
1217 ,p_project_id =>p_project_id
1218 ,p_task_id =>p_task_id
1219 ,p_top_task_id =>p_top_task_id
1220 ,p_Exp_item_date =>p_item_date
1221 ,p_expenditure_type =>p_expenditure_type
1222 ,p_expenditure_OU =>p_expenditure_org_id
1223 ,p_project_OU =>p_project_org_id
1224 ,p_Quantity =>p_Quantity
1225 ,p_resource_class =>p_resource_class
1226 ,p_person_id =>p_person_id
1227 ,p_non_labor_resource =>p_non_labor_resource
1228 ,p_NLR_organization_id =>p_NLR_organization_id
1229 ,p_override_organization_id =>p_override_to_organz_id
1230 ,p_incurred_by_organization_id =>p_incurred_by_organz_id
1231 ,p_inventory_item_id =>p_inventory_item_id
1232 ,p_BOM_resource_id =>p_BOM_resource_id
1233 ,p_override_trxn_curr_code =>p_override_currency_code
1234 ,p_override_burden_cost_rate =>p_override_burden_cost_rate
1235 ,p_override_trxn_cost_rate =>p_cost_override_rate
1236 ,p_override_trxn_raw_cost =>p_raw_cost
1237 ,p_override_trxn_burden_cost =>p_burden_cost
1238 ,p_mfc_cost_type_id =>p_mfc_cost_type_id
1239 ,p_mfc_cost_source =>p_mfc_cost_source --check
1240 ,p_item_category_id =>p_item_category_id
1241 ,p_job_id =>p_job_id
1242 , p_plan_cost_job_rate_sch_id =>NULL
1243 , p_plan_cost_emp_rate_sch_id =>NULL
1244 , p_plan_cost_nlr_rate_sch_id =>NULL
1245 , p_plan_cost_burden_sch_id =>NULL
1246 ,p_resource_assignment_id =>p_assignment_id /* bug 12374022 */
1247 ,x_trxn_curr_code =>l_trxn_curr_code
1248 ,x_trxn_raw_cost =>l_txn_raw_cost
1249 ,x_trxn_raw_cost_rate =>l_txn_cost_rate
1250 ,x_trxn_burden_cost =>l_txn_burden_cost
1251 ,x_trxn_burden_cost_rate =>l_txn_burden_cost_rate
1252 ,x_burden_multiplier =>l_burden_multiplier
1253 ,x_cost_ind_compiled_set_id =>l_cost_ind_compiled_set_id
1254 ,x_raw_cost_rejection_code =>l_raw_cost_rejection_code
1255 ,x_burden_cost_rejection_code =>l_burden_cost_rejection_code
1256 ,x_return_status =>l_x_return_status
1257 ,x_error_msg_code =>l_cost_msg_data ) ;
1258
1259 IF g1_debug_mode = 'Y' THEN
1260 pa_debug.g_err_stage:='Called PA_COST1.Get_Plan_Actual_Cost_Rates:l_x_return_status'||l_x_return_status||'l_msg_data'||l_cost_msg_data||'x_cost_rej_code'||l_raw_cost_rejection_code;
1261 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
1262 END IF;
1263 IF g1_debug_mode = 'Y' THEN
1264 pa_debug.g_err_stage:='Called PA_COST1.Get_Plan_Actual_Cost_Rates:x_burden_rej_code'||l_burden_cost_rejection_code||'l_txn_raw_cost'||l_txn_raw_cost;
1265 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
1266 END IF;
1267 IF g1_debug_mode = 'Y' THEN
1268 pa_debug.g_err_stage:='Called PA_COST1.Get_Plan_Actual_Cost_Rates:l_txn_cost_rate'||l_txn_cost_rate||'l_trxn_curr_code'||l_trxn_curr_code;
1269 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
1270 END IF;
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 x_msg_data := 'PA_COST1.Get_Plan_Actual_Cost_Rates:' || SUBSTR(SQLERRM,1,250);
1274 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
1275 x_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
1276 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1277
1278 IF g1_debug_mode = 'Y' THEN
1279 pa_debug.g_err_stage:=' PA_COST1.Get_Plan_Actual_Cost_Rates is throwing When Others';
1280 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
1281 END IF;
1282 RAISE;
1283 -- RAISE l_Get_plan_actual_Rates; /* Added to handle exception return by costing api */
1284 END;
1285 /* transferring the outout cost to one cost and checking if the costing API has computed Cost */
1286 IF p_system_linkage='BTC' THEN
1287 l_txn_cost := l_txn_burden_cost;
1288 ELSE
1289 l_txn_cost :=l_txn_raw_cost;
1290 END IF;
1291
1292
1293 ELSE
1294 /* If p_raw_cost and p_burden Cost are passed Costing API
1295 won't be called but the same value will be passed as it is */
1296 l_txn_raw_cost :=l_raw_cost ;
1297 l_txn_burden_cost :=l_burden_cost ;
1298 IF p_quantity <>0 THEN
1299 l_txn_cost_rate :=l_raw_cost/(NVL(p_quantity,1)) ;
1300 l_txn_burden_cost_rate :=l_burden_cost/(NVL(p_quantity,1)) ;
1301 END IF;
1302
1303 END IF;
1304
1305 /* Sending out all the out parametrs of Costing , This is send out here as even if the costing API has failed
1306 Revenue API will be called and revenue calculated if the required values are passed to the Billing API,
1307 though it'll pass the rejection code of Costing APi in the out parameters*/
1308 x_cost_rate := l_txn_cost_rate;
1309 x_burden_cost_rate := l_txn_burden_cost_rate;
1310 x_burden_multiplier := l_burden_multiplier ;
1311 x_raw_cost := l_txn_raw_cost;
1312 x_burden_cost := l_txn_burden_cost;
1313 x_cost_txn_curr_code := l_trxn_curr_code;
1314 x_raw_cost_rejection_code := l_raw_cost_rejection_code ;
1315 x_burden_cost_rejection_code := l_burden_cost_rejection_code;
1316 x_cost_ind_compiled_set_id := l_cost_ind_compiled_set_id;
1317 x_return_status := l_x_return_status ;
1318 x_msg_data := l_cost_msg_data ;
1319
1320 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
1321
1322 IF p_calculate_mode IN ('REVENUE','COST_REVENUE') THEN
1323
1324 /* Calling the Billing Revenue calculation Api only if p_raw_revenue is null */
1325 IF l_raw_revenue IS NULL THEN
1326 /* Checking for Rate based whether quantity is entered else */
1327 IF p_rate_based_flag ='Y' THEN
1328 null;
1329 ELSE
1330 IF NVL(l_txn_cost,0)=0 THEN
1331 IF p_quantity is NOT NULL and p_revenue_override_rate is not null then
1332 null;
1333 else
1334 RAISE l_no_cost;
1335 END IF;
1336 END IF;
1337 END IF;
1338
1339 IF g1_debug_mode = 'Y' THEN
1340 pa_debug.g_err_stage:='Calling Get_Plan_Actual_Rev_Rates';
1341 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1342 END IF;
1343
1344 Get_Plan_Actual_Rev_Rates (
1345 p_project_id => p_project_id ,
1346 p_task_id => p_task_id,
1347 p_person_id => p_person_id ,
1348 p_job_id => p_job_id,
1349 p_bill_job_grp_id => p_bill_job_grp_id,
1350 p_resource_class => p_resource_class,
1351 p_rate_based_flag => p_rate_based_flag,
1352 p_uom => p_uom,
1353 p_system_linkage => p_system_linkage,
1354 p_project_organz_id => p_project_organz_id,
1355 p_rev_proj_nl_rate_sch_id => p_rev_proj_nl_rate_sch_id ,
1356 p_rev_task_nl_rate_sch_id => p_rev_task_nl_rate_sch_id ,
1357 p_rev_job_rate_sch_id => p_rev_job_rate_sch_id,
1358 p_rev_emp_rate_sch_id => p_rev_emp_rate_sch_id ,
1359 p_mcb_flag => p_mcb_flag,
1360 p_bill_rate_multiplier => p_bill_rate_multiplier ,
1361 p_quantity => p_quantity ,
1362 p_item_date => p_item_date,
1363 p_labor_sch_type => p_labor_sch_type,
1364 p_labor_schdl_discnt => p_labor_schdl_discnt ,
1365 p_labor_bill_rate_org_id => p_labor_bill_rate_org_id ,
1366 p_labor_std_bill_rate_schdl => p_labor_std_bill_rate_schdl ,
1367 p_labor_schdl_fixed_date => p_labor_schdl_fixed_date ,
1368 p_assignment_id => p_assignment_id ,
1369 p_project_org_id => p_project_org_id ,
1370 p_project_type => p_project_type,
1371 p_expenditure_type => p_expenditure_type ,
1372 p_incurred_by_organz_id => p_incurred_by_organz_id ,
1373 p_override_to_organz_id => p_override_to_organz_id ,
1374 p_expenditure_org_id => l_expenditure_org_id, --p_expenditure_org_id ,
1375 p_assignment_precedes_task => p_assignment_precedes_task ,
1376 p_planning_transaction_id => p_planning_transaction_id,
1377 p_task_bill_rate_org_id => p_task_bill_rate_org_id,
1378 p_project_bill_rate_org_id => p_project_bill_rate_org_id ,
1379 p_non_labor_resource => p_non_labor_resource ,
1380 p_NLR_organization_id => p_NLR_organization_id ,
1381 p_non_labor_sch_type => p_non_labor_sch_type ,
1382 p_project_sch_date => p_project_sch_date ,
1383 p_task_sch_date => p_task_sch_date,
1384 p_project_sch_discount => p_project_sch_discount,
1385 p_task_sch_discount => p_task_sch_discount,
1386 p_revenue_override_rate => p_revenue_override_rate,
1387 p_override_currency_code => p_override_currency_code,
1388 p_txn_currency_code => l_trxn_curr_code ,
1389 p_raw_cost => l_txn_raw_cost,
1390 p_burden_cost => l_txn_burden_cost,
1391 p_raw_revenue => l_raw_revenue,
1392 p_raw_cost_rate => l_txn_cost_rate ,
1393 x_bill_rate => l_txn_bill_rate,
1394 x_raw_revenue => l_txn_raw_revenue,
1395 x_bill_markup_percentage => l_txn_bill_markup,
1396 x_txn_curr_code => l_rev_txn_curr_code,
1397 x_return_status => l_x_return_status,
1398 x_msg_data => l_bill_msg_data,
1399 x_msg_count => l_bill_msg_count
1400 );
1401 IF g1_debug_mode = 'Y' THEN
1402 pa_debug.g_err_stage:='OUT of Get_Plan_Actual_Rev_Rates:l_x_return_status'||l_x_return_status||'l_bill_msg_data'||l_bill_msg_data||'l_txn_raw_revenue'||l_txn_raw_revenue;
1403 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1404 END IF;
1405
1406 /* Raising the Billing Exception to pass the error values to the Main Api */
1407 IF l_x_return_status <> g_success THEN
1408 RAISE l_bill_api;
1409 END IF;
1410
1411 ELSE
1412 IF p_override_currency_Code IS NULL THEN
1413 RAISE l_invalid_currency;
1414 END IF;
1415 l_txn_raw_revenue :=l_raw_revenue ;
1416 IF p_quantity <>0 THEN
1417 l_txn_bill_rate :=l_raw_revenue/(NVL(p_quantity,1)) ;
1418 END IF;
1419 l_rev_txn_curr_code:=p_override_currency_Code;
1420 END IF;
1421 /* Passing the output parametrs of Billing for Revenue */
1422 END IF;/* End of IF p_calculate_mode IN ('REVENUE','COST_REVENUE') THEN */
1423
1424 x_raw_revenue :=l_txn_raw_revenue;
1425 x_bill_rate :=l_txn_bill_rate ;
1426 x_bill_markup_percentage :=l_txn_bill_markup;
1427 x_rev_txn_curr_code :=l_rev_txn_curr_code; /* x_txn_currency_code for Labor and x_rev_curr_code for non labor */
1428 x_revenue_rejection_code :=NULL;
1429 x_return_status :=l_x_return_status;
1430
1431 EXCEPTION
1432 WHEN l_invalid_currency THEN
1433
1434 IF g1_debug_mode = 'Y' THEN
1435 pa_debug.g_err_stage:='Currecny Override is not entered for the ammounts entered:p_project_id'||p_project_id||'p_task_id'||p_task_id;
1436 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1437 END IF;
1438 x_return_status := g_ERROR;
1439 x_msg_count := 1;
1440 x_msg_data := 'PA_INVALID_DENOM_CURRENCY';
1441 x_revenue_rejection_code := 'PA_INVALID_DENOM_CURRENCY';
1442 x_raw_cost_rejection_code := l_raw_cost_rejection_code;
1443 x_burden_cost_rejection_code := l_burden_cost_rejection_code;
1444 x_raw_revenue := NULL;
1445 x_bill_rate := NULL;
1446 x_rev_txn_curr_code :=NULL ;
1447
1448 WHEN l_no_cost THEN
1449 x_raw_revenue := NULL;
1450 x_bill_rate := NULL;
1451 x_bill_markup_percentage := NULL;
1452 x_rev_txn_curr_code := NULL;
1453 x_revenue_rejection_code := 'PA_NO_ACCT_COST';
1454 x_return_status := g_error;
1455 x_msg_data := 'PA_NO_ACCT_COST';
1456 x_msg_count := 1;
1457
1458
1459 IF g1_debug_mode = 'Y' THEN
1460 pa_debug.g_err_stage:='No Cost exist for the tranascation:p_project_id'||p_project_id||'p_task_id'||p_task_id;
1461 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1462 END IF;
1463 WHEN l_bill_api THEN
1464 x_raw_revenue := NULL;
1465 x_bill_rate := NULL;
1466 x_bill_markup_percentage := NULL;
1467 x_rev_txn_curr_code := NULL;
1468 x_revenue_rejection_code := l_bill_msg_data;
1469 x_return_status := l_x_return_status;
1470 x_msg_data := l_bill_msg_data;
1471 x_msg_count := l_bill_msg_count;
1472
1473 IF g1_debug_mode = 'Y' THEN
1474 pa_debug.g_err_stage:='Billing api is throwing error';
1475 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1476 END IF;
1477
1478
1479 END Get_plan_actual_Rates;
1480
1481
1482 -- Procedure : Get_plan_actual_RevRates
1483 -- Purpose : This is an internal procedure for calculating the bill rate and raw revenue from one of
1484 -- the given criteria's on the basis of passed parameters for the 'ACTUAL RATES' of Planning Transaction
1485 -- Parameters :
1486 --
1487
1488 PROCEDURE Get_Plan_Actual_Rev_Rates (
1489 p_project_id IN NUMBER,
1490 p_task_id IN NUMBER DEFAULT NULL,
1491 p_person_id IN NUMBER,
1492 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
1493 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
1494 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
1495 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
1496 p_uom IN VARCHAR2, /* Planning UOM */
1497 p_system_linkage IN VARCHAR2,
1498 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
1499 p_rev_proj_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
1500 p_rev_task_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
1501 p_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Job*/
1502 p_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Emp*/
1503 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
1504 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
1505 p_quantity IN NUMBER, /* required param for People/Equipment Class */
1506 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
1507 p_labor_sch_type IN VARCHAR2, /* Revenue Labor Schedule Type B/I */
1508 p_labor_schdl_discnt IN NUMBER DEFAULT NULL,
1509 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL,
1510 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL,
1511 p_labor_schdl_fixed_date IN DATE DEFAULT NULL,
1512 p_assignment_id IN NUMBER DEFAULT NULL, /* used as p_item_id in the internal APIs */
1513 p_project_org_id IN NUMBER, /* Project Org Id */
1514 p_project_type IN VARCHAR2,
1515 p_expenditure_type IN VARCHAR2,
1516 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
1517 p_override_to_organz_id IN NUMBER, /* Override Org Id */
1518 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
1519 p_assignment_precedes_task IN VARCHAR2, /* Added for Asgmt overide */
1520 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
1521 p_task_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at Project Level */
1522 p_project_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at task Level */
1523 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
1524 p_NLR_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
1525 p_non_labor_sch_type IN VARCHAR2 , /* Revenue Non_Labor Schedule Type B/I */
1526 p_project_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at project Level */
1527 p_task_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at task Level */
1528 p_project_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at project Level */
1529 p_task_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at task Level */
1530 p_revenue_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to billing internal api.*/
1531 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
1532 p_txn_currency_code IN VARCHAR2 DEFAULT NULL, /* if not null, amounts to be returned in this currency only else in x_txn_curr_code*/
1533 p_raw_cost IN NUMBER, /*If p_raw_cost is only passed,return the burden multiplier, burden_cost */
1534 p_burden_cost IN NUMBER DEFAULT NULL,
1535 p_raw_revenue IN NUMBER DEFAULT NULL,
1536 p_raw_cost_rate IN NUMBER DEFAULT NULL,
1537 x_bill_rate OUT NOCOPY NUMBER,
1538 x_raw_revenue OUT NOCOPY NUMBER,
1539 x_bill_markup_percentage OUT NOCOPY NUMBER,
1540 x_txn_curr_code OUT NOCOPY VARCHAR2, /* x_txn_currency_code for Labor and x_rev_curr_code for non labor */
1541 x_return_status OUT NOCOPY VARCHAR2,
1542 x_msg_data OUT NOCOPY VARCHAR2,
1543 x_msg_count OUT NOCOPY NUMBER
1544 )
1545 IS
1546 l_x_return_status VARCHAR2(20):=g_success;
1547 l_msg_count NUMBER;
1548 l_msg_data VARCHAR2(1000);
1549 l_called_process VARCHAR2(40);
1550 l_txn_curr_code VARCHAR2(30);
1551 l_rev_curr_code VARCHAR2(30);
1552 l_override_cost NUMBER:=NULL;
1553 l_proj_nl_bill_rate_sch_id NUMBER;
1554 l_task_nl_bill_rate_sch_id NUMBER;
1555 l_txn_bill_rate NUMBER:=NULL;
1556 l_txn_bill_markup NUMBER:=NULL;
1557 l_raw_revenue NUMBER:=NULL;
1558 l_txn_raw_revenue NUMBER;
1559 l_sl_function NUMBER ;
1560 l_exp_func_Curr_code VARCHAR2(30);
1561 l_project_curr_code VARCHAR2(30);
1562 l_projfunc_curr_code VARCHAR2(30);
1563 l_project_raw_cost NUMBER;
1564 l_project_raw_cost_rate NUMBER;
1565 l_project_burdened_cost NUMBER;
1566 l_project_burdened_cost_rate NUMBER;
1567 l_projfunc_raw_cost NUMBER;
1568 l_projfunc_raw_cost_rate NUMBER;
1569 l_projfunc_burdened_cost NUMBER;
1570 l_projfunc_burdened_cost_rate NUMBER;
1571 l_convert_return_status VARCHAR2(30):= g_success;
1572 l_error_msg_code VARCHAR2(2000);
1573 l_uom VARCHAR2(30);
1574 l_uom_flag NUMBER:=1;
1575 l_txn_adjusted_bill_rate NUMBER:=NULL;--4038485
1576 l_quantity NUMBER:=NULL; --bug#4284806
1577
1578 BEGIN
1579 IF g1_debug_mode = 'Y' THEN
1580 pa_debug.g_err_stage:='Validating all the input parameters';
1581 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1582 END IF;
1583
1584
1585 IF p_system_linkage='BTC' THEN
1586 l_override_cost := p_burden_cost;
1587 l_sl_function :=6;
1588 ELSE
1589 l_override_cost := p_raw_cost;
1590 l_sl_function :=2;
1591 END IF;
1592
1593 IF p_revenue_override_rate IS NOT NULL AND p_override_currency_code IS NULL THEN
1594 IF g1_debug_mode = 'Y' THEN
1595 pa_debug.g_err_stage:='Validating Get_plan_actual_rev_Rates:p_override_currency_code is required if passing any overrides';
1596 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1597 END IF;
1598 RAISE l_invalid_currency;
1599 END IF;
1600 IF p_rate_based_flag ='Y' AND p_quantity IS NULL
1601 AND NVL(l_override_cost,0)=0 THEN
1602 IF g1_debug_mode = 'Y' THEN
1603 pa_debug.g_err_stage:='Validating Get_plan_actual_rev_Rates:p_quantity is required for rate based';
1604 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1605 END IF;
1606 RAISE l_rate_based_no_quantity;
1607 END IF;
1608 /* If revenue Override rate is not null compute the raw_revenue based on the override rate and the p_quantity or rawCost */
1609 IF p_revenue_override_rate IS NOT NULL THEN
1610 SELECT p_revenue_override_rate b_rate,
1611 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((p_revenue_override_rate * p_quantity), p_override_currency_code) r_revenue,
1612 p_override_currency_code
1613 INTO l_txn_bill_rate,l_raw_revenue,l_txn_curr_code
1614 FROM dual;
1615 l_txn_raw_revenue:=l_raw_revenue;
1616
1617 END IF;/* End of check for p_revenue_override_rate */
1618 /* If in the above case the Raw Revenue is null then go for calling
1619 actual internal api of billing to compute the raw Revenue */
1620
1621 IF l_raw_revenue IS NULL THEN
1622
1623 IF p_task_Id IS NOT NULL THEN
1624 l_called_process := 'TASK_LEVEL_PLANNING';
1625 ELSE
1626 l_called_process := 'PROJECT_LEVEL_PLANNING';
1627 END IF;
1628 /*bug#4284806 If the transaction is non rate based which means the UOM is currency , then during revenue rate derivation ,
1629 for both actuals and resource class and for both labor ( people ) and non labor resource class you should ignore rate,
1630 if the setup is rate.
1631
1632 So setting the p_quanity to null for non-rate based transaction*/
1633
1634 IF p_rate_based_flag ='Y' THEN
1635 l_quantity:=p_quantity;
1636 else
1637 l_quantity:=null;
1638 end if;
1639
1640 /* Going to Call the Core Billing API for Revenue Calculation based on the Resource Class */
1641 IF p_resource_class='PEOPLE' THEN
1642 IF g1_debug_mode = 'Y' THEN
1643 pa_debug.g_err_stage:='Calling PA_REVENUE.Assignment_Rev_Amt';
1644 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1645 END IF;
1646
1647 PA_REVENUE.Assignment_Rev_Amt(
1648 p_project_id => p_project_id ,
1649 p_task_id => p_task_id ,
1650 p_bill_rate_multiplier => p_bill_rate_multiplier ,
1651 p_quantity => l_quantity ,
1652 p_person_id => p_person_id ,
1653 p_raw_cost => p_raw_cost,
1654 p_item_date => p_item_date ,
1655 p_labor_schdl_discnt => p_labor_schdl_discnt ,
1656 p_labor_bill_rate_org_id => p_labor_bill_rate_org_id ,
1657 p_labor_std_bill_rate_schdl => p_labor_std_bill_rate_schdl ,
1658 p_labor_schdl_fixed_date => p_labor_schdl_fixed_date ,
1659 p_bill_job_grp_id => p_bill_job_grp_id ,
1660 p_item_id => p_assignment_id ,
1661 p_forecast_item_id => p_planning_transaction_id ,
1662 p_labor_sch_type => p_labor_sch_type ,
1663 p_project_org_id => p_project_org_id ,
1664 p_project_type => p_project_type ,
1665 p_expenditure_type => p_expenditure_type ,
1666 p_exp_func_curr_code => p_txn_currency_code ,
1667 p_incurred_by_organz_id => p_incurred_by_organz_id ,
1668 p_raw_cost_rate => p_raw_cost_rate ,
1669 p_override_to_organz_id => p_override_to_organz_id ,
1670 p_emp_bill_rate_schedule_id => p_rev_emp_rate_sch_id ,
1671 p_job_bill_rate_schedule_id => p_rev_job_rate_sch_id,
1672 p_resource_job_id => p_job_id ,
1673 p_exp_raw_cost => p_raw_cost ,
1674 p_expenditure_org_id => p_expenditure_org_id ,
1675 p_projfunc_currency_code => p_txn_currency_code,
1676 p_assignment_precedes_task => p_assignment_precedes_task ,
1677 p_sys_linkage_function => p_system_linkage,
1678 p_called_process => l_called_process ,
1679 p_project_raw_cost => l_project_raw_cost,
1680 p_project_currency_code => p_txn_currency_code,
1681 p_denom_raw_cost => p_raw_cost,
1682 p_denom_curr_code => p_txn_currency_code,
1683 p_mcb_flag => NULL,
1684 x_bill_rate => l_txn_bill_rate ,
1685 x_adjusted_bill_rate => l_txn_adjusted_bill_rate , --4038485
1686 x_raw_revenue => l_txn_raw_revenue,
1687 x_markup_percentage => l_txn_bill_markup ,
1688 x_txn_currency_code => l_txn_curr_code,
1689 x_rev_currency_code => l_rev_curr_code ,
1690 x_return_status => l_x_return_status ,
1691 x_msg_count => l_msg_count ,
1692 x_msg_data => l_msg_data );
1693 IF g1_debug_mode = 'Y' THEN
1694 pa_debug.g_err_stage:='assignment_Rev_amount :l_return_status'||l_x_return_status||'l_msg_data'||l_msg_data||'l_raw_revenue'||l_txn_raw_revenue||'l_bill_rate'||l_txn_bill_rate||'l_bill_markup'||l_txn_bill_markup;
1695 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1696 END IF;
1697
1698
1699 if NVL(p_rate_based_flag,'N') = 'N' then
1700 l_txn_bill_rate := NULL;
1701 l_txn_adjusted_bill_rate:=NULL;
1702 end if;
1703 /* Rasising the Billing Exception to pass the error values to the Main Api */
1704 IF l_x_return_status <> g_success THEN
1705 RAISE l_bill_api;
1706 END IF;
1707
1708 ELSE
1709
1710 /* Expenditure Type is checked for UOM determination and to calculate p_uom_flag */
1711 IF g1_debug_mode = 'Y' THEN
1712 pa_debug.g_err_stage:='========g_expenditure_type_tbl.COUNT'||g_expenditure_type_tbl.COUNT;
1713 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1714 END IF;
1715
1716 FOR i IN g_expenditure_type_tbl.FIRST .. g_expenditure_type_tbl.LAST LOOP
1717 IF p_expenditure_type =g_expenditure_type_tbl(i) THEN
1718 IF g1_debug_mode = 'Y' THEN
1719 pa_debug.g_err_stage:='expendirure Type is::::'|| g_expenditure_type_tbl(i) ;
1720 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1721 END IF;
1722 IF g_uom_tbl.EXISTS(i) THEN
1723 l_uom := g_uom_tbl(i);
1724 END IF;
1725 EXIT;
1726 END IF;
1727 END LOOP;
1728
1729 IF l_uom<>p_uom THEN
1730 l_uom_flag:=0 ;
1731 ELSE
1732 l_uom_flag:=1 ;
1733 END IF;
1734 IF g1_debug_mode = 'Y' THEN
1735 pa_debug.g_err_stage:='l_uom is::::::'|| l_uom||'p_uom '||p_uom||'l_uom_flag'||l_uom_flag ;
1736 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1737 END IF;
1738
1739
1740 IF g1_debug_mode = 'Y' THEN
1741 pa_debug.g_err_stage:='Before Calling pa_revenue.Non_Labor_Rev_amount';
1742 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1743 END IF;
1744
1745 pa_revenue.Non_Labor_Rev_amount(
1746 p_called_process =>l_called_process ,
1747 p_project_id => p_project_id,
1748 p_task_id => p_task_id,
1749 p_bill_rate_multiplier => p_bill_rate_multiplier,
1750 p_quantity => l_quantity,
1751 p_raw_cost => p_raw_cost,
1752 p_burden_cost => p_burden_cost,
1753 p_denom_raw_cost => p_raw_cost,
1754 p_denom_burdened_cost => p_burden_cost,
1755 p_expenditure_item_date => p_item_date,
1756 p_task_bill_rate_org_id => p_task_bill_rate_org_id ,
1757 p_project_bill_rate_org_id => p_project_bill_rate_org_id ,
1758 p_task_nl_std_bill_rate_sch_id => p_rev_task_nl_rate_sch_id,
1759 p_proj_nl_std_bill_rate_sch_id => p_rev_proj_nl_rate_sch_id,
1760 p_project_org_id => p_project_org_id,
1761 p_sl_function => l_sl_function,
1762 p_denom_currency_code => p_txn_currency_code,
1763 p_proj_func_currency => p_txn_currency_code,
1764 p_proj_func_burdened_cost => p_burden_cost,
1765 p_expenditure_type => p_expenditure_type,
1766 p_non_labor_resource => p_non_labor_resource,
1767 p_task_sch_date => p_task_sch_date ,
1768 p_project_sch_date => p_project_sch_date ,
1769 p_project_sch_discount => p_project_sch_discount,
1770 p_task_sch_discount => p_task_sch_discount,
1771 p_mcb_flag => NULL,--p_mcb_flag,
1772 p_uom_flag => l_uom_flag,
1773 p_non_labor_sch_type => p_non_labor_sch_type,
1774 p_project_type => p_project_type,
1775 p_exp_raw_cost => p_raw_cost,
1776 p_raw_cost_rate => p_raw_cost_rate,
1777 p_incurred_by_organz_id => p_incurred_by_organz_id,
1778 p_override_to_organz_id => p_override_to_organz_id,
1779 px_exp_func_curr_code => l_exp_func_Curr_code,
1780 x_raw_revenue => l_txn_raw_revenue,
1781 x_rev_curr_code => l_txn_Curr_code,
1782 x_bill_rate => l_txn_bill_rate,
1783 x_adjusted_bill_rate => l_txn_adjusted_bill_rate , --4038485
1784 x_markup_percentage => l_txn_bill_markup,
1785 x_return_status => l_x_return_status,
1786 x_msg_count => l_msg_count,
1787 x_msg_data => l_msg_data);
1788 IF g1_debug_mode = 'Y' THEN
1789 pa_debug.g_err_stage:='Non_Labor_Rev_amount :l_return_status'||l_x_return_status||'l_msg_data'||l_msg_data||'l_raw_revenue'||l_txn_raw_revenue||'l_bill_rate'||l_txn_bill_rate||'l_bill_markup'||l_txn_bill_markup;
1790 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1791 END IF;
1792 if NVL(p_rate_based_flag,'N') = 'N' then
1793 l_txn_bill_rate := NULL;
1794 l_txn_adjusted_bill_rate:=NULL;
1795 end if;
1796 /* Raising the Billing Exception to pass the error values to the Main Api */
1797 IF l_x_return_status <> g_success THEN
1798 RAISE l_bill_api;
1799 END IF;
1800
1801 END IF;/*IF p_resource_class='PEOPLE' THEN */
1802 END IF;/* End if of l_raw_revenue IS NULL THEN*/
1803
1804 IF g1_debug_mode = 'Y' THEN
1805 pa_debug.g_err_stage:='Get_Plan_Actual_Rev_Rates :l_raw_revenue'||l_txn_raw_revenue||'l_bill_rate:'||l_txn_bill_rate||'l_bill_markup:'||l_txn_bill_markup||'l_txn_adjusted_bill_rate:'||l_txn_adjusted_bill_rate;
1806 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1807 END IF;
1808 x_raw_revenue :=l_txn_raw_revenue;
1809 x_bill_rate :=NVL(l_txn_adjusted_bill_rate,l_txn_bill_rate) ;--4038485
1810 x_bill_markup_percentage :=l_txn_bill_markup;
1811 x_txn_curr_code :=l_txn_curr_code;
1812 x_return_status :=l_x_return_status;
1813
1814 EXCEPTION
1815 WHEN l_bill_api THEN
1816 x_raw_revenue :=NULL;
1817 x_bill_rate :=NULL;
1818 x_bill_markup_percentage :=NULL;
1819 x_txn_curr_code :=NULL;
1820 x_return_status := g_error;
1821 x_msg_data := l_msg_data;
1822 x_msg_count := l_msg_count;
1823
1824 IF g1_debug_mode = 'Y' THEN
1825 pa_debug.g_err_stage:='Billing api is throwing error :p_project_id'||p_project_id||'p_task_id'||p_task_id;
1826 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1827 END IF;
1828
1829 WHEN l_rate_based_no_quantity THEN
1830 x_raw_revenue :=NULL;
1831 x_bill_rate :=NULL;
1832 x_bill_markup_percentage :=NULL;
1833 x_txn_curr_code :=NULL;
1834 x_return_status := g_error;
1835 x_msg_data := 'PA_EX_QTY_EXIST';
1836 x_msg_count := 1;
1837
1838 IF g1_debug_mode = 'Y' THEN
1839 pa_debug.g_err_stage:='Quantity is not passed to a rate based tranaction to the Get_Plan_Actual_Rev_Rates call';
1840 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1841 END IF;
1842 WHEN l_invalid_currency THEN
1843 x_raw_revenue :=NULL;
1844 x_bill_rate :=NULL;
1845 x_bill_markup_percentage :=NULL;
1846 x_txn_curr_code :=NULL;
1847 x_return_status := g_error;
1848 x_msg_data := 'PA_INVALID_DENOM_CURRENCY';
1849 x_msg_count := 1;
1850
1851 IF g1_debug_mode = 'Y' THEN
1852 pa_debug.g_err_stage:='Override Currency is not passed to a rate based tranaction to the Get_Plan_Actual_Rev_Rates call';
1853 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1854 END IF;
1855
1856
1857 END Get_Plan_Actual_Rev_Rates;
1858
1859 --
1860 -- Procedure : Get_plan_res_class_rates
1861 -- Purpose :This procedure will calculate the raw revenue and bill amount from one of the 12
1862 -- criterias on the basis of passed parameters
1863 -- Parameters :
1864 --
1865
1866 PROCEDURE Get_plan_res_class_rates (
1867 p_project_type IN VARCHAR2,
1868 p_project_id IN NUMBER,
1869 p_task_id IN NUMBER DEFAULT NULL,
1870 p_person_id IN NUMBER,
1871 p_job_id IN NUMBER DEFAULT NULL,
1872 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
1873 p_use_planning_rates_flag IN VARCHAR2 DEFAULT 'N', /* Rate using Actual Rates */
1874 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
1875 p_uom IN VARCHAR2, /* Planning UOM */
1876 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calculation use in Resource Class Sch carrying out*/
1877 p_rev_res_class_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Resource Class*/
1878 p_cost_res_class_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on Resource Class*/
1879 p_plan_burden_cost_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on burdening for planning*/
1880 p_cost_rate_multiplier IN NUMBER DEFAULT 1, /* p_bill_rate_multiplier or p_cost_rate_multiplier */
1881 p_bill_rate_multiplier IN NUMBER DEFAULT 1, /* p_bill_rate_multiplier or p_cost_rate_multiplier */
1882 p_quantity IN NUMBER,
1883 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
1884 p_schedule_type IN VARCHAR2 DEFAULT NULL, /* REVENUE OR COST OR NULL , will calculate both cost and Revenue*/
1885 p_project_org_id IN NUMBER,
1886 p_incurred_by_organz_id IN NUMBER,
1887 p_override_to_organz_id IN NUMBER,
1888 p_expenditure_org_id IN NUMBER,
1889 p_nlr_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
1890 p_override_trxn_cost_rate IN NUMBER DEFAULT NULL, /*p_override_trxn_cost_rate for costing */
1891 p_override_burden_cost_rate IN NUMBER DEFAULT NULL, /*override burden multiplier and p_raw_cost is not null calculate x_burden_cost */
1892 p_override_trxn_bill_rate IN NUMBER DEFAULT NULL, /*p_override_trxn_bill_rate or for billing */
1893 p_override_txn_currency_code IN VARCHAR2 DEFAULT NULL,
1894 p_txn_currency_code IN VARCHAR2,
1895 p_raw_cost IN NUMBER,
1896 p_burden_cost IN NUMBER DEFAULT NULL,
1897 p_raw_revenue IN NUMBER DEFAULT NULL,
1898 p_system_linkage IN VARCHAR2,
1899 p_expenditure_type IN VARCHAR2,
1900 x_bill_rate OUT NOCOPY NUMBER,
1901 x_cost_rate OUT NOCOPY NUMBER,
1902 x_burden_cost_rate OUT NOCOPY NUMBER,
1903 x_burden_multiplier OUT NOCOPY NUMBER,
1904 x_raw_cost OUT NOCOPY NUMBER,
1905 x_burden_cost OUT NOCOPY NUMBER,
1906 x_raw_revenue OUT NOCOPY NUMBER,
1907 x_bill_markup_percentage OUT NOCOPY NUMBER,
1908 x_cost_markup_percentage OUT NOCOPY NUMBER,
1909 x_cost_txn_curr_code OUT NOCOPY VARCHAR2,
1910 x_rev_txn_curr_code OUT NOCOPY VARCHAR2,
1911 x_raw_cost_rejection_code OUT NOCOPY VARCHAR2,
1912 x_burden_cost_rejection_code OUT NOCOPY VARCHAR2,
1913 x_revenue_rejection_code OUT NOCOPY VARCHAR2,
1914 x_cost_ind_compiled_set_id OUT NOCOPY NUMBER,
1915 x_return_status OUT NOCOPY VARCHAR2,
1916 x_msg_count OUT NOCOPY NUMBER,
1917 x_msg_data OUT NOCOPY VARCHAR2)
1918
1919 IS
1920
1921 l_x_return_status VARCHAR2(20):=g_success; -- store the return status
1922 l_bill_rate NUMBER:=NULL;
1923 l_cost_rate NUMBER:=NULL;
1924 l_adjust_amount NUMBER :=null;
1925 l_markup NUMBER :=null;
1926 l_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE;
1927 l_raw_revenue NUMBER :=null; -- store the raw revenue
1928 l_true BOOLEAN := FALSE;
1929 l_txn_raw_revenue NUMBER :=null; -- store the raw revenue trans. curr.
1930 l_raw_cost number := null;
1931 l_burden_cost number := null;
1932 l_txn_currency_code pa_bill_rates_all.rate_currency_code%TYPE;
1933 l_inter_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE;--4194214
1934 l_burden_cost_rate NUMBER:=NULL;
1935 l_cost_ind_compiled_set_id NUMBER;
1936 l_burd_organization_id NUMBER;
1937 l_x_msg_data VARCHAR2(1000);
1938 l_x_msg_count NUMBER;
1939 l_schedule_type VARCHAR2(60);
1940 l_burd_sch_id NUMBER;
1941 l_burd_sch_rev_id NUMBER;
1942 l_burd_sch_fixed_date DATE;
1943 l_burd_sch_cost_base VARCHAR2(1000);
1944 l_burd_sch_cp_structure VARCHAR2(1000);
1945 l_txn_burden_multiplier NUMBER :=null;
1946 l_cost_return_status VARCHAR2(1):=g_success;
1947 l_cost_msg_data VARCHAR2(1000);
1948 l_txn_burden_cost_rate NUMBER :=null;
1949 l_cost_txn_curr_code VARCHAR2(50);
1950 l_txn_burden_cost NUMBER :=null;
1951 l_txn_raw_cost NUMBER :=null;
1952 l_txn_cost_rate NUMBER:=NULL;
1953 l_burden_cost_rejection_code VARCHAR2(50);
1954 l_bill_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE;
1955 l_inter_return_status VARCHAR2(1):=null;
1956 l_calling_mode VARCHAR2(20):=null;
1957 l_override_organization_id NUMBER;
1958 l_ovr_return_status VARCHAR2(20):=g_success;
1959 l_ovr_msg_count NUMBER;
1960 l_ovr_msg_data VARCHAR2(1000);
1961
1962 BEGIN
1963
1964 pa_debug.init_err_stack('PA_PLAN_REVENUE.Get_plan_res_class_rates');
1965
1966 IF g1_debug_mode = 'Y' THEN
1967 pa_debug.g_err_stage:='Validating input parameters:->p_schedule_type'||p_schedule_type||'p_rate_based_flag'||p_rate_based_flag||'p_uom'||p_uom||'p_raw_cost'||p_raw_cost||'p_raw_revenue'||p_raw_revenue;
1968 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
1969 END IF;
1970 IF g1_debug_mode = 'Y' THEN
1971 pa_debug.g_err_stage:='Validating input parameters:->p_use_planning_rates_flag '||p_use_planning_rates_flag ||'p_plan_burden_cost_sch_id '||p_plan_burden_cost_sch_id ;
1972 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
1973 END IF;
1974 IF g1_debug_mode = 'Y' THEN
1975 pa_debug.g_err_stage:='Validating input parameters:->p_nlr_organization_id '||p_nlr_organization_id ||'p_expenditure_org_id '||p_expenditure_org_id ||'p_override_to_organz_id '||p_override_to_organz_id;
1976 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
1977 END IF;
1978
1979 IF g1_debug_mode = 'Y' THEN
1980 pa_debug.g_err_stage:='Validating input parameters:->p_rev_res_class_rate_sch_id '||p_rev_res_class_rate_sch_id ||'p_incurred_by_organz_id'||p_incurred_by_organz_id ;
1981 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
1982 END IF;
1983 /* Assigning the parameters to local variables */
1984 l_burden_cost :=p_burden_cost;
1985 l_raw_cost := p_raw_cost;
1986 l_cost_rate := p_override_trxn_cost_rate;
1987 l_bill_rate := p_override_trxn_bill_rate;
1988 l_override_organization_id :=p_override_to_organz_id;
1989 l_txn_curr_code :=p_txn_currency_code; --bug#4317221
1990 if p_use_planning_rates_flag ='Y' then
1991 l_calling_mode :='PLAN_RATES';
1992 else
1993 l_calling_mode :='ACTUAL_RATES';
1994 end if;
1995
1996 l_raw_revenue :=p_raw_revenue;
1997 /* Validating all the required Parameters */
1998 IF p_resource_class IS NULL OR p_item_date IS NULL
1999 OR p_uom IS NULL THEN
2000 IF g1_debug_mode = 'Y' THEN
2001 pa_debug.g_err_stage:='validating Get_plan_res_class_rates: either p_resource_class or p_item_date or p_uom is null ';
2002 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2003 END IF;
2004 RAISE l_insufficeient_param;
2005 ELSE
2006 null;
2007 END IF;/* Check for mandatory parameters done */
2008
2009 /* First computing the raw_cost if p_schedule_type is passed COST or NULL
2010 If COST is passed then it'll compute only Costing but if passed nll it'll
2011 compute both Costing as well as Revenue */
2012 IF g1_debug_mode = 'Y' THEN
2013 pa_debug.g_err_stage:='Calculating Costing data';
2014 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2015 END IF;
2016
2017 IF l_override_organization_id is NULL and p_resource_class = 'PEOPLE' Then
2018 IF g1_debug_mode = 'Y' THEN
2019 pa_debug.g_err_stage:='Calling pa_cost.Override_exp_organization api';
2020 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2021 end if;
2022 BEGIN
2023 pa_cost.Override_exp_organization
2024 (P_item_date => p_item_date
2025 ,P_person_id => p_person_id
2026 ,P_project_id => p_project_id
2027 ,P_incurred_by_organz_id => p_incurred_by_organz_id
2028 ,P_Expenditure_type => p_expenditure_type
2029 ,X_overr_to_organization_id => l_override_organization_id
2030 ,X_return_status => l_ovr_return_status
2031 ,X_msg_count => l_ovr_msg_count
2032 ,X_msg_data => l_ovr_msg_data
2033 );
2034 EXCEPTION
2035 WHEN OTHERS THEN
2036 IF g1_debug_mode = 'Y' THEN
2037 pa_debug.g_err_stage:='pa_cost.Override_exp_organization is throwing When Others'||SUBSTR(SQLERRM,1,250);
2038 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
2039 END IF;
2040 END;
2041
2042 IF g1_debug_mode = 'Y' THEN
2043 pa_debug.g_err_stage:='Return status of pa_cost.Override_exp_organization ['||l_ovr_return_status||
2044 ']msgData['||l_ovr_msg_data||']OverideOrg['||l_override_organization_id||']' ;
2045 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2046 end if;
2047
2048 End If;
2049
2050 IF NVL(p_schedule_type,'COST')='COST' THEN
2051 --13.02
2052 IF l_cost_rate IS NOT NULL AND l_raw_cost IS NULL THEN
2053 IF NVL(p_rate_based_flag,'N')='Y' THEN
2054 l_raw_cost :=l_cost_rate*p_quantity;
2055 END IF;
2056 END IF;
2057 --13.02
2058 IF l_raw_cost IS NULL THEN
2059 IF p_rate_based_flag ='Y' THEN
2060 IF p_quantity IS NULL THEN
2061 IF g1_debug_mode = 'Y' THEN
2062 pa_debug.g_err_stage:='validating Get_plan_res_class_rates: p_quantity is required for rate based ';
2063 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2064 END IF;
2065 RAISE l_rate_based_no_quantity;
2066 END IF;
2067 END IF;
2068 BEGIN
2069 DECLARE
2070 l_class_org_rate NUMBER:=NULL;
2071 l_class_org_markup NUMBER:=NULL;
2072 l_class_org_uom pa_bill_rates_all.bill_rate_unit%TYPE :=NULL;
2073 l_class_org_rate_curr_code pa_bill_rates_all.rate_currency_code%TYPE :=NULL;
2074 l_class_org_return_status VARCHAR2(20):= g_success;
2075 l_class_org_return_data VARCHAR2(30);
2076 l_class_org_return_count NUMBER;
2077 l_item_date DATE := p_item_date;
2078 l_res_class_org_id NUMBER := NVL (l_override_organization_id ,NVL(p_incurred_by_organz_id,p_project_organz_id));
2079 BEGIN
2080 IF g1_debug_mode = 'Y' THEN
2081 pa_debug.g_err_stage:='Callling Get_Res_Class_Hierarchy_Rate:p_project_id'||p_project_id||'p_task_id'||p_task_id;
2082 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2083 END IF;
2084 Get_Res_Class_Hierarchy_Rate( p_res_class_rate_sch_id => p_cost_res_class_rate_sch_id,
2085 p_item_date => l_item_date,
2086 p_org_id => p_project_org_id ,
2087 p_resource_class_code => p_resource_class,
2088 p_res_class_org_id => l_res_class_org_id,
2089 x_rate => l_class_org_rate ,
2090 x_markup_percentage => l_class_org_markup,
2091 x_uom => l_class_org_uom,
2092 x_rate_currency_code => l_class_org_rate_curr_code,
2093 x_return_status => l_class_org_return_status,
2094 x_msg_count => l_class_org_return_count,
2095 x_msg_data => l_class_org_return_data);
2096
2097 /* Checking the status if the above proc has return no rate then no need to call the block*/
2098 IF l_class_org_return_status = g_success THEN
2099
2100 DECLARE
2101
2102 CURSOR C_std_res_class_sch_cost IS
2103 SELECT DECODE (p_uom,'DOLLARS',1, DECODE(p_uom,l_class_org_uom,l_class_org_rate * NVL(p_cost_rate_multiplier,1),null)) b_rate,
2104 DECODE (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(p_quantity ,l_class_org_rate_curr_code)
2105 ,DECODE(p_uom,l_class_org_uom,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_class_org_rate * NVL(p_cost_rate_multiplier,1)
2106 * p_quantity, l_class_org_rate_curr_code),null)
2107 ) r_cost,
2108 l_class_org_rate_curr_code rate_currency_code
2109 FROM dual;
2110
2111
2112 BEGIN
2113 -- Opening cursor and fetching row
2114 OPEN C_std_res_class_sch_cost ;
2115 -- Assigning the Calculated raw revenue/adjusted to the local variable
2116 FETCH C_std_res_class_sch_cost INTO l_cost_rate,l_raw_cost,l_txn_curr_code;
2117 CLOSE C_std_res_class_sch_cost ;
2118
2119 IF g1_debug_mode = 'Y' THEN
2120 pa_debug.g_err_stage:='1002 cost rate : ' || l_cost_rate || 'Raw Cost : '
2121 || l_raw_cost || 'currency_code : ' || l_txn_curr_code;
2122 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2123 END IF;
2124 END;/*End of declare of cursor declaration for res_class_org_id*/
2125 END IF;/* End of Status check of Hierarcy Rate Procedure */
2126 END;/* End of proceduer call BEGIN */
2127
2128
2129 /* Getting the Rates and Revenue if the Rate is not present at the Resource Class and Res_class_organization_id Level */
2130 IF l_raw_cost IS NULL THEN
2131 DECLARE
2132 CURSOR C_std_res_class_sch_cost IS
2133 SELECT DECODE (p_uom,'DOLLARS', 1,DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_cost_rate_multiplier,1),null)) b_rate,
2134 DECODE (p_uom,'DOLLARS', PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(p_quantity ,b.rate_currency_code)
2135 ,DECODE(p_uom,b.bill_rate_unit,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_cost_rate_multiplier,1)
2136 * p_quantity, b.rate_currency_code),null)
2137 ) r_cost,
2138 DECODE(p_uom,'DOLLARS', b.rate_currency_code,b.rate_currency_code) rate_currency_code
2139 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2140 WHERE sch.bill_rate_sch_id=p_cost_res_class_rate_sch_id
2141 AND sch.bill_rate_sch_id=b.bill_rate_sch_id
2142 AND b.resource_class_code = p_resource_class
2143 AND sch.schedule_type = 'RESOURCE_CLASS'
2144 AND b.res_class_organization_id IS NULL
2145 AND trunc(p_item_date)
2146 BETWEEN trunc(b.start_date_active)
2147 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2148
2149
2150 BEGIN
2151
2152 -- Opening cursor and fetching row
2153 FOR Rec_std_res_class_sch_cost IN C_std_res_class_sch_cost LOOP
2154 -- Checking if the cursor is returning more than one row then error out
2155 IF (l_true) THEN
2156 RAISE l_more_than_one_row_excep;
2157 ELSE
2158 l_true := TRUE;
2159 END IF;
2160
2161 -- Assigning the Calculated raw cost to the local variable
2162 l_cost_rate := Rec_std_res_class_sch_cost.b_rate;
2163 l_raw_cost := Rec_std_res_class_sch_cost.r_cost;
2164 l_txn_curr_code := Rec_std_res_class_sch_cost.rate_currency_code;
2165 END LOOP;
2166 IF g1_debug_mode = 'Y' THEN
2167 pa_debug.g_err_stage:='1002 cost rate : ' || l_cost_rate || 'Raw Cost : '
2168 || l_raw_cost || 'currency_code : ' || l_txn_curr_code;
2169 pa_debug.write('Get_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2170 END IF;
2171
2172 EXCEPTION
2173 WHEN l_more_than_one_row_excep THEN
2174 x_raw_cost:= NULL;
2175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2176 x_msg_count := 1;
2177 x_msg_data := 'TOO_MANY_ROWS';
2178 x_raw_cost_rejection_code :='TOO_MANY_ROWS';
2179 IF g1_debug_mode = 'Y' THEN
2180 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2181 pa_debug.write('Get_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2182 END IF;
2183 RAISE;
2184 END;/*End of decalre cursor*/
2185
2186 IF ( l_raw_cost IS NULL) THEN
2187 RAISE l_no_cost;
2188 END IF;
2189
2190 IF g1_debug_mode = 'Y' THEN
2191 pa_debug.g_err_stage:='9999 l_cost_rate: ' || l_cost_rate||
2192 'raw_cost : ' ||l_raw_cost || 'currency_code : ' || l_txn_curr_code;
2193 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2194 END IF;
2195
2196 END IF;/* End of IF l_raw_cost */
2197
2198 EXCEPTION
2199 WHEN l_no_cost THEN
2200 x_raw_cost:= NULL;
2201 l_inter_return_status := FND_API.G_RET_STS_ERROR;
2202 x_msg_data:= 'PA_FCST_NO_COST_RATE';
2203 x_raw_cost_rejection_code := 'PA_FCST_NO_COST_RATE';
2204 x_msg_count := 1;
2205 IF g1_debug_mode = 'Y' THEN
2206 pa_debug.g_err_stage:='No Cost Rate Exists:p_project_id'||p_project_id||'p_task_id'||p_task_id;
2207 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2208 END IF;
2209
2210 WHEN OTHERS THEN
2211 x_raw_cost:= NULL;
2212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2213 x_msg_count := 1;
2214 x_msg_data := SUBSTR(SQLERRM,1,30);
2215 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
2216
2217 IF g1_debug_mode = 'Y' THEN
2218 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2219 pa_debug.write('Get_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2220 END IF;
2221
2222 RAISE;
2223
2224 END;/* End of Begin for Revenue Scedule Type */
2225 ELSE
2226 IF NVL(p_quantity,1) <>0 THEN
2227 l_cost_rate:=NVL(p_override_trxn_cost_rate,l_raw_cost/p_quantity);
2228 ELSE
2229 l_cost_rate:=p_override_trxn_cost_rate;
2230 END IF;
2231
2232 END IF;/* End of l_raw_cost */
2233
2234 END IF; /*End if of l_schedule_type=Cost */
2235
2236
2237 /*==========================================================================================*/
2238 /* If from the above raw_cost is computed and it needs to compute the burden cost on it
2239 then the folowing code will be executed */
2240 IF l_burden_cost is NOT NULL Then
2241
2242 --assigning override burden cost/or derived burden cost from Actual
2243 IF g1_debug_mode = 'Y' THEN
2244 pa_debug.g_err_stage := 'Assignging override burden cost values to out params';
2245 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2246 END IF;
2247 l_txn_burden_cost :=l_burden_cost;
2248 IF (p_quantity <> 0 ) THEN
2249 l_txn_burden_cost_rate := l_burden_cost / NVL(p_quantity,1) ;
2250 END IF;
2251 IF NVL(l_raw_cost,0)<>0 THEN
2252 l_txn_burden_multiplier := (l_burden_cost/l_raw_cost)-1;
2253 END IF;
2254
2255 ElsIf ( p_override_burden_cost_rate is NULL and l_burden_cost is NULL
2256 and l_raw_cost is NOT NULL and l_cost_rate is NOT NULL
2257 and pa_cost1.check_proj_burdened(p_project_type,p_project_id) = 'Y' ) Then
2258 l_burd_organization_id := NVL(l_override_organization_id, NVl(p_incurred_by_organz_id ,p_nlr_organization_id));
2259 IF g1_debug_mode = 'Y' THEN
2260 pa_debug.g_err_stage := 'calling pa_cost1.Get_burden_sch_details fro burden cost';
2261 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2262 END IF;
2263
2264 begin
2265
2266 pa_cost1.Get_burden_sch_details
2267 (p_calling_mode =>l_calling_mode
2268 ,p_exp_item_id => NULL
2269 ,p_trxn_type => NULL
2270 ,p_project_type => p_project_type
2271 ,p_project_id => p_project_id
2272 ,p_task_id => p_task_id
2273 ,p_exp_organization_id => l_burd_organization_id
2274 ,p_expenditure_type => p_expenditure_type
2275 ,p_schedule_type => 'COST'
2276 ,p_exp_item_date => p_item_date
2277 ,p_trxn_curr_code => NVL(l_txn_curr_code,p_override_txn_currency_code)
2278 ,p_burden_schedule_id => p_plan_burden_cost_sch_id
2279 ,x_schedule_id => l_burd_sch_id
2280 ,x_sch_revision_id => l_burd_sch_rev_id
2281 ,x_sch_fixed_date => l_burd_sch_fixed_date
2282 ,x_cost_base => l_burd_sch_cost_base
2283 ,x_cost_plus_structure => l_burd_sch_cp_structure
2284 ,x_compiled_set_id => l_cost_ind_compiled_set_id
2285 ,x_burden_multiplier => l_txn_burden_multiplier
2286 ,x_return_status => l_cost_return_status
2287 ,x_error_msg_code => l_cost_msg_data
2288 );
2289
2290 exception
2291 when others then
2292
2293 l_cost_return_status := 'E';
2294 l_cost_msg_data := 'GET_BURDEN_DETAILS: '||substr(sqlerrm,1,30);
2295 end;
2296 If ( l_cost_return_status <> g_success OR l_txn_burden_multiplier is NULL ) Then
2297 IF g1_debug_mode = 'Y' THEN
2298 pa_debug.g_err_stage := 'Error while Calculating burden costs';
2299 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2300 END IF;
2301 l_burden_cost_rejection_code := l_cost_msg_data;
2302
2303 -------------------------------------------------------
2304 -- Get Burden Cost and rate from Raw Cost and Quantity.
2305 -------------------------------------------------------
2306 ELSE
2307
2308 l_txn_burden_cost := pa_currency.round_trans_currency_amt(
2309 l_raw_cost * NVL(l_txn_burden_multiplier,0),NVL(l_txn_curr_code,p_override_txn_currency_code )) +
2310 l_raw_cost ;
2311
2312 /*bug3749153 no need to compute the burden cost rate if it equals raw_cost_rate */
2313 If l_txn_burden_cost = l_raw_cost Then
2314 l_txn_burden_cost_rate := l_cost_rate;
2315 Else
2316 IF (p_quantity <> 0 ) THEN
2317 l_txn_burden_cost_rate := l_txn_burden_cost / NVL(P_quantity, 1) ;
2318 END IF;
2319 End if;
2320 end if;
2321 Elsif p_override_burden_cost_rate is NOT NULL and l_burden_cost is NULL Then
2322 IF g1_debug_mode = 'Y' THEN
2323 pa_debug.g_err_stage := 'Calculating burden cost based on override burden multiplier ';
2324 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2325 END IF;
2326
2327 l_txn_burden_cost := p_quantity * p_override_burden_cost_rate;
2328 l_txn_burden_cost_rate := p_override_burden_cost_rate;
2329 IF NVL(l_raw_cost,0)<>0 THEN
2330 l_txn_burden_multiplier := ( l_txn_burden_cost / l_raw_cost)-1;
2331 END IF;
2332
2333 ElsIF l_raw_cost IS NOT NULL THEN
2334 --copy the raw cost to the burden costs
2335 IF g1_debug_mode = 'Y' THEN
2336 pa_debug.g_err_stage := 'Copying raw costs to burden costs';
2337 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2338 END IF;
2339 l_txn_burden_cost := l_raw_cost;
2340 l_txn_burden_cost_rate := l_cost_rate;
2341 l_txn_burden_multiplier := 0;
2342
2343 End IF;
2344
2345 /* Burden Cost Calucltaion complete */
2346 /*==========================================================================================*/
2347 l_inter_txn_curr_code :=NVL(l_txn_curr_code,p_override_txn_currency_code) ; --4194214
2348 /* Assigning out all the Cost and Burden Cost Parameters */
2349 x_raw_cost := l_raw_cost ;
2350 x_cost_rate := l_cost_rate ;
2351 x_burden_cost := l_txn_burden_cost;
2352 x_burden_cost_rate := l_txn_burden_cost_rate ;
2353 x_burden_multiplier := l_txn_burden_multiplier;
2354 x_cost_txn_curr_code := l_inter_txn_curr_code;--NVL(l_txn_curr_code,p_override_txn_currency_code) ;--4194214
2355 x_return_status := NVL(l_inter_return_status,l_x_return_status);
2356 l_inter_return_status := NVL(l_inter_return_status,l_x_return_status);
2357 x_burden_cost_rejection_code:= l_burden_cost_rejection_code;
2358
2359 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
2360 IF g1_debug_mode = 'Y' THEN
2361 pa_debug.g_err_stage := 'Going to calculate revenue:->p_schedule_type'||p_schedule_type||'l_raw_revenue'||l_raw_revenue||'l_raw_cost'||l_raw_cost||'l_txn_burden_cost'||l_txn_burden_cost;
2362 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2363 END IF;
2364
2365 IF NVL(p_schedule_type,'REVENUE')='REVENUE' THEN
2366 IF p_system_linkage='BTC' THEN
2367 l_raw_cost:= NVL(l_txn_burden_cost,p_burden_cost);
2368 ELSE
2369 l_raw_cost:= NVL(l_raw_cost,p_raw_cost);
2370 END IF;
2371 /* This code is added beacuse if you choose to calculate only REVENUE then for Non-Rate based transaction it might happen that the
2372 l_raw_cost is null since we have not called the COSTing api to compute the cost so just assigning p_quantity to l_raw_cost
2373 as l_raw_cost is used in the revenue calculation if its non-Rate based transaction */
2374 IF p_schedule_type ='REVENUE' AND l_raw_cost IS NULL THEN
2375 IF NVL(p_rate_based_flag,'N') ='N' THEN
2376 l_raw_cost:=p_quantity;
2377 END IF;
2378 IF g1_debug_mode = 'Y' THEN
2379 pa_debug.g_err_stage := 'p_quanity is directly getting converted to l_raw_cost for only REVENUE mode:l_raw_cost'||l_raw_cost;
2380 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2381 END IF;
2382 END IF;
2383
2384 IF l_raw_revenue IS NULL THEN
2385 l_true := FALSE;
2386 IF NVL(p_rate_based_flag,'N') ='N' AND l_raw_cost IS NULL THEN
2387 RAISE l_no_cost;
2388 END IF;
2389 IF p_rate_based_flag ='Y' THEN
2390 IF p_quantity IS NULL THEN
2391 IF g1_debug_mode = 'Y' THEN
2392 pa_debug.g_err_stage:='validating Get_plan_res_class_rates: p_quantity is required for rate based ';
2393 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2394 END IF;
2395 RAISE l_rate_based_no_quantity;
2396 END IF;
2397 END IF;
2398
2399
2400
2401 IF l_bill_rate IS NOT NULL AND l_raw_revenue IS NULL THEN
2402 l_raw_revenue :=l_bill_rate*p_quantity;
2403 END IF;
2404
2405
2406 BEGIN
2407 DECLARE
2408 l_class_org_rate NUMBER:=NULL;
2409 l_class_org_markup NUMBER:=NULL;
2410 l_class_org_uom pa_bill_rates_all.bill_rate_unit%TYPE :=NULL;
2411 l_class_org_rate_curr_code pa_bill_rates_all.rate_currency_code%TYPE :=NULL;
2412 l_class_org_return_status VARCHAR2(1):= g_success;
2413 l_class_org_return_data VARCHAR2(30);
2414 l_class_org_return_count NUMBER;
2415 l_item_date DATE := p_item_date;
2416 -- bug 9167821 skkoppul : added to get the override org, incurred org or project org in the order if null
2417 l_res_class_org_id NUMBER := NVL(l_override_organization_id ,NVL(p_incurred_by_organz_id,p_project_organz_id));
2418
2419 BEGIN
2420 IF l_raw_revenue IS NULL THEN --4108291
2421 Get_Res_Class_Hierarchy_Rate(p_res_class_rate_sch_id => p_rev_res_class_rate_sch_id,
2422 p_item_date => l_item_date,
2423 p_org_id => p_project_org_id ,
2424 p_resource_class_code => p_resource_class,
2425 p_res_class_org_id => l_res_class_org_id, -- bug 9167821:replaced p_project_organz_id
2426 x_rate => l_class_org_rate ,
2427 x_markup_percentage => l_class_org_markup,
2428 x_uom => l_class_org_uom,
2429 x_rate_currency_code => l_class_org_rate_curr_code,
2430 x_return_status => l_class_org_return_status,
2431 x_msg_count => l_class_org_return_count,
2432 x_msg_data => l_class_org_return_data);
2433 /* Checking the status*/
2434 IF l_class_org_return_status = g_success THEN
2435
2436 /* Bug 5048677. Added to check if uom are same then if rate is null
2437 then compute revenue using mark up or use rate*/
2438
2439 DECLARE
2440 CURSOR C_std_res_class_sch_rev IS
2441 SELECT DECODE (p_uom,'DOLLARS',NULL,DECODE(p_uom,l_class_org_uom,l_class_org_rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2442 DECODE (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +l_class_org_markup)
2443 * (l_raw_cost / 100), l_inter_txn_curr_code) --4194214
2444 ,DECODE(p_uom,l_class_org_uom,decode(l_class_org_rate,null,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +l_class_org_markup) * (l_raw_cost / 100), l_inter_txn_curr_code),
2445 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_class_org_rate * NVL(p_bill_rate_multiplier,1)
2446 * p_quantity, l_class_org_rate_curr_code))
2447 ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +l_class_org_markup)
2448 * (l_raw_cost / 100), l_inter_txn_curr_code) --4194214
2449 )
2450 ) r_revenue,
2451 DECODE(p_uom,'DOLLARS',l_inter_txn_curr_code,DECODE(p_uom,l_class_org_uom,decode(l_class_org_rate,null,l_inter_txn_curr_code,l_class_org_rate_curr_code),l_inter_txn_curr_code) ) rate_currency_code,
2452 DECODE (p_uom,'DOLLARS',l_class_org_markup,DECODE(p_uom,l_class_org_uom,decode(l_class_org_rate,null,l_class_org_markup,NULL),l_class_org_markup)) markup
2453 FROM dual;
2454 BEGIN
2455 -- Opening cursor and fetching row
2456 OPEN C_std_res_class_sch_rev;
2457 -- Assigning the Calculated raw revenue/adjusted to the local variable
2458 FETCH C_std_res_class_sch_rev INTO l_bill_rate ,l_raw_revenue ,l_bill_txn_curr_code,l_markup ;
2459 CLOSE C_std_res_class_sch_rev;
2460
2461 IF g1_debug_mode = 'Y' THEN
2462 pa_debug.g_err_stage:='1002 bill rate : ' || l_bill_rate || 'Revenue : '
2463 || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2464 pa_debug.write('Get_plan_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2465 END IF;
2466
2467 END;/*End of declare of cursor declaration for C_std_res_class_sch_rev*/
2468
2469 END IF;/* End of Status check for Get_hierarchy_rate */
2470 END IF;/* IF l_raw_revenue IS NULL THEN 4108291*/
2471 END;/* End of proceduer call BEGIN */
2472
2473 l_txn_raw_revenue := l_raw_revenue;
2474
2475 /* Getting the Rates and Revenue if the Rate is not present at the Resource Class and Res_class_organization_id Level */
2476 IF l_txn_raw_revenue IS NULL THEN
2477 /* Bug 5048677. Added to check if uom are same then if rate is null
2478 then compute revenue using mark up or use rate*/
2479 DECLARE
2480 CURSOR C_std_res_class_sch_rev IS
2481 SELECT DECODE (p_uom,'DOLLARS',NULL,DECODE(p_uom,b.bill_rate_unit, b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2482 DECODE (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2483 * (l_raw_cost / 100), l_inter_txn_curr_code)--4194214
2484 ,DECODE(p_uom,b.bill_rate_unit,decode(b.rate,null,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), l_inter_txn_curr_code),
2485 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
2486 * p_quantity, b.rate_currency_code))
2487 ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2488 * (l_raw_cost / 100), l_inter_txn_curr_code)--4194214
2489
2490 )
2491 ) r_revenue,
2492 DECODE(p_uom,'DOLLARS',l_inter_txn_curr_code,DECODE(p_uom,b.bill_rate_unit,decode(b.rate,null,l_inter_txn_curr_code,b.rate_currency_code),l_inter_txn_curr_code) ) rate_currency_code,
2493 -- DECODE(p_uom,'DOLLARS', b.rate_currency_code,b.rate_currency_code ) rate_currency_code, --4194214
2494 DECODE (p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,decode(b.rate,null,b.markup_percentage,NULL),b.markup_percentage)) markup
2495 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2496 WHERE sch.bill_rate_sch_id=p_rev_res_class_rate_sch_id
2497 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2498 AND b.resource_class_code = p_resource_class
2499 AND sch.schedule_type = 'RESOURCE_CLASS'
2500 AND b.res_class_organization_id IS NULL
2501 AND trunc(p_item_date)
2502 BETWEEN trunc(b.start_date_active)
2503 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2504
2505 BEGIN
2506 -- Opening cursor and fetching row
2507 FOR Rec_std_res_class_sch_rev IN C_std_res_class_sch_rev LOOP
2508 -- Checking if the cursor is returning more than one row then error out
2509 IF (l_true) THEN
2510 RAISE l_more_than_one_row_excep;
2511 ELSE
2512 l_true := TRUE;
2513 END IF;
2514
2515 -- Assigning the Calculated raw revenue/adjusted to the local variable
2516 l_bill_rate := Rec_std_res_class_sch_rev.b_rate;
2517 l_raw_revenue := Rec_std_res_class_sch_rev.r_revenue;
2518 l_bill_txn_curr_code := Rec_std_res_class_sch_rev.rate_currency_code;
2519 l_markup := Rec_std_res_class_sch_rev.markup;
2520
2521 END LOOP;
2522
2523 IF g1_debug_mode = 'Y' THEN
2524 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
2525 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2526 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2527 END IF;
2528 EXCEPTION
2529 WHEN l_more_than_one_row_excep THEN
2530 x_raw_revenue:= NULL;
2531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2532 x_msg_count := 1;
2533 x_msg_data := 'TOO_MANY_ROWS';
2534 x_revenue_rejection_code := 'TOO_MANY_ROWS';
2535
2536 IF g1_debug_mode = 'Y' THEN
2537 pa_debug.g_err_stage:='Too many Rows';
2538 pa_debug.write('Get_plan_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2539 END IF;
2540
2541 RAISE;
2542
2543 END;/*End of decalre cursor*/
2544
2545 l_txn_raw_revenue := l_raw_revenue;
2546
2547 IF ( l_txn_raw_revenue IS NULL) THEN
2548 RAISE l_no_revenue;
2549 END IF;
2550
2551 END IF;/* End of IF l_txn_raw_revenue */
2552
2553 x_raw_revenue := l_txn_raw_revenue ;
2554 x_bill_rate := l_bill_rate ;
2555 x_bill_markup_percentage := l_markup ;
2556 x_rev_txn_curr_code := l_bill_txn_curr_code ;
2557 x_return_status := l_x_return_status;
2558
2559 IF g1_debug_mode = 'Y' THEN
2560 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
2561 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2562 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2563 END IF;
2564
2565 EXCEPTION
2566 WHEN l_no_revenue THEN
2567 RAISE l_no_revenue;
2568 END;/* End of Begin for Revenue Scedule Type */
2569 ELSE
2570
2571 IF NVL(p_quantity,1)<>0 THEN
2572 l_bill_rate:=NVL(p_override_trxn_bill_rate,l_raw_revenue/p_quantity);
2573 ELSE
2574 l_bill_rate:=p_override_trxn_bill_rate;
2575 END IF;
2576
2577 END IF;/* end if of IF l_raw_revenue I NULL */
2578 ELSE
2579 l_bill_rate:=p_override_trxn_bill_rate;
2580 END IF; /*End if of l_schedule_type=Revenue */
2581 x_raw_revenue := l_raw_revenue ;
2582 x_bill_rate := l_bill_rate ;
2583 x_bill_markup_percentage := l_markup ;
2584 x_rev_txn_curr_code := NVL(l_bill_txn_curr_code,p_override_txn_currency_code) ;
2585 x_return_status := l_x_return_status;
2586
2587 pa_debug.reset_err_stack;
2588 EXCEPTION
2589 WHEN l_insufficeient_param THEN
2590 IF g1_debug_mode = 'Y' THEN
2591 pa_debug.g_err_stage:='All the Required parameters are not passes to Resource Schedule';
2592 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
2593 END IF;
2594 x_return_status := g_ERROR;
2595 x_msg_count := 1;
2596 x_msg_data := 'PA_FCST_INSUFFICIENT_PARA';
2597 x_revenue_rejection_code := 'PA_FCST_INSUFFICIENT_PARA';
2598 x_raw_cost_rejection_code := 'PA_FCST_INSUFFICIENT_PARA';
2599 x_burden_cost_rejection_code := 'PA_FCST_INSUFFICIENT_PARA';
2600 x_raw_revenue := NULL;
2601 x_raw_cost := NULL;
2602 x_bill_rate := NULL;
2603 x_cost_rate := NULL;
2604
2605 pa_debug.reset_err_stack;
2606
2607 WHEN l_rate_based_no_quantity THEN
2608 IF g1_debug_mode = 'Y' THEN
2609 pa_debug.g_err_stage:='Quantity is required for a rate based transaction';
2610 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
2611 END IF;
2612 x_return_status := g_ERROR;
2613 x_msg_count := 1;
2614 x_msg_data := 'PA_EX_QTY_EXIST';
2615 x_revenue_rejection_code := 'PA_EX_QTY_EXIST';
2616 x_raw_cost_rejection_code := 'PA_EX_QTY_EXIST';
2617 x_raw_revenue := NULL;
2618 x_raw_cost := NULL;
2619 x_bill_rate := NULL;
2620 x_cost_rate := NULL;
2621
2622 pa_debug.reset_err_stack;
2623 WHEN l_no_cost THEN
2624 x_raw_revenue := NULL;
2625 x_raw_cost := NULL;
2626 x_bill_rate := NULL;
2627 x_cost_rate := NULL;
2628 x_return_status := NVL(l_inter_return_status,g_ERROR);
2629 x_msg_data := 'PA_FCST_NO_COST_RATE';
2630 x_raw_cost_rejection_code := 'PA_FCST_NO_COST_RATE';
2631 x_burden_cost_rejection_code := 'PA_FCST_NO_COST_RATE';
2632 x_revenue_rejection_code := 'PA_FCST_NO_COST_RATE';
2633 x_msg_count := 1;
2634 IF g1_debug_mode = 'Y' THEN
2635 pa_debug.g_err_stage:='No Cost Rate Exists:p_project_id'||p_project_id||'p_task_id'||p_task_id;
2636 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2637 END IF;
2638
2639 pa_debug.reset_err_stack;
2640 WHEN l_no_revenue THEN
2641 x_raw_revenue := NULL;
2642 x_bill_rate := NULL;
2643 x_return_status := NVL(l_inter_return_status,g_ERROR);
2644 x_msg_data := 'PA_FCST_NO_BILL_RATE';
2645 x_revenue_rejection_code := 'PA_FCST_NO_BILL_RATE';
2646 x_msg_count := 1;
2647 IF g1_debug_mode = 'Y' THEN
2648 pa_debug.g_err_stage:='No Bill Rate Exists:p_project_id'||p_project_id||'p_task_id'||p_task_id;
2649 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2650 END IF;
2651
2652 pa_debug.reset_err_stack;
2653
2654 WHEN OTHERS THEN
2655 x_raw_revenue := NULL;
2656 x_raw_cost := NULL;
2657 x_bill_rate := NULL;
2658 x_cost_rate := NULL;
2659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2660 x_msg_count := 1;
2661 x_msg_data := SUBSTR(SQLERRM,1,30);
2662 x_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
2663 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
2664 x_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
2665
2666 IF g1_debug_mode = 'Y' THEN
2667 pa_debug.g_err_stage:='SQLERROR ' || SQLCODE;
2668 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,5);
2669 END IF;
2670
2671 pa_debug.reset_err_stack;
2672 RAISE;
2673
2674
2675
2676 END Get_plan_res_class_rates ;
2677
2678
2679 /**************************************************************************************************************************
2680 ***************************************************************************************************************************
2681 *********************************** FOR DOOSAN ITERATION 2 PLANNING RATES *************************************************
2682 ***************************************************************************************************************************
2683 **************************************************************************************************************************/
2684 PROCEDURE Get_Plan_plan_Rev_Rates (
2685 p_project_id IN NUMBER,
2686 p_task_id IN NUMBER DEFAULT NULL,
2687 p_person_id IN NUMBER,
2688 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
2689 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
2690 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
2691 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
2692 p_uom IN VARCHAR2, /* Planning UOM */
2693 p_system_linkage IN VARCHAR2,
2694 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
2695 p_plan_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on job for planning*/
2696 p_plan_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on emp for planning*/
2697 p_plan_rev_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on non labor for planning*/
2698 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
2699 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
2700 p_quantity IN NUMBER, /* required param for People/Equipment Class */
2701 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
2702 p_project_org_id IN NUMBER, /* Project Org Id */
2703 p_project_type IN VARCHAR2,
2704 p_expenditure_type IN VARCHAR2,
2705 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
2706 p_override_to_organz_id IN NUMBER, /* Override Org Id */
2707 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
2708 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
2709 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
2710 p_NLR_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
2711 p_revenue_override_rate IN NUMBER DEFAULT NULL,
2712 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
2713 p_txn_currency_code IN VARCHAR2 DEFAULT NULL,
2714 p_raw_cost IN NUMBER,
2715 p_burden_cost IN NUMBER DEFAULT NULL,
2716 p_raw_revenue IN NUMBER DEFAULT NULL,
2717 x_bill_rate OUT NOCOPY NUMBER,
2718 x_raw_revenue OUT NOCOPY NUMBER,
2719 x_bill_markup_percentage OUT NOCOPY NUMBER,
2720 x_txn_curr_code OUT NOCOPY VARCHAR2,
2721 x_return_status OUT NOCOPY VARCHAR2,
2722 x_msg_data OUT NOCOPY VARCHAR2,
2723 x_msg_count OUT NOCOPY NUMBER
2724 )
2725 IS
2726
2727
2728 l_x_return_status VARCHAR2(20):=g_success;
2729 l_msg_count NUMBER;
2730 l_msg_data VARCHAR2(1000);
2731 l_txn_curr_code VARCHAR2(30);
2732 l_rev_curr_code VARCHAR2(30);
2733 l_override_cost NUMBER:=NULL;
2734 l_txn_bill_rate pa_bill_rates_all.rate%TYPE:=NULL;
2735 l_bill_rate pa_bill_rates_all.rate%TYPE:=NULL;
2736 l_txn_bill_markup NUMBER:=NULL;
2737 l_markup NUMBER:=NULL;
2738 l_raw_revenue NUMBER:=NULL;
2739 l_txn_raw_revenue NUMBER;
2740 l_exp_func_Curr_code VARCHAR2(30);
2741 l_raw_cost NUMBER;
2742 l_true BOOLEAN :=FALSE;
2743 l_bill_txn_curr_code VARCHAR2(30);
2744 -- Added for bug 5952621
2745 l_job_group_id pa_std_bill_rate_schedules_all.job_group_id%TYPE;
2746 l_dest_job_id pa_bill_rates_all.job_id%TYPE;
2747
2748 /* Bug 8407306 */
2749 l_job_bl_rate NUMBER;
2750 l_emp_bl_rate NUMBER;
2751 l_r_curr_code VARCHAR2(30);
2752
2753
2754 -- Modified the select of all the four cursors for bug 5079161
2755 CURSOR C_std_emp_sch_rev IS
2756 SELECT
2757 /* DECODE (p_uom,'DOLLARS',NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
2758 DECODE (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2759 * (l_raw_cost / 100), p_txn_currency_code)
2760 ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
2761 * p_quantity, b.rate_currency_code)
2762 ) r_revenue,
2763 DECODE(p_uom,'DOLLARS', p_txn_currency_code ,b.rate_currency_code ) rate_currency_code,
2764 DECODE (p_uom,'DOLLARS',b.markup_percentage,NULL) markup
2765 */
2766 DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2767 DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2768 * (l_raw_cost / 100),p_txn_currency_code),
2769 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
2770 b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
2771 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
2772 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
2773 )
2774 ) r_revenue,
2775 DECODE(p_uom,'DOLLARS',p_txn_currency_code,DECODE(p_uom,b.bill_rate_unit, DECODE(b.rate,NULL,p_txn_currency_code,b.rate_currency_code),
2776 p_txn_currency_code) ) rate_currency_code, -- 4194214
2777 DECODE(p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,b.markup_percentage,NULL),b.markup_percentage)) markup
2778 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2779 WHERE sch.bill_rate_sch_id=p_plan_rev_emp_rate_sch_id
2780 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2781 AND sch.schedule_type = 'EMPLOYEE'
2782 AND b.person_id = p_person_id
2783 AND trunc(p_item_date)
2784 BETWEEN trunc(b.start_date_active)
2785 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2786
2787 /* bug 3712539 removed the join to p_person_id*/
2788 CURSOR C_std_job_sch_rev IS
2789 SELECT
2790 DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2791 DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2792 * (l_raw_cost / 100),p_txn_currency_code),
2793 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
2794 b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
2795 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
2796 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
2797 )
2798 ) r_revenue,
2799 DECODE(p_uom,'DOLLARS',p_txn_currency_code,DECODE(p_uom,b.bill_rate_unit, DECODE(b.rate,NULL,p_txn_currency_code,b.rate_currency_code),
2800 p_txn_currency_code) ) rate_currency_code, -- 4194214
2801 DECODE(p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,b.markup_percentage,NULL),b.markup_percentage)) markup
2802 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b --, per_assignments_f pa
2803 WHERE sch.bill_rate_sch_id = p_plan_rev_job_rate_sch_id
2804 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2805 AND sch.schedule_type = 'JOB'
2806 AND b.job_id = l_dest_job_id -- p_job_id Modified for bug 5952621
2807 AND trunc(p_item_date)
2808 BETWEEN trunc(b.start_date_active)
2809 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2810
2811 CURSOR C_std_nl_nls_sch_rev IS
2812 SELECT
2813 DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2814 DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2815 * (l_raw_cost / 100),p_txn_currency_code),
2816 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
2817 b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
2818 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
2819 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
2820 )
2821 ) r_revenue,
2822 DECODE(p_uom,'DOLLARS',p_txn_currency_code,DECODE(p_uom,b.bill_rate_unit, DECODE(b.rate,NULL,p_txn_currency_code,b.rate_currency_code),
2823 p_txn_currency_code) ) rate_currency_code, -- 4194214
2824 DECODE(p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,b.markup_percentage,NULL),b.markup_percentage)) markup
2825 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2826 WHERE sch.bill_rate_sch_id = p_plan_rev_nlr_rate_sch_id
2827 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2828 AND b.expenditure_type = p_expenditure_type
2829 AND b.non_labor_resource = p_non_labor_resource
2830 AND sch.schedule_type = 'NON-LABOR'
2831 AND trunc(p_item_date)
2832 BETWEEN trunc(b.start_date_active)
2833 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2834
2835 CURSOR C_std_nl_exp_sch_rev IS
2836 SELECT
2837 DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2838 DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2839 * (l_raw_cost / 100),p_txn_currency_code),
2840 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
2841 b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
2842 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
2843 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
2844 )
2845 ) r_revenue,
2846 DECODE(p_uom,'DOLLARS',p_txn_currency_code,DECODE(p_uom,b.bill_rate_unit, DECODE(b.rate,NULL,p_txn_currency_code,b.rate_currency_code),
2847 p_txn_currency_code) ) rate_currency_code, -- 4194214
2848 DECODE(p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,b.markup_percentage,NULL),b.markup_percentage)) markup
2849 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2850 WHERE sch.bill_rate_sch_id = p_plan_rev_nlr_rate_sch_id
2851 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2852 AND b.expenditure_type = p_expenditure_type
2853 AND sch.schedule_type = 'NON-LABOR'
2854 AND b.non_labor_resource IS NULL
2855 AND trunc(p_item_date)
2856 BETWEEN trunc(b.start_date_active)
2857 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2858
2859 Cursor c_emp_rate_proj_level IS /* 8407306 */
2860 select rate,rate_currency_code
2861 from PA_EMP_BILL_RATE_OVERRIDES
2862 where project_id = p_project_id
2863 and person_id = p_person_id;
2864
2865
2866 Cursor c_job_rate_proj_level IS /* 8407306 */
2867 select rate,rate_currency_code
2868 from pa_job_bill_rate_overrides
2869 where project_id = p_project_id
2870 and job_id = p_job_id;
2871
2872
2873 BEGIN
2874 IF g1_debug_mode = 'Y' THEN
2875 pa_debug.g_err_stage:='Validating all the input parameters';
2876 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2877 END IF;
2878
2879 IF p_system_linkage='BTC' THEN
2880 l_override_cost := p_burden_cost;
2881 ELSE
2882 l_override_cost := p_raw_cost;
2883 END IF;
2884 l_txn_raw_revenue :=p_raw_revenue;
2885 l_raw_cost := p_raw_cost; -- Added for bug 5039918
2886
2887 IF p_rate_based_flag ='Y' AND p_quantity IS NULL AND NVL(l_override_cost,0)=0 THEN
2888 IF g1_debug_mode = 'Y' THEN
2889 pa_debug.g_err_stage:='Validating Get_plan_plan_rev_Rates:p_quantity is required for rate based';
2890 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2891 END IF;
2892 RAISE l_rate_based_no_quantity;
2893 END IF;
2894 IF p_revenue_override_rate IS NOT NULL AND p_override_currency_code IS NULL THEN
2895 IF g1_debug_mode = 'Y' THEN
2896 pa_debug.g_err_stage:='Validating Get_plan_plan_rev_Rates:p_override_currency_code is required if passing any overrides';
2897 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2898 END IF;
2899 RAISE l_invalid_currency;
2900 END IF;
2901 /* If revenue Override rate is not null compute the raw_revenue based on the override rate and the p_quantity or rawCost */
2902 IF p_revenue_override_rate IS NOT NULL AND l_txn_raw_revenue IS NULL THEN
2903
2904 SELECT p_revenue_override_rate b_rate,
2905 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((p_revenue_override_rate * p_quantity), p_override_currency_code) r_revenue,
2906 p_override_currency_code
2907 INTO l_txn_bill_rate,l_raw_revenue,l_txn_curr_code
2908 FROM dual;
2909
2910
2911 l_txn_raw_revenue :=l_raw_revenue;
2912
2913 END IF;/* End of check for p_revenue_override_rate */
2914 /* If in the above case the Raw Revenue is null then go for calling
2915 actual internal api of billing to compute the raw Revenue */
2916
2917 IF l_txn_raw_revenue IS NULL THEN
2918 /* Deriving Planning rates based on planning rate schedules for emp n job and non labor*/
2919 IF p_resource_class='PEOPLE' THEN
2920 IF g1_debug_mode = 'Y' THEN
2921 pa_debug.g_err_stage:='Deriving Planning rates based on planning rate schedules for emp and job';
2922 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2923 END IF;
2924 /* Checking for Employee based Rates*/
2925 BEGIN
2926 /* bug8407306 */
2927 open c_emp_rate_proj_level;
2928 fetch c_emp_rate_proj_level
2929 into l_emp_bl_rate,l_r_curr_code;
2930 close c_emp_rate_proj_level;
2931
2932 -- Opening cursor and fetching row
2933 l_true := FALSE;
2934 FOR Rec_std_emp_sch_rev IN C_std_emp_sch_rev LOOP
2935 -- Checking if the cursor is returning more than one row then error out
2936 IF (l_true) THEN
2937 RAISE l_more_than_one_row_excep;
2938 ELSE
2939 l_true := TRUE;
2940 END IF;
2941
2942 -- Assigning the Calculated raw revenue/adjusted to the local variable
2943 l_bill_rate := Rec_std_emp_sch_rev.b_rate;
2944 l_raw_revenue := Rec_std_emp_sch_rev.r_revenue;
2945 l_bill_txn_curr_code := Rec_std_emp_sch_rev.rate_currency_code;
2946 l_markup := Rec_std_emp_sch_rev.markup;
2947
2948 END LOOP;
2949
2950 /* bug8407306 */
2951 IF (l_emp_bl_rate is not NULL AND l_bill_rate IS null) THEN
2952 l_bill_rate := l_emp_bl_rate;
2953 l_bill_txn_curr_code := l_r_curr_code;
2954 END IF;
2955
2956 IF g1_debug_mode = 'Y' THEN
2957 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
2958 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2959 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,2);
2960 END IF;
2961 EXCEPTION
2962 WHEN l_more_than_one_row_excep THEN
2963 x_raw_revenue:= NULL;
2964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2965 x_msg_count := 1;
2966 x_msg_data := 'TOO_MANY_ROWS';
2967
2968
2969 IF g1_debug_mode = 'Y' THEN
2970 pa_debug.g_err_stage:='Too many Rows';
2971 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2972 END IF;
2973
2974 RAISE;
2975 END;/*End of decalre cursor*/
2976
2977 IF l_raw_revenue IS NULL THEN
2978 /* Checking for Job based Rates if not by Employee based*/
2979 BEGIN
2980
2981 /* bug8407306 */
2982 open c_job_rate_proj_level;
2983 fetch c_job_rate_proj_level
2984 into l_job_bl_rate,l_r_curr_code;
2985 close c_job_rate_proj_level;
2986
2987 -- Opening cursor and fetching row
2988 l_true := FALSE;
2989
2990 /* Start of changes for bug 5952621 */
2991
2992 -- Start Changes for bug 6050924
2993 -- Handling scenario when p_plan_rev_job_rate_sch_id is null then select raises
2994 -- NO_DATA_FOUND exception. l_dest_job_id is set to p_job_id
2995 BEGIN
2996 select job_group_id
2997 into l_job_group_id
2998 from pa_std_bill_rate_schedules_all
2999 where bill_rate_sch_id = p_plan_rev_job_rate_sch_id;
3000 EXCEPTION
3001 When NO_DATA_FOUND Then
3002 l_job_group_id := NULL;
3003 END;
3004
3005 IF l_job_group_id IS NOT NULL THEN
3006 l_dest_job_id := pa_cross_business_grp.IsMappedToJob(p_job_id, l_job_group_id);
3007 ELSE
3008 l_dest_job_id := p_job_id;
3009 END IF;
3010 -- End Changes for bug 6050924
3011
3012 /* End of changes for bug 5952621 */
3013
3014 FOR Rec_std_job_sch_rev IN C_std_job_sch_rev LOOP
3015 -- Checking if the cursor is returning more than one row then error out
3016 IF (l_true) THEN
3017 RAISE l_more_than_one_row_excep;
3018 ELSE
3019 l_true := TRUE;
3020 END IF;
3021
3022 -- Assigning the Calculated raw revenue/adjusted to the local variable
3023 l_bill_rate := Rec_std_job_sch_rev.b_rate;
3024 l_raw_revenue := Rec_std_job_sch_rev.r_revenue;
3025 l_bill_txn_curr_code := Rec_std_job_sch_rev.rate_currency_code;
3026 l_markup := Rec_std_job_sch_rev.markup;
3027
3028 END LOOP;
3029
3030 /* bug8407306 */
3031 IF (l_job_bl_rate is not NULL AND l_bill_rate IS null) THEN
3032 l_bill_rate := l_job_bl_rate;
3033 l_bill_txn_curr_code := l_r_curr_code;
3034 END IF;
3035
3036 IF g1_debug_mode = 'Y' THEN
3037 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
3038 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
3039 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3040 END IF;
3041 EXCEPTION
3042 WHEN l_more_than_one_row_excep THEN
3043 x_raw_revenue:= NULL;
3044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3045 x_msg_count := 1;
3046 x_msg_data := 'TOO_MANY_ROWS';
3047
3048
3049 IF g1_debug_mode = 'Y' THEN
3050 pa_debug.g_err_stage:='Too many Rows';
3051 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
3052 END IF;
3053
3054 RAISE;
3055 END;/*End of decalre cursor*/
3056
3057 END IF;/*IF l_raw_revenue IS NULL THEN*/
3058
3059 ELSE /* Else of p_resource_class='PEOPLE' */
3060
3061 /* Checking for Non Labor based Rates first for Non Labor Resources*/
3062 BEGIN
3063 -- Opening cursor and fetching row
3064 l_true := FALSE;
3065 FOR Rec_std_nl_sch_rev IN C_std_nl_nls_sch_rev LOOP
3066 -- Checking if the cursor is returning more than one row then error out
3067 IF (l_true) THEN
3068 RAISE l_more_than_one_row_excep;
3069 ELSE
3070 l_true := TRUE;
3071 END IF;
3072
3073 -- Assigning the Calculated raw revenue/adjusted to the local variable
3074 l_bill_rate := Rec_std_nl_sch_rev.b_rate;
3075 l_raw_revenue := Rec_std_nl_sch_rev.r_revenue;
3076 l_bill_txn_curr_code := Rec_std_nl_sch_rev.rate_currency_code;
3077 l_markup := Rec_std_nl_sch_rev.markup;
3078
3079 END LOOP;
3080 IF g1_debug_mode = 'Y' THEN
3081 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
3082 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
3083 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3084 END IF;
3085 EXCEPTION
3086 WHEN l_more_than_one_row_excep THEN
3087 x_raw_revenue:= NULL;
3088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3089 x_msg_count := 1;
3090 x_msg_data := 'TOO_MANY_ROWS';
3091
3092
3093 IF g1_debug_mode = 'Y' THEN
3094 pa_debug.g_err_stage:='Too many Rows in non labor based on Non Labor Resources';
3095 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
3096 END IF;
3097
3098 RAISE;
3099 END;/*End of decalre cursor*/
3100
3101 IF l_raw_revenue IS NULL THEN
3102 /* Checking for Non Labor based Rates first for Non Labor Resources*/
3103 BEGIN
3104 -- Opening cursor and fetching row
3105 l_true := FALSE;
3106 FOR Rec_std_nl_sch_rev IN C_std_nl_exp_sch_rev LOOP
3107 -- Checking if the cursor is returning more than one row then error out
3108 IF (l_true) THEN
3109 RAISE l_more_than_one_row_excep;
3110 ELSE
3111 l_true := TRUE;
3112 END IF;
3113
3114 -- Assigning the Calculated raw revenue/adjusted to the local variable
3115 l_bill_rate := Rec_std_nl_sch_rev.b_rate;
3116 l_raw_revenue := Rec_std_nl_sch_rev.r_revenue;
3117 l_bill_txn_curr_code := Rec_std_nl_sch_rev.rate_currency_code;
3118 l_markup := Rec_std_nl_sch_rev.markup;
3119
3120 END LOOP;
3121 IF g1_debug_mode = 'Y' THEN
3122 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
3123 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
3124 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3125 END IF;
3126 EXCEPTION
3127 WHEN l_more_than_one_row_excep THEN
3128 x_raw_revenue:= NULL;
3129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3130 x_msg_count := 1;
3131 x_msg_data := 'TOO_MANY_ROWS';
3132
3133
3134 IF g1_debug_mode = 'Y' THEN
3135 pa_debug.g_err_stage:='Too many Rows in non labor based on expenditure Type';
3136 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
3137 END IF;
3138
3139 RAISE;
3140 END;/*End of decalre cursor*/
3141
3142 END IF;/* If l_raw_revenue IS NULL */
3143
3144
3145 IF g1_debug_mode = 'Y' THEN
3146 pa_debug.g_err_stage:='Deriving Planning rates based on planning rate schedules for Non Labor';
3147 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3148 END IF;
3149 END IF;
3150 END IF ;/* End of l_txn_raw_revenue IS NULL */
3151
3152
3153 x_raw_revenue :=NVL(l_txn_raw_revenue,l_raw_revenue);
3154 x_bill_rate := l_bill_rate ;
3155 x_bill_markup_percentage := l_markup ;
3156 x_txn_curr_code := NVL(l_bill_txn_curr_code,p_override_currency_code) ;
3157 x_return_status := l_x_return_status;
3158
3159
3160 EXCEPTION
3161 WHEN l_invalid_currency THEN
3162 x_raw_revenue :=NULL;
3163 x_bill_rate :=NULL;
3164 x_bill_markup_percentage :=NULL;
3165 x_txn_curr_code :=NULL;
3166 x_return_status := g_error;
3167 x_msg_data := 'PA_INVALID_DENOM_CURRENCY';
3168 x_msg_count := 1;
3169
3170 IF g1_debug_mode = 'Y' THEN
3171 pa_debug.g_err_stage:='Override Currency is not passed to a rate based tranaction to the Get_Plan_Actual_Rev_Rates call';
3172 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3173 END IF;
3174 WHEN l_rate_based_no_quantity THEN
3175 x_raw_revenue :=NULL;
3176 x_bill_rate :=NULL;
3177 x_bill_markup_percentage :=NULL;
3178 x_txn_curr_code :=NULL;
3179 x_return_status := g_error;
3180 x_msg_data := 'PA_EX_QTY_EXIST';
3181 x_msg_count := 1;
3182
3183 IF g1_debug_mode = 'Y' THEN
3184 pa_debug.g_err_stage:='Quantity is not passed to a rate based tranaction to the Get_Plan_Actual_Rev_Rates call';
3185 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3186 END IF;
3187
3188 END Get_Plan_plan_Rev_Rates;
3189
3190
3191
3192 PROCEDURE Get_plan_plan_Rates ( p_project_id IN NUMBER,
3193 p_task_id IN NUMBER DEFAULT NULL,
3194 p_top_task_id IN NUMBER DEFAULT NULL, /* for costing top task Id */
3195 p_person_id IN NUMBER,
3196 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
3197 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
3198 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
3199 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
3200 p_uom IN VARCHAR2, /* Planning UOM */
3201 p_system_linkage IN VARCHAR2,
3202 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
3203 p_plan_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on job for planning*/
3204 p_plan_cost_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on job for planning*/
3205 p_plan_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on emp for planning*/
3206 p_plan_cost_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For cost Rate Calculations based on emp for planning*/
3207 p_plan_rev_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on non labor for planning*/
3208 p_plan_cost_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on non labor for planning*/
3209 p_plan_burden_cost_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on burdening for planning*/
3210 p_calculate_mode IN VARCHAR2 DEFAULT 'COST_REVENUE' ,/* useed for calculating either only Cost(COST),only Revenue(REVENUE) or both Cost and Revenue(COST_REVENUE) */
3211 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
3212 p_cost_rate_multiplier IN NUMBER DEFAULT NULL ,
3213 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
3214 p_quantity IN NUMBER, /* required param for People/Equipment Class */
3215 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
3216 p_project_org_id IN NUMBER, /* Project Org Id */
3217 p_project_type IN VARCHAR2,
3218 p_expenditure_type IN VARCHAR2,
3219 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
3220 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
3221 p_override_to_organz_id IN NUMBER, /* Override Org Id */
3222 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
3223 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
3224 p_nlr_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
3225 p_inventory_item_id IN NUMBER DEFAULT NULL, /* Passed for Inventoty Items */
3226 p_BOM_resource_Id IN NUMBER DEFAULT NULL, /* Passed for BOM Resource Id */
3227 P_mfc_cost_type_id IN NUMBER DEFAULT 0, /* Manufacturing cost api */
3228 P_item_category_id IN NUMBER DEFAULT NULL, /* Manufacturing cost api */
3229 p_mfc_cost_source IN NUMBER DEFAULT 1,
3230 p_cost_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to costing internal api.*/
3231 p_revenue_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to billing internal api.*/
3232 p_override_burden_cost_rate IN NUMBER DEFAULT NULL, /*override burden multiplier and p_raw_cost is not null calculate x_burden_cost */
3233 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
3234 p_txn_currency_code IN VARCHAR2 DEFAULT NULL, /* if not null, amounts to be returned in this currency only else in x_txn_curr_code*/
3235 p_raw_cost IN NUMBER, /*If p_raw_cost is only passed,return the burden multiplier, burden_cost */
3236 p_burden_cost IN NUMBER DEFAULT NULL,
3237 p_raw_revenue IN NUMBER DEFAULT NULL,
3238 x_bill_rate OUT NOCOPY NUMBER,
3239 x_cost_rate OUT NOCOPY NUMBER,
3240 x_burden_cost_rate OUT NOCOPY NUMBER,
3241 x_burden_multiplier OUT NOCOPY NUMBER,
3242 x_raw_cost OUT NOCOPY NUMBER,
3243 x_burden_cost OUT NOCOPY NUMBER,
3244 x_raw_revenue OUT NOCOPY NUMBER,
3245 x_bill_markup_percentage OUT NOCOPY NUMBER,
3246 x_cost_txn_curr_code OUT NOCOPY VARCHAR2,
3247 x_rev_txn_curr_code OUT NOCOPY VARCHAR2, /* x_txn_currency_code for Labor and x_rev_curr_code for non labor */
3248 x_raw_cost_rejection_code OUT NOCOPY VARCHAR2,
3249 x_burden_cost_rejection_code OUT NOCOPY VARCHAR2,
3250 x_revenue_rejection_code OUT NOCOPY VARCHAR2,
3251 x_cost_ind_compiled_set_id OUT NOCOPY NUMBER,
3252 x_return_status OUT NOCOPY VARCHAR2,
3253 x_msg_data OUT NOCOPY VARCHAR2,
3254 x_msg_count OUT NOCOPY NUMBER
3255 )
3256 IS
3257 l_raw_cost NUMBER :=NULL;
3258 l_burden_cost NUMBER :=NULL;
3259 l_raw_revenue NUMBER:=NULL;
3260 l_x_return_status VARCHAR2(2):= g_success;
3261 l_cost_msg_count NUMBER;
3262 l_cost_msg_data VARCHAR2(1000);
3263 l_bill_msg_count NUMBER;
3264 l_bill_msg_data VARCHAR2(1000);
3265 l_called_process VARCHAR2(40);
3266 l_txn_curr_code VARCHAR2(30);
3267 l_trxn_curr_code VARCHAR2(30);
3268 l_cost_txn_curr_code VARCHAR2(30);
3269 l_rev_txn_curr_code VARCHAR2(30);
3270 l_rev_curr_code VARCHAR2(30);
3271 l_txn_cost NUMBER:=NULL; /* to store the value of p_burden_cost or p_raw_cost */
3272 l_proj_nl_bill_rate_sch_id NUMBER;
3273 l_task_nl_bill_rate_sch_id NUMBER;
3274 l_txn_cost_rate NUMBER;
3275 l_txn_raw_cost_rate NUMBER;
3276 l_txn_burden_cost_rate NUMBER;
3277 l_txn_bill_rate NUMBER;
3278 l_txn_bill_markup NUMBER:=NULL;
3279 l_txn_raw_cost NUMBER;
3280 l_txn_burden_cost NUMBER;
3281 l_txn_raw_revenue NUMBER;
3282 l_sl_function NUMBER ;
3283 l_exp_func_Curr_code VARCHAR2(30);
3284 l_raw_cost_rate NUMBER ;
3285 l_burden_cost_rate NUMBER ;
3286 l_bill_rate NUMBER:=NULL;
3287 l_burden_multiplier NUMBER;
3288 l_raw_cost_rejection_code VARCHAR2(30);
3289 l_burden_cost_rejection_code VARCHAR2(30);
3290 l_cost_ind_compiled_set_id NUMBER;
3291 l_proj_cost_job_id NUMBER;
3292 l_expenditure_org_id NUMBER;
3293 l_uom_flag NUMBER(1) :=1;
3294
3295
3296 BEGIN
3297 l_raw_revenue := p_raw_revenue;
3298 l_raw_cost := p_raw_cost;
3299 l_burden_cost := p_burden_cost;
3300 IF upper(p_resource_class)='PEOPLE' THEN
3301 l_expenditure_org_id :=nvl(p_incurred_by_organz_id, p_override_to_organz_id );
3302 ELSE
3303 l_expenditure_org_id :=nvl(p_nlr_organization_id,p_override_to_organz_id );
3304 END IF;
3305
3306 IF p_system_linkage='BTC' THEN
3307 l_txn_cost := p_burden_cost;
3308 ELSE
3309 l_txn_cost := p_raw_cost;
3310 END IF;
3311 IF ((p_raw_cost IS NULL OR p_burden_cost IS NULL)
3312 AND p_calculate_mode IN ('COST','COST_REVENUE')) THEN
3313 IF g1_debug_mode = 'Y' THEN
3314 pa_debug.g_err_stage:='Before Calling PA_COST1.Get_Plan_actual_Cost_Rates in PLAN mode';
3315 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3316 END IF;
3317
3318 BEGIN
3319 PA_COST1.Get_Plan_Actual_Cost_Rates
3320 (p_calling_mode =>'PLAN_RATES'
3321 ,p_project_type =>p_project_type
3322 ,p_project_id =>p_project_id
3323 ,p_task_id =>p_task_id
3324 ,p_top_task_id =>p_top_task_id
3325 ,p_Exp_item_date =>p_item_date
3326 ,p_expenditure_type =>p_expenditure_type
3327 ,p_expenditure_OU =>p_expenditure_org_id
3328 ,p_project_OU =>p_project_org_id
3329 ,p_Quantity =>p_Quantity
3330 ,p_resource_class =>p_resource_class
3331 ,p_person_id =>p_person_id
3332 ,p_non_labor_resource =>p_non_labor_resource
3333 ,p_NLR_organization_id =>p_NLR_organization_id
3334 ,p_override_organization_id =>p_override_to_organz_id
3335 ,p_incurred_by_organization_id =>p_incurred_by_organz_id
3336 ,p_inventory_item_id =>p_inventory_item_id
3337 ,p_BOM_resource_id =>p_BOM_resource_id
3338 ,p_override_trxn_curr_code =>p_override_currency_code
3339 ,p_override_burden_cost_rate =>p_override_burden_cost_rate
3340 ,p_override_trxn_cost_rate =>p_cost_override_rate
3341 ,p_override_trxn_raw_cost =>p_raw_cost
3342 ,p_override_trxn_burden_cost =>p_burden_cost
3343 ,p_mfc_cost_type_id =>p_mfc_cost_type_id
3344 ,p_mfc_cost_source =>p_mfc_cost_source --check
3345 ,p_item_category_id =>p_item_category_id
3346 ,p_job_id =>p_job_id
3347 , p_plan_cost_job_rate_sch_id =>p_plan_cost_job_rate_sch_id
3348 , p_plan_cost_emp_rate_sch_id =>p_plan_cost_emp_rate_sch_id
3349 , p_plan_cost_nlr_rate_sch_id =>p_plan_cost_nlr_rate_sch_id
3350 , p_plan_cost_burden_sch_id =>p_plan_burden_cost_sch_id
3351 ,x_trxn_curr_code =>l_trxn_curr_code
3352 ,x_trxn_raw_cost =>l_txn_raw_cost
3353 ,x_trxn_raw_cost_rate =>l_txn_cost_rate
3354 ,x_trxn_burden_cost =>l_txn_burden_cost
3355 ,x_trxn_burden_cost_rate =>l_txn_burden_cost_rate
3356 ,x_burden_multiplier =>l_burden_multiplier
3357 ,x_cost_ind_compiled_set_id =>l_cost_ind_compiled_set_id
3358 ,x_raw_cost_rejection_code =>l_raw_cost_rejection_code
3359 ,x_burden_cost_rejection_code =>l_burden_cost_rejection_code
3360 ,x_return_status =>l_x_return_status
3361 ,x_error_msg_code =>l_cost_msg_data ) ;
3362
3363 IF g1_debug_mode = 'Y' THEN
3364 pa_debug.g_err_stage:='Called PA_COST1.Get_Plan_Actual_Cost_Rates:x_raw_cost_rejection_code'||l_raw_cost_rejection_code||'x_burden_cost_rejection_code'||l_burden_cost_rejection_code;
3365 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
3366 END IF;
3367 EXCEPTION
3368 WHEN OTHERS THEN
3369 x_msg_data := 'PA_COST1.Get_Plan_Actual_Cost_Rates:' || SUBSTR(SQLERRM,1,250);
3370 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
3371 x_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
3372 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
3373
3374 IF g1_debug_mode = 'Y' THEN
3375 pa_debug.g_err_stage:=' PA_COST1.Get_Plan_Actual_Cost_Rates is throwing When Others';
3376 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
3377 END IF;
3378 RAISE;
3379
3380 END;
3381 /* transferring the outout cost to one cost and checking if the costing API has computed Cost */
3382 IF p_system_linkage='BTC' THEN
3383 l_txn_cost := l_txn_burden_cost;
3384 ELSE
3385 l_txn_cost :=l_txn_raw_cost;
3386 END IF;
3387
3388
3389 ELSE
3390 /* If p_raw_cost and p_burden Cost are passed Costing API
3391 won't be called but the same value will be passed as it is */
3392 l_txn_raw_cost :=l_raw_cost ;
3393 l_txn_burden_cost :=l_burden_cost ;
3394 IF p_quantity <>0 THEN
3395 l_txn_cost_rate :=l_raw_cost/(NVL(p_quantity,1)) ;
3396 l_txn_burden_cost_rate :=l_burden_cost/(NVL(p_quantity,1)) ;
3397 END IF;
3398
3399 END IF;
3400
3401 /* Sending out all the out parametrs of Costing , This is send out here as even if the costing API has failed
3402 Revenue API will be called and revenue calculated if the required values are passed to the Billing API,
3403 though it'll pass the rejection code of Costing APi in the out parameters*/
3404 x_cost_rate := l_txn_cost_rate;
3405 x_burden_cost_rate := l_txn_burden_cost_rate;
3406 x_burden_multiplier := l_burden_multiplier ;
3407 x_raw_cost := l_txn_raw_cost;
3408 x_burden_cost := l_txn_burden_cost;
3409 x_cost_txn_curr_code := l_trxn_curr_code;
3410 x_raw_cost_rejection_code := l_raw_cost_rejection_code ;
3411 x_burden_cost_rejection_code := l_burden_cost_rejection_code;
3412 x_cost_ind_compiled_set_id := l_cost_ind_compiled_set_id;
3413 x_return_status := l_x_return_status ;
3414 x_msg_data := l_cost_msg_data ;
3415
3416 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
3417
3418 IF p_calculate_mode IN ('REVENUE','COST_REVENUE') THEN
3419 /* Calling the Billing Revenue calculation Api only if p_raw_revenue is null */
3420 IF l_raw_revenue IS NULL THEN
3421 /* Checking for Rate based whether quantity is entered else */
3422
3423 IF p_rate_based_flag ='Y' THEN
3424 null;
3425 ELSE
3426 IF NVL(l_txn_cost,0)=0 THEN
3427 /*4108291 added the beloe code to have same check as in get_plan_actual_rates to compute the revenue based on quanity
3428 if revenue override is passed*/
3429 IF p_quantity is NOT NULL and p_revenue_override_rate is not null then
3430 null;
3431 ELSE
3432 RAISE l_no_cost;
3433 END IF;
3434 END IF;
3435 END IF;
3436 IF g1_debug_mode = 'Y' THEN
3437 pa_debug.g_err_stage:='Calling Get_Plan_plan_Rev_Rates';
3438 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3439 END IF;
3440
3441 Get_Plan_plan_Rev_Rates (
3442 p_project_id => p_project_id ,
3443 p_task_id => p_task_id,
3444 p_person_id => p_person_id ,
3445 p_job_id => p_job_id,
3446 p_bill_job_grp_id => p_bill_job_grp_id,
3447 p_resource_class => p_resource_class,
3448 p_rate_based_flag => p_rate_based_flag,
3449 p_uom => p_uom,
3450 p_system_linkage => p_system_linkage,
3451 p_project_organz_id => p_project_organz_id,
3452 p_plan_rev_job_rate_sch_id => p_plan_rev_job_rate_sch_id ,
3453 p_plan_rev_emp_rate_sch_id => p_plan_rev_emp_rate_sch_id,
3454 p_plan_rev_nlr_rate_sch_id => p_plan_rev_nlr_rate_sch_id ,
3455 p_mcb_flag => p_mcb_flag,
3456 p_bill_rate_multiplier => p_bill_rate_multiplier ,
3457 p_quantity => p_quantity ,
3458 p_item_date => p_item_date,
3459 p_project_org_id => p_project_org_id ,
3460 p_project_type => p_project_type,
3461 p_expenditure_type => p_expenditure_type ,
3462 p_incurred_by_organz_id => p_incurred_by_organz_id ,
3463 p_override_to_organz_id => p_override_to_organz_id ,
3464 p_expenditure_org_id => l_expenditure_org_id, --p_expenditure_org_id ,
3465 p_planning_transaction_id => p_planning_transaction_id,
3466 p_non_labor_resource => p_non_labor_resource ,
3467 p_NLR_organization_id => p_NLR_organization_id ,
3468 p_revenue_override_rate => p_revenue_override_rate,
3469 p_override_currency_code => p_override_currency_code,
3470 p_txn_currency_code => l_trxn_curr_code ,
3471 p_raw_cost => l_txn_raw_cost,
3472 p_burden_cost => l_txn_burden_cost,
3473 p_raw_revenue => l_raw_revenue,
3474 x_bill_rate => l_txn_bill_rate,
3475 x_raw_revenue => l_txn_raw_revenue,
3476 x_bill_markup_percentage => l_txn_bill_markup,
3477 x_txn_curr_code => l_rev_txn_curr_code,
3478 x_return_status => l_x_return_status,
3479 x_msg_data => l_bill_msg_data,
3480 x_msg_count => l_bill_msg_count
3481 );
3482
3483
3484 /* Raising the Billing Exception to pass the error values to the Main Api */
3485 IF l_x_return_status <> g_success THEN
3486 RAISE l_bill_api;
3487 END IF;
3488
3489 ELSE
3490 IF p_override_currency_Code IS NULL THEN
3491 RAISE l_invalid_currency;
3492 END IF;
3493 l_txn_raw_revenue :=l_raw_revenue ;
3494 IF p_quantity <>0 THEN
3495 l_txn_bill_rate :=l_raw_revenue/(NVL(p_quantity,1)) ;
3496 END IF;
3497 l_rev_txn_curr_code:=p_override_currency_Code;
3498 END IF;
3499
3500 END IF; /* End of IF p_calculate_mode IN ('REVENUE','COST_REVENUE') THEN */
3501 /* Passing the output parametrs of Billing for Revenue */
3502 x_raw_revenue :=l_txn_raw_revenue;
3503 x_bill_rate :=l_txn_bill_rate ;
3504 x_bill_markup_percentage :=l_txn_bill_markup;
3505 x_rev_txn_curr_code :=l_rev_txn_curr_code;
3506 x_revenue_rejection_code :=NULL;
3507 x_return_status :=l_x_return_status;
3508 IF g1_debug_mode = 'Y' THEN
3509 pa_debug.g_err_stage:='OUT of Get_plan_plan_Rates:p_project_id'||p_project_id||'p_task_id'||p_task_id||'l_txn_raw_revenue'||l_txn_raw_revenue||'l_txn_bill_rate'||l_txn_bill_rate||'l_rev_txn_curr_code'||l_rev_txn_curr_code;
3510 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3511 END IF;
3512 EXCEPTION
3513
3514 WHEN l_no_cost THEN
3515 x_raw_revenue := NULL;
3516 x_bill_rate := NULL;
3517 x_bill_markup_percentage := NULL;
3518 x_rev_txn_curr_code := NULL;
3519 x_revenue_rejection_code := 'PA_NO_ACCT_COST';
3520 x_return_status := g_error;
3521 x_msg_data := 'PA_NO_ACCT_COST';
3522 x_msg_count := 1;
3523
3524
3525 IF g1_debug_mode = 'Y' THEN
3526 pa_debug.g_err_stage:='No Cost exist for the tranascation:p_project_id'||p_project_id||'p_task_id'||p_task_id;
3527 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3528 END IF;
3529 WHEN l_bill_api THEN
3530 x_raw_revenue := NULL;
3531 x_bill_rate := NULL;
3532 x_bill_markup_percentage := NULL;
3533 x_rev_txn_curr_code := NULL;
3534 x_revenue_rejection_code := l_bill_msg_data;
3535 x_return_status := l_x_return_status;
3536 x_msg_data := l_bill_msg_data;
3537 x_msg_count := l_bill_msg_count;
3538
3539 IF g1_debug_mode = 'Y' THEN
3540 pa_debug.g_err_stage:='Billing api is throwing error';
3541 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3542 END IF;
3543
3544
3545 END Get_plan_plan_Rates;
3546 --
3547 -- Procedure : Get_plan_default_rates
3548 -- Purpose : This is an internal procedure for calculating the bill rate and raw revenue
3549 -- for the Enhanced PRL
3550 -- Parameters :
3551 --
3552 PROCEDURE Get_plan_default_rates(
3553 p_project_type IN VARCHAR2,
3554 p_budget_version_id IN NUMBER DEFAULT NULL,
3555 p_use_planning_rates_flag IN VARCHAR2 DEFAULT 'N',
3556 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y',
3557 p_quantity IN NUMBER,
3558 p_item_date IN DATE,
3559 p_schedule_type IN VARCHAR2 DEFAULT NULL,
3560 p_project_org_id IN NUMBER,
3561 p_expenditure_org_id IN NUMBER,
3562 p_nlr_organization_id IN NUMBER DEFAULT NULL,
3563 p_override_burden_cost_rate IN NUMBER DEFAULT NULL,
3564 p_override_txn_currency_code IN VARCHAR2 DEFAULT NULL,
3565 p_project_id IN NUMBER,
3566 p_system_linkage IN VARCHAR2,
3567 p_expenditure_type IN VARCHAR2,
3568 p_burden_cost IN NUMBER DEFAULT NULL,
3569 p_incurred_by_organz_id IN NUMBER,
3570 p_task_id IN NUMBER DEFAULT NULL,
3571 p_plan_burden_cost_sch_id IN NUMBER DEFAULT NULL,
3572 p_raw_cost IN NUMBER,
3573 p_override_trxn_cost_rate IN NUMBER DEFAULT NULL,
3574 p_override_trxn_bill_rate IN NUMBER DEFAULT NULL,
3575 p_raw_revenue IN NUMBER DEFAULT NULL,
3576 p_override_to_organz_id IN NUMBER,
3577 x_bill_rate OUT NOCOPY NUMBER,
3578 x_cost_rate OUT NOCOPY NUMBER,
3579 x_burden_cost_rate OUT NOCOPY NUMBER,
3580 x_burden_multiplier OUT NOCOPY NUMBER,
3581 x_raw_cost OUT NOCOPY NUMBER,
3582 x_burden_cost OUT NOCOPY NUMBER,
3583 x_raw_revenue OUT NOCOPY NUMBER,
3584 x_bill_markup_percentage OUT NOCOPY NUMBER,
3585 x_cost_markup_percentage OUT NOCOPY NUMBER,
3586 x_cost_txn_curr_code OUT NOCOPY VARCHAR2,
3587 x_rev_txn_curr_code OUT NOCOPY VARCHAR2,
3588 x_raw_cost_rejection_code OUT NOCOPY VARCHAR2,
3589 x_burden_cost_rejection_code OUT NOCOPY VARCHAR2,
3590 x_revenue_rejection_code OUT NOCOPY VARCHAR2,
3591 x_cost_ind_compiled_set_id OUT NOCOPY NUMBER,
3592 x_return_status OUT NOCOPY VARCHAR2,
3593 x_msg_count OUT NOCOPY NUMBER,
3594 x_msg_data OUT NOCOPY VARCHAR2)
3595 IS
3596 l_x_return_status VARCHAR2(20):=g_success; -- store the return status
3597 l_bill_rate NUMBER :=NULL;
3598 l_cost_rate NUMBER :=NULL;
3599 l_adjust_amount NUMBER :=NULL;
3600 l_markup NUMBER :=NULL;
3601 l_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE;
3602 l_raw_revenue NUMBER :=NULL; -- store the raw revenue
3603 l_true BOOLEAN := FALSE;
3604 l_txn_raw_revenue NUMBER :=NULL; -- store the raw revenue trans. curr.
3605 l_raw_cost NUMBER := NULL;
3606 l_burden_cost NUMBER := NULL;
3607 l_txn_currency_code pa_bill_rates_all.rate_currency_code%TYPE;
3608 l_inter_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE;--4194214
3609 l_burden_cost_rate NUMBER:=NULL;
3610 l_cost_ind_compiled_set_id NUMBER;
3611 l_burd_organization_id NUMBER;
3612 l_x_msg_data VARCHAR2(1000);
3613 l_x_msg_count NUMBER;
3614 l_schedule_type VARCHAR2(60);
3615 l_burd_sch_id NUMBER;
3616 l_burd_sch_rev_id NUMBER;
3617 l_burd_sch_fixed_date DATE;
3618 l_burd_sch_cost_base VARCHAR2(1000);
3619 l_burd_sch_cp_structure VARCHAR2(1000);
3620 l_txn_burden_multiplier NUMBER :=NULL;
3621 l_cost_return_status VARCHAR2(1):=g_success;
3622 l_cost_msg_data VARCHAR2(1000);
3623 l_txn_burden_cost_rate NUMBER :=NULL;
3624 l_cost_txn_curr_code VARCHAR2(50);
3625 l_txn_burden_cost NUMBER :=NULL;
3626 l_txn_raw_cost NUMBER :=NULL;
3627 l_txn_cost_rate NUMBER :=NULL;
3628 l_burden_cost_rejection_code VARCHAR2(50);
3629 l_bill_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE;
3630 l_inter_return_status VARCHAR2(1) :=NULL;
3631 l_calling_mode VARCHAR2(20):=NULL;
3632 l_override_organization_id NUMBER;
3633 l_ovr_return_status VARCHAR2(20):=g_success;
3634 l_ovr_msg_count NUMBER;
3635 l_ovr_msg_data VARCHAR2(1000);
3636 BEGIN
3637 pa_debug.init_err_stack('PA_PLAN_REVENUE.Get_plan_default_rates');
3638 IF g1_debug_mode = 'Y' THEN
3639 pa_debug.g_err_stage:='Validating input parameters:->p_schedule_type'||p_schedule_type||'p_rate_based_flag'||p_rate_based_flag||'p_raw_cost'||p_raw_cost||'p_raw_revenue'||p_raw_revenue;
3640 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,3);
3641 END IF;
3642 IF g1_debug_mode = 'Y' THEN
3643 pa_debug.g_err_stage:='Validating input parameters:->p_use_planning_rates_flag '||p_use_planning_rates_flag ||'p_plan_burden_cost_sch_id '||p_plan_burden_cost_sch_id ;
3644 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,3);
3645 END IF;
3646 IF g1_debug_mode = 'Y' THEN
3647 pa_debug.g_err_stage:='Validating input parameters:->p_nlr_organization_id '||p_nlr_organization_id ||'p_expenditure_org_id '||p_expenditure_org_id ||'p_override_to_organz_id '||p_override_to_organz_id;
3648 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,3);
3649 END IF;
3650 IF g1_debug_mode = 'Y' THEN
3651 pa_debug.g_err_stage:='Validating input parameters:->p_incurred_by_organz_id'||p_incurred_by_organz_id ;
3652 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,3);
3653 END IF;
3654 /* Assigning the parameters to local variables */
3655 l_burden_cost := p_burden_cost;
3656 l_raw_cost := p_raw_cost;
3657 l_cost_rate := p_override_trxn_cost_rate;
3658 l_bill_rate := p_override_trxn_bill_rate;
3659 l_raw_revenue := p_raw_revenue;
3660 l_override_organization_id := p_override_to_organz_id;
3661 IF p_use_planning_rates_flag = 'Y' THEN
3662 l_calling_mode :='PLAN_RATES';
3663 ELSE
3664 l_calling_mode :='ACTUAL_RATES';
3665 END IF;
3666 /* Check for mandatory parameters done */
3667 /* First computing the raw_cost if p_schedule_type is passed COST or NULL
3668 If COST is passed then it'll compute only Costing but if passed nll it'll
3669 compute both Costing as well as Revenue */
3670 IF g1_debug_mode = 'Y' THEN
3671 pa_debug.g_err_stage:='Calculating Costing data';
3672 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,5);
3673 END IF;
3674 IF NVL(p_schedule_type,'COST')='COST' THEN
3675 --13.02
3676 IF l_cost_rate IS NULL THEN
3677 begin
3678 SELECT default_raw_cost, DEF_RAW_COST_CURRENCY_CODE INTO l_cost_rate, l_txn_curr_code
3679 FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT
3680 WHERE BV.BUDGET_VERSION_ID = p_budget_version_id
3681 AND OPT.PROJECT_ID = BV.PROJECT_ID
3682 AND OPT.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
3683 AND OPT.FIN_PLAN_VERSION_ID = p_budget_version_id
3684 AND BV.PROJECT_ID = p_project_id;
3685 EXCEPTION
3686 WHEN OTHERS THEN
3687 l_cost_rate := NULL;
3688 l_txn_curr_code := NULL;
3689 END;
3690 ELSE
3691 begin
3692 SELECT DEF_RAW_COST_CURRENCY_CODE INTO l_txn_curr_code
3693 FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT
3694 WHERE BV.BUDGET_VERSION_ID = p_budget_version_id
3695 AND OPT.PROJECT_ID = BV.PROJECT_ID
3696 AND OPT.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
3697 AND OPT.FIN_PLAN_VERSION_ID = p_budget_version_id
3698 AND BV.PROJECT_ID = p_project_id;
3699 EXCEPTION
3700 WHEN OTHERS THEN
3701 l_txn_curr_code := NULL;
3702 END;
3703 END IF;
3704 IF l_cost_rate IS NOT NULL AND l_raw_cost IS NULL THEN
3705 IF NVL(p_rate_based_flag,'N')='Y' THEN
3706 l_raw_cost := l_cost_rate*p_quantity;
3707 END IF;
3708 END IF;
3709 --13.02
3710 IF l_raw_cost IS NULL THEN
3711 IF p_rate_based_flag = 'Y' THEN
3712 IF p_quantity IS NULL THEN
3713 IF g1_debug_mode = 'Y' THEN
3714 pa_debug.g_err_stage:='validating Get_plan_default_rates: p_quantity is required for rate based ';
3715 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,5);
3716 END IF;
3717 RAISE l_rate_based_no_quantity;
3718 END IF;
3719 END IF;
3720 BEGIN
3721 IF ( l_raw_cost IS NULL) THEN
3722 RAISE l_no_cost;
3723 END IF;
3724 IF g1_debug_mode = 'Y' THEN
3725 pa_debug.g_err_stage:='9999 l_cost_rate: ' || l_cost_rate|| 'raw_cost : ' ||l_raw_cost || 'currency_code : ' || l_txn_curr_code;
3726 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,3);
3727 END IF;
3728 EXCEPTION
3729 WHEN l_no_cost THEN
3730 x_raw_cost := NULL;
3731 l_inter_return_status := FND_API.G_RET_STS_ERROR;
3732 x_msg_data := 'PA_FCST_NO_COST_RATE';
3733 x_raw_cost_rejection_code := 'PA_FCST_NO_COST_RATE';
3734 x_msg_count := 1;
3735 IF g1_debug_mode = 'Y' THEN
3736 pa_debug.g_err_stage :='No Cost Rate Exists:p_project_id'||p_project_id||'p_task_id'||p_task_id;
3737 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,5);
3738 END IF;
3739 WHEN OTHERS THEN
3740 x_raw_cost := NULL;
3741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3742 x_msg_count := 1;
3743 x_msg_data := SUBSTR(SQLERRM,1,30);
3744 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
3745 IF g1_debug_mode = 'Y' THEN
3746 pa_debug.g_err_stage :='Unexpected Error'||SQLERRM;
3747 pa_debug.write('Get_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
3748 END IF;
3749 RAISE;
3750 END;
3751 /* End of Begin for Revenue Scedule Type */
3752 ELSE
3753 IF NVL(p_quantity,1) <>0 THEN
3754 l_cost_rate :=NVL(p_override_trxn_cost_rate,l_raw_cost/p_quantity);
3755 ELSE
3756 l_cost_rate:=p_override_trxn_cost_rate;
3757 END IF;
3758 END IF;
3759 /* End of l_raw_cost */
3760 END IF;
3761 /*End if of l_schedule_type=Cost */
3762 /*==========================================================================================*/
3763 /* If from the above raw_cost is computed and it needs to compute the burden cost on it
3764 then the folowing code will be executed */
3765 IF l_burden_cost IS NOT NULL THEN
3766 --assigning override burden cost/or derived burden cost from Actual
3767 IF g1_debug_mode = 'Y' THEN
3768 pa_debug.g_err_stage := 'Assignging override burden cost values to out params';
3769 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
3770 END IF;
3771 l_txn_burden_cost :=l_burden_cost;
3772 IF (p_quantity <> 0 ) THEN
3773 l_txn_burden_cost_rate := l_burden_cost / NVL(p_quantity,1) ;
3774 END IF;
3775 IF NVL(l_raw_cost,0) <>0 THEN
3776 l_txn_burden_multiplier := (l_burden_cost/l_raw_cost)-1;
3777 END IF;
3778 ElsIf ( p_override_burden_cost_rate IS NULL AND l_burden_cost IS NULL AND l_raw_cost IS NOT NULL AND l_cost_rate IS NOT NULL AND pa_cost1.check_proj_burdened(p_project_type,p_project_id) = 'Y' ) THEN
3779 l_burd_organization_id := NVL(l_override_organization_id, NVL(p_incurred_by_organz_id ,p_nlr_organization_id));
3780 BEGIN
3781 pa_cost1.Get_burden_sch_details (p_calling_mode =>l_calling_mode
3782 ,p_exp_item_id => NULL
3783 ,p_trxn_type => NULL
3784 ,p_project_type => p_project_type
3785 ,p_project_id => p_project_id
3786 ,p_task_id => p_task_id
3787 ,p_exp_organization_id => l_burd_organization_id
3788 ,p_expenditure_type => p_expenditure_type
3789 ,p_schedule_type => 'COST'
3790 ,p_exp_item_date => p_item_date
3791 ,p_trxn_curr_code => NVL(l_txn_curr_code,p_override_txn_currency_code)
3792 ,p_burden_schedule_id => p_plan_burden_cost_sch_id
3793 ,x_schedule_id => l_burd_sch_id
3794 ,x_sch_revision_id => l_burd_sch_rev_id
3795 ,x_sch_fixed_date => l_burd_sch_fixed_date
3796 ,x_cost_base => l_burd_sch_cost_base
3797 ,x_cost_plus_structure => l_burd_sch_cp_structure
3798 ,x_compiled_set_id => l_cost_ind_compiled_set_id
3799 ,x_burden_multiplier => l_txn_burden_multiplier
3800 ,x_return_status => l_cost_return_status
3801 ,x_error_msg_code => l_cost_msg_data );
3802 EXCEPTION
3803 WHEN OTHERS THEN
3804 l_cost_return_status := 'E';
3805 l_cost_msg_data := 'GET_BURDEN_DETAILS: '||SUBSTR(sqlerrm,1,30);
3806 END;
3807 IF ( l_cost_return_status <> g_success OR l_txn_burden_multiplier IS NULL ) THEN
3808 l_burden_cost_rejection_code := l_cost_msg_data;
3809 -------------------------------------------------------
3810 -- Get Burden Cost and rate from Raw Cost and Quantity.
3811 -------------------------------------------------------
3812 ELSE
3813 l_txn_burden_cost := pa_currency.round_trans_currency_amt( l_raw_cost * NVL(l_txn_burden_multiplier,0),NVL(l_txn_curr_code,p_override_txn_currency_code )) + l_raw_cost ;
3814 /*bug3749153 no need to compute the burden cost rate if it equals raw_cost_rate */
3815 IF l_txn_burden_cost = l_raw_cost THEN
3816 l_txn_burden_cost_rate := l_cost_rate;
3817 ELSE
3818 IF (p_quantity <> 0 ) THEN
3819 l_txn_burden_cost_rate := l_txn_burden_cost / NVL(P_quantity, 1) ;
3820 END IF;
3821 END IF;
3822 END IF;
3823 Elsif p_override_burden_cost_rate IS NOT NULL AND l_burden_cost IS NULL THEN
3824 l_txn_burden_cost := p_quantity * p_override_burden_cost_rate;
3825 l_txn_burden_cost_rate := p_override_burden_cost_rate;
3826 IF NVL(l_raw_cost,0) <>0 THEN
3827 l_txn_burden_multiplier := ( l_txn_burden_cost / l_raw_cost)-1;
3828 END IF;
3829 ElsIF l_raw_cost IS NOT NULL THEN
3830 --copy the raw cost to the burden costs
3831 l_txn_burden_cost := l_raw_cost;
3832 l_txn_burden_cost_rate := l_cost_rate;
3833 l_txn_burden_multiplier := 0;
3834 END IF;
3835 /* Burden Cost Calucltaion complete */
3836 /*==========================================================================================*/
3837 l_inter_txn_curr_code :=NVL(l_txn_curr_code,p_override_txn_currency_code) ; --4194214
3838 /* Assigning out all the Cost and Burden Cost Parameters */
3839 x_raw_cost := l_raw_cost ;
3840 x_cost_rate := l_cost_rate ;
3841 x_burden_cost := l_txn_burden_cost;
3842 x_burden_cost_rate := l_txn_burden_cost_rate ;
3843 x_burden_multiplier := l_txn_burden_multiplier;
3844 x_cost_txn_curr_code := l_inter_txn_curr_code;--NVL(l_txn_curr_code,p_override_txn_currency_code) ;--4194214
3845 x_return_status := NVL(l_inter_return_status,l_x_return_status);
3846 l_inter_return_status := NVL(l_inter_return_status,l_x_return_status);
3847 x_burden_cost_rejection_code:= l_burden_cost_rejection_code;
3848 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
3849 IF g1_debug_mode = 'Y' THEN
3850 pa_debug.g_err_stage := 'Going to calculate revenue:->p_schedule_type'||p_schedule_type||'l_raw_revenue'||l_raw_revenue||'l_raw_cost'||l_raw_cost||'l_txn_burden_cost'||l_txn_burden_cost;
3851 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
3852 END IF;
3853 IF NVL(p_schedule_type,'REVENUE')='REVENUE' THEN
3854 IF p_system_linkage ='BTC' THEN
3855 l_raw_cost := NVL(l_txn_burden_cost,p_burden_cost);
3856 ELSE
3857 l_raw_cost:= NVL(l_raw_cost,p_raw_cost);
3858 END IF;
3859 IF l_bill_rate IS null
3860 then
3861 BEGIN
3862 SELECT DEFAULT_BILL_RATE, DEF_BILL_RATE_CURRENCY_CODE,DEF_MARKUP_PERCENTAGE
3863 INTO l_bill_rate, l_bill_txn_curr_code, l_markup
3864 FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT
3865 WHERE BV.BUDGET_VERSION_ID = p_budget_version_id
3866 AND OPT.PROJECT_ID = BV.PROJECT_ID
3867 AND OPT.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
3868 AND OPT.FIN_PLAN_VERSION_ID = p_budget_version_id
3869 AND BV.PROJECT_ID = p_project_id;
3870 EXCEPTION
3871 WHEN OTHERS THEN
3872 l_bill_rate := NULL;
3873 l_bill_txn_curr_code := NULL;
3874 l_markup := NULL;
3875 END;
3876 ELSE
3877 BEGIN
3878 SELECT DEF_BILL_RATE_CURRENCY_CODE,DEF_MARKUP_PERCENTAGE
3879 INTO l_bill_txn_curr_code, l_markup
3880 FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT
3881 WHERE BV.BUDGET_VERSION_ID = p_budget_version_id
3882 AND OPT.PROJECT_ID = BV.PROJECT_ID
3883 AND OPT.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
3884 AND OPT.FIN_PLAN_VERSION_ID = p_budget_version_id
3885 AND BV.PROJECT_ID = p_project_id;
3886 EXCEPTION
3887 WHEN OTHERS THEN
3888 l_bill_txn_curr_code := NULL;
3889 l_markup := NULL;
3890 END;
3891 ENd IF;
3892 /* This code is added beacuse if you choose to calculate only REVENUE then for Non-Rate based transaction it might happen that the
3893 l_raw_cost is null since we have not called the COSTing api to compute the cost so just assigning p_quantity to l_raw_cost
3894 as l_raw_cost is used in the revenue calculation if its non-Rate based transaction */
3895 IF p_schedule_type ='REVENUE' AND l_raw_cost IS NULL THEN
3896 IF NVL(p_rate_based_flag,'N') ='N' THEN
3897 l_raw_cost :=p_quantity;
3898 END IF;
3899 IF g1_debug_mode = 'Y' THEN
3900 pa_debug.g_err_stage := 'p_quanity is directly getting converted to l_raw_cost for only REVENUE mode:l_raw_cost'||l_raw_cost;
3901 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,3);
3902 END IF;
3903 END IF;
3904 IF l_raw_revenue IS NULL THEN
3905 l_true := FALSE;
3906 IF NVL(p_rate_based_flag,'N') ='N' AND l_raw_cost IS NULL THEN
3907 RAISE l_no_cost;
3908 END IF;
3909 IF p_rate_based_flag ='Y' THEN
3910 IF p_quantity IS NULL THEN
3911 IF g1_debug_mode = 'Y' THEN
3912 pa_debug.g_err_stage:='validating Get_plan_default_rates: p_quantity is required for rate based ';
3913 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,5);
3914 END IF;
3915 RAISE l_rate_based_no_quantity;
3916 END IF;
3917 END IF;
3918 IF l_bill_rate IS NOT NULL AND l_raw_revenue IS NULL
3919 AND NVL(p_rate_based_flag,'N') = 'Y' THEN
3920 l_raw_revenue :=l_bill_rate*p_quantity;
3921 l_markup := null; --Added for bug 13833335
3922 END IF;
3923 IF l_raw_revenue is null and l_markup is NOT null
3924 AND NVL(p_rate_based_flag,'N') = 'N'
3925 then
3926 IF l_raw_cost IS null
3927 then
3928 l_raw_cost := p_quantity;
3929 END IF;
3930 l_raw_revenue := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +l_markup)
3931 * (l_raw_cost / 100), l_bill_txn_curr_code);
3932 l_bill_rate := null; --Added for bug 13833335
3933 END IF;
3934 begin
3935 l_txn_raw_revenue := l_raw_revenue;
3936 IF ( l_txn_raw_revenue IS NULL) THEN
3937 RAISE l_no_revenue;
3938 END IF;
3939 x_raw_revenue := l_txn_raw_revenue ;
3940 x_bill_rate := l_bill_rate ;
3941 x_bill_markup_percentage := l_markup ;
3942 x_rev_txn_curr_code := l_bill_txn_curr_code ;
3943 x_return_status := l_x_return_status;
3944 IF g1_debug_mode = 'Y' THEN
3945 pa_debug.g_err_stage :='l_bill_rate: ' || l_bill_rate || 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
3946 pa_debug.write('Get_plan_default_rates : ' || g_module_name,pa_debug.g_err_stage,2);
3947 END IF;
3948 EXCEPTION
3949 WHEN l_no_revenue THEN
3950 RAISE l_no_revenue;
3951 END;
3952 /* End of Begin for Revenue Scedule Type */
3953 ELSE
3954 IF NVL(p_quantity,1)<>0 THEN
3955 l_bill_rate :=NVL(p_override_trxn_bill_rate,l_raw_revenue/p_quantity);
3956 ELSE
3957 l_bill_rate:=p_override_trxn_bill_rate;
3958 END IF;
3959 END IF;
3960 /* end if of IF l_raw_revenue I NULL */
3961 ELSE
3962 l_bill_rate:=p_override_trxn_bill_rate;
3963 END IF;
3964 /*End if of l_schedule_type=Revenue */
3965 x_raw_revenue := l_raw_revenue ;
3966 x_bill_rate := l_bill_rate ;
3967 x_bill_markup_percentage := l_markup ;
3968 x_rev_txn_curr_code := l_bill_txn_curr_code;
3969 x_return_status := l_x_return_status;
3970 pa_debug.reset_err_stack;
3971 EXCEPTION
3972 WHEN l_insufficeient_param THEN
3973 IF g1_debug_mode = 'Y' THEN
3974 pa_debug.g_err_stage:='All the Required parameters are not passes to Resource Schedule';
3975 pa_debug.write('Get_plan_default_rates : ' || g_module_name,pa_debug.g_err_stage,3);
3976 END IF;
3977 x_return_status := g_ERROR;
3978 x_msg_count := 1;
3979 x_msg_data := 'PA_FCST_INSUFFICIENT_PARA';
3980 x_revenue_rejection_code := 'PA_FCST_INSUFFICIENT_PARA';
3981 x_raw_cost_rejection_code := 'PA_FCST_INSUFFICIENT_PARA';
3982 x_burden_cost_rejection_code := 'PA_FCST_INSUFFICIENT_PARA';
3983 x_raw_revenue := NULL;
3984 x_raw_cost := NULL;
3985 x_bill_rate := NULL;
3986 x_cost_rate := NULL;
3987 pa_debug.reset_err_stack;
3988 WHEN l_rate_based_no_quantity THEN
3989 IF g1_debug_mode = 'Y' THEN
3990 pa_debug.g_err_stage:='Quantity is required for a rate based transaction';
3991 pa_debug.write('Get_plan_default_rates : ' || g_module_name,pa_debug.g_err_stage,3);
3992 END IF;
3993 x_return_status := g_ERROR;
3994 x_msg_count := 1;
3995 x_msg_data := 'PA_EX_QTY_EXIST';
3996 x_revenue_rejection_code := 'PA_EX_QTY_EXIST';
3997 x_raw_cost_rejection_code := 'PA_EX_QTY_EXIST';
3998 x_raw_revenue := NULL;
3999 x_raw_cost := NULL;
4000 x_bill_rate := NULL;
4001 x_cost_rate := NULL;
4002 pa_debug.reset_err_stack;
4003 WHEN l_no_cost THEN
4004 x_raw_revenue := NULL;
4005 x_raw_cost := NULL;
4006 x_bill_rate := NULL;
4007 x_cost_rate := NULL;
4008 x_return_status := NVL(l_inter_return_status,g_ERROR);
4009 x_msg_data := 'PA_FCST_NO_COST_RATE';
4010 x_raw_cost_rejection_code := 'PA_FCST_NO_COST_RATE';
4011 x_burden_cost_rejection_code := 'PA_FCST_NO_COST_RATE';
4012 x_revenue_rejection_code := 'PA_FCST_NO_COST_RATE';
4013 x_msg_count := 1;
4014 IF g1_debug_mode = 'Y' THEN
4015 pa_debug.g_err_stage :='No Cost Rate Exists:p_project_id'||p_project_id||'p_task_id'||p_task_id;
4016 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,5);
4017 END IF;
4018 pa_debug.reset_err_stack;
4019 WHEN l_no_revenue THEN
4020 x_raw_revenue := NULL;
4021 x_bill_rate := NULL;
4022 x_return_status := NVL(l_inter_return_status,g_ERROR);
4023 x_msg_data := 'PA_FCST_NO_BILL_RATE';
4024 x_revenue_rejection_code := 'PA_FCST_NO_BILL_RATE';
4025 x_msg_count := 1;
4026 IF g1_debug_mode = 'Y' THEN
4027 pa_debug.g_err_stage :='No Bill Rate Exists:p_project_id'||p_project_id||'p_task_id'||p_task_id;
4028 pa_debug.write('Get_plan_default_rates: ' || g_module_name,pa_debug.g_err_stage,5);
4029 END IF;
4030 pa_debug.reset_err_stack;
4031 WHEN OTHERS THEN
4032 x_raw_revenue := NULL;
4033 x_raw_cost := NULL;
4034 x_bill_rate := NULL;
4035 x_cost_rate := NULL;
4036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4037 x_msg_count := 1;
4038 x_msg_data := SUBSTR(SQLERRM,1,30);
4039 x_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
4040 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
4041 x_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
4042 IF g1_debug_mode = 'Y' THEN
4043 pa_debug.g_err_stage :='SQLERROR ' || SQLCODE;
4044 pa_debug.write('Get_plan_default_rates : ' || g_module_name,pa_debug.g_err_stage,5);
4045 END IF;
4046 pa_debug.reset_err_stack;
4047 RAISE;
4048 END Get_plan_default_rates ;
4049 /*End of CBS Changes*/
4050 BEGIN
4051 Get_exp_type_uom;
4052 END PA_PLAN_REVENUE;
4053
[Home] [Help]