[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
inserted.
sdahiya 13-Apr-2007 115.3 07 transactions should not be
reported if IDW amount hasn't
changed since it was reported
last.
sdahiya 16-Apr-2007 115.4 5996000 PL/SQL table should not be read
after dynamic truncation.
sdahiya 19-Apr-2007 115.5 5998981 Modified cursor csr_prev_idw to
conditionally convert canonical
date stamped on action information
DDF.
sdahiya 20-Apr-2007 115.6 6002963 Affiliation report should suppress
07 transactions which are archived
with rehire.
Procedure process_transactions
created to identify and eliminate
redundant transactions.
sdahiya 22-Apr-2007 115.7 Modified process_transactions to
eliminate multiple 08 and 02
transactions. The earliest 08
and latest 02 will be reported.
Added parameters to this procedure
so that it may be called from
SUA.
sdahiya 26-Apr-2007 115.8 6008833 Modified range_cursor so that it
does not discard persons when
the report is run after persons'
GRE transfer.
sdahiya 15-May-2007 115.9 Modified action_creation and
generate_xml so that past-dated
transactions are picked.
sdahiya 16-May-2007 115.10 Version uprev after establishing
dual maintenance.
sdahiya 18-May-2007 115.11 6060070 Changed multiple SQL statements
to conditionally convert
canonical date stamped on DDF into
date.
sdahiya 22-May-2007 115.12 6065124 Modified get_emp_details.
sivanara 27-jun-2008 115.13 7185703 Added logic to filter the transaction
before implementation date
sivanara 16-jul-2008 115.14 7258802 In the procedure process_transaction
modified cursor csr_prev_idw by adding
trunc on fnd_date.canonical_to_date to
consider the first run of the GRE.
***************************************************************************/
--
-- Global Variables
--
TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
SELECT fnd_date.date_to_canonical (effective_date + 1/(24 * 60 * 60))
FROM pay_payroll_actions
WHERE report_type = 'SS_AFFILIATION'
AND report_qualifier IN ('HIRES', 'SEPARATIONS', 'SALARY')
AND pay_mx_utility.get_legi_param_val('TRANS_GRE',
legislative_parameters,
' ') = TO_CHAR(p_trans_gre)
ORDER BY payroll_action_id DESC;
SELECT fnd_date.date_to_canonical(
fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
legislative_parameters)) +
1/(24 * 60 * 60))
FROM pay_payroll_actions
WHERE report_type = 'MX_SS_AFFL'
AND report_qualifier = 'MX_SS_AFFL'
AND pay_mx_utility.get_legi_param_val('TRANS_GRE',
legislative_parameters) =
TO_CHAR(p_trans_gre)
ORDER BY payroll_action_id DESC;
SELECT org_information6
FROM hr_organization_information
WHERE org_information_context = 'MX_TAX_REGISTRATION'
AND organization_id = cp_organization_id;
SELECT hou.business_group_id
FROM hr_organization_units hou
WHERE hou.organization_id = p_trans_gre;
SELECT pay_mx_utility.get_legi_param_val('TRANS_GRE',
ppa.legislative_parameters),
pay_mx_utility.get_legi_param_val('START_DATE',
ppa.legislative_parameters),
pay_mx_utility.get_legi_param_val('END_DATE',
ppa.legislative_parameters),
business_group_id
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT organization_id
FROM hr_organization_information
WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
AND org_information3 = 'N'
AND org_information6 = g_trans_gre_id;
g_gre_tab.DELETE();
'SELECT DISTINCT person_id
FROM per_assignments_f
WHERE business_group_id = '||g_business_group||'
/*AND fnd_date.canonical_to_date('''||g_end_date
||''') BETWEEN effective_start_date AND effective_end_date*/
AND per_mx_ss_affiliation.derive_gre_from_loc_scl (location_id,
business_group_id,
soft_coding_keyflex_id,
fnd_date.canonical_to_date('''||
g_end_date||''')) IN
(SELECT organization_id
FROM hr_organization_information
WHERE org_information_context = ''MX_SOC_SEC_DETAILS''
AND (org_information3 = ''N''
AND org_information6 = '|| g_trans_gre_id ||'
OR organization_id = '||g_trans_gre_id||'))
AND :p_payroll_action_id > 0';
SELECT DISTINCT paf.person_id,
paf.assignment_id,
pai.action_context_id,
paf.primary_flag,
pai.tax_unit_id
FROM per_assignments_f paf,
pay_action_information pai
WHERE pai.action_information_category = 'MX SS TRANSACTIONS'
AND paf.business_group_id = g_business_group
AND paf.person_id BETWEEN p_start_person_id AND p_end_person_id
AND paf.person_id = pai.action_information1
AND pai.action_information4 IN ('02', '07', '08')
AND NVL(pai.action_information10, 'N') <> 'Y'
-- Bug 6060070
AND (DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
fnd_date.canonical_to_date (pai.action_information2),
hr_general.start_of_time) BETWEEN
fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
OR (pai.effective_date BETWEEN
fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
/*NOT EXISTS (SELECT 'X'
FROM pay_payroll_actions ppa_affl,
pay_assignment_actions paa_affl,
pay_action_interlocks lck
WHERE lck.locked_action_id = pai.action_context_id
AND lck.locking_action_id = paa_affl.assignment_action_id
AND paa_affl.payroll_action_id = ppa_affl.payroll_action_id
AND ppa_affl.report_type = 'MX_SS_AFFL'
AND ppa_affl.report_qualifier = 'MX_SS_AFFL'
AND ppa_affl.report_category = 'RT'
AND ppa_affl.action_status = 'C')*/
-- Bug 6060070
AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
fnd_date.canonical_to_date (pai.action_information2),
hr_general.start_of_time) <=
fnd_date.canonical_to_date (g_end_date)
)
)
/*AND fnd_date.canonical_to_date (g_end_date) BETWEEN
paf.effective_start_date
AND paf.effective_end_date*/
ORDER BY paf.person_id,
pai.action_context_id,
decode (paf.primary_flag, 'Y', 1, 2),
paf.assignment_id;
SELECT DISTINCT paf.person_id,
paf.assignment_id,
pai.action_context_id,
paf.primary_flag,
pai.tax_unit_id
FROM per_assignments_f paf,
pay_action_information pai,
pay_population_ranges ppr
WHERE pai.action_information_category = 'MX SS TRANSACTIONS'
AND paf.business_group_id = g_business_group
AND ppr.payroll_action_id = p_payroll_action_id
AND ppr.chunk_number = p_chunk
AND paf.person_id = ppr.person_id
AND paf.person_id = pai.action_information1
AND pai.action_information4 IN ('02', '07', '08')
AND NVL(pai.action_information10, 'N') <> 'Y'
-- Bug 6060070
AND (DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
fnd_date.canonical_to_date (pai.action_information2),
hr_general.start_of_time) BETWEEN
fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
OR (pai.effective_date BETWEEN
fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
/*NOT EXISTS (SELECT 'X'
FROM pay_payroll_actions ppa_affl,
pay_assignment_actions paa_affl,
pay_action_interlocks lck
WHERE lck.locked_action_id = pai.action_context_id
AND lck.locking_action_id = paa_affl.assignment_action_id
AND paa_affl.payroll_action_id = ppa_affl.payroll_action_id
AND ppa_affl.report_type = 'MX_SS_AFFL'
AND ppa_affl.report_qualifier = 'MX_SS_AFFL'
AND ppa_affl.report_category = 'RT'
AND ppa_affl.action_status = 'C')*/
-- Bug 6060070
AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
fnd_date.canonical_to_date (pai.action_information2),
hr_general.start_of_time) <=
fnd_date.canonical_to_date (g_end_date)
)
)
/*AND fnd_date.canonical_to_date (g_end_date) BETWEEN
paf.effective_start_date
AND paf.effective_end_date*/
ORDER BY paf.person_id,
pai.action_context_id,
decode (paf.primary_flag, 'Y', 1, 2),
paf.assignment_id;
SELECT pay_assignment_actions_s.nextval
INTO ln_affl_asg_act
FROM dual;
SELECT fnd_number.canonical_to_number(pai.action_information8)
FROM pay_payroll_actions ppa_mag,
pay_assignment_actions paa_mag,
--pay_assignment_actions paa_arch,
pay_action_interlocks lck,
pay_action_information pai
WHERE ppa_mag.payroll_action_id = paa_mag.payroll_action_id
AND paa_mag.assignment_action_id = lck.locking_action_id
/*AND lck.locked_action_id = paa_arch.assignment_action_id
AND paa_arch.assignment_action_id = pai.action_context_id*/
AND lck.locked_action_id = pai.action_context_id
AND pai.action_information_category = 'MX SS TRANSACTIONS'
AND pai.action_information1 = p_person_id
AND pai.action_information4 IN ('07','08')
AND ppa_mag.action_type = 'X'
AND ppa_mag.report_type = p_report_type
AND ppa_mag.report_qualifier = p_report_qualifier
AND ppa_mag.report_category = p_report_category
AND ppa_mag.action_status = 'C'
AND p_gre_id = fnd_number.canonical_to_number(
pay_mx_utility.get_legi_param_val(cp_gre_token,
ppa_mag.legislative_parameters))
AND p_end_date > fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa_mag.legislative_parameters))
-- Bug 5998981
AND DECODE (pai.action_information_category,
'MX SS TRANSACTIONS',
TRUNC(fnd_date.canonical_to_date(pai.action_information2)),
hr_general.start_of_time)
BETWEEN TRUNC(fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('START_DATE',
ppa_mag.legislative_parameters)))
AND TRUNC (fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa_mag.legislative_parameters)))
ORDER BY fnd_date.canonical_to_date (pai.action_information2) DESC;
p_transactions.DELETE(ln_cntr);
p_transactions.DELETE(ln_cntr);
p_transactions.DELETE(ln_cntr);
p_transactions.DELETE(ln_cntr);
p_transactions.DELETE(ln_hire_idx);
p_transactions.DELETE(ln_hire_idx);
SELECT pai.action_information_id,
pai.action_information1, -- Person ID
pai.action_information7, -- Employee name
pai.action_information8, -- Worker Type
pai.action_information9, -- RWW Indicator
pai.action_information10, -- Hire Date
-- Bug 5919339
fnd_number.canonical_to_number(pai.action_information11), -- IDW
pai.action_information18 -- Salary Type
-- Bug 6065124
FROM pay_action_information pai /*,
pay_assignment_actions paa_arch,
pay_action_interlocks lck*/
WHERE pai.action_information_category = 'MX SS PERSON INFORMATION'
/*and lck.locking_action_id = cp_assignment_action_id
AND lck.locked_action_id = pai.action_context_id*/
AND pai.action_context_type = 'AAP'
AND pai.action_information1 = cp_person_id
AND nvl(pai.action_information21, 'N') <> 'Y' -- Do not report flag
AND pai.effective_date <= fnd_date.canonical_to_date (g_end_date)
ORDER BY pai.effective_date DESC/*,
decode (paf.primary_flag, 'Y', 1, 2),
paf.assignment_id*/;
/*SELECT fnd_number.canonical_to_number(
pay_magtape_generic.get_parameter_value ('TRANSFER_ACT_ID')),
fnd_date.canonical_to_date (g_end_date)
FROM dual
UNION
SELECT paa.assignment_action_id,
fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa.legislative_parameters))
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_interlocks lck
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = lck.locking_action_id
AND pai.action_context_id = lck.locked_action_id
AND pai.action_information_category = 'MX SS PERSON INFORMATION'
AND pai.action_information1 = cp_person_id
AND ppa.report_type = 'MX_SS_AFFL'
AND ppa.report_qualifier = 'MX_SS_AFFL'
AND ppa.report_category = 'RT'
AND ppa.action_status = 'C'
AND pay_mx_utility.get_legi_param_val('TRANS_GRE',
ppa.legislative_parameters) =
g_trans_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)
ORDER BY 2 DESC;*/
SELECT pai.action_context_id,
pai.effective_date
FROM pay_action_information pai
WHERE pai.action_information_category = 'MX SS PERSON INFORMATION'
AND pai.action_information1 = cp_person_id
-- Bug 6060070
AND pai.effective_date <= fnd_date.canonical_to_date (g_end_date)
/*AND (DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
fnd_date.canonical_to_date (pai.action_information2),
hr_general.start_of_time) BETWEEN
fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
OR (pai.effective_date BETWEEN
fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
-- Bug 6060070
AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
fnd_date.canonical_to_date (pai.action_information2),
hr_general.start_of_time) <=
fnd_date.canonical_to_date (g_end_date)
)
)
/*AND fnd_date.canonical_to_date (g_end_date) BETWEEN
paf.effective_start_date
AND paf.effective_end_date*/
ORDER BY pai.effective_date DESC;
SELECT paf.person_id
FROM per_assignments_f paf,
pay_assignment_actions paa
WHERE paa.assignment_action_id = cp_assignment_action_id
AND paa.assignment_id = paf.assignment_id;
SELECT pai.action_information_id,
pai.action_information1, -- Person ID
pai.action_information2, -- Date of Transaction
pai.action_information3, -- Employee SSN
pai.action_information4, -- Type of Transaction
pai.action_information5, -- Employer SS ID
fnd_number.canonical_to_number (pai.action_information8), -- IDW
pai.action_information9 -- Leaving reason
FROM pay_action_information pai,
pay_action_interlocks lck
WHERE lck.locking_action_id = cp_assignment_action_id
AND lck.locked_action_id = pai.action_context_id
AND pai.action_information_category = 'MX SS TRANSACTIONS'
AND pai.action_information4 IN ('02', '07', '08')
AND NVL(pai.action_information10, 'N') <> 'Y'
--Bug 7185703
AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
fnd_date.canonical_to_date (pai.action_information2),hr_general.start_of_time
) >= fnd_date.canonical_to_date(cp_imp_date)
-- Bug 6060070
AND (DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
fnd_date.canonical_to_date (pai.action_information2),
hr_general.start_of_time) BETWEEN
fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
OR (pai.effective_date BETWEEN
fnd_date.canonical_to_date (g_start_date)
AND fnd_date.canonical_to_date (g_end_date)
/*NOT EXISTS (SELECT 'X'
FROM pay_payroll_actions ppa_affl,
pay_assignment_actions paa_affl,
pay_action_interlocks lck
WHERE lck.locked_action_id = pai.action_context_id
AND lck.locking_action_id = paa_affl.assignment_action_id
AND paa_affl.payroll_action_id = ppa_affl.payroll_action_id
AND ppa_affl.report_type = 'MX_SS_AFFL'
AND ppa_affl.report_qualifier = 'MX_SS_AFFL'
AND ppa_affl.report_category = 'RT'
AND ppa_affl.action_status = 'C')*/
-- Bug 6060070
AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
fnd_date.canonical_to_date (pai.action_information2),
hr_general.start_of_time) <=
fnd_date.canonical_to_date (g_end_date)
)
)
ORDER BY fnd_date.canonical_to_date (pai.action_information2),
DECODE (pai.action_information4,
'08', 1,
'07', 2,
'02', 3);
SELECT org_information6
FROM hr_organization_information
WHERE org_information_context = 'MX_TAX_REGISTRATION'
AND organization_id = cp_gre_id;
SELECT fnd_date.date_to_canonical(MIN(creation_date))
INTO ld_event_strt_date
FROM pay_process_events ppe
WHERE EXISTS
(SELECT 1
FROM pay_event_updates peu
WHERE table_name IN ('PER_ALL_PEOPLE_F','PER_ALL_ASSIGNMENTS_F','PAY_ELEMENT_ENTRIES_F','PAY_ELEMENT_ENTRY_VALUES_F')
AND ppe.event_update_id = peu.event_update_id
);
SELECT pai.action_information_id
FROM pay_action_information pai,
pay_action_interlocks lck,
pay_assignment_actions paa_affl,
pay_assignment_actions paa_arch
WHERE paa_affl.payroll_action_id =
pay_magtape_generic.get_parameter_value ('PAYROLL_ACTION_ID')
AND lck.locking_action_id = paa_affl.assignment_action_id
AND paa_arch.assignment_action_id = lck.locked_action_id
AND pai.action_context_id = paa_arch.payroll_action_id
--AND pai.action_information2 = pai.action_information4
AND pai.action_information_category = 'MX SS GRE INFORMATION'
AND pai.action_context_type = 'PA'
ORDER BY pai.action_information_id DESC;