Any ideas on the UserFile query?
--- In vantage@yahoogroups.com, "johngeh" <johngeh@...> wrote:
>
> After staring at this for the past month I figured part of it out - I needed to use DocUnitPrice instead of UnitPrice. I was getting the odd behavior before because UnitPrice wasn't getting updated with the changed unit price.
>
> So now that I have that figured out, I have run into another problem. Once I got this working, I spoke with the people that would be using it and they pointed out that based on the market prices, they sometimes do have to sell things below our cost. I need to implement Jose's suggestion below that certain people can sell items below cost.
>
> I looked at it and think that I have a good solution. In the UserFile table, I will use the checkbox01 field to indicate users that are allowed to generate sales orders with line items with a unit price less than our cost. To implement this, I will add a condition to the BPM that I created. The query will return a row when the user is NOT a member of this group. So if the user reduces the unit price below our cost and they are not a member of the group, the rule will be triggered generating an exception.
>
> The problem is that I am having trouble with the query again (why didn't they just use SQL?). Here is what I have:
>
> FOR EACH ttOrderDtl ,
> EACH OrderHed WHERE ttOrderDtl.Ordernum = OrderHed.OrderNum
> For EACH OrderHed,
> EACH UserFile WHERE OrderHed.EntryPerson = UserFile.DcdUserID
> AND Userfile.CheckBox01 = False
>
> What I am trying to do here is to get the order number to get the user id of the person that created it. From there, I check the CheckBox01 field to see if the user is a member of the group that can reduce unit costs below our cost. The query works (i.e. it executes without error) but when I create a sales order using a user that is not a member of the group, it works so I know that the query above is not returning the result that I would expect it to. If I could do this query in SQL, this is what I would have:
>
> SELECT *
> FROM OrderHed JOIN UserFile ON OrderHed.EntryPerson = UserFile.DcdUserID
> WHERE OrderHed.OrderNum = ttOrderDtl.OrderNum
> AND UserFile.CheckBox01 = False
>
> If anyone could tell me how to make this query work the way I am expecting it to, I would appreciate it.
>
> Thanks.
>
> John
>
>
> --- In vantage@yahoogroups.com, "Linda Lowney" <llowney@> wrote:
> >
> > Hi John,
> >
> >
> >
> > If someone else can see anything wrong with the query, please jump in.
> >
> >
> >
> > Try using “raise an exception†instead of “display an informational message.†Raise an exception will not allow the user to proceed until they type in a correct price.
> >
> >
> >
> > Regards,
> >
> > Linda
> >
> >
> >
> > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of johngeh
> > Sent: Friday, October 08, 2010 4:23 PM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] Re: How Do I Control Who Can Update a Sales Price?
> >
> >
> >
> >
> >
> > The problem is that if I enter an incorrect unit price (the updated unit price is less than 75% of the Part.UnitPrice) once, it is accepted. If I update it again, it gets rejected. It should get rejected the first time. For example, if the Part.UnitPrice field for a line item is 1.00, when I change it to 0.70, it is accepted. If I change it to 0.71, it is rejected. I tried a different scenario as well. If I enter a good number in the first time, it is accepted. If I enter a bad number in, it is accepted. If I enter in another bad number, it is rejected. It seems to accept one bad number and then rejects the next one.
> >
> > One other thing - I noticed that when it rejects a number and shows the informational message, it still accepts the number. How can I program it so that when a number is rejected, it reverts the unit price field back to the original unit price (or maybe the minimum value that I will allow the user to enter)?
> >
> > I should note that we aren't using the discount field. My sales people simply change the unit price field in the sales order line record.
> >
> > Here is the query:
> >
> > FOR EACH ttOrderDtl ,
> > EACH Part WHERE (ttOrderDtl.RowMod = 'A' or ttOrderDtl.RowMod = 'U')
> > AND Part.Company = ttOrderDtl.Company
> > AND Part.PartNum = ttOrderDtl.PartNum
> > AND (ttOrderDtl.UnitPrice < (Part.UnitPrice * .75))
> >
> > On the action, I am showing an informational message.
> >
> > I am so close!
> >
> > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Linda Lowney" <llowney@> wrote:
> > >
> > > Hi John,
> > >
> > >
> > >
> > > I'm not clear on what is wrong. I just tried it again entering several
> > > different amounts and then saving and I got the message every time it
> > > was below 75%. Can you post your modified query? Oh, and did you pick
> > > "show informational message" or "raise exception" in the Actions portion
> > > of the BPM?
> > >
> > >
> > >
> > > Linda
> > >
> > >
> > >
> > > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf
> > > Of johngeh
> > > Sent: Friday, October 08, 2010 2:58 PM
> > > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > > Subject: [Vantage] Re: How Do I Control Who Can Update a Sales Price?
> > >
> > >
> > >
> > >
> > >
> > > Linda - thank you for providing the query. I had to change it a bit as I
> > > wasn't using the discount field. I simply check the unit cost field in
> > > the changed record against.
> > >
> > > Jose - thanks for the suggestion. I like your idea so as soon as I get
> > > this working, I am going to see if I can implement your functionality.
> > >
> > > I have kind of got this working. I implemented the logic and it was
> > > accepted. To test it, I created a sales order and added a line. When I
> > > go to the unit price field, I tried entering a value that was less than
> > > 75% of the retail price. It worked. I can save the order and it works.
> > > But... if I try to change the unit price again, I get the error message
> > > that I configured.
> > >
> > > Why is it allowing the user to enter a price that doesn't meet the BPM
> > > criteria? The way that this is working seems very strange to me.
> > >
> > > I think that it might have something to do with a record being added
> > > versus one being updated. Am I missing something in the query?
> > >
> > > I am very, very close to getting this working so thank you for your help
> > > on this.
> > >
> > > John
> > >
> > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> , Jose
> > > Gomez <jose@> wrote:
> > > >
> > > > Get got a bit fancier,
> > > >
> > > > We set a permissions flag on the User Record (CheckBox01) that allows
> > > update
> > > > Sales Price, then on the OrderScreen, if you don't have that CheckBox,
> > > the
> > > > Update PRice fields are disabled, there is an override checkbox that a
> > > > supervisor can check on an indiviadual order that will allow them a
> > > one time
> > > > change to the price per order.
> > > >
> > > > All a bunch of customizations basically.
> > > >
> > > >
> > > > Sincerely
> > > > Jose C Gomez
> > > >
> > > > http://www.josecgomez.com
> > > >
> > > >
> > > > On Thu, Oct 7, 2010 at 9:22 AM, Linda Lowney <llowney@> wrote:
> > > >
> > > > >
> > > > >
> > > > > Hi John,
> > > > >
> > > > >
> > > > >
> > > > > Assuming the sales unit price is in the part master give this a try.
> > > Create
> > > > > a Pre-Processing directive, Conditions -- number of rows in the
> > > designed
> > > > > query is not less than 1. The query -- for each ttOrderDtl , each
> > > part where
> > > > > (ttOrderDtl.RowMod = 'A' or ttOrderDtl.RowMod = 'U') and
> > > Part.Company =
> > > > > ttOrderDtl.Company and Part.PartNum = ttOrderDtl.PartNum and
> > > > > ((ttOrderDtl.UnitPrice - ttOrderDtl.Discount) < (Part.UnitPrice *
> > > .75))
> > > > >
> > > > >
> > > > >
> > > > > In actions you can either display a message or raise an exception to
> > > stop
> > > > > them from proceeding.
> > > > >
> > > > >
> > > > >
> > > > > Regards,
> > > > >
> > > > > Linda
> > > > >
> > > > >
> > > > >
> > > > > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
> > > <vantage%40yahoogroups.com> [mailto:
> > > > > vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
> > > <vantage%40yahoogroups.com>] On Behalf Of johngeh
> > > > > Sent: Wednesday, October 06, 2010 9:48 PM
> > > > > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
> > > <vantage%40yahoogroups.com>
> > > > > Subject: [Vantage] Re: How Do I Control Who Can Update a Sales
> > > Price?
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > If anyone has an answer or suggestion on how to proceed, I would
> > > greatly
> > > > > appreciate it.
> > > > >
> > > > > Thanks.
> > > > >
> > > > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
> > > <vantage%40yahoogroups.com> <mailto:
> > > > > vantage%40yahoogroups.com <vantage%2540yahoogroups.com>> , "johngeh"
> > > > > <johngeh@> wrote:
> > > > > >
> > > > > > Hi David,
> > > > > >
> > > > > > To answer your first question - we are running 8.03.403D.
> > > > > >
> > > > > > I have access to a BPM class student manual and took a look
> > > through it. A
> > > > > BPM is definitely what I need but I am still having difficulty
> > > figuring out
> > > > > how to implement my business logic.
> > > > > >
> > > > > > I want my end users to have some flexibility in reducing the unit
> > > cost in
> > > > > a sales order. For parts.salesunitprice, we us a 50% markup over
> > > cost. A lot
> > > > > of our customers are contractors so we need to give them a discount.
> > > The
> > > > > problem is that occassionally (very rarely), we accidently mark it
> > > down to
> > > > > where we are making nothing. I want to give my end users the
> > > flexibility
> > > > > they need to get the sale but not mark items down too low. The
> > > business
> > > > > logic should be something to the effect that they can't update the
> > > sales
> > > > > price to less than 75% of the original sales price. (i.e. we
> > > purchase for
> > > > > $1, retail is $1.50, sales price cannot be changed to anything lower
> > > that
> > > > > $1.125).
> > > > > >
> > > > > > After reviewing the BPM student manual and looking through my
> > > system. I
> > > > > am pretty sure that I need a post process on the
> > > SalesOrder.ChangeUnitPrice
> > > > > method. I took a look at the conditions and I am pretty sure that I
> > > need to
> > > > > use the "number of rows in the designed query is not less than 1.
> > > The other
> > > > > queries that I looked at required a specified value to be used and
> > > the value
> > > > > would be dependent upon the original salesunitprice so I don't know
> > > what the
> > > > > specified value is.
> > > > > >
> > > > > > The problem that I have run into is how do I write the designed
> > > query? I
> > > > > haven't written a 4GL query in Epicor before so I don't know where
> > > to start.
> > > > > If I had to write it in SQL, I would do something like this...
> > > > > >
> > > > > > SELECT O.PartNum
> > > > > > FROM OrderDtl O INNER JOIN Part P
> > > > > > ON O.PartNum = P.PartNum
> > > > > > WHERE {O.UnitPrice} > (P.SalesUnitPrice * .75)
> > > > > >
> > > > > > Where {O.UnitPrice} is the value that the user is attempting to
> > > change
> > > > > the unit price to. If the updated value is greater than 75% of the
> > > original
> > > > > sales price, the query will return 1 row. If it is less than 75%, it
> > > will
> > > > > return 0 rows.
> > > > > >
> > > > > > Also, is there a way to exempt certain users or groups from this
> > > BPM? For
> > > > > example, I might want to change a price to less than my cost to get
> > > rid of
> > > > > certain inventory or to help a customer out with a job. How do I
> > > exempt
> > > > > myself from this business logic.
> > > > > >
> > > > > > If someone could help me with this query I would really appreciate
> > > it.
> > > > > >
> > > > > > Thanks.
> > > > > >
> > > > > > John
> > > > > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
> > > <vantage%40yahoogroups.com> <mailto:
> > > > > vantage%40yahoogroups.com <vantage%2540yahoogroups.com>> ,
> > > "nustepvantage"
> > > > > <dpfiester@> wrote:
> > > > > > >
> > > > > > > Hi John,
> > > > > > >
> > > > > > > What version of the product are you running? There is a
> > > PowerPoint
> > > > > presentation on BPM's posted under the Files > BPM's folder on this
> > > group
> > > > > site that might of help.
> > > > > > >
> > > > > > > Regards,
> > > > > > > David
> > > > > > >
> > > > > > >
> > > > > > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > > <mailto:vantage%40yahoogroups.com> <vantage%40yahoogroups.com> <mailto:
> > > > > vantage%40yahoogroups.com <vantage%2540yahoogroups.com>> , "johngeh"
> > > > > <johngeh@> wrote:
> > > > > > > >
> > > > > > > > I am not familar with BPM's. Can you point me to where I can
> > > learn
> > > > > more about them and what functionality I can use to implement this
> > > feature?
> > > > > > > >
> > > > > > > > Thanks.
> > > > > > > >
> > > > > > > > John
> > > > > > > >
> > > > > > > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > > <mailto:vantage%40yahoogroups.com> <vantage%40yahoogroups.com> <mailto:
> > > > > vantage%40yahoogroups.com <vantage%2540yahoogroups.com>> ,
> > > advantage@wrote:
> > > > > > > > >
> > > > > > > > > There are many ways to address this
> > > > > > > > >
> > > > > > > > > Are you familiar with BPMs
> > > > > > > > > Sent from my Verizon Wireless BlackBerry
> > > > > > > > >
> > > > > > > > > -----Original Message-----
> > > > > > > > > From: "johngeh" <johngeh@>
> > > > > > > > > Sender: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > > <mailto:vantage%40yahoogroups.com> <vantage%40yahoogroups.com><mailto:
> > > > > vantage%40yahoogroups.com <vantage%2540yahoogroups.com>>
> > > > > > > > > Date: Fri, 24 Sep 2010 15:56:38
> > > > > > > > > To: <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > > <mailto:vantage%40yahoogroups.com> <vantage%40yahoogroups.com> <mailto:
> > > > > vantage%40yahoogroups.com <vantage%2540yahoogroups.com>> >
> > > > > > > > > Reply-To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > > <mailto:vantage%40yahoogroups.com> <vantage%40yahoogroups.com><mailto:
> > > > > vantage%40yahoogroups.com <vantage%2540yahoogroups.com>>
> > > > > > > > > Subject: [Vantage] How Do I Control Who Can Update a Sales
> > > Price?
> > > > > > > > >
> > > > > > > > > What do I need to do to control who can update a sales price
> > > in a
> > > > > sales order? There must be some way to control who can change the
> > > sales
> > > > > price and by what amount.
> > > > > > > > >
> > > > > > > > > For example, I might want to give me full control but I
> > > might want
> > > > > to give a sales person the ability to reduce the sales price by up
> > > to 30% to
> > > > > get the sale.
> > > > > > > > >
> > > > > > > > > One thing that I know that I need to do is make it so that
> > > only I
> > > > > can reduce the sales price to something less then the cost.
> > > > > > > > >
> > > > > > > > > Any help or suggestions are appreciated.
> > > > > > > > >
> > > > > > > > > Thanks.
> > > > > > > > >
> > > > > > > > > John
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > [Non-text portions of this message have been removed]
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > [Non-text portions of this message have been removed]
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>