[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.element_name = l_element_name
AND pet.legislation_code = 'GB'
AND piv.name = l_piv_name;
select sum(result_value)
from pay_gb_pay_values_v
where base_classification_name in ('Earnings','Direct Net', 'Direct Payment')
and p_assignment_action_id = assignment_action_id;
cursor csr_deduct is select sum(result_value)
from pay_gb_pay_values_v
where base_classification_name in
('Pre Statutory', 'Statutory', 'Court Orders',
'Pre Tax Deductions','PAYE','NI','Voluntary Deductions',
'Pre NI Deductions','Pre Tax and NI Deductions')
and p_assignment_action_id = assignment_action_id;
SELECT peev.screen_entry_value
FROM pay_element_entry_values_f peev,
pay_element_entries_f pee,
pay_assignment_actions paa
WHERE pee.element_entry_id = peev.element_entry_id
AND pee.assignment_id = paa.assignment_id
AND paa.assignment_action_id = p_assig_act_id
AND peev.input_value_id +0 = p_input_value_id
AND to_date(p_date_earned, 'YYYY/MM/DD')
BETWEEN
pee.effective_start_date
AND pee.effective_end_date
AND to_date(p_date_earned, 'YYYY/MM/DD')
BETWEEN
peev.effective_start_date
AND peev.effective_end_date;
SELECT result_value
FROM pay_run_result_values prr,
pay_run_results pr,
pay_element_types_f pet,
pay_input_values_f piv
WHERE pr.assignment_action_id = p_assig_act_id
and pr.element_type_id = pet.element_type_id
and pr.run_result_id = prr.run_result_id
and prr.input_value_id = piv.input_value_id
and pet.element_type_id = piv.element_type_id
and piv.input_value_id = p_piv_id
and piv.business_group_id IS NULL
and piv.legislation_code = 'GB'
and to_date(p_date_earned, 'YYYY/MM/DD')
between piv.effective_start_date
and piv.effective_end_date
and to_date(p_date_earned, 'YYYY/MM/DD')
between pet.effective_start_date
and pet.effective_end_date
and pr.run_result_id = (select nvl(max(pr1.run_result_id),pr.run_result_id)
from pay_run_results pr1
where pr1.assignment_action_id = p_assig_act_id
and pr1.element_type_id = pr.element_type_id
and pr1.status = 'P');
select assignment_action_id
from pay_assignment_actions
where source_action_id = p_assig_act_id
order by action_sequence desc;
select max(org_information8) into g_tax_phone
from pay_payrolls_f p,
pay_payroll_actions pact,
hr_soft_coding_keyflex flex,
hr_organization_information org
where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
and org.ORG_INFORMATION_CONTEXT = 'Tax Details References'
and org.org_information1 = flex.segment1
and p.business_group_id = org.organization_id
and pact.payroll_action_id = p_payroll_action_id
and pact.payroll_id = p.payroll_id
and pact.effective_date between
p.effective_start_date and p.effective_end_date;
SELECT
defined_balance_id
FROM
pay_defined_balances PDB,
pay_balance_dimensions PBD,
pay_balance_types_tl PBT_TL,
pay_balance_types PBT
WHERE PBT_TL.balance_type_id = PBT.balance_type_id
and userenv('LANG') = PBT_TL.language
AND PBT_TL.balance_name = p_balance_type
AND nvl(PBT.legislation_code,l_legislation_code) = l_legislation_code
AND PDB.balance_type_id = PBT.balance_type_id
AND PBD.balance_dimension_id = PDB.balance_dimension_id
AND nvl(PDB.legislation_code,l_legislation_code) = l_legislation_code
AND PBD.database_item_suffix = p_dimension_suffix;
IF l_found = FALSE THEN -- calculate and insert the new value in the table.
--
hr_utility.trace(' NOT FOUND, inserted IN position : ' || TO_CHAR(l_table_index));
SELECT puci.value intval
FROM pay_user_rows pur,
pay_user_columns puc,
pay_user_tables put,
pay_user_column_instances puci
WHERE put.user_table_name = g_user_table_name
AND put.business_group_id is NULL
AND put.legislation_code = 'GB'
AND puc.user_column_name = 'Sequence'
AND puc.user_table_id = put.user_table_id
AND puc.business_group_id is NULL
AND puc.legislation_code = 'GB'
AND puci.user_column_id = puc.user_column_id
AND puci.business_group_id = p_business_group_id
AND pur.user_row_id = puci.user_row_id
AND pur.row_low_range_or_name = p_user_row_name;
CURSOR c_selected_balances IS
SELECT pur.row_low_range_or_name row_name,
puci.value user_desc
FROM pay_user_rows pur,
pay_user_columns puc,
pay_user_tables put,
pay_user_column_instances puci
WHERE put.user_table_name = g_user_table_name
AND put.legislation_code = 'GB'
AND puc.user_column_name = 'Narrative'
AND puc.user_table_id = put.user_table_id
AND puc.legislation_code = 'GB'
AND puci.user_column_id = puc.user_column_id
AND puci.value IS NOT NULL
AND puci.business_group_id = p_business_group_id
AND pur.user_row_id = puci.user_row_id;
SELECT 1
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = user_balance
AND nvl(pbt.legislation_code,'GB') = 'GB'
AND nvl(pbd.legislation_code,'GB') = 'GB'
AND nvl(pdb.legislation_code,'GB') = 'GB';
SELECT pbt.balance_name,
pbd.database_item_suffix
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND row_name = pbt.balance_name || pbd.database_item_suffix;
SELECT ppa.action_type
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = c_assignment_action_id;
for rec in c_selected_balances loop
l_user_defined_row_cnt := l_user_defined_row_cnt + 1;
CURSOR c_selected_balances IS
SELECT pur.row_low_range_or_name row_name,
puci.value user_desc
FROM pay_user_rows pur,
pay_user_columns puc,
pay_user_tables put,
pay_user_column_instances puci
WHERE put.user_table_name = g_user_table_name
AND put.legislation_code = 'GB'
AND puc.user_column_name = 'Narrative'
AND puc.user_table_id = put.user_table_id
AND puc.legislation_code = 'GB'
AND puci.user_column_id = puc.user_column_id
AND puci.value IS NOT NULL
AND puci.business_group_id = p_business_group_id
AND pur.user_row_id = puci.user_row_id;
SELECT 1
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = user_balance
AND nvl(pbt.legislation_code,'GB') = 'GB'
AND nvl(pbd.legislation_code,'GB') = 'GB'
AND nvl(pdb.legislation_code,'GB') = 'GB';
SELECT pbt.balance_name,
pbd.database_item_suffix
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND row_name = pbt.balance_name || pbd.database_item_suffix;
SELECT ppa.action_type
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = c_assignment_action_id;
for rec in c_selected_balances loop
l_user_defined_row_cnt := l_user_defined_row_cnt + 1;
select /*+ ORDERED USE_NL(paa,ppa,rpaa,rppa) */
to_char(nvl(rppa.date_earned,rppa.effective_date),'YYYY/MM/DD'),
rpaa.payroll_action_id,
rpaa.assignment_action_id,
paa.assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions rpaa,
pay_payroll_actions rppa
where paa.payroll_action_id = ppa.payroll_action_id
and rppa.payroll_action_id = rpaa.payroll_action_id
and paa.assignment_id = rpaa.assignment_id
and paa.assignment_action_id =
(select
to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
from pay_payroll_actions pa,
pay_assignment_actions aa
where pa.action_type in ('U','P')
and aa.action_status = 'C'
and pa.payroll_action_id = aa.payroll_action_id
and aa.assignment_id = p_assignment_id
and pa.effective_date <= p_session_date)
and ppa.action_type in ('P', 'U')
and rpaa.assignment_id = p_assignment_id
and rpaa.action_sequence =
(select max(aa.action_sequence)
from pay_assignment_actions aa,
pay_action_interlocks loc
where loc.locked_action_id = aa.assignment_action_id
and loc.locking_action_id = paa.assignment_action_id);
SELECT /*+ USE_NL(paa, ppa) */
--fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) /*Bug 4775025*/
to_number(substr(max(to_char(ppa.effective_date,'J')||lpad(paa.assignment_action_id,15,'0')),8))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
/* Commented below code, removed action_types 'B' and 'I' for bug fix 4775025*/
/* AND (paa.source_action_id is not null
or ppa.action_type in ('U','P'))*/
AND ppa.effective_date <= p_session_date
AND ppa.action_type in ('R', 'Q', 'U', 'P')
AND paa.action_status = 'C';
select to_char(nvl(ppa.date_earned,ppa.effective_date),'YYYY/MM/DD'),
paa.payroll_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_assig_act_id
and ppa.payroll_action_id = paa.payroll_action_id;
select pact.action_type
from pay_assignment_actions assact,
pay_payroll_actions pact
where assact.assignment_action_id = p_assignment_action_id
and pact.payroll_action_id = assact.payroll_action_id;
select assact.assignment_action_id
from pay_assignment_actions assact,
pay_action_interlocks loc
where loc.locking_action_id = p_assignment_action_id
and assact.assignment_action_id = loc.locked_action_id
order by assact.action_sequence desc;
select pact.action_type , assact.assignment_id
from pay_assignment_actions assact,
pay_payroll_actions pact
where assact.assignment_action_id = p_assignment_action_id
and pact.payroll_action_id = assact.payroll_action_id
;
select assact.assignment_action_id
from pay_assignment_actions assact,
pay_action_interlocks loc
where loc.locking_action_id = p_assignment_action_id
and assact.assignment_action_id = loc.locked_action_id
order by assact.action_sequence desc
;
select assact.assignment_action_id
from pay_assignment_actions assact,
pay_payroll_actions pact,
pay_action_interlocks loc
where loc.locked_action_id = p_assignment_action_id
and assact.assignment_action_id = loc.locking_action_id
and pact.payroll_action_id = assact.payroll_action_id
and pact.action_type in ('P','U') /* prepayments only */
order by assact.action_sequence desc
;
select pact.payroll_action_id,
to_char(nvl(pact.date_earned,pact.effective_date),'YYYY/MM/DD')
from pay_assignment_actions assact,
pay_payroll_actions pact
where assact.assignment_action_id = p_run_assignment_action_id
and pact.payroll_action_id = assact.payroll_action_id
;
select pad.address_line1,
pad.address_line2,
pad.address_line3,
l.meaning,
pad.postal_code,
pad.region_3,
pad.town_or_city
from per_addresses pad,
hr_lookups l
where pad.person_id = p_person_id
and pad.primary_flag = 'Y'
and l.lookup_type(+) = 'GB_COUNTY'
and l.lookup_code(+) = pad.region_1
and sysdate between nvl(pad.date_from, sysdate)
and nvl(pad.date_to, sysdate);
select
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
l.meaning,
hrl.region_2,
hrl.region_3,
hrl.town_or_city
from hr_locations hrl,
hr_lookups l
where hrl.location_id = p_location_id
and l.lookup_type(+) = 'GB_COUNTY'
and l.lookup_code(+) = hrl.region_1;
select put.user_table_id
from pay_user_tables put
where put.user_table_name = g_user_table_name
and put.business_group_id is NULL
and put.legislation_code = 'GB';
select decode(legislation_code,
'GB',replace(database_item_suffix,database_item_suffix,
' ' || substr(database_item_suffix,2)),
database_item_suffix || ' USER-REG')
from pay_balance_dimensions pbd
where dimension_name = p_dimension_name;
select pay_user_rows_s.nextval into l_user_row_id
from dual;
insert into PAY_USER_ROWS_F
(USER_ROW_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
USER_TABLE_ID,
ROW_LOW_RANGE_OR_NAME,
DISPLAY_SEQUENCE,
LEGISLATION_SUBGROUP,
ROW_HIGH_RANGE)
values
(l_user_row_id,
to_date('1900/01/01','YYYY/MM/DD'),
to_date('4712/12/31','YYYY/MM/DD'),
p_business_group_id,
l_legislation_code,
l_user_table_id,
p_balance_name || l_dimension_suffix,
NULL,
NULL,
NULL);
select put.user_table_id
from pay_user_tables put
where put.user_table_name = g_user_table_name
and put.business_group_id is NULL
and put.legislation_code = 'GB';
select 1
from pay_user_rows_f pur,
pay_balance_dimensions pbd
where pur.row_low_range_or_name =
p_balance_name ||
replace(pbd.database_item_suffix,pbd.database_item_suffix,
' '|| substr(pbd.database_item_suffix,2))
and pbd.dimension_name = p_dimension_name
and pur.user_table_id = l_user_table_id;
select sum(eev.screen_entry_value)
from pay_element_entry_values_f eev,
per_pay_bases ppb,
pay_element_entries_f pe
where ppb.pay_basis_id +0 = p_pay_basis_id
and pe.assignment_id = p_assignment_id
and eev.input_value_id = ppb.input_value_id
and eev.element_entry_id = pe.element_entry_id
and eev.input_value_id = ppb.input_value_id
and eev.element_entry_id = pe.element_entry_id
and p_effECtive_date between
eev.effective_start_date and eev.effective_end_date
and p_EFfective_date between
pe.effective_start_date and pe.effective_end_date;