Untitled
unknown
plain_text
9 months ago
126 kB
16
Indexable
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_INT_PT_WellServices] as
select [UNIQUE_KEY]
,[COST_ESTIMATION]
,[FA]
,[PLANNED_PRODUCTION_BOE]
,[PLANNED_PRODUCTION_DELTA_BOE]
,[WORKOVER_ACTUAL_HOURS]
,[WORKOVER_ESTIMATED_HOURS]
,[WORKOVER_ESTIMATED_DAYS]
,[BUDGET]
,[BUDGET_CODE]
,[COUNTRY_CODE]
,[END_DATE]
,[FA_DESCRIPTION]
,[FOLLOWING_WOWI]
,[FOLLOWING_WOWI_2]
,[FOLLOWING_WOWI_3]
,[IS_PLAN]
,[MODIFY_DATE]
,[ORDER_NUMBER]
,[PLANNED_EXECUTION]
,[PLAN_NUMBER]
,[PLAN_STATUS]
,[PLAN_STATUS_DELAY_REASON]
,[RIG_CREW]
,[START_DATE]
,[TYPE]
,[UWI]
,[UBHI]
,[WELL_FAILURE_DATE]
,[SCHEDULE_STARTDATE]
,[WELL_NAME]
,[WORKOVER_TYPE]
,[WORKOVER_TYPE_DESCRIPTION]
,[WOWI_REASON]
,[WOWI_REASON_2]
,[WOWI_TYPE]
,[PLAN]
,[LAST_UPDATED_DATE]
,[PERFORATION_ID]
--,[COSTS_ACTUAL]
--,[ACTIVITY_DESCRIPTION_CODE]
--,[ACTIVITY_DESCRIPTION_TEXT]
FROM [ROProductionTechnology].[WellServices]--NOT USED ANYMORE [CDFLayer].[RO_ProductionTechnology_WellServices]
union all
select [UNIQUE_KEY]
,[COST_ESTIMATION]
,[FA]
,[PLANNED_PRODUCTION_BOE]
,[PLANNED_PRODUCTION_DELTA_BOE]
,[WORKOVER_ACTUAL_HOURS]
,[WORKOVER_ESTIMATED_HOURS]
,[WORKOVER_ESTIMATED_DAYS]
,[BUDGET]
,[BUDGET_CODE]
,[COUNTRY_CODE]
,[END_DATE]
,[FA_DESCRIPTION]
,[FOLLOWING_WOWI]
,[FOLLOWING_WOWI_2]
,[FOLLOWING_WOWI_3]
,[IS_PLAN]
,[MODIFY_DATE]
,[ORDER_NUMBER]
,[PLANNED_EXECUTION]
,[PLAN_NUMBER]
,[PLAN_STATUS]
,[PLAN_STATUS_DELAY_REASON]
,[RIG_CREW]
,[START_DATE]
,[TYPE]
,[UWI]
,[UBHI]
,[WELL_FAILURE_DATE]
,[SCHEDULE_STARTDATE]
,[WELL_NAME]
,[WORKOVER_TYPE]
,[WORKOVER_TYPE_DESCRIPTION]
,[WOWI_REASON]
,[WOWI_REASON_2]
,[WOWI_TYPE]
,[PLAN]
,[LAST_UPDATED_DATE]
,[PERFORATION_ID]
--,[COSTS_ACTUAL]
--,[ACTIVITY_DESCRIPTION_CODE]
--,[ACTIVITY_DESCRIPTION_TEXT]
FROM [ATProductionTechnology].[WellServices]
GO
CREATE VIEW [OMVDDFLayer].[F_D_PO_ProductionReportingDailyEstimatedReport] as
SELECT [UNIQUE_KEY]
,[DAILYREPORT_ID]
,[DAILY_ACTUALPRODUCTION]
,[DAILY_MTPPRODUCTION_GAS_FUEL]
,[DAILY_MTPPRODUCTION_GAS_SALES]
,[DAILY_MTPPRODUCTION_NGL_FUEL]
,[DAILY_MTPPRODUCTION_NGL_SALES]
,[DAILY_MTPPRODUCTION_OIL_FUEL]
,[DAILY_MTPPRODUCTION_OIL_SALES]
,[STATE_CODE]
,[ASSET]
,[COUNTRY_CODE]
,[CREATED_ON]
,[DAILY_COMMENT_BO]
,[DATE]
,[IS_ACTIVE]
,[LAST_UPDATED_DATE]
,[OMV_VALUE_CENTER]
,[PROD_DAILYREPORT_ID]
FROM [ProductionOptimization].[ProductionReportingDailyEstimatedReport]
union all
select [UNIQUE_KEY]
,[DAILYREPORT_ID]
,[DAILY_ACTUALPRODUCTION]
,[DAILY_MTPPRODUCTION_GAS_FUEL]
,[DAILY_MTPPRODUCTION_GAS_SALES]
,[DAILY_MTPPRODUCTION_NGL_FUEL]
,[DAILY_MTPPRODUCTION_NGL_SALES]
,[DAILY_MTPPRODUCTION_OIL_FUEL]
,[DAILY_MTPPRODUCTION_OIL_SALES]
,[STATE_CODE]
,[ASSET]
,[COUNTRY_CODE]
,[CREATED_ON]
,[DAILY_COMMENT_BO]
,[DATE]
,[IS_ACTIVE]
,[LAST_UPDATED_DATE]
,[OMV_VALUE_CENTER]
,[PROD_DAILYREPORT_ID]
FROM [ROProductionOptimization].[ProductionReportingDailyEstimatedReport]
GO
-- Auto Generated (Do not modify) A390F660EF28F846197E1B6AAB8ACDC93791D4D056F283B07E91E1AFC400F06D
CREATE VIEW [OMVDDFLayer].[D_WellEvents] as
SELECT [UNIQUE_KEY]
,[EVENT_OUI]
,[ASSET_NAME]
,[UNIQUE_ID]
,[COUNTRY_CODE]
,[EVENT_TYPE_DESCRIPTION]
,[EVENT_ACTUAL_START_DATE]
,[EVENT_PLANNED_END_DATE]
,[EVENT_DESCRIPTION]
,[UWI]
,[EVENT_PLANNED_START_DATE]
,[EVENT_TYPE_CODE]
,[LAST_UPDATED_DATE]
,[EVENT_ACTUAL_END_DATE]
FROM [OMVDDFLayer].V_INT_PT_WellEvents
GO
CREATE VIEW [OMVDDFLayer].[F_D_F_ProductionCosts] as
select [UNIQUE_KEY]
,[ACCOUNT_BALANCE_FUNCTIONAL_CURRENCY]
,[ACCOUNT_BALANCE_SALES_COSTS]
,[ACCOUNT_BALANCE_TOTAL_COSTS]
,[FISCAL_PERIOD]
,[FISCAL_YEAR]
,[POSTING_PERIOD_VARIANT]
,[REPORTING_PERIOD]
,[VALUE_COUNTER]
,[CORA_ACCOUNT]
,[COUNTRY_CODE]
,[FISCAL_YEAR_VARIANT]
,[FUNCTIONAL_CURRENCY]
,[CORA_ACCOUNT_TEXT]
,[COMPANY_CODE]
,[ACCOUNT_BALANCE_EUR]
,[ACCOUNT_BALANCE_USD]
,[ASSET_TEXT]
,[CO_AREA]
,[ASSET]
,[RECORD_MODE]
,[PROFIT_CENTER]
,[LEGAL_ENTITY]
FROM [DLCFinance].[ProductionCosts]
GO
-- Auto Generated (Do not modify) 523778307FE9029F04307E4CD57C68823A6FF09B8422F21792841EE3A05B8B14
CREATE VIEW [OMVDDFLayer].[V_INT_FlatSubsurfaceHierarchy] as
SELECT [UNIQUE_KEY]
,[PARENT_WEBID]
,[PI_COMMENT]
,[PI_BOTTOM_DEPTH_TVD]
,[PI_DATE_INTERVAL_OPENED]
,[PI_TOP_DEPTH_MD]
,[PU_TOP_DEPTH]
,[PI_STATUS]
,[COUNTRY]
,[DESCRIPTION]
,[PI_TOP_DEPTH_TVDSS]
,[PI_TOP_DEPTH_TVD]
,[ELEMENT_ID]
,[WEBID]
,[UWI]
,[PI_BOTTOM_DEPTH_MD]
,[UBHI]
,[PI_DATE_INTERVAL_CLOSED]
,[F_PRODUCTION_SYSTEM_CODE]
,[ELEMENT_ID_TYPE]
,[ELEMENT_TEMPLATE]
,[SOURCE_SYSTEM]
,[PU_BOTTOM_DEPTH]
,[LAST_UPDATED_DATE]
,[PU_CONTACT_TYPE]
,[DELETION_FLAG]
-- ,[PI_BLOCK_NAME]
,[PI_BOTTOM_DEPTH_TVDSS]
,[PI_PERFORATION_CODE]
,[ELEMENT_DESCRIPTION]
,[ELEMENT_NAME]
,[F_ASSET_NAME]
,[PU_CODE]
-- ,[REOPENEDINTERVALS]
FROM [ATCurrentHierarchies].[FlatSubsurfaceHierarchy]
UNION ALL
SELECT [UNIQUE_KEY]
,[PARENT_WEBID]
,[PI_COMMENT]
,[PI_BOTTOM_DEPTH_TVD]
,[PI_DATE_INTERVAL_OPENED]
,[PI_TOP_DEPTH_MD]
,[PU_TOP_DEPTH]
,[PI_STATUS]
,[COUNTRY]
,[DESCRIPTION]
,[PI_TOP_DEPTH_TVDSS]
,[PI_TOP_DEPTH_TVD]
,[ELEMENT_ID]
,[WEBID]
,[UWI]
,[PI_BOTTOM_DEPTH_MD]
,[UBHI]
,[PI_DATE_INTERVAL_CLOSED]
,[F_PRODUCTION_SYSTEM_CODE]
,[ELEMENT_ID_TYPE]
,[ELEMENT_TEMPLATE]
,[SOURCE_SYSTEM]
,[PU_BOTTOM_DEPTH]
,[LAST_UPDATED_DATE]
,[PU_CONTACT_TYPE]
,[DELETION_FLAG]
-- ,[PI_BLOCK_NAME]
,[PI_BOTTOM_DEPTH_TVDSS]
,[PI_PERFORATION_CODE]
,[ELEMENT_DESCRIPTION]
,[ELEMENT_NAME]
,[F_ASSET_NAME]
,[PU_CODE]
-- ,[REOPENEDINTERVALS]
FROM [ROCurrentHierarchies].[FlatSubsurfaceHierarchy]
GO
CREATE VIEW [OMVDDFLayer].[DO_WellIntegrity] as
SELECT [UNIQUE_KEY]
,[empty_schema]
,[UWI]
,[UBHI]
,[MAASP_ANNULUS_A]
,[MAASP_ANNULUS_C]
,[MAASP_ANNULUS_B]
,[COUNTRY_CODE]
,[MAASP_ANNULUS_D]
FROM [ATProductionTechnology].[WellIntegrity]
GO
-- Auto Generated (Do not modify) BE5C83001E81F5F250EBAF1A5077D2FF70F5455CD0B72D0DCBFD3EA3548A8FF3
CREATE VIEW [OMVDDFLayer].[DO_D_H_BoreholeStatus] as
SELECT [UNIQUE_KEY]
,[BOREHOLE_STATUS_OPERATIONAL]
,[COUNTRY_CODE]
,[FLAG_PRODUCTION]
,[LAST_UPDATED_DATE]
,x.TheDate [DATE]
,[START_DATE]
,[END_DATE]
,[UBHI]
,[UWI]
,[WELL_NAME]
FROM [OMVDDFLayer].V_RO_BoreholeStatus join [OMVDDFLayer].D_Date x on CAST([START_DATE] as DATE) <= x.TheDate and (x.TheDate < CAST([END_DATE] as DATE) and [END_DATE] is not null or x.TheDate <=cast(SYSDATETIME() as DATE) and [END_DATE] is null )
GO
CREATE VIEW [OMVDDFLayer].[DO_BoreholePerforations] as
SELECT [UNIQUE_KEY]
,[BOTTOM_PERFORATION_MD]
,[TOP_PERFORATION_MD]
,[COUNTRY_CODE]
,[END_DATE]
,[HORIZON]
,[PERFORATION_ID]
,[PERFORATION_TYPE]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_UNIT]
,[START_DATE]
,[UBHI]
,[UWI]
,[PERFORATION_STATUS]
,[BOTTOM_PERFORATION_TVD]
,[MID_PERFORATION_MD]
,[TOP_PERFORATION_TVD]
,[MID_PERFORATION_TVD]
FROM [ATProductionTechnology].[BoreholePerforations]
GO
-- Auto Generated (Do not modify) 51F026D24FA2627723834D54433A3EAB269C2AD37C0573BF25FF5103EEBFE093
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[DO_WellEventOperations] as
SELECT [UNIQUE_KEY]
,[ACTION_TAKEN_DESCRIPTION]
,[OPERATION_EQUIPMENT_TYPE_DESCRIPTION]
,[ASSET_NAME]
,[OPERATION_EQUIPMENT_TYPE_CODE]
,[COUNTRY_CODE]
,[OPERATION_EQUIPMENT_FAILURE_DESCRIPTION]
,[OPERATION_TYPE_CODE]
,[ACTION_TAKEN_CODE]
,[EVENT_REMARKS]
,[REPORT_DESCRIPTION]
,[REPORT_INTERNAL_ID]
,[OPERATION_END_DATE]
,[OPERATION_EQUIPMENT_FAILURE_CODE]
,[EVENT_TYPE_CODE]
,[UNIQUE_ID]
,[OPERATION_COMMENT]
,[OPERATION_MODIFICATION_TYPE_DESCRIPTION]
,[OPERATION_PROCESS_TYPE_DESCRIPTION]
,[EVENT_UNIQUE_ID]
,[OPERATION_TYPE_DESCRIPTION]
,[OPERATION_START_DATE]
,[REPORT_DATE]
,[OPERATION_CAUSE_CODE]
,[OPERATION_CAUSE_DESCRIPTION]
,[LAST_UPDATED_DATE]
,[OPERATION_PROCESS_TYPE_CODE]
,[OPERATION_MODIFICATION_TYPE_CODE]
,[UWI]
FROM [OMVDDFLayer].V_INT_PT_WellEventOperations
GO
-- Auto Generated (Do not modify) 447E0ED744AC72CC068B54EF037CBFC313FF12E954E05D3450540D9C7EC08290
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_RO_Potential] as
SELECT
[POT_ASSOC_GAS] ,
[POT_FREE_GAS] ,
[POT_OIL] ,
[POT_WATER] ,
[POT_WATERCUT] ,
[COUNTRY_CODE] ,
cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE],
cast([POT_DATE] AS DATE) as [POT_DATE] ,
[UBHI] ,
[UWI]
,[POT_CONDENSATE]
,[POT_INJ_WATER]
FROM [ROProductionOptimization].[Potential]
GO
-- Auto Generated (Do not modify) 9D443386FCA673E3A10F4B6682E97126DADDA2996CB3096321313BF4751ED97D
CREATE VIEW [OMVDDFLayer].[V_RO_WH_Wells] as
SELECT cast(null as varchar) [ALTERNATIVE_COORDINATE_SYSTEM]
,cast(null as varchar) [ALTERNATIVE_EASTING]
,cast(null as varchar) [ALTERNATIVE_NORTHING]
,cast(null as varchar) [AREA_NAME]
,cast(null as varchar) [AREA_NAME_ABBR]
,[BLOCK_CODE] [BLOCK_CODE]
,[BLOCK_NAME] [BLOCK_NAME]
,cast(null as varchar) [CONTRACT_CODE]
,cast(null as varchar) [CONTRACT_NAME]
,[COORD_STATUS] [COORD_STATUS]
,[COUNTRY_CODE] [COUNTRY_CODE]
,[ELEVATION_ABOVE_DATUM] [ELEVATION_ABOVE_DATUM]
,[ELEVATION_VALUE_UOM] [ELEVATION_VALUE_UOM]
,[EXISTENCE_KIND] [EXISTENCE_KIND]
,cast(null as varchar) [GENERAL_FIELD_NAME]
,cast(null as varchar) [GENERAL_FIELD_NAME_SOURCE]
,cast(null as varchar) [GEOCOORD_EPSG]
,cast(null as varchar) [HORIZON_PRODUCTION_UNIT]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,[LIFE_CYCLE_PHASE] [LIFE_CYCLE_PHASE]
,[OMV_OWNED]
,[ON_OFF_WELL] [ON_OFF_WELL]
,cast(null as varchar) [ORIGINAL_COORDINATE_SYSTEM]
,cast(null as varchar) [ORIGINAL_EASTING]
,cast(null as varchar) [ORIGINAL_NORTHING]
,[PERMANENT_ELEV_REF_TYPE] [PERMANENT_ELEV_REF_TYPE]
,cast(null as varchar) [PRODUCTION_DENSITY_OIL]
,cast(null as varchar) [PRODUCTION_FIELD_CODE]
,cast(null as varchar) [PRODUCTION_SYSTEM]
,cast(null as varchar) [PROJCOORD_EPSG]
,cast(null as varchar) [RESPONSIBLE_DEPUTY1]
,cast(null as varchar) [RESPONSIBLE_DEPUTY2]
,cast(null as varchar) [RESPONSIBLE_ENGINEER]
,cast(null as varchar) [RESPONSIBLE_OPERATOR]
,cast(null as varchar) [RESPONSIBLE_PT]
,cast(null as varchar) [RESPONSIBLE_RE]
,cast(null as varchar) [SHUTOFF_REASON_COMMENT]
,[SURFACE_LATITUDE_WGS84] [SURFACE_LATITUDE_WGS84]
,[SURFACE_LONGITUDE_WGS84] [SURFACE_LONGITUDE_WGS84]
,[UWI] [UWI]
,[VENTURE] [VENTURE]
,cast(null as varchar) [WATER_DEPTH]
,[WATER_DEPTH_UOM] [WATER_DEPTH_UOM]
,[WELL_COMMENTS] [WELL_COMMENTS]
,cast(null as varchar) [WELL_DATA_SOURCE]
,cast(null as varchar) [WELL_DOWNTIME_REASON]
,cast(null as varchar) [WELL_FLUID_TYPE]
,[WELL_NAME] [WELL_NAME]
,[WELL_NAME_ABBR] [WELL_NAME_ABBR]
,cast(null as varchar) [WELL_PRODUCTION_METHOD]
,cast(null as varchar) [WELL_PRODUCTION_METHOD_DETAIL]
,[WELL_TYPE]
FROM [ROWellHeader].[Wells]
GO
CREATE VIEW [OMVDDFLayer].[MV_TEST_Cummulative_TN]
WITH SCHEMABINDING as
SELECT
-- [ALLOC_MASS_OIL]
--,[ALLOC_VOL_OIL]
--,[ALLOC_ASSOC_GAS]
-- ,[ALLOC_CONDENSATE]
--,[ALLOC_FREE_GAS]
-- ,[ALLOC_GAS]
-- ,[ALLOC_GASLIFT]
-- ,[ALLOC_INJECTION_WATER]
-- ,[ALLOC_MASS_CONDENSATE]
-- ,[ALLOC_VOL_CONDENSATE]
-- ,[ALLOC_WATER]
COUNT_BIG (*) total_records
,sum([S2].[ALLOC_MASS_OIL]) cum_alloc_mass_oil
,sum([S2].[ALLOC_VOL_OIL]) cum_alloc_vol_oil
,sum([S2].[ALLOC_ASSOC_GAS]) cum_ALLOC_ASSOC_GAS
,sum([S2].[ALLOC_CONDENSATE]) cum_ALLOC_CONDENSATE
,sum([S2].[ALLOC_FREE_GAS]) cum_ALLOC_FREE_GAS
,sum([S2].[ALLOC_GAS]) cum_ALLOC_GAS
,sum([S2].[ALLOC_GASLIFT]) cum_ALLOC_GASLIFT
,sum([S2].[ALLOC_INJECTION_WATER]) cum_ALLOC_INJECTION_WATER
,sum([S2].[ALLOC_MASS_CONDENSATE]) cum_ALLOC_MASS_CONDENSATE
,sum([S2].[ALLOC_VOL_CONDENSATE]) cum_ALLOC_VOL_CONDENSATE
,sum([S2].[ALLOC_WATER]) cum_ALLOC_WATER
,[S1].[DATE]
,[S1].UWI
FROM [ProductionOptimization].[Hydrocarbonbydate] S1, [ProductionOptimization].[Hydrocarbonbydate] S2
where S1.COUNTRY_CODE = 'TN' and S2.COUNTRY_CODE = 'TN' and s1.UWI = s2.UWI and s2.date <= s1.date group by s1.UWI, s1.date
--order by s1.UWI, s1.date
GO
-- Auto Generated (Do not modify) 790C16148251D85ECC7B305C1F0CBB71FC8A541B092E583AAD7A0BA959028DAA
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_RO_Deferment] as
SELECT UNIQUE_KEY
,[THEOR_ASSOC_GAS]
,[THEOR_CONDENSATE]
,[THEOR_DEFFERED_GAS]
,[THEOR_FREE_GAS]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_OIL]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[CLIENT_ID]
,[COMMENT]
,[COUNTRY_CODE]
,cast([DATE] AS DATE) as [DATE]
,[DEFERMENT_CHOKE]
,[DEFERMENT_IAP]
,[DEFERMENT_ID]
,[DEFERMENT_NAME]
,[DEVIATION_EVENT]
,[DEVIATION_EVENT_EXPLANATION]
,cast([END_DATE] AS DATETIME) as [END_DATE]
,[EVENT_TYPE]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,[OMV_SHARE_TYPE]
,[SHUT_OFF_REASON]
,[SHUT_OFF_REASON_NATIVE]
,cast([START_DATE] AS DATETIME) as [START_DATE]
,[TIME]
,[TYPE]
,[UWI]
,[ADD_COMMENT]
,[SHUT_OFF_REASON_PLS]
,[DEFERMENT_ROOT_CAUSE]
,[DEFERMENT_CAUSE]
,[SHUT_OFF_REASON_PLS_NATIVE]
,[DEFERMENT_SUB_CHOKE_EQU]
,[VERSION_ID]
,[PRODUCTION_SYSTEM]
,[DEFERMENT_SUB_CHOKE]
,[AREA_NAME_ABBREVIATION]
,[PERFORATION_ID]
FROM [ROProductionOptimization].[Deferments]
GO
-- Auto Generated (Do not modify) E7B0D6DE254B68A97F544634A70BA510EEF20DC2192E7191EDCFA0A5A13131BE
CREATE VIEW [OMVDDFLayer].[V_INT_HydrocarbonbyDate] as
SELECT UNIQUE_KEY
,[ALLOC_ASSOC_GAS]
,[ALLOC_CO2]
,[ALLOC_CONDENSATE]
,[ALLOC_FREE_GAS]
,[ALLOC_GAS_OIL_RATIO]
,[ALLOC_GROSS]
,[ALLOC_H2S]
,[ALLOC_INJECTION_WATER]
,[ALLOC_LPG]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_MASS_OIL]
,[ALLOC_OIL]
,[ALLOC_VOL_OIL]
,[ALLOC_WATER]
,[ALLOC_WATER_CUT]
,[CHOKE_POSITION]
,case when COUNTRY_CODE = 'AT' then [DENSITY_OIL]*1000--required by Bernd to have the same unit as RO
else [DENSITY_OIL] end [DENSITY_OIL]
,[PERFORATION_ID]
,[PRODUCTION_HOURS]
,[SHUTIN_HOURS]
,[THEOR_ASSOC_GAS]
,[THEOR_CO2]
,[THEOR_CONDENSATE]
,[THEOR_FREE_GAS]
,[THEOR_GASLIFT]
,[THEOR_GAS_OIL_RATIO]
,[THEOR_GROSS]
,[THEOR_H2S]
,[THEOR_INJECTION_WATER]
,[THEOR_LPG]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_OIL]
,[THEOR_VOL_OIL]
,[THEOR_WATER]
,[THEOR_WATER_CUT]
,[THEOR_WGR]
,[WELL_MAX_GAS_RATE]
,[WELL_MAX_GROSS_RATE]
,[WELL_MAX_H2O_INJ_RATE]
,[AREA_NAME]
,[AREA_NAME_ABBREVIATION]
,[COUNTRY_CODE]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,cast([DATE] AS DATE) as [DATE]
,[HORIZON]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_SYSTEM]
,[PRODUCTION_UNIT]
,[SHUT_OFF_REASON]
,[SHUT_OFF_REASON_COMMENT]
,[TIME]
,[TYPE]
,[UWI]
,[WELL_DOWNTIME_REASON]
,[WELL_FLUID_TYPE]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,[EVENT_DATE]
,[OBJECT_CODE]
,[WELL_NAME]
,[FUNCTIONAL_LOCATION]
,[THEOR_CONDENSATE_GAS_RATIO]
,[ALLOC_WATER_GAS_RATIO]
,[EVENT_CONDENSATE_GAS_RATIO]
,[ALLOC_GASLIFT]
,[THEOR_WATER_GAS_RATIO]
,[ALLOC_GAS]
,[THEOR_GAS]
,[THEOR_NET_OIL]
,[PRODUCTION_FIELD_NAME]
,[WELL_TYPE]
,[ALLOC_VOL_CONDENSATE]
,[ACTIVE_WELL_STATUS]
,[THEOR_TOTAL_PRODUCTION]
,[UBHI]
,[THEOR_NET_CONDENSATE]
,cast([DATE_STATUS] AS DATE) as [DATE_STATUS]
,[ALLOC_CONDENSATE_GAS_RATIO]
,[EVENT_WATER_GAS_RATIO]
,[ALLOC_TOTAL_HYDROCARBON]
,[THEOR_TOTAL_HYDROCARBON]
,[THEOR_VOL_CONDENSATE]
,[ALLOC_NET_OIL]
,[ALLOC_INJECTION_AIR]
,[THEOR_INJECTION_AIR]
,ALLOC_INJECTION_STEAM
,THEOR_INJECTION_STEAM
FROM [ProductionOptimization].[Hydrocarbonbydate]
GO
CREATE VIEW [OMVDDFLayer].[V_split_int_OrganizationalHierarchy] as
with splithierarchy as(
SELECT [COUNTRY]
,[HUB]
,[UWI]
,[WELL_NAME]
,[PATH]
,[1]
,[2]
,[3]
,[4]
,[5]
,[6]
,[7]
,[8]
FROM (
SELECT
[COUNTRY]
,[HUB]
,[UWI]
,[NAME] as [WELL_NAME]
,[PATH]
,value level_value
,ordinal ordinal_label
FROM [ATCurrentHierarchies].[OrganizationalHierarchy] CROSS APPLY STRING_SPLIT(path, '\' , 1 )
where [TEMPLATE_NAME] = 'Well - ORG' )AS SourceTable
PIVOT
(
max(level_value)
FOR ordinal_label IN ([1], [2], [3], [4], [5], [6], [7],[8])
) AS PivotTable)
select [UWI]
,[1] HIERARCHY_TYPE
,[2] HUB
,[3] COUNTRY
,[4] LEGAL_ENTITY
,[5] CONTRACT_AREA
,[6] ASSET
,[7] SUB_ASSET_GROUP
,[8] WELL_NAME
from splithierarchy
GO
CREATE VIEW [OMVDDFLayer].[DO_WellServicesSchedule] as
SELECT [UNIQUE_KEY]
,[COUNTRY_CODE]
,[CREATE_DATE]
,[LAST_UPDATED_DATE]
,[PLAN_NUMBER]
,[RIG_CREW]
,[SCHEDULE_ENDDATE]
,[SCHEDULE_STARTDATE]
,[UWI]
,[WELL_NAME]
FROM [ATProductionTechnology].[WellServicesSchedule]
GO
CREATE VIEW [OMVDDFLayer].[WH_getWells] as
SELECT [UNIQUE_KEY]
,[ALTERNATIVE_EASTING]
,[ALTERNATIVE_NORTHING]
,[ELEVATION_ABOVE_DATUM]
,[GEOCOORD_EPSG]
,[ORIGINAL_EASTING]
,[ORIGINAL_NORTHING]
,[PRODUCTION_DENSITY_OIL]
,[PROJCOORD_EPSG]
,[SURFACE_LATITUDE_WGS84]
,[SURFACE_LONGITUDE_WGS84]
,[WATER_DEPTH]
,[WELL_PRODUCTION_METHOD]
,[ALTERNATIVE_COORDINATE_SYSTEM]
,[AREA_NAME]
,[AREA_NAME_ABBR]
,[BLOCK_CODE]
,[BLOCK_NAME]
,[CONTRACT_CODE]
,[CONTRACT_NAME]
,[COORD_STATUS]
,[COUNTRY_CODE]
,[ELEVATION_VALUE_UOM]
,[EXISTENCE_KIND]
,[GENERAL_FIELD_NAME]
,[GENERAL_FIELD_NAME_SOURCE]
,[HORIZON_PRODUCTION_UNIT]
,[LAST_UPDATED_DATE]
,[LIFE_CYCLE_PHASE]
,[ON_OFF_WELL]
,[ORIGINAL_COORDINATE_SYSTEM]
,[PERMANENT_ELEV_REF_TYPE]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_SYSTEM]
,[RESPONSIBLE_DEPUTY1]
,[RESPONSIBLE_DEPUTY2]
,[RESPONSIBLE_ENGINEER]
,[RESPONSIBLE_OPERATOR]
,[RESPONSIBLE_PT]
,[RESPONSIBLE_RE]
,[SHUTOFF_REASON_COMMENT]
,[UWI]
,[VENTURE]
,[WATER_DEPTH_UOM]
,[WELL_COMMENTS]
,[WELL_DATA_SOURCE]
,[WELL_DOWNTIME_REASON]
,[WELL_FLUID_TYPE]
,[WELL_NAME]
,[WELL_NAME_ABBR]
,[WELL_PRODUCTION_METHOD_DETAIL]
FROM [WellHeader].[Wells]
GO
-- Auto Generated (Do not modify) 495B3B087B5E776331DDE7ACB920377FA9BC70251393C2CFDE15721F914F0229
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[F_D_PT_WellServices] as
select x.TheDate [DATE]
,[COST_ESTIMATION]
,[FA]
,[PLANNED_PRODUCTION_BOE]
,[PLANNED_PRODUCTION_DELTA_BOE]
,[WORKOVER_ACTUAL_HOURS]
,[WORKOVER_ESTIMATED_HOURS]
,[WORKOVER_ESTIMATED_DAYS]
,[BUDGET]
,[BUDGET_CODE]
,[COUNTRY_CODE]
,[END_DATE]
,[FA_DESCRIPTION]
,[FOLLOWING_WOWI]
,[FOLLOWING_WOWI_2]
,[FOLLOWING_WOWI_3]
,[IS_PLAN]
,[MODIFY_DATE]
,[ORDER_NUMBER]
,[PLANNED_EXECUTION]
,[PLAN_NUMBER]
,[PLAN_STATUS]
,[PLAN_STATUS_DELAY_REASON]
,[RIG_CREW]
,[START_DATE]
,[TYPE]
,[UWI]
,[UBHI]
,[WELL_FAILURE_DATE]
,[SCHEDULE_STARTDATE]
,[WELL_NAME]
,[WORKOVER_TYPE]
,[WORKOVER_TYPE_DESCRIPTION]
,[WOWI_REASON]
,[WOWI_REASON_2]
,[WOWI_TYPE]
FROM [OMVDDFLayer].[V_INT_PT_WellServices] join [OMVDDFLayer].D_Date x on START_DATE <= x.TheFirstOfMonth and END_DATE>= x.TheFirstOfMonth
GO
-- Auto Generated (Do not modify) EB9D2FA53615F049DDBADC88BD973AD731A8260F04B98D6ED7F3EE7D47010171
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[DO_PumpESPDetails] as
SELECT
[UNIQUE_KEY]
,[ESP_INTAKE_SERIAL_NO]
,[ESP_PUMP_RUNTIME]
,[ESP_WORKORDER_NO_INTERNAL]
,[ESP_GASSEP_PART_NO]
,[ESP_CABLE_AWG_SIZE]
,[ESP_CABLE_MLE_SERIE]
,[END_DATE]
,[ESP_BOLT_DISCH_DESC]
,[ESP_INTAKE_PART_NO]
,[ESP_PUMP_CUM_GROSS]
,[ESP_BOLT_DISCH_SERIAL_NO]
,[ESP_CONTROLLER_DESC]
,[ESP_DISCH_PART_NO]
,[ESP_PROTECTORS]
,[ESP_CABLE_MFR]
,[ESP_DOWN_SENS_PART_NO]
,[ESP_INTAKE_TYPE]
,[ESP_GASSEP_MFR]
,[ESP_PUMP_CUM_NET]
,[ESP_WORKORDER_NO_SAP]
,[ESP_PUMP_DEPTH_MD]
,[WO_AAP]
,[ESP_MOTORS]
,[ESP_DOWN_SENS_SERIAL_NO]
,[ESP_SECTION_TYPE]
,[COUNTRY_CODE]
,[ESP_GASSEP_SERIAL_NO]
,[ESP_INTAKE_SERIE]
,[ESP_CABLE_MLE_SERIAL_NO]
,[ESP_INTAKE_DESC]
,[ESP_DOWN_SENS_DESC]
,[ESP_LOWER_CONN_MFR]
,[ESP_BOLT_DISCH_PART_NO]
,[ESP_BOLT_DISCH_MFR]
,[ESP_CABLE_DESC]
,[ESP_CABLE_MLE_DESC]
,[ESP_CABLE_MLE_PART_NO]
,[ESP_DISCH_SERIAL_NO]
,[ESP_GASSEP_DESC]
,[ESP_LOWER_CONN_SERIAL_NO]
,[ESP_CABLE_TYPE]
,[ESP_DISCH_DESC]
,[ESP_PUMPS]
,[ESP_MOTOR_DERATING]
,[ESP_PUMP_DEPTH_TVD]
,[ESP_LOWER_CONN_DESC]
,[UWI]
,[ESP_CABLE_MLE_MFR]
,[ESP_CABLE_PART_NO]
,[ESP_DOWN_SENS_MFR]
,[UBHI]
,[START_DATE]
,[ESP_INSTALL_NUMBER]
,[ESP_DOWN_SENS_SERIE]
,[ESP_CABLE_SERIAL_NO]
,[LAST_UPDATED_DATE]
,[ESP_CABLE_LENGTH]
,[ESP_DISCH_MFR]
,[ESP_INTAKE_MFR]
,[ESP_MOTOR_TYPE]
,[ESP_LOWER_CONN_PART_NO]
,[ESP_CABLE_MLE_TYPE]
FROM [OMVDDFLayer].[V_INT_PT_PumpESPDetails]
GO
-- Auto Generated (Do not modify) 7F991E87EA02F889633924A9366BB69280F129E10F5B4D78913864F9F8ED3912
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[F_RO_D_PO_HydrocarbonByDate] as
SELECT
NULL [ACTIVE_WELL_STATUS]
,[ALLOC_ASSOC_GAS]
, NULL [ALLOC_CO2]
,[ALLOC_CONDENSATE]
,[ALLOC_CONDENSATE_GAS_RATIO]
,[ALLOC_FREE_GAS]
,[ALLOC_GAS]
,[ALLOC_GAS_OIL_RATIO]
,[ALLOC_GASLIFT]
,[ALLOC_GROSS]
, NULL [ALLOC_H2S]
,[ALLOC_INJECTION_WATER]
, NULL [ALLOC_LPG]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_MASS_OIL]
,[ALLOC_NET_OIL]
,[ALLOC_OIL]
, NULL [ALLOC_TOTAL_HYDROCARBON]
,[ALLOC_VOL_CONDENSATE]
,[ALLOC_VOL_OIL]
,[ALLOC_WATER]
,[ALLOC_WATER_CUT]
,[ALLOC_WATER_GAS_RATIO]
, NULL [AREA_NAME]
, NULL [AREA_NAME_ABBREVIATION]
,[CHOKE_POSITION]
,[COUNTRY_CODE]
,[DATE]
, NULL [DATE_STATUS]
,[DENSITY_OIL]
, NULL [EVENT_CONDENSATE_GAS_RATIO]
, NULL [EVENT_DATE]
, NULL [EVENT_WATER_GAS_RATIO]
, NULL [FUNCTIONAL_LOCATION]
, NULL [HORIZON]
,[LAST_UPDATED_DATE]
, NULL [OBJECT_CODE]
, NULL [PERFORATION_ID]
, NULL [PRODUCTION_FIELD_CODE]
, NULL [PRODUCTION_FIELD_NAME]
,[PRODUCTION_HOURS]
, NULL [PRODUCTION_SYSTEM]
, NULL [PRODUCTION_UNIT]
, NULL [SHUT_OFF_REASON]
, NULL [SHUT_OFF_REASON_COMMENT]
,[SHUTIN_HOURS]
,[THEOR_ASSOC_GAS]
, NULL [THEOR_CO2]
,[THEOR_CONDENSATE]
,[THEOR_CONDENSATE_GAS_RATIO]
,[THEOR_FREE_GAS]
,[THEOR_GAS]
,[THEOR_GAS_OIL_RATIO]
, NULL [THEOR_GASLIFT]
,[THEOR_GROSS]
, NULL [THEOR_H2S]
,[THEOR_INJECTION_WATER]
, NULL [THEOR_LPG]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_NET_CONDENSATE]
,[THEOR_NET_OIL]
,[THEOR_OIL]
,[THEOR_TOTAL_HYDROCARBON]
,[THEOR_TOTAL_PRODUCTION]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[THEOR_WATER]
,[THEOR_WATER_CUT]
,[THEOR_WATER_GAS_RATIO]
, NULL [THEOR_WGR]
,'D' [TIME]
,'C' [TYPE]
,[UBHI]
,[UWI]
, NULL [WELL_DOWNTIME_REASON]
,[WELL_FLUID_TYPE]
, NULL [WELL_MAX_GAS_RATE]
, NULL [WELL_MAX_GROSS_RATE]
,NULL [WELL_MAX_H2O_INJ_RATE]
,NULL [WELL_NAME]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,[WELL_TYPE]
,[ALLOC_INJECTION_AIR]
,[THEOR_INJECTION_AIR]
,ALLOC_INJECTION_STEAM
,THEOR_INJECTION_STEAM
FROM [OMVDDFLayer].[V_RO_HydrocarbonByDate]
GO
CREATE VIEW [OMVDDFLayer].[DO_BoreholeTechnicalData] as
SELECT [UNIQUE_KEY]
,[COMPONENT_CONNECTION_WEIGHT_BOTTOM]
,[COMPONENT_CONNECTION_WEIGHT_TOP]
,[COMPONENT_COUNT]
,[COMPONENT_DRIFT]
,[COMPONENT_INNER_DIAMETER]
,[COMPONENT_INNER_DIAMETER_MIN]
,[COMPONENT_LENGTH]
,[COMPONENT_MD_BASE]
,[COMPONENT_MD_TOP]
,[COMPONENT_OUTER_DIAMETER]
,[COMPONENT_OUTER_DIAMETER_MAX]
,[COMPONENT_PRESSURE_RATING_DIFF]
,[COMPONENT_WEIGHT]
,[COMPONENT_COATING_HOLIDAY]
,[COMPONENT_COATING_PROVIDER]
,[COMPONENT_COATING_TYPE]
,[COMPONENT_COMMENT]
,[COMPONENT_CONNECTION_DIAMETER_BOTTOM]
,[COMPONENT_CONNECTION_DIAMETER_TOP]
,[COMPONENT_CONNECTION_END_TYPE_BOTTOM]
,[COMPONENT_CONNECTION_END_TYPE_TOP]
,[COMPONENT_CONNECTION_TYPE_BOTTOM]
,[COMPONENT_CONNECTION_TYPE_TOP]
,[COMPONENT_DESCRIPTION]
,[COMPONENT_GRADE]
,[COMPONENT_PROVIDER]
,[COMPONENT_TYPE]
,[COUNTRY_CODE]
,[LAST_UPDATED_DATE]
,[START_DATE]
,[STRING_DESIGNATION]
,[STRING_MAIN_ID]
,[STRING_SUB_ID]
,[UBHI]
,[UWI]
FROM [ATProductionTechnology].[BoreholeTechnicalData]
union all
SELECT [UNIQUE_KEY]
,[COMPONENT_CONNECTION_WEIGHT_BOTTOM]
,[COMPONENT_CONNECTION_WEIGHT_TOP]
,[COMPONENT_COUNT]
,[COMPONENT_DRIFT]
,[COMPONENT_INNER_DIAMETER]
,[COMPONENT_INNER_DIAMETER_MIN]
,[COMPONENT_LENGTH]
,[COMPONENT_MD_BASE]
,[COMPONENT_MD_TOP]
,[COMPONENT_OUTER_DIAMETER]
,[COMPONENT_OUTER_DIAMETER_MAX]
,[COMPONENT_PRESSURE_RATING_DIFF]
,[COMPONENT_WEIGHT]
,[COMPONENT_COATING_HOLIDAY]
,[COMPONENT_COATING_PROVIDER]
,[COMPONENT_COATING_TYPE]
,[COMPONENT_COMMENT]
,[COMPONENT_CONNECTION_DIAMETER_BOTTOM]
,[COMPONENT_CONNECTION_DIAMETER_TOP]
,[COMPONENT_CONNECTION_END_TYPE_BOTTOM]
,[COMPONENT_CONNECTION_END_TYPE_TOP]
,[COMPONENT_CONNECTION_TYPE_BOTTOM]
,[COMPONENT_CONNECTION_TYPE_TOP]
,[COMPONENT_DESCRIPTION]
,[COMPONENT_GRADE]
,[COMPONENT_PROVIDER]
,[COMPONENT_TYPE]
,[COUNTRY_CODE]
,[LAST_UPDATED_DATE]
,[START_DATE]
,[STRING_DESIGNATION]
,[STRING_MAIN_ID]
,[STRING_SUB_ID]
,[UBHI]
,[UWI]
FROM [ROProductionTechnology].[BoreholeTechnicalData]
GO
-- Auto Generated (Do not modify) D522C5C402345021B7B61932FB8E1D19404FB924DFA3E7B653706D237A69D3F3
CREATE VIEW [dbo].[DO_PO_FacilityCapacities] as
SELECT [UNIQUE_KEY]
,[THEOR_CONDENSATE]
,[THEOR_FREE_GAS]
,[THEOR_GROSS]
,[THEOR_OIL]
,[THEOR_WATER]
,[COUNTRY_CODE]
,[FACILITY_OBJECT_NAME]
,[START_DATE]
FROM V_INT_PO_FacilityCapacities
GO
-- Auto Generated (Do not modify) 52B1EF278632AD71E92F9AD92D6ECAA34C2D02802B6BBBB162FE10097994F5DF
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[DO_WellServices] as
select [UNIQUE_KEY]
,[COST_ESTIMATION]
,[FA]
,[PLANNED_PRODUCTION_BOE]
,[PLANNED_PRODUCTION_DELTA_BOE]
,[WORKOVER_ACTUAL_HOURS]
,[WORKOVER_ESTIMATED_HOURS]
,[WORKOVER_ESTIMATED_DAYS]
,[BUDGET]
,[BUDGET_CODE]
,[COUNTRY_CODE]
,[END_DATE]
,[FA_DESCRIPTION]
,[FOLLOWING_WOWI]
,[FOLLOWING_WOWI_2]
,[FOLLOWING_WOWI_3]
,[IS_PLAN]
,[MODIFY_DATE]
,[ORDER_NUMBER]
,[PLANNED_EXECUTION]
,[PLAN_NUMBER]
,[PLAN_STATUS]
,[PLAN_STATUS_DELAY_REASON]
,[RIG_CREW]
,[START_DATE]
,[TYPE]
,[UWI]
,[UBHI]
,[WELL_FAILURE_DATE]
,[SCHEDULE_STARTDATE]
,[WELL_NAME]
,[WORKOVER_TYPE]
,[WORKOVER_TYPE_DESCRIPTION]
,[WOWI_REASON]
,[WOWI_REASON_2]
,[WOWI_TYPE]
,[PLAN]
,[LAST_UPDATED_DATE]
,[PERFORATION_ID]
-- ,[COSTS_ACTUAL]
-- ,[ACTIVITY_DESCRIPTION_CODE]
-- ,[ACTIVITY_DESCRIPTION_TEXT]
FROM [OMVDDFLayer].[V_INT_PT_WellServices]-- [CDFLayer].[AT_ProductionTechnology_WellServices]
GO
CREATE VIEW [OMVDDFLayer].[V_TEST_Cuummulative_TN]
WITH SCHEMABINDING as
SELECT
[ALLOC_MASS_OIL]
,[ALLOC_VOL_OIL]
,[ALLOC_ASSOC_GAS]
,[ALLOC_CONDENSATE]
,[ALLOC_FREE_GAS]
,[ALLOC_GAS]
,[ALLOC_GASLIFT]
,[ALLOC_INJECTION_WATER]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_VOL_CONDENSATE]
,[ALLOC_WATER]
,sum([ALLOC_MASS_OIL]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_alloc_mass_oil
,sum([ALLOC_VOL_OIL]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_alloc_vol_oil
,sum([ALLOC_ASSOC_GAS]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_ALLOC_ASSOC_GAS
,sum([ALLOC_CONDENSATE]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_ALLOC_CONDENSATE
,sum([ALLOC_FREE_GAS]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_ALLOC_FREE_GAS
,sum([ALLOC_GAS]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_ALLOC_GAS
,sum([ALLOC_GASLIFT]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_ALLOC_GASLIFT
,sum([ALLOC_INJECTION_WATER]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_ALLOC_INJECTION_WATER
,sum([ALLOC_MASS_CONDENSATE]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_ALLOC_MASS_CONDENSATE
,sum([ALLOC_VOL_CONDENSATE]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_ALLOC_VOL_CONDENSATE
,sum([ALLOC_WATER]) over(partition by UWI order by date RANGE UNBOUNDED PRECEDING) cum_ALLOC_WATER
,[COUNTRY_CODE]
,[DATE]
,[UWI]
FROM [ProductionOptimization].[Hydrocarbonbydate]
where COUNTRY_CODE = 'TN';
GO
-- Auto Generated (Do not modify) 1B6F72C417A33549D5E07A7408DBC7CE4C55B1DAC9028FDF64B036A6F1586F05
CREATE VIEW [OMVDDFLayer].[V_NZ_Well_Hierarchy] as
with
legalentity as(
select NAME, WEBID, PARENT_WEBID from [NZCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Legal Entity - ORG'),
contractarea as (
select NAME, WEBID, PARENT_WEBID from [NZCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Contract Area - ORG'),
asset as (
select NAME, WEBID, PARENT_WEBID from [NZCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Asset - ORG'),
subasset as (
select NAME, WEBID, PARENT_WEBID from [NZCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'SubAsset Group - ORG'),
wells as (
select NAME, WEBID, PARENT_WEBID, UWI,HUB, COUNTRY from [NZCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Well - ORG')
select w.HUB, w.COUNTRY,l.NAME as LEGAL_ENTITY, ca.NAME as CONTRACT_AREA, a.NAME as ASSET, sa.NAME as SUB_ASSET_GROUP, w.UWI, w.NAME as WELL_NAME
from legalentity l, contractarea ca, asset a, subasset sa, wells w
where l.WEBID = ca.PARENT_WEBID and ca.WEBID = a.PARENT_WEBID and a.WEBID = sa.PARENT_WEBID and (sa.WEBID = w.PARENT_WEBID or a.WEBID = w.PARENT_WEBID)
--and COUNTRY = 'Austria'
GO
-- Auto Generated (Do not modify) 26728D887824D20C8032E1737A6C9BE50EF67806F11DFF75C23F797AEADE2DD6
CREATE VIEW [OMVDDFLayer].[D_Well_Hierarchy] as
select
w.[UWI]
--,w.[HIERARCHY_TYPE]
,w.[HUB]
,w.[COUNTRY]
,w.[LEGAL_ENTITY]
,w.[CONTRACT_AREA]
,w.[ASSET]
,w.[SUB_ASSET_GROUP]
,w.[WELL_NAME]
from
--[OMVDDFLayer].[V_split_RO_OrganizationalHierarchy] w
[OMVDDFLayer].[V_RO_Well_Hierarchy] w
UNION ALL
select
w.[UWI]
--,w.[HIERARCHY_TYPE]
,w.[HUB]
,w.[COUNTRY]
,w.[LEGAL_ENTITY]
,w.[CONTRACT_AREA]
,w.[ASSET]
,w.[SUB_ASSET_GROUP]
,w.[WELL_NAME]
from
[OMVDDFLayer].[V_AT_Well_Hierarchy] w
UNION ALL
select
w.[UWI]
--,w.[HIERARCHY_TYPE]
,w.[HUB]
,w.[COUNTRY]
,w.[LEGAL_ENTITY]
,w.[CONTRACT_AREA]
,w.[ASSET]
,w.[SUB_ASSET_GROUP]
,w.[WELL_NAME]
from [OMVDDFLayer].[V_NZ_Well_Hierarchy] w
GO
CREATE VIEW [OMVDDFLayer].[RO_FlatSubsurfaceHierarchy] as
with country as
(select * from [ROCurrentHierarchies].[FlatSubsurfaceHierarchy] x
where x.deletion_flag = 'False'
and x.element_template = 'Country - SS' ),
field as
(select * from [ROCurrentHierarchies].[FlatSubsurfaceHierarchy] x
where x.deletion_flag = 'False'
and x.element_template = 'Field - SS' ),
production_unit as
(select * from [ROCurrentHierarchies].[FlatSubsurfaceHierarchy] x
where x.deletion_flag = 'False'
and x.element_template = 'Production Unit - SS' )
SELECT
pi.[PARENT_WEBID]
,pi.[PI_COMMENT]
,pi.[PI_BOTTOM_DEPTH_TVD]
,pi.[PI_DATE_INTERVAL_OPENED]
,pi.[PI_TOP_DEPTH_MD]
,pu.[PU_TOP_DEPTH]
,pi.[PI_ALLOCATION_FACTOR_INJECTION_STEAM]
,pi.[PI_STATUS]
,c.[COUNTRY]
,pi.[DESCRIPTION]
,pi.[PI_ALLOCATION_FACTOR_INJECTION_WATER]
,pi.[PI_ALLOCATION_FACTOR_GAS]
,pi.[PI_TOP_DEPTH_TVDSS]
,pi.[PI_ALLOCATION_FACTOR_WATER]
,pi.[PI_TOP_DEPTH_TVD]
,pi.[ELEMENT_ID] prod_interval_id
,pu.[ELEMENT_ID] prod_unit_id
,f.[ELEMENT_ID] field_id
,pi.[WEBID]
,pi.[UWI]
,pi.[PI_BOTTOM_DEPTH_MD]
,pi.[UBHI]
,pi.[PI_DATE_INTERVAL_CLOSED]
,f.[F_PRODUCTION_SYSTEM_CODE]
,pi.[ELEMENT_ID_TYPE]
,pi.[ELEMENT_TEMPLATE]
,pi.[PI_ALLOCATION_FACTOR_OIL]
,pi.[SOURCE_SYSTEM]
,pu.[PU_BOTTOM_DEPTH]
,pi.[LAST_UPDATED_DATE]
,pu.[PU_CONTACT_TYPE]
,pi.[DELETION_FLAG]
,pi.[PI_BOTTOM_DEPTH_TVDSS]
,pi.[PI_PERFORATION_CODE]
,pi.[ELEMENT_DESCRIPTION]
,pi.[ELEMENT_NAME] prod_interval_name
,pu.[ELEMENT_NAME] prod_unit_name
,f.[ELEMENT_NAME] field_name
,pi.[PI_ALLOCATION_FACTOR_CONDENSATE]
,f.[F_ASSET_NAME]
,pu.[PU_CODE]
FROM [ROCurrentHierarchies].[FlatSubsurfaceHierarchy] pi join production_unit pu on pi.[PARENT_WEBID] =pu.[WEBID]
join field f on pu.[PARENT_WEBID] =f.[WEBID] join country c on f.[PARENT_WEBID] =c.[WEBID]
where pi.deletion_flag = 'False'
and pi.element_template = 'Production Interval - SS'
GO
-- Auto Generated (Do not modify) F6CBFE411EEFF845ACC9CA843AC0E3969C2F3826C6AAC627FD0D9DA147F10082
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_RO_PO_WellPerformance] as
SELECT [UNIQUE_KEY]
,[WT_A_ANNULUS_PRESSURE]
,[WT_CGR]
,[WT_CHOKE_POSITION]
,[WT_DURATION]
,[WT_NET_CONDENSATE_RATE]
,[WT_NET_OIL_MASS_RATE]
,[WT_NET_OIL_RATE]
,[WT_TOTAL_GAS_RATE]
,[WT_TOTAL_WATER_RATE]
,[WT_TUBING_HEAD_FLOWING_PRESSURE]
,[WT_WATERCUT]
,[WT_WGR]
,[COUNTRY_CODE]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,[UBHI]
,[UWI]
,[WELLTEST_STATUS]
,cast([WT_DATE] AS DATETIME) as [WT_DATE]
,cast([WT_START_TEST] AS DATETIME) as [WT_START_TEST]
,LEAD(cast([WT_START_TEST] AS DATETIME), 1) OVER(partition by UBHI
ORDER BY UBHI,cast([WT_START_TEST] AS DATETIME) ASC) AS [WT_END_TEST]
,[WT_NET_CONDENSATE_MASS_RATE]
FROM [ROProductionOptimization].[WellPerformance]
GO
CREATE VIEW [OMVDDFLayer].[F_D_PO_Potential] as
SELECT
[POT_ASSOC_GAS]
,[POT_FREE_GAS]
,[POT_OIL]
,[POT_WATER]
,[POT_WATERCUT]
,[COUNTRY_CODE]
,[LAST_UPDATED_DATE]
,x.TheDate [DATE]
,[UBHI]
,[UWI]
,[POT_CONDENSATE]
,[POT_INJ_WATER]
FROM [OMVDDFLayer].[V_RO_Potential]
join [OMVDDFLayer].D_Date x on pot_date = x.thefirstofmonth
GO
-- Auto Generated (Do not modify) 515DCC9F098B30B360D6A98EF470F58946FF787A3FF2D233543CC5BAAC8AAEEC
CREATE VIEW [OMVDDFLayer].[D_Well] as
select
w.[UWI]
,w.[HUB]
,w.[COUNTRY]
,w.[LEGAL_ENTITY]
,w.[CONTRACT_AREA]
,w.[ASSET]
,w.[SUB_ASSET_GROUP]
,wh.[WELL_NAME]
,wh.[ALTERNATIVE_COORDINATE_SYSTEM]
, wh.[ALTERNATIVE_EASTING]
, wh.[ALTERNATIVE_NORTHING]
, wh.[AREA_NAME]
, wh.[AREA_NAME_ABBR]
, wh.[BLOCK_CODE]
, wh.[BLOCK_NAME]
, wh.[CONTRACT_CODE]
, wh.[CONTRACT_NAME]
, wh.[COORD_STATUS]
, wh.[COUNTRY_CODE]
, wh.[ELEVATION_ABOVE_DATUM]
, wh.[ELEVATION_VALUE_UOM]
, wh.[EXISTENCE_KIND]
, wh.[GENERAL_FIELD_NAME]
, wh.[GENERAL_FIELD_NAME_SOURCE]
, wh.[GEOCOORD_EPSG]
, wh.[HORIZON_PRODUCTION_UNIT]
, wh.[LAST_UPDATED_DATE]
, wh.[LIFE_CYCLE_PHASE]
, cast(wh.[OMV_OWNED] AS VARCHAR) [OMV_OWNED]
, wh.[ON_OFF_WELL]
, wh.[ORIGINAL_COORDINATE_SYSTEM]
, wh.[ORIGINAL_EASTING]
, wh.[ORIGINAL_NORTHING]
, wh.[PERMANENT_ELEV_REF_TYPE]
, wh.[PRODUCTION_DENSITY_OIL]
, wh.[PRODUCTION_FIELD_CODE]
, wh.[PRODUCTION_SYSTEM]
, wh.[PROJCOORD_EPSG]
, wh.[RESPONSIBLE_DEPUTY1]
, wh.[RESPONSIBLE_DEPUTY2]
, wh.[RESPONSIBLE_ENGINEER]
, wh.[RESPONSIBLE_OPERATOR]
, wh.[RESPONSIBLE_PT]
, wh.[RESPONSIBLE_RE]
, wh.[SHUTOFF_REASON_COMMENT]
, wh.[SURFACE_LATITUDE_WGS84]
, wh.[SURFACE_LONGITUDE_WGS84]
--, wh.[UWI]
, wh.[VENTURE]
, wh.[WATER_DEPTH]
, wh.[WATER_DEPTH_UOM]
, wh.[WELL_COMMENTS]
, wh.[WELL_DATA_SOURCE]
, wh.[WELL_DOWNTIME_REASON]
, wh.[WELL_FLUID_TYPE]
--, wh.[WELL_NAME]
, wh.[WELL_NAME_ABBR]
, wh.[WELL_PRODUCTION_METHOD]
, wh.[WELL_PRODUCTION_METHOD_DETAIL]
, cast(wh.[WELL_TYPE] AS VARCHAR) [WELL_TYPE]
from
--[OMVDDFLayer].[V_split_RO_OrganizationalHierarchy] w
[OMVDDFLayer].[V_INT_Well_Hierarchy] w join [OMVDDFLayer].[V_INT_WH_Wells] wh on w.UWI = wh.UWI
UNION ALL
select
w.[UWI]
,w.[HUB]
,w.[COUNTRY]
,w.[LEGAL_ENTITY]
,w.[CONTRACT_AREA]
,w.[ASSET]
,w.[SUB_ASSET_GROUP]
,wh.[WELL_NAME]
,wh.[ALTERNATIVE_COORDINATE_SYSTEM]
, wh.[ALTERNATIVE_EASTING]
, wh.[ALTERNATIVE_NORTHING]
,wh.[AREA_NAME]
, wh.[AREA_NAME_ABBR]
, wh.[BLOCK_CODE]
, wh.[BLOCK_NAME]
, wh.[CONTRACT_CODE]
, wh.[CONTRACT_NAME]
, wh.[COORD_STATUS]
, wh.[COUNTRY_CODE]
, wh.[ELEVATION_ABOVE_DATUM]
, wh.[ELEVATION_VALUE_UOM]
, wh.[EXISTENCE_KIND]
, wh.[GENERAL_FIELD_NAME]
, wh.[GENERAL_FIELD_NAME_SOURCE]
, wh.[GEOCOORD_EPSG]
, wh.[HORIZON_PRODUCTION_UNIT]
, wh.[LAST_UPDATED_DATE]
, wh.[LIFE_CYCLE_PHASE]
, wh.[OMV_OWNED]
, wh.[ON_OFF_WELL]
, wh.[ORIGINAL_COORDINATE_SYSTEM]
, wh.[ORIGINAL_EASTING]
, wh.[ORIGINAL_NORTHING]
, wh.[PERMANENT_ELEV_REF_TYPE]
, wh.[PRODUCTION_DENSITY_OIL]
, wh.[PRODUCTION_FIELD_CODE]
, wh.[PRODUCTION_SYSTEM]
, wh.[PROJCOORD_EPSG]
, wh.[RESPONSIBLE_DEPUTY1]
, wh.[RESPONSIBLE_DEPUTY2]
, wh.[RESPONSIBLE_ENGINEER]
, wh.[RESPONSIBLE_OPERATOR]
, wh.[RESPONSIBLE_PT]
, wh.[RESPONSIBLE_RE]
, wh.[SHUTOFF_REASON_COMMENT]
, wh.[SURFACE_LATITUDE_WGS84]
, wh.[SURFACE_LONGITUDE_WGS84]
--, wh.[UWI]
, wh.[VENTURE]
, wh.[WATER_DEPTH]
, wh.[WATER_DEPTH_UOM]
, wh.[WELL_COMMENTS]
, wh.[WELL_DATA_SOURCE]
, wh.[WELL_DOWNTIME_REASON]
, wh.[WELL_FLUID_TYPE]
--, wh.[WELL_NAME]
, wh.[WELL_NAME_ABBR]
, wh.[WELL_PRODUCTION_METHOD]
, wh.[WELL_PRODUCTION_METHOD_DETAIL]
, wh.[WELL_TYPE]
from
--[OMVDDFLayer].[V_split_RO_OrganizationalHierarchy] w
[OMVDDFLayer].[V_RO_Well_Hierarchy] w join [OMVDDFLayer].[V_RO_WH_Wells] wh on w.UWI = wh.UWI
UNION ALL
select
wh.[UWI]
,cast(null as varchar) [HUB]
,case when COUNTRY_CODE = 'TN' then 'Tunisia'
when COUNTRY_CODE = 'AE' then 'UAE'
end [COUNTRY]
,cast(null as varchar)[LEGAL_ENTITY]
,cast(null as varchar)[CONTRACT_AREA]
,cast(null as varchar)[ASSET]
,cast(null as varchar)[SUB_ASSET_GROUP]
, wh.[WELL_NAME]
, wh.[ALTERNATIVE_COORDINATE_SYSTEM]
, wh.[ALTERNATIVE_EASTING]
, wh.[ALTERNATIVE_NORTHING]
, wh.[AREA_NAME]
, wh.[AREA_NAME_ABBR]
, wh.[BLOCK_CODE]
, wh.[BLOCK_NAME]
, wh.[CONTRACT_CODE]
, wh.[CONTRACT_NAME]
, wh.[COORD_STATUS]
, wh.[COUNTRY_CODE]
, wh.[ELEVATION_ABOVE_DATUM]
, wh.[ELEVATION_VALUE_UOM]
, wh.[EXISTENCE_KIND]
, wh.[GENERAL_FIELD_NAME]
, wh.[GENERAL_FIELD_NAME_SOURCE]
, wh.[GEOCOORD_EPSG]
, wh.[HORIZON_PRODUCTION_UNIT]
, wh.[LAST_UPDATED_DATE]
, wh.[LIFE_CYCLE_PHASE]
, cast(wh.[OMV_OWNED]AS VARCHAR)
, wh.[ON_OFF_WELL]
, wh.[ORIGINAL_COORDINATE_SYSTEM]
, wh.[ORIGINAL_EASTING]
, wh.[ORIGINAL_NORTHING]
, wh.[PERMANENT_ELEV_REF_TYPE]
, wh.[PRODUCTION_DENSITY_OIL]
, wh.[PRODUCTION_FIELD_CODE]
, wh.[PRODUCTION_SYSTEM]
, wh.[PROJCOORD_EPSG]
, wh.[RESPONSIBLE_DEPUTY1]
, wh.[RESPONSIBLE_DEPUTY2]
, wh.[RESPONSIBLE_ENGINEER]
, wh.[RESPONSIBLE_OPERATOR]
, wh.[RESPONSIBLE_PT]
, wh.[RESPONSIBLE_RE]
, wh.[SHUTOFF_REASON_COMMENT]
, wh.[SURFACE_LATITUDE_WGS84]
, wh.[SURFACE_LONGITUDE_WGS84]
--, wh.[UWI]
, wh.[VENTURE]
, wh.[WATER_DEPTH]
, wh.[WATER_DEPTH_UOM]
, wh.[WELL_COMMENTS]
, wh.[WELL_DATA_SOURCE]
, wh.[WELL_DOWNTIME_REASON]
, wh.[WELL_FLUID_TYPE]
--, wh.[WELL_NAME]
, wh.[WELL_NAME_ABBR]
, wh.[WELL_PRODUCTION_METHOD]
, wh.[WELL_PRODUCTION_METHOD_DETAIL]
, cast(wh.[WELL_TYPE]AS VARCHAR)
from
[OMVDDFLayer].[V_INT_WH_Wells] wh where COUNTRY_CODE in ('TN','AE')
GO
CREATE VIEW [dbo].[V_INT_ProductionSystemHierarchy] as
SELECT [UNIQUE_KEY]
,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PATH]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,[LAST_UPDATED_DATE]
,[NAME]
,[PARENT_WEBID]
,[PATH]
,[TEMPLATE_NAME]
,[UBHI]
,[UWI]
,[WEBID]
FROM [ROCurrentHierarchies].[ProductionSystemHierarchy]
GO
-- Auto Generated (Do not modify) 790C16148251D85ECC7B305C1F0CBB71FC8A541B092E583AAD7A0BA959028DAA
CREATE VIEW [OMVDDFLayer].[V_AT_Deferment] as
SELECT UNIQUE_KEY
,[THEOR_ASSOC_GAS]
,[THEOR_CONDENSATE]
,[THEOR_DEFFERED_GAS]
,[THEOR_FREE_GAS]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_OIL]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[CLIENT_ID]
,[COMMENT]
,[COUNTRY_CODE]
,cast([DATE] AS DATE) as [DATE]
,[DEFERMENT_CHOKE]
,[DEFERMENT_IAP]
,[DEFERMENT_ID]
,[DEFERMENT_NAME]
,[DEVIATION_EVENT]
,[DEVIATION_EVENT_EXPLANATION]
,cast([END_DATE] AS DATETIME) as [END_DATE]
,[EVENT_TYPE]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,[OMV_SHARE_TYPE]
,[SHUT_OFF_REASON]
,[SHUT_OFF_REASON_NATIVE]
,cast([START_DATE] AS DATETIME) as [START_DATE]
,[TIME]
,[TYPE]
,[UWI]
,[ADD_COMMENT]
,[SHUT_OFF_REASON_PLS]
,[DEFERMENT_ROOT_CAUSE]
,[DEFERMENT_CAUSE]
,[SHUT_OFF_REASON_PLS_NATIVE]
,[DEFERMENT_SUB_CHOKE_EQU]
,[VERSION_ID]
,[PRODUCTION_SYSTEM]
,[DEFERMENT_SUB_CHOKE]
,[AREA_NAME_ABBREVIATION]
,[PERFORATION_ID]
FROM [ProductionOptimization].[Deferments]
GO
-- Auto Generated (Do not modify) FB1C4421916E535C892C82669F86FA49BF4776FA195CDEDC2A8EC148C2CDAFA0
CREATE VIEW [OMVDDFLayer].[F_D_O_DailyWellParameters] as
SELECT [UNIQUE_KEY]
,[CHOKE_POSITION]
,[UWI]
,[UBHI]
,[OMVDDFLayer].[get_BARG_from_PSI]([A_ANNULUS_PRESSURE]) [A_ANNULUS_PRESSURE]
,[OMVDDFLayer].[get_BARG_from_PSI]([C_ANNULUS_PRESSURE]) [C_ANNULUS_PRESSURE]
,[DOWNHOLE_TEMPERATURE]
,[LAST_UPDATED_DATE]
,[DATE]
,[FLOWLINE_TEMPERATURE]
,[OMVDDFLayer].[get_BARG_from_PSI]([FLOWLINE_PRESSURE]) [FLOWLINE_PRESSURE]
,[OMVDDFLayer].[get_BARG_from_PSI]([B_ANNULUS_PRESSURE]) [B_ANNULUS_PRESSURE]
,[OMVDDFLayer].[get_BARG_from_PSI]([WELLHEAD_PRESSURE]) [WELLHEAD_PRESSURE]
,[WELLHEAD_TEMPERATURE]
,[OMVDDFLayer].[get_BARG_from_PSI]([DOWNHOLE_PRESSURE]) [DOWNHOLE_PRESSURE]
,[COUNTRY_CODE]
from OMVDDFLayer.V_INT_O_DailyWellParameters
GO
-- Auto Generated (Do not modify) 8B61BD6891391C06E75A89575B19E192D6F59D647C9365DB5BE9DAD281D506AD
CREATE VIEW [OMVDDFLayer].[DO_SubsurfaceHierarchy] as
with country as
(select * from [OMVDDFLayer].[V_INT_FlatSubsurfaceHierarchy] x
where x.DELETION_FLAG = 'False'
and x.ELEMENT_TEMPLATE = 'Country - SS' ),
field as
(select * from [OMVDDFLayer].[V_INT_FlatSubsurfaceHierarchy] x
where x.DELETION_FLAG = 'False'
and x.ELEMENT_TEMPLATE = 'Field - SS' ),
production_unit as
(select * from [OMVDDFLayer].[V_INT_FlatSubsurfaceHierarchy] x
where x.DELETION_FLAG = 'False'
and x.ELEMENT_TEMPLATE = 'Production Unit - SS' )
SELECT pi.[UNIQUE_KEY]
,pi.[PARENT_WEBID]
,pi.[PI_COMMENT]
,pi.[PI_BOTTOM_DEPTH_TVD]
,pi.[PI_DATE_INTERVAL_OPENED]
,pi.[PI_TOP_DEPTH_MD]
,pu.[PU_TOP_DEPTH]
-- ,pi.[PI_ALLOCATION_FACTOR_INJECTION_STEAM]
,pi.[PI_STATUS]
,c.[COUNTRY]
,pi.[DESCRIPTION]
-- ,pi.[PI_ALLOCATION_FACTOR_INJECTION_WATER]
-- ,pi.[PI_ALLOCATION_FACTOR_GAS]
,pi.[PI_TOP_DEPTH_TVDSS]
-- ,pi.[PI_ALLOCATION_FACTOR_WATER]
,pi.[PI_TOP_DEPTH_TVD]
,pi.[ELEMENT_ID] prod_interval_id
,pu.[ELEMENT_ID] prod_unit_id
,f.[ELEMENT_ID] field_id
,pi.[WEBID]
,pi.[UWI]
,pi.[PI_BOTTOM_DEPTH_MD]
,pi.[UBHI]
,pi.[PI_DATE_INTERVAL_CLOSED]
,f.[F_PRODUCTION_SYSTEM_CODE]
,pi.[ELEMENT_ID_TYPE]
,pi.[ELEMENT_TEMPLATE]
-- ,pi.[PI_ALLOCATION_FACTOR_OIL]
,pi.[SOURCE_SYSTEM]
,pu.[PU_BOTTOM_DEPTH]
,pi.[LAST_UPDATED_DATE]
,pu.[PU_CONTACT_TYPE]
,pi.[DELETION_FLAG]
,pi.[PI_BOTTOM_DEPTH_TVDSS]
,pi.[PI_PERFORATION_CODE]
,pi.[ELEMENT_DESCRIPTION]
,pi.[ELEMENT_NAME] prod_interval_name
,pu.[ELEMENT_NAME] prod_unit_name
,f.[ELEMENT_NAME] field_name
-- ,pi.[PI_ALLOCATION_FACTOR_CONDENSATE]
,f.[F_ASSET_NAME]
,pu.[PU_CODE]
FROM [OMVDDFLayer].[V_INT_FlatSubsurfaceHierarchy] pi join production_unit pu on pi.[PARENT_WEBID] =pu.[WEBID]
join field f on pu.[PARENT_WEBID] =f.[WEBID] join country c on f.[PARENT_WEBID] =c.[WEBID]
where pi.DELETION_FLAG = 'False'
and pi.ELEMENT_TEMPLATE = 'Production Interval - SS'
GO
-- Auto Generated (Do not modify) 52CD693195254A6895423402CDA85E8588CCFF7CA0FDF981CB36C4CC8B944805
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[DO_PumpSrpDetails] as
select [UNIQUE_KEY]
,[DOG_LEG_SEVERITY]
,[SRP_MOTOR_POWER]
,[SRP_MOTOR_RPM]
,[SRP_MOTOR_SHEAVE]
,[SRP_PUMP_DEPTH]
,[SRP_PUMP_DEPTH_TVD]
,[SRP_ROD_API_NO]
,[SRP_RUNTIME_CURRENT]
,[SRP_RUNTIME_LAST]
,[SRP_SINKERBAR_LENGTH]
,[SRP_STROKE_LENGTH]
,[SRP_STROKE_MINUTES]
,[SRP_UNIT_ALLOW_GEARBOX_TORQUE]
,[SRP_UNIT_ALLOW_POL_ROD_LOAD]
,[SRP_UNIT_GEARBOX_RATIO]
,[SRP_UNIT_GEARBOX_SHEAVE]
,[COUNTRY_CODE]
,[END_DATE]
,[LAST_UPDATED_DATE]
,[SRP_BEAMGAS_COMPR_DEINSTALL_DATE]
,[SRP_BEAMGAS_COMPR_INSTALL_DATE]
,[SRP_BEAMGAS_COMPR_TYPE]
,[SRP_MOTOR_TYPE]
,[SRP_PUMP_API_NO]
,[SRP_PUMP_BARREL_MFR]
,[SRP_PUMP_MFR]
,[SRP_PUMP_PLUNGER_MFR]
,[SRP_PUMP_SERIAL_NO]
,[SRP_SINKERBAR_DIAMETER]
,[SRP_UNIT_GEARBOX_MFR]
,[SRP_UNIT_INV_NO]
,[SRP_UNIT_MFR]
,[START_DATE]
,[UBHI]
,[UWI]
,[WELL_COMPLETION_CLOSED_INSTALL_FLAG]
FROM OMVDDFLayer.V_INT_PT_PumpSrpDetails
GO
-- Auto Generated (Do not modify) 51F026D24FA2627723834D54433A3EAB269C2AD37C0573BF25FF5103EEBFE093
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_INT_PT_WellEventOperations] as
SELECT [UNIQUE_KEY]
,[ACTION_TAKEN_DESCRIPTION]
,[OPERATION_EQUIPMENT_TYPE_DESCRIPTION]
,[ASSET_NAME]
,[OPERATION_EQUIPMENT_TYPE_CODE]
,[COUNTRY_CODE]
,[OPERATION_EQUIPMENT_FAILURE_DESCRIPTION]
,[OPERATION_TYPE_CODE]
,[ACTION_TAKEN_CODE]
,[EVENT_REMARKS]
,[REPORT_DESCRIPTION]
,[REPORT_INTERNAL_ID]
,[OPERATION_END_DATE]
,[OPERATION_EQUIPMENT_FAILURE_CODE]
,[EVENT_TYPE_CODE]
,[UNIQUE_ID]
,[OPERATION_COMMENT]
,[OPERATION_MODIFICATION_TYPE_DESCRIPTION]
,[OPERATION_PROCESS_TYPE_DESCRIPTION]
,[EVENT_UNIQUE_ID]
,[OPERATION_TYPE_DESCRIPTION]
,[OPERATION_START_DATE]
,[REPORT_DATE]
,[OPERATION_CAUSE_CODE]
,[OPERATION_CAUSE_DESCRIPTION]
,[LAST_UPDATED_DATE]
,[OPERATION_PROCESS_TYPE_CODE]
,[OPERATION_MODIFICATION_TYPE_CODE]
,[UWI]
FROM [ROProductionTechnology].[WellEventOperations]
GO
CREATE VIEW [OMVDDFLayer].[F_D_PT_AT_WellServices] as
select [UNIQUE_KEY]
,[COST_ESTIMATION]
,[FA]
,[PLANNED_PRODUCTION_BOE]
,[PLANNED_PRODUCTION_DELTA_BOE]
,[WORKOVER_ACTUAL_HOURS]
,[WORKOVER_ESTIMATED_HOURS]
,[WORKOVER_ESTIMATED_DAYS]
,[BUDGET]
,[BUDGET_CODE]
,[COUNTRY_CODE]
,[END_DATE]
,[FA_DESCRIPTION]
,[FOLLOWING_WOWI]
,[FOLLOWING_WOWI_2]
,[FOLLOWING_WOWI_3]
,[IS_PLAN]
,[MODIFY_DATE]
,[ORDER_NUMBER]
,[PLANNED_EXECUTION]
,[PLAN_NUMBER]
,[PLAN_STATUS]
,[PLAN_STATUS_DELAY_REASON]
,[RIG_CREW]
,[START_DATE]
,[TYPE]
,[UWI]
,[UBHI]
,[WELL_FAILURE_DATE]
,[SCHEDULE_STARTDATE]
,[WELL_NAME]
,[WORKOVER_TYPE]
,[WORKOVER_TYPE_DESCRIPTION]
,[WOWI_REASON]
,[WOWI_REASON_2]
,[WOWI_TYPE]
,[PLAN]
,[LAST_UPDATED_DATE]
,[PERFORATION_ID]
,[COSTS_ACTUAL]
,[ACTIVITY_DESCRIPTION_CODE]
,[ACTIVITY_DESCRIPTION_TEXT]
FROM [ATProductionTechnology].[WellServices]
GO
CREATE VIEW [OMVDDFLayer].[DO_WellServicesEconomicSimulation] as
SELECT [UNIQUE_KEY]
,[MANUFACTURING_COSTS]
,[ROYALTIES]
,[TAX]
,[CONDENSATE_PRICE]
,[COUNTRY_CODE]
,[DECLINE_RATE]
,[GAS_PRICE]
,[LAST_UPDATED_DATE]
,[OIL_PRICE]
,[PLAN_NUMBER]
,[REFERENCE_YEAR]
,[SIMULATION_ID]
,[UWI]
,[WO_AAP]
FROM [ATProductionTechnology].[WellServicesEconomicSimulation]
GO
-- Auto Generated (Do not modify) 99D90B1A7C4553AEE3CA3A04A5D8E448932E217D12DC25B1D82A4EC6E96CB537
CREATE VIEW [OMVDDFLayer].[V_INT_PO_CalculatedAttributes] as
SELECT UNIQUE_KEY
,[DAILY_ACTUAL_CGR]
,[DAILY_ACTUAL_WC]
,[DAILY_ACTUAL_WOR]
,[DATE]
,[UWI]
FROM [ProductionOptimization].[CalculatedAttributes]
GO
-- Auto Generated (Do not modify) F37FB83EAA245AF489E03D7F950F3F8C0928547F7038F1C73D5E5EE141FD9F38
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[F_D_PO_HydrocarbonByDate] as
SELECT
[ACTIVE_WELL_STATUS]
,[ALLOC_ASSOC_GAS]
,[ALLOC_CO2]
,[ALLOC_CONDENSATE]
,[ALLOC_CONDENSATE_GAS_RATIO]
,[ALLOC_FREE_GAS]
,[ALLOC_GAS]
,[ALLOC_GAS_OIL_RATIO]
,[ALLOC_GASLIFT]
,[ALLOC_GROSS]
,[ALLOC_H2S]
,[ALLOC_INJECTION_WATER]
,[ALLOC_LPG]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_MASS_OIL]
,[ALLOC_NET_OIL]
,[ALLOC_OIL]
,[ALLOC_TOTAL_HYDROCARBON]
,[ALLOC_VOL_CONDENSATE]
,[ALLOC_VOL_OIL]
,[ALLOC_WATER]
,[ALLOC_WATER_CUT]
,[ALLOC_WATER_GAS_RATIO]
,[AREA_NAME]
,[AREA_NAME_ABBREVIATION]
,[CHOKE_POSITION]
,[COUNTRY_CODE]
,[DATE]
,[DATE_STATUS]
,[DENSITY_OIL]
,[EVENT_CONDENSATE_GAS_RATIO]
,[EVENT_DATE]
,[EVENT_WATER_GAS_RATIO]
,[FUNCTIONAL_LOCATION]
,[HORIZON]
,[LAST_UPDATED_DATE]
,[OBJECT_CODE]
,[PERFORATION_ID]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_FIELD_NAME]
,[PRODUCTION_HOURS]
,[PRODUCTION_SYSTEM]
,[PRODUCTION_UNIT]
,[SHUT_OFF_REASON]
,[SHUT_OFF_REASON_COMMENT]
,[SHUTIN_HOURS]
,[THEOR_ASSOC_GAS]
,[THEOR_CO2]
,[THEOR_CONDENSATE]
,[THEOR_CONDENSATE_GAS_RATIO]
,[THEOR_FREE_GAS]
,[THEOR_GAS]
,[THEOR_GAS_OIL_RATIO]
,[THEOR_GASLIFT]
,[THEOR_GROSS]
,[THEOR_H2S]
,[THEOR_INJECTION_WATER]
,[THEOR_LPG]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_NET_CONDENSATE]
,[THEOR_NET_OIL]
,[THEOR_OIL]
,[THEOR_TOTAL_HYDROCARBON]
,[THEOR_TOTAL_PRODUCTION]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[THEOR_WATER]
,[THEOR_WATER_CUT]
,[THEOR_WATER_GAS_RATIO]
,[THEOR_WGR]
,[TIME]
,[TYPE]
,[UBHI]
,[UWI]
,[WELL_DOWNTIME_REASON]
,[WELL_FLUID_TYPE]
,[WELL_MAX_GAS_RATE]
,[WELL_MAX_GROSS_RATE]
,[WELL_MAX_H2O_INJ_RATE]
,[WELL_NAME]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,[WELL_TYPE]
,[ALLOC_INJECTION_AIR]
,[THEOR_INJECTION_AIR]
,ALLOC_INJECTION_STEAM
,THEOR_INJECTION_STEAM
FROM [OMVDDFLayer].[V_INT_HydrocarbonbyDate]
where [TIME] = 'F' or [TIME] is null
UNION ALL
SELECT
NULL [ACTIVE_WELL_STATUS]
,[ALLOC_ASSOC_GAS]
, NULL [ALLOC_CO2]
,[ALLOC_CONDENSATE]
,[ALLOC_CONDENSATE_GAS_RATIO]
,[ALLOC_FREE_GAS]
,[ALLOC_GAS]
,[ALLOC_GAS_OIL_RATIO]
,[ALLOC_GASLIFT]
,[ALLOC_GROSS]
, NULL [ALLOC_H2S]
,[ALLOC_INJECTION_WATER]
, NULL [ALLOC_LPG]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_MASS_OIL]
,[ALLOC_NET_OIL]
,[ALLOC_OIL]
, NULL [ALLOC_TOTAL_HYDROCARBON]
,[ALLOC_VOL_CONDENSATE]
,[ALLOC_VOL_OIL]
,[ALLOC_WATER]
,[ALLOC_WATER_CUT]
,[ALLOC_WATER_GAS_RATIO]
, NULL [AREA_NAME]
, NULL [AREA_NAME_ABBREVIATION]
,[CHOKE_POSITION]
,[COUNTRY_CODE]
,[DATE]
, NULL [DATE_STATUS]
,[DENSITY_OIL]
, NULL [EVENT_CONDENSATE_GAS_RATIO]
, NULL [EVENT_DATE]
, NULL [EVENT_WATER_GAS_RATIO]
, NULL [FUNCTIONAL_LOCATION]
, NULL [HORIZON]
,[LAST_UPDATED_DATE]
, NULL [OBJECT_CODE]
, NULL [PERFORATION_ID]
, NULL [PRODUCTION_FIELD_CODE]
, NULL [PRODUCTION_FIELD_NAME]
,[PRODUCTION_HOURS]
, NULL [PRODUCTION_SYSTEM]
, NULL [PRODUCTION_UNIT]
, NULL [SHUT_OFF_REASON]
, NULL [SHUT_OFF_REASON_COMMENT]
,[SHUTIN_HOURS]
,[THEOR_ASSOC_GAS]
, NULL [THEOR_CO2]
,[THEOR_CONDENSATE]
,[THEOR_CONDENSATE_GAS_RATIO]
,[THEOR_FREE_GAS]
,[THEOR_GAS]
,[THEOR_GAS_OIL_RATIO]
, NULL [THEOR_GASLIFT]
,[THEOR_GROSS]
, NULL [THEOR_H2S]
,[THEOR_INJECTION_WATER]
, NULL [THEOR_LPG]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_NET_CONDENSATE]
,[THEOR_NET_OIL]
,[THEOR_OIL]
,[THEOR_TOTAL_HYDROCARBON]
,[THEOR_TOTAL_PRODUCTION]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[THEOR_WATER]
,[THEOR_WATER_CUT]
,[THEOR_WATER_GAS_RATIO]
, NULL [THEOR_WGR]
,'D' [TIME]
,'C' [TYPE]
,[UBHI]
,[UWI]
, NULL [WELL_DOWNTIME_REASON]
,[WELL_FLUID_TYPE]
, NULL [WELL_MAX_GAS_RATE]
, NULL [WELL_MAX_GROSS_RATE]
,NULL [WELL_MAX_H2O_INJ_RATE]
,NULL [WELL_NAME]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,[WELL_TYPE]
,[ALLOC_INJECTION_AIR]
,[THEOR_INJECTION_AIR]
,ALLOC_INJECTION_STEAM
,THEOR_INJECTION_STEAM
FROM [OMVDDFLayer].[V_RO_HydrocarbonByDate]
GO
-- Auto Generated (Do not modify) 34F9F609C530FD34ED9A3BC8CBD076CF0D39BA54E21E62867FDEABF651A7CEDE
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_INT_L_Compozition] as
SELECT [UNIQUE_KEY]
,[SAMPLE_DESCRIPTION]
,[ANALYZE_DATE]
,[PARAMETER_NAME]
,[SAMPLE_CODE]
,[PARAMETER_PRIMARY_FLUID_TYPE]
,[SAMPLING_DATE]
,[PARAMETER_TYPE]
,[COUNTRY_CODE]
,[PARAMETER_UOM]
,[PARAMETER_VALUE]
,[LAST_UPDATED_DATE]
,[SAMPLE_SOURCE]
,[SAMPLE_SOURCE_UID]
FROM [ROLaboratory].[Compozition]
GO
-- Auto Generated (Do not modify) 7C2B5A2547C469ABB4C55AB5E137DC159B5B1973A75D846137241018CD636ED2
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[F_M_PO_HydrocarbonByDate] as
SELECT
[ACTIVE_WELL_STATUS]
,[ALLOC_ASSOC_GAS]
,[ALLOC_CO2]
,[ALLOC_CONDENSATE]
,[ALLOC_CONDENSATE_GAS_RATIO]
,[ALLOC_FREE_GAS]
,[ALLOC_GAS]
,[ALLOC_GAS_OIL_RATIO]
,[ALLOC_GASLIFT]
,[ALLOC_GROSS]
,[ALLOC_H2S]
,[ALLOC_INJECTION_WATER]
,[ALLOC_LPG]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_MASS_OIL]
,[ALLOC_NET_OIL]
,[ALLOC_OIL]
,[ALLOC_TOTAL_HYDROCARBON]
,[ALLOC_VOL_CONDENSATE]
,[ALLOC_VOL_OIL]
,[ALLOC_WATER]
,[ALLOC_WATER_CUT]
,[ALLOC_WATER_GAS_RATIO]
,[AREA_NAME]
,[AREA_NAME_ABBREVIATION]
,[CHOKE_POSITION]
,[COUNTRY_CODE]
,[DATE]
,[DATE_STATUS]
,[DENSITY_OIL]
,[EVENT_CONDENSATE_GAS_RATIO]
,[EVENT_DATE]
,[EVENT_WATER_GAS_RATIO]
,[FUNCTIONAL_LOCATION]
,[HORIZON]
,[LAST_UPDATED_DATE]
,[OBJECT_CODE]
,[PERFORATION_ID]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_FIELD_NAME]
,[PRODUCTION_HOURS]
,[PRODUCTION_SYSTEM]
,[PRODUCTION_UNIT]
,[SHUT_OFF_REASON]
,[SHUT_OFF_REASON_COMMENT]
,[SHUTIN_HOURS]
,[THEOR_ASSOC_GAS]
,[THEOR_CO2]
,[THEOR_CONDENSATE]
,[THEOR_CONDENSATE_GAS_RATIO]
,[THEOR_FREE_GAS]
,[THEOR_GAS]
,[THEOR_GAS_OIL_RATIO]
,[THEOR_GASLIFT]
,[THEOR_GROSS]
,[THEOR_H2S]
,[THEOR_INJECTION_WATER]
,[THEOR_LPG]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_NET_CONDENSATE]
,[THEOR_NET_OIL]
,[THEOR_OIL]
,[THEOR_TOTAL_HYDROCARBON]
,[THEOR_TOTAL_PRODUCTION]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[THEOR_WATER]
,[THEOR_WATER_CUT]
,[THEOR_WATER_GAS_RATIO]
,[THEOR_WGR]
,[TIME]
,[TYPE]
,[UBHI]
,[UWI]
,[WELL_DOWNTIME_REASON]
,[WELL_FLUID_TYPE]
,[WELL_MAX_GAS_RATE]
,[WELL_MAX_GROSS_RATE]
,[WELL_MAX_H2O_INJ_RATE]
,[WELL_NAME]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,[WELL_TYPE]
,ALLOC_INJECTION_STEAM
,THEOR_INJECTION_STEAM
,ALLOC_INJECTION_AIR
,THEOR_INJECTION_AIR
FROM [OMVDDFLayer].[V_INT_HydrocarbonbyDate] where [TIME] = 'M'
GO
CREATE VIEW [dbo].[v_ro_subsurface_hierarchy] as
with Country as
(select *
from [OMVDDFLayer].[RO_CurrentHierarchies_SubsurfaceReservoirHierarchy]
where [TEMPLATE_NAME] = 'Country - SS'),
Field as
(select *
from [OMVDDFLayer ].[RO_CurrentHierarchies_SubsurfaceReservoirHierarchy]
where [TEMPLATE_NAME] = 'Field - SS'),
ProductionUnit as
(select *
from [OMVDDFLayer ].[RO_CurrentHierarchies_SubsurfaceReservoirHierarchy]
where [TEMPLATE_NAME] = 'Production Unit - SS'),
ProductionInterval as
(select *
from [OMVDDFLayer].[RO_CurrentHierarchies_SubsurfaceReservoirHierarchy]
where TEMPLATE_NAME = 'Production Interval - SS')
select a.NAME country,
-- a.TEMPLATE_NAME ,
b.NAME field,
-- b.TEMPLATE_NAME ,
c.NAME ProductionUnit,
-- c.TEMPLATE_NAME ,
d.NAME ProductionInterval--,
-- d.TEMPLATE_NAME
from Country a join Field b
on a.WEBID = b.PARENT_WEBID
join ProductionUnit c
on b.WEBID = c.PARENT_WEBID
left join ProductionInterval d
on c.WEBID = d.PARENT_WEBID
GO
-- Auto Generated (Do not modify) EB9D2FA53615F049DDBADC88BD973AD731A8260F04B98D6ED7F3EE7D47010171
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_INT_PT_PumpESPDetails] as
SELECT
[UNIQUE_KEY]
,[ESP_INTAKE_SERIAL_NO]
,[ESP_PUMP_RUNTIME]
,[ESP_WORKORDER_NO_INTERNAL]
,[ESP_GASSEP_PART_NO]
,[ESP_CABLE_AWG_SIZE]
,[ESP_CABLE_MLE_SERIE]
,[END_DATE]
,[ESP_BOLT_DISCH_DESC]
,[ESP_INTAKE_PART_NO]
,[ESP_PUMP_CUM_GROSS]
,[ESP_BOLT_DISCH_SERIAL_NO]
,[ESP_CONTROLLER_DESC]
,[ESP_DISCH_PART_NO]
,[ESP_PROTECTORS]
,[ESP_CABLE_MFR]
,[ESP_DOWN_SENS_PART_NO]
,[ESP_INTAKE_TYPE]
,[ESP_GASSEP_MFR]
,[ESP_PUMP_CUM_NET]
,[ESP_WORKORDER_NO_SAP]
,[ESP_PUMP_DEPTH_MD]
,[WO_AAP]
,[ESP_MOTORS]
,[ESP_DOWN_SENS_SERIAL_NO]
,[ESP_SECTION_TYPE]
,[COUNTRY_CODE]
,[ESP_GASSEP_SERIAL_NO]
,[ESP_INTAKE_SERIE]
,[ESP_CABLE_MLE_SERIAL_NO]
,[ESP_INTAKE_DESC]
,[ESP_DOWN_SENS_DESC]
,[ESP_LOWER_CONN_MFR]
,[ESP_BOLT_DISCH_PART_NO]
,[ESP_BOLT_DISCH_MFR]
,[ESP_CABLE_DESC]
,[ESP_CABLE_MLE_DESC]
,[ESP_CABLE_MLE_PART_NO]
,[ESP_DISCH_SERIAL_NO]
,[ESP_GASSEP_DESC]
,[ESP_LOWER_CONN_SERIAL_NO]
,[ESP_CABLE_TYPE]
,[ESP_DISCH_DESC]
,[ESP_PUMPS]
,[ESP_MOTOR_DERATING]
,[ESP_PUMP_DEPTH_TVD]
,[ESP_LOWER_CONN_DESC]
,[UWI]
,[ESP_CABLE_MLE_MFR]
,[ESP_CABLE_PART_NO]
,[ESP_DOWN_SENS_MFR]
,[UBHI]
,[START_DATE]
,[ESP_INSTALL_NUMBER]
,[ESP_DOWN_SENS_SERIE]
,[ESP_CABLE_SERIAL_NO]
,[LAST_UPDATED_DATE]
,[ESP_CABLE_LENGTH]
,[ESP_DISCH_MFR]
,[ESP_INTAKE_MFR]
,[ESP_MOTOR_TYPE]
,[ESP_LOWER_CONN_PART_NO]
,[ESP_CABLE_MLE_TYPE]
FROM [ATProductionTechnology].[PumpEspDetails]
union all
select
[UNIQUE_KEY]
,[ESP_INTAKE_SERIAL_NO]
,[ESP_PUMP_RUNTIME]
,[ESP_WORKORDER_NO_INTERNAL]
,[ESP_GASSEP_PART_NO]
,[ESP_CABLE_AWG_SIZE]
,[ESP_CABLE_MLE_SERIE]
,[END_DATE]
,[ESP_BOLT_DISCH_DESC]
,[ESP_INTAKE_PART_NO]
,[ESP_PUMP_CUM_GROSS]
,[ESP_BOLT_DISCH_SERIAL_NO]
,[ESP_CONTROLLER_DESC]
,[ESP_DISCH_PART_NO]
,[ESP_PROTECTORS]
,[ESP_CABLE_MFR]
,[ESP_DOWN_SENS_PART_NO]
,[ESP_INTAKE_TYPE]
,[ESP_GASSEP_MFR]
,[ESP_PUMP_CUM_NET]
,[ESP_WORKORDER_NO_SAP]
,[ESP_PUMP_DEPTH_MD]
,[WO_AAP]
,[ESP_MOTORS]
,[ESP_DOWN_SENS_SERIAL_NO]
,[ESP_SECTION_TYPE]
,[COUNTRY_CODE]
,[ESP_GASSEP_SERIAL_NO]
,[ESP_INTAKE_SERIE]
,[ESP_CABLE_MLE_SERIAL_NO]
,[ESP_INTAKE_DESC]
,[ESP_DOWN_SENS_DESC]
,[ESP_LOWER_CONN_MFR]
,[ESP_BOLT_DISCH_PART_NO]
,[ESP_BOLT_DISCH_MFR]
,[ESP_CABLE_DESC]
,[ESP_CABLE_MLE_DESC]
,[ESP_CABLE_MLE_PART_NO]
,[ESP_DISCH_SERIAL_NO]
,[ESP_GASSEP_DESC]
,[ESP_LOWER_CONN_SERIAL_NO]
,[ESP_CABLE_TYPE]
,[ESP_DISCH_DESC]
,[ESP_PUMPS]
,[ESP_MOTOR_DERATING]
,[ESP_PUMP_DEPTH_TVD]
,[ESP_LOWER_CONN_DESC]
,[UWI]
,[ESP_CABLE_MLE_MFR]
,[ESP_CABLE_PART_NO]
,[ESP_DOWN_SENS_MFR]
,[UBHI]
,[START_DATE]
,[ESP_INSTALL_NUMBER]
,[ESP_DOWN_SENS_SERIE]
,[ESP_CABLE_SERIAL_NO]
,[LAST_UPDATED_DATE]
,[ESP_CABLE_LENGTH]
,[ESP_DISCH_MFR]
,[ESP_INTAKE_MFR]
,[ESP_MOTOR_TYPE]
,[ESP_LOWER_CONN_PART_NO]
,[ESP_CABLE_MLE_TYPE]
FROM [ROProductionTechnology].[PumpEspDetails]
GO
create VIEW [OMVDDFLayer].[F_D_PT_SonologMeasurements] as
SELECT [UNIQUE_KEY]
,[SONOLOG_TUBING_HEAD_FLOW_PRESSURE]
,[SONOLOG_DYN_LIQUID_LEVEL]
,[HORIZON]
,[SONOLOG_MEASUREMENT_DATE]
,[COUNTRY_CODE]
,[WO_AAP]
,[SONOLOG_A_ANNULUS_PRESSURE]
,[SONOLOG_THEOR_GROSS]
,[LAST_UPDATED_DATE]
,[UBHI]
,[PRODUCTION_UNIT]
,[SONOLOG_STAT_LIQUID_LEVEL]
,[UWI]
,[SONOLOG_TUB_LIQUID_LEVEL]
FROM [ATProductionTechnology].[SonologMeasurements]
GO
-- Auto Generated (Do not modify) 6307F6F18EA1D5BB364AC945159AE162FCE864B3470C820CAED618C10AF6716D
CREATE VIEW [OMVDDFLayer].[V_INT_WellPerformance] as
SELECT [UNIQUE_KEY]
,[WT_LIFTGAS_RATE]
,[WT_NET_CONDENSATE_MASS_RATE]
,[WT_NET_GAS_RATE]
,[WT_NET_OIL_MASS_RATE]
,[WT_TOTAL_GAS_RATE]
,[WT_TOTAL_GROSS_RATE]
,[WT_TOTAL_WATER_RATE]
,[WT_WATERCUT]
,[COUNTRY_CODE]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,[UBHI]
,[UWI]
,[WELLTEST_STATUS]
,cast([WT_DATE] AS DATETIME) as [WT_DATE]
,LEAD(cast([WT_DATE] AS DATETIME), 1) OVER(partition by UBHI
ORDER BY UBHI,cast([WT_DATE] AS DATETIME) ASC) AS [WT_END_TEST]
FROM [ProductionOptimization].[WellPerformance]
GO
-- Auto Generated (Do not modify) 57B1ED8E0F71195EBBBF35A3D11640C034B3656239192E06EFB75FECF4BB621B
CREATE VIEW [OMVDDFLayer].[v_RO_well_facility]
as
SELECT
DISTINCT W.UWI, W.NAME WELL_NAME, W.TEMPLATE_NAME,W.COUNTRY, F.NAME FACILITY_NAME, F.TEMPLATE_NAME FACILITY_TEMPLATE_NAME
from [ROCurrentHierarchies].[ProductionSystemNetworks] W, [ROCurrentHierarchies].[ProductionSystemNetworks] F
WHERE W.PARENT_WEBID = F.WEBID
AND W.UWI IS NOT NULL
AND W.UWI <> '0'
GO
CREATE VIEW [OMVDDFLayer].[F_D_PO_ProductionReportingMonthlyComments] as
select [UNIQUE_KEY]
,[DIFFERENCE_PRODFYE]
,[FYE_OF_MONTH]
,[STATE_CODE]
,[ASSET]
,[COMMENT]
,[COMMENT_ID]
,[COUNTRY_CODE]
,[CREATED_ON]
,[IS_ACTIVE]
,[LAST_UPDATED_DATE]
,[MONTHLYPRODREPORT_COMMENT_ID]
,[PUBLISHING_DATE]
FROM [ProductionOptimization].[ProductionReportingMonthlyComments]
union all
select [UNIQUE_KEY]
,[DIFFERENCE_PRODFYE]
,[FYE_OF_MONTH]
,[STATE_CODE]
,[ASSET]
,[COMMENT]
,[COMMENT_ID]
,[COUNTRY_CODE]
,[CREATED_ON]
,[IS_ACTIVE]
,[LAST_UPDATED_DATE]
,[MONTHLYPRODREPORT_COMMENT_ID]
,[PUBLISHING_DATE]
FROM [ROProductionOptimization].[ProductionReportingMonthlyComments]
GO
-- Auto Generated (Do not modify) 2F8F57BD4E10F594915137C208F0CD6889C76BD69457A0A826091C87D333C9D8
CREATE VIEW [OMVDDFLayer].[D_Well_Additional_Info] as
SELECT distinct
FIRST_VALUE([CHOKE_POSITION]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS [CHOKE_POSITION]
,[COUNTRY_CODE]
,FIRST_VALUE([DATE]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS [DATE]
,FIRST_VALUE([DATE_STATUS]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[DATE_STATUS]
,FIRST_VALUE([DENSITY_OIL]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[DENSITY_OIL]
,FIRST_VALUE([PRODUCTION_FIELD_CODE]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[PRODUCTION_FIELD_CODE]
,FIRST_VALUE([PRODUCTION_FIELD_NAME]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[PRODUCTION_FIELD_NAME]
,FIRST_VALUE([PRODUCTION_HOURS]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[PRODUCTION_HOURS]
,FIRST_VALUE([PRODUCTION_SYSTEM]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[PRODUCTION_SYSTEM]
,FIRST_VALUE([PRODUCTION_UNIT]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[PRODUCTION_UNIT]
,[UWI]
,FIRST_VALUE([WELL_FLUID_TYPE]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[WELL_FLUID_TYPE]
,FIRST_VALUE([WELL_NAME]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[WELL_NAME]
,FIRST_VALUE([WELL_PRODUCTION_METHOD]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[WELL_PRODUCTION_METHOD]
,FIRST_VALUE([WELL_PRODUCTION_METHOD_DETAIL]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[WELL_PRODUCTION_METHOD_DETAIL]
,FIRST_VALUE([WELL_TYPE]) OVER (
PARTITION BY [UWI] ORDER BY [DATE] desc
) AS[WELL_TYPE]
FROM [OMVDDFLayer].[F_D_PO_HydrocarbonByDate] where [DATE] =cast(DATEADD (month, DATEDIFF(month, -1, getdate()) - 2, 0) as DATE)
GO
-- Auto Generated (Do not modify) CCED06C858AF2E68CA3AC0310C638086622EF3D061D42ECD2DFA77EFCFB35BA8
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[DO_Well_Facility] as
SELECT [UWI]
,COUNTRY
,[FACILITY_NAME]
FROM [OMVDDFLayer].[v_RO_well_facility]
UNION ALL
SELECT [UWI]
,COUNTRY
,[FACILITY_NAME]
FROM [OMVDDFLayer].[v_INT_well_facility]
GO
-- Auto Generated (Do not modify) 6182A3DF8E2F35667B09B71667C26D970DE1809BC1B4D88D5F033B9B634CAE28
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_RO_HydrocarbonByDate] as
SELECT UNIQUE_KEY
,[ALLOC_ASSOC_GAS]
,[ALLOC_CONDENSATE]
,[ALLOC_CONDENSATE_GAS_RATIO]
,[ALLOC_FREE_GAS]
,[ALLOC_GAS]
,[ALLOC_GASLIFT]
,[ALLOC_GAS_OIL_RATIO]
,[ALLOC_GROSS]
,[ALLOC_INJECTION_WATER]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_MASS_OIL]
,[ALLOC_NET_OIL]
,[ALLOC_OIL]
,[ALLOC_VOL_CONDENSATE]
,[ALLOC_VOL_OIL]
,[ALLOC_WATER]
,[ALLOC_WATER_CUT]
,[ALLOC_WATER_GAS_RATIO]
,[CHOKE_POSITION]
,[DENSITY_OIL]
,[PRODUCTION_HOURS]
,[SHUTIN_HOURS]
,[THEOR_ASSOC_GAS]
,[THEOR_CONDENSATE]
,[THEOR_CONDENSATE_GAS_RATIO]
,[THEOR_FREE_GAS]
,[THEOR_GAS]
,[THEOR_GAS_OIL_RATIO]
,[THEOR_GROSS]
,[THEOR_INJECTION_WATER]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_NET_CONDENSATE]
,[THEOR_NET_OIL]
,[THEOR_OIL]
,[THEOR_TOTAL_HYDROCARBON]
,[THEOR_TOTAL_PRODUCTION]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[THEOR_WATER]
,[THEOR_WATER_CUT]
,[THEOR_WATER_GAS_RATIO]
,[COUNTRY_CODE]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,cast([DATE] AS DATE) as [DATE]
,[UBHI]
,[UWI]
,[WELL_FLUID_TYPE]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,[WELL_TYPE]
,[ALLOC_INJECTION_AIR]
,[THEOR_INJECTION_AIR]
,ALLOC_INJECTION_STEAM
,THEOR_INJECTION_STEAM
FROM [ROProductionOptimization].[HydrocarbonByDate]
GO
-- Auto Generated (Do not modify) 93C295E6498A573F89D99E6AE1B0F38FEE34AF4F374D97A541FC1000DC6A3057
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_AT_WellCapacity] as
SELECT UNIQUE_KEY
,[FC_WELL_CAP_ASSOC_GAS]
,[FC_WELL_CAP_CONDENSATE]
,[FC_WELL_CAP_INJECTION_WATER]
,[FC_WELL_CAP_NON_ASSOC_GAS]
,[FC_WELL_CAP_OIL]
,[FC_WELL_CAP_TOTAL_LIQUID]
,[FC_WELL_CAP_WATER]
,[FC_WELL_MPC_ASSOC_GAS]
,[FC_WELL_MPC_CONDENSATE]
,[FC_WELL_MPC_INJECTION_WATER]
,[FC_WELL_MPC_NON_ASSOC_GAS]
,[FC_WELL_MPC_OIL]
,[FC_WELL_MPC_TOTAL_LIQUID]
,[FC_WELL_MPC_WATER]
,[COUNTRY_CODE]
,cast([DATE] AS DATE) as [DATE]
,[UWI]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
FROM [ProductionOptimization].[WellCapacity]
GO
-- Auto Generated (Do not modify) 20DD6543146C8327EC42E79D26460B1ED79473FB05CD02781015597663D49089
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_INT_PO_ProductionbyStream] as
SELECT [UNIQUE_KEY]
,[STREAM_TYPE_CI]
--,[STREAM_CATEGORY]
,[OBSERVATIONS]
,[FACILITY_UID_FROM]
,[STREAM_SUBGROUP]
,[FACILITY_UID_TO]
,[LAST_UPDATED_DATE]
,[EQUIPMENT_DESCRIPTION]
,[STREAM_PHASE]
,[DATE]
,[STREAM_GROUP]
,[EQUIPMENT_THERMIC_CAPACITY]
,[ALLOC_VOL]
,[STREAM_CODE]
,[THEOR_VOL]
--,[STREAM_SUBCATEGORY]
,[COUNTRY_CODE]
,[STREAM_NAME]
FROM [ROProductionOptimization].[ProductionbyStream]
GO
CREATE VIEW [OMVDDFLayer].[F_D_PT_BoreholePerforations] as
SELECT [UNIQUE_KEY]
,[BOTTOM_PERFORATION_MD]
,[TOP_PERFORATION_MD]
,[COUNTRY_CODE]
,[END_DATE]
,[HORIZON]
,[PERFORATION_ID]
,[PERFORATION_TYPE]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_UNIT]
,[START_DATE]
,[UBHI]
,[UWI]
,[PERFORATION_STATUS]
,[BOTTOM_PERFORATION_TVD]
,[MID_PERFORATION_MD]
,[TOP_PERFORATION_TVD]
,[MID_PERFORATION_TVD]
FROM [ATProductionTechnology].[BoreholePerforations]
GO
CREATE VIEW [OMVDDFLayer].[D_ConversionFactors] as
SELECT [UNIQUE_KEY]
,[OBJECT_CODE]
,[END_DATE]
,[START_DATE]
,[VALUE]
,[IS_ACTIVE]
,[OBJECT_TYPE]
,[PRECISION]
,[LAST_UPDATED_DATE]
,[UNIT_OF_MEASURE_TO_CODE]
,[FLUID_TYPE]
,[UNIT_OF_MEASURE_FROM_DESC]
,[UNIT_OF_MEASURE_TO_DESC]
,[OBJECT_NAME]
,[ADD_NUMBER]
,[UNIT_OF_MEASURE_FROM_CODE]
,[COUNTRY_CODE]
FROM [ROProductionOptimization].[ConversionFactors]
UNION ALL
SELECT [UNIQUE_KEY]
,[OBJECT_CODE]
,[END_DATE]
,[START_DATE]
,[VALUE]
,[IS_ACTIVE]
,[OBJECT_TYPE]
,[PRECISION]
,[LAST_UPDATED_DATE]
,[UNIT_OF_MEASURE_TO_CODE]
,[FLUID_TYPE]
,[UNIT_OF_MEASURE_FROM_DESC]
,[UNIT_OF_MEASURE_TO_DESC]
,[OBJECT_NAME]
,[ADD_NUMBER]
,[UNIT_OF_MEASURE_FROM_CODE]
,[COUNTRY_CODE]
FROM [ProductionOptimization].[ConversionFactors]
GO
-- Auto Generated (Do not modify) A390F660EF28F846197E1B6AAB8ACDC93791D4D056F283B07E91E1AFC400F06D
CREATE VIEW [OMVDDFLayer].[V_INT_PT_WellEvents] as
SELECT [UNIQUE_KEY]
,[EVENT_OUI]
,[ASSET_NAME]
,[UNIQUE_ID]
,[COUNTRY_CODE]
,[EVENT_TYPE_DESCRIPTION]
,[EVENT_ACTUAL_START_DATE]
,[EVENT_PLANNED_END_DATE]
,[EVENT_DESCRIPTION]
,[UWI]
,[EVENT_PLANNED_START_DATE]
,[EVENT_TYPE_CODE]
,[LAST_UPDATED_DATE]
,[EVENT_ACTUAL_END_DATE]
FROM [ROProductionTechnology].[WellEvents]
GO
CREATE VIEW [OMVDDFLayer].[DO_SubsurfaceGeologicalDetails] as
SELECT [UNIQUE_KEY]
,[ELEMENT_ID]
,[WEBID]
,[UWI]
,[UBHI]
,[ELEMENT_ID_TYPE]
,[LAST_UPDATED_DATE]
,[PI_STATUS]
,[GEOLOGICAL_UNIT]
,[GEOLOGICAL_AGE]
,[COUNTRY]
,[RESERVOIR_FORMATION_CODE]
,[RESERVOIR_FORMATION]
FROM [ROCurrentHierarchies].[FlatSubsurfaceGeologicalDetails]
GO
-- Auto Generated (Do not modify) 1B6F72C417A33549D5E07A7408DBC7CE4C55B1DAC9028FDF64B036A6F1586F05
CREATE VIEW [OMVDDFLayer].[V_RO_Well_Hierarchy] as
with
legalentity as(
select NAME, WEBID, PARENT_WEBID from [ROCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Legal Entity - ORG'),
contractarea as (
select NAME, WEBID, PARENT_WEBID from [ROCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Contract Area - ORG'),
asset as (
select NAME, WEBID, PARENT_WEBID from [ROCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Well - ORG'),
subasset as (
select NAME, WEBID, PARENT_WEBID from [ROCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'SubAsset Group - ORG'),
wells as (
select NAME, WEBID, PARENT_WEBID, UWI,HUB, COUNTRY from [ROCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Well - ORG')
select w.HUB, w.COUNTRY,l.NAME as LEGAL_ENTITY, ca.NAME as CONTRACT_AREA, a.NAME as ASSET, sa.NAME as SUB_ASSET_GROUP, w.UWI, w.NAME as WELL_NAME
from legalentity l, contractarea ca, asset a, subasset sa, wells w
where l.WEBID = ca.PARENT_WEBID and ca.WEBID = a.PARENT_WEBID and a.WEBID = sa.PARENT_WEBID and (sa.WEBID = w.PARENT_WEBID or a.WEBID = w.PARENT_WEBID)
and COUNTRY = 'Romania'
GO
CREATE VIEW [OMVDDFLayer].[V_AT_HydrocarbonbyDate] as
SELECT [UNIQUE_KEY]
,[ALLOC_ASSOC_GAS]
,[ALLOC_CO2]
,[ALLOC_CONDENSATE]
,[ALLOC_FREE_GAS]
,[ALLOC_GAS_OIL_RATIO]
,[ALLOC_GROSS]
,[ALLOC_H2S]
,[ALLOC_INJECTION_WATER]
,[ALLOC_LPG]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_MASS_OIL]
,[ALLOC_OIL]
,[ALLOC_VOL_OIL]
,[ALLOC_WATER]
,[ALLOC_WATER_CUT]
,[CHOKE_POSITION]
,[DENSITY_OIL]*1000 [DENSITY_OIL]--required by Bernd to have the same unit as RO
,[PERFORATION_ID]
,[PRODUCTION_HOURS]
,[SHUTIN_HOURS]
,[THEOR_ASSOC_GAS]
,[THEOR_CO2]
,[THEOR_CONDENSATE]
,[THEOR_FREE_GAS]
,[THEOR_GASLIFT]
,[THEOR_GAS_OIL_RATIO]
,[THEOR_GROSS]
,[THEOR_H2S]
,[THEOR_INJECTION_WATER]
,[THEOR_LPG]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_OIL]
,[THEOR_VOL_OIL]
,[THEOR_WATER]
,[THEOR_WATER_CUT]
,[THEOR_WGR]
,[WELL_MAX_GAS_RATE]
,[WELL_MAX_GROSS_RATE]
,[WELL_MAX_H2O_INJ_RATE]
,[AREA_NAME]
,[AREA_NAME_ABBREVIATION]
,[COUNTRY_CODE]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,cast([DATE] AS DATE) as [DATE]
,[HORIZON]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_SYSTEM]
,[PRODUCTION_UNIT]
,[SHUT_OFF_REASON]
,[SHUT_OFF_REASON_COMMENT]
,[TIME]
,[TYPE]
,[UWI]
,[WELL_DOWNTIME_REASON]
,[WELL_FLUID_TYPE]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,[EVENT_DATE]
,[OBJECT_CODE]
,[WELL_NAME]
,[FUNCTIONAL_LOCATION]
,[THEOR_CONDENSATE_GAS_RATIO]
,[ALLOC_WATER_GAS_RATIO]
,[EVENT_CONDENSATE_GAS_RATIO]
,[ALLOC_GASLIFT]
,[THEOR_WATER_GAS_RATIO]
,[ALLOC_GAS]
,[THEOR_GAS]
,[THEOR_NET_OIL]
,[PRODUCTION_FIELD_NAME]
,[WELL_TYPE]
,[ALLOC_VOL_CONDENSATE]
,[ACTIVE_WELL_STATUS]
,[THEOR_TOTAL_PRODUCTION]
,[UBHI]
,[THEOR_NET_CONDENSATE]
,cast([DATE_STATUS] AS DATE) as [DATE_STATUS]
,[ALLOC_CONDENSATE_GAS_RATIO]
,[EVENT_WATER_GAS_RATIO]
,[ALLOC_TOTAL_HYDROCARBON]
,[THEOR_TOTAL_HYDROCARBON]
,[THEOR_VOL_CONDENSATE]
,[ALLOC_NET_OIL]
,ALLOC_INJECTION_STEAM
,THEOR_INJECTION_STEAM
FROM [ProductionOptimization].[Hydrocarbonbydate]
where time = 'D'
and COUNTRY_CODE = 'AT'
GO
CREATE VIEW [OMVDDFLayer].[ReservoirEngineering_ReservoirData] as
SELECT [UNIQUE_KEY]
,[RES_ACT_REC_FACTOR_ASSOC_GAS]
,[RES_ACT_REC_FACTOR_OIL]
,[RES_EST_REC_FACTOR_OIL]
,[RES_GAS_OIL_CONTACT_TVD_SS]
,[RES_GAS_WATER_CONTACT_TVD_SS]
,[RES_INIT_ASSOC_GAS_IN_PLACE]
,[RES_INIT_NONASSOC_GAS_IN_PLACE]
,[RES_INIT_OIL_IN_PLACE]
,[RES_INIT_PRESSURE]
,[RES_OIL_WATER_CONTACT_TVD_SS]
,[RES_REFERENCE_DEPTH_TVD_SS]
,[COUNTRY_CODE]
,[HORIZON_NAME]
,[PRIMARY_RECOVERY]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_FIELD_NAME]
,[PRODUCTION_UNIT_NAME]
,[PRODUCTION_UNIT_UID]
,[RES_AREA]
,[RES_PROD_START_DATE]
,[SECONDARY_RECOVERY]
FROM [ATReservoirEngineering].[ReservoirData]
GO
-- Auto Generated (Do not modify) 57B1ED8E0F71195EBBBF35A3D11640C034B3656239192E06EFB75FECF4BB621B
CREATE VIEW [OMVDDFLayer].[v_INT_well_facility]
AS
SELECT
DISTINCT W.UWI, W.NAME WELL_NAME, W.TEMPLATE_NAME, W.COUNTRY,F.NAME FACILITY_NAME, F.TEMPLATE_NAME FACILITY_TEMPLATE_NAME
FROM [ATCurrentHierarchies].[ProductionSystemNetworks] W, [ATCurrentHierarchies].[ProductionSystemNetworks] F
WHERE W.PARENT_WEBID = F.WEBID
AND W.UWI IS NOT NULL
AND W.UWI <> '0'
GO
-- Auto Generated (Do not modify) 93C295E6498A573F89D99E6AE1B0F38FEE34AF4F374D97A541FC1000DC6A3057
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[F_D_PO_WellCapacity] as
SELECT [UNIQUE_KEY]
,[FC_WELL_CAP_ASSOC_GAS]
,[FC_WELL_CAP_CONDENSATE]
,[FC_WELL_CAP_INJECTION_WATER]
,[FC_WELL_CAP_NON_ASSOC_GAS]
,[FC_WELL_CAP_OIL]
,[FC_WELL_CAP_TOTAL_LIQUID]
,[FC_WELL_CAP_WATER]
,[FC_WELL_MPC_ASSOC_GAS]
,[FC_WELL_MPC_CONDENSATE]
,[FC_WELL_MPC_INJECTION_WATER]
,[FC_WELL_MPC_NON_ASSOC_GAS]
,[FC_WELL_MPC_OIL]
,[FC_WELL_MPC_TOTAL_LIQUID]
,[FC_WELL_MPC_WATER]
,[COUNTRY_CODE]
,[DATE]
,[UWI]
,[LAST_UPDATED_DATE]
FROM [OMVDDFLayer].[V_AT_WellCapacity]
GO
CREATE VIEW [OMVDDFLayer].[V_split_RO_OrganizationalHierarchy] as
with splithierarchy as(
SELECT [COUNTRY]
,[HUB]
,[UWI]
,[WELL_NAME]
,[PATH]
,[1]
,[2]
,[3]
,[4]
,[5]
,[6]
,[7]
,[8]
FROM (
SELECT
[COUNTRY]
,[HUB]
,[UWI]
,[NAME] as [WELL_NAME]
,[PATH]
,value level_value
,ordinal ordinal_label
FROM [ROCurrentHierarchies].[OrganizationalHierarchy] CROSS APPLY STRING_SPLIT(path, '\' , 1 )
where [TEMPLATE_NAME] = 'Well - ORG' and country = 'Romania' )AS SourceTable
PIVOT
(
max(level_value)
FOR ordinal_label IN ([1], [2], [3], [4], [5], [6], [7],[8])
) AS PivotTable)
select [UWI]
,[1] HIERARCHY_TYPE
,[2] HUB
,[3] COUNTRY
,[4] LEGAL_ENTITY
,[5] CONTRACT_AREA
,[6] ASSET
,[7] SUB_ASSET_GROUP
,[8] WELL_NAME
from splithierarchy
GO
-- Auto Generated (Do not modify) E255F0C72303558C199DE7EAA6EAF079DD0B932502DA7DA2F7A11B1DC15E4FF8
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_INT_ProductionSystemNetworks] as
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,[NAME]
,[PARENT_WEBID]
,[PATH]
,[PRODUCTION_SYSTEM]
,[TEMPLATE_NAME]
,[UWI]
,[WEBID]
,[LAST_UPDATED_DATE]
,[STREAM_FLUID]
FROM [ROCurrentHierarchies].[ProductionSystemNetworks]
UNION
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,[NAME]
,[PARENT_WEBID]
,[PATH]
,[PRODUCTION_SYSTEM]
,[TEMPLATE_NAME]
,[UWI]
,[WEBID]
,[LAST_UPDATED_DATE]
,[STREAM_FLUID]
FROM [ATCurrentHierarchies].[ProductionSystemNetworks]
UNION
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,[NAME]
,[PARENT_WEBID]
,[PATH]
,[PRODUCTION_SYSTEM]
,[TEMPLATE_NAME]
,[UWI]
,[WEBID]
,[LAST_UPDATED_DATE]
,[STREAM_FLUID]
FROM [AECurrentHierarchies].[ProductionSystemNetworks]
UNION
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,[NAME]
,[PARENT_WEBID]
,[PATH]
,[PRODUCTION_SYSTEM]
,[TEMPLATE_NAME]
,[UWI]
,[WEBID]
,[LAST_UPDATED_DATE]
,[STREAM_FLUID]
FROM [TNCurrentHierarchies].[ProductionSystemNetworks]
GO
-- Auto Generated (Do not modify) CF55F1B068F02CF235F16E011BBB470572E3AC1C3D04111C5BBB33235ED72E16
CREATE VIEW [OMVDDFLayer].[V_RO_BoreholeStatus] as
SELECT [UNIQUE_KEY]
,[BOREHOLE_STATUS_OPERATIONAL]
,[COUNTRY_CODE]
,[FLAG_PRODUCTION]
,cast([LAST_UPDATED_DATE] AS DATETIME)[LAST_UPDATED_DATE]
,cast([START_DATE] AS DATETIME)[START_DATE]
,LEAD(cast([START_DATE] AS DATETIME), 1) OVER(partition by UBHI
ORDER BY UBHI,cast([START_DATE] AS DATETIME) ) AS [END_DATE]
,[UBHI]
,[UWI]
,[WELL_NAME]
FROM [ROWellHeader].[BoreholeStatus]
GO
CREATE VIEW [OMVDDFLayer].[DO_PumpRodDetails] as
SELECT [UNIQUE_KEY]
,[ROD_DIAMETER]
,[ROD_LENGTH]
,[ROD_LOAD_CYCLES_NO]
,[COUNTRY_CODE]
,[LAST_UPDATED_DATE]
,[ROD_DIAMETER_IMP]
,[ROD_GUIDE_TYPE]
,[START_DATE]
,[UBHI]
,[UWI]
FROM [ATProductionTechnology].[PumpRodDetails]
GO
-- Auto Generated (Do not modify) 52CD693195254A6895423402CDA85E8588CCFF7CA0FDF981CB36C4CC8B944805
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_INT_PT_PumpSrpDetails] as
select [UNIQUE_KEY]
,[DOG_LEG_SEVERITY]
,[SRP_MOTOR_POWER]
,[SRP_MOTOR_RPM]
,[SRP_MOTOR_SHEAVE]
,[SRP_PUMP_DEPTH]
,[SRP_PUMP_DEPTH_TVD]
,[SRP_ROD_API_NO]
,[SRP_RUNTIME_CURRENT]
,[SRP_RUNTIME_LAST]
,[SRP_SINKERBAR_LENGTH]
,[SRP_STROKE_LENGTH]
,[SRP_STROKE_MINUTES]
,[SRP_UNIT_ALLOW_GEARBOX_TORQUE]
,[SRP_UNIT_ALLOW_POL_ROD_LOAD]
,[SRP_UNIT_GEARBOX_RATIO]
,[SRP_UNIT_GEARBOX_SHEAVE]
,[COUNTRY_CODE]
,[END_DATE]
,[LAST_UPDATED_DATE]
,[SRP_BEAMGAS_COMPR_DEINSTALL_DATE]
,[SRP_BEAMGAS_COMPR_INSTALL_DATE]
,[SRP_BEAMGAS_COMPR_TYPE]
,[SRP_MOTOR_TYPE]
,[SRP_PUMP_API_NO]
,[SRP_PUMP_BARREL_MFR]
,[SRP_PUMP_MFR]
,[SRP_PUMP_PLUNGER_MFR]
,[SRP_PUMP_SERIAL_NO]
,[SRP_SINKERBAR_DIAMETER]
,[SRP_UNIT_GEARBOX_MFR]
,[SRP_UNIT_INV_NO]
,[SRP_UNIT_MFR]
,[START_DATE]
,[UBHI]
,[UWI]
,[WELL_COMPLETION_CLOSED_INSTALL_FLAG]
FROM [ATProductionTechnology].[PumpSrpDetails]
GO
-- Auto Generated (Do not modify) EAF2C1AC145463534678E77DEEBB267FB17C9A5BCCE920087EB5C92892377614
CREATE VIEW [OMVDDFLayer].[V_RO_UBHI_FROM_HYDROCARBON] as
SELECT [UNIQUE_KEY]
,[COUNTRY_CODE]
,[LAST_UPDATED_DATE]
,[DATE]
,[UBHI]
,[UWI]
,[WELL_FLUID_TYPE]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,[WELL_TYPE]
FROM [OMVDDFLayer].[V_RO_HydrocarbonByDate] v
where [DATE] = dateadd(day,-2,cast(SYSDATETIME() as DATE))
GO
-- Auto Generated (Do not modify) 790C16148251D85ECC7B305C1F0CBB71FC8A541B092E583AAD7A0BA959028DAA
CREATE VIEW [OMVDDFLayer].[F_D_PO_Deferment] as
SELECT UNIQUE_KEY
,[THEOR_ASSOC_GAS]
,[THEOR_CONDENSATE]
,[THEOR_DEFFERED_GAS]
,[THEOR_FREE_GAS]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_OIL]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[CLIENT_ID]
,[COMMENT]
,[COUNTRY_CODE]
,[DATE]
,[DEFERMENT_CHOKE]
,[DEFERMENT_IAP]
,[DEFERMENT_ID]
,[DEFERMENT_NAME]
,[DEVIATION_EVENT]
,[DEVIATION_EVENT_EXPLANATION]
,[END_DATE]
,[EVENT_TYPE]
,[LAST_UPDATED_DATE]
,[OMV_SHARE_TYPE]
,[SHUT_OFF_REASON]
,[SHUT_OFF_REASON_NATIVE]
,[START_DATE]
,[TIME]
,[TYPE]
,[UWI]
,[ADD_COMMENT]
,[SHUT_OFF_REASON_PLS]
,[DEFERMENT_ROOT_CAUSE]
,[DEFERMENT_CAUSE]
,[SHUT_OFF_REASON_PLS_NATIVE]
,[DEFERMENT_SUB_CHOKE_EQU]
,[VERSION_ID]
,[PRODUCTION_SYSTEM]
,[DEFERMENT_SUB_CHOKE]
,[AREA_NAME_ABBREVIATION]
,[PERFORATION_ID]
FROM [OMVDDFLayer].[V_RO_Deferment]
union all
SELECT UNIQUE_KEY
,[THEOR_ASSOC_GAS]
,[THEOR_CONDENSATE]
,[THEOR_DEFFERED_GAS]
,[THEOR_FREE_GAS]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_OIL]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[CLIENT_ID]
,[COMMENT]
,[COUNTRY_CODE]
,[DATE]
,[DEFERMENT_CHOKE]
,[DEFERMENT_IAP]
,[DEFERMENT_ID]
,[DEFERMENT_NAME]
,[DEVIATION_EVENT]
,[DEVIATION_EVENT_EXPLANATION]
,[END_DATE]
,[EVENT_TYPE]
,[LAST_UPDATED_DATE]
,[OMV_SHARE_TYPE]
,[SHUT_OFF_REASON]
,[SHUT_OFF_REASON_NATIVE]
,[START_DATE]
,[TIME]
,[TYPE]
,[UWI]
,[ADD_COMMENT]
,[SHUT_OFF_REASON_PLS]
,[DEFERMENT_ROOT_CAUSE]
,[DEFERMENT_CAUSE]
,[SHUT_OFF_REASON_PLS_NATIVE]
,[DEFERMENT_SUB_CHOKE_EQU]
,[VERSION_ID]
,[PRODUCTION_SYSTEM]
,[DEFERMENT_SUB_CHOKE]
,[AREA_NAME_ABBREVIATION]
,[PERFORATION_ID]
FROM [OMVDDFLayer].[V_AT_Deferment]
GO
CREATE VIEW [OMVDDFLayer].[DO_WorkoverOpportunities] as
SELECT [UNIQUE_KEY]
,[ECONOMICAL_ANALYSIS_COMMENT]
,[RMHQ_PROJECT_DURATION_DAYS]
,[RMA_ESTIMATED_OIL_RATE]
,[SAND_DESIGN_COMMENTS]
,[PTA_INFRASTRUCTURE_CHECK]
,[PTA_ESTIMATED_COST]
,[WO_JOB_REASON]
,[PTA_HEAVY_WORKOVER_CHECK]
,[RMA_WELL_TYPE_AFER_WO]
,[PLANNED_TOTAL_WO_JOB_HOURS]
,[RMHQ_PROJECT_DURATION_HOURS]
,[FLAG_SAND_CONTROL_DESIGN]
,[WOD_SERVICES_CREW]
,[NET_PRESENT_VALUE_10]
,[RMA_PROPOSED_MONTHLY_DECLINE]
,[PTHQ_ESTIMATED_WO_STARTDATE]
,[PLANNED_MATERIALS]
,[RMHQ_ESTIMATED_GAS_RATE]
,[COMPLETION_DESIGN_COMMENTS]
,[NET_PRESENT_VALUE_13]
,[WELL_FAILURE_DATE]
,[PRIMARY_OPERATION]
,[FINAL_TECHNICAL_RATE_SUCCESS]
,[BUDGET_CODE]
,[APPROVAL_DATE]
,[RMHQ_ESTIMATED_CONDENSATE_RATE]
,[RMHQ_ESTIMATED_DELTA_PRODUCTION_RATE]
,[TERTIARY_OPERATION]
,[PTA_RENTEXTRALAND_CHECK]
,[FLAG_STIMULATION_DESIGN]
,[PROPOSED_LAYERS]
,[FLAG_ARTIFICIAL_DESIGN]
,[RMA_PROJECT_DURATION]
,[TE_VALIDATION_HQT_COMMENTS]
,[PTHQ_APPROVAL_MEETING_DATE]
,[RMHQ_INJECTION_PRESSURE]
,[WO_JOB_CODE]
,[INVESTMENT_RETURN_RATE]
,[SECOND_ADDITIONAL_OPERATION]
,[SHORT_DESCRIPTION]
,[RMA_INJECTION_PRESSURE]
,[DISCOUNT_PROFITABILITY_INDEX_13]
,[RMA_INJECTION_RATE]
,[WOD_INTERNAL_SERVICES]
,[DISCOUNT_PROFITABILITY_INDEX_10]
,[PTA_PROJECT_DURATION]
,[COUNTRY_CODE]
,[OPPORTUNITY_DATE]
,[RMA_GEOLOGICAL_RATE_OF_SUCCESS]
,[FLAG_SEND_TO_SAP]
,[RMHQ_ESTIMATED_OIL_RATE]
,[THIRD_PARTIES_SERVICES]
,[RMA_OIL_QUALITY]
,[FIRST_ADDITIONAL_OPERATION]
,[FLAG_COMPLETION_DESIGN]
,[TE_VALIDATION_HQE_COMMENTS]
,[RMHQ_GEOLOGICAL_RATE_OF_SUCCESS]
,[TOTAL_INVESTMENT]
,[RMA_COMMENTS]
,[WELL_TYPE]
,[RMHQ_COMMENTS_FORECAST]
,[RMHQ_COMMENTS]
,[RMHQ_PROJECT_DURATION_MONTHS]
,[RMA_ESTIMATED_GAS_RATE]
,[COMMERCIAL_FIELD]
,[PLANNED_OPERATIONS_HOURS]
,[RMHQ_WELL_TYPE_AFER_WO]
,[STIMULATION_DESIGN_COMMENTS]
,[WELL_LAYERS]
,[PLANNED_RIG_DOWN_MOVE_UP_HOURS]
,[PTA_COMMENTS]
,[RMHQ_INJECTION_RATE]
,[HQPT_COMMENTS]
,[UWI]
,[PTA_TECHNICAL_RATE_OF_SUCCESS]
,[OPPORTUNITY_STATUS]
,[THIRD_ADDITIONAL_OPERATION]
,[RMHQ_OIL_QUALITY]
,[LAST_UPDATED_DATE]
,[SECONDARY_OPERATION]
,[RMHQ_PROJECT_DELAY_REASON]
,[PAYOUT_TIME_AT_NVP_10]
,[PROGRAM_PROPOSED]
,[OPPORTUNITY_NUMBER]
,[BUDGET_NAME]
,[RMHQ_PROPOSED_MONTHLY_DECLINE]
,[PTHQ_WORB_REQUIRED_CHECK]
,[PLANNED_WO_JOB_COST]
,[ARTIFICIAL_DESIGN_COMMENTS]
,[WELL_STATUS]
FROM [ROProductionTechnology].[WorkoverOpportunities]
GO
-- Auto Generated (Do not modify) 99D90B1A7C4553AEE3CA3A04A5D8E448932E217D12DC25B1D82A4EC6E96CB537
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[F_D_PO_CalculatedAttributes] as
SELECT UNIQUE_KEY
,[DAILY_ACTUAL_CGR]
,[DAILY_ACTUAL_WC]
,[DAILY_ACTUAL_WOR]
,[DATE]
,[UWI]
FROM [OMVDDFLayer].V_INT_PO_CalculatedAttributes
GO
-- Auto Generated (Do not modify) 1B6F72C417A33549D5E07A7408DBC7CE4C55B1DAC9028FDF64B036A6F1586F05
CREATE VIEW [OMVDDFLayer].[V_INT_Well_Hierarchy] as
SELECT [HUB]
,[COUNTRY]
,[LEGAL_ENTITY]
,[CONTRACT_AREA]
,[ASSET]
,[SUB_ASSET_GROUP]
,[UWI]
,[WELL_NAME]
FROM [OMVDDFLayer].[V_AT_Well_Hierarchy]
union all
SELECT [HUB]
,[COUNTRY]
,[LEGAL_ENTITY]
,[CONTRACT_AREA]
,[ASSET]
,[SUB_ASSET_GROUP]
,[UWI]
,[WELL_NAME]
FROM [OMVDDFLayer].[V_NZ_Well_Hierarchy]
GO
CREATE VIEW [OMVDDFLayer].[DO_ProductionSystemNetworks] as
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,[NAME]
,[PARENT_WEBID]
,[PATH]
,[PRODUCTION_SYSTEM]
,[TEMPLATE_NAME]
,[UWI]
,[WEBID]
,[LAST_UPDATED_DATE]
,[STREAM_FLUID]
FROM [OMVDDFLayer].[V_INT_ProductionSystemNetworks]
GO
-- Auto Generated (Do not modify) 34F9F609C530FD34ED9A3BC8CBD076CF0D39BA54E21E62867FDEABF651A7CEDE
CREATE VIEW [OMVDDFLayer].[DO_L_Compozition] as
SELECT [UNIQUE_KEY]
,[SAMPLE_DESCRIPTION]
,[ANALYZE_DATE]
,[PARAMETER_NAME]
,[SAMPLE_CODE]
,[PARAMETER_PRIMARY_FLUID_TYPE]
,[SAMPLING_DATE]
,[PARAMETER_TYPE]
,[COUNTRY_CODE]
,[PARAMETER_UOM]
,[PARAMETER_VALUE]
,[LAST_UPDATED_DATE]
,[SAMPLE_SOURCE]
,[SAMPLE_SOURCE_UID]
FROM [OMVDDFLayer].V_INT_L_Compozition
GO
-- Auto Generated (Do not modify) F37FB83EAA245AF489E03D7F950F3F8C0928547F7038F1C73D5E5EE141FD9F38
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[F_INT_D_PO_HydrocarbonByDate] as
SELECT
[ACTIVE_WELL_STATUS]
,[ALLOC_ASSOC_GAS]
,[ALLOC_CO2]
,[ALLOC_CONDENSATE]
,[ALLOC_CONDENSATE_GAS_RATIO]
,[ALLOC_FREE_GAS]
,[ALLOC_GAS]
,[ALLOC_GAS_OIL_RATIO]
,[ALLOC_GASLIFT]
,[ALLOC_GROSS]
,[ALLOC_H2S]
,[ALLOC_INJECTION_WATER]
,[ALLOC_LPG]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_MASS_OIL]
,[ALLOC_NET_OIL]
,[ALLOC_OIL]
,[ALLOC_TOTAL_HYDROCARBON]
,[ALLOC_VOL_CONDENSATE]
,[ALLOC_VOL_OIL]
,[ALLOC_WATER]
,[ALLOC_WATER_CUT]
,[ALLOC_WATER_GAS_RATIO]
,[AREA_NAME]
,[AREA_NAME_ABBREVIATION]
,[CHOKE_POSITION]
,[COUNTRY_CODE]
,[DATE]
,[DATE_STATUS]
,[DENSITY_OIL]
,[EVENT_CONDENSATE_GAS_RATIO]
,[EVENT_DATE]
,[EVENT_WATER_GAS_RATIO]
,[FUNCTIONAL_LOCATION]
,[HORIZON]
,[LAST_UPDATED_DATE]
,[OBJECT_CODE]
,[PERFORATION_ID]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_FIELD_NAME]
,[PRODUCTION_HOURS]
,[PRODUCTION_SYSTEM]
,[PRODUCTION_UNIT]
,[SHUT_OFF_REASON]
,[SHUT_OFF_REASON_COMMENT]
,[SHUTIN_HOURS]
,[THEOR_ASSOC_GAS]
,[THEOR_CO2]
,[THEOR_CONDENSATE]
,[THEOR_CONDENSATE_GAS_RATIO]
,[THEOR_FREE_GAS]
,[THEOR_GAS]
,[THEOR_GAS_OIL_RATIO]
,[THEOR_GASLIFT]
,[THEOR_GROSS]
,[THEOR_H2S]
,[THEOR_INJECTION_WATER]
,[THEOR_LPG]
,[THEOR_MASS_CONDENSATE]
,[THEOR_MASS_OIL]
,[THEOR_NET_CONDENSATE]
,[THEOR_NET_OIL]
,[THEOR_OIL]
,[THEOR_TOTAL_HYDROCARBON]
,[THEOR_TOTAL_PRODUCTION]
,[THEOR_VOL_CONDENSATE]
,[THEOR_VOL_OIL]
,[THEOR_WATER]
,[THEOR_WATER_CUT]
,[THEOR_WATER_GAS_RATIO]
,[THEOR_WGR]
,[TIME]
,[TYPE]
,[UBHI]
,[UWI]
,[WELL_DOWNTIME_REASON]
,[WELL_FLUID_TYPE]
,[WELL_MAX_GAS_RATE]
,[WELL_MAX_GROSS_RATE]
,[WELL_MAX_H2O_INJ_RATE]
,[WELL_NAME]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,[WELL_TYPE]
,[ALLOC_INJECTION_AIR]
,[THEOR_INJECTION_AIR]
,ALLOC_INJECTION_STEAM
,THEOR_INJECTION_STEAM
FROM [OMVDDFLayer].[V_INT_HydrocarbonbyDate]
where [TIME]= 'F' or [TIME] is null
GO
-- Auto Generated (Do not modify) 1C46A1666B43FD16EFCE4696538C4E99B1287A1B46F138BC9FD2EF9062509268
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[D_O_ConversionFactors] as
SELECT [UNIQUE_KEY]
,[OBJECT_CODE]
,[END_DATE]
,[START_DATE]
,[VALUE]
,[IS_ACTIVE]
,[OBJECT_TYPE]
,[PRECISION]
,[LAST_UPDATED_DATE]
,[UNIT_OF_MEASURE_TO_CODE]
,[FLUID_TYPE]
,[UNIT_OF_MEASURE_FROM_DESC]
,[UNIT_OF_MEASURE_TO_DESC]
,[OBJECT_NAME]
,[ADD_NUMBER]
,[UNIT_OF_MEASURE_FROM_CODE]
,[COUNTRY_CODE]
FROM OMVDDFLayer.V_INT_PO_ConversionFactors
GO
CREATE VIEW [OMVDDFLayer].[F_D_PO_MonthlyWellProductionbyAggregationLevel] as
SELECT [UNIQUE_KEY]
,[CUMULATIVE_ASSOC_GAS]
,[CUMULATIVE_VOL_CONDENSATE]
,[CUMULATIVE_VOL_OIL]
,[CUMULATIVE_FREE_GAS]
,[INJECTION_STEAM_DAYS]
,[CUMULATIVE_WATER]
,[INJECTION_WATER_DAYS]
,[CUMULATIVE_MASS_STEAM_INJ]
,[CUMULATIVE_MASS_OIL]
,[PRODUCTION_DAYS]
,[REPORT_MONTH]
,[ALLOC_WATER_GAS_RATIO]
,[WELL_TYPE]
,[ALLOC_ASSOC_GAS]
,[ALLOC_MASS_CONDENSATE]
,[ALLOC_VOL_CONDENSATE]
,[CUMULATIVE_START_DATE]
,[INJECTION_AIR_DAYS]
,[ALLOC_INJECTION_WATER]
,[CUMULATIVE_GROSS]
,[ALLOC_MASS_OIL]
,[ALLOC_GROSS]
,[CUMULATIVE_VOL_AIR_INJ]
,[CUMULATIVE_INJECTION_WATER]
,[CUMULATIVE_MASS_CONDENSATE]
,[UWI]
,[AGGREGATION_LEVEL]
,[ALLOC_VOL_AIR_INJ]
,[CUMULATIVE_INJECTION_GAS]
,[ALLOC_GAS_OIL_RATIO]
,[ALLOC_VOL_STEAM_INJ]
,[UID]
,[ALLOC_GAS]
,[CUMULATIVE_GAS]
,[ALLOC_VOL_OIL]
,[ALLOC_WATER_CUT]
,[ALLOC_CONDENSATE_GAS_RATIO]
,[ALLOC_MASS_STEAM_INJ]
,[ALLOC_FREE_GAS]
,[INJECTION_GAS_DAYS]
,[CUMULATIVE_VOL_STEAM_INJ]
,[ALLOC_WATER]
,[COUNTRY_CODE]
,[ALLOC_INJECTION_GAS]
FROM [ROProductionOptimization].[MonthlyWellProductionbyAggregationLevel]
GO
-- Auto Generated (Do not modify) 4DB58F8ACCE97CA7FCEF1E3FEF64649FDF4D4FBEB0214D7BD6D52BF0C2C964F3
CREATE VIEW [OMVDDFLayer].[V_INT_WH_Wells] as
select [ALTERNATIVE_COORDINATE_SYSTEM]
,[ALTERNATIVE_EASTING]
,[ALTERNATIVE_NORTHING]
,[AREA_NAME]
,[AREA_NAME_ABBR]
,[BLOCK_CODE]
,[BLOCK_NAME]
,[CONTRACT_CODE]
,[CONTRACT_NAME]
,[COORD_STATUS]
,[COUNTRY_CODE]
,[ELEVATION_ABOVE_DATUM]
,[ELEVATION_VALUE_UOM]
,[EXISTENCE_KIND]
,[GENERAL_FIELD_NAME]
,[GENERAL_FIELD_NAME_SOURCE]
,[GEOCOORD_EPSG]
,[HORIZON_PRODUCTION_UNIT]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,[LIFE_CYCLE_PHASE]
,null [OMV_OWNED]
,[ON_OFF_WELL]
,[ORIGINAL_COORDINATE_SYSTEM]
,[ORIGINAL_EASTING]
,[ORIGINAL_NORTHING]
,[PERMANENT_ELEV_REF_TYPE]
,[PRODUCTION_DENSITY_OIL]
,[PRODUCTION_FIELD_CODE]
,[PRODUCTION_SYSTEM]
,[PROJCOORD_EPSG]
,[RESPONSIBLE_DEPUTY1]
,[RESPONSIBLE_DEPUTY2]
,[RESPONSIBLE_ENGINEER]
,[RESPONSIBLE_OPERATOR]
,[RESPONSIBLE_PT]
,[RESPONSIBLE_RE]
,[SHUTOFF_REASON_COMMENT]
,[SURFACE_LATITUDE_WGS84]
,[SURFACE_LONGITUDE_WGS84]
,[UWI]
,[VENTURE]
,[WATER_DEPTH]
,[WATER_DEPTH_UOM]
,[WELL_COMMENTS]
,[WELL_DATA_SOURCE]
,[WELL_DOWNTIME_REASON]
,[WELL_FLUID_TYPE]
,[WELL_NAME]
,[WELL_NAME_ABBR]
,[WELL_PRODUCTION_METHOD]
,[WELL_PRODUCTION_METHOD_DETAIL]
,null [WELL_TYPE]
FROM [WellHeader].[Wells]
GO
-- Auto Generated (Do not modify) FB1C4421916E535C892C82669F86FA49BF4776FA195CDEDC2A8EC148C2CDAFA0
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_INT_O_DailyWellParameters] as
SELECT [UNIQUE_KEY]
,[CHOKE_POSITION]
,[UWI]
,[UBHI]
,[A_ANNULUS_PRESSURE]
,[C_ANNULUS_PRESSURE]
,[DOWNHOLE_TEMPERATURE]
,[LAST_UPDATED_DATE]
,[DATE]
,[FLOWLINE_TEMPERATURE]
,[FLOWLINE_PRESSURE]
,[B_ANNULUS_PRESSURE]
,[WELLHEAD_PRESSURE]
,[WELLHEAD_TEMPERATURE]
,[DOWNHOLE_PRESSURE]
,[COUNTRY_CODE]
FROM [Operations].[DailyWellParameters]
UNION ALL
SELECT [UNIQUE_KEY]
,[CHOKE_POSITION]
,[UWI]
,[UBHI]
,[A_ANNULUS_PRESSURE]
,[C_ANNULUS_PRESSURE]
,[DOWNHOLE_TEMPERATURE]
,[LAST_UPDATED_DATE]
,[DATE]
,[FLOWLINE_TEMPERATURE]
,[FLOWLINE_PRESSURE]
,[B_ANNULUS_PRESSURE]
,[WELLHEAD_PRESSURE]
,[WELLHEAD_TEMPERATURE]
,[DOWNHOLE_PRESSURE]
,[COUNTRY_CODE]
FROM [AEOperations].[DailyWellParameters]
UNION ALL
SELECT [UNIQUE_KEY]
,[CHOKE_POSITION]
,[UWI]
,[UBHI]
,[A_ANNULUS_PRESSURE]
,[C_ANNULUS_PRESSURE]
,[DOWNHOLE_TEMPERATURE]
,[LAST_UPDATED_DATE]
,[DATE]
,[FLOWLINE_TEMPERATURE]
,[FLOWLINE_PRESSURE]
,[B_ANNULUS_PRESSURE]
,[WELLHEAD_PRESSURE]
,[WELLHEAD_TEMPERATURE]
,[DOWNHOLE_PRESSURE]
,[COUNTRY_CODE]
FROM [ROOperations].[DailyWellParameters]
GO
-- Auto Generated (Do not modify) 20DD6543146C8327EC42E79D26460B1ED79473FB05CD02781015597663D49089
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[F_D_PO_ProductionbyStream] as
SELECT [UNIQUE_KEY]
,[STREAM_TYPE_CI]
--,[STREAM_CATEGORY]
,[OBSERVATIONS]
,[FACILITY_UID_FROM]
,[STREAM_SUBGROUP]
,[FACILITY_UID_TO]
,[LAST_UPDATED_DATE]
,[EQUIPMENT_DESCRIPTION]
,[STREAM_PHASE]
,[DATE]
,[STREAM_GROUP]
,[EQUIPMENT_THERMIC_CAPACITY]
,[ALLOC_VOL]
,[STREAM_CODE]
,[THEOR_VOL]
--,[STREAM_SUBCATEGORY]
,[COUNTRY_CODE]
,[STREAM_NAME]
FROM [OMVDDFLayer].[V_INT_PO_ProductionbyStream]
GO
-- Auto Generated (Do not modify) 7C7BE992BF10EF1BF0C167E0BA518EF9F81AF495B9F63E9F7A4BABFC207D6F6F
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[DO_Well_Borehole] as
SELECT b.[UNIQUE_KEY]
,b.[BOREHOLE_ELEV_ABOVE_DATUM]
,b.[BLOCK_CODE]
,b.[BLOCK_NAME]
,b.[BOREHOLE_COMMENTS]
,b.[BOREHOLE_NAME]
,b.[BOREHOLE_STATUS]
,b.[COUNTRY_CODE]
,b.[ELEVATION_REF_TYPE]
,b.[ELEVATION_VALUE_UOM]
,b.[EXISTENCE_KIND]
,cast(b.[LAST_UPDATED_DATE] as DATETIME) as [LAST_UPDATED_DATE]
,b.[OMV_OWNED]
,b.[OUTCOME_OF_WELL]
,b.[STATUS_FLAG_PRODUCTION]
,b.[UBHI]
,b.[UWI]
,b.[VENTURE]
,hb.[WELL_FLUID_TYPE]
,hb.[WELL_PRODUCTION_METHOD]
,hb.[WELL_PRODUCTION_METHOD_DETAIL]
,hb.[WELL_TYPE]
FROM [ROWellHeader].[Borehole] b
left join [OMVDDFLayer].V_RO_UBHI_FROM_HYDROCARBON hb
ON b.UBHI = hb.UBHI
GO
CREATE VIEW [OMVDDFLayer].[V_INT_ProductionSystemHierarchy] as
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PATH]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,[LAST_UPDATED_DATE]
,[NAME]
,[PARENT_WEBID]
,[PATH]
,[TEMPLATE_NAME]
,[UBHI]
,[UWI]
,[WEBID]
FROM [ROCurrentHierarchies].[ProductionSystemHierarchy]
UNION
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PATH]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,NULL AS [LAST_UPDATED_DATE]
,NULL AS [NAME]
,[PARENT_WEBID]
,[PATH]
,[TEMPLATE_NAME]
,[UBHI]
,[UWI]
,[WEBID]
FROM [ATCurrentHierarchies].[ProductionSystemHierarchy]
UNION
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PATH]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,NULL AS [LAST_UPDATED_DATE]
,NULL AS [NAME]
,[PARENT_WEBID]
,[PATH]
,[TEMPLATE_NAME]
,NULL AS [UBHI]
,[UWI]
,[WEBID]
FROM [AECurrentHierarchies].[ProductionSystemHierarchy]
UNION
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PATH]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,NULL AS [LAST_UPDATED_DATE]
,NULL AS [NAME]
,[PARENT_WEBID]
,[PATH]
,[TEMPLATE_NAME]
,[UBHI]
,[UWI]
,[WEBID]
FROM [YECurrentHierarchies].[ProductionSystemHierarchy]
UNION
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PATH]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,NULL AS [LAST_UPDATED_DATE]
,NULL AS [NAME]
,[PARENT_WEBID]
,[PATH]
,[TEMPLATE_NAME]
,[UBHI]
,[UWI]
,[WEBID]
FROM NZCurrentHierarchies.ProductionSystemHierarchy
UNION
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PATH]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,NULL AS [LAST_UPDATED_DATE]
,NULL AS [NAME]
,[PARENT_WEBID]
,[PATH]
,[TEMPLATE_NAME]
,NULL AS [UBHI]
,[UWI]
,[WEBID]
FROM TNCurrentHierarchies.ProductionSystemHierarchy
UNION
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PATH]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,NULL AS [LAST_UPDATED_DATE]
,NULL AS [NAME]
,[PARENT_WEBID]
,[PATH]
,[TEMPLATE_NAME]
,[UBHI]
,[UWI]
,[WEBID]
FROM RUCurrentHierarchies.ProductionSystemHierarchy
GO
CREATE VIEW [OMVDDFLayer].[V_RO_SubsurfaceHierarchy] as
with country as
(select * from [ROCurrentHierarchies].[FlatSubsurfaceHierarchy] x
where x.deletion_flag = 'False'
and x.element_template = 'Country - SS' ),
field as
(select * from [ROCurrentHierarchies].[FlatSubsurfaceHierarchy] x
where x.deletion_flag = 'False'
and x.element_template = 'Field - SS' ),
production_unit as
(select * from [ROCurrentHierarchies].[FlatSubsurfaceHierarchy] x
where x.deletion_flag = 'False'
and x.element_template = 'Production Unit - SS' )
SELECT
pi.[PARENT_WEBID]
,pi.[PI_COMMENT]
,pi.[PI_BOTTOM_DEPTH_TVD]
,pi.[PI_DATE_INTERVAL_OPENED]
,pi.[PI_TOP_DEPTH_MD]
,pu.[PU_TOP_DEPTH]
-- ,pi.[PI_ALLOCATION_FACTOR_INJECTION_STEAM]
,pi.[PI_STATUS]
,c.[COUNTRY]
,pi.[DESCRIPTION]
-- ,pi.[PI_ALLOCATION_FACTOR_INJECTION_WATER]
-- ,pi.[PI_ALLOCATION_FACTOR_GAS]
,pi.[PI_TOP_DEPTH_TVDSS]
-- ,pi.[PI_ALLOCATION_FACTOR_WATER]
,pi.[PI_TOP_DEPTH_TVD]
,pi.[ELEMENT_ID] prod_interval_id
,pu.[ELEMENT_ID] prod_unit_id
,f.[ELEMENT_ID] field_id
,pi.[WEBID]
,pi.[UWI]
,pi.[PI_BOTTOM_DEPTH_MD]
,pi.[UBHI]
,pi.[PI_DATE_INTERVAL_CLOSED]
,f.[F_PRODUCTION_SYSTEM_CODE]
,pi.[ELEMENT_ID_TYPE]
,pi.[ELEMENT_TEMPLATE]
-- ,pi.[PI_ALLOCATION_FACTOR_OIL]
,pi.[SOURCE_SYSTEM]
,pu.[PU_BOTTOM_DEPTH]
,pi.[LAST_UPDATED_DATE]
,pu.[PU_CONTACT_TYPE]
,pi.[DELETION_FLAG]
,pi.[PI_BOTTOM_DEPTH_TVDSS]
,pi.[PI_PERFORATION_CODE]
,pi.[ELEMENT_DESCRIPTION]
,pi.[ELEMENT_NAME] prod_interval_name
,pu.[ELEMENT_NAME] prod_unit_name
,f.[ELEMENT_NAME] field_name
-- ,pi.[PI_ALLOCATION_FACTOR_CONDENSATE]
,f.[F_ASSET_NAME]
,pu.[PU_CODE]
FROM [ROCurrentHierarchies].[FlatSubsurfaceHierarchy] pi join production_unit pu on pi.[PARENT_WEBID] =pu.[WEBID]
join field f on pu.[PARENT_WEBID] =f.[WEBID] join country c on f.[PARENT_WEBID] =c.[WEBID]
where pi.deletion_flag = 'False'
and pi.element_template = 'Production Interval - SS'
GO
CREATE VIEW [OMVDDFLayer].[RO_FlatSubsurfaceGeologicalDetails] as
SELECT [UNIQUE_KEY]
,[ELEMENT_ID]
,[WEBID]
,[UWI]
,[UBHI]
,[ELEMENT_ID_TYPE]
,[LAST_UPDATED_DATE]
,[PI_STATUS]
,[GEOLOGICAL_UNIT]
,[GEOLOGICAL_AGE]
,[COUNTRY]
,[RESERVOIR_FORMATION_CODE]
,[RESERVOIR_FORMATION]
FROM [ROCurrentHierarchies].[FlatSubsurfaceGeologicalDetails]
GO
-- Auto Generated (Do not modify) 1C46A1666B43FD16EFCE4696538C4E99B1287A1B46F138BC9FD2EF9062509268
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [OMVDDFLayer].[V_INT_PO_ConversionFactors] as
SELECT [UNIQUE_KEY]
,[OBJECT_CODE]
,[END_DATE]
,[START_DATE]
,[VALUE]
,[IS_ACTIVE]
,[OBJECT_TYPE]
,[PRECISION]
,[LAST_UPDATED_DATE]
,[UNIT_OF_MEASURE_TO_CODE]
,[FLUID_TYPE]
,[UNIT_OF_MEASURE_FROM_DESC]
,[UNIT_OF_MEASURE_TO_DESC]
,[OBJECT_NAME]
,[ADD_NUMBER]
,[UNIT_OF_MEASURE_FROM_CODE]
,[COUNTRY_CODE]
FROM [ROProductionOptimization].[ConversionFactors]
union all
SELECT [UNIQUE_KEY]
,[OBJECT_CODE]
,[END_DATE]
,[START_DATE]
,[VALUE]
,[IS_ACTIVE]
,[OBJECT_TYPE]
,[PRECISION]
,[LAST_UPDATED_DATE]
,[UNIT_OF_MEASURE_TO_CODE]
,[FLUID_TYPE]
,[UNIT_OF_MEASURE_FROM_DESC]
,[UNIT_OF_MEASURE_TO_DESC]
,[OBJECT_NAME]
,[ADD_NUMBER]
,[UNIT_OF_MEASURE_FROM_CODE]
,[COUNTRY_CODE]
FROM [ProductionOptimization].[ConversionFactors]
GO
CREATE VIEW [OMVDDFLayer].[DO_ProductionSystemHierarchy] as
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PATH]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,[LAST_UPDATED_DATE]
,[NAME]
,[PARENT_WEBID]
,[PATH]
,[TEMPLATE_NAME]
,[UBHI]
,[UWI]
,[WEBID]
FROM [OMVDDFLayer].[V_INT_ProductionSystemHierarchy]
GO
-- Auto Generated (Do not modify) 1B6F72C417A33549D5E07A7408DBC7CE4C55B1DAC9028FDF64B036A6F1586F05
CREATE VIEW [OMVDDFLayer].[V_AT_Well_Hierarchy] as
with
legalentity as(
select NAME, WEBID, PARENT_WEBID from [ATCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Legal Entity - ORG'),
contractarea as (
select NAME, WEBID, PARENT_WEBID from [ATCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Contract Area - ORG'),
asset as (
select NAME, WEBID, PARENT_WEBID from [ATCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Asset - ORG'),
subasset as (
select NAME, WEBID, PARENT_WEBID from [ATCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'SubAsset Group - ORG'),
wells as (
select NAME, WEBID, PARENT_WEBID, UWI,HUB, COUNTRY from [ATCurrentHierarchies].[OrganizationalHierarchy] where TEMPLATE_NAME = 'Well - ORG')
select w.HUB, w.COUNTRY,l.NAME as LEGAL_ENTITY, ca.NAME as CONTRACT_AREA, a.NAME as ASSET, sa.NAME as SUB_ASSET_GROUP, w.UWI, w.NAME as WELL_NAME
from legalentity l, contractarea ca, asset a, subasset sa, wells w
where l.WEBID = ca.PARENT_WEBID and ca.WEBID = a.PARENT_WEBID and a.WEBID = sa.PARENT_WEBID and (sa.WEBID = w.PARENT_WEBID or a.WEBID = w.PARENT_WEBID)
--and COUNTRY = 'Austria'
GO
CREATE VIEW [dbo].[V_INT_PO_FacilityCapacities] as
SELECT [UNIQUE_KEY]
,[THEOR_CONDENSATE]
,[THEOR_FREE_GAS]
,[THEOR_GROSS]
,[THEOR_OIL]
,[THEOR_WATER]
,[COUNTRY_CODE]
,[FACILITY_OBJECT_NAME]
,[START_DATE]
FROM [ProductionOptimization].[FacilityCapacities]
GO
CREATE VIEW [OMVDDFLayer].[DO_WorkoverActualCosts] as
SELECT [UNIQUE_KEY]
,[ACHIEVED_TIME_CONSUMMED]
,[ACHIEVED_COST_VALUE]
,[UWI]
,[PROJECT_NUMBER]
,[WBS]
,[ACHIEVED_COST_TYPE]
,[SAP_INITIAL_AR]
--,[ACHIEVED_TIME_CATEGORY]
,[PROFIT_CENTER]
,[WO_AAP]
,[OPPORTUNITY_NUMBER]
,[COUNTRY_CODE]
FROM [ROProductionTechnology].[WorkoverActualCosts]
GO
CREATE VIEW [OMVDDFLayer].[V_INT_ProductionReporting] as
SELECT [UNIQUE_KEY]
,cast([ACTUAL_D_CUM_GAS_SALES] as float) [ACTUAL_D_CUM_GAS_SALES]
,cast([ACTUAL_D_CUM_OIL_SALES] as float) [ACTUAL_D_CUM_OIL_SALES]
,cast([ACTUAL_M_AVG_THC_BCP] as float) [ACTUAL_M_AVG_THC_BCP]
,cast([ACTUAL_M_AVG_THC_INCR_CP] as float) [ACTUAL_M_AVG_THC_INCR_CP]
,cast([ACTUAL_M_AVG_THC_IPSC] as float)[ACTUAL_M_AVG_THC_IPSC]
,cast([ACTUAL_M_AVG_THC_PD] as float) [ACTUAL_M_AVG_THC_PD]
,cast([ACTUAL_M_AVG_THC_UD] as float)[ACTUAL_M_AVG_THC_UD]
,cast([ACTUAL_M_CUM_GAS_FUEL] as float)[ACTUAL_M_CUM_GAS_FUEL]
,cast([ACTUAL_M_CUM_GAS_SALES] as float) [ACTUAL_M_CUM_GAS_SALES]
,cast([ACTUAL_M_CUM_OIL_SALES] as float)[ACTUAL_M_CUM_OIL_SALES]
,[FORECAST_M_AVG_GAS_FUEL]
,[FORECAST_M_AVG_GAS_SALES]
,[FORECAST_M_AVG_OIL_SALES]
,cast([FORECAST_M_AVG_THC_BCP] as float) [FORECAST_M_AVG_THC_BCP]
,cast([FORECAST_M_AVG_THC_INCR_CP_CY] as float)[FORECAST_M_AVG_THC_INCR_CP_CY]
,[FORECAST_M_AVG_THC_INCR_CP_NY]
,[FORECAST_M_AVG_THC_IPSC]
,[FORECAST_M_AVG_THC_PD]
,[FORECAST_M_AVG_THC_UD]
,[STARGET_M_AVG_THC_MKPROD]
,cast([TARGET_M_AVG_GAS_FUEL] as float) [TARGET_M_AVG_GAS_FUEL]
,cast([TARGET_M_AVG_GAS_SALES] as float) [TARGET_M_AVG_GAS_SALES]
,cast([TARGET_M_AVG_OIL_SALES] as float) [TARGET_M_AVG_OIL_SALES]
,cast([TARGET_M_AVG_THC_BCP] as float) [TARGET_M_AVG_THC_BCP]
,cast([TARGET_M_AVG_THC_INCR_CP] as float) [TARGET_M_AVG_THC_INCR_CP]
,cast([TARGET_M_AVG_THC_IPSC] as float)[TARGET_M_AVG_THC_IPSC]
,cast([TARGET_M_AVG_THC_PD] as float) [TARGET_M_AVG_THC_PD]
,cast([TARGET_M_AVG_THC_UD] as float) [TARGET_M_AVG_THC_UD]
,[ACCOUNTING]
,[AREA_ID]
,[AREA_NAME]
,[COUNTRY_CODE]
,cast([DATE] AS DATETIME) as [DATE]
,[HIERARCHY_LEVEL_NAME]
,[HIERARCHY_LEVEL_TYPE]
,[HIERARCHY_LEVEL_UID]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,cast([REPORT_DATE] AS DATETIME) as [REPORT_DATE]
,[TYPE]
,[FORECAST_M_AVG_THC_MPC_EXP]
,[FORECAST_M_AVG_THC_MPC_RES]
,cast ([ACTUAL_M_AVG_THC_MPC_EXP] as float)[ACTUAL_M_AVG_THC_MPC_EXP]
,[FORECAST_M_AVG_THC_UCP_WEL]
,cast([ACTUAL_M_AVG_THC_MPC_FAC] as float)[ACTUAL_M_AVG_THC_MPC_FAC]
,cast([TARGET_M_AVG_NGL_SALES] as float) [TARGET_M_AVG_NGL_SALES]
,cast([FORECAST_M_AVG_THC_ICP_FAC] as float) [FORECAST_M_AVG_THC_ICP_FAC]
,cast([ACTUAL_M_AVG_THC_UTCP_WEL]as float)[ACTUAL_M_AVG_THC_UTCP_WEL]
,cast([ACTUAL_M_AVG_THC_MPC_COM]as float)[ACTUAL_M_AVG_THC_MPC_COM]
,cast([ACTUAL_M_AVG_THC_ICP_EXP] as float) [ACTUAL_M_AVG_THC_ICP_EXP]
,cast([ACTUAL_M_AVG_THC_ICP_RES]as float)[ACTUAL_M_AVG_THC_ICP_RES]
,cast([ACTUAL_M_CUM_OIL_FUEL]as float)[ACTUAL_M_CUM_OIL_FUEL]
,cast([FORECAST_M_AVG_THC_ICP_COM] as float) [FORECAST_M_AVG_THC_ICP_COM]
,[FORECAST_M_AVG_OIL_FUEL]
,isNull([OMV_SHARE_TYPE],'net') [OMV_SHARE_TYPE]
,cast([TARGET_M_AVG_NGL_FUEL] as float) [TARGET_M_AVG_NGL_FUEL]
,[FORECAST_M_AVG_THC_UTCP_WEL]
,[FORECAST_M_AVG_NGL_SALES]
,cast([TARGET_M_AVG_OIL_FUEL] as float) [TARGET_M_AVG_OIL_FUEL]
,cast([ACTUAL_D_CUM_GAS_FUEL]as float)[ACTUAL_D_CUM_GAS_FUEL]
,cast([ACTUAL_D_CUM_OIL_FUEL]as float)[ACTUAL_D_CUM_OIL_FUEL]
,[FORECAST_M_AVG_THC_UCP_COM]
,cast([ACTUAL_M_AVG_THC_MPC_RES]as float)[ACTUAL_M_AVG_THC_MPC_RES]
,[FORECAST_M_AVG_THC_ICP_WEL]
,[FORECAST_M_AVG_THC_UCP_FAC]
,cast([ACTUAL_M_AVG_THC_MPC_WEL] as float)[ACTUAL_M_AVG_THC_MPC_WEL]
,[FORECAST_M_AVG_THC_MPC_WEL]
,[FORECAST_M_AVG_THC_UCP_EXP]
,cast([ACTUAL_M_CUM_NGL_SALES]as float)[ACTUAL_M_CUM_NGL_SALES]
,cast([ACTUAL_D_CUM_NGL_SALES]as float)[ACTUAL_D_CUM_NGL_SALES]
,cast([ACTUAL_M_CUM_NGL_FUEL]as float)[ACTUAL_M_CUM_NGL_FUEL]
,[FORECAST_M_AVG_THC_UCP_RES]
,cast([ACTUAL_M_AVG_THC_ICP_WEL]as float)[ACTUAL_M_AVG_THC_ICP_WEL]
,[FORECAST_M_AVG_NGL_FUEL]
,[FORECAST_M_AVG_THC_MPC_COM]
,cast( [ACTUAL_M_AVG_THC_ICP_COM] as float) as [ACTUAL_M_AVG_THC_ICP_COM]
,cast( [ACTUAL_M_AVG_THC_ICP_FAC]as float) as [ACTUAL_M_AVG_THC_ICP_FAC]
,cast([FORECAST_M_AVG_THC_ICP_EXP]as float) as [FORECAST_M_AVG_THC_ICP_EXP]
,[FORECAST_M_AVG_THC_MPC_FAC]
,[FORECAST_M_AVG_THC_ICP_RES]
FROM [ProductionOptimization].[ProductionReporting]
UNION ALL
SELECT[UNIQUE_KEY]
,cast([ACTUAL_D_CUM_GAS_SALES] as float) [ACTUAL_D_CUM_GAS_SALES]
,cast([ACTUAL_D_CUM_OIL_SALES] as float) [ACTUAL_D_CUM_OIL_SALES]
,cast([ACTUAL_M_AVG_THC_BCP] as float) [ACTUAL_M_AVG_THC_BCP]
,cast([ACTUAL_M_AVG_THC_INCR_CP] as float) [ACTUAL_M_AVG_THC_INCR_CP]
,cast([ACTUAL_M_AVG_THC_IPSC] as float)[ACTUAL_M_AVG_THC_IPSC]
,cast([ACTUAL_M_AVG_THC_PD] as float) [ACTUAL_M_AVG_THC_PD]
,cast([ACTUAL_M_AVG_THC_UD] as float)[ACTUAL_M_AVG_THC_UD]
,cast([ACTUAL_M_CUM_GAS_FUEL] as float)[ACTUAL_M_CUM_GAS_FUEL]
,cast([ACTUAL_M_CUM_GAS_SALES] as float) [ACTUAL_M_CUM_GAS_SALES]
,cast([ACTUAL_M_CUM_OIL_SALES] as float)[ACTUAL_M_CUM_OIL_SALES]
,[FORECAST_M_AVG_GAS_FUEL]
,[FORECAST_M_AVG_GAS_SALES]
,[FORECAST_M_AVG_OIL_SALES]
,[FORECAST_M_AVG_THC_BCP]
,[FORECAST_M_AVG_THC_INCR_CP_CY]
,[FORECAST_M_AVG_THC_INCR_CP_NY]
,[FORECAST_M_AVG_THC_IPSC]
,[FORECAST_M_AVG_THC_PD]
,[FORECAST_M_AVG_THC_UD]
,[STARGET_M_AVG_THC_MKPROD]
,cast([TARGET_M_AVG_GAS_FUEL] as float) [TARGET_M_AVG_GAS_FUEL]
,cast([TARGET_M_AVG_GAS_SALES] as float) [TARGET_M_AVG_GAS_SALES]
,cast([TARGET_M_AVG_OIL_SALES] as float) [TARGET_M_AVG_OIL_SALES]
,cast([TARGET_M_AVG_THC_BCP] as float) [TARGET_M_AVG_THC_BCP]
,cast([TARGET_M_AVG_THC_INCR_CP] as float) [TARGET_M_AVG_THC_INCR_CP]
,cast([TARGET_M_AVG_THC_IPSC] as float)[TARGET_M_AVG_THC_IPSC]
,cast([TARGET_M_AVG_THC_PD] as float) [TARGET_M_AVG_THC_PD]
,cast([TARGET_M_AVG_THC_UD] as float) [TARGET_M_AVG_THC_UD]
,[ACCOUNTING]
,[AREA_ID]
,[AREA_NAME]
,[COUNTRY_CODE]
,cast([DATE] AS DATETIME) as [DATE]
,[HIERARCHY_LEVEL_NAME]
,[HIERARCHY_LEVEL_TYPE]
,[HIERARCHY_LEVEL_UID]
,cast([LAST_UPDATED_DATE] AS DATETIME) as [LAST_UPDATED_DATE]
,cast([REPORT_DATE] AS DATETIME) as [REPORT_DATE]
,[TYPE]
,[FORECAST_M_AVG_THC_MPC_EXP]
,[FORECAST_M_AVG_THC_MPC_RES]
,cast([ACTUAL_M_AVG_THC_MPC_EXP] as float) [ACTUAL_M_AVG_THC_MPC_EXP]
,[FORECAST_M_AVG_THC_UCP_WEL]
,cast([ACTUAL_M_AVG_THC_MPC_FAC] as float)[ACTUAL_M_AVG_THC_MPC_FAC]
,cast([TARGET_M_AVG_NGL_SALES] as float) [TARGET_M_AVG_NGL_SALES]
,cast([FORECAST_M_AVG_THC_ICP_FAC] as float) [FORECAST_M_AVG_THC_ICP_FAC]
,cast([ACTUAL_M_AVG_THC_UTCP_WEL] as float)[ACTUAL_M_AVG_THC_UTCP_WEL]
,cast([ACTUAL_M_AVG_THC_MPC_COM] as float)[ACTUAL_M_AVG_THC_MPC_COM]
,cast([ACTUAL_M_AVG_THC_ICP_EXP] as float) [ACTUAL_M_AVG_THC_ICP_EXP]
,cast([ACTUAL_M_AVG_THC_ICP_RES] as float)[ACTUAL_M_AVG_THC_ICP_RES]
,cast([ACTUAL_M_CUM_OIL_FUEL] as float)[ACTUAL_M_CUM_OIL_FUEL]
,cast([FORECAST_M_AVG_THC_ICP_COM] as float) [FORECAST_M_AVG_THC_ICP_COM]
,[FORECAST_M_AVG_OIL_FUEL]
,isNull([OMV_SHARE_TYPE],'net') [OMV_SHARE_TYPE]
,cast([TARGET_M_AVG_NGL_FUEL] as float) [TARGET_M_AVG_NGL_FUEL]
,[FORECAST_M_AVG_THC_UTCP_WEL]
,[FORECAST_M_AVG_NGL_SALES]
,cast([TARGET_M_AVG_OIL_FUEL] as float) [TARGET_M_AVG_OIL_FUEL]
,cast([ACTUAL_D_CUM_GAS_FUEL] as float) [ACTUAL_D_CUM_GAS_FUEL]
,cast([ACTUAL_D_CUM_OIL_FUEL] as float)[ACTUAL_D_CUM_OIL_FUEL]
,[FORECAST_M_AVG_THC_UCP_COM]
,cast([ACTUAL_M_AVG_THC_MPC_RES] as float) [ACTUAL_M_AVG_THC_MPC_RES]
,[FORECAST_M_AVG_THC_ICP_WEL]
,[FORECAST_M_AVG_THC_UCP_FAC]
,cast([ACTUAL_M_AVG_THC_MPC_WEL] as float)[ACTUAL_M_AVG_THC_MPC_WEL]
,[FORECAST_M_AVG_THC_MPC_WEL]
,[FORECAST_M_AVG_THC_UCP_EXP]
,cast([ACTUAL_M_CUM_NGL_SALES] as float)[ACTUAL_M_CUM_NGL_SALES]
,cast([ACTUAL_D_CUM_NGL_SALES] as float)[ACTUAL_D_CUM_NGL_SALES]
,cast([ACTUAL_M_CUM_NGL_FUEL] as float)[ACTUAL_M_CUM_NGL_FUEL]
,[FORECAST_M_AVG_THC_UCP_RES]
,cast([ACTUAL_M_AVG_THC_ICP_WEL] as float)[ACTUAL_M_AVG_THC_ICP_WEL]
,[FORECAST_M_AVG_NGL_FUEL]
,[FORECAST_M_AVG_THC_MPC_COM]
,cast( [ACTUAL_M_AVG_THC_ICP_COM] as float) as [ACTUAL_M_AVG_THC_ICP_COM]
,cast( [ACTUAL_M_AVG_THC_ICP_FAC]as float) as [ACTUAL_M_AVG_THC_ICP_FAC]
,cast([FORECAST_M_AVG_THC_ICP_EXP]as float) as [FORECAST_M_AVG_THC_ICP_EXP]
,[FORECAST_M_AVG_THC_MPC_FAC]
,[FORECAST_M_AVG_THC_ICP_RES]
FROM [ROProductionOptimization].[ProductionReporting]
GO
-- Auto Generated (Do not modify) 64274BB2A379917CDD0FF6596E190F865E65B20BE52227DE68A2AD101C17081B
CREATE VIEW [OMVDDFLayer].[F_D_PO_WellPerformance] as
SELECT [UNIQUE_KEY]
,[WT_A_ANNULUS_PRESSURE]
,[WT_CGR]
,[WT_CHOKE_POSITION]
,[WT_DURATION]
,[WT_NET_CONDENSATE_RATE]
,[WT_NET_OIL_MASS_RATE]
,[WT_NET_OIL_RATE]
,[WT_TOTAL_GAS_RATE]
,[WT_TOTAL_WATER_RATE]
,[WT_TUBING_HEAD_FLOWING_PRESSURE]
,[WT_WATERCUT]
,[WT_WGR]
,[COUNTRY_CODE]
,[LAST_UPDATED_DATE]
,[UBHI]
,[UWI]
,[WELLTEST_STATUS]
,x.TheDate [DATE]
,[WT_DATE]
,[WT_START_TEST]
,[WT_NET_CONDENSATE_MASS_RATE]
FROM [OMVDDFLayer].[V_RO_PO_WellPerformance] join [OMVDDFLayer].D_Date x on CAST([WT_START_TEST]as DATE) <= x.TheDate and (x.TheDate < CAST([WT_END_TEST] as DATE) and [WT_END_TEST] is not null or x.TheDate <= cast(SYSDATETIME() as DATE) and [WT_END_TEST] is null )
union all
select
[UNIQUE_KEY]
,null [WT_A_ANNULUS_PRESSURE]
,null[WT_CGR]
,null [WT_CHOKE_POSITION]
,null [WT_DURATION]
,null[WT_NET_CONDENSATE_RATE]
,[WT_NET_OIL_MASS_RATE]
,null[WT_NET_OIL_RATE]
,[WT_TOTAL_GAS_RATE]
,[WT_TOTAL_WATER_RATE]
,null [WT_TUBING_HEAD_FLOWING_PRESSURE]
,[WT_WATERCUT]
,null [WT_WGR]
,[COUNTRY_CODE]
,[LAST_UPDATED_DATE]
,[UBHI]
,[UWI]
,[WELLTEST_STATUS]
,x.TheDate [DATE]
,[WT_DATE]
,[WT_DATE] [WT_START_TEST]
,[WT_NET_CONDENSATE_MASS_RATE]
from [OMVDDFLayer].[V_INT_WellPerformance] join [OMVDDFLayer].D_Date x on CAST([WT_DATE]as DATE) <= x.TheDate and (x.TheDate < CAST([WT_END_TEST] as DATE) and [WT_END_TEST] is not null or x.TheDate <= cast(SYSDATETIME() as DATE) and [WT_END_TEST] is null )
GO
CREATE VIEW [dbo].[V_INT_ProductionSystemNetworks] as
SELECT [UNIQUE_KEY]
--,[CATEGORY_NAMES]
,[COUNTRY]
,[DESCRIPTION]
,[EXTENDED_PROPERTIES]
,[HAS_CHILDREN]
,[HIERARCHY_TYPE]
,[NAME]
,[PARENT_WEBID]
,[PATH]
,[PRODUCTION_SYSTEM]
,[TEMPLATE_NAME]
,[UWI]
,[WEBID]
,[LAST_UPDATED_DATE]
,[STREAM_FLUID]
FROM [ROCurrentHierarchies].[ProductionSystemNetworks]
GO
Editor is loading...
Leave a Comment