VIEW: APPS.ECE_PYO_PAYMENT_V

Source

View Text - Preformatted

SELECT 'EDI' COMMUNICATION_METHOD, BKTPD.TEST_FLAG TEST_FLAG, 'PYO' DOCUMENT_ID, IPAY.PAYMENT_ID DOCUMENT_CODE, BKTPD.TRANSLATOR_CODE BK_TP_TRANSLATOR_CODE, HCP.EDI_ECE_TP_LOCATION_CODE BK_TP_LOCATION_CODE_EXT, BKTPH.TP_DESCRIPTION BK_TP_DESCRIPTION, BKTPH.TP_REFERENCE_EXT1 BK_TP_REFERENCE_EXT1, BKTPH.TP_REFERENCE_EXT2 BK_TP_REFERENCE_EXT2, SYSDATE TRANSACTION_DATE, BKTPH.ATTRIBUTE_CATEGORY TPH_ATTRIBUTE_CATEGORY, BKTPH.ATTRIBUTE1 TPH_ATTRIBUTE1, BKTPH.ATTRIBUTE2 TPH_ATTRIBUTE2, BKTPH.ATTRIBUTE3 TPH_ATTRIBUTE3, BKTPH.ATTRIBUTE4 TPH_ATTRIBUTE4, BKTPH.ATTRIBUTE5 TPH_ATTRIBUTE5, BKTPH.ATTRIBUTE6 TPH_ATTRIBUTE6, BKTPH.ATTRIBUTE7 TPH_ATTRIBUTE7, BKTPH.ATTRIBUTE8 TPH_ATTRIBUTE8, BKTPH.ATTRIBUTE9 TPH_ATTRIBUTE9, BKTPH.ATTRIBUTE10 TPH_ATTRIBUTE10, BKTPH.ATTRIBUTE11 TPH_ATTRIBUTE11, BKTPH.ATTRIBUTE12 TPH_ATTRIBUTE12, BKTPH.ATTRIBUTE13 TPH_ATTRIBUTE13, BKTPH.ATTRIBUTE14 TPH_ATTRIBUTE14, BKTPH.ATTRIBUTE15 TPH_ATTRIBUTE15, BKTPD.ATTRIBUTE_CATEGORY TPD_ATTRIBUTE_CATEGORY, BKTPD.ATTRIBUTE1 TPD_ATTRIBUTE1, BKTPD.ATTRIBUTE2 TPD_ATTRIBUTE2, BKTPD.ATTRIBUTE3 TPD_ATTRIBUTE3, BKTPD.ATTRIBUTE4 TPD_ATTRIBUTE4, BKTPD.ATTRIBUTE5 TPD_ATTRIBUTE5, BKTPD.DOCUMENT_STANDARD DOCUMENT_STANDARD, IPAY.BANK_INSTRUCTION1_CODE TRANSACTION_HANDLING_CODE, IPAY.PAYMENT_AMOUNT CHECK_AMOUNT, IPAY.PAYMENT_CURRENCY_CODE CURRENCY_CODE, IPAY.PAYMENT_METHOD_CODE EDI_PAYMENT_METHOD, IPAY.BANK_INSTRUCTION2_CODE EDI_PAYMENT_FORMAT, HCA1.CLASS_CODE BANK_BRANCH_TYPE, CBA.BANK_ACCOUNT_TYPE BANK_ACCOUNT_TYPE, IPAY.INT_BANK_ACCOUNT_NUMBER BANK_ACCOUNT_NUM, HCP.EDI_ID_NUMBER BANK_EDI_ID_NUMBER, HCA.CLASS_CODE VENDOR_BANK_BRANCH_TYPE, IPAY.EXT_BANK_ACCOUNT_TYPE VENDOR_BANK_ACCOUNT_TYPE, IPAY.EXT_BANK_ACCOUNT_NUMBER VENDOR_BANK_ACCOUNT_NUM, IPAY.PAYMENT_DATE PAYMENT_DATE, IPAY.INT_BANK_NUMBER BANK_NUM, IPAY.EXT_BANK_NUMBER VENDOR_BANK_NUM, IPAY.DELIVERY_CHANNEL_CODE EDI_REMITTANCE_METHOD, IPAY.PAYMENT_TEXT_MESSAGE1 EDI_REMITTANCE_INSTRUCTION, IPAY.PAYMENT_INSTRUCTION_ID PAYMENT_INSTRUCTION_ID, NULL CHECK_VOUCHER_NUM, null SELECTED_CHECK_ID, IPAY.PAPER_DOCUMENT_NUMBER CHECK_NUMBER, APS.CUSTOMER_NUM CUSTOMER_NUM, APS.SEGMENT1 SEGMENT1, APSS.EDI_ID_NUMBER VENDOR_EDI_ID_NUMBER, APS.SEGMENT2 SEGMENT2, APS.SEGMENT3 SEGMENT3, APS.SEGMENT4 SEGMENT4, APS.SEGMENT5 SEGMENT5, IPAY.INT_BANK_BRANCH_PARTY_ID BANK_BRANCH_ID, IPAY.INT_BANK_BRANCH_NAME BK_BANK_BRANCH_NAME, IBL.ADDRESS1 BK_ADDRESS_LINE1, IBL.ADDRESS2 BK_ADDRESS_LINE2, IBL.ADDRESS3 BK_ADDRESS_LINE3, IBL.CITY BK_CITY, IBL.POSTAL_CODE BK_ZIP, IBL.COUNTRY BK_COUNTRY, IBL.STATE BK_STATE, IBL.PROVINCE BK_PROVINCE, null BK_CONTACT_FIRST_NAME, null BK_CONTACT_MIDDLE_NAME, null BK_CONTACT_LAST_NAME, null BK_CONTACT_TITLE, null BK_CONTACT_PREFIX, null BK_CONTACT_AREA_CODE, null BK_CONTACT_PHONE, APSS.VENDOR_SITE_CODE VENDOR_SITE_CODE, IPAY.PAYEE_NAME VENDOR_NAME, IPAY.PAYEE_ADDRESS1 ADDRESS_LINE1, IPAY.PAYEE_ADDRESS2 ADDRESS_LINE2, IPAY.PAYEE_ADDRESS3 ADDRESS_LINE3, IPAY.PAYEE_ADDRESS4 ADDRESS_LINE4, IPAY.PAYEE_CITY CITY, IPAY.PAYEE_POSTAL_CODE ZIP, IPAY.PAYEE_COUNTRY COUNTRY, IPAY.PAYEE_STATE STATE, IPAY.PAYEE_PROVINCE PROVINCE, null ABA_GLOBAL_ATTRIBUTE_CATEGORY, null ABA_GLOBAL_ATTRIBUTE1, null ABA_GLOBAL_ATTRIBUTE2, null ABA_GLOBAL_ATTRIBUTE3, null ABA_GLOBAL_ATTRIBUTE4, null ABA_GLOBAL_ATTRIBUTE5, null ABA_GLOBAL_ATTRIBUTE6, null ABA_GLOBAL_ATTRIBUTE7, null ABA_GLOBAL_ATTRIBUTE8, null ABA_GLOBAL_ATTRIBUTE9, null ABA_GLOBAL_ATTRIBUTE10, null ABA_GLOBAL_ATTRIBUTE11, null ABA_GLOBAL_ATTRIBUTE12, null ABA_GLOBAL_ATTRIBUTE13, null ABA_GLOBAL_ATTRIBUTE14, null ABA_GLOBAL_ATTRIBUTE15, null ABA_GLOBAL_ATTRIBUTE16, null ABA_GLOBAL_ATTRIBUTE17, null ABA_GLOBAL_ATTRIBUTE18, null ABA_GLOBAL_ATTRIBUTE19, null ABA_GLOBAL_ATTRIBUTE20, null ABAS_GLOBAL_ATTRIBUTE_CATEGORY, null ABAS_GLOBAL_ATTRIBUTE1, null ABAS_GLOBAL_ATTRIBUTE2, null ABAS_GLOBAL_ATTRIBUTE3, null ABAS_GLOBAL_ATTRIBUTE4, null ABAS_GLOBAL_ATTRIBUTE5, null ABAS_GLOBAL_ATTRIBUTE6, null ABAS_GLOBAL_ATTRIBUTE7, null ABAS_GLOBAL_ATTRIBUTE8, null ABAS_GLOBAL_ATTRIBUTE9, null ABAS_GLOBAL_ATTRIBUTE10, null ABAS_GLOBAL_ATTRIBUTE11, null ABAS_GLOBAL_ATTRIBUTE12, null ABAS_GLOBAL_ATTRIBUTE13, null ABAS_GLOBAL_ATTRIBUTE14, null ABAS_GLOBAL_ATTRIBUTE15, null ABAS_GLOBAL_ATTRIBUTE16, null ABAS_GLOBAL_ATTRIBUTE17, null ABAS_GLOBAL_ATTRIBUTE18, null ABAS_GLOBAL_ATTRIBUTE19, null ABAS_GLOBAL_ATTRIBUTE20, APSS.GLOBAL_ATTRIBUTE_CATEGORY PVS_GLOBAL_ATTRIBUTE_CATEGORY, APSS.GLOBAL_ATTRIBUTE1 PVS_GLOBAL_ATTRIBUTE1, APSS.GLOBAL_ATTRIBUTE2 PVS_GLOBAL_ATTRIBUTE2, APSS.GLOBAL_ATTRIBUTE3 PVS_GLOBAL_ATTRIBUTE3, APSS.GLOBAL_ATTRIBUTE4 PVS_GLOBAL_ATTRIBUTE4, APSS.GLOBAL_ATTRIBUTE5 PVS_GLOBAL_ATTRIBUTE5, APSS.GLOBAL_ATTRIBUTE6 PVS_GLOBAL_ATTRIBUTE6, APSS.GLOBAL_ATTRIBUTE7 PVS_GLOBAL_ATTRIBUTE7, APSS.GLOBAL_ATTRIBUTE8 PVS_GLOBAL_ATTRIBUTE8, APSS.GLOBAL_ATTRIBUTE9 PVS_GLOBAL_ATTRIBUTE9, APSS.GLOBAL_ATTRIBUTE10 PVS_GLOBAL_ATTRIBUTE10, APSS.GLOBAL_ATTRIBUTE11 PVS_GLOBAL_ATTRIBUTE11, APSS.GLOBAL_ATTRIBUTE12 PVS_GLOBAL_ATTRIBUTE12, APSS.GLOBAL_ATTRIBUTE13 PVS_GLOBAL_ATTRIBUTE13, APSS.GLOBAL_ATTRIBUTE14 PVS_GLOBAL_ATTRIBUTE14, APSS.GLOBAL_ATTRIBUTE15 PVS_GLOBAL_ATTRIBUTE15, APSS.GLOBAL_ATTRIBUTE16 PVS_GLOBAL_ATTRIBUTE16, APSS.GLOBAL_ATTRIBUTE17 PVS_GLOBAL_ATTRIBUTE17, APSS.GLOBAL_ATTRIBUTE18 PVS_GLOBAL_ATTRIBUTE18, APSS.GLOBAL_ATTRIBUTE19 PVS_GLOBAL_ATTRIBUTE19, APSS.GLOBAL_ATTRIBUTE20 PVS_GLOBAL_ATTRIBUTE20, IPAY.INT_EFT_SWIFT_CODE BK_EFT_SWIFT_CODE, IPAY.EXT_BRANCH_NUMBER SBK_BANK_BRANCH_NUMBER, IPAY.EXT_BANK_BRANCH_NAME SBK_BANK_BRANCH_NAME, EBL.ADDRESS1 SBK_ADDRESS_LINE1, EBL.ADDRESS2 SBK_ADDRESS_LINE2, EBL.ADDRESS3 SBK_ADDRESS_LINE3, EBL.CITY SBK_CITY, EBL.STATE SBK_STATE, EBL.POSTAL_CODE SBK_ZIP, EBL.PROVINCE SBK_PROVINCE, EBL.COUNTRY SBK_COUNTRY, IPAY.EXT_EFT_SWIFT_CODE SBK_EFT_SWIFT_CODE, HR.LOCATION_CODE BILL_TO_INT_LOCATION_NAME, HR.ADDRESS_LINE_1 BILL_TO_INT_ADDRESS1, HR.ADDRESS_LINE_2 BILL_TO_INT_ADDRESS2, HR.ADDRESS_LINE_3 BILL_TO_INT_ADDRESS3, HR.TOWN_OR_CITY BILL_TO_INT_CITY, HR.POSTAL_CODE BILL_TO_INT_POSTAL_CODE, HR.COUNTRY BILL_TO_INT_COUNTRY, IPAY.PAYER_TAX_REGISTRATION_NUM FI_VAT_REGISTRATION_NUM, HR.LOCATION_ID BILL_TO_INT_LOCATION_ID, HR.ECE_TP_LOCATION_CODE ECE_TP_LOCATION_CODE, HR.REGION_1 BILL_TO_INT_REGION1, HR.REGION_2 BILL_TO_INT_REGION2, HR.REGION_3 BILL_TO_INT_REGION3, BKTPD.MAP_ID MAP_ID, IPAY.PAYMENT_DUE_DATE FUTURE_PAY_DUE_DATE, APS.VENDOR_NAME_ALT VENDOR_ALTERNATE_NAME, APSS.VENDOR_SITE_CODE_ALT VENDOR_ALTERNATE_SITE_CODE, IPAY.URI_CHECK_DIGIT SUPPLIER_CHECK_DIGITS, IPAY.EXT_BANK_NAME SBK_BANK_NAME, IPAY.INT_BANK_ACCOUNT_IBAN IBAN_NUMBER, IPAY.EXT_BANK_ACCOUNT_IBAN_NUMBER VENDOR_IBAN_NUMBER, IPM.ATTRIBUTE1 PAYMENT_MEAN, IPM.ATTRIBUTE2 PAYMENT_CHANNEL, APS.VENDOR_ID VENDOR_ID, APSS.VENDOR_SITE_ID VENDOR_SITE_ID, IPAY.PAYMENT_REFERENCE_NUMBER PAYMENT_REFERENCE_NUMBER, BAPR.BANK_CODE SBK_BANK_CODE FROM IBY_PAYMENTS_ALL IPAY, CE_BANK_ACCOUNTS CBA, AP_SUPPLIERS APS, AP_SUPPLIER_SITES APSS, HZ_CONTACT_POINTS HCP, ECE_TP_HEADERS BKTPH, ECE_TP_DETAILS BKTPD, HR_LOCATIONS HR, HZ_LOCATIONS EBL, HZ_CODE_ASSIGNMENTS HCA, HZ_LOCATIONS IBL, HZ_CODE_ASSIGNMENTS HCA1, IBY_PAYMENT_METHODS_B IPM, IBY_EXT_BANK_ACCOUNTS IEBA, (SELECT PARTY_ID, BANK_CODE FROM HZ_ORGANIZATION_PROFILES WHERE SYSDATE >= EFFECTIVE_START_DATE AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)) BAPR , FINANCIALS_SYSTEM_PARAMS_ALL FI WHERE APSS.VENDOR_SITE_ID = IPAY.SUPPLIER_SITE_ID AND CBA.BANK_ACCOUNT_ID = IPAY.INTERNAL_BANK_ACCOUNT_ID AND APSS.VENDOR_ID = APS.VENDOR_ID AND BKTPH.TP_HEADER_ID = HCP.EDI_TP_HEADER_ID AND BKTPD.TP_HEADER_ID = BKTPH.TP_HEADER_ID AND BKTPD.DOCUMENT_ID = 'PYO' AND HCP.OWNER_TABLE_ID = IPAY.INT_BANK_BRANCH_PARTY_ID AND HCP.OWNER_TABLE_NAME = 'HZ_PARTIES' AND HCP.CONTACT_POINT_TYPE = 'EDI' AND BKTPD.EDI_FLAG = 'Y' AND IPAY.ORG_ID = FI.ORG_ID AND HR.LOCATION_ID = FI.BILL_TO_LOCATION_ID AND EBL.LOCATION_ID (+) = IPAY.EXT_BANK_BRANCH_LOCATION_ID AND HCA.OWNER_TABLE_ID (+) = IPAY.EXT_BANK_BRANCH_PARTY_ID AND HCA.OWNER_TABLE_NAME (+) = 'HZ_PARTIES' AND HCA.CLASS_CATEGORY (+) = 'BANK_BRANCH_TYPE' AND HCA.STATUS (+) = 'A' AND sysdate BETWEEN nvl(HCA.START_DATE_ACTIVE(+),sysdate-1) and nvl(HCA.END_DATE_ACTIVE(+),sysdate+1) AND IBL.LOCATION_ID (+) = IPAY.INT_BANK_BRANCH_LOCATION_ID AND HCA1.OWNER_TABLE_ID (+) = IPAY.INT_BANK_BRANCH_PARTY_ID AND HCA1.OWNER_TABLE_NAME (+) = 'HZ_PARTIES' AND HCA1.CLASS_CATEGORY (+) = 'BANK_BRANCH_TYPE' AND HCA1.STATUS (+) = 'A' AND sysdate BETWEEN nvl(HCA1.START_DATE_ACTIVE(+),sysdate-1) and nvl(HCA1.END_DATE_ACTIVE(+),sysdate+1) and IPAY.PAYMENT_METHOD_CODE = IPM.PAYMENT_METHOD_CODE AND IPAY.EXTERNAL_BANK_ACCOUNT_ID = IEBA.EXT_BANK_ACCOUNT_ID(+) AND IEBA.BRANCH_ID = BAPR.PARTY_ID(+)

