Sybernet / Supplied Procedures Reference
Release 3.00
Jan 1, 2007 		backwards forwards

SP_HTML_FILTER

This procedure allows you to create a basic form screen for your procedure. Most of the reports we create require some sort of user input to specify such things as employee number, department number, or period end date. sp_html_filter probably handles 90% percent of the things you might want to specify. Of course if there is an option that sp_html_filter does not recognize and is not documented below, you can't use sp_html_filter.

No one thing in Sybernet is all that complicated. What is complicated is mixing all the features that are available into an application. Did I set the format correctly? Did I set the content-type right? Did I call sp_cron_insert and sp_cron_insert_parmater correctly? Did I call sp_print with the required information?

For each input field, you will pass to that parameter a value of TRUE or FALSE. A value of TRUE means you want to include that parameter in your form screen; a value of FALSE means you don't. A value of FALSE also means sp_html_filter will not pass that parameter name to your procedure. The syntax also allows you to specify a default value when that makes sense. 

    <name> => -- ' ----- TRUE --------------------------------- ' ----------------------------------
                     |           |     |                   |
                     |           |     |                   |
                     '-- FALSE --'     '-- ( <default> ) --'

There are cases when it makes sense to pass a default value when passing FALSE to this parameter, but most of the time you wouldn't do this.

Many of the input fields created by sp_html_filter are drop-down lists. In general, the user is presented with a list of descriptions while your procedure will receive the actual value for this parameter name.

sp_html_filter also supports row level security with the SECURITYTYPE parameter. SECURITYTYPE can be PUBLIC or SECURED. PUBLIC (the default) means all departments are displayed. SECURED means only the departments that this user can see are displayed. If you specify SECURED, however, it is your responsibility to implement this in your procedure because the user can specify all departments, divisions, or groups.

Syntax

