VIEW: APPS.FIIBV_PA_BUDGET_F_FCV

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'