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.GMS_AWARD_BAL_V

Source

View Text - Preformatted


SELECT project_id, award_id, NVL (task_id, 0), resource_list_member_id, budget_version_id, parent_member_id, start_date, end_date, SUM (NVL (budget_period_to_date, 0)) budget, SUM (NVL (actual_period_to_date, 0)) actuals, SUM (NVL (encumb_period_to_date, 0)) encumbrances, NVL (top_task_id, 0) FROM gms_balances WHERE balance_type IN ('AP', 'BGT', 'ENC', 'EXP', 'PO', 'REQ') GROUP BY budget_version_id, award_id, task_id, project_id, resource_list_member_id, parent_member_id, start_date, end_date, top_task_id UNION ALL SELECT gbc.project_id, gbc.award_id, gbc.task_id, gbc.resource_list_member_id, gbc.budget_version_id, gbc.parent_resource_id, pap.start_date, pap.end_date, 0 budget, SUM (( NVL (gbc.entered_dr, 0) - NVL (gbc.entered_cr, 0) ) * DECODE (gbc.document_type, 'EXP', 1, 0)) actuals, SUM (( NVL (gbc.entered_dr, 0) - NVL (gbc.entered_cr, 0) ) * DECODE (gbc.document_type, 'EXP', 0, 1)) encumbrances, gbc.top_task_id FROM pa_periods pap, gms_bc_packets gbc WHERE gbc.status_code = 'A' AND gbc.time_phased_type_code = 'P' AND gbc.expenditure_item_date BETWEEN pap.start_date AND pap.end_date GROUP BY gbc.budget_version_id, gbc.award_id, gbc.task_id, gbc.project_id, gbc.top_task_id, gbc.resource_list_member_id, gbc.parent_resource_id, pap.start_date, pap.end_date UNION ALL SELECT gbc.project_id, gbc.award_id, gbc.task_id, gbc.resource_list_member_id, gbc.budget_version_id, gbc.parent_resource_id, gps.start_date, gps.end_date, 0 budget, SUM (( NVL (gbc.entered_dr, 0) - NVL (gbc.entered_cr, 0) ) * DECODE (gbc.document_type, 'EXP', 1, 0)) actuals, SUM (( NVL (gbc.entered_dr, 0) - NVL (gbc.entered_cr, 0) ) * DECODE (gbc.document_type, 'EXP', 0, 1)) encumbrances, gbc.top_task_id FROM gl_period_statuses gps, gms_bc_packets gbc WHERE gbc.status_code = 'A' AND gbc.time_phased_type_code = 'G' AND gps.application_id = 101 AND gps.set_of_books_id = gbc.set_of_books_id AND gps.adjustment_period_flag = 'N' AND gbc.expenditure_item_date BETWEEN gps.start_date AND gps.end_date GROUP BY gbc.budget_version_id, gbc.award_id, gbc.task_id, gbc.project_id, gbc.top_task_id, gbc.resource_list_member_id, gbc.parent_resource_id, gps.start_date, gps.end_date UNION ALL SELECT gbc.project_id, gbc.award_id, gbc.task_id, gbc.resource_list_member_id, gbc.budget_version_id, gbc.parent_resource_id, NVL ( gb.start_date, DECODE ( bem.entry_level_code, 'P', GREATEST (NVL (ga.preaward_date, ga.start_date_active), NVL (pp.start_date, NVL (ga.preaward_date, ga.start_date_active))), GREATEST ( NVL (ga.preaward_date, ga.start_date_active), NVL (pp.start_date, NVL (ga.preaward_date, ga.start_date_active)), NVL (pt.start_date, NVL (ga.preaward_date, ga.start_date_active)) ) ) ), NVL ( gb.end_date, DECODE ( bem.entry_level_code, 'P', LEAST (ga.end_date_active, NVL (pp.completion_date, ga.end_date_active)), LEAST (ga.end_date_active, NVL (pp.completion_date, ga.end_date_active), NVL (pt.completion_date, ga.end_date_active)) ) ), 0 budget, SUM (( NVL (gbc.entered_dr, 0) - NVL (gbc.entered_cr, 0) ) * DECODE (gbc.document_type, 'EXP', 1, 0)) actuals, SUM (( NVL (gbc.entered_dr, 0) - NVL (gbc.entered_cr, 0) ) * DECODE (gbc.document_type, 'EXP', 0, 1)) encumbrances, gbc.top_task_id FROM pa_budget_entry_methods bem, gms_budget_versions gbv, gms_awards ga, gms_bc_packets gbc, pa_projects_all pp, pa_tasks pt, gms_none_bal_v gb WHERE gbc.status_code = 'A' AND gbv.budget_version_id = gbc.budget_version_id AND bem.budget_entry_method_code = gbv.budget_entry_method_code AND bem.time_phased_type_code = 'N' AND ga.award_id = gbc.award_id AND pp.project_id = gbc.project_id AND pt.task_id = gbc.task_id AND gb.project_id(+) = gbc.project_id AND gb.award_id(+) = gbc.award_id AND gb.budget_version_id(+) = gbc.budget_version_id AND gb.task_id(+) = gbc.bud_task_id AND gbc.expenditure_item_date BETWEEN NVL (gb.start_date, gbc.expenditure_item_date) AND NVL (gb.end_date, gbc.expenditure_item_date) GROUP BY gbc.budget_version_id, gbc.award_id, gbc.task_id, gbc.top_task_id, gbc.project_id, gbc.resource_list_member_id, gbc.parent_resource_id, NVL ( gb.start_date, DECODE ( bem.entry_level_code, 'P', GREATEST (NVL (ga.preaward_date, ga.start_date_active), NVL (pp.start_date, NVL (ga.preaward_date, ga.start_date_active))), GREATEST ( NVL (ga.preaward_date, ga.start_date_active), NVL (pp.start_date, NVL (ga.preaward_date, ga.start_date_active)), NVL (pt.start_date, NVL (ga.preaward_date, ga.start_date_active)) ) ) ), NVL ( gb.end_date, DECODE ( bem.entry_level_code, 'P', LEAST (ga.end_date_active, NVL (pp.completion_date, ga.end_date_active)), LEAST (ga.end_date_active, NVL (pp.completion_date, ga.end_date_active), NVL (pt.completion_date, ga.end_date_active)) ) ) UNION ALL SELECT gbc.project_id, gbc.award_id, gbc.task_id, gbc.resource_list_member_id, gbc.budget_version_id, gbc.parent_resource_id, nvl(gb.start_date,gps.start_date), nvl(gb.end_date,gps.end_date), 0 budget, SUM (( NVL (gbc.entered_dr, 0) - NVL (gbc.entered_cr, 0) ) * DECODE (gbc.document_type, 'EXP', 1, 0)) actuals, SUM (( NVL (gbc.entered_dr, 0) - NVL (gbc.entered_cr, 0) ) * DECODE (gbc.document_type, 'EXP', 0, 1)) encumbrances, gbc.top_task_id FROM gms_dtrange_bal_v gb, gms_bc_packets gbc, gl_period_statuses gps WHERE gbc.status_code = 'A' AND gbc.time_phased_type_code = 'R' AND gb.project_id(+) = gbc.project_id AND gb.award_id (+) = gbc.award_id AND gb.budget_version_id (+) = gbc.budget_version_id AND gps.application_id = 101 AND gps.set_of_books_id = gbc.set_of_books_id AND gps.adjustment_period_flag = 'N' AND gbc.period_name = gps.period_name AND gbc.expenditure_item_date BETWEEN gb.start_date(+) AND gb.end_date(+) GROUP BY gbc.budget_version_id, gbc.award_id, gbc.task_id, gbc.project_id, gbc.top_task_id, gbc.resource_list_member_id, gbc.parent_resource_id, nvl(gb.start_date,gps.start_date), nvl(gb.end_date,gps.end_date)