SP_HTML_FILTER
(
    PROCNAME                IN  VARCHAR2
,   VERSION                 IN  VARCHAR2
,   TITLE                   IN  VARCHAR2
    /*
    ** All
    */
,   BUTTON                  IN  VARCHAR2      
,   SORT                    IN  VARCHAR2      := 'ORG'
,   TARGET                  IN  VARCHAR2      := 'CENTER'
,   SECURITYTYPE            IN  VARCHAR2      := 'PUBLIC'
,   STYLE                   IN  VARCHAR2      := '107'
,   ONLOAD                  IN  VARCHAR2      := NULL
,   APPEARANCE              IN  VARCHAR2      := NULL
,   ATTACHMENT_NAME         IN  VARCHAR2      := NULL
,   DEPARTMENT              IN  VARCHAR2      := 'FALSE'
,   "GROUP"                 IN  VARCHAR2      := 'FALSE'
,   DIVISION                IN  VARCHAR2      := 'FALSE'
,   CONTEXTTYPE             IN  VARCHAR2      := 'FALSE'
,   DEVICENAME              IN  VARCHAR2      := 'FALSE'
,   OFFLINE                 IN  VARCHAR2      := 'FALSE'
,   POSTSCRIPT              IN  VARCHAR2      := 'FALSE'
,   START_DATE              IN  VARCHAR2      := 'FALSE'
,   STOP_DATE               IN  VARCHAR2      := 'FALSE'
,   DEBUG                   IN  VARCHAR2      := 'FALSE'
,   VERBOSE                 IN  VARCHAR2      := 'FALSE'
    /*
    ** PeopleSoft
    */
,   COMPANY                 IN  VARCHAR2      := 'FALSE'
,   EMPLID                  IN  VARCHAR2      := 'FALSE'
,   DEPTID                  IN  VARCHAR2      := 'FALSE'
,   YEAR_ACQUIRED           IN  VARCHAR2      := 'FALSE'
,   HRS_ADJUST_UNPROC       IN  VARCHAR2      := 'FALSE'
,   PLAN_TYPE               IN  VARCHAR2      := 'FALSE'
,   SCHOOL_CODE             IN  VARCHAR2      := 'FALSE'
,   RUN_ID                  IN  VARCHAR2      := 'FALSE'
,   TAX_LOCATION_CD         IN  VARCHAR2      := 'FALSE'
,   PERIOD_START_DT         IN  VARCHAR2      := 'FALSE'
,   PERIOD_END_DT           IN  VARCHAR2      := 'FALSE'
,   SUB_PERIOD_END_DT       IN  VARCHAR2      := 'FALSE'
,   EFFDT                   IN  VARCHAR2      := 'FALSE'
,   BALANCE_YEAR            IN  VARCHAR2      := 'FALSE'
,   BALANCE_QTR             IN  VARCHAR2      := 'FALSE'
,   BALANCE_PERIOD          IN  VARCHAR2      := 'FALSE'
,   BALANCE_ID              IN  VARCHAR2      := 'FALSE'
,   PAYGROUP                IN  VARCHAR2      := 'FALSE'
,   GROUPNUMBER             IN  VARCHAR2      := 'FALSE'
,   NATIONAL_ID             IN  VARCHAR2      := 'FALSE'
,   EMPL_STATUS             IN  VARCHAR2      := 'FALSE'
,   REG_TEMP                IN  VARCHAR2      := 'FALSE'
,   FTE                     IN  VARCHAR2      := 'FALSE'
,   LOCATION                IN  VARCHAR2      := 'FALSE'
    */
    ** Costpoint
    */
,   ROLLUP                  IN  VARCHAR2      := 'FALSE'
,   ACTIVITY                IN  VARCHAR2      := 'FALSE'
,   PROJ_ID                 IN  VARCHAR2      := 'FALSE'
,   EMPL_ID                 IN  VARCHAR2      := 'FALSE'
,   ORG_ID                  IN  VARCHAR2      := 'FALSE'
,   ACCT_ID                 IN  VARCHAR2      := 'FALSE'
,   PD_END_DT               IN  VARCHAR2      := 'FALSE'
,   SUB_PD_END_DT           IN  VARCHAR2      := 'FALSE'
,   EFFECT_DT               IN  VARCHAR2      := 'FALSE'
,   FY_CD                   IN  VARCHAR2      := 'FALSE'
,   PD_NO                   IN  VARCHAR2      := 'FALSE'
,   SUB_PD_NO               IN  VARCHAR2      := 'FALSE'
,   SUB_ORG_NAME            IN  VARCHAR2      := 'FALSE'
,   SUB_ORG_SUP             IN  VARCHAR2      := 'FALSE'
,   EXPORT_CONTROL          IN  VARCHAR2      := 'FALSE'
,   PROJECT_ID              IN  VARCHAR2      := 'FALSE'
,   PROPOSAL_NO             IN  VARCHAR2      := 'FALSE'
,   SHOW_ACTIVE             IN  VARCHAR2      := 'FALSE'
,   SUBCONTRACT             IN  VARCHAR2      := 'FALSE'
    /*
    ** Miscellaneous
    */
,   MENUBAR                 IN  VARCHAR2      := 'TRUE'
,   SHELLSCRIPT             IN  VARCHAR2      := NULL
,   CONFIGURATION           IN  VARCHAR2      := NULL
,   BORDER                  IN  VARCHAR2      := '1'
,   BORDERCOLOR             IN  VARCHAR2      := NULL
,   CELLPADDING             IN  VARCHAR2      := '2'
,   CELLSPACING             IN  VARCHAR2      := '2'
,   BGCOLOR                 IN  VARCHAR2      := NULL
,   WIDTH                   IN  VARCHAR2      := NULL
);

Parameters

The following table describes the parameters that are comon to PeopleSoft and Costpoint:

Parameter Description 
procname
 		 The fully qualified name of your stored procedure or subroutine. 
version
 		 The version of your procedure. 
title
 		 The title of your procedure. 
button
 		 The button value that is passed to your procedure. Multiple buttons can be defined by separating each value with a comma. 
sort
 		 Determines how to sort the table of departments, divisions, and groups. 
target
 		 The name of this frame. 
securitytype
 		 Determines whether to invoke row level security on the list of departments, divisions, and groups. 
style
 		 The Sybase style to use for all date fields. 
onload
 		 Performs a javascript:onload when this form is built. 
appearance
 		 The default appearance. 
attachment_name
 		 Allows you to specify the name of attachments. If ATTACHMENT_NAME is NULL, TITLE and an appropriate extension will be used for the name of your attachment. HTML documents can also be specified as attachments when ATTACHMENT_NAME is not NULL. 
