Solved

ORA-06530 and ORA-06512

Posted on 2001-06-12
2
2,377 Views
Last Modified: 2007-11-27
Hello,

I have a WinNT 4.0 sp6a iis 4.0 web server using mts and Microsoft ODBC driver for oracle to call a stored procedure.  Works fine when it is called once but if it is called multiple times in a row I get the following error:

6/12/01 11:56:26 AM GetDataByProcC: -2147467259 - [Microsoft][ODBC driver for Oracle][Oracle]ORA-06530: Reference to uninitialized composite
ORA-06512: at "DIMEA.DIMEPACK", line 373
ORA-06512: at line 1

here is the stored procedure (it's long)

create or replace package packRcv
as
      TYPE tDate is TABLE of Date
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar1 is TABLE of VARCHAR2(1)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar5 is TABLE of VARCHAR2(5)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar6 is TABLE of VARCHAR2(6)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar7 is TABLE of VARCHAR2(7)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar10 is TABLE of VARCHAR2(10)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar14 is TABLE of VARCHAR2(14)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar15 is TABLE of VARCHAR2(15)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar20 is TABLE of VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar25 is TABLE of VARCHAR2(25)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar30 is TABLE of VARCHAR2(30)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar35 is TABLE of VARCHAR2(35)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar40 is TABLE of VARCHAR2(40)
      INDEX BY BINARY_INTEGER;
      TYPE tVarchar512 is TABLE of VARCHAR2(512)
      INDEX BY BINARY_INTEGER;
      TYPE tNumber4 is TABLE of number(4,0)
      INDEX BY BINARY_INTEGER;
      TYPE tNumber12_2 is TABLE of number(12,2)
      INDEX BY BINARY_INTEGER;

      type tArcvInvID is table of arcvv.ArcvInvID%type
      index by binary_integer;
      type tBalance is table of arcvv.Balance%type
      index by binary_integer;
      type tBillAttns is table of client.BillAttns%type
      index by binary_integer;
      type tClientID is table of Client.ClientID%type
      index by binary_integer;      
      type tClientID4 is table of varchar2(4)
      index by binary_integer;      
      type tClntname is table of client.ClntName%type
      index by binary_integer;
      type tEmpNum is table of emp.empnum%type
      index by binary_integer;
      TYPE tInvseq is table of arcvv.InvSeq%type
      index by binary_integer;
      type tInvdate is table of varchar2(10)
      index by binary_integer;
      type tInvtotal is table of arcvv.invtotal%type
      index by binary_integer;
      type tMonth is table of varchar2(20)
      index by binary_integer;
      TYPE tOrgcode is TABLE of arcvv.orgcode%type
      INDEX BY BINARY_INTEGER;
      type tOrgName is table of uschcode.codedesc%type
      index by binary_integer;

      TYPE tChCd is TABLE of uschcode.CHARGECODE%type
      INDEX BY BINARY_INTEGER;
      TYPE tChCdName is TABLE of uschcode.CODEDESC%type
      INDEX BY BINARY_INTEGER;

      type tPaydate is table of payment.paydate%type
      index by binary_integer;
      type tPayment is table of payment.payment%type
      index by binary_integer;
      type tPaynote is table of payment.paynote%type
      index by binary_integer;
      type tPaytype is table of payment.paytype%type
      index by binary_integer;
      type tPeriod is table of varchar2(6)
      index by binary_integer;
      type tPrjid is table  of prjid.prjid%type
      index by binary_integer;
      type tRefinvseq is table of arcvd.refinvseq%type
      index by binary_integer;
      type tRcvType is table of arcvv.rcvtype%type
      index by binary_integer;
      type tRollupID is table of uschcode.rollupid%type
      index by binary_integer;
      type tStatus is table of varchar2(1)
      index by binary_integer;
      type tComments is table of colCmnt.comments%type
      index by binary_integer;
      type tComDate is table of colCmnt.timestamp%type
      index by binary_integer;
      type tComAuthor is table of colCmnt.userid%type
      index by binary_integer;
      type tComID is table of colCmnt.colcmntID%type
      index by binary_integer;
      
      procedure spGetBigClient(
            clientid            out      tClientID4
      );

      procedure spGetRcvInfoDtl(
            pInvseq            in      varchar2,
            arcvinvid      out      tArcvInvID,
            balance            out      tVarchar15,
            clientid      out      tClientID,
            invdate            out      tVarchar10,
            invseq            out      tInvSeq,
            invtotal      out      tVarchar15,
            mktr1            out      tEmpnum,
            mktr2            out      tEmpnum,
            OrgCode            out      tOrgCode,
            period            out      tPeriod,
            prjid            out      tPrjID,
            rcvtype            out      tRcvType,
            refInvseq      out      tRefInvseq,
            Status            out      tStatus
      );

      procedure spGetRollupID (
            pOrgCode      in varchar2,
            RollupID      out tRollupID
      );

      procedure spARCollIn
      (
            pNotes            in      varchar2,
            pUserID            in      number,
            pInvSeq            in       varchar2,
            result            out       tNumber4
      );

      procedure spARColl
      (
            pOrgCode            in      varchar2,
            pPrjID            in      number,
            pClntID            in      varchar2,
            pStartDate            in       varchar2,
            pEndDate            in      varchar2,
            pUnapplied            in       number,
            pPayDate            in      varchar2,
            pBaseCode            in      varchar2,
            invSeq            out      tinvSeq,
            oc                  out      tChCd,
            clntN                  out       tclntName,
            clntID            out      tclientID,
            bAttns            out      tbillAttns,
            invDt                  out      tinvDate,
            amt                  out      tNumber12_2,
            prjID                  out      tPrjID,
            com                  out      tComments,
            comDt                  out      tComDate,
            comIn                  out      tVarChar5
      );

      procedure spARCollSum
      (
            pSQL                  in      char,
            oc                  out      tChCd,
            clntN                  out       tclntName,
            clntID            out      tclientID,
            amt                  out      tNumber12_2
      );

      procedure rpt164
            (pInvDate1 in  char,
             pInvDate2 in  char,
             OrgCode   out tChCd,
             ClientID  out tClientID,
             InvSeq    out tInvSeq,
             InvTotal  out tInvTotal,
             InvDate   out tInvDate,
             Status    out tStatus,
             BillAttns out tBillAttns,
             OrgName   out tChCdName);

   procedure rpt165
            (pOrgCode  in  char,
             pInvDate1 in  char,
             pInvDate2 in  char,
             pAgeDate  in  char,
             pPayDate  in  char,
              pView     in  varchar2,
             OrgCode   out tChCd,
             ClientID  out tClientID,
             ClntName  out tClntName,
             InvSeq    out tInvSeq,
             InvTotal  out tInvTotal,
             InvDate   out tInvDate,
             Month1Bal out tInvTotal,
             Month2Bal out tInvTotal,
             Month3Bal out tInvTotal,
             Month4Bal out tInvTotal,
             Month5Bal out tInvTotal,
             Month6Bal out tInvTotal,
             Month7Bal out tInvTotal,
             Month8Bal out tInvTotal,
             Month9Bal out tInvTotal,
             Month10Bal out tInvTotal,
             Month11Bal out tInvTotal,
             Month12Bal out tInvTotal,
             Month13Bal out tInvTotal);

      PROCEDURE RPT166_1
            (pSQL            in char,
            pBalance      in number,
            pPayDate in char,
            Month                  out tVarchar20,
            Invseq            out tInvseq,
            Invtotal            out tInvtotal,
            invdate            out tInvDate,
            AsnChCd            OUT tChCd,
            Prjid                  out tPrjID,
            clntname            out tClntName,
            clientid       out tClientID,
            billattns       out tBillattns,
            Balance             out tNumber12_2
      );

      PROCEDURE RPT167
            (pSQL             in char,
            pPayDate            in char,
            AsnChCd            OUT tChCd,
            clntname            out tClntName,
            clientid             out tClientID,
            billattns             out tBillAttns,
            Invseq            out tInvSeq,
            invdate            out tInvDate,
            Marketer             out tVarchar6,
            Balance             out tBalance,
            Com                  out tComments,
            ComDt                  out tComDate,
            comAuth            out tVarChar5,
            comID                  out tComID
      );

   procedure rpt168
            (pAsnChCd  in  char,
             pInvYearMonth in char,
             pPayDate  in  char,
             pView     in  varchar2,       
            asnChCd   out tChCd,
             ClientID  out tClientID,
             ClntName  out tClntName,
             InvSeq    out tInvSeq,
             InvTotal  out tInvTotal,
             InvDate   out tInvDate,
             Month1Inv out tInvTotal,
             Month2Inv out tInvTotal,
             Month3Inv out tInvTotal,
             Month4Inv out tInvTotal,
             Month5Inv out tInvTotal,
             Month6Inv out tInvTotal,
             Month7Inv out tInvTotal,
             Month8Inv out tInvTotal,
             Month9Inv out tInvTotal,
             Month10Inv out tInvTotal,
             Month11Inv out tInvTotal,
             Month12Inv out tInvTotal);

      PROCEDURE RPT170
            (pSQL             in char,
            AsnChCd            OUT tChCd,
            clntname            out tClntName,
            clientid             out tClientID,
            Invseq            out tInvSeq,
            status            out tStatus,
            marketer             out tVarchar6,
            payment             out tPayment,
            paynote            out tPaynote,
            paytype            out tPaytype,
            paydate            out tPaydate);

      procedure Rpt173      
            (pMonthYear            in      number,
            pDate                  in      varchar2,
            pView                  in      varchar2,
            AsnChCd            out      tChCd,
            ClientID            out      tClientID,
            InvSeq            out      tInvSeq,
            refInvSeq            out      tRefInvSeq,
            payment            out      tPayment,
            paynote            out      tPaynote,
            empName            out      tVarchar40);

         procedure rpt178
            (pAsnChCd  in  char,
             pInvDate1 in  char,
             pInvDate2 in  char,
             pAgeDate  in  char,
             pPayDate  in  char,
             pView     in  varchar2,
             AsnChCd   out tChCd,
             ClientID  out tClientID,
             ClntName  out tClntName,
             InvSeq    out tInvSeq,
             InvTotal  out tInvTotal,
             InvDate   out tInvDate,
             Month1Bal out tInvTotal,
             Month2Bal out tInvTotal,
             Month3Bal out tInvTotal,
             Month4Bal out tInvTotal,
             Month5Bal out tInvTotal,
             Month6Bal out tInvTotal,
             Month7Bal out tInvTotal,
             Month8Bal out tInvTotal,
             Month9Bal out tInvTotal,
             Month10Bal out tInvTotal,
             Month11Bal out tInvTotal,
             Month12Bal out tInvTotal,
             Month13Bal out tInvTotal,
             Month1Inv out tInvTotal,
             Month2Inv out tInvTotal,
             Month3Inv out tInvTotal,
             Month4Inv out tInvTotal,
             Month5Inv out tInvTotal,
             Month6Inv out tInvTotal,
             Month7Inv out tInvTotal,
             Month8Inv out tInvTotal,
             Month9Inv out tInvTotal,
             Month10Inv out tInvTotal,
             Month11Inv out tInvTotal,
             Month12Inv out tInvTotal);


      procedure GetSingleRcv(
            pArcvInvID       in  number,
            clientid            out tInvseq,
            invdate            out tInvDate,
            invseq            out tInvseq,
            invtotal            out tBalance,
            orgcode             out torgcode,
            period            out tPeriod,
            prjid                  out tPrjid,
            status            out tStatus,
            balance            out tBalance,
            rcvtype            out tStatus);
      
      procedure GetPayment(
            pArcvInvID      in number,
            payment            out tBalance,
            paydate            out tInvDate,
            paytype            out tStatus,
            paynote             out tMonth);

end packRcv;
/

create or replace package body packRcv
as
      procedure spGetBigClient(
            clientid            out      tClientID4
      )
      as
            cursor c1 is
                  select substr(clientid,1,4) clientid
                  from arcvv
                  where invseq like '0%'
                  group by substr(clientid,1,4)
                  having count(*)>6000;
            i number default 1;
      begin
            for rec in c1
            loop
                  clientid(i) := rec.clientid;
                  i := i+1;
            end loop;
      end;

      procedure spGetRcvInfoDtl(
            pInvseq            in            varchar2,
            arcvinvid      out      tArcvInvID,
            balance            out      tVarchar15,
            clientid            out      tClientID,
            invdate            out      tVarchar10,
            invseq            out      tInvSeq,
            invtotal            out      tVarchar15,
            mktr1                  out      tEmpnum,
            mktr2                  out      tEmpnum,
            OrgCode            out      tOrgCode,
            period            out      tPeriod,
            prjid                  out      tPrjID,
            rcvtype            out      tRcvType,
            refInvseq      out      tRefInvseq,
            Status            out      tStatus
      )
      as
            cursor c1 is
                  select v.arcvinvid arcvinvid, to_char(balance,'999,999,999.99') balance,
                        v.clientid clientid,to_char(invdate,'mm/dd/yyyy') invdate,
                        v.invseq invseq,to_char(invtotal,'999,999,999.99') invtotal,
                        d.mktr1 mktr1,d.mktr2 mktr2,v.orgcode orgcode,v.prjid prjid,
                        v.rcvtype rcvtype,d.refinvseq refinvseq,v.status status
                  from arcvv v, arcvd d
                  where v.arcvinvid=d.arcvinvid and v.invseq=pInvseq;
            i number default 1;
      begin
            for rec in c1
            loop
                  arcvinvid(i)      := rec.arcvinvid;
                  balance(i)            := rec.balance;
                  clientid(i)            := rec.clientid;
                  invdate(i)            := rec.invdate;
                  invseq(i)            := rec.invseq;
                  invtotal(i)            := rec.invtotal;
                  mktr1(i)                  := rec.mktr1;
                  mktr2(i)                  := rec.mktr2;
                  orgcode(i)            := rec.orgcode;
                  prjid(i)                  := rec.prjid;
                  rcvtype(i)            := rec.rcvtype;
                  refinvseq(i)      := rec.refinvseq;
                  status(i)            := rec.status;
                  i := i+1;
            end loop;
      end;

      procedure spGetRollupID (
            pOrgCode            in varchar2,
            RollupID      out tRollupID
      )
      as
            l_rollupid      uschcode.rollupid%type;
      begin
            select rollupid into l_rollupid from uschcode where chargecode = pOrgCode;
            Rollupid(1) := l_rollupid;
      end;

      procedure spARCollIn
      (
            pNotes            in      varchar2,
            pUserID            in      number,
            pInvSeq            in       varchar2,
            result            out       tNumber4
      )
      as
      begin
            insert into colcmnt(colcmntID,comments,userid,invseq,timestamp)
                  values(colcmntID.nextval,pNotes,pUserID,pInvseq,sysdate);
            commit;
            result(1) := 1;
      end;

      procedure spARColl (
            pOrgCode            in      varchar2,
            pPrjID            in      number,
            pClntID            in      varchar2,
            pStartDate      in       varchar2,
            pEndDate            in      varchar2,
            pUnapplied      in       number,
            pPayDate            in      varchar2,
            pBaseCode      in      varchar2,
            invSeq            out      tinvSeq,
            oc                        out      tChCd,
            clntN                  out       tclntName,
            clntID            out      tclientID,
            bAttns            out      tbillAttns,
            invDt                  out      tinvDate,
            amt                  out      tNumber12_2,
            prjID                  out      tPrjID,
            com                  out      tComments,
            comDt                  out      tComDate,
            comIn                  out      tVarChar5
      )
      as
            i                         number             default 1;
            e_b                  number            default 0;
            c1                        integer;
            v_dummy            integer;
            pSQL                  varchar2(1024);

            v_invSeq            arcvv.invSeq%type;
            v_chCd            arcvv.AsnChCode%type;
            v_clntN            client.clntName%type;
            v_clntID            client.clientID%type;
            v_bAttns            client.billAttns%type;
            v_invDt            arcvv.invDate%type;
            v_invTtl            arcvv.invTotal%type;
            v_prjID            arcvv.prjID%type;
            v_arcvinvID      arcvv.arcvinvID%type;

            pmntSum            payment.payment%type;
            v_amt                  payment.payment%type;

            v_com                  colCmnt.comments%type;
            v_comDt            colCmnt.timeStamp%type;
            v_comAuth      colCmnt.userID%type;

            v_ComIn            varchar2(4);
            l_rollupid      uschcode.rollupid%type;
      begin
            select rollupid into l_rollupid from uschcode where chargecode=pOrgCode;

            pSQL:='select v.invseq,v.AsnChCode,substr(v.clientID,1,7), c.clntname, c.billattns, v.invdate,
                  v.invtotal, v.prjid, v.arcvinvid, m.comments, m.timestamp, m.userid
                  from client c, arcvvq_' || pBaseCode || ' v, colcmnt m
                  where v.clientid=c.clientid(+) and v.InvDate between to_date(''' || pStartDate || ''',
                        ''MM/DD/YYYY'') and to_date(''' || pEndDate || ''',''MM/DD/YYYY'') and v.invseq=m.invseq(+)';
      
            if pPrjID!=0 then
                  pSQL:=pSQL || 'and v.prjID = ' || pPrjID || ' ';
            end if;

            if pOrgCode!='*' then
                  pSQL:=pSQL || 'and exists (select 1 from uschcode u where u.chargecode=v.asnchcode'
                        || ' and u.rollupid like ''' || l_rollupid || '%'') ';
            end if;

            if pClntID!='*' then
                  pSQL:=pSQL || 'and v.ClientID like ''' || pClntID || '%'' ';
            end if;

            if pUnapplied=1 then
                  pSQL:=pSQL || 'and (v.invseq<''A'' or v.invseq>''Z'') ';
            else
                  pSQL:=pSQL || 'and v.invseq<''A'' ';
            end if;

            pSQL:=pSQL || ' order by v.AsnChCode, substr(v.clientID,1,4), v.invDate, v.invseq, m.timestamp desc';
      
            c1 := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(c1, pSQL, DBMS_SQL.V7);

            DBMS_SQL.DEFINE_COLUMN(c1, 1, v_invSeq,7);
            DBMS_SQL.DEFINE_COLUMN(c1, 2, v_ChCd,6);
            DBMS_SQL.DEFINE_COLUMN(c1, 3, v_clntID,7);
            DBMS_SQL.DEFINE_COLUMN(c1, 4, v_clntN,35);
            DBMS_SQL.DEFINE_COLUMN(c1, 5, v_bAttns,30);
            DBMS_SQL.DEFINE_COLUMN(c1, 6, v_invDt);
            DBMS_SQL.DEFINE_COLUMN(c1, 7, v_invTtl);
            DBMS_SQL.DEFINE_COLUMN(c1, 8, v_prjID);
            DBMS_SQL.DEFINE_COLUMN(c1, 9, v_arcvinvID);
            DBMS_SQL.DEFINE_COLUMN(c1, 10, v_com,512);
            DBMS_SQL.DEFINE_COLUMN(c1, 11, v_comDt);
            DBMS_SQL.DEFINE_COLUMN(c1, 12, v_comAuth);
            v_dummy := DBMS_SQL.EXECUTE(c1);
            
            loop
                  if DBMS_SQL.FETCH_ROWS(c1) = 0 then
                        exit;
                  end if;

                  DBMS_SQL.COLUMN_VALUE(c1, 1, v_invSeq);
                  DBMS_SQL.COLUMN_VALUE(c1, 2, v_chCd);
                  DBMS_SQL.COLUMN_VALUE(c1, 3, v_clntID);
                  DBMS_SQL.COLUMN_VALUE(c1, 4, v_clntN);
                  DBMS_SQL.COLUMN_VALUE(c1, 5, v_bAttns);
                  DBMS_SQL.COLUMN_VALUE(c1, 6, v_invDt);
                  DBMS_SQL.COLUMN_VALUE(c1, 7, v_invTtl);
                  DBMS_SQL.COLUMN_VALUE(c1, 8, v_prjID);
                  DBMS_SQL.COLUMN_VALUE(c1, 9, v_arcvinvID);
                  DBMS_SQL.COLUMN_VALUE(c1, 10, v_com);
                  DBMS_SQL.COLUMN_VALUE(c1, 11, v_comDt);
                  DBMS_SQL.COLUMN_VALUE(c1, 12, v_comAuth);
            
                  select sum(payment) into pmntSum
                  from payment
                  where arcvinvid=v_arcvinvID;
                  
                  if pmntSum is null then
                        v_amt := v_invTtl;
                  else
                        v_amt := v_invTtl-pmntSum;
                  end if;
            
                  if v_amt !=0 then
                        if v_comAuth is null then
                              v_ComIn := '';
                        else
                              select substr(fname,1,1)||'.'||substr(lname,1,1)||'.' into v_comIn from emp where empnum=v_comAuth;
                        end if;
                        if v_comIn is null then
                              v_comIn := '';
                        end if;
                        invSeq(i)            :=      v_invSeq;
                        oc(i)                  :=      v_chCd;
                        clntN(i)            :=      v_clntN;
                        clntID(i)            :=      v_clntID;
                        bAttns(i)            :=      v_bAttns;
                        invDt(i)            :=      v_invDt;
                        amt(i)            :=      v_amt;
                        prjID(i)            :=      v_prjID;
                        com(i)            :=      v_com;
                        comDt(i)            :=      v_comDt;
                        comIn(i)            :=      v_comIn;
                        i := i+1;            
                  end if;
            end loop;
            DBMS_SQL.CLOSE_CURSOR(c1);
      end;
      
      procedure spARCollSum
      (
            pSQL                  in      char,
            oc                  out      tChCd,
            clntN                  out       tclntName,
            clntID            out      tclientID,
            amt                  out      tNumber12_2
      )
            as
            i                   number             default 1;
            c1                  integer;
            v_dummy            integer;
            v_ChCd            arcvv.AsnChCode%type;
            v_clntN            client.clntName%type;
            v_clntID            varchar2(4);
            v_CLID            varchar2(6);
            v_tChCd            arcvv.AsnChCode%type;
            v_invTtl            arcvv.invTotal%type;
            v_arcvinvID            arcvv.arcvinvID%type;
            pmntSum            payment.payment%type;
            v_amt                  number(12,2)      default 0;
            v_total            number(12,2)      default 0;
      begin
            c1 := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(c1, pSQL, DBMS_SQL.V7);

            DBMS_SQL.DEFINE_COLUMN(c1, 1, v_ChCd,6);
            DBMS_SQL.DEFINE_COLUMN(c1, 2, v_clntID,4);
            DBMS_SQL.DEFINE_COLUMN(c1, 3, v_clntN,30);
            DBMS_SQL.DEFINE_COLUMN(c1, 4, v_invTtl);
            DBMS_SQL.DEFINE_COLUMN(c1, 5, v_arcvinvID);

            v_dummy := DBMS_SQL.EXECUTE(c1);
            
            v_CLID := '-1';
            v_tChCd := '-1';
            loop
                  if DBMS_SQL.FETCH_ROWS(c1) = 0 then
                        exit;
                  end if;

                  DBMS_SQL.COLUMN_VALUE(c1, 1, v_ChCd);
                  DBMS_SQL.COLUMN_VALUE(c1, 2, v_clntID);
                  DBMS_SQL.COLUMN_VALUE(c1, 3, v_clntN);
                  DBMS_SQL.COLUMN_VALUE(c1, 4, v_invTtl);
                  DBMS_SQL.COLUMN_VALUE(c1, 5, v_arcvinvID);
                  
                  select sum(payment) into pmntSum
                  from payment
                  where arcvinvid=v_arcvinvID;
                  
                  if pmntSum is null then
                        v_amt := v_invTtl;
                  else
                        v_amt := v_invTtl-pmntSum;
                  end if;
            
                  if v_amt !=0 then
                        if v_clntID != v_CLID or v_ChCd!=v_tChCd then
                              if v_CLID != '-1' then
                                    i := i+1;
                              end if;
                              v_CLID       :=       v_clntID;
                              v_tChCd       :=      v_ChCd;
                              clntID(i)      :=      v_clntID;
                              oc(i)            :=      v_ChCd;
                              clntN(i)      :=      v_clntN;
                              amt(i)      :=      v_amt;
                        else
                              amt(i)      :=      amt(i)+ v_amt;
                        end if;
                  end if;
            end loop;
            DBMS_SQL.CLOSE_CURSOR(c1);
      end;

      procedure rpt164
            (pInvDate1 in  char,
             pInvDate2 in  char,
             OrgCode   out tChCd,
             ClientID  out tClientID,
             InvSeq    out tInvSeq,
             InvTotal  out tInvTotal,
             InvDate   out tInvDate,
             Status    out tStatus,
             BillAttns out tBillAttns,
             OrgName   out tChCdName)
      is
         cursor c1 is
            select o.CodeDesc OrgName,v.AsnChCode OrgCode,v.ClientID ClientID,c.BillAttns BillAttns,
                   v.InvSeq InvSeq,v.InvTotal InvTotal,v.Status Status,v.InvDate InvDate
            from   arcvv v,client c,USChCode o
            where  v.InvSeq < 'A'
            and    v.AsnChCode = o.ChargeCode
            and    v.ClientID = c.ClientID
            and    v.InvDate between to_date(pInvDate1,'MM/DD/YYYY') and to_date(pInvDate2,'MM/DD/YYYY')
            order by v.AsnChCode,v.ClientID,v.InvSeq;
      i number default 1;
      begin
        for rec in c1
        loop              
            OrgCode(i)   := rec.OrgCode;      
            ClientID(i)  := rec.ClientID;
            InvSeq(i)    := rec.InvSeq;
            InvTotal(i)  := rec.InvTotal;
            InvDate(i)   := rec.InvDate;
            Status(i)    := rec.Status;
            BillAttns(i) := rec.BillAttns;
            OrgName(i)   := rec.OrgName;
            i := i+1;
      end loop;         
      end;

   procedure rpt165
            (pOrgCode  in  char,
             pInvDate1 in  char,
             pInvDate2 in  char,
             pAgeDate  in  char,
             pPayDate  in  char,
              pView     in  varchar2,
             OrgCode   out tChCd,
             ClientID  out tClientID,
             ClntName  out tClntName,
             InvSeq    out tInvSeq,
             InvTotal  out tInvTotal,
             InvDate   out tInvDate,
             Month1Bal out tInvTotal,
             Month2Bal out tInvTotal,
             Month3Bal out tInvTotal,
             Month4Bal out tInvTotal,
             Month5Bal out tInvTotal,
             Month6Bal out tInvTotal,
             Month7Bal out tInvTotal,
             Month8Bal out tInvTotal,
             Month9Bal out tInvTotal,
             Month10Bal out tInvTotal,
             Month11Bal out tInvTotal,
             Month12Bal out tInvTotal,
             Month13Bal out tInvTotal)
      is
            l_rollupid      uschcode.rollupid%type;
      v_chCd            USChCode.ChargeCode%type;
      v_invseq       arcvv.invseq%type;
      v_clntname     client.clntname%type;
      v_clientid     client.clientid%type;
      v_arcvinvid    arcvv.arcvInvID%type;
      v_invtotal     arcvv.invtotal%type;
      v_invdate      date;
      pSQL              varchar2(1024);
      pSQL1                  varchar2(1024);

      c1                   integer;
      v_dummy            integer;

      i number default 1;

      paymentsum number(13,2);
   mBalance number(13,2);
      mMonth1Bal number(13,2);
      mMonth2Bal number(13,2);
      mMonth3Bal number(13,2);
      mMonth4Bal number(13,2);
      mMonth5Bal number(13,2);
      mMonth6Bal number(13,2);
      mMonth7Bal number(13,2);
      mMonth8Bal number(13,2);
      mMonth9Bal number(13,2);
      mMonth10Bal number(13,2);
      mMonth11Bal number(13,2);
      mMonth12Bal number(13,2);
      mMonth13Bal number(13,2);
      mBalTotal number(13,2);

      begin
            select rollupid into l_rollupid from uschcode where chargecode=pOrgCode;

      pSQL := 'select v.AsnChCode OrgCode,v.InvSeq InvSeq,v.InvTotal InvTotal,'
                  || 'substr(v.ClientID,1,4) ClientID,c.ClntName ClntName,v.InvDate InvDate,'
                  || 'v.arcvInvID arcvInvID from   arcvvq_';
      pSQL1 := '(v.invseq<''A'' or v.invseq>''Z'') and v.InvDate between to_date('''
                  || pInvDate1 || ''',''MM/DD/YYYY'') and to_date(''' || pInvDate2
                  || ''',''MM/DD/YYYY'') and   v.clientID = c.clientid'
                  || ' order by v.AsnChCode,substr(v.ClientID,1,4),v.InvSeq';

      if pOrgCode = 'USTOT' then
         pSQL := pSQL || pView || ' v, client c where ' || pSQL1;
            else            
         pSQL := pSQL || pView || ' v, client c where exists(select 1 from uschcode u '
                        || 'where u.chargecode=v.asnchcode and u.rollupid like ''' || l_rollupid
                        || '%'') and ' || pSQL1;
   end if;


      c1 := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c1, pSQL, DBMS_SQL.V7);

      DBMS_SQL.DEFINE_COLUMN(c1, 1, v_chCd,6);
   DBMS_SQL.DEFINE_COLUMN(c1, 2, v_invseq,7);
   DBMS_SQL.DEFINE_COLUMN(c1, 3, v_invtotal);
   DBMS_SQL.DEFINE_COLUMN(c1, 4, v_clientid,7);
   DBMS_SQL.DEFINE_COLUMN(c1, 5, v_clntname,35);
   DBMS_SQL.DEFINE_COLUMN(c1, 6, v_invdate);
   DBMS_SQL.DEFINE_COLUMN(c1, 7, v_arcvinvid);

      v_dummy := DBMS_SQL.EXECUTE(c1);
      loop
            if DBMS_SQL.FETCH_ROWS(c1) = 0 then
                  exit;
            end if;

            DBMS_SQL.COLUMN_VALUE(c1,1,v_chCd);
      DBMS_SQL.COLUMN_VALUE(c1,2,v_invseq);
      DBMS_SQL.COLUMN_VALUE(c1,3,v_invtotal);
      DBMS_SQL.COLUMN_VALUE(c1,4,v_clientid);
      DBMS_SQL.COLUMN_VALUE(c1,5,v_clntname);
      DBMS_SQL.COLUMN_VALUE(c1,6,v_invdate);
      DBMS_SQL.COLUMN_VALUE(c1,7,v_arcvinvid);

            select sum(payment) into paymentsum
            from   payment
      where  arcvinvid = v_arcvinvid
            and    paydate<=to_date(pPayDate,'MM/DD/YYYY');
            if paymentsum is null then
         mBalance := v_invTotal;
            else
         mBalance := v_invTotal - paymentsum;
            end if;            

               if mBalance != 0 then
                mMonth1Bal := 0;
                mMonth2Bal := 0;
                mMonth3Bal := 0;
                mMonth4Bal := 0;
                mMonth5Bal := 0;
                mMonth6Bal := 0;
                mMonth7Bal := 0;
                mMonth8Bal := 0;
                mMonth9Bal := 0;
                mMonth10Bal := 0;
                mMonth11Bal := 0;
                mMonth12Bal := 0;
                mMonth13Bal := 0;
                         
         if v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY') and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-30 then
                  mMonth1Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-30 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-60 then
                  mMonth2Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-60 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-90 then
                  mMonth3Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-90 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-120 then
                  mMonth4Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-120 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-150 then
                  mMonth5Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-150 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-180 then
                  mMonth6Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-180 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-210 then
                  mMonth7Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-210 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-240 then
                  mMonth8Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-240 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-270 then
                  mMonth9Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-270 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-300 then
                  mMonth10Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-300 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-330 then
                  mMonth11Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-330 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-360 then
                  mMonth12Bal := mBalance;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-360 then
                  mMonth13Bal := mBalance;
               end if;

               InvTotal(i)    := mBalance;
         OrgCode(i)     := v_chCd;
         ClientID(i)    := v_ClientID;
         ClntName(i)    := v_ClntName;
         InvSeq(i)      := v_InvSeq;
         InvDate(i)     := v_InvDate;
               Month1Bal(i)   := mMonth1Bal;
               Month2Bal(i)   := mMonth2Bal;
               Month3Bal(i)   := mMonth3Bal;
               Month4Bal(i)   := mMonth4Bal;
               Month5Bal(i)   := mMonth5Bal;
               Month6Bal(i)   := mMonth6Bal;
               Month7Bal(i)   := mMonth7Bal;
               Month8Bal(i)   := mMonth8Bal;
               Month9Bal(i)   := mMonth9Bal;
               Month10Bal(i)  := mMonth10Bal;
               Month11Bal(i)  := mMonth11Bal;
               Month12Bal(i)  := mMonth12Bal;
               Month13Bal(i)  := mMonth13Bal;
               i := i+1;
            end if;
          end loop;
            DBMS_SQL.CLOSE_CURSOR(c1);
      end;


      PROCEDURE RPT166_1 (
            pSQL            in char,
            pBalance      in number,
            pPayDate in char,
            Month                  out tVarchar20,
            Invseq            out tInvseq,
            Invtotal            out tInvtotal,
            invdate            out tInvDate,
            AsnChCd            OUT tChCd,
            Prjid                  out tPrjID,
            clntname            out tClntName,
            clientid       out tClientID,
            billattns       out tBillattns,
            Balance             out tNumber12_2
      )
      is
      i number             default 1;
      v_dummy                  integer;
      v_month                  Varchar(20);
      v_invseq                  arcvv.invseq%type;
      v_invtotal            arcvv.invtotal%type;
      v_invdate            date;
      v_chCd            arcvv.AsnChCode%type;
      v_prjid                  arcvv.prjid%type;
      v_clntname            client.clntname%type;
      v_clientid            client.clientid%type;
      v_billattns            client.billattns%type;
      v_balance            number(12,2);
      v_arcvinvid            arcvv.arcvinvid%type;
      paymentsum            number(12,2);
      c1                         integer;
