Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00371: at most one declaration for 'V_MSG' is permitted
PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240522] Fri, 25 May 2007 02:54 Go to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
Hi there,

below is my coding structure and i am not able to rectify the problem. can someone help me.

the error message that i recieve is : 'PLS-00371: at most one declaration for 'V_MSG' is permitted'.

/*=========================================================================
  Program ID    : TMIN0870 TIM DTS Receiving Lot by Vendor
  Created Date  : 
  Created By    :
  Purpose       :

  Parameter     : Rcv Date From
                  Rcv Date To 
                  Vendor No 
                  Item ID

  ==============================================================================*/
  SET SERVEROUTPUT ON FORMAT WRAPPED
  CREATE OR REPLACE PROCEDURE TMIN0870( 
  ERRBUF           OUT VARCHAR2,
  RETCODE          OUT VARCHAR2,
  PRA_RCV_FROM     IN  DATE,
  PRA_RCV_TO       IN  DATE,
  PRA_VEND_NO      IN  VARCHAR2,
  PRA_ITEM_NO      IN  VARCHAR2
  ) IS

   CURSOR C01
  IS   
     
       SELECT RCV.VEND_NO,
              RCV.VENDOR_NAME,
              RCV.ITEM_NO,
              SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION, 
              RCV.CATEGORY ,
              COUNT(*)REC_LOT
       FROM ( SELECT RT.SHIPMENT_HEADER_ID,
                     MSI.SEGMENT1 ITEM_NO, 
                     MSI.DESCRIPTION,
                     PV.SEGMENT1 VEND_NO,
                     PV.VENDOR_NAME, 
                     NVL(B.CATEGORY,'DTS') CATEGORY
              FROM   RCV_TRANSACTIONS RT, 
                     PO_LINES_ALL PLA, 
                     PO_VENDORS PV, 
                     MTL_SYSTEM_ITEMS_B MSI,
            ( SELECT SUM(QUANTITY)QTY,
                     PARENT_TRANSACTION_ID
               FROM  RCV_TRANSACTIONS
              WHERE  TRANSACTION_TYPE = 'CORRECT'
            GROUP BY PARENT_TRANSACTION_ID) A,
            ( SELECT MIC.INVENTORY_ITEM_ID,
              DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
              FROM   MTL_ITEM_CATEGORIES MIC
              WHERE  MIC.CATEGORY_SET_ID IN (243,183)
              UNION 
              SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
              FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B

       WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
         AND RT.VENDOR_ID = PV.VENDOR_ID
         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
         AND RT.TRANSACTION_TYPE = 'RECEIVE'
         AND RT.ROUTING_HEADER_ID = 3
         AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO
         AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
         AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
         AND RT.QUANTITY + NVL(A.QTY,0) > 0
    GROUP BY RT.SHIPMENT_HEADER_ID,
             MSI.SEGMENT1, 
             MSI.DESCRIPTION,
             PV.SEGMENT1,
             PV.VENDOR_NAME, 
             NVL(B.CATEGORY,'DTS')) RCV
    GROUP BY RCV.VEND_NO,
             RCV.VENDOR_NAME, 
             RCV.ITEM_NO, 
             RCV.DESCRIPTION,
             RCV.CATEGORY
    ORDER BY RCV.VEND_NO,
             RCV.ITEM_NO

   ;/* END C01 */

 
  CURSOR C02
  IS
    
       SELECT RCV.VEND_NO, 
              RCV.VENDOR_NAME,
              RCV.ITEM_NO,
              SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
              RCV.CATEGORY,
              COUNT(*)REC_LOT 
       FROM ( SELECT RT.SHIPMENT_HEADER_ID,
                     MSI.SEGMENT1 ITEM_NO, 
                     MSI.DESCRIPTION,
                     PV.SEGMENT1 VEND_NO,
                     PV.VENDOR_NAME, 
                     NVL(B.CATEGORY,'DTS') CATEGORY
              FROM   RCV_TRANSACTIONS RT, 
                     PO_LINES_ALL PLA, 
                     PO_VENDORS PV, 
                     MTL_SYSTEM_ITEMS_B MSI,
            ( SELECT SUM(QUANTITY)QTY,
                     PARENT_TRANSACTION_ID
               FROM  RCV_TRANSACTIONS
              WHERE  TRANSACTION_TYPE = 'CORRECT'
            GROUP BY PARENT_TRANSACTION_ID) A,
            ( SELECT MIC.INVENTORY_ITEM_ID,
              DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
              FROM   MTL_ITEM_CATEGORIES MIC
              WHERE  MIC.CATEGORY_SET_ID IN (243,183)
              UNION 
              SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
              FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B

       WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
         AND RT.VENDOR_ID = PV.VENDOR_ID
         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
         AND RT.TRANSACTION_TYPE = 'RECEIVE'
         AND RT.ROUTING_HEADER_ID = 3  
         AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO
         AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
         AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
         AND RT.QUANTITY + NVL(A.QTY,0) > 0
         AND PV.VENDOR_ID = PRA_VEND_NO 
    GROUP BY RT.SHIPMENT_HEADER_ID,
             MSI.SEGMENT1, 
             MSI.DESCRIPTION,
             PV.SEGMENT1,
             PV.VENDOR_NAME, 
             NVL(B.CATEGORY,'DTS')) RCV
    GROUP BY RCV.VEND_NO,
             RCV.VENDOR_NAME,
             RCV.ITEM_NO,
             RCV.DESCRIPTION,
             RCV.CATEGORY         
    ORDER BY RCV.VEND_NO,
             RCV.ITEM_NO


   ; /* END C02 */       


  CURSOR C03
  IS
 
       SELECT RCV.VEND_NO,
              RCV.VENDOR_NAME,
              RCV.ITEM_NO,
              SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
              RCV.CATEGORY,
              COUNT(*)REC_LOT 
       FROM ( SELECT RT.SHIPMENT_HEADER_ID,
                     MSI.SEGMENT1 ITEM_NO, 
                     MSI.DESCRIPTION,
                     PV.SEGMENT1 VEND_NO,
                     PV.VENDOR_NAME, 
                     NVL(B.CATEGORY,'DTS') CATEGORY
              FROM   RCV_TRANSACTIONS RT, 
                     PO_LINES_ALL PLA, 
                     PO_VENDORS PV, 
                     MTL_SYSTEM_ITEMS_B MSI,
            ( SELECT SUM(QUANTITY)QTY,
                     PARENT_TRANSACTION_ID
               FROM  RCV_TRANSACTIONS
              WHERE  TRANSACTION_TYPE = 'CORRECT'
            GROUP BY PARENT_TRANSACTION_ID) A,
            ( SELECT MIC.INVENTORY_ITEM_ID,
              DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
              FROM   MTL_ITEM_CATEGORIES MIC
              WHERE  MIC.CATEGORY_SET_ID IN (243,183)
              UNION 
              SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
              FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B
       
       WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
         AND RT.VENDOR_ID = PV.VENDOR_ID
         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
         AND RT.TRANSACTION_TYPE = 'RECEIVE'
         AND RT.ROUTING_HEADER_ID = 3 
         AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO 
         AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
         AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
         AND RT.QUANTITY + NVL(A.QTY,0) > 0
         AND MSI.INVENTORY_ITEM_ID = PRA_ITEM_NO 
    GROUP BY RT.SHIPMENT_HEADER_ID,
             MSI.SEGMENT1, 
             MSI.DESCRIPTION,
             PV.SEGMENT1,
             PV.VENDOR_NAME, 
             NVL(B.CATEGORY,'DTS')) RCV
    GROUP BY RCV.VEND_NO,
             RCV.VENDOR_NAME,
             RCV.ITEM_NO,
             RCV.DESCRIPTION,
             RCV.CATEGORY         
    ORDER BY RCV.VEND_NO,
             RCV.ITEM_NO


   ;/* END C03*/


  CURSOR C04 
  IS
 
       SELECT RCV.VEND_NO,
              RCV.VENDOR_NAME,
              RCV.ITEM_NO,
              SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
              RCV.CATEGORY,
              COUNT(*)REC_LOT 
       FROM ( SELECT RT.SHIPMENT_HEADER_ID,
                     MSI.SEGMENT1 ITEM_NO, 
                     MSI.DESCRIPTION,
                     PV.SEGMENT1 VEND_NO,
                     PV.VENDOR_NAME, 
                     NVL(B.CATEGORY,'DTS') CATEGORY
              FROM   RCV_TRANSACTIONS RT, 
                     PO_LINES_ALL PLA, 
                     PO_VENDORS PV, 
                     MTL_SYSTEM_ITEMS_B MSI,
            ( SELECT SUM(QUANTITY)QTY,
                     PARENT_TRANSACTION_ID
               FROM  RCV_TRANSACTIONS
              WHERE  TRANSACTION_TYPE = 'CORRECT'
            GROUP BY PARENT_TRANSACTION_ID) A,
            ( SELECT MIC.INVENTORY_ITEM_ID,
              DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
              FROM   MTL_ITEM_CATEGORIES MIC
              WHERE  MIC.CATEGORY_SET_ID IN (243,183)
              UNION 
              SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
              FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B
      
       WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
         AND RT.VENDOR_ID = PV.VENDOR_ID
         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
         AND RT.TRANSACTION_TYPE = 'RECEIVE'
         AND RT.ROUTING_HEADER_ID = 3 
         AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO 
         AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
         AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
         AND RT.QUANTITY + NVL(A.QTY,0) > 0
         AND PV.VENDOR_ID = PRA_VEND_NO 
         AND MSI.INVENTORY_ITEM_ID = PRA_ITEM_NO
    GROUP BY RT.SHIPMENT_HEADER_ID,
             MSI.SEGMENT1, 
             MSI.DESCRIPTION,
             PV.SEGMENT1,
             PV.VENDOR_NAME, 
             NVL(B.CATEGORY,'DTS')) RCV
    GROUP BY RCV.VEND_NO,
             RCV.VENDOR_NAME,
             RCV.ITEM_NO,
             RCV.DESCRIPTION,
             RCV.CATEGORY         
    ORDER BY RCV.VEND_NO,
             RCV.ITEM_NO


   ;/* END C04 */ 



   TYPE REC_VEND_NO_TYPE IS TABLE OF PO_VENDORS.SEGMENT1%TYPE
        INDEX BY BINARY_INTEGER;
   REC_VEND_NO REC_VEND_NO_TYPE;  

   TYPE REC_VENDOR_NAME_TYPE IS TABLE OF PO_VENDORS.VENDOR_NAME%TYPE
        INDEX BY BINARY_INTEGER;
   REC_VENDOR_NAME REC_VENDOR_NAME_TYPE; 

   TYPE REC_ITEM_NO_TYPE IS TABLE OF MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE
        INDEX BY BINARY_INTEGER;
   REC_ITEM_NO REC_ITEM_NO_TYPE;

   TYPE REC_DESC_TYPE IS TABLE OF MTL_SYSTEM_ITEMS_B.DESCRIPTION%TYPE
        INDEX BY BINARY_INTEGER;
   REC_DESC REC_DESC_TYPE;

   TYPE REC_CATEGORY_TYPE IS TABLE OF VARCHAR2(6) 
        INDEX BY BINARY_INTEGER;
   REC_CATEGORY REC_CATEGORY_TYPE; 

   TYPE REC_COUNT_TYPE IS TABLE OF NUMBER
        INDEX BY BINARY_INTEGER;
   REC_COUNT REC_COUNT_TYPE; 
       


   V_ORG_NAME       ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
   V_VEND_NO        PO_VENDORS.SEGMENT1%TYPE; 
   V_VENDOR_NAME    PO_VENDORS.VENDOR_NAME%TYPE;
   V_ITEM_NO        MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
   V_DESC           MTL_SYSTEM_ITEMS_B.DESCRIPTION%TYPE;
   V_CATEGORY       VARCHAR2(6);
   V_COUNT          NUMBER;

   V_MSG                VARCHAR2(241);
   MAX_LINE             BINARY_INTEGER DEFAULT 0;
   PAGE_LINE            CONSTANT NUMBER := 44;
   l                    BINARY_INTEGER DEFAULT 0;
   STS      NUMBER DEFAULT 0;
   CNC_RET   BOOLEAN;
   STS_ERROR   EXCEPTION;
   ERR_RET   VARCHAR2(240);
   OK_RET   VARCHAR2(240);
   V_MSG                VARCHAR2(241);
   V_ERR_MSG            VARCHAR2(1000); 


/*=========================================================
   PRINT_HEADER
==========================================================*/

   PROCEDURE PRINT_HEADER (PAGE_CNT IN NUMBER) IS 

BEGIN


         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(V_ORG_NAME,23) || RPAD(' ',102) || 'Report Date : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI')); 
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Report ID : TMIN0870' || RPAD(' ',105) || 'Page       : ' || RPAD(TO_CHAR(PAGE_CNT),4)); 
 
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');

         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(' ',50) || 'TIM DTS Receiving Lot by Vendor');
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,LPAD('Ln',6) ||' '||RPAD('Vendor No',12)||' '||RPAD('Vendor Name',42)||' '||RPAD('Item No',12)||' '||RPAD('Description',60)||' '||RPAD('Category',8)||' '||RPAD('Rec Lot',4));
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'------------ ------------------------------------------ ------------ ------------------------------------------------------------ -------- ----'); 

