[Home] [Help]
View: GMS_STATUS_ACCUM_TASK_BASE_V
View Text
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
Columns
Name |
AWARD_ID |
PROJECT_ID |
TASK_ID |
TASK_NUMBER |
TASK_NAME |
TOTAL_REVENUE |
BUDGET_AMOUNT |
ACTUAL_COST |
REQUISITION_AMOUNT |
PURCHASE_ORDER_AMOUNT |
SUPPLIER_INVOICE_AMOUNT |
MANUAL_ENCUMBRANCE_AMOUNT |