1 PACKAGE PAY_CA_RL2_AMEND_MAG AUTHID CURRENT_USER as
2 /* $Header: pycarl2amd.pkh 120.2.12020000.1 2012/06/28 16:59:09 appldev ship $ */
3 /*
4 Name
5 pay_ca_rl2_amend_mag
6
7 Purpose
8 The purpose of this package is to support the generation of
9 amended magnetic tape RL2.
10
11 History
12 16-JAN-2007 ssmukher 115.1 Date created.
13 16-Mar-2007 ssmukher 115.3 5934191 Modified the
14 employee cursor.
15 14-Jul-2009 aneghosh 115.4 8316787 Removed function convert_special_char.
16 08-Oct-2009 aneghosh 115.5 8932598 Modified CURSOR mag_amend_rl2_employee
17 to prevent duplicate employee records.
18 ============================================================================*/
19
20
21 -- 'level_cnt' will allow the cursors to select function results,
22 -- whether it is a standard fuction such as to_char or a function
23 -- defined in a package (with the correct pragma restriction).
24
25 level_cnt NUMBER;
26
27
28 CURSOR mag_amend_rl2_transmitter IS
29 SELECT 'BUSINESS_GROUP_ID=C',ppa.business_group_id,
30 'PAYROLL_ACTION_ID=P',MAX(ppa.payroll_action_id)
31 FROM hr_organization_information hoi,
32 pay_payroll_actions ppa
33 WHERE to_char(hoi.organization_id) = pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
34 AND hoi.org_information_context='Prov Reporting Est'
35 AND ppa.report_type = 'CAEOY_RL2_AMEND_PP' -- RL2 Amendment Archiver Report Type
36 AND to_char(hoi.organization_id) = pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
37 AND to_char(ppa.effective_date,'YYYY')=pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
38 AND to_char(ppa.effective_date,'DD-MM')= '31-12'
39 GROUP BY
40 'BUSINESS_GROUP_ID=C',ppa.business_group_id,
41 'PAYROLL_ACTION_ID=P';
42
43 --
44 -- Used by Amended Magnetic RL2 (RL2 format).
45 --
46 -- Sets up the tax unit context for each employer to be reported. sets
47 -- up a parameter holding the tax unit identifier which can then be used by
48 -- subsequent cursors to restrict to employees within the employer.
49 --
50 --
51 CURSOR mag_amend_rl2_employer IS
52 SELECT DISTINCT
53 'BUSINESS_GROUP_ID=C',ppa.business_group_id,
54 'PAYROLL_ACTION_ID=C',MAX(ppa.payroll_action_id),
55 'PAYROLL_ACTION_ID=P',MAX(ppa.payroll_action_id)
56 FROM pay_payroll_actions ppa,
57 hr_organization_information hoi
58 WHERE decode(hoi.org_information3,'Y',to_char(hoi.organization_id) ,hoi.org_information20) =
59 pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
60 AND hoi.org_information_context='Prov Reporting Est'
61 AND to_char(hoi.organization_id) = pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
62 AND ppa.action_status = 'C'
63 AND to_char(ppa.effective_date,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
64 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
65 AND ppa.report_type = 'CAEOY_RL2_AMEND_PP'
66 GROUP BY
67 'BUSINESS_GROUP_ID=C',ppa.business_group_id,
68 'PAYROLL_ACTION_ID=C',
69 'PAYROLL_ACTION_ID=P';
70
71 --
72 -- Used by Amended Magnetic RL2 (RL2 format).
73 --
74 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
75 -- for an employee. The date_earned context is set to be the least of the
76 -- end of the period being reported and the maximum end date of the
77 -- assignment. This ensures that personal information ie. name etc... is
78 -- current relative to the period being reported on.
79 --
80
81 CURSOR mag_amend_rl2_employee IS
82 SELECT
83 'TRANSFER_ACT_ID=P',paa.assignment_action_id
84 FROM pay_action_information pin,
85 per_all_people_f ppf,
86 per_all_assignments_f paf,
87 pay_action_interlocks pai,
88 pay_assignment_actions paa,
89 pay_payroll_actions ppa,
90 pay_assignment_actions paa_arch,
91 pay_payroll_actions ppa_arch
92 WHERE ppa.payroll_action_id =
93 to_number( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'))
94 AND paa.payroll_action_id = ppa.payroll_action_id
95 AND pai.locking_action_id = paa.assignment_action_id
96 AND paf.assignment_id = paa.assignment_id
97 AND ppf.person_id = paf.person_id
98 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id) between
99 paf.effective_start_date and paf.effective_end_date
100 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id) between
101 ppf.effective_start_date and ppf.effective_end_date
102 AND pin.action_context_id = pai.locked_action_id
103 AND pin.action_context_type = 'AAP'
104 AND pin.action_information_category = 'CAEOY RL2 EMPLOYEE INFO'
105 AND paa_arch.assignment_action_id = pai.locked_action_id
106 AND paa_arch.payroll_action_id=ppa_arch.payroll_action_id
107 AND ppa_arch.report_type = 'CAEOY_RL2_AMEND_PP'
108 AND to_char(pin.effective_date,'YYYY') = to_char(ppa.effective_date,'YYYY')
109 ORDER BY ppf.last_name,ppf.first_name,ppf.middle_names;
110
111 PROCEDURE get_report_parameters
112 (
113 p_pactid IN NUMBER,
114 p_year_start IN OUT NOCOPY DATE,
115 p_year_end IN OUT NOCOPY DATE,
116 p_report_type IN OUT NOCOPY VARCHAR2,
117 p_business_group_id IN OUT NOCOPY NUMBER,
118 p_legislative_param IN OUT NOCOPY VARCHAR2
119 );
120
121
122 PROCEDURE range_cursor (
123 p_pactid IN NUMBER,
124 p_sqlstr OUT NOCOPY VARCHAR2
125 );
126
127
128 PROCEDURE create_assignment_act(
129 p_pactid IN NUMBER,
130 p_stperson IN NUMBER,
131 p_endperson IN NUMBER,
132 p_chunk IN NUMBER );
133
134
135 PROCEDURE end_of_file;
136
137 CURSOR rl2_amend_asg_actions
138 IS
139 SELECT 'TRANSFER_ACT_ID=P',
140 pay_magtape_generic.get_parameter_value(
141 'TRANSFER_ACT_ID')
142 FROM DUAL;
143
144 PROCEDURE xml_employee_record;
145
146 PROCEDURE xml_employer_start;
147
148 PROCEDURE xml_employer_record;
149
150 PROCEDURE xml_transmitter_record;
151
152 END pay_ca_rl2_amend_mag;
153
[Home] [Help]