SET scan off
CREATE OR REPLACE PACKAGE CESDrLic
AS
-- FILE NAME..: cesdrlic.sql
-- RELEASE....: 5.3
-- OBJECT NAME: CESDRLIC
-- PRODUCT....: GENWEB
-- RELEASED...: 7/1/02
-- USAGE......: CES BANNER Web Drivers License Handling Package Specification
-- AUTHOR.....: Stephen Rea; Maristream, Inc.

-- NOTE: Ths is for drivers licenses stored in the SPBPERS table, not the
-- PPRDLIC table.  It is assumed here that the SPBPERS record already exists
-- for this pidm.

-- Declare the procedures called within the Web environment.  In this case, it
-- is just the procedure that creates the drivers license screen (P_DispDLView)
-- and the procedure that is called when the submit (Update Drivers License)
-- button is pressed (P_ProcDLUpdate).  The supporting procedures don't need
-- to be declared here.

   PROCEDURE P_DispDLView;

   PROCEDURE P_ProcDLUpdate(
      license        spbpers.spbpers_driver_license%TYPE,
      stat_code      spbpers.spbpers_stat_code_driver%TYPE,
      natn_code      spbpers.spbpers_natn_code_driver%TYPE,
      issued         twbklibs.date_input_fmt%TYPE,
      expires        twbklibs.date_input_fmt%TYPE
   );

END CESDrLic;
/
SHOW errors
SET scan on

SET scan off
CREATE OR REPLACE PACKAGE BODY CESDrLic
AS
-- FILE NAME..: cesdrli1.sql
-- RELEASE....: 5.3
-- OBJECT NAME: CESDRLIC
-- PRODUCT....: GENWEB
-- RELEASED...: 7/1/02
-- USAGE......: CES BANNER Web Drivers License Handling Package Body
-- AUTHOR.....: Stephen Rea; Maristream, Inc.

-- Declare variables for the package's release version, the message
-- text string and severity level (1 = Stop, 2 = Warning, 3 = OK),
-- and the current user's pidm.

   curr_release   VARCHAR2(10) := '5.3';
   msg_text       VARCHAR2(1000) := NULL;
   msg_level      NUMBER := 3;
   pidm           NUMBER;

-- Declare cursors used in the package.  For CESDRLIC, these include the
-- spbpers (Basic Person) drivers license information for the given pidm,
-- and cursors for the state and nation codes, which will be used in
-- pull-down selection lists.

   CURSOR spbpers_cur(pidm NUMBER)
   IS
      SELECT spbpers_driver_license,
             spbpers_stat_code_driver,
             spbpers_natn_code_driver,
             spbpers_license_issued_date,
             spbpers_license_expires_date
        FROM spbpers
       WHERE spbpers_pidm = pidm;

   CURSOR stvstat_cur
   IS
      SELECT stvstat_code,
             stvstat_desc
        FROM stvstat
    ORDER BY stvstat_desc;

   CURSOR stvnatn_cur
   IS
      SELECT stvnatn_code,
             stvnatn_nation
        FROM stvnatn
    ORDER BY stvnatn_nation;

--------------------------------------------------------------------------

-- Function F_CharFmtDate converts a date string to a standard format,
-- such as 3/4/56 to 03/04/1956, including RR conversion for 2-digit
-- years or YYYY conversion for 4-digit years.  The standard format
-- (fmt_str) can be either MM/DD/YYYY (the default) or DD-MON-YYYY.
-- If it doesn't recognize the date string, it just returns the date
-- string unchanged (assuming that twbkwbis.f_isdate will be called
-- to check for invalid dates).

   FUNCTION F_CharFmtDate (str VARCHAR2, fmt_str VARCHAR2 DEFAULT NULL)
      RETURN VARCHAR2
   IS
      i   DATE;
   BEGIN
      IF LENGTH(SUBSTR(str,INSTR(str,'/',-1)+1)) = 4 OR
         LENGTH(SUBSTR(str,INSTR(str,'-',-1)+1)) = 4
      THEN
         i := TO_DATE(str,NVL(fmt_str,'MM/DD/YYYY'));
      ELSE
         i := TO_DATE(str,REPLACE(NVL(fmt_str,'MM/DD/YYYY'),'YYYY','RR'));
      END IF;
      RETURN TO_CHAR(i,NVL(fmt_str,'MM/DD/YYYY'));
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN str;
   END F_CharFmtDate;

