[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pay_mx_utility.get_legi_param_val('GRE',
ppa_mag.legislative_parameters),
fnd_date.date_to_canonical (start_date),
fnd_date.date_to_canonical (effective_date)
FROM pay_payroll_actions ppa_mag
WHERE ppa_mag.payroll_action_id = p_payroll_action_id;
SELECT DISTINCT paf.person_id
FROM per_assignments_f paf,
pay_payroll_actions ppa_sua,
pay_assignment_actions paa_sua
WHERE ppa_sua.payroll_action_id = paa_sua.payroll_action_id
AND paa_sua.assignment_id = paf.assignment_id
AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
(''END_DATE'',
ppa_sua.legislative_parameters))
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
(''END_DATE'',
ppa_sua.legislative_parameters))
BETWEEN fnd_date.canonical_to_date (''' || g_start_date ||
''') AND fnd_date.canonical_to_date (''' || g_end_date ||
''') AND pay_mx_utility.get_legi_param_val (''GRE'',
ppa_sua.legislative_parameters) = '||
g_gre_id ||
' AND ppa_sua.report_type = ''SUA_MAG''
AND ppa_sua.report_qualifier = ''SUA_MAG''
AND ppa_sua.report_category = ''RT''
AND ppa_sua.action_status = ''C''
AND :p_payroll_action_id > 0';
SELECT DISTINCT paf.person_id,
paf.assignment_id,
paa_sua.assignment_action_id,
paf.primary_flag,
paf.effective_end_date
FROM per_assignments_f paf,
pay_payroll_actions ppa_sua,
pay_assignment_actions paa_sua,
pay_action_interlocks lck,
pay_action_information pai
WHERE ppa_sua.payroll_action_id = paa_sua.payroll_action_id
AND paa_sua.assignment_id = paf.assignment_id
AND paa_sua.assignment_action_id = lck.locking_action_id
AND lck.locked_action_id = pai.action_context_id
AND pai.action_information_category = 'MX SS TRANSACTIONS'
AND pai.action_information4 = '12'
AND pai.action_information23 IS NOT NULL -- Absence end date
AND pai.action_information20 IN ('1', '3')--Type of risk(Bug 5688450)
AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
('END_DATE',
ppa_sua.legislative_parameters))
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
('END_DATE',
ppa_sua.legislative_parameters))
BETWEEN fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
AND pay_mx_utility.get_legi_param_val ('GRE',
ppa_sua.legislative_parameters) =
g_gre_id
AND paf.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND ppa_sua.report_type = 'SUA_MAG'
AND ppa_sua.report_qualifier = 'SUA_MAG'
AND ppa_sua.report_category = 'RT'
AND ppa_sua.action_status = 'C'
ORDER BY paf.person_id,
decode (paf.primary_flag, 'Y', 1, 2),
paf.assignment_id,
paf.effective_end_date;
SELECT DISTINCT paf.person_id,
paf.assignment_id,
paa_sua.assignment_action_id,
paf.primary_flag,
paf.effective_end_date
FROM per_assignments_f paf,
pay_payroll_actions ppa_sua,
pay_assignment_actions paa_sua,
pay_action_interlocks lck,
pay_action_information pai,
pay_population_ranges ppr
WHERE ppa_sua.payroll_action_id = paa_sua.payroll_action_id
AND paa_sua.assignment_id = paf.assignment_id
AND paa_sua.assignment_action_id = lck.locking_action_id
AND lck.locked_action_id = pai.action_context_id
AND pai.action_information_category = 'MX SS TRANSACTIONS'
AND pai.action_information4 = '12'
AND pai.action_information23 IS NOT NULL -- Absence end date
AND pai.action_information20 IN ('1', '3')--Type of risk(Bug 5688450)
AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
('END_DATE',
ppa_sua.legislative_parameters))
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
('END_DATE',
ppa_sua.legislative_parameters))
BETWEEN fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
AND pay_mx_utility.get_legi_param_val ('GRE',
ppa_sua.legislative_parameters) =
g_gre_id
AND ppr.payroll_action_id = p_payroll_action_id
AND ppr.chunk_number = p_chunk
AND ppr.person_id = paf.person_id
AND ppa_sua.report_type = 'SUA_MAG'
AND ppa_sua.report_qualifier = 'SUA_MAG'
AND ppa_sua.report_category = 'RT'
AND ppa_sua.action_status = 'C'
ORDER BY paf.person_id,
decode (paf.primary_flag, 'Y', 1, 2),
paf.assignment_id,
paf.effective_end_date;
SELECT pay_assignment_actions_s.nextval
INTO ln_wri_asg_act
FROM dual;
SELECT pai.action_information_id,
nvl(pai.action_information10, 'N') -- Do not report flag
FROM pay_action_information pai,
pay_action_interlocks lck_sua,
pay_action_interlocks lck_arch
WHERE lck_sua.locking_action_id = cp_wri_asg_act
AND lck_sua.locked_action_id = lck_arch.locking_action_id
AND lck_arch.locked_action_id = pai.action_context_id
AND pai.action_information_category = 'MX SS TRANSACTIONS'
AND pai.action_information4 = '12'
AND pai.action_information23 IS NOT NULL; -- Absence end date
SELECT DISTINCT paf.person_id
FROM pay_assignment_actions paa,
per_assignments_f paf
WHERE paf.assignment_id = paa.assignment_id
AND paa.assignment_action_id = cp_asg_act_id;
SELECT pai.action_information_id
FROM pay_action_information pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_interlocks lck,
pay_assignment_actions paa_arch,
pay_payroll_actions ppa_arch
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND fnd_number.canonical_to_number(
pay_mx_utility.get_legi_param_val('GRE',
ppa.legislative_parameters)) = g_gre_id
AND fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa.legislative_parameters)) <=
fnd_date.canonical_to_date(g_end_date)
AND paa.assignment_action_id = lck.locking_action_id
AND lck.locked_action_id = paa_arch.assignment_action_id
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND pai.action_context_id = paa_arch.assignment_action_id
AND pai.action_information_category = 'MX SS PERSON INFORMATION'
AND nvl(pai.action_information21, 'N') = 'N' -- Do not report flag
AND pai.action_information1 = cp_person_id
AND ppa.action_type = 'X'
AND ppa.report_type = 'SUA_MAG'
AND ppa.report_qualifier = 'SUA_MAG'
AND ppa.report_category = 'RT'
AND ppa.action_status = 'C'
AND ppa_arch.action_type = 'X'
AND ppa_arch.report_type = 'SS_ARCHIVE'
AND ppa_arch.report_qualifier = 'SS_ARCHIVE'
AND ppa_arch.report_category = 'RT'
AND ppa_arch.action_status = 'C'
ORDER BY fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa.legislative_parameters)) DESC;
/*SELECT pai.action_information_id
FROM pay_action_information pai,
pay_action_interlocks lck_sua,
pay_action_interlocks lck_arch,
pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch
WHERE lck_sua.locking_action_id = cp_wri_asg_act
AND lck_sua.locked_action_id = lck_arch.locking_action_id
AND lck_arch.locked_action_id = pai.action_context_id
AND pai.action_information_category = 'MX SS PERSON INFORMATION'
AND nvl(pai.action_information21, 'N') = 'N' -- Do not report flag
AND pai.action_context_id = paa_arch.assignment_action_id
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
ORDER BY fnd_date.canonical_to_date (
pay_mx_utility.get_legi_param_val('END_DATE',
ppa_arch.legislative_parameters)) DESC;*/
SELECT pai.action_information_id
FROM pay_action_information pai,
pay_action_interlocks lck_sua,
pay_action_interlocks lck_arch,
pay_assignment_actions paa_arch,
pay_assignment_actions paa_wri
WHERE paa_wri.payroll_action_id =
pay_magtape_generic.get_parameter_value ('PAYROLL_ACTION_ID')
AND lck_sua.locking_action_id = paa_wri.assignment_action_id
AND lck_sua.locked_action_id = lck_arch.locking_action_id
AND lck_arch.locked_action_id = paa_arch.assignment_action_id
AND paa_arch.payroll_action_id = pai.action_context_id
AND pai.action_information_category = 'MX SS GRE INFORMATION'
AND pai.action_context_type = 'PA'
AND ROWNUM = 1;
SELECT hl.address_line_1,
hl.address_line_2,
hl.region_2,
hl.postal_code,
hl.town_or_city,
hr_general.decode_lookup('PER_MX_STATE_CODES', hl.region_1),
ft.territory_short_name,
hl.telephone_number_1,
hl.telephone_number_2
FROM hr_locations hl,
hr_organization_units hou,
fnd_territories_vl ft
WHERE hou.location_id = hl.location_id
AND ft.territory_code = hl.country
AND hou.organization_id = g_gre_id;