View: FIIBV_PA_BUDGET_F_FCV

View Text

SELECT NULL SEQ_ID
, TO_CHAR(BL.RESOURCE_ASSIGNMENT_ID) || '-' || TO_CHAR(BL.START_DATE
, 'YYYY/MM/DD') || '-' || LI.INSTANCE_CODE BUDGET_LINE_PK
, LI.INSTANCE_CODE INSTANCE_FK
, TO_CHAR(SOB.SET_OF_BOOKS_ID) || '-' || LI.INSTANCE_CODE SET_OF_BOOKS_FK
, DECODE( BEM.ENTRY_LEVEL_CODE
, 'P'
, TO_CHAR(PRJ.PROJECT_ID) || '-' || LI.INSTANCE_CODE || '-PJ-PRJ'
, TO_CHAR(RA.TASK_ID) || '-' || LI.INSTANCE_CODE ) PROJECT_FK
, NULL PROJECT_ORG_FK
, TO_CHAR(BV.BUDGET_VERSION_ID) || '-' || LI.INSTANCE_CODE BUDGET_FK
, DECODE( RT.RESOURCE_TYPE_CODE
, 'EXPENDITURE_TYPE'
, R.NAME || '-' || LI.INSTANCE_CODE
, 'NA_EDW' ) EXPENDITURE_TYPE_FK
, SOB.CURRENCY_CODE CURRENCY_GL_FK
, DECODE( BEM.TIME_PHASED_TYPE_CODE
, 'G'
, SOB.PERIOD_SET_NAME || '-' || BL.PERIOD_NAME || '-' || LI.INSTANCE_CODE || '-CPER'
, 'NA_EDW') GL_PERIOD_FK
, DECODE(BEM.TIME_PHASED_TYPE_CODE
, 'P'
, SOB.PERIOD_SET_NAME || '-' || BL.PERIOD_NAME || '-' || LI.INSTANCE_CODE || '-PPER'
, 'NA_EDW') PA_PERIOD_FK
, DECODE( RA.UNIT_OF_MEASURE
, NULL
, 'NA_EDW'
, EDW_UTIL.GET_EDW_UOM( 'PA-' || RA.UNIT_OF_MEASURE
, NULL ) ) UNIT_OF_MEASURE_FK
, BL.START_DATE RANGE_FROM_DATE
, BL.END_DATE RANGE_TO_DATE
, R.NAME DESCRIPTION
, NULL RAW_COST_G
, NVL(BL.RAW_COST
, 0) RAW_COST_B
, NULL BURDENED_COST_G
, NVL(BL.BURDENED_COST
, 0) BURDENED_COST_B
, NVL(BL.QUANTITY
, 0) QUANTITY
, NULL REVENUE_G
, NVL(BL.REVENUE
, 0) REVENUE_B
, NVL(EDW_CURRENCY.GET_RATE ( SOB.CURRENCY_CODE
, FIIPK.CONVERSION_DATE
, NULL)
, -1) GLOBAL_CURRENCY_RATE
, BL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, '_DF:PA:PA_BUDGET_VERSIONS_DESC_FLEX:BV'
, '_DF:PA:PA_BUDGET_METHOD_DESC_FLEX:BEM'
, '_DF:PA:PA_RESOURCES_FLEX:R'
, '_DF:PA:PA_BUDGET_LINES_DESC_FLEX:BL'
, '_DF:PA:PA_TASKS_DESC_FLEX:T'
, '_DF:SQLGL:GL_SETS_OF_BOOKS:SOB'
, FIIPK.CONVERSION_DATE TRANSACTION_DATE
FROM FII_PA_BUDGET_PK FIIPK
, GL_SETS_OF_BOOKS SOB
, PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECTS_ALL PRJ
, PA_BUDGET_VERSIONS BV
, PA_BUDGET_ENTRY_METHODS BEM
, PA_RESOURCE_ASSIGNMENTS RA
, PA_RESOURCE_LIST_MEMBERS RLM
, PA_RESOURCES R
, PA_RESOURCE_TYPES RT
, PA_BUDGET_LINES BL
, EDW_LOCAL_INSTANCE LI
, PA_TASKS T
WHERE BL.RESOURCE_ASSIGNMENT_ID = FIIPK.PRIMARY_KEY1
AND BL.START_DATE = FIIPK.PRIMARY_KEY_DATE1
AND SOB.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID
AND NVL(IMP.ORG_ID
, -99) = NVL(PRJ.ORG_ID
, -99)
AND PRJ.PROJECT_ID = BV.PROJECT_ID
AND BV.BUDGET_VERSION_ID = RA.BUDGET_VERSION_ID
AND RA.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID
AND RLM.RESOURCE_ID = R.RESOURCE_ID
AND R.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID
AND RA.RESOURCE_ASSIGNMENT_ID = BL.RESOURCE_ASSIGNMENT_ID
AND BV.BUDGET_ENTRY_METHOD_CODE = BEM.BUDGET_ENTRY_METHOD_CODE
AND NVL(RA.TASK_ID
, -99) = T.TASK_ID (+)
AND BV.BUDGET_STATUS_CODE = 'B'

Columns