[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 aei_information3
,sum(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')
group by aei_information5,aei_information3;
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_15p_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,
aei_information7 l_public_legal_don, -- Bug 7508706
aei_information11 ltci_prem, -- Bug 7644535
aei_information13 l_lt_1500_won_limit -- Bug 9079478
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_information4,
aei_information6,
aei_information7
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_SP_TAX_EXEM_INFO3'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
select aei_information3 pers_pen_prem
,aei_information4 pers_pen_sav
,aei_information6 inv_part_fin2
,aei_information25 inv_part_fin3 -- Bug 9079478
,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
,aei_information20 small_bus_install -- Bug 7508706
,aei_information21 company_related_expense -- Bug 7615517
,aei_information22 long_term_stck_fund_1yr -- Bug 7615517
,aei_information23 long_term_stck_fund_2yr -- Bug 7615517
,aei_information24 long_term_stck_fund_3yr -- Bug 7615517
,nvl(to_number(aei_information26),0) -- Bug 9079478
,nvl(to_number(aei_information27),0) -- Bug 9079478
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 to_char(fnd_date.canonical_to_date(aei_information1) , 'YYYY.MM.DD') p_smb_start_date,
to_char(fnd_date.canonical_to_date(aei_information2) , 'YYYY.MM.DD') p_smb_end_date
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_TAX_REDUCTION_SMB'
and p_year between to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')
and to_char(fnd_date.canonical_to_date(aei_information2), 'YYYY');
select to_char(fnd_date.canonical_to_date(aei_information3) , 'YYYY.MM.DD') p_teach_application_date,
to_char(fnd_date.canonical_to_date(aei_information4) , 'YYYY.MM.DD') p_teach_submit_date
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_TAX_REDUCTION_TEACHERS'
and p_year between to_char(fnd_date.canonical_to_date(aei_information3), 'YYYY')
and to_char(fnd_date.canonical_to_date(aei_information4), 'YYYY');
select to_char(fnd_date.canonical_to_date(aei_information3) , 'YYYY.MM.DD') p_marine_confirm_date,
to_char(fnd_date.canonical_to_date(aei_information4) , 'YYYY.MM.DD') p_marine_submit_date
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_TAX_REDUCTION_MARINE'
and p_year between to_char(fnd_date.canonical_to_date(aei_information3), 'YYYY')
and to_char(fnd_date.canonical_to_date(aei_information4), 'YYYY');
select aei_information3 tm_emp_crd_exp
,aei_information4 tm_dep_crd_exp
,aei_information5 tm_emp_cash_exp
,aei_information6 tm_dep_cash_exp
,aei_information7 tm_emp_dir_exp
,aei_information8 tm_dep_dir_exp
,aei_information10 inv_fin_2011
,aei_information11 inv_fin_2012_ordinary
,aei_information12 inv_fin_2012_venture
from per_assignment_extra_info
where ASSIGNMENT_ID = p_assignment_id
and information_type = 'KR_YEA_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 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 */
sum(nvl(aei_information19,0)) p_total_sep_pension, /* Bug 7508706 */
sum(nvl(aei_information18,0)) p_total_ltci_prem /* Bug 7644535 */
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_information16,0) + nvl(cei_information17,0)) l_dpnt_direct_exp,
sum(nvl(cei_information9,0)) l_dpnt_cash_exp,
sum(nvl(cei_information18,0) + nvl(cei_information19,0)) l_dpnt_tm_card_exp,
sum(nvl(cei_information20,0)) l_dpnt_tm_cash_exp,
sum(nvl(cei_information21,0) + nvl(cei_information22,0)) l_dpnt_tm_direct_exp,
sum(nvl(cei_information24,0)) l_dpnt_tuition_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.cont_information11, -- Bug 7661820
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
peev.input_value_id,
peev.screen_entry_value
from pay_element_entry_values_f peev,
pay_element_entries_f pee,
pay_element_links_f pel
where pel.element_type_id = p_element_type_id
and p_year
between to_number(to_char(pel.effective_start_date,'YYYY'))
and to_number(to_char(pel.effective_end_date,'YYYY'))
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = p_assignment_id
and nvl(pee.entry_type, 'E') = 'E'
and p_year between
to_number(to_char(pee.effective_start_date,'YYYY'))
and to_number(to_char(pee.effective_end_date,'YYYY'))
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
order by peev.input_value_id;
Select decode(sign(to_number(to_char(pds.date_start,'YYYY')) -
(to_number(to_char(p_effective_date,'YYYY'))-1)),1,'X',0,'Y','N') Joined_last_year,
decode(sign(to_number(to_char(nvl(pds.actual_termination_date,fnd_date.canonical_to_date('4712/12/31')),'YYYY')) -
to_number(to_char(p_effective_date,'YYYY'))),1,'N',0,'Y','X') Leaving_cur_year,
asg.business_group_id bus_grp_id
from per_periods_of_service pds,
per_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and pds.period_of_service_id = asg.period_of_service_id;
select
nvl(hoi.org_information1,'N')
from hr_all_organization_units hou,
hr_organization_information hoi
where hoi.organization_id = hou.organization_id
and hou.business_group_id = p_business_group_id
and hoi.org_information_context = 'KR_YEA_ER_SMB_ELIGIBILITY_INFO';
select
nvl(pei.pei_information3,'N') house_holder_flag
from per_people_f per,
per_assignments_f asg,
per_people_extra_info pei
where asg.assignment_id = p_assignment_id
and p_effective_date
between asg.effective_start_date and asg.effective_end_date
and per.person_id = asg.person_id
and p_effective_date
between per.effective_start_date and per.effective_end_date
and per.person_id = pei.person_id(+)
and pei.information_type(+) = 'PER_KR_RELATED_YEA_INFORMATION';
Select aei_information1
From per_assignment_extra_info
Where assignment_id = p_assignment_id
And information_type = 'KR_YEA_FOREIGN_WORKER_TAX';
select sum(nvl(to_number(aei_information6), 0))
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_HOU_RENT_DETAILS'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
select
aei_information2 type,
to_number(aei_information5) cont_amount
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_HOU_SAVING_DETAILS'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY')= p_year;
select rownum,
aei_information6 priority,
aei_information2 type,
aei_information3 fin_institute,
aei_information4 acc_number,
to_number(aei_information5) cont_amount
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_SEP_PEN_DETAILS'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year
order by to_number(aei_information6);
select
rownum,
aei_information6 priority,
aei_information2 type,
aei_information3 fin_institute,
aei_information4 acc_number,
to_number(aei_information5) cont_amount
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_PEN_SAVING_DETAILS'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year
order by to_number(aei_information6);
select
rownum,
aei_information6 priority,
aei_information2 type,
aei_information3 fin_institute,
aei_information4 acc_number,
to_number(aei_information5) cont_amount
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_HOU_SAVING_DETAILS'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year
order by to_number(aei_information6);
select
rownum,
aei_information6 priority,
aei_information2 type,
aei_information3 fin_institute,
aei_information4 acc_number,
to_number(aei_information5) cont_amount
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_LT_STOCK_SAVING_DETAILS'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year
order by to_number(aei_information6);
select
per.person_id, -- bug 6012258
pds.date_start,
pds.actual_termination_date
from per_people_f per,
per_periods_of_service pds,
per_assignments_f asg
where
asg.assignment_id = p_assignment_id
and p_year between
to_number(to_char(asg.effective_start_date,'YYYY'))
and to_number(to_char(asg.effective_end_date,'YYYY'))
and pds.period_of_service_id = asg.period_of_service_id
and per.person_id = pds.person_id
and p_year between
to_number(to_char(per.effective_start_date,'YYYY'))
and to_number(to_char(per.effective_end_date,'YYYY'));
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
element_type_id
into l_element.element_type_id
from pay_element_types_f
where element_name = p_element_name
and legislation_code = 'KR'
and business_group_id is null
group by element_type_id;
select
input_value_id,
min(name)
bulk collect into
l_element.input_value_id_tbl,
l_element.input_value_name_tbl
from pay_input_values_f
where element_type_id = l_element.element_type_id
and legislation_code = 'KR'
and business_group_id is null
group by input_value_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)
+nvl(aei_information7,0)) -- Bug 7508706: Donation Total2
,nvl(aei_information8,0) -- Bug 7508706: Total Dependent Donation Expense
,nvl(aei_information11, 0) -- Bug 7644535: LTCI premium
,nvl(aei_information10,'N') -- Bug 9381801: Medical Total and Details Flag
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_information7,0) --Card Total
,nvl(aei_information13,0) --Cash Total
,nvl(aei_information10,0) --Direct Total
, nvl(aei_information12,0) -- Tution 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 nvl(aei_information3, 0), -- TM Card Total Bug 15911822
nvl(aei_information5, 0), -- TM Cash Total Bug 15911822
nvl(aei_information7, 0) -- TM Direct Total Bug 15911822
from per_assignment_extra_info
where ASSIGNMENT_ID=p_emp_assignment_id
and information_type = 'KR_YEA_TAX_EXEM_INFO2'
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
,aei_information7 --Direct Payment -- Bug 10184055
,aei_information8 -- TM Card NTS Bug 15911822
,aei_information9 -- TM Direct NTS Bug 15911822
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;
select
nvl(sum(decode(aei_information7, '0', nvl(aei_information3,0) + nvl(aei_information11,0), 0)),0) employee
from per_assignment_extra_info pai
where pai.assignment_id = p_emp_assignment_id
and pai.information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
and to_char(fnd_date.canonical_to_date(aei_information1), 'YYYY') = p_year;