--------------------------------------------------------------------------

-- Procedure P_DispDLView (Display Drivers License View) builds the HTML to
-- display the drivers license information and allow changes to those values.
-- It includes creating fields and their associated variables for:
--   The drivers license number field (license).
--   A pull-down menu for the state (stat_code).
--   A pull-down menu for the nation (natn_code).
--   The drivers license issued date field (issued).
--   The drivers license expires date field (expires).
-- Along with two buttons:
--   An Update Drivers License submit button (which calls P_ProcDLUpdate to
--     post the changes).
--   A Reset button to change the fields on the screen back to their original
--     values (assuming the submit button hasn't been pressed).
--
-- This procedure must be declared to Web Tailor (Click on Create button in
-- Customize a Web Menu or Procedure).
--
-- Note that only character string values can be passed between html procedures
-- called within the Web environment (although, any value type can be passed to
-- supporting procedures and functions), so the issued date and expires date
-- fields are passed from the generated HTML screen to P_ProcDLUpdate (the
-- 'post' procedure) in character string variables, not date variables.

   PROCEDURE P_DispDLView
   IS
      license        spbpers.spbpers_driver_license%TYPE;
      stat_code      spbpers.spbpers_stat_code_driver%TYPE;
      natn_code      spbpers.spbpers_natn_code_driver%TYPE;
      issued         twbklibs.date_input_fmt%TYPE;
      expires        twbklibs.date_input_fmt%TYPE;
      issued_date    spbpers.spbpers_license_issued_date%TYPE;
      expires_date   spbpers.spbpers_license_expires_date%TYPE;
      spbpers_found  BOOLEAN := TRUE;
   BEGIN

-- Get the current user ID's pidm (for whoever is logged in for this Web
-- session).
--
-- Note that the file names of Banner packages (with procedure and function
-- declarations) and package body's (with procedure and function code)
-- usually follow a standard format, such as <package name>.sql for the
-- declarations and <package nam1>.sql for the code, as in twbkwbis.sql
-- and twbkwbi1.sql for twbkwbis procedures and functions.
--
-- Also, the beginning of the name usually indicates what directory they
-- are in, such as "tw..." files are in $BANNER_HOME/wtlweb/dbprocs,
-- "bwg..." and "hwg..." files are in $BANNER_HOME/genweb/dbprocs, and
-- "hwp..." files are in $BANNER_HOME/payweb/dbprocs. 

      IF NOT twbkwbis.F_ValidUser(pidm)
      THEN
         RETURN;
      END IF;

-- Check to see if there is a SPBPERS record for this pidm, getting the
-- drivers license information fields in it if there is.

      OPEN spbpers_cur(pidm);
      FETCH spbpers_cur INTO license,stat_code,natn_code,issued_date,
         expires_date;

      IF spbpers_cur%NOTFOUND
      THEN
         spbpers_found := FALSE;
      END IF;

      CLOSE spbpers_cur;

-- Open the form, possibly just showing an error message in it if an spbpers
-- record wasn't found for the current user's pidm.
-- Note that one of the simplest Banner Web pages contains:
--   A header (which is generated by twbkwbis.P_OpenDoc, with a Page Name
--     parameter that must then be declared in Web Tailor as a new web procedure)
--   A text message (which is generated by twbkfrmt.P_PrintMessage, that also
--     displays an icon to the left of the text according to the severity level
--     (1 = Stop, 2 = Warning, 3 = OK; note that these numbers are passed as
--     character strings)), and
--   A footer (which is generated by twbkwbis.P_CloseDoc, that also includes the
--     current release number).

      twbkwbis.P_OpenDoc('cesdrlic.P_DispDLView');

      IF msg_text IS NOT NULL   -- for any pending messages, just in case.
      THEN
         twbkfrmt.P_PrintMessage(msg_text,TO_CHAR(msg_level));
         msg_text := NULL;
      END IF;

      IF NOT spbpers_found
      THEN
         twbkfrmt.P_PrintMessage(
            'There is not a basic person record (spbpers) for you to update.',
            '2');
         twbkwbis.P_CloseDoc(curr_release);
         RETURN;
      END IF;

