View: AMS_AP_CHECKS_V

View Text

SELECT AC.ROWID
, AC.CHECK_ID
, AC.CHECK_NUMBER
, AC.CHECK_DATE
, AC.CURRENCY_CODE
, AC.AMOUNT
, AC.AMOUNT
, AC.BASE_AMOUNT
, AC.EXCHANGE_DATE
, AC.EXCHANGE_RATE
, AC.EXCHANGE_RATE_TYPE
, GDCT.USER_CONVERSION_TYPE
, AC.CLEARED_AMOUNT
, AC.CLEARED_BASE_AMOUNT
, AC.CLEARED_DATE
, AC.CLEARED_EXCHANGE_DATE
, AC.CLEARED_EXCHANGE_RATE
, AC.CLEARED_EXCHANGE_RATE_TYPE
, AC.PAYMENT_TYPE_FLAG
, ALC1.DISPLAYED_FIELD
, AC.PAYMENT_METHOD_LOOKUP_CODE
, ALC2.DISPLAYED_FIELD
, AC.STATUS_LOOKUP_CODE
, ALC3.DISPLAYED_FIELD
, AC.VENDOR_ID
, AC.VENDOR_NAME
, AC.VENDOR_SITE_ID
, AC.VENDOR_SITE_CODE
, DECODE(AC.VENDOR_ID
, ''
, '********************************************************************************'
, PV.VENDOR_NAME)
, DECODE(AC.VENDOR_SITE_ID
, ''
, '***************'
, PVS.VENDOR_SITE_CODE)
, DECODE(AC.VENDOR_ID
, ''
, '******************************'
, PV.SEGMENT1)
, DECODE(AC.VENDOR_ID
, ''
, '******************************'
, PV.NUM_1099)
, AC.ADDRESS_LINE1
, AC.ADDRESS_LINE2
, AC.ADDRESS_LINE3
, AC.ADDRESS_LINE4
, AC.CITY
, AC.PROVINCE
, AC.COUNTY
, AC.STATE
, AC.ZIP
, AC.COUNTRY
, NVL (AC.ADDRESS_STYLE
, 'DEFAULT')
, AC.ADDRESS_LINE1 || DECODE(AC.ADDRESS_LINE1
, NULL
, ''
, CHR(10)) || AC.ADDRESS_LINE2 || DECODE(AC.ADDRESS_LINE2
, NULL
, ''
, CHR(10)) || AC.ADDRESS_LINE3 || DECODE(AC.ADDRESS_LINE3
, NULL
, ''
, CHR(10)) || AC.CITY || '
, ' || AC.STATE || ' ' || AC.ZIP || DECODE(AC.CITY
, NULL
, DECODE(AC.STATE
, NULL
, DECODE(AC.ZIP
, NULL
, ''
, CHR(10))
, CHR(10))
, CHR(10)) || AC.COUNTRY ADDRESS
, AC.BANK_ACCOUNT_ID
, ABB.BANK_NAME
, AC.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NAME
, AC.BANK_ACCOUNT_NUM
, AC.BANK_ACCOUNT_TYPE
, AC.BANK_NUM
, ABA.CURRENCY_CODE
, AC.CHECK_VOUCHER_NUM
, AC.RELEASED_DATE
, AC.RELEASED_BY
, AC.STOPPED_DATE
, AC.STOPPED_BY
, AC.VOID_DATE
, AC.FUTURE_PAY_DUE_DATE
, AC.ORG_ID
, AC.WITHHOLDING_STATUS_LOOKUP_CODE
, ABA.SET_OF_BOOKS_ID
FROM AP_BANK_ACCOUNTS ABA
, AP_BANK_BRANCHES ABB
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
, AP_LOOKUP_CODES ALC3
, GL_DAILY_CONVERSION_TYPES GDCT
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
, AP_CHECKS AC
WHERE AC.BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID
AND ABB.BANK_BRANCH_ID = ABA.BANK_BRANCH_ID
AND ALC1.LOOKUP_TYPE = 'PAYMENT TYPE'
AND ALC1.LOOKUP_CODE = AC.PAYMENT_TYPE_FLAG
AND ALC2.LOOKUP_TYPE = 'PAYMENT METHOD'
AND ALC2.LOOKUP_CODE = AC.PAYMENT_METHOD_LOOKUP_CODE
AND ALC3.LOOKUP_TYPE (+) = 'CHECK STATE'
AND ALC3.LOOKUP_CODE (+) = AC.STATUS_LOOKUP_CODE
AND AC.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE (+)
AND AC.VENDOR_ID = PV.VENDOR_ID (+)
AND AC.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND DECODE(AC.VENDOR_ID
, ''
, -1
, AC.VENDOR_ID) = DECODE(AC.STATUS_LOOKUP_CODE
, 'SET UP'
, -1
, 'SPOILED'
, -1
, AC.VENDOR_ID)

Columns