[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
)
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;
SELECT 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,
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
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,
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(+)
--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 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,
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 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
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
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
);
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;
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);
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
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
);
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;
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;