FREE AI-Enhanced Online Oracle PLSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE TEMP_DEMDET_TARIF
   ("DD_REF" NUMBER, 
   "DD_EQP_SIZE" VARCHAR2(2 BYTE), 
	"DD_EQP_TYPE" VARCHAR2(2 BYTE), 
    "DD_DEL_COUNTRY" VARCHAR2(2 BYTE),
    "DD_REC_COUNTRY" VARCHAR2(2 BYTE),
    "DD_PARTNER_CODE" VARCHAR2(10 BYTE) );

CREATE TABLE tab_mhd_tariff_dnd_param
(
	DD_REF NUMBER,
	DD_PRICE NUMBER
);

-- Create a diagnosing table where the function will store its ordered candidates:
CREATE TABLE DEM_REF_DIAG AS
	SELECT
		CAST(NULL AS VARCHAR2(2000)) AS S_PARAM,
		t.*,
		0 AS S_PARTNER_CODE,
		0 AS S_EQP_SIZE,
		0 AS S_EQP_TYPE,
		0 AS S_DEL_COUNTRY, 
		0 AS S_REC_COUNTRY
	FROM TEMP_DEMDET_TARIF t
	WHERE ROWNUM = 0;

Create OR REPLACE function Fun_Dem_Ref (P_PARTNER_CODE in varchar2 , P_del_country in varchar2,P_rec_country in varchar2,
  P_cnSize    in varchar2 , P_cnType       in varchar2)
     RETURN VARCHAR2 IS
     result VARCHAR2(50);
    CNT2 NUMBER; CNT3  NUMBER;CNT4 NUMBER;CNT5 NUMBER;CNT6 NUMBER;CNT7 NUMBER;CNT8 NUMBER;
    CNT9  NUMBER;   CNT10  NUMBER;  CNT11  NUMBER; CNT12  NUMBER;CNT13 NUMBER; CNT14  NUMBER; CNT15  NUMBER;  
     CNT16  NUMBER; CNT17  NUMBER; CNT18  NUMBER; CNT19  NUMBER;N_DD_REF       NUMBER;VAR_DND_TYPE VARCHAR2(2);
VAR_DND_SIZE VARCHAR2(2);
VAR_DND_DEL_COUNTRY VARCHAR2(2);
VAR_DND_ACT_TYPE VARCHAR2(10); -- guessing from usage
CNTR1 NUMBER;
CNTR2 NUMBER;
endDate DATE := SYSDATE;
    begin 
   
dbms_output.put_line('24');
     --IF CNT2 > 0 THEN
            SELECT COUNT(*) INTO CNT3 FROM TEMP_DEMDET_TARIF 
            WHERE DD_PARTNER_CODE =P_PARTNER_CODE AND   DD_EQP_SIZE     = P_cnSize
           AND  DD_EQP_TYPE     = P_cnType AND   DD_DEL_COUNTRY  = P_del_country
           AND   DD_REC_COUNTRY  = P_rec_country;
     IF CNT3 =1  THEN       
--dbms_output.put_line('31');
     SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF 
            WHERE   DD_PARTNER_CODE =P_PARTNER_CODE AND   DD_EQP_SIZE     = P_cnSize
           AND  DD_EQP_TYPE     = P_cnType AND   DD_DEL_COUNTRY  = P_del_country
           AND   DD_REC_COUNTRY  = P_rec_country;
            result:= N_DD_REF;     
            END IF;
      IF CNT3=0 THEN
          SELECT COUNT(*) INTO CNT4 FROM TEMP_DEMDET_TARIF ;
       IF CNT4 >0 THEN   SELECT COUNT(*) INTO CNT5 FROM TEMP_DEMDET_TARIF
                 WHERE  DD_PARTNER_CODE =P_PARTNER_CODE;
          IF CNT5 >0 THEN  SELECT COUNT(*) INTO CNT6 FROM TEMP_DEMDET_TARIF
                    WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                     AND   DD_EQP_SIZE     = P_cnSize;
--dbms_output.put_line('43');
          IF CNT6 >0 THEN   SELECT COUNT(*) INTO CNT6 FROM TEMP_DEMDET_TARIF
                    WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                     AND   DD_EQP_SIZE     = P_cnSize
                     AND   DD_EQP_TYPE     = P_cnType;   
       
              IF CNT7>0 THEN
