[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = cp_business_group_id;
select ppa.legislative_parameters,
ppa.business_group_id
into lv_legislative_param, ln_business_group_id
from pay_payroll_actions ppa
where payroll_action_id = pactid;
sqlstr := 'SELECT distinct ASG.person_id
FROM per_all_assignments_f ASG,
pay_payroll_actions PPA
WHERE PPA.payroll_action_id = :payroll_action_id
AND ASG.business_group_id = PPA.business_group_id
AND ASG.payroll_id is not null
AND ASG.assignment_type = ''E''
AND ASG.effective_start_date <= PPA.effective_date
AND ASG.effective_end_date >= PPA.start_date
AND EXISTS ( --CHECKING THAT ATLEAST ONE ASSIGN ACT EXIST
SELECT 1
FROM pay_assignment_actions paa
WHERE paa.assignment_id = ASG.assignment_id
AND PAA.action_status = ''C'''
||lv_rc_where||
')
ORDER BY ASG.person_id';
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = cp_business_group_id;
SELECT report_format
FROM pay_report_format_mappings_f
WHERE report_type = cp_report_type
AND report_qualifier = cp_report_qualifier
AND report_category = cp_report_category
AND cp_start_date BETWEEN effective_start_date AND effective_end_date
AND cp_end_date BETWEEN effective_start_date AND effective_end_date;
SELECT effective_date
,pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
,business_group_id
,report_type
,report_qualifier
,report_category
INTO g_effective_date,
g_tax_unit_id,
ln_business_group_id,
lv_report_type,
lv_report_qualifier,
lv_report_category
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
'SELECT distinct paf.assignment_id asg_id,
paa.assignment_action_id assact
FROM per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payrolls_f ppf,
pay_population_ranges ppr
WHERE paf.assignment_id = paa.assignment_id
AND paf.assignment_type = ''E''
AND paf.person_id = ppr.person_id
AND ppr.chunk_number = '|| chunk ||'
AND ppr.payroll_action_id = '|| pactid ||'
AND paf.effective_start_date <= add_months(ppa.effective_date, 12) - 1
AND paf.effective_end_date >= ppa.effective_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in (''R'',''B'',''Q'',''V'',''I'')
AND ppa.business_group_id = paf.business_group_id
AND ppa.effective_date between '''|| l_year_start ||''' AND '''|| l_year_end ||'''
AND ppa.payroll_id = ppf.payroll_id
AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
AND ppf.payroll_id >= 0
AND paf.person_id between '|| stperson ||' and '|| endperson ||'
'|| lv_ac_where ||'
ORDER BY paf.assignment_id';
'SELECT distinct paf.assignment_id asg_id,
paa.assignment_action_id assact
FROM per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payrolls_f ppf
WHERE paf.assignment_id = paa.assignment_id
AND paf.assignment_type = ''E''
AND paf.effective_start_date <= add_months(ppa.effective_date, 12) - 1
AND paf.effective_end_date >= ppa.effective_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in (''R'',''B'',''Q'',''V'',''I'')
AND ppa.business_group_id = paf.business_group_id
AND ppa.effective_date between '''|| l_year_start ||'''
AND '''|| l_year_end ||'''
AND ppa.payroll_id = ppf.payroll_id
AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
AND ppf.payroll_id >= 0
AND paf.person_id between '|| stperson ||' and '|| endperson ||'
'|| lv_ac_where ||'
ORDER BY paf.assignment_id';
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
hr_utility.trace('inserted into temp object actions - ' || lockingactid);
SELECT min(paa.chunk_number)
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id;
select distinct fcl.lookup_code
from fnd_common_lookups fcl,
fnd_lookup_values flv
where fcl.lookup_type = 'YE_ARCH_REPORTS_BAL_ATTRIBUTES'
and fcl.lookup_type = flv.lookup_type
and flv.tag = '+'||cp_leg_code
and fcl.lookup_code = flv.lookup_code;
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = cp_business_group_id;
SELECT attribute_id
FROM pay_bal_attribute_definitions
WHERE attribute_name = cp_attribute_name;
SELECT ppa.legislative_parameters,
ppa.business_group_id,
ppa.start_date,
ppa.effective_date
INTO l_param,
l_business_group_id,
l_start_date,
l_end_date
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT userenv('sessionid')
FROM dual;
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = cp_business_group_id;
SELECT aa.object_id,
aa.chunk_number,
aa.payroll_action_id
into l_asgid,
l_chunk,
l_payroll_action_id
FROM pay_temp_object_actions aa
WHERE aa.object_action_id = p_assignment_action_id;
select ppa.business_group_id
into ln_business_group_id
from pay_payroll_actions ppa
where payroll_action_id = l_payroll_action_id;
c_non_zero_run_balance := 'SELECT 1
FROM DUAL
WHERE EXISTS(
select 1
from pay_run_balances prb,
pay_balance_attributes pba,
pay_assignment_actions paa
where paa.assignment_id = '||to_char(l_asgid)||'
AND paa.assignment_Action_id = prb.assignment_Action_id
AND prb.effective_date between '''||to_char(add_months(g_effective_date,-12)+1)||
''' and '''||to_char(g_effective_date)||'''
and prb.defined_balance_id = pba.defined_balance_id
and pba.attribute_id = '||to_char(ltr_def_bal_status(lv_count).attribute_id) ||
lv_ac_where||')';
c_non_zero_run_result := 'SELECT 1 FROM dual
WHERE EXISTS (SELECT 1
FROM pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payrolls_f ppf
WHERE paa.assignment_id = '||to_char(l_asgid)||'
AND prr.assignment_Action_id = paa.assignment_Action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in (''R'',''B'',''Q'',''V'',''I'')
AND ppa.effective_date between '''||to_char(add_months(g_effective_date, -12) + 1)||
'''AND'''|| to_char(g_effective_date)||'''
AND ppa.payroll_id = ppf.payroll_id
AND ppa.effective_date between ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.payroll_id > 0
AND prrv.run_result_id = prr.run_result_id
AND prrv.result_value <> ''0''
AND piv.input_value_id = prrv.input_value_id
AND ppa.effective_date between piv.effective_Start_date
AND piv.effective_end_date
AND piv.uom = ''M''
and exists (select 1
from pay_balance_feeds_f pbf
where piv.input_value_id = pbf.input_value_id
and ppa.effective_date between pbf.effective_Start_date
AND pbf.effective_end_date
)'|| lv_ac_where||'
)';
insert
into pay_us_rpt_totals(
session_id,
tax_unit_id,
value1,
attribute1,
location_id
)
values
(
g_session_id,
nvl(g_tax_unit_id,0),
l_asgid, --assignment action id passed by PYUGEN
'YEAR END MISSING ASSIGNMENTS',
l_payroll_action_id);
select effective_date,
pay_core_utils.get_parameter('TRANSFER_GRE',
legislative_parameters)
into g_effective_date,
g_tax_unit_id
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
pay_yepp_miss_assign_pkg.select_employee(p_payroll_action_id,
g_effective_date,
g_tax_unit_id,
g_session_id);
DELETE FROM pay_us_rpt_totals
WHERE attribute1='YEAR END MISSING ASSIGNMENTS'
AND location_id = p_payroll_action_id;