create or replace procedure W_GET_ALL_REQUIREMENTS_N (IN_LOADTIME_BEGIN IN DATE, IN_LOADTIME_END IN DATE,
IN_DOC_NUMBER IN VARCHAR2,
IN_ACCOUNT_NUMBER IN VARCHAR2, --Номер счёта:
IN_ORDER_BY IN VARCHAR2, ---Код сортируемого поля
IN_ORDER_TYPE IN VARCHAR2, -- Тип сортировки ('asc'-по возрастанию, 'desc'-по убыванию)
IN_PAGE_INDEX IN NUMBER, -- Номер стриницы
IN_PAGE_SIZE IN NUMBER, -- Количество записей на странице
OUT_REC_COUNT OUT NUMBER, -- Общее количество записей
OUT_PAGE_COUNT OUT NUMBER, --Общее количество страниц
OUT_CUR OUT LOADER_PACK.REF_CURSOR--,
) is
SQLText dbms_sql.varchar2a;
WhereClause dbms_sql.varchar2a;
i integer;
c number;
d number;
N_FIRST_REC_NUM NUMBER;
N_LAST_REC_NUM NUMBER;
LOAD_TIME DATE;

begin

WhereClause(1):=' ';

N_FIRST_REC_NUM := (IN_PAGE_INDEX - 1) * IN_PAGE_SIZE + 1;
N_LAST_REC_NUM := IN_PAGE_SIZE * IN_PAGE_INDEX;

if IN_LOADTIME_BEGIN is not NULL then WhereClause(WhereClause.Last+1):=' and TO_DATE(MT.LOAD_TIME, ''dd.mm.yyyy'') > TO_DATE(:xIN_LOADTIME_BEGIN, ''dd.mm.yyyy'') ';
else WhereClause(WhereClause.Last+1):=' and TO_DATE(MT.LOAD_TIME, ''dd.mm.yyyy'') > TO_DATE(''01.01.0001'', ''dd.mm.yyyy'') '; end if;
if IN_LOADTIME_END is not NULL then WhereClause(WhereClause.Last+1):=' and TO_DATE(MT.LOAD_TIME, ''dd.mm.yyyy'') <= TO_DATE(:xIN_LOADTIME_END, ''dd.mm.yyyy'') ';
else WhereClause(WhereClause.Last+1):=' and TO_DATE(MT.LOAD_TIME, ''dd.mm.yyyy'') <= TO_DATE(SYSDATE, ''dd.mm.yyyy'') '; end if;
if IN_DOC_NUMBER is not NULL then WhereClause(WhereClause.Last+1):=' and MT.DOC_NUMBER = :xIN_DOC_NUMBER '; end if;
if IN_ACCOUNT_NUMBER is not NULL then WhereClause(WhereClause.Last+1):=' and upper(MT.ACCOUNT_NUMBER) like upper(''%''||:xIN_ACCOUNT_NUMBER||''%'') '; end if;
SQLText(1):=' select count(*) ';
SQLText(SQLText.last + 1):=' FROM MT_BODY MT, MT_BODY_PTP MP
WHERE MT.MT_BODY = MP.MT_BODY(+) ';

for i in WhereClause.First..WhereClause.Last
loop
SQLText(SQLText.Last+1):=WhereClause(i);
end loop;
c := dbms_sql.open_cursor;
dbms_sql.parse(c, SQLText, SQLText.first, SQLText.last, false, dbms_sql.native);

if IN_LOADTIME_BEGIN is not NULL then dbms_sql.Bind_Variable(c,':xIN_LOADTIME_BEGIN',IN_LOADTIME_BEGIN); end if;
if IN_LOADTIME_END is not NULL then dbms_sql.Bind_Variable(c,':xIN_LOADTIME_END',IN_LOADTIME_END); end if;
if IN_DOC_NUMBER is not NULL then dbms_sql.Bind_Variable(c,':xIN_DOC_NUMBER',IN_DOC_NUMBER); end if;
if IN_ACCOUNT_NUMBER is not NULL then dbms_sql.Bind_Variable(c,':xIN_ACCOUNT_NUMBER',IN_ACCOUNT_NUMBER); end if;

dbms_sql.Define_Column(c,1,OUT_REC_COUNT);
d := dbms_sql.execute(c);
d := dbms_sql.Fetch_Rows(c);
dbms_sql.Column_Value(c,1,OUT_REC_COUNT);
SQLText(1):='SELECT b.* FROM
(SELECT ROWNUM AS num, a.* FROM (SELECT MT.MT_BODY, MT.LOAD_TIME, MT.CLOSE_TIME, mt.MT_REFERENCE,
(select got.G_SEQUENCE from g_oper_type got where got.OPER_CODE=mt.OPER_TYPE and rownum=1) SEQUENCE,
(select ljc.CREATE_DATE from log_who_insert_card_index ljc where ljc.mt_body=mt.mt_body and rownum=1) SANCTION_TIME ';


SQLText(SQLText.Last+1):= ')a )b
WHERE b.num > (:N_FIRST_REC_NUM - 1) AND b.num < (:N_LAST_REC_NUM + 1)';

c := dbms_sql.open_cursor;
dbms_sql.parse(c, SQLText, SQLText.first, SQLText.last, false, dbms_sql.native);
if IN_LOADTIME_BEGIN is not NULL then dbms_sql.Bind_Variable(c,':xIN_LOADTIME_BEGIN',IN_LOADTIME_BEGIN); end if;
if IN_LOADTIME_END is not NULL then dbms_sql.Bind_Variable(c,':xIN_LOADTIME_END',IN_LOADTIME_END); end if;
if IN_DOC_NUMBER is not NULL then dbms_sql.Bind_Variable(c,':xIN_DOC_NUMBER',IN_DOC_NUMBER); end if;
if IN_ACCOUNT_NUMBER is not NULL then dbms_sql.Bind_Variable(c,':xIN_ACCOUNT_NUMBER',IN_ACCOUNT_NUMBER); end if;

dbms_sql.Bind_Variable(C,':N_FIRST_REC_NUM',N_FIRST_REC_NUM);
dbms_sql.Bind_Variable(C,':N_LAST_REC_NUM',N_LAST_REC_NUM);

d := dbms_sql.execute(c);
OUT_CUR := dbms_sql.to_refcursor(c);
OUT_PAGE_COUNT := TRUNC(OUT_REC_COUNT / IN_PAGE_SIZE);
IF (OUT_PAGE_COUNT * IN_PAGE_SIZE) < OUT_REC_COUNT THEN
OUT_PAGE_COUNT := OUT_PAGE_COUNT + 1;
END IF;
end;