DECLARE
   errmessage   NUMBER;
	 
BEGIN
   COCFP_VALIDATION;
   UPDATE_COCFP_TABLE;						

  
   IF :contr_stat = 'S' THEN
	 	  errmessage := msgbox('WARNING!','Contract is already tagged as "Matured"!' || CHR(10) || 'Printing will not continue unless authorizing passwords are provided...',1);
      GO_ITEM('rfs_password.employee');
   ELSE
      GENERATE_REPORT;
   END IF;
   
END;

PROCEDURE COCFP_VALIDATION IS
	GLOBAL_LOCATION VARCHAR2(30)	:= :global.location;
	GLOBAL_EMPLOYEE	VARCHAR2(30)	:= :global.emp_code;
	CONTRACT_NUMBER	NUMBER(8)			:= :CONTR_NO;
	CONTRACT_DIGIT	NUMBER(1)			:= :CHECK_DIGIT;
	PRODUCT_TYPE		VARCHAR2(30)	:= NVL(:PROD_TYPE,NULL);
	USER_REGION			VARCHAR2(30)	:= NVL(:REGION_NAME,NULL);
	FORM_NUMBER			VARCHAR2(30)	:= NVL(:FORM_NO,NULL);
BEGIN    
  VALIDATE_REQUIRED_FIELDS;
  VALIDATE_CONTRACT;
  VALIDATE_CFP_ISSUANCE;
  VALIDATE_RECON_FEE;
END;

PROCEDURE VALIDATE_REQUIRED_FIELDS IS
	USER_REGION			VARCHAR2(30)	:= NVL(:REGION,NULL);
	PRODUCT_TYPE		VARCHAR2(30)	:= NVL(:PROD_TYPE,NULL);
	FORM_NUMBER			VARCHAR2(30)	:= NVL(:FORM_NO,NULL);
	CONTRACT_NUMBER	NUMBER(8)			:= :CONTR_NO;
	CONTRACT_DIGIT	NUMBER(1)			:= :CHECK_DIGIT;
	ERRMESSAGE			NUMBER(1);

  ben_cnt								 number;
  enr_cnt                number;
  comp_cnt               number;
  pProd									 varchar2(1);
  pLump									 number(10,2);
  pTS										 number(2);
pCompNo                varchar2(15);


BEGIN 
	IF USER_REGION IS NULL THEN
		ERRMESSAGE := msgbox('WARNING','Required field parameter missing!' || CHR(10) || 'Please input a REGION NAME...',1);
		Raise Form_Trigger_Failure;
	END IF;
	
	IF PRODUCT_TYPE IS NULL THEN
		ERRMESSAGE := msgbox('WARNING','Required field parameter missing!' || CHR(10) || 'Please input a PRODUCT TYPE...',1);
		RAISE FORM_TRIGGER_FAILURE;
	END IF;
	
	IF CONTRACT_NUMBER IS NULL THEN
		ERRMESSAGE := msgbox('WARNING','Required field parameter missing!' || CHR(10) || 'Please input a CONTRACT NO...',1);
		RAISE FORM_TRIGGER_FAILURE;
	END IF;
	
	IF CONTRACT_DIGIT IS NULL THEN
		ERRMESSAGE := msgbox('WARNING','Required field parameter missing!' || CHR(10) || 'Please input a CHECK DIGIT...',1);
		RAISE FORM_TRIGGER_FAILURE;
	END IF;
	
	IF FORM_NUMBER IS NULL THEN
		ERRMESSAGE := msgbox('WARNING','Required field parameter missing!' || CHR(10) || 'Please input a FORM NUMBER...',1);
		RAISE FORM_TRIGGER_FAILURE;

  else
		 begin
		 	  select cust_prod_type,
			         cust_lumpsum_amt,
			         cust_ts_code,  
			         cust_ctrl_no   
			    into pProd,
			         pLump,
			         pTS,
			         pCompNo     
			    from customer
			   where cust_contr = :CONTR_NO;
		 end;  

     select count(*)
       into ben_cnt
       from benef
      where bene_contr = :CONTR_NO;

     select count(*)
       into comp_cnt
       from company
      where comp_code = pCompNo;
                    
     if (pTS <> 3 and nvl(ben_cnt,0) = 0) or (pTS = 3 and comp_cnt = 0) then
   	 	  ERRMESSAGE := msgbox('WARNING','CFP cannot be printed! There''s no BENEFICIARY ...',1); 
        raise form_trigger_failure;
     end if;

     if pProd = 'E' then
        select count(*)
          into enr_cnt
          from enrollee
         where enro_contr = :CONTR_NO;
           
        if nvl(enr_cnt,0) = 0 then
     	     ERRMESSAGE := msgbox('WARNING','CFP cannot be printed! There''s no ENROLLEE ...',1); 
           raise form_trigger_failure;
        end if;
     end if;
        
     if pProd = 'T' and nvl(pLump,0) = 0 then
      	ERRMESSAGE := msgbox('WARNING','CFP cannot be printed! Lumpsum Amount value is NULL or 0 ...',1); 
        raise form_trigger_failure;
     end if;

      
	END IF;	
