eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: FII_BIS_APTEX_V

Product:	FII - Financial Intelligence (Obsolete)
Description:	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)

Columns

Name
SOURCE
REPORT_DATE
TYPE
CY_PTD
PY_PTD
CY_QTD
PY_QTD
CY_YTD
PY_YTD
SEQUENCE