DROP TRIGGER ADRFILE.FILES_COUNT_TRGR;

CREATE OR REPLACE TRIGGER ADRFILE.FILES_COUNT_TRGR
   BEFORE DELETE OR INSERT OR UPDATE
   ON ADRFILE.FILES
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   /* *********************************************************************************************/
   /* 12/07/2011 Richard Do	Modified to include the "-"+the count+"/" when creating a new dir */
   /* 11/17/2011 Richard Do  	Commented out all the RAISE_APPLICATION_ERROR per Hsiao. All      */
   /*				errors will be viewed in the error table.			  */
   /*												  */
   /* 08/31/2011 Richard Do	CCR 1355 VBR Handbook - ADRFILE Schema Dynamic Directory Trigger  */
   /* This trigger is basically keeping track of directory.file_count as a file is saved or	  */
   /* deleted to the adrfile.file table.  Afterward, the trigger will make sure current directory */
   /* has file count is still under threadshold which is 90% of the mount_point.maximum_value.    */
   /* If the condition does not meet, a new directory entry will be created as indicated in	  */
   /* directory_pattern.name.									  */
   /*												  */
   /* Prerequsites:										  */
   /*												  */
   /* 1. adrfile.directory_pattern table:							  */
   /*	a. Column name: 									  */
   /*		o First character must be slash (/)						  */
   /*		o Date strings must be wrapped around by square brackets, eg. [dd/dd/yy]          */
   /*		o Date strings cannot be space [  ]						  */
   /*		o If a pair square brackets are not there, it will be treated as strings	  */
   /*	 					 						  */
   /* 	b. in_use_flag: Each application must have at least one active directory pattern.	  */
   /*												  */
   /* 2. adrfile.directory table:								  */
   /*   a. A file can only be stored in one directory which referenced to only a specified 	  */
   /*		an application.									  */
   /*	b. Column name: is in the same pattern defined in the mount_point.directory_pattern.name. */
   /*			The new directory will be created and suffixed with the count of current  */
   /*			directory per day.  For example Handbook/2011/08/23-0,, 		  */
   /*			Handbook/2011/08/23-1, Handbook/2011/08/24-0				  */
   /*	c. Column in_use_flag: at least one active directory per application.			  */
   /*											 	  */
   /* TRIGGER FUNCTIONALITIES:									  */
   /* =======================									  */
   /* A. Delete Mode: a file_count will be reduced by 1.					  */
   /* 												  */
   /* B. Insert Mode: a file count will increased by 1 for specified directory id.		  */
   /* 	 When a file count (after increment) > the max file count for a directory		  */
   /*  	       	  										  */
   /*	    											  */
   /*      1. reuse one of existing records of DIRECTORY to store next file if there	          */
   /*               is a record under DIRECTORY table with room (DIRECTORY.FILE_COUNT < 90%	  */
   /*    	    of  VN_max_file_num (assigned based on MOUNT_POINT.MAXIMUM_VALUE		  */
   /*												  */
   /*  		Steps:										  */
   /*    	1. Identify the directory records with room					  */
   /*    	 a. Set the DIRECTORY.IN_USE_FLAG of record with DIRECTORY.IN_USE_FLAG to 'Y' to N*/
   /*    	 b. Set the DIRECTORY.IN_USE_FLAG of record with with room to 'Y'		  */
   /*    											  */
   /*       2. If no existing directory with room then					  	  */
   /*    	 a. Create a new record of the DIRECTORY table with DIRECTORY.NAME 		  */
   /*    	   populated acordding to the corresponding record in the DIRECTORY_PATTERN table */
   /*    	   with DIRECTORY_PATTERN.IN_USE_FLAG = 'Y'for the associated applicaiton.	  */
   /*    											  */
   /*    	b. Set the DIRECTORY.IN_USE_FLAG of record with DIRECTORY.IN_USE_FLAG to 'Y'      */
   /*    	    to 'N' 									  */
   /*    	 c. set the DIRECTORY.IN_USE_FLAG of the new record to 'Y'			  */
   /*    										 	  */
   /*   Note: each application (a record of the APPLICATION table) has 		  		  */
   /*      * one and only one record of the DIRECTORY_PATTERN with DIRECTORY_PATTERN.IN_USE_FLAG='Y'*/
   /*      * one and only one record of the DIRECTORY with DIRECTORY.IN_USE_FLAG = 'Y'		  */
   /*    											  */
   /*   EXCEPTION HANDINGS 									  */
   /*    											  */
   /*   The trigger will log all ORA errors in the ADRMGT.Job_Process_Errs by invoking stored	  */
   /*   procedure ADRMGT.Job_Process_Err_Sp.							  */
   /*    											  */
   /*												  */
   /* *********************************************************************************************/

   VC_LEFT_SQ_BRACKET   CONSTANT VARCHAR2(1) := '[';
   VC_RIGHT_SQ_BRACKET  CONSTANT VARCHAR2(1) := ']';
   VC_DASH              CONSTANT VARCHAR2(1) := '-';
   VC_YES               CONSTANT VARCHAR2(1) := 'Y';
   VC_NO                CONSTANT VARCHAR2(1) := 'N';
   VC_SLASH		CONSTANT VARCHAR2(1) := '/';
   VC_TRG_NAME          CONSTANT VARCHAR2(16) := 'FILES_COUNT_TRGR';

   VN_tmp_count         NUMBER;
   VN_dir_count         NUMBER;

   VN_file_count              	ADRFILE.DIRECTORY.FILE_COUNT%TYPE := 0;
   VN_max_file_num              ADRFILE.MOUNT_POINT.MAXIMUM_VALUE%TYPE := 0;
   VN_90_pct_max_file_num       ADRFILE.MOUNT_POINT.MAXIMUM_VALUE%TYPE := 0;
   VN_application_id            ADRFILE.APPLICATION.APPLICATION_ID%TYPE;
   VN_directory_id              ADRFILE.DIRECTORY.DIRECTORY_ID%TYPE ;
   VN_temp_dir_id            	ADRFILE.DIRECTORY.DIRECTORY_ID%TYPE ;
   VN_mount_point_id            ADRFILE.MOUNT_POINT.MOUNT_POINT_ID%TYPE;
   VC_new_dir_name             	ADRFILE.DIRECTORY.NAME%TYPE;

   VN_directory_id_in      	ADRFILE.DIRECTORY.DIRECTORY_ID%TYPE ;
   VN_new_dir_id              	ADRFILE.DIRECTORY.DIRECTORY_ID%TYPE ;
   VC_in_use_flag		ADRFILE.DIRECTORY.in_use_flag%TYPE;
   VN_left_sb_pos        	NUMBER := 0;
   VN_right_sb_pos		NUMBER := 0;

   VC_dir_pattern_name          ADRFILE.DIRECTORY_PATTERN.NAME%TYPE;
   VC_date_string         	ADRFILE.DIRECTORY_PATTERN.NAME%TYPE;
   VC_dir_pattern_name_prefix   ADRFILE.DIRECTORY_PATTERN.NAME%TYPE;
   VC_dir_pattern_suffix    	ADRFILE.DIRECTORY_PATTERN.NAME%TYPE;

   VC_pattern_name_first_char   VARCHAR2(1);
   VC_SQLSTMNT_ADD_FILECNT     VARCHAR2(2000):= NULL;
   VC_SQLSTMNT_SUB_FILECNT     VARCHAR2(2000):= NULL;
   VC_SQLSTMNT_UPD_INUSE_FLAG  VARCHAR2(2000):= NULL;

   VC_msg_step     		VARCHAR2(500) := NULL;
   VC_record_info     		VARCHAR2(500) := NULL;
   VC_dir_pattern_info     	VARCHAR2(500) := NULL;

   VC_curr_date_char         	VARCHAR2(36);
   VD_SYSDATE         		DATE;
   VN_90_pct_max       		NUMBER:= .9;
   VC_userid        		VARCHAR2(100);

   VB_success_ind              	BOOLEAN := FALSE;

   DIR_PATRN_NOT_EXISTED_E     	EXCEPTION;
   DATE_STRINGS_EMPTY_E        	EXCEPTION;
   DATE_STRINGS_SPACE_E 	EXCEPTION;
   FILE_DIR_NOT_EXISTED_E       EXCEPTION;
   DIR_PATRN_ACTIVE_DUP_E       EXCEPTION;
   DIR_PAT_SLASH_NOT_EXISTED_E  EXCEPTION;


   CURSOR Get_Directory_Info_C (p_directory_id NUMBER) IS
         SELECT MP.MAXIMUM_VALUE,
                NVL(D.file_count,0) file_count,
                D.application_id,
                MP.mount_point_id ,
                d.in_use_flag
          FROM  ADRFILE.directory d, ADRFILE.mount_point MP
          WHERE D.mount_point_id = MP.mount_point_id
          AND   D.directory_id   = p_directory_id
          AND   EXISTS (SELECT '1'
                      FROM ADRFILE.application a
                        WHERE a.application_id = d.application_id);

   CURSOR Get_dir_pattern_Info_C IS
      SELECT name,
            SUBSTR(name, 1,1)                   pattern_name_first_char,
           INSTR(name,VC_LEFT_SQ_BRACKET)   	left_sb_pos,
           INSTR(name,VC_RIGHT_SQ_BRACKET)	right_sb_pos
      FROM ADRFILE.directory_pattern
      WHERE application_id =  VN_application_id
      AND  NVL(UPPER(IN_USE_FLAG),'N') = VC_YES;

