DROP PROCEDURE PSIM.PS_MSG_LOG_CLEANUP_SP;

CREATE OR REPLACE PROCEDURE PSIM.PS_MSG_LOG_CLEANUP_SP
  (p_rows_delete_in_batch IN INTEGER DEFAULT 100,
   p_days_retain      IN INTEGER DEFAULT 365) IS
-- ###########################################################################
-- #  Description: CCR 1526 - Retain 1 year data of PS_MSG_LOG
-- #
-- #
-- #   Usage:            Called by run_wrapper.sql
-- #   Input Parameters:
-- #     1) None
-- #   Output:
-- #     1) Terminal
-- #   Internal Script Parameters:
-- #     1) None
-- #
-- #
-- ###########################################################################
-- # Change History
-- #
-- # DATE       User Name         DESCRIPTION
-- # ---------- ----------------- -------------------------------------------
-- # 11/10/2012 Richard Do        Created script.  This is a release enhancement
-- # 01/09/2013 Richard Do      Modify to close/open cursor to avoid ora 1555.
-- #                  		Added DBMS_APPLICATION_INFO for debugging
-- # 02/22/2015 Richadr Do      Removed parallelism DOP.  Added parallel_instance_group
-- #                		to restrict the process run on the last node
-- ###########################################################################


-------------------------------------------------------------------------------
-- Begin Ccr dependent data input area
-------------------------------------------------------------------------------
CC_PSIM_OWNER     CONSTANT VARCHAR2(4) := 'PSIM';
CC_TAB_NAME       CONSTANT VARCHAR2(14):= 'PS_MESSAGE_LOG';
CC_CONS_NAME      CONSTANT VARCHAR2(16):= 'MSGLOG_MSGLOG_FK';
CC_MAX_ROWS_DEL   CONSTANT NUMBER(12)  :=  1000000000;  -- Assuming 100 mil is big enough to retain 1 yr data


VC_process_name   VARCHAR2(60) := 'DELETE PS MSG LOG';
VN_days_retain    PLS_INTEGER  := p_days_retain;
VC_msg_step       VARCHAR2(200);

VC_total_count    NUMBER := 0;
VC_count          NUMBER := 0;
VN_parallel_inst_grp       VARCHAR2(60) := NULL;
VN_job_count      NUMBER := 0;

VC_tmp          NUMBER := 0;

VN_batch_rows     PLS_INTEGER := p_rows_delete_in_batch;
VC_SQLSTMT_DDL    VARCHAR2(300) := NULL;
VD_log_date       DATE;
VD_beg_run_date   DATE;
VC_error_msg      VARCHAR2(2000);
VN_EXCEPTION_CNT  PLS_INTEGER;

dml_errors        EXCEPTION;
NULL_PARALLEL_INSTANCE_GRP_E  EXCEPTION;

PRAGMA EXCEPTION_INIT(dml_errors, -24381);


 time_a     INTEGER;
 time_b     INTEGER;
 time_c     INTEGER;

 vc_mod_name VARCHAR2(100);
 vc_act_name VARCHAR2(100);

TYPE varrayMsgLogID    IS TABLE OF PSIM.PS_MESSAGE_LOG.PS_MESSAGE_LOG_ID%TYPE;

  TABLE_MsgLogID         varrayMsgLogID;

 CURSOR Mod_cons_C IS
   SELECT delete_rule
   FROM   all_CONSTRAINTS
   WHERE CONSTRAINT_NAME= CC_CONS_NAME
   AND owner = CC_PSIM_OWNER;

 CURSOR GetMsgLogID_C IS
   SELECT PS_MESSAGE_LOG_ID
   FROM PSIM.PS_MESSAGE_LOG
   WHERE TRUNC(RECORD_CREATED_DATE) < TRUNC(SYSDATE) - VN_days_retain
   AND rownum <= VN_batch_rows;

