Reduce nested IF…ELSE conditions that iteratively condition building a query by adding filters one by one

Question

My stored procedure has this structure:

if Count 1>0 then
  count 2 with (condition 1,)
  if Count 2>0 then
    count 3 with (condition 1,2)
    if Count 3>0 then
      count 4 with(condition 1,2,3,)
      if Count 4>0 then
        count 5 with (condition 1,2,3,4,)
        if Count 5>0 then
          count 6 with (condition 1,2,3,4,5)
          if count 6>0 then
            count 7 where (condition 1,2 := +,3,4,5)
            …

like wise 60 filters are there.

This filter in where clause goes on until it satisfies count = 1 where clause filters are like:
cnt1 checking filter only on DD_EQP_SIZE if cnt is more than 1 then goes adding one more filter DD_EQP_TYPE,
if again cnt is more than 1 then adds DD_DEL_COUNTRY etc.
and at end checking againg only filter DD_EQP_TYPE.
like wise many more filter are there.

In the end we want tarif ID based on the eqpt type and epqt size ,deliver country and rec country tarif will be calculated and sometimes we end up no ID after appling all filters . so after confirming CNT3=0 then adding and removing one by one filters.
For example, if there is no country-specific tarif for this equipment, we'll default to the international tarif, and so on for each of the optional filters that help getting a "better" tarif but, if not found, can be omitted.

i want to reduce multiple line of code and if conditions without effecting performance.

Here is the start of the function (I tried to reduce as much as I can; see it complete and working in a fiddle):

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);
    CNTn 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);
CNTR1 NUMBER;
CNTR2 NUMBER;
endDate DATE := SYSDATE;
    begin 
   
     --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       
     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;
          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
                           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; 
                   ELSE 
                   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 
              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  = '+'
                            AND DD_REC_COUNTRY  = '+';
                      result:= N_DD_REF;
                          END IF;
                        END IF;
                 END IF;  
            END IF;  
     ELSE
    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
                              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
                              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
                        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    
       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
       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 count(*) into CNTn 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;
if CNTn = 1 then 
                      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  = '+';
  result:= N_DD_REF;             
     END IF;
            END IF;
         END IF;
      END IF;


 RETURN result;
END Fun_Dem_Ref;

"goes on untill it satisfies conut = 1": isn't it "goes on while it satisfies count > 0"? The former seems to mean that we have too much results and try to refine the filter until that returns exactly 1 row. The latter would mean that we don't mind having more than 1 result, as long as we have some. — Guillaume Outters
– Guillaume Outters, Commented Jul 29 at 6:57
Please show (even if simplifying all layers of IFs to a dozen) an end to the function: what should it return in the end? Add two small call examples, with one row of data per table and two calls to the function: one that reaches that nested return, one that stops in the middle (let's say on the 5th IF): what do they return? Create a fiddle (db<>fiddle, SQL Fiddle, …) where you ensure your queries work (for example you'll add a name to the function…), which will get everybody a starting point to understand and solve the problem — Guillaume Outters
– Guillaume Outters, Commented Jul 29 at 7:06
we want tarif ID based on the eqpt type and epqt size ,deliver country and rec country tarif will be calculated and sometimes we end up no ID after appling all filters . so after confirming CNT3=0 then adding and removing one by one filters. — Annapurna
– Annapurna, Commented Jul 29 at 8:42
In the first step you are trying to look at a POOL_CODE's rows and get one of its POOL_DND_PARAM (which fails because of WHERE pool_code = pool_code). Why are you fine with an arbitrary POOL_DND_PARAM for the POOL_CODE? I would rather expect you loop through its different POOL_DND_PARAMs. Or are you dealing with a flawed (i.e. non-normalized) database model here where you expect to find the same POOL_DND_PARAM in all rows for the POOL_CODE? Or is POOL_CODE unique in TAB_MHD_POOL and ROWNUM = 1 completely superfluous? And why do you select into VAR_DND_PARAM at all? You are not using it. — Thorsten Kettner
– Thorsten Kettner, Commented Jul 30 at 8:26
answering your first question yes i forgot to add N_DD_REF now its added and second one is if last count is more than 1 then if conditions will end; — Annapurna
– Annapurna, Commented Jul 31 at 6:18