department
 		 Determines if you want to create a table of department names. 
group
 		 Determines if you want to create a table of group names. 
division
 		 Determines if you want to create a table of division names. 
contexttype
 		 Determines if you want the ability to send the output of this procedure to your Browser, Excel, or Word. This option will set the appropriate value for CONTENTTYPE. Your procedure receives a value for CONTEXTTYPE that corresponds to one of the above: HTML, EXCEL, or WORD. 
devicename
 		 Determines if you want a list of printer names. If postscript is false, it is up to your procedure to send this to a printer. 
offline
 		 Determines if you want the ability to run this procedure off line. 
postscript
 		 Determines if you want the ability to send the output of this procedure to a printer. 
start_date
 		 Determines if you want to pass start_date to your procedure. 
stop_date
 		 Determines if you want to pass stop_date to your procedure. 
debug
 		 Determines if you want to pass debug to your procedure. 
verbose
 		 Determines if you want to pass verbose to your procedure.

PeopleSoft

The following table describes the parameters that can be used in PeopleSoft:

Parameter Description 
company
 		 Determines if you want a list of companies. 
emplid
 		 Determines if you want to enter a valid employee ID or name. 
deptid
 		 Determines if you want a list of department names. 
year_acquired
 		 Determines if you want a year field. 
hrs_adjust_unproc
 		 Determines if you want to allow the number of adjusted hours. 
plan_type
 		 Determines if you want a list of plan types. 
school_code
 		 Determines if you want a list of school codes. 
run_id
 		 Determines if you want a list of payroll run IDs. 
tax_location_cd
 		 Determines if you want a list of tax location codes. 
period_start_dt
 		 Determines if you want a list of period start dates. 
peiod_end_dt
 		 Determines if you want a list of period end dates. 
sub_period_end_dt
 		 Determines if you want a list of sub period end dates. 
effdt
 		 Determines if you want to specify an effective date. 
balance_year
 		 Determines if you want a list of balance years. 
balance_qtr
 		 Determines if you want a list of balance quarters. 
balance_id
 		 Determines if you want a list of balance IDs. 
paygroup
 		 Determines if you want a list of pay groups. 
groupnumber
 		 Determines if you want a list of group numbers. 
national_id
 		 Determines if you want to include national ID when searching an employee ID or name. 
empl_status
 		 Determines if you want a list of employee status. 
reg_temp
 		 Determines if you want a list of regular or temporary. 
fte
 		 Determines if you want to specify FTE to be true or false. 
location
 		 Determines if you want a list of locations.

Costpoint

The following table describes the parameters that can be used in Costpoint:

Parameter Description 
rollup
 		 Determines if you want to create a table of roll-ups. Specifying ROLLUP or ACTIVITY causes the filter to use the DELTEK.LOADORG_TBL instead of the PeopleSoft department table. 
activity
 		 Determines if you want to create a table of activities. Specifying ROLLUP or ACTIVITY causes the filter to use the DELTEK.LOADORG_TBL instead of the PeopleSoft department table. 
proj_id
 		 Determines if you want a list of project IDs. 
empl_id
 		 Determines if you want to enter a valid employee ID or name. 
org_id
 		 Determines if you want a list of org IDs. 
acct_id
 		 Determines if you want a list of account IDs. 
pd_end_dt
 		 Determines if you want a list of period end dates. 
sub_pd_end_dt
 		 Determines if you want a list of sub period end dates. 
effect_dt
 		 Determines if you want to specify the effective date. 
fy_cd
 		 Determines if you want a list of fiscal years. 
pd_no
 		 Determines if you want a list of period numbers. 
sub_pd_no
 		 Determines if you want a list of sub period numbers. 
sub_org_name
 		 Determines if you want a list of sub org names. 
sub_org_sup
 		 Determines if you want a list of sub org supervisors. 
export_control
 		 Determines if you want a list of export control values. 
project_id
 		 Determines if you want to allow the input of a project number. 
proposal_no
 		 Determines if you want to allow the input of a proposal number. 
show_active
 		 Determines if you want to create a check box that allows this option. 
subcontract
 		 Determines if you want to create a check box that allows this option.

Miscellaneous Parameters

The following table describes other parameters that you can specify:

