-- OBJECT TYPE:  PL/SQL
-- OBJECT NAME:  szpsact
-- AUTHOR:       RCLARK 
-- DATE WRITTEN: 10/10/08
--
-- INPUT:         
-- 
-- DESCRIPTION:   Update activity code for a given term
--
--When			Who		What
--10/1/2019		LZ		REQ0015487/TASK0019818 Added some type of of error logging in .log file for any issues with the szcsact.csv  file.
--
--
-- before migrating - comment out the line below.
-- tells TOAD not to scan for parmameters.
-- SET SCAN OFF;
set serveroutput ON SIZE UNLIMITED;
--set serveroutput ON;
SET echo OFF;
SET verify OFF;
SET feedback OFF;
SET tab OFF;

DECLARE

--variable declaration
  v_sysdate         date;
  v_term            stvterm.stvterm_code%TYPE;    --varchar2(30) := '200810';
  v_rpt_status      varchar2 (1):= 'b';
  v_spriden         spriden%ROWTYPE;
  v_line_out        VARCHAR(2048);                --output line for dbms
  v_act_code        varchar2(8);
  v_run_mode        varchar2(1):= 'A';
  v_commit          BOOLEAN :=FALSE;              --***  v_commit    BOOLEAN := TRUE;
  vcount            number := 0;
  f1                VARCHAR2(10) := '"';
  f2                varchar2(10) := '","';
  v_table_cnt       number := 0;
  v_count       	number := 0;

-- get count from  sgrsact - make sure valid DONE
CURSOR sgrsact_c1 (v_term varchar2)
IS
    SELECT COUNT (*) AS cnt
    FROM szcsact
    WHERE szcsact_term_code <> v_term
       OR szcsact_term_code IS NULL;

  
-- get report info from sgrsact and stvactc
CURSOR sgrsact_c2 (v_term   varchar2,
                   v_ACTP   varchar2)
IS
    SELECT SGRSACT.SGRSACT_PIDM, SGRSACT.SGRSACT_ACTC_CODE, SGRSACT.SGRSACT_TERM_CODE, STVACTC.STVACTC_CODE, STVACTC.STVACTC_ACTP_CODE
    FROM sgrsact, stvactc
    WHERE sgrsact.SGRSACT_ACTC_CODE = stvactc.STVACTC_CODE
    AND STVACTC.STVACTC_ACTP_CODE = v_ACTP
    AND SGRSACT.SGRSACT_TERM_CODE= v_term;
        
-- gets name and id from spriden
CURSOR spriden_c1 (v_pidm number)
IS
    --SELECT SPRIDEN.SPRIDEN_PIDM, SPRIDEN. SSPRIDEN_ID,PRIDEN.SPRIDEN_FIRST_NAME, SPRIDEN.SPRIDEN_LAST_NAME
    SELECT * 
    FROM spriden 
    WHERE spriden_pidm = v_pidm
    AND spriden_change_ind IS NULL;
    
-- gets name and id from spriden
CURSOR spriden_c2 (v_id spriden.spriden_id%TYPE)
IS
    --SELECT SPRIDEN.SPRIDEN_PIDM, SPRIDEN. SSPRIDEN_ID,PRIDEN.SPRIDEN_FIRST_NAME, SPRIDEN.SPRIDEN_LAST_NAME
    SELECT * 
    FROM spriden 
    WHERE spriden_id = v_id
    AND spriden_change_ind IS NULL;
	
spriden_r 	spriden_c2%rowtype;

CURSOR szcsact_c1  --change
IS
    SELECT *
    FROM szcsact;
  
