Open annalbrecht opened 8 months ago
annalbrecht
commented
7 months ago Chris: In the public view, a user would already be drilled down to location and quarter to see this report. Should it only show one line of information? Location/quarter.
4/9/24: Discussed with Chris.
annalbrecht
commented
4 months ago @djw4erg to add the specifics about where the data gets pulled from.
djw4erg
commented
4 months ago Update
Updated the description with the source of the values in the report and moved the ticket to groomed.
djw4erg
commented
3 months ago CREATE PROCEDURE [Reports].[EmissionSummaryReport]
@V_MON_PLAN_ID DB_ID_KEY,
@V_CALENDAR_YEAR INT,
@V_RESULT FLAG OUTPUT,
@V_ERROR_MSG DESCRIPTION OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @Rows INT
SET @Rows = 0;
SET @V_RESULT = 'F';
SET @V_ERROR_MSG = 'No data.';
BEGIN TRY
SELECT *
FROM (
SELECT agg.ECMPS_VERSION,
fac.ORIS_CODE,
fac.FACILITY_NAME,
fac.STATE,
cty.COUNTY_NAME,
agg.MON_PLAN_ID,
dbo.ConcatMonitorPlanLocations(agg.MON_PLAN_ID) AS MP_LOCATIONS,
loc.LOCATION_NAME UNIT_STACK_PIPE,
prd.CALENDAR_YEAR,
'QTR ' + CAST(prd.QUARTER AS VARCHAR) AS PERIOD,
ISNULL(agg.OPHOURS_VALUE, 0) OPHOURS_VALUE,
agg.OPDAYS_VALUE,
agg.OPTIME_VALUE,
agg.HIT_VALUE,
agg.SO2M_VALUE,
agg.CO2M_VALUE,
agg.NOXR_VALUE,
agg.NOXM_VALUE,
agg.BCO2_VALUE,
prd.QUARTER ROW_ORDER,
agg.MON_LOC_ID,
CASE
WHEN agg.FUEL_IND = 0 THEN ''
ELSE ISNULL(agg.FUEL_CD,'NO DATA')
END FUEL_CD
FROM (
SELECT lst.MON_PLAN_ID,
lst.MON_LOC_ID,
lst.RPT_PERIOD_ID,
lst.FUEL_IND,
lst.ECMPS_VERSION,
osd.FUEL_CD,
MAX(CASE WHEN osd.OP_TYPE_CD = 'OPHOURS' THEN osd.OP_VALUE END) OPHOURS_VALUE,
MAX(CASE WHEN osd.OP_TYPE_CD = 'OPDAYS' THEN osd.OP_VALUE END) OPDAYS_VALUE,
MAX(CASE WHEN osd.OP_TYPE_CD = 'OPTIME' THEN osd.OP_VALUE END) OPTIME_VALUE,
MAX(CASE WHEN osd.OP_TYPE_CD = 'HIT' THEN osd.OP_VALUE END) HIT_VALUE,
MAX(CASE WHEN osd.OP_TYPE_CD = 'SO2M' THEN osd.OP_VALUE END) SO2M_VALUE,
MAX(CASE WHEN osd.OP_TYPE_CD = 'CO2M' THEN osd.OP_VALUE END) CO2M_VALUE,
MAX(CASE WHEN osd.OP_TYPE_CD = 'NOXR' THEN osd.OP_VALUE END) NOXR_VALUE,
MAX(CASE WHEN osd.OP_TYPE_CD = 'NOXM' THEN osd.OP_VALUE END) NOXM_VALUE,
MAX(CASE WHEN osd.OP_TYPE_CD = 'BCO2' THEN osd.OP_VALUE END) BCO2_VALUE
FROM (
SELECT sel.MON_PLAN_ID,
sel.MON_LOC_ID,
sel.RPT_PERIOD_ID,
sel.FUEL_IND,
sel.ECMPS_VERSION,
MAX(osd.FUEL_CD) MAX_FUEL_CD
FROM (
SELECT mpl.MON_PLAN_ID,
mpl.MON_LOC_ID,
prd.RPT_PERIOD_ID,
ind.FUEL_IND,
ver.ECMPS_VERSION_TEXT ECMPS_VERSION
from MONITOR_PLAN_LOCATION mpl,
REPORTING_PERIOD prd,
VW_RPT_ECMPS_VERSION ver,
(
select 0 FUEL_IND
union all
select 1 FUEL_IND
) ind
where mpl.MON_PLAN_ID = @V_MON_PLAN_ID
and (@V_CALENDAR_YEAR IS NULL OR prd.CALENDAR_YEAR = @V_CALENDAR_YEAR)
) sel
INNER JOIN OPERATING_SUPP_DATA osd
on osd.MON_LOC_ID = sel.MON_LOC_ID
and osd.RPT_PERIOD_ID = sel.RPT_PERIOD_ID
and osd.OP_TYPE_CD in ('BCO2', 'CO2M', 'HIT', 'NOXM', 'NOXR', 'OPDAYS', 'OPHOURS', 'OPTIME', 'SO2M')
group by sel.MON_PLAN_ID,
sel.MON_LOC_ID,
sel.RPT_PERIOD_ID,
sel.FUEL_IND,
sel.ECMPS_VERSION
) lst
left join OPERATING_SUPP_DATA osd
on osd.MON_LOC_ID = lst.MON_LOC_ID
and osd.RPT_PERIOD_ID = lst.RPT_PERIOD_ID
and ( -- Gets non fuel specific data
(lst.FUEL_IND = 0 and osd.FUEL_CD is null) or
-- Get op hours when for fuel specific lines when no fueld specific data exists
(lst.FUEL_IND = 1 and lst.MAX_FUEL_CD is null and osd.OP_TYPE_CD = 'OPHOURS') or
-- Gets fuel specific data
(lst.FUEL_IND = 1 and osd.FUEL_CD is not null)
)
and osd.OP_TYPE_CD in ('BCO2', 'CO2M', 'HIT', 'NOXM', 'NOXR', 'OPDAYS', 'OPHOURS', 'OPTIME', 'SO2M')
group by lst.MON_PLAN_ID,
lst.MON_LOC_ID,
lst.RPT_PERIOD_ID,
lst.FUEL_IND,
lst.ECMPS_VERSION,
osd.FUEL_CD
) agg
join REPORTING_PERIOD prd
on prd.RPT_PERIOD_ID = agg.RPT_PERIOD_ID
join MONITOR_PLAN pln
on pln.MON_PLAN_ID = agg.MON_PLAN_ID
join FACILITY fac
on fac.FAC_ID = pln.FAC_ID
join VW_RPT_MONITOR_LOCATION_MERGED loc
on loc.MON_LOC_ID = agg.MON_LOC_ID
left join COUNTY_CODE cty
on cty.COUNTY_CD = fac.COUNTY_CD
UNION
SELECT DISTINCT
(SELECT ECMPS_VERSION_TEXT FROM VW_RPT_ECMPS_VERSION ) AS ECMPS_VERSION,
F.ORIS_CODE, F.FACILITY_NAME, F.STATE,
CC.COUNTY_NAME,
mp.MON_PLAN_ID,
dbo.ConcatMonitorPlanLocations(mp.MON_PLAN_ID) AS MP_LOCATIONS,
ML.LOCATION_NAME AS UNIT_STACK_PIPE,
RP.CALENDAR_YEAR,
'Ozone Season To Date' AS PERIOD,
dbo.DetermineOSTDOpSuppTotal2(RP.CALENDAR_YEAR, ML.MON_LOC_ID, 'OPHOURS') AS OPHOURS_VALUE,
NULL AS OPDAYS_VALUE,
dbo.DetermineOSTDOpSuppTotal2(RP.CALENDAR_YEAR ,ML.MON_LOC_ID, 'OPTIME') AS OPTIME_VALUE,
dbo.DetermineOSTDOpSuppTotal2(RP.CALENDAR_YEAR, ML.MON_LOC_ID, 'HIT') AS HIT_VALUE,
NULL AS SO2M_VALUE,
NULL AS CO2M_VALUE,
NULL AS NOXR_VALUE,
dbo.DetermineOSTDOpSuppTotal2(RP.CALENDAR_YEAR, ML.MON_LOC_ID, 'NOXM') AS NOXM_VALUE,
NULL AS BCO2_VALUE,
5 AS ROW_ORDER,
ML.MON_LOC_ID,
ISNULL(OP.FUEL_CD, '') AS FUEL_CD
FROM MONITOR_PLAN AS mp
INNER JOIN MONITOR_PLAN_LOCATION mpl on mp.MON_PLAN_ID = mpl.MON_PLAN_ID
INNER JOIN VW_RPT_MONITOR_LOCATION_MERGED ml on mpl.MON_LOC_ID = ml.MON_LOC_ID
INNER JOIN VW_LOCATION_PROGRAM AS UP ON ML.MON_LOC_ID = UP.MON_LOC_ID
INNER JOIN FACILITY AS F ON F.FAC_ID = mp.FAC_ID
LEFT OUTER JOIN COUNTY_CODE AS CC ON F.COUNTY_CD = CC.COUNTY_CD
INNER JOIN OPERATING_SUPP_DATA AS OP ON ML.MON_LOC_ID = OP.MON_LOC_ID
INNER JOIN ( SELECT rpInner.*,
CAST('09/30/' + CAST(rpInner.CALENDAR_YEAR AS VARCHAR) AS DATETIME) as UP_CERT_DATE,
CAST('05/01/' + CAST(rpInner.CALENDAR_YEAR AS VARCHAR) AS DATETIME) AS UP_END_DATE
FROM REPORTING_PERIOD rpInner ) rp ON rp.RPT_PERIOD_ID = OP.RPT_PERIOD_ID
WHERE mp.MON_PLAN_ID = @V_MON_PLAN_ID
AND UP.PRG_CD IN (select PRG_CD from ECMPS.Lookup.PROGRAM_CODE where OS_IND = 1)
AND UP.UNIT_MONITOR_CERT_BEGIN_DATE <= rp.UP_CERT_DATE
AND ISNULL(UP.END_DATE, '12/31/9999') >= rp.UP_END_DATE
AND OP.FUEL_CD IS NULL
UNION
SELECT DISTINCT
(SELECT ECMPS_VERSION_TEXT FROM DBO.VW_RPT_ECMPS_VERSION ) AS ECMPS_VERSION,
F.ORIS_CODE, F.FACILITY_NAME, F.STATE,
CC.COUNTY_NAME,
mp.MON_PLAN_ID,
dbo.ConcatMonitorPlanLocations(mp.MON_PLAN_ID) AS MP_LOCATIONS,
ML.LOCATION_NAME AS UNIT_STACK_PIPE,
RP.CALENDAR_YEAR,
'Year To Date' AS PERIOD,
dbo.DetermineYTDOpSuppTotal2(RP.CALENDAR_YEAR,ML.MON_LOC_ID,'OPHOURS') AS OPHOURS_VALUE,
dbo.DetermineYTDOpSuppTotal2(RP.CALENDAR_YEAR,ML.MON_LOC_ID,'OPDAYS') AS OPDAYS_VALUE,
dbo.DetermineYTDOpSuppTotal2(RP.CALENDAR_YEAR,ML.MON_LOC_ID,'OPTIME') AS OPTIME_VALUE,
dbo.DetermineYTDOpSuppTotal2(RP.CALENDAR_YEAR,ML.MON_LOC_ID,'HIT') AS HIT_VALUE,
dbo.DetermineYTDOpSuppTotal2(RP.CALENDAR_YEAR,ML.MON_LOC_ID,'SO2M') AS SO2M_VALUE,
dbo.DetermineYTDOpSuppTotal2(RP.CALENDAR_YEAR,ML.MON_LOC_ID,'CO2M') AS CO2M_VALUE,
--NULL AS NOXR_VALUE, bz6299
dbo.DetermineYTDOpSuppTotal2(RP.CALENDAR_YEAR,ML.MON_LOC_ID,'NOXRYTD') AS NOXR_VALUE,
dbo.DetermineYTDOpSuppTotal2(RP.CALENDAR_YEAR,ML.MON_LOC_ID,'NOXM') AS NOXM_VALUE,
dbo.DetermineYTDOpSuppTotal2(RP.CALENDAR_YEAR,ML.MON_LOC_ID,'BCO2') AS BCO2_VALUE,
6 AS ROW_ORDER,
ML.MON_LOC_ID,
ISNULL(OP.FUEL_CD, '') AS FUEL_CD
FROM FACILITY AS F
LEFT OUTER JOIN COUNTY_CODE AS CC ON F.COUNTY_CD = CC.COUNTY_CD
INNER JOIN MONITOR_PLAN AS mp ON F.FAC_ID = mp.FAC_ID
INNER JOIN MONITOR_PLAN_LOCATION mpl on mp.MON_PLAN_ID = mpl.MON_PLAN_ID
INNER JOIN VW_RPT_MONITOR_LOCATION_MERGED ml on mpl.MON_LOC_ID = ml.MON_LOC_ID
INNER JOIN OPERATING_SUPP_DATA AS OP ON ML.MON_LOC_ID = OP.MON_LOC_ID
INNER JOIN REPORTING_PERIOD AS RP ON OP.RPT_PERIOD_ID = RP.RPT_PERIOD_ID
LEFT OUTER JOIN MONITOR_PLAN_REPORTING_FREQ AS MPRF ON mp.MON_PLAN_ID = MPRF.MON_PLAN_ID
LEFT OUTER JOIN REPORTING_PERIOD AS BRP ON MPRF.BEGIN_RPT_PERIOD_ID = BRP.RPT_PERIOD_ID
LEFT OUTER JOIN REPORTING_PERIOD AS ERP ON MPRF.END_RPT_PERIOD_ID = ERP.RPT_PERIOD_ID
WHERE MPRF.REPORT_FREQ_CD = 'Q'
AND BRP.CALENDAR_YEAR <= RP.CALENDAR_YEAR
AND ISNULL(ERP.CALENDAR_YEAR, 9999) >= RP.CALENDAR_YEAR
AND OP.FUEL_CD IS NULL
AND MPL.MON_LOC_ID in
(SELECT MON_LOC_ID FROM MONITOR_PLAN_LOCATION
WHERE MON_PLAN_ID = @V_MON_PLAN_ID)
) as x
WHERE @V_CALENDAR_YEAR IS NULL OR CALENDAR_YEAR = @V_CALENDAR_YEAR
ORDER BY CALENDAR_YEAR
SET @Rows = @@RowCount;
IF @Rows = 0
BEGIN
SET @V_RESULT = 'T';
SET @V_ERROR_MSG = 'There are no emissions data for this configuration!';
IF @V_CALENDAR_YEAR IS NOT NULL
SET @V_ERROR_MSG = 'There are no emissions data for this configuration and year!';
END
ELSE
BEGIN
SET @V_RESULT = 'T';
SET @V_ERROR_MSG = 'Successfully got report data. (' + CAST(@Rows as varchar(12)) + ' rows)';
END
END TRY
BEGIN CATCH
SET @V_ERROR_MSG = 'Reports.' + ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (' + cast(Error_Line() as varchar) + ')';
SET @V_RESULT = 'F';
END CATCH
END
This version was developed in Oracle and may need some adjustments for PostgreSQL.
/*
1) This query produces data for two tables in the Emission Summary Report.
A) The first table contains the non-fuel-specific quarterly, ozone season, and year-to-date rows.
a) The quarterly and year-to-date data includes BCO2, CO2M, HIT, NOXM, NOXR, OP Days, Op Hours, Op Time, and SO2M.
b) The ozone season data includes HIT, NOXM, Op Hours, and Op Time.
c) Also includes the Location, Year, and Period (quarter and year-to-date or ozone-season-to-date labels).
B) The second table contains the fuel-specific quarterly rows.
a) The data includes Fuel Code and Op Hours.
b) Also includes the Location and Year.
2) The query core inclues three unioned queries.
A) The first unioned query returns quarterly data for both the non-fuel-specific and fuel-specific tables in the emission
report.
a) The Operating Supplemental data reported without a Fuel Code ends up in the non-fuel-specific table in
the report, and the date with a Fuel Code ends up in the fuel-specific table.
b) When no fuel-specific data exists for a location and quarter, the fuel-specific table in the report contains
'NO DATA' as the Fuel Code and the non-fuel-specific Op Hours.
B) The second unioned query returns the ozone-season-to-date totals for each year when an ozone season program is active.
a) Usually sums the Q2 OS values and the Q3 quarterly values.
b) Ensures that if the year is the year a location was first in a monitoring plan, values are not summed from
quarters before the begin quarter of the first monitoring plan.
c) Only sums quarters with Operating Supplemental Data.
d) Only includes Op Supplemental Data where Fuel Code is null.
C) The third unioned query returns the year-to-date values for each year.
a) Usually sums the non-NOxR values from Op Supp Data for all four quarters.
b) For NOx rate, usually uses the 'NOXRYTD' supplemental value from the last quarter that has supplemental for the
year and location.
c) Only includes Op Supplemental Data where Fuel Code is null.
*/
with
lst as
(
select pln.Mon_Plan_Id,
prd.Rpt_Period_Id,
mpl.Mon_Loc_Id,
pln.Fac_Id,
prd.Calendar_Year,
prd.Quarter
from MONITOR_PLAN pln,
MONITOR_PLAN_LOCATION mpl,
REPORTING_PERIOD prd
where pln.Mon_Plan_Id = 'TWCORNEL5-EA112F04E50E44E29ED6A63A025E4B4F' /* Replace with mon_plan_id variable */
and mpl.Mon_Plan_Id = pln.Mon_Plan_Id
and ( null /* Replace with year variable */ is null or prd.Calendar_Year = 2019 /* Replace with year variable */ )
)
select fac.Oris_Code,
fac.Facility_Name,
coalesce( unt.Unitid, stp.Stack_Name ) as Location_Name,
cmb.Calendar_Year,
cmb.Period,
cmb.Fuel_Cd,
cmb.Op_Hours,
cmb.Op_Days,
cmb.Op_Time,
cmb.Hit,
cmb.So2m,
cmb.Co2m,
cmb.Noxr,
cmb.Noxm,
cmb.Bco2,
cmb.Mon_Plan_Id,
cmb.Rpt_Period_Id,
cmb.Mon_Loc_Id
from (
--
-- Quarterly Data for the Non-Fuel-Specific and Fuel-Specific Report Report Tables
--
select sel.Mon_Plan_Id,
sel.Fuel_Ind,
sel.Rpt_Period_Id,
sel.Mon_Loc_Id,
prd.Calendar_Year,
( 'QTR ' || prd.Quarter ) as Period,
case
when sel.Fuel_Ind = 0
then null
else coalesce( osd.Fuel_Cd, 'NO DATA' )
end as Fuel_Cd,
max( case when osd.Op_Type_Cd = 'OPHOURS' then osd.Op_Value end ) as Op_Hours,
max( case when osd.Op_Type_Cd = 'OPDAYS' then osd.Op_Value end ) as Op_Days,
max( case when osd.Op_Type_Cd = 'OPTIME' then osd.Op_Value end ) as Op_Time,
max( case when osd.Op_Type_Cd = 'HIT' then osd.Op_Value end ) as Hit,
max( case when osd.Op_Type_Cd = 'SO2M' then osd.Op_Value end ) as So2m,
max( case when osd.Op_Type_Cd = 'CO2M' then osd.Op_Value end ) as Co2m,
max( case when osd.Op_Type_Cd = 'NOXR' then osd.Op_Value end ) as Noxr,
max( case when osd.Op_Type_Cd = 'NOXM' then osd.Op_Value end ) as Noxm,
max( case when osd.Op_Type_Cd = 'BCO2' then osd.Op_Value end ) as Bco2
from (
select lst.Mon_Plan_Id,
lst.Rpt_Period_Id,
lst.Mon_Loc_Id,
ind.Fuel_Ind,
-- Indicates whether fuel specific data was reported for the location in the emission report
max( case when osd.Fuel_Cd is null then 0 else 1 end ) as Fuel_Exists_Ind
from lst
join ( select 0 as Fuel_Ind from DUAL union all select 1 as Fuel_Ind from DUAL ) ind
on null is null
join OPERATING_SUPP_DATA osd
on osd.Mon_Loc_Id = lst.Mon_Loc_Id
and osd.Rpt_Period_Id = lst.Rpt_Period_Id
and osd.Op_Type_Cd in ( 'BCO2', 'CO2M', 'HIT', 'NOXM', 'NOXR', 'OPDAYS', 'OPHOURS', 'OPTIME', 'SO2M' )
group
by lst.Mon_Plan_Id,
lst.Rpt_Period_Id,
lst.Mon_Loc_Id,
ind.Fuel_Ind
) sel
join REPORTING_PERIOD prd
on prd.Rpt_Period_Id = sel.Rpt_Period_Id
left join OPERATING_SUPP_DATA osd
on osd.Mon_Loc_Id = sel.Mon_Loc_Id
and osd.Rpt_Period_Id = sel.Rpt_Period_Id
and osd.Op_Type_Cd in ( 'BCO2', 'CO2M', 'HIT', 'NOXM', 'NOXR', 'OPDAYS', 'OPHOURS', 'OPTIME', 'SO2M' )
and (
-- Gets non fuel specific data for non fuel rows
sel.Fuel_Ind = 0 and osd.Fuel_Cd is null
or
-- Gets Op Hours for fuel rows when no fuel specific data exists for the emission report
sel.Fuel_Ind = 1 and sel.Fuel_Exists_Ind = 0 and osd.Op_Type_Cd = 'OPHOURS'
or
-- Gets fuel specirfic data for fuel rows
sel.Fuel_Ind = 1 and osd.Fuel_Cd is not null
)
group
by sel.Mon_Plan_Id,
sel.Fuel_Ind,
sel.Rpt_Period_Id,
sel.Mon_Loc_Id,
prd.Calendar_Year,
prd.Quarter,
osd.Fuel_Cd
union all
--
-- Ozone-Season-to-Date Data for the Non-Fuel-Specific Report Tables
--
select lst.Mon_Plan_Id,
0 as Fuel_Ind,
null as Rpt_Period_Id,
lst.Mon_Loc_Id,
lst.Calendar_Year,
( 'Ozone Season To Date' ) as Period,
osd.Fuel_Cd,
sum( case when osd.Op_Type_Cd in ( 'OPHOURS', 'OSHOURS' ) then osd.Op_Value end ) as Op_Hours,
null as Op_Days,
sum( case when osd.Op_Type_Cd in ( 'OPTIME', 'OSTIME') then osd.Op_Value end ) as Op_Time,
sum( case when osd.Op_Type_Cd in ( 'HIT', 'HITOS' ) then osd.Op_Value end ) as Hit,
null as So2m,
null as Co2m,
null as Noxr,
sum( case when osd.Op_Type_Cd in ( 'NOXM', 'NOXMOS' ) then osd.Op_Value end ) as Noxm,
null as Bco2
from lst
join OPERATING_SUPP_DATA osd
on osd.Mon_Loc_Id = lst.Mon_Loc_Id
and osd.Rpt_Period_Id = lst.Rpt_Period_Id
and osd.Fuel_Cd is null
join REPORTING_PERIOD prd
on prd.Rpt_Period_Id = osd.Rpt_Period_Id
and prd.Quarter in ( 2, 3 )
where case
when prd.Quarter = 2 and osd.Op_Type_Cd in ( 'HITOS', 'NOXMOS', 'OSHOURS', 'OSTIME' ) then 1
when prd.Quarter = 3 and osd.Op_Type_Cd in ( 'HIT', 'NOXM', 'OPHOURS', 'OPTIME' ) then 1
else 0
end = 1
-- Ensure that the location had an active ozone season program during Op Supp Data quarter.
and exists
(
select 1
from (
select loc.Mon_Loc_Id,
unp.Prg_Cd,
unp.Class_Cd,
case
when usc.Begin_Date is null
then unp.Unit_Monitor_Cert_Begin_Date
when unp.Unit_Monitor_Cert_Begin_Date is null
then usc.Begin_Date
when unp.Unit_Monitor_Cert_Begin_Date >= usc.Begin_Date
then unp.Unit_Monitor_Cert_Begin_Date
else usc.Begin_Date
end as Unit_Monitor_Cert_Begin_Date,
case
when usc.End_Date is null
then unp.End_Date
when unp.End_Date is null
then usc.End_Date
when unp.End_Date <= usc.End_Date
then unp.End_Date
else usc.End_Date
end as End_Date
from MONITOR_LOCATION loc
left join UNIT_STACK_CONFIGURATION usc
on usc.Stack_Pipe_Id = loc.Stack_Pipe_Id
left join STACK_PIPE stp
on stp.Stack_Pipe_Id = loc.Stack_Pipe_Id
join UNIT_PROGRAM unp
on unp.Unit_Id = coalesce( loc.Unit_Id, usc.Unit_Id )
join PROGRAM_CODE prc
on prc.Prg_Cd = unp.Prg_Cd
and prc.Os_Ind = 1
where loc.Mon_Loc_Id = lst.Mon_Loc_Id
) sub
where sub.Unit_Monitor_Cert_Begin_Date <= to_date( prd.Calendar_Year || '-09-30', 'yyyy-mm-dd' )
and coalesce( sub.End_Date, to_date( '9999-12-31', 'yyyy-mm-dd' ) ) >= to_date( prd.Calendar_Year || '-05-01', 'yyyy-mm-dd' )
)
-- Ensure that the OPERATING_SUPP_DATA quarter is after the "first" ozone quarter (usually 2) for the location and year.
and exists
(
select 1
from (
select dat.Mon_Loc_Id,
(
select case
when sub.Calendar_Year = lst.Calendar_Year then
case
when sub.Quarter < 2
then 2
else sub.Quarter
end
else 2
end
from REPORTING_PERIOD sub
where sub.Begin_Date = dat.First_Begin_Date
) as First_Quarter
from (
select mpl.Mon_Loc_Id,
min( prd.Begin_Date ) as First_Begin_Date
from MONITOR_PLAN_LOCATION mpl
join MONITOR_PLAN pln
on pln.Mon_Plan_Id = mpl.Mon_Plan_Id
join REPORTING_PERIOD prd
on prd.Rpt_Period_Id = pln.Begin_Rpt_Period_Id
where mpl.Mon_Loc_Id = lst.Mon_Loc_Id
group
by mpl.Mon_Loc_Id
) dat
) agg
where ( agg.First_Quarter <= prd.Quarter )
)
group
by lst.Mon_Plan_Id,
lst.Mon_Loc_Id,
lst.Calendar_Year,
osd.Fuel_Cd
union all
--
-- Year-to-Date Data for the Non-Fuel-Specific Report Tables
--
select lst.Mon_Plan_Id,
0 as Fuel_Ind,
null as Rpt_Period_Id,
mpl.Mon_Loc_Id,
prd.Calendar_Year,
( 'Year To Date' ) as Period,
osd.Fuel_Cd,
sum( case when osd.Op_Type_Cd = 'OPHOURS' then osd.Op_Value end ) as Op_Hours,
sum( case when osd.Op_Type_Cd = 'OPDAYS' then osd.Op_Value end ) as Op_Days,
sum( case when osd.Op_Type_Cd = 'OPTIME' then osd.Op_Value end ) as Op_Time,
sum( case when osd.Op_Type_Cd = 'HIT' then osd.Op_Value end ) as Hit,
sum( case when osd.Op_Type_Cd = 'SO2M' then osd.Op_Value end ) as So2m,
sum( case when osd.Op_Type_Cd = 'CO2M' then osd.Op_Value end ) as Co2m,
max
(
case
when osd.Op_Type_Cd = 'NOXRYTD' then
(
select (
select max( osd_sub.Op_Value )
from OPERATING_SUPP_DATA osd_sub
join REPORTING_PERIOD prd_sub
on prd_sub.Rpt_Period_Id = osd_sub.Rpt_Period_Id
where osd_sub.Mon_Loc_Id = sub.Mon_Loc_Id
and prd_sub.Calendar_Year = sub.Calendar_Year
and prd_sub.Quarter = sub.Quarter
and osd_sub.Op_Type_Cd = 'NOXRYTD'
)
from (
select osd.Mon_Loc_Id,
prd.Calendar_Year,
max( prd.Quarter ) as Quarter
from OPERATING_SUPP_DATA osd
join REPORTING_PERIOD prd
on prd.Rpt_Period_Id = osd.Rpt_Period_Id
where osd.Mon_Loc_Id = lst.Mon_Loc_Id
and prd.Calendar_Year = lst.Calendar_Year
group
by osd.Mon_Loc_Id,
prd.Calendar_Year
) sub
)
end
) as Noxr,
sum( case when osd.Op_Type_Cd = 'NOXM' then osd.Op_Value end ) as Noxm,
sum( case when osd.Op_Type_Cd = 'BCO2' then osd.Op_Value end ) as Bco2
from lst
join REPORTING_PERIOD prd
on prd.Rpt_Period_Id = lst.Rpt_Period_Id
join MONITOR_PLAN pln
on pln.Fac_Id = lst.Fac_Id
join EMISSION_EVALUATION ems
on ems.Mon_Plan_Id = pln.Mon_Plan_Id
and ems.Rpt_Period_Id = prd.Rpt_Period_Id
join MONITOR_PLAN_LOCATION mpl
on mpl.Mon_Plan_Id = pln.Mon_Plan_Id
and mpl.Mon_Loc_Id = lst.Mon_Loc_Id
join OPERATING_SUPP_DATA osd
on osd.Mon_Loc_Id = mpl.Mon_Loc_Id
and osd.Rpt_Period_Id = prd.Rpt_Period_Id
and osd.Op_Type_Cd in ( 'BCO2', 'CO2M', 'HIT', 'NOXM', 'NOXRYTD', 'OPDAYS', 'OPHOURS', 'OPTIME', 'SO2M' )
and osd.Fuel_Cd is null
join MONITOR_PLAN_REPORTING_FREQ frq
on frq.Mon_Plan_Id = pln.Mon_Plan_Id
and frq.Report_Freq_Cd = 'Q'
join REPORTING_PERIOD prb
on prb.Rpt_Period_Id = frq.Begin_Rpt_Period_Id
left join REPORTING_PERIOD pre
on pre.Rpt_Period_Id = frq.End_Rpt_Period_Id
where prb.Calendar_Year <= prd.Calendar_Year
and coalesce( pre.Calendar_Year, 9999 ) >= prd.Calendar_Year
group
by lst.Mon_Plan_Id,
mpl.Mon_Loc_Id,
prd.Calendar_Year,
osd.Fuel_Cd
) cmb
join MONITOR_PLAN pln
on pln.Mon_Plan_Id = cmb.Mon_Plan_Id
join PLANT fac
on fac.Fac_Id = pln.Fac_Id
join MONITOR_LOCATION loc
on loc.Mon_Loc_Id = cmb.Mon_Loc_Id
left join UNIT unt
on unt.Unit_Id = loc.Unit_Id
left join STACK_PIPE stp
on stp.Stack_Pipe_Id = loc.Stack_Pipe_Id
order
by Oris_Code,
case when cmb.Fuel_Cd is null then 0 else 1 end,
Location_Name,
cmb.Calendar_Year,
case when cmb.Period = 'Ozone Season To Date' then 1 when cmb.Period = 'Year To Date' then 2 else 0 end,
cmb.Period,
cmb.Fuel_Cd;
lgiannini1
commented
3 months ago The emissions printout report is correct when created from the official view, but the Year to Date values are missing from the Quarterly Emissions Summary Data table in the workspace view.
Report generated from workspace view for ORIS 3, 1,2,CS0AAN:
Report generated from official view for ORIS 3, 1,2,CS0AAN:
ergjustin
commented
3 months ago @lgiannini1 added the Year to Date row in Quarterly Emissions Summary Data table in workspace.
lgiannini1
commented
3 months ago Verified on dev using ORIS 3, 1,2,CS0AAN and ORIS 628, 4.
ibarra-michelle
commented
2 months ago Verified in test for ORIS 470 Location 1 for the browser and download report from the global/public view and logged in view.
(parent is #6110) The "Emissions Printout Report" needs to be updated to return the same data as the Emission Summary Report in 1.0.
Example 1.0 Filter Criteria:
Example 1.0 Report: Emissions Summary Report.pdf
Acceptance Criteria: In the Export & Report screen, users (public or non-public) should be able to filter on:
Single year or "All Years".
The header of the report should contain the title "Emissions Summary Report" and the date the report was generated.
The first section of the report should show the:
There should be a "Quarterly Emissions Summary Data" table/view that contains the following columns:
The values will come for OPERATING_SUPP_DATA rows that match on MON_LOC_ID, RPT_PERIOD_ID and a corresponding OP_TYPE_CD value.
There should be rows for each MP location that shows all the Quarter, Ozone Season to Date, and Year to Date values that have been reported for the location/year(s).
There should be a second table in the report for "Fuel Operating Hours" that has columns for:
There should be rows for each MP location that shows all the quarters that have been reported for the location/year(s).
Source Query Information
See Model Query without SQL Server/ECMPS 1.0 DB Dependencies