[Home] [Help]
PACKAGE BODY: APPS.IBY_EXTRACTGEN_PVT
Source
1 PACKAGE BODY IBY_EXTRACTGEN_PVT AS
2 /* $Header: ibyxgenb.pls 120.59.12020000.2 2012/07/12 15:09:23 sgogula ship $ */
3
4 -- Global variables
5 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_EXTRACTGEN_PVT';
8
9 TYPE l_attribute_cat_rec_type IS RECORD(
10 l_hzp_attr_cat VARCHAR2(150)
11 );
12
13 TYPE l_att_cat_tbl_type IS TABLE OF l_attribute_cat_rec_type INDEX BY BINARY_INTEGER;
14
15
16 --For performance issue. 9280384
17 l_conc_invalid_chars VARCHAR2(50);
18 l_conc_replacement_chars VARCHAR2(50);
19
20 l_att_cat_tbl l_att_cat_tbl_type;
21
22 PROCEDURE print_debuginfo (
23 p_module IN VARCHAR2
24 , p_debug_text IN VARCHAR2)
25 IS
26 BEGIN
27 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
28 iby_debug_pub.add(p_debug_text,iby_debug_pub.G_LEVEL_INFO,p_module);
29 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_debug_text);
30 END IF;
31
32 END;
33
34 -- [lmallick]
35 -- Function simplified to just trim the preceding and leading characters
36 FUNCTION stripped_string (
37 expression_in IN VARCHAR2
38 ,characters_in IN VARCHAR2
39 ,placeholder_in IN VARCHAR2 DEFAULT '#'
40 )
41 RETURN VARCHAR2
42 IS
43 result_string VARCHAR2(3000);
44 BEGIN
45
46 result_string := TRIM (BOTH '''' FROM expression_in);
47
48 IF ( upper(result_string) = 'NULL' ) THEN
49 result_string := NULL;
50 END IF;
51 RETURN result_string;
52 END stripped_string;
53
54 PROCEDURE Create_Extract
55 (
56 p_extract_code IN iby_extracts_vl.extract_code%TYPE,
57 p_extract_version IN iby_extracts_vl.extract_version%TYPE,
58 p_params IN OUT NOCOPY JTF_VARCHAR2_TABLE_200,
59 x_extract_doc OUT NOCOPY CLOB
60 )
61 IS
62
63 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Create_Extract';
64
65 l_code_pkg iby_extracts_b.gen_code_package%TYPE;
66 l_code_entry iby_extracts_b.gen_code_entry_point%TYPE;
67 l_call VARCHAR2(3000);
68 l_param_1 VARCHAR2(3000);
69 l_param_2 VARCHAR2(3000);
70 l_param_3 VARCHAR2(3000);
71 l_param_4 VARCHAR2(3000);
72 l_param_5 VARCHAR2(3000);
73 l_param_6 VARCHAR2(3000);
74 l_param_7 VARCHAR2(3000);
75 l_param_8 VARCHAR2(3000);
76
77 l_x_not_found BOOLEAN;
78 l_numeric_char_mask V$NLS_PARAMETERS.value%TYPE;
79 l_default_num_mask VARCHAR2(10) := '.,';
80
81
82 x_return_status VARCHAR2(200);
83
84 CURSOR c_x_info(ci_extract_code IN iby_extracts_b.extract_code%TYPE,
85 ci_extract_version IN iby_extracts_b.extract_version%TYPE)
86 IS
87 SELECT gen_code_package,gen_code_entry_point
88 FROM iby_extracts_vl
89 WHERE (extract_code=ci_extract_code)
90 AND (extract_version=ci_extract_version)
91 AND (gen_code_language='PLSQL');
92 BEGIN
93 print_debuginfo(l_module_name
94 , 'Enter:TIMESTAMP:: ' || l_module_name||':: '||systimestamp
95 );
96
97 iby_debug_pub.log(l_module_name,'Enter:TIMESTAMP:: ' || l_module_name||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
98 -- Get NLS numeric character before calling extract.
99 -- bug 5604582
100 BEGIN
101 SELECT value
102 INTO l_numeric_char_mask
103 FROM V$NLS_PARAMETERS
104 WHERE parameter='NLS_NUMERIC_CHARACTERS';
105 EXCEPTION
106 WHEN others THEN NULL;
107 END;
108
109 IF (c_x_info%ISOPEN) THEN
110 CLOSE c_x_info;
111 END IF;
112
113 OPEN c_x_info(p_extract_code,p_extract_version);
114 FETCH c_x_info INTO l_code_pkg,l_code_entry;
115 l_x_not_found := c_x_info%NOTFOUND;
116 CLOSE c_x_info;
117
118 IF (l_x_not_found) THEN
119 raise_application_error(-20000,
120 'IBY_20590#TABLE=IBY_EXTRACTS_VL' || '#ID=' || p_extract_code,
121 FALSE);
122 END IF;
123 -- test_debug('l_code_pkg: '|| l_code_pkg);
124
125 -- alter session so that the numeric characters are set to decimal separatori(.)
126 -- if different
127 -- bug 5604582
128 IF l_numeric_char_mask <> l_default_num_mask THEN
129 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='||'"'||l_default_num_mask||'"';
130 END IF;
131
132 /* Bug 6016869
133 */
134 IF ( upper(l_code_pkg) = 'IBY_FNDCPT_EXTRACT_GEN_PVT'
135 AND upper(l_code_entry) = 'CREATE_EXTRACT_1_0'
136 AND p_params.COUNT IN (4,5) -- Bug 6673696
137 AND p_extract_version = 1) THEN
138
139 IF ( p_params.COUNT = 4 OR p_params(5) = 'NULL') THEN
140
141 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
142 print_debuginfo(l_module_name
143 , 'Calling CREATE_EXTRACT_1_0 with 4 params:'
144 );
145 --Do not log the following value
146 --p_params(4) = sys_key
147 print_debuginfo(l_module_name
148 , '4 params:'
149 || p_params(1) || ':'
150 || p_params(2) || ':'
151 || p_params(3) || ':'
152 -- || p_params(4) || ':'
153 );
154 END IF;
155 -- test_debug('p_params(1): '||p_params(1));
156 -- test_debug('p_params(4): '||p_params(4));
157 l_param_1 := stripped_string (p_params(1), '''');
158 l_param_2 := stripped_string (p_params(2), '''');
159 l_param_3 := stripped_string (p_params(3), '''');
160 l_param_4 := stripped_string (p_params(4), '''');
161
162 IF ( l_param_4 = 'NULL' ) THEN
163 l_param_4 := NULL;
164 END IF;
165
166 -- Bug 8544380
167 -- Changing NLS Charset to US Default as anyother format is
168 -- not accepted by BI Publisher.
169 IF l_numeric_char_mask <> l_default_num_mask THEN
170 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='||'"'||l_default_num_mask||'"';
171 END IF;
172
173 iby_debug_pub.log(l_module_name,'Enter:TIMESTAMP::Extract(4 params) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
174 IBY_FNDCPT_EXTRACT_GEN_PVT.CREATE_EXTRACT_1_0 (
175 p_instr_type => l_param_1,
176 p_req_type => l_param_2,
177 p_txn_id => to_number(l_param_3),
178 p_sys_key => HEXTORAW(l_param_4),
179 x_extract_doc => x_extract_doc
180 );
181
182 -- test_debug('after create extract... ');
183 -- Bug 8544380
184 -- Changing NLS Charset back to customer setting from US Default so
185 -- the customer is not affected by alter sessions.
186 IF l_numeric_char_mask <> l_default_num_mask THEN
187 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='||'"'||l_numeric_char_mask||'"';
188 END IF;
189 iby_debug_pub.log(l_module_name,'Exit:TIMESTAMP::Extract(4 params) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
190
191
192 ELSE
193 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
194 print_debuginfo(l_module_name
195 , 'Calling CREATE_EXTRACT_1_0 with 5 params:'
196 );
197
198 --Do not log these values
199 --p_params(4) = sys_key
200 --p_params(5) = cvv2
201 print_debuginfo(l_module_name
202 , '5 params:'
203 || p_params(1) || ':'
204 || p_params(2) || ':'
205 || p_params(3) || ':'
206 -- || p_params(4) || ':'
207 -- || p_params(5) || ':'
208 );
209
210 END IF;
211 l_param_1 := stripped_string (p_params(1), '''');
212 l_param_2 := stripped_string (p_params(2), '''');
213 l_param_3 := stripped_string (p_params(3), '''');
214 l_param_4 := stripped_string (p_params(4), '''');
215 l_param_5 := stripped_string (p_params(5), '''');
216
217 IF ( l_param_4 = 'NULL' ) THEN
218 l_param_4 := NULL;
219 END IF;
220
221 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
222 print_debuginfo(l_module_name, 'l_param_5 = ' || l_param_5 || ':');
223
224 END IF;
225 -- Bug 8544380
226 -- Changing NLS Charset to US Default as anyother format is
227 -- not accepted by BI Publisher.
228 IF l_numeric_char_mask <> l_default_num_mask THEN
229 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='||'"'||l_default_num_mask||'"';
230 END IF;
231
232 iby_debug_pub.log(l_module_name,'Enter:TIMESTAMP::Extract(5 params) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
233
234 IBY_FNDCPT_EXTRACT_GEN_PVT.CREATE_EXTRACT_1_0 (
235 p_instr_type => l_param_1,
236 p_req_type => l_param_2,
237 p_txn_id => to_number(l_param_3),
238 p_sys_key => HEXTORAW(l_param_4),
239 p_sec_val => l_param_5,
240 x_extract_doc => x_extract_doc
241 );
242
243 iby_debug_pub.log(l_module_name,'Exit:TIMESTAMP::Extract(5 params) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
244
245 -- Bug 8544380
246 -- Changing NLS Charset back to customer setting from US Default so
247 -- the customer is not affected by alter sessions.
248 IF l_numeric_char_mask <> l_default_num_mask THEN
249 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='||'"'||l_numeric_char_mask||'"';
250 END IF;
251
252
253 END IF;
254
255 ELSIF ( upper(l_code_pkg) ='IBY_FD_EXTRACT_GEN_PVT'
256 AND upper(l_code_entry) = 'CREATE_EXTRACT_1_0'
257 AND p_params.COUNT IN (5,8) -- Bug 6673696
258 AND p_extract_version = 1) THEN
259 BEGIN
260
261 IF l_numeric_char_mask <> l_default_num_mask THEN
262 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='||'"'||l_default_num_mask||'"';
263 END IF;
264
265 IF ( p_params.COUNT = 5) THEN
266
267 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
268 print_debuginfo(l_module_name
269 , 'Calling CREATE_EXTRACT_1_0 with 5 params:'
270 );
271 --Do not log p_param(5) as it contains sensitive data
272 print_debuginfo(l_module_name
273 , '5 params:'
274 || p_params(1) || ':'
275 || p_params(2) || ':'
276 || p_params(3) || ':'
277 || p_params(4) || ':'
278 -- || p_params(5) || ':'
279 );
280 END IF;
281 l_param_1 := stripped_string (p_params(1), '''');
282 l_param_2 := stripped_string (p_params(2), '''');
283 l_param_3 := stripped_string (p_params(3), '''');
284 l_param_4 := stripped_string (p_params(4), '''');
285 l_param_5 := stripped_string (p_params(5), '''');
286
287 IF ( l_param_5 = 'NULL' ) THEN
288 l_param_5 := NULL;
289 END IF;
290
291 iby_debug_pub.log(l_module_name,'Enter:TIMESTAMP::Extract(6 params) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
292
293
294 IBY_FD_EXTRACT_GEN_PVT.CREATE_EXTRACT_1_0 (
295 p_payment_instruction_id => to_number(l_param_1),
296 p_save_extract_flag => l_param_2,
297 p_format_type => l_param_3,
298 p_is_reprint_flag => l_param_4,
299 p_sys_key => HEXTORAW(l_param_5),
300 x_extract_doc => x_extract_doc
301 );
302 iby_debug_pub.log(l_module_name,'Exit:TIMESTAMP::Extract(6 params) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
303
304 ELSE
305 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
306 print_debuginfo(l_module_name
307 , 'Calling CREATE_EXTRACT_1_0 with 8 params:'
308 );
309 --Do not log p_param(8) as it contains sensitive data
310 print_debuginfo(l_module_name
311 , '6 params:'
312 || p_params(1) || ':'
313 || p_params(2) || ':'
314 || p_params(3) || ':'
315 || p_params(4) || ':'
316 || p_params(5) || ':'
317 || p_params(6) || ':'
318 || p_params(7) || ':'
319 -- || p_params(8) || ':'
320 );
321 END IF;
322 l_param_1 := stripped_string (p_params(1), '''');
323 l_param_2 := stripped_string (p_params(2), '''');
324 l_param_3 := stripped_string (p_params(3), '''');
325 l_param_4 := stripped_string (p_params(4), '''');
326 l_param_5 := stripped_string (p_params(5), '''');
327 l_param_6 := stripped_string (p_params(6), '''');
328 l_param_7 := stripped_string (p_params(7), '''');
329 l_param_8 := stripped_string (p_params(8), '''');
330 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
331 print_debuginfo(l_module_name, 'l_param_8 = ' || l_param_8 || ':');
332 END IF;
333 IF ( l_param_6 = 'NULL' ) THEN
334 l_param_6 := NULL;
335 END IF;
336 IF ( l_param_7 = 'NULL' ) THEN
337 l_param_7 := NULL;
338 END IF;
339 IF ( l_param_8 = 'NULL' ) THEN
340 l_param_8 := NULL;
341 END IF;
342 iby_debug_pub.log(l_module_name,'Enter:TIMESTAMP::Extract(8 params) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
343
344
345 IBY_FD_EXTRACT_GEN_PVT.CREATE_EXTRACT_1_0 (
346 p_payment_instruction_id =>to_number(l_param_1),
347 p_save_extract_flag => l_param_2,
348 p_format_type => l_param_3,
349 p_delivery_method => l_param_4,
350 p_sys_key => HEXTORAW(l_param_8),
351 p_payment_id => to_number(l_param_5),
352 x_extract_doc => x_extract_doc,
353 p_from_pmt_ref => to_number(l_param_6),
354 p_to_pmt_ref => to_number(l_param_7)
355
356 );
357
358 END IF;
359 iby_debug_pub.log(l_module_name,'Exit:TIMESTAMP::Extract(8 params) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
360
361 IF l_numeric_char_mask <> l_default_num_mask THEN
362 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '||
363 '"'||l_numeric_char_mask|| '"';
364 END IF;
365 EXCEPTION
366
367 WHEN OTHERS THEN
368 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
369 print_debuginfo(l_module_name, 'An exception has occured when creating extract..');
370 END IF;
371 /* Bug 9061437: commenting following code
372 -- when exception occurs, the payment_instruction must be unlocked from the request
373
374 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
375 p_object_id => to_number(l_param_1),
376 p_object_type => 'PAYMENT_INSTRUCTION',
377 x_return_status => x_return_status
378 );*/
379
380 RAISE;
381 END;
382 ELSIF ( upper(l_code_pkg) = 'IBY_FD_EXTRACT_GEN_PVT'
383 AND upper(l_code_entry) = ' CREATE_PPR_EXTRACT_1_0'
384 AND p_params.COUNT = 2 -- Bug 6673696
385 AND p_extract_version = 1) THEN
386 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
387 print_debuginfo(l_module_name
388 , 'Calling CREATE_EXTRACT_1_0 with 2 params:'
389 );
390 --Do not log p_param(2) as it contains sensitive data
391 print_debuginfo(l_module_name
392 , '2 params:'
393 || p_params(1) || ':'
394 -- || p_params(2) || ':'
395 );
396 END IF;
397 l_param_1 := stripped_string (p_params(1), '''');
398 l_param_2 := stripped_string (p_params(2), '''');
399 IF ( l_param_2 = 'NULL' ) THEN
400 l_param_2 := NULL;
401 END IF;
402 iby_debug_pub.log(l_module_name,'Enter:TIMESTAMP::PPR Extract ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
403
404 IBY_FD_EXTRACT_GEN_PVT.CREATE_PPR_EXTRACT_1_0 (
405 p_payment_service_request_id => to_number(l_param_1),
406 p_sys_key => HEXTORAW(l_param_2),
407 x_extract_doc => x_extract_doc
408 );
409 iby_debug_pub.log(l_module_name,'Exit:TIMESTAMP::PPR Extract ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
410
411 ELSIF ( upper(l_code_pkg) = 'IBY_FD_EXTRACT_GEN_PVT'
412 AND upper(l_code_entry) = 'CREATE_POS_PAY_EXTRACT_1_0'
413 AND p_params.COUNT IN (5,8) -- Bug 6673696
414 AND p_extract_version = 1) THEN
415
416 IF ( p_params.COUNT = 5) THEN
417
418 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
419 print_debuginfo(l_module_name
420 , 'Calling CREATE_POS_PAY_EXTRACT_1_0 with 5 params:'
421 );
422 --Do not log p_param(5) as it contains sensitive data
423 print_debuginfo(l_module_name
424 , '5 params:'
425 || p_params(1) || ':'
426 || p_params(2) || ':'
427 || p_params(3) || ':'
428 || p_params(4) || ':'
429 -- || p_params(5) || ':'
430 );
431 END IF;
432 l_param_1 := stripped_string (p_params(1), '''');
433 l_param_2 := stripped_string (p_params(2), '''');
434 l_param_3 := stripped_string (p_params(3), '''');
435 l_param_4 := stripped_string (p_params(4), '''');
436 l_param_5 := stripped_string (p_params(5), '''');
437
438 IF ( l_param_5 = 'NULL' ) THEN
439 l_param_5 := NULL;
440 END IF;
441 iby_debug_pub.log(l_module_name,'Enter:TIMESTAMP::Pos Pay Extract(5) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
442
443 IBY_FD_EXTRACT_GEN_PVT.CREATE_POS_PAY_EXTRACT_1_0 (
444 p_payment_instruction_id =>to_number(l_param_1),
445 p_payment_profile_id =>to_number(l_param_2),
446 p_from_date => l_param_3,
447 p_to_date => l_param_4,
448 p_sys_key => HEXTORAW(l_param_5),
449 x_extract_doc => x_extract_doc
450 );
451
452 iby_debug_pub.log(l_module_name,'Exit:TIMESTAMP::Pos Pay Extract(5) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
453
454 ELSE
455
456 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
457 print_debuginfo(l_module_name
458 , 'Calling CREATE_POS_PAY_EXTRACT_2_0 with 8 params:'
459 );
460
461 --Do not log p_param(8) as it contains sensitive data
462 print_debuginfo(l_module_name
463 , '8 params:'
464 || p_params(1) || ':'
465 || p_params(2) || ':'
466 || p_params(3) || ':'
467 || p_params(4) || ':'
468 || p_params(5) || ':'
469 || p_params(6) || ':'
470 || p_params(7) || ':'
471 -- || p_params(8) || ':'
472 );
473 END IF;
474 l_param_1 := stripped_string (p_params(1), '''');
475 l_param_2 := stripped_string (p_params(2), '''');
476 l_param_3 := stripped_string (p_params(3), '''');
477 l_param_4 := stripped_string (p_params(4), '''');
478 l_param_5 := stripped_string (p_params(5), '''');
479 l_param_6 := stripped_string (p_params(6), '''');
480 l_param_7 := stripped_string (p_params(7), '''');
481 l_param_8 := stripped_string (p_params(8), '''');
482
483 IF ( upper(l_param_8) = 'NULL' ) THEN
484 l_param_8 := NULL;
485 END IF;
486 iby_debug_pub.log(l_module_name,'Enter:TIMESTAMP::Pos Pay Extract(8) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
487
488 IBY_FD_EXTRACT_GEN_PVT.CREATE_POS_PAY_EXTRACT_2_0 (
489 p_payment_instruction_id =>to_number(l_param_1),
490 p_format_name => l_param_2,
491 p_internal_bank_account_name => l_param_3,
492 p_from_date => l_param_4,
493 p_to_date => l_param_5,
494 p_payment_status => l_param_6,
495 p_reselect => l_param_7,
496 p_sys_key => HEXTORAW(l_param_8),
497 x_extract_doc => x_extract_doc
498 );
499 iby_debug_pub.log(l_module_name,'Exit:TIMESTAMP::Pos Pay Extract(8) ::'||systimestamp,FND_LOG.LEVEL_STATEMENT);
500
501 END IF;
502 ELSE
503 /* End of Bug 6016869 */
504 -- provide the extract document OUT parameter
505 p_params.extend(1);
506 p_params(p_params.LAST) := null;
507
508 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
509 print_debuginfo(l_module_name, 'Calling Extract Dynamically');
510 print_debuginfo(l_module_name, 'p_params.COUNT' || p_params.COUNT );
511 print_debuginfo(l_module_name, 'l_code_entry: ' || l_code_entry);
512 print_debuginfo(l_module_name, 'l_code_pkg: ' || l_code_pkg);
513
514 END IF;
515
516 l_call := iby_utility_pvt.get_call_exec(l_code_pkg,l_code_entry,p_params);
517 --dbms_output.put_line('call:=<' || l_call || '>');
518
519 EXECUTE IMMEDIATE l_call USING OUT x_extract_doc;
520
521 END IF;
522
523 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
524 print_debuginfo(l_module_name, 'Appending XML Character Encoding Header');
525 END IF;
526 x_extract_doc := IBY_EXTRACTGEN_PVT.Get_XML_Char_Encoding_Header || x_extract_doc;
527
528 IF l_numeric_char_mask <> l_default_num_mask THEN
529 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '||
530 '"'||l_numeric_char_mask|| '"';
531 END IF;
532
533 print_debuginfo(l_module_name
534 , 'Exit:TIMESTAMP:: ' || l_module_name||':: '||systimestamp
535 );
536
537 END create_extract;
538
539
540 FUNCTION Get_Dffs(p_entity_table IN VARCHAR2, p_entity_id IN NUMBER, p_entity_code IN VARCHAR2)
541 RETURN XMLTYPE
542 IS
543 l_dffs XMLTYPE;
544 l_queryString VARCHAR2(4000); -- Bug 6827266
545 l_num_of_attributes NUMBER;
546 l_from_clause VARCHAR2(512);
547 l_where_clause VARCHAR2(2000); -- Bug 6827266
548 l_ap_attr_cat VARCHAR2(150);
549 l_hzp_attr_cat VARCHAR2(150);
550 l_attribute_category VARCHAR2(150);
551
552 /* Removed the cursor l_ap_doc_attr_cat_csr for the bug Bug 6763515*/
553
554
555 CURSOR l_hzp_attr_cat_csr (p_party_id IN NUMBER) IS
556 SELECT attribute_category
557 FROM hz_parties
558 WHERE party_id = p_party_id;
559
560 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Dffs';
561 BEGIN
562
563 print_debuginfo(l_debug_module
564 , 'Enter:TIMESTAMP:: ' || l_debug_module||':: '||systimestamp
565 );
566 /* Preparing the concatinated strings of invalid characters
567 and corresponding replacement characters. */
568
569
570 /* Do not need this loop as it now is in initialization block.
571 FOR i in 1..32 LOOP
572 l_conc_invalid_chars :=l_conc_invalid_chars||fnd_global.local_chr(i-1);
573 l_conc_replacement_chars :=l_conc_replacement_chars||' ';
574 END LOOP; */
575
576 IF p_entity_id IS NULL AND p_entity_code IS NULL THEN
577 RETURN NULL;
578 END IF;
579
580 print_debuginfo (l_debug_module, 'p_entity_table : '||p_entity_table);
581 print_debuginfo (l_debug_module, 'p_entity_id : '||p_entity_id);
582 print_debuginfo (l_debug_module, 'p_entity_code : '||p_entity_code);
583
584 IF p_entity_table = G_DFF_FD_PAYMENT_METHOD THEN
585 l_num_of_attributes := 15;
586 l_from_clause := ' From ' || p_entity_table;
587 l_where_clause := ' Where payment_method_code = :p_entity_code ';
588 SELECT attribute_category
589 INTO l_attribute_category
590 FROM IBY_PAYMENT_METHODS_B
591 WHERE payment_method_code = p_entity_code;
592
593 ELSIF p_entity_table = G_DFF_FD_PAYMENT_PROFILE THEN
594 l_num_of_attributes := 15;
595 l_from_clause := ' From ' || p_entity_table;
596 l_where_clause := ' Where system_profile_code = :p_entity_code ';
597 SELECT attribute_category
598 INTO l_attribute_category
599 FROM IBY_SYS_PMT_PROFILES_B
600 WHERE system_profile_code = p_entity_code;
601
602 ELSIF p_entity_table = G_DFF_FD_PAY_INSTRUCTION THEN
603 l_num_of_attributes := 15;
604 l_from_clause := ' From ' || p_entity_table;
605 l_where_clause := ' Where payment_instruction_id = :p_entity_id ';
606 SELECT attribute_category
607 INTO l_attribute_category
608 FROM IBY_PAY_INSTRUCTIONS_ALL
609 WHERE payment_instruction_id = p_entity_id;
610
611 ELSIF p_entity_table = G_DFF_FD_PAY_SERVICE_REQ THEN
612 l_num_of_attributes := 15;
613 l_from_clause := ' From ' || p_entity_table;
614 l_where_clause := ' Where payment_service_request_id = :p_entity_id ';
615 SELECT attribute_category
616 INTO l_attribute_category
617 FROM IBY_PAY_SERVICE_REQUESTS
618 WHERE payment_service_request_id = p_entity_id;
619
620 ELSIF p_entity_table = G_DFF_FD_PAYMENT THEN
621 l_num_of_attributes := 15;
622 l_from_clause := ' From ' || p_entity_table;
623 l_where_clause := ' Where payment_id = :p_entity_id ';
624 SELECT attribute_category
625 INTO l_attribute_category
626 FROM IBY_PAYMENTS_ALL
627 WHERE payment_id = p_entity_id;
628
629 ELSIF p_entity_table = G_DFF_AP_CHECK THEN
630 l_num_of_attributes := 15;
631 l_from_clause := ' From ' || p_entity_table;
632 l_where_clause := ' Where payment_id = :p_entity_id ';
633 SELECT attribute_category
634 INTO l_attribute_category
635 FROM AP_CHECKS_ALL
636 WHERE payment_id = p_entity_id;
637
638 ELSIF p_entity_table = G_DFF_FD_DOC_PAYABLE THEN
639 IF IBY_EXTRACTGEN_PVT.Is_Flex_Field_Enabled('AP_INVOICES',200) THEN
640 l_num_of_attributes := 15;
641 l_from_clause := ' From ' || p_entity_table;
642 l_where_clause := ' Where document_payable_id = :p_entity_id ';
643 SELECT attribute_category
644 INTO l_attribute_category
645 FROM IBY_DOCS_PAYABLE_ALL
646 WHERE document_payable_id = p_entity_id;
647 ELSE
648 RETURN NULL;
649 END IF;
650 ELSIF p_entity_table = G_DFF_FORMAT THEN
651 l_num_of_attributes := 15;
652 l_from_clause := ' From ' || p_entity_table;
653 l_where_clause := ' Where format_code = :p_entity_code ';
654 SELECT attribute_category
655 INTO l_attribute_category
656 FROM IBY_FORMATS_B
657 WHERE format_code = p_entity_code;
658
659 ELSIF p_entity_table = G_DFF_BEP_ACCOUNT THEN
660 l_num_of_attributes := 15;
661 l_from_clause := ' From ' || p_entity_table;
662 l_where_clause := ' Where bep_account_id = :p_entity_id ';
663 SELECT attribute_category
664 INTO l_attribute_category
665 FROM IBY_BEPKEYS
666 WHERE bep_account_id = p_entity_id;
667
668 ELSIF p_entity_table = G_DFF_LEGAL_ENTITY THEN
669 l_num_of_attributes := 20;
670 l_from_clause := ' From ' || p_entity_table;
671 l_where_clause := ' Where legal_entity_id = :p_entity_id ';
672 SELECT attribute_category
673 INTO l_attribute_category
674 FROM XLE_FIRSTPARTY_INFORMATION_V
675 WHERE legal_entity_id = p_entity_id;
676
677 ELSIF p_entity_table = G_DFF_PARTY THEN
678
679 IF (NOT(l_att_cat_tbl.EXISTS(p_entity_id))) THEN
680 OPEN l_hzp_attr_cat_csr (p_entity_id);
681 FETCH l_hzp_attr_cat_csr INTO l_att_cat_tbl(p_entity_id).l_hzp_attr_cat;
682 CLOSE l_hzp_attr_cat_csr;
683 END IF;
684
685 IF l_att_cat_tbl(p_entity_id).l_hzp_attr_cat IS NULL THEN
686 RETURN NULL;
687 END IF;
688
689 l_num_of_attributes := 24;
690 l_from_clause := ' From ' || p_entity_table;
691 l_where_clause := ' Where party_id = :p_entity_id ';
692 SELECT attribute_category
693 INTO l_attribute_category
694 FROM HZ_PARTIES
695 WHERE party_id = p_entity_id;
696
697 ELSIF p_entity_table = G_DFF_INT_BANK_ACCOUNT THEN
698 l_num_of_attributes := 15;
699 l_from_clause := ' From ' || p_entity_table;
700 l_where_clause := ' Where bank_account_id = :p_entity_id ';
701 SELECT attribute_category
702 INTO l_attribute_category
703 FROM CE_BANK_ACCOUNTS
704 WHERE bank_account_id = p_entity_id;
705
706 ELSIF p_entity_table = G_DFF_EXT_BANK_ACCOUNT THEN
707 l_num_of_attributes := 15;
708 l_from_clause := ' From ' || p_entity_table;
709 l_where_clause := ' Where ext_bank_account_id = :p_entity_id ';
710 SELECT attribute_category
711 INTO l_attribute_category
712 FROM IBY_EXT_BANK_ACCOUNTS
713 WHERE ext_bank_account_id = p_entity_id;
714
715 --Bug 11923557
716 ELSIF p_entity_table = G_DFF_PMT_INSTR_USES_ALL THEN
717 l_num_of_attributes := 15;
718 l_from_clause := ' From ' || p_entity_table;
719 l_where_clause := ' Where INSTRUMENT_PAYMENT_USE_ID = :p_entity_id ';
720 SELECT attribute_category
721 INTO l_attribute_category
722 FROM IBY_PMT_INSTR_USES_ALL
723 WHERE instrument_payment_use_id = p_entity_id;
724
725 ELSIF p_entity_table = G_DFF_PO_VENDORS THEN
726 l_num_of_attributes := 15;
727 l_from_clause := ' From ' || p_entity_table;
728 l_where_clause := ' Where VENDOR_ID = :p_entity_id ';
729 SELECT attribute_category
730 INTO l_attribute_category
731 FROM PO_VENDORS
732 WHERE VENDOR_ID = p_entity_id;
733
734 ELSIF p_entity_table = G_DFF_PO_VENDOR_SITES THEN
735 l_num_of_attributes := 15;
736 l_from_clause := ' From ' || p_entity_table;
737 l_where_clause := ' Where VENDOR_SITE_ID = :p_entity_id ';
738 SELECT attribute_category
739 INTO l_attribute_category
740 FROM PO_VENDOR_SITES_ALL
741 WHERE VENDOR_SITE_ID = p_entity_id;
742
743 ELSIF p_entity_table = G_DFF_AP_DOC THEN
744
745 IF IBY_EXTRACTGEN_PVT.Is_Flex_Field_Enabled('AP_INVOICES',200) THEN
746 /* Bug 6763515*/
747 print_debuginfo (l_debug_module, 'Before Execution the query for the table:'||G_DFF_AP_DOC);
748
749 SELECT attribute_category
750 INTO l_attribute_category
751 FROM iby_docs_payable_all idp
752 WHERE idp.document_payable_id = p_entity_id;
753
754 IF(l_attribute_category IS NULL) THEN
755 Select XMLConcat(XMLElement("AttributeCategory", attribute_category),
756 XMLElement("Attribute1", attribute1), XMLElement("Attribute2", attribute2),
757 XMLElement("Attribute3", attribute3), XMLElement("Attribute4", attribute4),
758 XMLElement("Attribute5", attribute5), XMLElement("Attribute6", attribute6),
759 XMLElement("Attribute7", attribute7), XMLElement("Attribute8", attribute8),
760 XMLElement("Attribute9", attribute9), XMLElement("Attribute10", attribute10),
761 XMLElement("Attribute11", attribute11), XMLElement("Attribute12", attribute12),
762 XMLElement("Attribute13", attribute13), XMLElement("Attribute14", attribute14),
763 XMLElement("Attribute15", attribute15))
764 INTO l_dffs
765 From iby_docs_payable_all idp
766 WHERE idp.document_payable_id = p_entity_id;
767
768 ELSE
769 Select XMLConcat(XMLElement("AttributeCategory", attribute_category),
770 XMLElement("Attribute1", TRANSLATE(attribute1,l_conc_invalid_chars,l_conc_replacement_chars)),
771 XMLElement("Attribute2", TRANSLATE(attribute2,l_conc_invalid_chars,l_conc_replacement_chars)),
772 XMLElement("Attribute3", TRANSLATE(attribute3,l_conc_invalid_chars,l_conc_replacement_chars)),
773 XMLElement("Attribute4", TRANSLATE(attribute4,l_conc_invalid_chars,l_conc_replacement_chars)),
774 XMLElement("Attribute5", TRANSLATE(attribute5,l_conc_invalid_chars,l_conc_replacement_chars)),
775 XMLElement("Attribute6", TRANSLATE(attribute6,l_conc_invalid_chars,l_conc_replacement_chars)),
776 XMLElement("Attribute7", TRANSLATE(attribute7,l_conc_invalid_chars,l_conc_replacement_chars)),
777 XMLElement("Attribute8", TRANSLATE(attribute8,l_conc_invalid_chars,l_conc_replacement_chars)),
778 XMLElement("Attribute9", TRANSLATE(attribute9,l_conc_invalid_chars,l_conc_replacement_chars)),
779 XMLElement("Attribute10", TRANSLATE(attribute10,l_conc_invalid_chars,l_conc_replacement_chars)),
780 XMLElement("Attribute11", TRANSLATE(attribute11,l_conc_invalid_chars,l_conc_replacement_chars)),
781 XMLElement("Attribute12", TRANSLATE(attribute12,l_conc_invalid_chars,l_conc_replacement_chars)),
782 XMLElement("Attribute13", TRANSLATE(attribute13,l_conc_invalid_chars,l_conc_replacement_chars)),
783 XMLElement("Attribute14", TRANSLATE(attribute14,l_conc_invalid_chars,l_conc_replacement_chars)),
784 XMLElement("Attribute15", TRANSLATE(attribute15,l_conc_invalid_chars,l_conc_replacement_chars)))
785 INTO l_dffs
786 From iby_docs_payable_all idp
787 WHERE idp.document_payable_id = p_entity_id;
788 END IF;
789 print_debuginfo (l_debug_module, 'After Execution the query for the table: '||G_DFF_AP_DOC);
790 ELSE
791 RETURN NULL;
792 END IF;
793 /* Bug 6763515*/
794 ELSE
795 RAISE FND_API.G_EXC_ERROR;
796 END IF;
797
798 IF (p_entity_table <> G_DFF_AP_DOC) THEN
799 l_queryString := 'Select XMLConcat(XMLElement("AttributeCategory", attribute_category), ';
800
801
802 FOR i in 1..l_num_of_attributes LOOP
803 --Fix for bug# 6141186
804 --Appended i with "Attribute"
805 IF(l_attribute_category IS NULL) THEN
806 l_queryString := l_queryString || 'XMLElement("Attribute'||i||'", attribute' || i ||')';
807 ELSE
808 l_queryString := l_queryString || 'XMLElement("Attribute'||i||'", TRANSLATE(attribute' || i ||','''||l_conc_invalid_chars||''','''||l_conc_replacement_chars||'''))';
809 END IF;
810
811 IF i < l_num_of_attributes THEN
812 l_queryString := l_queryString || ', ';
813 ELSE
814 l_queryString := l_queryString || ') ';
815 END IF;
816
817 END LOOP;
818
819 l_queryString := l_queryString || l_from_clause || l_where_clause;
820
821 print_debuginfo (l_debug_module, 'B4 Execution l_queryString : '||l_queryString);
822
823 IF p_entity_table = G_DFF_FD_PAYMENT_METHOD
824 or p_entity_table = G_DFF_FD_PAYMENT_PROFILE
825 or p_entity_table = G_DFF_FORMAT THEN
826 EXECUTE IMMEDIATE l_queryString INTO l_dffs using p_entity_code;
827 ELSIF p_entity_table = G_DFF_FD_PAY_INSTRUCTION
828 or p_entity_table = G_DFF_FD_PAY_SERVICE_REQ
829 or p_entity_table = G_DFF_FD_PAYMENT
830 or p_entity_table = G_DFF_AP_CHECK
831 or p_entity_table = G_DFF_FD_DOC_PAYABLE
832 or p_entity_table = G_DFF_BEP_ACCOUNT
833 or p_entity_table = G_DFF_LEGAL_ENTITY
834 or p_entity_table = G_DFF_PARTY
835 or p_entity_table = G_DFF_INT_BANK_ACCOUNT
836 or p_entity_table = G_DFF_EXT_BANK_ACCOUNT
837 or p_entity_table = G_DFF_PO_VENDORS
838 or p_entity_table = G_DFF_PO_VENDOR_SITES
839 or p_entity_table = G_DFF_PMT_INSTR_USES_ALL THEN
840 EXECUTE IMMEDIATE l_queryString INTO l_dffs using p_entity_id;
841 END IF;
842 END IF;
843
844 print_debuginfo(l_debug_module
845 , 'Exit:TIMESTAMP:: ' || l_debug_module||':: '||systimestamp
846 );
847 RETURN l_dffs;
848
849 EXCEPTION WHEN OTHERS THEN
850 print_debuginfo (l_debug_module, 'Error in fetching Get_Dffs: '||sqlerrm);
851 print_debuginfo (l_debug_module, 'l_queryString : '||l_queryString);
852 RAISE;
853 END Get_Dffs;
854
855 -- This function is general for the formatting of files
856 -- It allows passing parameters to the XDO template generator
857 -- Args: p_template_code. XDO template code
858 -- p_parameters_code. The code for the parameters we want to use in the
859 -- template during formatting
860 -- p_parameters_value. Value of the parameters
861 -- The 2 arrays should be defined with the same number of elements
862 PROCEDURE get_template_parameters
863 (
864 p_template_code IN iby_formats_b.format_template_code%TYPE,
865 p_pay_instruction IN VARCHAR2,
866 p_parameters_code OUT NOCOPY JTF_VARCHAR2_TABLE_200,
867 p_parameters_value OUT NOCOPY JTF_VARCHAR2_TABLE_200
868 ) IS
869
870 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.get_template_parameters';
871 l_filename fnd_concurrent_requests.outfile_name%TYPE;
872
873 BEGIN
874
875 -- initialize output parameters
876 p_parameters_code := JTF_VARCHAR2_TABLE_200();
877 p_parameters_value := JTF_VARCHAR2_TABLE_200();
878
879 IF (p_template_code = 'IBYAL_PT') THEN
880 -- get the filename and return in to be set as a template parameter
881
882 BEGIN
883 SELECT x.filename
884 INTO l_filename
885 FROM (SELECT SUBSTR(fcr.outfile_name,INSTR(fcr.outfile_name,'/',-1)+1) filename,
886 rank() over(partition by pi.payment_instruction_id order by pcc.request_id desc) c_rank
887 FROM iby_process_conc_requests pcc,
888 fnd_concurrent_requests fcr,
889 iby_pay_instructions_all pi,
890 iby_acct_pmt_profiles_b ap,
891 iby_sys_pmt_profiles_b sp,
892 iby_formats_b f
893 WHERE pcc.object_type = 'PAYMENT_INSTRUCTION'
894 AND pcc.request_id = fcr.request_id
895 AND pcc.object_id = pi.payment_instruction_id
896 AND ap.payment_profile_id = pi.payment_profile_id
897 AND ap.system_profile_code = sp.system_profile_code
898 AND f.format_code = sp.payment_format_code
899 AND f.format_template_code = p_template_code
900 AND pi.payment_instruction_id = TO_NUMBER(p_pay_instruction)) x
901 WHERE x.c_rank=1;
902
903 EXCEPTION
904 WHEN others THEN NULL;
905 END;
906
907 IF (l_filename IS NOT NULL) THEN
908 p_parameters_code.extend(1);
909 p_parameters_code(p_parameters_code.LAST) := 'P_FILENAME';
910
911 p_parameters_value.extend(1);
912 p_parameters_value(p_parameters_value.LAST) := l_filename;
913 END IF;
914
915 END IF;
916
917
918 END ;
919
920 FUNCTION Get_XML_Char_Encoding_Header
921 RETURN VARCHAR2
922 IS
923 l_encoding VARCHAR2(50);
924 l_xml_header VARCHAR2(1000);
925 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_XML_Char_Encoding_Header';
926
927 BEGIN
928
929 -- Bugs 8910544 and 8922250
930 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
931
932 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
933 print_debuginfo(l_module_name,'l_encoding : ' || l_encoding);
934 END IF;
935
936 IF((l_encoding IS NOT NULL) AND (LENGTH(l_encoding)>0)) THEN
937 l_xml_header := '<?xml version="'||'1.0'||'" encoding="'||l_encoding||'"?>';
938 ELSE
939 l_xml_header := '<?xml version="'||'1.0'||'" encoding="'||'UTF-8'||'"?>';
940 END IF;
941
942 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
943 print_debuginfo(l_module_name,'l_xml_header : ' || l_xml_header);
944 END IF;
945 RETURN l_xml_header;
946 END Get_XML_Char_Encoding_Header;
947
948 FUNCTION Is_Flex_Field_Enabled(p_flex_field_name varchar2, p_application_id number)
949 RETURN BOOLEAN
950 IS
951 l_cnt NUMBER;
952 BEGIN
953 IF g_flex_field_enabled.EXISTS(p_flex_field_name||'$'||p_application_id) THEN
954 RETURN g_flex_field_enabled(p_flex_field_name||'$'||p_application_id);
955 ELSE
956 SELECT count(1) into l_cnt
957 FROM FND_DESCR_FLEX_COLUMN_USAGES COL, FND_DESCR_FLEX_CONTEXTS CON
958 WHERE COL.DESCRIPTIVE_FLEXFIELD_NAME = p_flex_field_name and
959 col.application_id = p_application_id and
960 col.application_id = con.application_id and
961 con.descriptive_flexfield_name = col.descriptive_flexfield_name and
962 con.descriptive_flex_context_code = col.descriptive_flex_context_code and
963 con.enabled_flag = 'Y' and col.enabled_flag = 'Y';
964 IF l_cnt > 0 THEN
965 g_flex_field_enabled(p_flex_field_name||'$'||p_application_id):=TRUE;
966 ELSE
967 g_flex_field_enabled(p_flex_field_name||'$'||p_application_id):=FALSE;
968 END IF;
969 RETURN g_flex_field_enabled(p_flex_field_name||'$'||p_application_id);
970 END IF;
971 RETURN FALSE;
972 END Is_Flex_Field_Enabled;
973 BEGIN
974 FOR i in 1..32 LOOP
975 l_conc_invalid_chars :=l_conc_invalid_chars||fnd_global.local_chr(i-1);
976 l_conc_replacement_chars :=l_conc_replacement_chars||' ';
977 END LOOP;
978
979 END IBY_EXTRACTGEN_PVT;