--PROCEDURE p_print_report(v_term varchar2, v_rpt_status varchar2) IS 
--v_outline varchar2(2000);
--BEGIN
--
--
--    -- a report for current data for the term entered
--            -- WRITE heading line to file
--               v_line_out := f1 
--                            ||'TERM' ||f2
--                            ||'ID' ||f2
--                            ||'LAST' ||f2
--                            ||'FIRST' ||f2
--                            ||'CODE' 
--                            ||f1;
----               IF v_rpt_status = 'b' THEN
----                         gzpadmn.p_spool_lis('&1','&2','BEFORE','H',v_line_out,v_commit);
----                    ELSE
--                         gzpadmn.p_spool_lis('&1','&2',null,null,v_line_out,v_commit);
----                    END IF; 
--                    
--               /*
--               -- Write heading line to file
--               gzpadmn.p_spool_lis('&1','&2','','H',v_line_out,v_commit);
--                --***dbms_output.put_line(v_line_out);
--                */
--                            
--               
--               -- loops thru and writes the report data
--               FOR sgrsact_row IN sgrsact_c2 (v_term, v_act_code) LOOP
--                    v_spriden := NULL;
--                    OPEN spriden_c1 (sgrsact_row.sgrsact_pidm);
--                    FETCH spriden_c1 INTO v_spriden;
--                    CLOSE spriden_c1;
--                    
--                    
--                    v_line_out:=F1
--                                --|| sgrsact_row.sgrsact_pidm || F2
--                                || v_term || F2
--                                || v_spriden.spriden_id || F2
--                                || v_spriden.spriden_last_name || F2
--                                || v_spriden.spriden_first_name || F2
--                                || v_act_code
--                                || F1;
--                                
--                                
----                    IF v_rpt_status = 'b' THEN
----                         gzpadmn.p_spool_lis('&1','&2','BEFORE','T',v_line_out,v_commit);
----                    ELSE
--                         gzpadmn.p_spool_lis('&1','&2',null,null,v_line_out,v_commit);
----                    END IF;  
--                    
--                    
--                    --***dbms_output.put_line(v_line_out);
--                    vcount := vcount +1;
--                    
--               END LOOP;
--END p_print_report;

PROCEDURE p_table_update IS

		cursor is_set_cur(v_id  varchar2)
		is
			select 'Y' 
			from sgrsact
			where sgrsact_pidm = gb_common.f_get_pidm(v_id)
				and sgrsact_term_code = v_term
				and sgrsact_actc_code = v_act_code;
				
	   v_is_set     varchar2(1);        
  
	BEGIN 
		
		SELECT TRUNC(SYSDATE) INTO v_sysdate FROM DUAL;
		FOR szcsact_r IN szcsact_c1  LOOP -- change
		    
			v_count := 0;			
			
			--LZ 10/1/2019
			select count(1) into v_count
			FROM spriden 
			WHERE spriden_id = szcsact_r.szcsact_id
				AND spriden_change_ind IS NULL;
			
			spriden_r := Null;
			
			If v_count > 0 then
				 FOR spriden_r IN spriden_c2 (szcsact_r.szcsact_id)  LOOP 	
						v_is_set := 'N'; 
						open is_set_cur(szcsact_r.szcsact_id);
							fetch is_set_cur into v_is_set;
						close is_set_cur;
						
						if v_is_set = 'Y' then             
							gzpadmn.p_spool_lis('&1','&2',null,null,'Activity code ' || v_act_code || ' already set for ' || szcsact_r.szcsact_id 
										   || ',' || spriden_r.spriden_last_name  || ',' 
										   || spriden_r.spriden_first_name ,v_commit);            
						else            
							if UPPER(v_run_mode) = 'U' then                
								gzpadmn.p_spool_lis('&1','&2',null,null,'Activity code ' || v_act_code || ' set for ' || szcsact_r.szcsact_id 
														|| ',' || spriden_r.spriden_last_name  || ',' 
														|| spriden_r.spriden_first_name,v_commit);                                            
							else                
								gzpadmn.p_spool_lis('&1','&2',null,null,'Activity code ' || v_act_code || ' will be set for ' || szcsact_r.szcsact_id 
														|| ',' || spriden_r.spriden_last_name  || ',' 
														|| spriden_r.spriden_first_name,v_commit);                
							end if;                                            
							 
							BEGIN
								INSERT INTO sgrsact (SGRSACT_PIDM,SGRSACT_ACTC_CODE,SGRSACT_TERM_CODE,SGRSACT_ACTIVITY_DATE)
								 VALUES  (spriden_r.spriden_pidm,
										upper(v_act_code),
										szcsact_r.szcsact_term_code,
										v_sysdate);                
							EXCEPTION
								   WHEN OTHERS THEN 
										--verrmsg := SQLERRM;
										--verrcode := SQLCODE;
										DBMS_OUTPUT.PUT_LINE('Error found:'||f1|| spriden_r.spriden_last_name ||f1|| spriden_r.spriden_first_name ||f1||SQLERRM);            
							END;             
						end if;    
				 END LOOP; 
			Else
				DBMS_OUTPUT.PUT_LINE('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'); 
				DBMS_OUTPUT.PUT_LINE('Error found: ID = '||f1|| szcsact_r.szcsact_id ||f1|| ' is not exist. '); 
				DBMS_OUTPUT.PUT_LINE('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'); 
			End if;		
			
		END LOOP;
    
