[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT db.defined_balance_id
FROM pay_balance_types pbt
,pay_defined_balances db
,pay_balance_dimensions bd
WHERE pbt.balance_name = c_balance_name
AND pbt.balance_type_id = db.balance_type_id
AND bd.balance_dimension_id = db.balance_dimension_id
AND bd.dimension_name = c_dimension_name
AND (pbt.business_group_id = c_business_group_id OR
pbt.legislation_code = 'NL')
AND (db.business_group_id = pbt.business_group_id OR
db.legislation_code = 'NL');
SELECT *
FROM pqp_pension_types_f
WHERE c_date_earned BETWEEN effective_start_date
AND effective_end_date
AND business_group_id = c_business_group_id
AND pension_type_id = c_pension_type_id;
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'PQP_PENSION_SUB_CATEGORY'
AND lookup_code = c_sub_cat
AND language = 'US';
SELECT to_char(per.date_of_birth,'RRRR')
FROM per_all_people_f per,per_all_assignments_f paa
WHERE per.person_id = paa.person_id
AND p_date_earned between paa.effective_start_date and paa.effective_end_date
AND p_date_earned between per.effective_start_date and per.effective_end_date
AND paa.assignment_id = p_assignment_id;
SELECT NVL(EE_AGE_THRESHOLD,'N')
FROM pqp_pension_types_f
WHERE pension_type_id = c_pension_type_id
AND p_date_earned between effective_start_date and effective_end_date;
SELECT NVL(ER_AGE_THRESHOLD,'N')
FROM pqp_pension_types_f
WHERE pension_type_id = c_pension_type_id
AND p_date_earned between effective_start_date and effective_end_date;
g_pension_rec.DELETE;
g_pension_rec.DELETE(l_pension_id);
SELECT fnd_number.canonical_to_number(global_value)
FROM ff_globals_f
WHERE global_name = c_global_name
AND trunc(c_effective_date) BETWEEN effective_start_date AND effective_end_date
AND legislation_code = 'NL';
SELECT work_pattern
FROM pqp_assignment_attributes_f
WHERE assignment_id = c_assignment_id
AND trunc(c_effective_date) BETWEEN effective_start_date AND effective_end_date;
SELECT ptp.start_date,ptp.end_date
FROM per_all_assignments_f pasf,per_time_periods ptp
WHERE pasf.payroll_id = ptp.payroll_id
AND pasf.assignment_id = c_assignment_id
AND trunc(c_effective_date) BETWEEN ptp.start_date AND ptp.end_date;
SELECT min(asg.effective_start_date) , max(asg.effective_end_date)
FROM per_assignments_f asg,per_assignment_status_types past
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date <= c_period_end_dt
AND nvl(asg.effective_end_date, c_period_end_dt) >= c_period_start_dt
AND asg.assignment_id = c_assign_id;
SELECT
decode(asg.EFFECTIVE_END_DATE,to_date('31-12-4712','dd-mm-yyyy'),null,asg.EFFECTIVE_END_DATE)
FROM per_assignments_f asg,per_assignment_status_types past
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND trunc(c_period_start_dt) = asg.effective_start_date
AND asg.assignment_id = c_assign_id;
SELECT decode(c_term_date,to_date('31-12-4712','dd-mm-yyyy'),null,c_term_date)
FROM DUAL;
SELECT (c_end_date - c_start_date + 1)
FROM DUAL;
SELECT hoi.org_information5
FROM per_all_assignments_f paa,hr_organization_information hoi
WHERE paa.organization_id = hoi.organization_id
AND hoi.org_information_context='NL_ORG_INFORMATION'
AND paa.assignment_id = c_assignment_id
AND trunc(c_effective_date) between effective_start_date and effective_end_date;
SELECT SEGMENT26,segment27
FROM hr_soft_coding_keyflex hr_keyflex,per_assignments_f ASSIGN
WHERE ASSIGN.assignment_id = c_assignment_id
AND hr_keyflex.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND hr_keyflex.enabled_flag = 'Y'
AND trunc(c_effective_date) BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date;
SELECT aei_information1,aei_information2
FROM per_assignment_extra_info
WHERE information_type= 'NL_PAI'
AND assignment_id = c_assignment_id;
Select number_per_fiscal_year
from per_time_period_types
where period_type = c_period_type;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = p_assignment_id
AND ppa.action_status = 'C'
AND ppa.action_type IN ('Q','R')
AND paa.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = Nvl(p_payroll_id,ppa.payroll_id)
AND ppa.consolidation_set_id = ppa.consolidation_set_id
AND source_action_id IS NOT NULL
AND ppa.date_earned BETWEEN p_start_date AND p_end_date
AND ppa.effective_date <= p_date_earned;
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = c_asg_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_date_earned;
SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) pt_perc
FROM per_assignments_f asg
,hr_soft_coding_keyflex target
WHERE asg.assignment_id = p_assignment_id
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND trunc(c_effective_date) between asg.effective_start_date
AND asg.effective_end_date
AND target.enabled_flag = 'Y';
SELECT min(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT db.defined_balance_id
FROM pay_balance_types pbt
,pay_defined_balances db
,pay_balance_dimensions bd
WHERE pbt.balance_name = c_balance_name
AND pbt.balance_type_id = db.balance_type_id
AND bd.balance_dimension_id = db.balance_dimension_id
AND bd.dimension_name = c_dimension_name
AND (pbt.business_group_id = c_business_group_id OR
pbt.legislation_code = 'NL')
AND (db.business_group_id = pbt.business_group_id OR
db.legislation_code = 'NL');
SELECT fnd_number.canonical_to_number(global_value)
FROM ff_globals_f
WHERE global_name = c_global_name
AND trunc (p_date_earned) BETWEEN effective_start_date
AND effective_end_date;
SELECT cola.name
FROM per_collective_agreements cola
,per_all_assignments_f asg
WHERE asg.assignment_id = c_asg_id
AND asg.collective_agreement_id = cola.collective_agreement_id
AND cola.status = 'A'
AND c_eff_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND c_eff_date BETWEEN cola.start_date
AND NVL(cola.end_date,to_date('31/12/4712','DD/MM/YYYY'));
SELECT pety.number_per_fiscal_year
FROM pay_payrolls_f ppaf
,per_time_period_types pety
WHERE ppaf.payroll_id = p_payroll_id
AND ppaf.period_type = pety.period_type;
SELECT aei_information10
FROM per_assignment_extra_info
WHERE aei_information_category = 'NL_ABP_PAR_INFO'
AND information_type = 'NL_ABP_PAR_INFO'
AND assignment_id = p_assignment_id
AND aei_information10 IS NOT NULL
AND c_eff_date BETWEEN fnd_date.canonical_to_date(aei_information1)
AND fnd_date.canonical_to_date(nvl(aei_information2
,fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT hoi.org_information1
FROM hr_organization_information hoi
WHERE hoi.organization_id = c_org_id
AND hoi.org_information_context = 'PQP_NL_ABP_PTP_METHOD'
AND hoi.org_information1 IS NOT NULL;
SELECT aei_information11
FROM per_assignment_extra_info
WHERE aei_information_category = 'NL_ABP_PAR_INFO'
AND information_type = 'NL_ABP_PAR_INFO'
AND assignment_id = p_assignment_id
AND aei_information11 IS NOT NULL
AND c_eff_date BETWEEN fnd_date.canonical_to_date(aei_information1)
AND fnd_date.canonical_to_date(nvl(aei_information2
,fnd_date.date_to_canonical(hr_api.g_eot)));
SELECT hoi.org_information2
FROM hr_organization_information hoi
WHERE hoi.organization_id = c_org_id
AND hoi.org_information_context = 'PQP_NL_ABP_PTP_METHOD'
AND hoi.org_information2 IS NOT NULL;
select org_information1
from hr_organization_information
where organization_id = p_business_group_id
and org_information_context = 'NL_BG_INFO';
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where organization_structure_id = c_hierarchy_id
and c_eff_date between date_from
and nvl(date_to,hr_api.g_eot);
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where business_group_id = p_business_group_id
and c_eff_date between date_from
and nvl( date_to,hr_api.g_eot);
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND c_eff_date BETWEEN effective_start_date
AND effective_end_date;
select organization_id_parent
from per_org_structure_elements
where organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = p_business_group_id;
SELECT 1
FROM per_all_assignments_f asg
WHERE assignment_id = p_assignment_id
AND effective_start_date <= p_date_earned
AND assignment_status_type_id IN (SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
AND active_flag = 'Y');
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = c_asg_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_date_earned;
select pdb.defined_balance_id
from pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
where balance_name = p_salary_balance_name
and pbd.dimension_name = c_dim_name
and pdb.balance_type_id = pbt.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.legislation_code = 'NL';
SELECT time_period_id
,start_date
,end_date
FROM per_time_periods
WHERE payroll_id = c_payroll_id
AND trunc(start_date) >= c_effective_date
ORDER BY start_date;
SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND trunc(c_effective_date) BETWEEN effective_start_date
AND effective_end_date;
SELECT paa.assignment_action_id
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.time_period_id = c_time_period_id
--and payroll_id = c_payroll_id
and paa.assignment_id = p_assignment_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and ppa.date_earned between c_period_start and c_period_end
and rownum = 1;
SELECT 1
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND special_pension_type_code = 'ABP';
SELECT payroll_id
INTO l_payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND trunc(p_date_earned) BETWEEN effective_start_date
AND effective_end_date;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND trunc(p_date_earned) between effective_start_date and effective_end_date
AND business_group_id = p_business_group_id;
select 1 from dual
where exists (select 1 from hr_organization_information
where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
AND organization_id = c_org_id
AND p_date_earned between fnd_date.canonical_to_date(org_information4)
and fnd_date.canonical_to_date(nvl(org_information5,
fnd_date.date_to_canonical(hr_api.g_eot)))
);
select organization_id_parent
from per_org_structure_elements
where organization_id_child = c_org_id
AND org_structure_version_id = c_version_id
AND business_group_id = p_business_group_id;
SELECT 1
FROM hr_organization_information hoi,
pqp_pension_types_f pty
WHERE hoi.org_information_context = 'PQP_NL_ER_PENSION_TYPES'
AND hoi.org_information2 = TO_CHAR(p_pension_type_id)
AND hoi.organization_id = c_org_id
AND pty.pension_type_id = p_pension_type_id
AND p_date_earned between pty.effective_start_date
and pty.effective_end_date
AND p_date_earned between fnd_date.canonical_to_date(hoi.org_information4)
and fnd_date.canonical_to_date(nvl(hoi.org_information5,
fnd_date.date_to_canonical(hr_api.g_eot))
);
SELECT pension_type_name
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND rownum = 1;
SELECT 1
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND trunc(p_date_earned) between effective_start_date and effective_end_date
AND business_group_id = p_business_group_id;
select org_information1
from hr_organization_information
where organization_id = p_business_group_id
and org_information_context = 'NL_BG_INFO';
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where organization_structure_id = c_hierarchy_id
and p_date_earned between date_from
and nvl(date_to,hr_api.g_eot);
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where business_group_id = p_business_group_id
and p_date_earned between date_from
and nvl( date_to,hr_api.g_eot);
select person_id from per_all_assignments_f
where assignment_id = p_assignment_id
and p_date_earned between effective_start_date and effective_end_date
and business_group_id = p_business_group_id;
select assignment_id
from per_all_assignments_f asg
where asg.person_id = c_person_id
and asg.assignment_status_type_id in (select assignment_status_type_id from PER_ASS_STATUS_TYPE_AMENDS
where business_group_id = p_business_group_id and pay_system_status = 'P' and active_flag = 'Y'
union
select assignment_status_type_id from per_assignment_status_types typ
where typ.pay_system_status = 'P'
and typ.active_flag = 'Y'
and ( (typ.legislation_code is null and typ.business_group_id is null)
OR (typ.legislation_code is null and
(typ.business_group_id is not null and typ.business_group_id = p_business_group_id))
OR (typ.legislation_code ='NL')
))
and p_date_earned between asg.effective_start_date and asg.effective_end_date;
SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))
FROM hr_soft_coding_keyflex target,
per_all_assignments_f ASSIGN
WHERE p_date_earned BETWEEN ASSIGN.effective_start_date AND
ASSIGN.effective_end_date
AND ASSIGN.assignment_id = c_assignment_id
AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
AND target.enabled_flag = 'Y';
SELECT nvl(STD_TAX_REDUCTION,'0')||
nvl(SPL_TAX_REDUCTION,'0')||
nvl(SIG_SAL_SPL_TAX_REDUCTION,'0')||
nvl(SIG_SAL_NON_TAX_REDUCTION,'0')||
nvl(SIG_SAL_STD_TAX_REDUCTION,'0')||
nvl(SII_STD_TAX_REDUCTION,'0')||
nvl(SII_SPL_TAX_REDUCTION,'0')||
nvl(SII_NON_TAX_REDUCTION,'0') redstr
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND trunc(p_effective_date) BETWEEN
trunc(effective_start_date)
AND trunc(effective_end_date);
SELECT nvl(STD_TAX_REDUCTION,'0')||
nvl(SPL_TAX_REDUCTION,'0')||
nvl(SIG_SAL_SPL_TAX_REDUCTION,'0')||
nvl(SIG_SAL_NON_TAX_REDUCTION,'0')||
nvl(SIG_SAL_STD_TAX_REDUCTION,'0')||
nvl(SII_STD_TAX_REDUCTION,'0')||
nvl(SII_SPL_TAX_REDUCTION,'0')||
nvl(SII_NON_TAX_REDUCTION,'0') redstr
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND trunc(p_effective_date) BETWEEN
trunc(effective_start_date)
AND trunc(effective_end_date);
SELECT NVL(recurring_bonus_percent,0)
,NVL(non_recurring_bonus_percent,0)
,recurring_bonus_balance
,non_recurring_bonus_balance
,previous_year_bonus_included
,recurring_bonus_period
,non_recurring_bonus_period
FROM pqp_pension_types_f
WHERE pension_type_id = p_pension_type_id
AND TRUNC(p_date_earned) BETWEEN
effective_start_date AND effective_end_date
AND business_group_id = p_business_group_id;
SELECT pdb.defined_balance_id
FROM pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbd.dimension_name = 'Assignment Year To Date'
AND pdb.balance_type_id = c_balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.legislation_code = 'NL';
SELECT TO_NUMBER(TO_CHAR(c_date_earned,'YYYY')) -1
FROM dual;
SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND trunc(c_effective_date) BETWEEN
effective_start_date AND effective_end_date;
SELECT time_period_id
,start_date
,end_date
FROM per_time_periods
WHERE payroll_id = c_payroll_id
AND trunc(c_effective_date) BETWEEN start_date AND end_date;
SELECT paa.assignment_action_id
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND ppa.time_period_id = c_time_period_id
AND paa.assignment_id = p_assignment_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND ppa.date_earned BETWEEN c_period_start AND c_period_end
AND rownum = 1;
Select number_per_fiscal_year
from per_time_period_types
where period_type = c_period_type;
SELECT fnd_number.canonical_to_number(aei_information3)
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND aei_information1 = to_char(p_pension_type_id)
AND aei_information2 = to_char(p_payroll_period_number)
AND p_date_earned BETWEEN fnd_date.canonical_to_date(nvl(aei_information4,fnd_date.date_to_canonical(to_date('01-01-1951','dd-mm-yyyy'))))
AND fnd_date.canonical_to_date(nvl(aei_information5,fnd_date.date_to_canonical(to_date('31-12-4712','dd-mm-yyyy'))))
AND aei_information_category = 'NL_SAV_INFO'
AND information_type = 'NL_SAV_INFO';
SELECT max(date_start)
FROM per_all_assignments_f asg
,per_periods_of_service pps
WHERE pps.person_id = asg.person_id
AND asg.assignment_id = c_asg_id
AND pps.business_group_id = p_business_group_id
AND date_start <= p_date_earned;
SELECT min(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT min (fffunc.cn(decode(decode(INPUTV.uom,'M','N','N','N','I','N',NULL),'N'
,decode(INPUTV.hot_default_flag,'Y',nvl(EEV.screen_entry_value
,nvl(LIV.default_value,INPUTV.default_value)),'N',EEV.screen_entry_value),NULL)))
FROM pay_element_entry_values_f EEV,
pay_element_entries_f EE,
pay_link_input_values_f LIV,
pay_input_values_f INPUTV
WHERE INPUTV.input_value_id = c_ipv_id
AND c_effective_date BETWEEN INPUTV.effective_start_date
AND INPUTV.effective_end_date
AND INPUTV.element_type_id + 0 = p_element_type_id
AND LIV.input_value_id = INPUTV.input_value_id
AND c_effective_date BETWEEN LIV.effective_start_date
AND LIV.effective_end_date
AND EEV.input_value_id + 0 = INPUTV.input_value_id
AND EEV.element_entry_id = EE.element_entry_id
AND EEV.effective_start_date = EE.effective_start_date
AND EEV.effective_end_date = EE.effective_end_date
AND EE.element_link_id = LIV.element_link_id
AND EE.assignment_id = p_assignment_id
AND c_effective_date BETWEEN EE.effective_start_date
AND EE.effective_end_date
AND nvl(EE.ENTRY_TYPE, 'E') = 'E';
select iv.input_value_id
from pay_input_values_f_tl iv_tl,
pay_input_values_f iv
where iv.element_type_id = p_element_type_id
and iv_tl.input_value_id = iv.input_value_id
and iv_tl.language = userenv('LANG')
and upper(iv_tl.name) = upper(p_input_value_name)
AND p_date_earned BETWEEN iv.effective_start_date
AND iv.effective_end_date;
SELECT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = c_assignment_action_id;
SELECT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id = c_assignment_action_id;
SELECT min(asg.effective_start_date)
FROM per_assignments_f asg
,per_assignment_status_types past
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_start_date <= trunc(p_period_end_date)
AND nvl(asg.effective_end_date, trunc(p_period_end_date)) >= trunc(p_period_start_date)
AND asg.assignment_id = p_assignment_id
group by asg.assignment_id;
SELECT asg.effective_start_date Start_Dt,
decode(asg.effective_end_date,
to_date ('31/12/4712','DD/MM/YYYY'),
trunc(p_period_end_date),
asg.effective_end_date) End_Dt
,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) pt_perc
FROM per_assignments_f asg
,per_assignment_status_types past
,hr_soft_coding_keyflex target
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.effective_end_date >= c_effective_date
AND asg.assignment_id = p_assignment_id
AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND target.enabled_flag = 'Y';
select org_information1
from hr_organization_information
where organization_id = p_business_group_id
and org_information_context = 'NL_BG_INFO';
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where organization_structure_id = c_hierarchy_id
and p_date_earned between date_from
and nvl(date_to,hr_api.g_eot);
select ORG_STRUCTURE_VERSION_ID
from per_org_structure_versions_v
where business_group_id = p_business_group_id
and p_date_earned between date_from
and nvl( date_to,hr_api.g_eot);
SELECT TRUNC(date_of_birth)
FROM per_all_people_f per
,per_all_assignments_f paf
WHERE per.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT min(asg.effective_start_date)
FROM per_all_assignments_f asg
,per_assignment_status_types past
WHERE asg.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
--AND asg.effective_start_date <= trunc(p_period_start_date)
AND asg.assignment_id = p_assignment_id;