END;

PROCEDURE VALIDATE_CONTRACT IS
	CONTRACT_EXIST			VARCHAR2(1)		:= NULL;
	CONTRACT_NUMBER			NUMBER(10)		:= :CONTR_NO;
	CONTRACT_DIGIT			NUMBER(1)		:= :CHECK_DIGIT;
	BUTTON_OPTION			NUMBER(1)		:= :RG_OPTION;
	USER_REGION			VARCHAR2(30)	        := NVL(:REGION,NULL);
	PRODUCT_TYPE			VARCHAR2(1)		:= NVL(:PROD_TYPE,NULL);
	PLAN_PACKAGE			VARCHAR2(1);
	PRODUCT_CATEGORY		NUMBER(14);		
	ERRMESSAGE			NUMBER(1);	
	CONTRACT_REGION			VARCHAR2(30) 	:= NULL;
	CONTRACT_PRODUCT		VARCHAR2(1)		:= NULL;
	CONTRACT_STATUS			VARCHAR2(1)		:= NULL;
	CONTRACT_ADDRESS		VARCHAR2(100)	:= NULL;
	CONTRACT_CFPSTATUS	        VARCHAR2(30);
	CONTRACT_FCSTATUS		VARCHAR2(30);
	CONTRACT_GROSSPRICE             NUMBER;
	CONTRACT_PAYTOTAL		NUMBER(12,2)	:= 0.00;
	V_CODE                          NUMBER(2);
	BENEF_EXIST                     VARCHAR2(1);
		
	CURSOR GET_CONTRACT_DETAILS(p_contract NUMBER) IS
		SELECT CUST_LOCATION, CUST_PROD_TYPE, CUST_STATUS_INDICATOR,
					 CUST_CFPCO_ISSUED_INDICATOR, CUST_FCONTRACT_ISSUED_DT, CUST_GROSS_PRICE, CUST_HOME_STREET,CUST_PROD_CATEGORY, CUST_PLAN_PACK
		FROM	 CUSTOMER
		WHERE	 CUST_CONTR = p_contract;
	
	CURSOR GET_FULLYPAID_AMOUNT(p_contract NUMBER) IS	
		SELECT SUM(NVL(PAY_CASH_AMT,0))+ SUM(NVL(PAY_CHECK_AMT,0))+
				 	 SUM(NVL(PAY_CCARD_AMT,0))+ SUM(NVL(PAY_DISCOUNT_AMT,0)) AS PAY_AMT		
		from	 VW_PYMNT_ALL
		where	 PAY_CONTR = p_contract
		and		 PAY_DOC_STAT IN ('2','C','5','H','I');	
		-- credited, credit memo,RetCheq-Repl,cm credited, & cm transferred		
		   	
