[Home] [Help]
PACKAGE BODY: APPS.PAY_CORE_XDO_UTILS
Source
1 PACKAGE BODY pay_core_xdo_utils as
2 /* $Header: paycorexdoutil.pkb 120.0.12000000.1 2007/03/21 13:52:03 sausingh noship $ */
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 set_print_option BOOLEAN;
82 set_notification BOOLEAN;
83 copies_buffer VARCHAR2(80) := NULL;
84 print_buffer VARCHAR2(80) := NULL;
85 printer_buffer VARCHAR2(80) := NULL;
86 style_buffer VARCHAR2(80) := NULL;
87 save_buffer BOOLEAN := NULL;
88 save_result VARCHAR2(1) := NULL;
89
90
91 PROCEDURE set_print_options(req_id NUMBER) IS
92 BEGIN
93 hr_utility.trace ('Entering set_print_options');
94
95 select number_of_copies,
96 printer,
97 print_style,
98 save_output_flag
99 into copies_buffer,
100 printer_buffer,
101 style_buffer,
102 save_result
103 from fnd_concurrent_requests
104 where request_id = fnd_number.canonical_to_number(req_id);
105
106 hr_utility.trace ('number_of_copies '||copies_buffer);
107 hr_utility.trace ('printer '||printer_buffer);
108 hr_utility.trace ('print_style '||style_buffer);
109 hr_utility.trace ('save_output_flag '||save_result);
110
111 if (save_result='Y') then
112 save_buffer:=true;
113 elsif (save_result='N') then
114 save_buffer:=false;
115 else
116 save_buffer:=NULL;
117 end if;
118
119 set_print_option :=FND_REQUEST.set_print_options(
120 printer => printer_buffer,
121 style => style_buffer,
122 copies => copies_buffer,
123 save_output => save_buffer,
124 print_together => print_buffer);
125
126 -- Bug 3487186 Added by ssmukher
127 set_notification := fnd_Request.USE_CURRENT_NOTIFICATION;
128
129 hr_utility.trace ('Leaving set_print_options');
130
131 END;
132
133 BEGIN
134
135 lv_proc_name := 'PAY_AC_UTILITY.ARCHIVE_DEINIT';
136 hr_utility.trace ('Entering '|| lv_proc_name);
137 hr_utility.trace ('p_payroll_action_id '|| p_payroll_action_id);
138
139 OPEN c_get_action_parameter('PRINT_FILES');
140 FETCH c_get_action_parameter
141 INTO p_print_files;
142 CLOSE c_get_action_parameter;
143
144 OPEN c_get_action_parameter('RUN_XDO');
145 FETCH c_get_action_parameter
146 INTO p_xdo_run;
147 CLOSE c_get_action_parameter;
148
149 hr_utility.trace ('p_xdo_run '|| p_xdo_run);
150 hr_utility.trace ('p_print_files '|| p_print_files);
151
152 IF (p_xdo_run = 'N') and (p_print_files = 'N') THEN
153
154
155 OPEN get_request_id(p_payroll_action_id);
156 FETCH get_request_id INTO ln_current_request_id;
157 CLOSE get_request_id;
158
159 OPEN get_application_detais(ln_current_request_id);
160 FETCH get_application_detais INTO lv_app_short_name
161 ,ln_application_id;
162 CLOSE get_application_detais;
163
164 ln_count := 1;
165
166 OPEN c_get_templates(p_payroll_action_id);
167 LOOP
168 FETCH c_get_templates
169 INTO lv_template_code, lv_template_type, lv_report_level,
170 lv_report_definition_id, lv_app_short_name,
171 lv_definition_type, lv_report_variable_id;
172
173 EXIT WHEN c_get_templates%NOTFOUND;
174
175 OPEN get_template_name(lv_template_code);
176 FETCH get_template_name INTO lv_template_name;
177 CLOSE get_template_name;
178
179 -- pay_archive.remove_report_actions(p_payroll_action_id);
180
181 hr_utility.trace ('ln_current_request_id '|| ln_current_request_id);
182 hr_utility.trace ('lv_template_code '|| lv_template_code);
183 hr_utility.trace ('ln_application_id '|| ln_application_id);
184 hr_utility.trace ('lv_template_type '|| lv_template_type);
185
186 set_print_options(ln_current_request_id);
187
188 ln_req_id := fnd_request.submit_request
189 ( application => 'PAY',
190 program => 'PAYGENXDOREPORT',
191 argument1 => 'Y',
192 argument2 => ln_current_request_id,
193 argument3 => lv_template_code,
194 argument4 => lv_app_short_name,
195 argument5 => 'Y',
196 argument6 => lv_template_type,
197 argument7 => 'Y', --- calledFromDeinit
198 argument8 => lv_report_level, -- report_level
199 argument9 => 'STANDARD' -- report_level
200 );
201
202 hr_utility.trace ('Submitted PAYGENXDOREP,ln_req_id '|| ln_req_id);
203
204 request_list(ln_count) := ln_req_id;
205 ln_count := ln_count + 1;
206
207
208 END LOOP;
209
210 END IF; /* print_files & xdo_run*/
211 hr_utility.trace ('Leaving '|| lv_proc_name);
212 end ARCHIVE_DEINIT;
213
214 /* Procedure to remove data from pay_file_details*/
215 PROCEDURE del_file_details(pactid IN NUMBER) IS
216
217 Type file_detid_list is Table of pay_file_details.file_detail_id%type;
218 fdetlst file_detid_list;
219
220 Cursor csr_get_file_detl(payroll_act_id in number) is
221 select file_detail_id
222 from pay_file_details
223 where source_id = payroll_act_id
224 or source_id in (select assignment_action_id
225 from pay_assignment_actions paa
226 where payroll_action_id = payroll_act_id);
227 BEGIN
228 --
229 Open csr_get_file_detl(pactid);
230 Loop
231 Fetch csr_get_file_detl bulk collect into fdetlst limit 1000;
232 forall i in 1..fdetlst.count
233 delete from pay_file_details
234 where file_detail_id = fdetlst(i);
235
236 exit when csr_get_file_detl%notfound;
237 End loop;
238 Close csr_get_file_detl;
239 --
240 END del_file_details;
241
242 /* This procedure deletes created actions after the submitted requests gets completed */
243 PROCEDURE standard_deinit(pactid IN NUMBER) IS
244 remove_act VARCHAR2(10);
245 l_flag BOOLEAN := TRUE;
246 l_valid_request BOOLEAN := FALSE;
247 l_phase VARCHAR2(80);
248 l_status VARCHAR2(80);
249 l_dev_phase VARCHAR2(80);
250 l_dev_status VARCHAR2(80);
251 l_message VARCHAR2(80);
252 l_proc_name VARCHAR2(100);
253 l_xdo_run VARCHAR2(5);
254 l_print_files VARCHAR2(5);
255
256 CURSOR c_get_action_parameter(p_param_name VARCHAR2) IS
257 SELECT parameter_value
258 FROM pay_action_parameters
259 WHERE parameter_name = p_param_name;
260
261 BEGIN
262 --
263 l_proc_name := 'PAY_CORE_XDO_UTILS.STANDARD_DEINIT';
264 hr_utility.trace ('Entering '|| l_proc_name);
265 request_list.delete;
266
267 archive_deinit(pactid);
268 commit;
269
270 OPEN c_get_action_parameter('PRINT_FILES');
271 FETCH c_get_action_parameter
272 INTO l_print_files;
273 CLOSE c_get_action_parameter;
274
275 OPEN c_get_action_parameter('RUN_XDO');
276 FETCH c_get_action_parameter
277 INTO l_xdo_run;
278 CLOSE c_get_action_parameter;
279
280 hr_utility.trace ('l_xdo_run '|| l_xdo_run);
281 hr_utility.trace ('l_print_files '|| l_print_files);
282
283 IF (l_xdo_run = 'N') and (l_print_files = 'N') THEN
284 loop
285 exit when not l_flag;
286 for i in request_list.first..request_list.last loop
287 hr_utility.trace ('request_list(i)'||request_list(i));
288 l_valid_request := fnd_concurrent.get_request_status(
289 request_id => request_list(i),
290 appl_shortname => 'PAY',
291 program => 'PAYGENXDOREPORT',
292 phase => l_phase,
293 status => l_status,
294 dev_phase => l_dev_phase,
295 dev_status => l_dev_status,
296 message => l_message);
297
298 hr_utility.trace ('l_dev_phase '|| l_dev_phase);
299 if (l_valid_request and l_dev_phase not in ('PENDING', 'RUNNING')) then
300 hr_utility.trace ('In l_flag := FALSE');
301 l_flag := FALSE;
302 else
303 l_flag := TRUE;
304 hr_utility.trace ('In l_flag := TRUE');
305 exit;
306 end if;
307
308 dbms_lock.sleep(20);
309 hr_utility.trace ('Waiting for request completion...');
310
311 end loop;
312 end loop;
313 hr_utility.trace ('All Requests Completed...');
314
315 END IF; -- run_xdo = N and print_files = N
316 --
317 select pay_core_utils.get_parameter('REMOVE_ACT',
318 pa1.legislative_parameters)
319 into remove_act
320 from pay_payroll_actions pa1
321 where pa1.payroll_action_id = pactid;
322 --
323 if (remove_act is null or remove_act = 'Y') then
324 del_file_details(pactid);
325 hr_utility.trace ('pay_file_details deleted');
326 pay_archive.remove_report_actions(pactid);
327 hr_utility.trace ('Removed Payroll and assignment actions');
328 end if;
329
330 hr_utility.trace ('Leaving '|| l_proc_name);
331
332 END standard_deinit;
333
334
335 END pay_core_xdo_utils;