[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pad.address_line1,
pad.address_line2,
pad.address_line3,
pad.town_or_city,
pad.postal_code,
ftt.territory_short_name
from per_addresses pad,
fnd_territories_tl ftt
where pad.person_id = c_person_id
and ftt.language = userenv('LANG')
and ftt.territory_code = pad.country
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,
hrl.town_or_city,
hrl.postal_code,
ftt.territory_short_name
from hr_locations hrl,
fnd_territories_tl ftt
where hrl.location_id = c_location_id
and ftt.language = userenv('LANG')
and ftt.territory_code = hrl.country;
select pev.screen_entry_value
from per_pay_bases ppb,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pee.assignment_id = c_assignment_id
and ppb.pay_basis_id = c_pay_basis_id
and pee.element_entry_id = pev.element_entry_id
and ppb.input_value_id = pev.input_value_id
and c_effective_date between pev.effective_start_date
and pev.effective_end_date
and c_effective_date between pee.effective_start_date
and pee.effective_end_date;
select fcu.currency_code
from hr_organization_information hoi,
hr_organization_units hou,
fnd_currencies fcu
where hou.business_group_id = c_business_group_id
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'Business Group Information'
and fcu.issuing_territory_code = hoi.org_information9;
SELECT decode(pbt.balance_name, 'Earnings_Total',1
, 'Direct Payments',2
, 'Termination_Payments',3
, 'Involuntary Deductions',4
, 'Pre Tax Deductions',5
, 'Termination Deductions',6
, 'Voluntary Deductions',7
, 'Employer Superannuation Contribution',8
, 'Earnings_Non_Taxable',9
, 'Total_Tax_Deductions',10) sort_index,
pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name IN ('Earnings_Total'
, 'Direct Payments'
, 'Termination_Payments'
, 'Involuntary Deductions'
, 'Pre Tax Deductions'
, 'Termination Deductions'
, 'Voluntary Deductions'
, 'Employer Superannuation Contribution'
, 'Earnings_Non_Taxable'
, 'Total_Tax_Deductions')
AND pbd.dimension_name = c_dimension_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU'
ORDER BY sort_index;
SELECT tax_unit_id
from pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id;
SELECT decode(pbt.balance_name, 'Earnings_Total',1
, 'Direct Payments',2
, 'Termination_Payments',3
, 'Involuntary Deductions',4
, 'Pre Tax Deductions',5
, 'Termination Deductions',6
, 'Voluntary Deductions',7
, 'Employer Superannuation Contribution',8
, 'Earnings_Non_Taxable',9
, 'Total_Tax_Deductions',10) sort_index,
pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name IN ('Earnings_Total'
, 'Direct Payments'
, 'Termination_Payments'
, 'Involuntary Deductions'
, 'Pre Tax Deductions'
, 'Termination Deductions'
, 'Voluntary Deductions'
, 'Employer Superannuation Contribution'
, 'Earnings_Non_Taxable'
, 'Total_Tax_Deductions')
AND pbd.dimension_name = c_dimension_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'AU'
ORDER BY sort_index;
p_balance_value_tab_ytd.delete;
select
rppa.date_earned,
rpaa.payroll_action_id,
rpaa.assignment_action_id,
paa.assignment_action_id,
ptp.time_period_id,
ptp.period_name,
rppa.payroll_id,
nvl(rppa.pay_advice_date,ptp.pay_advice_date),
rppa.pay_advice_message
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions rpaa,
per_time_periods ptp,
pay_payroll_actions rppa
where paa.payroll_action_id = ppa.payroll_action_id
and rppa.payroll_action_id = rpaa.payroll_action_id
and rppa.time_period_id = ptp.time_period_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') /* Bug No : 2674887 */
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') /* Bug No : 2674887 */
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 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') -- Removed check for costing (2846119)
/* prepayments
only */
order by assact.action_sequence desc
;
select pact.payroll_id,
pact.payroll_action_id,
pact.date_earned,
ptp.time_period_id,
ptp.period_name,
nvl(pact.pay_advice_date,ptp.pay_advice_date),
pay_advice_message
from pay_assignment_actions assact,
pay_payroll_actions pact,
per_time_periods ptp
where assact.assignment_action_id = p_run_assignment_action_id
and pact.payroll_action_id = assact.payroll_action_id
and pact.payroll_id = ptp.payroll_id
and pact.date_earned between ptp.start_date and ptp.end_date ;
SELECT pai.locked_action_id
FROM pay_assignment_actions paa,
pay_action_interlocks pai
WHERE pai.LOCKING_ACTION_ID = p_assignment_action_id
AND pai.locked_action_id = paa.assignment_action_id
AND paa.assignment_action_id not in (select bpaa.source_action_id
from pay_assignment_actions bpaa
where bpaa.source_action_id =pai.locked_action_id)
ORDER BY locked_action_id ASC;
/*SELECT locked_action_id Bug 3245909 To fetch Master locked action_id only */
/* FROM pay_assignment_actions paa,
pay_action_interlocks pai
WHERE LOCKING_ACTION_ID = p_assignment_action_id
AND pai.locked_action_id = paa.assignment_action_id
AND paa.source_action_id IS NULL
ORDER BY locked_action_id ASC;*/
select distinct prrv.result_value ,prr.element_entry_id
from
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_classifications pec
where prr.source_id= p_source_id
and piv.input_value_id=prrv.input_value_id
and piv.name like '%Fund%Name%'
and pet.element_type_id=piv.element_type_id
and pet.classification_id=pec.classification_id
and pec.classification_name='Information'
and prr.element_type_id=pet.element_type_id
and prrv.result_value is not null
and prr.run_result_id=prrv.run_result_id
and prr.assignment_action_id=p_assignment_action_id
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and p_date_earned between piv.effective_start_date and piv.effective_end_date
AND (pec.legislation_code is null or pec.legislation_code = 'AU');
select distinct prrv.result_value ,prr.element_entry_id
from
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_classifications pec,
pay_element_entries_f pee,
pay_element_entries_f pee1
where pee.element_entry_id=p_source_id
and pee.source_id=pee1.element_entry_id
and prr.element_entry_id=pee1.element_entry_id
and piv.input_value_id=prrv.input_value_id
and piv.name like '%Fund%Name%'
and pet.element_type_id=piv.element_type_id
and pet.classification_id=pec.classification_id
and pec.classification_name='Information'
and prr.element_type_id=pet.element_type_id
and prrv.result_value is not null
and prr.run_result_id=prrv.run_result_id
and prr.assignment_action_id=p_assignment_action_id
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and p_date_earned between piv.effective_start_date and piv.effective_end_date
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and p_date_earned between pee1.effective_start_date and pee1.effective_end_date
AND (pec.legislation_code is null or pec.legislation_code = 'AU');
select distinct prrv.result_value ,prr.element_entry_id
from pay_element_entries_f pee,
pay_element_entries_f pee1,
pay_run_results prr,
pay_run_results prr1,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_classifications pec
where pee.element_entry_id=p_source_id
and pee.source_id =prr.run_result_id
and prr.source_id= pee1.element_entry_id
and prr1.element_entry_id=pee1.element_entry_id
and piv.input_value_id=prrv.input_value_id
and piv.name like '%Fund%Name%'
and pet.element_type_id=piv.element_type_id
and pet.classification_id=pec.classification_id
and pec.classification_name='Information'
and prr1.element_type_id=pet.element_type_id
and prrv.result_value is not null
and prr1.run_result_id=prrv.run_result_id
and prr1.assignment_action_id=p_assignment_action_id
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and p_date_earned between piv.effective_start_date and piv.effective_end_date
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and p_date_earned between pee1.effective_start_date and pee1.effective_end_date
AND (pec.legislation_code is null or pec.legislation_code = 'AU');
select PERSONAL_PAYMENT_METHOD_ID
from pay_element_entries_f pee
where element_entry_id=p_element_entry_id
and assignment_id=p_assignment_id
and p_date_earned between pee.effective_start_date and pee.effective_end_date;
select hoi.org_information2
from
hr_organization_information hoi,
hr_organization_units hou,
pay_personal_payment_methods_f pppm
where
hoi.org_information_context='AU_SUPER_FUND'
and hoi.organization_id=hou.organization_id
and pppm.payee_id=hoi.organization_id
and p_date_earned between pppm.effective_start_date and last_day(pppm.effective_end_date)
and (p_date_earned between to_date(hoi.org_information9,'yyyy/mm/dd hh24:mi:ss') and
nvl(to_date(hoi.org_information10,'yyyy/mm/dd hh24:mi:ss'),
to_date('4712/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss')))
and hou.business_group_id=p_business_group_id
and pppm.assignment_id=p_assignment_id
and pppm.personal_payment_method_id=p_third_party_id
order by 1 ;
select creator_type
from pay_element_entries_f
where element_entry_id=p_source_id;
SELECT prv.result_value
from pay_run_results prr,
pay_run_result_values prv,
pay_element_types_f pet,
pay_input_values_f piv
where prr.assignment_action_id = p_assignment_action_id
and prv.run_result_id = prr.run_result_id
and prv.input_value_id = piv.input_value_id
and prr.element_type_id = pet.element_type_id
and piv.uom like 'H_%'
and piv.element_type_id= pet.element_type_id
and pet.element_name= 'Normal Hours');
SELECT pivf.element_type_id
FROM pay_input_values_f pivf, per_pay_bases ppb
WHERE pivf.input_value_id = ppb.input_value_id
AND ppb.pay_basis_id = p_pay_bases_id);
SELECT pivf.input_value_id
,pivf.name
,decode(pivf.name,'Hours',1,2) sort_index
FROM pay_input_values_f pivf
WHERE pivf.element_type_id = c_element_type_id
AND substr(pivf.uom,1,1) = 'H'
AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date
ORDER BY sort_index;
SELECT prrv.result_value
FROM pay_run_result_values prrv
WHERE prrv.run_result_id = c_run_result_id
AND prrv.input_value_id = c_input_value_id;
SELECT pivf.input_value_id
FROM pay_input_values_f pivf
WHERE pivf.element_type_id = c_element_type_id
AND upper(pivf.name) like 'RATE%'
AND pivf.uom in ('N','M','I') /*bug 6109668 */
AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date;
SELECT prrv.result_value
FROM pay_run_result_values prrv
WHERE prrv.run_result_id = c_run_result_id
AND prrv.input_value_id = c_input_value_id;
SELECT pivf.input_value_id
FROM pay_input_values_f pivf
WHERE pivf.element_type_id = c_element_type_id
and pivf.name IN ('Hours','Days')
AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date;
SELECT prrv.result_value
FROM pay_run_result_values prrv
WHERE prrv.run_result_id = c_run_result_id
AND prrv.input_value_id = c_input_value_id;
select popm.currency_code
from pay_payrolls_f ppf,
pay_org_payment_methods_f popm
where ppf.default_payment_method_id = popm.org_payment_method_id
and ppf.payroll_id = c_payroll_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and p_effective_date between popm.effective_start_date and popm.effective_end_date;
select hoi.org_information10
from hr_organization_information hoi,
hr_organization_units hou
where hou.business_group_id = c_business_group_id
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'Business Group Information';
SELECT max(ppa.effective_date)
FROM pay_payroll_actions ppa ,
pay_assignment_actions paa ,
pay_action_interlocks pai
WHERE pai.locked_action_id = c_assignment_action_id
AND pai.locking_action_id = paa.assignment_action_id
AND paa.pre_payment_id is not null
AND ppa.action_type IN ('H','E','M','A')
AND ppa.payroll_action_id = paa.payroll_action_id;
SELECT ppa.effective_date
FROM pay_payroll_actions ppa ,
pay_assignment_actions paa
WHERE paa.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id;
select paf.person_id, paf.location_id, paf.organization_id, paf.business_group_id
from per_all_assignments_f paf, pay_payroll_actions ppa
where paf.assignment_id = p_assignment_id
and ppa.payroll_action_id = p_payroll_action_id
and (ppa.effective_date between paf.effective_start_date and paf.effective_end_date);
select pei_information3
from per_people_extra_info
where person_id = p_person_id
and information_type = 'HR_SELF_SERVICE_PER_PREFERENCE'
and pei_information1 = 'PAYSLIP';
select lei_information3
from hr_location_extra_info
where location_id = p_location_id
and information_type = 'HR_SELF_SERVICE_LOC_PREFERENCE'
and lei_information1 = 'PAYSLIP';
select org_information3
from hr_organization_information
where organization_id = p_organization_id
and org_information_context = 'HR_SELF_SERVICE_ORG_PREFERENCE'
and org_information1 = 'PAYSLIP';
select org_information3
from hr_organization_information
where organization_id = p_business_group_id
and org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
and org_information1 = 'PAYSLIP';