SQL for Customer Price Group (PRGP)

[mtbinpa]

I have about 800 items that I would like to get the unit price for each price group. We have 6 regions with price groups attached to each one and there are problems with some of the prices being incorrect. I would like to create a query that allows me to view the ITEM, PRICE GROUP, UNIT PRICE, EFFECTIVE DATES. The PRGP field is not stored in the F4106 and was wondering where JDE stores the customer price group. Currently, I am using the F4201, F4211, and F4106 and using the PRGP from the F4201, but I know there must be a faster way to do this. Thanks!!

 

[Eric Lehti]

compile F4106LZ logical over F4106 so that you can get the current base (list) price:
A R I4106 PFILE(F4106 )
A TEXT('LF- Item, EXP Date')
A K BPITM
A K BPMCU
A O BPEXDJ CMP(NE 120365) <- current expiry date
A S BPMCU CMP(EQ 'your branch')



Go to Branch/Plant Constants (program P41204) and press immediately press the F10 function key to get to the 4009W System Constants screen, then press F10 function key again to get to the 40091W screen (Pricing Constants). This screen tells you if you are on Standard Pricing or are on Advanced Pricing.

If you are on Standard Pricing, study menu G4222. If on Advanced Pricing, study menu G42311.

 

[JMast]

If I understand the question, you are looking for CPGP on the F03012 Customer Master By Line of Business.

 

[mtbinpa]

I guess I am confused by the response. I am looking more for SQL that pulls this information in for me. I can get prices from F4106 but it doesn't give me the Customer Price Group associated with it. The P4210 must have logic in the program that takes the Customer Price Group from the F4201 and uses that to get the correct price. So I am looking to see if anyone has just used SQL to extract this type of information. For exmaple:

SELECT BPLITM, BPEFTJ, BPEXTJ, BPUPRC
FROM F4106

So this gives me the Item #,effective dates, and price information. Now I just need to somehow pull in the Customer Price Group and I will be set. Thanks!

 

[JMast]

I wasn't sure I understood the question and I was right. Fencebuilder has you on the right track. You are talking about pulling info from the advanced pricing tables which can get complicated depending on your setup. The tables you need are probably F4071 and F4072.

The easiest way we have found to check pricing is:
1) create a spreadsheet with the list of items
2) find a customer with the correct price level/group
3) start a new sales order for that customer and import the item list.
4) export the grid to excel for analysis and review
5) and..most important... cancel out of the order!

Jer

 

[TerryWin]

[ QUOTE ]
The P4210 must have logic in the program that takes the Customer Price Group from the F4201 and uses that to get the correct price.

[/ QUOTE ]

A cursory check of program P4210 indicates that the price group (SHPRGP) is populated from the Customer Master (F0301) field A5CPGP based on the Sold-To Address Number (SHAN8). This is for A73 Cum 12.

Terry

 

[dhuggins]

This information is from my experience on A73 cum 11. I have had to retrieve prices and have followed these steps:
1. Retrieve the customer price group (A5CPGP) from F0301.
2. Retrieve the item price group (IBPRGR) from f4102.
3. Using fields A5CPGP & IBPRGR, I retrieve the Item/Customer Key ID (KIICID) from file F4094 (Item/Customer Key ID Master File) where kicpgp = a5cpgp and kiprgr = ibprgr.
4. Retrieve the unit price (BPUPRC) from F4106 where bpicid = kiicid and bpeftj <= 108207 (today) and bpexdj >= 108207 (today).
5. If no unit price is retrieved in the above steps, I retrieve the unit price from F4106 by item number & business unit.

 

[Eric Lehti]

Mtbinpa, from your signature it appears that you are on JDE Enterprise,
not World. Those of us on this World forum do not use Enterprise.

 

[JMast]

Oops. I saw this post on the hot list on the main page and didn't pay attention to the forum. It wasn't until after I read one of the other reponses that I realized I wandered off.

I will go back to my hole in E1 now.

 

[mtbinpa]

Re: RE: SQL for Customer Price Group (PRGP)

[ QUOTE ]
Mtbinpa, from your signature it appears that you are on JDE Enterprise,<br>not World. Those of us on this World forum do not use Enterprise.<br><br><br>

[/ QUOTE ]

I am working for a company that runs both World and One World. The World side is moving up to One World, and the One World is moving up to 8.12 (well, maybe).

Thank you all for your suggestions and help with this. I apologize if my requirements were unclear. I tried running a query with the F0301, F4102, F4094, and F4106 as suggested but the performance was pretty slow. I did just a basic query on the F4072 and it gave me what I was looking for (Item Number, Customer Price Group, Effective Dates, Price). I did some spot checking in the P4106 and the information looks good.

Thanks again everyone!!!!