CREATE OR REPLACE PROCEDURE PRCAPTIONSEARCH
(
	CAPTIONFROM	IN	NUMBER DEFAULT NULL ,
	CAPTIONTO	IN	NUMBER DEFAULT NULL ,
	CAPTION		IN	NVARCHAR2 DEFAULT NULL ,
	LANGUAGECODE	IN	NUMBER ,
	BASELANGUAGECODE IN	NUMBER ,
	FUNCTIONCODE	IN	NUMBER DEFAULT NULL ,
	SORTBY		IN	VARCHAR2 DEFAULT NULL ,
	SORTDIR		IN	NUMBER DEFAULT 0 ,-- 0 - ASCENDING; 1 - DESCENDING;
	BATCHNUMBER	IN	NUMBER  ,
	BATCHSIZE	IN	NUMBER ,
	RESULTCOUNT	OUT	NUMBER,
	TOTALPAGECOUNT	OUT	NUMBER,
	cv_1		OUT	SYS_REFCURSOR
)
AS

	vROWFROM NUMBER(19);
	VCAPTION   NVARCHAR2(500)   ;
	vCAPTIONLIST VARCHAR2(4000)  ;

BEGIN
	
	VCAPTION := NVL(CAPTION,'') ||'%';

   	IF FUNCTIONCODE IS NOT NULL THEN
		BEGIN
			SELECT FLDCAPTIONLIST
			INTO VCAPTIONLIST
			FROM TBLFUNCTION
			WHERE FLDFUNCTIONCODE = FUNCTIONCODE
			AND ROWNUM = 1;
		EXCEPTION
			WHEN OTHERS
			THEN
			VCAPTIONLIST := NULL;
		END;

   END IF;
		
   INSERT INTO GTT_CAPTION
     (	
	FLDCAPTIONCODE,
	FLDROWNUMBER
     )
      SELECT A.FLDCAPTIONCODE,
	     ROWNUM
	FROM TBLCAPTION A
		JOIN TBLCAPTION B
               ON B.FLDCAPTIONCODE = A.FLDCAPTIONCODE
              AND B.FLDLANGUAGECODE = BASELANGUAGECODE
          WHERE  A.FLDLANGUAGECODE = LANGUAGECODE
                  AND (
			CAPTIONFROM IS NULL
			OR A.FLDCAPTIONCODE >= CAPTIONFROM
		      )
                  AND (
			CAPTIONTO IS NULL
			OR A.FLDCAPTIONCODE <= CAPTIONTO
		      )
                  AND (
			CAPTION = '%'
			OR UPPER(A.FLDCAPTION) LIKE UPPER(VCAPTION)
		      )
                  AND (
			FUNCTIONCODE IS NULL
			OR A.FLDCAPTIONCODE IN ( SELECT *
                                         FROM TABLE(FNCSV2TABLEBIGINT(vCAPTIONLIST)
						    )
						)
		      )
		ORDER BY CASE
                       WHEN ( SORTDIR = 0
                         AND SORTBY = 'CAPTION') THEN A.FLDCAPTION
                  ELSE NULL
                     END,
                  CASE
                       WHEN ( SORTDIR = 0
                         AND SORTBY = 'BASECAPTION' ) THEN B.FLDCAPTION
                  ELSE NULL
                     END,
                  CASE
                       WHEN ( SORTDIR = 1
                         AND SORTBY = 'CAPTION' ) THEN A.FLDCAPTION
                  ELSE NULL
                     END DESC,
                  CASE
                       WHEN ( SORTDIR = 1
                         AND SORTBY = 'BASECAPTION' ) THEN B.FLDCAPTION
                  ELSE NULL
                     END DESC;


   RESULTCOUNT := SQL%ROWCOUNT;
   TOTALPAGECOUNT := FNTOTALPAGECOUNT(RESULTCOUNT, BATCHSIZE);
   vROWFROM := FNROWFROM(BATCHNUMBER, BATCHSIZE);

   OPEN cv_1 FOR
      SELECT *
        FROM ( SELECT R.FLDCAPTIONCODE,
                      C.FLDCAPTION,
                      B.FLDCAPTION FLDBASECAPTION
        FROM GTT_CAPTION R
               JOIN TBLCAPTION C
                ON R.FLDCAPTIONCODE = C.FLDCAPTIONCODE
               AND C.FLDLANGUAGECODE = LANGUAGECODE
               JOIN TBLCAPTION B
                ON B.FLDCAPTIONCODE = C.FLDCAPTIONCODE
               AND B.FLDLANGUAGECODE = BASELANGUAGECODE
	       AND R.FLDROWNUMBER > VROWFROM
	       ORDER BY ROWNUM
                 )
         WHERE ROWNUM < BATCHSIZE   ;

EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(SQLCODE||'  '||SQLERRM) ;
END  ;
/