END p_table_update;
       
--PROCEDURE p_table_delete IS -- (v_term stvterm.stvterm_code%TYPE) IS
--BEGIN
--    DELETE 
--    FROM SGRSACT
--    WHERE SGRSACT_TERM_CODE = v_term --'200810'
--    AND SGRSACT_ACTC_CODE IN (SELECT DISTINCT STVACTC_CODE FROM stvactc
--                    WHERE STVACTC_ACTP_CODE = 'GREEK');
--    
--    
--    
--          
--END p_table_delete;  

BEGIN
		   DBMS_OUTPUT.ENABLE(NULL);
		   DBMS_OUTPUT.PUT_LINE('BEGINNING OF SZPSACT'||': '||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss'));
		   DBMS_OUTPUT.PUT_LINE('-');
		   
		   
		   --parms
		   v_term := gzpadmn.f_get_parm ('&1','&2','&4','01');
		   v_act_code := gzpadmn.f_get_parm ('&1','&2','&4','02');
		   v_run_mode := gzpadmn.f_get_parm ('&1','&2','&4','03');
		   
		   IF v_run_mode = 'A' THEN		   
				v_commit := TRUE;				
		   END IF;
		   
			--v_run_mode := upper(gzpadmn.f_get_parm ('&1','&2','&4','02'));   
			--***v_term:='200810';
		   
		   --write parm values to log file
		   dbms_output.put_line('Term: ' || v_term);
		   dbms_output.put_line('SACT Code:' || v_act_code);
		   dbms_output.put_line('Run Mode:' || v_run_mode);
		   
		   v_table_cnt := 0;
		   
		   SELECT COUNT (*) 
				INTO v_table_cnt
		   FROM szcsact;
			
			
		   FOR valid_count IN sgrsact_c1 (v_term) LOOP				 
				-- checks to make sure the term code enter is the same as the term code for each entry on the external spread sheet
				   IF valid_count.cnt <> 0 or v_table_cnt = 0 
				   THEN
					   IF valid_count.cnt <> 0 THEN            
						  dbms_output.put_line('Error found:  There is a term code (or NULL) in the external table that does not equal to the term entered -' || v_term); 
					   ELSIF v_table_cnt = 0 THEN
						  dbms_output.put_line('Error found:  The external table is empty.  Need to reimport the csv file');
					   END IF;					   
					ELSE
						p_table_update;						
				    END IF;				
		   END LOOP;
		   
		  IF UPPER(v_run_mode) = 'U' THEN
				 COMMIT;
		  ELSE
				 ROLLBACK;
		  END IF; 
			   
		   dbms_output.put_line('Records processed: ' || vcount);
		   DBMS_OUTPUT.PUT_LINE('-');
		   DBMS_OUTPUT.PUT_LINE('SZCSACT complete.');
		   DBMS_OUTPUT.PUT_LINE('-');
		   DBMS_OUTPUT.PUT_LINE('END OF SZCSACT ' ||TO_CHAR(SYSDATE,'dd-MON-yyyy - hh:mi:ss'));
			   
		 
EXCEPTION

	   WHEN OTHERS THEN 
			--verrmsg := SQLERRM;
			--verrcode := SQLCODE;
			DBMS_OUTPUT.PUT_LINE('Error found.'||f1||SQLERRM);
END;

/