Thursday, November 8, 2012

ASO global variable ASO_PRICING_INT.G_LINE_REC value is not Correct

ASO global variable ASO_PRICING_INT.G_LINE_REC value is not Correct inside QP_CUSTOM get_custom_price
If you have used one of the Line level ASO pricing global variable inside QP_CUSTOM.get_custom_price code and found out it was not holding a correct value. Also, you have noticed that it always has information from the last line if Quote has multiple lines.
ASO Global variables used are:
ASO_PRICING_INT.G_LINE_REC.QUOTE_LINE_ID
ASO_PRICING_INT.G_LINE_REC.INVENTORY_ITEM_ID

A record structure unlike a table structure contains a single value in the memory at a give point in time. For example, if the formula is directly accessing the global pl/sql "record" structure, the formula will always contain a single line id at a given point in time in memory. Actually the G_LINE_REC will have the last quote line id in the quote by the time pricing is trying to evaluate the formula. Thus you always end up getting the same value of unit price although each line has a different quote line id.
Instead of using G_LINE_REC.quote_line_id, use below SQL logic:
SELECT line_id
INTO l_line_id
FROM qp_preq_lines_tmp
WHERE line_index = p_req_line_attrs_tbl(1).line_index;

Instead of using G_LINE_REC.inventory_item_id,  use below SQL logic to determine inventory item id:
FOR i in 1..p_req_line_attrs_tbl.count LOOP
IF p_req_line_attrs_tbl(i).attribute_type = 'PRODUCT'
and p_req_line_attrs_tbl(i).context = 'ITEM'
and p_req_line_attrs_tbl(i).attribute = 'PRICING_ATTRIBUTE1'
THEN
l_item_id := to_number(ltrim(rtrim(p_req_line_attrs_tbl(i).value)));
END IF;
END LOOP;

1 comment: