[Home] [Help]
1998: rank() over ( partition by ret.time_building_block_id,ret.object_version_number
1999: ORDER by ret.ROWID) rank
2000: FROM hxt_sum_hours_worked_f sum,
2001: hxt_det_hours_worked_f det,
2002: hxc_ret_pay_latest_details ret,
2003: hxc_ret_pay_details ret2,
2004: FND_sessions sess,
2005: pay_batch_lines pbl
2006: WHERE sum.tim_id = p_tim_id
2167: WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2168:
2169: put_log('Deleting duplicates from Ret Pay ');
2170: FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2171: DELETE FROM hxc_ret_pay_latest_details
2172: WHERE ROWID = CHARTOROWID(l_ret_rowtab(i))
2173: AND l_rank_tab(i) <> 1;
2174:
2175: put_log('Updating Ret Pay');
2173: AND l_rank_tab(i) <> 1;
2174:
2175: put_log('Updating Ret Pay');
2176: FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2177: UPDATE hxc_ret_pay_latest_details
2178: SET request_id = l_req_tab(i),
2179: measure = l_hrs_tab(i),
2180: attribute1 = l_att1_tab(i),
2181: attribute2 = l_att2_tab(i),
2419: pbl.batch_line_id
2420: FROM pay_batch_lines pbl,
2421: hxt_det_hours_worked_f det,
2422: hxt_sum_hours_worked_f sum,
2423: hxc_ret_pay_latest_details ret
2424: WHERE pbl.batch_id = p_batch_id
2425: AND pbl.batch_line_id = det.retro_pbl_line_id
2426: AND det.tim_id = p_tim_id
2427: AND det.pay_status = 'B'
2443: pbl.batch_line_id
2444: FROM pay_batch_lines pbl,
2445: hxt_det_hours_worked_f det,
2446: hxt_sum_hours_worked_f sum,
2447: hxc_ret_pay_latest_details ret
2448: WHERE pbl.batch_id = p_batch_id
2449: AND pbl.batch_line_id = det.retro_pbl_line_id
2450: AND det.pay_status = 'B'
2451: AND det.tim_id = p_tim_id
2461: CURSOR get_updated_records(p_batch_id IN NUMBER,
2462: p_tim_id IN NUMBER)
2463: IS SELECT DISTINCT
2464: ROWIDTOCHAR(ret.ROWID)
2465: FROM hxc_ret_pay_latest_details ret,
2466: hxt_sum_hours_worked_f sum
2467: WHERE hxt_batch_id = p_batch_id
2468: AND retro_batch_id IS NULL
2469: AND retro_pbl_id IS NULL
2478: ROWIDTOCHAR(ret.ROWID),
2479: pbl.batch_line_id
2480: FROM hxt_det_hours_worked_f det,
2481: hxt_sum_hours_worked_f sum,
2482: hxc_ret_pay_latest_details ret,
2483: pay_batch_lines pbl
2484: WHERE det.pay_status = 'C'
2485: AND det.retro_batch_id = p_batch_id
2486: AND det.retro_pbl_line_id = pbl.batch_line_id
2504: ret.object_version_number
2505: ORDER BY det.ROWID) rank
2506: FROM hxt_det_hours_worked_f det,
2507: hxt_sum_hours_worked_f sum,
2508: hxc_ret_pay_latest_details ret,
2509: pay_batch_lines pbl
2510: WHERE det.pay_status = 'C'
2511: AND det.retro_batch_id = p_batch_id
2512: AND det.tim_id = p_tim_id
2626: retro_pbl_line_id = retro_pbl_line_id_2
2627: WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2628:
2629: FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2630: UPDATE hxc_ret_pay_latest_details
2631: SET retro_batch_id = NULL,
2632: retro_pbl_id = NULL,
2633: request_id = hxt_request_id,
2634: measure = hxt_measure,
2667: WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2668:
2669:
2670: FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2671: UPDATE hxc_ret_pay_latest_details
2672: SET retro_batch_id = NULL,
2673: retro_pbl_id = NULL,
2674: request_id = old_request_id,
2675: measure = old_measure,
2704:
2705: IF l_det_rowtab.COUNT > 0
2706: THEN
2707: FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2708: DELETE FROM hxc_ret_pay_latest_details
2709: WHERE ROWID = CHARTOROWID(l_ret_rowtab(i));
2710:
2711: FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2712: UPDATE hxt_det_hours_worked_f
2731: IF l_det_rowtab.COUNT > 0
2732: THEN
2733:
2734: FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2735: DELETE FROM hxc_ret_pay_latest_details
2736: WHERE ROWID = CHARTOROWID(l_ret_rowtab(i))
2737: AND l_rank_tab(i) <> 1;
2738:
2739:
2737: AND l_rank_tab(i) <> 1;
2738:
2739:
2740: FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2741: UPDATE hxc_ret_pay_latest_details
2742: SET request_id = hxt_request_id,
2743: attribute1 = hxt_element_id,
2744: attribute2 = hxt_attribute2,
2745: attribute3 = hxt_attribute3,
2773: IF l_ret_rowtab.COUNT > 0
2774: THEN
2775:
2776: FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2777: UPDATE hxc_ret_pay_latest_details
2778: SET old_measure = measure,
2779: old_attribute1 = attribute1,
2780: old_attribute2 = attribute2,
2781: old_attribute3 = attribute3,
3277: ret2.attribute2,
3278: ret2.attribute3,
3279: ret2.request_id
3280: FROM hxt_sum_hours_worked sum,
3281: hxc_ret_pay_latest_details ret,
3282: hxc_ret_pay_details ret2
3283: WHERE sum.tim_id = p_tim_id
3284: AND sum.time_building_block_id = ret.time_building_block_id
3285: AND ret.time_building_block_id = ret2.time_building_block_id;
3417:
3418: IF l_id_tab.COUNT > 0
3419: THEN
3420: FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3421: DELETE FROM hxc_ret_pay_latest_details
3422: WHERE time_building_block_id = l_sum_id_tab(i)
3423: AND object_version_number <> l_sum_ovn_tab(i);
3424:
3425:
3428: WHERE time_building_block_id = l_sum_id_tab(i)
3429: AND object_version_number <> l_sum_ovn_tab(i);
3430:
3431: FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3432: UPDATE hxc_ret_pay_latest_details
3433: SET measure = l_hrs_tab(i),
3434: attribute1 = l_att1_tab(i),
3435: attribute2 = l_att2_tab(i),
3436: attribute3 = l_att3_tab(i),
3524: ret2.attribute2,
3525: ret2.attribute3,
3526: ret2.measure
3527: FROM hxt_sum_hours_worked sum,
3528: hxc_ret_pay_latest_details ret,
3529: hxc_ret_pay_details ret2
3530: WHERE sum.tim_id = p_tim_id
3531: AND sum.time_building_block_id = ret2.time_building_block_id
3532: AND sum.time_building_block_ovn = ret2.object_version_number
3733: IF l_id_tab.COUNT > 0
3734: THEN
3735:
3736: FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3737: DELETE FROM hxc_ret_pay_latest_details
3738: WHERE time_building_block_id = l_lines_time_building_block_id(i);
3739:
3740: /*
3741: FORALL i IN l_id_tab.FIRST..l_id_tab.LAST