Script for Inserting Data into Price List Import Interface Tables

This Post will give you the insert script for inserting data into Price list import interface tables.

INSERT INTO QP_INTERFACE_LIST_HEADERS (

ORIG_SYS_HEADER_REF,

LIST_TYPE_CODE,

NAME,

DESCRIPTION,

CURRENCY_CODE,

ACTIVE_FLAG,

CURRENCY_HEADER_ID,

START_DATE_ACTIVE,

END_DATE_ACTIVE,

ROUNDING_FACTOR,

SOURCE_LANG,

LANGUAGE,

INTERFACE_ACTION_CODE,

PROCESS_FLAG,

PROCESS_STATUS_FLAG

) VALUES

(

'SAMPLE_HEADER',

'PRL',

'SAMPLE_BLK_PL',

'SAMPLE BULK LOADED PRICE LIST',

'USD',

'Y',

3,

SYSDATE,

NULL,

-1,

'US',

'US',

'INSERT',

'Y',

'P'

)

/

/* Insert Price list line details into QP_INTERFACE_LIST_LINES table. */

INSERT INTO QP_INTERFACE_LIST_LINES (

ORIG_SYS_LINE_REF,

ORIG_SYS_HEADER_REF,

LIST_LINE_TYPE_CODE,

START_DATE_ACTIVE,

END_DATE_ACTIVE,

ARITHMETIC_OPERATOR,

OPERAND,

PRIMARY_UOM_FLAG,

PRODUCT_PRECEDENCE,

INTERFACE_ACTION_CODE,

PROCESS_FLAG,

PROCESS_STATUS_FLAG

) VALUES

(

'SAMPLE_LINE',

'SAMPLE_HEADER',

'PLL',

SYSDATE,

NULL,

'UNIT_PRICE',

100,

'Y',

230,

'INSERT',

'Y',

'P'

)

/

/* Insert Product attribute details into QP_INTERFACE_PRICING_ATTRIBS table. */

INSERT INTO QP_INTERFACE_PRICING_ATTRIBS (

ORIG_SYS_PRICING_ATTR_REF,

ORIG_SYS_LINE_REF,

ORIG_SYS_HEADER_REF,

PRODUCT_ATTRIBUTE_CONTEXT,

PRODUCT_ATTR_CODE,

PRODUCT_ATTR_VAL_DISP,

PRODUCT_UOM_CODE,

INTERFACE_ACTION_CODE,

PROCESS_FLAG,

PROCESS_STATUS_FLAG

) VALUES

(

'SAMPLE_PATTR',

'SAMPLE_LINE',

'SAMPLE_HEADER',

'ITEM',

'INVENTORY_ITEM_ID',

'123',

'KG',

'INSERT',

'Y',

'P'

)

/

COMMIT;

/

Oracle Pricing Table list

QP_LIST_HEADERS_B
QP_LIST_HEADERS_B stores the header information for all lists. List types can be, for example, Price Lists, Discount Lists or Promotions.

QP_LIST_LINES
QP_LIST_LINES stores all list lines for lists in QP_LIST_HEADERS_B. This table stores all types of list lines; price list lines, all types of modifiers including price modifier list lines used to derive factors. The different types of list lines are based on Lookup Type, 'LIST_LINE_TYPE_CODE'.

QP_PRICING_ATTRIBUTES
QP_PRICING_ATTRIBUTES stores product information and pricing attributes. The PRODUCT_ATTRIBUTE and PRODUCT_ATTRIBUTE_VALUE columns identify the product or level in the item hierarchy, i.e item context at which the price or modifier is set. The PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE and PRICING_ATTR_VALUE_FROM columns store the pricing attributes which further define what is being priced. If the PRICING_ATTRIBUTE_CONTEXT is VOLUME the pricing attributes column stores the break unit attribute, e.g. item quantity and both the PRICING_ATTR_VALUE_FROM and PRICING_ATTR_VALUE_TO columns can be populated. The Product information is repeated for all pricing attributes. A record is always created for the VOLUME context which may or may not have an attribute defined.
Tables 

Qp_interface_list_header  
qp_interface_list_lines  
qp_interface_qualifiers  
qp_interface_pricing_attribs  
qp_interface_errors  
qp_list_headers_b  
qp_list_headers_tl  
qp_list_lines  
qp_qualifiers  
qp_pricing_attributes  
qp_rltd_modifiers

Relationship

QP_LIST_HEADERS_B  <---LIST_HEADER_ID ---> QP_LIST_LINES

QP_LIST_LINES <---LIST_HEADER_ID,LIST_LINE_ID ---> QP_PRICING_ATTRIBUTES

QP_PRICING_ATTRIBUTES <---PRODUCT_ATTR_VALUE <> TO_CHAR(MSI.INVENTORY_ITEM_ID)---> mtl_system_items_b

