[Home] [Help]
PACKAGE BODY: APPS.PA_COST_RATE_PUB
Source
4 -- Start of comments
1 PACKAGE BODY PA_COST_RATE_PUB AS
2 /* $Header: PAXPCRTB.pls 120.18 2011/07/05 06:59:41 racheruv ship $ */
3
5 -- API name : get_labor_rate
6 -- Type : Public
7 -- Pre-reqs : None.
8 -- Function : Returns Labor Cost Rate for an Employee.
9 -- Parameters : Person Id, Transaction Date. Organization Id and Job Id are optional.
10 -- IN : p_person_id IN NUMBER Required
11 -- Id of the person for whom the rate is to be found.
12 -- p_txn_date IN DATE Required
13 -- The Date on which the rate is required.
14 -- x_organization_id IN NUMBER Optional
15 -- Organization to which the transaction is charged to.
16 -- p_org_id IN NUMBER Optional
17 -- Expenditure Org Id of the transaction
18 -- x_job_id IN NUMBER Optional
19 -- Job of the person.
20 -- Version : Current version 1.0
21 -- Initial version 1.0
22 -- End of comments
23 procedure get_labor_rate ( p_person_id IN per_all_people_f.person_id%TYPE
24 ,p_txn_date IN date
25 ,p_calling_module IN varchar2 default 'STAFFED'
26 ,p_org_id IN pa_expenditures_all.org_id%TYPE default NULL /*2879644*/
27 ,x_job_id IN OUT NOCOPY pa_expenditure_items_all.job_id%TYPE
28 ,x_organization_id IN OUT NOCOPY pa_expenditures_all.incurred_by_organization_id%TYPE
29 ,x_cost_rate OUT NOCOPY pa_bill_rates_all.rate%TYPE
30 ,x_start_date_active OUT NOCOPY date
31 ,x_end_date_active OUT NOCOPY date
32 ,x_org_labor_sch_rule_id OUT NOCOPY pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE
33 ,x_costing_rule OUT NOCOPY pa_compensation_rule_sets.compensation_rule_set%TYPE
34 ,x_rate_sch_id OUT NOCOPY pa_std_bill_rate_schedules_all.bill_rate_sch_id%TYPE
35 ,x_cost_rate_curr_code OUT NOCOPY gl_sets_of_books.currency_code%TYPE
36 ,x_acct_rate_type OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE
37 ,x_acct_rate_date_code OUT NOCOPY pa_implementations_all.acct_rate_date_code%TYPE
38 ,x_acct_exch_rate OUT NOCOPY pa_org_labor_sch_rule.acct_exchange_rate%TYPE
39 ,x_ot_project_id OUT NOCOPY pa_projects_all.project_id%TYPE
40 ,x_ot_task_id OUT NOCOPY pa_tasks.task_id%TYPE
41 ,x_err_stage OUT NOCOPY number
42 ,x_err_code OUT NOCOPY varchar2
43 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
44 )
45 is
46
47 USER_EXCEPTION EXCEPTION;
48
49 l_debug_mode varchar2(1);
50 l_stage varchar2(300);
51 l_use_cache varchar2(1);
52 l_num_dummy pa_expenditure_items_all.expenditure_item_id%TYPE;
53
54 l_override_flag varchar2(1) := 'N' ;
55 l_override_type pa_compensation_details.override_type%TYPE;
56 l_sch_type pa_std_bill_rate_schedules_all.schedule_type%TYPE;
57 l_job_group_id pa_std_bill_rate_schedules_all.job_group_id%TYPE;
58 l_dest_job_id pa_bill_rates_all.job_id%TYPE;
59 l_costing_method pa_compensation_rule_sets.costing_method%TYPE;
60 l_curr_org_id pa_expenditures_all.org_id%TYPE; /* Added for bug 7365397 */
61 l_rate_source_code pa_compensation_rule_sets.rate_source_code%TYPE; /* added for 12.2 . bug 10253400 */
62 l_base_hours pa_org_labor_sch_rule.base_hours%TYPE; /* 12.2 payroll intg .. bug 11811475 */
63 l_rbc_elem_type_id pa_org_labor_sch_rule.rbc_element_type_id%TYPE; /* 12.2 payroll intg .. bug 11811475 */
64 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
65 l_rbc_rate number;
66
67 begin
68
69 ----------------- initializaton ------------------------------
70 if pa_cc_utils.g_debug_mode then
71 l_debug_mode := 'Y';
72 else
73 l_debug_mode := 'N';
74 end if;
75
76
77
78 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
79
80 IF ( l_debug_mode = 'Y' ) THEN
81 pa_debug.set_process( x_process => 'PLSQL'
82 ,x_debug_mode => l_debug_mode
83 );
84 pa_cc_utils.set_curr_function('PA_COST_RATE_PUB.get_labor_rate');
85 -- pa_cc_utils.log_message('Start ');
86 END IF;
87
88 l_stage := 'Input person_id [' || to_char(p_person_id) || '] job_id ['
89 || to_char(x_job_id) || '] txn_date ['
90 || to_char(p_txn_date) || '] organization_id [' || to_char(x_organization_id) || ']';
91 if ( l_debug_mode = 'Y' )
92 then
93 pa_cc_utils.log_message(l_stage);
94 end if;
95
96 /* Bug 9913685: If p_org_id is passed through the concurrent program, then that should be assigned to l_curr_org_id. In case
97 it is not passed then it needs to be derived using pa_moac_utils.get_current_org_id. This API would return
98 the org_id in case of single-org context and would return NULL in case of multi-org context. The nvl handling below
99 makes sure to pick up all orgs in case of multi-org context. */
100
101 l_curr_org_id := p_org_id; -- bug 9913685
102
103 if l_curr_org_id is null then -- bug 9913685: added if condition
104 l_curr_org_id := pa_moac_utils.get_current_org_id; /* Added for bug 7365397 */
105 end if;
106
107 /*===================================================================+
108 | If the calling module is 'REQUIREMENT' - both job id and |
109 | organization id should be provided. |
110 +===================================================================*/
111 if ( p_calling_module = 'REQUIREMENT' and
112 (x_job_id is null or x_organization_id is null ) )
113 then
114 x_err_code := 'NO_JOB_ID' ;
115 /*Bug fix:3089560 Plsql numeric value error causes without resetting stack*/
116 pa_cc_utils.reset_curr_function;
117 return;
118 end if;
119
120 /*2879644 Commented for bug 7158405
121 if p_org_id is not null then
122 g_ou_id := p_org_id;
123 end if ;
124 2879644*/
125
126 /*=============================+
127 | Derive Functional Currency. |
128 +=============================*/
129 /*======================================================+
130 | Bug 2879644. Added NVL() while selecting org_id. |
131 | g_func_curr is not used anywhere and hence selecting |
132 | currency_code can be removed later. |
133 +======================================================*/
134 if ( g_func_curr is null or g_ou_id is null ) /* Reverted the fix done via 6908073 for bug 7365397 */
135 /* Removed the g_ou_id nul lcondition for bug 6908073 */
136 then
137 l_stage := 'Selecting Functional Currency, l_curr_org_id: ' || l_curr_org_id ||', p_org_id: ' || p_org_id;
138 if ( l_debug_mode = 'Y' )
139 then
140 pa_cc_utils.log_message(l_stage);
141 end if;
142 select nvl(p_org_id, imp.org_id)
143 ,sob.currency_code
144 into g_ou_id
145 ,g_func_curr
146 from gl_sets_of_books sob
147 ,pa_implementations imp
148 where imp.set_of_books_id = sob.set_of_books_id and
149 imp.org_id = nvl(l_curr_org_id, imp.org_id); /* Reverted the fix of 7191479 for bug 7365397 */
150 /* nvl(p_org_id, imp.org_id); /* Added the condition for bug 6908073 */
151 /* Modified the above select for bug 7191479 */
152 /* Bug 9913685: added nvl in the above condition */
153
154 l_stage := 'Org Id [' || to_char(g_ou_id) || '] Func Curr [' || g_func_curr || ']';
155 if ( l_debug_mode = 'Y' )
156 then
157 pa_cc_utils.log_message(l_stage);
158 end if;
159 end if; /* g_func_curr is null */
160
161 /*====================================================================+
162 | If input organization_id is null, derive it based on the employee. |
163 +====================================================================*/
164 --if ( x_organization_id is null or x_job_id is null ) Commented for bug 5004080
165 if ( p_person_id is not null and (x_organization_id is null or x_job_id is null) )
166 then
167 l_stage := 'Selecting Organization and Job Ids';
168 if ( l_debug_mode = 'Y' )
169 then
170 pa_cc_utils.log_message(l_stage);
171 end if;
172
173 /* cwk changes : Modified stmt to derive the Organization Id, Job Id
174 for a Person Id of a contingent worker also*/
175 begin
176 select nvl(x_organization_id, per.organization_id)
177 ,nvl(x_job_id, per.job_id)
178 into x_organization_id
179 ,x_job_id
180 from per_assignments_f per
181 ,per_assignment_status_types type
182 where trunc(p_txn_date) between trunc(effective_start_date) and trunc(nvl(effective_end_date,p_txn_date))
183 and per.person_id = p_person_id
184 and per.primary_flag = 'Y'
185 and per.assignment_type in ('E', 'C')
186 and per.assignment_status_type_id = type.assignment_status_type_id
187 and type.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
188 ;
189 exception
190 when no_data_found then
191 l_stage := 'No records found while fetching job id';
192 if ( l_debug_mode = 'Y' ) then
193 pa_cc_utils.log_message(l_stage);
194 end if;
195 end;
196
197 l_stage := 'Organization Id [' || to_char(x_organization_id) || '] Job Id [' || to_char(x_job_id) || ']';
198 if ( l_debug_mode = 'Y' )
199 then
200 pa_cc_utils.log_message(l_stage);
201 end if;
202 end if; /* x_organization_id is null */
203 ----------------- initializaton end ------------------------------
204 /*======================================+
205 | See whether the cache can be reused. |
206 +======================================*/
207 if ( (P_Called_From <> 'R') AND /* Added for 3405326 */
208 x_organization_id = g_rt_organization_id
209 and p_calling_module = g_rt_calling_module
210 and trunc(p_txn_date) between trunc(g_rt_start_date_active) and trunc(nvl(g_rt_end_date_active, p_txn_date))
211 )
212 then
213 if ( g_rt_sch_type = 'EMPLOYEE' and p_person_id = g_rt_person_id )
214 then
215 l_use_cache := 'Y';
216 end if;
217 if ( g_rt_sch_type = 'JOB' and x_job_id = g_rt_job_id )
218 then
219 l_use_cache := 'Y';
220 end if;
221 end if;
222 if ( l_use_cache = 'Y' )
223 then
224 x_organization_id := g_rt_organization_id ;
225 x_cost_rate := g_rt_cost_rate ;
226 x_start_date_active := g_rt_start_date_active ;
227 x_end_date_active := g_rt_end_date_active ;
228 x_org_labor_sch_rule_id := g_rt_org_labor_sch_rule_id ;
229 x_costing_rule := g_rt_costing_rule ;
230 x_rate_sch_id := g_rt_rate_sch_id ;
231 x_cost_rate_curr_code := g_rt_cost_rate_curr_code ;
232 x_acct_rate_type := g_rt_acct_rate_type ;
233 x_acct_rate_date_code := g_rt_acct_rate_date_code ;
234 x_acct_exch_rate := g_rt_acct_exch_rate ;
235 x_ot_project_id := g_rt_ot_project_id ;
236 x_ot_task_id := g_rt_ot_task_id ;
237 x_err_stage := g_rt_err_stage ;
238 x_err_code := g_rt_err_code ;
239 pa_cc_utils.reset_curr_function;
240 return;
241 end if;
242
243 if ( p_calling_module <> 'REQUIREMENT' )
244 then
245 /*================================================+
246 | Check if there is an override for this person. |
247 +================================================*/
248 l_override_flag := 'Y' ;
249 l_stage := 'Selecting Override';
250 if ( l_debug_mode = 'Y' )
251 then
252 pa_cc_utils.log_message(l_stage);
253 end if;
254 begin
255 select detail.compensation_rule_set
256 ,detail.hourly_cost_rate
257 ,detail.rate_schedule_id
258 ,detail.override_type
259 ,detail.cost_rate_currency_code
260 ,detail.acct_rate_type
261 ,detail.acct_rate_date_code
262 ,detail.acct_exchange_rate
263 ,detail.start_date_active
264 ,detail.end_date_active
265 ,detail.rbc_element_type_id
266 into x_costing_rule
267 ,x_cost_rate
268 ,x_rate_sch_id
269 ,l_override_type
270 ,x_cost_rate_curr_code
271 ,x_acct_rate_type
272 ,x_acct_rate_date_code
273 ,x_acct_exch_rate
274 ,x_start_date_active
275 ,x_end_date_active
276 ,l_rbc_elem_type_id
277 from pa_compensation_details_all detail /*2879644:Added ALL*/
278 where trunc(p_txn_date) between trunc(detail.start_date_active)
279 and trunc(nvl(detail.end_date_active,p_txn_date))
280 and NVL(detail.org_id,-99) = NVL(NVL(p_org_id, g_ou_id),-99) /*2879644 :Added org_id join 7158405 */
281 and detail.person_id = p_person_id;
282 exception
283 when no_data_found then
284 l_override_flag := 'N';
285 when too_many_rows then
286 x_err_code := 'DUP_REC';
287 end;
288 if ( x_err_code is not null )
289 then
290 raise USER_EXCEPTION;
291 end if;
292 if ( l_override_type = 'COST_RATE' and x_cost_rate is null )
293 then
294 x_err_code := 'NO_COST_RATE';
295 raise USER_EXCEPTION;
296 end if;
297 if ( l_override_type = 'COST_RATE_SCHEDULE' and x_rate_sch_id is null )
298 then
299 x_err_code := 'NO_COST_RATE_SCH';
300 raise USER_EXCEPTION;
301 end if;
302
303 if ( l_override_flag = 'Y' )
304 then
305 l_stage := 'comp rule [' || x_costing_rule
306 || '] rate [' || to_char( x_cost_rate)
307 || '] x_rate_sch_id [' || to_char( x_rate_sch_id)
308 || '] override type [' || l_override_type
309 || '] override flag [' || l_override_flag
310 || '] crcc type [' || x_cost_rate_curr_code
311 || '] art [' || x_acct_rate_type
312 || '] ardc [' || x_acct_rate_date_code
313 || '] aer [' || to_char(x_acct_exch_rate)
314 || '] start [' || to_char(x_start_date_active)
315 || '] end [' || to_char(x_end_date_active)
316 || ']';
317 if ( l_debug_mode = 'Y' )
318 then
319 pa_cc_utils.log_message(l_stage);
320 end if;
321 /*
322 * If the compensation rule available at the override level
323 * and if the costing method id 'Extension', set rate to null
324 * and return.
325 */
326 if ( x_costing_rule is not null )
327 then
328 l_stage := 'selecting costing method from rule [' || x_costing_rule || ']';
329 if ( l_debug_mode = 'Y' ) then
330 pa_cc_utils.log_message(l_stage);
331 end if;
332
333 /* 12.2 payroll intg .. bug 10253400. changes for 12.2:
334 costing method holds 2 values:
335 'STANDARD_COSTING', 'ACTUAL_ACCRUAL'.
336 The rate source would hold the source of the rates. */
337
338 select rule.costing_method, rule.rate_source_code
339 into l_costing_method, l_rate_source_code
340 from pa_compensation_rule_sets rule
341 where rule.compensation_rule_set = x_costing_rule
342 ;
343 if ( l_debug_mode = 'Y' ) then
344 pa_cc_utils.log_message('l_costing_method '||l_costing_method);
345 end if;
346
347 -- 12.2 payroll intg .. verify against the rate source
348 --if ( l_costing_method = 'LABOR_COST_EXTN' )
349 if ( l_rate_source_code = 'EXTENSION' )
350 then
351 x_cost_rate := null ;
352 raise USER_EXCEPTION;
353 end if; /*rate source is 'extension'*/
354 end if; /* costing rule is not null */
355 end if; /* override_flag */
356 end if; /* calling module */
357
358 /*=================================================+
359 | If cost rate is obtained, return to the caller. |
360 +=================================================*/
361 if ( l_override_flag = 'Y' and x_cost_rate is not null )
362 then
363 raise USER_EXCEPTION;
364 end if;
365
366 /*==========================================================+
367 | In the absense of an override, traverse the hierarchy. |
368 +==========================================================*/
369 if ( l_override_flag <> 'Y' or x_costing_rule is null)
370 then
371 l_stage := 'Calling get_orgn_level_costing_info with g_ou_id [' || to_char(g_ou_id) ||
372 '] x_organization_id [' || to_char(x_organization_id) ||
373 '] p_person_id [' || to_char(p_person_id) ||
374 '] x_job_id [' || to_char(x_job_id) || ']';
375 if ( l_debug_mode = 'Y' )
376 then
377 pa_cc_utils.log_message(l_stage);
378 end if;
379 begin
380 pa_cost_rate_pub.get_orgn_level_costing_info
381 ( p_org_id => nvl(p_org_id, g_ou_id) --7158405
382 ,p_organization_id => x_organization_id
383 ,p_person_id => p_person_id
384 ,p_job_id => x_job_id
385 ,p_txn_date => p_txn_date
386 ,p_calling_module => p_calling_module
387 ,x_org_labor_sch_rule_id => l_num_dummy
388 ,x_costing_rule => x_costing_rule
389 ,x_rate_sch_id => x_rate_sch_id
390 ,x_ot_project_id => x_ot_project_id
391 ,x_ot_task_id => x_ot_task_id
392 ,x_base_hours => l_base_hours /* 12.2 payroll intg .. bug 11811475 */
393 ,x_rbc_elem_type_id => l_rbc_elem_type_id
394 ,x_cost_rate_curr_code => x_cost_rate_curr_code
395 ,x_acct_rate_type => x_acct_rate_type
396 ,x_acct_rate_date_code => x_acct_rate_date_code
397 ,x_acct_exch_rate => x_acct_exch_rate
398 ,x_err_stage => x_err_stage
399 ,x_err_code => x_err_code
400 ,p_called_from => P_Called_from /*3405326*/
401 );
402 exception
403 when others then
404 if ( l_debug_mode = 'Y' ) then
405 pa_cc_utils.log_message('when others error '||substr(SQLERRM,1,300));
406 end if;
407 end;
408
409 if ( x_err_code is not null )
410 then
411 raise USER_EXCEPTION;
412 end if;
413
414 end if;
415
416 /*===============================================================+
417 | At this point, if any of the vital attributes |
418 | (costing rule, schedule/rate) are not found its an exception. |
419 | costing rule, (schedule or rate) |
420 +===============================================================*/
421 if ( x_costing_rule is null )
422 then
423 x_err_code := 'NO_COSTING_RULE';
424 raise USER_EXCEPTION;
425 end if;
426
427 /* 12.2 payroll intg .. bug 10253400. changes for 12.2:
428 costing method holds 2 values: 'STANDARD_COSTING', 'ACTUAL'.
429 The rate source would hold the source of the rates. */
430
431 l_stage := 'selecting costing method from rule [' || x_costing_rule || ']';
432 pa_cc_utils.log_message(l_stage);
433
434 select rule.costing_method, rule.rate_source_code
435 into l_costing_method, l_rate_source_code
436 from pa_compensation_rule_sets rule
437 where rule.compensation_rule_set = x_costing_rule;
438
439 if ( l_debug_mode = 'Y' ) then
440 pa_cc_utils.log_message('l_costing_method '||l_costing_method);
441 end if;
442
443 if ( x_rate_sch_id is null and l_rate_source_code = 'PROJECT_RATES')
444 then
445 x_err_code := 'NO_COST_RATE_SCH';
446 raise USER_EXCEPTION;
447 end if;
448
449 -- 12.2 payroll intg changes
450 --if ( l_costing_method = 'LABOR_COST_EXTN' )
451 if ( l_rate_source_code = 'EXTENSION' )
452 then
453 x_cost_rate := null ;
454 raise USER_EXCEPTION;
455 end if; /* rate source is 'extension'*/
456
457 if ( l_rate_source_code = 'HR_RATES' ) then
458
459 select assignment_id
460 into l_assignment_id
461 from per_all_assignments_f
462 where person_id = p_person_id
463 and p_txn_date between effective_start_date and nvl(effective_end_date, p_txn_date)
464 and primary_flag = 'Y';
465
466 pa_pay_util.get_hr_rate(p_person_id => p_person_id,
467 p_assignment_id => l_assignment_id,
468 p_ei_date => p_txn_date,
469 p_ei_id => NULL,
470 p_rbc_elem_type_id => l_rbc_elem_type_id,
471 x_rate => l_rbc_rate);
472
473 select a.currency_code
474 into x_cost_rate_curr_code
475 from pqh_criteria_rate_defn a,
476 pqh_criteria_rate_elements c
477 where a.criteria_rate_defn_id = c.criteria_rate_defn_id
478 and c.element_type_id = l_rbc_elem_type_id;
479
480 x_cost_rate := l_rbc_rate;
481 raise USER_EXCEPTION;
482 end if;
483
484 -- rest of the flow below would be for rate source of 'PROJECT_RATES'
485 /*
486 * Find what Type the schedule is.
487 */
488 l_stage := 'Reading Rate Schedule information for [' || to_char(x_rate_sch_id) || ']';
489 if ( l_debug_mode = 'Y' )
490 then
491 pa_cc_utils.log_message(l_stage);
492 end if;
493 select rate_sch.schedule_type
494 ,rate_sch.rate_sch_currency_code
495 ,rate_sch.job_group_id
496 into l_sch_type
497 ,x_cost_rate_curr_code
498 ,l_job_group_id
499 from pa_std_bill_rate_schedules_all rate_sch
500 where rate_sch.bill_rate_sch_id = x_rate_sch_id;
501
502 l_stage := 'sch type [' || l_sch_type || '] crcc [' || x_cost_rate_curr_code || '] job group [' ||
503 to_char(l_job_group_id) || ']';
504 if ( l_debug_mode = 'Y' )
505 then
506 pa_cc_utils.log_message(l_stage);
507 end if;
508
509 if ( l_sch_type = 'EMPLOYEE')
510 then
511 l_stage := 'Getting rate from this employee type schedule';
512 if ( l_debug_mode = 'Y' )
513 then
514 pa_cc_utils.log_message(l_stage);
515 end if;
516
517 begin
518 select bill_rates.rate
519 ,bill_rates.start_date_active
520 ,bill_rates.end_date_active
521 into x_cost_rate
522 ,x_start_date_active
523 ,x_end_date_active
524 from pa_bill_rates_all bill_rates
525 where trunc(p_txn_date) between trunc(bill_rates.start_date_active)
526 and trunc(nvl(bill_rates.end_date_active,p_txn_date))
527 and bill_rates.person_id = p_person_id
528 and bill_rates.bill_rate_sch_id = x_rate_sch_id
529 ;
530 exception
531 when no_data_found then
532 x_err_code := 'NO_RATE_PERSON';
533 end;
534 else
535 l_stage := 'Job mapping for [' || to_char(x_job_id) || ']';
536 if ( l_debug_mode = 'Y' )
537 then
538 pa_cc_utils.log_message(l_stage);
539 end if;
540 l_dest_job_id := pa_cross_business_grp.IsMappedToJob(x_job_id, l_job_group_id);
541
542 l_stage := 'Getting rate for job id [' || to_char(l_dest_job_id) || ']';
543 if ( l_debug_mode = 'Y' )
544 then
545 pa_cc_utils.log_message(l_stage);
546 end if;
547 begin
548 select bill_rates.rate
549 ,bill_rates.start_date_active
550 ,bill_rates.end_date_active
551 into x_cost_rate
552 ,x_start_date_active
553 ,x_end_date_active
554 from pa_bill_rates_all bill_rates
555 where trunc(p_txn_date) between trunc(bill_rates.start_date_active)
556 and trunc(nvl(bill_rates.end_date_active, p_txn_date))
557 and bill_rates.job_id = l_dest_job_id
558 and bill_rates.bill_rate_sch_id = x_rate_sch_id
559 ;
560 exception
561 when others then
562 x_err_code := 'NO_RATE_JOB';
563 end;
564 end if;
565 if ( x_err_code is not null )
566 then
567 raise USER_EXCEPTION;
568 end if;
569
570 l_stage := 'Rate is [' || to_char(x_cost_rate) || ']';
571 if ( l_debug_mode = 'Y' )
572 then
573 pa_cc_utils.log_message(l_stage);
574 end if;
575
576 /* commented for bug 7423839
577
578 if ( l_debug_mode = 'Y' )
579 then
580 pa_cc_utils.log_message('END');
581 end if;
582 */
583 pa_cc_utils.reset_curr_function;
584 exception
585 when USER_EXCEPTION
586 then
587 g_rt_calling_module := p_calling_module;
588 g_rt_organization_id := x_organization_id;
589 g_rt_cost_rate := x_cost_rate;
590 g_rt_start_date_active := x_start_date_active;
591 g_rt_end_date_active := x_end_date_active;
592 g_rt_costing_rule := x_costing_rule ;
593 g_rt_rate_sch_id := x_rate_sch_id;
594 g_rt_cost_rate_curr_code := x_cost_rate_curr_code;
595 g_rt_acct_rate_type := x_acct_rate_type;
596 g_rt_acct_rate_date_code := x_acct_rate_date_code;
597 g_rt_acct_exch_rate := x_acct_exch_rate;
598 g_rt_ot_project_id := x_ot_project_id ;
599 g_rt_ot_task_id := x_ot_task_id;
600 g_rt_err_stage := x_err_stage;
601 g_rt_err_code := x_err_code;
602 if ( l_debug_mode = 'Y' ) --skkoppul
603 then
604 /*
605 pa_cc_utils.log_message('p_calling_module '||p_calling_module);
606 pa_cc_utils.log_message('x_organization_id '||x_organization_id);
607 pa_cc_utils.log_message('x_cost_rate '||x_cost_rate);
608 pa_cc_utils.log_message('x_start_date_active '||to_char(x_start_date_active));
609 pa_cc_utils.log_message('x_end_date_active '||to_char(x_end_date_active));
610 pa_cc_utils.log_message('x_costing_rule '||x_costing_rule);
611 pa_cc_utils.log_message('x_rate_sch_id '||x_rate_sch_id);
612 pa_cc_utils.log_message('x_cost_rate_curr_code '||x_cost_rate_curr_code);
613 pa_cc_utils.log_message('x_acct_rate_type '||x_acct_rate_type);
614 pa_cc_utils.log_message('x_acct_rate_date_code '||x_acct_rate_date_code);
615 pa_cc_utils.log_message('x_acct_exch_rate '||x_acct_exch_rate);
616 pa_cc_utils.log_message('x_ot_project_id '||x_ot_project_id);
617 pa_cc_utils.log_message('x_ot_task_id '||x_ot_task_id);
618 pa_cc_utils.log_message('x_err_stage '||x_err_stage);
619 pa_cc_utils.log_message('x_err_code '||x_err_code);
620 pa_cc_utils.log_message('Error '||substr(SQLERRM,1,300));
621 */
622 pa_cc_utils.log_message('x_err_stage '||x_err_stage||' x_err_code '||x_err_code||' Error '||substr(SQLERRM,1,300));
623 end if;
624 pa_cc_utils.reset_curr_function;
625 when others then
626 if ( l_debug_mode = 'Y' )
627 then
628 pa_cc_utils.log_message('In when others exception '||substr(SQLERRM,1,300));
629 end if;
630 /*Bug fix:3089560 Plsql numeric value error causes without resetting stack*/
631 pa_cc_utils.reset_curr_function;
632 raise;
633 end get_labor_rate;
634 ---------------------------------------------------------------------------------------------
635 PROCEDURE get_orgn_level_costing_info
636 ( p_org_id IN pa_implementations_all.org_id%TYPE
637 ,p_organization_id IN pa_expenditures_all.incurred_by_organization_id%TYPE
638 ,p_person_id IN pa_expenditures_all.incurred_by_person_id%TYPE
639 ,p_job_id IN pa_expenditure_items_all.job_id%TYPE
640 ,p_txn_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
641 ,p_calling_module IN varchar2 default 'STAFFED'
642 ,x_org_labor_sch_rule_id IN OUT NOCOPY pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE
643 ,x_costing_rule IN OUT NOCOPY pa_compensation_rule_sets.compensation_rule_set%TYPE
644 ,x_rate_sch_id IN OUT NOCOPY pa_std_bill_rate_schedules.bill_rate_sch_id%TYPE
645 ,x_ot_project_id IN OUT NOCOPY pa_projects_all.project_id%TYPE
646 ,x_ot_task_id IN OUT NOCOPY pa_tasks.task_id%TYPE
647 ,x_base_hours IN OUT NOCOPY pa_org_labor_sch_rule.base_hours%TYPE /* 12.2 payroll intg .. bug 11811475 */
648 ,x_rbc_elem_type_id IN OUT NOCOPY pa_org_labor_sch_rule.rbc_element_type_id%TYPE
649 ,x_cost_rate_curr_code IN OUT NOCOPY pa_expenditure_items_all.denom_currency_code%TYPE
650 ,x_acct_rate_type IN OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE
651 ,x_acct_rate_date_code IN OUT NOCOPY pa_implementations_all.acct_rate_date_code%TYPE
652 ,x_acct_exch_rate IN OUT NOCOPY pa_compensation_details_all.acct_exchange_rate%TYPE
653 ,x_err_stage IN OUT NOCOPY number
654 ,x_err_code IN OUT NOCOPY varchar2
655 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
656 )
657 is
658
659 l_debug_mode VARCHAR2(1) := 'Y';
660 l_stage VARCHAR2(500);
661
662 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE;
663 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%TYPE;
664 l_costing_rule pa_compensation_rule_sets.compensation_rule_set%TYPE;
665 l_ot_project_id pa_projects_all.project_id%TYPE;
666 l_ot_task_id pa_tasks.task_id%TYPE;
667 l_acct_rate_date_code pa_org_labor_sch_rule.acct_rate_date_code%TYPE;
668 l_acct_rate_type pa_org_labor_sch_rule.acct_rate_type%TYPE;
669 l_acct_exch_rate pa_org_labor_sch_rule.acct_exchange_rate%TYPE;
670
671 l_costing_method pa_compensation_rule_sets.costing_method%TYPE;
672 l_rate_source_code pa_compensation_rule_sets.rate_source_code%TYPE;
673 l_base_hours pa_org_labor_sch_rule.base_hours%TYPE;
674 l_rbc_elem_type_id pa_org_labor_sch_rule.rbc_element_type_id%TYPE;
675
676 cursor org_costing_rule( p_org_id IN pa_implementations_all.org_id%TYPE
677 ,p_organization_id IN pa_expenditures_all.incurred_by_organization_id%TYPE
678 ,p_person_id IN pa_expenditures_all.incurred_by_person_id%TYPE
679 ,p_job_id IN pa_expenditure_items_all.job_id%TYPE
680 ,p_txn_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
681 ,p_calling_module IN varchar2
682 )
683 is
684 select assign.org_labor_sch_rule_id
685 ,decode(p_calling_module ,'REQUIREMENT'
686 ,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
687 ,assign.labor_costing_rule
688 ,assign.overtime_project_id
689 ,assign.overtime_task_id
690 ,assign.acct_rate_date_code
691 ,assign.acct_rate_type
692 ,assign.acct_exchange_rate
693 ,assign.base_hours
694 ,assign.rbc_element_type_id
695 from pa_org_labor_sch_rule assign
696 ,pa_org_hierarchy_denorm hier
697 ,pa_implementations imp
698 where hier.child_organization_id = p_organization_id
699 and imp.exp_org_structure_version_id=hier.org_hierarchy_version_id
700 and hier.pa_org_use_type = 'TP_SCHEDULE'
701 and assign.organization_id = hier.parent_organization_id
702 and (assign.org_id = p_org_id or assign.org_id is null)
703 and trunc(p_txn_date) between trunc(assign.start_date_active)
704 and trunc(nvl(assign.end_date_active,p_txn_date));
705
706
707 cursor assignment( p_org_id IN pa_implementations_all.org_id%TYPE
708 ,p_organization_id IN pa_expenditures_all.incurred_by_organization_id%TYPE
709 ,p_person_id IN pa_expenditures_all.incurred_by_person_id%TYPE
710 ,p_job_id IN pa_expenditure_items_all.job_id%TYPE
711 ,p_txn_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
712 ,p_calling_module IN varchar2
713 )
714 is
715 select assign.org_labor_sch_rule_id
716 ,decode(p_calling_module ,'REQUIREMENT'
717 ,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
718 ,assign.labor_costing_rule
719 ,assign.overtime_project_id
720 ,assign.overtime_task_id
721 ,assign.acct_rate_date_code
722 ,assign.acct_rate_type
723 ,assign.acct_exchange_rate
724 ,assign.base_hours
725 ,assign.rbc_element_type_id
726 from pa_org_labor_sch_rule assign
727 ,pa_org_hierarchy_denorm hier
728 ,pa_implementations imp
729 where hier.child_organization_id = p_organization_id
730 and imp.exp_org_structure_version_id=hier.org_hierarchy_version_id
731 and hier.pa_org_use_type = 'TP_SCHEDULE'
732 and assign.organization_id = hier.parent_organization_id
733 and (assign.org_id = p_org_id or assign.org_id is null)
734 and trunc(p_txn_date) between trunc(assign.start_date_active)
735 and trunc(nvl(assign.end_date_active,p_txn_date))
736 and (exists( select null
737 from pa_std_bill_rate_schedules_all rate_sch
738 ,pa_bill_rates_all bill_rates
739 where rate_sch.bill_rate_sch_id =
740 decode(p_calling_module ,'REQUIREMENT'
741 ,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
742 and bill_rates.bill_rate_sch_id = rate_sch.bill_rate_sch_id
743 and rate_sch.schedule_type = 'EMPLOYEE'
744 and bill_rates.person_id = p_person_id
745 and trunc(p_txn_date) between trunc(bill_rates.start_date_active) and
746 trunc(nvl(bill_rates.end_date_active,p_txn_date))
747 )
748 or
749 exists( select null
750 from pa_std_bill_rate_schedules_all rate_sch
751 ,pa_bill_rates_all bill_rates
752 where rate_sch.bill_rate_sch_id =
753 decode(p_calling_module ,'REQUIREMENT'
754 ,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
755 and bill_rates.bill_rate_sch_id = rate_sch.bill_rate_sch_id
756 and rate_sch.schedule_type = 'JOB'
757 and bill_rates.job_id =
758 pa_cross_business_grp.IsMappedToJob(p_job_id, rate_sch.job_group_id)
759 and trunc(p_txn_date) between trunc(bill_rates.start_date_active) and
760 trunc(nvl(bill_rates.end_date_active,p_txn_date))
761 )
762 )
763 order by hier.parent_level desc
764 ,assign.organization_id
765 ,assign.org_id;
766
767 begin
768 if pa_cc_utils.g_debug_mode then
769 l_debug_mode := 'Y';
770 else
771 l_debug_mode := 'N';
772 end if;
773 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); -- skkoppul
774 IF ( l_debug_mode = 'Y' ) THEN
775 pa_debug.set_process( x_process => 'PLSQL'
776 ,x_debug_mode => l_debug_mode
777 );
778 pa_cc_utils.set_curr_function('get_orgn_level_costing_info');
779 --pa_cc_utils.log_message('Start ');
780 END IF;
781
782 l_stage := 'org_id [' || to_char(p_org_id) || '] organization_id [' || to_char(p_organization_id) || ']';
783 if ( l_debug_mode = 'Y' )
784 then
785 pa_cc_utils.log_message(l_stage);
786 end if;
787
788 /* commented for bug 7423839
789
790 l_stage := 'opening assignment';
791 if ( l_debug_mode = 'Y' )
792 then
793 pa_cc_utils.log_message(l_stage);
794 end if;
795 */
796 /* added for 12.2 .. payroll intg. Evaluate the organization level rules setup.
797 Based on the costing rule found, find the costing method and rate source.
798 If the rate source is 'PROJECT_RATES', validate that the bill rate schedules
799 are defined. For rate sources of 'HR_RATES' and 'EXTENSION', no further
800 evaluation is done. */
801
802 l_rate_source_code := NULL;
803 l_costing_method := NULL;
804
805 pa_cc_utils.log_message('opening org costing rule');
806
807 open org_costing_rule (p_org_id,p_organization_id,p_person_id,p_job_id,p_txn_date,p_calling_module);
808
809 fetch org_costing_rule
810 into l_org_labor_sch_rule_id
811 ,l_rate_sch_id
812 ,l_costing_rule
813 ,l_ot_project_id
814 ,l_ot_task_id
815 ,l_acct_rate_date_code
816 ,l_acct_rate_type
817 ,l_acct_exch_rate
818 ,l_base_hours
819 ,l_rbc_elem_type_id;
820
821 pa_cc_utils.log_message('Fetched org costing rule .. costing_rule is: ' || l_costing_rule);
822
823 if org_costing_rule%FOUND then
824 begin
825 select costing_method, rate_source_code
826 into l_costing_method, l_rate_source_code
827 from pa_compensation_rule_sets
828 where compensation_rule_set = l_costing_rule;
829 exception
830 when no_data_found then
831 l_rate_source_code := NULL;
832 end;
833 end if;
834
835 pa_cc_utils.log_message('Fetched comp rule .. costing_method is: ' || l_costing_method ||', rate source: ' || l_rate_source_code);
836
837 --close org_costing_rule;
838
839 if ((org_costing_rule%NOTFOUND) OR (l_rate_source_code = 'PROJECT_RATES')) then
840 open assignment (p_org_id,p_organization_id,p_person_id,p_job_id,p_txn_date,p_calling_module);
841
842 /* commented for bug 7423839
843
844 l_stage := 'fetching assignment';
845 if ( l_debug_mode = 'Y' )
846 then
847 pa_cc_utils.log_message(l_stage);
848 end if;
849
850 */
851 fetch assignment
852 into l_org_labor_sch_rule_id
853 ,l_rate_sch_id
854 ,l_costing_rule
855 ,l_ot_project_id
856 ,l_ot_task_id
857 ,l_acct_rate_date_code
858 ,l_acct_rate_type
859 ,l_acct_exch_rate
860 ,l_base_hours
861 ,l_rbc_elem_type_id;
862
863 pa_cc_utils.log_message('Fetched assignment rule .. costing_rule is: ' || l_costing_rule);
864
865 if ( assignment%NOTFOUND )
866 then
867 l_stage := 'assignment not found for organization ';
868 if ( l_debug_mode = 'Y' )
869 then
870 pa_cc_utils.log_message(l_stage);
871 end if;
872 /*
873 * This means - neither this organization nor any of its parent
874 * organizations have an assignment. See if there is any assigmnent
875 * at the OU level.
876 */
877 /* 12.2 payroll intg .. total time costing .. commenting the caching logic
878 as the base hours, costing method, rate source can change by the date.
879 The usage based on OU check alone will not suffice.
880 */
881 -- if ( P_Called_From <> 'R' and /* Added for 3405326 */
882 -- g_ou_org_labor_sch_rule_id is not null and nvl(g_ou_id,-99) = nvl(p_org_id,-99) )
883 -- then
884 /*
885 * Reuse global - OU level cache.
886 */
887 -- l_org_labor_sch_rule_id := g_ou_org_labor_sch_rule_id ;
888 -- l_rate_sch_id := g_ou_cost_rate_sch_id ;
889 -- l_costing_rule := g_ou_labor_costing_rule ;
890 -- l_ot_project_id := g_ou_ot_project_id ;
891 -- l_ot_task_id := g_ou_ot_task_id ;
892 -- l_acct_rate_date_code := g_ou_acct_rate_date_code ;
893 -- l_acct_rate_type := g_ou_acct_rate_type ;
894 -- l_acct_exch_rate := g_ou_acct_exch_rate ;
895 -- --l_base_hours := g_base_hours;
896 -- else
897 /*
898 * Unable to reuse cache.
899 */
900 begin
901 l_stage := 'Getting OU level assignment for [' || to_char(p_org_id) || ']';
902 if ( l_debug_mode = 'Y' )
903 then
904 pa_cc_utils.log_message(l_stage);
905 end if;
906 select assign.org_id
907 ,assign.org_labor_sch_rule_id
908 ,decode(p_calling_module ,'REQUIREMENT'
909 ,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
910 ,assign.labor_costing_rule
911 ,assign.overtime_project_id
912 ,assign.overtime_task_id
913 ,assign.acct_rate_date_code
914 ,assign.acct_rate_type
915 ,assign.acct_exchange_rate
916 ,assign.base_hours
917 ,assign.rbc_element_type_id
918 into g_ou_id
919 ,g_ou_org_labor_sch_rule_id
920 ,g_ou_cost_rate_sch_id
921 ,g_ou_labor_costing_rule
922 ,g_ou_ot_project_id
923 ,g_ou_ot_task_id
924 ,g_ou_acct_rate_date_code
925 ,g_ou_acct_rate_type
926 ,g_ou_acct_exch_rate
927 ,g_base_hours
928 ,g_rbc_elem_type_id
929 from pa_org_labor_sch_rule assign
930 where assign.organization_id is null
931 and trunc(p_txn_date) between trunc(assign.start_date_active)
932 and trunc(nvl(assign.end_date_active,p_txn_date))
933 and nvl(assign.org_id,-99) = nvl(p_org_id, -99);
934 exception
935 when no_data_found then
936 l_stage := 'assignment not found at the OU level !!!!!!';
937 if ( l_debug_mode = 'Y' ) then
938 pa_cc_utils.log_message(l_stage);
939 end if;
940 x_err_code := 'NO_RATE';
941 when others then
942 raise;
943 end;
944 /*
945 * Copying global values to local.
946 */
947 l_org_labor_sch_rule_id := g_ou_org_labor_sch_rule_id ;
948 l_rate_sch_id := g_ou_cost_rate_sch_id ;
949 l_costing_rule := g_ou_labor_costing_rule ;
950 l_ot_project_id := g_ou_ot_project_id ;
951 l_ot_task_id := g_ou_ot_task_id ;
952 l_acct_rate_date_code := g_ou_acct_rate_date_code ;
953 l_acct_rate_type := g_ou_acct_rate_type ;
954 l_acct_exch_rate := g_ou_acct_exch_rate ;
955 l_base_hours := g_base_hours;
956 l_rbc_elem_type_id := g_rbc_elem_type_id;
957 -- end if; /* reuse cache ? */
958 end if; /* organization level assignment not found */
959 end if; /* org_costing_rule%notfound */
960
961 l_stage := 'l_org_labor_sch_rule_id [' || to_char(l_org_labor_sch_rule_id) ||
962 '] l_rate_sch_id [' || to_char( l_rate_sch_id) ||
963 '] l_costing_rule [' || l_costing_rule ||
964 '] l_ot_project_id [' || to_char( l_ot_project_id) ||
965 '] l_ot_task_id [' || to_char( l_ot_task_id) ||
966 '] l_acct_rate_date_code [' || l_acct_rate_date_code ||
967 '] l_acct_rate_type [' || l_acct_rate_type ||
968 '] l_acct_exch_rate [' || to_char( l_acct_exch_rate) ||
969 '] x_err_code [' || x_err_code || ']';
970 if ( l_debug_mode = 'Y' )
971 then
972 pa_cc_utils.log_message(l_stage);
973 end if;
974
975 /*
976 * Move local variables to out parameters.
977 */
978 x_org_labor_sch_rule_id := nvl(x_org_labor_sch_rule_id ,l_org_labor_sch_rule_id);
979 x_rate_sch_id := nvl(x_rate_sch_id ,l_rate_sch_id);
980 x_costing_rule := nvl(x_costing_rule ,l_costing_rule);
981 x_ot_project_id := l_ot_project_id;
982 x_ot_task_id := l_ot_task_id;
983 x_acct_rate_date_code := nvl(x_acct_rate_date_code ,l_acct_rate_date_code);
984 x_acct_rate_type := nvl(x_acct_rate_type ,l_acct_rate_type);
985 x_acct_exch_rate := nvl(x_acct_exch_rate ,l_acct_exch_rate);
986 x_base_hours := nvl(x_base_hours, l_base_hours);
987 x_rbc_elem_type_id := nvl(x_rbc_elem_type_id, l_rbc_elem_type_id);
988
989 /* commented for bug 7423839
990 l_stage := 'Closing Cursor';
991 if ( l_debug_mode = 'Y' )
992 then
993 pa_cc_utils.log_message(l_stage);
994 end if;
995
996 */
997 if assignment%ISOPEN then
998 close assignment;
999 end if;
1000
1001 /* commented for bug 7423839
1002 l_stage := 'END';
1003 if ( l_debug_mode = 'Y' )
1004 then
1005 pa_cc_utils.log_message(l_stage);
1006 end if;
1007 */
1008 pa_cc_utils.reset_curr_function;
1009
1010 exception
1011 when others
1012 then
1013 if ( l_debug_mode = 'Y' )
1014 then
1015 pa_cc_utils.log_message('inside others excpn get_orgn_level_costing_info');
1016 end if;
1017 pa_cc_utils.reset_curr_function;
1018 RAISE;
1019 end get_orgn_level_costing_info;
1020
1021 /*----------------------------------------------------------------------------*/
1022 -- Start of Comments
1023 -- API name : GetEmpCostRate
1024 -- Type : Public Function
1025 -- Pre-reqs : None
1026 -- Function : To get the emp cost rate.
1027 -- Return Value : NUMBER
1028 -- Prameters
1029 -- P_Person_Id IN NUMBER REQUIRED
1030 -- P_Job_Id IN NUMBER OPTIONAL
1031 -- P_Organization_Id IN NUMBER OPTIONAL
1032 -- P_Effective_Date IN DATE OPTIONAL DEFAULT SYSDATE
1033 -- P_Rate_Type IN VARCHAR2 REQUIRED
1034 -- -- FUNC for Rate in Functional Currency
1035 -- -- DENOM for Rate in Denom Currency
1036 -- P_Called_From IN varchar2 DEFAULT 'O' Added for 3405326. 'O'--Others
1037 -- 'R' -- Reports
1038 -- History
1039 -- 03-OCT-02 Vgade -Created
1040 --
1041 /*----------------------------------------------------------------------------*/
1042
1043 Function GetEmpCostRate( P_Person_Id IN per_all_people_f.person_id%type
1044 ,P_Job_Id IN pa_expenditure_items_all.job_id%type
1045 ,P_Organization_Id IN pa_expenditures_all.incurred_by_organization_id%type
1046 ,P_Effective_Date IN date
1047 ,P_Rate_Type IN varchar2
1048 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
1049 )
1050 RETURN pa_bill_rates_all.rate%type IS
1051 l_job_id pa_expenditure_items_all.job_id%type;
1052 l_organization_id pa_expenditures_all.incurred_by_organization_id%type;
1053 l_org_id pa_expenditures_all.org_id%type; /*2879644*/
1054 l_costing_rule pa_compensation_details_all.compensation_rule_set%type;
1055 l_cost_rate pa_bill_rates_all.rate%type;
1056 l_acct_cost_rate pa_bill_rates_all.rate%type;
1057 l_start_date_active date;
1058 l_end_date_active date;
1059 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%type;
1060 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%type;
1061 l_override_type pa_compensation_details.override_type%type;
1062 l_cost_rate_curr_code pa_compensation_details.cost_rate_currency_code%type;
1063 l_acct_rate_type pa_compensation_details.acct_rate_type%type;
1064 l_acct_rate_date_code pa_compensation_details.acct_rate_date_code%type;
1065 l_acct_exch_rate pa_compensation_details.acct_exchange_rate%type;
1066 l_ot_project_id pa_projects_all.project_id%type;
1067 l_ot_task_id pa_tasks.task_id%type;
1068 l_err_code varchar2(200) default null; /* Default null added for bug 2931397 */
1069 l_err_stage number;
1070 l_return_rate pa_bill_rates_all.rate%type;
1071 l_acct_currency_code varchar2(15);
1072 l_conversion_date date;
1073 l_numerator number;
1074 l_denominator number;
1075 Begin
1076
1077 --pa_cc_utils.log_message('In Getempcost rate');
1078 IF (P_Called_From <> 'R') AND /* Added for 3405326 */
1079 P_person_id = nvl(G_EMP_PERSON_ID,-99) AND
1080 nvl(P_job_id,-99) = nvl(G_EMP_JOB_ID,-99) AND
1081 nvl(P_organization_id,-99) = nvl(G_EMP_ORGANIZATION_ID,-99) AND
1082 P_effective_date BETWEEN G_EMP_RATE_START_DATE AND nvl(G_EMP_RATE_END_DATE,SYSDATE) THEN
1083 NULL; --Do nothing. Values are in the Cache.
1084 -- pa_cc_utils.log_message('Getting the Rate from Cache');
1085 ELSE
1086
1087 --pa_cc_utils.log_message('Deriving the Rate');
1088 --- Call the api to derive rate and attributes.
1089
1090 l_job_id := P_job_id; --IN OUT parameter
1091 l_organization_id := P_organization_id; --IN OUT parameter.
1092
1093 Begin
1094 PA_COST_RATE_PUB.get_labor_rate ( p_person_id =>P_person_id
1095 ,x_job_id =>l_job_id
1096 ,p_txn_date =>P_effective_date
1097 ,p_org_id =>l_org_id /*2879644*/
1098 ,x_organization_id =>l_organization_id
1099 ,x_cost_rate =>l_cost_rate
1100 ,x_start_date_active =>l_start_date_active
1101 ,x_end_date_active =>l_end_date_active
1102 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
1103 ,x_costing_rule =>l_costing_rule
1104 ,x_rate_sch_id =>l_rate_sch_id
1105 ,x_cost_rate_curr_code =>l_cost_rate_curr_code
1106 ,x_acct_rate_type =>l_acct_rate_type
1107 ,x_acct_rate_date_code =>l_acct_rate_date_code
1108 ,x_acct_exch_rate =>l_acct_exch_rate
1109 ,x_ot_project_id =>l_ot_project_id
1110 ,x_ot_task_id =>l_ot_task_id
1111 ,x_err_stage =>l_err_stage
1112 ,x_err_code =>l_err_code
1113 ,p_called_from =>P_called_from /*3405326*/
1114 );
1115 Exception
1116 When OTHERS Then
1117 NULL;
1118 End;
1119
1120 /* Following code added for bug 2931397 */
1121 If l_err_code is not null
1122 then return NULL;
1123 End if;
1124 /* bug 2931397 */
1125
1126 -- Cache the Emp Details
1127 G_EMP_PERSON_ID := P_person_id;
1128 G_EMP_JOB_ID := P_job_id;
1129 G_EMP_ORGANIZATION_ID := P_organization_id;
1130
1131 -- Cache the rate and the attributes
1132 G_EMP_COST_RATE := l_cost_rate;
1133 G_EMP_RATE_RULE := l_costing_rule;
1134 G_EMP_RATE_CURR := l_cost_rate_curr_code;
1135 G_EMP_RATE_START_DATE := l_start_date_active;
1136 G_EMP_RATE_END_DATE := l_end_date_active;
1137
1138 IF P_Rate_Type = 'FUNC' THEN --Return the Functional Rate
1139
1140 -- pa_cc_utils.log_message('Deriving the Functional Rate');
1141 -- Get the Functional Currency code
1142 l_acct_currency_code := PA_CURRENCY.get_currency_code;
1143
1144 --Check if the denom and functional currencies are different
1145
1146 IF l_acct_currency_code <> l_cost_rate_curr_code THEN
1147
1148 l_conversion_date := P_Effective_Date;
1149
1150 pa_multi_currency.convert_amount( P_from_currency =>l_cost_rate_curr_code,
1151 P_to_currency =>l_acct_currency_code,
1152 P_conversion_date =>l_conversion_date,
1153 P_conversion_type =>l_acct_rate_type,
1154 P_amount =>l_cost_rate,
1155 P_user_validate_flag =>'N',
1156 P_handle_exception_flag =>'Y', /* changed to 'Y' from 'N' for bug 2931397 */
1157 P_converted_amount =>l_acct_cost_rate,
1158 P_denominator =>l_denominator,
1159 P_numerator =>l_numerator,
1160 P_rate =>l_acct_exch_rate,
1161 X_status =>l_err_code ) ;
1162
1163 /* Following code added for bug 2931397 */
1164 If l_err_code is not null
1165 then return NULL;
1166 End if;
1167 /* bug 2931397 */
1168
1169 --Cache the Acct cost rate.
1170 G_EMP_ACCT_COST_RATE := l_acct_cost_rate;
1171 ELSE
1172 --If the call is in FUNC Mode, and the currencies are same
1173 G_EMP_ACCT_COST_RATE := l_cost_rate;
1174 END IF;
1175 END IF;
1176 END IF;
1177
1178 IF P_Rate_Type = 'FUNC' THEN
1179 --Return the Functional Rate
1180 l_return_rate := G_EMP_ACCT_COST_RATE;
1181 ELSE
1182 --Return the Transaction Rate
1183 l_return_rate := G_EMP_COST_RATE;
1184 END IF;
1185
1186 --pa_cc_utils.log_message('before return rate = '||l_return_rate);
1187 Return l_return_rate;
1188
1189 End GetEmpCostRate;
1190
1191 /*----------------------------------------------------------------------------*/
1192 -- Start of Comments
1193 -- API name : GetEmpCostRateInfo
1194 -- Type : Public Function
1195 -- Pre-reqs : None
1196 -- Function : To get the emp cost rate attributes; COMPENSATION RULE,CURRENCY
1197 -- CODE, RATE EFFECTIVE START DATE, and RATE EFFECTIVE END DATE.
1198 -- Return Value : VARCHAR2
1199 -- Prameters
1200 -- P_person_id IN NUMBER REQUIRED
1201 -- P_job_id IN NUMBER OPTIONAL
1202 -- P_organization_id IN NUMBER OPTIONAL
1203 -- P_effective_date IN DATE OPTIONAL DEFAULT SYSDATE
1204 -- P_Rate_Attribute IN VARCHAR2 REQUIRED
1205 -- Valid Values
1206 -- RULE for Employee Compensation Rule
1207 -- CURR for Rate Currency Code
1208 -- START for Rate Effective Start Date.
1209 -- END for Rate Effective End Date.
1210 -- History
1211 -- 03-OCT-02 Vgade -Created
1212 --
1213 /*----------------------------------------------------------------------------*/
1214 Function GetEmpCostRateInfo( P_Person_Id IN per_all_people_f.person_id%type
1215 ,P_Job_Id IN pa_expenditure_items_all.job_id%type
1216 ,P_Organization_Id IN pa_expenditures_all.incurred_by_organization_id%type
1217 ,P_Effective_Date IN date
1218 ,P_Rate_Attribute IN varchar2
1219 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
1220 )
1221 RETURN VARCHAR2 IS
1222 l_job_id pa_expenditure_items_all.job_id%type;
1223 l_organization_id pa_expenditures_all.incurred_by_organization_id%type;
1224 l_org_id pa_expenditures_all.org_id%TYPE ; /*2879644*/
1225 l_costing_rule pa_compensation_details_all.compensation_rule_set%type;
1226 l_cost_rate pa_bill_rates_all.rate%type;
1227 l_start_date_active date;
1228 l_end_date_active date;
1229 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%type;
1230 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%type;
1231 l_override_type pa_compensation_details.override_type%type;
1232 l_cost_rate_curr_code pa_compensation_details.cost_rate_currency_code%type;
1233 l_acct_rate_type pa_compensation_details.acct_rate_type%type;
1234 l_acct_rate_date_code pa_compensation_details.acct_rate_date_code%type;
1235 l_acct_exch_rate pa_compensation_details.acct_exchange_rate%type;
1236 l_ot_project_id pa_projects_all.project_id%type;
1237 l_ot_task_id pa_tasks.task_id%type;
1238 l_err_code varchar2(200);
1239 l_err_stage number;
1240 l_return_value varchar2(100);
1241 Begin
1242
1243 --pa_cc_utils.log_message('In Getempcostrateinfo...');
1244 IF (P_Called_From <> 'R') AND /* Added for 3405326 */
1245 P_person_id = nvl(G_EMP_PERSON_ID,-99) AND
1246 nvl(P_job_id,-99) = nvl(G_EMP_JOB_ID,-99) AND
1247 nvl(P_organization_id,-99) = nvl(G_EMP_ORGANIZATION_ID,-99) AND
1248 P_effective_date BETWEEN G_EMP_RATE_START_DATE AND nvl(G_EMP_RATE_END_DATE,SYSDATE) THEN
1249 NULL; --Don't do anything. The values are cached already.
1250 --pa_cc_utils.log_message('Getting the Rate Attributes from Cache');
1251 ELSE
1252
1253 --pa_cc_utils.log_message('Deriving the Rate Attributes');
1254 --- Call the api to derive rate and attributes.
1255
1256 l_job_id := P_job_id; --IN OUT parameter
1257 l_organization_id := P_organization_id; --IN OUT parameter.
1258
1259 Begin
1260 PA_COST_RATE_PUB.get_labor_rate ( p_person_id =>P_person_id
1261 ,x_job_id =>l_job_id
1262 ,p_txn_date =>P_effective_date
1263 ,p_org_id =>l_org_id /*2879644*/
1264 ,x_organization_id =>l_organization_id
1265 ,x_cost_rate =>l_cost_rate
1266 ,x_start_date_active =>l_start_date_active
1267 ,x_end_date_active =>l_end_date_active
1268 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
1269 ,x_costing_rule =>l_costing_rule
1270 ,x_rate_sch_id =>l_rate_sch_id
1271 ,x_cost_rate_curr_code =>l_cost_rate_curr_code
1272 ,x_acct_rate_type =>l_acct_rate_type
1273 ,x_acct_rate_date_code =>l_acct_rate_date_code
1274 ,x_acct_exch_rate =>l_acct_exch_rate
1275 ,x_ot_project_id =>l_ot_project_id
1276 ,x_ot_task_id =>l_ot_task_id
1277 ,x_err_stage =>l_err_stage
1278 ,x_err_code =>l_err_code
1279 ,p_called_from =>P_called_from /*3405326*/
1280 );
1281 Exception
1282 When OTHERS Then
1283 NULL;
1284 End;
1285
1286
1287 -- Cache the Emp Details
1288 G_EMP_PERSON_ID := P_person_id;
1289 G_EMP_JOB_ID := P_job_id;
1290 G_EMP_ORGANIZATION_ID := P_organization_id;
1291
1292 -- Cache the rate attributes
1293 G_EMP_COST_RATE := l_cost_rate; /* Added for bug 3624357 */
1294 G_EMP_RATE_RULE := l_costing_rule;
1295 G_EMP_RATE_CURR := l_cost_rate_curr_code;
1296 G_EMP_RATE_START_DATE := l_start_date_active;
1297 G_EMP_RATE_END_DATE := l_end_date_active;
1298 END IF;
1299
1300 IF P_Rate_Attribute = 'RULE' Then
1301 l_return_value := G_EMP_RATE_RULE;
1302 ELSIF P_Rate_Attribute = 'CURR' Then
1303 l_return_value := G_EMP_RATE_CURR;
1304 ELSIF P_Rate_Attribute = 'START' Then
1305 l_return_value := G_EMP_RATE_START_DATE;
1306 ELSIF P_Rate_Attribute = 'END' Then
1307 l_return_value := G_EMP_RATE_END_DATE;
1308 END IF;
1309
1310 Return l_return_value;
1311
1312 End GetEmpCostRateInfo;
1313
1314 --------------------------------
1315 PROCEDURE get_orgn_lvl_cst_info_set
1316 ( p_org_id_tab IN pa_plsql_datatypes.IdTabTyp
1317 ,p_organization_id_tab IN pa_plsql_datatypes.IdTabTyp
1318 ,p_person_id_tab IN pa_plsql_datatypes.IdTabTyp
1319 ,p_job_id_tab IN pa_plsql_datatypes.IdTabTyp
1320 ,p_txn_date_tab IN pa_plsql_datatypes.Char30TabTyp
1321 ,p_override_type_tab IN pa_plsql_datatypes.Char150TabTyp
1322 ,p_calling_module IN varchar2 default 'STAFFED'
1323 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
1324 ,x_org_labor_sch_rule_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
1325 ,x_costing_rule_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
1326 ,x_rate_sch_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
1327 ,x_ot_project_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
1328 ,x_ot_task_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
1329 ,x_base_hours_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp /* 12.2 payroll intg .. bug 11811475 */
1330 ,x_rbc_elem_type_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
1331 ,x_cost_rate_curr_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
1332 ,x_acct_rate_type_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
1333 ,x_acct_rate_date_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
1334 ,x_acct_exch_rate_tab IN OUT NOCOPY pa_plsql_datatypes.Char30TabTyp
1335 ,x_err_stage_tab IN OUT NOCOPY pa_plsql_datatypes.NumTabTyp
1336 ,x_err_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp)
1337 is
1338 l_count number := 0;
1339 l_stage varchar2(500);
1340 l_debug_mode varchar2(1) := 'Y';
1341 begin
1342
1343 if pa_cc_utils.g_debug_mode then
1344 l_debug_mode := 'Y';
1345 else
1346 l_debug_mode := 'N';
1347 end if;
1348 IF ( l_debug_mode = 'Y' ) THEN
1349 pa_debug.set_process( x_process => 'PLSQL'
1350 ,x_debug_mode => l_debug_mode
1351 );
1352 pa_cc_utils.set_curr_function('get_orgn_lvl_cst_info_set');
1353 pa_cc_utils.log_message('Start ');
1354 END IF;
1355
1356 l_count := p_organization_id_tab.count ;
1357 l_stage := 'count [' || to_char(l_count) || ']';
1358 if ( l_debug_mode = 'Y')
1359 then
1360 pa_cc_utils.log_message(l_stage);
1361 end if;
1362 for i in 1 .. l_count
1363 loop
1364 if ( p_override_type_tab(i) is null or x_costing_rule_tab(i) is null )
1365 then
1366 l_stage := 'Calling get_orgn_level_costing_info with Org Id [' || to_char( p_org_id_tab(i) ) ||
1367 '] orgn Id [' || to_char( p_organization_id_tab(i) ) ||
1368 '] person Id [' || to_char( p_person_id_tab(i) ) ||
1369 '] job Id [' || to_char( p_job_id_tab(i) ) ||
1370 '] txn date [' || p_txn_date_tab(i) ||
1371 '] cost rule [' || x_costing_rule_tab(i) ||
1372 '] sch id [' || to_char( x_rate_sch_id_tab(i) ) ||
1373 '] ot prj Id [' || to_char( x_ot_project_id_tab(i) ) ||
1374 '] ot tsk Id [' || to_char( x_ot_task_id_tab(i) ) ||
1375 '] crcc [' || x_cost_rate_curr_code_tab(i) ||
1376 '] art [' || x_acct_rate_type_tab(i) ||
1377 '] ardc [' || x_acct_rate_date_code_tab(i) ||
1378 '] aer [' || x_acct_exch_rate_tab(i) ||
1379 ']';
1380 if ( l_debug_mode = 'Y' )
1381 then
1382 pa_cc_utils.log_message(l_stage);
1383 end if;
1384
1385 /*
1386 * GSCC: Handled File.Date.5 for p_txn_date_tab(i).
1387 */
1388 begin
1389 pa_cost_rate_pub.get_orgn_level_costing_info
1390 (p_org_id => p_org_id_tab(i)
1391 ,p_organization_id => p_organization_id_tab(i)
1392 ,p_person_id => p_person_id_tab(i)
1393 ,p_job_id => p_job_id_tab(i)
1394 ,p_txn_date => to_date(p_txn_date_tab(i),'YYYY/MM/DD')
1395 ,p_calling_module => p_calling_module
1396 ,x_org_labor_sch_rule_id => x_org_labor_sch_rule_id_tab(i)
1397 ,x_costing_rule => x_costing_rule_tab(i)
1398 ,x_rate_sch_id => x_rate_sch_id_tab(i)
1399 ,x_ot_project_id => x_ot_project_id_tab(i)
1400 ,x_ot_task_id => x_ot_task_id_tab(i)
1401 ,x_base_hours => x_base_hours_tab(i) /* 12.2 payroll intg .. bug 11811475 */
1402 ,x_rbc_elem_type_id => x_rbc_elem_type_tab(i)
1403 ,x_cost_rate_curr_code => x_cost_rate_curr_code_tab(i)
1404 ,x_acct_rate_type => x_acct_rate_type_tab(i)
1405 ,x_acct_rate_date_code => x_acct_rate_date_code_tab(i)
1406 ,x_acct_exch_rate => x_acct_exch_rate_tab(i)
1407 ,x_err_stage => x_err_stage_tab(i)
1408 ,x_err_code => x_err_code_tab(i)
1409 ,p_called_from => p_Called_from /*3405326*/
1410 );
1411 exception
1412 when others then
1413 if ( l_debug_mode = 'Y' ) then
1414 pa_cc_utils.log_message('others err in get_orgn_lvl_cst_info_set '||substr(SQLERRM,1,300));
1415 end if;
1416 end;
1417
1418
1419 l_stage := 'After Call to get_orgn_level_costing_info, org labor id ['
1420 || to_char(x_org_labor_sch_rule_id_tab(i)) ||
1421 '] cost rule [' || x_costing_rule_tab(i) ||
1422 '] sch Id [' || to_char( x_rate_sch_id_tab(i) ) ||
1423 '] crcc [' || x_costing_rule_tab(i) ||
1424 '] ot proj Id [' || to_char( x_ot_project_id_tab(i) ) ||
1425 '] ot task Id [' || to_char( x_ot_task_id_tab(i) ) ||
1426 '] crcc [' || x_cost_rate_curr_code_tab(i) ||
1427 '] art [' || x_acct_rate_type_tab(i) ||
1428 '] ardc [' || x_acct_rate_date_code_tab(i) ||
1429 '] aer [' || x_acct_exch_rate_tab(i) ||
1430 '] err stg [' || to_char( x_err_stage_tab(i) ) ||
1431 '] err code [' || x_err_code_tab(i) ||
1432 ']';
1433 if ( l_debug_mode = 'Y' )
1434 then
1435 pa_cc_utils.log_message(l_stage);
1436 end if;
1437 end if; /* override type */
1438 end loop;
1439 pa_cc_utils.reset_curr_function;
1440 exception
1441 when others
1442 then
1443 if ( l_debug_mode = 'Y' ) then
1444 pa_cc_utils.log_message('final others err '||substr(SQLERRM,1,300));
1445 end if;
1446 pa_cc_utils.reset_curr_function;
1447 raise;
1448 end get_orgn_lvl_cst_info_set;
1449 --------------------------------
1450
1451
1452 END PA_COST_RATE_PUB;