Скрипты наполнения

OPER_JOURNAL_EVENT

create or replace

procedure fill_oper_journal(num in number)

is

crtTime date;

tableSize number;

r number;

maxOper number;

begin

r := num;

select max(oper_journal_id) into maxOper from oper_journal;

loop

select count(OPER_JOURNAL_ID) into tableSize from oper_journal;

insert into oper_journal(OPER_JOURNAL_ID, OPERATION_CODE,

CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID,

CLIENT_ID, USER_ID, TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID,

OJ_REG_TIME, OJ_DATE, OJ_STATUS_ENUM, OJ_NOTE, OJ_AMOUNT,

OJ_CONF_TIME, REQ_ID, OJ_EXT_ID,

OJ_SUSPECT_FLAG, OJ_REQUIRE_FM_FLAG, IDENTITY_CARD_ID)

select s_oper_journal.nextval, OPERATION_CODE, CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID, CLIENT_ID, USER_ID,

TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID,

trunc(sysdate - dbms_random.value(1,300)), '01.01.10', 1, dbms_random.string('x', 20), round(dbms_random.value(1,50000)), '01.01.10', null,null,

0, 0, IDENTITY_CARD_ID

from oper_journal s left join tmp t on s.OPER_JOURNAL_ID = t.tmp_id

where rownum <= r and tmp_id is null;

if tableSize <=0 then r :=-1;

else

r := r - tableSize;

end if;

exit when r <= 0;

commit;

end loop;

update oper_journal set main_oper_journal_id = oper_journal_id

where oper_journal_id > maxOper;

commit;

update oper_journal set OJ_CONF_TIME = OJ_REG_TIME

where oper_journal_id > maxOper;

commit;

update oper_journal set OJ_DATE = OJ_REG_TIME

where oper_journal_id > maxOper;

commit;

insert into oper_journal_event

select s_oper_journal_event.nextval, o.oper_journal_id, oje.user_id, trunc(sysdate - dbms_random.value(1,300)), oje_after_status_enum

oje_elapsed_time_interval, oje_note, oje_before_status_enum, oje_after_status_enum, oje.work_shift_id

from oper_journal o join oper_journal_event oje on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = oje.oper_journal_id

where o.oper_journal_id > maxOper;

insert into oper_journal_print

select s_oper_journal_print.nextval, o.oper_journal_id, PMT_DOC_ID, PRINT_TMPL_TYPE_ID, OJP_QTY,

OJP_EVENT_ENUM, OJP_AFTER_FLAG, OJP_PRINT_FLAG, SIGN_USER_ID

from oper_journal o join oper_journal_print ojp on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojp.oper_journal_id

where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal);

commit;

insert into oper_journal_content

select s_oper_journal_content.nextval, o.oper_journal_id, OJC_CONTENT_STORE_ENUM,

OJC_CONTENT_TYPE_ENUM,OJC_PRINTABLE_ENUM,OJC_CONTENT_XML,OJC_CONTENT_BLOB,

OJC_CONTENT_CLOB,OJC_NAME,OJC_FILE_NAME

from oper_journal o join oper_journal_content ojc on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojc.oper_journal_id

where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal);

commit;

end fill_oper_journal;

TRANS_POS

create or replace procedure fill_trans_pos(cnt in number)

is

tDate date;

rows_in_iter number;

n number;

intial_min_pd number;

startSeq number;

curr_trans_seq number;

min_pmt_doc_id number;

max_pmt_doc_id number;

begin

--на каждый аккаунт по 6 транзакций

rows_in_iter := 2;

n:= cnt;

if (n - rows_in_iter < 0) then rows_in_iter := n;

end if;

while (n > 0)

loop

tDate := trunc(sysdate - dbms_random.value(1,200));

select coalesce(max(trans_num)+1, 1) into startSeq

from trans where trans_date = tDate;

select coalesce(max(trans_num)+1,startSeq) into startSeq from trans_pos where trans_date = tDate;

reset_seq('trans_pos_seq', to_char(startSeq));

for i in 1..1000

loop

curr_trans_seq := trans_pos_seq.nextval;

insert into trans(TRANS_DATE, TRANS_NUM, REVERSE_TRANS_DATE, REVERSE_TRANS_NUM,

DEPT_ID,PMT_DOC_ID,TRANS_REVERSE_FLAG)

select trunc(tDate), curr_trans_seq, null,null,515, 35487,0

from dual

where rownum < 2;

insert into trans_pos(TRANS_DATE, TRANS_NUM, TRANS_POS_NUM, CURRENCY_ID,

CASH_SYMBOL_CODE, ACCOUNT_ID, TP_COR_NUM, TP_VALUE_DATE, TP_SIDE_ENUM, TP_AMOUNT,

TP_AMOUNT_RUB, TP_NEED_ACT_BAL_FLAG)

select trunc(tDate), curr_trans_seq, 1,126,37, 28155, 2, trunc(tDate), 'D',10000, 10000, null

from dual

where rownum < 2;

insert into trans_pos(TRANS_DATE, TRANS_NUM, TRANS_POS_NUM, CURRENCY_ID,

CASH_SYMBOL_CODE, ACCOUNT_ID, TP_COR_NUM, TP_VALUE_DATE, TP_SIDE_ENUM, TP_AMOUNT,

TP_AMOUNT_RUB, TP_NEED_ACT_BAL_FLAG)

select trunc(tDate), curr_trans_seq, 2,126,37,28155, 2, trunc(tDate), 'D',10000,10000,null

from dual

where rownum < 2;

end loop;

n := n - rows_in_iter - 1000;

end loop;

end fill_trans_pos;

VALUABLE_FLOW

create or replace procedure fill_val_flow(cnt in number)

is

tableSize number;

r number;

maxVFId number;

minVFId number;

rows_in_iter number;

n number;

m number;

minVF number;

begin

select min(valuable_flow_id) into minVFId from valuable_flow;

rows_in_iter := 100000;

n:= cnt;

minVF:=minVFId;

if (n - rows_in_iter < 0) then rows_in_iter := n;

end if;

while (n > 0)

loop

select max(valuable_flow_id) into maxVFId from valuable_flow;

m:= round(dbms_random.value(minVFId,maxVFId));

insert into VALUABLE_FLOW (VALUABLE_FLOW_ID, RECEIVER_WORK_SHIFT_ID,

SENDER_WORK_SHIFT_ID, OPER_JOURNAL_ID, VALUABLE_REGISTER_ID,

PMT_DOC_ID, VF_SERIES, VF_FIRST_NUM, VF_LAST_NUM,

VF_AMOUNT, VF_QUANTITY, SENDER_DEVICE_ID, SENDER_DEPT_ID,

RECEIVER_DEPT_ID, RECEIVER_DEVICE_ID, VF_SORT_ENUM, VF_RECEIPT_DOC_QTY,

VF_DISBURSEMT_DOC_QTY, MAIN_OPER_JOURNAL_ID, VN_SENDER_LOCATION_ENUM,

VN_RECEIVER_LOCATION_ENUM, VF_HIST_COST, SENDER_USER_ID, RECEIVER_USER_ID)

select S_VALUABLE_FLOW.nextval, RECEIVER_WORK_SHIFT_ID,

SENDER_WORK_SHIFT_ID, OPER_JOURNAL_ID, VALUABLE_REGISTER_ID,

PMT_DOC_ID, VF_SERIES, VF_FIRST_NUM, VF_LAST_NUM,

VF_AMOUNT, VF_QUANTITY, SENDER_DEVICE_ID, SENDER_DEPT_ID,

RECEIVER_DEPT_ID, RECEIVER_DEVICE_ID, VF_SORT_ENUM, VF_RECEIPT_DOC_QTY,

VF_DISBURSEMT_DOC_QTY, MAIN_OPER_JOURNAL_ID, VN_SENDER_LOCATION_ENUM,

VN_RECEIVER_LOCATION_ENUM, VF_HIST_COST, SENDER_USER_ID, RECEIVER_USER_ID

from valuable_flow v left join tmp t on v.valuable_flow_id = t.tmp_id

where (valuable_flow_id between minVFId and m) and rownum < rows_in_iter;

maxVFId := maxVFId + rows_in_iter;

if (m >= rows_in_iter) then

n := n - rows_in_iter;

else

n := n - m;

end if;

minVFId := minVFId + rows_in_iter;

if (n > 0 and (minVFId + rows_in_iter) > maxVFId) then

minVFId := minVF;

end if;

commit;

end loop;

end fill_val_flow;

AN_ATTR_VAL

create or replace procedure FILL_AN_ATTR_VAL(cnt in number)

is

rows_in_iter number;

n number;

min_acc_id number;

max_acc_id number;

init_min_acc_id number;

tDate date;

begin

rows_in_iter := 300000;

n:= cnt;

select min(account_id) into min_acc_id from account where account_id > 0;

select max(account_id) into max_acc_id from account;

init_min_acc_id := min_acc_id;

if (n - rows_in_iter < 0) then rows_in_iter := n;

end if;

while (n >0)

loop

tDate := trunc(sysdate - dbms_random.value(1,300));

SAVEPOINT start_transaction;

insert into an_attr_val (AN_ATTR_VAL_ID, AAV_OBJ_ID,

AN_ATTR_ID, AAV_TIME,

AAV_VALUE)

select s_an_attr_val.nextval, a.account_id,

ceil(dbms_random.value(11,19)), tDate,

'lt' || dbms_random.string('X',40)

from account a

where rownum < rows_in_iter and a.account_id between min_acc_id and max_acc_id;

n := n - rows_in_iter;

min_acc_id := min_acc_id + rows_in_iter;

if (min_acc_id >= max_acc_id) then

min_acc_id := init_min_acc_id;

end if;

commit;

end loop;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

rollback to start_transaction;

tDate := sysdate;

min_acc_id := min_acc_id + rows_in_iter;

dbms_output.put_line('dups');

WHEN OTHERS THEN

ROLLBACK;

END;

OPER_JOURNAL, OPER_JOURNAL_PRINT,

OPER_JOURNAL_CONTENT, OPER_JOURNAL_EXT

create or replace function func_fill_oper_journal(num in number) return number

is

crtTime date;

tableSize number;

r number;

maxOper number;

begin

r := num;

select max(oper_journal_id) into maxOper from oper_journal;

loop

select count(OPER_JOURNAL_ID) into tableSize from oper_journal;

insert into oper_journal(OPER_JOURNAL_ID, OPERATION_CODE,

CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID,

CLIENT_ID, USER_ID, TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID,

OJ_REG_TIME, OJ_DATE, OJ_STATUS_ENUM, OJ_NOTE, OJ_AMOUNT,

OJ_CONF_TIME, REQ_ID, OJ_EXT_ID,

OJ_SUSPECT_FLAG, OJ_REQUIRE_FM_FLAG, IDENTITY_CARD_ID)

select s_oper_journal.nextval, OPERATION_CODE, CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID, CLIENT_ID, USER_ID,

TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID,

trunc(sysdate - dbms_random.value(1,300)), '01.01.10', 1, dbms_random.string('x', 20), round(dbms_random.value(1,50000)), '01.01.10', null,

case when OJ_EXT_ID is null then null else OJ_EXT_ID || ', lt-' || dbms_random.string('x',5) end,

0, 0, IDENTITY_CARD_ID

from oper_journal s left join tmp t on s.OPER_JOURNAL_ID = t.tmp_id

where rownum <= r and tmp_id is null;

if tableSize <=0 then r :=-1;

else

r := r - tableSize;

end if;

exit when r <= 0;

commit;

end loop;

update oper_journal set main_oper_journal_id = oper_journal_id

where oper_journal_id > maxOper;

commit;

update oper_journal set OJ_CONF_TIME = OJ_REG_TIME

where oper_journal_id > maxOper;

commit;

update oper_journal set OJ_DATE = OJ_REG_TIME

where oper_journal_id > maxOper;

commit;

insert into oper_journal_event

select s_oper_journal_event.nextval, o.oper_journal_id, oje.user_id, trunc(sysdate - dbms_random.value(1,300)), oje_after_status_enum

oje_elapsed_time_interval, oje_note, oje_before_status_enum, oje_after_status_enum, oje.work_shift_id

from oper_journal o join oper_journal_event oje on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = oje.oper_journal_id

where o.oper_journal_id > maxOper;

insert into oper_journal_print

select s_oper_journal_print.nextval, o.oper_journal_id, PMT_DOC_ID, PRINT_TMPL_TYPE_ID, OJP_QTY,

OJP_EVENT_ENUM, OJP_AFTER_FLAG, OJP_PRINT_FLAG, SIGN_USER_ID

from oper_journal o join oper_journal_print ojp on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojp.oper_journal_id

where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal);

commit;

insert into oper_journal_content

select s_oper_journal_content.nextval, o.oper_journal_id, OJC_CONTENT_STORE_ENUM,

OJC_CONTENT_TYPE_ENUM,OJC_PRINTABLE_ENUM,OJC_CONTENT_XML,OJC_CONTENT_BLOB,

OJC_CONTENT_CLOB,OJC_NAME,OJC_FILE_NAME

from oper_journal o join oper_journal_content ojc on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojc.oper_journal_id

where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal);

commit;

insert into oper_journal_ext (oje_ext_id, oper_journal_id, id_sys_code)

select distinct OJ_EXT_ID, oj.oper_journal_id, case when mod(oj.oper_journal_id,2)=0 then '00000'

when mod(oj.oper_journal_id,3)=0 then 'SC_NAL'

when mod(oj.oper_journal_id,5)=0 then 'Бисквит'

else 'OW4' end

from oper_journal oj

where oj.oper_journal_id > maxOper and oj.OJ_EXT_ID is not null;

