SET scan off
CREATE OR REPLACE PACKAGE CESGetID
AS
-- FILE NAME..: cesgetid.sql
-- RELEASE....: 5.4
-- OBJECT NAME: CESGETID
-- PRODUCT....: GENWEB
-- RELEASED...: 6/2/04
-- USAGE......: CES BANNER Web Employee ID Retrieval Package Specification
-- AUTHOR.....: Stephen Rea; Maristream, Inc.

-- Declare the procedures called within the Web environment: the procedure that
-- creates the employee ID query screen (P_DispIDQuery) and the procedure that
-- is called when the submit (Get Employee ID) button is pressed (P_ProcIDQuery).
-- These procedures must be declared to Web Tailor and marked as "Insecure Access
-- Allowed" (Click on Create button in Customize a Web Menu or Procedure). (Note:
-- For a fully documented package, see /home/common/cesdrlic.sql)

-- Updates:

   PROCEDURE P_DispIDQuery;

   PROCEDURE P_ProcIDQuery(
      ssn4           varchar2,
      name5          varchar2
   );

END CESGetID;
/
SHOW errors
SET scan on

SET scan off
CREATE OR REPLACE PACKAGE BODY CESGetID
AS
-- FILE NAME..: cesgeti1.sql
-- RELEASE....: 5.4
-- OBJECT NAME: CESGETID
-- PRODUCT....: GENWEB
-- RELEASED...: 6/2/04
-- USAGE......: CES BANNER Web Employee ID Retrieval 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).

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

-- Declare cursors used in the package.  For CESGETID, this includes the employee ID
-- for the given last 4 digits of the SSN and the first 5 letters of the last name.

   CURSOR employee_id_cur(ssn4 varchar2, name5 varchar2)
   IS
      SELECT spriden_id FROM spriden,spbpers
      WHERE spriden_pidm = spbpers_pidm
         AND spriden_change_ind is null
         AND SUBSTR(spbpers_ssn,6) = ssn4
         AND LOWER(SUBSTR(spriden_last_name,1,5)) = LOWER(name5);

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

-- Procedure P_DispIDQuery (Display Employee ID Query) builds the HTML
-- to display the fields to query on to get the associated employee ID.
-- It includes creating fields and their associated variables for:
--   The last 4 digits of the SSN (ssn4).
--   The first 5 characters of the last name (name5).
-- Along with one button:
--   A Get Employee ID submit button (which calls P_ProcIDQuery to
--     process the ID query and display the result).
--
-- This procedure must be declared to Web Tailor and marked as "Insecure
-- Access Allowed" (Click on Create button in Customize a Web Menu or
-- Procedure).

   PROCEDURE P_DispIDQuery
   IS
      ssn4           varchar2(4);
      name5          varchar2(5);
   BEGIN

-- Open the form, possibly just showing any outstanding error message(s) in it
-- if present (shouldn't be).

      twbkwbis.P_OpenDoc('cesgetid.P_DispIDQuery');

      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;

-- Start building the form.

      twbkwbis.P_DispInfo('cesgetid.P_DispIDQuery','DEFAULT');
      HTP.FormOpen('cesgetid.P_ProcIDQuery','post'); -- this happens when submit button pressed
      twbkfrmt.P_TableOpen('DATAENTRY',
         cattributes=>'Summary="This table allows querying for the employee ID."');

-- Define field for last 4 digits of SSN for query.

      twbkfrmt.P_TableRowOpen;
      twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('Last 4 digits of your SSN: ',
         idname=>'ssn4_id'));
      twbkfrmt.P_TableData(htf.FormText('ssn4','4','4',ssn4,cattributes=>'ID="ssn4_id"'));
      twbkfrmt.P_TableRowClose;

-- Define field for first 5 characters of last name for query.

      twbkfrmt.P_TableRowOpen;
      twbkfrmt.P_TableDataLabel(twbkfrmt.F_FormLabel('First 5 characters of your last name: ',
         idname=>'name5_id'));
      twbkfrmt.P_TableData(htf.FormText('name5','5','5',name5,cattributes=>'ID="name5_id"'));
      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 button.  The HTP.FormSubmit button (labeled 'Get Employee ID')
-- will call the "post" procedure (cesdrlic.P_ProcIDQuery) specified in the
-- HTP.FormOpen call above.

      HTP.FormSubmit(cvalue=>'Get Employee ID');

-- 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_ProcIDQuery (Process the Employee ID query) does a query
-- for the employee ID (given the last 4 digits of the SSN and the first
-- 5 characters of the last name) when the submit button is pressed, and
-- returns to the parent screen (probably the Login screen), displaying
-- a message at the top of it with the resulting employee ID (if a match
-- was found).
--
-- This procedure must be declared to Web Tailor and marked as "Insecure
-- Access Allowed" (Click on Create button in Customize a Web Menu or
-- Procedure).

   PROCEDURE P_ProcIDQuery(
      ssn4           varchar2,
      name5          varchar2
   )
   IS
      employee_id    spriden.spriden_id%TYPE;
   BEGIN

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

      OPEN twbklibs.getmenuc('cesgetid.P_DispIDQuery');
      FETCH twbklibs.getmenuc INTO twbklibs.twgbwmnu_rec;

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

      CLOSE twbklibs.getmenuc;

-- Get the employee ID for the given SSN and Last Name substrings.

      OPEN employee_id_cur(ssn4,name5);
      FETCH employee_id_cur INTO employee_id;

      IF employee_id_cur%NOTFOUND
      THEN
         msg_text := 'No Employee ID was found for SSN ' || ssn4 || ' and Last Name ' || name5;
         msg_level := 2;
      ELSE
         msg_text := 'Employee ID is ' || employee_id;
         msg_level := 3;
      END IF;

      CLOSE employee_id_cur;

-- Return to the login (parent) screen, displaying the resulting message text
-- string and severity level icon.  (Note: P_WWWLogin is used here instead of
-- twbkwbis.P_GenMenu, since we're returning to the login screen, not a menu.
-- Also, msg_level won't show the icon on the login page, so, we won't use it.)

      twbkwbis.P_WWWLogin('<b>' || msg_text || '</b>');

-- This shouldn't happen!

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

END CESGetID;
/
SHOW errors
SET scan on