[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
05-sep-2005 rsethupa 115.1 Delete records from PAY_US_RPT_TOTALS
in DEINIT code
13-Sep-2005 sdhole 115.3 4577187 Changed the report type from YEMA to
YREND_YEMA.
16-Sep-2005 sdhole 115.4 4613898 Modified ARCHIVE_INIT,ARCHIVE_DEINIT,
ARCHIVE_INIT,ARCHIVE_CODE procedures.
23-sep-2005 djoshi 115.5 462035 Modified the Package.
1. Archive Init commented
2. Archive_code modified
*/
----------------------------------- range_cursor ----------------------------------
--
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
--
lv_legislative_param varchar2(240);
select legislative_parameters
into lv_legislative_param
from pay_payroll_actions
where payroll_action_id = pactid;
sqlstr := 'SELECT distinct ASG.person_id
FROM per_all_assignments_f ASG,
pay_us_asg_reporting PUAR,
pay_payroll_actions PPA
WHERE PPA.payroll_action_id = :payroll_action_id
AND PUAR.tax_unit_id = pay_us_payroll_utils.get_parameter(
''TRANSFER_GRE'',
legislative_parameters)
AND PUAR.assignment_id = ASG.assignment_id
AND ASG.assignment_type = ''E''
AND ASG.effective_start_date <= PPA.effective_date
AND ASG.effective_end_date >= PPA.start_date
AND ASG.business_group_id + 0 = PPA.business_group_id
AND ASG.payroll_id is not null
ORDER BY ASG.person_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,
pay_us_asg_reporting puar
WHERE paf.assignment_id = paa.assignment_id
AND paf.assignment_id = puar.assignment_id
AND puar.tax_unit_id = cp_tax_unit_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 cp_year_start
AND cp_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 paa.tax_unit_id = cp_tax_unit_id
AND paf.person_id between cp_start_person_id and cp_end_person_id
AND not exists (SELECT 1
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.report_type = 'YREND'
AND ppa.action_status = 'C'
AND ppa.effective_date = cp_year_end
AND to_number(substr(legislative_parameters,
instr(legislative_parameters,'TRANSFER_GRE=')+
length('TRANSFER_GRE='))) = cp_tax_unit_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.business_group_id = paf.business_group_id
AND paa.serial_number = to_char(paf.person_id))
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,
pay_us_asg_reporting puar,
pay_population_ranges ppr
WHERE paf.assignment_id = paa.assignment_id
AND paf.assignment_id = puar.assignment_id
AND puar.tax_unit_id = cp_tax_unit_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 cp_year_start
AND cp_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 paa.tax_unit_id = cp_tax_unit_id
AND paf.person_id between cp_start_person_id and cp_end_person_id
AND not exists (SELECT 1
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ppa.report_type = 'YREND'
AND ppa.action_status = 'C'
AND ppa.effective_date = cp_year_end
AND to_number(substr(legislative_parameters,
instr(legislative_parameters,'TRANSFER_GRE=')+
length('TRANSFER_GRE='))) = cp_tax_unit_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.business_group_id = paf.business_group_id
AND paa.serial_number = to_char(paf.person_id))
ORDER BY paf.assignment_id;
select effective_date,
pay_us_payroll_utils.get_parameter(
'TRANSFER_GRE',
legislative_parameters)
into g_effective_date,
g_tax_unit_id
from pay_payroll_actions
where payroll_action_id = pactid;
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 1
FROM DUAL
WHERE EXISTS(
select 1
from pay_run_balances prb,
pay_balance_attributes pba,
pay_assignment_actions paa
where paa.assignment_id = cp_assignment_id
AND paa.tax_unit_id = cp_tax_unit_id
AND paa.tax_unit_id = prb.tax_unit_id
AND paa.assignment_Action_id = prb.assignment_Action_id
AND prb.effective_date between add_months(cp_effective_date,-12)+1
and cp_effective_date
and prb.defined_balance_id = pba.defined_balance_id
and pba.attribute_id = cp_bal_attribute_id );
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 = cp_assignment_id
AND paa.tax_unit_id = cp_tax_unit_id
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 cp_effective_date
AND add_months(cp_effective_date, 12) - 1
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
)
);
SELECT userenv('sessionid')
FROM dual;
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;
insert
into pay_us_rpt_totals(
session_id,
tax_unit_id,
value1,
attribute1,
location_id
)
values
(
g_session_id,
g_tax_unit_id,
l_asgid,
'YEAR END MISSING ASSIGNMENTS',
l_payroll_action_id);
select effective_date,
pay_us_payroll_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_us_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;