begin
      c1 := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c1, pSQL, DBMS_SQL.V7);
      v_prjid := 0;

      DBMS_SQL.DEFINE_COLUMN(c1, 1, v_clientid,7);
      DBMS_SQL.DEFINE_COLUMN(c1, 2, v_invdate);
      DBMS_SQL.DEFINE_COLUMN(c1, 3, v_invseq,7);
      DBMS_SQL.DEFINE_COLUMN(c1, 4, v_month,20);
      DBMS_SQL.DEFINE_COLUMN(c1, 5, v_invtotal);
      DBMS_SQL.DEFINE_COLUMN(c1, 6, v_chCd,6);
      DBMS_SQL.DEFINE_COLUMN(c1, 7, v_prjid);
      DBMS_SQL.DEFINE_COLUMN(c1, 8, v_clntname,35);
      DBMS_SQL.DEFINE_COLUMN(c1, 9, v_billattns,30);
      DBMS_SQL.DEFINE_COLUMN(c1, 10, v_arcvinvid);
      v_dummy := DBMS_SQL.EXECUTE(c1);
      loop
            if DBMS_SQL.FETCH_ROWS(c1) = 0 then
                  exit;
            end if;

            DBMS_SQL.COLUMN_VALUE(c1,1,v_clientid);
            DBMS_SQL.COLUMN_VALUE(c1,2,v_invdate);
            DBMS_SQL.COLUMN_VALUE(c1,3,v_invseq);
            DBMS_SQL.COLUMN_VALUE(c1,4,v_month);
            DBMS_SQL.COLUMN_VALUE(c1,5,v_invtotal);
            DBMS_SQL.COLUMN_VALUE(c1,6,v_chCd);
            DBMS_SQL.COLUMN_VALUE(c1,7,v_prjid);
            DBMS_SQL.COLUMN_VALUE(c1,8,v_clntname);
            DBMS_SQL.COLUMN_VALUE(c1,9,v_billattns);
            DBMS_SQL.COLUMN_VALUE(c1,10,v_arcvinvid);

            if pPayDate='' then
                  select sum(payment) into paymentsum
                  from payment
                  where arcvinvid=v_arcvinvid;
            else
                  select sum(payment) into paymentsum
                  from payment
                  where arcvinvid=v_arcvinvid and paydate<=to_date(pPayDate,'MM/DD/YYYY');
            end if;
                  
            if paymentsum is null then
                  v_balance := v_invtotal;
            else
                  v_balance := v_invtotal-paymentsum;
            end if;
            
            if (pBalance=0 and v_balance<>0) or (pBalance=1 and v_balance=0) or pBalance=2 then
                  month(i)            := v_month;
                  invseq(i)      := v_invseq;
                  invtotal(i)      := v_invtotal;
                  invdate(i)      := v_invdate;
                  AsnChCd(i)      := v_chCd;
                  prjid(i)      := v_prjid;
                  clntname(i)      := v_clntname;
                  clientid(i)      := v_clientid;
                  billattns(i):= v_billattns;
                  balance(i)       := v_balance;
                  i := i+1;

            end if;
      end loop;
      DBMS_SQL.CLOSE_CURSOR(c1);