-- Initialize variables.  If I don't find a value for the state and nation
-- codes, I'm setting them to Arkansas and United States here.  Also, get the
-- string values for the date fields, using Banner's twbklibs.date_input_fmt
-- default format (which is MM/DD/YYYY; defined in the wtailor.twgbwrul table).

      IF stat_code IS NULL
      THEN
         stat_code := 'AR';
      END IF;

      IF natn_code IS NULL
      THEN
         natn_code := '157';
      END IF;

      issued := TO_CHAR(issued_date,twbklibs.date_input_fmt);
      expires := TO_CHAR(expires_date,twbklibs.date_input_fmt);

-- Start building the form.  After the header (twbkwbis.P_OpenDoc declared
-- above) comes:
--   The info text (which is generated by twbkwbis.P_DispInfo, and is just the
--     DEFAULT info text here; you can use this to provide your own info text
--     message on the web page that can be specified in Web Tailor by clicking
--     on the "Customize Information Text" button of the "Customize a Web Menu
--     or Procedure" screen and entering text for the "DEFAULT" label).
--   Then, specify the procedure that is called when the submit button is
--     pressed (when the form is "posted") (which is defined by HTP.FormOpen;
--     calling our cesdrlic.P_ProcDLUpdate procedure in this case).
--   Then, open the data entry table (this is an HTML table, not an Oracle
--     table) that will contain the drivers license fields (which is generated
--     by twbkfrmt.P_TableOpen; include a short description in the "Summary"
--     attribute - cattributes is a parameter for twbkfrmt.P_TableOpen).

      twbkwbis.P_DispInfo('cesdrlic.P_DispDLView','DEFAULT');
      HTP.FormOpen('cesdrlic.P_ProcDLUpdate','post'); -- this happens when submit button pressed
      twbkfrmt.P_TableOpen('DATAENTRY',
         cattributes=>'Summary="This table allows updates of drivers license information."');

-- Drivers License Number field.  An HTML table usually contains one row for
-- each field (or, may contain multiple columns per row - see name and SSN
-- example below).  To specify the row (using the new Web Tailor procedures):
--   Start the row with twbkfrmt.P_TableRowOpen.
--   Specify a label for the field, using twbkfrmt.P_TableDataLabel calling
--     twbkfrmt.F_FormLabel; which contains:
--       the text to use as the label, and
--       the idname, which must match the field's ID attribute below.
--   Specify the field itself, using twbkfrmt.P_TableData calling htf.FormText,
--     which contains:
--       the field variable name (license) passed to the Web procedure
--         (cesdrlic.P_ProcDLUpdate) when the submit button is pressed,
--       the size (20) and
--       the maximum length (20) for the field's value,
--       the initial value displayed in the field (the license value from
--         the spbpers_cur cursor), and
--       the ID attribute, which must match the field label's idname above.
--   End the row with twbkfrmt.P_TableRowClose.
--
-- Note: The first (cvalue) parameter to twbkfrmt.F_FormLabel could be a call
-- to g$_nls.get, such as in:
--   g$_nls.get('CESDRLI1-00XX','SQL','Drivers License Number: ')
-- which allows variable substitution in the string (such as %01%, which is
-- replaced by the first parameter after the string) and will, in the future,
-- do a lookup in a table to see if there is some other string (foreign language
-- equivalent) for the given message ID and type; otherwise, just returning the
-- given string.  I chose not to use g$_nls.get calls at this time, in order to
-- simplify this example.
--
-- Note: Multiple table data columns can be specified for a row by specifying
-- additional twbkfrmt.P_TableDataLabel/twbkfrmt.P_TableData pairs.  You can
-- play around with the ccolspan variable in twbkfrmt.P_TableDataLabel and
-- twbkfrmt.P_TableData (such as specifying ccolspan=>'2') to have the label
-- or value span multiple columns.  For example, I added the person's name and
-- SSN to the View Pay Stub Detail web page by creating a cursor and fetching
-- the values and specifying a new row at the beginning of the table, as shown
-- below (note that this doesn't use the F_FormLabel and FormText functions,
-- since it is a read-only 'DATADISPLAY' table instead of a 'DATAENTRY' table,
-- and it uses the old Web Tailor package twgkfrmt):
--   twgkfrmt.P_TableRowOpen;
--   twgkfrmt.P_TableDataLabel('Employee: ');
--   twgkfrmt.P_TableData(emp_name,ccolspan=>'2');
--   twgkfrmt.P_TableDataLabel('SSN: ');
--   twgkfrmt.P_TableData(emp_ssn,calign=>'right');
--   twgkfrmt.P_TableRowClose;

      twbkfrmt.P_TableRowOpen;
      twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Drivers License Number: ',
         idname=>'license_id'));
      twbkfrmt.P_TableData(htf.FormText('license','20','20',license,
         cattributes=>'ID="license_id"'));
      twbkfrmt.P_TableRowClose;

