5 Replies Latest reply: Jul 3, 2010 2:31 AM by 650575 RSS

    to update table data

    650575
      create or replace procedure SP_MIS_LEDGER_ON_DEMAND_V2
      as

      var_date1 date;

      VAR_STARTDATETIME DATE;
      VAR_ENDDATETIME DATE;
      -- VAR_EXECUTE_FOR_DATE DATE;
      -- VAR_STATEMENT VARCHAR2(4000);
      VAR_ELAPSEDTIME VARCHAR2(50);
      VAR_INTRIMSTATUSID NUMBER;

      CURSOR c1 IS
      select DISTINCT ta.accountid,day PROCESSDATE,(NVL(payment,0))PAYMENT,0 TOTALDUE,0 CURBILL, NVL(srf,0)SRF,NVL(sbpnt,0)sbpnt,NVL(srv,0)SRV,NVL(sbf,0)SBF,NVL(SBV,0)SBV,NVL(EF,0)EF,NVL(EV,0)EV,NVL(TSRV,0)TSRV,NVL(tsub,0)TSUB,NVL(teqe,0)TEQE,NVL(DT,0)DT,NVL(A.dep,0)RDEP,NVL(B.DEP,0)PDEP,NVL(pnt,0)PNT,NVL(eqp,0)EQP,NVL(dtr,0)DTR,NVL(drf,0)DRF,NVL(unadj,0)UNADJ from
      (select DISTINCT day ,accountid
      from
      syntblmaccount, tblmtime where yyyy=2010)ta,
      (SELECT accountid,
      SUM(srfee)srf,
      SUM(srvat)srv,
      SUM(subfee)sbf,
      SUM(subvat)sbv,
      SUM(eqefee)ef,
      SUM(eqevat)ev,
      SUM(ttlsrv)tsrv,
      SUM(ttlsub)tsub,
      SUM(ttleqe)teqe,
      SUM(dep)dep,
      SUM(dt)dt,trunc(FROMDATE)FROMDATE
      FROM VWDT_V6
      group by accountid, trunc(FROMDATE)
      )a,
      (SELECT accountid,
      SUM(pnt)pnt,
      SUM(subpnt)sbpnt,
      SUM(eqpnt)eqp,
      SUM(dep)dep,
      SUM(DEPTRANSFER)dtr,
      SUM(DEPREFUNDED)drf,
      SUM(unadj)unadj,trunc(paymentdate)paymentdate
      FROM vwkt_v4
      GROUP BY accountid,trunc(paymentdate)
      )b,
      (SELECT ACCOUNTID accountid,TRUNC(createdate)CREATEDATE, SUM(totalamount)PAYMENT
      from syntbltcreditdocument
      where CREDITDOCUMENTTYPEID IN ('CDT01','CDT04')
      group by accountid,TRUNC(createdate))credit
      where ta.accountid=a.accountid(+)
      and ta.accountid=b.accountid(+)
      and ta.accountid=credit.accountid(+)
      and ta.day=a.FROMDATE(+)
      and ta.day=credit.createdate(+)
      and ta.day=b.paymentdate(+)
      and ta.day =to_date('01-MAY-2010','DD-MON-YYYY');

      BEGIN

      SELECT MAX(PROCESSDATE) INTO VAR_DATE1 FROM MIS_LEDGER_DETAIL_TEST;

      SELECT SYSDATE INTO VAR_STARTDATETIME FROM DUAL;

      SELECT SEQ_PRC_STATUS.NEXTVAL INTO VAR_INTRIMSTATUSID FROM DUAL;

      FOR c1_rec IN c1
      LOOP
      EXIT WHEN c1%NOTFOUND;

      UPDATE MIS_LEDGER_DETAIL_tEST A
      SET A.PAYMENT=c1_rec.payment,
      A.TOTALDUE=c1_rec.TOTALDUE,
      A.CURBILL=c1_rec.CURBILL,
      A.SRF=c1_rec.srf,
      A.SBPNT=c1_rec.sbpnt,
      A.SRV=c1_rec.srv,
      A.SBF=c1_rec.sbf,
      A.SBV=c1_rec.sbv,
      A.EF=c1_rec.ef,
      A.EV=c1_rec.ev,
      A.TSRV=c1_rec.tsrv,
      A.TSUB=c1_rec.tsub,
      A.TEQE=c1_rec.teqe,
      A.DT=c1_rec.dt,
      A.PDEP=c1_rec.Pdep,
      A.RDEP=C1_REC.RDEP,
      A.PNT=c1_rec.pnt,
      A.EQP=c1_rec.eqp,
      A.DTR=c1_rec.dtr,
      A.DRF=c1_rec.drf,
      A.UNADJ=c1_rec.unadj
      where A.accountid=c1_rec.accountid
      and A.processdate=C1_REC.processdate
      and a.processdate =to_date('01-MAY-2010','DD-MON-YYYY');

      END LOOP ;

      commit;
      SELECT SYSDATE INTO VAR_ENDDATETIME FROM DUAL;


      SELECT CAST(VAR_ENDDATETIME AS TIMESTAMP) -
      CAST(VAR_STARTDATETIME AS TIMESTAMP) INTO VAR_ELAPSEDTIME
      FROM DUAL;

      INSERT INTO LedgerStatusSummary (StatusID, ProcedureName, STARTDATETIME, ENDDATETIME, LastExecutionDate,NextExecutionDate,LastModifiedDate,TIMETAKEN,Procedurestatus) VALUES
      (VAR_INTRIMSTATUSID,'SP_MIS_LEDGER_ON_DEMAND',VAR_STARTDATETIME,VAR_ENDDATETIME,TRUNC(VAR_DATE1),TRUNC(VAR_DATE1)+1,VAR_STARTDATETIME, VAR_ELAPSEDTIME,'MENUAL');

      COMMIT;

      EXCEPTION
      WHEN OTHERS
      THEN DBMS_OUTPUT.PUT_LINE('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);


      END SP_MIS_LEDGER_ON_DEMAND_V2;


      i have 9830 data in MIS_LEDGER_DETAIL_tEST table ... i am updating table data but it is taking more time to update for 01-may-2010 it is not completing execution in 15 min so i abort it...



      how to write update query....?? please guide me...

      Thanks in advance

      exec SP_MIS_LEDGER_ON_DEMAND_V2