SET scan off
CREATE OR REPLACE PACKAGE CESTimEn
AS
-- FILE NAME..: cestimen.sql
-- RELEASE....: 7.3
-- OBJECT NAME: CESTIMEN
-- PRODUCT....: GENWEB
-- RELEASED...: 9/25/07
-- USAGE......: CES BANNER Web Time Entry Approvals Package Specification
-- AUTHOR.....: Stephen Rea; Maristream, Inc.

-- Declare the procedures called within the Web environment: the procedure
-- that creates the time entry approval screen (P_DispTEView) and the procedure
-- that is called when the submit (Submit Approvals/Returns) button is pressed
-- (P_ProcTEUpdate).  These procedures must be declared to Web Tailor (click
-- on Create button in Customize a Web Menu or Procedure). The supporting
-- procedures don't need to be declared here.

-- Updates:
-- 3/15/04 - Original version.
-- 4/19/04 - Include employees with no hours reported (no perhour record).
--    Also, exclude BI's who have reported zero hours.
-- 5/7/04 - Added optional PIN verification for approver (see request_pin).
-- 5/19/04 - Select only the allowed org code job records for superusers.
-- 6/26/07 - Include Report Leave actions (L), along with Report Time
--   actions (T).
-- 7/16/07 - Call SCT's pekteap.p_approve_time procedure to also update
--   the leave balances (which weren't updated here) for the Payroll Leave
--   Reporting changes for SM employees.
-- 7/19/07 - Add a Return checkbox to return the timesheet for correction.
-- 9/25/07 - Combine htp.p javascript lines into a single htp.p call for
--   each javascript function (tip from Mark Styles of McGill University).

   empty_array owa_util.ident_arr;

   PROCEDURE P_DispTEView;

   PROCEDURE P_ProcTEUpdate(
      seqnos_approve owa_util.ident_arr default empty_array,
      seqnos_return owa_util.ident_arr default empty_array,
      PinNo char
   );

END CESTimEn;
/
SHOW errors
SET scan on

SET scan off
CREATE OR REPLACE PACKAGE BODY CESTimEn
AS
-- FILE NAME..: cestime1.sql
-- RELEASE....: 7.3
-- OBJECT NAME: CESTIMEN
-- PRODUCT....: GENWEB
-- RELEASED...: 9/25/07
-- USAGE......: CES BANNER Web Time Entry Approvals 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) := '7.3';
   msg_text       VARCHAR2(1000) := NULL;
   msg_level      NUMBER := 3;
   pidm           NUMBER;
   request_pin    BOOLEAN := FALSE; -- Set this to FALSE to never request approver's
                                   -- PIN, even if ptrinst_web_certification_ind is Y.

-- Declare cursors used in the package.  For CESTIMEN, this includes the time
-- entry records (time sheets) to be approved for the given approver's pidm.

   CURSOR approve_cur(pidm NUMBER)
   IS
      SELECT spriden_first_name first_name,spriden_last_name last_name,
         spriden_pidm,perjobs_year year,perjobs_pict_code pict,
         perjobs_payno payno,perjobs_seqno seqno,nvl(ptrearn_code,' ') code,
         nvl(ptrearn_long_desc,'(No Hours)') category,
         nvl(sum(perhour_hrs),0.00) hours,gobeacc_username approver,
         perrout_appr_pidm apidm
      FROM perjobs,perhour,perrout,spriden,gobeacc,ptrearn
      WHERE perjobs_action_ind in ('T','L')
         AND perjobs_status_ind = 'P'
         AND perhour_jobs_seqno(+) = perjobs_seqno
         AND perrout_jobs_seqno = perjobs_seqno
         AND spriden_pidm = perjobs_pidm
         AND spriden_change_ind is null
         AND gobeacc_pidm = perrout_appr_pidm
         AND ptrearn_code(+) = perhour_earn_code
         AND (perrout_appr_pidm = pidm
            OR EXISTS (SELECT 'x' FROM gobeacc,ptruser
               WHERE gobeacc_pidm = pidm
               AND ptruser_code = gobeacc_username
               AND ptruser_te_appr_superuser = 'Y'
               AND (EXISTS (SELECT 'x' FROM psrorgn
                  WHERE psrorgn_user_code = gobeacc_username
                  AND perjobs_orgn_code_ts between psrorgn_orgn_low
                     AND psrorgn_orgn_high)
               OR NOT EXISTS (SELECT 'x' FROM psrorgn
                  WHERE psrorgn_user_code = gobeacc_username)))
            OR EXISTS (SELECT 'x' from gobeacc g1,ntrtprx,gobeacc g2
               WHERE g1.gobeacc_pidm = pidm
               AND g1.gobeacc_username = ntrtprx_proxy_user_id
               AND g2.gobeacc_username = ntrtprx_user_id
               AND g2.gobeacc_pidm = perrout_appr_pidm))
      GROUP BY
         perjobs_seqno,perjobs_year,perjobs_pict_code,perjobs_payno,
         ptrearn_code,ptrearn_long_desc,spriden_last_name,spriden_first_name,
         spriden_pidm,gobeacc_username,perrout_appr_pidm
      HAVING
         not (nvl(sum(perhour_hrs),0.00) = 0 and perjobs_pict_code = 'BI')
      ORDER BY
         gobeacc_username,perjobs_year,perjobs_pict_code,perjobs_payno,
         spriden_last_name,spriden_first_name,spriden_pidm,ptrearn_long_desc;

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

