[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 fnd_date.canonical_to_date(aei_information1),
aei_information2,
hr_ni_chk_pkg.chk_nat_id_format(aei_information3, 'DDD-DD-DDDDD'),
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),
nvl(to_number(aei_information9), 0),
nvl(to_number(aei_information10), 0),
nvl(to_number(aei_information18), 0), -- Bug 7260606
nvl(to_number(aei_information11), 0),
nvl(to_number(aei_information12), 0),
nvl(to_number(aei_information13), 0),
nvl(to_number(aei_information14), 0),
nvl(to_number(aei_information15), 0),
nvl(to_number(aei_information16), 0), -- Bug 6024342
nvl(to_number(aei_information17), 0) -- Bug 6024342
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_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),
nvl(to_number(aei_information19), 0),
nvl(to_number(aei_information20), 0),
nvl(to_number(aei_information21), 0),
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
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 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.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_information9,'A',0,'B',0,decode(aei_information7,0,(nvl(aei_information3,0) + nvl(aei_information11,0))))),0) -- Employee Total
,nvl(sum(decode(aei_information9,'A',(nvl(aei_information3,0) + nvl(aei_information11,0)))),0) -- Disabled Total
,nvl(sum(decode(aei_information9,'B',(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 aei_information2,
aei_information3,
nvl(to_number(aei_information4), 0)
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');
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),
-- Modified for Bug# 2706537
nvl(to_number(aei_information7), 0) + nvl(to_number(aei_information9), 0),
-- 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 card
nvl(to_number(aei_information9), 0) + nvl(to_number(aei_information11), 0), -- dpdnt credit card + dpdnt direct card
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_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
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(sum(cei_information7),0), -- Dependent Cards(NTS) Expense
nvl(sum(cei_information8),0), -- Dependent Cards(Other) Expense
nvl(sum(cei_information9),0) -- Dependent Cash(NTS) Expense
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.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 (
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
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(p_effective_date, 'YYYY') and p_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;
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;