[Home] [Help]
PACKAGE BODY: APPS.PA_RATE_PVT_PKG
Source
1 PACKAGE BODY PA_RATE_PVT_PKG as
2 /* $Header: PAXRTPVB.pls 120.10 2011/03/25 12:27:15 racheruv 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 - prj type '||p_project_type; --Bug 7423839
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- prj type '||l_project_type; --Bug 7423839;
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 PA_DEBUG.Log_Message ('p_person_id '||p_person_id||'l_expenditure_org_id '||l_expenditure_org_id); --skkoppul
801 PA_DEBUG.Log_Message ('l_expenditure_organization_id '||l_expenditure_organization_id||' l_labor_cost_mult_name '||l_labor_cost_mult_name);
802 PA_DEBUG.Log_Message ('p_rate_calc_date '||p_rate_calc_date||' l_expenditure_curr_code_burdn '||l_expenditure_curr_code_burdn||' p_quantity'||p_quantity);
803 END IF;
804
805
806 begin
807 pa_multi_currency_txn.G_calling_module:=p_calling_mode; --Bug 8243561: PRC: CALCULATE AMOUNTS DOES NOT PROCESS FUTURE DATED FIS
808 -- dbms_output.put_line('pp_person_id '||p_person_id);
809
810 -- 12.2 payroll integration .. rbc reqmt
811
812 pa_pay_util.g_project_number := NULL;
813 pa_pay_util.g_task_number := NULL;
814 pa_pay_util.g_location_code := NULL;
815 pa_pay_util.g_job_name := NULL;
816 pa_pay_util.g_work_type_name := NULL;
817
818 if p_person_id is not null then
819
820 select name
821 into pa_pay_util.g_job_name
822 from per_jobs_v
823 where job_id = pa_utils.getempjobid(X_person_id => p_person_id, X_date => p_rate_calc_date);
824 select location_code
825 into pa_pay_util.g_location_code
826 from hr_locations a, per_all_assignments_f b
827 where b.person_id = p_person_id
828 and a.location_use = 'HR'
829 and a.location_id = b.location_id
830 and p_rate_calc_date between b.effective_start_date and nvl(p_rate_calc_date, b.effective_end_date);
831
832 end if;
833
834 -- get the project number and task number details
835 select segment1
836 into pa_pay_util.g_project_number
837 from pa_projects
838 where project_id = p_project_id;
839
840 if p_task_id is not null then
841 -- get the task number details
842 select task_number
843 into pa_pay_util.g_task_number
844 from pa_tasks
845 where task_id = p_task_id;
846
847 -- get the default work type information from the task
848 begin
849 select name
850 into pa_pay_util.g_work_type_name
851 from pa_work_types_vl a, pa_tasks b
852 where a.work_type_id = b.work_type_id
853 and b.task_id = p_task_id;
854
855 exception
856 when no_data_found then
857 pa_pay_util.g_work_type_name := NULL;
858 end;
859 end if;
860 -- 12.2 payroll integration .. rbc reqmt
861
862 PA_COST.get_raw_cost (
863 P_person_id => p_person_id ,
864 P_expenditure_org_id => l_expenditure_org_id ,
865 P_expend_organization_id => l_expenditure_organization_id , /*LCE*/
866 P_labor_Cost_Mult_Name => l_labor_cost_mult_name ,
867 P_Item_date => p_rate_calc_date ,
868 px_exp_func_curr_code => l_expenditure_curr_code_burdn ,
869 P_Quantity => p_quantity ,
870 X_Raw_cost_rate => l_exp_func_raw_cost_rate , /* Change for Org. Fore */
871 X_Raw_cost => l_exp_func_raw_cost , /* Change for Org. Fore */
872 x_return_status => l_x_return_status ,
873 x_msg_count => x_msg_count ,
874 x_msg_data => l_msg_data
875 );
876 exception --Bug 7423839
877 when others then
878 PA_DEBUG.g_err_stage := 'Error in PA_COST.get_raw_cost';
879 IF g1_debug_mode = 'Y' THEN
880 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
881 PA_DEBUG.Log_Message(p_message => 'x_msg_count '||x_msg_count || ' l_msg_data '||substr(l_msg_data,1,300)||' ret status '||l_x_return_status);
882 PA_DEBUG.Log_Message(p_message => SQLERRM);
883 END IF;
884 end;
885
886
887 x_exp_func_raw_cost_rate := l_exp_func_raw_cost_rate; /* Added for Org Forecasting */
888 x_exp_func_raw_cost := l_exp_func_raw_cost; /* Added for Org Forecasting */
889 x_exp_func_curr_code := l_expenditure_curr_code_burdn; /* Added for Org Forecasting */
890
891 IF g1_debug_mode = 'Y' THEN
892 PA_DEBUG.g_err_stage := 'RT13 : Leaving PA_COST.get_raw_cost';
893 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
894 END IF;
895
896 -- 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);
897
898
899 ELSIF (p_calling_mode = 'ROLE') THEN
900
901 -- dbms_output.put_line('start of req raw cost '||l_x_return_status);
902
903
904 IF g1_debug_mode = 'Y' THEN
905 PA_DEBUG.g_err_stage := 'RT12 : Entering PA_COST.requirement_raw_cost';
906 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
907 END IF;
908
909 PA_COST.requirement_raw_cost(
910 p_forecast_cost_job_group_id => l_forecast_job_group_id ,
911 p_forecast_cost_job_id => l_forecast_job_id ,
912 p_proj_cost_job_group_id => l_proj_cost_job_grp_id ,
913 px_proj_cost_job_id => l_proj_cost_job_id ,
914 p_item_date => p_rate_calc_date ,
915 p_job_cost_rate_sch_id => l_job_cost_rate_schedule_id ,
916 p_schedule_date => l_labor_schedule_fixed_date ,
917 p_quantity => p_quantity ,
918 p_cost_rate_multiplier => l_cost_rate_multiplier ,
919 P_expend_organization_id => l_expenditure_organization_id , /*LCE*/
920 p_org_id => l_project_org_id ,
921 x_raw_cost_rate => l_exp_func_raw_cost_rate ,
922 x_raw_cost => l_exp_func_raw_cost ,
923 x_txn_currency_code => l_expenditure_curr_code_burdn , /* Added for Org Forecasting */
924 x_return_status => l_x_return_status ,
925 x_msg_count => x_msg_count ,
926 x_msg_data => l_msg_data
927 );
928
929
930 x_exp_func_raw_cost_rate := l_exp_func_raw_cost_rate; /* Added for Org Forecasting */
931 x_exp_func_raw_cost := l_exp_func_raw_cost; /* Added for Org Forecasting */
932 x_exp_func_curr_code := l_expenditure_curr_code_burdn; /* Added for Org Forecasting */
933
934 IF g1_debug_mode = 'Y' THEN
935 PA_DEBUG.g_err_stage := 'RT13 : Leaving PA_COST.requirement_raw_cost';
936 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
937 END IF;
938 -- 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);
939
940 END IF;
941
942 -- Validating that the called procedure has run without error , if not,then not calling others
943 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(l_exp_func_raw_cost,0) = 0) THEN
944
945 -- dbms_output.put_line('in error of raw cost ');
946 /* Commented this for bug 2199203 and write the other one */
947 /* x_cost_rejct_reason := 'PA_FCST_NO_COST_RATE'; */
948 x_cost_rejct_reason := SUBSTR(l_msg_data,1,30);
949 x_exp_func_raw_cost_rate := 0;
950 x_exp_func_raw_cost := 0;
951 x_exp_func_curr_code := l_expenditure_currency_code; /* Added for Org Forecasting */
952
953 l_x_process_return_status := l_x_return_status;
954
955 -- dbms_output.put_line('in error of raw cost x_cost_rejct_reason '||NVL(x_cost_rejct_reason,'Bye Bye'));
956 -- RAISE l_raw_cost_null;
957
958 END IF;
959
960 l_new_pvdr_acct_raw_cost := l_exp_func_raw_cost;
961 l_raw_cost_rate := l_exp_func_raw_cost_rate;
962
963 -- dbms_output.put_line(' error in multi cost 1 '||x_cost_rejct_reason);
964 --------------------------------------------------------------------
965 -- To get the Override Organization Id, The procedure will be called,
966 -- This is only for Staffed assignment. (Assignment)
967 --------------------------------------------------------------------
968 IF (p_calling_mode = 'ASSIGNMENT') THEN
969
970 -- dbms_output.put_line('start of override '||l_x_return_status);
971 IF g1_debug_mode = 'Y' THEN
972 PA_DEBUG.g_err_stage := 'RT14 : Entering PA_COST.override_exp_organization';
973 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
974 END IF;
975
976 PA_COST.override_exp_organization(P_item_date => p_rate_calc_date ,
977 P_person_id => p_person_id ,
978 P_project_id => p_project_id ,
979 P_incurred_by_organz_id => l_expenditure_organization_id ,
980 P_Expenditure_type => l_expenditure_type ,
981 X_overr_to_organization_id => l_overr_to_organization_id ,
982 x_return_status => l_x_return_status ,
983 x_msg_count => x_msg_count ,
984 x_msg_data => l_msg_data
985 );
986
987 IF g1_debug_mode = 'Y' THEN
988 PA_DEBUG.g_err_stage := 'RT15 : Leaving PA_COST.override_exp_organization';
989 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
990 END IF;
991
992 -- dbms_output.put_line('end of override '||l_x_return_status ||' over id '||l_overr_to_organization_id);
993
994 END IF;
995
996 -- l_overr_to_organization_id := x_overr_to_organization_id;
997
998 -- dbms_output.put_line('start of get burden cost '||l_x_return_status);
999
1000 l_expenditure_currency_code := NVL(l_expenditure_curr_code_burdn,l_expenditure_currency_code); /* Made for Org Forecasting */
1001
1002 IF (NVL(l_exp_func_raw_cost,0) <> 0) THEN
1003
1004 /* Added for Org forecasting */
1005 IF g1_debug_mode = 'Y' THEN
1006 PA_DEBUG.g_err_stage := 'RT16 : Entering PA_COST.get_burdened_cost';
1007 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1008 END IF;
1009
1010 PA_COST.get_burdened_cost(
1011 p_project_type => l_project_type ,
1012 p_project_id => p_project_id ,
1013 p_task_id => p_task_id ,
1014 p_item_date => p_rate_calc_date ,
1015 p_expenditure_type => l_expenditure_type ,
1016 p_schedule_type => l_schedule_type ,
1017 px_exp_func_curr_code => l_expenditure_currency_code ,
1018 p_Incurred_by_organz_id => l_expenditure_organization_id ,
1019 p_raw_cost => l_new_pvdr_acct_raw_cost ,
1020 p_raw_cost_rate => l_raw_cost_rate ,
1021 p_quantity => p_quantity ,
1022 p_override_to_organz_id => l_overr_to_organization_id ,
1023 x_burden_cost => l_exp_func_burdened_cost , /* Changed for Org Forecasting */
1024 x_burden_cost_rate => l_exp_func_burdened_cost_rate , /* Changed for Org Forecasting */
1025 x_return_status => l_x_return_status ,
1026 x_msg_count => x_msg_count ,
1027 x_msg_data => l_msg_data
1028 );
1029
1030
1031 x_exp_func_burdened_cost_rate := l_exp_func_burdened_cost_rate; /* Added for Org Forecasting */
1032 x_exp_func_burdened_cost := l_exp_func_burdened_cost; /* Added for Org Forecasting */
1033 x_exp_func_curr_code := l_expenditure_currency_code; /* Added for Org Forecasting */
1034
1035 IF g1_debug_mode = 'Y' THEN
1036 PA_DEBUG.g_err_stage := 'RT17 : Leaving PA_COST.get_burdened_cost';
1037 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1038 END IF;
1039
1040 -- 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);
1041
1042 ELSIF (NVL(l_exp_func_raw_cost,0) = 0) THEN -- Added for bug 2347087
1043 l_msg_data := 'PA_FCST_NO_COST_RATE';
1044 END IF; /* Added for Org forecasting */
1045
1046 -- Validating that the called procedure has run without error , if not,then not calling others
1047 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(x_exp_func_burdened_cost,0) = 0) THEN
1048
1049 /* Commented this for bug 2199203 and write the other one */
1050 /* x_burdened_rejct_reason := 'PA_FCST_NO_COST_RATE'; */
1051 x_burdened_rejct_reason := SUBSTR(l_msg_data,1,30);
1052 x_exp_func_burdened_cost_rate := 0;
1053 x_exp_func_burdened_cost := 0;
1054 x_exp_func_curr_code := l_expenditure_currency_code; /* Added for Org Forecasting */
1055
1056 l_x_process_return_status := l_x_return_status;
1057 -- RAISE l_burdened_cost_null;
1058
1059 END IF;
1060
1061 l_new_pvdr_acct_burdened_cost := l_exp_func_burdened_cost; /* Changed for Org Forecasting */
1062 l_burdened_cost_rate := l_exp_func_burdened_cost_rate; /* Changed for Org Forecasting */
1063
1064 -- dbms_output.put_line('start of Get_Converted_Cost_Amounts '||l_x_return_status);
1065
1066 IF g1_debug_mode = 'Y' THEN
1067 PA_DEBUG.g_err_stage := 'RT18 : Entering PA_COST.get_proj_raw_burdened_cost';
1068 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1069 END IF;
1070
1071 -- dbms_output.put_line(' error in multi cost 2 '||x_cost_rejct_reason||' amount '||l_exp_func_raw_cost);
1072
1073 -- dbms_output.put_line('l_exp_func_raw_cost ' ||l_exp_func_raw_cost);
1074 /* Added for Org Forecasting */
1075 IF (NVL(l_exp_func_raw_cost,0) <> 0) THEN
1076 -- dbms_output.put_line(' inside l_exp_func_raw_cost ' ||l_exp_func_raw_cost);
1077 PA_COST.Get_Converted_Cost_Amounts(
1078 P_exp_org_id => l_expenditure_org_id,
1079 P_proj_org_id => l_project_org_id,
1080 P_project_id => p_project_id,
1081 P_task_id => p_task_id,
1082 P_item_date => p_rate_calc_date,
1083 p_system_linkage => p_system_linkage,
1084 px_txn_curr_code => l_cst_txn_curr_code,
1085 px_raw_cost => l_new_pvdr_acct_raw_cost,
1086 px_raw_cost_rate => l_raw_cost_rate,
1087 px_burden_cost => l_new_pvdr_acct_burdened_cost,
1088 px_burden_cost_rate => l_burdened_cost_rate,
1089 px_exp_func_curr_code => l_expenditure_currency_code,
1090 px_exp_func_rate_date => l_exp_func_cst_rt_date,
1091 px_exp_func_rate_type => l_exp_func_cst_rt_type,
1092 px_exp_func_exch_rate => l_exp_func_cst_exch_rt,
1093 px_exp_func_cost => l_exp_func_raw_cost,
1094 px_exp_func_cost_rate => l_exp_func_raw_cost_rate,
1095 px_exp_func_burden_cost => l_exp_func_burdened_cost,
1096 px_exp_func_burden_cost_rate => l_exp_func_burdened_cost_rate,
1097 px_proj_func_curr_code => l_projfunc_currency_code,
1098 px_projfunc_cost_rate_date => l_projfunc_cost_rate_date,
1099 px_projfunc_cost_rate_type => l_projfunc_cost_rate_type,
1100 px_projfunc_cost_exch_rate => l_projfunc_cost_exchange_rate,
1101 px_projfunc_raw_cost => l_projfunc_raw_cost ,
1102 px_projfunc_raw_cost_rate => l_projfunc_raw_cost_rate ,
1103 px_projfunc_burden_cost => l_projfunc_burdened_cost ,
1104 px_projfunc_burden_cost_rate => l_projfunc_burdened_cost_rate ,
1105 px_project_curr_code => l_project_currency_code,
1106 px_project_rate_date => l_project_cost_rate_date,
1107 px_project_rate_type => l_project_cost_rate_type,
1108 px_project_exch_rate => l_project_cost_exchange_rate,
1109 px_project_cost => l_project_raw_cost,
1110 px_project_cost_rate => l_project_raw_cost_rate,
1111 px_project_burden_cost => l_project_burdened_cost,
1112 px_project_burden_cost_rate => l_project_burdened_cost_rate,
1113 x_return_status => l_x_return_status ,
1114 x_msg_count => x_msg_count ,
1115 x_msg_data => l_msg_data
1116 );
1117
1118 x_projfunc_raw_cost := l_projfunc_raw_cost;
1119 x_projfunc_raw_cost_rate := l_projfunc_raw_cost_rate;
1120 x_projfunc_burdened_cost := l_projfunc_burdened_cost;
1121 x_projfunc_burdened_cost_rate := l_projfunc_burdened_cost_rate;
1122 x_projfunc_cst_rt_date := l_projfunc_cost_rate_date;
1123 x_projfunc_cst_rt_type := l_projfunc_cost_rate_type;
1124 x_projfunc_cst_exch_rt := l_projfunc_cost_exchange_rate;
1125
1126 x_project_raw_cst := l_project_raw_cost;
1127 x_project_raw_cst_rt := l_project_raw_cost_rate;
1128 x_project_burdned_cst := l_project_burdened_cost;
1129 x_project_burdned_cst_rt := l_project_burdened_cost_rate;
1130 x_project_cst_rt_date := l_project_cost_rate_date;
1131 x_project_cst_rt_type := l_project_cost_rate_type;
1132 x_project_cst_exch_rt := l_project_cost_exchange_rate;
1133
1134 x_exp_func_curr_code := l_expenditure_currency_code;
1135 x_exp_func_raw_cost_rate := l_exp_func_raw_cost_rate;
1136 x_exp_func_raw_cost := l_exp_func_raw_cost;
1137 x_exp_func_burdened_cost_rate := l_exp_func_burdened_cost_rate;
1138 x_exp_func_burdened_cost := l_exp_func_burdened_cost;
1139 x_exp_func_cst_rt_date := l_exp_func_cst_rt_date;
1140 x_exp_func_cst_rt_type := l_exp_func_cst_rt_type;
1141 x_exp_func_cst_exch_rt := l_exp_func_cst_exch_rt;
1142
1143 x_cst_txn_curr_code := l_cst_txn_curr_code;
1144 x_txn_raw_cst_rt := l_raw_cost_rate;
1145 x_txn_raw_cst := l_new_pvdr_acct_raw_cost;
1146 x_txn_burdned_cst_rt := l_burdened_cost_rate;
1147 x_txn_burdned_cst := l_new_pvdr_acct_burdened_cost;
1148
1149 -- dbms_output.put_line(' Inside error in multi cost 3 '||x_cost_rejct_reason);
1150 /* Deleted this proc PA_COST.get_projfunc_raw_burdened_cost() for Org Forecasting */
1151
1152 IF g1_debug_mode = 'Y' THEN
1153 PA_DEBUG.g_err_stage := 'RT19 : Leaving PA_COST.get_proj_raw_burdened_cost';
1154 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1155 END IF;
1156
1157 -- 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
1158 -- ||' proj bur co '||x_projfunc_burdened_cost||' proj bur rate '||x_projfunc_burdened_cost_rate||' PROJECT BURDEN COST '||x_project_burdned_cst);
1159
1160 -- 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);
1161
1162 END IF; -- Added for Org Forecasting
1163
1164 -- Validating that the called procedure has run without error , if not,then not calling others
1165 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(l_projfunc_raw_cost,0) = 0 ) THEN
1166
1167 -- 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
1168 -- ||' proj rate '||x_projfunc_raw_cost_rate||' proj bur co '||x_projfunc_burdened_cost||' proj bur rate '||x_projfunc_burdened_cost_rate);
1169
1170 -- dbms_output.put_line(' CHECK error in multi co '||x_cost_rejct_reason);
1171 /* Commented this for bug 2199203 and write the other one */
1172 /* x_cost_rejct_reason := 'PA_FCST_NO_COST_RATE'; */
1173 x_cost_rejct_reason := SUBSTR(NVL(x_cost_rejct_reason,l_msg_data),1,30); -- Added for bug 2347087
1174 x_projfunc_raw_cost_rate := 0;
1175 x_projfunc_raw_cost := 0;
1176 x_projfunc_burdened_cost := 0;
1177 x_projfunc_burdened_cost_rate := 0;
1178
1179 --dbms_output.put_line(' The error is '||x_cost_rejct_reason);
1180 /* Added for Org Forecasting */
1181 x_projfunc_cst_rt_date := l_projfunc_cost_rate_date;
1182 x_projfunc_cst_rt_type := l_projfunc_cost_rate_type;
1183 x_projfunc_cst_exch_rt := l_projfunc_cost_exchange_rate;
1184
1185 x_project_raw_cst := 0;
1186 x_project_raw_cst_rt := 0;
1187 x_project_burdned_cst := 0;
1188 x_project_burdned_cst_rt := 0;
1189
1190 x_project_cst_rt_date := l_project_cost_rate_date;
1191 x_project_cst_rt_type := l_project_cost_rate_type;
1192 x_project_cst_exch_rt := l_project_cost_exchange_rate;
1193
1194 x_exp_func_curr_code := l_expenditure_currency_code;
1195 x_exp_func_raw_cost_rate := 0;
1196 x_exp_func_raw_cost := 0;
1197 x_exp_func_burdened_cost_rate := 0;
1198 x_exp_func_burdened_cost := 0;
1199
1200 x_exp_func_cst_rt_date := l_exp_func_cst_rt_date;
1201 x_exp_func_cst_rt_type := l_exp_func_cst_rt_type;
1202 x_exp_func_cst_exch_rt := l_exp_func_cst_exch_rt;
1203
1204 x_cst_txn_curr_code := l_cst_txn_curr_code;
1205 x_txn_raw_cst_rt := 0;
1206 x_txn_raw_cst := 0;
1207 x_txn_burdned_cst_rt := 0;
1208 x_txn_burdned_cst := 0;
1209
1210 l_x_process_return_status := l_x_return_status;
1211
1212 -- RAISE l_raw_proj_cost_null;
1213
1214 END IF;
1215
1216 -- dbms_output.put_line(' END Check error in multi cost 4 '||x_cost_rejct_reason);
1217 l_new_rcvr_acct_raw_cost := x_projfunc_raw_cost;
1218 l_new_rcvr_acct_raw_cost_rate := x_projfunc_raw_cost_rate;
1219 l_new_rcvr_acct_burdened_cost := x_projfunc_burdened_cost;
1220 l_new_rcvr_acct_bur_cost_rate := x_projfunc_burdened_cost_rate;
1221
1222
1223 END IF; /* End of p_amount_calc_mode } */
1224
1225 --------------------------------------------------------------
1226 -- Calling Bill rate API to get the bill rate and raw Revenue.
1227 --------------------------------------------------------------
1228
1229 l_Schedule_type := 'REVENUE';
1230
1231 -- dbms_output.put_line('revenue start '||l_distribution_rule);
1232
1233 IF (l_amount_calc_mode <> 'COST') THEN /* Added for Org For. { */
1234
1235 IF ((SUBSTR(l_distribution_rule,1,4) = 'WORK') AND ( l_class_code = 'CONTRACT')
1236 AND (p_calling_mode <> 'UNASSIGNED') ) THEN /* Unasigned check added for Org { */
1237
1238 l_msg_data := NULL;
1239 IF ( p_calling_mode = 'ASSIGNMENT') THEN
1240
1241 -- dbms_output.put_line('start of get rev amt '||l_x_return_status);
1242
1243 IF g1_debug_mode = 'Y' THEN
1244 PA_DEBUG.g_err_stage := 'RT20 : Entering PA_REVENUE.get_rev_amt';
1245 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1246 END IF;
1247
1248 PA_REVENUE.Assignment_Rev_Amt(
1249 p_project_id => p_project_id ,
1250 p_task_id => p_task_id ,
1251 p_bill_rate_multiplier => p_bill_rate_multiplier ,
1252 p_quantity => p_quantity ,
1253 p_person_id => p_person_id ,
1254 p_raw_cost => l_new_rcvr_acct_raw_cost ,
1255 p_item_date => p_rate_calc_date ,
1256 p_labor_schdl_discnt => l_labor_schedule_discount , -- can be null
1257 p_labor_bill_rate_org_id => l_labor_bill_rate_org_id , -- can be null
1258 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schedule , -- can be null
1259 p_labor_schdl_fixed_date => l_labor_schedule_fixed_date , -- can be null
1260 p_bill_job_grp_id => l_project_bill_job_group_id ,
1261 p_item_id => p_item_id , /* changed for bug 2212852 */
1262 p_forecast_item_id => p_forecast_item_id , /* added for bug 2212852 */
1263 p_forecasting_type => p_forecasting_type , /* added for bug 2212852 */
1264 p_labor_sch_type => l_labor_schedule_type ,
1265 p_project_org_id => l_project_org_id ,
1266 p_project_type => l_project_type ,
1267 p_expenditure_type => l_expenditure_type ,
1268 p_exp_func_curr_code => l_expenditure_currency_code ,
1269 p_incurred_by_organz_id => l_expenditure_organization_id ,
1270 p_raw_cost_rate => l_raw_cost_rate ,
1271 p_override_to_organz_id => l_overr_to_organization_id ,
1272 p_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id ,
1273 p_resource_job_id => l_forecast_job_id ,
1274 p_exp_raw_cost => l_new_pvdr_acct_raw_cost ,
1275 p_expenditure_org_id => l_expenditure_org_id ,
1276 p_projfunc_currency_code => l_projfunc_currency_code , -- The following 5
1277 p_assignment_precedes_task => l_assignment_precedes_task , /* Added for Asgmt overide */
1278 p_sys_linkage_function => p_system_linkage, /* Added for Org FCST */
1279 x_bill_rate => l_txn_rev_bill_rt , /* Change for Org Forecsting */
1280 x_raw_revenue => l_txn_rev_raw_revenue, /* Change for Org Forecasting */
1281 x_markup_percentage => l_markup_percentage ,/* Added for Asgmt overide */
1282 x_txn_currency_code => l_rev_txn_curr_code, /* added for Org */
1283 x_rev_currency_code => l_projfunc_currency_code ,
1284 x_return_status => l_x_return_status ,
1285 x_msg_count => x_msg_count ,
1286 x_msg_data => l_msg_data ,
1287 /* Added for bug 2668753 */
1288 p_project_raw_cost => l_project_raw_cost ,
1289 p_project_currency_code => l_project_currency_code ,
1290 x_adjusted_bill_rate => l_txn_adjusted_bill_rt --added the parameter for 4038485
1291 );
1292
1293 x_txn_rev_bill_rt := l_txn_rev_bill_rt;
1294 x_txn_rev_raw_revenue := l_txn_rev_raw_revenue;
1295 x_rev_txn_curr_code := l_rev_txn_curr_code;
1296
1297 IF g1_debug_mode = 'Y' THEN
1298 PA_DEBUG.g_err_stage := 'RT21 : Leaving PA_REVENUE.get_rev_amt';
1299 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1300 END IF;
1301 -- dbms_output.put_line('end of get rev amt '||l_x_return_status|| ' rate '||x_projfunc_bill_rate||' rev '||x_projfunc_raw_revenue);
1302 ELSIF (p_calling_mode= 'ROLE' ) THEN
1303
1304 -- dbms_output.put_line('start of req rev amt '||l_x_return_status);
1305
1306 IF g1_debug_mode = 'Y' THEN
1307 PA_DEBUG.g_err_stage := 'RT20 : Entering PA_REVENUE.requirement_rev_amt';
1308 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1309 END IF;
1310
1311 PA_REVENUE.Requirement_Rev_Amt(
1312 p_project_id => p_project_id ,
1313 p_task_id => p_task_id ,
1314 p_bill_rate_multiplier => p_bill_rate_multiplier ,
1315 p_quantity => p_quantity ,
1316 p_raw_cost => l_new_rcvr_acct_raw_cost ,
1317 p_item_date => p_rate_calc_date ,
1318 p_project_bill_job_grp_id => l_project_bill_job_group_id ,
1319 p_labor_schdl_discnt => l_labor_schedule_discount , -- can be null
1320 p_labor_bill_rate_org_id => l_labor_bill_rate_org_id , -- can be null
1321 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schedule , -- can be null
1322 p_labor_schdl_fixed_date => l_labor_schedule_fixed_date , -- can be null
1323 p_forecast_job_id => l_forecast_job_id ,
1324 p_forecast_job_grp_id => l_forecast_job_group_id ,
1325 p_labor_sch_type => l_labor_schedule_type , -- can be null
1326 p_item_id => p_item_id , /* changed for bug 2212852 */
1327 p_forecast_item_id => p_forecast_item_id , /* added for bug 2212852 */
1328 p_forecasting_type => p_forecasting_type , /* added for bug 2212852 */
1329 p_project_org_id => l_project_org_id ,
1330 p_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id ,
1331 p_project_type => l_project_type ,
1332 p_expenditure_type => l_expenditure_type ,
1333 px_exp_func_curr_code => l_expenditure_currency_code ,
1334 p_incurred_by_organz_id => l_expenditure_organization_id ,
1335 p_raw_cost_rate => l_raw_cost_rate ,
1336 p_override_to_organz_id => l_overr_to_organization_id ,
1337 p_exp_raw_cost => l_new_pvdr_acct_raw_cost ,
1338 p_expenditure_org_id => l_expenditure_org_id ,
1339 p_projfunc_currency_code => l_projfunc_currency_code , -- The following 5
1340 p_assignment_precedes_task => l_assignment_precedes_task , /* Added for Asgmt overide */
1341 p_sys_linkage_function => p_system_linkage, /* Added for Org FCST */
1342 px_project_bill_job_id => l_proj_bill_job_id ,
1343 x_bill_rate => l_txn_rev_bill_rt , /* Change for Org Forecsting */
1344 x_raw_revenue => l_txn_rev_raw_revenue, /* Change for Org Forecasting */
1345 x_markup_percentage => l_markup_percentage ,/* Added for Asgmt overide */
1346 x_txn_currency_code => l_rev_txn_curr_code, /* added for Org */
1347 x_return_status => l_x_return_status ,
1348 x_msg_count => x_msg_count ,
1349 x_msg_data => l_msg_data
1350 );
1351
1352
1353 x_txn_rev_bill_rt := l_txn_rev_bill_rt;
1354 x_txn_rev_raw_revenue := l_txn_rev_raw_revenue;
1355 x_rev_txn_curr_code := l_rev_txn_curr_code;
1356
1357 IF g1_debug_mode = 'Y' THEN
1358 PA_DEBUG.g_err_stage := 'RT21 : Leaving PA_REVENUE.requirement_rev_amt';
1359 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1360 END IF;
1361
1362 -- dbms_output.put_line('end of req rev amt '||l_x_return_status||' rate '||x_projfunc_bill_rate||' rev '||x_projfunc_raw_revenue);
1363
1364 END IF; /* End of calling mode if */
1365
1366
1367 -- Validating that the called procedure has run without error , if not,then not calling others
1368 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(l_txn_rev_raw_revenue,0) = 0) THEN
1369
1370 /* Commented this for bug 2199203 and write the other one */
1371 /* x_rev_rejct_reason := 'PA_FCST_NO_BILL_RATE'; */
1372 x_rev_rejct_reason := SUBSTR(l_msg_data,1,30);
1373 x_txn_rev_bill_rt := 0;
1374 x_txn_rev_raw_revenue := 0;
1375 x_rev_txn_curr_code := l_rev_txn_curr_code;
1376
1377 l_x_process_return_status := l_x_return_status;
1378
1379 END IF; /* End of return status if */
1380
1381 IF ( (NVL(l_txn_rev_raw_revenue,0) <> 0) ) THEN
1382
1383 PA_REVENUE.Get_Converted_Revenue_Amounts(
1384 p_item_date => p_rate_calc_date,
1385 px_txn_curr_code => l_rev_txn_curr_code,
1386 px_txn_raw_revenue => l_txn_rev_raw_revenue,
1387 px_txn_bill_rate => l_txn_rev_bill_rt,
1388 px_projfunc_curr_code => l_projfunc_currency_code,
1389 p_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
1390 px_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
1391 px_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
1392 px_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
1393 px_projfunc_raw_revenue => l_projfunc_raw_revenue ,
1394 px_projfunc_bill_rate => l_projfunc_bill_rate ,
1395 px_project_curr_code => l_project_currency_code,
1396 p_project_bil_rate_date_code => l_project_bil_rate_date_code,
1397 px_project_bil_rate_type => l_project_bil_rate_type,
1398 px_project_bil_rate_date => l_project_bil_rate_date,
1399 px_project_bil_exchange_rate => l_project_bil_exchange_rate,
1400 px_project_raw_revenue => l_project_raw_revenue ,
1401 px_project_bill_rate => l_project_bill_rate ,
1402 x_return_status => l_x_return_status ,
1403 x_msg_count => x_msg_count ,
1404 /* x_msg_data => x_msg_data Commnted out for bug 3143819
1405 and added modified one below i.e. instead of x_msg_data using
1406 l_msg_data */
1407 x_msg_data => l_msg_data
1408 );
1409
1410
1411 x_projfunc_bill_rate := l_projfunc_bill_rate;
1412 x_projfunc_raw_revenue := l_projfunc_raw_revenue;
1413 x_projfunc_rev_rt_date := l_projfunc_bil_rate_date;
1414 x_projfunc_rev_rt_type := l_projfunc_bil_rate_type;
1415 x_projfunc_rev_exch_rt := l_projfunc_bil_exchange_rate;
1416
1417 x_project_bill_rt := l_project_bill_rate;
1418 x_project_raw_revenue := l_project_raw_revenue;
1419 x_project_rev_rt_date := l_project_bil_rate_date;
1420 x_project_rev_rt_type := l_project_bil_rate_type;
1421 x_project_rev_exch_rt := l_project_bil_exchange_rate;
1422
1423 x_txn_rev_bill_rt := l_txn_rev_bill_rt;
1424 x_txn_rev_raw_revenue := l_txn_rev_raw_revenue;
1425 x_rev_txn_curr_code := l_rev_txn_curr_code;
1426 -- dbms_output.put_line(' after revenue conversion ');
1427 END IF;
1428
1429 -- Validating that the called procedure has run without error , if not,then not calling others
1430 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (NVL(l_projfunc_raw_revenue,0) = 0) THEN
1431
1432 x_rev_rejct_reason := SUBSTR(l_msg_data,1,30);
1433 x_txn_rev_bill_rt := 0;
1434 x_txn_rev_raw_revenue := 0;
1435 x_rev_txn_curr_code := l_rev_txn_curr_code;
1436
1437 x_projfunc_bill_rate := 0;
1438 x_projfunc_raw_revenue := 0;
1439 x_projfunc_rev_rt_date := l_projfunc_bil_rate_date;
1440 x_projfunc_rev_rt_type := l_projfunc_bil_rate_type;
1441 x_projfunc_rev_exch_rt := l_projfunc_bil_exchange_rate;
1442
1443 x_project_bill_rt := 0;
1444 x_project_raw_revenue := 0;
1445 x_project_rev_rt_date := l_project_bil_rate_date;
1446 x_project_rev_rt_type := l_project_bil_rate_type;
1447 x_project_rev_exch_rt := l_project_bil_exchange_rate;
1448
1449 l_x_process_return_status := l_x_return_status;
1450
1451 END IF; /* End of return status if */
1452
1453 END IF; /* End of rule and class code if } */
1454 END IF; /* p_amount_calc_mode if } */
1455
1456 l_new_rcvr_revenue := x_projfunc_raw_revenue;
1457
1458 -------------------------------------------------------
1459 -- Assign the successful status back to output variable
1460 -------------------------------------------------------
1461
1462 x_return_status := l_x_process_return_status;
1463 x_msg_data := l_msg_data;
1464
1465 -- dbms_output.put_line(' End error in multi cost '||x_cost_rejct_reason);
1466 -- dbms_output.put_line('end of procs '||l_x_return_status);
1467 IF g1_debug_mode = 'Y' THEN
1468 PA_DEBUG.g_err_stage := 'RT22 : Leaving PA_RATE_PVT_PKG.get_item_amount';
1469 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1470 PA_DEBUG.Reset_Curr_Function;
1471 END IF;
1472
1473
1474 EXCEPTION
1475 WHEN l_insufficient_parameters THEN
1476 IF g1_debug_mode = 'Y' THEN
1477 PA_DEBUG.Reset_Curr_Function;
1478 END IF;
1479 x_return_status := FND_API.G_RET_STS_ERROR;
1480 x_msg_count := 1;
1481 x_msg_data := 'PA_FCST_INSUFFICIENT_PARA';
1482 x_others_rejct_reason := 'PA_FCST_INSUFFICIENT_PARA';
1483
1484 WHEN l_raw_cost_null THEN
1485 IF g1_debug_mode = 'Y' THEN
1486 PA_DEBUG.Reset_Curr_Function;
1487 END IF;
1488 x_return_status := FND_API.G_RET_STS_ERROR;
1489 x_cost_rejct_reason := 'PA_FCST_NO_COST_RATE';
1490 x_exp_func_raw_cost_rate := 0;
1491 x_exp_func_raw_cost := 0;
1492 WHEN l_raw_proj_cost_null THEN
1493 IF g1_debug_mode = 'Y' THEN
1494 PA_DEBUG.Reset_Curr_Function;
1495 END IF;
1496 x_return_status := FND_API.G_RET_STS_ERROR;
1497 x_cost_rejct_reason := 'PA_FCST_NO_COST_RATE';
1498 /* Added for Org Forecasting */
1499 x_projfunc_raw_cost_rate := 0;
1500 x_projfunc_raw_cost := 0;
1501 x_projfunc_burdened_cost := 0;
1502 x_projfunc_burdened_cost_rate := 0;
1503
1504 x_project_raw_cst := 0;
1505 x_project_raw_cst_rt := 0;
1506 x_project_burdned_cst := 0;
1507 x_project_burdned_cst_rt := 0;
1508
1509 x_exp_func_raw_cost_rate := 0;
1510 x_exp_func_raw_cost := 0;
1511 x_exp_func_burdened_cost_rate := 0;
1512 x_exp_func_burdened_cost := 0;
1513
1514 x_txn_raw_cst_rt := 0;
1515 x_txn_raw_cst := 0;
1516 x_txn_burdned_cst_rt := 0;
1517 x_txn_burdned_cst := 0;
1518
1519 --dbms_output.put_line('exp2');
1520 WHEN l_raw_revenue_null THEN
1521 IF g1_debug_mode = 'Y' THEN
1522 PA_DEBUG.Reset_Curr_Function;
1523 END IF;
1524 x_return_status := FND_API.G_RET_STS_ERROR;
1525 x_rev_rejct_reason := 'PA_FCST_NO_BILL_RATE';
1526 x_txn_rev_bill_rt := 0;
1527 x_txn_rev_raw_revenue := 0;
1528
1529 --dbms_output.put_line('exp3');
1530
1531 WHEN l_burdened_cost_null THEN
1532 IF g1_debug_mode = 'Y' THEN
1533 PA_DEBUG.Reset_Curr_Function;
1534 END IF;
1535 x_return_status := FND_API.G_RET_STS_ERROR;
1536 x_burdened_rejct_reason := 'PA_FCST_NO_COST_RATE';
1537 x_exp_func_burdened_cost_rate := 0;
1538 x_exp_func_burdened_cost := 0;
1539
1540 --dbms_output.put_line('exp4');
1541
1542 WHEN OTHERS THEN
1543 IF g1_debug_mode = 'Y' THEN
1544 PA_DEBUG.Reset_Curr_Function;
1545 END IF;
1546
1547 --dbms_output.put_line('exp5'||SQLERRM);
1548
1549 /* ATG Changes */
1550
1551 x_projfunc_bill_rate := null;
1552 x_projfunc_raw_revenue := null;
1553 x_projfunc_rev_rt_date := null;
1554 x_projfunc_rev_rt_type := null;
1555 x_projfunc_rev_exch_rt := null;
1556 x_projfunc_raw_cost := null;
1557 x_projfunc_raw_cost_rate := null;
1558 x_projfunc_burdened_cost := null;
1559 x_projfunc_burdened_cost_rate := null;
1560 x_projfunc_cst_rt_date := null;
1561 x_projfunc_cst_rt_type := null;
1562 x_projfunc_cst_exch_rt := null;
1563 x_project_bill_rt := null;
1564 x_project_raw_revenue := null;
1565 x_project_rev_rt_date := null;
1566 x_project_rev_rt_type := null;
1567 x_project_rev_exch_rt := null;
1568 x_project_raw_cst := null;
1569 x_project_raw_cst_rt := null;
1570 x_project_burdned_cst := null;
1571 x_project_burdned_cst_rt := null;
1572 x_project_cst_rt_date := null;
1573 x_project_cst_rt_type := null;
1574 x_project_cst_exch_rt := null;
1575 x_exp_func_curr_code := null;
1576 x_exp_func_raw_cost_rate := null;
1577 x_exp_func_raw_cost := null;
1578 x_exp_func_burdened_cost_rate := null;
1579 x_exp_func_burdened_cost := null;
1580 x_exp_func_cst_rt_date := null;
1581 x_exp_func_cst_rt_type := null;
1582 x_exp_func_cst_exch_rt := null;
1583 x_cst_txn_curr_code := null;
1584 x_txn_raw_cst_rt := null;
1585 x_txn_raw_cst := null;
1586 x_txn_burdned_cst_rt := null;
1587 x_txn_burdned_cst := null;
1588 x_rev_txn_curr_code := null;
1589 x_txn_rev_bill_rt := null;
1590 x_txn_rev_raw_revenue := null;
1591
1592
1593
1594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1595 x_error_msg := SUBSTR(SQLERRM,1,30);
1596 /* Checking error condition. Added for bug 2218386 */
1597 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1598 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
1599 p_procedure_name => 'Get_Item_Amount');
1600 RAISE;
1601 END IF;
1602 END Get_Item_Amount;
1603
1604
1605 -- This procedure will calculate the revenue for fixed price in event based rule on basis of passed parameters
1606 -- Input parameters
1607 -- Parameters Type Required Description
1608 -- P_project_id NUMBER YES Project Id
1609 -- Out parameters
1610 --
1611 -- x_proj_revenue_tab ProjAmt_TabTyp YES It store the amount and period name
1612 -- for fixed price project
1613
1614 PROCEDURE calc_event_based_revenue(
1615 p_project_id IN NUMBER ,
1616 p_rev_amt IN NUMBER,
1617 p_completion_date IN DATE,
1618 p_project_currency_code IN VARCHAR2, -- The following 6
1619 p_projfunc_currency_code IN VARCHAR2,
1620 p_projfunc_bil_rate_date_code IN VARCHAR2, -- columns have been
1621 px_projfunc_bil_rate_type IN OUT NOCOPY VARCHAR2, -- added for MCB2 --File.Sql.39 bug 4440895
1622 px_projfunc_bil_rate_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
1623 px_projfunc_bil_exchange_rate IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1624 x_error_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1625 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
1626 IS
1627
1628 l_completion_date pa_projects_all.completion_date%TYPE;
1629 l_period_type pa_rep_period_dates_v.period_type%TYPE;
1630 l_period_name pa_rep_period_dates_v.period_name%TYPE;
1631 l_start_date pa_rep_period_dates_v.start_date%TYPE;
1632 l_end_date pa_rep_period_dates_v.end_date%TYPE;
1633
1634 /* Added for MCB2 */
1635 l_converted_rev_amount pa_projects_all.project_value%TYPE;
1636 l_conversion_fail EXCEPTION;
1637 l_denominator Number;
1638 l_numerator Number;
1639 l_status Varchar2(30);
1640
1641 l_period_year NUMBER; /* Added for bug 2691192 */
1642 l_error_value VARCHAR2(50); /* Added for bug 2691192 */
1643
1644
1645 lx_projfunc_bil_rate_type VARCHAR2(30);
1646 lx_projfunc_bil_rate_date DATE;
1647 lx_projfunc_bil_exchange_rate NUMBER;
1648
1649
1650 BEGIN
1651
1652 /* ATG Changes */
1653
1654 lx_projfunc_bil_rate_type := px_projfunc_bil_rate_type;
1655 lx_projfunc_bil_rate_date := px_projfunc_bil_rate_date ;
1656 lx_projfunc_bil_exchange_rate := px_projfunc_bil_exchange_rate ;
1657
1658
1659
1660 /* Validating the project end date and project value if the project does not have
1661 end date the taking the max end date of the assignment schedule which belongs to
1662 this project */
1663
1664
1665 IF (p_completion_date IS NULL) THEN /* Bug fix 1842755 */
1666 BEGIN
1667 SELECT MAX(end_date)
1668 INTO l_completion_date
1669 FROM pa_schedules
1670 WHERE project_id = p_project_id
1671 AND DECODE (schedule_type_code, 'OPEN_ASSIGNMENT',
1672 pa_assignment_utils.Is_Asgmt_In_Open_Status(status_code,'OPEN_ASGMT'),
1673 'STAFFED_ASSIGNMENT',DECODE(
1674 pa_assignment_utils.Is_Staffed_Asgmt_Cancelled(status_code,'STAFFED_ASGMT'),'Y','N','Y'),'N') = 'Y'
1675 AND DECODE(schedule_type_code,
1676 'OPEN_ASSIGNMENT', pa_project_utils.check_prj_stus_action_allowed(
1677 status_code, 'OPEN_ASGMT_PROJ_FORECASTING'),
1678 'STAFFED_ASSIGNMENT', pa_project_utils.check_prj_stus_action_allowed(
1679 status_code, 'STAFFED_ASGMT_PROJ_FORECASTING'),
1680 'STAFFED_ADMIN_ASSIGNMENT', pa_project_utils.check_prj_stus_action_allowed(
1681 status_code, 'STAFFED_ASGMT_PROJ_FORECASTING'),'N') = 'Y';
1682 EXCEPTION
1683 WHEN NO_DATA_FOUND THEN
1684 x_error_code := 'PA_FCST_PDS_NOT_DEFINED';
1685 NULL;
1686 END;
1687 ELSE
1688 l_completion_date := p_completion_date;
1689 END IF;
1690
1691 -- dbms_output.put_line(' 11');
1692 -- Populating period type for forecasting it can be GL or PA using profile options
1693 l_period_type := FND_PROFILE.VALUE('PA_FORECASTING_PERIOD_TYPE');
1694
1695 -- dbms_output.put_line(' 12 '||l_period_type);
1696
1697 /* Added code for bug 2691192, this proc. will replace the call of
1698 pa_rep_period_dates_v.Because of performance issue tis view has been split
1699 in this new call of proc. */
1700
1701 PA_RATE_PVT_PKG.get_rep_period_dates (
1702 p_period_type => l_period_type,
1703 p_completion_date => l_completion_date,
1704 x_period_year => l_period_year,
1705 x_period_name => l_period_name,
1706 x_start_date => l_start_date,
1707 x_end_date => l_end_date,
1708 x_error_value => l_error_value
1709 );
1710 -- dbms_output.put_line(' 13 '||l_error_value);
1711
1712 IF ( l_error_value = 'NO_ERROR') THEN
1713 -- Do not raise error
1714 NULL;
1715 ELSIF ( l_error_value = 'NO_DATA_FOUND' ) THEN
1716 RAISE NO_DATA_FOUND;
1717 ELSIF (l_error_value = 'TOO_MANY_ROWS') THEN
1718 RAISE TOO_MANY_ROWS;
1719 END IF;
1720
1721 /* Fix for bug 2691192 till here */
1722
1723 /* Taking period name corresponds to the period type */
1724 /* Commneting out for bug 2691192
1725 SELECT period_name,start_date,end_date
1726 INTO l_period_name,l_start_date,l_end_date
1727 FROM pa_rep_period_dates_v
1728 WHERE period_type = l_period_type
1729 AND l_completion_date BETWEEN start_date AND end_date;
1730 */
1731
1732 /* Commented the below condition for bug 2193832, because even if the
1733 start date of the period is in one particular year, the period year could be previous on or
1734 depending upon how the ct has setup e.g. start date 01-jan-2001, but period year will be 2000
1735
1736 AND TO_CHAR(l_completion_date,'YYYY') = period_year; */
1737
1738
1739
1740 -- NOTE The following conversion is not going to be used in this dev drop
1741 -- because project value will be in PFC
1742
1743 /* Converting the amount into project functional currency for MCB2
1744 IF (p_project_currency_code <> p_projfunc_currency_code ) THEN
1745 PA_MULTI_CURRENCY.convert_amount(
1746 P_FROM_CURRENCY => p_project_currency_code,
1747 P_TO_CURRENCY => p_projfunc_currency_code,
1748 P_CONVERSION_DATE => l_completion_date,
1749 P_CONVERSION_TYPE => px_projfunc_bil_rate_type,
1750 P_AMOUNT => p_rev_amt,
1751 P_USER_VALIDATE_FLAG => 'Y',
1752 P_HANDLE_EXCEPTION_FLAG => 'Y',
1753 P_CONVERTED_AMOUNT => l_converted_rev_amount,
1754 P_DENOMINATOR => l_denominator,
1755 P_NUMERATOR => l_numerator,
1756 P_RATE => px_projfunc_bil_exchange_rate,
1757 X_STATUS => l_status);
1758
1759 IF (l_status IS NOT NULL) THEN
1760 RAISE l_conversion_fail;
1761 END IF;
1762
1763 x_projfunc_revenue_tab(1).amount := l_converted_rev_amount;
1764
1765 END IF; */
1766
1767 x_projfunc_revenue_tab(1).amount := p_rev_amt;
1768 x_projfunc_revenue_tab(1).period_name := l_period_name;
1769 x_projfunc_revenue_tab(1).start_date := l_start_date;
1770 x_projfunc_revenue_tab(1).end_date := l_end_date;
1771 EXCEPTION
1772 WHEN NO_DATA_FOUND THEN
1773 x_error_code := 'PA_FCST_PDS_NOT_DEFINED';
1774 NULL;
1775 WHEN l_conversion_fail THEN
1776 x_error_code := l_status||'_BC_PF';
1777 WHEN OTHERS THEN
1778
1779 /* ATG Changes */
1780
1781 px_projfunc_bil_rate_type := lx_projfunc_bil_rate_type;
1782 px_projfunc_bil_rate_date := lx_projfunc_bil_rate_date ;
1783 px_projfunc_bil_exchange_rate := lx_projfunc_bil_exchange_rate ;
1784
1785 /* Checking error condition. Added for bug 2218386 */
1786 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1787 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
1788 p_procedure_name => 'calc_event_based_revenue');
1789 RAISE;
1790 END IF;
1791
1792 END calc_event_based_revenue;
1793
1794
1795 -- This procedure will calculate the revenue for fixed price in cost based rule on basis of passed parameters
1796 -- Input parameters
1797 -- Parameters Type Required Description
1798 -- P_project_id NUMBER YES Project Id
1799 -- p_rev_amt NUMBER YES Revenue amount for project
1800 -- p_proj_cost_tab ProjAmt_TabTyp YES It contains the amount and period name
1801 -- for fixed price project
1802 -- Out parameters
1803 --
1804 -- x_proj_revenue_tab ProjAmt_TabTyp YES It stores the amount and period name
1805 -- for fixed price project
1806
1807 PROCEDURE calc_cost_based_revenue(
1808 p_project_id IN NUMBER ,
1809 p_rev_amt IN NUMBER ,
1810 p_projfunc_cost_tab IN PA_RATE_PVT_PKG.ProjAmt_TabTyp,
1811 p_project_currency_code IN VARCHAR2, -- The following 6
1812 p_projfunc_currency_code IN VARCHAR2,
1813 p_projfunc_bil_rate_date_code IN VARCHAR2, -- columns have been
1814 px_projfunc_bil_rate_type IN OUT NOCOPY VARCHAR2, -- added for MCB2 --File.Sql.39 bug 4440895
1815 px_projfunc_bil_rate_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
1816 px_projfunc_bil_exchange_rate IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1817 x_projfunc_revenue_tab OUT NOCOPY PA_RATE_PVT_PKG.ProjAmt_TabTyp, --File.Sql.39 bug 4440895
1818 x_error_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1819 )
1820 IS
1821
1822 l_proj_rev NUMBER;
1823 l_period_rev NUMBER;
1824 l_tot_period_rev NUMBER;
1825 l_last_num NUMBER;
1826 l_proj_cost NUMBER;
1827 l_running_cost NUMBER:=0;
1828
1829 /* Added for MCB2 */
1830 l_converted_rev_amount pa_projects_all.project_value%TYPE;
1831 l_conversion_fail EXCEPTION;
1832 l_denominator Number;
1833 l_numerator Number;
1834 l_status Varchar2(30);
1835 l_conversion_date DATE;
1836
1837 lx_projfunc_bil_rate_type VARCHAR2(30);
1838 lx_projfunc_bil_rate_date DATE;
1839 lx_projfunc_bil_exchange_rate NUMBER;
1840
1841
1842 BEGIN
1843
1844 /* ATG Changes */
1845
1846 lx_projfunc_bil_rate_type := px_projfunc_bil_rate_type;
1847 lx_projfunc_bil_rate_date := px_projfunc_bil_rate_date ;
1848 lx_projfunc_bil_exchange_rate := px_projfunc_bil_exchange_rate ;
1849
1850
1851 /* Checking that the passed table of records should have some value */
1852 IF (p_projfunc_cost_tab.count <> 0 ) THEN
1853
1854 l_proj_rev := NVL(p_rev_amt,0);
1855 l_proj_cost := 0;
1856
1857 -- NOTE The following conversion is not going to be used in this dev drop
1858 -- because project value will be in PFC
1859 /* Converting the amount into project functional currency for MCB2
1860 IF ( p_project_currency_code <> p_projfunc_currency_code ) THEN
1861 l_conversion_date := p_projfunc_cost_tab(1).start_date;
1862 PA_MULTI_CURRENCY.convert_amount(
1863 P_FROM_CURRENCY => p_project_currency_code,
1864 P_TO_CURRENCY => p_projfunc_currency_code,
1865 P_CONVERSION_DATE => l_conversion_date,
1866 P_CONVERSION_TYPE => px_projfunc_bil_rate_type,
1867 P_AMOUNT => p_rev_amt,
1868 P_USER_VALIDATE_FLAG => 'Y',
1869 P_HANDLE_EXCEPTION_FLAG => 'Y',
1870 P_CONVERTED_AMOUNT => l_converted_rev_amount,
1871 P_DENOMINATOR => l_denominator,
1872 P_NUMERATOR => l_numerator,
1873 P_RATE => px_projfunc_bil_exchange_rate,
1874 X_STATUS => l_status);
1875
1876 IF (l_status IS NOT NULL) THEN
1877 RAISE l_conversion_fail;
1878 END IF;
1879 l_proj_rev := NVL(l_converted_rev_amount,0);
1880 l_proj_cost := 0;
1881 END IF; */
1882
1883 -- Taking the total cost for the project by summing up all the amount for all periods
1884 -- l_last_num := p_proj_cost_tab.last;
1885 FOR j IN p_projfunc_cost_tab.first..p_projfunc_cost_tab.last LOOP
1886 l_proj_cost := l_proj_cost + NVL(p_projfunc_cost_tab(j).amount,0);
1887 END LOOP;
1888
1889 -- Initializing the local variable
1890 l_period_rev := 0;
1891 l_tot_period_rev := 0;
1892
1893 /* Calculating the revenue period wise */
1894 IF l_proj_cost <> 0 THEN
1895 FOR i IN p_projfunc_cost_tab.first..p_projfunc_cost_tab.last LOOP
1896 IF ( I = p_projfunc_cost_tab.last ) THEN
1897 l_period_rev := NVL(l_proj_rev,0) - NVL(l_tot_period_rev,0);
1898 x_projfunc_revenue_tab(i).amount := NVL(l_period_rev,0);
1899 x_projfunc_revenue_tab(i).period_name := p_projfunc_cost_tab(i).period_name;
1900 ELSE
1901 l_running_cost := NVL(l_running_cost,0) + NVL(p_projfunc_cost_tab(i).amount,0);
1902 l_period_rev := ((NVL(l_running_cost,0)/ NVL(l_proj_cost,0)) * NVL(l_proj_rev,0)) -NVL(l_tot_period_rev,0);
1903 x_projfunc_revenue_tab(i).amount := l_period_rev;
1904 x_projfunc_revenue_tab(i).period_name := p_projfunc_cost_tab(i).period_name;
1905 l_tot_period_rev := l_tot_period_rev + l_period_rev ;
1906 END IF;
1907 END LOOP;
1908 END IF; /* Closing if for l_proj_cost <> */
1909 END IF; /* Closing if for p_proj_cost_tab validation */
1910 EXCEPTION
1911 WHEN l_conversion_fail THEN
1912 x_error_code := l_status||'_BC_PF';
1913 NULL;
1914 WHEN OTHERS THEN
1915 /* ATG Changes */
1916
1917 px_projfunc_bil_rate_type := lx_projfunc_bil_rate_type;
1918 px_projfunc_bil_rate_date := lx_projfunc_bil_rate_date ;
1919 px_projfunc_bil_exchange_rate := lx_projfunc_bil_exchange_rate ;
1920
1921 /* Checking error condition. Added for bug 2218386 */
1922 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1923 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
1924 p_procedure_name => 'calc_cost_based_revenue');
1925 RAISE;
1926 END IF;
1927
1928 END calc_cost_based_revenue;
1929
1930
1931 -- This procedure will return that whta type of the project is this on the basis of passed parameters
1932 -- Input parameters
1933 -- Parameters Type Required Description
1934 -- P_project_id NUMBER NO Project Id
1935 -- p_distribution_rule NUMBER NO distribution rule
1936 -- Out parameters
1937 --
1938
1939 PROCEDURE get_revenue_generation_method( p_project_id IN NUMBER DEFAULT NULL,
1940 p_distribution_rule IN VARCHAR2 DEFAULT NULL,
1941 x_rev_gen_method OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1942 x_error_msg OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1943 IS
1944 l_rule pa_projects_all.distribution_rule%TYPE; -- Used to store the distribution rule for
1945 -- further result e.g if rule starts with work
1946 -- then project is T and M else Fixed price
1947 l_proj_typ VARCHAR2(1); -- Used to store type of the project for return
1948 -- statement i.e. if 'T' - 'T and M' ,'C' - 'Cost based'
1949 -- and if 'E' - ' Event based'
1950
1951 l_class_code pa_project_types_all.project_type_class_code%TYPE;
1952 l_no_rule EXCEPTION;
1953
1954 BEGIN
1955
1956 /*
1957 IF( p_distribution_rule IS NULL) THEN
1958 ELSE
1959 END IF ;
1960 l_rule := p_distribution_rule;
1961 */
1962 /* Selecting distribution rule for checking wheather the project is Fixed Price or T and M */
1963 BEGIN
1964 SELECT proj.distribution_rule,typ.project_type_class_code
1965 INTO l_rule,l_class_code
1966 FROM pa_project_types_all typ, pa_projects_all proj
1967 WHERE proj.project_id = p_project_id
1968 AND proj.project_type = typ.project_type
1969 AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul : removed NVL function
1970
1971 IF ( l_class_code = 'CONTRACT') THEN
1972 IF ( l_rule IS NULL ) THEN
1973 RAISE l_no_rule;
1974 END IF;
1975 END IF;
1976 END;
1977
1978 --DBMS_OUTPUT.PUT_LINE('2');
1979 /* Checking wheather the project type is T and M or Cost based or Event based */
1980 IF ( l_class_code = 'CONTRACT') THEN
1981 IF (l_rule IS NOT NULL ) THEN
1982 IF ( l_rule = 'WORK/WORK') OR ( l_rule = 'WORK/EVENT') THEN
1983 x_rev_gen_method := 'T';
1984 ELSIF (l_rule = 'COST/COST') OR ( l_rule = 'COST/EVENT')OR (l_rule = 'COST/WORK') THEN
1985 x_rev_gen_method := 'C';
1986 ELSIF (l_rule = 'EVENT/EVENT') OR (l_rule = 'EVENT/WORK') THEN
1987 x_rev_gen_method := 'E';
1988 END IF;
1989 END IF;
1990 ELSE
1991 x_rev_gen_method := 'N';
1992 END IF;
1993 EXCEPTION
1994 WHEN l_no_rule THEN
1995 x_rev_gen_method := 'N';
1996 x_error_msg := 'PA_FCST_DIST_RULE_NOT_FOUND';
1997 WHEN OTHERS THEN
1998
1999 /* ATG Changes */
2000 x_rev_gen_method := null;
2001
2002
2003 /* Checking error condition. Added for bug 2218386 */
2004 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2005 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
2006 p_procedure_name => 'get_revenue_generation_method');
2007 RAISE;
2008 END IF;
2009
2010 END get_revenue_generation_method;
2011
2012
2013
2014 -- This procedure will calculate the initial bill rate for Assignment and Requirement
2015 -- Input parameters
2016 -- Parameters Type Required Description
2017 -- p_assignment_type VARCHAR2 YES Type of assignment like REQUIREMENT(R)/ASSIGNMENT(A)
2018 -- p_asgn_start_date DATE YES Rate calculation date
2019 -- P_assignment_id NUMBER YES Unique identifier
2020 -- P_project_id NUMBER YES Project Id
2021 -- P_quantity NUMBER YES Quantity in Hours
2022 -- P_forecast_job_id NUMBER NO Forecast job Id at assignment level
2023 -- P_forecast_job_group_id NUMBER NO Forecast job group id at assignment level
2024 -- p_person_id NUMBER NO Person id
2025 -- p_expenditure_org_id NUMBER NO Expenditure org id
2026 -- P_expenditure_type VARCHAR2 NO Expenditure Type
2027 -- p_expenditure_organization_id NUMBER NO Expenditure organization id
2028 -- p_project_org_id NUMBER NO Project org id
2029 -- p_expenditure_currency_code VARCHAR2 NO Expenditure functional currency code
2030 -- P_project_type VARCHAR2 NO Project Type
2031 -- P_task_id NUMBER NO Task Id for the given project
2032 -- p_projfunc_currency_code VARCHAR2 NO Project Functional currency code
2033 -- P_bill_rate_multiplier NUMBER NO Bill rate multiplier for calculating the revenue
2034 -- P_project_bill_job_group_id NUMBER NO Billing job group id for project
2035 -- p_emp_bill_rate_schedule_id NUMBER NO Employee bill rate schedule id
2036 -- P_job_bill_rate_schedule_id NUMBER NO Job bill rate schedule id
2037 -- and rate
2038 --
2039 -- Out parameters
2040 --
2041 -- x_projfunc_bill_rate NUMBER YES Bill rate in project currency
2042 -- x_projfunc_raw_revenue NUMBER YES Raw revenue in project currency
2043 -- x_rev_currency_code VARCHAR2 YES Revenue currency code
2044
2045 PROCEDURE get_initial_bill_rate(
2046 p_assignment_type IN VARCHAR2 ,
2047 p_asgn_start_date IN DATE ,
2048 p_project_id IN NUMBER ,
2049 p_quantity IN NUMBER ,
2050 p_expenditure_org_id IN NUMBER ,
2051 p_expenditure_type IN VARCHAR2 ,
2052 p_expenditure_organization_id IN NUMBER ,
2053 p_person_id IN NUMBER DEFAULT NULL,
2054 p_assignment_id IN NUMBER DEFAULT NULL,
2055 p_forecast_job_id IN NUMBER DEFAULT NULL,
2056 p_forecast_job_group_id IN NUMBER DEFAULT NULL,
2057 p_project_org_id IN NUMBER DEFAULT NULL,
2058 p_expenditure_currency_code IN VARCHAR2 DEFAULT NULL,
2059 p_project_type IN VARCHAR2 DEFAULT NULL,
2060 p_task_id IN NUMBER DEFAULT NULL,
2061 p_bill_rate_multiplier IN NUMBER DEFAULT NULL,
2062 p_project_bill_job_group_id IN NUMBER DEFAULT NULL,
2063 p_emp_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
2064 p_job_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
2065 p_job_cost_rate_schedule_id IN NUMBER DEFAULT NULL,
2066 p_proj_cost_job_group_id IN NUMBER DEFAULT NULL,
2067 p_calculate_cost_flag IN VARCHAR2 DEFAULT 'Y', /* Added to fix bug 2162965 */
2068 p_forecast_item_id IN NUMBER DEFAULT NULL, /* Added para for bug 2212852 */
2069 p_forecasting_type IN VARCHAR2 DEFAULT 'PROJECT_FORECASTING', /* added para for bug 2212852 */
2070 p_assign_precedes_task IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2071 p_system_linkage IN pa_expenditure_items_all.system_linkage_function%TYPE DEFAULT NULL,/* Added */
2072 /* for Org Forecasting */
2073 p_labor_schdl_discnt IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
2074 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
2075 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2076 p_labor_schedule_fixed_date IN DATE DEFAULT NULL, /* Added for Org Forecasting */
2077 p_labor_sch_type IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2078 p_projfunc_currency_code IN VARCHAR2 DEFAULT NULL,
2079 p_projfunc_rev_rt_dt_code IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2080 p_projfunc_rev_rt_date IN DATE DEFAULT NULL, /* Added for Org Forecasting */
2081 p_projfunc_rev_rt_type IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2082 p_projfunc_rev_exch_rt IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
2083 p_projfunc_cst_rt_date IN DATE DEFAULT NULL, /* Added for Org Forecasting */
2084 p_projfunc_cst_rt_type IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
2085 p_project_currency_code IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
2086 p_project_rev_rt_dt_code IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
2087 p_project_rev_rt_date IN DATE DEFAULT NULL, /* Added for org Forecasting */
2088 p_project_rev_rt_type IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
2089 p_project_rev_exch_rt IN NUMBER DEFAULT NULL, /* Added for org Forecasting */
2090 p_project_cst_rt_date IN DATE DEFAULT NULL, /* Added for org Forecasting */
2091 p_project_cst_rt_type IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
2092 x_projfunc_bill_rate OUT NOCOPY NUMBER /* Changed for MCb2 */, --File.Sql.39 bug 4440895
2093 x_projfunc_raw_revenue OUT NOCOPY NUMBER /* Changed for MCb2 */, --File.Sql.39 bug 4440895
2094 x_rev_currency_code OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2095 x_markup_percentage OUT NOCOPY NUMBER /* Added for Assignment Override */, --File.Sql.39 bug 4440895
2096 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2097 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2098 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2099 IS
2100
2101 l_insufficient_parameters EXCEPTION;
2102 l_job_not_found EXCEPTION;
2103 l_no_rule EXCEPTION;
2104
2105 l_calculate_cost_flag VARCHAR2(1); /* Added to fix bug 2162965 */
2106
2107 l_forecast_job_id pa_project_assignments.fcst_job_id%TYPE;
2108 l_forecast_job_group_id pa_project_assignments.fcst_job_group_id%TYPE;
2109
2110 l_labor_cost_mult_name pa_tasks.labor_cost_multiplier_name%TYPE;
2111
2112
2113
2114 l_project_type pa_project_types_all.project_type%TYPE;
2115 l_proj_cost_job_grp_id pa_std_bill_rate_schedules_all.job_group_id%TYPE;
2116
2117 l_project_org_id pa_projects_all.org_id%TYPE;
2118 l_project_bill_job_group_id pa_projects_all.bill_job_group_id%TYPE;
2119 l_emp_bill_rate_schedule_id pa_projects_all.emp_bill_rate_schedule_id%TYPE;
2120 l_job_bill_rate_schedule_id pa_projects_all.job_bill_rate_schedule_id%TYPE;
2121
2122
2123 l_labor_schedule_fixed_date pa_projects_all.labor_schedule_fixed_date%TYPE;
2124 l_labor_schedule_discount NUMBER;
2125 l_labor_bill_rate_org_id NUMBER;
2126 l_labor_std_bill_rate_schedule pa_projects_all.labor_std_bill_rate_schdl%TYPE;
2127 l_labor_schedule_type pa_projects_all.labor_sch_type%TYPE;
2128
2129 l_raw_cost_rate NUMBER;
2130 l_raw_cost NUMBER;
2131
2132 l_x_return_status VARCHAR2(50);
2133 l_proj_bill_job_id NUMBER;
2134 l_overr_to_organization_id NUMBER;
2135 l_job_cost_rate_schedule_id pa_forecasting_options.job_cost_rate_schedule_id%TYPE;
2136 l_distribution_rule pa_projects_all.distribution_rule%TYPE;
2137
2138
2139 l_proj_cost_job_id NUMBER;
2140 l_cost_rate_multiplier NUMBER;
2141 l_class_code pa_project_types_all.project_type_class_code%TYPE;
2142
2143 l_schedule_type VARCHAR2(50);
2144
2145 l_expenditure_currency_code gl_sets_of_books.currency_code%TYPE;
2146 l_expenditure_curr_code_burdn gl_sets_of_books.currency_code%TYPE; /* Added for Org Forecasting */
2147 l_exp_func_cst_rt_date DATE; /* Added for Org Forecasting */
2148 l_exp_func_cst_rt_type PA_IMPLEMENTATIONS_ALL.default_rate_type%TYPE; /* Added for Org Forecasting */
2149 l_exp_func_cst_exch_rt NUMBER; /* Added for Org Forecasting */
2150 l_exp_func_raw_cost_rate NUMBER;
2151 l_exp_func_raw_cost NUMBER;
2152 l_exp_func_burdened_cost_rate NUMBER;
2153 l_exp_func_burdened_cost NUMBER;
2154
2155 /* Added for MCB2 */
2156 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
2157 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
2158 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
2159 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
2160 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
2161 l_projfunc_cost_rate_type pa_projects_all.projfunc_cost_rate_type%TYPE;
2162 l_projfunc_cost_rate_date pa_projects_all.projfunc_cost_rate_DATE%TYPE;
2163 l_projfunc_cost_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
2164 l_markup_percentage pa_bill_rates_all.markup_percentage%TYPE; /* Added for Asgmt overide */
2165 l_assignment_precedes_task pa_projects_all.assign_precedes_task%TYPE; /* Added for Asgmt overide */
2166 /* Till here for mcb 2 */
2167
2168 /* Added for Org Foreasting */
2169 l_projfunc_bill_rate NUMBER;
2170 l_projfunc_raw_revenue NUMBER;
2171 l_projfunc_raw_cost NUMBER;
2172 l_projfunc_raw_cost_rate NUMBER;
2173 l_projfunc_burdened_cost NUMBER;
2174 l_projfunc_burdened_cost_rate NUMBER;
2175
2176 l_amount_calc_mode VARCHAR2(50);
2177
2178 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
2179 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
2180 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
2181 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
2182 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
2183 l_project_cost_rate_type pa_projects_all.project_rate_type%TYPE;
2184 l_project_cost_rate_date pa_projects_all.project_rate_DATE%TYPE;
2185 l_project_cost_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
2186 l_project_bill_rate NUMBER;
2187 l_project_raw_revenue NUMBER;
2188 l_project_raw_cost NUMBER;
2189 l_project_raw_cost_rate NUMBER;
2190 l_project_burdened_cost NUMBER;
2191 l_project_burdened_cost_rate NUMBER;
2192
2193 l_cst_txn_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
2194 l_txn_raw_cst_rt NUMBER;
2195 l_txn_raw_cst NUMBER;
2196 l_txn_burdned_cst_rt NUMBER;
2197 l_txn_burdned_cst NUMBER;
2198
2199 l_rev_txn_curr_code PA_BILL_RATES_ALL.rate_currency_code%TYPE;
2200 l_txn_rev_bill_rt NUMBER;
2201
2202 l_txn_adjusted_bill_rt NUMBER;-- 4038485
2203 l_txn_rev_raw_revenue NUMBER;
2204
2205 l_system_linkage pa_expenditure_items_all.system_linkage_function%TYPE;
2206 /* Till here for Org */
2207
2208
2209 BEGIN
2210
2211 IF g1_debug_mode = 'Y' THEN
2212 PA_DEBUG.Set_Curr_Function( p_function => 'Get Initial Bill Rate');
2213 PA_DEBUG.g_err_stage := 'RT50 : Before Validation PA_RATE_PVT_PKG.get_initial_bill_rate';
2214 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2215 END IF;
2216
2217 /* Validating that the required parameters should not be null */
2218 IF ( p_assignment_type IS NULL) OR (p_asgn_start_date IS NULL ) OR (p_project_id IS NULL)
2219 OR (p_quantity IS NULL) OR (p_expenditure_type IS NULL )
2220 /* OR (p_expenditure_organization_id IS NULL) OR (p_expenditure_org_id IS NULL) commented because
2221 null condition is taken care in lower api */
2222 THEN
2223 RAISE l_insufficient_parameters;
2224 END IF;
2225
2226
2227 /* Validating that the required parameters should not be null */
2228 IF ( p_assignment_type = 'A') THEN
2229 IF (p_person_id IS NULL) THEN
2230 RAISE l_insufficient_parameters;
2231 END IF;
2232 END IF;
2233
2234 /* Selecting expenditure org id , type ,organization id , forecast job id and forecast job group
2235 id from project assignments table only if the passed value is null otherwise storing passed
2236 values */
2237 IF (p_assignment_type = 'R') THEN
2238 IF (p_forecast_job_id IS NULL) OR (p_forecast_job_group_id IS NULL) THEN
2239 RAISE l_job_not_found;
2240 ELSE
2241 l_forecast_job_id := p_forecast_job_id;
2242 l_forecast_job_group_id := p_forecast_job_group_id;
2243 END IF;
2244 ELSIF ( p_assignment_type = 'A') THEN
2245 SELECT job_id
2246 INTO l_forecast_job_id
2247 FROM pa_resources_denorm
2248 WHERE person_id = p_person_id
2249 AND ( p_asgn_start_date BETWEEN TRUNC(resource_effective_start_date) AND
2250 NVL(TRUNC(resource_effective_end_date),p_asgn_start_date));
2251
2252 END IF;
2253
2254 /* Selecting distribution rule for calculation rate */
2255 BEGIN
2256 SELECT proj.distribution_rule,typ.project_type_class_code,proj.labor_sch_type
2257 INTO l_distribution_rule,l_class_code,l_labor_schedule_type
2258 FROM pa_project_types_all typ, pa_projects_all proj
2259 WHERE proj.project_id = p_project_id
2260 AND proj.project_type = typ.project_type
2261 AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul : removed NVL function
2262
2263 IF ( l_class_code = 'CONTRACT') THEN
2264 IF ( l_distribution_rule IS NULL) THEN
2265 RAISE l_no_rule;
2266 END IF;
2267 END IF;
2268 EXCEPTION
2269 WHEN l_no_rule THEN
2270 NULL;
2271 WHEN NO_DATA_FOUND THEN
2272 NULL;
2273 END;
2274
2275 /* Selecting expenditure currency code from project set of books and implementations table
2276 only if the passed value is null otherwise storing passed values */
2277 IF ( p_expenditure_currency_code IS NULL) THEN
2278 BEGIN
2279 SELECT glsb.currency_code
2280 INTO l_expenditure_currency_code
2281 FROM gl_sets_of_books glsb, pa_implementations_all paimp
2282 WHERE glsb.set_of_books_id = paimp.set_of_books_id
2283 AND paimp.org_id = p_expenditure_org_id; -- bug 7413961 skkoppul: removed NVL function
2284 END;
2285 ELSE
2286 l_expenditure_currency_code := p_expenditure_currency_code;
2287 END IF;
2288
2289 l_expenditure_curr_code_burdn := l_expenditure_currency_code; /* Made for Org Forecasting */
2290
2291 /* Selecting labor cost mult name from tasks table only if the passed value is null and task id
2292 is not null otherwise storing passed values */
2293 IF ( p_task_id IS NOT NULL ) THEN
2294 BEGIN
2295 SELECT labor_cost_multiplier_name
2296 INTO l_labor_cost_mult_name
2297 FROM pa_tasks
2298 WHERE task_id = p_task_id;
2299 END;
2300 END IF;
2301
2302 /* Selecting project type from project types table only if the
2303 passed value is null otherwise storing passed values */
2304 IF ( p_project_type IS NULL) THEN
2305
2306 SELECT typ.project_type
2307 INTO l_project_type
2308 FROM pa_project_types_all typ, pa_projects_all proj
2309 WHERE proj.project_id = p_project_id
2310 AND proj.project_type = typ.project_type
2311 AND proj.org_id = typ.org_id; -- bug 7413961 skkoppul: removed NVL function
2312
2313 ELSE
2314 l_project_type := p_project_type;
2315 END IF;
2316
2317
2318 /* Selecting project org id, project currency code, project bill job
2319 group id, employee bill rate schedule id and job bill rate schedule id from project all table
2320 only if the passed value is null otherwise storing passed values */
2321 IF (p_project_org_id IS NULL) OR (p_project_bill_job_group_id IS NULL) OR
2322 (p_emp_bill_rate_schedule_id IS NULL) OR (p_job_bill_rate_schedule_id IS NULL)
2323 OR (p_labor_schedule_fixed_date IS NULL) THEN
2324 BEGIN
2325 SELECT NVL(org_id,-99), bill_job_group_id,
2326 emp_bill_rate_schedule_id,job_bill_rate_schedule_id,
2327 labor_schedule_fixed_date,
2328 projfunc_currency_code,
2329 projfunc_bil_rate_date_code, /* Added the following column for MCB2 */
2330 projfunc_bil_rate_type,
2331 projfunc_bil_rate_date,
2332 projfunc_bil_exchange_rate,
2333 projfunc_cost_rate_date,
2334 projfunc_cost_rate_type,
2335 NVL(assign_precedes_task,'1'),/* Added for Asgmt overide */
2336 project_currency_code, /* Added for Org Forecasting */
2337 project_bil_rate_date_code, /* Added for Org Forecasting */
2338 project_bil_rate_type, /* Added for Org Forecasting */
2339 project_bil_rate_date, /* Added for Org Forecasting */
2340 project_bil_exchange_rate, /* Added for Org Forecasting */
2341 project_rate_date, /* Added for Org Forecasting */
2342 project_rate_type, /* Added for Org Forecasting */
2343 labor_schedule_discount, /* Added for Org Forecasting */
2344 labor_bill_rate_org_id, /* Added for Org Forecasting */
2345 labor_std_bill_rate_schdl, /* Added for Org Forecasting */
2346 labor_schedule_fixed_date, /* Added for Org Forecasting */
2347 labor_sch_type /* Added for Org Forecasting */
2348 INTO l_project_org_id,l_project_bill_job_group_id,
2349 l_emp_bill_rate_schedule_id,l_job_bill_rate_schedule_id ,
2350 l_labor_schedule_fixed_date,
2351 l_projfunc_currency_code,
2352 l_projfunc_bil_rate_date_code, /* Added the following columns for MCB2 */
2353 l_projfunc_bil_rate_type,
2354 l_projfunc_bil_rate_date,
2355 l_projfunc_bil_exchange_rate,
2356 l_projfunc_cost_rate_date,
2357 l_projfunc_cost_rate_type,
2358 l_assignment_precedes_task,
2359 l_project_currency_code,
2360 l_project_bil_rate_date_code,
2361 l_project_bil_rate_type,
2362 l_project_bil_rate_date,
2363 l_project_bil_exchange_rate,
2364 l_project_cost_rate_date,
2365 l_project_cost_rate_type,
2366 l_labor_schedule_discount,
2367 l_labor_bill_rate_org_id,
2368 l_labor_std_bill_rate_schedule,
2369 l_labor_schedule_fixed_date,
2370 l_labor_schedule_type
2371 FROM pa_projects_all
2372 WHERE project_id = p_project_id;
2373 END;
2374 ELSE
2375 IF p_project_org_id IS NOT NULL THEN
2376 l_project_org_id := p_project_org_id;
2377 END IF;
2378 IF p_project_bill_job_group_id IS NOT NULL THEN
2379 l_project_bill_job_group_id := p_project_bill_job_group_id;
2380 END IF;
2381 IF p_emp_bill_rate_schedule_id IS NOT NULL THEN
2382 l_emp_bill_rate_schedule_id := p_emp_bill_rate_schedule_id;
2383 END IF;
2384 IF p_job_bill_rate_schedule_id IS NOT NULL THEN
2385 l_job_bill_rate_schedule_id := p_job_bill_rate_schedule_id;
2386 END IF;
2387
2388 /* Added for Org Forecasting */
2389 IF p_labor_schdl_discnt IS NOT NULL THEN
2390 l_labor_schedule_discount := p_labor_schdl_discnt;
2391 END IF;
2392
2393 IF p_labor_bill_rate_org_id IS NOT NULL THEN
2394 l_labor_bill_rate_org_id := p_labor_bill_rate_org_id;
2395 END IF;
2396
2397 IF p_labor_std_bill_rate_schdl IS NOT NULL THEN
2398 l_labor_std_bill_rate_schedule := p_labor_std_bill_rate_schdl;
2399 END IF;
2400
2401 IF p_labor_schedule_fixed_date IS NOT NULL THEN
2402 l_labor_schedule_fixed_date := p_labor_schedule_fixed_date;
2403 END IF;
2404
2405 IF p_labor_sch_type IS NOT NULL THEN
2406 l_labor_schedule_type := p_labor_sch_type;
2407 END IF;
2408
2409 IF p_projfunc_rev_rt_date IS NOT NULL THEN
2410 l_projfunc_bil_rate_date := p_projfunc_rev_rt_date;
2411 END IF;
2412
2413 IF p_projfunc_rev_rt_type IS NOT NULL THEN
2414 l_projfunc_bil_rate_type := p_projfunc_rev_rt_type;
2415 END IF;
2416
2417 IF p_projfunc_rev_exch_rt IS NOT NULL THEN
2418 l_projfunc_bil_exchange_rate := p_projfunc_rev_exch_rt;
2419 END IF;
2420
2421 IF p_projfunc_cst_rt_date IS NOT NULL THEN
2422 l_projfunc_cost_rate_date := p_projfunc_cst_rt_date;
2423 END IF;
2424
2425 IF p_projfunc_cst_rt_type IS NOT NULL THEN
2426 l_projfunc_cost_rate_type := p_projfunc_cst_rt_type;
2427 END IF;
2428
2429 IF p_project_currency_code IS NOT NULL THEN
2430 l_project_currency_code := p_project_currency_code;
2431 END IF;
2432
2433 IF p_project_rev_rt_date IS NOT NULL THEN
2434 l_project_bil_rate_date := p_project_rev_rt_date;
2435 END IF;
2436
2437 IF p_project_rev_rt_type IS NOT NULL THEN
2438 l_project_bil_rate_type := p_project_rev_rt_type;
2439 END IF;
2440
2441 IF p_project_rev_exch_rt IS NOT NULL THEN
2442 l_project_bil_exchange_rate := p_project_rev_exch_rt;
2443 END IF;
2444
2445 IF p_project_cst_rt_date IS NOT NULL THEN
2446 l_project_cost_rate_date := p_project_cst_rt_date;
2447 END IF;
2448
2449 IF p_project_cst_rt_type IS NOT NULL THEN
2450 l_project_cost_rate_type := p_project_cst_rt_type;
2451 END IF;
2452
2453
2454 END IF;
2455
2456 IF (p_system_linkage IS NULL ) THEN
2457 /* Added for Org_forecasting */
2458 SELECT default_assign_exp_type_class
2459 INTO l_system_linkage
2460 FROM pa_forecasting_options_all
2461 WHERE NVL(org_id,-99) = nvl(l_project_org_id,-99);
2462 ELSE
2463 l_system_linkage := p_system_linkage;
2464 END IF;
2465
2466 /* Selecting project cost job group id,job cost rate schedule id from forecasting options and
2467 pa std billrate table only if the passed value is null otherwise storing passed values */
2468
2469 IF ( p_proj_cost_job_group_id IS NULL) OR ( p_job_cost_rate_schedule_id IS NULL) THEN
2470 SELECT bschal.job_group_id,foptal.job_cost_rate_schedule_id
2471 INTO l_proj_cost_job_grp_id,l_job_cost_rate_schedule_id
2472 FROM pa_std_bill_rate_schedules_all bschal,pa_forecasting_options_all foptal
2473 WHERE bschal.bill_rate_sch_id = foptal.job_cost_rate_schedule_id
2474 /* For bug 4101595: Reverted the fix done for bug 3786192 */
2475 /* AND nvl(foptal.org_id, -99) = nvl(p_expenditure_org_id, -99) */ /* Added for 3786192 */
2476 AND bschal.org_id = l_project_org_id; -- bug 7413961 skkoppul: removed NVL function
2477
2478 ELSE
2479 l_proj_cost_job_grp_id := p_proj_cost_job_group_id;
2480 l_job_cost_rate_schedule_id := p_job_cost_rate_schedule_id;
2481 END IF;
2482
2483 /* commented for Org Forecasting
2484 IF (l_labor_schedule_fixed_date IS NULL) OR (l_projfunc_currency_code IS NULL) THEN
2485 BEGIN
2486 SELECT labor_schedule_fixed_date,
2487 projfunc_currency_code,projfunc_bil_rate_date_code, -- Added the following column for MCB2
2488 projfunc_bil_rate_type,projfunc_bil_rate_date,projfunc_bil_exchange_rate,
2489 projfunc_cost_rate_date,projfunc_cost_rate_type,
2490 NVL(assign_precedes_task,'1'), -- Added for Asgmt overide
2491 labor_sch_type
2492 INTO l_labor_schedule_fixed_date,
2493 l_projfunc_currency_code,l_projfunc_bil_rate_date_code, -- Added the following columns for MCB2
2494 l_projfunc_bil_rate_type,l_projfunc_bil_rate_date,l_projfunc_bil_exchange_rate,
2495 l_projfunc_cost_rate_date,l_projfunc_cost_rate_type,
2496 l_assignment_precedes_task,
2497 l_labor_schedule_type
2498 FROM pa_projects_all
2499 WHERE project_id = p_project_id;
2500 END;
2501 END IF;
2502 */
2503
2504 /* Added to fix bug 2162965, if the assignment api is calling this api then, we do not need to
2505 calculate the cost rate, else we do */
2506 IF (l_labor_schedule_type = 'I') THEN
2507 l_calculate_cost_flag := 'Y';
2508 ELSE
2509 l_calculate_cost_flag := p_calculate_cost_flag;
2510 END IF;
2511
2512 IF g1_debug_mode = 'Y' THEN
2513 PA_DEBUG.g_err_stage := 'RT51 : After Validation PA_RATE_PVT_PKG.get_initial_bill_rate';
2514 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2515 END IF;
2516 /* Calling the rate calculation APIs */
2517
2518 --------------------------------------------
2519 -- Initialize the successful return status
2520 --------------------------------------------
2521
2522 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2523 l_Schedule_type := 'COST';
2524
2525 IF (l_calculate_cost_flag = 'Y') THEN /* Added this if to fix bug 2162965 */
2526 IF ( p_assignment_type = 'A') THEN
2527 IF g1_debug_mode = 'Y' THEN
2528 PA_DEBUG.g_err_stage := 'RT52 : Entering PA_COST.get_raw_cost';
2529 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2530 END IF;
2531
2532 /* 12.2 payroll intg .. set the globals for HR rates .. */
2533
2534 pa_pay_util.g_project_number := NULL;
2535 pa_pay_util.g_task_number := NULL;
2536 pa_pay_util.g_location_code := NULL;
2537 pa_pay_util.g_job_name := NULL;
2538 pa_pay_util.g_work_type_name := NULL;
2539
2540 if p_person_id is not null then
2541 if p_forecast_job_id is null then
2542 select name
2543 into pa_pay_util.g_job_name
2544 from per_jobs_v
2545 where job_id = pa_utils.getempjobid(X_person_id => p_person_id, X_date => p_asgn_start_date);
2546 end if;
2547
2548 select location_code
2549 into pa_pay_util.g_location_code
2550 from hr_locations a, per_all_assignments_f b
2551 where b.person_id = p_person_id
2552 and a.location_use = 'HR'
2553 and a.location_id = b.location_id
2554 and p_asgn_start_date between b.effective_start_date and nvl(p_asgn_start_date, b.effective_end_date);
2555
2556 elsif p_forecast_job_id is not null then
2557 select name
2558 into pa_pay_util.g_job_name
2559 from per_jobs_v
2560 where job_id = p_forecast_job_id;
2561 end if;
2562
2563 -- get the project number and task number details
2564 select segment1
2565 into pa_pay_util.g_project_number
2566 from pa_projects
2567 where project_id = p_project_id;
2568
2569 if p_task_id is not null then
2570 -- get the task number details
2571 select task_number
2572 into pa_pay_util.g_task_number
2573 from pa_tasks
2574 where task_id = p_task_id;
2575
2576 -- get the default work type information from the task
2577 begin
2578 select name
2579 into pa_pay_util.g_work_type_name
2580 from pa_work_types_vl a, pa_tasks b
2581 where a.work_type_id = b.work_type_id
2582 and b.task_id = p_task_id;
2583
2584 exception
2585 when no_data_found then
2586 pa_pay_util.g_work_type_name := NULL;
2587 end;
2588 end if;
2589
2590 /* 12.2 payroll intg .. set globals for HR rates .. end */
2591
2592 PA_COST.get_raw_cost ( P_person_id => p_person_id ,
2593 P_expenditure_org_id => p_expenditure_org_id ,
2594 P_expend_organization_id => p_expenditure_organization_id, /*LCE*/
2595 P_labor_Cost_Mult_Name => l_labor_cost_mult_name ,
2596 P_Item_date => p_asgn_start_date ,
2597 px_exp_func_curr_code => l_expenditure_curr_code_burdn,
2598 P_Quantity => p_quantity ,
2599 X_Raw_cost_rate => l_raw_cost_rate ,
2600 X_Raw_cost => l_raw_cost ,
2601 x_return_status => l_x_return_status ,
2602 x_msg_count => x_msg_count ,
2603 x_msg_data => x_msg_data
2604 );
2605
2606 IF g1_debug_mode = 'Y' THEN
2607 PA_DEBUG.g_err_stage := 'RT53 : Leaving PA_COST.get_raw_cost';
2608 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2609 END IF;
2610
2611 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2612 IF g1_debug_mode = 'Y' THEN
2613 PA_DEBUG.g_err_stage := 'RT54 : Entering PA_COST.override_exp_organization';
2614 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2615 END IF;
2616
2617 PA_COST.override_exp_organization(P_item_date => p_asgn_start_date ,
2618 P_person_id => p_person_id ,
2619 P_project_id => p_project_id ,
2620 P_incurred_by_organz_id => p_expenditure_organization_id ,
2621 P_Expenditure_type => p_expenditure_type ,
2622 X_overr_to_organization_id => l_overr_to_organization_id ,
2623 x_return_status => l_x_return_status ,
2624 x_msg_count => x_msg_count ,
2625 x_msg_data => x_msg_data
2626 );
2627
2628 IF g1_debug_mode = 'Y' THEN
2629 PA_DEBUG.g_err_stage := 'RT55 : Leaving PA_COST.override_exp_organization';
2630 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2631 END IF;
2632
2633 END IF;
2634
2635 ELSIF (p_assignment_type = 'R') THEN
2636 IF g1_debug_mode = 'Y' THEN
2637 PA_DEBUG.g_err_stage := 'RT52 : Entering PA_COST.requirement_raw_cost';
2638 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2639 END IF;
2640 /* Four project functional attributes added for MCB2 */
2641 PA_COST.requirement_raw_cost( p_forecast_cost_job_group_id => l_forecast_job_group_id ,
2642 p_forecast_cost_job_id => l_forecast_job_id ,
2643 p_proj_cost_job_group_id => l_proj_cost_job_grp_id ,
2644 px_proj_cost_job_id => l_proj_cost_job_id ,
2645 p_item_date => p_asgn_start_date ,
2646 p_job_cost_rate_sch_id => l_job_cost_rate_schedule_id ,
2647 p_schedule_date => l_labor_schedule_fixed_date ,
2648 p_quantity => p_quantity ,
2649 p_cost_rate_multiplier => l_cost_rate_multiplier ,
2650 p_org_id => l_project_org_id ,
2651 p_expend_organization_id => p_expenditure_organization_id , /*LCE*/
2652 x_raw_cost_rate => l_raw_cost_rate ,
2653 x_raw_cost => l_raw_cost ,
2654 x_txn_currency_code => l_expenditure_curr_code_burdn,
2655 x_return_status => l_x_return_status ,
2656 x_msg_count => x_msg_count ,
2657 x_msg_data => x_msg_data
2658 );
2659 END IF;
2660
2661 l_expenditure_currency_code := NVL(l_expenditure_curr_code_burdn,l_expenditure_currency_code); /* added for Org Fcst */
2662
2663 IF (NVL(l_raw_cost,0) <> 0 ) THEN
2664
2665 PA_COST.get_burdened_cost(p_project_type => l_project_type ,
2666 p_project_id => p_project_id ,
2667 p_task_id => p_task_id ,
2668 p_item_date => p_asgn_start_date ,
2669 p_expenditure_type => p_expenditure_type ,
2670 p_schedule_type => l_schedule_type ,
2671 px_exp_func_curr_code => l_expenditure_currency_code ,
2672 p_Incurred_by_organz_id => p_expenditure_organization_id ,
2673 p_raw_cost => l_raw_cost ,
2674 p_raw_cost_rate => l_raw_cost_rate ,
2675 p_quantity => p_quantity ,
2676 p_override_to_organz_id => l_overr_to_organization_id ,
2677 x_burden_cost => l_exp_func_burdened_cost ,
2678 x_burden_cost_rate => l_exp_func_burdened_cost_rate ,
2679 x_return_status => l_x_return_status ,
2680 x_msg_count => x_msg_count ,
2681 x_msg_data => x_msg_data
2682 );
2683
2684 PA_COST.Get_Converted_Cost_Amounts(
2685 P_exp_org_id => p_expenditure_org_id,
2686 P_proj_org_id => l_project_org_id,
2687 P_project_id => p_project_id,
2688 P_task_id => p_task_id,
2689 P_item_date => p_asgn_start_date,
2690 p_system_linkage => l_system_linkage,
2691 px_txn_curr_code => l_cst_txn_curr_code,
2692 px_raw_cost => l_raw_cost,
2693 px_raw_cost_rate => l_raw_cost_rate,
2694 px_burden_cost => l_exp_func_burdened_cost,
2695 px_burden_cost_rate => l_exp_func_burdened_cost_rate,
2696 px_exp_func_curr_code => l_expenditure_currency_code,
2697 px_exp_func_rate_date => l_exp_func_cst_rt_date,
2698 px_exp_func_rate_type => l_exp_func_cst_rt_type,
2699 px_exp_func_exch_rate => l_exp_func_cst_exch_rt,
2700 px_exp_func_cost => l_exp_func_raw_cost,
2701 px_exp_func_cost_rate => l_exp_func_raw_cost_rate,
2702 px_exp_func_burden_cost => l_exp_func_burdened_cost,
2703 px_exp_func_burden_cost_rate => l_exp_func_burdened_cost_rate,
2704 px_proj_func_curr_code => l_projfunc_currency_code,
2705 px_projfunc_cost_rate_date => l_projfunc_cost_rate_date,
2706 px_projfunc_cost_rate_type => l_projfunc_cost_rate_type,
2707 px_projfunc_cost_exch_rate => l_projfunc_cost_exchange_rate,
2708 px_projfunc_raw_cost => l_projfunc_raw_cost ,
2709 px_projfunc_raw_cost_rate => l_projfunc_raw_cost_rate ,
2710 px_projfunc_burden_cost => l_projfunc_burdened_cost ,
2711 px_projfunc_burden_cost_rate => l_projfunc_burdened_cost_rate ,
2712 px_project_curr_code => l_project_currency_code,
2713 px_project_rate_date => l_project_cost_rate_date,
2714 px_project_rate_type => l_project_cost_rate_type,
2715 px_project_exch_rate => l_project_cost_exchange_rate,
2716 px_project_cost => l_project_raw_cost,
2717 px_project_cost_rate => l_project_raw_cost_rate,
2718 px_project_burden_cost => l_project_burdened_cost,
2719 px_project_burden_cost_rate => l_project_burdened_cost_rate,
2720 x_return_status => l_x_return_status ,
2721 x_msg_count => x_msg_count ,
2722 x_msg_data => x_msg_data
2723 );
2724
2725 END IF;
2726
2727 /* Deleted this proc PA_COST.get_projfunc_raw_burdened_cost() for Org Forecasting */
2728
2729 END IF; /* end of calculate cost flag */
2730
2731 IF (SUBSTR(l_distribution_rule,1,4) = 'WORK' AND l_class_code = 'CONTRACT') THEN
2732 IF ( p_assignment_type = 'A') THEN
2733 IF g1_debug_mode = 'Y' THEN
2734 PA_DEBUG.g_err_stage := 'RT56 : Entering PA_REVENUE.get_rev_amt';
2735 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2736 END IF;
2737
2738 PA_REVENUE.Assignment_Rev_Amt(
2739 p_project_id => p_project_id ,
2740 p_task_id => p_task_id ,
2741 p_bill_rate_multiplier => p_bill_rate_multiplier ,
2742 p_quantity => p_quantity ,
2743 p_person_id => p_person_id ,
2744 p_raw_cost => l_projfunc_raw_cost ,
2745 p_item_date => p_asgn_start_date ,
2746 p_labor_schdl_discnt => l_labor_schedule_discount ,
2747 p_labor_bill_rate_org_id => l_labor_bill_rate_org_id ,
2748 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schedule ,
2749 p_labor_schdl_fixed_date => l_labor_schedule_fixed_date ,
2750 p_bill_job_grp_id => l_project_bill_job_group_id ,
2751 p_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id , -- Bug# 10138946
2752 p_item_id => p_assignment_id , /* changed for bug 2212852 */
2753 p_forecast_item_id => p_forecast_item_id, /* added for bug 2212852 */
2754 p_forecasting_type => p_forecasting_type , /* added for bug 2212852 */
2755 p_labor_sch_type => l_labor_schedule_type ,
2756 p_project_org_id => l_project_org_id ,
2757 p_project_type => l_project_type ,
2758 p_expenditure_type => p_expenditure_type ,
2759 p_exp_func_curr_code => l_expenditure_currency_code ,
2760 p_incurred_by_organz_id => p_expenditure_organization_id ,
2761 p_raw_cost_rate => l_raw_cost_rate ,
2762 p_override_to_organz_id => l_overr_to_organization_id ,
2763 p_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id ,
2764 p_resource_job_id => l_forecast_job_id ,
2765 p_exp_raw_cost => l_raw_cost ,
2766 p_expenditure_org_id => p_expenditure_org_id ,
2767 p_projfunc_currency_code => l_projfunc_currency_code , -- The following 5
2768 p_assignment_precedes_task => l_assignment_precedes_task , /* Added for Asgmt overide */
2769 p_sys_linkage_function => l_system_linkage, /* Added for Org FCST */
2770 x_bill_rate => l_txn_rev_bill_rt,/* Change for Org Forecsting */
2771 x_raw_revenue => l_txn_rev_raw_revenue ,
2772 x_markup_percentage => l_markup_percentage,/* Added for Asgmt overide */
2773 x_txn_currency_code => l_rev_txn_curr_code, /* added for Org */
2774 x_rev_currency_code => l_projfunc_currency_code ,
2775 x_return_status => l_x_return_status ,
2776 x_msg_count => x_msg_count ,
2777 x_msg_data => x_msg_data ,
2778 /* Added for bug 2668753 */
2779 p_project_raw_cost => l_project_raw_cost ,
2780 p_project_currency_code => l_project_currency_code ,
2781 x_adjusted_bill_rate => l_txn_adjusted_bill_rt
2782 );
2783
2784 -- dbms_output.put_line(' Get Ini rev : '||l_txn_rev_raw_revenue||' curr : '||l_rev_txn_curr_code);
2785 IF g1_debug_mode = 'Y' THEN
2786 PA_DEBUG.g_err_stage := 'RT57 : Leaving PA_REVENUE.get_rev_amt';
2787 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2788 END IF;
2789 /* IF ( x_projfunc_raw_revenue IS NULL OR x_projfunc_raw_revenue = 0 ) THEN
2790 x_projfunc_bill_rate := 0;
2791 x_projfunc_raw_revenue := 0;
2792 END IF; Commented for Org Forecasting */
2793
2794
2795 ELSIF (p_assignment_type = 'R') THEN
2796 IF g1_debug_mode = 'Y' THEN
2797 PA_DEBUG.g_err_stage := 'RT53 : Leaving PA_COST.requirement_raw_cost';
2798 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2799 END IF;
2800
2801 PA_REVENUE.requirement_rev_amt(
2802 p_project_id => p_project_id ,
2803 p_task_id => p_task_id ,
2804 p_bill_rate_multiplier => p_bill_rate_multiplier ,
2805 p_quantity => p_quantity ,
2806 p_raw_cost => l_projfunc_raw_cost ,
2807 p_item_date => p_asgn_start_date ,
2808 p_project_bill_job_grp_id => l_project_bill_job_group_id ,
2809 p_labor_schdl_discnt => l_labor_schedule_discount ,
2810 p_labor_bill_rate_org_id => l_labor_bill_rate_org_id ,
2811 p_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schedule ,
2812 p_labor_schdl_fixed_date => l_labor_schedule_fixed_date ,
2813 p_forecast_job_id => l_forecast_job_id ,
2814 p_forecast_job_grp_id => l_forecast_job_group_id ,
2815 p_labor_sch_type => l_labor_schedule_type ,
2816 p_item_id => p_assignment_id , /* changed for bug 2212852 */
2817 p_forecast_item_id => p_forecast_item_id, /* added for bug 2212852 */
2818 p_forecasting_type => p_forecasting_type , /* added for bug 2212852 */
2819 p_project_org_id => l_project_org_id ,
2820 p_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id ,
2821 p_project_type => l_project_type ,
2822 p_expenditure_type => p_expenditure_type ,
2823 px_exp_func_curr_code => l_expenditure_currency_code ,
2824 p_incurred_by_organz_id => p_expenditure_organization_id ,
2825 p_raw_cost_rate => l_raw_cost_rate ,
2826 p_override_to_organz_id => l_overr_to_organization_id ,
2827 p_exp_raw_cost => l_raw_cost ,
2828 p_expenditure_org_id => p_expenditure_org_id ,
2829 p_projfunc_currency_code => l_projfunc_currency_code , -- The following 5
2830 p_assignment_precedes_task => l_assignment_precedes_task , /* Added for Asgmt overide */
2831 p_sys_linkage_function => l_system_linkage, /* Added for Org FCST */
2832 px_project_bill_job_id => l_proj_bill_job_id ,
2833 x_bill_rate => l_txn_rev_bill_rt,/*Change for Org Forecsting */
2834 x_raw_revenue => l_txn_rev_raw_revenue ,
2835 x_markup_percentage => l_markup_percentage,/* Added for Asgmt overide */
2836 x_txn_currency_code => l_rev_txn_curr_code, /* added for Org */
2837 x_return_status => l_x_return_status ,
2838 x_msg_count => x_msg_count ,
2839 x_msg_data => x_msg_data
2840 );
2841
2842 IF g1_debug_mode = 'Y' THEN
2843 PA_DEBUG.g_err_stage := 'RT55 : Leaving PA_REVENUE.requirement_rev_amt';
2844 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2845 END IF;
2846
2847 END IF; /* End of assignment_type if */
2848
2849 IF ( (NVL(l_txn_rev_raw_revenue,0) <> 0) ) THEN
2850
2851 -- dbms_output.put_line(' Get Ini rev 0.1 : '||l_txn_rev_raw_revenue||' curr : '||l_rev_txn_curr_code);
2852 PA_REVENUE.Get_Converted_Revenue_Amounts(
2853 p_item_date => p_asgn_start_date,
2854 px_txn_curr_code => l_rev_txn_curr_code,
2855 px_txn_raw_revenue => l_txn_rev_raw_revenue,
2856 px_txn_bill_rate => l_txn_rev_bill_rt,
2857 px_projfunc_curr_code => l_projfunc_currency_code,
2858 p_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
2859 px_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
2860 px_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
2861 px_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
2862 px_projfunc_raw_revenue => l_projfunc_raw_revenue ,
2863 px_projfunc_bill_rate => l_projfunc_bill_rate ,
2864 px_project_curr_code => l_project_currency_code,
2865 p_project_bil_rate_date_code => l_project_bil_rate_date_code,
2866 px_project_bil_rate_type => l_project_bil_rate_type,
2867 px_project_bil_rate_date => l_project_bil_rate_date,
2868 px_project_bil_exchange_rate => l_project_bil_exchange_rate,
2869 px_project_raw_revenue => l_project_raw_revenue ,
2870 px_project_bill_rate => l_project_bill_rate ,
2871 x_return_status => l_x_return_status ,
2872 x_msg_count => x_msg_count ,
2873 x_msg_data => x_msg_data
2874 );
2875
2876 -- dbms_output.put_line(' Get Ini rev 1 : '||l_txn_rev_raw_revenue||' curr : '||l_rev_txn_curr_code);
2877 x_projfunc_bill_rate := l_projfunc_bill_rate;
2878 x_projfunc_raw_revenue := l_projfunc_raw_revenue;
2879 END IF;
2880
2881 IF ( x_projfunc_raw_revenue IS NULL OR x_projfunc_raw_revenue = 0 ) THEN
2882 x_projfunc_bill_rate := 0;
2883 x_projfunc_raw_revenue := 0;
2884 END IF;
2885 END IF; /* End of class code and rule if (for R and A) */
2886
2887 -------------------------------------------------------
2888 -- Assign the successful status back to output variable
2889 -------------------------------------------------------
2890
2891 x_return_status := l_x_return_status;
2892
2893 x_rev_currency_code := l_projfunc_currency_code;
2894 x_markup_percentage := l_markup_percentage; /* Added for Asgmt overide */
2895
2896 IF g1_debug_mode = 'Y' THEN
2897 PA_DEBUG.g_err_stage := 'RT58 : Leaving PA_RATE_PVT_PKG.get_initial_bill_rate';
2898 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2899 PA_DEBUG.Reset_Curr_Function;
2900 END IF;
2901
2902 EXCEPTION
2903 WHEN l_insufficient_parameters THEN
2904 IF g1_debug_mode = 'Y' THEN
2905 PA_DEBUG.Reset_Curr_Function;
2906 END IF;
2907 x_return_status := FND_API.G_RET_STS_ERROR;
2908 x_msg_count := 1;
2909 x_msg_data := 'PA_FCST_INSUFFICIENT_PARA';
2910 WHEN l_job_not_found THEN
2911 IF g1_debug_mode = 'Y' THEN
2912 PA_DEBUG.Reset_Curr_Function;
2913 END IF;
2914 x_return_status := FND_API.G_RET_STS_ERROR;
2915 x_msg_count := 1;
2916 x_msg_data := 'PA_FCST_NO_JOB_FOUND';
2917 WHEN OTHERS THEN
2918 IF g1_debug_mode = 'Y' THEN
2919 PA_DEBUG.Reset_Curr_Function;
2920 END IF;
2921
2922 /* ATG Changes */
2923
2924 x_projfunc_bill_rate := null;
2925 x_projfunc_raw_revenue := null;
2926 x_rev_currency_code := null;
2927 x_markup_percentage := null;
2928
2929
2930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2931 x_msg_data := SUBSTR(SQLERRM,1,30);
2932 /* Checking error condition. Added for bug 2218386 */
2933 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2934 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG', /* Moved this here to fix bug 2434663 */
2935 p_procedure_name => 'get_initial_bill_rate');
2936 RAISE;
2937 END IF;
2938
2939 END get_initial_bill_rate;
2940
2941
2942 -- This procedure contains consolidated procedure and function to calculate the raw cost,
2943 -- burdened cost and raw revenue on the basis of passed parameters on array basis
2944 -- Input parameters
2945 -- Parameters Type Required Description
2946 -- p_calling_mode VARCHAR2 YES Calling mode values are ACTUAL/ROLE/ASSIGNMENT
2947 -- P_item_id NUMBER YES Unique identifier
2948 -- P_project_id NUMBER YES Project Id
2949 -- P_forecast_job_id NUMBER NO Forecast job Id at assignment level
2950 -- P_forecast_job_group_id NUMBER NO Forecast job group id at assignment level
2951 -- p_person_id NUMBER NO Person id
2952 -- P_expenditure_type VARCHAR2 NO Expenditure Type
2953 -- p_expenditure_organization_id NUMBER NO Expenditure organization id
2954 -- p_project_org_id NUMBER NO Project org id
2955 -- p_labor_cost_multi_name VARCHAR2 NO Labor cost multiplier name for calculating the cost
2956 -- p_expenditure_currency_code VARCHAR2 NO Expenditure functional currency code
2957 -- P_proj_cost_job_group_id NUMBER NO Project cost job gorup id
2958 -- P_job_cost_rate_schedule_id NUMBER NO Job cost rate schedule id
2959 -- P_project_type VARCHAR2 NO Project Type
2960 -- P_task_id NUMBER NO Task Id for the given project
2961 -- p_projfunc_currency_code VARCHAR2 NO Project Functional currency code
2962 -- P_bill_rate_multiplier NUMBER NO Bill rate multiplier for calculating the revenue
2963 -- P_project_bill_job_group_id NUMBER NO Billing job group id for project
2964 -- p_emp_bill_rate_schedule_id NUMBER NO Employee bill rate schedule id
2965 -- P_job_bill_rate_schedule_id NUMBER NO Job bill rate schedule id
2966 -- and rate
2967 -- p_distribution_rule VARCHAR2 NO Distribution rule
2968 --
2969 -- Out parameters
2970 --
2971 -- x_exp_func_raw_cost_rate NUMBER YES Row cost rate in expenditure currency
2972 -- x_exp_func_raw_cost NUMBER YES Row cost in expenditure currency
2973 -- x_exp_func_burdened_cost_rate NUMBER YES Burdened cost rate in expenditure currency
2974 -- x_exp_func_burdened_cost NUMBER YES Burdened cost in expenditure currency
2975 -- x_projfunc_bill_rate NUMBER YES Bill rate in project currency
2976 -- x_projfunc_raw_revenue NUMBER YES Raw revenue in project currency
2977 -- x_projfunc_raw_cost NUMBER YES Raw cost in project currency
2978 -- x_projfunc_raw_cost_rate NUMBER YES Raw cost rate in project currency
2979 -- x_projfunc_burdened_cost_rate NUMBER YES Burdened cost rate in project currency
2980 -- x_projfunc_burdened_cost NUMBER YES Burdened cost in project currency
2981 -- x_error_msg VARCHAR2 YES Error message used in when others exception
2982 -- x_rev_rejct_reason VARCHAR2 YES Rejection reason for revenue
2983 -- x_cost_rejct_reason VARCHAR2 YES Rejection reason for cost
2984 -- x_burdened_rejct_reason VARCHAR2 YES Rejection reason for burden
2985 -- x_others_rejct_reason VARCHAR2 YES Rejection reason for other error like pl/sql etc.
2986
2987 PROCEDURE calc_rate_amount(
2988
2989 p_calling_mode IN VARCHAR2 , /* possible values 'ASSIGNMENT','ROLE','UNASSIGNED' */
2990 /* for Org forecasting */
2991 p_rate_calc_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp ,
2992 p_asgn_start_date IN DATE ,
2993 p_item_id IN NUMBER ,
2994 p_project_id IN NUMBER ,
2995 p_quantity_tab IN PA_PLSQL_DATATYPES.NumTabTyp,
2996 p_forecast_job_id IN NUMBER DEFAULT NULL,
2997 p_forecast_job_group_id IN NUMBER DEFAULT NULL,
2998 p_person_id IN NUMBER DEFAULT NULL,
2999 p_expenditure_org_id_tab IN PA_PLSQL_DATATYPES.IdTabTyp,
3000 p_expenditure_type IN VARCHAR2 DEFAULT NULL,
3001 p_expenditure_orgz_id_tab IN PA_PLSQL_DATATYPES.IdTabTyp ,
3002 p_project_org_id IN NUMBER DEFAULT NULL,
3003 p_labor_cost_multi_name IN VARCHAR2 DEFAULT NULL,
3004 p_proj_cost_job_group_id IN NUMBER DEFAULT NULL,
3005 p_job_cost_rate_schedule_id IN NUMBER DEFAULT NULL,
3006 p_project_type IN VARCHAR2 DEFAULT NULL,
3007 p_task_id IN NUMBER DEFAULT NULL,
3008 p_bill_rate_multiplier IN NUMBER DEFAULT NULL,
3009 p_project_bill_job_group_id IN NUMBER DEFAULT NULL,
3010 p_emp_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
3011 p_job_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
3012 p_distribution_rule IN VARCHAR2 DEFAULT NULL,
3013 p_amount_calc_mode IN VARCHAR2 DEFAULT 'ALL', /* Possible values 'ALL','COST','REVENUE' */
3014 /* Added fro Org Forecasting*/
3015 P_system_linkage IN PA_PLSQL_DATATYPES.Char30TabTyp,/* Added */
3016 /* for Org Forecasting */
3017 p_assign_precedes_task IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
3018 p_labor_schdl_discnt IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
3019 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL, /* Added for Org Forecasting */
3020 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
3021 p_labor_schedule_fixed_date IN DATE DEFAULT NULL, /* Added for Org Forecasting */
3022 p_labor_sch_type IN VARCHAR2 DEFAULT NULL, /* Added for Org Forecasting */
3023 p_forecast_item_id_tab IN PA_PLSQL_DATATYPES.IdTabTyp, /* Added para for bug 2212852 */
3024 p_forecasting_type IN VARCHAR2 DEFAULT 'PROJECT_FORECASTING',/*Added par for bug2212852*/
3025 p_projfunc_currency_code IN VARCHAR2 DEFAULT NULL,
3026 p_projfunc_rev_rt_dt_code_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for Org Forecasting */
3027 p_projfunc_rev_rt_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp, /* Added for Org Forecasting */
3028 p_projfunc_rev_rt_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for Org Forecasting */
3029 p_projfunc_rev_exch_rt_tab IN PA_PLSQL_DATATYPES.NumTabTyp, /* Added for Org Forecasting */
3030 p_projfunc_cst_rt_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp, /* Added for Org Forecasting */
3031 p_projfunc_cst_rt_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for Org Forecasting */
3032 x_projfunc_bill_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3033 x_projfunc_raw_revenue_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3034 x_projfunc_rev_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3035 x_projfunc_rev_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3036 x_projfunc_rev_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3037 x_projfunc_raw_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3038 x_projfunc_raw_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3039 x_projfunc_burdned_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3040 x_projfunc_burdned_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3041 x_projfunc_cst_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3042 x_projfunc_cst_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3043 x_projfunc_cst_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3044 p_project_currency_code IN VARCHAR2 DEFAULT NULL, /* Added for org Forecasting */
3045 p_project_rev_rt_dt_code_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */
3046 p_project_rev_rt_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */
3047 p_project_rev_rt_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */
3048 p_project_rev_exch_rt_tab IN PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */
3049 p_project_cst_rt_date_tab IN PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */
3050 p_project_cst_rt_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */
3051 x_project_bill_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3052 x_project_raw_revenue_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3053 x_project_rev_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3054 x_project_rev_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3055 x_project_rev_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3056 x_project_raw_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3057 x_project_raw_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3058 x_project_burdned_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3059 x_project_burdned_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3060 x_project_cst_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3061 x_project_cst_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3062 x_project_cst_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3063 x_exp_func_curr_code_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
3064 x_exp_func_raw_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp , --File.Sql.39 bug 4440895
3065 x_exp_func_raw_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3066 x_exp_func_burdned_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3067 x_exp_func_burdned_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3068 x_exp_func_cst_rt_date_tab OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3069 x_exp_func_cst_rt_type_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3070 x_exp_func_cst_exch_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3071 x_cst_txn_curr_code_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
3072 x_txn_raw_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp , --File.Sql.39 bug 4440895
3073 x_txn_raw_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3074 x_txn_burdned_cst_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3075 x_txn_burdned_cst_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, --File.Sql.39 bug 4440895
3076 x_rev_txn_curr_code_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
3077 x_txn_rev_bill_rt_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3078 x_txn_rev_raw_revenue_tab OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp, /* Added for org Forecasting */ --File.Sql.39 bug 4440895
3079 x_error_msg OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3080 x_rev_rejct_reason_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, --File.Sql.39 bug 4440895
3081 x_cst_rejct_reason_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, --File.Sql.39 bug 4440895
3082 x_burdned_rejct_reason_tab OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, --File.Sql.39 bug 4440895
3083 x_others_rejct_reason_tab IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp, /* Changed for Org Forecasting */ --File.Sql.39 bug 4440895
3084 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3085 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3086 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
3087 IS
3088 l_rate_calc_date DATE;
3089 l_rate_calc_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
3090 l_quantity NUMBER;
3091 l_expenditure_org_id pa_project_assignments.expenditure_org_id%TYPE;
3092 l_expenditure_orgz_id pa_project_assignments.expenditure_organization_id%TYPE;
3093 /* MCB2 related changes */
3094 l_projfunc_bill_rate NUMBER;
3095 l_projfunc_raw_revenue NUMBER;
3096 l_projfunc_raw_cost NUMBER;
3097 l_projfunc_raw_cost_rate NUMBER;
3098 l_projfunc_burdened_cost NUMBER;
3099 l_projfunc_burdened_cost_rate NUMBER;
3100 /* till here */
3101 l_rev_rejct_reason VARCHAR2(30);
3102 l_cost_rejct_reason VARCHAR2(30);
3103 l_burdened_rejct_reason VARCHAR2(30);
3104 l_others_rejct_reason VARCHAR2(30);
3105
3106 l_return_status VARCHAR2(30);
3107 l_error_msg VARCHAR2(30);
3108
3109 l_exp_func_raw_cost_rate NUMBER;
3110 l_exp_func_raw_cost NUMBER;
3111 l_exp_func_burdened_cost_rate NUMBER;
3112 l_exp_func_burdened_cost NUMBER;
3113
3114 l_forecast_item_id pa_forecast_items.forecast_item_id%TYPE; /* added for bug 2212852 */
3115
3116 /* Adding for Org Forecasting */
3117
3118 l_projfunc_rev_rt_dt_code PA_PROJECTS_ALL.projfunc_bil_rate_date_code%TYPE;
3119 l_projfunc_rev_rt_type PA_PROJECTS_ALL.projfunc_bil_rate_type%TYPE;
3120 l_projfunc_rev_rt_date PA_PROJECTS_ALL.projfunc_bil_rate_date%TYPE;
3121 l_projfunc_rev_exch_rt PA_PROJECTS_ALL.projfunc_bil_exchange_rate%TYPE;
3122
3123 l_projfunc_cst_rt_type PA_PROJECTS_ALL.projfunc_cost_rate_type%TYPE;
3124 l_projfunc_cst_rt_date PA_PROJECTS_ALL.projfunc_cost_rate_date%TYPE;
3125 l_projfunc_cst_exch_rt NUMBER;
3126
3127 l_project_rev_rt_dt_code PA_PROJECTS_ALL.project_bil_rate_date_code%TYPE;
3128 l_project_rev_rt_type PA_PROJECTS_ALL.project_bil_rate_type%TYPE;
3129 l_project_rev_rt_date PA_PROJECTS_ALL.project_bil_rate_date%TYPE;
3130 l_project_rev_exch_rt PA_PROJECTS_ALL.project_bil_exchange_rate%TYPE;
3131
3132 l_project_cst_rt_type PA_PROJECTS_ALL.project_rate_type%TYPE;
3133 l_project_cst_rt_date PA_PROJECTS_ALL.project_rate_date%TYPE;
3134 l_project_cst_exch_rt NUMBER;
3135
3136 l_project_bill_rate NUMBER;
3137 l_project_raw_revenue NUMBER;
3138 l_project_raw_cost NUMBER;
3139 l_project_raw_cost_rate NUMBER;
3140 l_project_burdened_cost NUMBER;
3141 l_project_burdened_cost_rate NUMBER;
3142
3143 l_exp_func_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
3144 l_exp_func_cst_rt_date DATE;
3145 l_exp_func_cst_rt_type PA_IMPLEMENTATIONS_ALL.default_rate_type%TYPE;
3146 l_exp_func_cst_exch_rt NUMBER;
3147
3148 l_cst_txn_curr_code GL_SETS_OF_BOOKS.currency_code%TYPE;
3149 l_txn_raw_cst_rt NUMBER;
3150 l_txn_raw_cst NUMBER;
3151 l_txn_burdned_cst_rt NUMBER;
3152 l_txn_burdned_cst NUMBER;
3153
3154 l_rev_txn_curr_code PA_BILL_RATES_ALL.rate_currency_code%TYPE;
3155 l_txn_rev_bill_rt NUMBER;
3156 l_txn_rev_raw_revenue NUMBER;
3157
3158 l_system_linkage pa_expenditure_items_all.system_linkage_function%TYPE;
3159
3160 BEGIN
3161
3162 --dbms_output.put_line(' I am in CALC RATE AMOUNT ');
3163
3164 IF g1_debug_mode = 'Y' THEN
3165 PA_DEBUG.Set_Curr_Function( p_function => 'Calc Rate Amount');
3166 PA_DEBUG.g_err_stage := 'RT40 : Entering PA_RATE_PVT_PKG.Calc_Rate_Amount';
3167 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3168 END IF;
3169
3170 x_return_status := FND_API.G_RET_STS_SUCCESS;
3171 IF g1_debug_mode = 'Y' THEN
3172 PA_DEBUG.g_err_stage := 'RTS1 : Checking tab count '||TO_CHAR(NVL(p_rate_calc_date_tab.count,0));
3173 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3174
3175
3176 PA_DEBUG.g_err_stage := ' Rate CALC Amt : Inside API project id '||p_project_id||' Item Id '||p_item_id;
3177 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3178 END IF;
3179
3180 IF ((p_rate_calc_date_tab.count) >= 1 ) THEN
3181 --dbms_output.put_line(' First : '||p_rate_calc_date_tab.FIRST);
3182 --dbms_output.put_line(' Last : '||p_rate_calc_date_tab.LAST);
3183 --dbms_output.put_line(' count : '||p_rate_calc_date_tab.count);
3184
3185 FOR l_J IN p_rate_calc_date_tab.FIRST..p_rate_calc_date_tab.LAST LOOP
3186 /* If the passed table does not have the specific index then inserting null at that possition
3187 so that other part of code should not execute for Org Forecasting */
3188 IF (p_rate_calc_date_tab.EXISTS(l_j)) THEN
3189 l_rate_calc_date_tab(l_j) := p_rate_calc_date_tab(l_j);
3190 ELSE
3191 l_rate_calc_date_tab(l_j) := NULL;
3192 END IF;
3193 END LOOP;
3194
3195 FOR l_J IN l_rate_calc_date_tab.FIRST..l_rate_calc_date_tab.LAST LOOP
3196
3197 -- dbms_output.put_line(' step 1 '||l_J);
3198 IF (l_rate_calc_date_tab(l_J) IS NOT NULL ) THEN /* { IF index in between is missing, then not calling for Org Forecasting */
3199
3200 -- dbms_output.put_line(' step 2 '||l_J);
3201 ----------------------------------------------------------------------------------------
3202 -- Assigning pl/sql table varibles to local varible and nulling out the OUT
3203 -- pl/sql table varibles
3204 -----------------------------------------------------------------------------------------
3205
3206 /* Project Functional currency */
3207
3208 l_projfunc_rev_rt_dt_code := p_projfunc_rev_rt_dt_code_tab(l_J); /* Added for Org Forecasting */
3209 l_projfunc_rev_rt_date := p_projfunc_rev_rt_date_tab(l_J); /* Added for Org Forecasting */
3210 l_projfunc_rev_rt_type := p_projfunc_rev_rt_type_tab(l_J); /* Added for Org Forecasting */
3211 l_projfunc_rev_exch_rt := p_projfunc_rev_exch_rt_tab(l_J); /* Added for Org Forecasting */
3212
3213 l_projfunc_cst_rt_date := p_projfunc_cst_rt_date_tab(l_J); /* Added for Org Forecasting */
3214 l_projfunc_cst_rt_type := p_projfunc_cst_rt_type_tab(l_J); /* Added for Org Forecasting */
3215
3216 x_projfunc_bill_rt_tab(l_J) := 0;
3217 x_projfunc_raw_revenue_tab(l_J) := 0;
3218
3219 x_projfunc_rev_rt_date_tab(l_J) := l_projfunc_rev_rt_date; /* Added for org Forecasting */
3220 x_projfunc_rev_rt_type_tab(l_J) := l_projfunc_rev_rt_type; /* Added for org Forecasting */
3221 x_projfunc_rev_exch_rt_tab(l_J) := l_projfunc_rev_exch_rt; /* Added for org Forecasting */
3222
3223 x_projfunc_raw_cst_tab(l_J) := 0;
3224 x_projfunc_raw_cst_rt_tab(l_J) := 0;
3225 x_projfunc_burdned_cst_tab(l_J) := 0;
3226 x_projfunc_burdned_cst_rt_tab(l_J) := 0;
3227
3228 x_projfunc_cst_rt_date_tab(l_J) := l_projfunc_cst_rt_date; /* Added for org Forecasting */
3229 x_projfunc_cst_rt_type_tab(l_J) := l_projfunc_cst_rt_type; /* Added for org Forecasting */
3230 x_projfunc_cst_exch_rt_tab(l_J) := l_projfunc_cst_exch_rt; /* Added for org Forecasting */
3231
3232 /* Project currency */
3233
3234 l_project_rev_rt_dt_code := p_project_rev_rt_dt_code_tab(l_J); /* Added for Org Forecasting */
3235 l_project_rev_rt_date := p_project_rev_rt_date_tab(l_J); /* Added for Org Forecasting */
3236 l_project_rev_rt_type := p_project_rev_rt_type_tab(l_J); /* Added for Org Forecasting */
3237 l_project_rev_exch_rt := p_project_rev_exch_rt_tab(l_J); /* Added for Org Forecasting */
3238
3239 l_project_cst_rt_date := p_project_cst_rt_date_tab(l_J); /* Added for Org Forecasting */
3240 l_project_cst_rt_type := p_project_cst_rt_type_tab(l_J); /* Added for Org Forecasting */
3241
3242 x_project_bill_rt_tab(l_J) := 0;
3243 x_project_raw_revenue_tab(l_J) := 0;
3244
3245 x_project_rev_rt_date_tab(l_J) := l_project_rev_rt_date; /* Added for org Forecasting */
3246 x_project_rev_rt_type_tab(l_J) := l_project_rev_rt_type; /* Added for org Forecasting */
3247 x_project_rev_exch_rt_tab(l_J) := l_project_rev_exch_rt; /* Added for org Forecasting */
3248
3249 x_project_raw_cst_tab(l_J) := 0;
3250 x_project_raw_cst_rt_tab(l_J) := 0;
3251 x_project_burdned_cst_tab(l_J) := 0;
3252 x_project_burdned_cst_rt_tab(l_J) := 0;
3253
3254 x_project_cst_rt_date_tab(l_J) := l_project_cst_rt_date; /* Added for org Forecasting */
3255 x_project_cst_rt_type_tab(l_J) := l_project_cst_rt_type; /* Added for org Forecasting */
3256 x_project_cst_exch_rt_tab(l_J) := l_project_cst_exch_rt; /* Added for org Forecasting */
3257
3258 -- 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);
3259
3260 /* Expenditure Functional currency */
3261
3262 l_system_linkage := P_system_linkage(l_J);
3263
3264 x_exp_func_curr_code_tab(l_J) := l_exp_func_curr_code; /* Added for Org Forecasting */
3265 x_exp_func_cst_rt_date_tab(l_J) := l_exp_func_cst_rt_date; /* Added for Org Forecasting */
3266 x_exp_func_cst_rt_type_tab(l_J) := l_exp_func_cst_rt_type; /* Added for Org Forecasting */
3267 x_exp_func_cst_exch_rt_tab(l_J) := l_exp_func_cst_exch_rt; /* Added for Org Forecasting */
3268
3269 x_exp_func_raw_cst_rt_tab(l_J) := 0;
3270 x_exp_func_raw_cst_tab(l_J) := 0;
3271 x_exp_func_burdned_cst_rt_tab(l_J) := 0;
3272 x_exp_func_burdned_cst_tab(l_J) := 0;
3273
3274
3275 /* Transactional currency */
3276
3277 x_cst_txn_curr_code_tab(l_J) := l_cst_txn_curr_code; /* Added for Org Forecasting */
3278 x_txn_raw_cst_rt_tab(l_J) := 0;
3279 x_txn_raw_cst_tab(l_J) := 0;
3280 x_txn_burdned_cst_rt_tab(l_J) := 0;
3281 x_txn_burdned_cst_tab(l_J) := 0;
3282
3283 x_rev_txn_curr_code_tab(l_J) := l_rev_txn_curr_code; /* Added for Org Forecasting */
3284 x_txn_rev_bill_rt_tab(l_J) := 0; /* Added for Org Forecasting */
3285 x_txn_rev_raw_revenue_tab(l_J) := 0; /* Added for Org Forecasting */
3286
3287 x_rev_rejct_reason_tab(l_J) := NULL;
3288 x_cst_rejct_reason_tab(l_J) := NULL;
3289 x_burdned_rejct_reason_tab(l_J) := NULL;
3290 x_others_rejct_reason_tab(l_J) := NULL;
3291
3292 IF (x_others_rejct_reason_tab(l_J) IS NULL ) THEN /* Added for Org Forecasting , Added if got error from
3293 calling api { */
3294
3295 -- dbms_output.put_line(' step 3 '||l_J);
3296
3297 IF ( (p_calling_mode = 'ASSIGNMENT') OR (p_calling_mode = 'ROLE') ) THEN /* added for bug 2425570 */
3298 IF ( p_forecasting_type = 'PROJECT_FORECASTING') THEN /* Added this if for Org Forecasting */
3299 IF ( l_rate_calc_date_tab(1) >= p_asgn_start_date ) THEN
3300 null;
3301 ELSE
3302 l_rate_calc_date_tab(1) := p_asgn_start_date;
3303 END IF;
3304 END IF;
3305 END IF;
3306
3307 -- dbms_output.put_line('Index '||l_J);
3308 -- dbms_output.put_line('l_rate_calc_date_tab '||l_rate_calc_date_tab(l_J));
3309 -- dbms_output.put_line('p_quantity_tab '||p_quantity_tab(l_J));
3310 -- dbms_output.put_line('p_expenditure_org_id_tab '||p_expenditure_org_id_tab(l_J));
3311 -- dbms_output.put_line('p_expenditure_orgz_id_tab '||p_expenditure_orgz_id_tab(l_J));
3312 -- dbms_output.put_line('p_forecast_item_id_tab '||p_forecast_item_id_tab(l_J));
3313
3314 l_rate_calc_date := l_rate_calc_date_tab(l_J);
3315 l_quantity := p_quantity_tab(l_J);
3316 l_expenditure_org_id := p_expenditure_org_id_tab(l_J);
3317 l_expenditure_orgz_id := p_expenditure_orgz_id_tab(l_J);
3318 l_forecast_item_id := p_forecast_item_id_tab(l_J); /* added for bug 2212852 */
3319
3320 IF g1_debug_mode = 'Y' THEN
3321 PA_DEBUG.g_err_stage := 'RT41 : Entering PA_RATE_PVT_PKG.get_item_amount';
3322 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3323 PA_DEBUG.g_err_stage := 'prj type: '||p_project_type||' prj id:'||p_project_id;
3324 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3325 END IF;
3326
3327 -- dbms_output.put_line('before calling Get Item AMount the index is : '||l_J);
3328 PA_RATE_PVT_PKG.get_item_amount(
3329 p_calling_mode => p_calling_mode ,
3330 p_rate_calc_date => l_rate_calc_date ,
3331 p_item_id => p_item_id ,
3332 p_project_id => p_project_id ,
3333 p_quantity => l_quantity ,
3334 p_forecast_job_id => p_forecast_job_id ,
3335 p_forecast_job_group_id => p_forecast_job_group_id ,
3336 p_person_id => p_person_id ,
3337 p_expenditure_org_id => l_expenditure_org_id ,
3338 p_expenditure_type => p_expenditure_type ,
3339 p_expenditure_organization_id => l_expenditure_orgz_id ,
3340 p_project_org_id => p_project_org_id ,
3341 p_labor_cost_multi_name => p_labor_cost_multi_name ,
3342 p_expenditure_currency_code => NULL ,
3343 p_proj_cost_job_group_id => p_proj_cost_job_group_id ,
3344 p_job_cost_rate_schedule_id => p_job_cost_rate_schedule_id ,
3345 p_project_type => p_project_type ,
3346 p_task_id => p_task_id ,
3347 p_bill_rate_multiplier => p_bill_rate_multiplier ,
3348 p_project_bill_job_group_id => p_project_bill_job_group_id ,
3349 p_emp_bill_rate_schedule_id => p_emp_bill_rate_schedule_id ,
3350 p_job_bill_rate_schedule_id => p_job_bill_rate_schedule_id ,
3351 p_distribution_rule => p_distribution_rule ,
3352 p_forecast_item_id => l_forecast_item_id, /* Added for bug 2212852 */
3353 p_forecasting_type => p_forecasting_type, /* Added for bug 2212852 */
3354 p_amount_calc_mode => p_amount_calc_mode, /* Added for Org Forcasting */
3355 p_system_linkage => l_system_linkage, /* Added for Org Forcasting */
3356 p_assign_precedes_task => p_assign_precedes_task , /* Added for Org Forcasting */
3357 p_labor_schdl_discnt => p_labor_schdl_discnt , /* Added for Org Forcasting */
3358 p_labor_bill_rate_org_id => p_labor_bill_rate_org_id , /* Added for Org Forcasting */
3359 p_labor_std_bill_rate_schdl => p_labor_std_bill_rate_schdl ,/* Added for Org Forcasting */
3360 p_labor_schedule_fixed_date => p_labor_schedule_fixed_date ,/* Added for Org Forcasting */
3361 p_labor_sch_type => p_labor_sch_type , /* Added for Org Forcasting */
3362 p_projfunc_currency_code => p_projfunc_currency_code , /* MCB2 change */
3363 p_projfunc_rev_rt_dt_code => l_projfunc_rev_rt_dt_code,
3364 p_projfunc_rev_rt_date => l_projfunc_rev_rt_date, /* Added for Org Forecasting */
3365 p_projfunc_rev_rt_type => l_projfunc_rev_rt_type, /* Added for Org Forecasting */
3366 p_projfunc_rev_exch_rt => l_projfunc_rev_exch_rt, /* Added for Org Forecasting */
3367 p_projfunc_cst_rt_date => l_projfunc_cst_rt_date, /* Added for Org Forecasting */
3368 p_projfunc_cst_rt_type => l_projfunc_cst_rt_type, /* Added for Org Forecasting */
3369 x_projfunc_bill_rate => l_projfunc_bill_rate ,
3370 x_projfunc_raw_revenue => l_projfunc_raw_revenue ,
3371 x_projfunc_rev_rt_date => l_projfunc_rev_rt_date, /* Added for org Forecasting */
3372 x_projfunc_rev_rt_type => l_projfunc_rev_rt_type, /* Added for org Forecasting */
3373 x_projfunc_rev_exch_rt => l_projfunc_rev_exch_rt, /* Added for org Forecasting */
3374 x_projfunc_raw_cost => l_projfunc_raw_cost ,
3375 x_projfunc_raw_cost_rate => l_projfunc_raw_cost_rate ,
3376 x_projfunc_burdened_cost => l_projfunc_burdened_cost ,
3377 x_projfunc_burdened_cost_rate => l_projfunc_burdened_cost_rate ,
3378 x_projfunc_cst_rt_date => l_projfunc_cst_rt_date, /* Added for org Forecasting */
3379 x_projfunc_cst_rt_type => l_projfunc_cst_rt_type, /* Added for org Forecasting */
3380 x_projfunc_cst_exch_rt => l_projfunc_cst_exch_rt, /* Added for org Forecasting */
3381 p_project_currency_code => p_project_currency_code, /* Added for org Forecasting */
3382 p_project_rev_rt_dt_code => l_project_rev_rt_dt_code, /* Added for org Forecasting */
3383 p_project_rev_rt_date => l_project_rev_rt_date, /* Added for org Forecasting */
3384 p_project_rev_rt_type => l_project_rev_rt_type, /* Added for org Forecasting */
3385 p_project_rev_exch_rt => l_project_rev_exch_rt, /* Added for org Forecasting */
3386 p_project_cst_rt_date => l_project_cst_rt_date, /* Added for org Forecasting */
3387 p_project_cst_rt_type => l_project_cst_rt_type, /* Added for org Forecasting */
3388 x_project_bill_rt => l_project_bill_rate, /* Added for org Forecasting */
3389 x_project_raw_revenue => l_project_raw_revenue, /* Added for org Forecasting */
3390 x_project_rev_rt_date => l_project_rev_rt_date, /* Added for org Forecasting */
3391 x_project_rev_rt_type => l_project_rev_rt_type, /* Added for org Forecasting */
3392 x_project_rev_exch_rt => l_project_rev_exch_rt, /* Added for org Forecasting */
3393 x_project_raw_cst => l_project_raw_cost, /* Added for org Forecasting */
3394 x_project_raw_cst_rt => l_project_raw_cost_rate, /* Added for org Forecasting */
3395 x_project_burdned_cst => l_project_burdened_cost, /* Added for org Forecasting */
3396 x_project_burdned_cst_rt => l_project_burdened_cost_rate, /* Added for org Forecasting */
3397 x_project_cst_rt_date => l_project_cst_rt_date, /* Added for org Forecasting */
3398 x_project_cst_rt_type => l_project_cst_rt_type, /* Added for org Forecasting */
3399 x_project_cst_exch_rt => l_project_cst_exch_rt, /* Added for org Forecasting */
3400 x_exp_func_curr_code => l_exp_func_curr_code, /* Added for Org Forecasting */
3401 x_exp_func_raw_cost_rate => l_exp_func_raw_cost_rate ,
3402 x_exp_func_raw_cost => l_exp_func_raw_cost ,
3403 x_exp_func_burdened_cost_rate => l_exp_func_burdened_cost_rate ,
3404 x_exp_func_burdened_cost => l_exp_func_burdened_cost ,
3405 x_exp_func_cst_rt_date => l_exp_func_cst_rt_date, /* Added for org Forecasting */
3406 x_exp_func_cst_rt_type => l_exp_func_cst_rt_type, /* Added for org Forecasting */
3407 x_exp_func_cst_exch_rt => l_exp_func_cst_exch_rt, /* Added for org Forecasting */
3408 x_cst_txn_curr_code => l_cst_txn_curr_code, /* Added for Org Forecasting */
3409 x_txn_raw_cst_rt => l_txn_raw_cst_rt ,
3410 x_txn_raw_cst => l_txn_raw_cst,
3411 x_txn_burdned_cst_rt => l_txn_burdned_cst_rt,
3412 x_txn_burdned_cst => l_txn_burdned_cst,
3413 x_rev_txn_curr_code => l_rev_txn_curr_code, /* Added for Org Forecasting */
3414 x_txn_rev_bill_rt => l_txn_rev_bill_rt, /* Added for org Forecasting */
3415 x_txn_rev_raw_revenue => l_txn_rev_raw_revenue, /* Added for org Forecasting */
3416 x_error_msg => l_error_msg ,
3417 x_rev_rejct_reason => l_rev_rejct_reason ,
3418 x_cost_rejct_reason => l_cost_rejct_reason ,
3419 x_burdened_rejct_reason => l_burdened_rejct_reason ,
3420 x_others_rejct_reason => l_others_rejct_reason ,
3421 x_return_status => l_return_status ,
3422 x_msg_count => x_msg_count ,
3423 x_msg_data => x_msg_data );
3424
3425 IF g1_debug_mode = 'Y' THEN
3426 PA_DEBUG.g_err_stage := 'RT42 : Leaving PA_RATE_PVT_PKG.get_item_amount';
3427 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3428 PA_DEBUG.g_err_stage := 'err msg '||substr(l_error_msg,1,300);
3429 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3430 PA_DEBUG.g_err_stage := 'ret sts '||l_return_status||' rrr '||l_rev_rejct_reason||' crr '||l_cost_rejct_reason||' brr '||l_burdened_rejct_reason||' orr '||l_others_rejct_reason;
3431 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3432 PA_DEBUG.g_err_stage := 'msg cnt '||x_msg_count||' err msg '||substr(x_msg_data,1,300);
3433 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3434 END IF;
3435
3436 -- dbms_output.put_line(' I am in CALC RATE AMOUNT end of get_item_amount : '||l_J);
3437 -- 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);
3438 ----------------------------------------------------------------------------------------
3439 -- Assigning back local varibles to pl/sql tables
3440 -----------------------------------------------------------------------------------------
3441
3442 /* Project Functional currency */
3443
3444
3445 x_projfunc_bill_rt_tab(l_J) := l_projfunc_bill_rate;
3446 x_projfunc_raw_revenue_tab(l_J) := l_projfunc_raw_revenue;
3447
3448 x_projfunc_rev_rt_date_tab(l_J) := l_projfunc_rev_rt_date; /* Added for org Forecasting */
3449 x_projfunc_rev_rt_type_tab(l_J) := l_projfunc_rev_rt_type; /* Added for org Forecasting */
3450 x_projfunc_rev_exch_rt_tab(l_J) := l_projfunc_rev_exch_rt; /* Added for org Forecasting */
3451
3452 x_projfunc_raw_cst_tab(l_J) := l_projfunc_raw_cost;
3453 x_projfunc_raw_cst_rt_tab(l_J) := l_projfunc_raw_cost_rate;
3454 x_projfunc_burdned_cst_tab(l_J) := l_projfunc_burdened_cost;
3455 x_projfunc_burdned_cst_rt_tab(l_J) := l_projfunc_burdened_cost_rate;
3456
3457 x_projfunc_cst_rt_date_tab(l_J) := l_projfunc_cst_rt_date; /* Added for org Forecasting */
3458 x_projfunc_cst_rt_type_tab(l_J) := l_projfunc_cst_rt_type; /* Added for org Forecasting */
3459 x_projfunc_cst_exch_rt_tab(l_J) := l_projfunc_cst_exch_rt; /* Added for org Forecasting */
3460
3461 --dbms_output.put_line(' I am in CALC RATE AMOUNT PROJ FUNC : '||'projfunc bill : '||x_projfunc_bill_rt_tab(l_J)
3462 -- ||'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));
3463
3464 /* Project currency */
3465
3466 x_project_bill_rt_tab(l_J) := l_project_bill_rate;
3467 x_project_raw_revenue_tab(l_J) := l_project_raw_revenue;
3468
3469 x_project_rev_rt_date_tab(l_J) := l_project_rev_rt_date; /* Added for org Forecasting */
3470 x_project_rev_rt_type_tab(l_J) := l_project_rev_rt_type; /* Added for org Forecasting */
3471 x_project_rev_exch_rt_tab(l_J) := l_project_rev_exch_rt; /* Added for org Forecasting */
3472
3473 x_project_raw_cst_tab(l_J) := l_project_raw_cost;
3474 x_project_raw_cst_rt_tab(l_J) := l_project_raw_cost_rate;
3475 x_project_burdned_cst_tab(l_J) := l_project_burdened_cost;
3476 x_project_burdned_cst_rt_tab(l_J) := l_project_burdened_cost_rate;
3477
3478 x_project_cst_rt_date_tab(l_J) := l_project_cst_rt_date; /* Added for org Forecasting */
3479 x_project_cst_rt_type_tab(l_J) := l_project_cst_rt_type; /* Added for org Forecasting */
3480 x_project_cst_exch_rt_tab(l_J) := l_project_cst_exch_rt; /* Added for org Forecasting */
3481
3482 -- dbms_output.put_line(' I am in CALC RATE AMOUNT PROJ : '||'proj bill : '||x_project_bill_rt_tab(l_J)
3483 -- ||'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));
3484
3485 -- dbms_output.put_line(' I am in CALC RATE AMOUNT PROJ RATES : '||'proj bill : '||x_project_bill_rt_tab(l_J)
3486 -- ||'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));
3487
3488 /* Expenditure Functional currency */
3489
3490 x_exp_func_curr_code_tab(l_J) := l_exp_func_curr_code; /* Added for Org Forecasting */
3491 x_exp_func_cst_rt_date_tab(l_J) := l_exp_func_cst_rt_date; /* Added for Org Forecasting */
3492 x_exp_func_cst_rt_type_tab(l_J) := l_exp_func_cst_rt_type; /* Added for Org Forecasting */
3493 x_exp_func_cst_exch_rt_tab(l_J) := l_exp_func_cst_exch_rt; /* Added for Org Forecasting */
3494
3495 x_exp_func_raw_cst_rt_tab(l_J) := l_exp_func_raw_cost_rate;
3496 x_exp_func_raw_cst_tab(l_J) := l_exp_func_raw_cost;
3497 x_exp_func_burdned_cst_rt_tab(l_J) := l_exp_func_burdened_cost_rate;
3498 x_exp_func_burdned_cst_tab(l_J) := l_exp_func_burdened_cost;
3499
3500
3501 -- 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));
3502
3503 /* Transactional currency */
3504
3505 x_cst_txn_curr_code_tab(l_J) := l_cst_txn_curr_code; /* Added for Org Forecasting */
3506 x_txn_raw_cst_rt_tab(l_J) := l_txn_raw_cst_rt;
3507 x_txn_raw_cst_tab(l_J) := l_txn_raw_cst;
3508 x_txn_burdned_cst_rt_tab(l_J) := l_txn_burdned_cst_rt;
3509 x_txn_burdned_cst_tab(l_J) := l_txn_burdned_cst;
3510
3511 x_rev_txn_curr_code_tab(l_J) := l_rev_txn_curr_code; /* Added for Org Forecasting */
3512 x_txn_rev_bill_rt_tab(l_J) := l_txn_rev_bill_rt; /* Added for Org Forecasting */
3513 x_txn_rev_raw_revenue_tab(l_J) := l_txn_rev_raw_revenue; /* Added for Org Forecasting */
3514
3515 x_rev_rejct_reason_tab(l_J) := l_rev_rejct_reason;
3516 x_cst_rejct_reason_tab(l_J) := l_cost_rejct_reason;
3517 x_burdned_rejct_reason_tab(l_J) := l_burdened_rejct_reason;
3518 x_others_rejct_reason_tab(l_J) := l_others_rejct_reason;
3519
3520 x_return_status := l_return_status;
3521 x_error_msg := l_error_msg;
3522
3523 -- dbms_output.put_line(' I am in CALC RATE AMT ERROR : '||l_J ||x_cst_rejct_reason_tab(l_J));
3524 -- dbms_output.put_line(' I am in CALC RATE AMT ERROR 1 : '||l_J ||x_cst_rejct_reason_tab(l_J));
3525 -- dbms_output.put_line(' I am in CALC RATE AMOUNT ERROR : '||'l_cost_rejct_reason : '||x_cst_rejct_reason_tab(l_J));
3526 -- -- 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));
3527 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
3528 x_return_status := l_return_status;
3529 END IF;
3530 END IF; /* End of other rejection if }*/
3531 END IF; /* } End of if date is null */
3532 END LOOP;
3533 ELSE
3534 -- DBMS_OUTPUT.PUT_LINE(' ERROR USER DEFINED ');
3535 RAISE NO_DATA_FOUND;
3536 END IF; /* Ending of table of record check if */
3537
3538 -- dbms_output.put_line(' I am in CALC RATE AMT ERROR END : ');
3539
3540 IF g1_debug_mode = 'Y' THEN
3541 PA_DEBUG.g_err_stage := 'RT43 : Leaving PA_RATE_PVT_PKG.calc rate amount';
3542 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3543 PA_DEBUG.Reset_Curr_Function;
3544 END IF;
3545
3546 EXCEPTION
3547 WHEN OTHERS THEN
3548 IF g1_debug_mode = 'Y' THEN
3549 PA_DEBUG.Reset_Curr_Function;
3550 END IF;
3551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3552 x_error_msg := SUBSTR(SQLERRM,1,30);
3553 /* Checking error condition. Added for bug 2218386 */
3554 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
3555 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
3556 p_procedure_name => 'Calc_Rate_Amount');
3557 RAISE;
3558 END IF;
3559
3560 END calc_rate_amount;
3561
3562
3563
3564 /* Added for performance bug 2691192, it replaces the use of view pa_rep_period_dates_v */
3565
3566 -- This procedure will display information about period types such as the name of the period
3567 -- and the start and end dates.
3568 -- Input parameters
3569 -- Parameters Type Required Description
3570 -- p_period_type VARCHAR2 YES Period type
3571 -- p_period_type DATE YES Schedule completion date
3572 -- Out parameters
3573 -- x_period_name VARCHAR2 Period name
3574 -- x_start_date DATE Start date of the period
3575 -- x_end_datet DATE End date of the period
3576 -- x_error_value VARCHAR2 Error status
3577 --
3578
3579 PROCEDURE get_rep_period_dates(
3580 p_period_type IN VARCHAR2 ,
3581 p_completion_date IN DATE,
3582 x_period_year OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3583 x_period_name OUT NOCOPY gl_periods.period_name%TYPE, --File.Sql.39 bug 4440895
3584 x_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
3585 x_end_date OUT NOCOPY DATE , --File.Sql.39 bug 4440895
3586 x_error_value OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3587 )
3588 IS
3589
3590 l_period_year NUMBER;
3591 l_period_name gl_periods.period_name%TYPE;
3592 l_start_date DATE;
3593 l_end_date DATE;
3594
3595 BEGIN
3596
3597 IF g1_debug_mode = 'Y' THEN
3598 PA_DEBUG.Set_Curr_Function( p_function => 'get_rep_period_dates ');
3599 PA_DEBUG.g_err_stage := 'RT 101 : Entering PA_RATE_PVT_PKG.get_rep_period_dates';
3600 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3601 END IF;
3602
3603 x_error_value := 'NO_ERROR';
3604
3605 IF (p_period_type = 'GL' ) THEN
3606
3607 IF g1_debug_mode = 'Y' THEN
3608 PA_DEBUG.g_err_stage := 'RT 102 : get_rep_period_dates-> Inside GL select prd typ '||p_period_type;
3609 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3610 END IF;
3611
3612 SELECT
3613 glper.period_year,
3614 glper.period_name,
3615 glper.start_date,
3616 glper.end_date
3617 INTO
3618 l_period_year,
3619 l_period_name,
3620 l_start_date,
3621 l_end_date
3622 FROM pa_implementations imp,
3623 gl_sets_of_books gl,
3624 gl_periods glper,
3625 gl_period_statuses glpersts
3626 WHERE imp.set_of_books_id = gl.set_of_books_id
3627 AND gl.period_set_name = glper.period_set_name
3628 AND gl.accounted_period_type = glper.period_type
3629 AND glpersts.set_of_books_id = gl.set_of_books_id
3630 AND glpersts.period_type = glper.period_type
3631 AND glpersts.period_name = glper.period_name
3632 AND glpersts.period_year = glper.period_year
3633 AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID
3634 AND p_completion_date BETWEEN glper.start_date AND glper.end_date
3635 AND EXISTS ( SELECT NULL
3636 FROM gl_date_period_map glmaps
3637 WHERE 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 EXISTS ( SELECT NULL
3641 FROM gl_lookups prsts
3642 WHERE prsts.lookup_code IN('C','F','N','O','P')
3643 AND prsts.lookup_type ='CLOSING_STATUS'
3644 AND glpersts.closing_status = prsts.lookup_code);
3645
3646 IF g1_debug_mode = 'Y' THEN
3647 PA_DEBUG.g_err_stage := 'RT 103 : get_rep_period_dates-> Passed GL select prd nam '||l_period_name;
3648 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3649 END IF;
3650
3651 ELSIF (p_period_type = 'PA' ) THEN
3652
3653 IF g1_debug_mode = 'Y' THEN
3654 PA_DEBUG.g_err_stage := 'RT 104 : get_rep_period_dates-> Inside PA select prd typ '||p_period_type;
3655 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3656 END IF;
3657
3658 SELECT
3659 glp.period_year
3660 , pap.period_name
3661 , pap.start_date
3662 , pap.end_date
3663 INTO
3664 l_period_year,
3665 l_period_name,
3666 l_start_date,
3667 l_end_date
3668 FROM pa_periods pap,
3669 gl_period_statuses glp,
3670 pa_implementations paimp
3671 WHERE pap.gl_period_name = glp.period_name
3672 AND glp.set_of_books_id = paimp.set_of_books_id
3673 AND glp.application_id = Pa_Period_Process_Pkg.Application_id
3674 AND glp.adjustment_period_flag = 'N'
3675 AND p_completion_date BETWEEN pap.start_date and pap.end_date
3676 AND EXISTS (SELECT NULL
3677 FROM pa_lookups pal
3678 WHERE pal.lookup_type = 'CLOSING STATUS'
3679 AND pal.lookup_code = pap.status);
3680
3681 IF g1_debug_mode = 'Y' THEN
3682 PA_DEBUG.g_err_stage := 'RT 104 : get_rep_period_dates-> Passed PA select prd nam '||l_period_name;
3683 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3684 END IF;
3685
3686 ELSIF (p_period_type = 'QR' ) THEN
3687
3688 IF g1_debug_mode = 'Y' THEN
3689 PA_DEBUG.g_err_stage := 'RT 105 : get_rep_period_dates-> Inside QR select prd typ '||p_period_type;
3690 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3691 END IF;
3692
3693 SELECT
3694 period_yr
3695 , period_nam
3696 , start_dt
3697 , end_dt
3698 INTO
3699 l_period_year,
3700 l_period_name,
3701 l_start_date,
3702 l_end_date
3703 FROM (
3704 SELECT
3705 glper.period_year period_yr,
3706 TO_CHAR(glper.quarter_num) period_nam,
3707 MIN(glper.start_date) start_dt,
3708 MAX(glper.end_date) end_dt
3709 FROM pa_implementations imp,
3710 gl_sets_of_books gl,
3711 gl_periods glper,
3712 gl_period_statuses glpersts,
3713 gl_date_period_map glmaps
3714 WHERE imp.set_of_books_id = gl.set_of_books_id
3715 AND gl.period_set_name = glper.period_set_name
3716 AND gl.accounted_period_type = glper.period_type
3717 AND glpersts.set_of_books_id = gl.set_of_books_id
3718 AND glpersts.period_type = glper.period_type
3719 AND glpersts.period_name = glper.period_name
3720 AND glpersts.period_year = glper.period_year
3721 AND glmaps.period_type = glper.period_type
3722 AND glmaps.period_name = glper.period_name
3723 AND glmaps.period_set_name = glper.period_set_name
3724 AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID
3725 AND EXISTS (SELECT null
3726 FROM gl_lookups prsts
3727 WHERE prsts.lookup_code IN('C','F','N','O','P')
3728 AND prsts.lookup_type ='CLOSING_STATUS'
3729 AND glpersts.closing_status = prsts.lookup_code)
3730 GROUP BY glper.period_year,
3731 glper.quarter_num
3732 )
3733 WHERE p_completion_date BETWEEN start_dt AND end_dt;
3734
3735 IF g1_debug_mode = 'Y' THEN
3736 PA_DEBUG.g_err_stage := 'RT 106 : get_rep_period_dates-> Passed QR select prd nam '||l_period_name;
3737 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3738 END IF;
3739
3740 ELSIF (p_period_type = 'YR' ) THEN
3741
3742 IF g1_debug_mode = 'Y' THEN
3743 PA_DEBUG.g_err_stage := 'RT 107 : get_rep_period_dates-> Inside YR select prd typ '||p_period_type;
3744 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3745 END IF;
3746
3747 SELECT
3748 period_yr
3749 , period_nam
3750 , start_dt
3751 , end_dt
3752 INTO
3753 l_period_year,
3754 l_period_name,
3755 l_start_date,
3756 l_end_date
3757 FROM (
3758 SELECT
3759 glper.period_year period_yr,
3760 TO_CHAR(glper.period_year) period_nam,
3761 MIN(glper.start_date) start_dt,
3762 MAX(glper.end_date) end_dt
3763 FROM pa_implementations imp,
3764 gl_sets_of_books gl,
3765 gl_periods glper,
3766 gl_period_statuses glpersts,
3767 gl_date_period_map glmaps
3768 WHERE imp.set_of_books_id = gl.set_of_books_id
3769 AND gl.period_set_name = glper.period_set_name
3770 AND gl.accounted_period_type = glper.period_type
3771 AND glpersts.set_of_books_id = gl.set_of_books_id
3772 AND glpersts.period_type = glper.period_type
3773 AND glpersts.period_name = glper.period_name
3774 AND glpersts.period_year = glper.period_year
3775 AND glmaps.period_type = glper.period_type
3776 AND glmaps.period_name = glper.period_name
3777 AND glmaps.period_set_name = glper.period_set_name
3778 AND glpersts.application_id = PA_Period_Process_Pkg.Application_ID
3779 AND EXISTS (SELECT null
3780 FROM gl_lookups prsts
3781 WHERE prsts.lookup_code IN('C','F','N','O','P')
3782 AND prsts.lookup_type ='CLOSING_STATUS'
3783 AND glpersts.closing_status = prsts.lookup_code)
3784 GROUP BY glper.period_year
3785 )
3786 WHERE p_completion_date BETWEEN start_dt AND end_dt;
3787
3788 IF g1_debug_mode = 'Y' THEN
3789 PA_DEBUG.g_err_stage := 'RT 108 : get_rep_period_dates-> Passed YR select prd nam '||l_period_name;
3790 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3791 END IF;
3792
3793 ELSIF (p_period_type = 'GE' ) THEN
3794
3795 IF g1_debug_mode = 'Y' THEN
3796 PA_DEBUG.g_err_stage := 'RT 109 : get_rep_period_dates-> Inside GE select prd typ '||p_period_type;
3797 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3798 END IF;
3799
3800 SELECT
3801 period_yr
3802 , period_nam
3803 , start_dt
3804 , end_dt
3805 INTO
3806 l_period_year,
3807 l_period_name,
3808 l_start_date,
3809 l_end_date
3810 FROM (
3811 SELECT
3812 period_year period_yr,
3813 TO_CHAR((NEXT_DAY(TO_DATE('01/01/'||TO_CHAR(period_Year),'MM/DD/YYYY'),
3814 TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7) period_nam,
3815 ( ((NEXT_DAY(TO_DATE('01/01/'||TO_CHAR(period_Year),'MM/DD/YYYY'),
3816 TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7) - 6 ) start_dt,
3817 ( (NEXT_DAY(TO_DATE('01/01/'||TO_CHAR(period_Year),'MM/DD/YYYY'),
3818 TO_NUMBER(FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY'))) -1 ) + (seq_number-1) * 7 ) end_dt
3819 FROM pa_rep_year_cal_v,
3820 pa_rep_seq_number
3821 WHERE seq_number BETWEEN 1 AND 53
3822 )
3823 WHERE p_completion_date BETWEEN start_dt AND end_dt;
3824
3825 IF g1_debug_mode = 'Y' THEN
3826 PA_DEBUG.g_err_stage := 'RT 110 : get_rep_period_dates-> Passed GE select prd nam '||l_period_name;
3827 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3828 END IF;
3829
3830 END IF;
3831
3832 x_period_year := l_period_year;
3833 x_period_name := l_period_name;
3834 x_start_date := l_start_date;
3835 x_end_date := l_end_date;
3836
3837 IF g1_debug_mode = 'Y' THEN
3838 PA_DEBUG.g_err_stage := 'RT 111 : Leaving PA_RATE_PVT_PKG.get_rep_period_dates';
3839 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
3840 PA_DEBUG.Reset_Curr_Function;
3841 END IF;
3842
3843 EXCEPTION
3844 WHEN NO_DATA_FOUND THEN
3845 x_error_value := 'NO_DATA_FOUND';
3846 IF g1_debug_mode = 'Y' THEN
3847 PA_DEBUG.Reset_Curr_Function;
3848 END IF;
3849 NULL;
3850 WHEN TOO_MANY_ROWS THEN
3851 x_error_value := 'TOO_MANY_ROWS';
3852 IF g1_debug_mode = 'Y' THEN
3853 PA_DEBUG.Reset_Curr_Function;
3854 END IF;
3855 NULL;
3856 WHEN OTHERS THEN
3857 IF g1_debug_mode = 'Y' THEN
3858 PA_DEBUG.Reset_Curr_Function;
3859 END IF;
3860
3861 /* ATG Changes */
3862
3863 x_period_name := null;
3864 x_start_date := null;
3865 x_end_date := null;
3866
3867 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
3868 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RATE_PVT_PKG',
3869 p_procedure_name => 'get_rep_period_dates');
3870 RAISE;
3871 END IF;
3872
3873 END get_rep_period_dates;
3874
3875
3876 END PA_RATE_PVT_PKG;
3877