-- State pull-down menu field.  We will build a list of values to show in
-- the pull-down menu, with the current value selected in the menu.  Instead
-- of twbkfrmt.P_TableData used for text fields, we will:
--   Start the pull-down list with twbkfrmt.P_TableDataOpen followed by
--     HTP.FormSelectOpen, where the field variable name and the ID attribute
--     is specified (similar to htf.FormText).
--   For each value in the list (beginning with a 'Not Applicable' item, which
--     returns NULL if it is selected, followed by an item for each record in
--     the stvstat table, which is cycled through using a cursor), call the
--     twbkwbis.P_FormSelectOption procedure, specifying:
--       the text to display in the list,
--       the value to return in the field variable name if that item is selected
--         by the user when the pull-down is activated, and
--       an optional 'selected' parameter if this item is initially selected
--         (the stat_code value from the spbpers_cur cursor, in this case).
--   End the pull-down list with HTP.FormSelectClose followed by
--     twbkfrmt.P_TableDataClose.

      twbkfrmt.P_TableRowOpen;
      twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('State: ',
         idname=>'stat_code_id'));
      twbkfrmt.P_TableDataOpen;  -- use (ccolspan => '5') to make this wider
      HTP.FormSelectOpen('stat_code',
         cattributes=>'ID="stat_code_id"');
      IF stat_code IS NULL
      THEN
         twbkwbis.P_FormSelectOption('Not Applicable',NULL,'selected');
      ELSE
         twbkwbis.P_FormSelectOption('Not Applicable',NULL);
      END IF;

      FOR stvstat_rec IN stvstat_cur
      LOOP
         IF stat_code = stvstat_rec.stvstat_code
         THEN
            twbkwbis.P_FormSelectOption(stvstat_rec.stvstat_desc,
               stvstat_rec.stvstat_code,'selected');
         ELSE
            twbkwbis.P_FormSelectOption(stvstat_rec.stvstat_desc,
               stvstat_rec.stvstat_code);
         END IF;
      END LOOP;

      HTP.FormSelectClose;
      twbkfrmt.P_TableDataClose;
      twbkfrmt.P_TableRowClose;

-- Nation pull-down menu field.  This is similar to the State pull-down menu field.

      twbkfrmt.P_TableRowOpen;
      twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Nation: ',
         idname=>'natn_code_id'));
      twbkfrmt.P_TableDataOpen;  -- use (ccolspan => '5') to make this wider
      HTP.FormSelectOpen('natn_code',
         cattributes=>'ID="natn_code_id"');
      IF natn_code IS NULL
      THEN
         twbkwbis.P_FormSelectOption('Not Applicable',NULL,'selected');
      ELSE
         twbkwbis.P_FormSelectOption('Not Applicable',NULL);
      END IF;

      FOR stvnatn_rec IN stvnatn_cur
      LOOP
         IF natn_code = stvnatn_rec.stvnatn_code
         THEN
            twbkwbis.P_FormSelectOption(stvnatn_rec.stvnatn_nation,
               stvnatn_rec.stvnatn_code,'selected');
         ELSE
            twbkwbis.P_FormSelectOption(stvnatn_rec.stvnatn_nation,
               stvnatn_rec.stvnatn_code);
         END IF;
      END LOOP;

      HTP.FormSelectClose;
      twbkfrmt.P_TableDataClose;
      twbkfrmt.P_TableRowClose;

