VIEW: APPS.GMS_STATUS_ACCUM_PROJ_BASE_V

Source

View Text - Preformatted

SELECT GB.award_id, GB.project_id, PPA.segment1, PPA.name, sum(nvl(GB.revenue_period_to_date,0)* decode(GB.balance_type,'REV',1,0) ), sum(nvl(GB.budget_period_to_date,0)* decode(GB.balance_type,'BGT',1,0)), sum(nvl(GB.actual_period_to_date,0)* decode(GB.balance_type,'EXP',1,0)), sum(nvl(GB.encumb_period_to_date,0)* decode(GB.balance_type,'REQ',1,0)), sum(nvl(GB.encumb_period_to_date,0)* decode(GB.balance_type,'PO',1,0)), sum(nvl(GB.encumb_period_to_date,0)* decode(GB.balance_type,'AP',1,0)), sum(nvl(GB.encumb_period_to_date,0)* decode(GB.balance_type, 'ENC',1,0)) FROM GMS_BALANCES GB, PA_PROJECTS_ALL PPA, GMS_BUDGET_VERSIONS GBV WHERE GB.project_id = PPA.project_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 GB.award_id, GB.project_id, PPA.segment1, PPA.name union all select ga.award_id, gea.actual_project_id, PPA.segment1, PPA.name, sum(pe.revenue_amount), 0, 0, 0, 0, 0, 0 from gms_awards_all ga, pa_events pe, gms_event_attribute gea, pa_projects_all ppa where pe.project_id = ga.award_project_id and pe.revenue_distributed_flag='Y' and pe.event_type = 'Manual' and gea.project_id = pe.project_id and gea.event_num = pe.event_num and ppa.project_id = gea.actual_project_id group by ga.award_id, gea.actual_project_id, PPA.segment1, PPA.name union all select Gbc.award_id, Gbc.project_id, PPA.segment1, ppa.name, 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)) from gms_bc_packets gbc, pa_projects_all ppa, GMS_BUDGET_VERSIONS GBV where 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') and gbc.project_id = ppa.project_id group by gbc.award_id, Gbc.project_id, PPA.segment1, ppa.name

View Text - HTML Formatted

SELECT GB.AWARD_ID
, GB.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, SUM(NVL(GB.REVENUE_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'REV'
, 1
, 0) )
, SUM(NVL(GB.BUDGET_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'BGT'
, 1
, 0))
, SUM(NVL(GB.ACTUAL_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'EXP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'REQ'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'PO'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'AP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'ENC'
, 1
, 0))
FROM GMS_BALANCES GB
, PA_PROJECTS_ALL PPA
, GMS_BUDGET_VERSIONS GBV
WHERE GB.PROJECT_ID = PPA.PROJECT_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 GB.AWARD_ID
, GB.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME UNION ALL SELECT GA.AWARD_ID
, GEA.ACTUAL_PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, SUM(PE.REVENUE_AMOUNT)
, 0
, 0
, 0
, 0
, 0
, 0
FROM GMS_AWARDS_ALL GA
, PA_EVENTS PE
, GMS_EVENT_ATTRIBUTE GEA
, PA_PROJECTS_ALL PPA
WHERE PE.PROJECT_ID = GA.AWARD_PROJECT_ID
AND PE.REVENUE_DISTRIBUTED_FLAG='Y'
AND PE.EVENT_TYPE = 'MANUAL'
AND GEA.PROJECT_ID = PE.PROJECT_ID
AND GEA.EVENT_NUM = PE.EVENT_NUM
AND PPA.PROJECT_ID = GEA.ACTUAL_PROJECT_ID GROUP BY GA.AWARD_ID
, GEA.ACTUAL_PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME UNION ALL SELECT GBC.AWARD_ID
, GBC.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, 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))
FROM GMS_BC_PACKETS GBC
, PA_PROJECTS_ALL PPA
, GMS_BUDGET_VERSIONS GBV
WHERE 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')
AND GBC.PROJECT_ID = PPA.PROJECT_ID GROUP BY GBC.AWARD_ID
, GBC.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME