I have a requirement to identify the selling price of an item/location on a daily basis (based on the price changes or clearances that becomes effective on that day). I need to get the list of item/locs and their selling price of only those which are undergoing a change in price on that day. I am trying to understand how this information gets updated in ITEM_LOC on a daily basis from RPM so that I can leverage the similar logic or tables.
Could you please provide some details on this or on the Price event execution RMS batch? Where does it calculate the selling retail from? Does this information get written into any tables after the PriceEventExecution batch from where the RMS batch picks it from?
How is RPM_EVENT_ITEMLOC used? Does this table get populated whenever there is a price change or clearance that is taking effect on a day?
At our site we put quite some effort in getting this right, we had to send the changes a day before effect to our POS.
If the requirements allow, you could use the rms.price_hist table to get some information about regular, promotional and clearance (price) changes, and a few more varieties. This table is filled by I believe the priceeventexecution batch.
One could also access the (deprecated/bypassed) RIB topics to get the changes.
Also studying the RPM download SQL script for POS (see the RPM Operations Guide) may help in getting those changes.
The hardest thing is to get future prices correctly, as the RPM datamodel is a bit tough, e.g. in case your POS requires to receive price records one or more days in advance of the effective date.
We also implemented an Oracle Stream based feature, Change Data Capture (CDC, based on Logminer, read the online redologs and posting any changes to a table to a special change table, recording, old and new images of the rows for updates, the new image for an insert and the old image for a delete) on e.g. rpm_future_price table to process all the changes and report them to our stores at the moment the price changes was approved.
There may be simpler ways, but this is what we came up with.
But if you can wait untill the price changes have gotten into effect and your pricing model is not too complex (re. complex mix&match promotions), price_hist may be the easy way out, or the vanilla RPMtoPOS scripts.
I agree here with Erik,
the simplest way would be to check the active prices (action_date=vdate) from the rpm_future_retail table, and sent them to the any Legacy or whatever.
If your customer tries to manage the Retail prices at Zone level, then the rpm_zone_future_retail maybe used.
If I remember correct, RMSPriceEvenExecution batch is responsible to actualize the selling prices in the rpm_future_retail. (with rpm_zone_future_retail it is little bit different).
So, you either have to wait until last run of the batch to extract the prices or implement incremental approach to sent the retail prices.
For the clearances there should be additional table...