commit;

return maxOper;

end func_fill_oper_journal;

create or replace procedure fill_oper_journal(num in number)

is

crtTime date;

tableSize number;

r number;

maxOper number;

begin

r := num;

select max(oper_journal_id) into maxOper from oper_journal;

loop

select count(OPER_JOURNAL_ID) into tableSize from oper_journal;

insert into oper_journal(OPER_JOURNAL_ID, OPERATION_CODE,

CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID,

CLIENT_ID, USER_ID, TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID,

OJ_REG_TIME, OJ_DATE, OJ_STATUS_ENUM, OJ_NOTE, OJ_AMOUNT,

OJ_CONF_TIME, REQ_ID, OJ_EXT_ID,

OJ_SUSPECT_FLAG, OJ_REQUIRE_FM_FLAG, IDENTITY_CARD_ID)

select s_oper_journal.nextval, OPERATION_CODE, CURRENCY_ID, DEPT_ID, PARENT_OPER_JOURNAL_ID, CLIENT_ID, USER_ID,

TELLER_EMP_USER_ID, CONTROL_EMP_USER_ID, LAST_CHANGE_USER_ID,

trunc(sysdate - dbms_random.value(1,300)), '01.01.10', 1, dbms_random.string('x', 20), round(dbms_random.value(1,50000)), '01.01.10', null,

case when OJ_EXT_ID is null then null else OJ_EXT_ID || ', lt-' || dbms_random.string('x',5) end,

0, 0, IDENTITY_CARD_ID

from oper_journal s left join tmp t on s.OPER_JOURNAL_ID = t.tmp_id

where rownum <= r and tmp_id is null;

if tableSize <=0 then r :=-1;

else

r := r - tableSize;

end if;

exit when r <= 0;

commit;

end loop;

update oper_journal set main_oper_journal_id = oper_journal_id

where oper_journal_id > maxOper;

commit;

update oper_journal set OJ_CONF_TIME = OJ_REG_TIME

where oper_journal_id > maxOper;

commit;

update oper_journal set OJ_DATE = OJ_REG_TIME

where oper_journal_id > maxOper;

commit;

insert into oper_journal_event

select s_oper_journal_event.nextval, o.oper_journal_id, oje.user_id, trunc(sysdate - dbms_random.value(1,300)), oje_after_status_enum

oje_elapsed_time_interval, oje_note, oje_before_status_enum, oje_after_status_enum, oje.work_shift_id

from oper_journal o join oper_journal_event oje on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = oje.oper_journal_id

where o.oper_journal_id > maxOper;

insert into oper_journal_print

select s_oper_journal_print.nextval, o.oper_journal_id, PMT_DOC_ID, PRINT_TMPL_TYPE_ID, OJP_QTY,

OJP_EVENT_ENUM, OJP_AFTER_FLAG, OJP_PRINT_FLAG, SIGN_USER_ID

from oper_journal o join oper_journal_print ojp on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojp.oper_journal_id

where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal);

commit;

insert into oper_journal_content

select s_oper_journal_content.nextval, o.oper_journal_id, OJC_CONTENT_STORE_ENUM,

OJC_CONTENT_TYPE_ENUM,OJC_PRINTABLE_ENUM,OJC_CONTENT_XML,OJC_CONTENT_BLOB,

OJC_CONTENT_CLOB,OJC_NAME,OJC_FILE_NAME

from oper_journal o join oper_journal_content ojc on o.oper_journal_id - 2*((select max(oper_journal_id) from oper_journal) - maxOper) = ojc.oper_journal_id

where o.oper_journal_id > maxOper + (select (max(oper_journal_id)-maxOper)/2 from oper_journal);

commit;

insert into oper_journal_ext (oje_ext_id, oper_journal_id, id_sys_code)

select distinct OJ_EXT_ID, oj.oper_journal_id, case when mod(oj.oper_journal_id,2)=0 then '00000'

when mod(oj.oper_journal_id,3)=0 then 'SC_NAL'

when mod(oj.oper_journal_id,5)=0 then 'Бисквит'

else 'OW4' end

from oper_journal oj

where oj.oper_journal_id > maxOper and oj.OJ_EXT_ID is not null;

commit;

insert into OBJ_ID_SYS_ROLE

select 'OPER_JOURNAL', oje.oper_journal_id, 1, oje.id_sys_code

from oper_journal_ext oje

where oje.oper_journal_id > maxOper;

commit;

end fill_oper_journal;

PMT_DOC

procedure fill_pmt_doc(num in number)

is

tableSize number;

r number;

maxPmtId number;

begin

r := num;

select max(pmt_doc_id) into maxPmtId from pmt_doc;

loop

select count(pmt_doc_id) into tableSize from pmt_doc;

insert into pmt_doc(PMT_DOC_ID,DEBIT_ACCOUNT_ID,CREDIT_ACCOUNT_ID,

CASH_OPERATION_SORT_CODE,PAR_PMT_DOC_ID,IDENTITY_CARD_ID,

OPER_JOURNAL_ID,PMT_TYPE_CODE,PMT_SORT_CODE,PMT_ORDER_TYPE_CODE,

PAYER_BIC, BEN_BIC, CREDIT_CASH_SYMBOL_CODE, DEBIT_CASH_SYMBOL_CODE,

PRINT_TMPL_TYPE_ID, PAYER_STATUS_CODE, PMT_GROUND_TYPE_CODE,

CURRENCY_ID, MARK_DOC_CODE, DEBIT_CURRENCY_ID, CREDIT_CURRENCY_ID,

PD_ORD_COMP_ENUM, PD_SIGN_ENUM, PD_NUM, PD_CASH_OPERATION_NUMBER,

PD_PRINT_QTY, PD_REG_DATE, PD_DATE, PD_VALUE_DATE, PD_PAYER_ACC_NUM,

PD_PAYER_NAME, PD_PAYER_INN, PD_PAYER_KPP, PD_PAYER_BANK_ACC_NUM,

PD_BEN_ACC_NUM, PD_BEN_NAME, PD_BEN_INN, PD_BEN_KPP,

PD_BEN_BANK_ACC_NUM, PD_AMOUNT, PD_PMT_DETAILS,

PD_CBC, PD_OKATO, PD_FISKAL_PERIOD, PD_FISKAL_DOC_NUM,

PD_FISKAL_DOC_DATE_C, PD_DEBIT_AMOUNT, PD_CREDIT_AMOUNT,

CURRENCY_OPER_SORT_CODE, PD_PAYER_RESIDENT_FLAG, PD_BEN_RESIDENT_FLAG,

PD_EXT_ID, PD_NUM_S, PD_CASH_OPERATION_NUMBER_S,

PD_CCY_EXCH_RATE, PD_PAYER_IDENTITY_CARD, PD_PRINCIPAL,

PD_PRINCIPAL_IDENTITY_CARD, PD_POA, PD_PRINT_FLAG,

PD_PRINT_CLIENT_INFO_FLAG, PD_CASH_RECEIPT_NUM, PD_CASH_DISBURSEMT_NUM,

PD_BEN_IDENTITY_CARD, BENEFICIARY_ID, MAIN_OPER_JOURNAL_ID,

PD_POA_EXP_DATE, PD_TAX_CHARGE_UID, PD_CB_CODE, DEBIT_DEPT_ID,

CREDIT_DEPT_ID, DEBIT_ID_SYS_CODE, CREDIT_ID_SYS_CODE,

DEBIT_SYNT_ACC_ID, CREDIT_SYNT_ACC_ID, PD_PAYER_ADDRESS,

DEBIT_AGR_ID, CREDIT_AGR_ID, PD_BENEFICIARY_SURNAME,

PD_BENEFICIARY_FIRST_NAME, PD_BENEFICIARY_PATRONYMIC)

select s_pmt_doc.nextval, DEBIT_ACCOUNT_ID,CREDIT_ACCOUNT_ID,

CASH_OPERATION_SORT_CODE,PAR_PMT_DOC_ID,IDENTITY_CARD_ID,

OPER_JOURNAL_ID,PMT_TYPE_CODE,PMT_SORT_CODE,PMT_ORDER_TYPE_CODE,

PAYER_BIC, BEN_BIC, CREDIT_CASH_SYMBOL_CODE, DEBIT_CASH_SYMBOL_CODE,

PRINT_TMPL_TYPE_ID, PAYER_STATUS_CODE, PMT_GROUND_TYPE_CODE,

CURRENCY_ID, MARK_DOC_CODE, DEBIT_CURRENCY_ID, CREDIT_CURRENCY_ID,

PD_ORD_COMP_ENUM, PD_SIGN_ENUM, PD_NUM, PD_CASH_OPERATION_NUMBER,

PD_PRINT_QTY, trunc(sysdate - dbms_random.value(1,300)), '01.01.2010', '01.01.2010', PD_PAYER_ACC_NUM,

PD_PAYER_NAME, PD_PAYER_INN, PD_PAYER_KPP, PD_PAYER_BANK_ACC_NUM,

PD_BEN_ACC_NUM, PD_BEN_NAME, PD_BEN_INN, PD_BEN_KPP,

PD_BEN_BANK_ACC_NUM, PD_AMOUNT, PD_PMT_DETAILS,

PD_CBC, PD_OKATO, PD_FISKAL_PERIOD, PD_FISKAL_DOC_NUM,

PD_FISKAL_DOC_DATE_C, PD_DEBIT_AMOUNT, PD_CREDIT_AMOUNT,

CURRENCY_OPER_SORT_CODE, PD_PAYER_RESIDENT_FLAG, PD_BEN_RESIDENT_FLAG,

PD_EXT_ID, PD_NUM_S, PD_CASH_OPERATION_NUMBER_S,

PD_CCY_EXCH_RATE, PD_PAYER_IDENTITY_CARD, PD_PRINCIPAL,

PD_PRINCIPAL_IDENTITY_CARD, PD_POA, PD_PRINT_FLAG,

PD_PRINT_CLIENT_INFO_FLAG, PD_CASH_RECEIPT_NUM, PD_CASH_DISBURSEMT_NUM,

PD_BEN_IDENTITY_CARD, BENEFICIARY_ID, MAIN_OPER_JOURNAL_ID,

PD_POA_EXP_DATE, PD_TAX_CHARGE_UID, PD_CB_CODE, DEBIT_DEPT_ID,

CREDIT_DEPT_ID, DEBIT_ID_SYS_CODE, CREDIT_ID_SYS_CODE,

DEBIT_SYNT_ACC_ID, CREDIT_SYNT_ACC_ID, PD_PAYER_ADDRESS,

DEBIT_AGR_ID, CREDIT_AGR_ID, PD_BENEFICIARY_SURNAME,

PD_BENEFICIARY_FIRST_NAME, PD_BENEFICIARY_PATRONYMIC

from pmt_doc s left join tmp t on s.PMT_DOC_ID = t.tmp_id

where rownum <= r and tmp_id is null;

if tableSize <=0 then r :=-1;

else

r := r - tableSize;

end if;

exit when r <= 0;

commit;

end loop;

update pmt_doc set PD_DATE = PD_REG_DATE

where pmt_doc_id > maxPmtId;

commit;

update pmt_doc set PD_VALUE_DATE = PD_REG_DATE

where pmt_doc_id > maxPmtId;

commit;

end fill_pmt_doc;

TRANS

create or replace procedure fill_trans_pos(cnt in number)

is

tDate date;

rows_in_iter number;

n number;

intial_min_pd number;

startSeq number;

curr_trans_seq number;

min_pmt_doc_id number;

max_pmt_doc_id number;

begin

--на каждый аккаунт по 6 транзакций

rows_in_iter := 2;

n:= cnt;

if (n - rows_in_iter < 0) then rows_in_iter := n;

end if;

while (n > 0)

loop

tDate := trunc(sysdate - dbms_random.value(1,200));

select coalesce(max(trans_num)+1, 1) into startSeq

from trans where trans_date = tDate;

select coalesce(max(trans_num)+1,startSeq) into startSeq from trans_pos where trans_date = tDate;

reset_seq('trans_pos_seq', to_char(startSeq));

for i in 1..1000

loop

curr_trans_seq := trans_pos_seq.nextval;

insert into trans(TRANS_DATE, TRANS_NUM, REVERSE_TRANS_DATE, REVERSE_TRANS_NUM,

DEPT_ID,PMT_DOC_ID,TRANS_REVERSE_FLAG)

select trunc(tDate), curr_trans_seq, null,null,515, 35487,0

from dual

where rownum < 2;

insert into trans_pos(TRANS_DATE, TRANS_NUM, TRANS_POS_NUM, CURRENCY_ID,

CASH_SYMBOL_CODE, ACCOUNT_ID, TP_COR_NUM, TP_VALUE_DATE, TP_SIDE_ENUM, TP_AMOUNT,

TP_AMOUNT_RUB, TP_NEED_ACT_BAL_FLAG)

select trunc(tDate), curr_trans_seq, 1,126,37, 28155, 2, trunc(tDate), 'D',10000, 10000, null

from dual

where rownum < 2;

insert into trans_pos(TRANS_DATE, TRANS_NUM, TRANS_POS_NUM, CURRENCY_ID,

CASH_SYMBOL_CODE, ACCOUNT_ID, TP_COR_NUM, TP_VALUE_DATE, TP_SIDE_ENUM, TP_AMOUNT,

TP_AMOUNT_RUB, TP_NEED_ACT_BAL_FLAG)

select trunc(tDate), curr_trans_seq, 2,126,37,28155, 2, trunc(tDate), 'D',10000,10000,null

from dual

where rownum < 2;

end loop;

n := n - rows_in_iter - 1000;

end loop;

end fill_trans_pos;

AUDIT_LOG