Parameter Description 
menubar
 		 Determines if you want sp_html_filter to create the standard menu bar (sp_html_menubar). 
shellscript
 		 Allows you to specify the name of the shell script that will print your document. 
configuration
 		 Allows you to specify the name of the confaguration file when converting your file from HTML to postscript. 
border
 		 Table border. 
bordercolor
 		 Table borderColor. 
cellpading
 		 Table cellPadding. 
cellspacing
 		 Table cellSpacing. 
bgcolor
 		 Table bgColor. 
width
 		 Table width.

Example

The following example illustrates how to call sp_html_filter: 

       
SP_HTML_FILTER
(
    PROCNAME                      => HTTP.MYSELF.NAME
,   VERSION                       => HTTP.MYSELF.VERSION
,   TITLE                         => HTTP.MYSELF.TITLE
,   BUTTON                        => 'Report'
,   TARGET                        => 'CENTER'
,   DEPARTMENT                    => 'TRUE'
,   "GROUP"                       => 'TRUE'
,   DIVISION                      => 'TRUE'
)

Passing TRUE for any one of DEPARTMENT, DIVISION, or GROUP means you get a table of all departments, divisions, and groups. A value of TRUE for that item means that column is clickable and its value can be passed to your procedure.


Example

The following example illustrates how to call sp_html_filter to request an employee ID or name: 

DELTEK.SP_HTML_FILTER
(
    PROCNAME                      => HTTP.MYSELF.NAME
,   VERSION                       => HTTP.MYSELF.VERSION
,   TITLE                         => HTTP.MYSELF.TITLE
,   BUTTON                        => 'Report'
,   TARGET                        => 'CENTER'
,   EMPL_ID                       => 'TRUE'
)

Because this option also creates a name field that is suitable for searching, your procedure must also accept that field. In Costpoint it is called LAST_FIRST_NAME; in PeopleSoft it is called NAME.


Example

The standard idiom for retrieving rows from the PeopleSoft department table looks like this: 

FOR R IN
(
    SELECT DEPTID
    FROM   HTTP.PS_DEPT_TBL
    WHERE  ( "GROUP"    IS NULL OR "GROUP"    = RTRIM(SRI_GROUP)    )
    AND    ( DIVISION   IS NULL OR DIVISION   = RTRIM(SRI_DIVISION) )
    AND    ( DEPARTMENT IS NULL OR DEPARTMENT = RTRIM(DEPTID)       )
    AND    EFF_STATUS   = 'A'
    AND    SETID        = 'COMMN'
    AND    EFFDT        = 
    (
        SELECT MAX(EFFDT)
        FROM   HTTP.PS_DEPT_TBL B
        WHERE  DEPTID       = PS_DEPT_TBL.DEPTID
        AND    SETID        = PS_DEPT_TBL.SETID
        AND    EFF_STATUS   = PS_DEPT_TBL.EFF_STATUS
        AND    EFFDT       <= SYSDATE
    )
    ORDER BY DEPTID
)   LOOP
    HTTP.WRITELN(R.DEPTID);
END LOOP;

Example

The standard idiom for retrieving rows from the LOADORG_TBL looks like this: 

FOR R IN
(
    SELECT ORG_ID
    FROM   LOADORG_TBL
    WHERE  ( "GROUP"    IS NULL OR "GROUP"    = ALLOC_CD           )
    AND    ( DIVISION   IS NULL OR DIVISION   = MGMT_CD            )
    AND    ( ROLLUP     IS NULL OR ROLLUP     = DIV_CD             )
    AND    ( ACTIVITY   IS NULL OR ACTIVITY   = DEPT_CD            )
    AND    ( DEPARTMENT IS NULL OR DEPARTMENT = SUBSTR(ORG_ID,3,3) )
    AND    LOAD_DATE = 
    (
        SELECT MAX(LOAD_DATE)
        FROM   DELTEK.LOADORG_TBL
        WHERE  LOAD_DATE <= SYSDATE
    )
    ORDER BY ORG_ID
)   LOOP
    HTTP.WRITELN(R.ORG_ID);
END LOOP;

Example

The standard idiom for retrieving rows from the PeopleSoft distribution system looks like this: 

