[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
deal with date-tracked updates to car
element entries and modified nicar_nicable_
value_CO2 and nicar_nicable_value_non_CO2 to
be called from nicar_main. Fast formulas for
NI Car will now call nicar_main directly
instead of calling the other 2 procedures as
happened previously. Also removed specific
parameters for P11d and get_next_pay_date function as
no longer required. Changed code for deduction of
employee contributions (payments)
115.10 11/4/02 GBUTLER Put nvl() around condition which determines which
calculation procedure to call to deal with cars registered
before 01-JAN-1998 with no fuel type attached. Also included
defaults for engine size where required so that auto
calculation of fuel scale charges work correctly. Bugs 2281529
and 2279049. Changed fuel default to diesel.
Added functionality to process Euro IV diesel cars
115.11 24/4/02 GBUTLER Updated handling of messages to provide more logical approach.
Moved defaulting of fuel type and engine size to nicar_main
function.
115.12 10/5/02 GBUTLER Replaced p_message parameter in nicar_nicable_value_non_co2
function as parameter required by p11d packages that call
this function. Added l_cc_message in nicar_main to capture
output from p_message even though output will be null.
This fix for 11i only (not required on 11 or 10.7)
115.13 03/7/02 GBUTLER Bug 2444082. Added functionality to annualise period
numbers for non-standard payrolls (e.g. lunar) so that
comparison between current payroll period number and
payroll periods per year is effective when determining
whether to use tax year end date or current payroll period
end date in calculation.
115.14 13/11/02 RMAKHIJA Legislative changes to fuel scale charge for
tax year 2003-2004
115.15 29/11/02 RMAKHIJA Commented Out trace on and off
115.16 29/11/02 RMAKHIJA Added WHWENVER OSERROR command at the top to fix
GSCC warning.
115.17 09/12/02 RMAKHIJA Changed to count 29-FEB in calculation for leap years
115.18 09/12/02 RMAKHIJA Added NOCOPY to out parameters to fix GSCC warning
115.19 12/12/02 RMAKHIJA Added g_last_opt_out_date and csr_last_opt_out_date cursor
to make sure fuel scale charge is not calculated for the
last opted out period if the employee remains opted out.
115.20 07/01/03 MMAHMAD Added HYBRID ELECTRIC in the IN clause for the calculation
of fuel charge.
115.21 15/01/03 MMAHMAD Added a call for the get_CO2_percentage function to calculate
CO2 percentage for HYBRID ELECTRIC, LPG_CNG, LPG_CNG_Petrol and
LPG_CNG_Petrol_Conv fuel types.
*/
g_package_name VARCHAR2(21) := 'PAY_GB_NICAR_06042002';
select min(pur.row_low_range_or_name)
from pay_user_rows_f pur,
pay_user_tables put
where put.user_table_id = pur.user_table_id
and put.user_table_name = 'GB_CO2_EMISSIONS'
and p_session_date between
pur.effective_start_date and pur.effective_end_date;
select max(pur.row_low_range_or_name)
from pay_user_rows_f pur,
pay_user_tables put
where put.user_table_id = pur.user_table_id
and put.user_table_name = 'GB_CO2_EMISSIONS'
and p_session_date between
pur.effective_start_date and pur.effective_end_date;
select min(value)
from pay_user_column_instances_f puci,
pay_user_columns puc
where puc.user_column_id = puci.user_column_id
and puc.user_table_id =
(select user_table_id
from pay_user_tables
where user_table_name = 'GB_CO2_EMISSIONS')
and p_session_date between
puci.effective_start_date and puci.effective_end_date;
select max(value)
from pay_user_column_instances_f puci,
pay_user_columns puc
where puc.user_column_id = puci.user_column_id
and puc.user_table_id =
(select user_table_id
from pay_user_tables
where user_table_name = 'GB_CO2_EMISSIONS')
and p_session_date between
puci.effective_start_date and puci.effective_end_date;
select to_number(global_value)
from ff_globals_f
where global_name = 'NI_CAR_MAX_PRICE'
and p_session_date between
effective_start_date and effective_end_date;
select to_number(global_value)
from ff_globals_f
where global_name = 'NI_CAR_MAX_PRICE'
and p_session_date between
effective_start_date and effective_end_date;
select to_number(global_value)
from ff_globals_f
where legislation_code = 'GB'
and upper(global_name) = upper(l_global_name)
and p_session_date between effective_start_date
and effective_end_date;
SELECT fnd_number.canonical_to_number(LIM.global_value)
, fnd_number.canonical_to_number(NIR.global_value)
FROM ff_globals_f LIM
, ff_globals_f NIR
WHERE LIM.global_name = 'NI_CAR_MAX_PRICE'
AND csr0_session_date between LIM.effective_start_date and LIM.effective_end_date
AND NIR.global_name = 'NI_ERS_RATE'
AND csr0_session_date between NIR.effective_start_date and NIR.effective_end_date;
SELECT E_TL.element_name
, IPR.input_value_id
, IRD.input_value_id
, IRN.input_value_id
, IMB.input_value_id
, IFT.input_value_id
, ICC.input_value_id
, IFS.input_value_id
, IAP.input_value_id
, ICO.input_value_id
FROM pay_input_values_f IPR
, pay_input_values_f IRD
, pay_input_values_f IRN
, pay_input_values_f IMB
, pay_input_values_f IFT
, pay_input_values_f ICC
, pay_input_values_f IFS
, pay_input_values_f IAP
, pay_input_values_f ICO
, pay_element_types_f_tl E_TL
, pay_element_types_f E
WHERE E_TL.element_type_id = E.element_type_id
AND E.element_type_id = p_element_type_id
AND userenv('LANG') = E_TL.language
AND IPR.element_type_id = E.element_type_id
AND IPR.name = 'Price'
AND IRD.element_type_id =E.element_type_id
AND IRD.name = 'Registration Date'
AND IRN.element_type_id = E.element_type_id
AND IRN.name = 'Registration Number'
AND IMB.element_type_id = E.element_type_id
AND IMB.name = 'Mileage Band'
AND IFT.element_type_id = E.element_type_id
AND IFT.name = 'Fuel Type'
AND ICC.element_type_id = E.element_type_id
AND ICC.name = 'Engine cc'
AND IFS.element_type_id = E.element_type_id
AND IFS.name = 'Fuel Scale'
AND IAP.element_type_id = E.element_type_id
AND IAP.name = 'Payment'
AND ICO.element_type_id = E.element_type_id
AND ICO.name = 'CO2 Emissions'
AND csr0_session_date between E.effective_start_date and E.effective_end_date;
select
max(decode(V.input_value_id,csr2_pr,
fnd_number.canonical_to_number(V.Screen_entry_value),null) ) csr3_price
,max(decode(V.input_value_id, csr2_mb,
fnd_number.canonical_to_number(V.Screen_entry_value),null)) csr3_mileage_band
,max(decode(V.input_value_id,csr2_rd,
fnd_date.canonical_to_date(V.Screen_entry_value),null)) csr3_reg_date
,max(decode(V.input_value_id,csr2_ft,
V.Screen_entry_value,null)) csr3_fuel_type
,max(decode(V.input_value_id,csr2_cc,
fnd_number.canonical_to_number(v.Screen_entry_value),null)) csr3_engine_cc
,max(decode(V.input_value_id,csr2_fs,
fnd_number.canonical_to_number(V.Screen_entry_value),null)) csr3_fuel_scale
,max(decode(V.input_value_id,csr2_ap,
nvl(fnd_number.canonical_to_number(V.Screen_entry_value),0),null)) csr3_payment
,max(decode(V.input_value_id,csr2_co2,
fnd_number.canonical_to_number(V.Screen_entry_value),null)) csr3_co2
,EENT.effective_end_date csr3_end_date
,EENT.effective_start_date csr3_start_date
FROM pay_element_entries_f EENT
, pay_element_links_f LINK
, pay_element_entry_values_f V
WHERE EENT.effective_end_date >= g_tax_year_start
AND EENT.effective_start_date <=
least(p_emp_term_date,l_end_of_period_date,g_tax_year_end)
AND EENT.assignment_id = p_assignment_id
AND LINK.element_type_id = p_element_type_id
AND EENT.element_link_id = LINK.element_link_id
AND EENT.effective_start_date >= LINK.effective_start_date
AND EENT.effective_end_date <= LINK.effective_end_date
AND EENT.entry_type = 'E'
AND V.Element_entry_id = EENT.element_entry_id
AND V.Effective_start_date = EENT.effective_start_date
group by EENT.effective_end_date, EENT.effective_start_date;
SELECT 'Y'
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_entry_values_f peev
WHERE pee.effective_start_date > g_tax_year_start
AND pee.effective_start_date <= least(p_emp_term_date, l_end_of_period_date, g_tax_year_end)
AND pee.assignment_id = p_assignment_id
AND pel.element_type_id = p_element_type_id
AND pee.element_link_id = pel.element_link_id
AND pee.effective_start_date >= pel.effective_start_date
AND pee.effective_end_date <= pel.effective_end_date
AND pee.entry_type = 'E'
AND peev.element_entry_id = pee.element_entry_id
AND peev.effective_start_date = pee.effective_start_date
AND peev.input_value_id = csr2_fs
AND peev.screen_entry_value IS NOT NULL;
SELECT nvl(max(pee.effective_start_date), g_tax_year_end+1)
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_entry_values_f peev
WHERE least(p_emp_term_date, l_end_of_period_date, g_tax_year_end) BETWEEN
pee.effective_start_date AND pee.effective_end_date
AND pee.assignment_id = p_assignment_id
AND pel.element_type_id = p_element_type_id
AND pee.element_link_id = pel.element_link_id
AND pee.effective_start_date >= pel.effective_start_date
AND pee.effective_end_date <= pel.effective_end_date
AND pee.entry_type = 'E'
AND peev.element_entry_id = pee.element_entry_id
AND peev.effective_start_date = pee.effective_start_date
AND peev.input_value_id = csr2_fs
AND peev.screen_entry_value IS NULL;
select ptpt.period_type
from per_time_period_types ptpt,
per_time_periods ptp,
per_assignments_f paf
where paf.payroll_id = ptp.payroll_id
and ptpt.period_type = ptp.period_type
and paf.assignment_id = p_assignment_id
and trunc(p_session_date) between trunc(ptp.start_date) and trunc(ptp.end_date);
select ptp.end_date
from per_time_periods ptp,
pay_all_payrolls_f papf,
per_assignments_f paf
where paf.payroll_id = papf.payroll_id
and papf.payroll_id = ptp.payroll_id
and paf.assignment_id = p_assignment_id
and ptp.start_date = p_curr_payroll_period_end_date + 1;