Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: PL/SQL Procedure not returning correct value
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
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 -----
>
> 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
![]() |
![]() |