[Home] [Help]
PACKAGE BODY: APPS.IBY_EXTRACTGEN_PVT
Source
1 PACKAGE BODY IBY_EXTRACTGEN_PVT AS
2 /* $Header: ibyxgenb.pls 120.33.12010000.6 2008/09/23 06:39:20 pschalla ship $ */
3
4 -- Global variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_EXTRACTGEN_PVT';
6
7 TYPE l_attribute_cat_rec_type IS RECORD(
8 l_hzp_attr_cat VARCHAR2(150)
9 );
10
11 TYPE l_att_cat_tbl_type IS TABLE OF l_attribute_cat_rec_type INDEX BY BINARY_INTEGER;
12
13 l_att_cat_tbl l_att_cat_tbl_type;
14
15 PROCEDURE print_debuginfo (
16 p_module IN VARCHAR2
17 , p_debug_text IN VARCHAR2)
18 IS
19 BEGIN
20 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
21 iby_debug_pub.add(p_debug_text,iby_debug_pub.G_LEVEL_INFO,p_module);
22 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_debug_text);
23 END IF;
24
25 END;
26 FUNCTION stripped_string (
27 expression_in IN VARCHAR2
28 ,characters_in IN VARCHAR2
29 ,placeholder_in IN VARCHAR2 DEFAULT '#'
30 )
31 RETURN VARCHAR2
32 IS
33 result_string VARCHAR2(3000);
34 BEGIN
35
36 result_string := TRANSLATE(SUBSTR(expression_in, 1, 1), placeholder_in || characters_in, placeholder_in) || --stripping first apos
37 SUBSTR(expression_in, 2, LENGTH(expression_in) -2) || --fetching unstripped
38 TRANSLATE(SUBSTR(expression_in, -1, 1), placeholder_in || characters_in, placeholder_in); --stripping last apos
39
40 IF ( upper(result_string) = 'NULL' ) THEN
41 result_string := NULL;
42 END IF;
43 RETURN result_string;
44 END stripped_string;
45
46 PROCEDURE Create_Extract
47 (
48 p_extract_code IN iby_extracts_vl.extract_code%TYPE,
49 p_extract_version IN iby_extracts_vl.extract_version%TYPE,
50 p_params IN OUT NOCOPY JTF_VARCHAR2_TABLE_200,
51 x_extract_doc OUT NOCOPY CLOB
52 )
53 IS
54
55 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Create_Extract';
56
57 l_code_pkg iby_extracts_b.gen_code_package%TYPE;
58 l_code_entry iby_extracts_b.gen_code_entry_point%TYPE;
59 l_call VARCHAR2(3000);
60 l_param_1 VARCHAR2(3000);
61 l_param_2 VARCHAR2(3000);
62 l_param_3 VARCHAR2(3000);
63 l_param_4 VARCHAR2(3000);
64 l_param_5 VARCHAR2(3000);
65 l_param_6 VARCHAR2(3000);
66 l_param_7 VARCHAR2(3000);
67 l_param_8 VARCHAR2(3000);
68
69 l_x_not_found BOOLEAN;
70 l_numeric_char_mask V$NLS_PARAMETERS.value%TYPE;
71 l_default_num_mask VARCHAR2(10) := '.,';
72
73
74 x_return_status VARCHAR2(200);
75
76 CURSOR c_x_info(ci_extract_code IN iby_extracts_b.extract_code%TYPE,
77 ci_extract_version IN iby_extracts_b.extract_version%TYPE)
78 IS
79 SELECT gen_code_package,gen_code_entry_point
80 FROM iby_extracts_vl
81 WHERE (extract_code=ci_extract_code)
82 AND (extract_version=ci_extract_version)
83 AND (gen_code_language='PLSQL');
84 BEGIN
85
86 -- Get NLS numeric character before calling extract.
87 -- bug 5604582
88 BEGIN
89 SELECT value
90 INTO l_numeric_char_mask
91 FROM V$NLS_PARAMETERS
92 WHERE parameter='NLS_NUMERIC_CHARACTERS';
93 EXCEPTION
94 WHEN others THEN NULL;
95 END;
96
97 IF (c_x_info%ISOPEN) THEN
98 CLOSE c_x_info;
99 END IF;
100
101 OPEN c_x_info(p_extract_code,p_extract_version);
102 FETCH c_x_info INTO l_code_pkg,l_code_entry;
103 l_x_not_found := c_x_info%NOTFOUND;
104 CLOSE c_x_info;
105
106 IF (l_x_not_found) THEN
107 raise_application_error(-20000,
108 'IBY_20590#TABLE=IBY_EXTRACTS_VL' || '#ID=' || p_extract_code,
109 FALSE);
110 END IF;
111 -- test_debug('l_code_pkg: '|| l_code_pkg);
112
113 /* Bug 6016869
114 */
115 IF ( upper(l_code_pkg) = 'IBY_FNDCPT_EXTRACT_GEN_PVT'
116 AND upper(l_code_entry) = 'CREATE_EXTRACT_1_0'
117 AND p_params.COUNT IN (4,5) -- Bug 6673696
118 AND p_extract_version = 1) THEN
119
120 IF ( p_params.COUNT = 4 OR p_params(5) = 'NULL') THEN
121
122 print_debuginfo(l_module_name
123 , 'Calling CREATE_EXTRACT_1_0 with 4 params:'
124 );
125 print_debuginfo(l_module_name
126 , '4 params:'
127 || p_params(1) || ':'
128 || p_params(2) || ':'
129 || p_params(3) || ':'
130 || p_params(4) || ':'
131 );
132 -- test_debug('p_params(1): '||p_params(1));
133 -- test_debug('p_params(4): '||p_params(4));
134 l_param_1 := stripped_string (p_params(1), '''');
135 l_param_2 := stripped_string (p_params(2), '''');
136 l_param_3 := stripped_string (p_params(3), '''');
137 l_param_4 := stripped_string (p_params(4), '''');
138
139 IF ( l_param_4 = 'NULL' ) THEN
140 l_param_4 := NULL;
141 END IF;
142
143 IBY_FNDCPT_EXTRACT_GEN_PVT.CREATE_EXTRACT_1_0 (
144 p_instr_type => l_param_1,
145 p_req_type => l_param_2,
146 p_txn_id => to_number(l_param_3),
147 p_sys_key => HEXTORAW(l_param_4),
148 x_extract_doc => x_extract_doc
149 );
150
151 -- test_debug('after create extract... ');
152
153
154 ELSE
155 print_debuginfo(l_module_name
156 , 'Calling CREATE_EXTRACT_1_0 with 5 params:'
157 );
158 print_debuginfo(l_module_name
159 , '5 params:'
160 || p_params(1) || ':'
161 || p_params(2) || ':'
162 || p_params(3) || ':'
163 || p_params(4) || ':'
164 || p_params(5) || ':'
165 );
166
167 l_param_1 := stripped_string (p_params(1), '''');
168 l_param_2 := stripped_string (p_params(2), '''');
169 l_param_3 := stripped_string (p_params(3), '''');
170 l_param_4 := stripped_string (p_params(4), '''');
171 l_param_5 := stripped_string (p_params(5), '''');
172
173 IF ( l_param_4 = 'NULL' ) THEN
174 l_param_4 := NULL;
175 END IF;
176
177 print_debuginfo(l_module_name, 'l_param_5 = ' || l_param_5 || ':');
178
179 IBY_FNDCPT_EXTRACT_GEN_PVT.CREATE_EXTRACT_1_0 (
180 p_instr_type => l_param_1,
181 p_req_type => l_param_2,
182 p_txn_id => to_number(l_param_3),
183 p_sys_key => HEXTORAW(l_param_4),
184 p_sec_val => l_param_5,
185 x_extract_doc => x_extract_doc
186 );
187 END IF;
188
189 ELSIF ( upper(l_code_pkg) ='IBY_FD_EXTRACT_GEN_PVT'
190 AND upper(l_code_entry) = 'CREATE_EXTRACT_1_0'
191 AND p_params.COUNT IN (5,6) -- Bug 6673696
192 AND p_extract_version = 1) THEN
193 BEGIN
194 IF ( p_params.COUNT = 5) THEN
195
196 print_debuginfo(l_module_name
197 , 'Calling CREATE_EXTRACT_1_0 with 5 params:'
198 );
199 print_debuginfo(l_module_name
200 , '5 params:'
201 || p_params(1) || ':'
202 || p_params(2) || ':'
203 || p_params(3) || ':'
204 || p_params(4) || ':'
205 || p_params(5) || ':'
206 );
207 l_param_1 := stripped_string (p_params(1), '''');
208 l_param_2 := stripped_string (p_params(2), '''');
209 l_param_3 := stripped_string (p_params(3), '''');
210 l_param_4 := stripped_string (p_params(4), '''');
211 l_param_5 := stripped_string (p_params(5), '''');
212
213 IF ( l_param_5 = 'NULL' ) THEN
214 l_param_5 := NULL;
215 END IF;
216
217 IBY_FD_EXTRACT_GEN_PVT.CREATE_EXTRACT_1_0 (
218 p_payment_instruction_id => to_number(l_param_1),
219 p_save_extract_flag => l_param_2,
220 p_format_type => l_param_3,
221 p_is_reprint_flag => l_param_4,
222 p_sys_key => HEXTORAW(l_param_5),
223 x_extract_doc => x_extract_doc
224 );
225 ELSE
226 print_debuginfo(l_module_name
227 , 'Calling CREATE_EXTRACT_1_0 with 6 params:'
228 );
229 print_debuginfo(l_module_name
230 , '6 params:'
231 || p_params(1) || ':'
232 || p_params(2) || ':'
233 || p_params(3) || ':'
234 || p_params(4) || ':'
235 || p_params(5) || ':'
236 || p_params(6) || ':'
237 );
238 l_param_1 := stripped_string (p_params(1), '''');
239 l_param_2 := stripped_string (p_params(2), '''');
240 l_param_3 := stripped_string (p_params(3), '''');
241 l_param_4 := stripped_string (p_params(4), '''');
242 l_param_5 := stripped_string (p_params(5), '''');
243 l_param_6 := stripped_string (p_params(6), '''');
244 print_debuginfo(l_module_name, 'l_param_6 = ' || l_param_6 || ':');
245 IF ( l_param_6 = 'NULL' ) THEN
246 l_param_6 := NULL;
247 END IF;
248
249 IBY_FD_EXTRACT_GEN_PVT.CREATE_EXTRACT_1_0 (
250 p_payment_instruction_id =>to_number(l_param_1),
251 p_save_extract_flag => l_param_2,
252 p_format_type => l_param_3,
253 p_delivery_method => l_param_4,
254 p_sys_key => HEXTORAW(l_param_6),
255 p_payment_id => to_number(l_param_5),
256 x_extract_doc => x_extract_doc
257 );
258 END IF;
259
260 EXCEPTION
261
262 WHEN OTHERS THEN
263 print_debuginfo(l_module_name, 'An exception has occured when creating extract.. Unlocking the payment instruction');
264 -- when exception occurs, the payment_instruction must be unlocked from the request
265
266 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
267 p_object_id => to_number(l_param_1),
268 p_object_type => 'PAYMENT_INSTRUCTION',
269 x_return_status => x_return_status
270 );
271
272 RAISE;
273 END;
274 ELSIF ( upper(l_code_pkg) = 'IBY_FD_EXTRACT_GEN_PVT'
275 AND upper(l_code_entry) = ' CREATE_PPR_EXTRACT_1_0'
276 AND p_params.COUNT = 2 -- Bug 6673696
277 AND p_extract_version = 1) THEN
278 print_debuginfo(l_module_name
279 , 'Calling CREATE_EXTRACT_1_0 with 2 params:'
280 );
281 print_debuginfo(l_module_name
282 , '2 params:'
283 || p_params(1) || ':'
284 || p_params(2) || ':'
285 );
286 l_param_1 := stripped_string (p_params(1), '''');
287 l_param_2 := stripped_string (p_params(2), '''');
288 IF ( l_param_2 = 'NULL' ) THEN
289 l_param_2 := NULL;
290 END IF;
291
292 IBY_FD_EXTRACT_GEN_PVT.CREATE_PPR_EXTRACT_1_0 (
293 p_payment_service_request_id => to_number(l_param_1),
294 p_sys_key => HEXTORAW(l_param_2),
295 x_extract_doc => x_extract_doc
296 );
297 ELSIF ( upper(l_code_pkg) = 'IBY_FD_EXTRACT_GEN_PVT'
298 AND upper(l_code_entry) = 'CREATE_POS_PAY_EXTRACT_1_0'
299 AND p_params.COUNT IN (5,8) -- Bug 6673696
300 AND p_extract_version = 1) THEN
301
302 IF ( p_params.COUNT = 5) THEN
303
304 print_debuginfo(l_module_name
305 , 'Calling CREATE_POS_PAY_EXTRACT_1_0 with 5 params:'
306 );
307 print_debuginfo(l_module_name
308 , '5 params:'
309 || p_params(1) || ':'
310 || p_params(2) || ':'
311 || p_params(3) || ':'
312 || p_params(4) || ':'
313 || p_params(5) || ':'
314 );
315 l_param_1 := stripped_string (p_params(1), '''');
316 l_param_2 := stripped_string (p_params(2), '''');
317 l_param_3 := stripped_string (p_params(3), '''');
318 l_param_4 := stripped_string (p_params(4), '''');
319 l_param_5 := stripped_string (p_params(5), '''');
320
321 IF ( l_param_5 = 'NULL' ) THEN
322 l_param_5 := NULL;
323 END IF;
324
325 IBY_FD_EXTRACT_GEN_PVT.CREATE_POS_PAY_EXTRACT_1_0 (
326 p_payment_instruction_id =>to_number(l_param_1),
327 p_payment_profile_id =>to_number(l_param_2),
328 p_from_date => l_param_3,
329 p_to_date => l_param_4,
330 p_sys_key => HEXTORAW(l_param_5),
331 x_extract_doc => x_extract_doc
332 );
333 ELSE
334
335 print_debuginfo(l_module_name
336 , 'Calling CREATE_POS_PAY_EXTRACT_2_0 with 8 params:'
337 );
338 print_debuginfo(l_module_name
339 , '8 params:'
340 || p_params(1) || ':'
341 || p_params(2) || ':'
342 || p_params(3) || ':'
343 || p_params(4) || ':'
344 || p_params(5) || ':'
345 || p_params(6) || ':'
346 || p_params(7) || ':'
347 || p_params(8) || ':'
348 );
349 l_param_1 := stripped_string (p_params(1), '''');
350 l_param_2 := stripped_string (p_params(2), '''');
351 l_param_3 := stripped_string (p_params(3), '''');
352 l_param_4 := stripped_string (p_params(4), '''');
353 l_param_5 := stripped_string (p_params(5), '''');
354 l_param_6 := stripped_string (p_params(6), '''');
355 l_param_7 := stripped_string (p_params(7), '''');
356 l_param_8 := stripped_string (p_params(8), '''');
357
358 IF ( upper(l_param_8) = 'NULL' ) THEN
359 l_param_8 := NULL;
360 END IF;
361
362 IBY_FD_EXTRACT_GEN_PVT.CREATE_POS_PAY_EXTRACT_2_0 (
363 p_payment_instruction_id =>to_number(l_param_1),
364 p_format_name => l_param_2,
365 p_internal_bank_account_name => l_param_3,
366 p_from_date => l_param_4,
367 p_to_date => l_param_5,
368 p_payment_status => l_param_6,
369 p_reselect => l_param_7,
370 p_sys_key => HEXTORAW(l_param_8),
371 x_extract_doc => x_extract_doc
372 );
373
374 END IF;
375 ELSE
376 /* End of Bug 6016869 */
377 -- provide the extract document OUT parameter
378 p_params.extend(1);
379 p_params(p_params.LAST) := null;
380
381 print_debuginfo(l_module_name, 'Calling Extract Dynamically');
382 print_debuginfo(l_module_name, 'p_params.COUNT' || p_params.COUNT );
383 print_debuginfo(l_module_name, 'l_code_entry: ' || l_code_entry);
384 print_debuginfo(l_module_name, 'l_code_pkg: ' || l_code_pkg);
385
386 -- alter session so that the numeric characters are set to decimal separatori(.)
387 -- if different
388 -- bug 5604582
389 IF l_numeric_char_mask <> l_default_num_mask THEN
390 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='||'"'||l_default_num_mask||'"';
391 END IF;
392
393 l_call := iby_utility_pvt.get_call_exec(l_code_pkg,l_code_entry,p_params);
394 --dbms_output.put_line('call:=<' || l_call || '>');
395
396 EXECUTE IMMEDIATE l_call USING OUT x_extract_doc;
397
398 IF l_numeric_char_mask <> l_default_num_mask THEN
399 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '||
400 '"'||l_numeric_char_mask|| '"';
401 END IF;
402
403 END IF;
404 END create_extract;
405
406
407 FUNCTION Get_Dffs(p_entity_table IN VARCHAR2, p_entity_id IN NUMBER, p_entity_code IN VARCHAR2)
408 RETURN XMLTYPE
409 IS
410 l_dffs XMLTYPE;
411 l_queryString VARCHAR2(4000); -- Bug 6827266
412 l_num_of_attributes NUMBER;
413 l_from_clause VARCHAR2(512);
414 l_where_clause VARCHAR2(2000); -- Bug 6827266
415 l_ap_attr_cat VARCHAR2(150);
416 l_hzp_attr_cat VARCHAR2(150);
417 l_attribute_category VARCHAR2(150);
418 l_conc_invalid_chars VARCHAR2(50);
419 l_conc_replacement_chars VARCHAR2(50);
420
421 /* Removed the cursor l_ap_doc_attr_cat_csr for the bug Bug 6763515*/
422
423
424 CURSOR l_hzp_attr_cat_csr (p_party_id IN NUMBER) IS
425 SELECT attribute_category
426 FROM hz_parties
427 WHERE party_id = p_party_id;
428
429 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Dffs';
430 BEGIN
431 /* Preparing the concatinated strings of invalid characters
432 and corresponding replacement characters. */
433 FOR i in 1..32 LOOP
434 l_conc_invalid_chars :=l_conc_invalid_chars||fnd_global.local_chr(i-1);
435 l_conc_replacement_chars :=l_conc_replacement_chars||' ';
436 END LOOP;
437
438 IF p_entity_id IS NULL AND p_entity_code IS NULL THEN
439 RETURN NULL;
440 END IF;
441
442 print_debuginfo (l_debug_module, 'p_entity_table : '||p_entity_table);
443 print_debuginfo (l_debug_module, 'p_entity_id : '||p_entity_id);
444 print_debuginfo (l_debug_module, 'p_entity_code : '||p_entity_code);
445
446 IF p_entity_table = G_DFF_FD_PAYMENT_METHOD THEN
447 l_num_of_attributes := 15;
448 l_from_clause := ' From ' || p_entity_table;
449 l_where_clause := ' Where payment_method_code = :p_entity_code ';
450 SELECT attribute_category
451 INTO l_attribute_category
452 FROM IBY_PAYMENT_METHODS_B
453 WHERE payment_method_code = p_entity_code;
454
455 ELSIF p_entity_table = G_DFF_FD_PAYMENT_PROFILE THEN
456 l_num_of_attributes := 15;
457 l_from_clause := ' From ' || p_entity_table;
458 l_where_clause := ' Where system_profile_code = :p_entity_code ';
459 SELECT attribute_category
460 INTO l_attribute_category
461 FROM IBY_SYS_PMT_PROFILES_B
462 WHERE system_profile_code = p_entity_code;
463
464 ELSIF p_entity_table = G_DFF_FD_PAY_INSTRUCTION THEN
465 l_num_of_attributes := 15;
466 l_from_clause := ' From ' || p_entity_table;
467 l_where_clause := ' Where payment_instruction_id = :p_entity_id ';
468 SELECT attribute_category
469 INTO l_attribute_category
470 FROM IBY_PAY_INSTRUCTIONS_ALL
471 WHERE payment_instruction_id = p_entity_id;
472
473 ELSIF p_entity_table = G_DFF_FD_PAYMENT THEN
474 l_num_of_attributes := 15;
475 l_from_clause := ' From ' || p_entity_table;
476 l_where_clause := ' Where payment_id = :p_entity_id ';
477 SELECT attribute_category
478 INTO l_attribute_category
479 FROM IBY_PAYMENTS_ALL
480 WHERE payment_id = p_entity_id;
481
482 ELSIF p_entity_table = G_DFF_FD_DOC_PAYABLE THEN
483 l_num_of_attributes := 15;
484 l_from_clause := ' From ' || p_entity_table;
485 l_where_clause := ' Where document_payable_id = :p_entity_id ';
486 SELECT attribute_category
487 INTO l_attribute_category
488 FROM IBY_DOCS_PAYABLE_ALL
489 WHERE document_payable_id = p_entity_id;
490
491 ELSIF p_entity_table = G_DFF_FORMAT THEN
492 l_num_of_attributes := 15;
493 l_from_clause := ' From ' || p_entity_table;
494 l_where_clause := ' Where format_code = :p_entity_code ';
495 SELECT attribute_category
496 INTO l_attribute_category
497 FROM IBY_FORMATS_B
498 WHERE format_code = p_entity_code;
499
500 ELSIF p_entity_table = G_DFF_BEP_ACCOUNT THEN
501 l_num_of_attributes := 15;
502 l_from_clause := ' From ' || p_entity_table;
503 l_where_clause := ' Where bep_account_id = :p_entity_id ';
504 SELECT attribute_category
505 INTO l_attribute_category
506 FROM IBY_BEPKEYS
507 WHERE bep_account_id = p_entity_id;
508
509 ELSIF p_entity_table = G_DFF_LEGAL_ENTITY THEN
510 l_num_of_attributes := 20;
511 l_from_clause := ' From ' || p_entity_table;
512 l_where_clause := ' Where legal_entity_id = :p_entity_id ';
513 SELECT attribute_category
514 INTO l_attribute_category
515 FROM XLE_FIRSTPARTY_INFORMATION_V
516 WHERE legal_entity_id = p_entity_id;
517
518 ELSIF p_entity_table = G_DFF_PARTY THEN
519
520 IF (NOT(l_att_cat_tbl.EXISTS(p_entity_id))) THEN
521 OPEN l_hzp_attr_cat_csr (p_entity_id);
522 FETCH l_hzp_attr_cat_csr INTO l_att_cat_tbl(p_entity_id).l_hzp_attr_cat;
523 CLOSE l_hzp_attr_cat_csr;
524 END IF;
525
526 IF l_att_cat_tbl(p_entity_id).l_hzp_attr_cat IS NULL THEN
527 RETURN NULL;
528 END IF;
529
530 l_num_of_attributes := 24;
531 l_from_clause := ' From ' || p_entity_table;
532 l_where_clause := ' Where party_id = :p_entity_id ';
533 SELECT attribute_category
534 INTO l_attribute_category
535 FROM HZ_PARTIES
536 WHERE party_id = p_entity_id;
537
538 ELSIF p_entity_table = G_DFF_INT_BANK_ACCOUNT THEN
539 l_num_of_attributes := 15;
540 l_from_clause := ' From ' || p_entity_table;
541 l_where_clause := ' Where bank_account_id = :p_entity_id ';
542 SELECT attribute_category
543 INTO l_attribute_category
544 FROM CE_BANK_ACCOUNTS
545 WHERE bank_account_id = p_entity_id;
546
547 ELSIF p_entity_table = G_DFF_EXT_BANK_ACCOUNT THEN
548 l_num_of_attributes := 15;
549 l_from_clause := ' From ' || p_entity_table;
550 l_where_clause := ' Where ext_bank_account_id = :p_entity_id ';
551 SELECT attribute_category
552 INTO l_attribute_category
553 FROM IBY_EXT_BANK_ACCOUNTS
554 WHERE ext_bank_account_id = p_entity_id;
555
556 ELSIF p_entity_table = G_DFF_PO_VENDORS THEN
557 l_num_of_attributes := 15;
558 l_from_clause := ' From ' || p_entity_table;
559 l_where_clause := ' Where VENDOR_ID = :p_entity_id ';
560 SELECT attribute_category
561 INTO l_attribute_category
562 FROM PO_VENDORS
563 WHERE VENDOR_ID = p_entity_id;
564
565 ELSIF p_entity_table = G_DFF_PO_VENDOR_SITES THEN
566 l_num_of_attributes := 15;
567 l_from_clause := ' From ' || p_entity_table;
568 l_where_clause := ' Where VENDOR_SITE_ID = :p_entity_id ';
569 SELECT attribute_category
570 INTO l_attribute_category
571 FROM PO_VENDOR_SITES_ALL
572 WHERE VENDOR_SITE_ID = p_entity_id;
573
574 ELSIF p_entity_table = G_DFF_AP_DOC THEN
575
576 /* Bug 6763515*/
577 print_debuginfo (l_debug_module, 'Before Execution the query for the table:'||G_DFF_AP_DOC);
578
579 SELECT attribute_category
580 INTO l_attribute_category
581 FROM iby_docs_payable_all idp
582 WHERE idp.document_payable_id = p_entity_id;
583
584 IF(l_attribute_category IS NULL) THEN
585 Select XMLConcat(XMLElement("AttributeCategory", attribute_category),
586 XMLElement("Attribute1", attribute1), XMLElement("Attribute2", attribute2),
587 XMLElement("Attribute3", attribute3), XMLElement("Attribute4", attribute4),
588 XMLElement("Attribute5", attribute5), XMLElement("Attribute6", attribute6),
589 XMLElement("Attribute7", attribute7), XMLElement("Attribute8", attribute8),
590 XMLElement("Attribute9", attribute9), XMLElement("Attribute10", attribute10),
591 XMLElement("Attribute11", attribute11), XMLElement("Attribute12", attribute12),
592 XMLElement("Attribute13", attribute13), XMLElement("Attribute14", attribute14),
593 XMLElement("Attribute15", attribute15))
594 INTO l_dffs
595 From iby_docs_payable_all idp
596 WHERE idp.document_payable_id = p_entity_id;
597
598 ELSE
599 Select XMLConcat(XMLElement("AttributeCategory", attribute_category),
600 XMLElement("Attribute1", TRANSLATE(attribute1,l_conc_invalid_chars,l_conc_replacement_chars)),
601 XMLElement("Attribute2", TRANSLATE(attribute2,l_conc_invalid_chars,l_conc_replacement_chars)),
602 XMLElement("Attribute3", TRANSLATE(attribute3,l_conc_invalid_chars,l_conc_replacement_chars)),
603 XMLElement("Attribute4", TRANSLATE(attribute4,l_conc_invalid_chars,l_conc_replacement_chars)),
604 XMLElement("Attribute5", TRANSLATE(attribute5,l_conc_invalid_chars,l_conc_replacement_chars)),
605 XMLElement("Attribute6", TRANSLATE(attribute6,l_conc_invalid_chars,l_conc_replacement_chars)),
606 XMLElement("Attribute7", TRANSLATE(attribute7,l_conc_invalid_chars,l_conc_replacement_chars)),
607 XMLElement("Attribute8", TRANSLATE(attribute8,l_conc_invalid_chars,l_conc_replacement_chars)),
608 XMLElement("Attribute9", TRANSLATE(attribute9,l_conc_invalid_chars,l_conc_replacement_chars)),
609 XMLElement("Attribute10", TRANSLATE(attribute10,l_conc_invalid_chars,l_conc_replacement_chars)),
610 XMLElement("Attribute11", TRANSLATE(attribute11,l_conc_invalid_chars,l_conc_replacement_chars)),
611 XMLElement("Attribute12", TRANSLATE(attribute12,l_conc_invalid_chars,l_conc_replacement_chars)),
612 XMLElement("Attribute13", TRANSLATE(attribute13,l_conc_invalid_chars,l_conc_replacement_chars)),
613 XMLElement("Attribute14", TRANSLATE(attribute14,l_conc_invalid_chars,l_conc_replacement_chars)),
614 XMLElement("Attribute15", TRANSLATE(attribute15,l_conc_invalid_chars,l_conc_replacement_chars)))
615 INTO l_dffs
616 From iby_docs_payable_all idp
617 WHERE idp.document_payable_id = p_entity_id;
618 END IF;
619 print_debuginfo (l_debug_module, 'After Execution the query for the table: '||G_DFF_AP_DOC);
620
621 /* Bug 6763515*/
622 ELSE
623 RAISE FND_API.G_EXC_ERROR;
624 END IF;
625
626 IF (p_entity_table <> G_DFF_AP_DOC) THEN
627 l_queryString := 'Select XMLConcat(XMLElement("AttributeCategory", attribute_category), ';
628
629
630 FOR i in 1..l_num_of_attributes LOOP
631 --Fix for bug# 6141186
632 --Appended i with "Attribute"
633 IF(l_attribute_category IS NULL) THEN
634 l_queryString := l_queryString || 'XMLElement("Attribute'||i||'", attribute' || i ||')';
635 ELSE
636 l_queryString := l_queryString || 'XMLElement("Attribute'||i||'", TRANSLATE(attribute' || i ||','''||l_conc_invalid_chars||''','''||l_conc_replacement_chars||'''))';
637 END IF;
638
639 IF i < l_num_of_attributes THEN
640 l_queryString := l_queryString || ', ';
641 ELSE
642 l_queryString := l_queryString || ') ';
643 END IF;
644
645 END LOOP;
646
647 l_queryString := l_queryString || l_from_clause || l_where_clause;
648
649 print_debuginfo (l_debug_module, 'B4 Execution l_queryString : '||l_queryString);
650
651 IF p_entity_table = G_DFF_FD_PAYMENT_METHOD
652 or p_entity_table = G_DFF_FD_PAYMENT_PROFILE
653 or p_entity_table = G_DFF_FORMAT THEN
654 EXECUTE IMMEDIATE l_queryString INTO l_dffs using p_entity_code;
655 ELSIF p_entity_table = G_DFF_FD_PAY_INSTRUCTION
656 or p_entity_table = G_DFF_FD_PAYMENT
657 or p_entity_table = G_DFF_FD_DOC_PAYABLE
658 or p_entity_table = G_DFF_BEP_ACCOUNT
659 or p_entity_table = G_DFF_LEGAL_ENTITY
660 or p_entity_table = G_DFF_PARTY
661 or p_entity_table = G_DFF_INT_BANK_ACCOUNT
662 or p_entity_table = G_DFF_EXT_BANK_ACCOUNT
663 or p_entity_table = G_DFF_PO_VENDORS
664 or p_entity_table = G_DFF_PO_VENDOR_SITES THEN
665 EXECUTE IMMEDIATE l_queryString INTO l_dffs using p_entity_id;
666 END IF;
667 END IF;
668 RETURN l_dffs;
669
670 EXCEPTION WHEN OTHERS THEN
671 print_debuginfo (l_debug_module, 'Error in fetching Get_Dffs: '||sqlerrm);
672 print_debuginfo (l_debug_module, 'l_queryString : '||l_queryString);
673 RAISE;
674 END Get_Dffs;
675
676 -- This function is general for the formatting of files
677 -- It allows passing parameters to the XDO template generator
678 -- Args: p_template_code. XDO template code
679 -- p_parameters_code. The code for the parameters we want to use in the
680 -- template during formatting
681 -- p_parameters_value. Value of the parameters
682 -- The 2 arrays should be defined with the same number of elements
683 PROCEDURE get_template_parameters
684 (
685 p_template_code IN iby_formats_b.format_template_code%TYPE,
686 p_pay_instruction IN VARCHAR2,
687 p_parameters_code OUT NOCOPY JTF_VARCHAR2_TABLE_200,
688 p_parameters_value OUT NOCOPY JTF_VARCHAR2_TABLE_200
689 ) IS
690
691 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.get_template_parameters';
692 l_filename fnd_concurrent_requests.outfile_name%TYPE;
693
694 BEGIN
695
696 -- initialize output parameters
697 p_parameters_code := JTF_VARCHAR2_TABLE_200();
698 p_parameters_value := JTF_VARCHAR2_TABLE_200();
699
700 IF (p_template_code = 'IBYAL_PT') THEN
701 -- get the filename and return in to be set as a template parameter
702
703 BEGIN
704 SELECT x.filename
705 INTO l_filename
706 FROM (SELECT SUBSTR(fcr.outfile_name,INSTR(fcr.outfile_name,'/',-1)+1) filename,
707 rank() over(partition by pi.payment_instruction_id order by pcc.request_id desc) c_rank
708 FROM iby_process_conc_requests pcc,
709 fnd_concurrent_requests fcr,
710 iby_pay_instructions_all pi,
711 iby_acct_pmt_profiles_b ap,
712 iby_sys_pmt_profiles_b sp,
713 iby_formats_b f
714 WHERE pcc.object_type = 'PAYMENT_INSTRUCTION'
715 AND pcc.request_id = fcr.request_id
716 AND pcc.object_id = pi.payment_instruction_id
717 AND ap.payment_profile_id = pi.payment_profile_id
718 AND ap.system_profile_code = sp.system_profile_code
719 AND f.format_code = sp.payment_format_code
720 AND f.format_template_code = p_template_code
721 AND pi.payment_instruction_id = TO_NUMBER(p_pay_instruction)) x
722 WHERE x.c_rank=1;
723
724 EXCEPTION
725 WHEN others THEN NULL;
726 END;
727
728 IF (l_filename IS NOT NULL) THEN
729 p_parameters_code.extend(1);
730 p_parameters_code(p_parameters_code.LAST) := 'P_FILENAME';
731
732 p_parameters_value.extend(1);
733 p_parameters_value(p_parameters_value.LAST) := l_filename;
734 END IF;
735
736 END IF;
737
738
739 END ;
740
741
742 END IBY_EXTRACTGEN_PVT;