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 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
     --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 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 TEMP_DEMDET_TARIF;

----------------
-- 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, '+', '+', '+', '+', '+');

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

SELECT Fun_Dem_Ref('AE00000118', '+', '+', 40, '+') FROM DUAL;
SELECT Fun_Dem_Ref('PK0000188', 'OM', 'IN', 20, 'GP') FROM DUAL;
SELECT Fun_Dem_Ref('IN0002409', 'OM', 'IN', 40, 'GP') FROM DUAL;

FUN_DEM_REF('AE00000118','+','+',40,'+')
233

FUN_DEM_REF('PK0000188','OM','IN',20,'GP')

FUN_DEM_REF('IN0002409','OM','IN',40,'GP')
1339

FUN_DEM_REF('IN0002409','OM','IN',40,'GP')
1339