[Home] [Help]
983: CURSOR current_hours (p_tim_id NUMBER)
984: IS
985: SELECT /*+ LEADING(tim.HXT_TIMECARDS_F)
986: INDEX(tim.HXT_TIMECARDS_F HXT_TIMECARDS_PK)
987: USE_NL(tim.HXT_TIMECARDS_F hrw.HXT_DET_HOURS_WORKED_F)
988: INDEX(hrw.HXT_DET_HOURS_WORKED_F HXT_DET_HOURS_WORKED_F_TIM_FK)
989: INDEX(ffvr FND_FLEX_VALUES_U1)
990: INDEX(loct HR_LOCATIONS_ALL_TL_PK)
991: INDEX(loc.HR_LOCATIONS_ALL HR_LOCATIONS_PK)
984: IS
985: SELECT /*+ LEADING(tim.HXT_TIMECARDS_F)
986: INDEX(tim.HXT_TIMECARDS_F HXT_TIMECARDS_PK)
987: USE_NL(tim.HXT_TIMECARDS_F hrw.HXT_DET_HOURS_WORKED_F)
988: INDEX(hrw.HXT_DET_HOURS_WORKED_F HXT_DET_HOURS_WORKED_F_TIM_FK)
989: INDEX(ffvr FND_FLEX_VALUES_U1)
990: INDEX(loct HR_LOCATIONS_ALL_TL_PK)
991: INDEX(loc.HR_LOCATIONS_ALL HR_LOCATIONS_PK)
992: INDEX(elt PAY_ELEMENT_TYPES_F_PK)
1309: IS
1310: SELECT hrw.hours hours_worked, hrw.ROWID hrw_rowid -- OHM199
1311: FROM hxt_timecards_x tim,
1312: per_time_periods ptp,
1313: hxt_det_hours_worked_f hrw,
1314: hxt_sum_hours_worked_f hsw,
1315: per_assignments_f asm,
1316: pay_element_types_f elt
1317: WHERE hrw.assignment_id = p_assignment_id
1451: IS
1452: SELECT hrw.amount amount, hrw.ROWID hrw_rowid -- OHM199
1453: FROM hxt_timecards_x tim,
1454: per_time_periods ptp,
1455: hxt_det_hours_worked_f hrw,
1456: per_assignments_f asm,
1457: pay_element_types_f elt
1458: WHERE hrw.assignment_id = p_assignment_id
1459: AND asm.assignment_id = hrw.assignment_id
1690: -- and move A and D back to 'C', to reverse the data.
1691:
1692: IF l_hours_to_send = 0
1693: THEN
1694: UPDATE hxt_det_hours_worked_f
1695: SET pay_status = 'D',
1696: last_update_date = SYSDATE,
1697: retro_batch_id_2 = p_batch_id
1698: WHERE ROWID = l_prev_hours_rec.hrw_rowid
1699: -- ADDED BY MV: IF THERE IS ONLY ONE ROW, PREV and CURR records
1700: -- are the same; we should not update such records.
1701: AND l_prev_hours_rec.hrw_rowid <> l_hours_rec.hrw_rowid;
1702: ELSE
1703: UPDATE hxt_det_hours_worked_f
1704: SET pay_status = 'A',
1705: last_update_date = SYSDATE,
1706: retro_batch_id_2 = p_batch_id
1707: WHERE ROWID = l_prev_hours_rec.hrw_rowid;
1882: l_location := 'Step 1E';
1883:
1884:
1885: -- begin OHM180
1886: UPDATE hxt_det_hours_worked_f
1887: SET retro_pbl_line_id = l_batch_line_id
1888: WHERE ROWID = l_hours_rec.hrw_rowid;
1889:
1890: -- end OHM180
1955:
1956: -- begin OHM199
1957: IF l_amount_to_send = 0
1958: THEN
1959: UPDATE hxt_det_hours_worked_f
1960: SET pay_status = 'D',
1961: last_update_date = SYSDATE
1962: WHERE ROWID = l_prev_amount_rec.hrw_rowid
1963: -- ADDED BY MV: IF THERE IS ONLY ONE ROW, PREV and CURR records
1963: -- ADDED BY MV: IF THERE IS ONLY ONE ROW, PREV and CURR records
1964: -- are the same; we should not update such records.
1965: AND l_prev_amount_rec.hrw_rowid <> l_amount_rec.hrw_rowid;
1966: ELSE
1967: UPDATE hxt_det_hours_worked_f
1968: SET pay_status = 'A',
1969: last_update_date = SYSDATE
1970: WHERE ROWID = l_prev_amount_rec.hrw_rowid;
1971: END IF;
2104: hxt_util.DEBUG (' insert amount to paymix.'); -- debug only --HXT115
2105:
2106:
2107: -- begin OHM180
2108: UPDATE hxt_det_hours_worked_f
2109: SET retro_pbl_line_id = l_batch_line_id
2110: WHERE ROWID = l_amount_rec.hrw_rowid;
2111:
2112: -- end OHM180
2207:
2208: PROCEDURE mark_prev_hours_rows (p_tim_id IN NUMBER)
2209: IS
2210: BEGIN
2211: UPDATE hxt_det_hours_worked_f
2212: SET pay_status = 'D',
2213: last_update_date = SYSDATE
2214: WHERE ROWID IN
2215: (SELECT hrw.ROWID
2212: SET pay_status = 'D',
2213: last_update_date = SYSDATE
2214: WHERE ROWID IN
2215: (SELECT hrw.ROWID
2216: FROM hxt_det_hours_worked_f hrw
2217: WHERE hrw.tim_id = p_tim_id
2218: AND hrw.pay_status = 'C'
2219: AND hrw.amount IS NULL
2220: AND hrw.parent_id > 0
2286: retro.fcl_tax_rule_code,
2287: 'ZZZZZZZZZZ'
2288: )));
2289:
2290: UPDATE hxt_det_hours_worked_f
2291: SET pay_status = 'A',
2292: last_update_date = SYSDATE
2293: WHERE ROWID IN
2294: (SELECT hrw.ROWID
2291: SET pay_status = 'A',
2292: last_update_date = SYSDATE
2293: WHERE ROWID IN
2294: (SELECT hrw.ROWID
2295: FROM hxt_det_hours_worked_f hrw
2296: WHERE hrw.tim_id = p_tim_id
2297: AND hrw.pay_status = 'C'
2298: AND hrw.amount IS NULL
2299: AND hrw.parent_id > 0
2369:
2370: PROCEDURE mark_prev_amount_rows (p_tim_id IN NUMBER)
2371: IS
2372: BEGIN
2373: UPDATE hxt_det_hours_worked_f
2374: SET pay_status = 'D',
2375: last_update_date = SYSDATE
2376: WHERE ROWID IN
2377: (SELECT hrw.ROWID
2374: SET pay_status = 'D',
2375: last_update_date = SYSDATE
2376: WHERE ROWID IN
2377: (SELECT hrw.ROWID
2378: FROM hxt_det_hours_worked_f hrw
2379: WHERE hrw.tim_id = p_tim_id
2380: AND hrw.pay_status = 'C'
2381: AND hrw.amount IS NOT NULL
2382: AND hrw.parent_id > 0
2449: retro.fcl_tax_rule_code,
2450: 'ZZZZZZZZZZ'
2451: )));
2452:
2453: UPDATE hxt_det_hours_worked_f
2454: SET pay_status = 'A',
2455: last_update_date = SYSDATE
2456: WHERE ROWID IN
2457: (SELECT hrw.ROWID
2454: SET pay_status = 'A',
2455: last_update_date = SYSDATE
2456: WHERE ROWID IN
2457: (SELECT hrw.ROWID
2458: FROM hxt_det_hours_worked_f hrw
2459: WHERE hrw.tim_id = p_tim_id
2460: AND hrw.pay_status = 'C'
2461: AND hrw.amount IS NOT NULL
2462: AND hrw.parent_id > 0
2533:
2534: PROCEDURE mark_retro_rows_complete (p_tim_id NUMBER)
2535: IS
2536: BEGIN
2537: UPDATE hxt_det_hours_worked_f
2538: SET pay_status = 'C',
2539: last_update_date = SYSDATE
2540: WHERE ROWID IN (SELECT hrw.ROWID
2541: FROM hxt_det_hours_worked_x hrw
2647: hrw.zip_code,
2648: hrw.parent_id
2649: FROM hxt_timecards_x tim, /* SIR416 PWM 21MAR00 */
2650: per_time_periods ptp,
2651: hxt_det_hours_worked_f hrw,
2652: hr_lookups hcl, --GLOBAL
2653: per_assignments_f asm,
2654: pay_element_types_f elt,
2655: hxt_pay_element_types_f_ddf_v eltv, --SIR65
2711: l_retcode NUMBER; /* BSE107 */
2712:
2713: --BEGIN GLOBAL
2714: -- l_hourly_rate pay_pdt_batch_lines.hourly_rate%TYPE := NULL; --SIR65
2715: l_hourly_rate hxt_det_hours_worked_f.hourly_rate%TYPE := NULL;
2716:
2717:
2718: --CURSOR rate_paid_cur(c_line_id NUMBER) IS --SIR65
2719: --SELECT pbl.hourly_rate --SIR65
3057: -- before backing out.
3058: -- This comes handy during rollback.
3059: -- Eg.
3060: -- Reg 8 hrs entered in retro change.
3061: -- HXT_DET_HOURS_WORKED_F has status 'C', retro_pbl_line_id populated, retro_batch_id populated.
3062: -- This entry is zeroed out in the next change.
3063: -- Backout happens and this below update will mark the backout retro_pbl_line_id
3064: -- We now lose the original retro_pbl_line_id and cant rollback this detail.
3065: -- Hence the new column to keep history.
3062: -- This entry is zeroed out in the next change.
3063: -- Backout happens and this below update will mark the backout retro_pbl_line_id
3064: -- We now lose the original retro_pbl_line_id and cant rollback this detail.
3065: -- Hence the new column to keep history.
3066: UPDATE hxt_det_hours_worked_f
3067: SET pay_status = 'B',
3068: last_update_date = SYSDATE,
3069: retro_pbl_line_id_2 = retro_pbl_line_id,
3070: retro_pbl_line_id = l_batch_line_id -- OHM180
3146: hrw.zip_code
3147: /* BSE107 */
3148: FROM hxt_timecards_x tim, /* SIR416 PWM 21MAR00 */
3149: per_time_periods ptp,
3150: hxt_det_hours_worked_f hrw,
3151: hr_lookups hcl, --GLOBAL
3152: per_assignments_f asm,
3153: pay_element_types_f elt,
3154: pay_cost_allocation_keyflex pcak,
3332: );
3333:
3334:
3335: --END GLOBAL
3336: UPDATE hxt_det_hours_worked_f
3337: SET pay_status = 'B',
3338: last_update_date = SYSDATE,
3339: retro_pbl_line_id = l_batch_line_id -- OHM180
3340: WHERE ROWID = l_leftover.hrw_rowid;
3367: -- Added hints to improve performance.
3368: CURSOR get_retro_ids( p_batch_id IN NUMBER,
3369: p_tim_id IN NUMBER)
3370: IS SELECT /*+ LEADING(det)
3371: INDEX(det HXT_DET_HOURS_WORKED_F_TIM_FK)
3372: INDEX(pbl PAY_BATCH_LINES_PK)
3373: INDEX(ret HXC_RET_PAY_LATEST_DETAILS_N7) *
3374: det.retro_pbl_line_id,
3375: p_batch_id,
3373: INDEX(ret HXC_RET_PAY_LATEST_DETAILS_N7) *
3374: det.retro_pbl_line_id,
3375: p_batch_id,
3376: ROWIDTOCHAR(ret.ROWID)
3377: FROM hxt_det_hours_worked_f det,
3378: hxc_ret_pay_latest_details ret,
3379: pay_batch_lines pbl
3380: WHERE det.pay_status = 'B'
3381: AND det.effective_end_date <> hr_general.end_of_time
3392: -- Added hints to improve performance.
3393: CURSOR get_retro_ids2( p_batch_id IN NUMBER,
3394: p_tim_id IN NUMBER)
3395: IS SELECT /*+ LEADING(det)
3396: INDEX(det HXT_DET_HOURS_WORKED_F_TIM_FK)
3397: INDEX(pbl PAY_BATCH_LINES_PK)
3398: INDEX(ret HXC_RET_PAY_LATEST_DETAILS_N6) *
3399: det.retro_pbl_line_id,
3400: p_batch_id,
3398: INDEX(ret HXC_RET_PAY_LATEST_DETAILS_N6) *
3399: det.retro_pbl_line_id,
3400: p_batch_id,
3401: ROWIDTOCHAR(ret.ROWID)
3402: FROM hxt_det_hours_worked_f det,
3403: hxc_ret_pay_latest_details ret,
3404: pay_batch_lines pbl
3405: WHERE det.pay_status = 'B'
3406: AND det.effective_end_date <> hr_general.end_of_time
3417: IS SELECT det.retro_pbl_line_id,
3418: det.retro_batch_id,
3419: det.hours,
3420: ROWIDTOCHAR(ret.ROWID)
3421: FROM hxt_det_hours_worked_f det,
3422: hxt_sum_hours_worked_f sum,
3423: hxc_ret_pay_latest_details ret
3424: WHERE det.tim_id = p_tim_id
3425: AND det.effective_end_date = hr_general.end_of_time
3503: old_request_id,
3504: batch_id,
3505: retro_batch_id,
3506: old_batch_id
3507: FROM ( SELECT /*+ INDEX(det HXT_DET_HOURS_WORKED_F_TIM_FK)
3508: INDEX(sum HXT_SUM_HOURS_WORKED_PK)
3509: INDEX(ret HXC_RET_PAY_LATEST_DETAILS_FK1) *
3510: ret.resource_id,
3511: ret.time_building_block_id,
3535: NULL old_batch_id,
3536: RANK() OVER (PARTITION BY ret.time_building_block_id,
3537: ret.object_version_number
3538: ORDER BY ret.ROWID) rank
3539: FROM hxt_det_hours_worked_f det,
3540: hxt_sum_hours_worked_f sum,
3541: hxc_ret_pay_latest_details ret
3542: WHERE det.parent_id = sum.id
3543: AND det.tim_id = p_tim_id
3589: det.retro_batch_id,
3590: ret.retro_batch_id,
3591: ret.old_batch_id,
3592: ROWIDTOCHAR(ret.ROWID)
3593: FROM hxt_det_hours_worked_f det,
3594: hxt_sum_hours_worked_f sum,
3595: hxc_ret_pay_latest_details ret
3596: WHERE det.tim_id = p_tim_id
3597: AND det.effective_end_date = hr_general.end_of_time
3715: -- a change. Change would have been picked up by the earlier cursor. This
3716: -- cursor would pick up the new entry.
3717: -- The cursor will pick up one record for each time building block id from
3718: -- hxc_ret_pay_latest_details just to provide the relevant details which are
3719: -- not in hxt_det_hours_worked_f
3720:
3721: OPEN pick_new_details(p_tim_id,p_batch_id);
3722: FETCH pick_new_details BULK COLLECT INTO
3723: resource_id_tab,
3924:
3925: CURSOR get_backouts_not_touched_now
3926: IS SELECT ROWIDTOCHAR(ret.rowid),
3927: det.retro_pbl_line_id
3928: FROM hxt_det_hours_worked_f det,
3929: hxt_sum_hours_worked_f sum,
3930: pay_batch_lines pbl,
3931: hxc_ret_pay_latest_details ret
3932: WHERE pbl.batch_id = p_batch_id
3943:
3944: CURSOR get_backouts_touched_now
3945: IS SELECT ROWIDTOCHAR(ret.rowid),
3946: det.retro_pbl_line_id
3947: FROM hxt_det_hours_worked_f det,
3948: hxt_sum_hours_worked_f sum,
3949: pay_batch_lines pbl,
3950: hxc_ret_pay_latest_details ret
3951: WHERE pbl.batch_id = p_batch_id
3963:
3964:
3965: CURSOR get_changed_hours
3966: IS SELECT ROWIDTOCHAR(ret.rowid)
3967: FROM hxt_det_hours_worked_f det,
3968: hxt_sum_hours_worked_f sum,
3969: hxc_ret_pay_latest_details ret
3970: WHERE det.retro_batch_id = p_batch_id
3971: AND det.pay_status = 'C'
4049: det.retro_batch_id batch_id,
4050: NULL retro_batch_id,
4051: NULL old_batch_id,
4052: ret.application_set_id
4053: FROM hxt_det_hours_worked_f det,
4054: hxt_sum_hours_worked_f sum,
4055: hxc_ret_pay_latest_details ret,
4056: pay_batch_lines pbl
4057: WHERE det.retro_batch_id = p_batch_id