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_STATUS_AWARD_ACCUM_BASE_V

Source

View Text - Preformatted


SELECT GA.award_id , GA.award_number , GA.award_short_name , GA.status , sum(nvl(GB.revenue_period_to_date,0)* decode(balance_type,'REV',1,0) ) , sum(nvl(GB.budget_period_to_date,0)* decode(balance_type,'BGT',1,0)) , sum(nvl(GB.actual_period_to_date,0)* decode(balance_type,'EXP',1,0)) , sum(nvl(GB.encumb_period_to_date,0)* decode(balance_type, 'REQ' , 1, 0)) , sum(nvl(GB.encumb_period_to_date,0)* decode(balance_type, 'PO', 1, 0)) , sum(nvl(GB.encumb_period_to_date,0)* decode(balance_type, 'AP' , 1, 0)) , sum(nvl(GB.encumb_period_to_date,0)* decode(balance_type, 'ENC' , 1, 0)) , ga.type , ga.funding_source_id , ga.award_organization_id , ga.org_id FROM GMS_BALANCES GB , GMS_AWARDS GA , GMS_BUDGET_VERSIONS GBV WHERE GBV.award_id = GA.award_id and GBV.budget_version_id = gb.budget_version_id and GBV.current_flag in ('Y','R') and GBV.budget_status_code = 'B' group by GA.award_id , GA.award_number , GA.award_short_name , GA.status , ga.type , ga.funding_source_id , ga.award_organization_id , ga.org_id union all select GBC.award_id , GA.award_number , GA.award_short_name , GA.status , 0 , 0 , sum(((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'EXP',1,0))) , sum((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'REQ',1,0)) , sum((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'PO',1,0)) , sum((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'AP',1,0)) , sum((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'ENC',1,0)) , ga.type , ga.funding_source_id , ga.award_organization_id , ga.org_id from gms_bc_packets gbc , gms_awards ga , GMS_BUDGET_VERSIONS GBV where gbc.award_id = ga.award_id and gbc.status_code = 'A' and gbv.budget_version_id = gbc.budget_version_id and GBV.budget_status_code = 'B' and GBV.current_flag in ('Y','R') group by GBC.award_id , GA.award_number , GA.award_short_name , GA.status ,ga.type , ga.funding_source_id , ga.award_organization_id , ga.org_id union all select ga.award_id , ga.award_number , ga.award_short_name , ga.status , sum(pe.revenue_amount) , 0 , 0 , 0 , 0 , 0 , 0 , ga.type , ga.funding_source_id , ga.award_organization_id , ga.org_id from gms_awards ga , pa_events pe where pe.project_id = ga.award_project_id and pe.revenue_distributed_flag='Y' and pe.event_type = 'Manual' group by ga.award_id , ga.award_number , ga.award_short_name , ga.status , ga.type , ga.funding_source_id , ga.award_organization_id , ga.org_id

View Text - HTML Formatted

SELECT GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, SUM(NVL(GB.REVENUE_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'REV'
, 1
, 0) )
, SUM(NVL(GB.BUDGET_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'BGT'
, 1
, 0))
, SUM(NVL(GB.ACTUAL_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'EXP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'REQ'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'PO'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'AP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(BALANCE_TYPE
, 'ENC'
, 1
, 0))
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID
FROM GMS_BALANCES GB
, GMS_AWARDS GA
, GMS_BUDGET_VERSIONS GBV
WHERE GBV.AWARD_ID = GA.AWARD_ID
AND GBV.BUDGET_VERSION_ID = GB.BUDGET_VERSION_ID
AND GBV.CURRENT_FLAG IN ('Y'
, 'R')
AND GBV.BUDGET_STATUS_CODE = 'B' GROUP BY GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID UNION ALL SELECT GBC.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, 0
, 0
, SUM(((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0)))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'REQ'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'PO'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'AP'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'ENC'
, 1
, 0))
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID
FROM GMS_BC_PACKETS GBC
, GMS_AWARDS GA
, GMS_BUDGET_VERSIONS GBV
WHERE GBC.AWARD_ID = GA.AWARD_ID
AND GBC.STATUS_CODE = 'A'
AND GBV.BUDGET_VERSION_ID = GBC.BUDGET_VERSION_ID
AND GBV.BUDGET_STATUS_CODE = 'B'
AND GBV.CURRENT_FLAG IN ('Y'
, 'R') GROUP BY GBC.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID UNION ALL SELECT GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, SUM(PE.REVENUE_AMOUNT)
, 0
, 0
, 0
, 0
, 0
, 0
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID
FROM GMS_AWARDS GA
, PA_EVENTS PE
WHERE PE.PROJECT_ID = GA.AWARD_PROJECT_ID
AND PE.REVENUE_DISTRIBUTED_FLAG='Y'
AND PE.EVENT_TYPE = 'MANUAL' GROUP BY GA.AWARD_ID
, GA.AWARD_NUMBER
, GA.AWARD_SHORT_NAME
, GA.STATUS
, GA.TYPE
, GA.FUNDING_SOURCE_ID
, GA.AWARD_ORGANIZATION_ID
, GA.ORG_ID