/* fs.0002 sd02d357.0053ewk8      1488.480  2005-03-14 05:53:03  28166  185175    189631  185728    737090 */
set linesize 1000;
/*set heading off;*/
column FILE_NAME format A20
/*column FILE_SIZE_IN_BYTES format ZZZ*/
/*column START_TIME format A35*/
SELECT /*+ RULE; */ SMDF.FILE_NAME, 
                    /* SMDT.DATA_TIER, SMAF.FAMILY, SMAF.APPL_NAME, SMAF.VERSION, */
                    /* SMPROC.PROCESS_TYPE, SMPDS.PHYSICAL_DATASTREAM_NAME, */
                    /* SMLDS.LOGICAL_DATASTREAM_NAME, SMDF.FILE_ID, */
/*                   ( SELECT COUNT(SMR.RUN_NUMBER) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) RUNCOUNT, */
	            SMDF.FILE_SIZE_IN_BYTES/1000 AS FILE_SIZE_IN_KB,
/* */		    TO_CHAR(SMDF.START_TIME,'YYYY-MM-DD HH24:MI:SS') AS FILE_DATE,
		    SMDF.EVENT_COUNT,
/*                    SMDF.FIRST_EVENT_NUMBER, */
                   ( SELECT MIN(SMR.RUN_NUMBER) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) FIRSTRUN, 
                    SMDF.FIRST_EVENT_NUMBER, 
/*                    SMDF.LAST_EVENT_NUMBER,*/
                   ( SELECT MAX(SMR.RUN_NUMBER) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) LASTRUN,
                    SMDF.LAST_EVENT_NUMBER
	        /*, SMDF.FILE_PARTITION, */
/*
                   ( SELECT MIN(SMRT.RUN_TYPE) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR, RUN_TYPES SMRT 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID AND 
                     SMR.RUN_TYPE_ID = SMRT.RUN_TYPE_ID) MINRUNTYPE, 
                   ( SELECT COUNT(DISTINCT SMRT.RUN_TYPE) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR, RUN_TYPES SMRT 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID AND SMR.RUN_TYPE_ID = SMRT.RUN_TYPE_ID) RUNTYPECOUNT, 
                     SMFCST.FILE_CONTENT_STATUS, 
                     DECODE( (SELECT 1 FROM DUAL 
                              WHERE ( SMDF.FILE_CONTENT_STATUS_ID IS NULL OR 
                                      SMDF.FILE_CONTENT_STATUS_ID = (SELECT FILE_CONTENT_STATUS_ID FROM FILE_CONTENT_STATUSES 
                                                                     WHERE FILE_CONTENT_STATUS = 'good')) AND 
                              EXISTS ( SELECT 1 FROM DATA_FILE_LOCATIONS SMDFL, DATA_STORAGE_LOCATIONS SMDSL 
                                       WHERE SMDFL.FILE_ID = SMDF.FILE_ID AND SMDFL.LOCATION_ID = SMDSL.LOCATION_ID AND 
                                       ( SMDSL.LOCATION_TYPE != 'tape' OR 
                                         EXISTS (SELECT 1 FROM VOLUMES SMV 
                                                 WHERE SMV.VOLUME_ID = SMDFL.VOLUME_ID AND SMV.VOLUME_STATUS = 'online')))),1,'Yes','No') DELIVERABLE,
	            ,SMDF.START_TIME
*/
/*		     ,SMDF.END_TIME,*/
                     FROM DATA_FILES            SMDF, APPLICATION_FAMILIES SMAF, PHYSICAL_DATASTREAMS SMPDS,
                          LOGICAL_DATASTREAMS   SMLDS, PROCESSES SMPROC, PROCESS_TYPES SMPT, 
                          DATA_TIERS            SMDT, 
                          FILE_CONTENT_STATUSES SMFCST  
/* */
                     WHERE SMDF.APPL_FAMILY_ID = SMAF.APPL_FAMILY_ID(+) AND SMDF.STREAM_ID = SMPDS.STREAM_ID(+) AND 
                           SMDF.DATA_TIER_ID = SMDT.DATA_TIER_ID(+) AND SMPDS.LOGICAL_DATASTREAM_ID = SMLDS.LOGICAL_DATASTREAM_ID(+) AND 
                           SMDF.PROCESS_ID = SMPROC.PROCESS_ID(+) AND SMPROC.PROCESS_TYPE = SMPT.PROCESS_TYPE(+)AND 
                           SMDF.FILE_CONTENT_STATUS_ID = SMFCST.FILE_CONTENT_STATUS_ID AND
/*			   SMDF.FILE_NAME = 'gh03056b.0111stn7' AND*/
/* */
                           SMDF.FILE_ID IN ( SELECT FILE_ID 
                                             FROM DATA_FILES DF JOIN DATA_FILES_PARAM_VALUES DFPV USING(FILE_ID) JOIN PARAM_VALUES PV 
                                             USING(PARAM_VALUE_ID) JOIN PARAM_TYPES PT USING(PARAM_TYPE_ID) JOIN PARAM_CATEGORIES PC 
                                             USING(PARAM_CATEGORY_ID) 
                                             WHERE PC.PARAM_CATEGORY = 'cdf' AND PT.PARAM_TYPE = 'dataset' AND PV.PARAM_VALUE = 'XYXYXY')
/* */
                     ORDER BY SMDF.FILE_NAME ;
exit;
