[Home] [Help]
PACKAGE BODY: APPS.IBY_FD_EXTRACT_GEN_PVT
Source
1 PACKAGE BODY IBY_FD_EXTRACT_GEN_PVT AS
2 /* $Header: ibyfdxgb.pls 120.139.12020000.5 2013/03/04 11:34:10 jnallam ship $ */
3
4
5 --============================================================================
6 -- This package has the following dependencies (compilation) to apps products:
7 -- AP:
8 -- 1. AP_DOCUMENT_LINES_V. Referenced by function: Get_Doc_DocLineAgg()
9 --
10 -- FV:
11 -- 1. FV_FEDERAL_PAYMENT_FIELDS_PKG.get_FEIN(payment_instruction_id).
12 -- Referenced by the same named function in this package.
13 -- 2. FV_FEDERAL_PAYMENT_FIELDS_PKG.get_Abbreviated_Agency_Code(payment_instruction_id)
14 -- Referenced by the same named function in this package.
15 -- 3. FV_FEDERAL_PAYMENT_FIELDS_PKG.get_Allotment_Code(payment_id)
16 -- Referenced by the same named function in this package.
17 -- 4. FV_FEDERAL_PAYMENT_FIELDS_PKG.TOP_Offset_Eligibility_Flag(payment_id)
18 -- Referenced by the same named function in this package.
19 --============================================================================
20
21 G_SRA_DELIVERY_METHOD_ATTR CONSTANT NUMBER := 1;
22 G_SRA_EMAIL_ATTR CONSTANT NUMBER := 2;
23 G_SRA_FAX_ATTR CONSTANT NUMBER := 3;
24 G_SRA_REQ_FLAG_ATTR CONSTANT NUMBER := 4;
25 G_SRA_PS_LANG_ATTR CONSTANT NUMBER := 5;
26 G_SRA_PS_TERRITORY_ATTR CONSTANT NUMBER := 6;
27 G_SRA_EMAIL_SUBJECT_ATTR CONSTANT NUMBER := 7;
28
29 G_SRA_DELIVERY_METHOD_PRINTED CONSTANT VARCHAR2(30) := 'PRINTED';
30 G_SRA_DELIVERY_METHOD_EMAIL CONSTANT VARCHAR2(30) := 'EMAIL';
31 G_SRA_DELIVERY_METHOD_FAX CONSTANT VARCHAR2(30) := 'FAX';
32
33 G_EXTRACT_MODE_PMT CONSTANT NUMBER := 1;
34 G_EXTRACT_MODE_SRA CONSTANT NUMBER := 2;
35 G_EXTRACT_MODE_AUX CONSTANT NUMBER := 3;
36 G_EXTRACT_MODE_FV_SMMY CONSTANT NUMBER := 4;
37 G_EXTRACT_MODE_PI_RPT CONSTANT NUMBER := 5;
38 G_EXTRACT_MODE_PPR_RPT CONSTANT NUMBER := 6;
39 G_EXTRACT_MODE_AMEX_REM CONSTANT NUMBER := 7;
40
41 G_Extract_Run_Mode NUMBER;
42 G_Extract_Run_Delivery_Method VARCHAR2(30);
43 G_Extract_Run_Payment_id NUMBER;
44 G_Extract_Run_From_Pmt_Ref NUMBER;
45 G_Extract_Run_To_Pmt_Ref NUMBER;
46
47 G_May_Need_HR_Masking BOOLEAN := FALSE;
48
49 G_Is_Reprint VARCHAR2(1);
50
51 G_IS_BRAZIL NUMBER;
52
53 -- temp debugging
54 Get_Payee_LegalRegistration_C number := 0;
55 Get_Payee_TaxRegistration_C number := 0;
56 Get_FP_TaxRegistration_C number := 0;
57 Get_PayeeContact_C number := 0;
58 Get_PayerContact_C number := 0;
59 Get_PayerBankAccount_C number := 0;
60 Get_Payer_C number := 0;
61 Get_Hz_Address_C NUMBER := 0;
62 Get_Hr_Address_C NUMBER := 0;
63 format_hz_address_C NUMBER := 0;
64 format_hr_address_C NUMBER := 0;
65
66 /* perf bug 6763515 */
67 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
68 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
69 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
70 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
71 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
72 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
73 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
74
75
76 -- Performance Fix : 9184059
77 l_conc_invalid_chars VARCHAR2(50);
78 l_conc_replacement_chars VARCHAR2(50);
79
80 TYPE t_docs_pay_attribs_type IS RECORD(
81 IS_BRAZIL NUMBER
82 );
83
84 TYPE t_docs_pay_attribs_tbl_type IS TABLE OF t_docs_pay_attribs_type INDEX BY BINARY_INTEGER;
85 g_docs_pay_attribs_tbl t_docs_pay_attribs_tbl_type;
86
87 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
88 Removing the storing of XMLTYPE in cache.
89 */
90
91 TYPE t_hr_addr_rec_type IS RECORD(
92 location_id NUMBER(15),
93 address1 VARCHAR2(240),
94 address2 VARCHAR2(240),
95 address3 VARCHAR2(240),
96 town_or_city VARCHAR2(60),
97 county VARCHAR2(60),
98 state VARCHAR2(60),
99 country VARCHAR2(60),
100 iso_territory_code VARCHAR2(60),
101 territory_short_name VARCHAR2(200),
102 postal_code VARCHAR2(60),
103 concat_addr VARCHAR2(4000),
104 mail_addr VARCHAR2(4000),
105 location_code VARCHAR2(240)
106 );
107
108 TYPE t_hr_addr_tbl_type IS TABLE OF t_hr_addr_rec_type INDEX BY BINARY_INTEGER;
109 g_hr_addr_tbl t_hr_addr_tbl_type;
110
111 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
112 Removing the storing of XMLTYPE in cache.
113 */
114
115 /* TYPE t_hz_addr_rec_type IS RECORD(
116 hz_address XMLTYPE
117 ); */
118
119 TYPE t_hz_addr_rec_type IS RECORD(
120 location_id NUMBER(15),
121 address1 VARCHAR2(240),
122 address2 VARCHAR2(240),
123 address3 VARCHAR2(240),
124 address4 VARCHAR2(240),
125 city VARCHAR2(60),
126 county VARCHAR2(60),
127 state VARCHAR2(60),
128 province VARCHAR2(60),
129 country VARCHAR2(60),
130 iso_territory_code VARCHAR2(60),
131 territory_short_name VARCHAR2(200),
132 postal_code VARCHAR2(60),
133 concat_addr VARCHAR2(4000),
134 mail_addr VARCHAR2(4000)
135 );
136
137 TYPE t_hz_addr_tbl_type IS TABLE OF t_hz_addr_rec_type INDEX BY BINARY_INTEGER;
138 g_hz_addr_tbl t_hz_addr_tbl_type;
139
140 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
141 Removing the storing of XMLTYPE in cache.
142 */
143
144 TYPE t_payer_contact_rec_type IS RECORD(
145 phone_cp_id NUMBER,
146 fax_cp_id NUMBER,
147 email VARCHAR2(2000),
148 url VARCHAR2(2000),
149 hr_loc_phone VARCHAR2(60),
150 hr_loc_fax VARCHAR2(60)
151 );
152
153 TYPE t_payer_contact_tbl_type IS TABLE OF t_payer_contact_rec_type INDEX BY BINARY_INTEGER;
154
155 g_payer_contact_tbl t_payer_contact_tbl_type;
156
157 /* Bug 8670295 */
158 /* added for caching the account address*/
159
160 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
161 Removing the storing of XMLTYPE in cache.
162 */
163 TYPE t_account_addr_rec_type IS RECORD(
164 iso_territory_code VARCHAR2(50),
165 territory_short_name VARCHAR2(50)
166 );
167 TYPE t_account_addr_tbl_type IS TABLE OF t_account_addr_rec_type INDEX BY VARCHAR2(2000);
168 g_account_addr_tbl t_account_addr_tbl_type;
169
170 /* added for caching the formatted hz address*/
171
172 TYPE t_formatted_hz_addr_rec_type IS RECORD(
173 formatted_address VARCHAR2(4000)
174 );
175 TYPE t_formatted_hz_addr_tbl_type IS TABLE OF t_formatted_hz_addr_rec_type INDEX BY VARCHAR2(2000);
176 g_formatted_hz_addr_tbl t_formatted_hz_addr_tbl_type;
177 /* end of caching the formatted hz address*/
178
179 /* Added for caching the Registration Number */
180 TYPE t_registration_rec_type IS RECORD(
181 registration_number VARCHAR2(100)
182 );
183 TYPE t_registration_tbl_type IS TABLE OF t_registration_rec_type INDEX BY VARCHAR2(2000);
184 g_registration_tbl t_registration_tbl_type;
185 /* Bug 8670295 */
186
187 /* Added for caching Payer's Tax Registration Number */
188 TYPE t_payer_registration_rec_type IS RECORD(
189 registration_number VARCHAR2(100)
190 );
191 TYPE t_payer_registration_tbl_type IS TABLE OF t_payer_registration_rec_type INDEX BY VARCHAR2(2000);
192 g_payer_registration_tbl t_payer_registration_tbl_type;
193 /* Bug 8760084 */
194
195 /* Bug 9266772*/
196 /* TYPE t_inter_acct_tbl_type IS TABLE OF XMLTYPE INDEX BY varchar2(1000);
197 g_inter_accts_tbl t_inter_acct_tbl_type; */
198 /* Bug 9266772*/
199 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
200 Removing the storing of XMLTYPE in cache.
201 */
202
203 TYPE t_inter_acct_rec_type IS RECORD(
204 Intermediary_acct_id NUMBER(15),
205 Account_Number VARCHAR2(100),
206 IBAN VARCHAR2(50),
207 Bank_Name VARCHAR2(360),
208 Bank_code VARCHAR2(30),
209 BRANCH_NUMBER VARCHAR2(30),
210 BIC VARCHAR2(30),
211 check_digits VARCHAR2(30),
212 city VARCHAR2(60),
213 country_code VARCHAR2(2),
214 comments VARCHAR2(240)
215 );
216
217 TYPE t_inter_acct_tbl_type IS TABLE OF t_inter_acct_rec_type INDEX BY varchar2(1000);
218 g_inter_accts_tbl t_inter_acct_tbl_type;
219
220 /*Bug 11923557 */
221 TYPE t_instr_assign_tbl_type IS TABLE OF XMLTYPE INDEX BY BINARY_INTEGER;
222 g_instr_assign_tbl t_instr_assign_tbl_type;
223 /*Bug 11923557 */
224
225 FUNCTION Get_Payee_Default_Attribute(p_payment_id IN NUMBER, p_attribute_type IN NUMBER)
226 RETURN VARCHAR2;
227
228
229 PROCEDURE print_log(
230 p_module IN VARCHAR2,
231 p_debug_text IN VARCHAR2
232 )
233 IS
234 l_default_debug_level NUMBER := FND_LOG.LEVEL_STATEMENT;
235 BEGIN
236
237
238
239 /*
240 * Write the debug message to the concurrent manager log file.
241 */
242 IBY_DEBUG_PUB.log(p_module, p_debug_text,l_default_debug_level);
243
244
245 END print_log;
246
247 PROCEDURE Create_Extract_1_0_Main
248 (
249 p_payment_instruction_id IN NUMBER,
250 p_save_extract_flag IN VARCHAR2,
251 p_sys_key IN iby_security_pkg.des3_key_type,
252 x_extract_doc OUT NOCOPY CLOB
253 );
254
255 PROCEDURE Validate_and_Set_Syskey
256 (
257 p_sys_key IN iby_security_pkg.des3_key_type
258 );
259
260 -- for payment format
261 PROCEDURE Create_Extract_1_0
262 (
263 p_payment_instruction_id IN NUMBER,
264 p_save_extract_flag IN VARCHAR2,
265 p_format_type IN VARCHAR2,
266 p_is_reprint_flag IN VARCHAR2,
267 p_sys_key IN iby_security_pkg.des3_key_type,
268 x_extract_doc OUT NOCOPY CLOB
269 )
270 IS
271 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Extract_1_0';
272
273 BEGIN
274 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
275 iby_debug_pub.add(debug_msg => 'Input p_is_reprint_flag: ' || p_is_reprint_flag,
276 debug_level => G_LEVEL_STATEMENT,
277 module => l_Debug_Module);
278 END IF;
279
280 G_Is_Reprint := p_is_reprint_flag;
281
282 -- call the one for aux formats
283 Create_Extract_1_0
284 (
285 p_payment_instruction_id => p_payment_instruction_id,
286 p_save_extract_flag => p_save_extract_flag,
287 p_format_type => p_format_type,
288 p_sys_key => p_sys_key,
289 x_extract_doc => x_extract_doc
290 );
291
292 END Create_Extract_1_0;
293
294 -- for payment and auxiliary formats
295 -- Auxiliary formats include:
296 -- DISBURSEMENT_ACCOMPANY_LETTER
297 -- REGULATORY_REPORTING
298 -- REMITTANCE_ADVICE
299 -- PAYMENT_INSTRUCTION_REGISTER
300 -- FEDERAL_SUMMARY
301 -- for FEDERAL_SUMMARY, the p_save_extract_flag
302 -- is used for the ecs_dos_seq_num
303 PROCEDURE Create_Extract_1_0
304 (
305 p_payment_instruction_id IN NUMBER,
306 p_save_extract_flag IN VARCHAR2,
307 p_format_type IN VARCHAR2,
308 p_sys_key IN iby_security_pkg.des3_key_type,
309 x_extract_doc OUT NOCOPY CLOB
310 )
311 IS
312 l_extract_count NUMBER;
313 l_trxn_doc_id NUMBER;
314 l_save_extract_flag VARCHAR2(255);
315 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Extract_1_0';
316
317 BEGIN
318 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
319 debug_level => G_LEVEL_PROCEDURE,
320 module => l_Debug_Module);
321 iby_debug_pub.log(l_Debug_Module,'Enter:TIMESTAMP::' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
322
323
324 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
325
326 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
327 debug_level => G_LEVEL_PROCEDURE,
328 module => l_Debug_Module);
329
330 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
331 debug_level => G_LEVEL_STATEMENT,
332 module => l_Debug_Module);
333 END IF;
334
335 IF p_format_type = 'FEDERAL_SUMMARY' THEN
336 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
337 iby_debug_pub.add(debug_msg => 'ECS dos Seq num: ' || p_save_extract_flag,
338 debug_level => G_LEVEL_STATEMENT,
339 module => l_Debug_Module);
340 END IF;
341 ELSE
342 l_save_extract_flag := p_save_extract_flag;
343 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
344 iby_debug_pub.add(debug_msg => 'Save extract flag: ' || p_save_extract_flag,
345 debug_level => G_LEVEL_STATEMENT,
346 module => l_Debug_Module);
347 END IF;
348 END IF;
349 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
350 iby_debug_pub.add(debug_msg => 'Format type: ' || p_format_type,
351 debug_level => G_LEVEL_STATEMENT,
352 module => l_Debug_Module);
353 END IF;
354
355 -- for now there are no differences between different type
356 -- of aux formats in terms of extract. So only differenciate
357 -- them from payment format
358 IF p_format_type = 'OUTBOUND_PAYMENT_INSTRUCTION' THEN
359 G_Extract_Run_Mode := G_EXTRACT_MODE_PMT;
360 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
361 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_PMT',
362 debug_level => G_LEVEL_STATEMENT,
363 module => l_Debug_Module);
364 END IF;
365
366 -- to do: confirm the format type code with PM
367 ELSIF p_format_type = 'FEDERAL_SUMMARY' THEN
368 G_Extract_Run_Mode := G_EXTRACT_MODE_FV_SMMY;
369
370 IF p_save_extract_flag IS NOT NULL THEN
371 iby_utility_pvt.set_view_param(G_VP_FV_ECS_SEQ, p_save_extract_flag);
372 END IF;
373
374 -- revert the param to its original function
375 l_save_extract_flag := 'N';
376 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
377 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_FV_SMMY',
378 debug_level => G_LEVEL_STATEMENT,
379 module => l_Debug_Module);
380 END IF;
381 ELSIF p_format_type = 'PAYMENT_INSTRUCTION_REGISTER' THEN
382 G_Extract_Run_Mode := G_EXTRACT_MODE_PI_RPT;
383 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
384 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_PI_RPT',
385 debug_level => G_LEVEL_STATEMENT,
386 module => l_Debug_Module);
387 END IF;
388 ELSIF p_format_type = 'AMEX_REMITTANCE_FILE' THEN
389 G_Extract_Run_Mode := G_EXTRACT_MODE_AMEX_REM;
390 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
391 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_AMEX_REM',
392 debug_level => G_LEVEL_STATEMENT,
393 module => l_Debug_Module);
394 END IF;
395 IBY_FD_EXTRACT_GEN_PVT.G_FORMAT_TYPE := p_format_type;
396 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
397 iby_debug_pub.add(debug_msg => 'Format Type global variable is set to: AMEX_REMITTANCE_FILE',
398 debug_level => G_LEVEL_STATEMENT,
399 module => l_Debug_Module);
400 END IF;
401 ELSE
402 G_Extract_Run_Mode := G_EXTRACT_MODE_AUX;
403 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
404 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_AUX',
405 debug_level => G_LEVEL_STATEMENT,
406 module => l_Debug_Module);
407 END IF;
408 END IF;
409
410 IF p_format_type = 'PAYMENT_INSTRUCTION_REGISTER' THEN
411 G_May_Need_HR_Masking := TRUE;
412 END IF;
413
414 Create_Extract_1_0_Main
415 (
416 p_payment_instruction_id => p_payment_instruction_id,
417 p_save_extract_flag => l_save_extract_flag,
418 p_sys_key => p_sys_key,
419 x_extract_doc => x_extract_doc
420 );
421 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
422 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
423 debug_level => G_LEVEL_PROCEDURE,
424 module => l_Debug_Module);
425 END IF;
426
427 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
428 debug_level => G_LEVEL_PROCEDURE,
429 module => l_Debug_Module);
430
431 iby_debug_pub.log(l_Debug_Module,'Exit:TIMESTAMP::' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
432
433 END Create_Extract_1_0;
434
435
436 -- for separate remittance advice
437 -- the p_save_extract_flag and p_format_type
438 -- are currently ignored
439 PROCEDURE Create_Extract_1_0
440 (
441 p_payment_instruction_id IN NUMBER,
442 p_save_extract_flag IN VARCHAR2,
443 p_format_type IN VARCHAR2,
444 p_delivery_method IN VARCHAR2,
445 p_payment_id IN NUMBER,
446 p_sys_key IN iby_security_pkg.des3_key_type,
447 x_extract_doc OUT NOCOPY CLOB,
448 p_from_pmt_ref IN NUMBER,
449 p_to_pmt_ref IN NUMBER
450 )
451 IS
452 l_extract_count NUMBER;
453 l_trxn_doc_id NUMBER;
454 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Extract_1_0';
455
456 BEGIN
457
458 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
459 debug_level => G_LEVEL_PROCEDURE,
460 module => l_Debug_Module);
461 iby_debug_pub.log(l_Debug_Module,'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
462
463 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
464 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
465 debug_level => G_LEVEL_PROCEDURE,
466 module => l_Debug_Module);
467
468 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
469 debug_level => G_LEVEL_STATEMENT,
470 module => l_Debug_Module);
471
472 iby_debug_pub.add(debug_msg => 'SRA delivery method: ' || p_delivery_method,
473 debug_level => G_LEVEL_STATEMENT,
474 module => l_Debug_Module);
475
476 iby_debug_pub.add(debug_msg => 'payment id: ' || p_payment_id,
477 debug_level => G_LEVEL_STATEMENT,
478 module => l_Debug_Module);
479 END IF;
480
481 G_Extract_Run_Mode := G_EXTRACT_MODE_SRA;
482 G_Extract_Run_Delivery_Method := p_delivery_method;
483 G_Extract_Run_Payment_id := p_payment_id;
484 G_Extract_Run_From_Pmt_Ref := p_from_pmt_ref;
485 G_Extract_Run_To_Pmt_Ref := p_to_pmt_ref;
486 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
487 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_SRA',
488 debug_level => G_LEVEL_STATEMENT,
489 module => l_Debug_Module);
490 END IF;
491 Create_Extract_1_0_Main
492 (
493 p_payment_instruction_id => p_payment_instruction_id,
494 p_save_extract_flag => 'N',
495 p_sys_key => p_sys_key,
496 x_extract_doc => x_extract_doc
497 );
498 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
499 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
500 debug_level => G_LEVEL_PROCEDURE,
501 module => l_Debug_Module);
502 END IF;
503 iby_debug_pub.log(l_Debug_Module,'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
504 END Create_Extract_1_0;
505
506
507 -- for formats based on extract code: IBY_FD_PAYMENT_INSTRUCTION
508 -- i.e., payment, separate remittance advice, acp ltr, etc.
509 PROCEDURE Create_Extract_1_0_Main
510 (
511 p_payment_instruction_id IN NUMBER,
512 p_save_extract_flag IN VARCHAR2,
513 p_sys_key IN iby_security_pkg.des3_key_type,
514 x_extract_doc OUT NOCOPY CLOB
515 )
516 IS
517 l_extract_count NUMBER;
518 l_trxn_doc_id NUMBER;
519 l_ele_channel VARCHAR2(50);
520 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Extract_1_0';
521
522 CURSOR l_ece_csr(p_payment_instruction_id IN NUMBER) IS
523 SELECT electronic_processing_channel
524 FROM
525 iby_payment_profiles ppp,
526 iby_pay_instructions_all ins
527 WHERE
528 ins.payment_instruction_id = p_payment_instruction_id
529 AND ins.payment_profile_id = ppp.payment_profile_id;
530
531 BEGIN
532 iby_debug_pub.log(l_Debug_Module,'Enter:TIMESTAMP:: ExtractMain' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
533 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
534 debug_level => G_LEVEL_PROCEDURE,
535 module => l_Debug_Module);
536 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
537 iby_debug_pub.add(debug_msg => 'ENTER CREATE EXTRACT MAIN -- BEFORE INITIALIZING',
538 debug_level => G_LEVEL_STATEMENT,
539 module => l_Debug_Module);
540 END IF;
541 initialize;
542 iby_utility_pvt.set_view_param(G_VP_INSTR_ID,p_payment_instruction_id);
543
544 Validate_and_Set_Syskey(p_sys_key);
545 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
546 iby_debug_pub.add(debug_msg => 'Before XML query ',
547 debug_level => G_LEVEL_STATEMENT,
548 module => l_Debug_Module);
549 END IF;
550 CEP_STANDARD.init_security;
551 OPEN l_ece_csr(p_payment_instruction_id);
552 FETCH l_ece_csr INTO l_ele_channel;
553 CLOSE l_ece_csr;
554 IF l_ele_channel = 'ECE' AND G_Extract_Run_Mode = G_EXTRACT_MODE_PMT THEN
555 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
556 iby_debug_pub.add(debug_msg => 'Electronic processing channel is ECE',
557 debug_level => G_LEVEL_STATEMENT,
558 module => l_Debug_Module);
559 END IF;
560 IBY_FD_POST_PICP_PROGS_PVT.Run_ECE_Formatting(p_payment_instruction_id);
561 RETURN;
562 END IF;
563
564 SELECT XMLType.getClobVal(instruction)
565 INTO x_extract_doc
566 FROM iby_xml_fd_ins_1_0_v
567 WHERE payment_instruction_id = p_payment_instruction_id;
568
569 SELECT count(trxn_document_id)
570 INTO l_extract_count
571 FROM iby_trxn_documents
572 where doctype=100 and trxnmid=p_payment_instruction_id;
573 --(bug 5970838) WHERE doctype = 100 and payment_instruction_id = p_payment_instruction_id;
574 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
575 iby_debug_pub.add(debug_msg => 'After XML query ',
576 debug_level => G_LEVEL_STATEMENT,
577 module => l_Debug_Module);
578 END IF;
579 IF p_save_extract_flag = 'Y' and l_extract_count = 0 THEN
580 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
581 iby_debug_pub.add(debug_msg => 'The save extract flag is Y and there were no extract previously saved ' ||
582 'for the instruction. Saving the extract for reuse.',
583 debug_level => G_LEVEL_STATEMENT,
584 module => l_Debug_Module);
585 END IF;
586
587 iby_trxn_documents_pkg.CreateDocument
588 (
589 p_payment_instruction_id => p_payment_instruction_id,
590 p_doctype => 100,
591 p_doc => x_extract_doc,
592 docid_out => l_trxn_doc_id
593 );
594 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
595 iby_debug_pub.add(debug_msg => 'Save successful, iby trxn doc id: ' || l_trxn_doc_id,
596 debug_level => G_LEVEL_STATEMENT,
597 module => l_Debug_Module);
598 END IF;
599 END IF;
600
601 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
602 debug_level => G_LEVEL_PROCEDURE,
603 module => l_Debug_Module);
604
605 iby_debug_pub.log(l_Debug_Module,'Exit:TIMESTAMP:: ExtractMain' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
606
607 -- clears out data from global temporary table
608 -- frzhang 8/11/2005: commit will be managed by the java CP
609 -- COMMIT;
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 -- make sure procedure is not exited before a COMMIT
614 -- so as to remove security keys
615 -- frzhang 8/11/2005: commit will be managed by the java CP
616 -- COMMIT;
617 /* bug 6878265 */
618 -- when exception occurs, the payment_instruction must be unlocked from the request
619
620 RAISE;
621
622 END Create_Extract_1_0_Main;
623
624
625 PROCEDURE Validate_and_Set_Syskey
626 (
627 p_sys_key IN iby_security_pkg.des3_key_type
628 )
629 IS
630 lx_err_code VARCHAR2(30);
631 BEGIN
632
633
634 IF (NOT p_sys_key IS NULL) THEN
635 iby_security_pkg.validate_sys_key(p_sys_key,lx_err_code);
636 IF (NOT lx_err_code IS NULL) THEN
637 raise_application_error(-20000,lx_err_code, FALSE);
638 END IF;
639 iby_utility_pvt.set_view_param(G_VP_SYS_KEY,p_sys_key);
640 END IF;
641
642 END Validate_and_Set_Syskey;
643
644
645 PROCEDURE Create_PPR_Extract_1_0
646 (
647 p_payment_service_request_id IN NUMBER,
648 p_sys_key IN iby_security_pkg.des3_key_type,
649 x_extract_doc OUT NOCOPY CLOB
650 )
651 IS
652 l_extract_count NUMBER;
653 l_trxn_doc_id NUMBER;
654 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_PPR_Extract_1_0';
655 BEGIN
656 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
657 debug_level => G_LEVEL_PROCEDURE,
658 module => l_Debug_Module);
659 iby_debug_pub.log(l_Debug_Module,'Enter:TIMESTAMP:: PPR Extract' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
660
661 iby_utility_pvt.set_view_param(G_VP_INSTR_ID,p_payment_service_request_id);
662
663 iby_utility_pvt.set_view_param(G_VP_FMT_TYPE,'PAYMENT_PROCESS_REQUEST_REPORT');
664
665 Validate_and_Set_Syskey(p_sys_key);
666
667 G_Extract_Run_Mode := G_EXTRACT_MODE_PPR_RPT;
668 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
669 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_PPR_RPT',
670 debug_level => G_LEVEL_STATEMENT,
671 module => l_Debug_Module);
672 END IF;
673
674 G_May_Need_HR_Masking := TRUE;
675 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
676 iby_debug_pub.add(debug_msg => 'Before XML query ',
677 debug_level => G_LEVEL_STATEMENT,
678 module => l_Debug_Module);
679 END IF;
680 CEP_STANDARD.init_security;
681
682 SELECT XMLType.getClobVal(payment_process_request)
683 INTO x_extract_doc
684 FROM iby_xml_fd_ppr_1_0_v
685 WHERE payment_service_request_id = p_payment_service_request_id;
686
687 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
688 iby_debug_pub.add(debug_msg => 'After XML query ',
689 debug_level => G_LEVEL_STATEMENT,
690 module => l_Debug_Module);
691 END IF;
692
693 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
694 debug_level => G_LEVEL_PROCEDURE,
695 module => l_Debug_Module);
696 iby_debug_pub.log(l_Debug_Module,'Exit:TIMESTAMP:: PPR Extract' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
697
698 EXCEPTION
699 WHEN OTHERS THEN
700 RAISE;
701
702 END Create_PPR_Extract_1_0;
703
704
705 -- for positive pay - bug 5028143
706 PROCEDURE Create_Pos_Pay_Extract_1_0
707 (
708 p_payment_instruction_id IN NUMBER,
709 p_payment_profile_id IN NUMBER,
710 p_from_date IN VARCHAR2,
711 p_to_date IN VARCHAR2,
712 p_sys_key IN iby_security_pkg.des3_key_type,
713 x_extract_doc OUT NOCOPY CLOB
714 )
715 IS
716
717 type payment_arr is table of number;
718 l_paymentid_arr payment_arr;
719 l_paymentinstrid_arr payment_arr;
720
721 l_to_date VARCHAR2(255);
722 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Pos_Pay_Extract_1_0';
723
724 CURSOR l_pospay_ins_csr IS
725 SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))
726 FROM
727 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
728 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
729 AND xml_pmt_lvl.payment_status = 'ISSUED'
730 AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL);
731
732 CURSOR l_pospay_appp_csr (p_to_date IN VARCHAR2) IS
733 SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))
734 FROM
735 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
736 WHERE xml_pmt_lvl.payment_profile_id = p_payment_profile_id
737 AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)
738 AND xml_pmt_lvl.payment_date <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), SYSDATE)
739 AND xml_pmt_lvl.payment_status = 'ISSUED'
740 AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL);
741
742 BEGIN
743 iby_debug_pub.log(l_Debug_Module,'Enter:TIMESTAMP:: POS Pay' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
744
745 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
746 debug_level => G_LEVEL_PROCEDURE,
747 module => l_Debug_Module);
748 Validate_and_Set_Syskey(p_sys_key);
749
750 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
751 iby_debug_pub.add(debug_msg => 'Before XML query ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
752 END IF;
753
754 CEP_STANDARD.init_security;
755
756 IF p_payment_instruction_id <> -99 THEN
757 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
758 iby_debug_pub.add(debug_msg => 'payment instruction id is supplied ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
759 END IF;
760
761 OPEN l_pospay_ins_csr;
762 FETCH l_pospay_ins_csr INTO x_extract_doc;
763 CLOSE l_pospay_ins_csr;
764
765 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
766 iby_debug_pub.add(debug_msg => 'payment level attribute setting ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
767 END IF;
768
769 /*LKQ POS PAY - PAVAN */
770 -- payment level attribute setting
771 UPDATE iby_payments_all pmt
772 SET pmt.positive_pay_file_created_flag = 'Y'
773 WHERE pmt.payment_instruction_id = p_payment_instruction_id
774 AND pmt.payment_status = 'ISSUED'
775 AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL);
776
777 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
778 iby_debug_pub.add(debug_msg => 'payment instruction level attribute setting ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
779 END IF;
780
781 /*LKQ POS PAY - PAVAN */
782 -- payment instruction level attribute setting
783
784 UPDATE iby_pay_instructions_all
785 SET positive_pay_file_created_flag= 'Y'
786 WHERE payment_instruction_id = p_payment_instruction_id;
787
788 ELSE
789 IF instr(p_to_date, '00:00:00') <> 0 THEN
790 l_to_date := REPLACE(p_to_date, '00:00:00', '23:59:59');
791 ELSE
792 l_to_date := p_to_date;
793 END IF;
794
795 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
796 iby_debug_pub.add(debug_msg => 'payment instruction id is NOT supplied ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
797 END IF;
798
799 OPEN l_pospay_appp_csr(l_to_date);
800 FETCH l_pospay_appp_csr INTO x_extract_doc;
801 CLOSE l_pospay_appp_csr;
802
803 SELECT pmt.payment_id,pmt.payment_instruction_id
804 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
805 FROM iby_xml_fd_pmt_1_0_v pmt
806 WHERE pmt.payment_profile_id = p_payment_profile_id
807 AND pmt.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
808 AND pmt.payment_date <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), SYSDATE)
809 AND pmt.payment_status = 'ISSUED'
810 AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL);
811
812 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
813 iby_debug_pub.add(debug_msg => 'payment level attribute setting ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
814 END IF;
815
816
817 /*LKQ POS PAY - PAVAN */
818 -- payment level attribute setting
819 iby_debug_pub.add(debug_msg => 'Payment Count : '|| l_paymentid_arr.COUNT,debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
820 IF ( l_paymentid_arr.COUNT <> 0) THEN
821 FOR i IN l_paymentid_arr.FIRST .. l_paymentid_arr.LAST LOOP
822 UPDATE iby_payments_all
823 SET positive_pay_file_created_flag = 'Y'
824 WHERE payment_id = l_paymentid_arr(i);
825 iby_debug_pub.add(debug_msg => 'Payment ID : '|| l_paymentid_arr(i),debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
826 END LOOP;
827 ELSE
828 iby_debug_pub.add(debug_msg => 'Payment ID : '|| 'Empty',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
829 END IF;
830
831 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
832 iby_debug_pub.add(debug_msg => 'payment instruction level attribute setting ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
833 END IF;
834
835 /*LKQ POS PAY - PAVAN */
836 -- payment instruction level attribute setting
837 iby_debug_pub.add(debug_msg => 'Payment Instr Count : '|| l_paymentinstrid_arr.COUNT,debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
838 IF ( l_paymentinstrid_arr.COUNT <> 0) THEN
839 FOR i IN l_paymentinstrid_arr.FIRST .. l_paymentinstrid_arr.LAST LOOP
840 IF (l_paymentinstrid_arr(i) IS NOT NULL ) THEN
841 UPDATE iby_pay_instructions_all ins
842 SET ins.positive_pay_file_created_flag = 'Y'
843 WHERE not exists (SELECT 'N'
844 FROM iby_payments_all pmt
845 WHERE nvl(pmt.positive_pay_file_created_flag,'N') = 'N'
846 AND pmt.payment_status IN('ISSUED', 'PAID')
847 AND pmt.payment_instruction_id = l_paymentinstrid_arr(i))
848 AND ins.payment_instruction_id = l_paymentinstrid_arr(i);
849 iby_debug_pub.add(debug_msg => 'Payment Instr ID : '|| l_paymentinstrid_arr(i),debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
850 END IF;
851 END LOOP;
852 ELSE
853 iby_debug_pub.add(debug_msg => 'Payment Instr ID : '|| 'Empty',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
854 END IF;
855
856 END IF;
857
858 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
859 iby_debug_pub.add(debug_msg => 'After XML query ',
860 debug_level => G_LEVEL_STATEMENT,
861 module => l_Debug_Module);
862 END IF;
863
864 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
865 debug_level => G_LEVEL_PROCEDURE,
866 module => l_Debug_Module);
867 iby_debug_pub.log(l_Debug_Module,'Exit:TIMESTAMP:: POS Pay' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
868
869 EXCEPTION
870 WHEN OTHERS THEN
871 RAISE;
872
873 END Create_Pos_Pay_Extract_1_0;
874
875
876
877
878 -- LKQ POS PAY ISSUE - PAVAN
879 PROCEDURE Create_Pos_Pay_Extract_2_0
880 (
881 p_payment_instruction_id IN NUMBER,
882
883 p_format_name IN VARCHAR2,
884 p_internal_bank_account_name IN VARCHAR2,
885 p_from_date IN VARCHAR2,
886 p_to_date IN VARCHAR2,
887 p_payment_status IN VARCHAR2,
888 p_reselect IN VARCHAR2,
889
890 p_sys_key IN iby_security_pkg.des3_key_type,
891 x_extract_doc OUT NOCOPY CLOB
892 )
893 IS
894
895 type payment_arr is table of number;
896 l_paymentid_arr payment_arr;
897 l_paymentinstrid_arr payment_arr;
898 l_to_date VARCHAR2(255);
899 l_from_date VARCHAR2(255);
900 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Pos_Pay_Extract_2_0';
901 x_extract_doc_R12 CLOB;
902 x_extract_doc_11i CLOB;
903
904 --cursor for - pmt instr id supplied,negotiable payments, reselect - no
905 CURSOR l_pospay_ins_csr_1_1 IS
906 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
907 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
908 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
909 AND xml_pmt_lvl.payment_status IN ('ISSUED','PAID')
910 AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)
911 ;
912
913 --cursor for - pmt instr id supplied,negotiable payments, reselect - yes
914 CURSOR l_pospay_ins_csr_1_2 IS
915 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
916 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
917 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
918 AND xml_pmt_lvl.payment_status IN ('ISSUED','PAID')
919 ;
920
921 --cursor for - pmt instr id supplied,voided payments, reselect - no
922 CURSOR l_pospay_ins_csr_2_1 IS
923 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
924 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
925 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
926 AND xml_pmt_lvl.payment_status IN('VOID')
927 AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)
928 ;
929
930 --cursor for - pmt instr id supplied,voided payments, reselect - yes
931 CURSOR l_pospay_ins_csr_2_2 IS
932 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
933 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
934 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
935 AND xml_pmt_lvl.payment_status IN('VOID')
936 ;
937
938 --cursor for - pmt instr id supplied,negotiable and voided payments, reselect - no
939 CURSOR l_pospay_ins_csr_3_1 IS
940 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
941 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
942 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
943 AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
944 AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)
945 ;
946
947 --cursor for - pmt instr id supplied,negotiable and voided payments, reselect - yes
948 CURSOR l_pospay_ins_csr_3_2 IS
949 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
950 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
951 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
952 AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
953 ;
954
955
956
957 --cursor for - pmt instr id not supplied,negotiable payments, reselect - no
958 CURSOR l_pospay_appp_csr_1_1 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
959 SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
960 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
961 WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
962 AND ppp.positive_pay_format_code IN
963 (SELECT ppfformat.format_code
964 FROM iby_formats_vl ppfformat
965 WHERE ppfformat.format_name = p_format_name)
966 AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)
967 AND xml_pmt_lvl.payment_date <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
968 AND xml_pmt_lvl.payment_status IN('ISSUED', 'PAID')
969 AND(xml_pmt_lvl.positive_pay_file_created_flag = 'N' OR xml_pmt_lvl.positive_pay_file_created_flag IS NULL)
970 AND xml_pmt_lvl.internal_bank_account_id IN
971 (SELECT ba.bank_account_id
972 FROM ce_bank_accounts ba
973 WHERE ba.bank_account_name = p_internal_bank_account_name)
974 ;
975
976 --cursor for - pmt instr id not supplied,negotiable payments, reselect - yes
977 CURSOR l_pospay_appp_csr_1_2 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
978 SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
979 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
980 WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
981 AND ppp.positive_pay_format_code IN
982 (SELECT ppfformat.format_code
983 FROM iby_formats_vl ppfformat
984 WHERE ppfformat.format_name = p_format_name)
985 AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)
986 AND xml_pmt_lvl.payment_date <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
987 AND xml_pmt_lvl.payment_status IN('ISSUED', 'PAID')
988 AND xml_pmt_lvl.internal_bank_account_id IN
989 (SELECT ba.bank_account_id
990 FROM ce_bank_accounts ba
991 WHERE ba.bank_account_name = p_internal_bank_account_name)
992 ;
993
994 --cursor for - pmt instr id not supplied,voided payments, reselect - no
995 CURSOR l_pospay_appp_csr_2_1 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
996 SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
997 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
998 WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
999 AND ppp.positive_pay_format_code IN
1000 (SELECT ppfformat.format_code
1001 FROM iby_formats_vl ppfformat
1002 WHERE ppfformat.format_name = p_format_name)
1003 AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date))
1004 AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1005 AND xml_pmt_lvl.payment_status IN('VOID')
1006 AND(xml_pmt_lvl.positive_pay_file_created_flag = 'N' OR xml_pmt_lvl.positive_pay_file_created_flag IS NULL)
1007 AND xml_pmt_lvl.internal_bank_account_id IN
1008 (SELECT ba.bank_account_id
1009 FROM ce_bank_accounts ba
1010 WHERE ba.bank_account_name = p_internal_bank_account_name)
1011 ;
1012
1013 --cursor for - pmt instr id not supplied,voided payments, reselect - yes
1014 CURSOR l_pospay_appp_csr_2_2 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
1015 SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
1016 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
1017 WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
1018 AND ppp.positive_pay_format_code IN
1019 (SELECT ppfformat.format_code
1020 FROM iby_formats_vl ppfformat
1021 WHERE ppfformat.format_name = p_format_name)
1022 AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date))
1023 AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1024 AND xml_pmt_lvl.payment_status IN('VOID')
1025 AND xml_pmt_lvl.internal_bank_account_id IN
1026 (SELECT ba.bank_account_id
1027 FROM ce_bank_accounts ba
1028 WHERE ba.bank_account_name = p_internal_bank_account_name)
1029 ;
1030
1031 --cursor for - pmt instr id not supplied,negotiable and voided payments, reselect - no
1032 CURSOR l_pospay_appp_csr_3_1 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
1033 SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
1034 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
1035 WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
1036 AND ppp.positive_pay_format_code IN
1037 (SELECT ppfformat.format_code
1038 FROM iby_formats_vl ppfformat
1039 WHERE ppfformat.format_name = p_format_name)
1040 AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date))
1041 AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1042 AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
1043 AND(xml_pmt_lvl.positive_pay_file_created_flag = 'N' OR xml_pmt_lvl.positive_pay_file_created_flag IS NULL)
1044 AND xml_pmt_lvl.internal_bank_account_id IN
1045 (SELECT ba.bank_account_id
1046 FROM ce_bank_accounts ba
1047 WHERE ba.bank_account_name = p_internal_bank_account_name)
1048 ;
1049
1050 --cursor for - pmt instr id not supplied,negotiable and voided payments, reselect - yes
1051 CURSOR l_pospay_appp_csr_3_2 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
1052 SELECT xmltype.getclobval(xmlagg(xml_pmt_lvl.payment))
1053 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
1054 WHERE xml_pmt_lvl.payment_profile_id = ppp.payment_profile_id
1055 AND ppp.positive_pay_format_code IN
1056 (SELECT ppfformat.format_code
1057 FROM iby_formats_vl ppfformat
1058 WHERE ppfformat.format_name = p_format_name)
1059 AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date))
1060 AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1061 AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
1062 AND xml_pmt_lvl.internal_bank_account_id IN
1063 (SELECT ba.bank_account_id
1064 FROM ce_bank_accounts ba
1065 WHERE ba.bank_account_name = p_internal_bank_account_name)
1066 ;
1067
1068 CURSOR l_pospay_appp_csr_11i (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
1069 SELECT xmltype.getclobval(xmlagg(pmt.payment))
1070 FROM IBY_XML_FD_11iPMT_1_0_V pmt
1071 WHERE pmt.internal_bank_account_id IN (SELECT ba.bank_account_id FROM ce_bank_accounts ba WHERE ba.bank_account_name = p_internal_bank_account_name)
1072 AND pmt.payment_date >= NVL(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
1073 AND pmt.payment_date <= NVL(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1074 AND ( ( NVL(UPPER(p_reselect),'NO') = 'YES' )
1075 OR ( NVL(pmt.positive_pay_file_created_flag,'N') = 'N' AND NVL(UPPER(p_reselect),'NO') = 'NO' ) );
1076
1077 BEGIN
1078
1079 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
1080 debug_level => G_LEVEL_PROCEDURE,
1081 module => l_Debug_Module);
1082
1083 iby_debug_pub.log(l_Debug_Module,'Enter:TIMESTAMP:: POS Pay2' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
1084
1085
1086 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1087 -- parameter disp
1088 iby_debug_pub.add(debug_msg => 'Parameters 0 ' || ':' || p_payment_instruction_id || ':' ||
1089 p_format_name || ':' || p_internal_bank_account_name || ':' || p_payment_status || ':' ||
1090 p_reselect || ':' || p_from_date || ':' || p_to_date || ':' ||
1091 p_sys_key, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1092
1093
1094 --system key validation
1095 iby_debug_pub.add(debug_msg => 'Enter ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1096
1097
1098 --initializing
1099 iby_debug_pub.add(debug_msg => 'Before XML query ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1100 END IF;
1101
1102
1103
1104 Validate_and_Set_Syskey(p_sys_key);
1105 CEP_STANDARD.init_security;
1106
1107
1108 --parameter editing
1109 l_from_date := p_from_date;
1110 l_to_date := p_to_date;
1111
1112 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1113 iby_debug_pub.add(debug_msg => 'l_from_date 0 : ' || l_from_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1114 iby_debug_pub.add(debug_msg => 'l_to_date 0 : ' || l_to_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1115 END IF;
1116
1117 /* From date does not need any manipulations, and should not be defaulted to sysdate
1118 IF ( trim(l_from_date) = '' or l_from_date = null or l_from_date = 'null') THEN
1119 l_from_date := to_char(sysdate,'YYYY/MM/DD')||' 00:00:00';
1120 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1121 iby_debug_pub.add(debug_msg => 'l_from_date 1 : ' || l_from_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1122 END IF;
1123 END IF;*/
1124 IF ( trim(l_to_date) = '' or l_to_date is null or l_to_date = 'null') THEN
1125 l_to_date := to_char(sysdate,'YYYY/MM/DD')||' 00:00:00';
1126 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1127 iby_debug_pub.add(debug_msg => 'l_to_date 1 : ' || l_to_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1128 END IF;
1129 END IF;
1130 /* From date does not need any manipulations, and should not be defaulted to sysdate
1131 IF instr(l_from_date, '00:00:00') <> 0 THEN
1132 l_from_date := REPLACE(l_from_date, '00:00:00', '00:00:01');
1133 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1134 iby_debug_pub.add(debug_msg => 'l_from_date 2 : ' || l_from_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1135 END IF;
1136 END IF;*/
1137
1138 IF instr(l_to_date, '00:00:00') <> 0 THEN
1139 l_to_date := REPLACE(l_to_date, '00:00:00', '23:59:59');
1140 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1141 iby_debug_pub.add(debug_msg => 'l_to_date 2 : ' || l_to_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1142 END IF;
1143 END IF;
1144
1145 -- parameter disp
1146 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1147 iby_debug_pub.add(debug_msg => 'Parameters 1 ' || ':' || p_payment_instruction_id || ':' ||
1148 p_format_name || ':' || p_internal_bank_account_name || ':' || p_payment_status || ':' ||
1149 p_reselect || ':' || l_from_date || ':' || l_to_date || ':' ||
1150 p_sys_key, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1151 END IF;
1152
1153 --parameter checks
1154 IF (p_payment_instruction_id = -99 ) THEN
1155 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1156 iby_debug_pub.add(debug_msg => 'Payment Instruction ID not supplied ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1157 END IF;
1158 IF ((trim(p_format_name) = '' or p_format_name is null or p_format_name = 'null') OR (trim(p_internal_bank_account_name) = '' or p_internal_bank_account_name is null or p_internal_bank_account_name = 'null')) THEN
1159 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1160 iby_debug_pub.add(debug_msg => 'Enter the conditional mandatory fields Format Name and Internal Bank Account ID or Payment Instruction ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1161 END IF;
1162 RAISE FND_API.G_EXC_ERROR;
1163 END IF;
1164 END IF;
1165
1166 IF (nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate) < nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)) ) THEN
1167 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1168 iby_debug_pub.add(debug_msg => 'From Payment Date is greater than To Payment Date', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1169 END IF;
1170 RAISE FND_API.G_EXC_ERROR;
1171 END IF;
1172
1173 IF (trim(p_payment_status) = '' or p_payment_status is null or p_payment_status = 'null') THEN
1174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1175 iby_debug_pub.add(debug_msg => 'Cannot proceed since Payment Status attribute is not supplied', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1176 END IF;
1177 RAISE FND_API.G_EXC_ERROR;
1178 END IF;
1179
1180 IF (trim(p_reselect) = '' or p_reselect is null or p_reselect = 'null') THEN
1181 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1182 iby_debug_pub.add(debug_msg => 'Cannot proceed since Reselect attribute is not supplied', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1183 END IF;
1184 RAISE FND_API.G_EXC_ERROR;
1185 END IF;
1186
1187 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1188 -- edited parameter disp
1189 iby_debug_pub.add(debug_msg => 'Parameters 2 ' || ':' || p_payment_instruction_id || ':' ||
1190 p_format_name || ':' || p_internal_bank_account_name || ':' || p_payment_status || ':' ||
1191 p_reselect || ':' || l_from_date || ':' || l_to_date || ':' ||
1192 p_sys_key, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1193 END IF;
1194
1195 --pmt instr supplied
1196 IF p_payment_instruction_id <> -99 THEN
1197
1198 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1199 iby_debug_pub.add(debug_msg => 'Payment Instruction ID supplied ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1200 END IF;
1201 IF upper(p_payment_status) = 'NEGOTIABLE' THEN
1202 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1203 iby_debug_pub.add(debug_msg => 'Payment Status is negotiable ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1204 END IF;
1205 IF upper(p_reselect) = 'NO' THEN
1206 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1207 iby_debug_pub.add(debug_msg => 'Reselect Option No ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1208 END IF;
1209 OPEN l_pospay_ins_csr_1_1;
1210 FETCH l_pospay_ins_csr_1_1 INTO x_extract_doc;
1211 CLOSE l_pospay_ins_csr_1_1;
1212
1213 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1214 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1215 END IF;
1216 UPDATE iby_payments_all
1217 SET positive_pay_file_created_flag = 'Y'
1218 WHERE payment_instruction_id = p_payment_instruction_id
1219 AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
1220 AND payment_status IN ('ISSUED','PAID');
1221 ELSE
1222 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1223 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1224 END IF;
1225 OPEN l_pospay_ins_csr_1_2;
1226 FETCH l_pospay_ins_csr_1_2 INTO x_extract_doc;
1227 CLOSE l_pospay_ins_csr_1_2;
1228
1229 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1230 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1231 END IF;
1232 UPDATE iby_payments_all
1233 SET positive_pay_file_created_flag = 'Y'
1234 WHERE payment_instruction_id = p_payment_instruction_id
1235 AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
1236 AND payment_status IN ('ISSUED','PAID');
1237
1238 END IF;
1239 ELSIF upper(p_payment_status) = 'VOIDED' THEN
1240 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1241 iby_debug_pub.add(debug_msg => 'Payment Status is voided ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1242 END IF;
1243 IF upper(p_reselect) = 'NO' THEN
1244 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1245 iby_debug_pub.add(debug_msg => 'Reselect Option No ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1246 END IF;
1247 OPEN l_pospay_ins_csr_2_1;
1248 FETCH l_pospay_ins_csr_2_1 INTO x_extract_doc;
1249 CLOSE l_pospay_ins_csr_2_1;
1250
1251 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1252 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1253 END IF;
1254 UPDATE iby_payments_all
1255 SET positive_pay_file_created_flag = 'Y'
1256 WHERE payment_instruction_id = p_payment_instruction_id
1257 AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
1258 AND payment_status IN ('VOID');
1259
1260 ELSE
1261 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1262 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1263 END IF;
1264 OPEN l_pospay_ins_csr_2_2;
1265 FETCH l_pospay_ins_csr_2_2 INTO x_extract_doc;
1266 CLOSE l_pospay_ins_csr_2_2;
1267
1268 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1269 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1270 END IF;
1271 UPDATE iby_payments_all
1272 SET positive_pay_file_created_flag = 'Y'
1273 WHERE payment_instruction_id = p_payment_instruction_id
1274 AND payment_status IN ('VOID');
1275
1276 END IF;
1277 ELSE
1278 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1279 iby_debug_pub.add(debug_msg => 'Payment Status is negotiable and voided ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1280 END IF;
1281 IF upper(p_reselect) = 'NO' THEN
1282 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1283 iby_debug_pub.add(debug_msg => 'Reselect Option No ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1284 END IF;
1285 OPEN l_pospay_ins_csr_3_1;
1286 FETCH l_pospay_ins_csr_3_1 INTO x_extract_doc;
1287 CLOSE l_pospay_ins_csr_3_1;
1288
1289 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1290 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1291 END IF;
1292 UPDATE iby_payments_all
1293 SET positive_pay_file_created_flag = 'Y'
1294 WHERE payment_instruction_id = p_payment_instruction_id
1295 AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
1296 AND payment_status IN ('VOID','ISSUED','PAID');
1297
1298 ELSE
1299 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1300 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1301 END IF;
1302 OPEN l_pospay_ins_csr_3_2;
1303 FETCH l_pospay_ins_csr_3_2 INTO x_extract_doc;
1304 CLOSE l_pospay_ins_csr_3_2;
1305
1306 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1307 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1308 END IF;
1309 UPDATE iby_payments_all
1310 SET positive_pay_file_created_flag = 'Y'
1311 WHERE payment_instruction_id = p_payment_instruction_id
1312 AND payment_status IN ('VOID','ISSUED','PAID');
1313
1314 END IF;
1315 END IF;
1316 --pmt instr not supplied
1317 ELSE
1318 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1319 iby_debug_pub.add(debug_msg => 'Payment Instruction ID NOT supplied ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1320 END IF;
1321 IF upper(p_payment_status) = 'NEGOTIABLE' THEN
1322 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1323 iby_debug_pub.add(debug_msg => 'Payment Status is negotiable ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1324 END IF;
1325 IF upper(p_reselect) = 'NO' THEN
1326 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1327 iby_debug_pub.add(debug_msg => 'Reselect Option No ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1328 END IF;
1329 OPEN l_pospay_appp_csr_1_1(l_from_date,l_to_date);
1330 FETCH l_pospay_appp_csr_1_1 INTO x_extract_doc_R12;
1331 CLOSE l_pospay_appp_csr_1_1;
1332
1333 SELECT pmt.payment_id,pmt.payment_instruction_id
1334 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1335 FROM iby_xml_fd_pmt_1_0_v pmt,
1336 iby_payment_profiles ppp,
1337 iby_formats_vl ppfformat,
1338 ce_bank_accounts ba
1339 WHERE pmt.payment_profile_id = ppp.payment_profile_id
1340 AND ppp.positive_pay_format_code = ppfformat.format_code
1341 AND pmt.internal_bank_account_id = ba.bank_account_id
1342 AND ppfformat.format_name = p_format_name
1343 AND ba.bank_account_name = p_internal_bank_account_name
1344 AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL)
1345 AND pmt.payment_date >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
1346 AND pmt.payment_date <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1347 AND pmt.payment_status IN('ISSUED','PAID');
1348
1349 ELSE
1350 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1351 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1352 END IF;
1353 OPEN l_pospay_appp_csr_1_2(l_from_date,l_to_date);
1354 FETCH l_pospay_appp_csr_1_2 INTO x_extract_doc_R12;
1355 CLOSE l_pospay_appp_csr_1_2;
1356
1357 SELECT pmt.payment_id,pmt.payment_instruction_id
1358 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1359 FROM iby_xml_fd_pmt_1_0_v pmt,
1360 iby_payment_profiles ppp,
1361 iby_formats_vl ppfformat,
1362 ce_bank_accounts ba
1363 WHERE pmt.payment_profile_id = ppp.payment_profile_id
1364 AND ppp.positive_pay_format_code = ppfformat.format_code
1365 AND pmt.internal_bank_account_id = ba.bank_account_id
1366 AND ppfformat.format_name = p_format_name
1367 AND ba.bank_account_name = p_internal_bank_account_name
1368 AND pmt.payment_date >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
1369 AND pmt.payment_date <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1370 AND pmt.payment_status IN('ISSUED','PAID');
1371
1372 END IF;
1373 ELSIF upper(p_payment_status) = 'VOIDED' THEN
1374 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1375 iby_debug_pub.add(debug_msg => 'Payment Status is voided ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1376 END IF;
1377 IF upper(p_reselect) = 'NO' THEN
1378 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1379 iby_debug_pub.add(debug_msg => 'Reselect Option No ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1380 END IF;
1381 OPEN l_pospay_appp_csr_2_1(l_from_date,l_to_date);
1382 FETCH l_pospay_appp_csr_2_1 INTO x_extract_doc_R12;
1383 CLOSE l_pospay_appp_csr_2_1;
1384
1385 SELECT pmt.payment_id,pmt.payment_instruction_id
1386 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1387 FROM iby_xml_fd_pmt_1_0_v pmt,
1388 iby_payment_profiles ppp,
1389 iby_formats_vl ppfformat,
1390 ce_bank_accounts ba
1391 WHERE pmt.payment_profile_id = ppp.payment_profile_id
1392 AND ppp.positive_pay_format_code = ppfformat.format_code
1393 AND pmt.internal_bank_account_id = ba.bank_account_id
1394 AND ppfformat.format_name = p_format_name
1395 AND ba.bank_account_name = p_internal_bank_account_name
1396 AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL)
1397 AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date))
1398 AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1399 AND pmt.payment_status IN('VOID');
1400 ELSE
1401 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1402 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1403 END IF;
1404 OPEN l_pospay_appp_csr_2_2(l_from_date,l_to_date);
1405 FETCH l_pospay_appp_csr_2_2 INTO x_extract_doc_R12;
1406 CLOSE l_pospay_appp_csr_2_2;
1407
1408 SELECT pmt.payment_id,pmt.payment_instruction_id
1409 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1410 FROM iby_xml_fd_pmt_1_0_v pmt,
1411 iby_payment_profiles ppp,
1412 iby_formats_vl ppfformat,
1413 ce_bank_accounts ba
1414 WHERE pmt.payment_profile_id = ppp.payment_profile_id
1415 AND ppp.positive_pay_format_code = ppfformat.format_code
1416 AND pmt.internal_bank_account_id = ba.bank_account_id
1417 AND ppfformat.format_name = p_format_name
1418 AND ba.bank_account_name = p_internal_bank_account_name
1419 AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date))
1420 AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1421 AND pmt.payment_status IN('VOID');
1422
1423 END IF;
1424 ELSE
1425 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1426 iby_debug_pub.add(debug_msg => 'Payment Status is negotiable and voided ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1427 END IF;
1428 IF upper(p_reselect) = 'NO' THEN
1429 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1430 iby_debug_pub.add(debug_msg => 'Reselect Option No ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1431 END IF;
1432 OPEN l_pospay_appp_csr_3_1(l_from_date,l_to_date);
1433 FETCH l_pospay_appp_csr_3_1 INTO x_extract_doc_R12;
1434 CLOSE l_pospay_appp_csr_3_1;
1435
1436 SELECT pmt.payment_id,pmt.payment_instruction_id
1437 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1438 FROM iby_xml_fd_pmt_1_0_v pmt,
1439 iby_payment_profiles ppp,
1440 iby_formats_vl ppfformat,
1441 ce_bank_accounts ba
1442 WHERE pmt.payment_profile_id = ppp.payment_profile_id
1443 AND ppp.positive_pay_format_code = ppfformat.format_code
1444 AND pmt.internal_bank_account_id = ba.bank_account_id
1445 AND ppfformat.format_name = p_format_name
1446 AND ba.bank_account_name = p_internal_bank_account_name
1447 AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL)
1448 AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date))
1449 AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1450 AND pmt.payment_status IN('VOID','ISSUED','PAID');
1451
1452 ELSE
1453 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1454 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1455 END IF;
1456 OPEN l_pospay_appp_csr_3_2(l_from_date,l_to_date);
1457 FETCH l_pospay_appp_csr_3_2 INTO x_extract_doc_R12;
1458 CLOSE l_pospay_appp_csr_3_2;
1459
1460 SELECT pmt.payment_id,pmt.payment_instruction_id
1461 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1462 FROM iby_xml_fd_pmt_1_0_v pmt,
1463 iby_payment_profiles ppp,
1464 iby_formats_vl ppfformat,
1465 ce_bank_accounts ba
1466 WHERE pmt.payment_profile_id = ppp.payment_profile_id
1467 AND ppp.positive_pay_format_code = ppfformat.format_code
1468 AND pmt.internal_bank_account_id = ba.bank_account_id
1469 AND ppfformat.format_name = p_format_name
1470 AND ba.bank_account_name = p_internal_bank_account_name
1471 AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date))
1472 AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1473 AND pmt.payment_status IN('VOID','ISSUED','PAID');
1474
1475 END IF;
1476 END IF;
1477
1478 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1479 iby_debug_pub.add(debug_msg => 'payment level attribute setting ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1480 END IF;
1481
1482
1483 iby_debug_pub.add(debug_msg => 'Payment Count : '|| l_paymentid_arr.COUNT,debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1484 IF ( l_paymentid_arr.COUNT <> 0) THEN
1485 FOR i IN l_paymentid_arr.FIRST .. l_paymentid_arr.LAST LOOP
1486 UPDATE iby_payments_all
1487 SET positive_pay_file_created_flag = 'Y'
1488 WHERE payment_id = l_paymentid_arr(i);
1489 iby_debug_pub.add(debug_msg => 'Payment ID : '|| l_paymentid_arr(i),debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1490 END LOOP;
1491 ELSE
1492 iby_debug_pub.add(debug_msg => 'Payment ID : '|| 'Empty',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1493 END IF;
1494
1495 IF upper(p_payment_status) <> 'NEGOTIABLE' THEN
1496
1497 --11i Data Extract
1498 OPEN l_pospay_appp_csr_11i(l_from_date,l_to_date);
1499 FETCH l_pospay_appp_csr_11i INTO x_extract_doc_11i;
1500 CLOSE l_pospay_appp_csr_11i;
1501
1502 UPDATE ap_checks_all
1503 SET positive_pay_status_code = 'SENT AS VOIDED'
1504 WHERE check_ID IN
1505 (SELECT pmt.check_id
1506 FROM IBY_XML_FD_11iPMT_1_0_V pmt
1507 WHERE pmt.internal_bank_account_id IN
1508 (SELECT ba.bank_account_id
1509 FROM ce_bank_accounts ba
1510 WHERE ba.bank_account_name = p_internal_bank_account_name)
1511 AND pmt.payment_date >= NVL(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
1512 AND pmt.payment_date <= NVL(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1513 AND ( ( NVL(UPPER(p_reselect),'NO') = 'YES' )
1514 OR ( NVL(pmt.positive_pay_file_created_flag,'N') = 'N'
1515 AND NVL(UPPER(p_reselect),'NO') = 'NO' ) ) );
1516
1517 END IF;
1518
1519 x_extract_doc := '<PositivePayDataExtract>' || x_extract_doc_R12 || x_extract_doc_11i || '</PositivePayDataExtract>';
1520
1521 END IF;
1522
1523
1524
1525 --payment instruction level attribute setting
1526
1527 IF p_payment_instruction_id <> -99 THEN
1528
1529 --if payment instruction is supplied
1530 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1531 iby_debug_pub.add(debug_msg => 'Setting the payment instruction level positive_pay_file_created_flag ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1532 END IF;
1533 UPDATE iby_pay_instructions_all
1534 SET positive_pay_file_created_flag='Y'
1535 WHERE payment_instruction_id = p_payment_instruction_id;
1536
1537 ELSE
1538 --if payment instruction is NOT supplied
1539 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1540 iby_debug_pub.add(debug_msg => 'Setting the payment instruction level positive_pay_file_created_flag ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1541 END IF;
1542
1543
1544 iby_debug_pub.add(debug_msg => 'Payment Instr Count : '|| l_paymentinstrid_arr.COUNT,debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1545 IF ( l_paymentinstrid_arr.COUNT <> 0) THEN
1546 FOR i IN l_paymentinstrid_arr.FIRST .. l_paymentinstrid_arr.LAST LOOP
1547 IF (l_paymentinstrid_arr(i) IS NOT NULL ) THEN
1548 UPDATE iby_pay_instructions_all ins
1549 SET ins.positive_pay_file_created_flag = 'Y'
1550 WHERE not exists (SELECT 'N'
1551 FROM iby_payments_all pmt
1552 WHERE nvl(pmt.positive_pay_file_created_flag,'N') = 'N'
1553 AND pmt.payment_status IN('ISSUED', 'PAID')
1554 AND pmt.payment_instruction_id = l_paymentinstrid_arr(i))
1555 AND ins.payment_instruction_id = l_paymentinstrid_arr(i);
1556 iby_debug_pub.add(debug_msg => 'Payment Instr ID : '|| l_paymentinstrid_arr(i),debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1557 END IF;
1558 END LOOP;
1559 ELSE
1560 iby_debug_pub.add(debug_msg => 'Payment Instr ID : '|| 'Empty',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1561 END IF;
1562
1563 END IF;
1564
1565 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1566 --Done
1567 iby_debug_pub.add(debug_msg => 'After XML query ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1568 iby_debug_pub.add(debug_msg => 'Exit ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1569 END IF;
1570
1571 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
1572 debug_level => G_LEVEL_PROCEDURE,
1573 module => l_Debug_Module);
1574
1575 iby_debug_pub.log(l_Debug_Module,'Exit:TIMESTAMP:: POS Pay2' || l_Debug_Module||':: '||systimestamp,FND_LOG.LEVEL_STATEMENT);
1576
1577
1578 EXCEPTION
1579 WHEN OTHERS THEN
1580 RAISE;
1581
1582 END Create_Pos_Pay_Extract_2_0;
1583 -- LKQ POS PAY ISSUE - PAVAN
1584
1585
1586
1587 FUNCTION Get_FP_TaxRegistration(p_legal_entity_id IN NUMBER)
1588 RETURN VARCHAR2
1589 IS
1590 l_tax_registration VARCHAR2(2000);
1591 l_return_status VARCHAR2(1);
1592 l_msg_count NUMBER;
1593 l_msg_data VARCHAR2(2000);
1594 l_key NUMBER;
1595 l_registration_number VARCHAR2(2000);
1596 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_FP_TaxRegistration';
1597
1598 BEGIN
1599
1600 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1601 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
1602 debug_level => G_LEVEL_PROCEDURE,
1603 module => l_Debug_Module);
1604 END IF;
1605 l_key := p_legal_entity_id;
1606 /* If the Registration Number is not found in the cache */
1607 IF (NOT(g_payer_registration_tbl.EXISTS( l_key ))) THEN
1608
1609 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1610 iby_debug_pub.add(debug_msg => 'Payer tax reg number not found in the cache for p_legal_entity_id : '||l_key,
1611 debug_level => G_LEVEL_STATEMENT, module => l_debug_module);
1612 END IF;
1613
1614 XLE_UTILITIES_GRP.Get_FP_VATRegistration_LEID
1615 (
1616 p_api_version => 1.0,
1617 p_init_msg_list => fnd_api.g_false,
1618 p_commit => fnd_api.g_false,
1619 p_effective_date => SYSDATE,
1620 x_return_status => l_return_status,
1621 x_msg_count => l_msg_count,
1622 x_msg_data => l_msg_data,
1623 p_legal_entity_id => p_legal_entity_id,
1624 x_registration_number => l_tax_registration
1625 );
1626
1627
1628 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1629 iby_debug_pub.add(debug_msg => 'Inserting tax reg number l_tax_registration : '|| l_tax_registration ||' in cache for p_legal_entity_id : '||l_key,
1630 debug_level => G_LEVEL_STATEMENT, module => l_debug_module);
1631 END IF;
1632
1633 g_payer_registration_tbl(l_key).registration_number:= l_tax_registration;
1634 l_registration_number := g_payer_registration_tbl(l_key).registration_number;
1635
1636 ELSE
1637
1638 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1639 iby_debug_pub.add(debug_msg => 'Reg.No found in the cache for p_legal_entity_id : '||l_key,
1640 debug_level => G_LEVEL_STATEMENT, module => l_debug_module);
1641 END IF;
1642
1643 l_registration_number := g_payer_registration_tbl(l_key).registration_number;
1644
1645
1646 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1647 iby_debug_pub.add(debug_msg => 'Fetched tax reg number l_registration_number : '|| l_registration_number ||' in cache for p_legal_entity_id : '||l_key,
1648 debug_level => G_LEVEL_STATEMENT, module => l_debug_module);
1649 END IF;
1650
1651 END IF;
1652 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1653 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
1654 debug_level => G_LEVEL_PROCEDURE,
1655 module => l_Debug_Module);
1656 END IF;
1657
1658 RETURN l_registration_number;
1659
1660 EXCEPTION
1661 -- swallow exceptions
1662 WHEN OTHERS THEN
1663 RETURN NULL;
1664
1665 END Get_FP_TaxRegistration;
1666
1667
1668
1669 FUNCTION Get_Payee_LegalRegistration(p_vendor_id IN NUMBER,
1670 p_vendor_site_id IN NUMBER,
1671 p_vendor_site_country IN VARCHAR2)
1672 RETURN VARCHAR2
1673 IS
1674
1675 l_legal_information_rec XLE_THIRDPARTY.LegalInformation_Rec;
1676 l_return_status VARCHAR2(1);
1677 l_msg_count NUMBER;
1678 l_msg_data VARCHAR2(2000);
1679
1680
1681 BEGIN
1682
1683 -- old code. XLE descoped third party APIs.
1684 -- current XLE implementation (xlethpab.pls 116.9) only supports
1685 -- supplier and is only for IT, ES and GR
1686 -- the implementation is based on 11i PO schema, rather than TCA
1687 -- basically the API selects PO_VENDORS.NUM_1099 as the
1688 -- registration number
1689 XLE_THIRDPARTY.Get_LegalInformation
1690 (
1691 p_api_version => 1.0,
1692 p_init_msg_list => fnd_api.g_false,
1693 p_commit => fnd_api.g_false,
1694 x_return_status => l_return_status,
1695 x_msg_count => l_msg_count,
1696 x_msg_data => l_msg_data,
1697 p_business_entity_type => 'SUPPLIER',
1698 p_business_entity_id => p_vendor_id,
1699 p_business_entity_site_id => p_vendor_site_id,
1700 p_country => p_vendor_site_country,
1701 p_legal_function => 'STIC',
1702 p_legislative_category => null,
1703 x_legal_information_rec => l_legal_information_rec
1704 );
1705 /*
1706 * frzhang 4/6/05. copied from XLE code
1707 *
1708
1709 -- ***** Business entity type is SUPPLIER *****
1710 -- For Italy
1711 CURSOR case1_legal_information_cur IS
1712 SELECT pvs.vendor_site_code,
1713 pv.num_1099,
1714 pv.global_attribute2,
1715 pv.global_attribute3,
1716 pv.standard_industry_class,
1717 pvs.address_line1,
1718 pvs.address_line2,
1719 pvs.address_line3,
1720 pvs.city,
1721 pvs.zip,
1722 pvs.province,
1723 pvs.country,
1724 pvs.state
1725 FROM PO_VENDOR_SITES_ALL pvs,
1726 PO_VENDORS pv
1727 WHERE pv.vendor_id=p_business_entity_id
1728 AND pvs.vendor_site_id=p_business_entity_site_id
1729 AND pvs.country=p_country
1730 AND pv.vendor_id=pvs.vendor_id;
1731
1732 -- For Spain, Greece
1733 CURSOR case2_legal_information_cur IS
1734 SELECT decode(pvs.country,'ES',pv.vendor_name,'GR',pvs.vendor_site_code),
1735 pv.num_1099,
1736 pv.global_attribute2,
1737 pv.global_attribute3,
1738 pv.standard_industry_class,
1739 pvs.address_line1,
1740 pvs.address_line2,
1741 pvs.address_line3,
1742 pvs.city,
1743 pvs.zip,
1744 pvs.province,
1745 pvs.country,
1746 pvs.state
1747 FROM PO_VENDOR_SITES_ALL pvs,
1748 PO_VENDORS pv
1749 WHERE pv.vendor_id=p_business_entity_id
1750 AND pvs.tax_reporting_site_flag='Y'
1751 AND pvs.country=p_country
1752 AND pv.vendor_id=pvs.vendor_id;
1753
1754
1755
1756 BEGIN
1757
1758 x_msg_count := NULL;
1759 x_msg_data := NULL;
1760
1761 -- Standard Start of API savepoint
1762 SAVEPOINT Get_LegalInformation;
1763
1764 -- Standard call to check for call compatibility.
1765 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1766 p_api_version,
1767 l_api_name,
1768 G_PKG_NAME ) THEN
1769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1770 END IF;
1771
1772 -- Initialize message list if p_init_msg_list is set to TRUE.
1773 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1774 FND_MSG_PUB.initialize;
1775 END IF;
1776
1777 -- Initialize API return status to success
1778 x_return_status := FND_API.G_RET_STS_SUCCESS;
1779
1780 -- ======== START OF API BODY ============
1781
1782 -- ***** Business entity type is SUPPLIER *****
1783 IF p_business_entity_type='SUPPLIER' THEN
1784
1785 -- Legal Information for Italy
1786 IF p_country='IT' THEN
1787
1788 OPEN case1_legal_information_cur;
1789 FETCH case1_legal_information_cur INTO
1790 x_legal_information_rec.legal_name,
1791 x_legal_information_rec.registration_number,
1792 x_legal_information_rec.date_of_birth,
1793 x_legal_information_rec.place_of_birth,
1794 x_legal_information_rec.company_activity_code,
1795 x_legal_information_rec.address_line1,
1796 x_legal_information_rec.address_line2,
1797 x_legal_information_rec.address_line3,
1798 x_legal_information_rec.city,
1799 x_legal_information_rec.zip,
1800 x_legal_information_rec.province,
1801 x_legal_information_rec.country,
1802 x_legal_information_rec.state;
1803
1804 IF case1_legal_information_cur%NOTFOUND THEN
1805 --specific xle message under creation fnd message used as workaround
1806 FND_MESSAGE.SET_NAME('FND','FND_GRANTS_RECORD_NOT_FOUND');
1807 FND_MSG_PUB.Add;
1808 x_return_status := FND_API.G_RET_STS_ERROR;
1809 END IF;
1810
1811
1812 CLOSE case1_legal_information_cur;
1813
1814
1815 -- Legal Information for Spain and Greece
1816 ELSIF p_country in ('ES','GR') THEN
1817
1818 OPEN case2_legal_information_cur;
1819 FETCH case2_legal_information_cur INTO
1820 x_legal_information_rec.legal_name,
1821 x_legal_information_rec.registration_number,
1822 x_legal_information_rec.date_of_birth,
1823 x_legal_information_rec.place_of_birth,
1824 x_legal_information_rec.company_activity_code,
1825 x_legal_information_rec.address_line1,
1826 x_legal_information_rec.address_line2,
1827 x_legal_information_rec.address_line3,
1828 x_legal_information_rec.city,
1829 x_legal_information_rec.zip,
1830 x_legal_information_rec.province,
1831 x_legal_information_rec.country,
1832 x_legal_information_rec.state;
1833
1834 IF case2_legal_information_cur%NOTFOUND THEN
1835 --specific xle message under creation fnd message used as workaround
1836 FND_MESSAGE.SET_NAME('FND','FND_GRANTS_RECORD_NOT_FOUND');
1837 FND_MSG_PUB.Add;
1838 x_return_status := FND_API.G_RET_STS_ERROR;
1839 END IF;
1840
1841
1842 CLOSE case2_legal_information_cur;
1843
1844 END IF;
1845
1846 END IF;
1847
1848
1849 -- End of API body.
1850 */
1851
1852
1853 RETURN l_legal_information_rec.registration_number;
1854
1855 EXCEPTION
1856 -- swallow exceptions
1857 WHEN OTHERS THEN
1858 RETURN NULL;
1859
1860 END Get_Payee_LegalRegistration;
1861
1862
1863 /**
1864 * This function calls an XLE wrapper API which in turn calls an eTax API
1865 * to get the VAT registration number.
1866 *
1867 * The parameter of this function follows the underlying XLE/ZX APIs.
1868 * See ZX_TCM_EXT_SERVICES_PUB.get_default_tax_reg()
1869 *
1870 * frzhang 4/6/05:
1871 * XLE scoped out third party LE APIs for R12. As for the Vat reg number
1872 * XLE was just providing a wrapper around ZX APIs, we will call the
1873 * ZX API directly.
1874 *
1875 *
1876 */
1877 FUNCTION Get_Payee_TaxRegistration(p_party_id IN NUMBER,
1878 p_supplier_site_id IN NUMBER)
1879 RETURN VARCHAR2
1880 IS
1881 l_registration_number VARCHAR2(50);
1882 l_return_status VARCHAR2(1);
1883 l_msg_count NUMBER;
1884 l_msg_data VARCHAR2(2000);
1885
1886 --Bug # 7412315
1887 l_party_type VARCHAR2(20);
1888 l_key VARCHAR2(100);
1889 l_debug_module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Payee_TaxRegistration';
1890
1891 BEGIN
1892 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1893 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
1894 debug_level => G_LEVEL_PROCEDURE,
1895 module => l_Debug_Module);
1896 END IF;
1897 --Bug # 7412315
1898 --Bug# 8670295
1899 IF NVL(p_supplier_site_id,'-1') = '-1' then
1900 l_party_type := 'THIRD_PARTY';
1901 l_key := p_party_id || l_party_type ;
1902 /* If the Registration Number is not found in the cache */
1903 IF (NOT(g_registration_tbl.EXISTS( l_key ))) THEN
1904 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1905 iby_debug_pub.add(debug_msg => 'Reg.No not found in the cache ',
1906 debug_level => G_LEVEL_STATEMENT, module => l_debug_module);
1907 END IF;
1908 g_registration_tbl(l_key).registration_number:=
1909 ZX_API_PUB.get_default_tax_reg
1910 (
1911 p_api_version => 1.0 ,
1912 p_init_msg_list => NULL,
1913 p_commit=> NULL,
1914 p_validation_level => NULL,
1915 x_return_status => l_return_status,
1916 x_msg_count => l_msg_count,
1917 x_msg_data => l_msg_data,
1918 p_party_id => p_party_id,
1919 p_party_type => l_party_type,
1920 p_effective_date => null );
1921 l_registration_number := g_registration_tbl(l_key).registration_number;
1922 ELSE
1923 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1924 iby_debug_pub.add(debug_msg => 'Reg.No found in the cache ',
1925 debug_level => G_LEVEL_STATEMENT, module => l_debug_module);
1926 END IF;
1927 l_registration_number := g_registration_tbl(l_key).registration_number;
1928 END IF;
1929
1930 else /* Supplier Site Id is not null */
1931 l_party_type := 'THIRD_PARTY_SITE';
1932 l_key := p_supplier_site_id || l_party_type ;
1933 IF (NOT(g_registration_tbl.EXISTS( l_key ))) THEN
1934 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1935 iby_debug_pub.add(debug_msg => 'Reg.No not found in the cache for the supplier ',
1936 debug_level => G_LEVEL_STATEMENT, module => l_debug_module);
1937 END IF;
1938 g_registration_tbl(l_key).registration_number:=
1939 NVL(ZX_API_PUB.get_default_tax_reg
1940 (
1941 p_api_version => 1.0 ,
1942 p_init_msg_list => NULL,
1943 p_commit=> NULL,
1944 p_validation_level => NULL,
1945 x_return_status => l_return_status,
1946 x_msg_count => l_msg_count,
1947 x_msg_data => l_msg_data,
1948 p_party_id => p_supplier_site_id,
1949 p_party_type => l_party_type,
1950 p_effective_date => null ),ZX_API_PUB.get_default_tax_reg
1951 (
1952 p_api_version => 1.0 ,
1953 p_init_msg_list => NULL,
1954 p_commit=> NULL,
1955 p_validation_level => NULL,
1956 x_return_status => l_return_status,
1957 x_msg_count => l_msg_count,
1958 x_msg_data => l_msg_data,
1959 p_party_id => p_party_id,
1960 p_party_type => 'THIRD_PARTY',
1961 p_effective_date => null ));
1962
1963 l_registration_number := g_registration_tbl(l_key).registration_number;
1964 ELSE
1965 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1966 iby_debug_pub.add(debug_msg => 'Reg.No found in the cache for the supplier ',
1967 debug_level => G_LEVEL_STATEMENT, module => l_debug_module);
1968 END IF;
1969 l_registration_number := g_registration_tbl(l_key).registration_number;
1970 END IF;
1971 END IF;
1972
1973 -- frzhang 4/6/05
1974 -- call directly to ZX API. copied from XLE code
1975
1976
1977 /*l_registration_number := ZX_API_PUB.get_default_tax_reg
1978 (
1979 p_api_version => 1.0 ,
1980 p_init_msg_list => NULL,
1981 p_commit=> NULL,
1982 p_validation_level => NULL,
1983 x_return_status => l_return_status,
1984 x_msg_count => l_msg_count,
1985 x_msg_data => l_msg_data,
1986 p_party_id => p_party_id,
1987 p_party_type => p_party_type,
1988 p_effective_date => null );*/ --Commented as part of Bug# 7412315
1989
1990 --Bug # 7412315
1991 /*l_registration_number := NVL(ZX_API_PUB.get_default_tax_reg
1992 (
1993 p_api_version => 1.0 ,
1994 p_init_msg_list => NULL,
1995 p_commit=> NULL,
1996 p_validation_level => NULL,
1997 x_return_status => l_return_status,
1998 x_msg_count => l_msg_count,
1999 x_msg_data => l_msg_data,
2000 p_party_id => NVL(p_supplier_site_id,p_party_id),
2001 p_party_type => l_party_type,
2002 p_effective_date => null ),ZX_API_PUB.get_default_tax_reg
2003 (
2004 p_api_version => 1.0 ,
2005 p_init_msg_list => NULL,
2006 p_commit=> NULL,
2007 p_validation_level => NULL,
2008 x_return_status => l_return_status,
2009 x_msg_count => l_msg_count,
2010 x_msg_data => l_msg_data,
2011 p_party_id => p_party_id,
2012 p_party_type => 'THIRD_PARTY',
2013 p_effective_date => null ));*/
2014
2015
2016 /* -- XLE descoped third party APIs
2017 XLE_THIRDPARTY.Get_TP_VATRegistration_PTY
2018 (
2019 p_api_version => 1.0,
2020 p_init_msg_list => fnd_api.g_false,
2021 p_commit => fnd_api.g_false,
2022 p_effective_date => SYSDATE,
2023 x_return_status => l_return_status,
2024 x_msg_count => l_msg_count,
2025 x_msg_data => l_msg_data,
2026 p_party_id => p_party_id,
2027 p_party_type => p_party_type,
2028 x_registration_number => l_registration_number
2029 );
2030 */
2031 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2032 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
2033 debug_level => G_LEVEL_PROCEDURE,
2034 module => l_Debug_Module);
2035 END IF;
2036
2037 RETURN l_registration_number;
2038
2039 EXCEPTION
2040 -- swallow exceptions
2041 WHEN OTHERS THEN
2042 RETURN NULL;
2043
2044 END Get_Payee_TaxRegistration;
2045
2046
2047 -- the party is the party that is linked to the LE
2048 -- on the payments
2049 FUNCTION Get_PayerContact(p_party_id IN NUMBER)
2050 RETURN XMLTYPE
2051 IS
2052 l_contactinfo XMLTYPE;
2053 l_phone_cp_id NUMBER;
2054 l_fax_cp_id NUMBER;
2055
2056 l_email VARCHAR2(2000);
2057 l_url VARCHAR2(2000);
2058
2059 l_hr_loc_phone VARCHAR2(60);
2060 l_hr_loc_fax VARCHAR2(60);
2061
2062
2063 CURSOR l_email_csr (p_owner_table_id IN NUMBER) IS
2064 SELECT email_address
2065 FROM hz_contact_points
2066 WHERE owner_table_name = 'HZ_PARTIES'
2067 AND owner_table_id = p_owner_table_id
2068 AND contact_point_type = 'EMAIL'
2069 AND primary_flag = 'Y'
2070 AND status = 'A';
2071
2072 -- bug 6044338. Fax and telephone numbers in the TCA data model
2073 -- are stored under the same contact_point_type PHONE. The
2074 -- difference is the phone_line_type. Since they are stored
2075 -- under the same contact_point_type the assume that there will
2076 -- only 1 primary phone or fax does not apply.
2077 -- The extract will display only 1 phone or fax as follows:
2078 -- 1. if the primary flag is set for it
2079 -- 2. if none of them are the primary contact point, the latest entered.
2080 CURSOR l_phone_csr (p_owner_table_id IN NUMBER) IS
2081 SELECT contact_point_id
2082 FROM (SELECT t.contact_point_id,
2083 t.primary_flag,
2084 t.phone_line_type,
2085 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
2086 FROM hz_contact_points t
2087 WHERE t.owner_table_name = 'HZ_PARTIES'
2088 AND t.owner_table_id = p_owner_table_id
2089 AND t.contact_point_type = 'PHONE'
2090 AND t.phone_line_type = 'GEN'
2091 AND t.status = 'A') x
2092 WHERE x.primary_phone = 1;
2093
2094 -- bug 6044338.
2095 CURSOR l_fax_csr (p_owner_table_id IN NUMBER) IS
2096 SELECT contact_point_id
2097 FROM (SELECT t.contact_point_id,
2098 t.primary_flag,
2099 t.phone_line_type,
2100 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
2101 FROM hz_contact_points t
2102 WHERE t.owner_table_name = 'HZ_PARTIES'
2103 AND t.owner_table_id = p_owner_table_id
2104 AND t.contact_point_type = 'PHONE'
2105 AND t.phone_line_type = 'FAX'
2106 AND t.status = 'A') x
2107 WHERE x.primary_phone = 1;
2108
2109
2110 CURSOR l_web_csr (p_owner_table_id IN NUMBER) IS
2111 SELECT url
2112 FROM hz_contact_points
2113 WHERE owner_table_name = 'HZ_PARTIES'
2114 AND owner_table_id = p_owner_table_id
2115 AND contact_point_type = 'WEB'
2116 AND primary_flag = 'Y'
2117 AND status = 'A';
2118
2119 CURSOR l_hr_loc_contact_csr (p_party_id IN NUMBER) IS
2120 SELECT TELEPHONE_NUMBER_1, TELEPHONE_NUMBER_2
2121 FROM hr_locations_all hr_loc, xle_firstparty_information_v xle_firstparty
2122 WHERE hr_loc.location_id = xle_firstparty.location_id
2123 AND xle_firstparty.party_id = p_party_id;
2124
2125 BEGIN
2126 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2127 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || G_Debug_Module || '.get_payer_contact'':: '||systimestamp,
2128 debug_level => G_LEVEL_PROCEDURE,
2129 module => G_Debug_Module || '.get_payer_contact');
2130 END IF;
2131
2132 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2133 iby_debug_pub.add(debug_msg => 'ENTER get_payer_contact',
2134 debug_level => G_LEVEL_STATEMENT,
2135 module => G_Debug_Module || '.get_payer_contact');
2136 END IF;
2137 -- Bug 7253633
2138 -- Checking to make sure that party_id IS NOT NULL
2139 -- Skip procedure if p_party_id IS NOT NULL;
2140 IF(p_party_id IS NOT NULL) THEN
2141 IF (NOT(g_payer_contact_tbl.EXISTS(p_party_id))) THEN
2142 OPEN l_email_csr (p_party_id);
2143 FETCH l_email_csr INTO l_email;
2144 CLOSE l_email_csr;
2145
2146 OPEN l_phone_csr (p_party_id);
2147 FETCH l_phone_csr INTO l_phone_cp_id;
2148 CLOSE l_phone_csr;
2149
2150 OPEN l_fax_csr (p_party_id);
2151 FETCH l_fax_csr INTO l_fax_cp_id;
2152 CLOSE l_fax_csr;
2153 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2154 iby_debug_pub.add(debug_msg => 'After getting mail, phone and fax',
2155 debug_level => G_LEVEL_STATEMENT,
2156 module => G_Debug_Module || '.get_payer_contact');
2157 END IF;
2158 OPEN l_web_csr (p_party_id);
2159 FETCH l_web_csr INTO l_url;
2160 CLOSE l_web_csr;
2161
2162 OPEN l_hr_loc_contact_csr (p_party_id);
2163 FETCH l_hr_loc_contact_csr INTO l_hr_loc_phone, l_hr_loc_fax;
2164 CLOSE l_hr_loc_contact_csr;
2165 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2166 iby_debug_pub.add(debug_msg => 'Before XML query',
2167 debug_level => G_LEVEL_STATEMENT,
2168 module => G_Debug_Module || '.get_payer_contact');
2169 END IF;
2170 -- the ContactName is left null
2171 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
2172 Removing the storing of XMLTYPE in cache.
2173 */
2174 g_payer_contact_tbl(p_party_id).phone_cp_id := l_phone_cp_id;
2175 g_payer_contact_tbl(p_party_id).fax_cp_id := l_fax_cp_id;
2176 g_payer_contact_tbl(p_party_id).email := l_email;
2177 g_payer_contact_tbl(p_party_id).url := l_url;
2178 g_payer_contact_tbl(p_party_id).hr_loc_phone := l_hr_loc_phone;
2179 g_payer_contact_tbl(p_party_id).hr_loc_fax := l_hr_loc_fax;
2180 END IF;
2181 ELSE
2182 RETURN null;
2183 END IF;
2184 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
2185 Removing the storing of XMLTYPE in cache.
2186 */
2187 l_phone_cp_id := g_payer_contact_tbl(p_party_id).phone_cp_id ;
2188 l_fax_cp_id := g_payer_contact_tbl(p_party_id).fax_cp_id ;
2189 l_email := g_payer_contact_tbl(p_party_id).email ;
2190 l_url := g_payer_contact_tbl(p_party_id).url ;
2191 l_hr_loc_phone := g_payer_contact_tbl(p_party_id).hr_loc_phone ;
2192 l_hr_loc_fax := g_payer_contact_tbl(p_party_id).hr_loc_fax ;
2193
2194 SELECT
2195 XMLElement("ContactLocators",
2196 XMLElement("PhoneNumber", nvl(hz_format_phone_v2pub.get_formatted_phone(l_phone_cp_id), l_hr_loc_phone)),
2197 XMLElement("FaxNumber", nvl(hz_format_phone_v2pub.get_formatted_phone(l_fax_cp_id), l_hr_loc_fax)),
2198 XMLElement("EmailAddress", l_email),
2199 XMLElement("Website", l_url)
2200 )
2201 INTO l_contactinfo
2202 FROM dual;
2203
2204 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2205 iby_debug_pub.add(debug_msg => 'EXIT get_payer_contact',
2206 debug_level => G_LEVEL_STATEMENT,
2207 module => G_Debug_Module || '.get_payer_contact');
2208 END IF;
2209
2210 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2211 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || G_Debug_Module || '.get_payer_contact'':: '||systimestamp,
2212 debug_level => G_LEVEL_PROCEDURE,
2213 module => G_Debug_Module || '.get_payer_contact');
2214 END IF;
2215 RETURN l_contactinfo;
2216
2217
2218 EXCEPTION
2219 WHEN OTHERS THEN
2220 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2221 iby_debug_pub.add(debug_msg => 'EXCEPTION -'||sqlerrm,
2222 debug_level => G_LEVEL_STATEMENT,
2223 module => G_Debug_Module || '.get_payer_contact');
2224 END IF;
2225
2226 Raise;
2227
2228 END Get_PayerContact;
2229
2230
2231 -- Add cache for owner_table_id and owner_Table_name, so this gets reduced
2232 -- will give most bang for the buck.
2233
2234 FUNCTION Get_PayeeContact(p_payment_id IN NUMBER)
2235 RETURN XMLTYPE
2236 IS
2237 l_contactinfo XMLTYPE;
2238 l_remit_to_loc_id NUMBER;
2239 l_party_site_id NUMBER;
2240 l_payee_party_id NUMBER;
2241 l_owner_table_name VARCHAR2(30);
2242 l_owner_table_id NUMBER;
2243 l_phone_cp_id NUMBER;
2244 l_fax_cp_id NUMBER;
2245 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayeeContact';
2246
2247 l_email VARCHAR2(2000);
2248 l_url VARCHAR2(2000);
2249
2250 CURSOR l_pmt_csr (p_payment_id IN NUMBER) IS
2251 SELECT remit_to_location_id, party_site_id, payee_party_id
2252 FROM iby_payments_all
2253 WHERE payment_id = p_payment_id;
2254
2255 CURSOR l_email_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
2256 SELECT email_address
2257 FROM hz_contact_points
2258 WHERE owner_table_name = p_owner_table_name
2259 AND owner_table_id = p_owner_table_id
2260 AND contact_point_type = 'EMAIL'
2261 AND primary_flag = 'Y'
2262 AND status = 'A';
2263
2264
2265 -- bug 6044338. Fax and telephone numbers in the TCA data model
2266 -- are stored under the same contact_point_type PHONE. The
2267 -- difference is the phone_line_type. Since they are stored
2268 -- under the same contact_point_type the assume that there will
2269 -- only 1 primary phone or fax does not apply.
2270 -- The extract will display only 1 phone or fax as follows:
2271 -- 1. if the primary flag is set for it
2272 -- 2. if none of them are the primary contact point, the latest entered.
2273 CURSOR l_phone_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
2274 SELECT contact_point_id
2275 FROM (SELECT t.contact_point_id,
2276 t.primary_flag,
2277 t.phone_line_type,
2278 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
2279 FROM hz_contact_points t
2280 WHERE t.owner_table_name = p_owner_table_name
2281 AND t.owner_table_id = p_owner_table_id
2282 AND t.contact_point_type = 'PHONE'
2283 AND t.phone_line_type = 'GEN'
2284 AND t.status = 'A') x
2285 WHERE x.primary_phone = 1;
2286
2287 -- bug 6044338.
2288 CURSOR l_fax_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
2289 SELECT contact_point_id
2290 FROM (SELECT t.contact_point_id,
2291 t.primary_flag,
2292 t.phone_line_type,
2293 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
2294 FROM hz_contact_points t
2295 WHERE t.owner_table_name = p_owner_table_name
2296 AND t.owner_table_id = p_owner_table_id
2297 AND t.contact_point_type = 'PHONE'
2298 AND t.phone_line_type = 'FAX'
2299 AND t.status = 'A') x
2300 WHERE x.primary_phone = 1;
2301
2302 CURSOR l_web_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
2303 SELECT url
2304 FROM hz_contact_points
2305 WHERE owner_table_name = p_owner_table_name
2306 AND owner_table_id = p_owner_table_id
2307 AND contact_point_type = 'WEB'
2308 AND primary_flag = 'Y'
2309 AND status = 'A';
2310
2311 BEGIN
2312
2313 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2314 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2315 debug_level => G_LEVEL_PROCEDURE,
2316 module => l_Debug_Module);
2317 END IF;
2318 OPEN l_pmt_csr (p_payment_id);
2319 FETCH l_pmt_csr INTO l_remit_to_loc_id, l_party_site_id, l_payee_party_id;
2320 CLOSE l_pmt_csr;
2321
2322 IF l_party_site_id IS NOT NULL THEN
2323 l_owner_table_name := 'HZ_PARTY_SITES';
2324 l_owner_table_id := l_party_site_id;
2325 ELSE
2326 l_owner_table_name := 'HZ_PARTIES';
2327 l_owner_table_id := l_payee_party_id;
2328 END IF;
2329
2330
2331 OPEN l_email_csr (l_owner_table_name, l_owner_table_id);
2332 FETCH l_email_csr INTO l_email;
2333 CLOSE l_email_csr;
2334
2335 OPEN l_phone_csr (l_owner_table_name, l_owner_table_id);
2336 FETCH l_phone_csr INTO l_phone_cp_id;
2337 CLOSE l_phone_csr;
2338
2339 OPEN l_fax_csr (l_owner_table_name, l_owner_table_id);
2340 FETCH l_fax_csr INTO l_fax_cp_id;
2341 CLOSE l_fax_csr;
2342
2343 OPEN l_web_csr (l_owner_table_name, l_owner_table_id);
2344 FETCH l_web_csr INTO l_url;
2345 CLOSE l_web_csr;
2346
2347 -- the ContactName is left null
2348 SELECT
2349 XMLElement("ContactLocators",
2350 XMLElement("PhoneNumber", hz_format_phone_v2pub.get_formatted_phone(l_phone_cp_id)),
2351 XMLElement("FaxNumber", hz_format_phone_v2pub.get_formatted_phone(l_fax_cp_id)),
2352 XMLElement("EmailAddress", l_email),
2353 XMLElement("Website", l_url)
2354 )
2355 INTO l_contactinfo
2356 FROM dual;
2357 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2358 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2359 debug_level => G_LEVEL_PROCEDURE,
2360 module => l_Debug_Module);
2361 END IF;
2362 RETURN l_contactinfo;
2363
2364 EXCEPTION
2365 WHEN OTHERS THEN
2366 RETURN NULL; /*Modified the line to return NULL*/
2367
2368 END Get_PayeeContact;
2369
2370
2371 /* Overloaded Function : Citi Perf */
2372 /* Also adding caching for Payee Contact. Given type of party or party_site
2373 we will be able to get the email,phone, fax details.*/
2374 FUNCTION Get_PayeeContact(p_payment_id IN NUMBER
2375 ,p_remit_to_location_id IN iby_payments_all.remit_to_location_id%TYPE
2376 ,p_party_site_id IN iby_payments_all.party_site_id%TYPE
2377 ,p_payee_party_id IN iby_payments_all.payee_party_id%TYPE)
2378 RETURN XMLTYPE
2379 IS
2380 l_contactinfo XMLTYPE;
2381 l_remit_to_loc_id NUMBER;
2382 l_party_site_id NUMBER;
2383 l_payee_party_id NUMBER;
2384 l_owner_table_name VARCHAR2(30);
2385 l_owner_table_id NUMBER;
2386 l_phone_cp_id NUMBER;
2387 l_fax_cp_id NUMBER;
2388 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayeeContact';
2389
2390 l_email VARCHAR2(2000);
2391 l_url VARCHAR2(2000);
2392
2393 /*
2394 CURSOR l_pmt_csr (p_payment_id IN NUMBER) IS
2395 SELECT remit_to_location_id, party_site_id, payee_party_id
2396 FROM iby_payments_all
2397 WHERE payment_id = p_payment_id;
2398 */
2399
2400 CURSOR l_email_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
2401 SELECT email_address
2402 FROM hz_contact_points
2403 WHERE owner_table_name = p_owner_table_name
2404 AND owner_table_id = p_owner_table_id
2405 AND contact_point_type = 'EMAIL'
2406 AND primary_flag = 'Y'
2407 AND status = 'A';
2408
2409
2410 -- bug 6044338. Fax and telephone numbers in the TCA data model
2411 -- are stored under the same contact_point_type PHONE. The
2412 -- difference is the phone_line_type. Since they are stored
2413 -- under the same contact_point_type the assume that there will
2414 -- only 1 primary phone or fax does not apply.
2415 -- The extract will display only 1 phone or fax as follows:
2416 -- 1. if the primary flag is set for it
2417 -- 2. if none of them are the primary contact point, the latest entered.
2418 CURSOR l_phone_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
2419 SELECT contact_point_id
2420 FROM (SELECT t.contact_point_id,
2421 t.primary_flag,
2422 t.phone_line_type,
2423 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
2424 FROM hz_contact_points t
2425 WHERE t.owner_table_name = p_owner_table_name
2426 AND t.owner_table_id = p_owner_table_id
2427 AND t.contact_point_type = 'PHONE'
2428 AND t.phone_line_type = 'GEN'
2429 AND t.status = 'A') x
2430 WHERE x.primary_phone = 1;
2431
2432 -- bug 6044338.
2433 CURSOR l_fax_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
2434 SELECT contact_point_id
2435 FROM (SELECT t.contact_point_id,
2436 t.primary_flag,
2437 t.phone_line_type,
2438 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
2439 FROM hz_contact_points t
2440 WHERE t.owner_table_name = p_owner_table_name
2441 AND t.owner_table_id = p_owner_table_id
2442 AND t.contact_point_type = 'PHONE'
2443 AND t.phone_line_type = 'FAX'
2444 AND t.status = 'A') x
2445 WHERE x.primary_phone = 1;
2446
2447 CURSOR l_web_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
2448 SELECT url
2449 FROM hz_contact_points
2450 WHERE owner_table_name = p_owner_table_name
2451 AND owner_table_id = p_owner_table_id
2452 AND contact_point_type = 'WEB'
2453 AND primary_flag = 'Y'
2454 AND status = 'A';
2455
2456 BEGIN
2457
2458 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2459 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2460 debug_level => G_LEVEL_PROCEDURE,
2461 module => l_Debug_Module);
2462 END IF;
2463 /*
2464 OPEN l_pmt_csr (p_payment_id);
2465 FETCH l_pmt_csr INTO l_remit_to_loc_id, l_party_site_id, l_payee_party_id;
2466 CLOSE l_pmt_csr;
2467 */
2468 l_remit_to_loc_id := p_remit_to_location_id;
2469 l_party_site_id := p_party_site_id;
2470 l_payee_party_id := p_payee_party_id;
2471
2472
2473 IF l_party_site_id IS NOT NULL THEN
2474 l_owner_table_name := 'HZ_PARTY_SITES';
2475 l_owner_table_id := l_party_site_id;
2476
2477 ELSE
2478 l_owner_table_name := 'HZ_PARTIES';
2479 l_owner_table_id := l_payee_party_id;
2480 END IF;
2481
2482 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
2483 Removing the storing in cache for payee contact.
2484 */
2485 -- Before calling cursors check if we have already fetched
2486 -- the same values
2487 /*
2488 IF site_contact_tab.EXISTS(l_party_site_id)
2489 THEN
2490 -- PARTY SITE DATA EXISTS
2491 l_email := site_contact_tab(l_party_site_id).l_email;
2492 l_phone_cp_id := site_contact_tab(l_party_site_id).l_phone_cp_id;
2493 l_fax_cp_id := site_contact_tab(l_party_site_id).l_fax_cp_id;
2494 l_url := site_contact_tab(l_party_site_id).l_url;
2495
2496 ELSIF party_contact_tab.EXISTS(l_payee_party_id)
2497 THEN
2498 -- PARTY DATA exists
2499 l_email := party_contact_tab(l_payee_party_id).l_email;
2500 l_phone_cp_id := party_contact_tab(l_payee_party_id).l_phone_cp_id;
2501 l_fax_cp_id := party_contact_tab(l_payee_party_id).l_fax_cp_id;
2502 l_url := party_contact_tab(l_payee_party_id).l_url;
2503 ELSE
2504 -- New values , must fetch from tables.
2505 */
2506 OPEN l_email_csr (l_owner_table_name, l_owner_table_id);
2507 FETCH l_email_csr INTO l_email;
2508 CLOSE l_email_csr;
2509
2510 OPEN l_phone_csr (l_owner_table_name, l_owner_table_id);
2511 FETCH l_phone_csr INTO l_phone_cp_id;
2512 CLOSE l_phone_csr;
2513
2514 OPEN l_fax_csr (l_owner_table_name, l_owner_table_id);
2515 FETCH l_fax_csr INTO l_fax_cp_id;
2516 CLOSE l_fax_csr;
2517
2518 OPEN l_web_csr (l_owner_table_name, l_owner_table_id);
2519 FETCH l_web_csr INTO l_url;
2520 CLOSE l_web_csr;
2521
2522 /*
2523 -- Assign values to cache : for party_site or party
2524 IF l_party_site_id IS NOT NULL THEN
2525
2526 site_contact_tab(l_party_site_id).l_email := l_email;
2527 site_contact_tab(l_party_site_id).l_phone_cp_id := l_phone_cp_id;
2528 site_contact_tab(l_party_site_id).l_fax_cp_id := l_fax_cp_id;
2529 site_contact_tab(l_party_site_id).l_url := l_url;
2530
2531 ELSE
2532 party_contact_tab(l_payee_party_id).l_email := l_email;
2533 party_contact_tab(l_payee_party_id).l_phone_cp_id := l_phone_cp_id;
2534 party_contact_tab(l_payee_party_id).l_fax_cp_id := l_fax_cp_id;
2535 party_contact_tab(l_payee_party_id).l_url := l_url;
2536
2537 END IF;
2538 */
2539
2540 -- END IF;
2541 -- the ContactName is left null
2542 SELECT
2543 XMLElement("ContactLocators",
2544 XMLElement("PhoneNumber", hz_format_phone_v2pub.get_formatted_phone(l_phone_cp_id)),
2545 XMLElement("FaxNumber", hz_format_phone_v2pub.get_formatted_phone(l_fax_cp_id)),
2546 XMLElement("EmailAddress", l_email),
2547 XMLElement("Website", l_url)
2548 )
2549 INTO l_contactinfo
2550 FROM dual;
2551 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2552 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2553 debug_level => G_LEVEL_PROCEDURE,
2554 module => l_Debug_Module);
2555 END IF;
2556 RETURN l_contactinfo;
2557
2558 EXCEPTION
2559 WHEN OTHERS THEN
2560 RETURN NULL; /*Modified the line to return NULL*/
2561
2562 END Get_PayeeContact;
2563 /*End of Overloaded function : Citi Perf*/
2564
2565
2566 FUNCTION format_hr_address(p_hr_location_id IN NUMBER,
2567 p_style_code IN VARCHAR2 DEFAULT NULL)
2568 RETURN VARCHAR2
2569 IS
2570
2571 l_formatted_address VARCHAR2(2000);
2572 l_address_line_1 VARCHAR2(240);
2573 l_address_line_2 VARCHAR2(240);
2574 l_address_line_3 VARCHAR2(240);
2575 l_city VARCHAR2(30);
2576 l_postal_code VARCHAR2(30);
2577 l_state VARCHAR2(120);
2578 l_county VARCHAR2(120);
2579 l_country VARCHAR2(60);
2580
2581 CURSOR l_hr_loc_csr (p_hr_location_id IN NUMBER) IS
2582 SELECT address_line_1, address_line_2, address_line_3,
2583 town_or_city, region_1, region_2,
2584 postal_code, country
2585 FROM hr_locations_all
2586 WHERE location_id = p_hr_location_id;
2587
2588 BEGIN
2589
2590 format_hr_address_C := format_hr_address_C + 1;
2591 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2592 iby_debug_pub.add(debug_msg => 'format_hr_address() entered. count: ' || format_hr_address_C,
2593 debug_level => G_LEVEL_STATEMENT,
2594 module => G_Debug_Module || '.format_hr_address');
2595 iby_debug_pub.add(debug_msg => 'p_hr_location_id: ' || p_hr_location_id,
2596 debug_level => G_LEVEL_STATEMENT,
2597 module => G_Debug_Module || '.format_hr_address');
2598 END IF;
2599
2600 IF p_hr_location_id IS NULL THEN
2601 RETURN NULL;
2602 END IF;
2603
2604 OPEN l_hr_loc_csr (p_hr_location_id);
2605 FETCH l_hr_loc_csr INTO l_address_line_1, l_address_line_2, l_address_line_3,
2606 l_city, l_county, l_state, l_postal_code, l_country;
2607 CLOSE l_hr_loc_csr;
2608
2609 l_formatted_address := hz_format_pub.format_address_lov(
2610 p_address_line_1 => l_address_line_1,
2611 p_address_line_2 => l_address_line_2,
2612 p_address_line_3 => l_address_line_3,
2613 p_address_line_4 => NULL,
2614 p_city => l_city,
2615 p_postal_code => l_postal_code,
2616 p_state => l_state,
2617 p_province => NULL,
2618 p_county => l_county,
2619 p_country => l_country,
2620 p_address_lines_phonetic => NULL
2621 );
2622
2623 RETURN l_formatted_address;
2624
2625 EXCEPTION
2626 WHEN OTHERS THEN
2627 NULL;
2628
2629 END format_hr_address;
2630
2631
2632 FUNCTION format_hz_address(p_hz_location_id IN NUMBER,
2633 p_style_code IN VARCHAR2 DEFAULT NULL)
2634 RETURN VARCHAR2
2635 IS
2636
2637 l_formatted_address VARCHAR2(4000);
2638 l_key VARCHAR2(2000);
2639
2640 BEGIN
2641
2642 format_hz_address_C := format_hz_address_C + 1;
2643 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2644 iby_debug_pub.add(debug_msg => 'format_hz_address() entered. count: ' || format_hz_address_C,
2645 debug_level => G_LEVEL_STATEMENT,
2646 module => G_Debug_Module || '.format_hz_address');
2647 iby_debug_pub.add(debug_msg => 'p_hz_location_id: ' || p_hz_location_id,
2648 debug_level => G_LEVEL_STATEMENT,
2649 module => G_Debug_Module || '.format_hz_address');
2650 END IF;
2651
2652 IF p_hz_location_id IS NULL THEN
2653 RETURN NULL;
2654 END IF;
2655
2656 l_key := p_hz_location_id || nvl(p_style_code,'-') ;
2657
2658 IF (NOT(g_formatted_hz_addr_tbl.EXISTS( l_key ))) THEN
2659 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2660 iby_debug_pub.add(debug_msg => 'Address not found in the cache.
2661 Executing the Cursor',
2662 debug_level => G_LEVEL_STATEMENT,
2663 module => G_Debug_Module || '.format_hz_address');
2664 END IF;
2665
2666 g_formatted_hz_addr_tbl(l_key).formatted_address := hz_format_pub.format_address(
2667 p_location_id => p_hz_location_id,
2668 p_style_code => p_style_code
2669 );
2670
2671 l_formatted_address := g_formatted_hz_addr_tbl(l_key).formatted_address;
2672
2673 ELSE
2674 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2675 iby_debug_pub.add(debug_msg => 'Address found in the cache.',
2676 debug_level => G_LEVEL_STATEMENT,
2677 module => G_Debug_Module || '.format_hz_address');
2678 END IF;
2679
2680 l_formatted_address := g_formatted_hz_addr_tbl(l_key).formatted_address;
2681
2682 END IF;
2683
2684 RETURN l_formatted_address;
2685
2686 EXCEPTION
2687 WHEN OTHERS THEN
2688 NULL;
2689
2690 END format_hz_address;
2691
2692
2693
2694 -- CE defines bank contact at three levels: bank, branch
2695 -- and bank account (internal ba only). Each level can have any number of contact
2696 -- persons. The contact are always based on contact person;
2697 -- in other words no contact points (email, phone, etc) linking
2698 -- directly to the bank/branch parties.
2699 -- CE uses CPUI to create the contact persons. CE always pass
2700 -- the bank party as the subject party of the relationship to
2701 -- create the org contact.
2702 -- A record in the HZ_RELATIONSHIPS is created with the contact
2703 -- person party as the subject_id, the bank party as the object_id
2704 -- and relationship_code = 'CONTACT_OF', directional_flag = 'F'.
2705 -- A record is created in the HZ_ORG_CONTACTS table
2706 -- with party_relationship_id = relationship_id
2707 -- A record is created in the HZ_ORG_CONTACT_ROLES table
2708 -- with the org_contact_id.
2709 -- There is a 'BANKING_CONTACT' role type, however CE is not
2710 -- setting any limit or default on the role type.
2711 -- The CE_CONTACT_ASSIGNMENTS table stores the contact
2712 -- assignments to the levels.
2713 --
2714 -- CE current primary contact person design is not clear.
2715 -- Also the bank/branch/account level contact filtering
2716 -- rule is not clear. Omar wanted to defer the bank contacts
2717 -- in the extract until the requirement arises.
2718 --
2719 -- FUNCTION Get_Int_BankContact(p_bank_account_id IN NUMBER)
2720 -- RETURN XMLTYPE
2721
2722
2723 FUNCTION Get_Pmt_DocPayableCount(p_payment_id IN NUMBER)
2724 RETURN NUMBER
2725 IS
2726 l_pmt_docpayablecount NUMBER;
2727 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Pmt_DocPayableCount';
2728 /* Performance Fix : 9184059
2729 CURSOR l_pmt_docpayablecount_csr (p_payment_id IN NUMBER) IS
2730 SELECT count(payment_id)
2731 FROM iby_xml_fd_doc_1_0_v xml_doc_lvl
2732 WHERE xml_doc_lvl.formatting_payment_id = p_payment_id; --bug 7006504
2733 */
2734 /* Perf Function : 9184059*/
2735 CURSOR l_pmt_docpayablecount_csr (p_payment_id IN NUMBER) IS
2736 SELECT count(payment_id)
2737 FROM iby_docs_payable_all xml_doc_lvl
2738 WHERE xml_doc_lvl.formatting_payment_id = p_payment_id
2739 AND xml_doc_lvl.document_status NOT IN
2740 ('FAILED_BY_CALLING_APP','FAILED_BY_REJECTION_LEVEL','FAILED_BY_RELATED_DOCUMENT','FAILED_VALIDATION','REJECTED', 'REMOVED'); --bug 7006504
2741
2742 /* Performance Fix : 9184059
2743 CURSOR l_docpayablecount_ppr_rpt_csr (p_payment_id IN NUMBER) IS
2744 SELECT count(payment_id)
2745 FROM iby_xml_fd_doc_1_0_v xml_doc_lvl
2746 WHERE xml_doc_lvl.payment_id = p_payment_id; --bug 7459662
2747 */
2748 CURSOR l_docpayablecount_ppr_rpt_csr (p_payment_id IN NUMBER) IS
2749 SELECT count(payment_id)
2750 FROM iby_docs_payable_all xml_doc_lvl
2751 WHERE xml_doc_lvl.payment_id = p_payment_id; --bug 7459662
2752
2753
2754 CURSOR l_docpayablecount_amex_csr (p_payment_id IN NUMBER) IS
2755 SELECT count(payment_id)
2756 FROM iby_docs_payable_all xml_doc_lvl
2757 WHERE xml_doc_lvl.payment_id = p_payment_id
2758 and xml_doc_lvl.document_status = 'PAYMENT_CREATED';
2759
2760 BEGIN
2761 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2762 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2763 debug_level => G_LEVEL_PROCEDURE,
2764 module => l_Debug_Module);
2765 END IF;
2766
2767 -- Bug 7459662 Begin
2768 IF G_Extract_Run_Mode = G_EXTRACT_MODE_PPR_RPT THEN
2769 OPEN l_docpayablecount_ppr_rpt_csr (p_payment_id);
2770 FETCH l_docpayablecount_ppr_rpt_csr INTO l_pmt_docpayablecount;
2771 CLOSE l_docpayablecount_ppr_rpt_csr;
2772
2773 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_AMEX_REM THEN
2774 OPEN l_docpayablecount_amex_csr (p_payment_id);
2775 FETCH l_docpayablecount_amex_csr INTO l_pmt_docpayablecount;
2776 CLOSE l_docpayablecount_amex_csr;
2777
2778 ELSE
2779 OPEN l_pmt_docpayablecount_csr (p_payment_id);
2780 FETCH l_pmt_docpayablecount_csr INTO l_pmt_docpayablecount;
2781 CLOSE l_pmt_docpayablecount_csr;
2782
2783 END IF;
2784 -- Bug 7459662 End
2785 /* Commented as part of Bug 7459662
2786 OPEN l_pmt_docpayablecount_csr(p_payment_id);
2787 FETCH l_pmt_docpayablecount_csr INTO l_pmt_docpayablecount;
2788 CLOSE l_pmt_docpayablecount_csr;*/
2789 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2790 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2791 debug_level => G_LEVEL_PROCEDURE,
2792 module => l_Debug_Module);
2793 END IF;
2794 RETURN l_pmt_docpayablecount;
2795
2796 END Get_Pmt_DocPayableCount;
2797
2798
2799 FUNCTION Get_Ins_FVFieldsAgg(p_payment_instruction_id IN NUMBER)
2800 RETURN XMLTYPE
2801 IS
2802 l_fv_summary_agg XMLTYPE;
2803 l_fv_treasury_symbol_agg XMLTYPE;
2804 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Ins_FVFieldsAgg';
2805
2806 CURSOR l_fv_treasury_symbol_csr (p_payment_instruction_id IN NUMBER) IS
2807 SELECT XMLAgg(
2808 XMLElement("TreasurySymbol",
2809 XMLElement("Name", fv.treasury_symbol),
2810 XMLElement("Amount",
2811 XMLElement("Value", fv.amount),
2812 XMLElement("Currency", XMLElement("Code", iby.payment_currency_code))
2813 )
2814 )
2815 )
2816 FROM fv_tp_ts_amt_data fv, iby_pay_instructions_all iby
2817 WHERE iby.payment_instruction_id = fv.payment_instruction_id
2818 AND iby.payment_instruction_id = p_payment_instruction_id;
2819
2820 CURSOR l_fv_summary_csr (p_payment_instruction_id IN NUMBER,
2821 p_fv_treasury_symbol_agg XMLTYPE) IS
2822 SELECT XMLElement("FederalInstructionInfo",
2823 XMLElement("TreasurySymbols", p_fv_treasury_symbol_agg),
2824 XMLElement("ControlNumber", control_number),
2825 XMLElement("ECSSummaryDosSeqNumber", iby_utility_pvt.get_view_param('FV_ECS_SEQ'))
2826 )
2827 FROM fv_summary_consolidate
2828 WHERE payment_instruction_id = p_payment_instruction_id;
2829
2830 BEGIN
2831
2832 IF G_Extract_Run_Mode = G_EXTRACT_MODE_FV_SMMY THEN
2833
2834 OPEN l_fv_treasury_symbol_csr(p_payment_instruction_id);
2835 FETCH l_fv_treasury_symbol_csr INTO l_fv_treasury_symbol_agg;
2836 CLOSE l_fv_treasury_symbol_csr;
2837
2838 OPEN l_fv_summary_csr(p_payment_instruction_id, l_fv_treasury_symbol_agg);
2839 FETCH l_fv_summary_csr INTO l_fv_summary_agg;
2840 CLOSE l_fv_summary_csr;
2841
2842 RETURN l_fv_summary_agg;
2843
2844 ELSE
2845 RETURN NULL;
2846 END IF;
2847
2848 END Get_Ins_FVFieldsAgg;
2849
2850
2851 FUNCTION Get_Ins_PayerInstrAgg(p_payment_instruction_id IN NUMBER)
2852 RETURN XMLTYPE
2853 IS
2854 l_payerinstr_agg XMLTYPE;
2855 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Ins_PayerInstrAgg';
2856 /* perf bug- 6763515 */
2857
2858
2859 -- for payment format: normal and reprint entire instruction
2860 CURSOR l_payerinstr_csr (p_payment_instruction_id IN NUMBER) IS
2861 SELECT XMLAgg(xml_pmt_lvl.payment)
2862 FROM
2863 iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
2864 IBY_PAY_INSTRUCTIONS_ALL ins
2865 WHERE
2866 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2867 AND ins.payment_instruction_id = xml_pmt_lvl.payment_instruction_id
2868 AND ((xml_pmt_lvl.payment_status in ('INSTRUCTION_CREATED',
2869 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW') AND ins.process_type = 'STANDARD') OR
2870 ins.process_type = 'IMMEDIATE');
2871
2872 -- for payment format: reprint individual and ranges
2873 CURSOR l_payerinstr_reprt_csr (p_payment_instruction_id IN NUMBER) IS
2874 SELECT XMLAgg(xml_pmt_lvl.payment)
2875 FROM
2876 iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
2877 IBY_PAY_INSTRUCTIONS_ALL ins
2878 WHERE
2879 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2880 AND ins.payment_instruction_id = xml_pmt_lvl.payment_instruction_id
2881 AND ((xml_pmt_lvl.payment_status in ('READY_TO_REPRINT',
2882 'VOID_BY_SETUP_REPRINT', 'VOID_BY_OVERFLOW_REPRINT') AND ins.process_type = 'STANDARD') OR
2883 ins.process_type = 'IMMEDIATE');
2884
2885 -- for payment instruction register
2886 -- we are extract payments in all statuses
2887 CURSOR l_payerinstr_rpt_csr (p_payment_instruction_id IN NUMBER) IS
2888 SELECT XMLAgg(xml_pmt_lvl.payment)
2889 FROM
2890 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2891 WHERE
2892 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id;
2893
2894
2895 -- for AMEX REMITTANCE PROGRAM
2896 CURSOR l_payerinstr_amex_csr (p_payment_instruction_id IN NUMBER) IS
2897 SELECT XMLAgg(xml_pmt_lvl.DOCS_AGG)
2898 FROM
2899 IBY_XML_FD_AMEX_PMT_1_0_V xml_pmt_lvl
2900 WHERE
2901 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2902 and xml_pmt_lvl.payment_status not in ('VOID', 'VOID_BY_OVERFLOW', 'VOID_BY_SETUP');
2903
2904
2905 -- for other auxiliary formats
2906 CURSOR l_payerinstr_aux_csr (p_payment_instruction_id IN NUMBER) IS
2907 SELECT XMLAgg(xml_pmt_lvl.payment)
2908 FROM
2909 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2910 WHERE
2911 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2912 AND xml_pmt_lvl.payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
2913 'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED', 'PAID');
2914
2915 -- for separate remittance advice electronic delivery: email and fax
2916 CURSOR l_payerinstr_sra_ele_csr (p_payment_instruction_id IN NUMBER) IS
2917 SELECT XMLAgg(xml_pmt_lvl.payment)
2918 FROM
2919 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2920 WHERE
2921 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2922 AND xml_pmt_lvl.payment_id = G_Extract_Run_Payment_id;
2923 -- note the status qualification is done in Java CP main driver cursor
2924
2925 -- for separate remittance advice print delivery
2926 CURSOR l_payerinstr_sra_prt_csr (p_payment_instruction_id IN NUMBER, p_from_pmt_ref IN NUMBER, p_to_pmt_ref IN NUMBER) IS
2927 SELECT XMLAgg(xml_pmt_lvl.payment)
2928 FROM
2929 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2930 WHERE
2931 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2932 AND xml_pmt_lvl.logical_reference_number between nvl(p_from_pmt_ref,xml_pmt_lvl.logical_reference_number)
2933 and nvl(p_to_pmt_ref,xml_pmt_lvl.logical_reference_number)
2934 AND (Get_SRA_Attribute(xml_pmt_lvl.payment_id, G_SRA_REQ_FLAG_ATTR) = 'Y' OR xml_pmt_lvl.payment_status ='VOID_BY_OVERFLOW')
2935 AND Get_SRA_Attribute(xml_pmt_lvl.payment_id, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
2936 AND xml_pmt_lvl.payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
2937 'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED', 'PAID','VOID_BY_OVERFLOW')
2938 ORDER BY xml_pmt_lvl.logical_reference_number;
2939
2940
2941 CURSOR l_rep_debug_pmt_csr (p_payment_instruction_id IN NUMBER) IS
2942 SELECT ext_pmt_v.payment_id, ext_pmt_v.paper_document_number, ext_pmt_v.payment_status
2943 FROM
2944 IBY_EXT_FD_PMT_1_0_V ext_pmt_v,
2945 IBY_PAY_INSTRUCTIONS_ALL ins
2946 WHERE
2947 ext_pmt_v.payment_instruction_id = p_payment_instruction_id
2948 AND ins.payment_instruction_id = ext_pmt_v.payment_instruction_id;
2949
2950 CURSOR l_rep_debug_ins_csr (p_payment_instruction_id IN NUMBER) IS
2951 SELECT ins.payment_instruction_status
2952 FROM
2953 IBY_PAY_INSTRUCTIONS_ALL ins
2954 WHERE
2955 ins.payment_instruction_id = p_payment_instruction_id;
2956
2957
2958 l_rep_ins_st VARCHAR2(30);
2959
2960 BEGIN
2961 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2962 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2963 debug_level => G_LEVEL_PROCEDURE,
2964 module => l_Debug_Module);
2965 END IF;
2966 print_log(l_Debug_Module, 'Enter Get_Ins_PayerInstrAgg -Timestamp:'||systimestamp);
2967
2968 IF G_Extract_Run_Mode is null OR G_Extract_Run_Mode = G_EXTRACT_MODE_PMT THEN
2969
2970
2971 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2972 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_PMT. ',
2973 debug_level => G_LEVEL_STATEMENT,
2974 module => l_Debug_Module);
2975
2976 iby_debug_pub.add(debug_msg => 'For reprint debugging: ',
2977 debug_level => G_LEVEL_STATEMENT,
2978 module => l_Debug_Module);
2979 END IF;
2980
2981 /* PERF BUG- 6763515 */
2982 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2983 OPEN l_rep_debug_ins_csr (p_payment_instruction_id);
2984 FETCH l_rep_debug_ins_csr INTO l_rep_ins_st;
2985 CLOSE l_rep_debug_ins_csr;
2986
2987 iby_debug_pub.add(debug_msg => 'instruction id: ' || p_payment_instruction_id
2988 || ', instruction status: ' || l_rep_ins_st,
2989 debug_level => G_LEVEL_STATEMENT,
2990 module => l_Debug_Module);
2991
2992 iby_debug_pub.add(debug_msg => 'payment id, paper document number, payment status for all payments in the instruction: ',
2993 debug_level => G_LEVEL_STATEMENT,
2994 module => l_Debug_Module);
2995 END IF;
2996 /* PERF BUG- 6763515 */
2997 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2998 FOR l_payment IN l_rep_debug_pmt_csr(p_payment_instruction_id) LOOP
2999
3000 iby_debug_pub.add(debug_msg => 'payment_id: ' || l_payment.payment_id
3001 || ', paper_document_number: ' || l_payment.paper_document_number
3002 || ', payment_status: ' || l_payment.payment_status,
3003 debug_level => G_LEVEL_STATEMENT,
3004 module => l_Debug_Module);
3005
3006 END LOOP;
3007 END IF;
3008
3009
3010
3011 IF nvl(G_Is_Reprint, 'N') = 'N' THEN
3012 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3013 iby_debug_pub.add(debug_msg => 'Before executing the cursor l_payerinstr_csr ',
3014 debug_level => G_LEVEL_STATEMENT,
3015 module => l_Debug_Module);
3016 END IF;
3017 OPEN l_payerinstr_csr (p_payment_instruction_id);
3018 FETCH l_payerinstr_csr INTO l_payerinstr_agg;
3019 CLOSE l_payerinstr_csr;
3020 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3021 iby_debug_pub.add(debug_msg => 'After executing the cursor l_payerinstr_csr ',
3022 debug_level => G_LEVEL_STATEMENT,
3023 module => l_Debug_Module);
3024 END IF;
3025
3026 ELSE
3027 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3028 iby_debug_pub.add(debug_msg => 'Before executing the cursor l_payerinstr_reprt_csr ',
3029 debug_level => G_LEVEL_STATEMENT,
3030 module => l_Debug_Module);
3031 END IF;
3032 OPEN l_payerinstr_reprt_csr (p_payment_instruction_id);
3033 FETCH l_payerinstr_reprt_csr INTO l_payerinstr_agg;
3034 CLOSE l_payerinstr_reprt_csr;
3035 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3036 iby_debug_pub.add(debug_msg => 'After executing the cursor l_payerinstr_reprt_csr ',
3037 debug_level => G_LEVEL_STATEMENT,
3038 module => l_Debug_Module);
3039 END IF;
3040
3041 END IF;
3042
3043 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_AUX THEN
3044 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3045 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_AUX. ',
3046 debug_level => G_LEVEL_STATEMENT,
3047 module => l_Debug_Module);
3048 END IF;
3049 OPEN l_payerinstr_aux_csr (p_payment_instruction_id);
3050 FETCH l_payerinstr_aux_csr INTO l_payerinstr_agg;
3051 CLOSE l_payerinstr_aux_csr;
3052
3053 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_PI_RPT THEN
3054 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3055 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_PI_RPT. ',
3056 debug_level => G_LEVEL_STATEMENT,
3057 module => l_Debug_Module);
3058 END IF;
3059 OPEN l_payerinstr_rpt_csr (p_payment_instruction_id);
3060 FETCH l_payerinstr_rpt_csr INTO l_payerinstr_agg;
3061 CLOSE l_payerinstr_rpt_csr;
3062
3063 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_AMEX_REM THEN
3064 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3065 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_AMEX_REM. ',
3066 debug_level => G_LEVEL_STATEMENT,
3067 module => l_Debug_Module);
3068 END IF;
3069 OPEN l_payerinstr_amex_csr (p_payment_instruction_id);
3070 FETCH l_payerinstr_amex_csr INTO l_payerinstr_agg;
3071 CLOSE l_payerinstr_amex_csr;
3072
3073 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_SRA THEN
3074
3075 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3076 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_SRA. ',
3077 debug_level => G_LEVEL_STATEMENT,
3078 module => l_Debug_Module);
3079 END IF;
3080
3081 IF G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_PRINTED THEN
3082
3083 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3084 iby_debug_pub.add(debug_msg => 'SRA Delivery method is printed. ',
3085 debug_level => G_LEVEL_STATEMENT,
3086 module => l_Debug_Module);
3087 END IF;
3088
3089 OPEN l_payerinstr_sra_prt_csr (p_payment_instruction_id, G_Extract_Run_From_Pmt_Ref, G_Extract_Run_To_Pmt_Ref);
3090 FETCH l_payerinstr_sra_prt_csr INTO l_payerinstr_agg;
3091 CLOSE l_payerinstr_sra_prt_csr;
3092
3093 ELSIF G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_EMAIL OR
3094 G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_FAX THEN
3095
3096 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3097 iby_debug_pub.add(debug_msg => 'SRA Delivery method is Email/Fax. ',
3098 debug_level => G_LEVEL_STATEMENT,
3099 module => l_Debug_Module);
3100 END IF;
3101
3102 OPEN l_payerinstr_sra_ele_csr (p_payment_instruction_id);
3103 FETCH l_payerinstr_sra_ele_csr INTO l_payerinstr_agg;
3104 CLOSE l_payerinstr_sra_ele_csr;
3105
3106 END IF;
3107
3108 END IF;
3109 print_log(l_Debug_Module, 'Exit Get_Ins_PayerInstrAgg -Timestamp:'||systimestamp);
3110
3111 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3112 IF l_payerinstr_agg is null THEN
3113 iby_debug_pub.add(debug_msg => 'After fetch from payer instrument cursor. l_payerinstr_agg is null',
3114 debug_level => G_LEVEL_STATEMENT,
3115 module => l_Debug_Module);
3116
3117 ELSE
3118 iby_debug_pub.add(debug_msg => 'After fetch from payer instrument cursor. l_payerinstr_agg is not null',
3119 debug_level => G_LEVEL_STATEMENT,
3120 module => l_Debug_Module);
3121
3122 END IF;
3123
3124 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3125 debug_level => G_LEVEL_PROCEDURE,
3126 module => l_Debug_Module);
3127 END IF;
3128
3129 RETURN l_payerinstr_agg;
3130 EXCEPTION
3131 WHEN OTHERS THEN
3132 iby_debug_pub.add(debug_msg => 'EXECPTION OCCURED IN : ' || l_Debug_Module || sqlerrm ,
3133 debug_level => G_LEVEL_PROCEDURE,
3134 module => l_Debug_Module);
3135 RAISE;
3136 END Get_Ins_PayerInstrAgg;
3137
3138 FUNCTION Get_Payer(p_legal_entity_id IN NUMBER)
3139 RETURN XMLTYPE
3140 IS
3141 l_payer XMLTYPE;
3142 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Payer';
3143 CURSOR l_payer_csr (p_legal_entity_id IN NUMBER) IS
3144 SELECT payer
3145 FROM iby_xml_fd_payer_1_0_v
3146 WHERE legal_entity_id = p_legal_entity_id;
3147
3148 BEGIN
3149 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3150 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3151 debug_level => G_LEVEL_PROCEDURE,
3152 module => l_Debug_Module);
3153 END IF;
3154 Get_Payer_C := Get_Payer_C + 1;
3155 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3156 iby_debug_pub.add(debug_msg => 'Get_Payer() entered. count: ' || Get_Payer_C,
3157 debug_level => G_LEVEL_STATEMENT,
3158 module => G_Debug_Module || '.Get_Payer');
3159 iby_debug_pub.add(debug_msg => 'p_legal_entity_id: ' || p_legal_entity_id,
3160 debug_level => G_LEVEL_STATEMENT,
3161 module => G_Debug_Module || '.Get_Payer');
3162 END IF;
3163
3164 IF p_legal_entity_id IS NULL THEN
3165 RETURN NULL;
3166 END IF;
3167
3168 OPEN l_payer_csr (p_legal_entity_id);
3169 FETCH l_payer_csr INTO l_payer;
3170 CLOSE l_payer_csr;
3171
3172 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3173 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3174 debug_level => G_LEVEL_PROCEDURE,
3175 module => l_Debug_Module);
3176 END IF;
3177
3178 RETURN l_payer;
3179
3180 END Get_Payer;
3181
3182
3183 FUNCTION Get_PayerBankAccount(p_bank_account_id IN NUMBER)
3184 RETURN XMLTYPE
3185 IS
3186 l_payer_ba XMLTYPE;
3187 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayerBankAccount';
3188 CURSOR l_payer_ba_csr (p_bank_account_id IN NUMBER) IS
3189 SELECT int_bank_account
3190 FROM iby_xml_fd_prba_1_0_v
3191 WHERE bank_account_id = p_bank_account_id;
3192
3193 BEGIN
3194 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3195 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3196 debug_level => G_LEVEL_PROCEDURE,
3197 module => l_Debug_Module);
3198 END IF;
3199 Get_PayerBankAccount_C := Get_PayerBankAccount_C + 1;
3200 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3201 iby_debug_pub.add(debug_msg => 'Get_PayerBankAccount() entered. count: ' || Get_PayerBankAccount_C,
3202 debug_level => G_LEVEL_STATEMENT,
3203 module => G_Debug_Module || '.Get_PayerBankAccount');
3204 iby_debug_pub.add(debug_msg => 'p_bank_account_id: ' || p_bank_account_id,
3205 debug_level => G_LEVEL_STATEMENT,
3206 module => G_Debug_Module || '.Get_PayerBankAccount');
3207 END IF;
3208 IF p_bank_account_id IS NULL THEN
3209 RETURN NULL;
3210 END IF;
3211
3212 OPEN l_payer_ba_csr (p_bank_account_id);
3213 FETCH l_payer_ba_csr INTO l_payer_ba;
3214 CLOSE l_payer_ba_csr;
3215
3216
3217 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3218 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3219 debug_level => G_LEVEL_PROCEDURE,
3220 module => l_Debug_Module);
3221 END IF;
3222 RETURN l_payer_ba;
3223
3224 END Get_PayerBankAccount;
3225
3226
3227 FUNCTION Get_Payer_Denorm(p_payment_id IN NUMBER)
3228 RETURN XMLTYPE
3229 IS
3230 l_payer XMLTYPE;
3231 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Payer_Denorm';
3232 CURSOR l_payer_csr (p_payment_id IN NUMBER) IS
3233 SELECT payer
3234 FROM iby_xml_fd_payer_1_0_v
3235 WHERE payment_id = p_payment_id;
3236
3237 BEGIN
3238
3239 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3240 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3241 debug_level => G_LEVEL_PROCEDURE,
3242 module => l_Debug_Module);
3243 END IF;
3244 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3245 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3246 debug_level => G_LEVEL_PROCEDURE,
3247 module => l_Debug_Module);
3248 END IF;
3249 Get_Payer_C := Get_Payer_C + 1;
3250 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3251 iby_debug_pub.add(debug_msg => 'Get_Payer_Denorm() entered. count: ' || Get_Payer_C,
3252 debug_level => G_LEVEL_STATEMENT,
3253 module => G_Debug_Module || '.Get_Payer');
3254 iby_debug_pub.add(debug_msg => 'p_payment_id: ' || p_payment_id,
3255 debug_level => G_LEVEL_STATEMENT,
3256 module => G_Debug_Module || '.Get_Payer');
3257 END IF;
3258
3259 OPEN l_payer_csr (p_payment_id);
3260 FETCH l_payer_csr INTO l_payer;
3261 CLOSE l_payer_csr;
3262
3263 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3264 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3265 debug_level => G_LEVEL_PROCEDURE,
3266 module => l_Debug_Module);
3267 END IF;
3268
3269 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3270 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3271 debug_level => G_LEVEL_PROCEDURE,
3272 module => l_Debug_Module);
3273 END IF;
3274 RETURN l_payer;
3275
3276 END Get_Payer_Denorm;
3277
3278
3279 FUNCTION Get_PayerBankAccount_Denorm(p_payment_id IN NUMBER)
3280 RETURN XMLTYPE
3281 IS
3282 l_payer_ba XMLTYPE;
3283 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayerBankAccount_Denorm';
3284 CURSOR l_payer_ba_csr (p_payment_id IN NUMBER) IS
3285 SELECT int_bank_account
3286 FROM iby_xml_fd_prba_1_0_v
3287 WHERE payment_id = p_payment_id;
3288
3289 BEGIN
3290
3291 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3292 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3293 debug_level => G_LEVEL_PROCEDURE,
3294 module => l_Debug_Module);
3295 END IF;
3296 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3297 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3298 debug_level => G_LEVEL_PROCEDURE,
3299 module => l_Debug_Module);
3300 END IF;
3301 Get_PayerBankAccount_C := Get_PayerBankAccount_C + 1;
3302 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3303 iby_debug_pub.add(debug_msg => 'Get_PayerBankAccount() entered. count: ' || Get_PayerBankAccount_C,
3304 debug_level => G_LEVEL_STATEMENT,
3305 module => G_Debug_Module || '.Get_PayerBankAccount');
3306 iby_debug_pub.add(debug_msg => 'p_payment_id: ' || p_payment_id,
3307 debug_level => G_LEVEL_STATEMENT,
3308 module => G_Debug_Module || '.Get_PayerBankAccount');
3309 END IF;
3310 OPEN l_payer_ba_csr (p_payment_id);
3311 FETCH l_payer_ba_csr INTO l_payer_ba;
3312 CLOSE l_payer_ba_csr;
3313
3314 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3315 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3316 debug_level => G_LEVEL_PROCEDURE,
3317 module => l_Debug_Module);
3318 END IF;
3319
3320 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3321 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3322 debug_level => G_LEVEL_PROCEDURE,
3323 module => l_Debug_Module);
3324 END IF;
3325 RETURN l_payer_ba;
3326
3327 END Get_PayerBankAccount_Denorm;
3328
3329
3330 FUNCTION Get_PayerIns_Denorm(p_payment_instruction_id IN NUMBER)
3331 RETURN XMLTYPE
3332 IS
3333 l_payment_id NUMBER;
3334 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayerIns_Denorm';
3335 CURSOR l_ins_payment_csr (p_payment_instruction_id IN NUMBER) IS
3336 SELECT payment_id
3337 FROM iby_payments_all
3338 WHERE payment_instruction_id = p_payment_instruction_id
3339 AND ROWNUM = 1;
3340
3341 BEGIN
3342 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3343 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3344 debug_level => G_LEVEL_PROCEDURE,
3345 module => l_Debug_Module);
3346 END IF;
3347 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3348 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3349 debug_level => G_LEVEL_PROCEDURE,
3350 module => l_Debug_Module);
3351 END IF;
3352
3353 OPEN l_ins_payment_csr (p_payment_instruction_id);
3354 FETCH l_ins_payment_csr INTO l_payment_id;
3355 CLOSE l_ins_payment_csr;
3356
3357 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3358 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3359 debug_level => G_LEVEL_PROCEDURE,
3360 module => l_Debug_Module);
3361 END IF;
3362
3363 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3364 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3365 debug_level => G_LEVEL_PROCEDURE,
3366 module => l_Debug_Module);
3367 END IF;
3368 RETURN Get_Payer_Denorm(l_payment_id);
3369
3370 END Get_PayerIns_Denorm;
3371
3372
3373 FUNCTION Get_PayerBankAccountIns_Denorm(p_payment_instruction_id IN NUMBER)
3374 RETURN XMLTYPE
3375 IS
3376 l_payment_id NUMBER;
3377 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayerBankAccountIns_Denorm';
3378
3379 /*Bug 8662990 - sort by payment_id to get a non void-by-overflow payment*/
3380 CURSOR l_ins_payment_csr (p_payment_instruction_id IN NUMBER) IS
3381 SELECT min(payment_id)
3382 FROM iby_payments_all
3383 WHERE payment_instruction_id = p_payment_instruction_id;
3384
3385 BEGIN
3386 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3387 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3388 debug_level => G_LEVEL_PROCEDURE,
3389 module => l_Debug_Module);
3390 END IF;
3391 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3392 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3393 debug_level => G_LEVEL_PROCEDURE,
3394 module => l_Debug_Module);
3395 END IF;
3396
3397 OPEN l_ins_payment_csr (p_payment_instruction_id);
3398 FETCH l_ins_payment_csr INTO l_payment_id;
3399 CLOSE l_ins_payment_csr;
3400
3401 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3402 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3403 debug_level => G_LEVEL_PROCEDURE,
3404 module => l_Debug_Module);
3405 END IF;
3406
3407 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3408 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3409 debug_level => G_LEVEL_PROCEDURE,
3410 module => l_Debug_Module);
3411 END IF;
3412 RETURN Get_PayerBankAccount_Denorm(l_payment_id);
3413
3414 END Get_PayerBankAccountIns_Denorm;
3415
3416
3417 FUNCTION Get_Ins_AccountSettingsAgg(p_bep_account_id IN NUMBER)
3418 RETURN XMLTYPE
3419 IS
3420 l_acctsettings_agg XMLTYPE;
3421
3422 CURSOR l_acctsettings_csr (p_bep_account_id IN NUMBER) IS
3423 SELECT XMLAgg(account_setting)
3424 FROM iby_xml_fd_acct_settings_1_0_v
3425 WHERE bep_account_id = p_bep_account_id;
3426
3427 BEGIN
3428
3429 OPEN l_acctsettings_csr (p_bep_account_id);
3430 FETCH l_acctsettings_csr INTO l_acctsettings_agg;
3431 CLOSE l_acctsettings_csr;
3432
3433 RETURN l_acctsettings_agg;
3434
3435 END Get_Ins_AccountSettingsAgg;
3436
3437
3438
3439 FUNCTION Get_Pmt_DocPayableAgg(p_payment_id IN NUMBER)
3440 RETURN XMLTYPE
3441 IS
3442 l_docpayable_agg XMLTYPE;
3443 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Pmt_DocPayableAgg';
3444
3445 CURSOR l_docpayable_csr (p_payment_id IN NUMBER) IS
3446 SELECT XMLAgg(doc_payable)
3447 FROM iby_xml_fd_doc_1_0_v
3448 WHERE formatting_payment_id = p_payment_id --bug 7006504
3449 AND document_status <> 'REMOVED';
3450
3451 -- bug 11839898
3452 -- void by overflow documents should be printed under the real paying check
3453 CURSOR l_docpayable_sra_csr (p_payment_id IN NUMBER) IS
3454 SELECT XMLAgg(doc_payable)
3455 FROM iby_xml_fd_doc_1_0_v
3456 WHERE payment_id = p_payment_id
3457 AND document_status <> 'REMOVED';
3458
3459 -- for ppr report we need to filter the docs by MOAC accessibility check
3460 CURSOR l_docpayable_ppr_rpt_csr (p_payment_id IN NUMBER) IS
3461 SELECT XMLAgg(doc_payable)
3462 FROM iby_xml_fd_doc_1_0_v xml_doc, iby_docs_payable_all doc, ce_security_profiles_v ce_sp
3463 WHERE xml_doc.payment_id = p_payment_id --bug 7459662
3464 AND xml_doc.document_payable_id = doc.document_payable_id
3465 AND ce_sp.organization_type = doc.org_type
3466 AND ce_sp.organization_id = doc.org_id
3467 AND xml_doc.document_status <> 'REMOVED';
3468
3469 CURSOR l_docpayable_amex_csr (p_payment_id IN NUMBER) IS
3470 SELECT XMLAgg(doc_payable)
3471 FROM iby_xml_fd_doc_1_0_v
3472 WHERE formatting_payment_id = p_payment_id
3473 AND document_status = 'PAYMENT_CREATED';
3474
3475 BEGIN
3476 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3477 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3478 debug_level => G_LEVEL_PROCEDURE,
3479 module => l_Debug_Module);
3480 END IF;
3481 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3482 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3483 debug_level => G_LEVEL_PROCEDURE,
3484 module => l_Debug_Module);
3485
3486 iby_debug_pub.add(debug_msg => 'input p_payment_id: ' || p_payment_id,
3487 debug_level => G_LEVEL_STATEMENT,
3488 module => l_Debug_Module);
3489 END IF;
3490
3491 IF G_Extract_Run_Mode = G_EXTRACT_MODE_PPR_RPT THEN
3492 OPEN l_docpayable_ppr_rpt_csr (p_payment_id);
3493 FETCH l_docpayable_ppr_rpt_csr INTO l_docpayable_agg;
3494 CLOSE l_docpayable_ppr_rpt_csr;
3495
3496 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_AMEX_REM THEN
3497 OPEN l_docpayable_amex_csr (p_payment_id);
3498 FETCH l_docpayable_amex_csr INTO l_docpayable_agg;
3499 CLOSE l_docpayable_amex_csr;
3500
3501 -- bug 11839898
3502 -- void by overflow documents should be printed under the real paying check
3503 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_SRA THEN
3504 OPEN l_docpayable_sra_csr (p_payment_id);
3505 FETCH l_docpayable_sra_csr INTO l_docpayable_agg;
3506 CLOSE l_docpayable_sra_csr;
3507
3508 ELSE
3509 OPEN l_docpayable_csr (p_payment_id);
3510 FETCH l_docpayable_csr INTO l_docpayable_agg;
3511 CLOSE l_docpayable_csr;
3512
3513 END IF;
3514
3515 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3516 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3517 debug_level => G_LEVEL_PROCEDURE,
3518 module => l_Debug_Module);
3519 END IF;
3520
3521 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3522 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3523 debug_level => G_LEVEL_PROCEDURE,
3524 module => l_Debug_Module);
3525 END IF;
3526
3527 RETURN l_docpayable_agg;
3528 EXCEPTION
3529 WHEN OTHERS THEN
3530 iby_debug_pub.add(debug_msg => 'EXECPTION OCCURED IN : ' || l_Debug_Module || sqlerrm ,
3531 debug_level => G_LEVEL_PROCEDURE,
3532 module => l_Debug_Module);
3533 RAISE;
3534 END Get_Pmt_DocPayableAgg;
3535
3536
3537 FUNCTION Get_Payee(p_payment_id IN NUMBER)
3538 RETURN XMLTYPE
3539 IS
3540 l_payee XMLTYPE;
3541 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Payee';
3542 l_pmt_func VARCHAR2(1);
3543
3544 CURSOR l_pmt_func_csr (p_payment_id IN NUMBER) IS
3545 SELECT nvl(employee_payment_flag, 'N')
3546 FROM iby_payments_all
3547 WHERE payment_id = p_payment_id;
3548
3549 CURSOR l_payee_csr (p_payment_id IN NUMBER) IS
3550 SELECT payee
3551 FROM iby_xml_fd_payee_1_0_v
3552 WHERE payment_id = p_payment_id;
3553
3554 CURSOR l_payeem_csr (p_payment_id IN NUMBER) IS
3555 SELECT payee
3556 FROM iby_xml_fd_payeem_1_0_v
3557 WHERE payment_id = p_payment_id;
3558
3559 BEGIN
3560
3561 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3562 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3563 debug_level => G_LEVEL_PROCEDURE,
3564 module => l_Debug_Module);
3565
3566 iby_debug_pub.add(debug_msg => 'PaymentId ::' || p_payment_id,
3567 debug_level => G_LEVEL_PROCEDURE,
3568 module => l_Debug_Module);
3569 END IF;
3570 OPEN l_pmt_func_csr (p_payment_id);
3571 FETCH l_pmt_func_csr INTO l_pmt_func;
3572 CLOSE l_pmt_func_csr;
3573 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3574 iby_debug_pub.add(debug_msg => 'Before the condition - ' || ' l_pmt_func:' || l_pmt_func,
3575 debug_level => G_LEVEL_PROCEDURE,
3576 module => l_Debug_Module);
3577 END IF;
3578 IF l_pmt_func = 'Y' AND G_May_Need_HR_Masking THEN
3579
3580 OPEN l_payeem_csr (p_payment_id);
3581 FETCH l_payeem_csr INTO l_payee;
3582 CLOSE l_payeem_csr;
3583
3584 ELSE
3585
3586 OPEN l_payee_csr (p_payment_id);
3587 FETCH l_payee_csr INTO l_payee;
3588 CLOSE l_payee_csr;
3589
3590 END IF;
3591
3592 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3593 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3594 debug_level => G_LEVEL_PROCEDURE,
3595 module => l_Debug_Module);
3596 END IF;
3597
3598 RETURN l_payee;
3599
3600 END Get_Payee;
3601
3602 /*Perf Fixes : Citi Will add bug tag soon */
3603 /* Overloaded Function */
3604
3605
3606 FUNCTION Get_Payee(p_payment_id IN NUMBER,
3607 p_pmt_func IN VARCHAR2)
3608 RETURN XMLTYPE
3609 IS
3610 l_payee XMLTYPE;
3611 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Payee(payment_id, pmt_func)';
3612 l_pmt_func VARCHAR2(1);
3613
3614 CURSOR l_pmt_func_csr (p_payment_id IN NUMBER) IS
3615 SELECT nvl(employee_payment_flag, 'N')
3616 FROM iby_payments_all
3617 WHERE payment_id = p_payment_id;
3618
3619 CURSOR l_payee_csr (p_payment_id IN NUMBER) IS
3620 SELECT payee
3621 FROM iby_xml_fd_payee_1_0_v
3622 WHERE payment_id = p_payment_id;
3623
3624 CURSOR l_payeem_csr (p_payment_id IN NUMBER) IS
3625 SELECT payee
3626 FROM iby_xml_fd_payeem_1_0_v
3627 WHERE payment_id = p_payment_id;
3628
3629 BEGIN
3630 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3631 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3632 debug_level => G_LEVEL_PROCEDURE,
3633 module => l_Debug_Module);
3634 END IF;
3635 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3636 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3637 debug_level => G_LEVEL_PROCEDURE,
3638 module => l_Debug_Module);
3639
3640 iby_debug_pub.add(debug_msg => 'PaymentId::' || p_payment_id,
3641 debug_level => G_LEVEL_PROCEDURE,
3642 module => l_Debug_Module);
3643 END IF;
3644 /* Will pass through view
3645 OPEN l_pmt_func_csr (p_payment_id);
3646 FETCH l_pmt_func_csr INTO l_pmt_func;
3647 CLOSE l_pmt_func_csr;
3648 */
3649 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3650 iby_debug_pub.add(debug_msg => 'Before the condition - ' || ' p_pmt_func:' || p_pmt_func,
3651 debug_level => G_LEVEL_PROCEDURE,
3652 module => l_Debug_Module);
3653 END IF;
3654 IF p_pmt_func = 'Y' AND G_May_Need_HR_Masking THEN
3655
3656 OPEN l_payeem_csr (p_payment_id);
3657 FETCH l_payeem_csr INTO l_payee;
3658 CLOSE l_payeem_csr;
3659
3660 ELSE
3661
3662 OPEN l_payee_csr (p_payment_id);
3663 FETCH l_payee_csr INTO l_payee;
3664 CLOSE l_payee_csr;
3665
3666 END IF;
3667
3668 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3669 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3670 debug_level => G_LEVEL_PROCEDURE,
3671 module => l_Debug_Module);
3672 END IF;
3673 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3674 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3675 debug_level => G_LEVEL_PROCEDURE,
3676 module => l_Debug_Module);
3677 END IF;
3678
3679 RETURN l_payee;
3680
3681 END Get_Payee;
3682 /* End of overloaded function */
3683
3684
3685 /* TPP - Start */
3686 FUNCTION Get_InvPayee(p_payment_id IN NUMBER)
3687 RETURN XMLTYPE
3688 IS
3689 l_payee XMLTYPE;
3690 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_InvPayee';
3691
3692 CURSOR l_payee_csr (p_payment_id IN NUMBER) IS
3693 SELECT payee
3694 FROM iby_xml_fd_invpayee_1_0_v
3695 WHERE payment_id = p_payment_id;
3696
3697 BEGIN
3698 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3699 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3700 debug_level => G_LEVEL_PROCEDURE,
3701 module => l_Debug_Module);
3702 END IF;
3703 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3704 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3705 debug_level => G_LEVEL_PROCEDURE,
3706 module => l_Debug_Module);
3707
3708 iby_debug_pub.add(debug_msg => 'PaymentId ::' || p_payment_id,
3709 debug_level => G_LEVEL_PROCEDURE,
3710 module => l_Debug_Module);
3711 END IF;
3712
3713 OPEN l_payee_csr (p_payment_id);
3714 FETCH l_payee_csr INTO l_payee;
3715 CLOSE l_payee_csr;
3716
3717
3718 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3719 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3720 debug_level => G_LEVEL_PROCEDURE,
3721 module => l_Debug_Module);
3722 END IF;
3723 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3724 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3725 debug_level => G_LEVEL_PROCEDURE,
3726 module => l_Debug_Module);
3727 END IF;
3728
3729 RETURN l_payee;
3730
3731 END Get_InvPayee;
3732
3733
3734
3735 FUNCTION get_rel_add_info(
3736 payee_party_id IN NUMBER,
3737 supplier_site_id IN NUMBER,
3738 inv_payee_party_id IN NUMBER,
3739 inv_supplier_site_id IN NUMBER)
3740 RETURN VARCHAR2
3741 IS
3742 l_rel_add_info VARCHAR2(255);
3743 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.get_rel_add_info';
3744
3745 CURSOR l_add_info_csr (
3746 l_party_id IN NUMBER,
3747 l_supplier_site_id IN NUMBER,
3748 l_remit_party_id IN NUMBER,
3749 l_remit_supplier_site_id IN NUMBER) IS
3750 SELECT irel.additional_information
3751 FROM iby_ext_payee_relationships irel
3752 WHERE irel.party_id = l_party_id
3753 AND irel.supplier_site_id = l_supplier_site_id
3754 AND irel.remit_party_id = l_remit_party_id
3755 AND irel.remit_supplier_site_id = l_remit_supplier_site_id
3756 AND irel.active = 'Y'
3757 AND to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') BETWEEN (to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00') AND (to_char(nvl(irel.to_date,sysdate), 'YYYY-MM-DD') || ' 23:59:59')
3758 ;
3759
3760 BEGIN
3761
3762 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3763 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3764 debug_level => G_LEVEL_PROCEDURE,
3765 module => l_Debug_Module);
3766 END IF;
3767
3768 OPEN l_add_info_csr (inv_payee_party_id, inv_supplier_site_id, payee_party_id, supplier_site_id);
3769 FETCH l_add_info_csr INTO l_rel_add_info;
3770 CLOSE l_add_info_csr;
3771
3772
3773 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3774 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3775 debug_level => G_LEVEL_PROCEDURE,
3776 module => l_Debug_Module);
3777 END IF;
3778
3779 RETURN l_rel_add_info;
3780
3781 END get_rel_add_info;
3782
3783
3784
3785
3786
3787 FUNCTION get_relship_id(
3788 payee_party_id IN NUMBER,
3789 supplier_site_id IN NUMBER,
3790 inv_payee_party_id IN NUMBER,
3791 inv_supplier_site_id IN NUMBER)
3792 RETURN NUMBER
3793 IS
3794 l_relship_id NUMBER := -1;
3795 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.get_relship_id';
3796
3797 CURSOR l_relshipid_csr (
3798 l_party_id IN NUMBER,
3799 l_supplier_site_id IN NUMBER,
3800 l_remit_party_id IN NUMBER,
3801 l_remit_supplier_site_id IN NUMBER) IS
3802 SELECT irel.relationship_id
3803 FROM iby_ext_payee_relationships irel
3804 WHERE irel.party_id = l_party_id
3805 AND irel.supplier_site_id = l_supplier_site_id
3806 AND irel.remit_party_id = l_remit_party_id
3807 AND irel.remit_supplier_site_id = l_remit_supplier_site_id
3808 AND irel.active = 'Y'
3809 AND to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') BETWEEN (to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00') AND (to_char(nvl(irel.to_date,sysdate), 'YYYY-MM-DD') || ' 23:59:59')
3810 ;
3811
3812 BEGIN
3813
3814 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3815 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3816 debug_level => G_LEVEL_PROCEDURE,
3817 module => l_Debug_Module);
3818 END IF;
3819
3820 OPEN l_relshipid_csr (inv_payee_party_id, inv_supplier_site_id, payee_party_id, supplier_site_id);
3821 FETCH l_relshipid_csr INTO l_relship_id;
3822 CLOSE l_relshipid_csr;
3823
3824
3825 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3826 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3827 debug_level => G_LEVEL_PROCEDURE,
3828 module => l_Debug_Module);
3829 END IF;
3830
3831 RETURN l_relship_id;
3832
3833 END get_relship_id;
3834 /* TPP - End */
3835
3836
3837 FUNCTION Get_PayeeBankAccount(p_payment_id IN NUMBER, p_external_bank_account_id IN NUMBER)
3838 RETURN XMLTYPE
3839 IS
3840 l_payee_ba XMLTYPE;
3841 l_pmt_func VARCHAR2(1);
3842 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayeeBankAccount';
3843
3844 CURSOR l_pmt_func_csr (p_payment_id IN NUMBER) IS
3845 SELECT nvl(employee_payment_flag, 'N')
3846 FROM iby_payments_all
3847 WHERE payment_id = p_payment_id;
3848
3849 CURSOR l_payee_ba_csr (p_external_bank_account_id IN NUMBER) IS
3850 SELECT ext_bank_account
3851 FROM iby_xml_fd_peba_1_0_v
3852 WHERE bank_account_id = p_external_bank_account_id;
3853
3854 CURSOR l_payee_bam_csr (p_external_bank_account_id IN NUMBER) IS
3855 SELECT ext_bank_account
3856 FROM iby_xml_fd_pebam_1_0_v
3857 WHERE bank_account_id = p_external_bank_account_id;
3858
3859 BEGIN
3860 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3861 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3862 debug_level => G_LEVEL_PROCEDURE,
3863 module => l_Debug_Module);
3864 END IF;
3865 -- Added this as a workaround for bug 5293384
3866 -- The new behavior is this procedure will always be called
3867 IF (p_external_bank_account_id IS NULL) THEN
3868 RETURN NULL;
3869 end if;
3870
3871 OPEN l_pmt_func_csr (p_payment_id);
3872 FETCH l_pmt_func_csr INTO l_pmt_func;
3873 CLOSE l_pmt_func_csr;
3874 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3875 iby_debug_pub.add(debug_msg => 'Before the condition: ' || ' l_pmt_func:'|| l_pmt_func,
3876 debug_level => G_LEVEL_PROCEDURE,
3877 module => l_Debug_Module);
3878 END IF;
3879 IF l_pmt_func = 'Y' AND G_May_Need_HR_Masking THEN
3880
3881 OPEN l_payee_bam_csr (p_external_bank_account_id);
3882 FETCH l_payee_bam_csr INTO l_payee_ba;
3883 CLOSE l_payee_bam_csr;
3884
3885 ELSE
3886
3887 OPEN l_payee_ba_csr (p_external_bank_account_id);
3888 FETCH l_payee_ba_csr INTO l_payee_ba;
3889 CLOSE l_payee_ba_csr;
3890
3891 END IF;
3892
3893 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3894 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3895 debug_level => G_LEVEL_PROCEDURE,
3896 module => l_Debug_Module);
3897 END IF;
3898
3899 RETURN l_payee_ba;
3900
3901 END Get_PayeeBankAccount;
3902
3903
3904 FUNCTION Get_PayeeBankAccount_Denorm(p_payment_id IN NUMBER, p_external_bank_account_id IN NUMBER)
3905 RETURN XMLTYPE
3906 IS
3907 l_payee_ba XMLTYPE;
3908 l_pmt_func VARCHAR2(1);
3909 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayeeBankAccount_Denorm 2 input param';
3910
3911 CURSOR l_pmt_func_csr (p_payment_id IN NUMBER) IS
3912 SELECT nvl(employee_payment_flag, 'N')
3913 FROM iby_payments_all
3914 WHERE payment_id = p_payment_id;
3915
3916 CURSOR l_payee_ba_csr (p_payment_id IN NUMBER) IS
3917 SELECT ext_bank_account
3918 FROM iby_xml_fd_peba_1_0_vd
3919 WHERE payment_id = p_payment_id;
3920
3921 CURSOR l_payee_bam_csr (p_payment_id IN NUMBER) IS
3922 SELECT ext_bank_account
3923 FROM iby_xml_fd_pebam_1_0_vd
3924 WHERE payment_id = p_payment_id;
3925
3926 BEGIN
3927
3928 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3929 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3930 debug_level => G_LEVEL_PROCEDURE,
3931 module => l_Debug_Module);
3932 END IF;
3933 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3934 iby_debug_pub.add(debug_msg => 'ENTER: ' || l_Debug_Module,
3935 debug_level => G_LEVEL_PROCEDURE,
3936 module => l_Debug_Module);
3937 END IF;
3938 -- Added this as a workaround for bug 5293384
3939 -- The new behavior is this procedure will always be called
3940 IF (p_external_bank_account_id IS NULL) THEN
3941 RETURN NULL;
3942 end if;
3943
3944 OPEN l_pmt_func_csr (p_payment_id);
3945 FETCH l_pmt_func_csr INTO l_pmt_func;
3946 CLOSE l_pmt_func_csr;
3947 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3948 iby_debug_pub.add(debug_msg => 'Before IF : -- l_pmt_func: ' || l_pmt_func ,
3949 debug_level => G_LEVEL_PROCEDURE,
3950 module => l_Debug_Module);
3951 END IF;
3952 IF l_pmt_func = 'Y' AND G_May_Need_HR_Masking THEN
3953
3954 OPEN l_payee_bam_csr (p_payment_id);
3955 FETCH l_payee_bam_csr INTO l_payee_ba;
3956 CLOSE l_payee_bam_csr;
3957
3958 ELSE
3959
3960 OPEN l_payee_ba_csr (p_payment_id);
3961 FETCH l_payee_ba_csr INTO l_payee_ba;
3962 CLOSE l_payee_ba_csr;
3963
3964 END IF;
3965
3966 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3967 iby_debug_pub.add(debug_msg => 'EXIT: ' || l_Debug_Module,
3968 debug_level => G_LEVEL_PROCEDURE,
3969 module => l_Debug_Module);
3970 END IF;
3971 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3972 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
3973 debug_level => G_LEVEL_PROCEDURE,
3974 module => l_Debug_Module);
3975 END IF;
3976 RETURN l_payee_ba;
3977
3978 END Get_PayeeBankAccount_Denorm;
3979
3980
3981 /*Overloaded Function .
3982 For perf issue : Citi. */
3983 FUNCTION Get_PayeeBankAccount_Denorm(p_payment_id IN NUMBER
3984 , p_external_bank_account_id IN NUMBER
3985 ,p_pmt_func IN VARCHAR2)
3986 RETURN XMLTYPE
3987 IS
3988 l_payee_ba XMLTYPE;
3989 l_pmt_func VARCHAR2(1);
3990 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayeeBankAccount_Denorm 3 input param';
3991
3992 CURSOR l_pmt_func_csr (p_payment_id IN NUMBER) IS
3993 SELECT nvl(employee_payment_flag, 'N')
3994 FROM iby_payments_all
3995 WHERE payment_id = p_payment_id;
3996
3997 CURSOR l_payee_ba_csr (p_payment_id IN NUMBER) IS
3998 SELECT ext_bank_account
3999 FROM iby_xml_fd_peba_1_0_vd
4000 WHERE payment_id = p_payment_id;
4001
4002 CURSOR l_payee_bam_csr (p_payment_id IN NUMBER) IS
4003 SELECT ext_bank_account
4004 FROM iby_xml_fd_pebam_1_0_vd
4005 WHERE payment_id = p_payment_id;
4006
4007 BEGIN
4008
4009 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4010 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
4011 debug_level => G_LEVEL_PROCEDURE,
4012 module => l_Debug_Module);
4013 END IF;
4014 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4015 iby_debug_pub.add(debug_msg => 'ENTER: ' || l_Debug_Module,
4016 debug_level => G_LEVEL_PROCEDURE,
4017 module => l_Debug_Module);
4018 END IF;
4019 -- Added this as a workaround for bug 5293384
4020 -- The new behavior is this procedure will always be called
4021 IF (p_external_bank_account_id IS NULL) THEN
4022 RETURN NULL;
4023 end if;
4024 /* For 9184059
4025 OPEN l_pmt_func_csr (p_payment_id);
4026 FETCH l_pmt_func_csr INTO l_pmt_func;
4027 CLOSE l_pmt_func_csr;
4028 */
4029 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4030 iby_debug_pub.add(debug_msg => 'Before IF : -- l_pmt_func: ' || l_pmt_func ,
4031 debug_level => G_LEVEL_PROCEDURE,
4032 module => l_Debug_Module);
4033 END IF;
4034 IF p_pmt_func = 'Y' AND G_May_Need_HR_Masking THEN
4035
4036 OPEN l_payee_bam_csr (p_payment_id);
4037 FETCH l_payee_bam_csr INTO l_payee_ba;
4038 CLOSE l_payee_bam_csr;
4039
4040 ELSE
4041
4042 OPEN l_payee_ba_csr (p_payment_id);
4043 FETCH l_payee_ba_csr INTO l_payee_ba;
4044 CLOSE l_payee_ba_csr;
4045
4046 END IF;
4047
4048 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4049 iby_debug_pub.add(debug_msg => 'EXIT: ' || l_Debug_Module,
4050 debug_level => G_LEVEL_PROCEDURE,
4051 module => l_Debug_Module);
4052 END IF;
4053 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4054 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
4055 debug_level => G_LEVEL_PROCEDURE,
4056 module => l_Debug_Module);
4057 END IF;
4058 RETURN l_payee_ba;
4059
4060 END Get_PayeeBankAccount_Denorm;
4061 /*End of Overloaded Function*/
4062
4063 FUNCTION Get_Doc_DocLineAgg(p_document_payable_id IN NUMBER)
4064 RETURN XMLTYPE
4065 IS
4066 l_docline_agg XMLTYPE;
4067 --l_conc_invalid_chars VARCHAR2(50);
4068 --l_conc_replacement_chars VARCHAR2(50);
4069 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Doc_DocLineAgg';
4070
4071 -- IBY_XML_FD_DOCLINE_1_0_V is obselete fz 8/30/2005
4072 -- Bug 6321384 Added nvl to check for NULL values
4073 CURSOR l_docline_csr (p_document_payable_id IN NUMBER,
4074 p_conc_invalid_chars IN VARCHAR2,
4075 p_conc_replacement_chars IN VARCHAR2) IS
4076 SELECT XMLAgg(
4077 XMLElement("DocumentPayableLine",
4078 XMLElement("LineNumber", ail.line_number),
4079 XMLElement("PONumber", ph.segment1),
4080 XMLElement("LineType",
4081 XMLElement("Code", ail.line_type_lookup_code),
4082 XMLElement("Meaning", null)),
4083 XMLElement("LineDescription", TRANSLATE(ail.description, p_conc_invalid_chars, p_conc_replacement_chars)),
4084 XMLElement("LineGrossAmount",
4085 XMLElement("Value", ail.amount),
4086 XMLElement("Currency", XMLElement("Code", ibydoc.document_currency_code))),
4087 XMLElement("UnitPrice", ail.unit_price),
4088 XMLElement("Quantity", ail.quantity_invoiced),
4089 XMLElement("UnitOfMeasure",
4090 XMLElement("Code", ail.unit_meas_lookup_code),
4091 XMLElement("Meaning", null)),
4092 XMLElement("Tax",
4093 XMLElement("TaxCode", ail.tax),
4094 XMLElement("TaxRate", ail.tax_rate)
4095 ),
4096 IBY_FD_EXTRACT_EXT_PUB.Get_Docline_Ext_Agg(ibydoc.document_payable_id, ail.line_number)
4097 )
4098 )
4099 FROM
4100 ap_invoice_lines_all ail,
4101 po_headers_all ph,
4102 iby_docs_payable_all ibydoc
4103 WHERE ibydoc.document_payable_id = p_document_payable_id
4104 AND ail.po_header_id = ph.po_header_id(+)
4105 AND nvl(ibydoc.calling_app_doc_unique_ref2,-99) = ail.invoice_id
4106 AND ibydoc.calling_app_id = 200;
4107
4108 BEGIN
4109 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4110 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
4111 debug_level => G_LEVEL_PROCEDURE,
4112 module => l_Debug_Module);
4113 END IF;
4114
4115 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4116 iby_debug_pub.add(debug_msg => 'ENTER: ' || l_Debug_Module,
4117 debug_level => G_LEVEL_PROCEDURE,
4118 module => l_Debug_Module);
4119 END IF;
4120 /* Preparing the concatinated strings of invalid characters
4121 and corresponding replacement characters. Bug 7292070 */
4122 /*
4123 FOR i in 1..32 LOOP
4124 l_conc_invalid_chars :=l_conc_invalid_chars||fnd_global.local_chr(i-1);
4125 l_conc_replacement_chars :=l_conc_replacement_chars||' ';
4126 END LOOP;
4127 */
4128 OPEN l_docline_csr (p_document_payable_id,l_conc_invalid_chars,l_conc_replacement_chars);
4129 FETCH l_docline_csr INTO l_docline_agg;
4130 CLOSE l_docline_csr;
4131
4132 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4133 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
4134 debug_level => G_LEVEL_PROCEDURE,
4135 module => l_Debug_Module);
4136 END IF;
4137
4138 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4139 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
4140 debug_level => G_LEVEL_PROCEDURE,
4141 module => l_Debug_Module);
4142 END IF;
4143 RETURN l_docline_agg;
4144 EXCEPTION
4145 WHEN OTHERS THEN
4146 iby_debug_pub.add(debug_msg => 'EXECPTION OCCURED IN : ' || l_Debug_Module || sqlerrm ,
4147 debug_level => G_LEVEL_PROCEDURE,
4148 module => l_Debug_Module);
4149 RAISE;
4150 END Get_Doc_DocLineAgg;
4151
4152
4153
4154 /* Start of overloaded function */
4155 FUNCTION Get_Doc_DocLineAgg(p_document_payable_id IN NUMBER,
4156 p_call_app_doc_unique_ref2 IN ap_invoices_all.invoice_id%TYPE,
4157 p_doc_currency_code IN iby_docs_payable_all.document_currency_code%TYPE,
4158 p_calling_app_id IN iby_docs_payable_all.calling_app_id%TYPE)
4159 RETURN XMLTYPE
4160 IS
4161 l_docline_agg XMLTYPE;
4162 --l_conc_invalid_chars VARCHAR2(50);
4163 --l_conc_replacement_chars VARCHAR2(50);
4164 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Doc_DocLineAgg';
4165
4166 CURSOR l_docline_csr(p_document_payable_id IN NUMBER,
4167 p_conc_invalid_chars IN VARCHAR2,
4168 p_conc_replacement_chars IN VARCHAR2,
4169 p_call_app_doc_unique_ref2 IN ap_invoices_all.invoice_id%TYPE,
4170 p_doc_currency_code IN iby_docs_payable_all.document_currency_code%TYPE,
4171 p_calling_app_id IN iby_docs_payable_all.calling_app_id%TYPE)
4172 IS
4173 SELECT XMLAgg(
4174 XMLElement("DocumentPayableLine",
4175 XMLElement("LineNumber", ail.line_number),
4176 XMLElement("PONumber", ph.segment1),
4177 XMLElement("LineType",
4178 XMLElement("Code", ail.line_type_lookup_code),
4179 XMLElement("Meaning", null)),
4180 XMLElement("LineDescription", TRANSLATE(ail.description, p_conc_invalid_chars, p_conc_replacement_chars)),
4181 XMLElement("LineGrossAmount",
4182 XMLElement("Value", ail.amount),
4183 XMLElement("Currency", XMLElement("Code", p_doc_currency_code))),
4184 XMLElement("UnitPrice", ail.unit_price),
4185 XMLElement("Quantity", ail.quantity_invoiced),
4186 XMLElement("UnitOfMeasure",
4187 XMLElement("Code", ail.unit_meas_lookup_code),
4188 XMLElement("Meaning", null)),
4189 XMLElement("Tax",
4190 XMLElement("TaxCode", ail.tax),
4191 XMLElement("TaxRate", ail.tax_rate)
4192 ),
4193 IBY_FD_EXTRACT_EXT_PUB.Get_Docline_Ext_Agg(p_document_payable_id, ail.line_number)
4194 )
4195 )
4196 FROM
4197 ap_invoice_lines_all ail,
4198 po_headers_all ph
4199 WHERE ail.po_header_id = ph.po_header_id(+)
4200 AND nvl(p_call_app_doc_unique_ref2,-99) = ail.invoice_id
4201 AND p_calling_app_id = 200;
4202
4203 BEGIN
4204 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4205 iby_debug_pub.add(debug_msg => 'Enter:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
4206 debug_level => G_LEVEL_PROCEDURE,
4207 module => l_Debug_Module);
4208 END IF;
4209
4210 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4211 iby_debug_pub.add(debug_msg => 'ENTER: ' || l_Debug_Module,
4212 debug_level => G_LEVEL_PROCEDURE,
4213 module => l_Debug_Module);
4214 END IF;
4215
4216 OPEN l_docline_csr (p_document_payable_id
4217 ,l_conc_invalid_chars
4218 ,l_conc_replacement_chars
4219 ,p_call_app_doc_unique_ref2
4220 ,p_doc_currency_code
4221 ,p_calling_app_id);
4222 FETCH l_docline_csr INTO l_docline_agg;
4223 CLOSE l_docline_csr;
4224
4225 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4226 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
4227 debug_level => G_LEVEL_PROCEDURE,
4228 module => l_Debug_Module);
4229 END IF;
4230
4231 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4232 iby_debug_pub.add(debug_msg => 'Exit:TIMESTAMP:: ' || l_Debug_Module||':: '||systimestamp,
4233 debug_level => G_LEVEL_PROCEDURE,
4234 module => l_Debug_Module);
4235 END IF;
4236 RETURN l_docline_agg;
4237
4238
4239 EXCEPTION
4240 WHEN OTHERS THEN
4241 iby_debug_pub.add(debug_msg => 'EXECPTION OCCURED IN : ' || l_Debug_Module || sqlerrm ,
4242 debug_level => G_LEVEL_PROCEDURE,
4243 module => l_Debug_Module);
4244 RAISE;
4245 END Get_Doc_DocLineAgg;
4246 /*End of Overloaded function */
4247
4248
4249
4250
4251
4252 FUNCTION Get_SRA_Attribute(p_payment_id IN NUMBER, p_attribute_type IN NUMBER)
4253 RETURN VARCHAR2
4254 IS
4255 l_sra_delivery_method VARCHAR2(30);
4256 l_override_payee_flag VARCHAR2(1);
4257 l_sra_req_flag VARCHAR2(1);
4258 l_pp_sra_delivery_method VARCHAR2(30);
4259 l_ps_lang VARCHAR2(4);
4260 l_ps_territory VARCHAR2(60);
4261
4262 l_pmt_ref NUMBER;
4263 l_chk_num NUMBER;
4264 l_email_sub VARCHAR2(2000);
4265
4266 CURSOR l_sra_setup_csr (p_payment_id IN NUMBER) IS
4267 SELECT sra_setup.sra_override_payee_flag, sra_setup.remit_advice_delivery_method
4268 FROM iby_payments_all pmt, iby_pay_instructions_all ins,
4269 iby_payment_profiles pp, iby_remit_advice_setup sra_setup
4270 WHERE pmt.payment_id = p_payment_id
4271 AND pmt.payment_instruction_id = ins.payment_instruction_id
4272 AND pp.payment_profile_id = ins.payment_profile_id
4273 AND pp.system_profile_code = sra_setup.system_profile_code;
4274
4275 CURSOR l_sra_req_flag_csr (p_payment_id IN NUMBER) IS
4276 SELECT separate_remit_advice_req_flag
4277 FROM iby_payments_all
4278 WHERE payment_id = p_payment_id;
4279
4280 --Changing for bug 9686312.
4281 --Adding fnd_languages table to fetch language country in
4282 --ISO standard.
4283 CURSOR l_lang_territory_csr (p_payment_id IN NUMBER) IS
4284 SELECT fl.language_code , loc.country
4285 FROM hz_party_sites ps, hz_locations loc, iby_payments_all pmt
4286 , fnd_languages fl
4287 WHERE payment_id = p_payment_id
4288 AND fl.language_code = loc.language
4289 AND pmt.party_site_id = ps.party_site_id(+)
4290 AND loc.location_id = ps.location_id;
4291 -- End of bug 9686312.
4292
4293 CURSOR l_payment_details_csr (p_payment_id IN NUMBER) IS
4294 SELECT pmt.payment_reference_number, pmt.paper_document_number
4295 FROM iby_payments_all pmt
4296 WHERE pmt.payment_id = p_payment_id;
4297
4298 BEGIN
4299
4300 IF p_attribute_type = G_SRA_DELIVERY_METHOD_ATTR THEN
4301
4302 OPEN l_sra_setup_csr (p_payment_id);
4303 FETCH l_sra_setup_csr INTO l_override_payee_flag, l_pp_sra_delivery_method;
4304 CLOSE l_sra_setup_csr;
4305
4306 IF l_override_payee_flag = 'Y' THEN
4307 l_sra_delivery_method := l_pp_sra_delivery_method;
4308
4309 ELSE
4310 l_sra_delivery_method := Get_Payee_Default_Attribute(p_payment_id, p_attribute_type);
4311
4312 IF l_sra_delivery_method is null THEN
4313 l_sra_delivery_method := l_pp_sra_delivery_method;
4314 END IF;
4315 END IF;
4316
4317 return l_sra_delivery_method;
4318
4319 ELSIF p_attribute_type = G_SRA_REQ_FLAG_ATTR THEN
4320
4321 OPEN l_sra_req_flag_csr (p_payment_id);
4322 FETCH l_sra_req_flag_csr INTO l_sra_req_flag;
4323 CLOSE l_sra_req_flag_csr;
4324
4325 return l_sra_req_flag;
4326
4327 ELSIF p_attribute_type = G_SRA_PS_LANG_ATTR OR
4328 p_attribute_type = G_SRA_PS_TERRITORY_ATTR THEN
4329
4330 OPEN l_lang_territory_csr (p_payment_id);
4331 FETCH l_lang_territory_csr INTO l_ps_lang, l_ps_territory;
4332 CLOSE l_lang_territory_csr;
4333
4334 IF p_attribute_type = G_SRA_PS_LANG_ATTR THEN
4335 return l_ps_lang;
4336 ELSE
4337 return l_ps_territory;
4338 END IF;
4339
4340
4341 ELSIF p_attribute_type = G_SRA_EMAIL_SUBJECT_ATTR THEN
4342
4343 OPEN l_payment_details_csr (p_payment_id);
4344 FETCH l_payment_details_csr INTO l_pmt_ref, l_chk_num;
4345 CLOSE l_payment_details_csr;
4346
4347 IF ( l_chk_num > 0 ) THEN
4348 fnd_message.set_name('IBY', 'IBY_FD_SRA_EMAIL_SUBJ_2');
4349 fnd_message.set_Token('PAPER_DOC_NUM',l_chk_num);
4350 l_email_sub := FND_MESSAGE.GET;
4351 ELSE
4352 fnd_message.set_name('IBY', 'IBY_FD_SRA_EMAIL_SUBJ');
4353 fnd_message.set_Token('PMT_REF_NUM',l_pmt_ref);
4354 l_email_sub := FND_MESSAGE.GET;
4355 END IF;
4356
4357 RETURN l_email_sub;
4358
4359 ELSE
4360 return Get_Payee_Default_Attribute(p_payment_id, p_attribute_type);
4361 END IF;
4362
4363 END Get_SRA_Attribute;
4364
4365
4366 FUNCTION Get_Payee_Default_Attribute(p_payment_id IN NUMBER, p_attribute_type IN NUMBER)
4367 RETURN VARCHAR2
4368 IS
4369 l_attribute_val VARCHAR2(1000);
4370
4371 CURSOR l_payee_defaulting_cur (p_payment_id NUMBER) IS
4372 SELECT payee.remit_advice_delivery_method,
4373 payee.remit_advice_email,
4374 payee.remit_advice_fax
4375 FROM iby_external_payees_all payee,
4376 iby_payments_all pmt
4377 WHERE payee.payee_party_id = pmt.payee_party_id
4378 AND payee.payment_function = pmt.payment_function
4379 AND (payee.org_id is NULL OR (payee.org_id = pmt.org_id AND payee.org_type = pmt.org_type))
4380 AND (payee.party_site_id is NULL OR payee.party_site_id = pmt.party_site_id)
4381 AND (payee.supplier_site_id is NULL OR payee.supplier_site_id = pmt.supplier_site_id)
4382 AND pmt.payment_id = p_payment_id
4383 ORDER BY payee.supplier_site_id, payee.party_site_id, payee.org_id;
4384
4385 BEGIN
4386
4387 FOR l_default_rec in l_payee_defaulting_cur(p_payment_id) LOOP
4388 IF (l_attribute_val is NULL) THEN
4389 IF p_attribute_type = G_SRA_DELIVERY_METHOD_ATTR THEN
4390 l_attribute_val := l_default_rec.remit_advice_delivery_method;
4391 ELSIF p_attribute_type = G_SRA_EMAIL_ATTR THEN
4392 l_attribute_val := l_default_rec.remit_advice_email;
4393 ELSIF p_attribute_type = G_SRA_FAX_ATTR THEN
4394 l_attribute_val := l_default_rec.remit_advice_fax;
4395 END IF;
4396 END IF;
4397 END LOOP;
4398
4399 return l_attribute_val;
4400 END Get_Payee_Default_Attribute;
4401
4402
4403 -- following are four wrappers for the corresponding
4404 -- federal APIs. The wrappers will swallow any exceptions
4405 -- from calling the federal code.
4406 -- comment out actual calls to federal code
4407 -- as the FV package are not yet available
4408 -- - in F12MSS2/F12DBS2 8/30/2005
4409 FUNCTION get_FEIN(payment_instruction_id IN NUMBER)
4410 RETURN VARCHAR2
4411 IS
4412 BEGIN
4413 return FV_FEDERAL_PAYMENT_FIELDS_PKG.get_FEIN(payment_instruction_id);
4414 EXCEPTION
4415 -- swallow exceptions
4416 WHEN OTHERS THEN
4417 RETURN NULL;
4418 END get_FEIN;
4419
4420 FUNCTION get_Abbreviated_Agency_Code(payment_instruction_id IN NUMBER)
4421 RETURN VARCHAR2
4422 IS
4423 BEGIN
4424 return FV_FEDERAL_PAYMENT_FIELDS_PKG.get_Abbreviated_Agency_Code(payment_instruction_id);
4425 EXCEPTION
4426 -- swallow exceptions
4427 WHEN OTHERS THEN
4428 RETURN NULL;
4429 END get_Abbreviated_Agency_Code;
4430
4431 FUNCTION get_Allotment_Code(payment_id IN NUMBER)
4432 RETURN VARCHAR2
4433 IS
4434 BEGIN
4435 return FV_FEDERAL_PAYMENT_FIELDS_PKG.get_Allotment_Code(payment_id);
4436 EXCEPTION
4437 -- swallow exceptions
4438 WHEN OTHERS THEN
4439 RETURN NULL;
4440 END get_Allotment_Code;
4441
4442 FUNCTION TOP_Offset_Eligibility_Flag(payment_id IN NUMBER)
4443 RETURN VARCHAR2
4444 IS
4445 BEGIN
4446 return FV_FEDERAL_PAYMENT_FIELDS_PKG.TOP_Offset_Eligibility_Flag(payment_id);
4447 EXCEPTION
4448 -- swallow exceptions
4449 WHEN OTHERS THEN
4450 RETURN NULL;
4451 END TOP_Offset_Eligibility_Flag;
4452
4453 FUNCTION get_SPS_PMT_TS(payment_id IN NUMBER)
4454 RETURN VARCHAR2
4455 IS
4456 l_treasury_symbol VARCHAR2(35);
4457
4458 CURSOR l_ts_cur (p_payment_id NUMBER) IS
4459 SELECT fv.treasury_symbol
4460 FROM FV_TP_TS_AMT_DATA fv,
4461 iby_payments_all pmt
4462 WHERE pmt.payment_instruction_id = fv.payment_instruction_id
4463 AND pmt.payment_id = p_payment_id
4464 AND ROWNUM = 1;
4465
4466 BEGIN
4467
4468 OPEN l_ts_cur (payment_id);
4469 FETCH l_ts_cur INTO l_treasury_symbol;
4470 CLOSE l_ts_cur;
4471
4472 return l_treasury_symbol;
4473 EXCEPTION
4474 -- swallow exceptions
4475 WHEN OTHERS THEN
4476 RETURN NULL;
4477 END get_SPS_PMT_TS;
4478
4479
4480 FUNCTION Get_Bordero_Bank_Ref(p_doc_payable_id IN NUMBER)
4481 RETURN VARCHAR2
4482 IS
4483 l_return_status VARCHAR2(1);
4484 l_bordero_bank_ref VARCHAR2(30);
4485 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Bordero_Bank_Ref';
4486 l_doc_idx NUMBER;
4487
4488 BEGIN
4489
4490 --IF G_IS_BRAZIL IS NULL THEN
4491 l_doc_idx := p_doc_payable_id;
4492
4493 /* perf bug 6763515 */
4494 IF l_doc_idx is NOT NULL THEN
4495 IF (NOT(g_docs_pay_attribs_tbl.EXISTS(l_doc_idx))) THEN
4496 g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL := JL_BR_AP_PAY_SCHED_GDF_PKG.Check_Brazil(
4497 P_Doc_Payable_ID => p_doc_payable_id,
4498 P_RETURN_STATUS => l_return_status);
4499 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4500 iby_debug_pub.add(debug_msg => 'Called JL_BR_AP_PAY_SCHED_GDF_PKG.Check_Brazil(). G_IS_BRAZIL: ' || g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL,
4501 debug_level => G_LEVEL_STATEMENT,
4502 module => l_Debug_Module);
4503 END IF;
4504 END IF;
4505 G_IS_BRAZIL :=g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL;
4506 END IF;
4507
4508
4509 --END IF;
4510
4511 IF G_IS_BRAZIL = 1 THEN
4512 l_bordero_bank_ref := JL_BR_AP_PAY_SCHED_GDF_PKG.Get_Bordero_Bank_Ref(
4513 P_Doc_Payable_ID => p_doc_payable_id,
4514 P_RETURN_STATUS => l_return_status);
4515
4516 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4517 iby_debug_pub.add(debug_msg => 'Called JL_BR_AP_PAY_SCHED_GDF_PKG.Get_Bordero_Bank_Ref(). l_bordero_bank_ref: ' || l_bordero_bank_ref,
4518 debug_level => G_LEVEL_STATEMENT,
4519 module => l_Debug_Module);
4520 END IF;
4521 END IF;
4522
4523 return l_bordero_bank_ref;
4524 EXCEPTION
4525 -- swallow exceptions
4526 WHEN OTHERS THEN
4527 RETURN NULL;
4528 END Get_Bordero_Bank_Ref;
4529
4530
4531 FUNCTION Get_Bordero_Int_Amt(p_doc_payable_id IN NUMBER)
4532 RETURN Number
4533 IS
4534 l_return_status VARCHAR2(1);
4535 l_process_type VARCHAR2(30);
4536 l_bordero_int_amt NUMBER;
4537 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Bordero_Int_Amt';
4538 l_doc_idx NUMBER;
4539
4540 BEGIN
4541
4542 --IF G_IS_BRAZIL IS NULL THEN
4543 l_doc_idx := p_doc_payable_id;
4544
4545 /* perf bug 6763515 */
4546 IF l_doc_idx is NOT NULL THEN
4547 IF (NOT(g_docs_pay_attribs_tbl.EXISTS(l_doc_idx))) THEN
4548 g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL := JL_BR_AP_PAY_SCHED_GDF_PKG.Check_Brazil(
4549 P_Doc_Payable_ID => p_doc_payable_id,
4550 P_RETURN_STATUS => l_return_status);
4551
4552 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4553 iby_debug_pub.add(debug_msg => 'Called JL_BR_AP_PAY_SCHED_GDF_PKG.Check_Brazil(). G_IS_BRAZIL: ' || g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL,
4554 debug_level => G_LEVEL_STATEMENT,
4555 module => l_Debug_Module);
4556 END IF;
4557 END IF;
4558 G_IS_BRAZIL :=g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL;
4559 END IF;
4560
4561
4562 --END IF;
4563
4564 IF G_IS_BRAZIL = 1 THEN
4565 l_bordero_int_amt := JL_BR_AP_PAY_SCHED_GDF_PKG.Get_Bordero_Int_Amt(
4566 P_Doc_Payable_ID => p_doc_payable_id,
4567 P_Process_Type => l_process_type,
4568 P_RETURN_STATUS => l_return_status);
4569 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4570 iby_debug_pub.add(debug_msg => 'Called JL_BR_AP_PAY_SCHED_GDF_PKG.Get_Bordero_Int_Amt(). l_bordero_int_amt: ' || l_bordero_int_amt,
4571 debug_level => G_LEVEL_STATEMENT,
4572 module => l_Debug_Module);
4573 END IF;
4574 END IF;
4575
4576 return l_bordero_int_amt;
4577 EXCEPTION
4578 -- swallow exceptions
4579 WHEN OTHERS THEN
4580 RETURN NULL;
4581 END Get_Bordero_Int_Amt;
4582
4583
4584
4585 FUNCTION Get_Bordero_Abatement(p_doc_payable_id IN NUMBER)
4586 RETURN Number
4587 IS
4588 l_return_status VARCHAR2(1);
4589 l_process_type VARCHAR2(30);
4590 l_bordero_abt_amt NUMBER;
4591 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Bordero_Abatement';
4592 l_doc_idx NUMBER;
4593
4594 BEGIN
4595
4596 --IF G_IS_BRAZIL IS NULL THEN
4597 l_doc_idx := p_doc_payable_id;
4598
4599 /* perf bug 6763515 */
4600 IF l_doc_idx is NOT NULL THEN
4601 IF (NOT(g_docs_pay_attribs_tbl.EXISTS(l_doc_idx))) THEN
4602 g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL := JL_BR_AP_PAY_SCHED_GDF_PKG.Check_Brazil(
4603 P_Doc_Payable_ID => p_doc_payable_id,
4604 P_RETURN_STATUS => l_return_status);
4605
4606 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4607 iby_debug_pub.add(debug_msg => 'Called JL_BR_AP_PAY_SCHED_GDF_PKG.Check_Brazil(). G_IS_BRAZIL: ' || g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL,
4608 debug_level => G_LEVEL_STATEMENT,
4609 module => l_Debug_Module);
4610 END IF;
4611
4612 END IF;
4613 G_IS_BRAZIL :=g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL;
4614 END IF;
4615
4616
4617 --END IF;
4618
4619 IF G_IS_BRAZIL = 1 THEN
4620 l_bordero_abt_amt := JL_BR_AP_PAY_SCHED_GDF_PKG.Get_Bordero_Abatement(
4621 P_Doc_Payable_ID => p_doc_payable_id,
4622 P_Process_Type => l_process_type,
4623 P_RETURN_STATUS => l_return_status);
4624
4625 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4626 iby_debug_pub.add(debug_msg => 'Called JL_BR_AP_PAY_SCHED_GDF_PKG.Get_Bordero_Abatement(). l_bordero_abt_amt: ' || l_bordero_abt_amt,
4627 debug_level => G_LEVEL_STATEMENT,
4628 module => l_Debug_Module);
4629 END IF;
4630 END IF;
4631
4632
4633 return l_bordero_abt_amt;
4634 EXCEPTION
4635 -- swallow exceptions
4636 WHEN OTHERS THEN
4637 RETURN NULL;
4638 END Get_Bordero_Abatement;
4639
4640
4641 FUNCTION Get_Payment_Amount_Text(payment_id IN NUMBER)
4642 RETURN VARCHAR2
4643 IS
4644 l_amount NUMBER;
4645 l_currency_code VARCHAR2(10);
4646
4647 CURSOR l_amount_cur (p_payment_id NUMBER) IS
4648 SELECT pmt.payment_amount,
4649 pmt.payment_currency_code
4650 FROM iby_payments_all pmt
4651 WHERE pmt.payment_id = p_payment_id;
4652
4653 BEGIN
4654
4655 OPEN l_amount_cur (payment_id);
4656 FETCH l_amount_cur INTO l_amount, l_currency_code;
4657 CLOSE l_amount_cur;
4658
4659 RETURN IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(l_amount, l_currency_code);
4660
4661 EXCEPTION
4662 -- swallow exceptions
4663 WHEN OTHERS THEN
4664 RETURN NULL;
4665 END Get_Payment_Amount_Text;
4666
4667 FUNCTION Get_Payment_Amount_Withheld(payment_id IN NUMBER)
4668 RETURN NUMBER
4669 IS
4670 l_amount_withheld NUMBER;
4671 CURSOR l_pmt_amount_withheld_csr (p_payment_id IN NUMBER) IS
4672 SELECT sum(amount_withheld)
4673 FROM iby_docs_payable_all
4674 WHERE payment_id = p_payment_id;
4675 BEGIN
4676
4677 OPEN l_pmt_amount_withheld_csr (payment_id);
4678 FETCH l_pmt_amount_withheld_csr INTO l_amount_withheld;
4679 CLOSE l_pmt_amount_withheld_csr;
4680
4681 RETURN l_amount_withheld;
4682 EXCEPTION
4683 -- swallow exceptions
4684 WHEN OTHERS THEN
4685 RETURN NULL;
4686 END Get_Payment_Amount_Withheld;
4687
4688
4689
4690 -- Payment process request extract functions
4691 FUNCTION Get_Ppr_PmtAgg(p_payment_service_request_id IN NUMBER)
4692 RETURN XMLTYPE
4693 IS
4694 l_ppr_pmt_agg XMLTYPE;
4695
4696 CURSOR l_ppr_pmt_csr (p_payment_service_request_id IN NUMBER) IS
4697 SELECT XMLAgg(payment)
4698 FROM iby_xml_fd_pmt_1_0_v
4699 WHERE payment_service_request_id = p_payment_service_request_id;
4700
4701 BEGIN
4702
4703 OPEN l_ppr_pmt_csr (p_payment_service_request_id);
4704 FETCH l_ppr_pmt_csr INTO l_ppr_pmt_agg;
4705 CLOSE l_ppr_pmt_csr;
4706
4707 RETURN l_ppr_pmt_agg;
4708
4709 END Get_Ppr_PmtAgg;
4710
4711 FUNCTION Get_Ppr_PmtCount(p_payment_service_request_id IN NUMBER)
4712 RETURN NUMBER
4713 IS
4714 l_ppr_pmt_count NUMBER;
4715
4716 CURSOR l_ppr_pmt_count_csr (p_payment_service_request_id IN NUMBER) IS
4717 SELECT count(payment_id)
4718 FROM iby_xml_fd_pmt_1_0_v
4719 WHERE payment_service_request_id = p_payment_service_request_id;
4720
4721 BEGIN
4722
4723 OPEN l_ppr_pmt_count_csr (p_payment_service_request_id);
4724 FETCH l_ppr_pmt_count_csr INTO l_ppr_pmt_count;
4725 CLOSE l_ppr_pmt_count_csr;
4726
4727 RETURN l_ppr_pmt_count;
4728
4729 END Get_Ppr_PmtCount;
4730
4731
4732 FUNCTION Get_Ppr_PreBuildDocAgg(p_payment_service_request_id IN NUMBER)
4733 RETURN XMLTYPE
4734 IS
4735 l_docpayable_agg XMLTYPE;
4736
4737 CURSOR l_baddoc_csr (p_payment_service_request_id IN NUMBER) IS
4738 SELECT XMLAgg(doc_payable)
4739 FROM iby_xml_fd_doc_1_0_v
4740 WHERE payment_service_request_id = p_payment_service_request_id
4741 AND payment_id is null;
4742 -- AND document_status in ('REJECTED', 'FAILED_VALIDATION');
4743
4744 BEGIN
4745
4746 OPEN l_baddoc_csr (p_payment_service_request_id);
4747 FETCH l_baddoc_csr INTO l_docpayable_agg;
4748 CLOSE l_baddoc_csr;
4749
4750 RETURN l_docpayable_agg;
4751
4752 END Get_Ppr_PreBuildDocAgg;
4753
4754 FUNCTION Get_Ppr_PreBuildDocCount(p_payment_service_request_id IN NUMBER)
4755 RETURN NUMBER
4756 IS
4757 l_docpayable_count NUMBER;
4758
4759 CURSOR l_baddoc_count_csr (p_payment_service_request_id IN NUMBER) IS
4760 SELECT count(document_payable_id)
4761 FROM iby_xml_fd_doc_1_0_v
4762 WHERE payment_service_request_id = p_payment_service_request_id
4763 AND payment_id is null;
4764 -- AND document_status in ('REJECTED', 'FAILED_VALIDATION');
4765
4766 BEGIN
4767
4768 OPEN l_baddoc_count_csr (p_payment_service_request_id);
4769 FETCH l_baddoc_count_csr INTO l_docpayable_count;
4770 CLOSE l_baddoc_count_csr;
4771
4772 RETURN l_docpayable_count;
4773
4774 END Get_Ppr_PreBuildDocCount;
4775
4776
4777 FUNCTION Get_Pmt_PmtErrAgg(p_payment_id IN NUMBER)
4778 RETURN XMLTYPE
4779 IS
4780 l_pmterr_agg XMLTYPE;
4781 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Pmt_PmtErrAgg';
4782 CURSOR l_pmterr_csr (p_payment_id IN NUMBER) IS
4783 SELECT XMLAgg(payment_error)
4784 FROM iby_xml_fd_pmt_err_1_0_v
4785 WHERE payment_id = p_payment_id;
4786
4787 BEGIN
4788 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4789 iby_debug_pub.add(debug_msg => 'Get_Pmt_PmtErrAgg Enter: ',
4790 debug_level => G_LEVEL_STATEMENT,
4791 module => l_Debug_Module);
4792 END IF;
4793 OPEN l_pmterr_csr (p_payment_id);
4794 FETCH l_pmterr_csr INTO l_pmterr_agg;
4795 CLOSE l_pmterr_csr;
4796 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4797 iby_debug_pub.add(debug_msg => 'Get_Pmt_PmtErrAgg Exit: ',
4798 debug_level => G_LEVEL_STATEMENT,
4799 module => l_Debug_Module);
4800 END IF;
4801 RETURN l_pmterr_agg;
4802
4803 END Get_Pmt_PmtErrAgg;
4804
4805
4806 FUNCTION Get_Doc_DocErrAgg(p_document_payable_id IN NUMBER)
4807 RETURN XMLTYPE
4808 IS
4809 l_docerr_agg XMLTYPE;
4810
4811 CURSOR l_docerr_csr (p_document_payable_id IN NUMBER) IS
4812 SELECT XMLAgg(doc_payable_error)
4813 FROM iby_xml_fd_doc_err_1_0_v
4814 WHERE document_payable_id = p_document_payable_id;
4815
4816 BEGIN
4817
4818 OPEN l_docerr_csr (p_document_payable_id);
4819 FETCH l_docerr_csr INTO l_docerr_agg;
4820 CLOSE l_docerr_csr;
4821
4822 RETURN l_docerr_agg;
4823
4824 END Get_Doc_DocErrAgg;
4825
4826
4827 PROCEDURE Update_Pmt_SRA_Attr_Prt
4828 (
4829 p_payment_instruction_id IN NUMBER
4830 )
4831 IS
4832 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Update_Pmt_SRA_Attr_Prt';
4833
4834 CURSOR l_pmt_csr (p_payment_instruction_id IN NUMBER) IS
4835 SELECT payment_id, payment_reference_number
4836 FROM iby_payments_all
4837 WHERE payment_instruction_id = p_payment_instruction_id
4838 -- note: this where clause should be kept
4839 -- in-sync with the where clause of l_payerinstr_sra_prt_csr
4840 -- in Get_Ins_PayerInstrAgg()
4841 AND Get_SRA_Attribute(payment_id, G_SRA_REQ_FLAG_ATTR) = 'Y'
4842 AND Get_SRA_Attribute(payment_id, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
4843 AND payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
4844 'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED');
4845
4846 BEGIN
4847
4848 FOR l_payment IN l_pmt_csr(p_payment_instruction_id) LOOP
4849
4850 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4851 iby_debug_pub.add(debug_msg => 'updating the SRA delivery method for payment ref number ' ||
4852 l_payment.payment_reference_number || ' to be PRINTED',
4853 debug_level => G_LEVEL_STATEMENT,
4854 module => l_Debug_Module);
4855 END IF;
4856
4857 UPDATE
4858 iby_payments_all
4859 SET
4860 remit_advice_delivery_method = 'PRINTED',
4861 remit_advice_email = null,
4862 remit_advice_fax = null,
4863 object_version_number = object_version_number + 1,
4864 last_updated_by = fnd_global.user_id,
4865 last_update_date = SYSDATE,
4866 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
4867 WHERE
4868 payment_id = l_payment.payment_id;
4869
4870 END LOOP;
4871
4872
4873 END Update_Pmt_SRA_Attr_Prt;
4874
4875
4876 PROCEDURE Update_Pmt_SRA_Attr_Ele
4877 (
4878 p_payment_id IN NUMBER,
4879 p_delivery_method IN VARCHAR2,
4880 p_recipient_email IN VARCHAR2,
4881 p_recipient_fax IN VARCHAR2
4882 )
4883 IS
4884 BEGIN
4885
4886 IF p_delivery_method = 'EMAIL' THEN
4887 UPDATE
4888 iby_payments_all
4889 SET
4890 remit_advice_delivery_method = p_delivery_method,
4891 remit_advice_email = p_recipient_email,
4892 remit_advice_fax = null,
4893 object_version_number = object_version_number + 1,
4894 last_updated_by = fnd_global.user_id,
4895 last_update_date = SYSDATE,
4896 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
4897 WHERE payment_id = p_payment_id;
4898 ELSIF p_delivery_method = 'FAX' THEN
4899 UPDATE
4900 iby_payments_all
4901 SET
4902 remit_advice_delivery_method = p_delivery_method,
4903 remit_advice_email = null,
4904 remit_advice_fax = p_recipient_fax,
4905 object_version_number = object_version_number + 1,
4906 last_updated_by = fnd_global.user_id,
4907 last_update_date = SYSDATE,
4908 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
4909 WHERE payment_id = p_payment_id;
4910 END IF;
4911 END Update_Pmt_SRA_Attr_Ele;
4912
4913
4914 PROCEDURE initialize
4915 IS
4916 BEGIN
4917 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4918 iby_debug_pub.add(debug_msg => 'ENTER',
4919 debug_level => G_LEVEL_STATEMENT,
4920 module => G_Debug_Module || '.initialize');
4921 END IF;
4922 g_docs_pay_attribs_tbl.DELETE;
4923 g_hr_addr_tbl.DELETE;
4924 g_hz_addr_tbl.DELETE;
4925 g_payer_contact_tbl.DELETE;
4926 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4927 iby_debug_pub.add(debug_msg => 'EXIT',
4928 debug_level => G_LEVEL_STATEMENT,
4929 module => G_Debug_Module || '.initialize');
4930 END IF;
4931
4932 END initialize;
4933
4934 FUNCTION Get_Hz_Address(p_location_id IN NUMBER)
4935 RETURN XMLTYPE
4936 IS
4937 l_hz_addr XMLTYPE;
4938
4939 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
4940 Removing the storing of XMLTYPE in cache.
4941 */
4942
4943 l_location_id NUMBER(15);
4944 l_address1 VARCHAR2(240);
4945 l_address2 VARCHAR2(240);
4946 l_address3 VARCHAR2(240);
4947 l_address4 VARCHAR2(240);
4948 l_city VARCHAR2(60);
4949 l_county VARCHAR2(60);
4950 l_state VARCHAR2(60);
4951 l_province VARCHAR2(60);
4952 l_country VARCHAR2(60);
4953 l_iso_territory_code VARCHAR2(60);
4954 l_territory_short_name VARCHAR2(200);
4955 l_postal_code VARCHAR2(60);
4956 l_concat_addr VARCHAR2(4000);
4957 l_mail_addr VARCHAR2(4000);
4958
4959
4960 CURSOR l_hz_addr_csr (p_location_id IN NUMBER) IS
4961 SELECT hz_loc.location_id , hz_loc.address1 , hz_loc.address2,
4962 hz_loc.address3 , hz_loc.address4 , hz_loc.city, hz_loc.county, hz_loc.state,
4963 hz_loc.province, hz_loc.country, te.iso_territory_code, te.territory_short_name, hz_loc.postal_code,
4964 IBY_FD_EXTRACT_GEN_PVT.format_hz_address(hz_loc.location_id),
4965 IBY_FD_EXTRACT_GEN_PVT.format_hz_address(hz_loc.location_id, 'POSTAL_ADDR')
4966 FROM hz_locations hz_loc,
4967 fnd_territories_vl te
4968 WHERE hz_loc.country = te.territory_code
4969 AND location_id = p_location_id;
4970 /*
4971 SELECT address
4972 FROM IBY_XML_HZ_ADDR_1_0_V
4973 WHERE location_id = p_location_id; */
4974
4975
4976 BEGIN
4977
4978 Get_Hz_Address_C := Get_Hz_Address_C + 1;
4979 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4980 iby_debug_pub.add(debug_msg => 'Get_Hz_Address() entered. count: ' || Get_Hz_Address_C,
4981 debug_level => G_LEVEL_STATEMENT,
4982 module => G_Debug_Module || '.Get_Hz_Address');
4983 iby_debug_pub.add(debug_msg => 'p_location_id: ' || p_location_id,
4984 debug_level => G_LEVEL_STATEMENT,
4985 module => G_Debug_Module || '.Get_Hz_Address');
4986 END IF;
4987 IF p_location_id IS NULL THEN
4988 RETURN NULL;
4989 END IF;
4990 /*
4991 IF (NOT(g_hz_addr_tbl.EXISTS(p_location_id))) THEN
4992 OPEN l_hz_addr_csr (p_location_id);
4993 FETCH l_hz_addr_csr INTO g_hz_addr_tbl(p_location_id).hz_address;
4994 CLOSE l_hz_addr_csr;
4995 END IF;
4996 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4997 iby_debug_pub.add(debug_msg => 'EXIT',
4998 debug_level => G_LEVEL_STATEMENT,
4999 module => G_Debug_Module || '.Get_HZ_Address');
5000 END IF;
5001 RETURN g_hz_addr_tbl(p_location_id).hz_address; */
5002
5003 IF (NOT(g_hz_addr_tbl.EXISTS(p_location_id))) THEN
5004 OPEN l_hz_addr_csr (p_location_id);
5005 FETCH l_hz_addr_csr INTO g_hz_addr_tbl(p_location_id).location_id,
5006 g_hz_addr_tbl(p_location_id).address1,g_hz_addr_tbl(p_location_id).address2,
5007 g_hz_addr_tbl(p_location_id).address3,g_hz_addr_tbl(p_location_id).address4,
5008 g_hz_addr_tbl(p_location_id).city,g_hz_addr_tbl(p_location_id).county,
5009 g_hz_addr_tbl(p_location_id).state,g_hz_addr_tbl(p_location_id).province,
5010 g_hz_addr_tbl(p_location_id).country,g_hz_addr_tbl(p_location_id).iso_territory_code,
5011 g_hz_addr_tbl(p_location_id).territory_short_name,g_hz_addr_tbl(p_location_id).postal_code,
5012 g_hz_addr_tbl(p_location_id).concat_addr,g_hz_addr_tbl(p_location_id).mail_addr;
5013 CLOSE l_hz_addr_csr;
5014 l_location_id:=g_hz_addr_tbl(p_location_id).location_id;
5015 l_address1:=g_hz_addr_tbl(p_location_id).address1;
5016 l_address2:=g_hz_addr_tbl(p_location_id).address2;
5017 l_address3:=g_hz_addr_tbl(p_location_id).address3;
5018 l_address4:=g_hz_addr_tbl(p_location_id).address4;
5019 l_city:=g_hz_addr_tbl(p_location_id).city;
5020 l_county:=g_hz_addr_tbl(p_location_id).county;
5021 l_state:=g_hz_addr_tbl(p_location_id).state;
5022 l_province:=g_hz_addr_tbl(p_location_id).province;
5023 l_country:=g_hz_addr_tbl(p_location_id).country;
5024 l_iso_territory_code:=g_hz_addr_tbl(p_location_id).iso_territory_code;
5025 l_territory_short_name:=g_hz_addr_tbl(p_location_id).territory_short_name;
5026 l_postal_code:=g_hz_addr_tbl(p_location_id).postal_code;
5027 l_concat_addr:=g_hz_addr_tbl(p_location_id).concat_addr;
5028 l_mail_addr:=g_hz_addr_tbl(p_location_id).mail_addr;
5029 ELSE
5030 l_location_id:=g_hz_addr_tbl(p_location_id).location_id;
5031 l_address1:=g_hz_addr_tbl(p_location_id).address1;
5032 l_address2:=g_hz_addr_tbl(p_location_id).address2;
5033 l_address3:=g_hz_addr_tbl(p_location_id).address3;
5034 l_address4:=g_hz_addr_tbl(p_location_id).address4;
5035 l_city:=g_hz_addr_tbl(p_location_id).city;
5036 l_county:=g_hz_addr_tbl(p_location_id).county;
5037 l_state:=g_hz_addr_tbl(p_location_id).state;
5038 l_province:=g_hz_addr_tbl(p_location_id).province;
5039 l_country:=g_hz_addr_tbl(p_location_id).country;
5040 l_iso_territory_code:=g_hz_addr_tbl(p_location_id).iso_territory_code;
5041 l_territory_short_name:=g_hz_addr_tbl(p_location_id).territory_short_name;
5042 l_postal_code:=g_hz_addr_tbl(p_location_id).postal_code;
5043 l_concat_addr:=g_hz_addr_tbl(p_location_id).concat_addr;
5044 l_mail_addr:=g_hz_addr_tbl(p_location_id).mail_addr;
5045 END IF;
5046
5047 /* Bug 8658052 */
5048 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
5049 Removing the storing of XMLTYPE in cache.
5050 */
5051
5052 SELECT XMLConcat( XMLElement("AddressInternalID", l_location_id),
5053 XMLElement("AddressLine1", l_address1), XMLElement("AddressLine2", l_address2),
5054 XMLElement("AddressLine3", l_address3), XMLElement("AddressLine4", l_address4),
5055 XMLElement("City", l_city), XMLElement("County", l_county),
5056 XMLElement("State", l_state), XMLElement("Province", l_province),
5057 XMLElement("Country", l_country), XMLElement("ISO3DigitCountry", l_iso_territory_code),
5058 XMLElement("CountryName", l_territory_short_name), XMLElement("PostalCode", l_postal_code),
5059 XMLElement("PreFormattedConcatenatedAddress", l_concat_addr),
5060 XMLElement("PreFormattedMailingAddress", l_mail_addr, 'POSTAL_ADDR')) INTO l_hz_addr
5061 FROM DUAL;
5062
5063 RETURN l_hz_addr;
5064
5065
5066 END Get_Hz_Address;
5067
5068 FUNCTION Get_Account_Address(p_location_id IN NUMBER, p_country IN VARCHAR2)
5069 RETURN XMLTYPE
5070 IS
5071 l_hz_addr XMLTYPE;
5072
5073 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
5074 Removing the storing of XMLTYPE in cache.
5075 */
5076
5077 l_te_code VARCHAR2(50);
5078 l_te_sname VARCHAR2(50);
5079
5080 l_location_id NUMBER(15);
5081 l_address1 VARCHAR2(240);
5082 l_address2 VARCHAR2(240);
5083 l_address3 VARCHAR2(240);
5084 l_address4 VARCHAR2(240);
5085 l_city VARCHAR2(60);
5086 l_county VARCHAR2(60);
5087 l_state VARCHAR2(60);
5088 l_province VARCHAR2(60);
5089 l_country VARCHAR2(60);
5090 l_iso_territory_code VARCHAR2(60);
5091 l_territory_short_name VARCHAR2(200);
5092 l_postal_code VARCHAR2(60);
5093 l_concat_addr VARCHAR2(4000);
5094 l_mail_addr VARCHAR2(4000);
5095
5096
5097
5098 CURSOR l_hz_addr_csr (p_location_id IN NUMBER) IS
5099 SELECT hz_loc.location_id , hz_loc.address1 , hz_loc.address2,
5100 hz_loc.address3 , hz_loc.address4 , hz_loc.city, hz_loc.county, hz_loc.state,
5101 hz_loc.province, hz_loc.country, te.iso_territory_code, te.territory_short_name, hz_loc.postal_code,
5102 IBY_FD_EXTRACT_GEN_PVT.format_hz_address(hz_loc.location_id),
5103 IBY_FD_EXTRACT_GEN_PVT.format_hz_address(hz_loc.location_id, 'POSTAL_ADDR')
5104 FROM hz_locations hz_loc,
5105 fnd_territories_vl te
5106 WHERE hz_loc.country = te.territory_code
5107 AND location_id = p_location_id;
5108
5109 /* SELECT address
5110 FROM IBY_XML_HZ_ADDR_1_0_V
5111 WHERE location_id = p_location_id; */
5112
5113
5114 CURSOR l_country_csr (p_country IN VARCHAR2) IS
5115 SELECT te.iso_territory_code , te.territory_short_name
5116 FROM fnd_territories_vl te
5117 WHERE te.territory_code = p_country;
5118
5119
5120 BEGIN
5121 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5122 iby_debug_pub.add(debug_msg => 'Get_Account_Address() entered. ',
5123 debug_level => G_LEVEL_STATEMENT,
5124 module => G_Debug_Module || '.Get_Account_Address');
5125 iby_debug_pub.add(debug_msg => 'p_location_id: ' || p_location_id || 'p_country: ' || p_country,
5126 debug_level => G_LEVEL_STATEMENT,
5127 module => G_Debug_Module || '.Get_Account_Address');
5128 END IF;
5129
5130 IF p_location_id IS NULL THEN
5131 IF trim(p_country) IS NULL THEN
5132 RETURN NULL;
5133 ELSE
5134 /* Bug 8658052 */
5135 IF (NOT(g_account_addr_tbl.EXISTS(p_country))) THEN
5136 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5137 iby_debug_pub.add(debug_msg => 'Address not found in the cache.
5138 Executing the Cursor',
5139 debug_level => G_LEVEL_STATEMENT,
5140 module => G_Debug_Module || '.Get_Account_Address');
5141 END IF;
5142 OPEN l_country_csr (p_country);
5143 FETCH l_country_csr INTO g_account_addr_tbl(p_country).iso_territory_code , g_account_addr_tbl(p_country).territory_short_name;
5144 CLOSE l_country_csr;
5145 l_te_code := g_account_addr_tbl(p_country).iso_territory_code;
5146 l_te_sname := g_account_addr_tbl(p_country).territory_short_name;
5147
5148 -- l_hz_addr:=g_account_addr_tbl(p_country).account_address;
5149
5150 ELSE
5151 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5152 iby_debug_pub.add(debug_msg => 'Address found in the cache.',
5153 debug_level => G_LEVEL_STATEMENT,
5154 module => G_Debug_Module || '.Get_Account_Address');
5155 END IF;
5156
5157 l_te_code := g_account_addr_tbl(p_country).iso_territory_code;
5158 l_te_sname := g_account_addr_tbl(p_country).territory_short_name;
5159
5160 -- l_hz_addr:=g_account_addr_tbl(p_country).account_address;
5161
5162 END IF;
5163
5164 SELECT XMLConcat( XMLElement("Country", p_country),
5165 XMLElement("ISO3DigitCountry", l_te_code),
5166 XMLElement("CountryName", l_te_sname) ) INTO l_hz_addr
5167 FROM dual ;
5168
5169 RETURN l_hz_addr;
5170 END IF;
5171 END IF;
5172
5173 IF (NOT(g_hz_addr_tbl.EXISTS(p_location_id))) THEN
5174 OPEN l_hz_addr_csr (p_location_id);
5175 FETCH l_hz_addr_csr INTO g_hz_addr_tbl(p_location_id).location_id,
5176 g_hz_addr_tbl(p_location_id).address1,g_hz_addr_tbl(p_location_id).address2,
5177 g_hz_addr_tbl(p_location_id).address3,g_hz_addr_tbl(p_location_id).address4,
5178 g_hz_addr_tbl(p_location_id).city,g_hz_addr_tbl(p_location_id).county,
5179 g_hz_addr_tbl(p_location_id).state,g_hz_addr_tbl(p_location_id).province,
5180 g_hz_addr_tbl(p_location_id).country,g_hz_addr_tbl(p_location_id).iso_territory_code,
5181 g_hz_addr_tbl(p_location_id).territory_short_name,g_hz_addr_tbl(p_location_id).postal_code,
5182 g_hz_addr_tbl(p_location_id).concat_addr,g_hz_addr_tbl(p_location_id).mail_addr;
5183 CLOSE l_hz_addr_csr;
5184 l_location_id:=g_hz_addr_tbl(p_location_id).location_id;
5185 l_address1:=g_hz_addr_tbl(p_location_id).address1;
5186 l_address2:=g_hz_addr_tbl(p_location_id).address2;
5187 l_address3:=g_hz_addr_tbl(p_location_id).address3;
5188 l_address4:=g_hz_addr_tbl(p_location_id).address4;
5189 l_city:=g_hz_addr_tbl(p_location_id).city;
5190 l_county:=g_hz_addr_tbl(p_location_id).county;
5191 l_state:=g_hz_addr_tbl(p_location_id).state;
5192 l_province:=g_hz_addr_tbl(p_location_id).province;
5193 l_country:=g_hz_addr_tbl(p_location_id).country;
5194 l_iso_territory_code:=g_hz_addr_tbl(p_location_id).iso_territory_code;
5195 l_territory_short_name:=g_hz_addr_tbl(p_location_id).territory_short_name;
5196 l_postal_code:=g_hz_addr_tbl(p_location_id).postal_code;
5197 l_concat_addr:=g_hz_addr_tbl(p_location_id).concat_addr;
5198 l_mail_addr:=g_hz_addr_tbl(p_location_id).mail_addr;
5199 ELSE
5200 l_location_id:=g_hz_addr_tbl(p_location_id).location_id;
5201 l_address1:=g_hz_addr_tbl(p_location_id).address1;
5202 l_address2:=g_hz_addr_tbl(p_location_id).address2;
5203 l_address3:=g_hz_addr_tbl(p_location_id).address3;
5204 l_address4:=g_hz_addr_tbl(p_location_id).address4;
5205 l_city:=g_hz_addr_tbl(p_location_id).city;
5206 l_county:=g_hz_addr_tbl(p_location_id).county;
5207 l_state:=g_hz_addr_tbl(p_location_id).state;
5208 l_province:=g_hz_addr_tbl(p_location_id).province;
5209 l_country:=g_hz_addr_tbl(p_location_id).country;
5210 l_iso_territory_code:=g_hz_addr_tbl(p_location_id).iso_territory_code;
5211 l_territory_short_name:=g_hz_addr_tbl(p_location_id).territory_short_name;
5212 l_postal_code:=g_hz_addr_tbl(p_location_id).postal_code;
5213 l_concat_addr:=g_hz_addr_tbl(p_location_id).concat_addr;
5214 l_mail_addr:=g_hz_addr_tbl(p_location_id).mail_addr;
5215 END IF;
5216
5217 /* Bug 8658052 */
5218 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
5219 Removing the storing of XMLTYPE in cache.
5220 */
5221
5222 SELECT XMLConcat( XMLElement("AddressInternalID", l_location_id),
5223 XMLElement("AddressLine1", l_address1), XMLElement("AddressLine2", l_address2),
5224 XMLElement("AddressLine3", l_address3), XMLElement("AddressLine4", l_address4),
5225 XMLElement("City", l_city), XMLElement("County", l_county),
5226 XMLElement("State", l_state), XMLElement("Province", l_province),
5227 XMLElement("Country", l_country), XMLElement("ISO3DigitCountry", l_iso_territory_code),
5228 XMLElement("CountryName", l_territory_short_name), XMLElement("PostalCode", l_postal_code),
5229 XMLElement("PreFormattedConcatenatedAddress", l_concat_addr),
5230 XMLElement("PreFormattedMailingAddress", l_mail_addr, 'POSTAL_ADDR')) INTO l_hz_addr
5231 FROM DUAL;
5232
5233 RETURN l_hz_addr;
5234
5235 END Get_Account_Address;
5236
5237
5238
5239
5240 FUNCTION Get_Hr_Address(p_location_id IN NUMBER)
5241 RETURN XMLTYPE
5242 IS
5243 l_hr_addr XMLTYPE;
5244 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
5245 Removing the storing of XMLTYPE in cache.
5246 */
5247
5248 l_location_id NUMBER(15);
5249 l_address1 VARCHAR2(240);
5250 l_address2 VARCHAR2(240);
5251 l_address3 VARCHAR2(240);
5252 l_town_or_city VARCHAR2(60);
5253 l_county VARCHAR2(60);
5254 l_state VARCHAR2(60);
5255 l_country VARCHAR2(60);
5256 l_iso_territory_code VARCHAR2(60);
5257 l_territory_short_name VARCHAR2(200);
5258 l_postal_code VARCHAR2(60);
5259 l_concat_addr VARCHAR2(4000);
5260 l_mail_addr VARCHAR2(4000);
5261 l_location_code VARCHAR2(240);
5262
5263
5264 CURSOR l_hr_addr_csr (p_location_id IN NUMBER) IS
5265 SELECT hr_loc.location_id, hr_loc.address_line_1, hr_loc.address_line_2, hr_loc.address_line_3,
5266 hr_loc.town_or_city, DECODE(hr_loc.country, 'CA', NULL, hr_loc.region_1),
5267 DECODE(hr_loc.country, 'CA', NULL, hr_loc.region_2), hr_loc.country,
5268 te.iso_territory_code, te.territory_short_name, hr_loc.postal_code,
5269 IBY_FD_EXTRACT_GEN_PVT.format_hr_address(hr_loc.location_id),
5270 IBY_FD_EXTRACT_GEN_PVT.format_hr_address(hr_loc.location_id, 'POSTAL_ADDR'),
5271 hr_loc.location_code
5272 FROM hr_locations_all hr_loc, fnd_territories_vl te
5273 WHERE hr_loc.country = te.territory_code(+)
5274 AND location_id = p_location_id;
5275 /*
5276 SELECT address
5277 FROM IBY_XML_HR_ADDR_1_0_V
5278 WHERE location_id = p_location_id; */
5279
5280 BEGIN
5281
5282 Get_Hr_Address_C := Get_Hr_Address_C + 1;
5283 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5284 iby_debug_pub.add(debug_msg => 'Get_Hr_Address() entered. count: ' || Get_Hr_Address_C,
5285 debug_level => G_LEVEL_STATEMENT,
5286 module => G_Debug_Module || '.Get_Hr_Address');
5287 iby_debug_pub.add(debug_msg => 'p_location_id: ' || p_location_id,
5288 debug_level => G_LEVEL_STATEMENT,
5289 module => G_Debug_Module || '.Get_Hr_Address');
5290 END IF;
5291
5292 IF p_location_id IS NULL THEN
5293 RETURN NULL;
5294 END IF;
5295 IF (NOT(g_hr_addr_tbl.EXISTS(p_location_id))) THEN
5296 OPEN l_hr_addr_csr (p_location_id);
5297 FETCH l_hr_addr_csr INTO g_hr_addr_tbl(p_location_id).location_id,
5298 g_hr_addr_tbl(p_location_id).address1,g_hr_addr_tbl(p_location_id).address2,
5299 g_hr_addr_tbl(p_location_id).address3,g_hr_addr_tbl(p_location_id).town_or_city,
5300 g_hr_addr_tbl(p_location_id).county,g_hr_addr_tbl(p_location_id).state,
5301 g_hr_addr_tbl(p_location_id).country,g_hr_addr_tbl(p_location_id).iso_territory_code,
5302 g_hr_addr_tbl(p_location_id).territory_short_name,g_hr_addr_tbl(p_location_id).postal_code,
5303 g_hr_addr_tbl(p_location_id).concat_addr,g_hr_addr_tbl(p_location_id).mail_addr,
5304 g_hr_addr_tbl(p_location_id).location_code;
5305 CLOSE l_hr_addr_csr;
5306
5307 l_location_id:=g_hr_addr_tbl(p_location_id).location_id;
5308 l_address1:=g_hr_addr_tbl(p_location_id).address1;
5309 l_address2:=g_hr_addr_tbl(p_location_id).address2;
5310 l_address3:=g_hr_addr_tbl(p_location_id).address3;
5311 l_town_or_city:=g_hr_addr_tbl(p_location_id).town_or_city;
5312 l_county:=g_hr_addr_tbl(p_location_id).county;
5313 l_state:=g_hr_addr_tbl(p_location_id).state;
5314 l_country:=g_hr_addr_tbl(p_location_id).country;
5315 l_iso_territory_code:=g_hr_addr_tbl(p_location_id).iso_territory_code;
5316 l_territory_short_name:=g_hr_addr_tbl(p_location_id).territory_short_name;
5317 l_postal_code:=g_hr_addr_tbl(p_location_id).postal_code;
5318 l_concat_addr:=g_hr_addr_tbl(p_location_id).concat_addr;
5319 l_mail_addr:=g_hr_addr_tbl(p_location_id).mail_addr;
5320 l_location_code:=g_hr_addr_tbl(p_location_id).location_code;
5321 ELSE
5322 l_location_id:=g_hr_addr_tbl(p_location_id).location_id;
5323 l_address1:=g_hr_addr_tbl(p_location_id).address1;
5324 l_address2:=g_hr_addr_tbl(p_location_id).address2;
5325 l_address3:=g_hr_addr_tbl(p_location_id).address3;
5326 l_town_or_city:=g_hr_addr_tbl(p_location_id).town_or_city;
5327 l_county:=g_hr_addr_tbl(p_location_id).county;
5328 l_state:=g_hr_addr_tbl(p_location_id).state;
5329 l_country:=g_hr_addr_tbl(p_location_id).country;
5330 l_iso_territory_code:=g_hr_addr_tbl(p_location_id).iso_territory_code;
5331 l_territory_short_name:=g_hr_addr_tbl(p_location_id).territory_short_name;
5332 l_postal_code:=g_hr_addr_tbl(p_location_id).postal_code;
5333 l_concat_addr:=g_hr_addr_tbl(p_location_id).concat_addr;
5334 l_mail_addr:=g_hr_addr_tbl(p_location_id).mail_addr;
5335 l_location_code:=g_hr_addr_tbl(p_location_id).location_code;
5336 END IF;
5337
5338 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5339 iby_debug_pub.add(debug_msg => 'EXIT',
5340 debug_level => G_LEVEL_STATEMENT,
5341 module => G_Debug_Module || '.Get_Hr_Address');
5342 END IF;
5343
5344 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
5345 Removing the storing of XMLTYPE in cache.
5346 */
5347
5348 SELECT XMLConcat( XMLElement("AddressInternalID", l_location_id),
5349 XMLElement("AddressLine1", l_address1), XMLElement("AddressLine2", l_address2),
5350 XMLElement("AddressLine3", l_address3), XMLElement("City", l_town_or_city),
5351 XMLElement("County", l_county), XMLElement("State", l_state), XMLElement("Country", l_country),
5352 XMLElement("ISO3DigitCountry", l_iso_territory_code),
5353 XMLElement("CountryName", l_territory_short_name),XMLElement("PostalCode", l_postal_code),
5354 XMLElement("PreFormattedConcatenatedAddress", l_concat_addr),
5355 XMLElement("PreFormattedMailingAddress", l_mail_addr),
5356 XMLElement("AddressName", l_location_code) ) INTO l_hr_addr
5357 FROM DUAL;
5358
5359 RETURN l_hr_addr;
5360
5361 END Get_Hr_Address;
5362
5363
5364 FUNCTION Get_Ins_TotalAmt(p_payment_instruction_id IN NUMBER)
5365 RETURN NUMBER
5366 IS
5367 l_amt_total NUMBER;
5368 l_group_by_curr_flag VARCHAR2(1);
5369 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Ins_TotalAmt';
5370
5371 CURSOR l_ins_group_by_curr_csr (p_payment_instruction_id IN NUMBER) IS
5372 SELECT GROUP_BY_PAYMENT_CURRENCY
5373 FROM IBY_EXT_FD_INS_1_0_V
5374 WHERE payment_instruction_id = p_payment_instruction_id;
5375
5376 CURSOR l_amt_total_csr (p_payment_instruction_id IN NUMBER) IS
5377 SELECT SUM(payment_amount)
5378 FROM iby_payments_all
5379 WHERE payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
5380 'SUBMITTED_FOR_PRINTING', 'ISSUED', 'FORMATTED', 'TRANSMITTED')
5381 AND payment_instruction_id = p_payment_instruction_id;
5382
5383 -- Bug : 8237325
5384 -- Changing to decimal type before extract generation.
5385 -- Will Reset after sending value.
5386 l_numeric_char_mask V$NLS_PARAMETERS.value%TYPE;
5387 l_default_num_mask VARCHAR2(10) := '.,';
5388
5389
5390 BEGIN
5391
5392 -- Get NLS numeric character before calling extract.
5393 -- Bug: 8237325
5394 BEGIN
5395 SELECT value
5396 INTO l_numeric_char_mask
5397 FROM V$NLS_PARAMETERS
5398 WHERE parameter='NLS_NUMERIC_CHARACTERS';
5399 EXCEPTION
5400 WHEN others THEN NULL;
5401 END;
5402
5403
5404 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5405 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
5406 debug_level => G_LEVEL_PROCEDURE,
5407 module => l_Debug_Module);
5408 END IF;
5409
5410 OPEN l_ins_group_by_curr_csr (p_payment_instruction_id);
5411 FETCH l_ins_group_by_curr_csr INTO l_group_by_curr_flag;
5412 CLOSE l_ins_group_by_curr_csr;
5413
5414 IF NVL(l_group_by_curr_flag, 'N') = 'N' THEN
5415
5416 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5417 iby_debug_pub.add(debug_msg => 'Instruction is not grouped by currency, so returning null for the total amount.',
5418 debug_level => G_LEVEL_STATEMENT,
5419 module => l_Debug_Module);
5420 END IF;
5421 RETURN NULL;
5422 END IF;
5423
5424 -- Bug : 8237325
5425 -- Changing to decimal type before extract generation.
5426 -- Will Reset after sending value.
5427 IF l_numeric_char_mask <> l_default_num_mask THEN
5428 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='||'"'||l_default_num_mask||'"';
5429 END IF;
5430
5431
5432 OPEN l_amt_total_csr (p_payment_instruction_id);
5433 FETCH l_amt_total_csr INTO l_amt_total;
5434 CLOSE l_amt_total_csr;
5435
5436 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5437 iby_debug_pub.add(debug_msg => 'Instruction total amount: ' || l_amt_total,
5438 debug_level => G_LEVEL_STATEMENT,
5439 module => l_Debug_Module);
5440
5441 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
5442 debug_level => G_LEVEL_PROCEDURE,
5443 module => l_Debug_Module);
5444 END IF;
5445 -- Bug : 8237325
5446 -- Changing to decimal type before extract generation.
5447 -- Will Reset after sending value.
5448 IF l_numeric_char_mask <> l_default_num_mask THEN
5449 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '||'"'||l_numeric_char_mask|| '"';
5450 END IF;
5451
5452
5453 RETURN l_amt_total;
5454
5455 END Get_Ins_TotalAmt;
5456
5457
5458 FUNCTION Get_Expense_Rpt_CC_Num(p_document_payable_id IN NUMBER)
5459 RETURN VARCHAR2
5460 IS
5461 l_instrid NUMBER;
5462 l_base_inv_num iby_docs_payable_all.CALLING_APP_DOC_REF_NUMBER%TYPE;
5463 l_card_num VARCHAR2(30);
5464 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Expense_Rpt_CC_Num';
5465
5466 BEGIN
5467
5468 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5469 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
5470 debug_level => G_LEVEL_PROCEDURE,
5471 module => l_Debug_Module);
5472
5473 iby_debug_pub.add(debug_msg => 'Calling iExpense API to get instrid.',
5474 debug_level => G_LEVEL_STATEMENT,
5475 module => l_Debug_Module);
5476 END IF;
5477
5478 l_instrid := AP_WEB_CREDIT_CARD_PKG.get_card_reference_id(p_document_payable_id);
5479
5480 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5481 iby_debug_pub.add(debug_msg => 'Got instrid: ' || l_instrid,
5482 debug_level => G_LEVEL_STATEMENT,
5483 module => l_Debug_Module);
5484 END IF;
5485
5486 IF l_instrid IS NOT NULL THEN
5487 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5488 iby_debug_pub.add(debug_msg => 'Getting instr number',
5489 debug_level => G_LEVEL_STATEMENT,
5490 module => l_Debug_Module);
5491 END IF;
5492
5493 l_card_num := iby_creditcard_pkg.uncipher_ccnumber(l_instrid, iby_utility_pvt.get_view_param('SYS_KEY'));
5494
5495 END IF;
5496
5497 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5498 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
5499 debug_level => G_LEVEL_PROCEDURE,
5500 module => l_Debug_Module);
5501 END IF;
5502
5503 RETURN l_card_num;
5504
5505 EXCEPTION
5506 -- swallow exceptions
5507 WHEN OTHERS THEN
5508 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5509 iby_debug_pub.add(debug_msg => 'Swallowing exception',
5510 debug_level => G_LEVEL_STATEMENT,
5511 module => l_Debug_Module);
5512 END IF;
5513 END Get_Expense_Rpt_CC_Num;
5514
5515 /* This function would retrieve Credit card number for invoices
5516 which pay Credit Card Issuer */
5517 /* This logic will work only for Both Pay Expense reports */
5518 FUNCTION Get_CC_Num_For_Issuer_Pmt_Agg(p_document_payable_id IN NUMBER)
5519 RETURN XMLTYPE
5520 IS
5521 l_instrid NUMBER;
5522 l_card_num_agg XMLTYPE := NULL;
5523 l_base_inv_num iby_docs_payable_all.CALLING_APP_DOC_REF_NUMBER%TYPE;
5524 l_card_num VARCHAR2(30);
5525 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_CC_Num_For_Issuer_Pmt_Agg';
5526
5527 CURSOR cc_num_ext_agg(p_card_num varchar2) is
5528 Select XMLAgg(
5529 XMLElement("CreditCardNumber",p_card_num))
5530 from dual;
5531
5532 BEGIN
5533
5534 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5535 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
5536 debug_level => G_LEVEL_PROCEDURE,
5537 module => l_Debug_Module);
5538 END IF;
5539
5540
5541 IF G_Extract_Run_Mode = G_EXTRACT_MODE_AMEX_REM THEN
5542
5543 /* Fetching base invoice number where trxn_id is stamped */
5544 SELECT REPLACE(calling_app_doc_ref_number,'.1')
5545 INTO l_base_inv_num
5546 FROM iby_docs_payable_all
5547 WHERE document_payable_id = p_document_payable_id;
5548
5549 /* Fetching instrument id from Expense tables */
5550 BEGIN
5551 SELECT DISTINCT cards.card_reference_id
5552 INTO l_instrid
5553 FROM
5554 ap_expense_report_headers_all hdr,
5555 ap_expense_report_lines_all lines,
5556 ap_credit_card_trxns_all trxns,
5557 ap_cards_all cards
5558 WHERE cards.card_id = trxns.card_id
5559 AND trxns.trx_id = lines.credit_card_trx_id
5560 AND lines.report_header_id = hdr.report_header_id
5561 AND hdr.invoice_num =l_base_inv_num;
5562 EXCEPTION
5563 WHEN NO_DATA_FOUND THEN
5564 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5565 iby_debug_pub.add(debug_msg => 'Can not fetch instrument id',
5566 debug_level => G_LEVEL_STATEMENT,
5567 module => l_Debug_Module);
5568 END IF;
5569 RAISE;
5570 WHEN OTHERS THEN
5571 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5572 iby_debug_pub.add(debug_msg => 'Error occured while fetching instrument_id',
5573 debug_level => G_LEVEL_STATEMENT,
5574 module => l_Debug_Module);
5575 END IF;
5576 RAISE;
5577 END;
5578
5579
5580
5581 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5582 iby_debug_pub.add(debug_msg => 'Got instrid: ' || l_instrid,
5583 debug_level => G_LEVEL_STATEMENT,
5584 module => l_Debug_Module);
5585 END IF;
5586
5587 IF l_instrid IS NOT NULL THEN
5588 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5589 iby_debug_pub.add(debug_msg => 'Getting instr number',
5590 debug_level => G_LEVEL_STATEMENT,
5591 module => l_Debug_Module);
5592 END IF;
5593
5594 l_card_num := iby_creditcard_pkg.uncipher_ccnumber(l_instrid, iby_utility_pvt.get_view_param('SYS_KEY'));
5595
5596 OPEN cc_num_ext_agg (l_card_num);
5597 FETCH cc_num_ext_agg INTO l_card_num_agg;
5598 CLOSE cc_num_ext_agg;
5599
5600 END IF;
5601
5602 END IF;
5603
5604 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5605 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
5606 debug_level => G_LEVEL_PROCEDURE,
5607 module => l_Debug_Module);
5608 END IF;
5609
5610 RETURN l_card_num_agg;
5611
5612 EXCEPTION
5613 -- swallow exceptions
5614 WHEN OTHERS THEN
5615 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5616 iby_debug_pub.add(debug_msg => 'Swallowing exception',
5617 debug_level => G_LEVEL_STATEMENT,
5618 module => l_Debug_Module);
5619 END IF;
5620 RETURN NULL;
5621 END Get_CC_Num_For_Issuer_Pmt_Agg;
5622
5623 FUNCTION Replace_Special_Characters(p_base_string IN varchar2)
5624 RETURN VARCHAR2
5625 IS
5626 l_conc_invalid_chars VARCHAR2(50);
5627 l_conc_replacement_chars VARCHAR2(50);
5628 l_modified varchar2(255);
5629 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Replace_Special_Characters';
5630 BEGIN
5631 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5632 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
5633 debug_level => G_LEVEL_PROCEDURE,
5634 module => l_Debug_Module);
5635 END IF;
5636
5637
5638 /* Preparing the concatinated strings of invalid characters
5639 and corresponding replacement characters. Bug 7292070 */
5640 FOR i in 1..32 LOOP
5641 l_conc_invalid_chars :=l_conc_invalid_chars||fnd_global.local_chr(i-1);
5642 l_conc_replacement_chars :=l_conc_replacement_chars||' ';
5643 END LOOP;
5644
5645 l_modified := TRANSLATE(p_base_string, l_conc_invalid_chars, l_conc_replacement_chars);
5646
5647 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5648 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
5649 debug_level => G_LEVEL_PROCEDURE,
5650 module => l_Debug_Module);
5651 END IF;
5652 RETURN l_modified;
5653 END Replace_Special_Characters;
5654
5655
5656
5657
5658
5659 /* Bug 9266772*/
5660 FUNCTION Get_Intermediary_Bank_Accts(p_bank_acct_id NUMBER) RETURN XMLTYPE
5661 IS
5662
5663 l_intermediary_accounts xmltype;
5664
5665 /* Bug 16184066*/
5666 l_Intermediary_acct_id NUMBER(15);
5667 l_Account_Number VARCHAR2(100);
5668 l_IBAN VARCHAR2(50);
5669 l_Bank_Name VARCHAR2(360);
5670 l_Bank_code VARCHAR2(30);
5671 l_BRANCH_NUMBER VARCHAR2(30);
5672 l_BIC VARCHAR2(30);
5673 l_check_digits VARCHAR2(30);
5674 l_city VARCHAR2(60);
5675 l_country_code VARCHAR2(2);
5676 l_comments VARCHAR2(240);
5677
5678 l_Intermediary_acct_id2 NUMBER(15);
5679 l_Account_Number2 VARCHAR2(100);
5680 l_IBAN2 VARCHAR2(50);
5681 l_Bank_Name2 VARCHAR2(360);
5682 l_Bank_code2 VARCHAR2(30);
5683 l_BRANCH_NUMBER2 VARCHAR2(30);
5684 l_BIC2 VARCHAR2(30);
5685 l_check_digits2 VARCHAR2(30);
5686 l_city2 VARCHAR2(60);
5687 l_country_code2 VARCHAR2(2);
5688 l_comments2 VARCHAR2(240);
5689 /* Bug 16184066*/
5690
5691
5692
5693 cursor c_intermediate_bank_accts(p_bank_acct_id number) IS
5694 select Intermediary_acct_id,
5695 Account_Number,
5696 IBAN,
5697 Bank_Name,
5698 Bank_code,
5699 BRANCH_NUMBER,
5700 BIC,
5701 check_digits,
5702 city,
5703 country_code,
5704 comments
5705 from iby_intermediary_accts
5706 where bank_acct_id = p_bank_acct_id
5707 order by Intermediary_acct_id asc;
5708
5709 /* select XMLCONCAT(
5710 (XMLELEMENT("IntermediaryAccountID", Intermediary_acct_id)),
5711 (XMLELEMENT("BankAccountNumber",Account_Number)),
5712 (XMLELEMENT("IBANNumber",IBAN)),
5713 (XMLELEMENT("BankName",Bank_Name)),
5714 (XMLELEMENT("BankCode", Bank_code)),
5715 (XMLELEMENT("BranchNumber",BRANCH_NUMBER)),
5716 (XMLELEMENT("SwiftCode",BIC)),
5717 (XMLELEMENT("CheckDigits",check_digits)),
5718 (XMLELEMENT("City",city)),
5719 (XMLELEMENT("Country",country_code)),
5720 (XMLELEMENT("Comments",comments))) from iby_intermediary_accts where bank_acct_id = p_bank_acct_id order by Intermediary_acct_id asc;*/
5721
5722 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Intermediary_Bank_Accts';
5723
5724 BEGIN
5725
5726 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5727 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
5728 debug_level => G_LEVEL_PROCEDURE,
5729 module => l_Debug_Module);
5730 END IF;
5731 IF p_bank_acct_id IS NULL THEN
5732 RETURN NULL;
5733 END IF;
5734
5735 IF (NOT(g_inter_accts_tbl.EXISTS(p_bank_acct_id||'_1') and NOT(g_inter_accts_tbl.EXISTS(p_bank_acct_id||'_2')))) THEN
5736
5737 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5738 iby_debug_pub.add(debug_msg => 'Fecthing Intermediary Accounts from DB:' || l_Debug_Module,
5739 debug_level => G_LEVEL_PROCEDURE,
5740 module => l_Debug_Module);
5741 END IF;
5742
5743 OPEN c_intermediate_bank_accts (p_bank_acct_id);
5744
5745 FETCH c_intermediate_bank_accts INTO
5746 g_inter_accts_tbl(p_bank_acct_id||'_1').Intermediary_acct_id ,
5747 g_inter_accts_tbl(p_bank_acct_id||'_1').Account_Number ,
5748 g_inter_accts_tbl(p_bank_acct_id||'_1').IBAN ,
5749 g_inter_accts_tbl(p_bank_acct_id||'_1').Bank_Name ,
5750 g_inter_accts_tbl(p_bank_acct_id||'_1').Bank_code ,
5751 g_inter_accts_tbl(p_bank_acct_id||'_1').BRANCH_NUMBER ,
5752 g_inter_accts_tbl(p_bank_acct_id||'_1').BIC ,
5753 g_inter_accts_tbl(p_bank_acct_id||'_1').check_digits ,
5754 g_inter_accts_tbl(p_bank_acct_id||'_1').city ,
5755 g_inter_accts_tbl(p_bank_acct_id||'_1').country_code ,
5756 g_inter_accts_tbl(p_bank_acct_id||'_1').comments ;
5757
5758 FETCH c_intermediate_bank_accts INTO g_inter_accts_tbl(p_bank_acct_id||'_2').Intermediary_acct_id ,
5759 g_inter_accts_tbl(p_bank_acct_id||'_2').Account_Number ,
5760 g_inter_accts_tbl(p_bank_acct_id||'_2').IBAN ,
5761 g_inter_accts_tbl(p_bank_acct_id||'_2').Bank_Name ,
5762 g_inter_accts_tbl(p_bank_acct_id||'_2').Bank_code ,
5763 g_inter_accts_tbl(p_bank_acct_id||'_2').BRANCH_NUMBER ,
5764 g_inter_accts_tbl(p_bank_acct_id||'_2').BIC ,
5765 g_inter_accts_tbl(p_bank_acct_id||'_2').check_digits ,
5766 g_inter_accts_tbl(p_bank_acct_id||'_2').city ,
5767 g_inter_accts_tbl(p_bank_acct_id||'_2').country_code ,
5768 g_inter_accts_tbl(p_bank_acct_id||'_2').comments ;
5769
5770 CLOSE c_intermediate_bank_accts;
5771
5772 l_Intermediary_acct_id := g_inter_accts_tbl(p_bank_acct_id||'_1').Intermediary_acct_id;
5773 l_Account_Number := g_inter_accts_tbl(p_bank_acct_id||'_1').Account_Number;
5774 l_IBAN := g_inter_accts_tbl(p_bank_acct_id||'_1').IBAN;
5775 l_Bank_Name := g_inter_accts_tbl(p_bank_acct_id||'_1').Bank_Name;
5776 l_Bank_code := g_inter_accts_tbl(p_bank_acct_id||'_1').Bank_code;
5777 l_BRANCH_NUMBER := g_inter_accts_tbl(p_bank_acct_id||'_1').BRANCH_NUMBER;
5778 l_BIC := g_inter_accts_tbl(p_bank_acct_id||'_1').BIC;
5779 l_check_digits := g_inter_accts_tbl(p_bank_acct_id||'_1').check_digits;
5780 l_city := g_inter_accts_tbl(p_bank_acct_id||'_1').city;
5781 l_country_code := g_inter_accts_tbl(p_bank_acct_id||'_1').country_code;
5782 l_comments := g_inter_accts_tbl(p_bank_acct_id||'_1').comments;
5783
5784 l_Intermediary_acct_id2 := g_inter_accts_tbl(p_bank_acct_id||'_2').Intermediary_acct_id;
5785 l_Account_Number2 := g_inter_accts_tbl(p_bank_acct_id||'_2').Account_Number;
5786 l_IBAN2 := g_inter_accts_tbl(p_bank_acct_id||'_2').IBAN;
5787 l_Bank_Name2 := g_inter_accts_tbl(p_bank_acct_id||'_2').Bank_Name;
5788 l_Bank_code2 := g_inter_accts_tbl(p_bank_acct_id||'_2').Bank_code;
5789 l_BRANCH_NUMBER2 := g_inter_accts_tbl(p_bank_acct_id||'_2').BRANCH_NUMBER;
5790 l_BIC2 := g_inter_accts_tbl(p_bank_acct_id||'_2').BIC;
5791 l_check_digits2 := g_inter_accts_tbl(p_bank_acct_id||'_2').check_digits;
5792 l_city2 := g_inter_accts_tbl(p_bank_acct_id||'_2').city;
5793 l_country_code2 := g_inter_accts_tbl(p_bank_acct_id||'_2').country_code;
5794 l_comments2 := g_inter_accts_tbl(p_bank_acct_id||'_2').comments;
5795
5796 ELSE
5797 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5798 iby_debug_pub.add(debug_msg => 'Using Cached Intermediary Accounts:' || l_Debug_Module,
5799 debug_level => G_LEVEL_PROCEDURE,
5800 module => l_Debug_Module);
5801 END IF;
5802
5803 l_Intermediary_acct_id := g_inter_accts_tbl(p_bank_acct_id||'_1').Intermediary_acct_id;
5804 l_Account_Number := g_inter_accts_tbl(p_bank_acct_id||'_1').Account_Number;
5805 l_IBAN := g_inter_accts_tbl(p_bank_acct_id||'_1').IBAN;
5806 l_Bank_Name := g_inter_accts_tbl(p_bank_acct_id||'_1').Bank_Name;
5807 l_Bank_code := g_inter_accts_tbl(p_bank_acct_id||'_1').Bank_code;
5808 l_BRANCH_NUMBER := g_inter_accts_tbl(p_bank_acct_id||'_1').BRANCH_NUMBER;
5809 l_BIC := g_inter_accts_tbl(p_bank_acct_id||'_1').BIC;
5810 l_check_digits := g_inter_accts_tbl(p_bank_acct_id||'_1').check_digits;
5811 l_city := g_inter_accts_tbl(p_bank_acct_id||'_1').city;
5812 l_country_code := g_inter_accts_tbl(p_bank_acct_id||'_1').country_code;
5813 l_comments := g_inter_accts_tbl(p_bank_acct_id||'_1').comments;
5814
5815 l_Intermediary_acct_id2 := g_inter_accts_tbl(p_bank_acct_id||'_2').Intermediary_acct_id;
5816 l_Account_Number2 := g_inter_accts_tbl(p_bank_acct_id||'_2').Account_Number;
5817 l_IBAN2 := g_inter_accts_tbl(p_bank_acct_id||'_2').IBAN;
5818 l_Bank_Name2 := g_inter_accts_tbl(p_bank_acct_id||'_2').Bank_Name;
5819 l_Bank_code2 := g_inter_accts_tbl(p_bank_acct_id||'_2').Bank_code;
5820 l_BRANCH_NUMBER2 := g_inter_accts_tbl(p_bank_acct_id||'_2').BRANCH_NUMBER;
5821 l_BIC2 := g_inter_accts_tbl(p_bank_acct_id||'_2').BIC;
5822 l_check_digits2 := g_inter_accts_tbl(p_bank_acct_id||'_2').check_digits;
5823 l_city2 := g_inter_accts_tbl(p_bank_acct_id||'_2').city;
5824 l_country_code2 := g_inter_accts_tbl(p_bank_acct_id||'_2').country_code;
5825 l_comments2 := g_inter_accts_tbl(p_bank_acct_id||'_2').comments;
5826
5827 END IF;
5828
5829 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
5830 Removing the storing of XMLTYPE in cache.
5831 */
5832
5833 select XMLCONCAT(XMLELEMENT("IntermediaryBankAccount1",
5834 (XMLELEMENT("IntermediaryAccountID", l_Intermediary_acct_id)),
5835 (XMLELEMENT("BankAccountNumber",l_Account_Number)),
5836 (XMLELEMENT("IBANNumber",l_IBAN)),
5837 (XMLELEMENT("BankName",l_Bank_Name)),
5838 (XMLELEMENT("BankCode", l_Bank_code)),
5839 (XMLELEMENT("BranchNumber",l_BRANCH_NUMBER)),
5840 (XMLELEMENT("SwiftCode",l_BIC)),
5841 (XMLELEMENT("CheckDigits",l_check_digits)),
5842 (XMLELEMENT("City",l_city)),
5843 (XMLELEMENT("Country",l_country_code)),
5844 (XMLELEMENT("Comments",l_comments))),
5845 XMLELEMENT("IntermediaryBankAccount2",
5846 (XMLELEMENT("IntermediaryAccountID", l_Intermediary_acct_id2)),
5847 (XMLELEMENT("BankAccountNumber",l_Account_Number2)),
5848 (XMLELEMENT("IBANNumber",l_IBAN2)),
5849 (XMLELEMENT("BankName",l_Bank_Name2)),
5850 (XMLELEMENT("BankCode", l_Bank_code2)),
5851 (XMLELEMENT("BranchNumber",l_BRANCH_NUMBER2)),
5852 (XMLELEMENT("SwiftCode",l_BIC2)),
5853 (XMLELEMENT("CheckDigits",l_check_digits2)),
5854 (XMLELEMENT("City",l_city2)),
5855 (XMLELEMENT("Country",l_country_code2)),
5856 (XMLELEMENT("Comments",l_comments2)) )
5857 )
5858 into l_intermediary_accounts from dual;
5859
5860 RETURN l_intermediary_accounts;
5861
5862 END Get_Intermediary_Bank_Accts;
5863
5864 /* Bug 9266772*/
5865
5866 FUNCTION get_PayeeBankAccountAssignment(p_pmt_id Number, p_ext_bank_acct_id NUMBER) RETURN XMLTYPE IS
5867 l_instr_assign_id IBY_PMT_INSTR_USES_ALL.INSTRUMENT_PAYMENT_USE_ID%TYPE;
5868 l_attribute_category IBY_PMT_INSTR_USES_ALL.ATTRIBUTE_CATEGORY%TYPE;
5869 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.get_PayeeBankAccountAssignment';
5870 l_assign_dff XMLTYPE;
5871
5872 cursor get_Assignments(c_payment_id NUMBER,
5873 c_ext_bank_account_id NUMBER) is
5874 SELECT instr.instrument_payment_use_id, instr.attribute_category
5875 FROM iby_pmt_instr_uses_all instr,
5876 iby_payments_all pmt,
5877 iby_external_payees_all payee
5878 WHERE instr.instrument_id = c_ext_bank_account_id
5879 AND InStr.instrument_type = 'BANKACCOUNT'
5880 AND InStr.payment_flow = 'DISBURSEMENTS'
5881 AND pmt.payment_id = c_payment_id
5882 AND payee.ext_payee_id = instr.ext_pmt_party_id
5883 AND
5884 (
5885 (payee.supplier_site_id IS NOT NULL AND payee.org_id IS NOT NULL AND payee.org_type IS NOT NULL AND
5886 payee.supplier_site_id = pmt.supplier_site_id AND
5887 NVL(payee.party_site_id,-1) = NVL(pmt.party_site_id,-1) AND --Bug 14597313
5888 payee.org_id = pmt.org_id AND
5889 payee.org_type = pmt.org_type AND
5890 payee.payment_function = pmt.payment_function AND
5891 payee.payee_party_id = pmt.payee_party_id)
5892 OR
5893 (payee.supplier_site_id IS NULL AND payee.party_site_id IS NOT NULL AND payee.org_id IS NOT NULL AND payee.org_type IS NOT NULL AND
5894 payee.party_site_id = pmt.party_site_id AND
5895 payee.org_id = pmt.org_id AND
5896 payee.org_type = pmt.org_type AND
5897 payee.payment_function = pmt.payment_function AND
5898 payee.payee_party_id = pmt.payee_party_id)
5899 OR
5900 (payee.supplier_site_id IS NULL AND payee.party_site_id IS NOT NULL AND payee.org_id IS NULL AND payee.org_type IS NULL AND
5901 payee.party_site_id = pmt.party_site_id AND
5902 payee.payment_function = pmt.payment_function AND
5903 payee.payee_party_id = pmt.payee_party_id)
5904 OR
5905 (payee.supplier_site_id IS NULL AND payee.party_site_id IS NULL AND payee.org_id IS NULL AND payee.org_type IS NULL AND
5906 payee.payment_function = pmt.payment_function AND
5907 payee.payee_party_id = pmt.payee_party_id)
5908 OR
5909 (payee.supplier_site_id IS NULL AND payee.party_site_id IS NULL AND payee.org_id IS NOT NULL AND payee.org_type IS NOT NULL AND
5910 payee.org_id = pmt.org_id AND
5911 payee.org_type = pmt.org_type AND
5912 payee.payment_function = pmt.payment_function AND
5913 payee.payee_party_id = pmt.payee_party_id)
5914 )
5915
5916 ORDER BY payee.supplier_site_id,
5917 payee.party_site_id,
5918 payee.org_id
5919 ;
5920
5921
5922 BEGIN
5923 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5924 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
5925 debug_level => G_LEVEL_PROCEDURE,
5926 module => l_Debug_Module);
5927 END IF;
5928
5929 /* 16184066: +T5:R12.1:IBY_FD_PAYMENT_FORMAT_TEXT PERFORMANCE
5930 Removing the storing of XMLTYPE in cache.
5931 */
5932
5933 IF (p_ext_bank_acct_id is not null) then
5934
5935 OPEN get_Assignments (p_pmt_id, p_ext_bank_acct_id);
5936 fetch get_Assignments INTO l_instr_assign_id,l_attribute_category;
5937 CLOSE get_Assignments;
5938 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5939 iby_debug_pub.add(debug_msg => 'Instrument Assignment Id:: '||l_instr_assign_id ,
5940 debug_level => G_LEVEL_PROCEDURE,
5941 module => l_Debug_Module);
5942 END IF;
5943 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5944 iby_debug_pub.add(debug_msg => 'Attribute Category:: ' || l_attribute_category,
5945 debug_level => G_LEVEL_PROCEDURE,
5946 module => l_Debug_Module);
5947 END IF;
5948
5949 /* IF (l_instr_assign_id is not null) then
5950 IF (NOT(g_inter_accts_tbl.EXISTS(l_instr_assign_id))) then
5951
5952 IF (l_attribute_category is null) then
5953 g_inter_accts_tbl(l_instr_assign_id):=null;
5954 ELSE
5955 SELECT XMLCONCAT(XMLELEMENT("PayeeBankAccountAssignment",
5956 XMLELEMENT("DescriptiveFlexField",IBY_EXTRACTGEN_PVT.Get_Dffs('IBY_PMT_INSTR_USES_ALL',l_instr_assign_id, null))))
5957 into g_inter_accts_tbl(l_instr_assign_id) from dual;
5958 END IF;
5959 ELSE
5960 RETURN g_inter_accts_tbl(l_instr_assign_id);
5961 END IF;
5962 RETURN g_inter_accts_tbl(l_instr_assign_id);
5963 END IF; */
5964
5965 IF (l_instr_assign_id is not null) then
5966 IF (l_attribute_category is null) then
5967 l_assign_dff := null;
5968 ELSE
5969 SELECT XMLCONCAT(XMLELEMENT("PayeeBankAccountAssignment",
5970 XMLELEMENT("DescriptiveFlexField",IBY_EXTRACTGEN_PVT.Get_Dffs('IBY_PMT_INSTR_USES_ALL',l_instr_assign_id, null))))
5971 into l_assign_dff from dual;
5972 END IF;
5973 RETURN l_assign_dff;
5974 END IF;
5975 END IF;
5976 RETURN NULL;
5977 END get_PayeeBankAccountAssignment;
5978
5979 BEGIN
5980 FOR i in 1..32 LOOP
5981 l_conc_invalid_chars :=l_conc_invalid_chars||fnd_global.local_chr(i-1);
5982 l_conc_replacement_chars :=l_conc_replacement_chars||' ';
5983 END LOOP;
5984 END IBY_FD_EXTRACT_GEN_PVT;
5985