create or replace procedure fill_audit_log(min_us_id in number, cnt in number)

is

event numbertype;

max_us number;

rows_in_iter number;

n number;

m number;

begin

select r bulk collect into event from

(select event_sort_id r from event_sort);

select max(user_session_id) into max_us from user_session;

rows_in_iter := 10000;

n:= cnt;

m := min_us_id;

if (n - rows_in_iter < 0) then rows_in_iter := n;

end if;

while (n > 0)

loop

insert into audit_log

select s_audit_log.nextval, user_session_id, 330001, trunc(sysdate - dbms_random.value(1,300)), dbms_random.string('x', 10)

from user_session

where user_session_id > m and rownum < rows_in_iter;

update audit_log set event_sort_id = event(round(dbms_random.value(1,event.count)))

where user_session_id > m;

m := m + rows_in_iter;

n := n - rows_in_iter;

if (n > 0 and (m + rows_in_iter) > max_us) then

m := min_us_id;

end if;

commit;

end loop;

end;

OBJ_ID_SYS_ROLE

create or replace procedure FILL_OBJ_ID_SYS_ROLE(cnt in number)

is

rows_in_iter number;

n number;

SYS_ROLE_CODE number;

SYS_CODE varchar2(16 byte);

cnt_av number;

begin

rows_in_iter := 300000;

n:= cnt;

if (n - rows_in_iter < 0) then rows_in_iter := n;

end if;

SYS_ROLE_CODE :=1;

SYS_CODE :='99995';

while (n >0)

loop

SAVEPOINT start_transaction;

select count(*) into cnt_av from FCY_CASH_REGISTER_OPER f left join obj_id_sys_role ob on to_char(f.fcy_cash_register_id) = ob.OISR_OBJ_ID

where rownum < rows_in_iter and ob.OISR_OBJ_ID is null and f.fcy_cash_register_id is not null;

insert into obj_id_sys_role (OISR_OBJ_ID, OISR_TABLE, ID_SYS_ROLE_CODE, ID_SYS_CODE)

select distinct f.fcy_cash_register_id, 'OPER_JOURNAL', SYS_ROLE_CODE, SYS_CODE

from FCY_CASH_REGISTER_OPER f left join obj_id_sys_role ob on to_char(f.fcy_cash_register_id) = ob.OISR_OBJ_ID

where rownum < rows_in_iter and ob.OISR_OBJ_ID is null and f.fcy_cash_register_id is not null;

if (cnt_av < rows_in_iter) then

n:= n- cnt_av;

select id_sys_code into SYS_CODE from id_sys

where id_sys_code not like '99995'

order by dbms_random.value;

else

n := n - rows_in_iter;

SYS_ROLE_CODE :=1;

SYS_CODE :='99995';

end if;

commit;

end loop;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

rollback to start_transaction;

SYS_ROLE_CODE:=2;

select id_sys_code into SYS_CODE from id_sys

where id_sys_code not like '99995'

order by dbms_random.value;

dbms_output.put_line('dups');

WHEN OTHERS THEN

ROLLBACK;

end;

PUBLIC_SERVICE_OJ_PAR

create or replace procedure fill_public_service_oj_par(min_oj_id in number, cnt in number)

is

min_psop_id number;

oj numbertype;

psrp numbertype;

j number;

begin

select max(public_service_repmt_par_code) into min_psop_id from public_service_oj_par;

select r bulk collect into oj

from (

select oper_journal_id r from oper_journal

where oper_journal_id > min_oj_id and rownum < cnt

)rndm;

select r bulk collect into psrp

from (

select public_service_repmt_par_code r from public_service_repmt_par

order by dbms_random.value

)rndm;

j := 1;

for i in 1..oj.count

loop

insert into public_service_oj_par(OPER_JOURNAL_ID, PUBLIC_SERVICE_REPMT_PAR_CODE,

PUBLIC_SERVICE_OJ_PAR_SN, PSOP_VALUE, PSOP_MOMENT_ENUM, ID_SYS_CODE)

values(oj(i), psrp(j), round(dbms_random.value(1,7)), dbms_random.string('x', 10) ,1, null);

j:=j+1;

if j > psrp.count then j := 1;

end if;

end loop;

end;

CLIENT

create or replace procedure fill_client_tmp(num in number)

is

clients_cnt number;

i number;

dept number;

type rowid_nt is table of rowid;

rowids rowid_nt;

begin

--заполняем темповую таблицу клиентами и рандомными департаментами. Количество записей = количеству создаваемых аккаунтов.

--берутся клиенты без аккаутов

--delete from tmp_client;

insert into tmp_client (id, cl_id)

select tsec.nextval, c.client_id from client c left join account a on c.client_id = a.client_id

where a.client_id is null and rownum < i;

commit;

dbms_output.put_line('filled clliet_id');

select r bulk collect into rowids

from (

select rowid r from tmp_client

order by dbms_random.value

) rndm;

forall j in 1..rowids.count

update tmp_client set d_id = ( select dept_id from

(select dept_id from dept order by dbms_random.value) t

where rownum = 1 )

where rowid=rowids(j) and d_id is null;

commit;

dbms_output.put_line('updated dept_id');

forall j in 1..rowids.count

update tmp_client set s_acc_id = ( select SYNT_ACC_ID from

(select SYNT_ACC_ID from synt_acc order by dbms_random.value) t

where rownum = 1 )

where rowid=rowids(j) and s_acc_id is null;

dbms_output.put_line('updated s_acc_id');

commit;

end fill_client_tmp;

PMT_DOC_EXT

create or replace PROCEDURE fill_pmt_doc_ext(num in number)

is

minJoinedId number;

max_pmt_doc_id number;

begin

select max(pmt_doc_id) into max_pmt_doc_id from pmt_doc;

select max(pd.pmt_doc_id) into minJoinedId from pmt_doc pd join pmt_doc_ext pde on pde.pmt_doc_id = pd.pmt_doc_id;

--10294362

--11294569

insert into pmt_doc_ext (PMT_DOC_ID,ID_SYS_CODE,PDE_EXT_ID)

select p.PMT_DOC_ID, ID_SYS_CODE, tmp_pde_doc_ext_id.nextval

from pmt_doc p join pmt_doc_ext pde on p.PMT_DOC_ID- 4*(max_pmt_doc_id - minJoinedId)/3 = pde.pmt_doc_id

where p.PMT_DOC_ID > minJoinedId and rownum < num;

end;

--create sequence tmp_pde_doc_ext_id increment by 960241436 minvalue 1

--select tmp_pde_doc_ext_id.nextval from dual

--alter sequence tmp_pde_doc_ext_id increment by 1 minvalue 1

PMT_DOC

create or replace procedure fill_pmt_doc(cnt in number)

is

tableSize number;

r number;

maxPmtId number;

minPmtId number;

rows_in_iter number;

n number;

mAcc number;

m number;

minPD number;

begin

select max(pmt_doc_id) into minPmtId from pmt_doc;