end;

PROCEDURE RPT167
            (pSQL                  IN CHAR,
            pPayDate             in char,
            AsnChCd            OUT tChCd,
            clntname            out tClntName,
            clientid             out tClientID,
            billattns             out tBillAttns,
            Invseq            out tInvSeq,
            invdate            out tInvDate,
            Marketer          out tVarchar6,
            Balance             out tBalance,
            Com                  out tComments,
            ComDt                  out tComDate,
            comAuth            out tVarChar5,
            comID                  out tComID)
is
      i number             default 1;
      v_dummy            integer;
      v_chCd            arcvv.asnchcode%type;
      v_clntname            varchar(35);
      v_clientid            varchar(7);
      v_billattns            varchar(30);
      v_invseq            Varchar(7);
      v_invdate            varchar(10);
      v_marketer            varchar(6);
      v_arcvinvid            number(7,0);
      v_invtotal            number(12,2);
      v_balance            number(12,2);
      v_com                  colcmnt.comments%type;
      v_comDt            colcmnt.timestamp%type;
      v_comAuth            colcmnt.userid%type;
      v_comID            colcmnt.colcmntID%type;
      v_comIn            varchar(5);
      paymentsum            number(12,2);
      c1                   integer;
begin
      c1 := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c1, pSQL, DBMS_SQL.V7);

      DBMS_SQL.DEFINE_COLUMN(c1, 1, v_chCd,6);
      DBMS_SQL.DEFINE_COLUMN(c1, 2, v_clntname,35);
      DBMS_SQL.DEFINE_COLUMN(c1, 3, v_clientid,7);
      DBMS_SQL.DEFINE_COLUMN(c1, 4, v_billattns,30);
      DBMS_SQL.DEFINE_COLUMN(c1, 5, v_invseq,7);
      DBMS_SQL.DEFINE_COLUMN(c1, 6, v_invdate,10);
      DBMS_SQL.DEFINE_COLUMN(c1, 7, v_invtotal);
      DBMS_SQL.DEFINE_COLUMN(c1, 8, v_marketer,6);
      DBMS_SQL.DEFINE_COLUMN(c1, 9, v_arcvinvid);
      DBMS_SQL.DEFINE_COLUMN(c1, 10, v_com,512);
      DBMS_SQL.DEFINE_COLUMN(c1, 11, v_comDt);
      DBMS_SQL.DEFINE_COLUMN(c1, 12, v_comAuth);
      DBMS_SQL.DEFINE_COLUMN(c1, 13, v_comID);

      v_dummy := DBMS_SQL.EXECUTE(c1);
      loop
            if DBMS_SQL.FETCH_ROWS(c1) = 0 then
                  exit;
            end if;

            DBMS_SQL.COLUMN_VALUE(c1,1,v_chCd);
            DBMS_SQL.COLUMN_VALUE(c1,2,v_clntname);
            DBMS_SQL.COLUMN_VALUE(c1,3,v_clientid);
            DBMS_SQL.COLUMN_VALUE(c1,4,v_billattns);
            DBMS_SQL.COLUMN_VALUE(c1,5,v_invseq);
            DBMS_SQL.COLUMN_VALUE(c1,6,v_invdate);
            DBMS_SQL.COLUMN_VALUE(c1,7,v_invtotal);
            DBMS_SQL.COLUMN_VALUE(c1,8,v_marketer);
            DBMS_SQL.COLUMN_VALUE(c1,9,v_arcvinvid);
            DBMS_SQL.COLUMN_VALUE(c1,10,v_com);
            DBMS_SQL.COLUMN_VALUE(c1,11,v_comDt);
            DBMS_SQL.COLUMN_VALUE(c1,12,v_comAuth);
            DBMS_SQL.COLUMN_VALUE(c1,13,v_comID);

            if pPayDate='' then
                  select sum(payment) into paymentsum
                  from payment
                  where arcvinvid=v_arcvinvid;
            else
                  select sum(payment) into paymentsum
                  from payment
                  where arcvinvid=v_arcvinvid and paydate<=to_date(pPayDate,'MM/DD/YYYY');
            end if;
                  
            if paymentsum is null then
                  v_balance := v_invtotal;
            else
                  v_balance := v_invtotal-paymentsum;
            end if;
            if v_balance != 0 then
                  if v_comAuth is null then
                        v_ComIn := '';
                  else
                        select substr(fname,1,1)||'.'||substr(lname,1,1)||'.' into v_comIn from emp where empnum=v_comAuth;
                  end if;
                  if v_comIn is null then
                        v_comIn := '';
                  end if;
                  AsnChCd(i)      := v_chCd;
                  clntname(i)      := v_clntname;
                  clientid(i)      := v_clientid;
                  billattns(i):= v_billattns;
                  invseq(i)      := v_invseq;
                  invdate(i)      := v_invdate;
                  marketer(i) := v_marketer;            
                  balance(i)       := v_balance;
                  com(i)      := v_com;
                  comDt(i)      := v_comDt;
                  comAuth(i)      := v_comIn;
                  comID(i)      := v_comID;
                  i := i+1;
            end if;
      end loop;
      DBMS_SQL.CLOSE_CURSOR(c1);