BEGIN
     			
	BEGIN		
		SELECT 'Y'
		INTO	 CONTRACT_EXIST
		FROM 	 CUSTOMER
		WHERE  CUST_CONTR 			= CONTRACT_NUMBER;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			CONTRACT_EXIST	:= 'N';
	END;	
					
	IF CONTRACT_EXIST = 'N' THEN
		ERRMESSAGE	:= msgbox('ERROR(S) FOUND','This Contract Number <' || CONTRACT_NUMBER || '> is invalid and was not found on the database!',1);
		:CHECK_DIGIT 			:= NULL;															
		GO_ITEM('CONTR_NO');
		RAISE FORM_TRIGGER_FAILURE;				
	END IF;
				
	BEGIN		
		SELECT 'Y'
		INTO	 CONTRACT_EXIST
		FROM 	 CUSTOMER
		WHERE  CUST_CONTR 			= CONTRACT_NUMBER
		AND 	 CUST_CHECK_DIGIT = CONTRACT_DIGIT;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			CONTRACT_EXIST	:= 'N';
	END;	
				
	IF CONTRACT_EXIST = 'N' THEN
		ERRMESSAGE	:= msgbox('ERROR(S) FOUND','Contract Number and Verification No. matching failed!' || CHR(10) || 'This Contract < ' || CONTRACT_NUMBER || ' - ' || CONTRACT_DIGIT || ' > is invalid.',1);							
		:CHECK_DIGIT 			:= NULL;								
		GO_ITEM('CHECK_DIGIT');
		RAISE FORM_TRIGGER_FAILURE;				
	END IF;
      
	BEGIN
		SELECT COUNT(*)
	    INTO	 BENEF_EXIST
		  FROM 	 BENEF
		 WHERE   BENE_CONTR 			= CONTRACT_NUMBER;
	END;
	
	IF BENEF_EXIST = 0  THEN
			 SELECT CUST_TS_CODE 
			   INTO V_CODE
			   FROM CUSTOMER
			  WHERE CUST_CONTR = CONTRACT_NUMBER;
		 
		 IF NVL(V_CODE,1) <> 3 THEN	
			  ERRMESSAGE	:= msgbox('ERROR(S) FOUND','No Beneficiary found!' || CHR(10) || 'Printing will not continue.',1);							
		  	RAISE FORM_TRIGGER_FAILURE;	
		 END IF;	
	END IF;
			

	OPEN GET_CONTRACT_DETAILS(CONTRACT_NUMBER);
	FETCH GET_CONTRACT_DETAILS INTO CONTRACT_REGION, CONTRACT_PRODUCT, CONTRACT_STATUS, CONTRACT_CFPSTATUS, CONTRACT_FCSTATUS, CONTRACT_GROSSPRICE, CONTRACT_ADDRESS, PRODUCT_CATEGORY, PLAN_PACKAGE;
	IF GET_CONTRACT_DETAILS%NOTFOUND THEN
		ERRMESSAGE	:= msgbox('WARNING','Contract does not exists!',1);
		RAISE FORM_TRIGGER_FAILURE;
	ELSE		
		IF CONTRACT_REGION <> USER_REGION THEN 
			   ERRMESSAGE := msgbox('WARNING','You cannot print CFP with different Region. Please try again...',1);
			   RAISE FORM_TRIGGER_FAILURE;
		END IF;
		
		IF CONTRACT_PRODUCT <> PRODUCT_TYPE THEN
			ERRMESSAGE := msgbox('WARNING!','You cannot print contract with different Product Type. Please try again...',1);
			RAISE FORM_TRIGGER_FAILURE;
		END IF;
		
		IF CONTRACT_ADDRESS IN ('N/A','NONE') THEN
			ERRMESSAGE := msgbox('WARNING!','No Planholder Address specified for this contract' || CHR(10) || 'Please correct first planholder''s address.',1);
			RAISE FORM_TRIGGER_FAILURE;
		END IF;

		IF CONTRACT_STATUS <> '4' THEN
		IF CONTRACT_STATUS NOT IN ('4','S') THEN
			ERRMESSAGE := msgbox('WARNING!','Contract Status should be <Fully Paid>!' || CHR(10) || 'Reconstruction of CFP shall not continue...',1);
			RAISE FORM_TRIGGER_FAILURE;			
		END IF; 
		
	IF :GLOBAL.LOCATION <> 'CUSTSERV' THEN
		 IF BUTTON_OPTION = 0 OR PRODUCT_CATEGORY = 204 THEN
		 		ERRMESSAGE:= msgbox('WARNING!','Reconstrucion of CFP for FFR account is not allowed. Only the Customer Service Department is authorized for FFR accounts!',1);				
				RAISE FORM_TRIGGER_FAILURE;
		 ELSIF BUTTON_OPTION = 1 OR PLAN_PACKAGE = 's'  THEN		
		 	  ERRMESSAGE:= msgbox('WARNING!','Reconstrucion of CFP for SCP account is not allowed. Only the Customer Service Department is authorized for SCP accounts!',1);				
				RAISE FORM_TRIGGER_FAILURE;
		 ELSIF BUTTON_OPTION = 3 OR PLAN_PACKAGE = 'C'  THEN		
		 	  ERRMESSAGE:= msgbox('WARNING!','Reconstrucion of CFP for TOP2 account is not allowed. Only the Customer Service Department is authorized for TOP2 accounts!',1);				
				RAISE FORM_TRIGGER_FAILURE;		
		 END IF;
	ELSE		 
		IF BUTTON_OPTION = 0 THEN
			IF PRODUCT_CATEGORY <> 204 THEN
					BUTTON_OPTION := 2;
					ERRMESSAGE:= msgbox('WARNING!','You cannot print CFP with different category. ',1);				
					RAISE FORM_TRIGGER_FAILURE;
			END IF;		
		ELSIF BUTTON_OPTION = 1 THEN
			IF PLAN_PACKAGE <> 's' THEN
					BUTTON_OPTION := 2;
					ERRMESSAGE:= msgbox('WARNING!','You cannot print CFP with different category.',1);				
					RAISE FORM_TRIGGER_FAILURE;								
			END IF;	
		ELSIF BUTTON_OPTION = 2 THEN
			 IF PLAN_PACKAGE IN ('C','s') OR PRODUCT_CATEGORY = 204 THEN
			    BUTTON_OPTION := 2;
          ERRMESSAGE:= msgbox('WARNING!','You cannot print CFP with different category.',1);				
          RAISE FORM_TRIGGER_FAILURE;												
			END IF; 
		ELSIF BUTTON_OPTION = 3 THEN
			IF PLAN_PACKAGE <> 'C' THEN
				 BUTTON_OPTION := 2;
				 ERRMESSAGE:= msgbox('WARNING!','You cannot print CFP with different category.',1);				
			   RAISE FORM_TRIGGER_FAILURE;												
			END IF;	
		END IF;
	END IF;	
		
	END IF;
	CLOSE GET_CONTRACT_DETAILS;	     
	
	OPEN GET_FULLYPAID_AMOUNT(CONTRACT_NUMBER);
	FETCH GET_FULLYPAID_AMOUNT INTO CONTRACT_PAYTOTAL;
	IF GET_FULLYPAID_AMOUNT%NOTFOUND THEN
		ERRMESSAGE	:= msgbox('WARNING!','No payment record(s) found in the database!' || CHR(10) || 'Please contact the Systems Development Department',1);
		RAISE FORM_TRIGGER_FAILURE;
	ELSE		
		IF CONTRACT_PAYTOTAL < CONTRACT_GROSSPRICE THEN
			ERRMESSAGE := msgbox('WARNING!', 'This Plan is not yet Fully Paid.' || CHR(10) ||'Reconstruction Printing will not be allowed...',1);
			RAISE FORM_TRIGGER_FAILURE;
		END IF;
	END IF;
	CLOSE GET_FULLYPAID_AMOUNT; 		