BEGIN

    DBMS_APPLICATION_INFO.SET_MODULE(vc_mod_name, vc_act_name);


    DBMS_OUTPUT.PUT_LINE(CHR(10));
    VC_msg_step := 'Get parallel_instance_group id';


    EXECUTE IMMEDIATE 'alter session force parallel query parallel 1';
    EXECUTE IMMEDIATE 'alter session force parallel dml parallel 1';

    EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count=128';

    vc_mod_name := VC_process_name;
    vc_act_name := 'Initial';



    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
               PD_job_log_date => SYSDATE,
               PC_job_status => 'INFORMATION - Process Initiation - DOP '||VC_count,
               PC_job_log_message => 'Initiating '||VC_process_name,
               PD_date1 => VD_beg_run_date,
               PC_text1 => 'Batch rows: '||VN_batch_rows|| ' Retention: '||p_days_retain );


    -- =======================================================================
    --
    -- Check job is currently running or not.  If yes, no need to start again.
    --
    -- =======================================================================
    VN_job_count:= 0;
    VC_count := 0;

    VC_msg_step := 'Checking job actively running.';

    SELECT COUNT(1)
      INTO   VN_job_count
      FROM   SYS.DBA_JOBS_RUNNING R,
             SYS.DBA_JOBS J
      WHERE  J.JOB = R.JOB
      AND    UPPER(J.WHAT) LIKE 'PSIM.PS_MSG_LOG_CLEANUP_SP%';

      IF VN_job_count > 1 THEN

        VD_log_date := SYSDATE;

        ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                  PD_job_log_date => VD_log_date,
                                  PC_job_status => 'INFORMATION  - Job currently running',
                                  PC_job_log_message => 'Currently '||VC_process_name||' job is running => Exiting SP.',
                                  PD_date1 => VD_beg_run_date,
                                  PC_text1 => 'Job aborted because it is currently running.'||VN_job_count);

        RAISE_APPLICATION_ERROR(-20200,'The job is currently still running.');


    END IF;


    -- ========================================================================================
    --
    -- The WHILE-LOOP loops thru until all rows deleted  that satifies CC_MAX_ROWS_DEL
    -- conditions.  CC_MAX_ROWS_DEL is to provide numbe of rows to be deleted.
    --
    -- =======================================================================================

    VC_msg_step := 'Begin while-loop of '||CC_MAX_ROWS_DEL;

    ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
               PD_job_log_date => SYSDATE,
               PC_job_status => 'INFORMATION - WHILE-LOOP Started',
               PC_job_log_message => VC_msg_step,
               PD_date1 => SYSDATE);


    time_a := dbms_utility.get_time;

    ------------------------------------------------------------
    -- This allows for-loop to be open-close to avoid ORA 1555.
    ------------------------------------------------------------

    WHILE VC_total_count <= CC_MAX_ROWS_DEL LOOP

        DBMS_APPLICATION_INFO.SET_ACTION(Action_name => 'Processing Date: ' || dbms_utility.get_time);

        VC_msg_step := 'Begin bulk-delete of '||VN_batch_rows;


       ----------------------------------------------------------------------------
       -- The cursor will open, fetch and close  every xx rows which is limited by
       -- rownum of the cursor.  This is all to avoid  to avoid ORA1555
       -----------------------------------------------------=-----------------------

       OPEN GetMsgLogID_C;

       VC_msg_step := 'Begin Bulk-Collection Fetch.';

       FETCH GetMsgLogID_C BULK COLLECT INTO TABLE_MsgLogID LIMIT VN_batch_rows;


       -- ======================================
       -- Exit loop if there is no rows found
       -- ======================================
       --
       IF TABLE_MsgLogID.COUNT = 0 THEN

           VC_msg_step := 'No more rows found in the collection.';

           ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
               PD_job_log_date => SYSDATE,
               PC_job_status => 'INFORMATION - WHILE-LOOP Ended (Total deleted '||VC_total_count||')',
               PC_job_log_message => VC_msg_step,
               PD_date1 => SYSDATE);

           EXIT;

       ELSE


           VC_msg_step := 'Begin for-all batch deletion';

           FORALL I IN TABLE_MsgLogID.FIRST..TABLE_MsgLogID.LAST SAVE EXCEPTIONS

           DELETE PS_MESSAGE_LOG WHERE PS_MESSAGE_LOG_ID = TABLE_MsgLogID(I);

           VC_count := SQL%ROWCOUNT;

           commit;


       END IF;

       CLOSE GetMsgLogID_C;


       -- =======================================================================
       --
       -- Log info to the log table.
       --
       -- =======================================================================

       time_c := dbms_utility.get_time;

       VC_msg_step := 'While-loop: logging del rowcounts';

       VC_total_count := VC_total_count + VC_count;


       DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PS_MSG_LOG: Deleted: '
                           || TO_CHAR(VC_count,'999,999,990')
                           ||' Total:'||TO_CHAR(VC_total_count,'999,999,999,990') );


    END LOOP;  -- WHILE LOOP


    -- ==================================
    -- ==================================
    --
    -- Logging results to the log table
    --
    -- ==================================
    -- ==================================

    VC_msg_step := 'After While-loop';

    IF VC_total_count > 0 THEN

         ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                      PD_job_log_date => SYSDATE,
                     PC_job_status => 'INFORMATION - Bulk Deletion Completed',
                      PC_job_log_message => 'Total rows deleted: '||VC_total_count||
                             '. Time took '||NVL(TO_CHAR((time_c - time_a)/100),0)|| ' seconds',
                      PD_date1 => SYSDATE,
                      PC_text1 => 'Processed in batch of '||VN_batch_rows||
                          ' - Days retentions of '||p_days_retain);

    ELSE

         ADRMGT.Job_Process_Log_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                          PD_job_log_date => SYSDATE,
                         PC_job_status => 'INFORMATION - Bulk Deletion Completed',
                          PC_job_log_message => 'No rows found to be deleted. Time took '
                                ||NVL(TO_CHAR((time_c - time_a)/100),0)|| ' seconds',
                          PD_date1 => SYSDATE,
                          PC_text1 => 'Processed in batch of '||VN_batch_rows||
                          ' - Days retentions of '||p_days_retain);
    END IF;


    DBMS_APPLICATION_INFO.SET_ACTION(Action_name => NULL);

    DBMS_APPLICATION_INFO.SET_CLIENT_INFO('DELETE MSG LOG - COMPLETED: DELETED '||TO_CHAR(VC_total_count,'999,999,999,990'));



