[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_DD_MAGTAPE_PKG
Source
1 PACKAGE BODY pay_ca_dd_magtape_pkg AS
2 /* $Header: pycaddmg.pkb 120.8 2006/10/23 19:20:02 pganguly noship $ */
3
4
5 procedure run_formula_or_jcp_xml is
6 l_ret varchar2(32767);
7 p_ff_formula_id varchar2(9);
8 l_ff_formula_id varchar2(9);
9 l_ff_formula_name varchar2(80);
10 p_formula_name varchar2(30);
11 l_outdir varchar2(30);
12 l_outfile varchar2(30);
13 l_logfile varchar2(30);
14 l_xslfile varchar2(30);
15 l_filename varchar2(30);
16 l_doctag varchar2(80);
17 l_fcn varchar2(30);
18 errbuff varchar2(240);
19 retcode number;
20 l_success boolean;
21 l_originator_id varchar2(25);
22
23 cursor c_get_originator_id(cp_payment_method_id number) is
24 select PMETH_INFORMATION2
25 from pay_org_payment_methods_f
26 where org_payment_method_id = cp_payment_method_id;
27
28 Begin
29 /* hr_utility.trace_on('Y','TESTCADD'); */
30 p_formula_name := 'DUMMY_DD_FORMULA';
31 g_payroll_action_id := fnd_number.canonical_to_number(pay_mag_tape.internal_prm_values(3));
32 --g_payroll_action_id := 7193;
33 hr_utility.trace('g_payroll_action_id = '||to_char(g_payroll_action_id));
34 --
35 -- Select all the relevent information using payroll action id
36 --
37 select business_group_id,
38 effective_date,
39 to_char(overriding_dd_date,'YYMMDD'),
40 org_payment_method_id,
41 request_id,
42 legislative_parameters
43 into g_business_group_id,
44 g_effective_date,
45 g_direct_dep_date,
46 g_org_payment_method_id,
47 g_request_id,
48 g_legislative_parameters
49 from pay_payroll_actions
50 where payroll_action_id = g_payroll_action_id;
51
52 if SQL%NOTFOUND then
53 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
54 hr_utility.set_message_token('PROCEDURE','pay_ca_dd_magtape_pkg');
55 hr_utility.set_message_token('STEP','1');
56 hr_utility.raise_error;
57 end if;
58
59 hr_utility.trace('g_payroll_action_id = '||to_char(g_payroll_action_id));
60 hr_utility.trace('g_org_payment_method_id = '||to_char(g_org_payment_method_id));
61 hr_utility.trace('g_request_id = '||g_request_id);
62 if g_legislative_parameters is not null then
63
64 -- Get the MAGTAPE_REPORT_ID
65
66 g_magtape_report_id := pay_ca_dd_magtape_pkg.get_parameter('MAGTAPE_REPORT_ID',g_legislative_parameters);
67
68 hr_utility.trace('g_magtape_report_id = '||g_magtape_report_id);
69
70 -- Get the File Creation Number Override
71
72 g_fcn_override := pay_ca_dd_magtape_pkg.get_parameter('FILE_CREATION_NUMBER_OVERRIDE',g_legislative_parameters);
73
74 hr_utility.trace('g_fcn_override = '||g_fcn_override);
75
76 -- Get the File Creation Date
77
78 g_file_creation_date := pay_ca_dd_magtape_pkg.get_parameter('FILE_CREATION_DATE',g_legislative_parameters);
79
80 hr_utility.trace('g_file_creation_date = '||g_file_creation_date);
81
82 end if;
83
84 /* Added for FCN Validation */
85
86 if g_org_payment_method_id is not null then
87 open c_get_originator_id(g_org_payment_method_id);
88 fetch c_get_originator_id into l_originator_id;
89 close c_get_originator_id;
90 end if;
91
92 /* End for FCN Validation */
93 /* Testing ---if g_magtape_report_id <> 'BMO' then --'RBC' then --'CIBC' then */
94 /* hr_utility.trace_on('Y','MITA'); */
95 if g_magtape_report_id not in ('CIBC','NOVA_SCOT','TD','CPA') then
96 begin
97 hr_utility.trace('In not CIBC');
98 pay_magtape_generic.new_formula;
99
100 l_ff_formula_id := pay_mag_tape.internal_prm_values(2);
101
102 hr_utility.trace('Formula id is '||l_ff_formula_id);
103 if l_ff_formula_id <> '0'
104 then
105 begin
106 select formula_name
107 INTO l_ff_formula_name
108 from FF_FORMULAS_F
109 where g_effective_date between EFFECTIVE_START_DATE and
110 EFFECTIVE_END_DATE
111 and FORMULA_id = l_ff_formula_id;
112 if l_ff_formula_name in ('BNC_TRAILER','RBC_TRAILER','BMO_TRAILER')
113
114 /* ( 'BNC_HEADER','BNC_MULTI_PAYMENTS',
115 'BNC_PAYMENT','BNC_REPORT_TITLES' ,
116 'BNC_TRAILER',
117 'BMO_BATCH_HEADER',
118 'BMO_BATCH_TRAILER',
119 'BMO_HEADER',
120 'BMO_MULTI_PAYMENTS',
121 'BMO_PAYMENT','BMO_REPORT_TITLES',
122 'BMO_TRAILER', 'RBC_HEADER' ,
123 'RBC_MULTI_PAYMENTS','RBC_PAYMENT',
124 'RBC_REPORT_TITLES', 'RBC_TRAILER' )
125 */
126 then
127 hr_utility.trace('In raise no_data_found 1');
128 raise no_data_found;
129 end if;
130 exception when no_data_found then
131 hr_utility.trace('In raise no_data_found 2');
132 raise no_data_found;
133 end;
134 end if;
135 exception when others then
136 -- raise no_data_found;
137 null;
138 end;
139 else
140
141 --IF g_magtape_report_id in ('NOVA_SCOT','TD','CIBC') THEN
142 -- pay_magtape_generic.new_formula;
143 --ELSE
144 BEGIN
145 select TO_CHAR(FORMULA_ID)
146 INTO p_ff_formula_id
147 from FF_FORMULAS_F
148 where g_effective_date between EFFECTIVE_START_DATE and
149 EFFECTIVE_END_DATE
150 and FORMULA_NAME = p_formula_name;
151
152
153 if SQL%NOTFOUND then
154 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
155 hr_utility.set_message_token('PROCEDURE','pay_ca_dd_magtape_pkg');
156 hr_utility.set_message_token('STEP','1');
157 hr_utility.raise_error;
158 end if;
159
160
161 if g_magtape_report_id = 'CIBC' then
162 l_xslfile := 'html/pycacibc.xsl'; /* CIBC Bank */
163 elsif g_magtape_report_id = 'NOVA_SCOT' then
164 l_xslfile := 'html/pycanvsc.xsl'; /*Nova Scotia Bank */
165 elsif g_magtape_report_id = 'TD' then
166 l_xslfile := 'html/pycatdct.xsl'; /* TD Bank */
167 elsif g_magtape_report_id = 'CPA' then
168 l_xslfile := 'html/pycacucc.xsl'; /* Credit Union Central
169 of Canada */
170 end if;
171
172
173 l_outdir := '/sqlcom/out/';
174 l_outfile := 'p'||g_request_id||'.mf';
175 l_logfile := 'l'||g_request_id||'.req1';
176 l_doctag := 'PAYMENT_INFO_ROW';
177
178 /* if g_fcn_override is not null then
179 l_fcn := g_fcn_override;
180 else
181 l_fcn := pay_ca_direct_deposit_pkg.get_file_creation_number('7000','RBC',null);
182 end if;
183 */
184
185 /* New FCN validation for XML formats with actual
186 parameter values, also includes fix for bug#2790271 */
187
188 if g_fcn_override is not null then
189 l_fcn := pay_ca_direct_deposit_pkg.get_dd_file_creation_number
190 (g_org_payment_method_id,g_magtape_report_id,
191 g_fcn_override,g_payroll_action_id,
192 g_business_group_id);
193 else
194 l_fcn := pay_ca_direct_deposit_pkg.get_dd_file_creation_number
195 (g_org_payment_method_id,g_magtape_report_id,
196 null,g_payroll_action_id,g_business_group_id);
197 end if;
198 hr_utility.trace('l_fcn:'||l_fcn);
199
200 if l_fcn = '1.1' then
201 pay_core_utils.push_message(801,'PAY_74121_FILE_ALREADY_EXISTS','A');
202 pay_core_utils.push_token('package','pay_ca_dd_magtape_pkg.run_formula_or_jcp_xml');
203
204 raise hr_utility.hr_error;
205
206 elsif l_fcn = '1.2' then
207 pay_core_utils.push_message(801,'PAY_74122_INVALID_FILE_NUMBER','A');
208 raise hr_utility.hr_error;
209
210 end if;
211 /* New FCN validation for XML formats ends here */
212
213 hr_utility.trace('l_outdir = '||l_outdir);
214 hr_utility.trace('l_outfile = '||l_outfile);
215 hr_utility.trace('l_logfile = '||l_logfile);
216 hr_utility.trace('l_xslfile = '||l_xslfile);
217 hr_utility.trace('l_doctag = '||l_doctag);
218 hr_utility.trace('l_fcn = '||l_fcn);
219 hr_utility.trace('In newdd 6');
220 pay_ca_dd_magtape_pkg.submit_xml_mag_jcp(errbuff,
221 retcode,
222 g_payroll_action_id,
223 g_org_payment_method_id,
224 l_outdir,
225 l_outfile,
226 l_logfile,
227 l_xslfile,
228 l_doctag,
229 l_fcn,
230 g_request_id,
231 l_success
232 );
233 /* l_ret := pay_xml_magtape_pkg.submit_xml_mag_jcp(7193,1425); */
234 pay_mag_tape.internal_prm_values(1) := '2';
235 pay_mag_tape.internal_prm_values(2) := p_ff_formula_id;
236
237 if l_success then
238 hr_utility.trace('TRUE ' );
239 else
240 hr_utility.trace('FALSE ' );
241 end if;
242 hr_utility.trace('retcode = '||to_char(retcode));
243 hr_utility.trace('errbuff = '||errbuff );
244 hr_utility.trace('In newdd 7');
245 if retcode = 2 then
246 raise java_conc_error;
247 end if;
248 exception
249 when java_conc_error then
250 raise;
251 end ;
252 -- END IF; -- End IF NOVA_SCOT
253 end if;
254
255 if g_magtape_report_id in ('CIBC','NOVA_SCOT','TD','CPA') then
256 raise no_data_found;
257 else
258 l_ff_formula_id := pay_mag_tape.internal_prm_values(2);
259
260 hr_utility.trace('Formula id is '||l_ff_formula_id);
261 begin
262 select formula_name
263 INTO l_ff_formula_name
264 from FF_FORMULAS_F
265 where g_effective_date between EFFECTIVE_START_DATE and
266 EFFECTIVE_END_DATE
267 and FORMULA_id = l_ff_formula_id;
268 if l_ff_formula_name not in ( 'BNC_HEADER','BNC_MULTI_PAYMENTS',
269 'BNC_PAYMENT','BNC_REPORT_TITLES' ,
270 'BNC_TRAILER',
271 'BMO_BATCH_HEADER',
272 'BMO_BATCH_TRAILER',
273 'BMO_HEADER',
274 'BMO_MULTI_PAYMENTS',
275 'BMO_PAYMENT','BMO_REPORT_TITLES',
276 'BMO_TRAILER', 'RBC_HEADER' ,
277 'RBC_MULTI_PAYMENTS','RBC_PAYMENT',
278 'RBC_REPORT_TITLES', 'RBC_TRAILER' )
279 then
280 hr_utility.trace('In raise no_data_found 1');
281 select TO_CHAR(FORMULA_ID)
282 INTO p_ff_formula_id
283 from FF_FORMULAS_F
284 where g_effective_date between EFFECTIVE_START_DATE and
285 EFFECTIVE_END_DATE
286 and FORMULA_NAME = p_formula_name;
287 pay_mag_tape.internal_prm_values(2) := p_ff_formula_id;
288 raise no_data_found;
289 end if;
290 end;
291 end if;
292 -- hr_utility.trace_off;
293
294 End run_formula_or_jcp_xml;
295
296 procedure submit_xml_mag_jcp(
297 ERRBUF OUT NOCOPY VARCHAR2,
298 RETCODE OUT NOCOPY NUMBER,
299 P_PACTID in number,
300 P_PMETHID in number,
301 P_OUTDIR in varchar2,
302 P_OUTFILE in varchar2,
303 P_LOGFILE in varchar2,
304 P_XSLFILE in varchar2,
305 P_DOCTAG in varchar2,
306 P_FCN in varchar2,
307 P_REQUEST_ID in out NOCOPY number,
308 P_SUCCESS out NOCOPY boolean
309 ) is
310
311 l6_wait BOOLEAN;
312 l6_phase VARCHAR2(30);
313 l6_status VARCHAR2(30);
314 l6_dev_phase VARCHAR2(30);
315 l6_dev_status VARCHAR2(30);
316 l6_message VARCHAR2(255);
317 l_req_id NUMBER;
318 copies_buffer varchar2(80) := null;
319 print_buffer varchar2(80) := null;
320 printer_buffer varchar2(80) := null;
321 style_buffer varchar2(80) := null;
322 save_buffer boolean := null;
323 save_result varchar2(1) := null;
324 req_id VARCHAR2(80) := NULL; /* Request Id of
325 the main request */
326 x BOOLEAN;
327 p_result varchar2(80);
328 l_errbuf VARCHAR2(240);
329
330
331 BEGIN
332
333 BEGIN
334
335 -- initialise variables - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
336 retcode := 0;
337
338 hr_utility.trace('Before Concurrent Request');
339
340 -- req_id:=fnd_profile.value('CONC_REQUEST_ID');
341
342 /*
343 l_req_id := fnd_request.submit_request(application => 'PAY',
344 program => 'JATINJCP',
345 argument1 => 'PER');
346 */
347 hr_utility.trace('payroll_action_id = '||to_char(P_PACTID));
348 hr_utility.trace('org_payment_method_id = '||to_char(P_PMETHID));
349 hr_utility.trace('P_OUTDIR = '||P_OUTDIR);
350 hr_utility.trace('P_OUTFILE = '||P_OUTFILE);
351 hr_utility.trace('P_LOGFILE = '||P_LOGFILE);
352 hr_utility.trace('P_REQUEST_ID = '||P_REQUEST_ID);
353 hr_utility.trace('P_XSLFILE = '||P_XSLFILE);
354 hr_utility.trace('P_DOCTAG = '||P_DOCTAG);
355 hr_utility.trace('P_FCN = '||P_FCN);
356
357 l_req_id := fnd_request.submit_request(application => 'PAY',
358 program => 'PYCADDMG',
359 argument1 => 'PAY',
360 argument2 => P_PACTID,
361 argument3 => P_PMETHID,
362 argument4 => P_OUTDIR,
363 argument5 => P_OUTFILE,
367 argument9 => P_FCN,
364 argument6 => P_REQUEST_ID,
365 argument7 => P_XSLFILE,
366 argument8 => P_DOCTAG,
368 argument10 => P_LOGFILE
369 );
370
371 IF(l_req_id = 0) THEN
372 p_success := FALSE;
373 fnd_message.retrieve(l_errbuf);
374 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
375 -- hr_utility.raise_error;
376 raise zero_req_id;
377 ELSE
378 hr_utility.trace(' Concurrent Request Id : ' ||to_char(l_req_id));
379
380 COMMIT;
381
382 /* Wait for report request completion */
383 hr_utility.trace('Waiting for the application to get completed ');
384
385 /* Check for Report Request Status */
386
387 l6_wait := fnd_concurrent.wait_for_request
388 (request_id => l_req_id
389 ,interval => 30
390 ,phase => l6_phase
391 ,status => l6_status
392 ,dev_phase => l6_dev_phase
393 ,dev_status => l6_dev_status
394 ,message => l6_message);
395
396 p_success := TRUE;
397 hr_utility.trace('Wait completed');
398
399 hr_utility.trace('phase :'||l6_phase);
400 hr_utility.trace('status :'||l6_status);
401 hr_utility.trace('dev_phase :'||l6_dev_phase);
402 hr_utility.trace('dev_status :'||l6_dev_status);
403 hr_utility.trace('message :'||l6_message);
404
405 END IF; /* if l_req_id */
406
407 IF NOT (l6_dev_phase = 'COMPLETE' and l6_dev_status = 'NORMAL') THEN
408 hr_utility.trace(' Exited with error ');
409 if l6_dev_status = 'WARNING' then
410 retcode := 1;
411 else
412 retcode := 2;
413 end if;
414
415 ELSE
416 hr_utility.trace(' Request completed successfully') ;
417 hr_utility.trace('Successful');
418 END IF; /* l6_dev_phase */
419
420 -- Set up error message and error return code.
421 --
422 errbuf := hr_utility.get_message;
423
424
425 EXCEPTION
426 when zero_req_id then
427 raise;
428
429 WHEN NO_DATA_FOUND THEN
430 hr_utility.trace('Exception : No data Found');
431 p_success := FALSE;
432 l_errbuf := SQLERRM;
433 errbuf := l_errbuf;
434 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
435 --
436 -- Set up error message and error return code.
437 --
438
439 errbuf := hr_utility.get_message;
440 WHEN OTHERS THEN
441 hr_utility.trace('Exception : When Others');
442 p_success := FALSE;
443 l_errbuf := SQLERRM;
444 errbuf := l_errbuf;
445 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
446
447 --
448 END;
449
450 hr_utility.trace('RETCODE : '|| to_char(retcode) ||'ERRBUF: '||errbuf);
451 --
452 --
453
454 END submit_xml_mag_jcp;
455 ----------------------------- get_parameter -------------------------------
456 FUNCTION get_parameter(name in varchar2,
457 parameter_list varchar2)
458 RETURN VARCHAR2
459 IS
460 start_ptr number;
461 end_ptr number;
462 token_val pay_payroll_actions.legislative_parameters%type;
463 par_value pay_payroll_actions.legislative_parameters%type;
464 BEGIN
465
466 token_val := name || '=';
467
468 start_ptr := instr(parameter_list, token_val) + length(token_val);
469 end_ptr := instr(parameter_list, ' ',start_ptr);
470
471 /* if there is no spaces use then length of the string */
472 if end_ptr = 0 then
473 end_ptr := length(parameter_list) + 1;
474 end if;
475
476 /* Did we find the token */
477 if instr(parameter_list, token_val) = 0 then
478 par_value := NULL;
479 else
480 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
481 end if;
482
483 return par_value;
484
485 END get_parameter;
486
487 end pay_ca_dd_magtape_pkg;