END;

PROCEDURE VALIDATE_CFP_ISSUANCE IS
	CONTRACT_NUMBER				NUMBER(10)		:= :CONTR_NO;
	CONTRACT_DIGIT				NUMBER(1)			:= :CHECK_DIGIT;
	USER_REGION						VARCHAR2(30)	:= NVL(:REGION,NULL);
	PRODUCT_TYPE					VARCHAR2(1)		:= NVL(:PROD_TYPE,NULL);
	FORM_NUMBER						VARCHAR2(30)	:= NVL(:FORM_NO,NULL);
	
	FLAG_CFPNO						VARCHAR2(1)		:= NULL;	
	FLAG_FORMNO_NEW    		VARCHAR2(1)		:= NULL;
	FLAG_FORMNO_RECON			VARCHAR2(1)		:= NULL;
	FLAG_UNTAGGING_NEW		VARCHAR2(1)		:= NULL;
	FLAG_UNTAGGING_RECON	VARCHAR2(1)		:= NULL;	
	ERRMESSAGE						NUMBER(1);
	
	CURSOR VALIDATE_CFPNO (p_contract NUMBER) IS
		SELECT 	'x'
		FROM 		COCFP
		WHERE		CFP_CONTR = p_contract; 	

	CURSOR VALIDATE_FORMNO_NEW	(p_formno VARCHAR2) IS
		SELECT 	'x'
		FROM 		COCFP
		WHERE		CFP_FORM_NR = p_formno; 
		
	CURSOR VALIDATE_FORMNO_RECON(p_formno VARCHAR2) IS
		SELECT 	'x'
		FROM 		COCFP_ADD
		WHERE		ACFP_FORM_NR = p_formno; 

	CURSOR VALIDATE_UNTAGGING_NEW(p_formno VARCHAR2) IS
		SELECT 	'x'
		FROM 		COCFP
		WHERE		CFP_FORM_NR = p_formno
		AND			CFP_REMARKS	= 'UNTAGGING'; 	
		
	CURSOR VALIDATE_UNTAGGING_RECON(p_formno VARCHAR2) IS
		SELECT 	'x'
		FROM 		COCFP_ADD
		WHERE		ACFP_FORM_NR = p_formno
		AND			ACFP_REMARKS	= 'UNTAGGING'; 			
		