--dbms_output.put_line('52');
                           VAR_DND_TYPE := P_cnType;
                           SELECT COUNT(*) INTO CNT8 FROM TEMP_DEMDET_TARIF
                          WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                           AND   DD_EQP_SIZE     = P_cnSize
                           AND   DD_EQP_TYPE     = VAR_DND_TYPE
                           AND   DD_DEL_COUNTRY  = P_del_country;    
                IF CNT8 >0 THEN VAR_DND_DEL_COUNTRY := P_del_country; 
--dbms_output.put_line('60');
                   ELSE 
--dbms_output.put_line('62');
                   VAR_DND_DEL_COUNTRY := '+';        
                
                SELECT COUNT(*) INTO CNT9 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY;
                 IF CNT9 = 1 THEN     SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY;   
              result:= N_DD_REF;   
              else 
--dbms_output.put_line('77');
              SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                               
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
           ELSE  
--dbms_output.put_line('94');
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                             AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE 
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+'
                            AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                          END IF;
                        END IF;
                 END IF;  
            END IF;  
--dbms_output.put_line('114');
     ELSE
--dbms_output.put_line('116');
    VAR_DND_TYPE := '+';
    SELECT COUNT(*) INTO CNT10 FROM TEMP_DEMDET_TARIF
                          WHERE  DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY; 
      IF CNT10>0 THEN
--dbms_output.put_line('123');
                              VAR_DND_DEL_COUNTRY := P_del_country;
               SELECT COUNT(*) INTO CNT11 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE =P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY;
       IF CNT11 = 1 THEN
        SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE =P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY;
                              result:= N_DD_REF;
            ELSE
            SELECT  COUNT(*) INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                               
                                    SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
                                 ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
                          
                      result:= N_DD_REF;
                END IF;
           END IF;
         END IF;  
         
               ELSE
--dbms_output.put_line('174');
                              VAR_DND_DEL_COUNTRY := '+';
               SELECT COUNT(*) INTO CNT12 FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY ;
                 IF CNT12 = 1 THEN
                  SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY; 
                 result:= N_DD_REF;
                                 ELSE
                SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
                                 ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                      END IF;
                     END IF;
                   END IF;
               END IF;
              END IF;
                
             ELSE
--dbms_output.put_line('225');
                        VAR_DND_SIZE := '+';
              SELECT COUNT(*) INTO CNT13 FROM TEMP_DEMDET_TARIF 
                       WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                        AND   DD_EQP_SIZE     = VAR_DND_SIZE
                        AND   DD_EQP_TYPE     = P_cnType;
                 IF CNT13>=1 THEN
                        SELECT COUNT(*) INTO CNT14 FROM TEMP_DEMDET_TARIF
                           WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                          AND DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     =  P_cnType
                           AND   DD_DEL_COUNTRY  = P_del_country;
           IF CNT14 >=1 THEN
                              SELECT  COUNT(*) INTO CNT15 FROM TEMP_DEMDET_TARIF
                           WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                          AND  DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     =  P_cnType
                           AND   DD_DEL_COUNTRY  = P_del_country;  
                      IF CNT15 = 1 THEN
                              SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                          WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                          AND DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     =  P_cnType
                           AND   DD_DEL_COUNTRY  = P_del_country;  
                     result:= N_DD_REF;
                                 ELSE 
                  SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
                  
          ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = P_del_country 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                 END IF;
           END IF;
           END IF;
   ELSE    
--dbms_output.put_line('285');
       VAR_DND_DEL_COUNTRY := '+';
       SELECT COUNT(*) INTO CNT16 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY ;
        IF CNT16 = 1 THEN     
          SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY; 
                  result:= N_DD_REF;
                                 ELSE       
                                 SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
          ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                 END IF;
                 END IF; 
                 END IF;
                 END IF;
                 
      ELSE