-- Procedure P_DispTEView (Display Time Entry Approvals View) builds the HTML to
-- display the time entry records and allow changes to the status indicators.
-- It includes creating one or more lines for each person's time entry with:
--   The text for each person's earn code hours.
--   A check box allowing the user to mark the status as approved.
-- Along with the following buttons:
--   A Check All button to set all of the seqno check boxes.
--   An Uncheck All button to clear all of the seqno check boxes.
--   A Switch All button (commented out) to flip the values of all of the
--     seqno check boxes.
--   A Submit Approvals/Returns button (which calls P_ProcTEUpdate to post the
--     changes), along with an optional PIN field for verification.
--   A Reset button (commented out) to change the fields on the screen back
--     to their original (unchecked) values (assuming the submit button hasn't
--     been pressed).

   PROCEDURE P_DispTEView
   IS
      first_name     spriden.spriden_first_name%TYPE;
      last_name      spriden.spriden_last_name%TYPE;
      epidm          spriden.spriden_pidm%TYPE;
      year           perjobs.perjobs_year%TYPE;
      pict           perjobs.perjobs_pict_code%TYPE;
      payno          perjobs.perjobs_payno%TYPE;
      seqno          perjobs.perjobs_seqno%TYPE;
      code           ptrearn.ptrearn_code%TYPE;
      category       ptrearn.ptrearn_long_desc%TYPE;
      hours          perhour.perhour_hrs%TYPE;
      approver       gobeacc.gobeacc_username%TYPE;
      apidm          perrout.perrout_appr_pidm%TYPE;
      spriden_name   varchar2(30);
      approve_line   VARCHAR2(80);
      approve_found  BOOLEAN := TRUE;
      prev_seqno     perjobs.perjobs_seqno%TYPE := 0;
      cnt            number;
      reqpin         char;
   BEGIN

-- Get the current user ID's pidm (for whoever is logged in for this Web
-- session).

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

-- Check to see if there are any time entry records to approve for this pidm.

      OPEN approve_cur(pidm);
      FETCH approve_cur INTO first_name,last_name,epidm,year,pict,payno,seqno,
         code,category,hours,approver,apidm;

      IF approve_cur%NOTFOUND
      THEN
         approve_found := FALSE;
      END IF;

      CLOSE approve_cur;

-- See if the approver's PIN should be requested for verification of approvals.

      IF request_pin
      THEN
        SELECT ptrinst_web_certification_ind INTO reqpin FROM ptrinst;
        request_pin := reqpin = 'Y';
      END IF;

