[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select effective_date,
start_date,
business_group_id,
pay_us_payroll_utils.get_parameter(
'TRANSFER_STATE',
legislative_parameters) state_abbrev,
to_number(substr(legislative_parameters,
instr(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=')
+ length('TRANSFER_CONSOLIDATION_SET_ID='))),
to_number(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ length('TRANSFER_PAYROLL_ID='),
(instr(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=') - 1 )
- (instr(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ length('TRANSFER_PAYROLL_ID='))))))
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
select input_value_id
from pay_input_values_f piv
where piv.element_type_id = cp_element_type_id
and piv.legislation_code = 'US'
and piv.name = cp_input_value_name
and cp_effective_date between piv.effective_start_date
and piv.effective_end_date;
select 'Y' from dual
where exists (select 1 from pay_element_types_f pet,
pay_element_classifications pec
where pet.classification_id = pec.classification_id
and pet.business_group_id = cp_business_group_id
and pet.element_information1 = cp_element_category
and pec.classification_name = 'Pre-Tax Deductions'
and pec.legislation_code = 'US');
select element_type_id from pay_element_types_f
where element_name = cp_elment_name
and legislation_code = 'US';
select creator_id from ff_user_entities
where user_entity_name = cp_user_entity_name;
Purpose : This returns the select statement that is
used to created the range rows
Arguments :
Notes : Calls procedure - get_payroll_action_info
********************************************************************/
PROCEDURE range_cursor(
p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
ld_end_date DATE;
insert into pay_action_information
(ACTION_INFORMATION_ID,
ACTION_CONTEXT_ID,
ACTION_CONTEXT_TYPE,
ACTION_INFORMATION_CATEGORY,
ACTION_INFORMATION1
)
select pay_action_information_s.nextval,
p_payroll_action_id,
'PPA',
'GAGA_STATUS',
'U'
from dual;
'select distinct paf.person_id
from per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.business_group_id = ''' || ln_business_group_id || '''
and paf.assignment_id = paa.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date
between fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_start_date-10) || ''')
and fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_end_date+30) || ''')
and ppa.action_type in (''R'',''Q'')
and ppa.last_update_date >= fnd_date.canonical_to_date(''' ||
lv_date || ''')
and ppa.consolidation_set_id like ''' || lv_cons_set_id || '''
and ppa.payroll_id like ''' || lv_payroll_id || '''
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = ''C''
and paa.source_action_id is null
and :payroll_action_id is not null
order by paf.person_id';
select distinct
paa.tax_unit_id,
paa.assignment_id,
ppa.effective_date
from per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paf.person_id between cp_start_person_id
and cp_end_person_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paa.assignment_id = paf.assignment_id
and ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date - 10
and cp_end_date + 30
and ppa.action_type in ('R','Q')
and ppa.last_update_date >=
greatest(cp_start_date,
fnd_date.canonical_to_date('2004/07/01 00:00:00'))
and ppa.consolidation_set_id like cp_cons_set_id
and ppa.payroll_id like cp_payroll_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.source_action_id is not null
and paa.action_status = 'C'
and not exists
(select 1
from pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type = 'V'
)
order by 1, 2;
select distinct
paa.tax_unit_id,
paa.assignment_id,
ppa.effective_date
from per_assignments_f paf,
pay_us_emp_state_tax_rules_f pest,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paf.person_id between cp_start_person_id
and cp_end_person_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and pest.assignment_id = paf.assignment_id
and ppa.effective_date between pest.effective_start_date
and pest.effective_end_date
and pest.state_code = cp_state_code
and paa.assignment_id = paf.assignment_id
and ppa.business_group_id = cp_business_group_id
and ppa.effective_date between cp_start_date - 10
and cp_end_date + 30
and ppa.action_type in ('R','Q')
and ppa.last_update_date >=
greatest(cp_start_date,
fnd_date.canonical_to_date('2004/07/01 00:00:00'))
and ppa.consolidation_set_id like cp_cons_set_id
and ppa.payroll_id like cp_payroll_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.source_action_id is not null
and paa.action_status = 'C'
and not exists
(select 1
from pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type = 'V'
)
order by 1, 2;
select distinct state_code
from pay_us_emp_state_tax_rules_f pest
where pest.assignment_id = cp_assignment_id
and cp_effective_date between pest.effective_Start_date
and pest.effective_end_Date;
select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
paa.assignment_action_id, ppa.effective_date,
ppa.payroll_id, ppa.consolidation_set_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
and ppa.effective_date between to_date('2004/01/01', 'yyyy/mm/dd')
and to_date('2004/12/31', 'yyyy/mm/dd')
order by paa.action_sequence desc;
select state_code from pay_us_states
where state_Abbrev = cp_state_abbrev;
select pay_assignment_actions_s.nextval
into ln_adj_action_id
from dual;
hr_utility.trace('Update Serail Number = ' || lv_serial_number);
update pay_assignment_actions
set serial_number = lv_serial_number
where assignment_action_id = ln_adj_action_id;
hr_utility.trace('Insert into temp table ');
insert into pay_us_rpt_totals
(location_id, organization_id, tax_unit_id,
value1, value3)
select
p_payroll_action_id,
to_char(ld_run_effective_date, 'ddmmyyyy'),
ln_run_payroll_id,
ln_run_consolidation_id,
ln_adj_business_group_id
from dual
where not exists
(select 1 from pay_us_rpt_totals
where location_id = p_payroll_action_id
and tax_unit_id = ln_run_payroll_id
and value1 = ln_run_consolidation_id
and organization_id
= to_char(ld_run_effective_date, 'ddmmyyyy'));
select prt.rowid,
to_date(lpad(to_char(prt.organization_id),8,'0'),'ddmmyyyy'),
tax_unit_id,
value1
from pay_us_rpt_totals prt
where prt.location_id = cp_payroll_action_id
and prt.value2 is null
order by to_date(lpad(to_char(prt.organization_id),8,'0'),'ddmmyyyy'),
tax_unit_id, value1;
select 1
from pay_action_information
where action_information1 = 'C'
and action_context_id = cp_payroll_action_id
and action_context_type = 'PPA';
update pay_us_rpt_totals
set value2 = ln_payroll_action_id
where rowid = lr_rowid;
delete from pay_us_rpt_totals
where rowid = lr_rowid;
update pay_action_information
set ACTION_INFORMATION1 = 'C'
where ACTION_CONTEXT_ID = p_payroll_action_id
and ACTION_CONTEXT_TYPE = 'PPA';
select ACTION_INFORMATION1
into status
from pay_action_information
where ACTION_CONTEXT_ID = p_payroll_action_id
and ACTION_CONTEXT_TYPE = 'PPA';
select state_code from pay_us_states
where state_Abbrev = cp_state_abbrev;
select min(paa.chunk_number)
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_Action_id
and ppa.payroll_action_id = cp_payroll_action_id;
select prt.value2
from pay_us_rpt_totals prt
where prt.location_id = cp_payroll_action_id;
select count(*)
into ln_count_incomplete_actions
from pay_assignment_actions
where payroll_action_id = p_payroll_action_id
and action_status <> 'C';
delete from pay_us_rpt_totals
where location_id = p_payroll_action_id;
delete from pay_action_information
where ACTION_CONTEXT_ID = p_payroll_action_id
and ACTION_CONTEXT_TYPE = 'PPA';
select
to_date(substr(paa.serial_number,1,8),'ddmmyyyy') sort_date,
paa.assignment_id,
paa.tax_unit_id,
paa.payroll_action_id,
to_number(substr(paa.serial_number,9)) bal_asg_action_id
from pay_assignment_actions paa
where paa.assignment_action_id = cp_assignment_action_id;
select payroll_id, consolidation_set_id
from pay_payroll_actions ppa
,pay_assignment_actions paa
where ppa.payroll_Action_id = paa.payroll_action_id
and paa.assignment_action_id = cp_run_action_id;
select st.state_code, st.jurisdiction_code
from pay_us_emp_state_tax_rules_f st
where st.assignment_id = cp_assignment_id
and st.state_code like cp_where_state_code
and cp_effective_date between st.effective_start_date
and st.effective_end_date;
select value2 badj_payroll_Action,
value3 business_group_id
from pay_us_rpt_totals prt
where prt.location_id = cp_payroll_action_id
and to_date(lpad(to_char(prt.organization_id),8,'0'),'ddmmyyyy')
= cp_badj_effective_date
and tax_unit_id = cp_run_payroll_id
and value1 = cp_consolidation_id;
select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
paa.assignment_action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_assignments_f paf1,
pay_payroll_actions ppa
where paf1.assignment_id = cp_assignment_id
and paf.person_id = paf1.person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'B', 'V', 'I')
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date between to_date('2004/01/01', 'yyyy/mm/dd')
and to_date('2004/12/31', 'yyyy/mm/dd')
order by paa.action_sequence desc;
select payroll_action_id,
chunk_number
into l_payroll_action_id,
l_chunk_number
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
sub_input_value_table.delete;
sub2_input_value_table.delete;
sub_entry_value_table.delete;
sub2_entry_value_table.delete;
sub_input_value_table.delete;
sub2_input_value_table.delete;
sub_entry_value_table.delete;
sub2_entry_value_table.delete;