Sunday, May 11, 2008

Reprice an Order Line

Suppose you need to price all lines of an order using pl/sql script, you may use following script. Advantage of using OE_LINE_REPRICE.Reprice_Line procedure:


  1. You will have access to correct value of oe_order_pub.g_line.line_id in QP_CUSTOM.get_custom_price procedure



CREATE OR REPLACE PROCEDURE skm_reprice_lines
( p_header_id NUMBER
) IS
l_line_rec OE_Order_Pub.Line_Rec_Type;
l_return_status VARCHAR2(10);
i NUMBER;
l_msg_data VARCHAR2(250);

CURSOR c_lines IS
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id;
BEGIN

OE_DEBUG_PUB.debug_on();
OE_DEBUG_PUB.Start_ONT_Debugger('/home/users/smisra','skm1',null);

DBMS_APPLICATION_INFO.set_client_info('1');

FOR l_line IN c_lines
LOOP
OE_Line_Util.Query_Row
( p_line_id => l_line.line_id
, x_line_rec => l_line_rec
);

DBMS_OUTPUT.put_line('Line Id:' || l_line_rec.line_id);
DBMS_OUTPUT.put_line('ordered_item:' || l_line_rec.ordered_item);
OE_LINE_REPRICE.Reprice_Line
( p_line_rec => l_line_rec
, p_Repricing_date => 'SYSDATE'
, p_Repricing_event => 'LINE'
, p_Honor_Price_Flag => 'Y'
, x_return_status => l_return_status
) ;
END LOOP;

DBMS_OUTPUT.put_line('Return Message:' || l_return_status);

IF fnd_msg_pub.count_msg > 0
THEN
FOR j in 1..FND_MSG_PUB.count_msg
LOOP
FND_MSG_PUB.get
( p_msg_index => j
, p_encoded => 'F'
, p_data => l_msg_data
, p_msg_index_out => i
);
dbms_output.put_line( 'Error: ' || j || ':' || l_msg_data);
END LOOP;
END IF;

OE_DEBUG_PUB.debug_off();
END;
/

2 comments:

  1. How can i know promotional good (get item )for a buy item.
    lets say
    1.Buy A GET B FREE.
    2.Buy C GET B FREE.
    in sales order i have orderd for A and C then pricing engine will add two additional lines to sales
    1.1 A
    2.1 C
    3.1 B
    4.1 B

    order for A->B AND FOR C->B.
    HOW CAN I DIFFERENTIATE WHICH 'B' IS FOR WHICH ITEM (A OR C)

    ReplyDelete
  2. how to relate QP_RLTD_MODIFIER AND oe_order_lines

    ReplyDelete