BPM Query Blank Title 73387

system · January 21, 2015, 10:22am

Mark,

You are finding the ttInvcHead record with a no-lock status. This makes the record as read only so that it can't be updated.

If you plan to update a record that you are finding, you should use the exclusive-lock option on the find statement.

Tom Osen

IT Manager

Water Gremlin Co.

system · January 23, 2009, 11:01am

How do I use an input parameter in a BPM query. When I right click and
select Parameters and select an input parameter is inserts something like
%sourceQuote% but my query does not validate. In the actions section using
4GL I can just type in sourceQuote without % and it works. Suggestions?

Daniel

[Non-text portions of this message have been removed]

system · August 28, 2009, 5:19pm

I am trying to write a query that identifies parts with selling prices lower than our cost at the sales order level. And I am very new to writing queries. So that being said, how do I compare the ttorderdtl.unitprice field to the sum of the average costs from the part cost table? I was hoping it would be something simple like ttorderdtl.unitprice < (avgburden + avglabor + avgmtl etc) but when I try this I get an error. Is there a sum function that I can use to do this for me?

Any help would be greatly appreciated.

Michelle de la Vega
Business Applications Manager
Cold Jet, LLC
455 Wards Corner Road
Loveland, Ohio 45140
USA
+1 513-716-6400 (office)
+1 513-382-3281 (mobile)
+1 513-831-1209 (fax)
www.coldjet.com<http://www.coldjet.com>
[cid:image001.jpg@01CA2803.A374BC70]

[Non-text portions of this message have been removed]

system · August 30, 2009, 9:09am

Hi Michelle,

We have something like this is place. We use two BPMs to check the margin on Sales Orders. There's probably a shorter way of doing it - these were some of my earlier BPMs - but it works ok.

The first BPM gets the cost and records it in a UD field. The second BPM then checks the margin.

The first BPM is created as a pre-processing directive against the SalesOrder.Update BO. No conditions, just one action "sychronously execute 4GL code record nothing". The code is:

Find first ttOrderDtl where ttOrderDtl.DemandHeadSeq = 0 and (ttOrderDtl.RowMod = 'A' or ttOrderDtl.RowMod = 'U') NO-LOCK NO-ERROR.
If available ttOrderDtl then do:

DEFINE VARIABLE UnitCost AS DECIMAL NO-UNDO.
DEFINE VARIABLE Margin as DECIMAL NO-UNDO.
DEFINE VARIABLE UnitDisc as DECIMAL NO-UNDO.

UnitCost=0.
Margin=0.
UnitDisc=0.

Find PartCost WHERE ttOrderDtl.Company = PartCost.Company AND ttOrderDtl.PartNum = PartCost.PartNum NO-LOCK NO-ERROR.
if available PartCost then do:
UnitCost = PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost.
UnitDisc = ttOrderDtl.Discount / ttOrderDtl.OrderQty.
Margin = (ttOrderDtl.UnitPrice - UnitCost - UnitDisc) / (ttOrderDtl.UnitPrice - UnitDisc) * 100.
Assign ttOrderDtl.Number01 = UnitCost.
Assign ttOrderDtl.Number02 = Margin.
end.
end.

The second BPM is also a pre-processing directive against the SalesOrder.Update BO. Make sure that the second BPM has a higher "Order" set that the first one.

There's two conditions on this:
1. the OrderDtl.Number02 field of the changed row is less than the the 30 value
2. and the OrderDtl.OrderLine field of the changed row is not equal to the 0 value

Remember we're checking for a too-low margin, not a negative margin. So you may want to change 30 to 0.

We then have two actions: display information message and send an email. You could raise an exception instead to prevent the order being saved.

HTH,

Nigel.

--- In vantage@yahoogroups.com, Michelle de la Vega <mdelavega@...> wrote:
>
> I am trying to write a query that identifies parts with selling prices lower than our cost at the sales order level. And I am very new to writing queries. So that being said, how do I compare the ttorderdtl.unitprice field to the sum of the average costs from the part cost table? I was hoping it would be something simple like ttorderdtl.unitprice < (avgburden + avglabor + avgmtl etc) but when I try this I get an error. Is there a sum function that I can use to do this for me?
>
> Any help would be greatly appreciated.
>
> Michelle de la Vega
> Business Applications Manager
> Cold Jet, LLC
> 455 Wards Corner Road
> Loveland, Ohio 45140
> USA
> +1 513-716-6400 (office)
> +1 513-382-3281 (mobile)
> +1 513-831-1209 (fax)
> www.coldjet.com<http://www.coldjet.com>
> [cid:image001.jpg@...]
>
>
>
> [Non-text portions of this message have been removed]
>

system · September 1, 2009, 2:03pm

Nigel,

Looks like this is going to work for us! Thanks for sharing, I really appreciate it.

Best,

Michelle de la Vega
Business Applications Manager
Cold Jet, LLC
455 Wards Corner Road
Loveland, Ohio 45140
USA
+1 513-716-6400 (office)
+1 513-382-3281 (mobile)
+1 513-831-1209 (fax)
www.coldjet.com<http://www.coldjet.com>
[cid:image001.jpg@...120F90]

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of nigelkerley
Sent: Sunday, August 30, 2009 9:09 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BPM Query

Hi Michelle,

We have something like this is place. We use two BPMs to check the margin on Sales Orders. There's probably a shorter way of doing it - these were some of my earlier BPMs - but it works ok.

The first BPM gets the cost and records it in a UD field. The second BPM then checks the margin.

The first BPM is created as a pre-processing directive against the SalesOrder.Update BO. No conditions, just one action "sychronously execute 4GL code record nothing". The code is:

Find first ttOrderDtl where ttOrderDtl.DemandHeadSeq = 0 and (ttOrderDtl.RowMod = 'A' or ttOrderDtl.RowMod = 'U') NO-LOCK NO-ERROR.
If available ttOrderDtl then do:

DEFINE VARIABLE UnitCost AS DECIMAL NO-UNDO.
DEFINE VARIABLE Margin as DECIMAL NO-UNDO.
DEFINE VARIABLE UnitDisc as DECIMAL NO-UNDO.

UnitCost=0.
Margin=0.
UnitDisc=0.

Find PartCost WHERE ttOrderDtl.Company = PartCost.Company AND ttOrderDtl.PartNum = PartCost.PartNum NO-LOCK NO-ERROR.
if available PartCost then do:
UnitCost = PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost.
UnitDisc = ttOrderDtl.Discount / ttOrderDtl.OrderQty.
Margin = (ttOrderDtl.UnitPrice - UnitCost - UnitDisc) / (ttOrderDtl.UnitPrice - UnitDisc) * 100.
Assign ttOrderDtl.Number01 = UnitCost.
Assign ttOrderDtl.Number02 = Margin.
end.
end.

The second BPM is also a pre-processing directive against the SalesOrder.Update BO. Make sure that the second BPM has a higher "Order" set that the first one.

There's two conditions on this:
1. the OrderDtl.Number02 field of the changed row is less than the the 30 value
2. and the OrderDtl.OrderLine field of the changed row is not equal to the 0 value

Remember we're checking for a too-low margin, not a negative margin. So you may want to change 30 to 0.

We then have two actions: display information message and send an email. You could raise an exception instead to prevent the order being saved.

HTH,

Nigel.

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Michelle de la Vega <mdelavega@...> wrote:
>
> I am trying to write a query that identifies parts with selling prices lower than our cost at the sales order level. And I am very new to writing queries. So that being said, how do I compare the ttorderdtl.unitprice field to the sum of the average costs from the part cost table? I was hoping it would be something simple like ttorderdtl.unitprice < (avgburden + avglabor + avgmtl etc) but when I try this I get an error. Is there a sum function that I can use to do this for me?
>
> Any help would be greatly appreciated.
>
> Michelle de la Vega
> Business Applications Manager
> Cold Jet, LLC
> 455 Wards Corner Road
> Loveland, Ohio 45140
> USA
> +1 513-716-6400 (office)
> +1 513-382-3281 (mobile)
> +1 513-831-1209 (fax)
> www.coldjet.com<http://www.coldjet.com>
> [cid:image001.jpg@...]
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]

system · January 20, 2015, 7:43pm

I have written a BPM to transfer the CheckBox01 field from the OrderHead table to the InvcHead table. I have created a post processing BPM on ARInvoice.Update. The code is below. For some reason the code does not transfer the data.

Any suggestions please or other ways of doing this.

Any help would be gratefully appreciated.

Mark Betts

find first ttInvcHead where ttInvcHead.Company = CUR-COMP and
ttInvcHead.RowMod <> "" and ttInvcHead.RowMod <> "D" no-lock no-error.

if available ttInvcHead then
do:
find first orderhed where ttInvcHead.OrderNum = OrderHed.OrderNum no-lock
no-error.
ttInvcHead.CheckBox01 = OrderHed.CheckBox01.

end.

system · January 20, 2015, 8:02pm

Mark,

Did you try with OR instead of AND in your where clause?

Sent from my Verizon 4G LTE Smartphone

----- Reply message -----
From: "Mark Betts mbetts1953@... [vantage]" <vantage@yahoogroups.com>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Subject: [Vantage] BPM Query
Date: Tue, Jan 20, 2015 6:47 PM

I have written a BPM to transfer the CheckBox01 field from the OrderHead table to the InvcHead table. I have created a post processing BPM on ARInvoice.Update. The code is below. For some reason the code does not transfer the data.

Any suggestions please or other ways of doing this.

Any help would be gratefully appreciated.

Mark Betts

find first ttInvcHead where ttInvcHead.Company = CUR-COMP and
ttInvcHead.RowMod <> "" and ttInvcHead.RowMod <> "D" no-lock no-error.

if available ttInvcHead then
do:
find first orderhed where ttInvcHead.OrderNum = OrderHed.OrderNum no-lock
no-error.
ttInvcHead.CheckBox01 = OrderHed.CheckBox01.

end.

system · January 20, 2015, 8:06pm

#ygrps-yiv-1849370494 P.ygrps-yiv-1849370494b1e7fd89-0279-4ffe-a16a-1f1bcba0d537 { MARGIN:0cm 0cm 0pt;} #ygrps-yiv-1849370494 LI.ygrps-yiv-1849370494b1e7fd89-0279-4ffe-a16a-1f1bcba0d537 { MARGIN:0cm 0cm 0pt;} #ygrps-yiv-1849370494 DIV.ygrps-yiv-1849370494b1e7fd89-0279-4ffe-a16a-1f1bcba0d537 { MARGIN:0cm 0cm 0pt;} #ygrps-yiv-1849370494 TABLE.ygrps-yiv-1849370494b1e7fd89-0279-4ffe-a16a-1f1bcba0d537Table { MARGIN:0cm 0cm 0pt;} #ygrps-yiv-1849370494 DIV.ygrps-yiv-1849370494Section1 { }

Mark,

This should be implemented as a DataDirective.

Also when you read a real table such as OrderHed, always include the company clause or covering columns of an index.

Also if you do a no-error your next line should be if available OrderHed then DO:â€¦

You may also want to only perform the lookup if ordernum > 0 save an additional lookup.

Finally instead of causing a select *, you can utilise the fields clause to only project the columns you need.

Regards,

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: 21 January 2015 00:43
To: vantage@yahoogroups.com
Subject: [Vantage] BPM Query

I have written a BPM to transfer the CheckBox01 field from the OrderHead table to the InvcHead table. I have created a post processing BPM on ARInvoice.Update. The code is below. For some reason the code does not transfer the data.

Any suggestions please or other ways of doing this.

Any help would be gratefully appreciated.

Mark Betts

find first ttInvcHead where ttInvcHead.Company = CUR-COMP and
ttInvcHead.RowMod <> "" and ttInvcHead.RowMod <> "D" no-lock no-error.

if available ttInvcHead then
do:
find first orderhed where ttInvcHead.OrderNum = OrderHed.OrderNum no-lock
no-error.
ttInvcHead.CheckBox01 = OrderHed.CheckBox01.

end.

Stephen Edginton (Beng) | Solutions & Technical Director

Mobile:	+44 7734 858415
DDI:	+44 121 314 1179
Main:	+44 (0) 121 314 1170
Web:	www.dotnetit.co.uk

Dot Net IT Limited, One St Kenelm Court, Steel Park Road, Halesowen, B62 8HD, United Kingdom.

Epicor Platinum Partner

Epicor Alliance Partner