function layth_thuebao(psso_tb varchar2, pskieuso_id varchar2, psschema varchar2, pspageNum varchar2, psPageRec varchar2, psuserip varchar2)
    return sys_refcursor
    is
        ref_ sys_refcursor;
        s varchar2(1000);
        c_ number;

        pages number;
        tb_ varchar2(100):=psso_tb;
    begin
        dms_admin.admin_log_access('layth_thuebao','{so_tb|'||psso_tb||'}{kieuso_id|'||pskieuso_id||'}{schema|'||psschema||'}{page_num|'||pspageNum||'}{page_rec|'||psPageRec||'}',psuserip);

        if substr(psso_tb,1,1)='0' then
            tb_:='84'||substr(psso_tb,2);
        end if;
        s:='select count(1) from '||psschema||'khosos a, '||psschema||'kieusos b where a.status in (0,1) and a.kieuso_id=b.kieuso_id and a.kieuso_id is not null';

        if pskieuso_id is not null then
            s:=s||' and a.kieuso_id in ('||pskieuso_id||')';
        end if;
        if psso_tb is not null then
            s:=s||' and a.so_tb like '''||replace(tb_,'*','%')||'''';
        end if;

        --s:=s||' order by id desc';

        DBMS_OUTPUT.Put_Line( s );


        EXECUTE IMMEDIATE s into c_;

        pages:=(case when trunc(c_/psPageRec)<c_/psPageRec then trunc(c_/psPageRec)+2 else trunc(c_/psPageRec)+1 end);

        if c_ < pspagerec then
            open ref_ for
                'select :c_ page,''0'' page_num,''0'' page_rec from dual' using c_;
            return ref_;
        end if;

        if pages<=10 then
            open ref_ for
                'select :c_ page,''0'' page_num,''0'' page_rec,'''' bg_color  from dual union all
                select rownum,:1,:2,decode(rownum,:pagenum,''style=''''background-color:#ffffff'''''','''') bg_color from '||psschema||'khosos where rownum<='||pages
            using c_, pspageNum, psPageRec, pspageNum;
        else

            open ref_ for
                'select :c_ page,''0'' page_num,''0'' page_rec,'''' bg_color from dual union all
                select a, :pspageNum, :psPageRec, decode(a,:pagenum,''style=''''background-color:#ffffff'''''','''') bg_color from (
                    select rownum a from '||psschema||'khosos where rownum<='||pages||'
                ) where a>=:1 and rownum<=10'
            using c_, pspageNum, psPageRec, pspageNum, (case when pspageNum+10>pages then pages-10 else pspageNum-1 end);
        end if;

        return ref_;
    exception
        when others then
            DECLARE
                err_ varchar2(10000);
            begin
                err_:='Loi thuc hien: '||to_char(sqlcode);
                insert into numstore.log_sql_err (content,ngayth) values ('layth_thuebao.'||err_||'|'||psschema||'|'||psuserip,sysdate);
                commit;
                open ref_ for 'select :1 ten_kieuso from dual' using err_;
                return ref_;
            end;

    end;

function layds_thuebao(psso_tb varchar2, pskieuso_id varchar2, psschema varchar2, pspageNum varchar2, psPageRec varchar2, psuserip varchar2)
    return sys_refcursor
    is
        ref_ sys_refcursor;
        s varchar2(10000);

        dk_ varchar2(100);
        tb_ varchar2(100):=psso_tb;
    begin
        dms_admin.admin_log_access('layds_thuebao','{so_tb|'||psso_tb||'}{kieuso_id|'||pskieuso_id||'}{schema|'||psschema||'}{page_num|'||pspageNum||'}{page_rec|'||psPageRec||'}',psuserip);

        if substr(psso_tb,1,1)='0' then
            tb_:='84'||substr(psso_tb,2);
        end if;

        select REGEXP_REPLACE(psso_tb,'[0-9*]+') into dk_ from dual;
        if dk_ is not null then
            open ref_ for 'select :1 ten_kieuso from dual' using 'Chi nhap chu so va * trong o Tim kiem';
            return ref_;
        end if;
