[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_MAGTAPE_PKG
Source
1 package body pay_jp_magtape_pkg as
2 /* $Header: payjpmag.pkb 120.0 2005/05/29 02:37:56 appldev noship $ */
3 --
4 -- Global variables and Constants.
5 --
6 g_character_set varchar2(80);
7 g_header_sql varchar2(2000);
8 g_data_sql varchar2(2000);
9 g_trailer_sql varchar2(2000);
10 g_end_sql varchar2(2000);
11 g_package_name varchar2(30) := 'pay_jp_magtape_pkg';
12 c_default_package_name constant varchar2(30) := 'pay_jp_magtape_format_pkg';
13 --------------------------------------------------------------------------------
14 -- This function returns latest request_id for specified payroll_action_id.
15 --------------------------------------------------------------------------------
16 function latest_request_id(
17 p_payroll_action_id in number) return number
18 --------------------------------------------------------------------------------
19 is
20 l_request_id number;
21 --
22 -- Pay attention that this cursor never raise %NOTFOUND error
23 -- because of using MAX function.
24 --
25 cursor csr_request_id is
26 select max(request_id)
27 from fnd_concurrent_requests fcr,
28 fnd_concurrent_programs fcp
29 where fcp.application_id = 801
30 and fcp.concurrent_program_name = 'PAYJPMAG'
31 and fcr.program_application_id = fcp.application_id
32 and fcr.concurrent_program_id = fcp.concurrent_program_id
33 and fcr.argument1 = to_char(p_payroll_action_id);
34 begin
35 open csr_request_id;
36 fetch csr_request_id into l_request_id;
37 close csr_request_id;
38 --
39 return l_request_id;
40 end latest_request_id;
41 --------------------------------------------------------------------------------
42 -- Debug purpose.
43 --------------------------------------------------------------------------------
44 function show_sql(
45 p_record_type in varchar2) return varchar2
46 --------------------------------------------------------------------------------
47 is
48 begin
49 if p_record_type = 'H' then
50 return g_header_sql;
51 elsif p_record_type = 'D' then
52 return g_data_sql;
53 elsif p_record_type = 'T' then
54 return g_trailer_sql;
55 elsif p_record_type = 'E' then
56 return g_end_sql;
57 else
58 return null;
59 end if;
60 end show_sql;
61 --------------------------------------------------------------------------------
62 -- This procedure initializes dynamic sql string which is kicked for each
63 -- header, data, trailer and end record of direct deposit file.
64 --------------------------------------------------------------------------------
65 procedure init(
66 p_package_name in varchar2,
67 p_character_set in varchar2)
68 --------------------------------------------------------------------------------
69 is
70 l_api_name varchar2(61) := g_package_name || '.init';
71 l_package_name varchar2(30) := nvl(p_package_name,c_default_package_name);
72 begin
73 --
74 -- Check mandatory argument.
75 --
76 hr_api.mandatory_arg_error(
77 p_api_name => l_api_name,
78 p_argument => 'p_character_set',
79 p_argument_value => p_character_set);
80 --
81 -- Initialize global variables.
82 --
83 g_character_set := p_character_set;
84 --
85 g_header_sql :=
86 'declare
87 l_data pay_jp_magtape_pkg.header;
88 begin
89 l_data.org_payment_method_id := :v1;
90 l_data.transaction_type := :v2;
91 l_data.character_set_code := :v3;
92 l_data.direct_deposit_date := :v4;
93 l_data.company_code := :v5;
94 l_data.company_name := :v6;
95 l_data.external_account_id := :v7;
96 l_data.bank_code := :v8;
97 l_data.bank_name_kana := :v9;
98 l_data.branch_code := :v10;
99 l_data.branch_name_kana := :v11;
100 l_data.account_type := :v12;
101 l_data.account_number := :v13;
102 l_data.account_name := :v14;
103 l_data.description1 := :v15;
104 l_data.description2 := :v16;
105 :v17 := ' || l_package_name || '.header_record(l_data);
106 end;';
107 --
108 g_data_sql :=
109 'declare
110 l_data pay_jp_magtape_pkg.data;
111 begin
112 l_data.person_id := :v1;
113 l_data.employee_number := :v2;
114 l_data.external_account_id := :v3;
115 l_data.bank_code := :v4;
116 l_data.bank_name_kana := :v5;
117 l_data.branch_code := :v6;
118 l_data.branch_name_kana := :v7;
119 l_data.account_type := :v8;
120 l_data.account_number := :v9;
121 l_data.account_name := :v10;
122 l_data.description1 := :v11;
123 l_data.description2 := :v12;
124 l_data.payment := :v13;
125 :v14 := ' || l_package_name || '.data_record(l_data);
126 end;';
127 --
128 g_trailer_sql :=
129 'declare
130 l_data pay_jp_magtape_pkg.trailer;
131 begin
132 l_data.total_count := :v1;
133 l_data.total_payment := :v2;
134 :v3 := ' || l_package_name || '.trailer_record(l_data);
135 end;';
136 --
137 g_end_sql :=
138 'begin
139 :v1 := ' || l_package_name || '.end_record;
140 end;';
141 end init;
142 --------------------------------------------------------------------------------
143 -- Function to return HEADER record of direct deposit file.
144 -- This function is interface between PAYJPMAG.rdf and pay_jp_magtape_format_pkg
145 -- (default format package. user can override format package on Org Paymeth
146 -- form).
147 --------------------------------------------------------------------------------
148 function header_record(
149 p_org_payment_method_id in number,
150 p_transaction_type in varchar2,
151 p_character_set_code in varchar2,
152 p_direct_deposit_date in date,
153 p_company_code in varchar2,
154 p_company_name in varchar2,
155 p_external_account_id in number,
156 p_bank_code in varchar2,
157 p_bank_name_kana in varchar2,
158 p_branch_code in varchar2,
159 p_branch_name_kana in varchar2,
160 p_account_type in varchar2,
161 p_account_number in varchar2,
162 p_account_name in varchar2,
163 p_description1 in varchar2,
164 p_description2 in varchar2) return varchar2
165 --------------------------------------------------------------------------------
166 is
167 l_api_name varchar2(61) := g_package_name || '.header_record';
168 l_buffer varchar2(2000);
169 begin
170 --
171 -- Check mandatory argument.
172 --
173 hr_api.mandatory_arg_error(
174 p_api_name => l_api_name,
175 p_argument => 'p_org_payment_method_id',
176 p_argument_value => p_org_payment_method_id);
177 hr_api.mandatory_arg_error(
178 p_api_name => l_api_name,
179 p_argument => 'p_transaction_type',
180 p_argument_value => p_transaction_type);
181 hr_api.mandatory_arg_error(
182 p_api_name => l_api_name,
183 p_argument => 'p_character_set_code',
184 p_argument_value => p_character_set_code);
185 hr_api.mandatory_arg_error(
186 p_api_name => l_api_name,
187 p_argument => 'p_direct_deposit_date',
188 p_argument_value => p_direct_deposit_date);
189 hr_api.mandatory_arg_error(
190 p_api_name => l_api_name,
191 p_argument => 'p_company_code',
192 p_argument_value => p_company_code);
193 hr_api.mandatory_arg_error(
194 p_api_name => l_api_name,
195 p_argument => 'p_company_name',
196 p_argument_value => p_company_name);
197 hr_api.mandatory_arg_error(
198 p_api_name => l_api_name,
199 p_argument => 'p_external_account_id',
200 p_argument_value => p_external_account_id);
201 hr_api.mandatory_arg_error(
202 p_api_name => l_api_name,
203 p_argument => 'p_bank_code',
204 p_argument_value => p_bank_code);
205 hr_api.mandatory_arg_error(
206 p_api_name => l_api_name,
207 p_argument => 'p_bank_name_kana',
208 p_argument_value => p_bank_name_kana);
209 hr_api.mandatory_arg_error(
210 p_api_name => l_api_name,
211 p_argument => 'p_branch_code',
212 p_argument_value => p_branch_code);
213 hr_api.mandatory_arg_error(
214 p_api_name => l_api_name,
215 p_argument => 'p_branch_name_kana',
216 p_argument_value => p_branch_name_kana);
217 hr_api.mandatory_arg_error(
218 p_api_name => l_api_name,
219 p_argument => 'p_account_type',
220 p_argument_value => p_account_type);
221 hr_api.mandatory_arg_error(
222 p_api_name => l_api_name,
223 p_argument => 'p_account_number',
224 p_argument_value => p_account_number);
225 hr_api.mandatory_arg_error(
226 p_api_name => l_api_name,
227 p_argument => 'p_account_name',
228 p_argument_value => p_account_name);
229 --
230 -- Get formatted record.
231 --
232 execute immediate g_header_sql using
233 p_org_payment_method_id,
234 p_transaction_type,
235 p_character_set_code,
236 p_direct_deposit_date,
237 p_company_code,
238 p_company_name,
239 p_external_account_id,
240 p_bank_code,
241 p_bank_name_kana,
242 p_branch_code,
243 p_branch_name_kana,
244 p_account_type,
245 p_account_number,
246 p_account_name,
247 p_description1,
248 p_description2,
249 out l_buffer;
250 --
251 -- Return value.
252 --
253 return convert(l_buffer,g_character_set);
254 exception
255 when others then
256 fnd_message.set_name('PER','HR_ERROR');
257 fnd_message.set_token('SQL',g_header_sql);
258 fnd_message.raise_error;
259 end header_record;
260 --------------------------------------------------------------------------------
261 -- Function to return DATA record of direct deposit file.
262 -- This function is interface between PAYJPMAG.rdf and pay_jp_magtape_format_pkg
263 -- (default format package. user can override format package on Org Paymeth
264 -- form).
265 --------------------------------------------------------------------------------
266 function data_record(
267 p_person_id in number,
268 p_employee_number in varchar2,
269 p_external_account_id in number,
270 p_bank_code in varchar2,
271 p_bank_name_kana in varchar2,
272 p_branch_code in varchar2,
273 p_branch_name_kana in varchar2,
274 p_account_type in varchar2,
275 p_account_number in varchar2,
276 p_account_name in varchar2,
277 p_description1 in varchar2,
278 p_description2 in varchar2,
279 p_payment in number) return varchar2
280 --------------------------------------------------------------------------------
281 is
282 l_api_name varchar2(61) := g_package_name || '.data_record';
283 l_buffer varchar2(2000);
284 begin
285 --
286 -- Check mandatory argument.
287 --
288 hr_api.mandatory_arg_error(
289 p_api_name => l_api_name,
290 p_argument => 'p_person_id',
291 p_argument_value => p_person_id);
292 hr_api.mandatory_arg_error(
293 p_api_name => l_api_name,
294 p_argument => 'p_employee_number',
295 p_argument_value => p_employee_number);
296 hr_api.mandatory_arg_error(
297 p_api_name => l_api_name,
298 p_argument => 'p_external_account_id',
299 p_argument_value => p_external_account_id);
300 hr_api.mandatory_arg_error(
301 p_api_name => l_api_name,
302 p_argument => 'p_bank_code',
303 p_argument_value => p_bank_code);
304 hr_api.mandatory_arg_error(
305 p_api_name => l_api_name,
306 p_argument => 'p_bank_name_kana',
307 p_argument_value => p_bank_name_kana);
308 hr_api.mandatory_arg_error(
309 p_api_name => l_api_name,
310 p_argument => 'p_branch_code',
311 p_argument_value => p_branch_code);
312 hr_api.mandatory_arg_error(
313 p_api_name => l_api_name,
314 p_argument => 'p_branch_name_kana',
315 p_argument_value => p_branch_name_kana);
316 hr_api.mandatory_arg_error(
317 p_api_name => l_api_name,
318 p_argument => 'p_account_type',
319 p_argument_value => p_account_type);
320 hr_api.mandatory_arg_error(
321 p_api_name => l_api_name,
322 p_argument => 'p_account_number',
323 p_argument_value => p_account_number);
324 hr_api.mandatory_arg_error(
325 p_api_name => l_api_name,
326 p_argument => 'p_account_name',
327 p_argument_value => p_account_name);
328 hr_api.mandatory_arg_error(
329 p_api_name => l_api_name,
330 p_argument => 'p_payment',
331 p_argument_value => p_payment);
332 --
333 -- Get formatted record.
334 --
335 execute immediate g_data_sql using
336 p_person_id,
337 p_employee_number,
338 p_external_account_id,
339 p_bank_code,
340 p_bank_name_kana,
341 p_branch_code,
342 p_branch_name_kana,
343 p_account_type,
344 p_account_number,
345 p_account_name,
346 p_description1,
347 p_description2,
348 p_payment,
349 out l_buffer;
350 --
351 -- Return value.
352 --
353 return convert(l_buffer,g_character_set);
354 exception
355 when others then
356 fnd_message.set_name('PER','HR_ERROR');
357 fnd_message.set_token('SQL',g_data_sql);
358 fnd_message.raise_error;
359 end data_record;
360 --------------------------------------------------------------------------------
361 -- Function to return TRAILER record of direct deposit file.
362 -- This function is interface between PAYJPMAG.rdf and pay_jp_magtape_format_pkg
363 -- (default format package. user can override format package on Org Paymeth
364 -- form).
365 --------------------------------------------------------------------------------
366 function trailer_record(
367 p_total_count in number,
368 p_total_payment in number) return varchar2
369 --------------------------------------------------------------------------------
370 is
371 l_api_name varchar2(61) := g_package_name || '.trailer_record';
372 l_buffer varchar2(2000);
373 begin
374 --
375 -- Check mandatory argument.
376 --
377 hr_api.mandatory_arg_error(
378 p_api_name => l_api_name,
379 p_argument => 'p_total_count',
380 p_argument_value => p_total_count);
381 hr_api.mandatory_arg_error(
382 p_api_name => l_api_name,
383 p_argument => 'p_total_payment',
384 p_argument_value => p_total_payment);
385 --
386 -- Get formatted record.
387 --
388 execute immediate g_trailer_sql using
389 p_total_count,
390 p_total_payment,
391 out l_buffer;
392 --
393 -- Return value.
394 --
395 return convert(l_buffer,g_character_set);
396 exception
397 when others then
398 fnd_message.set_name('PER','HR_ERROR');
399 fnd_message.set_token('SQL',g_trailer_sql);
400 fnd_message.raise_error;
401 end trailer_record;
402 --------------------------------------------------------------------------------
403 -- Function to return END record of direct deposit file.
404 -- This function is interface between PAYJPMAG.rdf and pay_jp_magtape_format_pkg
405 -- (default format package. user can override format package on Org Paymeth
406 -- form).
407 --------------------------------------------------------------------------------
408 function end_record return varchar2
409 --------------------------------------------------------------------------------
410 is
411 l_api_name varchar2(61) := g_package_name || '.end_record';
412 l_buffer varchar2(2000);
413 begin
414 --
415 -- Get formatted record.
416 --
417 execute immediate g_end_sql using out l_buffer;
418 --
419 -- Return value.
420 --
421 return convert(l_buffer,g_character_set);
422 exception
423 when others then
424 fnd_message.set_name('PER','HR_ERROR');
425 fnd_message.set_token('SQL',g_end_sql);
426 fnd_message.raise_error;
427 end end_record;
428 --
429 end pay_jp_magtape_pkg;