DEAR EXPERTS,
I want to decline a customer who is doing transaction more than 5000 in 12 hours cooling period window since his first transaction.
create table TRAN_LOG
(
id number,
CUST_MOB varchar(20),
TRAN_AMT number,
TRAN_DATE timestamp default systimestamp
);
insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (1,'999999999',1000,TO_TIMESTAMP('17-JAN-19 10.24.54.918726000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (2,'999999999',3000,TO_TIMESTAMP('17-JAN-19 02.33.28.918726000 PM','DD-MON-RR HH.MI.SS.FF AM'));
insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (3,'88888888',2500,TO_TIMESTAMP('16-JAN-19 01.25.28.918726000 PM','DD-MON-RR HH.MI.SS.FF AM'));
insert into TRAN_LOG (id,CUST_MOB,TRAN_AMT,TRAN_DATE) values (4,'88888888',3000,TO_TIMESTAMP('17-JAN-19 04.42.28.918726000 PM','DD-MON-RR HH.MI.SS.FF AM'));
create table TRAN_LIMIT
(
LIMIT_AMT number,
LIMIT_TIME number
);
insert into TRAN_LIMIT values(5000,12);
HERE I WANT to DECLINE 999999999
and allow 88888888 because he has completed cooling period
Tried one
declare
P_MOB varchar2(20);
p_amt number;
CURSOR CUR_LMT
is
select NVL(SUM(TRAN_AMT),0) MCL_AMT_PD
from TRAN_LOG
where CUST_MOB =p_mob
GROUP BY CUST_MOB;
begin
FOR J IN CUR_LMT
LOOP
if J.MCL_AMT_PD > P_AMT then
dbms_output.put_line('wait for cooling period to complete');
RETURN;
end if;
END LOOP;
end;
end;
Please help