This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Mar 21, 2012 10:50 PM by Luthfi RSS

Cursor returning wrong result

Luthfi Newbie
Currently Being Moderated
Hi,

Oracle Application Server / Oracle Form Server: Oracle 10g 9.0.4
Operating System: Linux

I've 3 tables, a package and procedure as below:
CREATE TABLE PAPER
(
  BRNCD       VARCHAR2(5 BYTE) NOT NULL,
  MODCD       VARCHAR2(2 BYTE) NOT NULL,
  ACNO        NUMBER(6)        NOT NULL,
  CHKDGT      NUMBER(2)        NOT NULL,
  CRLINE      VARCHAR2(2 BYTE) NOT NULL,
  PSBKBAL     NUMBER(16,2)     DEFAULT 0
)
*Primary key: BRNCD, MODCD, ACNO, CHKDGT
Sample Data:

INSERT INTO PAPER(BRNCD, MODCD, ACNO, CHKDGT, CRLINE, PSBKBAL) VALUES('00000', '01', 123, 12, 'SA', 1000);
INSERT INTO PAPER(BRNCD, MODCD, ACNO, CHKDGT, CRLINE, PSBKBAL) VALUES('00001', '02', 456, 23, 'CA', 150.30);

BRNCD  MODCD  ACNO  CHKDGT  CRLINE  PSBKBAL
-----  -----  ----  ------  ------  -------
00000  01     123   12      SA      1000
00001  02     456   23      CA      150.30
CREATE TABLE TRANX
(
  TRNBRN     VARCHAR2(5 BYTE)  NOT NULL,
  CRLINE     VARCHAR2(2 BYTE)  NOT NULL,
  YR         NUMBER(4)         NOT NULL,
  SEQNO      NUMBER(6)         NOT NULL,
  SEQCNT     NUMBER(6)         NOT NULL,
  BRNCD      VARCHAR2(5 BYTE)  NOT NULL,
  MODCD      VARCHAR2(2 BYTE)  NOT NULL,
  ACNO       NUMBER(6)         NOT NULL,
  CHKDGT     NUMBER(2)         NOT NULL,
  POST       VARCHAR2(1 BYTE)  NOT NULL,
  TRNAMT     NUMBER(16,2)      DEFAULT 0 NOT NULL,
  TRNCD      VARCHAR2(3 BYTE)  NOT NULL,
  TRNDT      DATE              NOT NULL,
  OVRRD      VARCHAR2(1 BYTE)  DEFAULT 'N',
  PSBKBAL    NUMBER(16,2)      DEFAULT 0,
  TIME       DATE     
)
*Primary key: TRNBRN, CRLINE, YR, SEQNO, SEQCNT
  FK (PAPER) : BRNCD, MODCD, ACNO, CHKDGT
Sample Data:

INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                  TRNDT, OVRRD, PSBKBAL, TIME)
               VALUES('00000', 'SA', 2012, 1, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                      TO_DATE('15/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('15/12/2012 10:10:00','DD/MM/YYYY HH24:MI:SS');
INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                  TRNDT, OVRRD, PSBKBAL, TIME)
               VALUES('00000', 'SA', 2012, 2, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                      TO_DATE('20/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('20/12/2012 09:09:00','DD/MM/YYYY HH24:MI:SS');
INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                  TRNDT, OVRRD, PSBKBAL, TIME)
               VALUES('00000', 'SA', 2012, 3, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                      TO_DATE('21/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('21/12/2012 11:21:00','DD/MM/YYYY HH24:MI:SS');
INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                  TRNDT, OVRRD, PSBKBAL, TIME)
               VALUES('00000', 'SA', 2012, 4, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                      TO_DATE('22/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('22/12/2012 07:09:00','DD/MM/YYYY HH24:MI:SS');
INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                  TRNDT, OVRRD, PSBKBAL, TIME)
               VALUES('00000', 'SA', 2012, 5, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                      TO_DATE('23/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('23/12/2012 13:09:00','DD/MM/YYYY HH24:MI:SS');
INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                  TRNDT, OVRRD, PSBKBAL, TIME)
               VALUES('00001', 'CA', 2012, 1, 1, '00001', '02', 456, 23, 'S', -5.25, '101', 
                      TO_DATE('20/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('20/12/2012 09:09:00','DD/MM/YYYY HH24:MI:SS');
INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                  TRNDT, OVRRD, PSBKBAL, TIME)
               VALUES('00000', 'SA', 2012, 6, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                      TO_DATE('24/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('24/12/2012 19:09:00','DD/MM/YYYY HH24:MI:SS');
INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                  TRNDT, OVRRD, PSBKBAL, TIME)
               VALUES('00000', 'SA', 2012, 7, 1, '00000', '01', 123, 12, 'U', -70, '102', 
                      TO_DATE('30/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('30/12/2012 09:09:00','DD/MM/YYYY HH24:MI:SS');

TRNBRN  CRLINE  YR    SEQNO  SEQCNT  BRNCD  MODCD  ACNO  CHKDGT  POST  TRNAMT  TRNCD  TRNDT        OVRRD  PSBKBAL  TIME
------  ------  ----  -----  ------  -----  -----  ----  ------  ----  ------  -----  ----------  -----  -------  ---------------------
00000   SA      2012  1      1       00000  01     123   12      U     -50     111    15/11/2012   N      0        15/12/2012 10:10:00 AM
00000   SA      2012  2      1       00000  01     123   12      U     -50     111    20/11/2012   N      0        20/12/2012 09:09:00 AM
00000   SA      2012  3      1       00000  01     123   12      U     -50     111    21/11/2012   N      0        21/12/2012 11:21:00 AM
00000   SA      2012  4      1       00000  01     123   12      U     -50     111    22/11/2012   N      0        22/12/2012 07:09:00 AM
00000   SA      2012  5      1       00000  01     123   12      U     -50     111    23/11/2012   N      0        23/12/2012 13:09:00 PM
00001   CA      2012  1      1       00001  02     456   23      S     -5.25   101    20/11/2012   N      0        20/12/2012 09:09:00 AM
00000   SA      2012  6      1       00000  01     123   12      U     -50     111    24/11/2012   N      0        24/12/2012 19:09:00 PM
00000   SA      2012  7      1       00000  01     123   12      U     -70     102    30/11/2012   N      0        30/12/2012 09:09:00 AM
CREATE TABLE CODE(
  CRLINE     VARCHAR2(2 BYTE)  NOT NULL,
  TRNCD      VARCHAR2(3 BYTE)  NOT NULL,
  DESCR      VARCHAR2(40 BYTE)
)
*Primary key: CRLINE, TRNCD
Sample Data:

INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('SA', '102', 'Goodies');
INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('SA', '111', 'Online');
INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('CA', '101', 'Bag');
INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('CA', '411', 'Book');
INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('CA', '413', 'Pencil');

CRLINE  TRNCD  DESCR
------  -----  -----
SA      102    Goodies
SA      111    Online
CA      101    Bag
CA      411    Book
CA      413    Pencil
PACKAGE SAFO_INIT IS
    n_Lineno      number(2);
    n_Psbkbal     number(16,2);
END;
PROCEDURE I_Psbk_Cont(B_FOFLG IN BOOLEAN, V_CRLINE IN VARCHAR2, V_BRNCD IN VARCHAR2, V_MODCD IN VARCHAR2,
N_ACNO IN NUMBER, N_CHKDGT IN NUMBER, V_TRNCD IN VARCHAR2, D_SYSDATE IN VARCHAR2) IS

     CURSOR psbcon IS
     SELECT trndt, a.trncd, trnamt, psbkbal, seqno, seqcnt 
     FROM tranx a, code b
     WHERE a.crline = v_crline
          AND brncd = v_brncd
          AND modcd = v_modcd
          AND acno = n_acno
          AND chkdgt = n_chkdgt
          AND ((a.crline in ('SA','IS') and post in ('U', 'S') OR (post = 'C' and psbkbal <> 0))
          or (a.crline in ('CA','IC') and post<>'C'))
          AND ovrrd IN ('N','A')
          AND a.crline = b.crline
          AND a.trncd = b.trncd
          and a.trncd != '455'
     ORDER BY trndt,TIME, seqno, seqcnt;

     CURSOR psbcon3 IS     
     SELECT trndt, a.trncd, trnamt, psbkbal, seqno, seqcnt 
     FROM tranx a, code b
     WHERE a.crline = v_crline
          AND brncd = v_brncd
          AND modcd = v_modcd
          AND acno = n_acno
          AND ((a.crline in ('SA','IS') and post in ('U', 'S')) or (a.crline in ('CA','IC') and post = 'S'))
          AND ovrrd IN ('N','A')
          AND a.crline = b.crline
          AND a.trncd = b.trncd
     ORDER BY trndt, TIME, seqno, seqcnt;

     prt_rec psbcon%ROWTYPE;

     n_bfbal NUMBER(16,2) := 0;
BEGIN
     IF b_foflg = TRUE THEN
          SELECT psbkbal INTO safo_init.n_psbkbal
          FROM paper
          WHERE crline = v_crline AND brncd = v_brncd AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt;
          
          IF v_trncd IN ('411','413') THEN
               n_bfbal := safo_init.n_psbkbal;
          END IF;

          n_bal := safo_init.n_psbkbal;

          IF v_trncd IN ('411','412','413') THEN
               OPEN psbcon3;
               FETCH psbcon3 INTO prt_rec;

               IF psbcon3%NOTFOUND THEN
                    CLOSE psbcon3;
                    RETURN;
               END IF;
          ELSE
               OPEN psbcon;
               FETCH psbcon INTO prt_rec;

               IF psbcon%NOTFOUND THEN
                    message('No');
                    CLOSE psbcon;
                    RETURN;
               END IF;
          END IF;

          IF v_trncd IN ('411','412','413') THEN
               CLOSE psbcon3;
               OPEN psbcon3;
          ELSE
               CLOSE psbcon;
               OPEN psbcon;
          END IF;

          LOOP
               IF v_trncd IN ('411','412','413') THEN
                    FETCH psbcon3 INTO prt_rec;
               ELSE
                    FETCH psbcon INTO prt_rec;
               END IF;
               v_scode := prt_rec.scode;

               IF v_trncd IN ('411','412','413') THEN
                    EXIT WHEN psbcon3%NOTFOUND;
               ELSE
                    EXIT WHEN psbcon%NOTFOUND;
               END IF;
               IF prt_rec.trncd IN ('411','413') THEN
                    safo_init.n_psbkbal := n_bal;
               END IF;

               safo_init.n_psbkbal := safo_init.n_psbkbal + prt_rec.trnamt;

               if v_crline in ('CA','IC') then  
                    UPDATE tranx SET psbkbal = safo_init.n_psbkbal                        
                    WHERE seqno = prt_rec.seqno AND seqcnt = prt_rec.seqcnt AND brncd = v_brncd
                         AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt
                         AND trnbrn = prt_rec.trnbrn AND post <> 'C';
               else 
                    UPDATE tranx SET post = 'P', psbkbal = safo_init.n_psbkbal                            
                    WHERE seqno = prt_rec.seqno AND seqcnt = prt_rec.seqcnt AND brncd = v_brncd
                         AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt
                         AND trnbrn = prt_rec.trnbrn AND post <> 'C';
               end if;

          END LOOP;  -- psbcon loop

          IF v_trncd IN ('411','412','413') THEN
               CLOSE psbcon3;
          ELSE
               CLOSE psbcon;
          END IF;

          COMMIT;

     ELSE
          Message('Problem');
     END IF;
END;
I run this procedure using the input as below:
I_Psbk_Cont(TRUE, 'SA', '00000', '01', 123, 12, '102', '30/11/2012')
The result (TRANX.POST & TRANX.PSBKBAL):
TRNBRN  CRLINE  YR    SEQNO  SEQCNT  BRNCD  MODCD  ACNO  CHKDGT  POST  TRNAMT  TRNCD  TRNDT        OVRRD  PSBKBAL  TIME
------  ------  ----  -----  ------  -----  -----  ----  ------  ----  ------  -----  ----------  -----  -------  ---------------------
00000   SA      2012  1      1       00000  01     123   12      P     -50     111    15/11/2012   N      950      15/12/2012 10:10:00 AM
00000   SA      2012  2      1       00000  01     123   12      P     -50     111    20/11/2012   N      900      20/12/2012 09:09:00 AM
00000   SA      2012  3      1       00000  01     123   12      P     -50     111    21/11/2012   N      850      21/12/2012 11:21:00 AM
00000   SA      2012  4      1       00000  01     123   12      P     -50     111    22/11/2012   N      800      22/12/2012 07:09:00 AM
00000   SA      2012  5      1       00000  01     123   12      P     -50     111    23/11/2012   N      794.75   23/12/2012 13:09:00 PM
00001   CA      2012  1      1       00001  02     456   23      S     -5.25   101    20/11/2012   N      0        20/12/2012 09:09:00 AM
00000   SA      2012  6      1       00000  01     123   12      P     -50     111    24/11/2012   N      744.75   24/12/2012 19:09:00 PM
00000   SA      2012  7      1       00000  01     123   12      P     -70     102    30/11/2012   N      674.75   30/12/2012 09:09:00 AM
Above result was wrong+ on TRANX.PSBKBAL+ started from *794.75 - 674.75*, it show something like the cursor has took the wrong result (wrong TRANX.TRNAMT) to update TRANX.PSBKBAL, it does not follow the WHERE clause in the cursor at TRANX.SEQNO = 5 only, but for the rest it return to the correct amount.

Noticed that it took the TRANX.TRNAMT = -5.25 from TRANX.CRLINE = 'CA'.

The correct results should be:
TRNBRN  CRLINE  YR    SEQNO  SEQCNT  BRNCD  MODCD  ACNO  CHKDGT  POST  TRNAMT  TRNCD  TRNDT        OVRRD  PSBKBAL  TIME
------  ------  ----  -----  ------  -----  -----  ----  ------  ----  ------  -----  ----------  -----  -------  ---------------------
00000   SA      2012  1      1       00000  01     123   12      P     -50     111    15/11/2012   N      950      15/12/2012 10:10:00 AM
00000   SA      2012  2      1       00000  01     123   12      P     -50     111    20/11/2012   N      900      20/12/2012 09:09:00 AM
00000   SA      2012  3      1       00000  01     123   12      P     -50     111    21/11/2012   N      850      21/12/2012 11:21:00 AM
00000   SA      2012  4      1       00000  01     123   12      P     -50     111    22/11/2012   N      800      22/12/2012 07:09:00 AM
00000   SA      2012  5      1       00000  01     123   12      P     -50     111    23/11/2012   N      750      23/12/2012 13:09:00 PM
00001   CA      2012  1      1       00001  02     456   23      S     -5.25   101    20/11/2012   N      0        20/12/2012 09:09:00 AM
00000   SA      2012  6      1       00000  01     123   12      P     -50     111    24/11/2012   N      700      24/12/2012 19:09:00 PM
00000   SA      2012  7      1       00000  01     123   12      P     -70     102    30/11/2012   N      630      30/12/2012 09:09:00 AM
My Questions:

1. Why and How this situation could occur? What make me so confuse is the cursor took the wrong result for 1 record only, but others were return to correct result again.

2. Is this a bug? Or there have something wrong with my codes that can make the cursor not consistent?

3. Or this situation could happen because of global variable?

Any explanation or helps would be appreciated.


Thanks & Regards,

Luthfi

Edited by: Luthfi on Mar 14, 2012 10:48 AM

Edited by: Luthfi on Mar 14, 2012 11:09 AM
  • 1. Re: Cursor returning wrong result
    rp0428 Guru
    Currently Being Moderated
    >
         IF b_foflg = TRUE THEN
              SELECT psbkbal INTO safo_init.n_psbkbal
              FROM paper
              WHERE crline = v_crline AND brncd = v_brncd AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt;
    >
    1. You are selecting into a variable but the query could return no rows. What happens then? You aren't checking for that.

    2. When you only want to process when a condition is true
         ELSE
              Message('Problem');
         END IF;
    Put the short code first and then return so developers don't have to search for the ELSE. Then you don't need to put the bulk of the code in an IF block.
         IF b_foflg != TRUE THEN
              Message('Problem');
                                    RETURN;
         END IF;
  • 2. Re: Cursor returning wrong result
    Luthfi Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply.
    1. You are selecting into a variable but the query could return no rows. What happens then? You aren't checking for that.
    The query has return 1 row if we refer to PAPER+ table. Assume that i run the procedure only for existing record in PAPER+.
    2. When you only want to process when a condition is true
    Actually, for ELSE+ condition have another process which it calls another procedure. I just put the message for the example because i run the procedure using TRUE+:
    I_Psbk_Cont(TRUE, 'SA', '00000', '01', 123, 12, '102', '30/11/2012')
    It return correctly for the first 4 rows, but 5th row it is wrongly updated.


    Thanks & Regards,

    Luthfi
  • 3. Re: Cursor returning wrong result
    Paul Horth Expert
    Currently Being Moderated
    Please post EXACT table and procedure definitions + insert statements that allow us to put
    data in.

    I say EXACT as

    SELECT psbkbal INTO safo_init.n_psbkbal
              FROM paper
              WHERE crline = v_crline AND brncd = v_brncd AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt;

    cannot possibly work as there is no crline in your paper table.
  • 4. Re: Cursor returning wrong result
    rp0428 Guru
    Currently Being Moderated
    OP just updated his original post to include this.
    CRLINE      VARCHAR2(2 BYTE) NOT NULL,
    Maybe they think we won't notice it wasn't there to begin with. So they probably won't retest anything now that it is present.
  • 5. Re: Cursor returning wrong result
    Luthfi Newbie
    Currently Being Moderated
    Hi,

    Sorry, I've updated the post. Actually in my table have that column but I forgot to put it in my post before. I also provides the insert statement as per request. Thanks for giving alert to me. Kindly help me why and how this issue happened.

    Thanks & Regards,

    Luthfi
  • 6. Re: Cursor returning wrong result
    Luthfi Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply.
    Maybe they think we won't notice it wasn't there to begin with. So they probably won't retest anything now that it is present.
    Sorry, that is my mistake.
    It return correctly for the first 4 rows, but 5th row it is wrongly updated.
    This is what make me confused. The cursor was returned 7 rows. For the first 4 rows it correctly updated according the correct amount, but at 5th row it take wrong amount *(-5.25)* to update the TRANX.PSBKBAL. Then at 6th row it return to correct amount again. Why this situation could occur?

    Any explanation or helps would be appreciated.

    Thanks & Regards,

    Luthfi
  • 7. Re: Cursor returning wrong result
    Paul Horth Expert
    Currently Being Moderated
    Please post a correct procedure as well please: the one you posted doesn't compile (for example: and a.trncd '455') also what is safo_init.n_psbkbal, is it an global in another package or another mistake?

    Alos what is n_bal? It doesn't seem to be defined.

    Also, insert statements for tranx do not work: can you correct and test please?
  • 8. Re: Cursor returning wrong result
    Marwim Expert
    Currently Being Moderated
    and a.trncd '455'
    should be
    and a.trncd != '455'
    The forum software does not show "tags" like &lt; &gt;

    Regards
    Marcus

    Edited by: Marwim on 14.03.2012 10:17
    See [url https://wikis.oracle.com/display/Forums/Forums+FAQ#ForumsFAQ-Cautions] FAQ - Cautions
  • 9. Re: Cursor returning wrong result
    Paul Horth Expert
    Currently Being Moderated
    Thanks, didn't realise that - it's a bit of a nuisance.

    Still don't know what n_bal is though.
  • 10. Re: Cursor returning wrong result
    Luthfi Newbie
    Currently Being Moderated
    Hi Paul Horth,

    Thanks for reply.
    what is safo_init.n_psbkbal, is it an global in another package or another mistake?
    It is global.
    what is n_bal? It doesn't seem to be defined.
    Im sorry, this is another mistake.
    n_bal NUMBER(16,2) := 0;
    Insert statement for tranx, I missed the right parenthesis at the last. Oh, too many mistakes in my post, my bad.
    INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                      TRNDT, OVRRD, PSBKBAL, TIME)
                   VALUES('00000', 'SA', 2012, 1, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                          TO_DATE('15/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('15/12/2012 10:10:00','DD/MM/YYYY HH24:MI:SS'));
    Any explanation or helps would be appreciated.

    Thanks & Regards,

    Luthfi
  • 11. Re: Cursor returning wrong result
    Paul Horth Expert
    Currently Being Moderated
    I'm sorry, still can't compile:

    What's v_scode := prt_rec.scode ?

    What is prt_rec.trnbrn?

    Until you post something we can compile, we can't really help.
  • 12. Re: Cursor returning wrong result
    Luthfi Newbie
    Currently Being Moderated
    Hi,

    This is the latest procedure, I've corrected and compiled it and it should not have the problem. I am sorry to have trouble you, thank you for being patient with me.
    PROCEDURE I_Psbk_Cont(B_FOFLG IN BOOLEAN, V_CRLINE IN VARCHAR2, V_BRNCD IN VARCHAR2, V_MODCD IN VARCHAR2,
    N_ACNO IN NUMBER, N_CHKDGT IN NUMBER, V_TRNCD IN VARCHAR2, D_SYSDATE IN VARCHAR2) IS
    
         CURSOR psbcon IS
         SELECT trndt, a.trncd, trnamt, psbkbal, seqno, seqcnt, trnbrn 
         FROM tranx a, code b
         WHERE a.crline = v_crline
              AND brncd = v_brncd
              AND modcd = v_modcd
              AND acno = n_acno
              AND chkdgt = n_chkdgt
              AND ((a.crline in ('SA','IS') and post in ('U', 'S') OR (post = 'C' and psbkbal <> 0))
              or (a.crline in ('CA','IC') and post<>'C'))
              AND ovrrd IN ('N','A')
              AND a.crline = b.crline
              AND a.trncd = b.trncd
              and a.trncd != '455'
         ORDER BY trndt,TIME, seqno, seqcnt;
    
         CURSOR psbcon3 IS     
         SELECT trndt, a.trncd, trnamt, psbkbal, seqno, seqcnt, trnbrn 
         FROM tranx a, code b
         WHERE a.crline = v_crline
              AND brncd = v_brncd
              AND modcd = v_modcd
              AND acno = n_acno
              AND ((a.crline in ('SA','IS') and post in ('U', 'S')) or (a.crline in ('CA','IC') and post = 'S'))
              AND ovrrd IN ('N','A')
              AND a.crline = b.crline
              AND a.trncd = b.trncd
         ORDER BY trndt, TIME, seqno, seqcnt;
    
         prt_rec psbcon%ROWTYPE;
    
         n_bfbal NUMBER(16,2) := 0;
         
         n_bal NUMBER(16,2) := 0;
    
    BEGIN
         IF b_foflg = TRUE THEN
              SELECT psbkbal INTO safo_init.n_psbkbal
              FROM paper
              WHERE crline = v_crline AND brncd = v_brncd AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt;
              
              IF v_trncd IN ('411','413') THEN
                   n_bfbal := safo_init.n_psbkbal;
              END IF;
    
              n_bal := safo_init.n_psbkbal;
    
              IF v_trncd IN ('411','412','413') THEN
                   OPEN psbcon3;
                   FETCH psbcon3 INTO prt_rec;
    
                   IF psbcon3%NOTFOUND THEN
                        CLOSE psbcon3;
                        RETURN;
                   END IF;
              ELSE
                   OPEN psbcon;
                   FETCH psbcon INTO prt_rec;
    
                   IF psbcon%NOTFOUND THEN
                        Message('No');
                        CLOSE psbcon;
                        RETURN;
                   END IF;
              END IF;
    
              IF v_trncd IN ('411','412','413') THEN
                   CLOSE psbcon3;
                   OPEN psbcon3;
              ELSE
                   CLOSE psbcon;
                   OPEN psbcon;
              END IF;
    
              LOOP
                   IF v_trncd IN ('411','412','413') THEN
                        FETCH psbcon3 INTO prt_rec;
                   ELSE
                        FETCH psbcon INTO prt_rec;
                   END IF;
                   --v_scode := prt_rec.scode;
    
                   IF v_trncd IN ('411','412','413') THEN
                        EXIT WHEN psbcon3%NOTFOUND;
                   ELSE
                        EXIT WHEN psbcon%NOTFOUND;
                   END IF;
                   IF prt_rec.trncd IN ('411','413') THEN
                        safo_init.n_psbkbal := n_bal;
                   END IF;
    
                   safo_init.n_psbkbal := safo_init.n_psbkbal + prt_rec.trnamt;
    
                   if v_crline in ('CA','IC') then  
                        UPDATE tranx SET psbkbal = safo_init.n_psbkbal                        
                        WHERE seqno = prt_rec.seqno AND seqcnt = prt_rec.seqcnt AND brncd = v_brncd
                             AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt
                             AND trnbrn = prt_rec.trnbrn AND post <> 'C';
                   else 
                        UPDATE tranx SET post = 'P', psbkbal = safo_init.n_psbkbal                            
                        WHERE seqno = prt_rec.seqno AND seqcnt = prt_rec.seqcnt AND brncd = v_brncd
                             AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt
                             AND trnbrn = prt_rec.trnbrn AND post <> 'C';
                   end if;
    
              END LOOP;  -- psbcon loop
    
              IF v_trncd IN ('411','412','413') THEN
                   CLOSE psbcon3;
              ELSE
                   CLOSE psbcon;
              END IF;
    
              COMMIT;
    
         ELSE
              Message('Problem');
         END IF;
    END;
    Any explanation or helps would be appreciated.

    Thanks & Regards,

    Luthfi
  • 13. Re: Cursor returning wrong result
    Paul Horth Expert
    Currently Being Moderated
    I'm getting the correct results:
    TRNBRN     CRLINE     YR     SEQNO     SEQCNT     BRNCD     MODCD     ACNO     CHKDGT     POST     TRNAMT     TRNCD     TRNDT     OVRRD     PSBKBAL     TIME
    00000     SA     2012     1     1     00000     01     123     12     P     -50.00     111     15-Nov-12     N     950.00     15-Dec-12 10:10:00 AM
    00000     SA     2012     2     1     00000     01     123     12     P     -50.00     111     20-Nov-12     N     900.00     20-Dec-12 9:09:00 AM
    00000     SA     2012     3     1     00000     01     123     12     P     -50.00     111     21-Nov-12     N     850.00     21-Dec-12 11:21:00 AM
    00000     SA     2012     4     1     00000     01     123     12     P     -50.00     111     22-Nov-12     N     800.00     22-Dec-12 7:09:00 AM
    00000     SA     2012     5     1     00000     01     123     12     P     -50.00     111     23-Nov-12     N     750.00     23-Dec-12 1:09:00 PM
    00001     CA     2012     1     1     00001     02     456     23     S     -5.25     101     20-Nov-12     N     0.00     20-Dec-12 9:09:00 AM
    00000     SA     2012     6     1     00000     01     123     12     P     -50.00     111     24-Nov-12     N     700.00     24-Dec-12 7:09:00 PM
    00000     SA     2012     7     1     00000     01     123     12     P     -70.00     102     30-Nov-12     N     630.00     30-Dec-12 9:09:00 AM
  • 14. Re: Cursor returning wrong result
    Luthfi Newbie
    Currently Being Moderated
    Hi Paul Horth,

    Thanks for your helps.

    Ok, now you got the correct results. Actually, I also got the correct results. But, what happen if many users or programs are using the
    Procedure I_Psbkbal_Cont()
    at the same time within more than thousand records? And assume that more than 1 record have the TRANX.TRNAMT = -5.25 with different TRANX.BRNCD, TRANX.MODCD, TRANX.ACNO, & TRANX.CHKDGT.
    Is it possible for the error occur? So now if it possible, back to my questions,
    My Questions:
    1. Why and How this situation could occur? What make me so confuse is the cursor took the wrong result for 1 record only, but others were return to correct result again.

    2. Is this a bug? Or there have something wrong with my codes that can make the cursor not consistent?

    3. Or this situation could happen because of global variable?>

    Any explanation or helps would be appreciated.

    Thanks & Regards,

    Luthfi
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points