Internal view used for AP Expense Transactions PM Viewer Reports
Implementation/DBA Data:
Not implemented in this database
View Text
SELECT SOURCE , ACCOUNTING_DATE REPORT_DATE , TYPE , DECODE(TYPE , 'COUNT' , COUNT_EXP_CY_PTD , 'AMOUNT' , SUM_EXP_AMT_CY_PTD , 'AVERAGE' , AVG_CY_PTD , 0) CY_PTD , DECODE(TYPE , 'COUNT' , COUNT_EXP_PY_PTD , 'AMOUNT' , SUM_EXP_AMT_PY_PTD , 'AVERAGE' , AVG_PY_PTD , 0) PY_PTD , DECODE(TYPE , 'COUNT' , COUNT_EXP_CY_QTD , 'AMOUNT' , SUM_EXP_AMT_CY_QTD , 'AVERAGE' , AVG_CY_QTD , 0) CY_QTD , DECODE(TYPE , 'COUNT' , COUNT_EXP_PY_QTD , 'AMOUNT' , SUM_EXP_AMT_PY_QTD , 'AVERAGE' , AVG_PY_QTD , 0) PY_QTD , DECODE(TYPE , 'COUNT' , COUNT_EXP_CY_YTD , 'AMOUNT' , SUM_EXP_AMT_CY_YTD , 'AVERAGE' , AVG_CY_YTD , 0) CY_YTD , DECODE(TYPE , 'COUNT' , COUNT_EXP_PY_YTD , 'AMOUNT' , SUM_EXP_AMT_PY_YTD , 'AVERAGE' , AVG_PY_YTD , 0) PY_YTD , DECODE(TYPE , 'COUNT' , 2 , 'AMOUNT' , 1 , 'AVERAGE' , 3 , 0) SEQUENCE FROM (SELECT I.SOURCE , TYPE.TYPE , PD.ACCOUNTING_DATE , COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.START_DATE AND PD.ACCOUNTING_DATE THEN I.INVOICE_ID END) COUNT_EXP_CY_PTD , SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.START_DATE AND PD.ACCOUNTING_DATE THEN D.AMOUNT ELSE 0 END) SUM_EXP_AMT_CY_PTD , COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.QUARTER_START_DATE AND PD.ACCOUNTING_DATE THEN I.INVOICE_ID END) COUNT_EXP_CY_QTD , SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.QUARTER_START_DATE AND PD.ACCOUNTING_DATE THEN D.AMOUNT ELSE 0 END) SUM_EXP_AMT_CY_QTD , 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.START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN I.INVOICE_ID END) COUNT_EXP_PY_PTD , SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN D.AMOUNT ELSE 0 END) SUM_EXP_AMT_PY_PTD , COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.QUARTER_START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN I.INVOICE_ID END) COUNT_EXP_PY_QTD , SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.QUARTER_START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN D.AMOUNT ELSE 0 END) SUM_EXP_AMT_PY_QTD , 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 , (SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.START_DATE AND PD.ACCOUNTING_DATE THEN D.AMOUNT ELSE 0 END))/(COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.START_DATE AND PD.ACCOUNTING_DATE THEN I.INVOICE_ID END) + 1.E-15) AVG_CY_PTD , (SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.QUARTER_START_DATE AND PD.ACCOUNTING_DATE THEN D.AMOUNT ELSE 0 END))/(COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.QUARTER_START_DATE AND PD.ACCOUNTING_DATE THEN I.INVOICE_ID END) + 1.E-15) AVG_CY_QTD , (SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.YEAR_START_DATE AND PD.ACCOUNTING_DATE THEN D.AMOUNT ELSE 0 END))/(COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN GP.YEAR_START_DATE AND PD.ACCOUNTING_DATE THEN I.INVOICE_ID END) + 1.E-15) AVG_CY_YTD , (SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN D.AMOUNT ELSE 0 END))/(COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN I.INVOICE_ID END) + 1.E-15) AVG_PY_PTD , (SUM(CASE WHEN D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.QUARTER_START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN D.AMOUNT ELSE 0 END))/(COUNT(DISTINCT CASE WHEN D.ACCOUNTING_DATE BETWEEN ADD_MONTHS(GP.QUARTER_START_DATE , -12) AND ADD_MONTHS(PD.ACCOUNTING_DATE , -12) THEN I.INVOICE_ID END) + 1.E-15) AVG_PY_QTD , (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))/(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) + 1.E-15) AVG_PY_YTD FROM AP_INVOICES_ALL I , AP_INVOICE_DISTRIBUTIONS_ALL D , AP_SYSTEM_PARAMETERS ASP , GL_SETS_OF_BOOKS GSOB , GL_PERIODS GP , GL_DATE_PERIOD_MAP PD , (SELECT 'COUNT' TYPE FROM DUAL UNION SELECT 'AMOUNT' TYPE FROM DUAL UNION SELECT 'AVERAGE' TYPE FROM DUAL) TYPE WHERE I.INVOICE_ID = D.INVOICE_ID AND I.INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT' 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(I.ORG_ID , -99) = NVL(ASP.ORG_ID , -99) AND NVL(D.ORG_ID , -99) = NVL(I.ORG_ID , -99) AND NVL(ASP.ORG_ID , -99) = NVL(D.ORG_ID , -99) GROUP BY I.SOURCE , TYPE.TYPE , PD.ACCOUNTING_DATE)