[Home] [Help]
View: AP_ENC_GL_PAY_V
View Text
SELECT R.JE_HEADER_ID JE_HEADER_ID
,
R.JE_LINE_NUM JE_LINE_NUM
,
200 APPLICATION_ID
,
D.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
,
D.ORG_ID ORG_ID
,
'PAY' TRX_CLASS
,
NULL TRX_CLASS_NAME
,
IPMV.PAYMENT_METHOD_NAME TRX_TYPE_NAME
,
C.PAYMENT_METHOD_LOOKUP_CODE TRX_TYPE_C
,
C.CHECK_NUMBER TRX_NUMBER_N
,
TO_CHAR(C.CHECK_NUMBER) TRX_NUMBER_DISPLAYED
,
R.SUBLEDGER_DOC_SEQUENCE_ID DOC_SEQUENCE_ID
,
NULL DOC_SEQUENCE_NAME
,
R.SUBLEDGER_DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
,
JEL.ACCOUNTED_CR ACCOUNTED_CR
,
JEL.ACCOUNTED_DR ACCOUNTED_DR
,
JEL.ENTERED_CR ENTERED_CR
,
JEL.ENTERED_DR ENTERED_DR
,
TO_DATE(NULL) ACCOUNTING_DATE
,
NULL ACCT_LINE_TYPE
,
NULL ACCT_LINE_TYPE_NAME
,
TO_NUMBER(NULL) AEH_ID
,
TO_NUMBER(NULL) AEL_ID
,
NULL AEL_TABLE
,
NULL AE_LINE_REFERENCE
,
JEL.CODE_COMBINATION_ID CODE_COMBINATION_ID
,
JEH.CURRENCY_CODE CURRENCY_CODE
,
JEH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
,
JEH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
,
JEH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
,
NULL CURRENCY_USER_CONVERSION_TYPE
,
NULL GL_TRANSFER_STATUS
,
NULL GL_TRANSFER_STATUS_NAME
,
NULL TRANSFER_STATUS_DETAIL_NAME
,
TO_NUMBER(NULL) SOURCE_ID
,
NULL SOURCE_TABLE
,
AT.NAME TAX_CODE
,
'SUPPLIER' THIRD_PARTY_TYPE
,
TO_NUMBER(NULL) THIRD_PARTY_ID
,
V.SEGMENT1 THIRD_PARTY_NUMBER
,
V.VENDOR_NAME THIRD_PARTY_NAME
,
NULL THIRD_PARTY_SUB_ID
,
NULL THIRD_PARTY_SUB_NAME
,
C.CHECK_DATE TRX_DATE
,
C.CHECK_ID TRX_HDR_ID
,
'APC' TRX_HDR_TABLE
,
JEH.JE_CATEGORY JE_CATEGORY
,
NULL USER_JE_CATEGORY_NAME
,
C.LAST_UPDATE_DATE LAST_UPDATE_DATE
,
C.LAST_UPDATED_BY LAST_UPDATED_BY
,
C.CREATION_DATE CREATION_DATE
,
C.CREATED_BY CREATED_BY
,
C.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
,
D.REQUEST_ID REQUEST_ID
,
D.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
,
D.PROGRAM_ID PROGRAM_ID
,
D.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
,
TO_NUMBER(NULL) ACCOUNTING_EVENT_NUMBER
,
NULL ACCOUNTING_EVENT_TYPE
,
NULL ACCOUNTING_EVENT_TYPE_NAME
,
TO_NUMBER(NULL) ACCOUNTING_LINE_NUMBER
,
JEH.DESCRIPTION COMMENTS
,
I.INVOICE_TYPE_LOOKUP_CODE TRX_TYPE_C_I
,
I.INVOICE_NUM APPLIED_TO_TRX_HDR_NUMBER_DISP
,
I.INVOICE_NUM APPLIED_TO_TRX_HDR_NUMBER_C
,
NULL AEH_ACCOUNTING_ERROR_CODE
,
NULL AEH_ACCOUNTING_ERROR_NAME
,
NULL AEL_ACCOUNTING_ERROR_CODE
,
NULL AEL_ACCOUNTING_ERROR_NAME
,
C.CURRENCY_CODE TRX_HDR_CURRENCY
,
/*---------------------------------------------------------------*/
/* AP PAYMENT SPECIFIC COLUMNS */
/*---------------------------------------------------------------*/
C.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME
,
'AP_INVOICES' APPLIED_TO_TRX_HDR_TABLE
,
I.INVOICE_ID APPLIED_TO_TRX_HDR_ID
,
I.INVOICE_DATE APPLIED_TO_TRX_DATE
,
I.INVOICE_CURRENCY_CODE APPLIED_TO_TRX_HDR_CURRENCY
,
/*---------------------------------------------------------------*/
/* COLUMNS FOR CASH BASIS ACCOUNTING */
/*---------------------------------------------------------------*/
D.OLD_DIST_LINE_NUMBER TRX_LINE_NUMBER
,
D.LINE_TYPE_LOOKUP_CODE TRX_LINE_TYPE
,
L.DISPLAYED_FIELD TRX_LINE_TYPE_NAME
,
PH.SEGMENT1 PO_ORDER_NUMBER
,
TO_NUMBER(NULL) PO_ORDER_RELEASE_NUM
,
TO_NUMBER(NULL) RCV_RECEIPT_NUM
,
/*---------------------------------------------------------------*/
/* AP RECONCILIATION SPECIFIC COLUMNS */
/*---------------------------------------------------------------*/
C.CLEARED_DATE PAYMENT_CLEARED_DATE
,
SH.STATEMENT_NUMBER BANK_STATEMENT_NUMBER
,
NULL PAYMENT_RECON_CURRENCY
,
NULL BANK_STATEMENT_LINE_NUMBER
,
SH.DOC_SEQUENCE_ID BANK_STATEMENT_DOC_SEQ_ID
,
NULL BANK_STATEMENT_DOC_SEQ_NAME
,
SH.DOC_SEQUENCE_VALUE BANK_STATEMENT_DOC_SEQ_VALUE
FROM GL_LOOKUPS L2
,
AP_LOOKUP_CODES L
,
PO_HEADERS_ALL PH
,
PO_DISTRIBUTIONS_ALL PD
,
PO_VENDORS V
,
AP_INVOICE_DISTRIBUTIONS_ALL D
,
AP_INVOICES_ALL I
,
GL_JE_HEADERS JEH
,
GL_JE_LINES JEL
,
GL_IMPORT_REFERENCES R
,
IBY_PAYMENT_METHODS_VL IPMV
,
AP_TAX_CODES_ALL AT
,
AP_CHECKS_ALL C
,
CE_STATEMENT_LINES SL
,
CE_STATEMENT_HEADERS SH
,
CE_STATEMENT_RECONCILIATIONS CSR
,
AP_ENCUMBRANCE_LINES_ALL AEL
WHERE L2.LOOKUP_TYPE = 'SUBLDGR_DRILLDOWN_TRANS_TYPE'
AND L2.LOOKUP_CODE = NVL(R.REFERENCE_10
, 'CASH')
AND L.LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
AND L.LOOKUP_CODE = D.LINE_TYPE_LOOKUP_CODE
AND IPMV.PAYMENT_METHOD_CODE = C.PAYMENT_METHOD_CODE
AND PH.PO_HEADER_ID(+) = PD.PO_HEADER_ID
AND PD.PO_DISTRIBUTION_ID(+) = D.PO_DISTRIBUTION_ID
AND I.INVOICE_ID = D.INVOICE_ID
AND I.VENDOR_ID = V.VENDOR_ID
AND JEH.JE_SOURCE ='PAYABLES'
AND JEH.JE_HEADER_ID = JEL.JE_HEADER_ID
AND JEL.JE_HEADER_ID = R.JE_HEADER_ID
AND JEL.JE_LINE_NUM = R.JE_LINE_NUM
AND AT.TAX_ID(+) = D.TAX_CODE_ID
AND SL.STATEMENT_HEADER_ID = SH.STATEMENT_HEADER_ID(+)
AND CSR.STATEMENT_LINE_ID = SL.STATEMENT_LINE_ID(+)
AND C.CHECK_ID = CSR.REFERENCE_ID(+)
AND CSR.REFERENCE_TYPE(+) = 'PAYMENT'
AND CSR.CURRENT_RECORD_FLAG(+) = 'Y'
AND AEL.INVOICE_DISTRIBUTION_ID = D.OLD_DISTRIBUTION_ID
AND R.GL_SL_LINK_ID = AEL.GL_SL_LINK_ID
AND TO_CHAR(C.CHECK_ID) = R.REFERENCE_3
AND TO_CHAR(I.INVOICE_ID) = R.REFERENCE_2