Thanks Nigel. I spent some time reworking the original example you gave me, and after a lot of head-banging I was able to get the process to work without having to rely on the ReadyToInvoice flag. I have a BPM without any qualifications on CustShip.Update, and this code (I'll remove the PublishInfoMsg calls when I deploy it).
One thing I discovered about your initial example is that GlbCustCred has multiple rows for a given company for linked Global customers. I had to qualify it further as I couldn't figure out why I wasn't getting the real GlobalCreditLimit for the customer (I was getting the first row back, and there are two returned in my system if I don't qualify it so that GlbCustCred.ExtCompany = ttShipHead.Company).
I have some mysteries about how to deal with flow control in 4GL (the "Return." Statement didn't seem to work as I expected), and the {lib/PublishInfoMsg.i} call mysteriously doesn't do anything if you actually throw a real exception via {lib/PublishEx.i}.
I also couldn't figure out how to properly detect if the ttShipHead record isn't really populated (i.e. you create a new ShipHead, and then immediately add a ShipDtl without specifying the order in the header) so that's commented out.
Anyways, along with checks for CreditHold and OrderHold, this satisfies my requirement (at least based on my testing). I can send along the entire BPM package if folks want it.
Thanks for your help!
-bws
DEFINE VARIABLE InfoMsg as CHARACTER NO-UNDO.
DEFINE VARIABLE ExMsg as CHARACTER NO-UNDO.
DEFINE VARIABLE ShipValue AS DECIMAL INITIAL 0 NO-UNDO.
DEFINE VARIABLE ARTotal AS DECIMAL INITIAL 0 NO-UNDO.
DEFINE VARIABLE GlobalCreditLimit as DECIMAL INITIAL 0 NO-UNDO.
DEFINE VARIABLE ExceededValue AS DECIMAL INITIAL 0 NO-UNDO.
find first ttShipHead where ttShipHead.RowMod = 'A' or ttShipHead.RowMod='U' no-lock no-error.
if available ttShipHead then do:
/* No point in going further if we have no Customer yet in the ShipHead record. */
/* THIS DOESN'T WORK, AS A RESULT ADDING A SHIPDTL TO A BLANK SHIPHEAD RECORD GENERATES A 4GL ERROR.
if NOT ttShipHead.CustNum > 0 then do:
InfoMsg = "No CustNum Defined.".
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
return.
end.
*/
/* First get our current AR Invoice Total for this Customer */
find GlbCustCred where GlbCustCred.Company = ttShipHead.Company and GlbCustCred.CustNum = ttShipHead.CustNum and
GlbCustCred.ExtCompany = ttShipHead.Company no-lock no-error.
if available GlbCustCred then do:
ARTotal = GlbCustCred.ARTotal.
end. InfoMsg = "ARTotal: " + STRING(ARTotal) + " CustNum: " + STRING(GlbCustCred.CustNum).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
/* Find the Credit Limit for this Customer */
find Customer where Customer.Company = ttShipHead.Company and Customer.CustNum = ttShipHead.CustNum no-lock no-error.
if available Customer then do:
GlobalCreditLimit = Customer.GlobalCreditLimit.
end.
InfoMsg = "GlobalCreditLimit: " + STRING(GlobalCreditLimit).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
/* Find the value of the shipment */
/* for each ShipDtl where ShipDtl.Company = ttShipHead.Company and ShipDtl.PackNum = ttShipHead.PackNum no-lock: */
for each ttShipDtl where ttShipDtl.RowMod = 'A' or ttShipDtl.RowMod = 'U' no-lock:
find OrderDtl where OrderDtl.Company = ttShipDtl.Company and OrderDtl.OrderNum = ttShipDtl.OrderNum and
OrderDtl.OrderLine = ttShipDtl.OrderLine no-lock no-error.
find OrderRel where OrderRel.Company = ttShipDtl.Company and OrderRel.OrderNum = ttShipDtl.OrderNum and
OrderRel.OrderLine = ttShipDtl.OrderLine and OrderRel.OrderRelNum = ttShipDtl.OrderRelNum no-lock no-error.
if available OrderDtl and available OrderRel then do:
if OrderRel.CheckBox01 = False then do:
ShipValue = ShipValue + OrderDtl.UnitPrice * ttShipDtl.OurInventoryShipQty.
end.
end.
end.
InfoMsg = "ShipValue: " + STRING(ShipValue).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
ExceededValue = GlobalCreditLimit - ARTotal - ShipValue.
InfoMsg = "ExceededValue: " + STRING(ExceededValue).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
if ExceededValue < 0 then do:
ExMsg = "Credit limit exceeded by this shipment by $" + STRING(- ExceededValue) + ". Cannot Ship.".
/* {lib/PublishInfoMsg.i &InfoMsg = ExMsg}. */
{lib/PublishEx.i &ExMsg = ExMsg} {&THROW_PRIVATE}.
end.
end.
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Nigel Kerley
Sent: Friday, May 29, 2009 10:09 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: State of Temp Tables in BPM
Hi Brian,
A couple of points:
You mentioned "server\bpm\ttable2xml.i". I don't know how access this, but there's a function in the GlbAlert.i file "DumpRecordAsXMLFile". It's explained on P467 of the Tools manual. I haven't looked at it but it may be of use.
Next, I was trying to create a BAM against the ShipHead table some time ago and found the same as you regarding the state of the Ship tables. I eventually created a post-processing directive against the CustShip.Update BO and used a condition of "number of rows in the <query> is equal to one", and query of "for each ttShipHead where ReadyToInvoice=True". That seems to be the last process before an order is "Shipped".
Using this you could try the following code (though you may want to use a pre-processing directive):
find first ttShipHead where ttShipHead.ShipStatus="Shipped" no-lock no-error.
if available ttShipHead then do:
DEFINE VARIABLE ShipValue AS DECIMAL NO-UNDO.
ShipValue = 0.
/* Loop through each line in the shipment to calculate its value */
for each ShipDtl where ShipDtl.Company = ttShipHead.Company and ShipDtl.PackNum = ttShipHead.PackNum no-lock no-error:
InfoMsg = "PackLine: " + STRING(ShipDtl.PackLine).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
find OrderDtl where OrderDtl.Company = ShipDtl.Company and OrderDtl.OrderNum = ShipDtl.OrderNum
and OrderDtl.OrderLine = ShipDtl.OrderLine no-lock no-error.
if available OrderDtl then do:
ShipValue = ShipValue + (OrderDtl.UnitPrice * ShipDtl.OurInventoryShipQty).
end.
end.
if ShipValue < <Certain Min Value> then do:
{lib/PublishEx.i &ExMsg = "'Shipment is below min value.'"}
{&THROW_PRIVATE}.
end.
HTH,
Nigel.
One thing I discovered about your initial example is that GlbCustCred has multiple rows for a given company for linked Global customers. I had to qualify it further as I couldn't figure out why I wasn't getting the real GlobalCreditLimit for the customer (I was getting the first row back, and there are two returned in my system if I don't qualify it so that GlbCustCred.ExtCompany = ttShipHead.Company).
I have some mysteries about how to deal with flow control in 4GL (the "Return." Statement didn't seem to work as I expected), and the {lib/PublishInfoMsg.i} call mysteriously doesn't do anything if you actually throw a real exception via {lib/PublishEx.i}.
I also couldn't figure out how to properly detect if the ttShipHead record isn't really populated (i.e. you create a new ShipHead, and then immediately add a ShipDtl without specifying the order in the header) so that's commented out.
Anyways, along with checks for CreditHold and OrderHold, this satisfies my requirement (at least based on my testing). I can send along the entire BPM package if folks want it.
Thanks for your help!
-bws
DEFINE VARIABLE InfoMsg as CHARACTER NO-UNDO.
DEFINE VARIABLE ExMsg as CHARACTER NO-UNDO.
DEFINE VARIABLE ShipValue AS DECIMAL INITIAL 0 NO-UNDO.
DEFINE VARIABLE ARTotal AS DECIMAL INITIAL 0 NO-UNDO.
DEFINE VARIABLE GlobalCreditLimit as DECIMAL INITIAL 0 NO-UNDO.
DEFINE VARIABLE ExceededValue AS DECIMAL INITIAL 0 NO-UNDO.
find first ttShipHead where ttShipHead.RowMod = 'A' or ttShipHead.RowMod='U' no-lock no-error.
if available ttShipHead then do:
/* No point in going further if we have no Customer yet in the ShipHead record. */
/* THIS DOESN'T WORK, AS A RESULT ADDING A SHIPDTL TO A BLANK SHIPHEAD RECORD GENERATES A 4GL ERROR.
if NOT ttShipHead.CustNum > 0 then do:
InfoMsg = "No CustNum Defined.".
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
return.
end.
*/
/* First get our current AR Invoice Total for this Customer */
find GlbCustCred where GlbCustCred.Company = ttShipHead.Company and GlbCustCred.CustNum = ttShipHead.CustNum and
GlbCustCred.ExtCompany = ttShipHead.Company no-lock no-error.
if available GlbCustCred then do:
ARTotal = GlbCustCred.ARTotal.
end. InfoMsg = "ARTotal: " + STRING(ARTotal) + " CustNum: " + STRING(GlbCustCred.CustNum).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
/* Find the Credit Limit for this Customer */
find Customer where Customer.Company = ttShipHead.Company and Customer.CustNum = ttShipHead.CustNum no-lock no-error.
if available Customer then do:
GlobalCreditLimit = Customer.GlobalCreditLimit.
end.
InfoMsg = "GlobalCreditLimit: " + STRING(GlobalCreditLimit).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
/* Find the value of the shipment */
/* for each ShipDtl where ShipDtl.Company = ttShipHead.Company and ShipDtl.PackNum = ttShipHead.PackNum no-lock: */
for each ttShipDtl where ttShipDtl.RowMod = 'A' or ttShipDtl.RowMod = 'U' no-lock:
find OrderDtl where OrderDtl.Company = ttShipDtl.Company and OrderDtl.OrderNum = ttShipDtl.OrderNum and
OrderDtl.OrderLine = ttShipDtl.OrderLine no-lock no-error.
find OrderRel where OrderRel.Company = ttShipDtl.Company and OrderRel.OrderNum = ttShipDtl.OrderNum and
OrderRel.OrderLine = ttShipDtl.OrderLine and OrderRel.OrderRelNum = ttShipDtl.OrderRelNum no-lock no-error.
if available OrderDtl and available OrderRel then do:
if OrderRel.CheckBox01 = False then do:
ShipValue = ShipValue + OrderDtl.UnitPrice * ttShipDtl.OurInventoryShipQty.
end.
end.
end.
InfoMsg = "ShipValue: " + STRING(ShipValue).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
ExceededValue = GlobalCreditLimit - ARTotal - ShipValue.
InfoMsg = "ExceededValue: " + STRING(ExceededValue).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
if ExceededValue < 0 then do:
ExMsg = "Credit limit exceeded by this shipment by $" + STRING(- ExceededValue) + ". Cannot Ship.".
/* {lib/PublishInfoMsg.i &InfoMsg = ExMsg}. */
{lib/PublishEx.i &ExMsg = ExMsg} {&THROW_PRIVATE}.
end.
end.
--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Nigel Kerley
Sent: Friday, May 29, 2009 10:09 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: State of Temp Tables in BPM
Hi Brian,
A couple of points:
You mentioned "server\bpm\ttable2xml.i". I don't know how access this, but there's a function in the GlbAlert.i file "DumpRecordAsXMLFile". It's explained on P467 of the Tools manual. I haven't looked at it but it may be of use.
Next, I was trying to create a BAM against the ShipHead table some time ago and found the same as you regarding the state of the Ship tables. I eventually created a post-processing directive against the CustShip.Update BO and used a condition of "number of rows in the <query> is equal to one", and query of "for each ttShipHead where ReadyToInvoice=True". That seems to be the last process before an order is "Shipped".
Using this you could try the following code (though you may want to use a pre-processing directive):
find first ttShipHead where ttShipHead.ShipStatus="Shipped" no-lock no-error.
if available ttShipHead then do:
DEFINE VARIABLE ShipValue AS DECIMAL NO-UNDO.
ShipValue = 0.
/* Loop through each line in the shipment to calculate its value */
for each ShipDtl where ShipDtl.Company = ttShipHead.Company and ShipDtl.PackNum = ttShipHead.PackNum no-lock no-error:
InfoMsg = "PackLine: " + STRING(ShipDtl.PackLine).
{lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
find OrderDtl where OrderDtl.Company = ShipDtl.Company and OrderDtl.OrderNum = ShipDtl.OrderNum
and OrderDtl.OrderLine = ShipDtl.OrderLine no-lock no-error.
if available OrderDtl then do:
ShipValue = ShipValue + (OrderDtl.UnitPrice * ShipDtl.OurInventoryShipQty).
end.
end.
if ShipValue < <Certain Min Value> then do:
{lib/PublishEx.i &ExMsg = "'Shipment is below min value.'"}
{&THROW_PRIVATE}.
end.
HTH,
Nigel.
--- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@...> wrote:
>
> Okay, here's what I'm struggling with. I've finally gotten consensus from my colleagues (sort of) on how to deal with the shipment-related issue I asked about a while back (and got a really great starting point from Nigel).
>
> I'm trying to come up with the logic in a 4GL BPM pre-processing action custship.update() to properly capture the "proposed value" of the shipment.
>
> find first ttShipHead where ttShipHead.RowMod = 'A' or ttShipHead.RowMod='U' no-lock no-error.
> find ttShipDtl where ttShipDtl.RowMod = 'A' or ttShipDtl.RowMod='U' no-lock no-error.
>
> if available ttShipHead or available ttShipDtl then do:
> DEFINE VARIABLE ShipValue AS DECIMAL NO-UNDO.
> ShipValue = 0.
> /* Loop through each line in the shipment to calculate its value */
> /* for each ShipDtl where ttShipHead.Company = ShipDtl.Company and ttShipHead.PackNum = ShipDtl.Packnum no-lock: */
> for each ttShipDtl no-lock:
> InfoMsg = "PackLine: " + STRING(ttShipDtl.PackLine).
> {lib/PublishInfoMsg.i &InfoMsg = InfoMsg}.
> find OrderDtl where OrderDtl.Company = ttShipDtl.Company and OrderDtl.OrderNum = ttShipDtl.OrderNum
> and OrderDtl.OrderLine = ttShipDtl.OrderLine no-lock no-error.
> if available OrderDtl then do:
> ShipValue = ShipValue + OrderDtl.UnitPrice * ttShipDtl.OurInventoryShipQty.
> end.
> end.
>
> It seems like sometimes depending on how I do the update (create new packlist, save, update later, save immediately, change quantity, etc.) I get the data I'm looking for in ttShipDtl, sometimes its in ShipDtl. Note I have a commented-out version of the "for each" loop.
>
> Basically ShipValue sometimes doesn't get set and I don't enter the foreach loop consistently.
>
> Do I have to do a 'find' to do a 'for each' or does Progress handle that all for me behind the scenes?
>
> But more generally I'm not sure how to approach this requirement.
>
> Basically I want to ensure that a CustShip record doesn't get created and saved that doesn't exceed a certain value, and I'm not familiar enough with how Vantage behaves to understand how to do that to get it to work in all cases.
>
> -bws
>
> --
> Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
> Â Â Â Â bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com
>
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Rob Bucek
> Sent: Thursday, May 28, 2009 10:01 AM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] State of Temp Tables in BPM
>
> Funny thing, assumptions...mine was always that the data sets present in
> the trace was that of the temporary tables...if that's not true most of
> the time...then we should all definitely be looking for a better tool to
> debug BPM's... generally speaking I have seen a correlation between
> trace data and what was available in the temporary tables during a
> particular method call...ive still so much to learn yet..
>
------------------------------------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links