rows_in_iter := 100000;

n:= cnt;

minPD:=minPmtId;

if (n - rows_in_iter < 0) then rows_in_iter := n;

end if;

while (n > 0)

loop

select max(pmt_doc_id) into maxPmtId from pmt_doc;

m:= round(dbms_random.value(minPmtId,maxPmtId));

insert into pmt_doc(PMT_DOC_ID,DEBIT_ACCOUNT_ID,CREDIT_ACCOUNT_ID,

CASH_OPERATION_SORT_CODE,PAR_PMT_DOC_ID,IDENTITY_CARD_ID,

OPER_JOURNAL_ID,PMT_TYPE_CODE,PMT_SORT_CODE,PMT_ORDER_TYPE_CODE,

PAYER_BIC, BEN_BIC, CREDIT_CASH_SYMBOL_CODE, DEBIT_CASH_SYMBOL_CODE,

PRINT_TMPL_TYPE_ID, PAYER_STATUS_CODE, PMT_GROUND_TYPE_CODE,

CURRENCY_ID, MARK_DOC_CODE, DEBIT_CURRENCY_ID, CREDIT_CURRENCY_ID,

PD_ORD_COMP_ENUM, PD_SIGN_ENUM, PD_NUM, PD_CASH_OPERATION_NUMBER,

PD_PRINT_QTY, PD_REG_DATE, PD_DATE, PD_VALUE_DATE, PD_PAYER_ACC_NUM,

PD_PAYER_NAME, PD_PAYER_INN, PD_PAYER_KPP, PD_PAYER_BANK_ACC_NUM,

PD_BEN_ACC_NUM, PD_BEN_NAME, PD_BEN_INN, PD_BEN_KPP,

PD_BEN_BANK_ACC_NUM, PD_AMOUNT, PD_PMT_DETAILS,

PD_CBC, PD_OKATO, PD_FISKAL_PERIOD, PD_FISKAL_DOC_NUM,

PD_FISKAL_DOC_DATE_C, PD_DEBIT_AMOUNT, PD_CREDIT_AMOUNT,

CURRENCY_OPER_SORT_CODE, PD_PAYER_RESIDENT_FLAG, PD_BEN_RESIDENT_FLAG,

PD_EXT_ID, PD_NUM_S, PD_CASH_OPERATION_NUMBER_S,

PD_CCY_EXCH_RATE, PD_PAYER_IDENTITY_CARD, PD_PRINCIPAL,

PD_PRINCIPAL_IDENTITY_CARD, PD_POA, PD_PRINT_FLAG,

PD_PRINT_CLIENT_INFO_FLAG, PD_CASH_RECEIPT_NUM, PD_CASH_DISBURSEMT_NUM,

PD_BEN_IDENTITY_CARD, BENEFICIARY_ID, MAIN_OPER_JOURNAL_ID,

PD_POA_EXP_DATE, PD_TAX_CHARGE_UID, PD_CB_CODE, DEBIT_DEPT_ID,

CREDIT_DEPT_ID, DEBIT_ID_SYS_CODE, CREDIT_ID_SYS_CODE,

DEBIT_SYNT_ACC_ID, CREDIT_SYNT_ACC_ID, PD_PAYER_ADDRESS,

DEBIT_AGR_ID, CREDIT_AGR_ID, PD_BENEFICIARY_SURNAME,

PD_BENEFICIARY_FIRST_NAME, PD_BENEFICIARY_PATRONYMIC)

select s_pmt_doc.nextval, DEBIT_ACCOUNT_ID,CREDIT_ACCOUNT_ID,

CASH_OPERATION_SORT_CODE,PAR_PMT_DOC_ID,IDENTITY_CARD_ID,

OPER_JOURNAL_ID,PMT_TYPE_CODE,PMT_SORT_CODE,PMT_ORDER_TYPE_CODE,

PAYER_BIC, BEN_BIC, CREDIT_CASH_SYMBOL_CODE, DEBIT_CASH_SYMBOL_CODE,

PRINT_TMPL_TYPE_ID, PAYER_STATUS_CODE, PMT_GROUND_TYPE_CODE,

CURRENCY_ID, MARK_DOC_CODE, DEBIT_CURRENCY_ID, CREDIT_CURRENCY_ID,

PD_ORD_COMP_ENUM, PD_SIGN_ENUM, PD_NUM, PD_CASH_OPERATION_NUMBER,

PD_PRINT_QTY, trunc(sysdate - dbms_random.value(1,300)), '01.01.2010', '01.01.2010', PD_PAYER_ACC_NUM,

PD_PAYER_NAME, PD_PAYER_INN, PD_PAYER_KPP, PD_PAYER_BANK_ACC_NUM,

PD_BEN_ACC_NUM, PD_BEN_NAME, PD_BEN_INN, PD_BEN_KPP,

PD_BEN_BANK_ACC_NUM, PD_AMOUNT, PD_PMT_DETAILS,

PD_CBC, PD_OKATO, PD_FISKAL_PERIOD, PD_FISKAL_DOC_NUM,

PD_FISKAL_DOC_DATE_C, PD_DEBIT_AMOUNT, PD_CREDIT_AMOUNT,

CURRENCY_OPER_SORT_CODE, PD_PAYER_RESIDENT_FLAG, PD_BEN_RESIDENT_FLAG,

PD_EXT_ID, PD_NUM_S, PD_CASH_OPERATION_NUMBER_S,

PD_CCY_EXCH_RATE, PD_PAYER_IDENTITY_CARD, PD_PRINCIPAL,

PD_PRINCIPAL_IDENTITY_CARD, PD_POA, PD_PRINT_FLAG,

PD_PRINT_CLIENT_INFO_FLAG, PD_CASH_RECEIPT_NUM, PD_CASH_DISBURSEMT_NUM,

PD_BEN_IDENTITY_CARD, BENEFICIARY_ID, MAIN_OPER_JOURNAL_ID,

PD_POA_EXP_DATE, PD_TAX_CHARGE_UID, PD_CB_CODE, DEBIT_DEPT_ID,

CREDIT_DEPT_ID, DEBIT_ID_SYS_CODE, CREDIT_ID_SYS_CODE,

DEBIT_SYNT_ACC_ID, CREDIT_SYNT_ACC_ID, PD_PAYER_ADDRESS,

DEBIT_AGR_ID, CREDIT_AGR_ID, PD_BENEFICIARY_SURNAME,

PD_BENEFICIARY_FIRST_NAME, PD_BENEFICIARY_PATRONYMIC

from pmt_doc s left join tmp t on s.PMT_DOC_ID = t.tmp_id

where (pmt_doc_id between minPmtId and m) and rownum < rows_in_iter;

maxPmtId := maxPmtId + rows_in_iter;

if (m >= rows_in_iter) then

n := n - rows_in_iter;

else

n := n - m;

end if;

minPmtId := minPmtId + rows_in_iter;

if (n > 0 and (minPmtId + rows_in_iter) > maxPmtId) then

