1 PACKAGE BODY pay_ca_archive_utils AS
2 /* $Header: pycaroep.pkb 120.6 2011/01/07 07:32:38 nkjaladi ship $ */
3
4 --
5 -- Name : get_archive_value
6 -- Parameters : p_archive_action_id,p_db_name
7 -- Return : VARCHAR2 - the value of db_name
8 -- Description : This function retrieves the archive value for a given
9 -- assignment_action_id and user_name used for the ROE paper report
10 --
11
12
13 FUNCTION get_archive_value(p_archive_action_id number,
14 p_db_name varchar2)
15 RETURN VARCHAR2 IS
16 CURSOR csr_get_value(p_archive_action_id NUMBER,p_db_name varchar2) IS
17 select fai.value
18 from ff_archive_items fai,
19 ff_database_items fdi
20 where fai.context1 = p_archive_action_id
21 and fai.user_entity_id=fdi.user_entity_id
22 and fdi.user_name=p_db_name;
23
24 l_value varchar2(200);
25
26 BEGIN
27
28 OPEN csr_get_value(p_archive_action_id,p_db_name);
29 FETCH csr_get_value INTO l_value;
30
31 IF csr_get_value%NOTFOUND THEN
32 l_value := null;
33 END IF;
34
35 CLOSE csr_get_value;
36 return(l_value);
37
38
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 CLOSE csr_get_value;
42 return (null);
43
44 WHEN OTHERS THEN
45 CLOSE csr_get_value;
46 return (null);
47
48 END get_archive_value;
49
50
51 --
52 -- Name : get_archive_value
53 -- Parameters : p_asg_action_id,p_context,p_context_name,p_db_name
54 -- Return : VARCHAR2 - the value of db_name
55 -- Description : This function retrieves the archive value for a given
56 -- assignment_action_id,user_name,context and context_name
57 -- used for the T4,T4A and RL1 reports
58 --
59
60 FUNCTION get_archive_value(p_asg_action_id number,
61 p_context varchar2,
62 p_context_name varchar2,
63 p_db_name varchar2)
64 RETURN VARCHAR2 IS
65 CURSOR csr_get_value(p_asg_act_id NUMBER,p_context varchar2,p_context_name varchar2,
66 p_db_name varchar2) IS
67 select fai.value
68 from ff_archive_items fai,
69 ff_database_items fdi,
70 ff_archive_item_contexts fac,
71 ff_contexts ffc
72 where fai.context1 = p_asg_act_id
73 and fai.archive_item_id = fac.archive_item_id
74 and fai.user_entity_id = fdi.user_entity_id
75 and fdi.user_name = p_db_name
76 and fac.context = p_context
77 and fac.context_id = ffc.context_id
78 and ffc.context_name = p_context_name;
79
80 l_value varchar2(200);
81
82 BEGIN
83
84 OPEN csr_get_value(p_asg_action_id,p_context,p_context_name,p_db_name);
85 FETCH csr_get_value INTO l_value;
86
87 IF csr_get_value%NOTFOUND THEN
88 l_value := null;
89 END IF;
90 CLOSE csr_get_value;
91
92 return(l_value);
93
94
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 CLOSE csr_get_value;
98 return (null);
99 WHEN OTHERS THEN
100 CLOSE csr_get_value;
101 return (null);
102
103 END get_archive_value;
104
105 /*This funciton get_enable_template_flag is created to serve the purpose of deciding
106 whether the Template Group field of T4 Amendment PDF,T4A Amendment PDF, RL1 Amendment
107 PDF and RL2 Amendment PDF Concurrent Programs based on the reports ran earlier for
108 the Employees*/
109
110 FUNCTION get_enable_template_flag (p_report_type in pay_payroll_actions.report_type%TYPE,
111 p_reporting_year in VARCHAR2,
112 p_business_group_id in pay_payroll_actions.business_group_id%TYPE,
113 p_tax_unit_id in pay_assignment_actions.tax_unit_id%TYPE,
114 p_report_mode in VARCHAR2,
115 p_province in VARCHAR2)
116 RETURN VARCHAR2 IS
117
118
119 l_pdf_output_flag VARCHAR2(10) := NULL;
120 l_gre_or_pre VARCHAR2(20) ;
121 l_archive_type VARCHAR2(20) ;
122 l_main_report_type VARCHAR2(30) ;
123 l_amend_report_type VARCHAR2(30) ;
124 l_substr_st_pos NUMBER :=3;
125
126 CURSOR get_template_flag IS
127 select distinct 'Y'
128 from pay_payroll_actions ppa,
129 pay_assignment_actions paa
130 where ppa.report_type = l_archive_type
131 and to_char(ppa.effective_date,'YYYY') = p_reporting_year
132 and ppa.business_group_id = p_business_group_id
133 and ppa.payroll_action_id = paa.payroll_action_id
134 and to_number(pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters))= nvl(p_tax_unit_id,to_number(pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters)))
135 and ((p_report_mode = 'REPRINT'
136 and exists (select distinct 'Y' from pay_action_interlocks pai,pay_assignment_actions paa2,pay_payroll_actions ppa2
137 where pai.locked_action_id = paa.assignment_action_id
138 and pai.locking_action_id = paa2.assignment_action_id
139 and paa2.payroll_action_id = ppa2.payroll_action_id
140 and ppa2.effective_date = ppa.effective_date /*Added for 10381064*/
141 and paa2.tax_unit_id = nvl(paa.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters))
142 and ppa2.report_type = p_report_type)
143 and not exists (select 1 from pay_assignment_actions paa1,pay_payroll_actions ppa1
144 where ppa1.payroll_action_id = paa1.payroll_action_id
145 and ppa1.effective_date = ppa.effective_date /*Added for 10381064*/
146 and paa1.assignment_id = paa.assignment_id
147 and paa1.assignment_action_id > paa.assignment_action_id
148 and nvl(paa1.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa1.legislative_parameters))
149 = nvl(paa.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters))
150 and ppa1.report_type = l_archive_type))
151 or (p_report_mode = 'LATEST'
152 and not exists (select distinct 'Y' from pay_action_interlocks pai,pay_assignment_actions paa2,pay_payroll_actions ppa2
153 where pai.locked_action_id = paa.assignment_action_id
154 and pai.locking_action_id = paa2.assignment_action_id
155 and paa2.payroll_action_id = ppa2.payroll_action_id
156 and ppa2.effective_date = ppa.effective_date /*Added for 10381064*/
157 and nvl(paa2.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa2.legislative_parameters))
158 = nvl(paa.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters))
159 and ppa2.report_type in (l_archive_type,p_report_type) )
160 and ppa.payroll_action_id = (select max(payroll_action_id) from pay_payroll_actions ppa1 where ppa1.payroll_action_id = ppa.payroll_action_id)));
161 /*Added for Bug#10359114*/
162 CURSOR get_cancel_template_flag IS
163 select distinct 'Y'
164 from pay_payroll_actions ppa,
165 pay_assignment_actions paa
166 where ppa.report_type in (l_main_report_type,l_amend_report_type)
167 and to_char(ppa.effective_date,'YYYY') = p_reporting_year
168 and ppa.business_group_id = p_business_group_id
169 and ppa.payroll_action_id = paa.payroll_action_id
170 and decode(ppa.report_type,l_amend_report_type,instr(ppa.legislative_parameters,'MODE=LATEST'),1) <> 0
171 and paa.tax_unit_id = nvl(p_tax_unit_id,paa.tax_unit_id)
172 and substr(paa.serial_number,1,2) = nvl(p_province,substr(paa.serial_number,1,2))
173 and not exists
174 (select 1
175 from pay_assignment_actions paa1,
176 pay_payroll_actions ppa1
177 where ppa1.report_type in (l_main_report_type,l_amend_report_type)
178 and ppa1.payroll_action_id = paa1.payroll_action_id
179 and ppa1.effective_date = ppa.effective_date
180 and paa1.tax_unit_id = paa.tax_unit_id
181 and paa1.assignment_id = paa.assignment_id
182 and substr(paa1.serial_number,1,2) = substr(paa.serial_number,1,2)
183 and substr(paa1.serial_number,l_substr_st_pos,14)||lpad(paa1.assignment_action_id,14,0) > substr(paa.serial_number,l_substr_st_pos,14)||lpad(paa.assignment_action_id,14,0)
184 and decode(ppa1.report_type,l_amend_report_type,instr(ppa1.legislative_parameters,'MODE=LATEST'),1) <> 0)
185 and ((p_report_mode = 'LATEST'
186 and not exists
187 (select 1
188 from pay_action_interlocks pail,
189 pay_payroll_actions pact,
190 pay_assignment_actions passt
191 where pact.report_type = p_report_type
192 and pact.payroll_action_id = passt.payroll_action_id
193 and pact.effective_date = ppa.effective_date
194 and passt.assignment_action_id = pail.locking_action_id
195 and pail.locked_action_id = paa.assignment_action_id)
196 )
197 or (p_report_mode = 'REPRINT'
198 and exists
199 (select pail.locked_action_id
200 from pay_action_interlocks pail,
201 pay_payroll_actions pact,
202 pay_assignment_actions passt
203 where pact.report_type = p_report_type
204 and pact.payroll_action_id = passt.payroll_action_id
205 and pact.effective_date = ppa.effective_date
206 and passt.assignment_action_id = pail.locking_action_id
207 and pail.locked_action_id = paa.assignment_action_id
208 and passt.tax_unit_id = paa.tax_unit_id))
209 );
210 /*End Bug#10359114*/
211 BEGIN
212
213 l_substr_st_pos :=3;
214
215 IF p_report_type IN ('PAYCAT4AMPDF','PAYCAT4AAMPDF')
216 THEN
217
218 l_gre_or_pre := 'TRANSFER_GRE';
219
220 SELECT decode(p_report_type,'PAYCAT4AMPDF','CAEOY_T4_AMEND_PP','CAEOY_T4A_AMEND_PP')
221 INTO l_archive_type
222 FROM DUAL;
223
224 ELSIF p_report_type IN ('PAYCARL1AMPDF','PAYCARL2AMPDF')
225 THEN
226
227 l_gre_or_pre := 'PRE_ORGANIZATION_ID';
228
229 SELECT decode(p_report_type,'PAYCARL1AMPDF','CAEOY_RL1_AMEND_PP','CAEOY_RL2_AMEND_PP')
230 INTO l_archive_type
231 FROM DUAL;
232
233 END IF;
234 /*Added for Bug#10359114*/
235 IF p_report_type IN ('PAYCAT4CLPDF') THEN
236
237 l_gre_or_pre := 'TRANSFER_GRE';
238
239 l_main_report_type := 'T4_XML';
240 l_amend_report_type := 'PAYCAT4AMPDF';
241
242 END IF;
243
244 IF p_report_type IN ('PAYCAT4ACLPDF') THEN
245
246 l_gre_or_pre := 'TRANSFER_GRE';
247
248 l_main_report_type := 'PAYCAT4APDF';
249 l_amend_report_type := 'PAYCAT4AAMPDF';
250
251 l_substr_st_pos := 1;
252
253 END IF;
254
255
256 IF p_report_type IN ('PAYCARL1CLPDF') THEN
257
258 l_gre_or_pre := 'PRE_ORGANIZATION_ID';
259
260 l_main_report_type := 'RL1PAPERPDF';
261 l_amend_report_type := 'PAYCARL1AMPDF';
262
263 END IF;
264
265 IF p_report_type IN ('PAYCARL2CLPDF') THEN
266
267 l_gre_or_pre := 'PRE_ORGANIZATION_ID';
268
269 l_main_report_type := 'RL2PAPERPDF';
270 l_amend_report_type := 'PAYCARL2AMPDF';
271
272 END IF;
273 /*End Bug#10359114*/
274 IF p_report_type IN ('PAYCAT4AMPDF','PAYCAT4AAMPDF','PAYCARL1AMPDF','PAYCARL2AMPDF') THEN
275
276 OPEN get_template_flag;
277 FETCH get_template_flag INTO l_pdf_output_flag;
278
279 IF get_template_flag%NOTFOUND
280 THEN
281 l_pdf_output_flag := NULL;
282 END IF;
283
284 CLOSE get_template_flag;
285
286 END IF;
287
288 IF p_report_type IN ('PAYCAT4CLPDF','PAYCAT4ACLPDF','PAYCARL1CLPDF','PAYCARL2CLPDF') THEN
289
290 OPEN get_cancel_template_flag;
291 FETCH get_cancel_template_flag INTO l_pdf_output_flag;
292
293 IF get_cancel_template_flag%NOTFOUND
294 THEN
295 l_pdf_output_flag := NULL;
296 END IF;
297
298 CLOSE get_cancel_template_flag;
299
300 END IF;
301
302
303 RETURN l_pdf_output_flag;
304
305 END get_enable_template_flag;
306
307 /*Added for Bug#10359114*/
308
309 FUNCTION get_enable_template_flag (p_report_type in pay_payroll_actions.report_type%TYPE,
310 p_reporting_year in VARCHAR2,
311 p_business_group_id in pay_payroll_actions.business_group_id%TYPE,
312 p_tax_unit_id in pay_assignment_actions.tax_unit_id%TYPE,
313 p_report_mode in VARCHAR2)
314 RETURN VARCHAR2 IS
315
316 BEGIN
317
318 return get_enable_template_flag (p_report_type,
319 p_reporting_year,
320 p_business_group_id,
321 p_tax_unit_id,
322 p_report_mode,
323 NULL);
324
325 END get_enable_template_flag;
326
327
328 END pay_ca_archive_utils;
[Home] [Help]