--dbms_output.put_line('335');
       VAR_DND_TYPE := '+';
          SELECT COUNT(*) INTO CNT17 FROM TEMP_DEMDET_TARIF
                          WHERE  DD_PARTNER_CODE =P_PARTNER_CODE
                           AND   DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     = VAR_DND_TYPE
                           AND   DD_DEL_COUNTRY  = P_del_country;
          IF CNT17 >=1 THEN        
                           SELECT COUNT(*) INTO CNT18 FROM TEMP_DEMDET_TARIF
                          WHERE  DD_PARTNER_CODE =P_PARTNER_CODE
                           AND   DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     = VAR_DND_TYPE
                           AND   DD_DEL_COUNTRY  = P_del_country;
       IF CNT18 = 1 THEN
                                 SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE =P_PARTNER_CODE
                           AND   DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     = VAR_DND_TYPE
                           AND   DD_DEL_COUNTRY  = P_del_country;
                  result:= N_DD_REF;
                    ELSE      
                                    SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
          ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = P_del_country 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                 END IF; 
                 END IF;
                 END IF;
         ELSE   
               VAR_DND_DEL_COUNTRY := '+';  
               SELECT COUNT(*) INTO CNT19 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY ;
        IF CNT19 = 1 THEN     
          SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY; 
                  result:= N_DD_REF;
                                 ELSE       
                                 SELECT  COUNT(*)INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
          ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                  END IF;
              END IF;
            END IF;
        END IF;
      END IF;
   END IF;
         ELSE 
                      SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE =  '+'
                                 AND   DD_EQP_SIZE     =  '+'
                                 AND   DD_EQP_TYPE     =  '+'
                                 AND   DD_DEL_COUNTRY  = '+'
                                AND   DD_REC_COUNTRY  = '+';
               
     END IF;
            END IF;
         END IF;
      --END IF;

RETURN result;
END Fun_Dem_Ref;
/

SELECT * FROM user_errors;

Create OR REPLACE function Fun_Dem_Ref2 (P_PARTNER_CODE in varchar2 , P_del_country in varchar2,P_rec_country in varchar2,
  P_cnSize    in varchar2 , P_cnType       in varchar2)
     RETURN VARCHAR2 IS
	PRAGMA AUTONOMOUS_TRANSACTION; -- To allow the INSERT to the diagnosing table DEM_REF_DIAG.
     result VARCHAR2(50);
    CNT2 NUMBER; CNT3  NUMBER;CNT4 NUMBER;CNT5 NUMBER;CNT6 NUMBER;CNT7 NUMBER;CNT8 NUMBER;
    CNT9  NUMBER;   CNT10  NUMBER;  CNT11  NUMBER; CNT12  NUMBER;CNT13 NUMBER; CNT14  NUMBER; CNT15  NUMBER;  
     CNT16  NUMBER; CNT17  NUMBER; CNT18  NUMBER; CNT19  NUMBER;N_DD_REF       NUMBER;VAR_DND_TYPE VARCHAR2(2);
VAR_DND_SIZE VARCHAR2(2);
VAR_DND_DEL_COUNTRY VARCHAR2(2);
VAR_DND_ACT_TYPE VARCHAR2(10); -- guessing from usage
CNTR1 NUMBER;
CNTR2 NUMBER;
endDate DATE := SYSDATE;
    begin 
   
    INSERT INTO DEM_REF_DIAG
	SELECT
		P_PARTNER_CODE||'/'||P_del_country||'/'||P_rec_country||'/'||P_cnSize||'/'||P_cnType,
		t.*,
        CASE WHEN DD_PARTNER_CODE = P_PARTNER_CODE THEN 100 END AS S_PARTNER_CODE,
		CASE WHEN DD_EQP_SIZE = P_cnSize THEN 100 END AS S_EQP_SIZE,
		CASE WHEN DD_EQP_TYPE = P_cnType THEN 100 END AS S_EQP_TYPE,
		CASE WHEN DD_DEL_COUNTRY = P_del_country THEN 100 END AS S_DEL_COUNTRY,
		CASE WHEN DD_REC_COUNTRY = P_rec_country THEN 100 END AS S_REC_COUNTRY
	FROM TEMP_DEMDET_TARIF t;
     --IF CNT2 > 0 THEN
            SELECT COUNT(*) INTO CNT3 FROM TEMP_DEMDET_TARIF 
            WHERE DD_PARTNER_CODE =P_PARTNER_CODE AND   DD_EQP_SIZE     = P_cnSize
           AND  DD_EQP_TYPE     = P_cnType AND   DD_DEL_COUNTRY  = P_del_country
           AND   DD_REC_COUNTRY  = P_rec_country;
     IF CNT3 =1  THEN
