This Is to Highlight Tools Used for Some Critical Reports Used by DWRR License Writers

Click here to go to the various reports, created using ReportSmith software:

Fac_Eflm.rpt

Fac_viol.rpt

Fac_viol_reg.rpt

Inspectors-Reg.rpt

MissingDMRprintnomvdt.rpt

Pcsaddr_pickfacility.rpt

This is to highlight tools used for some critical reports used by DWRR license writers. The reports, written in ReportSmith, can be found in the following directory: H:\L&W\PROGSERV\DMU\REPORTS\WATER\PCS\EFFLUENT

Report name: Efflim1

Tables: Too many to view, see the SQL

Sorting:

Derived fields:

PARAMETER_DF: PCS.PARAMETER_LIMITS.PRAM || ' ' || PCS.PRAM_LUP.PRAMD

SPACES_DF: ' '

Report variables:

SQL:

SELECT

PCS.PARAMETER_LIMITS.PRAM || ' ' || PCS.PRAM_LUP.PRAMD, ' ',

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."STNO", "PCS"."PERMIT_FACILITY"."CYNM", "PCS"."PERMIT_FACILITY"."FNMS", "PCS"."PERMIT_FACILITY"."RDF5", "PCS"."PIPE_SCHEDULE"."DSDG", "PCS"."PIPE_SCHEDULE"."PIPE", "PCS"."PIPE_SCHEDULE"."ALLP", "PCS"."PARAMETER_LIMITS"."PRAM", "PCS"."PARAMETER_LIMITS"."MLOC", "PCS"."PARAMETER_LIMITS"."ALLS", "PCS"."PARAMETER_LIMITS"."LCMX", "PCS"."PARAMETER_LIMITS"."LCAV", "PCS"."PARAMETER_LIMITS"."LCMN", "PCS"."PARAMETER_LIMITS"."LCUC", "PCS"."PARAMETER_LIMITS"."LQMX", "PCS"."PARAMETER_LIMITS"."LQAV", "PCS"."PARAMETER_LIMITS"."LQUC", "PCS"."PARAMETER_LIMITS"."LQAS", "PCS"."PARAMETER_LIMITS"."LQXS", "PCS"."PARAMETER_LIMITS"."LCMS", "PCS"."PARAMETER_LIMITS"."LCAS", "PCS"."PARAMETER_LIMITS"."LCXS", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."MVIO", "PCS"."MEASUREMENT_VIOLATIONS"."MQAV", "PCS"."MEASUREMENT_VIOLATIONS"."MQMX", "PCS"."MEASUREMENT_VIOLATIONS"."MCMN", "PCS"."MEASUREMENT_VIOLATIONS"."MCAV", "PCS"."MEASUREMENT_VIOLATIONS"."MCMX", "PCS"."MEASUREMENT_VIOLATIONS"."REXC", "PCS"."MEASUREMENT_VIOLATIONS"."NODI", "PCS"."PRAM_LUP"."PRAMD", "PCS"."UNITS_LUP"."DESCRIPTION", "UNITS_LUP2"."DESCRIPTION", "PCS"."STAT_BASE_LUP"."CODE", "PCS"."STAT_BASE_LUP"."DESCRIPTION", "STAT_BASE_LUP2"."CODE", "STAT_BASE_LUP2"."DESCRIPTION", "STAT_BASE_LUP3"."CODE", "STAT_BASE_LUP3"."DESCRIPTION", "STAT_BASE_LUP4"."CODE", "STAT_BASE_LUP4"."DESCRIPTION", "STAT_BASE_LUP5"."CODE", "STAT_BASE_LUP5"."DESCRIPTION"

FROM

"PCS"."PERMIT_FACILITY", "PCS"."PIPE_SCHEDULE", "PCS"."PARAMETER_LIMITS", "PCS"."MEASUREMENT_VIOLATIONS", "PCS"."PRAM_LUP", "PCS"."UNITS_LUP", "PCS"."UNITS_LUP" "UNITS_LUP2", "PCS"."STAT_BASE_LUP", "PCS"."STAT_BASE_LUP" "STAT_BASE_LUP2", "PCS"."STAT_BASE_LUP" "STAT_BASE_LUP3", "PCS"."STAT_BASE_LUP" "STAT_BASE_LUP4", "PCS"."STAT_BASE_LUP" "STAT_BASE_LUP5"

WHERE