EXCEPTION

   WHEN dml_errors THEN


      VN_EXCEPTION_CNT := SQL%BULK_EXCEPTIONS.COUNT;

      FOR I IN 1 .. VN_EXCEPTION_CNT LOOP
          VC_error_msg :=
            'Error ' || I ||  ' During execution ' ||SQL%BULK_EXCEPTIONS(I).ERROR_INDEX ||
            ' for ' || TABLE_MsgLogID(I) ||' produced error ' ||
            SQL%BULK_EXCEPTIONS(I).ERROR_CODE;

          ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                               PD_job_error_date  => VD_log_date,
                               PC_job_error_code => SUBSTR(SQL%BULK_EXCEPTIONS(I).ERROR_CODE,1,60),
                               PC_job_error_message => VC_msg_step||' - '||SUBSTR(VC_error_msg,1,2000),
                               PD_date1 => VD_beg_run_date,
                               PC_text1 => 'Failed during DML process. Process in batch of '||VN_batch_rows||
                                ' - Days retentions of '||p_days_retain);
     END LOOP;

   WHEN OTHERS THEN

      ADRMGT.Job_Process_Err_Sp(PC_job_name => SUBSTR(VC_process_name,1,60),
                                PD_job_error_date  => VD_log_date,
                               PC_job_error_message => VC_msg_step||SUBSTR(' - Unknown error while deleting from PS_MESSAGE_LOG row.'
                                                                || SQLERRM,1,2000),
                                PD_date1 => VD_beg_run_date,
                                PC_text1 => 'Failed due to unkown error. Process in batch of '||VN_batch_rows||
                                ' - Days retentions of '||p_days_retain);

END;
/

CREATE OR REPLACE PUBLIC SYNONYM PS_MSG_LOG_CLEANUP_SP FOR PSIM.PS_MSG_LOG_CLEANUP_SP;
DROP PROCEDURE PSIM.INS_PROCESS_LOG_SP;

CREATE OR REPLACE PROCEDURE PSIM.Ins_Process_log_SP
					(
			   p_Process_Name		IN adr_process_log.process_name%type
			,  p_Process_Start_Date		IN adr_process_log.process_start_date%type
			,  p_Process_End_Date		IN adr_process_log.process_end_date%type
			,  p_Process_Status_Code	IN adr_process_log.process_status_code%type
			,  p_Ins_Kount			IN adr_process_log.processed_count%type
			,  p_Err_Kount			IN adr_process_log.error_count%type
					)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN				-- Procedure Begin
	  INSERT INTO ADR_PROCESS_LOG
	          	(
		   ADR_PROCESS_LOG_ID
	          	,  PROCESS_NAME
	          	,  PROCESS_START_DATE
	          	,  PROCESS_END_DATE
	          	,  PROCESS_STATUS_CODE
		,  PROCESSED_COUNT
		,  ERROR_COUNT
		)
	VALUES
(
   rpt_psim_s.nextval
,  p_Process_Name
,  p_Process_Start_Date
,  p_Process_End_Date
,  p_Process_Status_Code
,  p_Ins_Kount
,  p_Err_Kount
	         	) ;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
END Ins_Process_Log_SP ;
/

GRANT EXECUTE ON PSIM.INS_PROCESS_LOG_SP TO PSIM_R;

GRANT EXECUTE ON PSIM.INS_PROCESS_LOG_SP TO PSIM_RW;
DROP PROCEDURE PSIM.INS_ERROR_MESSAGES_SP;

CREATE OR REPLACE PROCEDURE PSIM.Ins_Error_Messages_SP
					(
			   p_Process_Name		IN adr_error_messages.process_name%type
			,  p_primary_key_value		IN adr_error_messages.primary_key_value%type
			,  p_error_code			IN adr_error_messages.error_code%type
			,  p_error_message_text		IN adr_error_messages.error_message_text%type
			,  p_error_description		IN adr_error_messages.error_description%type
					)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN				-- Procedure Begin

		INSERT INTO ADR_ERROR_MESSAGES
	(
	   ADR_ERROR_MESSAGE_ID
	,  PROCESS_NAME
	,  PRIMARY_KEY_VALUE
	,  ERROR_CODE
	,  ERROR_DATE
	,  ERROR_MESSAGE_TEXT
	,  ERROR_DESCRIPTION
	)
VALUES
	(
	   rpt_psim_s.nextval
	,  p_Process_Name
	,  p_primary_key_value
	,  p_error_code
	,  sysdate
	,  p_error_message_text
	,  p_error_description
			) ;

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
END Ins_Error_Messages_SP ;
/

GRANT EXECUTE ON PSIM.INS_ERROR_MESSAGES_SP TO PSIM_R;

GRANT EXECUTE ON PSIM.INS_ERROR_MESSAGES_SP TO PSIM_RW;
DROP PROCEDURE PSIM.INITLOAD_PSIM_TRAITS_SP;

