[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fnd_date.canonical_to_date(legislation_info1)
from pay_mx_legislation_info_f
where legislation_info_type='MX Social Security Reporting' ;
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,
'LEGAL_EMPLOYER=') - 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 ='IMSS'
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
to_number(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'LEGAL_EMPLOYER=')
+ length('LEGAL_EMPLOYER='),
(instr(legislative_parameters,
'TRANS_GRE=') - 1 )
- (instr(legislative_parameters,
'LEGAL_EMPLOYER=')
+ length('LEGAL_EMPLOYER=')))))) , -- legal_employer
fnd_date.canonical_to_date(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'END_DATE=')
+ length('END_DATE='),
(instr(legislative_parameters,
'LEGAL_EMPLOYER=') - 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
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 /*+INDEX(PFA PAY_FUNCTIONAL_AREAS_PK)
INDEX(PTE PAY_TRIGGER_EVENTS_PK)*/
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 ppe.assignment_id
from pay_process_events ppe,
pay_event_updates peu,
pay_datetracked_events pde
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 ppe.event_update_id = peu.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 (( decode(peu.column_name,''EFFECTIVE_END_DATE'',''1'') = nvl(pde.column_name,''1'')
and decode(peu.event_type,''U'',''E'')=pde.update_type )
or ( nvl(peu.column_name,1) = nvl(pde.column_name,1)
and peu.event_type=pde.update_type ))
and :payroll_action_id > 0 ' ;
select distinct ppe.assignment_id
-- ,paf.person_id,
-- ,per_mx_ssaffl_archive.derive_gre_from_loc_scl(paf.location_id,paf.business_group_id,paf.soft_coding_keyflex_id,ppe.effective_date)
from pay_process_events ppe,
pay_event_updates peu,
pay_datetracked_events pde,
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 ppe.business_group_id = cp_business_group_id
and pde.dated_table_id = peu.dated_table_id
and pde.event_group_id = cp_event_group_id
and (( decode(peu.column_name,'EFFECTIVE_END_DATE','1') = nvl(pde.column_name,'1')
and decode(peu.event_type,'U','E')=pde.update_type )
or ( nvl(peu.column_name,1) = nvl(pde.column_name,1)
and peu.event_type=pde.update_type ) )
and paf.assignment_id = ppe.assignment_id
and paf.assignment_id between cp_start_assignment_id and cp_end_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 ( 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 pay_assignment_actions_s.nextval
into ln_action_id
from dual;
select 'Y'
from per_all_assignments_f paf
where paf.assignment_id <> cp_skip_assignment_id
and paf.person_id = cp_person_id
and cp_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,cp_effective_date)
= cp_gre_id ;
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
,ppf.per_information1 maternal_last_name
,ppf.first_name || ' ' ||ppf.middle_names name
,ppf.employee_number worker_id
from per_all_assignments_f paf,
per_all_people_f ppf
where paf.assignment_id = cp_assignment_id
and paf.person_id = ppf.person_id
and cp_effective_date between paf.effective_start_date and paf.effective_end_date
and cp_effective_date between ppf.effective_start_date and ppf.effective_end_date ;
select 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 aei_information3
from per_assignment_extra_info pae
where pae.assignment_id = cp_assignment_id
and information_type = 'MX_SS_EMP_TRANS_REASON'
and fnd_date.canonical_to_date(aei_information1) = cp_effective_date
and aei_information2 = cp_gre_id ;
select pds_information1, actual_termination_date
from per_periods_of_service ppos,
per_all_assignments_f paf
where paf.assignment_id = cp_assignment_id
and paf.person_id = ppos.person_id
and cp_effective_date between paf.effective_start_date and paf.effective_end_date
and pds_information_category='MX' ;
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 location_code
from hr_locations
where location_id = cp_location_id ;
select name
from hr_organization_units
where organization_id = cp_gre_id ;
Name : process_insert_event
Purpose : This procedure process insert event.
This procedure is called
from interpret_all_asg_events procedure
************************************************************/
PROCEDURE process_insert_event(
p_assignment_action_id in number
,p_assignment_id in number
,p_effective_date in date
)
IS
-- Cursor to check the record exist in the archive table
cursor c_chk_archive ( cp_person_id in number,
cp_gre_id in number,
cp_effective_date date,
cp_action_info_category varchar2 )
is
select 'Y'
from pay_action_information pai,
per_all_assignments_f paf
where pai.action_context_type ='AAP'
and pai.action_information_category = cp_action_info_category
and pai.tax_unit_id = cp_gre_id
and pai.assignment_id = paf.assignment_id
and paf.person_id = cp_person_id
and cp_effective_date between paf.effective_start_date and paf.effective_end_date
order by pai.effective_date desc ;
select paf.person_id,
paf.assignment_number,
paf.location_id,
paf.soft_coding_keyflex_id,
paf.business_group_id
from per_all_assignments_f paf
where paf.assignment_id = cp_assignment_id
and cp_effective_date between paf.effective_start_date
and paf.effective_end_date ;
dbg('Entering process insert event..........' );
lv_procedure_name := '.process_insert_event';
dbg('Exiting process insert event..........' );
END process_insert_event ;
select person_id,
assignment_number,
location_id,
soft_coding_keyflex_id,
business_group_id
from per_all_assignments_f paf
where paf.assignment_id = cp_assignment_id
and cp_effective_date between paf.effective_start_date
and paf.effective_end_date ;
select 'Y'
from pay_action_information pai,
per_all_assignments_f paf
where pai.action_context_type ='AAP'
and pai.action_information_category = cp_action_info_category
and pai.tax_unit_id = cp_gre_id
and pai.assignment_id = paf.assignment_id
and paf.person_id = cp_person_id
and cp_effective_date between paf.effective_start_date and paf.effective_end_date
order by pai.effective_date desc ;
Name : process_update_event
Purpose : This procedure process the update event.
This procedure is called
from interpret_all_asg_events procedure
************************************************************/
PROCEDURE process_update_event ( p_assignment_action_id in number
,p_assignment_id in number
,p_effective_date in date
,p_column_name in varchar
,p_old_value in number
,p_new_value in number
,p_column_name1 in varchar
,p_old_value1 in number
,p_new_value1 in number
)
IS
-- Cursor to get person details
cursor c_person_details(cp_assignment_id in number,
cp_effective_date in date )
is
select person_id,
assignment_number,
location_id,
soft_coding_keyflex_id,
business_group_id
from per_all_assignments_f paf
where paf.assignment_id = cp_assignment_id
and cp_effective_date between paf.effective_start_date
and paf.effective_end_date ;
select 'Y'
from pay_action_information pai,
per_all_assignments_f paf
where pai.action_context_type ='AAP'
and pai.action_information_category = cp_action_info_category
and pai.tax_unit_id = cp_gre_id
and pai.assignment_id = paf.assignment_id
and cp_effective_date between paf.effective_start_date and paf.effective_end_date
and paf.person_id = CP_PERSON_ID
order by pai.effective_date desc ;
dbg('Entering process_update_event ..........');
lv_procedure_name := '.process_update_event';
dbg('Exiting process update event..........' );
END process_update_event;
select paf.person_id,paf.assignment_number,paf.location_id,
paf.soft_coding_keyflex_id, pas.per_system_status,
paf.business_group_id
from per_all_assignments_f paf,
per_assignment_status_types pas
where paf.assignment_id = cp_assignment_id
and pas.assignment_status_type_id = paf.assignment_status_type_id
and cp_effective_date between paf.effective_start_date
and paf.effective_end_date ;
select 'Y'
from pay_action_information pai
where pai.action_context_type ='AAP'
and pai.action_information_category = cp_action_info_category
and pai.assignment_id = cp_assignment_id
and trunc(pai.effective_date) = trunc(cp_effective_date) ;
select PER_SYSTEM_STATUS from per_assignment_status_types
where assignment_status_type_id = cp_assignment_status_type_id ;
lv_insert_found VARCHAR2(1);
dbg('Update_type : '||int_pkg_events(i).update_type ) ;
1) User Entry : Insert record
on 01-JAN-2004 record created
(default record inserted while entering the person record)
Int pkg Returns : Returns one record
Process : Process only the insert record
2) User Entry : Insert followed by multiple correction record
a)on 01-JAN-2004 record inserted
b)DTrack 01-JAN-2004 corrected either location or GRE in the SCL
Int pkg Returns : Returns 3 records
i) Insert
ii) Location correction
iii)SCL correction
Process : Process only the insert and skip the correction records
3) User Entry : Insert record and Update record followed by correction record
a)on 01-JAN-2004 record inserted
b)DTrack 05-JAN-2004 updated either location or GRE in the SCL
c)DTrack 05-JAN-2004 corrected either location or GRE in the SCL
Int pkg Returns : Returns 5 records
i) insert
ii) Location update
iii) SCL update
iv) location correction
v) SCL Correction
Process : process insert and update record with the last correction record.
4) User Entry : Single or multiple correction record
a)DTrack 05-JAN-2004 corrected either location or GRE in the SCL
Int Pkg Returns : Single or Multiple records
Process : Process the last correction record
Example 05-JAN-2004 A to B on last update date 06-Jan-2004 at 10:00 am
05-JAN-2004 B to C on last update date 06-Jan-2004 at 11:00 am
05-JAN-2004 c to D on last update date 06-Jan-2004 at 12:00 am
Take the last record and new value is D
5) User Entry : Single or multiple update record
a)DTrack 05-FEB-2004 updated either location or GRE in the SCL
Int Pkg Returns : Single or Multiple records
Process : Process the update record with both location and scl
6) User Entry : Multiple update record followed by correction record
a)DTrack 05-MAR-2004 updated both location and GRE in the SCL
b)DTrack 05-MAR-2004 corrected both location and GRE in the SCL
Int Pkg Returns : Single or Multiple records
Process : Process the update record with the correction value
7) User Entry : Hire, Correction and Terminate record
a)DTrack 01-JUL-2004 record Inserted
b)DTrack 01-JUL-2004 corrected both location and GRE in the SCL
C)End Employement on 31-JUL-2004 with
actual_termination_date = final process date = 31-JUL-2004
Int Pkg Returns : Single or Multiple records
Process : Process Insert, correction and termination record
8) User Entry : Hire, Correction,Terminate record and Reverse terminate
a)DTrack 01-JUL-2004 record Inserted
b)DTrack 01-JUL-2004 corrected both location and GRE in the SCL
C)End Employement on 31-JUL-2004 with
actual_termination_date = final process date = 31-JUL-2004
d)Reverse Terminate
Int Pkg Returns : Single or Multiple records
Process : Process Insert, correction records ( ignore termination and
corresponding reverse termination)
9) User Entry : Hire, Correction and Terminate record
a)DTrack 01-JUL-2004 record Inserted
b)DTrack 01-JUL-2004 corrected both location and GRE in the SCL
C)End Employement on 31-JUL-2004 with
actual_termination_date = 31-JUL-2004
final process date = null
Int Pkg Returns : Single or Multiple records
Process : Process Insert, correction and termination record
10)User Entry : Hire, Correction,Terminate record and Reverse terminate
a)DTrack 01-JUL-2004 record Inserted
b)DTrack 01-JUL-2004 corrected both location and GRE in the SCL
C)End Employement on 31-JUL-2004 with
actual_termination_date = 31-JUL-2004
final process date = null
d)Reverse Terminate
Int Pkg Returns : Single or Multiple records
Process : Process Insert, correction records ( ignore termination and
corresponding reverse termination)
*/
hr_utility.set_location(gv_package || lv_procedure_name, 20);
dbg('Update_type : '||int_pkg_events(i).update_type ) ;
if int_pkg_events(i).update_type ='I' then
ln_index := asg_events_table.COUNT + 1 ;
asg_events_table(ln_index).update_type := int_pkg_events(i).update_type ;
elsif int_pkg_events(i).update_type ='E' then
-- convert the values from change_values to old and new value
-- change values will have -> ie 31-DEC-12 -> 31-JUL-04
lv_change_values := int_pkg_events(i).change_values ;
asg_events_table(ln_index).update_type := int_pkg_events(i).update_type ;
(int_pkg_events(i).update_type ='C' or
int_pkg_events(i).update_type ='U' ) THEN
-- convert the values from change_values to old and new value
dbg( 'convert the values from change_values to old and new value' );
if int_pkg_events(i).update_type = 'C' then
-- change values will have -> ie 590 -> 610
lv_change_values := int_pkg_events(i).change_values ;
elsif int_pkg_events(i).update_type = 'U' then
lv_old_value := int_pkg_events(i).old_value ;
asg_events_table(ln_index).update_type := 'E' ;
( int_pkg_events(i).update_type ='C' or
int_pkg_events(i).update_type ='U' ) THEN
-- check the row exists in asg_events_table with matching effective_date and
-- update_type = I
lV_insert_found := 'N' ;
asg_events_table(j).update_type = 'I' ) then
lV_insert_found :='Y' ;
if lV_insert_found = 'Y' then
dbg( 'row skipped from int_pkg_events as the insert record exists on the same effective date' );
if int_pkg_events(i).update_type = 'C' then
-- change values will have -> ie 590 -> 610
lv_change_values := int_pkg_events(i).change_values ;
elsif int_pkg_events(i).update_type = 'U' then
lv_old_value := int_pkg_events(i).old_value ;
(asg_events_table(j).update_type = 'U' or asg_events_table(j).update_type = 'C')
) then
lV_row_found :='Y' ;
dbg( 'row updated with current value as multiple correction/update record found' );
asg_events_table(ln_index).update_type := int_pkg_events(i).update_type ;
end if; --lv_insert_found
end if; -- update_type
dbg('Update Type :' || asg_events_table(i).update_type );
if asg_events_table(i).update_type = 'I' then
dbg('call process insert event' );
msg('Processing Insert Event' );
process_insert_event( p_assignment_action_id
,p_assignment_id
,asg_events_table(i).effective_date
) ;
elsif asg_events_table(i).update_type = 'E' then
dbg('call process_endate_event' );
elsif ( asg_events_table(i).update_type = 'C' or
asg_events_table(i).update_type = 'U' ) then
dbg('Event update type is C or U ' );
dbg('Update Type :' || asg_events_table(i).update_type );
if asg_events_table(i).update_type = 'C' then
dbg('call process_correction_event' );
dbg('call process_update_event' );
msg('Processing Update Event' );
process_update_event( p_assignment_action_id
,p_assignment_id
,asg_events_table(i).effective_date
,asg_events_table(i).column_name
,ln_old_value
,ln_new_value
,asg_events_table(i).column_name1
,ln_old_value1
,ln_new_value1
);
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;