[Home] [Help]
PACKAGE BODY: APPS.PAY_CORE_XDO_UTILS
Source
1 PACKAGE BODY pay_core_xdo_utils as
2 /* $Header: paycorexdoutil.pkb 120.0.12020000.2 2013/01/03 09:44:46 pracagra ship $ */
3
4
5 Procedure ARCHIVE_DEINIT(p_payroll_action_id IN NUMBER) IS
6
7 -- Get request_id
8 CURSOR get_request_id (c_pact_id NUMBER)IS
9 SELECT request_id
10 FROM pay_payroll_actions
11 WHERE payroll_action_id = c_pact_id;
12
13 -- Get Application Short Name and Application ID
14 CURSOR get_application_detais(c_request_id NUMBER) IS
15 SELECT app.application_short_name, fcp.application_id
16 FROM fnd_application_vl app,
17 fnd_concurrent_programs fcp,
18 fnd_concurrent_requests r
19 WHERE fcp.concurrent_program_id = r.concurrent_program_id
20 AND r.request_id = c_request_id
21 and app.application_id = fcp.application_id;
22
23
24 -- Get template type
25 CURSOR get_template_name(c_templ_code xdo_templates_tl.template_code%TYPE) IS
26 SELECT template_name --template_type_code
27 FROM xdo_templates_vl
28 WHERE template_code = c_templ_code;
29
30 CURSOR c_get_templates(l_payroll_action_id NUMBER) IS
31 SELECT prv.value,
32 prd.report_type,
33 prd.report_level,
34 prd.report_definition_id,
35 nvl(prd.application_short_name,'PAY'),
36 prv.definition_type,
37 prv.report_variable_id
38 FROM pay_report_groups prg,
39 pay_payroll_actions ppa,
40 pay_report_definitions prd,
41 pay_report_categories prc,
42 pay_report_category_components prcc,
43 pay_report_variables prv
44 WHERE prg.short_name =
45 nvl(pay_core_utils.get_parameter('REP_GROUP',
46 ppa.legislative_parameters),
47 -1)
48 AND ppa.payroll_action_id = l_payroll_action_id
49 AND prg.report_group_id = prd.report_group_id
50 AND prc.short_name =
51 nvl(pay_core_utils.get_parameter('REP_CAT',
52 ppa.legislative_parameters),
53 -1)
54 AND prc.report_category_id = prcc.report_category_id
55 AND prcc.report_definition_id = prd.report_definition_id
56 AND prv.report_variable_id = prcc.style_sheet_variable_id
57 ORDER BY prd.report_definition_id;
58
59 CURSOR c_get_action_parameter(p_param_name VARCHAR2) IS
60 SELECT parameter_value
61 FROM pay_action_parameters
62 where parameter_name = p_param_name;
63
64
65 ln_req_id NUMBER;
66 ln_current_request_id NUMBER;
67 ln_application_id NUMBER;
68 ln_count NUMBER;
69 lv_proc_name VARCHAR2(100);
70 lv_template_type xdo_templates_b.template_type_code%TYPE;
71 lv_template_code xdo_templates_tl.template_code%TYPE;
72 lv_app_short_name fnd_application_vl.application_short_name%TYPE;
73 lv_template_name xdo_templates_vl.template_name%TYPE;
74
75 lv_report_definition_id NUMBER;
76 lv_definition_type VARCHAR2(20);
77 lv_report_variable_id NUMBER;
78 lv_report_level VARCHAR2(5);
79 p_xdo_run VARCHAR2(5);
80 p_print_files VARCHAR2(5);
81 p_logging VARCHAR2(80); --Added for bug 13794925
82 set_print_option BOOLEAN;
83 set_notification BOOLEAN;
84 copies_buffer VARCHAR2(80) := NULL;
85 print_buffer VARCHAR2(80) := NULL;
86 printer_buffer VARCHAR2(80) := NULL;
87 style_buffer VARCHAR2(80) := NULL;
88 save_buffer BOOLEAN := NULL;
89 save_result VARCHAR2(1) := NULL;
90 debug_flag VARCHAR2(1) := 'N'; --Added for bug 13794925
91
92
93 PROCEDURE set_print_options(req_id NUMBER) IS
94 BEGIN
95 hr_utility.trace ('Entering set_print_options');
96
97 select number_of_copies,
98 printer,
99 print_style,
100 save_output_flag
101 into copies_buffer,
102 printer_buffer,
103 style_buffer,
104 save_result
105 from fnd_concurrent_requests
106 where request_id = fnd_number.canonical_to_number(req_id);
107
108 hr_utility.trace ('number_of_copies '||copies_buffer);
109 hr_utility.trace ('printer '||printer_buffer);
110 hr_utility.trace ('print_style '||style_buffer);
111 hr_utility.trace ('save_output_flag '||save_result);
112
113 if (save_result='Y') then
114 save_buffer:=true;
115 elsif (save_result='N') then
116 save_buffer:=false;
117 else
118 save_buffer:=NULL;
119 end if;
120
121 set_print_option :=FND_REQUEST.set_print_options(
122 printer => printer_buffer,
123 style => style_buffer,
124 copies => copies_buffer,
125 save_output => save_buffer,
126 print_together => print_buffer);
127
128 -- Bug 3487186 Added by ssmukher
129 set_notification := fnd_Request.USE_CURRENT_NOTIFICATION;
130
131 hr_utility.trace ('Leaving set_print_options');
132
133 END;
134
135 BEGIN
136
137 lv_proc_name := 'PAY_AC_UTILITY.ARCHIVE_DEINIT';
138 hr_utility.trace ('Entering '|| lv_proc_name);
139 hr_utility.trace ('p_payroll_action_id '|| p_payroll_action_id);
140
141 OPEN c_get_action_parameter('PRINT_FILES');
142 FETCH c_get_action_parameter
143 INTO p_print_files;
144 CLOSE c_get_action_parameter;
145
146 OPEN c_get_action_parameter('RUN_XDO');
147 FETCH c_get_action_parameter
148 INTO p_xdo_run;
149 CLOSE c_get_action_parameter;
150
151 /*Added for bug 13794925*/
152 OPEN c_get_action_parameter('LOGGING');
153 FETCH c_get_action_parameter
154 INTO p_logging;
155 CLOSE c_get_action_parameter;
156
157 hr_utility.trace ('p_xdo_run '|| p_xdo_run);
158 hr_utility.trace ('p_print_files '|| p_print_files);
159 hr_utility.trace ('p_logging '|| p_logging);
160
161 /*Added for bug 13794925*/
162 IF (nvl(instr(p_logging,'G'),0) > 0) THEN
163 debug_flag:='Y';
164 END IF;
165
166 IF (p_xdo_run = 'N') and (p_print_files = 'N') THEN
167
168
169 OPEN get_request_id(p_payroll_action_id);
170 FETCH get_request_id INTO ln_current_request_id;
171 CLOSE get_request_id;
172
173 OPEN get_application_detais(ln_current_request_id);
174 FETCH get_application_detais INTO lv_app_short_name
175 ,ln_application_id;
176 CLOSE get_application_detais;
177
178 ln_count := 1;
179
180 OPEN c_get_templates(p_payroll_action_id);
181 LOOP
182 FETCH c_get_templates
183 INTO lv_template_code, lv_template_type, lv_report_level,
184 lv_report_definition_id, lv_app_short_name,
185 lv_definition_type, lv_report_variable_id;
186
187 EXIT WHEN c_get_templates%NOTFOUND;
188
189 OPEN get_template_name(lv_template_code);
190 FETCH get_template_name INTO lv_template_name;
191 CLOSE get_template_name;
192
193 -- pay_archive.remove_report_actions(p_payroll_action_id);
194
195 hr_utility.trace ('ln_current_request_id '|| ln_current_request_id);
196 hr_utility.trace ('lv_template_code '|| lv_template_code);
197 hr_utility.trace ('ln_application_id '|| ln_application_id);
198 hr_utility.trace ('lv_template_type '|| lv_template_type);
199
200 set_print_options(ln_current_request_id);
201
202 ln_req_id := fnd_request.submit_request
203 ( application => 'PAY',
204 program => 'PAYGENXDOREPORT',
205 argument1 => 'Y',
206 argument2 => ln_current_request_id,
207 argument3 => lv_template_code,
208 argument4 => lv_app_short_name,
209 argument5 => debug_flag, --'Y', Modified for bug 13794925
210 argument6 => lv_template_type,
211 argument7 => 'Y', --- calledFromDeinit
212 argument8 => lv_report_level, -- report_level
213 argument9 => 'STANDARD' -- report_level
214 );
215
216 hr_utility.trace ('Submitted PAYGENXDOREP,ln_req_id '|| ln_req_id);
217
218 request_list(ln_count) := ln_req_id;
219 ln_count := ln_count + 1;
220
221
222 END LOOP;
223
224 END IF; /* print_files & xdo_run*/
225 hr_utility.trace ('Leaving '|| lv_proc_name);
226 end ARCHIVE_DEINIT;
227
228 /* Procedure to remove data from pay_file_details*/
229 PROCEDURE del_file_details(pactid IN NUMBER) IS
230
231 Type file_detid_list is Table of pay_file_details.file_detail_id%type;
232 fdetlst file_detid_list;
233
234 Cursor csr_get_file_detl(payroll_act_id in number) is
235 select file_detail_id
236 from pay_file_details
237 where source_id = payroll_act_id
238 or source_id in (select assignment_action_id
239 from pay_assignment_actions paa
240 where payroll_action_id = payroll_act_id);
241 BEGIN
242 --
243 Open csr_get_file_detl(pactid);
244 Loop
245 Fetch csr_get_file_detl bulk collect into fdetlst limit 1000;
246 forall i in 1..fdetlst.count
247 delete from pay_file_details
248 where file_detail_id = fdetlst(i);
249
250 exit when csr_get_file_detl%notfound;
251 End loop;
252 Close csr_get_file_detl;
253 --
254 END del_file_details;
255
256 /* This procedure deletes created actions after the submitted requests gets completed */
257 PROCEDURE standard_deinit(pactid IN NUMBER) IS
258 remove_act VARCHAR2(10);
259 l_flag BOOLEAN := TRUE;
260 l_valid_request BOOLEAN := FALSE;
261 l_phase VARCHAR2(80);
262 l_status VARCHAR2(80);
263 l_dev_phase VARCHAR2(80);
264 l_dev_status VARCHAR2(80);
265 l_message VARCHAR2(80);
266 l_proc_name VARCHAR2(100);
267 l_xdo_run VARCHAR2(5);
268 l_print_files VARCHAR2(5);
269
270 CURSOR c_get_action_parameter(p_param_name VARCHAR2) IS
271 SELECT parameter_value
272 FROM pay_action_parameters
273 WHERE parameter_name = p_param_name;
274
275 BEGIN
276 --
277 l_proc_name := 'PAY_CORE_XDO_UTILS.STANDARD_DEINIT';
278 hr_utility.trace ('Entering '|| l_proc_name);
279 request_list.delete;
280
281 archive_deinit(pactid);
282 commit;
283
284 OPEN c_get_action_parameter('PRINT_FILES');
285 FETCH c_get_action_parameter
286 INTO l_print_files;
287 CLOSE c_get_action_parameter;
288
289 OPEN c_get_action_parameter('RUN_XDO');
290 FETCH c_get_action_parameter
291 INTO l_xdo_run;
292 CLOSE c_get_action_parameter;
293
294 hr_utility.trace ('l_xdo_run '|| l_xdo_run);
295 hr_utility.trace ('l_print_files '|| l_print_files);
296
297 IF (l_xdo_run = 'N') and (l_print_files = 'N') THEN
298 loop
299 exit when not l_flag;
300 for i in request_list.first..request_list.last loop
301 hr_utility.trace ('request_list(i)'||request_list(i));
302 l_valid_request := fnd_concurrent.get_request_status(
303 request_id => request_list(i),
304 appl_shortname => 'PAY',
305 program => 'PAYGENXDOREPORT',
306 phase => l_phase,
307 status => l_status,
308 dev_phase => l_dev_phase,
309 dev_status => l_dev_status,
310 message => l_message);
311
312 hr_utility.trace ('l_dev_phase '|| l_dev_phase);
313 if (l_valid_request and l_dev_phase not in ('PENDING', 'RUNNING')) then
314 hr_utility.trace ('In l_flag := FALSE');
315 l_flag := FALSE;
316 else
317 l_flag := TRUE;
318 hr_utility.trace ('In l_flag := TRUE');
319 exit;
320 end if;
321
322 dbms_lock.sleep(20);
323 hr_utility.trace ('Waiting for request completion...');
324
325 end loop;
326 end loop;
327 hr_utility.trace ('All Requests Completed...');
328
329 END IF; -- run_xdo = N and print_files = N
330 --
331 select pay_core_utils.get_parameter('REMOVE_ACT',
332 pa1.legislative_parameters)
333 into remove_act
334 from pay_payroll_actions pa1
335 where pa1.payroll_action_id = pactid;
336 --
337 if (remove_act is null or remove_act = 'Y') then
338 del_file_details(pactid);
339 hr_utility.trace ('pay_file_details deleted');
340 pay_archive.remove_report_actions(pactid);
341 hr_utility.trace ('Removed Payroll and assignment actions');
342 end if;
343
344 hr_utility.trace ('Leaving '|| l_proc_name);
345
346 END standard_deinit;
347
348
349 END pay_core_xdo_utils;