[Home] [Help]
PACKAGE BODY: APPS.IBY_PYO_TRANSACTION
Source
1 PACKAGE BODY IBY_PYO_TRANSACTION AS
2 -- $Header: ibypyob.pls 120.1.12020000.2 2012/07/12 15:06:39 sgogula ship $
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_PYO_TRANSACTION';
4
5 PROCEDURE insert_into_gt (
6 p_payment_instruction_id IN number
7 );
8
9 PROCEDURE delete_from_gt;
10
11 /*===========================================================================
12
13 PROCEDURE NAME: Extract_PYO_Outbound
14
15 PURPOSE: This procedure initiates the concurrent process to
16 extract the eligible transactions.
17
18 ===========================================================================*/
19 PROCEDURE Extract_PYO_Outbound ( p_api_version IN number,
20 p_init_msg_list IN varchar2,
21 p_commit IN varchar2,
22 x_return_status OUT NOCOPY varchar2,
23 x_msg_count OUT NOCOPY number,
24 x_msg_data OUT NOCOPY varchar2,
25 p_payment_instruction_id IN number
26 ) IS
27
28 xProgress VARCHAR2(80);
29 i_file_id PLS_INTEGER;
30 cOutput_Path VARCHAR2(120);
31 i_Filename VARCHAR2(50);
32 i_Transaction_Type VARCHAR2(50);
33 p_errbuf VARCHAR2(32767);
34 p_retcode VARCHAR2(32767);
35 l_api_version CONSTANT NUMBER := 1.0;
36 l_api_name CONSTANT VARCHAR2(30) := 'Extract_PYO_Outbound';
37 p_debug_mode NUMBER;
38 l_pmt_count NUMBER;
39 l_doc_count NUMBER;
40 BEGIN
41
42 fnd_profile.get('ECE_PYO_DEBUG_MODE', p_Debug_Mode);
43 --p_debug_mode := 3;
44
45 IF p_Debug_mode IS NULL THEN
46 p_Debug_Mode := 3;
47 END IF;
48
49
50 ec_debug.enable_debug(p_debug_mode);
51 ec_debug.pl ( 0, 'EC', 'ECE_PYO_START', NULL );
52 ec_debug.push('ECE_AP_TRANSACTION.Extract_PYO_Outbound');
53 ec_debug.pl(3,'Payment Instruction ID ',p_payment_instruction_id );
54 ec_debug.pl(3,'Debug Mode: ',p_debug_mode );
55
56 i_Transaction_Type := 'PYO';
57
58 /*
59 * Inserting values from iby view to Global Temporary table
60 * For performance.
61 */
62
63 insert_into_gt(p_payment_instruction_id);
64
65
66 select count(*) into l_pmt_count FROM IBY_PYO_PAYMENT_GT;
67 SELECT COUNT(*) into l_doc_count FROM IBY_PYO_INVOICE_GT;
68
69 IF (l_pmt_count = 0 OR l_doc_count = 0) THEN
70 fnd_file.put_line(fnd_file.log, 'Number of Payments Selected:'||l_pmt_count);
71 fnd_file.put_line(fnd_file.log, 'Number of Documents Selected:'||l_doc_count);
72 fnd_file.put_line(fnd_file.log, 'UnExpected Error. Payments/Documents could not be fetched');
73 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
74 return;
75 END IF;
76
77 x_return_status := fnd_api.g_ret_sts_success; -- bug:5512623
78
79 xProgress := 'PYO-10-1004';
80
81 -- Derive output filename
82 -- IF fnd_global.conc_request_id IS NOT NULL THEN
83
84 i_file_id := IBY_EC_OP_FILE_NAME_EXT_PUB.get_File_Id(p_payment_instruction_id);
85 ec_debug.pl(3,'File Id returned from ext pub::',i_file_id );
86
87
88 IF i_file_id is null THEN
89 BEGIN
90 SELECT ece_output_runs_s.NEXTVAL INTO i_file_id
91 FROM DUAL;
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 ec_debug.pl ( 0,
95 'EC',
96 'ECE_GET_NEXT_SEQ_FAILED',
97 'PROGRESS_LEVEL',
98 xProgress,
99 'SEQ',
100 'ECE_OUTPUT_RUNS_S'
101 );
102 END;
103 END IF;
104
105 ec_debug.pl (3, 'comes here');
106
107 ec_debug.pl ( 3, 'File ID ',i_file_id);
108
109
110 i_Filename := i_Transaction_Type || i_file_id || '.dat';
111
112
113 xProgress := 'PYO-10-1005';
114 fnd_profile.get ( 'ECE_OUT_FILE_PATH',
115 cOutput_path );
116 ec_debug.pl ( 3, 'cOutput_Path: ',cOutput_Path );
117
118
119
120
121
122
123 xProgress := 'PYO-10-1030';
124 ec_debug.pl ( 0, 'EC', 'ECE_PAYMENT_BATCH', 'PAYMENT INSTRUCTION ID ', p_payment_instruction_id );
125
126 fnd_file.put_line(fnd_file.log, 'Calling EC Process Outbound :: START ::'||systimestamp);
127
128 ec_document.process_outbound(
129 errbuf => p_errbuf,
130 retcode => p_retcode,
131 i_Output_Path => cOutput_Path,
132 i_Output_Filename => i_Filename,
133 i_Transaction_Type => i_Transaction_Type,
134 i_debug_mode => p_debug_mode,
135 parameter1 => p_payment_instruction_id
136 );
137
138 fnd_file.put_line(fnd_file.log, 'Calling EC Process Outbound :: END ::'||systimestamp);
139
140
141 -- Standard check of p_commit
142 if fnd_api.to_Boolean(p_commit) then
143 COMMIT WORK;
144 end if;
145
146 -- Standard call to get message count and if count is 1, get message info.
147 fnd_msg_pub.count_and_get(
148 p_count => x_msg_count,
149 p_data => x_msg_data);
150
151
152 IF ec_mapping_utils.ec_get_trans_upgrade_status(i_Transaction_Type) = 'U' THEN
153 ec_debug.pl(0,'EC','ECE_REC_TRANS_PENDING',NULL);
154 END IF;
155
156 delete_from_gt;
157 ec_debug.pop ( 'ECE_AP_TRANSACTION.Extract_PYO_Outbound' );
158 ec_debug.disable_debug;
159 EXCEPTION
160 WHEN fnd_api.g_exc_error THEN
161 ROLLBACK;
162 fnd_file.put_line(fnd_file.log, 'EXCEPTION :: END ::'||systimestamp);
163 x_return_status := fnd_api.g_ret_sts_error;
164 fnd_msg_pub.count_and_get(p_count => x_msg_count,
165 p_data => x_msg_data);
166 ec_debug.disable_debug;
167 WHEN fnd_api.g_exc_unexpected_error THEN
168 ROLLBACK;
169 fnd_file.put_line(fnd_file.log, 'EXCEPTION :: END ::'||systimestamp);
170 x_return_status := fnd_api.g_ret_sts_unexp_error;
171 fnd_msg_pub.count_and_get(p_count => x_msg_count,
172 p_data => x_msg_data);
173 ec_debug.disable_debug;
174 WHEN OTHERS THEN
175 ROLLBACK;
176 fnd_file.put_line(fnd_file.log, 'EXCEPTION :: END ::'||systimestamp);
177 x_return_status := fnd_api.g_ret_sts_unexp_error;
178 IF fnd_msg_pub.Check_Msg_Level
179 (fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
180 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
181 END IF;
182 fnd_msg_pub.count_and_get(p_count => x_msg_count,
183 p_data => x_msg_data);
184
185 ec_debug.disable_debug;
186 END Extract_PYO_Outbound;
187
188
189 PROCEDURE insert_into_gt(p_payment_instruction_id IN NUMBER)
190 IS
191
192 BEGIN
193 ec_debug.push('ECE_AP_TRANSACTION.insert_into_gt');
194 ec_debug.pl (3, 'Fetching Payment data for Instruction',p_payment_instruction_id);
195 fnd_file.put_line(fnd_file.log, 'Calling insert_into_gt :: START ::'||systimestamp);
196
197 INSERT INTO IBY_PYO_PAYMENT_GT(COMMUNICATION_METHOD,
198 TEST_FLAG,
199 DOCUMENT_ID,
200 DOCUMENT_CODE,
201 BK_TP_TRANSLATOR_CODE,
202 BK_TP_LOCATION_CODE_EXT,
203 BK_TP_DESCRIPTION,
204 BK_TP_REFERENCE_EXT1,
205 BK_TP_REFERENCE_EXT2,
206 TRANSACTION_DATE,
207 TPH_ATTRIBUTE_CATEGORY,
208 TPH_ATTRIBUTE1,
209 TPH_ATTRIBUTE2,
210 TPH_ATTRIBUTE3,
211 TPH_ATTRIBUTE4,
212 TPH_ATTRIBUTE5,
213 TPH_ATTRIBUTE6,
214 TPH_ATTRIBUTE7,
215 TPH_ATTRIBUTE8,
216 TPH_ATTRIBUTE9,
217 TPH_ATTRIBUTE10,
218 TPH_ATTRIBUTE11,
219 TPH_ATTRIBUTE12,
220 TPH_ATTRIBUTE13,
221 TPH_ATTRIBUTE14,
222 TPH_ATTRIBUTE15,
223 TPD_ATTRIBUTE_CATEGORY,
224 TPD_ATTRIBUTE1,
225 TPD_ATTRIBUTE2,
226 TPD_ATTRIBUTE3,
227 TPD_ATTRIBUTE4,
228 TPD_ATTRIBUTE5,
229 DOCUMENT_STANDARD,
230 TRANSACTION_HANDLING_CODE,
231 CHECK_AMOUNT,
232 CURRENCY_CODE,
233 EDI_PAYMENT_METHOD,
234 EDI_PAYMENT_FORMAT,
235 BANK_BRANCH_TYPE,
236 BANK_ACCOUNT_TYPE,
237 BANK_ACCOUNT_NUM,
238 BANK_EDI_ID_NUMBER,
239 VENDOR_BANK_BRANCH_TYPE,
240 VENDOR_BANK_ACCOUNT_TYPE,
241 VENDOR_BANK_ACCOUNT_NUM,
242 PAYMENT_DATE,
243 BANK_NUM,
244 VENDOR_BANK_NUM,
245 EDI_REMITTANCE_METHOD,
246 EDI_REMITTANCE_INSTRUCTION,
247 PAYMENT_INSTRUCTION_ID,
248 CHECK_VOUCHER_NUM,
249 SELECTED_CHECK_ID,
250 CHECK_NUMBER,
251 CUSTOMER_NUM,
252 SEGMENT1,
253 VENDOR_EDI_ID_NUMBER,
254 SEGMENT2,
255 SEGMENT3,
256 SEGMENT4,
257 SEGMENT5,
258 BANK_BRANCH_ID,
259 BK_BANK_BRANCH_NAME,
260 BK_ADDRESS_LINE1,
261 BK_ADDRESS_LINE2,
262 BK_ADDRESS_LINE3,
263 BK_CITY,
264 BK_ZIP,
265 BK_COUNTRY,
266 BK_STATE,
267 BK_PROVINCE,
268 BK_CONTACT_FIRST_NAME,
269 BK_CONTACT_MIDDLE_NAME,
270 BK_CONTACT_LAST_NAME,
271 BK_CONTACT_TITLE,
272 BK_CONTACT_PREFIX,
273 BK_CONTACT_AREA_CODE,
274 BK_CONTACT_PHONE,
275 VENDOR_SITE_CODE,
276 VENDOR_NAME,
277 ADDRESS_LINE1,
278 ADDRESS_LINE2,
279 ADDRESS_LINE3,
280 ADDRESS_LINE4,
281 CITY,
282 ZIP,
283 COUNTRY,
284 STATE,
285 PROVINCE,
286 ABA_GLOBAL_ATTRIBUTE_CATEGORY,
287 ABA_GLOBAL_ATTRIBUTE1,
288 ABA_GLOBAL_ATTRIBUTE2,
289 ABA_GLOBAL_ATTRIBUTE3,
290 ABA_GLOBAL_ATTRIBUTE4,
291 ABA_GLOBAL_ATTRIBUTE5,
292 ABA_GLOBAL_ATTRIBUTE6,
293 ABA_GLOBAL_ATTRIBUTE7,
294 ABA_GLOBAL_ATTRIBUTE8,
295 ABA_GLOBAL_ATTRIBUTE9,
296 ABA_GLOBAL_ATTRIBUTE10,
297 ABA_GLOBAL_ATTRIBUTE11,
298 ABA_GLOBAL_ATTRIBUTE12,
299 ABA_GLOBAL_ATTRIBUTE13,
300 ABA_GLOBAL_ATTRIBUTE14,
301 ABA_GLOBAL_ATTRIBUTE15,
302 ABA_GLOBAL_ATTRIBUTE16,
303 ABA_GLOBAL_ATTRIBUTE17,
304 ABA_GLOBAL_ATTRIBUTE18,
305 ABA_GLOBAL_ATTRIBUTE19,
306 ABA_GLOBAL_ATTRIBUTE20,
307 ABAS_GLOBAL_ATTRIBUTE_CATEGORY,
308 ABAS_GLOBAL_ATTRIBUTE1,
309 ABAS_GLOBAL_ATTRIBUTE2,
310 ABAS_GLOBAL_ATTRIBUTE3,
311 ABAS_GLOBAL_ATTRIBUTE4,
312 ABAS_GLOBAL_ATTRIBUTE5,
313 ABAS_GLOBAL_ATTRIBUTE6,
314 ABAS_GLOBAL_ATTRIBUTE7,
315 ABAS_GLOBAL_ATTRIBUTE8,
316 ABAS_GLOBAL_ATTRIBUTE9,
317 ABAS_GLOBAL_ATTRIBUTE10,
318 ABAS_GLOBAL_ATTRIBUTE11,
319 ABAS_GLOBAL_ATTRIBUTE12,
320 ABAS_GLOBAL_ATTRIBUTE13,
321 ABAS_GLOBAL_ATTRIBUTE14,
322 ABAS_GLOBAL_ATTRIBUTE15,
323 ABAS_GLOBAL_ATTRIBUTE16,
324 ABAS_GLOBAL_ATTRIBUTE17,
325 ABAS_GLOBAL_ATTRIBUTE18,
326 ABAS_GLOBAL_ATTRIBUTE19,
327 ABAS_GLOBAL_ATTRIBUTE20,
328 PVS_GLOBAL_ATTRIBUTE_CATEGORY,
329 PVS_GLOBAL_ATTRIBUTE1,
330 PVS_GLOBAL_ATTRIBUTE2,
331 PVS_GLOBAL_ATTRIBUTE3,
332 PVS_GLOBAL_ATTRIBUTE4,
333 PVS_GLOBAL_ATTRIBUTE5,
334 PVS_GLOBAL_ATTRIBUTE6,
335 PVS_GLOBAL_ATTRIBUTE7,
336 PVS_GLOBAL_ATTRIBUTE8,
337 PVS_GLOBAL_ATTRIBUTE9,
338 PVS_GLOBAL_ATTRIBUTE10,
339 PVS_GLOBAL_ATTRIBUTE11,
340 PVS_GLOBAL_ATTRIBUTE12,
341 PVS_GLOBAL_ATTRIBUTE13,
342 PVS_GLOBAL_ATTRIBUTE14,
343 PVS_GLOBAL_ATTRIBUTE15,
344 PVS_GLOBAL_ATTRIBUTE16,
345 PVS_GLOBAL_ATTRIBUTE17,
346 PVS_GLOBAL_ATTRIBUTE18,
347 PVS_GLOBAL_ATTRIBUTE19,
348 PVS_GLOBAL_ATTRIBUTE20,
349 BK_EFT_SWIFT_CODE,
350 SBK_BANK_BRANCH_NUMBER,
351 SBK_BANK_BRANCH_NAME,
352 SBK_ADDRESS_LINE1,
353 SBK_ADDRESS_LINE2,
354 SBK_ADDRESS_LINE3,
355 SBK_CITY,
356 SBK_STATE,
357 SBK_ZIP,
358 SBK_PROVINCE,
359 SBK_COUNTRY,
360 SBK_EFT_SWIFT_CODE,
361 BILL_TO_INT_LOCATION_NAME,
362 BILL_TO_INT_ADDRESS1,
363 BILL_TO_INT_ADDRESS2,
364 BILL_TO_INT_ADDRESS3,
365 BILL_TO_INT_CITY,
366 BILL_TO_INT_POSTAL_CODE,
367 BILL_TO_INT_COUNTRY,
368 FI_VAT_REGISTRATION_NUM,
369 BILL_TO_INT_LOCATION_ID,
370 ECE_TP_LOCATION_CODE,
371 BILL_TO_INT_REGION1,
372 BILL_TO_INT_REGION2,
373 BILL_TO_INT_REGION3,
374 MAP_ID,
375 FUTURE_PAY_DUE_DATE,
376 VENDOR_ALTERNATE_NAME,
377 VENDOR_ALTERNATE_SITE_CODE,
378 SUPPLIER_CHECK_DIGITS,
379 SBK_BANK_NAME,
380 IBAN_NUMBER,
381 VENDOR_IBAN_NUMBER,
382 PAYMENT_MEAN,
383 PAYMENT_CHANNEL,
384 VENDOR_ID,
385 VENDOR_SITE_ID,
386 PAYMENT_REFERENCE_NUMBER,
387 SBK_BANK_CODE,
388 PAYMENT_PROCESS_REQUEST_NAME,
389 PAYMENT_ORG_ID,
390 BANK_NAME,
391 BRANCH_NUMBER,
392 BANK_ACCOUNT_ID,
393 BANK_ACCOUNT_NAME,
394 BK_ACCT_ATTRIBUTE_CATEGORY,
395 BK_ACCT_ATTRIBUTE1,
396 BK_ACCT_ATTRIBUTE2,
397 BK_ACCT_ATTRIBUTE3,
398 BK_ACCT_ATTRIBUTE4,
399 BK_ACCT_ATTRIBUTE5,
400 BK_ACCT_ATTRIBUTE6,
401 BK_ACCT_ATTRIBUTE7,
402 BK_ACCT_ATTRIBUTE8,
403 BK_ACCT_ATTRIBUTE9,
404 BK_ACCT_ATTRIBUTE10,
405 BK_ACCT_ATTRIBUTE11,
406 BK_ACCT_ATTRIBUTE12,
407 BK_ACCT_ATTRIBUTE13,
408 BK_ACCT_ATTRIBUTE14,
409 BK_ACCT_ATTRIBUTE15,
410 VENDOR_BANK_ACCOUNT_ID,
411 SBK_ACCT_ATTRIBUTE_CATEGORY,
412 SBK_ACCT_ATTRIBUTE1,
413 SBK_ACCT_ATTRIBUTE2,
414 SBK_ACCT_ATTRIBUTE3,
415 SBK_ACCT_ATTRIBUTE4,
416 SBK_ACCT_ATTRIBUTE5,
417 SBK_ACCT_ATTRIBUTE6,
418 SBK_ACCT_ATTRIBUTE7,
419 SBK_ACCT_ATTRIBUTE8,
420 SBK_ACCT_ATTRIBUTE9,
421 SBK_ACCT_ATTRIBUTE10,
422 SBK_ACCT_ATTRIBUTE11,
423 SBK_ACCT_ATTRIBUTE12,
424 SBK_ACCT_ATTRIBUTE13,
425 SBK_ACCT_ATTRIBUTE14,
426 SBK_ACCT_ATTRIBUTE15,
427 VEND_SITE_COUNTY,
428 VEND_SITE_ATTRIBUTE_CATEGORY,
429 VEND_SITE_ATTRIBUTE1,
430 VEND_SITE_ATTRIBUTE2,
431 VEND_SITE_ATTRIBUTE3,
432 VEND_SITE_ATTRIBUTE4,
433 VEND_SITE_ATTRIBUTE5,
434 VEND_SITE_ATTRIBUTE6,
435 VEND_SITE_ATTRIBUTE7,
436 VEND_SITE_ATTRIBUTE8,
437 VEND_SITE_ATTRIBUTE9,
438 VEND_SITE_ATTRIBUTE10,
439 VEND_SITE_ATTRIBUTE11,
440 VEND_SITE_ATTRIBUTE12,
441 VEND_SITE_ATTRIBUTE13,
442 VEND_SITE_ATTRIBUTE14,
443 VEND_SITE_ATTRIBUTE15,
444 VENDOR_TYPE_LOOKUP_CODE,
445 VENDOR_ATTRIBUTE_CATEGORY,
446 VENDOR_ATTRIBUTE1,
447 VENDOR_ATTRIBUTE2,
448 VENDOR_ATTRIBUTE3,
449 VENDOR_ATTRIBUTE4,
450 VENDOR_ATTRIBUTE5,
451 VENDOR_ATTRIBUTE6,
452 VENDOR_ATTRIBUTE7,
453 VENDOR_ATTRIBUTE8,
454 VENDOR_ATTRIBUTE9,
455 VENDOR_ATTRIBUTE10,
456 VENDOR_ATTRIBUTE11,
457 VENDOR_ATTRIBUTE12,
458 VENDOR_ATTRIBUTE13,
459 VENDOR_ATTRIBUTE14,
460 VENDOR_ATTRIBUTE15
461 )
462 SELECT 'EDI' communication_method ,
463 bktpd.test_flag test_flag ,
464 'PYO' document_id ,
465 ipay.payment_id document_code ,
466 bktpd.translator_code bk_tp_translator_code ,
467 hcp.edi_ece_tp_location_code bk_tp_location_code_ext ,
468 bktph.tp_description bk_tp_description ,
469 bktph.tp_reference_ext1 bk_tp_reference_ext1 ,
470 bktph.tp_reference_ext2 bk_tp_reference_ext2 ,
471 sysdate transaction_date ,
472 bktph.attribute_category tph_attribute_category ,
473 bktph.attribute1 tph_attribute1 ,
474 bktph.attribute2 tph_attribute2 ,
475 bktph.attribute3 tph_attribute3 ,
476 bktph.attribute4 tph_attribute4 ,
477 bktph.attribute5 tph_attribute5 ,
478 bktph.attribute6 tph_attribute6 ,
479 bktph.attribute7 tph_attribute7 ,
480 bktph.attribute8 tph_attribute8 ,
481 bktph.attribute9 tph_attribute9 ,
482 bktph.attribute10 tph_attribute10 ,
483 bktph.attribute11 tph_attribute11 ,
484 bktph.attribute12 tph_attribute12 ,
485 bktph.attribute13 tph_attribute13 ,
486 bktph.attribute14 tph_attribute14 ,
487 bktph.attribute15 tph_attribute15 ,
488 bktpd.attribute_category tpd_attribute_category ,
489 bktpd.attribute1 tpd_attribute1 ,
490 bktpd.attribute2 tpd_attribute2 ,
491 bktpd.attribute3 tpd_attribute3 ,
492 bktpd.attribute4 tpd_attribute4 ,
493 bktpd.attribute5 tpd_attribute5 ,
494 bktpd.document_standard document_standard ,
495 ipay.bank_instruction2_code transaction_handling_code ,
496 ipay.payment_amount check_amount ,
497 ipay.payment_currency_code currency_code ,
498 ipay.payment_method_code edi_payment_method ,
499 ipay.bank_instruction1_code edi_payment_format ,
500 hca1.class_code bank_branch_type ,
501 cba.bank_account_type bank_account_type ,
502 ipay.int_bank_account_number bank_account_num ,
503 hcp.edi_id_number bank_edi_id_number ,
504 hca.class_code vendor_bank_branch_type ,
505 ipay.ext_bank_account_type vendor_bank_account_type ,
506 ipay.ext_bank_account_number vendor_bank_account_num ,
507 ipay.payment_date payment_date ,
508 ipay.int_bank_branch_number bank_num , -- Bug 9365065
509 ipay.ext_branch_number vendor_bank_num , -- Bug 9365065
510 ipay.delivery_channel_code edi_remittance_method ,
511 ipay.payment_text_message1 edi_remittance_instruction ,
512 ipay.payment_instruction_id payment_instruction_id ,
513 NULL check_voucher_num ,
514 NULL selected_check_id ,
515 nvl(ipay.paper_document_number, ipay.payment_reference_number) check_number, -- Bug 9365065
516 aps.customer_num customer_num ,
517 aps.segment1 segment1 ,
518 apss.edi_id_number vendor_edi_id_number ,
519 aps.segment2 segment2 ,
520 aps.segment3 segment3 ,
521 aps.segment4 segment4 ,
522 aps.segment5 segment5 ,
523 ipay.int_bank_branch_party_id bank_branch_id ,
524 ipay.int_bank_branch_name bk_bank_branch_name ,
525 ibl.address1 bk_address_line1 ,
526 ibl.address2 bk_address_line2 ,
527 ibl.address3 bk_address_line3 ,
528 ibl.city bk_city ,
529 ibl.postal_code bk_zip ,
530 ibl.country bk_country ,
531 ibl.state bk_state ,
532 ibl.province bk_province ,
533 NULL bk_contact_first_name ,
534 NULL bk_contact_middle_name ,
535 NULL bk_contact_last_name ,
536 NULL bk_contact_title ,
537 NULL bk_contact_prefix ,
538 NULL bk_contact_area_code ,
539 NULL bk_contact_phone ,
540 apss.vendor_site_code vendor_site_code ,
541 ipay.payee_name vendor_name ,
542 ipay.payee_address1 address_line1 ,
543 ipay.payee_address2 address_line2 ,
544 ipay.payee_address3 address_line3 ,
545 ipay.payee_address4 address_line4 ,
546 ipay.payee_city city ,
547 ipay.payee_postal_code zip ,
548 ipay.payee_country country ,
549 ipay.payee_state state ,
550 ipay.payee_province province ,
551 NULL aba_global_attribute_category ,
552 NULL aba_global_attribute1 ,
553 NULL aba_global_attribute2 ,
554 NULL aba_global_attribute3 ,
555 NULL aba_global_attribute4 ,
556 NULL aba_global_attribute5 ,
557 NULL aba_global_attribute6 ,
558 NULL aba_global_attribute7 ,
559 NULL aba_global_attribute8 ,
560 NULL aba_global_attribute9 ,
561 NULL aba_global_attribute10 ,
562 NULL aba_global_attribute11 ,
563 NULL aba_global_attribute12 ,
564 NULL aba_global_attribute13 ,
565 NULL aba_global_attribute14 ,
566 NULL aba_global_attribute15 ,
567 NULL aba_global_attribute16 ,
568 NULL aba_global_attribute17 ,
569 NULL aba_global_attribute18 ,
570 NULL aba_global_attribute19 ,
571 NULL aba_global_attribute20 ,
572 NULL abas_global_attribute_category ,
573 NULL abas_global_attribute1 ,
574 NULL abas_global_attribute2 ,
575 ipay.payee_country abas_global_attribute3 ,
576 NULL abas_global_attribute4 ,
577 ipay.payment_reason_code abas_global_attribute5 ,
578 NULL abas_global_attribute6 ,
579 NULL abas_global_attribute7 ,
580 ipay.bank_charge_bearer abas_global_attribute8 ,
581 NULL abas_global_attribute9 ,
582 NULL abas_global_attribute10 ,
583 NULL abas_global_attribute11 ,
584 NULL abas_global_attribute12 ,
585 NULL abas_global_attribute13 ,
586 NULL abas_global_attribute14 ,
587 NULL abas_global_attribute15 ,
588 NULL abas_global_attribute16 ,
589 NULL abas_global_attribute17 ,
590 NULL abas_global_attribute18 ,
591 NULL abas_global_attribute19 ,
592 NULL abas_global_attribute20 ,
593 apss.global_attribute_category pvs_global_attribute_category,
594 apss.global_attribute1 pvs_global_attribute1 ,
595 apss.global_attribute2 pvs_global_attribute2 ,
596 apss.global_attribute3 pvs_global_attribute3 ,
597 apss.global_attribute4 pvs_global_attribute4 ,
598 apss.global_attribute5 pvs_global_attribute5 ,
599 apss.global_attribute6 pvs_global_attribute6 ,
600 apss.global_attribute7 pvs_global_attribute7 ,
601 apss.global_attribute8 pvs_global_attribute8 ,
602 apss.global_attribute9 pvs_global_attribute9 ,
603 apss.global_attribute10 pvs_global_attribute10 ,
604 apss.global_attribute11 pvs_global_attribute11 ,
605 apss.global_attribute12 pvs_global_attribute12 ,
606 apss.global_attribute13 pvs_global_attribute13 ,
607 apss.global_attribute14 pvs_global_attribute14 ,
608 apss.global_attribute15 pvs_global_attribute15 ,
609 apss.global_attribute16 pvs_global_attribute16 ,
610 apss.global_attribute17 pvs_global_attribute17 ,
611 apss.global_attribute18 pvs_global_attribute18 ,
612 apss.global_attribute19 pvs_global_attribute19 ,
613 apss.global_attribute20 pvs_global_attribute20 ,
614 ipay.int_eft_swift_code bk_eft_swift_code ,
615 ipay.ext_branch_number sbk_bank_branch_number ,
616 ipay.ext_bank_branch_name sbk_bank_branch_name ,
617 ebl.address1 sbk_address_line1 ,
618 ebl.address2 sbk_address_line2 ,
619 ebl.address3 sbk_address_line3 ,
620 ebl.city sbk_city ,
621 ebl.state sbk_state ,
622 ebl.postal_code sbk_zip ,
623 ebl.province sbk_province ,
624 ebl.country sbk_country ,
625 ipay.ext_eft_swift_code sbk_eft_swift_code ,
626 hr.location_code bill_to_int_location_name ,
627 hr.address_line_1 bill_to_int_address1 ,
628 hr.address_line_2 bill_to_int_address2 ,
629 hr.address_line_3 bill_to_int_address3 ,
630 hr.town_or_city bill_to_int_city ,
631 hr.postal_code bill_to_int_postal_code ,
632 hr.country bill_to_int_country ,
633 ipay.payer_tax_registration_num fi_vat_registration_num ,
634 hr.location_id bill_to_int_location_id ,
635 hr.ece_tp_location_code ece_tp_location_code ,
636 hr.region_1 bill_to_int_region1 ,
637 hr.region_2 bill_to_int_region2 ,
638 hr.region_3 bill_to_int_region3 ,
639 bktpd.map_id map_id ,
640 ipay.payment_due_date future_pay_due_date ,
641 aps.vendor_name_alt vendor_alternate_name ,
642 apss.vendor_site_code_alt vendor_alternate_site_code ,
643 ipay.uri_check_digit supplier_check_digits ,
644 ipay.ext_bank_name sbk_bank_name ,
645 ipay.int_bank_account_iban iban_number ,
646 ipay.ext_bank_account_iban_number vendor_iban_number ,
647 ipm.attribute1 payment_mean ,
648 ipm.attribute2 payment_channel ,
649 aps.vendor_id vendor_id ,
650 apss.vendor_site_id vendor_site_id ,
651 ipay.payment_reference_number payment_reference_number ,
652 bapr.bank_code sbk_bank_code ,
653 ipay.PAYMENT_PROCESS_REQUEST_NAME PAYMENT_PROCESS_REQUEST_NAME,
654 ipay.ORG_ID PAYMENT_ORG_ID ,
655 ipay.INT_BANK_NAME BANK_NAME ,
656 ipay.INT_BANK_BRANCH_NUMBER BRANCH_NUMBER ,
657 cba.BANK_ACCOUNT_ID BANK_ACCOUNT_ID ,
658 cba.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME ,
659 cba.ATTRIBUTE_CATEGORY BK_ACCT_ATTRIBUTE_CATEGORY ,
660 cba.ATTRIBUTE1 BK_ACCT_ATTRIBUTE1 ,
661 cba.ATTRIBUTE2 BK_ACCT_ATTRIBUTE2 ,
662 cba.ATTRIBUTE3 BK_ACCT_ATTRIBUTE3 ,
663 cba.ATTRIBUTE4 BK_ACCT_ATTRIBUTE4 ,
664 cba.ATTRIBUTE5 BK_ACCT_ATTRIBUTE5 ,
665 cba.ATTRIBUTE6 BK_ACCT_ATTRIBUTE6 ,
666 cba.ATTRIBUTE7 BK_ACCT_ATTRIBUTE7 ,
667 cba.ATTRIBUTE8 BK_ACCT_ATTRIBUTE8 ,
668 cba.ATTRIBUTE9 BK_ACCT_ATTRIBUTE9 ,
669 cba.ATTRIBUTE10 BK_ACCT_ATTRIBUTE10 ,
670 cba.ATTRIBUTE11 BK_ACCT_ATTRIBUTE11 ,
671 cba.ATTRIBUTE12 BK_ACCT_ATTRIBUTE12 ,
672 cba.ATTRIBUTE13 BK_ACCT_ATTRIBUTE13 ,
673 cba.ATTRIBUTE14 BK_ACCT_ATTRIBUTE14 ,
674 cba.ATTRIBUTE15 BK_ACCT_ATTRIBUTE15 ,
675 ieba.EXT_BANK_ACCOUNT_ID VENDOR_BANK_ACCOUNT_ID ,
676 ieba.ATTRIBUTE_CATEGORY SBK_ACCT_ATTRIBUTE_CATEGORY ,
677 ieba.ATTRIBUTE1 SBK_ACCT_ATTRIBUTE1 ,
678 ieba.ATTRIBUTE2 SBK_ACCT_ATTRIBUTE2 ,
679 ieba.ATTRIBUTE3 SBK_ACCT_ATTRIBUTE3 ,
680 ieba.ATTRIBUTE4 SBK_ACCT_ATTRIBUTE4 ,
681 ieba.ATTRIBUTE5 SBK_ACCT_ATTRIBUTE5 ,
682 ieba.ATTRIBUTE6 SBK_ACCT_ATTRIBUTE6 ,
683 ieba.ATTRIBUTE7 SBK_ACCT_ATTRIBUTE7 ,
684 ieba.ATTRIBUTE8 SBK_ACCT_ATTRIBUTE8 ,
685 ieba.ATTRIBUTE9 SBK_ACCT_ATTRIBUTE9 ,
686 ieba.ATTRIBUTE10 SBK_ACCT_ATTRIBUTE10 ,
687 ieba.ATTRIBUTE11 SBK_ACCT_ATTRIBUTE11 ,
688 ieba.ATTRIBUTE12 SBK_ACCT_ATTRIBUTE12 ,
689 ieba.ATTRIBUTE13 SBK_ACCT_ATTRIBUTE13 ,
690 ieba.ATTRIBUTE14 SBK_ACCT_ATTRIBUTE14 ,
691 ieba.ATTRIBUTE15 SBK_ACCT_ATTRIBUTE15 ,
692 apss.COUNTY VEND_SITE_COUNTY ,
693 apss.ATTRIBUTE_CATEGORY VEND_SITE_ATTRIBUTE_CATEGORY,
694 apss.ATTRIBUTE1 VEND_SITE_ATTRIBUTE1 ,
695 apss.ATTRIBUTE2 VEND_SITE_ATTRIBUTE2 ,
696 apss.ATTRIBUTE3 VEND_SITE_ATTRIBUTE3 ,
697 apss.ATTRIBUTE4 VEND_SITE_ATTRIBUTE4 ,
698 apss.ATTRIBUTE5 VEND_SITE_ATTRIBUTE5 ,
699 apss.ATTRIBUTE6 VEND_SITE_ATTRIBUTE6 ,
700 apss.ATTRIBUTE7 VEND_SITE_ATTRIBUTE7 ,
701 apss.ATTRIBUTE8 VEND_SITE_ATTRIBUTE8 ,
702 apss.ATTRIBUTE9 VEND_SITE_ATTRIBUTE9 ,
703 apss.ATTRIBUTE10 VEND_SITE_ATTRIBUTE10 ,
704 apss.ATTRIBUTE11 VEND_SITE_ATTRIBUTE11 ,
705 apss.ATTRIBUTE12 VEND_SITE_ATTRIBUTE12 ,
706 apss.ATTRIBUTE13 VEND_SITE_ATTRIBUTE13 ,
707 apss.ATTRIBUTE14 VEND_SITE_ATTRIBUTE14 ,
708 apss.ATTRIBUTE15 VEND_SITE_ATTRIBUTE15 ,
709 aps.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE_LOOKUP_CODE ,
710 aps.ATTRIBUTE_CATEGORY VENDOR_ATTRIBUTE_CATEGORY ,
711 aps.ATTRIBUTE1 VENDOR_ATTRIBUTE1 ,
712 aps.ATTRIBUTE2 VENDOR_ATTRIBUTE2 ,
713 aps.ATTRIBUTE3 VENDOR_ATTRIBUTE3 ,
714 aps.ATTRIBUTE4 VENDOR_ATTRIBUTE4 ,
715 aps.ATTRIBUTE5 VENDOR_ATTRIBUTE5 ,
716 aps.ATTRIBUTE6 VENDOR_ATTRIBUTE6 ,
717 aps.ATTRIBUTE7 VENDOR_ATTRIBUTE7 ,
718 aps.ATTRIBUTE8 VENDOR_ATTRIBUTE8 ,
719 aps.ATTRIBUTE9 VENDOR_ATTRIBUTE9 ,
720 aps.ATTRIBUTE10 VENDOR_ATTRIBUTE10 ,
721 aps.ATTRIBUTE11 VENDOR_ATTRIBUTE11 ,
722 aps.ATTRIBUTE12 VENDOR_ATTRIBUTE12 ,
723 aps.ATTRIBUTE13 VENDOR_ATTRIBUTE13 ,
724 aps.ATTRIBUTE14 VENDOR_ATTRIBUTE14 ,
725 aps.ATTRIBUTE15 VENDOR_ATTRIBUTE15
726 FROM iby_payments_all ipay,
727 ce_bank_accounts cba ,
728 ap_suppliers aps ,
729 ap_supplier_sites apss ,
730 hz_contact_points hcp ,
731 ece_tp_headers bktph ,
732 ece_tp_details bktpd ,
733 hr_locations hr ,
734 hz_locations ebl ,
735 hz_code_assignments hca ,
736 hz_locations ibl ,
737 hz_code_assignments hca1 ,
738 iby_payment_methods_b ipm ,
739 iby_ext_bank_accounts ieba ,
740 (SELECT party_id ,
741 bank_code
742 FROM hz_organization_profiles
743 WHERE sysdate >= effective_start_date
744 AND(effective_end_date IS NULL
745 OR effective_end_date > sysdate)
746 ) bapr,
747 financials_system_params_all fi
748 WHERE apss.vendor_site_id(+) = ipay.supplier_site_id
749 AND cba.bank_account_id = ipay.internal_bank_account_id
750 AND apss.vendor_id = aps.vendor_id(+)
751 AND bktph.tp_header_id = hcp.edi_tp_header_id
752 AND bktpd.tp_header_id = bktph.tp_header_id
753 AND bktpd.document_id = 'PYO'
754 AND hcp.owner_table_id = ipay.int_bank_branch_party_id
755 AND hcp.owner_table_name = 'HZ_PARTIES'
756 AND hcp.contact_point_type = 'EDI'
757 AND bktpd.edi_flag = 'Y'
758 AND ipay.org_id = fi.org_id
759 AND hr.location_id = fi.bill_to_location_id
760 AND ebl.location_id(+) = ipay.ext_bank_branch_location_id
761 AND hca.owner_table_id(+) = ipay.ext_bank_branch_party_id
762 AND hca.owner_table_name(+) = 'HZ_PARTIES'
763 AND hca.class_category(+) = 'BANK_BRANCH_TYPE'
764 AND hca.status(+) = 'A'
765 AND sysdate BETWEEN NVL(hca.start_date_active(+), sysdate -1) AND NVL(hca.end_date_active(+), sysdate + 1)
766 AND ibl.location_id(+) = ipay.int_bank_branch_location_id
767 AND hca1.owner_table_id(+) = ipay.int_bank_branch_party_id
768 AND hca1.owner_table_name(+) = 'HZ_PARTIES'
769 AND hca1.class_category(+) = 'BANK_BRANCH_TYPE'
770 AND hca1.status(+) = 'A'
771 AND sysdate BETWEEN NVL(hca1.start_date_active(+), sysdate -1) AND NVL(hca1.end_date_active(+), sysdate + 1)
772 AND ipay.payment_method_code = ipm.payment_method_code
773 AND ipay.external_bank_account_id = ieba.ext_bank_account_id(+)
774 AND ieba.branch_id = bapr.party_id(+)
775 AND ipay.payment_instruction_id = p_payment_instruction_id
776 AND (ipay.payment_status = 'INSTRUCTION_CREATED'
777 OR (ipay.payment_status = 'FORMATTED' AND ipay.process_type = 'IMMEDIATE'));
778
779 ec_debug.pl (3, 'Finished fetching Payment data');
780 ec_debug.pl (3, 'Fetching Invoice data');
781 fnd_file.put_line(fnd_file.log, 'Finished fetching Payment Data ::'||systimestamp);
782
783 INSERT INTO IBY_PYO_INVOICE_GT(PAYMENT_ID,
784 PAYMENT_INSTRUCTION_ID,
785 PAY_SELECTED_CHECK_ID,
786 VENDOR_NUM,
787 CUSTOMER_NUM,
788 INVOICE_NUM,
789 INVOICE_DATE,
790 INVOICE_DESCRIPTION,
791 PROPOSED_PAYMENT_AMOUNT,
792 INVOICE_AMOUNT,
793 DISCOUNT_AMOUNT,
794 PRINT_SELECTED_CHECK_ID,
795 ATTRIBUTE_CATEGORY,
796 ATTRIBUTE1,
797 ATTRIBUTE2,
798 ATTRIBUTE3,
799 ATTRIBUTE4,
800 ATTRIBUTE5,
801 ATTRIBUTE6,
802 ATTRIBUTE7,
803 ATTRIBUTE8,
804 ATTRIBUTE9,
805 ATTRIBUTE10,
806 ATTRIBUTE11,
807 ATTRIBUTE12,
808 ATTRIBUTE13,
809 ATTRIBUTE14,
810 ATTRIBUTE15,
811 INV_GLOBAL_ATTRIBUTE_CATEGORY,
812 INV_GLOBAL_ATTRIBUTE1,
813 INV_GLOBAL_ATTRIBUTE2,
814 INV_GLOBAL_ATTRIBUTE3,
815 INV_GLOBAL_ATTRIBUTE4,
816 INV_GLOBAL_ATTRIBUTE5,
817 INV_GLOBAL_ATTRIBUTE6,
818 INV_GLOBAL_ATTRIBUTE7,
819 INV_GLOBAL_ATTRIBUTE8,
820 INV_GLOBAL_ATTRIBUTE9,
821 INV_GLOBAL_ATTRIBUTE10,
822 INV_GLOBAL_ATTRIBUTE11,
823 INV_GLOBAL_ATTRIBUTE12,
824 INV_GLOBAL_ATTRIBUTE13,
825 INV_GLOBAL_ATTRIBUTE14,
826 INV_GLOBAL_ATTRIBUTE15,
827 INV_GLOBAL_ATTRIBUTE16,
828 INV_GLOBAL_ATTRIBUTE17,
829 INV_GLOBAL_ATTRIBUTE18,
830 INV_GLOBAL_ATTRIBUTE19,
831 INV_GLOBAL_ATTRIBUTE20,
832 BANK_CHARGE_BEARER,
833 PAYMENT_REASON_CODE,
834 PAYMENT_REASON_COMMENTS,
835 REMITTANCE_MESSAGE1,
836 REMITTANCE_MESSAGE2,
837 REMITTANCE_MESSAGE3,
838 UNIQUE_REMITTANCE_IDENTIFIER,
839 URI_CHECK_DIGIT,
840 DELIVERY_CHANNEL_CODE,
841 SETTLEMENT_PRIORITY,
842 EXTERNAL_BANK_ACCOUNT_ID,
843 PAYMENT_METHOD_CODE,
844 VENDOR_ID,
845 VENDOR_SITE_ID,
846 PAYMENT_MEAN,
847 PAYMENT_CHANNEL,
848 COUNTRY,
849 PAYMENT_PROCESS_REQUEST_NAME,
850 BANK_ACCOUNT_ID,
851 VENDOR_BANK_ACCOUNT_ID,
852 PAYMENT_REFERENCE_NUMBER,
853 CHECK_NUMBER,
854 AMOUNT_WITHHELD,
855 CALLING_APP_ID,
856 CALLING_APP_DOC_UNIQUE_REF1,
857 CALLING_APP_DOC_UNIQUE_REF2,
858 CALLING_APP_DOC_UNIQUE_REF3,
859 CALLING_APP_DOC_UNIQUE_REF4,
860 CALLING_APP_DOC_UNIQUE_REF5,
861 document_sequence_id,
862 document_sequence_value,
863 document_category_code
864 )
865 SELECT idpa.payment_id payment_id
866 , ipa.payment_instruction_id payment_instruction_id
867 , NULL pay_selected_check_id
868 , ipa.payee_supplier_number vendor_num
869 , NULL customer_num
870 , idpa.calling_app_doc_ref_number invoice_num
871 , idpa.document_date invoice_date
872 , idpa.document_description invoice_description
873 , idpa.payment_amount proposed_payment_amount
874 , idpa.document_amount invoice_amount
875 , idpa.PAYMENT_CURR_DISCOUNT_TAKEN
876 , NULL print_selected_check_id
877 , idpa.attribute_category attribute_category
878 , idpa.attribute1 attribute1
879 , idpa.attribute2 attribute2
880 , idpa.attribute3 attribute3
881 , idpa.attribute4 attribute4
882 , idpa.attribute5 attribute5
883 , idpa.attribute6 attribute6
884 , idpa.attribute7 attribute7
885 , idpa.attribute8 attribute8
886 , idpa.attribute9 attribute9
887 , idpa.attribute10 attribute10
888 , idpa.attribute11 attribute11
889 , idpa.attribute12 attribute12
890 , idpa.attribute13 attribute13
891 , idpa.attribute14 attribute14
892 , idpa.attribute15 attribute15
893 , idpa.global_attribute_category inv_global_attribute_category
894 , idpa.global_attribute1 inv_global_attribute1
895 , idpa.global_attribute2 inv_global_attribute2
896 , idpa.global_attribute3 inv_global_attribute3
897 , idpa.global_attribute4 inv_global_attribute4
898 , idpa.global_attribute5 inv_global_attribute5
899 , idpa.global_attribute6 inv_global_attribute6
900 , idpa.global_attribute7 inv_global_attribute7
901 , idpa.global_attribute8 inv_global_attribute8
902 , idpa.global_attribute9 inv_global_attribute9
903 , idpa.global_attribute10 inv_global_attribute10
904 , idpa.global_attribute11 inv_global_attribute11
905 , idpa.global_attribute12 inv_global_attribute12
906 , idpa.global_attribute13 inv_global_attribute13
907 , idpa.global_attribute14 inv_global_attribute14
908 , idpa.global_attribute15 inv_global_attribute15
909 , idpa.global_attribute16 inv_global_attribute16
910 , idpa.global_attribute17 inv_global_attribute17
911 , idpa.global_attribute18 inv_global_attribute18
912 , idpa.global_attribute19 inv_global_attribute19
913 , idpa.global_attribute20 inv_global_attribute20
914 , idpa.bank_charge_bearer bank_charge_bearer
915 , idpa.payment_reason_code payment_reason_code
916 , idpa.payment_reason_comments payment_reason_comments
917 , idpa.remittance_message1 remittance_message1
918 , idpa.remittance_message2 remittance_message2
919 , idpa.remittance_message3 remittance_message3
920 , idpa.unique_remittance_identifier unique_remittance_identifier
921 , idpa.uri_check_digit uri_check_digit
922 , idpa.delivery_channel_code delivery_channel_code
923 , idpa.settlement_priority settlement_priority
924 , idpa.external_bank_account_id external_bank_account_id
925 , idpa.payment_method_code payment_method_code
926 , ipa.payee_supplier_id vendor_id
927 , ipa.supplier_site_id vendor_site_id
928 , ipm.attribute1 payment_mean
929 , ipm.attribute2 payment_channel
930 , ipa.payee_country country
931 , ipa.PAYMENT_PROCESS_REQUEST_NAME
932 , ipa.INTERNAL_BANK_ACCOUNT_ID
933 , ipa.EXTERNAL_BANK_ACCOUNT_ID
934 , ipa.PAYMENT_REFERENCE_NUMBER
935 , ipa.PAPER_DOCUMENT_NUMBER
936 , idpa.AMOUNT_WITHHELD
937 , idpa.CALLING_APP_ID
938 , idpa.CALLING_APP_DOC_UNIQUE_REF1
939 , idpa.CALLING_APP_DOC_UNIQUE_REF2
940 , idpa.CALLING_APP_DOC_UNIQUE_REF3
941 , idpa.CALLING_APP_DOC_UNIQUE_REF4
942 , idpa.CALLING_APP_DOC_UNIQUE_REF5
943 , idpa.document_sequence_id
944 , idpa.document_sequence_value
945 , idpa.document_category_code
946 FROM iby_docs_payable_all idpa
947 , iby_payments_all ipa
948 , iby_payment_methods_b ipm
949 WHERE idpa.payment_id = ipa.payment_id
950 AND idpa.payment_method_code = ipm.payment_method_code
951 AND ipa.payment_instruction_id = p_payment_instruction_id
952 AND idpa.document_status in ('PAYMENT_CREATED');
953
954
955 ec_debug.pop ( 'ECE_AP_TRANSACTION.insert_into_gt');
956 fnd_file.put_line(fnd_file.log, 'Calling insert_into_gt :: END ::'||systimestamp);
957 END insert_into_gt;
958
959
960 PROCEDURE delete_from_gt IS
961 BEGIN
962 ec_debug.push('ECE_AP_TRANSACTION.delete_from_gt');
963 fnd_file.put_line(fnd_file.log, 'Calling delete_from_gt :: START ::'||systimestamp);
964 delete from IBY_PYO_PAYMENT_GT;
965 delete from IBY_PYO_INVOICE_GT;
966 fnd_file.put_line(fnd_file.log, 'Calling delete_from_gt :: END ::'||systimestamp);
967 ec_debug.pop ( 'ECE_AP_TRANSACTION.delete_from_gt');
968 END delete_from_gt;
969
970 END IBY_PYO_TRANSACTION;
971