rgm565 · Accepted Answer · 2025-07-29 09:23:29Z

Without even going into the logic, the code is a mess. Start by implementing coding standards and when working on someone else's messy code, apply those standards.To start with, my suggestion is keywords in upper case and tables, columns, variables etc in lower case. Indent 4 spaces (do not use TAB). Procedure parameters should start with p_. Line up vertically, use new lines and plenty of spaces.

With the above in mind, this would never run. First off, the initial query has WHERE pool_code = pool_code. Then the query following IF cnt3 = 0 has no semicolon to end it. There is also the duplicate IF cnt3 = 1 THEN which is hopefully a copy-and-paste typo.

Reformatting the supplied code as per my preferred standards, we can easily see that this is often repeated and could be executed once at the start:

SELECT COUNT (*)
INTO   <localVar>
FROM   tab_thd_eqp_demdet_tarif
WHERE  dd_ref IN (
    SELECT tariff_dd_ref
    FROM tab_mhd_tariff_dnd_param
    WHERE tariff_dnd_id = n_dnd_id)

We can also see that, for example, var_dnd_size := cnSize; is unnecessary. Likewise, result is set without any reason (hopefully it comes into play later).

For reference, I have reformatted the supplied code as per my preferred standards:

CREATE OR REPLACE FUNCTION  (
    startMove      IN VARCHAR2,
    endMove        IN VARCHAR2,
    demType        IN VARCHAR2,
    pool_code      IN VARCHAR2,
    partner_code   IN VARCHAR2,
    del_country    IN VARCHAR2,
    cnSize         IN VARCHAR2,
    cnType         IN VARCHAR2,
    endDate        IN DATE,
    loadstat       IN VARCHAR2,
    startdate      IN DATE,
    dnd_st_agcode  IN VARCHAR2,
    pBkgRef        IN VARCHAR2,
    rec_country    IN VARCHAR2)
