IM_PriceCode issues with Visual Integrator

Hello!

I am having a problem importing records with Visual Integrator using the IM_PriceCode table.  HELP!

In the past, we have used this table to manually import customer pricing, basically uploading the CustomerNo, ItemCode, & Price directly into each customer account - using an Import Job that was created.  So now every single item on every single customer is set to "Price Override", with the designated price listed.  This was originally meant to be a solution to our pricing problems - we have a lot of items, and a lot of customers, with a lot of different pricing - but has become more of a bandaid and doesnt seem to actually work moving forward...  every time we add a new item or need to edit pricing for customers (which is often) we have to go through hundreds of customer pricing files, update the one item, then upload the hundreds of customer files individually... its a chore, its inefficient, and it isnt sustainable.

I've spent the last three days researching & learning about how the price codes work, how the price levels work, how the pricing heirarchy of the system works... and think that I have figured out how I would need to set up the accounts to work within the systems current capabilities without this amount of manual maintenance... but now am facing going through each individual account, for each item in our system, and switching it from "Pricing Override" to "Pricing Discount Amount",etc.   

- Right now, each customer account's Pricing Method is set to "O" (for all items) with no regard to the price codes/levels - and I'd like to set it them all to "P" (for all items) so I can figure out a way to set up the price codes/price levels so everything is automatic as we add new items... 
- When run the Customer Pricing Report from the Accounts Receivable Module, it lists that the Price Source is from the "Item/Customer Price" when I think I want it to say "Price Code".
- Also, what is the PriceCodeRecord - is that the Pricing Heirarchy level that is assigned? 

Mind you, I'm testing this Import Job business on only one account, an unimportant account - so if I mess it up I dont mess up anything important... 

So I went back through Visual Integrator, studied the original Import Job that got us in this mess, and tried to duplicate it so I could change back the Pricing Method & restore the other fields to their default settings - I was hoping I could replace the Pricing Method to all accounts in one fell swoop - but every time I try to run the Import Job it tells me that the Customer No is not on file... UGH!  Am I on the right track here, or is there an easier way to do this!?

HELP!


This is all I'm trying to import!

Just one of the couple of error messages I've been getting! Sometimes it says Item Code is Required, or Price Code is required, so I add them as under "assign", but then it will say things like "CustomerNo is not on file" - when it is certainly a valid & active customer no!

Parents
  • 0

    Note that when specifying a customer you have to specify the division number as well.

    AND IT IS  ABSOLUTELY NECESSARY THAT YOU TEST THIS IN A BACKUP COPY OF YOUR DATABASE.

    Your description suggests that you have a rather complex situation where it would be very easy to mess things up if anyone offering advice does not have the full understanding of what is going on.  And even then there would most likely be a bit of trial and error to make sure you got everything correct.

    For that reason,  I'm not going to give you any more specific info and recommend that you work with your Sage partner on this (I wouldn't want to be blamed for messing up your pricing files on limited info).   Spending a little amount for his/her time could save you a lot in the long run.

  • 0 in reply to TomTarget

    Hey Tom,

    Thanks so much for the input!  Yea - I was only testing it on one or two unessential records just to see if I could get it to work for one account without messing up the whole system...

    There's no other way you know of to reverse all this manual uploading we've done to the accounts? I've noticed that even if I go into an item under a customer & switch the Pricing Method manually - it will switch away from Price Override (O then becomes P), but it wont switch it entirely to pull from price codes/levels... Price Source on the Customer Pricing Report still stems from Item/Customer Price & not from the Price Codes.

    I was kind of hoping that if I could figure it out for one account, then I could move from there into setting up an actual price code/price level system that works within our needs. And even then I wouldnt change any of the accounts until I was sure...

    This may sound juvenile, but is it easy to create a back up of the database myself? or do you still suggest I reach out to my Sage partner?

  • 0 in reply to kspain1987

    Making a backup company in company maintenance is not difficult,  however from your posts,  I get the impression that you are not well versed in what you are trying to do here.  

    I would find this challenging in my own right with 25+ years of experience as a Sage partner and I would proceed with caution, so I really think you should talk with your Sage partner.

  • 0 in reply to TomTarget

    You are mostly correct there sir - Certainly not as well versed as you are I'm sure :)  Only as versed as I've needed to be to set up, use, & manage the system for our purposes... But unfortunately I have found that customization is key for all facets of the nature of my particular business - hence the manual uploading in the first place... Just frustrating that one thing works for one thing, but not in all cases apparently. I couldn't even get it through the "Import Job - Test" phase, let alone actually import anything... I had the ARDivisionNo in there, but it keeps rejecting it saying it isnt on file, same as the CustomerNo.  Both of which are listed in the same way on other Import Jobs, and arent ever rejected...

    Thanks for your help.  

  • 0 in reply to kspain1987

    One of the things that makes the price code import difficult is that there are different types of records.  Those for item overrides,  customer overrides and just general overrides and the requirements for each type of record can be different.

    Without getting a hands on,  it is just kind of hard to say where things are going wrong.

  • 0 in reply to TomTarget

    I'll agree with Tom in that you want to get the input of someone experienced to directly help you "hands-on", and that would be your Sage Partner.  Switching pricing methods is complicated enough when using the regular interface.  Manipulating the raw table data with an import just makes it more difficult, unless you understand how the system works.

  • 0 in reply to Kevin M

    Yea, its frustrating.  I was able to figure on my own out that the system reads the PriceCodeRecord as either a 0, 1, or 2...  0 = follows price codes by price level, 1 = follows unique item pricing by price level, 2 = follows customer price schedule for uploaded items - with 2 being the priority in terms of hierarchy when the system reads the data...

    So basically all of my accounts are set at #2, when I want them to be set at #0.... I will have to just manually maintain all records/items that  have been manually uploaded, but set up the price codes/price levels for new customers & new items going forward... I dont see any other way around it.  

  • 0 in reply to kspain1987

    I basically gave up on the visual integration approach that I was trying to accomplish - so no worries on that front with messing anything up...

    It may be counter productive, but I prefer to figure things out on my own, so I understand them & can take care of problems myself... I dont particularly care for how our Sage Consultants handle our issues.  

  • 0 in reply to kspain1987

    Perhaps ask them to teach you how to change pricing methods, instead of doing it for you.  It is possible to change existing customers.

    There also might be a course on Sage University which could help.

  • 0 in reply to Kevin M

    Ahhh.   Important thing was just said about your goal.

    Appears you don't want individual price levels set by each individual customer!   You want various price levels (think pricing groups) that can then be assigned to each customer.

    For example:   Price levels (group) A = 10% discount,   Price level (group) B = 20% discount,   Price level(group) C = 30% discount.    

    Then you assign the price level to each customer.   Is that correct?

    If that's the case your situation is much simpler.   Now you only have three pricing records to maintain instead of a separate record for each and every customer.

    In that case I would reinitialize the pricing code file.   Re-enter each of the price levels as desired.   Assign the price level to each of the customers.

    This does assume that all of your pricing records in the file are customer specific.   There are no others that you do not wish to retain.

    ONCE AGAIN - TEST IN BACKUP FIRST!