CREATE OR REPLACE PROCEDURE PSIM.InitLoad_PSIM_Traits_SP(p_SOI 		NUMBER,
				p_Status_Ind	VARCHAR2,
				p_Active_Flag 	VARCHAR2,
				p_Name_Type 	VARCHAR2,
				p_From_VPID	NUMBER,
				p_To_VPID	NUMBER
			)
	IS
--
--	Date Repaired		Author			Description
--	07/3/2007		Chris Woodyard		added PK exception handling
--	07/22/2008		Chris Woodyard		added initialization of local variables
--
--
--
	BEGIN			-- Procedure Main Begin
	DECLARE

	-- Local variable declarations

		v_Primary_VPID_Id		NUMBER(20)	;
		v_Primary_VPID_Value		VARCHAR2 (29)	;
	   	v_Primary_Key_Value		VARCHAR2(40)	;
	   	v_Person_Trait_id		NUMBER(20)	;
	   	v_Date_of_Birth			VARCHAR2(8)	;
	   	v_Gender_Code			VARCHAR2(4)	;
	  	v_Last_Name			VARCHAR2(35)	;
	   	v_First_Name			VARCHAR2(25)	;
	   	v_Middle_Name			VARCHAR2(25)	;
	   	v_Prefix			VARCHAR2(10)	;
	   	v_Suffix			VARCHAR2(10)	;
	   	v_SSN_Value			VARCHAR2(20)	;
	   	v_SSN_Type_Text 		VARCHAR2(32)	;
	   	v_PSeudo_SSN_Reason_Text	VARCHAR2(50)	;
	   	v_VRFN_Status_Text		VARCHAR2(70)	;
	   	v_Process_Name			VARCHAR2(25)	;
	   	v_Process_Start_Date		DATE		;
	   	v_Process_End_Date		DATE		;
           	v_Process_Status_Code		VARCHAR2(20)	;
	   	v_ssn_type_code			VARCHAR2(30)	;
	   	v_pseudossnreason_code		VARCHAR2(1)	;
	   	v_ssnverificationstatus_code	VARCHAR2(10)	;
	   	v_ssa_sent_date			DATE		;
	   	v_ssa_received_Date		DATE		;
	   	v_ssa_verified_date		DATE		;
	   	v_err_code			VARCHAR2(50)	;
	   	v_err_msg_text			VARCHAR2(250)	;
	   	v_err_descr			VARCHAR2(2000)	;
	   	v_Ins_Kount 			NUMBER(10)	;
	   	v_Err_Kount			NUMBER(10)	;
	   	v_Commit_Kount			NUMBER(10)	;
	   	v_ssaverifcode_code		char(1)		;
		pk_viol_flag			VARCHAR2(1)	;
		pk_violation			EXCEPTION	;

		PRAGMA EXCEPTION_INIT(pk_violation,-1);


	-- Cursor to extract all valid VPIDs based on System of Interest and Status Indicator criteria	--


		CURSOR c_ps_vpid_recs is
		SELECT
	    		ps_vpid.ps_personvpid_id,
        		ps_vpid.VPID_VALUE,
			ps_vpid.id_state_ind,
			ps_vpid.identity_transaction_type,
			ps_pc.persontrait_id
  		  FROM
 	   		PS_Person_VPID ps_vpid,
			PS_Person_Correlation PS_PC
  		 WHERE  ps_vpid.ps_personvpid_id = ps_pc.person_vpid_id
    		   AND  ps_pc.system_of_interest_type_id = p_SOI
    		   AND  ps_pc.status_ind = p_Status_Ind
    		   AND  ps_vpid.ps_personvpid_id between p_From_VPID and p_To_VPID ;

		v_ps_vpid_recs 	c_ps_vpid_recs%rowtype ;

		BEGIN	-- Procedure Body Begin

		-- Local variables value assignments

		  v_Ins_Kount		:= 0	;
		  v_Err_Kount		:= 0	;
		  v_Commit_Kount	:= 0	;
		  v_Process_Name	:= 'INIT_LOAD_PSIM_TRAITS' 	;
        	  v_Process_Start_Date	:= sysdate 			;
		  v_Process_Status_Code	:= 'SUCCESS'			;

     		  OPEN c_ps_vpid_recs;		--  Outer Loop for c_ps_vpid_recs (Step 1 Design)

		  LOOP
	  	    v_Person_Trait_Id := NULL;
		    v_Date_of_Birth := NULL;
		    v_Gender_Code := NULL;
		    v_Last_Name := NULL;
		    v_Middle_Name := NULL;
		    v_First_Name := NULL;
		    v_Prefix := NULL;
		    v_Suffix := NULL;
 		    v_SSN_Value := NULL;
		    v_ssn_type_code := NULL;
		    v_pseudossnreason_code := NULL;
		    v_ssnverificationstatus_code := NULL;
		    v_ssa_sent_date := NULL;
		    v_ssa_received_date := NULL;
		    v_ssa_verified_date := NULL;
		    v_ssaverifcode_code := NULL;


         	  FETCH c_ps_vpid_recs INTO v_ps_vpid_recs ;
           	  	EXIT WHEN c_ps_vpid_recs%NOTFOUND;