end;

   procedure rpt168
            (pAsnChCd  in  char,
             pInvYearMonth in char,
             pPayDate  in  char,
       pView     in  varchar2,
             AsnChCd   out tChCd,
             ClientID  out tClientID,
             ClntName  out tClntName,
             InvSeq    out tInvSeq,
             InvTotal  out tInvTotal,
             InvDate   out tInvDate,
             Month1Inv out tInvTotal,
             Month2Inv out tInvTotal,
             Month3Inv out tInvTotal,
             Month4Inv out tInvTotal,
             Month5Inv out tInvTotal,
             Month6Inv out tInvTotal,
             Month7Inv out tInvTotal,
             Month8Inv out tInvTotal,
             Month9Inv out tInvTotal,
             Month10Inv out tInvTotal,
             Month11Inv out tInvTotal,
             Month12Inv out tInvTotal)
      is
      v_chCd      arcvv.AsnChCode%type;
      v_invseq       arcvv.invseq%type;
      v_clntname     client.clntname%type;
      v_clientid     client.clientid%type;
      v_arcvinvid    arcvv.arcvInvID%type;
      v_invtotal     arcvv.invtotal%type;
      v_invdate      date;
      mInvDate       char(6);
      pSQL              varchar2(1024);
      pSQL1                  varchar2(1024);

      c1                   integer;
      v_dummy            integer;
      i number default 1;

   mMonthNum number(3,0);
      mCrrYear number(4,0);
   mCrrMonth number(2,0);
      mInvYear number(4,0);
   mInvMonth number(2,0);
   paymentsum number(13,2);
      mBalance number(13,2);
      mMonth1Inv number(13,2);
      mMonth2Inv number(13,2);
      mMonth3Inv number(13,2);
      mMonth4Inv number(13,2);
      mMonth5Inv number(13,2);
      mMonth6Inv number(13,2);
      mMonth7Inv number(13,2);
      mMonth8Inv number(13,2);
      mMonth9Inv number(13,2);
      mMonth10Inv number(13,2);
      mMonth11Inv number(13,2);
      mMonth12Inv number(13,2);
      l_rollupid            uschcode.rollupid%type;

      begin
            select rollupid into l_rollupid from uschcode where chargecode=pAsnChCd;

      pSQL := 'select v.AsnChCode,v.InvTotal,
               substr(v.ClientID,1,4) ClientID,c.ClntName ClntName,
               v.arcvInvID arcvInvID,v.InvDate InvDate,v.InvSeq InvSeq
               from arcvvq_';

      pSQL1 := '(v.invseq<''A'' or v.invseq>''Z'') and to_char(v.InvDate,''YYYYMM'')<=''' || pInvYearMonth ||
               ''' and v.clientID = c.clientid
               order by v.AsnChCode,substr(v.ClientID,1,4),v.InvSeq';

   if pAsnChCd = 'USTOT' then
         pSQL := pSQL || pView || ' v, client c where ' || pSQL1;
      else            
         pSQL := pSQL || pView || ' v, client c where exists(select 1 from uschcode u'
                        || ' where u.chargecode=v.asnchcode and u.rollupid like ''' || l_rollupid
                        || '%'') and ' || pSQL1;
   end if;

      c1 := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c1, pSQL, DBMS_SQL.V7);

      DBMS_SQL.DEFINE_COLUMN(c1, 1, v_chCd,6);
   DBMS_SQL.DEFINE_COLUMN(c1, 2, v_invtotal);
   DBMS_SQL.DEFINE_COLUMN(c1, 3, v_clientid,7);
   DBMS_SQL.DEFINE_COLUMN(c1, 4, v_clntname,35);
   DBMS_SQL.DEFINE_COLUMN(c1, 5, v_arcvinvid);
   DBMS_SQL.DEFINE_COLUMN(c1, 6, v_invdate);
   DBMS_SQL.DEFINE_COLUMN(c1, 7, v_invseq,7);

      v_dummy := DBMS_SQL.EXECUTE(c1);
      loop
            if DBMS_SQL.FETCH_ROWS(c1) = 0 then
                  exit;
            end if;

            DBMS_SQL.COLUMN_VALUE(c1,1,v_chCd);
      DBMS_SQL.COLUMN_VALUE(c1,2,v_invtotal);
      DBMS_SQL.COLUMN_VALUE(c1,3,v_clientid);
      DBMS_SQL.COLUMN_VALUE(c1,4,v_clntname);
      DBMS_SQL.COLUMN_VALUE(c1,5,v_arcvinvid);
      DBMS_SQL.COLUMN_VALUE(c1,6,v_invdate);
      DBMS_SQL.COLUMN_VALUE(c1,7,v_invseq);

            select sum(payment) into paymentsum
            from   payment
      where  arcvinvid = v_arcvinvid
            and    paydate<=to_date(pPayDate,'MM/DD/YYYY');

            if paymentsum is null then
         mBalance := v_invTotal;
            else
         mBalance := v_invTotal - paymentsum;
            end if;            

              mCrrYear := to_number(substr(pInvYearMonth,1,4),'9999');
              mCrrMonth := to_number(substr(pInvYearMonth,5,2),'99');
            
      if v_InvDate is null then
               mInvYear := 0;
               mInvMonth := 0;
         mMonthNum := 0;
            else
         mInvDate := to_char(v_InvDate,'YYYYMM');
         mInvYear := to_number(substr(mInvDate,1,4),'9999');
         mInvMonth := to_number(substr(mInvDate,5,2),'99');

               if mInvYear = mCrrYear then
                    mMonthNum := mCrrMonth - mInvMonth + 1;
               else
                   if mInvYear = 0 then
                        mMonthNum := 0;
                  else
                   if mInvYear > 1990 then
                               mMonthNum := 12 * (mCrrYear - mInvYear) + mCrrMonth - mInvMonth + 1;
                   else
                           mMonthNum := 0;
                   end if;
                  end if;
               end if;
            end if;

         if mBalance != 0 or (v_InvTotal != 0 and mMonthNum>0 and mMonthNum<13) then
               mMonth1Inv := 0;
                mMonth2Inv := 0;
                mMonth3Inv := 0;
                mMonth4Inv := 0;
                mMonth5Inv := 0;
                mMonth6Inv := 0;
                mMonth7Inv := 0;
                mMonth8Inv := 0;
                mMonth9Inv := 0;
                mMonth10Inv := 0;
                mMonth11Inv := 0;
                mMonth12Inv := 0;       
               if mMonthNum = 1 then
         mMonth1Inv := v_InvTotal;
               elsif mMonthNum = 2 then
         mMonth2Inv := v_InvTotal;
               elsif mMonthNum = 3 then
         mMonth3Inv := v_InvTotal;
               elsif mMonthNum = 4 then
         mMonth4Inv := v_InvTotal;
               elsif mMonthNum = 5 then
         mMonth5Inv := v_InvTotal;
               elsif mMonthNum = 6 then
         mMonth6Inv := v_InvTotal;
               elsif mMonthNum = 7 then
         mMonth7Inv := v_InvTotal;
               elsif mMonthNum = 8 then
         mMonth8Inv := v_InvTotal;
               elsif mMonthNum = 9 then
         mMonth9Inv := v_InvTotal;
               elsif mMonthNum = 10 then
         mMonth10Inv := v_InvTotal;
               elsif mMonthNum = 11 then
         mMonth11Inv := v_InvTotal;
               elsif mMonthNum = 12 then
         mMonth12Inv := v_InvTotal;
               end if;

               InvTotal(i)    := mBalance;
         AsnChCd(i)     := v_chCd;
         ClientID(i)    := v_ClientID;
         ClntName(i)    := v_ClntName;
         InvSeq(i)      := v_InvSeq;
         InvDate(i)     := mInvDate;
               Month1Inv(i)   := mMonth1Inv;
               Month2Inv(i)   := mMonth2Inv;
               Month3Inv(i)   := mMonth3Inv;
               Month4Inv(i)   := mMonth4Inv;
               Month5Inv(i)   := mMonth5Inv;
               Month6Inv(i)   := mMonth6Inv;
               Month7Inv(i)   := mMonth7Inv;
               Month8Inv(i)   := mMonth8Inv;
               Month9Inv(i)   := mMonth9Inv;
               Month10Inv(i)  := mMonth10Inv;
               Month11Inv(i)  := mMonth11Inv;
               Month12Inv(i)  := mMonth12Inv;
               i := i+1;
            end if;
          end loop;
            DBMS_SQL.CLOSE_CURSOR(c1);         
      end;

