VIEW: APPS.GMS_STATUS_ACCUM_TASK_BASE_V

Source

View Text - Preformatted

SELECT GB.award_id, GB.project_id, PT.task_id, PT.task_number, PT.task_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_TASKS_V PT, GMS_BUDGET_VERSIONS GBV WHERE GB.project_id = PT.project_id and GB.task_id = PT.task_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, PT.task_id, PT.task_number, PT.task_name union all select Gbc.award_id, Gbc.project_id, Pt.task_id, pt.task_number, pt.task_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_tasks_v pt, 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.task_id = pt.task_id and gbc.project_id = pt.project_id group by Gbc.award_id, Gbc.project_id, Pt.task_id, pt.task_number, pt.task_name

View Text - HTML Formatted

SELECT GB.AWARD_ID
, GB.PROJECT_ID
, PT.TASK_ID
, PT.TASK_NUMBER
, PT.TASK_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_TASKS_V PT
, GMS_BUDGET_VERSIONS GBV
WHERE GB.PROJECT_ID = PT.PROJECT_ID
AND GB.TASK_ID = PT.TASK_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
, PT.TASK_ID
, PT.TASK_NUMBER
, PT.TASK_NAME UNION ALL SELECT GBC.AWARD_ID
, GBC.PROJECT_ID
, PT.TASK_ID
, PT.TASK_NUMBER
, PT.TASK_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_TASKS_V PT
, 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.TASK_ID = PT.TASK_ID
AND GBC.PROJECT_ID = PT.PROJECT_ID GROUP BY GBC.AWARD_ID
, GBC.PROJECT_ID
, PT.TASK_ID
, PT.TASK_NUMBER
, PT.TASK_NAME