Re: need some help with pl-pgsql

Adrian Klaver wrote:
> ----- "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)ne

I have taken your idea and made sure all the variables all appear the
same and add raise notice for each portion of the command that is failing.

-------------------------------------------------------------
NOTICE: _wipvalue: <NULL>
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement

NOTICE: _wipqty: 1.00000000
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement

NOTICE: _wipvalue/_wipqty= <NULL>
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement

NOTICE: pwoid: 6916
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement

NOTICE: TotalCost: <NULL>
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement

----------------------------------------------------------------

So it appears that something is causing _wipvalue to get set to NULL
somewhere else in the code. Now when running the failing select
statement manually works is because the modified record is still not
committed yet and its reading the unmodified record.

the ForceCloseWo() calls several other functions before PostProduction()
that beat on the header record a few times. ForceCloseWo does extactly
what it sounds like closes Work Orders forcible based on default values
set in the database. I'm betting this record has some flaky setting
somewhere thats either in the posting of raw material or labor
operations.

its late i'm going to bed fight this again later this morning.

thanks