Adrian Klaver wrote:
On Tuesday 23 December 2008 1:04:49 pm justin wrote:
have a function written in pgsql it runs just fine, except its doing
something really stupid.
The function runs just fine till this select statement
Select (wo_wipvalue/wo_qtyord)
into _TotalCost
from wo
where wo_id = pWoid ;
This goes to the work order header table to gets the current value
divided by current qty thats been completed so far, then sticks the
value into _TotalCost variable based on the parameter passed into
variable pWoid.
problem is it returns NULL which is impossible as i can manually run
the select statement and get the $1.589445
I have no idea how to fix this problem going forward???
and the database is 8.3.5 running Ubuntu 8.0 lts
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;