BEGIN

    VC_msg_step := 'Obtaining userid.';
    SELECT SYS_CONTEXT('USERENV','OS_USER') INTO VC_userid
    FROM DUAL;


    VC_msg_step := 'Storing DIR ID.';
    IF DELETING  THEN
       VN_directory_id := :OLD.directory_id;
    ELSE
       VN_directory_id := :NEW.directory_id;
    END IF;

    --==============================================
    --
    -- Retrieving info from directory and mount point
    --
    --==============================================
   VC_msg_step := 'Retrieving directory info.';

   FOR I IN Get_Directory_Info_C (VN_directory_id)  LOOP
          VN_max_file_num       := I.maximum_value;
          VN_90_pct_max_file_num:=  TRUNC(ROUND(I.maximum_value * VN_90_pct_max,2));
          VN_file_count         := I.file_count;
          VN_application_id     := I.application_id;
          VN_mount_point_id     := I.mount_point_id;
          VC_in_use_flag	:= I.in_use_flag;
          VB_success_ind        := TRUE;
   END LOOP;

   IF NOT VB_success_ind THEN
       RAISE FILE_DIR_NOT_EXISTED_E;

   END IF;

   VC_msg_step := 'Set VC_SQLSTMNT_ADD_FILECNT';

   VC_SQLSTMNT_ADD_FILECNT :=
             'UPDATE ADRFILE.directory SET '                	||
              'record_modified_count= record_modified_count+1,' ||
              'file_count= NVL(file_count,0)+1,'               	||
              'record_modified_date=SYSDATE,'                   ||
              'record_modified_by= :1 ';

   VC_msg_step := 'Set VC_SQLSTMNT_UPD_INUSE_FLAG';
   VC_SQLSTMNT_UPD_INUSE_FLAG :=
     		'UPDATE ADRFILE.directory SET '			||
                'in_use_flag = :1, '                      	||
                'record_modified_date=SYSDATE,'                 ||
                'record_modified_by=:2 ';

   VC_msg_step := 'Set VC_SQLSTMNT_SUB_FILECNT';
   VC_SQLSTMNT_SUB_FILECNT :=
     		'UPDATE ADRFILE.directory SET '              	||
                'record_modified_count= record_modified_count+1,' ||
                'file_count= NVL(file_count,0)-1,'             	||
                'record_modified_date=SYSDATE,'              	||
                'record_modified_by=:1 '              		||
                'WHERE directory_id=:2';

   --==============================================
   --
   -- DELETE MODE
   --
   --==============================================
   IF DELETING  THEN

       --
       IF  VN_file_count > 0 THEN

          VC_msg_step := 'DELETION: Reduce file count by 1.';
          EXECUTE IMMEDIATE VC_SQLSTMNT_SUB_FILECNT USING VC_userid, VN_directory_id;

       END IF;

    END IF;


    --==============================================
    --
    -- INSERT MODE
    --
    --==============================================

    IF INSERTING THEN

       --==================================================
       --
       -- Increase file_count by 1 of DIRECTORY table
       --
       --==================================================

       VC_msg_step := 'Increase file count.';

       VC_SQLSTMNT_ADD_FILECNT := VC_SQLSTMNT_ADD_FILECNT||' WHERE directory_id='||VN_directory_id;
       EXECUTE IMMEDIATE VC_SQLSTMNT_ADD_FILECNT USING VC_userid;


       --====================================================================================
       --
       --  Now the the pdf file is stored in specified directory, let's check file count
       --  is still under max number or not, and set active flag (in_use_flag) accordingly
       --
       --====================================================================================

       VC_msg_step := 'Current DIR ID, file count is still under max#.';

       IF VN_file_count+1 < VN_max_file_num THEN

            RETURN; 	-- file count +1 is still under max number, do nothing

       END IF;



       VC_msg_step := 'Current DIR ID, file count exceeds max#.';

       --=============================================================================
       -- Current directory is exceeding the max number threadshold so we will look
       -- for an existing directory that has the least file count and < max number.
       -- Make sure in_use_flag is 'Y' and is for the next use
       --
       --=============================================================================

       VN_tmp_count := 0;


       VC_msg_step := 'Checking existing DIR ID for room.';
       VB_success_ind := FALSE;  	-- reset to false
       VC_in_use_flag := VC_NO;	-- reset to 'N'

       FOR I IN (
       	    SELECT d2.directory_id, d2.in_use_flag
       	    FROM (
		SELECT d.directory_id, d.in_use_flag
	        FROM   adrfile.directory d
	        WHERE  d.mount_point_id = VN_mount_point_id
		AND    d.application_id = VN_application_id
		AND    d.directory_id  != VN_directory_id
		AND    d.file_count    < VN_90_pct_max_file_num	-- Only a directory has room
	        ORDER BY DECODE(d.in_use_flag,VC_YES,1,2)) d2 	-- Pick in_use_flag of "Y" record first
	    	WHERE    ROWNUM <= 1)      			-- Need to find one row
	  LOOP

               VC_in_use_flag := I.in_use_flag;
               VN_temp_dir_id := I.directory_id;
               VB_success_ind := TRUE;

       END LOOP;


       --
       -- Find an existing ACTIVE DIR ID record with room, no need to proceed
       --

       IF VC_in_use_flag = VC_YES THEN

           RETURN;

       END IF;

       --==============================================================
       --
       -- Find an existing non-active Directory ID with room, find others
       -- with smallest file count and active it.
       --
       --==============================================================
       --

       IF VB_success_ind THEN

          --====================================================================================
	  -- Found an existing non-active Directory with room, then in_use_flag to 'Y'
	  --====================================================================================
	  EXECUTE IMMEDIATE 'LOCK TABLE adrfile.directory IN ROW SHARE MODE NOWAIT';

	  VC_msg_step := 'Setting active flag for next use.';
	  EXECUTE IMMEDIATE VC_SQLSTMNT_UPD_INUSE_FLAG||' WHERE directory_id='||VN_temp_dir_id USING VC_YES, VC_userid;

	  --====================================================================================
	  --
	  -- Set in_use_flag to 'N' for current directory id and other directories that were
	  -- previously active
	  --
	  --====================================================================================

	  VC_msg_step := 'Setting non-active flag on current dir.';

	  EXECUTE IMMEDIATE VC_SQLSTMNT_UPD_INUSE_FLAG||' WHERE directory_id!='||VN_temp_dir_id||
              		' AND UPPER(in_use_flag)='''||VC_YES||'''' USING VC_NO, VC_userid;


       ELSE   -- Found no existing DIR ID, create a new directory for next use


          --================================================
     	  --
     	  -- Creating new directory id entry for next use
     	  --
          --================================================


          --=================================================
          --
          -- Check current pattern directory existed
          --
          --=================================================
          VC_msg_step := 'Checking dir pattern existed.';

          VN_tmp_count := 0;

          SELECT COUNT(1)
               INTO VN_tmp_count
          FROM adrfile.directory_pattern DP
          WHERE DP.application_id = VN_application_id
          AND UPPER(DP.in_use_flag) = VC_YES;


          --===============================================================
          --
          -- If VN_tmp_count > 1 that means we have more than one
          -- active rows in the directory pattern table. Raise EXCEPTION
          --
          --================================================================
          VC_msg_step := 'Validate directory pattern.';

          IF VN_tmp_count > 1 THEN

              RAISE DIR_PATRN_ACTIVE_DUP_E;


          ELSIF VN_tmp_count = 1 THEN

              -- Count the number of existing directories today's date which will be used to create directory name
              -- For example:Handbook/2011/08/23-3.
              --

              VC_msg_step := 'Checking existing dir for today date.';

              SELECT COUNT (1)
                    INTO VN_dir_count
              FROM directory
              WHERE application_id = VN_application_id
              AND TRUNC(RECORD_CREATED_DATE) = TRUNC(SYSDATE);

              --========================================================
	      --
              -- Extract directory pattern (e.g. Handbook/[YYYY/MM/DD])
              -- to get prefix, date format and any sufix which are
              -- are used to create a new directory
	      --
	      --==========================================================

	      VC_msg_step := 'Retrieving directory pattern and extracting.';

              FOR I IN Get_dir_pattern_Info_C LOOP

                  --
                  -- Extract info from the directory pattern
                  --
                  VC_dir_pattern_name  := I.name;
                  VC_pattern_name_first_char:= I.pattern_name_first_char;
                  VN_left_sb_pos   	:= I.left_sb_pos;
                  VN_right_sb_pos	:= I.right_sb_pos;

              END LOOP;


 	      --===================================================================
	      -- Foward slash (/) is required first position of directory pattern,
	      -- else raise exception
	      --===================================================================

 	      VC_msg_step := 'Validating slash (/) existed in the 1st pos of dir pattern.';

	      IF VC_SLASH != VC_pattern_name_first_char THEN

 		-- Raise exception when slash is not found in the first position
 		RAISE DIR_PAT_SLASH_NOT_EXISTED_E;
  	      END IF;

 	      --

              IF VN_left_sb_pos > 1 THEN

                 IF  VN_right_sb_pos = VN_left_sb_pos+1 THEN

		     -- =========================================================
                     -- If a pair square bracket is found and has empty date
                     -- strings [], then raise exception
                     -- =========================================================

                     VC_msg_step := 'Empty date string in []';
                     RAISE DATE_STRINGS_EMPTY_E;
                 ELSE

                     VC_msg_step := 'Extracting dt str, squ brackets of dir pattern.';

                     VC_dir_pattern_name_prefix := SUBSTR(VC_dir_pattern_name,1, VN_left_sb_pos-1);
                     VC_dir_pattern_suffix :=  SUBSTR(VC_dir_pattern_name,VN_right_sb_pos+1);
                     VC_date_string := SUBSTR(VC_dir_pattern_name,VN_left_sb_pos+1, (VN_right_sb_pos-VN_left_sb_pos)-1);


                      -- ==================================================
                      -- If date string has space only, then rase exception
                      -- ==================================================
                     IF RTRIM(VC_date_string) IS NULL THEN

                       VC_msg_step := 'Date Strings has space.';
                       RAISE DATE_STRINGS_SPACE_E;

                      END IF;
                 END IF;

              ELSE

                VC_msg_step := 'Square brackets not found.';
                VC_dir_pattern_name_prefix := VC_dir_pattern_name;
                VC_dir_pattern_suffix := NULL;
                VC_date_string := NULL;

              END IF;


              --=================================================================
	      -- Convert today's date to data string of directory_pattern
              --=================================================================
              VC_curr_date_char := TO_CHAR (SYSDATE, VC_date_string);

              --
              VC_msg_step := 'Setting up a new directory name.';
              -- Setting up a new directory name

              VC_new_dir_name := VC_dir_pattern_name_prefix ||
                               	VC_curr_date_char	 ||
                               	VC_dir_pattern_suffix	 ||
                              -- 	VC_DASH		 || -- will be handled in the dir pattern
                              	TO_CHAR(VN_dir_count)	 ||
                              	VC_SLASH;


              VC_dir_pattern_info := VC_dir_pattern_info||CHR(10)||
                        	'New Dir Name    : '||VC_new_dir_name;

   	      --===========================================================================
   	      --
              -- Add a new entry in the directory table and set it active IN_USE_FLAG = 'Y'
              --
              --============================================================================

              VC_msg_step := 'Adding a new directory record.';

              EXECUTE IMMEDIATE 'SELECT DIRECTORY_S.NEXTVAL   FROM DUAL' INTO VN_new_dir_id;

              INSERT INTO ADRFILE.directory (
                          DIRECTORY_ID, APPLICATION_ID,IN_USE_FLAG,MOUNT_POINT_ID,
                          NAME, FILE_COUNT,RECORD_CREATED_BY, RECORD_CREATED_DATE,
                          RECORD_MODIFIED_BY, RECORD_MODIFIED_DATE,RECORD_MODIFIED_COUNT)
              VALUES (VN_new_dir_id,
                       VN_application_id,
                       VC_YES,
                       VN_mount_point_id,
                       VC_new_dir_name,
                       0,
                       VC_userid,
                       SYSDATE,
                       VC_userid,
                       SYSDATE,
                       1);

              VC_msg_step := 'Setting non-active flag on curr rec.';

              --========================================
              -- Set in_use_flag to 'N' for all others
              --========================================
              VC_SQLSTMNT_UPD_INUSE_FLAG := VC_SQLSTMNT_UPD_INUSE_FLAG||' WHERE directory_id!='||VN_new_dir_id||' AND UPPER(in_use_flag)='''||VC_YES||'''';

              EXECUTE IMMEDIATE VC_SQLSTMNT_UPD_INUSE_FLAG USING VC_NO, VC_userid;


          ELSE
              -- Raise EXCEPTION when an active directory pattern not found.
              --
              RAISE DIR_PATRN_NOT_EXISTED_E;

          END IF;		-- Directory Pattern look up

       END IF;

    END IF;  		-- INSERT MODE


 EXCEPTION

     WHEN DIR_PAT_SLASH_NOT_EXISTED_E THEN

         VD_SYSDATE := SYSDATE;
         VC_record_info :=
	   	'Directory ID  : '|| VN_directory_id    || CHR(10)||
	       	'Application ID: '|| VN_application_id  || CHR(10)||
	       	'Mount Point ID: '|| VN_mount_point_id  || CHR(10)||
	       	'File Count    : '|| VN_file_count    	|| CHR(10)||
	       	'90% max count : '|| VN_90_pct_max_file_num;

	 VC_dir_pattern_info :=
	 	 'Dir Pattern Name: '||VC_dir_pattern_name	||CHR(10)||
	 	 'Date String	   : '||VC_date_string		||CHR(10)||
	 	 'Prefix	     : '||VC_dir_pattern_name_prefix||CHR(10)||
	 	 'Sufix		   : '||VC_dir_pattern_suffix	||CHR(10)||
	 	 'Left SB position : '||VN_left_sb_pos		||CHR(10)||
	         'Right SB position: '||VN_right_sb_pos;

	 ADRMGT.Job_Process_Err_Sp(
	       	PC_job_name => SUBSTR(VC_TRG_NAME,1,60),
	      	PD_job_error_date  => VD_SYSDATE,
	        PC_job_error_code => SUBSTR(SQLCODE,1,60),
	        PC_job_error_message => SUBSTR('Error: Forward Slash (/) Required in 1st Position - ' || SQLERRM,1,2000),
	        PD_date1 => VD_SYSDATE,
	        PC_text1 => SUBSTR(VC_msg_step,1,255),
	       	PC_text2 => SUBSTR(VC_record_info,1,255),
	        PC_text3 => SUBSTR(VC_dir_pattern_info,1,255));

	/* RAISE_APPLICATION_ERROR(-20100, 'Forward Slash (/) Required in 1st Position.'||CHR(10)||
	          '======================================================'|| CHR(10)||
	          VC_record_info||CHR(10)||
	          '===================================='|| CHR(10)||
          	  VC_dir_pattern_info||CHR(10)||
          	   '======================================================');
    */

     WHEN DIR_PATRN_ACTIVE_DUP_E THEN

         VD_SYSDATE := SYSDATE;
         VC_record_info :=
	     	 'Directory ID  : '|| VN_directory_id   || CHR(10)||
	         'Application ID: '|| VN_application_id || CHR(10)||
	         'Mount Point ID: '|| VN_mount_point_id || CHR(10)||
	         'File Count    : '|| VN_file_count    	|| CHR(10)||
         	 '90% max count : '|| VN_90_pct_max_file_num;

         ADRMGT.Job_Process_Err_Sp(
              	PC_job_name => SUBSTR(VC_TRG_NAME,1,60),
                PD_job_error_date  => VD_SYSDATE,
           	PC_job_error_code => SUBSTR(SQLCODE,1,60),
           	PC_job_error_message => SUBSTR('Error: Dup Active Dir Patterns - ' || SQLERRM,1,2000),
              	PD_date1 => VD_SYSDATE,
              	PC_text1 => SUBSTR(VC_msg_step,1,255),
              	PC_text2 => SUBSTR(VC_record_info,1,255));

        /* RAISE_APPLICATION_ERROR(-20110, 'Duplicate Active Directory Patterns Found.'||CHR(10)||
          	  '======================================================'|| CHR(10)||VC_record_info||CHR(10)||
          	  '======================================================');
    */

     WHEN DIR_PATRN_NOT_EXISTED_E THEN

         VD_SYSDATE := SYSDATE;
         VC_record_info :=
	      	 'Directory ID  : '|| VN_directory_id   || CHR(10)||
	         'Application ID: '|| VN_application_id || CHR(10)||
	         'Mount Point ID: '|| VN_mount_point_id || CHR(10)||
	         'File Count    : '|| VN_file_count    	|| CHR(10)||
         	 '90% max count : '|| VN_90_pct_max_file_num;

          ADRMGT.Job_Process_Err_Sp(
             	PC_job_name => SUBSTR(VC_TRG_NAME,1,60),
               	PD_job_error_date => VD_SYSDATE,
              	PC_job_error_code => SUBSTR(SQLCODE,1,60),
              	PC_job_error_message => SUBSTR('Error: No Active Directory Pattern Found - '|| SQLERRM,1,2000),
                PD_date1 => VD_SYSDATE,
                PC_text1 => SUBSTR(VC_msg_step,1,255),
              	PC_text2 => SUBSTR(VC_record_info,1,255),
                PC_text3 => SUBSTR(VC_dir_pattern_info,1,255));

        /* RAISE_APPLICATION_ERROR(-20120, 'No Active Directory Pattern Found.'||CHR(10)||
            '======================================================'|| CHR(10)||VC_record_info||CHR(10)||
            '======================================================');
        */

     WHEN DATE_STRINGS_EMPTY_E THEN

          VD_SYSDATE := SYSDATE;
          VC_record_info :=
	      	 'Directory ID  : '|| VN_directory_id   || CHR(10)||
	         'Application ID: '|| VN_application_id || CHR(10)||
	         'Mount Point ID: '|| VN_mount_point_id || CHR(10)||
	         'File Count    : '|| VN_file_count    	|| CHR(10)||
         	 '90% max count : '|| VN_90_pct_max_file_num;

         VC_dir_pattern_info :=
	       	 'Dir Pattern Name: '||VC_dir_pattern_name	||CHR(10)||
	         'Date String	  : '||VC_date_string		||CHR(10)||
	         'Prefix	  : '||VC_dir_pattern_name_prefix||CHR(10)||
	         'Sufix		   : '||VC_dir_pattern_suffix	||CHR(10)||
	         'Left SB position : '||VN_left_sb_pos		||CHR(10)||
         	 'Right SB position: '||VN_right_sb_pos;

         ADRMGT.Job_Process_Err_Sp(
              	PC_job_name => SUBSTR(VC_TRG_NAME,1,60),
              	PD_job_error_date => VD_SYSDATE,
              	PC_job_error_code => SUBSTR(SQLCODE,1,60),
              	PC_job_error_message => SUBSTR('Error: Date Strings is Required - '|| SQLERRM,1,2000),
              	PD_date1 => VD_SYSDATE,
              	PC_text1 => SUBSTR(VC_msg_step,1,255),
              	PC_text2 => SUBSTR(VC_record_info,1,255),
              	PC_text3 => SUBSTR(VC_dir_pattern_info,1,255));

      /*   RAISE_APPLICATION_ERROR(-20120, 'Date Strings is Required.'||CHR(10)||
          	  '======================================================'|| CHR(10)||
           	 VC_record_info||CHR(10)||
	          '===================================='|| CHR(10)||
                  VC_dir_pattern_info||CHR(10)||
          	   '======================================================');
           */

     WHEN DATE_STRINGS_SPACE_E THEN
         VD_SYSDATE := SYSDATE;
	 VC_record_info :=
	       	 'Directory ID  : '|| VN_directory_id   || CHR(10)||
	          'Application ID: '|| VN_application_id || CHR(10)||
	          'Mount Point ID: '|| VN_mount_point_id || CHR(10)||
	          'File Count    : '|| VN_file_count    	|| CHR(10)||
	       	  '90% max count : '|| VN_90_pct_max_file_num;

	 VC_dir_pattern_info :=
	       	  'Dir Pattern Name: '||VC_dir_pattern_name	||CHR(10)||
	          'Date String	  : '||VC_date_string		||CHR(10)||
	          'Prefix	  : '||VC_dir_pattern_name_prefix||CHR(10)||
	 	  'Sufix		   : '||VC_dir_pattern_suffix	||CHR(10)||
	 	  'Left SB position : '||VN_left_sb_pos		||CHR(10)||
	          'Right SB position: '||VN_right_sb_pos;

	 ADRMGT.Job_Process_Err_Sp(
	      	PC_job_name => SUBSTR(VC_TRG_NAME,1,60),
	       	PD_job_error_date => VD_SYSDATE,
	       	PC_job_error_code => SUBSTR(SQLCODE,1,60),
	       	PC_job_error_message => SUBSTR('Error: Date Strings Has Invalid Date Format - '|| SQLERRM,1,2000),
	       	PD_date1 => VD_SYSDATE,
	       	PC_text1 => SUBSTR(VC_msg_step,1,255),
	       	PC_text2 => SUBSTR(VC_record_info,1,255),
	       	PC_text3 => SUBSTR(VC_dir_pattern_info,1,255));

	 /*RAISE_APPLICATION_ERROR(-20120, 'Date Strings Invalid Date Format.'||CHR(10)||
	       	  '======================================================'|| CHR(10)||
	       	 VC_record_info||CHR(10)||
	          '===================================='|| CHR(10)||
	       	  VC_dir_pattern_info||CHR(10)||
                 '======================================================');
        */

     WHEN FILE_DIR_NOT_EXISTED_E THEN

          VD_SYSDATE := SYSDATE;
          VC_record_info :=
	      	 'Directory ID  : '|| VN_directory_id   || CHR(10)||
	         'Application ID: '|| VN_application_id || CHR(10)||
	         'Mount Point ID: '|| VN_mount_point_id || CHR(10)||
	         'File Count    : '|| VN_file_count    	|| CHR(10)||
           	 '90% max count : '|| VN_90_pct_max_file_num;

          ADRMGT.Job_Process_Err_Sp(
               	PC_job_name => SUBSTR(VC_TRG_NAME,1,60),
               	PD_job_error_date => VD_SYSDATE,
               	PC_job_error_code => SUBSTR(SQLCODE,1,60),
               	PC_job_error_message => SUBSTR('Error - Current File ID Not Found- ' || SQLERRM,1,2000),
               	PD_date1 => VD_SYSDATE,
               	PC_text1 => SUBSTR(VC_msg_step,1,255),
               	PC_text2 => 'File record not found in directory id: '||VN_directory_id||' File ID: '||:NEW.file_id,
               	PC_text3 => SUBSTR(VC_record_info,1,255));

       /*  RAISE_APPLICATION_ERROR(-20140, 'Current File ID Not Found in DIRECTORY, MOUNT POINT.'||CHR(10)||
           '======================================================'|| CHR(10)||VC_record_info||CHR(10)||
            '======================================================');
      */

     WHEN OTHERS THEN

         VD_SYSDATE := SYSDATE;
         VC_record_info :=
	     	  'Directory ID  : '|| VN_directory_id    || CHR(10)||
	          'Application ID: '|| VN_application_id  || CHR(10)||
	          'Mount Point ID: '|| VN_mount_point_id  || CHR(10)||
	          'File Count    : '|| VN_file_count      || CHR(10)||
         	  '90% max count : '|| VN_90_pct_max_file_num;

         VC_dir_pattern_info :=
	       	 'Dir Pattern Name: '||VC_dir_pattern_name	||CHR(10)||
	         'Date String	  : '||VC_date_string		||CHR(10)||
	         'Prefix	  : '||VC_dir_pattern_name_prefix||CHR(10)||
	         'Sufix		   : '||VC_dir_pattern_suffix	||CHR(10)||
	         'Left SB position : '||VN_left_sb_pos		||CHR(10)||
                 'Right SB position: '||VN_right_sb_pos;

         ADRMGT.Job_Process_Err_Sp(
             	PC_job_name => SUBSTR(VC_TRG_NAME,1,60),
             	PD_job_error_date  => VD_SYSDATE,
             	PC_job_error_code => SUBSTR(SQLCODE,1,60),
             	PC_job_error_message => SUBSTR('Error Exception - ' || SQLERRM,1,2000),
             	PD_date1 => VD_SYSDATE,
             	PC_text1 => SUBSTR(VC_msg_step,1,255));


    /*	 RAISE_APPLICATION_ERROR(-20999, 'Error Exception.'||CHR(10)||VC_msg_step||CHR(10)||
    	 	 '======================================================'|| CHR(10)||
    	  	VC_record_info||CHR(10)||
   	          '===================================='|| CHR(10)||
          	  VC_dir_pattern_info||CHR(10)||
          	   '======================================================');
*/
END;
/
