View: PA_PROJECT_BAL_V

Product:	PA - Projects
Description:	Master View for Budget Funds Check Results - Source for views created on various Levels
Implementation/DBA Data:	APPS.PA_PROJECT_BAL_V

View Text

SELECT PABAL.PROJECT_ID
, PABAL.TASK_ID
, PABAL.RESOURCE_LIST_MEMBER_ID
, PABAL.BUDGET_VERSION_ID
, PABAL.PARENT_MEMBER_ID
, PABAL.START_DATE
, PABAL.END_DATE
, SUM(NVL(PABAL.BUDGET_PERIOD_TO_DATE
, 0)) BUDGET
, SUM(NVL(PABAL.ACTUAL_PERIOD_TO_DATE
, 0)) ACTUALS
, SUM(NVL(PABAL.ENCUMB_PERIOD_TO_DATE
, 0)) ENCUMBRANCES
, TOP_TASK_ID
, PABAL.BALANCE_TYPE ENCUMB_TYPE
FROM PA_BC_BALANCES PABAL GROUP BY PABAL.BUDGET_VERSION_ID
, PABAL.TASK_ID
, PABAL.PROJECT_ID
, PABAL.RESOURCE_LIST_MEMBER_ID
, PABAL.PARENT_MEMBER_ID
, PABAL.START_DATE
, PABAL.END_DATE
, PABAL.TOP_TASK_ID
, PABAL.BALANCE_TYPE UNION ALL SELECT PABCP.PROJECT_ID
, PABCP.TASK_ID
, PABCP.RESOURCE_LIST_MEMBER_ID
, PABCP.BUDGET_VERSION_ID
, PABCP.PARENT_RESOURCE_ID
, PAPER.START_DATE
, PAPER.END_DATE
, 0 BUDGET
, SUM((NVL(PABCP.ACCOUNTED_DR
, 0) - NVL(PABCP.ACCOUNTED_CR
, 0)) * DECODE(PABCP.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM((NVL(PABCP.ACCOUNTED_DR
, 0) - NVL(PABCP.ACCOUNTED_CR
, 0)) * DECODE(PABCP.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, PABCP.TOP_TASK_ID
, PABCP.DOCUMENT_TYPE ENCUMB_TYPE
FROM PA_PERIODS PAPER
, PA_BUDGET_ENTRY_METHODS PABEM
, PA_BUDGET_VERSIONS PABV
, PA_BC_PACKETS PABCP
WHERE (PABCP.STATUS_CODE = DECODE(PA_BUDGETARY_CONTROLS_PKG.GET_BUDGET_STATUS(PABCP.BUDGET_VERSION_ID)
, 'B'
, 'A'
, PABCP.STATUS_CODE) OR PABCP.STATUS_CODE = 'C')
AND PABCP.STATUS_CODE NOT IN('P'
, 'T'
, 'X')
AND PABV.BUDGET_VERSION_ID = PABCP.BUDGET_VERSION_ID
AND PABEM.BUDGET_ENTRY_METHOD_CODE = PABV.BUDGET_ENTRY_METHOD_CODE
AND PABEM.TIME_PHASED_TYPE_CODE = 'P'
AND PABCP.PA_DATE BETWEEN PAPER.START_DATE
AND PAPER.END_DATE GROUP BY PABCP.BUDGET_VERSION_ID
, PABCP.TASK_ID
, PABCP.PROJECT_ID
, PABCP.TOP_TASK_ID
, PABCP.DOCUMENT_TYPE
, PABCP.RESOURCE_LIST_MEMBER_ID
, PABCP.PARENT_RESOURCE_ID
, PAPER.START_DATE
, PAPER.END_DATE UNION ALL SELECT PABCP.PROJECT_ID
, PABCP.TASK_ID
, PABCP.RESOURCE_LIST_MEMBER_ID
, PABCP.BUDGET_VERSION_ID
, PABCP.PARENT_RESOURCE_ID
, PAGPS.START_DATE
, PAGPS.END_DATE
, 0 BUDGET
, SUM((NVL(PABCP.ACCOUNTED_DR
, 0) - NVL(PABCP.ACCOUNTED_CR
, 0)) * DECODE(PABCP.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM((NVL(PABCP.ACCOUNTED_DR
, 0) - NVL(PABCP.ACCOUNTED_CR
, 0)) * DECODE(PABCP.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, PABCP.TOP_TASK_ID
, PABCP.DOCUMENT_TYPE ENCUMB_TYPE
FROM GL_PERIOD_STATUSES PAGPS
, PA_BUDGET_ENTRY_METHODS PABEM
, PA_BUDGET_VERSIONS PABV
, PA_BC_PACKETS PABCP
WHERE (PABCP.STATUS_CODE = DECODE(PA_BUDGETARY_CONTROLS_PKG.GET_BUDGET_STATUS(PABCP.BUDGET_VERSION_ID)
, 'B'
, 'A'
, PABCP.STATUS_CODE) OR PABCP.STATUS_CODE = 'C')
AND PABCP.STATUS_CODE NOT IN('P'
, 'T'
, 'X')
AND PABV.BUDGET_VERSION_ID = PABCP.BUDGET_VERSION_ID
AND PABEM.BUDGET_ENTRY_METHOD_CODE = PABV.BUDGET_ENTRY_METHOD_CODE
AND PABEM.TIME_PHASED_TYPE_CODE = 'G'
AND PAGPS.APPLICATION_ID = 101
AND PAGPS.SET_OF_BOOKS_ID = PABCP.SET_OF_BOOKS_ID
AND PAGPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND PABCP.GL_DATE BETWEEN PAGPS.START_DATE
AND PAGPS.END_DATE GROUP BY PABCP.BUDGET_VERSION_ID
, PABCP.TASK_ID
, PABCP.PROJECT_ID
, PABCP.TOP_TASK_ID
, PABCP.DOCUMENT_TYPE
, PABCP.RESOURCE_LIST_MEMBER_ID
, PABCP.PARENT_RESOURCE_ID
, PAGPS.START_DATE
, PAGPS.END_DATE UNION ALL SELECT PABCP.PROJECT_ID
, PABCP.TASK_ID
, PABCP.RESOURCE_LIST_MEMBER_ID
, PABCP.BUDGET_VERSION_ID
, PABCP.PARENT_RESOURCE_ID
, PAPRJ.START_DATE
, PAPRJ.COMPLETION_DATE
, 0 BUDGET
, SUM((NVL(PABCP.ACCOUNTED_DR
, 0) - NVL(PABCP.ACCOUNTED_CR
, 0)) * DECODE(PABCP.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM((NVL(PABCP.ACCOUNTED_DR
, 0) - NVL(PABCP.ACCOUNTED_CR
, 0)) * DECODE(PABCP.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, PABCP.TOP_TASK_ID
, PABCP.DOCUMENT_TYPE ENCUMB_TYPE
FROM PA_BUDGET_ENTRY_METHODS PABEM
, PA_PROJECTS_ALL PAPRJ
, PA_BUDGET_VERSIONS PABV
, PA_BC_PACKETS PABCP
WHERE (PABCP.STATUS_CODE = DECODE(PA_BUDGETARY_CONTROLS_PKG.GET_BUDGET_STATUS(PABCP.BUDGET_VERSION_ID)
, 'B'
, 'A'
, PABCP.STATUS_CODE) OR PABCP.STATUS_CODE = 'C')
AND PABCP.STATUS_CODE NOT IN('P'
, 'T'
, 'X')
AND PABV.BUDGET_VERSION_ID = PABCP.BUDGET_VERSION_ID
AND PABEM.BUDGET_ENTRY_METHOD_CODE = PABV.BUDGET_ENTRY_METHOD_CODE
AND PABEM.TIME_PHASED_TYPE_CODE = 'N'
AND PAPRJ.PROJECT_ID = PABV.PROJECT_ID GROUP BY PABCP.BUDGET_VERSION_ID
, PABCP.TASK_ID
, PABCP.TOP_TASK_ID
, PABCP.PROJECT_ID
, PABCP.RESOURCE_LIST_MEMBER_ID
, PABCP.PARENT_RESOURCE_ID
, PABCP.DOCUMENT_TYPE
, PAPRJ.START_DATE
, PAPRJ.COMPLETION_DATE UNION ALL SELECT PABCP.PROJECT_ID
, PABCP.TASK_ID
, PABCP.RESOURCE_LIST_MEMBER_ID
, PABCP.BUDGET_VERSION_ID
, PABCP.PARENT_RESOURCE_ID
, NVL(PABAL.START_DATE
, PABCP.EXPENDITURE_ITEM_DATE)
, NVL(PABAL.END_DATE
, PABCP.EXPENDITURE_ITEM_DATE)
, 0 BUDGET
, SUM((NVL(PABCP.ACCOUNTED_DR
, 0) - NVL(PABCP.ACCOUNTED_CR
, 0)) * DECODE(PABCP.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM((NVL(PABCP.ACCOUNTED_DR
, 0) - NVL(PABCP.ACCOUNTED_CR
, 0)) * DECODE(PABCP.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, PABCP.TOP_TASK_ID
, PABCP.DOCUMENT_TYPE ENCUMB_TYPE
FROM PA_BUDGET_ENTRY_METHODS PABEM
, PA_BC_BALANCES PABAL
, PA_BUDGET_VERSIONS PABV
, PA_BC_PACKETS PABCP
WHERE (PABCP.STATUS_CODE = DECODE(PA_BUDGETARY_CONTROLS_PKG.GET_BUDGET_STATUS(PABCP.BUDGET_VERSION_ID)
, 'B'
, 'A'
, PABCP.STATUS_CODE) OR PABCP.STATUS_CODE = 'C')
AND PABCP.STATUS_CODE NOT IN('P'
, 'T'
, 'X')
AND PABV.BUDGET_VERSION_ID = PABCP.BUDGET_VERSION_ID
AND PABEM.BUDGET_ENTRY_METHOD_CODE = PABV.BUDGET_ENTRY_METHOD_CODE
AND PABEM.TIME_PHASED_TYPE_CODE = 'R'
AND PABAL.BUDGET_VERSION_ID (+) = PABCP.BUDGET_VERSION_ID
AND PABAL.RESOURCE_LIST_MEMBER_ID (+) = PABCP.RESOURCE_LIST_MEMBER_ID
AND PABAL.TASK_ID (+) = PABCP.TASK_ID
AND PABCP.EXPENDITURE_ITEM_DATE BETWEEN NVL(PABAL.START_DATE
, PABCP.EXPENDITURE_ITEM_DATE)
AND NVL(PABAL.END_DATE
, PABCP.EXPENDITURE_ITEM_DATE) GROUP BY PABCP.BUDGET_VERSION_ID
, PABCP.TASK_ID
, PABCP.DOCUMENT_TYPE
, PABCP.PROJECT_ID
, PABCP.TOP_TASK_ID
, PABCP.RESOURCE_LIST_MEMBER_ID
, PABCP.PARENT_RESOURCE_ID
, NVL(PABAL.START_DATE
, PABCP.EXPENDITURE_ITEM_DATE)
, NVL(PABAL.END_DATE
, PABCP.EXPENDITURE_ITEM_DATE)

Columns