[SOLVED] INCLUDE does not work now in new version of WebFocus 7.6.6

Hi there,
I have a report that I've tested using WF v5.3.2 and it worked in Dev and Test environments. Now when I try to test it in the WF v7.6.6 environment I get an error message saying this:
Error occurred.
ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key &APPROOT/rms-gl/drv_rev.sql.

The SQL for the report is in drv_rev.sql located in rms-gl app in EDASERVE server. The &APPROOT is '/u04/ibi/v767/ibi/apps'
Your help is very much appreciated.

Here's the report code:


-*
ENGINE SQLORA SET DEFAULT_CONNECTION DW_DWGL
SQL SQLORA SET OPTIMIZATION ON
-*
-* General settings
SET XRETRIEVAL = ON
SET EMPTYREPORT = ON
SET ASNAMES = ON
-*
-* Default parameters
-DEFAULT &ADATE1=20050801
-DEFAULT &ADATE2=20050815
-DEFAULT &BEG_DATE=20050801
-DEFAULT &END_DATE=20050815
-SET &BEG_DATE = EDIT(&ADATE1,'99999999');
-SET &END_DATE = EDIT(&ADATE2,'99999999');
-DEFAULT &OUT_FMT=PDF
-*
-* Run query in database, obtaining Revenue information by:
-* - Product
-* - Account
-* and finally classifying it by Fee Group and Fee
-*
SQL SQLORA
  -INCLUDE &APPROOT/rms-gl/drv_rev.sql
  TABLE ON TABLE HOLD AS H_REVFEE FORMAT ALPHA
END
-RUN
-*
-* Determine sorting sequence for each Fee Group
DEFINE FILE H_REVFEE
FEE_GROUP_SEQ/I2=IF FEE_GROUP EQ 'DRIVER LICENSING'       THEN 1 ELSE
                 IF FEE_GROUP EQ 'DRIVER EXAMINATION'     THEN 2 ELSE
                 IF FEE_GROUP EQ 'VEHICLE VALIDATION'     THEN 3 ELSE
                 IF FEE_GROUP EQ 'VEHICLE REGISTRATION'   THEN 4 ELSE
                 IF FEE_GROUP EQ 'CONTRACTUAL AGREEMENTS' THEN 5 ELSE
                 IF FEE_GROUP EQ 'INFORMATION PRODUCTS'   THEN 6 ELSE
                 IF FEE_GROUP EQ 'OTHER'                  THEN 7 ELSE 99;
END
-* Produce final report
TABLE FILE H_REVFEE
PRINT
     FEE AS 'Regulatory Fee'
COMPUTE TOT_REVENUE/D16.2CNB = ( ENTERED_CR - ENTERED_DR ); AS 'Total,Revenue'
     TXN_VOL/D10 AS 'Transaction,Volume'
BY
     FEE_GROUP_SEQ NOPRINT
BY
     SEQ NOPRINT
ON FEE_GROUP_SEQ SUBHEAD
" "
"HEADING
"Driver Licensing and Vehicle Registration Revenue"
"Road User Safety Division - Ministry of Transportation"
"Reporting Period: <+0>&BEG_DATE <+0> to  <+0>&END_DATE "
" "
FOOTING BOTTOM
"Page ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL REVENUE'
ON TABLE PCHOLD FORMAT &OUT_FMT
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     LEFTMARGIN=0.500000,
     RIGHTMARGIN=0.500000,
     TOPMARGIN=0.250000,
     BOTTOMMARGIN=0.000000,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=10,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
     RIGHTGAP=0.125000,
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=DATA,
     SIZE=8,
     BACKCOLOR=( 'WHITE' RGB(210 240 250) ),
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=DATA,
     COLUMN=N4,
     COLOR='RED',
     WHEN=N4 LT 0,
$
TYPE=TITLE,
     SIZE=9,
     COLOR='WHITE',
     BACKCOLOR='NAVY',
     STYLE=-UNDERLINE+BOLD,
     JUSTIFY=CENTER,
$
TYPE=HEADING,
     JUSTIFY=CENTER,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=2,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=4,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=9,
     JUSTIFY=RIGHT,
$
TYPE=SUBHEAD,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N3,
     WRAP=7.000000,
$
ENDSTYLE
END
 

Thanks.

Sorry,...here's the SQL:

