[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ppa_user.business_group_id,
ppa_user.date_earned,
ppa_owner.date_earned
into l_business_group_id,
l_user_date_earned,
l_owner_date_earned
from pay_payroll_actions ppa_owner,
pay_payroll_actions ppa_user
where ppa_user.payroll_action_id = p_user_payroll_action_id
and ppa_owner.payroll_action_id = p_owner_payroll_action_id;
SELECT OWNER.business_group_id,
OWNER.date_earned
FROM pay_payroll_actions OWNER
WHERE OWNER.payroll_action_id = p_owner_payroll_action_id;
SELECT /*+ ORDERED */
pet.element_name
FROM
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values piv,
pay_element_types pet
WHERE prr.run_result_id = p_run_result_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id
AND pet.element_type_id = piv.element_type_id;
select pbf.scale
into p_feed_scale
from pay_balance_feeds_f pbf,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and pbf.balance_type_id = p_bt_id
and pbf.input_value_id = p_iv_id
and ppa.date_earned
between pbf.effective_start_date and pbf.effective_end_date;
SELECT /*+ ORDERED */
pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pdb.balance_type_id = c_balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = c_dimension_name;
SELECT palb.value
FROM pay_assignment_latest_balances palb,
pay_balance_context_values pbcv
WHERE pbcv.context_id = c_source_id
AND palb.latest_balance_id = pbcv.latest_balance_id
AND palb.assignment_action_id = c_assignment_action_id
AND palb.defined_balance_id = c_defined_bal_id;
SELECT /*+ ORDERED */
paa.assignment_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT /*+ ORDERED */
TO_NUMBER(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date <= c_effective_date
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT BAL_ASSACT.assignment_id,
BAL_ASSACT.action_sequence,
BACT.action_type,
BACT.effective_date,
BACT.date_earned,
BACT.business_group_id
FROM pay_payroll_actions BACT,
pay_assignment_actions BAL_ASSACT
WHERE BAL_ASSACT.assignment_action_id = p_assignment_action_id
AND BACT.payroll_action_id = BAL_ASSACT.payroll_action_id;
SELECT DB.balance_type_id,
DIM.dimension_name
FROM pay_balance_dimensions DIM,
pay_defined_balances DB
WHERE DB.defined_balance_id = p_defined_balance_id
AND DIM.balance_dimension_id = DB.balance_dimension_id;
SELECT /*+ ORDERED */
pdb.defined_balance_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
ff_database_items ffd,
ff_user_entities ffu,
hr_organization_information hoi,
pay_defined_balances pdb
WHERE paa.assignment_action_id=p_assignment_action_id
AND ppa.payroll_action_id=paa.payroll_action_id
AND ffd.user_name=p_item_name
AND ffu.user_entity_id=ffd.user_entity_id
AND ffu.creator_type='B'
AND nvl(ffu.business_group_id,ppa.business_group_id)=ppa.business_group_id
AND hoi.organization_id=ppa.business_group_id
AND hoi.org_information_context='Business Group Information'
AND nvl(ffu.legislation_code,hoi.org_information9)=hoi.org_information9
AND pdb.defined_balance_id=ffu.creator_id;
select language_code
from fnd_languages
where installed_flag in ('B', 'I');
-- INSERT INTO FF_ROUTES --
-- ---------------------------
select ff_routes_s.nextval
into l_route_id
from dual;
select pay_balance_dimensions_s.nextval
into l_balance_dimension_id
from dual;
insert into ff_routes(
route_id,
route_name,
user_defined_flag,
description,
text)
values( l_route_id,
'ROUTE_NAME_' || to_char(l_route_id),
'N',
'Route for User Defined Assignment Balance Dimension ' || l_dimension_name,
l_route_text);
-- INSERT INTO FF_ROUTE_CONTEXT_USAGES --
-- -----------------------------------------
insert into ff_route_context_usages(
route_id,
context_id,
sequence_no)
select l_route_id,
context_id,
1
FROM ff_contexts
WHERE context_name = 'ASSIGNMENT_ACTION_ID';
-- INSERT INTO FF_ROUTE_PARAMETER --
-- ------------------------------------
insert into ff_route_parameters(
route_parameter_id,
route_id,
sequence_no,
parameter_name,
data_type)
values( ff_route_parameters_s.nextval,
l_route_id,
1,
'Balance Type Id',
'N');
insert into pay_balance_dimensions(
balance_dimension_id,
business_group_id,
legislation_code,
route_id,
database_item_suffix,
dimension_name,
dimension_type,
description,
legislation_subgroup,
payments_flag,
expiry_checking_level,
expiry_checking_code,
feed_checking_type,
feed_checking_code,
-- for Run Balances
SAVE_RUN_BALANCE_ENABLED,
DIMENSION_LEVEL,
PERIOD_TYPE,
START_DATE_CODE,
-- for Group Level dimension (run balance compliant)
ASG_ACTION_BALANCE_DIM_ID,
-- for hrdyndbi DBI Generator (run balance compliant)
DATABASE_ITEM_FUNCTION)
values( l_balance_dimension_id,
p_business_group_id,
null,
l_route_id,
l_database_item_suffix,
l_dimension_name,
l_dimension_type,
l_description,
null,
'N',
l_expiry_checking_level,
l_expiry_checking_code,
null,
null,
null,
null,
null,
null,
null,
null);
select rowid
into l_rowid
from pay_balance_dimensions_tl
where balance_dimension_id = l_balance_dimension_id
and language = l_rec.language_code
for update nowait;
update pay_balance_dimensions_tl
set dimension_name = l_database_item_suffix,
database_item_suffix = l_database_item_suffix,
description = l_description
where rowid = l_rowid;
insert into pay_balance_dimensions_tl(
BALANCE_DIMENSION_ID,
LANGUAGE,
SOURCE_LANG,
DIMENSION_NAME,
DATABASE_ITEM_SUFFIX,
DESCRIPTION)
values( l_balance_dimension_id,
l_rec.language_code,
l_rec.language_code,
l_database_item_suffix,
l_database_item_suffix,
l_description);
SELECT TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||
ASSACT.assignment_action_id),16))
FROM pay_payroll_actions PACT,
pay_assignment_actions ASSACT
WHERE ASSACT.assignment_id = c_assignment_id
AND PACT.payroll_action_id = ASSACT.payroll_action_id
AND PACT.date_earned <= c_effective_date
AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT ppa.effective_date effect_date,
ppa.date_earned earned_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT /*+ ORDERED */
ptp.end_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.time_period_id = ppa.time_period_id;
SELECT /*+ ORDERED */
ptp.end_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.time_period_id = ppa.time_period_id;
SELECT /*+ ORDERED */
trunc(ppa.effective_date,'MM')
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id;
SELECT /*+ ORDERED */
trunc(ppa.effective_date,'YYYY')
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id;
SELECT /*+ ORDERED */
add_months(nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),trunc(PACT.date_earned,'YYYY')),
floor(months_between(PACT.date_earned,nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),
trunc(PACT.date_earned,'YYYY')))/12)*12) last_start_date
FROM pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
hr_organization_information HROG
WHERE ASSACT.assignment_action_id = c_assignment_action_id
AND PACT.payroll_action_id = ASSACT.payroll_action_id
AND HROG.organization_id = PACT.business_group_id
AND HROG.org_information_context = 'Business Group Information';
SELECT /*+ ORDERED */
TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||
ASSACT.assignment_action_id),16))
FROM pay_assignment_actions ASSACT,
pay_payroll_actions PACT
WHERE ASSACT.assignment_id = c_assignment_id
AND PACT.payroll_action_id = ASSACT.payroll_action_id
AND PACT.date_earned <= c_effective_date
AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT /*+ ORDERED */
add_months(nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),trunc(PACT.effective_date,'YYYY')),
floor(months_between(PACT.effective_date,nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),
trunc(PACT.effective_date,'YYYY')))/12)*12) last_start_date
FROM pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
hr_organization_information HROG
WHERE ASSACT.assignment_action_id = c_assignment_action_id
AND PACT.payroll_action_id = ASSACT.payroll_action_id
AND HROG.organization_id = PACT.business_group_id
AND HROG.org_information_context = 'Business Group Information';
SELECT /*+ ORDERED */
TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||
ASSACT.assignment_action_id),16))
FROM pay_assignment_actions ASSACT,
pay_payroll_actions PACT
WHERE ASSACT.assignment_id = c_assignment_id
AND PACT.payroll_action_id = ASSACT.payroll_action_id
AND PACT.effective_date <= c_effective_date
AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
select /*+ ORDERED */
add_months(trunc(add_months(ppa.effective_date,9),'YYYY'),-9)
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = c_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
SELECT /*+ ORDERED */
add_months(trunc(add_months(ppa.effective_date,5),'YYYY'),-5)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id;
SELECT /*+ ORDERED */
add_months(trunc(add_months(ppa.effective_date,6),'YYYY'),-6)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT /*+ ORDERED */
ptp.end_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.time_period_id = ppa.time_period_id;
SELECT /*+ ORDERED */
ptp.end_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
WHERE paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.time_period_id = ppa.time_period_id;
SELECT /*+ ORDERED */
pdb.balance_type_id,
pbd.dimension_name
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pdb.defined_balance_id = c_defined_balance_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
SELECT /*+ ORDERED */
pdb.balance_type_id,
pbd.dimension_name
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pdb.defined_balance_id = c_defined_balance_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;