PROCEDURE RPT170
            (pSQL                  IN CHAR,
            AsnChCd            OUT tChCd,
            clntname            out tClntName,
            clientid             out tClientID,
            Invseq            out tInvSeq,
            status            out tStatus,
            marketer             out tVarchar6,
            payment             out tPayment,
            paynote            out tPaynote,
            paytype            out tPaytype,
            paydate            out tPaydate)
is
      i number             default 1;
      v_dummy            integer;
      v_chCd            arcvv.asnchcode%type;
      v_clntname            varchar(35);
      v_clientid            varchar(7);
      v_invseq            Varchar(7);
      v_status            varchar(1);
      v_marketer            varchar(6);
      v_payment            number(12,2);
      v_paynote            varchar(30);
      v_paytype            varchar(1);
      v_paydate            date;
      c1                   integer;
begin
      c1 := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c1, pSQL, DBMS_SQL.V7);

      DBMS_SQL.DEFINE_COLUMN(c1, 1, v_chCd,6);
      DBMS_SQL.DEFINE_COLUMN(c1, 2, v_clntname,35);
      DBMS_SQL.DEFINE_COLUMN(c1, 3, v_clientid,7);
      DBMS_SQL.DEFINE_COLUMN(c1, 4, v_invseq,7);
      DBMS_SQL.DEFINE_COLUMN(c1, 5, v_status,1);
      DBMS_SQL.DEFINE_COLUMN(c1, 6, v_marketer,6);
      DBMS_SQL.DEFINE_COLUMN(c1, 7, v_payment);
      DBMS_SQL.DEFINE_COLUMN(c1, 8, v_paynote,30);
      DBMS_SQL.DEFINE_COLUMN(c1, 9, v_paytype,1);
      DBMS_SQL.DEFINE_COLUMN(c1, 10, v_paydate);

      v_dummy := DBMS_SQL.EXECUTE(c1);
      loop
            if DBMS_SQL.FETCH_ROWS(c1) = 0 then
                  exit;
            end if;

            DBMS_SQL.COLUMN_VALUE(c1,1,v_chCd);
            DBMS_SQL.COLUMN_VALUE(c1,2,v_clntname);
            DBMS_SQL.COLUMN_VALUE(c1,3,v_clientid);
            DBMS_SQL.COLUMN_VALUE(c1,4,v_invseq);
            DBMS_SQL.COLUMN_VALUE(c1,5,v_status);
            DBMS_SQL.COLUMN_VALUE(c1,6,v_marketer);
            DBMS_SQL.COLUMN_VALUE(c1,7,v_payment);
            DBMS_SQL.COLUMN_VALUE(c1,8,v_paynote);
            DBMS_SQL.COLUMN_VALUE(c1,9,v_paytype);
            DBMS_SQL.COLUMN_VALUE(c1,10,v_paydate);
            
            if v_payment !=0 then
                  AsnChCd(i)      := v_chCd;
                  clntname(i)      := v_clntname;
                  clientid(i)      := v_clientid;
                  invseq(i)      := v_invseq;
                  status(i)      := v_status;
                  marketer(i) := v_marketer;            
                  payment(i)      := v_payment;
                  paynote(i)      := v_paynote;
                  paytype(i)      := v_paytype;
                  paydate(i)      := v_paydate;
      
                  i := i+1;
            end if;
      end loop;
      DBMS_SQL.CLOSE_CURSOR(c1);