View Text - HTML Formatted

SELECT PROJECT_ID
, AWARD_ID
, NVL (TASK_ID
, 0)
, RESOURCE_LIST_MEMBER_ID
, BUDGET_VERSION_ID
, PARENT_MEMBER_ID
, START_DATE
, END_DATE
, SUM (NVL (BUDGET_PERIOD_TO_DATE
, 0)) BUDGET
, SUM (NVL (ACTUAL_PERIOD_TO_DATE
, 0)) ACTUALS
, SUM (NVL (ENCUMB_PERIOD_TO_DATE
, 0)) ENCUMBRANCES
, NVL (TOP_TASK_ID
, 0)
FROM GMS_BALANCES
WHERE BALANCE_TYPE IN ('AP'
, 'BGT'
, 'ENC'
, 'EXP'
, 'PO'
, 'REQ') GROUP BY BUDGET_VERSION_ID
, AWARD_ID
, TASK_ID
, PROJECT_ID
, RESOURCE_LIST_MEMBER_ID
, PARENT_MEMBER_ID
, START_DATE
, END_DATE
, TOP_TASK_ID UNION ALL SELECT GBC.PROJECT_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.BUDGET_VERSION_ID
, GBC.PARENT_RESOURCE_ID
, PAP.START_DATE
, PAP.END_DATE
, 0 BUDGET
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, GBC.TOP_TASK_ID
FROM PA_PERIODS PAP
, GMS_BC_PACKETS GBC
WHERE GBC.STATUS_CODE = 'A'
AND GBC.TIME_PHASED_TYPE_CODE = 'P'
AND GBC.EXPENDITURE_ITEM_DATE BETWEEN PAP.START_DATE
AND PAP.END_DATE GROUP BY GBC.BUDGET_VERSION_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.PROJECT_ID
, GBC.TOP_TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.PARENT_RESOURCE_ID
, PAP.START_DATE
, PAP.END_DATE UNION ALL SELECT GBC.PROJECT_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.BUDGET_VERSION_ID
, GBC.PARENT_RESOURCE_ID
, GPS.START_DATE
, GPS.END_DATE
, 0 BUDGET
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, GBC.TOP_TASK_ID
FROM GL_PERIOD_STATUSES GPS
, GMS_BC_PACKETS GBC
WHERE GBC.STATUS_CODE = 'A'
AND GBC.TIME_PHASED_TYPE_CODE = 'G'
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = GBC.SET_OF_BOOKS_ID
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GBC.EXPENDITURE_ITEM_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE GROUP BY GBC.BUDGET_VERSION_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.PROJECT_ID
, GBC.TOP_TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.PARENT_RESOURCE_ID
, GPS.START_DATE
, GPS.END_DATE UNION ALL SELECT GBC.PROJECT_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.BUDGET_VERSION_ID
, GBC.PARENT_RESOURCE_ID
, NVL ( GB.START_DATE
, DECODE ( BEM.ENTRY_LEVEL_CODE
, 'P'
, GREATEST (NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)
, NVL (PP.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)))
, GREATEST ( NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)
, NVL (PP.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE))
, NVL (PT.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)) ) ) )
, NVL ( GB.END_DATE
, DECODE ( BEM.ENTRY_LEVEL_CODE
, 'P'
, LEAST (GA.END_DATE_ACTIVE
, NVL (PP.COMPLETION_DATE
, GA.END_DATE_ACTIVE))
, LEAST (GA.END_DATE_ACTIVE
, NVL (PP.COMPLETION_DATE
, GA.END_DATE_ACTIVE)
, NVL (PT.COMPLETION_DATE
, GA.END_DATE_ACTIVE)) ) )
, 0 BUDGET
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, GBC.TOP_TASK_ID
FROM PA_BUDGET_ENTRY_METHODS BEM
, GMS_BUDGET_VERSIONS GBV
, GMS_AWARDS GA
, GMS_BC_PACKETS GBC
, PA_PROJECTS_ALL PP
, PA_TASKS PT
, GMS_NONE_BAL_V GB
WHERE GBC.STATUS_CODE = 'A'
AND GBV.BUDGET_VERSION_ID = GBC.BUDGET_VERSION_ID
AND BEM.BUDGET_ENTRY_METHOD_CODE = GBV.BUDGET_ENTRY_METHOD_CODE
AND BEM.TIME_PHASED_TYPE_CODE = 'N'
AND GA.AWARD_ID = GBC.AWARD_ID
AND PP.PROJECT_ID = GBC.PROJECT_ID
AND PT.TASK_ID = GBC.TASK_ID
AND GB.PROJECT_ID(+) = GBC.PROJECT_ID
AND GB.AWARD_ID(+) = GBC.AWARD_ID
AND GB.BUDGET_VERSION_ID(+) = GBC.BUDGET_VERSION_ID
AND GB.TASK_ID(+) = GBC.BUD_TASK_ID
AND GBC.EXPENDITURE_ITEM_DATE BETWEEN NVL (GB.START_DATE
, GBC.EXPENDITURE_ITEM_DATE)
AND NVL (GB.END_DATE
, GBC.EXPENDITURE_ITEM_DATE) GROUP BY GBC.BUDGET_VERSION_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.TOP_TASK_ID
, GBC.PROJECT_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.PARENT_RESOURCE_ID
, NVL ( GB.START_DATE
, DECODE ( BEM.ENTRY_LEVEL_CODE
, 'P'
, GREATEST (NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)
, NVL (PP.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)))
, GREATEST ( NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)
, NVL (PP.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE))
, NVL (PT.START_DATE
, NVL (GA.PREAWARD_DATE
, GA.START_DATE_ACTIVE)) ) ) )
, NVL ( GB.END_DATE
, DECODE ( BEM.ENTRY_LEVEL_CODE
, 'P'
, LEAST (GA.END_DATE_ACTIVE
, NVL (PP.COMPLETION_DATE
, GA.END_DATE_ACTIVE))
, LEAST (GA.END_DATE_ACTIVE
, NVL (PP.COMPLETION_DATE
, GA.END_DATE_ACTIVE)
, NVL (PT.COMPLETION_DATE
, GA.END_DATE_ACTIVE)) ) ) UNION ALL SELECT GBC.PROJECT_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.BUDGET_VERSION_ID
, GBC.PARENT_RESOURCE_ID
, NVL(GB.START_DATE
, GPS.START_DATE)
, NVL(GB.END_DATE
, GPS.END_DATE)
, 0 BUDGET
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)) ACTUALS
, SUM (( NVL (GBC.ENTERED_DR
, 0) - NVL (GBC.ENTERED_CR
, 0) ) * DECODE (GBC.DOCUMENT_TYPE
, 'EXP'
, 0
, 1)) ENCUMBRANCES
, GBC.TOP_TASK_ID
FROM GMS_DTRANGE_BAL_V GB
, GMS_BC_PACKETS GBC
, GL_PERIOD_STATUSES GPS
WHERE GBC.STATUS_CODE = 'A'
AND GBC.TIME_PHASED_TYPE_CODE = 'R'
AND GB.PROJECT_ID(+) = GBC.PROJECT_ID
AND GB.AWARD_ID (+) = GBC.AWARD_ID
AND GB.BUDGET_VERSION_ID (+) = GBC.BUDGET_VERSION_ID
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = GBC.SET_OF_BOOKS_ID
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GBC.PERIOD_NAME = GPS.PERIOD_NAME
AND GBC.EXPENDITURE_ITEM_DATE BETWEEN GB.START_DATE(+)
AND GB.END_DATE(+) GROUP BY GBC.BUDGET_VERSION_ID
, GBC.AWARD_ID
, GBC.TASK_ID
, GBC.PROJECT_ID
, GBC.TOP_TASK_ID
, GBC.RESOURCE_LIST_MEMBER_ID
, GBC.PARENT_RESOURCE_ID
, NVL(GB.START_DATE
, GPS.START_DATE)
, NVL(GB.END_DATE
, GPS.END_DATE)