END PRINT_HEADER;


/*==============================================
             DISPLAY_REPORT
 =============================================*/

FUNCTION DISPLAY_REPORT
(
              P_MAX_LINE   IN NUMBER,
              P_ERR_MSG    OUT VARCHAR2   
)
 RETURN NUMBER IS
              LINE_CNT NUMBER DEFAULT 0;
              PAGE_CNT NUMBER DEFAULT 0;

 BEGIN
              FOR IDX IN 0..P_MAX_LINE
 LOOP
       IF LINE_CNT = 0 THEN
              PAGE_CNT := PAGE_CNT + 1;
              PRINT_HEADER(PAGE_CNT);
              LINE_CNT := 0;
       END IF;   

   
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,LPAD(TO_CHAR(IDX+1),6) ||' '||
         RPAD(REC_VEND_NO(IDX),12)||' '|| RPAD(REC_VENDOR_NAME(IDX),42)
         ||' '||RPAD(REC_ITEM_NO(IDX),12)||' '||RPAD(REC_DESC(IDX),60)
         ||' '||RPAD(' ',8)||REC_CATEGORY(IDX)||' '||REC_COUNT(IDX));

              LINE_CNT := LINE_CNT + 1;
 
      IF LINE_CNT >= PAGE_LINE THEN
              LINE_CNT := 0;
              FND_FILE.PUT(FND_FILE.OUTPUT,CHR(12));
      END IF;

 END LOOP;

      IF LINE_CNT >=  PAGE_LINE THEN 
              LINE_CNT := 0;
              FND_FILE.PUT(FND_FILE.OUTPUT,CHR(12));
              PAGE_CNT := PAGE_CNT + 1;
              PRINT_HEADER(PAGE_CNT);
      END IF;
  
              FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
              FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(' ',60) ||
              '********** END OF REPORT **********');
 
      RETURN OK_RET;

 END DISPLAY_REPORT;