end;

procedure Rpt173      
      (pMonthYear            in      number,
      pDate                  in      varchar2,
      pView                  in      varchar2,
      AsnChCd            out      tChCd,
      ClientID            out      tClientID,
      InvSeq            out      tInvSeq,
      RefInvSeq            out      tRefInvSeq,
      Payment            out      tPayment,
      Paynote            out      tPaynote,
      EmpName            out      tVarchar40)
is
      v_chCd            arcvv.AsnChCode%type;
      v_invseq            arcvv.invseq%type;
      v_clientid           client.clientid%type;
      v_mktr               arcvd.mktr1%type;
      v_refinvseq          arcvd.refinvseq%type;
      v_payment            number(12,2);
      v_paynote            varchar2(30);
      mEmpName             varchar2(40);
      pSQL              varchar2(1024);
      pSQL1                  varchar2(1024);
      c1                integer;
      v_dummy           integer;
      i                   number default 1;
begin
      pSQL := 'select v.AsnChCode,v.ClientID,v.InvSeq,
                     max(d.mktr1), max(d.refinvseq),
                     p.payment,p.paynote
               from payment p,arcvd d,arcvvq_' || pView || ' v
               where ';
      if pMonthYear = 0 then
         pSQL := pSQL || 'to_char(p.paydate,''MMYYYY'')=''' || pDate || ''' ';
      else
         pSQL := pSQL || 'to_char(p.paydate,''MMYYYY'')<=''' || pDate || ''' ';
      end if;

      pSQL := pSQL || 'and p.payment<>0 and p.paytype=''W''
                     and v.arcvinvid = p.arcvinvid
                     and v.arcvinvid = d.arcvinvid
               group by v.AsnChCode,v.ClientID,v.InvSeq,p.payment,p.paynote
               order by v.AsnChCode,max(d.mktr1),v.ClientID,v.InvSeq';

      insert into test(test3) values(pSQL);
      commit;

      c1 := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c1, pSQL, DBMS_SQL.V7);
      DBMS_SQL.DEFINE_COLUMN(c1, 1, v_chCd,6);
      DBMS_SQL.DEFINE_COLUMN(c1, 2, v_clientid,7);
      DBMS_SQL.DEFINE_COLUMN(c1, 3, v_invseq,7);
      DBMS_SQL.DEFINE_COLUMN(c1, 4, v_mktr);
      DBMS_SQL.DEFINE_COLUMN(c1, 5, v_refinvseq,7);
      DBMS_SQL.DEFINE_COLUMN(c1, 6, v_payment);
      DBMS_SQL.DEFINE_COLUMN(c1, 7, v_paynote,30);

      v_dummy := DBMS_SQL.EXECUTE(c1);

      loop
         if DBMS_SQL.FETCH_ROWS(c1) = 0 then
            exit;
         end if;

         DBMS_SQL.COLUMN_VALUE(c1,1,v_chCd);
         DBMS_SQL.COLUMN_VALUE(c1,2,v_clientid);
         DBMS_SQL.COLUMN_VALUE(c1,3,v_invseq);
         DBMS_SQL.COLUMN_VALUE(c1,4,v_mktr);
         DBMS_SQL.COLUMN_VALUE(c1,5,v_refinvseq);
         DBMS_SQL.COLUMN_VALUE(c1,6,v_payment);
         DBMS_SQL.COLUMN_VALUE(c1,7,v_paynote);

         if v_mktr > 0 then
            select lname||','||fname into mEmpname
            from   emp
            where  empnum = v_mktr;
         else
            mEmpname := 'No Marketer';
         end if;
        
         AsnChCd(i)     := v_chCd;
         ClientID(i)    := v_ClientID;
         InvSeq(i)      := v_InvSeq;
         RefInvSeq(i)   := v_refinvseq;
         Payment(i)     := v_payment;
         Paynote(i)     := v_paynote;
         empname(i)     := mEmpName;
         i := i+1;

      end loop;  
   
end;

   procedure rpt178
            (pAsnChCd  in  char,
             pInvDate1 in  char,
             pInvDate2 in  char,
             pAgeDate  in  char,
             pPayDate  in  char,
             pView     in  varchar2,
             AsnChCd   out tChCd,
             ClientID  out tClientID,
             ClntName  out tClntName,
             InvSeq    out tInvSeq,
             InvTotal  out tInvTotal,
             InvDate   out tInvDate,
             Month1Bal out tInvTotal,
             Month2Bal out tInvTotal,
             Month3Bal out tInvTotal,
             Month4Bal out tInvTotal,
             Month5Bal out tInvTotal,
             Month6Bal out tInvTotal,
             Month7Bal out tInvTotal,
             Month8Bal out tInvTotal,
             Month9Bal out tInvTotal,
             Month10Bal out tInvTotal,
             Month11Bal out tInvTotal,
             Month12Bal out tInvTotal,
             Month13Bal out tInvTotal,
             Month1Inv out tInvTotal,
             Month2Inv out tInvTotal,
             Month3Inv out tInvTotal,
             Month4Inv out tInvTotal,
             Month5Inv out tInvTotal,
             Month6Inv out tInvTotal,
             Month7Inv out tInvTotal,
             Month8Inv out tInvTotal,
             Month9Inv out tInvTotal,
             Month10Inv out tInvTotal,
             Month11Inv out tInvTotal,
             Month12Inv out tInvTotal)
      is
            l_rollupid            uschcode.rollupid%type;
      v_chCd        arcvv.AsnChCode%type;
      v_invseq       arcvv.invseq%type;
      v_clntname     client.clntname%type;
      v_clientid     client.clientid%type;
      v_arcvinvid    arcvv.arcvInvID%type;
      v_invtotal     arcvv.invtotal%type;
      v_invdate      date;
      pSQL              varchar2(1024);
      pSQL1                  varchar2(1024);


      c1                   integer;
      v_dummy            integer;

      i number default 1;

   paymentsum number(13,2);
   v_balance number(13,2);
      mMonth1Bal number(13,2);
      mMonth2Bal number(13,2);
      mMonth3Bal number(13,2);
      mMonth4Bal number(13,2);
      mMonth5Bal number(13,2);
      mMonth6Bal number(13,2);
      mMonth7Bal number(13,2);
      mMonth8Bal number(13,2);
      mMonth9Bal number(13,2);
      mMonth10Bal number(13,2);
      mMonth11Bal number(13,2);
      mMonth12Bal number(13,2);
      mMonth13Bal number(13,2);
      mMonth1Inv number(13,2);
      mMonth2Inv number(13,2);
      mMonth3Inv number(13,2);
      mMonth4Inv number(13,2);
      mMonth5Inv number(13,2);
      mMonth6Inv number(13,2);
      mMonth7Inv number(13,2);
      mMonth8Inv number(13,2);
      mMonth9Inv number(13,2);
      mMonth10Inv number(13,2);
      mMonth11Inv number(13,2);
      mMonth12Inv number(13,2);

      begin
            select rollupid into l_rollupid from uschcode where chargecode=pAsnChCd;
      pSQL := 'select v.AsnChCode,v.InvSeq InvSeq,v.InvTotal InvTotal,'
                  || 'substr(v.ClientID,1,4) ClientID,c.ClntName ClntName,v.InvDate InvDate,'
                  || 'v.arcvInvID arcvInvID from arcvvq_';

      pSQL1 := '(v.invseq<''A'' or v.invseq>''Z'') and v.InvDate between to_date('''
                  || pInvDate1 || ''',''MM/DD/YYYY'') and to_date(''' || pInvDate2
                  || ''',''MM/DD/YYYY'') and   v.clientID = c.clientid'
                  || ' order by v.AsnChCode,substr(v.ClientID,1,4),v.InvSeq';

      if pAsnChCd = 'USTOT' then
         pSQL := pSQL || pView || ' v, client c where ' || pSQL1;
            else            
         pSQL := pSQL || pView || ' v, client c where exists (select 1 from uschcode u'
                        || ' where u.chargecode=v.asnchcode and u.rollupid like ''' || l_rollupid
                        || '%'') and ' || pSQL1;
   end if;


      c1 := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(c1, pSQL, DBMS_SQL.V7);

      DBMS_SQL.DEFINE_COLUMN(c1, 1, v_chCd,6);
   DBMS_SQL.DEFINE_COLUMN(c1, 2, v_invseq,7);
   DBMS_SQL.DEFINE_COLUMN(c1, 3, v_invtotal);
   DBMS_SQL.DEFINE_COLUMN(c1, 4, v_clientid,7);
   DBMS_SQL.DEFINE_COLUMN(c1, 5, v_clntname,35);
   DBMS_SQL.DEFINE_COLUMN(c1, 6, v_invdate);
   DBMS_SQL.DEFINE_COLUMN(c1, 7, v_arcvinvid);

      v_dummy := DBMS_SQL.EXECUTE(c1);
      loop
            if DBMS_SQL.FETCH_ROWS(c1) = 0 then
                  exit;
            end if;

            DBMS_SQL.COLUMN_VALUE(c1,1,v_chCd);
      DBMS_SQL.COLUMN_VALUE(c1,2,v_invseq);
      DBMS_SQL.COLUMN_VALUE(c1,3,v_invtotal);
      DBMS_SQL.COLUMN_VALUE(c1,4,v_clientid);
      DBMS_SQL.COLUMN_VALUE(c1,5,v_clntname);
      DBMS_SQL.COLUMN_VALUE(c1,6,v_invdate);
      DBMS_SQL.COLUMN_VALUE(c1,7,v_arcvinvid);

            select sum(payment) into paymentsum
            from   payment
      where  arcvinvid = v_arcvinvid
            and    paydate<=to_date(pPayDate,'MM/DD/YYYY');
            if paymentsum is null then
         v_balance := v_invTotal;
            else
         v_balance := v_invTotal - paymentsum;
            end if;            

         if v_balance != 0 or (v_InvTotal != 0 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-360) then
                mMonth1Bal := 0;
                mMonth2Bal := 0;
                mMonth3Bal := 0;
                mMonth4Bal := 0;
                mMonth5Bal := 0;
                mMonth6Bal := 0;
                mMonth7Bal := 0;
                mMonth8Bal := 0;
                mMonth9Bal := 0;
                mMonth10Bal := 0;
                mMonth11Bal := 0;
                mMonth12Bal := 0;
                mMonth13Bal := 0;
               mMonth1Inv := 0;
                mMonth2Inv := 0;
                mMonth3Inv := 0;
                mMonth4Inv := 0;
                mMonth5Inv := 0;
                mMonth6Inv := 0;
                mMonth7Inv := 0;
                mMonth8Inv := 0;
                mMonth9Inv := 0;
                mMonth10Inv := 0;
                mMonth11Inv := 0;
                mMonth12Inv := 0;       
         if v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY') and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-30 then
         mMonth1Bal := v_balance;
         mMonth1Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-30 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-60 then
         mMonth2Bal := v_balance;
         mMonth2Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-60 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-90 then
         mMonth3Bal := v_balance;
         mMonth3Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-90 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-120 then
         mMonth4Bal := v_balance;
         mMonth4Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-120 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-150 then
         mMonth5Bal := v_balance;
         mMonth5Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-150 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-180 then
         mMonth6Bal := v_balance;
         mMonth6Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-180 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-210 then
         mMonth7Bal := v_balance;
         mMonth7Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-210 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-240 then
         mMonth8Bal := v_balance;
         mMonth8Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-240 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-270 then
         mMonth9Bal := v_balance;
         mMonth9Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-270 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-300 then
         mMonth10Bal := v_balance;
         mMonth10Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-300 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-330 then
         mMonth11Bal := v_balance;
         mMonth11Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-330 and v_InvDate>TO_Date(pAgeDate,'MM/DD/YYYY')-360 then
         mMonth12Bal := v_balance;
         mMonth12Inv := v_InvTotal;
         elsif v_InvDate<=TO_Date(pAgeDate,'MM/DD/YYYY')-360 then
         mMonth13Bal := v_balance;
               end if;

         InvTotal(i)    := v_balance;
         AsnChCd(i)     := v_chCd;
         ClientID(i)    := v_ClientID;
         ClntName(i)    := v_ClntName;
         InvSeq(i)      := v_InvSeq;
         InvDate(i)     := v_InvDate;
               Month1Bal(i)   := mMonth1Bal;
               Month2Bal(i)   := mMonth2Bal;
               Month3Bal(i)   := mMonth3Bal;
               Month4Bal(i)   := mMonth4Bal;
               Month5Bal(i)   := mMonth5Bal;
               Month6Bal(i)   := mMonth6Bal;
               Month7Bal(i)   := mMonth7Bal;
               Month8Bal(i)   := mMonth8Bal;
               Month9Bal(i)   := mMonth9Bal;
               Month10Bal(i)  := mMonth10Bal;
               Month11Bal(i)  := mMonth11Bal;
               Month12Bal(i)  := mMonth12Bal;
               Month13Bal(i)  := mMonth13Bal;
               Month1Inv(i)   := mMonth1Inv;
               Month2Inv(i)   := mMonth2Inv;
               Month3Inv(i)   := mMonth3Inv;
               Month4Inv(i)   := mMonth4Inv;
               Month5Inv(i)   := mMonth5Inv;
               Month6Inv(i)   := mMonth6Inv;
               Month7Inv(i)   := mMonth7Inv;
               Month8Inv(i)   := mMonth8Inv;
               Month9Inv(i)   := mMonth9Inv;
               Month10Inv(i)  := mMonth10Inv;
               Month11Inv(i)  := mMonth11Inv;
               Month12Inv(i)  := mMonth12Inv;
               i := i+1;
            end if;
          end loop;
            DBMS_SQL.CLOSE_CURSOR(c1);         
      end;

