[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT effective_date,
business_group_id,
pay_mx_utility.get_legi_param_val( 'LEGAL_EMPLOYER'
,legislative_parameters),
pay_mx_utility.get_legi_param_val( 'SORT_OPT'
,legislative_parameters) ,
pay_mx_utility.get_legi_param_val( 'ASS_SET'
,legislative_parameters)
, pay_mx_utility.get_legi_param_val( 'RPT_YEAR'
,legislative_parameters)
, pay_mx_utility.get_legi_param_val( 'START_DATE'
,legislative_parameters)
,pay_mx_utility.get_legi_param_val( 'END_DATE'
,legislative_parameters)
FROM pay_payroll_actions
WHERE payroll_action_id = cp_payroll_action_id;
SELECT assignment_set_name
FROM hr_assignment_sets
WHERE assignment_set_id = cp_ass_set_id;
SELECT DISTINCT paa_arch.serial_number
FROM pay_assignment_actions paa_arch
,pay_payroll_actions ppa_arch
WHERE ppa_arch.business_group_id = '|| gn_business_group_id ||'
AND ppa_arch.report_type = ''MX_YREND_ARCHIVE''
AND ppa_arch.report_qualifier = ''MX''
AND ppa_arch.report_category = ''ARCHIVE''
AND pay_mx_utility.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',
ppa_arch.legislative_parameters) = '||gn_legal_er_id||'
AND TRUNC(ppa_arch.effective_date,''Y'') =
fnd_date.canonical_to_date('''||
fnd_date.date_to_canonical(gd_effective_date)||''')
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status = ''C''
AND :p_payroll_action_id = '||p_payroll_action_id||'
ORDER BY serial_number';
SELECT paa_arch.assignment_action_id
,paa_arch.assignment_id
,paa_arch.serial_number person_id
,ppa_arch.payroll_action_id
FROM pay_assignment_actions paa_arch,
pay_payroll_actions ppa_arch
WHERE ppa_arch.business_group_id = cp_business_group_id
AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
AND ppa_arch.report_qualifier = 'MX'
AND ppa_arch.report_category = 'ARCHIVE'
AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
ppa_arch.legislative_parameters) = cp_legal_er_id
AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status = 'C'
AND paa_arch.serial_number BETWEEN cp_start_person_id
AND cp_end_person_id
AND (gv_ass_set_id IS NULL OR (exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = gv_ass_set_id
and hasa.assignment_id = paa_arch.assignment_id
and upper(hasa.include_or_exclude) = 'I')))
ORDER BY paa_arch.serial_number,
paa_arch.assignment_action_id desc;
SELECT paa_arch.assignment_action_id
,paa_arch.assignment_id
,paa_arch.serial_number person_id
,ppa_arch.payroll_action_id
FROM pay_assignment_actions paa_arch,
pay_payroll_actions ppa_arch,
pay_population_ranges ppr
WHERE ppa_arch.business_group_id = cp_business_group_id
AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
AND ppa_arch.report_qualifier = 'MX'
AND ppa_arch.report_category = 'ARCHIVE'
AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
ppa_arch.legislative_parameters) = cp_legal_er_id
AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status = 'C'
AND paa_arch.serial_number = ppr.person_id
AND ppr.chunk_number = cp_chunk
AND ppr.payroll_action_id = cp_payroll_action_id
AND (gv_ass_set_id IS NULL OR (exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = gv_ass_set_id
and hasa.assignment_id = paa_arch.assignment_id
and upper(hasa.include_or_exclude) = 'I')))
ORDER BY paa_arch.serial_number,
paa_arch.assignment_action_id desc;
SELECT pay_assignment_actions_s.nextval
INTO ln_mag_asg_act_id
FROM dual;
SELECT DISTINCT subemp_rpt.person_id PERSON_ID
,pos.period_of_service_id pos_id
,ppf.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
,subemp_rpt.ER_LEGAL_NAME ER_LEGAL_NAME
,subemp_rpt.FISCAL_YEAR_REPORTING FISCAL_YEAR
,replace(RFC_ID,'-','') RFC_ID
,CURP
,pos.date_start HIRE_DATE
,pos.actual_termination_date SEPARATION_DATE
,ltrim(rtrim(PATERNAL_LAST_NAME))||' '||
ltrim(rtrim(MATERNAL_LAST_NAME))||' '||
ltrim(rtrim(NAMES)) NAME
,nvl( ISR_SUBSIDY_FOR_EMP_PAID,0) ISR_SUBSIDY_FOR_EMPL_PAID
,nvl( ISR_SUBSIDY_FOR_EMP,0) ISR_SUBSIDY_FOR_EMP
,nvl(TOTAL_SUBJECT_EARNINGS,0)+
nvl(TOTAL_EXEMPT_EARNINGS,0) TOTAL_EARNINGS
FROM PAY_MX_ISR_TAX_FORMAT37_V subemp_rpt
,pay_assignment_actions paa
,pay_action_interlocks pai
,per_periods_of_service pos
,per_all_people_f ppf
,per_all_assignments_f paf
WHERE subemp_rpt.legal_employer_id = gn_legal_er_id
AND subemp_rpt.payroll_action_id = paa.payroll_action_id
AND subemp_rpt.person_id = to_number(paa.serial_number)
and paa.assignment_id = paf.assignment_id
AND pos.person_id = subemp_rpt.person_id
AND paa.assignment_action_id = pai.locked_action_id
AND pai.locking_action_id = cp_assignment_action_id
AND paa.assignment_action_id = subemp_rpt.assignment_action_id
AND subemp_rpt.effective_date BETWEEN gd_start_date AND gd_end_date
AND ppf.person_id = pos.person_id
AND pos.period_of_service_id = paf.period_of_service_id
AND subemp_rpt.effective_date between ppf.effective_start_date AND ppf.effective_end_date
AND NVL(pos.actual_termination_date, gd_end_date) > gd_start_date;
SELECT paa.payroll_action_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_assignment_action_id;
subemppaid_xml_tbl.DELETE;
SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10)
INTO EOL
FROM dual;
subemppaid_xml_tbl.DELETE;
SELECT COUNT(*)
INTO ln_count
FROM pay_us_rpt_totals
WHERE tax_unit_id = ln_payroll_action_id
AND session_id = ln_session_id;
INSERT INTO pay_us_rpt_totals ( session_id
,business_group_id
,tax_unit_id
,organization_name )
VALUES ( ln_session_id
,gn_business_group_id
,ln_payroll_action_id
,subemppaid.ER_LEGAL_NAME );
SELECT hou.name
FROM hr_organization_units hou
WHERE hou.organization_id = gn_legal_er_id;
subemppaid_xml_tbl.delete;
lv_rpt_sort_opt := hr_general.decode_lookup('REPORT_SELECT_SORT_CODE',gv_sort_opt);