BEGIN
		      SELECT T.UNIT_RATE 
		  		INTO   :TRANSACTIONS_MST.CAST_PRIZE
  	  		FROM   TRANSACTIONS_MST T
  	  		WHERE  T.EFF_DT    = :BLOCK_PARAM.EFF_DT
  	  		AND    T.GRADE_CD  = :TRANSACTIONS_MST.GRADE_CD 
  	  		AND	   T.CR_TYPE   = 'C';
	  	 EXCEPTION
		  		WHEN NO_DATA_FOUND THEN
			  		:TRANSACTIONS_MST.CAST_PRIZE := NULL;
	  	 END;
	
	     BEGIN
		  		SELECT T.UNIT_RATE 
		  		INTO   :TRANSACTIONS_MST.ROLL_PRIZE
		  		FROM   TRANSACTIONS_MST T
  	  		WHERE  T.EFF_DT    = :BLOCK_PARAM.EFF_DT
  	  		AND    T.GRADE_CD  = :TRANSACTIONS_MST.GRADE_CD 
  	  		AND	   T.CR_TYPE   = 'R';  	  
	  	 EXCEPTION
		  		WHEN NO_DATA_FOUND 
		  		THEN :TRANSACTIONS_MST.ROLL_PRIZE := NULL;
	  	 END;	  	 

  CURSOR c_unit_rate(p_cr_type transactions_mst.cr_Type%TYPE) IS
  SELECT UNIT_RATE
  FROM   TRANSACTIONS_MST T
  WHERE  T.EFF_DT    = :BLOCK_PARAM.EFF_DT
  AND    T.GRADE_CD  = :TRANSACTIONS_MST.GRADE_CD 
  AND     T.CR_TYPE   = p_cr_type;

PROCEDURE PROC_DISPLAY_GRADE_CODE (COL_NAME VARCHAR2, ORDER_TYPE VARCHAR2, P_CALL NUMBER) IS
  CURSOR C1  IS
	  SELECT S.GRADE_CD,S.GRADE_DESC
	  FROM   STEEL_GRADE_MST S
	  WHERE  S.STATUS_FLAG='A'
	  AND    S.GRADE_CD NOT IN( 
				     SELECT DISTINCT T.GRADE_CD
				      FROM   TRANSACTIONS_MST T
				      WHERE  T.AUTH_BY IS NOT NULL 
				      AND    T.AUTH_DT IS NOT NULL
				      AND    T.EFF_DT = :BLOCK_PARAM.EFF_DT
				   )
    ORDER BY S.GRADE_CD;
																		DECODE(COL_NAME, 'GRADE_DESC', S.GRADE_DESC, null), null) DESC;
   
BEGIN
	GO_BLOCK('TRANSACTIONS_MST');
	FIRST_RECORD;
	FOR C IN C1 
	  LOOP
	   :TRANSACTIONS_MST.GRADE_CD   := C.GRADE_CD;
  	   :TRANSACTIONS_MST.GRADE_DESC := C.GRADE_DESC;  	   
	     
	     BEGIN
		      SELECT T.UNIT_RATE 
		      INTO   :TRANSACTIONS_MST.CAST_PRIZE
  	  	      FROM   TRANSACTIONS_MST T
  	  	      WHERE  T.EFF_DT    = :BLOCK_PARAM.EFF_DT
  	  	      AND    T.GRADE_CD  = :TRANSACTIONS_MST.GRADE_CD 
  	  	      AND    T.CR_TYPE   = 'C';
	  	 EXCEPTION
		      WHEN NO_DATA_FOUND THEN
			:TRANSACTIONS_MST.CAST_PRIZE := NULL;
	  	 END;
	
	     BEGIN
		  	SELECT T.UNIT_RATE 
		  	INTO   :TRANSACTIONS_MST.ROLL_PRIZE
		  	FROM   TRANSACTIONS_MST T
  	  		WHERE  T.EFF_DT    = :BLOCK_PARAM.EFF_DT
  	  		AND    T.GRADE_CD  = :TRANSACTIONS_MST.GRADE_CD 
  	  		AND	   T.CR_TYPE   = 'R';  	  
	  	 EXCEPTION
		  	WHEN NO_DATA_FOUND 
		  	THEN :TRANSACTIONS_MST.ROLL_PRIZE := NULL;
	  	 END;	  	 
	  
	     NEXT_RECORD;
	  END LOOP;
	
	FIRST_RECORD;
END;

CREATE OR REPLACE FUNCTION UNIT_RATE (V_EFF_DT IN DATE, V_GRADE_CD IN VARCHAR2(3), V_CR_TYPE IN VARCHAR2(2)) 
          RETURN number IS RATE NUMBER;
          BEGIN
            SELECT T.UNIT_RATE 
            INTO   RATE
	    FROM   TRANSACTIONS_MST T
  	    WHERE  T.EFF_DT    = V_EFF_DT
  	    AND    T.GRADE_CD  = V_GRADE_CD
  	    AND	   T.CR_TYPE   = V_CR_TYPE;
            RETURN RATE;
          EXCEPTION
		  		  WHEN NO_DATA_FOUND THEN
             DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
          END UNIT_RATE;