View: AP_AEL_GL_INV_V

View Text

SELECT R.JE_HEADER_ID JE_HEADER_ID
, R.JE_LINE_NUM JE_LINE_NUM
, 200 APPLICATION_ID
, AEH.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AEH.ORG_ID ORG_ID
, 'INV' TRX_CLASS
, L8.DISPLAYED_FIELD TRX_CLASS_NAME
, L1.DISPLAYED_FIELD TRX_TYPE_NAME
, I.INVOICE_TYPE_LOOKUP_CODE TRX_TYPE_C
, I.INVOICE_NUM TRX_NUMBER_C
, I.INVOICE_NUM TRX_NUMBER_DISPLAYED
, AEL.DESCRIPTION COMMENTS
, AEL.SUBLEDGER_DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, FD.NAME DOC_SEQUENCE_NAME
, AEL.SUBLEDGER_DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, AEL.ACCOUNTED_CR ACCOUNTED_CR
, AEL.ACCOUNTED_DR ACCOUNTED_DR
, AEH.ACCOUNTING_DATE ACCOUNTING_DATE
, AEL.AE_LINE_TYPE_CODE ACCT_LINE_TYPE
, L3.DISPLAYED_FIELD ACCT_LINE_TYPE_NAME
, AEH.AE_HEADER_ID AEH_ID
, AEL.AE_LINE_ID AEL_ID
, 'APID' AEL_TABLE
, DECODE(AEL.SOURCE_TABLE
, 'AP_INVOICES'
, L10.DISPLAYED_FIELD || ' ' || AE.EVENT_NUMBER || '
, ' || L11.DISPLAYED_FIELD|| ' ' || AEL.AE_LINE_NUMBER || '
, ' || L8.DISPLAYED_FIELD || ' ' || I.INVOICE_NUM
, L10.DISPLAYED_FIELD || ' '||AE.EVENT_NUMBER || '
, '|| L11.DISPLAYED_FIELD || ' ' || AEL.AE_LINE_NUMBER || '
, ' || L12.DISPLAYED_FIELD || ' ' || D.DISTRIBUTION_LINE_NUMBER ) AE_LINE_REFERENCE
, AEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, AEL.CURRENCY_CODE CURRENCY_CODE
, AEL.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, AEL.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, AEL.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, AEL.ENTERED_CR ENTERED_CR
, AEL.ENTERED_DR ENTERED_DR
, AEH.GL_TRANSFER_FLAG GL_TRANSFER_STATUS
, L4.DISPLAYED_FIELD GL_TRANSFER_STATUS_NAME
, L9.DISPLAYED_FIELD TRANSFER_STATUS_DETAIL_NAME
, AEL.SOURCE_ID SOURCE_ID
, AEL.SOURCE_TABLE SOURCE_TABLE
, AT.NAME TAX_CODE
, 'SUPPLIER' THIRD_PARTY_TYPE
, AEL.THIRD_PARTY_ID THIRD_PARTY_ID
, V.SEGMENT1 THIRD_PARTY_NUMBER
, V.VENDOR_NAME THIRD_PARTY_NAME
, AEL.THIRD_PARTY_SUB_ID THIRD_PARTY_SUB_ID
, VS.VENDOR_SITE_CODE THIRD_PARTY_SUB_NAME
, I.INVOICE_DATE TRX_DATE
, I.INVOICE_ID TRX_HDR_ID
, 'API' TRX_HDR_TABLE
, AEH.AE_CATEGORY JE_CATEGORY
, JC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, AEL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AEL.LAST_UPDATED_BY LAST_UPDATED_BY
, AEL.CREATION_DATE CREATION_DATE
, AEL.CREATED_BY CREATED_BY
, AEL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AEL.REQUEST_ID REQUEST_ID
, AEL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, AEL.PROGRAM_ID PROGRAM_ID
, AEL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, /*---------------------------------------------------------------*/ /* AP INVOICE SPECIFIC COLUMNS */ /*---------------------------------------------------------------*/ D.DISTRIBUTION_LINE_NUMBER TRX_LINE_NUMBER
, D.LINE_TYPE_LOOKUP_CODE TRX_LINE_TYPE
, L2.DISPLAYED_FIELD TRX_LINE_TYPE_NAME
, AE.EVENT_NUMBER ACCOUNTING_EVENT_NUMBER
, AE.EVENT_TYPE_CODE ACCOUNTING_EVENT_TYPE
, L5.DISPLAYED_FIELD ACCOUNTING_EVENT_TYPE_NAME
, AEL.AE_LINE_NUMBER ACCOUNTING_LINE_NUMBER
, AEH.ACCOUNTING_ERROR_CODE AEH_ACCOUNTING_ERROR_CODE
, L6.DISPLAYED_FIELD AEH_ACCOUNTING_ERROR_NAME
, AEL.ACCOUNTING_ERROR_CODE AEL_ACCOUNTING_ERROR_CODE
, L7.DISPLAYED_FIELD AEL_ACCOUNTING_ERROR_NAME
, I.INVOICE_CURRENCY_CODE TRX_HDR_CURRENCY
, PH.SEGMENT1 PO_ORDER_NUMBER
, PR.RELEASE_NUM PO_ORDER_RELEASE_NUM
, RSH.RECEIPT_NUM RCV_RECEIPT_NUM
FROM GL_IMPORT_REFERENCES R
, GL_JE_CATEGORIES JC
, AP_AE_LINES_ALL AEL
, AP_AE_HEADERS_ALL AEH
, AP_INVOICE_DISTRIBUTIONS_ALL D
, AP_INVOICES_ALL I
, AP_ACCOUNTING_EVENTS_ALL AE
, PO_RELEASES_ALL PR
, PO_HEADERS_ALL PH
, PO_DISTRIBUTIONS_ALL PD
, RCV_TRANSACTIONS RCT
, RCV_SHIPMENT_HEADERS RSH
, PO_VENDOR_SITES_ALL VS
, PO_VENDORS V
, AP_TAX_CODES_ALL AT
, GL_DAILY_CONVERSION_TYPES GLCT
, FND_DOCUMENT_SEQUENCES FD
, AP_LOOKUP_CODES L1
, AP_LOOKUP_CODES L2
, AP_LOOKUP_CODES L3
, AP_LOOKUP_CODES L4
, AP_LOOKUP_CODES L5
, AP_LOOKUP_CODES L6
, AP_LOOKUP_CODES L7
, AP_LOOKUP_CODES L8
, AP_LOOKUP_CODES L9
, AP_LOOKUP_CODES L10
, AP_LOOKUP_CODES L11
, AP_LOOKUP_CODES L12
WHERE L2.LOOKUP_CODE(+) = D.LINE_TYPE_LOOKUP_CODE
AND L2.LOOKUP_TYPE(+) = 'INVOICE DISTRIBUTION TYPE'
AND L3.LOOKUP_CODE = AEL.AE_LINE_TYPE_CODE
AND L3.LOOKUP_TYPE = 'AE LINE TYPE'
AND L4.LOOKUP_CODE = AEH.GL_TRANSFER_FLAG||''
AND L4.LOOKUP_CODE = AEH.GL_TRANSFER_FLAG||''
AND L4.LOOKUP_TYPE = 'POSTING STATUS'
AND L5.LOOKUP_CODE = AE.EVENT_TYPE_CODE
AND L5.LOOKUP_TYPE = 'EVENT TYPE'
AND L6.LOOKUP_CODE(+) = AEH.ACCOUNTING_ERROR_CODE
AND L6.LOOKUP_TYPE(+) = 'ACCOUNTING ERROR TYPE'
AND L7.LOOKUP_CODE(+) = AEL.ACCOUNTING_ERROR_CODE
AND L7.LOOKUP_TYPE(+) = 'ACCOUNTING ERROR TYPE'
AND L8.LOOKUP_CODE = 'INVOICES'
AND L8.LOOKUP_TYPE = 'POSTING CATEGORY'
AND L9.LOOKUP_CODE(+) = AEL.GL_TRANSFER_ERROR_CODE
AND L9.LOOKUP_TYPE(+) = 'POSTING EXCEPTIONS'
AND L10.LOOKUP_CODE = 'EVENT'
AND L10.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L11.LOOKUP_CODE = 'LINE'
AND L11.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND L12.LOOKUP_CODE = 'DIST_LINE_NUM'
AND L12.LOOKUP_TYPE = 'VIEW_ACCOUNTING'
AND JC.JE_CATEGORY_NAME = AEH.AE_CATEGORY
AND RSH.SHIPMENT_HEADER_ID (+) = RCT.SHIPMENT_HEADER_ID
AND D.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID (+)
AND PR.PO_RELEASE_ID(+) = PD.PO_RELEASE_ID
AND PH.PO_HEADER_ID(+) = PD.PO_HEADER_ID
AND PD.PO_DISTRIBUTION_ID(+) = D.PO_DISTRIBUTION_ID
AND AEL.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE(+)
AND AEL.SUBLEDGER_DOC_SEQUENCE_ID = FD.DOC_SEQUENCE_ID (+)
AND AEL.THIRD_PARTY_SUB_ID = VS.VENDOR_SITE_ID
AND AEL.THIRD_PARTY_ID = V.VENDOR_ID
AND L1.LOOKUP_CODE = I.INVOICE_TYPE_LOOKUP_CODE
AND L1.LOOKUP_TYPE = 'INVOICE TYPE'
AND AT.TAX_ID(+) = AEL.TAX_CODE_ID
AND AE.ACCOUNTING_EVENT_ID = AEH.ACCOUNTING_EVENT_ID
AND I.INVOICE_ID = AE.SOURCE_ID
AND AE.SOURCE_TABLE = 'AP_INVOICES'
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND DECODE(AEL.SOURCE_TABLE
, 'AP_INVOICE_DISTRIBUTIONS'
, AEL.SOURCE_ID
, NULL) = D.INVOICE_DISTRIBUTION_ID(+)
AND AEL.GL_SL_LINK_ID = R.GL_SL_LINK_ID

Columns