[Home] [Help]
1: PACKAGE BODY HXT_TIME_PAY AS
2: /* $Header: hxttpay.pkb 120.8.12010000.4 2010/02/17 12:11:27 asrajago ship $ */
3: g_debug boolean := hr_utility.debug_enabled;
4:
5: -- Bug 8855103
100: END IF;
101:
102: g_debug :=hr_utility.debug_enabled;
103: if g_debug then
104: hr_utility.set_location('hxt_time_pay.get_retro_fields',10);
105: end if;
106:
107: -- Bug 7359347
108: -- Added session date parameter.
114: end if;
115:
116: IF retro_pay_trans%NOTFOUND THEN
117: if g_debug then
118: hr_utility.set_location('hxt_time_pay.get_retro_fields',20);
119: end if;
120: OPEN complete_pay_trans;
121: FETCH complete_pay_trans INTO p_pay_status;
122: if g_debug then
124: end if;
125:
126: IF complete_pay_trans%NOTFOUND THEN
127: if g_debug then
128: hr_utility.set_location('hxt_time_pay.get_retro_fields',30);
129: end if;
130: p_pay_status := 'P';
131: p_retro_batch_id := NULL;
132: -- Bug 9367730
132: -- Bug 9367730
133: l_no_pay_trans := TRUE;
134: ELSE
135: if g_debug then
136: hr_utility.set_location('hxt_time_pay.get_retro_fields',40);
137: end if;
138: p_retro_batch_id := hxt_UTIL.Get_Retro_Batch_Id(p_tim_id
139: ,p_batch_name
140: ,p_batch_ref);
142: hr_utility.trace('p_retro_batch_id :'||p_retro_batch_id);
143: end if;
144: END IF;
145: if g_debug then
146: hr_utility.set_location('hxt_time_pay.get_retro_fields',50);
147: end if;
148: CLOSE complete_pay_trans;
149: END IF;
150: if g_debug then
147: end if;
148: CLOSE complete_pay_trans;
149: END IF;
150: if g_debug then
151: hr_utility.set_location('hxt_time_pay.get_retro_fields',60);
152: end if;
153: CLOSE retro_pay_trans;
154:
155: OPEN retro_pa_trans;
159: end if;
160:
161: IF retro_pa_trans%NOTFOUND THEN
162: if g_debug then
163: hr_utility.set_location('hxt_time_pay.get_retro_fields',70);
164: end if;
165: OPEN complete_pa_trans;
166: FETCH complete_pa_trans INTO p_pa_status;
167: if g_debug then
169: end if;
170:
171: IF complete_pa_trans%NOTFOUND THEN
172: if g_debug then
173: hr_utility.set_location('hxt_time_pay.get_retro_fields',80);
174: end if;
175: p_pa_status := 'P';
176: END IF;
177: if g_debug then
174: end if;
175: p_pa_status := 'P';
176: END IF;
177: if g_debug then
178: hr_utility.set_location('hxt_time_pay.get_retro_fields',90);
179: end if;
180: CLOSE complete_pa_trans;
181: END IF;
182: if g_debug then
179: end if;
180: CLOSE complete_pa_trans;
181: END IF;
182: if g_debug then
183: hr_utility.set_location('hxt_time_pay.get_retro_fields',100);
184: end if;
185: CLOSE retro_pa_trans;
186:
187:
199: end if;
200:
201: IF check_for_details%FOUND THEN
202: if g_debug then
203: hr_utility.set_location('hxt_time_pay.get_retro_fields',110);
204: end if;
205: p_retro_batch_id := HXT_UTIL.Get_Retro_Batch_Id(p_tim_id
206: ,p_batch_name
207: ,p_batch_ref);
209: hr_utility.trace('p_retro_batch_id :'||p_retro_batch_id);
210: end if;
211: END IF ;
212: if g_debug then
213: hr_utility.set_location('hxt_time_pay.get_retro_fields',120);
214: end if;
215: close check_for_details ;
216:
217: END IF;
222:
223: EXCEPTION
224: WHEN OTHERS THEN
225: if g_debug then
226: hr_utility.set_location('hxt_time_pay.get_retro_fields',130);
227: end if;
228: p_error_status := 2;
229: p_sqlerrm := sqlerrm;
230:
301:
302: BEGIN
303:
304: if g_debug then
305: hr_utility.set_location('hxt_time_pay.call_gen_error',10);
306: end if;
307: hxt_util.gen_error(g_tim_id,
308: g_id,
309: NULL,
313: g_EFFECTIVE_START_DATE,
314: g_EFFECTIVE_END_DATE,
315: 'ERR');
316: if g_debug then
317: hr_utility.set_location('hxt_time_pay.call_gen_error',20);
318: end if;
319: RETURN 2;
320: END;
321:
330: -- calls error processing procedure --
331: BEGIN
332:
333: if g_debug then
334: hr_utility.set_location('hxt_time_pay.call_gen_error',10);
335: end if;
336: hxt_util.gen_error(g_tim_id,
337: g_id,
338: NULL,
356: p_time_attribute_ovn=> NULL
357: );
358: if g_debug then
359: hr_utility.trace('Adding to g_otm_messages'||p_msg_name);
360: hr_utility.set_location('hxt_time_pay.call_gen_error',20);
361: end if;
362: RETURN 2;
363: END;
364:
439: BEGIN
440:
441: if g_debug then
442:
443: hr_utility.set_location('hxt_time_pay.INSERT_HRS',10);
444: end if;
445:
446: --Set return code and get ID.
447: p_return_code := 0;
466: ,l_sqlerrm);
467:
468: IF l_error_status = 0 THEN
469: if g_debug then
470: hr_utility.set_location('hxt_time_pay.INSERT_HRS',20);
471: end if;
472:
473: BEGIN
474: -- ***********************************
475: -- ORACLE START Bugs #465434 & #464850
476: -- ***********************************
477:
478: if g_debug then
479: hr_utility.set_location('hxt_time_pay.INSERT_HRS',30);
480: end if;
481:
482: OPEN get_ovt_rates_cur ;
483: FETCH get_ovt_rates_cur
500: -- Determine if a premium amount should apply.
501: --
502: IF premium_type = 'FACTOR' THEN
503: if g_debug then
504: hr_utility.set_location('hxt_time_pay.INSERT_HRS',40);
505: end if;
506: l_rate_multiple := premium_amount ;
507: ELSIF premium_type = 'RATE' THEN
508: if g_debug then
505: end if;
506: l_rate_multiple := premium_amount ;
507: ELSIF premium_type = 'RATE' THEN
508: if g_debug then
509: hr_utility.set_location('hxt_time_pay.INSERT_HRS',50);
510: end if;
511: l_hourly_rate := premium_amount ;
512: ELSIF premium_type = 'FIXED' THEN
513: if g_debug then
510: end if;
511: l_hourly_rate := premium_amount ;
512: ELSIF premium_type = 'FIXED' THEN
513: if g_debug then
514: hr_utility.set_location('hxt_time_pay.INSERT_HRS',60);
515: end if;
516: l_amount := premium_amount ;
517: l_hours := 0 ; -- SIR092 Hours have no meaning with
518: -- flat amount premiums
517: l_hours := 0 ; -- SIR092 Hours have no meaning with
518: -- flat amount premiums
519: ELSE
520: if g_debug then
521: hr_utility.set_location('hxt_time_pay.INSERT_HRS',70);
522: end if;
523: NULL ;
524: END IF ;
525: --
526: -- Any values passed in from globals will override retrieved values.
527: --
528: IF g_rate_multiple IS NOT NULL THEN
529: if g_debug then
530: hr_utility.set_location('hxt_time_pay.INSERT_HRS',80);
531: end if;
532: l_rate_multiple := g_rate_multiple ;
533: if g_debug then
534: hr_utility.trace('l_rate_multiple :'||l_rate_multiple);
536: END IF ;
537: --
538: IF g_hourly_rate IS NOT NULL THEN
539: if g_debug then
540: hr_utility.set_location('hxt_time_pay.INSERT_HRS',90);
541: end if;
542: l_hourly_rate := g_hourly_rate ;
543: END IF ;
544: --
543: END IF ;
544: --
545: IF g_amount IS NOT NULL THEN
546: if g_debug then
547: hr_utility.set_location('hxt_time_pay.INSERT_HRS',100);
548: end if;
549: l_amount := g_amount ;
550: if g_debug then
551: hr_utility.trace('l_amount :'||l_amount);
556: -- ORACLE END Bugs #465434 & #464850
557: -- *********************************
558: --
559: if g_debug then
560: hr_utility.set_location('hxt_time_pay.INSERT_HRS',110);
561: end if;
562: l_costable_type := HXT_UTIL.get_costable_type(p_element_type_id,
563: g_date_worked,
564: g_assignment_id);
567: end if;
568:
569: IF l_costable_type in ('C','F') THEN
570: if g_debug then
571: hr_utility.set_location('hxt_time_pay.INSERT_HRS',120);
572: end if;
573: l_ffv_cost_center_id := g_FFV_COST_CENTER_ID;
574: if g_debug then
575: hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
575: hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
576: end if;
577: ELSE
578: if g_debug then
579: hr_utility.set_location('hxt_time_pay.INSERT_HRS',130);
580: end if;
581: l_ffv_cost_center_id := NULL;
582: if g_debug then
583: hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
657:
658:
659: /* Call dml to insert hours into hxt_det_hours_worked_f */
660: if g_debug then
661: hr_utility.set_location('hxt_time_pay.INSERT_HRS',140);
662: end if;
663:
664: hxt_dml.insert_HXT_DET_HOURS_WORKED(
665: p_rowid => l_rowid,
708: p_CITY_NAME => g_CITY_NAME,
709: p_ZIP_CODE => g_ZIP_CODE);
710:
711: if g_debug then
712: hr_utility.set_location('hxt_time_pay.INSERT_HRS',150);
713: end if;
714:
715: EXCEPTION
716:
715: EXCEPTION
716:
717: WHEN OTHERS THEN
718: if g_debug then
719: hr_utility.set_location('hxt_time_pay.INSERT_HRS',160);
720: end if;
721: -- Insert record in error table.
722: FND_MESSAGE.SET_NAME('HXT','HXT_39313_OR_ERR_INS_REC');
723: p_return_code := call_hxthxc_gen_error('HXT','HXT_39313_OR_ERR_INS_REC',NULL,p_location||':INS', '',sqlerrm);
728: END;
729:
730: ELSE
731: if g_debug then
732: hr_utility.set_location('hxt_time_pay.INSERT_HRS',170);
733: end if;
734: FND_MESSAGE.SET_NAME('HXT','HXT_39421_GET_RETRO_ERR');
735: p_return_code := call_hxthxc_gen_error('HXT','HXT_39421_GET_RETRO_ERR',NULL,p_location||':INS', '',sqlerrm);
736: --2278400 p_return_code := call_gen_error(p_location||':INS', '',sqlerrm);
1123:
1124: BEGIN
1125:
1126: if g_debug then
1127: hr_utility.set_location('hxt_time_pay.Gen_Premiums',10);
1128: end if;
1129:
1130:
1131: -- Bug 7359347
1140: --Increment line seqno
1141: l_seqno := p_seqno + 10;
1142: if g_debug then
1143: hr_utility.trace('l_seqno :'||l_seqno);
1144: hr_utility.set_location('hxt_time_pay.Gen_Premiums',20);
1145: end if;
1146:
1147: open get_min_detail_seqno;
1148: fetch get_min_detail_seqno into l_min_detail_seqno;
1151: end if;
1152: close get_min_detail_seqno;
1153:
1154: if g_debug then
1155: hr_utility.set_location('hxt_time_pay.Gen_Premiums',30);
1156: end if;
1157:
1158: open get_max_detail_seqno;
1159: fetch get_max_detail_seqno into l_max_detail_seqno;
1165: --Loop through premiums person is eligible for
1166: --in order of priority
1167:
1168: if g_debug then
1169: hr_utility.set_location('hxt_time_pay.Gen_Premiums',40);
1170: end if;
1171:
1172: <
1173:
1171:
1172: <
1173:
1174: if g_debug then
1175: hr_utility.set_location('hxt_time_pay.Gen_Premiums',50);
1176: hr_utility.trace('g_ASSIGNMENT_ID :'||g_ASSIGNMENT_ID);
1177: hr_utility.trace('g_DATE_WORKED :'||g_DATE_WORKED);
1178: hr_utility.trace('g_PEP_ID :'||g_PEP_ID);
1179: hr_utility.trace('p_element_type_id:'||p_element_type_id);
1187: FOR elig_prem_rec IN cur_elig_prem
1188: LOOP
1189:
1190: if g_debug then
1191: hr_utility.set_location('hxt_time_pay.Gen_Premiums',60);
1192: end if;
1193: /*
1194: -- Check if the hxt_earning_category is Shift Differential Override.
1195: -- If yes,then check whether the employee has worked other than their
1197: -- worked other than their regular shifts.
1198:
1199: IF elig_prem_rec.hxt_earning_category <> 'SDF' THEN
1200: if g_debug then
1201: hr_utility.set_location('hxt_time_pay.Gen_Premiums',61);
1202: end if;
1203: lv_elig_for_prem := TRUE;
1204: END IF;
1205:
1204: END IF;
1205:
1206: IF elig_prem_rec.hxt_earning_category = 'SDF' THEN
1207: if g_debug then
1208: hr_utility.set_location('hxt_time_pay.Gen_Premiums',62);
1209: end if;
1210:
1211: open Get_Shift_Info( g_ASSIGNMENT_ID,g_DATE_WORKED );
1212: fetch Get_Shift_Info into lv_rotation_plan ,lv_tws_id ,lv_week_day
1212: fetch Get_Shift_Info into lv_rotation_plan ,lv_tws_id ,lv_week_day
1213: ,lv_standard_start ,lv_standard_stop;
1214:
1215: if g_debug then
1216: hr_utility.set_location('hxt_time_pay.Gen_Premiums',63);
1217: end if;
1218: close Get_Shift_Info;
1219:
1220: if g_debug then
1222: hr_utility.trace('lv_tws_id :'||lv_tws_id);
1223: hr_utility.trace('lv_week_day :'||lv_week_day);
1224: hr_utility.trace('lv_standard_start:'||lv_standard_start);
1225: hr_utility.trace('lv_standard_stop :'||lv_standard_stop);
1226: hr_utility.set_location('hxt_time_pay.Gen_Premiums',64);
1227:
1228: hr_utility.trace('p_time_in :'||to_char(p_time_in,'HH24MI'));
1229: hr_utility.trace('p_time_out :'||to_char(p_time_out,'HH24MI'));
1230:
1234: IF lv_standard_start = 0 AND lv_standard_stop = 0
1235: AND g_osp_id is NOT NULL THEN -- for off shift days
1236:
1237: if g_debug then
1238: hr_utility.set_location('hxt_time_pay.Gen_Premiums',64.1);
1239: end if;
1240: lv_elig_for_prem := TRUE; -- Thus for an off day shift the
1241: -- shift prem gets paid too
1242: -- along with the off shift premium
1256:
1257: -- i.e., If shift differential policy is applicable to time_in and
1258: -- time_outs
1259: if g_debug then
1260: hr_utility.set_location('hxt_time_pay.Gen_Premiums',64.2);
1261: end if;
1262: lv_elig_for_prem := TRUE;
1263:
1264: ELSIF p_sdf_id = g_sdovr_id THEN
1268: -- gen_details ,so that the cursor cur_elig_prem returns a row and the
1269: -- logic gets called to insert the data into hxt_det_hours_worked
1270:
1271: if g_debug then
1272: hr_utility.set_location('hxt_time_pay.Gen_Premiums',64.3);
1273: end if;
1274: lv_elig_for_prem := TRUE;
1275: END IF;
1276:
1274: lv_elig_for_prem := TRUE;
1275: END IF;
1276:
1277: if g_debug then
1278: hr_utility.set_location('hxt_time_pay.Gen_Premiums',67);
1279: end if;
1280: END IF;
1281:
1282: IF lv_elig_for_prem = TRUE THEN
1281:
1282: IF lv_elig_for_prem = TRUE THEN
1283:
1284: if g_debug then
1285: hr_utility.set_location('hxt_time_pay.Gen_Premiums',68);
1286: end if;
1287: */
1288: -- Default override multiple to override multiple in summary hour
1289: -- record (usually null)
1299: IF elig_prem_rec.hxt_premium_type = 'FIXED' and p_hours <> 0
1300: THEN
1301: IF g_debug
1302: THEN
1303: hr_utility.set_location ('hxt_time_pay.Gen_Premiums', 70);
1304: END IF;
1305:
1306: l_hours := 0;
1307: l_time_in := NULL;
1318: return 0;
1319: ELSE
1320: IF g_debug
1321: THEN
1322: hr_utility.set_location ('hxt_time_pay.Gen_Premiums', 80);
1323: END IF;
1324:
1325: l_hours := p_hours;
1326: l_time_in := p_time_in;
1344:
1345: -- Check if override multiple is applicable
1346: IF elig_prem_rec.hxt_premium_type = 'FACTOR' THEN
1347: if g_debug then
1348: hr_utility.set_location('hxt_time_pay.Gen_Premiums',90);
1349: end if;
1350:
1351: -- Step through the premium detail records of type factor that
1352: -- were already inserted into the hours worked table - all are
1357:
1358: <
1359:
1360: if g_debug then
1361: hr_utility.set_location('hxt_time_pay.Gen_Premiums',100);
1362: end if;
1363:
1364: FOR prem_intr_rec IN cur_prem_intr(elig_prem_rec.elt_premium_id
1365: ,elig_prem_rec.hxt_processing_order)
1364: FOR prem_intr_rec IN cur_prem_intr(elig_prem_rec.elt_premium_id
1365: ,elig_prem_rec.hxt_processing_order)
1366: LOOP
1367: if g_debug then
1368: hr_utility.set_location('hxt_time_pay.Gen_Premiums',110);
1369: end if;
1370:
1371: IF prem_intr_rec.hxt_premium_type = 'FACTOR' THEN --SIR190
1372: if g_debug then
1369: end if;
1370:
1371: IF prem_intr_rec.hxt_premium_type = 'FACTOR' THEN --SIR190
1372: if g_debug then
1373: hr_utility.set_location('hxt_time_pay.Gen_Premiums',120);
1374: end if;
1375:
1376: -- SIR103 We must strip off the 1 from overtime premium
1377: -- so that it will be treated like any other premium in the
1378: -- interaction calculations. PWM
1379:
1380: IF elig_prem_rec.hxt_earning_category = 'OVT' THEN --SIR103 ORA131
1381: if g_debug then
1382: hr_utility.set_location('hxt_time_pay.Gen_Premiums',130);
1383: end if;
1384: l_mult := nvl(g_RATE_MULTIPLE
1385: ,elig_prem_rec.hxt_premium_amount) -1 ; --SIR103
1386: if g_debug then
1387: hr_utility.trace('l_mult :'||l_mult);
1388: end if;
1389: ELSE --SIR103
1390: if g_debug then
1391: hr_utility.set_location('hxt_time_pay.Gen_Premiums',140);
1392: end if;
1393: l_mult := nvl(g_RATE_MULTIPLE
1394: ,elig_prem_rec.hxt_premium_amount) ; --SIR103
1395: if g_debug then
1402: -- the interaction calculations. PWM
1403:
1404: IF prem_intr_rec.hxt_earning_category = 'OVT' THEN --SIR103
1405: if g_debug then
1406: hr_utility.set_location('hxt_time_pay.Gen_Premiums',150);
1407: end if;
1408: prem_intr_rec.multiple := prem_intr_rec.multiple -1 ;--SIR103
1409: END IF; --SIR103
1410: --
1416: -- the multiple by applying the factors of the premium earned
1417: -- previously.
1418:
1419: if g_debug then
1420: hr_utility.set_location('hxt_time_pay.Gen_Premiums',160);
1421: end if;
1422: l_mult := nvl(prem_intr_rec.multiple,0) + l_mult * nvl(prem_intr_rec.multiple,1); --SIR103
1423: if g_debug then
1424: hr_utility.trace('l_mult :'||l_mult);
1428: -- the calculation. PWM
1429:
1430: IF prem_intr_rec.hxt_earning_category = 'OVT' THEN
1431: if g_debug then
1432: hr_utility.set_location('hxt_time_pay.Gen_Premiums',170);
1433: end if;
1434: l_mult := l_mult + 1 ;
1435: if g_debug then
1436: hr_utility.trace('l_mult :'||l_mult);
1438: END IF;
1439:
1440: ELSE
1441: if g_debug then
1442: hr_utility.set_location('hxt_time_pay.Gen_Premiums',180);
1443: end if;
1444: -- Premium was RATE or FIXED, but not FACTOR so don't
1445: -- change the multiple
1446:
1445: -- change the multiple
1446:
1447: IF elig_prem_rec.hxt_earning_category = 'OVT' THEN
1448: if g_debug then
1449: hr_utility.set_location('hxt_time_pay.Gen_Premiums',190);
1450: end if;
1451: l_mult := nvl(g_RATE_MULTIPLE
1452: ,elig_prem_rec.hxt_premium_amount) -1 ;
1453: if g_debug then
1454: hr_utility.trace('l_mult :'||l_mult);
1455: end if;
1456: ELSE
1457: if g_debug then
1458: hr_utility.set_location('hxt_time_pay.Gen_Premiums',200);
1459: end if;
1460: l_mult := nvl(g_RATE_MULTIPLE
1461: ,elig_prem_rec.hxt_premium_amount);
1462: if g_debug then
1464: end if;
1465: END IF;
1466:
1467: if g_debug then
1468: hr_utility.set_location('hxt_time_pay.Gen_Premiums',210);
1469: end if;
1470: l_rate := prem_intr_rec.multiple + (l_mult * prem_intr_rec.multiple);
1471: l_mult := NULL;
1472: if g_debug then
1475: end if;
1476: END IF;
1477:
1478: if g_debug then
1479: hr_utility.set_location('hxt_time_pay.Gen_Premiums',220);
1480: end if;
1481: hrw_rowid := prem_intr_rec.hrwrowid;
1482: if g_debug then
1483: hr_utility.trace('hrw_rowid :'||hrw_rowid);
1484: end if;
1485: END LOOP prem_intr;
1486:
1487: if g_debug then
1488: hr_utility.set_location('hxt_time_pay.Gen_Premiums',240);
1489: end if;
1490: END IF; -- Factor premium
1491:
1492: --
1492: --
1493: -- ORA131 MOD START(all code between start and end mark was added forORA131)
1494: --
1495: if g_debug then
1496: hr_utility.set_location('hxt_time_pay.Gen_Premiums',250);
1497: end if;
1498: --
1499: -- Check if override rate is applicable
1500: --
1499: -- Check if override rate is applicable
1500: --
1501: IF elig_prem_rec.hxt_premium_type = 'RATE' THEN
1502: if g_debug then
1503: hr_utility.set_location('hxt_time_pay.Gen_Premiums',260);
1504: end if;
1505: -- Step through the premium detail records of type rate that
1506: -- were already inserted into the hours worked table - all are
1507: -- lower priority than the current premium. By matching up against
1511:
1512: <
1513:
1514: if g_debug then
1515: hr_utility.set_location('hxt_time_pay.Gen_Premiums',270);
1516: end if;
1517:
1518: FOR prem_intr_rec IN cur_prem_intr(elig_prem_rec.elt_premium_id
1519: ,elig_prem_rec.hxt_processing_order)
1518: FOR prem_intr_rec IN cur_prem_intr(elig_prem_rec.elt_premium_id
1519: ,elig_prem_rec.hxt_processing_order)
1520: LOOP
1521: if g_debug then
1522: hr_utility.set_location('hxt_time_pay.Gen_Premiums',280);
1523: end if;
1524:
1525: -- SIR190 No calculations are needed for Rate premiums
1526: -- since these cannot interact with another premium,
1531: l_rate := nvl(g_HOURLY_RATE
1532: ,elig_prem_rec.hxt_premium_amount); --SIR190
1533: if g_debug then
1534: hr_utility.trace('l_rate :'||l_rate);
1535: hr_utility.set_location('hxt_time_pay.Gen_Premiums',290);
1536: end if;
1537: -- The new rate is derived by multiplying the premium amount
1538: -- by the multiple of the interactive premium (which is most
1539: -- likely overtime)
1547:
1548: hrw_rowid := prem_intr_rec.hrwrowid;
1549: if g_debug then
1550: hr_utility.trace('hrw_rowid :'||hrw_rowid);
1551: hr_utility.set_location('hxt_time_pay.Gen_Premiums',300);
1552: end if;
1553: END LOOP prem_intr;
1554:
1555: if g_debug then
1552: end if;
1553: END LOOP prem_intr;
1554:
1555: if g_debug then
1556: hr_utility.set_location('hxt_time_pay.Gen_Premiums',320);
1557: end if;
1558: END IF; -- Rate premium
1559:
1560: -- Check if OVT premium and override multiple was derived
1559:
1560: -- Check if OVT premium and override multiple was derived
1561: IF (l_mult is not null) THEN
1562: if g_debug then
1563: hr_utility.set_location('hxt_time_pay.Gen_Premiums',330);
1564: end if;
1565: -- Update existing base-hour detail record
1566:
1567: BEGIN
1566:
1567: BEGIN
1568:
1569: if g_debug then
1570: hr_utility.set_location('hxt_time_pay.Gen_Premiums',340);
1571: end if;
1572: UPDATE hxt_det_hours_worked_f hrw
1573: SET rate_multiple = l_mult
1574: WHERE hrw.rowid = hrw_rowid;
1574: WHERE hrw.rowid = hrw_rowid;
1575: EXCEPTION
1576: WHEN OTHERS THEN
1577: if g_debug then
1578: hr_utility.set_location('hxt_time_pay.Gen_Premiums',350);
1579: end if;
1580: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1581: l_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location,'', sqlerrm);
1582: --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm);
1584: hr_utility.trace('l_error_code :'||l_error_code);
1585: end if;
1586: END;
1587: if g_debug then
1588: hr_utility.set_location('hxt_time_pay.Gen_Premiums',360);
1589: end if;
1590: END IF ;
1591: --
1592: -- ORA131 MOD START (all code between start and end mark was added for ORA131)
1592: -- ORA131 MOD START (all code between start and end mark was added for ORA131)
1593: --
1594: IF (l_rate is not null) THEN
1595: if g_debug then
1596: hr_utility.set_location('hxt_time_pay.Gen_Premiums',370);
1597: end if;
1598:
1599: -- Update existing base-hour detail record
1600: BEGIN
1599: -- Update existing base-hour detail record
1600: BEGIN
1601:
1602: if g_debug then
1603: hr_utility.set_location('hxt_time_pay.Gen_Premiums',380);
1604: end if;
1605: UPDATE hxt_det_hours_worked_f hrw
1606: SET hourly_rate = l_rate
1607: WHERE hrw.rowid = hrw_rowid;
1607: WHERE hrw.rowid = hrw_rowid;
1608: EXCEPTION
1609: WHEN OTHERS THEN
1610: if g_debug then
1611: hr_utility.set_location('hxt_time_pay.Gen_Premiums',390);
1612: end if;
1613: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1614: l_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location,'', sqlerrm);
1615: --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm);
1618: end if;
1619: END;
1620:
1621: if g_debug then
1622: hr_utility.set_location('hxt_time_pay.Gen_Premiums',400);
1623: end if;
1624: END IF ;
1625:
1626: IF elig_prem_rec.hxt_earning_category <> 'OVT' THEN
1625:
1626: IF elig_prem_rec.hxt_earning_category <> 'OVT' THEN
1627:
1628: if g_debug then
1629: hr_utility.set_location('hxt_time_pay.Gen_Premiums',410);
1630: end if;
1631: get_retro_fields( g_tim_id
1632: ,HXT_TIME_COLLECTION.g_batch_name
1633: ,HXT_TIME_COLLECTION.g_batch_ref
1639:
1640: IF l_error_status = 0 THEN
1641:
1642: if g_debug then
1643: hr_utility.set_location('hxt_time_pay.Gen_Premiums',420);
1644: end if;
1645: l_costable_type := HXT_UTIL.get_costable_type(
1646: elig_prem_rec.elt_premium_id
1647: ,g_DATE_WORKED
1651: end if;
1652:
1653: IF l_costable_type in ('C','F') THEN
1654: if g_debug then
1655: hr_utility.set_location('hxt_time_pay.Gen_Premiums',430);
1656: end if;
1657: l_ffv_cost_center_id := g_FFV_COST_CENTER_ID;
1658: if g_debug then
1659: hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
1659: hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
1660: end if;
1661: ELSE
1662: if g_debug then
1663: hr_utility.set_location('hxt_time_pay.Gen_Premiums',440);
1664: end if;
1665: l_ffv_cost_center_id := NULL;
1666: if g_debug then
1667: hr_utility.trace('l_ffv_cost_center_id :'||l_ffv_cost_center_id);
1673: --
1674: BEGIN
1675:
1676: if g_debug then
1677: hr_utility.set_location('hxt_time_pay.Gen_Premiums',450);
1678: end if;
1679:
1680:
1681: /* INSERT into hxt_det_hours_worked_f
1752: hr_utility.trace('l_id :'||l_id);
1753: hr_utility.trace('l_rate_multiple :'||l_rate_multiple);
1754: hr_utility.trace('l_hourly_rate :'||l_hourly_rate);
1755: hr_utility.trace('l_amount :'||l_amount);
1756: hr_utility.set_location('hxt_time_pay.Gen_Premiums',460);
1757: end if;
1758: hxt_dml.insert_HXT_DET_HOURS_WORKED(
1759: p_rowid => l_rowid,
1760: p_id => l_id,
1802: p_CITY_NAME => g_CITY_NAME,
1803: p_ZIP_CODE => g_ZIP_CODE);
1804:
1805: if g_debug then
1806: hr_utility.set_location('hxt_time_pay.Gen_Premiums',470);
1807: end if;
1808:
1809:
1810: EXCEPTION
1809:
1810: EXCEPTION
1811: WHEN OTHERS THEN
1812: if g_debug then
1813: hr_utility.set_location('hxt_time_pay.Gen_Premiums',480);
1814: end if;
1815: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1816: l_error_code := call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location,'', sqlerrm);
1817: --2278400 l_error_code := call_gen_error(l_location,'', sqlerrm);
1820: end if;
1821: END;
1822: ELSE
1823: if g_debug then
1824: hr_utility.set_location('hxt_time_pay.Gen_Premiums',490);
1825: end if;
1826: FND_MESSAGE.SET_NAME('HXT','HXT_39421_GET_RETRO_ERR');
1827: l_error_code := call_hxthxc_gen_error('HXT','HXT_39421_GET_RETRO_ERR',NULL,p_location||':INS', '',sqlerrm);
1828: --2278400 l_error_code := call_gen_error(p_location||':INS', '',sqlerrm);
1831: end if;
1832: END IF;
1833:
1834: if g_debug then
1835: hr_utility.set_location('hxt_time_pay.Gen_Premiums',500);
1836: end if;
1837: -- Increment line sequence number
1838: l_seqno := l_seqno + 10;
1839: if g_debug then
1841: end if;
1842: END IF;
1843:
1844: if g_debug then
1845: hr_utility.set_location('hxt_time_pay.Gen_Premiums',505);
1846: end if;
1847: -- END IF; -- END IF lv_elig_for_prem := TRUE
1848: if g_debug then
1849: hr_utility.set_location('hxt_time_pay.Gen_Premiums',510);
1845: hr_utility.set_location('hxt_time_pay.Gen_Premiums',505);
1846: end if;
1847: -- END IF; -- END IF lv_elig_for_prem := TRUE
1848: if g_debug then
1849: hr_utility.set_location('hxt_time_pay.Gen_Premiums',510);
1850: end if;
1851: END LOOP elig_prem;
1852:
1853: if g_debug then
1850: end if;
1851: END LOOP elig_prem;
1852:
1853: if g_debug then
1854: hr_utility.set_location('hxt_time_pay.Gen_Premiums',520);
1855: end if;
1856: RETURN l_error_code;
1857:
1858: EXCEPTION
1857:
1858: EXCEPTION
1859: WHEN OTHERS THEN
1860: if g_debug then
1861: hr_utility.set_location('hxt_time_pay.Gen_Premiums',530);
1862: end if;
1863: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
1864: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,l_location,'', sqlerrm);
1865: --2278400 RETURN call_gen_error(l_location,'', sqlerrm);
1902:
1903: BEGIN
1904:
1905: if g_debug then
1906: hr_utility.set_location('hxt_time_pay.local_pay',10);
1907: end if;
1908:
1909:
1910: -- Bug 7359347
1918: OPEN next_seq_cur;
1919: FETCH next_seq_cur INTO next_seq;
1920: if g_debug then
1921: hr_utility.trace('next_seq :'||next_seq);
1922: hr_utility.set_location('hxt_time_pay.local_pay',20);
1923: end if;
1924: IF next_seq_cur%NOTFOUND THEN
1925: if g_debug then
1926: hr_utility.set_location('hxt_time_pay.local_pay',30);
1922: hr_utility.set_location('hxt_time_pay.local_pay',20);
1923: end if;
1924: IF next_seq_cur%NOTFOUND THEN
1925: if g_debug then
1926: hr_utility.set_location('hxt_time_pay.local_pay',30);
1927: end if;
1928: FND_MESSAGE.SET_NAME('HXT','HXT_39290_NO_SEQ_NO_F_HRS_WKED');
1929: RETURN call_hxthxc_gen_error('HXT','HXT_39290_NO_SEQ_NO_F_HRS_WKED',NULL,location, '');
1930: --RETURN call_gen_error(location, '');
1930: --RETURN call_gen_error(location, '');
1931: END IF;
1932:
1933: if g_debug then
1934: hr_utility.set_location('hxt_time_pay.local_pay',40);
1935: end if;
1936: next_seq := next_seq + 10;
1937: if g_debug then
1938: hr_utility.trace('next_seq :'||next_seq);
1949: hr_utility.trace('location :'||location);
1950: end if;
1951: -- Call function to insert record in to hours worked table.
1952: if g_debug then
1953: hr_utility.set_location('hxt_time_pay.local_pay',50);
1954: end if;
1955: insert_hrs(pay_return_code,
1956: new_record_id,
1957: p_hours_to_pay,
1963:
1964: if g_debug then
1965: hr_utility.trace('pay_return_code :'||pay_return_code);
1966: hr_utility.trace('new_record_id :'||new_record_id);
1967: hr_utility.set_location('hxt_time_pay.local_pay',60);
1968: end if;
1969: -- Now generate all premiums for the record just inserted if
1970: -- premiums are to be paid.
1971: if g_debug then
1975: hr_utility.trace('g_sdf_id :'||g_sdf_id);
1976: end if;
1977: IF g_pep_id IS NOT NULL AND pay_return_code = 0 THEN
1978: if g_debug then
1979: hr_utility.set_location('hxt_time_pay.local_pay',70);
1980: end if;
1981: -- Commented out this and passing both sdf_id and sdovr_id to GEN_PREMIUMS
1982: -- because we need both the values to determine which gets applied to the
1983: -- incoming time_in and time_outs
1997: p_time_out,
1998: p_pay_element_type_id,
1999: next_seq);
2000: if g_debug then
2001: hr_utility.set_location('hxt_time_pay.local_pay',80);
2002: end if;
2003: END IF;
2004: if g_debug then
2005: hr_utility.set_location('hxt_time_pay.local_pay',90);
2001: hr_utility.set_location('hxt_time_pay.local_pay',80);
2002: end if;
2003: END IF;
2004: if g_debug then
2005: hr_utility.set_location('hxt_time_pay.local_pay',90);
2006: end if;
2007: RETURN pay_return_code;
2008: EXCEPTION
2009: WHEN OTHERS THEN
2007: RETURN pay_return_code;
2008: EXCEPTION
2009: WHEN OTHERS THEN
2010: if g_debug then
2011: hr_utility.set_location('hxt_time_pay.local_pay',100);
2012: end if;
2013: FND_MESSAGE.SET_NAME('HXT','HXT_39273_OR_ERR_IN_PAY_MDLE');
2014: RETURN call_hxthxc_gen_error('HXT','HXT_39273_OR_ERR_IN_PAY_MDLE',NULL,location, '', sqlerrm);
2015: -- RETURN call_gen_error(location, '', sqlerrm);
2057: BEGIN
2058:
2059: g_debug :=hr_utility.debug_enabled;
2060: if g_debug then
2061: hr_utility.set_location('hxt_time_pay.pay',10);
2062: end if;
2063:
2064: -- Bug 8855103
2065: -- Below construct added to throw error when
2084: g_element_type_id,
2085: g_time_in,
2086: g_time_out);
2087: if g_debug then
2088: hr_utility.set_location('hxt_time_pay.pay',20);
2089: end if;
2090:
2091: EXCEPTION
2092: WHEN OTHERS THEN
2090:
2091: EXCEPTION
2092: WHEN OTHERS THEN
2093: if g_debug then
2094: hr_utility.set_location('hxt_time_pay.pay',30);
2095: end if;
2096: FND_MESSAGE.SET_NAME('HXT','HXT_39269_ORACLE_ERROR');
2097: RETURN call_hxthxc_gen_error('HXT','HXT_39269_ORACLE_ERROR',NULL,location, '', sqlerrm);
2098: --2278400 RETURN call_gen_error(location, '', sqlerrm);
2099: END; -- END pay
2100:
2101: --begin
2102:
2103: END; -- package hxt_time_pay