/*=================================================
       MAIN
=================================================*/

 BEGIN

         SELECT ORGANIZATION_NAME
         INTO V_ORG_NAME
         FROM ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_ID = 21;


         IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NULL THEN
             V_MSG := 'OPEN C01';
             OPEN C01;
             V_MSG := 'FETCH C01';
 LOOP  
        
         FETCH C01 INTO 
        V_VEND_NO,
      V_VENDOR_NAME,
      V_ITEM_NO,
      V_DESC,
      V_CATEGORY,
      V_COUNT;  
         EXIT WHEN C01%NOTFOUND;

         l := l + 1; 

 END LOOP;
 
             V_MSG := 'CLOSE C01';
             CLOSE C01;

         ELSIF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NULL THEN 
             V_MSG := 'OPEN C02';
             OPEN C02;
             V_MSG := 'FETCH C01';
 LOOP 

         FETCH C02 INTO
             V_VEND_NO,
             V_VENDOR_NAME,
             V_ITEM_NO,
             V_DESC,
             V_CATEGORY,
             V_COUNT;
         EXIT WHEN C02%NOTFOUND;

          l := l + 1;       

 END LOOP;
             V_MSG := 'CLOSE C02';
             CLOSE C02;
   
         ELSIF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NOT NULL THEN 
             V_MSG := 'OPEN C03';
             OPEN C03;
             V_MSG := 'FETCH C03';
       
 LOOP  
         FETCH C03 INTO 
             V_VEND_NO,
             V_VENDOR_NAME,
             V_ITEM_NO,
             V_DESC,
             V_CATEGORY,
             V_COUNT;
         EXIT WHEN C03%NOTFOUND;

           l := l + 1;
  
 END LOOP;
             V_MSG := 'CLOSE C03';
             CLOSE C03;

         ELSIF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NOT NULL THEN 
             V_MSG := 'OPEN C04';
             OPEN C04;
             V_MSG := 'FETCH C04';
     
 LOOP   
         FETCH C04 INTO
             V_VEND_NO,
             V_VENDOR_NAME,
             V_ITEM_NO,
             V_DESC,
             V_CATEGORY,
             V_COUNT;
         EXIT WHEN C04%NOTFOUND;
 
          l := l + 1;
 
 END LOOP;
             V_MSG := 'CLOSE C04';
             CLOSE C04;

          

 END IF;

   
         MAX_LINE := l - 1;

             FND_FILE.PUT_LINE(FND_FILE.LOG,' MAX_LINE: '||MAX_LINE);   

             STS := DISPLAY_REPORT(MAX_LINE,DISPLAY_REPORT,V_MSG);
  
         IF STS = ERR_RET THEN RAISE STS_ERROR;
   
         END IF;

             CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',NULL);
             /* FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'));*/ 


         EXCEPTION
             WHEN STS_ERROR THEN
                  IF C01%ISOPEN THEN CLOSE C01;
                  END IF;
                  IF C02%ISOPEN THEN CLOSE C02;
                  END IF;
                  IF C03%ISOPEN THEN CLOSE C03;
                  END IF;
                  IF C04%ISOPEN THEN CLOSE C04; 
                  END IF; 

             FND_FILE.PUT_LINE(FND_FILE.LOG,'STS_ERROR :'||V_MSG);
        
         IF SQLCODE != 0 THEN
             FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLCODE : '||TO_CHAR(SQLCODE));
             FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLERRM : '||SQLERRM);
         END IF;

             FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------');
             FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-2');
             CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);

           WHEN OTHERS THEN
                IF C01%ISOPEN THEN CLOSE C01;
                END IF;
                IF C02%ISOPEN THEN CLOSE C02;
                END IF;
                IF C03%ISOPEN THEN CLOSE C03;
                END IF;
                IF C04%ISOPEN THEN CLOSE C04;
                END IF;

             FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-1 : '||V_MSG);
         IF SQLCODE != 0 THEN
             FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLCODE : '||TO_CHAR(SQLCODE));
             FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLERRM : '||SQLERRM);
         END IF;

             FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------');
             FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-2');
             CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);
  
END;
/
show error

Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240527 is a reply to message #240522] Fri, 25 May 2007 03:05 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

You have declare v_msg variable 2 times.
May be that's the reason?

   V_MSG                VARCHAR2(241);
   MAX_LINE             BINARY_INTEGER DEFAULT 0;
   PAGE_LINE            CONSTANT NUMBER := 44;
   l                    BINARY_INTEGER DEFAULT 0;
   STS      NUMBER DEFAULT 0;
   CNC_RET   BOOLEAN;
   STS_ERROR   EXCEPTION;
   ERR_RET   VARCHAR2(240);
   OK_RET   VARCHAR2(240);
   V_MSG                VARCHAR2(241);
   V_ERR_MSG            VARCHAR2(1000); 
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240530 is a reply to message #240522] Fri, 25 May 2007 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You declare twice the same variable in the same scope.
Next time, post execution and line numbers. We are not PL/SQL syntax analyzer.

Regards
Michel
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240538 is a reply to message #240522] Fri, 25 May 2007 03:19 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
Thank you very much..it was a silly mistake of mine...
by the way i have made somechanges onto the coding but then i am getting the simplest error and my eyes is aching.....i hope to make this my final request to you guys to help me...

the error that occurs is :
LINE/COL ERROR
-------- -----------------------------------------------------------------
472/9 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
if
what could be the error..i have broke the if..else...end if comments invidually and yest it would strike me like this!!!!..please advise me

Quote:

