[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select business_group_id,
report_qualifier,
to_number(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'GRE_ID=')
+ length('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='))))))
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
Purpose : This returns the select statement that is
used to created the range rows for the
Social Security Affiliation Magnetic DISPMAG report.
Arguments :
******************************************************************/
PROCEDURE range_cursor( p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
ln_business_group_id NUMBER;
lv_sql_string := 'select distinct pai.assignment_id
from pay_action_information pai
where pai.action_information_category =
decode(''' ||lv_affl_type|| ''',''HIRES'',''MX SS HIRE DETAILS'',''SEPARATIONS'',''MX SS SEPARATION DETAILS'')
and pai.action_information22 =''A''
and :payroll_action_id > 0 ' ;
select pai.action_context_id,pai.assignment_id,pai.tax_unit_id
from pay_action_information pai
where pai.assignment_id between cp_start_assignment_id and cp_end_assignment_id
and pai.action_information_category =
decode(cp_affl_type,'HIRES','MX SS HIRE DETAILS','SEPARATIONS','MX SS SEPARATION DETAILS')
and pai.action_information22 ='A'
and pai.action_context_type='AAP'
and (( cp_trans_gre_id is not null and cp_gre_id is not null and pai.tax_unit_id= cp_gre_id )
or ( cp_trans_gre_id is not null and cp_gre_id is null and
pai.tax_unit_id in
(select organization_id
from hr_organization_information hoi
where hoi.org_information_context = 'MX_SOC_SEC_DETAILS'
and ((org_information6 = cp_trans_gre_id ) OR ( organization_id = cp_trans_gre_id and org_information3='Y')))))
and not exists (
select 'Y'
from pay_action_interlocks pal,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pal.locked_action_id = pai.action_context_id
and pal.locking_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type='X'
and ppa.report_type='SS_AFFILIATION'
and ppa.report_qualifier=cp_affl_type
and paa.tax_unit_id = pai.tax_unit_id
) ;
select pay_assignment_actions_s.nextval
into ln_action_id
from dual;
select action_information1, -- employer ss id
action_information2, -- employer ss check digit
action_information3, -- employee ss id
action_information4, -- employee ss check digit
action_information5, -- Paternal Last Name
action_information7, -- Employee Name
action_information8, -- IDW
action_information10, -- Worker Type
action_information11, -- Salary Type
action_information12, -- Reduced Working Week
action_information13, -- Date of Hire/Rehire
action_information17, -- IMSS Waybill
rpad(action_information1,10,' ') || -- employer ss id
nvl(action_information2,' ') || -- employer ss check digit
rpad(action_information3,10,' ') || -- employee ss id
nvl(action_information4,' ') || -- employee ss check digit
rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ') || -- Paternal Last Name
rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ') || -- Maternal Last Name
rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ') || -- Employee Name
lpad(to_char(to_number(nvl(action_information8,'0'))*100),6,'0') || -- IDW
rpad(' ',6,' ') || -- Filler
nvl(action_information10,' ')|| -- Worker Type
nvl(action_information11,' ') || -- Salary Type
nvl(action_information12,' ') || -- Reduced Working Week
rpad(nvl(action_information13,' '),8,' ') || -- Date of Hire/Rehire
lpad(nvl(action_information14,'000'),3,'0') || -- Medical Centre
rpad(' ',2,' ') || -- Filler
action_information16 || -- Type of Trans
rpad(action_information17,5,' ') || -- IMSS Waybill
rpad(action_information18,10,' ') || -- Worker Id
' ' || -- Filler
rpad(action_information20,18,' ')|| -- CURP
action_information21 , -- Layout Identifier
format_data_string(rpad(action_information1,10,' ')) ||
format_data_string(nvl(action_information2,' ')) ||
format_data_string(rpad(action_information3,10,' ')) ||
format_data_string(nvl(action_information4,' ')) ||
format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ')) ||
format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ')) ||
format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ')) ||
format_data_string(lpad(to_char(to_number(nvl(action_information8,'0'))*100),6,'0')) ||
format_data_string(rpad(' ',6,' ')) ||
format_data_string(nvl(action_information10,' ')) ||
format_data_string(nvl(action_information11,' ')) ||
format_data_string(nvl(action_information12,' ')) ||
format_data_string(rpad(nvl(action_information13,' '),8,' ')) ||
format_data_string(lpad(nvl(action_information14,'000'),3,'0')) ||
format_data_string(rpad(' ',2,' ')) ||
format_data_string(action_information16) ||
format_data_string(rpad(action_information17,5,' ')) ||
format_data_string(rpad(action_information18,10,' ')) ||
format_data_string(' ') ||
format_data_string(rpad(action_information20,18,' '))||
format_data_string(action_information21)
from pay_action_information pai,
pay_action_interlocks pal,
pay_assignment_actions paa
where pal.locking_action_id = cp_assignment_action_id
and pal.locked_action_id = pai.action_context_id
and pai.action_context_type ='AAP'
and pai.action_information_category = 'MX SS HIRE DETAILS'
and pai.action_information22 ='A'
and paa.assignment_action_id = pal.locking_action_id
and pai.tax_unit_id = paa.tax_unit_id ;
select action_information1, -- employer ss id
action_information2, -- employer ss check digit
action_information3, -- employee ss id
action_information4, -- employee ss check digit
action_information5, -- Paternal Last Name
action_information7, -- Employee Name
action_information9, -- Date of separation
action_information12, -- IMSS Waybill
action_information14, -- Leaving reason
rpad(action_information1,10,' ') || -- employer ss id
nvl(action_information2,' ') || -- employer ss check digit
rpad(action_information3,10,' ') || -- employee ss id
nvl(action_information4,' ') || -- employee ss check digit
rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ') || -- Paternal Last Name
rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ') || -- Maternal Last Name
rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ') || -- Employee Name
rpad('0',15,'0') || -- Filler
rpad(nvl(action_information9,' '),8,' ') || -- Date of Emp Separation
rpad(' ',5,' ') || -- Filler
action_information11 || -- Type of Trans
rpad(nvl(action_information12,' '),5,' ') || -- IMSS Waybill
rpad(nvl(action_information13,' '),10,' ') || -- Worker Id
nvl(action_information14,' ') || -- Leaving reason
rpad(' ',18,' ') || -- Filler
action_information16 , -- Layout Identifier
format_data_string(rpad(action_information1,10,' ')) ||
format_data_string(nvl(action_information2,' ')) ||
format_data_string(rpad(action_information3,10,' ')) ||
format_data_string(nvl(action_information4,' ')) ||
format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ')) ||
format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ')) ||
format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ')) ||
format_data_string(rpad('0',15,'0')) ||
format_data_string(rpad(action_information9,8,' ')) ||
format_data_string(rpad(' ',5,' ')) ||
format_data_string(action_information11) ||
format_data_string(rpad(action_information12,5,' ')) ||
format_data_string(rpad(action_information13,10,' ')) ||
format_data_string(nvl(action_information14,' ')) ||
format_data_string(rpad(' ',18,' ')) ||
format_data_string(action_information16)
from pay_action_information pai,
pay_action_interlocks pal,
pay_assignment_actions paa
where pal.locking_action_id = cp_assignment_action_id
and pal.locked_action_id = pai.action_context_id
and pai.action_context_type ='AAP'
and pai.action_information_category = 'MX SS SEPARATION DETAILS'
and pai.action_information22 ='A'
and paa.assignment_action_id = pal.locking_action_id
and pai.tax_unit_id = paa.tax_unit_id ;
select action_information1, -- employer ss id
action_information2, -- employer ss check digit
action_information3, -- employee ss id
action_information4, -- employee ss check digit
action_information5, -- Paternal Last Name
action_information7, -- Employee Name
action_information8, -- IDW
action_information10, -- Worker Type
action_information11, -- Salary Type
action_information12, -- Reduced Working Week
action_information13, -- Date of Salary modification
action_information17, -- IMSS Waybill
rpad(action_information1,10,' ') || -- employer ss id
nvl(action_information2,' ') || -- employer ss check digit
rpad(action_information3,10,' ') || -- employee ss id
nvl(action_information4,' ') || -- employee ss check digit
rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ') || -- Paternal Last Name
rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ') || -- Maternal Last Name
rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ') || -- Employee Name
lpad(to_char(to_number(nvl(action_information8,'0'))*100),6,'0') || -- IDW
rpad(' ',6,' ') || -- Filler
nvl(action_information10,' ')|| -- Worker Type
nvl(action_information11,' ') || -- Salary Type
nvl(action_information12,' ') || -- Reduced Working Week
rpad(nvl(action_information13,' '),8,' ') || -- Date of Salary Modification
rpad(' ',5,' ') || -- Filler 5 spaces 3 for med center and 2
action_information16 || -- Type of Trans
rpad(action_information17,5,' ') || -- IMSS Waybill
rpad(action_information18,10,' ') || -- Worker Id
' ' || -- Filler
rpad(action_information20,18,' ')|| -- CURP
action_information21 , -- Layout Identifier
format_data_string(rpad(action_information1,10,' ')) ||
format_data_string(nvl(action_information2,' ')) ||
format_data_string(rpad(action_information3,10,' ')) ||
format_data_string(nvl(action_information4,' ')) ||
format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information5)),' '),1,27),27,' ')) ||
format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information6)),' '),1,27),27,' ')) ||
format_data_string(rpad(substr(nvl(pay_mx_rules.STRIP_SPL_CHARS(upper(action_information7)),' '),1,27),27,' ')) ||
format_data_string(lpad(to_char(to_number(nvl(action_information8,'0'))*100),6,'0')) ||
format_data_string(rpad(' ',6,' ')) ||
format_data_string(nvl(action_information10,' ')) ||
format_data_string(nvl(action_information11,' ')) ||
format_data_string(nvl(action_information12,' ')) ||
format_data_string(rpad(nvl(action_information13,' '),8,' ')) ||
format_data_string(rpad(' ',5,' ')) ||
format_data_string(action_information16) ||
format_data_string(rpad(action_information17,5,' ')) ||
format_data_string(rpad(action_information18,10,' ')) ||
format_data_string(' ') ||
format_data_string(rpad(action_information20,18,' '))||
format_data_string(action_information21)
from pay_action_information pai
where pai.action_context_id = cp_assignment_action_id
and pai.action_information_category = 'MX SS SALARY DETAILS'
and pai.action_context_type ='AAP'
and pai.action_information22 ='A' ;
select org_information5
from hr_organization_information
where org_information_context= 'MX_SOC_SEC_DETAILS'
and organization_id = cp_organization_id ;