/*
        s:='select rownum r_id, ''0''||substr(a.so_tb,3) so_tb,replace(ten_kieuso,''<br>'','''') ten_kieuso,
                decode(a.status,0,''Trong kho'',1,''Đã ĐK'',2,''Đợi thu hồi'') status,
                to_char(b.cuocthang_toithieu,'''|| store.fomart_number ||''')||''VNĐ'' cuocthang_toithieu,
                to_char(b.tra_truoc/thoigian_sudung,'''|| store.fomart_number ||''')||''VNĐ/tháng'' datcoc,
                b.thoigian_sudung||'' Tháng'' thoigian_sudung
            from '||psschema||'khosos a, '||psschema||'kieusos b where a.status in (0,1) and a.kieuso_id=b.kieuso_id and a.kieuso_id is not null';
*/
        s:='select rownum r_id, ''0''||substr(a.so_tb,3) so_tb,replace(ten_kieuso,''<br>'','''') ten_kieuso,
                decode(a.status,0,''Trong kho'',1,''Đã ĐK'',2,''Đợi thu hồi'') status,
                to_char(b.cuocthang_toithieu,'''|| store.fomart_number ||''')||'' VNĐ'' cuocthang_toithieu,
                decode(thoigian_sudung,''0'',''0'',to_char(b.tra_truoc/thoigian_sudung,'''|| store.fomart_number ||'''))||'' VNĐ/tháng'' datcoc,
                b.thoigian_sudung||'' Tháng'' thoigian_sudung,
                decode(a.khoitao_tt,1,''Có th¿ hòa m¿ng tr¿ tru¿c ho¿c tr¿ sau'',
                            ''Ch¿ du¿c hòa m¿ng tr¿ sau'') hinhthuc_hm
            from '||psschema||'khosos a, '||psschema||'kieusos b where a.status in (0,1) and a.kieuso_id=b.kieuso_id and a.kieuso_id is not null';

        if pskieuso_id is not null then
            s:=s||' and a.kieuso_id in ('||pskieuso_id||')';
        end if;
        if psso_tb is not null then
            s:=s||' and a.so_tb like '''||replace(tb_,'*','%')||'''';
        end if;

        s:=s||' order by a.status';

        s:= dms_admin.util.xuly_phantrang(s, pspageNum, psPageRec);
        s:='select decode(mod(rownum,2),1,''#ffffff'',''#f9f6f3'') bg_color,a.* from ('||s||') a';

        --insert into dms_admin.testsql values (s);

        open ref_ for s;

        return ref_;
    exception
        when others then
            DECLARE
                err_ varchar2(1000);
            begin
                rollback;
                err_:='Loi thuc hien: '||to_char(sqlcode);
                insert into numstore.log_sql_err (content,ngayth) values ('layds_thuebao.'||err_||'|'||psschema||'|'||psuserip,sysdate);
                commit;
                open ref_ for 'select :1 ten_kieuso from dual' using err_;
                return ref_;
            end;

    end;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------
SQL_ID 9mz65qwxmwr7m
--------------------
SELECT COUNT(*) FROM "SUBADMIN"."LICH_SU_TB" "A1" WHERE "A1"."SO_TB"=:"SYS_B_0"
AND
"A1"."NGAY_THANG">ADD_MONTHS(SYSDATE@!,(-:"SYS_B_1")) AND ("A1"."MA_DV"=:"SYS_B_
2" OR "A1"."MA_DV"=:"SYS_B_3")

Plan hash value: 618329699

--------------------------------------------------------------------------------
--------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | C
ost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------------------
|   0 | SELECT STATEMENT                     |               |       |       |
 290 (100)|          |       |       |
|   1 |  SORT AGGREGATE                      |               |     1 |    26 |
          |          |       |       |
|   2 |   PARTITION RANGE ITERATOR           |               |     1 |    26 |
 290   (0)| 00:00:04 |   KEY |    98 |
|   3 |    PARTITION HASH SINGLE             |               |     1 |    26 |
 290   (0)| 00:00:04 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| LICH_SU_TB    |     1 |    26 |
 290   (0)| 00:00:04 |       |       |
|   5 |      INDEX RANGE SCAN                | LSTB_SOTB_IDX |   114 |       |
 197   (0)| 00:00:03 |       |       |
--------------------------------------------------------------------------------
--------------------------------------

SQL_ID 9mz65qwxmwr7m
--------------------
SELECT COUNT(*) FROM "SUBADMIN"."LICH_SU_TB" "A1" WHERE "A1"."SO_TB"=:"SYS_B_0"
AND
"A1"."NGAY_THANG">ADD_MONTHS(SYSDATE@!,(-:"SYS_B_1")) AND ("A1"."MA_DV"=:"SYS_B_
2" OR "A1"."MA_DV"=:"SYS_B_3")

Plan hash value: 1194074997

--------------------------------------------------------------------------------
-------------------------------------------
| Id  | Operation                            | Name               | Rows  | Byte
s | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------
-------------------------------------------
|   0 | SELECT STATEMENT                     |                    |       |
  |   212 (100)|          |       |       |
|   1 |  SORT AGGREGATE                      |                    |     1 |    2
6 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR           |                    |     1 |    2
6 |   212   (0)| 00:00:03 |   KEY |    98 |
|   3 |    PARTITION HASH SINGLE             |                    |     1 |    2
6 |   212   (0)| 00:00:03 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| LICH_SU_TB         |     1 |    2
6 |   212   (0)| 00:00:03 |       |       |
|   5 |      INDEX RANGE SCAN                | LSTB_NGAYTHANG_IDX |    29 |
  |   197   (0)| 00:00:03 |       |       |
--------------------------------------------------------------------------------
-------------------------------------------