minPmtId := minPD;

end if;

commit;

end loop;

update pmt_doc set PD_DATE = PD_REG_DATE

where pmt_doc_id > maxPmtId;

commit;

update pmt_doc set PD_VALUE_DATE = PD_REG_DATE

where pmt_doc_id > maxPmtId;

commit;

end fill_pmt_doc;

PERSON,

IDENTITY_CARD,

ADDRESS

create or replace procedure fill_persons(num in number)

is

addr VARCHAR2(20);

pers_id number;

curr_ident_type number;

type_cnt integer;

begin

select count(IDENTITY_CARD_TYPE_ID) into type_cnt from identity_card_type;

for cnt in 1..num

loop

addr := concat(concat(dbms_random.string('x', 10), ', '),dbms_random.string('x', 5));

insert into ADDRESS (address_id, address_full)

values(S_ADDRESS.nextval, addr);

pers_id := s_person.nextval;

insert into PERSON(person_id, person_reg_state_type_code, person_post_state_type_code, person_surname, person_first_name, person_patronymic, person_sex_enum, person_birth_date, person_reg_address, person_post_address, person_illiterate_flag, person_resident_flag, person_verified_flag, person_citizen_country_code, person_code_word, birth_country_code, person_refugee_flag, person_act_address)

values(pers_id, 643, 643, dbms_random.string('x',7), dbms_random.string('x',7), dbms_random.string('x',8), 'M', '01.05.83',

addr,addr,0,1,1,643,'МЕРКУРИЙ',643,0, addr);

select IDENTITY_CARD_TYPE_ID into curr_ident_type from

(select IDENTITY_CARD_TYPE_ID from identity_card_type

order by dbms_random.value) t

where rownum = 1;

insert into identity_card(IDENTITY_CARD_ID, IDENTITY_CARD_TYPE_ID, PERSON_ID,

COUNTRY_CODE,IC_SERIES,IC_NUM,IC_ISSUE_DATE,IC_ISSUE_AUTHORITY,IC_VALID_THRU_DATE,

IC_ISSUE_AUTHORITY_CODE,IC_ISSUE_AUTHORITY_EN,IC_PRIMARY_FLAG,IC_DEL_FLAG)

values (S_IDENTITY_CARD.nextval, curr_ident_type, pers_id, null, 4004, 189745,

'12.12.12', 'ОВД', '31.12.29', '782-031', null, 1, 0);

end loop;

end fill_persons;

FCY_CASH_REGISTER_OPER

create or replace procedure FILL_FCY_CASH_REGISTER_OPER(cnt in number)

is

rows_in_iter number;

n number;

begin

rows_in_iter := 100000;

n:= cnt;

if (n - rows_in_iter < 0) then rows_in_iter := n;

end if;

while (n >0)

loop

insert into fcy_cash_register_oper (PMT_DOC_ID, OPER_JOURNAL_ID, FCY_CASH_REGISTER_ID,

FCRO_SN, FCRO_RATE, RECEIVED_CURRENCY_ID, FCRO_RECEIVED_AMOUNT, PAYOUT_CURRENCY_ID,

FCRO_PAYOUT_AMOUNT, FCRO_USE_CARD_FLAG, FCRO_CHEQUE_QTY, CHEQUE_CURRENCY_ID,

FCRO_CHEQUE_AMOUNT, ACCOUNT_ID, FCRO_DISPONENT_SORT_ENUM, FCY_IN_CASH_N_CHK_SORT_CODE,

FCRO_ACCOUNT_NUM)

select pd.pmt_doc_id, main_oper_journal_id, S_FCY_CASH_REGISTER.nextval,

5, 40.55, 130, round(dbms_random.value(1,200000)),126, null,1,null,null,

null,null,null,'P15',null

from pmt_doc pd left join fcy_cash_register_oper f on pd.pmt_doc_id= f.pmt_doc_id

where rownum < rows_in_iter and f.pmt_doc_id is null;

n := n - rows_in_iter;

end loop;

end;

USER_SESSION

create or replace function fill_user_session(cnt in number) return number

is

beginDate date;

rows_in_iter number;

max_us_id number;

min_us_id number;

n number;

begin

beginDate := sysdate - dbms_random.value(1,250);

dbms_output.put_line(beginDate);

n:= cnt;

rows_in_iter := 10000;

if (cnt <= 0) then n:=1; rows_in_iter := 1;

else if (cnt - rows_in_iter) < 0 then rows_in_iter := cnt;

else rows_in_iter := 10000;

end if;

end if;

select max(user_session_id) into max_us_id from user_session;

select max(user_session_id) into min_us_id from user_session;

while (n > 0)

loop

beginDate := sysdate - dbms_random.value(1,250);

insert into user_session (user_session_id, us_begin_time, us_end_time,

us_status_enum, us_operday_date, dept_id, computer_id,

user_id, us_ip, us_user_agent, us_server_name, us_http_session_id, real_user_id)

select s_user_session.nextval, beginDate, beginDate, us_status_enum, null,

dept_id, computer_id,user_id, us_ip, us_user_agent, us_server_name,

us_http_session_id, real_user_id

from user_session

where user_session_id > min_us_id and rownum <= rows_in_iter;

commit;

min_us_id := min_us_id + rows_in_iter;

n := n - rows_in_iter;

end loop;

return max_us_id;

end fill_user_session;

VALUABLE_BALANCE

create or replace

procedure fill_val_balance(cnt in number)

is

cntToInsert number;

currReg number;

nextId number;

currBal number;

accountId number;

vSortId number;

deptID number;

tdate date;

begin

select count(*) into currReg from valuable_register;

select count(*) into currBal from valuable_balance;

cntToInsert := cnt * currReg / currBal;

if (cntToInsert < 1) then cntToInsert := 1;

end if;

for i in 1..cntToInsert

loop

select account_id into accountId from account sample (0.1) where rownum < 2 order by dbms_random.value ;

select dept_id into deptID from dept sample (10) where rownum < 2 order by dbms_random.value ;

select vs.valuable_sort_id into vSortId from valuable_sort vs left join valuable_register vr on vs.valuable_sort_id = vr.valuable_sort_id

where (vr.valuable_sort_id is null or dept_id is null) and rownum < 2 order by dbms_random.value ;

select s_valuable_register.nextval into nextId from dual;

--valuable register;

insert into valuable_register(VALUABLE_REGISTER_ID, DEPT_ID, VALUABLE_SORT_ID, ACCOUNT_ID)

values (nextId, deptID, vsortid, accountid);

commit;

--valueable_balance

for i in 1..round(currBal/currReg)

loop

tDate := sysdate - i;

insert into valuable_balance(VALUABLE_REGISTER_ID, VB_DATE, VB_QUANTITY,

VB_BALANCE, VB_RESERVED_BALANCE, VB_RESERVED_QTY)

values(nextId, trunc(tDate), round(dbms_random.value(0,100)),

round(dbms_random.value(1,100000)), 0, 0);

