[Home] [Help]
PACKAGE BODY: APPS.PA_COST_RATE_PUB
Source
1 PACKAGE BODY PA_COST_RATE_PUB AS
2 /* $Header: PAXPCRTB.pls 120.5.12010000.3 2008/09/01 04:16:44 rmandali ship $ */
3
4 -- Start of comments
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
62 begin
63
64 ----------------- initializaton ------------------------------
65 if pa_cc_utils.g_debug_mode then
66 l_debug_mode := 'Y';
67 else
68 l_debug_mode := 'N';
69 end if;
70
71
72
73 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
74
75 IF ( l_debug_mode = 'Y' ) THEN
76 pa_debug.set_process( x_process => 'PLSQL'
77 ,x_debug_mode => l_debug_mode
78 );
79 pa_cc_utils.set_curr_function('PA_COST_RATE_PUB.get_labor_rate');
80 pa_cc_utils.log_message('Start ');
81 END IF;
82
83 l_stage := 'Input person_id [' || to_char(p_person_id) || '] job_id ['
84 || to_char(x_job_id) || '] txn_date ['
85 || to_char(p_txn_date) || '] organization_id [' || to_char(x_organization_id) || ']';
86 if ( l_debug_mode = 'Y' )
87 then
88 pa_cc_utils.log_message(l_stage);
89 end if;
90 l_curr_org_id := pa_moac_utils.get_current_org_id; /* Added for bug 7365397 */
91
92 /*===================================================================+
93 | If the calling module is 'REQUIREMENT' - both job id and |
94 | organization id should be provided. |
95 +===================================================================*/
96 if ( p_calling_module = 'REQUIREMENT' and
97 (x_job_id is null or x_organization_id is null ) )
98 then
99 x_err_code := 'NO_JOB_ID' ;
100 /*Bug fix:3089560 Plsql numeric value error causes without resetting stack*/
101 pa_cc_utils.reset_curr_function;
102 return;
103 end if;
104
105 /*2879644*/
106 if p_org_id is not null then
107 g_ou_id := p_org_id;
108 end if ;
109 /*2879644*/
110
111 /*=============================+
112 | Derive Functional Currency. |
113 +=============================*/
114 /*======================================================+
115 | Bug 2879644. Added NVL() while selecting org_id. |
116 | g_func_curr is not used anywhere and hence selecting |
117 | currency_code can be removed later. |
118 +======================================================*/
119 if ( g_func_curr is null or g_ou_id is null ) /* Reverted the fix done via 6908073 for bug 7365397 */
120 /* Removed the g_ou_id nul lcondition for bug 6908073 */
121 then
122 l_stage := 'Selecting Functional Currency';
123 if ( l_debug_mode = 'Y' )
124 then
125 pa_cc_utils.log_message(l_stage);
126 end if;
127 select nvl(p_org_id, imp.org_id)
128 ,sob.currency_code
129 into g_ou_id
130 ,g_func_curr
131 from gl_sets_of_books sob
132 ,pa_implementations imp
133 where imp.set_of_books_id = sob.set_of_books_id and
134 imp.org_id = l_curr_org_id; /* Reverted the fix of 7191479 for bug 7365397 */
135 /* nvl(p_org_id, imp.org_id); /* Added the condition for bug 6908073 */
136 /* Modified the above select for bug 7191479 */
137
138 l_stage := 'Org Id [' || to_char(g_ou_id) || '] Func Curr [' || g_func_curr || ']';
139 if ( l_debug_mode = 'Y' )
140 then
141 pa_cc_utils.log_message(l_stage);
142 end if;
143 end if; /* g_func_curr is null */
144
145 /*====================================================================+
146 | If input organization_id is null, derive it based on the employee. |
147 +====================================================================*/
148 --if ( x_organization_id is null or x_job_id is null ) Commented for bug 5004080
149 if ( p_person_id is not null and (x_organization_id is null or x_job_id is null) )
150 then
151 l_stage := 'Selecting Organization and Job Ids';
152 if ( l_debug_mode = 'Y' )
153 then
154 pa_cc_utils.log_message(l_stage);
155 end if;
156
157 /* cwk changes : Modified stmt to derive the Organization Id, Job Id
158 for a Person Id of a contingent worker also*/
159
160 select nvl(x_organization_id, per.organization_id)
161 ,nvl(x_job_id, per.job_id)
162 into x_organization_id
163 ,x_job_id
164 from per_assignments_f per
165 ,per_assignment_status_types type
166 where trunc(p_txn_date) between trunc(effective_start_date) and trunc(nvl(effective_end_date,p_txn_date))
167 and per.person_id = p_person_id
168 and per.primary_flag = 'Y'
169 and per.assignment_type in ('E', 'C')
170 and per.assignment_status_type_id = type.assignment_status_type_id
171 and type.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
172 ;
173
174 l_stage := 'Organization Id [' || to_char(x_organization_id) || '] Job Id [' || to_char(x_job_id) || ']';
175 if ( l_debug_mode = 'Y' )
176 then
177 pa_cc_utils.log_message(l_stage);
178 end if;
179 end if; /* x_organization_id is null */
180 ----------------- initializaton end ------------------------------
181 /*======================================+
182 | See whether the cache can be reused. |
183 +======================================*/
184 if ( (P_Called_From <> 'R') AND /* Added for 3405326 */
185 x_organization_id = g_rt_organization_id
186 and p_calling_module = g_rt_calling_module
187 and trunc(p_txn_date) between trunc(g_rt_start_date_active) and trunc(nvl(g_rt_end_date_active, p_txn_date))
188 )
189 then
190 if ( g_rt_sch_type = 'EMPLOYEE' and p_person_id = g_rt_person_id )
191 then
192 l_use_cache := 'Y';
193 end if;
194 if ( g_rt_sch_type = 'JOB' and x_job_id = g_rt_job_id )
195 then
196 l_use_cache := 'Y';
197 end if;
198 end if;
199 if ( l_use_cache = 'Y' )
200 then
201 x_organization_id := g_rt_organization_id ;
202 x_cost_rate := g_rt_cost_rate ;
203 x_start_date_active := g_rt_start_date_active ;
204 x_end_date_active := g_rt_end_date_active ;
205 x_org_labor_sch_rule_id := g_rt_org_labor_sch_rule_id ;
206 x_costing_rule := g_rt_costing_rule ;
207 x_rate_sch_id := g_rt_rate_sch_id ;
208 x_cost_rate_curr_code := g_rt_cost_rate_curr_code ;
209 x_acct_rate_type := g_rt_acct_rate_type ;
210 x_acct_rate_date_code := g_rt_acct_rate_date_code ;
211 x_acct_exch_rate := g_rt_acct_exch_rate ;
212 x_ot_project_id := g_rt_ot_project_id ;
213 x_ot_task_id := g_rt_ot_task_id ;
214 x_err_stage := g_rt_err_stage ;
215 x_err_code := g_rt_err_code ;
216 pa_cc_utils.reset_curr_function;
217 return;
218 end if;
219
220 if ( p_calling_module <> 'REQUIREMENT' )
221 then
222 /*================================================+
223 | Check if there is an override for this person. |
224 +================================================*/
225 l_override_flag := 'Y' ;
226 l_stage := 'Selecting Override';
227 if ( l_debug_mode = 'Y' )
228 then
229 pa_cc_utils.log_message(l_stage);
230 end if;
231 begin
232 select detail.compensation_rule_set
233 ,detail.hourly_cost_rate
234 ,detail.rate_schedule_id
235 ,detail.override_type
236 ,detail.cost_rate_currency_code
237 ,detail.acct_rate_type
238 ,detail.acct_rate_date_code
239 ,detail.acct_exchange_rate
240 ,detail.start_date_active
241 ,detail.end_date_active
242 into x_costing_rule
243 ,x_cost_rate
244 ,x_rate_sch_id
245 ,l_override_type
246 ,x_cost_rate_curr_code
247 ,x_acct_rate_type
248 ,x_acct_rate_date_code
249 ,x_acct_exch_rate
250 ,x_start_date_active
251 ,x_end_date_active
252 from pa_compensation_details_all detail /*2879644:Added ALL*/
253 where trunc(p_txn_date) between trunc(detail.start_date_active)
254 and trunc(nvl(detail.end_date_active,p_txn_date))
255 and NVL(detail.org_id,-99) = NVL(g_ou_id,-99) /*2879644 :Added org_id join*/
256 and detail.person_id = p_person_id;
257 exception
258 when no_data_found then
259 l_override_flag := 'N';
260 when too_many_rows then
261 x_err_code := 'DUP_REC';
262 end;
263 if ( x_err_code is not null )
264 then
265 raise USER_EXCEPTION;
266 end if;
267 if ( l_override_type = 'COST_RATE' and x_cost_rate is null )
268 then
269 x_err_code := 'NO_COST_RATE';
270 raise USER_EXCEPTION;
271 end if;
272 if ( l_override_type = 'COST_RATE_SCHEDULE' and x_rate_sch_id is null )
273 then
274 x_err_code := 'NO_COST_RATE_SCH';
275 raise USER_EXCEPTION;
276 end if;
277
278 if ( l_override_flag = 'Y' )
279 then
280 l_stage := 'comp rule [' || x_costing_rule
281 || '] rate [' || to_char( x_cost_rate)
282 || '] x_rate_sch_id [' || to_char( x_rate_sch_id)
283 || '] override type [' || l_override_type
284 || '] override flag [' || l_override_flag
285 || '] crcc type [' || x_cost_rate_curr_code
286 || '] art [' || x_acct_rate_type
287 || '] ardc [' || x_acct_rate_date_code
288 || '] aer [' || to_char(x_acct_exch_rate)
289 || '] start [' || to_char(x_start_date_active)
290 || '] end [' || to_char(x_end_date_active)
291 || ']';
292 if ( l_debug_mode = 'Y' )
293 then
294 pa_cc_utils.log_message(l_stage);
295 end if;
296 /*
297 * If the compensation rule available at the override level
298 * and if the costing method id 'Extension', set rate to null
299 * and return.
300 */
301 if ( x_costing_rule is not null )
302 then
303 l_stage := 'selecting costing method from rule [' || x_costing_rule || ']';
304 if ( l_debug_mode = 'Y' ) then
305 pa_cc_utils.log_message(l_stage);
306 end if;
307 select rule.costing_method
308 into l_costing_method
309 from pa_compensation_rule_sets rule
310 where rule.compensation_rule_set = x_costing_rule
311 ;
312 if ( l_costing_method = 'LABOR_COST_EXTN' )
313 then
314 x_cost_rate := null ;
315 raise USER_EXCEPTION;
316 end if; /*costing method is 'extension'*/
317 end if; /* costing rule is not null */
318 end if; /* override_flag */
319 end if; /* calling module */
320
321 /*=================================================+
322 | If cost rate is obtained, return to the caller. |
323 +=================================================*/
324 if ( l_override_flag = 'Y' and x_cost_rate is not null )
325 then
326 raise USER_EXCEPTION;
327 end if;
328
329 /*==========================================================+
330 | In the absense of an override, traverse the hierarchy. |
331 +==========================================================*/
332 if ( l_override_flag <> 'Y' or x_costing_rule is null)
333 then
334 l_stage := 'Calling get_orgn_level_costing_info with g_ou_id [' || to_char(g_ou_id) ||
335 '] x_organization_id [' || to_char(x_organization_id) ||
336 '] p_person_id [' || to_char(p_person_id) ||
337 '] x_job_id [' || to_char(x_job_id) || ']';
338 if ( l_debug_mode = 'Y' )
339 then
340 pa_cc_utils.log_message(l_stage);
341 end if;
342 pa_cost_rate_pub.get_orgn_level_costing_info
343 ( p_org_id => g_ou_id
344 ,p_organization_id => x_organization_id
345 ,p_person_id => p_person_id
346 ,p_job_id => x_job_id
347 ,p_txn_date => p_txn_date
348 ,p_calling_module => p_calling_module
349 ,x_org_labor_sch_rule_id => l_num_dummy
350 ,x_costing_rule => x_costing_rule
351 ,x_rate_sch_id => x_rate_sch_id
352 ,x_ot_project_id => x_ot_project_id
353 ,x_ot_task_id => x_ot_task_id
354 ,x_cost_rate_curr_code => x_cost_rate_curr_code
355 ,x_acct_rate_type => x_acct_rate_type
356 ,x_acct_rate_date_code => x_acct_rate_date_code
357 ,x_acct_exch_rate => x_acct_exch_rate
358 ,x_err_stage => x_err_stage
359 ,x_err_code => x_err_code
360 ,p_called_from => P_Called_from /*3405326*/
361 );
362 if ( x_err_code is not null )
363 then
364 raise USER_EXCEPTION;
365 end if;
366
367 end if;
368
369 /*===============================================================+
370 | At this point, if any of the vital attributes |
371 | (costing rule, schedule/rate) are not found its an exception. |
372 | costing rule, (schedule or rate) |
373 +===============================================================*/
374 if ( x_costing_rule is null )
375 then
376 x_err_code := 'NO_COSTING_RULE';
377 raise USER_EXCEPTION;
378 end if;
379 if ( x_rate_sch_id is null )
380 then
381 x_err_code := 'NO_COST_RATE_SCH';
382 raise USER_EXCEPTION;
383 end if;
384
385 l_stage := 'selecting costing method from rule [' || x_costing_rule || ']';
386 pa_cc_utils.log_message(l_stage);
387 select rule.costing_method
388 into l_costing_method
389 from pa_compensation_rule_sets rule
390 where rule.compensation_rule_set = x_costing_rule
391 ;
392 if ( l_costing_method = 'LABOR_COST_EXTN' )
393 then
394 x_cost_rate := null ;
395 raise USER_EXCEPTION;
396 end if; /*costing method is 'extension'*/
397
398 /*
399 * Find what Type the schedule is.
400 */
401 l_stage := 'Reading Rate Schedule information for [' || to_char(x_rate_sch_id) || ']';
402 if ( l_debug_mode = 'Y' )
403 then
404 pa_cc_utils.log_message(l_stage);
405 end if;
406 select rate_sch.schedule_type
407 ,rate_sch.rate_sch_currency_code
408 ,rate_sch.job_group_id
409 into l_sch_type
410 ,x_cost_rate_curr_code
411 ,l_job_group_id
412 from pa_std_bill_rate_schedules_all rate_sch
413 where rate_sch.bill_rate_sch_id = x_rate_sch_id;
414
415 l_stage := 'sch type [' || l_sch_type || '] crcc [' || x_cost_rate_curr_code || '] job group [' ||
416 to_char(l_job_group_id) || ']';
417 if ( l_debug_mode = 'Y' )
418 then
419 pa_cc_utils.log_message(l_stage);
420 end if;
421
422 if ( l_sch_type = 'EMPLOYEE')
423 then
424 l_stage := 'Getting rate from this employee type schedule';
425 if ( l_debug_mode = 'Y' )
426 then
427 pa_cc_utils.log_message(l_stage);
428 end if;
429
430 begin
431 select bill_rates.rate
432 ,bill_rates.start_date_active
433 ,bill_rates.end_date_active
434 into x_cost_rate
435 ,x_start_date_active
436 ,x_end_date_active
437 from pa_bill_rates_all bill_rates
438 where trunc(p_txn_date) between trunc(bill_rates.start_date_active)
439 and trunc(nvl(bill_rates.end_date_active,p_txn_date))
440 and bill_rates.person_id = p_person_id
441 and bill_rates.bill_rate_sch_id = x_rate_sch_id
442 ;
443 exception
444 when no_data_found then
445 x_err_code := 'NO_RATE_PERSON';
446 end;
447 else
448 l_stage := 'Job mapping for [' || to_char(x_job_id) || ']';
449 if ( l_debug_mode = 'Y' )
450 then
451 pa_cc_utils.log_message(l_stage);
452 end if;
453 l_dest_job_id := pa_cross_business_grp.IsMappedToJob(x_job_id, l_job_group_id);
454
455 l_stage := 'Getting rate for job id [' || to_char(l_dest_job_id) || ']';
456 if ( l_debug_mode = 'Y' )
457 then
458 pa_cc_utils.log_message(l_stage);
459 end if;
460 begin
461 select bill_rates.rate
462 ,bill_rates.start_date_active
463 ,bill_rates.end_date_active
464 into x_cost_rate
465 ,x_start_date_active
466 ,x_end_date_active
467 from pa_bill_rates_all bill_rates
468 where trunc(p_txn_date) between trunc(bill_rates.start_date_active)
469 and trunc(nvl(bill_rates.end_date_active, p_txn_date))
470 and bill_rates.job_id = l_dest_job_id
471 and bill_rates.bill_rate_sch_id = x_rate_sch_id
472 ;
473 exception
474 when others then
475 x_err_code := 'NO_RATE_JOB';
476 end;
477 end if;
478 if ( x_err_code is not null )
479 then
480 raise USER_EXCEPTION;
481 end if;
482
483 l_stage := 'Rate is [' || to_char(x_cost_rate) || ']';
484 if ( l_debug_mode = 'Y' )
485 then
486 pa_cc_utils.log_message(l_stage);
487 end if;
488
489 if ( l_debug_mode = 'Y' )
490 then
491 pa_cc_utils.log_message('END');
492 end if;
493 pa_cc_utils.reset_curr_function;
494 exception
495 when USER_EXCEPTION
496 then
497 g_rt_calling_module := p_calling_module;
498 g_rt_organization_id := x_organization_id;
499 g_rt_cost_rate := x_cost_rate;
500 g_rt_start_date_active := x_start_date_active;
501 g_rt_end_date_active := x_end_date_active;
502 g_rt_costing_rule := x_costing_rule ;
503 g_rt_rate_sch_id := x_rate_sch_id;
504 g_rt_cost_rate_curr_code := x_cost_rate_curr_code;
505 g_rt_acct_rate_type := x_acct_rate_type;
506 g_rt_acct_rate_date_code := x_acct_rate_date_code;
507 g_rt_acct_exch_rate := x_acct_exch_rate;
508 g_rt_ot_project_id := x_ot_project_id ;
509 g_rt_ot_task_id := x_ot_task_id;
510 g_rt_err_stage := x_err_stage;
511 g_rt_err_code := x_err_code;
512 pa_cc_utils.reset_curr_function;
513 when others then
514 /*Bug fix:3089560 Plsql numeric value error causes without resetting stack*/
515 pa_cc_utils.reset_curr_function;
516 raise;
517 end get_labor_rate;
518 ---------------------------------------------------------------------------------------------
519 PROCEDURE get_orgn_level_costing_info
520 ( p_org_id IN pa_implementations_all.org_id%TYPE
521 ,p_organization_id IN pa_expenditures_all.incurred_by_organization_id%TYPE
522 ,p_person_id IN pa_expenditures_all.incurred_by_person_id%TYPE
523 ,p_job_id IN pa_expenditure_items_all.job_id%TYPE
524 ,p_txn_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
525 ,p_calling_module IN varchar2 default 'STAFFED'
526 ,x_org_labor_sch_rule_id IN OUT NOCOPY pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE
527 ,x_costing_rule IN OUT NOCOPY pa_compensation_rule_sets.compensation_rule_set%TYPE
528 ,x_rate_sch_id IN OUT NOCOPY pa_std_bill_rate_schedules.bill_rate_sch_id%TYPE
529 ,x_ot_project_id IN OUT NOCOPY pa_projects_all.project_id%TYPE
530 ,x_ot_task_id IN OUT NOCOPY pa_tasks.task_id%TYPE
531 ,x_cost_rate_curr_code IN OUT NOCOPY pa_expenditure_items_all.denom_currency_code%TYPE
532 ,x_acct_rate_type IN OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE
533 ,x_acct_rate_date_code IN OUT NOCOPY pa_implementations_all.acct_rate_date_code%TYPE
534 ,x_acct_exch_rate IN OUT NOCOPY pa_compensation_details_all.acct_exchange_rate%TYPE
535 ,x_err_stage IN OUT NOCOPY number
536 ,x_err_code IN OUT NOCOPY varchar2
537 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
538 )
539 is
540
541 l_debug_mode VARCHAR2(1) := 'Y';
542 l_stage VARCHAR2(500);
543
544 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%TYPE;
545 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%TYPE;
546 l_costing_rule pa_compensation_rule_sets.compensation_rule_set%TYPE;
547 l_ot_project_id pa_projects_all.project_id%TYPE;
548 l_ot_task_id pa_tasks.task_id%TYPE;
549 l_acct_rate_date_code pa_org_labor_sch_rule.acct_rate_date_code%TYPE;
550 l_acct_rate_type pa_org_labor_sch_rule.acct_rate_type%TYPE;
551 l_acct_exch_rate pa_org_labor_sch_rule.acct_exchange_rate%TYPE;
552
553 cursor assignment( p_org_id IN pa_implementations_all.org_id%TYPE
554 ,p_organization_id IN pa_expenditures_all.incurred_by_organization_id%TYPE
555 ,p_person_id IN pa_expenditures_all.incurred_by_person_id%TYPE
556 ,p_job_id IN pa_expenditure_items_all.job_id%TYPE
557 ,p_txn_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
558 ,p_calling_module IN varchar2
559 )
560 is
561 select assign.org_labor_sch_rule_id
562 ,decode(p_calling_module ,'REQUIREMENT'
563 ,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
564 ,assign.labor_costing_rule
565 ,assign.overtime_project_id
566 ,assign.overtime_task_id
567 ,assign.acct_rate_date_code
568 ,assign.acct_rate_type
569 ,assign.acct_exchange_rate
570 from pa_org_labor_sch_rule assign
571 ,pa_org_hierarchy_denorm hier
572 ,pa_implementations imp
573 where hier.child_organization_id = p_organization_id
574 and imp.exp_org_structure_version_id=hier.org_hierarchy_version_id
575 and hier.pa_org_use_type = 'TP_SCHEDULE'
576 and assign.organization_id = hier.parent_organization_id
577 and (assign.org_id = p_org_id or assign.org_id is null)
578 and trunc(p_txn_date) between trunc(assign.start_date_active)
579 and trunc(nvl(assign.end_date_active,p_txn_date))
580 and (exists( select null
581 from pa_std_bill_rate_schedules_all rate_sch
582 ,pa_bill_rates_all bill_rates
583 where rate_sch.bill_rate_sch_id =
584 decode(p_calling_module ,'REQUIREMENT'
585 ,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
586 and bill_rates.bill_rate_sch_id = rate_sch.bill_rate_sch_id
587 and rate_sch.schedule_type = 'EMPLOYEE'
588 and bill_rates.person_id = p_person_id
589 and trunc(p_txn_date) between trunc(bill_rates.start_date_active) and
590 trunc(nvl(bill_rates.end_date_active,p_txn_date))
591 )
592 or
593 exists( select null
594 from pa_std_bill_rate_schedules_all rate_sch
595 ,pa_bill_rates_all bill_rates
596 where rate_sch.bill_rate_sch_id =
597 decode(p_calling_module ,'REQUIREMENT'
598 ,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
599 and bill_rates.bill_rate_sch_id = rate_sch.bill_rate_sch_id
600 and rate_sch.schedule_type = 'JOB'
601 and bill_rates.job_id =
602 pa_cross_business_grp.IsMappedToJob(p_job_id, rate_sch.job_group_id)
603 and trunc(p_txn_date) between trunc(bill_rates.start_date_active) and
604 trunc(nvl(bill_rates.end_date_active,p_txn_date))
605 )
606 )
607 order by hier.parent_level desc
608 ,assign.organization_id
609 ,assign.org_id;
610
611 begin
612 if pa_cc_utils.g_debug_mode then
613 l_debug_mode := 'Y';
614 else
615 l_debug_mode := 'N';
616 end if;
617 IF ( l_debug_mode = 'Y' ) THEN
618 pa_debug.set_process( x_process => 'PLSQL'
619 ,x_debug_mode => l_debug_mode
620 );
621 pa_cc_utils.set_curr_function('get_orgn_level_costing_info');
622 pa_cc_utils.log_message('Start ');
623 END IF;
624
625 l_stage := 'org_id [' || to_char(p_org_id) || '] organization_id [' || to_char(p_organization_id) || ']';
626 if ( l_debug_mode = 'Y' )
627 then
628 pa_cc_utils.log_message(l_stage);
629 end if;
630
631 l_stage := 'opening assignment';
632 if ( l_debug_mode = 'Y' )
633 then
634 pa_cc_utils.log_message(l_stage);
635 end if;
636 open assignment (p_org_id,p_organization_id,p_person_id,p_job_id,p_txn_date,p_calling_module);
637
638 l_stage := 'fetching assignment';
639 if ( l_debug_mode = 'Y' )
640 then
641 pa_cc_utils.log_message(l_stage);
642 end if;
643 fetch assignment
644 into l_org_labor_sch_rule_id
645 ,l_rate_sch_id
646 ,l_costing_rule
647 ,l_ot_project_id
648 ,l_ot_task_id
649 ,l_acct_rate_date_code
650 ,l_acct_rate_type
651 ,l_acct_exch_rate;
652
653 if ( assignment%NOTFOUND )
654 then
655 l_stage := 'assignment not found for organization ';
656 if ( l_debug_mode = 'Y' )
657 then
658 pa_cc_utils.log_message(l_stage);
659 end if;
660 /*
661 * This means - neither this organization nor any of its parent
662 * organizations have an assignment. See if there is any assigmnent
663 * at the OU level.
664 */
665 if ( P_Called_From <> 'R' and /* Added for 3405326 */
666 g_ou_org_labor_sch_rule_id is not null and nvl(g_ou_id,-99) = nvl(p_org_id,-99) )
667 then
668 /*
669 * Reuse global - OU level cache.
670 */
671 l_org_labor_sch_rule_id := g_ou_org_labor_sch_rule_id ;
672 l_rate_sch_id := g_ou_cost_rate_sch_id ;
673 l_costing_rule := g_ou_labor_costing_rule ;
674 l_ot_project_id := g_ou_ot_project_id ;
675 l_ot_task_id := g_ou_ot_task_id ;
676 l_acct_rate_date_code := g_ou_acct_rate_date_code ;
677 l_acct_rate_type := g_ou_acct_rate_type ;
678 l_acct_exch_rate := g_ou_acct_exch_rate ;
679 else
680 /*
681 * Unable to reuse cache.
682 */
683 begin
684 l_stage := 'Getting OU level assignment for [' || to_char(p_org_id) || ']';
685 if ( l_debug_mode = 'Y' )
686 then
687 pa_cc_utils.log_message(l_stage);
688 end if;
689 select assign.org_id
690 ,assign.org_labor_sch_rule_id
691 ,decode(p_calling_module ,'REQUIREMENT'
692 ,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
693 ,assign.labor_costing_rule
694 ,assign.overtime_project_id
695 ,assign.overtime_task_id
696 ,assign.acct_rate_date_code
697 ,assign.acct_rate_type
698 ,assign.acct_exchange_rate
699 into g_ou_id
700 ,g_ou_org_labor_sch_rule_id
701 ,g_ou_cost_rate_sch_id
702 ,g_ou_labor_costing_rule
703 ,g_ou_ot_project_id
704 ,g_ou_ot_task_id
705 ,g_ou_acct_rate_date_code
706 ,g_ou_acct_rate_type
707 ,g_ou_acct_exch_rate
708 from pa_org_labor_sch_rule assign
709 where assign.organization_id is null
710 and trunc(p_txn_date) between trunc(assign.start_date_active)
711 and trunc(nvl(assign.end_date_active,p_txn_date))
712 and nvl(assign.org_id,-99) = nvl(p_org_id, -99);
713 exception
714 when no_data_found then
715 l_stage := 'assignment not found at the OU level !!!!!!';
716 if ( l_debug_mode = 'Y' ) then
717 pa_cc_utils.log_message(l_stage);
718 end if;
719 x_err_code := 'NO_RATE';
720 when others then
721 raise;
722 end;
723 /*
724 * Copying global values to local.
725 */
726 l_org_labor_sch_rule_id := g_ou_org_labor_sch_rule_id ;
727 l_rate_sch_id := g_ou_cost_rate_sch_id ;
728 l_costing_rule := g_ou_labor_costing_rule ;
729 l_ot_project_id := g_ou_ot_project_id ;
730 l_ot_task_id := g_ou_ot_task_id ;
731 l_acct_rate_date_code := g_ou_acct_rate_date_code ;
732 l_acct_rate_type := g_ou_acct_rate_type ;
733 l_acct_exch_rate := g_ou_acct_exch_rate ;
734 end if; /* reuse cache ? */
735 end if; /* organization level assignment not found */
736
737 l_stage := 'l_org_labor_sch_rule_id [' || to_char(l_org_labor_sch_rule_id) ||
738 '] l_rate_sch_id [' || to_char( l_rate_sch_id) ||
739 '] l_costing_rule [' || l_costing_rule ||
740 '] l_ot_project_id [' || to_char( l_ot_project_id) ||
741 '] l_ot_task_id [' || to_char( l_ot_task_id) ||
742 '] l_acct_rate_date_code [' || l_acct_rate_date_code ||
743 '] l_acct_rate_type [' || l_acct_rate_type ||
744 '] l_acct_exch_rate [' || to_char( l_acct_exch_rate) ||
745 '] x_err_code [' || x_err_code || ']';
746 if ( l_debug_mode = 'Y' )
747 then
748 pa_cc_utils.log_message(l_stage);
749 end if;
750
751 /*
752 * Move local variables to out parameters.
753 */
754 x_org_labor_sch_rule_id := nvl(x_org_labor_sch_rule_id ,l_org_labor_sch_rule_id);
755 x_rate_sch_id := nvl(x_rate_sch_id ,l_rate_sch_id);
756 x_costing_rule := nvl(x_costing_rule ,l_costing_rule);
757 x_ot_project_id := l_ot_project_id;
758 x_ot_task_id := l_ot_task_id;
759 x_acct_rate_date_code := nvl(x_acct_rate_date_code ,l_acct_rate_date_code);
760 x_acct_rate_type := nvl(x_acct_rate_type ,l_acct_rate_type);
761 x_acct_exch_rate := nvl(x_acct_exch_rate ,l_acct_exch_rate);
762
763 l_stage := 'Closing Cursor';
764 if ( l_debug_mode = 'Y' )
765 then
766 pa_cc_utils.log_message(l_stage);
767 end if;
768 close assignment;
769
770 l_stage := 'END';
771 if ( l_debug_mode = 'Y' )
772 then
773 pa_cc_utils.log_message(l_stage);
774 end if;
775 pa_cc_utils.reset_curr_function;
776
777 exception
778 when others
779 then
780 RAISE;
781 end get_orgn_level_costing_info;
782
783 /*----------------------------------------------------------------------------*/
784 -- Start of Comments
785 -- API name : GetEmpCostRate
786 -- Type : Public Function
787 -- Pre-reqs : None
788 -- Function : To get the emp cost rate.
789 -- Return Value : NUMBER
790 -- Prameters
791 -- P_Person_Id IN NUMBER REQUIRED
792 -- P_Job_Id IN NUMBER OPTIONAL
793 -- P_Organization_Id IN NUMBER OPTIONAL
794 -- P_Effective_Date IN DATE OPTIONAL DEFAULT SYSDATE
795 -- P_Rate_Type IN VARCHAR2 REQUIRED
796 -- -- FUNC for Rate in Functional Currency
797 -- -- DENOM for Rate in Denom Currency
798 -- P_Called_From IN varchar2 DEFAULT 'O' Added for 3405326. 'O'--Others
799 -- 'R' -- Reports
800 -- History
801 -- 03-OCT-02 Vgade -Created
802 --
803 /*----------------------------------------------------------------------------*/
804
805 Function GetEmpCostRate( P_Person_Id IN per_all_people_f.person_id%type
806 ,P_Job_Id IN pa_expenditure_items_all.job_id%type
807 ,P_Organization_Id IN pa_expenditures_all.incurred_by_organization_id%type
808 ,P_Effective_Date IN date
809 ,P_Rate_Type IN varchar2
810 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
811 )
812 RETURN pa_bill_rates_all.rate%type IS
813 l_job_id pa_expenditure_items_all.job_id%type;
814 l_organization_id pa_expenditures_all.incurred_by_organization_id%type;
815 l_org_id pa_expenditures_all.org_id%type; /*2879644*/
816 l_costing_rule pa_compensation_details_all.compensation_rule_set%type;
817 l_cost_rate pa_bill_rates_all.rate%type;
818 l_acct_cost_rate pa_bill_rates_all.rate%type;
819 l_start_date_active date;
820 l_end_date_active date;
821 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%type;
822 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%type;
823 l_override_type pa_compensation_details.override_type%type;
824 l_cost_rate_curr_code pa_compensation_details.cost_rate_currency_code%type;
825 l_acct_rate_type pa_compensation_details.acct_rate_type%type;
826 l_acct_rate_date_code pa_compensation_details.acct_rate_date_code%type;
827 l_acct_exch_rate pa_compensation_details.acct_exchange_rate%type;
828 l_ot_project_id pa_projects_all.project_id%type;
829 l_ot_task_id pa_tasks.task_id%type;
830 l_err_code varchar2(200) default null; /* Default null added for bug 2931397 */
831 l_err_stage number;
832 l_return_rate pa_bill_rates_all.rate%type;
833 l_acct_currency_code varchar2(15);
834 l_conversion_date date;
835 l_numerator number;
836 l_denominator number;
837 Begin
838
839 --pa_cc_utils.log_message('In Getempcost rate');
840 IF (P_Called_From <> 'R') AND /* Added for 3405326 */
841 P_person_id = nvl(G_EMP_PERSON_ID,-99) AND
842 nvl(P_job_id,-99) = nvl(G_EMP_JOB_ID,-99) AND
843 nvl(P_organization_id,-99) = nvl(G_EMP_ORGANIZATION_ID,-99) AND
844 P_effective_date BETWEEN G_EMP_RATE_START_DATE AND nvl(G_EMP_RATE_END_DATE,SYSDATE) THEN
845 NULL; --Do nothing. Values are in the Cache.
846 -- pa_cc_utils.log_message('Getting the Rate from Cache');
847 ELSE
848
849 --pa_cc_utils.log_message('Deriving the Rate');
850 --- Call the api to derive rate and attributes.
851
852 l_job_id := P_job_id; --IN OUT parameter
853 l_organization_id := P_organization_id; --IN OUT parameter.
854
855 Begin
856 PA_COST_RATE_PUB.get_labor_rate ( p_person_id =>P_person_id
857 ,x_job_id =>l_job_id
858 ,p_txn_date =>P_effective_date
859 ,p_org_id =>l_org_id /*2879644*/
860 ,x_organization_id =>l_organization_id
861 ,x_cost_rate =>l_cost_rate
862 ,x_start_date_active =>l_start_date_active
863 ,x_end_date_active =>l_end_date_active
864 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
865 ,x_costing_rule =>l_costing_rule
866 ,x_rate_sch_id =>l_rate_sch_id
867 ,x_cost_rate_curr_code =>l_cost_rate_curr_code
868 ,x_acct_rate_type =>l_acct_rate_type
869 ,x_acct_rate_date_code =>l_acct_rate_date_code
870 ,x_acct_exch_rate =>l_acct_exch_rate
871 ,x_ot_project_id =>l_ot_project_id
872 ,x_ot_task_id =>l_ot_task_id
873 ,x_err_stage =>l_err_stage
874 ,x_err_code =>l_err_code
875 ,p_called_from =>P_called_from /*3405326*/
876 );
877 Exception
878 When OTHERS Then
879 NULL;
880 End;
881
882 /* Following code added for bug 2931397 */
883 If l_err_code is not null
884 then return NULL;
885 End if;
886 /* bug 2931397 */
887
888 -- Cache the Emp Details
889 G_EMP_PERSON_ID := P_person_id;
890 G_EMP_JOB_ID := P_job_id;
891 G_EMP_ORGANIZATION_ID := P_organization_id;
892
893 -- Cache the rate and the attributes
894 G_EMP_COST_RATE := l_cost_rate;
895 G_EMP_RATE_RULE := l_costing_rule;
896 G_EMP_RATE_CURR := l_cost_rate_curr_code;
897 G_EMP_RATE_START_DATE := l_start_date_active;
898 G_EMP_RATE_END_DATE := l_end_date_active;
899
900 IF P_Rate_Type = 'FUNC' THEN --Return the Functional Rate
901
902 -- pa_cc_utils.log_message('Deriving the Functional Rate');
903 -- Get the Functional Currency code
904 l_acct_currency_code := PA_CURRENCY.get_currency_code;
905
906 --Check if the denom and functional currencies are different
907
908 IF l_acct_currency_code <> l_cost_rate_curr_code THEN
909
910 l_conversion_date := P_Effective_Date;
911
912 pa_multi_currency.convert_amount( P_from_currency =>l_cost_rate_curr_code,
913 P_to_currency =>l_acct_currency_code,
914 P_conversion_date =>l_conversion_date,
915 P_conversion_type =>l_acct_rate_type,
916 P_amount =>l_cost_rate,
917 P_user_validate_flag =>'N',
918 P_handle_exception_flag =>'Y', /* changed to 'Y' from 'N' for bug 2931397 */
919 P_converted_amount =>l_acct_cost_rate,
920 P_denominator =>l_denominator,
921 P_numerator =>l_numerator,
922 P_rate =>l_acct_exch_rate,
923 X_status =>l_err_code ) ;
924
925 /* Following code added for bug 2931397 */
926 If l_err_code is not null
927 then return NULL;
928 End if;
929 /* bug 2931397 */
930
931 --Cache the Acct cost rate.
932 G_EMP_ACCT_COST_RATE := l_acct_cost_rate;
933 ELSE
934 --If the call is in FUNC Mode, and the currencies are same
935 G_EMP_ACCT_COST_RATE := l_cost_rate;
936 END IF;
937 END IF;
938 END IF;
939
940 IF P_Rate_Type = 'FUNC' THEN
941 --Return the Functional Rate
942 l_return_rate := G_EMP_ACCT_COST_RATE;
943 ELSE
944 --Return the Transaction Rate
945 l_return_rate := G_EMP_COST_RATE;
946 END IF;
947
948 --pa_cc_utils.log_message('before return rate = '||l_return_rate);
949 Return l_return_rate;
950
951 End GetEmpCostRate;
952
953 /*----------------------------------------------------------------------------*/
954 -- Start of Comments
955 -- API name : GetEmpCostRateInfo
956 -- Type : Public Function
957 -- Pre-reqs : None
958 -- Function : To get the emp cost rate attributes; COMPENSATION RULE,CURRENCY
959 -- CODE, RATE EFFECTIVE START DATE, and RATE EFFECTIVE END DATE.
960 -- Return Value : VARCHAR2
961 -- Prameters
962 -- P_person_id IN NUMBER REQUIRED
963 -- P_job_id IN NUMBER OPTIONAL
964 -- P_organization_id IN NUMBER OPTIONAL
965 -- P_effective_date IN DATE OPTIONAL DEFAULT SYSDATE
966 -- P_Rate_Attribute IN VARCHAR2 REQUIRED
967 -- Valid Values
968 -- RULE for Employee Compensation Rule
969 -- CURR for Rate Currency Code
970 -- START for Rate Effective Start Date.
971 -- END for Rate Effective End Date.
972 -- History
973 -- 03-OCT-02 Vgade -Created
974 --
975 /*----------------------------------------------------------------------------*/
976 Function GetEmpCostRateInfo( P_Person_Id IN per_all_people_f.person_id%type
977 ,P_Job_Id IN pa_expenditure_items_all.job_id%type
978 ,P_Organization_Id IN pa_expenditures_all.incurred_by_organization_id%type
979 ,P_Effective_Date IN date
980 ,P_Rate_Attribute IN varchar2
981 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
982 )
983 RETURN VARCHAR2 IS
984 l_job_id pa_expenditure_items_all.job_id%type;
985 l_organization_id pa_expenditures_all.incurred_by_organization_id%type;
986 l_org_id pa_expenditures_all.org_id%TYPE ; /*2879644*/
987 l_costing_rule pa_compensation_details_all.compensation_rule_set%type;
988 l_cost_rate pa_bill_rates_all.rate%type;
989 l_start_date_active date;
990 l_end_date_active date;
991 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%type;
992 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%type;
993 l_override_type pa_compensation_details.override_type%type;
994 l_cost_rate_curr_code pa_compensation_details.cost_rate_currency_code%type;
995 l_acct_rate_type pa_compensation_details.acct_rate_type%type;
996 l_acct_rate_date_code pa_compensation_details.acct_rate_date_code%type;
997 l_acct_exch_rate pa_compensation_details.acct_exchange_rate%type;
998 l_ot_project_id pa_projects_all.project_id%type;
999 l_ot_task_id pa_tasks.task_id%type;
1000 l_err_code varchar2(200);
1001 l_err_stage number;
1002 l_return_value varchar2(100);
1003 Begin
1004
1005 --pa_cc_utils.log_message('In Getempcostrateinfo...');
1006 IF (P_Called_From <> 'R') AND /* Added for 3405326 */
1007 P_person_id = nvl(G_EMP_PERSON_ID,-99) AND
1008 nvl(P_job_id,-99) = nvl(G_EMP_JOB_ID,-99) AND
1009 nvl(P_organization_id,-99) = nvl(G_EMP_ORGANIZATION_ID,-99) AND
1010 P_effective_date BETWEEN G_EMP_RATE_START_DATE AND nvl(G_EMP_RATE_END_DATE,SYSDATE) THEN
1011 NULL; --Don't do anything. The values are cached already.
1012 --pa_cc_utils.log_message('Getting the Rate Attributes from Cache');
1013 ELSE
1014
1015 --pa_cc_utils.log_message('Deriving the Rate Attributes');
1016 --- Call the api to derive rate and attributes.
1017
1018 l_job_id := P_job_id; --IN OUT parameter
1019 l_organization_id := P_organization_id; --IN OUT parameter.
1020
1021 Begin
1022 PA_COST_RATE_PUB.get_labor_rate ( p_person_id =>P_person_id
1023 ,x_job_id =>l_job_id
1024 ,p_txn_date =>P_effective_date
1025 ,p_org_id =>l_org_id /*2879644*/
1026 ,x_organization_id =>l_organization_id
1027 ,x_cost_rate =>l_cost_rate
1028 ,x_start_date_active =>l_start_date_active
1029 ,x_end_date_active =>l_end_date_active
1030 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
1031 ,x_costing_rule =>l_costing_rule
1032 ,x_rate_sch_id =>l_rate_sch_id
1033 ,x_cost_rate_curr_code =>l_cost_rate_curr_code
1034 ,x_acct_rate_type =>l_acct_rate_type
1035 ,x_acct_rate_date_code =>l_acct_rate_date_code
1036 ,x_acct_exch_rate =>l_acct_exch_rate
1037 ,x_ot_project_id =>l_ot_project_id
1038 ,x_ot_task_id =>l_ot_task_id
1039 ,x_err_stage =>l_err_stage
1040 ,x_err_code =>l_err_code
1041 ,p_called_from =>P_called_from /*3405326*/
1042 );
1043 Exception
1044 When OTHERS Then
1045 NULL;
1046 End;
1047
1048
1049 -- Cache the Emp Details
1050 G_EMP_PERSON_ID := P_person_id;
1051 G_EMP_JOB_ID := P_job_id;
1052 G_EMP_ORGANIZATION_ID := P_organization_id;
1053
1054 -- Cache the rate attributes
1055 G_EMP_COST_RATE := l_cost_rate; /* Added for bug 3624357 */
1056 G_EMP_RATE_RULE := l_costing_rule;
1057 G_EMP_RATE_CURR := l_cost_rate_curr_code;
1058 G_EMP_RATE_START_DATE := l_start_date_active;
1059 G_EMP_RATE_END_DATE := l_end_date_active;
1060 END IF;
1061
1062 IF P_Rate_Attribute = 'RULE' Then
1063 l_return_value := G_EMP_RATE_RULE;
1064 ELSIF P_Rate_Attribute = 'CURR' Then
1065 l_return_value := G_EMP_RATE_CURR;
1066 ELSIF P_Rate_Attribute = 'START' Then
1067 l_return_value := G_EMP_RATE_START_DATE;
1068 ELSIF P_Rate_Attribute = 'END' Then
1069 l_return_value := G_EMP_RATE_END_DATE;
1070 END IF;
1071
1072 Return l_return_value;
1073
1074 End GetEmpCostRateInfo;
1075
1076 --------------------------------
1077 PROCEDURE get_orgn_lvl_cst_info_set
1078 ( p_org_id_tab IN pa_plsql_datatypes.IdTabTyp
1079 ,p_organization_id_tab IN pa_plsql_datatypes.IdTabTyp
1080 ,p_person_id_tab IN pa_plsql_datatypes.IdTabTyp
1081 ,p_job_id_tab IN pa_plsql_datatypes.IdTabTyp
1082 ,p_txn_date_tab IN pa_plsql_datatypes.Char30TabTyp
1083 ,p_override_type_tab IN pa_plsql_datatypes.Char150TabTyp
1084 ,p_calling_module IN varchar2 default 'STAFFED'
1085 ,P_Called_From IN varchar2 DEFAULT 'O' /* Added for 3405326 */
1086 ,x_org_labor_sch_rule_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
1087 ,x_costing_rule_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
1088 ,x_rate_sch_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
1089 ,x_ot_project_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
1090 ,x_ot_task_id_tab IN OUT NOCOPY pa_plsql_datatypes.IdTabTyp
1091 ,x_cost_rate_curr_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
1092 ,x_acct_rate_type_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
1093 ,x_acct_rate_date_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp
1094 ,x_acct_exch_rate_tab IN OUT NOCOPY pa_plsql_datatypes.Char30TabTyp
1095 ,x_err_stage_tab IN OUT NOCOPY pa_plsql_datatypes.NumTabTyp
1096 ,x_err_code_tab IN OUT NOCOPY pa_plsql_datatypes.Char150TabTyp)
1097 is
1098 l_count number := 0;
1099 l_stage varchar2(500);
1100 l_debug_mode varchar2(1) := 'Y';
1101 begin
1102
1103 if pa_cc_utils.g_debug_mode then
1104 l_debug_mode := 'Y';
1105 else
1106 l_debug_mode := 'N';
1107 end if;
1108 IF ( l_debug_mode = 'Y' ) THEN
1109 pa_debug.set_process( x_process => 'PLSQL'
1110 ,x_debug_mode => l_debug_mode
1111 );
1112 pa_cc_utils.set_curr_function('get_orgn_lvl_cst_info_set');
1113 pa_cc_utils.log_message('Start ');
1114 END IF;
1115
1116 l_count := p_organization_id_tab.count ;
1117 l_stage := 'count [' || to_char(l_count) || ']';
1118 if ( l_debug_mode = 'Y')
1119 then
1120 pa_cc_utils.log_message(l_stage);
1121 end if;
1122 for i in 1 .. l_count
1123 loop
1124 if ( p_override_type_tab(i) is null or x_costing_rule_tab(i) is null )
1125 then
1126 l_stage := 'Calling get_orgn_level_costing_info with Org Id [' || to_char( p_org_id_tab(i) ) ||
1127 '] orgn Id [' || to_char( p_organization_id_tab(i) ) ||
1128 '] person Id [' || to_char( p_person_id_tab(i) ) ||
1129 '] job Id [' || to_char( p_job_id_tab(i) ) ||
1130 '] txn date [' || p_txn_date_tab(i) ||
1131 '] cost rule [' || x_costing_rule_tab(i) ||
1132 '] sch id [' || to_char( x_rate_sch_id_tab(i) ) ||
1133 '] ot prj Id [' || to_char( x_ot_project_id_tab(i) ) ||
1134 '] ot tsk Id [' || to_char( x_ot_task_id_tab(i) ) ||
1135 '] crcc [' || x_cost_rate_curr_code_tab(i) ||
1136 '] art [' || x_acct_rate_type_tab(i) ||
1137 '] ardc [' || x_acct_rate_date_code_tab(i) ||
1138 '] aer [' || x_acct_exch_rate_tab(i) ||
1139 ']';
1140 if ( l_debug_mode = 'Y' )
1141 then
1142 pa_cc_utils.log_message(l_stage);
1143 end if;
1144
1145 /*
1146 * GSCC: Handled File.Date.5 for p_txn_date_tab(i).
1147 */
1148 pa_cost_rate_pub.get_orgn_level_costing_info
1149 (p_org_id => p_org_id_tab(i)
1150 ,p_organization_id => p_organization_id_tab(i)
1151 ,p_person_id => p_person_id_tab(i)
1152 ,p_job_id => p_job_id_tab(i)
1153 ,p_txn_date => to_date(p_txn_date_tab(i),'YYYY/MM/DD')
1154 ,p_calling_module => p_calling_module
1155 ,x_org_labor_sch_rule_id => x_org_labor_sch_rule_id_tab(i)
1156 ,x_costing_rule => x_costing_rule_tab(i)
1157 ,x_rate_sch_id => x_rate_sch_id_tab(i)
1158 ,x_ot_project_id => x_ot_project_id_tab(i)
1159 ,x_ot_task_id => x_ot_task_id_tab(i)
1160 ,x_cost_rate_curr_code => x_cost_rate_curr_code_tab(i)
1161 ,x_acct_rate_type => x_acct_rate_type_tab(i)
1162 ,x_acct_rate_date_code => x_acct_rate_date_code_tab(i)
1163 ,x_acct_exch_rate => x_acct_exch_rate_tab(i)
1164 ,x_err_stage => x_err_stage_tab(i)
1165 ,x_err_code => x_err_code_tab(i)
1166 ,p_called_from => p_Called_from /*3405326*/
1167 );
1168
1169 l_stage := 'After Call to get_orgn_level_costing_info, org labor id ['
1170 || to_char(x_org_labor_sch_rule_id_tab(i)) ||
1171 '] cost rule [' || x_costing_rule_tab(i) ||
1172 '] sch Id [' || to_char( x_rate_sch_id_tab(i) ) ||
1173 '] crcc [' || x_costing_rule_tab(i) ||
1174 '] ot proj Id [' || to_char( x_ot_project_id_tab(i) ) ||
1175 '] ot task Id [' || to_char( x_ot_task_id_tab(i) ) ||
1176 '] crcc [' || x_cost_rate_curr_code_tab(i) ||
1177 '] art [' || x_acct_rate_type_tab(i) ||
1178 '] ardc [' || x_acct_rate_date_code_tab(i) ||
1179 '] aer [' || x_acct_exch_rate_tab(i) ||
1180 '] err stg [' || to_char( x_err_stage_tab(i) ) ||
1181 '] err code [' || x_err_code_tab(i) ||
1182 ']';
1183 if ( l_debug_mode = 'Y' )
1184 then
1185 pa_cc_utils.log_message(l_stage);
1186 end if;
1187 end if; /* override type */
1188 end loop;
1189 pa_cc_utils.reset_curr_function;
1190 exception
1191 when others
1192 then
1193 raise;
1194 end get_orgn_lvl_cst_info_set;
1195 --------------------------------
1196
1197
1198 END PA_COST_RATE_PUB;