• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Is there someone who already build a SQL query to load base prices in the P4106?

SyrahFab

Member
Hello everybody,

First time I am really playing with items, costs and prices....
Items mass upload is ok, costs also (via interop).

But what about prices (F4106) I have 4000 items that are waiting to get a price....

Anyone can help me building a sql statement that will fill the F4106 and all that need to be filled....

Thanks!

Fab.
 

JMast

Reputable Poster
Hello Fab,

One way to this is the following. It is very tough to just explain in words and without knowing what tools you have, so I hope this helps. It also depends how many unique branches, customers, dates are in your price list.
1. Identify the differences in needed prices - different branch plants, different customers, different dates, etc...
2. Enter 1 price record for each of the differences in JDE.
3. Use a sql tool to retrieve all the columns for those entered records.
4. You can then take your list of items and prices and sort them to identify which of your JDE created records is a match on the differences.
5. create a new record for each item price that includes all the remaining columns from the correct JDE record
6. load the newly created records into F4106.

small example of how I would do one.
- 50 new records where all of the fields are the same except item number and price.
1. create one of the item price records in JDE manually
2. using MS Access, I would get that record into a temporary table.
3. create a second Access table with the list of short item, item, price for all my items except the one I already entered in JDE
4. create a new query that has both of my new tables but no join. This will cause Access to create a row for each possible combination in the two tables.
5. add the short item, item, price columns from the table with just the unique 49 items to the query.
6. add all the columns EXCEPT short item, item, price from the table that has the one JDE record
7. run the query I get 49 rows each one having a unique short item, item, price and all the other columns from the JDE record
8. append this list of records to the F4106 in JDE.

If you do these steps for each of your combinations, you should end up with good pricing records in JDE.

Jer
 
Top