View: FV_LINK_GL_TO_PAYMENTS_V

View Text

SELECT V.VENDOR_ID VENDOR_ID
, V.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE
, V.ATTRIBUTE9 ELIMINATIONS_ID
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.CODE_COMBINATION_ID CCID
, GJL.SET_OF_BOOKS_ID SOB
, GLC.CHART_OF_ACCOUNTS_ID COA
, GLP.PERIOD_YEAR
, GLP.PERIOD_NUM
FROM AP_PAYMENT_DISTRIBUTIONS_ALL PD
, AP_INVOICE_PAYMENTS_ALL P
, AP_CHECKS_ALL C
, AP_INVOICES_ALL I
, GL_PERIODS GLP
, GL_JE_HEADERS GJH
, GL_CODE_COMBINATIONS GLC
, GL_JE_LINES GJL
, PO_VENDORS V
, AP_LOOKUP_CODES L
WHERE L.LOOKUP_CODE = PD.LINE_TYPE_LOOKUP_CODE
AND L.LOOKUP_TYPE = 'PAY_DIST_TYPES'
AND (NVL(C.PAYMENT_METHOD_LOOKUP_CODE
, 'OLD') NOT IN ('FUTURE DATED'
, 'MANUAL FUTURE DATED') OR (NVL(C.PAYMENT_METHOD_LOOKUP_CODE
, 'OLD') IN ('FUTURE DATED'
, 'MANUAL FUTURE DATED')
AND NVL(P.FUTURE_PAY_POSTED_FLAG
, 'N') = 'N'))
AND C.CHECK_ID = P.CHECK_ID
AND PD.INVOICE_PAYMENT_ID = P.INVOICE_PAYMENT_ID
AND P.POSTED_FLAG = 'Y'
AND V.VENDOR_ID = I.VENDOR_ID
AND P.INVOICE_ID = I.INVOICE_ID
AND TO_CHAR(I.INVOICE_ID) = GJL.REFERENCE_2
AND TO_CHAR( P.CHECK_ID) = GJL.REFERENCE_3
AND TO_CHAR(PD.INVOICE_DISTR_LINE_NUMBER) = GJL.REFERENCE_8
AND TO_CHAR(P.INVOICE_PAYMENT_ID) = GJL.REFERENCE_9
AND GJL.REFERENCE_6 = 'AP PAYMENTS'
AND PD.LINE_TYPE_LOOKUP_CODE = DECODE(GJL.REFERENCE_10
, 'LIABILITY'
, PD.LINE_TYPE_LOOKUP_CODE
, 'FUTURE PAY'
, 'CASH'
, GJL.REFERENCE_10)
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.ACTUAL_FLAG = 'A'
AND GJL.STATUS = 'P'
AND GJL.PERIOD_NAME = GLP.PERIOD_NAME
AND GJL.CODE_COMBINATION_ID = GLC.CODE_COMBINATION_ID
AND GLC.ACCOUNT_TYPE <> 'C'
AND GLC.ACCOUNT_TYPE <> 'D'
AND GLC.TEMPLATE_ID IS NULL

Columns