[Home] [Help]
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,
3376: ROWIDTOCHAR(ret.ROWID)
3377: FROM hxt_det_hours_worked_f det,
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
3382: AND det.pbl_line_id = ret.old_pbl_id
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,
3401: ROWIDTOCHAR(ret.ROWID)
3402: FROM hxt_det_hours_worked_f det,
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
3407: AND det.pbl_line_id = ret.pbl_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
3426: AND sum.id = det.parent_id
3427: AND sum.effective_end_date = hr_general.end_of_time
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,
3512: ret.approval_status,
3513: ret.start_time,
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
3544: AND det.hours <> 0
3545: AND det.retro_batch_id = p_batch_id
3549: AND sum.time_building_block_ovn = ret.object_version_number
3550: AND (ret.batch_id IS NOT NULL
3551: OR ret.retro_batch_id IS NOT NULL
3552: OR ret.old_batch_id IS NOT NULL)
3553: AND NOT EXISTS ( SELECT /*+ INDEX(ret2 HXC_RET_PAY_LATEST_DETAILS_FK1) *
3554: 1
3555: FROM hxc_ret_pay_latest_details ret2
3556: WHERE ret.time_building_block_id = ret2.time_building_block_id
3557: AND ret.object_version_number = ret2.object_version_number
3551: OR ret.retro_batch_id IS NOT NULL
3552: OR ret.old_batch_id IS NOT NULL)
3553: AND NOT EXISTS ( SELECT /*+ INDEX(ret2 HXC_RET_PAY_LATEST_DETAILS_FK1) *
3554: 1
3555: FROM hxc_ret_pay_latest_details ret2
3556: WHERE ret.time_building_block_id = ret2.time_building_block_id
3557: AND ret.object_version_number = ret2.object_version_number
3558: AND ret2.old_attribute1 = det.element_type_id )
3559: )
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
3598: AND det.retro_batch_id = p_batch_id
3599: AND det.parent_id = sum.id
3622: CLOSE get_retro_ids;
3623:
3624: -- Update the relevant retro batch details.
3625: FORALL i IN ret_pbl_tab.FIRST..ret_pbl_tab.LAST
3626: UPDATE hxc_ret_pay_latest_details
3627: SET retro_batch_id = ret_batch_tab(i),
3628: retro_pbl_id = ret_pbl_tab(i),
3629: request_id = FND_GLOBAL.conc_request_id
3630: WHERE ROWID = CHARTOROWID(rowtab(i));
3643: CLOSE get_retro_ids2;
3644:
3645: -- Update the relevant retro batch details.
3646: FORALL i IN ret_pbl_tab.FIRST..ret_pbl_tab.LAST
3647: UPDATE hxc_ret_pay_latest_details
3648: SET retro_batch_id = ret_batch_tab(i),
3649: retro_pbl_id = ret_pbl_tab(i),
3650: old_pbl_id = pbl_id,
3651: old_batch_id = batch_id,
3686:
3687: CLOSE get_modified_values;
3688:
3689: FORALL i IN ret_pbl_tab.FIRST..ret_pbl_tab.LAST
3690: UPDATE hxc_ret_pay_latest_details
3691: SET batch_id = ret_batch_tab(i),
3692: pbl_id = ret_pbl_tab(i),
3693: attribute1 = old_attribute1,
3694: measure = hrs_tab(i),
3714: -- Here, Dbt 8 hours is a new entry altogether, while Ovt 4 hours is just
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
3748: old_batch_id_tab;
3749:
3750:
3751: FORALL i IN old_batch_id_tab.FIRST..old_batch_id_tab.LAST
3752: INSERT INTO hxc_ret_pay_latest_details
3753: ( resource_id,
3754: time_building_block_id,
3755: approval_status,
3756: start_time,
3840: rowid_tab;
3841:
3842: -- Delete the entries already there.
3843: FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
3844: DELETE FROM hxc_ret_pay_latest_details
3845: WHERE ROWID = CHARTOROWID(rowid_tab(i));
3846:
3847: -- Insert the new entries.
3848: FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
3845: WHERE ROWID = CHARTOROWID(rowid_tab(i));
3846:
3847: -- Insert the new entries.
3848: FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
3849: INSERT INTO hxc_ret_pay_latest_details
3850: ( resource_id,
3851: time_building_block_id,
3852: approval_status,
3853: start_time,
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
3933: AND pbl.batch_line_id = det.retro_pbl_line_id
3934: AND det.tim_id = p_tim_id
3935: AND det.pay_status = 'B'
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
3952: AND pbl.batch_line_id = det.retro_pbl_line_id
3953: AND det.tim_id = p_tim_id
3954: AND det.pay_status = 'B'
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'
3972: AND det.tim_id = p_tim_id
3973: AND det.effective_start_date >= sum.effective_start_date
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
4058: AND det.retro_pbl_line_id IS NOT NULL
4059: AND det.pay_status = 'C'
4076: ret.attribute1,
4077: ret.attribute2,
4078: ret.attribute3
4079: FROM hxt_sum_hours_worked_f sum,
4080: hxc_ret_pay_latest_details ret
4081: WHERE sum.tim_id = p_tim_id
4082: AND ret.time_building_block_id = sum.time_building_block_id
4083: AND ret.batch_id = p_batch_id
4084: AND ret.request_id <> FND_GLOBAL.conc_request_id
4126: hr_utility.trace('Count := '||l_rowidtab.COUNT);
4127: END IF;
4128:
4129: FORALL i IN l_rowidtab.FIRST..l_line_tab.LAST
4130: UPDATE hxc_ret_pay_latest_details
4131: SET retro_batch_id = p_batch_id,
4132: retro_pbl_id = l_line_tab(i),
4133: old_batch_id = batch_id,
4134: old_pbl_id = pbl_id,
4174: hr_utility.trace('Count '||l_rowidtab.COUNT);
4175: END IF;
4176:
4177: FORALL i IN l_rowidtab.FIRST..l_line_tab.LAST
4178: UPDATE hxc_ret_pay_latest_details
4179: SET retro_batch_id = p_batch_id,
4180: retro_pbl_id = l_line_tab(i),
4181: hxt_request_id = request_id,
4182: hxt_batch_id = p_batch_id,
4238: hr_utility.trace('Count '||l_rowidtab.COUNT);
4239: END IF;
4240:
4241: FORALL i IN l_rowidtab.FIRST..l_rowidtab.LAST
4242: UPDATE hxc_ret_pay_latest_details
4243: SET hxt_element_id = attribute1,
4244: hxt_attribute2 = attribute2,
4245: hxt_measure = measure,
4246: hxt_request_id = request_id,
4317: END IF;
4318:
4319:
4320: FORALL i IN resource_id_tab.FIRST..resource_id_tab.LAST
4321: INSERT INTO hxc_ret_pay_latest_details
4322: ( resource_id,
4323: time_building_block_id,
4324: approval_status,
4325: start_time,
4382: -- Now we inserted the exploded details in the table;
4383: -- we have to delete the already existing dummy records created by Xfer time from OTL to BEE.
4384: -- Meanwhile, collect the actual attributes, measure, batch_id etc created by Xfer time process.
4385: /*
4386: DELETE FROM hxc_ret_pay_latest_details
4387: WHERE batch_id = p_batch_id
4388: AND request_id <> FND_GLOBAL.conc_request_id
4389: AND pbl_id IS NULL
4390: RETURNING time_building_block_id,
4418: IF time_building_block_id_tab.COUNT > 0
4419: THEN
4420:
4421: FORALL i IN l_rowidtab.FIRST..l_rowidtab.LAST
4422: DELETE FROM hxc_ret_pay_latest_details
4423: WHERE rowid = CHARTOROWID(l_rowidtab(i));
4424:
4425:
4426: IF g_debug
4432:
4433: -- Update this back on to the RDB table. These actual values would be used by
4434: -- Rollback process.
4435: FORALL i IN time_building_block_id_tab.FIRST..time_building_block_id_tab.LAST
4436: UPDATE hxc_ret_pay_latest_details
4437: SET hxt_request_id = hxt_reqtab(i),
4438: hxt_measure = hxt_measuretab(i),
4439: hxt_element_id = hxt_att1tab(i),
4440: hxt_attribute2 = attribute2_tab(i),