View Text - HTML Formatted

SELECT 'EDI' COMMUNICATION_METHOD
, BKTPD.TEST_FLAG TEST_FLAG
, 'PYO' DOCUMENT_ID
, IPAY.PAYMENT_ID DOCUMENT_CODE
, BKTPD.TRANSLATOR_CODE BK_TP_TRANSLATOR_CODE
, HCP.EDI_ECE_TP_LOCATION_CODE BK_TP_LOCATION_CODE_EXT
, BKTPH.TP_DESCRIPTION BK_TP_DESCRIPTION
, BKTPH.TP_REFERENCE_EXT1 BK_TP_REFERENCE_EXT1
, BKTPH.TP_REFERENCE_EXT2 BK_TP_REFERENCE_EXT2
, SYSDATE TRANSACTION_DATE
, BKTPH.ATTRIBUTE_CATEGORY TPH_ATTRIBUTE_CATEGORY
, BKTPH.ATTRIBUTE1 TPH_ATTRIBUTE1
, BKTPH.ATTRIBUTE2 TPH_ATTRIBUTE2
, BKTPH.ATTRIBUTE3 TPH_ATTRIBUTE3
, BKTPH.ATTRIBUTE4 TPH_ATTRIBUTE4
, BKTPH.ATTRIBUTE5 TPH_ATTRIBUTE5
, BKTPH.ATTRIBUTE6 TPH_ATTRIBUTE6
, BKTPH.ATTRIBUTE7 TPH_ATTRIBUTE7
, BKTPH.ATTRIBUTE8 TPH_ATTRIBUTE8
, BKTPH.ATTRIBUTE9 TPH_ATTRIBUTE9
, BKTPH.ATTRIBUTE10 TPH_ATTRIBUTE10
, BKTPH.ATTRIBUTE11 TPH_ATTRIBUTE11
, BKTPH.ATTRIBUTE12 TPH_ATTRIBUTE12
, BKTPH.ATTRIBUTE13 TPH_ATTRIBUTE13
, BKTPH.ATTRIBUTE14 TPH_ATTRIBUTE14
, BKTPH.ATTRIBUTE15 TPH_ATTRIBUTE15
, BKTPD.ATTRIBUTE_CATEGORY TPD_ATTRIBUTE_CATEGORY
, BKTPD.ATTRIBUTE1 TPD_ATTRIBUTE1
, BKTPD.ATTRIBUTE2 TPD_ATTRIBUTE2
, BKTPD.ATTRIBUTE3 TPD_ATTRIBUTE3
, BKTPD.ATTRIBUTE4 TPD_ATTRIBUTE4
, BKTPD.ATTRIBUTE5 TPD_ATTRIBUTE5
, BKTPD.DOCUMENT_STANDARD DOCUMENT_STANDARD
, IPAY.BANK_INSTRUCTION1_CODE TRANSACTION_HANDLING_CODE
, IPAY.PAYMENT_AMOUNT CHECK_AMOUNT
, IPAY.PAYMENT_CURRENCY_CODE CURRENCY_CODE
, IPAY.PAYMENT_METHOD_CODE EDI_PAYMENT_METHOD
, IPAY.BANK_INSTRUCTION2_CODE EDI_PAYMENT_FORMAT
, HCA1.CLASS_CODE BANK_BRANCH_TYPE
, CBA.BANK_ACCOUNT_TYPE BANK_ACCOUNT_TYPE
, IPAY.INT_BANK_ACCOUNT_NUMBER BANK_ACCOUNT_NUM
, HCP.EDI_ID_NUMBER BANK_EDI_ID_NUMBER
, HCA.CLASS_CODE VENDOR_BANK_BRANCH_TYPE
, IPAY.EXT_BANK_ACCOUNT_TYPE VENDOR_BANK_ACCOUNT_TYPE
, IPAY.EXT_BANK_ACCOUNT_NUMBER VENDOR_BANK_ACCOUNT_NUM
, IPAY.PAYMENT_DATE PAYMENT_DATE
, IPAY.INT_BANK_NUMBER BANK_NUM
, IPAY.EXT_BANK_NUMBER VENDOR_BANK_NUM
, IPAY.DELIVERY_CHANNEL_CODE EDI_REMITTANCE_METHOD
, IPAY.PAYMENT_TEXT_MESSAGE1 EDI_REMITTANCE_INSTRUCTION
, IPAY.PAYMENT_INSTRUCTION_ID PAYMENT_INSTRUCTION_ID
, NULL CHECK_VOUCHER_NUM
, NULL SELECTED_CHECK_ID
, IPAY.PAPER_DOCUMENT_NUMBER CHECK_NUMBER
, APS.CUSTOMER_NUM CUSTOMER_NUM
, APS.SEGMENT1 SEGMENT1
, APSS.EDI_ID_NUMBER VENDOR_EDI_ID_NUMBER
, APS.SEGMENT2 SEGMENT2
, APS.SEGMENT3 SEGMENT3
, APS.SEGMENT4 SEGMENT4
, APS.SEGMENT5 SEGMENT5
, IPAY.INT_BANK_BRANCH_PARTY_ID BANK_BRANCH_ID
, IPAY.INT_BANK_BRANCH_NAME BK_BANK_BRANCH_NAME
, IBL.ADDRESS1 BK_ADDRESS_LINE1
, IBL.ADDRESS2 BK_ADDRESS_LINE2
, IBL.ADDRESS3 BK_ADDRESS_LINE3
, IBL.CITY BK_CITY
, IBL.POSTAL_CODE BK_ZIP
, IBL.COUNTRY BK_COUNTRY
, IBL.STATE BK_STATE
, IBL.PROVINCE BK_PROVINCE
, NULL BK_CONTACT_FIRST_NAME
, NULL BK_CONTACT_MIDDLE_NAME
, NULL BK_CONTACT_LAST_NAME
, NULL BK_CONTACT_TITLE
, NULL BK_CONTACT_PREFIX
, NULL BK_CONTACT_AREA_CODE
, NULL BK_CONTACT_PHONE
, APSS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, IPAY.PAYEE_NAME VENDOR_NAME
, IPAY.PAYEE_ADDRESS1 ADDRESS_LINE1
, IPAY.PAYEE_ADDRESS2 ADDRESS_LINE2
, IPAY.PAYEE_ADDRESS3 ADDRESS_LINE3
, IPAY.PAYEE_ADDRESS4 ADDRESS_LINE4
, IPAY.PAYEE_CITY CITY
, IPAY.PAYEE_POSTAL_CODE ZIP
, IPAY.PAYEE_COUNTRY COUNTRY
, IPAY.PAYEE_STATE STATE
, IPAY.PAYEE_PROVINCE PROVINCE
, NULL ABA_GLOBAL_ATTRIBUTE_CATEGORY
, NULL ABA_GLOBAL_ATTRIBUTE1
, NULL ABA_GLOBAL_ATTRIBUTE2
, NULL ABA_GLOBAL_ATTRIBUTE3
, NULL ABA_GLOBAL_ATTRIBUTE4
, NULL ABA_GLOBAL_ATTRIBUTE5
, NULL ABA_GLOBAL_ATTRIBUTE6
, NULL ABA_GLOBAL_ATTRIBUTE7
, NULL ABA_GLOBAL_ATTRIBUTE8
, NULL ABA_GLOBAL_ATTRIBUTE9
, NULL ABA_GLOBAL_ATTRIBUTE10
, NULL ABA_GLOBAL_ATTRIBUTE11
, NULL ABA_GLOBAL_ATTRIBUTE12
, NULL ABA_GLOBAL_ATTRIBUTE13
, NULL ABA_GLOBAL_ATTRIBUTE14
, NULL ABA_GLOBAL_ATTRIBUTE15
, NULL ABA_GLOBAL_ATTRIBUTE16
, NULL ABA_GLOBAL_ATTRIBUTE17
, NULL ABA_GLOBAL_ATTRIBUTE18
, NULL ABA_GLOBAL_ATTRIBUTE19
, NULL ABA_GLOBAL_ATTRIBUTE20
, NULL ABAS_GLOBAL_ATTRIBUTE_CATEGORY
, NULL ABAS_GLOBAL_ATTRIBUTE1
, NULL ABAS_GLOBAL_ATTRIBUTE2
, NULL ABAS_GLOBAL_ATTRIBUTE3
, NULL ABAS_GLOBAL_ATTRIBUTE4
, NULL ABAS_GLOBAL_ATTRIBUTE5
, NULL ABAS_GLOBAL_ATTRIBUTE6
, NULL ABAS_GLOBAL_ATTRIBUTE7
, NULL ABAS_GLOBAL_ATTRIBUTE8
, NULL ABAS_GLOBAL_ATTRIBUTE9
, NULL ABAS_GLOBAL_ATTRIBUTE10
, NULL ABAS_GLOBAL_ATTRIBUTE11
, NULL ABAS_GLOBAL_ATTRIBUTE12
, NULL ABAS_GLOBAL_ATTRIBUTE13
, NULL ABAS_GLOBAL_ATTRIBUTE14
, NULL ABAS_GLOBAL_ATTRIBUTE15
, NULL ABAS_GLOBAL_ATTRIBUTE16
, NULL ABAS_GLOBAL_ATTRIBUTE17
, NULL ABAS_GLOBAL_ATTRIBUTE18
, NULL ABAS_GLOBAL_ATTRIBUTE19
, NULL ABAS_GLOBAL_ATTRIBUTE20
, APSS.GLOBAL_ATTRIBUTE_CATEGORY PVS_GLOBAL_ATTRIBUTE_CATEGORY
, APSS.GLOBAL_ATTRIBUTE1 PVS_GLOBAL_ATTRIBUTE1
, APSS.GLOBAL_ATTRIBUTE2 PVS_GLOBAL_ATTRIBUTE2
, APSS.GLOBAL_ATTRIBUTE3 PVS_GLOBAL_ATTRIBUTE3
, APSS.GLOBAL_ATTRIBUTE4 PVS_GLOBAL_ATTRIBUTE4
, APSS.GLOBAL_ATTRIBUTE5 PVS_GLOBAL_ATTRIBUTE5
, APSS.GLOBAL_ATTRIBUTE6 PVS_GLOBAL_ATTRIBUTE6
, APSS.GLOBAL_ATTRIBUTE7 PVS_GLOBAL_ATTRIBUTE7
, APSS.GLOBAL_ATTRIBUTE8 PVS_GLOBAL_ATTRIBUTE8
, APSS.GLOBAL_ATTRIBUTE9 PVS_GLOBAL_ATTRIBUTE9
, APSS.GLOBAL_ATTRIBUTE10 PVS_GLOBAL_ATTRIBUTE10
, APSS.GLOBAL_ATTRIBUTE11 PVS_GLOBAL_ATTRIBUTE11
, APSS.GLOBAL_ATTRIBUTE12 PVS_GLOBAL_ATTRIBUTE12
, APSS.GLOBAL_ATTRIBUTE13 PVS_GLOBAL_ATTRIBUTE13
, APSS.GLOBAL_ATTRIBUTE14 PVS_GLOBAL_ATTRIBUTE14
, APSS.GLOBAL_ATTRIBUTE15 PVS_GLOBAL_ATTRIBUTE15
, APSS.GLOBAL_ATTRIBUTE16 PVS_GLOBAL_ATTRIBUTE16
, APSS.GLOBAL_ATTRIBUTE17 PVS_GLOBAL_ATTRIBUTE17
, APSS.GLOBAL_ATTRIBUTE18 PVS_GLOBAL_ATTRIBUTE18
, APSS.GLOBAL_ATTRIBUTE19 PVS_GLOBAL_ATTRIBUTE19
, APSS.GLOBAL_ATTRIBUTE20 PVS_GLOBAL_ATTRIBUTE20
, IPAY.INT_EFT_SWIFT_CODE BK_EFT_SWIFT_CODE
, IPAY.EXT_BRANCH_NUMBER SBK_BANK_BRANCH_NUMBER
, IPAY.EXT_BANK_BRANCH_NAME SBK_BANK_BRANCH_NAME
, EBL.ADDRESS1 SBK_ADDRESS_LINE1
, EBL.ADDRESS2 SBK_ADDRESS_LINE2
, EBL.ADDRESS3 SBK_ADDRESS_LINE3
, EBL.CITY SBK_CITY
, EBL.STATE SBK_STATE
, EBL.POSTAL_CODE SBK_ZIP
, EBL.PROVINCE SBK_PROVINCE
, EBL.COUNTRY SBK_COUNTRY
, IPAY.EXT_EFT_SWIFT_CODE SBK_EFT_SWIFT_CODE
, HR.LOCATION_CODE BILL_TO_INT_LOCATION_NAME
, HR.ADDRESS_LINE_1 BILL_TO_INT_ADDRESS1
, HR.ADDRESS_LINE_2 BILL_TO_INT_ADDRESS2
, HR.ADDRESS_LINE_3 BILL_TO_INT_ADDRESS3
, HR.TOWN_OR_CITY BILL_TO_INT_CITY
, HR.POSTAL_CODE BILL_TO_INT_POSTAL_CODE
, HR.COUNTRY BILL_TO_INT_COUNTRY
, IPAY.PAYER_TAX_REGISTRATION_NUM FI_VAT_REGISTRATION_NUM
, HR.LOCATION_ID BILL_TO_INT_LOCATION_ID
, HR.ECE_TP_LOCATION_CODE ECE_TP_LOCATION_CODE
, HR.REGION_1 BILL_TO_INT_REGION1
, HR.REGION_2 BILL_TO_INT_REGION2
, HR.REGION_3 BILL_TO_INT_REGION3
, BKTPD.MAP_ID MAP_ID
, IPAY.PAYMENT_DUE_DATE FUTURE_PAY_DUE_DATE
, APS.VENDOR_NAME_ALT VENDOR_ALTERNATE_NAME
, APSS.VENDOR_SITE_CODE_ALT VENDOR_ALTERNATE_SITE_CODE
, IPAY.URI_CHECK_DIGIT SUPPLIER_CHECK_DIGITS
, IPAY.EXT_BANK_NAME SBK_BANK_NAME
, IPAY.INT_BANK_ACCOUNT_IBAN IBAN_NUMBER
, IPAY.EXT_BANK_ACCOUNT_IBAN_NUMBER VENDOR_IBAN_NUMBER
, IPM.ATTRIBUTE1 PAYMENT_MEAN
, IPM.ATTRIBUTE2 PAYMENT_CHANNEL
, APS.VENDOR_ID VENDOR_ID
, APSS.VENDOR_SITE_ID VENDOR_SITE_ID
, IPAY.PAYMENT_REFERENCE_NUMBER PAYMENT_REFERENCE_NUMBER
, BAPR.BANK_CODE SBK_BANK_CODE
FROM IBY_PAYMENTS_ALL IPAY
, CE_BANK_ACCOUNTS CBA
, AP_SUPPLIERS APS
, AP_SUPPLIER_SITES APSS
, HZ_CONTACT_POINTS HCP
, ECE_TP_HEADERS BKTPH
, ECE_TP_DETAILS BKTPD
, HR_LOCATIONS HR
, HZ_LOCATIONS EBL
, HZ_CODE_ASSIGNMENTS HCA
, HZ_LOCATIONS IBL
, HZ_CODE_ASSIGNMENTS HCA1
, IBY_PAYMENT_METHODS_B IPM
, IBY_EXT_BANK_ACCOUNTS IEBA
, (SELECT PARTY_ID
, BANK_CODE
FROM HZ_ORGANIZATION_PROFILES
WHERE SYSDATE >= EFFECTIVE_START_DATE
AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE)) BAPR
, FINANCIALS_SYSTEM_PARAMS_ALL FI
WHERE APSS.VENDOR_SITE_ID = IPAY.SUPPLIER_SITE_ID
AND CBA.BANK_ACCOUNT_ID = IPAY.INTERNAL_BANK_ACCOUNT_ID
AND APSS.VENDOR_ID = APS.VENDOR_ID
AND BKTPH.TP_HEADER_ID = HCP.EDI_TP_HEADER_ID
AND BKTPD.TP_HEADER_ID = BKTPH.TP_HEADER_ID
AND BKTPD.DOCUMENT_ID = 'PYO'
AND HCP.OWNER_TABLE_ID = IPAY.INT_BANK_BRANCH_PARTY_ID
AND HCP.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE = 'EDI'
AND BKTPD.EDI_FLAG = 'Y'
AND IPAY.ORG_ID = FI.ORG_ID
AND HR.LOCATION_ID = FI.BILL_TO_LOCATION_ID
AND EBL.LOCATION_ID (+) = IPAY.EXT_BANK_BRANCH_LOCATION_ID
AND HCA.OWNER_TABLE_ID (+) = IPAY.EXT_BANK_BRANCH_PARTY_ID
AND HCA.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCA.CLASS_CATEGORY (+) = 'BANK_BRANCH_TYPE'
AND HCA.STATUS (+) = 'A'
AND SYSDATE BETWEEN NVL(HCA.START_DATE_ACTIVE(+)
, SYSDATE-1)
AND NVL(HCA.END_DATE_ACTIVE(+)
, SYSDATE+1)
AND IBL.LOCATION_ID (+) = IPAY.INT_BANK_BRANCH_LOCATION_ID
AND HCA1.OWNER_TABLE_ID (+) = IPAY.INT_BANK_BRANCH_PARTY_ID
AND HCA1.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCA1.CLASS_CATEGORY (+) = 'BANK_BRANCH_TYPE'
AND HCA1.STATUS (+) = 'A'
AND SYSDATE BETWEEN NVL(HCA1.START_DATE_ACTIVE(+)
, SYSDATE-1)
AND NVL(HCA1.END_DATE_ACTIVE(+)
, SYSDATE+1)
AND IPAY.PAYMENT_METHOD_CODE = IPM.PAYMENT_METHOD_CODE
AND IPAY.EXTERNAL_BANK_ACCOUNT_ID = IEBA.EXT_BANK_ACCOUNT_ID(+)
AND IEBA.BRANCH_ID = BAPR.PARTY_ID(+)