dbms_output.put_line('31');
     SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF 
            WHERE   DD_PARTNER_CODE =P_PARTNER_CODE AND   DD_EQP_SIZE     = P_cnSize
           AND  DD_EQP_TYPE     = P_cnType AND   DD_DEL_COUNTRY  = P_del_country
           AND   DD_REC_COUNTRY  = P_rec_country;
            result:= N_DD_REF;     
            END IF;
      IF CNT3=0 THEN
dbms_output.put_line('488');
          SELECT COUNT(*) INTO CNT4 FROM TEMP_DEMDET_TARIF ;
       IF CNT4 >0 THEN   SELECT COUNT(*) INTO CNT5 FROM TEMP_DEMDET_TARIF
                 WHERE  DD_PARTNER_CODE =P_PARTNER_CODE;
          IF CNT5 >0 THEN  SELECT COUNT(*) INTO CNT6 FROM TEMP_DEMDET_TARIF
                    WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                     AND   DD_EQP_SIZE     = P_cnSize;
dbms_output.put_line('43');
          IF CNT6 >0 THEN   SELECT COUNT(*) INTO CNT6 FROM TEMP_DEMDET_TARIF
                    WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                     AND   DD_EQP_SIZE     = P_cnSize
                     AND   DD_EQP_TYPE     = P_cnType;   
       
              IF CNT7>0 THEN
dbms_output.put_line('52');
                           VAR_DND_TYPE := P_cnType;
                           SELECT COUNT(*) INTO CNT8 FROM TEMP_DEMDET_TARIF
                          WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                           AND   DD_EQP_SIZE     = P_cnSize
                           AND   DD_EQP_TYPE     = VAR_DND_TYPE
                           AND   DD_DEL_COUNTRY  = P_del_country;    
                IF CNT8 >0 THEN VAR_DND_DEL_COUNTRY := P_del_country; 
dbms_output.put_line('60');
                   ELSE 
dbms_output.put_line('62');
                   VAR_DND_DEL_COUNTRY := '+';        
                
                SELECT COUNT(*) INTO CNT9 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY;
                 IF CNT9 = 1 THEN     SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY;   
              result:= N_DD_REF;   
              else 
dbms_output.put_line('77');
              SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                               
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
           ELSE  
dbms_output.put_line('94');
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                             AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE 
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+'
                            AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                          END IF;
                        END IF;
                 END IF;  
            END IF;  
dbms_output.put_line('114');
     ELSE
dbms_output.put_line('116');
    VAR_DND_TYPE := '+';
    SELECT COUNT(*) INTO CNT10 FROM TEMP_DEMDET_TARIF
                          WHERE  DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY; 
      IF CNT10>0 THEN
dbms_output.put_line('123');
                              VAR_DND_DEL_COUNTRY := P_del_country;
               SELECT COUNT(*) INTO CNT11 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE =P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY;
       IF CNT11 = 1 THEN
        SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE =P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY;
                              result:= N_DD_REF;
            ELSE
            SELECT  COUNT(*) INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                               
                                    SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
                                 ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
                          
                      result:= N_DD_REF;
                END IF;
           END IF;
         END IF;  
         
               ELSE
dbms_output.put_line('174');
                              VAR_DND_DEL_COUNTRY := '+';
               SELECT COUNT(*) INTO CNT12 FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY ;
                 IF CNT12 = 1 THEN
                  SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY; 
                 result:= N_DD_REF;
                                 ELSE
                SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
                                 ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = P_cnSize
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                      END IF;
                     END IF;
                   END IF;
               END IF;
              END IF;
                
             ELSE
dbms_output.put_line('675');
                        VAR_DND_SIZE := '+';
              SELECT COUNT(*) INTO CNT13 FROM TEMP_DEMDET_TARIF 
                       WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                        AND   DD_EQP_SIZE     = VAR_DND_SIZE
                        AND   DD_EQP_TYPE     = P_cnType;
                 IF CNT13>=1 THEN
dbms_output.put_line('682');
                        SELECT COUNT(*) INTO CNT14 FROM TEMP_DEMDET_TARIF
                           WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                          AND DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     =  P_cnType
                           AND   DD_DEL_COUNTRY  = P_del_country;
           IF CNT14 >=1 THEN
                              SELECT  COUNT(*) INTO CNT15 FROM TEMP_DEMDET_TARIF
                           WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                          AND  DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     =  P_cnType
                           AND   DD_DEL_COUNTRY  = P_del_country;  
                      IF CNT15 = 1 THEN