procedure GetSingleRcv(
            pArcvInvID       in  number,
            clientid            out tInvseq,
            invdate            out tInvDate,
            invseq            out tInvseq,
            invtotal            out tBalance,
            orgcode             out torgcode,
            period            out tPeriod,
            prjid                  out tPrjid,
            status            out tStatus,
            balance            out tBalance,
            rcvtype            out tStatus)      
is
      cursor c1 is
      select clientid,to_char(invdate,'MM/DD/YYYY') invdate,invseq,
            to_char(invtotal, '99,999,990.00') invtotal,orgcode,period,prjid,status,
            to_char(balance, '99,999,990.00') balance,rcvtype
      from arcvv
      where arcvinvid=parcvinvid;

      i number default 1;
begin
      for rec in c1
      loop
            clientid(i)            := rec.clientid;
            invdate(i)            := rec.invdate;
            invseq(i)            := rec.invseq;
            invtotal(i)            := rec.invtotal;
            orgcode(i)            := rec.orgcode;
            period(i)            := rec.period;
            prjid(i)                  := rec.prjid;
            status(i)            := rec.status;
            balance(i)            := rec.balance;
            rcvtype(i)            := rec.rcvtype;
            i := i+1;
      end loop;
end;      

procedure GetPayment(
            pArcvInvID      in number,
            payment            out tBalance,
            paydate            out tInvDate,
            paytype            out tStatus,
            paynote            out tMonth)