-- Issued Date field.  This is similar to the Drivers License Number field.
-- However, note that we are including the data format in the label of the
-- field, to show the user what format to use for the date.  Also, instead
-- of calling twbkfrmt.P_TableData with htf.FormText, we are using
-- twbkfrmt.P_TableDataOpen and twbkfrmt.P_TableDataClose (almost like a
-- pull-down menu) surrounding a call to twbkfrmt.P_FormDateText.  If you
-- look through SCT's code, you'll see several methods of doing the same
-- thing on a web page.

      twbkfrmt.P_TableRowOpen;
      twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Issued Date: ' || twbklibs.date_input_fmt,
         idname=>'issued_id'));
      twbkfrmt.P_TableDataOpen;
      twbkfrmt.P_FormDateText('issued',issued,
         cattributes=>'ID="issued_id"');
      twbkfrmt.P_TableDataClose;
      twbkfrmt.P_TableRowClose;

-- Expires Date field.  This is similar to the Issued Date field.

      twbkfrmt.P_TableRowOpen;
      twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Expires Date: ' || twbklibs.date_input_fmt,
         idname=>'expires_id'));
      twbkfrmt.P_TableDataOpen;
      twbkfrmt.P_FormDateText('expires',expires,
         cattributes=>'ID="expires_id"');
      twbkfrmt.P_TableDataClose;
      twbkfrmt.P_TableRowClose;

-- Finish specifying the data entry fields with a call to twbkfrmt.P_TableClose
-- to match the starting twbkfrmt.P_TableOpen.

      twbkfrmt.P_TableClose;
      HTP.Para;

