Re: PL/SQL Procedure not returning correct value

Hi,

   In the following sql,

 SELECT MIN(GRN_NO) INTO MIN_GRN FROM
 STORE_FIFO_GRN_DTL
  WHERE PART_NO=PART_NO
    AND PRODUCT=PRODUCT
    AND STOCK > 0;   I suspect that the part_no and product that u   are using in the right hand side of the = operator   are being intepreted by the database as the   database columns itself from store_fifo_grn_dtl   table and is not being equated to your input   parameter value. The database is right in this.

  Use a naming standard like I_PRODUCT and   I_PART_NO for the input variables.

  This should solve the mystery.

  Let me know if your requirement is not met.

Regards
Rajagopal Venkataramany

• Ajay Kothari <akothari_at_lipidata.com> wrote:
  > Dear Mr.Padhi,
  >
  > Thanks for the same.
  >
  > The following is the Procedure Statement:
  > -------------PROCEDURE
  >
  

  ---

  
  > ---------------------
  > --SEARCH FOR MIN. GRN_NO ON WHICH STOCK IS PRESENT
  > --CALCULATE THE DIFF BETWEEN STOCK AND QTY_ISSUE
  > --IF DIFF IS POSITIVE THEN
  > --INSERT INTO TABLE FOR FIFO ISSUE
  > --IF DIFF IS NEGATIVE THAN INSERT INTO FIFO ISSUE
  > --SEARCH FOR NEXT GRN WITH STOCK
  > --CAL DIFF ,CONTINUE THE SAME TILL DIFF IS 0 OR
  > POSITIVE
  >
  > CREATE OR REPLACE PROCEDURE PROC_GRN_STOCK(PRODUCT
  > VARCHAR2,PART_NO
  > varchar2,QTY_ISS NUMBER,INDENT_NO NUMBER)
  > AS
  >
  > MIN_GRN NUMBER(6) :=0;
  > DIFF NUMBER(9,2);
  > ISS_RATE NUMBER(9,2);
  > STK NUMBER(9,2);
  > ISS_VAL NUMBER(9,2);
  > QTY_BAL NUMBER(9,2) :=QTY_ISS;
  >
  > BEGIN
  >
  > WHILE QTY_BAL > 0
  >
  > LOOP
  >
  >
  >
  > SELECT MIN(GRN_NO) INTO MIN_GRN FROM
  > STORE_FIFO_GRN_DTL
  > WHERE PART_NO=PART_NO
  > AND PRODUCT=PRODUCT
  > AND STOCK > 0;
  >
  >
  > dbms_output.put_line(TO_CHAR(MIN_GRN));
  >
  > SELECT STOCK INTO STK FROM STORE_FIFO_GRN_DTL
  > WHERE PART_NO=PART_NO
  > AND PRODUCT=PRODUCT
  > AND GRN_NO=MIN_GRN;
  >
  > SELECT RATE INTO ISS_RATE FROM STORE_FIFO_GRN_DTL
  > WHERE PART_NO=PART_NO
  > AND PRODUCT=PRODUCT
  > AND GRN_NO=MIN_GRN;
  >
  > DIFF :=STK-QTY_BAL;
  >
  > IF DIFF >= 0 THEN
  > ISS_VAL :=QTY_BAL*ISS_RATE;
  > INSERT INTO
  >
  STORE_FIFO_ISSUE_DTL(PRODUCT,INDENT_NO,GRN_NO,PART_NO,QTY_ISSUE,VALUE)
  >
  >
  VALUES(PRODUCT,INDENT_NO,MIN_GRN,PART_NO,QTY_BAL,ISS_VAL);
  > QTY_BAL:=0;
  >
  > UPDATE STORE_FIFO_GRN_DTL
  > SET STOCK=DIFF
  > WHERE GRN_NO=MIN_GRN
  > AND PART_NO=PART_NO
  > AND PRODUCT=PRODUCT;
  >
  > ELSE
  >
  > QTY_BAL :=QTY_BAL-STK;
  > ISS_VAL :=STK*ISS_RATE;
  >
  > INSERT INTO
  >
  STORE_FIFO_ISSUE_DTL(PRODUCT,INDENT_NO,GRN_NO,PART_NO,QTY_ISSUE,VALUE)
  >
  >
  VALUES(PRODUCT,INDENT_NO,MIN_GRN,PART_NO,STK,ISS_VAL);
  >
  > UPDATE STORE_FIFO_GRN_DTL
  > SET STOCK=0
  > WHERE GRN_NO=MIN_GRN
  > AND PART_NO=PART_NO
  > AND PRODUCT=PRODUCT;
  >
  > END IF;
  >
  > END LOOP;
  > END;
  >
  >
  >
  =================================END==========================
  >
  

  ---

  
  > Keep smiling !!! Visit us at www.lipidata.com
  > ----- Original Message -----
  > To: Multiple recipients of list ORACLE-L
  > <ORACLE-L_at_fatcity.com>
  > Sent: Monday, September 18, 2000 04:05 PM
  >
  >
  > >
  > > If you have no problem,
  > > can you post the full statement.
  > >
  > > Thanks,
  > > Amar
  > > 00-971-50-7883254
  > > ts2017_at_emirates.com
  > > amar_padhi_at_hotmail.com
  > > amar_padhi_at_musclemail.com
  > >
  > >
  > >
  > > -----Original Message-----
  > > Sent: Monday, September 18, 2000 1:35 PM
  > > To: Multiple recipients of list ORACLE-L
  > >
  > >
  > > I am facing the problem in getting minimum value
  > of a key item
  > conditionally
  > > in PL/SQL Stored Procedure. The same thing is
  > exactly working at SQL
  > prompt.
  > >
  > > My query is like this:
  > >
  > > Select min(grn_no) into :grnno from <table_name>
  > where<condition>
  > >
  > > we are using Oracle7.3.4 on Sco-Unix Relase5.
  > >
  > > Kindly suggest the solution as I am hanged-up.
  > >
  > > Regards and thanks in advance
  > > Ajay Kothari
  > >
  > > Keep smile !!! Visit us at www.lipidata.com
  > >
  > > __________________________________________________
  > > Lipi Data Systems Ltd, Mumbai
  > >
  > >
  > > --
  > > Author: Ajay Kothari
  > > INET: akothari_at_lipidata.com
  > >
  > > Fat City Network Services -- (858) 538-5051
  > FAX: (858) 538-5051
  > > San Diego, California -- Public Internet
  > access / Mailing Lists
  > >
  >
  

  ---

  
  > > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > > (or the name of mailing list you want to be
  > removed from). You may
  > > also send the HELP command for other information
  > (like subscribing).
  > > --
  > > Author: Amar Kumar Padhi
  > > INET: TS2017_at_emirates.com
  > >
  > > Fat City Network Services -- (858) 538-5051
  > FAX: (858) 538-5051
  > > San Diego, California -- Public Internet
  > access / Mailing Lists
  > >
  >
  

  ---

  
  > > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > > (or the name of mailing list you want to be
  > removed from). You may
  > > also send the HELP command for other information
  > (like subscribing).
  > >
  >
  > __________________________________________________
  > Lipi Data Systems Ltd, Mumbai
  >
  >
  > --
  > Author: Ajay Kothari
  > INET: akothari_at_lipidata.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >
  

  ---