View: AP_CHECKS_V

Product:	AP - Payables
Description:
Implementation/DBA Data:	APPS.AP_CHECKS_V

View Text

SELECT AC.ROWID ROW_ID
, AC.ADDRESS_LINE1 ADDRESS_LINE1
, AC.ADDRESS_LINE2 ADDRESS_LINE2
, AC.ADDRESS_LINE3 ADDRESS_LINE3
, AC.ADDRESS_LINE4 ADDRESS_LINE4
, NVL(AC.ADDRESS_STYLE
, 'DEFAULT') ADDRESS_STYLE
, AC.AMOUNT AMOUNT
, AC.AMOUNT CONTROL_AMOUNT
, AC.ATTRIBUTE1 ATTRIBUTE1
, AC.ATTRIBUTE10 ATTRIBUTE10
, AC.ATTRIBUTE11 ATTRIBUTE11
, AC.ATTRIBUTE12 ATTRIBUTE12
, AC.ATTRIBUTE13 ATTRIBUTE13
, AC.ATTRIBUTE14 ATTRIBUTE14
, AC.ATTRIBUTE15 ATTRIBUTE15
, AC.ATTRIBUTE2 ATTRIBUTE2
, AC.ATTRIBUTE3 ATTRIBUTE3
, AC.ATTRIBUTE4 ATTRIBUTE4
, AC.ATTRIBUTE5 ATTRIBUTE5
, AC.ATTRIBUTE6 ATTRIBUTE6
, AC.ATTRIBUTE7 ATTRIBUTE7
, AC.ATTRIBUTE8 ATTRIBUTE8
, AC.ATTRIBUTE9 ATTRIBUTE9
, AC.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CBA.BANK_ACCOUNT_ID BANK_ACCOUNT_ID
, AC.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
, AC.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM
, AC.BANK_ACCOUNT_TYPE BANK_ACCOUNT_TYPE
, AC.BANK_NUM BANK_NUM
, AC.BASE_AMOUNT BASE_AMOUNT
, AC.CHECKRUN_ID CHECKRUN_ID
, AC.CHECKRUN_NAME CHECKRUN_NAME
, AC.CHECK_DATE CHECK_DATE
, AC.CHECK_ID CHECK_ID
, AC.CHECK_NUMBER CHECK_NUMBER
, PD.PAYMENT_DOCUMENT_ID
, AC.CHECK_VOUCHER_NUM CHECK_VOUCHER_NUM
, AC.CITY CITY
, AC.CLEARED_AMOUNT CLEARED_AMOUNT
, AC.CLEARED_BASE_AMOUNT CLEARED_BASE_AMOUNT
, AC.CLEARED_DATE CLEARED_DATE
, AC.CLEARED_EXCHANGE_DATE CLEARED_EXCHANGE_DATE
, AC.CLEARED_EXCHANGE_RATE CLEARED_EXCHANGE_RATE
, AC.CLEARED_EXCHANGE_RATE_TYPE CLEARED_EXCHANGE_RATE_TYPE
, AC.COUNTRY COUNTRY
, AC.COUNTY COUNTY
, AC.CREATED_BY CREATED_BY
, AC.CREATION_DATE CREATION_DATE
, AC.CURRENCY_CODE CURRENCY_CODE
, AC.DOC_CATEGORY_CODE DOC_CATEGORY_CODE
, AC.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, AC.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, AC.EXCHANGE_DATE EXCHANGE_DATE
, AC.EXCHANGE_RATE EXCHANGE_RATE
, AC.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, AC.FUTURE_PAY_DUE_DATE FUTURE_PAY_DUE_DATE
, AC.LAST_UPDATED_BY LAST_UPDATED_BY
, AC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AC.ORG_ID ORG_ID
, AC.PAYMENT_METHOD_CODE
, AC.PAYMENT_TYPE_FLAG PAYMENT_TYPE_FLAG
, AC.PROVINCE PROVINCE
, AC.RECONCILIATION_BATCH_ID RECONCILIATION_BATCH_ID
, AC.RELEASED_DATE RELEASED_DATE
, AC.RELEASED_BY RELEASED_BY
, AC.STATE STATE
, AC.STATUS_LOOKUP_CODE STATUS_LOOKUP_CODE
, AC.STOPPED_DATE STOPPED_DATE
, AC.STOPPED_BY STOPPED_BY
, AC.TREASURY_PAY_DATE TREASURY_PAY_DATE
, AC.TREASURY_PAY_NUMBER TREASURY_PAY_NUMBER
, AC.VENDOR_ID VENDOR_ID
, AC.VENDOR_NAME VENDOR_NAME /* BUG 15966842 */
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, NVL((SELECT NVL(ALC.DISPLAYED_FIELD
, AC.VENDOR_SITE_CODE)
FROM AP_LOOKUP_CODES ALC
WHERE ALC.LOOKUP_TYPE(+) = 'VENDOR_SITE_CODE'
AND ALC.LOOKUP_CODE(+) = AC.VENDOR_SITE_CODE)
, AC.VENDOR_SITE_CODE)
, AC.VENDOR_SITE_CODE) VENDOR_SITE_CODE
, AC.VENDOR_SITE_ID VENDOR_SITE_ID
, AC.VOID_DATE VOID_DATE
, AC.WITHHOLDING_STATUS_LOOKUP_CODE WITHHOLDING_STATUS_LOOKUP_CODE
, AC.ZIP ZIP
, AC.ADDRESS_LINE1 || DECODE(AC.ADDRESS_LINE1
, NULL
, ''
, FND_GLOBAL.LOCAL_CHR(10)) || AC.ADDRESS_LINE2 || DECODE(AC.ADDRESS_LINE2
, NULL
, ''
, FND_GLOBAL.LOCAL_CHR(10)) || AC.ADDRESS_LINE3 || DECODE(AC.ADDRESS_LINE3
, NULL
, ''
, FND_GLOBAL.LOCAL_CHR(10)) || AC.CITY || '
, ' || AC.STATE || ' ' || AC.ZIP || DECODE(AC.CITY
, NULL
, DECODE(AC.STATE
, NULL
, DECODE(AC.ZIP
, NULL
, ''
, FND_GLOBAL.LOCAL_CHR(10))
, FND_GLOBAL.LOCAL_CHR(10))
, FND_GLOBAL.LOCAL_CHR(10)) || AC.COUNTRY ADDRESS
, CBB.BANK_NAME BANK_NAME /*
, CBA.BANK_ACCOUNT_NAME CURRENT_BANK_ACCOUNT_NAME*/
, DECODE(PAYCARD_REFERENCE_ID
, NULL
, CBA.BANK_ACCOUNT_NAME
, AC.BANK_ACCOUNT_NAME ) CURRENT_BANK_ACCOUNT_NAME
, CBA.CURRENCY_CODE BANK_CURRENCY_CODE
, ASPA.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PD.PAYMENT_DOCUMENT_NAME
, ALC1.DISPLAYED_FIELD PAYMENT_TYPE
, IBY1.PAYMENT_METHOD_NAME PAYMENT_METHOD
, ALC3.DISPLAYED_FIELD CHECK_STATUS
, STOPPED_DATE STOP_DATE
, FDS.NAME DOC_SEQUENCE_NAME
, FDSC.NAME DOC_CATEGORY_NAME
, FT.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME
, GDCT.USER_CONVERSION_TYPE USER_RATE_TYPE
, NVL(ASUP.VENDOR_NAME
, DECODE(AC.PARTY_ID
, ''
, '********************************************************************************'
, HZP.PARTY_NAME)) CURRENT_VENDOR_NAME
, DECODE(AC.VENDOR_ID
, ''
, DECODE(AC.PARTY_ID
, ''
, '******************************'
, NULL)
, ASUP.SEGMENT1) VENDOR_NUMBER
, DECODE(AC.VENDOR_ID
, ''
, DECODE(AC.PARTY_ID
, ''
, '******************************'
, NULL)
, ASUP.NUM_1099) NUM_1099 /* BUG 15966842 */
, DECODE(AC.VENDOR_SITE_ID
, ''
, DECODE(AC.PARTY_ID
, ''
, '**************'
, NULL)
, DECODE(SIGN(AC.VENDOR_SITE_ID)
, -1
, NULL
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, NVL((SELECT NVL(ALC.DISPLAYED_FIELD
, PVS.VENDOR_SITE_CODE)
FROM AP_LOOKUP_CODES ALC
WHERE ALC.LOOKUP_TYPE(+) = 'VENDOR_SITE_CODE'
AND ALC.LOOKUP_CODE(+) = PVS.VENDOR_SITE_CODE)
, PVS.VENDOR_SITE_CODE)
, PVS.VENDOR_SITE_CODE))) CURRENT_VENDOR_SITE_CODE
, DECODE(SIGN(AC.VENDOR_SITE_ID)
, -1
, NULL
, HZL.ADDRESS1 || ' ' || HZL.ADDRESS2 || ' ' || HZL.CITY || ' '|| HZL.STATE || ' '|| HZL.POSTAL_CODE) TRADING_PARTNER_ADDRESS
, CSH.STATEMENT_NUMBER STATEMENT_NUMBER
, CSLINES.LINE_NUMBER STATEMENT_LINE_NUMBER
, AP_AUTO_PAYMENT_PKG.SELECTION_CRITERIA_EXISTS(AC.CHECK_ID) SELECTION_CRITERIA_FLAG
, AP_INVOICE_PAYMENTS_PKG.GET_MAX_GL_DATE(AC.CHECK_ID) MAX_PAYMENT_GL_DATE
, AC.POSITIVE_PAY_STATUS_CODE
, AC.TRANSFER_PRIORITY
, AC.EXTERNAL_BANK_ACCOUNT_ID
, AC.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, AC.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, AC.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, AC.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, AC.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, AC.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, AC.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, AC.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, AC.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, AC.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, AC.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, AC.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, AC.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, AC.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, AC.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, AC.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, AC.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, AC.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, AC.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, AC.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, AC.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, AC.STAMP_DUTY_AMT
, AC.STAMP_DUTY_BASE_AMT
, AC.MATURITY_EXCHANGE_DATE
, AC.MATURITY_EXCHANGE_RATE_TYPE
, AC.MATURITY_EXCHANGE_RATE
, GDCT1.USER_CONVERSION_TYPE MATURITY_USER_RATE_TYPE
, AC.DESCRIPTION
, AC.ANTICIPATED_VALUE_DATE
, AC.ACTUAL_VALUE_DATE
, AP_CHECKS_PKG.GET_POSTING_STATUS(AC.CHECK_ID)
, IBY3.MEANING AS BANK_CHARGE_BEARER_DSP
, IBY5.MEANING AS SETTLEMENT_PRIORITY_DSP
, AC.BANK_CHARGE_BEARER
, AC.SETTLEMENT_PRIORITY
, AC.PARTY_ID
, AC.PARTY_SITE_ID
, IBY2.PAYMENT_PROFILE_ID
, IBY2.PAYMENT_PROFILE_NAME
, IBY2.PROCESSING_TYPE
, AC.PAYMENT_ID
, AC.LEGAL_ENTITY_ID
, AC.VOID_CHECK_ID
, AC.VOID_CHECK_NUMBER
, AC.CE_BANK_ACCT_USE_ID /* BUG 7535348*/
, AC.REMIT_TO_SUPPLIER_NAME REMIT_TO_SUPPLIER_NAME
, AC.REMIT_TO_SUPPLIER_ID REMIT_TO_SUPPLIER_ID
, AC.REMIT_TO_SUPPLIER_SITE REMIT_TO_SUPPLIER_SITE
, AC.REMIT_TO_SUPPLIER_SITE_ID REMIT_TO_SUPPLIER_SITE_ID
, PV1.SEGMENT1 REMIT_TO_SUPPLIER_NUMBER
, DECODE(SIGN(AC.REMIT_TO_SUPPLIER_SITE_ID)
, -1
, NULL
, HZL1.ADDRESS1 || ' ' || HZL1.ADDRESS2 || ' ' || HZL1.CITY || ' '|| HZL1.STATE || ' '|| HZL1.POSTAL_CODE) REMIT_TO_SUPPLIER_ADDRESS
, AC.RELATIONSHIP_ID RELATIONSHIP_ID /* BUG 7535348*/
, DECODE(AC.REMIT_TO_SUPPLIER_ID
, ''
, DECODE(PV1.PARTY_ID
, ''
, '*******'
, NULL)
, PV1.NUM_1099) REMIT_NUM_1099
, DECODE(PV1.PARTY_ID
, ''
, '************************'
, HZP1.PARTY_NAME) CURRENT_REMIT_TO_SUPPLIER_NAME
, DECODE(AC.REMIT_TO_SUPPLIER_SITE_ID
, ''
, DECODE(PV1.PARTY_ID
, ''
, '******'
, NULL)
, DECODE(SIGN(AC.REMIT_TO_SUPPLIER_SITE_ID)
, -1
, NULL
, PVS1.VENDOR_SITE_CODE)) CURRENT_REMIT_VENDOR_SITE_CODE
, AC.ACKNOWLEDGED_FLAG ACKNOWLEDGED_FLAG /* ADDED FOR NOEX */
, AC.PAYCARD_AUTHORIZATION_NUMBER
, AC.PAYCARD_REFERENCE_ID
FROM CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_ALL CBAU
, CE_BANK_BRANCHES_V CBB
, AP_SYSTEM_PARAMETERS_ALL ASPA
, CE_PAYMENT_DOCUMENTS PD
, AP_LOOKUP_CODES ALC1
, IBY_PAYMENT_METHODS_VL IBY1
, IBY_PAYMENT_PROFILES IBY2
, FND_LOOKUPS IBY3
, FND_LOOKUPS IBY5
, AP_LOOKUP_CODES ALC3
, FND_DOCUMENT_SEQUENCES FDS
, FND_DOC_SEQUENCE_CATEGORIES FDSC
, FND_TERRITORIES_VL FT
, GL_DAILY_CONVERSION_TYPES GDCT
, AP_SUPPLIERS ASUP
, AP_SUPPLIER_SITES_ALL PVS
, CE_STATEMENT_RECONCILS_ALL CSRA
, CE_STATEMENT_HEADERS CSH
, CE_STATEMENT_LINES CSLINES
, AP_CHECKS AC
, GL_DAILY_CONVERSION_TYPES GDCT1
, HZ_PARTIES HZP
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HZL /* BUG 7535348*/
, AP_SUPPLIERS PV1
, AP_SUPPLIER_SITES_ALL PVS1
, HZ_PARTY_SITES HPS1
, HZ_LOCATIONS HZL1
, /* BUG 7535348*/ HZ_PARTIES HZP1
WHERE AC.CE_BANK_ACCT_USE_ID = CBAU.BANK_ACCT_USE_ID (+)
AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+)
AND CBAU.ORG_ID = ASPA.ORG_ID (+)
AND AC.MATURITY_EXCHANGE_RATE_TYPE = GDCT1.CONVERSION_TYPE(+)
AND CBB.BRANCH_PARTY_ID (+)= CBA.BANK_BRANCH_ID
AND AC.PAYMENT_DOCUMENT_ID = PD.PAYMENT_DOCUMENT_ID(+)
AND ALC1.LOOKUP_TYPE = 'PAYMENT TYPE'
AND ALC1.LOOKUP_CODE = AC.PAYMENT_TYPE_FLAG
AND IBY1.PAYMENT_METHOD_CODE (+) = AC.PAYMENT_METHOD_CODE
AND IBY2.PAYMENT_PROFILE_ID (+) = AC.PAYMENT_PROFILE_ID
AND ALC3.LOOKUP_TYPE (+) = 'CHECK STATE'
AND ALC3.LOOKUP_CODE (+) = AC.STATUS_LOOKUP_CODE
AND AC.BANK_CHARGE_BEARER = IBY3.LOOKUP_CODE(+)
AND IBY3.LOOKUP_TYPE (+) = 'IBY_BANK_CHARGE_BEARER'
AND AC.SETTLEMENT_PRIORITY = IBY5.LOOKUP_CODE (+)
AND IBY5.LOOKUP_TYPE (+) = 'IBY_SETTLEMENT_PRIORITY'
AND AC.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID (+)
AND FDSC.APPLICATION_ID(+) = 200
AND AC.DOC_CATEGORY_CODE = FDSC.CODE (+)
AND AC.COUNTRY = FT.TERRITORY_CODE (+)
AND AC.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE (+)
AND AC.VENDOR_ID = ASUP.VENDOR_ID (+)
AND AC.PARTY_ID = HZP.PARTY_ID (+)
AND AC.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND AC.PARTY_SITE_ID = HPS.PARTY_SITE_ID (+)
AND HPS.LOCATION_ID = HZL.LOCATION_ID (+)
AND CSRA.REFERENCE_TYPE (+) = 'PAYMENT'
AND CSRA.REFERENCE_ID (+) = AC.CHECK_ID
AND CSRA.CURRENT_RECORD_FLAG (+) = 'Y'
AND CSRA.STATEMENT_LINE_ID = CSLINES.STATEMENT_LINE_ID (+)
AND CSLINES.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID (+)
AND CSRA.STATUS_FLAG (+) = 'M' /* BUG 7535348*/
AND AC.REMIT_TO_SUPPLIER_ID = PV1.VENDOR_ID (+)
AND AC.REMIT_TO_SUPPLIER_SITE_ID = PVS1.VENDOR_SITE_ID (+)
AND PVS1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID (+)
AND HPS1.LOCATION_ID = HZL1.LOCATION_ID (+) /* BUG 7535348*/
AND PV1.PARTY_ID = HZP1.PARTY_ID (+)

Columns