[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT legislation_code
INTO l_legislation_code_l
FROM per_business_groups
WHERE business_group_id =p_business_group_id;
SELECT fuel_type
INTO l_fuel_type
FROM pqp_vehicle_details
WHERE vehicle_details_id=p_veh_id;
SELECT pur.row_low_range_or_name
FROM pay_user_rows_f pur,pay_user_tables put
WHERE pur.user_table_id = put.user_table_id
AND put.user_table_name = p_table_name
AND (put.business_group_id = p_business_group_id
OR put.legislation_code IS NOT NULL)
AND in_claim_date BETWEEN NVL(pur.effective_start_date,in_claim_date)
AND NVL(pur.effective_end_date,in_claim_date)
ORDER BY pur.row_low_range_or_name;
SELECT puc.user_column_name
FROM pay_user_columns puc
,pay_user_tables put
,pay_user_column_instances_f puci
WHERE puc.user_table_id = put.user_table_id
AND put.user_table_name = p_table_name
AND puc.user_column_id = puci.user_column_id
AND (put.business_group_id = p_business_group_id
OR put.legislation_code IS NOT NULL)
AND (puci.business_group_id = p_business_group_id
OR puci.legislation_code IS NOT NULL)
AND puci.value IS NOT NULL
AND in_claim_date BETWEEN NVL(puci.effective_start_date,in_claim_date)
AND NVL(puci.effective_end_date,in_claim_date)
ORDER BY puc.user_column_name;
SELECT PCV_INFORMATION13 info
FROM pqp_configuration_values pcv
WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
AND (pcv.business_group_id=p_business_group_id OR
( legislation_code = 'GB' AND
NOT EXISTS
(SELECT 'X' from pqp_configuration_values pcv
WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
AND pcv.business_group_id=p_business_group_id ))) ;
SELECT PCV_INFORMATION14 info
FROM pqp_configuration_values pcv
WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
AND (pcv.business_group_id=p_business_group_id OR
( legislation_code = 'GB' AND
NOT EXISTS
(SELECT 'X' from pqp_configuration_values pcv
WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
AND pcv.business_group_id=p_business_group_id ))) ;
SELECT PCV_INFORMATION15 info
FROM pqp_configuration_values pcv
WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
AND (pcv.business_group_id=p_business_group_id OR
( legislation_code = 'GB' AND
NOT EXISTS
(SELECT 'X' from pqp_configuration_values pcv
WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
AND pcv.business_group_id=p_business_group_id ))) ;
SELECT pcv.pcv_information5 rates_type
FROM pqp_configuration_values pcv
WHERE business_group_id=p_business_group_id
AND pcv.pcv_information_category='GB_VEHICLE_CALC_INFO'
AND (pcv_information2=cp_usage_type
OR pcv_information2 IS NULL)
AND (pcv_information3=cp_vehicle_type
OR pcv_information3 IS NULL)
AND (pcv_information4=cp_fuel_type
OR pcv_information4 IS NULL)
AND pcv.pcv_information5 IS NOT NULL ; */
SELECT pcv.aat_information5 rates_type
FROM pqp_assignment_attributes_f pcv
WHERE rownum=1;
SELECT pva.company_car_calc_method calculation_method
,pva.private_car rates_table_id
,pvr.engine_capacity_in_cc engine_capacity_in_cc
,pvr.fuel_type fuel_type
,pvr.vehicle_type default_vehicle
,pvr.vehicle_type vehicle_type
FROM pqp_vehicle_details pvr
,pqp_assignment_attributes_f pva
WHERE rownum=1;
SELECT pcv.aat_information1 calculation_method
FROM pqp_assignment_attributes_f pcv
WHERE rownum=1;
SELECT pva.calculation_method
,pva.rates_table_id
,pvr.engine_capacity_in_cc
,pvr.fuel_type
,pva.default_vehicle
,pvr.vehicle_type
FROM pqp_vehicle_repository_f pvr
,pqp_vehicle_allocations_f pva
WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
AND pva.assignment_id=p_assignment_id
AND pva.business_group_id=p_business_group_id
AND pva.business_group_id=pvr.business_group_id
AND pva.usage_type=p_car_type
AND pva.usage_type IN ('P','S')
AND p_claim_date BETWEEN pva.effective_start_date
AND pva.effective_end_date
AND p_claim_date BETWEEN pvr.effective_start_date
AND pvr.effective_end_date
UNION
SELECT pva.calculation_method
,pva.rates_table_id
,pvr.engine_capacity_in_cc
,pvr.fuel_type
,pva.default_vehicle
,pvr.vehicle_type
FROM pqp_vehicle_repository_f pvr
,pqp_vehicle_allocations_f pva
WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
AND pva.assignment_id=p_assignment_id
AND pva.business_group_id=p_business_group_id
AND pva.business_group_id=pvr.business_group_id
AND pvr.vehicle_ownership='P'
AND p_car_type in ('C','E')
-- AND decode(p_car_type,'E',pva.usage_type,'C',pva.usage_type,NULL) IS NULL
AND ( default_vehicle='Y' or default_vehicle IS NULL or default_vehicle='N')
AND p_claim_date BETWEEN pva.effective_start_date
AND pva.effective_end_date
AND p_claim_date BETWEEN pvr.effective_start_date
AND pvr.effective_end_date
;*/
SELECT pcv_information1 calculation_method
FROM pqp_configuration_values pcv
WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
AND pcv.legislation_code='GB'
AND NOT EXISTS (SELECT 'X'
FROM pqp_configuration_values pcv1
WHERE pcv1.business_group_id=p_business_group_id)
UNION
SELECT pcv_information1 calculation_method
FROM pqp_configuration_values pcv
WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
AND pcv.business_group_id=p_business_group_id;*/
SELECT pvd.vehicle_type
FROM pqp_vehicle_details pvd
WHERE pvd.vehicle_details_id =cp_vehicle_id
AND pvd.business_group_id=p_business_group_id;
SELECT primary_company_car
,secondary_company_car
,private_car
,company_car_rates_table_id
,company_car_secondary_table_id
,private_car_rates_table_id
,private_car_essential_table_id
,company_car_calc_method
,private_car_calc_method
FROM PQP_ASSIGNMENT_ATTRIBUTES_F
WHERE assignment_id = p_assignment_id
AND decode(TO_CHAR(TRUNC(p_claim_date),'DD/MM/YYYY'),
'01/01/1900',l_effective_date,p_claim_date)
BETWEEN effective_start_date AND effective_end_date;
SELECT distinct user_table_name
FROM pay_user_tables
WHERE user_table_id = c_rates_table
AND business_group_id=p_business_group_id;
SELECT 'X'
FROM fnd_tables ft
WHERE ft.application_id=8303
AND ft.table_name='PQP_VEHICLE_ALLOCATIONS_F'
AND rownum=1;
SELECT pva.calculation_method
,pva.rates_table_id
,pvr.engine_capacity_in_cc
,pvr.fuel_type
,pva.default_vehicle
,pvr.vehicle_type
FROM pqp_vehicle_repository_f pvr
,pqp_vehicle_allocations_f pva
WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
AND pvr.registration_number=decode(p_veh_reg,'NE',pvr.registration_number,p_veh_reg)
AND pva.assignment_id=p_assignment_id
AND pva.business_group_id=p_business_group_id
AND pva.business_group_id=pvr.business_group_id
AND pva.usage_type=l_car_type
AND pva.usage_type IN ('P','S')
AND l_temp_effective_date BETWEEN pva.effective_start_date
AND pva.effective_end_date
AND l_temp_effective_date BETWEEN pvr.effective_start_date
AND pvr.effective_end_date
UNION
SELECT pva.calculation_method
,pva.rates_table_id
,pvr.engine_capacity_in_cc
,pvr.fuel_type
,pva.default_vehicle
,pvr.vehicle_type
FROM pqp_vehicle_repository_f pvr
,pqp_vehicle_allocations_f pva
WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
AND pvr.registration_number=decode(p_veh_reg,'NE',pvr.registration_number,p_veh_reg)
AND pva.assignment_id=p_assignment_id
AND pva.business_group_id=p_business_group_id
AND pva.business_group_id=pvr.business_group_id
AND pvr.vehicle_ownership='P'
AND l_car_type in ('C','E')
AND ( default_vehicle='Y' or default_vehicle IS NULL
OR default_vehicle='N')
AND l_temp_effective_date BETWEEN pva.effective_start_date
AND pva.effective_end_date
AND l_temp_effective_date BETWEEN pvr.effective_start_date
AND pvr.effective_end_date;
SELECT engine_capacity_in_cc
INTO p_cc
FROM PQP_VEHICLE_DETAILS
WHERE vehicle_details_id = l_vehicle_details_id;
/*l_str_val:= 'SELECT pva.calculation_method
,pva.rates_table_id
,pvr.engine_capacity_in_cc
,pvr.fuel_type
,pva.default_vehicle
,pvr.vehicle_type
FROM pqp_vehicle_repository_f pvr
,pqp_vehicle_allocations_f pva
WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
AND pvr.registration_number='''||p_veh_reg||'''
AND pva.assignment_id='||p_assignment_id||
' AND pva.business_group_id='||p_business_group_id||
' AND pva.business_group_id=pvr.business_group_id
AND pva.usage_type='''||l_car_type ||'''
AND pva.usage_type IN (''P'''||','||'''S'')
AND '''||l_temp_effective_date||''' BETWEEN pva.effective_start_date
AND pva.effective_end_date
AND '''|| l_temp_effective_date||''' BETWEEN pvr.effective_start_date
AND pvr.effective_end_date
UNION
SELECT pva.calculation_method
,pva.rates_table_id
,pvr.engine_capacity_in_cc
,pvr.fuel_type
,pva.default_vehicle
,pvr.vehicle_type
FROM pqp_vehicle_repository_f pvr
,pqp_vehicle_allocations_f pva
WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
AND pvr.registration_number='''||p_veh_reg||'''
AND pva.assignment_id='||p_assignment_id||
' AND pva.business_group_id='||p_business_group_id||
' AND pva.business_group_id=pvr.business_group_id
AND pvr.vehicle_ownership=''P''
AND '''||l_car_type ||''' in (''C'',''E'')
AND ( default_vehicle=''Y'' or default_vehicle IS NULL
OR default_vehicle=''N'')
AND '''|| l_temp_effective_date ||''' BETWEEN pva.effective_start_date
AND pva.effective_end_date
AND '''|| l_temp_effective_date ||''' BETWEEN pvr.effective_start_date
AND pvr.effective_end_date' ;*/
l_str_info :='SELECT pcv_information1 calculation_method
FROM pqp_configuration_values pcv
WHERE pcv_information_category=''PQP_VEHICLE_MILEAGE''
AND pcv.legislation_code=''GB''
AND NOT EXISTS (SELECT ''X''
FROM pqp_configuration_values pcv1
WHERE pcv1.business_group_id='||p_business_group_id ||'
-- added to check for this info category only (5632627)
AND pcv1.pcv_information_category=''PQP_VEHICLE_MILEAGE'')
UNION
SELECT pcv_information1 calculation_method
FROM pqp_configuration_values pcv
WHERE pcv_information_category=''PQP_VEHICLE_MILEAGE''
AND pcv.business_group_id='||p_business_group_id;
l_str :='SELECT pcv.pcv_information5 rates_type
FROM pqp_configuration_values pcv
WHERE business_group_id='||p_business_group_id||
' AND pcv.pcv_information_category=''GB_VEHICLE_CALC_INFO''
AND (pcv_information2='||'''||l_car_type||'''||
' OR pcv_information2 IS NULL)
AND (pcv_information3='||'''||l_get_attr_val.vehicle_type||'''||'
OR pcv_information3 IS NULL)
AND (pcv_information4='||'''||l_get_attr_val.fuel_type||'''||
' OR pcv_information4 IS NULL)
AND pcv.pcv_information5 IS NOT NULL';
SELECT period_type
FROM per_time_periods
WHERE payroll_id = p_payroll_id;
SELECT period_num, period_type,start_date,end_date
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND pdate
BETWEEN start_date AND end_date;
SELECT period_num
FROM per_time_periods
WHERE payroll_id=p_payroll_id
AND end_date >=pdate
ORDER BY end_date;
SELECT MAX(period_num) INTO l_max_period_num
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND end_date >= ADD_MONTHS(to_date('06/04/'||TO_CHAR(TRUNC(p_claim_date),'RRRR'),'DD/MM/RRRR'),-6)
AND end_date <= to_date('06/04/'||TO_CHAR(TRUNC(p_claim_date),'RRRR'),'DD/MM/RRRR');
SELECT engine_capacity_in_cc
FROM pqp_vehicle_details
WHERE registration_number = p_reg_num;
SELECT hrl.meaning
FROM hr_lookups hrl
WHERE hrl.lookup_code = cp_lcode
AND hrl.application_id=800
AND lookup_type='PQP_FUEL_TYPE';
SELECT legislation_code
INTO g_leg_code
FROM per_business_groups
WHERE business_group_id = p_bus_group_id;
SELECT range_or_match, user_table_id
INTO l_range_or_match, l_table_id
FROM pay_user_tables
WHERE upper(user_table_name) = upper(p_table_name)
AND nvl (business_group_id,
p_bus_group_id) = p_bus_group_id
AND nvl(legislation_code, g_leg_code) = g_leg_code;
SELECT CINST.value
INTO l_value
FROM pay_user_column_instances_f CINST
, pay_user_columns C
, pay_user_rows_f R
, pay_user_tables TAB
WHERE TAB.user_table_id = l_table_id
AND C.user_table_id = TAB.user_table_id
AND nvl (C.business_group_id,
p_bus_group_id) = p_bus_group_id
AND nvl (C.legislation_code,
g_leg_code) = g_leg_code
AND upper (C.user_column_name) = upper (p_col_name)
AND CINST.user_column_id = C.user_column_id
AND R.user_table_id = TAB.user_table_id
AND l_effective_date between R.effective_start_date
AND R.effective_end_date
AND nvl (R.business_group_id,
p_bus_group_id) = p_bus_group_id
AND nvl (R.legislation_code,
g_leg_code) = g_leg_code
AND decode
(TAB.user_key_units,
'D', to_char(fnd_date.canonical_to_date(p_row_value)),
'N',replace(replace( upper(p_row_value),'_',' '),' '),
'T', replace(replace(upper (p_row_value),'_',' '),' '),
null) =
decode
(TAB.user_key_units,
'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
'N', replace(replace(upper(R.row_low_range_or_name),'_',' '),' '),
'T', replace(replace(upper (R.row_low_range_or_name),'_',' '),' '),
null)
AND CINST.user_row_id = R.user_row_id
AND l_effective_date between CINST.effective_start_date
AND CINST.effective_end_date
AND nvl (CINST.business_group_id,
p_bus_group_id) = p_bus_group_id
AND nvl (CINST.legislation_code,
g_leg_code) = g_leg_code;
SELECT CINST.value
INTO l_value
FROM pay_user_column_instances_f CINST
, pay_user_columns C
, pay_user_rows_f R
, pay_user_tables TAB
WHERE TAB.user_table_id = l_table_id
AND C.user_table_id = TAB.user_table_id
AND nvl (C.business_group_id,
p_bus_group_id) = p_bus_group_id
AND nvl (C.legislation_code,
g_leg_code) = g_leg_code
AND upper (C.user_column_name) = upper (p_col_name)
AND CINST.user_column_id = C.user_column_id
AND R.user_table_id = TAB.user_table_id
AND l_effective_date between R.effective_start_date
AND R.effective_end_date
AND nvl (R.business_group_id,
p_bus_group_id) = p_bus_group_id
AND nvl (R.legislation_code,
g_leg_code) = g_leg_code
AND decode
(TAB.user_key_units,
'D', to_char(fnd_date.canonical_to_date(l_fuel_type.meaning)),
'T', replace(replace(upper (l_fuel_type.meaning),'_',' '),' '),
null) =
decode
(TAB.user_key_units,
'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
'N', replace(replace(upper(R.row_low_range_or_name),'_',' '),' '),
'T', replace(replace(upper (R.row_low_range_or_name),'_',' '),' '),
null)
AND CINST.user_row_id = R.user_row_id
AND l_effective_date between CINST.effective_start_date
AND CINST.effective_end_date
AND nvl (CINST.business_group_id,
p_bus_group_id) = p_bus_group_id
AND nvl (CINST.legislation_code,
g_leg_code) = g_leg_code;
select CINST.value
into l_value
from pay_user_column_instances_f CINST
, pay_user_columns C
, pay_user_rows_f R
, pay_user_tables TAB
where TAB.user_table_id = l_table_id
and C.user_table_id = TAB.user_table_id
and nvl (C.business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl (C.legislation_code,
g_leg_code) = g_leg_code
and upper (C.user_column_name) = upper (p_col_name)
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and l_effective_date between R.effective_start_date
and R.effective_end_date
and nvl (R.business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl (R.legislation_code,
g_leg_code) = g_leg_code
and fnd_number.canonical_to_number (p_row_value)
between fnd_number.canonical_to_number (R.row_low_range_or_name)
and fnd_number.canonical_to_number (R.row_high_range)
and TAB.user_key_units = 'N'
and CINST.user_row_id = R.user_row_id
and l_effective_date between CINST.effective_start_date
and CINST.effective_end_date
and nvl (CINST.business_group_id,
p_bus_group_id) = p_bus_group_id
and nvl (CINST.legislation_code,
g_leg_code) = g_leg_code;
SELECT 'x'
INTO l_dummy
FROM pay_user_tables
WHERE user_table_name = p_table_name
AND (business_group_id = p_business_group_id OR legislation_code is NOT NULL)
AND rownum = 1;
SELECT instr (p_rates_table,'+PLUS+',1 )
INTO l_length
FROM dual;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id= p_payroll_action_id;
SELECT INSTR (p_rates_table,'+PLUS+',1 )
INTO l_length
FROM dual;
SELECT ppa.effective_date
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.business_group_id = p_business_group_id;
SELECT pee.effective_end_date
FROM pay_element_entries_f pee
WHERE pee.element_entry_id =p_element_entry_id
AND pee.assignment_id =p_assignment_id
AND pee.effective_end_date <=cp_date;
SELECT DECODE(NVL(TO_CHAR(pds.actual_termination_date), 'N'), 'N', 'N', 'Y') term_date
FROM per_periods_of_service pds
,per_assignments_f pas
WHERE pds.actual_termination_date <= p_date_earned
AND pds.period_of_service_id = pas.period_of_service_id
AND p_date_earned BETWEEN pas.effective_start_date
AND pas.effective_end_date
AND pas.primary_flag = 'Y'
AND pas.assignment_id =p_assignment_id
AND pds.business_group_id =p_business_group_id
AND pds.business_group_id=pas.business_group_id;
SELECT flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_type=p_lookup_type
AND flv.lookup_code <= p_additional_passenger
ORDER BY flv.lookup_code DESC;
SELECT pete.eei_information1 vehicle_type
FROM pay_element_types_f petf
,pay_element_type_extra_info pete
WHERE pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
AND pete.element_type_id=petf.element_type_id
AND petf.element_type_id= cp_element_type_id
AND petf.business_group_id= cp_business_group_id
AND cp_effective_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND pete.eei_information1 in ('P','PP',
'PM','C','CM'
,'CP');
SELECT 'Y'
FROM pay_element_type_extra_info petef
,pay_element_types_f pet
,pay_element_entries_f pee
,pay_element_entry_values_f pev
WHERE pet.element_type_id =petef.element_type_id
AND petef.information_type='PQP_VEHICLE_MILEAGE_INFO'
AND petef.eei_information1 = 'P'
AND pet.element_type_id =pee.element_type_id
AND pee.element_entry_id=pev.element_entry_id
AND pev.input_value_id =(SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id=petef.element_type_id
AND name='Claim Start Date') and pee.assignment_id=cp_assignment_id
--AND FND_DATE.canonical_to_date(screen_entry_value) < cp_to_date
AND (screen_entry_value) > FND_DATE.date_to_canonical (cp_to_date)
AND EXISTS (select 'Y'
from pay_run_results RESULT,
pay_assignment_actions ASGT_ACTION,
pay_payroll_actions PAY_ACTION,
per_time_periods PERIOD
where result.source_id = pev.element_entry_id --nvl (p_original_entry_id, p_element_entry_id)
and result.status <> 'U'
and result.source_type = 'E'
and result.assignment_action_id = asgt_action.assignment_action_id
and asgt_action.payroll_action_id = pay_action.payroll_action_id
and pay_action.payroll_id = period.payroll_id
and pay_action.date_earned between period.start_date and period.end_date
and pay_action.effective_date between period.start_date and period.end_date
and pay_action.effective_date < cp_eff_dt)
AND rownum=1;
SELECT ppa.effective_date
FROM pay_payroll_actions ppa
WHERE payroll_action_id=cp_payroll_action_id;