[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
First, update previous detail pay_status='A' (adjusted if there
exists a current version of the row where only the
hours/amount are different.
Second, update previous detail pay_status='D'(dead) if there exists
a current version of the row where the hours/amount are equal.
Third, for any detail rows where pay_status='C' and the row
is expired, send backout transaction to PayMIX.
Step 5. Set rows on timecard to pay_status='C' if pay_status='R'.
*/
g_debug boolean := hr_utility.debug_enabled;
SELECT lookup_code
FROM hr_lookups
WHERE meaning = p_meaning
AND lookup_type = 'NAME_TRANSLATIONS'
AND enabled_flag = 'Y'
AND p_date_active BETWEEN NVL (start_date_active, p_date_active)
AND NVL (end_date_active, p_date_active);
SELECT fcl.meaning
FROM hr_lookups fcl --FORMS60
WHERE fcl.lookup_code = p_code
AND fcl.lookup_type = p_type
AND fcl.enabled_flag = 'Y'
AND p_date BETWEEN NVL (fcl.start_date_active, p_date)
AND NVL (fcl.end_date_active, p_date);
PROCEDURE insert_pay_batch_lines (
p_batch_id NUMBER,
p_batch_line_id OUT NOCOPY NUMBER,
p_assignment_id NUMBER,
p_assignment_number VARCHAR2,
p_amount NUMBER,
p_cost_allocation_keyflex_id NUMBER,
p_concatenated_segments VARCHAR2,
p_segment1 VARCHAR2,
p_segment2 VARCHAR2,
p_segment3 VARCHAR2,
p_segment4 VARCHAR2,
p_segment5 VARCHAR2,
p_segment6 VARCHAR2,
p_segment7 VARCHAR2,
p_segment8 VARCHAR2,
p_segment9 VARCHAR2,
p_segment10 VARCHAR2,
p_segment11 VARCHAR2,
p_segment12 VARCHAR2,
p_segment13 VARCHAR2,
p_segment14 VARCHAR2,
p_segment15 VARCHAR2,
p_segment16 VARCHAR2,
p_segment17 VARCHAR2,
p_segment18 VARCHAR2,
p_segment19 VARCHAR2,
p_segment20 VARCHAR2,
p_segment21 VARCHAR2,
p_segment22 VARCHAR2,
p_segment23 VARCHAR2,
p_segment24 VARCHAR2,
p_segment25 VARCHAR2,
p_segment26 VARCHAR2,
p_segment27 VARCHAR2,
p_segment28 VARCHAR2,
p_segment29 VARCHAR2,
p_segment30 VARCHAR2,
p_element_type_id NUMBER,
p_element_name VARCHAR2,
p_hourly_rate NUMBER,
p_locality_worked VARCHAR2,
p_rate_code VARCHAR2,
p_rate_multiple NUMBER,
p_separate_check_flag VARCHAR2,
p_tax_separately_flag VARCHAR2,
p_hours NUMBER,
p_date_worked DATE,
p_reason VARCHAR2,
p_batch_sequence NUMBER,
p_state_name VARCHAR2 default null, --dd
p_county_name VARCHAR2 default null,
p_city_name VARCHAR2 default null,
p_zip_code varchar2 default null,
p_parent_id NUMBER default 0
)
IS
-- l_batch_sequence NUMBER;
SELECT DISTINCT piv.NAME -- PIV.display_sequence
FROM --pay_element_types_f PET
pay_input_values_f piv,
pay_accrual_plans pap,
pay_net_calculation_rules pncr
WHERE --PET.element_type_id = cp_element_type_id
-- AND PET.element_type_id = PIV.element_type_id
piv.element_type_id = cp_element_type_id
AND cp_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pncr.date_input_value_id = piv.input_value_id
AND pncr.input_value_id <> pap.pto_input_value_id
AND pncr.input_value_id <> pap.co_input_value_id
AND pncr.accrual_plan_id = pap.accrual_plan_id
AND pap.accrual_plan_id IN
(SELECT papl.accrual_plan_id
FROM pay_accrual_plans papl,
pay_element_links_f pel,
pay_element_entries_f pee
WHERE pel.element_type_id =
papl.accrual_plan_element_type_id
AND cp_effective_date
BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pee.element_link_id =
pel.element_link_id
AND pee.assignment_id = cp_assignment_id
AND cp_effective_date
BETWEEN pee.effective_start_date
AND pee.effective_end_date);
hr_utility.set_location ('insert_pay_batch_lines', 10);
hr_utility.set_location ('insert_pay_batch_lines', 20);
hr_utility.set_location ('insert_pay_batch_lines', 30);
hr_utility.set_location ('insert_pay_batch_lines', 40);
hr_utility.set_location ('insert_pay_batch_lines', 50);
hr_utility.set_location ('insert_pay_batch_lines', 60);
hr_utility.set_location ('insert_pay_batch_lines', 70);
hr_utility.set_location ('insert_pay_batch_lines', 70);
hr_utility.set_location ('insert_pay_batch_lines', 80);
hr_utility.set_location ('insert_pay_batch_lines', 90);
hr_utility.set_location ('insert_pay_batch_lines', 100);
hr_utility.set_location ('insert_pay_batch_lines', 110);
hr_utility.set_location ('insert_pay_batch_lines', 120);
hr_utility.set_location ('insert_pay_batch_lines', 130);
hr_utility.set_location ('insert_pay_batch_lines', 140);
hr_utility.set_location ('insert_pay_batch_lines', 150);
hr_utility.set_location ('insert_pay_batch_lines', 160);
hr_utility.set_location ('insert_pay_batch_lines', 170);
'Oops...g_lookup_not_found in insert_pay_batch_lines'
); --HXT115
hxt_util.DEBUG ('Oops...others in insert_pay_batch_lines'); --HXT115
END insert_pay_batch_lines;
IS SELECT
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM hxt_sum_hours_worked_x
WHERE id = p_id;
SELECT /*+ LEADING(tim.HXT_TIMECARDS_F)
INDEX(tim.HXT_TIMECARDS_F HXT_TIMECARDS_PK)
USE_NL(tim.HXT_TIMECARDS_F hrw.HXT_DET_HOURS_WORKED_F)
INDEX(hrw.HXT_DET_HOURS_WORKED_F HXT_DET_HOURS_WORKED_F_TIM_FK)
INDEX(ffvr FND_FLEX_VALUES_U1)
INDEX(loct HR_LOCATIONS_ALL_TL_PK)
INDEX(loc.HR_LOCATIONS_ALL HR_LOCATIONS_PK)
INDEX(elt PAY_ELEMENT_TYPES_F_PK)
INDEX(eltv.HXT_ADD_ELEM_INFO_F HXT_ADD_ELEM_INFO_ON1)
INDEX(pcak PAY_COST_ALLOCATION_KEYFLE_PK)
INDEX(ptp PER_TIME_PERIODS_PK)
INDEX(asm.PAA PER_ASSIGNMENTS_F_PK) */
asm.assignment_number, elt.element_name, --FORMS60
eltv.hxt_premium_type, --SIR65
eltv.hxt_premium_amount, --SIR65
eltv.hxt_earning_category, --SIR65
DECODE (
SIGN (
DECODE (
SIGN (
ptp.start_date
- asm.effective_start_date
),
1, ptp.start_date,
asm.effective_start_date
)
- elt.effective_start_date
),
1, DECODE (
SIGN ( ptp.start_date
- asm.effective_start_date),
1, ptp.start_date,
asm.effective_start_date
),
elt.effective_start_date
)
from_date,
DECODE (
SIGN (
DECODE (
SIGN ( ptp.end_date
- asm.effective_end_date),
-1, ptp.end_date,
asm.effective_end_date
)
- elt.effective_end_date
),
-1, DECODE (
SIGN ( ptp.end_date
- asm.effective_end_date),
-1, ptp.end_date,
asm.effective_end_date
),
elt.effective_end_date
) TO_DATE,
hrw.rate_multiple, hrw.hourly_rate,
loct.location_code locality_worked, --FORMS60
ffvr.flex_value rate_code, hrw.separate_check_flag,
hrw.fcl_tax_rule_code tax_separately_flag, hrw.amount,
hrw.hours hours_worked, hrw.assignment_id,
/* fk - assignment_number */
hrw.ffv_cost_center_id, /* fk - cost_center_code */ pcak.concatenated_segments,
pcak.segment1, pcak.segment2, pcak.segment3, pcak.segment4,
pcak.segment5, pcak.segment6, pcak.segment7, pcak.segment8,
pcak.segment9, pcak.segment10, pcak.segment11,
pcak.segment12, pcak.segment13, pcak.segment14,
pcak.segment15, pcak.segment16, pcak.segment17,
pcak.segment18, pcak.segment19, pcak.segment20,
pcak.segment21, pcak.segment22, pcak.segment23,
pcak.segment24, pcak.segment25, pcak.segment26,
pcak.segment27, pcak.segment28, pcak.segment29,
pcak.segment30, hrw.element_type_id,
hrw.location_id,
hrw.ffv_rate_code_id,
asm.effective_end_date asm_effective_end_date,
elt.effective_end_date elt_effective_end_date,
hrw.parent_id, hrw.ROWID hrw_rowid, -- OHM180
hcl.meaning reason, --GLOBAL
hrw.date_worked, ptp.time_period_id,
hrw.state_name,
hrw.county_name,
hrw.city_name,
hrw.zip_code,
-- Bug 9159142
-- Added the following columns to pick up input values.
hsw.attribute1,
hsw.attribute2,
hsw.attribute3,
hsw.attribute4,
hsw.attribute5,
hsw.attribute6,
hsw.attribute7,
hsw.attribute8,
hsw.attribute9,
hsw.attribute10,
hsw.attribute11,
hsw.attribute12,
hsw.attribute13,
hsw.attribute14,
hsw.attribute15
FROM hxt_timecards_x tim,
per_time_periods ptp,
hxt_det_hours_worked_x hrw,
hxt_sum_hours_worked_x hsw,
hr_lookups hcl, --GLOBAL
per_assignments_f asm,
pay_element_types_f elt,
hxt_pay_element_types_f_ddf_v eltv, --SIR65
pay_cost_allocation_keyflex pcak,
hr_locations_all_tl loct, --FORMS60
hr_locations_no_join loc, --FORMS60
fnd_flex_values ffvr
WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
AND hrw.location_id = loc.location_id(+)
AND hsw.id = hrw.parent_id
AND loc.location_id = loct.location_id(+)
AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
--END FORMS60
AND hrw.ffv_cost_center_id =
pcak.cost_allocation_keyflex_id(+)
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND hrw.element_type_id = elt.element_type_id
AND elt.element_type_id = eltv.element_type_id
AND hrw.date_worked BETWEEN eltv.effective_start_date
AND eltv.effective_end_date
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND hrw.assignment_id = asm.assignment_id
AND hrw.amount IS NULL
AND hrw.tim_id = tim.id
AND tim.id = p_tim_id
AND tim.time_period_id = ptp.time_period_id
--BEGIN GLOBAL
AND hrw.date_worked BETWEEN NVL (
hcl.start_date_active(+),
hrw.date_worked
)
AND NVL (
hcl.end_date_active(+),
hrw.date_worked
)
AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
--END GLOBAL
ORDER BY hrw.id; --SIR95
SELECT asm.assignment_number, elt.element_name, --FORMS60
DECODE (
SIGN (
DECODE (
SIGN (
ptp.start_date
- asm.effective_start_date
),
1, ptp.start_date,
asm.effective_start_date
)
- elt.effective_start_date
),
1, DECODE (
SIGN ( ptp.start_date
- asm.effective_start_date),
1, ptp.start_date,
asm.effective_start_date
),
elt.effective_start_date
)
from_date,
DECODE (
SIGN (
DECODE (
SIGN ( ptp.end_date
- asm.effective_end_date),
-1, ptp.end_date,
asm.effective_end_date
)
- elt.effective_end_date
),
-1, DECODE (
SIGN ( ptp.end_date
- asm.effective_end_date),
-1, ptp.end_date,
asm.effective_end_date
),
elt.effective_end_date
) TO_DATE,
rate_multiple, hrw.hourly_rate,
loct.location_code locality_worked, --FORMS60
ffvr.flex_value rate_code, hrw.separate_check_flag,
hrw.fcl_tax_rule_code tax_separately_flag,
hrw.hours hours_worked, hrw.amount amount,
hrw.assignment_id, hrw.ffv_cost_center_id,
pcak.concatenated_segments, pcak.segment1, pcak.segment2,
pcak.segment3, pcak.segment4, pcak.segment5, pcak.segment6,
pcak.segment7, pcak.segment8, pcak.segment9, pcak.segment10,
pcak.segment11, pcak.segment12, pcak.segment13,
pcak.segment14, pcak.segment15, pcak.segment16,
pcak.segment17, pcak.segment18, pcak.segment19,
pcak.segment20, pcak.segment21, pcak.segment22,
pcak.segment23, pcak.segment24, pcak.segment25,
pcak.segment26, pcak.segment27, pcak.segment28,
pcak.segment29, pcak.segment30, hrw.element_type_id,
hrw.location_id, hrw.ffv_rate_code_id,
asm.effective_end_date asm_effective_end_date,
elt.effective_end_date elt_effective_end_date,
hrw.parent_id, hrw.ROWID hrw_rowid, -- OHM180
hcl.meaning reason, --GLOBAL
hrw.date_worked, ptp.time_period_id,
hrw.state_name,
hrw.county_name,
hrw.city_name,
hrw.zip_code
FROM hxt_timecards_x tim,
per_time_periods ptp,
hxt_det_hours_worked_x hrw,
hr_lookups hcl, --GLOBAL
per_assignments_f asm,
pay_element_types_f elt,
pay_cost_allocation_keyflex pcak,
hr_locations_all_tl loct, --FORMS60
hr_locations_no_join loc, --FORMS60
fnd_flex_values ffvr
WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
AND hrw.location_id = loc.location_id(+)
--BEGIN FORMS60
AND loc.location_id = loct.location_id(+)
AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
--END FORMS60
AND hrw.ffv_cost_center_id =
pcak.cost_allocation_keyflex_id(+)
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND hrw.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND hrw.assignment_id = asm.assignment_id
AND hrw.amount IS NOT NULL
AND hrw.tim_id = tim.id
AND tim.id = p_tim_id
AND tim.time_period_id = ptp.time_period_id
--BEGIN GLOBAL
AND hrw.date_worked BETWEEN NVL (
hcl.start_date_active(+),
hrw.date_worked
)
AND NVL (
hcl.end_date_active(+),
hrw.date_worked
)
AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
--END GLOBAL
ORDER BY hrw.id; --SIR95
SELECT hrw.hours hours_worked, hrw.ROWID hrw_rowid -- OHM199
FROM hxt_timecards_x tim,
per_time_periods ptp,
hxt_det_hours_worked_f hrw,
hxt_sum_hours_worked_f hsw,
per_assignments_f asm,
pay_element_types_f elt
WHERE hrw.assignment_id = p_assignment_id
AND asm.assignment_id = hrw.assignment_id
AND hrw.parent_id = hsw.id
-- Bug 12655429
--AND hsw.effective_end_date = hrw.effective_end_date
AND hrw.effective_start_date <= hsw.effective_end_date
AND hrw.effective_end_date >= hsw.effective_start_date
AND NVL(p_attribute_list,'XXX') =
NVL(hsw.attribute1||
hsw.attribute2||
hsw.attribute3||
hsw.attribute4||
hsw.attribute5||
hsw.attribute6||
hsw.attribute7||
hsw.attribute8||
hsw.attribute9||
hsw.attribute10||
hsw.attribute11||
hsw.attribute12||
hsw.attribute13||
hsw.attribute14||
hsw.attribute15,'XXX')
AND asm.effective_end_date = p_asm_effective_end_date
AND NVL (hrw.ffv_cost_center_id, 999999999999999) =
NVL (p_ffv_cost_center_id, 999999999999999)
AND hrw.element_type_id = p_element_type_id
AND elt.element_type_id = hrw.element_type_id
AND elt.effective_end_date = p_elt_effective_end_date
AND p_from_date =
DECODE (
SIGN (
DECODE (
SIGN (
ptp.start_date
- asm.effective_start_date
),
1, ptp.start_date,
asm.effective_start_date
)
- elt.effective_start_date
),
1, DECODE (
SIGN (
ptp.start_date
- asm.effective_start_date
),
1, ptp.start_date,
asm.effective_start_date
),
elt.effective_start_date
)
AND NVL (hrw.hourly_rate, 999999999999999) =
NVL (p_hourly_rate, 999999999999999)
AND NVL (hrw.location_id, 999999999999999) =
NVL (p_location_id, 999999999999999)
AND NVL (hrw.ffv_rate_code_id, 999999999999999) =
NVL (p_ffv_rate_code_id, 999999999999999)
AND NVL (hrw.rate_multiple, 999999999999999) =
NVL (p_rate_multiple, 999999999999999)
AND NVL (hrw.separate_check_flag, 'ZZZZZZZZZZ') =
NVL (p_separate_check_flag, 'ZZZZZZZZZZ')
AND NVL (hrw.fcl_tax_rule_code, 'ZZZZZZZZZZ') =
NVL (p_fcl_tax_rule_code, 'ZZZZZZZZZZ')
AND p_to_date =
DECODE (
SIGN (
DECODE (
SIGN (
ptp.end_date
- asm.effective_end_date
),
-1, ptp.end_date,
asm.effective_end_date
)
- elt.effective_end_date
),
-1, DECODE (
SIGN (
ptp.end_date
- asm.effective_end_date
),
-1, ptp.end_date,
asm.effective_end_date
),
elt.effective_end_date
)
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND hrw.amount IS NULL
AND hrw.parent_id = p_parent_id
AND hrw.tim_id = tim.id
AND tim.id = p_tim_id
AND tim.time_period_id = ptp.time_period_id
AND hrw.pay_status = 'C'
AND NVL (hrw.state_name, 'ZZZZZZZZZZ') =
NVL (p_state_name, 'ZZZZZZZZZZ')
AND NVL (hrw.county_name, 'ZZZZZZZZZZ') =
NVL (p_county_name, 'ZZZZZZZZZZ')
AND NVL (hrw.city_name, 'ZZZZZZZZZZ') =
NVL (p_city_name, 'ZZZZZZZZZZ')
AND NVL (hrw.zip_code, 'ZZZZZZZZZZ') =
NVL (p_zip_code, 'ZZZZZZZZZZ')
ORDER BY hrw.id; --SIR95
SELECT hrw.amount amount, hrw.ROWID hrw_rowid -- OHM199
FROM hxt_timecards_x tim,
per_time_periods ptp,
hxt_det_hours_worked_f hrw,
per_assignments_f asm,
pay_element_types_f elt
WHERE hrw.assignment_id = p_assignment_id
AND asm.assignment_id = hrw.assignment_id
AND asm.effective_end_date = p_asm_effective_end_date
AND NVL (hrw.ffv_cost_center_id, 999999999999999) =
NVL (p_ffv_cost_center_id, 999999999999999)
AND hrw.element_type_id = p_element_type_id
AND elt.element_type_id = hrw.element_type_id
AND elt.effective_end_date = p_elt_effective_end_date
AND p_from_date =
DECODE (
SIGN (
DECODE (
SIGN (
ptp.start_date
- asm.effective_start_date
),
1, ptp.start_date,
asm.effective_start_date
)
- elt.effective_start_date
),
1, DECODE (
SIGN (
ptp.start_date
- asm.effective_start_date
),
1, ptp.start_date,
asm.effective_start_date
),
elt.effective_start_date
)
AND NVL (hrw.hourly_rate, 999999999999999) =
NVL (p_hourly_rate, 999999999999999)
AND NVL (hrw.location_id, 999999999999999) =
NVL (p_location_id, 999999999999999)
AND NVL (hrw.ffv_rate_code_id, 999999999999999) =
NVL (p_ffv_rate_code_id, 999999999999999)
AND NVL (hrw.rate_multiple, 999999999999999) =
NVL (p_rate_multiple, 999999999999999)
AND NVL (hrw.separate_check_flag, 'ZZZZZZZZZZ') =
NVL (p_separate_check_flag, 'ZZZZZZZZZZ')
AND NVL (hrw.fcl_tax_rule_code, 'ZZZZZZZZZZ') =
NVL (p_fcl_tax_rule_code, 'ZZZZZZZZZZ')
AND p_to_date =
DECODE (
SIGN (
DECODE (
SIGN (
ptp.end_date
- asm.effective_end_date
),
-1, ptp.end_date,
asm.effective_end_date
)
- elt.effective_end_date
),
-1, DECODE (
SIGN (
ptp.end_date
- asm.effective_end_date
),
-1, ptp.end_date,
asm.effective_end_date
),
elt.effective_end_date
)
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND hrw.amount IS NOT NULL
AND hrw.parent_id > 0
AND hrw.parent_id = p_parent_id
AND hrw.tim_id = tim.id
AND tim.id = p_tim_id
AND tim.time_period_id = ptp.time_period_id
AND hrw.pay_status = 'C'
AND NVL (hrw.state_name, 'ZZZZZZZZZZ') =
NVL (p_state_name, 'ZZZZZZZZZZ')
AND NVL (hrw.county_name, 'ZZZZZZZZZZ') =
NVL (p_county_name, 'ZZZZZZZZZZ')
AND NVL (hrw.city_name, 'ZZZZZZZZZZ') =
NVL (p_city_name, 'ZZZZZZZZZZ')
AND NVL (hrw.zip_code, 'ZZZZZZZZZZ') =
NVL (p_zip_code, 'ZZZZZZZZZZ')
ORDER BY hrw.id; --SIR95
UPDATE hxt_det_hours_worked_f
SET pay_status = 'D',
last_update_date = SYSDATE,
retro_batch_id_2 = p_batch_id
WHERE ROWID = l_prev_hours_rec.hrw_rowid
-- ADDED BY MV: IF THERE IS ONLY ONE ROW, PREV and CURR records
-- are the same; we should not update such records.
UPDATE hxt_det_hours_worked_f
SET pay_status = 'A',
last_update_date = SYSDATE,
retro_batch_id_2 = p_batch_id
WHERE ROWID = l_prev_hours_rec.hrw_rowid;
hxt_util.DEBUG (' insert hours to paymix.'); -- debug only --HXT115
insert_pay_batch_lines (
p_batch_id,
l_batch_line_id,
l_hours_rec.assignment_id,
l_hours_rec.assignment_number,
l_hours_rec.amount,
l_hours_rec.ffv_cost_center_id,
l_hours_rec.concatenated_segments,
l_hours_rec.segment1,
l_hours_rec.segment2,
l_hours_rec.segment3,
l_hours_rec.segment4,
l_hours_rec.segment5,
l_hours_rec.segment6,
l_hours_rec.segment7,
l_hours_rec.segment8,
l_hours_rec.segment9,
l_hours_rec.segment10,
l_hours_rec.segment11,
l_hours_rec.segment12,
l_hours_rec.segment13,
l_hours_rec.segment14,
l_hours_rec.segment15,
l_hours_rec.segment16,
l_hours_rec.segment17,
l_hours_rec.segment18,
l_hours_rec.segment19,
l_hours_rec.segment20,
l_hours_rec.segment21,
l_hours_rec.segment22,
l_hours_rec.segment23,
l_hours_rec.segment24,
l_hours_rec.segment25,
l_hours_rec.segment26,
l_hours_rec.segment27,
l_hours_rec.segment28,
l_hours_rec.segment29,
l_hours_rec.segment30,
l_hours_rec.element_type_id,
l_hours_rec.element_name,
l_hours_rec.hourly_rate,
l_hours_rec.locality_worked,
l_hours_rec.rate_code,
l_hours_rec.rate_multiple,
l_hours_rec.separate_check_flag,
l_hours_rec.tax_separately_flag,
l_hours_to_send,
l_hours_rec.date_worked,
l_hours_rec.reason,
l_batch_sequence,
l_hours_rec.state_name,
l_hours_rec.county_name,
l_hours_rec.city_name,
l_hours_rec.zip_code,
l_hours_rec.parent_id
);
UPDATE hxt_det_hours_worked_f
SET retro_pbl_line_id = l_batch_line_id
WHERE ROWID = l_hours_rec.hrw_rowid;
UPDATE hxt_det_hours_worked_f
SET pay_status = 'D',
last_update_date = SYSDATE
WHERE ROWID = l_prev_amount_rec.hrw_rowid
-- ADDED BY MV: IF THERE IS ONLY ONE ROW, PREV and CURR records
-- are the same; we should not update such records.
UPDATE hxt_det_hours_worked_f
SET pay_status = 'A',
last_update_date = SYSDATE
WHERE ROWID = l_prev_amount_rec.hrw_rowid;
insert_pay_batch_lines (
p_batch_id,
l_batch_line_id,
l_amount_rec.assignment_id,
l_amount_rec.assignment_number,
l_amount_to_send,
l_amount_rec.ffv_cost_center_id,
l_amount_rec.concatenated_segments,
l_amount_rec.segment1,
l_amount_rec.segment2,
l_amount_rec.segment3,
l_amount_rec.segment4,
l_amount_rec.segment5,
l_amount_rec.segment6,
l_amount_rec.segment7,
l_amount_rec.segment8,
l_amount_rec.segment9,
l_amount_rec.segment10,
l_amount_rec.segment11,
l_amount_rec.segment12,
l_amount_rec.segment13,
l_amount_rec.segment14,
l_amount_rec.segment15,
l_amount_rec.segment16,
l_amount_rec.segment17,
l_amount_rec.segment18,
l_amount_rec.segment19,
l_amount_rec.segment20,
l_amount_rec.segment21,
l_amount_rec.segment22,
l_amount_rec.segment23,
l_amount_rec.segment24,
l_amount_rec.segment25,
l_amount_rec.segment26,
l_amount_rec.segment27,
l_amount_rec.segment28,
l_amount_rec.segment29,
l_amount_rec.segment30,
l_amount_rec.element_type_id,
l_amount_rec.element_name,
l_amount_rec.hourly_rate,
l_amount_rec.locality_worked,
l_amount_rec.rate_code,
l_amount_rec.rate_multiple,
l_amount_rec.separate_check_flag,
l_amount_rec.tax_separately_flag,
l_amount_rec.hours_worked,
l_amount_rec.date_worked,
l_amount_rec.reason,
l_batch_sequence,
l_amount_rec.state_name,
l_amount_rec.county_name,
l_amount_rec.city_name,
l_amount_rec.zip_code
);
hxt_util.DEBUG (' insert amount to paymix.'); -- debug only --HXT115
UPDATE hxt_det_hours_worked_f
SET retro_pbl_line_id = l_batch_line_id
WHERE ROWID = l_amount_rec.hrw_rowid;
g_xiv_table.DELETE;
hxt_batch_process.insert_pay_batch_errors (
p_batch_id,
'VE', -- RETROPAY
'',
l_return
);
hxt_util.DEBUG ('Error attempting to insert paymix information'); -- debug only --HXT115
hxt_batch_process.insert_pay_batch_errors (
p_batch_id,
'VE', -- RETROPAY
'',
l_return
);
hxt_util.DEBUG (' back from calling insert_pay_batch_errors'); -- debug only --HXT115
hxt_batch_process.insert_pay_batch_errors (
p_batch_id,
'VE', -- RETROPAY
--HXT111 'Error attempting to insert paymix information: (' || sqlerrm || ')',
'', --HXT111
l_return
);
hxt_util.DEBUG (' back from calling insert_pay_batch_errors'); -- debug only --HXT115
UPDATE hxt_det_hours_worked_f
SET pay_status = 'D',
last_update_date = SYSDATE
WHERE ROWID IN
(SELECT hrw.ROWID
FROM hxt_det_hours_worked_f hrw
WHERE hrw.tim_id = p_tim_id
AND hrw.pay_status = 'C'
AND hrw.amount IS NULL
AND hrw.parent_id > 0
AND EXISTS ( SELECT 'X'
FROM hxt_det_hours_worked_x retro
WHERE hrw.parent_id = retro.parent_id
AND retro.pay_status = 'R'
AND hrw.hours = retro.hours
AND hrw.amount IS NULL
and NVL(hrw.state_name,'ZZZZZZZZZZ')=
NVL(retro.state_name,'ZZZZZZZZZZ')
and NVL(hrw.county_name,'ZZZZZZZZZZ')=
NVL(retro.county_name,'ZZZZZZZZZZ')
and NVL(hrw.city_name,'ZZZZZZZZZZ')=
NVL(retro.city_name,'ZZZZZZZZZZ')
and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
NVL(retro.zip_code,'ZZZZZZZZZZ')
AND hrw.assignment_id =
retro.assignment_id
AND NVL (
hrw.ffv_cost_center_id,
999999999999999
) = NVL (
retro.ffv_cost_center_id,
999999999999999
)
AND hrw.element_type_id =
retro.element_type_id
AND NVL (
hrw.hourly_rate,
999999999999999
) = NVL (
retro.hourly_rate,
999999999999999
)
AND NVL (
hrw.location_id,
999999999999999
) = NVL (
retro.location_id,
999999999999999
)
AND NVL (
hrw.ffv_rate_code_id,
999999999999999
) = NVL (
retro.ffv_rate_code_id,
999999999999999
)
AND NVL (
hrw.rate_multiple,
999999999999999
) = NVL (
retro.rate_multiple,
999999999999999
)
AND NVL (
hrw.separate_check_flag,
'ZZZZZZZZZZ'
) = NVL (
retro.separate_check_flag,
'ZZZZZZZZZZ'
)
AND NVL (
hrw.fcl_tax_rule_code,
'ZZZZZZZZZZ'
) = NVL (
retro.fcl_tax_rule_code,
'ZZZZZZZZZZ'
)));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'A',
last_update_date = SYSDATE
WHERE ROWID IN
(SELECT hrw.ROWID
FROM hxt_det_hours_worked_f hrw
WHERE hrw.tim_id = p_tim_id
AND hrw.pay_status = 'C'
AND hrw.amount IS NULL
AND hrw.parent_id > 0
AND EXISTS ( SELECT 'X'
FROM hxt_det_hours_worked_x retro
WHERE hrw.parent_id = retro.parent_id
AND retro.pay_status = 'R'
AND hrw.hours <> retro.hours
AND hrw.amount IS NULL
AND hrw.assignment_id =
retro.assignment_id
and NVL(hrw.state_name,'ZZZZZZZZZZ')=
NVL(retro.state_name,'ZZZZZZZZZZ')
and NVL(hrw.county_name,'ZZZZZZZZZZ')=
NVL(retro.county_name,'ZZZZZZZZZZ')
and NVL(hrw.city_name,'ZZZZZZZZZZ')=
NVL(retro.city_name,'ZZZZZZZZZZ')
and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
NVL(retro.zip_code,'ZZZZZZZZZZ')
AND NVL (
hrw.ffv_cost_center_id,
999999999999999
) = NVL (
retro.ffv_cost_center_id,
999999999999999
)
AND hrw.element_type_id =
retro.element_type_id
AND NVL (
hrw.hourly_rate,
999999999999999
) = NVL (
retro.hourly_rate,
999999999999999
)
AND NVL (
hrw.location_id,
999999999999999
) = NVL (
retro.location_id,
999999999999999
)
AND NVL (
hrw.ffv_rate_code_id,
999999999999999
) = NVL (
retro.ffv_rate_code_id,
999999999999999
)
AND NVL (
hrw.rate_multiple,
999999999999999
) = NVL (
retro.rate_multiple,
999999999999999
)
AND NVL (
hrw.separate_check_flag,
'ZZZZZZZZZZ'
) = NVL (
retro.separate_check_flag,
'ZZZZZZZZZZ'
)
AND NVL (
hrw.fcl_tax_rule_code,
'ZZZZZZZZZZ'
) = NVL (
retro.fcl_tax_rule_code,
'ZZZZZZZZZZ'
)));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'D',
last_update_date = SYSDATE
WHERE ROWID IN
(SELECT hrw.ROWID
FROM hxt_det_hours_worked_f hrw
WHERE hrw.tim_id = p_tim_id
AND hrw.pay_status = 'C'
AND hrw.amount IS NOT NULL
AND hrw.parent_id > 0
AND EXISTS ( SELECT 'X'
FROM hxt_det_hours_worked_x retro
WHERE hrw.parent_id = retro.parent_id
AND retro.pay_status = 'R'
AND hrw.amount = retro.amount
AND hrw.amount IS NOT NULL
AND hrw.assignment_id =
retro.assignment_id
and NVL(hrw.state_name,'ZZZZZZZZZZ')=
NVL(retro.state_name,'ZZZZZZZZZZ')
and NVL(hrw.county_name,'ZZZZZZZZZZ')=
NVL(retro.county_name,'ZZZZZZZZZZ')
and NVL(hrw.city_name,'ZZZZZZZZZZ')=
NVL(retro.city_name,'ZZZZZZZZZZ')
and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
NVL(retro.zip_code,'ZZZZZZZZZZ')
AND NVL (
hrw.ffv_cost_center_id,
999999999999999
) = NVL (
retro.ffv_cost_center_id,
999999999999999
)
AND hrw.element_type_id =
retro.element_type_id
AND NVL (
hrw.hourly_rate,
999999999999999
) = NVL (
retro.hourly_rate,
999999999999999
)
AND NVL (
hrw.location_id,
999999999999999
) = NVL (
retro.location_id,
999999999999999
)
AND NVL (
hrw.ffv_rate_code_id,
999999999999999
) = NVL (
retro.ffv_rate_code_id,
999999999999999
)
AND NVL (
hrw.rate_multiple,
999999999999999
) = NVL (
retro.rate_multiple,
999999999999999
)
AND NVL (
hrw.separate_check_flag,
'ZZZZZZZZZZ'
) = NVL (
retro.separate_check_flag,
'ZZZZZZZZZZ'
)
AND NVL (
hrw.fcl_tax_rule_code,
'ZZZZZZZZZZ'
) = NVL (
retro.fcl_tax_rule_code,
'ZZZZZZZZZZ'
)));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'A',
last_update_date = SYSDATE
WHERE ROWID IN
(SELECT hrw.ROWID
FROM hxt_det_hours_worked_f hrw
WHERE hrw.tim_id = p_tim_id
AND hrw.pay_status = 'C'
AND hrw.amount IS NOT NULL
AND hrw.parent_id > 0
AND EXISTS ( SELECT 'X'
FROM hxt_det_hours_worked_x retro
WHERE hrw.parent_id = retro.parent_id
AND retro.pay_status = 'R'
AND hrw.amount <> retro.amount
AND hrw.amount IS NOT NULL
AND hrw.assignment_id =
retro.assignment_id
and NVL(hrw.state_name,'ZZZZZZZZZZ')=
NVL(retro.state_name,'ZZZZZZZZZZ')
and NVL(hrw.county_name,'ZZZZZZZZZZ')=
NVL(retro.county_name,'ZZZZZZZZZZ')
and NVL(hrw.city_name,'ZZZZZZZZZZ')=
NVL(retro.city_name,'ZZZZZZZZZZ')
and NVL(hrw.zip_code,'ZZZZZZZZZZ')=
NVL(retro.zip_code,'ZZZZZZZZZZ')
AND NVL (
hrw.ffv_cost_center_id,
999999999999999
) = NVL (
retro.ffv_cost_center_id,
999999999999999
)
AND hrw.element_type_id =
retro.element_type_id
AND NVL (
hrw.hourly_rate,
999999999999999
) = NVL (
retro.hourly_rate,
999999999999999
)
AND NVL (
hrw.location_id,
999999999999999
) = NVL (
retro.location_id,
999999999999999
)
AND NVL (
hrw.ffv_rate_code_id,
999999999999999
) = NVL (
retro.ffv_rate_code_id,
999999999999999
)
AND NVL (
hrw.rate_multiple,
999999999999999
) = NVL (
retro.rate_multiple,
999999999999999
)
AND NVL (
hrw.separate_check_flag,
'ZZZZZZZZZZ'
) = NVL (
retro.separate_check_flag,
'ZZZZZZZZZZ'
)
AND NVL (
hrw.fcl_tax_rule_code,
'ZZZZZZZZZZ'
) = NVL (
retro.fcl_tax_rule_code,
'ZZZZZZZZZZ'
)));
UPDATE hxt_det_hours_worked_f
SET pay_status = 'C',
last_update_date = SYSDATE
WHERE ROWID IN (SELECT hrw.ROWID
FROM hxt_det_hours_worked_x hrw
WHERE hrw.parent_id > 0
AND hrw.pay_status = 'R'
AND hrw.tim_id = p_tim_id);
IS SELECT
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM hxt_sum_hours_worked_f
WHERE id = p_id
AND effective_end_date <> hr_general.end_of_time
ORDER BY effective_end_date DESC;
SELECT hrw.ROWID hrw_rowid,
NVL (hrw.retro_pbl_line_id, hrw.pbl_line_id) line_id,
/* TA36 01/09/98 */
asm.assignment_number, elt.element_name, --FORMS60
eltv.hxt_premium_type, --SIR65
eltv.hxt_premium_amount, --SIR65
eltv.hxt_earning_category, --SIR65
DECODE (
SIGN (
DECODE (
SIGN ( ptp.start_date
- asm.effective_start_date),
1, ptp.start_date,
asm.effective_start_date
)
- elt.effective_start_date
),
1, DECODE (
SIGN ( ptp.start_date
- asm.effective_start_date),
1, ptp.start_date,
asm.effective_start_date
),
elt.effective_start_date
)
from_date,
DECODE (
SIGN (
DECODE (
SIGN ( ptp.end_date
- asm.effective_end_date),
-1, ptp.end_date,
asm.effective_end_date
)
- elt.effective_end_date
),
-1, DECODE (
SIGN ( ptp.end_date
- asm.effective_end_date),
-1, ptp.end_date,
asm.effective_end_date
),
elt.effective_end_date
) TO_DATE,
rate_multiple, hrw.hourly_rate, hrw.ffv_cost_center_id,
/* fk - cost_center_code */
pcak.concatenated_segments, pcak.segment1, pcak.segment2,
pcak.segment3, pcak.segment4, pcak.segment5, pcak.segment6,
pcak.segment7, pcak.segment8, pcak.segment9, pcak.segment10,
pcak.segment11, pcak.segment12, pcak.segment13,
pcak.segment14, pcak.segment15, pcak.segment16,
pcak.segment17, pcak.segment18, pcak.segment19,
pcak.segment20, pcak.segment21, pcak.segment22,
pcak.segment23, pcak.segment24, pcak.segment25,
pcak.segment26, pcak.segment27, pcak.segment28,
pcak.segment29, pcak.segment30,
/*TA36 ffvl.flex_value labor_dist_code,*/
loct.location_code locality_worked, --FORMS60
ffvr.flex_value rate_code,
hrw.separate_check_flag,
hrw.fcl_tax_rule_code tax_separately_flag, hrw.amount,
hrw.hours hours_worked, hrw.element_type_id, --GLOBAL
hcl.meaning reason, --GLOBAL
hrw.date_worked, /* BSE107 */ ptp.time_period_id,
/* BSE107 */
hrw.assignment_id, /* BSE107 */
hrw.state_name,
hrw.county_name,
hrw.city_name,
hrw.zip_code,
hrw.parent_id
FROM hxt_timecards_x tim, /* SIR416 PWM 21MAR00 */
per_time_periods ptp,
hxt_det_hours_worked_f hrw,
hr_lookups hcl, --GLOBAL
per_assignments_f asm,
pay_element_types_f elt,
hxt_pay_element_types_f_ddf_v eltv, --SIR65
pay_cost_allocation_keyflex pcak,
/*TA36fnd_flex_values ffvl, */
hr_locations_all_tl loct, --FORMS60
hr_locations_no_join loc, --FORMS60
fnd_flex_values ffvr
WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
AND hrw.location_id = loc.location_id(+)
--BEGIN FORMS60
AND loc.location_id = loct.location_id(+)
AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
--END FORMS60
/*TA36AND hrw.ffv_labor_account_id = ffvl.flex_value_id(+)*/
AND hrw.ffv_cost_center_id = pcak.cost_allocation_keyflex_id(+)
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND hrw.element_type_id = elt.element_type_id
/*GLOBAL AND elt.rowid=eltv.row_id --SIR65 */
AND elt.element_type_id = eltv.element_type_id /* GLOBAL */
AND hrw.date_worked BETWEEN eltv.effective_start_date /* GLOBAL */
AND eltv.effective_end_date /* GLOBAL */
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND hrw.assignment_id = asm.assignment_id
AND hrw.amount IS NULL
/* GLOBAL AND hrw.parent_id > 0 */
AND hrw.tim_id = tim.id
AND tim.id = p_tim_id
AND tim.time_period_id = ptp.time_period_id
--BEGIN GLOBAL
AND hrw.date_worked BETWEEN NVL (
hcl.start_date_active(+),
hrw.date_worked
)
AND NVL (
hcl.end_date_active(+),
hrw.date_worked
)
AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
--END GLOBAL
AND hrw.pay_status = 'C'
AND hrw.effective_end_date < hr_general.end_of_time; --SIR149 --FORMS60
SELECT pbl.value_1, pbl.value_2, pbl.value_3, pbl.value_4,
pbl.value_5, pbl.value_6, pbl.value_7, pbl.value_8,
pbl.value_9, pbl.value_10, pbl.value_11, pbl.value_12,
pbl.value_13, pbl.value_14, pbl.value_15
FROM pay_batch_lines pbl
WHERE pbl.batch_line_id = c_line_id;
insert_pay_batch_lines (
p_batch_id,
l_batch_line_id,
l_leftover.assignment_id,
l_leftover.assignment_number,
l_leftover.amount,
l_leftover.ffv_cost_center_id,
l_leftover.concatenated_segments,
l_leftover.segment1,
l_leftover.segment2,
l_leftover.segment3,
l_leftover.segment4,
l_leftover.segment5,
l_leftover.segment6,
l_leftover.segment7,
l_leftover.segment8,
l_leftover.segment9,
l_leftover.segment10,
l_leftover.segment11,
l_leftover.segment12,
l_leftover.segment13,
l_leftover.segment14,
l_leftover.segment15,
l_leftover.segment16,
l_leftover.segment17,
l_leftover.segment18,
l_leftover.segment19,
l_leftover.segment20,
l_leftover.segment21,
l_leftover.segment22,
l_leftover.segment23,
l_leftover.segment24,
l_leftover.segment25,
l_leftover.segment26,
l_leftover.segment27,
l_leftover.segment28,
l_leftover.segment29,
l_leftover.segment30,
l_leftover.element_type_id,
l_leftover.element_name,
l_leftover.hourly_rate,
l_leftover.locality_worked,
l_leftover.rate_code,
l_leftover.rate_multiple,
l_leftover.separate_check_flag,
l_leftover.tax_separately_flag,
0-l_leftover.hours_worked,
l_leftover.date_worked,
l_leftover.reason,
l_batch_sequence,
l_leftover.state_name,
l_leftover.county_name,
l_leftover.city_name,
l_leftover.zip_code,
l_leftover.parent_id
);
UPDATE hxt_det_hours_worked_f
SET pay_status = 'B',
last_update_date = SYSDATE,
retro_pbl_line_id_2 = retro_pbl_line_id,
retro_pbl_line_id = l_batch_line_id -- OHM180
WHERE ROWID = l_leftover.hrw_rowid;
SELECT hrw.ROWID hrw_rowid, asm.assignment_number, elt.element_name, --FORMS60
DECODE (
SIGN (
DECODE (
SIGN ( ptp.start_date
- asm.effective_start_date),
1, ptp.start_date,
asm.effective_start_date
)
- elt.effective_start_date
),
1, DECODE (
SIGN ( ptp.start_date
- asm.effective_start_date),
1, ptp.start_date,
asm.effective_start_date
),
elt.effective_start_date
)
from_date,
DECODE (
SIGN (
DECODE (
SIGN ( ptp.end_date
- asm.effective_end_date),
-1, ptp.end_date,
asm.effective_end_date
)
- elt.effective_end_date
),
-1, DECODE (
SIGN ( ptp.end_date
- asm.effective_end_date),
-1, ptp.end_date,
asm.effective_end_date
),
elt.effective_end_date
) TO_DATE,
rate_multiple, hrw.hourly_rate, hrw.ffv_cost_center_id,
/* fk - cost_center_code */
pcak.concatenated_segments, pcak.segment1, pcak.segment2,
pcak.segment3, pcak.segment4, pcak.segment5, pcak.segment6,
pcak.segment7, pcak.segment8, pcak.segment9, pcak.segment10,
pcak.segment11, pcak.segment12, pcak.segment13,
pcak.segment14, pcak.segment15, pcak.segment16,
pcak.segment17, pcak.segment18, pcak.segment19,
pcak.segment20, pcak.segment21, pcak.segment22,
pcak.segment23, pcak.segment24, pcak.segment25,
pcak.segment26, pcak.segment27, pcak.segment28,
pcak.segment29, pcak.segment30,
/*TA36 ffvl.flex_value labor_dist_code,*/
loct.location_code locality_worked, --FORMS60
ffvr.flex_value rate_code,
hrw.separate_check_flag,
hrw.fcl_tax_rule_code tax_separately_flag, hrw.amount,
hrw.hours hours_worked, hrw.element_type_id, --GLOBAL
hcl.meaning reason, --GLOBAL
hrw.date_worked, /* BSE107 */ ptp.time_period_id,
/* BSE107 */
hrw.assignment_id ,
hrw.state_name,
hrw.county_name,
hrw.city_name,
hrw.zip_code
/* BSE107 */
FROM hxt_timecards_x tim, /* SIR416 PWM 21MAR00 */
per_time_periods ptp,
hxt_det_hours_worked_f hrw,
hr_lookups hcl, --GLOBAL
per_assignments_f asm,
pay_element_types_f elt,
pay_cost_allocation_keyflex pcak,
/*TA36fnd_flex_values ffvl, */
hr_locations_all_tl loct, --FORMS60
hr_locations_no_join loc, --FORMS60
fnd_flex_values ffvr
WHERE hrw.ffv_rate_code_id = ffvr.flex_value_id(+)
AND hrw.location_id = loc.location_id(+)
--BEGIN FORMS60
AND loc.location_id = loct.location_id(+)
AND DECODE (loct.location_id, NULL, '1', loct.LANGUAGE) =
DECODE (loct.location_id, NULL, '1', USERENV ('LANG'))
--END FORMS60
/*TA36AND hrw.ffv_labor_account_id = ffvl.flex_value_id(+)*/
AND hrw.ffv_cost_center_id = pcak.cost_allocation_keyflex_id(+)
AND hrw.date_worked BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND hrw.element_type_id = elt.element_type_id
AND hrw.date_worked BETWEEN asm.effective_start_date
AND asm.effective_end_date
AND hrw.assignment_id = asm.assignment_id
AND hrw.amount IS NOT NULL
/* GLOBAL AND hrw.parent_id > 0 */
AND hrw.tim_id = tim.id
AND tim.id = p_tim_id
AND tim.time_period_id = ptp.time_period_id
--BEGIN GLOBAL
AND hrw.date_worked BETWEEN NVL (
hcl.start_date_active(+),
hrw.date_worked
)
AND NVL (
hcl.end_date_active(+),
hrw.date_worked
)
AND hcl.lookup_type(+) = 'ELE_ENTRY_REASON'
AND hcl.lookup_code(+) = hrw.fcl_earn_reason_code
--END GLOBAL
AND hrw.pay_status = 'C'
AND hrw.effective_end_date < hr_general.end_of_time; --SIR149 --FORMS60
insert_pay_batch_lines (
p_batch_id,
l_batch_line_id,
l_leftover.assignment_id,
l_leftover.assignment_number,
0
- l_leftover.amount,
l_leftover.ffv_cost_center_id,
l_leftover.concatenated_segments,
l_leftover.segment1,
l_leftover.segment2,
l_leftover.segment3,
l_leftover.segment4,
l_leftover.segment5,
l_leftover.segment6,
l_leftover.segment7,
l_leftover.segment8,
l_leftover.segment9,
l_leftover.segment10,
l_leftover.segment11,
l_leftover.segment12,
l_leftover.segment13,
l_leftover.segment14,
l_leftover.segment15,
l_leftover.segment16,
l_leftover.segment17,
l_leftover.segment18,
l_leftover.segment19,
l_leftover.segment20,
l_leftover.segment21,
l_leftover.segment22,
l_leftover.segment23,
l_leftover.segment24,
l_leftover.segment25,
l_leftover.segment26,
l_leftover.segment27,
l_leftover.segment28,
l_leftover.segment29,
l_leftover.segment30,
l_leftover.element_type_id,
l_leftover.element_name,
l_leftover.hourly_rate,
l_leftover.locality_worked,
l_leftover.rate_code,
l_leftover.rate_multiple,
l_leftover.separate_check_flag,
l_leftover.tax_separately_flag,
l_leftover.hours_worked,
l_leftover.date_worked,
l_leftover.reason,
l_batch_sequence,
l_leftover.state_name,
l_leftover.county_name,
l_leftover.city_name,
l_leftover.zip_code
);
UPDATE hxt_det_hours_worked_f
SET pay_status = 'B',
last_update_date = SYSDATE,
retro_pbl_line_id = l_batch_line_id -- OHM180
WHERE ROWID = l_leftover.hrw_rowid;
IS SELECT /*+ LEADING(det)
INDEX(det HXT_DET_HOURS_WORKED_F_TIM_FK)
INDEX(pbl PAY_BATCH_LINES_PK)
INDEX(ret HXC_RET_PAY_LATEST_DETAILS_N7) *
det.retro_pbl_line_id,
p_batch_id,
ROWIDTOCHAR(ret.ROWID)
FROM hxt_det_hours_worked_f det,
hxc_ret_pay_latest_details ret,
pay_batch_lines pbl
WHERE det.pay_status = 'B'
AND det.effective_end_date <> hr_general.end_of_time
AND det.pbl_line_id = ret.old_pbl_id
AND ret.retro_batch_id IS NULL
AND det.tim_id = p_tim_id
AND pbl.batch_id = p_batch_id
AND det.retro_pbl_line_id = pbl.batch_line_id;
IS SELECT /*+ LEADING(det)
INDEX(det HXT_DET_HOURS_WORKED_F_TIM_FK)
INDEX(pbl PAY_BATCH_LINES_PK)
INDEX(ret HXC_RET_PAY_LATEST_DETAILS_N6) *
det.retro_pbl_line_id,
p_batch_id,
ROWIDTOCHAR(ret.ROWID)
FROM hxt_det_hours_worked_f det,
hxc_ret_pay_latest_details ret,
pay_batch_lines pbl
WHERE det.pay_status = 'B'
AND det.effective_end_date <> hr_general.end_of_time
AND det.pbl_line_id = ret.pbl_id
AND ret.retro_batch_id IS NULL
AND det.tim_id = p_tim_id
AND pbl.batch_id = p_batch_id
AND det.retro_pbl_line_id = pbl.batch_line_id;
IS SELECT det.retro_pbl_line_id,
det.retro_batch_id,
det.hours,
ROWIDTOCHAR(ret.ROWID)
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret
WHERE det.tim_id = p_tim_id
AND det.effective_end_date = hr_general.end_of_time
AND sum.id = det.parent_id
AND sum.effective_end_date = hr_general.end_of_time
AND det.retro_pbl_line_id IS NOT NULL
AND det.retro_batch_id = p_batch_id
AND ret.time_building_block_id = sum.time_building_block_id
AND ret.object_version_number = sum.time_building_block_ovn
AND ret.old_attribute1 = det.element_type_id ;
IS SELECT resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
element_type_id,
attribute2,
attribute3,
hours,
object_version_number,
old_ovn,
old_measure,
old_attribute1,
old_attribute2,
old_attribute3,
pbl_id,
retro_pbl_id,
old_pbl_id,
request_id,
old_request_id,
batch_id,
retro_batch_id,
old_batch_id
FROM ( SELECT /*+ INDEX(det HXT_DET_HOURS_WORKED_F_TIM_FK)
INDEX(sum HXT_SUM_HOURS_WORKED_PK)
INDEX(ret HXC_RET_PAY_LATEST_DETAILS_FK1) *
ret.resource_id,
ret.time_building_block_id,
ret.approval_status,
ret.start_time,
ret.stop_time,
ret.org_id,
ret.business_group_id,
ret.timecard_id,
det.element_type_id,
ret.attribute2,
ret.attribute3,
det.hours,
ret.object_version_number,
NULL old_ovn,
NULL old_measure,
NULL old_attribute1,
NULL old_attribute2,
NULL old_attribute3,
det.retro_pbl_line_id pbl_id,
NULL retro_pbl_id,
NULL old_pbl_id,
FND_GLOBAL.conc_request_id request_id,
NULL old_request_id,
det.retro_batch_id batch_id,
NULL retro_batch_id,
NULL old_batch_id,
RANK() OVER (PARTITION BY ret.time_building_block_id,
ret.object_version_number
ORDER BY ret.ROWID) rank
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret
WHERE det.parent_id = sum.id
AND det.tim_id = p_tim_id
AND det.hours <> 0
AND det.retro_batch_id = p_batch_id
AND det.effective_end_date = hr_general.end_of_time
AND sum.effective_end_date = hr_general.end_of_time
AND sum.time_building_block_id = ret.time_building_block_id
AND sum.time_building_block_ovn = ret.object_version_number
AND (ret.batch_id IS NOT NULL
OR ret.retro_batch_id IS NOT NULL
OR ret.old_batch_id IS NOT NULL)
AND NOT EXISTS ( SELECT /*+ INDEX(ret2 HXC_RET_PAY_LATEST_DETAILS_FK1) *
1
FROM hxc_ret_pay_latest_details ret2
WHERE ret.time_building_block_id = ret2.time_building_block_id
AND ret.object_version_number = ret2.object_version_number
AND ret2.old_attribute1 = det.element_type_id )
)
WHERE rank = 1;
IS SELECT
ret.resource_id,
ret.time_building_block_id,
ret.approval_status,
ret.start_time,
ret.stop_time,
ret.org_id,
ret.business_group_id,
ret.timecard_id,
det.element_type_id,
ret.attribute2,
ret.attribute3,
det.hours,
ret.object_version_number,
ret.old_ovn,
ret.old_measure,
ret.old_attribute1,
ret.old_attribute2,
ret.old_attribute3,
det.retro_pbl_line_id,
ret.retro_pbl_id,
ret.old_pbl_id,
FND_GLOBAL.conc_request_id,
ret.old_request_id,
det.retro_batch_id,
ret.retro_batch_id,
ret.old_batch_id,
ROWIDTOCHAR(ret.ROWID)
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret
WHERE det.tim_id = p_tim_id
AND det.effective_end_date = hr_general.end_of_time
AND det.retro_batch_id = p_batch_id
AND det.parent_id = sum.id
AND sum.effective_end_date = hr_general.end_of_time
AND sum.time_building_block_id = ret.time_building_block_id
AND sum.time_building_block_ovn = ret.object_version_number
AND det.retro_pbl_line_id IS NOT NULL
AND ret.pbl_id IS NULL
AND ret.old_pbl_id IS NULL;
UPDATE hxc_ret_pay_latest_details
SET retro_batch_id = ret_batch_tab(i),
retro_pbl_id = ret_pbl_tab(i),
request_id = FND_GLOBAL.conc_request_id
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_ret_pay_latest_details
SET retro_batch_id = ret_batch_tab(i),
retro_pbl_id = ret_pbl_tab(i),
old_pbl_id = pbl_id,
old_batch_id = batch_id,
old_request_id = request_id,
old_measure = measure,
old_attribute1 = attribute1,
old_attribute2 = attribute2,
attribute1 = NULL,
attribute2 = NULL,
measure = NULL,
pbl_id = NULL,
request_id = FND_GLOBAL.conc_request_id,
batch_id = NULL
WHERE ROWID = CHARTOROWID(rowtab(i));
UPDATE hxc_ret_pay_latest_details
SET batch_id = ret_batch_tab(i),
pbl_id = ret_pbl_tab(i),
attribute1 = old_attribute1,
measure = hrs_tab(i),
request_id = fnd_global.conc_request_id
WHERE ROWID = CHARTOROWID(rowtab(i));
INSERT INTO hxc_ret_pay_latest_details
( resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure,
object_version_number,
old_ovn,
old_measure,
old_attribute1,
old_attribute2,
old_attribute3,
pbl_id,
retro_pbl_id,
old_pbl_id,
request_id,
old_request_id,
batch_id,
retro_batch_id,
old_batch_id)
VALUES ( resource_id_tab(i),
time_building_block_id_tab(i),
approval_status_tab(i),
start_time_tab(i),
stop_time_tab(i),
org_id_tab(i),
business_group_id_tab(i),
timecard_id_tab(i),
attribute1_tab(i),
attribute2_tab(i),
attribute3_tab(i),
measure_tab(i),
object_version_number_tab(i),
old_ovn_tab(i),
old_measure_tab(i),
old_attribute1_tab(i),
old_attribute2_tab(i),
old_attribute3_tab(i),
pbl_id_tab(i),
retro_pbl_id_tab(i),
old_pbl_id_tab(i),
request_id_tab(i),
old_request_id_tab(i),
batch_id_tab(i),
retro_batch_id_tab(i),
old_batch_id_tab(i));
DELETE FROM hxc_ret_pay_latest_details
WHERE ROWID = CHARTOROWID(rowid_tab(i));
INSERT INTO hxc_ret_pay_latest_details
( resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure,
object_version_number,
old_ovn,
old_measure,
old_attribute1,
old_attribute2,
old_attribute3,
pbl_id,
retro_pbl_id,
old_pbl_id,
request_id,
old_request_id,
batch_id,
retro_batch_id,
old_batch_id)
VALUES ( resource_id_tab(i),
time_building_block_id_tab(i),
approval_status_tab(i),
start_time_tab(i),
stop_time_tab(i),
org_id_tab(i),
business_group_id_tab(i),
timecard_id_tab(i),
attribute1_tab(i),
attribute2_tab(i),
attribute3_tab(i),
measure_tab(i),
object_version_number_tab(i),
old_ovn_tab(i),
old_measure_tab(i),
old_attribute1_tab(i),
old_attribute2_tab(i),
old_attribute3_tab(i),
pbl_id_tab(i),
retro_pbl_id_tab(i),
old_pbl_id_tab(i),
request_id_tab(i),
old_request_id_tab(i),
batch_id_tab(i),
retro_batch_id_tab(i),
old_batch_id_tab(i));
IS SELECT ROWIDTOCHAR(ret.rowid),
det.retro_pbl_line_id
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
pay_batch_lines pbl,
hxc_ret_pay_latest_details ret
WHERE pbl.batch_id = p_batch_id
AND pbl.batch_line_id = det.retro_pbl_line_id
AND det.tim_id = p_tim_id
AND det.pay_status = 'B'
AND det.parent_id = sum.id
AND sum.time_building_block_id = ret.time_building_block_id
AND ret.object_version_number = sum.time_building_block_ovn
AND sum.effective_start_date <= det.effective_start_date
AND sum.effective_end_date >= det.effective_end_date
AND det.element_type_id = ret.attribute1;
IS SELECT ROWIDTOCHAR(ret.rowid),
det.retro_pbl_line_id
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
pay_batch_lines pbl,
hxc_ret_pay_latest_details ret
WHERE pbl.batch_id = p_batch_id
AND pbl.batch_line_id = det.retro_pbl_line_id
AND det.tim_id = p_tim_id
AND det.pay_status = 'B'
AND det.parent_id = sum.id
AND sum.time_building_block_id = ret.time_building_block_id
AND ret.old_ovn = sum.time_building_block_ovn
AND sum.effective_start_date <= det.effective_start_date
AND sum.effective_end_date >= det.effective_end_date
AND det.element_type_id = ret.old_attribute1
AND ret.batch_id = p_batch_id;
IS SELECT ROWIDTOCHAR(ret.rowid)
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret
WHERE det.retro_batch_id = p_batch_id
AND det.pay_status = 'C'
AND det.tim_id = p_tim_id
AND det.effective_start_date >= sum.effective_start_date
AND det.effective_end_date <= sum.effective_end_date
AND det.parent_id = sum.id
AND sum.time_building_block_id = ret.time_building_block_id
AND sum.time_building_block_ovn = ret.object_version_number
AND det.element_type_id = ret.old_attribute1
AND ret.batch_id = p_batch_id
AND ret.pbl_id IS NULL
AND ret.request_id <> FND_GLOBAL.conc_request_id ;
IS SELECT DISTINCT
ret.resource_id,
ret.time_building_block_id,
ret.approval_status,
ret.start_time,
ret.stop_time,
ret.org_id,
ret.business_group_id,
ret.timecard_id,
det.element_type_id,
ret.attribute2,
ret.attribute3,
det.hours,
ret.object_version_number,
NULL old_ovn,
NULL old_measure,
NULL old_attribute1,
NULL old_attribute2,
NULL old_attribute3,
det.retro_pbl_line_id pbl_id,
NULL retro_pbl_id,
NULL old_pbl_id,
FND_GLOBAL.conc_request_id request_id,
NULL old_request_id,
det.retro_batch_id batch_id,
NULL retro_batch_id,
NULL old_batch_id,
ret.application_set_id
FROM hxt_det_hours_worked_f det,
hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret,
pay_batch_lines pbl
WHERE det.retro_batch_id = p_batch_id
AND det.retro_pbl_line_id IS NOT NULL
AND det.pay_status = 'C'
AND det.tim_id = p_tim_id
AND det.parent_id = sum.id
AND det.effective_start_date >= sum.effective_start_date
AND det.effective_end_date <= sum.effective_end_date
AND sum.time_building_block_id = ret.time_building_block_id
AND sum.time_building_block_ovn = ret.object_version_number
AND pbl.batch_id = p_batch_id
AND pbl.batch_line_id = det.retro_pbl_line_id;
CURSOR get_details_to_delete
IS SELECT ROWIDTOCHAR(ret.rowid),
ret.time_building_block_id,
ret.object_version_number,
ret.request_id,
ret.measure,
ret.attribute1,
ret.attribute2,
ret.attribute3
FROM hxt_sum_hours_worked_f sum,
hxc_ret_pay_latest_details ret
WHERE sum.tim_id = p_tim_id
AND ret.time_building_block_id = sum.time_building_block_id
AND ret.batch_id = p_batch_id
AND ret.request_id <> FND_GLOBAL.conc_request_id
AND ret.pbl_id IS NULL;
UPDATE hxc_ret_pay_latest_details
SET retro_batch_id = p_batch_id,
retro_pbl_id = l_line_tab(i),
old_batch_id = batch_id,
old_pbl_id = pbl_id,
old_request_id = request_id,
old_attribute1= attribute1,
old_measure = measure,
request_id = FND_global.conc_request_id,
attribute1 = NULL,
attribute2 = NULL,
measure = NULL,
pbl_id = NULL,
batch_id = NULL
WHERE ROWID = CHARTOROWID(l_rowidtab(i));
UPDATE hxc_ret_pay_latest_details
SET retro_batch_id = p_batch_id,
retro_pbl_id = l_line_tab(i),
hxt_request_id = request_id,
hxt_batch_id = p_batch_id,
request_id = FND_global.conc_request_id,
hxt_measure = measure,
measure = NULL,
batch_id = NULL,
hxt_element_id = attribute1,
hxt_attribute2 = attribute2,
hxt_attribute3 = attribute3,
attribute1 = NULL,
attribute2 = NULL,
attribute3 = NULL
WHERE ROWID = CHARTOROWID(l_rowidtab(i));
UPDATE hxc_ret_pay_latest_details
SET hxt_element_id = attribute1,
hxt_attribute2 = attribute2,
hxt_measure = measure,
hxt_request_id = request_id,
hxt_batch_id = batch_id,
attribute1 = old_attribute1,
measure = old_measure,
batch_id = old_batch_id,
pbl_id = old_pbl_id,
request_id = old_request_id,
old_attribute1 = NULL,
old_measure = NULL,
old_batch_id = NULL,
old_pbl_id = NULL,
old_request_id = NULL
WHERE ROWID = CHARTOROWID(l_rowidtab(i));
INSERT INTO hxc_ret_pay_latest_details
( resource_id,
time_building_block_id,
approval_status,
start_time,
stop_time,
org_id,
business_group_id,
timecard_id,
attribute1,
attribute2,
attribute3,
measure,
object_version_number,
old_ovn,
old_measure,
old_attribute1,
old_attribute2,
old_attribute3,
pbl_id,
retro_pbl_id,
old_pbl_id,
request_id,
old_request_id,
batch_id,
retro_batch_id,
old_batch_id,
application_set_id
)
VALUES ( resource_id_tab(i),
time_building_block_id_tab(i),
approval_status_tab(i),
start_time_tab(i),
stop_time_tab(i),
org_id_tab(i),
business_group_id_tab(i),
timecard_id_tab(i),
attribute1_tab(i),
attribute2_tab(i),
attribute3_tab(i),
batch_line_hours(pbl_id_tab(i)),
object_version_number_tab(i),
NULL,
NULL,
NULL,
NULL,
NULL,
pbl_id_tab(i),
NULL,
NULL,
FND_GLOBAL.conc_request_id,
NULL,
p_batch_id,
NULL,
NULL,
app_set_id_tab(i)
);
DELETE FROM hxc_ret_pay_latest_details
WHERE batch_id = p_batch_id
AND request_id <> FND_GLOBAL.conc_request_id
AND pbl_id IS NULL
RETURNING time_building_block_id,
object_version_number,
request_id,
measure,
attribute1,
attribute2,
attribute3
BULK COLLECT INTO time_building_block_id_tab,
object_version_number_tab,
hxt_reqtab,
hxt_measuretab,
hxt_att1tab,
attribute2_tab,
attribute3_tab
;
OPEN get_details_to_delete;
FETCH get_details_to_delete BULK COLLECT INTO l_rowidtab,
time_building_block_id_tab,
object_version_number_tab,
hxt_reqtab,
hxt_measuretab,
hxt_att1tab,
attribute2_tab,
attribute3_tab;
CLOSE get_details_to_delete;
DELETE FROM hxc_ret_pay_latest_details
WHERE rowid = CHARTOROWID(l_rowidtab(i));
hr_utility.trace('About to update all the orig details into the new records. ');
UPDATE hxc_ret_pay_latest_details
SET hxt_request_id = hxt_reqtab(i),
hxt_measure = hxt_measuretab(i),
hxt_element_id = hxt_att1tab(i),
hxt_attribute2 = attribute2_tab(i),
hxt_attribute3 = attribute3_tab(i),
hxt_batch_id = p_batch_id
WHERE time_building_block_id = time_building_block_id_tab(i)
AND object_version_number = object_version_number_tab(i);