i am trying to simulate an alert for transaction being done on a bank account as my project but the person i asked to help me told me that using flexcube all i need is to execute the code below it would create a table OUTMESG which my own code.
---------------------script to generate sequence number to be used on the temporary table (transaction_log2)------------------------------------------
CREATE SEQUENCE transaction_log_sequence
INCREMENT BY 1
START WITH 362551
MINVALUE 1
MAXVALUE 999999999999999999999999999
NOCYCLE
NOORDER
CACHE 20
/
-- Grants for Sequence
GRANT SELECT ON transaction_log_sequence TO schema_name
/
--------------------------------Trigger on before insert on ch_nobook------------------------------------------
create trigger on
DECLARE
no_rows EXCEPTION;
BEGIN
INSERT INTO transaction_log2 (seqno,
acctno,
trans_amt,
dat_txn,
trans_description,
trans_date,
debit_credit,
trans_amt2,
txn_ccy,
cod_txn_mnemonic )
values (schema_name.transaction_log2_sequence.nextval,
:NEW.COD_ACCT_NO,
:NEW.amt_txn_tcy,
:NEW.dat_post,
:NEW.txt_txn_desc,
sysdate,
:NEW.cod_drcr,
to_char(:NEW.amt_txn_tcy , '99,999,999,999,999.99'),
:NEW.cod_txn_ccy,
:NEW.cod_txn_mnemonic);
IF (SQL%ROWCOUNT = 0) THEN
RAISE no_rows;
END IF;
EXCEPTION
WHEN no_rows THEN
ora_raiserror(SQLCODE, 'No rows inserted in transaction_log2 110',
79);
RAISE;
WHEN others THEN
ora_raiserror(SQLCODE, 'insert failed in transaction_log2 111',
82);
RAISE;
END;
---------------------------Create Temporary copy of transacton table (transaction_log2)-------------------------------------
CREATE TABLE transaction_log2
(seqno NUMBER(10,0),
acctno CHAR(16),
trans_amt NUMBER,
dat_txn DATE,
trans_description VARCHAR2(120),
trans_date DATE,
debit_credit VARCHAR2(6),
trans_amt2 VARCHAR2(40),
txn_ccy NUMBER(5,0),
cod_txn_mnemonic NUMBER(5,0))
PCTFREE 10
INITRANS 100
MAXTRANS 255
TABLESPACE flexlive
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
-- Grants for Table
GRANT ALTER ON transaction_log2 TO skyelive
/
GRANT DELETE ON transaction_log2 TO skyelive
/
GRANT INDEX ON transaction_log2 TO skyelive
/
GRANT INSERT ON transaction_log2 TO skyelive
/
GRANT SELECT ON transaction_log2 TO skyelive
/
GRANT UPDATE ON transaction_log2 TO skyelive
/
GRANT REFERENCES ON transaction_log2 TO skyelive
/
GRANT ON COMMIT REFRESH ON transaction_log2 TO skyelive
/
GRANT QUERY REWRITE ON transaction_log2 TO skyelive
/
GRANT DEBUG ON transaction_log2 TO skyelive
/
GRANT FLASHBACK ON transaction_log2 TO skyelive
/
-- Indexes for TRANSACTION_LOG2
CREATE INDEX indx_translog_date ON transaction_log2
(
TRUNC("TRANS_DATE") ASC
)
PCTFREE 10
INITRANS 100
MAXTRANS 255
TABLESPACE retailindx
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
CREATE INDEX indx_translog2 ON transaction_log2
(
seqno ASC
)
PCTFREE 10
INITRANS 100
MAXTRANS 255
TABLESPACE retailindx
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
-------------------------
scheduler can be configure on either windows or database level. It determines the frequences of pulling transactions from the database using the store procedure pulling shown below.
---------------------stored procedure to pull subscribed customers on transaction alert-----------------------------------------------
PROCEDURE TRANSALERT
AS
maxno number(10);
cursor ca is
select /*+ index(a indx_translog2) */
a.seqno, '234'||b.gsmno as gsmno, 'Alert!'||chr(10)||'Type: '||DECODE(a.debit_credit,'D','Debit','C','Credit')||chr(10)||
'Acct No: ' ||trim(a.acctno)||chr(10)||'Amt ='||DECODE(a.txn_ccy,1,'N=',2,'USD',3,'GBP',13,'EUR')||ltrim(to_char(a.trans_amt, '999,999,999.99'))||chr(10)|| 'Details: ' || a.trans_description ||chr(10)||
'Bal ='||DECODE(a.txn_ccy,1,'N=',2,'USD',3,'GBP',13,'EUR')|| trim(to_char(c.bal_available, '9,999,999,999.99')) ||chr(10)|| 'Date: ' || trunc(a.trans_date) as content,'F' as status,
'Super Bank' as sendername, '1' as requestreceipt, '0' as sendasflash, '' as sentmsgserverid, a.acctno,
a.trans_date, a.trans_description, DECODE(a.debit_credit,'D','Debit','C','Credit') as debit_credit, a.trans_amt,
c.bal_available, c.cod_cc_brn, '0' as appid, c.cod_acct_stat
from transaction_log2 a, transaction_alert b, ch_acct_mast c
where a.acctno=b.acctno
and a.seqno > maxno
and length(b.gsmno)= 10
and a.ACCTNO = c.COD_ACCT_NO
and c.flg_mnt_status='A' and c.cod_prod not in ('205','206','207','208','209','210')
and a.trans_description not like '%REV SERVICE%'
and c.cod_acct_no not in ('7676873453349','5675675675675','567576565765675','65544354354354')
---and trunc(a.dat_txn)= trunc(a.trans_date)
and a.trans_amt >= b.credit_threshold
and a.debit_credit ='C'
union all
select /*+ index(a indx_translog2) */
a.seqno, '234'||b.gsmno as gsmno, 'Alert!'||chr(10)||'Type: '||DECODE(a.debit_credit,'D','Debit','C','Credit')||chr(10)||
'Acct No: ' ||trim(a.acctno)||chr(10)||'Amt ='||DECODE(a.txn_ccy,1,'N=',2,'USD',3,'GBP',13,'EUR')||ltrim(to_char(a.trans_amt, '999,999,999.99'))||chr(10)|| 'Details: ' || a.trans_description ||chr(10)||
'Bal ='||DECODE(a.txn_ccy,1,'N=',2,'USD',3,'GBP',13,'EUR')|| trim(to_char(c.bal_available, '9,999,999,999.99')) ||chr(10)|| 'Date: ' || trunc(a.trans_date) as content,'F' as status,
'Super Bank' as sendername, '1' as requestreceipt, '0' as sendasflash, '' as sentmsgserverid, a.acctno,
a.trans_date, a.trans_description, DECODE(a.debit_credit,'D','Debit','C','Credit') as debit_credit, a.trans_amt,
c.bal_available, c.cod_cc_brn, '0' as appid, c.cod_acct_stat
from transaction_log2 a, transaction_alert b, ch_acct_mast c
where a.acctno=b.acctno
and a.seqno > maxno
and length(b.gsmno)= 10
and a.ACCTNO = c.COD_ACCT_NO
and c.flg_mnt_status='A' and c.cod_prod not in ('205','206','207','208','209','210')
and a.trans_description not like '%REV SERVICE%'
and c.cod_acct_no not in ('7676873453349','5675675675675','567576565765675','65544354354354')
---and trunc(a.dat_txn)= trunc(a.trans_date)
and a.trans_amt >= b.debit_threshold
and a.debit_credit ='D';
v1 ca%ROWTYPE;
begin
select nvl(max(outmsgqid),0) into maxno from outmsgq;
open ca;
loop fetch ca into v1;
exit when ca%NOTFOUND;
insert into outmsgq values(v1.seqno, v1.gsmno, v1.content,
v1.status,
v1.sendername, v1.requestreceipt, v1.sendasflash, v1.sentmsgserverid, v1.acctno,
v1.trans_date, v1.trans_description, v1.debit_credit, v1.trans_amt, v1.bal_available, v1.cod_cc_brn,v1.appid,v1.cod_acct_stat);
end loop;
commit;
end;
----------------------------create table for generating outgoing messages------------------------------------------------------------------
CREATE TABLE outmsgq
(outmsgqid NUMBER NOT NULL,
destphoneno VARCHAR2(100),
content VARCHAR2(4000),
status VARCHAR2(1),
sendername VARCHAR2(15),
requestreceipt VARCHAR2(1),
sendasflash VARCHAR2(1),
sentmsgserverid VARCHAR2(35),
acctno CHAR(16),
trans_date DATE,
trans_description VARCHAR2(2000),
debit_credit VARCHAR2(6),
trans_amt NUMBER,
bal_available NUMBER,
cod_cc_brn NUMBER(5,0),
appid CHAR(3),
cod_acct_stat CHAR(3))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE flexlive
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
-- Indexes for OUTMSGQ
CREATE INDEX indx_outmsgq1 ON outmsgq
(
status ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE flexlive
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
CREATE INDEX indx_outmsgq4 ON outmsgq
(
trans_date ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE flexlive
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
CREATE INDEX indx_outmsgq2_brn ON outmsgq
(
acctno ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE flexlive
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
CREATE INDEX indx_outmsgq3 ON outmsgq
(
cod_cc_brn ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE flexlive
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
CREATE INDEX indx_outmsgq5 ON outmsgq
(
sentmsgserverid ASC
)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE flexlive
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
-- Constraints for OUTMSGQ
ALTER TABLE outmsgq
ADD PRIMARY KEY (outmsgqid)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE retailindx
STORAGE (
INITIAL 81920
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
but the problem are these;
1) is there a way to create a sheduler on a Oracle DB to execute a procedure at interval or would i have to design an application to execute the procedure at interval
2) The Procedure TRANSALERT some error and i seem not to be able to know where to correct the error since i do not have flexcube schema on my system so i would want any help from any person as how i can get flexcube to be able to at LEAST create the DB on my system
3) i would also want to know if there is something wrong with the procedure TRANSALERT
4) i would also want to know where i can get the