The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
sales orders and opportunities
Posted: 12 Apr 08 1:30 PM
|
Does anyone have any ideas or solutions for the following:
When a sales order status is changed to "Closed" that the opportunity products are updated to match the salesorderitems.
Thanks,
dave |
|
|
|
Re: sales orders and opportunities
Posted: 12 Apr 08 4:37 PM
|
| You could create a SQL trigger on the Sales Order table that looks for inserts or updates to the Status field and then checks oportunity products and sales orderitems tables to make sure they're the same. |
|
|
|
Re: sales orders and opportunities
Posted: 13 Apr 08 12:23 AM
|
| If you create a SQL trigger, don't you end up circumventing the SLXOLEDB provider, and hence cause issues with syncing of remotes? |
|
|
|
Re: sales orders and opportunities
Posted: 13 Apr 08 12:31 AM
|
Yes, that is true date created via a SQL trigger will not sync to the remotes, so you'd have to run an agent on your sync server to check for data entered via the trigger (add a field and mark it false until the Agent grabs the data) and then write it to the database in a manner in which the SalesLogix provier will create TEF files for it (i.e. load the data into a record set and re-write it to the database).
You could also use a third party provider like Scribe or KnowledgeSync to do the same thing, and by pass the Agent strategy, because those tools create the TEF files for you. |
|
|
|
Re: sales orders and opportunities
Posted: 13 Apr 08 8:04 AM
|
I think this is just the reverse code for when a sales order is created. All of the opportunity products records are used to create salesorderitems. Can't you just do the reverse? When "Closed", delete OpportunityProducts and then insert based upon the salesorderitems. This would be my strategy.
Daryl |
|
|
|
Re: sales orders and opportunities
Posted: 13 Apr 08 11:27 AM
|
Well..
Just because how Sales Order(s) are a "subset" of Opportunity Prodcuct (You cannot have anything in a Sales Order if it does not exist in Opportunity product.. and you cannot have quantities that exceed the Opportunity Product..
Than like Daryl says.. it's sort of a "reverse..."..
Sounds like a "simple" vbscript that runs off Opp Close to properly update opp product...
BUT.. here's the snag.. WHICH Sales Order? ... I suggest a mod to the Sales Order table and put in a "primary" flag that's tied to a checkbox on the SO datagrid to flag it... (we did that to be able to properly drive quoting for one client).
--
RJLedger - rjlSystems |
|
|
|
Re: sales orders and opportunities
Posted: 13 Apr 08 8:41 PM
|
An Opportunity that is closed is NOT the same as the actual SalesOrder. I am interested to know why someone would want to destroy historical data - there are always reasons I have not thought of  . The ability to identify trends in the differences between opportunities and resulting salesorders is useful information. It helps train the sales team to better enter opportunities if nothing else.
There are also scenarios where you may have multiple salesorders generated from the same opportunity. Therefore pushing data into the Opportunity could contradict those other associations.
If this is a must here are some suggestions:
1. Associate each SalesOrder to an Opportunity
2. Associate each SalesOrderDetail to an Opportunity_Product
3. On close of SalesOrder
a. Update matching Opportunity_Product records
b. Insert Opportunity_Product records and backfill the SalesOrderDetail.OppProductID foreign key if the SalesOrderDetail was added to the SalesOrder after initial creation. This allows users to do their job without jumping through hoops that can be solved with automation.
c. Delete Opportunity_Product records that do not have an associated SalesOrderDetail record.
4. Do not use triggers. As RJL says, this can all be done in the SalesLogix Architect - assuming the SalesOrder is closed within SalesLogix.
Timmus
|
|
|
|
Re: sales orders and opportunities
Posted: 14 Apr 08 12:35 AM
|
Hi guys, Timmus, RJL and others all make good points and it somewhat depends on the business need. In our QuoteWizard for SalesLogix product we add a checkbox to the Sales Order form that allows the user to overwrite all the Opportunity Products from the Sales Order/Quote record when it is saved. This is not dependent on the Sales Order Status but the concept is the same. We go the route of replacing all Opportuninty Products and pricing from the Sales Order.
Unlike the limitations that RJL pointed out in standard SalesLogix, we do not limt the user to the Quote/Sales Order being a subset of the Opportunity. The Quote/Sales Order can be whatever the user needs (Say multiple quotes must be issued for the Opp. for different scenarios for the customer to choose from but only one will be ordered. User can choose which quote to update the Opp from)
So, yes, you can do this through script but do avoid triggers as others have indicated since the data will not synch (if that is relevant to you)
Michael |
|
|
|
Re: sales orders and opportunities
Posted: 14 Apr 08 8:05 AM
|
Timmus, Michael.... exactly!.....
Nice "plug" for QuoteWizard.. in fact anytime anyone has asked me about a "quote package".. I've said QW is the way to go...
--
RJLedger - rjlSystems |
|
|
|
Re: sales orders and opportunities
Posted: 14 Apr 08 8:07 AM
|
| Dave, you could consider TaskCentre here too. It will let you build the trigger but write data back through the provider so not breaking sync. |
|
|
|
Re: sales orders and opportunities
Posted: 14 Apr 08 8:16 AM
|
Thanks guys for all the input. I've been wrestling with this for a week now and have many of the concerns brought up. I forgot to add one big "gotcha" on this and that is that we have a option to edit an "extended description" to each opp product. I think what I may have to do is go through the opp products line by line and compare them to the SO items. If the item is present, update the quantity and adjusted price. If the item isn't in the SO items, then change the opp product quantity to 0. This will be a really booger  though.
thanks,
dave |
|
|
|