/*=========================================================================
  Program ID    : TMIN0870 TIM DTS Receiving Lot by Vendor
  Created Date  : 
  Created By    :
  Purpose       :

  Parameter     : Rcv Date From
                  Rcv Date To 
                  Vendor No 
                  Item ID

  ==============================================================================*/
  SET SERVEROUTPUT ON FORMAT WRAPPED
  CREATE OR REPLACE PROCEDURE TMIN0870( 
  ERRBUF           OUT VARCHAR2,
  RETCODE          OUT VARCHAR2,
  PRA_RCV_FROM     IN  DATE,
  PRA_RCV_TO       IN  DATE,
  PRA_VEND_NO      IN  VARCHAR2,
  PRA_ITEM_NO      IN  VARCHAR2
  ) IS

   CURSOR C01
  IS   
     
       SELECT RCV.VEND_NO,
              RCV.VENDOR_NAME,
              RCV.ITEM_NO,
              SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION, 
              RCV.CATEGORY ,
              COUNT(*)REC_LOT
       FROM ( SELECT RT.SHIPMENT_HEADER_ID,
                     MSI.SEGMENT1 ITEM_NO, 
                     MSI.DESCRIPTION,
                     PV.SEGMENT1 VEND_NO,
                     PV.VENDOR_NAME, 
                     NVL(B.CATEGORY,'DTS') CATEGORY
              FROM   RCV_TRANSACTIONS RT, 
                     PO_LINES_ALL PLA, 
                     PO_VENDORS PV, 
                     MTL_SYSTEM_ITEMS_B MSI,
            ( SELECT SUM(QUANTITY)QTY,
                     PARENT_TRANSACTION_ID
               FROM  RCV_TRANSACTIONS
              WHERE  TRANSACTION_TYPE = 'CORRECT'
            GROUP BY PARENT_TRANSACTION_ID) A,
            ( SELECT MIC.INVENTORY_ITEM_ID,
              DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
              FROM   MTL_ITEM_CATEGORIES MIC
              WHERE  MIC.CATEGORY_SET_ID IN (243,183)
              UNION 
              SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
              FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B

       WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
         AND RT.VENDOR_ID = PV.VENDOR_ID
         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
         AND RT.TRANSACTION_TYPE = 'RECEIVE'
         AND RT.ROUTING_HEADER_ID = 3
         AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO
         AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
         AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
         AND RT.QUANTITY + NVL(A.QTY,0) > 0
    GROUP BY RT.SHIPMENT_HEADER_ID,
             MSI.SEGMENT1, 
             MSI.DESCRIPTION,
             PV.SEGMENT1,
             PV.VENDOR_NAME, 
             NVL(B.CATEGORY,'DTS')) RCV
    GROUP BY RCV.VEND_NO,
             RCV.VENDOR_NAME, 
             RCV.ITEM_NO, 
             RCV.DESCRIPTION,
             RCV.CATEGORY
    ORDER BY RCV.VEND_NO,
             RCV.ITEM_NO

   ;/* END C01 */

 
  CURSOR C02
  IS
    
       SELECT RCV.VEND_NO, 
              RCV.VENDOR_NAME,
              RCV.ITEM_NO,
              SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
              RCV.CATEGORY,
              COUNT(*)REC_LOT 
       FROM ( SELECT RT.SHIPMENT_HEADER_ID,
                     MSI.SEGMENT1 ITEM_NO, 
                     MSI.DESCRIPTION,
                     PV.SEGMENT1 VEND_NO,
                     PV.VENDOR_NAME, 
                     NVL(B.CATEGORY,'DTS') CATEGORY
              FROM   RCV_TRANSACTIONS RT, 
                     PO_LINES_ALL PLA, 
                     PO_VENDORS PV, 
                     MTL_SYSTEM_ITEMS_B MSI,
            ( SELECT SUM(QUANTITY)QTY,
                     PARENT_TRANSACTION_ID
               FROM  RCV_TRANSACTIONS
              WHERE  TRANSACTION_TYPE = 'CORRECT'
            GROUP BY PARENT_TRANSACTION_ID) A,
            ( SELECT MIC.INVENTORY_ITEM_ID,
              DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
              FROM   MTL_ITEM_CATEGORIES MIC
              WHERE  MIC.CATEGORY_SET_ID IN (243,183)
              UNION 
              SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
              FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B

       WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
         AND RT.VENDOR_ID = PV.VENDOR_ID
         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
         AND RT.TRANSACTION_TYPE = 'RECEIVE'
         AND RT.ROUTING_HEADER_ID = 3  
         AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO
         AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
         AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
         AND RT.QUANTITY + NVL(A.QTY,0) > 0
         AND PV.VENDOR_ID = PRA_VEND_NO 
    GROUP BY RT.SHIPMENT_HEADER_ID,
             MSI.SEGMENT1, 
             MSI.DESCRIPTION,
             PV.SEGMENT1,
             PV.VENDOR_NAME, 
             NVL(B.CATEGORY,'DTS')) RCV
    GROUP BY RCV.VEND_NO,
             RCV.VENDOR_NAME,
             RCV.ITEM_NO,
             RCV.DESCRIPTION,
             RCV.CATEGORY         
    ORDER BY RCV.VEND_NO,
             RCV.ITEM_NO


   ; /* END C02 */       


  CURSOR C03
  IS
 
       SELECT RCV.VEND_NO,
              RCV.VENDOR_NAME,
              RCV.ITEM_NO,
              SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
              RCV.CATEGORY,
              COUNT(*)REC_LOT 
       FROM ( SELECT RT.SHIPMENT_HEADER_ID,
                     MSI.SEGMENT1 ITEM_NO, 
                     MSI.DESCRIPTION,
                     PV.SEGMENT1 VEND_NO,
                     PV.VENDOR_NAME, 
                     NVL(B.CATEGORY,'DTS') CATEGORY
              FROM   RCV_TRANSACTIONS RT, 
                     PO_LINES_ALL PLA, 
                     PO_VENDORS PV, 
                     MTL_SYSTEM_ITEMS_B MSI,
            ( SELECT SUM(QUANTITY)QTY,
                     PARENT_TRANSACTION_ID
               FROM  RCV_TRANSACTIONS
              WHERE  TRANSACTION_TYPE = 'CORRECT'
            GROUP BY PARENT_TRANSACTION_ID) A,
            ( SELECT MIC.INVENTORY_ITEM_ID,
              DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
              FROM   MTL_ITEM_CATEGORIES MIC
              WHERE  MIC.CATEGORY_SET_ID IN (243,183)
              UNION 
              SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
              FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B
       
       WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
         AND RT.VENDOR_ID = PV.VENDOR_ID
         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
         AND RT.TRANSACTION_TYPE = 'RECEIVE'
         AND RT.ROUTING_HEADER_ID = 3 
         AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO 
         AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
         AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
         AND RT.QUANTITY + NVL(A.QTY,0) > 0
         AND MSI.INVENTORY_ITEM_ID = PRA_ITEM_NO 
    GROUP BY RT.SHIPMENT_HEADER_ID,
             MSI.SEGMENT1, 
             MSI.DESCRIPTION,
             PV.SEGMENT1,
             PV.VENDOR_NAME, 
             NVL(B.CATEGORY,'DTS')) RCV
    GROUP BY RCV.VEND_NO,
             RCV.VENDOR_NAME,
             RCV.ITEM_NO,
             RCV.DESCRIPTION,
             RCV.CATEGORY         
    ORDER BY RCV.VEND_NO,
             RCV.ITEM_NO


   ;/* END C03*/


  CURSOR C04 
  IS
 
       SELECT RCV.VEND_NO,
              RCV.VENDOR_NAME,
              RCV.ITEM_NO,
              SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
              RCV.CATEGORY,
              COUNT(*)REC_LOT 
       FROM ( SELECT RT.SHIPMENT_HEADER_ID,
                     MSI.SEGMENT1 ITEM_NO, 
                     MSI.DESCRIPTION,
                     PV.SEGMENT1 VEND_NO,
                     PV.VENDOR_NAME, 
                     NVL(B.CATEGORY,'DTS') CATEGORY
              FROM   RCV_TRANSACTIONS RT, 
                     PO_LINES_ALL PLA, 
                     PO_VENDORS PV, 
                     MTL_SYSTEM_ITEMS_B MSI,
            ( SELECT SUM(QUANTITY)QTY,
                     PARENT_TRANSACTION_ID
               FROM  RCV_TRANSACTIONS
              WHERE  TRANSACTION_TYPE = 'CORRECT'
            GROUP BY PARENT_TRANSACTION_ID) A,
            ( SELECT MIC.INVENTORY_ITEM_ID,
              DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
              FROM   MTL_ITEM_CATEGORIES MIC
              WHERE  MIC.CATEGORY_SET_ID IN (243,183)
              UNION 
              SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
              FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B
      
       WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
         AND RT.VENDOR_ID = PV.VENDOR_ID
         AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
         AND RT.TRANSACTION_TYPE = 'RECEIVE'
         AND RT.ROUTING_HEADER_ID = 3 
         AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO 
         AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
         AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
         AND RT.QUANTITY + NVL(A.QTY,0) > 0
         AND PV.VENDOR_ID = PRA_VEND_NO 
         AND MSI.INVENTORY_ITEM_ID = PRA_ITEM_NO
    GROUP BY RT.SHIPMENT_HEADER_ID,
             MSI.SEGMENT1, 
             MSI.DESCRIPTION,
             PV.SEGMENT1,
             PV.VENDOR_NAME, 
             NVL(B.CATEGORY,'DTS')) RCV
    GROUP BY RCV.VEND_NO,
             RCV.VENDOR_NAME,
             RCV.ITEM_NO,
             RCV.DESCRIPTION,
             RCV.CATEGORY         
    ORDER BY RCV.VEND_NO,
             RCV.ITEM_NO


   ;/* END C04 */ 



   TYPE REC_VEND_NO_TYPE IS TABLE OF PO_VENDORS.SEGMENT1%TYPE
        INDEX BY BINARY_INTEGER;
   REC_VEND_NO REC_VEND_NO_TYPE;  

   TYPE REC_VENDOR_NAME_TYPE IS TABLE OF PO_VENDORS.VENDOR_NAME%TYPE
        INDEX BY BINARY_INTEGER;
   REC_VENDOR_NAME REC_VENDOR_NAME_TYPE; 

   TYPE REC_ITEM_NO_TYPE IS TABLE OF MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE
        INDEX BY BINARY_INTEGER;
   REC_ITEM_NO REC_ITEM_NO_TYPE;

   TYPE REC_DESC_TYPE IS TABLE OF MTL_SYSTEM_ITEMS_B.DESCRIPTION%TYPE
        INDEX BY BINARY_INTEGER;
   REC_DESC REC_DESC_TYPE;

   TYPE REC_CATEGORY_TYPE IS TABLE OF VARCHAR2(6) 
        INDEX BY BINARY_INTEGER;
   REC_CATEGORY REC_CATEGORY_TYPE; 

   TYPE REC_COUNT_TYPE IS TABLE OF NUMBER
        INDEX BY BINARY_INTEGER;
   REC_COUNT REC_COUNT_TYPE; 
       


   V_ORG_NAME       ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
   V_VEND_NO        PO_VENDORS.SEGMENT1%TYPE; 
   V_VENDOR_NAME    PO_VENDORS.VENDOR_NAME%TYPE;
   V_ITEM_NO        MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
   V_DESC           MTL_SYSTEM_ITEMS_B.DESCRIPTION%TYPE;
   V_CATEGORY       VARCHAR2(6);
   V_COUNT          NUMBER;

   V_MSG                VARCHAR2(241);
   MAX_LINE             BINARY_INTEGER DEFAULT 0;
   PAGE_LINE            CONSTANT NUMBER := 44;
   l                    BINARY_INTEGER DEFAULT 0;
   STS      NUMBER DEFAULT 0;
   CNC_RET   BOOLEAN;
   STS_ERROR   EXCEPTION;
   ERR_RET   VARCHAR2(240);
   OK_RET   VARCHAR2(240);
   V_ERR_MSG            VARCHAR2(1000); 


/*=========================================================
   PRINT_HEADER
==========================================================*/

   PROCEDURE PRINT_HEADER (PAGE_CNT IN NUMBER) IS 

BEGIN


         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(V_ORG_NAME,23) || RPAD(' ',102) || 'Report Date : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI')); 
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Report ID : TMIN0870' || RPAD(' ',105) || 'Page       : ' || RPAD(TO_CHAR(PAGE_CNT),4)); 
 
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');

         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(' ',50) || 'TIM DTS Receiving Lot by Vendor');
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,LPAD('Ln',6) ||' '||RPAD('Vendor No',12)||' '||RPAD('Vendor Name',42)||' '||RPAD('Item No',12)||' '||RPAD('Description',60)||' '||RPAD('Category',8)||' '||RPAD('Rec Lot',4));
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'------------ ------------------------------------------ ------------ ------------------------------------------------------------ -------- ----'); 

