[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rule_mode
FROM pay_legislation_rules
WHERE legislation_code = 'US'
and rule_type = 'S';
SELECT target.SEGMENT4
FROM /* route for SCL keyflex - assignment level */
hr_soft_coding_keyflex target,
per_all_assignments_f ASSIGN
WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND ASSIGN.assignment_id = p_assignment_id
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND target.enabled_flag = 'Y'
AND target.id_flex_num = p_id_flex_num;
select put.user_table_id
,put.user_table_name
from hr_organization_information hoi
,pay_user_tables put
where hoi.organization_id = p_bg_id
and hoi.org_information_context ='Work Schedule'
and hoi.org_information1 = put.user_table_id ;
select PUC.user_column_id
,PUC.user_column_name
from pay_user_tables PUT,
pay_user_columns PUC
where PUC.USER_COLUMN_ID = p_user_col_id
and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
and NVL(PUC.legislation_code,'US') = 'US'
and PUC.user_table_id = PUT.user_table_id
and PUT.user_table_id = p_user_table_id;
select user_row_id
from pay_user_rows_f
where user_table_id = p_user_table_id
and p_date_earned between effective_start_date and effective_end_date ;
select value
from pay_user_column_instances_f
where user_row_id = p_user_row_id
and user_column_id = p_user_col_id
and NVL(business_group_id, p_bg_id) = p_bg_id
and NVL(legislation_code, 'US') = 'US'
and p_date_earned between effective_start_date and effective_end_date;
select legislation_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
SELECT peef.element_entry_id
FROM pay_element_entries_f peef
,pay_element_types_f pet
where peef.element_type_id = pet.element_type_id
and peef.assignment_id = cp_assignment_id
and pet.element_name = 'Regular Salary'
and pet.legislation_code = cp_leg_code
and pet.business_group_id IS NULL
-- Added for #14067207 Start
and peef.creator_type IN ('H','P','SP'
,'F','M','S'
,'A','D','DF'
,'R','EE','RR'
,'AD','AE','PR'
,'NR','FL')
and peef.entry_type IN ('E','S','D')
and cp_effective_date between peef.effective_start_date
and peef.effective_end_date;
SELECT petei.eei_information10
FROM pay_element_entries_f peef
,pay_element_types_f pet
,pay_element_type_extra_info petei
where peef.element_entry_id = cp_element_entry_id
and peef.assignment_id = cp_assignment_id
and peef.element_type_id = pet.element_type_id
and pet.business_group_id = cp_bg_id
and pet.legislation_code IS NULL
and pet.element_type_id = petei.element_type_id
and petei.information_type = 'US_EARNINGS'
and petei.eei_information_category = 'US_EARNINGS';
SELECT pet.business_group_id
,pet.legislation_code
,pec.classification_name
FROM pay_element_entries_f peef
,pay_element_types_f pet
,pay_element_classifications pec
WHERE peef.element_entry_id = cp_element_entry_id
and peef.assignment_id = cp_assignment_id
and peef.element_type_id = pet.element_type_id
and peef.effective_start_date >= pet.effective_start_date
and peef.effective_end_date <= pet.effective_end_date
and pet.classification_id = pec.classification_id
and pec.legislation_code = cp_leg_code
and pec.business_group_id IS NULL;
SELECT hr_general.decode_lookup('FREQUENCY', ASSIGN.frequency)
,ASSIGN.normal_hours
FROM per_all_assignments_f ASSIGN
where cp_date_earned
BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = cp_assignment_id
and UPPER(ASSIGN.frequency) = 'W';
SELECT lookup_code
FROM hr_lookups lkp
WHERE lkp.application_id = 800
AND lkp.lookup_type = 'PAY_BASIS'
AND lkp.lookup_code = p_freq;
SELECT lookup_code
FROM hr_lookups lkp
WHERE lkp.application_id = 800
AND lkp.lookup_type = 'PAY_BASIS'
AND lkp.meaning = p_freq;
SELECT TPT.number_per_fiscal_year
INTO v_periods_per_fiscal_yr
FROM pay_payrolls_f PPF,
per_time_period_types TPT,
fnd_sessions fs
WHERE PPF.payroll_id = p_payroll_id
AND fs.session_id = USERENV('SESSIONID')
AND fs.effective_date between PPF.effective_start_date
and PPF.effective_end_date
AND TPT.period_type = PPF.period_type;
SELECT TPT.number_per_fiscal_year
INTO v_annualizing_factor
FROM pay_payrolls_f PRL,
per_time_period_types TPT,
fnd_sessions fs
WHERE TPT.period_type = PRL.period_type
and fs.session_id = USERENV('SESSIONID')
and fs.effective_date BETWEEN PRL.effective_start_date
AND PRL.effective_end_date
AND PRL.payroll_id = p_payroll_id
AND PRL.business_group_id + 0 = p_bg;
END; /* SELECT LOOKUP CODE */
SELECT TPT.number_per_fiscal_year
INTO v_annualizing_factor
FROM pay_payrolls_f PRL,
per_time_period_types TPT,
fnd_sessions fs
WHERE TPT.period_type = PRL.period_type
AND fs.session_id = USERENV('SESSIONID')
AND fs.effective_date BETWEEN PRL.effective_start_date
AND PRL.effective_end_date
AND PRL.payroll_id = p_payroll_id
AND PRL.business_group_id + 0 = p_bg;
SELECT TPT.number_per_fiscal_year
INTO v_periods_per_fiscal_yr
FROM pay_payrolls_f ppf,
per_time_period_types tpt,
fnd_sessions fs
WHERE ppf.payroll_id = p_payroll_id
AND fs.session_id = USERENV('SESSIONID')
AND fs.effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND tpt.period_type = ppf.period_type;
hr_utility.trace(' selecting from per_time_period_types');
SELECT PT.number_per_fiscal_year
INTO v_annualizing_factor
FROM per_time_period_types PT
WHERE UPPER(PT.period_type) = UPPER(p_freq);
select nvl(ppf.prl_information2,'NOT ENTERED')
into v_calc_type
from pay_payrolls_f ppf
where payroll_id = p_payroll_id
and v_stnd_start_date between ppf.effective_start_date
and ppf.effective_end_Date;
SELECT ASG.effective_start_date,
ASG.effective_end_date,
NVL(ASG.normal_hours, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date > p_period_start
AND ASG.effective_end_date <= p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
SELECT user_column_name
INTO v_ws_name
FROM pay_user_columns
WHERE user_column_id = v_ws_id
AND NVL(business_group_id, p_bg_id) = p_bg_id
AND NVL(legislation_code,'US') = 'US';
SELECT EEV.screen_entry_value,
EEV.effective_start_date,
EEV.effective_end_date
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_element_entry_id
AND EEV.input_value_id = p_inpval_id
AND EEV.effective_start_date > p_range_start
AND EEV.effective_end_date <= p_range_end
ORDER BY EEV.effective_start_date;
SELECT EEV.screen_entry_value,
GREATEST(EEV.effective_start_date, p_range_start_date),
EEV.effective_end_date
INTO v_earnings_entry,
v_entry_start,
v_entry_end
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_element_entry_id
AND EEV.input_value_id = p_inpval_id
AND EEV.effective_start_date <= p_range_start_date
AND EEV.effective_end_date >= p_range_start_date
AND EEV.effective_end_date < p_range_end_date;
hr_utility.trace('Select EEV3');
SELECT EEV.screen_entry_value,
EEV.effective_start_date,
LEAST(EEV.effective_end_date, p_range_end_date)
INTO v_earnings_entry,
v_entry_start,
v_entry_end
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_element_entry_id
AND EEV.input_value_id = p_inpval_id
AND EEV.effective_start_date > p_range_start_date
AND EEV.effective_start_date <= p_range_end_date
AND EEV.effective_end_date > p_range_end_date;
select fnd_number.canonical_to_number(pev.screen_entry_value)
from per_absence_attendance_types abt,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pev.input_value_id = abt.input_value_id
and abt.absence_category = 'V'
and v_eff_date between pev.effective_start_date
and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = v_asg_id
and v_eff_date between pee.effective_start_date
and pee.effective_end_date;
select fnd_number.canonical_to_number(pev.screen_entry_value)
from per_absence_attendance_types abt,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pev.input_value_id = abt.input_value_id
and abt.absence_category = 'S'
and v_eff_date between pev.effective_start_date
and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = v_asg_id
and v_eff_date between pee.effective_start_date
and pee.effective_end_date;
SELECT PYB.input_value_id,
FCL.meaning
INTO v_inpval_id,
v_pay_basis
FROM per_assignments_f ASG,
per_pay_bases PYB,
hr_lookups FCL
WHERE FCL.lookup_code = PYB.pay_basis
AND FCL.lookup_type = 'PAY_BASIS'
AND FCL.application_id = 800
AND PYB.pay_basis_id = ASG.pay_basis_id
AND ASG.assignment_id = p_asst_id
AND p_date_earned BETWEEN ASG.effective_start_date
AND ASG.effective_end_date;
SELECT IPV.input_value_id
INTO v_inpval_id
FROM pay_input_values_f IPV,
pay_element_types_f ELT
WHERE ELT.element_name = 'Regular Wages'
and p_period_start BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
and ELT.element_type_id = IPV.element_type_id
and p_period_start BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
and IPV.name = 'Rate'
and ELT.legislation_code = g_legislation_code;
SELECT IPV.input_value_id
INTO v_inpval_id
FROM pay_input_values_f IPV,
pay_element_types_f ELT
WHERE ELT.element_name = 'Regular Wages'
and p_period_start BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
and ELT.element_type_id = IPV.element_type_id
and p_period_start BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
and IPV.name = 'Rate Code'
and ELT.legislation_code = g_legislation_code;
SELECT IPV.input_value_id
INTO v_inpval_id
FROM pay_input_values_f IPV,
pay_element_types_f ELT
WHERE ELT.element_name = 'Regular Salary'
and p_period_start BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
and ELT.element_type_id = IPV.element_type_id
and p_period_start BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
and IPV.name = 'Monthly Salary'
and ELT.legislation_code = g_legislation_code;
SELECT TPT.number_per_fiscal_year
INTO v_pay_periods_per_year
FROM pay_payrolls_f PRL,
per_time_period_types TPT
WHERE TPT.period_type = PRL.period_type
AND p_period_end between PRL.effective_start_date
and PRL.effective_end_date
AND PRL.payroll_id = p_payroll_id
AND PRL.business_group_id + 0 = p_bus_grp_id;
SELECT user_column_name
INTO v_work_sched_name
FROM pay_user_columns
WHERE user_column_id = v_ws_id
AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
AND NVL(legislation_code,'US') = 'US';
select 1 INTO l_asg_info_changes
from dual
where exists (
SELECT 1
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL
WHERE ASG.assignment_id = p_asst_id
AND ASG.effective_start_date <= p_period_start
AND ASG.effective_end_date >= p_period_start
AND ASG.effective_end_date < p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y' );
select 1 INTO l_eev_info_changes
from dual
where exists (
SELECT 1
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id+0 = v_inpval_id
AND ( ( EEV.effective_start_date <= p_period_start
AND EEV.effective_end_date >= p_period_start
AND EEV.effective_end_date < p_period_end)
OR ( EEV.effective_start_date between p_period_start and p_period_end)
) );
select 1 INTO l_asg_info_changes
from dual
where exists (
SELECT 1
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL
WHERE ASG.assignment_id = p_asst_id
AND ASG.effective_start_date > p_period_start
AND ASG.effective_start_date <= p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y');
select 1 INTO l_eev_info_changes
from dual
where exists (
SELECT 1
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id+0 = v_inpval_id
AND ( ( EEV.effective_start_date <= p_period_start
AND EEV.effective_end_date >= p_period_start
AND EEV.effective_end_date < p_period_end)
--OR ( EEV.effective_start_date between p_period_start and p_period_end)
) );
select 1 INTO l_eev_info_changes
from dual
where exists (
SELECT 1
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id+0 = v_inpval_id
AND EEV.effective_start_date <= p_period_start
AND EEV.effective_end_date >= p_period_start
AND EEV.effective_end_date < p_period_end);
SELECT parameter_value
INTO l_proc_reg_sal
FROM pay_action_parameters
WHERE parameter_name = 'PROC_REG_SAL_INACT';
SELECT AST.per_system_status
INTO v_asg_status
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL
WHERE ASG.assignment_id = p_asst_id
AND p_period_start BETWEEN ASG.effective_start_date
AND ASG.effective_end_date
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y';
SELECT GREATEST(ASG.effective_start_date, p_period_start),
ASG.effective_end_date,
NVL(ASG.NORMAL_HOURS, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
v_work_schedule
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date <= p_period_start
AND ASG.effective_end_date >= p_period_start
AND ASG.effective_end_date < p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
hr_utility.trace('ONLY ASG , select MULTIASG');
OPEN get_asst_chgs; -- SELECT (ASG2 MULTIASG)
hr_utility.trace('ONLY ASG , select END_SPAN_RECORD');
SELECT ASG.effective_start_date,
LEAST(ASG.effective_end_date, p_period_end),
NVL(ASG.normal_hours, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
v_work_schedule
FROM hr_soft_coding_keyflex SCL,
per_assignment_status_types AST,
per_assignments_f ASG,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date > p_period_start
AND ASG.effective_start_date <= p_period_end
AND ASG.effective_end_date > p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
INTO v_earnings_entry
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND p_period_start between EEV.effective_start_date
AND EEV.effective_end_date;
SELECT GREATEST(ASG.effective_start_date, p_period_start),
ASG.effective_end_date,
NVL(ASG.NORMAL_HOURS, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
v_work_schedule
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date <= p_period_start
AND ASG.effective_end_date >= p_period_start
AND ASG.effective_end_date < p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
hr_utility.trace('Select app. EEVMPE again after range is determined');
SELECT COUNT(EEV.element_entry_value_id)
INTO l_eev_info_changes
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND EEV.effective_start_date <= v_range_start
AND EEV.effective_end_date >= v_range_start
AND EEV.effective_end_date < v_range_end;
SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
INTO v_earnings_entry
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND v_range_end BETWEEN EEV.effective_start_date
AND EEV.effective_end_date;
hr_utility.trace(' BOTH ASG - SELECT ASG_MULTI_WITHIN');
OPEN get_asst_chgs; -- SELECT ( ASG_MULTI_WITHIN)
hr_utility.trace('BOTH ASG MULTI select app. EEVMPE again after range det.');
SELECT COUNT(EEV.element_entry_value_id)
INTO l_eev_info_changes
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND EEV.effective_start_date <= v_range_start
AND EEV.effective_end_date >= v_range_start
AND EEV.effective_end_date < v_range_end;
SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
INTO v_earnings_entry
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND v_range_end BETWEEN EEV.effective_start_date
AND EEV.effective_end_date;
hr_utility.trace('BOTH ASG SELECT END_SPAN_RECORD');
SELECT ASG.effective_start_date,
LEAST(ASG.effective_end_date, p_period_end),
NVL(ASG.normal_hours, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
v_work_schedule
FROM hr_soft_coding_keyflex SCL,
per_assignment_status_types AST,
per_assignments_f ASG,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date > p_period_start
AND ASG.effective_start_date <= p_period_end
AND ASG.effective_end_date > p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
hr_utility.trace('SELECT EEVMPE');
SELECT COUNT(EEV.element_entry_value_id)
INTO l_eev_info_changes
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND EEV.effective_start_date <= v_range_start
AND EEV.effective_end_date >= v_range_start
AND EEV.effective_end_date < v_range_end;
hr_utility.trace('BOTH ASG SPAN - SELECT EEV_FOR_CURR_RANGE_END');
SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
INTO v_earnings_entry
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND v_range_end BETWEEN EEV.effective_start_date
AND EEV.effective_end_date;
select start_date, end_date
from per_time_periods pt
where payroll_id = l_payroll_id
and l_date_earned between start_date and end_date;
select /*hr_general.decode_lookup('PAY_BASIS',BASES.pay_basis)*/
BASES.pay_basis
, INPUTV.input_value_id
from
per_all_assignments_f ASSIGN
, per_pay_bases BASES
, pay_input_values_f INPUTV
, pay_element_types_f ETYPE
, pay_rates RATE
where l_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = l_assignment_id
and BASES.pay_basis_id (+)= ASSIGN.pay_basis_id
and INPUTV.input_value_id (+)= BASES.input_value_id
and l_date_earned between nvl (INPUTV.effective_start_date, l_date_earned)
and nvl (INPUTV.effective_end_date, l_date_earned)
and ETYPE.element_type_id (+)= INPUTV.element_type_id
and RATE.rate_id (+)= BASES.rate_id
and l_date_earned between nvl (ETYPE.effective_start_date, l_date_earned)
and nvl (ETYPE.effective_end_date, l_date_earned) ;
select fnd_number.canonical_to_number (EEV.screen_entry_value)
from pay_element_entries_f EE
, pay_element_entry_values_f EEV
where EEV.input_value_id = l_input_value_id
and l_date_earned BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
and EE.assignment_id = l_assignment_id
and EE.entry_type = 'E'
and l_date_earned BETWEEN EE.effective_start_date
AND EE.effective_end_date
and EEV.element_entry_id = EE.element_entry_id;
select actual_termination_date
from per_assignments_f paf,
per_periods_of_service pps
where paf.assignment_id = l_assignment_id
and l_date_earned between paf.effective_start_date and
paf.effective_end_date
and paf.PERIOD_OF_SERVICE_ID = pps.period_of_service_id;
SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
(SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
INTO p_duration
FROM DUAL;
SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
(SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
INTO p_duration
FROM DUAL;
SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
INTO p_duration
FROM DUAL;
SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
(SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
INTO p_duration
FROM DUAL;
SELECT hr_general.decode_lookup('FREQUENCY', ASSIGN.frequency)
,ASSIGN.normal_hours
FROM per_all_assignments_f ASSIGN
where date_earned
BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = p_assignment_id;
select ptp.start_date
,ptp.end_date
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and p_date_earned between ptp.start_date and ptp.end_date;
select pds.actual_termination_date
,pds.last_standard_process_date
,pds.final_process_date
from per_periods_of_service PDS,
per_assignments_f ASS
WHERE PDS.actual_termination_date <= p_date_earned
AND PDS.period_of_service_id = ASS.period_of_service_id
AND p_date_earned BETWEEN ASS.effective_start_date
AND ASS.effective_end_date
AND ASS.primary_flag = 'Y'
AND ASS.assignment_id = p_assignment_id;
SELECT min(ppa_run.date_earned)
FROM pay_payroll_actions ppa_run,
pay_assignment_actions paa_run
WHERE ppa_run.date_earned >= p_atd
AND ppa_run.action_status = 'C'
AND ppa_run.action_type in ('Q','R','B','I')
AND ((nvl(paa_run.run_type_id, ppa_run.run_type_id) is null and
paa_run.source_action_id is null) or
(nvl(paa_run.run_type_id, ppa_run.run_type_id) is not null and
paa_run.source_action_id is not null))
AND ppa_run.payroll_action_id = paa_run.payroll_action_id
AND paa_run.action_status = 'C'
AND paa_run.assignment_id = p_assignment_id
AND NOT EXISTS (
SELECT 1
FROM pay_payroll_actions ppa_rev,
pay_assignment_actions paa_rev,
pay_action_interlocks pai
WHERE pai.locked_Action_id = paa_run.assignment_action_id
AND pai.locking_action_id = paa_rev.assignment_action_id
AND ppa_rev.payroll_action_id = paa_rev.payroll_action_id
AND ppa_rev.action_type = 'V');
SELECT min(ppa_run.date_earned)
FROM pay_payroll_actions ppa_run,
pay_assignment_actions paa_run
WHERE ppa_run.date_earned >= p_lspd
AND ppa_run.action_status = 'C'
AND ppa_run.action_type in ('Q','R','B','I')
AND ((nvl(paa_run.run_type_id, ppa_run.run_type_id) is null and
paa_run.source_action_id is null) or
(nvl(paa_run.run_type_id, ppa_run.run_type_id) is not null and
paa_run.source_action_id is not null))
AND ppa_run.payroll_action_id = paa_run.payroll_action_id
AND paa_run.action_status = 'C'
AND paa_run.assignment_id = p_assignment_id
AND NOT EXISTS (
SELECT 1
FROM pay_payroll_actions ppa_rev,
pay_assignment_actions paa_rev,
pay_action_interlocks pai
WHERE pai.locked_Action_id = paa_run.assignment_action_id
AND pai.locking_action_id = paa_rev.assignment_action_id
AND ppa_rev.payroll_action_id = paa_rev.payroll_action_id
AND ppa_rev.action_type = 'V');
SELECT hr_general.decode_lookup('FREQUENCY', ASSIGN.frequency)
,ASSIGN.normal_hours
FROM per_all_assignments_f ASSIGN
where date_earned
BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = p_assignment_id;
select fnd_number.canonical_to_number(pev.screen_entry_value)
from per_absence_attendance_types abt,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pev.input_value_id = abt.input_value_id
and abt.absence_category = 'S'
and v_eff_date between pev.effective_start_date
and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = v_asg_id
and v_eff_date between pee.effective_start_date
and pee.effective_end_date;
select distinct pee.element_entry_id
,abs.absence_attendance_id
,abs.date_start
,abs.date_end
,fnd_number.canonical_to_number(peev.screen_entry_value)
from per_absence_attendance_types abt
,per_absence_attendances abs
,pay_element_entries_f pee
,pay_element_entry_values_f peev
where abt.input_value_id = peev.input_value_id
and abt.absence_category = 'S'
and abt.absence_attendance_type_id = abs.absence_attendance_type_id
and peev.element_entry_id = pee.element_entry_id
and pee.creator_id = abs.absence_attendance_id
and pee.creator_type = 'A'
and abs.date_start between v_st_date and v_eff_date
and pee.assignment_id = v_asg_id
and v_eff_date between peev.effective_start_date and peev.effective_end_date
and v_eff_date between pee.effective_start_date and pee.effective_end_date;
select fnd_number.canonical_to_number(pev.screen_entry_value)
from per_absence_attendance_types abt,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pev.input_value_id = abt.input_value_id
and abt.absence_category = 'V'
and v_eff_date between pev.effective_start_date
and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = v_asg_id
and v_eff_date between pee.effective_start_date
and pee.effective_end_date;
select distinct pee.element_entry_id
,abs.absence_attendance_id
,abs.date_start
,abs.date_end
,fnd_number.canonical_to_number(peev.screen_entry_value)
from per_absence_attendance_types abt
,per_absence_attendances abs
,pay_element_entries_f pee
,pay_element_entry_values_f peev
where abt.input_value_id = peev.input_value_id
and abt.absence_category = 'V'
and abt.absence_attendance_type_id = abs.absence_attendance_type_id
and peev.element_entry_id = pee.element_entry_id
and pee.creator_id = abs.absence_attendance_id
and pee.creator_type = 'A'
and abs.date_start between v_st_date and v_eff_date
and pee.assignment_id = v_asg_id
and v_eff_date between peev.effective_start_date and peev.effective_end_date
and v_eff_date between pee.effective_start_date and pee.effective_end_date;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'PROC_REG_SAL_INACT';