eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AP_CHECKS_V

Source

View Text - Preformatted


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 , 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 , 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 , DECODE(AC.PARTY_ID,'', '********************************************************************************', HZP.PARTY_NAME) CURRENT_VENDOR_NAME , DECODE(AC.VENDOR_ID,'',DECODE(AC.PARTY_ID,'','******************************',NULL), PV.SEGMENT1) VENDOR_NUMBER , DECODE(AC.VENDOR_ID,'',DECODE(AC.PARTY_ID,'','******************************',NULL), PV.NUM_1099) NUM_1099 , DECODE(AC.VENDOR_SITE_ID,'',DECODE(AC.PARTY_ID,'','**************',NULL), DECODE(SIGN(AC.VENDOR_SITE_ID), -1, NULL,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 , CSL.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*/ ,NVL(AC.REMIT_TO_SUPPLIER_NAME, AC.VENDOR_NAME) REMIT_TO_SUPPLIER_NAME ,NVL(AC.REMIT_TO_SUPPLIER_ID, AC.VENDOR_ID) REMIT_TO_SUPPLIER_ID ,NVL(AC.REMIT_TO_SUPPLIER_SITE,AC.VENDOR_SITE_CODE) REMIT_TO_SUPPLIER_SITE ,NVL(AC.REMIT_TO_SUPPLIER_SITE_ID, AC.VENDOR_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 ,NVL(AC.RELATIONSHIP_ID, -1) RELATIONSHIP_ID, /* Bug 7535348*/ 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 PV, AP_SUPPLIER_SITES_ALL PVS, CE_STATEMENT_RECONCILS_ALL CSR, CE_STATEMENT_HEADERS CSH, CE_STATEMENT_LINES CSL, AP_CHECKS AC, GL_DAILY_CONVERSION_TYPES GDCT1, HZ_PARTIES HZP, HZ_PARTY_SITES HPS, HZ_LOCATIONS HZL, AP_SUPPLIERS PV1, AP_SUPPLIER_SITES_ALL PVS1, HZ_PARTY_SITES HPS1, HZ_LOCATIONS HZL1 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 = PV.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 CSR.REFERENCE_TYPE (+) = 'PAYMENT' AND CSR.REFERENCE_ID (+) = AC.CHECK_ID AND CSR.CURRENT_RECORD_FLAG (+) = 'Y' AND CSR.STATEMENT_LINE_ID = CSL.STATEMENT_LINE_ID (+) AND CSL.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID (+) AND CSR.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*/

View Text - HTML Formatted

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
, 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
, 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
, DECODE(AC.PARTY_ID
, ''
, '********************************************************************************'
, HZP.PARTY_NAME) CURRENT_VENDOR_NAME
, DECODE(AC.VENDOR_ID
, ''
, DECODE(AC.PARTY_ID
, ''
, '******************************'
, NULL)
, PV.SEGMENT1) VENDOR_NUMBER
, DECODE(AC.VENDOR_ID
, ''
, DECODE(AC.PARTY_ID
, ''
, '******************************'
, NULL)
, PV.NUM_1099) NUM_1099
, DECODE(AC.VENDOR_SITE_ID
, ''
, DECODE(AC.PARTY_ID
, ''
, '**************'
, NULL)
, DECODE(SIGN(AC.VENDOR_SITE_ID)
, -1
, NULL
, 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
, CSL.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*/
, NVL(AC.REMIT_TO_SUPPLIER_NAME
, AC.VENDOR_NAME) REMIT_TO_SUPPLIER_NAME
, NVL(AC.REMIT_TO_SUPPLIER_ID
, AC.VENDOR_ID) REMIT_TO_SUPPLIER_ID
, NVL(AC.REMIT_TO_SUPPLIER_SITE
, AC.VENDOR_SITE_CODE) REMIT_TO_SUPPLIER_SITE
, NVL(AC.REMIT_TO_SUPPLIER_SITE_ID
, AC.VENDOR_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
, NVL(AC.RELATIONSHIP_ID
, -1) RELATIONSHIP_ID
, /* BUG 7535348*/ 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 PV
, AP_SUPPLIER_SITES_ALL PVS
, CE_STATEMENT_RECONCILS_ALL CSR
, CE_STATEMENT_HEADERS CSH
, CE_STATEMENT_LINES CSL
, AP_CHECKS AC
, GL_DAILY_CONVERSION_TYPES GDCT1
, HZ_PARTIES HZP
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HZL
, AP_SUPPLIERS PV1
, AP_SUPPLIER_SITES_ALL PVS1
, HZ_PARTY_SITES HPS1
, HZ_LOCATIONS HZL1
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 = PV.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 CSR.REFERENCE_TYPE (+) = 'PAYMENT'
AND CSR.REFERENCE_ID (+) = AC.CHECK_ID
AND CSR.CURRENT_RECORD_FLAG (+) = 'Y'
AND CSR.STATEMENT_LINE_ID = CSL.STATEMENT_LINE_ID (+)
AND CSL.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID (+)
AND CSR.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*/