VIEW: APPS.AP_INVOICES_INTERFACE_V

Source

View Text - Preformatted

SELECT AII.ROWID , AII.ATTRIBUTE1 , AII.ATTRIBUTE10 , AII.ATTRIBUTE11 , AII.ATTRIBUTE12 , AII.ATTRIBUTE13 , AII.ATTRIBUTE14 , AII.ATTRIBUTE15 , AII.ATTRIBUTE2 , AII.ATTRIBUTE3 , AII.ATTRIBUTE4 , AII.ATTRIBUTE5 , AII.ATTRIBUTE6 , AII.ATTRIBUTE7 , AII.ATTRIBUTE8 , AII.ATTRIBUTE9 , AII.ATTRIBUTE_CATEGORY , AII.GLOBAL_ATTRIBUTE10 , AII.GLOBAL_ATTRIBUTE11 , AII.GLOBAL_ATTRIBUTE12 , AII.GLOBAL_ATTRIBUTE13 , AII.GLOBAL_ATTRIBUTE14 , AII.GLOBAL_ATTRIBUTE15 , AII.GLOBAL_ATTRIBUTE16 , AII.GLOBAL_ATTRIBUTE17 , AII.GLOBAL_ATTRIBUTE18 , AII.GLOBAL_ATTRIBUTE19 , AII.GLOBAL_ATTRIBUTE20 , AII.GLOBAL_ATTRIBUTE1 , AII.GLOBAL_ATTRIBUTE2 , AII.GLOBAL_ATTRIBUTE3 , AII.GLOBAL_ATTRIBUTE4 , AII.GLOBAL_ATTRIBUTE5 , AII.GLOBAL_ATTRIBUTE6 , AII.GLOBAL_ATTRIBUTE7 , AII.GLOBAL_ATTRIBUTE8 , AII.GLOBAL_ATTRIBUTE9 , AII.GLOBAL_ATTRIBUTE_CATEGORY , AII.AWT_GROUP_ID , AII.CREATED_BY , AII.CREATION_DATE , AII.DESCRIPTION , AII.DOC_CATEGORY_CODE , AII.EXCHANGE_DATE , AII.EXCHANGE_RATE , AII.EXCHANGE_RATE_TYPE , GDCT.USER_CONVERSION_TYPE , AII.GOODS_RECEIVED_DATE , AII.INVOICE_AMOUNT , AII.INVOICE_CURRENCY_CODE , AII.INVOICE_DATE , AII.INVOICE_ID , AII.INVOICE_NUM , AII.INVOICE_RECEIVED_DATE , AII.INVOICE_TYPE_LOOKUP_CODE , AII.LAST_UPDATED_BY , AII.LAST_UPDATE_DATE , AII.LAST_UPDATE_LOGIN , AII.PAY_GROUP_LOOKUP_CODE , AII.PO_NUMBER , AII.SOURCE , AII.GROUP_ID , AII.TERMS_ID , AII.VENDOR_ID , AII.VENDOR_SITE_ID , AII.VOUCHER_NUM , AII.STATUS , AII.REQUEST_ID , AII.WORKFLOW_FLAG , AII.GL_DATE , AII.ACCTS_PAY_CODE_COMBINATION_ID , AII.EXCLUSIVE_PAYMENT_FLAG , ALC1.DISPLAYED_FIELD INVOICE_TYPE , ALC3.DISPLAYED_FIELD STATUS_DSP , ALC4.DISPLAYED_FIELD SOURCE_DSP , DECODE(AII.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT','', AT.NAME) , FDSC.NAME DOC_CATEGORY_NAME , PV.VENDOR_NAME , PV.SEGMENT1 VENDOR_NUMBER , PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE , PVS.INVOICE_AMOUNT_LIMIT , AAG.NAME AWT_GROUP_NAME , AII.ORG_ID , AII.PAYMENT_CROSS_RATE_TYPE , AII.PAYMENT_CROSS_RATE , AII.PAYMENT_CROSS_RATE_DATE , AII.PAYMENT_CURRENCY_CODE , AII.AMOUNT_APPLICABLE_TO_DISCOUNT , AII.PREPAY_NUM , AII.PREPAY_APPLY_AMOUNT , AII.PREPAY_GL_DATE , AII.INVOICE_INCLUDES_PREPAY_FLAG , AII.NO_XRATE_BASE_AMOUNT , AII.TERMS_DATE , REQUESTER_ID , AII.OPERATING_UNIT , AII.PREPAY_LINE_NUM , AII.REQUESTER_FIRST_NAME , AII.REQUESTER_LAST_NAME , AII.REQUESTER_EMPLOYEE_NUM , AII.LEGAL_ENTITY_ID , AII.CALC_TAX_DURING_IMPORT_FLAG , AII.CONTROL_AMOUNT , AII.ADD_TAX_TO_INV_AMT_FLAG , AII.TAX_RELATED_INVOICE_ID , AII.TAXATION_COUNTRY , AII.DOCUMENT_SUB_TYPE , AII.SUPPLIER_TAX_INVOICE_NUMBER , AII.SUPPLIER_TAX_INVOICE_DATE , AII.SUPPLIER_TAX_EXCHANGE_RATE , AII.TAX_INVOICE_RECORDING_DATE , AII.TAX_INVOICE_INTERNAL_SEQ ,AII.PAYMENT_METHOD_CODE ,IBY1.PAYMENT_METHOD_NAME ,AII.PAYMENT_REASON_CODE ,IBY2.meaning ,AII.PAYMENT_REASON_COMMENTS ,AII.UNIQUE_REMITTANCE_IDENTIFIER ,AII.URI_CHECK_DIGIT ,AII.REMITTANCE_MESSAGE1 ,AII.REMITTANCE_MESSAGE2 ,AII.REMITTANCE_MESSAGE3 ,AII.BANK_CHARGE_BEARER ,IBY3.MEANING ,AII.DELIVERY_CHANNEL_CODE ,IBY4.MEANING ,AII.SETTLEMENT_PRIORITY ,IBY5.MEANING ,PV.PARTY_ID ,PVS.PARTY_SITE_ID ,AII.NET_OF_RETAINAGE_FLAG ,AII.PORT_OF_ENTRY_CODE ,aii.external_bank_account_id ,ibybnk.bank_account_name ,ibybnk.bank_account_number ,AII.PAY_AWT_GROUP_ID PAY_AWT_GROUP_ID ,AAG1.NAME PAY_AWT_GROUP_NAME /* Bug 7535348*/ ,AII.REMIT_TO_SUPPLIER_NAME ,AII.REMIT_TO_SUPPLIER_ID ,AII.REMIT_TO_SUPPLIER_SITE ,AII.REMIT_TO_SUPPLIER_SITE_ID ,AII.RELATIONSHIP_ID /* Bug 7535348*/ ,AII.REMIT_TO_SUPPLIER_NUM /* bug 7587574 */ FROM AP_INVOICES_INTERFACE AII , AP_LOOKUP_CODES ALC1 , AP_LOOKUP_CODES ALC3 , AP_LOOKUP_CODES ALC4 , AP_TERMS AT , FND_DOC_SEQUENCE_CATEGORIES FDSC , GL_DAILY_CONVERSION_TYPES GDCT , PO_VENDORS PV , PO_VENDOR_SITES PVS , AP_AWT_GROUPS AAG , AP_AWT_GROUPS AAG1, iby_payment_methods_vl iby1, iby_payment_reasons_vl iby2, fnd_lookups iby3, iby_delivery_channels_vl iby4, fnd_lookups iby5, iby_ext_bank_accounts_v ibybnk WHERE AII.TERMS_ID = AT.TERM_ID (+) AND ALC1.LOOKUP_TYPE (+) = 'INVOICE TYPE' AND ALC1.LOOKUP_CODE (+) = AII.INVOICE_TYPE_LOOKUP_CODE AND ALC3.LOOKUP_TYPE (+) = 'NLS TRANSLATION' AND ALC3.LOOKUP_CODE (+) = AII.STATUS AND ALC4.LOOKUP_TYPE (+) = 'SOURCE' AND ALC4.LOOKUP_CODE (+) = AII.SOURCE AND AII.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE (+) AND AII.DOC_CATEGORY_CODE = FDSC.CODE (+) AND FDSC.APPLICATION_ID (+) = 200 AND AII.VENDOR_ID = PV.VENDOR_ID AND AII.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND AII.AWT_GROUP_ID = AAG.GROUP_ID (+) AND AII.PAY_AWT_GROUP_ID=AAG1.GROUP_ID(+) AND AII.PAYMENT_METHOD_CODE = IBY1.PAYMENT_METHOD_CODE (+) AND AII.PAYMENT_REASON_CODE = IBY2.PAYMENT_REASON_CODE (+) AND AII.BANK_CHARGE_BEARER = IBY3.LOOKUP_CODE(+) AND IBY3.LOOKUP_TYPE (+) = 'IBY_BANK_CHARGE_BEARER' AND AII.DELIVERY_CHANNEL_CODE = IBY4.DELIVERY_CHANNEL_CODE(+) AND AII.SETTLEMENT_PRIORITY = IBY5.LOOKUP_CODE (+) AND IBY5.LOOKUP_TYPE (+) = 'IBY_SETTLEMENT_PRIORITY' AND IBYBNK.BANK_ACCOUNT_ID (+) = AII.EXTERNAL_BANK_ACCOUNT_ID

View Text - HTML Formatted

SELECT AII.ROWID
, AII.ATTRIBUTE1
, AII.ATTRIBUTE10
, AII.ATTRIBUTE11
, AII.ATTRIBUTE12
, AII.ATTRIBUTE13
, AII.ATTRIBUTE14
, AII.ATTRIBUTE15
, AII.ATTRIBUTE2
, AII.ATTRIBUTE3
, AII.ATTRIBUTE4
, AII.ATTRIBUTE5
, AII.ATTRIBUTE6
, AII.ATTRIBUTE7
, AII.ATTRIBUTE8
, AII.ATTRIBUTE9
, AII.ATTRIBUTE_CATEGORY
, AII.GLOBAL_ATTRIBUTE10
, AII.GLOBAL_ATTRIBUTE11
, AII.GLOBAL_ATTRIBUTE12
, AII.GLOBAL_ATTRIBUTE13
, AII.GLOBAL_ATTRIBUTE14
, AII.GLOBAL_ATTRIBUTE15
, AII.GLOBAL_ATTRIBUTE16
, AII.GLOBAL_ATTRIBUTE17
, AII.GLOBAL_ATTRIBUTE18
, AII.GLOBAL_ATTRIBUTE19
, AII.GLOBAL_ATTRIBUTE20
, AII.GLOBAL_ATTRIBUTE1
, AII.GLOBAL_ATTRIBUTE2
, AII.GLOBAL_ATTRIBUTE3
, AII.GLOBAL_ATTRIBUTE4
, AII.GLOBAL_ATTRIBUTE5
, AII.GLOBAL_ATTRIBUTE6
, AII.GLOBAL_ATTRIBUTE7
, AII.GLOBAL_ATTRIBUTE8
, AII.GLOBAL_ATTRIBUTE9
, AII.GLOBAL_ATTRIBUTE_CATEGORY
, AII.AWT_GROUP_ID
, AII.CREATED_BY
, AII.CREATION_DATE
, AII.DESCRIPTION
, AII.DOC_CATEGORY_CODE
, AII.EXCHANGE_DATE
, AII.EXCHANGE_RATE
, AII.EXCHANGE_RATE_TYPE
, GDCT.USER_CONVERSION_TYPE
, AII.GOODS_RECEIVED_DATE
, AII.INVOICE_AMOUNT
, AII.INVOICE_CURRENCY_CODE
, AII.INVOICE_DATE
, AII.INVOICE_ID
, AII.INVOICE_NUM
, AII.INVOICE_RECEIVED_DATE
, AII.INVOICE_TYPE_LOOKUP_CODE
, AII.LAST_UPDATED_BY
, AII.LAST_UPDATE_DATE
, AII.LAST_UPDATE_LOGIN
, AII.PAY_GROUP_LOOKUP_CODE
, AII.PO_NUMBER
, AII.SOURCE
, AII.GROUP_ID
, AII.TERMS_ID
, AII.VENDOR_ID
, AII.VENDOR_SITE_ID
, AII.VOUCHER_NUM
, AII.STATUS
, AII.REQUEST_ID
, AII.WORKFLOW_FLAG
, AII.GL_DATE
, AII.ACCTS_PAY_CODE_COMBINATION_ID
, AII.EXCLUSIVE_PAYMENT_FLAG
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC3.DISPLAYED_FIELD STATUS_DSP
, ALC4.DISPLAYED_FIELD SOURCE_DSP
, DECODE(AII.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, ''
, AT.NAME)
, FDSC.NAME DOC_CATEGORY_NAME
, PV.VENDOR_NAME
, PV.SEGMENT1 VENDOR_NUMBER
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, PVS.INVOICE_AMOUNT_LIMIT
, AAG.NAME AWT_GROUP_NAME
, AII.ORG_ID
, AII.PAYMENT_CROSS_RATE_TYPE
, AII.PAYMENT_CROSS_RATE
, AII.PAYMENT_CROSS_RATE_DATE
, AII.PAYMENT_CURRENCY_CODE
, AII.AMOUNT_APPLICABLE_TO_DISCOUNT
, AII.PREPAY_NUM
, AII.PREPAY_APPLY_AMOUNT
, AII.PREPAY_GL_DATE
, AII.INVOICE_INCLUDES_PREPAY_FLAG
, AII.NO_XRATE_BASE_AMOUNT
, AII.TERMS_DATE
, REQUESTER_ID
, AII.OPERATING_UNIT
, AII.PREPAY_LINE_NUM
, AII.REQUESTER_FIRST_NAME
, AII.REQUESTER_LAST_NAME
, AII.REQUESTER_EMPLOYEE_NUM
, AII.LEGAL_ENTITY_ID
, AII.CALC_TAX_DURING_IMPORT_FLAG
, AII.CONTROL_AMOUNT
, AII.ADD_TAX_TO_INV_AMT_FLAG
, AII.TAX_RELATED_INVOICE_ID
, AII.TAXATION_COUNTRY
, AII.DOCUMENT_SUB_TYPE
, AII.SUPPLIER_TAX_INVOICE_NUMBER
, AII.SUPPLIER_TAX_INVOICE_DATE
, AII.SUPPLIER_TAX_EXCHANGE_RATE
, AII.TAX_INVOICE_RECORDING_DATE
, AII.TAX_INVOICE_INTERNAL_SEQ
, AII.PAYMENT_METHOD_CODE
, IBY1.PAYMENT_METHOD_NAME
, AII.PAYMENT_REASON_CODE
, IBY2.MEANING
, AII.PAYMENT_REASON_COMMENTS
, AII.UNIQUE_REMITTANCE_IDENTIFIER
, AII.URI_CHECK_DIGIT
, AII.REMITTANCE_MESSAGE1
, AII.REMITTANCE_MESSAGE2
, AII.REMITTANCE_MESSAGE3
, AII.BANK_CHARGE_BEARER
, IBY3.MEANING
, AII.DELIVERY_CHANNEL_CODE
, IBY4.MEANING
, AII.SETTLEMENT_PRIORITY
, IBY5.MEANING
, PV.PARTY_ID
, PVS.PARTY_SITE_ID
, AII.NET_OF_RETAINAGE_FLAG
, AII.PORT_OF_ENTRY_CODE
, AII.EXTERNAL_BANK_ACCOUNT_ID
, IBYBNK.BANK_ACCOUNT_NAME
, IBYBNK.BANK_ACCOUNT_NUMBER
, AII.PAY_AWT_GROUP_ID PAY_AWT_GROUP_ID
, AAG1.NAME PAY_AWT_GROUP_NAME /* BUG 7535348*/
, AII.REMIT_TO_SUPPLIER_NAME
, AII.REMIT_TO_SUPPLIER_ID
, AII.REMIT_TO_SUPPLIER_SITE
, AII.REMIT_TO_SUPPLIER_SITE_ID
, AII.RELATIONSHIP_ID /* BUG 7535348*/
, AII.REMIT_TO_SUPPLIER_NUM /* BUG 7587574 */
FROM AP_INVOICES_INTERFACE AII
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC3
, AP_LOOKUP_CODES ALC4
, AP_TERMS AT
, FND_DOC_SEQUENCE_CATEGORIES FDSC
, GL_DAILY_CONVERSION_TYPES GDCT
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
, AP_AWT_GROUPS AAG
, AP_AWT_GROUPS AAG1
, IBY_PAYMENT_METHODS_VL IBY1
, IBY_PAYMENT_REASONS_VL IBY2
, FND_LOOKUPS IBY3
, IBY_DELIVERY_CHANNELS_VL IBY4
, FND_LOOKUPS IBY5
, IBY_EXT_BANK_ACCOUNTS_V IBYBNK
WHERE AII.TERMS_ID = AT.TERM_ID (+)
AND ALC1.LOOKUP_TYPE (+) = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE (+) = AII.INVOICE_TYPE_LOOKUP_CODE
AND ALC3.LOOKUP_TYPE (+) = 'NLS TRANSLATION'
AND ALC3.LOOKUP_CODE (+) = AII.STATUS
AND ALC4.LOOKUP_TYPE (+) = 'SOURCE'
AND ALC4.LOOKUP_CODE (+) = AII.SOURCE
AND AII.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE (+)
AND AII.DOC_CATEGORY_CODE = FDSC.CODE (+)
AND FDSC.APPLICATION_ID (+) = 200
AND AII.VENDOR_ID = PV.VENDOR_ID
AND AII.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AII.AWT_GROUP_ID = AAG.GROUP_ID (+)
AND AII.PAY_AWT_GROUP_ID=AAG1.GROUP_ID(+)
AND AII.PAYMENT_METHOD_CODE = IBY1.PAYMENT_METHOD_CODE (+)
AND AII.PAYMENT_REASON_CODE = IBY2.PAYMENT_REASON_CODE (+)
AND AII.BANK_CHARGE_BEARER = IBY3.LOOKUP_CODE(+)
AND IBY3.LOOKUP_TYPE (+) = 'IBY_BANK_CHARGE_BEARER'
AND AII.DELIVERY_CHANNEL_CODE = IBY4.DELIVERY_CHANNEL_CODE(+)
AND AII.SETTLEMENT_PRIORITY = IBY5.LOOKUP_CODE (+)
AND IBY5.LOOKUP_TYPE (+) = 'IBY_SETTLEMENT_PRIORITY'
AND IBYBNK.BANK_ACCOUNT_ID (+) = AII.EXTERNAL_BANK_ACCOUNT_ID