1 Reply Latest reply: Jan 13, 2013 11:30 AM by user568781 RSS

    how to view records more 60000 from package???

    user2338758
      Dear all,

      I have query my records more than 5000 rows and wanna add more accounting entries.
      e.g:
      System posted a transaction as below:
      debit: cash 5000
      credit: liab 5000

      But accountant need is
      debit: cash 5000
      credit: liab 2000
      credit: OD 3000

      But after query from package i can see my record only 1333 rows. Can you help me please why the result of records is less than the original records?


      CREATE OR REPLACE PACKAGE BODY VBL_GL001 IS
      --FIND FCY ACCOUNT BALANCE
      FUNCTION FN_FIND_AC_BAL(P_BRN IN VARCHAR2,P_ACC IN VARCHAR2,P_CCY IN VARCHAR2,P_ENTRY_SR IN NUMBER) RETURN NUMBER IS
      NAT_BAL NUMBER :=NULL;
      BEGIN

      SELECT DECODE(E.AC_CCY,'USD',NVL(SUM(DECODE(E.DRCR_IND,'C',NVL(E.LCY_AMOUNT,0))) - SUM(DECODE(E.DRCR_IND,'D',NVL(E.LCY_AMOUNT,0))),0)
      ,NVL(SUM(DECODE(E.DRCR_IND,'C',NVL(E.FCY_AMOUNT,0))) - SUM(DECODE(E.DRCR_IND,'D',NVL(E.FCY_AMOUNT,0))),0) ) BAL
      INTO NAT_BAL FROM ACVW_ALL_AC_ENTRIES E WHERE E.AC_NO = P_ACC AND E.AC_CCY = P_CCY AND E.AC_BRANCH = P_BRN
      AND E.AC_ENTRY_SR_NO < P_ENTRY_SR GROUP BY E.AC_CCY,E.AC_BRANCH;

      RETURN (NAT_BAL);
      END FN_FIND_AC_BAL;

      --FIND LCY ACCOUNT BALANCE
      FUNCTION FN_FIND_AC_LCY_BAL(P_BRN IN VARCHAR2,P_ACC IN VARCHAR2,P_CCY IN VARCHAR2,P_ENTRY_SR IN NUMBER) RETURN NUMBER IS
      LCY_BAL NUMBER :=NULL;
      BEGIN

      SELECT NVL(SUM(DECODE(E.DRCR_IND,'C',NVL(E.LCY_AMOUNT,0))) - SUM(DECODE(E.DRCR_IND,'D',NVL(E.LCY_AMOUNT,0))),0) BAL
      INTO LCY_BAL FROM ACVW_ALL_AC_ENTRIES E WHERE E.AC_NO = P_ACC AND E.AC_CCY = P_CCY AND E.AC_BRANCH = P_BRN
      AND E.AC_ENTRY_SR_NO < P_ENTRY_SR GROUP BY E.AC_CCY,E.AC_BRANCH;

      RETURN (LCY_BAL);
      END FN_FIND_AC_LCY_BAL;

      --VIEW
      FUNCTION FN_REFRESH_DATA(P_BRN IN VARCHAR2,P_CCY IN VARCHAR2,P_SDATE IN DATE,P_EDATE IN DATE) RETURN DS_ACVW PIPELINED IS
      TYPE r_cursor IS REF CURSOR; ACVW_TRN r_cursor;
      REC_ACVW DS_ACVW := DS_ACVW();
      C NUMBER := 1;
      XDR_GL VARCHAR2(9); XCR_GL VARCHAR2(9); XTRN_REF_NO VARCHAR2(16); XENTRY_SR_NO NUMBER; XEVENT VARCHAR2(4);
      XBRN VARCHAR2(3); XAC_NO VARCHAR2(20); XCCY VARCHAR2(3); XDRCR VARCHAR2(1); XTRN_CODE VARCHAR2(3);
      X_RATE NUMBER(24,12); XFCY_AMT NUMBER(22,3); XLCY_AMT NUMBER(22,3); XTRN_DT DATE; XVAL_DT DATE;
      XINIT_DT DATE; XAMT_TAG VARCHAR2(35); XREL_ACC VARCHAR2(20); XREL_CUST VARCHAR2(9); XMODULES VARCHAR2(2);
      XCUST_GL VARCHAR2(1); XFIN VARCHAR2(9); XPERIOD VARCHAR2(3); XBATCH_NO VARCHAR2(4); XUSER_ID VARCHAR2(12);
      XAUTH_ID VARCHAR2(12); XREF VARCHAR2(35);

      AC_BAL NUMBER(22,3) := NULL; AC_LCY_BAL NUMBER(22,3) := NULL; NAT_TRN_AMT NUMBER(22,3) := NULL;
      REC_CNT NUMBER;

      BEGIN
      OPEN ACVW_TRN FOR
      SELECT (SELECT DISTINCT DR_GL FROM STTM_CUST_ACCOUNT WHERE CUST_AC_NO = E.AC_NO) DR_GL
      ,(SELECT DISTINCT CR_GL FROM STTM_CUST_ACCOUNT WHERE CUST_AC_NO = E.AC_NO) CR_GL
      ,E.TRN_REF_NO,E.AC_ENTRY_SR_NO,E.EVENT,E.AC_BRANCH,E.AC_NO,E.AC_CCY,E.DRCR_IND
      ,E.TRN_CODE,E.EXCH_RATE,E.FCY_AMOUNT,E.LCY_AMOUNT,E.TRN_DT,E.VALUE_DT,E.TXN_INIT_DATE
      ,E.AMOUNT_TAG,E.RELATED_ACCOUNT,E.RELATED_CUSTOMER,E.MODULE,E.CUST_GL,E.FINANCIAL_CYCLE
      ,E.PERIOD_CODE,E.BATCH_NO,E.USER_ID,E.AUTH_ID,E.EXTERNAL_REF_NO
      FROM ACVW_ALL_AC_ENTRIES E
      WHERE E.TRN_DT BETWEEN P_SDATE AND P_EDATE AND E.AC_CCY = NVL(P_CCY,E.AC_CCY)
      AND E.AC_BRANCH = NVL(P_BRN,E.AC_BRANCH) AND E.CUST_GL = 'A' AND (E.EVENT != 'REVL' AND E.TRN_CODE != 'RGL');

      -- COUNT ALL TRANSACTION RECORDS
      SELECT COUNT(*) INTO REC_CNT
      FROM ACVW_ALL_AC_ENTRIES E
      WHERE E.TRN_DT BETWEEN P_SDATE AND P_EDATE AND E.AC_CCY = NVL(P_CCY,E.AC_CCY)
      AND E.AC_BRANCH = NVL(P_BRN,E.AC_BRANCH) AND E.CUST_GL = 'A' AND (E.EVENT != 'REVL' AND E.TRN_CODE != 'RGL');
      DBMS_OUTPUT.put_line ('CNT: '||REC_CNT); --rows is 50016
      LOOP
      FETCH ACVW_TRN INTO XDR_GL,XCR_GL,XTRN_REF_NO,XENTRY_SR_NO,XEVENT,XBRN,XAC_NO,XCCY,XDRCR,XTRN_CODE,X_RATE,XFCY_AMT,XLCY_AMT
      ,XTRN_DT,XVAL_DT,XINIT_DT,XAMT_TAG,XREL_ACC,XREL_CUST,XMODULES,XCUST_GL,XFIN,XPERIOD,XBATCH_NO,XUSER_ID,XAUTH_ID,XREF;
      REC_ACVW.EXTEND;

      AC_BAL := FN_FIND_AC_BAL(XBRN,XAC_NO,XCCY,XENTRY_SR_NO);
      AC_LCY_BAL := FN_FIND_AC_LCY_BAL(XBRN,XAC_NO,XCCY,XENTRY_SR_NO);
      IF XCCY = 'USD' THEN
      NAT_TRN_AMT := NVL(XLCY_AMT,0);
      ELSE
      NAT_TRN_AMT := NVL(XFCY_AMT,0);
      END IF;

      BEGIN
      IF AC_BAL >= 0 THEN -- BALANCE BEFORE TRANSACTION IS NOT MINUS
      IF XDRCR = 'C' THEN --CREDIT TRANSACTION
      REC_ACVW(C).GL_CODE := XCR_GL; REC_ACVW(C).TRN_REF_NO := XTRN_REF_NO; REC_ACVW(C).ENTRY_SR_NO := XENTRY_SR_NO;
      REC_ACVW(C).EVENT := XEVENT; REC_ACVW(C).BRN := XBRN; REC_ACVW(C).AC_NO := XAC_NO; REC_ACVW(C).CCY := XCCY;
      REC_ACVW(C).DRCR := XDRCR; REC_ACVW(C).TRN_CODE := XTRN_CODE; REC_ACVW(C).RATE := X_RATE;
      REC_ACVW(C).FCY_AMT := XFCY_AMT; REC_ACVW(C).LCY_AMT := XLCY_AMT; REC_ACVW(C).TRN_DT := XTRN_DT;
      REC_ACVW(C).VAL_DT := XVAL_DT; REC_ACVW(C).INIT_DT := XINIT_DT; REC_ACVW(C).AMT_TAG := XAMT_TAG;
      REC_ACVW(C).REL_ACC := XREL_ACC; REC_ACVW(C).REL_CUST := XREL_CUST; REC_ACVW(C).MODULES := XMODULES;
      REC_ACVW(C).CUST_GL := XCUST_GL; REC_ACVW(C).FIN := XFIN; REC_ACVW(C).PERIOD := XPERIOD; REC_ACVW(C).BATCH_NO := XBATCH_NO;
      REC_ACVW(C).USER_ID := XUSER_ID; REC_ACVW(C).AUTH_ID := XAUTH_ID; REC_ACVW(C).XREF := XREF;
      ELSE --DEBIT TRANSACTION
      IF AC_BAL >= NAT_TRN_AMT THEN -- IF ( BALANCE - TRANSACTION AMT ) STILL LARGER THAN 0
      REC_ACVW(C).GL_CODE := XCR_GL; REC_ACVW(C).TRN_REF_NO := XTRN_REF_NO; REC_ACVW(C).ENTRY_SR_NO := XENTRY_SR_NO;
      REC_ACVW(C).EVENT := XEVENT; REC_ACVW(C).BRN := XBRN; REC_ACVW(C).AC_NO := XAC_NO; REC_ACVW(C).CCY := XCCY;
      REC_ACVW(C).DRCR := XDRCR; REC_ACVW(C).TRN_CODE := XTRN_CODE; REC_ACVW(C).RATE := X_RATE;
      REC_ACVW(C).FCY_AMT := XFCY_AMT; REC_ACVW(C).LCY_AMT := XLCY_AMT; REC_ACVW(C).TRN_DT := XTRN_DT;
      REC_ACVW(C).VAL_DT := XVAL_DT; REC_ACVW(C).INIT_DT := XINIT_DT; REC_ACVW(C).AMT_TAG := XAMT_TAG;
      REC_ACVW(C).REL_ACC := XREL_ACC; REC_ACVW(C).REL_CUST := XREL_CUST; REC_ACVW(C).MODULES := XMODULES;
      REC_ACVW(C).CUST_GL := XCUST_GL; REC_ACVW(C).FIN := XFIN; REC_ACVW(C).PERIOD := XPERIOD; REC_ACVW(C).BATCH_NO := XBATCH_NO;
      REC_ACVW(C).USER_ID := XUSER_ID; REC_ACVW(C).AUTH_ID := XAUTH_ID; REC_ACVW(C).XREF := XREF;

      ELSE -- IF ( BALANCE - TRANSACTION AMT ) IS LESS THAN 0
      REC_ACVW(C).GL_CODE := XCR_GL; REC_ACVW(C).TRN_REF_NO := XTRN_REF_NO; REC_ACVW(C).ENTRY_SR_NO := XENTRY_SR_NO;
      REC_ACVW(C).EVENT := XEVENT; REC_ACVW(C).BRN := XBRN; REC_ACVW(C).AC_NO := XAC_NO; REC_ACVW(C).CCY := XCCY;
      REC_ACVW(C).DRCR := XDRCR; REC_ACVW(C).TRN_CODE := XTRN_CODE; REC_ACVW(C).RATE := X_RATE;
      IF XCCY !='USD' THEN
      REC_ACVW(C).FCY_AMT := AC_BAL;
      ELSE
      REC_ACVW(C).FCY_AMT := NULL;
      END IF;
      REC_ACVW(C).LCY_AMT := AC_LCY_BAL; REC_ACVW(C).TRN_DT := XTRN_DT;
      REC_ACVW(C).VAL_DT := XVAL_DT; REC_ACVW(C).INIT_DT := XINIT_DT; REC_ACVW(C).AMT_TAG := XAMT_TAG;
      REC_ACVW(C).REL_ACC := XREL_ACC; REC_ACVW(C).REL_CUST := XREL_CUST; REC_ACVW(C).MODULES := XMODULES;
      REC_ACVW(C).CUST_GL := XCUST_GL; REC_ACVW(C).FIN := XFIN; REC_ACVW(C).PERIOD := XPERIOD; REC_ACVW(C).BATCH_NO := XBATCH_NO;
      REC_ACVW(C).USER_ID := XUSER_ID; REC_ACVW(C).AUTH_ID := XAUTH_ID; REC_ACVW(C).XREF := XREF;
      PIPE ROW (REC_ACVW(C));
      C := C + 1;
      REC_ACVW.EXTEND;

      REC_ACVW(C).GL_CODE := XDR_GL; REC_ACVW(C).TRN_REF_NO := XTRN_REF_NO; REC_ACVW(C).ENTRY_SR_NO := XENTRY_SR_NO;
      REC_ACVW(C).EVENT := XEVENT; REC_ACVW(C).BRN := XBRN; REC_ACVW(C).AC_NO := XAC_NO; REC_ACVW(C).CCY := XCCY;
      REC_ACVW(C).DRCR := XDRCR; REC_ACVW(C).TRN_CODE := XTRN_CODE; REC_ACVW(C).RATE := X_RATE; REC_ACVW(C).FCY_AMT := (XFCY_AMT - AC_BAL);
      REC_ACVW(C).LCY_AMT := (XLCY_AMT - AC_LCY_BAL); REC_ACVW(C).TRN_DT := XTRN_DT; REC_ACVW(C).VAL_DT := XVAL_DT;
      REC_ACVW(C).INIT_DT := XINIT_DT; REC_ACVW(C).AMT_TAG := XAMT_TAG; REC_ACVW(C).REL_ACC := XREL_ACC; REC_ACVW(C).REL_CUST := XREL_CUST;
      REC_ACVW(C).MODULES := XMODULES; REC_ACVW(C).CUST_GL := XCUST_GL; REC_ACVW(C).FIN := XFIN; REC_ACVW(C).PERIOD := XPERIOD;
      REC_ACVW(C).BATCH_NO := XBATCH_NO; REC_ACVW(C).USER_ID := XUSER_ID; REC_ACVW(C).AUTH_ID := XAUTH_ID; REC_ACVW(C).XREF := XREF;
      END IF;
      END IF;

      ELSE -- BALANCE BEFORE TRANSACTION IS MINUS
      IF XDRCR = 'C' THEN --CREDIT TRANSACTION
      IF (NAT_TRN_AMT + AC_BAL) > 0 THEN -- IF TRANSACTION AMT IS LARGER THAN UTILIZED AMT
      REC_ACVW(C).GL_CODE := XDR_GL; REC_ACVW(C).TRN_REF_NO := XTRN_REF_NO; REC_ACVW(C).ENTRY_SR_NO := XENTRY_SR_NO;
      REC_ACVW(C).EVENT := XEVENT; REC_ACVW(C).BRN := XBRN; REC_ACVW(C).AC_NO := XAC_NO; REC_ACVW(C).CCY := XCCY;
      REC_ACVW(C).DRCR := XDRCR; REC_ACVW(C).TRN_CODE := XTRN_CODE; REC_ACVW(C).RATE := X_RATE;
      IF XCCY != 'USD' THEN
      REC_ACVW(C).FCY_AMT := (ABS(AC_BAL));
      ELSE
      REC_ACVW(C).FCY_AMT := NULL;
      END IF;
      REC_ACVW(C).LCY_AMT := (ABS(AC_LCY_BAL)); REC_ACVW(C).TRN_DT := XTRN_DT;
      REC_ACVW(C).VAL_DT := XVAL_DT; REC_ACVW(C).INIT_DT := XINIT_DT; REC_ACVW(C).AMT_TAG := XAMT_TAG;
      REC_ACVW(C).REL_ACC := XREL_ACC; REC_ACVW(C).REL_CUST := XREL_CUST; REC_ACVW(C).MODULES := XMODULES;
      REC_ACVW(C).CUST_GL := XCUST_GL; REC_ACVW(C).FIN := XFIN; REC_ACVW(C).PERIOD := XPERIOD; REC_ACVW(C).BATCH_NO := XBATCH_NO;
      REC_ACVW(C).USER_ID := XUSER_ID; REC_ACVW(C).AUTH_ID := XAUTH_ID; REC_ACVW(C).XREF := XREF;
      PIPE ROW (REC_ACVW(C));
      C := C + 1;
      REC_ACVW.EXTEND;
      REC_ACVW(C).GL_CODE := XCR_GL; REC_ACVW(C).TRN_REF_NO := XTRN_REF_NO; REC_ACVW(C).ENTRY_SR_NO := XENTRY_SR_NO;
      REC_ACVW(C).EVENT := XEVENT; REC_ACVW(C).BRN := XBRN; REC_ACVW(C).AC_NO := XAC_NO; REC_ACVW(C).CCY := XCCY;
      REC_ACVW(C).DRCR := XDRCR; REC_ACVW(C).TRN_CODE := XTRN_CODE; REC_ACVW(C).RATE := X_RATE;
      REC_ACVW(C).FCY_AMT := (XFCY_AMT + AC_BAL); REC_ACVW(C).LCY_AMT := (XLCY_AMT + AC_LCY_BAL); REC_ACVW(C).TRN_DT := XTRN_DT;
      REC_ACVW(C).VAL_DT := XVAL_DT; REC_ACVW(C).INIT_DT := XINIT_DT; REC_ACVW(C).AMT_TAG := XAMT_TAG;
      REC_ACVW(C).REL_ACC := XREL_ACC; REC_ACVW(C).REL_CUST := XREL_CUST; REC_ACVW(C).MODULES := XMODULES;
      REC_ACVW(C).CUST_GL := XCUST_GL; REC_ACVW(C).FIN := XFIN; REC_ACVW(C).PERIOD := XPERIOD; REC_ACVW(C).BATCH_NO := XBATCH_NO;
      REC_ACVW(C).USER_ID := XUSER_ID; REC_ACVW(C).AUTH_ID := XAUTH_ID; REC_ACVW(C).XREF := XREF;
      ELSE -- TRANSACTION AMT STILL NOT LARGER THAN UTILIZED AMT
      REC_ACVW(C).GL_CODE := XDR_GL; REC_ACVW(C).TRN_REF_NO := XTRN_REF_NO; REC_ACVW(C).ENTRY_SR_NO := XENTRY_SR_NO;
      REC_ACVW(C).EVENT := XEVENT; REC_ACVW(C).BRN := XBRN; REC_ACVW(C).AC_NO := XAC_NO; REC_ACVW(C).CCY := XCCY;
      REC_ACVW(C).DRCR := XDRCR; REC_ACVW(C).TRN_CODE := XTRN_CODE; REC_ACVW(C).RATE := X_RATE;
      REC_ACVW(C).FCY_AMT := XFCY_AMT; REC_ACVW(C).LCY_AMT := XLCY_AMT; REC_ACVW(C).TRN_DT := XTRN_DT;
      REC_ACVW(C).VAL_DT := XVAL_DT; REC_ACVW(C).INIT_DT := XINIT_DT; REC_ACVW(C).AMT_TAG := XAMT_TAG;
      REC_ACVW(C).REL_ACC := XREL_ACC; REC_ACVW(C).REL_CUST := XREL_CUST; REC_ACVW(C).MODULES := XMODULES;
      REC_ACVW(C).CUST_GL := XCUST_GL; REC_ACVW(C).FIN := XFIN; REC_ACVW(C).PERIOD := XPERIOD; REC_ACVW(C).BATCH_NO := XBATCH_NO;
      REC_ACVW(C).USER_ID := XUSER_ID; REC_ACVW(C).AUTH_ID := XAUTH_ID; REC_ACVW(C).XREF := XREF;
      END IF;
      ELSE
      REC_ACVW(C).GL_CODE := XDR_GL; REC_ACVW(C).TRN_REF_NO := XTRN_REF_NO; REC_ACVW(C).ENTRY_SR_NO := XENTRY_SR_NO;
      REC_ACVW(C).EVENT := XEVENT; REC_ACVW(C).BRN := XBRN; REC_ACVW(C).AC_NO := XAC_NO; REC_ACVW(C).CCY := XCCY;
      REC_ACVW(C).DRCR := XDRCR; REC_ACVW(C).TRN_CODE := XTRN_CODE; REC_ACVW(C).RATE := X_RATE;
      REC_ACVW(C).FCY_AMT := XFCY_AMT; REC_ACVW(C).LCY_AMT := XLCY_AMT; REC_ACVW(C).TRN_DT := XTRN_DT;
      REC_ACVW(C).VAL_DT := XVAL_DT; REC_ACVW(C).INIT_DT := XINIT_DT; REC_ACVW(C).AMT_TAG := XAMT_TAG;
      REC_ACVW(C).REL_ACC := XREL_ACC; REC_ACVW(C).REL_CUST := XREL_CUST; REC_ACVW(C).MODULES := XMODULES;
      REC_ACVW(C).CUST_GL := XCUST_GL; REC_ACVW(C).FIN := XFIN; REC_ACVW(C).PERIOD := XPERIOD; REC_ACVW(C).BATCH_NO := XBATCH_NO;
      REC_ACVW(C).USER_ID := XUSER_ID; REC_ACVW(C).AUTH_ID := XAUTH_ID; REC_ACVW(C).XREF := XREF;
      END IF;
      END IF;
      PIPE ROW (REC_ACVW(C));
      C := C + 1;
      END;
      END LOOP;
      CLOSE ACVW_TRN;

      RETURN;
      END FN_REFRESH_DATA;

      END VBL_GL001;