Useful Queries

 

    /* Formatted on 7/18/2014 11:02:10 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b
WHERE list_header_id IN (SELECT list_header_id
                         FROM qp_list_headers_tl
                         WHERE name = 'Corporate');           --Price List Name
    
/* Formatted on 7/18/2014 11:02:04 AM (QP5 v5.115.810.9015) */
SELECT line.*
FROM qp_list_lines line, qp_list_headers_b header
WHERE line.list_header_id = header.list_header_id
      AND line.list_header_id IN (SELECT list_header_id
                                  FROM qp_list_headers_tl
                                  WHERE name = 'Corporate'); --Price List Name
    
    
/* Formatted on 7/18/2014 11:01:59 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
WHERE     spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.list_header_id IN (SELECT list_header_id
                                 FROM qp_list_headers_tl
                                 WHERE name = 'Corporate');--Price List Name
    
/* Formatted on 7/18/2014 11:01:53 AM (QP5 v5.115.810.9015) */
SELECT qpa.*
FROM qp_list_headers_b spl,
     qp_list_lines spll,
     qp_pricing_attributes qpa,
     mtl_system_items_b msi
WHERE     msi.organization_id = 244
      AND msi.inventory_item_id = 434257
      AND spl.list_header_id = 164075
      AND spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.product_attribute_context = 'ITEM'
      AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
      AND qpa.product_attr_value = TO_CHAR (msi.inventory_item_id)
      AND qpa.product_uom_code = msi.primary_uom_code
      AND qpa.pricing_attribute_context IS NULL
      AND qpa.excluder_flag = 'N'
      AND qpa.pricing_phase_id = 1;
    /* Formatted on 7/18/2014 11:01:45 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b
WHERE list_header_id IN (SELECT list_header_id
                         FROM qp_list_headers_tl
                         WHERE name = 'Corporate');                --Price List Name
    
/* Formatted on 7/18/2014 11:01:35 AM (QP5 v5.115.810.9015) */
SELECT line.*
FROM qp_list_lines line, qp_list_headers_b header
WHERE line.list_header_id = header.list_header_id
      AND line.list_header_id IN (SELECT list_header_id
                                  FROM qp_list_headers_tl
                                  WHERE name = 'Corporate'); --Price List Name
    
    
/* Formatted on 7/18/2014 11:01:29 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
WHERE     spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.list_header_id IN (SELECT list_header_id
                                 FROM qp_list_headers_tl
                                 WHERE name = 'Corporate');       --Price List Name

  /* Formatted on 7/18/2014 11:01:23 AM (QP5 v5.115.810.9015) */
SELECT qpa.*
FROM qp_list_headers_b spl,
     qp_list_lines spll,
     qp_pricing_attributes qpa,
     mtl_system_items_b msi
WHERE     msi.organization_id = 244
      AND msi.inventory_item_id = 434257
      AND spl.list_header_id = 164075
      AND spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.product_attribute_context = 'ITEM'
      AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
      AND qpa.product_attr_value = TO_CHAR (msi.inventory_item_id)
      AND qpa.product_uom_code = msi.primary_uom_code
      AND qpa.pricing_attribute_context IS NULL
      AND qpa.excluder_flag = 'N'
      AND qpa.pricing_phase_id = 1;

Reprice an Order Line or Order

Reprice an Order Line or Order

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;
/

References:

http://sanjaimisra.blogspot.com/2008/05/reprice-order-line.html

PROCEDURE reprice_order (p_in_header_id IN NUMBER)
   IS
      v_header_count    NUMBER;
      v_header_list     VARCHAR2 (32000);
      v_line_count      NUMBER;
      v_line_list       VARCHAR2 (32000);
      v_price_level     VARCHAR2 (32000);
      v_return_status   VARCHAR2 (32000);
      v_msg_count       NUMBER;
      v_msg_data        VARCHAR2 (32000);
   BEGIN

      v_header_count := 1;
      v_header_list := TO_CHAR (p_in_header_id);
      v_price_level := 'ORDER';

      BEGIN
         oe_order_adj_pvt.price_action (p_header_count       => v_header_count,
                                        p_header_list        => v_header_list,
                                        p_line_count         => v_line_count,
                                        p_line_list          => v_line_list,
                                        p_price_level        => v_price_level,
                                        x_return_status      => v_return_status,
                                        x_msg_count          => v_msg_count,
                                        x_msg_data           => v_msg_data
                                       );
         COMMIT;

         IF (v_return_status != fnd_api.g_ret_sts_success)
         THEN
            dbms_output.put_line(v_msg_data);
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            dbms_output.put_line(sqlerrm);
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
        dbms_output.put_line(sqlerrm);
   END reprice_order;