with dwglt_product_revenue as
(
  select p.product_code,  p.product_desc,
         substr(p.product_code, 1, 3) prod_type,
         substr(p.product_code, 4, 2) prod_category,
         substr(p.product_code, 6, 2) prod_attr_1,
         substr(p.product_code, 8, 2) prod_attr_2,
         substr(p.product_code, 10, 2) prod_attr_3,
         po.pgm_option_code, po.pgm_option_desc,
         ts.trans_status_code,
         /* Volume: Exclude transaction with the following sources:
                  * Spreadsheet,
                  * Manual,
                  * General
                  * Deferred
                  From the remaining transactions, consider only those with "Normal"
                  status that correspond to a "CR" operation.
         */
         sum(case
             when (tt.source_code not in ('Spreadsheet','Manual','0','4')) and
                  (ts.trans_status_code = 'TSNM') and
                  (nvl(r.entered_cr,0) > 0) then 1
             else 0 end) txn_vol,
         nvl(sum(r.entered_dr),0) entered_dr,
         nvl(sum(r.entered_cr),0) entered_cr
  from dwgl_owner.dwglf_revenue r
  join dwgl_owner.dwgld_product p on (r.gld_product_id = p.id)
  join dwgl_owner.dwgld_account a on (r.gld_account_id = a.id)
  join dwgl_owner.dwgld_calendar c on (r.gld_calendar_id = c.id)
  join dwgl_owner.dwgld_pgm_option po on (r.gld_pgm_option_id = po.id)
  join dwgl_owner.dwgld_trans_status ts on (r.gld_trans_status_id = ts.id)
  join dwgl_owner.dwgld_trans_type tt on (r.gld_trans_type_id = tt.id)
  where a.account_code like '44%' and
        a.account_code not in ('442514','442515','442517','442518','442551','442513','442560','442565','442580','442610') and
        c.calendar_date between to_date('&BEG_DATE','YYYYMMDD') and to_date('&END_DATE','YYYYMMDD')
  group by p.product_code,  p.product_desc,
           substr(p.product_code, 1, 3),
           substr(p.product_code, 4, 2),
           substr(p.product_code, 6, 2),
           substr(p.product_code, 8, 2),
           substr(p.product_code, 10, 2),
           po.pgm_option_code, po.pgm_option_desc,
           ts.trans_status_code
)
select cast(to_number(max(substr(fee, 1, instr(fee, '::', 1, 1)-1))) as number(4)) seq,
       max(substr(fee, instr(fee, '::', 1, 1)+2, (instr(fee, '::', 1, 2)-instr(fee, '::', 1, 1)-2))) fee_group,
       max(substr(fee, instr(fee, '::', 1, 2)+2)) fee,
       sum(txn_vol) txn_vol,
       sum(entered_dr) entered_dr,
       sum(entered_cr) entered_cr
