Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: PL/SQL Procedure not returning correct value

Re: PL/SQL Procedure not returning correct value

From: Ajay Kothari <akothari_at_lipidata.com>
Date: Wed, 20 Sep 2000 11:54:38 +0530
Message-Id: <10625.117413@fatcity.com>


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 -----
From: Amar Kumar Padhi <TS2017_at_emirates.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Monday, September 18, 2000 04:05 PM Subject: RE: PL/SQL Procedure not returning correct value

>
> 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).
Received on Wed Sep 20 2000 - 01:24:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US