[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fnd_date.canonical_to_date(ltrim(rtrim(substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,
'END_DATE=')
+ length('END_DATE='),
(instr(ppa.legislative_parameters,
'TRANS_GRE=') - 1 )
- (instr(ppa.legislative_parameters,
'END_DATE=')
+ length('END_DATE='))))))
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.tax_unit_id = cp_tax_unit_id
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.report_type='SS_AFFILIATION'
and ppa.report_qualifier ='SALARY'
order by paa.payroll_action_id desc ;
select fnd_date.canonical_to_date(org_information6)
from hr_organization_information
where org_information_context= 'MX_TAX_REGISTRATION'
and organization_id = cp_organization_id ;
select business_group_id,
to_number(substr(legislative_parameters,
instr(legislative_parameters,
'GRE_ID=')
+ length('GRE_ID='))) , -- gre_id
to_number(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'TRANS_GRE=')
+ length('TRANS_GRE='),
(instr(legislative_parameters,
'GRE_ID=') - 1 )
- (instr(legislative_parameters,
'TRANS_GRE=')
+ length('TRANS_GRE=')))))) , -- trans_gre
fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'END_DATE=')
+ length('END_DATE='),
(instr(legislative_parameters,
'TRANS_GRE=') - 1 )
- (instr(legislative_parameters,
'END_DATE=')
+ length('END_DATE=')))))), -- end_date
fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'START_DATE=')
+ length('START_DATE='),
(instr(legislative_parameters,
'END_DATE=') - 1 )
- (instr(legislative_parameters,
'START_DATE=')
+ length('START_DATE=')))))), -- start_date
fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'PERIOD_ENDING_DATE=')
+ length('PERIOD_ENDING_DATE='),
(instr(legislative_parameters,
'START_DATE=') - 1 )
- (instr(legislative_parameters,
'PERIOD_ENDING_DATE=')
+ length('PERIOD_ENDING_DATE=')))))), -- period_ending_date
trunc( add_months (
fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'PERIOD_ENDING_DATE=')
+ length('PERIOD_ENDING_DATE='),
(instr(legislative_parameters,
'START_DATE=') - 1 )
- (instr(legislative_parameters,
'PERIOD_ENDING_DATE=')
+ length('PERIOD_ENDING_DATE=')))))) , -1 ),'MM'), -- period_start_date
ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'REPORT_MODE=')
+ length('REPORT_MODE='),
(instr(legislative_parameters,
'PERIOD_ENDING_DATE=') - 1 )
- (instr(legislative_parameters,
'REPORT_MODE=')
+ length('REPORT_MODE='))))) -- report_mode
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
select fnd_date.canonical_to_date(org_information6)
from hr_organization_information
where org_information_context= 'MX_TAX_REGISTRATION'
and organization_id = cp_organization_id ;
select event_group_id
from pay_event_groups
where event_group_name = cp_event_group_name ;
Purpose : This returns the select statement that is
used to created the range rows for the
Social Security Affiliation Archiver.
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE range_cursor( p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
CURSOR c_chk_dyn_triggers_enabled(cp_func_area in VARCHAR2)
IS
select pte.short_name
from pay_functional_areas pfa,
pay_functional_triggers pft,
pay_trigger_events pte
where pfa.short_name = cp_func_area
and pfa.area_id = pft.area_id
and pft.event_id = pte.event_id
and ( pte.generated_flag <> 'Y' or pte.enabled_flag <> 'Y' ) ;
lv_sql_string := 'select distinct paf.person_id
from pay_process_events ppe,
pay_datetracked_events pde,
pay_event_updates peu,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info petei,
per_all_assignments_f paf
where ppe.creation_date between
fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_start_date) || ''')
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_end_date) || ''')
and peu.event_update_id =ppe.event_update_id
and peu.dated_table_id = pde.dated_table_id
and pde.event_group_id = ''' ||ln_event_group_id || '''
and ppe.business_group_id = ''' ||ln_business_group_id || '''
and nvl(peu.column_name,1) = nvl(pde.column_name,1)
and decode(pde.update_type,''I'',''INSERT'',''U'',''UPDATE'',pde.update_type) = peu.event_type
and peu.change_type = ''DATE_EARNED''
and pee.element_entry_id = ppe.surrogate_key
and pet.element_type_id = pee.element_type_id
and petei.element_type_id = pee.element_type_id
and petei.eei_information_category=''PQP_UK_RATE_TYPE''
and petei.eei_information1=''MX_IDWF''
and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
and paf.assignment_id = ppe.assignment_id
and ppe.effective_date between paf.effective_start_date and paf.effective_end_date
and (( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date) = -1 )
or ( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date) = -2 )
or ( ''' ||ln_tran_gre_id || ''' is not null and ''' ||ln_gre_id || ''' is not null and
per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)=''' ||ln_gre_id || ''' )
or ( ''' ||ln_tran_gre_id || ''' is not null and ''' ||ln_gre_id || ''' is null and
per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)
in
(select organization_id
from hr_organization_information hoi
where hoi.org_information_context = ''MX_SOC_SEC_DETAILS''
and ((org_information6 = ''' ||ln_tran_gre_id || ''' ) OR
( organization_id = ''' ||ln_tran_gre_id || ''' and org_information3=''Y'')))))
and :payroll_action_id > 0 ' ;
lv_sql_string := 'select paf1.person_id
from
(
select distinct paf.assignment_id
from pay_process_events ppe,
pay_datetracked_events pde,
pay_event_updates peu,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info petei,
per_all_assignments_f paf
where ppe.creation_date between
fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_start_date) || ''')
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_end_date) || ''')
and peu.event_update_id =ppe.event_update_id
and peu.dated_table_id = pde.dated_table_id
and pde.event_group_id = ''' ||ln_event_group_id || '''
and ppe.business_group_id = ''' ||ln_business_group_id || '''
and nvl(peu.column_name,1) = nvl(pde.column_name,1)
and decode(pde.update_type,''I'',''INSERT'',''U'',''UPDATE'',pde.update_type) = peu.event_type
and peu.change_type = ''DATE_EARNED''
and pee.element_entry_id = ppe.surrogate_key
and pet.element_type_id = pee.element_type_id
and petei.element_type_id = pee.element_type_id
and petei.eei_information_category=''PQP_UK_RATE_TYPE''
and petei.eei_information1=''MX_IDWF''
and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
and paf.assignment_id = ppe.assignment_id
and ppe.effective_date between paf.effective_start_date and paf.effective_end_date
union
select distinct pee.assignment_id
from pay_element_entries_f pee,
pay_element_type_extra_info petei
where pee.effective_start_date between
fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_start_date) || ''')
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || ''')
and petei.element_type_id = pee.element_type_id
and petei.eei_information_category=''PQP_UK_RATE_TYPE''
and petei.eei_information1=''MX_IDWV''
) x,
per_all_assignments_f paf1
where x.assignment_id = paf1.assignment_id
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || ''')
between paf1.effective_start_date and paf1.effective_end_date
and (( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || ''')) = -1 )
or ( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || ''')) = -2 )
or ( ''' ||ln_tran_gre_id || ''' is not null and ''' ||ln_gre_id || ''' is not null and
per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || '''))=''' ||ln_gre_id || ''')
or ( ''' ||ln_tran_gre_id || ''' is not null and ''' ||ln_gre_id || ''' is null and
per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(ld_period_end_date) || '''))
in
(select organization_id
from hr_organization_information hoi
where hoi.org_information_context = ''MX_SOC_SEC_DETAILS''
and ((org_information6 = ''' ||ln_tran_gre_id || ''' ) OR
( organization_id = ''' ||ln_tran_gre_id || ''' and org_information3=''Y'')))))
and :payroll_action_id > 0 ' ;
select distinct ppe.assignment_id
from pay_process_events ppe,
pay_datetracked_events pde,
pay_event_updates peu,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info petei,
per_all_assignments_f paf
where ppe.creation_date between cp_start_date and cp_end_date
and peu.event_update_id =ppe.event_update_id
and peu.dated_table_id = pde.dated_table_id
and pde.event_group_id = cp_event_group_id
and ppe.business_group_id = cp_business_group_id
and nvl(peu.column_name,1) = nvl(pde.column_name,1)
and decode(pde.update_type,'I','INSERT','U','UPDATE',pde.update_type) = peu.event_type
and peu.change_type = 'DATE_EARNED'
and pee.element_entry_id = ppe.surrogate_key
and pet.element_type_id = pee.element_type_id
and petei.element_type_id = pee.element_type_id
and petei.eei_information_category='PQP_UK_RATE_TYPE'
and petei.eei_information1='MX_IDWF'
and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
and paf.assignment_id = ppe.assignment_id
and paf.person_id between cp_start_person_id and cp_end_person_id
and ppe.effective_date between paf.effective_start_date and paf.effective_end_date
and (( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date) = -1 )
or ( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date) = -2 )
or ( cp_tran_gre_id is not null and cp_gre_id is not null and
per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)=cp_gre_id )
or ( cp_tran_gre_id is not null and cp_gre_id is null and
per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)
in
(select organization_id
from hr_organization_information hoi
where hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
and ((org_information6 = cp_tran_gre_id ) OR ( organization_id = cp_tran_gre_id and org_information3='Y')))))
;
select x.assignment_id
from
(
select distinct paf.assignment_id
from pay_process_events ppe,
pay_datetracked_events pde,
pay_event_updates peu,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info petei,
per_all_assignments_f paf
where ppe.creation_date between cp_start_date and cp_end_date
and peu.event_update_id =ppe.event_update_id
and peu.dated_table_id = pde.dated_table_id
and pde.event_group_id = cp_event_group_id
and ppe.business_group_id = cp_business_group_id
and nvl(peu.column_name,1) = nvl(pde.column_name,1)
and decode(pde.update_type,'I','INSERT','U','UPDATE',pde.update_type) = peu.event_type
and peu.change_type = 'DATE_EARNED'
and pee.element_entry_id = ppe.surrogate_key
and pet.element_type_id = pee.element_type_id
and petei.element_type_id = pee.element_type_id
and petei.eei_information_category='PQP_UK_RATE_TYPE'
and petei.eei_information1='MX_IDWF'
and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
and paf.assignment_id = ppe.assignment_id
and ppe.effective_date between paf.effective_start_date and paf.effective_end_date
union
select distinct pee.assignment_id
from pay_element_entries_f pee,
pay_element_type_extra_info petei
where pee.effective_start_date between cp_period_start_date and cp_period_end_date
and petei.element_type_id = pee.element_type_id
and petei.eei_information_category='PQP_UK_RATE_TYPE'
and petei.eei_information1='MX_IDWV'
) x,
per_all_assignments_f paf1
where x.assignment_id = paf1.assignment_id
and paf1.person_id between cp_start_person_id and cp_end_person_id
and cp_period_end_date between paf1.effective_start_date and paf1.effective_end_date
and (( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
cp_period_end_date) = -1 )
or ( per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
cp_period_end_date) = -2 )
or ( cp_tran_gre_id is not null and cp_gre_id is not null and
per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,
cp_period_end_date)=cp_gre_id )
or ( cp_tran_gre_id is not null and cp_gre_id is null and
per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf1.location_id,paf1.business_group_id,paf1.soft_coding_keyflex_id,cp_period_end_date)
in
(select organization_id
from hr_organization_information hoi
where hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
and ((org_information6 = cp_tran_gre_id )
OR ( organization_id = cp_tran_gre_id and org_information3='Y'))))) ;
select pay_assignment_actions_s.nextval
into ln_action_id
from dual;
select sum(decode(to_number(puci.value),0,0,1))
from pay_user_column_instances_f puci,
pay_user_columns puc
where puc.user_column_name = cp_workschedule
and puc.legislation_code='MX'
and puc.user_column_id = puci.user_column_id ;
select replace(ppf.per_information3,'-','') emp_ss_number
,ppf.last_name paternal_last_name
,per_information1 maternal_last_name
,ppf.first_name || ' ' || ppf.middle_names name
,substr(employment_category,3,1) worker_type
,hsc.segment6 salary_type
,puc.user_column_name work_schedule
,per_information4 med_center
,employee_number worker_id
,national_identifier CURP
from per_all_assignments_f paf,
per_all_people_f ppf,
hr_soft_coding_keyflex hsc,
pay_user_columns puc
where paf.assignment_id = cp_assignment_id
and paf.person_id = ppf.person_id
and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id (+)
and hsc.segment4 = puc.user_column_id(+)
and trunc(cp_effective_date) between paf.effective_start_date and paf.effective_end_date
and trunc(cp_effective_date) between ppf.effective_start_date and ppf.effective_end_date ;
select replace(org_information1,'-','')
from hr_organization_information
where org_information_context= 'MX_SOC_SEC_DETAILS'
and organization_id = cp_gre_id ;
select org_information3,org_information5, org_information6
from hr_organization_information
where org_information_context= 'MX_SOC_SEC_DETAILS'
and organization_id = cp_organization_id ;
select legislation_info2
from pay_mx_legislation_info_f
where legislation_info_type='MX Minimum Wage Information'
and legislation_info1='MWA'
and cp_effective_date between effective_start_date and effective_end_date ;
dbg('call api to insert the record in pay action information with parameters' );
select paa.payroll_action_id,
paa.assignment_id,
paa.tax_unit_id
from pay_assignment_actions paa
where paa.assignment_action_id = cp_assignment_action;
select max(ppe.effective_date)
from pay_process_events ppe,
pay_datetracked_events pde,
pay_event_updates peu,
pay_element_entries_f pee,
pay_element_types_f pet,
pay_element_type_extra_info petei
where ppe.assignment_id = cp_assignment_id
and ppe.creation_date between cp_start_date and cp_end_date
and peu.event_update_id =ppe.event_update_id
and peu.dated_table_id = pde.dated_table_id
and pde.event_group_id = cp_event_group_id
and ppe.business_group_id = cp_business_group_id
and nvl(peu.column_name,1) = nvl(pde.column_name,1)
and decode(pde.update_type,'I','INSERT','U','UPDATE',pde.update_type) = peu.event_type
and peu.change_type = 'DATE_EARNED'
and pee.element_entry_id = ppe.surrogate_key
and pet.element_type_id = pee.element_type_id
and petei.element_type_id = pee.element_type_id
and petei.eei_information_category='PQP_UK_RATE_TYPE'
and petei.eei_information1='MX_IDWF'
and ppe.effective_date between pee.effective_start_date and pee.effective_end_date
group by ppe.assignment_id ;
select location_code ,
assignment_number,
per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,cp_effective_date)
from per_all_assignments_f paf,
hr_locations hrl
where paf.assignment_id = cp_assignment_id
and cp_effective_date between paf.effective_start_date and paf.effective_end_date
and hrl.location_id = paf.location_id ;