commit;

end loop;

end loop;

end;

POS_REGISTER_OPER

create or replace procedure FILL_POS_REGISTER_OPER(cnt in number)

is

rows_in_iter number;

n number;

min_oj_id number;

max_oj_id number;

init_min_oj_id number;

begin

rows_in_iter := 300000;

n:= cnt;

select min(oj.oper_journal_id)into min_oj_id from FCY_CASH_REGISTER_OPER oj left join pos_register_oper p on oj.oper_journal_id = p.oper_journal_id

where oj.oper_journal_id > 1000000 and p.oper_journal_id is null;

select max(oper_journal_id) into max_oj_id from FCY_CASH_REGISTER_OPER;

init_min_oj_id := min_oj_id;

if (n - rows_in_iter < 0) then rows_in_iter := n;

end if;

while (n >0)

loop

insert into pos_register_oper (OPER_JOURNAL_ID, PRO_CARD_OPER_TYPE_ENUM,

POS_OPER_REGISTER_ID, PRO_SN, PRO_CARD_NUM,

PRO_AUTH_CODE, PRO_TERMINAL_CODE, PRO_REF_CODE, PRO_RESPONSE_CODE,

PRO_SALES_SLIP_NUM, PRO_BATCH_NUM, PRO_RRN, PRO_EXP_DATE, PRO_OWN_CARD_FLAG,

PRO_ACCOUNT_NUM, PRO_PARAM1, PRO_PARAM2, PRO_PARAM3, CARD_DEPT_ID,

PRO_MULTI_CURRENCY_FLAG)

select distinct oj.oper_journal_id, ceil(dbms_random.value(1,2)),

1199, ceil(dbms_random.value(1,5)), ceil(dbms_random.value(4272000000009795,4272999999999795)),

'509216','55535101','000321','256',

'787878787',0,'310901464561','30.09.17',1,'40702810500000101768',

'266995',null,null,null, 0

from FCY_CASH_REGISTER_OPER oj left join pos_register_oper p on oj.oper_journal_id = p.oper_journal_id

where rownum < rows_in_iter and oj.OPER_JOURNAL_ID between min_oj_id and max_oj_id

and p.oper_journal_id is null;

n := n - rows_in_iter;

min_oj_id := min_oj_id + rows_in_iter;

if (min_oj_id >= max_oj_id) then

min_oj_id := init_min_oj_id;

end if;

commit;

end loop;

end;

LIM_REC_POSTING

create or replace procedure fill_lim_rec_posting(min_lim_rec_id in number, min_oper_id in number, cnt in number)

is

n number;

rows_in_iter number;

m_LR_Id number;

min_lrp_id number;

type rowid_nt is table of rowid;

lrp rowid_nt;

oj numbertype;

begin

n:=cnt;

rows_in_iter := 100000;

m_LR_Id := min_lim_rec_id;

select max(LIM_REC_POSTING_ID) into min_lrp_id from LIM_REC_POSTING;

while (n > 0)

loop

insert into lim_rec_posting(LIM_REC_POSTING_ID, LIM_REC_ID,

LRP_ACTION_TIME, LRP_AMOUNT, OPER_JOURNAL_ID)

select s_lim_rec_posting.nextval, lim_rec_id, trunc(sysdate-dbms_random.value(1,200)),1, null

from lim_rec

where lim_rec_id > m_LR_Id and rownum <= rows_in_iter;

m_LR_Id := m_LR_Id + rows_in_iter;

n := n - rows_in_iter;

commit;

end loop;

select r bulk collect into lrp

from (

select rowid r from lim_rec_posting

where LIM_REC_POSTING_ID > min_lim_rec_id

) rndm;

select r bulk collect into oj

from (

select oper_journal_id r from oper_journal

where oper_journal_id > min_oper_id and rownum <

(select max(LIM_REC_POSTING_ID) - min_lrp_id from LIM_REC_POSTING)

)rndm;

forall j in 1..lrp.count

update lim_rec_posting set oper_journal_id = oj(j)

where rowid = lrp(j);

commit;

end;

LIM_REC

create or replace function fill_lim_rec(minaccId in number, cnt in number) return number

is

min_limrec_id number;

n number;

iter number;

rows_in_iter number;

mAccId number;

maxAccId number;

begin

select max(lim_rec_id) into min_limrec_id from lim_rec;

select max(account_id) into maxAccId from account;

n:=cnt;

mAccId := minaccId;

rows_in_iter := 100000;

if (cnt - rows_in_iter) < 0 then rows_in_iter := cnt;

else rows_in_iter := 100000;

end if;

while (n > 0)

loop

insert into lim_rec(LIM_REC_ID,LIM_ID,LR_START_TIME,

LR_CATEGORY_ENUM,LR_OBJ_ID)

select s_lim_rec.nextval, 2, trunc(sysdate-dbms_random.value(1,200)),1, account_id

from account

where account_id > mAccId and rownum <= rows_in_iter;

mAccId := mAccId + rows_in_iter;

if (mAccId >= maxAccId) then mAccId:=minaccId;

end if;

n:=n-rows_in_iter;

commit;

end loop;

update lim_rec set lr_next_time = lr_start_time where lim_rec_id > min_limrec_id;

commit;

return min_limrec_id;

end fill_lim_rec;

ACCOUNT

create or replace

function fill_account(num in number) return number

is

i number;

cnt number;

mId number;

begin

select count(id) into cnt from tmp_client;

i:=num;

mId := s_account.nextval;

loop

insert into account (ACCOUNT_ID, DEPT_ID,

CURRENCY_ID, CLIENT_ID, SYNT_ACC_ID,

ACCOUNT_NUM, ACCOUNT_REG_DATE, ACCOUNT_STATUS_ENUM,

ACCOUNT_NAME, EXECUTIVE_USER_ID)

select s_account.nextval, t.d_id, 126, t.cl_id, t.s_acc_id, round(dbms_random.value(40700000000000000000,40799999999999999999)),

'16.04.09',0,'ÚÂÍÛ˘ËÈ Ò˜ÂÚ',null

from tmp_client t where rownum <= i;

if (i = 0) then i:=-1;

else i:= i - cnt;

end if;

commit;

exit when i <= 0;

end loop;

return (mId + 1);

end fill_account;

BALANCE

reate or replace procedure fill_balance(num in number)

is

cnt number;

bDate date;

begin

if num = -1

then

select count(a.account_id) into cnt from account a left join balance b on a.account_id = b.account_id

where b.account_id is null;

else

cnt := num;

end if;

insert into balance(ACCOUNT_ID,BALANCE_DATE,

BALANCE_VALUE, BALANCE_VALUE_DATED, BALANCE_DEBIT, BALANCE_CREDIT)

select a.account_id,trunc(sysdate - dbms_random.value(1,250)),1000000,1000000,0,1000000

from account a left join balance b on a.account_id = b.account_id

where b.account_id is null and rownum <= cnt;

end fill_balance;