-- Open the form, possibly just showing any outstanding error message(s) in it
-- if present (shouldn't be).  Also, define the Check All, Uncheck All, and
-- Switch All functions to check and/or uncheck all of the seqno check boxes.

      twbkwbis.P_OpenDoc('cestimen.P_DispTEView');

      HTP.meta (
         'Content-Script-Type',
         'Default_Script_Language',
         'text/javascript'
      );
      twbkjscr.p_jscriptopen;
      htp.p('function approveAll(cnt) {
         for (var j = 0; j < cnt; j++) {
            if (cnt == 1) box = eval("document.timenform.seqnos_approve");
            else box = eval("document.timenform.seqnos_approve(" + j + ")");
            if (box.checked == false) box.checked = true;
            if (cnt == 1) box = eval("document.timenform.seqnos_return");
            else box = eval("document.timenform.seqnos_return(" + j + ")");
            if (box.checked == true) box.checked = false;
         }
      }');
      twbkjscr.p_jscriptclose;
      twbkjscr.p_jscriptopen;
      htp.p('function unapproveAll(cnt) {
         for (var j = 0; j < cnt; j++) {
            if (cnt == 1) box = eval("document.timenform.seqnos_approve");
            else box = eval("document.timenform.seqnos_approve(" + j + ")");
            if (box.checked == true) box.checked = false;
         }
      }');
      twbkjscr.p_jscriptclose;
      twbkjscr.p_jscriptopen;
      htp.p('function switchAll(cnt) {
         for (var j = 0; j < cnt; j++) {
            if (cnt == 1) box = eval("document.timenform.seqnos_approve");
            else box = eval("document.timenform.seqnos_approve(" + j + ")");
            box.checked = !box.checked;
            if (cnt == 1) box = eval("document.timenform.seqnos_return");
            else box = eval("document.timenform.seqnos_return(" + j + ")");
            box.checked = !box.checked;
         }
      }');
      twbkjscr.p_jscriptclose;
      twbkjscr.p_jscriptopen;
      htp.p('function returnAll(cnt) {
         for (var j = 0; j < cnt; j++) {
            if (cnt == 1) box = eval("document.timenform.seqnos_return");
            else box = eval("document.timenform.seqnos_return(" + j + ")");
            if (box.checked == false) box.checked = true;
            if (cnt == 1) box = eval("document.timenform.seqnos_approve");
            else box = eval("document.timenform.seqnos_approve(" + j + ")");
            if (box.checked == true) box.checked = false;
         }
      }');
      twbkjscr.p_jscriptclose;
      twbkjscr.p_jscriptopen;
      htp.p('function unreturnAll(cnt) {
         for (var j = 0; j < cnt; j++) {
            if (cnt == 1) box = eval("document.timenform.seqnos_return");
            else box = eval("document.timenform.seqnos_return(" + j + ")");
            if (box.checked == true) box.checked = false;
         }
      }');
      twbkjscr.p_jscriptclose;
      twbkjscr.p_jscriptopen;
      htp.p('function clearApprove(i) {
         box = eval("document.timenform.seqnos_approve(" + i + ")");
         if (box.checked == true) box.checked = false;
      }');
      twbkjscr.p_jscriptclose;
      twbkjscr.p_jscriptopen;
      htp.p('function clearReturn(i) {
         box = eval("document.timenform.seqnos_return(" + i + ")");
         if (box.checked == true) box.checked = false;
      }');
      twbkjscr.p_jscriptclose;

      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('cestimen.P_DispTEView','DEFAULT');
      HTP.FormOpen('cestimen.P_ProcTEUpdate','post',cattributes=>'name=timenform'); -- this happens when submit button pressed
      twbkfrmt.P_TableOpen('DATAENTRY',
         cattributes=>'Summary="This table allows approval of time sheets."');

-- Create each time entry approval field.

      cnt := 0;
      IF approve_found
      THEN
         twbkfrmt.P_TableRowOpen;
         twbkfrmt.P_TableDataLabel('Employee Name');
         twbkfrmt.P_TableDataLabel('Year');
         twbkfrmt.P_TableDataLabel('Pict');
         twbkfrmt.P_TableDataLabel('PayNo');
--         twbkfrmt.P_TableDataLabel('SeqNo');
--         twbkfrmt.P_TableDataLabel('Code');
         twbkfrmt.P_TableDataLabel('Category');
         twbkfrmt.P_TableDataLabel('Hours');
         twbkfrmt.P_TableDataLabel('Approver');
         twbkfrmt.P_TableDataLabel('Approve It');
         twbkfrmt.P_TableDataLabel('Return It');
         twbkfrmt.P_TableRowClose;
         FOR approve_rec IN approve_cur(pidm)
         LOOP
            twbkfrmt.P_TableRowOpen;
            IF approve_rec.seqno != prev_seqno
            THEN
               twbkfrmt.P_TableDataLabel(approve_rec.first_name || ' ' || approve_rec.last_name);
               twbkfrmt.P_TableDataLabel(approve_rec.year,calign=>'right');
               twbkfrmt.P_TableDataLabel(approve_rec.pict);
               twbkfrmt.P_TableDataLabel(approve_rec.payno,calign=>'right');
--               twbkfrmt.P_TableDataLabel(approve_rec.seqno,calign=>'right');
--               twbkfrmt.P_TableDataLabel(approve_rec.code,calign=>'right');
               twbkfrmt.P_TableDataLabel(approve_rec.category);
               twbkfrmt.P_TableDataLabel(to_char(approve_rec.hours,'990.99'),calign=>'right');
               twbkfrmt.P_TableDataLabel(approve_rec.approver);
               twbkfrmt.P_TableData(htf.FormCheckBox('seqnos_approve',
                  to_char(approve_rec.seqno) || '_' || to_char(approve_rec.apidm),cattributes=>'onclick="clearReturn(' || cnt || ')"'),
                  calign=>'center');
--                  cattributes=>'ID="' || 'approve_' || approve_rec.seqno || '_' ||
--                  approve_rec.code || '"'));
               twbkfrmt.P_TableData(htf.FormCheckBox('seqnos_return',
                  to_char(approve_rec.seqno) || '_' || to_char(approve_rec.apidm),cattributes=>'onclick="clearApprove(' || cnt || ')"'),
                  calign=>'center');
