[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.77.12010000.15 2009/01/09 18:43:33 pschalla 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
28 G_SRA_DELIVERY_METHOD_PRINTED CONSTANT VARCHAR2(30) := 'PRINTED';
29 G_SRA_DELIVERY_METHOD_EMAIL CONSTANT VARCHAR2(30) := 'EMAIL';
30 G_SRA_DELIVERY_METHOD_FAX CONSTANT VARCHAR2(30) := 'FAX';
31
32 G_EXTRACT_MODE_PMT CONSTANT NUMBER := 1;
33 G_EXTRACT_MODE_SRA CONSTANT NUMBER := 2;
34 G_EXTRACT_MODE_AUX CONSTANT NUMBER := 3;
35 G_EXTRACT_MODE_FV_SMMY CONSTANT NUMBER := 4;
36 G_EXTRACT_MODE_PI_RPT CONSTANT NUMBER := 5;
37 G_EXTRACT_MODE_PPR_RPT CONSTANT NUMBER := 6;
38
39 G_Extract_Run_Mode NUMBER;
40 G_Extract_Run_Delivery_Method VARCHAR2(30);
41 G_Extract_Run_Payment_id NUMBER;
42
43 G_May_Need_HR_Masking BOOLEAN := FALSE;
44
45 G_Is_Reprint VARCHAR2(1);
46
47 G_IS_BRAZIL NUMBER;
48
49 -- temp debugging
50 Get_Payee_LegalRegistration_C number := 0;
51 Get_Payee_TaxRegistration_C number := 0;
52 Get_FP_TaxRegistration_C number := 0;
53 Get_PayeeContact_C number := 0;
54 Get_PayerContact_C number := 0;
55 Get_PayerBankAccount_C number := 0;
56 Get_Payer_C number := 0;
57 Get_Hz_Address_C NUMBER := 0;
58 Get_Hr_Address_C NUMBER := 0;
59 format_hz_address_C NUMBER := 0;
60 format_hr_address_C NUMBER := 0;
61
62 /* perf bug 6763515 */
63 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
64 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
65 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
66 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
67 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
68 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
69 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
70
71
72 TYPE t_docs_pay_attribs_type IS RECORD(
73 IS_BRAZIL NUMBER
74 );
75
76 TYPE t_docs_pay_attribs_tbl_type IS TABLE OF t_docs_pay_attribs_type INDEX BY BINARY_INTEGER;
77 g_docs_pay_attribs_tbl t_docs_pay_attribs_tbl_type;
78
79 TYPE t_hr_addr_rec_type IS RECORD(
80 hr_address XMLTYPE
81 );
82
83 TYPE t_hr_addr_tbl_type IS TABLE OF t_hr_addr_rec_type INDEX BY BINARY_INTEGER;
84 g_hr_addr_tbl t_hr_addr_tbl_type;
85
86 TYPE t_hz_addr_rec_type IS RECORD(
87 hz_address XMLTYPE
88 );
89
90 TYPE t_hz_addr_tbl_type IS TABLE OF t_hz_addr_rec_type INDEX BY BINARY_INTEGER;
91 g_hz_addr_tbl t_hz_addr_tbl_type;
92
93 TYPE t_payer_contact_rec_type IS RECORD(
94 l_contactinfo XMLTYPE
95 );
96
97 TYPE t_payer_contact_tbl_type IS TABLE OF t_payer_contact_rec_type INDEX BY BINARY_INTEGER;
98
99 g_payer_contact_tbl t_payer_contact_tbl_type;
100
101 FUNCTION Get_Payee_Default_Attribute(p_payment_id IN NUMBER, p_attribute_type IN NUMBER)
102 RETURN VARCHAR2;
103
104 PROCEDURE Create_Extract_1_0_Main
105 (
106 p_payment_instruction_id IN NUMBER,
107 p_save_extract_flag IN VARCHAR2,
108 p_sys_key IN iby_security_pkg.des3_key_type,
109 x_extract_doc OUT NOCOPY CLOB
110 );
111
112 PROCEDURE Validate_and_Set_Syskey
113 (
114 p_sys_key IN iby_security_pkg.des3_key_type
115 );
116
117 -- for payment format
118 PROCEDURE Create_Extract_1_0
119 (
120 p_payment_instruction_id IN NUMBER,
121 p_save_extract_flag IN VARCHAR2,
122 p_format_type IN VARCHAR2,
123 p_is_reprint_flag IN VARCHAR2,
124 p_sys_key IN iby_security_pkg.des3_key_type,
125 x_extract_doc OUT NOCOPY CLOB
126 )
127 IS
128 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Extract_1_0';
129
130 BEGIN
131 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
132 iby_debug_pub.add(debug_msg => 'Input p_is_reprint_flag: ' || p_is_reprint_flag,
133 debug_level => G_LEVEL_STATEMENT,
134 module => l_Debug_Module);
135 END IF;
136
137 G_Is_Reprint := p_is_reprint_flag;
138
139 -- call the one for aux formats
140 Create_Extract_1_0
141 (
142 p_payment_instruction_id => p_payment_instruction_id,
143 p_save_extract_flag => p_save_extract_flag,
144 p_format_type => p_format_type,
145 p_sys_key => p_sys_key,
146 x_extract_doc => x_extract_doc
147 );
148
149 END Create_Extract_1_0;
150
151 -- for payment and auxiliary formats
152 -- Auxiliary formats include:
153 -- DISBURSEMENT_ACCOMPANY_LETTER
154 -- REGULATORY_REPORTING
155 -- REMITTANCE_ADVICE
156 -- PAYMENT_INSTRUCTION_REGISTER
157 -- FEDERAL_SUMMARY
158 -- for FEDERAL_SUMMARY, the p_save_extract_flag
159 -- is used for the ecs_dos_seq_num
160 PROCEDURE Create_Extract_1_0
161 (
162 p_payment_instruction_id IN NUMBER,
163 p_save_extract_flag IN VARCHAR2,
164 p_format_type IN VARCHAR2,
165 p_sys_key IN iby_security_pkg.des3_key_type,
166 x_extract_doc OUT NOCOPY CLOB
167 )
168 IS
169 l_extract_count NUMBER;
170 l_trxn_doc_id NUMBER;
171 l_save_extract_flag VARCHAR2(255);
172 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Extract_1_0';
173
174 BEGIN
175 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
176 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
177 debug_level => G_LEVEL_PROCEDURE,
178 module => l_Debug_Module);
179
180 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
181 debug_level => G_LEVEL_STATEMENT,
182 module => l_Debug_Module);
183 END IF;
184
185 IF p_format_type = 'FEDERAL_SUMMARY' THEN
186 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
187 iby_debug_pub.add(debug_msg => 'ECS dos Seq num: ' || p_save_extract_flag,
188 debug_level => G_LEVEL_STATEMENT,
189 module => l_Debug_Module);
190 END IF;
191 ELSE
192 l_save_extract_flag := p_save_extract_flag;
193 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
194 iby_debug_pub.add(debug_msg => 'Save extract flag: ' || p_save_extract_flag,
195 debug_level => G_LEVEL_STATEMENT,
196 module => l_Debug_Module);
197 END IF;
198 END IF;
199 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
200 iby_debug_pub.add(debug_msg => 'Format type: ' || p_format_type,
201 debug_level => G_LEVEL_STATEMENT,
202 module => l_Debug_Module);
203 END IF;
204
205 -- for now there are no differences between different type
206 -- of aux formats in terms of extract. So only differenciate
207 -- them from payment format
208 IF p_format_type = 'OUTBOUND_PAYMENT_INSTRUCTION' THEN
209 G_Extract_Run_Mode := G_EXTRACT_MODE_PMT;
210 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
211 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_PMT',
212 debug_level => G_LEVEL_STATEMENT,
213 module => l_Debug_Module);
214 END IF;
215
216 -- to do: confirm the format type code with PM
217 ELSIF p_format_type = 'FEDERAL_SUMMARY' THEN
218 G_Extract_Run_Mode := G_EXTRACT_MODE_FV_SMMY;
219
220 IF p_save_extract_flag IS NOT NULL THEN
221 iby_utility_pvt.set_view_param(G_VP_FV_ECS_SEQ, p_save_extract_flag);
222 END IF;
223
224 -- revert the param to its original function
225 l_save_extract_flag := 'N';
226 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
227 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_FV_SMMY',
228 debug_level => G_LEVEL_STATEMENT,
229 module => l_Debug_Module);
230 END IF;
231 ELSIF p_format_type = 'PAYMENT_INSTRUCTION_REGISTER' THEN
232 G_Extract_Run_Mode := G_EXTRACT_MODE_PI_RPT;
233 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
234 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_PI_RPT',
235 debug_level => G_LEVEL_STATEMENT,
236 module => l_Debug_Module);
237 END IF;
238 ELSE
239 G_Extract_Run_Mode := G_EXTRACT_MODE_AUX;
240 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
241 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_AUX',
242 debug_level => G_LEVEL_STATEMENT,
243 module => l_Debug_Module);
244 END IF;
245 END IF;
246
247 IF p_format_type = 'PAYMENT_INSTRUCTION_REGISTER' THEN
248 G_May_Need_HR_Masking := TRUE;
249 END IF;
250
251 Create_Extract_1_0_Main
252 (
253 p_payment_instruction_id => p_payment_instruction_id,
254 p_save_extract_flag => l_save_extract_flag,
255 p_sys_key => p_sys_key,
256 x_extract_doc => x_extract_doc
257 );
258 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
259 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
260 debug_level => G_LEVEL_PROCEDURE,
261 module => l_Debug_Module);
262 END IF;
263 END Create_Extract_1_0;
264
265
266 -- for separate remittance advice
267 -- the p_save_extract_flag and p_format_type
268 -- are currently ignored
269 PROCEDURE Create_Extract_1_0
270 (
271 p_payment_instruction_id IN NUMBER,
272 p_save_extract_flag IN VARCHAR2,
273 p_format_type IN VARCHAR2,
274 p_delivery_method IN VARCHAR2,
275 p_payment_id IN NUMBER,
276 p_sys_key IN iby_security_pkg.des3_key_type,
277 x_extract_doc OUT NOCOPY CLOB
278 )
279 IS
280 l_extract_count NUMBER;
281 l_trxn_doc_id NUMBER;
282 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Extract_1_0';
283
284 BEGIN
285 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
286 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
287 debug_level => G_LEVEL_PROCEDURE,
288 module => l_Debug_Module);
289
290 iby_debug_pub.add(debug_msg => 'Input payment instruction ID: ' || p_payment_instruction_id,
291 debug_level => G_LEVEL_STATEMENT,
292 module => l_Debug_Module);
293
294 iby_debug_pub.add(debug_msg => 'SRA delivery method: ' || p_delivery_method,
295 debug_level => G_LEVEL_STATEMENT,
296 module => l_Debug_Module);
297
298 iby_debug_pub.add(debug_msg => 'payment id: ' || p_payment_id,
299 debug_level => G_LEVEL_STATEMENT,
300 module => l_Debug_Module);
301 END IF;
302
303 G_Extract_Run_Mode := G_EXTRACT_MODE_SRA;
304 G_Extract_Run_Delivery_Method := p_delivery_method;
305 G_Extract_Run_Payment_id := p_payment_id;
306 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
307 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_SRA',
308 debug_level => G_LEVEL_STATEMENT,
309 module => l_Debug_Module);
310 END IF;
311 Create_Extract_1_0_Main
312 (
313 p_payment_instruction_id => p_payment_instruction_id,
314 p_save_extract_flag => 'N',
315 p_sys_key => p_sys_key,
316 x_extract_doc => x_extract_doc
317 );
318 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
319 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
320 debug_level => G_LEVEL_PROCEDURE,
321 module => l_Debug_Module);
322 END IF;
323 END Create_Extract_1_0;
324
325
326 -- for formats based on extract code: IBY_FD_PAYMENT_INSTRUCTION
327 -- i.e., payment, separate remittance advice, acp ltr, etc.
328 PROCEDURE Create_Extract_1_0_Main
329 (
330 p_payment_instruction_id IN NUMBER,
331 p_save_extract_flag IN VARCHAR2,
332 p_sys_key IN iby_security_pkg.des3_key_type,
333 x_extract_doc OUT NOCOPY CLOB
334 )
335 IS
336 l_extract_count NUMBER;
337 l_trxn_doc_id NUMBER;
338 l_ele_channel VARCHAR2(50);
339 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Extract_1_0';
340
341 CURSOR l_ece_csr(p_payment_instruction_id IN NUMBER) IS
342 SELECT electronic_processing_channel
343 FROM
344 iby_payment_profiles ppp,
345 iby_pay_instructions_all ins
346 WHERE
347 ins.payment_instruction_id = p_payment_instruction_id
348 AND ins.payment_profile_id = ppp.payment_profile_id;
349
350 BEGIN
351 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
352 iby_debug_pub.add(debug_msg => 'ENTER CREATE EXTRACT MAIN -- BEFORE INITIALIZING',
353 debug_level => G_LEVEL_STATEMENT,
354 module => l_Debug_Module);
355 END IF;
356 initialize;
357 iby_utility_pvt.set_view_param(G_VP_INSTR_ID,p_payment_instruction_id);
358
359 Validate_and_Set_Syskey(p_sys_key);
360 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
361 iby_debug_pub.add(debug_msg => 'Before XML query ',
362 debug_level => G_LEVEL_STATEMENT,
363 module => l_Debug_Module);
364 END IF;
365 CEP_STANDARD.init_security;
366 OPEN l_ece_csr(p_payment_instruction_id);
367 FETCH l_ece_csr INTO l_ele_channel;
368 CLOSE l_ece_csr;
369 IF l_ele_channel = 'ECE' AND G_Extract_Run_Mode = G_EXTRACT_MODE_PMT THEN
370 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
371 iby_debug_pub.add(debug_msg => 'Electronic processing channel is ECE',
372 debug_level => G_LEVEL_STATEMENT,
373 module => l_Debug_Module);
374 END IF;
375 IBY_FD_POST_PICP_PROGS_PVT.Run_ECE_Formatting(p_payment_instruction_id);
376 RETURN;
377 END IF;
378
379 SELECT '<?xml version="1.0" encoding="UTF-8"?>'||XMLType.getClobVal(instruction)
380 INTO x_extract_doc
381 FROM iby_xml_fd_ins_1_0_v
382 WHERE payment_instruction_id = p_payment_instruction_id;
383
384 SELECT count(trxn_document_id)
385 INTO l_extract_count
386 FROM iby_trxn_documents
387 where doctype=100 and trxnmid=p_payment_instruction_id;
388 --(bug 5970838) WHERE doctype = 100 and payment_instruction_id = p_payment_instruction_id;
389 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
390 iby_debug_pub.add(debug_msg => 'After XML query ',
391 debug_level => G_LEVEL_STATEMENT,
392 module => l_Debug_Module);
393 END IF;
394 IF p_save_extract_flag = 'Y' and l_extract_count = 0 THEN
395 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
396 iby_debug_pub.add(debug_msg => 'The save extract flag is Y and there were no extract previously saved ' ||
397 'for the instruction. Saving the extract for reuse.',
398 debug_level => G_LEVEL_STATEMENT,
399 module => l_Debug_Module);
400 END IF;
401
402 iby_trxn_documents_pkg.CreateDocument
403 (
404 p_payment_instruction_id => p_payment_instruction_id,
405 p_doctype => 100,
406 p_doc => x_extract_doc,
407 docid_out => l_trxn_doc_id
408 );
409 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
410 iby_debug_pub.add(debug_msg => 'Save successful, iby trxn doc id: ' || l_trxn_doc_id,
411 debug_level => G_LEVEL_STATEMENT,
412 module => l_Debug_Module);
413 END IF;
414 END IF;
415
416
417 -- clears out data from global temporary table
418 -- frzhang 8/11/2005: commit will be managed by the java CP
419 -- COMMIT;
420
421 EXCEPTION
422 WHEN OTHERS THEN
423 -- make sure procedure is not exited before a COMMIT
424 -- so as to remove security keys
425 -- frzhang 8/11/2005: commit will be managed by the java CP
426 -- COMMIT;
427 /* bug 6878265 */
428 -- when exception occurs, the payment_instruction must be unlocked from the request
429
430 RAISE;
431
432 END Create_Extract_1_0_Main;
433
434
435 PROCEDURE Validate_and_Set_Syskey
436 (
437 p_sys_key IN iby_security_pkg.des3_key_type
438 )
439 IS
440 lx_err_code VARCHAR2(30);
441 BEGIN
442
443 IF (NOT p_sys_key IS NULL) THEN
444 iby_security_pkg.validate_sys_key(p_sys_key,lx_err_code);
445 IF (NOT lx_err_code IS NULL) THEN
446 raise_application_error(-20000,lx_err_code, FALSE);
447 END IF;
448 iby_utility_pvt.set_view_param(G_VP_SYS_KEY,p_sys_key);
449 END IF;
450
451 END Validate_and_Set_Syskey;
452
453
454 PROCEDURE Create_PPR_Extract_1_0
455 (
456 p_payment_service_request_id IN NUMBER,
457 p_sys_key IN iby_security_pkg.des3_key_type,
458 x_extract_doc OUT NOCOPY CLOB
459 )
460 IS
461 l_extract_count NUMBER;
462 l_trxn_doc_id NUMBER;
463 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_PPR_Extract_1_0';
464 BEGIN
465
466 iby_utility_pvt.set_view_param(G_VP_INSTR_ID,p_payment_service_request_id);
467
468 iby_utility_pvt.set_view_param(G_VP_FMT_TYPE,'PAYMENT_PROCESS_REQUEST_REPORT');
469
470 Validate_and_Set_Syskey(p_sys_key);
471
472 G_Extract_Run_Mode := G_EXTRACT_MODE_PPR_RPT;
473 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
474 iby_debug_pub.add(debug_msg => 'The extract mode is set to: G_EXTRACT_MODE_PPR_RPT',
475 debug_level => G_LEVEL_STATEMENT,
476 module => l_Debug_Module);
477 END IF;
478
479 G_May_Need_HR_Masking := TRUE;
480 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
481 iby_debug_pub.add(debug_msg => 'Before XML query ',
482 debug_level => G_LEVEL_STATEMENT,
483 module => l_Debug_Module);
484 END IF;
485 CEP_STANDARD.init_security;
486
487 SELECT XMLType.getClobVal(payment_process_request)
488 INTO x_extract_doc
489 FROM iby_xml_fd_ppr_1_0_v
490 WHERE payment_service_request_id = p_payment_service_request_id;
491
492 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
493 iby_debug_pub.add(debug_msg => 'After XML query ',
494 debug_level => G_LEVEL_STATEMENT,
495 module => l_Debug_Module);
496 END IF;
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 RAISE;
501
502 END Create_PPR_Extract_1_0;
503
504
505 -- for positive pay - bug 5028143
506 PROCEDURE Create_Pos_Pay_Extract_1_0
507 (
508 p_payment_instruction_id IN NUMBER,
509 p_payment_profile_id IN NUMBER,
510 p_from_date IN VARCHAR2,
511 p_to_date IN VARCHAR2,
512 p_sys_key IN iby_security_pkg.des3_key_type,
513 x_extract_doc OUT NOCOPY CLOB
514 )
515 IS
516
517 type payment_arr is table of number;
518 l_paymentid_arr payment_arr;
519 l_paymentinstrid_arr payment_arr;
520
521 l_to_date VARCHAR2(255);
522 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Pos_Pay_Extract_1_0';
523
524 CURSOR l_pospay_ins_csr IS
525 SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))
526 FROM
527 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
528 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
529 AND xml_pmt_lvl.payment_status = 'ISSUED'
530 AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL);
531
532 CURSOR l_pospay_appp_csr (p_to_date IN VARCHAR2) IS
533 SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))
534 FROM
535 iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
536 iby_pay_instructions_all ins
537 WHERE xml_pmt_lvl.payment_instruction_id = ins.payment_instruction_id
538 AND ins.payment_profile_id = p_payment_profile_id
539 AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)
540 AND xml_pmt_lvl.payment_date <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), SYSDATE)
541 AND xml_pmt_lvl.payment_status = 'ISSUED'
542 AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL);
543
544 BEGIN
545
546 Validate_and_Set_Syskey(p_sys_key);
547
548 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
549 iby_debug_pub.add(debug_msg => 'Before XML query ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
550 END IF;
551
552 CEP_STANDARD.init_security;
553
554 IF p_payment_instruction_id <> -99 THEN
555 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
556 iby_debug_pub.add(debug_msg => 'payment instruction id is supplied ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
557 END IF;
558
559 OPEN l_pospay_ins_csr;
560 FETCH l_pospay_ins_csr INTO x_extract_doc;
561 CLOSE l_pospay_ins_csr;
562
563 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
564 iby_debug_pub.add(debug_msg => 'payment level attribute setting ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
565 END IF;
566
567 /*LKQ POS PAY - PAVAN */
568 -- payment level attribute setting
569 UPDATE iby_payments_all pmt
570 SET pmt.positive_pay_file_created_flag = 'Y'
571 WHERE pmt.payment_instruction_id = p_payment_instruction_id
572 AND pmt.payment_status = 'ISSUED'
573 AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL);
574
575 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
576 iby_debug_pub.add(debug_msg => 'payment instruction level attribute setting ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
577 END IF;
578
579 /*LKQ POS PAY - PAVAN */
580 -- payment instruction level attribute setting
581
582 UPDATE iby_pay_instructions_all
583 SET positive_pay_file_created_flag= 'Y'
584 WHERE payment_instruction_id = p_payment_instruction_id;
585
586 ELSE
587 IF instr(p_to_date, '00:00:00') <> 0 THEN
588 l_to_date := REPLACE(p_to_date, '00:00:00', '23:59:59');
589 ELSE
590 l_to_date := p_to_date;
591 END IF;
592
593 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
594 iby_debug_pub.add(debug_msg => 'payment instruction id is NOT supplied ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
595 END IF;
596
597 OPEN l_pospay_appp_csr(l_to_date);
598 FETCH l_pospay_appp_csr INTO x_extract_doc;
599 CLOSE l_pospay_appp_csr;
600
601 SELECT pmt.payment_id,pmt.payment_instruction_id
602 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
603 FROM iby_xml_fd_pmt_1_0_v pmt,
604 iby_pay_instructions_all ins
605 WHERE pmt.payment_instruction_id = ins.payment_instruction_id
606 AND ins.payment_profile_id = p_payment_profile_id
607 AND pmt.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
608 AND pmt.payment_date <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), SYSDATE)
609 AND pmt.payment_status = 'ISSUED'
610 AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL);
611
612 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
613 iby_debug_pub.add(debug_msg => 'payment level attribute setting ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
614 END IF;
615
616
617 /*LKQ POS PAY - PAVAN */
618 -- payment level attribute setting
619 iby_debug_pub.add(debug_msg => 'Payment Count : '|| l_paymentid_arr.COUNT,debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
620 IF ( l_paymentid_arr.COUNT <> 0) THEN
621 FOR i IN l_paymentid_arr.FIRST .. l_paymentid_arr.LAST LOOP
622 UPDATE iby_payments_all
623 SET positive_pay_file_created_flag = 'Y'
624 WHERE payment_id = l_paymentid_arr(i);
625 iby_debug_pub.add(debug_msg => 'Payment ID : '|| l_paymentid_arr(i),debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
626 END LOOP;
627 ELSE
628 iby_debug_pub.add(debug_msg => 'Payment ID : '|| 'Empty',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
629 END IF;
630
631 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
632 iby_debug_pub.add(debug_msg => 'payment instruction level attribute setting ',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
633 END IF;
634
635 /*LKQ POS PAY - PAVAN */
636 -- payment instruction level attribute setting
637 iby_debug_pub.add(debug_msg => 'Payment Instr Count : '|| l_paymentinstrid_arr.COUNT,debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
638 IF ( l_paymentinstrid_arr.COUNT <> 0) THEN
639 FOR i IN l_paymentinstrid_arr.FIRST .. l_paymentinstrid_arr.LAST LOOP
640 IF (l_paymentinstrid_arr(i) IS NOT NULL ) THEN
641 UPDATE iby_pay_instructions_all ins
642 SET ins.positive_pay_file_created_flag = 'Y'
643 WHERE not exists (SELECT 'N'
644 FROM iby_payments_all pmt
645 WHERE nvl(pmt.positive_pay_file_created_flag,'N') = 'N'
646 AND pmt.payment_status IN('ISSUED', 'PAID')
647 AND pmt.payment_instruction_id = l_paymentinstrid_arr(i))
648 AND ins.payment_instruction_id = l_paymentinstrid_arr(i);
649 iby_debug_pub.add(debug_msg => 'Payment Instr ID : '|| l_paymentinstrid_arr(i),debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
650 END IF;
651 END LOOP;
652 ELSE
653 iby_debug_pub.add(debug_msg => 'Payment Instr ID : '|| 'Empty',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
654 END IF;
655
656 END IF;
657
658 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
659 iby_debug_pub.add(debug_msg => 'After XML query ',
660 debug_level => G_LEVEL_STATEMENT,
661 module => l_Debug_Module);
662 END IF;
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 RAISE;
667
668 END Create_Pos_Pay_Extract_1_0;
669
670
671
672
673 -- LKQ POS PAY ISSUE - PAVAN
674 PROCEDURE Create_Pos_Pay_Extract_2_0
675 (
676 p_payment_instruction_id IN NUMBER,
677
678 p_format_name IN VARCHAR2,
679 p_internal_bank_account_name IN VARCHAR2,
680 p_from_date IN VARCHAR2,
681 p_to_date IN VARCHAR2,
682 p_payment_status IN VARCHAR2,
683 p_reselect IN VARCHAR2,
684
685 p_sys_key IN iby_security_pkg.des3_key_type,
686 x_extract_doc OUT NOCOPY CLOB
687 )
688 IS
689
690 type payment_arr is table of number;
691 l_paymentid_arr payment_arr;
692 l_paymentinstrid_arr payment_arr;
693 l_to_date VARCHAR2(255);
694 l_from_date VARCHAR2(255);
695 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Create_Pos_Pay_Extract_2_0';
696
697 --cursor for - pmt instr id supplied,negotiable payments, reselect - no
698 CURSOR l_pospay_ins_csr_1_1 IS
699 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
700 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
701 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
702 AND xml_pmt_lvl.payment_status IN ('ISSUED','PAID')
703 AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)
704 ;
705
706 --cursor for - pmt instr id supplied,negotiable payments, reselect - yes
707 CURSOR l_pospay_ins_csr_1_2 IS
708 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
709 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
710 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
711 AND xml_pmt_lvl.payment_status IN ('ISSUED','PAID')
712 ;
713
714 --cursor for - pmt instr id supplied,voided payments, reselect - no
715 CURSOR l_pospay_ins_csr_2_1 IS
716 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
717 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
718 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
719 AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
720 AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)
721 ;
722
723 --cursor for - pmt instr id supplied,voided payments, reselect - yes
724 CURSOR l_pospay_ins_csr_2_2 IS
725 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
726 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
727 WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
728 AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
729 ;
730
731
732 --cursor for - pmt instr id not supplied,negotiable payments, reselect - no
733 CURSOR l_pospay_appp_csr_1_1 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
734 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
735 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_pay_instructions_all pmtinstr,iby_payment_profiles ppp
736 WHERE xml_pmt_lvl.payment_instruction_id = pmtinstr.payment_instruction_id
737 AND pmtinstr.payment_profile_id = ppp.payment_profile_id
738 AND ppp.positive_pay_format_code IN
739 (SELECT ppfformat.format_code
740 FROM iby_formats_vl ppfformat
741 WHERE ppfformat.format_name = p_format_name)
742 AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)
743 AND xml_pmt_lvl.payment_date <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
744 AND xml_pmt_lvl.payment_status IN('ISSUED', 'PAID')
745 AND(xml_pmt_lvl.positive_pay_file_created_flag = 'N' OR xml_pmt_lvl.positive_pay_file_created_flag IS NULL)
746 AND xml_pmt_lvl.internal_bank_account_id IN
747 (SELECT ba.bank_account_id
748 FROM ce_bank_accounts ba
749 WHERE ba.bank_account_name = p_internal_bank_account_name)
750 ;
751
752 --cursor for - pmt instr id not supplied,negotiable payments, reselect - yes
753 CURSOR l_pospay_appp_csr_1_2 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
754 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
755 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_pay_instructions_all pmtinstr,iby_payment_profiles ppp
756 WHERE xml_pmt_lvl.payment_instruction_id = pmtinstr.payment_instruction_id
757 AND pmtinstr.payment_profile_id = ppp.payment_profile_id
758 AND ppp.positive_pay_format_code IN
759 (SELECT ppfformat.format_code
760 FROM iby_formats_vl ppfformat
761 WHERE ppfformat.format_name = p_format_name)
762 AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)
763 AND xml_pmt_lvl.payment_date <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
764 AND xml_pmt_lvl.payment_status IN('ISSUED', 'PAID')
765 AND xml_pmt_lvl.internal_bank_account_id IN
766 (SELECT ba.bank_account_id
767 FROM ce_bank_accounts ba
768 WHERE ba.bank_account_name = p_internal_bank_account_name)
769 ;
770
771 --cursor for - pmt instr id not supplied,voided payments, reselect - no
772 CURSOR l_pospay_appp_csr_2_1 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
773 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
774 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_pay_instructions_all pmtinstr,iby_payment_profiles ppp
775 WHERE xml_pmt_lvl.payment_instruction_id = pmtinstr.payment_instruction_id
776 AND pmtinstr.payment_profile_id = ppp.payment_profile_id
777 AND ppp.positive_pay_format_code IN
778 (SELECT ppfformat.format_code
779 FROM iby_formats_vl ppfformat
780 WHERE ppfformat.format_name = p_format_name)
781 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))
782 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)
783 AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
784 AND(xml_pmt_lvl.positive_pay_file_created_flag = 'N' OR xml_pmt_lvl.positive_pay_file_created_flag IS NULL)
785 AND xml_pmt_lvl.internal_bank_account_id IN
786 (SELECT ba.bank_account_id
787 FROM ce_bank_accounts ba
788 WHERE ba.bank_account_name = p_internal_bank_account_name)
789 ;
790
791 --cursor for - pmt instr id not supplied,voided payments, reselect - yes
792 CURSOR l_pospay_appp_csr_2_2 (p_from_date IN VARCHAR2,p_to_date IN VARCHAR2) IS
793 SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
794 FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_pay_instructions_all pmtinstr,iby_payment_profiles ppp
795 WHERE xml_pmt_lvl.payment_instruction_id = pmtinstr.payment_instruction_id
796 AND pmtinstr.payment_profile_id = ppp.payment_profile_id
797 AND ppp.positive_pay_format_code IN
798 (SELECT ppfformat.format_code
799 FROM iby_formats_vl ppfformat
800 WHERE ppfformat.format_name = p_format_name)
801 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))
802 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)
803 AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
804 AND xml_pmt_lvl.internal_bank_account_id IN
805 (SELECT ba.bank_account_id
806 FROM ce_bank_accounts ba
807 WHERE ba.bank_account_name = p_internal_bank_account_name)
808 ;
809
810 BEGIN
811
812 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
813 -- parameter disp
814 iby_debug_pub.add(debug_msg => 'Parameters 0 ' || ':' || p_payment_instruction_id || ':' ||
815 p_format_name || ':' || p_internal_bank_account_name || ':' || p_payment_status || ':' ||
816 p_reselect || ':' || p_from_date || ':' || p_to_date || ':' ||
817 p_sys_key, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
818
819
820 --system key validation
821 iby_debug_pub.add(debug_msg => 'Enter ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
822
823
824 --initializing
825 iby_debug_pub.add(debug_msg => 'Before XML query ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
826 END IF;
827
828
829
830 Validate_and_Set_Syskey(p_sys_key);
831 CEP_STANDARD.init_security;
832
833
834 --parameter editing
835 l_from_date := p_from_date;
836 l_to_date := p_to_date;
837
838 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
839 iby_debug_pub.add(debug_msg => 'l_from_date 0 : ' || l_from_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
840 iby_debug_pub.add(debug_msg => 'l_to_date 0 : ' || l_to_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
841 END IF;
842
843 /* From date does not need any manipulations, and should not be defaulted to sysdate
844 IF ( trim(l_from_date) = '' or l_from_date = null or l_from_date = 'null') THEN
845 l_from_date := to_char(sysdate,'YYYY/MM/DD')||' 00:00:00';
846 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
847 iby_debug_pub.add(debug_msg => 'l_from_date 1 : ' || l_from_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
848 END IF;
849 END IF;*/
850 IF ( trim(l_to_date) = '' or l_to_date is null or l_to_date = 'null') THEN
851 l_to_date := to_char(sysdate,'YYYY/MM/DD')||' 00:00:00';
852 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
853 iby_debug_pub.add(debug_msg => 'l_to_date 1 : ' || l_to_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
854 END IF;
855 END IF;
856 /* From date does not need any manipulations, and should not be defaulted to sysdate
857 IF instr(l_from_date, '00:00:00') <> 0 THEN
858 l_from_date := REPLACE(l_from_date, '00:00:00', '00:00:01');
859 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
860 iby_debug_pub.add(debug_msg => 'l_from_date 2 : ' || l_from_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
861 END IF;
862 END IF;*/
863
864 IF instr(l_to_date, '00:00:00') <> 0 THEN
865 l_to_date := REPLACE(l_to_date, '00:00:00', '23:59:59');
866 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
867 iby_debug_pub.add(debug_msg => 'l_to_date 2 : ' || l_to_date, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
868 END IF;
869 END IF;
870
871 -- parameter disp
872 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
873 iby_debug_pub.add(debug_msg => 'Parameters 1 ' || ':' || p_payment_instruction_id || ':' ||
874 p_format_name || ':' || p_internal_bank_account_name || ':' || p_payment_status || ':' ||
875 p_reselect || ':' || l_from_date || ':' || l_to_date || ':' ||
876 p_sys_key, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
877 END IF;
878
879 --parameter checks
880 IF (p_payment_instruction_id = -99 ) THEN
881 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
882 iby_debug_pub.add(debug_msg => 'Payment Instruction ID not supplied ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
883 END IF;
884 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
885 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
886 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);
887 END IF;
888 RAISE FND_API.G_EXC_ERROR;
889 END IF;
890 END IF;
891
892 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'), sysdate) ) THEN
893 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
894 iby_debug_pub.add(debug_msg => 'From Payment Date is greater than To Payment Date', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
895 END IF;
896 RAISE FND_API.G_EXC_ERROR;
897 END IF;
898
899 IF (trim(p_payment_status) = '' or p_payment_status is null or p_payment_status = 'null') THEN
900 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
901 iby_debug_pub.add(debug_msg => 'Cannot proceed since Payment Status attribute is not supplied', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
902 END IF;
903 RAISE FND_API.G_EXC_ERROR;
904 END IF;
905
906 IF (trim(p_reselect) = '' or p_reselect is null or p_reselect = 'null') THEN
907 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
908 iby_debug_pub.add(debug_msg => 'Cannot proceed since Reselect attribute is not supplied', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
909 END IF;
910 RAISE FND_API.G_EXC_ERROR;
911 END IF;
912
913 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
914 -- edited parameter disp
915 iby_debug_pub.add(debug_msg => 'Parameters 2 ' || ':' || p_payment_instruction_id || ':' ||
916 p_format_name || ':' || p_internal_bank_account_name || ':' || p_payment_status || ':' ||
917 p_reselect || ':' || l_from_date || ':' || l_to_date || ':' ||
918 p_sys_key, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
919 END IF;
920
921 --pmt instr supplied
922 IF p_payment_instruction_id <> -99 THEN
923
924 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
925 iby_debug_pub.add(debug_msg => 'Payment Instruction ID supplied ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
926 END IF;
927 IF upper(p_payment_status) = 'NEGOTIABLE' THEN
928 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
929 iby_debug_pub.add(debug_msg => 'Payment Status is negotiable ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
930 END IF;
931 IF upper(p_reselect) = 'NO' THEN
932 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
933 iby_debug_pub.add(debug_msg => 'Reselect Option No ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
934 END IF;
935 OPEN l_pospay_ins_csr_1_1;
936 FETCH l_pospay_ins_csr_1_1 INTO x_extract_doc;
937 CLOSE l_pospay_ins_csr_1_1;
938
939 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
940 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
941 END IF;
942 UPDATE iby_payments_all
943 SET positive_pay_file_created_flag = 'Y'
944 WHERE payment_instruction_id = p_payment_instruction_id
945 AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
946 AND payment_status IN ('ISSUED','PAID');
947 ELSE
948 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
949 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
950 END IF;
951 OPEN l_pospay_ins_csr_1_2;
952 FETCH l_pospay_ins_csr_1_2 INTO x_extract_doc;
953 CLOSE l_pospay_ins_csr_1_2;
954
955 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
956 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
957 END IF;
958 UPDATE iby_payments_all
959 SET positive_pay_file_created_flag = 'Y'
960 WHERE payment_instruction_id = p_payment_instruction_id
961 AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
962 AND payment_status IN ('ISSUED','PAID');
963
964 END IF;
965 ELSE
966 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
967 iby_debug_pub.add(debug_msg => 'Payment Status is voided ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
968 END IF;
969 IF upper(p_reselect) = 'NO' THEN
970 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
971 iby_debug_pub.add(debug_msg => 'Reselect Option No ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
972 END IF;
973 OPEN l_pospay_ins_csr_2_1;
974 FETCH l_pospay_ins_csr_2_1 INTO x_extract_doc;
975 CLOSE l_pospay_ins_csr_2_1;
976
977 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
978 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
979 END IF;
980 UPDATE iby_payments_all
981 SET positive_pay_file_created_flag = 'Y'
982 WHERE payment_instruction_id = p_payment_instruction_id
983 AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
984 AND payment_status IN ('VOID','ISSUED','PAID');
985
986 ELSE
987 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
988 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
989 END IF;
990 OPEN l_pospay_ins_csr_2_2;
991 FETCH l_pospay_ins_csr_2_2 INTO x_extract_doc;
992 CLOSE l_pospay_ins_csr_2_2;
993
994 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
995 iby_debug_pub.add(debug_msg => 'payment level attribute setting ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
996 END IF;
997 UPDATE iby_payments_all
998 SET positive_pay_file_created_flag = 'Y'
999 WHERE payment_instruction_id = p_payment_instruction_id
1000 AND payment_status IN ('VOID','ISSUED','PAID');
1001
1002 END IF;
1003 END IF;
1004 --pmt instr not supplied
1005 ELSE
1006 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1007 iby_debug_pub.add(debug_msg => 'Payment Instruction ID NOT supplied ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1008 END IF;
1009 IF upper(p_payment_status) = 'NEGOTIABLE' THEN
1010 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1011 iby_debug_pub.add(debug_msg => 'Payment Status is negotiable ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1012 END IF;
1013 IF upper(p_reselect) = 'NO' THEN
1014 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1015 iby_debug_pub.add(debug_msg => 'Reselect Option No ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1016 END IF;
1017 OPEN l_pospay_appp_csr_1_1(l_from_date,l_to_date);
1018 FETCH l_pospay_appp_csr_1_1 INTO x_extract_doc;
1019 CLOSE l_pospay_appp_csr_1_1;
1020
1021 SELECT pmt.payment_id,pmt.payment_instruction_id
1022 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1023 FROM iby_xml_fd_pmt_1_0_v pmt,
1024 iby_pay_instructions_all pmtinstr,
1025 iby_payment_profiles ppp,
1026 iby_formats_vl ppfformat,
1027 ce_bank_accounts ba
1028 WHERE pmt.payment_instruction_id = pmtinstr.payment_instruction_id
1029 AND pmtinstr.payment_profile_id = ppp.payment_profile_id
1030 AND ppp.positive_pay_format_code = ppfformat.format_code
1031 AND pmt.internal_bank_account_id = ba.bank_account_id
1032 AND ppfformat.format_name = p_format_name
1033 AND ba.bank_account_name = p_internal_bank_account_name
1034 AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL)
1035 AND pmt.payment_date >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
1036 AND pmt.payment_date <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1037 AND pmt.payment_status IN('ISSUED','PAID');
1038
1039 ELSE
1040 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1041 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1042 END IF;
1043 OPEN l_pospay_appp_csr_1_2(l_from_date,l_to_date);
1044 FETCH l_pospay_appp_csr_1_2 INTO x_extract_doc;
1045 CLOSE l_pospay_appp_csr_1_2;
1046
1047 SELECT pmt.payment_id,pmt.payment_instruction_id
1048 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1049 FROM iby_xml_fd_pmt_1_0_v pmt,
1050 iby_pay_instructions_all pmtinstr,
1051 iby_payment_profiles ppp,
1052 iby_formats_vl ppfformat,
1053 ce_bank_accounts ba
1054 WHERE pmt.payment_instruction_id = pmtinstr.payment_instruction_id
1055 AND pmtinstr.payment_profile_id = ppp.payment_profile_id
1056 AND ppp.positive_pay_format_code = ppfformat.format_code
1057 AND pmt.internal_bank_account_id = ba.bank_account_id
1058 AND ppfformat.format_name = p_format_name
1059 AND ba.bank_account_name = p_internal_bank_account_name
1060 AND pmt.payment_date >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
1061 AND pmt.payment_date <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
1062 AND pmt.payment_status IN('ISSUED','PAID');
1063
1064 END IF;
1065 ELSE
1066 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1067 iby_debug_pub.add(debug_msg => 'Payment Status is voided ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1068 END IF;
1069 IF upper(p_reselect) = 'NO' THEN
1070 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1071 iby_debug_pub.add(debug_msg => 'Reselect Option No ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1072 END IF;
1073 OPEN l_pospay_appp_csr_2_1(l_from_date,l_to_date);
1074 FETCH l_pospay_appp_csr_2_1 INTO x_extract_doc;
1075 CLOSE l_pospay_appp_csr_2_1;
1076
1077 SELECT pmt.payment_id,pmt.payment_instruction_id
1078 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1079 FROM iby_xml_fd_pmt_1_0_v pmt,
1080 iby_pay_instructions_all pmtinstr,
1081 iby_payment_profiles ppp,
1082 iby_formats_vl ppfformat,
1083 ce_bank_accounts ba
1084 WHERE pmt.payment_instruction_id = pmtinstr.payment_instruction_id
1085 AND pmtinstr.payment_profile_id = ppp.payment_profile_id
1086 AND ppp.positive_pay_format_code = ppfformat.format_code
1087 AND pmt.internal_bank_account_id = ba.bank_account_id
1088 AND ppfformat.format_name = p_format_name
1089 AND ba.bank_account_name = p_internal_bank_account_name
1090 AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL)
1091 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))
1092 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)
1093 AND pmt.payment_status IN('VOID','ISSUED','PAID');
1094
1095 ELSE
1096 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1097 iby_debug_pub.add(debug_msg => 'Reselect Option Yes ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1098 END IF;
1099 OPEN l_pospay_appp_csr_2_2(l_from_date,l_to_date);
1100 FETCH l_pospay_appp_csr_2_2 INTO x_extract_doc;
1101 CLOSE l_pospay_appp_csr_2_2;
1102
1103 SELECT pmt.payment_id,pmt.payment_instruction_id
1104 BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
1105 FROM iby_xml_fd_pmt_1_0_v pmt,
1106 iby_pay_instructions_all pmtinstr,
1107 iby_payment_profiles ppp,
1108 iby_formats_vl ppfformat,
1109 ce_bank_accounts ba
1110 WHERE pmt.payment_instruction_id = pmtinstr.payment_instruction_id
1111 AND pmtinstr.payment_profile_id = ppp.payment_profile_id
1112 AND ppp.positive_pay_format_code = ppfformat.format_code
1113 AND pmt.internal_bank_account_id = ba.bank_account_id
1114 AND ppfformat.format_name = p_format_name
1115 AND ba.bank_account_name = p_internal_bank_account_name
1116 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))
1117 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)
1118 AND pmt.payment_status IN('VOID','ISSUED','PAID');
1119
1120 END IF;
1121 END IF;
1122
1123 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1124 iby_debug_pub.add(debug_msg => 'payment level attribute setting ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1125 END IF;
1126
1127
1128 iby_debug_pub.add(debug_msg => 'Payment Count : '|| l_paymentid_arr.COUNT,debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1129 IF ( l_paymentid_arr.COUNT <> 0) THEN
1130 FOR i IN l_paymentid_arr.FIRST .. l_paymentid_arr.LAST LOOP
1131 UPDATE iby_payments_all
1132 SET positive_pay_file_created_flag = 'Y'
1133 WHERE payment_id = l_paymentid_arr(i);
1134 iby_debug_pub.add(debug_msg => 'Payment ID : '|| l_paymentid_arr(i),debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1135 END LOOP;
1136 ELSE
1137 iby_debug_pub.add(debug_msg => 'Payment ID : '|| 'Empty',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1138 END IF;
1139
1140 END IF;
1141
1142
1143
1144 --payment instruction level attribute setting
1145
1146 IF p_payment_instruction_id <> -99 THEN
1147
1148 --if payment instruction is supplied
1149 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1150 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);
1151 END IF;
1152 UPDATE iby_pay_instructions_all
1153 SET positive_pay_file_created_flag='Y'
1154 WHERE payment_instruction_id = p_payment_instruction_id;
1155
1156 ELSE
1157 --if payment instruction is NOT supplied
1158 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1159 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);
1160 END IF;
1161
1162
1163 iby_debug_pub.add(debug_msg => 'Payment Instr Count : '|| l_paymentinstrid_arr.COUNT,debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1164 IF ( l_paymentinstrid_arr.COUNT <> 0) THEN
1165 FOR i IN l_paymentinstrid_arr.FIRST .. l_paymentinstrid_arr.LAST LOOP
1166 IF (l_paymentinstrid_arr(i) IS NOT NULL ) THEN
1167 UPDATE iby_pay_instructions_all ins
1168 SET ins.positive_pay_file_created_flag = 'Y'
1169 WHERE not exists (SELECT 'N'
1170 FROM iby_payments_all pmt
1171 WHERE nvl(pmt.positive_pay_file_created_flag,'N') = 'N'
1172 AND pmt.payment_status IN('ISSUED', 'PAID')
1173 AND pmt.payment_instruction_id = l_paymentinstrid_arr(i))
1174 AND ins.payment_instruction_id = l_paymentinstrid_arr(i);
1175 iby_debug_pub.add(debug_msg => 'Payment Instr ID : '|| l_paymentinstrid_arr(i),debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1176 END IF;
1177 END LOOP;
1178 ELSE
1179 iby_debug_pub.add(debug_msg => 'Payment Instr ID : '|| 'Empty',debug_level => G_LEVEL_STATEMENT,module => l_Debug_Module);
1180 END IF;
1181
1182 END IF;
1183
1184 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1185 --Done
1186 iby_debug_pub.add(debug_msg => 'After XML query ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1187 iby_debug_pub.add(debug_msg => 'Exit ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
1188 END IF;
1189
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192 RAISE;
1193
1194 END Create_Pos_Pay_Extract_2_0;
1195 -- LKQ POS PAY ISSUE - PAVAN
1196
1197
1198
1199 FUNCTION Get_FP_TaxRegistration(p_legal_entity_id IN NUMBER)
1200 RETURN VARCHAR2
1201 IS
1202 l_tax_registration VARCHAR2(2000);
1203 l_return_status VARCHAR2(1);
1204 l_msg_count NUMBER;
1205 l_msg_data VARCHAR2(2000);
1206
1207 BEGIN
1208
1209 XLE_UTILITIES_GRP.Get_FP_VATRegistration_LEID
1210 (
1211 p_api_version => 1.0,
1212 p_init_msg_list => fnd_api.g_false,
1213 p_commit => fnd_api.g_false,
1214 p_effective_date => SYSDATE,
1215 x_return_status => l_return_status,
1216 x_msg_count => l_msg_count,
1217 x_msg_data => l_msg_data,
1218 p_legal_entity_id => p_legal_entity_id,
1219 x_registration_number => l_tax_registration
1220 );
1221
1222 RETURN l_tax_registration;
1223
1224 EXCEPTION
1225 -- swallow exceptions
1226 WHEN OTHERS THEN
1227 RETURN NULL;
1228
1229 END Get_FP_TaxRegistration;
1230
1231
1232
1233 FUNCTION Get_Payee_LegalRegistration(p_vendor_id IN NUMBER,
1234 p_vendor_site_id IN NUMBER,
1235 p_vendor_site_country IN VARCHAR2)
1236 RETURN VARCHAR2
1237 IS
1238
1239 l_legal_information_rec XLE_THIRDPARTY.LegalInformation_Rec;
1240 l_return_status VARCHAR2(1);
1241 l_msg_count NUMBER;
1242 l_msg_data VARCHAR2(2000);
1243
1244
1245 BEGIN
1246
1247 -- old code. XLE descoped third party APIs.
1248 -- current XLE implementation (xlethpab.pls 116.9) only supports
1249 -- supplier and is only for IT, ES and GR
1250 -- the implementation is based on 11i PO schema, rather than TCA
1251 -- basically the API selects PO_VENDORS.NUM_1099 as the
1252 -- registration number
1253 XLE_THIRDPARTY.Get_LegalInformation
1254 (
1255 p_api_version => 1.0,
1256 p_init_msg_list => fnd_api.g_false,
1257 p_commit => fnd_api.g_false,
1258 x_return_status => l_return_status,
1259 x_msg_count => l_msg_count,
1260 x_msg_data => l_msg_data,
1261 p_business_entity_type => 'SUPPLIER',
1262 p_business_entity_id => p_vendor_id,
1263 p_business_entity_site_id => p_vendor_site_id,
1264 p_country => p_vendor_site_country,
1265 p_legal_function => 'STIC',
1266 p_legislative_category => null,
1267 x_legal_information_rec => l_legal_information_rec
1268 );
1269 /*
1270 * frzhang 4/6/05. copied from XLE code
1271 *
1272
1273 -- ***** Business entity type is SUPPLIER *****
1274 -- For Italy
1275 CURSOR case1_legal_information_cur IS
1276 SELECT pvs.vendor_site_code,
1277 pv.num_1099,
1278 pv.global_attribute2,
1279 pv.global_attribute3,
1280 pv.standard_industry_class,
1281 pvs.address_line1,
1282 pvs.address_line2,
1283 pvs.address_line3,
1284 pvs.city,
1285 pvs.zip,
1286 pvs.province,
1287 pvs.country,
1288 pvs.state
1289 FROM PO_VENDOR_SITES_ALL pvs,
1290 PO_VENDORS pv
1291 WHERE pv.vendor_id=p_business_entity_id
1292 AND pvs.vendor_site_id=p_business_entity_site_id
1293 AND pvs.country=p_country
1294 AND pv.vendor_id=pvs.vendor_id;
1295
1296 -- For Spain, Greece
1297 CURSOR case2_legal_information_cur IS
1298 SELECT decode(pvs.country,'ES',pv.vendor_name,'GR',pvs.vendor_site_code),
1299 pv.num_1099,
1300 pv.global_attribute2,
1301 pv.global_attribute3,
1302 pv.standard_industry_class,
1303 pvs.address_line1,
1304 pvs.address_line2,
1305 pvs.address_line3,
1306 pvs.city,
1307 pvs.zip,
1308 pvs.province,
1309 pvs.country,
1310 pvs.state
1311 FROM PO_VENDOR_SITES_ALL pvs,
1312 PO_VENDORS pv
1313 WHERE pv.vendor_id=p_business_entity_id
1314 AND pvs.tax_reporting_site_flag='Y'
1315 AND pvs.country=p_country
1316 AND pv.vendor_id=pvs.vendor_id;
1317
1318
1319
1320 BEGIN
1321
1322 x_msg_count := NULL;
1323 x_msg_data := NULL;
1324
1325 -- Standard Start of API savepoint
1326 SAVEPOINT Get_LegalInformation;
1327
1328 -- Standard call to check for call compatibility.
1329 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1330 p_api_version,
1331 l_api_name,
1332 G_PKG_NAME ) THEN
1333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334 END IF;
1335
1336 -- Initialize message list if p_init_msg_list is set to TRUE.
1337 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1338 FND_MSG_PUB.initialize;
1339 END IF;
1340
1341 -- Initialize API return status to success
1342 x_return_status := FND_API.G_RET_STS_SUCCESS;
1343
1344 -- ======== START OF API BODY ============
1345
1346 -- ***** Business entity type is SUPPLIER *****
1347 IF p_business_entity_type='SUPPLIER' THEN
1348
1349 -- Legal Information for Italy
1350 IF p_country='IT' THEN
1351
1352 OPEN case1_legal_information_cur;
1353 FETCH case1_legal_information_cur INTO
1354 x_legal_information_rec.legal_name,
1355 x_legal_information_rec.registration_number,
1356 x_legal_information_rec.date_of_birth,
1357 x_legal_information_rec.place_of_birth,
1358 x_legal_information_rec.company_activity_code,
1359 x_legal_information_rec.address_line1,
1360 x_legal_information_rec.address_line2,
1361 x_legal_information_rec.address_line3,
1362 x_legal_information_rec.city,
1363 x_legal_information_rec.zip,
1364 x_legal_information_rec.province,
1365 x_legal_information_rec.country,
1366 x_legal_information_rec.state;
1367
1368 IF case1_legal_information_cur%NOTFOUND THEN
1369 --specific xle message under creation fnd message used as workaround
1370 FND_MESSAGE.SET_NAME('FND','FND_GRANTS_RECORD_NOT_FOUND');
1371 FND_MSG_PUB.Add;
1372 x_return_status := FND_API.G_RET_STS_ERROR;
1373 END IF;
1374
1375
1376 CLOSE case1_legal_information_cur;
1377
1378
1379 -- Legal Information for Spain and Greece
1380 ELSIF p_country in ('ES','GR') THEN
1381
1382 OPEN case2_legal_information_cur;
1383 FETCH case2_legal_information_cur INTO
1384 x_legal_information_rec.legal_name,
1385 x_legal_information_rec.registration_number,
1386 x_legal_information_rec.date_of_birth,
1387 x_legal_information_rec.place_of_birth,
1388 x_legal_information_rec.company_activity_code,
1389 x_legal_information_rec.address_line1,
1390 x_legal_information_rec.address_line2,
1391 x_legal_information_rec.address_line3,
1392 x_legal_information_rec.city,
1393 x_legal_information_rec.zip,
1394 x_legal_information_rec.province,
1395 x_legal_information_rec.country,
1396 x_legal_information_rec.state;
1397
1398 IF case2_legal_information_cur%NOTFOUND THEN
1399 --specific xle message under creation fnd message used as workaround
1400 FND_MESSAGE.SET_NAME('FND','FND_GRANTS_RECORD_NOT_FOUND');
1401 FND_MSG_PUB.Add;
1402 x_return_status := FND_API.G_RET_STS_ERROR;
1403 END IF;
1404
1405
1406 CLOSE case2_legal_information_cur;
1407
1408 END IF;
1409
1410 END IF;
1411
1412
1413 -- End of API body.
1414 */
1415
1416
1417 RETURN l_legal_information_rec.registration_number;
1418
1419 EXCEPTION
1420 -- swallow exceptions
1421 WHEN OTHERS THEN
1422 RETURN NULL;
1423
1424 END Get_Payee_LegalRegistration;
1425
1426
1427 /**
1428 * This function calls an XLE wrapper API which in turn calls an eTax API
1429 * to get the VAT registration number.
1430 *
1431 * The parameter of this function follows the underlying XLE/ZX APIs.
1432 * See ZX_TCM_EXT_SERVICES_PUB.get_default_tax_reg()
1433 *
1434 * frzhang 4/6/05:
1435 * XLE scoped out third party LE APIs for R12. As for the Vat reg number
1436 * XLE was just providing a wrapper around ZX APIs, we will call the
1437 * ZX API directly.
1438 *
1439 *
1440 */
1441 FUNCTION Get_Payee_TaxRegistration(p_party_id IN NUMBER,
1442 p_supplier_site_id IN NUMBER)
1443 RETURN VARCHAR2
1444 IS
1445 l_registration_number VARCHAR2(50);
1446 l_return_status VARCHAR2(1);
1447 l_msg_count NUMBER;
1448 l_msg_data VARCHAR2(2000);
1449
1450 --Bug # 7412315
1451 l_party_type VARCHAR2(20);
1452
1453 BEGIN
1454
1455 --Bug # 7412315
1456 IF NVL(p_supplier_site_id,'-1') = '-1' then
1457 l_party_type := 'THIRD_PARTY';
1458 else
1459 l_party_type := 'THIRD_PARTY_SITE';
1460 END IF;
1461
1462 -- frzhang 4/6/05
1463 -- call directly to ZX API. copied from XLE code
1464
1465
1466 /*l_registration_number := ZX_API_PUB.get_default_tax_reg
1467 (
1468 p_api_version => 1.0 ,
1469 p_init_msg_list => NULL,
1470 p_commit=> NULL,
1471 p_validation_level => NULL,
1472 x_return_status => l_return_status,
1473 x_msg_count => l_msg_count,
1474 x_msg_data => l_msg_data,
1475 p_party_id => p_party_id,
1476 p_party_type => p_party_type,
1477 p_effective_date => null );*/ --Commented as part of Bug# 7412315
1478
1479 --Bug # 7412315
1480 l_registration_number := NVL(ZX_API_PUB.get_default_tax_reg
1481 (
1482 p_api_version => 1.0 ,
1483 p_init_msg_list => NULL,
1484 p_commit=> NULL,
1485 p_validation_level => NULL,
1486 x_return_status => l_return_status,
1487 x_msg_count => l_msg_count,
1488 x_msg_data => l_msg_data,
1489 p_party_id => NVL(p_supplier_site_id,p_party_id),
1490 p_party_type => l_party_type,
1491 p_effective_date => null ),ZX_API_PUB.get_default_tax_reg
1492 (
1493 p_api_version => 1.0 ,
1494 p_init_msg_list => NULL,
1495 p_commit=> NULL,
1496 p_validation_level => NULL,
1497 x_return_status => l_return_status,
1498 x_msg_count => l_msg_count,
1499 x_msg_data => l_msg_data,
1500 p_party_id => p_party_id,
1501 p_party_type => 'THIRD_PARTY',
1502 p_effective_date => null ));
1503
1504
1505 /* -- XLE descoped third party APIs
1506 XLE_THIRDPARTY.Get_TP_VATRegistration_PTY
1507 (
1508 p_api_version => 1.0,
1509 p_init_msg_list => fnd_api.g_false,
1510 p_commit => fnd_api.g_false,
1511 p_effective_date => SYSDATE,
1512 x_return_status => l_return_status,
1513 x_msg_count => l_msg_count,
1514 x_msg_data => l_msg_data,
1515 p_party_id => p_party_id,
1516 p_party_type => p_party_type,
1517 x_registration_number => l_registration_number
1518 );
1519 */
1520
1521
1522 RETURN l_registration_number;
1523
1524 EXCEPTION
1525 -- swallow exceptions
1526 WHEN OTHERS THEN
1527 RETURN NULL;
1528
1529 END Get_Payee_TaxRegistration;
1530
1531
1532 -- the party is the party that is linked to the LE
1533 -- on the payments
1534 FUNCTION Get_PayerContact(p_party_id IN NUMBER)
1535 RETURN XMLTYPE
1536 IS
1537 l_contactinfo XMLTYPE;
1538 l_phone_cp_id NUMBER;
1539 l_fax_cp_id NUMBER;
1540
1541 l_email VARCHAR2(2000);
1542 l_url VARCHAR2(2000);
1543
1544 l_hr_loc_phone VARCHAR2(60);
1545 l_hr_loc_fax VARCHAR2(60);
1546
1547
1548 CURSOR l_email_csr (p_owner_table_id IN NUMBER) IS
1549 SELECT email_address
1550 FROM hz_contact_points
1551 WHERE owner_table_name = 'HZ_PARTIES'
1552 AND owner_table_id = p_owner_table_id
1553 AND contact_point_type = 'EMAIL'
1554 AND primary_flag = 'Y'
1555 AND status = 'A';
1556
1557 -- bug 6044338. Fax and telephone numbers in the TCA data model
1558 -- are stored under the same contact_point_type PHONE. The
1559 -- difference is the phone_line_type. Since they are stored
1560 -- under the same contact_point_type the assume that there will
1561 -- only 1 primary phone or fax does not apply.
1562 -- The extract will display only 1 phone or fax as follows:
1563 -- 1. if the primary flag is set for it
1564 -- 2. if none of them are the primary contact point, the latest entered.
1565 CURSOR l_phone_csr (p_owner_table_id IN NUMBER) IS
1566 SELECT contact_point_id
1567 FROM (SELECT t.contact_point_id,
1568 t.primary_flag,
1569 t.phone_line_type,
1570 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
1571 FROM hz_contact_points t
1572 WHERE t.owner_table_name = 'HZ_PARTIES'
1573 AND t.owner_table_id = p_owner_table_id
1574 AND t.contact_point_type = 'PHONE'
1575 AND t.phone_line_type = 'GEN'
1576 AND t.status = 'A') x
1577 WHERE x.primary_phone = 1;
1578
1579 -- bug 6044338.
1580 CURSOR l_fax_csr (p_owner_table_id IN NUMBER) IS
1581 SELECT contact_point_id
1582 FROM (SELECT t.contact_point_id,
1583 t.primary_flag,
1584 t.phone_line_type,
1585 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
1586 FROM hz_contact_points t
1587 WHERE t.owner_table_name = 'HZ_PARTIES'
1588 AND t.owner_table_id = p_owner_table_id
1589 AND t.contact_point_type = 'PHONE'
1590 AND t.phone_line_type = 'FAX'
1591 AND t.status = 'A') x
1592 WHERE x.primary_phone = 1;
1593
1594
1595 CURSOR l_web_csr (p_owner_table_id IN NUMBER) IS
1596 SELECT url
1597 FROM hz_contact_points
1598 WHERE owner_table_name = 'HZ_PARTIES'
1599 AND owner_table_id = p_owner_table_id
1600 AND contact_point_type = 'WEB'
1601 AND primary_flag = 'Y'
1602 AND status = 'A';
1603
1604 CURSOR l_hr_loc_contact_csr (p_party_id IN NUMBER) IS
1605 SELECT TELEPHONE_NUMBER_1, TELEPHONE_NUMBER_2
1606 FROM hr_locations_all hr_loc, xle_firstparty_information_v xle_firstparty
1607 WHERE hr_loc.location_id = xle_firstparty.location_id
1608 AND xle_firstparty.party_id = p_party_id;
1609
1610 BEGIN
1611 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1612 iby_debug_pub.add(debug_msg => 'ENTER get_payer_contact',
1613 debug_level => G_LEVEL_STATEMENT,
1614 module => G_Debug_Module || '.get_payer_contact');
1615 END IF;
1616 -- Bug 7253633
1617 -- Checking to make sure that party_id IS NOT NULL
1618 -- Skip procedure if p_party_id IS NOT NULL;
1619 IF(p_party_id IS NOT NULL) THEN
1620 IF (NOT(g_payer_contact_tbl.EXISTS(p_party_id))) THEN
1621 OPEN l_email_csr (p_party_id);
1622 FETCH l_email_csr INTO l_email;
1623 CLOSE l_email_csr;
1624
1625 OPEN l_phone_csr (p_party_id);
1626 FETCH l_phone_csr INTO l_phone_cp_id;
1627 CLOSE l_phone_csr;
1628
1629 OPEN l_fax_csr (p_party_id);
1630 FETCH l_fax_csr INTO l_fax_cp_id;
1631 CLOSE l_fax_csr;
1632 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1633 iby_debug_pub.add(debug_msg => 'After getting mail, phone and fax',
1634 debug_level => G_LEVEL_STATEMENT,
1635 module => G_Debug_Module || '.get_payer_contact');
1636 END IF;
1637 OPEN l_web_csr (p_party_id);
1638 FETCH l_web_csr INTO l_url;
1639 CLOSE l_web_csr;
1640
1641 OPEN l_hr_loc_contact_csr (p_party_id);
1642 FETCH l_hr_loc_contact_csr INTO l_hr_loc_phone, l_hr_loc_fax;
1643 CLOSE l_hr_loc_contact_csr;
1644 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1645 iby_debug_pub.add(debug_msg => 'Before XML query',
1646 debug_level => G_LEVEL_STATEMENT,
1647 module => G_Debug_Module || '.get_payer_contact');
1648 END IF;
1649 -- the ContactName is left null
1650 SELECT
1651 XMLElement("ContactLocators",
1652 XMLElement("PhoneNumber", nvl(hz_format_phone_v2pub.get_formatted_phone(l_phone_cp_id), l_hr_loc_phone)),
1653 XMLElement("FaxNumber", nvl(hz_format_phone_v2pub.get_formatted_phone(l_fax_cp_id), l_hr_loc_fax)),
1654 XMLElement("EmailAddress", l_email),
1655 XMLElement("Website", l_url)
1656 )
1657 INTO g_payer_contact_tbl(p_party_id).l_contactinfo
1658 FROM dual;
1659 END IF;
1660 ELSE
1661 RETURN null;
1662 END IF;
1663
1664 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1665 iby_debug_pub.add(debug_msg => 'EXIT get_payer_contact',
1666 debug_level => G_LEVEL_STATEMENT,
1667 module => G_Debug_Module || '.get_payer_contact');
1668 END IF;
1669 RETURN g_payer_contact_tbl(p_party_id).l_contactinfo;
1670
1671
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1675 iby_debug_pub.add(debug_msg => 'EXCEPTION -'||sqlerrm,
1676 debug_level => G_LEVEL_STATEMENT,
1677 module => G_Debug_Module || '.get_payer_contact');
1678 END IF;
1679
1680 Raise;
1681
1682 END Get_PayerContact;
1683
1684
1685 FUNCTION Get_PayeeContact(p_payment_id IN NUMBER)
1686 RETURN XMLTYPE
1687 IS
1688 l_contactinfo XMLTYPE;
1689 l_remit_to_loc_id NUMBER;
1690 l_party_site_id NUMBER;
1691 l_payee_party_id NUMBER;
1692 l_owner_table_name VARCHAR2(30);
1693 l_owner_table_id NUMBER;
1694 l_phone_cp_id NUMBER;
1695 l_fax_cp_id NUMBER;
1696 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayeeContact';
1697
1698 l_email VARCHAR2(2000);
1699 l_url VARCHAR2(2000);
1700
1701 CURSOR l_pmt_csr (p_payment_id IN NUMBER) IS
1702 SELECT remit_to_location_id, party_site_id, payee_party_id
1703 FROM iby_payments_all
1704 WHERE payment_id = p_payment_id;
1705
1706 CURSOR l_email_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
1707 SELECT email_address
1708 FROM hz_contact_points
1709 WHERE owner_table_name = p_owner_table_name
1710 AND owner_table_id = p_owner_table_id
1711 AND contact_point_type = 'EMAIL'
1712 AND primary_flag = 'Y'
1713 AND status = 'A';
1714
1715
1716 -- bug 6044338. Fax and telephone numbers in the TCA data model
1717 -- are stored under the same contact_point_type PHONE. The
1718 -- difference is the phone_line_type. Since they are stored
1719 -- under the same contact_point_type the assume that there will
1720 -- only 1 primary phone or fax does not apply.
1721 -- The extract will display only 1 phone or fax as follows:
1722 -- 1. if the primary flag is set for it
1723 -- 2. if none of them are the primary contact point, the latest entered.
1724 CURSOR l_phone_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
1725 SELECT contact_point_id
1726 FROM (SELECT t.contact_point_id,
1727 t.primary_flag,
1728 t.phone_line_type,
1729 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
1730 FROM hz_contact_points t
1731 WHERE t.owner_table_name = p_owner_table_name
1732 AND t.owner_table_id = p_owner_table_id
1733 AND t.contact_point_type = 'PHONE'
1734 AND t.phone_line_type = 'GEN'
1735 AND t.status = 'A') x
1736 WHERE x.primary_phone = 1;
1737
1738 -- bug 6044338.
1739 CURSOR l_fax_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
1740 SELECT contact_point_id
1741 FROM (SELECT t.contact_point_id,
1742 t.primary_flag,
1743 t.phone_line_type,
1744 RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
1745 FROM hz_contact_points t
1746 WHERE t.owner_table_name = p_owner_table_name
1747 AND t.owner_table_id = p_owner_table_id
1748 AND t.contact_point_type = 'PHONE'
1749 AND t.phone_line_type = 'FAX'
1750 AND t.status = 'A') x
1751 WHERE x.primary_phone = 1;
1752
1753 CURSOR l_web_csr (p_owner_table_name IN VARCHAR2, p_owner_table_id IN NUMBER) IS
1754 SELECT url
1755 FROM hz_contact_points
1756 WHERE owner_table_name = p_owner_table_name
1757 AND owner_table_id = p_owner_table_id
1758 AND contact_point_type = 'WEB'
1759 AND primary_flag = 'Y'
1760 AND status = 'A';
1761
1762 BEGIN
1763
1764 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1765 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
1766 debug_level => G_LEVEL_PROCEDURE,
1767 module => l_Debug_Module);
1768 END IF;
1769 OPEN l_pmt_csr (p_payment_id);
1770 FETCH l_pmt_csr INTO l_remit_to_loc_id, l_party_site_id, l_payee_party_id;
1771 CLOSE l_pmt_csr;
1772
1773 IF l_party_site_id IS NOT NULL THEN
1774 l_owner_table_name := 'HZ_PARTY_SITES';
1775 l_owner_table_id := l_party_site_id;
1776 ELSE
1777 l_owner_table_name := 'HZ_PARTIES';
1778 l_owner_table_id := l_payee_party_id;
1779 END IF;
1780
1781
1782 OPEN l_email_csr (l_owner_table_name, l_owner_table_id);
1783 FETCH l_email_csr INTO l_email;
1784 CLOSE l_email_csr;
1785
1786 OPEN l_phone_csr (l_owner_table_name, l_owner_table_id);
1787 FETCH l_phone_csr INTO l_phone_cp_id;
1788 CLOSE l_phone_csr;
1789
1790 OPEN l_fax_csr (l_owner_table_name, l_owner_table_id);
1791 FETCH l_fax_csr INTO l_fax_cp_id;
1792 CLOSE l_fax_csr;
1793
1794 OPEN l_web_csr (l_owner_table_name, l_owner_table_id);
1795 FETCH l_web_csr INTO l_url;
1796 CLOSE l_web_csr;
1797
1798 -- the ContactName is left null
1799 SELECT
1800 XMLElement("ContactLocators",
1801 XMLElement("PhoneNumber", hz_format_phone_v2pub.get_formatted_phone(l_phone_cp_id)),
1802 XMLElement("FaxNumber", hz_format_phone_v2pub.get_formatted_phone(l_fax_cp_id)),
1803 XMLElement("EmailAddress", l_email),
1804 XMLElement("Website", l_url)
1805 )
1806 INTO l_contactinfo
1807 FROM dual;
1808 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1809 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1810 debug_level => G_LEVEL_PROCEDURE,
1811 module => l_Debug_Module);
1812 END IF;
1813 RETURN l_contactinfo;
1814
1815 EXCEPTION
1816 WHEN OTHERS THEN
1817 NULL;
1818
1819 END Get_PayeeContact;
1820
1821
1822 FUNCTION format_hr_address(p_hr_location_id IN NUMBER,
1823 p_style_code IN VARCHAR2 DEFAULT NULL)
1824 RETURN VARCHAR2
1825 IS
1826
1827 l_formatted_address VARCHAR2(2000);
1828 l_address_line_1 VARCHAR2(240);
1829 l_address_line_2 VARCHAR2(240);
1830 l_address_line_3 VARCHAR2(240);
1831 l_city VARCHAR2(30);
1832 l_postal_code VARCHAR2(30);
1833 l_state VARCHAR2(120);
1834 l_county VARCHAR2(120);
1835 l_country VARCHAR2(60);
1836
1837 CURSOR l_hr_loc_csr (p_hr_location_id IN NUMBER) IS
1838 SELECT address_line_1, address_line_2, address_line_3,
1839 town_or_city, region_1, region_2,
1840 postal_code, country
1841 FROM hr_locations_all
1842 WHERE location_id = p_hr_location_id;
1843
1844 BEGIN
1845
1846 format_hr_address_C := format_hr_address_C + 1;
1847 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1848 iby_debug_pub.add(debug_msg => 'format_hr_address() entered. count: ' || format_hr_address_C,
1849 debug_level => G_LEVEL_STATEMENT,
1850 module => G_Debug_Module || '.format_hr_address');
1851 iby_debug_pub.add(debug_msg => 'p_hr_location_id: ' || p_hr_location_id,
1852 debug_level => G_LEVEL_STATEMENT,
1853 module => G_Debug_Module || '.format_hr_address');
1854 END IF;
1855
1856 IF p_hr_location_id IS NULL THEN
1857 RETURN NULL;
1858 END IF;
1859
1860 OPEN l_hr_loc_csr (p_hr_location_id);
1861 FETCH l_hr_loc_csr INTO l_address_line_1, l_address_line_2, l_address_line_3,
1862 l_city, l_county, l_state, l_postal_code, l_country;
1863 CLOSE l_hr_loc_csr;
1864
1865 l_formatted_address := hz_format_pub.format_address_lov(
1866 p_address_line_1 => l_address_line_1,
1867 p_address_line_2 => l_address_line_2,
1868 p_address_line_3 => l_address_line_3,
1869 p_address_line_4 => NULL,
1870 p_city => l_city,
1871 p_postal_code => l_postal_code,
1872 p_state => l_state,
1873 p_province => NULL,
1874 p_county => l_county,
1875 p_country => l_country,
1876 p_address_lines_phonetic => NULL
1877 );
1878
1879 RETURN l_formatted_address;
1880
1881 EXCEPTION
1882 WHEN OTHERS THEN
1883 NULL;
1884
1885 END format_hr_address;
1886
1887
1888 FUNCTION format_hz_address(p_hz_location_id IN NUMBER,
1889 p_style_code IN VARCHAR2 DEFAULT NULL)
1890 RETURN VARCHAR2
1891 IS
1892
1893 l_formatted_address VARCHAR2(2000);
1894
1895 BEGIN
1896
1897 format_hz_address_C := format_hz_address_C + 1;
1898 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1899 iby_debug_pub.add(debug_msg => 'format_hz_address() entered. count: ' || format_hz_address_C,
1900 debug_level => G_LEVEL_STATEMENT,
1901 module => G_Debug_Module || '.format_hz_address');
1902 iby_debug_pub.add(debug_msg => 'p_hz_location_id: ' || p_hz_location_id,
1903 debug_level => G_LEVEL_STATEMENT,
1904 module => G_Debug_Module || '.format_hz_address');
1905 END IF;
1906
1907 IF p_hz_location_id IS NULL THEN
1908 RETURN NULL;
1909 END IF;
1910
1911 l_formatted_address := hz_format_pub.format_address(
1912 p_location_id => p_hz_location_id,
1913 p_style_code => p_style_code
1914 );
1915
1916 RETURN l_formatted_address;
1917
1918 EXCEPTION
1919 WHEN OTHERS THEN
1920 NULL;
1921
1922 END format_hz_address;
1923
1924
1925 -- CE defines bank contact at three levels: bank, branch
1926 -- and bank account (internal ba only). Each level can have any number of contact
1927 -- persons. The contact are always based on contact person;
1928 -- in other words no contact points (email, phone, etc) linking
1929 -- directly to the bank/branch parties.
1930 -- CE uses CPUI to create the contact persons. CE always pass
1931 -- the bank party as the subject party of the relationship to
1932 -- create the org contact.
1933 -- A record in the HZ_RELATIONSHIPS is created with the contact
1934 -- person party as the subject_id, the bank party as the object_id
1935 -- and relationship_code = 'CONTACT_OF', directional_flag = 'F'.
1936 -- A record is created in the HZ_ORG_CONTACTS table
1937 -- with party_relationship_id = relationship_id
1938 -- A record is created in the HZ_ORG_CONTACT_ROLES table
1939 -- with the org_contact_id.
1940 -- There is a 'BANKING_CONTACT' role type, however CE is not
1941 -- setting any limit or default on the role type.
1942 -- The CE_CONTACT_ASSIGNMENTS table stores the contact
1943 -- assignments to the levels.
1944 --
1945 -- CE current primary contact person design is not clear.
1946 -- Also the bank/branch/account level contact filtering
1947 -- rule is not clear. Omar wanted to defer the bank contacts
1948 -- in the extract until the requirement arises.
1949 --
1950 -- FUNCTION Get_Int_BankContact(p_bank_account_id IN NUMBER)
1951 -- RETURN XMLTYPE
1952
1953
1954 FUNCTION Get_Pmt_DocPayableCount(p_payment_id IN NUMBER)
1955 RETURN NUMBER
1956 IS
1957 l_pmt_docpayablecount NUMBER;
1958 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Pmt_DocPayableCount';
1959 CURSOR l_pmt_docpayablecount_csr (p_payment_id IN NUMBER) IS
1960 SELECT count(payment_id)
1961 FROM iby_xml_fd_doc_1_0_v xml_doc_lvl
1962 WHERE xml_doc_lvl.formatting_payment_id = p_payment_id; --bug 7006504
1963
1964 CURSOR l_docpayablecount_ppr_rpt_csr (p_payment_id IN NUMBER) IS
1965 SELECT count(payment_id)
1966 FROM iby_xml_fd_doc_1_0_v xml_doc_lvl
1967 WHERE xml_doc_lvl.payment_id = p_payment_id; --bug 7459662
1968 BEGIN
1969 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1970 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
1971 debug_level => G_LEVEL_PROCEDURE,
1972 module => l_Debug_Module);
1973 END IF;
1974
1975 -- Bug 7459662 Begin
1976 IF G_Extract_Run_Mode = G_EXTRACT_MODE_PPR_RPT THEN
1977 OPEN l_docpayablecount_ppr_rpt_csr (p_payment_id);
1978 FETCH l_docpayablecount_ppr_rpt_csr INTO l_pmt_docpayablecount;
1979 CLOSE l_docpayablecount_ppr_rpt_csr;
1980
1981 ELSE
1982 OPEN l_pmt_docpayablecount_csr (p_payment_id);
1983 FETCH l_pmt_docpayablecount_csr INTO l_pmt_docpayablecount;
1984 CLOSE l_pmt_docpayablecount_csr;
1985
1986 END IF;
1987 -- Bug 7459662 End
1988 /* Commented as part of Bug 7459662
1989 OPEN l_pmt_docpayablecount_csr(p_payment_id);
1990 FETCH l_pmt_docpayablecount_csr INTO l_pmt_docpayablecount;
1991 CLOSE l_pmt_docpayablecount_csr;*/
1992 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1993 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
1994 debug_level => G_LEVEL_PROCEDURE,
1995 module => l_Debug_Module);
1996 END IF;
1997 RETURN l_pmt_docpayablecount;
1998
1999 END Get_Pmt_DocPayableCount;
2000
2001
2002 FUNCTION Get_Ins_FVFieldsAgg(p_payment_instruction_id IN NUMBER)
2003 RETURN XMLTYPE
2004 IS
2005 l_fv_summary_agg XMLTYPE;
2006 l_fv_treasury_symbol_agg XMLTYPE;
2007 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Ins_FVFieldsAgg';
2008
2009 CURSOR l_fv_treasury_symbol_csr (p_payment_instruction_id IN NUMBER) IS
2010 SELECT XMLAgg(
2011 XMLElement("TreasurySymbol",
2012 XMLElement("Name", fv.treasury_symbol),
2013 XMLElement("Amount",
2014 XMLElement("Value", fv.amount),
2015 XMLElement("Currency", XMLElement("Code", iby.payment_currency_code))
2016 )
2017 )
2018 )
2019 FROM fv_tp_ts_amt_data fv, iby_pay_instructions_all iby
2020 WHERE iby.payment_instruction_id = fv.payment_instruction_id
2021 AND iby.payment_instruction_id = p_payment_instruction_id;
2022
2023 CURSOR l_fv_summary_csr (p_payment_instruction_id IN NUMBER,
2024 p_fv_treasury_symbol_agg XMLTYPE) IS
2025 SELECT XMLElement("FederalInstructionInfo",
2026 XMLElement("TreasurySymbols", p_fv_treasury_symbol_agg),
2027 XMLElement("ControlNumber", control_number),
2028 XMLElement("ECSSummaryDosSeqNumber", iby_utility_pvt.get_view_param('FV_ECS_SEQ'))
2029 )
2030 FROM fv_summary_consolidate
2031 WHERE payment_instruction_id = p_payment_instruction_id;
2032
2033 BEGIN
2034
2035 IF G_Extract_Run_Mode = G_EXTRACT_MODE_FV_SMMY THEN
2036
2037 OPEN l_fv_treasury_symbol_csr(p_payment_instruction_id);
2038 FETCH l_fv_treasury_symbol_csr INTO l_fv_treasury_symbol_agg;
2039 CLOSE l_fv_treasury_symbol_csr;
2040
2041 OPEN l_fv_summary_csr(p_payment_instruction_id, l_fv_treasury_symbol_agg);
2042 FETCH l_fv_summary_csr INTO l_fv_summary_agg;
2043 CLOSE l_fv_summary_csr;
2044
2045 RETURN l_fv_summary_agg;
2046
2047 ELSE
2048 RETURN NULL;
2049 END IF;
2050
2051 END Get_Ins_FVFieldsAgg;
2052
2053
2054 FUNCTION Get_Ins_PayerInstrAgg(p_payment_instruction_id IN NUMBER)
2055 RETURN XMLTYPE
2056 IS
2057 l_payerinstr_agg XMLTYPE;
2058 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Ins_PayerInstrAgg';
2059 /* perf bug- 6763515 */
2060
2061
2062 -- for payment format: normal and reprint entire instruction
2063 CURSOR l_payerinstr_csr (p_payment_instruction_id IN NUMBER) IS
2064 SELECT XMLAgg(xml_pmt_lvl.payment)
2065 FROM
2066 iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
2067 IBY_PAY_INSTRUCTIONS_ALL ins
2068 WHERE
2069 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2070 AND ins.payment_instruction_id = xml_pmt_lvl.payment_instruction_id
2071 AND ((xml_pmt_lvl.payment_status in ('INSTRUCTION_CREATED',
2072 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW') AND ins.process_type = 'STANDARD') OR
2073 ins.process_type = 'IMMEDIATE');
2074
2075 -- for payment format: reprint individual and ranges
2076 CURSOR l_payerinstr_reprt_csr (p_payment_instruction_id IN NUMBER) IS
2077 SELECT XMLAgg(xml_pmt_lvl.payment)
2078 FROM
2079 iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
2080 IBY_PAY_INSTRUCTIONS_ALL ins
2081 WHERE
2082 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2083 AND ins.payment_instruction_id = xml_pmt_lvl.payment_instruction_id
2084 AND ((xml_pmt_lvl.payment_status in ('READY_TO_REPRINT',
2085 'VOID_BY_SETUP_REPRINT', 'VOID_BY_OVERFLOW_REPRINT') AND ins.process_type = 'STANDARD') OR
2086 ins.process_type = 'IMMEDIATE');
2087
2088 -- for payment instruction register
2089 -- we are extract payments in all statuses
2090 CURSOR l_payerinstr_rpt_csr (p_payment_instruction_id IN NUMBER) IS
2091 SELECT XMLAgg(xml_pmt_lvl.payment)
2092 FROM
2093 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2094 WHERE
2095 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id;
2096
2097 -- for other auxiliary formats
2098 CURSOR l_payerinstr_aux_csr (p_payment_instruction_id IN NUMBER) IS
2099 SELECT XMLAgg(xml_pmt_lvl.payment)
2100 FROM
2101 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2102 WHERE
2103 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2104 AND xml_pmt_lvl.payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
2105 'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED', 'PAID');
2106
2107 -- for separate remittance advice electronic delivery: email and fax
2108 CURSOR l_payerinstr_sra_ele_csr (p_payment_instruction_id IN NUMBER) IS
2109 SELECT XMLAgg(xml_pmt_lvl.payment)
2110 FROM
2111 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2112 WHERE
2113 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2114 AND xml_pmt_lvl.payment_id = G_Extract_Run_Payment_id;
2115 -- note the status qualification is done in Java CP main driver cursor
2116
2117 -- for separate remittance advice print delivery
2118 CURSOR l_payerinstr_sra_prt_csr (p_payment_instruction_id IN NUMBER) IS
2119 SELECT XMLAgg(xml_pmt_lvl.payment)
2120 FROM
2121 iby_xml_fd_pmt_1_0_v xml_pmt_lvl
2122 WHERE
2123 xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
2124 AND (Get_SRA_Attribute(xml_pmt_lvl.payment_id, G_SRA_REQ_FLAG_ATTR) = 'Y' OR xml_pmt_lvl.payment_status ='VOID_BY_OVERFLOW')
2125 AND Get_SRA_Attribute(xml_pmt_lvl.payment_id, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
2126 AND xml_pmt_lvl.payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
2127 'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED', 'PAID','VOID_BY_OVERFLOW');
2128
2129
2130 CURSOR l_rep_debug_pmt_csr (p_payment_instruction_id IN NUMBER) IS
2131 SELECT ext_pmt_v.payment_id, ext_pmt_v.paper_document_number, ext_pmt_v.payment_status
2132 FROM
2133 IBY_EXT_FD_PMT_1_0_V ext_pmt_v,
2134 IBY_PAY_INSTRUCTIONS_ALL ins
2135 WHERE
2136 ext_pmt_v.payment_instruction_id = p_payment_instruction_id
2137 AND ins.payment_instruction_id = ext_pmt_v.payment_instruction_id;
2138
2139 CURSOR l_rep_debug_ins_csr (p_payment_instruction_id IN NUMBER) IS
2140 SELECT ins.payment_instruction_status
2141 FROM
2142 IBY_PAY_INSTRUCTIONS_ALL ins
2143 WHERE
2144 ins.payment_instruction_id = p_payment_instruction_id;
2145
2146
2147 l_rep_ins_st VARCHAR2(30);
2148
2149 BEGIN
2150 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2151 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2152 debug_level => G_LEVEL_PROCEDURE,
2153 module => l_Debug_Module);
2154 END IF;
2155
2156 IF G_Extract_Run_Mode is null OR G_Extract_Run_Mode = G_EXTRACT_MODE_PMT THEN
2157
2158
2159 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2160 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_PMT. ',
2161 debug_level => G_LEVEL_STATEMENT,
2162 module => l_Debug_Module);
2163
2164 iby_debug_pub.add(debug_msg => 'For reprint debugging: ',
2165 debug_level => G_LEVEL_STATEMENT,
2166 module => l_Debug_Module);
2167 END IF;
2168
2169 /* PERF BUG- 6763515 */
2170 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2171 OPEN l_rep_debug_ins_csr (p_payment_instruction_id);
2172 FETCH l_rep_debug_ins_csr INTO l_rep_ins_st;
2173 CLOSE l_rep_debug_ins_csr;
2174
2175 iby_debug_pub.add(debug_msg => 'instruction id: ' || p_payment_instruction_id
2176 || ', instruction status: ' || l_rep_ins_st,
2177 debug_level => G_LEVEL_STATEMENT,
2178 module => l_Debug_Module);
2179
2180 iby_debug_pub.add(debug_msg => 'payment id, paper document number, payment status for all payments in the instruction: ',
2181 debug_level => G_LEVEL_STATEMENT,
2182 module => l_Debug_Module);
2183 END IF;
2184 /* PERF BUG- 6763515 */
2185 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2186 FOR l_payment IN l_rep_debug_pmt_csr(p_payment_instruction_id) LOOP
2187
2188 iby_debug_pub.add(debug_msg => 'payment_id: ' || l_payment.payment_id
2189 || ', paper_document_number: ' || l_payment.paper_document_number
2190 || ', payment_status: ' || l_payment.payment_status,
2191 debug_level => G_LEVEL_STATEMENT,
2192 module => l_Debug_Module);
2193
2194 END LOOP;
2195 END IF;
2196
2197
2198
2199 IF nvl(G_Is_Reprint, 'N') = 'N' THEN
2200 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2201 iby_debug_pub.add(debug_msg => 'Before executing the cursor l_payerinstr_csr ',
2202 debug_level => G_LEVEL_STATEMENT,
2203 module => l_Debug_Module);
2204 END IF;
2205 OPEN l_payerinstr_csr (p_payment_instruction_id);
2206 FETCH l_payerinstr_csr INTO l_payerinstr_agg;
2207 CLOSE l_payerinstr_csr;
2208 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2209 iby_debug_pub.add(debug_msg => 'After executing the cursor l_payerinstr_csr ',
2210 debug_level => G_LEVEL_STATEMENT,
2211 module => l_Debug_Module);
2212 END IF;
2213
2214 ELSE
2215 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2216 iby_debug_pub.add(debug_msg => 'Before executing the cursor l_payerinstr_reprt_csr ',
2217 debug_level => G_LEVEL_STATEMENT,
2218 module => l_Debug_Module);
2219 END IF;
2220 OPEN l_payerinstr_reprt_csr (p_payment_instruction_id);
2221 FETCH l_payerinstr_reprt_csr INTO l_payerinstr_agg;
2222 CLOSE l_payerinstr_reprt_csr;
2223 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2224 iby_debug_pub.add(debug_msg => 'After executing the cursor l_payerinstr_reprt_csr ',
2225 debug_level => G_LEVEL_STATEMENT,
2226 module => l_Debug_Module);
2227 END IF;
2228
2229 END IF;
2230
2231 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_AUX THEN
2232 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2233 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_AUX. ',
2234 debug_level => G_LEVEL_STATEMENT,
2235 module => l_Debug_Module);
2236 END IF;
2237 OPEN l_payerinstr_aux_csr (p_payment_instruction_id);
2238 FETCH l_payerinstr_aux_csr INTO l_payerinstr_agg;
2239 CLOSE l_payerinstr_aux_csr;
2240
2241 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_PI_RPT THEN
2242 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2243 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_PI_RPT. ',
2244 debug_level => G_LEVEL_STATEMENT,
2245 module => l_Debug_Module);
2246 END IF;
2247 OPEN l_payerinstr_rpt_csr (p_payment_instruction_id);
2248 FETCH l_payerinstr_rpt_csr INTO l_payerinstr_agg;
2249 CLOSE l_payerinstr_rpt_csr;
2250
2251 ELSIF G_Extract_Run_Mode = G_EXTRACT_MODE_SRA THEN
2252
2253 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2254 iby_debug_pub.add(debug_msg => 'Extract mode is G_EXTRACT_MODE_SRA. ',
2255 debug_level => G_LEVEL_STATEMENT,
2256 module => l_Debug_Module);
2257 END IF;
2258
2259 IF G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_PRINTED THEN
2260
2261 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2262 iby_debug_pub.add(debug_msg => 'SRA Delivery method is printed. ',
2263 debug_level => G_LEVEL_STATEMENT,
2264 module => l_Debug_Module);
2265 END IF;
2266
2267 OPEN l_payerinstr_sra_prt_csr (p_payment_instruction_id);
2268 FETCH l_payerinstr_sra_prt_csr INTO l_payerinstr_agg;
2269 CLOSE l_payerinstr_sra_prt_csr;
2270
2271 ELSIF G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_EMAIL OR
2272 G_Extract_Run_Delivery_Method = G_SRA_DELIVERY_METHOD_FAX THEN
2273
2274 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2275 iby_debug_pub.add(debug_msg => 'SRA Delivery method is Email/Fax. ',
2276 debug_level => G_LEVEL_STATEMENT,
2277 module => l_Debug_Module);
2278 END IF;
2279
2280 OPEN l_payerinstr_sra_ele_csr (p_payment_instruction_id);
2281 FETCH l_payerinstr_sra_ele_csr INTO l_payerinstr_agg;
2282 CLOSE l_payerinstr_sra_ele_csr;
2283
2284 END IF;
2285
2286 END IF;
2287
2288 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2289 IF l_payerinstr_agg is null THEN
2290 iby_debug_pub.add(debug_msg => 'After fetch from payer instrument cursor. l_payerinstr_agg is null',
2291 debug_level => G_LEVEL_STATEMENT,
2292 module => l_Debug_Module);
2293
2294 ELSE
2295 iby_debug_pub.add(debug_msg => 'After fetch from payer instrument cursor. l_payerinstr_agg is not null',
2296 debug_level => G_LEVEL_STATEMENT,
2297 module => l_Debug_Module);
2298
2299 END IF;
2300
2301 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2302 debug_level => G_LEVEL_PROCEDURE,
2303 module => l_Debug_Module);
2304 END IF;
2305
2306 RETURN l_payerinstr_agg;
2307 EXCEPTION
2308 WHEN OTHERS THEN
2309 iby_debug_pub.add(debug_msg => 'EXECPTION OCCURED IN : ' || l_Debug_Module || sqlerrm ,
2310 debug_level => G_LEVEL_PROCEDURE,
2311 module => l_Debug_Module);
2312 RAISE;
2313 END Get_Ins_PayerInstrAgg;
2314
2315 FUNCTION Get_Payer(p_legal_entity_id IN NUMBER)
2316 RETURN XMLTYPE
2317 IS
2318 l_payer XMLTYPE;
2319 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Payer';
2320 CURSOR l_payer_csr (p_legal_entity_id IN NUMBER) IS
2321 SELECT payer
2322 FROM iby_xml_fd_payer_1_0_v
2323 WHERE legal_entity_id = p_legal_entity_id;
2324
2325 BEGIN
2326 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2327 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2328 debug_level => G_LEVEL_PROCEDURE,
2329 module => l_Debug_Module);
2330 END IF;
2331 Get_Payer_C := Get_Payer_C + 1;
2332 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2333 iby_debug_pub.add(debug_msg => 'Get_Payer() entered. count: ' || Get_Payer_C,
2334 debug_level => G_LEVEL_STATEMENT,
2335 module => G_Debug_Module || '.Get_Payer');
2336 iby_debug_pub.add(debug_msg => 'p_legal_entity_id: ' || p_legal_entity_id,
2337 debug_level => G_LEVEL_STATEMENT,
2338 module => G_Debug_Module || '.Get_Payer');
2339 END IF;
2340
2341 IF p_legal_entity_id IS NULL THEN
2342 RETURN NULL;
2343 END IF;
2344
2345 OPEN l_payer_csr (p_legal_entity_id);
2346 FETCH l_payer_csr INTO l_payer;
2347 CLOSE l_payer_csr;
2348
2349 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2350 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2351 debug_level => G_LEVEL_PROCEDURE,
2352 module => l_Debug_Module);
2353 END IF;
2354
2355 RETURN l_payer;
2356
2357 END Get_Payer;
2358
2359
2360 FUNCTION Get_PayerBankAccount(p_bank_account_id IN NUMBER)
2361 RETURN XMLTYPE
2362 IS
2363 l_payer_ba XMLTYPE;
2364 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayerBankAccount';
2365 CURSOR l_payer_ba_csr (p_bank_account_id IN NUMBER) IS
2366 SELECT int_bank_account
2367 FROM iby_xml_fd_prba_1_0_v
2368 WHERE bank_account_id = p_bank_account_id;
2369
2370 BEGIN
2371 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2372 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2373 debug_level => G_LEVEL_PROCEDURE,
2374 module => l_Debug_Module);
2375 END IF;
2376 Get_PayerBankAccount_C := Get_PayerBankAccount_C + 1;
2377 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2378 iby_debug_pub.add(debug_msg => 'Get_PayerBankAccount() entered. count: ' || Get_PayerBankAccount_C,
2379 debug_level => G_LEVEL_STATEMENT,
2380 module => G_Debug_Module || '.Get_PayerBankAccount');
2381 iby_debug_pub.add(debug_msg => 'p_bank_account_id: ' || p_bank_account_id,
2382 debug_level => G_LEVEL_STATEMENT,
2383 module => G_Debug_Module || '.Get_PayerBankAccount');
2384 END IF;
2385 IF p_bank_account_id IS NULL THEN
2386 RETURN NULL;
2387 END IF;
2388
2389 OPEN l_payer_ba_csr (p_bank_account_id);
2390 FETCH l_payer_ba_csr INTO l_payer_ba;
2391 CLOSE l_payer_ba_csr;
2392
2393
2394 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2395 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2396 debug_level => G_LEVEL_PROCEDURE,
2397 module => l_Debug_Module);
2398 END IF;
2399 RETURN l_payer_ba;
2400
2401 END Get_PayerBankAccount;
2402
2403
2404 FUNCTION Get_Payer_Denorm(p_payment_id IN NUMBER)
2405 RETURN XMLTYPE
2406 IS
2407 l_payer XMLTYPE;
2408 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Payer_Denorm';
2409 CURSOR l_payer_csr (p_payment_id IN NUMBER) IS
2410 SELECT payer
2411 FROM iby_xml_fd_payer_1_0_v
2412 WHERE payment_id = p_payment_id;
2413
2414 BEGIN
2415 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2416 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2417 debug_level => G_LEVEL_PROCEDURE,
2418 module => l_Debug_Module);
2419 END IF;
2420 Get_Payer_C := Get_Payer_C + 1;
2421 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2422 iby_debug_pub.add(debug_msg => 'Get_Payer_Denorm() entered. count: ' || Get_Payer_C,
2423 debug_level => G_LEVEL_STATEMENT,
2424 module => G_Debug_Module || '.Get_Payer');
2425 iby_debug_pub.add(debug_msg => 'p_payment_id: ' || p_payment_id,
2426 debug_level => G_LEVEL_STATEMENT,
2427 module => G_Debug_Module || '.Get_Payer');
2428 END IF;
2429
2430 OPEN l_payer_csr (p_payment_id);
2431 FETCH l_payer_csr INTO l_payer;
2432 CLOSE l_payer_csr;
2433
2434 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2435 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2436 debug_level => G_LEVEL_PROCEDURE,
2437 module => l_Debug_Module);
2438 END IF;
2439 RETURN l_payer;
2440
2441 END Get_Payer_Denorm;
2442
2443
2444 FUNCTION Get_PayerBankAccount_Denorm(p_payment_id IN NUMBER)
2445 RETURN XMLTYPE
2446 IS
2447 l_payer_ba XMLTYPE;
2448 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayerBankAccount_Denorm';
2449 CURSOR l_payer_ba_csr (p_payment_id IN NUMBER) IS
2450 SELECT int_bank_account
2451 FROM iby_xml_fd_prba_1_0_v
2452 WHERE payment_id = p_payment_id;
2453
2454 BEGIN
2455 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2456 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2457 debug_level => G_LEVEL_PROCEDURE,
2458 module => l_Debug_Module);
2459 END IF;
2460 Get_PayerBankAccount_C := Get_PayerBankAccount_C + 1;
2461 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2462 iby_debug_pub.add(debug_msg => 'Get_PayerBankAccount() entered. count: ' || Get_PayerBankAccount_C,
2463 debug_level => G_LEVEL_STATEMENT,
2464 module => G_Debug_Module || '.Get_PayerBankAccount');
2465 iby_debug_pub.add(debug_msg => 'p_payment_id: ' || p_payment_id,
2466 debug_level => G_LEVEL_STATEMENT,
2467 module => G_Debug_Module || '.Get_PayerBankAccount');
2468 END IF;
2469 OPEN l_payer_ba_csr (p_payment_id);
2470 FETCH l_payer_ba_csr INTO l_payer_ba;
2471 CLOSE l_payer_ba_csr;
2472
2473 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2474 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2475 debug_level => G_LEVEL_PROCEDURE,
2476 module => l_Debug_Module);
2477 END IF;
2478 RETURN l_payer_ba;
2479
2480 END Get_PayerBankAccount_Denorm;
2481
2482
2483 FUNCTION Get_PayerIns_Denorm(p_payment_instruction_id IN NUMBER)
2484 RETURN XMLTYPE
2485 IS
2486 l_payment_id NUMBER;
2487 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayerIns_Denorm';
2488 CURSOR l_ins_payment_csr (p_payment_instruction_id IN NUMBER) IS
2489 SELECT payment_id
2490 FROM iby_payments_all
2491 WHERE payment_instruction_id = p_payment_instruction_id
2492 AND ROWNUM = 1;
2493
2494 BEGIN
2495 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2496 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2497 debug_level => G_LEVEL_PROCEDURE,
2498 module => l_Debug_Module);
2499 END IF;
2500
2501 OPEN l_ins_payment_csr (p_payment_instruction_id);
2502 FETCH l_ins_payment_csr INTO l_payment_id;
2503 CLOSE l_ins_payment_csr;
2504
2505 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2506 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2507 debug_level => G_LEVEL_PROCEDURE,
2508 module => l_Debug_Module);
2509 END IF;
2510 RETURN Get_Payer_Denorm(l_payment_id);
2511
2512 END Get_PayerIns_Denorm;
2513
2514
2515 FUNCTION Get_PayerBankAccountIns_Denorm(p_payment_instruction_id IN NUMBER)
2516 RETURN XMLTYPE
2517 IS
2518 l_payment_id NUMBER;
2519 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayerBankAccountIns_Denorm';
2520 CURSOR l_ins_payment_csr (p_payment_instruction_id IN NUMBER) IS
2521 SELECT payment_id
2522 FROM iby_payments_all
2523 WHERE payment_instruction_id = p_payment_instruction_id
2524 AND ROWNUM = 1;
2525
2526 BEGIN
2527 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2528 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2529 debug_level => G_LEVEL_PROCEDURE,
2530 module => l_Debug_Module);
2531 END IF;
2532
2533 OPEN l_ins_payment_csr (p_payment_instruction_id);
2534 FETCH l_ins_payment_csr INTO l_payment_id;
2535 CLOSE l_ins_payment_csr;
2536
2537 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2538 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2539 debug_level => G_LEVEL_PROCEDURE,
2540 module => l_Debug_Module);
2541 END IF;
2542 RETURN Get_PayerBankAccount_Denorm(l_payment_id);
2543
2544 END Get_PayerBankAccountIns_Denorm;
2545
2546
2547 FUNCTION Get_Ins_AccountSettingsAgg(p_bep_account_id IN NUMBER)
2548 RETURN XMLTYPE
2549 IS
2550 l_acctsettings_agg XMLTYPE;
2551
2552 CURSOR l_acctsettings_csr (p_bep_account_id IN NUMBER) IS
2553 SELECT XMLAgg(account_setting)
2554 FROM iby_xml_fd_acct_settings_1_0_v
2555 WHERE bep_account_id = p_bep_account_id;
2556
2557 BEGIN
2558
2559 OPEN l_acctsettings_csr (p_bep_account_id);
2560 FETCH l_acctsettings_csr INTO l_acctsettings_agg;
2561 CLOSE l_acctsettings_csr;
2562
2563 RETURN l_acctsettings_agg;
2564
2565 END Get_Ins_AccountSettingsAgg;
2566
2567
2568
2569 FUNCTION Get_Pmt_DocPayableAgg(p_payment_id IN NUMBER)
2570 RETURN XMLTYPE
2571 IS
2572 l_docpayable_agg XMLTYPE;
2573 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Pmt_DocPayableAgg';
2574
2575 CURSOR l_docpayable_csr (p_payment_id IN NUMBER) IS
2576 SELECT XMLAgg(doc_payable)
2577 FROM iby_xml_fd_doc_1_0_v
2578 WHERE formatting_payment_id = p_payment_id --bug 7006504
2579 AND document_status <> 'REMOVED';
2580
2581 -- for ppr report we need to filter the docs by MOAC accessibility check
2582 CURSOR l_docpayable_ppr_rpt_csr (p_payment_id IN NUMBER) IS
2583 SELECT XMLAgg(doc_payable)
2584 FROM iby_xml_fd_doc_1_0_v xml_doc, iby_docs_payable_all doc, ce_security_profiles_v ce_sp
2585 WHERE xml_doc.payment_id = p_payment_id --bug 7459662
2586 AND xml_doc.document_payable_id = doc.document_payable_id
2587 AND ce_sp.organization_type = doc.org_type
2588 AND ce_sp.organization_id = doc.org_id
2589 AND xml_doc.document_status <> 'REMOVED';
2590
2591 BEGIN
2592
2593 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2594 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2595 debug_level => G_LEVEL_PROCEDURE,
2596 module => l_Debug_Module);
2597
2598 iby_debug_pub.add(debug_msg => 'input p_payment_id: ' || p_payment_id,
2599 debug_level => G_LEVEL_STATEMENT,
2600 module => l_Debug_Module);
2601 END IF;
2602
2603 IF G_Extract_Run_Mode = G_EXTRACT_MODE_PPR_RPT THEN
2604 OPEN l_docpayable_ppr_rpt_csr (p_payment_id);
2605 FETCH l_docpayable_ppr_rpt_csr INTO l_docpayable_agg;
2606 CLOSE l_docpayable_ppr_rpt_csr;
2607
2608 ELSE
2609 OPEN l_docpayable_csr (p_payment_id);
2610 FETCH l_docpayable_csr INTO l_docpayable_agg;
2611 CLOSE l_docpayable_csr;
2612
2613 END IF;
2614
2615 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2616 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2617 debug_level => G_LEVEL_PROCEDURE,
2618 module => l_Debug_Module);
2619 END IF;
2620
2621 RETURN l_docpayable_agg;
2622 EXCEPTION
2623 WHEN OTHERS THEN
2624 iby_debug_pub.add(debug_msg => 'EXECPTION OCCURED IN : ' || l_Debug_Module || sqlerrm ,
2625 debug_level => G_LEVEL_PROCEDURE,
2626 module => l_Debug_Module);
2627 RAISE;
2628 END Get_Pmt_DocPayableAgg;
2629
2630
2631 FUNCTION Get_Payee(p_payment_id IN NUMBER)
2632 RETURN XMLTYPE
2633 IS
2634 l_payee XMLTYPE;
2635 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Payee';
2636 l_pmt_func VARCHAR2(1);
2637
2638 CURSOR l_pmt_func_csr (p_payment_id IN NUMBER) IS
2639 SELECT nvl(employee_payment_flag, 'N')
2640 FROM iby_payments_all
2641 WHERE payment_id = p_payment_id;
2642
2643 CURSOR l_payee_csr (p_payment_id IN NUMBER) IS
2644 SELECT payee
2645 FROM iby_xml_fd_payee_1_0_v
2646 WHERE payment_id = p_payment_id;
2647
2648 CURSOR l_payeem_csr (p_payment_id IN NUMBER) IS
2649 SELECT payee
2650 FROM iby_xml_fd_payeem_1_0_v
2651 WHERE payment_id = p_payment_id;
2652
2653 BEGIN
2654
2655 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2656 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2657 debug_level => G_LEVEL_PROCEDURE,
2658 module => l_Debug_Module);
2659 END IF;
2660 OPEN l_pmt_func_csr (p_payment_id);
2661 FETCH l_pmt_func_csr INTO l_pmt_func;
2662 CLOSE l_pmt_func_csr;
2663 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2664 iby_debug_pub.add(debug_msg => 'Before the condition - ' || ' l_pmt_func:' || l_pmt_func,
2665 debug_level => G_LEVEL_PROCEDURE,
2666 module => l_Debug_Module);
2667 END IF;
2668 IF l_pmt_func = 'Y' AND G_May_Need_HR_Masking THEN
2669
2670 OPEN l_payeem_csr (p_payment_id);
2671 FETCH l_payeem_csr INTO l_payee;
2672 CLOSE l_payeem_csr;
2673
2674 ELSE
2675
2676 OPEN l_payee_csr (p_payment_id);
2677 FETCH l_payee_csr INTO l_payee;
2678 CLOSE l_payee_csr;
2679
2680 END IF;
2681
2682 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2683 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2684 debug_level => G_LEVEL_PROCEDURE,
2685 module => l_Debug_Module);
2686 END IF;
2687
2688 RETURN l_payee;
2689
2690 END Get_Payee;
2691
2692
2693 /* TPP - Start */
2694 FUNCTION Get_InvPayee(p_payment_id IN NUMBER)
2695 RETURN XMLTYPE
2696 IS
2697 l_payee XMLTYPE;
2698 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Payee';
2699
2700 CURSOR l_payee_csr (p_payment_id IN NUMBER) IS
2701 SELECT payee
2702 FROM iby_xml_fd_invpayee_1_0_v
2703 WHERE payment_id = p_payment_id;
2704
2705 BEGIN
2706
2707 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2708 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2709 debug_level => G_LEVEL_PROCEDURE,
2710 module => l_Debug_Module);
2711 END IF;
2712
2713 OPEN l_payee_csr (p_payment_id);
2714 FETCH l_payee_csr INTO l_payee;
2715 CLOSE l_payee_csr;
2716
2717
2718 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2719 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2720 debug_level => G_LEVEL_PROCEDURE,
2721 module => l_Debug_Module);
2722 END IF;
2723
2724 RETURN l_payee;
2725
2726 END Get_InvPayee;
2727
2728
2729
2730 FUNCTION get_rel_add_info(
2731 payee_party_id IN NUMBER,
2732 supplier_site_id IN NUMBER,
2733 inv_payee_party_id IN NUMBER,
2734 inv_supplier_site_id IN NUMBER)
2735 RETURN VARCHAR2
2736 IS
2737 l_rel_add_info VARCHAR2(255);
2738 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.get_rel_add_info';
2739
2740 CURSOR l_add_info_csr (
2741 l_party_id IN NUMBER,
2742 l_supplier_site_id IN NUMBER,
2743 l_remit_party_id IN NUMBER,
2744 l_remit_supplier_site_id IN NUMBER) IS
2745 SELECT irel.additional_information
2746 FROM iby_ext_payee_relationships irel
2747 WHERE irel.party_id = l_party_id
2748 AND irel.supplier_site_id = l_supplier_site_id
2749 AND irel.remit_party_id = l_remit_party_id
2750 AND irel.remit_supplier_site_id = l_remit_supplier_site_id
2751 AND irel.active = 'Y'
2752 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')
2753 ;
2754
2755 BEGIN
2756
2757 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2758 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2759 debug_level => G_LEVEL_PROCEDURE,
2760 module => l_Debug_Module);
2761 END IF;
2762
2763 OPEN l_add_info_csr (inv_payee_party_id, inv_supplier_site_id, payee_party_id, supplier_site_id);
2764 FETCH l_add_info_csr INTO l_rel_add_info;
2765 CLOSE l_add_info_csr;
2766
2767
2768 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2769 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2770 debug_level => G_LEVEL_PROCEDURE,
2771 module => l_Debug_Module);
2772 END IF;
2773
2774 RETURN l_rel_add_info;
2775
2776 END get_rel_add_info;
2777
2778
2779
2780
2781
2782 FUNCTION get_relship_id(
2783 payee_party_id IN NUMBER,
2784 supplier_site_id IN NUMBER,
2785 inv_payee_party_id IN NUMBER,
2786 inv_supplier_site_id IN NUMBER)
2787 RETURN NUMBER
2788 IS
2789 l_relship_id NUMBER := -1;
2790 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.get_relship_id';
2791
2792 CURSOR l_relshipid_csr (
2793 l_party_id IN NUMBER,
2794 l_supplier_site_id IN NUMBER,
2795 l_remit_party_id IN NUMBER,
2796 l_remit_supplier_site_id IN NUMBER) IS
2797 SELECT irel.relationship_id
2798 FROM iby_ext_payee_relationships irel
2799 WHERE irel.party_id = l_party_id
2800 AND irel.supplier_site_id = l_supplier_site_id
2801 AND irel.remit_party_id = l_remit_party_id
2802 AND irel.remit_supplier_site_id = l_remit_supplier_site_id
2803 AND irel.active = 'Y'
2804 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')
2805 ;
2806
2807 BEGIN
2808
2809 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2810 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2811 debug_level => G_LEVEL_PROCEDURE,
2812 module => l_Debug_Module);
2813 END IF;
2814
2815 OPEN l_relshipid_csr (inv_payee_party_id, inv_supplier_site_id, payee_party_id, supplier_site_id);
2816 FETCH l_relshipid_csr INTO l_relship_id;
2817 CLOSE l_relshipid_csr;
2818
2819
2820 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2821 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2822 debug_level => G_LEVEL_PROCEDURE,
2823 module => l_Debug_Module);
2824 END IF;
2825
2826 RETURN l_relship_id;
2827
2828 END get_relship_id;
2829 /* TPP - End */
2830
2831
2832 FUNCTION Get_PayeeBankAccount(p_payment_id IN NUMBER, p_external_bank_account_id IN NUMBER)
2833 RETURN XMLTYPE
2834 IS
2835 l_payee_ba XMLTYPE;
2836 l_pmt_func VARCHAR2(1);
2837 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayeeBankAccount';
2838
2839 CURSOR l_pmt_func_csr (p_payment_id IN NUMBER) IS
2840 SELECT nvl(employee_payment_flag, 'N')
2841 FROM iby_payments_all
2842 WHERE payment_id = p_payment_id;
2843
2844 CURSOR l_payee_ba_csr (p_external_bank_account_id IN NUMBER) IS
2845 SELECT ext_bank_account
2846 FROM iby_xml_fd_peba_1_0_v
2847 WHERE bank_account_id = p_external_bank_account_id;
2848
2849 CURSOR l_payee_bam_csr (p_external_bank_account_id IN NUMBER) IS
2850 SELECT ext_bank_account
2851 FROM iby_xml_fd_pebam_1_0_v
2852 WHERE bank_account_id = p_external_bank_account_id;
2853
2854 BEGIN
2855 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2856 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
2857 debug_level => G_LEVEL_PROCEDURE,
2858 module => l_Debug_Module);
2859 END IF;
2860 -- Added this as a workaround for bug 5293384
2861 -- The new behavior is this procedure will always be called
2862 IF (p_external_bank_account_id IS NULL) THEN
2863 RETURN NULL;
2864 end if;
2865
2866 OPEN l_pmt_func_csr (p_payment_id);
2867 FETCH l_pmt_func_csr INTO l_pmt_func;
2868 CLOSE l_pmt_func_csr;
2869 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2870 iby_debug_pub.add(debug_msg => 'Before the condition: ' || ' l_pmt_func:'|| l_pmt_func,
2871 debug_level => G_LEVEL_PROCEDURE,
2872 module => l_Debug_Module);
2873 END IF;
2874 IF l_pmt_func = 'Y' AND G_May_Need_HR_Masking THEN
2875
2876 OPEN l_payee_bam_csr (p_external_bank_account_id);
2877 FETCH l_payee_bam_csr INTO l_payee_ba;
2878 CLOSE l_payee_bam_csr;
2879
2880 ELSE
2881
2882 OPEN l_payee_ba_csr (p_external_bank_account_id);
2883 FETCH l_payee_ba_csr INTO l_payee_ba;
2884 CLOSE l_payee_ba_csr;
2885
2886 END IF;
2887
2888 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2889 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
2890 debug_level => G_LEVEL_PROCEDURE,
2891 module => l_Debug_Module);
2892 END IF;
2893
2894 RETURN l_payee_ba;
2895
2896 END Get_PayeeBankAccount;
2897
2898
2899 FUNCTION Get_PayeeBankAccount_Denorm(p_payment_id IN NUMBER, p_external_bank_account_id IN NUMBER)
2900 RETURN XMLTYPE
2901 IS
2902 l_payee_ba XMLTYPE;
2903 l_pmt_func VARCHAR2(1);
2904 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_PayeeBankAccount_Denorm';
2905
2906 CURSOR l_pmt_func_csr (p_payment_id IN NUMBER) IS
2907 SELECT nvl(employee_payment_flag, 'N')
2908 FROM iby_payments_all
2909 WHERE payment_id = p_payment_id;
2910
2911 CURSOR l_payee_ba_csr (p_payment_id IN NUMBER) IS
2912 SELECT ext_bank_account
2913 FROM iby_xml_fd_peba_1_0_vd
2914 WHERE payment_id = p_payment_id;
2915
2916 CURSOR l_payee_bam_csr (p_payment_id IN NUMBER) IS
2917 SELECT ext_bank_account
2918 FROM iby_xml_fd_pebam_1_0_vd
2919 WHERE payment_id = p_payment_id;
2920
2921 BEGIN
2922 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2923 iby_debug_pub.add(debug_msg => 'ENTER: ' || l_Debug_Module,
2924 debug_level => G_LEVEL_PROCEDURE,
2925 module => l_Debug_Module);
2926 END IF;
2927 -- Added this as a workaround for bug 5293384
2928 -- The new behavior is this procedure will always be called
2929 IF (p_external_bank_account_id IS NULL) THEN
2930 RETURN NULL;
2931 end if;
2932
2933 OPEN l_pmt_func_csr (p_payment_id);
2934 FETCH l_pmt_func_csr INTO l_pmt_func;
2935 CLOSE l_pmt_func_csr;
2936 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2937 iby_debug_pub.add(debug_msg => 'Before IF : -- l_pmt_func: ' || l_pmt_func ,
2938 debug_level => G_LEVEL_PROCEDURE,
2939 module => l_Debug_Module);
2940 END IF;
2941 IF l_pmt_func = 'Y' AND G_May_Need_HR_Masking THEN
2942
2943 OPEN l_payee_bam_csr (p_payment_id);
2944 FETCH l_payee_bam_csr INTO l_payee_ba;
2945 CLOSE l_payee_bam_csr;
2946
2947 ELSE
2948
2949 OPEN l_payee_ba_csr (p_payment_id);
2950 FETCH l_payee_ba_csr INTO l_payee_ba;
2951 CLOSE l_payee_ba_csr;
2952
2953 END IF;
2954
2955 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2956 iby_debug_pub.add(debug_msg => 'EXIT: ' || l_Debug_Module,
2957 debug_level => G_LEVEL_PROCEDURE,
2958 module => l_Debug_Module);
2959 END IF;
2960
2961 RETURN l_payee_ba;
2962
2963 END Get_PayeeBankAccount_Denorm;
2964
2965
2966 FUNCTION Get_Doc_DocLineAgg(p_document_payable_id IN NUMBER)
2967 RETURN XMLTYPE
2968 IS
2969 l_docline_agg XMLTYPE;
2970 l_conc_invalid_chars VARCHAR2(50);
2971 l_conc_replacement_chars VARCHAR2(50);
2972 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Doc_DocLineAgg';
2973
2974 -- IBY_XML_FD_DOCLINE_1_0_V is obselete fz 8/30/2005
2975 -- Bug 6321384 Added nvl to check for NULL values
2976 CURSOR l_docline_csr (p_document_payable_id IN NUMBER,
2977 p_conc_invalid_chars IN VARCHAR2,
2978 p_conc_replacement_chars IN VARCHAR2) IS
2979 SELECT XMLAgg(
2980 XMLElement("DocumentPayableLine",
2981 XMLElement("LineNumber", apdl.line_number),
2982 XMLElement("PONumber", apdl.po_number),
2983 XMLElement("LineType",
2984 XMLElement("Code", apdl.line_type),
2985 XMLElement("Meaning", null)),
2986 XMLElement("LineDescription", TRANSLATE(apdl.description, p_conc_invalid_chars, p_conc_replacement_chars)),
2987 XMLElement("LineGrossAmount",
2988 XMLElement("Value", apdl.line_gross_amount),
2989 XMLElement("Currency", XMLElement("Code", ibydoc.document_currency_code))),
2990 XMLElement("UnitPrice", apdl.unit_price),
2991 XMLElement("Quantity", apdl.quantity),
2992 XMLElement("UnitOfMeasure",
2993 XMLElement("Code", apdl.unit_of_measure),
2994 XMLElement("Meaning", null)),
2995 XMLElement("Tax",
2996 XMLElement("TaxCode", apdl.tax),
2997 XMLElement("TaxRate", apdl.tax_rate)
2998 ),
2999 IBY_FD_EXTRACT_EXT_PUB.Get_Docline_Ext_Agg(ibydoc.document_payable_id, apdl.line_number)
3000 )
3001 )
3002 FROM ap_document_lines_v apdl, iby_docs_payable_all ibydoc,
3003 iby_pay_service_requests ibypsr
3004 WHERE ibydoc.document_payable_id = p_document_payable_id
3005 AND ibydoc.calling_app_doc_unique_ref1 = apdl.calling_app_doc_unique_ref1
3006 AND nvl(ibydoc.calling_app_doc_unique_ref2,-99) = apdl.calling_app_doc_unique_ref2
3007 AND nvl(ibydoc.calling_app_doc_unique_ref3,-99) = apdl.calling_app_doc_unique_ref3
3008 AND nvl(ibydoc.calling_app_doc_unique_ref4,-99) = nvl(apdl.calling_app_doc_unique_ref4,-99)
3009 AND nvl(ibydoc.calling_app_doc_unique_ref5,-99) = nvl(apdl.calling_app_doc_unique_ref5,-99)
3010 AND ibydoc.calling_app_id = apdl.calling_app_id
3011 AND ibypsr.call_app_pay_service_req_code = apdl.call_app_pay_service_req_code
3012 AND ibydoc.payment_service_request_id = ibypsr.payment_service_request_id;
3013
3014 BEGIN
3015
3016 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3017 iby_debug_pub.add(debug_msg => 'ENTER: ' || l_Debug_Module,
3018 debug_level => G_LEVEL_PROCEDURE,
3019 module => l_Debug_Module);
3020 END IF;
3021 /* Preparing the concatinated strings of invalid characters
3022 and corresponding replacement characters. Bug 7292070 */
3023 FOR i in 1..32 LOOP
3024 l_conc_invalid_chars :=l_conc_invalid_chars||fnd_global.local_chr(i-1);
3025 l_conc_replacement_chars :=l_conc_replacement_chars||' ';
3026 END LOOP;
3027
3028 OPEN l_docline_csr (p_document_payable_id,l_conc_invalid_chars,l_conc_replacement_chars);
3029 FETCH l_docline_csr INTO l_docline_agg;
3030 CLOSE l_docline_csr;
3031
3032 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3033 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3034 debug_level => G_LEVEL_PROCEDURE,
3035 module => l_Debug_Module);
3036 END IF;
3037 RETURN l_docline_agg;
3038 EXCEPTION
3039 WHEN OTHERS THEN
3040 iby_debug_pub.add(debug_msg => 'EXECPTION OCCURED IN : ' || l_Debug_Module || sqlerrm ,
3041 debug_level => G_LEVEL_PROCEDURE,
3042 module => l_Debug_Module);
3043 RAISE;
3044 END Get_Doc_DocLineAgg;
3045
3046
3047 FUNCTION Get_SRA_Attribute(p_payment_id IN NUMBER, p_attribute_type IN NUMBER)
3048 RETURN VARCHAR2
3049 IS
3050 l_sra_delivery_method VARCHAR2(30);
3051 l_override_payee_flag VARCHAR2(1);
3052 l_sra_req_flag VARCHAR2(1);
3053 l_pp_sra_delivery_method VARCHAR2(30);
3054 l_ps_lang VARCHAR2(4);
3055 l_ps_territory VARCHAR2(60);
3056
3057 CURSOR l_sra_setup_csr (p_payment_id IN NUMBER) IS
3058 SELECT sra_setup.sra_override_payee_flag, sra_setup.remit_advice_delivery_method
3059 FROM iby_payments_all pmt, iby_pay_instructions_all ins,
3060 iby_payment_profiles pp, iby_remit_advice_setup sra_setup
3061 WHERE pmt.payment_id = p_payment_id
3062 AND pmt.payment_instruction_id = ins.payment_instruction_id
3063 AND pp.payment_profile_id = ins.payment_profile_id
3064 AND pp.system_profile_code = sra_setup.system_profile_code;
3065
3066 CURSOR l_sra_req_flag_csr (p_payment_id IN NUMBER) IS
3067 SELECT separate_remit_advice_req_flag
3068 FROM iby_payments_all
3069 WHERE payment_id = p_payment_id;
3070
3071 CURSOR l_lang_territory_csr (p_payment_id IN NUMBER) IS
3072 SELECT loc.language, loc.country
3073 FROM hz_party_sites ps, hz_locations loc, iby_payments_all pmt
3074 WHERE payment_id = p_payment_id
3075 AND pmt.party_site_id = ps.party_site_id(+)
3076 AND loc.location_id = ps.location_id;
3077
3078 BEGIN
3079
3080 IF p_attribute_type = G_SRA_DELIVERY_METHOD_ATTR THEN
3081
3082 OPEN l_sra_setup_csr (p_payment_id);
3083 FETCH l_sra_setup_csr INTO l_override_payee_flag, l_pp_sra_delivery_method;
3084 CLOSE l_sra_setup_csr;
3085
3086 IF l_override_payee_flag = 'Y' THEN
3087 l_sra_delivery_method := l_pp_sra_delivery_method;
3088
3089 ELSE
3090 l_sra_delivery_method := Get_Payee_Default_Attribute(p_payment_id, p_attribute_type);
3091
3092 IF l_sra_delivery_method is null THEN
3093 l_sra_delivery_method := l_pp_sra_delivery_method;
3094 END IF;
3095 END IF;
3096
3097 return l_sra_delivery_method;
3098
3099 ELSIF p_attribute_type = G_SRA_REQ_FLAG_ATTR THEN
3100
3101 OPEN l_sra_req_flag_csr (p_payment_id);
3102 FETCH l_sra_req_flag_csr INTO l_sra_req_flag;
3103 CLOSE l_sra_req_flag_csr;
3104
3105 return l_sra_req_flag;
3106
3107 ELSIF p_attribute_type = G_SRA_PS_LANG_ATTR OR
3108 p_attribute_type = G_SRA_PS_TERRITORY_ATTR THEN
3109
3110 OPEN l_lang_territory_csr (p_payment_id);
3111 FETCH l_lang_territory_csr INTO l_ps_lang, l_ps_territory;
3112 CLOSE l_lang_territory_csr;
3113
3114 IF p_attribute_type = G_SRA_PS_LANG_ATTR THEN
3115 return l_ps_lang;
3116 ELSE
3117 return l_ps_territory;
3118 END IF;
3119
3120 ELSE
3121 return Get_Payee_Default_Attribute(p_payment_id, p_attribute_type);
3122 END IF;
3123
3124 END Get_SRA_Attribute;
3125
3126
3127 FUNCTION Get_Payee_Default_Attribute(p_payment_id IN NUMBER, p_attribute_type IN NUMBER)
3128 RETURN VARCHAR2
3129 IS
3130 l_attribute_val VARCHAR2(1000);
3131
3132 CURSOR l_payee_defaulting_cur (p_payment_id NUMBER) IS
3133 SELECT payee.remit_advice_delivery_method,
3134 payee.remit_advice_email,
3135 payee.remit_advice_fax
3136 FROM iby_external_payees_all payee,
3137 iby_payments_all pmt
3138 WHERE payee.payee_party_id = pmt.payee_party_id
3139 AND payee.payment_function = pmt.payment_function
3140 AND (payee.org_id is NULL OR (payee.org_id = pmt.org_id AND payee.org_type = pmt.org_type))
3141 AND (payee.party_site_id is NULL OR payee.party_site_id = pmt.party_site_id)
3142 AND (payee.supplier_site_id is NULL OR payee.supplier_site_id = pmt.supplier_site_id)
3143 AND pmt.payment_id = p_payment_id
3144 ORDER BY payee.supplier_site_id, payee.party_site_id, payee.org_id;
3145
3146 BEGIN
3147
3148 FOR l_default_rec in l_payee_defaulting_cur(p_payment_id) LOOP
3149 IF (l_attribute_val is NULL) THEN
3150 IF p_attribute_type = G_SRA_DELIVERY_METHOD_ATTR THEN
3151 l_attribute_val := l_default_rec.remit_advice_delivery_method;
3152 ELSIF p_attribute_type = G_SRA_EMAIL_ATTR THEN
3153 l_attribute_val := l_default_rec.remit_advice_email;
3154 ELSIF p_attribute_type = G_SRA_FAX_ATTR THEN
3155 l_attribute_val := l_default_rec.remit_advice_fax;
3156 END IF;
3157 END IF;
3158 END LOOP;
3159
3160 return l_attribute_val;
3161 END Get_Payee_Default_Attribute;
3162
3163
3164 -- following are four wrappers for the corresponding
3165 -- federal APIs. The wrappers will swallow any exceptions
3166 -- from calling the federal code.
3167 -- comment out actual calls to federal code
3168 -- as the FV package are not yet available
3169 -- - in F12MSS2/F12DBS2 8/30/2005
3170 FUNCTION get_FEIN(payment_instruction_id IN NUMBER)
3171 RETURN VARCHAR2
3172 IS
3173 BEGIN
3174 return FV_FEDERAL_PAYMENT_FIELDS_PKG.get_FEIN(payment_instruction_id);
3175 EXCEPTION
3176 -- swallow exceptions
3177 WHEN OTHERS THEN
3178 RETURN NULL;
3179 END get_FEIN;
3180
3181 FUNCTION get_Abbreviated_Agency_Code(payment_instruction_id IN NUMBER)
3182 RETURN VARCHAR2
3183 IS
3184 BEGIN
3185 return FV_FEDERAL_PAYMENT_FIELDS_PKG.get_Abbreviated_Agency_Code(payment_instruction_id);
3186 EXCEPTION
3187 -- swallow exceptions
3188 WHEN OTHERS THEN
3189 RETURN NULL;
3190 END get_Abbreviated_Agency_Code;
3191
3192 FUNCTION get_Allotment_Code(payment_id IN NUMBER)
3193 RETURN VARCHAR2
3194 IS
3195 BEGIN
3196 return FV_FEDERAL_PAYMENT_FIELDS_PKG.get_Allotment_Code(payment_id);
3197 EXCEPTION
3198 -- swallow exceptions
3199 WHEN OTHERS THEN
3200 RETURN NULL;
3201 END get_Allotment_Code;
3202
3203 FUNCTION TOP_Offset_Eligibility_Flag(payment_id IN NUMBER)
3204 RETURN VARCHAR2
3205 IS
3206 BEGIN
3207 return FV_FEDERAL_PAYMENT_FIELDS_PKG.TOP_Offset_Eligibility_Flag(payment_id);
3208 EXCEPTION
3209 -- swallow exceptions
3210 WHEN OTHERS THEN
3211 RETURN NULL;
3212 END TOP_Offset_Eligibility_Flag;
3213
3214 FUNCTION get_SPS_PMT_TS(payment_id IN NUMBER)
3215 RETURN VARCHAR2
3216 IS
3217 l_treasury_symbol VARCHAR2(35);
3218
3219 CURSOR l_ts_cur (p_payment_id NUMBER) IS
3220 SELECT fv.treasury_symbol
3221 FROM FV_TP_TS_AMT_DATA fv,
3222 iby_payments_all pmt
3223 WHERE pmt.payment_instruction_id = fv.payment_instruction_id
3224 AND pmt.payment_id = p_payment_id
3225 AND ROWNUM = 1;
3226
3227 BEGIN
3228
3229 OPEN l_ts_cur (payment_id);
3230 FETCH l_ts_cur INTO l_treasury_symbol;
3231 CLOSE l_ts_cur;
3232
3233 return l_treasury_symbol;
3234 EXCEPTION
3235 -- swallow exceptions
3236 WHEN OTHERS THEN
3237 RETURN NULL;
3238 END get_SPS_PMT_TS;
3239
3240
3241 FUNCTION Get_Bordero_Bank_Ref(p_doc_payable_id IN NUMBER)
3242 RETURN VARCHAR2
3243 IS
3244 l_return_status VARCHAR2(1);
3245 l_bordero_bank_ref VARCHAR2(30);
3246 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Bordero_Bank_Ref';
3247 l_doc_idx NUMBER;
3248
3249 BEGIN
3250
3251 --IF G_IS_BRAZIL IS NULL THEN
3252 l_doc_idx := p_doc_payable_id;
3253
3254 /* perf bug 6763515 */
3255 IF l_doc_idx is NOT NULL THEN
3256 IF (NOT(g_docs_pay_attribs_tbl.EXISTS(l_doc_idx))) THEN
3257 g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL := JL_BR_AP_PAY_SCHED_GDF_PKG.Check_Brazil(
3258 P_Doc_Payable_ID => p_doc_payable_id,
3259 P_RETURN_STATUS => l_return_status);
3260 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3261 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,
3262 debug_level => G_LEVEL_STATEMENT,
3263 module => l_Debug_Module);
3264 END IF;
3265 END IF;
3266 G_IS_BRAZIL :=g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL;
3267 END IF;
3268
3269
3270 --END IF;
3271
3272 IF G_IS_BRAZIL = 1 THEN
3273 l_bordero_bank_ref := JL_BR_AP_PAY_SCHED_GDF_PKG.Get_Bordero_Bank_Ref(
3274 P_Doc_Payable_ID => p_doc_payable_id,
3275 P_RETURN_STATUS => l_return_status);
3276
3277 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3278 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,
3279 debug_level => G_LEVEL_STATEMENT,
3280 module => l_Debug_Module);
3281 END IF;
3282 END IF;
3283
3284 return l_bordero_bank_ref;
3285 EXCEPTION
3286 -- swallow exceptions
3287 WHEN OTHERS THEN
3288 RETURN NULL;
3289 END Get_Bordero_Bank_Ref;
3290
3291
3292 FUNCTION Get_Bordero_Int_Amt(p_doc_payable_id IN NUMBER)
3293 RETURN Number
3294 IS
3295 l_return_status VARCHAR2(1);
3296 l_process_type VARCHAR2(30);
3297 l_bordero_int_amt NUMBER;
3298 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Bordero_Int_Amt';
3299 l_doc_idx NUMBER;
3300
3301 BEGIN
3302
3303 --IF G_IS_BRAZIL IS NULL THEN
3304 l_doc_idx := p_doc_payable_id;
3305
3306 /* perf bug 6763515 */
3307 IF l_doc_idx is NOT NULL THEN
3308 IF (NOT(g_docs_pay_attribs_tbl.EXISTS(l_doc_idx))) THEN
3309 g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL := JL_BR_AP_PAY_SCHED_GDF_PKG.Check_Brazil(
3310 P_Doc_Payable_ID => p_doc_payable_id,
3311 P_RETURN_STATUS => l_return_status);
3312
3313 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3314 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,
3315 debug_level => G_LEVEL_STATEMENT,
3316 module => l_Debug_Module);
3317 END IF;
3318 END IF;
3319 G_IS_BRAZIL :=g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL;
3320 END IF;
3321
3322
3323 --END IF;
3324
3325 IF G_IS_BRAZIL = 1 THEN
3326 l_bordero_int_amt := JL_BR_AP_PAY_SCHED_GDF_PKG.Get_Bordero_Int_Amt(
3327 P_Doc_Payable_ID => p_doc_payable_id,
3328 P_Process_Type => l_process_type,
3329 P_RETURN_STATUS => l_return_status);
3330 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3331 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,
3332 debug_level => G_LEVEL_STATEMENT,
3333 module => l_Debug_Module);
3334 END IF;
3335 END IF;
3336
3337 return l_bordero_int_amt;
3338 EXCEPTION
3339 -- swallow exceptions
3340 WHEN OTHERS THEN
3341 RETURN NULL;
3342 END Get_Bordero_Int_Amt;
3343
3344
3345
3346 FUNCTION Get_Bordero_Abatement(p_doc_payable_id IN NUMBER)
3347 RETURN Number
3348 IS
3349 l_return_status VARCHAR2(1);
3350 l_process_type VARCHAR2(30);
3351 l_bordero_abt_amt NUMBER;
3352 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Bordero_Abatement';
3353 l_doc_idx NUMBER;
3354
3355 BEGIN
3356
3357 --IF G_IS_BRAZIL IS NULL THEN
3358 l_doc_idx := p_doc_payable_id;
3359
3360 /* perf bug 6763515 */
3361 IF l_doc_idx is NOT NULL THEN
3362 IF (NOT(g_docs_pay_attribs_tbl.EXISTS(l_doc_idx))) THEN
3363 g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL := JL_BR_AP_PAY_SCHED_GDF_PKG.Check_Brazil(
3364 P_Doc_Payable_ID => p_doc_payable_id,
3365 P_RETURN_STATUS => l_return_status);
3366
3367 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3368 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,
3369 debug_level => G_LEVEL_STATEMENT,
3370 module => l_Debug_Module);
3371 END IF;
3372
3373 END IF;
3374 G_IS_BRAZIL :=g_docs_pay_attribs_tbl(l_doc_idx).IS_BRAZIL;
3375 END IF;
3376
3377
3378 --END IF;
3379
3380 IF G_IS_BRAZIL = 1 THEN
3381 l_bordero_abt_amt := JL_BR_AP_PAY_SCHED_GDF_PKG.Get_Bordero_Abatement(
3382 P_Doc_Payable_ID => p_doc_payable_id,
3383 P_Process_Type => l_process_type,
3384 P_RETURN_STATUS => l_return_status);
3385
3386 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3387 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,
3388 debug_level => G_LEVEL_STATEMENT,
3389 module => l_Debug_Module);
3390 END IF;
3391 END IF;
3392
3393
3394 return l_bordero_abt_amt;
3395 EXCEPTION
3396 -- swallow exceptions
3397 WHEN OTHERS THEN
3398 RETURN NULL;
3399 END Get_Bordero_Abatement;
3400
3401
3402 FUNCTION Get_Payment_Amount_Text(payment_id IN NUMBER)
3403 RETURN VARCHAR2
3404 IS
3405 l_amount NUMBER;
3406 l_currency_code VARCHAR2(10);
3407
3408 CURSOR l_amount_cur (p_payment_id NUMBER) IS
3409 SELECT pmt.payment_amount,
3410 pmt.payment_currency_code
3411 FROM iby_payments_all pmt
3412 WHERE pmt.payment_id = p_payment_id;
3413
3414 BEGIN
3415
3416 OPEN l_amount_cur (payment_id);
3417 FETCH l_amount_cur INTO l_amount, l_currency_code;
3418 CLOSE l_amount_cur;
3419
3420 RETURN IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(l_amount, l_currency_code);
3421
3422 EXCEPTION
3423 -- swallow exceptions
3424 WHEN OTHERS THEN
3425 RETURN NULL;
3426 END Get_Payment_Amount_Text;
3427
3428 FUNCTION Get_Payment_Amount_Withheld(payment_id IN NUMBER)
3429 RETURN NUMBER
3430 IS
3431 l_amount_withheld NUMBER;
3432 CURSOR l_pmt_amount_withheld_csr (p_payment_id IN NUMBER) IS
3433 SELECT sum(amount_withheld)
3434 FROM iby_docs_payable_all
3435 WHERE payment_id = p_payment_id;
3436 BEGIN
3437
3438 OPEN l_pmt_amount_withheld_csr (payment_id);
3439 FETCH l_pmt_amount_withheld_csr INTO l_amount_withheld;
3440 CLOSE l_pmt_amount_withheld_csr;
3441
3442 RETURN l_amount_withheld;
3443 EXCEPTION
3444 -- swallow exceptions
3445 WHEN OTHERS THEN
3446 RETURN NULL;
3447 END Get_Payment_Amount_Withheld;
3448
3449
3450
3451 -- Payment process request extract functions
3452 FUNCTION Get_Ppr_PmtAgg(p_payment_service_request_id IN NUMBER)
3453 RETURN XMLTYPE
3454 IS
3455 l_ppr_pmt_agg XMLTYPE;
3456
3457 CURSOR l_ppr_pmt_csr (p_payment_service_request_id IN NUMBER) IS
3458 SELECT XMLAgg(payment)
3459 FROM iby_xml_fd_pmt_1_0_v
3460 WHERE payment_service_request_id = p_payment_service_request_id;
3461
3462 BEGIN
3463
3464 OPEN l_ppr_pmt_csr (p_payment_service_request_id);
3465 FETCH l_ppr_pmt_csr INTO l_ppr_pmt_agg;
3466 CLOSE l_ppr_pmt_csr;
3467
3468 RETURN l_ppr_pmt_agg;
3469
3470 END Get_Ppr_PmtAgg;
3471
3472 FUNCTION Get_Ppr_PmtCount(p_payment_service_request_id IN NUMBER)
3473 RETURN NUMBER
3474 IS
3475 l_ppr_pmt_count NUMBER;
3476
3477 CURSOR l_ppr_pmt_count_csr (p_payment_service_request_id IN NUMBER) IS
3478 SELECT count(payment_id)
3479 FROM iby_xml_fd_pmt_1_0_v
3480 WHERE payment_service_request_id = p_payment_service_request_id;
3481
3482 BEGIN
3483
3484 OPEN l_ppr_pmt_count_csr (p_payment_service_request_id);
3485 FETCH l_ppr_pmt_count_csr INTO l_ppr_pmt_count;
3486 CLOSE l_ppr_pmt_count_csr;
3487
3488 RETURN l_ppr_pmt_count;
3489
3490 END Get_Ppr_PmtCount;
3491
3492
3493 FUNCTION Get_Ppr_PreBuildDocAgg(p_payment_service_request_id IN NUMBER)
3494 RETURN XMLTYPE
3495 IS
3496 l_docpayable_agg XMLTYPE;
3497
3498 CURSOR l_baddoc_csr (p_payment_service_request_id IN NUMBER) IS
3499 SELECT XMLAgg(doc_payable)
3500 FROM iby_xml_fd_doc_1_0_v
3501 WHERE payment_service_request_id = p_payment_service_request_id
3502 AND payment_id is null;
3503 -- AND document_status in ('REJECTED', 'FAILED_VALIDATION');
3504
3505 BEGIN
3506
3507 OPEN l_baddoc_csr (p_payment_service_request_id);
3508 FETCH l_baddoc_csr INTO l_docpayable_agg;
3509 CLOSE l_baddoc_csr;
3510
3511 RETURN l_docpayable_agg;
3512
3513 END Get_Ppr_PreBuildDocAgg;
3514
3515 FUNCTION Get_Ppr_PreBuildDocCount(p_payment_service_request_id IN NUMBER)
3516 RETURN NUMBER
3517 IS
3518 l_docpayable_count NUMBER;
3519
3520 CURSOR l_baddoc_count_csr (p_payment_service_request_id IN NUMBER) IS
3521 SELECT count(document_payable_id)
3522 FROM iby_xml_fd_doc_1_0_v
3523 WHERE payment_service_request_id = p_payment_service_request_id
3524 AND payment_id is null;
3525 -- AND document_status in ('REJECTED', 'FAILED_VALIDATION');
3526
3527 BEGIN
3528
3529 OPEN l_baddoc_count_csr (p_payment_service_request_id);
3530 FETCH l_baddoc_count_csr INTO l_docpayable_count;
3531 CLOSE l_baddoc_count_csr;
3532
3533 RETURN l_docpayable_count;
3534
3535 END Get_Ppr_PreBuildDocCount;
3536
3537
3538 FUNCTION Get_Pmt_PmtErrAgg(p_payment_id IN NUMBER)
3539 RETURN XMLTYPE
3540 IS
3541 l_pmterr_agg XMLTYPE;
3542 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Pmt_PmtErrAgg';
3543 CURSOR l_pmterr_csr (p_payment_id IN NUMBER) IS
3544 SELECT XMLAgg(payment_error)
3545 FROM iby_xml_fd_pmt_err_1_0_v
3546 WHERE payment_id = p_payment_id;
3547
3548 BEGIN
3549 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3550 iby_debug_pub.add(debug_msg => 'Get_Pmt_PmtErrAgg Enter: ',
3551 debug_level => G_LEVEL_STATEMENT,
3552 module => l_Debug_Module);
3553 END IF;
3554 OPEN l_pmterr_csr (p_payment_id);
3555 FETCH l_pmterr_csr INTO l_pmterr_agg;
3556 CLOSE l_pmterr_csr;
3557 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3558 iby_debug_pub.add(debug_msg => 'Get_Pmt_PmtErrAgg Exit: ',
3559 debug_level => G_LEVEL_STATEMENT,
3560 module => l_Debug_Module);
3561 END IF;
3562 RETURN l_pmterr_agg;
3563
3564 END Get_Pmt_PmtErrAgg;
3565
3566
3567 FUNCTION Get_Doc_DocErrAgg(p_document_payable_id IN NUMBER)
3568 RETURN XMLTYPE
3569 IS
3570 l_docerr_agg XMLTYPE;
3571
3572 CURSOR l_docerr_csr (p_document_payable_id IN NUMBER) IS
3573 SELECT XMLAgg(doc_payable_error)
3574 FROM iby_xml_fd_doc_err_1_0_v
3575 WHERE document_payable_id = p_document_payable_id;
3576
3577 BEGIN
3578
3579 OPEN l_docerr_csr (p_document_payable_id);
3580 FETCH l_docerr_csr INTO l_docerr_agg;
3581 CLOSE l_docerr_csr;
3582
3583 RETURN l_docerr_agg;
3584
3585 END Get_Doc_DocErrAgg;
3586
3587
3588 PROCEDURE Update_Pmt_SRA_Attr_Prt
3589 (
3590 p_payment_instruction_id IN NUMBER
3591 )
3592 IS
3593 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Update_Pmt_SRA_Attr_Prt';
3594
3595 CURSOR l_pmt_csr (p_payment_instruction_id IN NUMBER) IS
3596 SELECT payment_id, payment_reference_number
3597 FROM iby_payments_all
3598 WHERE payment_instruction_id = p_payment_instruction_id
3599 -- note: this where clause should be kept
3600 -- in-sync with the where clause of l_payerinstr_sra_prt_csr
3601 -- in Get_Ins_PayerInstrAgg()
3602 AND Get_SRA_Attribute(payment_id, G_SRA_REQ_FLAG_ATTR) = 'Y'
3603 AND Get_SRA_Attribute(payment_id, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
3604 AND payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
3605 'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED');
3606
3607 BEGIN
3608
3609 FOR l_payment IN l_pmt_csr(p_payment_instruction_id) LOOP
3610
3611 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3612 iby_debug_pub.add(debug_msg => 'updating the SRA delivery method for payment ref number ' ||
3613 l_payment.payment_reference_number || ' to be PRINTED',
3614 debug_level => G_LEVEL_STATEMENT,
3615 module => l_Debug_Module);
3616 END IF;
3617
3618 UPDATE
3619 iby_payments_all
3620 SET
3621 remit_advice_delivery_method = 'PRINTED',
3622 remit_advice_email = null,
3623 remit_advice_fax = null,
3624 object_version_number = object_version_number + 1,
3625 last_updated_by = fnd_global.user_id,
3626 last_update_date = SYSDATE,
3627 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
3628 WHERE
3629 payment_id = l_payment.payment_id;
3630
3631 END LOOP;
3632
3633
3634 END Update_Pmt_SRA_Attr_Prt;
3635
3636
3637 PROCEDURE Update_Pmt_SRA_Attr_Ele
3638 (
3639 p_payment_id IN NUMBER,
3640 p_delivery_method IN VARCHAR2,
3641 p_recipient_email IN VARCHAR2,
3642 p_recipient_fax IN VARCHAR2
3643 )
3644 IS
3645 BEGIN
3646
3647 IF p_delivery_method = 'EMAIL' THEN
3648 UPDATE
3649 iby_payments_all
3650 SET
3651 remit_advice_delivery_method = p_delivery_method,
3652 remit_advice_email = p_recipient_email,
3653 remit_advice_fax = null,
3654 object_version_number = object_version_number + 1,
3655 last_updated_by = fnd_global.user_id,
3656 last_update_date = SYSDATE,
3657 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
3658 WHERE payment_id = p_payment_id;
3659 ELSIF p_delivery_method = 'FAX' THEN
3660 UPDATE
3661 iby_payments_all
3662 SET
3663 remit_advice_delivery_method = p_delivery_method,
3664 remit_advice_email = null,
3665 remit_advice_fax = p_recipient_fax,
3666 object_version_number = object_version_number + 1,
3667 last_updated_by = fnd_global.user_id,
3668 last_update_date = SYSDATE,
3669 last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
3670 WHERE payment_id = p_payment_id;
3671 END IF;
3672 END Update_Pmt_SRA_Attr_Ele;
3673
3674
3675 PROCEDURE initialize
3676 IS
3677 BEGIN
3678 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3679 iby_debug_pub.add(debug_msg => 'ENTER',
3680 debug_level => G_LEVEL_STATEMENT,
3681 module => G_Debug_Module || '.initialize');
3682 END IF;
3683 g_docs_pay_attribs_tbl.DELETE;
3684 g_hr_addr_tbl.DELETE;
3685 g_hz_addr_tbl.DELETE;
3686 g_payer_contact_tbl.DELETE;
3687 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3688 iby_debug_pub.add(debug_msg => 'EXIT',
3689 debug_level => G_LEVEL_STATEMENT,
3690 module => G_Debug_Module || '.initialize');
3691 END IF;
3692
3693 END initialize;
3694
3695 FUNCTION Get_Hz_Address(p_location_id IN NUMBER)
3696 RETURN XMLTYPE
3697 IS
3698 l_hz_addr XMLTYPE;
3699
3700 CURSOR l_hz_addr_csr (p_location_id IN NUMBER) IS
3701 SELECT address
3702 FROM IBY_XML_HZ_ADDR_1_0_V
3703 WHERE location_id = p_location_id;
3704
3705
3706 BEGIN
3707
3708 Get_Hz_Address_C := Get_Hz_Address_C + 1;
3709 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3710 iby_debug_pub.add(debug_msg => 'Get_Hz_Address() entered. count: ' || Get_Hz_Address_C,
3711 debug_level => G_LEVEL_STATEMENT,
3712 module => G_Debug_Module || '.Get_Hz_Address');
3713 iby_debug_pub.add(debug_msg => 'p_location_id: ' || p_location_id,
3714 debug_level => G_LEVEL_STATEMENT,
3715 module => G_Debug_Module || '.Get_Hz_Address');
3716 END IF;
3717 IF p_location_id IS NULL THEN
3718 RETURN NULL;
3719 END IF;
3720
3721 IF (NOT(g_hz_addr_tbl.EXISTS(p_location_id))) THEN
3722 OPEN l_hz_addr_csr (p_location_id);
3723 FETCH l_hz_addr_csr INTO g_hz_addr_tbl(p_location_id).hz_address;
3724 CLOSE l_hz_addr_csr;
3725 END IF;
3726 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3727 iby_debug_pub.add(debug_msg => 'EXIT',
3728 debug_level => G_LEVEL_STATEMENT,
3729 module => G_Debug_Module || '.Get_HZ_Address');
3730 END IF;
3731 RETURN g_hz_addr_tbl(p_location_id).hz_address;
3732
3733 END Get_Hz_Address;
3734
3735
3736
3737 FUNCTION Get_Account_Address(p_location_id IN NUMBER, p_country IN VARCHAR2)
3738 RETURN XMLTYPE
3739 IS
3740 l_hz_addr XMLTYPE;
3741
3742 CURSOR l_hz_addr_csr (p_location_id IN NUMBER) IS
3743 SELECT address
3744 FROM IBY_XML_HZ_ADDR_1_0_V
3745 WHERE location_id = p_location_id;
3746
3747
3748 CURSOR l_country_csr (p_country IN VARCHAR2) IS
3749 SELECT XMLConcat( XMLElement("AddressInternalID", null),
3750 XMLElement("AddressLine1", null), XMLElement("AddressLine2", null),
3751 XMLElement("AddressLine3", null), XMLElement("AddressLine4", null),
3752 XMLElement("City", null), XMLElement("County", null), XMLElement("State", null),
3753 XMLElement("Province", null), XMLElement("Country", te.territory_code),
3754 XMLElement("ISO3DigitCountry", te.iso_territory_code), XMLElement("CountryName", te.territory_short_name),
3755 XMLElement("PostalCode", null),
3756 XMLElement("PreFormattedConcatenatedAddress", null),
3757 XMLElement("PreFormattedMailingAddress", null) )
3758 FROM fnd_territories_vl te
3759 WHERE te.territory_code = p_country;
3760
3761
3762 BEGIN
3763 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3764 iby_debug_pub.add(debug_msg => 'Get_Account_Address() entered. ',
3765 debug_level => G_LEVEL_STATEMENT,
3766 module => G_Debug_Module || '.Get_Account_Address');
3767 iby_debug_pub.add(debug_msg => 'p_location_id: ' || p_location_id || 'p_country: ' || p_country,
3768 debug_level => G_LEVEL_STATEMENT,
3769 module => G_Debug_Module || '.Get_Account_Address');
3770 END IF;
3771
3772 IF p_location_id IS NULL THEN
3773 IF p_country IS NULL THEN
3774 RETURN NULL;
3775 ELSE
3776 OPEN l_country_csr (p_country);
3777 FETCH l_country_csr INTO l_hz_addr;
3778 CLOSE l_country_csr;
3779
3780 RETURN l_hz_addr;
3781 END IF;
3782 END IF;
3783
3784 OPEN l_hz_addr_csr (p_location_id);
3785 FETCH l_hz_addr_csr INTO l_hz_addr;
3786 CLOSE l_hz_addr_csr;
3787
3788 RETURN l_hz_addr;
3789
3790 END Get_Account_Address;
3791
3792
3793
3794 FUNCTION Get_Hr_Address(p_location_id IN NUMBER)
3795 RETURN XMLTYPE
3796 IS
3797 l_hr_addr XMLTYPE;
3798
3799 CURSOR l_hr_addr_csr (p_location_id IN NUMBER) IS
3800 SELECT address
3801 FROM IBY_XML_HR_ADDR_1_0_V
3802 WHERE location_id = p_location_id;
3803
3804 BEGIN
3805
3806 Get_Hr_Address_C := Get_Hr_Address_C + 1;
3807 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3808 iby_debug_pub.add(debug_msg => 'Get_Hr_Address() entered. count: ' || Get_Hr_Address_C,
3809 debug_level => G_LEVEL_STATEMENT,
3810 module => G_Debug_Module || '.Get_Hr_Address');
3811 iby_debug_pub.add(debug_msg => 'p_location_id: ' || p_location_id,
3812 debug_level => G_LEVEL_STATEMENT,
3813 module => G_Debug_Module || '.Get_Hr_Address');
3814 END IF;
3815
3816 IF p_location_id IS NULL THEN
3817 RETURN NULL;
3818 END IF;
3819 IF (NOT(g_hr_addr_tbl.EXISTS(p_location_id))) THEN
3820 OPEN l_hr_addr_csr (p_location_id);
3821 FETCH l_hr_addr_csr INTO g_hr_addr_tbl(p_location_id).hr_address;
3822 CLOSE l_hr_addr_csr;
3823 END IF;
3824
3825 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3826 iby_debug_pub.add(debug_msg => 'EXIT',
3827 debug_level => G_LEVEL_STATEMENT,
3828 module => G_Debug_Module || '.Get_Hr_Address');
3829 END IF;
3830 RETURN g_hr_addr_tbl(p_location_id).hr_address;
3831
3832 END Get_Hr_Address;
3833
3834
3835 FUNCTION Get_Ins_TotalAmt(p_payment_instruction_id IN NUMBER)
3836 RETURN NUMBER
3837 IS
3838 l_amt_total NUMBER;
3839 l_group_by_curr_flag VARCHAR2(1);
3840 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Ins_TotalAmt';
3841
3842 CURSOR l_ins_group_by_curr_csr (p_payment_instruction_id IN NUMBER) IS
3843 SELECT GROUP_BY_PAYMENT_CURRENCY
3844 FROM IBY_EXT_FD_INS_1_0_V
3845 WHERE payment_instruction_id = p_payment_instruction_id;
3846
3847 CURSOR l_amt_total_csr (p_payment_instruction_id IN NUMBER) IS
3848 SELECT SUM(payment_amount)
3849 FROM iby_payments_all
3850 WHERE payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
3851 'SUBMITTED_FOR_PRINTING', 'ISSUED', 'FORMATTED', 'TRANSMITTED')
3852 AND payment_instruction_id = p_payment_instruction_id;
3853
3854 BEGIN
3855
3856 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3857 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3858 debug_level => G_LEVEL_PROCEDURE,
3859 module => l_Debug_Module);
3860 END IF;
3861
3862 OPEN l_ins_group_by_curr_csr (p_payment_instruction_id);
3863 FETCH l_ins_group_by_curr_csr INTO l_group_by_curr_flag;
3864 CLOSE l_ins_group_by_curr_csr;
3865
3866 IF NVL(l_group_by_curr_flag, 'N') = 'N' THEN
3867
3868 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3869 iby_debug_pub.add(debug_msg => 'Instruction is not grouped by currency, so returning null for the total amount.',
3870 debug_level => G_LEVEL_STATEMENT,
3871 module => l_Debug_Module);
3872 END IF;
3873 RETURN NULL;
3874 END IF;
3875
3876 OPEN l_amt_total_csr (p_payment_instruction_id);
3877 FETCH l_amt_total_csr INTO l_amt_total;
3878 CLOSE l_amt_total_csr;
3879
3880 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3881 iby_debug_pub.add(debug_msg => 'Instruction total amount: ' || l_amt_total,
3882 debug_level => G_LEVEL_STATEMENT,
3883 module => l_Debug_Module);
3884
3885 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3886 debug_level => G_LEVEL_PROCEDURE,
3887 module => l_Debug_Module);
3888 END IF;
3889
3890 RETURN l_amt_total;
3891
3892 END Get_Ins_TotalAmt;
3893
3894
3895 FUNCTION Get_Expense_Rpt_CC_Num(p_document_payable_id IN NUMBER)
3896 RETURN VARCHAR2
3897 IS
3898 l_instrid NUMBER;
3899 l_card_num VARCHAR2(30);
3900 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Get_Expense_Rpt_CC_Num';
3901
3902 BEGIN
3903
3904 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3905 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3906 debug_level => G_LEVEL_PROCEDURE,
3907 module => l_Debug_Module);
3908
3909 iby_debug_pub.add(debug_msg => 'Calling iExpense API to get instrid.',
3910 debug_level => G_LEVEL_STATEMENT,
3911 module => l_Debug_Module);
3912 END IF;
3913
3914 l_instrid := AP_WEB_CREDIT_CARD_PKG.get_card_reference_id(p_document_payable_id);
3915
3916 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3917 iby_debug_pub.add(debug_msg => 'Got instrid: ' || l_instrid,
3918 debug_level => G_LEVEL_STATEMENT,
3919 module => l_Debug_Module);
3920 END IF;
3921
3922 IF l_instrid IS NOT NULL THEN
3923 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3924 iby_debug_pub.add(debug_msg => 'Getting instr number',
3925 debug_level => G_LEVEL_STATEMENT,
3926 module => l_Debug_Module);
3927 END IF;
3928
3929 l_card_num := iby_creditcard_pkg.uncipher_ccnumber(l_instrid, iby_utility_pvt.get_view_param('SYS_KEY'));
3930
3931 END IF;
3932
3933 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3934 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3935 debug_level => G_LEVEL_PROCEDURE,
3936 module => l_Debug_Module);
3937 END IF;
3938
3939 RETURN l_card_num;
3940
3941 EXCEPTION
3942 -- swallow exceptions
3943 WHEN OTHERS THEN
3944 RETURN NULL;
3945
3946 END Get_Expense_Rpt_CC_Num;
3947
3948 FUNCTION Replace_Special_Characters(p_base_string IN varchar2)
3949 RETURN VARCHAR2
3950 IS
3951 l_conc_invalid_chars VARCHAR2(50);
3952 l_conc_replacement_chars VARCHAR2(50);
3953 l_modified varchar2(255);
3954 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Replace_Special_Characters';
3955 BEGIN
3956 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3957 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
3958 debug_level => G_LEVEL_PROCEDURE,
3959 module => l_Debug_Module);
3960 END IF;
3961
3962
3963 /* Preparing the concatinated strings of invalid characters
3964 and corresponding replacement characters. Bug 7292070 */
3965 FOR i in 1..32 LOOP
3966 l_conc_invalid_chars :=l_conc_invalid_chars||fnd_global.local_chr(i-1);
3967 l_conc_replacement_chars :=l_conc_replacement_chars||' ';
3968 END LOOP;
3969
3970 l_modified := TRANSLATE(p_base_string, l_conc_invalid_chars, l_conc_replacement_chars);
3971
3972 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3973 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
3974 debug_level => G_LEVEL_PROCEDURE,
3975 module => l_Debug_Module);
3976 END IF;
3977 RETURN l_modified;
3978 END Replace_Special_Characters;
3979
3980
3981
3982 END IBY_FD_EXTRACT_GEN_PVT;
3983
3984
3985