from (
  /* Revenue by Product */
  select
         case
            when (prod_type = '100' and pgm_option_code not in ('03','04'))
                 then '100:RIVER LICENSING:RIVER''S LICENCE ORIGINAL/RENEWAL - VALIDATION FOR 5 YEARS'
            when (prod_type = '100' and pgm_option_code = '04')
                 then '110:RIVER LICENSING:RIVER LICENSE REINSTATEMENT AFTER SUSPENSION'
            when (prod_type = '100' and pgm_option_code = '03')
                 then '120:RIVER LICENSING::REPLACEMENT DRIVER''S LICENSE'
            when (prod_type = '102')
                 then '200:RIVER EXAMINATION:RIVER''S WRITTEN TEST '
            when (prod_type = '101')
                 then '210:RIVER EXAMINATION::LEVEL ONE ROAD TEST (M1,G1)'
            when (prod_type = '201' and prod_category = '01' and
                  prod_attr_1 not in ('23','51') and prod_attr_3 <> '11') or
                 (prod_type = '236' and prod_category = '01' and
                 prod_attr_1 not in ('03','06','10','13','15','16','92','96','97','L3','51')) or
                 (prod_type in ('263','264'))
                 then '300::VEHICLE VALIDATION::PASSENGER CAR VALIDATION FEE- SOUTHERN ONTARIO'
            when (prod_type = '201' and prod_category = '01' and
                  prod_attr_1 <> '23' and prod_attr_3 = '11')
                 then '305::VEHICLE VALIDATION::PASSENGER CAR VALIDATION FEE - NORTHERN ONTARIO'
            when (prod_type = '201' and prod_category = '01' and prod_attr_1 = '23')
                 then '310::VEHICLE VALIDATION::HISTORIC VALIDATION FEE'
            when (prod_type = '204' and prod_category = '01' and
                  prod_attr_1 not in ('14','51') and prod_attr_3 <> '11')
                 then '315::VEHICLE VALIDATION::MOTORCYCLE VALIDATION FEE - SOUTHERN ONTARIO'
            when (prod_type = '204' and prod_category = '01' and
                  prod_attr_1 not in ('14','51') and prod_attr_3 = '11')
                 then '320::VEHICLE VALIDATION::MOTORCYCLE VALIDATION FEE - NORTHERN ONTARIO'
            when (prod_type = '205' and prod_category = '01' and
                  prod_attr_1 not in ('14','51') and prod_attr_3 = '11')
                 then '325::VEHICLE VALIDATION::MOPED VALIDATION FEE (Motor-assisted bicycle)'
            when (prod_type = '202' and prod_category = '01' and
                  prod_attr_1 in ('10','29') and prod_attr_2 = '01' and prod_attr_3 <> '11')
                 then '330::VEHICLE VALIDATION::LIGHT COMMERCIAL MOTOR VEHICLE VALIDATIONS - GROSS WEIGHT LESS THEN 3,000 KG'
            when (prod_type = '202' and prod_category = '01' and
                  prod_attr_2 = '07' and prod_attr_3 <> '11')
                 then '335::VEHICLE VALIDATION::COMMERCIAL MOTOR VEHICLE - GROSS WEIGHT LESS THEN 3,000KG PERSONAL USE ONLY - SOUTHERN.ONT.'
            when (prod_type = '202' and prod_category = '01' and
                  prod_attr_2 in ('01','07') and prod_attr_3 = '11')
                 then '340::VEHICLE VALIDATION::COMMERCIAL MOTOR VEHICLE - GROSS WEIGHT LESS THEN 3,000KG PERSONAL USE ONLY - NORTHERN.ONT.'
            when (prod_type = '401')
                 then '345::VEHICLE VALIDATION::COMMERCIAL MOTOR VEHICLE - VALIDATION OVER 3,000 KG - IRP'
            when (prod_type = '202' and prod_category = '01' and
                  prod_attr_1 in ('10','11','12','29') and prod_attr_2 not in ('01','07')) or
                 (prod_type = '236' and prod_category = '01' and
                  prod_attr_1 in ('06','10','13'))
                 then '350::VEHICLE VALIDATION::COMMERCIAL MOTOR VEHICLE - VALIDATION OVER 3,000 KG - NON-IRP'
            when (prod_type = '202' and prod_category = '01' and prod_attr_1 = '20')
                 then '355::VEHICLE VALIDATION::FARM VEHICLE VALIDATION'
            when (prod_type in ('203','236') and prod_category = '01' and prod_attr_1 = '03')
                 then '360::VEHICLE VALIDATION::BUS VALIDATION FEE'
            when (prod_type = '203' and prod_category = '01' and prod_attr_1 = '38')
                 then '365::VEHICLE VALIDATION::SCHOOL BUS VALIDATION'
            when (prod_type = '206' and prod_category = '01' and prod_attr_1 = '43')
                 then '370::VEHICLE VALIDATION::TRAILER VALIDATION FEE'
            when (prod_type = '207' and prod_category = '01' and prod_attr_1 = '52')
                 then '375::VEHICLE VALIDATION::MOTORIZED SNOW VEHICLE ANNUAL VALIDATION'
            when (prod_type = '208' and prod_category = '01' and prod_attr_1 = '41')
                 then '380::VEHICLE VALIDATION::OFF ROAD VEHICLE VALIDATION'
            when (prod_type = '236' and prod_category = '01' and
                  prod_attr_1 in ('15','16','92','96','97','L3'))
                 then '385::VEHICLE VALIDATION:EALER PERMIT - MOTOR VEHICLE OR TRAILER VALIDATION'
            when (prod_type = '204' and prod_category = '01' and prod_attr_1 = '14')
                 then '390::VEHICLE VALIDATION:EALER PERMIT - MOTORCYCLE - MOPED VALIDATION'
            when (prod_type in ('201','202','203','204','205','236') and prod_category = '03' and
                  prod_attr_1 not in ('16','29','30','31','96','97'))
                 then '250::VEHICLE REGISTRATION::VEHICLE NUMBER PLATE'
            when (prod_type in ('201','202','203','204','205','236') and prod_category = '02' and
                  prod_attr_1 not in ('16','29','30','31','96','97','41','43','51'))
                 then '400::VEHICLE REGISTRATION::PERMIT ISSUE (MOTOR VEHICLE AND TRAILER)'
            when (prod_type in ('201','236') and prod_category in ('02','03') and
                  prod_attr_1 in ('16','96','97'))
                 then '405::VEHICLE REGISTRATION:EALER/SERVICE - VEHICLE PERMIT AND PLATE'
            when (prod_type in ('206','236') and prod_category in ('02','03') and
                  prod_attr_1 in ('43','49'))
                 then '410::VEHICLE REGISTRATION::VEHICLE PERMIT AND NUMBER PLATE FOR A TRAILER'
            when (prod_type = '207' and prod_category in ('02','03'))
                 then '415::VEHICLE REGISTRATION::VEHICLE PERMIT AND NUMBER PLATE FOR A MOTORIZED SNOW VEHICLE(REGISTRATION)'
            when (prod_type = '208' and prod_category in ('02','03') and
                  prod_attr_1 in ('41','49'))
                 then '420::VEHICLE REGISTRATION::VEHICLE PERMIT AND PLATE FOR AN OFF-ROAD VEHICLE'
            when (prod_type in ('201','202','236') and prod_category in ('01','02') and
                  prod_attr_1 = '51' and prod_attr_2 = '00' and prod_attr_3 = '00')
                 then '425::VEHICLE REGISTRATION::YEAR OF MANUFACTURE PLATES (YOM)'
            when (prod_type in ('201','202','204','236') and prod_category in ('02','03') and
                  prod_attr_1 in ('29','30','31')) or
                 (prod_type in ('258','259','260') and prod_category = '22')
                 then '430::VEHICLE REGISTRATION::OWN CHOICE PLATES (Plates and permit with requested number)-PLP'
            when (prod_type in ('258','259','260') and prod_category = '21')
                 then '435::VEHICLE REGISTRATION::OWN CHOICE PLATES (Plates and permit with requested number and graphic)'
            when (prod_type in ('200','201') and prod_category in ('02','15','28') and
                  prod_attr_1 in ('00','82','83','84','87'))
                 then '440::VEHICLE REGISTRATION::TEMPORARY PERMIT - MOTOR VEHICLE OR TRAILER'
            when (prod_type = '202' and prod_category = '28' and prod_attr_1 = '93')
                 then '445::VEHICLE REGISTRATION::TEMPORARY PERMIT - UNLADEN COMMERCIAL MOTOR VEHICLE OR LADEN/UNLADEN TRAILER'
            when (prod_type = '202' and prod_category = '28' and prod_attr_1 = '94')
                 then '450::VEHICLE REGISTRATION::TEMPORARY PERMIT - LADEN COMMERCIAL VEHICLE'
            when (prod_type = '202' and prod_category = '28' and prod_attr_1 = '95')
                 then '455::VEHICLE REGISTRATION::TEMPORARY PERMIT - COMBINATION OF COMM MOTOR VEHICLE AND TRAILER OR TRAILERS'
            when (prod_type = '360' and prod_category = '02')
                 then '460::VEHICLE REGISTRATION::O\O Annual Permit'
            when (prod_type = '360' and prod_category = '26')
                 then '465::VEHICLE REGISTRATION::O\O Project Permit'
            when (prod_type = '362' and prod_category = '28' and prod_attr_1 = '65')
                 then '470::VEHICLE REGISTRATION::Single Trip Permit Less than 100km'
            when (prod_type = '362' and prod_category = '28' and prod_attr_1 = '66')
                 then '475::VEHICLE REGISTRATION::Single Trip Permit  between 100km and  500km'
            when (prod_type = '362' and prod_category = '28' and prod_attr_1 = '67')
                 then '480::VEHICLE REGISTRATION::Single Trip Permit greater than  500km'
            when (prod_type = '362' and prod_category = '28' and prod_attr_1 = '68')
                 then '485::VEHICLE REGISTRATION::Single Trip Permit  over Dimensional'
            when (prod_type = '363')
                 then '490::VEHICLE REGISTRATION::Special Vehicle Configuration'
            when (prod_type = '364')
                 then '495::VEHICLE REGISTRATION::O/O Single Trip Permit Amendment'
            when (prod_type = '370')
                 then '496::VEHICLE REGISTRATION::TTA/MVTA Load Broker Certificate/Operating License'
            when (prod_type in ('560','561','562','564','565','567') and
                  prod_category in ('04','38'))
                 then '600::INFORMATION PRODUCTS:RIVER ABSTRACTS (DRIVER LICENSE HISTORY,DRIVER LICENSE SEARCH)'
            when (prod_type in ('531','532','533','534','566','568','569','579','580','581') and
                  prod_category in ('04','38'))
                 then '610::INFORMATION PRODUCTS::VEHICLE ABSTRACTS (VEHICLE HISTORY SEARCH)'
            when (prod_type = '302')
                 then '620::INFORMATION PRODUCTS::Commercial Vehicle Operating Record (CVOR) CERTIFICATE'
            when (prod_type in ('510','511') and prod_category = '04')
                 then '630::INFORMATION PRODUCTS::Commercial Vehicle Operating Record (CVOR) ABSTRACT'
            when (prod_type in ('340', '341','342','343','350','381','501','502'))
                 then '640::INFORMATION PRODUCTS::Carrier Abstract and Report'
            when (prod_type = '530')
                 then '650::INFORMATION PRODUCTS::Used Vehicle Information Package (UVIP)'
            when (prod_type in ('570','601'))
                 then '660::INFORMATION PRODUCTS::Miscellaneous Admin. Fee'
            when (prod_type in ('000'))
                 then '700::OTHER::Special Handling'
            else '* UNKNOWN *'
         end fee,
         txn_vol,
         entered_dr,
         entered_cr
  from dwglt_product_revenue
  union all
  /* Revenue by Account */
  select
         case account_code
           when '442551' then '500::CONTRACTUAL AGREEMENTS::SPLIT DRIVER LICENCE FEE (REVENUE FROM SERCO)'
           when '442514' then '510::CONTRACTUAL AGREEMENTS::MUNICIPAL PARKING TAG PROGRAM / MUNICIPAL DATA ACCESS'
           when '442515' then '520::CONTRACTUAL AGREEMENTS::MUNICIPAL RED LIGHT INITIATIVE'
           when '442517' then '670::INFORMATION PRODUCTS:L Check ( Telephone and Internet)'
           when '442518' then '680::INFORMATION PRODUCTS::IVR License Check'
           else '* UNKNOWN *'
         end fee,
         sum(case
             when (tt.source_code not in ('Spreadsheet','Manual','0','4')) and
                  (ts.trans_status_code = 'TSNM') and
                  (nvl(r.entered_cr,0) > 0) then 1
             else 0 end) txn_vol,
         nvl(sum(r.entered_dr),0) entered_dr,
         nvl(sum(r.entered_cr),0) entered_cr
  from dwgl_owner.dwglf_revenue r
  join dwgl_owner.dwgld_account a on (r.gld_account_id = a.id)
  join dwgl_owner.dwgld_calendar c on (r.gld_calendar_id = c.id)
  join dwgl_owner.dwgld_trans_status ts on (r.gld_trans_status_id = ts.id)
  join dwgl_owner.dwgld_trans_type tt on (r.gld_trans_type_id = tt.id)
  where a.account_code in ('442514','442515','442517','442518','442551') and
        c.calendar_date between to_date('&BEG_DATE','YYYYMMDD') and to_date('&END_DATE','YYYYMMDD')
  group by a.account_code,
           ts.trans_status_code
)
where fee <> '* UNKNOWN *'
group by fee
;
This message has been edited. Last edited by: Kerry, 

WF V7.7.03 
Platform: AIX 6.1 64-bit, WinXP, IE 8.0 
Output Formats: Html, Pdf, Excel

Posts: 71 | Registered: October 16, 2006

Darin Lee

Virtuoso

posted

Hide Post

I assume that you are running this fex from within MRE and you are wanting to -INCLUDE a procedure that exists in the server's app path. You should place -MRNOEDIT at the beginning of the line and just use the fex name. Like

-MRNOEDIT -INCLUDE drv_rev

This forces MRE to look outside of the MR environment on the server app path instead of the application directory structure within MRE.

However, I believe that the file extension must be .fex (even though the code does not need to be FOCUS code.)

Regards,

Darin

In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted

Show Post

GamP

Virtuoso

posted

Hide Post

Darin, a -INCLUDE file can be any extension, only if you do not specify a extension it has to be '.fex'. This also means that you cannot include a file without extension.
The file has to be on the server's search path, or else you have to specify the path where the file is located (as Adrian did with his &APPROOT).
So just the -MRNOEDIT in front of the -INCLUDE should do the trick.

GamP