--                  cattributes=>'ID="' || 'return_' || approve_rec.seqno || '_' ||
--                  approve_rec.code || '"'));
               cnt := cnt + 1;
            ELSE
               twbkfrmt.P_TableDataLabel('',ccolspan=>'4');
--               twbkfrmt.P_TableDataLabel(approve_rec.code,calign=>'right');
               twbkfrmt.P_TableDataLabel(approve_rec.category);
               twbkfrmt.P_TableDataLabel(to_char(approve_rec.hours,'990.99'),calign=>'right');
               twbkfrmt.P_TableDataLabel('');
            END IF;
            twbkfrmt.P_TableRowClose;
            prev_seqno := approve_rec.seqno;
         END LOOP;
      ELSE
         twbkfrmt.P_PrintMessage('There are no time sheets for you to approve.','2');
      END IF;

-- 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 'Submit Approvals/
-- Returns') will call the "post" procedure (cestimen.P_ProcTEUpdate) specified
-- in the HTP.FormOpen call above.  The HTP.FormReset button (labeled 'Reset')
-- will change the fields on the screen back to their original (unchecked)
-- values.  The other buttons control the seqno check boxes.  An optional PIN
-- verification for approval is also here.

      IF approve_found
      THEN
         htp.p('<input type=button value="Approve All" onClick="approveAll(' || cnt || ')">');
         htp.p('<input type=button value="Clear Approves" onClick="unapproveAll(' || cnt || ')">');
         htp.p('<input type=button value="Return All" onClick="returnAll(' || cnt || ')">');
         htp.p('<input type=button value="Clear Returns" onClick="unreturnAll(' || cnt || ')">');
--         htp.p('<input type=button value="Switch All" onClick="switchAll(' || cnt || ')">');
         htp.p('&nbsp;&nbsp;&nbsp;&nbsp;');
         IF request_pin
         THEN
           htp.p('PIN for Approver:');
           htp.p('<input type="password" name="PinNo" size="8" maxlength="6">');
         ELSE
           htp.p('<input type="hidden" name="PinNo" size="8" maxlength="6">');
           htp.p('&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;');
         END IF;
         HTP.FormSubmit(cvalue=>'Submit Approvals/Returns');
--         HTP.FormReset(cvalue=>'Reset');
      END IF;

