[Home] [Help]
PACKAGE BODY: APPS.PA_RATE_PVT_PKG
Source
1 PACKAGE BODY PA_RATE_PVT_PKG as
2 /* $Header: PAXRTPVB.pls 120.4.12010000.3 2008/10/08 23:31:38 skkoppul ship $ */
3 -- This procedure contains consolidated procedure and function to calculate the raw cost,
4 -- burdened cost and raw revenue on the basis of passed parameters
5 -- Input parameters
6 -- Parameters Type Required Description
7 -- p_calling_mode VARCHAR2 YES Calling mode values are ACTUAL/ROLE/ASSIGNMENT
8 -- p_rate_calc_date DATE YES Rate calculation date
9 -- P_item_id NUMBER YES Unique identifier
10 -- P_project_id NUMBER YES Project Id
11 -- P_quantity NUMBER YES Quantity in Hours
12 -- P_forecast_job_id NUMBER NO Forecast job Id at assignment level
13 -- P_forecast_job_group_id NUMBER NO Forecast job group id at assignment level
14 -- p_person_id NUMBER NO Person id
15 -- p_expenditure_org_id NUMBER NO Expenditure org id
16 -- P_expenditure_type VARCHAR2 NO Expenditure Type
17 -- p_expenditure_organization_id NUMBER NO Expenditure organization id
18 -- p_project_org_id NUMBER NO Project org id
19 -- p_labor_cost_multi_name VARCHAR2 NO Labor cost multiplier name for calculating the cost
20 -- p_expenditure_currency_code VARCHAR2 NO Expenditure functional currency code
21 -- P_proj_cost_job_group_id NUMBER NO Project cost job gorup id
22 -- P_job_cost_rate_schedule_id NUMBER NO Job cost rate schedule id
23 -- P_project_type VARCHAR2 NO Project Type
24 -- P_task_id NUMBER NO Task Id for the given project
25 -- p_projfunc_currency_code VARCHAR2 NO Project Functional currency code
26 -- P_bill_rate_multiplier NUMBER NO Bill rate multiplier for calculating the revenue
27 -- P_project_bill_job_group_id NUMBER NO Billing job group id for project
28 -- p_emp_bill_rate_schedule_id NUMBER NO Employee bill rate schedule id
29 -- P_job_bill_rate_schedule_id NUMBER NO Job bill rate schedule id
30 -- and rate
31 -- p_distribution_rule VARCHAR2 NO Distribution rule
32 --
33 -- Out parameters
34 --
35 -- x_exp_func_raw_cost_rate NUMBER YES Row cost rate in expenditure currency
36 -- x_exp_func_raw_cost NUMBER YES Row cost in expenditure currency
37 -- x_exp_func_burdened_cost_rate NUMBER YES Burdened cost rate in expenditure currency
38 -- x_exp_func_burdened_cost NUMBER YES Burdened cost in expenditure currency
39 -- x_projfunc_bill_rate NUMBER YES Bill rate in project currency
40 -- x_projfunc_raw_revenue NUMBER YES Raw revenue in project currency
41 -- x_projfunc_raw_cost NUMBER YES Raw cost in project currency
42 -- x_projfunc_raw_cost_rate NUMBER YES Raw cost rate in project currency
43 -- x_projfunc_burdened_cost_rate NUMBER YES Burdened cost rate in project currency
44 -- x_projfunc_burdened_cost NUMBER YES Burdened cost in project currency
45 -- x_error_msg VARCHAR2 YES Error message used in when others exception
46 -- x_rev_rejct_reason VARCHAR2 YES Rejection reason for revenue
47 -- x_cost_rejct_reason VARCHAR2 YES Rejection reason for cost
48 -- x_burdened_rejct_reason VARCHAR2 YES Rejection reason for burden
49 -- x_others_rejct_reason VARCHAR2 YES Rejection reason for other error like pl/sql etc.
50
51 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
52
53 PROCEDURE get_item_amount(
54 p_calling_mode IN VARCHAR2 ,
55 p_rate_calc_date IN DATE ,
56 p_item_id IN NUMBER ,
57 p_project_id IN NUMBER ,
58 p_quantity IN NUMBER ,
59 p_forecast_job_id IN NUMBER DEFAULT NULL,
60 p_forecast_job_group_id IN NUMBER DEFAULT NULL,
61 p_person_id IN NUMBER DEFAULT NULL,
62 p_expenditure_org_id IN NUMBER DEFAULT NULL,
63 p_expenditure_type IN VARCHAR2 DEFAULT NULL,
64 p_expenditure_organization_id IN NUMBER DEFAULT NULL,
65 p_project_org_id IN NUMBER DEFAULT NULL,
66 p_labor_cost_multi_name IN VARCHAR2 DEFAULT NULL,
67 p_expenditure_currency_code IN VARCHAR2 DEFAULT NULL,
68 p_proj_cost_job_group_id IN NUMBER DEFAULT NULL,
69 p_job_cost_rate_schedule_id IN NUMBER DEFAULT NULL,
70 p_project_type IN VARCHAR2 DEFAULT NULL,
71 p_task_id IN NUMBER DEFAULT NULL,
72 p_bill_rate_multiplier IN NUMBER DEFAULT NULL,
73 p_project_bill_job_group_id IN NUMBER DEFAULT NULL,
74 p_emp_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
75 p_job_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
76 p_distribution_rule IN VARCHAR2 DEFAULT NULL,
77 p_forecast_item_id IN NUMBER DEFAULT NULL, /* added para for bug 2212852 */
78 p_forecasting_type IN VARCHAR2 DEFAULT 'PROJECT_FORECASTING', /* added para for */
79 /* bug 2212852 */
80 p_amount_calc_mode IN VARCHAR2, /* Added for Org Forecasting */
81 p_system_linkage IN pa_expenditure_items_all.system_linkage_function%TYPE,/* Added */
82 /* for Org Forecasting */
83 p_assign_precedes_task IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
84 p_labor_schdl_discnt IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
85 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
86 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
87 p_labor_schedule_fixed_date IN DATE DEFAULT NULL, /* Added for Org Forecasting */
88 p_labor_sch_type IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
89 p_projfunc_currency_code IN VARCHAR2 DEFAULT NULL,
90 p_projfunc_rev_rt_dt_code IN VARCHAR2, /* Added for Org Forecasting */
91 p_projfunc_rev_rt_date IN DATE, /* Added for Org Forecasting */
92 p_projfunc_rev_rt_type IN VARCHAR2, /* Added for Org Forecasting */
93 p_projfunc_rev_exch_rt IN NUMBER, /* Added for Org Forecasting */
94 p_projfunc_cst_rt_date IN DATE, /* Added for Org Forecasting */
95 p_projfunc_cst_rt_type IN VARCHAR2, /* Added for Org Forecasting */
96 x_projfunc_bill_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
97 x_projfunc_raw_revenue OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
98 x_projfunc_rev_rt_date OUT NOCOPY DATE, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
99 x_projfunc_rev_rt_type OUT NOCOPY VARCHAR2, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
100 x_projfunc_rev_exch_rt OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
101 x_projfunc_raw_cost OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
102 x_projfunc_raw_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
103 x_projfunc_burdened_cost OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
104 x_projfunc_burdened_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
105 x_projfunc_cst_rt_date OUT NOCOPY DATE, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
106 x_projfunc_cst_rt_type OUT NOCOPY VARCHAR2, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
107 x_projfunc_cst_exch_rt OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
108 p_project_currency_code IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
109 p_project_rev_rt_dt_code IN VARCHAR2, /* Added for org Forecasting */
110 p_project_rev_rt_date IN DATE, /* Added for org Forecasting */
111 p_project_rev_rt_type IN VARCHAR2, /* Added for org Forecasting */
112 p_project_rev_exch_rt IN NUMBER, /* Added for org Forecasting */
113 p_project_cst_rt_date IN DATE, /* Added for org Forecasting */
114 p_project_cst_rt_type IN VARCHAR2, /* Added for org Forecasting */
115 x_project_bill_rt OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
116 x_project_raw_revenue OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
117 x_project_rev_rt_date OUT NOCOPY DATE, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
118 x_project_rev_rt_type OUT NOCOPY VARCHAR2, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
119 x_project_rev_exch_rt OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
120 x_project_raw_cst OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
121 x_project_raw_cst_rt OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
122 x_project_burdned_cst OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
123 x_project_burdned_cst_rt OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
124 x_project_cst_rt_date OUT NOCOPY DATE, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
125 x_project_cst_rt_type OUT NOCOPY VARCHAR2, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
126 x_project_cst_exch_rt OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
127 x_exp_func_curr_code OUT NOCOPY VARCHAR2, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
128 x_exp_func_raw_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
129 x_exp_func_raw_cost OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
130 x_exp_func_burdened_cost_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
131 x_exp_func_burdened_cost OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
132 x_exp_func_cst_rt_date OUT NOCOPY DATE, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
133 x_exp_func_cst_rt_type OUT NOCOPY VARCHAR2, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
134 x_exp_func_cst_exch_rt OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
135 x_cst_txn_curr_code OUT NOCOPY VARCHAR2, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
136 x_txn_raw_cst_rt OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
137 x_txn_raw_cst OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
138 x_txn_burdned_cst_rt OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
139 x_txn_burdned_cst OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
140 x_rev_txn_curr_code OUT NOCOPY VARCHAR2, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
141 x_txn_rev_bill_rt OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
142 x_txn_rev_raw_revenue OUT NOCOPY NUMBER, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
143 x_error_msg OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
144 x_rev_rejct_reason OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
145 x_cost_rejct_reason OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
146 x_burdened_rejct_reason OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
147 x_others_rejct_reason OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
148 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
149 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
150 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
151 IS
152
153 l_insufficient_parameters EXCEPTION;
154 l_raw_cost_null EXCEPTION;
155 l_raw_proj_cost_null EXCEPTION;
156 l_burdened_cost_null EXCEPTION;
157 l_raw_revenue_null EXCEPTION;
158 l_no_rule EXCEPTION;
159
160 l_expenditure_org_id pa_project_assignments.expenditure_org_id%TYPE;
161 l_expenditure_organization_id pa_project_assignments.expenditure_organization_id%TYPE;
162 l_expenditure_type pa_project_assignments.expenditure_type%TYPE;
163 l_forecast_job_id pa_project_assignments.fcst_job_id%TYPE;
164 l_forecast_job_group_id pa_project_assignments.fcst_job_group_id%TYPE;
165
166 l_labor_cost_mult_name pa_tasks.labor_cost_multiplier_name%TYPE;
167 l_project_type pa_project_types_all.project_type%TYPE;
168 l_proj_cost_job_grp_id pa_std_bill_rate_schedules_all.job_group_id%TYPE;
169 l_project_org_id pa_projects_all.org_id%TYPE;
170 l_project_bill_job_group_id pa_projects_all.bill_job_group_id%TYPE;
171 l_emp_bill_rate_schedule_id pa_projects_all.emp_bill_rate_schedule_id%TYPE;
172 l_job_bill_rate_schedule_id pa_projects_all.job_bill_rate_schedule_id%TYPE;
173 l_distribution_rule pa_projects_all.distribution_rule%TYPE;
174
175 l_job_cost_rate_schedule_id pa_forecasting_options.job_cost_rate_schedule_id%TYPE;
176
177 l_labor_schedule_fixed_date pa_projects_all.labor_schedule_fixed_date%TYPE;
178 l_labor_schedule_discount NUMBER;
179 l_labor_bill_rate_org_id NUMBER;
180 l_labor_std_bill_rate_schedule pa_projects_all.labor_std_bill_rate_schdl%TYPE;
181 l_labor_schedule_type pa_projects_all.labor_sch_type%TYPE;
182
183 l_x_return_status VARCHAR2(50);
184 l_x_process_return_status VARCHAR2(50);
185 l_schedule_type VARCHAR2(50);
186 l_proj_cost_job_id NUMBER;
187 l_proj_bill_job_id NUMBER;
188 l_cost_rate_multiplier NUMBER;
189 l_new_pvdr_acct_raw_cost NUMBER;
190
191 l_raw_cost_rate NUMBER;
192
193 l_overr_to_organization_id NUMBER;
194 l_new_pvdr_acct_burdened_cost NUMBER;
195 l_burdened_cost_rate NUMBER;
196 l_new_rcvr_acct_raw_cost NUMBER;
197 l_new_rcvr_acct_burdened_cost NUMBER;
198 l_new_rcvr_acct_raw_cost_rate NUMBER;
199 l_new_rcvr_acct_bur_cost_rate NUMBER;
200 l_new_rcvr_revenue NUMBER;
201
202 l_class_code pa_project_types_all.project_type_class_code%TYPE;
203
204 l_expenditure_currency_code gl_sets_of_books.currency_code%TYPE;
205 l_expenditure_curr_code_burdn gl_sets_of_books.currency_code%TYPE; /* Added for Org Forecasting */
206 l_exp_func_cst_rt_date DATE; /* Added for Org Forecasting */
207 l_exp_func_cst_rt_type PA_IMPLEMENTATIONS_ALL.default_rate_type%TYPE; /* Added for Org Forecasting */
208 l_exp_func_cst_exch_rt NUMBER; /* Added for Org Forecasting */
209 l_exp_func_raw_cost_rate NUMBER;
210 l_exp_func_raw_cost NUMBER;
211 l_exp_func_burdened_cost_rate NUMBER;
212 l_exp_func_burdened_cost NUMBER;
213
214 /* Added for MCB2 */
215 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
216 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
217 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
218 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
219 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
220 l_projfunc_cost_rate_type pa_projects_all.projfunc_cost_rate_type%TYPE;
221 l_projfunc_cost_rate_date pa_projects_all.projfunc_cost_rate_DATE%TYPE;
222 l_projfunc_cost_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
223 l_markup_percentage pa_bill_rates_all.markup_percentage%TYPE; /* Added for Asgmt overide */
224 l_assignment_precedes_task pa_projects_all.assign_precedes_task%TYPE; /* Added for Asgmt overide */
225 /* Till here for mcb 2 */
226
227 /* Added for Org Foreasting */
228 l_projfunc_bill_rate NUMBER;
229 l_projfunc_raw_revenue NUMBER;
230 l_projfunc_raw_cost NUMBER;
231 l_projfunc_raw_cost_rate NUMBER;
232 l_projfunc_burdened_cost NUMBER;
233 l_projfunc_burdened_cost_rate NUMBER;
234
235 l_amount_calc_mode VARCHAR2(50);
236
237 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
238 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
239 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
240 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
241 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
242 l_project_cost_rate_type pa_projects_all.project_rate_type%TYPE;
243 l_project_cost_rate_date pa_projects_all.project_rate_DATE%TYPE;
244 l_project_cost_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
245 l_project_bill_rate NUMBER;
246 l_project_raw_revenue NUMBER;
247 l_project_raw_cost NUMBER;
248 l_project_raw_cost_rate NUMBER;
249 l_project_burdened_cost NUMBER;
250 l_project_burdened_cost_rate NUMBER;
251
252 l_cst_txn_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
253 l_txn_raw_cst_rt NUMBER;
254 l_txn_raw_cst NUMBER;
255 l_txn_burdned_cst_rt NUMBER;
256 l_txn_burdned_cst NUMBER;
257
258 l_rev_txn_curr_code PA_BILL_RATES_ALL.rate_currency_code%TYPE;
259 l_txn_rev_bill_rt NUMBER;
260 l_txn_adjusted_bill_rt NUMBER;--4038485
261 l_txn_rev_raw_revenue NUMBER;
262
263 l_msg_data VARCHAR2(250); -- Added
264 /* Till here for Org */
265
266 /*LCE change*/
267 l_err_code VARCHAR2(20);
268 l_err_stage NUMBER;
269 /*Till here for LCE.*/
270
271 BEGIN
272 IF g1_debug_mode = 'Y' THEN
273 PA_DEBUG.Set_Curr_Function( p_function => 'Get_Item_Amount');
274 PA_DEBUG.g_err_stage := 'RT10 : Before Validation Entering PA_RATE_PVT_PKG.Get_Item_Amount';
275 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
276 END IF;
277
278 /* Validating that the required parameters should not be null */
279 IF ( p_calling_mode IS NULL) OR (p_rate_calc_date IS NULL ) OR
280 (p_item_id IS NULL) OR (p_project_id IS NULL) OR (p_quantity IS NULL) OR ( p_quantity = 0 ) THEN
281 RAISE l_insufficient_parameters;
282 END IF;
283
284 /* Validating that the required parameters should not be null */
285 IF ( p_calling_mode = 'ASSIGNMENT') THEN
286 IF (p_person_id IS NULL) THEN
287 RAISE l_insufficient_parameters;
288 END IF;
289 END IF;
290
291 IF g1_debug_mode = 'Y' THEN
292 PA_DEBUG.g_err_stage := 'RTS10 : After sufficient parameter';
293 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
294 END IF;
295 /* Selecting distribution_rule from project all table only if the passed value is null
296 otherwise storing passed values */
297 /*
298 IF (p_distribution_rule IS NULL) THEN
299 ELSE
300 l_distribution_rule := p_distribution_rule ;
301 END IF;
302 */
303 BEGIN
304 SELECT proj.distribution_rule,typ.project_type_class_code
305 INTO l_distribution_rule,l_class_code
306 FROM pa_project_types_all typ, pa_projects_all proj
307 WHERE proj.project_id = p_project_id
308 AND proj.project_type = typ.project_type
309 AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul : removed NVL function
310
311 IF ( l_class_code = 'CONTRACT') THEN
312 IF ( l_distribution_rule IS NULL) THEN
313 RAISE l_no_rule;
314 END IF;
315 END IF;
316 EXCEPTION
317 WHEN l_no_rule THEN
318 x_others_rejct_reason := 'PA_FCST_DIST_RULE_NOT_FOUND';
319 NULL;
320 WHEN NO_DATA_FOUND THEN
321 NULL;
322 END;
323
324 IF g1_debug_mode = 'Y' THEN
325 PA_DEBUG.g_err_stage := 'RTS11 : After Rule parameter'||to_char(p_rate_calc_date,'dd-mon-yyyy');
326 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
327
328 PA_DEBUG.g_err_stage := 'RTS11.1 : checking para Quantity '||p_quantity||' calling mode '||p_calling_mode;
329 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
330 -- dbms_output.put_line('distribution_rule '||l_distribution_rule||'calling mode '||p_calling_mode||'item id' ||p_item_id||' proj id '||p_project_id||' p_person_id '||p_person_id||' date '||to_char(p_rate_calc_date,'dd-mon-yyyy'));
331 END IF;
332
333 /* Selecting expenditure org id , type ,organization id , forecast job id and forecast job group
334 id from project assignments table only if the passed value is null otherwise storing passed
335 values */
336 IF (p_expenditure_org_id IS NULL) OR (p_expenditure_type IS NULL ) OR
337 (p_expenditure_organization_id IS NULL) OR (p_forecast_job_id IS NULL) OR
338 (p_forecast_job_group_id IS NULL) THEN
339
340 IF ( p_calling_mode = 'ROLE') THEN
341 SELECT NVL(expenditure_org_id,-99), expenditure_organization_id, expenditure_type,
342 fcst_job_id, fcst_job_group_id
343 INTO l_expenditure_org_id,l_expenditure_organization_id,l_expenditure_type,
344 l_forecast_job_id,l_forecast_job_group_id
345 FROM pa_project_assignments
346 WHERE project_id = p_project_id
347 AND assignment_id = p_item_id;
348
349 IF g1_debug_mode = 'Y' THEN
350 PA_DEBUG.g_err_stage := 'RTS12 : After Role expenditure org id/orgnz id , job,job grp id par';
351 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
352 END IF;
353
354 ELSIF ( p_calling_mode = 'ASSIGNMENT') THEN
355 BEGIN -- Added for Bug 3877942
356 SELECT NVL(resource_org_id,-99),resource_organization_id,
357 job_id
358 INTO l_expenditure_org_id,l_expenditure_organization_id,
359 l_forecast_job_id
360 FROM pa_resources_denorm
361 WHERE person_id = p_person_id
362 AND ( p_rate_calc_date BETWEEN TRUNC(resource_effective_start_date) AND
363 NVL(TRUNC(Resource_effective_end_date),p_rate_calc_date));
364 EXCEPTION -- Added Exception block for Bug 3877942
365 WHEN NO_DATA_FOUND THEN
366 IF g1_debug_mode = 'Y' THEN
367 PA_DEBUG.g_err_stage := 'RTS12 : No Record in PA_RESOURCES_DENORM for this period start date' || p_rate_calc_date;
368 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
369 END IF;
370 PA_DEBUG.Reset_Curr_Function;
371 RETURN;
372 END;
373
374 IF g1_debug_mode = 'Y' THEN
375 PA_DEBUG.g_err_stage := 'RTS12 : After Asgn resource org id/orgnz id , job id par';
376 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
377 END IF;
378
379 SELECT job_group_id
380 INTO l_forecast_job_group_id
381 FROM per_jobs
382 WHERE job_id = l_forecast_job_id;
383
384 IF g1_debug_mode = 'Y' THEN
385 PA_DEBUG.g_err_stage := 'RTS13 : After Asgn job grp id par';
386 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
387 END IF;
388
389 SELECT expenditure_type
390 INTO l_expenditure_type
391 FROM pa_project_assignments
392 WHERE project_id = p_project_id
393 AND assignment_id = p_item_id;
394 -- dbms_output.put_line(' after all assignment select ');
395
396 ELSE /*for p_calling_mode <> 'ROLE' /'ASSIGNMENT'*/
397 /*LCE Changes : Selecting override organization if any. */
398
399 IF g1_debug_mode = 'Y' THEN
400 PA_DEBUG.g_err_stage := 'RTS13.1 : Selecting override organization if any .';
401 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
402 END IF;
403
404 PA_COST.override_exp_organization(P_item_date => p_rate_calc_date ,
405 P_person_id => p_person_id ,
406 P_project_id => p_project_id ,
407 P_incurred_by_organz_id => l_expenditure_organization_id ,
408 P_Expenditure_type => l_expenditure_type ,
409 X_overr_to_organization_id => l_overr_to_organization_id ,
410 x_return_status => l_x_return_status ,
411 x_msg_count => x_msg_count ,
412 x_msg_data => l_msg_data
413 );
414
415 IF g1_debug_mode = 'Y' THEN
416 PA_DEBUG.g_err_stage := 'RTS13.2 : No override ...selecting expenditure organization id ';
417 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
418 END IF;
419
420 IF l_overr_to_organization_id is NULL THEN
421 BEGIN
422 SELECT organization_id
423 INTO l_expenditure_organization_id
424 FROM PER_ALL_ASSIGNMENTS_F -- Bug 4358495 : per_assignments_f
425 WHERE person_id = p_person_id
426 AND primary_flag ='Y'
427 -- AND assignment_type ='E'
428 AND assignment_type IN ('E','C') -- Modified for CWK impacts
429 AND TRUNC (p_rate_calc_date) BETWEEN TRUNC(Effective_start_date)
430 AND TRUNC(Effective_End_date); /* Removed nvl on effective_end_date
431 as it is a NOT NULL column For bug 2911451 */
432
433 EXCEPTION
434 WHEN NO_DATA_FOUND THEN
435 IF g1_debug_mode = 'Y' THEN
436 PA_DEBUG.g_err_stage :='RTS13.3 :No Expenditure organization id assigned to the person id :'||
437 P_person_id;
438 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
439 END IF;
440 x_cost_rejct_reason :='NO_ASSIGN';
441 END;
442 ELSE
443
444 l_expenditure_organization_id := l_overr_to_organization_id;
445 END IF;
446
447 /*End of LCE changes*/
448
449 END IF;
450
451 IF g1_debug_mode = 'Y' THEN
452 PA_DEBUG.g_err_stage := 'RTS14 : After Asgn expenditure_type par';
453 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
454 END IF;
455
456 IF p_expenditure_org_id IS NOT NULL THEN
457 l_expenditure_org_id := p_expenditure_org_id ;
458 END IF;
459 IF p_expenditure_organization_id IS NOT NULL THEN
460 l_expenditure_organization_id := p_expenditure_organization_id;
461 END IF;
462
463 IF p_expenditure_type IS NOT NULL THEN
464 l_expenditure_type := p_expenditure_type;
465 END IF;
466 IF p_forecast_job_id IS NOT NULL THEN
467 l_forecast_job_id := p_forecast_job_id;
468 END IF;
469 ELSE
470
471 l_expenditure_org_id := p_expenditure_org_id ;
472 l_expenditure_organization_id := p_expenditure_organization_id;
473 l_expenditure_type := p_expenditure_type;
474 l_forecast_job_id := p_forecast_job_id;
475 l_forecast_job_group_id := p_forecast_job_group_id;
476 END IF; /* Expenditure org id and others related if */
477
478
479
480 -- dbms_output.put_line('l_expenditure_org_id '||to_char(l_expenditure_org_id));
481 -- dbms_output.put_line('l_expenditure_organization_id '||to_char(l_expenditure_organization_id));
482 -- dbms_output.put_line('l_expenditure_type '||l_expenditure_type);
483 -- dbms_output.put_line('l_forecast_job_id '||to_char(l_forecast_job_id));
484 -- dbms_output.put_line('l_forecast_job_group_id '||to_char(l_forecast_job_group_id));
485
486
487
488 /* Selecting expenditure currency code from project set of books and implementations table
489 only if the passed value is null otherwise storing passed values */
490 IF ( p_expenditure_currency_code IS NULL) THEN
491 SELECT glsb.currency_code
492 INTO l_expenditure_currency_code
493 FROM gl_sets_of_books glsb, pa_implementations_all paimp
494 WHERE glsb.set_of_books_id = paimp.set_of_books_id
495 AND paimp.org_id = l_expenditure_org_id; -- bug 7413961 skkoppul: removed NVL function
496 ELSE
497 l_expenditure_currency_code := p_expenditure_currency_code;
498 END IF;
499
500 l_expenditure_curr_code_burdn := l_expenditure_currency_code; /* Made for Org Forecasting */
501
502 IF g1_debug_mode = 'Y' THEN
503 PA_DEBUG.g_err_stage := 'RTS15 : After currency code par';
504 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
505 END IF;
506 -- dbms_output.put_line('l_expenditure_currency_code '||l_expenditure_currency_code);
507
508
509 /* Selecting labor cost mult name from tasks table only if the passed value is null and task id
510 is not null otherwise storing passed values */
511 IF ( p_task_id IS NOT NULL ) THEN
512 IF ( p_labor_cost_multi_name IS NULL ) THEN
513 SELECT labor_cost_multiplier_name
514 INTO l_labor_cost_mult_name
515 FROM pa_tasks
516 WHERE task_id = p_task_id;
517 ELSE
518 l_labor_cost_mult_name := p_labor_cost_multi_name;
519 END IF;
520 END IF;
521
522 IF g1_debug_mode = 'Y' THEN
523 PA_DEBUG.g_err_stage := 'RTS16 : After task level cost multi name par';
524 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
525 END IF;
526 -- dbms_output.put_line('1');
527
528 /* Selecting project type from project types table only if the
529 passed value is null otherwise storing passed values */
530 IF ( p_project_type IS NULL) THEN
531
532 SELECT typ.project_type
533 INTO l_project_type
534 FROM pa_project_types_all typ, pa_projects_all proj
535 WHERE proj.project_id = p_project_id
536 AND proj.project_type = typ.project_type
537 AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul: removed NVL function
538
539 ELSE
540 l_project_type := p_project_type;
541 END IF;
542
543 IF g1_debug_mode = 'Y' THEN
544 PA_DEBUG.g_err_stage := 'RTS17 : After Project type par';
545 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
546 -- dbms_output.put_line('l_project_type '||l_project_type);
547 END IF;
548
549 /* Selecting project org id, project currency code, project bill job
550 group id, employee bill rate schedule id and job bill rate schedule id from project all table
551 only if the passed value is null otherwise storing passed values */
552
553 IF (p_project_org_id IS NULL) OR (p_projfunc_currency_code IS NULL) OR (p_project_bill_job_group_id IS NULL) OR
554 (p_emp_bill_rate_schedule_id IS NULL) OR (p_job_bill_rate_schedule_id IS NULL) THEN
555 SELECT NVL(org_id,-99), bill_job_group_id,
556 emp_bill_rate_schedule_id,job_bill_rate_schedule_id,
557 labor_schedule_fixed_date,
558 projfunc_currency_code,
559 projfunc_bil_rate_date_code, /* Added the following column for MCB2 */
560 projfunc_bil_rate_type,
561 projfunc_bil_rate_date,
562 projfunc_bil_exchange_rate,
563 projfunc_cost_rate_date,
564 projfunc_cost_rate_type,
565 NVL(assign_precedes_task,'1'),/* Added for Asgmt overide */
566 project_currency_code, /* Added for Org Forecasting */
567 project_bil_rate_date_code, /* Added for Org Forecasting */
568 project_bil_rate_type, /* Added for Org Forecasting */
569 project_bil_rate_date, /* Added for Org Forecasting */
570 project_bil_exchange_rate, /* Added for Org Forecasting */
571 project_rate_date, /* Added for Org Forecasting */
572 project_rate_type, /* Added for Org Forecasting */
573 labor_schedule_discount, /* Added for Org Forecasting */
574 labor_bill_rate_org_id, /* Added for Org Forecasting */
575 labor_std_bill_rate_schdl, /* Added for Org Forecasting */
576 labor_schedule_fixed_date, /* Added for Org Forecasting */
577 labor_sch_type /* Added for Org Forecasting */
578 INTO l_project_org_id,l_project_bill_job_group_id,
579 l_emp_bill_rate_schedule_id,l_job_bill_rate_schedule_id ,
580 l_labor_schedule_fixed_date,
581 l_projfunc_currency_code,
582 l_projfunc_bil_rate_date_code, /* Added the following columns for MCB2 */
583 l_projfunc_bil_rate_type,
584 l_projfunc_bil_rate_date,
585 l_projfunc_bil_exchange_rate,
586 l_projfunc_cost_rate_date,
587 l_projfunc_cost_rate_type,
588 l_assignment_precedes_task,
589 l_project_currency_code,
590 l_project_bil_rate_date_code,
591 l_project_bil_rate_type,
592 l_project_bil_rate_date,
593 l_project_bil_exchange_rate,
594 l_project_cost_rate_date,
595 l_project_cost_rate_type,
596 l_labor_schedule_discount,
597 l_labor_bill_rate_org_id,
598 l_labor_std_bill_rate_schedule,
599 l_labor_schedule_fixed_date,
600 l_labor_schedule_type
601 FROM pa_projects_all
602 WHERE project_id = p_project_id;
603
604 IF g1_debug_mode = 'Y' THEN
605 -- dbms_output.put_line('projfunc currency '||l_projfunc_currency_code);
606 PA_DEBUG.g_err_stage := 'RTS18 : After emp,job rate schedule , currency code and org id par';
607 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
608 END IF;
609
610 ELSE
611 IF p_projfunc_currency_code IS NOT NULL THEN
612 l_projfunc_currency_code := p_projfunc_currency_code;
613 END IF;
614 IF p_project_org_id IS NOT NULL THEN
615 l_project_org_id := p_project_org_id;
616 END IF;
617 IF p_project_bill_job_group_id IS NOT NULL THEN
618 l_project_bill_job_group_id := p_project_bill_job_group_id;
619 END IF;
620 IF p_emp_bill_rate_schedule_id IS NOT NULL THEN
621 l_emp_bill_rate_schedule_id := p_emp_bill_rate_schedule_id;
622 END IF;
623 IF p_job_bill_rate_schedule_id IS NOT NULL THEN
624 l_job_bill_rate_schedule_id := p_job_bill_rate_schedule_id;
625 END IF;
626
627 /* Added for Org Forecasting */
628 IF p_labor_schdl_discnt IS NOT NULL THEN
629 l_labor_schedule_discount := p_labor_schdl_discnt;
630 END IF;
631
632 IF p_labor_bill_rate_org_id IS NOT NULL THEN
633 l_labor_bill_rate_org_id := p_labor_bill_rate_org_id;
634 END IF;
635
636 IF p_labor_std_bill_rate_schdl IS NOT NULL THEN
637 l_labor_std_bill_rate_schedule := p_labor_std_bill_rate_schdl;
638 END IF;
639
640 IF p_labor_schedule_fixed_date IS NOT NULL THEN
641 l_labor_schedule_fixed_date := p_labor_schedule_fixed_date;
642 END IF;
643
644 IF p_labor_sch_type IS NOT NULL THEN
645 l_labor_schedule_type := p_labor_sch_type;
646 END IF;
647
648 IF p_projfunc_rev_rt_date IS NOT NULL THEN
649 l_projfunc_bil_rate_date := p_projfunc_rev_rt_date;
650 END IF;
651
652 IF p_projfunc_rev_rt_type IS NOT NULL THEN
653 l_projfunc_bil_rate_type := p_projfunc_rev_rt_type;
654 END IF;
655
656 IF p_projfunc_rev_exch_rt IS NOT NULL THEN
657 l_projfunc_bil_exchange_rate := p_projfunc_rev_exch_rt;
658 END IF;
659
660 IF p_projfunc_cst_rt_date IS NOT NULL THEN
661 l_projfunc_cost_rate_date := p_projfunc_cst_rt_date;
662 END IF;
663
664 IF p_projfunc_cst_rt_type IS NOT NULL THEN
665 l_projfunc_cost_rate_type := p_projfunc_cst_rt_type;
666 END IF;
667
668 IF p_project_currency_code IS NOT NULL THEN
669 l_project_currency_code := p_project_currency_code;
670 END IF;
671
672 IF p_project_rev_rt_date IS NOT NULL THEN
673 l_project_bil_rate_date := p_project_rev_rt_date;
674 END IF;
675
676 IF p_project_rev_rt_type IS NOT NULL THEN
677 l_project_bil_rate_type := p_project_rev_rt_type;
678 END IF;
679
680 IF p_project_rev_exch_rt IS NOT NULL THEN
681 l_project_bil_exchange_rate := p_project_rev_exch_rt;
682 END IF;
683
684 IF p_project_cst_rt_date IS NOT NULL THEN
685 l_project_cost_rate_date := p_project_cst_rt_date;
686 END IF;
687
688 IF p_project_cst_rt_type IS NOT NULL THEN
689 l_project_cost_rate_type := p_project_cst_rt_type;
690 END IF;
691
692 END IF;
693
694
695
696 -- dbms_output.put_line('l_projfunc_currency_code '||l_projfunc_currency_code);
697 -- dbms_output.put_line('l_project_org_id '||to_char(l_project_org_id));
698 -- dbms_output.put_line('l_project_bill_job_group_id '||to_char(l_project_bill_job_group_id));
699 -- dbms_output.put_line('l_emp_bill_rate_schedule_id '||to_char(l_emp_bill_rate_schedule_id));
700 -- dbms_output.put_line('l_job_bill_rate_schedule_id '||to_char(l_job_bill_rate_schedule_id));
701
702
703
704
705 /* Selecting project cost job group id,job cost rate schedule id from forecasting options and
706 pa std billrate table only if the passed value is null otherwise storing passed values */
707
708 IF ( p_proj_cost_job_group_id IS NULL) OR ( p_job_cost_rate_schedule_id IS NULL) THEN
709 SELECT bschal.job_group_id,foptal.job_cost_rate_schedule_id
710 INTO l_proj_cost_job_grp_id,l_job_cost_rate_schedule_id
711 FROM pa_std_bill_rate_schedules_all bschal,pa_forecasting_options_all foptal
712 WHERE bschal.bill_rate_sch_id = foptal.job_cost_rate_schedule_id
713 /* For Bug 4101595: Reverted the fix done for 3786192 */
714 /* AND nvl(foptal.org_id, -99) = nvl(l_expenditure_org_id, -99) */ /* Added for 3786192 */
715 AND foptal.org_id = l_project_org_id; -- bug 7413961 skkoppul: removed NVL function, changed bschal.org_id to foptal.org_id
716
717 ELSE
718 l_proj_cost_job_grp_id := p_proj_cost_job_group_id;
719 l_job_cost_rate_schedule_id := p_job_cost_rate_schedule_id;
720 END IF;
721
722 IF g1_debug_mode = 'Y' THEN
723 PA_DEBUG.g_err_stage := 'RTS19 : After cost job group id and cost rate schedule id par';
724 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
725 END IF;
726 -- dbms_output.put_line('l_proj_cost_job_grp_id '||to_char(l_proj_cost_job_grp_id));
727 -- dbms_output.put_line('l_job_cost_rate_schedule_id '||to_char(l_job_cost_rate_schedule_id));
728
729 /* commented for Org Forecasting
730 IF (l_labor_schedule_fixed_date IS NULL) THEN
731 SELECT labor_schedule_fixed_date,
732 projfunc_currency_code,
733 projfunc_bil_rate_date_code, -- Added the following column for MCB2
734 projfunc_bil_rate_type,
735 projfunc_bil_rate_date,
736 projfunc_bil_exchange_rate,
737 projfunc_cost_rate_date,
738 projfunc_cost_rate_type,
739 NVL(assign_precedes_task,'1') -- Added for Asgmt overide
740 INTO l_labor_schedule_fixed_date,
741 l_projfunc_currency_code,l_projfunc_bil_rate_date_code, -- Added the following columns for MCB2
742 l_projfunc_bil_rate_type,l_projfunc_bil_rate_date,l_projfunc_bil_exchange_rate,
743 l_projfunc_cost_rate_date,l_projfunc_cost_rate_type,
744 l_assignment_precedes_task
745 FROM pa_projects_all
746 WHERE project_id = p_project_id;
747
748 END IF;
749
750 */
751
752 IF g1_debug_mode = 'Y' THEN
753 PA_DEBUG.g_err_stage := 'RTS20 : After fixed date par';
754 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
755
756 -- dbms_output.put_line('l_job_cost_rate_schedule_id '||to_char(l_job_cost_rate_schedule_id));
757
758
759 PA_DEBUG.g_err_stage := 'RT11 : After Validation Entering PA_RATE_PVT_PKG.Get_Item_Amount';
760 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
761
762 -- dbms_output.put_line('l_labor_schedule_fixed_date '||to_char(l_labor_schedule_fixed_date,'dd-mon-yyyy'));
763
764 END IF;
765
766 /* Calling the rate calculation APIs */
767
768 l_Schedule_type := 'COST';
769 l_amount_calc_mode := p_amount_calc_mode; -- Added for Org Forecasting
770
771
772
773 --------------------------------------------
774 -- Initialize the successful return status
775 --------------------------------------------
776
777 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
778 l_x_process_return_status := FND_API.G_RET_STS_SUCCESS;
779
780 -------------------------------------------------
781 -- Get the Raw Cost for Transaction Currency
782 -------------------------------------------------
783
784 -- dbms_output.put_line('starting of procs ');
785
786 /* Added for Org Forecasting */
787 IF ( (l_labor_schedule_type = 'I') AND ( l_amount_calc_mode = 'REVENUE') ) THEN
788 l_amount_calc_mode := 'ALL';
789 END IF;
790
791 -- dbms_output.put_line('l_amount_calc_mode '||l_amount_calc_mode );
792 -- dbms_output.put_line('p_calling_mode '||p_calling_mode);
793 IF (l_amount_calc_mode <> 'REVENUE') THEN /* Added for Org For. { */
794
795 IF ( (p_calling_mode = 'ASSIGNMENT') OR (p_calling_mode = 'UNASSIGNED') ) THEN
796
797 IF g1_debug_mode = 'Y' THEN
798 PA_DEBUG.g_err_stage := 'RT12 : Entering PA_COST.get_raw_cost';
799 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
800 END IF;
801
802 -- dbms_output.put_line('pp_person_id '||p_person_id);
803 PA_COST.get_raw_cost (
804 P_person_id => p_person_id ,
805 P_expenditure_org_id => l_expenditure_org_id ,
806 P_expend_organization_id => l_expenditure_organization_id , /*LCE*/
807 P_labor_Cost_Mult_Name => l_labor_cost_mult_name ,
808 P_Item_date => p_rate_calc_date ,
809 px_exp_func_curr_code => l_expenditure_curr_code_burdn ,
810 P_Quantity => p_quantity ,
811 X_Raw_cost_rate => l_exp_func_raw_cost_rate , /* Change for Org. Fore */
812 X_Raw_cost => l_exp_func_raw_cost , /* Change for Org. Fore */
813 x_return_status => l_x_return_status ,
814 x_msg_count => x_msg_count ,
815 x_msg_data => l_msg_data
816 );
817
818 x_exp_func_raw_cost_rate := l_exp_func_raw_cost_rate; /* Added for Org Forecasting */
819 x_exp_func_raw_cost := l_exp_func_raw_cost; /* Added for Org Forecasting */
820 x_exp_func_curr_code := l_expenditure_curr_code_burdn; /* Added for Org Forecasting */
821
822 IF g1_debug_mode = 'Y' THEN
823 PA_DEBUG.g_err_stage := 'RT13 : Leaving PA_COST.get_raw_cost';
824 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
825 END IF;
826
827 -- dbms_output.put_line('end of get raw cost '||l_x_return_status||' rate '||x_exp_func_raw_cost_rate||' raw cost '||x_exp_func_raw_cost||' Currency '||l_expenditure_currency_code);
828
829
830 ELSIF (p_calling_mode = 'ROLE') THEN
831
832 -- dbms_output.put_line('start of req raw cost '||l_x_return_status);
833
834
835 IF g1_debug_mode = 'Y' THEN
836 PA_DEBUG.g_err_stage := 'RT12 : Entering PA_COST.requirement_raw_cost';
837 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
838 END IF;
839
840 PA_COST.requirement_raw_cost(
841 p_forecast_cost_job_group_id => l_forecast_job_group_id ,
842 p_forecast_cost_job_id => l_forecast_job_id ,
843 p_proj_cost_job_group_id => l_proj_cost_job_grp_id ,
844 px_proj_cost_job_id => l_proj_cost_job_id ,
845 p_item_date => p_rate_calc_date ,
846 p_job_cost_rate_sch_id => l_job_cost_rate_schedule_id ,
847 p_schedule_date => l_labor_schedule_fixed_date ,
848 p_quantity => p_quantity ,
849 p_cost_rate_multiplier => l_cost_rate_multiplier ,
850 P_expend_organization_id => l_expenditure_organization_id , /*LCE*/
851 p_org_id => l_project_org_id ,
852 x_raw_cost_rate => l_exp_func_raw_cost_rate ,
853 x_raw_cost => l_exp_func_raw_cost ,
854 x_txn_currency_code => l_expenditure_curr_code_burdn , /* Added for Org Forecasting */
855 x_return_status => l_x_return_status ,
856 x_msg_count => x_msg_count ,
857 x_msg_data => l_msg_data
858 );
859
860
861 x_exp_func_raw_cost_rate := l_exp_func_raw_cost_rate; /* Added for Org Forecasting */
862 x_exp_func_raw_cost := l_exp_func_raw_cost; /* Added for Org Forecasting */
863 x_exp_func_curr_code := l_expenditure_curr_code_burdn; /* Added for Org Forecasting */
864
865 IF g1_debug_mode = 'Y' THEN
866 PA_DEBUG.g_err_stage := 'RT13 : Leaving PA_COST.requirement_raw_cost';
867 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
868 END IF;
869 -- dbms_output.put_line('end of req raw cost '||l_x_return_status||' : rate '||x_exp_func_raw_cost_rate||' cost '||x_exp_func_raw_cost||' Currency '||l_expenditure_currency_code);
870
871 END IF;
872
873 -- Validating that the called procedure has run without error , if not,then not calling others
874 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(l_exp_func_raw_cost,0) = 0) THEN
875
876 -- dbms_output.put_line('in error of raw cost ');
877 /* Commented this for bug 2199203 and write the other one */
878 /* x_cost_rejct_reason := 'PA_FCST_NO_COST_RATE'; */
879 x_cost_rejct_reason := SUBSTR(l_msg_data,1,30);
880 x_exp_func_raw_cost_rate := 0;
881 x_exp_func_raw_cost := 0;
882 x_exp_func_curr_code := l_expenditure_currency_code; /* Added for Org Forecasting */
883
884 l_x_process_return_status := l_x_return_status;
885
886 -- dbms_output.put_line('in error of raw cost x_cost_rejct_reason '||NVL(x_cost_rejct_reason,'Bye Bye'));
887 -- RAISE l_raw_cost_null;
888
889 END IF;
890
891 l_new_pvdr_acct_raw_cost := l_exp_func_raw_cost;
892 l_raw_cost_rate := l_exp_func_raw_cost_rate;
893
894 -- dbms_output.put_line(' error in multi cost 1 '||x_cost_rejct_reason);
895 --------------------------------------------------------------------
896 -- To get the Override Organization Id, The procedure will be called,
897 -- This is only for Staffed assignment. (Assignment)
898 --------------------------------------------------------------------
899 IF (p_calling_mode = 'ASSIGNMENT') THEN
900
901 -- dbms_output.put_line('start of override '||l_x_return_status);
902 IF g1_debug_mode = 'Y' THEN
903 PA_DEBUG.g_err_stage := 'RT14 : Entering PA_COST.override_exp_organization';
904 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
905 END IF;
906
907 PA_COST.override_exp_organization(P_item_date => p_rate_calc_date ,
908 P_person_id => p_person_id ,
909 P_project_id => p_project_id ,
910 P_incurred_by_organz_id => l_expenditure_organization_id ,
911 P_Expenditure_type => l_expenditure_type ,
912 X_overr_to_organization_id => l_overr_to_organization_id ,
913 x_return_status => l_x_return_status ,
914 x_msg_count => x_msg_count ,
915 x_msg_data => l_msg_data
916 );
917
918 IF g1_debug_mode = 'Y' THEN
919 PA_DEBUG.g_err_stage := 'RT15 : Leaving PA_COST.override_exp_organization';
920 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
921 END IF;
922
923 -- dbms_output.put_line('end of override '||l_x_return_status ||' over id '||l_overr_to_organization_id);
924
925 END IF;
926
927 -- l_overr_to_organization_id := x_overr_to_organization_id;
928
929 -- dbms_output.put_line('start of get burden cost '||l_x_return_status);
930
931 l_expenditure_currency_code := NVL(l_expenditure_curr_code_burdn,l_expenditure_currency_code); /* Made for Org Forecasting */
932
933 IF (NVL(l_exp_func_raw_cost,0) <> 0) THEN
934
935 /* Added for Org forecasting */
936 IF g1_debug_mode = 'Y' THEN
937 PA_DEBUG.g_err_stage := 'RT16 : Entering PA_COST.get_burdened_cost';
938 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
939 END IF;
940
941 PA_COST.get_burdened_cost(
942 p_project_type => l_project_type ,
943 p_project_id => p_project_id ,
944 p_task_id => p_task_id ,
945 p_item_date => p_rate_calc_date ,
946 p_expenditure_type => l_expenditure_type ,
947 p_schedule_type => l_schedule_type ,
948 px_exp_func_curr_code => l_expenditure_currency_code ,
949 p_Incurred_by_organz_id => l_expenditure_organization_id ,
950 p_raw_cost => l_new_pvdr_acct_raw_cost ,
951 p_raw_cost_rate => l_raw_cost_rate ,
952 p_quantity => p_quantity ,
953 p_override_to_organz_id => l_overr_to_organization_id ,
954 x_burden_cost => l_exp_func_burdened_cost , /* Changed for Org Forecasting */
955 x_burden_cost_rate => l_exp_func_burdened_cost_rate , /* Changed for Org Forecasting */
956 x_return_status => l_x_return_status ,
957 x_msg_count => x_msg_count ,
958 x_msg_data => l_msg_data
959 );
960
961
962 x_exp_func_burdened_cost_rate := l_exp_func_burdened_cost_rate; /* Added for Org Forecasting */
963 x_exp_func_burdened_cost := l_exp_func_burdened_cost; /* Added for Org Forecasting */
964 x_exp_func_curr_code := l_expenditure_currency_code; /* Added for Org Forecasting */
965
966 IF g1_debug_mode = 'Y' THEN
967 PA_DEBUG.g_err_stage := 'RT17 : Leaving PA_COST.get_burdened_cost';
968 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
969 END IF;
970
971 -- dbms_output.put_line('end of get burden cost '||l_x_return_status||' burden cost '||x_exp_func_burdened_cost||' rate '||x_exp_func_burdened_cost_rate);
972
973 ELSIF (NVL(l_exp_func_raw_cost,0) = 0) THEN -- Added for bug 2347087
974 l_msg_data := 'PA_FCST_NO_COST_RATE';
975 END IF; /* Added for Org forecasting */
976
977 -- Validating that the called procedure has run without error , if not,then not calling others
978 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(x_exp_func_burdened_cost,0) = 0) THEN
979
980 /* Commented this for bug 2199203 and write the other one */
981 /* x_burdened_rejct_reason := 'PA_FCST_NO_COST_RATE'; */
982 x_burdened_rejct_reason := SUBSTR(l_msg_data,1,30);
983 x_exp_func_burdened_cost_rate := 0;
984 x_exp_func_burdened_cost := 0;
985 x_exp_func_curr_code := l_expenditure_currency_code; /* Added for Org Forecasting */
986
987 l_x_process_return_status := l_x_return_status;
988 -- RAISE l_burdened_cost_null;
989
990 END IF;
991
992 l_new_pvdr_acct_burdened_cost := l_exp_func_burdened_cost; /* Changed for Org Forecasting */
993 l_burdened_cost_rate := l_exp_func_burdened_cost_rate; /* Changed for Org Forecasting */
994
995 -- dbms_output.put_line('start of Get_Converted_Cost_Amounts '||l_x_return_status);
996
997 IF g1_debug_mode = 'Y' THEN
998 PA_DEBUG.g_err_stage := 'RT18 : Entering PA_COST.get_proj_raw_burdened_cost';
999 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1000 END IF;
1001
1002 -- dbms_output.put_line(' error in multi cost 2 '||x_cost_rejct_reason||' amount '||l_exp_func_raw_cost);
1003
1004 -- dbms_output.put_line('l_exp_func_raw_cost ' ||l_exp_func_raw_cost);
1005 /* Added for Org Forecasting */
1006 IF (NVL(l_exp_func_raw_cost,0) <> 0) THEN
1007 -- dbms_output.put_line(' inside l_exp_func_raw_cost ' ||l_exp_func_raw_cost);
1008 PA_COST.Get_Converted_Cost_Amounts(
1009 P_exp_org_id => l_expenditure_org_id,
1010 P_proj_org_id => l_project_org_id,
1011 P_project_id => p_project_id,
1012 P_task_id => p_task_id,
1013 P_item_date => p_rate_calc_date,
1014 p_system_linkage => p_system_linkage,
1015 px_txn_curr_code => l_cst_txn_curr_code,
1016 px_raw_cost => l_new_pvdr_acct_raw_cost,
1017 px_raw_cost_rate => l_raw_cost_rate,
1018 px_burden_cost => l_new_pvdr_acct_burdened_cost,
1019 px_burden_cost_rate => l_burdened_cost_rate,
1020 px_exp_func_curr_code => l_expenditure_currency_code,
1021 px_exp_func_rate_date => l_exp_func_cst_rt_date,
1022 px_exp_func_rate_type => l_exp_func_cst_rt_type,
1023 px_exp_func_exch_rate => l_exp_func_cst_exch_rt,
1024 px_exp_func_cost => l_exp_func_raw_cost,
1025 px_exp_func_cost_rate => l_exp_func_raw_cost_rate,
1026 px_exp_func_burden_cost => l_exp_func_burdened_cost,
1027 px_exp_func_burden_cost_rate => l_exp_func_burdened_cost_rate,
1028 px_proj_func_curr_code => l_projfunc_currency_code,
1029 px_projfunc_cost_rate_date => l_projfunc_cost_rate_date,
1030 px_projfunc_cost_rate_type => l_projfunc_cost_rate_type,
1031 px_projfunc_cost_exch_rate => l_projfunc_cost_exchange_rate,
1032 px_projfunc_raw_cost => l_projfunc_raw_cost ,
1033 px_projfunc_raw_cost_rate => l_projfunc_raw_cost_rate ,
1034 px_projfunc_burden_cost => l_projfunc_burdened_cost ,
1035 px_projfunc_burden_cost_rate => l_projfunc_burdened_cost_rate ,
1036 px_project_curr_code => l_project_currency_code,
1037 px_project_rate_date => l_project_cost_rate_date,
1038 px_project_rate_type => l_project_cost_rate_type,
1039 px_project_exch_rate => l_project_cost_exchange_rate,
1040 px_project_cost => l_project_raw_cost,
1041 px_project_cost_rate => l_project_raw_cost_rate,
1042 px_project_burden_cost => l_project_burdened_cost,
1043 px_project_burden_cost_rate => l_project_burdened_cost_rate,
1044 x_return_status => l_x_return_status ,
1045 x_msg_count => x_msg_count ,
1046 x_msg_data => l_msg_data
1047 );
1048
1049 x_projfunc_raw_cost := l_projfunc_raw_cost;
1050 x_projfunc_raw_cost_rate := l_projfunc_raw_cost_rate;
1051 x_projfunc_burdened_cost := l_projfunc_burdened_cost;
1052 x_projfunc_burdened_cost_rate := l_projfunc_burdened_cost_rate;
1053 x_projfunc_cst_rt_date := l_projfunc_cost_rate_date;
1054 x_projfunc_cst_rt_type := l_projfunc_cost_rate_type;
1055 x_projfunc_cst_exch_rt := l_projfunc_cost_exchange_rate;
1056
1057 x_project_raw_cst := l_project_raw_cost;
1058 x_project_raw_cst_rt := l_project_raw_cost_rate;
1059 x_project_burdned_cst := l_project_burdened_cost;
1060 x_project_burdned_cst_rt := l_project_burdened_cost_rate;
1061 x_project_cst_rt_date := l_project_cost_rate_date;
1062 x_project_cst_rt_type := l_project_cost_rate_type;
1063 x_project_cst_exch_rt := l_project_cost_exchange_rate;
1064
1065 x_exp_func_curr_code := l_expenditure_currency_code;
1066 x_exp_func_raw_cost_rate := l_exp_func_raw_cost_rate;
1067 x_exp_func_raw_cost := l_exp_func_raw_cost;
1068 x_exp_func_burdened_cost_rate := l_exp_func_burdened_cost_rate;
1069 x_exp_func_burdened_cost := l_exp_func_burdened_cost;
1070 x_exp_func_cst_rt_date := l_exp_func_cst_rt_date;
1071 x_exp_func_cst_rt_type := l_exp_func_cst_rt_type;
1072 x_exp_func_cst_exch_rt := l_exp_func_cst_exch_rt;
1073
1074 x_cst_txn_curr_code := l_cst_txn_curr_code;
1075 x_txn_raw_cst_rt := l_raw_cost_rate;
1076 x_txn_raw_cst := l_new_pvdr_acct_raw_cost;
1077 x_txn_burdned_cst_rt := l_burdened_cost_rate;
1078 x_txn_burdned_cst := l_new_pvdr_acct_burdened_cost;
1079
1080 -- dbms_output.put_line(' Inside error in multi cost 3 '||x_cost_rejct_reason);
1081 /* Deleted this proc PA_COST.get_projfunc_raw_burdened_cost() for Org Forecasting */
1082
1083 IF g1_debug_mode = 'Y' THEN
1084 PA_DEBUG.g_err_stage := 'RT19 : Leaving PA_COST.get_proj_raw_burdened_cost';
1085 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1086 END IF;
1087
1088 -- dbms_output.put_line('end of Get_Converted_Cost_Amounts '||l_x_return_status||' proj cost '||x_projfunc_raw_cost||' proj rate '||x_projfunc_raw_cost_rate
1089 -- ||' proj bur co '||x_projfunc_burdened_cost||' proj bur rate '||x_projfunc_burdened_cost_rate||' PROJECT BURDEN COST '||x_project_burdned_cst);
1090
1091 -- dbms_output.put_line('end of Get_Converted_Cost_Amounts exch rates '||l_x_return_status||' proj cost exch '||l_project_cost_exchange_rate||' proj func exch rate '||l_projfunc_cost_exchange_rate ||' exp exch rate '||l_exp_func_cst_exch_rt);
1092
1093 END IF; -- Added for Org Forecasting
1094
1095 -- Validating that the called procedure has run without error , if not,then not calling others
1096 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(l_projfunc_raw_cost,0) = 0 ) THEN
1097
1098 -- dbms_output.put_line('inside if return status is error of Get_Converted_Cost_Amounts '||l_x_return_status||' proj cost '||x_projfunc_raw_cost
1099 -- ||' proj rate '||x_projfunc_raw_cost_rate||' proj bur co '||x_projfunc_burdened_cost||' proj bur rate '||x_projfunc_burdened_cost_rate);
1100
1101 -- dbms_output.put_line(' CHECK error in multi co '||x_cost_rejct_reason);
1102 /* Commented this for bug 2199203 and write the other one */
1103 /* x_cost_rejct_reason := 'PA_FCST_NO_COST_RATE'; */
1104 x_cost_rejct_reason := SUBSTR(NVL(x_cost_rejct_reason,l_msg_data),1,30); -- Added for bug 2347087
1105 x_projfunc_raw_cost_rate := 0;
1106 x_projfunc_raw_cost := 0;
1107 x_projfunc_burdened_cost := 0;
1108 x_projfunc_burdened_cost_rate := 0;
1109
1110 --dbms_output.put_line(' The error is '||x_cost_rejct_reason);
1111 /* Added for Org Forecasting */
1112 x_projfunc_cst_rt_date := l_projfunc_cost_rate_date;
1113 x_projfunc_cst_rt_type := l_projfunc_cost_rate_type;
1114 x_projfunc_cst_exch_rt := l_projfunc_cost_exchange_rate;
1115
1116 x_project_raw_cst := 0;
1117 x_project_raw_cst_rt := 0;
1118 x_project_burdned_cst := 0;
1119 x_project_burdned_cst_rt := 0;
1120
1121 x_project_cst_rt_date := l_project_cost_rate_date;
1122 x_project_cst_rt_type := l_project_cost_rate_type;
1123 x_project_cst_exch_rt := l_project_cost_exchange_rate;
1124
1125 x_exp_func_curr_code := l_expenditure_currency_code;
1126 x_exp_func_raw_cost_rate := 0;
1127 x_exp_func_raw_cost := 0;
1128 x_exp_func_burdened_cost_rate := 0;
1129 x_exp_func_burdened_cost := 0;
1130
1131 x_exp_func_cst_rt_date := l_exp_func_cst_rt_date;
1132 x_exp_func_cst_rt_type := l_exp_func_cst_rt_type;
1133 x_exp_func_cst_exch_rt := l_exp_func_cst_exch_rt;
1134
1135 x_cst_txn_curr_code := l_cst_txn_curr_code;
1136 x_txn_raw_cst_rt := 0;
1137 x_txn_raw_cst := 0;
1138 x_txn_burdned_cst_rt := 0;
1139 x_txn_burdned_cst := 0;
1140
1141 l_x_process_return_status := l_x_return_status;
1142
1143 -- RAISE l_raw_proj_cost_null;
1144
1145 END IF;
1146
1147 -- dbms_output.put_line(' END Check error in multi cost 4 '||x_cost_rejct_reason);
1148 l_new_rcvr_acct_raw_cost := x_projfunc_raw_cost;
1149 l_new_rcvr_acct_raw_cost_rate := x_projfunc_raw_cost_rate;
1150 l_new_rcvr_acct_burdened_cost := x_projfunc_burdened_cost;
1151 l_new_rcvr_acct_bur_cost_rate := x_projfunc_burdened_cost_rate;
1152
1153
1154 END IF; /* End of p_amount_calc_mode } */
1155
1156 --------------------------------------------------------------
1157 -- Calling Bill rate API to get the bill rate and raw Revenue.
1158 --------------------------------------------------------------
1159
1160 l_Schedule_type := 'REVENUE';
1161
1162 -- dbms_output.put_line('revenue start '||l_distribution_rule);
1163
1164 IF (l_amount_calc_mode <> 'COST') THEN /* Added for Org For. { */
1165
1166 IF ((SUBSTR(l_distribution_rule,1,4) = 'WORK') AND ( l_class_code = 'CONTRACT')
1167 AND (p_calling_mode <> 'UNASSIGNED') ) THEN /* Unasigned check added for Org { */
1168
1169 l_msg_data := NULL;
1170 IF ( p_calling_mode = 'ASSIGNMENT') THEN
1171
1172 -- dbms_output.put_line('start of get rev amt '||l_x_return_status);
1173
1174 IF g1_debug_mode = 'Y' THEN
1175 PA_DEBUG.g_err_stage := 'RT20 : Entering PA_REVENUE.get_rev_amt';
1176 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1177 END IF;
1178
1179 PA_REVENUE.Assignment_Rev_Amt(
1180 p_project_id => p_project_id ,
1181 p_task_id => p_task_id ,
1182 p_bill_rate_multiplier => p_bill_rate_multiplier ,
1183 p_quantity => p_quantity ,
1184 p_person_id => p_person_id ,
1185 p_raw_cost => l_new_rcvr_acct_raw_cost ,
1186 p_item_date => p_rate_calc_date ,
1187 p_labor_schdl_discnt => l_labor_schedule_discount , -- can be null
1188 p_labor_bill_rate_org_id => l_labor_bill_rate_org_id , -- can be null
1189 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schedule , -- can be null
1190 p_labor_schdl_fixed_date => l_labor_schedule_fixed_date , -- can be null
1191 p_bill_job_grp_id => l_project_bill_job_group_id ,
1192 p_item_id => p_item_id , /* changed for bug 2212852 */
1193 p_forecast_item_id => p_forecast_item_id , /* added for bug 2212852 */
1194 p_forecasting_type => p_forecasting_type , /* added for bug 2212852 */
1195 p_labor_sch_type => l_labor_schedule_type ,
1196 p_project_org_id => l_project_org_id ,
1197 p_project_type => l_project_type ,
1198 p_expenditure_type => l_expenditure_type ,
1199 p_exp_func_curr_code => l_expenditure_currency_code ,
1200 p_incurred_by_organz_id => l_expenditure_organization_id ,
1201 p_raw_cost_rate => l_raw_cost_rate ,
1202 p_override_to_organz_id => l_overr_to_organization_id ,
1203 p_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id ,
1204 p_resource_job_id => l_forecast_job_id ,
1205 p_exp_raw_cost => l_new_pvdr_acct_raw_cost ,
1206 p_expenditure_org_id => l_expenditure_org_id ,
1207 p_projfunc_currency_code => l_projfunc_currency_code , -- The following 5
1208 p_assignment_precedes_task => l_assignment_precedes_task , /* Added for Asgmt overide */
1209 p_sys_linkage_function => p_system_linkage, /* Added for Org FCST */
1210 x_bill_rate => l_txn_rev_bill_rt , /* Change for Org Forecsting */
1211 x_raw_revenue => l_txn_rev_raw_revenue, /* Change for Org Forecasting */
1212 x_markup_percentage => l_markup_percentage ,/* Added for Asgmt overide */
1213 x_txn_currency_code => l_rev_txn_curr_code, /* added for Org */
1214 x_rev_currency_code => l_projfunc_currency_code ,
1215 x_return_status => l_x_return_status ,
1216 x_msg_count => x_msg_count ,
1217 x_msg_data => l_msg_data ,
1218 /* Added for bug 2668753 */
1219 p_project_raw_cost => l_project_raw_cost ,
1220 p_project_currency_code => l_project_currency_code ,
1221 x_adjusted_bill_rate => l_txn_adjusted_bill_rt --added the parameter for 4038485
1222 );
1223
1224 x_txn_rev_bill_rt := l_txn_rev_bill_rt;
1225 x_txn_rev_raw_revenue := l_txn_rev_raw_revenue;
1226 x_rev_txn_curr_code := l_rev_txn_curr_code;
1227
1228 IF g1_debug_mode = 'Y' THEN
1229 PA_DEBUG.g_err_stage := 'RT21 : Leaving PA_REVENUE.get_rev_amt';
1230 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1231 END IF;
1232 -- dbms_output.put_line('end of get rev amt '||l_x_return_status|| ' rate '||x_projfunc_bill_rate||' rev '||x_projfunc_raw_revenue);
1233 ELSIF (p_calling_mode= 'ROLE' ) THEN
1234
1235 -- dbms_output.put_line('start of req rev amt '||l_x_return_status);
1236
1237 IF g1_debug_mode = 'Y' THEN
1238 PA_DEBUG.g_err_stage := 'RT20 : Entering PA_REVENUE.requirement_rev_amt';
1239 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1240 END IF;
1241
1242 PA_REVENUE.Requirement_Rev_Amt(
1243 p_project_id => p_project_id ,
1244 p_task_id => p_task_id ,
1245 p_bill_rate_multiplier => p_bill_rate_multiplier ,
1246 p_quantity => p_quantity ,
1247 p_raw_cost => l_new_rcvr_acct_raw_cost ,
1248 p_item_date => p_rate_calc_date ,
1249 p_project_bill_job_grp_id => l_project_bill_job_group_id ,
1250 p_labor_schdl_discnt => l_labor_schedule_discount , -- can be null
1251 p_labor_bill_rate_org_id => l_labor_bill_rate_org_id , -- can be null
1252 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schedule , -- can be null
1253 p_labor_schdl_fixed_date => l_labor_schedule_fixed_date , -- can be null
1254 p_forecast_job_id => l_forecast_job_id ,
1255 p_forecast_job_grp_id => l_forecast_job_group_id ,
1256 p_labor_sch_type => l_labor_schedule_type , -- can be null
1257 p_item_id => p_item_id , /* changed for bug 2212852 */
1258 p_forecast_item_id => p_forecast_item_id , /* added for bug 2212852 */
1259 p_forecasting_type => p_forecasting_type , /* added for bug 2212852 */
1260 p_project_org_id => l_project_org_id ,
1261 p_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id ,
1262 p_project_type => l_project_type ,
1263 p_expenditure_type => l_expenditure_type ,
1264 px_exp_func_curr_code => l_expenditure_currency_code ,
1265 p_incurred_by_organz_id => l_expenditure_organization_id ,
1266 p_raw_cost_rate => l_raw_cost_rate ,
1267 p_override_to_organz_id => l_overr_to_organization_id ,
1268 p_exp_raw_cost => l_new_pvdr_acct_raw_cost ,
1269 p_expenditure_org_id => l_expenditure_org_id ,
1270 p_projfunc_currency_code => l_projfunc_currency_code , -- The following 5
1271 p_assignment_precedes_task => l_assignment_precedes_task , /* Added for Asgmt overide */
1272 p_sys_linkage_function => p_system_linkage, /* Added for Org FCST */
1273 px_project_bill_job_id => l_proj_bill_job_id ,
1274 x_bill_rate => l_txn_rev_bill_rt , /* Change for Org Forecsting */
1275 x_raw_revenue => l_txn_rev_raw_revenue, /* Change for Org Forecasting */
1276 x_markup_percentage => l_markup_percentage ,/* Added for Asgmt overide */
1277 x_txn_currency_code => l_rev_txn_curr_code, /* added for Org */
1278 x_return_status => l_x_return_status ,
1279 x_msg_count => x_msg_count ,
1280 x_msg_data => l_msg_data
1281 );
1282
1283
1284 x_txn_rev_bill_rt := l_txn_rev_bill_rt;
1285 x_txn_rev_raw_revenue := l_txn_rev_raw_revenue;
1286 x_rev_txn_curr_code := l_rev_txn_curr_code;
1287
1288 IF g1_debug_mode = 'Y' THEN
1289 PA_DEBUG.g_err_stage := 'RT21 : Leaving PA_REVENUE.requirement_rev_amt';
1290 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1291 END IF;
1292
1293 -- dbms_output.put_line('end of req rev amt '||l_x_return_status||' rate '||x_projfunc_bill_rate||' rev '||x_projfunc_raw_revenue);
1294
1295 END IF; /* End of calling mode if */
1296
1297
1298 -- Validating that the called procedure has run without error , if not,then not calling others
1299 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(l_txn_rev_raw_revenue,0) = 0) THEN
1300
1301 /* Commented this for bug 2199203 and write the other one */
1302 /* x_rev_rejct_reason := 'PA_FCST_NO_BILL_RATE'; */
1303 x_rev_rejct_reason := SUBSTR(l_msg_data,1,30);
1304 x_txn_rev_bill_rt := 0;
1305 x_txn_rev_raw_revenue := 0;
1306 x_rev_txn_curr_code := l_rev_txn_curr_code;
1307
1308 l_x_process_return_status := l_x_return_status;
1309
1310 END IF; /* End of return status if */
1311
1312 IF ( (NVL(l_txn_rev_raw_revenue,0) <> 0) ) THEN
1313
1314 PA_REVENUE.Get_Converted_Revenue_Amounts(
1315 p_item_date => p_rate_calc_date,
1316 px_txn_curr_code => l_rev_txn_curr_code,
1317 px_txn_raw_revenue => l_txn_rev_raw_revenue,
1318 px_txn_bill_rate => l_txn_rev_bill_rt,
1319 px_projfunc_curr_code => l_projfunc_currency_code,
1320 p_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
1321 px_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
1322 px_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
1323 px_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
1324 px_projfunc_raw_revenue => l_projfunc_raw_revenue ,
1325 px_projfunc_bill_rate => l_projfunc_bill_rate ,
1326 px_project_curr_code => l_project_currency_code,
1327 p_project_bil_rate_date_code => l_project_bil_rate_date_code,
1328 px_project_bil_rate_type => l_project_bil_rate_type,
1329 px_project_bil_rate_date => l_project_bil_rate_date,
1330 px_project_bil_exchange_rate => l_project_bil_exchange_rate,
1331 px_project_raw_revenue => l_project_raw_revenue ,
1332 px_project_bill_rate => l_project_bill_rate ,
1333 x_return_status => l_x_return_status ,
1334 x_msg_count => x_msg_count ,
1335 /* x_msg_data => x_msg_data Commnted out for bug 3143819
1336 and added modified one below i.e. instead of x_msg_data using
1337 l_msg_data */
1338 x_msg_data => l_msg_data
1339 );
1340
1341
1342 x_projfunc_bill_rate := l_projfunc_bill_rate;
1343 x_projfunc_raw_revenue := l_projfunc_raw_revenue;
1344 x_projfunc_rev_rt_date := l_projfunc_bil_rate_date;
1345 x_projfunc_rev_rt_type := l_projfunc_bil_rate_type;
1346 x_projfunc_rev_exch_rt := l_projfunc_bil_exchange_rate;
1347
1348 x_project_bill_rt := l_project_bill_rate;
1349 x_project_raw_revenue := l_project_raw_revenue;
1350 x_project_rev_rt_date := l_project_bil_rate_date;
1351 x_project_rev_rt_type := l_project_bil_rate_type;
1352 x_project_rev_exch_rt := l_project_bil_exchange_rate;
1353
1354 x_txn_rev_bill_rt := l_txn_rev_bill_rt;
1355 x_txn_rev_raw_revenue := l_txn_rev_raw_revenue;
1356 x_rev_txn_curr_code := l_rev_txn_curr_code;
1357 -- dbms_output.put_line(' after revenue conversion ');
1358 END IF;
1359
1360 -- Validating that the called procedure has run without error , if not,then not calling others
1361 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(l_projfunc_raw_revenue,0) = 0) THEN
1362
1363 x_rev_rejct_reason := SUBSTR(l_msg_data,1,30);
1364 x_txn_rev_bill_rt := 0;
1365 x_txn_rev_raw_revenue := 0;
1366 x_rev_txn_curr_code := l_rev_txn_curr_code;
1367
1368 x_projfunc_bill_rate := 0;
1369 x_projfunc_raw_revenue := 0;
1370 x_projfunc_rev_rt_date := l_projfunc_bil_rate_date;
1371 x_projfunc_rev_rt_type := l_projfunc_bil_rate_type;
1372 x_projfunc_rev_exch_rt := l_projfunc_bil_exchange_rate;
1373
1374 x_project_bill_rt := 0;
1375 x_project_raw_revenue := 0;
1376 x_project_rev_rt_date := l_project_bil_rate_date;
1377 x_project_rev_rt_type := l_project_bil_rate_type;
1378 x_project_rev_exch_rt := l_project_bil_exchange_rate;
1379
1380 l_x_process_return_status := l_x_return_status;
1381
1382 END IF; /* End of return status if */
1383
1384 END IF; /* End of rule and class code if } */
1385 END IF; /* p_amount_calc_mode if } */
1386
1387 l_new_rcvr_revenue := x_projfunc_raw_revenue;
1388
1389 -------------------------------------------------------
1390 -- Assign the successful status back to output variable
1391 -------------------------------------------------------
1392
1393 x_return_status := l_x_process_return_status;
1394 x_msg_data := l_msg_data;
1395
1396 -- dbms_output.put_line(' End error in multi cost '||x_cost_rejct_reason);
1397 -- dbms_output.put_line('end of procs '||l_x_return_status);
1398 IF g1_debug_mode = 'Y' THEN
1399 PA_DEBUG.g_err_stage := 'RT22 : Leaving PA_RATE_PVT_PKG.get_item_amount';
1400 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1401 PA_DEBUG.Reset_Curr_Function;
1402 END IF;
1403
1404
1405 EXCEPTION
1406 WHEN l_insufficient_parameters THEN
1407 IF g1_debug_mode = 'Y' THEN
1408 PA_DEBUG.Reset_Curr_Function;
1409 END IF;
1410 x_return_status := FND_API.G_RET_STS_ERROR;
1411 x_msg_count := 1;
1412 x_msg_data := 'PA_FCST_INSUFFICIENT_PARA';
1413 x_others_rejct_reason := 'PA_FCST_INSUFFICIENT_PARA';
1414
1415 WHEN l_raw_cost_null THEN
1416 IF g1_debug_mode = 'Y' THEN
1417 PA_DEBUG.Reset_Curr_Function;
1418 END IF;
1419 x_return_status := FND_API.G_RET_STS_ERROR;
1420 x_cost_rejct_reason := 'PA_FCST_NO_COST_RATE';
1421 x_exp_func_raw_cost_rate := 0;
1422 x_exp_func_raw_cost := 0;
1423 WHEN l_raw_proj_cost_null THEN
1424 IF g1_debug_mode = 'Y' THEN
1425 PA_DEBUG.Reset_Curr_Function;
1426 END IF;
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428 x_cost_rejct_reason := 'PA_FCST_NO_COST_RATE';
1429 /* Added for Org Forecasting */
1430 x_projfunc_raw_cost_rate := 0;
1431 x_projfunc_raw_cost := 0;
1432 x_projfunc_burdened_cost := 0;
1433 x_projfunc_burdened_cost_rate := 0;
1434
1435 x_project_raw_cst := 0;
1436 x_project_raw_cst_rt := 0;
1437 x_project_burdned_cst := 0;
1438 x_project_burdned_cst_rt := 0;
1439
1440 x_exp_func_raw_cost_rate := 0;
1441 x_exp_func_raw_cost := 0;
1442 x_exp_func_burdened_cost_rate := 0;
1443 x_exp_func_burdened_cost := 0;
1444
1445 x_txn_raw_cst_rt := 0;
1446 x_txn_raw_cst := 0;
1447 x_txn_burdned_cst_rt := 0;
1448 x_txn_burdned_cst := 0;
1449
1450 --dbms_output.put_line('exp2');
1451 WHEN l_raw_revenue_null THEN
1452 IF g1_debug_mode = 'Y' THEN
1453 PA_DEBUG.Reset_Curr_Function;
1454 END IF;
1455 x_return_status := FND_API.G_RET_STS_ERROR;
1456 x_rev_rejct_reason := 'PA_FCST_NO_BILL_RATE';
1457 x_txn_rev_bill_rt := 0;
1458 x_txn_rev_raw_revenue := 0;
1459
1460 --dbms_output.put_line('exp3');
1461
1462 WHEN l_burdened_cost_null THEN
1463 IF g1_debug_mode = 'Y' THEN
1464 PA_DEBUG.Reset_Curr_Function;
1465 END IF;
1466 x_return_status := FND_API.G_RET_STS_ERROR;
1467 x_burdened_rejct_reason := 'PA_FCST_NO_COST_RATE';
1468 x_exp_func_burdened_cost_rate := 0;
1469 x_exp_func_burdened_cost := 0;
1470
1471 --dbms_output.put_line('exp4');
1472
1473 WHEN OTHERS THEN
1474 IF g1_debug_mode = 'Y' THEN
1475 PA_DEBUG.Reset_Curr_Function;
1476 END IF;
1477
1478 --dbms_output.put_line('exp5'||SQLERRM);
1479
1480 /* ATG Changes */
1481
1482 x_projfunc_bill_rate := null;
1483 x_projfunc_raw_revenue := null;
1484 x_projfunc_rev_rt_date := null;
1485 x_projfunc_rev_rt_type := null;
1486 x_projfunc_rev_exch_rt := null;
1487 x_projfunc_raw_cost := null;
1488 x_projfunc_raw_cost_rate := null;
1489 x_projfunc_burdened_cost := null;
1490 x_projfunc_burdened_cost_rate := null;
1491 x_projfunc_cst_rt_date := null;
1492 x_projfunc_cst_rt_type := null;
1493 x_projfunc_cst_exch_rt := null;
1494 x_project_bill_rt := null;
1495 x_project_raw_revenue := null;
1496 x_project_rev_rt_date := null;
1497 x_project_rev_rt_type := null;
1498 x_project_rev_exch_rt := null;
1499 x_project_raw_cst := null;
1500 x_project_raw_cst_rt := null;
1501 x_project_burdned_cst := null;
1502 x_project_burdned_cst_rt := null;
1503 x_project_cst_rt_date := null;
1504 x_project_cst_rt_type := null;
1505 x_project_cst_exch_rt := null;
1506 x_exp_func_curr_code := null;
1507 x_exp_func_raw_cost_rate := null;
1508 x_exp_func_raw_cost := null;
1509 x_exp_func_burdened_cost_rate := null;
1510 x_exp_func_burdened_cost := null;
1511 x_exp_func_cst_rt_date := null;
1512 x_exp_func_cst_rt_type := null;
1513 x_exp_func_cst_exch_rt := null;
1514 x_cst_txn_curr_code := null;
1515 x_txn_raw_cst_rt := null;
1516 x_txn_raw_cst := null;
1517 x_txn_burdned_cst_rt := null;
1518 x_txn_burdned_cst := null;
1519 x_rev_txn_curr_code := null;
1520 x_txn_rev_bill_rt := null;
1521 x_txn_rev_raw_revenue := null;
1522
1523
1524
1525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1526 x_error_msg := SUBSTR(SQLERRM,1,30);
1527 /* Checking error condition. Added for bug 2218386 */
1528 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1529 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
1530 p_procedure_name => 'Get_Item_Amount');
1531 RAISE;
1532 END IF;
1533 END Get_Item_Amount;
1534
1535
1536 -- This procedure will calculate the revenue for fixed price in event based rule on basis of passed parameters
1537 -- Input parameters
1538 -- Parameters Type Required Description
1539 -- P_project_id NUMBER YES Project Id
1540 -- Out parameters
1541 --
1542 -- x_proj_revenue_tab ProjAmt_TabTyp YES It store the amount and period name
1543 -- for fixed price project
1544
1545 PROCEDURE calc_event_based_revenue(
1546 p_project_id IN NUMBER ,
1547 p_rev_amt IN NUMBER,
1548 p_completion_date IN DATE,
1549 p_project_currency_code IN VARCHAR2, -- The following 6
1550 p_projfunc_currency_code IN VARCHAR2,
1551 p_projfunc_bil_rate_date_code IN VARCHAR2, -- columns have been
1552 px_projfunc_bil_rate_type IN OUT NOCOPY VARCHAR2, -- added for MCB2 --File.Sql.39 bug 4440895
1553 px_projfunc_bil_rate_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
1554 px_projfunc_bil_exchange_rate IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1555 x_error_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1556 x_projfunc_revenue_tab OUT NOCOPY PA_RATE_PVT_PKG.ProjAmt_TabTyp /* This tabwill store aount in project functional currency */) --File.Sql.39 bug 4440895
1557 IS
1558
1559 l_completion_date pa_projects_all.completion_date%TYPE;
1560 l_period_type pa_rep_period_dates_v.period_type%TYPE;
1561 l_period_name pa_rep_period_dates_v.period_name%TYPE;
1562 l_start_date pa_rep_period_dates_v.start_date%TYPE;
1563 l_end_date pa_rep_period_dates_v.end_date%TYPE;
1564
1565 /* Added for MCB2 */
1566 l_converted_rev_amount pa_projects_all.project_value%TYPE;
1567 l_conversion_fail EXCEPTION;
1568 l_denominator Number;
1569 l_numerator Number;
1570 l_status Varchar2(30);
1571
1572 l_period_year NUMBER; /* Added for bug 2691192 */
1573 l_error_value VARCHAR2(50); /* Added for bug 2691192 */
1574
1575
1576 lx_projfunc_bil_rate_type VARCHAR2(30);
1577 lx_projfunc_bil_rate_date DATE;
1578 lx_projfunc_bil_exchange_rate NUMBER;
1579
1580
1581 BEGIN
1582
1583 /* ATG Changes */
1584
1585 lx_projfunc_bil_rate_type := px_projfunc_bil_rate_type;
1586 lx_projfunc_bil_rate_date := px_projfunc_bil_rate_date ;
1587 lx_projfunc_bil_exchange_rate := px_projfunc_bil_exchange_rate ;
1588
1589
1590
1591 /* Validating the project end date and project value if the project does not have
1592 end date the taking the max end date of the assignment schedule which belongs to
1593 this project */
1594
1595
1596 IF (p_completion_date IS NULL) THEN /* Bug fix 1842755 */
1597 BEGIN
1598 SELECT MAX(end_date)
1599 INTO l_completion_date
1600 FROM pa_schedules
1601 WHERE project_id = p_project_id
1602 AND DECODE (schedule_type_code, 'OPEN_ASSIGNMENT',
1603 pa_assignment_utils.Is_Asgmt_In_Open_Status(status_code,'OPEN_ASGMT'),
1604 'STAFFED_ASSIGNMENT',DECODE(
1605 pa_assignment_utils.Is_Staffed_Asgmt_Cancelled(status_code,'STAFFED_ASGMT'),'Y','N','Y'),'N') = 'Y'
1606 AND DECODE(schedule_type_code,
1607 'OPEN_ASSIGNMENT', pa_project_utils.check_prj_stus_action_allowed(
1608 status_code, 'OPEN_ASGMT_PROJ_FORECASTING'),
1609 'STAFFED_ASSIGNMENT', pa_project_utils.check_prj_stus_action_allowed(
1610 status_code, 'STAFFED_ASGMT_PROJ_FORECASTING'),
1611 'STAFFED_ADMIN_ASSIGNMENT', pa_project_utils.check_prj_stus_action_allowed(
1612 status_code, 'STAFFED_ASGMT_PROJ_FORECASTING'),'N') = 'Y';
1613 EXCEPTION
1614 WHEN NO_DATA_FOUND THEN
1615 x_error_code := 'PA_FCST_PDS_NOT_DEFINED';
1616 NULL;
1617 END;
1618 ELSE
1619 l_completion_date := p_completion_date;
1620 END IF;
1621
1622 -- dbms_output.put_line(' 11');
1623 -- Populating period type for forecasting it can be GL or PA using profile options
1624 l_period_type := FND_PROFILE.VALUE('PA_FORECASTING_PERIOD_TYPE');
1625
1626 -- dbms_output.put_line(' 12 '||l_period_type);
1627
1628 /* Added code for bug 2691192, this proc. will replace the call of
1629 pa_rep_period_dates_v.Because of performance issue tis view has been split
1630 in this new call of proc. */
1631
1632 PA_RATE_PVT_PKG.get_rep_period_dates (
1633 p_period_type => l_period_type,
1634 p_completion_date => l_completion_date,
1635 x_period_year => l_period_year,
1636 x_period_name => l_period_name,
1637 x_start_date => l_start_date,
1638 x_end_date => l_end_date,
1639 x_error_value => l_error_value
1640 );
1641 -- dbms_output.put_line(' 13 '||l_error_value);
1642
1643 IF ( l_error_value = 'NO_ERROR') THEN
1644 -- Do not raise error
1645 NULL;
1646 ELSIF ( l_error_value = 'NO_DATA_FOUND' ) THEN
1647 RAISE NO_DATA_FOUND;
1648 ELSIF (l_error_value = 'TOO_MANY_ROWS') THEN
1649 RAISE TOO_MANY_ROWS;
1650 END IF;
1651
1652 /* Fix for bug 2691192 till here */
1653
1654 /* Taking period name corresponds to the period type */
1655 /* Commneting out for bug 2691192
1656 SELECT period_name,start_date,end_date
1657 INTO l_period_name,l_start_date,l_end_date
1658 FROM pa_rep_period_dates_v
1659 WHERE period_type = l_period_type
1660 AND l_completion_date BETWEEN start_date AND end_date;
1661 */
1662
1663 /* Commented the below condition for bug 2193832, because even if the
1664 start date of the period is in one particular year, the period year could be previous on or
1665 depending upon how the ct has setup e.g. start date 01-jan-2001, but period year will be 2000
1666
1667 AND TO_CHAR(l_completion_date,'YYYY') = period_year; */
1668
1669
1670
1671 -- NOTE The following conversion is not going to be used in this dev drop
1672 -- because project value will be in PFC
1673
1674 /* Converting the amount into project functional currency for MCB2
1675 IF (p_project_currency_code <> p_projfunc_currency_code ) THEN
1676 PA_MULTI_CURRENCY.convert_amount(
1677 P_FROM_CURRENCY => p_project_currency_code,
1678 P_TO_CURRENCY => p_projfunc_currency_code,
1679 P_CONVERSION_DATE => l_completion_date,
1680 P_CONVERSION_TYPE => px_projfunc_bil_rate_type,
1681 P_AMOUNT => p_rev_amt,
1682 P_USER_VALIDATE_FLAG => 'Y',
1683 P_HANDLE_EXCEPTION_FLAG => 'Y',
1684 P_CONVERTED_AMOUNT => l_converted_rev_amount,
1685 P_DENOMINATOR => l_denominator,
1686 P_NUMERATOR => l_numerator,
1687 P_RATE => px_projfunc_bil_exchange_rate,
1688 X_STATUS => l_status);
1689
1690 IF (l_status IS NOT NULL) THEN
1691 RAISE l_conversion_fail;
1692 END IF;
1693
1694 x_projfunc_revenue_tab(1).amount := l_converted_rev_amount;
1695
1696 END IF; */
1697
1698 x_projfunc_revenue_tab(1).amount := p_rev_amt;
1699 x_projfunc_revenue_tab(1).period_name := l_period_name;
1700 x_projfunc_revenue_tab(1).start_date := l_start_date;
1701 x_projfunc_revenue_tab(1).end_date := l_end_date;
1702 EXCEPTION
1703 WHEN NO_DATA_FOUND THEN
1704 x_error_code := 'PA_FCST_PDS_NOT_DEFINED';
1705 NULL;
1706 WHEN l_conversion_fail THEN
1707 x_error_code := l_status||'_BC_PF';
1708 WHEN OTHERS THEN
1709
1710 /* ATG Changes */
1711
1712 px_projfunc_bil_rate_type := lx_projfunc_bil_rate_type;
1713 px_projfunc_bil_rate_date := lx_projfunc_bil_rate_date ;
1714 px_projfunc_bil_exchange_rate := lx_projfunc_bil_exchange_rate ;
1715
1716 /* Checking error condition. Added for bug 2218386 */
1717 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1718 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
1719 p_procedure_name => 'calc_event_based_revenue');
1720 RAISE;
1721 END IF;
1722
1723 END calc_event_based_revenue;
1724
1725
1726 -- This procedure will calculate the revenue for fixed price in cost based rule on basis of passed parameters
1727 -- Input parameters
1728 -- Parameters Type Required Description
1729 -- P_project_id NUMBER YES Project Id
1730 -- p_rev_amt NUMBER YES Revenue amount for project
1731 -- p_proj_cost_tab ProjAmt_TabTyp YES It contains the amount and period name
1732 -- for fixed price project
1733 -- Out parameters
1734 --
1735 -- x_proj_revenue_tab ProjAmt_TabTyp YES It stores the amount and period name
1736 -- for fixed price project
1737
1738 PROCEDURE calc_cost_based_revenue(
1739 p_project_id IN NUMBER ,
1740 p_rev_amt IN NUMBER ,
1741 p_projfunc_cost_tab IN PA_RATE_PVT_PKG.ProjAmt_TabTyp,
1742 p_project_currency_code IN VARCHAR2, -- The following 6
1743 p_projfunc_currency_code IN VARCHAR2,
1744 p_projfunc_bil_rate_date_code IN VARCHAR2, -- columns have been
1745 px_projfunc_bil_rate_type IN OUT NOCOPY VARCHAR2, -- added for MCB2 --File.Sql.39 bug 4440895
1746 px_projfunc_bil_rate_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
1747 px_projfunc_bil_exchange_rate IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1748 x_projfunc_revenue_tab OUT NOCOPY PA_RATE_PVT_PKG.ProjAmt_TabTyp, --File.Sql.39 bug 4440895
1749 x_error_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1750 )
1751 IS
1752
1753 l_proj_rev NUMBER;
1754 l_period_rev NUMBER;
1755 l_tot_period_rev NUMBER;
1756 l_last_num NUMBER;
1757 l_proj_cost NUMBER;
1758 l_running_cost NUMBER:=0;
1759
1760 /* Added for MCB2 */
1761 l_converted_rev_amount pa_projects_all.project_value%TYPE;
1762 l_conversion_fail EXCEPTION;
1763 l_denominator Number;
1764 l_numerator Number;
1765 l_status Varchar2(30);
1766 l_conversion_date DATE;
1767
1768 lx_projfunc_bil_rate_type VARCHAR2(30);
1769 lx_projfunc_bil_rate_date DATE;
1770 lx_projfunc_bil_exchange_rate NUMBER;
1771
1772
1773 BEGIN
1774
1775 /* ATG Changes */
1776
1777 lx_projfunc_bil_rate_type := px_projfunc_bil_rate_type;
1778 lx_projfunc_bil_rate_date := px_projfunc_bil_rate_date ;
1779 lx_projfunc_bil_exchange_rate := px_projfunc_bil_exchange_rate ;
1780
1781
1782 /* Checking that the passed table of records should have some value */
1783 IF (p_projfunc_cost_tab.count <> 0 ) THEN
1784
1785 l_proj_rev := NVL(p_rev_amt,0);
1786 l_proj_cost := 0;
1787
1788 -- NOTE The following conversion is not going to be used in this dev drop
1789 -- because project value will be in PFC
1790 /* Converting the amount into project functional currency for MCB2
1791 IF ( p_project_currency_code <> p_projfunc_currency_code ) THEN
1792 l_conversion_date := p_projfunc_cost_tab(1).start_date;
1793 PA_MULTI_CURRENCY.convert_amount(
1794 P_FROM_CURRENCY => p_project_currency_code,
1795 P_TO_CURRENCY => p_projfunc_currency_code,
1796 P_CONVERSION_DATE => l_conversion_date,
1797 P_CONVERSION_TYPE => px_projfunc_bil_rate_type,
1798 P_AMOUNT => p_rev_amt,
1799 P_USER_VALIDATE_FLAG => 'Y',
1800 P_HANDLE_EXCEPTION_FLAG => 'Y',
1801 P_CONVERTED_AMOUNT => l_converted_rev_amount,
1802 P_DENOMINATOR => l_denominator,
1803 P_NUMERATOR => l_numerator,
1804 P_RATE => px_projfunc_bil_exchange_rate,
1805 X_STATUS => l_status);
1806
1807 IF (l_status IS NOT NULL) THEN
1808 RAISE l_conversion_fail;
1809 END IF;
1810 l_proj_rev := NVL(l_converted_rev_amount,0);
1811 l_proj_cost := 0;
1812 END IF; */
1813
1814 -- Taking the total cost for the project by summing up all the amount for all periods
1815 -- l_last_num := p_proj_cost_tab.last;
1816 FOR j IN p_projfunc_cost_tab.first..p_projfunc_cost_tab.last LOOP
1817 l_proj_cost := l_proj_cost + NVL(p_projfunc_cost_tab(j).amount,0);
1818 END LOOP;
1819
1820 -- Initializing the local variable
1821 l_period_rev := 0;
1822 l_tot_period_rev := 0;
1823
1824 /* Calculating the revenue period wise */
1825 IF l_proj_cost <> 0 THEN
1826 FOR i IN p_projfunc_cost_tab.first..p_projfunc_cost_tab.last LOOP
1827 IF ( I = p_projfunc_cost_tab.last ) THEN
1828 l_period_rev := NVL(l_proj_rev,0) - NVL(l_tot_period_rev,0);
1829 x_projfunc_revenue_tab(i).amount := NVL(l_period_rev,0);
1830 x_projfunc_revenue_tab(i).period_name := p_projfunc_cost_tab(i).period_name;
1831 ELSE
1832 l_running_cost := NVL(l_running_cost,0) + NVL(p_projfunc_cost_tab(i).amount,0);
1833 l_period_rev := ((NVL(l_running_cost,0)/ NVL(l_proj_cost,0)) * NVL(l_proj_rev,0)) -NVL(l_tot_period_rev,0);
1834 x_projfunc_revenue_tab(i).amount := l_period_rev;
1835 x_projfunc_revenue_tab(i).period_name := p_projfunc_cost_tab(i).period_name;
1836 l_tot_period_rev := l_tot_period_rev + l_period_rev ;
1837 END IF;
1838 END LOOP;
1839 END IF; /* Closing if for l_proj_cost <> */
1840 END IF; /* Closing if for p_proj_cost_tab validation */
1841 EXCEPTION
1842 WHEN l_conversion_fail THEN
1843 x_error_code := l_status||'_BC_PF';
1844 NULL;
1845 WHEN OTHERS THEN
1846 /* ATG Changes */
1847
1848 px_projfunc_bil_rate_type := lx_projfunc_bil_rate_type;
1849 px_projfunc_bil_rate_date := lx_projfunc_bil_rate_date ;
1850 px_projfunc_bil_exchange_rate := lx_projfunc_bil_exchange_rate ;
1851
1852 /* Checking error condition. Added for bug 2218386 */
1853 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1854 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
1855 p_procedure_name => 'calc_cost_based_revenue');
1856 RAISE;
1857 END IF;
1858
1859 END calc_cost_based_revenue;
1860
1861
1862 -- This procedure will return that whta type of the project is this on the basis of passed parameters
1863 -- Input parameters
1864 -- Parameters Type Required Description
1865 -- P_project_id NUMBER NO Project Id
1866 -- p_distribution_rule NUMBER NO distribution rule
1867 -- Out parameters
1868 --
1869
1870 PROCEDURE get_revenue_generation_method( p_project_id IN NUMBER DEFAULT NULL,
1871 p_distribution_rule IN VARCHAR2 DEFAULT NULL,
1872 x_rev_gen_method OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1873 x_error_msg OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1874 IS
1875 l_rule pa_projects_all.distribution_rule%TYPE; -- Used to store the distribution rule for
1876 -- further result e.g if rule starts with work
1877 -- then project is T and M else Fixed price
1878 l_proj_typ VARCHAR2(1); -- Used to store type of the project for return
1879 -- statement i.e. if 'T' - 'T and M' ,'C' - 'Cost based'
1880 -- and if 'E' - ' Event based'
1881
1882 l_class_code pa_project_types_all.project_type_class_code%TYPE;
1883 l_no_rule EXCEPTION;
1884
1885 BEGIN
1886
1887 /*
1888 IF( p_distribution_rule IS NULL) THEN
1889 ELSE
1890 END IF ;
1891 l_rule := p_distribution_rule;
1892 */
1893 /* Selecting distribution rule for checking wheather the project is Fixed Price or T and M */
1894 BEGIN
1895 SELECT proj.distribution_rule,typ.project_type_class_code
1896 INTO l_rule,l_class_code
1897 FROM pa_project_types_all typ, pa_projects_all proj
1898 WHERE proj.project_id = p_project_id
1899 AND proj.project_type = typ.project_type
1900 AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul : removed NVL function
1901
1902 IF ( l_class_code = 'CONTRACT') THEN
1903 IF ( l_rule IS NULL ) THEN
1904 RAISE l_no_rule;
1905 END IF;
1906 END IF;
1907 END;
1908
1909 --DBMS_OUTPUT.PUT_LINE('2');
1910 /* Checking wheather the project type is T and M or Cost based or Event based */
1911 IF ( l_class_code = 'CONTRACT') THEN
1912 IF (l_rule IS NOT NULL ) THEN
1913 IF ( l_rule = 'WORK/WORK') OR ( l_rule = 'WORK/EVENT') THEN
1914 x_rev_gen_method := 'T';
1915 ELSIF (l_rule = 'COST/COST') OR ( l_rule = 'COST/EVENT')OR (l_rule = 'COST/WORK') THEN
1916 x_rev_gen_method := 'C';
1917 ELSIF (l_rule = 'EVENT/EVENT') OR (l_rule = 'EVENT/WORK') THEN
1918 x_rev_gen_method := 'E';
1919 END IF;
1920 END IF;
1921 ELSE
1922 x_rev_gen_method := 'N';
1923 END IF;
1924 EXCEPTION
1925 WHEN l_no_rule THEN
1926 x_rev_gen_method := 'N';
1927 x_error_msg := 'PA_FCST_DIST_RULE_NOT_FOUND';
1928 WHEN OTHERS THEN
1929
1930 /* ATG Changes */
1931 x_rev_gen_method := null;
1932
1933
1934 /* Checking error condition. Added for bug 2218386 */
1935 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1936 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
1937 p_procedure_name => 'get_revenue_generation_method');
1938 RAISE;
1939 END IF;
1940
1941 END get_revenue_generation_method;
1942
1943
1944
1945 -- This procedure will calculate the initial bill rate for Assignment and Requirement
1946 -- Input parameters
1947 -- Parameters Type Required Description
1948 -- p_assignment_type VARCHAR2 YES Type of assignment like REQUIREMENT(R)/ASSIGNMENT(A)
1949 -- p_asgn_start_date DATE YES Rate calculation date
1950 -- P_assignment_id NUMBER YES Unique identifier
1951 -- P_project_id NUMBER YES Project Id
1952 -- P_quantity NUMBER YES Quantity in Hours
1953 -- P_forecast_job_id NUMBER NO Forecast job Id at assignment level
1954 -- P_forecast_job_group_id NUMBER NO Forecast job group id at assignment level
1955 -- p_person_id NUMBER NO Person id
1956 -- p_expenditure_org_id NUMBER NO Expenditure org id
1957 -- P_expenditure_type VARCHAR2 NO Expenditure Type
1958 -- p_expenditure_organization_id NUMBER NO Expenditure organization id
1959 -- p_project_org_id NUMBER NO Project org id
1960 -- p_expenditure_currency_code VARCHAR2 NO Expenditure functional currency code
1961 -- P_project_type VARCHAR2 NO Project Type
1962 -- P_task_id NUMBER NO Task Id for the given project
1963 -- p_projfunc_currency_code VARCHAR2 NO Project Functional currency code
1964 -- P_bill_rate_multiplier NUMBER NO Bill rate multiplier for calculating the revenue
1965 -- P_project_bill_job_group_id NUMBER NO Billing job group id for project
1966 -- p_emp_bill_rate_schedule_id NUMBER NO Employee bill rate schedule id
1967 -- P_job_bill_rate_schedule_id NUMBER NO Job bill rate schedule id
1968 -- and rate
1969 --
1970 -- Out parameters
1971 --
1972 -- x_projfunc_bill_rate NUMBER YES Bill rate in project currency
1973 -- x_projfunc_raw_revenue NUMBER YES Raw revenue in project currency
1974 -- x_rev_currency_code VARCHAR2 YES Revenue currency code
1975
1976 PROCEDURE get_initial_bill_rate(
1977 p_assignment_type IN VARCHAR2 ,
1978 p_asgn_start_date IN DATE ,
1979 p_project_id IN NUMBER ,
1980 p_quantity IN NUMBER ,
1981 p_expenditure_org_id IN NUMBER ,
1982 p_expenditure_type IN VARCHAR2 ,
1983 p_expenditure_organization_id IN NUMBER ,
1984 p_person_id IN NUMBER DEFAULT NULL,
1985 p_assignment_id IN NUMBER DEFAULT NULL,
1986 p_forecast_job_id IN NUMBER DEFAULT NULL,
1987 p_forecast_job_group_id IN NUMBER DEFAULT NULL,
1988 p_project_org_id IN NUMBER DEFAULT NULL,
1989 p_expenditure_currency_code IN VARCHAR2 DEFAULT NULL,
1990 p_project_type IN VARCHAR2 DEFAULT NULL,
1991 p_task_id IN NUMBER DEFAULT NULL,
1992 p_bill_rate_multiplier IN NUMBER DEFAULT NULL,
1993 p_project_bill_job_group_id IN NUMBER DEFAULT NULL,
1994 p_emp_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
1995 p_job_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
1996 p_job_cost_rate_schedule_id IN NUMBER DEFAULT NULL,
1997 p_proj_cost_job_group_id IN NUMBER DEFAULT NULL,
1998 p_calculate_cost_flag IN VARCHAR2 DEFAULT 'Y', /* Added to fix bug 2162965 */
1999 p_forecast_item_id IN NUMBER DEFAULT NULL, /* Added para for bug 2212852 */
2000 p_forecasting_type IN VARCHAR2 DEFAULT 'PROJECT_FORECASTING', /* added para for bug 2212852 */
2001 p_assign_precedes_task IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2002 p_system_linkage IN pa_expenditure_items_all.system_linkage_function%TYPE DEFAULT NULL,/* Added */
2003 /* for Org Forecasting */
2004 p_labor_schdl_discnt IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
2005 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
2006 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2007 p_labor_schedule_fixed_date IN DATE DEFAULT NULL, /* Added for Org Forecasting */
2008 p_labor_sch_type IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2009 p_projfunc_currency_code IN VARCHAR2 DEFAULT NULL,
2010 p_projfunc_rev_rt_dt_code IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2011 p_projfunc_rev_rt_date IN DATE DEFAULT NULL, /* Added for Org Forecasting */
2012 p_projfunc_rev_rt_type IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2013 p_projfunc_rev_exch_rt IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
2014 p_projfunc_cst_rt_date IN DATE DEFAULT NULL, /* Added for Org Forecasting */
2015 p_projfunc_cst_rt_type IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2016 p_project_currency_code IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
2017 p_project_rev_rt_dt_code IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
2018 p_project_rev_rt_date IN DATE DEFAULT NULL, /* Added for org Forecasting */
2019 p_project_rev_rt_type IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
2020 p_project_rev_exch_rt IN NUMBER DEFAULT NULL, /* Added for org Forecasting */
2021 p_project_cst_rt_date IN DATE DEFAULT NULL, /* Added for org Forecasting */
2022 p_project_cst_rt_type IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
2023 x_projfunc_bill_rate OUT NOCOPY NUMBER /* Changed for MCb2 */, --File.Sql.39 bug 4440895
2024 x_projfunc_raw_revenue OUT NOCOPY NUMBER /* Changed for MCb2 */, --File.Sql.39 bug 4440895
2025 x_rev_currency_code OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2026 x_markup_percentage OUT NOCOPY NUMBER /* Added for Assignment Override */, --File.Sql.39 bug 4440895
2027 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2028 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2029 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2030 IS
2031
2032 l_insufficient_parameters EXCEPTION;
2033 l_job_not_found EXCEPTION;
2034 l_no_rule EXCEPTION;
2035
2036 l_calculate_cost_flag VARCHAR2(1); /* Added to fix bug 2162965 */
2037
2038 l_forecast_job_id pa_project_assignments.fcst_job_id%TYPE;
2039 l_forecast_job_group_id pa_project_assignments.fcst_job_group_id%TYPE;
2040
2041 l_labor_cost_mult_name pa_tasks.labor_cost_multiplier_name%TYPE;
2042
2043
2044
2045 l_project_type pa_project_types_all.project_type%TYPE;
2046 l_proj_cost_job_grp_id pa_std_bill_rate_schedules_all.job_group_id%TYPE;
2047
2048 l_project_org_id pa_projects_all.org_id%TYPE;
2049 l_project_bill_job_group_id pa_projects_all.bill_job_group_id%TYPE;
2050 l_emp_bill_rate_schedule_id pa_projects_all.emp_bill_rate_schedule_id%TYPE;
2051 l_job_bill_rate_schedule_id pa_projects_all.job_bill_rate_schedule_id%TYPE;
2052
2053
2054 l_labor_schedule_fixed_date pa_projects_all.labor_schedule_fixed_date%TYPE;
2055 l_labor_schedule_discount NUMBER;
2056 l_labor_bill_rate_org_id NUMBER;
2057 l_labor_std_bill_rate_schedule pa_projects_all.labor_std_bill_rate_schdl%TYPE;
2058 l_labor_schedule_type pa_projects_all.labor_sch_type%TYPE;
2059
2060 l_raw_cost_rate NUMBER;
2061 l_raw_cost NUMBER;
2062
2063 l_x_return_status VARCHAR2(50);
2064 l_proj_bill_job_id NUMBER;
2065 l_overr_to_organization_id NUMBER;
2066 l_job_cost_rate_schedule_id pa_forecasting_options.job_cost_rate_schedule_id%TYPE;
2067 l_distribution_rule pa_projects_all.distribution_rule%TYPE;
2068
2069
2070 l_proj_cost_job_id NUMBER;
2071 l_cost_rate_multiplier NUMBER;
2072 l_class_code pa_project_types_all.project_type_class_code%TYPE;
2073
2074 l_schedule_type VARCHAR2(50);
2075
2076 l_expenditure_currency_code gl_sets_of_books.currency_code%TYPE;
2077 l_expenditure_curr_code_burdn gl_sets_of_books.currency_code%TYPE; /* Added for Org Forecasting */
2078 l_exp_func_cst_rt_date DATE; /* Added for Org Forecasting */
2079 l_exp_func_cst_rt_type PA_IMPLEMENTATIONS_ALL.default_rate_type%TYPE; /* Added for Org Forecasting */
2080 l_exp_func_cst_exch_rt NUMBER; /* Added for Org Forecasting */
2081 l_exp_func_raw_cost_rate NUMBER;
2082 l_exp_func_raw_cost NUMBER;
2083 l_exp_func_burdened_cost_rate NUMBER;
2084 l_exp_func_burdened_cost NUMBER;
2085
2086 /* Added for MCB2 */
2087 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
2088 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
2089 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
2090 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
2091 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
2092 l_projfunc_cost_rate_type pa_projects_all.projfunc_cost_rate_type%TYPE;
2093 l_projfunc_cost_rate_date pa_projects_all.projfunc_cost_rate_DATE%TYPE;
2094 l_projfunc_cost_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
2095 l_markup_percentage pa_bill_rates_all.markup_percentage%TYPE; /* Added for Asgmt overide */
2096 l_assignment_precedes_task pa_projects_all.assign_precedes_task%TYPE; /* Added for Asgmt overide */
2097 /* Till here for mcb 2 */
2098
2099 /* Added for Org Foreasting */
2100 l_projfunc_bill_rate NUMBER;
2101 l_projfunc_raw_revenue NUMBER;
2102 l_projfunc_raw_cost NUMBER;
2103 l_projfunc_raw_cost_rate NUMBER;
2104 l_projfunc_burdened_cost NUMBER;
2105 l_projfunc_burdened_cost_rate NUMBER;
2106
2107 l_amount_calc_mode VARCHAR2(50);
2108
2109 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2110 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
2111 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
2112 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
2113 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
2114 l_project_cost_rate_type pa_projects_all.project_rate_type%TYPE;
2115 l_project_cost_rate_date pa_projects_all.project_rate_DATE%TYPE;
2116 l_project_cost_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
2117 l_project_bill_rate NUMBER;
2118 l_project_raw_revenue NUMBER;
2119 l_project_raw_cost NUMBER;
2120 l_project_raw_cost_rate NUMBER;
2121 l_project_burdened_cost NUMBER;
2122 l_project_burdened_cost_rate NUMBER;
2123
2124 l_cst_txn_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
2125 l_txn_raw_cst_rt NUMBER;
2126 l_txn_raw_cst NUMBER;
2127 l_txn_burdned_cst_rt NUMBER;
2128 l_txn_burdned_cst NUMBER;
2129
2130 l_rev_txn_curr_code PA_BILL_RATES_ALL.rate_currency_code%TYPE;
2131 l_txn_rev_bill_rt NUMBER;
2132
2133 l_txn_adjusted_bill_rt NUMBER;-- 4038485
2134 l_txn_rev_raw_revenue NUMBER;
2135
2136 l_system_linkage pa_expenditure_items_all.system_linkage_function%TYPE;
2137 /* Till here for Org */
2138
2139
2140 BEGIN
2141
2142 IF g1_debug_mode = 'Y' THEN
2143 PA_DEBUG.Set_Curr_Function( p_function => 'Get Initial Bill Rate');
2144 PA_DEBUG.g_err_stage := 'RT50 : Before Validation PA_RATE_PVT_PKG.get_initial_bill_rate';
2145 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2146 END IF;
2147
2148 /* Validating that the required parameters should not be null */
2149 IF ( p_assignment_type IS NULL) OR (p_asgn_start_date IS NULL ) OR (p_project_id IS NULL)
2150 OR (p_quantity IS NULL) OR (p_expenditure_type IS NULL )
2151 /* OR (p_expenditure_organization_id IS NULL) OR (p_expenditure_org_id IS NULL) commented because
2152 null condition is taken care in lower api */
2153 THEN
2154 RAISE l_insufficient_parameters;
2155 END IF;
2156
2157
2158 /* Validating that the required parameters should not be null */
2159 IF ( p_assignment_type = 'A') THEN
2160 IF (p_person_id IS NULL) THEN
2161 RAISE l_insufficient_parameters;
2162 END IF;
2163 END IF;
2164
2165 /* Selecting expenditure org id , type ,organization id , forecast job id and forecast job group
2166 id from project assignments table only if the passed value is null otherwise storing passed
2167 values */
2168 IF (p_assignment_type = 'R') THEN
2169 IF (p_forecast_job_id IS NULL) OR (p_forecast_job_group_id IS NULL) THEN
2170 RAISE l_job_not_found;
2171 ELSE
2172 l_forecast_job_id := p_forecast_job_id;
2173 l_forecast_job_group_id := p_forecast_job_group_id;
2174 END IF;
2175 ELSIF ( p_assignment_type = 'A') THEN
2176 SELECT job_id
2177 INTO l_forecast_job_id
2178 FROM pa_resources_denorm
2179 WHERE person_id = p_person_id
2180 AND ( p_asgn_start_date BETWEEN TRUNC(resource_effective_start_date) AND
2181 NVL(TRUNC(resource_effective_end_date),p_asgn_start_date));
2182
2183 END IF;
2184
2185 /* Selecting distribution rule for calculation rate */
2186 BEGIN
2187 SELECT proj.distribution_rule,typ.project_type_class_code,proj.labor_sch_type
2188 INTO l_distribution_rule,l_class_code,l_labor_schedule_type
2189 FROM pa_project_types_all typ, pa_projects_all proj
2190 WHERE proj.project_id = p_project_id
2191 AND proj.project_type = typ.project_type
2192 AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul : removed NVL function
2193
2194 IF ( l_class_code = 'CONTRACT') THEN
2195 IF ( l_distribution_rule IS NULL) THEN
2196 RAISE l_no_rule;
2197 END IF;
2198 END IF;
2199 EXCEPTION
2200 WHEN l_no_rule THEN
2201 NULL;
2202 WHEN NO_DATA_FOUND THEN
2203 NULL;
2204 END;
2205
2206 /* Selecting expenditure currency code from project set of books and implementations table
2207 only if the passed value is null otherwise storing passed values */
2208 IF ( p_expenditure_currency_code IS NULL) THEN
2209 BEGIN
2210 SELECT glsb.currency_code
2211 INTO l_expenditure_currency_code
2212 FROM gl_sets_of_books glsb, pa_implementations_all paimp
2213 WHERE glsb.set_of_books_id = paimp.set_of_books_id
2214 AND paimp.org_id = p_expenditure_org_id; -- bug 7413961 skkoppul: removed NVL function
2215 END;
2216 ELSE
2217 l_expenditure_currency_code := p_expenditure_currency_code;
2218 END IF;
2219
2220 l_expenditure_curr_code_burdn := l_expenditure_currency_code; /* Made for Org Forecasting */
2221
2222 /* Selecting labor cost mult name from tasks table only if the passed value is null and task id
2223 is not null otherwise storing passed values */
2224 IF ( p_task_id IS NOT NULL ) THEN
2225 BEGIN
2226 SELECT labor_cost_multiplier_name
2227 INTO l_labor_cost_mult_name
2228 FROM pa_tasks
2229 WHERE task_id = p_task_id;
2230 END;
2231 END IF;
2232
2233 /* Selecting project type from project types table only if the
2234 passed value is null otherwise storing passed values */
2235 IF ( p_project_type IS NULL) THEN
2236
2237 SELECT typ.project_type
2238 INTO l_project_type
2239 FROM pa_project_types_all typ, pa_projects_all proj
2240 WHERE proj.project_id = p_project_id
2241 AND proj.project_type = typ.project_type
2242 AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul: removed NVL function
2243
2244 ELSE
2245 l_project_type := p_project_type;
2246 END IF;
2247
2248
2249 /* Selecting project org id, project currency code, project bill job
2250 group id, employee bill rate schedule id and job bill rate schedule id from project all table
2251 only if the passed value is null otherwise storing passed values */
2252 IF (p_project_org_id IS NULL) OR (p_project_bill_job_group_id IS NULL) OR
2253 (p_emp_bill_rate_schedule_id IS NULL) OR (p_job_bill_rate_schedule_id IS NULL)
2254 OR (p_labor_schedule_fixed_date IS NULL) THEN
2255 BEGIN
2256 SELECT NVL(org_id,-99), bill_job_group_id,
2257 emp_bill_rate_schedule_id,job_bill_rate_schedule_id,
2258 labor_schedule_fixed_date,
2259 projfunc_currency_code,
2260 projfunc_bil_rate_date_code, /* Added the following column for MCB2 */
2261 projfunc_bil_rate_type,
2262 projfunc_bil_rate_date,
2263 projfunc_bil_exchange_rate,
2264 projfunc_cost_rate_date,
2265 projfunc_cost_rate_type,
2266 NVL(assign_precedes_task,'1'),/* Added for Asgmt overide */
2267 project_currency_code, /* Added for Org Forecasting */
2268 project_bil_rate_date_code, /* Added for Org Forecasting */
2269 project_bil_rate_type, /* Added for Org Forecasting */
2270 project_bil_rate_date, /* Added for Org Forecasting */
2271 project_bil_exchange_rate, /* Added for Org Forecasting */
2272 project_rate_date, /* Added for Org Forecasting */
2273 project_rate_type, /* Added for Org Forecasting */
2274 labor_schedule_discount, /* Added for Org Forecasting */
2275 labor_bill_rate_org_id, /* Added for Org Forecasting */
2276 labor_std_bill_rate_schdl, /* Added for Org Forecasting */
2277 labor_schedule_fixed_date, /* Added for Org Forecasting */
2278 labor_sch_type /* Added for Org Forecasting */
2279 INTO l_project_org_id,l_project_bill_job_group_id,
2280 l_emp_bill_rate_schedule_id,l_job_bill_rate_schedule_id ,
2281 l_labor_schedule_fixed_date,
2282 l_projfunc_currency_code,
2283 l_projfunc_bil_rate_date_code, /* Added the following columns for MCB2 */
2284 l_projfunc_bil_rate_type,
2285 l_projfunc_bil_rate_date,
2286 l_projfunc_bil_exchange_rate,
2287 l_projfunc_cost_rate_date,
2288 l_projfunc_cost_rate_type,
2289 l_assignment_precedes_task,
2290 l_project_currency_code,
2291 l_project_bil_rate_date_code,
2292 l_project_bil_rate_type,
2293 l_project_bil_rate_date,
2294 l_project_bil_exchange_rate,
2295 l_project_cost_rate_date,
2296 l_project_cost_rate_type,
2297 l_labor_schedule_discount,
2298 l_labor_bill_rate_org_id,
2299 l_labor_std_bill_rate_schedule,
2300 l_labor_schedule_fixed_date,
2301 l_labor_schedule_type
2302 FROM pa_projects_all
2303 WHERE project_id = p_project_id;
2304 END;
2305 ELSE
2306 IF p_project_org_id IS NOT NULL THEN
2307 l_project_org_id := p_project_org_id;
2308 END IF;
2309 IF p_project_bill_job_group_id IS NOT NULL THEN
2310 l_project_bill_job_group_id := p_project_bill_job_group_id;
2311 END IF;
2312 IF p_emp_bill_rate_schedule_id IS NOT NULL THEN
2313 l_emp_bill_rate_schedule_id := p_emp_bill_rate_schedule_id;
2314 END IF;
2315 IF p_job_bill_rate_schedule_id IS NOT NULL THEN
2316 l_job_bill_rate_schedule_id := p_job_bill_rate_schedule_id;
2317 END IF;
2318
2319 /* Added for Org Forecasting */
2320 IF p_labor_schdl_discnt IS NOT NULL THEN
2321 l_labor_schedule_discount := p_labor_schdl_discnt;
2322 END IF;
2323
2324 IF p_labor_bill_rate_org_id IS NOT NULL THEN
2325 l_labor_bill_rate_org_id := p_labor_bill_rate_org_id;
2326 END IF;
2327
2328 IF p_labor_std_bill_rate_schdl IS NOT NULL THEN
2329 l_labor_std_bill_rate_schedule := p_labor_std_bill_rate_schdl;
2330 END IF;
2331
2332 IF p_labor_schedule_fixed_date IS NOT NULL THEN
2333 l_labor_schedule_fixed_date := p_labor_schedule_fixed_date;
2334 END IF;
2335
2336 IF p_labor_sch_type IS NOT NULL THEN
2337 l_labor_schedule_type := p_labor_sch_type;
2338 END IF;
2339
2340 IF p_projfunc_rev_rt_date IS NOT NULL THEN
2341 l_projfunc_bil_rate_date := p_projfunc_rev_rt_date;
2342 END IF;
2343
2344 IF p_projfunc_rev_rt_type IS NOT NULL THEN
2345 l_projfunc_bil_rate_type := p_projfunc_rev_rt_type;
2346 END IF;
2347
2348 IF p_projfunc_rev_exch_rt IS NOT NULL THEN
2349 l_projfunc_bil_exchange_rate := p_projfunc_rev_exch_rt;
2350 END IF;
2351
2352 IF p_projfunc_cst_rt_date IS NOT NULL THEN
2353 l_projfunc_cost_rate_date := p_projfunc_cst_rt_date;
2354 END IF;
2355
2356 IF p_projfunc_cst_rt_type IS NOT NULL THEN
2357 l_projfunc_cost_rate_type := p_projfunc_cst_rt_type;
2358 END IF;
2359
2360 IF p_project_currency_code IS NOT NULL THEN
2361 l_project_currency_code := p_project_currency_code;
2362 END IF;
2363
2364 IF p_project_rev_rt_date IS NOT NULL THEN
2365 l_project_bil_rate_date := p_project_rev_rt_date;
2366 END IF;
2367
2368 IF p_project_rev_rt_type IS NOT NULL THEN
2369 l_project_bil_rate_type := p_project_rev_rt_type;
2370 END IF;
2371
2372 IF p_project_rev_exch_rt IS NOT NULL THEN
2373 l_project_bil_exchange_rate := p_project_rev_exch_rt;
2374 END IF;
2375
2376 IF p_project_cst_rt_date IS NOT NULL THEN
2377 l_project_cost_rate_date := p_project_cst_rt_date;
2378 END IF;
2379
2380 IF p_project_cst_rt_type IS NOT NULL THEN
2381 l_project_cost_rate_type := p_project_cst_rt_type;
2382 END IF;
2383
2384
2385 END IF;
2386
2387 IF (p_system_linkage IS NULL ) THEN
2388 /* Added for Org_forecasting */
2389 SELECT default_assign_exp_type_class
2390 INTO l_system_linkage
2391 FROM pa_forecasting_options_all
2392 WHERE NVL(org_id,-99) = nvl(l_project_org_id,-99);
2393 ELSE
2394 l_system_linkage := p_system_linkage;
2395 END IF;
2396
2397 /* Selecting project cost job group id,job cost rate schedule id from forecasting options and
2398 pa std billrate table only if the passed value is null otherwise storing passed values */
2399
2400 IF ( p_proj_cost_job_group_id IS NULL) OR ( p_job_cost_rate_schedule_id IS NULL) THEN
2401 SELECT bschal.job_group_id,foptal.job_cost_rate_schedule_id
2402 INTO l_proj_cost_job_grp_id,l_job_cost_rate_schedule_id
2403 FROM pa_std_bill_rate_schedules_all bschal,pa_forecasting_options_all foptal
2404 WHERE bschal.bill_rate_sch_id = foptal.job_cost_rate_schedule_id
2405 /* For bug 4101595: Reverted the fix done for bug 3786192 */
2406 /* AND nvl(foptal.org_id, -99) = nvl(p_expenditure_org_id, -99) */ /* Added for 3786192 */
2407 AND bschal.org_id = l_project_org_id; -- bug 7413961 skkoppul: removed NVL function
2408
2409 ELSE
2410 l_proj_cost_job_grp_id := p_proj_cost_job_group_id;
2411 l_job_cost_rate_schedule_id := p_job_cost_rate_schedule_id;
2412 END IF;
2413
2414 /* commented for Org Forecasting
2415 IF (l_labor_schedule_fixed_date IS NULL) OR (l_projfunc_currency_code IS NULL) THEN
2416 BEGIN
2417 SELECT labor_schedule_fixed_date,
2418 projfunc_currency_code,projfunc_bil_rate_date_code, -- Added the following column for MCB2
2419 projfunc_bil_rate_type,projfunc_bil_rate_date,projfunc_bil_exchange_rate,
2420 projfunc_cost_rate_date,projfunc_cost_rate_type,
2421 NVL(assign_precedes_task,'1'), -- Added for Asgmt overide
2422 labor_sch_type
2423 INTO l_labor_schedule_fixed_date,
2424 l_projfunc_currency_code,l_projfunc_bil_rate_date_code, -- Added the following columns for MCB2
2425 l_projfunc_bil_rate_type,l_projfunc_bil_rate_date,l_projfunc_bil_exchange_rate,
2426 l_projfunc_cost_rate_date,l_projfunc_cost_rate_type,
2427 l_assignment_precedes_task,
2428 l_labor_schedule_type
2429 FROM pa_projects_all
2430 WHERE project_id = p_project_id;
2431 END;
2432 END IF;
2433 */
2434
2435 /* Added to fix bug 2162965, if the assignment api is calling this api then, we do not need to
2436 calculate the cost rate, else we do */
2437 IF (l_labor_schedule_type = 'I') THEN
2438 l_calculate_cost_flag := 'Y';
2439 ELSE
2440 l_calculate_cost_flag := p_calculate_cost_flag;
2441 END IF;
2442
2443 IF g1_debug_mode = 'Y' THEN
2444 PA_DEBUG.g_err_stage := 'RT51 : After Validation PA_RATE_PVT_PKG.get_initial_bill_rate';
2445 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2446 END IF;
2447 /* Calling the rate calculation APIs */
2448
2449 --------------------------------------------
2450 -- Initialize the successful return status
2451 --------------------------------------------
2452
2453 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2454 l_Schedule_type := 'COST';
2455
2456 IF (l_calculate_cost_flag = 'Y') THEN /* Added this if to fix bug 2162965 */
2457 IF ( p_assignment_type = 'A') THEN
2458 IF g1_debug_mode = 'Y' THEN
2459 PA_DEBUG.g_err_stage := 'RT52 : Entering PA_COST.get_raw_cost';
2460 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2461 END IF;
2462
2463 PA_COST.get_raw_cost ( P_person_id => p_person_id ,
2464 P_expenditure_org_id => p_expenditure_org_id ,
2465 P_expend_organization_id => p_expenditure_organization_id, /*LCE*/
2466 P_labor_Cost_Mult_Name => l_labor_cost_mult_name ,
2467 P_Item_date => p_asgn_start_date ,
2468 px_exp_func_curr_code => l_expenditure_curr_code_burdn,
2469 P_Quantity => p_quantity ,
2470 X_Raw_cost_rate => l_raw_cost_rate ,
2471 X_Raw_cost => l_raw_cost ,
2472 x_return_status => l_x_return_status ,
2473 x_msg_count => x_msg_count ,
2474 x_msg_data => x_msg_data
2475 );
2476
2477 IF g1_debug_mode = 'Y' THEN
2478 PA_DEBUG.g_err_stage := 'RT53 : Leaving PA_COST.get_raw_cost';
2479 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2480 END IF;
2481
2482 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2483 IF g1_debug_mode = 'Y' THEN
2484 PA_DEBUG.g_err_stage := 'RT54 : Entering PA_COST.override_exp_organization';
2485 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2486 END IF;
2487
2488 PA_COST.override_exp_organization(P_item_date => p_asgn_start_date ,
2489 P_person_id => p_person_id ,
2490 P_project_id => p_project_id ,
2491 P_incurred_by_organz_id => p_expenditure_organization_id ,
2492 P_Expenditure_type => p_expenditure_type ,
2493 X_overr_to_organization_id => l_overr_to_organization_id ,
2494 x_return_status => l_x_return_status ,
2495 x_msg_count => x_msg_count ,
2496 x_msg_data => x_msg_data
2497 );
2498
2499 IF g1_debug_mode = 'Y' THEN
2500 PA_DEBUG.g_err_stage := 'RT55 : Leaving PA_COST.override_exp_organization';
2501 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2502 END IF;
2503
2504 END IF;
2505
2506 ELSIF (p_assignment_type = 'R') THEN
2507 IF g1_debug_mode = 'Y' THEN
2508 PA_DEBUG.g_err_stage := 'RT52 : Entering PA_COST.requirement_raw_cost';
2509 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2510 END IF;
2511 /* Four project functional attributes added for MCB2 */
2512 PA_COST.requirement_raw_cost( p_forecast_cost_job_group_id => l_forecast_job_group_id ,
2513 p_forecast_cost_job_id => l_forecast_job_id ,
2514 p_proj_cost_job_group_id => l_proj_cost_job_grp_id ,
2515 px_proj_cost_job_id => l_proj_cost_job_id ,
2516 p_item_date => p_asgn_start_date ,
2517 p_job_cost_rate_sch_id => l_job_cost_rate_schedule_id ,
2518 p_schedule_date => l_labor_schedule_fixed_date ,
2519 p_quantity => p_quantity ,
2520 p_cost_rate_multiplier => l_cost_rate_multiplier ,
2521 p_org_id => l_project_org_id ,
2522 p_expend_organization_id => p_expenditure_organization_id , /*LCE*/
2523 x_raw_cost_rate => l_raw_cost_rate ,
2524 x_raw_cost => l_raw_cost ,
2525 x_txn_currency_code => l_expenditure_curr_code_burdn,
2526 x_return_status => l_x_return_status ,
2527 x_msg_count => x_msg_count ,
2528 x_msg_data => x_msg_data
2529 );
2530 END IF;
2531
2532 l_expenditure_currency_code := NVL(l_expenditure_curr_code_burdn,l_expenditure_currency_code); /* added for Org Fcst */
2533
2534 IF (NVL(l_raw_cost,0) <> 0 ) THEN
2535
2536 PA_COST.get_burdened_cost(p_project_type => l_project_type ,
2537 p_project_id => p_project_id ,
2538 p_task_id => p_task_id ,
2539 p_item_date => p_asgn_start_date ,
2540 p_expenditure_type => p_expenditure_type ,
2541 p_schedule_type => l_schedule_type ,
2542 px_exp_func_curr_code => l_expenditure_currency_code ,
2543 p_Incurred_by_organz_id => p_expenditure_organization_id ,
2544 p_raw_cost => l_raw_cost ,
2545 p_raw_cost_rate => l_raw_cost_rate ,
2546 p_quantity => p_quantity ,
2547 p_override_to_organz_id => l_overr_to_organization_id ,
2548 x_burden_cost => l_exp_func_burdened_cost ,
2549 x_burden_cost_rate => l_exp_func_burdened_cost_rate ,
2550 x_return_status => l_x_return_status ,
2551 x_msg_count => x_msg_count ,
2552 x_msg_data => x_msg_data
2553 );
2554
2555 PA_COST.Get_Converted_Cost_Amounts(
2556 P_exp_org_id => p_expenditure_org_id,
2557 P_proj_org_id => l_project_org_id,
2558 P_project_id => p_project_id,
2559 P_task_id => p_task_id,
2560 P_item_date => p_asgn_start_date,
2561 p_system_linkage => l_system_linkage,
2562 px_txn_curr_code => l_cst_txn_curr_code,
2563 px_raw_cost => l_raw_cost,
2564 px_raw_cost_rate => l_raw_cost_rate,
2565 px_burden_cost => l_exp_func_burdened_cost,
2566 px_burden_cost_rate => l_exp_func_burdened_cost_rate,
2567 px_exp_func_curr_code => l_expenditure_currency_code,
2568 px_exp_func_rate_date => l_exp_func_cst_rt_date,
2569 px_exp_func_rate_type => l_exp_func_cst_rt_type,
2570 px_exp_func_exch_rate => l_exp_func_cst_exch_rt,
2571 px_exp_func_cost => l_exp_func_raw_cost,
2572 px_exp_func_cost_rate => l_exp_func_raw_cost_rate,
2573 px_exp_func_burden_cost => l_exp_func_burdened_cost,
2574 px_exp_func_burden_cost_rate => l_exp_func_burdened_cost_rate,
2575 px_proj_func_curr_code => l_projfunc_currency_code,
2576 px_projfunc_cost_rate_date => l_projfunc_cost_rate_date,
2577 px_projfunc_cost_rate_type => l_projfunc_cost_rate_type,
2578 px_projfunc_cost_exch_rate => l_projfunc_cost_exchange_rate,
2579 px_projfunc_raw_cost => l_projfunc_raw_cost ,
2580 px_projfunc_raw_cost_rate => l_projfunc_raw_cost_rate ,
2581 px_projfunc_burden_cost => l_projfunc_burdened_cost ,
2582 px_projfunc_burden_cost_rate => l_projfunc_burdened_cost_rate ,
2583 px_project_curr_code => l_project_currency_code,
2584 px_project_rate_date => l_project_cost_rate_date,
2585 px_project_rate_type => l_project_cost_rate_type,
2586 px_project_exch_rate => l_project_cost_exchange_rate,
2587 px_project_cost => l_project_raw_cost,
2588 px_project_cost_rate => l_project_raw_cost_rate,
2589 px_project_burden_cost => l_project_burdened_cost,
2590 px_project_burden_cost_rate => l_project_burdened_cost_rate,
2591 x_return_status => l_x_return_status ,
2592 x_msg_count => x_msg_count ,
2593 x_msg_data => x_msg_data
2594 );
2595
2596 END IF;
2597
2598 /* Deleted this proc PA_COST.get_projfunc_raw_burdened_cost() for Org Forecasting */
2599
2600 END IF; /* end of calculate cost flag */
2601
2602 IF (SUBSTR(l_distribution_rule,1,4) = 'WORK' AND l_class_code = 'CONTRACT') THEN
2603 IF ( p_assignment_type = 'A') THEN
2604 IF g1_debug_mode = 'Y' THEN
2605 PA_DEBUG.g_err_stage := 'RT56 : Entering PA_REVENUE.get_rev_amt';
2606 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2607 END IF;
2608
2609 PA_REVENUE.Assignment_Rev_Amt(
2610 p_project_id => p_project_id ,
2611 p_task_id => p_task_id ,
2612 p_bill_rate_multiplier => p_bill_rate_multiplier ,
2613 p_quantity => p_quantity ,
2614 p_person_id => p_person_id ,
2615 p_raw_cost => l_projfunc_raw_cost ,
2616 p_item_date => p_asgn_start_date ,
2617 p_labor_schdl_discnt => l_labor_schedule_discount ,
2618 p_labor_bill_rate_org_id => l_labor_bill_rate_org_id ,
2619 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schedule ,
2620 p_labor_schdl_fixed_date => l_labor_schedule_fixed_date ,
2621 p_bill_job_grp_id => l_project_bill_job_group_id ,
2622 p_item_id => p_assignment_id , /* changed for bug 2212852 */
2623 p_forecast_item_id => p_forecast_item_id, /* added for bug 2212852 */
2624 p_forecasting_type => p_forecasting_type , /* added for bug 2212852 */
2625 p_labor_sch_type => l_labor_schedule_type ,
2626 p_project_org_id => l_project_org_id ,
2627 p_project_type => l_project_type ,
2628 p_expenditure_type => p_expenditure_type ,
2629 p_exp_func_curr_code => l_expenditure_currency_code ,
2630 p_incurred_by_organz_id => p_expenditure_organization_id ,
2631 p_raw_cost_rate => l_raw_cost_rate ,
2632 p_override_to_organz_id => l_overr_to_organization_id ,
2633 p_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id ,
2634 p_resource_job_id => l_forecast_job_id ,
2635 p_exp_raw_cost => l_raw_cost ,
2636 p_expenditure_org_id => p_expenditure_org_id ,
2637 p_projfunc_currency_code => l_projfunc_currency_code , -- The following 5
2638 p_assignment_precedes_task => l_assignment_precedes_task , /* Added for Asgmt overide */
2639 p_sys_linkage_function => l_system_linkage, /* Added for Org FCST */
2640 x_bill_rate => l_txn_rev_bill_rt,/* Change for Org Forecsting */
2641 x_raw_revenue => l_txn_rev_raw_revenue ,
2642 x_markup_percentage => l_markup_percentage,/* Added for Asgmt overide */
2643 x_txn_currency_code => l_rev_txn_curr_code, /* added for Org */
2644 x_rev_currency_code => l_projfunc_currency_code ,
2645 x_return_status => l_x_return_status ,
2646 x_msg_count => x_msg_count ,
2647 x_msg_data => x_msg_data ,
2648 /* Added for bug 2668753 */
2649 p_project_raw_cost => l_project_raw_cost ,
2650 p_project_currency_code => l_project_currency_code ,
2651 x_adjusted_bill_rate => l_txn_adjusted_bill_rt
2652 );
2653
2654 -- dbms_output.put_line(' Get Ini rev : '||l_txn_rev_raw_revenue||' curr : '||l_rev_txn_curr_code);
2655 IF g1_debug_mode = 'Y' THEN
2656 PA_DEBUG.g_err_stage := 'RT57 : Leaving PA_REVENUE.get_rev_amt';
2657 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2658 END IF;
2659 /* IF ( x_projfunc_raw_revenue IS NULL OR x_projfunc_raw_revenue = 0 ) THEN
2660 x_projfunc_bill_rate := 0;
2661 x_projfunc_raw_revenue := 0;
2662 END IF; Commented for Org Forecasting */
2663
2664
2665 ELSIF (p_assignment_type = 'R') THEN
2666 IF g1_debug_mode = 'Y' THEN
2667 PA_DEBUG.g_err_stage := 'RT53 : Leaving PA_COST.requirement_raw_cost';
2668 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2669 END IF;
2670
2671 PA_REVENUE.requirement_rev_amt(
2672 p_project_id => p_project_id ,
2673 p_task_id => p_task_id ,
2674 p_bill_rate_multiplier => p_bill_rate_multiplier ,
2675 p_quantity => p_quantity ,
2676 p_raw_cost => l_projfunc_raw_cost ,
2677 p_item_date => p_asgn_start_date ,
2678 p_project_bill_job_grp_id => l_project_bill_job_group_id ,
2679 p_labor_schdl_discnt => l_labor_schedule_discount ,
2680 p_labor_bill_rate_org_id => l_labor_bill_rate_org_id ,
2681 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schedule ,
2682 p_labor_schdl_fixed_date => l_labor_schedule_fixed_date ,
2683 p_forecast_job_id => l_forecast_job_id ,
2684 p_forecast_job_grp_id => l_forecast_job_group_id ,
2685 p_labor_sch_type => l_labor_schedule_type ,
2686 p_item_id => p_assignment_id , /* changed for bug 2212852 */
2687 p_forecast_item_id => p_forecast_item_id, /* added for bug 2212852 */
2688 p_forecasting_type => p_forecasting_type , /* added for bug 2212852 */
2689 p_project_org_id => l_project_org_id ,
2690 p_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id ,
2691 p_project_type => l_project_type ,
2692 p_expenditure_type => p_expenditure_type ,
2693 px_exp_func_curr_code => l_expenditure_currency_code ,
2694 p_incurred_by_organz_id => p_expenditure_organization_id ,
2695 p_raw_cost_rate => l_raw_cost_rate ,
2696 p_override_to_organz_id => l_overr_to_organization_id ,
2697 p_exp_raw_cost => l_raw_cost ,
2698 p_expenditure_org_id => p_expenditure_org_id ,
2699 p_projfunc_currency_code => l_projfunc_currency_code , -- The following 5
2700 p_assignment_precedes_task => l_assignment_precedes_task , /* Added for Asgmt overide */
2701 p_sys_linkage_function => l_system_linkage, /* Added for Org FCST */
2702 px_project_bill_job_id => l_proj_bill_job_id ,
2703 x_bill_rate => l_txn_rev_bill_rt,/*Change for Org Forecsting */
2704 x_raw_revenue => l_txn_rev_raw_revenue ,
2705 x_markup_percentage => l_markup_percentage,/* Added for Asgmt overide */
2706 x_txn_currency_code => l_rev_txn_curr_code, /* added for Org */
2707 x_return_status => l_x_return_status ,
2708 x_msg_count => x_msg_count ,
2709 x_msg_data => x_msg_data
2710 );
2711
2712 IF g1_debug_mode = 'Y' THEN
2713 PA_DEBUG.g_err_stage := 'RT55 : Leaving PA_REVENUE.requirement_rev_amt';
2714 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2715 END IF;
2716
2717 END IF; /* End of assignment_type if */
2718
2719 IF ( (NVL(l_txn_rev_raw_revenue,0) <> 0) ) THEN
2720
2721 -- dbms_output.put_line(' Get Ini rev 0.1 : '||l_txn_rev_raw_revenue||' curr : '||l_rev_txn_curr_code);
2722 PA_REVENUE.Get_Converted_Revenue_Amounts(
2723 p_item_date => p_asgn_start_date,
2724 px_txn_curr_code => l_rev_txn_curr_code,
2725 px_txn_raw_revenue => l_txn_rev_raw_revenue,
2726 px_txn_bill_rate => l_txn_rev_bill_rt,
2727 px_projfunc_curr_code => l_projfunc_currency_code,
2728 p_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
2729 px_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
2730 px_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
2731 px_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
2732 px_projfunc_raw_revenue => l_projfunc_raw_revenue ,
2733 px_projfunc_bill_rate => l_projfunc_bill_rate ,
2734 px_project_curr_code => l_project_currency_code,
2735 p_project_bil_rate_date_code => l_project_bil_rate_date_code,
2736 px_project_bil_rate_type => l_project_bil_rate_type,
2737 px_project_bil_rate_date => l_project_bil_rate_date,
2738 px_project_bil_exchange_rate => l_project_bil_exchange_rate,
2739 px_project_raw_revenue => l_project_raw_revenue ,
2740 px_project_bill_rate => l_project_bill_rate ,
2741 x_return_status => l_x_return_status ,
2742 x_msg_count => x_msg_count ,
2743 x_msg_data => x_msg_data
2744 );
2745
2746 -- dbms_output.put_line(' Get Ini rev 1 : '||l_txn_rev_raw_revenue||' curr : '||l_rev_txn_curr_code);
2747 x_projfunc_bill_rate := l_projfunc_bill_rate;
2748 x_projfunc_raw_revenue := l_projfunc_raw_revenue;
2749 END IF;
2750
2751 IF ( x_projfunc_raw_revenue IS NULL OR x_projfunc_raw_revenue = 0 ) THEN
2752 x_projfunc_bill_rate := 0;
2753 x_projfunc_raw_revenue := 0;
2754 END IF;
2755 END IF; /* End of class code and rule if (for R and A) */
2756
2757 -------------------------------------------------------
2758 -- Assign the successful status back to output variable
2759 -------------------------------------------------------
2760
2761 x_return_status := l_x_return_status;
2762
2763 x_rev_currency_code := l_projfunc_currency_code;
2764 x_markup_percentage := l_markup_percentage; /* Added for Asgmt overide */
2765
2766 IF g1_debug_mode = 'Y' THEN
2767 PA_DEBUG.g_err_stage := 'RT58 : Leaving PA_RATE_PVT_PKG.get_initial_bill_rate';
2768 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2769 PA_DEBUG.Reset_Curr_Function;
2770 END IF;
2771
2772 EXCEPTION
2773 WHEN l_insufficient_parameters THEN
2774 IF g1_debug_mode = 'Y' THEN
2775 PA_DEBUG.Reset_Curr_Function;
2776 END IF;
2777 x_return_status := FND_API.G_RET_STS_ERROR;
2778 x_msg_count := 1;
2779 x_msg_data := 'PA_FCST_INSUFFICIENT_PARA';
2780 WHEN l_job_not_found THEN
2781 IF g1_debug_mode = 'Y' THEN
2782 PA_DEBUG.Reset_Curr_Function;
2783 END IF;
2784 x_return_status := FND_API.G_RET_STS_ERROR;
2785 x_msg_count := 1;
2786 x_msg_data := 'PA_FCST_NO_JOB_FOUND';
2787 WHEN OTHERS THEN
2788 IF g1_debug_mode = 'Y' THEN
2789 PA_DEBUG.Reset_Curr_Function;
2790 END IF;
2791
2792 /* ATG Changes */
2793
2794 x_projfunc_bill_rate := null;
2795 x_projfunc_raw_revenue := null;
2796 x_rev_currency_code := null;
2797 x_markup_percentage := null;
2798
2799
2800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2801 x_msg_data := SUBSTR(SQLERRM,1,30);
2802 /* Checking error condition. Added for bug 2218386 */
2803 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2804 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG', /* Moved this here to fix bug 2434663 */
2805 p_procedure_name => 'get_initial_bill_rate');
2806 RAISE;
2807 END IF;
2808
2809 END get_initial_bill_rate;
2810
2811
2812 -- This procedure contains consolidated procedure and function to calculate the raw cost,
2813 -- burdened cost and raw revenue on the basis of passed parameters on array basis
2814 -- Input parameters
2815 -- Parameters Type Required Description
2816 -- p_calling_mode VARCHAR2 YES Calling mode values are ACTUAL/ROLE/ASSIGNMENT
2817 -- P_item_id NUMBER YES Unique identifier
2818 -- P_project_id NUMBER YES Project Id
2819 -- P_forecast_job_id NUMBER NO Forecast job Id at assignment level
2820 -- P_forecast_job_group_id NUMBER NO Forecast job group id at assignment level
2821 -- p_person_id NUMBER NO Person id
2822 -- P_expenditure_type VARCHAR2 NO Expenditure Type
2823 -- p_expenditure_organization_id NUMBER NO Expenditure organization id
2824 -- p_project_org_id NUMBER NO Project org id
2825 -- p_labor_cost_multi_name VARCHAR2 NO Labor cost multiplier name for calculating the cost
2826 -- p_expenditure_currency_code VARCHAR2 NO Expenditure functional currency code
2827 -- P_proj_cost_job_group_id NUMBER NO Project cost job gorup id
2828 -- P_job_cost_rate_schedule_id NUMBER NO Job cost rate schedule id
2829 -- P_project_type VARCHAR2 NO Project Type
2830 -- P_task_id NUMBER NO Task Id for the given project
2831 -- p_projfunc_currency_code VARCHAR2 NO Project Functional currency code
2832 -- P_bill_rate_multiplier NUMBER NO Bill rate multiplier for calculating the revenue
2833 -- P_project_bill_job_group_id NUMBER NO Billing job group id for project
2834 -- p_emp_bill_rate_schedule_id NUMBER NO Employee bill rate schedule id
2835 -- P_job_bill_rate_schedule_id NUMBER NO Job bill rate schedule id
2836 -- and rate
2837 -- p_distribution_rule VARCHAR2 NO Distribution rule
2838 --
2839 -- Out parameters
2840 --
2841 -- x_exp_func_raw_cost_rate NUMBER YES Row cost rate in expenditure currency
2842 -- x_exp_func_raw_cost NUMBER YES Row cost in expenditure currency
2843 -- x_exp_func_burdened_cost_rate NUMBER YES Burdened cost rate in expenditure currency
2844 -- x_exp_func_burdened_cost NUMBER YES Burdened cost in expenditure currency
2845 -- x_projfunc_bill_rate NUMBER YES Bill rate in project currency
2846 -- x_projfunc_raw_revenue NUMBER YES Raw revenue in project currency
2847 -- x_projfunc_raw_cost NUMBER YES Raw cost in project currency
2848 -- x_projfunc_raw_cost_rate NUMBER YES Raw cost rate in project currency
2849 -- x_projfunc_burdened_cost_rate NUMBER YES Burdened cost rate in project currency
2850 -- x_projfunc_burdened_cost NUMBER YES Burdened cost in project currency
2851 -- x_error_msg VARCHAR2 YES Error message used in when others exception
2852 -- x_rev_rejct_reason VARCHAR2 YES Rejection reason for revenue
2853 -- x_cost_rejct_reason VARCHAR2 YES Rejection reason for cost
2854 -- x_burdened_rejct_reason VARCHAR2 YES Rejection reason for burden
2855 -- x_others_rejct_reason VARCHAR2 YES Rejection reason for other error like pl/sql etc.
2856
2857 PROCEDURE calc_rate_amount(
2858
2859 p_calling_mode IN VARCHAR2 , /* possible values 'ASSIGNMENT','ROLE','UNASSIGNED' */
2860 /* for Org forecasting */
2861 p_rate_calc_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp ,
2862 p_asgn_start_date IN DATE ,
2863 p_item_id IN NUMBER ,
2864 p_project_id IN NUMBER ,
2865 p_quantity_tab IN PA_PLSQL_DATATYPES.NumTabTyp,
2866 p_forecast_job_id IN NUMBER DEFAULT NULL,
2867 p_forecast_job_group_id IN NUMBER DEFAULT NULL,
2868 p_person_id IN NUMBER DEFAULT NULL,
2869 p_expenditure_org_id_tab IN PA_PLSQL_DATATYPES.IdTabTyp,
2870 p_expenditure_type IN VARCHAR2 DEFAULT NULL,
2871 p_expenditure_orgz_id_tab IN PA_PLSQL_DATATYPES.IdTabTyp ,
2872 p_project_org_id IN NUMBER DEFAULT NULL,
2873 p_labor_cost_multi_name IN VARCHAR2 DEFAULT NULL,
2874 p_proj_cost_job_group_id IN NUMBER DEFAULT NULL,
2875 p_job_cost_rate_schedule_id IN NUMBER DEFAULT NULL,
2876 p_project_type IN VARCHAR2 DEFAULT NULL,
2877 p_task_id IN NUMBER DEFAULT NULL,
2878 p_bill_rate_multiplier IN NUMBER DEFAULT NULL,
2879 p_project_bill_job_group_id IN NUMBER DEFAULT NULL,
2880 p_emp_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
2881 p_job_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
2882 p_distribution_rule IN VARCHAR2 DEFAULT NULL,
2883 p_amount_calc_mode IN VARCHAR2 DEFAULT 'ALL', /* Possible values 'ALL','COST','REVENUE' */
2884 /* Added fro Org Forecasting*/
2885 P_system_linkage IN PA_PLSQL_DATATYPES.Char30TabTyp,/* Added */
2886 /* for Org Forecasting */
2887 p_assign_precedes_task IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2888 p_labor_schdl_discnt IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
2889 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
2890 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2891 p_labor_schedule_fixed_date IN DATE DEFAULT NULL, /* Added for Org Forecasting */
2892 p_labor_sch_type IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2893 p_forecast_item_id_tab IN PA_PLSQL_DATATYPES.IdTabTyp, /* Added para for bug 2212852 */
2894 p_forecasting_type IN VARCHAR2 DEFAULT 'PROJECT_FORECASTING',/*Added par for bug2212852*/
2895 p_projfunc_currency_code IN VARCHAR2 DEFAULT NULL,
2896 p_projfunc_rev_rt_dt_code_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for Org Forecasting */
2897 p_projfunc_rev_rt_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp, /* Added for Org Forecasting */
2898 p_projfunc_rev_rt_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for Org Forecasting */
2899 p_projfunc_rev_exch_rt_tab IN PA_PLSQL_DATATYPES.NumTabTyp, /* Added for Org Forecasting */
2900 p_projfunc_cst_rt_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp, /* Added for Org Forecasting */
2901 p_projfunc_cst_rt_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for Org Forecasting */
2902 x_projfunc_bill_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2903 x_projfunc_raw_revenue_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2904 x_projfunc_rev_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2905 x_projfunc_rev_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2906 x_projfunc_rev_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2907 x_projfunc_raw_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2908 x_projfunc_raw_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2909 x_projfunc_burdned_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2910 x_projfunc_burdned_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2911 x_projfunc_cst_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2912 x_projfunc_cst_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2913 x_projfunc_cst_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2914 p_project_currency_code IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
2915 p_project_rev_rt_dt_code_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */
2916 p_project_rev_rt_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */
2917 p_project_rev_rt_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */
2918 p_project_rev_exch_rt_tab IN PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */
2919 p_project_cst_rt_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */
2920 p_project_cst_rt_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */
2921 x_project_bill_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2922 x_project_raw_revenue_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2923 x_project_rev_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2924 x_project_rev_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2925 x_project_rev_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2926 x_project_raw_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2927 x_project_raw_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2928 x_project_burdned_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2929 x_project_burdned_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2930 x_project_cst_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2931 x_project_cst_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2932 x_project_cst_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2933 x_exp_func_curr_code_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
2934 x_exp_func_raw_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp , --File.Sql.39 bug 4440895
2935 x_exp_func_raw_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2936 x_exp_func_burdned_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2937 x_exp_func_burdned_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2938 x_exp_func_cst_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2939 x_exp_func_cst_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2940 x_exp_func_cst_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2941 x_cst_txn_curr_code_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
2942 x_txn_raw_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp , --File.Sql.39 bug 4440895
2943 x_txn_raw_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2944 x_txn_burdned_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2945 x_txn_burdned_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
2946 x_rev_txn_curr_code_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
2947 x_txn_rev_bill_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2948 x_txn_rev_raw_revenue_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
2949 x_error_msg OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2950 x_rev_rejct_reason_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, --File.Sql.39 bug 4440895
2951 x_cst_rejct_reason_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, --File.Sql.39 bug 4440895
2952 x_burdned_rejct_reason_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, --File.Sql.39 bug 4440895
2953 x_others_rejct_reason_tab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Changed for Org Forecasting */ --File.Sql.39 bug 4440895
2954 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2955 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2956 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2957 IS
2958 l_rate_calc_date DATE;
2959 l_rate_calc_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2960 l_quantity NUMBER;
2961 l_expenditure_org_id pa_project_assignments.expenditure_org_id%TYPE;
2962 l_expenditure_orgz_id pa_project_assignments.expenditure_organization_id%TYPE;
2963 /* MCB2 related changes */
2964 l_projfunc_bill_rate NUMBER;
2965 l_projfunc_raw_revenue NUMBER;
2966 l_projfunc_raw_cost NUMBER;
2967 l_projfunc_raw_cost_rate NUMBER;
2968 l_projfunc_burdened_cost NUMBER;
2969 l_projfunc_burdened_cost_rate NUMBER;
2970 /* till here */
2971 l_rev_rejct_reason VARCHAR2(30);
2972 l_cost_rejct_reason VARCHAR2(30);
2973 l_burdened_rejct_reason VARCHAR2(30);
2974 l_others_rejct_reason VARCHAR2(30);
2975
2976 l_return_status VARCHAR2(30);
2977 l_error_msg VARCHAR2(30);
2978
2979 l_exp_func_raw_cost_rate NUMBER;
2980 l_exp_func_raw_cost NUMBER;
2981 l_exp_func_burdened_cost_rate NUMBER;
2982 l_exp_func_burdened_cost NUMBER;
2983
2984 l_forecast_item_id pa_forecast_items.forecast_item_id%TYPE; /* added for bug 2212852 */
2985
2986 /* Adding for Org Forecasting */
2987
2988 l_projfunc_rev_rt_dt_code PA_PROJECTS_ALL.projfunc_bil_rate_date_code%TYPE;
2989 l_projfunc_rev_rt_type PA_PROJECTS_ALL.projfunc_bil_rate_type%TYPE;
2990 l_projfunc_rev_rt_date PA_PROJECTS_ALL.projfunc_bil_rate_date%TYPE;
2991 l_projfunc_rev_exch_rt PA_PROJECTS_ALL.projfunc_bil_exchange_rate%TYPE;
2992
2993 l_projfunc_cst_rt_type PA_PROJECTS_ALL.projfunc_cost_rate_type%TYPE;
2994 l_projfunc_cst_rt_date PA_PROJECTS_ALL.projfunc_cost_rate_date%TYPE;
2995 l_projfunc_cst_exch_rt NUMBER;
2996
2997 l_project_rev_rt_dt_code PA_PROJECTS_ALL.project_bil_rate_date_code%TYPE;
2998 l_project_rev_rt_type PA_PROJECTS_ALL.project_bil_rate_type%TYPE;
2999 l_project_rev_rt_date PA_PROJECTS_ALL.project_bil_rate_date%TYPE;
3000 l_project_rev_exch_rt PA_PROJECTS_ALL.project_bil_exchange_rate%TYPE;
3001
3002 l_project_cst_rt_type PA_PROJECTS_ALL.project_rate_type%TYPE;
3003 l_project_cst_rt_date PA_PROJECTS_ALL.project_rate_date%TYPE;
3004 l_project_cst_exch_rt NUMBER;
3005
3006 l_project_bill_rate NUMBER;
3007 l_project_raw_revenue NUMBER;
3008 l_project_raw_cost NUMBER;
3009 l_project_raw_cost_rate NUMBER;
3010 l_project_burdened_cost NUMBER;
3011 l_project_burdened_cost_rate NUMBER;
3012
3013 l_exp_func_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
3014 l_exp_func_cst_rt_date DATE;
3015 l_exp_func_cst_rt_type PA_IMPLEMENTATIONS_ALL.default_rate_type%TYPE;
3016 l_exp_func_cst_exch_rt NUMBER;
3017
3018 l_cst_txn_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
3019 l_txn_raw_cst_rt NUMBER;
3020 l_txn_raw_cst NUMBER;
3021 l_txn_burdned_cst_rt NUMBER;
3022 l_txn_burdned_cst NUMBER;
3023
3024 l_rev_txn_curr_code PA_BILL_RATES_ALL.rate_currency_code%TYPE;
3025 l_txn_rev_bill_rt NUMBER;
3026 l_txn_rev_raw_revenue NUMBER;
3027
3028 l_system_linkage pa_expenditure_items_all.system_linkage_function%TYPE;
3029
3030 BEGIN
3031
3032 --dbms_output.put_line(' I am in CALC RATE AMOUNT ');
3033
3034 IF g1_debug_mode = 'Y' THEN
3035 PA_DEBUG.Set_Curr_Function( p_function => 'Calc Rate Amount');
3036 PA_DEBUG.g_err_stage := 'RT40 : Entering PA_RATE_PVT_PKG.Calc_Rate_Amount';
3037 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3038 END IF;
3039
3040 x_return_status := FND_API.G_RET_STS_SUCCESS;
3041 IF g1_debug_mode = 'Y' THEN
3042 PA_DEBUG.g_err_stage := 'RTS1 : Checking tab count '||TO_CHAR(NVL(p_rate_calc_date_tab.count,0));
3043 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3044
3045
3046 PA_DEBUG.g_err_stage := ' Rate CALC Amt : Inside API project id '||p_project_id||' Item Id '||p_item_id;
3047 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3048 END IF;
3049
3050 IF ((p_rate_calc_date_tab.count) >= 1 ) THEN
3051 --dbms_output.put_line(' First : '||p_rate_calc_date_tab.FIRST);
3052 --dbms_output.put_line(' Last : '||p_rate_calc_date_tab.LAST);
3053 --dbms_output.put_line(' count : '||p_rate_calc_date_tab.count);
3054
3055 FOR l_J IN p_rate_calc_date_tab.FIRST..p_rate_calc_date_tab.LAST LOOP
3056 /* If the passed table does not have the specific index then inserting null at that possition
3057 so that other part of code should not execute for Org Forecasting */
3058 IF (p_rate_calc_date_tab.EXISTS(l_j)) THEN
3059 l_rate_calc_date_tab(l_j) := p_rate_calc_date_tab(l_j);
3060 ELSE
3061 l_rate_calc_date_tab(l_j) := NULL;
3062 END IF;
3063 END LOOP;
3064
3065 FOR l_J IN l_rate_calc_date_tab.FIRST..l_rate_calc_date_tab.LAST LOOP
3066
3067 -- dbms_output.put_line(' step 1 '||l_J);
3068 IF (l_rate_calc_date_tab(l_J) IS NOT NULL ) THEN /* { IF index in between is missing, then not calling for Org Forecasting */
3069
3070 -- dbms_output.put_line(' step 2 '||l_J);
3071 ----------------------------------------------------------------------------------------
3072 -- Assigning pl/sql table varibles to local varible and nulling out the OUT
3073 -- pl/sql table varibles
3074 -----------------------------------------------------------------------------------------
3075
3076 /* Project Functional currency */
3077
3078 l_projfunc_rev_rt_dt_code := p_projfunc_rev_rt_dt_code_tab(l_J); /* Added for Org Forecasting */
3079 l_projfunc_rev_rt_date := p_projfunc_rev_rt_date_tab(l_J); /* Added for Org Forecasting */
3080 l_projfunc_rev_rt_type := p_projfunc_rev_rt_type_tab(l_J); /* Added for Org Forecasting */
3081 l_projfunc_rev_exch_rt := p_projfunc_rev_exch_rt_tab(l_J); /* Added for Org Forecasting */
3082
3083 l_projfunc_cst_rt_date := p_projfunc_cst_rt_date_tab(l_J); /* Added for Org Forecasting */
3084 l_projfunc_cst_rt_type := p_projfunc_cst_rt_type_tab(l_J); /* Added for Org Forecasting */
3085
3086 x_projfunc_bill_rt_tab(l_J) := 0;
3087 x_projfunc_raw_revenue_tab(l_J) := 0;
3088
3089 x_projfunc_rev_rt_date_tab(l_J) := l_projfunc_rev_rt_date; /* Added for org Forecasting */
3090 x_projfunc_rev_rt_type_tab(l_J) := l_projfunc_rev_rt_type; /* Added for org Forecasting */
3091 x_projfunc_rev_exch_rt_tab(l_J) := l_projfunc_rev_exch_rt; /* Added for org Forecasting */
3092
3093 x_projfunc_raw_cst_tab(l_J) := 0;
3094 x_projfunc_raw_cst_rt_tab(l_J) := 0;
3095 x_projfunc_burdned_cst_tab(l_J) := 0;
3096 x_projfunc_burdned_cst_rt_tab(l_J) := 0;
3097
3098 x_projfunc_cst_rt_date_tab(l_J) := l_projfunc_cst_rt_date; /* Added for org Forecasting */
3099 x_projfunc_cst_rt_type_tab(l_J) := l_projfunc_cst_rt_type; /* Added for org Forecasting */
3100 x_projfunc_cst_exch_rt_tab(l_J) := l_projfunc_cst_exch_rt; /* Added for org Forecasting */
3101
3102 /* Project currency */
3103
3104 l_project_rev_rt_dt_code := p_project_rev_rt_dt_code_tab(l_J); /* Added for Org Forecasting */
3105 l_project_rev_rt_date := p_project_rev_rt_date_tab(l_J); /* Added for Org Forecasting */
3106 l_project_rev_rt_type := p_project_rev_rt_type_tab(l_J); /* Added for Org Forecasting */
3107 l_project_rev_exch_rt := p_project_rev_exch_rt_tab(l_J); /* Added for Org Forecasting */
3108
3109 l_project_cst_rt_date := p_project_cst_rt_date_tab(l_J); /* Added for Org Forecasting */
3110 l_project_cst_rt_type := p_project_cst_rt_type_tab(l_J); /* Added for Org Forecasting */
3111
3112 x_project_bill_rt_tab(l_J) := 0;
3113 x_project_raw_revenue_tab(l_J) := 0;
3114
3115 x_project_rev_rt_date_tab(l_J) := l_project_rev_rt_date; /* Added for org Forecasting */
3116 x_project_rev_rt_type_tab(l_J) := l_project_rev_rt_type; /* Added for org Forecasting */
3117 x_project_rev_exch_rt_tab(l_J) := l_project_rev_exch_rt; /* Added for org Forecasting */
3118
3119 x_project_raw_cst_tab(l_J) := 0;
3120 x_project_raw_cst_rt_tab(l_J) := 0;
3121 x_project_burdned_cst_tab(l_J) := 0;
3122 x_project_burdned_cst_rt_tab(l_J) := 0;
3123
3124 x_project_cst_rt_date_tab(l_J) := l_project_cst_rt_date; /* Added for org Forecasting */
3125 x_project_cst_rt_type_tab(l_J) := l_project_cst_rt_type; /* Added for org Forecasting */
3126 x_project_cst_exch_rt_tab(l_J) := l_project_cst_exch_rt; /* Added for org Forecasting */
3127
3128 -- dbms_output.put_line(' CALL CALC AMT EXCH RATE BEFORE PASSING : '||' rev_exch_rt '||l_project_rev_exch_rt||' _project_cst_exch_rt ' ||l_project_cst_exch_rt);
3129
3130 /* Expenditure Functional currency */
3131
3132 l_system_linkage := P_system_linkage(l_J);
3133
3134 x_exp_func_curr_code_tab(l_J) := l_exp_func_curr_code; /* Added for Org Forecasting */
3135 x_exp_func_cst_rt_date_tab(l_J) := l_exp_func_cst_rt_date; /* Added for Org Forecasting */
3136 x_exp_func_cst_rt_type_tab(l_J) := l_exp_func_cst_rt_type; /* Added for Org Forecasting */
3137 x_exp_func_cst_exch_rt_tab(l_J) := l_exp_func_cst_exch_rt; /* Added for Org Forecasting */
3138
3139 x_exp_func_raw_cst_rt_tab(l_J) := 0;
3140 x_exp_func_raw_cst_tab(l_J) := 0;
3141 x_exp_func_burdned_cst_rt_tab(l_J) := 0;
3142 x_exp_func_burdned_cst_tab(l_J) := 0;
3143
3144
3145 /* Transactional currency */
3146
3147 x_cst_txn_curr_code_tab(l_J) := l_cst_txn_curr_code; /* Added for Org Forecasting */
3148 x_txn_raw_cst_rt_tab(l_J) := 0;
3149 x_txn_raw_cst_tab(l_J) := 0;
3150 x_txn_burdned_cst_rt_tab(l_J) := 0;
3151 x_txn_burdned_cst_tab(l_J) := 0;
3152
3153 x_rev_txn_curr_code_tab(l_J) := l_rev_txn_curr_code; /* Added for Org Forecasting */
3154 x_txn_rev_bill_rt_tab(l_J) := 0; /* Added for Org Forecasting */
3155 x_txn_rev_raw_revenue_tab(l_J) := 0; /* Added for Org Forecasting */
3156
3157 x_rev_rejct_reason_tab(l_J) := NULL;
3158 x_cst_rejct_reason_tab(l_J) := NULL;
3159 x_burdned_rejct_reason_tab(l_J) := NULL;
3160 x_others_rejct_reason_tab(l_J) := NULL;
3161
3162 IF (x_others_rejct_reason_tab(l_J) IS NULL ) THEN /* Added for Org Forecasting , Added if got error from
3163 calling api { */
3164
3165 -- dbms_output.put_line(' step 3 '||l_J);
3166
3167 IF ( (p_calling_mode = 'ASSIGNMENT') OR (p_calling_mode = 'ROLE') ) THEN /* added for bug 2425570 */
3168 IF ( p_forecasting_type = 'PROJECT_FORECASTING') THEN /* Added this if for Org Forecasting */
3169 IF ( l_rate_calc_date_tab(1) >= p_asgn_start_date ) THEN
3170 null;
3171 ELSE
3172 l_rate_calc_date_tab(1) := p_asgn_start_date;
3173 END IF;
3174 END IF;
3175 END IF;
3176
3177 -- dbms_output.put_line('Index '||l_J);
3178 -- dbms_output.put_line('l_rate_calc_date_tab '||l_rate_calc_date_tab(l_J));
3179 -- dbms_output.put_line('p_quantity_tab '||p_quantity_tab(l_J));
3180 -- dbms_output.put_line('p_expenditure_org_id_tab '||p_expenditure_org_id_tab(l_J));
3181 -- dbms_output.put_line('p_expenditure_orgz_id_tab '||p_expenditure_orgz_id_tab(l_J));
3182 -- dbms_output.put_line('p_forecast_item_id_tab '||p_forecast_item_id_tab(l_J));
3183
3184 l_rate_calc_date := l_rate_calc_date_tab(l_J);
3185 l_quantity := p_quantity_tab(l_J);
3186 l_expenditure_org_id := p_expenditure_org_id_tab(l_J);
3187 l_expenditure_orgz_id := p_expenditure_orgz_id_tab(l_J);
3188 l_forecast_item_id := p_forecast_item_id_tab(l_J); /* added for bug 2212852 */
3189
3190 IF g1_debug_mode = 'Y' THEN
3191 PA_DEBUG.g_err_stage := 'RT41 : Entering PA_RATE_PVT_PKG.get_item_amount';
3192 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3193 END IF;
3194
3195 -- dbms_output.put_line('before calling Get Item AMount the index is : '||l_J);
3196 PA_RATE_PVT_PKG.get_item_amount(
3197 p_calling_mode => p_calling_mode ,
3198 p_rate_calc_date => l_rate_calc_date ,
3199 p_item_id => p_item_id ,
3200 p_project_id => p_project_id ,
3201 p_quantity => l_quantity ,
3202 p_forecast_job_id => p_forecast_job_id ,
3203 p_forecast_job_group_id => p_forecast_job_group_id ,
3204 p_person_id => p_person_id ,
3205 p_expenditure_org_id => l_expenditure_org_id ,
3206 p_expenditure_type => p_expenditure_type ,
3207 p_expenditure_organization_id => l_expenditure_orgz_id ,
3208 p_project_org_id => p_project_org_id ,
3209 p_labor_cost_multi_name => p_labor_cost_multi_name ,
3210 p_expenditure_currency_code => NULL ,
3211 p_proj_cost_job_group_id => p_proj_cost_job_group_id ,
3212 p_job_cost_rate_schedule_id => p_job_cost_rate_schedule_id ,
3213 p_project_type => p_project_type ,
3214 p_task_id => p_task_id ,
3215 p_bill_rate_multiplier => p_bill_rate_multiplier ,
3216 p_project_bill_job_group_id => p_project_bill_job_group_id ,
3217 p_emp_bill_rate_schedule_id => p_emp_bill_rate_schedule_id ,
3218 p_job_bill_rate_schedule_id => p_job_bill_rate_schedule_id ,
3219 p_distribution_rule => p_distribution_rule ,
3220 p_forecast_item_id => l_forecast_item_id, /* Added for bug 2212852 */
3221 p_forecasting_type => p_forecasting_type, /* Added for bug 2212852 */
3222 p_amount_calc_mode => p_amount_calc_mode, /* Added for Org Forcasting */
3223 p_system_linkage => l_system_linkage, /* Added for Org Forcasting */
3224 p_assign_precedes_task => p_assign_precedes_task , /* Added for Org Forcasting */
3225 p_labor_schdl_discnt => p_labor_schdl_discnt , /* Added for Org Forcasting */
3226 p_labor_bill_rate_org_id => p_labor_bill_rate_org_id , /* Added for Org Forcasting */
3227 p_labor_std_bill_rate_schdl => p_labor_std_bill_rate_schdl ,/* Added for Org Forcasting */
3228 p_labor_schedule_fixed_date => p_labor_schedule_fixed_date ,/* Added for Org Forcasting */
3229 p_labor_sch_type => p_labor_sch_type , /* Added for Org Forcasting */
3230 p_projfunc_currency_code => p_projfunc_currency_code , /* MCB2 change */
3231 p_projfunc_rev_rt_dt_code => l_projfunc_rev_rt_dt_code,
3232 p_projfunc_rev_rt_date => l_projfunc_rev_rt_date, /* Added for Org Forecasting */
3233 p_projfunc_rev_rt_type => l_projfunc_rev_rt_type, /* Added for Org Forecasting */
3234 p_projfunc_rev_exch_rt => l_projfunc_rev_exch_rt, /* Added for Org Forecasting */
3235 p_projfunc_cst_rt_date => l_projfunc_cst_rt_date, /* Added for Org Forecasting */
3236 p_projfunc_cst_rt_type => l_projfunc_cst_rt_type, /* Added for Org Forecasting */
3237 x_projfunc_bill_rate => l_projfunc_bill_rate ,
3238 x_projfunc_raw_revenue => l_projfunc_raw_revenue ,
3239 x_projfunc_rev_rt_date => l_projfunc_rev_rt_date, /* Added for org Forecasting */
3240 x_projfunc_rev_rt_type => l_projfunc_rev_rt_type, /* Added for org Forecasting */
3241 x_projfunc_rev_exch_rt => l_projfunc_rev_exch_rt, /* Added for org Forecasting */
3242 x_projfunc_raw_cost => l_projfunc_raw_cost ,
3243 x_projfunc_raw_cost_rate => l_projfunc_raw_cost_rate ,
3244 x_projfunc_burdened_cost => l_projfunc_burdened_cost ,
3245 x_projfunc_burdened_cost_rate => l_projfunc_burdened_cost_rate ,
3246 x_projfunc_cst_rt_date => l_projfunc_cst_rt_date, /* Added for org Forecasting */
3247 x_projfunc_cst_rt_type => l_projfunc_cst_rt_type, /* Added for org Forecasting */
3248 x_projfunc_cst_exch_rt => l_projfunc_cst_exch_rt, /* Added for org Forecasting */
3249 p_project_currency_code => p_project_currency_code, /* Added for org Forecasting */
3250 p_project_rev_rt_dt_code => l_project_rev_rt_dt_code, /* Added for org Forecasting */
3251 p_project_rev_rt_date => l_project_rev_rt_date, /* Added for org Forecasting */
3252 p_project_rev_rt_type => l_project_rev_rt_type, /* Added for org Forecasting */
3253 p_project_rev_exch_rt => l_project_rev_exch_rt, /* Added for org Forecasting */
3254 p_project_cst_rt_date => l_project_cst_rt_date, /* Added for org Forecasting */
3255 p_project_cst_rt_type => l_project_cst_rt_type, /* Added for org Forecasting */
3256 x_project_bill_rt => l_project_bill_rate, /* Added for org Forecasting */
3257 x_project_raw_revenue => l_project_raw_revenue, /* Added for org Forecasting */
3258 x_project_rev_rt_date => l_project_rev_rt_date, /* Added for org Forecasting */
3259 x_project_rev_rt_type => l_project_rev_rt_type, /* Added for org Forecasting */
3260 x_project_rev_exch_rt => l_project_rev_exch_rt, /* Added for org Forecasting */
3261 x_project_raw_cst => l_project_raw_cost, /* Added for org Forecasting */
3262 x_project_raw_cst_rt => l_project_raw_cost_rate, /* Added for org Forecasting */
3263 x_project_burdned_cst => l_project_burdened_cost, /* Added for org Forecasting */
3264 x_project_burdned_cst_rt => l_project_burdened_cost_rate, /* Added for org Forecasting */
3265 x_project_cst_rt_date => l_project_cst_rt_date, /* Added for org Forecasting */
3266 x_project_cst_rt_type => l_project_cst_rt_type, /* Added for org Forecasting */
3267 x_project_cst_exch_rt => l_project_cst_exch_rt, /* Added for org Forecasting */
3268 x_exp_func_curr_code => l_exp_func_curr_code, /* Added for Org Forecasting */
3269 x_exp_func_raw_cost_rate => l_exp_func_raw_cost_rate ,
3270 x_exp_func_raw_cost => l_exp_func_raw_cost ,
3271 x_exp_func_burdened_cost_rate => l_exp_func_burdened_cost_rate ,
3272 x_exp_func_burdened_cost => l_exp_func_burdened_cost ,
3273 x_exp_func_cst_rt_date => l_exp_func_cst_rt_date, /* Added for org Forecasting */
3274 x_exp_func_cst_rt_type => l_exp_func_cst_rt_type, /* Added for org Forecasting */
3275 x_exp_func_cst_exch_rt => l_exp_func_cst_exch_rt, /* Added for org Forecasting */
3276 x_cst_txn_curr_code => l_cst_txn_curr_code, /* Added for Org Forecasting */
3277 x_txn_raw_cst_rt => l_txn_raw_cst_rt ,
3278 x_txn_raw_cst => l_txn_raw_cst,
3279 x_txn_burdned_cst_rt => l_txn_burdned_cst_rt,
3280 x_txn_burdned_cst => l_txn_burdned_cst,
3281 x_rev_txn_curr_code => l_rev_txn_curr_code, /* Added for Org Forecasting */
3282 x_txn_rev_bill_rt => l_txn_rev_bill_rt, /* Added for org Forecasting */
3283 x_txn_rev_raw_revenue => l_txn_rev_raw_revenue, /* Added for org Forecasting */
3284 x_error_msg => l_error_msg ,
3285 x_rev_rejct_reason => l_rev_rejct_reason ,
3286 x_cost_rejct_reason => l_cost_rejct_reason ,
3287 x_burdened_rejct_reason => l_burdened_rejct_reason ,
3288 x_others_rejct_reason => l_others_rejct_reason ,
3289 x_return_status => l_return_status ,
3290 x_msg_count => x_msg_count ,
3291 x_msg_data => x_msg_data );
3292
3293 IF g1_debug_mode = 'Y' THEN
3294 PA_DEBUG.g_err_stage := 'RT42 : Leaving PA_RATE_PVT_PKG.get_item_amount';
3295 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3296 END IF;
3297
3298 -- dbms_output.put_line(' I am in CALC RATE AMOUNT end of get_item_amount : '||l_J);
3299 -- dbms_output.put_line(' I am in CALC RATE AMOUNT end of get_item_amount l_cost_rejct_reason : '||l_J||l_cost_rejct_reason);
3300 ----------------------------------------------------------------------------------------
3301 -- Assigning back local varibles to pl/sql tables
3302 -----------------------------------------------------------------------------------------
3303
3304 /* Project Functional currency */
3305
3306
3307 x_projfunc_bill_rt_tab(l_J) := l_projfunc_bill_rate;
3308 x_projfunc_raw_revenue_tab(l_J) := l_projfunc_raw_revenue;
3309
3310 x_projfunc_rev_rt_date_tab(l_J) := l_projfunc_rev_rt_date; /* Added for org Forecasting */
3311 x_projfunc_rev_rt_type_tab(l_J) := l_projfunc_rev_rt_type; /* Added for org Forecasting */
3312 x_projfunc_rev_exch_rt_tab(l_J) := l_projfunc_rev_exch_rt; /* Added for org Forecasting */
3313
3314 x_projfunc_raw_cst_tab(l_J) := l_projfunc_raw_cost;
3315 x_projfunc_raw_cst_rt_tab(l_J) := l_projfunc_raw_cost_rate;
3316 x_projfunc_burdned_cst_tab(l_J) := l_projfunc_burdened_cost;
3317 x_projfunc_burdned_cst_rt_tab(l_J) := l_projfunc_burdened_cost_rate;
3318
3319 x_projfunc_cst_rt_date_tab(l_J) := l_projfunc_cst_rt_date; /* Added for org Forecasting */
3320 x_projfunc_cst_rt_type_tab(l_J) := l_projfunc_cst_rt_type; /* Added for org Forecasting */
3321 x_projfunc_cst_exch_rt_tab(l_J) := l_projfunc_cst_exch_rt; /* Added for org Forecasting */
3322
3323 --dbms_output.put_line(' I am in CALC RATE AMOUNT PROJ FUNC : '||'projfunc bill : '||x_projfunc_bill_rt_tab(l_J)
3324 -- ||'projfunc revenue : '||x_projfunc_raw_revenue_tab(l_J)||' projfunc cost : '||x_projfunc_raw_cst_tab(l_J)||' burden cost :'||x_projfunc_burdned_cst_tab(l_J));
3325
3326 /* Project currency */
3327
3328 x_project_bill_rt_tab(l_J) := l_project_bill_rate;
3329 x_project_raw_revenue_tab(l_J) := l_project_raw_revenue;
3330
3331 x_project_rev_rt_date_tab(l_J) := l_project_rev_rt_date; /* Added for org Forecasting */
3332 x_project_rev_rt_type_tab(l_J) := l_project_rev_rt_type; /* Added for org Forecasting */
3333 x_project_rev_exch_rt_tab(l_J) := l_project_rev_exch_rt; /* Added for org Forecasting */
3334
3335 x_project_raw_cst_tab(l_J) := l_project_raw_cost;
3336 x_project_raw_cst_rt_tab(l_J) := l_project_raw_cost_rate;
3337 x_project_burdned_cst_tab(l_J) := l_project_burdened_cost;
3338 x_project_burdned_cst_rt_tab(l_J) := l_project_burdened_cost_rate;
3339
3340 x_project_cst_rt_date_tab(l_J) := l_project_cst_rt_date; /* Added for org Forecasting */
3341 x_project_cst_rt_type_tab(l_J) := l_project_cst_rt_type; /* Added for org Forecasting */
3342 x_project_cst_exch_rt_tab(l_J) := l_project_cst_exch_rt; /* Added for org Forecasting */
3343
3344 -- dbms_output.put_line(' I am in CALC RATE AMOUNT PROJ : '||'proj bill : '||x_project_bill_rt_tab(l_J)
3345 -- ||'proj revenue : '||x_project_raw_revenue_tab(l_J)||' proj cost : '||x_project_raw_cst_tab(l_J)||'proj cost exch rate '||NVL(l_project_cst_exch_rt,-99)||' proj burden cost :'||x_project_burdned_cst_tab(l_J));
3346
3347 -- dbms_output.put_line(' I am in CALC RATE AMOUNT PROJ RATES : '||'proj bill : '||x_project_bill_rt_tab(l_J)
3348 -- ||'proj rev_exch_rt : '||x_project_rev_exch_rt_tab(l_J)||' proj raw_cst_rt : '||x_project_raw_cst_rt_tab(l_J)||'proj burdned_cst_rt '||x_project_burdned_cst_rt_tab(l_J)||' project_cst_exch_rt '||x_project_cst_exch_rt_tab(l_J));
3349
3350 /* Expenditure Functional currency */
3351
3352 x_exp_func_curr_code_tab(l_J) := l_exp_func_curr_code; /* Added for Org Forecasting */
3353 x_exp_func_cst_rt_date_tab(l_J) := l_exp_func_cst_rt_date; /* Added for Org Forecasting */
3354 x_exp_func_cst_rt_type_tab(l_J) := l_exp_func_cst_rt_type; /* Added for Org Forecasting */
3355 x_exp_func_cst_exch_rt_tab(l_J) := l_exp_func_cst_exch_rt; /* Added for Org Forecasting */
3356
3357 x_exp_func_raw_cst_rt_tab(l_J) := l_exp_func_raw_cost_rate;
3358 x_exp_func_raw_cst_tab(l_J) := l_exp_func_raw_cost;
3359 x_exp_func_burdned_cst_rt_tab(l_J) := l_exp_func_burdened_cost_rate;
3360 x_exp_func_burdned_cst_tab(l_J) := l_exp_func_burdened_cost;
3361
3362
3363 -- dbms_output.put_line(' I am in CALC RATE AMOUNT EXP : '||' exp cost : '||x_exp_func_raw_cst_tab(l_J)||' exp burden cost :'||x_exp_func_burdned_cst_tab(l_J));
3364
3365 /* Transactional currency */
3366
3367 x_cst_txn_curr_code_tab(l_J) := l_cst_txn_curr_code; /* Added for Org Forecasting */
3368 x_txn_raw_cst_rt_tab(l_J) := l_txn_raw_cst_rt;
3369 x_txn_raw_cst_tab(l_J) := l_txn_raw_cst;
3370 x_txn_burdned_cst_rt_tab(l_J) := l_txn_burdned_cst_rt;
3371 x_txn_burdned_cst_tab(l_J) := l_txn_burdned_cst;
3372
3373 x_rev_txn_curr_code_tab(l_J) := l_rev_txn_curr_code; /* Added for Org Forecasting */
3374 x_txn_rev_bill_rt_tab(l_J) := l_txn_rev_bill_rt; /* Added for Org Forecasting */
3375 x_txn_rev_raw_revenue_tab(l_J) := l_txn_rev_raw_revenue; /* Added for Org Forecasting */
3376
3377 x_rev_rejct_reason_tab(l_J) := l_rev_rejct_reason;
3378 x_cst_rejct_reason_tab(l_J) := l_cost_rejct_reason;
3379 x_burdned_rejct_reason_tab(l_J) := l_burdened_rejct_reason;
3380 x_others_rejct_reason_tab(l_J) := l_others_rejct_reason;
3381
3382 x_return_status := l_return_status;
3383 x_error_msg := l_error_msg;
3384
3385 -- dbms_output.put_line(' I am in CALC RATE AMT ERROR : '||l_J ||x_cst_rejct_reason_tab(l_J));
3386 -- dbms_output.put_line(' I am in CALC RATE AMT ERROR 1 : '||l_J ||x_cst_rejct_reason_tab(l_J));
3387 -- dbms_output.put_line(' I am in CALC RATE AMOUNT ERROR : '||'l_cost_rejct_reason : '||x_cst_rejct_reason_tab(l_J));
3388 -- -- dbms_output.put_line(' I am in CALC RATE AMOUNT TXN : '||'txn bill : '||x_txn_rev_bill_rt_tab(l_J)||'txn revenue : '||x_txn_rev_raw_revenue_tab(l_J)||' txn cost : '||x_txn_raw_cst_tab(l_J)||' txn burden cost :'||x_txn_burdned_cst_tab(l_J));
3389 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
3390 x_return_status := l_return_status;
3391 END IF;
3392 END IF; /* End of other rejection if }*/
3393 END IF; /* } End of if date is null */
3394 END LOOP;
3395 ELSE
3396 -- DBMS_OUTPUT.PUT_LINE(' ERROR USER DEFINED ');
3397 RAISE NO_DATA_FOUND;
3398 END IF; /* Ending of table of record check if */
3399
3400 -- dbms_output.put_line(' I am in CALC RATE AMT ERROR END : ');
3401
3402 IF g1_debug_mode = 'Y' THEN
3403 PA_DEBUG.g_err_stage := 'RT43 : Leaving PA_RATE_PVT_PKG.calc rate amount';
3404 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3405 PA_DEBUG.Reset_Curr_Function;
3406 END IF;
3407
3408 EXCEPTION
3409 WHEN OTHERS THEN
3410 IF g1_debug_mode = 'Y' THEN
3411 PA_DEBUG.Reset_Curr_Function;
3412 END IF;
3413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3414 x_error_msg := SUBSTR(SQLERRM,1,30);
3415 /* Checking error condition. Added for bug 2218386 */
3416 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
3417 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
3418 p_procedure_name => 'Calc_Rate_Amount');
3419 RAISE;
3420 END IF;
3421
3422 END calc_rate_amount;
3423
3424
3425
3426 /* Added for performance bug 2691192, it replaces the use of view pa_rep_period_dates_v */
3427
3428 -- This procedure will display information about period types such as the name of the period
3429 -- and the start and end dates.
3430 -- Input parameters
3431 -- Parameters Type Required Description
3432 -- p_period_type VARCHAR2 YES Period type
3433 -- p_period_type DATE YES Schedule completion date
3434 -- Out parameters
3435 -- x_period_name VARCHAR2 Period name
3436 -- x_start_date DATE Start date of the period
3437 -- x_end_datet DATE End date of the period
3438 -- x_error_value VARCHAR2 Error status
3439 --
3440
3441 PROCEDURE get_rep_period_dates(
3442 p_period_type IN VARCHAR2 ,
3443 p_completion_date IN DATE,
3444 x_period_year OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3445 x_period_name OUT NOCOPY gl_periods.period_name%TYPE, --File.Sql.39 bug 4440895
3446 x_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
3447 x_end_date OUT NOCOPY DATE , --File.Sql.39 bug 4440895
3448 x_error_value OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3449 )
3450 IS
3451
3452 l_period_year NUMBER;
3453 l_period_name gl_periods.period_name%TYPE;
3454 l_start_date DATE;
3455 l_end_date DATE;
3456
3457 BEGIN
3458
3459 IF g1_debug_mode = 'Y' THEN
3460 PA_DEBUG.Set_Curr_Function( p_function => 'get_rep_period_dates ');
3461 PA_DEBUG.g_err_stage := 'RT 101 : Entering PA_RATE_PVT_PKG.get_rep_period_dates';
3462 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3463 END IF;
3464
3465 x_error_value := 'NO_ERROR';
3466
3467 IF (p_period_type = 'GL' ) THEN
3468
3469 IF g1_debug_mode = 'Y' THEN
3470 PA_DEBUG.g_err_stage := 'RT 102 : get_rep_period_dates-> Inside GL select prd typ '||p_period_type;
3471 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3472 END IF;
3473
3474 SELECT
3475 glper.period_year,
3476 glper.period_name,
3477 glper.start_date,
3478 glper.end_date
3479 INTO
3480 l_period_year,
3481 l_period_name,
3482 l_start_date,
3483 l_end_date
3484 FROM pa_implementations imp,
3485 gl_sets_of_books gl,
3486 gl_periods glper,
3487 gl_period_statuses glpersts
3488 WHERE imp.set_of_books_id = gl.set_of_books_id
3489 AND gl.period_set_name = glper.period_set_name
3490 AND gl.accounted_period_type = glper.period_type
3491 AND glpersts.set_of_books_id = gl.set_of_books_id
3492 AND glpersts.period_type = glper.period_type
3493 AND glpersts.period_name = glper.period_name
3494 AND glpersts.period_year = glper.period_year
3495 AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID
3496 AND p_completion_date BETWEEN glper.start_date AND glper.end_date
3497 AND EXISTS ( SELECT NULL
3498 FROM gl_date_period_map glmaps
3499 WHERE glmaps.period_type = glper.period_type
3500 AND glmaps.period_name = glper.period_name
3501 AND glmaps.period_set_name = glper.period_set_name )
3502 AND EXISTS ( SELECT NULL
3503 FROM gl_lookups prsts
3504 WHERE prsts.lookup_code IN('C','F','N','O','P')
3505 AND prsts.lookup_type ='CLOSING_STATUS'
3506 AND glpersts.closing_status = prsts.lookup_code);
3507
3508 IF g1_debug_mode = 'Y' THEN
3509 PA_DEBUG.g_err_stage := 'RT 103 : get_rep_period_dates-> Passed GL select prd nam '||l_period_name;
3510 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3511 END IF;
3512
3513 ELSIF (p_period_type = 'PA' ) THEN
3514
3515 IF g1_debug_mode = 'Y' THEN
3516 PA_DEBUG.g_err_stage := 'RT 104 : get_rep_period_dates-> Inside PA select prd typ '||p_period_type;
3517 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3518 END IF;
3519
3520 SELECT
3521 glp.period_year
3522 , pap.period_name
3523 , pap.start_date
3524 , pap.end_date
3525 INTO
3526 l_period_year,
3527 l_period_name,
3528 l_start_date,
3529 l_end_date
3530 FROM pa_periods pap,
3531 gl_period_statuses glp,
3532 pa_implementations paimp
3533 WHERE pap.gl_period_name = glp.period_name
3534 AND glp.set_of_books_id = paimp.set_of_books_id
3535 AND glp.application_id = Pa_Period_Process_Pkg.Application_id
3536 AND glp.adjustment_period_flag = 'N'
3537 AND p_completion_date BETWEEN pap.start_date and pap.end_date
3538 AND EXISTS (SELECT NULL
3539 FROM pa_lookups pal
3540 WHERE pal.lookup_type = 'CLOSING STATUS'
3541 AND pal.lookup_code = pap.status);
3542
3543 IF g1_debug_mode = 'Y' THEN
3544 PA_DEBUG.g_err_stage := 'RT 104 : get_rep_period_dates-> Passed PA select prd nam '||l_period_name;
3545 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3546 END IF;
3547
3548 ELSIF (p_period_type = 'QR' ) THEN
3549
3550 IF g1_debug_mode = 'Y' THEN
3551 PA_DEBUG.g_err_stage := 'RT 105 : get_rep_period_dates-> Inside QR select prd typ '||p_period_type;
3552 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3553 END IF;
3554
3555 SELECT
3556 period_yr
3557 , period_nam
3558 , start_dt
3559 , end_dt
3560 INTO
3561 l_period_year,
3562 l_period_name,
3563 l_start_date,
3564 l_end_date
3565 FROM (
3566 SELECT
3567 glper.period_year period_yr,
3568 TO_CHAR(glper.quarter_num) period_nam,
3569 MIN(glper.start_date) start_dt,
3570 MAX(glper.end_date) end_dt
3571 FROM pa_implementations imp,
3572 gl_sets_of_books gl,
3573 gl_periods glper,
3574 gl_period_statuses glpersts,
3575 gl_date_period_map glmaps
3576 WHERE imp.set_of_books_id = gl.set_of_books_id
3577 AND gl.period_set_name = glper.period_set_name
3578 AND gl.accounted_period_type = glper.period_type
3579 AND glpersts.set_of_books_id = gl.set_of_books_id
3580 AND glpersts.period_type = glper.period_type
3581 AND glpersts.period_name = glper.period_name
3582 AND glpersts.period_year = glper.period_year
3583 AND glmaps.period_type = glper.period_type
3584 AND glmaps.period_name = glper.period_name
3585 AND glmaps.period_set_name = glper.period_set_name
3586 AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID
3587 AND EXISTS (SELECT null
3588 FROM gl_lookups prsts
3589 WHERE prsts.lookup_code IN('C','F','N','O','P')
3590 AND prsts.lookup_type ='CLOSING_STATUS'
3591 AND glpersts.closing_status = prsts.lookup_code)
3592 GROUP BY glper.period_year,
3593 glper.quarter_num
3594 )
3595 WHERE p_completion_date BETWEEN start_dt AND end_dt;
3596
3597 IF g1_debug_mode = 'Y' THEN
3598 PA_DEBUG.g_err_stage := 'RT 106 : get_rep_period_dates-> Passed QR select prd nam '||l_period_name;
3599 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3600 END IF;
3601
3602 ELSIF (p_period_type = 'YR' ) THEN
3603
3604 IF g1_debug_mode = 'Y' THEN
3605 PA_DEBUG.g_err_stage := 'RT 107 : get_rep_period_dates-> Inside YR select prd typ '||p_period_type;
3606 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3607 END IF;
3608
3609 SELECT
3610 period_yr
3611 , period_nam
3612 , start_dt
3613 , end_dt
3614 INTO
3615 l_period_year,
3616 l_period_name,
3617 l_start_date,
3618 l_end_date
3619 FROM (
3620 SELECT
3621 glper.period_year period_yr,
3622 TO_CHAR(glper.period_year) period_nam,
3623 MIN(glper.start_date) start_dt,
3624 MAX(glper.end_date) end_dt
3625 FROM pa_implementations imp,
3626 gl_sets_of_books gl,
3627 gl_periods glper,
3628 gl_period_statuses glpersts,
3629 gl_date_period_map glmaps
3630 WHERE imp.set_of_books_id = gl.set_of_books_id
3631 AND gl.period_set_name = glper.period_set_name
3632 AND gl.accounted_period_type = glper.period_type
3633 AND glpersts.set_of_books_id = gl.set_of_books_id
3634 AND glpersts.period_type = glper.period_type
3635 AND glpersts.period_name = glper.period_name
3636 AND glpersts.period_year = glper.period_year
3637 AND glmaps.period_type = glper.period_type
3638 AND glmaps.period_name = glper.period_name
3639 AND glmaps.period_set_name = glper.period_set_name
3640 AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID
3641 AND EXISTS (SELECT null
3642 FROM gl_lookups prsts
3643 WHERE prsts.lookup_code IN('C','F','N','O','P')
3644 AND prsts.lookup_type ='CLOSING_STATUS'
3645 AND glpersts.closing_status = prsts.lookup_code)
3646 GROUP BY glper.period_year
3647 )
3648 WHERE p_completion_date BETWEEN start_dt AND end_dt;
3649
3650 IF g1_debug_mode = 'Y' THEN
3651 PA_DEBUG.g_err_stage := 'RT 108 : get_rep_period_dates-> Passed YR select prd nam '||l_period_name;
3652 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3653 END IF;
3654
3655 ELSIF (p_period_type = 'GE' ) THEN
3656
3657 IF g1_debug_mode = 'Y' THEN
3658 PA_DEBUG.g_err_stage := 'RT 109 : get_rep_period_dates-> Inside GE select prd typ '||p_period_type;
3659 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3660 END IF;
3661
3662 SELECT
3663 period_yr
3664 , period_nam
3665 , start_dt
3666 , end_dt
3667 INTO
3668 l_period_year,
3669 l_period_name,
3670 l_start_date,
3671 l_end_date
3672 FROM (
3673 SELECT
3674 period_year period_yr,
3675 TO_CHAR((NEXT_DAY(TO_DATE('01/01/'||TO_CHAR(period_Year),'MM/DD/YYYY'),
3676 TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7) period_nam,
3677 ( ((NEXT_DAY(TO_DATE('01/01/'||TO_CHAR(period_Year),'MM/DD/YYYY'),
3678 TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7) - 6 ) start_dt,
3679 ( (NEXT_DAY(TO_DATE('01/01/'||TO_CHAR(period_Year),'MM/DD/YYYY'),
3680 TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7 ) end_dt
3681 FROM pa_rep_year_cal_v,
3682 pa_rep_seq_number
3683 WHERE seq_number BETWEEN 1 AND 53
3684 )
3685 WHERE p_completion_date BETWEEN start_dt AND end_dt;
3686
3687 IF g1_debug_mode = 'Y' THEN
3688 PA_DEBUG.g_err_stage := 'RT 110 : get_rep_period_dates-> Passed GE select prd nam '||l_period_name;
3689 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3690 END IF;
3691
3692 END IF;
3693
3694 x_period_year := l_period_year;
3695 x_period_name := l_period_name;
3696 x_start_date := l_start_date;
3697 x_end_date := l_end_date;
3698
3699 IF g1_debug_mode = 'Y' THEN
3700 PA_DEBUG.g_err_stage := 'RT 111 : Leaving PA_RATE_PVT_PKG.get_rep_period_dates';
3701 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3702 PA_DEBUG.Reset_Curr_Function;
3703 END IF;
3704
3705 EXCEPTION
3706 WHEN NO_DATA_FOUND THEN
3707 x_error_value := 'NO_DATA_FOUND';
3708 IF g1_debug_mode = 'Y' THEN
3709 PA_DEBUG.Reset_Curr_Function;
3710 END IF;
3711 NULL;
3712 WHEN TOO_MANY_ROWS THEN
3713 x_error_value := 'TOO_MANY_ROWS';
3714 IF g1_debug_mode = 'Y' THEN
3715 PA_DEBUG.Reset_Curr_Function;
3716 END IF;
3717 NULL;
3718 WHEN OTHERS THEN
3719 IF g1_debug_mode = 'Y' THEN
3720 PA_DEBUG.Reset_Curr_Function;
3721 END IF;
3722
3723 /* ATG Changes */
3724
3725 x_period_name := null;
3726 x_start_date := null;
3727 x_end_date := null;
3728
3729 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
3730 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
3731 p_procedure_name => 'get_rep_period_dates');
3732 RAISE;
3733 END IF;
3734
3735 END get_rep_period_dates;
3736
3737
3738 END PA_RATE_PVT_PKG;
3739