Re: need some help with pl-pgsql

----- "justin" <justin(at)emproshunts(dot)com> wrote:

> Adrian Klaver wrote:
>
> Would help to see the
> whole function. Also make sure you did not name one of
> the variables the same as a column name, this will confuse plpgsql.
> Are you
> using the same value for wo_id in the function as in the manual select
> statement?
>
> First the funciton has been running for months and never has had a
> problem. No changes to the database scheme. Second use variable naming
> scheme completely different from column names. _ always is the first
> character in variables. p is always the first character in passed
> parameters.
>
> Take a look at the screen shot and be in aw as i am
>
>
>
> postproduction(pwoid integer, pqty numeric, pbackflush boolean,
> pbackflushoperations boolean, pitemlocseries integer, psuuser text,
> prnuser text, pdate date)
> RETURNS integer AS
> $BODY$ DECLARE
> _woNumber TEXT;
> _itemlocSeries INTEGER;
>
> _parentQty NUMERIC;
> _qty NUMERIC;
> _TotalCost numeric;
>
> BEGIN
>
> IF (pQty <= 0) THEN
> RETURN 0;
> END IF;
>
> IF ( ( SELECT wo_status
> FROM wo
> WHERE (wo_id=pWoid) ) NOT IN ('R','E','I') ) THEN
> RETURN -1;
> END IF;
>
> --If this is item type Job then we are using the wrong function
> SELECT item_type INTO _check
> FROM wo, itemsite, item
> WHERE ((wo_id=pWoid)
> AND (wo_itemsite_id=itemsite_id)
> AND (itemsite_item_id=item_id)
> AND (item_type = 'J'));
>
> IF (FOUND) THEN
> RAISE EXCEPTION 'Work orders for job items are posted when quantities
> are shipped on the associated sales order';
> END IF;
>
> SELECT formatWoNumber(pWoid) INTO _woNumber;
>
> SELECT roundQty(item_fractional, pQty) INTO _parentQty
> FROM wo,
> itemsite,
> item
> WHERE ((wo_itemsite_id=itemsite_id)
> AND (itemsite_item_id=item_id)
> AND (wo_id=pWoid));
>
> -- Create the material receipt transaction
> IF (pItemlocSeries = 0) THEN
> SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
> ELSE
> _itemlocSeries = pItemlocSeries;
> END IF;
>
>
>
> --Lets get Wips Current total cost
> Select (wo_wipvalue/wo_qtyord)
> into _TotalCost
> from wo
> where wo_id = pWoid ;
> --Moves WIP into Inventory.
> SELECT postInvTrans( itemsite_id,
> 'RM',
> _parentQty,
> 'W/O',
> 'WO',
> _woNumber,
> '',
> 'Receive Inventory from Manufacturing',
> costcat_asset_accnt_id,
> costcat_wip_accnt_id,
> _itemlocSeries,
> true,
> _TotalCost,
> pDate::timestamp ) INTO _invhistid
> FROM wo,
> itemsite,
> costcat
> WHERE ( (wo_itemsite_id=itemsite_id)
> AND (itemsite_costcat_id=costcat_id)
> AND (wo_id=pWoid) );
>
> -- Increase this W/O's received qty decrease its WIP value
> UPDATE wo SET
> wo_qtyrcv = (wo_qtyrcv + _parentQty),
> wo_wipvalue = (wo_wipvalue - (_TotalCost*pQty))
> FROM itemsite,
> item
> WHERE ((wo_itemsite_id=itemsite_id)
> AND (itemsite_item_id=item_id)
> AND (wo_id=pWoid));
>
> -- Make sure the W/O is at issue status
> UPDATE wo SET
> wo_status='I'
> WHERE (wo_id=pWoid);
>
>
>
> RETURN _itemlocSeries;
>
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
> ALTER FUNCTION postproduction(integer, numeric, boolean, boolean,
> integer, text, text, date) OWNER TO justin;
> GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
> boolean, integer, text, text, date) TO justin;
> GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
> boolean, integer, text, text, date) TO public;

Hmmm. A couple of suggestions while I ponder this more. For clarity sake decide on a case scheme. I start getting twitchy when I see pwoid and pWoid refer to the same thing. This depends on case folding being consistent, not something I like to count on. Second you might want to put in a few RAISE NOTICE statements to see what values are being pulled from the db for wo_wipvalue and wo_qtyord and what is being supplied to the WHERE clause for pWoid.

Adrian Klaver
aklaver(at)comcast(dot)net