-- 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_DispTEUpdate (Display Time Entry 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 time entry
-- updates. (Note that this routine is called by P_ProcTEUpdate, which
-- is only called when the submit button is pressed, indicating an attempt
-- was made to approve the time entries.)  Passing 'Y' to this routine
-- indicates a successful update; otherwise, unsuccessful.

   PROCEDURE P_DispTEUpdate(disp_msg VARCHAR2)
   IS
   BEGIN

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

      OPEN twbklibs.getmenuc('cestimen.P_DispTEView');
      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 time sheet approvals have been saved.';
         msg_level := 3;

-- Indicate a problem with the update.  If the message text string is not
-- empty, a problem was encountered when attempting to save the time entry
-- approvals; otherwise, there were no changes made for the time entry
-- approvals (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 time sheet approvals were not saved.';
            msg_level := 1;
         ELSE
            msg_text := 'There was nothing to approve for your time sheets.';
            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 cestimen.P_DispTEView not found.','1');
   END;

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

-- Procedure P_ProcTEUpdate (Process Time Entry Approvals) updates the
-- time entry status indicators, if valid, and is called when the submit
-- button is pressed.  Note that only the seqnos which are checked are
-- included in the seqnos_approve array passed by the HTML, not the ones
-- which the approver unchecked.  (Same with seqnos_return.)

   PROCEDURE P_ProcTEUpdate(
      seqnos_approve owa_util.ident_arr default empty_array,
      seqnos_return owa_util.ident_arr default empty_array,
      PinNo char
   )
   IS
      seqno          perjobs.perjobs_seqno%TYPE;
      apidm          perrout.perrout_appr_pidm%TYPE;
      superuser      char;
      proxy          char;
      userpin        varchar2(6);
      msg_rec        nokmsgs.msg_rectype;
   BEGIN

-- Get the current user ID's pidm.

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

      IF request_pin
      THEN
        SELECT gobtpac_pin INTO userpin FROM gobtpac
          WHERE gobtpac_pidm = pidm;
        IF PinNo is null or PinNo != userpin
        THEN
          userpin := '';
          msg_text := 'The PIN you entered is incorrect.';
          P_DispTEUpdate('N');
          RETURN;
        END IF;
        userpin := '';
      END IF;

-- Note: The job's org doesn't need to be checked here for superuser,
-- since, if superuser was used to select the job in the first place
-- to be displayed, then the org will have already matched for the
-- job's seqno at that time.

      SELECT DECODE(COUNT(*),0,'N','Y') INTO superuser FROM gobeacc,ptruser
         WHERE gobeacc_pidm = pidm
         AND ptruser_code = gobeacc_username
         AND ptruser_te_appr_superuser = 'Y';

-- Loop through the job seqnos to be approved, and approve each one.

      IF seqnos_approve.EXISTS(1)
      THEN
         FOR i in seqnos_approve.FIRST .. seqnos_approve.LAST
         LOOP
            seqno := SUBSTR(seqnos_approve(i),1,INSTR(seqnos_approve(i),'_')-1);
            apidm := SUBSTR(seqnos_approve(i),INSTR(seqnos_approve(i),'_')+1);
            SELECT DECODE(COUNT(*),0,'N','Y') INTO proxy
               FROM perrout,gobeacc g1,ntrtprx,gobeacc g2
               WHERE perrout_queue_status_ind = 'P'
               AND perrout_jobs_seqno = seqno
               AND g1.gobeacc_pidm = pidm
               AND g1.gobeacc_username = ntrtprx_proxy_user_id
               AND g2.gobeacc_username = ntrtprx_user_id
               AND g2.gobeacc_pidm = perrout_appr_pidm;
            IF proxy = 'Y'
            THEN
               pekteap.p_approve_time(seqno,'A',pidm,apidm,'W',msg_rec);
            ELSIF superuser = 'Y'
            THEN
               pekteap.p_approve_time(seqno,'S',pidm,null,'W',msg_rec);
            ELSE
               pekteap.p_approve_time(seqno,'A',pidm,null,'W',msg_rec);
            END IF;
            IF nokmsgs.f_error_found(msg_rec)
            THEN
               msg_text := 'Error on job ' || seqno || ': ' || msg_rec.msg_text;
               P_DispTEUpdate('N');
               RETURN;
            END IF;
         END LOOP;
      END IF;

-- Loop through the job seqnos to be returned, and return each one.

      IF seqnos_return.EXISTS(1)
      THEN
         FOR i in seqnos_return.FIRST .. seqnos_return.LAST
         LOOP
            seqno := SUBSTR(seqnos_return(i),1,INSTR(seqnos_return(i),'_')-1);
            apidm := SUBSTR(seqnos_return(i),INSTR(seqnos_return(i),'_')+1);
            SELECT DECODE(COUNT(*),0,'N','Y') INTO proxy
               FROM perrout,gobeacc g1,ntrtprx,gobeacc g2
               WHERE perrout_queue_status_ind = 'P'
               AND perrout_jobs_seqno = seqno
               AND g1.gobeacc_pidm = pidm
               AND g1.gobeacc_username = ntrtprx_proxy_user_id
               AND g2.gobeacc_username = ntrtprx_user_id
               AND g2.gobeacc_pidm = perrout_appr_pidm;
            IF proxy = 'Y'
            THEN
               pekteap.p_return_time(seqno,'A',pidm,apidm,'W',msg_rec);
            ELSIF superuser = 'Y'
            THEN
               pekteap.p_return_time(seqno,'S',pidm,null,'W',msg_rec);
            ELSE
               pekteap.p_return_time(seqno,'A',pidm,null,'W',msg_rec);
            END IF;
            IF nokmsgs.f_error_found(msg_rec)
            THEN
               msg_text := 'Error on job ' || seqno || ': ' || msg_rec.msg_text;
               P_DispTEUpdate('N');
               RETURN;
            END IF;
         END LOOP;
      END IF;

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

      IF seqnos_approve.EXISTS(1) or seqnos_return.EXISTS(1)
      THEN
         P_DispTEUpdate('Y');

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

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

END CESTimEn;
/
SHOW errors
SET scan on