------	Step 2 Design: Fetch Trait Identifiers information based on Primary Trait Ids for each veteran VPID derived in step 1


			BEGIN		-- Begin Person Trait PL/SQL Block


			  SELECT
			  	pt.ps_person_trait_id,
				pth.birth_date_text,
				gndr.code
		    	    INTO
				v_Person_Trait_Id,
				v_Date_of_Birth,
				v_Gender_Code
			    FROM
				ps_person_trait pt,
		         	ps_person_trait_history pth,
		         	std_gender gndr
			   WHERE
			   	  pt.ps_person_trait_Id = v_ps_vpid_recs.persontrait_id
		     	     AND  (( pth.owner_id = pt.ps_person_trait_id )
		     	     AND  (pth.is_active_flag = p_Active_Flag ))
		     	     AND  gndr.id = pth.gender_id ;

			EXCEPTION		-- Begin Person Trait Exception
			  WHEN NO_DATA_FOUND THEN
				BEGIN
					v_err_code 	:= SQLCODE ;
					v_err_msg_text	:= substr(SQLERRM,1,64) ;
					v_err_descr	:= 'PS_Person Trait Data not available for the following VPID/Person Trait Id: ' || to_char(v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

					v_Primary_Key_Value := to_char( v_person_trait_id) ;

		-- Call to Insert record into Error Messages table

			  		Ins_Error_Messages_SP(
						v_Process_Name,
				    		v_Primary_Key_Value,
         					v_err_code,
				    		v_err_msg_text,
	         			    	v_err_descr
	   		         		) ;
					v_Err_Kount  :=  v_Err_Kount + 1 ;

			  	END ;	-- End Person Trait Exception

			  WHEN TOO_MANY_ROWS THEN
				BEGIN
					v_err_code	:= SQLCODE ;
					v_err_msg_text	:= substr(SQLERRM,1,64) ;
					v_err_descr	:= 'PS_Person Trait Data (Too Many Rows) for the following VPID/Person Trait Id: ' || to_char(v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

					v_Primary_Key_Value := to_char( v_person_trait_id) ;

			-- Call to Insert record into Error Messages table

			  		Ins_Error_Messages_SP(
						v_Process_Name,
				    		v_Primary_Key_Value,
         					v_err_code,
				    		v_err_msg_text,
	         			    	v_err_descr
	   		         		) ;
					v_Err_Kount  :=  v_Err_Kount + 1 ;

			  	END ;	-- End Person Trait Exception

			END ;	-- End Person Trait PL/SQL Block

-----	Step 3: Fetch name information based on Person Trait Id for each veteran Primary Trait Identifier derived in step 2


			BEGIN		-- Begin Person Name PL/SQL Block


			  SELECT
				pnh.Last_Name,
				pnh.Middle_Name,
				pnh.First_Name,
				pnh.Prefix,
				pnh.Suffix
			    INTO
				v_Last_Name,
				v_Middle_Name,
				v_First_Name,
				v_Prefix,
				v_Suffix
			    FROM
				PS_Person_Name pn,
		    		PS_Person_Name_History pnh
			   WHERE
				  pn.Person_Trait_Id   	=  v_Person_Trait_Id
		     	     AND  (( pnh.owner_Id      	= pn.PS_Person_Name_Id )
		     	     AND  (pnh.is_active_Flag 	= p_Active_Flag )
		     	     AND  (pnh.Name_type_Id 	= p_Name_Type )) ;

			EXCEPTION		-- Begin Person Name Exception
			  WHEN NO_DATA_FOUND THEN
				BEGIN
					v_err_code	:= SQLCODE ;
					v_err_msg_text	:= substr(SQLERRM,1,64) ;
					v_err_descr 	:= 'PS_Person Name Data not available for the following VPID/Person Trait Id: ' || to_char( v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

					v_Primary_Key_Value	:= to_char( v_person_trait_id) ;

				-- Call to Insert record into Error Messages table

 			  		Ins_Error_Messages_SP(
						v_Process_Name,
				    		v_Primary_Key_Value,
         					v_err_code,
				    		v_err_msg_text,
	         			    	v_err_descr
	   		         		) ;
					v_Err_Kount  :=  v_Err_Kount + 1 ;

				END ;	-- End Person Name Exception

			  WHEN TOO_MANY_ROWS THEN
				BEGIN
					v_err_code	:= SQLCODE ;
					v_err_msg_text	:= substr(SQLERRM,1,64) ;
					v_err_descr 	:= 'PS_Person Name Data (Too Many Rows) for the following VPID/Person Trait Id: ' || to_char(v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

					v_Primary_Key_Value	:= to_char( v_person_trait_id) ;

				-- Call to Insert record into Error Messages table

			  		Ins_Error_Messages_SP(
						v_Process_Name,
				    		v_Primary_Key_Value,
         					v_err_code,
				    		v_err_msg_text,
	         			    	v_err_descr
	   		         		) ;
					v_Err_Kount  :=  v_Err_Kount + 1 ;

				END ;	-- End Person Name Exception

			END ;	-- End Person Name PL/SQL Block

-----	Step 4: Fetch SSN information based on Person Trait Id for each veteran derived in step2


			BEGIN			-- Begin Person SSN PL/SQL Block


				SELECT
			   		ssn.Person_Trait_Id,
					ssnh.SSN_Value,
					ssnt.code,
					ssnr.code,
					vs.code,
					ssnh.SSA_SENT_Date,
		    			ssnh.SSA_Received_Date,
					ssnh.SSA_Verified_Date,
					ssc.code
		    		INTO
			   		v_Person_Trait_Id,
					v_SSN_Value,
					v_ssn_type_code,
					v_pseudossnreason_code,
					v_ssnverificationstatus_code,
					v_ssa_sent_date,
					v_ssa_received_date,
					v_ssa_verified_date,
					v_ssaverifcode_code
		  		FROM
			   		ps_ssn  ssn,
					ps_ssn_history ssnh,
					std_ssntype ssnt,
					std_pseudossnreason ssnr,
					std_ssnverificationstatus vs,
					std_ssaverifcode ssc
				WHERE
		 	    		ssn.person_trait_id   = v_person_trait_id
   		      		  AND   (( ssnh.owner_id   	= ssn.ps_ssn_id )
		      		  AND   ( ssnh.is_active_flag 	= p_Active_Flag ))
		      		  AND   ssnt.ID(+)		= ssnh.STD_SSNType_ID
		      		  AND   ssnr.ID(+) 		= ssnh.STD_PseudoSSNReason_ID
		      		  AND   vs.id(+) 		= ssnh.STD_SSNVerificationStatus_ID
		      		  AND   ssc.id(+)		= ssnh.std_ssaverifcode_id;

			EXCEPTION			-- Begin Person SSN Exception
			  WHEN NO_DATA_FOUND THEN
				BEGIN
					v_err_code	:= SQLCODE ;
					v_err_msg_text	:= substr(SQLERRM,1,64) ;
					v_err_descr 	:= 'PS_SSN Data not available for the following VPID/Person Trait Id: ' || to_char( v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

					v_Primary_Key_Value	:= to_char( v_person_trait_id) ;

			-- Call to Insert record into Error Messages table

			  		Ins_Error_Messages_SP(
						v_Process_Name,
				    		v_Primary_Key_Value,
         					v_err_code,
				    		v_err_msg_text,
	         			    	v_err_descr
	   		         		) ;
					v_Err_Kount  :=  v_Err_Kount + 1 ;

				END ;	-- End Person SSN Exception

			  WHEN TOO_MANY_ROWS THEN
				BEGIN
					v_err_code	:= SQLCODE ;
					v_err_msg_text	:= substr(SQLERRM,1,64) ;
					v_err_descr 	:= 'PS_SSN (Too Many Rows) for the following VPID/Person Trait Id: ' || to_char( v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

					v_Primary_Key_Value	:= to_char( v_person_trait_id) ;

			-- Call to Insert record into Error Messages table

			  		Ins_Error_Messages_SP(
						v_Process_Name,
				    		v_Primary_Key_Value,
         					v_err_code,
				    		v_err_msg_text,
	         			    	v_err_descr
	   		         		) ;
					v_Err_Kount  :=  v_Err_Kount + 1 ;

				END ;	-- End Person SSN Exception


			END ;	-- End Person SSN PL/SQL Block

-----	Step 5: Insert Record into RPT_PSIM_TRAITS table

			IF v_ps_vpid_recs.identity_transaction_type = 'A'
			  THEN
				BEGIN
		  			INSERT INTO RPT_PSIM_TRAITS
					   (VPID_ID,		VPID_Value,		Last_Name,
					    First_Name,		Middle_Name,		Prefix,
					    Suffix, 		SSN,			SSN_TYPE_CODE,
					    PSEUDO_SSN_REASON_CODE,			SSN_VRFN_STATUS_CODE,
					    SSA_Sent_Date,	SSA_Received_Date,	SSA_Verified_Date,
					    GENDER_CODE,    	Date_of_Birth,		RECORD_CREATED_BY,
					    RECORD_CREATED_DATE,			ssaverifcode_code
	  				   )
					  VALUES
					   (
				  	    v_ps_vpid_recs.ps_personvpid_id,		v_ps_vpid_recs.VPID_VALUE,
					    v_Last_Name,	v_First_Name,		v_Middle_Name,
					    v_Prefix,		v_Suffix,		v_SSN_Value,
					    v_ssn_type_code,	v_pseudossnreason_code,	v_ssnverificationstatus_code,
					    v_SSA_SENT_Date, 	v_SSA_Received_Date,	v_SSA_Verified_Date,
					    v_Gender_Code,	v_Date_of_Birth,	'ADR_PSIM_IPROCCESS',
					    sysdate,		v_ssaverifcode_code
					   );

					v_Ins_Kount := v_Ins_Kount + 1 ;
					v_Commit_Kount := v_Commit_Kount + 1 ;

				EXCEPTION		-- insert report table

		  			WHEN pk_violation then
	        			BEGIN
						v_err_code		:= SQLCODE ;
						v_err_msg_text		:= substr(SQLERRM,1,64) ;
						v_err_descr 		:= ' Main Exception Error (Report table PK violation on insert) for the following VPID/Person Trait Id: ' || to_char( v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

						v_Primary_Key_Value	:= to_char( v_ps_vpid_recs.ps_personvpid_id) ;

                                                pk_viol_flag		:= 'x';

		  				Ins_Error_Messages_SP 			-- Call to Insert record into Error Messages table
						(
						  v_Process_Name,
						  v_Primary_Key_Value,
         					  v_err_code,
						  v_err_msg_text,
         					  v_err_descr
         					) ;

	  					v_Process_End_Date	:=  sysdate  ;
						IF v_Ins_Kount = 0 THEN
	  						v_Process_Status_Code := 'NO Records processed';
						END IF;

	 					Ins_Process_Log_SP
						(
					   	  v_Process_Name,
	         				  v_Process_Start_Date,
         					  v_Process_End_Date,
						  v_Process_Status_Code,
         					  v_Ins_Kount,
         					  v_Err_Kount
         					) ;

		  			END ;

				END ;

			END IF;

-----	       Check for PK violation and fix accordingly
			IF pk_viol_flag	= 'x'
			  THEN
				BEGIN
					update ps_person_vpid
					  set IDENTITY_TRANSACTION_TYPE = 'M',
					      IDENTITY_UPDATE_EVENT_DATE = trunc(sysdate)
					 where ps_personvpid_id = v_ps_vpid_recs.ps_personvpid_id;

					v_Commit_Kount	:= v_Commit_Kount + 1 ;
					pk_viol_flag	:= '';

				EXCEPTION

				  WHEN NO_DATA_FOUND THEN
					BEGIN
					v_err_code	:= SQLCODE ;
					v_err_msg_text	:= substr(SQLERRM,1,64) ;
					v_err_descr 	:= 'Update of Person_VPID failed for the following VPID/Person Trait Id: ' || to_char( v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

					v_Primary_Key_Value	:= to_char( v_person_trait_id) ;

					-- Call to Insert record into Error Messages table

			  		Ins_Error_Messages_SP(
						v_Process_Name,
				    		v_Primary_Key_Value,
         					v_err_code,
				    		v_err_msg_text,
	         			    	v_err_descr
	   		         		) ;
					v_Err_Kount  :=  v_Err_Kount + 1 ;

					END ;	-- End Person SSN Exception

				END ;

			END IF;

			IF v_Commit_Kount = 25000 THEN
			  COMMIT;
			  v_Commit_Kount := 0 ;
			END IF;

		  END LOOP;
		  CLOSE c_ps_vpid_recs;

		  -- Call to Insert record into Process Log table

	  	  v_Process_End_Date	:=  sysdate  ;
		  IF v_Ins_Kount = 0 THEN
	  		v_Process_Status_Code := 'NO Records processed';
		  END IF;

	  	  Ins_Process_Log_SP
			  (
			   v_Process_Name,
	         	   v_Process_Start_Date,
         		   v_Process_End_Date,
			   v_Process_Status_Code,
         		   v_Ins_Kount,
         		   v_Err_Kount
         		  ) ;

     		  COMMIT;

		  EXCEPTION		-- Main SP: Raise Exception

		  WHEN NO_DATA_FOUND then
	        	BEGIN
				v_err_code		:= SQLCODE ;
				v_err_msg_text		:= substr(SQLERRM,1,64) ;
				v_err_descr 		:= ' Main Exception Error (No Data Found) for the following VPID/Person Trait Id: ' || to_char( v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

				v_Primary_Key_Value	:= to_char( v_ps_vpid_recs.ps_personvpid_id) ;

		  		Ins_Error_Messages_SP 			-- Call to Insert record into Error Messages table
					(
					  v_Process_Name,
					  v_Primary_Key_Value,
         				  v_err_code,
					  v_err_msg_text,
         				  v_err_descr
         				) ;

	  			v_Process_End_Date	:=  sysdate  ;
				IF v_Ins_Kount = 0 THEN
	  				v_Process_Status_Code := 'NO Records processed';
				END IF;

	 			Ins_Process_Log_SP
					(
				   	  v_Process_Name,
	         			  v_Process_Start_Date,
         				  v_Process_End_Date,
					  v_Process_Status_Code,
         				  v_Ins_Kount,
         				  v_Err_Kount
         				) ;

		  	END ;

		  WHEN OTHERS then
	        	BEGIN
				v_err_code	:= SQLCODE ;
				v_err_msg_text	:= substr(SQLERRM,1,64) ;
				v_err_descr 	:= ' Main Exception Error (Others) for the following VPID/PersonTrait Id: ' || to_char( v_ps_vpid_recs.ps_personvpid_id) || '/' || to_char(v_person_trait_id);

				v_Primary_Key_Value	:= to_char( v_ps_vpid_recs.ps_personvpid_id) ;

		  		Ins_Error_Messages_SP 			-- Call to Insert record into Error Messages table
					(
					  v_Process_Name,
					  v_Primary_Key_Value,
         				  v_err_code,
					  v_err_msg_text,
         				  v_err_descr
         				) ;

		  		v_Process_End_Date	:=  sysdate  ;

				IF v_Ins_Kount = 0 THEN
					v_Process_Status_Code := 'NO Records processed';
				END IF;

		  		Ins_Process_Log_SP
					(
					   v_Process_Name,
					   v_Process_Start_Date,
					   v_Process_End_Date,
					   v_Process_Status_Code,
					   v_Ins_Kount,
					   v_Err_Kount
		         		) ;

	        END ;  --  Closing END to EXCEPTION OTHERS BEGIN
	END; 	-- Procedure Body End

END InitLoad_PSIM_Traits_SP ; -- Main SP End (InitLoad_PSIM_Traits_SP)
/

GRANT EXECUTE ON PSIM.INITLOAD_PSIM_TRAITS_SP TO PSIM_R;

GRANT EXECUTE ON PSIM.INITLOAD_PSIM_TRAITS_SP TO PSIM_RW;
DROP PROCEDURE PSIM.BATCH_TAB_PRIVS;

CREATE OR REPLACE PROCEDURE PSIM.BATCH_TAB_PRIVS
 (GRANTEE STRING
 ,TAB_NAME_PAT STRING := '%'
 ,SELECT_PRIV BOOLEAN := TRUE
 ,REFERENCES_PRIV BOOLEAN := TRUE
 ,UPDATE_PRIV BOOLEAN := FALSE
 ,INSERT_PRIV BOOLEAN := FALSE
 ,DELETE_PRIV BOOLEAN := FALSE
 )
 IS

    priv_not_granted EXCEPTION;
    PRAGMA EXCEPTION_INIT(priv_not_granted, -1927);

    CURSOR matching_tables IS SELECT table_name FROM user_tables WHERE table_name LIKE UPPER(tab_name_pat);

    schema_owner  STRING(30);
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    -- Validate the grantee name
    SELECT username               -- Look for the grantee in the all_users view
      INTO schema_owner
      FROM all_users
     WHERE username = UPPER(grantee);

    -- Execute GRANT/REVOKE for

    FOR this_table IN matching_tables LOOP
       BEGIN
           IF select_priv THEN
              EXECUTE IMMEDIATE 'GRANT SELECT ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE SELECT ON ' || this_table.table_name || ' FROM ' || UPPER(grantee);
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       BEGIN
           IF references_priv THEN
              EXECUTE IMMEDIATE 'GRANT REFERENCES ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE REFERENCES ON ' || this_table.table_name || ' FROM ' || UPPER(grantee) ||
                                ' CASCADE CONSTRAINTS';
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       BEGIN
           IF update_priv THEN
              EXECUTE IMMEDIATE 'GRANT UPDATE ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE UPDATE ON ' || this_table.table_name || ' FROM ' || UPPER(grantee);
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       BEGIN
           IF insert_priv THEN
              EXECUTE IMMEDIATE 'GRANT INSERT ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE INSERT ON ' || this_table.table_name || ' FROM ' || UPPER(grantee);
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       BEGIN
           IF delete_priv THEN
              EXECUTE IMMEDIATE 'GRANT DELETE ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE DELETE ON ' || this_table.table_name || ' FROM ' || UPPER(grantee);
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       DBMS_OUTPUT.PUT_LINE('Privileges updated on ' ||this_table.table_name || ' for ' || UPPER(grantee) ||'.');
    END LOOP;

EXCEPTION
    WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE('Grantee: ' || UPPER(grantee) || ' is not a valid user on this database.');

    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/

GRANT EXECUTE ON PSIM.BATCH_TAB_PRIVS TO PSIM_R;

GRANT EXECUTE ON PSIM.BATCH_TAB_PRIVS TO PSIM_RW;
DROP PROCEDURE PSIM.BATCH_SEQ_PRIVS;

CREATE OR REPLACE PROCEDURE PSIM.BATCH_SEQ_PRIVS
 (GRANTEE STRING
 ,SEQ_NAME_PAT STRING := '%'
 ,SELECT_PRIV BOOLEAN := TRUE
 )
 IS

    priv_not_granted EXCEPTION;
    PRAGMA EXCEPTION_INIT(priv_not_granted, -1927);

    CURSOR matching_sequences IS SELECT sequence_name FROM user_sequences WHERE sequence_name LIKE UPPER(seq_name_pat);

    schema_owner  STRING(30);
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    -- Validate the grantee name
    SELECT username               -- Look for the grantee in the all_users view
      INTO schema_owner
      FROM all_users
     WHERE username = UPPER(grantee);

    -- Execute GRANT/REVOKE for

    FOR this_sequence IN matching_sequences LOOP
       BEGIN
           IF select_priv THEN
              EXECUTE IMMEDIATE 'GRANT SELECT ON ' || this_sequence.sequence_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE SELECT ON ' || this_sequence.sequence_name || ' FROM ' || UPPER(grantee);
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       DBMS_OUTPUT.PUT_LINE('Privileges updated on ' ||this_sequence.sequence_name || ' for ' || UPPER(grantee) ||'.');
    END LOOP;

EXCEPTION
    WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE('Grantee: ' || UPPER(grantee) || ' is not a valid user on this database.');

    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/

GRANT EXECUTE ON PSIM.BATCH_SEQ_PRIVS TO PSIM_R;

GRANT EXECUTE ON PSIM.BATCH_SEQ_PRIVS TO PSIM_RW;