RETURN VARCHAR2 IS
    result VARCHAR2(50);

    cnt1 NUMBER;
    cnt2 NUMBER;
    cnt3 NUMBER;
    cnt4 NUMBER;
    cnt5 NUMBER;
    cnt6 NUMBER; 

    IF demType LIKE 'E%' THEN
        SELECT pool_dnd_param
        INTO   var_dnd_param
        FROM   tab_mhd_pool
        WHERE  pool_code = pool_code
        AND    ROWNUM = 1;

        SELECT COUNT (*)
        INTO   cnt1
        FROM   tab_mdl_pool_dnd_parm
        WHERE  dnd_pool_code   = pool_code
        AND    dnd_dem_type    = demType
        AND    dnd_start_point = startMove
        AND    dnd_end_point   = endMove;

        IF cnt1 > 0 THEN
            SELECT dnd_id
            INTO   n_dnd_id
            FROM   tab_mdl_pool_dnd_parm
            WHERE  dnd_pool_code   = pool_code
            AND    dnd_dem_type    = demType
            AND    dnd_start_point = startMove
            AND    dnd_end_point   = endMove;

            SELECT COUNT (*)
            INTO   cnt2
            FROM   tab_mhd_tariff_dnd_param 
            WHERE  tariff_dnd_id = n_dnd_id;

            IF CNT2 > 0 THEN
                SELECT COUNT (*)
                INTO   cnt3
                FROM   tab_thd_eqp_demdet_tarif 
                WHERE  dd_ref IN (
                    SELECT tariff_dd_ref
                    FROM   tab_mhd_tariff_dnd_param
                    WHERE  tariff_dnd_id = n_dnd_id)
                AND    dd_eqp_size     = cnSize
                AND    dd_eqp_type     = cnType 
                AND    dd_del_country  = del_country
                AND    dd_rec_country  = REC_country;

                IF CNT3 = 1  THEN 
                    IF CNT3 = 1  THEN
                        SELECT dd_ref
                        INTO   n_dd_ref
                        FROM   tab_thd_eqp_demdet_tarif 
                        WHERE  dd_ref IN (
                            SELECT tariff_dd_ref
                            FROM   tab_mhd_tariff_dnd_param
                            WHERE  tariff_dnd_id = n_dnd_id)
                        AND    dd_eqp_size     = cnSize
                        AND    dd_eqp_type     = cnType 
                        AND    dd_del_country  = del_country
                        AND    dd_rec_country  = REC_country
                        AND    dd_load_status  = NVL (loadstat, 'F');

                        result := n_dd_ref;     
                    END IF;

                    IF cnt3 = 0 THEN
                        SELECT COUNT (*)
                        INTO cnt4
                        FROM tab_thd_eqp_demdet_tarif
                        WHERE dd_ref IN (
                            SELECT tariff_dd_ref
                            FROM   tab_mhd_tariff_dnd_param
                            WHERE  tariff_dnd_id = n_dnd_id)
             
                        IF cnt4 > 0 THEN
                            SELECT COUNT (*)
                            INTO   cnt5
                            FROM   tab_thd_eqp_demdet_tarif
                            WHERE  dd_ref IN (
                                SELECT tariff_dd_ref
                                FROM tab_mhd_tariff_dnd_param
                                WHERE tariff_dnd_id = n_dnd_id)
                            AND    dd_partner_code = partner_code;

                            IF cnt5 > 0 THEN
                                SELECT COUNT (*)
                                INTO   cnt6
                                FROM   tab_thd_eqp_demdet_tarif
                                WHERE  dd_ref IN (
                                    SELECT tariff_dd_ref
                                    FROM   tab_mhd_tariff_dnd_param
                                    WHERE  tariff_dnd_id = n_dnd_id)
                                AND    dd_partner_code = partner_code 
                                AND    dd_eqp_size     = cnSize ;

                                IF cnt6 > 0 THEN
                                    var_dnd_size := cnSize;
                                    SELECT COUNT (*)
                                    INTO   cnt7
                                    FROM   tab_thd_eqp_demdet_tarif 
                                    WHERE  dd_ref IN (
                                        SELECT tariff_dd_ref
                                        FROM tab_mhd_tariff_dnd_param
                                        WHERE tariff_dnd_id = n_dnd_id)
                                    AND    dd_eqp_size     = var_dnd_size
                                    AND    dd_eqp_type     = cnType;

                                    IF cnt7 > 0 THEN
                                        var_dnd_type := cnType;
                                        SELECT COUNT (*)
                                        INTO   cnt8
                                        FROM   tab_thd_eqp_demdet_tarif
                                        WHERE  dd_ref IN (
                                            SELECT tariff_dd_ref
                                            FROM   tab_mhd_tariff_dnd_param
                                            WHERE  tariff_dnd_id = n_dnd_id)
                                        AND    dd_eqp_size     = var_dnd_size
                                        AND    dd_eqp_type     = var_dnd_type
                                        AND    dd_del_country  = del_countrY
    
                                        IF cnt8 > 0 THEN
                                            var_dnd_del_country := del_country;
                                        ELSE
                                            var_dnd_del_country := '+';
                                            SELECT COUNT (*)
                                            INTO   cnt9
                                            FROM   tab_thd_eqp_demdet_tarif
                                            WHERE  dd_ref IN (
                                                SELECT tariff_dd_ref
                                                FROM tab_mhd_tariff_dnd_param
                                                WHERE tariff_dnd_id = n_dnd_id)
                                            AND    dd_eqp_size     = var_dnd_size
                                            AND    dd_eqp_type     = var_dnd_type
                                            AND    dd_del_country  = var_dnd_del_country;

