1 PACKAGE pay_ca_t4_mag AS
2 /* $Header: pycat4mg.pkh 120.2.12010000.2 2008/08/08 09:26:14 sapalani ship $ */
3
4 /* 'level_cnt' will allow the cursors to select function results,
5 whether it is a standard fuction such as to_char or a function
6 defined in a package (with the correct pragma restriction).
7 */
8
9 level_cnt NUMBER;
10
11 /* Used by T4 Magnetic Media
12
13 Sets up the tax unit context and payroll action context for the
14 transmitter_GRE
15 */
16
17
18 CURSOR mag_t4_transmitter IS
19 Select 'TAX_UNIT_ID=C', hoi.organization_id,
20 'PAYROLL_ACTION_ID=C',ppa.payroll_action_id,
21 'TRANSFER_CPP_MAX=P', pcli.information_value,
22 'TRANSFER_EI_MAX=P', pcli1.information_value,
23 'TRANSFER_PPIP_MAX=P', pcli2.information_value,
24 'SUBMISSION_REF_ID=P',ppa.payroll_action_id,
25 'ORG_ID=P',hoi.organization_id,
26 'T4_YEAR=P',to_char(ppa.effective_date,'YYYY')
27 FROM hr_organization_information hoi,
28 pay_payroll_actions PPA,
29 pay_ca_legislation_info pcli,
30 pay_ca_legislation_info pcli1,
31 pay_ca_legislation_info pcli2
32 WHERE hoi.organization_id = pay_magtape_generic.get_parameter_value('TRANSMITTER_GRE')
33 and hoi.org_information_context='Fed Magnetic Reporting'
34 and ppa.report_type = 'T4' -- T4 Archiver Report Type
35 and hoi.organization_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='))
36 and to_char(ppa.effective_date,'YYYY')=pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
37 and to_char(ppa.effective_date,'DD-MM')= '31-12'
38 and pcli.information_type = 'MAX_CPP_EARNINGS'
39 and ppa.effective_date between pcli.start_date and pcli.end_date
40 and pcli.jurisdiction_code is null
41 and pcli1.information_type = 'MAX_EI_EARNINGS'
42 and pcli1.jurisdiction_code is null
43 and ppa.effective_date between pcli1.start_date and pcli1.end_date
44 and pcli2.information_type = 'MAX_PPIP_EARNINGS'
45 and pcli2.jurisdiction_code is null
46 and ppa.effective_date between pcli2.start_date and pcli2.end_date;
47
48
49
50
51 /* Used by Magnetic T4 (T4 format).
52
53 Sets up the tax unit context for each employer to be reported. sets
54 up a parameter holding the tax unit identifier which can then be used by
55 subsequent cursors to restrict to employees within the employer.
56 */
57
58
59 CURSOR mag_t4_employer IS
60 Select distinct 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
61 'TAX_UNIT_ID=C', AA.tax_unit_id,
62 'TAX_UNIT_ID=P', AA.tax_unit_id,
63 'TAX_UNIT_NAME=P', fai.value,
64 'TRANSFER_EI_ER_RATE=P', pcli2.information_value
65 From ff_archive_items fai,
66 ff_database_items fdi,
67 pay_payroll_actions ppa,
68 pay_assignment_actions AA,
69 pay_ca_legislation_info pcli2
70 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID') --Magtape Payroll Action id
71 and ppa.report_type = 'T4'
72 and to_char(ppa.effective_date,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
73 and to_char(ppa.effective_date,'DD-MM') = '31-12'
74 and AA.tax_unit_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='))
75 and fdi.user_name = 'CAEOY_EMPLOYER_NAME'
76 and ppa.payroll_action_id = fai.context1
77 and fdi.user_entity_id = fai.user_entity_id
78 and pcli2.information_type = 'EI_ER_RATE'
79 and pcli2.jurisdiction_code is null
80 and ppa.effective_date between pcli2.start_date and pcli2.end_date
81 order by fai.value;
82
83
84 /* Used by Magnetic T4 (T4 format).
85
86 Sets up the assignment_action_id, assignment_id, and date_earned contexts
87 for an employee. The date_earned context is set to be the least of the
88 end of the period being reported and the maximum end date of the
89 assignment. This ensures that personal information ie. name etc... is
90 current relative to the period being reported on.
91 */
92
93 CURSOR mag_t4_employee IS
94 Select 'ASSIGNMENT_ACTION_ID=C',paa.assignment_action_id, -- Archiver Assignment_Action_id
95 'ASSIGNMENT_ID=C',paa.assignment_id,
96 'DATE_EARNED=C',fnd_date.date_to_canonical(pay_magtape_generic.date_earned(ppa_mag.effective_date,paa.assignment_id)),
97 'JURISDICTION_CODE=C', fai.value,
98 'TRANSFER_JURISDICTION_CODE=P', fai.value
99 From ff_archive_items fai,
100 ff_database_items fdi,
101 per_all_people_f ppf,
102 pay_assignment_actions paa,
103 pay_payroll_actions ppa,
104 pay_payroll_actions ppa_mag
105 where ppa_mag.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
106 and ppa.report_type = 'T4'
107 and ppa.report_qualifier = 'CAEOY'
108 and ppa.report_category = 'CAEOY'
109 and ppa.effective_date = ppa_mag.effective_date
110 and paa.payroll_action_id = ppa.payroll_action_id
111 and paa.action_status = 'C'
112 AND paa.tax_unit_id = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
113 AND fai.context1 = paa.assignment_action_id
114 AND fdi.user_entity_id = fai.user_entity_id
115 AND fdi.user_name = 'CAEOY_PROVINCE_OF_EMPLOYMENT'
116 AND ppf.person_id = to_number(paa.serial_number)
117 AND pay_magtape_generic.date_earned(ppa_mag.effective_date,paa.assignment_id)
118 between ppf.effective_start_date and ppf.effective_end_date
119 ORDER BY ppf.last_name,ppf.first_name,ppf.middle_names;
120
121
122 PROCEDURE get_report_parameters
123 (
124 p_pactid IN NUMBER,
125 p_year_start IN OUT NOCOPY DATE,
126 p_year_end IN OUT NOCOPY DATE,
127 p_report_type IN OUT NOCOPY VARCHAR2,
128 p_business_group_id IN OUT NOCOPY NUMBER,
129 p_legislative_parameters OUT NOCOPY VARCHAR2
130 );
131
132
133 PROCEDURE range_cursor (
134 p_pactid IN NUMBER,
135 p_sqlstr OUT NOCOPY VARCHAR2
136 );
137
138
139 PROCEDURE create_assignment_act(
140 p_pactid IN NUMBER,
141 p_stperson IN NUMBER,
142 p_endperson IN NUMBER,
143 p_chunk IN NUMBER );
144
145 FUNCTION get_parameter(name in varchar2,
146 parameter_list varchar2) return varchar2;
147 pragma restrict_references(get_parameter, WNDS, WNPS);
148
149 FUNCTION get_dbitem_value(p_asg_act_id in number,
150 p_dbitem_name in varchar2,
151 p_jurisdiction varchar2 default null) return varchar2;
152
153 FUNCTION convert_2_xml(p_data IN VARCHAR2,
154 p_tag IN VARCHAR2,
155 p_datatype IN CHAR DEFAULT 'T',
156 p_format IN VARCHAR2 DEFAULT NULL,
157 p_null_allowed IN VARCHAR2 DEFAULT 'N')
158 return VARCHAR2;
159
160
161 FUNCTION validate_gre_data( p_trans in varchar2,
162 p_year in varchar2)
163 return varchar2;
164
165 FUNCTION get_arch_val( p_context_id IN NUMBER,
166 p_user_name IN VARCHAR2)
167 return varchar2;
168
169 FUNCTION convert_t4_oth_info_amt(p_assignment_action_id IN Number,
170 p_payroll_action_id IN Number,
171 p_fail IN char,
172 p_oth_rep1 OUT nocopy varchar2,
173 p_oth_rep2 OUT nocopy varchar2,
174 p_oth_rep3 OUT nocopy varchar2,
175 p_write_f31 OUT nocopy varchar2,
176 p_transfer_other_info1_str1 OUT nocopy varchar2,
177 p_transfer_other_info1_str2 OUT nocopy varchar2,
178 p_transfer_other_info1_str3 OUT nocopy varchar2,
179 p_transfer_other_info2_str1 OUT nocopy varchar2,
180 p_transfer_other_info2_str2 OUT nocopy varchar2,
181 p_transfer_other_info2_str3 OUT nocopy varchar2,
182 p_transfer_other_info3_str1 OUT nocopy varchar2,
183 p_transfer_other_info3_str2 OUT nocopy varchar2,
184 p_transfer_other_info3_str3 OUT nocopy varchar2,
185 p_transfer_other_info4_str1 OUT nocopy varchar2,
186 p_transfer_other_info4_str2 OUT nocopy varchar2,
187 p_transfer_other_info4_str3 OUT nocopy varchar2,
188 p_transfer_oth1_rep1 OUT nocopy varchar2,
189 p_transfer_oth1_rep2 OUT nocopy varchar2,
190 p_transfer_oth1_rep3 OUT nocopy varchar2,
191 p_transfer_oth2_rep2 OUT nocopy varchar2,
192 p_transfer_oth2_rep3 OUT nocopy varchar2,
193 p_transfer_oth3_rep2 OUT nocopy varchar2,
194 p_transfer_oth3_rep3 OUT nocopy varchar2,
195 p_transfer_oth4_rep3 OUT nocopy varchar2,
196 p_cnt OUT nocopy Number)
197 return varchar2;
198
199 END pay_ca_t4_mag;
[Home] [Help]