Store Procedure

CREATE OR REPLACE PROCEDURE TAMPUNGAN.GET_ALL_TRANS (P_TRANS_DT VARCHAR2) IS
CURSOR LOADRec (P_TRANS_DATE VARCHAR2) IS
select tr.ID, tr.DESCRIPTION, tr.CREATED
from trans tr
where tr.CREATED between to_timestamp(P_TRANS_DATE||'000000.000','YYYYMMDDHH24MISS.FF3')
and to_timestamp(P_TRANS_DATE||'235959.999','YYYYMMDDHH24MISS.FF3')
and tr.DESCRIPTION<>'Commission';
-- and rownum <= 1000;

CURSOR GETTransData (P_TRANS_ID NUMBER) IS
select td.td_key, trim(td.value) value
from trans_data td
where td.transid=P_TRANS_ID
and td.td_key in ('target','amount');

CURSOR GETTransExt (P_TRANS_ID NUMBER) IS
select txt.type_, txt.result
from trans_ext_trans txt
where txt.transid=P_TRANS_ID;

v_trans_id number;
v_description varchar2(40);
v_created date;
v_result varchar2(20);
v_target varchar2(255);
v_amount varchar2(255);

v_loop_count integer;
v_trans_dt varchar2(10);

BEGIN
v_loop_count := 0;

-- for default value use GET_ALL_TRANS('YYYYMMDD');
-- is mean get data of yesterday data
if P_TRANS_DT = 'YYYYMMDD' then
v_trans_dt := to_char(sysdate-1,'YYYYMMDD');
else
v_trans_dt := P_TRANS_DT;
end if;

for CurrRLDRec in LOADRec (v_trans_dt)
loop
begin
v_trans_id := CurrRLDRec.id;
v_description := CurrRLDRec.description;
v_created := CurrRLDRec.created;

v_result := null;
v_target := null;
v_amount := null;

for CurrTERec in GETTransExt (v_trans_id)
loop
v_result := CurrTERec.result;
end loop;

for CurrTDRec in GETTransData (v_trans_id)
loop
if CurrTDRec.td_key = 'target' then
if substr(CurrTDRec.value,1,1) <> '0' then
v_target := '0'||CurrTDRec.value;
else
v_target := CurrTDRec.value;
end if;
else
if CurrTDRec.td_key = 'amount' then
v_amount := CurrTDRec.value;
end if;
end if;
end loop;

-- Insert data to table
insert into tamp_daily
(TRANSID, DESCRIPTION, CREATED, RESULT, TARGET, AMOUNT)
values
(v_trans_id, v_description, v_created, v_result, v_target, v_amount);

v_loop_count := v_loop_count + 1;

-- commit if v_loop_count is more than those value
if v_loop_count >= 10000 then
commit;
v_loop_count := 0;
end if;

exception
when NO_DATA_FOUND then null;
when others then rollback;
end;
end loop;

commit;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END GET_ALL_TRANS;