END PRINT_HEADER;


/*==============================================
             DISPLAY_REPORT
 =============================================*/

FUNCTION DISPLAY_REPORT
(
              P_MAX_LINE   IN NUMBER,
              P_ERR_MSG    OUT VARCHAR2   
)
 RETURN NUMBER IS
              LINE_CNT NUMBER DEFAULT 0;
              PAGE_CNT NUMBER DEFAULT 0;

 BEGIN
              FOR IDX IN 0..P_MAX_LINE
 LOOP
       IF LINE_CNT = 0 THEN
              PAGE_CNT := PAGE_CNT + 1;
              PRINT_HEADER(PAGE_CNT);
              LINE_CNT := 0;
       END IF;   

   
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,LPAD(TO_CHAR(IDX+1),6) ||' '||
         RPAD(REC_VEND_NO(IDX),12)||' '|| RPAD(REC_VENDOR_NAME(IDX),42)
         ||' '||RPAD(REC_ITEM_NO(IDX),12)||' '||RPAD(REC_DESC(IDX),60)
         ||' '||RPAD(' ',8)||REC_CATEGORY(IDX)||' '||REC_COUNT(IDX));

              LINE_CNT := LINE_CNT + 1;
 
      IF LINE_CNT >= PAGE_LINE THEN
              LINE_CNT := 0;
              FND_FILE.PUT(FND_FILE.OUTPUT,CHR(12));
      END IF;

 END LOOP;

      IF LINE_CNT >=  PAGE_LINE THEN 
              LINE_CNT := 0;
              FND_FILE.PUT(FND_FILE.OUTPUT,CHR(12));
              PAGE_CNT := PAGE_CNT + 1;
              PRINT_HEADER(PAGE_CNT);
      END IF;
  
              FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
              FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(' ',60) ||
              '********** END OF REPORT **********');
 
      RETURN OK_RET;

 END DISPLAY_REPORT;


/*=================================================
       MAIN
=================================================*/

 BEGIN

         SELECT ORGANIZATION_NAME
         INTO V_ORG_NAME
         FROM ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_ID = 21;


         IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NULL THEN
             V_MSG := 'OPEN C01';
             OPEN C01;
             V_MSG := 'FETCH C01';
 LOOP  
        
         FETCH C01 INTO 
        V_VEND_NO,
      V_VENDOR_NAME,
      V_ITEM_NO,
      V_DESC,
      V_CATEGORY,
      V_COUNT;  
         EXIT WHEN C01%NOTFOUND;

         l := l + 1; 

 END LOOP;
 
             V_MSG := 'CLOSE C01';
             CLOSE C01;
END IF;
 
         IF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NULL THEN 

             V_MSG := 'OPEN C02';
             OPEN C02;
             V_MSG := 'FETCH C01';
 LOOP 

         FETCH C02 INTO
             V_VEND_NO,
             V_VENDOR_NAME,
             V_ITEM_NO,
             V_DESC,
             V_CATEGORY,
             V_COUNT;
         EXIT WHEN C02%NOTFOUND;

          l := l + 1;       

 END LOOP;
             V_MSG := 'CLOSE C02';
             CLOSE C02;

END IF;   
        
  IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NOT NULL THEN 
             V_MSG := 'OPEN C03';
             OPEN C03;
             V_MSG := 'FETCH C03';
       
 LOOP  
         FETCH C03 INTO 
             V_VEND_NO,
             V_VENDOR_NAME,
             V_ITEM_NO,
             V_DESC,
             V_CATEGORY,
             V_COUNT;
         EXIT WHEN C03%NOTFOUND;

           l := l + 1;
  
 END LOOP;
             V_MSG := 'CLOSE C03';
             CLOSE C03;
END IF;
         IF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NOT NULL THEN 
             V_MSG := 'OPEN C04';
             OPEN C04;
             V_MSG := 'FETCH C04';
     
 LOOP   
         FETCH C04 INTO
             V_VEND_NO,
             V_VENDOR_NAME,
             V_ITEM_NO,
             V_DESC,
             V_CATEGORY,
             V_COUNT;
         EXIT WHEN C04%NOTFOUND;
 
          l := l + 1;
 
 END LOOP;
             V_MSG := 'CLOSE C04';
             CLOSE C04;

     END;     

 END IF;

   
         MAX_LINE := l - 1;

             FND_FILE.PUT_LINE(FND_FILE.LOG,' MAX_LINE: '||MAX_LINE);   

             STS := DISPLAY_REPORT(MAX_LINE,DISPLAY_REPORT,V_MSG);
  
         IF STS = ERR_RET THEN RAISE STS_ERROR;
   
         END IF;

             CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',NULL);
             /* FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'));*/ 


         EXCEPTION
             WHEN STS_ERROR THEN
                  IF C01%ISOPEN THEN CLOSE C01;
                  END IF;
                  IF C02%ISOPEN THEN CLOSE C02;
                  END IF;
                  IF C03%ISOPEN THEN CLOSE C03;
                  END IF;
                  IF C04%ISOPEN THEN CLOSE C04; 
                  END IF; 

             FND_FILE.PUT_LINE(FND_FILE.LOG,'STS_ERROR :'||V_MSG);
        
         IF SQLCODE != 0 THEN
             FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLCODE : '||TO_CHAR(SQLCODE));
             FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLERRM : '||SQLERRM);
         END IF;

             FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------');
             FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-2');
             CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);

           WHEN OTHERS THEN
                IF C01%ISOPEN THEN CLOSE C01;
                END IF;
                IF C02%ISOPEN THEN CLOSE C02;
                END IF;
                IF C03%ISOPEN THEN CLOSE C03;
                END IF;
                IF C04%ISOPEN THEN CLOSE C04;
                END IF;

             FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-1 : '||V_MSG);
         IF SQLCODE != 0 THEN
             FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLCODE : '||TO_CHAR(SQLCODE));
             FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLERRM : '||SQLERRM);
         END IF;

             FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------');
             FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-2');
             CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);
  
END;
/
show error





Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240539 is a reply to message #240538] Fri, 25 May 2007 03:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You don't expect us to count 472 lines, do you?
Add linenumbers.
(you can do that by giving the command "get <file>" in sqlplus)

[Updated on: Fri, 25 May 2007 03:21]

Report message to a moderator

Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240540 is a reply to message #240530] Fri, 25 May 2007 03:22 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
hi there,

please tell me how to apply the numbers for the lines...is there a button that i should click on or must i put in the numbers manually...

thank you
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240545 is a reply to message #240540] Fri, 25 May 2007 03:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I edited my previous post and added an explanation. Probably did that just after you read the original.
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240549 is a reply to message #240522] Fri, 25 May 2007 03:33 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
sorry about that....