is
      cursor c1 is select to_char(payment,'99,999,990.00') pay,
                  to_char(paydate,'MM/DD/YYYY') paydate,paytype,paynote
                  /*from payment where arcvinvid=parcvinvid;*/

            from payment where arcvinvid=parcvinvid;

      i number default 1;
begin
      for rec in c1
      loop
            payment(i)      := rec.pay;
            paydate(i)      := rec.paydate;
            paytype(i)      := rec.paytype;
            paynote(i)      := rec.paynote;
            i := i+1;
      end loop;
end;

end;
/


0
Comment
Question by:jvieira
2 Comments
 
LVL 3

Accepted Solution

by:
mathavra earned 200 total points
Comment Utility
Usually, you receive these errors when some of the objects
are not initialized.

For example:
------------

set serveroutput on;

drop table persons;
drop type person_type;

create type person_type as object(
name VARCHAR2(40),
address VARCHAR2(40));
/
show errors;

create table persons of person_type;

insert into persons
values (person_type('John Doe', 'Address1'));

create or replace procedure test as

po1 person_type;

begin

  select px.name, px.address into po1.name, po1.address
  from persons px
  where px.name = 'John Doe';

  dbms_output.put_line('po1.name : '||po1.name);
  dbms_output.put_line('po1.address : '||po1.address);

end;

/
show errors;

SQL> execute test;
begin test; end;

*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "NLIU.TEST", line 15
ORA-06512: at line 1


Solution Description
--------------------
You have to initialize the object using a constructor.

After initializing the object 'p01' as below in the example it worked.

   po1 := person_type(NULL, NULL);


Changed example:
----------------

set serveroutput on;

drop table persons;
drop type person_type;

create type person_type as object(
name VARCHAR2(40),
address VARCHAR2(40));
/
show errors;

create table persons of person_type;

insert into persons
values (person_type('John Doe', 'Address1'));

create or replace procedure test as

po1 person_type;

begin

  po1 := person_type(NULL, NULL);
  select px.name, px.address into po1.name, po1.address
  from persons px
  where px.name = 'John Doe';

  dbms_output.put_line('po1.name : '||po1.name);
  dbms_output.put_line('po1.address : '||po1.address);

end;
/
show errors;

SQL> execute test;
po1.name : John Doe
po1.address : Address1

PL/SQL procedure successfully completed.





After looking at your code briefly (I did not look at your code in detail), you may have to initialize the objects
 colcmnt

and some types
 separately.
0
 

Author Comment

by:jvieira
Comment Utility
I always forget to grade these things.  Thanks

Joe
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now