Reply
  • 0 in reply to Kevin M

    Ahhh.   Important thing was just said about your goal.

    Appears you don't want individual price levels set by each individual customer!   You want various price levels (think pricing groups) that can then be assigned to each customer.

    For example:   Price levels (group) A = 10% discount,   Price level (group) B = 20% discount,   Price level(group) C = 30% discount.    

    Then you assign the price level to each customer.   Is that correct?

    If that's the case your situation is much simpler.   Now you only have three pricing records to maintain instead of a separate record for each and every customer.

    In that case I would reinitialize the pricing code file.   Re-enter each of the price levels as desired.   Assign the price level to each of the customers.

    This does assume that all of your pricing records in the file are customer specific.   There are no others that you do not wish to retain.

    ONCE AGAIN - TEST IN BACKUP FIRST!

Children
  • 0 in reply to TomTarget

    Yes, I think... But I don't want to assign the price level per customer... if I leave the "Price Level" field blank on the Customer Maintenance (Additional) screen, then it will default to the price code system that I set up.

    I'm trying to sort it by item type (via price code) and then individual price levels from there...

    I just know that I tested this process, by adding a price level for a particular price code to one account (that had individual pricing uploaded previously) and it did not do anything... the items still read the uploaded price, not the price code/price level that I had specified for that price code for that account...

    When I went into that account, into item pricing through Customer Maintenance, it still ready "Price Override" for the item I was testing.  So I switched it to "Price Discount Amount" manually, and I had to then manually enter the Discount Amount, because it didnt automatically pull the price code/price level for that item type...

    Make sense?

    Ugh I hate pricing!

  • 0 in reply to kspain1987

    If I put "Price Level = A" on Customer A, then all items under Customer A will default to Price Level A for all Price Codes.

    But if I want Customer A to follow Price Level A for Item A but Price Level B for Item B, then I can specify under Price Level By Customer Maintenance that I want the account to follow different price levels for different items under that one Customer Account.

    The problem is that I cant use these Price Levels as a blanket price level per account.  It has to be item specific, not customer specific, but usable across many accounts in many different item/price code variations...

  • 0 in reply to kspain1987

    At it's simplest level,  the thing to remember is that there are price levels (customer) and price codes (items) that essentially form a two dimensional grid.

    Getting more complex there are price levels by Customer where you can set price levels and price codes by different product lines as well.

    You do have to turn this last option on in sales order options.

    One way or another you do have to understand what is available and plan it out.

  • 0 in reply to TomTarget

    Also remember that pricing has a hierarchy.  From the help files:

  • 0 in reply to TomTarget

    Yes - this screenshot is precisely what i am working on doing now - leaving that Customer Price Level blank, but designate different price levels by product line & price code...

    the problem that i was trying to fix is like i said - getting items that were manually uploaded to revert to this setting... which i cant seem to do, so any item that was manually uploaded will have to continue to be manually maintained is what it seems.  setting up the price codes/levels by customer this way will only affect new items entered into the system, or items that were never manually adjusted.

  • 0 in reply to kspain1987

    and thank you Kevin - I've been referring to that Hierarchy plenty! thats why i think the manual uploads are superceding everything - because it uploaded it as a customer price schedule...

  • 0 in reply to kspain1987

    That's where reinitializing the price code file comes in.   Essentially you start from scratch.

    A VI job can't delete old records.

  • 0 in reply to TomTarget

    You can delete the overrides through the interface, one by one, but with many records it is impractical.  There should be an easier way, but there isn't.  Wiping out the table and starting from scratch (as Tom suggests) is the fastest way.

  • 0 in reply to Kevin M

    Yessssss - OMG, Thank you - this is what I need to do!  This changed the "Price Source" to Price Code instead of Item/Customer Price just like I wanted!

    Is there a way to do all items under a customer at once? or do I have to do it one by one on each account?

    Thank you thank youuuuuu

  • 0 in reply to kspain1987

    As Tom suggested, you can reinitialize IM_PriceCode if it is not set up as you desire. I would suggest copying to a test company, reinitialize the file in your test company and examine KB47758 which explains how to import Price Overrides for Item Pricing.