anyway..here is the one comeplete with the line numbers...
thank you...


 get TMIN0870
  1  /*=========================================================================
  2    Program ID    : TMIN0870 TIM DTS Receiving Lot by Vendor
  3    Created Date  :
  4    Created By    :
  5    Purpose       :
  6    Parameter     : Rcv Date From
  7                    Rcv Date To
  8                    Vendor No
  9                    Item ID
 10    ==============================================================================*/
 11    SET SERVEROUTPUT ON FORMAT WRAPPED
 12    CREATE OR REPLACE PROCEDURE TMIN0870(
 13    ERRBUF           OUT VARCHAR2,
 14    RETCODE          OUT VARCHAR2,
 15    PRA_RCV_FROM     IN  DATE,
 16    PRA_RCV_TO       IN  DATE,
 17    PRA_VEND_NO      IN  VARCHAR2,
 18    PRA_ITEM_NO      IN  VARCHAR2
 19    ) IS
 20     CURSOR C01
 21    IS
 22         SELECT RCV.VEND_NO,
 23                RCV.VENDOR_NAME,
 24                RCV.ITEM_NO,
 25                SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
 26                RCV.CATEGORY ,
 27                COUNT(*)REC_LOT
 28         FROM ( SELECT RT.SHIPMENT_HEADER_ID,
 29                       MSI.SEGMENT1 ITEM_NO,
 30                       MSI.DESCRIPTION,
 31                       PV.SEGMENT1 VEND_NO,
 32                       PV.VENDOR_NAME,
 33                       NVL(B.CATEGORY,'DTS') CATEGORY
 34                FROM   RCV_TRANSACTIONS RT,
 35                       PO_LINES_ALL PLA,
 36                       PO_VENDORS PV,
 37                       MTL_SYSTEM_ITEMS_B MSI,
 38              ( SELECT SUM(QUANTITY)QTY,
 39                       PARENT_TRANSACTION_ID
 40                 FROM  RCV_TRANSACTIONS
 41                WHERE  TRANSACTION_TYPE = 'CORRECT'
 42              GROUP BY PARENT_TRANSACTION_ID) A,
 43              ( SELECT MIC.INVENTORY_ITEM_ID,
 44                DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
 45                FROM   MTL_ITEM_CATEGORIES MIC
 46                WHERE  MIC.CATEGORY_SET_ID IN (243,183)
 47                UNION
 48                SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
 49                FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B
 50         WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
 51           AND RT.VENDOR_ID = PV.VENDOR_ID
 52           AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
 53           AND RT.TRANSACTION_TYPE = 'RECEIVE'
 54           AND RT.ROUTING_HEADER_ID = 3
 55           AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO
 56           AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
 57           AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
 58           AND RT.QUANTITY + NVL(A.QTY,0) > 0
 59      GROUP BY RT.SHIPMENT_HEADER_ID,
 60               MSI.SEGMENT1,
 61               MSI.DESCRIPTION,
 62               PV.SEGMENT1,
 63               PV.VENDOR_NAME,
 64               NVL(B.CATEGORY,'DTS')) RCV
 65      GROUP BY RCV.VEND_NO,
 66               RCV.VENDOR_NAME,
 67               RCV.ITEM_NO,
 68               RCV.DESCRIPTION,
 69               RCV.CATEGORY
 70      ORDER BY RCV.VEND_NO,
 71               RCV.ITEM_NO
 72     ;/* END C01 */
 73    CURSOR C02
 74    IS
 75         SELECT RCV.VEND_NO,
 76                RCV.VENDOR_NAME,
 77                RCV.ITEM_NO,
 78                SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
 79                RCV.CATEGORY,
 80                COUNT(*)REC_LOT
 81         FROM ( SELECT RT.SHIPMENT_HEADER_ID,
 82                       MSI.SEGMENT1 ITEM_NO,
 83                       MSI.DESCRIPTION,
 84                       PV.SEGMENT1 VEND_NO,
 85                       PV.VENDOR_NAME,
 86                       NVL(B.CATEGORY,'DTS') CATEGORY
 87                FROM   RCV_TRANSACTIONS RT,
 88                       PO_LINES_ALL PLA,
 89                       PO_VENDORS PV,
 90                       MTL_SYSTEM_ITEMS_B MSI,
 91              ( SELECT SUM(QUANTITY)QTY,
 92                       PARENT_TRANSACTION_ID
 93                 FROM  RCV_TRANSACTIONS
 94                WHERE  TRANSACTION_TYPE = 'CORRECT'
 95              GROUP BY PARENT_TRANSACTION_ID) A,
 96              ( SELECT MIC.INVENTORY_ITEM_ID,
 97                DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
 98                FROM   MTL_ITEM_CATEGORIES MIC
 99                WHERE  MIC.CATEGORY_SET_ID IN (243,183)
100                UNION
101                SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
102                FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B
103         WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
104           AND RT.VENDOR_ID = PV.VENDOR_ID
105           AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
106           AND RT.TRANSACTION_TYPE = 'RECEIVE'
107           AND RT.ROUTING_HEADER_ID = 3
108           AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO
109           AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
110           AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
111           AND RT.QUANTITY + NVL(A.QTY,0) > 0
112           AND PV.VENDOR_ID = PRA_VEND_NO
113      GROUP BY RT.SHIPMENT_HEADER_ID,
114               MSI.SEGMENT1,
115               MSI.DESCRIPTION,
116               PV.SEGMENT1,
117               PV.VENDOR_NAME,
118               NVL(B.CATEGORY,'DTS')) RCV
119      GROUP BY RCV.VEND_NO,
120               RCV.VENDOR_NAME,
121               RCV.ITEM_NO,
122               RCV.DESCRIPTION,
123               RCV.CATEGORY
124      ORDER BY RCV.VEND_NO,
125               RCV.ITEM_NO
126     ; /* END C02 */
127    CURSOR C03
128    IS
129         SELECT RCV.VEND_NO,
130                RCV.VENDOR_NAME,
131                RCV.ITEM_NO,
132                SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
133                RCV.CATEGORY,
134                COUNT(*)REC_LOT
135         FROM ( SELECT RT.SHIPMENT_HEADER_ID,
136                       MSI.SEGMENT1 ITEM_NO,
137                       MSI.DESCRIPTION,
138                       PV.SEGMENT1 VEND_NO,
139                       PV.VENDOR_NAME,
140                       NVL(B.CATEGORY,'DTS') CATEGORY
141                FROM   RCV_TRANSACTIONS RT,
142                       PO_LINES_ALL PLA,
143                       PO_VENDORS PV,
144                       MTL_SYSTEM_ITEMS_B MSI,
145              ( SELECT SUM(QUANTITY)QTY,
146                       PARENT_TRANSACTION_ID
147                 FROM  RCV_TRANSACTIONS
148                WHERE  TRANSACTION_TYPE = 'CORRECT'
149              GROUP BY PARENT_TRANSACTION_ID) A,
150              ( SELECT MIC.INVENTORY_ITEM_ID,
151                DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
152                FROM   MTL_ITEM_CATEGORIES MIC
153                WHERE  MIC.CATEGORY_SET_ID IN (243,183)
154                UNION
155                SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
156                FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B
157         WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
158           AND RT.VENDOR_ID = PV.VENDOR_ID
159           AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
160           AND RT.TRANSACTION_TYPE = 'RECEIVE'
161           AND RT.ROUTING_HEADER_ID = 3
162           AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO
163           AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
164           AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
165           AND RT.QUANTITY + NVL(A.QTY,0) > 0
166           AND MSI.INVENTORY_ITEM_ID = PRA_ITEM_NO
167      GROUP BY RT.SHIPMENT_HEADER_ID,
168               MSI.SEGMENT1,
169               MSI.DESCRIPTION,
170               PV.SEGMENT1,
171               PV.VENDOR_NAME,
172               NVL(B.CATEGORY,'DTS')) RCV
173      GROUP BY RCV.VEND_NO,
174               RCV.VENDOR_NAME,
175               RCV.ITEM_NO,
176               RCV.DESCRIPTION,
177               RCV.CATEGORY
178      ORDER BY RCV.VEND_NO,
179               RCV.ITEM_NO
180     ;/* END C03*/
181    CURSOR C04
182    IS
183         SELECT RCV.VEND_NO,
184                RCV.VENDOR_NAME,
185                RCV.ITEM_NO,
186                SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
187                RCV.CATEGORY,
188                COUNT(*)REC_LOT
189         FROM ( SELECT RT.SHIPMENT_HEADER_ID,
190                       MSI.SEGMENT1 ITEM_NO,
191                       MSI.DESCRIPTION,
192                       PV.SEGMENT1 VEND_NO,
193                       PV.VENDOR_NAME,
194                       NVL(B.CATEGORY,'DTS') CATEGORY
195                FROM   RCV_TRANSACTIONS RT,
196                       PO_LINES_ALL PLA,
197                       PO_VENDORS PV,
198                       MTL_SYSTEM_ITEMS_B MSI,
199              ( SELECT SUM(QUANTITY)QTY,
200                       PARENT_TRANSACTION_ID
201                 FROM  RCV_TRANSACTIONS
202                WHERE  TRANSACTION_TYPE = 'CORRECT'
203              GROUP BY PARENT_TRANSACTION_ID) A,
204              ( SELECT MIC.INVENTORY_ITEM_ID,
205                DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
206                FROM   MTL_ITEM_CATEGORIES MIC
207                WHERE  MIC.CATEGORY_SET_ID IN (243,183)
208                UNION
209                SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
210                FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B
211         WHERE RT.PO_LINE_ID = PLA.PO_LINE_ID
212           AND RT.VENDOR_ID = PV.VENDOR_ID
213           AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
214           AND RT.TRANSACTION_TYPE = 'RECEIVE'
215           AND RT.ROUTING_HEADER_ID = 3
216           AND TRUNC(RT.TRANSACTION_DATE) BETWEEN PRA_RCV_FROM AND PRA_RCV_TO
217           AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
218           AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
219           AND RT.QUANTITY + NVL(A.QTY,0) > 0
220           AND PV.VENDOR_ID = PRA_VEND_NO
221           AND MSI.INVENTORY_ITEM_ID = PRA_ITEM_NO
222      GROUP BY RT.SHIPMENT_HEADER_ID,
223               MSI.SEGMENT1,
224               MSI.DESCRIPTION,
225               PV.SEGMENT1,
226               PV.VENDOR_NAME,
227               NVL(B.CATEGORY,'DTS')) RCV
228      GROUP BY RCV.VEND_NO,
229               RCV.VENDOR_NAME,
230               RCV.ITEM_NO,
231               RCV.DESCRIPTION,
232               RCV.CATEGORY
233      ORDER BY RCV.VEND_NO,
234               RCV.ITEM_NO
235     ;/* END C04 */
236     TYPE REC_VEND_NO_TYPE IS TABLE OF PO_VENDORS.SEGMENT1%TYPE
237          INDEX BY BINARY_INTEGER;
238     REC_VEND_NO REC_VEND_NO_TYPE;
239     TYPE REC_VENDOR_NAME_TYPE IS TABLE OF PO_VENDORS.VENDOR_NAME%TYPE
240          INDEX BY BINARY_INTEGER;
241     REC_VENDOR_NAME REC_VENDOR_NAME_TYPE;
242     TYPE REC_ITEM_NO_TYPE IS TABLE OF MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE
243          INDEX BY BINARY_INTEGER;
244     REC_ITEM_NO REC_ITEM_NO_TYPE;
245     TYPE REC_DESC_TYPE IS TABLE OF MTL_SYSTEM_ITEMS_B.DESCRIPTION%TYPE
246          INDEX BY BINARY_INTEGER;
247     REC_DESC REC_DESC_TYPE;
248     TYPE REC_CATEGORY_TYPE IS TABLE OF VARCHAR2(6)
249          INDEX BY BINARY_INTEGER;
250     REC_CATEGORY REC_CATEGORY_TYPE;
251     TYPE REC_COUNT_TYPE IS TABLE OF NUMBER
252          INDEX BY BINARY_INTEGER;
253     REC_COUNT REC_COUNT_TYPE;
254     V_ORG_NAME       ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
255     V_VEND_NO        PO_VENDORS.SEGMENT1%TYPE;
256     V_VENDOR_NAME    PO_VENDORS.VENDOR_NAME%TYPE;
257     V_ITEM_NO        MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
258     V_DESC           MTL_SYSTEM_ITEMS_B.DESCRIPTION%TYPE;
259     V_CATEGORY       VARCHAR2(6);
260     V_COUNT          NUMBER;
261     V_MSG                VARCHAR2(241);
262     MAX_LINE             BINARY_INTEGER DEFAULT 0;
263     PAGE_LINE            CONSTANT NUMBER := 44;
264     l                    BINARY_INTEGER DEFAULT 0;
265     STS             NUMBER DEFAULT 0;
266     CNC_RET                 BOOLEAN;
267     STS_ERROR               EXCEPTION;
268     ERR_RET                 VARCHAR2(240);
269     OK_RET          VARCHAR2(240);
270     V_ERR_MSG            VARCHAR2(1000);
271  /*=========================================================
272     PRINT_HEADER
273  ==========================================================*/
274     PROCEDURE PRINT_HEADER (PAGE_CNT IN NUMBER) IS
275  BEGIN
276           FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(V_ORG_NAME,23) || RPAD(' ',102) || 'Report Date : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI'));
277           FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Report ID : TMIN0870' || RPAD(' ',105) || 'Page       : ' || RPAD(TO_CHAR(PAGE_CNT),4));
278           FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
279           FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(' ',50) || 'TIM DTS Receiving Lot by Vendor');
280           FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
281           FND_FILE.PUT_LINE(FND_FILE.OUTPUT,LPAD('Ln',6) ||' '||RPAD('Vendor No',12)||' '||RPAD('Vendor Name',42)||' '||RPAD('Item No',12)||' '||RPAD('Description',60)||' '||RPAD('Category',8)||' '||RPAD('Rec Lot',4));
282           FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'------------ ------------------------------------------ ------------ ------------------------------------------------------------ -------- ----');
283  END PRINT_HEADER;
284  /*==============================================
285               DISPLAY_REPORT
286   =============================================*/
287  FUNCTION DISPLAY_REPORT
288  (
289                P_MAX_LINE   IN NUMBER,
290                P_ERR_MSG    OUT VARCHAR2
291  )
292   RETURN NUMBER IS
293                LINE_CNT NUMBER DEFAULT 0;
294                PAGE_CNT NUMBER DEFAULT 0;
295   BEGIN
296                FOR IDX IN 0..P_MAX_LINE
297   LOOP
298         IF LINE_CNT = 0 THEN
299                PAGE_CNT := PAGE_CNT + 1;
300                PRINT_HEADER(PAGE_CNT);
301                LINE_CNT := 0;
302         END IF;
303           FND_FILE.PUT_LINE(FND_FILE.OUTPUT,LPAD(TO_CHAR(IDX+1),6) ||' '||
304           RPAD(REC_VEND_NO(IDX),12)||' '|| RPAD(REC_VENDOR_NAME(IDX),42)
305           ||' '||RPAD(REC_ITEM_NO(IDX),12)||' '||RPAD(REC_DESC(IDX),60)
306           ||' '||RPAD(' ',8)||REC_CATEGORY(IDX)||' '||REC_COUNT(IDX));
307                LINE_CNT := LINE_CNT + 1;
308        IF LINE_CNT >= PAGE_LINE THEN
309                LINE_CNT := 0;
310                FND_FILE.PUT(FND_FILE.OUTPUT,CHR(12));
311        END IF;
312   END LOOP;
313        IF LINE_CNT >=  PAGE_LINE THEN
314                LINE_CNT := 0;
315                FND_FILE.PUT(FND_FILE.OUTPUT,CHR(12));
316                PAGE_CNT := PAGE_CNT + 1;
317                PRINT_HEADER(PAGE_CNT);
318        END IF;
319                FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
320                FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(' ',60) ||
321                '********** END OF REPORT **********');
322        RETURN OK_RET;
323   END DISPLAY_REPORT;
324  /*=================================================
325         MAIN
326  =================================================*/
327   BEGIN
328           SELECT ORGANIZATION_NAME
329           INTO V_ORG_NAME
330           FROM ORG_ORGANIZATION_DEFINITIONS
331           WHERE ORGANIZATION_ID = 21;
332           IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NULL THEN
333               V_MSG := 'OPEN C01';
334               OPEN C01;
335               V_MSG := 'FETCH C01';
336   LOOP
337           FETCH C01 INTO
338          V_VEND_NO,
339          V_VENDOR_NAME,
340          V_ITEM_NO,
341          V_DESC,
342          V_CATEGORY,
343          V_COUNT;
344           EXIT WHEN C01%NOTFOUND;
345           l := l + 1;
346   END LOOP;
347               V_MSG := 'CLOSE C01';
348               CLOSE C01;
349  END IF;
350           IF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NULL THEN
351               V_MSG := 'OPEN C02';
352               OPEN C02;
353               V_MSG := 'FETCH C01';
354   LOOP
355           FETCH C02 INTO
356               V_VEND_NO,
357               V_VENDOR_NAME,
358               V_ITEM_NO,
359               V_DESC,
360               V_CATEGORY,
361               V_COUNT;
362           EXIT WHEN C02%NOTFOUND;
363            l := l + 1;
364   END LOOP;
365               V_MSG := 'CLOSE C02';
366               CLOSE C02;
367  END IF;
368      IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NOT NULL THEN
369               V_MSG := 'OPEN C03';
370               OPEN C03;
371               V_MSG := 'FETCH C03';
372   LOOP
373           FETCH C03 INTO
374               V_VEND_NO,
375               V_VENDOR_NAME,
376               V_ITEM_NO,
377               V_DESC,
378               V_CATEGORY,
379               V_COUNT;
380           EXIT WHEN C03%NOTFOUND;
381             l := l + 1;
382   END LOOP;
383               V_MSG := 'CLOSE C03';
384               CLOSE C03;
385  END IF;
386           IF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NOT NULL THEN
387               V_MSG := 'OPEN C04';
388               OPEN C04;
389               V_MSG := 'FETCH C04';
390   LOOP
391           FETCH C04 INTO
392               V_VEND_NO,
393               V_VENDOR_NAME,
394               V_ITEM_NO,
395               V_DESC,
396               V_CATEGORY,
397               V_COUNT;
398           EXIT WHEN C04%NOTFOUND;
399            l := l + 1;
400   END LOOP;
401               V_MSG := 'CLOSE C04';
402               CLOSE C04;
403       END;
404   END IF;
405           MAX_LINE := l - 1;
406               FND_FILE.PUT_LINE(FND_FILE.LOG,' MAX_LINE: '||MAX_LINE);
407               STS := DISPLAY_REPORT(MAX_LINE,DISPLAY_REPORT,V_MSG);
408           IF STS = ERR_RET THEN RAISE STS_ERROR;
409           END IF;
410               CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',NULL);
411               /* FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'));*/
412           EXCEPTION
413               WHEN STS_ERROR THEN
414                    IF C01%ISOPEN THEN CLOSE C01;
415                    END IF;
416                    IF C02%ISOPEN THEN CLOSE C02;
417                    END IF;
418                    IF C03%ISOPEN THEN CLOSE C03;
419                    END IF;
420                    IF C04%ISOPEN THEN CLOSE C04;
421                    END IF;
422               FND_FILE.PUT_LINE(FND_FILE.LOG,'STS_ERROR :'||V_MSG);
423           IF SQLCODE != 0 THEN
424               FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLCODE : '||TO_CHAR(SQLCODE));
425               FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLERRM : '||SQLERRM);
426           END IF;
427               FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------');
428               FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-2');
429               CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);
430             WHEN OTHERS THEN
431                  IF C01%ISOPEN THEN CLOSE C01;
432                  END IF;
433                  IF C02%ISOPEN THEN CLOSE C02;
434                  END IF;
435                  IF C03%ISOPEN THEN CLOSE C03;
436                  END IF;
437                  IF C04%ISOPEN THEN CLOSE C04;
438                  END IF;
439               FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-1 : '||V_MSG);
440           IF SQLCODE != 0 THEN
441               FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLCODE : '||TO_CHAR(SQLCODE));
442               FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLERRM : '||SQLERRM);
443           END IF;
444               FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------');
445               FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-2');
446               CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);
447  END;
448  /
449* show error
450  


Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240550 is a reply to message #240538] Fri, 25 May 2007 03:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
CTRL-F is what you need Frank.

