[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
to avoid insertion of duplicate
action interlocks due to multiple
archiver runs.
sdahiya 02-Mar-2007 115.25 Modified the process to pick only
those transactions which are
effective in the reporting period.
sdahiya 08-Apr-2007 115.26 07 transactions should not be
reported if IDW amount hasn't
changed since it was reported
last.
sdahiya 19-Apr-2007 115.27 5998981 Modified cursor csr_prev_idw to
conditionally convert canonical
date stamped on action information
DDF.
sdahiya 19-Apr-2007 115.28 6004485 Modified action_creation to ensure
that exactly one interlock is
inserted for every archiver asg
action.
sdahiya 23-Apr-2007 115.29 Modified generate_xml to use
transaction processing from
affiliation report.
sdahiya 15-May-2007 115.30 Modified action_creation and
generate_xml so that past-dated
transactions are picked.
nragavar 31-May-2007 115.31 6073090 Person information selection was
not done in cursors c_arch_asg,
c_arch_asg_range
nragavar 12-Jul-2007 115.32 6198089 added new procedure INIT
sivanara 27-Jun-2008 115.33 7185703 added logic to filter the
transaction after implementation
date.
swamukhe 04-Oct-2008 115.35 6451017 Modified the c_arch_asg_range so
truncated the dates in the cursor.
vvijayku 07-Oct-2008 115.36 6451017 Modified the cursor get_emp_trans
so that it will take fetch the value
of the reporting option.
sjawid 10-Mar-2009 115.37 8280047 Modified Person information selection
in cursors c_arch_asg, c_arch_asg_range
so that it selects valid person transaction.
vvijayku 19-Nov-2009 115.38 8768679 Added code to report the 08 transactions one
day before the date of transaction.
vvijayku 20-Nov-2009 115.39 8768679 Added comments about the changes made for the
fix.
vvijayku 20-Nov-2009 115.40 8768679 Modified the comments added earlier.
vvijayku 03-Sep-2011 115.41 8438074 Added code to Report the excluded transactions
in the Workers Transaction Exceptions report
sjawid 04-Oct-2012 115.42 14637921 Changed indexing of 'num_tab' sequence in the
Procedure 'ACTION_CREATION' from BINARY_INTEGER to
VARCHAR2(32767)
========================================================================*/
--
-- Global Variables
--
TYPE char_tab IS TABLE OF pay_action_information.action_information1%type
INDEX BY BINARY_INTEGER;
SELECT pay_mx_utility.get_legi_param_val('GRE',
ppa_mag.legislative_parameters),
pay_mx_utility.get_legi_param_val('START_DATE',
ppa_mag.legislative_parameters),
pay_mx_utility.get_legi_param_val('END_DATE',
ppa_mag.legislative_parameters),
pay_mx_utility.get_legi_param_val('MODE',
ppa_mag.legislative_parameters)
FROM pay_payroll_actions ppa_mag
WHERE ppa_mag.payroll_action_id = p_payroll_action_id;
SELECT hou.business_group_id
FROM hr_organization_units hou
WHERE hou.organization_id = p_gre_id;
SELECT org_information6
FROM hr_organization_information
WHERE org_information_context = 'MX_TAX_REGISTRATION'
AND organization_id = cp_organization_id;
SELECT fnd_date.date_to_canonical(
fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val ('END_DATE',
ppa.legislative_parameters)) +
1/(24 * 60 * 60))
FROM pay_payroll_actions ppa
WHERE pay_mx_utility.get_legi_param_val('GRE',
ppa.legislative_parameters) =
p_gre_id
AND ppa.report_type = 'SUA_MAG'
AND ppa.report_qualifier = 'SUA_MAG'
AND ppa.report_category = 'RT'
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
ORDER BY ppa.payroll_action_id DESC;
SELECT DISTINCT substr(paa_arch.serial_number, 1,
instr(paa_arch.serial_number, ''|'')-1)
FROM pay_assignment_actions paa_arch,
pay_payroll_actions ppa_arch
WHERE paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.tax_unit_id = '|| g_mag_gre_id ||'/*
AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val(''END_DATE'',
ppa_arch.legislative_parameters))
BETWEEN fnd_date.canonical_to_date ('''|| g_mag_start_date ||''')
AND fnd_date.canonical_to_date ('''|| l_new_end_date ||''')
*/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''
AND :p_payroll_action_id = '||p_payroll_action_id||'
ORDER BY 1';
/*Bug 8768679 - Added decode statements in the cursor so that only for 08 type ,future dated transactions are also selected*/
CURSOR c_arch_asg (p_end_date VARCHAR2) IS
SELECT paa_arch.assignment_action_id,
paf.assignment_id,
paf.person_id,
ppa_arch.payroll_action_id
FROM pay_assignment_actions paa_arch,
pay_payroll_actions ppa_arch,
per_all_assignments_f paf,
pay_action_information pai
WHERE paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.assignment_id = paf.assignment_id
-- Bug 4518777
AND paf.person_id BETWEEN p_start_person_id AND p_end_person_id
AND paa_arch.tax_unit_id = g_mag_gre_id
/*AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val(
'END_DATE',
ppa_arch.legislative_parameters))
BETWEEN fnd_date.canonical_to_date(g_mag_start_date)
AND fnd_date.canonical_to_date(g_mag_end_date)*/
----
AND paa_arch.assignment_action_id = pai.action_context_id
AND (( pai.action_information_category = 'MX SS TRANSACTIONS'
AND (fnd_date.canonical_to_date (pai.action_information2) BETWEEN
fnd_date.canonical_to_date (g_mag_start_date)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_mag_end_date))
OR (pai.effective_date BETWEEN
fnd_date.canonical_to_date (g_mag_start_date)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_mag_end_date))
/*Bug 8768679*/
AND fnd_date.canonical_to_date (pai.action_information2) <=
fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_mag_end_date))))
)
OR (pai.action_information_category = 'MX SS PERSON INFORMATION'
AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val(
'END_DATE',
ppa_arch.legislative_parameters))
BETWEEN fnd_date.canonical_to_date(g_mag_start_date)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_mag_end_date))
)
)
AND paa_arch.tax_unit_id = g_mag_gre_id
/*AND NOT EXISTS (SELECT 'X'
FROM pay_payroll_actions ppa_sua,
pay_assignment_actions paa_sua,
pay_action_interlocks lck
WHERE lck.locked_action_id = pai.action_context_id
AND lck.locking_action_id =
paa_sua.assignment_action_id
AND paa_sua.payroll_action_id =
ppa_sua.payroll_action_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 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 paf.person_id,
decode (paf.primary_flag, 'Y', 1, 2),
paf.assignment_id,
ppa_arch.payroll_action_id,
paf.effective_end_date;
/*Bug 8768679 - Added decode statements in the cursor so that only for 08 type ,future dated transactions are also selected*/
CURSOR c_arch_asg_range (p_end_date VARCHAR2) IS
SELECT paa_arch.assignment_action_id,
paf.assignment_id,
paf.person_id,
ppa_arch.payroll_action_id
FROM pay_assignment_actions paa_arch,
pay_payroll_actions ppa_arch,
per_all_assignments_f paf,
pay_population_ranges ppr,
pay_action_information pai
WHERE paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.assignment_id = paf.assignment_id
AND paf.person_id = ppr.person_id
AND ppr.chunk_number = p_chunk
AND ppr.payroll_action_id = p_payroll_action_id
AND paa_arch.tax_unit_id = g_mag_gre_id
/*AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val(
'END_DATE',
ppa_arch.legislative_parameters))
BETWEEN fnd_date.canonical_to_date(g_mag_start_date)
AND fnd_date.canonical_to_date(g_mag_end_date)*/
----
AND paa_arch.assignment_action_id = pai.action_context_id
AND ((pai.action_information_category = 'MX SS TRANSACTIONS'
AND (fnd_date.canonical_to_date (pai.action_information2) BETWEEN
fnd_date.canonical_to_date (g_mag_start_date)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_mag_end_date))
OR (pai.effective_date BETWEEN
trunc(fnd_date.canonical_to_date (g_mag_start_date))
/*Bug 8768679*/
AND trunc(fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_mag_end_date)))
/*Bug 8768679*/
AND fnd_date.canonical_to_date (pai.action_information2) <=
fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_mag_end_date))))
)
OR (pai.action_information_category = 'MX SS PERSON INFORMATION'
AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val(
'END_DATE',
ppa_arch.legislative_parameters))
BETWEEN fnd_date.canonical_to_date(g_mag_start_date)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',p_end_date,g_mag_end_date))
)
)
AND paa_arch.tax_unit_id = g_mag_gre_id
/*AND NOT EXISTS (SELECT 'X'
FROM pay_payroll_actions ppa_sua,
pay_assignment_actions paa_sua,
pay_action_interlocks lck
WHERE lck.locked_action_id = pai.action_context_id
AND lck.locking_action_id =
paa_sua.assignment_action_id
AND paa_sua.payroll_action_id =
ppa_sua.payroll_action_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 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 paf.person_id,
decode (paf.primary_flag, 'Y', 1, 2),
paf.assignment_id,
ppa_arch.payroll_action_id,
paf.effective_end_date;
SELECT 'Y'
FROM pay_payroll_actions ppa
WHERE ppa.report_type = 'SUA_MAG'
AND ppa.report_qualifier = 'SUA_MAG'
AND ppa.report_category = 'RT'
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND pay_mx_utility.get_legi_param_val('GRE',
ppa.legislative_parameters) =
g_mag_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_mag_end_date);
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 = 'SUA_MAG';
SELECT pay_assignment_actions_s.nextval
INTO ln_mag_asg_act_id
FROM dual;
lt_arch_act.DELETE(); -- Bug 6004485
SELECT paa_arch.payroll_action_id,
pai.action_context_id,
pai.action_information_id,
nvl(pai.action_information21, 'N') -- Do not report flag
FROM pay_action_information pai,
pay_assignment_actions paa_arch,
per_all_assignments_f paf,
pay_action_interlocks lck
WHERE pai.action_context_id = paa_arch.assignment_action_id
AND paf.assignment_id = paa_arch.assignment_id
/*Bug 8768679*/
AND fnd_date.canonical_to_date(pai.action_information10)
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paa_arch.assignment_action_id = lck.locked_action_id
AND lck.locking_action_id = cp_assignment_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'MX SS PERSON INFORMATION'
ORDER BY paa_arch.payroll_action_id DESC,
decode (paf.primary_flag, 'Y', 1, 2),
paf.assignment_id;
SELECT pai.action_information_id,
pai.action_information4, -- transaction type
pai.assignment_id,
pai.action_information1, -- person ID
pai.tax_unit_id,
pai.effective_date,
nvl(pai.action_information10, 'N'), -- Do not report flag
fnd_number.canonical_to_number(pai.action_information8), --IDW
pai.action_information2, -- transaction date
pai.action_information24 -- Report Rehire Termination
FROM pay_action_information pai,
pay_assignment_actions paa_arch,
per_all_assignments_f paf,
pay_action_interlocks lck
WHERE pai.action_context_id = paa_arch.assignment_action_id
AND paf.assignment_id = paa_arch.assignment_id
--Bug 7185703
AND fnd_date.canonical_to_date (pai.action_information2) >= fnd_date.canonical_to_date(cp_imp_date)
AND (fnd_date.canonical_to_date (pai.action_information2) BETWEEN
fnd_date.canonical_to_date (g_mag_start_date)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',cp_end_date,g_mag_end_date))
OR (pai.effective_date BETWEEN
fnd_date.canonical_to_date (g_mag_start_date)
/*Bug 8768679*/
AND fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',cp_end_date,g_mag_end_date))
/*Bug 8768679*/
AND fnd_date.canonical_to_date (pai.action_information2) <=
fnd_date.canonical_to_date (decode(pai.action_information4 ,'08',cp_end_date,g_mag_end_date))))
AND fnd_date.canonical_to_date(pai.action_information2)
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paa_arch.assignment_action_id = lck.locked_action_id
AND lck.locking_action_id = cp_assignment_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'MX SS TRANSACTIONS'
ORDER BY fnd_date.canonical_to_date (pai.action_information2), -- tran dt
DECODE (pai.action_information4,
'08', 1,
'07', 2,
'02', 3),
pai.action_information_id,
paa_arch.payroll_action_id,
paf.person_id,
decode (paf.primary_flag, 'Y', 1, 2),
paf.assignment_id;
SELECT fnd_date.canonical_to_date(pai.action_information2) -- tran date
FROM pay_assignment_actions paa_mag,
pay_payroll_actions ppa_mag,
pay_assignment_actions paa_mag_prev,
pay_payroll_actions ppa_mag_prev,
pay_action_information pai,
pay_action_interlocks lck
WHERE paa_mag.payroll_action_id = ppa_mag.payroll_action_id
AND paa_mag_prev.payroll_action_id = ppa_mag_prev.payroll_action_id
AND pay_mx_utility.get_legi_param_val ('GRE',
ppa_mag.payroll_action_id) =
pay_mx_utility.get_legi_param_val ('GRE',
ppa_mag_prev.payroll_action_id)
AND ppa_mag_prev.payroll_action_id < ppa_mag.payroll_action_id
AND paa_mag_prev.assignment_action_id = lck.locking_action_id
AND lck.locked_action_id = pai.action_context_id
AND paa_mag.assignment_action_id = cp_mag_asact_id
AND pai.action_information1 = cp_person_id -- person ID
AND pai.action_information4 = cp_tran_type -- transaction type
AND nvl(pai.action_information10, 'N') <> 'Y' -- do not report flag
AND ((fnd_date.canonical_to_date (pai.action_information2) >
cp_tran_dt) OR
(cp_tran_dt IS NULL))
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'MX SS TRANSACTIONS'
AND ppa_mag_prev.action_type = 'X'
AND ppa_mag_prev.report_type = 'SUA_MAG'
AND ppa_mag_prev.report_qualifier = 'SUA_MAG'
AND ppa_mag_prev.report_category = 'RT'
AND ppa_mag_prev.action_status = 'C';
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 fnd_number.canonical_to_number(
pay_magtape_generic.get_parameter_value ('TRANSFER_ACT_ID')),
fnd_date.canonical_to_date(g_mag_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,
per_assignments_f paf
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = paf.assignment_id
AND paf.person_id = cp_person_id
AND fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa.legislative_parameters))
BETWEEN paf.effective_start_date AND paf.effective_end_date
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 cp_gre_id = fnd_number.canonical_to_number(
pay_mx_utility.get_legi_param_val('GRE',
ppa.legislative_parameters))
AND cp_curr_date > fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa.legislative_parameters))*/
AND fnd_number.canonical_to_number(g_mag_gre_id) =
fnd_number.canonical_to_number(
pay_mx_utility.get_legi_param_val('GRE',
ppa.legislative_parameters))
AND fnd_date.canonical_to_date(g_mag_end_date) >
fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa.legislative_parameters))
ORDER BY 2 DESC;
SELECT fnd_number.canonical_to_number(pai.action_information8)
FROM pay_payroll_actions ppa_sua,
pay_assignment_actions paa_sua,
pay_assignment_actions paa_arch,
pay_action_interlocks lck,
pay_action_information pai
WHERE ppa_sua.payroll_action_id = paa_sua.payroll_action_id
AND paa_sua.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 pai.action_information_category = 'MX SS TRANSACTIONS'
AND pai.action_information1 = cp_person_id
AND pai.action_information4 = '07'
AND ppa_sua.action_type = 'X'
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 fnd_number.canonical_to_number(g_mag_gre_id) =
fnd_number.canonical_to_number(
pay_mx_utility.get_legi_param_val('GRE',
ppa_sua.legislative_parameters))
AND fnd_date.canonical_to_date(g_mag_end_date) >
fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa_sua.legislative_parameters))
-- Bug 5998981
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(
pay_mx_utility.get_legi_param_val('START_DATE',
ppa_sua.legislative_parameters))
AND fnd_date.canonical_to_date(
pay_mx_utility.get_legi_param_val('END_DATE',
ppa_sua.legislative_parameters))
ORDER BY fnd_date.canonical_to_date (pai.action_information2) DESC;
SELECT org_information6
FROM hr_organization_information
WHERE org_information_context = 'MX_TAX_REGISTRATION'
AND organization_id = cp_gre_id;
SELECT hou.business_group_id
FROM hr_organization_units hou
WHERE hou.organization_id = g_mag_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 DISTINCT paa_arch.payroll_action_id
FROM pay_assignment_actions paa_arch,
pay_assignment_actions paa_mag,
pay_action_interlocks lck
WHERE paa_arch.assignment_action_id = lck.locked_action_id
AND lck.locking_action_id = paa_mag.assignment_action_id
AND paa_mag.chunk_number >= cp_chunk
AND paa_mag.payroll_action_id =
pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
SELECT pai.action_information_id
FROM pay_action_information pai
WHERE pai.action_context_id = cp_action_context_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'MX SS GRE INFORMATION';