Oracle Apps R12 Function to get query rows in single variable

CREATE OR REPLACE function APPS.F_CONTACT_MSG(P_DAYS IN NUMBER)

return varchar2  is

CURSOR C1 IS 

SELECT 'TXN_NUMBER' , 'CUSTOMER_DETAILS' FROM DUAL

UNION ALL

SELECT TRX_NUMBER, PARTY_NAME||' '||END_DATE FROM F_AR_TRX_CUST_DETAILS_V

WHERE  END_DT = TRUNC(SYSDATE) + P_DAYS

;       

    

  M_LOCN_CODE        VARCHAR2(20000);

  M_STK_QTY_BU         VARCHAR2(20000);

  --M_REF_DEL_DT      OW_FS.DT%TYPE;

  M_TEMP1           NUMBER  := 0; 

  M_REF_CODE_NO     VARCHAR2(20000);

  

begin

    

    IF C1%ISOPEN THEN

        CLOSE C1;

    END IF;

    

    OPEN C1;

      LOOP

          FETCH C1 INTO M_LOCN_CODE, M_STK_QTY_BU;--, M_REF_DEL_DT;

          EXIT WHEN C1%NOTFOUND;

          M_TEMP1 := M_TEMP1 + 1;

       IF M_TEMP1 = 1 THEN

          M_REF_CODE_NO := M_LOCN_CODE||'     '||M_STK_QTY_BU;       

       ELSE

             M_REF_CODE_NO := M_REF_CODE_NO  || CHR(13)|| CHR(10)|| M_LOCN_CODE||'     '||M_STK_QTY_BU;

       END IF;

      END LOOP;

 

RETURN NVL(M_REF_CODE_NO,'');      

end;

/

USAGE :

In any block, define variable like below and use it.

L_RETURN VARCHAR2(2000):= F_CONTACT_MSG(P_DAYS);