You only applied your preferred standards partly. The function parameters still don't differ from column names (so you still have WHERE pool_code = pool_code). A leading p_ is very common. An alternative I am familiar with is vi_, vo_, and vio_ for input, output and in/out parameters. Local variables should also be prefixed. Very common is a leading v_, e.g. v_cnt1.
@ThorstenKettner quite right, however my intention was not to correct the code but to show how standards help debug code issues.

Thorsten Kettner · Accepted Answer · 2025-09-03 07:00:01Z

You have a table temp_demdet_tarif in which there are dd_refs for combinations of dd_partner_code, dd_eqp_size, dd_eqp_type, dd_del_country, dd_rec_country. There are rows for exact matches and rows with wild card '+'.

You want to get the row with the best match.

Example: We are looking for the row matching dd_partner_code = 'PK1234567', dd_eqp_size = 20, dd_eqp_type = 'GP', dd_del_country = 'QM', dd_rec_country = 'IN' best, and there are these rows in the table:

dd_ref	dd_partner_code	dd_eqp_size	dd_eqp_type	dd_del_country	dd_rec_country
1	PK9999999	20	GP	QM	IN
2	PK1234567	10	GP	QM	IN
3	PK1234567	20	GP	QM	+
4	PK1234567	20	GP	+	+
5	PK1234567	20	+	+	+
6	PK1234567	+	+	+	+

then

dd_ref 1 gets dismissed because of the wrong partner code.
dd_ref 2 gets dismissed because of the wrong size.
All others are possible matches.
In these other rows there is no exact match, but the best match is dd_ref 3, because it matches the partner_code and the first three other parameters exactly, and only the last parameter is covered by a wild card.

In SQL we can get the best matching row with an appropriate ORDER BY clause that brings the best match to the top, and a FETCH FIRST ROW ONLY to get that row only.

The function would reduce to something like this:

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
    v_result VARCHAR2(50);
BEGIN
    SELECT dd_ref
    INTO v_result
    FROM temp_demdet_tarif
    WHERE dd_partner_code =  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
      CASE WHEN dd_eqp_size    = p_cnsize      THEN 1 ELSE 2 END,
      CASE WHEN dd_eqp_type    = p_cntype      THEN 1 ELSE 2 END,
      CASE WHEN dd_del_country = p_del_country THEN 1 ELSE 2 END,
      CASE WHEN dd_rec_country = p_rec_country THEN 1 ELSE 2 END
    FETCH FIRST ROW ONLY;

    RETURN v_result;
EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END fun_dem_ref;

Demo: https://sqlfiddle.com/oracle-plsql/online-compiler?id=60a2a7f0-b80f-417f-98f6-28f1493dca6c

You may have to adjust this to meet your exact requirements in perfection.

This solution is equivalent to part 2. Order by more important filters of my answer: reading both together can be interesting, as their different ways of explanation make them mutually enlightening.
@Guillaume Outters: Your answer is very detailed and profound, and I like it very much. Too bad, I gave it an upvote early, so I cannot upvote your considerable updates on it now :D

Guillaume Outters · Accepted Answer · 2025-07-31 13:39:56Z

Blindly giving clues, as we're waiting for your description of what happens at the end of that IFs chain (or in the ELSE):

