Обсуждение: need some help with pl-pgsql
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
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? -- Adrian Klaver aklaver@comcast.net
Adrian Klaver wrote:
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;
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 ltsWould 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;
----- "justin" <justin@emproshunts.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
twitchywhen I see pwoid and pWoid refer to the same thing. This depends on case folding being consistent, not something
Ilike to count on. Second you might want to put in a few RAISE NOTICE statements to see what values are being pulled
fromthe db for wo_wipvalue and wo_qtyord and what is being supplied to the WHERE clause for pWoid.
Adrian Klaver
aklaver@comcast.net
On Tuesday 23 December 2008 3:36:46 pm Adrian Klaver wrote: > > 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@comcast.net 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 Potential issue. I don't see where _invhistid is declared. -- Adrian Klaver aklaver@comcast.net
Adrian Klaver wrote:
This function was completely rewritten several months ago along with a allot of other functions so there still allot of clean up to do making notes and removing stuff that does not need to be in the function.
I just can't figure out why in this function it can't find the record.
This malfunctioning function is called by ForceCloseWo() which goes through the work order completing any outstanding operations, and issuing material so it has to find the record first or it will fail out right. Once all material and operations are marked completed it then calls PostProduction() which moves the items out of WIP tables put the items into Inventory and does all the accounting stuff. So i really can't figure out what it is doing???
Now On the mixed case. i come from the Xbase languages specifically Foxpro which is case insensitive and a typeless language so i have a few really bad habits. I try to get every thing to match but sometimes screw up.
its declared. i noticed this function has more variables declared than it ever used so i removed them when posting to the list. I did not want anymore confusion so I removed one to many sorry.On Tuesday 23 December 2008 3:36:46 pm Adrian Klaver wrote: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@comcast.netSELECT 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 Potential issue. I don't see where _invhistid is declared.
This function was completely rewritten several months ago along with a allot of other functions so there still allot of clean up to do making notes and removing stuff that does not need to be in the function.
I just can't figure out why in this function it can't find the record.
This malfunctioning function is called by ForceCloseWo() which goes through the work order completing any outstanding operations, and issuing material so it has to find the record first or it will fail out right. Once all material and operations are marked completed it then calls PostProduction() which moves the items out of WIP tables put the items into Inventory and does all the accounting stuff. So i really can't figure out what it is doing???
Now On the mixed case. i come from the Xbase languages specifically Foxpro which is case insensitive and a typeless language so i have a few really bad habits. I try to get every thing to match but sometimes screw up.
Adrian Klaver wrote:
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
----- "justin" <justin@emproshunts.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@comcast.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
On Tuesday 23 December 2008 9:57:01 pm justin wrote: > > 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 Looks like you are narrowing it down. Good luck on that and if you need another set of eyes don't hesitate to post. -- Adrian Klaver aklaver@comcast.net
justin wrote:
Adrian Klaver wrote:----- "justin" <justin@emproshunts.com> wrote:
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
Found the problem at last. it was what i thought early this morning, the database had some flaky data set to null. so modified the the tables in question set defaults to zero and updated all the records that has null to zero. hopefully that fixes the problem going forward.
thanks to all for helping, pointing me in the right direction to figure out what is going on, as i was completely lost of what next to do.
On Wed, Dec 24, 2008 at 9:32 AM, justin <justin@emproshunts.com> wrote: > > > Found the problem at last. it was what i thought early this morning, the > database had some flaky data set to null. so modified the the tables in > question set defaults to zero and updated all the records that has null to > zero. hopefully that fixes the problem going forward. > > thanks to all for helping, pointing me in the right direction to figure out > what is going on, as i was completely lost of what next to do. Would it make sense to set that field to not null?