dbms_output.put_line('695');
                              SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                          WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                          AND DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     =  P_cnType
                           AND   DD_DEL_COUNTRY  = P_del_country;  
                     result:= N_DD_REF;
                                 ELSE 
dbms_output.put_line('702');
                  SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
dbms_output.put_line('711');
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
                  
          ELSE
dbms_output.put_line('721');
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = P_del_country 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
dbms_output.put_line('729');
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                 END IF;
           END IF;
           END IF;
   ELSE    
dbms_output.put_line('285');
       VAR_DND_DEL_COUNTRY := '+';
       SELECT COUNT(*) INTO CNT16 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY ;
        IF CNT16 = 1 THEN     
          SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY; 
                  result:= N_DD_REF;
                                 ELSE       
                                 SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
          ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE   DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = P_cnType
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                 END IF;
                 END IF; 
                 END IF;
                 END IF;
                 
      ELSE
dbms_output.put_line('335');
       VAR_DND_TYPE := '+';
          SELECT COUNT(*) INTO CNT17 FROM TEMP_DEMDET_TARIF
                          WHERE  DD_PARTNER_CODE =P_PARTNER_CODE
                           AND   DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     = VAR_DND_TYPE
                           AND   DD_DEL_COUNTRY  = P_del_country;
          IF CNT17 >=1 THEN        
                           SELECT COUNT(*) INTO CNT18 FROM TEMP_DEMDET_TARIF
                          WHERE  DD_PARTNER_CODE =P_PARTNER_CODE
                           AND   DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     = VAR_DND_TYPE
                           AND   DD_DEL_COUNTRY  = P_del_country;
       IF CNT18 = 1 THEN
                                 SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE =P_PARTNER_CODE
                           AND   DD_EQP_SIZE     = VAR_DND_SIZE
                           AND   DD_EQP_TYPE     = VAR_DND_TYPE
                           AND   DD_DEL_COUNTRY  = P_del_country;
                  result:= N_DD_REF;
                    ELSE      
                                    SELECT  COUNT(*)  INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
          ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = P_del_country 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = P_del_country 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                 END IF; 
                 END IF;
                 END IF;
         ELSE   
               VAR_DND_DEL_COUNTRY := '+';  
               SELECT COUNT(*) INTO CNT19 FROM TEMP_DEMDET_TARIF
                             WHERE  DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY ;
        IF CNT19 = 1 THEN     
          SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY; 
                  result:= N_DD_REF;
                                 ELSE       
                                 SELECT  COUNT(*)INTO CNTR1 FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                 IF CNTR1=1 THEN
                           SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND   DD_REC_COUNTRY  = P_rec_country;
                  result:= N_DD_REF;
          ELSE  
              SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
                             WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                           AND DD_REC_COUNTRY  = '+';
             IF CNTR2 =1 THEN 
             SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE = P_PARTNER_CODE
                              AND   DD_EQP_SIZE     = VAR_DND_SIZE
                              AND   DD_EQP_TYPE     = VAR_DND_TYPE
                              AND   DD_DEL_COUNTRY  = VAR_DND_DEL_COUNTRY 
                              AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                  END IF;
              END IF;
            END IF;
        END IF;
      END IF;
   END IF;
         ELSE 
                      SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
                                WHERE DD_PARTNER_CODE =  '+'
                                 AND   DD_EQP_SIZE     =  '+'
                                 AND   DD_EQP_TYPE     =  '+'
                                 AND   DD_DEL_COUNTRY  = '+'
                                AND   DD_REC_COUNTRY  = '+';
               
     END IF;
            END IF;
         END IF;
      --END IF;

COMMIT; -- to DEM_REF_DIAG.
 RETURN result;
END Fun_Dem_Ref2;
/

SELECT * FROM user_errors;