FOR R IN
(
    SELECT DEPTID
    FROM   DATAPROTECTS
    WHERE  USERNAME                         = HTTP.MYSELF.USERNAME
    AND    PROCNAME                         = HTTP.MYSELF.NAME(2)
)   LOOP
    HTTP.WRITELN(R.DEPTID);
END LOOP;

Example

The standard idiom for retrieving rows from SRI_USER_REPORT_PRIV looks like this: 

FOR R IN
(
    SELECT SUBSTR(PRIV_ID,3,5)              "ORG_ID"
    FROM   SRI_USER_REPORT_PRIV
    ,      HTTP.PS_DEPT_TBL PS_DEPT_TBL
    WHERE  RTRIM(PS_DEPT_TBL.DEPTID)        = SUBSTR(PRIV_ID,3,5)
    AND    SRI_USER_REPORT_PRIV.USER_ID     = HTTP.MYSELF.USERNAME
    AND    REC_CD                           = 'ORG'
    AND    SRI_USER_REPORT_PRIV.EFF_STATUS  = 'A'
    AND    SRI_USER_REPORT_PRIV.EFFDT       =
    (
        SELECT MAX(B.EFFDT)
        FROM   SRI_USER_REPORT_PRIV B
        WHERE  USER_ID                      = SRI_USER_REPORT_PRIV.USER_ID
        AND    REC_CD                       = SRI_USER_REPORT_PRIV.REC_CD
        AND    PRIV_ID                      = SRI_USER_REPORT_PRIV.PRIV_ID
        AND    EFFDT                       <= SYSDATE
    )
    AND    PS_DEPT_TBL.EFF_STATUS           = 'A'
    AND    PS_DEPT_TBL.SETID                = 'COMMN'
    AND    PS_DEPT_TBL.EFFDT                =
    (
        SELECT MAX(EFFDT)
        FROM   HTTP.PS_DEPT_TBL B
        WHERE  DEPTID                       = PS_DEPT_TBL.DEPTID
        AND    SETID                        = PS_DEPT_TBL.SETID
        AND    EFF_STATUS                   = PS_DEPT_TBL.EFF_STATUS
        AND    EFFDT                       <= SYSDATE
    )
    
    UNION
    
    SELECT SUBSTR(DEPTID,1,3)               "ORG_ID"
    FROM   SRI_USER_REPORT_PRIV
    ,      HTTP.PS_DEPT_TBL PS_DEPT_TBL
    WHERE  SRI_USER_REPORT_PRIV.USER_ID     = HTTP.MYSELF.USERNAME
    AND    REC_CD                           = 'ORG'
    AND    PRIV_ID                          = 'ALL'
    AND    SRI_USER_REPORT_PRIV.EFF_STATUS  = 'A'
    AND    SRI_USER_REPORT_PRIV.EFFDT       =
    (
        SELECT MAX(EFFDT)
        FROM   SRI_USER_REPORT_PRIV B
        WHERE  USER_ID                      = SRI_USER_REPORT_PRIV.USER_ID
        AND    REC_CD                       = SRI_USER_REPORT_PRIV.REC_CD
        AND    PRIV_ID                      = SRI_USER_REPORT_PRIV.PRIV_ID
        AND    EFFDT                       <= SYSDATE
    )
    AND    PS_DEPT_TBL.EFF_STATUS           = 'A'
    AND    PS_DEPT_TBL.SETID                = 'COMMN'
    AND    PS_DEPT_TBL.EFFDT                =
    (
        SELECT MAX(B.EFFDT)
        FROM   HTTP.PS_DEPT_TBL B
        WHERE  DEPTID                       = PS_DEPT_TBL.DEPTID
        AND    SETID                        = PS_DEPT_TBL.SETID
        AND    EFF_STATUS                   = PS_DEPT_TBL.EFF_STATUS
        AND    B.EFFDT                     <= SYSDATE
    )
    AND    PS_DEPT_TBL.DEPTID               BETWEEN '001' AND '999'
)   LOOP
    HTTP.WRITELN(R.ORG_ID);
END LOOP;


See Also

SP_HTML_DATATYPES
SP_HTML_FRAME
SP_HTML_MENUBAR
SP_HTML_PREFERENCES

Sybernet is a trademark of SRI International.
Copyright © 1996-2009 SRI International. All Rights Reserved.
Denis D. Workman / http://Sybernet.sri.com/