View: FV_ACH_CTX_VENDOR_V

View Text

SELECT APSI.CHECKRUN_NAME CHECKRUN_NAME
, SUBSTR( NVL(APSI.INVOICE_NUM
, ' ')
, 1
, 50) INVOICE_NUM
, APSI.PAY_SELECTED_CHECK_ID SELECTED_CHECK_ID
, APSI.PAYMENT_AMOUNT PAYMENT_AMOUNT
, UPPER(SUBSTR(HRORG.NAME
, 1
, 25)) AGENCY_NAME
, UPPER(RPAD(FVS.ALC_CODE
, 8
, ' ')) AGENCY_ALC_CODE
, UPPER(SUBSTR( NVL(APBB2.BANK_BRANCH_TYPE
, ' ')
, 1
, 3)) AGENCY_BANK_ACCOUNT_TYPE
, UPPER(SUBSTR(NVL(HRLOC.ADDRESS_LINE_1
, ' ')
, 1
, 25)) AGENCY_ADDRESS_LINE_1
, UPPER(SUBSTR( NVL(HRLOC.ADDRESS_LINE_2
, ' ')
, 1
, 25)) AGENCY_ADDRESS_LINE_2
, UPPER(SUBSTR(NVL(HRLOC.TOWN_OR_CITY||'
, '|| HRLOC.REGION_2||' '||HRLOC.POSTAL_CODE
, ' ')
, 1
, 25)) AGENCY_CITY_STATE_ZIP
, SUBSTR(NVL(HRLOC.TELEPHONE_NUMBER_1
, ' ')
, 1
, 12) AGENCY_TELEPHONE_NUMBER_1
, UPPER(APSI.VENDOR_NUM) VENDOR_NUM
, UPPER(SUBSTR( NVL(APSI.VENDOR_NAME
, ' ')
, 1
, 22)) VENDOR_NAME
, SUBSTR(NVL(APBA.BANK_ACCOUNT_NUM
, ' ')
, 1
, 17) VENDOR_BANK_ACCOUNT_NUM
, APBA.CHECK_DIGITS
, UPPER(SUBSTR(NVL(APBA.BANK_ACCOUNT_TYPE
, ' ')
, 1
, 1)) VENDOR_BANK_ACCOUNT_TYPE
, UPPER(SUBSTR(NVL(POV.NUM_1099
, ' ')
, 1
, 9)) VENDOR_NUM_1099
, UPPER(LPAD(APBB.BANK_NUM
, 9
, '0')) VENDOR_BANK_NUM
, ASIC.CHECK_AMOUNT VENDOR_CHECK_AMT
, ASIC.SELECTED_CHECK_ID VENDOR_CHECK_ID
, UPPER(SUBSTR( NVL(ASIC.VENDOR_NAME
, ' ')
, 1
, 35)) VENDOR_NAME1
, UPPER(SUBSTR( NVL(ASIC.VENDOR_NUM
, ' ')
, 1
, 16)) VENDOR_NUMBER1
, UPPER(SUBSTR( NVL(ASIC.ADDRESS_LINE1
, ' ')
, 1
, 35)) VENDOR_ADDRESS_LINE1
, UPPER(SUBSTR( NVL(ASIC.ADDRESS_LINE2
, ' ')
, 1
, 35)) VENDOR_ADDRESS_LINE2
, UPPER(SUBSTR(NVL(ASIC.ADDRESS_LINE3
, ' ')
, 1
, 25)) VENDOR_ADDRESS_LINE3
, UPPER(SUBSTR(NVL(ASIC.CITY||'
, '|| ASIC.STATE||' '||ASIC.ZIP
, ' ')
, 1
, 25)) VENDOR_ADDRESS_LINE4
, TO_CHAR(ASIC.CHECK_NUMBER) CHECK_NUM
FROM HR_ORGANIZATION_UNITS HRORG
, FV_SYSTEM_PARAMETERS_V FVS
, HR_LOCATIONS HRLOC
, AP_SELECTED_INVOICES APSI
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, AP_BANK_ACCOUNTS APBA
, AP_BANK_ACCOUNTS APBA2
, AP_BANK_BRANCHES APBB
, AP_BANK_BRANCHES APBB2
, AP_INVOICE_SELECTION_CRITERIA APISC
, AP_SELECTED_INVOICE_CHECKS ASIC
WHERE FVS.LE_ORGANIZATION_ID = HRORG.ORGANIZATION_ID
AND HRORG.LOCATION_ID = HRLOC.LOCATION_ID
AND FVS.SET_OF_BOOKS_ID = APSI.SET_OF_BOOKS_ID
AND APSI.VENDOR_ID = POV.VENDOR_ID
AND APSI.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND APSI.CHECKRUN_NAME = APISC.CHECKRUN_NAME
AND NVL( APSI.EXTERNAL_BANK_ACCOUNT_ID
, 1) = DECODE(APSI.EXTERNAL_BANK_ACCOUNT_ID
, NULL
, 1
, APBA.BANK_ACCOUNT_ID)
AND APBA.BANK_BRANCH_ID = APBB.BANK_BRANCH_ID
AND APISC.BANK_ACCOUNT_ID = APBA2.BANK_ACCOUNT_ID
AND APBA2.BANK_BRANCH_ID = APBB2.BANK_BRANCH_ID
AND APSI.PAY_SELECTED_CHECK_ID = ASIC.SELECTED_CHECK_ID
AND ASIC.OK_TO_PAY_FLAG = 'Y'
AND APSI.OK_TO_PAY_FLAG = 'Y'

Columns