View: ECE_PYO_PAYMENT_V

Product:	EC - e-Commerce Gateway
Description:	This view extracts payment informationfor the outbound Payment Order/Remittance Advice (820/PAYORD/REMADV) transaction.@rep:scope private@rep:product AP@rep:lifecycle active@rep:displayname Payment Order/Remittance Advice Payment View@rep:c
Implementation/DBA Data:	APPS.ECE_PYO_PAYMENT_V

View Text

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.PAYMENT_ID 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
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
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 HR.LOCATION_ID = IPAY.PAYER_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 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'

Columns