Flexcube Problem

Question

awo 0 Newbie Poster

14 Years Ago

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

oracle

2 Contributors
1 Reply
292 Views
7 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by debasisdas

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

debasisdas 580 Posting Genius Featured Poster · Answer 1 · 2010-03-05T18:03:02+00:00

1. You can use DBMS_JOB or DBMS_SCHEDULER on a Oracle DB to execute a procedure at interval .

Other questions are totally irrelevant without the knowledge of the actual requirement.