VIEW: APPS.GMS_FUNDSCTL_RES_V

Source

View Text - Preformatted

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 AND PR.org_id = ppt.org_id

View Text - HTML Formatted

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
AND PR.ORG_ID = PPT.ORG_ID