BEGIN
  OPEN VALIDATE_CFPNO(CONTRACT_NUMBER);
  FETCH VALIDATE_CFPNO INTO FLAG_CFPNO;
  IF VALIDATE_CFPNO%NOTFOUND THEN
  	ERRMESSAGE := msgbox('WARNING!','This contract has not yet been issued with a Certificate of Full Payment!' || CHR(10) || 'Reconstruction printing will not continue...',1);
  	RAISE FORM_TRIGGER_FAILURE;
  ELSE
  	OPEN VALIDATE_FORMNO_NEW(FORM_NUMBER);
  	FETCH VALIDATE_FORMNO_NEW INTO FLAG_FORMNO_NEW;
  	IF VALIDATE_FORMNO_NEW%FOUND THEN
  		OPEN VALIDATE_UNTAGGING_NEW(FORM_NUMBER);
  		FETCH VALIDATE_UNTAGGING_NEW INTO FLAG_UNTAGGING_NEW;
  		IF VALIDATE_UNTAGGING_NEW%NOTFOUND THEN
  			ERRMESSAGE := msgbox('WARNING','Duplicate Form number found in the database!'	|| CHR(10) || 'The form number you entered has been used!' || CHR(10) || 'Please verify the form number you entered or contact the Systems development Department for help!',1); 
  			RAISE FORM_TRIGGER_FAILURE;
  		END IF;
  		CLOSE VALIDATE_UNTAGGING_NEW;	 		  	
  	END IF;
  	CLOSE VALIDATE_FORMNO_NEW;
  	
  	
  	OPEN VALIDATE_FORMNO_RECON(FORM_NUMBER);
  	FETCH VALIDATE_FORMNO_RECON INTO FLAG_FORMNO_RECON;
  	IF VALIDATE_FORMNO_RECON%FOUND THEN
  		OPEN VALIDATE_UNTAGGING_RECON(FORM_NUMBER);
  		FETCH VALIDATE_UNTAGGING_RECON INTO FLAG_UNTAGGING_RECON;
  		IF VALIDATE_UNTAGGING_RECON%NOTFOUND THEN
  			ERRMESSAGE := msgbox('WARNING','Duplicate Form number found in the database!'	|| CHR(10) || 'The form number you entered has been used!' || CHR(10) || 'Please verify the form number you entered or contact the Systems development Department for help!',1); 
  			RAISE FORM_TRIGGER_FAILURE;
  		END IF;
  		CLOSE VALIDATE_UNTAGGING_RECON;	  		
  	END IF;
  	CLOSE VALIDATE_FORMNO_RECON;
  END IF;
  CLOSE VALIDATE_CFPNO;  
