[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_SESSION_DATE(
P_EFFECTIVE_DATE IN DATE)
IS
BEGIN
delete_session_date;
insert into fnd_sessions(session_id,effective_date)
select userenv('sessionid'),trunc(p_effective_date)
from dual;
END INSERT_SESSION_DATE;
PROCEDURE DELETE_SESSION_DATE
IS
BEGIN
delete from fnd_sessions where session_id=userenv('sessionid');
END DELETE_SESSION_DATE;
select pp.last_name EE_LAST_NAME_KANA,
pp.per_information18 EE_LAST_NAME,
pcon.last_name CON_LAST_NAME_KANA,
pcon.first_name CON_FIRST_NAME_KANA,
pcon.per_information18 CON_LAST_NAME,
pcon.per_information19 CON_FIRST_NAME,
decode(pcon.sex,'M',1,'F',2,3) SEX_ORDER,
pcon.date_of_birth DATE_OF_BIRTH,
decode(pcr.contact_type,'S',decode(pcon.sex,'F',fnd_message.get_string('PAY','PAY_JP_WIFE'),fnd_message.get_string('PAY','PAY_JP_HUSBAND')),flv1.meaning) CONTACT_TYPE,
decode(pcr.contact_type,'S',decode(pcon.sex,'F',fnd_message.get_string('PAY','PAY_JP_WIFE_KANA'),fnd_message.get_string('PAY','PAY_JP_HUSBAND_KANA')),flv2.meaning) CONTACT_TYPE_KANA
from
hr_lookups flv2,
hr_lookups flv1,
per_all_people_f pcon,
per_contact_relationships pcr,
per_all_people_f pp
where pp.person_id=p_person_id
and p_effective_date
between pp.effective_start_date and pp.effective_end_date
and pcr.person_id=pp.person_id
and pcr.dependent_flag='Y'
and p_effective_date
between pcr.date_start and nvl(pcr.date_end,to_date('4712-12-31','YYYY-MM-DD'))
and pcon.person_id=pcr.contact_person_id
and ( (p_effective_date
between pcon.effective_start_date and pcon.effective_end_date)
or (not exists(
select NULL
from per_all_people_f pcon2
where pcon2.person_id=pcon.person_id
and p_effective_date
between pcon2.effective_start_date and pcon2.effective_end_date)
and pcon.effective_start_date=pcon.start_date))
and flv1.lookup_type='CONTACT'
and flv1.lookup_code=pcr.contact_type
and flv2.lookup_type(+)='JP_CONTACT_KANA'
and flv2.lookup_code(+)=pcr.contact_type
order by 8,7,3,4;
select max(to_number(value))
from pay_user_tables put,
pay_user_columns puc,
pay_user_column_instances_f puci
where put.user_table_name = p_user_table_name
and puc.user_table_id = put.user_table_id
and puc.user_column_name = p_udt_column_name
and puci.user_column_id = puc.user_column_id
and p_effective_date
between puci.effective_start_date and puci.effective_end_date;
select min(to_number(value))
from pay_user_tables put,
pay_user_columns puc,
pay_user_column_instances_f puci
where put.user_table_name = p_user_table_name
and puc.user_table_id = put.user_table_id
and puc.user_column_name = p_udt_column_name
and puci.user_column_id = puc.user_column_id
and p_effective_date
between puci.effective_start_date and puci.effective_end_date;
select v1.business_group_id,
v1.itax_organization_id,
v1.effective_date,
v1.date_earned,
v1.assignment_id,
v1.action_sequence
from pay_jp_pre_itax_v1 v1
where v1.business_group_id = p_business_group_id
and to_char(v1.effective_date, 'YYYY') = p_year
and v1.assignment_id = p_assignment_id
--
and v1.itax_organization_id <> p_itax_organization_id
and v1.action_sequence < p_action_sequence
order by v1.date_earned desc;
select /* Removed the hint as per Bug# 4767108 */
nvl(sum(decode(pai.action_information13, 'TERM',
NULL, decode(pai.action_information21, cp_itax_organization_id,
pai.action_information2 + pai.action_information3, NULL ))),0) PREV_SWOT_TAXABLE_AMT,
nvl(sum(decode(pai.action_information13, 'TERM',
NULL, decode(pai.action_information21, cp_itax_organization_id,
pai.action_information24 + pai.action_information25, NULL))),0) PREV_SWOT_ITAX,
nvl(sum(decode(pai.action_information13, 'TERM',
NULL, decode(pai.action_information21, cp_itax_organization_id,
pai.action_information6 + pai.action_information9 + pai.action_information12 + pai.action_information20 + pai.action_information14, NULL))),0) PREV_SWOT_SI_PREM,
nvl(sum(decode(pai.action_information13, 'TERM',
NULL, decode(pai.action_information21, cp_itax_organization_id, pai.action_information14, NULL))), 0) PREV_SWOT_MUTUAL_AID
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_information pai,
per_all_assignments_f pa
where paa.assignment_id = p_assignment_id
/* Below conditions have already been taken care in Pre-Tax Archiver
process. So they are redundant here and removed.
for Bug# 5033800 */
-- and paa.action_status = 'C'
-- and ppa.action_type in ('R', 'Q', 'B', 'I')
/* Below conditions were removed, as they are redundant ones.
for Bug# 5033800 */
-- and pai.action_context_type = 'AAP'
-- and pai.assignment_id = pass.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and to_char(ppa.effective_date, 'YYYY') = p_year
and pai.action_information_category = 'JP_PRE_TAX_1'
and pai.action_information1 = paa.assignment_action_id
and ((pai.action_information13 in ('SALARY', 'BONUS', 'SP_BONUS', 'YEA', 'RE_YEA')
and paa.action_sequence <= cp_action_sequence)
or
(pai.action_information13 = 'TERM'))
and pai.action_information22 in ('M_KOU', 'M_OTSU', 'D_KOU', 'D_OTSU', 'D_HEI')
and pa.assignment_id = paa.assignment_id
and ppa.effective_date between pa.effective_start_date and pa.effective_end_date
--
and not exists(
select NULL /* Removed the hint as per Bug# 5033800 */
from pay_action_interlocks pai2,
pay_assignment_actions paa2,
pay_payroll_actions ppa2
where pai2.locked_action_id = paa.assignment_action_id
and paa2.assignment_action_id = pai2.locking_action_id
and ppa2.payroll_action_id = paa2.payroll_action_id
and ppa2.action_type = 'V');
select /* Removed the hint as per Bug# 4767108 */
decode(p_kanji_flag,
'1',hoi.org_information1,hoi.org_information2) EMPLOYER_NAME,
pay_jp_report_pkg.substrb2(
decode(p_kanji_flag,
'1',hoi.org_information6||hoi.org_information7||hoi.org_information8,
hoi.org_information9||hoi.org_information10||hoi.org_information11),1,255) EMPLOYER_ADDRESS,
peev.effective_end_date PREV_SWOT_TERM_DATE
from hr_organization_information hoi,
pay_element_entry_values_f peev,
pay_element_entries_f pee
where hoi.organization_id(+) = cp_itax_organization_id
and hoi.org_information_context(+) = 'JP_TAX_SWOT_INFO'
-- Previous SWOT term date
and cp_date_earned between peev.effective_start_date and peev.effective_end_date
and peev.input_value_id = p_swot_iv_id
and peev.screen_entry_value = hoi.organization_id
and pee.element_entry_id = peev.element_entry_id
and pee.assignment_id = p_assignment_id
and pee.effective_start_date = peev.effective_start_date
and pee.effective_end_date = peev.effective_end_date;
select /*+ ORDERED
NO_MERGE(entry_type_v)
INDEX(TAXABLE_AMT PAY_ELEMENT_ENTRY_VALUES_F_N50)
INDEX(ITAX PAY_ELEMENT_ENTRY_VALUES_F_N50)
INDEX(SI_PREM PAY_ELEMENT_ENTRY_VALUES_F_N50)
INDEX(MUTUAL_AID PAY_ELEMENT_ENTRY_VALUES_F_N50)
INDEX(TERM_DATE PAY_ELEMENT_ENTRY_VALUES_F_N50)
INDEX(ADDR PAY_ELEMENT_ENTRY_VALUES_F_N50)
INDEX(EMPLOYER_NAME PAY_ELEMENT_ENTRY_VALUES_F_N50) */
nvl(taxable_amt.screen_entry_value,0) PJOB_TAXABLE_AMT,
nvl(itax.screen_entry_value,0) PJOB_ITAX,
nvl(si_prem.screen_entry_value,0) PJOB_SI_PREM,
nvl(mutual_aid.screen_entry_value,0) PJOB_MUTUAL_AID,
fnd_date.canonical_to_date(term_date.screen_entry_value) PJOB_TERM_DATE,
addr.screen_entry_value PJOB_ADDR,
employer_name.screen_entry_value PJOB_EMPLOYER_NAME
from (select /*+ ORDERED
INDEX(PETF PAY_ELEMENT_TYPES_F_PK)
INDEX(PELF PAY_ELEMENT_LINKS_F_N7)
INDEX(PEEF PAY_ELEMENT_ENTRIES_F_N51) */
peef.element_entry_id
from pay_element_types_f petf,
pay_element_links_f pelf,
pay_element_entries_f peef
where petf.element_type_id = p_pjob_ele_type_id
and pelf.element_type_id = petf.element_type_id
and pelf.business_group_id +0 = p_business_group_id
and peef.element_link_id = pelf.element_link_id
and peef.assignment_id = p_assignment_id) entry_type_v,
pay_element_entry_values_f taxable_amt,
pay_element_entry_values_f itax,
pay_element_entry_values_f si_prem,
pay_element_entry_values_f mutual_aid,
pay_element_entry_values_f term_date,
pay_element_entry_values_f addr,
pay_element_entry_values_f employer_name
where taxable_amt.element_entry_id = entry_type_v.element_entry_id
and taxable_amt.input_value_id = p_taxable_amt_iv_id
and p_effective_date
between taxable_amt.effective_start_date and taxable_amt.effective_end_date
and itax.element_entry_id = entry_type_v.element_entry_id
and itax.input_value_id = p_itax_iv_id
and p_effective_date
between itax.effective_start_date and itax.effective_end_date
and si_prem.element_entry_id = entry_type_v.element_entry_id
and si_prem.input_value_id = p_si_prem_iv_id
and p_effective_date
between si_prem.effective_start_date and si_prem.effective_end_date
and mutual_aid.element_entry_id = entry_type_v.element_entry_id
and mutual_aid.input_value_id = p_mutual_aid_iv_id
and p_effective_date
between mutual_aid.effective_start_date and mutual_aid.effective_end_date
and term_date.element_entry_id = entry_type_v.element_entry_id
and term_date.input_value_id = p_term_date_iv_id
and p_effective_date
between term_date.effective_start_date and term_date.effective_end_date
and addr.element_entry_id = entry_type_v.element_entry_id
and addr.input_value_id = p_addr_iv_id
and p_effective_date
between addr.effective_start_date and addr.effective_end_date
and employer_name.element_entry_id = entry_type_v.element_entry_id
and employer_name.input_value_id = p_employer_name_iv_id
and p_effective_date
between employer_name.effective_start_date and employer_name.effective_end_date
order by pjob_term_date desc;
SELECT /*+ ORDERED
INDEX(PCR PER_CONTACT_RELATIONSHIPS_N2)
INDEX(PCEIF PER_CONTACT_EXTRA_INFO_N1)
INDEX(PAPF PER_PEOPLE_F_PK) */
DECODE(pceif.cei_information7,
NULL,SUBSTRB(papf.per_information18 || ' ' || papf.per_information19 ||
DECODE(pceif.cei_information6,
'20', '(' ||fnd_message.get_string('PAY','PAY_JP_LIVING_SEPARATELY') || ')',
'30', '(' ||fnd_message.get_string('PAY','PAY_JP_LIVING_TOGETHER') || ')' , NULL), 1, 2000),
SUBSTRB(papf.per_information18 || ' ' || papf.per_information19 || ' (' || pceif.cei_information7 ||
DECODE(pceif.cei_information6,
'20', ', ' || fnd_message.get_string('PAY','PAY_JP_LIVING_SEPARATELY'),
'30', ', ' || fnd_message.get_string('PAY','PAY_JP_LIVING_TOGETHER'), NULL) || ')',1,2000)) details
FROM per_contact_relationships pcr,
per_contact_extra_info_f pceif,
per_all_people_f papf
WHERE pcr.person_id = p_person_id
AND pcr.cont_information_category = 'JP'
AND pcr.cont_information1 = 'Y'
AND p_effective_date
BETWEEN NVL(pcr.date_start, p_effective_date) AND NVL(pcr.date_end, p_effective_date)
AND pceif.contact_relationship_id = pcr.contact_relationship_id
AND pceif.information_type = 'JP_ITAX_DEPENDENT'
AND pceif.cei_information6 <> '0'
AND p_effective_date
BETWEEN pceif.effective_start_date AND pceif.effective_end_date
AND papf.person_id = pcr.contact_person_id
AND p_effective_date
BETWEEN papf.effective_start_date AND papf.effective_end_date
ORDER BY pcr.cont_information2,
papf.date_of_birth;
SELECT 'Y'
FROM dual
WHERE EXISTS(
SELECT /*+ ORDERED */
NULL
FROM per_contact_relationships pcr,
per_contact_extra_info_f pceif
WHERE pcr.person_id = p_person_id
AND pcr.cont_information_category = 'JP'
AND pcr.cont_information1 = 'Y'
AND p_effective_date
BETWEEN NVL(pcr.date_start, p_effective_date) AND NVL(pcr.date_end, p_effective_date)
AND pceif.contact_relationship_id = pcr.contact_relationship_id
AND pceif.information_type LIKE 'JP_HI%'
AND p_effective_date
between pceif.effective_start_date and pceif.effective_end_date
AND p_effective_date
between DECODE(pceif.information_type,
'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information3),
'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information1),
null)
and nvl(DECODE(pceif.information_type,
'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information10),
'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information6),
null),pceif.effective_end_date));
select count(pcr.person_id)
from per_contact_relationships pcr
where pcr.person_id = p_person_id
and pcr.cont_information_category = 'JP'
and pcr.cont_information1 = 'Y'
and p_effective_date
between nvl(pcr.date_start, p_effective_date) and nvl(pcr.date_end, p_effective_date)
and exists(
select null
from per_contact_extra_info_f pceif
where pceif.contact_relationship_id = pcr.contact_relationship_id
and p_effective_date
between pceif.effective_start_date and pceif.effective_end_date
and pceif.information_type like 'JP_HI%'
and p_effective_date
between decode(pceif.information_type,
'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information3),
'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information1),
null)
and nvl(DECODE(pceif.information_type,
'JP_HI_SPOUSE', fnd_date.canonical_to_date(pceif.cei_information10),
'JP_HI_DEPENDENT', fnd_date.canonical_to_date(pceif.cei_information6),
null),pceif.effective_end_date));
select hoi.org_information1
from hr_organization_information hoi
where hoi.organization_id = p_business_group_id
and hoi.org_information_context = 'JP_BUSINESS_GROUP_INFO';
SELECT 1 FROM per_jp_si_dependent_transfer_v
WHERE person_id = p_person_id
AND dependent_type IN ('S', 'D')
AND transfer_type = 'I'
AND TRUNC(transfer_date) <> p_qualified_date;
SELECT 2 FROM per_jp_si_dependent_transfer_v
WHERE person_id = p_person_id
AND dependent_type IN ('S', 'D')
AND transfer_type = 'E';
SELECT 4 FROM per_jp_si_dependent_transfer_v
WHERE person_id = p_person_id
AND dependent_type = '3'
AND transfer_type = 'I'
AND TRUNC(transfer_date) <> p_qualified_date;
SELECT 8 FROM per_jp_si_dependent_transfer_v
WHERE person_id = p_person_id
AND dependent_type = '3'
AND transfer_type = 'E'
AND type3_disqualified_notice = 'Y';
SELECT transfer_date
FROM per_jp_si_dependent_transfer_v
WHERE person_id = p_person_id
AND DECODE(transfer_type, 'I', transfer_date, 'E', transfer_date + 1) BETWEEN p_date_from AND p_date_to
AND (p_report_type = '0'
OR (p_report_type = '10'
AND dependent_type IN ('S', 'D'))
OR (p_report_type = '20'
AND dependent_type = '3'))
ORDER BY transfer_date DESC;
select assignment_set_name
from hr_assignment_sets
where assignment_set_id = p_assignment_set_id;
select pes.element_set_id
from pay_element_sets pes
where pes.legislation_code = c_legislation_code
and pes.element_set_name = l_ele_set;
select /*+ ORDERED
USE_NL(PLIV1, PLIV2, PEE, PEEV1, PEEV2)
INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV1)
INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV2)
INDEX(PAY_ELEMENT_ENTRIES_F_N51 PEE)
INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV1)
INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV2) */
pee.element_entry_id ee_id,
pee.effective_start_date ee_esd,
pee.effective_end_date ee_eed,
peev1.screen_entry_value applied_mth,
peev2.screen_entry_value new_std_mth_comp
from pay_link_input_values_f pliv1,
pay_link_input_values_f pliv2,
pay_element_entries_f pee,
pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2
where pliv1.input_value_id = p_applied_mth_iv_id
and pliv2.input_value_id = p_new_std_mth_comp_iv_id
and pee.assignment_id = p_assignment_id
/* use not eed but esd to include entry data as qualification */
/* DBItem Entry is referred by date earned, */
/* but if update recurring has been occurred, */
/* all future entry are updating from effective date. */
/* therefore, don't need to include future entry till date earned. */
and pee.entry_type = 'E'
and pee.effective_start_date < p_effective_date
and pee.element_link_id = pliv1.element_link_id
and pee.element_link_id = pliv2.element_link_id
and pee.effective_start_date
between pliv1.effective_start_date and pliv1.effective_end_date
and pee.effective_start_date
between pliv2.effective_start_date and pliv2.effective_end_date
and peev1.element_entry_id = pee.element_entry_id
and peev1.input_value_id = pliv1.input_value_id
and peev1.effective_start_date = pee.effective_start_date
and peev1.effective_end_date = pee.effective_end_date
and peev2.element_entry_id = pee.element_entry_id
and peev2.input_value_id = pliv2.input_value_id
and peev2.effective_start_date = pee.effective_start_date
and peev2.effective_end_date = pee.effective_end_date
order by pee.effective_start_date desc;
/* This case is for entry that is not updated(process) ie. qualificaiton data */
l_std_mth_comp_old := pay_jp_balance_pkg.get_entry_value_char(p_new_std_mth_comp_iv_id,p_assignment_id,p_date_earned);
select pett.element_name
from pay_element_types_f pet,
pay_element_types_f_tl pett
where pet.element_name = p_base_elm_name
and pett.element_type_id = pet.element_type_id
and pett.language = userenv('LANG');