[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select org_information3
from hr_organization_information
where organization_id = p_business_group_id
and org_information_context = 'JP_BUSINESS_GROUP_INFO';
select car.element_entry_id,
car.tranpo_type,
car.period,
car.distance,
car.amount,
car.parking_fees,
car.equivalent_cost,
car.pay_start,
car.pay_end
from (
select v.element_entry_id,
v.tranpo_type,
v.period,
v.distance,
v.amount,
v.parking_fees,
v.equivalent_cost,
nvl(to_date(v.pay_start, 'YYYYMM'), trunc(v.effective_start_date, 'MM')) pay_start,
nvl(to_date(v.pay_end, 'YYYYMM'), trunc(v.effective_end_date, 'MM')) pay_end
from (
select /*+ ORDERED USE_NL(PEL PEE PEEV PIV) INDEX (PEE PAY_ELEMENT_ENTRIES_F_N51) */
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
substrb(min(decode(piv.display_sequence, 1, peev.screen_entry_value, NULL)), 1, 30) tranpo_type,
nvl(fnd_number.canonical_to_number(substr(min(decode(piv.display_sequence, 3, peev.screen_entry_value, NULL)), 5)), 1) period, -- MTH_x(x=1,3,6)
-- Value needs to truncated to avoid no_data_found in get_table_value
trunc(nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 4, peev.screen_entry_value, NULL))), 0) * 1000) distance,
-- amount and parking_fees can be null values.
fnd_number.canonical_to_number(min(decode(piv.display_sequence, 6, peev.screen_entry_value, NULL))) amount,
fnd_number.canonical_to_number(min(decode(piv.display_sequence, 7, peev.screen_entry_value, NULL))) parking_fees,
nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 8, peev.screen_entry_value, NULL))), 0) equivalent_cost,
substrb(min(decode(piv.display_sequence, 9, peev.screen_entry_value, NULL)), 1, 6) pay_start,
substrb(min(decode(piv.display_sequence, 10, peev.screen_entry_value, NULL)), 1, 6) pay_end
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f piv
where pet.element_name = c_car_element_name
and pet.legislation_code = 'JP'
and p_effective_date
between pet.effective_start_date and pet.effective_end_date
and pel.element_type_id = pet.element_type_id
and p_effective_date
between pel.effective_start_date and pel.effective_end_date
and pel.business_group_id = p_business_group_id
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id
and p_effective_date
between pee.effective_start_date and pee.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.effective_end_date = pee.effective_end_date
and piv.input_value_id = peev.input_value_id
and p_effective_date
between piv.effective_start_date and piv.effective_end_date
group by
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date
) v
where v.tranpo_type is not null
) car
where l_payment_date
between car.pay_start and car.pay_end;
select train.element_entry_id,
train.effective_start_date,
train.effective_end_date,
train.tranpo_type,
train.period,
train.amount_type,
train.amount,
train.pay_start,
train.pay_end,
train.si_start,
train.si_end,
train.si_month
from (
select v.element_entry_id,
v.effective_start_date,
v.effective_end_date,
v.tranpo_type,
v.period,
v.amount_type,
v.amount,
--
-- When pay_start is null, pay_start is
-- 1. 1 month before effective_start_date month when si_month = "Next Month"
-- 2. effective_start_date month when si_month = "This Month"
--
nvl(to_date(v.pay_start, 'YYYYMM'), trunc(add_months(v.effective_start_date, - v.si_month), 'MM')) pay_start,
nvl(to_date(v.pay_end, 'YYYYMM'), trunc(add_months(v.effective_end_date, - v.si_month), 'MM')) pay_end,
nvl(add_months(to_date(v.pay_start, 'YYYYMM'), v.si_month), trunc(v.effective_start_date, 'MM')) si_start,
nvl(add_months(to_date(v.pay_end, 'YYYYMM'), v.si_month), trunc(v.effective_end_date, 'MM')) si_end,
v.si_month
from (
select /*+ ORDERED USE_NL(PEL PEE PEEV PIV) INDEX (PEE PAY_ELEMENT_ENTRIES_F_N51) */
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
substrb(min(decode(piv.display_sequence, 1, peev.screen_entry_value, NULL)), 1, 30) tranpo_type,
nvl(fnd_number.canonical_to_number(substr(min(decode(piv.display_sequence, 5, peev.screen_entry_value, NULL)), 5)), 1) period, -- MTH_x(x=1,3,6)
nvl(substrb(min(decode(piv.display_sequence, 6, peev.screen_entry_value, NULL)), 1, 30), 'SALARY') amount_type, -- SALARY/MATERIAL
nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence,7, peev.screen_entry_value, NULL))), 0) amount,
substrb(min(decode(piv.display_sequence, 8, peev.screen_entry_value, NULL)), 1, 6) pay_start,
substrb(min(decode(piv.display_sequence, 9, peev.screen_entry_value, NULL)), 1, 6) pay_end,
nvl(fnd_number.canonical_to_number(min(decode(piv.display_sequence, 10, peev.screen_entry_value, NULL))), 0) si_month
from pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f piv
where pet.element_name = c_train_element_name
and pet.legislation_code = 'JP'
and p_effective_date
between pet.effective_start_date and pet.effective_end_date
and pel.element_type_id = pet.element_type_id
and p_effective_date
between pel.effective_start_date and pel.effective_end_date
and pel.business_group_id = p_business_group_id
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id
-- This date range check is loose, and will exact range check be done outside this inline SQL.
-- We take si_month into consideration only for train tranpo, not car tranpo.
and pee.effective_start_date <= p_effective_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.effective_end_date = pee.effective_end_date
and piv.input_value_id = peev.input_value_id
and p_effective_date
between piv.effective_start_date and piv.effective_end_date
group by
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date
) v
where v.tranpo_type is not null
) train
where
--
-- Taxable Check
--
(
train.effective_end_date >= p_effective_date
and l_payment_date
between train.pay_start and train.pay_end
)
or
--
-- SI Wage Check (Already paid before l_payment_date)
--
(
train.si_month <> 0
and train.effective_end_date >= add_months(p_effective_date, - train.si_month)
and train.si_end >= l_payment_date
/*
and add_months(p_effective_date, - train.si_month)
between train.effective_start_date and train.effective_end_date
and l_payment_date
between train.si_start and train.si_end
*/
);