The first two queries (SELECT COUNT(*) INTO CNT1 FROM tab_mdl_pool_dnd_parm … and IF CNT1 > 0 THEN SELECT DND_ID INTO N_DND_ID FROM tab_mdl_pool_dnd_parm …) can be reduced to SELECT DND_ID INTO N_DND_ID FROM tab_mdl_pool_dnd_parm … and replacing the test by IF N_DND_ID IS NOT NULL.
General policy: instead of "Do I have a result with filters 1,2? Then test with filters 1,2,3.", do a SELECT /* non optional filters */ ORDER BY CASE WHEN filter1 THEN 0 END, CASE WHEN filter2 THEN 0 END, CASE WHEN filter3 THEN 0 END: your returned top row (SELECT * FROM (…) WHERE ROWNUM = 1 or FETCH FIRST ROWS depending on your RDBMS version) will be **the one with the most filters passing.
From a performance point of view: as you'll loose index filtering, it's better to apply it only after some mandatory filters do the heavy filtering: it seems that after your IF CNT2 > 0 THEN would be the right location (because it only adds filters to an already filters set on 8 criteria: DD_REF which was determined against 4 of them, plus cnSize, cnType, del_country, REC_country).
If your filters are not strictly conditioning the following ones (for example, if filter32 can be applied if filter30 or filter31 instead of requiring all filters until filter31 to have passed), you can transform this to a more elaborate scoring column where each passed filter adds to the score.
Taking powers of 2 as score increments (= having a bitfield score) can help make separate filters (if filter1 gives a bit that no other combination of filters can reach, it will stay the most prioritized filter and its matched tarif will get chosen over all other not passing it), reserving bit ranges for equivalent filters and starting with the same bit will implement "equivalent" filters (e.g. if filter32 has bit 4 (value = 16), reserve bit 5 and 6 for filter30 and filter31, give them both a value of 32 (bit 5): filter30 or filter31 are equally sorted, but having both filter30 and filter31 matching gives 32+32 = 64 which is classed before matches of only one of them).
Mandatory filters can be checked on the score too (by ensuring their corresponding bit is set).
Of course the performance concerns still applies (the score will have to be computed on all tarif rows), so if you really have many products and delivery modes combinations, keeping the mandatory filters as standalone WHERE clauses will help reduce the still-to-be-scored resultset.
The better case would be that your tariffs are already ordered, with some kind of score associated to each DD_REF in TAB_MHD_TARIFF_DND_PARAM. For example, if each matching DD_REF is associated with a price, ORDER BY DD_PRICE and keep the smallest one: customers will be happy!

For the performance: although the hints hereabove warn you on filtering as soon as possible,
on the other hand keep in mind that running only one albeit complex SELECT, instead of dozens of simpler SELECTs will alleviate your database workload, so maybe all in all you'll get better performance even if circumventing indices.

General structure: filter & keep 1

The first step common to all these methods is of course filtering: you can discard too specific but non matching tariffs, that is, if sending to India, you can keep tariffs for India (DD_REC_COUNTRY = 'IN'), the ones for generic international delivery (DD_REC_COUNTRY = '+'), but you can certainly remove tariffs to other countries: you wouldn't want to choose a tariff to France even if (by chance, or by typo?) it's less costly than to India, because the delivery would be rejected.
This condition can be expressed as WHERE DD_REC_COUNTRY IN (P_rec_country, '+').

Then you'll still have multiple candidate tariffs, but you want to keep only one. You'll have to order the remaining tariffs, and then return only the first "best match".
This is ORDER BY ??? FETCH FIRST ROW ONLY, with the ??? to be determined (we'll study this in the next paragraphs).

Thus your function would look like:

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 
   
    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 ???
    FETCH FIRST ROW ONLY;
    RETURN N_DD_REF;
END;

2. Order by more important filters first

If you can say that "partner is the most important, then equipment size, and so on", that is, if you will choose a tariff with a matching partner (but all other criteria at '+'), over tariffs that are on DD_PARTNER_CODE = '+' (even though they have a perfect match on size, type, delivering and receiving countries),
then you will order by each of these successively:

    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)

