[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select aei_information3
,to_number(aei_information4)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
= to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
select sum(to_number(aei_information3)) aei_information3
from per_assignment_extra_info
where assignment_id = p_assignment_id
and aei_information2 <> 'HST2'
and information_type = 'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
= to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
select aei_information5 pers_ins_prem
,aei_information6 dis_ins_prem
,aei_information7 gen_med_exp
,aei_information8 l_med_exp_disabled
,aei_information9 l_med_exp_aged
,aei_information24 l_med_exp_emp
,aei_information15 hou_loan_repay
,aei_information17 lt_hou_int_repay
,nvl(aei_information18,0)+nvl(aei_information19,0)+nvl(aei_information20,0)
+nvl(aei_information21,0)+nvl(aei_information22,0)+nvl(aei_information23,0) Donations
,aei_information10 emp_educ
,aei_information2 hi_prem
,aei_information3 ei_prem
,aei_information18 p_100p_donation
,aei_information19 l_political_since_040312
,aei_information20 l_political_before_040312
,aei_information23 p_50p_donation
,aei_information30 p_30p_donation
,aei_information22 p_10p_donation
,aei_information27 l_marriage_count
,aei_information29 l_reloc_count
,aei_information28 l_funeral_count
,aei_information26 l_lt_hou_int_repay_gt_15
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
= to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
select aei_information2 emp_occ_trg_exp,
aei_information3 med_exp_card,
aei_information4 l_promotional_fund_don,
aei_information5 l_religious_don,
aei_information6 l_other_don
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
= to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
select aei_information3 pers_pen_prem
,aei_information4 pers_pen_sav
,aei_information6 inv_part_fin2
,aei_information7 emp_crd_exp
,aei_information9 dep_crd_exp
,aei_information8 emp_stk_opt
,aei_information10 emp_crd_drt_exp
,aei_information11 dep_crd_drt_exp -- 4046680
,aei_information12 p_tuition_giro
,aei_information13 cash_receipt -- Bug 4336742
,aei_information2 p_other_exem_np_prem
,aei_information15 p_other_exem_pen_prem -- Bug 6655323
,aei_information14 corporate_pension -- Bug 4764823
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_TAX_EXEM_INFO'
and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
= to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
select aei_information2 hou_loan_int_repay
,aei_information4 lt_stk_sav1
,aei_information5 lt_stk_sav2
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_TAX_BREAK_INFO'
and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
= to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
select nvl(aei_information2,0) + nvl(aei_information3,0)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_FW_TAX_EXEM_INFO'
and to_date(aei_information1,'rrrr/mm/dd hh24:mi:ss')
= to_date('31-12-'||to_char(p_year),'dd-mm-rrrr');
select aei_information7 p_ovs_tax_paid_fc,
aei_information8 p_ovs_tax_paid_lc,
hr_general.decode_territory(aei_information2) p_ovs_country,
to_char(fnd_date.canonical_to_date(aei_information1) , 'YYYY.MM.DD') p_ovs_paid_date,
to_char(fnd_date.canonical_to_date(aei_information10), 'YYYY.MM.DD') p_ovs_submit_date,
aei_information11 p_ovs_location,
aei_information12 p_ovs_period,
aei_information13 p_ovs_title
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_OVS_TAX_BREAK_INFO'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
select aei_information1 p_immigration_purpose,
to_char(fnd_date.canonical_to_date(aei_information2) , 'YYYY.MM.DD') p_fw_contract_date,
to_char(fnd_date.canonical_to_date(aei_information3) , 'YYYY.MM.DD') p_fw_expiry_date,
to_char(fnd_date.canonical_to_date(aei_information5) , 'YYYY.MM.DD') p_fw_application_date,
to_char(fnd_date.canonical_to_date(aei_information6) , 'YYYY.MM.DD') p_fw_submit_date
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_FW_TAX_BREAK_INFO'
and p_year between to_char(fnd_date.canonical_to_date(aei_information2), 'YYYY')
and to_char(fnd_date.canonical_to_date(aei_information3), 'YYYY');
select sum(nvl(aei_information10,0)) p_total_hi_prem,
sum(nvl(aei_information11,0)) p_total_ei_prem,
sum(nvl(aei_information12,0)) p_total_np_prem,
sum(nvl(aei_information16,0)) p_total_pen_prem /* Bug 6655323 */
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_PREV_ER_INFO'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
select sum(nvl(cei_information1,0) + nvl(cei_information2,0)) l_dpnt_pers_ins,
sum(nvl(cei_information10,0) + nvl(cei_information11,0)) l_dpnt_dis_ins,
sum(nvl(cei_information7,0) + nvl(cei_information8,0)) l_dpnt_cards_exp,
sum(nvl(cei_information9,0)) l_dpnt_cash_exp
from pay_kr_cont_details_v pkc,
per_contact_extra_info_f cei -- Bug 5879106
where assignment_id = p_assignment_id
-- Bug 5879106
and cei.information_type(+) = 'KR_DPNT_EXPENSE_INFO'
and cei.contact_relationship_id(+) = pkc.contact_relationship_id
and to_char(cei.effective_start_date(+), 'YYYY') = to_char(p_effective_date, 'YYYY')
--
and p_effective_date between emp_start_date and emp_end_date
and p_effective_date between cont_start_date and cont_end_date
and p_effective_date between nvl(ADDRESS_START_DATE,p_effective_date) and nvl(ADDRESS_END_DATE, p_effective_date)
and p_effective_date between nvl(pkc.date_start, p_effective_date)
and decode(pkc.cont_information9, 'D', trunc(add_months(nvl(pkc.date_end, p_effective_date),12),'YYYY')-1, nvl(pkc.date_end, p_effective_date) )
and pay_kr_ff_functions_pkg.is_exempted_dependent( pkc.contact_type,
pkc.national_identifier,
pkc.cont_information2,
pkc.cont_information3,
pkc.cont_information4,
pkc.cont_information7,
pkc.cont_information8,
p_effective_date,
pkc.cont_information10,
pkc.cont_information12,
pkc.cont_information13,
pkc.cont_information14,
cei.contact_extra_info_id
) = 'Y';
select pdb.defined_balance_id
from pay_balance_types pbt,
pay_balance_dimensions dim,
pay_defined_balances pdb
where pbt.legislation_code = 'KR'
and pbt.balance_name = p_ytd_balance_name
and dim.legislation_code = 'KR'
and dim.dimension_name = '_ASG_YTD'
and pdb.legislation_code = 'KR'
and pdb.BALANCE_DIMENSION_ID = dim.BALANCE_DIMENSION_ID
and pdb.BALANCE_TYPE_ID = pbt.balance_type_id;
select (nvl(aei_information2,0) + nvl(aei_information3,0) +
nvl(aei_information5,0) + nvl(aei_information6,0)) --Insurance total -- Bug 5726158
,nvl(aei_information24,0) --Medical Exem Total
,nvl(aei_information10,0) --Education Exem Total1
,(nvl(aei_information18,0)
+ nvl(aei_information19,0)
+ nvl(aei_information20,0)
+ nvl(aei_information22,0)
+ nvl(aei_information23,0)
+ nvl(aei_information30,0)) --Donation Total1
,nvl(aei_information19,0) --Political_donation
,nvl(aei_information1,fnd_date.date_to_canonical(to_date('31-12-'||to_char(p_year),'dd-mm-rrrr'))) --Effective Date
from per_assignment_extra_info
where ASSIGNMENT_ID=p_emp_assignment_id
and information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
select nvl(aei_information2,0) --Education Exem Total2
,(nvl(aei_information4,0)
+nvl(aei_information5,0)
+nvl(aei_information6,0)) --Donation Total2
from per_assignment_extra_info
where ASSIGNMENT_ID=p_emp_assignment_id
and information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
select (nvl(aei_information10,0)
+nvl(aei_information7,0)
+nvl(aei_information12,0)) --Card Total
,nvl(aei_information13,0) --Cash Total
from per_assignment_extra_info
where ASSIGNMENT_ID=p_emp_assignment_id
and information_type = 'KR_YEA_TAX_EXEM_INFO'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;
select defined_balance_id
from pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
where pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbt.balance_name = p_balance_name
and pbd.dimension_name = p_dimension_name
and pdb.legislation_code = 'KR';
select aei_information2 --Medical NTS
,aei_information3 --Education NTS
,aei_information4 --Cards NTS
,aei_information5 --Donation NTS
,aei_information6 --Insurance NTS -- Bug 5726158
from per_assignment_extra_info
where ASSIGNMENT_ID=p_emp_assignment_id
and information_type = 'KR_YEA_EMP_EXPENSE_DETAILS'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;