[Home] [Help]
PACKAGE BODY: APPS.PA_CALC_OVERTIME
Source
1 package body PA_CALC_OVERTIME as
2 /* $Header: PAXDLCOB.pls 120.1.12010000.2 2008/09/16 09:28:40 jravisha ship $ */
3
4 -- The user parameters used are:
5 -- User_ID, Request_ID, Program_ID, Program_App_ID
6 --
7 -- The database columns used are:
8 -- Person_ID, Person_Full_Name, Organization, Expenditure_End_Date,
9 -- Rule_Set, Overtime_Exp_Type
10
11 -- ======================================================================
12 --
13 -- GLOBALS
14 --
15 -- ======================================================================
16
17 TYPE OTaskIDTyp IS TABLE OF number INDEX BY BINARY_INTEGER;
18 TYPE OTaskLCMTyp IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
19 TYPE OTaskNameTyp IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
20
21 OTaskID_Tab OTaskIDTyp;
22 OTaskLCM_Tab OTaskLCMTyp;
23 OTaskName_Tab OTaskNameTyp;
24
25 /*
26 Multi-Currency related changes:
27 New variables added to get currency codes in various currencies.
28 One variable is used to get Txn/Functional currencies because they are same in
29 case of labor.
30 Project rate date and type not populated because the costing program will take care of that
31 */
32 OCurrcode VARCHAR2(15);
33 OProjCurrcode VARCHAR2(15);
34 OProjfuncCurrcode VARCHAR2(15);
35
36 Exp_Group_Created_Flag varchar2(1) := 'N';
37 Exp_Created_Flag varchar2(1) := 'N';
38 G_Org_Id number := NULL ;
39 /* Bug 1756677. Moved out of procedure Insert_Expenditure_And_Group */
40 /* Bug#2373198 Modified data type from varchar2(20 to reflect as is in table */
41 /* overtime_expenditure_group varchar2(20); */
42 overtime_expenditure_group pa_expenditure_groups_all.expenditure_group%type;
43
44
45
46 -- ======================================================================
47 --
48 -- PRIVATE PROCEDURE/FUNCTIONS
49 --
50 -- ======================================================================
51
52 --
53 -- Fetch Job Id for the Overtime Item
54 --
55 FUNCTION Get_Job_ID(
56 X_Expenditure_ID IN number) RETURN number IS
57 Job_ID number;
58 BEGIN
59 SELECT job_id
60 INTO Job_Id
61 FROM per_assignments_f a,
62 pa_expenditures ex
63 WHERE ex.expenditure_id = X_expenditure_id
64 AND a.person_id = ex.incurred_by_person_id
65 AND (a.assignment_type = 'E' OR a.assignment_type = 'C')
66 AND a.primary_flag = 'Y'
67 AND trunc(ex.expenditure_ending_date)
68 BETWEEN a.effective_start_date AND
69 a.effective_end_date;
70 RETURN Job_ID;
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 /*
74 * Bug#1575317
75 * If control comes here, its possible that, the previous select is
76 * unable to get a job_id because, the expenditure_ending_date falls
77 * later to the effective_end_date and there's NO assignments for the
78 * person from then on.
79 * To handle this situation, the effective_end_date is mapped to the
80 * next weekending date - to get the job_id.
81 */
82 SELECT job_id
83 INTO Job_Id
84 FROM per_assignments_f a,
85 pa_expenditures ex
86 WHERE ex.expenditure_id = X_expenditure_id
87 AND a.person_id = ex.incurred_by_person_id
88 AND (a.assignment_type = 'E' OR a.assignment_type = 'C')
89 AND a.primary_flag = 'Y'
90 AND trunc(ex.expenditure_ending_date)
91 BETWEEN a.effective_start_date AND
92 pa_utils.GetWeekEnding(a.effective_end_date);
93
94 RETURN Job_ID;
95 END Get_Job_ID;
96
97 --
98 -- Update old, existing overtime expenditure items
99 -- Set NET_ZERO_ADJUSTMENT_FLAG = 'Y'
100 --
101 PROCEDURE Update_Old_Overtime_Item (
102 Temp_Task IN number,
103 R_P_User_ID IN number,
104 R_P_Program_ID IN number,
105 R_P_Request_ID IN number,
106 R_P_Program_App_ID IN number,
107 R_Person_Id IN number,
108 R_Expenditure_End_Date IN date,
109 R_Overtime_Exp_Type IN varchar2) IS
110 X_status number;
111 BEGIN
112 FOR c IN
113 (select expenditure_item_id
114 from pa_expenditure_items i
115 ,pa_expenditures e
116 where i.system_linkage_function ||'' = 'OT'
117 and i.expenditure_id = e.expenditure_id
118 and e.incurred_by_person_id = R_Person_Id
119 and e.expenditure_ending_date = R_Expenditure_End_Date
120 and i.task_id = Temp_Task
121 and i.expenditure_item_date = R_Expenditure_End_Date
122 and i.expenditure_type = R_Overtime_Exp_Type
123 and nvl(i.net_zero_adjustment_flag,'N') <> 'Y') LOOP
124 Pa_Adjustments.SetNetZero(
125 c.expenditure_item_id
126 ,R_P_User_ID
127 ,0
128 ,X_status);
129 END LOOP;
130 END Update_Old_Overtime_Item;
131
132 --
133 -- Reverse old overtime expenditure items
134 --
135 -- Set adjusted_expenditure_item_id = old overtime expenditure item id.
136 -- Set NET_ZERO_ADJUSTMENT_FLAG = 'Y'
137 --
138 PROCEDURE Reverse_Old_Overtime_Item (
139 X_Expenditure_ID IN number,
140 Temp_Task IN number,
141 R_P_User_ID IN number,
142 R_P_Request_ID IN number,
143 R_P_Program_ID IN number,
144 R_P_Program_App_ID IN number,
145 R_Person_Id IN number,
146 R_Expenditure_End_Date IN date,
147 R_Overtime_Exp_Type IN varchar2) IS
148 i BINARY_INTEGER := 0;
149 X_status NUMBER;
150 BEGIN
151 FOR c IN
152 (select
153 PA_EXPENDITURE_ITEMS_S.NEXTVAL X_expenditure_item_id
154 ,i.expenditure_id X_expenditure_id
155 ,i.expenditure_item_date X_expenditure_item_date
156 ,i.task_id X_task_id
157 ,i.expenditure_type X_expenditure_type
158 ,i.non_labor_resource X_non_labor_resource
159 ,i.organization_id X_nl_resource_org_id
160 ,i.quantity* -1 X_quantity
161 ,i.raw_cost* -1 X_raw_cost
162 ,i.raw_cost_rate X_raw_cost_rate
163 ,i.override_to_organization_id X_override_to_org_id
164 ,i.billable_flag X_billable_flag
165 ,i.bill_hold_flag X_bill_hold_flag
166 ,i.orig_transaction_reference X_orig_transaction_ref
167 ,i.transferred_from_exp_item_id X_transferred_from_ei
168 ,i.adjusted_expenditure_item_id X_adj_expend_item_id
169 ,i.attribute_category X_attribute_category
170 ,i.attribute1 X_attribute1
171 ,i.attribute2 X_attribute2
172 ,i.attribute3 X_attribute3
173 ,i.attribute4 X_attribute4
174 ,i.attribute5 X_attribute5
175 ,i.attribute6 X_attribute6
176 ,i.attribute7 X_attribute7
177 ,i.attribute8 X_attribute8
178 ,i.attribute9 X_attribute9
179 ,i.attribute10 X_attribute10
180 ,NULL X_ei_comment
181 ,i.transaction_source X_transaction_source
182 ,i.source_expenditure_item_id X_source_exp_item_id
183 ,i.job_id X_job_id
184 ,i.org_id X_org_id
185 ,i.labor_cost_multiplier_name X_labor_cost_multiplier_name
186 ,NULL X_drccid
187 ,NULL X_crccid
188 ,NULL X_cdlsr1
189 ,NULL X_cdlsr2
190 ,NULL X_cdlsr3
191 ,NULL X_gldate
192 ,i.burden_cost* -1 X_bcost
193 ,i.burden_cost_rate X_bcostrate
194 ,i.system_linkage_function X_etypeclass
195 ,i.burden_sum_dest_run_id X_burden_sum_dest_run_id
196 ,i.cost_ind_compiled_set_id X_burden_Compile_set_id
197 ,i.receipt_currency_amount X_receipt_currency_amount
198 ,i.receipt_currency_code X_receipt_currency_code
199 ,i.receipt_exchange_rate X_receipt_exchange_rate
200 ,i.denom_currency_code X_denom_currency_code
201 ,i.denom_raw_cost* -1 X_denom_raw_cost
202 ,i.denom_burdened_cost* -1 X_denom_burdened_cost
203 ,i.acct_currency_code X_acct_currency_code
204 ,i.acct_rate_date X_acct_rate_date
205 ,i.acct_rate_type X_acct_rate_type
206 ,i.acct_exchange_rate X_acct_exchange_rate
207 ,i.acct_raw_cost* -1 X_acct_raw_cost
208 ,i.acct_burdened_Cost* -1 X_acct_burdened_cost
209 ,i.acct_exchange_rounding_limit X_acct_exchange_rounding_limit
210 ,i.project_currency_code X_project_currency_code
211 ,i.project_rate_date X_project_rate_date
212 ,i.project_rate_type X_project_rate_type
213 ,i.project_exchange_rate X_project_exchange_rate
214 ,i.CC_CROSS_CHARGE_CODE CC_CROSS_CHARGE_CODE
215 ,i.CC_PRVDR_ORGANIZATION_ID CC_PRVDR_ORGANIZATION_ID
216 ,i.CC_RECVR_ORGANIZATION_ID CC_RECVR_ORGANIZATION_ID
217 ,i.DENOM_TP_CURRENCY_CODE DENOM_TP_CURRENCY_CODE
218 ,i.DENOM_TRANSFER_PRICE DENOM_TRANSFER_PRICE
219 ,i.ACCT_TP_RATE_TYPE ACCT_TP_RATE_TYPE
220 ,i.ACCT_TP_RATE_DATE ACCT_TP_RATE_DATE
221 ,i.ACCT_TP_EXCHANGE_RATE ACCT_TP_EXCHANGE_RATE
222 ,i.ACCT_TRANSFER_PRICE ACCT_TRANSFER_PRICE
223 ,i.PROJACCT_TRANSFER_PRICE PROJACCT_TRANSFER_PRICE
224 ,i.CC_MARKUP_BASE_CODE CC_MARKUP_BASE_CODE
225 ,i.TP_BASE_AMOUNT TP_BASE_AMOUNT
226 ,i.CC_CROSS_CHARGE_TYPE CC_CROSS_CHARGE_TYPE
227 ,i.RECVR_ORG_ID RECVR_ORG_ID
228 ,decode(i.CC_CROSS_CHARGE_CODE,'B','N','X') CC_BL_DISTRIBUTED_CODE
229 ,decode(i.CC_CROSS_CHARGE_CODE,'I','N','X') CC_IC_PROCESSED_CODE
230 ,i.TP_IND_COMPILED_SET_ID TP_IND_COMPILED_SET_ID
231 ,i.TP_BILL_RATE TP_BILL_RATE
232 ,i.TP_BILL_MARKUP_PERCENTAGE TP_BILL_MARKUP_PERCENTAGE
233 ,i.TP_SCHEDULE_LINE_PERCENTAGE TP_SCHEDULE_LINE_PERCENTAGE
234 ,i.TP_RULE_PERCENTAGE TP_RULE_PERCENTAGE
235 ,i.project_id X_project_id -- Bugfix:2201207
236 ,i.projfunc_currency_code X_projfunc_currency_code
237 ,i.projfunc_cost_rate_date X_projfunc_cost_rate_date
238 ,i.projfunc_cost_rate_type X_projfunc_cost_rate_type
239 ,i.projfunc_cost_exchange_rate X_projfunc_cost_exchg_rate
240 ,i.assignment_id X_assignment_id
241 ,i.work_type_id X_work_type_id
242 ,i.tp_amt_type_code X_tp_amt_type_code
243 ,i.project_raw_cost* -1 x_project_raw_cost
244 ,i.project_burdened_cost* -1 x_project_burdened_cost
245 from pa_expenditure_items i
246 ,pa_expenditures e
247 where i.system_linkage_function ||'' = 'OT'
248 and i.expenditure_id = e.expenditure_id
249 and e.incurred_by_person_id = R_Person_Id
250 and e.expenditure_ending_date = R_Expenditure_End_Date
251 and i.task_id = Temp_task
252 and i.expenditure_item_date = R_Expenditure_End_Date
253 and i.expenditure_type = R_Overtime_Exp_Type
254 and nvl(i.net_zero_adjustment_flag,'N') <> 'Y') LOOP
255
256 i := i + 1;
257 /*
258 Multi-currency related changes:
259 Get All the attributes from the parent ei and pass it to the reversed ei.
260 */
261 /*
262 IC Changes: Get IC attribute from parent and pass to reversed ei.
263 */
264 Pa_Transactions.Loadei(
265 X_expenditure_item_id => c.X_expenditure_item_id
266 , X_expenditure_id => c.X_expenditure_id
267 , X_expenditure_item_date => c.X_expenditure_item_date
268 , X_project_id => c.X_project_id -- Bugfix : 2201207 NULL
269 , X_task_id => c.X_task_id
270 , X_expenditure_type => c.X_expenditure_type
271 , X_non_labor_resource => c.X_non_labor_resource
272 , X_nl_resource_org_id => c.X_nl_resource_org_id
273 , X_quantity => c.X_quantity
274 , X_raw_cost => c.X_raw_cost
275 , X_raw_cost_rate => c.X_raw_cost_rate
276 , X_override_to_org_id => c.X_override_to_org_id
277 , X_billable_flag => c.X_billable_flag
278 , X_bill_hold_flag => c.X_bill_hold_flag
279 , X_orig_transaction_ref => c.X_orig_transaction_ref
280 , X_transferred_from_ei => c.X_transferred_from_ei
281 , X_adj_expend_item_id => c.X_adj_expend_item_id
282 , X_attribute_category => c.X_attribute_category
283 , X_attribute1 => c.X_attribute1
284 , X_attribute2 => c.X_attribute2
285 , X_attribute3 => c.X_attribute3
286 , X_attribute4 => c.X_attribute4
287 , X_attribute5 => c.X_attribute5
288 , X_attribute6 => c.X_attribute6
289 , X_attribute7 => c.X_attribute7
290 , X_attribute8 => c.X_attribute8
291 , X_attribute9 => c.X_attribute9
292 , X_attribute10 => c.X_attribute10
293 , X_ei_comment => c.X_ei_comment
294 , X_transaction_source => c.X_transaction_source
295 , X_source_exp_item_id => c.X_source_exp_item_id
296 , i => i
297 , X_job_id => c.X_job_id
298 , X_org_id => c.X_org_id
299 , X_labor_cost_multiplier_name => c.X_labor_cost_multiplier_name
300 , X_drccid => c.X_drccid
301 , X_crccid => c.X_crccid
302 , X_cdlsr1 => c.X_cdlsr1
303 , X_cdlsr2 => c.X_cdlsr2
304 , X_cdlsr3 => c.X_cdlsr3
305 , X_gldate => c.X_gldate
306 , X_bcost => c.X_bcost
307 , X_bcostrate => c.X_bcostrate
308 , X_etypeclass => c.X_etypeclass
309 , X_burden_sum_dest_run_id => c.X_burden_sum_dest_run_id
310 , X_burden_compile_set_id => c.X_burden_Compile_set_id
311 , X_receipt_currency_amount => c.X_receipt_currency_amount
312 , X_receipt_currency_code => c.X_receipt_currency_code
313 , X_receipt_exchange_rate => c.X_receipt_exchange_rate
314 , X_denom_currency_code => c.X_denom_currency_code
315 , X_denom_raw_cost => c.X_denom_raw_cost
316 , X_denom_burdened_cost => c.X_denom_burdened_cost
317 , X_acct_currency_code => c.X_acct_currency_code
318 , X_acct_rate_date => c.X_acct_rate_date
319 , X_acct_rate_type => c.X_acct_rate_type
320 , X_acct_exchange_rate => c.X_acct_exchange_rate
321 , X_acct_raw_cost => c.X_acct_raw_cost
322 , X_acct_burdened_cost => c.X_acct_burdened_cost
323 , X_acct_exchange_rounding_limit => c.X_acct_exchange_rounding_limit
324 , X_project_currency_code => c.X_project_currency_code
325 , X_project_rate_date => c.X_project_rate_date
326 , X_project_rate_type => c.X_project_rate_type
327 , X_project_exchange_rate => c.X_project_exchange_rate
328 , X_Cross_Charge_Type => c.CC_CROSS_CHARGE_TYPE
329 , X_Cross_Charge_Code => c.CC_CROSS_CHARGE_CODE
330 , X_Prvdr_organization_id => c.CC_PRVDR_ORGANIZATION_ID
331 , X_Recv_organization_id => c.CC_RECVR_ORGANIZATION_ID
332 , X_Recv_Operating_Unit => c.RECVR_ORG_ID
333 , X_Borrow_Lent_Dist_Code => c.CC_BL_DISTRIBUTED_CODE
334 , X_Ic_Processed_Code => c.CC_IC_PROCESSED_CODE
335 , X_Denom_Tp_Currency_Code => c.DENOM_TP_CURRENCY_CODE
336 , X_Denom_Transfer_Price => c.DENOM_TRANSFER_PRICE
337 , X_Acct_Tp_Rate_Type => c.ACCT_TP_RATE_TYPE
338 , X_Acct_Tp_Rate_Date => c.ACCT_TP_RATE_DATE
339 , X_Acct_Tp_Exchange_Rate => c.ACCT_TP_EXCHANGE_RATE
340 , X_ACCT_TRANSFER_PRICE => c.ACCT_TRANSFER_PRICE
341 , X_PROJACCT_TRANSFER_PRICE => c.PROJACCT_TRANSFER_PRICE
342 , X_CC_MARKUP_BASE_CODE => c.CC_MARKUP_BASE_CODE
343 , X_TP_BASE_AMOUNT => c.TP_BASE_AMOUNT
344 , X_TP_IND_COMPILED_SET_ID => c.TP_IND_COMPILED_SET_ID
345 , X_TP_BILL_RATE => c.TP_BILL_RATE
346 , X_TP_BILL_MARKUP_PERCENTAGE => c.TP_BILL_MARKUP_PERCENTAGE
347 , X_TP_SCHEDULE_LINE_PERCENTAGE => c.TP_SCHEDULE_LINE_PERCENTAGE
348 , X_TP_RULE_PERCENTAGE => c.TP_RULE_PERCENTAGE
349 , p_assignment_id => c.x_assignment_id
350 , p_work_type_id => c.x_work_type_id
351 , p_projfunc_currency_code => c.x_projfunc_currency_code
352 , p_projfunc_cost_rate_date => c.x_projfunc_cost_rate_date
353 , p_projfunc_cost_rate_type => c.x_projfunc_cost_rate_type
354 , p_projfunc_cost_exchange_rate => c.x_projfunc_cost_exchg_rate
355 , p_project_raw_cost => c.x_project_raw_cost
356 , p_project_burdened_cost => c.x_project_burdened_cost
357 , p_tp_amt_type_code => c.x_tp_amt_type_code );
358
359 END LOOP;
360
361 Pa_Transactions.InsItems(
362 R_P_User_ID
363 ,0 -- last_update_login
364 ,NULL -- module
365 ,NULL -- calling_process
366 ,i -- Rows
367 ,X_status
368 ,NULL -- gl_flag
369 );
370
371 Pa_Transactions.FlushEiTabs;
372 END Reverse_Old_Overtime_Item;
373
374 --
375 -- Insert Expenditure Group and Expenditure only if
376 -- not already inserted for person and week in this run
377 --
378 PROCEDURE Insert_Expenditure_And_Group(
379 Expenditure_ID IN OUT NOCOPY number,
380 R_P_User_ID IN number,
381 R_P_Program_ID IN number,
382 R_P_Request_ID IN number,
383 R_P_Program_App_ID IN number,
384 R_Person_Id IN number,
385 R_Expenditure_End_Date IN Date,
386 R_Overtime_Exp_Type IN varchar2,
387 R_Organization IN number) IS
388 Cycle_Start_Day number;
389 l_org_id NUMBER:= PA_MOAC_UTILS.get_current_org_id ; /*6317198*/
390
391 /* overtime_expenditure_group varchar2(20); 1756677 */
392 BEGIN
393 IF Exp_Group_Created_Flag <> 'Y' THEN
394
395 -- Sel_Cycle_Day()
396 SELECT Exp_Cycle_Start_Day_Code
397 INTO Cycle_Start_Day
398 FROM PA_Implementations;
399
400 -- Sel_Overtime_Expend_Group_Name()
401 select 'PREMIUM - ' || to_char(R_P_Request_ID)
402 into overtime_expenditure_group
403 from sys.dual;
404 -- Insert_Expenditure_Group()
405 Pa_Transactions.InsertExpGroup(
406 Overtime_Expenditure_Group
407 ,'RELEASED'
408 ,trunc(sysdate) - to_number(to_char(sysdate-Cycle_Start_Day+1,'D')) + 7
409 ,'ST'
410 ,R_P_User_ID
411 ,NULL
412 ,NULL /*6317198*/
413 ,l_org_id /*6317198*/ );
414 Exp_Group_Created_Flag := 'Y';
415 END IF;
416
417 IF Exp_Created_Flag <> 'Y' THEN
418 -- Sel_Expenditure_ID()
419 select PA_EXPENDITURES_S.NEXTVAL INTO Expenditure_ID FROM sys.dual;
420
421 Pa_Transactions.InsertExp(
422 X_expenditure_id => Expenditure_ID,
423 X_expend_status => 'APPROVED',
424 X_expend_ending => R_Expenditure_End_Date,
425 X_expend_class => 'PT',
426 X_inc_by_person => R_Person_Id,
427 X_inc_by_org => R_Organization,
428 X_expend_group => Overtime_Expenditure_Group,
429 X_entered_by_id => R_P_User_ID,
430 X_created_by_id => R_P_User_ID,
431 X_attribute_category => NULL,
432 X_attribute1 => NULL,
433 X_attribute2 => NULL,
434 X_attribute3 => NULL,
435 X_attribute4 => NULL,
436 X_attribute5 => NULL,
437 X_attribute6 => NULL,
438 X_attribute7 => NULL,
439 X_attribute8 => NULL,
440 X_attribute9 => NULL,
441 X_attribute10 => NULL,
442 X_description => 'System created temporary overtime expenditure',
443 X_control_total => NULL,
444 P_Org_Id => l_org_id /*6317198*/ );
445
446 Exp_Created_Flag := 'Y';
447 END IF;
448 END Insert_Expenditure_And_Group;
449
450 --
451 -- Insert overtime items, determining whether the new overtime total
452 -- is the same as the existing overtime total.
453 --
454 -- If the new OT total does NOT equal the existing overtime total,
455 -- the existing overtime items are reversed and the new items are created.
456 -- When the old items are reversed, the adjusted item and reversed item
457 -- is marked with NET_ZERO_ADJUSTMENT_FLAG = 'Y' to note that the item
458 -- is fully reversed.
459 --
460 -- A new overtime item is created only if the total hours <> 0.
461 --
462 PROCEDURE Insert_Overtime_Items(
463 Temp_Existing_Hours IN number,
464 Temp_Actual_Hours IN number,
465 Temp_Task IN number,
466 Temp_LCM IN varchar2,
467 Expenditure_ID IN OUT NOCOPY number,
468 R_P_User_ID IN number,
469 R_P_Program_ID IN number,
470 R_P_Request_ID IN number,
471 R_P_Program_App_ID IN number,
472 R_Person_Id IN number,
473 R_Expenditure_End_Date IN date,
474 R_Overtime_Exp_Type IN varchar2,
475 R_Organization IN number) IS
476 Any_Data_Flag varchar2(1);
477 Job_ID number;
478 X_expenditure_item_id number;
479 X_status NUMBER;
480 x_project_id NUMBER;
481 x_tp_amt_type_code varchar2(100);
482 x_assignment_id number;
483 x_assignment_name varchar2(100);
484 x_work_type_id number;
485 x_work_type_name varchar2(100);
486 x_return_status varchar2(100);
487 x_error_message_code varchar2(1000);
488 x_projfunc_currency_code varchar2(30);
489
490 cursor get_proj_id IS
491 select p.project_id
492 ,p.projfunc_currency_code
493 from pa_tasks t
494 ,pa_projects p
495 where t.task_id = Temp_Task
496 and t.project_id = p.project_id;
497
498 BEGIN
499
500 /** Added EPP and project currency changes **/
501 -- get the project id for the temp task
502 If Temp_Task is not null then
503 OPEN get_proj_id;
504 FETCH get_proj_id into
505 x_project_id
506 ,x_projfunc_currency_code;
507 CLOSE get_proj_id;
508 End if;
509 -- if the work type profile is installed
510 -- get the assignment details and work type details for the given project and tasks
511 IF PA_UTILS4.is_exp_work_type_enabled = 'Y' then
512 PA_UTILS4.get_work_assignment
513 (p_person_id => R_Person_Id
514 ,p_project_id => x_project_id
515 ,p_task_id => temp_task
516 ,p_ei_date => R_Expenditure_End_Date
517 ,p_system_linkage => 'OT'
518 ,x_tp_amt_type_code => x_tp_amt_type_code
519 ,x_assignment_id => x_assignment_id
520 ,x_assignment_name => x_assignment_name
521 ,x_work_type_id => x_work_type_id
522 ,x_work_type_name => x_work_type_name
523 ,x_return_status => x_return_status
524 ,x_error_message_code => x_error_message_code );
525 -- donot error out generating OT lines even if the work type is not enabled
526 -- or assignment is not scheduled. populate OT lines with NULL values
527 IF x_return_status <> 'S' then
528 x_work_type_id := NULL;
529 x_assignment_id := NULL;
530 x_tp_amt_type_code := NULL;
531 END IF;
532
533
534
535 ELSE
536 x_tp_amt_type_code := NULL;
537 x_assignment_id := NULL;
538 x_work_type_id := NULL;
539
540 END IF;
541
542 /** end of EPP and project currency changes **/
543
544
545 IF Temp_Existing_Hours <> Temp_Actual_Hours THEN
546 Insert_Expenditure_And_Group(
547 Expenditure_ID,
548 R_P_User_ID,
549 R_P_Program_ID,
550 R_P_Request_ID,
551 R_P_Program_App_ID,
552 R_Person_Id,
553 R_Expenditure_End_Date,
554 R_Overtime_Exp_Type,
555 R_Organization);
556 Reverse_Old_Overtime_Item(
557 Expenditure_ID,
558 Temp_Task,
559 R_P_User_ID,
560 R_P_Request_ID,
561 R_P_Program_ID,
562 R_P_Program_App_ID,
563 R_Person_Id,
564 R_Expenditure_End_Date,
565 R_Overtime_Exp_Type);
566 Update_Old_Overtime_Item(
567 Temp_Task,
568 R_P_User_ID,
569 R_P_Program_ID,
570 R_P_Request_ID,
571 R_P_Program_App_ID,
572 R_Person_Id,
573 R_Expenditure_End_Date,
574 R_Overtime_Exp_Type);
575
576 IF Temp_Actual_Hours <> 0 THEN
577
578 Job_ID := Get_Job_ID(Expenditure_ID);
579
580 -- insert_exp_item()
581 select PA_EXPENDITURE_ITEMS_S.NEXTVAL
582 into X_expenditure_item_id
583 from sys.dual;
584
585 /*
586 Multi-currency related changes:
587 Pass Txn, Functional and Project currency
588 */
589 ---------------------------------------------------------------------
590 -- Bug 911108: Modified to put 'OT' into the expenditure type class
591 -- (system linkage) field
592 ---------------------------------------------------------------------
593 /* Not modified for IC, bcoz the IC attributes are determined by
594 IC identification process. The IC identification process will
595 pick EI's that have cross_charge_code = P( which is the default
596 for LOadEi API)
597 */
598 /*
599 * IC related change:
600 * Recvr_Org_Id is populated for the Overtime Item
601 */
602 Pa_Transactions.Loadei(
603 X_expenditure_item_id => X_expenditure_item_id
604 , X_expenditure_id => Expenditure_ID
605 , X_expenditure_item_date => R_Expenditure_End_Date
606 , X_project_id => x_project_id --Bugfix: 2201207 NULL
607 , X_task_id => Temp_Task
608 , X_expenditure_type => R_Overtime_Exp_Type
609 , X_non_labor_resource => NULL
610 , X_nl_resource_org_id => NULL
611 , X_quantity => Temp_Actual_Hours
612 , X_raw_cost => NULL
613 , X_raw_cost_rate => NULL
614 , X_override_to_org_id => NULL
615 , X_billable_flag => 'N'
616 , X_bill_hold_flag => 'N'
617 , X_orig_transaction_ref => NULL
618 , X_transferred_from_ei => NULL
619 , X_adj_expend_item_id => NULL
620 , X_attribute_category => NULL
621 , X_attribute1 => NULL
622 , X_attribute2 => NULL
623 , X_attribute3 => NULL
624 , X_attribute4 => NULL
625 , X_attribute5 => NULL
626 , X_attribute6 => NULL
627 , X_attribute7 => NULL
628 , X_attribute8 => NULL
629 , X_attribute9 => NULL
630 , X_attribute10 => NULL
631 , X_ei_comment => NULL
632 , X_transaction_source => NULL
633 , X_source_exp_item_id => NULL
634 , i => 1
635 , X_job_id => Job_ID
636 , X_org_id => G_org_id
637 /* Bug# 1483807 */
638 , X_labor_cost_multiplier_name => Temp_LCM
639 , X_drccid => NULL
640 , X_crccid => NULL
641 , X_cdlsr1 => NULL
642 , X_cdlsr2 => NULL
643 , X_cdlsr3 => NULL
644 , X_gldate => NULL
645 , X_bcost => NULL
646 , X_bcostrate => NULL
647 , X_etypeclass => 'OT'
648 , X_burden_sum_dest_run_id => NULL
649 , X_burden_compile_set_id => NULL
650 , X_receipt_currency_amount => NULL
651 , X_receipt_currency_code => NULL
652 , X_receipt_exchange_rate => NULL
653 , X_denom_currency_code => OCurrCode
654 , X_denom_raw_cost => NULL
655 , X_denom_burdened_cost => NULL
656 , X_acct_currency_code => OCurrCode
657 , X_acct_rate_date => NULL
658 , X_acct_rate_type => NULL
659 , X_acct_exchange_rate => NULL
660 , X_acct_raw_cost => NULL
661 , X_acct_burdened_cost => NULL
662 , X_acct_exchange_rounding_limit => NULL
663 , X_project_currency_code => OProjCurrCode
664 , X_project_rate_date => NULL
665 , X_project_rate_type => NULL
666 , X_project_exchange_rate => NULL
667 , X_recv_operating_unit => PA_UTILS2.GetPrjOrgId(NULL,temp_task)
668 /** added EPP and project currency changes **/
669 , p_assignment_id => x_assignment_id
670 , p_work_type_id => NULL /* Changed for labor costing enhancements */
671 , p_projfunc_currency_code => x_projfunc_currency_code
672 , p_projfunc_cost_rate_date => NULL
673 , p_projfunc_cost_rate_type => NULL
674 , p_projfunc_cost_exchange_rate => NULL
675 , p_project_raw_cost => NULL
676 , p_project_burdened_cost => NULL
677 , p_tp_amt_type_code => NULL /* Changed for labor costing enhancements */
678 );
679 /** end of EPP and project currency changes **/
680 Pa_Transactions.InsItems(
681 R_P_User_ID
682 ,0 -- last_update_login
683 ,NULL -- module
684 ,NULL -- calling_process
685 ,1 -- rows
686 ,X_status
687 ,NULL -- gl_flag
688 );
689 Pa_Transactions.FlushEiTabs;
690 END IF;
691 END IF;
692 END Insert_Overtime_Items;
693
694 --
695 -- Calculate Overtime based on the totals and the compensation rule
696 --
697 PROCEDURE Calc_OT (
698 Total_Hours IN number,
699 Sunday IN number,
700 Monday IN number,
701 Tuesday IN number,
702 Wednesday IN number,
703 Thursday IN number,
704 Friday IN number,
705 Saturday IN number,
706 Double_Time_Hours IN OUT NOCOPY number,
707 Time_And_A_Half_Hours IN OUT NOCOPY number,
708 Uncompensated_Hours IN OUT NOCOPY number,
709 Extra_OT_Hours_1 IN OUT NOCOPY number,
710 Extra_OT_Hours_2 IN OUT NOCOPY number,
711 R_Rule_Set IN varchar2) IS
712
713 PROCEDURE Calc_Daily_Overtime(
714 Day_Total IN number,
715 Double_Time_Hours IN OUT NOCOPY number,
716 Time_And_A_Half_Hours IN OUT NOCOPY number) IS
717 BEGIN
718 IF Day_Total > 12 THEN
719 Double_Time_Hours := Double_Time_Hours + (Day_Total - 12);
720 Time_And_A_Half_Hours := Time_And_A_Half_Hours + 4;
721 ELSIF Day_Total > 8 THEN
722 Time_And_A_Half_Hours := Time_And_A_Half_Hours + (Day_Total - 8);
723 END IF;
724 END Calc_Daily_Overtime;
725
726 BEGIN
727 -- Reset hours for every person/period pair
728 Double_Time_Hours := 0;
729 Time_And_A_Half_Hours := 0;
730 Uncompensated_Hours := 0;
731 Extra_OT_Hours_1 := 0;
732 Extra_OT_Hours_2 := 0;
733
734 IF R_Rule_Set = 'Compensated' THEN
735 IF Total_Hours > 80 THEN
736 Double_Time_Hours := Double_Time_Hours + (Total_Hours - 80);
737 Time_And_A_Half_Hours := Time_And_A_Half_Hours + 40;
738 ELSIF Total_Hours > 40 THEN
739 Time_And_A_Half_Hours := Time_And_A_Half_Hours + (Total_Hours - 40);
740 END IF;
741 ELSIF R_Rule_Set = 'Exempt' THEN
742 IF Total_Hours > 40 THEN
743 Uncompensated_Hours := Uncompensated_Hours + (Total_Hours - 40);
744 END IF;
745 ELSIF R_Rule_Set = 'Hourly' THEN
746 Calc_Daily_Overtime(Monday, Double_Time_Hours, Time_And_A_Half_Hours);
747 Calc_Daily_Overtime(Tuesday, Double_Time_Hours, Time_And_A_Half_Hours);
748 Calc_Daily_Overtime(Wednesday, Double_Time_Hours, Time_And_A_Half_Hours);
749 Calc_Daily_Overtime(Thursday, Double_Time_Hours, Time_And_A_Half_Hours);
750 Calc_Daily_Overtime(Friday, Double_Time_Hours, Time_And_A_Half_Hours);
751 Double_Time_Hours := Double_Time_Hours + Saturday + Sunday;
752 -- Put other overtime rules below
753 -- elsif R_Rule_Set = 'Extra OT1'
754 -- ...
755 END IF;
756 END Calc_OT;
757
758
759 -- ======================================================================
760 --
761 -- PUBLIC PROCEDURE/FUNCTIONS (entry points)
762 --
763 -- ======================================================================
764
765 --
766 -- Process different types of overtime for each compensation rule
767 --
768 PROCEDURE Process_Overtime(
769 New_Expenditure_Created OUT NOCOPY boolean,
770 R_P_User_ID IN number,
771 R_P_Program_ID IN number,
772 R_P_Request_ID IN number,
773 R_P_Program_App_ID in number,
774 R_Person_Id in number,
775 R_Expenditure_End_Date IN date,
776 R_Overtime_Exp_Type IN varchar2,
777 R_C_Double_Time_Hours IN OUT NOCOPY number,
778 R_C_Time_And_A_Half_Hours IN OUT NOCOPY number,
779 R_C_Uncompensated_Hours IN OUT NOCOPY number,
780 R_C_Extra_OT_Hours_1 IN OUT NOCOPY number,
781 R_C_Extra_OT_Hours_2 IN OUT NOCOPY number,
782 R_Organization in number,
783 R_Rule_Set IN varchar2) IS
784 Total_Hours number;
785 Sunday number;
786 Monday number;
787 Tuesday number;
788 Wednesday number;
789 Thursday number;
790 Friday number;
791 Saturday number;
792 OT_PT_Exist boolean;
793 Existing_Double_Time_Hours number;
794 Existing_Half_Time_Hours number;
795 Existing_Uncomp_Time_Hours number;
796 Existing_Extra_OT_Hours_1 number;
797 Existing_Extra_OT_Hours_2 number;
798 Actual_Double_Time_Hours number;
799 Actual_Half_Time_Hours number;
800 Actual_Uncomp_Time_Hours number;
801 Actual_Extra_OT_Hours_1 number;
802 Actual_Extra_OT_Hours_2 number;
803 Expenditure_ID number;
804 BEGIN
805 -- Main_Body()
806 -- Reset for every person/period pair
807 Exp_Created_Flag := 'N';
808
809 -- Sel_Time_Totals()
810 SELECT SUM(ITEM2.Quantity),
811 SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
812 '1',ITEM2.Quantity,0)),
813 SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
814 '2',ITEM2.Quantity,0)),
815 SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
816 '3',ITEM2.Quantity,0)),
817 SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
818 '4',ITEM2.Quantity,0)),
819 SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
820 '5',ITEM2.Quantity,0)),
821 SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
822 '6',ITEM2.Quantity,0)),
823 SUM(DECODE(TO_CHAR(ITEM2.Expenditure_Item_Date,'D'),
824 '7',ITEM2.Quantity,0))
825 INTO Total_Hours, Sunday, Monday, Tuesday, Wednesday,
826 Thursday, Friday, Saturday
827 FROM PA_Expenditure_Items ITEM2,
828 PA_Expenditures EXP2
829 WHERE EXP2.Incurred_By_Person_ID = R_Person_ID
830 AND EXP2.Expenditure_Ending_Date = R_Expenditure_End_Date
831 AND EXP2.Expenditure_Status_Code||'' = 'APPROVED'
832 AND EXP2.Expenditure_ID = ITEM2.Expenditure_ID
833 AND ITEM2.Quantity <> 0
834 AND ITEM2.System_Linkage_Function||'' = 'ST';
835
836 -- Sel_Existing_Overtime() + Check_Zero_Existing_Overtime()
837 select nvl(sum(decode(ITEM.task_id,
838 OTaskID_Tab(1),ITEM.quantity,
839 0)),0),
840 nvl(sum(decode(ITEM.task_id,
841 OTaskID_Tab(2),ITEM.quantity,
842 0)),0),
843 nvl(sum(decode(ITEM.task_id,
844 OTaskID_Tab(3),ITEM.quantity,
845 0)),0),
846 nvl(sum(decode(ITEM.task_id,
847 OTaskID_Tab(4),ITEM.quantity,
848 0)),0),
849 nvl(sum(decode(ITEM.task_id,
850 OTaskID_Tab(5),ITEM.quantity,
851 0)),0)
852 into Existing_Double_Time_Hours,
853 Existing_Half_Time_Hours,
854 Existing_Uncomp_Time_Hours,
855 Existing_Extra_OT_Hours_1,
856 Existing_Extra_OT_Hours_2
857 FROM PA_expenditure_items ITEM
858 ,PA_expenditures EXP
859 WHERE EXP.Incurred_By_Person_Id = R_Person_Id
860 AND EXP.Expenditure_Ending_Date = R_Expenditure_End_Date
861 AND ITEM.Expenditure_Id = EXP.Expenditure_Id
862 AND ITEM.Expenditure_Item_Date = R_Expenditure_End_Date
863 AND ITEM.System_Linkage_Function ||'' = 'OT';
864
865 -- Calc_Overtime()
866 Calc_OT(Total_Hours, Sunday, Monday, Tuesday, Wednesday,
867 Thursday, Friday, Saturday, Actual_Double_Time_Hours,
868 Actual_Half_Time_Hours, Actual_Uncomp_Time_Hours,
869 Actual_Extra_OT_Hours_1, Actual_Extra_OT_Hours_2, R_Rule_Set);
870 R_C_Double_Time_Hours := Actual_Double_Time_Hours;
871 R_C_Time_And_A_Half_Hours := Actual_Half_Time_Hours;
872 R_C_Uncompensated_Hours := Actual_Uncomp_Time_Hours;
873 R_C_Extra_OT_Hours_1 := Actual_Extra_OT_Hours_1;
874 R_C_Extra_OT_Hours_2 := Actual_Extra_OT_Hours_2;
875
876 -- Added by Sandeep Bharathan. We noticed that
877 -- org_id was not populated while inserting exp items.
878
879 if pa_utils.pa_morg_implemented = 'Y' then
880 FND_PROFILE.GET ('ORG_ID', G_Org_Id);
881 end if;
882
883 -- End
884
885 -- Process_Overtime(): double + half + uncomp
886 -- process_double()
887 FOR i in 1 .. 5 LOOP
888 EXIT WHEN OTaskID_Tab(i) is NULL;
889 /* Bug# 1483807 */
890 IF OTaskName_Tab(i) = 'Uncompensated' THEN
891 Insert_Overtime_Items(
892 Existing_Uncomp_Time_Hours,
893 Actual_Uncomp_Time_Hours,
894 OTaskID_Tab(i),
895 OTaskLCM_Tab(i),
896 Expenditure_ID,
897 R_P_User_ID,
898 R_P_Program_ID,
899 R_P_Request_ID,
900 R_P_Program_App_ID,
901 R_Person_Id,
902 R_Expenditure_End_Date,
903 R_Overtime_Exp_Type,
904 R_Organization);
905
906 ELSIF OTaskName_Tab(i) = 'Time and Half' THEN
907 Insert_Overtime_Items(
908 Existing_Half_Time_Hours,
909 Actual_Half_Time_Hours,
910 OTaskID_Tab(i),
911 OTaskLCM_Tab(i),
912 Expenditure_ID,
913 R_P_User_ID,
914 R_P_Program_ID,
915 R_P_Request_ID,
916 R_P_Program_App_ID,
917 R_Person_Id,
918 R_Expenditure_End_Date,
919 R_Overtime_Exp_Type,
920 R_Organization);
921
922 ELSIF OTaskName_Tab(i) = 'Double Time' THEN
923 Insert_Overtime_Items(
924 Existing_Double_Time_Hours,
925 Actual_Double_time_Hours,
926 OTaskID_Tab(i),
927 OTaskLCM_Tab(i),
928 Expenditure_ID,
929 R_P_User_ID,
930 R_P_Program_ID,
931 R_P_Request_ID,
932 R_P_Program_App_ID,
933 R_Person_Id,
934 R_Expenditure_End_Date,
935 R_Overtime_Exp_Type,
936 R_Organization);
937 END IF;
938 END LOOP;
939
940 New_Expenditure_Created := Exp_Created_Flag = 'Y';
941
942 END Process_Overtime;
943
944 --
945 -- Fetch all overtime task ids.
946 -- Called from BEFOREREPORT trigger so that if no 'OT' project or
947 -- Double, Half, and Uncomp tasks exist, report will stop.
948 --
949 PROCEDURE Check_Overtime_Tasks_Exist(
950 Overtime_Tasks_Exist OUT NOCOPY boolean,
951 R_ot_title_1 OUT NOCOPY varchar2,
952 R_ot_title_2 OUT NOCOPY varchar2,
953 R_ot_title_3 OUT NOCOPY varchar2,
954 R_ot_title_4 OUT NOCOPY varchar2,
955 R_ot_title_5 OUT NOCOPY varchar2) IS
956 x_count number := 0;
957 BEGIN
958 /*
959 Multi-currency related changes:
960 Get project currency code
961 */
962 FOR c IN (
963 SELECT t.task_id overtime_task_id
964 , t.labor_cost_multiplier_name overtime_LCM
965 , t.task_name overtime_task_name
966 , p.project_currency_code proj_curr_code
967 , p.projfunc_currency_code projfunc_currency_code
968 FROM pa_tasks t
969 , pa_projects p
970 WHERE t.project_id = p.project_id
971 AND p.segment1 = 'OT') LOOP
972 EXIT WHEN x_count >= 5;
973 x_count := x_count+1;
974 OTaskID_Tab(x_count) := c.overtime_task_id;
975 OTaskLCM_Tab(x_count) := c.overtime_LCM;
976 OTaskName_Tab(x_count) := c.overtime_task_name;
977 OProjCurrCode := c.proj_curr_code;
978 OProjfuncCurrCode := c.projfunc_currency_code;
979 END LOOP;
980
981 FOR d IN x_count+1 .. 5 LOOP
982 OTaskID_Tab(d) := NULL;
983 OTaskLCM_Tab(d) := NULL;
984 OTaskName_Tab(d) := NULL;
985 END LOOP;
986
987 /*
988 Multi-currency related changes:
989 Get Txn/Functional currency code
990 */
991 OCurrCode := pa_multi_currency.get_acct_currency_code;
992
993 R_ot_title_1 := OTaskName_Tab(1);
994 R_ot_title_2 := OTaskName_Tab(2);
995 R_ot_title_3 := OTaskName_Tab(3);
996 R_ot_title_4 := OTaskName_Tab(4);
997 R_ot_title_5 := OTaskName_Tab(5);
998
999 Overtime_Tasks_Exist := x_count > 0;
1000 EXCEPTION
1001 WHEN others THEN
1002 Overtime_Tasks_Exist := FALSE;
1003 END Check_Overtime_Tasks_Exist;
1004
1005 --
1006 -- Create status record so labor distribution knows Report finished
1007 --
1008 PROCEDURE Create_Status_Record(
1009 R_P_User_ID IN number,
1010 R_P_Request_ID IN number,
1011 R_P_Program_ID IN number,
1012 R_P_Program_App_ID IN number) IS
1013 BEGIN
1014 INSERT INTO PA_Spawned_Program_Statuses
1015 (
1016 Last_Update_Date,
1017 Last_Updated_By,
1018 Creation_Date,
1019 Created_By,
1020 Request_ID,
1021 Program_ID,
1022 Program_Application_ID,
1023 Program_Update_Date
1024 )
1025 VALUES
1026 (
1027 SYSDATE,
1028 R_P_User_ID,
1029 SYSDATE,
1030 R_P_User_ID,
1031 R_P_Request_ID,
1032 R_P_Program_ID,
1033 R_P_Program_App_ID,
1034 SYSDATE
1035 );
1036 END Create_Status_Record;
1037
1038 END PA_CALC_OVERTIME;