[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.
swamukhe 04-Oct-2008 115.18 6451017 Commented a set of code to so that the
rehire and termination.
vvijayku 07-Nov-2008 115.19 6451017 Modified the cursor get_emp_trans to get the
value of the option yes/no archived.Also
added logic in PROCESS_TRANSACTION to filter
out the 02 and 08 transactions depending on the
reporting option.
vvijayku 10-Nov-2008 115.20 6451017 Added a filteration condition in process_transctions
to filter out the extra 07 transactions.
vvijayku 15-Nov-2008 115.21 7568378 Added more code in PROCESS_TRANSACTIONS to remove the
regression it created in the normal termination reporting.
vvijayku 19-Nov-2009 115.22 8768679 Added code to report the 08 transactions one day before the
date of transaction.
vvijayku 20-Nov-2009 115.23 8768679 Added comments about the changes made for the fix.
vvijayku 20-Nov-2009 115.24 8768679 Modified the comments added earlier.
vvijayku 13-Apr-2011 115.25 11906020 Added fnd_number.number_to_canonical in the cursors
which are comparing the action_information1 to cp_person_id
jeisaac 28-Aug-2012 115.26 14325500 Modified procedure process_transactions to filter transactions
for SUA when seniority changes but IDW does not change
***************************************************************************/
--
-- 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('''||l_new_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('''||
l_new_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
ORDER BY person_id';
/*Bug 8768679 - Added decode statements in the cursor so that only for 08 type ,future dated transactions are also selected*/
CURSOR c_affl_person (p_end_date VARCHAR2) IS
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)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_end_date))
OR (pai.effective_date BETWEEN
fnd_date.canonical_to_date (g_start_date)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_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
/*Bug 8768679*/
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 (decode(pai.action_information4 ,'08',p_end_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;
/*Bug 8768679 - Added decode statements in the cursor so that only for 08 type ,future dated transactions are also selected*/
CURSOR c_affl_person_range (p_end_date VARCHAR2) IS
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)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_end_date))
OR (pai.effective_date BETWEEN
trunc(fnd_date.canonical_to_date (g_start_date))
/*Bug 8768679*/
AND trunc(fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_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
/*Bug 8768679*/
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 (decode(pai.action_information4 ,'08',p_end_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 count(*)
FROM pay_action_interlocks
WHERE locked_action_id = p_arch_asg_act;
SELECT count(*)
FROM pay_payroll_actions pact,
pay_assignment_actions paa,
pay_action_interlocks pail
WHERE p_arch_asg_act = pail.locked_action_id
AND pail.locking_action_id = paa.assignment_action_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.report_type = 'MX_SS_AFFL';
SELECT pay_assignment_actions_s.nextval
INTO ln_affl_asg_act
FROM dual;
SELECT nvl(fnd_number.canonical_to_number(pai.action_information8),-1)
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 = fnd_number.number_to_canonical(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
/*Bug 8768679 - A decode statement has been added so that previous idw value can be obtained
when the transaction date of the immediate earlier 08 transaction lies between START_DATE and
END_DATE+1*/
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 DECODE (pai.action_information4,'08',
(TRUNC (fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa_mag.legislative_parameters)))+1),
(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_cntr);
p_transactions.DELETE(ln_cntr);
p_transactions.DELETE(ln_cntr);
p_transactions.DELETE(ln_term_idx);
p_transactions.DELETE(ln_cntr);
p_transactions.DELETE(ln_term_idx);
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 = fnd_number.number_to_canonical(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 = fnd_number.number_to_canonical(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 = fnd_number.number_to_canonical(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
pai.action_information24 -- Reporting option (YES/NO)
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)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',cp_end_date,g_end_date))
OR (pai.effective_date BETWEEN
fnd_date.canonical_to_date (g_start_date)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',cp_end_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
/*Bug 8768679*/
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 (decode(pai.action_information4 ,'08',cp_end_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;