(note: this method 2. solution is well explained in Thorsten Kettner's all-in-one answer)

N.B.: I initially used a DECODE(DD_PARTNER_CODE, P_PARTNER_CODE, 2, '+', 1, 0) DESC, but the simpler form with DECODE(DD_PARTNER_CODE, P_PARTNER_CODE, 0) ASC works because 0 (for a match) gets ordered before NULL (default value, for a non-match); as we eliminated non-match non-'+' with the WHERE, the only remaining non-matches are the +, which fall into the implicit ELSE NULL and thus get ordered after the match.

3. Scoring

If all filters are equally important, then you have to ORDER against them not successively, but combined. For example: count the number of specific filters matching, and take the tariff with the most "points" (1 point for a specific match, 0 point for a generic '+'):

    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

Note that:

you can choose to give different weights to each filter, for example if partner is more important that size then give it 2 points
if you choose powers of 2, you can even emulate 2.: if partner gets 16 points, size 8 points and so on, a match on only partner will get 16 points, while a match on all other criteria will get only 8+4+2+1 = 15, so the "only partner matching" tariff will win.

4. Best price offer

What would be really great is that you already have some kind of score in your tables, which would simply be the price: thus, when having multiple possible delivery modes, you would simply choose the lowest cost one.

To prototype it, I took the liberty to add a TAB_MHD_TARIFF_DND_PARAM.DD_PRICE column, filled with some simple rules (for size: 1 ₹ per unit (so 20 ₹ or 40 ₹ for your example data; and I chose a fixed price of 60 ₹ for the generic size of '+'); for other filters: 5 ₹ for a specific match, 15 ₹ for a '+').

Then the SELECT becomes simply:

    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 tp.DD_PRICE
    FETCH FIRST ROW ONLY;

Size refinement

You could also say that an equipment of size 20 would fit a package of size 40: so we could transform the WHERE to also accept bigger packages, if by chance this allows getting a better price (for example, if the partner only has a tariff for packages of 40, but at a better price than packages of 20 via generic partner '+', then choose the better price in the oversize package).
Of course, if we end up with both packages at the same price, choose the smallest one, as a second clause to the ORDER BY:

    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;

Summary & advice

I've made these 3 variants run on your data + some tests of mine in a complete fiddle:

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

I personally would choose solution 4. which is the more elegant (and client oriented)… but relies on you having the tariffs prices, of course.

Final advice: keep your current Fun_Dem_Ref running along the new function(s) you'll choose,
take time to compare their results (for example compare on your last year of deliveries: compute the percentage of exactly matching results where the new function can serve as a replace-in for the current, and understand and explain the differences to either (if the new function is erroneous) refine the scoring or (if the new function returns a better result) enjoy;
then once you're fully satisfied switch to the new function (you could simply rename Fun_Dem_Ref to Fun_Dem_Ref_old and the new function to Fun_Dem_Ref).

Can you please expain more about how scoring column will work
@Annapurna it really would be easier to explain if you provided a working fiddle (complete with data and expected result) I could then try my assumptions onto. As db<>fiddle seems down presently, go to SQL Fiddle for Oracle PLSQL, paste a simplified but working (with ELSE and RETURN) version of your function, followed by two calls with different criteria sets, then Run: it will fail for a non-existing table, add the different CREATE TABLEs and INSERT and Run until you get a result. Then copy the URL and paste it in the question.
check this [link]sqlfiddle.com/oracle-plsql/… hope from above link you able see code.
@Annapurna thank you! Now if we could have some calls to it, to verify that even after modifying the function we still get the same results, that would be perfect. I tried in this fiddle (where I reordered the INSERTs to put them near the calls): could you add one or two calls (that return something), then paste the new URL directly in the question (where it will be more visible, so that anyone can start working on your query)?
thank you and can suggest or any tuning possibility and reduse code it make it more easy code readable & understand.

Collectives™ on Stack Overflow

Reduce nested IF…ELSE conditions that iteratively condition building a query by adding filters one by one

3 Answers 3

2 Comments

2 Comments

General structure: filter & keep 1

2. Order by more important filters first

3. Scoring

4. Best price offer

Size refinement

Summary & advice

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

General structure: filter & keep 1

2. Order by more important filters first

3. Scoring

4. Best price offer

Size refinement

Summary & advice

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related