((("PCS"."PERMIT_FACILITY"."NPID" LIKE '%' || UPPER('<FACILITY>') || '%') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVDT" BETWEEN <BEGIN_DATE, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')"> AND <END_DATE, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">)))

AND

("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."PARAMETER_LIMITS"."NPID" ) AND ("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."PIPE_SCHEDULE"."NPID" ) AND ("PCS"."PIPE_SCHEDULE"."DSDG" = "PCS"."PARAMETER_LIMITS"."DSDG" ) AND ("PCS"."PIPE_SCHEDULE"."PIPQ" = "PCS"."PARAMETER_LIMITS"."LIPQ" ) AND ("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID" ) AND ("PCS"."PARAMETER_LIMITS"."PRAM" = "PCS"."PRAM_LUP"."PRAM" ) AND ("PCS"."PARAMETER_LIMITS"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID" ) AND ("PCS"."PARAMETER_LIMITS"."NPID" = "PCS"."PIPE_SCHEDULE"."NPID" ) AND ("PCS"."PIPE_SCHEDULE"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID" ) AND ("PCS"."PARAMETER_LIMITS"."DSDG" = "PCS"."MEASUREMENT_VIOLATIONS"."DSDG" ) AND ("PCS"."PARAMETER_LIMITS"."LIPQ" = "PCS"."MEASUREMENT_VIOLATIONS"."VIPQ" ) AND ("PCS"."PARAMETER_LIMITS"."PRAM" = "PCS"."MEASUREMENT_VIOLATIONS"."VPRM" ) AND ("PCS"."PARAMETER_LIMITS"."MLOC" = "PCS"."MEASUREMENT_VIOLATIONS"."VMLO" ) AND ("PCS"."PARAMETER_LIMITS"."LNTP" = "PCS"."MEASUREMENT_VIOLATIONS"."VLIM" ) AND ("PCS"."PARAMETER_LIMITS"."MODN" = "PCS"."MEASUREMENT_VIOLATIONS"."VMOD" ) AND ("PCS"."PARAMETER_LIMITS"."SEAN" = "PCS"."MEASUREMENT_VIOLATIONS"."VSEA" ) AND ("PCS"."PARAMETER_LIMITS"."LCUC" = "PCS"."UNITS_LUP"."UNIT"(+) ) AND ("PCS"."PARAMETER_LIMITS"."LQUC" = "UNITS_LUP2"."UNIT"(+) ) AND ("PCS"."PARAMETER_LIMITS"."LQAS" = "PCS"."STAT_BASE_LUP"."CODE"(+) ) AND ("PCS"."PARAMETER_LIMITS"."LQXS" = "STAT_BASE_LUP2"."CODE"(+) ) AND ("PCS"."PARAMETER_LIMITS"."LCMS" = "STAT_BASE_LUP3"."CODE"(+) ) AND ("PCS"."PARAMETER_LIMITS"."LCAS" = "STAT_BASE_LUP4"."CODE"(+) ) AND ("PCS"."PARAMETER_LIMITS"."LCXS" = "STAT_BASE_LUP5"."CODE"(+) )

ORDER BY

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PIPE_SCHEDULE"."DSDG", "PCS"."PARAMETER_LIMITS"."PRAM", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT"

Report name: Effonly1

Tables:

Selections:

Sorting:

Derived Fields:

ADDRESS_DF (macro):

Sub ADDRESS_DF()

Dim aAddress as String

If LTrim(RTrim(Field("ANAM"))) > "" Then

aAddress = LTrim(RTrim(Field("ANAM")))

End If

MAJOR_DF (sql):

DECODE(PCS.PERMIT_FACILITY.MADI,'M','YES',null,'NO')

ACTIVE_DF (sql):

DECODE(IACC,null,'YES','A','YES','I','NO')

Report variables:

SQL:

SELECT

DECODE(PCS.PERMIT_FACILITY.MADI,'M','YES',null,'NO'), DECODE(IACC,null,'YES','A','YES','I','NO'),

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNMS", "PCS"."PERMIT_FACILITY"."OFFL", "PCS"."PERMIT_FACILITY"."RDF5", "PCS"."PERMIT_FACILITY"."ANAM", "PCS"."PERMIT_FACILITY"."AST1", "PCS"."PERMIT_FACILITY"."AST2", "PCS"."PERMIT_FACILITY"."ACTY", "PCS"."PERMIT_FACILITY"."ASTT", "PCS"."PERMIT_FACILITY"."AZIP", "PCS"."PERMIT_FACILITY"."MADI", "PCS"."PERMIT_FACILITY"."CYNM", "PCS"."PERMIT_FACILITY"."IACC", "PCS"."INSPECTOR_LUP"."INITIALS", "PCS"."INSPECTOR_LUP"."FULL_NAME", "PCS"."INSPECTOR_LUP"."WORK_PHONE", "PCS"."MEASUREMENT_VIOLATIONS"."NPID", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM", "PCS"."MEASUREMENT_VIOLATIONS"."VMLO", "PCS"."MEASUREMENT_VIOLATIONS"."VMOD", "PCS"."MEASUREMENT_VIOLATIONS"."VLIM", "PCS"."MEASUREMENT_VIOLATIONS"."VSEA", "PCS"."MEASUREMENT_VIOLATIONS"."VIPQ", "PCS"."MEASUREMENT_VIOLATIONS"."MVIO", "PCS"."MEASUREMENT_VIOLATIONS"."MQAV", "PCS"."MEASUREMENT_VIOLATIONS"."MQMX", "PCS"."MEASUREMENT_VIOLATIONS"."MCMN", "PCS"."MEASUREMENT_VIOLATIONS"."MCAV", "PCS"."MEASUREMENT_VIOLATIONS"."MCMX", "PCS"."MEASUREMENT_VIOLATIONS"."DMRR", "PCS"."MEASUREMENT_VIOLATIONS"."SNCE", "PCS"."MEASUREMENT_VIOLATIONS"."SNDE", "PCS"."MEASUREMENT_VIOLATIONS"."SRCE", "PCS"."MEASUREMENT_VIOLATIONS"."SRDE", "PCS"."MEASUREMENT_VIOLATIONS"."REXC", "PCS"."MEASUREMENT_VIOLATIONS"."RFRQ", "PCS"."MEASUREMENT_VIOLATIONS"."RSAM", "PCS"."MEASUREMENT_VIOLATIONS"."RUNT", "PCS"."MEASUREMENT_VIOLATIONS"."RCUN", "PCS"."MEASUREMENT_VIOLATIONS"."NODI", "PCS"."PRAM_LUP"."PRAMD"

FROM

"PCS"."PERMIT_FACILITY", "PCS"."INSPECTOR_LUP", "PCS"."MEASUREMENT_VIOLATIONS", "PCS"."PRAM_LUP"

WHERE

((("PCS"."PERMIT_FACILITY"."NPID" LIKE '%' || UPPER('<FACILITY_RV>') || '%') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVDT" BETWEEN <BEGIN_DATE, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')"> AND <END_DATE, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">)))

AND

("PCS"."PERMIT_FACILITY"."NPID" (+)= "PCS"."MEASUREMENT_VIOLATIONS"."NPID" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" = "PCS"."PRAM_LUP"."PRAM" ) AND ("PCS"."PERMIT_FACILITY"."RDF5" = "PCS"."INSPECTOR_LUP"."INITIALS"(+) )

ORDER BY

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT"

Report name: Pramlist

Tables:

Selections:

Sorting:

Derived fields:

Season (sql): SUBSTR(PCS.PARAMETER_LIMITS.ALLS, TO_NUMBER(SUBSTR(PCS.MEASUREMENT_VIOLATIONS.MVDT,1,2 )) ,1 )

Report Variables:

SQL statement:

SELECT

SUBSTR(PCS.PARAMETER_LIMITS.ALLS, TO_NUMBER(SUBSTR(PCS.MEASUREMENT_VIOLATIONS.MVDT,1,2 )) ,1 ) ,

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."OFFL", "PCS"."PERMIT_FACILITY"."SIC2", "PCS"."PERMIT_FACILITY"."IACC", "PCS"."PERMIT_FACILITY"."STNO", "PCS"."PERMIT_FACILITY"."FLOW", "PCS"."PERMIT_FACILITY"."MADI", "PCS"."PERMIT_FACILITY"."RDF5", "PCS"."MEASUREMENT_VIOLATIONS"."NPID", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM", "PCS"."MEASUREMENT_VIOLATIONS"."VLIM", "PCS"."MEASUREMENT_VIOLATIONS"."MVIO", "PCS"."MEASUREMENT_VIOLATIONS"."MQAV", "PCS"."MEASUREMENT_VIOLATIONS"."MQMX", "PCS"."MEASUREMENT_VIOLATIONS"."MCMN", "PCS"."MEASUREMENT_VIOLATIONS"."MCAV", "PCS"."MEASUREMENT_VIOLATIONS"."MCMX", "PCS"."MEASUREMENT_VIOLATIONS"."REXC", "PCS"."MEASUREMENT_VIOLATIONS"."NODI", "PCS"."PARAMETER_LIMITS"."ALLS", "PCS"."PARAMETER_LIMITS"."LCMX", "PCS"."PARAMETER_LIMITS"."LCAV", "PCS"."PARAMETER_LIMITS"."LCMN", "PCS"."PARAMETER_LIMITS"."LCUC", "PCS"."PARAMETER_LIMITS"."LQMX", "PCS"."PARAMETER_LIMITS"."LQAV", "PCS"."PARAMETER_LIMITS"."LQUC", "PCS"."PRAM_LUP"."PRAM", "PCS"."PRAM_LUP"."PRAMD"

FROM

"PCS"."PERMIT_FACILITY", "PCS"."MEASUREMENT_VIOLATIONS", "PCS"."PARAMETER_LIMITS", "PCS"."PRAM_LUP"

WHERE

((("PCS"."PERMIT_FACILITY"."NPID" = '<NPID>') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVDT" BETWEEN <Begindate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')"> AND <Enddate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">) AND

("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" IN( '00310', '81011', '81010', '50050', '00545', '00530'))))

AND

("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID"(+) ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" = "PCS"."PRAM_LUP"."PRAM" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."NPID" = "PCS"."PARAMETER_LIMITS"."NPID" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."DSDG" = "PCS"."PARAMETER_LIMITS"."DSDG" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" = "PCS"."PARAMETER_LIMITS"."PRAM" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VIPQ" = "PCS"."PARAMETER_LIMITS"."LIPQ" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VMOD" = "PCS"."PARAMETER_LIMITS"."MODN" )

ORDER BY

"PCS"."MEASUREMENT_VIOLATIONS"."MVDT"

Report name: Fac-viol

Tables:

Selections:

Sorting:

Derived fields:

Report variables:

SQL:

SELECT

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."OFFL", "PCS"."PERMIT_FACILITY"."SIC2", "PCS"."PERMIT_FACILITY"."IACC", "PCS"."PERMIT_FACILITY"."STNO", "PCS"."PERMIT_FACILITY"."FLOW", "PCS"."PERMIT_FACILITY"."MADI", "PCS"."PERMIT_FACILITY"."RDF5", "PCS"."PERMIT_FACILITY"."ORID", "PCS"."PARAMETER_LIMITS"."NPID", "PCS"."PARAMETER_LIMITS"."DSDG", "PCS"."PARAMETER_LIMITS"."LIPQ", "PCS"."PARAMETER_LIMITS"."PRAM", "PCS"."PARAMETER_LIMITS"."MLOC", "PCS"."PARAMETER_LIMITS"."SEAN", "PCS"."PARAMETER_LIMITS"."MODN", "PCS"."PARAMETER_LIMITS"."ALLS", "PCS"."PARAMETER_LIMITS"."LCMX", "PCS"."PARAMETER_LIMITS"."LCAV", "PCS"."PARAMETER_LIMITS"."LCMN", "PCS"."PARAMETER_LIMITS"."LQMX", "PCS"."PARAMETER_LIMITS"."LQAV", "PCS"."PARAMETER_LIMITS"."FRAN", "PCS"."PARAMETER_LIMITS"."SAMP", "PCS"."PARAMETER_LIMITS"."LNTP", "PCS"."PARAMETER_LIMITS"."LTYP", "PCS"."MEASUREMENT_VIOLATIONS"."NPID", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM", "PCS"."MEASUREMENT_VIOLATIONS"."VLIM", "PCS"."MEASUREMENT_VIOLATIONS"."MVIO", "PCS"."MEASUREMENT_VIOLATIONS"."MQAV", "PCS"."MEASUREMENT_VIOLATIONS"."MQMX", "PCS"."MEASUREMENT_VIOLATIONS"."MCMN", "PCS"."MEASUREMENT_VIOLATIONS"."MCAV", "PCS"."MEASUREMENT_VIOLATIONS"."MCMX", "PCS"."MEASUREMENT_VIOLATIONS"."REXC", "PCS"."MEASUREMENT_VIOLATIONS"."NODI", "PCS"."MEASUREMENT_VIOLATIONS"."RFRQ", "PCS"."MEASUREMENT_VIOLATIONS"."RSAM", "PCS"."PRAM_LUP"."PRAM", "PCS"."PRAM_LUP"."PRAMD"

FROM

"PCS"."PERMIT_FACILITY", "PCS"."PARAMETER_LIMITS", "PCS"."MEASUREMENT_VIOLATIONS", "PCS"."PRAM_LUP"

WHERE

((("PCS"."PERMIT_FACILITY"."NPID" = '<NPID>') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVIO" > 'E00') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVDT" BETWEEN <Begindate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')"> AND <Enddate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">)))

AND

("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID"(+) ) AND ("PCS"."PARAMETER_LIMITS"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID" ) AND ("PCS"."PARAMETER_LIMITS"."DSDG" = "PCS"."MEASUREMENT_VIOLATIONS"."DSDG" ) AND ("PCS"."PARAMETER_LIMITS"."PRAM" = "PCS"."MEASUREMENT_VIOLATIONS"."VPRM" ) AND ("PCS"."PARAMETER_LIMITS"."LIPQ" = "PCS"."MEASUREMENT_VIOLATIONS"."VIPQ" ) AND ("PCS"."PARAMETER_LIMITS"."MODN" = "PCS"."MEASUREMENT_VIOLATIONS"."VMOD" ) AND ("PCS"."PARAMETER_LIMITS"."MLOC" = "PCS"."MEASUREMENT_VIOLATIONS"."VMLO" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" = "PCS"."PRAM_LUP"."PRAM" )

ORDER BY

"PCS"."MEASUREMENT_VIOLATIONS"."MVDT"

Report name: Fac_Eflm.rpt

Tables:

Selections:

Sorting:

Derived fields:

Season (sql statement): SUBSTR(PCS.PARAMETER_LIMITS.ALLS, TO_NUMBER(SUBSTR(PCS.MEASUREMENT_VIOLATIONS.MVDT,1,2 )) ,1 )

Derived fields:

SQL:

SELECT

SUBSTR(PCS.PARAMETER_LIMITS.ALLS, TO_NUMBER(SUBSTR(PCS.MEASUREMENT_VIOLATIONS.MVDT,1,2 )) ,1 ) ,

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."OFFL", "PCS"."PERMIT_FACILITY"."SIC2", "PCS"."PERMIT_FACILITY"."IACC", "PCS"."PERMIT_FACILITY"."STNO", "PCS"."PERMIT_FACILITY"."FLOW", "PCS"."PERMIT_FACILITY"."MADI", "PCS"."PERMIT_FACILITY"."RDF5", "PCS"."MEASUREMENT_VIOLATIONS"."NPID", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM", "PCS"."MEASUREMENT_VIOLATIONS"."VMLO", "PCS"."MEASUREMENT_VIOLATIONS"."VMOD", "PCS"."MEASUREMENT_VIOLATIONS"."VLIM", "PCS"."MEASUREMENT_VIOLATIONS"."VIPQ", "PCS"."MEASUREMENT_VIOLATIONS"."MVIO", "PCS"."MEASUREMENT_VIOLATIONS"."MQAV", "PCS"."MEASUREMENT_VIOLATIONS"."MQMX", "PCS"."MEASUREMENT_VIOLATIONS"."MCMN", "PCS"."MEASUREMENT_VIOLATIONS"."MCAV", "PCS"."MEASUREMENT_VIOLATIONS"."MCMX", "PCS"."MEASUREMENT_VIOLATIONS"."REXC", "PCS"."MEASUREMENT_VIOLATIONS"."NODI", "PCS"."PARAMETER_LIMITS"."LIPQ", "PCS"."PARAMETER_LIMITS"."MLOC", "PCS"."PARAMETER_LIMITS"."MODN", "PCS"."PARAMETER_LIMITS"."LCMX", "PCS"."PARAMETER_LIMITS"."LCAV", "PCS"."PARAMETER_LIMITS"."LCMN", "PCS"."PARAMETER_LIMITS"."LCUC", "PCS"."PARAMETER_LIMITS"."LQMX", "PCS"."PARAMETER_LIMITS"."LQAV", "PCS"."PARAMETER_LIMITS"."LQUC", "PCS"."PRAM_LUP"."PRAM", "PCS"."PRAM_LUP"."PRAMD"

FROM

"PCS"."PERMIT_FACILITY", "PCS"."MEASUREMENT_VIOLATIONS", "PCS"."PARAMETER_LIMITS", "PCS"."PRAM_LUP"

WHERE

((("PCS"."PERMIT_FACILITY"."NPID" = '<NPID>') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVDT" BETWEEN <Begindate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')"> AND <Enddate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">)))

AND

("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."PARAMETER_LIMITS"."NPID"(+) ) AND ("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID"(+) ) AND ("PCS"."PARAMETER_LIMITS"."DSDG" = "PCS"."MEASUREMENT_VIOLATIONS"."DSDG" ) AND ("PCS"."PARAMETER_LIMITS"."PRAM" = "PCS"."MEASUREMENT_VIOLATIONS"."VPRM" ) AND ("PCS"."PARAMETER_LIMITS"."LIPQ" = "PCS"."MEASUREMENT_VIOLATIONS"."VIPQ" ) AND ("PCS"."PARAMETER_LIMITS"."MODN" = "PCS"."MEASUREMENT_VIOLATIONS"."VMOD" ) AND ("PCS"."PARAMETER_LIMITS"."MLOC" = "PCS"."MEASUREMENT_VIOLATIONS"."VMLO" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" = "PCS"."PRAM_LUP"."PRAM" )

ORDER BY

"PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM"

Screen shot of report:

Report name: Fac_Pram.rpt

Tables:

Selections:

Sorting:

Derived fields:

Report variables:

SQL:

SELECT

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."OFFL", "PCS"."PERMIT_FACILITY"."SIC2", "PCS"."PERMIT_FACILITY"."IACC", "PCS"."PERMIT_FACILITY"."STNO", "PCS"."PERMIT_FACILITY"."FLOW", "PCS"."PERMIT_FACILITY"."MADI", "PCS"."PERMIT_FACILITY"."RDF5", "PCS"."MEASUREMENT_VIOLATIONS"."NPID", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM", "PCS"."MEASUREMENT_VIOLATIONS"."VLIM", "PCS"."MEASUREMENT_VIOLATIONS"."MVIO", "PCS"."MEASUREMENT_VIOLATIONS"."MQAV", "PCS"."MEASUREMENT_VIOLATIONS"."MQMX", "PCS"."MEASUREMENT_VIOLATIONS"."MCMN", "PCS"."MEASUREMENT_VIOLATIONS"."MCAV", "PCS"."MEASUREMENT_VIOLATIONS"."MCMX", "PCS"."MEASUREMENT_VIOLATIONS"."REXC", "PCS"."MEASUREMENT_VIOLATIONS"."NODI", "PCS"."PRAM_LUP"."PRAM", "PCS"."PRAM_LUP"."PRAMD"

FROM

"PCS"."PERMIT_FACILITY", "PCS"."MEASUREMENT_VIOLATIONS", "PCS"."PRAM_LUP"

WHERE

((("PCS"."PERMIT_FACILITY"."NPID" = '<NPID>') AND

("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" = '<ParameterCode>') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVDT" BETWEEN <Begindate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')"> AND <Enddate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">)))

AND

("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID"(+) ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" = "PCS"."PRAM_LUP"."PRAM" )

ORDER BY

"PCS"."MEASUREMENT_VIOLATIONS"."MVDT"

Fac_viol.rpt

Tables:

Selections:

Sorting:

Derived fields:

None

Report variables:

SQL:

SELECT

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."OFFL", "PCS"."PERMIT_FACILITY"."SIC2", "PCS"."PERMIT_FACILITY"."IACC", "PCS"."PERMIT_FACILITY"."STNO", "PCS"."PERMIT_FACILITY"."FLOW", "PCS"."PERMIT_FACILITY"."MADI", "PCS"."PERMIT_FACILITY"."RDF5", "PCS"."PERMIT_FACILITY"."ORID", "PCS"."PARAMETER_LIMITS"."NPID", "PCS"."PARAMETER_LIMITS"."DSDG", "PCS"."PARAMETER_LIMITS"."LIPQ", "PCS"."PARAMETER_LIMITS"."PRAM", "PCS"."PARAMETER_LIMITS"."MLOC", "PCS"."PARAMETER_LIMITS"."SEAN", "PCS"."PARAMETER_LIMITS"."MODN", "PCS"."PARAMETER_LIMITS"."ALLS", "PCS"."PARAMETER_LIMITS"."LCMX", "PCS"."PARAMETER_LIMITS"."LCAV", "PCS"."PARAMETER_LIMITS"."LCMN", "PCS"."PARAMETER_LIMITS"."LQMX", "PCS"."PARAMETER_LIMITS"."LQAV", "PCS"."PARAMETER_LIMITS"."FRAN", "PCS"."PARAMETER_LIMITS"."SAMP", "PCS"."PARAMETER_LIMITS"."LNTP", "PCS"."PARAMETER_LIMITS"."LTYP", "PCS"."MEASUREMENT_VIOLATIONS"."NPID", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM", "PCS"."MEASUREMENT_VIOLATIONS"."VLIM", "PCS"."MEASUREMENT_VIOLATIONS"."MVIO", "PCS"."MEASUREMENT_VIOLATIONS"."MQAV", "PCS"."MEASUREMENT_VIOLATIONS"."MQMX", "PCS"."MEASUREMENT_VIOLATIONS"."MCMN", "PCS"."MEASUREMENT_VIOLATIONS"."MCAV", "PCS"."MEASUREMENT_VIOLATIONS"."MCMX", "PCS"."MEASUREMENT_VIOLATIONS"."REXC", "PCS"."MEASUREMENT_VIOLATIONS"."NODI", "PCS"."MEASUREMENT_VIOLATIONS"."RFRQ", "PCS"."MEASUREMENT_VIOLATIONS"."RSAM", "PCS"."PRAM_LUP"."PRAM", "PCS"."PRAM_LUP"."PRAMD"

FROM

"PCS"."PERMIT_FACILITY", "PCS"."PARAMETER_LIMITS", "PCS"."MEASUREMENT_VIOLATIONS", "PCS"."PRAM_LUP"

WHERE

((("PCS"."PERMIT_FACILITY"."NPID" = '<NPID>') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVIO" > 'E00') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVDT" BETWEEN <Begindate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')"> AND <Enddate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">)))

AND

("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID"(+) ) AND ("PCS"."PARAMETER_LIMITS"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID" ) AND ("PCS"."PARAMETER_LIMITS"."DSDG" = "PCS"."MEASUREMENT_VIOLATIONS"."DSDG" ) AND ("PCS"."PARAMETER_LIMITS"."PRAM" = "PCS"."MEASUREMENT_VIOLATIONS"."VPRM" ) AND ("PCS"."PARAMETER_LIMITS"."LIPQ" = "PCS"."MEASUREMENT_VIOLATIONS"."VIPQ" ) AND ("PCS"."PARAMETER_LIMITS"."MODN" = "PCS"."MEASUREMENT_VIOLATIONS"."VMOD" ) AND ("PCS"."PARAMETER_LIMITS"."MLOC" = "PCS"."MEASUREMENT_VIOLATIONS"."VMLO" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" = "PCS"."PRAM_LUP"."PRAM"(+) )

ORDER BY

"PCS"."MEASUREMENT_VIOLATIONS"."MVDT"

Screen shot of report:

Fac_viol_reg.rpt

This report is much like fac_viol.rpt but it is grouped by regional office and shows facilities with measurement violations associated with the regional office.

Tables: same as fac_viol.rpt

Selections:

Sorting:

Derived fields: none

Report variables:

SQL:

SELECT

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."OFFL", "PCS"."PERMIT_FACILITY"."SIC2", "PCS"."PERMIT_FACILITY"."IACC", "PCS"."PERMIT_FACILITY"."STNO", "PCS"."PERMIT_FACILITY"."FLOW", "PCS"."PERMIT_FACILITY"."MADI", "PCS"."PERMIT_FACILITY"."RDF5", "PCS"."PERMIT_FACILITY"."ORID", "PCS"."PERMIT_FACILITY"."SUBR", "PCS"."PERMIT_FACILITY"."FNMS", "PCS"."INSPECTOR_LUP"."INITIALS", "PCS"."INSPECTOR_LUP"."FULL_NAME", "PCS"."INSPECTOR_LUP"."WORK_PHONE", "PCS"."PARAMETER_LIMITS"."NPID", "PCS"."PARAMETER_LIMITS"."DSDG", "PCS"."PARAMETER_LIMITS"."LIPQ", "PCS"."PARAMETER_LIMITS"."PRAM", "PCS"."PARAMETER_LIMITS"."MLOC", "PCS"."PARAMETER_LIMITS"."SEAN", "PCS"."PARAMETER_LIMITS"."MODN", "PCS"."PARAMETER_LIMITS"."ALLS", "PCS"."PARAMETER_LIMITS"."LCMX", "PCS"."PARAMETER_LIMITS"."LCAV", "PCS"."PARAMETER_LIMITS"."LCMN", "PCS"."PARAMETER_LIMITS"."LQMX", "PCS"."PARAMETER_LIMITS"."LQAV", "PCS"."PARAMETER_LIMITS"."FRAN", "PCS"."PARAMETER_LIMITS"."SAMP", "PCS"."PARAMETER_LIMITS"."LNTP", "PCS"."PARAMETER_LIMITS"."LTYP", "PCS"."MEASUREMENT_VIOLATIONS"."NPID", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM", "PCS"."MEASUREMENT_VIOLATIONS"."VLIM", "PCS"."MEASUREMENT_VIOLATIONS"."MVIO", "PCS"."MEASUREMENT_VIOLATIONS"."MQAV", "PCS"."MEASUREMENT_VIOLATIONS"."MQMX", "PCS"."MEASUREMENT_VIOLATIONS"."MCMN", "PCS"."MEASUREMENT_VIOLATIONS"."MCAV", "PCS"."MEASUREMENT_VIOLATIONS"."MCMX", "PCS"."MEASUREMENT_VIOLATIONS"."REXC", "PCS"."MEASUREMENT_VIOLATIONS"."NODI", "PCS"."MEASUREMENT_VIOLATIONS"."RFRQ", "PCS"."MEASUREMENT_VIOLATIONS"."RSAM", "PCS"."PRAM_LUP"."PRAM", "PCS"."PRAM_LUP"."PRAMD"

FROM

"PCS"."PERMIT_FACILITY", "PCS"."INSPECTOR_LUP", "PCS"."PARAMETER_LIMITS", "PCS"."MEASUREMENT_VIOLATIONS", "PCS"."PRAM_LUP"

WHERE

((("PCS"."PERMIT_FACILITY"."SUBR" = '<Region>') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVIO" > 'E00') AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVDT" BETWEEN <Begindate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')"> AND <Enddate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">) AND

("PCS"."PERMIT_FACILITY"."RDF5" > 'OBD')))

AND

("PCS"."PERMIT_FACILITY"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID"(+) ) AND ("PCS"."PERMIT_FACILITY"."RDF5" = "PCS"."INSPECTOR_LUP"."INITIALS"(+) ) AND ("PCS"."PARAMETER_LIMITS"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID" ) AND ("PCS"."PARAMETER_LIMITS"."DSDG" = "PCS"."MEASUREMENT_VIOLATIONS"."DSDG" ) AND ("PCS"."PARAMETER_LIMITS"."PRAM" = "PCS"."MEASUREMENT_VIOLATIONS"."VPRM" ) AND ("PCS"."PARAMETER_LIMITS"."LIPQ" = "PCS"."MEASUREMENT_VIOLATIONS"."VIPQ" ) AND ("PCS"."PARAMETER_LIMITS"."MODN" = "PCS"."MEASUREMENT_VIOLATIONS"."VMOD" ) AND ("PCS"."PARAMETER_LIMITS"."MLOC" = "PCS"."MEASUREMENT_VIOLATIONS"."VMLO" ) AND ("PCS"."MEASUREMENT_VIOLATIONS"."VPRM" = "PCS"."PRAM_LUP"."PRAM" )

ORDER BY

"PCS"."PERMIT_FACILITY"."RDF5", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM"

Screen shot of report:

Inspectors-Reg.rpt

This report is found in the FACILITY subdirectory, not in EFFLUENT.It appears as though it is much like the Active facilities report, but is parameterized to group by regional office.

Tables:

Selections:

Sorting:

Derived fields: none

Report variables:

SQL:

SELECT

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."OFFL", "PCS"."PERMIT_FACILITY"."TELE", "PCS"."PERMIT_FACILITY"."SIC2", "PCS"."PERMIT_FACILITY"."IACC", "PCS"."PERMIT_FACILITY"."STNO", "PCS"."PERMIT_FACILITY"."SUBR", "PCS"."PERMIT_FACILITY"."RWAT", "PCS"."PERMIT_FACILITY"."MNAM", "PCS"."PERMIT_FACILITY"."MST1", "PCS"."PERMIT_FACILITY"."MST2", "PCS"."PERMIT_FACILITY"."MCTY", "PCS"."PERMIT_FACILITY"."MSTT", "PCS"."PERMIT_FACILITY"."MZIP", "PCS"."PERMIT_FACILITY"."MADI", "PCS"."PERMIT_FACILITY"."ACTY", "PCS"."PERMIT_FACILITY"."CYNM", "PCS"."PERMIT_FACILITY"."CITY", "PCS"."PERMIT_FACILITY"."RDF5"

FROM

"PCS"."PERMIT_FACILITY"

WHERE

((("PCS"."PERMIT_FACILITY"."IACC" = 'A') AND

("PCS"."PERMIT_FACILITY"."SUBR" = '<Region>')))

ORDER BY

"PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."RDF5"

Screen shot of report:

MissingDMRprintnomvdt.rpt

This report is back over in the EFFLUENT subdirectory, parallel with FACILITY.

Selections:

Sorting:

Derived fields: none

Report variables:

Report Grouping:

SQL:

SELECT

"PCS"."DMR_PRINT_PARAMETER"."NPID", "PCS"."DMR_PRINT_PARAMETER"."END_DATE", "PCS"."DMR_PRINT_PARAMETER"."PERIOD", "PCS"."DMR_PRINT_PARAMETER"."DSDG", "PCS"."DMR_PRINT_PARAMETER"."MONITOR_LOCATION", "PCS"."DMR_PRINT_PARAMETER"."PRAM", "PCS"."MEASUREMENT_VIOLATIONS"."NPID", "PCS"."MEASUREMENT_VIOLATIONS"."MVDT", "PCS"."MEASUREMENT_VIOLATIONS"."DSDG", "PCS"."MEASUREMENT_VIOLATIONS"."VMLO", "PCS"."MEASUREMENT_VIOLATIONS"."VMOD", "PCS"."MEASUREMENT_VIOLATIONS"."MVIO", "PCS"."MEASUREMENT_VIOLATIONS"."NODI", "PCS"."MEASUREMENT_VIOLATIONS"."VPRM", "PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."IACC", "PCS"."PERMIT_FACILITY"."STNO", "PCS"."PERMIT_FACILITY"."SUBR", "PCS"."PERMIT_FACILITY"."RDF5"

FROM

"PCS"."DMR_PRINT_PARAMETER", "PCS"."MEASUREMENT_VIOLATIONS", "PCS"."PERMIT_FACILITY"

WHERE

((("PCS"."PERMIT_FACILITY"."SUBR" = '<Region>') AND

("PCS"."DMR_PRINT_PARAMETER"."END_DATE" <= <Date, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">) AND

("PCS"."MEASUREMENT_VIOLATIONS"."MVDT" IS NULL) AND

("PCS"."PERMIT_FACILITY"."RDF5" NOT IN ( 'OBD', 'JSC'))))

AND

("PCS"."DMR_PRINT_PARAMETER"."NPID" = "PCS"."PERMIT_FACILITY"."NPID" ) AND ("PCS"."DMR_PRINT_PARAMETER"."NPID" = "PCS"."MEASUREMENT_VIOLATIONS"."NPID"(+) ) AND ("PCS"."DMR_PRINT_PARAMETER"."END_DATE" = "PCS"."MEASUREMENT_VIOLATIONS"."MVDT"(+) ) AND ("PCS"."DMR_PRINT_PARAMETER"."DSDG" = "PCS"."MEASUREMENT_VIOLATIONS"."DSDG"(+) ) AND ("PCS"."DMR_PRINT_PARAMETER"."MONITOR_LOCATION" = "PCS"."MEASUREMENT_VIOLATIONS"."VMLO"(+) )

ORDER BY

"PCS"."DMR_PRINT_PARAMETER"."NPID", "PCS"."DMR_PRINT_PARAMETER"."END_DATE", "PCS"."DMR_PRINT_PARAMETER"."DSDG", "PCS"."DMR_PRINT_PARAMETER"."PRAM"

Screen shot of report:

Pcsaddr_pickfacility.rpt

Tables:

Selections:

Sorting:

Derived fields: none

Report variables:

Report grouping:

SQL:

SELECT

"PCS"."PERMIT_FACILITY"."NPID", "PCS"."PERMIT_FACILITY"."FNML", "PCS"."PERMIT_FACILITY"."FNMS", "PCS"."PERMIT_FACILITY"."OFFL", "PCS"."PERMIT_FACILITY"."MNAM", "PCS"."PERMIT_FACILITY"."MST1", "PCS"."PERMIT_FACILITY"."MST2", "PCS"."PERMIT_FACILITY"."MCTY", "PCS"."PERMIT_FACILITY"."MSTT", "PCS"."PERMIT_FACILITY"."MZIP", "PCS"."PERMIT_FACILITY"."ANAM", "PCS"."PERMIT_FACILITY"."AST1", "PCS"."PERMIT_FACILITY"."AST2", "PCS"."PERMIT_FACILITY"."ACTY", "PCS"."PERMIT_FACILITY"."ASTT", "PCS"."PERMIT_FACILITY"."AZIP", "PCS"."PERMIT_FACILITY"."RNAM", "PCS"."PERMIT_FACILITY"."RST1", "PCS"."PERMIT_FACILITY"."RST2", "PCS"."PERMIT_FACILITY"."RCTY", "PCS"."PERMIT_FACILITY"."RSTT", "PCS"."PERMIT_FACILITY"."RZIP", "PCS"."PERMIT_FACILITY"."RTEL", "PCS"."PERMIT_FACILITY"."ONAM", "PCS"."PERMIT_FACILITY"."OST1", "PCS"."PERMIT_FACILITY"."OST2", "PCS"."PERMIT_FACILITY"."OCTY", "PCS"."PERMIT_FACILITY"."OSTT", "PCS"."PERMIT_FACILITY"."OZIP", "PCS"."PERMIT_FACILITY"."OTEL", "PCS"."PERMIT_FACILITY"."ENAM", "PCS"."PERMIT_FACILITY"."EST1", "PCS"."PERMIT_FACILITY"."EST2", "PCS"."PERMIT_FACILITY"."ECTY", "PCS"."PERMIT_FACILITY"."ESTT", "PCS"."PERMIT_FACILITY"."EZIP", "PCS"."PERMIT_FACILITY"."ETEL", "PCS"."PERMIT_FACILITY"."SNAM", "PCS"."PERMIT_FACILITY"."SST1", "PCS"."PERMIT_FACILITY"."SST2", "PCS"."PERMIT_FACILITY"."SCTY", "PCS"."PERMIT_FACILITY"."SSTT", "PCS"."PERMIT_FACILITY"."SZIP", "PCS"."PERMIT_FACILITY"."RDF5", "PCS"."PERMIT_FACILITY"."TELE", "PCS"."PERMIT_FACILITY"."RWAT"

FROM

"PCS"."PERMIT_FACILITY"

WHERE

((("PCS"."PERMIT_FACILITY"."NPID" = '<NPID>') AND

("PCS"."PERMIT_FACILITY"."IACC" = 'A')))

ORDER BY

"PCS"."PERMIT_FACILITY"."RDF5", "PCS"."PERMIT_FACILITY"."NPID"

Screen shot of report: