Wednesday, October 21, 2015

Get DDL for any object with a query with Oracle

Have you ever wonder how the DB Client tools such as SQL Developer, TOAD and so on..
get the DDL to for you?

Well, I wonder that as well, and it is easy to get, that is META_DATA of the database right?
You can access any object DDL with a query:

Here are just some examples of this:

And this is how looks like the result set.
Important Note:

  • Any object can be queried, index, package, procedures, triggers and so on..
  • Object name is case sensitive, so you should be able to find it only if you use the same casing, otherwise will return an error as follows, my_function does not exist, what exist is MY_FUNCTION:

Friday, October 16, 2015

PMDL templates does not consider salePrice when applying rule (ATG Oracle Commerce)

One of our clients had an issue about a BOGO promotion that was giving  FREE the item with higher price, even when they configured it to use the "Lowest Item First" on the "Apply Discount To:" property on the Condition and Offer section, as the follow screen shot.

After some research we found that it was OOTB issue.
I have replicated this on CRS with these two jeans and the promotion from screenshot above.

As you Can see the item "Distressed Jeans" has a lower sale price, but a higher list price than "Straight Leg Jeans".
This translate into a pmdl rule evaluation for the Promotion above give the discount to the item with lower price, but as you see the next screenshot you will notice is applying the rule with the one with higher price.

In the cart the item with higher price was the one getting the promotion, this happened due the PMDL rule was saying that it was going to use listPrice when doing the sort for the evaluation.

In order to Fix this issue:
The pmdl rule that does the validation needs to consider salePrice instead.
This can be done for existing promotions with a Content Administration Project as follows:
Change that to use salePrice will solve the issue and it will consider salePrice to do apply the rule.

However this is considering that the items that qualify for the promotion needs to have salePrice, otherwise this will not work, since is relaying on something that is not defined.

Also you can change the template that creates this, those templates are located inside of $ATG_ROOT/DCS/config/config.jar at /atg/registry/data/promotiontemplates

And is as easy as edit that xml and replace the config on your application.

Note: Oracle is working on a permanent solution for this, but since needs to consider both, sale and list prices and the pmdl rule just allows you to use one of them.