Internal view used for AP Expense Transactions Lines PM Viewer Reports
Implementation/DBA Data:
Not implemented in this database
View Text
SELECT I.SOURCE , PD.ACCOUNTING_DATE , GP2.PERIOD_NUM PERIOD_NUMBER , GP2.PERIOD_NAME PERIOD_NAME , COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.YEAR_START_DATE AND PD.ACCOUNTING_DATE THEN I.INVOICE_ID END) COUNT_EXP_CY_YTD , SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.YEAR_START_DATE AND PD.ACCOUNTING_DATE THEN D.AMOUNT ELSE 0 END) SUM_EXP_AMT_CY_YTD , COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.YEAR_START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN I.INVOICE_ID END) COUNT_EXP_PY_YTD , SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.YEAR_START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN D.AMOUNT ELSE 0 END) SUM_EXP_AMT_PY_YTD FROM AP_INVOICES_ALL I , AP_INVOICE_DISTRIBUTIONS_ALL D , AP_SYSTEM_PARAMETERS_ALL ASP , GL_SETS_OF_BOOKS GSOB , GL_PERIODS GP , GL_DATE_PERIOD_MAP PD , GL_PERIODS GP2 WHERE I.INVOICE_ID = D.INVOICE_ID AND I.SOURCE IN ('XPENSEXPRESS' , 'ORACLE PROJECT ACCOUNTING') AND (D.ACCOUNTING_DATE BETWEEN GP.YEAR_START_DATE AND PD.ACCOUNTING_DATE OR D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.YEAR_START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12)) AND D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.YEAR_START_DATE , -12) AND PD.ACCOUNTING_DATE AND GSOB.SET_OF_BOOKS_ID = ASP.SET_OF_BOOKS_ID AND GP.PERIOD_SET_NAME = GSOB.PERIOD_SET_NAME AND GP.PERIOD_TYPE = GSOB.ACCOUNTED_PERIOD_TYPE AND PD.PERIOD_SET_NAME = GP.PERIOD_SET_NAME AND PD.PERIOD_NAME = GP.PERIOD_NAME AND NVL(ASP.ORG_ID , -999) = NVL(I.ORG_ID , -999) AND NVL(I.ORG_ID , -999) = NVL(D.ORG_ID , -999) AND NVL(D.ORG_ID , -999) = NVL(ASP.ORG_ID , -999) AND GP2.PERIOD_SET_NAME = GP.PERIOD_SET_NAME AND GP2.PERIOD_TYPE = GP.PERIOD_TYPE AND D.ACCOUNTING_DATE BETWEEN GP2.START_DATE AND GP2.END_DATE GROUP BY I.SOURCE , PD.ACCOUNTING_DATE , GP2.PERIOD_NUM , GP2.PERIOD_NAME
[Home] [Help]