You have a DECLARE section like this:[CODE]V_MSG VARCHAR2(241);
MAX_LINE BINARY_INTEGER DEFAULT 0;
PAGE_LINE CONSTANT NUMBER := 44;
l BINARY_INTEGER DEFAULT 0;
STS NUMBER DEFAULT 0;
CNC_RET BOOLEAN;
STS_ERROR EXCEPTION;
ERR_RET VARCHAR2(240);
OK_RET VARCHAR2(240);
V_MSG VARCHAR2(241);
V_ERR_MSG VARCHAR2(1000);
/CODE]
You have two declareations for the same variable V_MSG, which is exactly what the error message said.
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240556 is a reply to message #240522] Fri, 25 May 2007 03:45 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
Hi,
I have rectified the error "v-msg"....
and have a new error which is causing havoc for me....
the error is :
"LINE/COL ERROR
-------- -----------------------------------------------------------------
472/9 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
if"


where could i have gone wrong....?

regards,
gil
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240559 is a reply to message #240556] Fri, 25 May 2007 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'll have an END; where Oracle is expecting to see END IF;.

Check your IF statements to see if they've all got matching END IF; statements.
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240562 is a reply to message #240556] Fri, 25 May 2007 03:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You don't have 472 lines...
Try to be consequent in your indenting.
For example lines 332 and 349 should have the same indent, as should 296 and 297.
Your exception handler is very hard to check too..
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240565 is a reply to message #240522] Fri, 25 May 2007 03:59 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
yes i know..i have tried putting in the if as you can see below, but then there is another error message which appears to be bugging the coding....

      IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NULL THEN
 .....
             
 LOOP  
      ......

 END LOOP;

          .....