Create OR REPLACE function Fun_Dem_Ref_lowprice (P_PARTNER_CODE in varchar2 , P_del_country in varchar2,P_rec_country in varchar2,
  P_cnSize    in varchar2 , P_cnType       in varchar2)
     RETURN VARCHAR2 IS
     result VARCHAR2(50);
    CNT2 NUMBER; CNT3  NUMBER;CNT4 NUMBER;CNT5 NUMBER;CNT6 NUMBER;CNT7 NUMBER;CNT8 NUMBER;
    CNT9  NUMBER;   CNT10  NUMBER;  CNT11  NUMBER; CNT12  NUMBER;CNT13 NUMBER; CNT14  NUMBER; CNT15  NUMBER;  
     CNT16  NUMBER; CNT17  NUMBER; CNT18  NUMBER; CNT19  NUMBER;N_DD_REF       NUMBER;VAR_DND_TYPE VARCHAR2(2);
VAR_DND_SIZE VARCHAR2(2);
VAR_DND_DEL_COUNTRY VARCHAR2(2);
VAR_DND_ACT_TYPE VARCHAR2(10); -- guessing from usage
CNTR1 NUMBER;
CNTR2 NUMBER;
endDate DATE := SYSDATE;
    begin 
   
    SELECT t.DD_REF INTO N_DD_REF
	FROM TEMP_DEMDET_TARIF t JOIN tab_mhd_tariff_dnd_param tp ON tp.DD_REF = t.DD_REF
	WHERE DD_PARTNER_CODE IN (P_PARTNER_CODE, '+')
	  AND (DD_EQP_SIZE IN (P_cnSize, '+') OR (DD_EQP_SIZE <> '+' AND P_cnSize <> '+' AND CAST(DD_EQP_SIZE AS NUMBER) > P_cnSize)) -- Do not forget to cast to number! Else 100 will be (alphabetically) lower than 80.
	  AND DD_EQP_TYPE     IN (P_cnType, '+')
	  AND DD_DEL_COUNTRY  IN (P_del_country, '+')
	  AND DD_REC_COUNTRY  IN (P_rec_country, '+')
    ORDER BY tp.DD_PRICE, DD_EQP_SIZE
    FETCH FIRST ROW ONLY;
	RETURN N_DD_REF;
END;
/

SELECT * FROM user_errors;

Create OR REPLACE function Fun_Dem_Ref_orderedfilters (P_PARTNER_CODE in varchar2 , P_del_country in varchar2,P_rec_country in varchar2,
  P_cnSize    in varchar2 , P_cnType       in varchar2)
     RETURN VARCHAR2 IS
     result VARCHAR2(50);
    CNT2 NUMBER; CNT3  NUMBER;CNT4 NUMBER;CNT5 NUMBER;CNT6 NUMBER;CNT7 NUMBER;CNT8 NUMBER;
    CNT9  NUMBER;   CNT10  NUMBER;  CNT11  NUMBER; CNT12  NUMBER;CNT13 NUMBER; CNT14  NUMBER; CNT15  NUMBER;  
     CNT16  NUMBER; CNT17  NUMBER; CNT18  NUMBER; CNT19  NUMBER;N_DD_REF       NUMBER;VAR_DND_TYPE VARCHAR2(2);
VAR_DND_SIZE VARCHAR2(2);
VAR_DND_DEL_COUNTRY VARCHAR2(2);
VAR_DND_ACT_TYPE VARCHAR2(10); -- guessing from usage
CNTR1 NUMBER;
CNTR2 NUMBER;
endDate DATE := SYSDATE;
    begin 
   
    SELECT t.DD_REF INTO N_DD_REF
	FROM TEMP_DEMDET_TARIF t JOIN tab_mhd_tariff_dnd_param tp ON tp.DD_REF = t.DD_REF
	WHERE DD_PARTNER_CODE IN (P_PARTNER_CODE, '+')
	  AND DD_EQP_SIZE     IN (P_cnSize, '+')
	  AND DD_EQP_TYPE     IN (P_cnType, '+')
	  AND DD_DEL_COUNTRY  IN (P_del_country, '+')
	  AND DD_REC_COUNTRY  IN (P_rec_country, '+')
	ORDER BY
		DECODE(DD_PARTNER_CODE, P_PARTNER_CODE, 0),
		DECODE(DD_EQP_SIZE,     P_cnSize, 0),
		DECODE(DD_EQP_TYPE,     P_cnType, 0),
		DECODE(DD_DEL_COUNTRY,  P_del_country, 0),
		DECODE(DD_REC_COUNTRY,  P_rec_country, 0)
	FETCH FIRST ROW ONLY;
	RETURN N_DD_REF;
END;
/

SELECT * FROM user_errors;