-- Add the buttons.  The HTP.FormSubmit button (labeled 'Update Drivers
-- License') will call the "post" procedure (cesdrlic.P_ProcDLUpdate)
-- specified in the HTP.FormOpen call above.  The HTP.FormReset button
-- (labeled 'Reset') will change the fields on the screen back to their
-- original values.

      HTP.FormSubmit(cvalue=>'Update Drivers License');
      HTP.FormReset(cvalue=>'Reset');

-- Finish building the form by closing the form and document.  The value
-- passed to twbkwbis.P_CloseDoc is usually the current release version
-- of this form, and is placed at the bottom of the form.

      HTP.FormClose;
      twbkwbis.P_CloseDoc(curr_release);
   END;

--------------------------------------------------------------------------

-- Procedure P_ValDLUpdate (Validate Drivers License Update) checks to see
-- that the changes to be entered for the drivers license are correct.  It
-- makes sure that:
--
--   A value (non-null) is entered for all drivers license fields.
--   The issued and expires dates are valid dates for the date format.
--   The expires date is later than the issued date.
--
-- If errors are found, it updates the message text string to include the
-- appropriate error messages (possibly generating multiple error message
-- lines, separated by HTML line breaks), and sets the severity level to 1
-- (which is Stop).

   PROCEDURE P_ValDLUpdate(
      license        spbpers.spbpers_driver_license%TYPE,
      stat_code      spbpers.spbpers_stat_code_driver%TYPE,
      natn_code      spbpers.spbpers_natn_code_driver%TYPE,
      issued         twbklibs.date_input_fmt%TYPE,
      expires        twbklibs.date_input_fmt%TYPE
   )
   IS
      issued_date    spbpers.spbpers_license_issued_date%TYPE := NULL;
      expires_date   spbpers.spbpers_license_expires_date%TYPE := NULL;
   BEGIN

-- Check for some value in drivers license.

      IF license IS NULL
      THEN
         msg_text := msg_text || '&nbsp;&nbsp;&nbsp;License must be entered to update it.<br>';
      END IF;

-- Check that a state has been selected (if a drivers license value was entered).

      IF stat_code IS NULL AND license IS NOT NULL
      THEN
         msg_text := msg_text || '&nbsp;&nbsp;&nbsp;State must be selected.<br>';
      END IF;

-- Check that a nation has been selected (if a drivers license value was entered).

      IF natn_code IS NULL AND license IS NOT NULL
      THEN
         msg_text := msg_text || '&nbsp;&nbsp;&nbsp;Nation must be selected.<br>';
      END IF;

-- Check that an issued date has been entered and that it is a valid date format
-- (if a drivers license value was entered).

      IF issued IS NULL AND license IS NOT NULL
      THEN
         msg_text := msg_text || '&nbsp;&nbsp;&nbsp;Issued Date must be entered.<br>';
      ELSE
         IF twbkwbis.f_isdate(issued,twbklibs.date_input_fmt)
         THEN
            issued_date := twbkwbis.f_fmtdate(issued);
         ELSE
            msg_text := msg_text || '&nbsp;&nbsp;&nbsp;Issued Date (' || issued ||
              ') has invalid date format or value.<br>';
         END IF;
      END IF;

-- Check that an expires date has been entered and that it is a valid date format
-- and that it is later than the issued date (if a drivers license value was entered).

      IF expires IS NULL AND license IS NOT NULL
      THEN
         msg_text := msg_text || '&nbsp;&nbsp;&nbsp;Expires Date must be entered.<br>';
      ELSE
         IF twbkwbis.f_isdate(expires,twbklibs.date_input_fmt)
         THEN
            expires_date := twbkwbis.f_fmtdate(expires);
            IF issued_date IS NOT NULL AND issued_date > expires_date
            THEN
               msg_text := msg_text || '&nbsp;&nbsp;&nbsp;Inconsistent Issued/Expires Dates (' ||
                  issued || ' later than ' || expires || ').<br>';
            END IF;
         ELSE
            msg_text := msg_text || '&nbsp;&nbsp;&nbsp;Expires Date (' || expires ||
              ') has invalid date format or value.<br>';
         END IF;
      END IF;

-- If errors were found, set the severity level to 1 (Stop).

      IF msg_text IS NOT NULL
      THEN
         msg_level := 1;
         msg_text := 'Errors occurred (listed below).  Please try again.<br>' || msg_text;
      END IF;
   END;

--------------------------------------------------------------------------

-- Procedure P_DispDLUpdate (Display Drivers License Update) returns to the
-- parent screen (probably the Personal Information screen) and displays a
-- message at the top of it indicating success or failure of the drivers
-- license change. (Note that this routine is called by P_ProcDLUpdate, which
-- is only called when the submit button is pressed, indicating an attempt
-- was made to change the drivers license information.)  Passing 'Y' to this
-- routine indicates a successful update; otherwise, unsuccessful.

   PROCEDURE P_DispDLUpdate(disp_msg VARCHAR2)
   IS
   BEGIN

-- Determine which menu to call, stored in TWGBWMNU_BACK_URL.

      OPEN twbklibs.getmenuc('cesdrlic.P_DispDLView');
      FETCH twbklibs.getmenuc INTO twbklibs.twgbwmnu_rec;

      IF twbklibs.getmenuc%NOTFOUND
      THEN
         CLOSE twbklibs.getmenuc;
         RAISE twbklibs.getmenuerror;
      END IF;

      CLOSE twbklibs.getmenuc;

-- Indicate a successful update.

      IF disp_msg = 'Y'
      THEN
         msg_text := 'Your drivers license has been successfully changed.';
         msg_level := 3;

-- Indicate a problem with the update.  If the message text string is not
-- empty, a problem was encountered when attempting to update the drivers
-- license information; otherwise, there were no changes made for the drivers
-- license data (even though the user clicked on the submit button).

      ELSE
         IF LENGTH(msg_text) > 0
         THEN
            msg_text := msg_text || '<BR>';
            msg_text := msg_text || 'Your drivers license was not changed.';
         ELSE
            msg_text := 'There was nothing to change on your drivers license.';
            msg_level := 2;
         END IF;
      END IF;

-- Return to the parent screen, displaying the resulting message text string
-- and severity level icon.

      twbkwbis.P_GenMenu(SUBSTR(twbklibs.twgbwmnu_rec.twgbwmnu_back_url,
         INSTR(twbklibs.twgbwmnu_rec.twgbwmnu_back_url,'/',-1)+1),
         msg_text,message_type=>TO_CHAR(msg_level));

-- This shouldn't happen!

   EXCEPTION
      WHEN twbklibs.getmenuerror
      THEN
         twbkfrmt.P_PrintMessage('Configuration Error -' ||
         ' Menu cesdrlic.P_DispDLView not found.','1');
   END;

--------------------------------------------------------------------------

-- Procedure P_ProcDLUpdate (Process Drivers License Update) updates the
-- drivers license information, if valid, and is called when the submit
-- button is pressed.  The parameters passed to it are the ones created
-- by P_DispDLView (in no particular order - they are matched by name).
--
-- This procedure must be declared to Web Tailor (Click on Create button
-- in Customize a Web Menu or Procedure).

   PROCEDURE P_ProcDLUpdate(
      license        spbpers.spbpers_driver_license%TYPE,
      stat_code      spbpers.spbpers_stat_code_driver%TYPE,
      natn_code      spbpers.spbpers_natn_code_driver%TYPE,
      issued         twbklibs.date_input_fmt%TYPE,
      expires        twbklibs.date_input_fmt%TYPE
   )
   IS
      orig_license   spbpers.spbpers_driver_license%TYPE;
      orig_stat_code spbpers.spbpers_stat_code_driver%TYPE;
      orig_natn_code spbpers.spbpers_natn_code_driver%TYPE;
      orig_issued    spbpers.spbpers_license_issued_date%TYPE;
      orig_expires   spbpers.spbpers_license_expires_date%TYPE;
      fmt_issued     twbklibs.date_input_fmt%TYPE;
      fmt_expires    twbklibs.date_input_fmt%TYPE;
      issued_date    spbpers.spbpers_license_issued_date%TYPE;
      expires_date   spbpers.spbpers_license_expires_date%TYPE;
      dummy_date     DATE := to_date('01-JAN-2098');
   BEGIN

-- Get the current user ID's pidm.

      IF NOT twbkwbis.F_ValidUser(pidm)
      THEN
         RETURN;
      END IF;

-- Convert the issued and expires dates to a standard character string
-- format (most likely MM/DD/YYYY).

      fmt_issued := F_CharFmtDate(issued,twbklibs.date_input_fmt);
      fmt_expires := F_CharFmtDate(expires,twbklibs.date_input_fmt);

-- Check to see if the drivers license information entered is valid.
-- If not, return to the parent screen with the appropriate error
-- messages indicating that the update failed.

      P_ValDLUpdate(license,stat_code,natn_code,fmt_issued,fmt_expires);
      IF msg_text IS NOT NULL
      THEN
         P_DispDLUpdate('N');
         RETURN;
      END IF;

-- Convert the issued and expires date strings to date values.

      issued_date := twbkwbis.f_fmtdate(fmt_issued);
      expires_date := twbkwbis.f_fmtdate(fmt_expires);

-- Get the current drivers license values in spbpers for this pidm.

      OPEN spbpers_cur(pidm);
      FETCH spbpers_cur INTO orig_license,orig_stat_code,
         orig_natn_code,orig_issued,orig_expires;
      CLOSE spbpers_cur;

-- If any of the values are different between what's in spbpers and
-- what's passed from the drivers license screen, update spbpers with
-- the changed values, along with setting the activity date.

      IF NVL(license,'~') <> NVL(orig_license,'~')
         OR NVL(stat_code,'~') <> NVL(orig_stat_code,'~')
         OR NVL(natn_code,'~') <> NVL(orig_natn_code,'~')
         OR NVL(issued_date,dummy_date) <> NVL(orig_issued,dummy_date)
         OR NVL(expires_date,dummy_date) <> NVL(orig_expires,dummy_date)
      THEN
         IF license IS NULL
         THEN
            -- Note: P_ValDLUpdate rejects null licenses; otherwise, this
            -- would delete the drivers license information.
            UPDATE spbpers
               SET spbpers_driver_license = NULL,
                   spbpers_stat_code_driver = NULL,
                   spbpers_natn_code_driver = NULL,
                   spbpers_license_issued_date = NULL,
                   spbpers_license_expires_date = NULL,
                   spbpers_activity_date = SYSDATE
               WHERE spbpers_pidm = pidm;
         ELSE
            UPDATE spbpers
               SET spbpers_driver_license = license,
                   spbpers_stat_code_driver = stat_code,
                   spbpers_natn_code_driver = natn_code,
                   spbpers_license_issued_date = issued_date,
                   spbpers_license_expires_date = expires_date,
                   spbpers_activity_date = SYSDATE
               WHERE spbpers_pidm = pidm;
         END IF;
         COMMIT;

-- Return to the parent screen, indicating that the update was successful.

         P_DispDLUpdate('Y');

-- Otherwise, return to the parent screen, indicating nothing was changed.

      ELSE
         P_DispDLUpdate('N');
      END IF;
   END;

END CESDrLic;
/
SHOW errors
SET scan on