END;

PROCEDURE VALIDATE_RECON_FEE IS
	GLOBAL_EMPLOYEE	VARCHAR(30)	:= :global.emp_code;
	GLOBAL_LOCATION	VARCHAR(30) := :global.location;
	CONTRACT_NUMBER	NUMBER(8) 	:= :CONTR_NO;
	CONTRACT_DIGIT	NUMBER(1) 	:= :CHECK_DIGIT;	
	FLAG_RECON_FEE	VARCHAR2(1);
	FLAG_TRANS_STAT	VARCHAR2(1);
	ERRMESSAGE			NUMBER(1);
	V_DATE          DATE;
	V_REASON        VARCHAR2(1);
	
	CURSOR CHECK_RECONFEE(p_contr NUMBER) IS
		SELECT 'x'
		FROM	 PYMNT
		WHERE	 PAY_CONTR = p_contr
		AND		 PAY_DOC_TYPE IN ('7','8') 					
		AND		 PAY_DOC_STAT IN ('2','C','H','I')
		AND		 PAY_REMARKS IN ('RECONSTRUCTION FEE','TRANSFER FEE','TRANSFER & ENRO SUBST FEE','ENRO SUBST FEE');
		
	CURSOR CHECK_TRANSFER_STATUS(p_contr NUMBER) IS     
	  SELECT MAX(tran_created_dt)	  
		FROM 	 TRANSFER
		WHERE  TRAN_CONTR  = p_contr AND
					 TRAN_REASON in ('D','U');	
BEGIN
  OPEN CHECK_RECONFEE(CONTRACT_NUMBER);
  FETCH CHECK_RECONFEE INTO FLAG_RECON_FEE;
  IF CHECK_RECONFEE%NOTFOUND AND FLAG_RECON_FEE IS NULL THEN  	
		OPEN CHECK_TRANSFER_STATUS(CONTRACT_NUMBER);
		FETCH CHECK_TRANSFER_STATUS INTO V_DATE;		
		IF V_DATE IS NULL THEN
			ERRMESSAGE	:= msgbox('WARNING!','Plan Holder has not yet paid a Reconstruction Fee...',1);
			RAISE FORM_TRIGGER_FAILURE; 
		ELSE
			SELECT TRAN_REASON 
			  INTO V_REASON 
			  FROM TRANSFER
			 WHERE TRAN_CONTR = :CONTR_NO;
			 
			IF V_REASON = 'D' THEN
				UPDATE TRANSFER
				   SET TRAN_REASON = 'P'
				 WHERE TRAN_CONTR = CONTRACT_NUMBER
				   AND TRAN_CREATED_DT = V_DATE
				   AND TRAN_REASON = 'D';
			ELSIF V_REASON = 'U' THEN	    
				UPDATE TRANSFER
				   SET TRAN_REASON = 'Q'
				 WHERE TRAN_CONTR = CONTRACT_NUMBER
				   AND TRAN_CREATED_DT = V_DATE
				   AND TRAN_REASON = 'U';
			END IF;	   
		END IF;	   
		CLOSE CHECK_TRANSFER_STATUS;
  END IF;
  CLOSE CHECK_RECONFEE;
END;