Create OR REPLACE function Fun_Dem_Ref_simplescore (P_PARTNER_CODE in varchar2 , P_del_country in varchar2,P_rec_country in varchar2,
  P_cnSize    in varchar2 , P_cnType       in varchar2)
     RETURN VARCHAR2 IS
     result VARCHAR2(50);
    CNT2 NUMBER; CNT3  NUMBER;CNT4 NUMBER;CNT5 NUMBER;CNT6 NUMBER;CNT7 NUMBER;CNT8 NUMBER;
    CNT9  NUMBER;   CNT10  NUMBER;  CNT11  NUMBER; CNT12  NUMBER;CNT13 NUMBER; CNT14  NUMBER; CNT15  NUMBER;  
     CNT16  NUMBER; CNT17  NUMBER; CNT18  NUMBER; CNT19  NUMBER;N_DD_REF       NUMBER;VAR_DND_TYPE VARCHAR2(2);
VAR_DND_SIZE VARCHAR2(2);
VAR_DND_DEL_COUNTRY VARCHAR2(2);
VAR_DND_ACT_TYPE VARCHAR2(10); -- guessing from usage
CNTR1 NUMBER;
CNTR2 NUMBER;
endDate DATE := SYSDATE;
    begin 
   
    SELECT t.DD_REF INTO N_DD_REF
	FROM TEMP_DEMDET_TARIF t
	WHERE DD_PARTNER_CODE IN (P_PARTNER_CODE, '+')
	  AND DD_EQP_SIZE     IN (P_cnSize, '+')
	  AND DD_EQP_TYPE     IN (P_cnType, '+')
	  AND DD_DEL_COUNTRY  IN (P_del_country, '+')
	  AND DD_REC_COUNTRY  IN (P_rec_country, '+')
	ORDER BY
		DECODE(DD_PARTNER_CODE, P_PARTNER_CODE, 1) +
		DECODE(DD_EQP_SIZE,     P_cnSize,       1) +
		DECODE(DD_EQP_TYPE,     P_cnType,       1) +
		DECODE(DD_DEL_COUNTRY,  P_del_country,  1) +
		DECODE(DD_REC_COUNTRY,  P_rec_country,  1)
	DESC
    FETCH FIRST ROW ONLY;
	RETURN N_DD_REF;
END;
/

SELECT * FROM user_errors;

----------------
-- Example data and tests

INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (224	,20,'RF','+','+','+');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (233	,40,'+','+','+','AE00000118');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (309	,40,'+','IN','AE','AE00000017');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (324	,40,'+','+','+','AE00002068');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (603, 20, 'GP', 'IN', 'OM', 'OM0000023');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (853, 20, 'GP', 'IN', 'BH', 'AE0002054');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (909, 20, 'GP', 'KW', 'IN', 'IN0002988');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (1340, 20, 'GP', 'OM', 'IN', 'IN0001774');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (1340, 20, 'GP', 'OM', 'IN', 'IN0000313');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (1340, 20, 'GP', 'OM', 'IN', '+');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (1339, '+', 'GP', 'OM', 'IN', 'IN0002409');
INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE)VALUES (1339, '+', '+', '+', '+', '+');

INSERT INTO TEMP_DEMDET_TARIF(DD_REF,DD_EQP_SIZE,DD_EQP_TYPE,DD_DEL_COUNTRY,DD_REC_COUNTRY,DD_PARTNER_CODE) VALUES
	(1000, 20, 'GP', 'IN', 'FR', 'REC1'),
	(1001, 20, 'GP', 'IN', 'DE', 'REC1'),
	(1100, 20, 'GP', 'IN', '+', 'REC1'),
	( 999, 20, 'GP', 'FR', 'IN', 'REC1'),
	( 900, 20, 'GP', 'FR', 'DE', 'REC1'),
;

-- Imagine a table of tarifs. A specific tarif is lower than the generic, last resort one ('+'). Bigger equipments are more costly.
INSERT INTO tab_mhd_tariff_dnd_param
	SELECT
		DD_REF AS TARIFF_DD_REF,
		MAX -- If many combinations are associated to this tarif, take the one with the most '+'.
		(
			DECODE(DD_EQP_SIZE, '+', 60, DD_EQP_SIZE)
			+ DECODE(DD_EQP_TYPE, '+', 15, 5)
			+ DECODE(DD_DEL_COUNTRY, '+', 15, 5)
			+ DECODE(DD_REC_COUNTRY, '+', 15, 5)
			+ DECODE(DD_PARTNER_CODE, '+', 15, 5)
		)
		AS DD_PRICE
	FROM TEMP_DEMDET_TARIF
	GROUP BY DD_REF;

