-* File District_Org_Chart.fex
-SET &ECHO=ALL;
-*
-DEFAULT &STARTDATE = '2014/06/08';
-DEFAULT &ENDDATE = '2014/06/14';
-*-DEFAULT &DIVISION = '1D' ;
-*-DEFAULT &SUB_ASG = 'SHIFT 1' ;
-TYPE STARTDATE = &STARTDATE
-TYPE ENDDATE = &ENDDATE

-SET &BEG_DATE_ISSUED = &STARTDATE | ' 00:00:00.000';
-SET &END_DATE_ISSUED = &ENDDATE | ' 00:00:00.000';

ENGINE SQLORA SET CONNECTION_ATTRIBUTES EJSWAR
SQL SQLORA
select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER,
INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE,
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE,
OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID,
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID,OFFICERS.CAD_EMP_ID
from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES , OFFICERS
where INCIDENTS.INCIDENT_ID=INCIDENT_SUPPLEMENTS.INC_INCIDENT_ID and
INCIDENTS.INCIDENT_ID=OFFENSES.INC_INCIDENT_ID and
EMPLOYEES.EJS_EMP_ID=EMP_SERVICE_HISTORIES.EJS_EMP_ID and
INCIDENT_TYPE_CODES.CODE=INCIDENTS.ITC_CODE and
INCIDENT_SUPPLEMENTS.CREATOR_ID = EMPLOYEES.LOGIN_ID and
INCIDENTS.ITC_CODE = INCIDENT_TYPE_CODES.CODE AND
offenses.OFFNSE_CD_OFFENSE_STATUS_CODE = '03' and
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE = 'A'
and INCIDENT_SUPPLEMENTS.APPROVAL_DATE >= trunc(sysdate) -2 and INCIDENT_SUPPLEMENTS.APPROVAL_DATE < trunc[sysdate)
AND OFFICERS.OFFICER_ID=EMPLOYEES.OFFICER_ID
group by EMPLOYEES.LNAME,INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER,
INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE,
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE, EMPLOYEES.FNAME,
OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID,
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID,OFFICERS.CAD_EMP_ID;
-*and ROWNUM <= 10;



-*TABLE FILE SQLOUT
-*PRINT *
-*ON TABLE HOLD FORMAT ALPHA
-*END

TABLE
ON TABLE HOLD AS OFFNSDAT
END

-*RUN


-*EXIT

-RUN

-*?FF HOLD
-*-EXIT

-*-SET &LASTDT2= AYMD (&YYMD, -2, 'I8YYMD');


TABLE FILE OFFNSDAT
SUM
APPROVAL_DATE
INC_REPORT_NUMBER
CREATOR_ID
DESCRIPTION
OFFNSE_CD_OFFENSE_STATUS_CODE
ISC_STATUS_CODE
INCIDENT_ID
ITC_CODE
INC_INCIDENT_ID
LOGIN_ID
EJS_EMP_ID
EJS_EMP_ID
SRV_ASSIGN_CODE
OFFICER_ID
BY CAD_EMP_ID
-*WHERE INCIDENT_SUPPLEMENTS.APPROVAL_DATE GE &LASTDT2;
ON TABLE HOLD AS OFFNSDAT FORMAT FOCUS INDEX CAD_EMP_ID
END
-RUN



SET ALL=ON
-*JOIN CAD_EMP_ID IN OFFNSDAT TO ALL PerID IN TELESTAFFROSTER AS J1
JOIN PerID IN TELESTAFFROSTER TO CAD_EMP_ID IN OFFNSDAT AS J1

DEFINE FILE TELESTAFFROSTER
APPROVAL_DATE1/YYMD = HDATE(APPROVAL_DATE,'YYMD');


-*CURRDATE/YYMD=&YYMD;
-*TWODAYSPRIOR/YYMD=CURRDATE-2;
-*DISPLAYDATE/YYMD=IF APPROVAL_DATE1 GE TWODAYSPRIOR THEN APPROVAL_DATE1 ELSE '';
DISPLAYDATE/YYMD=APPROVAL_DATE1;



END



TABLE FILE  TELESTAFFROSTER
-*TABLE FILE  TELESTAFFROSTER
HEADING CENTER
"&SUB_ASG"

PRINT UPLASTNAME AS ''


DISPLAYDATE AS 'DATE CLOSED'

INC_REPORT_NUMBER AS 'CASE #'

DESCRIPTION
INCIDENT_ID NOPRINT

-*COMPUTE DATEISSUED/YYMD = HDATE(DATE_ISSUED,'YYMD'); AS 'DATE,ISSUED'


BY RANK_ORDER NOPRINT
BY RANK_ABRV AS ''
WHERE TSDIVISION EQ '&DIVISION';
WHERE SUB_ASSIGNMENT EQ '&SUB_ASG';
WHERE RANK_ABRV NE 'CPT' OR 'LT';

-*WHERE APPROVAL_DATE FROM '&BEG_DATE_ISSUED.EVAL' TO '&END_DATE_ISSUED.EVAL';


ON TABLE HOLD FORMAT HTMTABLE AS 'X1'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = overtime_blue.sty,
	 TOPMARGIN=0.0,
	 LEFTMARGIN=0.0,
	 RIGHTMARGIN=0.0,
	 BOTTOMMARGIN=0.0,
$
TYPE=HEADING,
     STYLE=BOLD,
$
TYPE=DATA,
     COLUMN=INC_REPORT_NUMBER,
     TARGET='_blank',
     URL=http://ejsp-prp:7778/reports/rwservlet?( \
     P_INC_ID=INCIDENT_ID \
     server='REP_EJSP01-PRP_OracleAS1012' \
     report='INCFULL.rep' \
     desformat='PDF' \
     destype='CACHE' \
     PARAMFORM='NO' \
     ENVID='V93' \
     userid='@EJSP' \
     ),
    ALT='Get Incident Report',

$

TYPE=REPORT,
  BACKCOLOR=RGB(153 204 255),
  FONT=ARIAL,
$
TYPE=REPORT
    COLUMN=3,
	WIDTH=1.2,
	WRAP=1.2,
	SIZE=9,
$
END

-HTMLFORM BEGIN
<html>
<body style="background-color:#99CCFF;")>
<div valign="top" style="width:150px;">
!IBI.FIL.X1;
</div>
</body>
</html>
-HTMLFORM END

 -*-? &
-*-EXIT
 

quote:

parameters tab from the HTML Composer

quote:

Originally posted by MartinY:
Depending on how you've included the variables and the controls, they may not bind automatically, do it manually : click on a control and drag it through the its proper variable.

quote:

Originally posted by MattC:
Did you link your run button to an external procedure?