END IF;
 
      IF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NULL THEN 

      .......
 LOOP 

         .......

 END LOOP;
               ..............


END IF;   
        

       IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NOT NULL THEN 
     
       .........
       
 LOOP  
       .........        
  
 END LOOP;
             
          .........

END IF;
    IF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NOT NULL THEN 
    
         .............. 
 LOOP   
         ......
 
 END LOOP;
             ........

     [COLOR=red]END IF; [/COLOR]    

 END IF;




--error code --

LINE/COL ERROR
-------- -----------------------------------------------------------------
474/6    PLS-00103: Encountered the symbol "IF" when expecting one of the
         following:
         ; <an identifier> <a double-quoted delimited-identifier>
         delete exists prior <a single-quoted SQL string>
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240571 is a reply to message #240565] Fri, 25 May 2007 04:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Dear me - I can't believe I'm actually doing your code debugging for you.
Get yourself a copy of the free SQL Developer from Oracle - it shows matching If/End if pairs for you.

Right: You have a section of code like this:
          IF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NOT NULL THEN
               V_MSG := 'OPEN C04';
               OPEN C04;
               V_MSG := 'FETCH C04';
   LOOP
           FETCH C04 INTO
               V_VEND_NO,
               V_VENDOR_NAME,
               V_ITEM_NO,
               V_DESC,
               V_CATEGORY,
               V_COUNT;
           EXIT WHEN C04%NOTFOUND;
            l := l + 1;
   END LOOP;
               V_MSG := 'CLOSE C04';
               CLOSE C04;
       END;
   END IF;
There is a spurious END in that block, just before the END IF;
That took me about 3 minutes to find, so what why couldn't you find it?
Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240574 is a reply to message #240522] Fri, 25 May 2007 04:19 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
hi there....tahnks alot for your help...
anyway....while waiting for your reply..i was messing around witht he codes..and hulalla!!!i manage to compile it...guess what..my errors were the what yu see...just like you said......the end if.....i removed it..then it worked...but then..i wonder why just now i did remove the end if and yet it still gave me the error...anwyay....i have completed it...but not without you help..thanks alot....
wish i could buy you a hot coffee from starbucks...thank you once again


 IF PRA_VEND_NO IS NOT NULL AND PRA_ITEM_NO IS NOT NULL THEN
             V_MSG := 'OPEN C04';
             OPEN C04;
             V_MSG := 'FETCH C04';

 LOOP  
         FETCH C04 INTO
             V_VEND_NO,
             V_VENDOR_NAME,
             V_ITEM_NO,
             V_DESC,
             V_CATEGORY,
             V_COUNT;
         EXIT WHEN C04%NOTFOUND;

          l := l + 1;

 END LOOP;
             V_MSG := 'CLOSE C04';
             CLOSE C04;

     END IF;

[B][COLOR=red] --END IF;[/COLOR][/B]

Re: PLS-00371: at most one declaration for 'V_MSG' is permitted [message #240616 is a reply to message #240574] Fri, 25 May 2007 05:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
wish i could buy you a hot coffee from starbucks...thank you once again

Thank you for the offer at least.
Good luck with the code.
Previous Topic: Flowchart / sequential list of calling objects.
Next Topic: optimize this procedure..
Goto Forum:
  


Current Time: Sun Dec 11 04:28:46 CST 2016

Total time taken to generate the page: 0.05174 seconds