SELECT * FROM TEMP_DEMDET_TARIF;
SELECT * FROM tab_mhd_tariff_dnd_param ORDER BY DD_PRICE;

-- Fun_Dem_Ref (P_PARTNER_CODE, P_del_country, P_rec_country, P_cnSize, P_cnType)

CREATE TABLE tests (P_PARTNER_CODE VARCHAR2(99), P_del_country VARCHAR2(99), P_rec_country VARCHAR2(99), P_cnSize varchar2(99), P_cnType VARCHAR2(99), expected INT);
INSERT INTO tests VALUES
	('AE00000118', '+', '+', 40, '+', NULL),
	('PK0000188', 'OM', 'IN', 20, 'GP', NULL),
	('IN0002409', 'OM', 'IN', 40, 'GP', NULL),
	('IN0002409', 'OM', 'IN', '+', 'GP', NULL),
	('REC1',      'IN', 'FR', '+', 'GP', NULL),
	('REC1',      'IN', 'FR', 20, 'GP', NULL),
	('REC1',      'IN', 'ES', 20, 'GP', NULL),
	('REC1',      'FR', 'DE', 20, 'GP', NULL),
	('REC1',      'FR', 'GB', 20, 'GP', NULL),
;

SELECT
	tests.*,
	Fun_Dem_Ref (P_PARTNER_CODE, P_del_country, P_rec_country, P_cnSize, P_cnType) old_result,
	Fun_Dem_Ref_orderedfilters (P_PARTNER_CODE, P_del_country, P_rec_country, P_cnSize, P_cnType) "2.prio",
	Fun_Dem_Ref_simplescore (P_PARTNER_CODE, P_del_country, P_rec_country, P_cnSize, P_cnType) "3.score",
    Fun_Dem_Ref_lowprice (P_PARTNER_CODE, P_del_country, P_rec_country, P_cnSize, P_cnType) "4.price"
FROM tests;

SELECT * FROM DEM_REF_DIAG ORDER BY S_PARAM;

-- To avoid SQL Fiddle duplicating the last query.
SELECT 1 FROM DUAL;

DD_REF	DD	DD	DD	DD	DD_PARTNER
224	20	RF	+	+	+
233	40	+	+	+	AE00000118
309	40	+	IN	AE	AE00000017
324	40	+	+	+	AE00002068
603	20	GP	IN	OM	OM0000023
853	20	GP	IN	BH	AE0002054
909	20	GP	KW	IN	IN0002988
1340	20	GP	OM	IN	IN0001774
1340	20	GP	OM	IN	IN0000313
1340	20	GP	OM	IN	+
1339	+	GP	OM	IN	IN0002409
1339	+	+	+	+	+
1000	20	GP	IN	FR	REC1
1001	20	GP	IN	DE	REC1
DD_REF	DD	DD	DD	DD	DD_PARTNER
1100	20	GP	IN	+	REC1
999	20	GP	FR	IN	REC1
900	20	GP	FR	DE	REC1

DD_REF	DD_PRICE
909	40
999	40
1001	40
1000	40
900	40
853	40
603	40
1100	50
1340	50
309	70
224	70
324	90
233	90
1339	120

P_PARTNER_CODE	P_DEL_COUNTRY	P_REC_COUNTRY	P_CNSIZE	P_CNTYPE	OLD_RESULT	2.prio	3.score	4.price
AE00000118	+	+	40	+	233	233	1339	233
PK0000188	OM	IN	20	GP		1340	1340	1340
IN0002409	OM	IN	40	GP	1339	1339	1339	1339
IN0002409	OM	IN	+	GP	1339	1339	1339	1339
REC1	IN	FR	+	GP		1339	1339	1339
REC1	IN	FR	20	GP	1000	1000	1339	1000
REC1	IN	ES	20	GP		1100	1339	1100
REC1	FR	DE	20	GP	900	900	1339	900
REC1	FR	GB	20	GP		1339	1339	1339

24
24
24
24
24
24
24
24
24

1
1

1
1