1 PACKAGE BODY PA_PLAN_REVENUE AS
2 -- $Header: PAXPLRTB.pls 120.7.12000000.3 2007/05/24 13:24:05 kmaddi 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 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
29 /* PRIVATE PROCEDURE : Get_Res_Class_Hierarchy_Rate to get the rates based on the Resource Hierarcy*/
30 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
31
32 PROCEDURE Get_Res_Class_Hierarchy_Rate(
33 p_res_class_rate_sch_id IN NUMBER,
34 p_item_date IN DATE ,
35 p_org_id IN NUMBER,
36 p_resource_class_code IN VARCHAR2,
37 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 */
38 x_rate OUT NOCOPY NUMBER,
39 x_markup_percentage OUT NOCOPY NUMBER,
40 x_uom OUT NOCOPY VARCHAR2,
41 x_rate_currency_code OUT NOCOPY VARCHAR2,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2) AS
45 Cursor c_rule is
46 SELECT b.rate,b.markup_percentage,b.bill_rate_unit,b.rate_currency_code
47 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
48 WHERE sch.bill_rate_sch_id = p_res_class_rate_sch_id
49 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
50 AND sch.schedule_type = 'RESOURCE_CLASS'
51 AND trunc(p_item_date) BETWEEN trunc(b.start_date_active) AND trunc(NVL(b.end_date_active ,p_item_date))
52 AND b.res_class_organization_id = p_res_class_org_id
53 AND b.resource_class_code = p_resource_class_code;
54
55 /* Below cursor will get the rate and markup for the schedule if no rate exists for
56 the Organization and resource Class in the above cursor*/
57
58 cursor c_parent_rule (p_proj_org_version_id IN NUMBER) is
59 SELECT b.rate,b.markup_percentage,b.bill_rate_unit,b.rate_currency_code
60 From pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b,
61 ( select organization_id_parent PARENT_ORGANIZATION_ID,level parent_level
62 from per_org_structure_elements
63 where org_structure_version_id = p_proj_org_version_id
64 connect by prior organization_id_parent=organization_id_child
65 and prior org_structure_version_id = org_structure_version_id
66 start with organization_id_child=p_res_class_org_id
67 and org_structure_version_id=p_proj_org_version_id) org
68 Where sch.bill_rate_sch_id = p_res_class_rate_sch_id
69 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
70 AND sch.schedule_type = 'RESOURCE_CLASS'
71 AND trunc(p_item_date) BETWEEN trunc(b.start_date_active) AND trunc(NVL(b.end_date_active, p_item_date))
72 AND b.res_class_organization_id = org.PARENT_ORGANIZATION_ID
73 AND b.resource_class_code = p_resource_class_code
74 order by org.parent_level ;
75
76 l_rate NUMBER:=NULL;
77 l_markup_percentage NUMBER:=NULL;
78 l_rate_currency_code VARCHAR2(30) :=NULL;
79 l_uom VARCHAR2(30):=NULL;
80 l_true BOOLEAN:= FALSE;
81 l_x_return_status VARCHAR2(20):= g_success;
82 l_insufficient_param EXCEPTION;
83 l_no_rate EXCEPTION;
84 l_PROJ_ORG_STRUCT_VERSION_ID pa_implementations_all.PROJ_ORG_STRUCTURE_VERSION_ID%TYPE;
85
86 BEGIN
87 /*Checking all the mandatory Parameters */
88
89 IF g1_debug_mode = 'Y' THEN
90 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 ;
91 pa_debug.write('PA_PLAN_REVENUE.Get_Res_Class_Hierarchy_Rate: ' || g_module_name,pa_debug.g_err_stage,3);
92 END IF;
93 IF g1_debug_mode = 'Y' THEN
94 pa_debug.g_err_stage := 'p_resource_class_code:'||p_resource_class_code||'p_item_date :'||p_item_date ;
95 pa_debug.write('PA_PLAN_REVENUE.Get_Res_Class_Hierarchy_Rate: ' || g_module_name,pa_debug.g_err_stage,3);
96 END IF;
97
98 IF p_res_class_rate_sch_id IS NULL OR p_item_date IS NULL
99 OR p_resource_class_code IS NULL THEN
100 raise l_insufficient_param;
101 END IF;
102 open c_rule ;
103 fetch c_rule
104 into l_rate,l_markup_percentage,l_uom,l_rate_currency_code;
105 begin
106 select i. PROJ_ORG_STRUCTURE_VERSION_ID
107 into l_PROJ_ORG_STRUCT_VERSION_ID
108 from pa_implementations_all i
109 where NVL(i.org_id,-99) = NVL(p_org_id,-99);
110 exception
111 when no_data_found then
112 null;
113 end;
114 /* If Not found for the direct resourceOrganization Id go for Climbing the Hierarchy */
115 IF c_rule%NOTFOUND THEN
116 FOR r_parent_rule IN c_parent_rule(l_PROJ_ORG_STRUCT_VERSION_ID) LOOP
117 -- Checking if the cursor is returning more than one row then exit getting the first row only
118 IF (l_true) THEN
119 EXIT;
120 ELSE
121 l_rate:=r_parent_rule.rate;
122 l_markup_percentage := r_parent_rule.markup_percentage;
123 l_uom := r_parent_rule.bill_rate_unit;
124 l_rate_currency_code := r_parent_rule.rate_currency_code;
125 l_true := TRUE;
126 END IF;
127 END LOOP;
128
129 END IF;
130 close c_rule;
131 IF l_rate IS NULL AND l_markup_percentage IS NULL THEN
132 RAISE l_no_rate;
133 END IF;
134 x_rate :=l_rate;
135 x_markup_percentage :=l_markup_percentage ;
136 x_uom :=l_uom;
137 x_rate_currency_code :=l_rate_currency_code;
138 x_return_status :=l_x_return_status;
139
140 EXCEPTION
141 WHEN l_insufficient_param THEN
142 x_rate :=NULL;
143 x_markup_percentage :=NULL;
144 x_uom :=NULL;
145 x_rate_currency_code :=NULL;
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
147 x_msg_count := 1;
148 x_msg_data := 'PA_FCST_INSUFFICIENT_PARA';
149 WHEN l_no_rate THEN
150 x_rate :=NULL;
151 x_markup_percentage :=NULL;
152 x_uom :=NULL;
153 x_rate_currency_code :=NULL;
154 x_return_status := g_error;
155 x_msg_count := 1;
156 x_msg_data := 'PA_RES_NO_BILL_MARKUP';
157
158 WHEN OTHERS THEN
159 if c_rule%isopen then
160 close c_rule;
161 end if;
162 x_rate :=NULL;
163 x_markup_percentage :=NULL;
164 x_uom :=NULL;
165 x_rate_currency_code :=NULL;
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 x_msg_count := 1;
168 x_msg_data := SUBSTR(SQLERRM,1,250);
169 RAISE;
170 END Get_Res_Class_Hierarchy_Rate;
171
172
173
174 PROCEDURE Get_exp_type_uom AS
175
176 cursor temp is
177 select expenditure_type,unit_of_measure
178 from pa_expenditure_types;
179
180 BEGIN
181 OPEN temp;
182 FETCH temp BULK COLLECT INTO g_expenditure_type_tbl,g_uom_tbl;
183 CLOSE temp;
184
185
186 g_count_init:= g_count_init+1;
187
188 IF g1_debug_mode = 'Y' THEN
189 pa_debug.g_err_stage:='Before count of g_count_init'|| g_count_init||'g_expenditure_type_tbl.COUNT'||g_expenditure_type_tbl.COUNT;
190 pa_debug.write('Get_exp_type_uom: ' || g_module_name,pa_debug.g_err_stage,2);
191 END IF;
192 END;
193
194 -- This procedure will calculate the raw revenue and bill amount from one of the 12 criterias on the basis
195
196
197
198 PROCEDURE Get_planning_Rates (
199 p_project_id IN NUMBER,
200 p_task_id IN NUMBER DEFAULT NULL,
201 p_top_task_id IN NUMBER DEFAULT NULL, /* for costing top task Id */
202 p_person_id IN NUMBER,
203 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
204 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
205 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
206 p_planning_resource_format IN VARCHAR2, /* resource format required for Costing */
207 p_use_planning_rates_flag IN VARCHAR2 DEFAULT 'N', /* Rate using Actual Rates */
208 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
209 p_uom IN VARCHAR2, /* Planning UOM */
210 p_system_linkage IN VARCHAR2,
211 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
212 p_rev_res_class_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Resource Class*/
213 p_cost_res_class_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on Resource Class*/
214 p_rev_task_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
215 p_rev_proj_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
216 --p_cost_nl_rate_sch_id IN NUMBER DEFAULT NULL,
217 p_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Job*/
218 p_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Emp*/
219 /* added for iteration2*/
220 p_plan_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on job for planning*/
221 p_plan_cost_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on job for planning*/
222 p_plan_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on emp for planning*/
223 p_plan_cost_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For cost Rate Calculations based on emp for planning*/
224 p_plan_rev_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on non labor for planning*/
225 p_plan_cost_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on non labor for planning*/
226 p_plan_burden_cost_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on burdening for planning*/
227 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) */
228 /* end here */
229 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
230 p_cost_rate_multiplier IN NUMBER DEFAULT NULL ,
231 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
232 p_quantity IN NUMBER, /* required param for People/Equipment Class */
233 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
234 p_cost_sch_type IN VARCHAR2 , /* Costing Schedule Type'COST' / 'REVENUE' / 'INVOICE' */
235 p_labor_sch_type IN VARCHAR2, /* Revenue Labor Schedule Type B/I */
236 p_non_labor_sch_type IN VARCHAR2 , /* Revenue Non_Labor Schedule Type B/I */
237 p_labor_schdl_discnt IN NUMBER DEFAULT NULL,
238 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL,
239 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL,
240 p_labor_schdl_fixed_date IN DATE DEFAULT NULL,
241 p_assignment_id IN NUMBER DEFAULT NULL, /* used as p_item_id in the internal APIs */
242 p_project_org_id IN NUMBER, /* Project Org Id */
243 p_project_type IN VARCHAR2,
244 p_expenditure_type IN VARCHAR2,
245 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
246 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
247 p_override_to_organz_id IN NUMBER, /* Override Org Id */
248 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
249 p_assignment_precedes_task IN VARCHAR2, /* Added for Asgmt overide */
250 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
251 p_task_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at Project Level */
252 p_project_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at task Level */
253 p_nlr_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
254 p_project_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at project Level */
255 p_task_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at task Level */
256 p_project_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at project Level */
257 p_task_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at task Level */
258 p_inventory_item_id IN NUMBER DEFAULT NULL, /* Passed for Inventoty Items */
259 p_BOM_resource_Id IN NUMBER DEFAULT NULL, /* Passed for BOM Resource Id */
260 P_mfc_cost_type_id IN NUMBER DEFAULT 0, /* Manufacturing cost api */
261 P_item_category_id IN NUMBER DEFAULT NULL, /* Manufacturing cost api */
262 p_mfc_cost_source IN NUMBER DEFAULT 1,
263 p_cost_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to costing internal api.*/
264 p_revenue_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to billing internal api.*/
265 p_override_burden_cost_rate IN NUMBER DEFAULT NULL, /*override burden multiplier and p_raw_cost is not null calculate x_burden_cost */
266 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
267 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*/
268 p_raw_cost IN NUMBER, /*If p_raw_cost is only passed,return the burden multiplier, burden_cost */
269 p_burden_cost IN NUMBER DEFAULT NULL,
270 p_raw_revenue IN NUMBER DEFAULT NULL,
271 p_billability_flag IN VARCHAR2 DEFAULT 'Y', /* Added rate calculation honoring billability flag */
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
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
762
763 IF ( (l_schedule_type='COST' and l_raw_cost IS NULL )
764 OR (l_schedule_type='REVENUE' and l_raw_revenue IS NULL )
765 OR l_schedule_type IS NULL ) THEN
766 IF g1_debug_mode = 'Y' THEN
767 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;
768 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
769 END IF;
770 BEGIN
771 Get_plan_res_class_rates (
772 p_project_type =>p_project_type
773 ,p_project_id =>p_project_id
774 ,p_task_id =>p_task_id
775 ,p_person_id =>p_person_id
776 ,p_job_id =>p_job_id
777 ,p_resource_class =>p_resource_class
778 ,p_use_planning_rates_flag =>p_use_planning_rates_flag
779 ,p_rate_based_flag =>p_rate_based_flag
780 ,p_uom =>p_uom
781 ,p_project_organz_id =>p_project_organz_id
782 ,p_rev_res_class_rate_sch_id =>p_rev_res_class_rate_sch_id
783 ,p_cost_res_class_rate_sch_id =>p_cost_res_class_rate_sch_id
784 ,p_plan_burden_cost_sch_id =>p_plan_burden_cost_sch_id
785 ,p_cost_rate_multiplier =>p_cost_rate_multiplier
786 ,p_bill_rate_multiplier =>p_bill_rate_multiplier
787 ,p_quantity =>p_quantity
788 ,p_item_date =>p_item_date
789 ,p_schedule_type =>l_schedule_type
790 ,p_project_org_id =>p_project_org_id
791 ,p_incurred_by_organz_id =>p_incurred_by_organz_id
792 ,p_override_to_organz_id =>p_override_to_organz_id
793 ,p_expenditure_org_id =>p_expenditure_org_id --l_expenditure_org_id
794 ,p_nlr_organization_id =>p_nlr_organization_id
795 ,p_override_trxn_cost_rate =>l_i_txn_cost_rate
796 ,p_override_burden_cost_rate =>l_i_txn_burden_cost_rate
797 ,p_override_trxn_bill_rate =>l_i_txn_bill_rate
798 ,p_override_txn_currency_code =>p_override_currency_code
799 ,p_txn_currency_code =>NVL(l_cost_txn_curr_code,p_txn_currency_code)--4194214
800 ,p_raw_cost =>NVL(l_raw_cost,p_raw_cost)
801 ,p_burden_cost =>NVL(l_burden_cost,p_burden_cost)
802 ,p_raw_revenue =>NVL(l_raw_revenue,p_raw_revenue)
803 ,p_system_linkage =>p_system_linkage
804 ,p_expenditure_type =>p_expenditure_type
805 ,x_bill_rate =>l_txn_bill_rate
806 ,x_cost_rate =>l_txn_cost_rate
807 ,x_burden_cost_rate =>l_txn_burden_cost_rate
808 ,x_raw_cost =>l_txn_raw_cost
809 ,x_burden_cost =>l_txn_burden_cost
810 ,x_raw_revenue =>l_txn_raw_revenue
811 ,x_bill_markup_percentage =>l_txn_bill_markup
812 ,x_cost_markup_percentage =>l_txn_cost_markup
813 ,x_burden_multiplier =>l_txn_burden_multiplier
814 ,x_cost_txn_curr_code =>l_cost_res_txn_curr_code
815 ,x_rev_txn_curr_code =>l_rev_res_txn_curr_code
816 ,x_raw_cost_rejection_code =>l_raw_cost_rejection_code
817 ,x_burden_cost_rejection_code =>l_burden_cost_rejection_code
818 ,x_revenue_rejection_code =>l_revenue_rejection_code
819 ,x_cost_ind_compiled_set_id =>l_cost_ind_compiled_set_id
820 ,x_return_status =>l_x_return_status
821 ,x_msg_count =>l_x_msg_count
822 ,x_msg_data =>l_x_msg_data);
823
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 l_x_msg_data := 'pa_plan_revenue.Get_plan_res_class_rates:' || SUBSTR(SQLERRM,1,250);
828 l_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
829 l_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
830 l_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
831 IF g1_debug_mode = 'Y' THEN
832 pa_debug.g_err_stage:=' Get_plan_res_class_rates_api is throwing When Others';
833 pa_debug.write('Get_planing_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
834 END IF;
835 RAISE l_Get_planning_Rates_api;
836 END;
837 /*++++++++++++++++++++++++++++++++++++++++++++++++++*/
838 IF g1_debug_mode = 'Y' THEN
839 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;
840 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
841 END IF;
842 IF g1_debug_mode = 'Y' THEN
843 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;
844 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
845 END IF;
846 IF g1_debug_mode = 'Y' THEN
847 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;
848 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
849 END IF;
850 ELSE
851 l_txn_raw_cost := l_raw_cost;
852 l_txn_burden_cost := l_burden_cost ;
853 l_txn_raw_revenue := l_raw_revenue;
854
855 END IF;/* IF ( (l_schedule_type='COST' and l_raw_cost IS NULL )OR (l_schedule_type='REVENUE' and l_raw_revenue IS NULL )*/
856
857 /* If Revenue or Cost is there that means rates must be associated to if computed by API
858 else pass the same cost and the override rate to the out parameters */
859
860 ELSE
861 l_txn_raw_cost := l_raw_cost;
862 l_txn_burden_cost := l_burden_cost ;
863 l_txn_raw_revenue := l_raw_revenue;
864 END IF;/* End of check for calling Resourtce Calss schedule */
865
866 END IF;/* IF p_raw_cost IS NOT NULL AND p_burden_cost IS NOT NULL AND p_raw_revenue IS NOT NULL THEN */
867 /* Assigning All the out parameters for the procedure */
868
869 IF g1_debug_mode = 'Y' THEN
870 pa_debug.g_err_stage:='Setting all the out parameters for thr procedure';
871 pa_debug.write('Get_planning_rates: ' || g_module_name,pa_debug.g_err_stage,3);
872 END IF;
873
874 x_bill_rate :=NVL(l_i_txn_bill_rate,l_txn_bill_rate);--4108291
875 x_cost_rate :=NVL(l_i_txn_cost_rate,l_txn_cost_rate) ;--4108291
876 x_burden_cost_rate :=NVL(l_i_txn_burden_cost_rate,l_txn_burden_cost_rate) ;
877 x_burden_multiplier :=NVL(l_i_txn_burden_multiplier,l_txn_burden_multiplier) ;--4108291
878 x_raw_cost :=l_txn_raw_cost;
879 x_burden_cost :=l_txn_burden_cost ;
880 x_raw_revenue :=l_txn_raw_revenue ;
881 x_bill_markup_percentage :=NVL(l_bill_markup,l_txn_bill_markup);
882 IF l_txn_raw_cost IS NOT NULL THEN
883 x_cost_txn_curr_code :=NVL(l_cost_txn_curr_code,l_cost_res_txn_curr_code) ;
884 END IF;
885 IF l_txn_raw_revenue IS NOT NULL THEN
886 x_rev_txn_curr_code :=NVL(l_rev_txn_curr_code,l_rev_res_txn_curr_code) ;
887 END IF;
888 x_raw_cost_rejection_code :=l_raw_cost_rejection_code ;
889 x_burden_cost_rejection_code :=l_burden_cost_rejection_code ;
890 x_revenue_rejection_code :=l_revenue_rejection_code;
891 x_cost_ind_compiled_set_id :=l_cost_ind_compiled_set_id ;
892 x_return_status :=l_x_return_status;
893 x_msg_data :=l_x_msg_data;
894 x_msg_count :=l_x_msg_count;
895 PA_DEBUG.reset_err_stack;
896 EXCEPTION
897 WHEN l_rate_based_no_quantity THEN
898 IF g1_debug_mode = 'Y' THEN
899 pa_debug.g_err_stage:='Quantity is required for a rate based transaction ';
900 pa_debug.write('Get_planning_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
901 END IF;
902 x_return_status := g_ERROR;
903 x_msg_count := 1;
904 x_msg_data := 'PA_EX_QTY_EXIST';
905 x_revenue_rejection_code := 'PA_EX_QTY_EXIST';
906 x_raw_cost_rejection_code := 'PA_EX_QTY_EXIST';
907 x_raw_revenue := NULL;
908 x_raw_cost := NULL;
909 x_bill_rate := NULL;
910 x_cost_rate := NULL;
911 x_cost_txn_curr_code := NULL ;
912 x_rev_txn_curr_code := NULL ;
913
914 PA_DEBUG.reset_err_stack;
915 WHEN l_invalid_currency THEN
916 IF g1_debug_mode = 'Y' THEN
917 pa_debug.g_err_stage:='Currecny Override is not entered for the ammounts entered';
918 pa_debug.write('Get_planning_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
919 END IF;
920 x_return_status := g_ERROR;
921 x_msg_count := 1;
922 x_msg_data := 'PA_INVALID_DENOM_CURRENCY';
923 x_revenue_rejection_code := 'PA_INVALID_DENOM_CURRENCY';
924 x_raw_cost_rejection_code := 'PA_INVALID_DENOM_CURRENCY';
925 x_raw_revenue := NULL ;
926 x_raw_cost := NULL ;
927 x_bill_rate := NULL ;
928 x_cost_rate := NULL ;
929 x_cost_txn_curr_code := NULL ;
930 x_rev_txn_curr_code := NULL ;
931
932 PA_DEBUG.reset_err_stack;
933 WHEN l_Get_planning_Rates_api THEN
934 IF g1_debug_mode = 'Y' THEN
935 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;
936 pa_debug.write('Get_planning_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
937 END IF;
938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
939 x_msg_count := 1;
940 x_msg_data := l_x_msg_data ;
941 x_revenue_rejection_code := l_revenue_rejection_code;
942 x_raw_cost_rejection_code := l_raw_cost_rejection_code;
943 x_burden_cost_rejection_code:=l_burden_cost_rejection_code;
944 x_raw_revenue := NULL;
945 x_raw_cost := NULL;
946 x_bill_rate := NULL;
947 x_cost_rate := NULL;
948 x_cost_txn_curr_code := NULL;
949 x_rev_txn_curr_code := NULL;
950 x_bill_markup_percentage := NULL;
951
952 PA_DEBUG.reset_err_stack;
953
954 WHEN OTHERS THEN
955 IF g1_debug_mode = 'Y' THEN
956 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;
957 pa_debug.write('Get_planing_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
958 END IF;
959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
960 x_msg_count := 1;
961 x_msg_data := 'Get_Planning_Rates'||SUBSTR(SQLERRM,1,250);
962 x_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
963 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
964 x_burden_cost_rejection_code:=SUBSTR(SQLERRM,1,30);
965 x_raw_revenue := NULL;
966 x_raw_cost := NULL;
967 x_bill_rate := NULL;
968 x_cost_rate := NULL;
969 x_cost_txn_curr_code := NULL;
970 x_rev_txn_curr_code := NULL;
971 x_bill_markup_percentage := NULL;
972
973 PA_DEBUG.reset_err_stack;
974
975 END Get_Planning_Rates;
976
977
978 --
979 -- Procedure : Get_plan_actual_Rates
980 -- Purpose : This is an internal procedure for calculating the bill rate and raw revenue from one of
981 -- the given criteria's on the basis of passed parameters for the 'ACTUAL RATES' of Planning Transaction
982 -- Parameters :
983 --
984
985
986 PROCEDURE Get_plan_actual_Rates (
987 p_project_id IN NUMBER,
988 p_task_id IN NUMBER DEFAULT NULL,
989 p_top_task_id IN NUMBER DEFAULT NULL, /* for costing top task Id */
990 p_person_id IN NUMBER,
991 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
992 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
993 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
994 p_planning_resource_format IN VARCHAR2 , /* resource format required for Costing */
995 p_use_planning_rates_flag IN VARCHAR2 DEFAULT 'N', /* Rate using Actual Rates */
996 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
997 p_uom IN VARCHAR2, /* Planning UOM */
998 p_system_linkage IN VARCHAR2,
999 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
1000 p_rev_task_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
1001 p_rev_proj_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
1002 p_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Job*/
1003 p_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Emp*/
1004 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) */
1005 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
1006 p_cost_rate_multiplier IN NUMBER DEFAULT NULL ,
1007 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
1008 p_quantity IN NUMBER, /* required param for People/Equipment Class */
1009 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
1010 p_cost_sch_type IN VARCHAR2 , /* Costing Schedule Type'COST' / 'REVENUE' / 'INVOICE' */
1011 p_labor_sch_type IN VARCHAR2, /* Revenue Labor Schedule Type B/I */
1012 p_non_labor_sch_type IN VARCHAR2 , /* Revenue Non_Labor Schedule Type B/I */
1013 p_labor_schdl_discnt IN NUMBER DEFAULT NULL,
1014 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL,
1015 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL,
1016 p_labor_schdl_fixed_date IN DATE DEFAULT NULL,
1017 p_assignment_id IN NUMBER DEFAULT NULL, /* used as p_item_id in the internal APIs */
1018 p_project_org_id IN NUMBER, /* Project Org Id */
1019 p_project_type IN VARCHAR2,
1020 p_expenditure_type IN VARCHAR2,
1021 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
1022 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
1023 p_override_to_organz_id IN NUMBER, /* Override Org Id */
1024 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
1025 p_assignment_precedes_task IN VARCHAR2, /* Added for Asgmt overide */
1026 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
1027 p_task_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at Project Level */
1028 p_project_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at task Level */
1029 p_nlr_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
1030 p_project_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at project Level */
1031 p_task_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at task Level */
1032 p_project_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at project Level */
1033 p_task_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at task Level */
1034 p_inventory_item_id IN NUMBER DEFAULT NULL, /* Passed for Inventoty Items */
1035 p_BOM_resource_Id IN NUMBER DEFAULT NULL, /* Passed for BOM Resource Id */
1036 P_mfc_cost_type_id IN NUMBER DEFAULT 0, /* Manufacturing cost api */
1037 P_item_category_id IN NUMBER DEFAULT NULL, /* Manufacturing cost api */
1038 p_mfc_cost_source IN NUMBER DEFAULT 1,
1039 p_cost_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to costing internal api.*/
1040 p_revenue_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to billing internal api.*/
1041 p_override_burden_cost_rate IN NUMBER DEFAULT NULL,
1042 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
1043 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*/
1044 p_raw_cost IN NUMBER, /*If p_raw_cost is only passed,return the burden multiplier, burden_cost */
1045 p_burden_cost IN NUMBER DEFAULT NULL,
1046 p_raw_revenue IN NUMBER DEFAULT NULL,
1047 x_bill_rate OUT NOCOPY NUMBER,
1048 x_cost_rate OUT NOCOPY NUMBER,
1049 x_burden_cost_rate OUT NOCOPY NUMBER,
1050 x_burden_multiplier OUT NOCOPY NUMBER,
1051 x_raw_cost OUT NOCOPY NUMBER,
1052 x_burden_cost OUT NOCOPY NUMBER,
1053 x_raw_revenue OUT NOCOPY NUMBER,
1054 x_bill_markup_percentage OUT NOCOPY NUMBER,
1055 x_cost_txn_curr_code OUT NOCOPY VARCHAR2,
1056 x_rev_txn_curr_code OUT NOCOPY VARCHAR2,
1057 x_raw_cost_rejection_code OUT NOCOPY VARCHAR2,
1058 x_burden_cost_rejection_code OUT NOCOPY VARCHAR2,
1059 x_revenue_rejection_code OUT NOCOPY VARCHAR2,
1060 x_cost_ind_compiled_set_id OUT NOCOPY NUMBER,
1061 x_return_status OUT NOCOPY VARCHAR2,
1062 x_msg_data OUT NOCOPY VARCHAR2,
1063 x_msg_count OUT NOCOPY NUMBER
1064 )
1065 IS
1066 l_raw_cost NUMBER :=NULL;
1067 l_burden_cost NUMBER :=NULL;
1068 l_raw_revenue NUMBER:=NULL;
1069 l_x_return_status VARCHAR2(2):= g_success;
1070 l_cost_msg_count NUMBER;
1071 l_cost_msg_data VARCHAR2(1000);
1072 l_bill_msg_count NUMBER;
1073 l_bill_msg_data VARCHAR2(1000);
1074 l_called_process VARCHAR2(40);
1075 l_txn_curr_code VARCHAR2(30);
1076 l_trxn_curr_code VARCHAR2(30);
1077 l_cost_txn_curr_code VARCHAR2(30);
1078 l_rev_txn_curr_code VARCHAR2(30);
1079 l_rev_curr_code VARCHAR2(30);
1080 l_txn_cost NUMBER:=NULL; /* to store the value of p_burden_cost or p_raw_cost */
1081 l_proj_nl_bill_rate_sch_id NUMBER;
1082 l_task_nl_bill_rate_sch_id NUMBER;
1083 l_txn_cost_rate NUMBER;
1084 l_txn_raw_cost_rate NUMBER;
1085 l_txn_burden_cost_rate NUMBER;
1086 l_txn_bill_rate NUMBER;
1087 l_txn_bill_markup NUMBER:=NULL;
1088 l_txn_raw_cost NUMBER;
1089 l_txn_burden_cost NUMBER;
1090 l_txn_raw_revenue NUMBER;
1091 l_sl_function NUMBER ;
1092 l_exp_func_Curr_code VARCHAR2(30);
1093 l_raw_cost_rate NUMBER ;
1094 l_burden_cost_rate NUMBER ;
1095 l_bill_rate NUMBER:=NULL;
1096 l_burden_multiplier NUMBER;
1097 l_raw_cost_rejection_code VARCHAR2(1000);
1098 l_burden_cost_rejection_code VARCHAR2(1000);
1099 l_cost_ind_compiled_set_id NUMBER;
1100 l_proj_cost_job_id NUMBER;
1101 l_expenditure_org_id NUMBER;
1102
1103
1104
1105 BEGIN
1106 l_raw_revenue := p_raw_revenue;
1107 l_raw_cost := p_raw_cost;
1108 l_burden_cost := p_burden_cost;
1109 IF upper(p_resource_class)='PEOPLE' THEN
1110 l_expenditure_org_id :=nvl(p_incurred_by_organz_id, p_override_to_organz_id );
1111 ELSE
1112 l_expenditure_org_id :=nvl(p_nlr_organization_id,p_override_to_organz_id );
1113 END IF;
1114
1115 IF p_system_linkage='BTC' THEN
1116 l_txn_cost := p_burden_cost;
1117 ELSE
1118 l_txn_cost := p_raw_cost;
1119 END IF;
1120 IF ((p_raw_cost IS NULL OR p_burden_cost IS NULL)
1121 AND p_calculate_mode IN ('COST','COST_REVENUE')) THEN
1122 IF g1_debug_mode = 'Y' THEN
1123 pa_debug.g_err_stage:='Before Calling PA_COST1.Get_Plan_Actual_Cost_Rates';
1124 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1125 END IF;
1126
1127 BEGIN
1128 PA_COST1.Get_Plan_Actual_Cost_Rates
1129 (p_calling_mode =>'ACTUAL_RATES'
1130 ,p_project_type =>p_project_type
1131 ,p_project_id =>p_project_id
1132 ,p_task_id =>p_task_id
1133 ,p_top_task_id =>p_top_task_id
1134 ,p_Exp_item_date =>p_item_date
1135 ,p_expenditure_type =>p_expenditure_type
1136 ,p_expenditure_OU =>p_expenditure_org_id
1137 ,p_project_OU =>p_project_org_id
1138 ,p_Quantity =>p_Quantity
1139 ,p_resource_class =>p_resource_class
1140 ,p_person_id =>p_person_id
1141 ,p_non_labor_resource =>p_non_labor_resource
1142 ,p_NLR_organization_id =>p_NLR_organization_id
1143 ,p_override_organization_id =>p_override_to_organz_id
1144 ,p_incurred_by_organization_id =>p_incurred_by_organz_id
1145 ,p_inventory_item_id =>p_inventory_item_id
1146 ,p_BOM_resource_id =>p_BOM_resource_id
1147 ,p_override_trxn_curr_code =>p_override_currency_code
1148 ,p_override_burden_cost_rate =>p_override_burden_cost_rate
1149 ,p_override_trxn_cost_rate =>p_cost_override_rate
1150 ,p_override_trxn_raw_cost =>p_raw_cost
1151 ,p_override_trxn_burden_cost =>p_burden_cost
1152 ,p_mfc_cost_type_id =>p_mfc_cost_type_id
1153 ,p_mfc_cost_source =>p_mfc_cost_source --check
1154 ,p_item_category_id =>p_item_category_id
1155 ,p_job_id =>p_job_id
1156 , p_plan_cost_job_rate_sch_id =>NULL
1157 , p_plan_cost_emp_rate_sch_id =>NULL
1158 , p_plan_cost_nlr_rate_sch_id =>NULL
1159 , p_plan_cost_burden_sch_id =>NULL
1160 ,x_trxn_curr_code =>l_trxn_curr_code
1161 ,x_trxn_raw_cost =>l_txn_raw_cost
1162 ,x_trxn_raw_cost_rate =>l_txn_cost_rate
1163 ,x_trxn_burden_cost =>l_txn_burden_cost
1164 ,x_trxn_burden_cost_rate =>l_txn_burden_cost_rate
1165 ,x_burden_multiplier =>l_burden_multiplier
1166 ,x_cost_ind_compiled_set_id =>l_cost_ind_compiled_set_id
1167 ,x_raw_cost_rejection_code =>l_raw_cost_rejection_code
1168 ,x_burden_cost_rejection_code =>l_burden_cost_rejection_code
1169 ,x_return_status =>l_x_return_status
1170 ,x_error_msg_code =>l_cost_msg_data ) ;
1171
1172 IF g1_debug_mode = 'Y' THEN
1173 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;
1174 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
1175 END IF;
1176 IF g1_debug_mode = 'Y' THEN
1177 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;
1178 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
1179 END IF;
1180 IF g1_debug_mode = 'Y' THEN
1181 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;
1182 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
1183 END IF;
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 x_msg_data := 'PA_COST1.Get_Plan_Actual_Cost_Rates:' || SUBSTR(SQLERRM,1,250);
1187 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
1188 x_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
1189 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1190
1191 IF g1_debug_mode = 'Y' THEN
1192 pa_debug.g_err_stage:=' PA_COST1.Get_Plan_Actual_Cost_Rates is throwing When Others';
1193 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
1194 END IF;
1195 RAISE;
1196 -- RAISE l_Get_plan_actual_Rates; /* Added to handle exception return by costing api */
1197 END;
1198 /* transferring the outout cost to one cost and checking if the costing API has computed Cost */
1199 IF p_system_linkage='BTC' THEN
1200 l_txn_cost := l_txn_burden_cost;
1201 ELSE
1202 l_txn_cost :=l_txn_raw_cost;
1203 END IF;
1204
1205
1206 ELSE
1207 /* If p_raw_cost and p_burden Cost are passed Costing API
1208 won't be called but the same value will be passed as it is */
1209 l_txn_raw_cost :=l_raw_cost ;
1210 l_txn_burden_cost :=l_burden_cost ;
1211 IF p_quantity <>0 THEN
1212 l_txn_cost_rate :=l_raw_cost/(NVL(p_quantity,1)) ;
1213 l_txn_burden_cost_rate :=l_burden_cost/(NVL(p_quantity,1)) ;
1214 END IF;
1215
1216 END IF;
1217
1218 /* Sending out all the out parametrs of Costing , This is send out here as even if the costing API has failed
1219 Revenue API will be called and revenue calculated if the required values are passed to the Billing API,
1220 though it'll pass the rejection code of Costing APi in the out parameters*/
1221 x_cost_rate := l_txn_cost_rate;
1222 x_burden_cost_rate := l_txn_burden_cost_rate;
1223 x_burden_multiplier := l_burden_multiplier ;
1224 x_raw_cost := l_txn_raw_cost;
1225 x_burden_cost := l_txn_burden_cost;
1226 x_cost_txn_curr_code := l_trxn_curr_code;
1227 x_raw_cost_rejection_code := l_raw_cost_rejection_code ;
1228 x_burden_cost_rejection_code := l_burden_cost_rejection_code;
1229 x_cost_ind_compiled_set_id := l_cost_ind_compiled_set_id;
1230 x_return_status := l_x_return_status ;
1231 x_msg_data := l_cost_msg_data ;
1232
1233 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
1234
1235 IF p_calculate_mode IN ('REVENUE','COST_REVENUE') THEN
1236
1237 /* Calling the Billing Revenue calculation Api only if p_raw_revenue is null */
1238 IF l_raw_revenue IS NULL THEN
1239 /* Checking for Rate based whether quantity is entered else */
1240 IF p_rate_based_flag ='Y' THEN
1241 null;
1242 ELSE
1243 IF NVL(l_txn_cost,0)=0 THEN
1244 IF p_quantity is NOT NULL and p_revenue_override_rate is not null then
1245 null;
1246 else
1247 RAISE l_no_cost;
1248 END IF;
1249 END IF;
1250 END IF;
1251
1252 IF g1_debug_mode = 'Y' THEN
1253 pa_debug.g_err_stage:='Calling Get_Plan_Actual_Rev_Rates';
1254 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1255 END IF;
1256
1257 Get_Plan_Actual_Rev_Rates (
1258 p_project_id => p_project_id ,
1259 p_task_id => p_task_id,
1260 p_person_id => p_person_id ,
1261 p_job_id => p_job_id,
1262 p_bill_job_grp_id => p_bill_job_grp_id,
1263 p_resource_class => p_resource_class,
1264 p_rate_based_flag => p_rate_based_flag,
1265 p_uom => p_uom,
1266 p_system_linkage => p_system_linkage,
1267 p_project_organz_id => p_project_organz_id,
1268 p_rev_proj_nl_rate_sch_id => p_rev_proj_nl_rate_sch_id ,
1269 p_rev_task_nl_rate_sch_id => p_rev_task_nl_rate_sch_id ,
1270 p_rev_job_rate_sch_id => p_rev_job_rate_sch_id,
1271 p_rev_emp_rate_sch_id => p_rev_emp_rate_sch_id ,
1272 p_mcb_flag => p_mcb_flag,
1273 p_bill_rate_multiplier => p_bill_rate_multiplier ,
1274 p_quantity => p_quantity ,
1275 p_item_date => p_item_date,
1276 p_labor_sch_type => p_labor_sch_type,
1277 p_labor_schdl_discnt => p_labor_schdl_discnt ,
1278 p_labor_bill_rate_org_id => p_labor_bill_rate_org_id ,
1279 p_labor_std_bill_rate_schdl => p_labor_std_bill_rate_schdl ,
1280 p_labor_schdl_fixed_date => p_labor_schdl_fixed_date ,
1281 p_assignment_id => p_assignment_id ,
1282 p_project_org_id => p_project_org_id ,
1283 p_project_type => p_project_type,
1284 p_expenditure_type => p_expenditure_type ,
1285 p_incurred_by_organz_id => p_incurred_by_organz_id ,
1286 p_override_to_organz_id => p_override_to_organz_id ,
1287 p_expenditure_org_id => l_expenditure_org_id, --p_expenditure_org_id ,
1288 p_assignment_precedes_task => p_assignment_precedes_task ,
1289 p_planning_transaction_id => p_planning_transaction_id,
1290 p_task_bill_rate_org_id => p_task_bill_rate_org_id,
1291 p_project_bill_rate_org_id => p_project_bill_rate_org_id ,
1292 p_non_labor_resource => p_non_labor_resource ,
1293 p_NLR_organization_id => p_NLR_organization_id ,
1294 p_non_labor_sch_type => p_non_labor_sch_type ,
1295 p_project_sch_date => p_project_sch_date ,
1296 p_task_sch_date => p_task_sch_date,
1297 p_project_sch_discount => p_project_sch_discount,
1298 p_task_sch_discount => p_task_sch_discount,
1299 p_revenue_override_rate => p_revenue_override_rate,
1300 p_override_currency_code => p_override_currency_code,
1301 p_txn_currency_code => l_trxn_curr_code ,
1302 p_raw_cost => l_txn_raw_cost,
1303 p_burden_cost => l_txn_burden_cost,
1304 p_raw_revenue => l_raw_revenue,
1305 p_raw_cost_rate => l_txn_cost_rate ,
1306 x_bill_rate => l_txn_bill_rate,
1307 x_raw_revenue => l_txn_raw_revenue,
1308 x_bill_markup_percentage => l_txn_bill_markup,
1309 x_txn_curr_code => l_rev_txn_curr_code,
1310 x_return_status => l_x_return_status,
1311 x_msg_data => l_bill_msg_data,
1312 x_msg_count => l_bill_msg_count
1313 );
1314 IF g1_debug_mode = 'Y' THEN
1315 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;
1316 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1317 END IF;
1318
1319 /* Raising the Billing Exception to pass the error values to the Main Api */
1320 IF l_x_return_status <> g_success THEN
1321 RAISE l_bill_api;
1322 END IF;
1323
1324 ELSE
1325 IF p_override_currency_Code IS NULL THEN
1326 RAISE l_invalid_currency;
1327 END IF;
1328 l_txn_raw_revenue :=l_raw_revenue ;
1329 IF p_quantity <>0 THEN
1330 l_txn_bill_rate :=l_raw_revenue/(NVL(p_quantity,1)) ;
1331 END IF;
1332 l_rev_txn_curr_code:=p_override_currency_Code;
1333 END IF;
1334 /* Passing the output parametrs of Billing for Revenue */
1335 END IF;/* End of IF p_calculate_mode IN ('REVENUE','COST_REVENUE') THEN */
1336
1337 x_raw_revenue :=l_txn_raw_revenue;
1338 x_bill_rate :=l_txn_bill_rate ;
1339 x_bill_markup_percentage :=l_txn_bill_markup;
1340 x_rev_txn_curr_code :=l_rev_txn_curr_code; /* x_txn_currency_code for Labor and x_rev_curr_code for non labor */
1341 x_revenue_rejection_code :=NULL;
1342 x_return_status :=l_x_return_status;
1343
1344 EXCEPTION
1345 WHEN l_invalid_currency THEN
1346
1347 IF g1_debug_mode = 'Y' THEN
1348 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;
1349 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1350 END IF;
1351 x_return_status := g_ERROR;
1352 x_msg_count := 1;
1353 x_msg_data := 'PA_INVALID_DENOM_CURRENCY';
1354 x_revenue_rejection_code := 'PA_INVALID_DENOM_CURRENCY';
1355 x_raw_cost_rejection_code := l_raw_cost_rejection_code;
1356 x_burden_cost_rejection_code := l_burden_cost_rejection_code;
1357 x_raw_revenue := NULL;
1358 x_bill_rate := NULL;
1359 x_rev_txn_curr_code :=NULL ;
1360
1361 WHEN l_no_cost THEN
1362 x_raw_revenue := NULL;
1363 x_bill_rate := NULL;
1364 x_bill_markup_percentage := NULL;
1365 x_rev_txn_curr_code := NULL;
1366 x_revenue_rejection_code := 'PA_NO_ACCT_COST';
1367 x_return_status := g_error;
1368 x_msg_data := 'PA_NO_ACCT_COST';
1369 x_msg_count := 1;
1370
1371
1372 IF g1_debug_mode = 'Y' THEN
1373 pa_debug.g_err_stage:='No Cost exist for the tranascation:p_project_id'||p_project_id||'p_task_id'||p_task_id;
1374 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1375 END IF;
1376 WHEN l_bill_api THEN
1377 x_raw_revenue := NULL;
1378 x_bill_rate := NULL;
1379 x_bill_markup_percentage := NULL;
1380 x_rev_txn_curr_code := NULL;
1381 x_revenue_rejection_code := l_bill_msg_data;
1382 x_return_status := l_x_return_status;
1383 x_msg_data := l_bill_msg_data;
1384 x_msg_count := l_bill_msg_count;
1385
1386 IF g1_debug_mode = 'Y' THEN
1387 pa_debug.g_err_stage:='Billing api is throwing error';
1388 pa_debug.write('Get_plan_actual_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1389 END IF;
1390
1391
1392 END Get_plan_actual_Rates;
1393
1394
1395 -- Procedure : Get_plan_actual_RevRates
1396 -- Purpose : This is an internal procedure for calculating the bill rate and raw revenue from one of
1397 -- the given criteria's on the basis of passed parameters for the 'ACTUAL RATES' of Planning Transaction
1398 -- Parameters :
1399 --
1400
1401 PROCEDURE Get_Plan_Actual_Rev_Rates (
1402 p_project_id IN NUMBER,
1403 p_task_id IN NUMBER DEFAULT NULL,
1404 p_person_id IN NUMBER,
1405 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
1406 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
1407 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
1408 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
1409 p_uom IN VARCHAR2, /* Planning UOM */
1410 p_system_linkage IN VARCHAR2,
1411 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
1412 p_rev_proj_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
1413 p_rev_task_nl_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Non Labor*/
1414 p_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Job*/
1415 p_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Emp*/
1416 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
1417 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
1418 p_quantity IN NUMBER, /* required param for People/Equipment Class */
1419 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
1420 p_labor_sch_type IN VARCHAR2, /* Revenue Labor Schedule Type B/I */
1421 p_labor_schdl_discnt IN NUMBER DEFAULT NULL,
1422 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL,
1423 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL,
1424 p_labor_schdl_fixed_date IN DATE DEFAULT NULL,
1425 p_assignment_id IN NUMBER DEFAULT NULL, /* used as p_item_id in the internal APIs */
1426 p_project_org_id IN NUMBER, /* Project Org Id */
1427 p_project_type IN VARCHAR2,
1428 p_expenditure_type IN VARCHAR2,
1429 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
1430 p_override_to_organz_id IN NUMBER, /* Override Org Id */
1431 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
1432 p_assignment_precedes_task IN VARCHAR2, /* Added for Asgmt overide */
1433 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
1434 p_task_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at Project Level */
1435 p_project_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Org Id of the Bill Rate at task Level */
1436 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
1437 p_NLR_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
1438 p_non_labor_sch_type IN VARCHAR2 , /* Revenue Non_Labor Schedule Type B/I */
1439 p_project_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at project Level */
1440 p_task_sch_date IN DATE DEFAULT NULL, /* Revenue Non_Labor Schedule Date at task Level */
1441 p_project_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at project Level */
1442 p_task_sch_discount IN NUMBER DEFAULT NULL , /* Revenue Non_Labor Schedule Discount at task Level */
1443 p_revenue_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to billing internal api.*/
1444 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
1445 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*/
1446 p_raw_cost IN NUMBER, /*If p_raw_cost is only passed,return the burden multiplier, burden_cost */
1447 p_burden_cost IN NUMBER DEFAULT NULL,
1448 p_raw_revenue IN NUMBER DEFAULT NULL,
1449 p_raw_cost_rate IN NUMBER DEFAULT NULL,
1450 x_bill_rate OUT NOCOPY NUMBER,
1451 x_raw_revenue OUT NOCOPY NUMBER,
1452 x_bill_markup_percentage OUT NOCOPY NUMBER,
1453 x_txn_curr_code OUT NOCOPY VARCHAR2, /* x_txn_currency_code for Labor and x_rev_curr_code for non labor */
1454 x_return_status OUT NOCOPY VARCHAR2,
1455 x_msg_data OUT NOCOPY VARCHAR2,
1456 x_msg_count OUT NOCOPY NUMBER
1457 )
1458 IS
1459 l_x_return_status VARCHAR2(20):=g_success;
1460 l_msg_count NUMBER;
1461 l_msg_data VARCHAR2(1000);
1462 l_called_process VARCHAR2(40);
1463 l_txn_curr_code VARCHAR2(30);
1464 l_rev_curr_code VARCHAR2(30);
1465 l_override_cost NUMBER:=NULL;
1466 l_proj_nl_bill_rate_sch_id NUMBER;
1467 l_task_nl_bill_rate_sch_id NUMBER;
1468 l_txn_bill_rate NUMBER:=NULL;
1469 l_txn_bill_markup NUMBER:=NULL;
1470 l_raw_revenue NUMBER:=NULL;
1471 l_txn_raw_revenue NUMBER;
1472 l_sl_function NUMBER ;
1473 l_exp_func_Curr_code VARCHAR2(30);
1474 l_project_curr_code VARCHAR2(30);
1475 l_projfunc_curr_code VARCHAR2(30);
1476 l_project_raw_cost NUMBER;
1477 l_project_raw_cost_rate NUMBER;
1478 l_project_burdened_cost NUMBER;
1479 l_project_burdened_cost_rate NUMBER;
1480 l_projfunc_raw_cost NUMBER;
1481 l_projfunc_raw_cost_rate NUMBER;
1482 l_projfunc_burdened_cost NUMBER;
1483 l_projfunc_burdened_cost_rate NUMBER;
1484 l_convert_return_status VARCHAR2(30):= g_success;
1485 l_error_msg_code VARCHAR2(2000);
1486 l_uom VARCHAR2(30);
1487 l_uom_flag NUMBER:=1;
1488 l_txn_adjusted_bill_rate NUMBER:=NULL;--4038485
1489 l_quantity NUMBER:=NULL; --bug#4284806
1490
1491 BEGIN
1492 IF g1_debug_mode = 'Y' THEN
1493 pa_debug.g_err_stage:='Validating all the input parameters';
1494 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1495 END IF;
1496
1497
1498 IF p_system_linkage='BTC' THEN
1499 l_override_cost := p_burden_cost;
1500 l_sl_function :=6;
1501 ELSE
1502 l_override_cost := p_raw_cost;
1503 l_sl_function :=2;
1504 END IF;
1505
1506 IF p_revenue_override_rate IS NOT NULL AND p_override_currency_code IS NULL THEN
1507 IF g1_debug_mode = 'Y' THEN
1508 pa_debug.g_err_stage:='Validating Get_plan_actual_rev_Rates:p_override_currency_code is required if passing any overrides';
1509 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1510 END IF;
1511 RAISE l_invalid_currency;
1512 END IF;
1513 IF p_rate_based_flag ='Y' AND p_quantity IS NULL
1514 AND NVL(l_override_cost,0)=0 THEN
1515 IF g1_debug_mode = 'Y' THEN
1516 pa_debug.g_err_stage:='Validating Get_plan_actual_rev_Rates:p_quantity is required for rate based';
1517 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1518 END IF;
1519 RAISE l_rate_based_no_quantity;
1520 END IF;
1521 /* If revenue Override rate is not null compute the raw_revenue based on the override rate and the p_quantity or rawCost */
1522 IF p_revenue_override_rate IS NOT NULL THEN
1523 SELECT p_revenue_override_rate b_rate,
1524 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((p_revenue_override_rate * p_quantity), p_override_currency_code) r_revenue,
1525 p_override_currency_code
1526 INTO l_txn_bill_rate,l_raw_revenue,l_txn_curr_code
1527 FROM dual;
1528 l_txn_raw_revenue:=l_raw_revenue;
1529
1530 END IF;/* End of check for p_revenue_override_rate */
1531 /* If in the above case the Raw Revenue is null then go for calling
1532 actual internal api of billing to compute the raw Revenue */
1533
1534 IF l_raw_revenue IS NULL THEN
1535
1536 IF p_task_Id IS NOT NULL THEN
1537 l_called_process := 'TASK_LEVEL_PLANNING';
1538 ELSE
1539 l_called_process := 'PROJECT_LEVEL_PLANNING';
1540 END IF;
1541 /*bug#4284806 If the transaction is non rate based which means the UOM is currency , then during revenue rate derivation ,
1542 for both actuals and resource class and for both labor ( people ) and non labor resource class you should ignore rate,
1543 if the setup is rate.
1544
1545 So setting the p_quanity to null for non-rate based transaction*/
1546
1547 IF p_rate_based_flag ='Y' THEN
1548 l_quantity:=p_quantity;
1549 else
1550 l_quantity:=null;
1551 end if;
1552
1553 /* Going to Call the Core Billing API for Revenue Calculation based on the Resource Class */
1554 IF p_resource_class='PEOPLE' THEN
1555 IF g1_debug_mode = 'Y' THEN
1556 pa_debug.g_err_stage:='Calling PA_REVENUE.Assignment_Rev_Amt';
1557 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1558 END IF;
1559
1560 PA_REVENUE.Assignment_Rev_Amt(
1561 p_project_id => p_project_id ,
1562 p_task_id => p_task_id ,
1563 p_bill_rate_multiplier => p_bill_rate_multiplier ,
1564 p_quantity => l_quantity ,
1565 p_person_id => p_person_id ,
1566 p_raw_cost => p_raw_cost,
1567 p_item_date => p_item_date ,
1568 p_labor_schdl_discnt => p_labor_schdl_discnt ,
1569 p_labor_bill_rate_org_id => p_labor_bill_rate_org_id ,
1570 p_labor_std_bill_rate_schdl => p_labor_std_bill_rate_schdl ,
1571 p_labor_schdl_fixed_date => p_labor_schdl_fixed_date ,
1572 p_bill_job_grp_id => p_bill_job_grp_id ,
1573 p_item_id => p_assignment_id ,
1574 p_forecast_item_id => p_planning_transaction_id ,
1575 p_labor_sch_type => p_labor_sch_type ,
1576 p_project_org_id => p_project_org_id ,
1577 p_project_type => p_project_type ,
1578 p_expenditure_type => p_expenditure_type ,
1579 p_exp_func_curr_code => p_txn_currency_code ,
1580 p_incurred_by_organz_id => p_incurred_by_organz_id ,
1581 p_raw_cost_rate => p_raw_cost_rate ,
1582 p_override_to_organz_id => p_override_to_organz_id ,
1583 p_emp_bill_rate_schedule_id => p_rev_emp_rate_sch_id ,
1584 p_job_bill_rate_schedule_id => p_rev_job_rate_sch_id,
1585 p_resource_job_id => p_job_id ,
1586 p_exp_raw_cost => p_raw_cost ,
1587 p_expenditure_org_id => p_expenditure_org_id ,
1588 p_projfunc_currency_code => p_txn_currency_code,
1589 p_assignment_precedes_task => p_assignment_precedes_task ,
1590 p_sys_linkage_function => p_system_linkage,
1591 p_called_process => l_called_process ,
1592 p_project_raw_cost => l_project_raw_cost,
1593 p_project_currency_code => p_txn_currency_code,
1594 p_denom_raw_cost => p_raw_cost,
1595 p_denom_curr_code => p_txn_currency_code,
1596 p_mcb_flag => NULL,
1597 x_bill_rate => l_txn_bill_rate ,
1598 x_adjusted_bill_rate => l_txn_adjusted_bill_rate , --4038485
1599 x_raw_revenue => l_txn_raw_revenue,
1600 x_markup_percentage => l_txn_bill_markup ,
1601 x_txn_currency_code => l_txn_curr_code,
1602 x_rev_currency_code => l_rev_curr_code ,
1603 x_return_status => l_x_return_status ,
1604 x_msg_count => l_msg_count ,
1605 x_msg_data => l_msg_data );
1606 IF g1_debug_mode = 'Y' THEN
1607 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;
1608 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1609 END IF;
1610
1611
1612 if NVL(p_rate_based_flag,'N') = 'N' then
1613 l_txn_bill_rate := NULL;
1614 l_txn_adjusted_bill_rate:=NULL;
1615 end if;
1616 /* Rasising the Billing Exception to pass the error values to the Main Api */
1617 IF l_x_return_status <> g_success THEN
1618 RAISE l_bill_api;
1619 END IF;
1620
1621 ELSE
1622
1623 /* Expenditure Type is checked for UOM determination and to calculate p_uom_flag */
1624 IF g1_debug_mode = 'Y' THEN
1625 pa_debug.g_err_stage:='========g_expenditure_type_tbl.COUNT'||g_expenditure_type_tbl.COUNT;
1626 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1627 END IF;
1628
1629 FOR i IN g_expenditure_type_tbl.FIRST .. g_expenditure_type_tbl.LAST LOOP
1630 IF p_expenditure_type =g_expenditure_type_tbl(i) THEN
1631 IF g1_debug_mode = 'Y' THEN
1632 pa_debug.g_err_stage:='expendirure Type is::::'|| g_expenditure_type_tbl(i) ;
1633 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1634 END IF;
1635 IF g_uom_tbl.EXISTS(i) THEN
1636 l_uom := g_uom_tbl(i);
1637 END IF;
1638 EXIT;
1639 END IF;
1640 END LOOP;
1641
1642 IF l_uom<>p_uom THEN
1643 l_uom_flag:=0 ;
1644 ELSE
1645 l_uom_flag:=1 ;
1646 END IF;
1647 IF g1_debug_mode = 'Y' THEN
1648 pa_debug.g_err_stage:='l_uom is::::::'|| l_uom||'p_uom '||p_uom||'l_uom_flag'||l_uom_flag ;
1649 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1650 END IF;
1651
1652
1653 IF g1_debug_mode = 'Y' THEN
1654 pa_debug.g_err_stage:='Before Calling pa_revenue.Non_Labor_Rev_amount';
1655 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1656 END IF;
1657
1658 pa_revenue.Non_Labor_Rev_amount(
1659 p_called_process =>l_called_process ,
1660 p_project_id => p_project_id,
1661 p_task_id => p_task_id,
1662 p_bill_rate_multiplier => p_bill_rate_multiplier,
1663 p_quantity => l_quantity,
1664 p_raw_cost => p_raw_cost,
1665 p_burden_cost => p_burden_cost,
1666 p_denom_raw_cost => p_raw_cost,
1667 p_denom_burdened_cost => p_burden_cost,
1668 p_expenditure_item_date => p_item_date,
1669 p_task_bill_rate_org_id => p_task_bill_rate_org_id ,
1670 p_project_bill_rate_org_id => p_project_bill_rate_org_id ,
1671 p_task_nl_std_bill_rate_sch_id => p_rev_task_nl_rate_sch_id,
1672 p_proj_nl_std_bill_rate_sch_id => p_rev_proj_nl_rate_sch_id,
1673 p_project_org_id => p_project_org_id,
1674 p_sl_function => l_sl_function,
1675 p_denom_currency_code => p_txn_currency_code,
1676 p_proj_func_currency => p_txn_currency_code,
1677 p_proj_func_burdened_cost => p_burden_cost,
1678 p_expenditure_type => p_expenditure_type,
1679 p_non_labor_resource => p_non_labor_resource,
1680 p_task_sch_date => p_task_sch_date ,
1681 p_project_sch_date => p_project_sch_date ,
1682 p_project_sch_discount => p_project_sch_discount,
1683 p_task_sch_discount => p_task_sch_discount,
1684 p_mcb_flag => NULL,--p_mcb_flag,
1685 p_uom_flag => l_uom_flag,
1686 p_non_labor_sch_type => p_non_labor_sch_type,
1687 p_project_type => p_project_type,
1688 p_exp_raw_cost => p_raw_cost,
1689 p_raw_cost_rate => p_raw_cost_rate,
1690 p_incurred_by_organz_id => p_incurred_by_organz_id,
1691 p_override_to_organz_id => p_override_to_organz_id,
1692 px_exp_func_curr_code => l_exp_func_Curr_code,
1693 x_raw_revenue => l_txn_raw_revenue,
1694 x_rev_curr_code => l_txn_Curr_code,
1695 x_bill_rate => l_txn_bill_rate,
1696 x_adjusted_bill_rate => l_txn_adjusted_bill_rate , --4038485
1697 x_markup_percentage => l_txn_bill_markup,
1698 x_return_status => l_x_return_status,
1699 x_msg_count => l_msg_count,
1700 x_msg_data => l_msg_data);
1701 IF g1_debug_mode = 'Y' THEN
1702 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;
1703 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1704 END IF;
1705 if NVL(p_rate_based_flag,'N') = 'N' then
1706 l_txn_bill_rate := NULL;
1707 l_txn_adjusted_bill_rate:=NULL;
1708 end if;
1709 /* Raising the Billing Exception to pass the error values to the Main Api */
1710 IF l_x_return_status <> g_success THEN
1711 RAISE l_bill_api;
1712 END IF;
1713
1714 END IF;/*IF p_resource_class='PEOPLE' THEN */
1715 END IF;/* End if of l_raw_revenue IS NULL THEN*/
1716
1717 IF g1_debug_mode = 'Y' THEN
1718 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;
1719 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1720 END IF;
1721 x_raw_revenue :=l_txn_raw_revenue;
1722 x_bill_rate :=NVL(l_txn_adjusted_bill_rate,l_txn_bill_rate) ;--4038485
1723 x_bill_markup_percentage :=l_txn_bill_markup;
1724 x_txn_curr_code :=l_txn_curr_code;
1725 x_return_status :=l_x_return_status;
1726
1727 EXCEPTION
1728 WHEN l_bill_api THEN
1729 x_raw_revenue :=NULL;
1730 x_bill_rate :=NULL;
1731 x_bill_markup_percentage :=NULL;
1732 x_txn_curr_code :=NULL;
1733 x_return_status := g_error;
1734 x_msg_data := l_msg_data;
1735 x_msg_count := l_msg_count;
1736
1737 IF g1_debug_mode = 'Y' THEN
1738 pa_debug.g_err_stage:='Billing api is throwing error :p_project_id'||p_project_id||'p_task_id'||p_task_id;
1739 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1740 END IF;
1741
1742 WHEN l_rate_based_no_quantity THEN
1743 x_raw_revenue :=NULL;
1744 x_bill_rate :=NULL;
1745 x_bill_markup_percentage :=NULL;
1746 x_txn_curr_code :=NULL;
1747 x_return_status := g_error;
1748 x_msg_data := 'PA_EX_QTY_EXIST';
1749 x_msg_count := 1;
1750
1751 IF g1_debug_mode = 'Y' THEN
1752 pa_debug.g_err_stage:='Quantity is not passed to a rate based tranaction to the Get_Plan_Actual_Rev_Rates call';
1753 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1754 END IF;
1755 WHEN l_invalid_currency THEN
1756 x_raw_revenue :=NULL;
1757 x_bill_rate :=NULL;
1758 x_bill_markup_percentage :=NULL;
1759 x_txn_curr_code :=NULL;
1760 x_return_status := g_error;
1761 x_msg_data := 'PA_INVALID_DENOM_CURRENCY';
1762 x_msg_count := 1;
1763
1764 IF g1_debug_mode = 'Y' THEN
1765 pa_debug.g_err_stage:='Override Currency is not passed to a rate based tranaction to the Get_Plan_Actual_Rev_Rates call';
1766 pa_debug.write('Get_plan_actual_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
1767 END IF;
1768
1769
1770 END Get_Plan_Actual_Rev_Rates;
1771
1772 --
1773 -- Procedure : Get_plan_res_class_rates
1774 -- Purpose :This procedure will calculate the raw revenue and bill amount from one of the 12
1775 -- criterias on the basis of passed parameters
1776 -- Parameters :
1777 --
1778
1779 PROCEDURE Get_plan_res_class_rates (
1780 p_project_type IN VARCHAR2,
1781 p_project_id IN NUMBER,
1782 p_task_id IN NUMBER DEFAULT NULL,
1783 p_person_id IN NUMBER,
1784 p_job_id IN NUMBER DEFAULT NULL,
1785 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
1786 p_use_planning_rates_flag IN VARCHAR2 DEFAULT 'N', /* Rate using Actual Rates */
1787 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
1788 p_uom IN VARCHAR2, /* Planning UOM */
1789 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calculation use in Resource Class Sch carrying out*/
1790 p_rev_res_class_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on Resource Class*/
1791 p_cost_res_class_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on Resource Class*/
1792 p_plan_burden_cost_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on burdening for planning*/
1793 p_cost_rate_multiplier IN NUMBER DEFAULT 1, /* p_bill_rate_multiplier or p_cost_rate_multiplier */
1794 p_bill_rate_multiplier IN NUMBER DEFAULT 1, /* p_bill_rate_multiplier or p_cost_rate_multiplier */
1795 p_quantity IN NUMBER,
1796 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
1797 p_schedule_type IN VARCHAR2 DEFAULT NULL, /* REVENUE OR COST OR NULL , will calculate both cost and Revenue*/
1798 p_project_org_id IN NUMBER,
1799 p_incurred_by_organz_id IN NUMBER,
1800 p_override_to_organz_id IN NUMBER,
1801 p_expenditure_org_id IN NUMBER,
1802 p_nlr_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
1803 p_override_trxn_cost_rate IN NUMBER DEFAULT NULL, /*p_override_trxn_cost_rate for costing */
1804 p_override_burden_cost_rate IN NUMBER DEFAULT NULL, /*override burden multiplier and p_raw_cost is not null calculate x_burden_cost */
1805 p_override_trxn_bill_rate IN NUMBER DEFAULT NULL, /*p_override_trxn_bill_rate or for billing */
1806 p_override_txn_currency_code IN VARCHAR2 DEFAULT NULL,
1807 p_txn_currency_code IN VARCHAR2,
1808 p_raw_cost IN NUMBER,
1809 p_burden_cost IN NUMBER DEFAULT NULL,
1810 p_raw_revenue IN NUMBER DEFAULT NULL,
1811 p_system_linkage IN VARCHAR2,
1812 p_expenditure_type IN VARCHAR2,
1813 x_bill_rate OUT NOCOPY NUMBER,
1814 x_cost_rate OUT NOCOPY NUMBER,
1815 x_burden_cost_rate OUT NOCOPY NUMBER,
1816 x_burden_multiplier OUT NOCOPY NUMBER,
1817 x_raw_cost OUT NOCOPY NUMBER,
1818 x_burden_cost OUT NOCOPY NUMBER,
1819 x_raw_revenue OUT NOCOPY NUMBER,
1820 x_bill_markup_percentage OUT NOCOPY NUMBER,
1821 x_cost_markup_percentage OUT NOCOPY NUMBER,
1822 x_cost_txn_curr_code OUT NOCOPY VARCHAR2,
1823 x_rev_txn_curr_code OUT NOCOPY VARCHAR2,
1824 x_raw_cost_rejection_code OUT NOCOPY VARCHAR2,
1825 x_burden_cost_rejection_code OUT NOCOPY VARCHAR2,
1826 x_revenue_rejection_code OUT NOCOPY VARCHAR2,
1827 x_cost_ind_compiled_set_id OUT NOCOPY NUMBER,
1828 x_return_status OUT NOCOPY VARCHAR2,
1829 x_msg_count OUT NOCOPY NUMBER,
1830 x_msg_data OUT NOCOPY VARCHAR2)
1831
1832 IS
1833
1834 l_x_return_status VARCHAR2(20):=g_success; -- store the return status
1835 l_bill_rate NUMBER:=NULL;
1836 l_cost_rate NUMBER:=NULL;
1837 l_adjust_amount NUMBER :=null;
1838 l_markup NUMBER :=null;
1839 l_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE;
1840 l_raw_revenue NUMBER :=null; -- store the raw revenue
1841 l_true BOOLEAN := FALSE;
1842 l_txn_raw_revenue NUMBER :=null; -- store the raw revenue trans. curr.
1843 l_raw_cost number := null;
1844 l_burden_cost number := null;
1845 l_txn_currency_code pa_bill_rates_all.rate_currency_code%TYPE;
1846 l_inter_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE;--4194214
1847 l_burden_cost_rate NUMBER:=NULL;
1848 l_cost_ind_compiled_set_id NUMBER;
1849 l_burd_organization_id NUMBER;
1850 l_x_msg_data VARCHAR2(1000);
1851 l_x_msg_count NUMBER;
1852 l_schedule_type VARCHAR2(60);
1853 l_burd_sch_id NUMBER;
1854 l_burd_sch_rev_id NUMBER;
1855 l_burd_sch_fixed_date DATE;
1856 l_burd_sch_cost_base VARCHAR2(1000);
1857 l_burd_sch_cp_structure VARCHAR2(1000);
1858 l_txn_burden_multiplier NUMBER :=null;
1859 l_cost_return_status VARCHAR2(1):=g_success;
1860 l_cost_msg_data VARCHAR2(1000);
1861 l_txn_burden_cost_rate NUMBER :=null;
1862 l_cost_txn_curr_code VARCHAR2(50);
1863 l_txn_burden_cost NUMBER :=null;
1864 l_txn_raw_cost NUMBER :=null;
1865 l_txn_cost_rate NUMBER:=NULL;
1866 l_burden_cost_rejection_code VARCHAR2(50);
1867 l_bill_txn_curr_code pa_bill_rates_all.rate_currency_code%TYPE;
1868 l_inter_return_status VARCHAR2(1):=null;
1869 l_calling_mode VARCHAR2(20):=null;
1870 l_override_organization_id NUMBER;
1871 l_ovr_return_status VARCHAR2(20):=g_success;
1872 l_ovr_msg_count NUMBER;
1873 l_ovr_msg_data VARCHAR2(1000);
1874
1875 BEGIN
1876
1877 pa_debug.init_err_stack('PA_PLAN_REVENUE.Get_plan_res_class_rates');
1878
1879 IF g1_debug_mode = 'Y' THEN
1880 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;
1881 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
1882 END IF;
1883 IF g1_debug_mode = 'Y' THEN
1884 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 ;
1885 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
1886 END IF;
1887 IF g1_debug_mode = 'Y' THEN
1888 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;
1889 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
1890 END IF;
1891
1892 IF g1_debug_mode = 'Y' THEN
1893 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 ;
1894 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
1895 END IF;
1896 /* Assigning the parameters to local variables */
1897 l_burden_cost :=p_burden_cost;
1898 l_raw_cost := p_raw_cost;
1899 l_cost_rate := p_override_trxn_cost_rate;
1900 l_bill_rate := p_override_trxn_bill_rate;
1901 l_override_organization_id :=p_override_to_organz_id;
1902 l_txn_curr_code :=p_txn_currency_code; --bug#4317221
1903 if p_use_planning_rates_flag ='Y' then
1904 l_calling_mode :='PLAN_RATES';
1905 else
1906 l_calling_mode :='ACTUAL_RATES';
1907 end if;
1908
1909 l_raw_revenue :=p_raw_revenue;
1910 /* Validating all the required Parameters */
1911 IF p_resource_class IS NULL OR p_item_date IS NULL
1912 OR p_uom IS NULL THEN
1913 IF g1_debug_mode = 'Y' THEN
1914 pa_debug.g_err_stage:='validating Get_plan_res_class_rates: either p_resource_class or p_item_date or p_uom is null ';
1915 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
1916 END IF;
1917 RAISE l_insufficeient_param;
1918 ELSE
1919 null;
1920 END IF;/* Check for mandatory parameters done */
1921
1922 /* First computing the raw_cost if p_schedule_type is passed COST or NULL
1923 If COST is passed then it'll compute only Costing but if passed nll it'll
1924 compute both Costing as well as Revenue */
1925 IF g1_debug_mode = 'Y' THEN
1926 pa_debug.g_err_stage:='Calculating Costing data';
1927 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
1928 END IF;
1929
1930 IF l_override_organization_id is NULL and p_resource_class = 'PEOPLE' Then
1931 IF g1_debug_mode = 'Y' THEN
1932 pa_debug.g_err_stage:='Calling pa_cost.Override_exp_organization api';
1933 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
1934 end if;
1935 BEGIN
1936 pa_cost.Override_exp_organization
1937 (P_item_date => p_item_date
1938 ,P_person_id => p_person_id
1939 ,P_project_id => p_project_id
1940 ,P_incurred_by_organz_id => p_incurred_by_organz_id
1941 ,P_Expenditure_type => p_expenditure_type
1942 ,X_overr_to_organization_id => l_override_organization_id
1943 ,X_return_status => l_ovr_return_status
1944 ,X_msg_count => l_ovr_msg_count
1945 ,X_msg_data => l_ovr_msg_data
1946 );
1947 EXCEPTION
1948 WHEN OTHERS THEN
1949 IF g1_debug_mode = 'Y' THEN
1950 pa_debug.g_err_stage:='pa_cost.Override_exp_organization is throwing When Others'||SUBSTR(SQLERRM,1,250);
1951 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
1952 END IF;
1953 END;
1954
1955 IF g1_debug_mode = 'Y' THEN
1956 pa_debug.g_err_stage:='Return status of pa_cost.Override_exp_organization ['||l_ovr_return_status||
1957 ']msgData['||l_ovr_msg_data||']OverideOrg['||l_override_organization_id||']' ;
1958 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
1959 end if;
1960
1961 End If;
1962
1963 IF NVL(p_schedule_type,'COST')='COST' THEN
1964 --13.02
1965 IF l_cost_rate IS NOT NULL AND l_raw_cost IS NULL THEN
1966 IF NVL(p_rate_based_flag,'N')='Y' THEN
1967 l_raw_cost :=l_cost_rate*p_quantity;
1968 END IF;
1969 END IF;
1970 --13.02
1971 IF l_raw_cost IS NULL THEN
1972 IF p_rate_based_flag ='Y' THEN
1973 IF p_quantity IS NULL THEN
1974 IF g1_debug_mode = 'Y' THEN
1975 pa_debug.g_err_stage:='validating Get_plan_res_class_rates: p_quantity is required for rate based ';
1976 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
1977 END IF;
1978 RAISE l_rate_based_no_quantity;
1979 END IF;
1980 END IF;
1981 BEGIN
1982 DECLARE
1983 l_class_org_rate NUMBER:=NULL;
1984 l_class_org_markup NUMBER:=NULL;
1985 l_class_org_uom pa_bill_rates_all.bill_rate_unit%TYPE :=NULL;
1986 l_class_org_rate_curr_code pa_bill_rates_all.rate_currency_code%TYPE :=NULL;
1987 l_class_org_return_status VARCHAR2(20):= g_success;
1988 l_class_org_return_data VARCHAR2(30);
1989 l_class_org_return_count NUMBER;
1990 l_item_date DATE := p_item_date;
1991 l_res_class_org_id NUMBER := NVL (l_override_organization_id ,NVL(p_incurred_by_organz_id,p_project_organz_id));
1992 BEGIN
1993 IF g1_debug_mode = 'Y' THEN
1994 pa_debug.g_err_stage:='Callling Get_Res_Class_Hierarchy_Rate:p_project_id'||p_project_id||'p_task_id'||p_task_id;
1995 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
1996 END IF;
1997 Get_Res_Class_Hierarchy_Rate( p_res_class_rate_sch_id => p_cost_res_class_rate_sch_id,
1998 p_item_date => l_item_date,
1999 p_org_id => p_project_org_id ,
2000 p_resource_class_code => p_resource_class,
2001 p_res_class_org_id => l_res_class_org_id,
2002 x_rate => l_class_org_rate ,
2003 x_markup_percentage => l_class_org_markup,
2004 x_uom => l_class_org_uom,
2005 x_rate_currency_code => l_class_org_rate_curr_code,
2006 x_return_status => l_class_org_return_status,
2007 x_msg_count => l_class_org_return_count,
2008 x_msg_data => l_class_org_return_data);
2009
2010 /* Checking the status if the above proc has return no rate then no need to call the block*/
2011 IF l_class_org_return_status = g_success THEN
2012
2013 DECLARE
2014
2015 CURSOR C_std_res_class_sch_cost IS
2016 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,
2017 DECODE (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(p_quantity ,l_class_org_rate_curr_code)
2018 ,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)
2019 * p_quantity, l_class_org_rate_curr_code),null)
2020 ) r_cost,
2021 l_class_org_rate_curr_code rate_currency_code
2022 FROM dual;
2023
2024
2025 BEGIN
2026 -- Opening cursor and fetching row
2027 OPEN C_std_res_class_sch_cost ;
2028 -- Assigning the Calculated raw revenue/adjusted to the local variable
2029 FETCH C_std_res_class_sch_cost INTO l_cost_rate,l_raw_cost,l_txn_curr_code;
2030 CLOSE C_std_res_class_sch_cost ;
2031
2032 IF g1_debug_mode = 'Y' THEN
2033 pa_debug.g_err_stage:='1002 cost rate : ' || l_cost_rate || 'Raw Cost : '
2034 || l_raw_cost || 'currency_code : ' || l_txn_curr_code;
2035 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2036 END IF;
2037 END;/*End of declare of cursor declaration for res_class_org_id*/
2038 END IF;/* End of Status check of Hierarcy Rate Procedure */
2039 END;/* End of proceduer call BEGIN */
2040
2041
2042 /* Getting the Rates and Revenue if the Rate is not present at the Resource Class and Res_class_organization_id Level */
2043 IF l_raw_cost IS NULL THEN
2044 DECLARE
2045 CURSOR C_std_res_class_sch_cost IS
2046 SELECT DECODE (p_uom,'DOLLARS', 1,DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_cost_rate_multiplier,1),null)) b_rate,
2047 DECODE (p_uom,'DOLLARS', PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(p_quantity ,b.rate_currency_code)
2048 ,DECODE(p_uom,b.bill_rate_unit,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_cost_rate_multiplier,1)
2049 * p_quantity, b.rate_currency_code),null)
2050 ) r_cost,
2051 DECODE(p_uom,'DOLLARS', b.rate_currency_code,b.rate_currency_code) rate_currency_code
2052 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2053 WHERE sch.bill_rate_sch_id=p_cost_res_class_rate_sch_id
2054 AND sch.bill_rate_sch_id=b.bill_rate_sch_id
2055 AND b.resource_class_code = p_resource_class
2056 AND sch.schedule_type = 'RESOURCE_CLASS'
2057 AND b.res_class_organization_id IS NULL
2058 AND trunc(p_item_date)
2059 BETWEEN trunc(b.start_date_active)
2060 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2061
2062
2063 BEGIN
2064
2065 -- Opening cursor and fetching row
2066 FOR Rec_std_res_class_sch_cost IN C_std_res_class_sch_cost LOOP
2067 -- Checking if the cursor is returning more than one row then error out
2068 IF (l_true) THEN
2069 RAISE l_more_than_one_row_excep;
2070 ELSE
2071 l_true := TRUE;
2072 END IF;
2073
2074 -- Assigning the Calculated raw cost to the local variable
2075 l_cost_rate := Rec_std_res_class_sch_cost.b_rate;
2076 l_raw_cost := Rec_std_res_class_sch_cost.r_cost;
2077 l_txn_curr_code := Rec_std_res_class_sch_cost.rate_currency_code;
2078 END LOOP;
2079 IF g1_debug_mode = 'Y' THEN
2080 pa_debug.g_err_stage:='1002 cost rate : ' || l_cost_rate || 'Raw Cost : '
2081 || l_raw_cost || 'currency_code : ' || l_txn_curr_code;
2082 pa_debug.write('Get_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2083 END IF;
2084
2085 EXCEPTION
2086 WHEN l_more_than_one_row_excep THEN
2087 x_raw_cost:= NULL;
2088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2089 x_msg_count := 1;
2090 x_msg_data := 'TOO_MANY_ROWS';
2091 x_raw_cost_rejection_code :='TOO_MANY_ROWS';
2092 IF g1_debug_mode = 'Y' THEN
2093 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2094 pa_debug.write('Get_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2095 END IF;
2096 RAISE;
2097 END;/*End of decalre cursor*/
2098
2099 IF ( l_raw_cost IS NULL) THEN
2100 RAISE l_no_cost;
2101 END IF;
2102
2103 IF g1_debug_mode = 'Y' THEN
2104 pa_debug.g_err_stage:='9999 l_cost_rate: ' || l_cost_rate||
2105 'raw_cost : ' ||l_raw_cost || 'currency_code : ' || l_txn_curr_code;
2106 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2107 END IF;
2108
2109 END IF;/* End of IF l_raw_cost */
2110
2111 EXCEPTION
2112 WHEN l_no_cost THEN
2113 x_raw_cost:= NULL;
2114 l_inter_return_status := FND_API.G_RET_STS_ERROR;
2115 x_msg_data:= 'PA_FCST_NO_COST_RATE';
2116 x_raw_cost_rejection_code := 'PA_FCST_NO_COST_RATE';
2117 x_msg_count := 1;
2118 IF g1_debug_mode = 'Y' THEN
2119 pa_debug.g_err_stage:='No Cost Rate Exists:p_project_id'||p_project_id||'p_task_id'||p_task_id;
2120 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2121 END IF;
2122
2123 WHEN OTHERS THEN
2124 x_raw_cost:= NULL;
2125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2126 x_msg_count := 1;
2127 x_msg_data := SUBSTR(SQLERRM,1,30);
2128 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
2129
2130 IF g1_debug_mode = 'Y' THEN
2131 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2132 pa_debug.write('Get_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2133 END IF;
2134
2135 RAISE;
2136
2137 END;/* End of Begin for Revenue Scedule Type */
2138 ELSE
2139 IF NVL(p_quantity,1) <>0 THEN
2140 l_cost_rate:=NVL(p_override_trxn_cost_rate,l_raw_cost/p_quantity);
2141 ELSE
2142 l_cost_rate:=p_override_trxn_cost_rate;
2143 END IF;
2144
2145 END IF;/* End of l_raw_cost */
2146
2147 END IF; /*End if of l_schedule_type=Cost */
2148
2149
2150 /*==========================================================================================*/
2151 /* If from the above raw_cost is computed and it needs to compute the burden cost on it
2152 then the folowing code will be executed */
2153 IF l_burden_cost is NOT NULL Then
2154
2155 --assigning override burden cost/or derived burden cost from Actual
2156 IF g1_debug_mode = 'Y' THEN
2157 pa_debug.g_err_stage := 'Assignging override burden cost values to out params';
2158 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2159 END IF;
2160 l_txn_burden_cost :=l_burden_cost;
2161 IF (p_quantity <> 0 ) THEN
2162 l_txn_burden_cost_rate := l_burden_cost / NVL(p_quantity,1) ;
2163 END IF;
2164 IF NVL(l_raw_cost,0)<>0 THEN
2165 l_txn_burden_multiplier := (l_burden_cost/l_raw_cost)-1;
2166 END IF;
2167
2168 ElsIf ( p_override_burden_cost_rate is NULL and l_burden_cost is NULL
2169 and l_raw_cost is NOT NULL and l_cost_rate is NOT NULL
2170 and pa_cost1.check_proj_burdened(p_project_type,p_project_id) = 'Y' ) Then
2171 l_burd_organization_id := NVL(l_override_organization_id, NVl(p_incurred_by_organz_id ,p_nlr_organization_id));
2172 IF g1_debug_mode = 'Y' THEN
2173 pa_debug.g_err_stage := 'calling pa_cost1.Get_burden_sch_details fro burden cost';
2174 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2175 END IF;
2176
2177 begin
2178
2179 pa_cost1.Get_burden_sch_details
2180 (p_calling_mode =>l_calling_mode
2181 ,p_exp_item_id => NULL
2182 ,p_trxn_type => NULL
2183 ,p_project_type => p_project_type
2184 ,p_project_id => p_project_id
2185 ,p_task_id => p_task_id
2186 ,p_exp_organization_id => l_burd_organization_id
2187 ,p_expenditure_type => p_expenditure_type
2188 ,p_schedule_type => 'COST'
2189 ,p_exp_item_date => p_item_date
2190 ,p_trxn_curr_code => NVL(l_txn_curr_code,p_override_txn_currency_code)
2191 ,p_burden_schedule_id => p_plan_burden_cost_sch_id
2192 ,x_schedule_id => l_burd_sch_id
2193 ,x_sch_revision_id => l_burd_sch_rev_id
2194 ,x_sch_fixed_date => l_burd_sch_fixed_date
2195 ,x_cost_base => l_burd_sch_cost_base
2196 ,x_cost_plus_structure => l_burd_sch_cp_structure
2197 ,x_compiled_set_id => l_cost_ind_compiled_set_id
2198 ,x_burden_multiplier => l_txn_burden_multiplier
2199 ,x_return_status => l_cost_return_status
2200 ,x_error_msg_code => l_cost_msg_data
2201 );
2202
2203 exception
2204 when others then
2205
2206 l_cost_return_status := 'E';
2207 l_cost_msg_data := 'GET_BURDEN_DETAILS: '||substr(sqlerrm,1,30);
2208 end;
2209 If ( l_cost_return_status <> g_success OR l_txn_burden_multiplier is NULL ) Then
2210 IF g1_debug_mode = 'Y' THEN
2211 pa_debug.g_err_stage := 'Error while Calculating burden costs';
2212 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2213 END IF;
2214 l_burden_cost_rejection_code := l_cost_msg_data;
2215
2216 -------------------------------------------------------
2217 -- Get Burden Cost and rate from Raw Cost and Quantity.
2218 -------------------------------------------------------
2219 ELSE
2220
2221 l_txn_burden_cost := pa_currency.round_trans_currency_amt(
2222 l_raw_cost * NVL(l_txn_burden_multiplier,0),NVL(l_txn_curr_code,p_override_txn_currency_code )) +
2223 l_raw_cost ;
2224
2225 /*bug3749153 no need to compute the burden cost rate if it equals raw_cost_rate */
2226 If l_txn_burden_cost = l_raw_cost Then
2227 l_txn_burden_cost_rate := l_cost_rate;
2228 Else
2229 IF (p_quantity <> 0 ) THEN
2230 l_txn_burden_cost_rate := l_txn_burden_cost / NVL(P_quantity, 1) ;
2231 END IF;
2232 End if;
2233 end if;
2234 Elsif p_override_burden_cost_rate is NOT NULL and l_burden_cost is NULL Then
2235 IF g1_debug_mode = 'Y' THEN
2236 pa_debug.g_err_stage := 'Calculating burden cost based on override burden multiplier ';
2237 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2238 END IF;
2239
2240 l_txn_burden_cost := p_quantity * p_override_burden_cost_rate;
2241 l_txn_burden_cost_rate := p_override_burden_cost_rate;
2242 IF NVL(l_raw_cost,0)<>0 THEN
2243 l_txn_burden_multiplier := ( l_txn_burden_cost / l_raw_cost)-1;
2244 END IF;
2245
2246 ElsIF l_raw_cost IS NOT NULL THEN
2247 --copy the raw cost to the burden costs
2248 IF g1_debug_mode = 'Y' THEN
2249 pa_debug.g_err_stage := 'Copying raw costs to burden costs';
2250 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2251 END IF;
2252 l_txn_burden_cost := l_raw_cost;
2253 l_txn_burden_cost_rate := l_cost_rate;
2254 l_txn_burden_multiplier := 0;
2255
2256 End IF;
2257
2258 /* Burden Cost Calucltaion complete */
2259 /*==========================================================================================*/
2260 l_inter_txn_curr_code :=NVL(l_txn_curr_code,p_override_txn_currency_code) ; --4194214
2261 /* Assigning out all the Cost and Burden Cost Parameters */
2262 x_raw_cost := l_raw_cost ;
2263 x_cost_rate := l_cost_rate ;
2264 x_burden_cost := l_txn_burden_cost;
2265 x_burden_cost_rate := l_txn_burden_cost_rate ;
2266 x_burden_multiplier := l_txn_burden_multiplier;
2267 x_cost_txn_curr_code := l_inter_txn_curr_code;--NVL(l_txn_curr_code,p_override_txn_currency_code) ;--4194214
2268 x_return_status := NVL(l_inter_return_status,l_x_return_status);
2269 l_inter_return_status := NVL(l_inter_return_status,l_x_return_status);
2270 x_burden_cost_rejection_code:= l_burden_cost_rejection_code;
2271
2272 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
2273 IF g1_debug_mode = 'Y' THEN
2274 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;
2275 pa_debug.write('Get_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2276 END IF;
2277
2278 IF NVL(p_schedule_type,'REVENUE')='REVENUE' THEN
2279 IF p_system_linkage='BTC' THEN
2280 l_raw_cost:= NVL(l_txn_burden_cost,p_burden_cost);
2281 ELSE
2282 l_raw_cost:= NVL(l_raw_cost,p_raw_cost);
2283 END IF;
2284 /* This code is added beacuse if you choose to calculate only REVENUE then for Non-Rate based transaction it might happen that the
2285 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
2286 as l_raw_cost is used in the revenue calculation if its non-Rate based transaction */
2287 IF p_schedule_type ='REVENUE' AND l_raw_cost IS NULL THEN
2288 IF NVL(p_rate_based_flag,'N') ='N' THEN
2289 l_raw_cost:=p_quantity;
2290 END IF;
2291 IF g1_debug_mode = 'Y' THEN
2292 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;
2293 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,3);
2294 END IF;
2295 END IF;
2296
2297 IF l_raw_revenue IS NULL THEN
2298 l_true := FALSE;
2299 IF NVL(p_rate_based_flag,'N') ='N' AND l_raw_cost IS NULL THEN
2300 RAISE l_no_cost;
2301 END IF;
2302 IF p_rate_based_flag ='Y' THEN
2303 IF p_quantity IS NULL THEN
2304 IF g1_debug_mode = 'Y' THEN
2305 pa_debug.g_err_stage:='validating Get_plan_res_class_rates: p_quantity is required for rate based ';
2306 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2307 END IF;
2308 RAISE l_rate_based_no_quantity;
2309 END IF;
2310 END IF;
2311
2312
2313
2314 IF l_bill_rate IS NOT NULL AND l_raw_revenue IS NULL THEN
2315 l_raw_revenue :=l_bill_rate*p_quantity;
2316 END IF;
2317
2318
2319 BEGIN
2320 DECLARE
2321 l_class_org_rate NUMBER:=NULL;
2322 l_class_org_markup NUMBER:=NULL;
2323 l_class_org_uom pa_bill_rates_all.bill_rate_unit%TYPE :=NULL;
2324 l_class_org_rate_curr_code pa_bill_rates_all.rate_currency_code%TYPE :=NULL;
2325 l_class_org_return_status VARCHAR2(1):= g_success;
2326 l_class_org_return_data VARCHAR2(30);
2327 l_class_org_return_count NUMBER;
2328 l_item_date DATE := p_item_date;
2329 BEGIN
2330 IF l_raw_revenue IS NULL THEN --4108291
2331 Get_Res_Class_Hierarchy_Rate(p_res_class_rate_sch_id => p_rev_res_class_rate_sch_id,
2332 p_item_date => l_item_date,
2333 p_org_id => p_project_org_id ,
2334 p_resource_class_code => p_resource_class,
2335 p_res_class_org_id => p_project_organz_id,
2336 x_rate => l_class_org_rate ,
2337 x_markup_percentage => l_class_org_markup,
2338 x_uom => l_class_org_uom,
2339 x_rate_currency_code => l_class_org_rate_curr_code,
2340 x_return_status => l_class_org_return_status,
2341 x_msg_count => l_class_org_return_count,
2342 x_msg_data => l_class_org_return_data);
2343 /* Checking the status*/
2344 IF l_class_org_return_status = g_success THEN
2345
2346 /* Bug 5048677. Added to check if uom are same then if rate is null
2347 then compute revenue using mark up or use rate*/
2348
2349 DECLARE
2350 CURSOR C_std_res_class_sch_rev IS
2351 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,
2352 DECODE (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +l_class_org_markup)
2353 * (l_raw_cost / 100), l_inter_txn_curr_code) --4194214
2354 ,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),
2355 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_class_org_rate * NVL(p_bill_rate_multiplier,1)
2356 * p_quantity, l_class_org_rate_curr_code))
2357 ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +l_class_org_markup)
2358 * (l_raw_cost / 100), l_inter_txn_curr_code) --4194214
2359 )
2360 ) r_revenue,
2361 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,
2362 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
2363 FROM dual;
2364 BEGIN
2365 -- Opening cursor and fetching row
2366 OPEN C_std_res_class_sch_rev;
2367 -- Assigning the Calculated raw revenue/adjusted to the local variable
2368 FETCH C_std_res_class_sch_rev INTO l_bill_rate ,l_raw_revenue ,l_bill_txn_curr_code,l_markup ;
2369 CLOSE C_std_res_class_sch_rev;
2370
2371 IF g1_debug_mode = 'Y' THEN
2372 pa_debug.g_err_stage:='1002 bill rate : ' || l_bill_rate || 'Revenue : '
2373 || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2374 pa_debug.write('Get_plan_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2375 END IF;
2376
2377 END;/*End of declare of cursor declaration for C_std_res_class_sch_rev*/
2378
2379 END IF;/* End of Status check for Get_hierarchy_rate */
2380 END IF;/* IF l_raw_revenue IS NULL THEN 4108291*/
2381 END;/* End of proceduer call BEGIN */
2382
2383 l_txn_raw_revenue := l_raw_revenue;
2384
2385 /* Getting the Rates and Revenue if the Rate is not present at the Resource Class and Res_class_organization_id Level */
2386 IF l_txn_raw_revenue IS NULL THEN
2387 /* Bug 5048677. Added to check if uom are same then if rate is null
2388 then compute revenue using mark up or use rate*/
2389 DECLARE
2390 CURSOR C_std_res_class_sch_rev IS
2391 SELECT DECODE (p_uom,'DOLLARS',NULL,DECODE(p_uom,b.bill_rate_unit, b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2392 DECODE (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2393 * (l_raw_cost / 100), l_inter_txn_curr_code)--4194214
2394 ,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),
2395 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
2396 * p_quantity, b.rate_currency_code))
2397 ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2398 * (l_raw_cost / 100), l_inter_txn_curr_code)--4194214
2399
2400 )
2401 ) r_revenue,
2402 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,
2403 -- DECODE(p_uom,'DOLLARS', b.rate_currency_code,b.rate_currency_code ) rate_currency_code, --4194214
2404 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
2405 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2406 WHERE sch.bill_rate_sch_id=p_rev_res_class_rate_sch_id
2407 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2408 AND b.resource_class_code = p_resource_class
2409 AND sch.schedule_type = 'RESOURCE_CLASS'
2410 AND b.res_class_organization_id IS NULL
2411 AND trunc(p_item_date)
2412 BETWEEN trunc(b.start_date_active)
2413 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2414
2415 BEGIN
2416 -- Opening cursor and fetching row
2417 FOR Rec_std_res_class_sch_rev IN C_std_res_class_sch_rev LOOP
2418 -- Checking if the cursor is returning more than one row then error out
2419 IF (l_true) THEN
2420 RAISE l_more_than_one_row_excep;
2421 ELSE
2422 l_true := TRUE;
2423 END IF;
2424
2425 -- Assigning the Calculated raw revenue/adjusted to the local variable
2426 l_bill_rate := Rec_std_res_class_sch_rev.b_rate;
2427 l_raw_revenue := Rec_std_res_class_sch_rev.r_revenue;
2428 l_bill_txn_curr_code := Rec_std_res_class_sch_rev.rate_currency_code;
2429 l_markup := Rec_std_res_class_sch_rev.markup;
2430
2431 END LOOP;
2432
2433 IF g1_debug_mode = 'Y' THEN
2434 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
2435 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2436 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2437 END IF;
2438 EXCEPTION
2439 WHEN l_more_than_one_row_excep THEN
2440 x_raw_revenue:= NULL;
2441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2442 x_msg_count := 1;
2443 x_msg_data := 'TOO_MANY_ROWS';
2444 x_revenue_rejection_code := 'TOO_MANY_ROWS';
2445
2446 IF g1_debug_mode = 'Y' THEN
2447 pa_debug.g_err_stage:='Too many Rows';
2448 pa_debug.write('Get_plan_Res_Class_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2449 END IF;
2450
2451 RAISE;
2452
2453 END;/*End of decalre cursor*/
2454
2455 l_txn_raw_revenue := l_raw_revenue;
2456
2457 IF ( l_txn_raw_revenue IS NULL) THEN
2458 RAISE l_no_revenue;
2459 END IF;
2460
2461 END IF;/* End of IF l_txn_raw_revenue */
2462
2463 x_raw_revenue := l_txn_raw_revenue ;
2464 x_bill_rate := l_bill_rate ;
2465 x_bill_markup_percentage := l_markup ;
2466 x_rev_txn_curr_code := l_bill_txn_curr_code ;
2467 x_return_status := l_x_return_status;
2468
2469 IF g1_debug_mode = 'Y' THEN
2470 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
2471 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2472 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2473 END IF;
2474
2475 EXCEPTION
2476 WHEN l_no_revenue THEN
2477 RAISE l_no_revenue;
2478 END;/* End of Begin for Revenue Scedule Type */
2479 ELSE
2480
2481 IF NVL(p_quantity,1)<>0 THEN
2482 l_bill_rate:=NVL(p_override_trxn_bill_rate,l_raw_revenue/p_quantity);
2483 ELSE
2484 l_bill_rate:=p_override_trxn_bill_rate;
2485 END IF;
2486
2487 END IF;/* end if of IF l_raw_revenue I NULL */
2488 ELSE
2489 l_bill_rate:=p_override_trxn_bill_rate;
2490 END IF; /*End if of l_schedule_type=Revenue */
2491 x_raw_revenue := l_raw_revenue ;
2492 x_bill_rate := l_bill_rate ;
2493 x_bill_markup_percentage := l_markup ;
2494 x_rev_txn_curr_code := NVL(l_bill_txn_curr_code,p_override_txn_currency_code) ;
2495 x_return_status := l_x_return_status;
2496
2497 pa_debug.reset_err_stack;
2498 EXCEPTION
2499 WHEN l_insufficeient_param THEN
2500 IF g1_debug_mode = 'Y' THEN
2501 pa_debug.g_err_stage:='All the Required parameters are not passes to Resource Schedule';
2502 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
2503 END IF;
2504 x_return_status := g_ERROR;
2505 x_msg_count := 1;
2506 x_msg_data := 'PA_FCST_INSUFFICIENT_PARA';
2507 x_revenue_rejection_code := 'PA_FCST_INSUFFICIENT_PARA';
2508 x_raw_cost_rejection_code := 'PA_FCST_INSUFFICIENT_PARA';
2509 x_burden_cost_rejection_code := 'PA_FCST_INSUFFICIENT_PARA';
2510 x_raw_revenue := NULL;
2511 x_raw_cost := NULL;
2512 x_bill_rate := NULL;
2513 x_cost_rate := NULL;
2514
2515 pa_debug.reset_err_stack;
2516
2517 WHEN l_rate_based_no_quantity THEN
2518 IF g1_debug_mode = 'Y' THEN
2519 pa_debug.g_err_stage:='Quantity is required for a rate based transaction';
2520 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
2521 END IF;
2522 x_return_status := g_ERROR;
2523 x_msg_count := 1;
2524 x_msg_data := 'PA_EX_QTY_EXIST';
2525 x_revenue_rejection_code := 'PA_EX_QTY_EXIST';
2526 x_raw_cost_rejection_code := 'PA_EX_QTY_EXIST';
2527 x_raw_revenue := NULL;
2528 x_raw_cost := NULL;
2529 x_bill_rate := NULL;
2530 x_cost_rate := NULL;
2531
2532 pa_debug.reset_err_stack;
2533 WHEN l_no_cost THEN
2534 x_raw_revenue := NULL;
2535 x_raw_cost := NULL;
2536 x_bill_rate := NULL;
2537 x_cost_rate := NULL;
2538 x_return_status := NVL(l_inter_return_status,g_ERROR);
2539 x_msg_data := 'PA_FCST_NO_COST_RATE';
2540 x_raw_cost_rejection_code := 'PA_FCST_NO_COST_RATE';
2541 x_burden_cost_rejection_code := 'PA_FCST_NO_COST_RATE';
2542 x_revenue_rejection_code := 'PA_FCST_NO_COST_RATE';
2543 x_msg_count := 1;
2544 IF g1_debug_mode = 'Y' THEN
2545 pa_debug.g_err_stage:='No Cost Rate Exists:p_project_id'||p_project_id||'p_task_id'||p_task_id;
2546 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2547 END IF;
2548
2549 pa_debug.reset_err_stack;
2550 WHEN l_no_revenue THEN
2551 x_raw_revenue := NULL;
2552 x_bill_rate := NULL;
2553 x_return_status := NVL(l_inter_return_status,g_ERROR);
2554 x_msg_data := 'PA_FCST_NO_BILL_RATE';
2555 x_revenue_rejection_code := 'PA_FCST_NO_BILL_RATE';
2556 x_msg_count := 1;
2557 IF g1_debug_mode = 'Y' THEN
2558 pa_debug.g_err_stage:='No Bill Rate Exists:p_project_id'||p_project_id||'p_task_id'||p_task_id;
2559 pa_debug.write('Get_plan_res_class_rates: ' || g_module_name,pa_debug.g_err_stage,5);
2560 END IF;
2561
2562 pa_debug.reset_err_stack;
2563
2564 WHEN OTHERS THEN
2565 x_raw_revenue := NULL;
2566 x_raw_cost := NULL;
2567 x_bill_rate := NULL;
2568 x_cost_rate := NULL;
2569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2570 x_msg_count := 1;
2571 x_msg_data := SUBSTR(SQLERRM,1,30);
2572 x_revenue_rejection_code := SUBSTR(SQLERRM,1,30);
2573 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
2574 x_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
2575
2576 IF g1_debug_mode = 'Y' THEN
2577 pa_debug.g_err_stage:='SQLERROR ' || SQLCODE;
2578 pa_debug.write('Get_plan_res_class_Rates : ' || g_module_name,pa_debug.g_err_stage,5);
2579 END IF;
2580
2581 pa_debug.reset_err_stack;
2582 RAISE;
2583
2584
2585
2586 END Get_plan_res_class_rates ;
2587
2588
2589 /**************************************************************************************************************************
2590 ***************************************************************************************************************************
2591 *********************************** FOR DOOSAN ITERATION 2 PLANNING RATES *************************************************
2592 ***************************************************************************************************************************
2593 **************************************************************************************************************************/
2594 PROCEDURE Get_Plan_plan_Rev_Rates (
2595 p_project_id IN NUMBER,
2596 p_task_id IN NUMBER DEFAULT NULL,
2597 p_person_id IN NUMBER,
2598 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
2599 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
2600 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
2601 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
2602 p_uom IN VARCHAR2, /* Planning UOM */
2603 p_system_linkage IN VARCHAR2,
2604 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
2605 p_plan_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on job for planning*/
2606 p_plan_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on emp for planning*/
2607 p_plan_rev_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on non labor for planning*/
2608 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
2609 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
2610 p_quantity IN NUMBER, /* required param for People/Equipment Class */
2611 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
2612 p_project_org_id IN NUMBER, /* Project Org Id */
2613 p_project_type IN VARCHAR2,
2614 p_expenditure_type IN VARCHAR2,
2615 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
2616 p_override_to_organz_id IN NUMBER, /* Override Org Id */
2617 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
2618 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
2619 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
2620 p_NLR_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
2621 p_revenue_override_rate IN NUMBER DEFAULT NULL,
2622 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
2623 p_txn_currency_code IN VARCHAR2 DEFAULT NULL,
2624 p_raw_cost IN NUMBER,
2625 p_burden_cost IN NUMBER DEFAULT NULL,
2626 p_raw_revenue IN NUMBER DEFAULT NULL,
2627 x_bill_rate OUT NOCOPY NUMBER,
2628 x_raw_revenue OUT NOCOPY NUMBER,
2629 x_bill_markup_percentage OUT NOCOPY NUMBER,
2630 x_txn_curr_code OUT NOCOPY VARCHAR2,
2631 x_return_status OUT NOCOPY VARCHAR2,
2632 x_msg_data OUT NOCOPY VARCHAR2,
2633 x_msg_count OUT NOCOPY NUMBER
2634 )
2635 IS
2636
2637
2638 l_x_return_status VARCHAR2(20):=g_success;
2639 l_msg_count NUMBER;
2640 l_msg_data VARCHAR2(1000);
2641 l_txn_curr_code VARCHAR2(30);
2642 l_rev_curr_code VARCHAR2(30);
2643 l_override_cost NUMBER:=NULL;
2644 l_txn_bill_rate pa_bill_rates_all.rate%TYPE:=NULL;
2645 l_bill_rate pa_bill_rates_all.rate%TYPE:=NULL;
2646 l_txn_bill_markup NUMBER:=NULL;
2647 l_markup NUMBER:=NULL;
2648 l_raw_revenue NUMBER:=NULL;
2649 l_txn_raw_revenue NUMBER;
2650 l_exp_func_Curr_code VARCHAR2(30);
2651 l_raw_cost NUMBER;
2652 l_true BOOLEAN :=FALSE;
2653 l_bill_txn_curr_code VARCHAR2(30);
2654 -- Added for bug 5952621
2655 l_job_group_id pa_std_bill_rate_schedules_all.job_group_id%TYPE;
2656 l_dest_job_id pa_bill_rates_all.job_id%TYPE;
2657
2658
2659 -- Modified the select of all the four cursors for bug 5079161
2660 CURSOR C_std_emp_sch_rev IS
2661 SELECT
2662 /* DECODE (p_uom,'DOLLARS',NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
2663 DECODE (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2664 * (l_raw_cost / 100), p_txn_currency_code)
2665 ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
2666 * p_quantity, b.rate_currency_code)
2667 ) r_revenue,
2668 DECODE(p_uom,'DOLLARS', p_txn_currency_code ,b.rate_currency_code ) rate_currency_code,
2669 DECODE (p_uom,'DOLLARS',b.markup_percentage,NULL) markup
2670 */
2671 DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2672 DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2673 * (l_raw_cost / 100),p_txn_currency_code),
2674 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
2675 b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
2676 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
2677 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
2678 )
2679 ) r_revenue,
2680 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),
2681 p_txn_currency_code) ) rate_currency_code, -- 4194214
2682 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
2683 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2684 WHERE sch.bill_rate_sch_id=p_plan_rev_emp_rate_sch_id
2685 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2686 AND sch.schedule_type = 'EMPLOYEE'
2687 AND b.person_id = p_person_id
2688 AND trunc(p_item_date)
2689 BETWEEN trunc(b.start_date_active)
2690 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2691
2692 /* bug 3712539 removed the join to p_person_id*/
2693 CURSOR C_std_job_sch_rev IS
2694 SELECT
2695 DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2696 DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2697 * (l_raw_cost / 100),p_txn_currency_code),
2698 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
2699 b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
2700 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
2701 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
2702 )
2703 ) r_revenue,
2704 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),
2705 p_txn_currency_code) ) rate_currency_code, -- 4194214
2706 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
2707 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b --, per_assignments_f pa
2708 WHERE sch.bill_rate_sch_id = p_plan_rev_job_rate_sch_id
2709 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2710 AND sch.schedule_type = 'JOB'
2711 AND b.job_id = l_dest_job_id -- p_job_id Modified for bug 5952621
2712 AND trunc(p_item_date)
2713 BETWEEN trunc(b.start_date_active)
2714 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2715
2716 CURSOR C_std_nl_nls_sch_rev IS
2717 SELECT
2718 DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2719 DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2720 * (l_raw_cost / 100),p_txn_currency_code),
2721 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
2722 b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
2723 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
2724 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
2725 )
2726 ) r_revenue,
2727 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),
2728 p_txn_currency_code) ) rate_currency_code, -- 4194214
2729 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
2730 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2731 WHERE sch.bill_rate_sch_id = p_plan_rev_nlr_rate_sch_id
2732 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2733 AND b.expenditure_type = p_expenditure_type
2734 AND b.non_labor_resource = p_non_labor_resource
2735 AND sch.schedule_type = 'NON-LABOR'
2736 AND trunc(p_item_date)
2737 BETWEEN trunc(b.start_date_active)
2738 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2739
2740 CURSOR C_std_nl_exp_sch_rev IS
2741 SELECT
2742 DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
2743 DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
2744 * (l_raw_cost / 100),p_txn_currency_code),
2745 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
2746 b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
2747 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
2748 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
2749 )
2750 ) r_revenue,
2751 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),
2752 p_txn_currency_code) ) rate_currency_code, -- 4194214
2753 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
2754 FROM pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
2755 WHERE sch.bill_rate_sch_id = p_plan_rev_nlr_rate_sch_id
2756 AND b.bill_rate_sch_id = sch.bill_rate_sch_id
2757 AND b.expenditure_type = p_expenditure_type
2758 AND sch.schedule_type = 'NON-LABOR'
2759 AND b.non_labor_resource IS NULL
2760 AND trunc(p_item_date)
2761 BETWEEN trunc(b.start_date_active)
2762 AND NVL(trunc(b.end_date_active),trunc(p_item_date));
2763
2764
2765 BEGIN
2766 IF g1_debug_mode = 'Y' THEN
2767 pa_debug.g_err_stage:='Validating all the input parameters';
2768 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2769 END IF;
2770
2771 IF p_system_linkage='BTC' THEN
2772 l_override_cost := p_burden_cost;
2773 ELSE
2774 l_override_cost := p_raw_cost;
2775 END IF;
2776 l_txn_raw_revenue :=p_raw_revenue;
2777 l_raw_cost := p_raw_cost; -- Added for bug 5039918
2778
2779 IF p_rate_based_flag ='Y' AND p_quantity IS NULL AND NVL(l_override_cost,0)=0 THEN
2780 IF g1_debug_mode = 'Y' THEN
2781 pa_debug.g_err_stage:='Validating Get_plan_plan_rev_Rates:p_quantity is required for rate based';
2782 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2783 END IF;
2784 RAISE l_rate_based_no_quantity;
2785 END IF;
2786 IF p_revenue_override_rate IS NOT NULL AND p_override_currency_code IS NULL THEN
2787 IF g1_debug_mode = 'Y' THEN
2788 pa_debug.g_err_stage:='Validating Get_plan_plan_rev_Rates:p_override_currency_code is required if passing any overrides';
2789 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2790 END IF;
2791 RAISE l_invalid_currency;
2792 END IF;
2793 /* If revenue Override rate is not null compute the raw_revenue based on the override rate and the p_quantity or rawCost */
2794 IF p_revenue_override_rate IS NOT NULL AND l_txn_raw_revenue IS NULL THEN
2795
2796 SELECT p_revenue_override_rate b_rate,
2797 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((p_revenue_override_rate * p_quantity), p_override_currency_code) r_revenue,
2798 p_override_currency_code
2799 INTO l_txn_bill_rate,l_raw_revenue,l_txn_curr_code
2800 FROM dual;
2801
2802
2803 l_txn_raw_revenue :=l_raw_revenue;
2804
2805 END IF;/* End of check for p_revenue_override_rate */
2806 /* If in the above case the Raw Revenue is null then go for calling
2807 actual internal api of billing to compute the raw Revenue */
2808
2809 IF l_txn_raw_revenue IS NULL THEN
2810 /* Deriving Planning rates based on planning rate schedules for emp n job and non labor*/
2811 IF p_resource_class='PEOPLE' THEN
2812 IF g1_debug_mode = 'Y' THEN
2813 pa_debug.g_err_stage:='Deriving Planning rates based on planning rate schedules for emp and job';
2814 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2815 END IF;
2816 /* Checking for Employee based Rates*/
2817 BEGIN
2818 -- Opening cursor and fetching row
2819 l_true := FALSE;
2820 FOR Rec_std_emp_sch_rev IN C_std_emp_sch_rev LOOP
2821 -- Checking if the cursor is returning more than one row then error out
2822 IF (l_true) THEN
2823 RAISE l_more_than_one_row_excep;
2824 ELSE
2825 l_true := TRUE;
2826 END IF;
2827
2828 -- Assigning the Calculated raw revenue/adjusted to the local variable
2829 l_bill_rate := Rec_std_emp_sch_rev.b_rate;
2830 l_raw_revenue := Rec_std_emp_sch_rev.r_revenue;
2831 l_bill_txn_curr_code := Rec_std_emp_sch_rev.rate_currency_code;
2832 l_markup := Rec_std_emp_sch_rev.markup;
2833
2834 END LOOP;
2835 IF g1_debug_mode = 'Y' THEN
2836 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
2837 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2838 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,2);
2839 END IF;
2840 EXCEPTION
2841 WHEN l_more_than_one_row_excep THEN
2842 x_raw_revenue:= NULL;
2843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2844 x_msg_count := 1;
2845 x_msg_data := 'TOO_MANY_ROWS';
2846
2847
2848 IF g1_debug_mode = 'Y' THEN
2849 pa_debug.g_err_stage:='Too many Rows';
2850 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2851 END IF;
2852
2853 RAISE;
2854 END;/*End of decalre cursor*/
2855
2856 IF l_raw_revenue IS NULL THEN
2857 /* Checking for Job based Rates if not by Employee based*/
2858 BEGIN
2859 -- Opening cursor and fetching row
2860 l_true := FALSE;
2861
2862 /* Start of changes for bug 5952621 */
2863
2864 -- Start Changes for bug 6050924
2865 -- Handling scenario when p_plan_rev_job_rate_sch_id is null then select raises
2866 -- NO_DATA_FOUND exception. l_dest_job_id is set to p_job_id
2867 BEGIN
2868 select job_group_id
2869 into l_job_group_id
2870 from pa_std_bill_rate_schedules_all
2871 where bill_rate_sch_id = p_plan_rev_job_rate_sch_id;
2872 EXCEPTION
2873 When NO_DATA_FOUND Then
2874 l_job_group_id := NULL;
2875 END;
2876
2877 IF l_job_group_id IS NOT NULL THEN
2878 l_dest_job_id := pa_cross_business_grp.IsMappedToJob(p_job_id, l_job_group_id);
2879 ELSE
2880 l_dest_job_id := p_job_id;
2881 END IF;
2882 -- End Changes for bug 6050924
2883
2884 /* End of changes for bug 5952621 */
2885
2886 FOR Rec_std_job_sch_rev IN C_std_job_sch_rev LOOP
2887 -- Checking if the cursor is returning more than one row then error out
2888 IF (l_true) THEN
2889 RAISE l_more_than_one_row_excep;
2890 ELSE
2891 l_true := TRUE;
2892 END IF;
2893
2894 -- Assigning the Calculated raw revenue/adjusted to the local variable
2895 l_bill_rate := Rec_std_job_sch_rev.b_rate;
2896 l_raw_revenue := Rec_std_job_sch_rev.r_revenue;
2897 l_bill_txn_curr_code := Rec_std_job_sch_rev.rate_currency_code;
2898 l_markup := Rec_std_job_sch_rev.markup;
2899
2900 END LOOP;
2901 IF g1_debug_mode = 'Y' THEN
2902 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
2903 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2904 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2905 END IF;
2906 EXCEPTION
2907 WHEN l_more_than_one_row_excep THEN
2908 x_raw_revenue:= NULL;
2909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2910 x_msg_count := 1;
2911 x_msg_data := 'TOO_MANY_ROWS';
2912
2913
2914 IF g1_debug_mode = 'Y' THEN
2915 pa_debug.g_err_stage:='Too many Rows';
2916 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2917 END IF;
2918
2919 RAISE;
2920 END;/*End of decalre cursor*/
2921
2922 END IF;/*IF l_raw_revenue IS NULL THEN*/
2923
2924 ELSE /* Else of p_resource_class='PEOPLE' */
2925
2926 /* Checking for Non Labor based Rates first for Non Labor Resources*/
2927 BEGIN
2928 -- Opening cursor and fetching row
2929 l_true := FALSE;
2930 FOR Rec_std_nl_sch_rev IN C_std_nl_nls_sch_rev LOOP
2931 -- Checking if the cursor is returning more than one row then error out
2932 IF (l_true) THEN
2933 RAISE l_more_than_one_row_excep;
2934 ELSE
2935 l_true := TRUE;
2936 END IF;
2937
2938 -- Assigning the Calculated raw revenue/adjusted to the local variable
2939 l_bill_rate := Rec_std_nl_sch_rev.b_rate;
2940 l_raw_revenue := Rec_std_nl_sch_rev.r_revenue;
2941 l_bill_txn_curr_code := Rec_std_nl_sch_rev.rate_currency_code;
2942 l_markup := Rec_std_nl_sch_rev.markup;
2943
2944 END LOOP;
2945 IF g1_debug_mode = 'Y' THEN
2946 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
2947 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2948 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2949 END IF;
2950 EXCEPTION
2951 WHEN l_more_than_one_row_excep THEN
2952 x_raw_revenue:= NULL;
2953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2954 x_msg_count := 1;
2955 x_msg_data := 'TOO_MANY_ROWS';
2956
2957
2958 IF g1_debug_mode = 'Y' THEN
2959 pa_debug.g_err_stage:='Too many Rows in non labor based on Non Labor Resources';
2960 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
2961 END IF;
2962
2963 RAISE;
2964 END;/*End of decalre cursor*/
2965
2966 IF l_raw_revenue IS NULL THEN
2967 /* Checking for Non Labor based Rates first for Non Labor Resources*/
2968 BEGIN
2969 -- Opening cursor and fetching row
2970 l_true := FALSE;
2971 FOR Rec_std_nl_sch_rev IN C_std_nl_exp_sch_rev LOOP
2972 -- Checking if the cursor is returning more than one row then error out
2973 IF (l_true) THEN
2974 RAISE l_more_than_one_row_excep;
2975 ELSE
2976 l_true := TRUE;
2977 END IF;
2978
2979 -- Assigning the Calculated raw revenue/adjusted to the local variable
2980 l_bill_rate := Rec_std_nl_sch_rev.b_rate;
2981 l_raw_revenue := Rec_std_nl_sch_rev.r_revenue;
2982 l_bill_txn_curr_code := Rec_std_nl_sch_rev.rate_currency_code;
2983 l_markup := Rec_std_nl_sch_rev.markup;
2984
2985 END LOOP;
2986 IF g1_debug_mode = 'Y' THEN
2987 pa_debug.g_err_stage:='l_bill_rate: ' || l_bill_rate ||
2988 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_bill_txn_curr_code;
2989 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
2990 END IF;
2991 EXCEPTION
2992 WHEN l_more_than_one_row_excep THEN
2993 x_raw_revenue:= NULL;
2994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2995 x_msg_count := 1;
2996 x_msg_data := 'TOO_MANY_ROWS';
2997
2998
2999 IF g1_debug_mode = 'Y' THEN
3000 pa_debug.g_err_stage:='Too many Rows in non labor based on expenditure Type';
3001 pa_debug.write('Get_plan_plan_rev_Rates: ' || g_module_name,pa_debug.g_err_stage,5);
3002 END IF;
3003
3004 RAISE;
3005 END;/*End of decalre cursor*/
3006
3007 END IF;/* If l_raw_revenue IS NULL */
3008
3009
3010 IF g1_debug_mode = 'Y' THEN
3011 pa_debug.g_err_stage:='Deriving Planning rates based on planning rate schedules for Non Labor';
3012 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3013 END IF;
3014 END IF;
3015 END IF ;/* End of l_txn_raw_revenue IS NULL */
3016
3017
3018 x_raw_revenue :=NVL(l_txn_raw_revenue,l_raw_revenue);
3019 x_bill_rate := l_bill_rate ;
3020 x_bill_markup_percentage := l_markup ;
3021 x_txn_curr_code := NVL(l_bill_txn_curr_code,p_override_currency_code) ;
3022 x_return_status := l_x_return_status;
3023
3024
3025 EXCEPTION
3026 WHEN l_invalid_currency THEN
3027 x_raw_revenue :=NULL;
3028 x_bill_rate :=NULL;
3029 x_bill_markup_percentage :=NULL;
3030 x_txn_curr_code :=NULL;
3031 x_return_status := g_error;
3032 x_msg_data := 'PA_INVALID_DENOM_CURRENCY';
3033 x_msg_count := 1;
3034
3035 IF g1_debug_mode = 'Y' THEN
3036 pa_debug.g_err_stage:='Override Currency is not passed to a rate based tranaction to the Get_Plan_Actual_Rev_Rates call';
3037 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3038 END IF;
3039 WHEN l_rate_based_no_quantity THEN
3040 x_raw_revenue :=NULL;
3041 x_bill_rate :=NULL;
3042 x_bill_markup_percentage :=NULL;
3043 x_txn_curr_code :=NULL;
3044 x_return_status := g_error;
3045 x_msg_data := 'PA_EX_QTY_EXIST';
3046 x_msg_count := 1;
3047
3048 IF g1_debug_mode = 'Y' THEN
3049 pa_debug.g_err_stage:='Quantity is not passed to a rate based tranaction to the Get_Plan_Actual_Rev_Rates call';
3050 pa_debug.write('Get_plan_plan_rev_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3051 END IF;
3052
3053 END Get_Plan_plan_Rev_Rates;
3054
3055
3056
3057 PROCEDURE Get_plan_plan_Rates ( p_project_id IN NUMBER,
3058 p_task_id IN NUMBER DEFAULT NULL,
3059 p_top_task_id IN NUMBER DEFAULT NULL, /* for costing top task Id */
3060 p_person_id IN NUMBER,
3061 p_job_id IN NUMBER, /* for costing p_proj_cost_job_id */
3062 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
3063 p_resource_class IN VARCHAR2, /* resource_class_code for Resource Class */
3064 p_rate_based_flag IN VARCHAR2 DEFAULT 'Y', /* to identify a rate based transaction */
3065 p_uom IN VARCHAR2, /* Planning UOM */
3066 p_system_linkage IN VARCHAR2,
3067 p_project_organz_id IN NUMBER DEFAULT NULL, /* For revenue calc use in Resource Class Sch carrying out Org Id */
3068 p_plan_rev_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on job for planning*/
3069 p_plan_cost_job_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on job for planning*/
3070 p_plan_rev_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on emp for planning*/
3071 p_plan_cost_emp_rate_sch_id IN NUMBER DEFAULT NULL, /* For cost Rate Calculations based on emp for planning*/
3072 p_plan_rev_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Bill Rate Calculations based on non labor for planning*/
3073 p_plan_cost_nlr_rate_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on non labor for planning*/
3074 p_plan_burden_cost_sch_id IN NUMBER DEFAULT NULL, /* For Cost Rate Calculations based on burdening for planning*/
3075 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) */
3076 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
3077 p_cost_rate_multiplier IN NUMBER DEFAULT NULL ,
3078 p_bill_rate_multiplier IN NUMBER DEFAULT 1,
3079 p_quantity IN NUMBER, /* required param for People/Equipment Class */
3080 p_item_date IN DATE, /* Used as p_expenditure_item_date for non labor */
3081 p_project_org_id IN NUMBER, /* Project Org Id */
3082 p_project_type IN VARCHAR2,
3083 p_expenditure_type IN VARCHAR2,
3084 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
3085 p_incurred_by_organz_id IN NUMBER, /* Incurred By Org Id */
3086 p_override_to_organz_id IN NUMBER, /* Override Org Id */
3087 p_expenditure_org_id IN NUMBER, /* p_expenditure_OU (p_exp_organization_id in costing) */
3088 p_planning_transaction_id IN NUMBER DEFAULT NULL, /* changeed from p_forecast_item_id will passed to client extension */
3089 p_nlr_organization_id IN NUMBER DEFAULT NULL, /* Org Id of the Non Labor Resource */
3090 p_inventory_item_id IN NUMBER DEFAULT NULL, /* Passed for Inventoty Items */
3091 p_BOM_resource_Id IN NUMBER DEFAULT NULL, /* Passed for BOM Resource Id */
3092 P_mfc_cost_type_id IN NUMBER DEFAULT 0, /* Manufacturing cost api */
3093 P_item_category_id IN NUMBER DEFAULT NULL, /* Manufacturing cost api */
3094 p_mfc_cost_source IN NUMBER DEFAULT 1,
3095 p_cost_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to costing internal api.*/
3096 p_revenue_override_rate IN NUMBER DEFAULT NULL, /*override rate if not null no call to billing internal api.*/
3097 p_override_burden_cost_rate IN NUMBER DEFAULT NULL, /*override burden multiplier and p_raw_cost is not null calculate x_burden_cost */
3098 p_override_currency_code IN VARCHAR2 DEFAULT NULL, /*override currency Code */
3099 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*/
3100 p_raw_cost IN NUMBER, /*If p_raw_cost is only passed,return the burden multiplier, burden_cost */
3101 p_burden_cost IN NUMBER DEFAULT NULL,
3102 p_raw_revenue IN NUMBER DEFAULT NULL,
3103 x_bill_rate OUT NOCOPY NUMBER,
3104 x_cost_rate OUT NOCOPY NUMBER,
3105 x_burden_cost_rate OUT NOCOPY NUMBER,
3106 x_burden_multiplier OUT NOCOPY NUMBER,
3107 x_raw_cost OUT NOCOPY NUMBER,
3108 x_burden_cost OUT NOCOPY NUMBER,
3109 x_raw_revenue OUT NOCOPY NUMBER,
3110 x_bill_markup_percentage OUT NOCOPY NUMBER,
3111 x_cost_txn_curr_code OUT NOCOPY VARCHAR2,
3112 x_rev_txn_curr_code OUT NOCOPY VARCHAR2, /* x_txn_currency_code for Labor and x_rev_curr_code for non labor */
3113 x_raw_cost_rejection_code OUT NOCOPY VARCHAR2,
3114 x_burden_cost_rejection_code OUT NOCOPY VARCHAR2,
3115 x_revenue_rejection_code OUT NOCOPY VARCHAR2,
3116 x_cost_ind_compiled_set_id OUT NOCOPY NUMBER,
3117 x_return_status OUT NOCOPY VARCHAR2,
3118 x_msg_data OUT NOCOPY VARCHAR2,
3119 x_msg_count OUT NOCOPY NUMBER
3120 )
3121 IS
3122 l_raw_cost NUMBER :=NULL;
3123 l_burden_cost NUMBER :=NULL;
3124 l_raw_revenue NUMBER:=NULL;
3125 l_x_return_status VARCHAR2(2):= g_success;
3126 l_cost_msg_count NUMBER;
3127 l_cost_msg_data VARCHAR2(1000);
3128 l_bill_msg_count NUMBER;
3129 l_bill_msg_data VARCHAR2(1000);
3130 l_called_process VARCHAR2(40);
3131 l_txn_curr_code VARCHAR2(30);
3132 l_trxn_curr_code VARCHAR2(30);
3133 l_cost_txn_curr_code VARCHAR2(30);
3134 l_rev_txn_curr_code VARCHAR2(30);
3135 l_rev_curr_code VARCHAR2(30);
3136 l_txn_cost NUMBER:=NULL; /* to store the value of p_burden_cost or p_raw_cost */
3137 l_proj_nl_bill_rate_sch_id NUMBER;
3138 l_task_nl_bill_rate_sch_id NUMBER;
3139 l_txn_cost_rate NUMBER;
3140 l_txn_raw_cost_rate NUMBER;
3141 l_txn_burden_cost_rate NUMBER;
3142 l_txn_bill_rate NUMBER;
3143 l_txn_bill_markup NUMBER:=NULL;
3144 l_txn_raw_cost NUMBER;
3145 l_txn_burden_cost NUMBER;
3146 l_txn_raw_revenue NUMBER;
3147 l_sl_function NUMBER ;
3148 l_exp_func_Curr_code VARCHAR2(30);
3149 l_raw_cost_rate NUMBER ;
3150 l_burden_cost_rate NUMBER ;
3151 l_bill_rate NUMBER:=NULL;
3152 l_burden_multiplier NUMBER;
3153 l_raw_cost_rejection_code VARCHAR2(30);
3154 l_burden_cost_rejection_code VARCHAR2(30);
3155 l_cost_ind_compiled_set_id NUMBER;
3156 l_proj_cost_job_id NUMBER;
3157 l_expenditure_org_id NUMBER;
3158 l_uom_flag NUMBER(1) :=1;
3159
3160
3161 BEGIN
3162 l_raw_revenue := p_raw_revenue;
3163 l_raw_cost := p_raw_cost;
3164 l_burden_cost := p_burden_cost;
3165 IF upper(p_resource_class)='PEOPLE' THEN
3166 l_expenditure_org_id :=nvl(p_incurred_by_organz_id, p_override_to_organz_id );
3167 ELSE
3168 l_expenditure_org_id :=nvl(p_nlr_organization_id,p_override_to_organz_id );
3169 END IF;
3170
3171 IF p_system_linkage='BTC' THEN
3172 l_txn_cost := p_burden_cost;
3173 ELSE
3174 l_txn_cost := p_raw_cost;
3175 END IF;
3176 IF ((p_raw_cost IS NULL OR p_burden_cost IS NULL)
3177 AND p_calculate_mode IN ('COST','COST_REVENUE')) THEN
3178 IF g1_debug_mode = 'Y' THEN
3179 pa_debug.g_err_stage:='Before Calling PA_COST1.Get_Plan_actual_Cost_Rates in PLAN mode';
3180 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3181 END IF;
3182
3183 BEGIN
3184 PA_COST1.Get_Plan_Actual_Cost_Rates
3185 (p_calling_mode =>'PLAN_RATES'
3186 ,p_project_type =>p_project_type
3187 ,p_project_id =>p_project_id
3188 ,p_task_id =>p_task_id
3189 ,p_top_task_id =>p_top_task_id
3190 ,p_Exp_item_date =>p_item_date
3191 ,p_expenditure_type =>p_expenditure_type
3192 ,p_expenditure_OU =>p_expenditure_org_id
3193 ,p_project_OU =>p_project_org_id
3194 ,p_Quantity =>p_Quantity
3195 ,p_resource_class =>p_resource_class
3196 ,p_person_id =>p_person_id
3197 ,p_non_labor_resource =>p_non_labor_resource
3198 ,p_NLR_organization_id =>p_NLR_organization_id
3199 ,p_override_organization_id =>p_override_to_organz_id
3200 ,p_incurred_by_organization_id =>p_incurred_by_organz_id
3201 ,p_inventory_item_id =>p_inventory_item_id
3202 ,p_BOM_resource_id =>p_BOM_resource_id
3203 ,p_override_trxn_curr_code =>p_override_currency_code
3204 ,p_override_burden_cost_rate =>p_override_burden_cost_rate
3205 ,p_override_trxn_cost_rate =>p_cost_override_rate
3206 ,p_override_trxn_raw_cost =>p_raw_cost
3207 ,p_override_trxn_burden_cost =>p_burden_cost
3208 ,p_mfc_cost_type_id =>p_mfc_cost_type_id
3209 ,p_mfc_cost_source =>p_mfc_cost_source --check
3210 ,p_item_category_id =>p_item_category_id
3211 ,p_job_id =>p_job_id
3212 , p_plan_cost_job_rate_sch_id =>p_plan_cost_job_rate_sch_id
3213 , p_plan_cost_emp_rate_sch_id =>p_plan_cost_emp_rate_sch_id
3214 , p_plan_cost_nlr_rate_sch_id =>p_plan_cost_nlr_rate_sch_id
3215 , p_plan_cost_burden_sch_id =>p_plan_burden_cost_sch_id
3216 ,x_trxn_curr_code =>l_trxn_curr_code
3217 ,x_trxn_raw_cost =>l_txn_raw_cost
3218 ,x_trxn_raw_cost_rate =>l_txn_cost_rate
3219 ,x_trxn_burden_cost =>l_txn_burden_cost
3220 ,x_trxn_burden_cost_rate =>l_txn_burden_cost_rate
3221 ,x_burden_multiplier =>l_burden_multiplier
3222 ,x_cost_ind_compiled_set_id =>l_cost_ind_compiled_set_id
3223 ,x_raw_cost_rejection_code =>l_raw_cost_rejection_code
3224 ,x_burden_cost_rejection_code =>l_burden_cost_rejection_code
3225 ,x_return_status =>l_x_return_status
3226 ,x_error_msg_code =>l_cost_msg_data ) ;
3227
3228 IF g1_debug_mode = 'Y' THEN
3229 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;
3230 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,3);
3231 END IF;
3232 EXCEPTION
3233 WHEN OTHERS THEN
3234 x_msg_data := 'PA_COST1.Get_Plan_Actual_Cost_Rates:' || SUBSTR(SQLERRM,1,250);
3235 x_raw_cost_rejection_code := SUBSTR(SQLERRM,1,30);
3236 x_burden_cost_rejection_code := SUBSTR(SQLERRM,1,30);
3237 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
3238
3239 IF g1_debug_mode = 'Y' THEN
3240 pa_debug.g_err_stage:=' PA_COST1.Get_Plan_Actual_Cost_Rates is throwing When Others';
3241 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,1);
3242 END IF;
3243 RAISE;
3244
3245 END;
3246 /* transferring the outout cost to one cost and checking if the costing API has computed Cost */
3247 IF p_system_linkage='BTC' THEN
3248 l_txn_cost := l_txn_burden_cost;
3249 ELSE
3250 l_txn_cost :=l_txn_raw_cost;
3251 END IF;
3252
3253
3254 ELSE
3255 /* If p_raw_cost and p_burden Cost are passed Costing API
3256 won't be called but the same value will be passed as it is */
3257 l_txn_raw_cost :=l_raw_cost ;
3258 l_txn_burden_cost :=l_burden_cost ;
3259 IF p_quantity <>0 THEN
3260 l_txn_cost_rate :=l_raw_cost/(NVL(p_quantity,1)) ;
3261 l_txn_burden_cost_rate :=l_burden_cost/(NVL(p_quantity,1)) ;
3262 END IF;
3263
3264 END IF;
3265
3266 /* Sending out all the out parametrs of Costing , This is send out here as even if the costing API has failed
3267 Revenue API will be called and revenue calculated if the required values are passed to the Billing API,
3268 though it'll pass the rejection code of Costing APi in the out parameters*/
3269 x_cost_rate := l_txn_cost_rate;
3270 x_burden_cost_rate := l_txn_burden_cost_rate;
3271 x_burden_multiplier := l_burden_multiplier ;
3272 x_raw_cost := l_txn_raw_cost;
3273 x_burden_cost := l_txn_burden_cost;
3274 x_cost_txn_curr_code := l_trxn_curr_code;
3275 x_raw_cost_rejection_code := l_raw_cost_rejection_code ;
3276 x_burden_cost_rejection_code := l_burden_cost_rejection_code;
3277 x_cost_ind_compiled_set_id := l_cost_ind_compiled_set_id;
3278 x_return_status := l_x_return_status ;
3279 x_msg_data := l_cost_msg_data ;
3280
3281 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
3282
3283 IF p_calculate_mode IN ('REVENUE','COST_REVENUE') THEN
3284 /* Calling the Billing Revenue calculation Api only if p_raw_revenue is null */
3285 IF l_raw_revenue IS NULL THEN
3286 /* Checking for Rate based whether quantity is entered else */
3287
3288 IF p_rate_based_flag ='Y' THEN
3289 null;
3290 ELSE
3291 IF NVL(l_txn_cost,0)=0 THEN
3292 /*4108291 added the beloe code to have same check as in get_plan_actual_rates to compute the revenue based on quanity
3293 if revenue override is passed*/
3294 IF p_quantity is NOT NULL and p_revenue_override_rate is not null then
3295 null;
3296 ELSE
3297 RAISE l_no_cost;
3298 END IF;
3299 END IF;
3300 END IF;
3301 IF g1_debug_mode = 'Y' THEN
3302 pa_debug.g_err_stage:='Calling Get_Plan_plan_Rev_Rates';
3303 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3304 END IF;
3305
3306 Get_Plan_plan_Rev_Rates (
3307 p_project_id => p_project_id ,
3308 p_task_id => p_task_id,
3309 p_person_id => p_person_id ,
3310 p_job_id => p_job_id,
3311 p_bill_job_grp_id => p_bill_job_grp_id,
3312 p_resource_class => p_resource_class,
3313 p_rate_based_flag => p_rate_based_flag,
3314 p_uom => p_uom,
3315 p_system_linkage => p_system_linkage,
3316 p_project_organz_id => p_project_organz_id,
3317 p_plan_rev_job_rate_sch_id => p_plan_rev_job_rate_sch_id ,
3318 p_plan_rev_emp_rate_sch_id => p_plan_rev_emp_rate_sch_id,
3319 p_plan_rev_nlr_rate_sch_id => p_plan_rev_nlr_rate_sch_id ,
3320 p_mcb_flag => p_mcb_flag,
3321 p_bill_rate_multiplier => p_bill_rate_multiplier ,
3322 p_quantity => p_quantity ,
3323 p_item_date => p_item_date,
3324 p_project_org_id => p_project_org_id ,
3325 p_project_type => p_project_type,
3326 p_expenditure_type => p_expenditure_type ,
3327 p_incurred_by_organz_id => p_incurred_by_organz_id ,
3328 p_override_to_organz_id => p_override_to_organz_id ,
3329 p_expenditure_org_id => l_expenditure_org_id, --p_expenditure_org_id ,
3330 p_planning_transaction_id => p_planning_transaction_id,
3331 p_non_labor_resource => p_non_labor_resource ,
3332 p_NLR_organization_id => p_NLR_organization_id ,
3333 p_revenue_override_rate => p_revenue_override_rate,
3334 p_override_currency_code => p_override_currency_code,
3335 p_txn_currency_code => l_trxn_curr_code ,
3336 p_raw_cost => l_txn_raw_cost,
3337 p_burden_cost => l_txn_burden_cost,
3338 p_raw_revenue => l_raw_revenue,
3339 x_bill_rate => l_txn_bill_rate,
3340 x_raw_revenue => l_txn_raw_revenue,
3341 x_bill_markup_percentage => l_txn_bill_markup,
3342 x_txn_curr_code => l_rev_txn_curr_code,
3343 x_return_status => l_x_return_status,
3344 x_msg_data => l_bill_msg_data,
3345 x_msg_count => l_bill_msg_count
3346 );
3347
3348
3349 /* Raising the Billing Exception to pass the error values to the Main Api */
3350 IF l_x_return_status <> g_success THEN
3351 RAISE l_bill_api;
3352 END IF;
3353
3354 ELSE
3355 IF p_override_currency_Code IS NULL THEN
3356 RAISE l_invalid_currency;
3357 END IF;
3358 l_txn_raw_revenue :=l_raw_revenue ;
3359 IF p_quantity <>0 THEN
3360 l_txn_bill_rate :=l_raw_revenue/(NVL(p_quantity,1)) ;
3361 END IF;
3362 l_rev_txn_curr_code:=p_override_currency_Code;
3363 END IF;
3364
3365 END IF; /* End of IF p_calculate_mode IN ('REVENUE','COST_REVENUE') THEN */
3366 /* Passing the output parametrs of Billing for Revenue */
3367 x_raw_revenue :=l_txn_raw_revenue;
3368 x_bill_rate :=l_txn_bill_rate ;
3369 x_bill_markup_percentage :=l_txn_bill_markup;
3370 x_rev_txn_curr_code :=l_rev_txn_curr_code;
3371 x_revenue_rejection_code :=NULL;
3372 x_return_status :=l_x_return_status;
3373 IF g1_debug_mode = 'Y' THEN
3374 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;
3375 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3376 END IF;
3377 EXCEPTION
3378
3379 WHEN l_no_cost THEN
3380 x_raw_revenue := NULL;
3381 x_bill_rate := NULL;
3382 x_bill_markup_percentage := NULL;
3383 x_rev_txn_curr_code := NULL;
3384 x_revenue_rejection_code := 'PA_NO_ACCT_COST';
3385 x_return_status := g_error;
3386 x_msg_data := 'PA_NO_ACCT_COST';
3387 x_msg_count := 1;
3388
3389
3390 IF g1_debug_mode = 'Y' THEN
3391 pa_debug.g_err_stage:='No Cost exist for the tranascation:p_project_id'||p_project_id||'p_task_id'||p_task_id;
3392 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3393 END IF;
3394 WHEN l_bill_api THEN
3395 x_raw_revenue := NULL;
3396 x_bill_rate := NULL;
3397 x_bill_markup_percentage := NULL;
3398 x_rev_txn_curr_code := NULL;
3399 x_revenue_rejection_code := l_bill_msg_data;
3400 x_return_status := l_x_return_status;
3401 x_msg_data := l_bill_msg_data;
3402 x_msg_count := l_bill_msg_count;
3403
3404 IF g1_debug_mode = 'Y' THEN
3405 pa_debug.g_err_stage:='Billing api is throwing error';
3406 pa_debug.write('Get_plan_plan_Rates : ' || g_module_name,pa_debug.g_err_stage,2);
3407 END IF;
3408
3409
3410 END Get_plan_plan_Rates;
3411 BEGIN
3412 Get_exp_type_uom;
3413 END PA_PLAN_REVENUE;
3414
[Home] [Help]