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.

VIEW: APPS.PA_PROJECT_BAL_V

Source

View Text - Preformatted


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)

View Text - HTML Formatted

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)