The following lines contain the word 'select', 'insert', 'update' or 'delete':
select lookup_code from hr_lookups
where lookup_type = 'KR_NON_TAXABLE_EARNINGS_CODE'
and enabled_flag = 'Y'
order by 1;
select user_entity_id
bulk collect into
l_user_entity_id_tbl
from pay_report_format_items_f
where report_type = p_report_type
and report_qualifier = p_report_qualifier
and report_category = p_report_category
and p_effective_date
between effective_start_date and effective_end_date;
g_archive_item.archive_item_tbl.delete;
select hr_ni_chk_pkg.chk_nat_id_format(aei_information2, 'DDD-DD-DDDDD'),
aei_information3,
fnd_date.canonical_to_date(aei_information15), -- Bug 8644512
fnd_date.canonical_to_date(aei_information16), -- Bug 8644512
fnd_date.canonical_to_date(aei_information17), -- Bug 8644512
fnd_date.canonical_to_date(aei_information18), -- Bug 8644512
nvl(to_number(aei_information4), 0),
nvl(to_number(aei_information5), 0),
nvl(to_number(aei_information6), 0),
nvl(to_number(aei_information7), 0),
nvl(to_number(aei_information19), 0), -- Bug 8644512
nvl(to_number(aei_information8), 0),
nvl(to_number(aei_information9), 0),
nvl(to_number(aei_information10), 0),
nvl(to_number(aei_information11), 0), -- Bug 7508706
nvl(to_number(aei_information12), 0), -- Bug 7508706
nvl(to_number(aei_information13), 0), -- Bug 7508706
nvl(to_number(aei_information14), 0), -- Bug 7508706
nvl(to_number(aei_information20), 0), -- Bug 8644512
nvl(to_number(aei_information21), 0), -- Bug 8644512
nvl(to_number(aei_information22), 0), -- Bug 8644512
nvl(to_number(aei_information23), 0) -- Bug 8644512
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_TAX_GROUP_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select fnd_date.canonical_to_date(aei_information23) aei23, -- Bug 8644512
fnd_date.canonical_to_date(aei_information1) aei1,
aei_information2 aei2,
hr_ni_chk_pkg.chk_nat_id_format(aei_information3, 'DDD-DD-DDDDD') aei3,
fnd_date.canonical_to_date(aei_information24) aei24, -- Bug 8644512
fnd_date.canonical_to_date(aei_information25) aei25, -- Bug 8644512
nvl(to_number(aei_information4), 0) aei4,
nvl(to_number(aei_information5), 0) aei5,
nvl(to_number(aei_information6), 0) aei6,
nvl(to_number(aei_information7), 0) aei7,
nvl(to_number(aei_information8), 0) aei8,
nvl(to_number(aei_information9), 0) aei9,
nvl(to_number(aei_information10), 0) aei10,
nvl(to_number(aei_information18), 0) aei18, -- Bug 7260606
nvl(to_number(aei_information11), 0) aei11,
nvl(to_number(aei_information12), 0) aei12,
nvl(to_number(aei_information13), 0) aei13,
nvl(to_number(aei_information14), 0) aei14,
nvl(to_number(aei_information15), 0) aei15,
nvl(to_number(aei_information16), 0) aei16, -- Bug 6024342
nvl(to_number(aei_information28), 0) aei28, -- Bug 9737699
nvl(to_number(aei_information29), 0) aei29, -- Bug 9737699
nvl(to_number(aei_information30), 0) aei30, -- Bug 9737699
nvl(to_number(aei_information17), 0) aei17, -- Bug 6024342
nvl(to_number(aei_information19), 0) aei19, -- Bug 7508706
nvl(to_number(aei_information20), 0) aei20, -- Bug 8341054
nvl(to_number(aei_information21), 0) aei21, -- Bug 8341054
nvl(to_number(aei_information22), 0) aei22, -- Bug 8341054
nvl(to_number(aei_information26), 0) aei26, -- Bug 8644512
assignment_extra_info_id asg_ex_info_id -- Bug 9737699
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_PREV_ER_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by 1;
select nvl(to_number(aei_information4), 0) aei4,
nvl(to_number(aei_information5), 0) aei5,
nvl(to_number(aei_information6), 0) aei6
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_PREV_ER_INFO2'
and aei_information2 = p_assignment_extra_info_id
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by 1;
select
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 trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by to_number(aei_information6);
select
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 trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by to_number(aei_information6);
select
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 trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by to_number(aei_information6);
select
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 trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by to_number(aei_information6);
select aei_information4 bus_reg_num,
aei_information2 code,
nvl(aei_information5,0) value
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_NON_TAXABLE_EARN_DETAIL'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by 1;
select
assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
nvl(to_number(aei_information2),0) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '31'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by aei_information2;
select
assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
nvl(to_number(aei_information2),0) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '31'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by aei_information2 desc;
select assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
to_number(aei_information2) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '31'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and to_number(aei_information2) = to_number(to_char(p_effective_date, 'YYYY'))
order by donated_year;
select assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
to_number(aei_information2) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '31'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and to_number(aei_information2) <> to_number(to_char(p_effective_date, 'YYYY'))
order by donated_year;
select
assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
nvl(to_number(aei_information2),0) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '30'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by aei_information2;
select
assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
nvl(to_number(aei_information2),0) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '30'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by aei_information2 desc;
select assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
to_number(aei_information2) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '30'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and to_number(aei_information2) = to_number(to_char(p_effective_date, 'YYYY'))
order by donated_year;
select assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
to_number(aei_information2) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '30'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and to_number(aei_information2) <> to_number(to_char(p_effective_date, 'YYYY'))
order by donated_year;
select
assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
nvl(to_number(aei_information2),0) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '10'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by aei_information2;
select
assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
nvl(to_number(aei_information2),0) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '10'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by aei_information2 desc;
select assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
to_number(aei_information2) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '10'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and to_number(aei_information2) = to_number(to_char(p_effective_date, 'YYYY'))
order by donated_year;
select assignment_extra_info_id asg_info_id,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
to_number(aei_information2) donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '10'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and to_number(aei_information2) <> to_number(to_char(p_effective_date, 'YYYY'))
order by donated_year;
select
assignment_extra_info_id asg_info_id,
to_number(aei_information2) donated_year,
nvl(to_number(aei_information10),0) amt_sub_to_exem
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '40'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by aei_information2;
select
assignment_extra_info_id asg_info_id,
to_number(aei_information2) donated_year,
nvl(to_number(aei_information10),0) amt_sub_to_exem
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = '41'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by aei_information2;
select
sum(decode(chk_valid_donation(to_number(aei_information10),aei_information8,p_effective_date),'Y',nvl(to_number(aei_information10),0),0)),
to_number(aei_information2)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 in ('40','41')
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
group by to_number(aei_information2)
order by to_number(aei_information2);
select
sum(decode(chk_valid_donation(to_number(aei_information10),aei_information8,p_effective_date),'Y',nvl(to_number(aei_information10),0),0)),
to_number(aei_information2)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 = ('41')
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
group by to_number(aei_information2)
order by to_number(aei_information2);
select
nvl(hoi.org_information1,'Y'),
nvl(hoi.org_information2,'Y')
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_DON_CARRY_OVER_CALC';
select
assignment_extra_info_id asg_info_id,
to_number(aei_information2) donated_year,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
aei_information8 donation_code
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 in ('41','40')
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by to_number(aei_information8), aei_information2;
select
assignment_extra_info_id asg_info_id,
to_number(aei_information2) donated_year,
nvl(to_number(aei_information10),0) amt_sub_to_exem,
aei_information8 donation_code
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 in ('41','40')
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by to_number(aei_information8) , aei_information2 desc;
select donated_year from (
select distinct aei_information2 donated_year
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 in ('41','40')
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
order by aei_information2 desc) where rownum <=2;
select assignment_extra_info_id asg_info_id,
aei_information8 donation_code,
to_number(aei_information2) donated_year,
nvl(to_number(aei_information10),0) amt_sub_to_exem
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 in ('41','40')
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and to_number(aei_information2) = to_number(to_char(p_effective_date, 'YYYY'))
order by donation_code;
select assignment_extra_info_id asg_info_id,
aei_information8 donation_code,
to_number(aei_information2) donated_year,
nvl(to_number(aei_information10),0) amt_sub_to_exem
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information8 in ('41','40')
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and to_number(aei_information2) <> to_number(to_char(p_effective_date, 'YYYY'))
order by donation_code, donated_year;
select nvl(to_number(aei_information2), 0),
nvl(to_number(aei_information3), 0),
aei_information4,
nvl(to_number(aei_information5), 0),
nvl(to_number(aei_information6), 0),
nvl(to_number(aei_information7), 0),
nvl(to_number(aei_information8), 0),
nvl(to_number(aei_information9), 0),
nvl(to_number(aei_information10), 0),
fnd_date.canonical_to_date(aei_information13),
fnd_date.canonical_to_date(aei_information14),
nvl(to_number(aei_information15), 0),
fnd_date.canonical_to_date(aei_information16),
nvl(to_number(aei_information17), 0),
fnd_date.canonical_to_date(aei_information25),
nvl(to_number(aei_information26), 0),
nvl(to_number(aei_information18), 0), -- Statutory (100%)
nvl(to_number(aei_information19), 0), -- Political (100,000 Limit)
nvl(to_number(aei_information20), 0), -- Political (100%)
nvl(to_number(aei_information21), 0), -- Political (Obsolete)
nvl(to_number(aei_information22), 0),
nvl(to_number(aei_information23), 0),
-- Bug 3966549
nvl(to_number(aei_information30), 0), -- ESOA Donation
-- End of 3966549
aei_information27,
aei_information28,
aei_information29,
-- Bug 3172960
nvl(to_number(aei_information24), 0)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select nvl(to_number(aei_information2), 0) -- Bug 3971542 Employee's occupational training (educational) expense
,nvl(to_number(aei_information3), 0) -- Bug 4704848
,nvl(to_number(aei_information4), 0)
,nvl(to_number(aei_information5), 0)
,nvl(aei_information10,'N') -- Bug 6737106 Check Box Medical Total and Details
,nvl(to_number(aei_information11), 0) -- Bug 7260606
,nvl(to_number(aei_information7), 0) -- Bug 7142612: Public Legal Entity Donation Trust
,fnd_date.canonical_to_date(aei_information12) -- Bug 8237227
,nvl(to_number(aei_information13), 0) -- Bug 8237227
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select
fnd_date.canonical_to_date(aei_information2),
fnd_date.canonical_to_date(aei_information3),
nvl(to_number(aei_information4), 0),
aei_information5, -- Bug 13247926
nvl(to_number(aei_information6),0), -- Bug 14754836
nvl(to_number(aei_information7),0) -- Bug 14754836
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_SP_TAX_EXEM_INFO3'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select sum(cei_information1),
sum(cei_information2),
sum(cei_information10),
sum(cei_information11)
from pay_kr_cont_details_v pkc,
per_contact_extra_info_f cei
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 nvl(sum(decode(aei_information7,0,(nvl(aei_information3,0) + nvl(aei_information11,0)))),0) -- Employee Total
,nvl(sum(decode(aei_information7,0,0,decode(aei_information9,'A',(nvl(aei_information3,0) + nvl(aei_information11,0))))),0) -- Disabled Total
--
-- Bug 9079450: The medical expense exemption for aged dependents
-- will be given if age is 65 or older.
,nvl(sum(decode(aei_information7,0,0,decode(aei_information9,'B',
decode(sign(to_char(p_effective_date, 'YYYY')-2008),1,
decode(pay_kr_ff_functions_pkg.aged_flag(aei_information8, fnd_date.canonical_to_date(to_char(p_effective_date, 'YYYY')||'/12/31')),'Y', (nvl(aei_information3,0) + nvl(aei_information11,0)),0
),(nvl(aei_information3,0) + nvl(aei_information11,0))
)
)
)
),0
) -- Aged Total
,nvl(sum(decode(aei_information9,'A',0,'B',0,
decode(aei_information7,0,0,(nvl(aei_information3,0) + nvl(aei_information11,0))))),0) -- Dependent Total
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
Select 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,'Y',0,'N','N') create_donation_record
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 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 trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select aei_information2,
aei_information3,
sum(nvl(to_number(aei_information4), 0)), -- Bug 9348911
aei_information7, -- Bug 9079450: Contact Name
aei_information5 -- Bug 9079450: Contact National Identifier
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
group by aei_information5,aei_information7,aei_information3,aei_information2; -- Bug 9348911
select nvl(to_number(aei_information2), 0),
nvl(to_number(aei_information3), 0)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_FW_TAX_EXEM_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select aei_information2,
nvl(to_number(aei_information3), 0)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_HOU_EXP_TAX_EXEM_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select nvl(to_number(aei_information2), 0),
nvl(to_number(aei_information3), 0),
nvl(to_number(aei_information14), 0), -- Bug 4750653
nvl(to_number(aei_information4), 0),
nvl(to_number(aei_information5), 0),
nvl(to_number(aei_information6), 0),
nvl(to_number(aei_information25), 0), -- Bug 8237227
-- Modified for Bug# 2706537
nvl(to_number(aei_information7), 0) + nvl(to_number(aei_information9), 0), -- emp credit card + dpnt credit card
-- Added for fix 2879008
-- Bug 3966549
-- Added second term for Bug 3966549
nvl(to_number(aei_information10),0) + nvl(to_number(aei_information11), 0), -- employee's direct payment + dependents' direct payments
nvl(to_number(aei_information7), 0) + nvl(to_number(aei_information10), 0), -- emp credit card + emp direct payment
nvl(to_number(aei_information9), 0) + nvl(to_number(aei_information11), 0), -- dpdnt credit card + dpdnt direct payment
nvl(to_number(aei_information12), 0), -- giro tuition paid
-- End of 3966549
-- Bug No 3506168
nvl(to_number(aei_information13), 0), -- Cash Receipt expenses
--
nvl(to_number(aei_information8), 0),
nvl(to_number(aei_information15), 0),
nvl(to_number(aei_information28), 0), -- Bug 9737699
nvl(to_number(aei_information29), 0), -- Bug 9737699
nvl(to_number(aei_information30), 0), -- Bug 9737699
nvl(to_number(aei_information16), 0), -- Total Medical Expense Paid in Cards Bug 6630135
nvl(to_number(aei_information17), 0), -- Medical Expense Paid in Cards For Dependents who are not eligible for Basic Exemption Bug 6630135
nvl(to_number(aei_information20), 0), -- Small Business Installment Amount Bug 6895093
nvl(to_number(aei_information21), 0), -- Bug 7615517: Company Related Expense
nvl(to_number(aei_information22), 0), -- Bug 7615517: Long Term Stock Fund for 1st Year
nvl(to_number(aei_information23), 0), -- Bug 7615517: Long Term Stock Fund for 2nd Year
nvl(to_number(aei_information24), 0), -- Bug 7615517: Long Term Stock Fund for 3rd Year
nvl(to_number(aei_information26), 0), -- Bug 9079450: Working Days for the Current Period
nvl(to_number(aei_information27), 0) -- Bug 9079450: Working Days for the Previous Period
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_TAX_EXEM_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select
nvl(to_number(aei_information3), 0) + nvl(to_number(aei_information4), 0), -- TM emp credit card + TM dpnt credit card
nvl(to_number(aei_information7),0) + nvl(to_number(aei_information8), 0), -- TM emp direct payment + TM dpnt direct payments
nvl(to_number(aei_information3), 0) + nvl(to_number(aei_information7), 0), -- TM emp credit card + TM emp direct card
nvl(to_number(aei_information4), 0) + nvl(to_number(aei_information8), 0), -- TM dpdnt credit card + TM dpdnt direct card
nvl(to_number(aei_information5), 0) + nvl(to_number(aei_information6), 0), -- TM emp Cash Receipt + TM dpnt Cash Receipt
nvl(to_number(aei_information9), 0), -- giro tuition paid
nvl(to_number(aei_information10), 0), -- Invest_Partner_Fin_2011
nvl(to_number(aei_information11), 0), -- Invest_Partner_Fin_2012_Ordinary
nvl(to_number(aei_information12), 0) -- Invest_Partner_Fin_2012_Venture
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_TAX_EXEM_INFO2'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
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(sum(cei_information7),0), -- Dependent Cards(NTS) Expense
nvl(sum(cei_information8),0), -- Dependent Cards(Other) Expense
nvl(sum(cei_information16),0), -- Dependent Direct(NTS) Expense
nvl(sum(cei_information17),0), -- Dependent Direct(Others) Expense
nvl(sum(cei_information9),0), -- Dependent Cash(NTS) Expense
nvl(sum(cei_information18),0), -- BUG 14219478 : Traditional Market Dpnt Credit Card(NTS)
nvl(sum(cei_information19),0), -- BUG 14219478 : Traditional Market Dpnt Credit Card(Others)
nvl(sum(cei_information20),0), -- BUG 14219478 : Traditional Market Dpnt Cash Receipt Expense(NTS)
nvl(sum(cei_information21),0), -- BUG 14219478 : Traditional Market Dpnt Direct Payment(NTS)
nvl(sum(cei_information22),0), -- BUG 14219478 : Traditional Market Dpnt Direct Payment(Others)
nvl(sum(cei_information23),0), -- BUG 14219478 : Dpnt Tution Paid in GIRO(NTS)
nvl(sum(cei_information24),0) -- BUG 14219478 : Dpnt Tution Paid in GIRO(Others)
from pay_kr_cont_details_v pkc,
per_contact_extra_info_f cei
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 nvl(to_number(aei_information2), 0),
nvl(to_number(aei_information3), 0),
nvl(to_number(aei_information4), 0),
nvl(to_number(aei_information5), 0)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_TAX_BREAK_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select fnd_date.canonical_to_date(aei_information1),
aei_information2,
aei_information3,
nvl(to_number(aei_information4), 0),
nvl(to_number(aei_information5), 0),
nvl(to_number(aei_information6), 0),
nvl(to_number(aei_information7), 0),
nvl(to_number(aei_information8), 0),
fnd_date.canonical_to_date(aei_information9),
fnd_date.canonical_to_date(aei_information10)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_OVS_TAX_BREAK_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select aei_information1,
fnd_date.canonical_to_date(aei_information2),
fnd_date.canonical_to_date(aei_information3),
fnd_date.canonical_to_date(aei_information5),
fnd_date.canonical_to_date(aei_information6)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_FW_TAX_BREAK_INFO'
and p_effective_date between -- Bug 5083240: Consider end date to be last day of contract_expiry_date year
fnd_date.canonical_to_date(aei_information2)
and (add_months(trunc(fnd_date.canonical_to_date(aei_information3), 'YYYY'), 12) - 1) ;
select BAL_TYPE.balance_name,
nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)
from pay_balance_feeds_f FEED,
pay_balance_types BAL_TYPE,
pay_run_result_values TARGET,
pay_run_results RR,
pay_payroll_actions PACT,
pay_assignment_actions ASSACT,
pay_payroll_actions BACT,
pay_assignment_actions BAL_ASSACT,
pay_run_types_f RTYPE
where BAL_ASSACT.assignment_action_id = p_assignment_action_id
and BAL_TYPE.balance_name in ('TOTAL_TAXABLE_EARNINGS', 'TOTAL_NON_TAXABLE_EARNINGS')
and BAL_TYPE.legislation_code = 'KR'
and FEED.balance_type_id = BAL_TYPE.balance_type_id
and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
and ASSACT.assignment_id = BAL_ASSACT.assignment_id
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and PACT.payroll_action_id = ASSACT.payroll_action_id
and trunc(PACT.effective_date, 'YYYY') = trunc(BACT.effective_date, 'YYYY')
and PACT.effective_date between p_contr_start_date
and
(add_months(trunc(p_contr_expiry_date, 'MONTH'), 1) - 1 )
and months_between(PACT.effective_date,p_contr_start_date)/l_pay_periods_per_year < l_fw_tax_duration
and (
RTYPE.run_type_name = 'MTH'
or
RTYPE.run_type_name like 'BON\_%' escape '\'
)
and RTYPE.legislation_code = 'KR'
and PACT.effective_date between RTYPE.effective_start_date
and RTYPE.effective_end_date
and PACT.run_type_id = RTYPE.run_type_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P', 'PA')
and TARGET.run_result_id = RR.run_result_id
and nvl(TARGET.result_value, '0') <> '0'
and FEED.input_value_id = TARGET.input_value_id
and PACT.effective_date between FEED.effective_start_date
and
FEED.effective_end_date
group by
BAL_TYPE.balance_name ;
select to_number(glb.global_value,'99999999999999999999.99999') -- Bug 5726158
from ff_globals_f glb
where glb.global_name = p_glbvar
and p_process_date between glb.effective_start_date and glb.effective_end_date;
select fnd_date.canonical_to_date(aei_information1),
fnd_date.canonical_to_date(aei_information2)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_information_type;
select pds.DATE_START, pds.ACTUAL_TERMINATION_DATE
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 pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pbt.balance_name = p_balance_name
and pbt.legislation_code = 'KR'
and pbd.legislation_code = 'KR'
and pdb.legislation_code = 'KR'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.database_item_suffix = '_ASG_YTD';
select
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.source_action_id is null
and ppa.business_group_id = p_business_group_id
and ppa.payroll_id = p_payroll_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('B', 'I', 'V', 'R', 'Q')
and paa.assignment_action_id <> p_assignment_action_id
and ppa.effective_date between trunc(l_effective_date, 'YYYY') and l_effective_date
order by paa.action_sequence desc;
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_effective_date
between pel.effective_start_date and pel.effective_end_date
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_effective_date
between pee.effective_start_date and pee.effective_end_date
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; -- Bug 7142620
Select aei_information1
From per_assignment_extra_info
Where assignment_id = p_assignment_id
And information_type = 'KR_YEA_FOREIGN_WORKER_TAX';
select
u.user_entity_id,
a.value
from pay_report_format_items_f i,
ff_database_items d,
ff_user_entities u,
ff_archive_items a,
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and a.context1 = paa.assignment_action_id
and a.value is not null
and u.user_entity_id = a.user_entity_id
and d.user_entity_id = u.user_entity_id
--
-- Default value for data type number is "0" in t_yea_info
--
and decode(a.value, '0', decode(d.data_type, 'N', 'N', 'Y'), 'Y') = 'Y'
and i.report_type = nvl(ppa.report_type, u.user_entity_name)
and i.report_qualifier = ppa.report_qualifier
and i.report_category = ppa.report_category
and i.user_entity_id = u.user_entity_id
and ppa.effective_date
between i.effective_start_date and i.effective_end_date
order by i.display_sequence;
select paa.assignment_id,
ppa.effective_date
into l_assignment_id,
l_effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select
pa.business_group_id,
pa.effective_start_date,
pa.effective_end_date,
pa.payroll_id
from per_assignments_f pa
where pa.assignment_id = p_assignment_id
and pa.assignment_type = 'E'
and pa.effective_start_date <= p_effective_date
and pa.effective_end_date >= trunc(p_effective_date, 'YYYY')
order by pa.effective_start_date desc
for update of pa.assignment_id;
select
'Y'
from dual
where exists(
select null
from pay_action_classifications pac,
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.action_status not in ('C', 'S') -- Bug 4442484: A 'S'kipped assact is not an errored one
and paa.assignment_action_id <> p_bal_asg_action_id
and paa.source_action_id is null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date <= p_effective_date
and pac.action_type = ppa.action_type
and pac.classification_name = 'SEQUENCED');
select ppa.effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.source_action_id is null
and ppa.action_type IN ('X','B')
and ppa.report_type = 'YEA'
and ppa.report_qualifier = 'KR'
and ppa.report_category IN ('N','I')
and trunc(ppa.effective_date, 'YYYY') = trunc(p_effective_date, 'YYYY')
order by paa.action_sequence desc;
select ppa.effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_id = p_payroll_id -- Bug 5045110
and paa.assignment_action_id <> p_bal_asg_action_id
and paa.source_action_id is null
and ppa.action_type = 'B'
and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_TYPE', null) = 'YEA'
and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_QUALIFIER', null) = 'KR'
and pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_CATEGORY', null) IN ('N','I')
and ppa.effective_date between trunc(p_effective_date, 'YYYY')
and (trunc(add_months(p_effective_date, 12), 'YYYY') - 1) -- Bug 5045110
order by paa.action_sequence desc;
insert into pay_message_lines(
LINE_SEQUENCE,
PAYROLL_ID,
MESSAGE_LEVEL,
SOURCE_ID,
SOURCE_TYPE,
LINE_TEXT)
values( pay_message_lines_s.nextval,
p_payroll_id,
'F',
p_assignment_action_id,
'A',
fnd_message.get);
if p_archive_type_used <> 'AAP' then -- old archive, delete manually.
delete from ff_archive_items
where CONTEXT1 = p_assignment_action_id
and ARCHIVE_TYPE = 'AAC';
-- Insert into FF_ARCHIVE_ITEMS
------------------------------------------------------------------------
forall i in 1..l_user_entity_id_tbl.count
insert into ff_archive_items(
ARCHIVE_ITEM_ID,
USER_ENTITY_ID,
CONTEXT1,
VALUE,
ARCHIVE_TYPE)
values( ff_archive_items_s.nextval,
l_user_entity_id_tbl(i),
p_assignment_action_id,
l_archive_item_value_tbl(i),
p_archive_type_used); -- Bug 5036734
-- Insert into PAY_MESSAGE_LINES
------------------------------------------------------------------------
forall i in 1..l_message_level_tbl.count
insert into pay_message_lines(
LINE_SEQUENCE,
PAYROLL_ID,
MESSAGE_LEVEL,
SOURCE_ID,
SOURCE_TYPE,
LINE_TEXT)
values( pay_message_lines_s.nextval,
p_payroll_id,
l_message_level_tbl(i),
p_assignment_action_id,
'A',
l_message_text_tbl(i));
select pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'REPORT_CATEGORY', null) report_category
,ppa.element_type_id element_type_id
,ppa.payroll_id payroll_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
select
u.creator_id
into l_defined_balance_id
from ff_user_entities u,
ff_database_items d
where d.user_name = p_user_name
and u.user_entity_id = d.user_entity_id
and u.legislation_code = 'KR'
and u.business_group_id is null
and u.creator_type = 'B';
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;
l_user_entity_id_tbl.delete(l_index);
l_user_entity_name_tbl.delete(l_index);
select
distinct
u.user_entity_id,
u.user_entity_name
bulk collect into
l_user_entity_id_tbl,
l_user_entity_name_tbl
from ff_user_entities u,
pay_report_format_items_f i
where i.report_type = 'YEA'
and i.report_qualifier = 'KR'
and u.user_entity_id = i.user_entity_id;