View: GMS_FUNDSCTL_RES_V

Product:	GMS - Grants Accounting
Description:
Implementation/DBA Data:	APPS.GMS_FUNDSCTL_RES_V

View Text

SELECT BC.PACKET_ID
, BC.BC_PACKET_ID
, BC.PARENT_BC_PACKET_ID
, BC.REQUEST_ID
, BC.PROJECT_ID
, BC.AWARD_ID
, BC.TASK_ID
, BC.TOP_TASK_ID
, BC.EXPENDITURE_TYPE
, BC.EXPENDITURE_ITEM_DATE
, BC.ACTUAL_FLAG
, BC.STATUS_CODE
, BC.SET_OF_BOOKS_ID
, BC.JE_CATEGORY_NAME
, BC.JE_SOURCE_NAME
, BC.DOCUMENT_TYPE
, BC.EXPENDITURE_ORGANIZATION_ID
, BC.PERIOD_NAME
, BC.PERIOD_YEAR
, BC.PERIOD_NUM
, BC.DOCUMENT_HEADER_ID
, BC.DOCUMENT_DISTRIBUTION_ID
, BC.BUDGET_VERSION_ID
, BC.ACCOUNT_TYPE
, BC.ENTERED_DR
, BC.ENTERED_CR
, BC.EFFECT_ON_FUNDS_CODE
, BC.RESULT_CODE
, BC.GL_ROW_NUMBER
, BC.RESOURCE_LIST_MEMBER_ID
, BC.PARENT_RESOURCE_ID
, BC.BUD_RESOURCE_LIST_MEMBER_ID
, BC.BUD_TASK_ID
, BC.FUNDING_PATTERN_ID
, BC.FUNDING_SEQUENCE
, BC.FP_STATUS
, BC.R_FUNDS_CONTROL_LEVEL_CODE
, BC.RG_FUNDS_CONTROL_LEVEL_CODE
, BC.T_FUNDS_CONTROL_LEVEL_CODE
, BC.TT_FUNDS_CONTROL_LEVEL_CODE
, BC.A_FUNDS_CONTROL_LEVEL_CODE
, BC.AWARD_BUDGET_POSTED
, BC.AWARD_BUDGET_APPROVED
, BC.AWARD_BUDGET_PENDING
, TO_NUMBER(DECODE(AWARD_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.AWARD_BUDGET_POSTED
, 0)+NVL(BC.AWARD_BUDGET_APPROVED
, 0)+ NVL(BC.AWARD_BUDGET_PENDING
, 0))) TOTAL_AWARD_BUDGET
, BC.AWARD_ENC_POSTED
, BC.AWARD_ENC_APPROVED
, BC.AWARD_ENC_PENDING
, TO_NUMBER(DECODE(AWARD_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.AWARD_ENC_POSTED
, 0)+NVL(BC.AWARD_ENC_APPROVED
, 0)+ NVL(BC.AWARD_ENC_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'E'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_AWARD_ENCUMBRANCE
, BC.AWARD_ACTUAL_POSTED
, BC.AWARD_ACTUAL_APPROVED
, BC.AWARD_ACTUAL_PENDING
, TO_NUMBER(DECODE(AWARD_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.AWARD_ACTUAL_POSTED
, 0)+NVL(BC.AWARD_ACTUAL_APPROVED
, 0)+ NVL(BC.AWARD_ACTUAL_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'A'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_AWARD_ACTUAL
, BC.AWARD_RESULT_CODE
, BC.TOP_TASK_BUDGET_POSTED
, BC.TOP_TASK_BUDGET_APPROVED
, BC.TOP_TASK_BUDGET_PENDING
, TO_NUMBER(DECODE(TOP_TASK_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.TOP_TASK_BUDGET_POSTED
, 0)+NVL(BC.TOP_TASK_BUDGET_APPROVED
, 0)+ NVL(BC.TOP_TASK_BUDGET_PENDING
, 0))) TOTAL_TOP_TASK_BUDGET
, BC.TOP_TASK_ENC_POSTED
, BC.TOP_TASK_ENC_APPROVED
, BC.TOP_TASK_ENC_PENDING
, TO_NUMBER(DECODE(TOP_TASK_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.TOP_TASK_ENC_POSTED
, 0)+NVL(BC.TOP_TASK_ENC_APPROVED
, 0)+ NVL(BC.TOP_TASK_ENC_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'E'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_TOP_TASK_ENCUMBRANCE
, BC.TOP_TASK_ACTUAL_POSTED
, BC.TOP_TASK_ACTUAL_APPROVED
, BC.TOP_TASK_ACTUAL_PENDING
, TO_NUMBER(DECODE(TOP_TASK_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.TOP_TASK_ACTUAL_POSTED
, 0)+NVL(BC.TOP_TASK_ACTUAL_APPROVED
, 0)+ NVL(BC.TOP_TASK_ACTUAL_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'A'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_TOP_TASK_ACTUAL
, BC.TOP_TASK_RESULT_CODE
, BC.TASK_BUDGET_POSTED
, BC.TASK_BUDGET_APPROVED
, BC.TASK_BUDGET_PENDING
, TO_NUMBER(DECODE(TASK_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.TASK_BUDGET_POSTED
, 0)+NVL(BC.TASK_BUDGET_APPROVED
, 0)+ NVL(BC.TASK_BUDGET_PENDING
, 0))) TOTAL_TASK_BUDGET
, BC.TASK_ENC_POSTED
, BC.TASK_ENC_APPROVED
, BC.TASK_ENC_PENDING
, TO_NUMBER(DECODE(TASK_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.TASK_ENC_POSTED
, 0)+NVL(BC.TASK_ENC_APPROVED
, 0)+ NVL(BC.TASK_ENC_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'E'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_TASK_ENCUMBRANCE
, BC.TASK_ACTUAL_POSTED
, BC.TASK_ACTUAL_APPROVED
, BC.TASK_ACTUAL_PENDING
, TO_NUMBER(DECODE(TASK_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.TASK_ACTUAL_POSTED
, 0)+NVL(BC.TASK_ACTUAL_APPROVED
, 0)+ NVL(BC.TASK_ACTUAL_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'A'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_TASK_ACTUAL
, BC.TASK_RESULT_CODE
, BC.RES_GRP_BUDGET_POSTED
, BC.RES_GRP_BUDGET_APPROVED
, BC.RES_GRP_BUDGET_PENDING
, TO_NUMBER(DECODE(RES_GRP_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.RES_GRP_BUDGET_POSTED
, 0)+NVL(BC.RES_GRP_BUDGET_APPROVED
, 0) + NVL(BC.RES_GRP_BUDGET_PENDING
, 0))) TOTAL_RES_GRP_BUDGET
, BC.RES_GRP_ENC_POSTED
, BC.RES_GRP_ENC_APPROVED
, BC.RES_GRP_ENC_PENDING
, TO_NUMBER(DECODE(RES_GRP_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.RES_GRP_ENC_POSTED
, 0)+NVL(BC.RES_GRP_ENC_APPROVED
, 0)+ NVL(BC.RES_GRP_ENC_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'E'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_RES_GRP_ENC
, BC.RES_GRP_ACTUAL_POSTED
, BC.RES_GRP_ACTUAL_APPROVED
, BC.RES_GRP_ACTUAL_PENDING
, TO_NUMBER(DECODE(RES_GRP_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.RES_GRP_ACTUAL_POSTED
, 0)+NVL(BC.RES_GRP_ACTUAL_APPROVED
, 0) + NVL(BC.RES_GRP_ACTUAL_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'A'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_RES_GRP_ACTUAL
, BC.RES_GRP_RESULT_CODE
, BC.RES_BUDGET_POSTED
, BC.RES_BUDGET_APPROVED
, BC.RES_BUDGET_PENDING
, TO_NUMBER(DECODE(RES_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.RES_BUDGET_POSTED
, 0)+NVL(BC.RES_BUDGET_APPROVED
, 0)+ NVL(BC.RES_BUDGET_PENDING
, 0))) TOTAL_RES_BUDGET
, BC.RES_ENC_POSTED
, BC.RES_ENC_APPROVED
, BC.RES_ENC_PENDING
, TO_NUMBER(DECODE(RES_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.RES_ENC_POSTED
, 0)+NVL(BC.RES_ENC_APPROVED
, 0)+ NVL(BC.RES_ENC_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'E'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_RES_ENC
, BC.RES_ACTUAL_POSTED
, BC.RES_ACTUAL_APPROVED
, BC.RES_ACTUAL_PENDING
, TO_NUMBER(DECODE(RES_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.RES_ACTUAL_POSTED
, 0)+NVL(BC.RES_ACTUAL_APPROVED
, 0)+ NVL(BC.RES_ACTUAL_PENDING
, 0) - DECODE(BC.ACTUAL_FLAG
, 'A'
, (BC.ENTERED_DR - BC.ENTERED_CR)
, 0))) TOTAL_RES_ACTUAL
, BC.RES_RESULT_CODE
, NVL(BC.AWARD_BUDGET_POSTED
, 0)-NVL(BC.AWARD_ACTUAL_POSTED
, 0)- NVL(BC.AWARD_ENC_POSTED
, 0) AWARD_FA_POSTED
, NVL(BC.AWARD_BUDGET_PENDING
, 0)-NVL(BC.AWARD_ACTUAL_PENDING
, 0)- NVL(BC.AWARD_ENC_PENDING
, 0) AWARD_FA_PENDING
, NVL(BC.AWARD_BUDGET_APPROVED
, 0)-NVL(BC.AWARD_ACTUAL_APPROVED
, 0)- NVL(BC.AWARD_ENC_APPROVED
, 0) AWARD_FA_APPROVED
, NVL(BC.TOP_TASK_BUDGET_POSTED
, 0)-NVL(BC.TOP_TASK_ACTUAL_POSTED
, 0)- NVL(BC.TOP_TASK_ENC_POSTED
, 0) TOP_TASK_FA_POSTED
, NVL(BC.TOP_TASK_BUDGET_PENDING
, 0)-NVL(BC.TOP_TASK_ACTUAL_PENDING
, 0)- NVL(BC.TOP_TASK_ENC_PENDING
, 0) TOP_TASK_FA_PENDING
, NVL(BC.TOP_TASK_BUDGET_APPROVED
, 0)-NVL(BC.TOP_TASK_ACTUAL_APPROVED
, 0)- NVL(BC.TOP_TASK_ENC_APPROVED
, 0) TOP_TASK_FA_APPROVED
, NVL(BC.TASK_BUDGET_POSTED
, 0)- NVL(BC.TASK_ACTUAL_POSTED
, 0)- NVL(BC.TASK_ENC_POSTED
, 0) TASK_FA_POSTED
, NVL(BC.TASK_BUDGET_PENDING
, 0)-NVL(BC.TASK_ACTUAL_PENDING
, 0)- NVL(BC.TASK_ENC_PENDING
, 0) TASK_FA_PENDING
, NVL(BC.TASK_BUDGET_APPROVED
, 0)-NVL(BC.TASK_ACTUAL_APPROVED
, 0)- NVL(BC.TASK_ENC_APPROVED
, 0) TASK_FA_APPROVED
, NVL(BC.RES_BUDGET_POSTED
, 0)-NVL(BC.RES_ACTUAL_POSTED
, 0)- NVL(BC.RES_ENC_POSTED
, 0) RES_FA_POSTED
, NVL(BC.RES_BUDGET_PENDING
, 0)-NVL(BC.RES_ACTUAL_PENDING
, 0)- NVL(BC.RES_ENC_PENDING
, 0) RES_FA_PENDING
, NVL(BC.RES_BUDGET_APPROVED
, 0)-NVL(BC.RES_ACTUAL_APPROVED
, 0)- NVL(BC.RES_ENC_APPROVED
, 0) RES_FA_APPROVED
, NVL(BC.RES_GRP_BUDGET_POSTED
, 0)-NVL(BC.RES_GRP_ACTUAL_POSTED
, 0)- NVL(BC.RES_GRP_ENC_POSTED
, 0) RES_GRP_FA_POSTED
, NVL(BC.RES_GRP_BUDGET_PENDING
, 0)-NVL(BC.RES_GRP_ACTUAL_PENDING
, 0) - NVL(BC.RES_GRP_ENC_PENDING
, 0) RES_GRP_FA_PENDING
, NVL(BC.RES_GRP_BUDGET_APPROVED
, 0)-NVL(BC.RES_GRP_ACTUAL_APPROVED
, 0)- NVL(BC.RES_GRP_ENC_APPROVED
, 0) RES_GRP_FA_APPROVED
, TO_NUMBER(DECODE(BC.AWARD_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.AWARD_BUDGET_POSTED
, 0)-NVL(BC.AWARD_ACTUAL_POSTED
, 0) -NVL(BC.AWARD_ENC_POSTED
, 0)+NVL(BC.AWARD_BUDGET_PENDING
, 0) -NVL(BC.AWARD_ACTUAL_PENDING
, 0)- NVL(BC.AWARD_ENC_PENDING
, 0)+ NVL(BC.AWARD_BUDGET_APPROVED
, 0)-NVL(BC.AWARD_ACTUAL_APPROVED
, 0)- NVL(BC.AWARD_ENC_APPROVED
, 0))) TOTAL_AWARD_FA
, TO_NUMBER(DECODE(BC.TASK_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.TASK_BUDGET_POSTED
, 0)-NVL(BC.TASK_ACTUAL_POSTED
, 0)- NVL(BC.TASK_ENC_POSTED
, 0) +NVL(BC.TASK_BUDGET_PENDING
, 0) -NVL(BC.TASK_ACTUAL_PENDING
, 0)- NVL(BC.TASK_ENC_PENDING
, 0)+ NVL(BC.TASK_BUDGET_APPROVED
, 0)-NVL(BC.TASK_ACTUAL_APPROVED
, 0)- NVL(BC.TASK_ENC_APPROVED
, 0))) TOTAL_TASK_FA
, TO_NUMBER(DECODE(BC.TOP_TASK_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.TOP_TASK_BUDGET_POSTED
, 0)-NVL(BC.TOP_TASK_ACTUAL_POSTED
, 0)- NVL(BC.TOP_TASK_ENC_POSTED
, 0) +NVL(BC.TOP_TASK_BUDGET_PENDING
, 0) -NVL(BC.TOP_TASK_ACTUAL_PENDING
, 0)- NVL(BC.TOP_TASK_ENC_PENDING
, 0)+ NVL(BC.TOP_TASK_BUDGET_APPROVED
, 0)-NVL(BC.TOP_TASK_ACTUAL_APPROVED
, 0)- NVL(BC.TOP_TASK_ENC_APPROVED
, 0))) TOTAL_TOP_TASK_FA
, TO_NUMBER(DECODE(BC.RES_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.RES_BUDGET_POSTED
, 0)-NVL(BC.RES_ACTUAL_POSTED
, 0)- NVL(BC.RES_ENC_POSTED
, 0)+NVL(BC.RES_BUDGET_PENDING
, 0) -NVL(BC.RES_ACTUAL_PENDING
, 0)- NVL(BC.RES_ENC_PENDING
, 0)+ NVL(BC.RES_BUDGET_APPROVED
, 0)-NVL(BC.RES_ACTUAL_APPROVED
, 0)- NVL(BC.RES_ENC_APPROVED
, 0))) TOTAL_RES_FA
, TO_NUMBER(DECODE(BC.RES_GRP_ACTUAL_PENDING
, NULL
, NULL
, NVL(BC.RES_GRP_BUDGET_POSTED
, 0)-NVL(BC.RES_GRP_ACTUAL_POSTED
, 0)- NVL(BC.RES_GRP_ENC_POSTED
, 0) +NVL(BC.RES_GRP_BUDGET_PENDING
, 0) -NVL(BC.RES_GRP_ACTUAL_PENDING
, 0)- NVL(BC.RES_GRP_ENC_PENDING
, 0)+ NVL(BC.RES_GRP_BUDGET_APPROVED
, 0)-NVL(BC.RES_GRP_ACTUAL_APPROVED
, 0)- NVL(BC.RES_GRP_ENC_APPROVED
, 0))) TOTAL_RES_GRP_F
, PR.SEGMENT1 PROJECT
, TA.TASK_NUMBER TASK
, NVL( AW.AWARD_NUMBER
, IMP.DEFAULT_DIST_AWARD_NUMBER) AWARD_NUMBER
, BV.VERSION_NAME
, L1.MEANING GMS_RESULT_CODE_MEAN
, L2.MEANING AWARD_RESULT_CODE_MEAN
, L3.MEANING TASK_RESULT_CODE_MEAN
, L4.MEANING RES_RESULT_CODE_MEAN
, L5.MEANING RES_GRP_RESULT_CODE_MEAN
, L6.MEANING TOP_TASK_RESULT_CODE_MEAN
, PR.ORG_ID
FROM GMS_BC_PACKETS BC
, PA_PROJECTS_ALL PR
, PA_TASKS TA
, GMS_AWARDS_ALL AW
, GMS_IMPLEMENTATIONS_ALL IMP
, PA_PROJECT_TYPES_ALL PPT
, GMS_LOOKUPS L1
, GMS_LOOKUPS L2
, GMS_LOOKUPS L3
, GMS_LOOKUPS L4
, GMS_LOOKUPS L5
, GMS_LOOKUPS L6
, GMS_BUDGET_VERSIONS BV
WHERE BC.PROJECT_ID = PR.PROJECT_ID
AND PR.PROJECT_TYPE = PPT.PROJECT_TYPE
AND PPT.PROJECT_TYPE <> 'AWARD_PROJECT'
AND PPT.SPONSORED_FLAG = 'Y'
AND BC.TASK_ID = TA.TASK_ID
AND BC.AWARD_ID = AW.AWARD_ID(+)
AND IMP.DEFAULT_DIST_AWARD_ID(+) = BC.AWARD_ID
AND BV.BUDGET_VERSION_ID(+) = BC.BUDGET_VERSION_ID
AND L1.LOOKUP_TYPE(+) = 'FC_RESULT_CODE'
AND L1.LOOKUP_CODE(+) = BC.RESULT_CODE
AND L2.LOOKUP_TYPE(+) = 'FC_RESULT_CODE'
AND L2.LOOKUP_CODE(+) = BC.AWARD_RESULT_CODE
AND L3.LOOKUP_TYPE(+) = 'FC_RESULT_CODE'
AND L3.LOOKUP_CODE(+) = BC.TASK_RESULT_CODE
AND L4.LOOKUP_TYPE(+) = 'FC_RESULT_CODE'
AND L4.LOOKUP_CODE(+) = BC.RES_RESULT_CODE
AND L5.LOOKUP_TYPE(+) = 'FC_RESULT_CODE'
AND L5.LOOKUP_CODE(+) = BC.RES_GRP_RESULT_CODE
AND L6.LOOKUP_TYPE(+) = 'FC_RESULT_CODE'
AND L6.LOOKUP_CODE(+) = BC.TOP_TASK_RESULT_CODE

Columns