Home » Applications » Oracle Fusion Apps & E-Business Suite » qp_preq_pub.price_request does not return Line Quantity
qp_preq_pub.price_request does not return Line Quantity [message #362176] Mon, 01 December 2008 04:46
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
I am using the following code unit in TOAD
DECLARE
 p_line_tbl                  QP_PREQ_GRP.LINE_TBL_TYPE;
 p_qual_tbl                  QP_PREQ_GRP.QUAL_TBL_TYPE;
 p_line_attr_tbl             QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
 p_LINE_DETAIL_tbl           QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
 p_LINE_DETAIL_qual_tbl      QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
 p_LINE_DETAIL_attr_tbl      QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
 p_related_lines_tbl         QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
 p_control_rec               QP_PREQ_GRP.CONTROL_RECORD_TYPE;
 x_line_tbl                  QP_PREQ_GRP.LINE_TBL_TYPE;
 x_line_qual                 QP_PREQ_GRP.QUAL_TBL_TYPE;
 x_line_attr_tbl             QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
 x_line_detail_tbl           QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
 x_line_detail_qual_tbl      QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
 x_line_detail_attr_tbl      QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
 x_related_lines_tbl         QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
 x_return_status             VARCHAR2(240);
 x_return_status_text        VARCHAR2(240);
 qual_rec                    QP_PREQ_GRP.QUAL_REC_TYPE;
 line_attr_rec               QP_PREQ_GRP.LINE_ATTR_REC_TYPE;      
 line_rec                    QP_PREQ_GRP.LINE_REC_TYPE;
 rltd_rec                    QP_PREQ_GRP.RELATED_LINES_REC_TYPE;

 I BINARY_INTEGER;
 l_version VARCHAR2(240);
BEGIN

-- The statments below help the user in turning debug on
-- The user needs to set the oe_debug_pub.G_DIR value.
-- This value can be found by executing the following statement
--     select value
--     from   v$parameter
--     where name like 'utl_file_dir%';
-- This might return multiple values , and any one of the values can be taken 
-- Make sure that the value of the directory specified , actually exists

oe_debug_pub.SetDebugLevel(10);
oe_debug_pub.G_DIR :='/sqlcom/outbound/tst115'; -- Example
--dbms_output.put_line('The File is'|| oe_debug_pub.Set_Debug_Mode('FILE'));
oe_debug_pub.Initialize;
oe_debug_pub.debug_on;

-- Passing Information to the Pricing Engine

-- Setting up the control record variables
-- Please refer documentation for explanation of each of these settings

 p_control_rec.pricing_event := 'LINE';
 p_control_rec.calculate_flag := 'Y';
 p_control_rec.simulation_flag := 'N';

-- Request Line (Order Line) Information
 line_rec.request_type_code :='ONT';
 line_rec.line_id :=22293;                     -- Order Line Id. This can be any thing for this script
 line_rec.line_Index :='1';                    -- Request Line Index 
 line_rec.line_type_code := 'LINE';            -- LINE or ORDER(Summary Line)
 line_rec.pricing_effective_date := sysdate;   -- Pricing as of what date ? 
 line_rec.active_date_first := sysdate;        -- Can be Ordered Date or Ship Date
 line_rec.active_date_second := sysdate;       -- Can be Ordered Date or Ship Date
 line_rec.active_date_first_type := 'NO TYPE'; -- ORD/SHIP
 line_rec.active_date_second_type :='NO TYPE'; -- ORD/SHIP
 line_rec.line_quantity := 1884;                  -- Ordered Quantity
 line_rec.line_uom_code := 'CCK';               -- Ordered UOM Code
 line_rec.currency_code := 'USD';              -- Currency Code
 line_rec.price_flag := 'Y';                   -- Price Flag can have 'Y' , 'N'(No pricing) , 'P'(Phase)
 p_line_tbl(1) := line_rec;

-- If u need to get the price for multiple order lines , please fill the above information for each line
-- and add to the p_line_tbl

-- Pricing Attributes Passed In
-- Please refer documentation for explanation of each of these settings
 line_attr_rec.LINE_INDEX := 1; -- Attributes for the above line. Attributes are attached with the line index 
 line_attr_rec.PRICING_CONTEXT :='ITEM'; -- 
 line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
 line_attr_rec.PRICING_ATTR_VALUE_FROM  := '72968'; -- Inventory Item Id
 line_attr_rec.VALIDATED_FLAG :='N';  
 p_line_attr_tbl(1):= line_attr_rec;

-- If u need to add multiple attributes , please fill the above information for each attribute
-- and add to the p_line_attr_tbl
-- Make sure that u are adding the attribute to the right line index
 
-- Qualifiers Passed In
-- Please refer documentation for explanation of each of these settings
 qual_rec.LINE_INDEX := 1; -- Attributes for the above line. Attributes are attached with the line index 
 qual_rec.QUALIFIER_CONTEXT :='MODLIST';
 qual_rec.QUALIFIER_ATTRIBUTE :='QUALIFIER_ATTRIBUTE4';
 qual_rec.QUALIFIER_ATTR_VALUE_FROM :='6019'; -- Price List Id
 qual_rec.COMPARISON_OPERATOR_CODE := '=';
 qual_rec.VALIDATED_FLAG :='Y';
 p_qual_tbl(1):= qual_rec; 

 
 /*qual_rec.line_index := 1;
-- Attributes for the above line. Attributes are attached with the line index
   qual_rec.qualifier_context := 'CUSTOMER';
   qual_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE2';
   qual_rec.qualifier_attr_value_from := '15996';--p_cust_account_id;
   -- Customer Account ID
   qual_rec.comparison_operator_code := '=';
   qual_rec.validated_flag := 'Y';
   p_qual_tbl (2) := qual_rec;
*/
 
 
-- This statement prints out the version of the QP_PREQ_PUB API(QPXPPREB.pls).Information only
l_version :=  QP_PREQ_GRP.GET_VERSION;
--DBMS_OUTPUT.PUT_LINE('Testing version '||l_version);

-- Actual Call to the Pricing Engine
 QP_PREQ_PUB.PRICE_REQUEST
       (p_line_tbl,
        p_qual_tbl,
        p_line_attr_tbl,
        p_line_detail_tbl,
        p_line_detail_qual_tbl,
        p_line_detail_attr_tbl,
        p_related_lines_tbl,
        p_control_rec,
        x_line_tbl,
        x_line_qual,
        x_line_attr_tbl,
        x_line_detail_tbl,
        x_line_detail_qual_tbl,
        x_line_detail_attr_tbl,
        x_related_lines_tbl,
        x_return_status,
        x_return_status_text);

-- Interpreting Information From the Pricing Engine . Output statements commented. Please uncomment for debugging

-- Return Status Information .. 
DBMS_OUTPUT.PUT_LINE('Return Status text '||  x_return_status_text);
DBMS_OUTPUT.PUT_LINE('Return Status  '||  x_return_status);

DBMS_OUTPUT.PUT_LINE('+---------Information Returned to Caller---------------------+ ');

DBMS_OUTPUT.PUT_LINE('-------------Request Line Information-------------------');

I := x_line_tbl.FIRST;
IF I IS NOT NULL THEN
 LOOP
  DBMS_OUTPUT.PUT_LINE('Line Index: '||x_line_tbl(I).line_index);
  DBMS_OUTPUT.PUT_LINE('Unit_price: '||x_line_tbl(I).unit_price);
  DBMS_OUTPUT.PUT_LINE('Percent price: '||x_line_tbl(I).percent_price);
  DBMS_OUTPUT.PUT_LINE('Adjusted Unit Price: '||x_line_tbl(I).adjusted_unit_price);
  DBMS_OUTPUT.PUT_LINE('Pricing status code: '||x_line_tbl(I).status_code);
  DBMS_OUTPUT.PUT_LINE('Pricing status text: '||x_line_tbl(I).status_text);
  EXIT WHEN I = x_line_tbl.LAST;
  I := x_line_tbl.NEXT(I);
 END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('-----------Pricing Attributes Information-------------');

I := x_line_detail_attr_tbl.FIRST;
IF I IS NOT NULL THEN
 LOOP

  DBMS_OUTPUT.PUT_LINE('Line detail Index '||x_line_detail_attr_tbl(I).line_detail_index);
  DBMS_OUTPUT.PUT_LINE('Context '||x_line_detail_attr_tbl(I).pricing_context);
  DBMS_OUTPUT.PUT_LINE('Attribute '||x_line_detail_attr_tbl(I).pricing_attribute);
  DBMS_OUTPUT.PUT_LINE('Value '||x_line_detail_attr_tbl(I).pricing_attr_value_from);
  DBMS_OUTPUT.PUT_LINE('Status Code '||x_line_detail_attr_tbl(I).status_code);
  DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');

  EXIT WHEN I = x_line_detail_attr_tbl.last;
  I:=x_line_detail_attr_tbl.NEXT(I);

 END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('-----------Qualifier Attributes Information-------------');

I := x_line_detail_qual_tbl.FIRST;
IF I IS NOT NULL THEN
 LOOP
  DBMS_OUTPUT.PUT_LINE('Line Detail Index '||x_line_detail_qual_tbl(I).line_detail_index);
  DBMS_OUTPUT.PUT_LINE('Context '||x_line_detail_qual_tbl(I).qualifier_context);
  DBMS_OUTPUT.PUT_LINE('Attribute '||x_line_detail_qual_tbl(I).qualifier_attribute);
  DBMS_OUTPUT.PUT_LINE('Value '||x_line_detail_qual_tbl(I).qualifier_attr_value_from);
  DBMS_OUTPUT.PUT_LINE('Status Code '||x_line_detail_qual_tbl(I).status_code);
  DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');

  EXIT WHEN I = x_line_detail_qual_tbl.last;
  I:=x_line_detail_qual_tbl.NEXT(I);

 END LOOP;
END IF;

I := x_line_detail_tbl.FIRST;

DBMS_OUTPUT.PUT_LINE('------------Price List/Discount Information------------');

IF I IS NOT NULL THEN
 LOOP
  DBMS_OUTPUT.PUT_LINE('Line Index: '||x_line_detail_tbl(I).line_index);
  DBMS_OUTPUT.PUT_LINE('Line Detail Index: '||x_line_detail_tbl(I).line_detail_index);
  DBMS_OUTPUT.PUT_LINE('Line Detail Type:'||x_line_detail_tbl(I).line_detail_type_code);
  DBMS_OUTPUT.PUT_LINE('List Header Id: '||x_line_detail_tbl(I).list_header_id);
  DBMS_OUTPUT.PUT_LINE('List Line Id: '||x_line_detail_tbl(I).list_line_id);
  DBMS_OUTPUT.PUT_LINE('List Line Type Code: '||x_line_detail_tbl(I).list_line_type_code);
  DBMS_OUTPUT.PUT_LINE('Adjustment Amount : '||x_line_detail_tbl(I).adjustment_amount);
  DBMS_OUTPUT.PUT_LINE('Line Quantity : '||x_line_detail_tbl(I).line_quantity);
  DBMS_OUTPUT.PUT_LINE('Operand Calculation Code: '||x_line_detail_tbl(I).Operand_calculation_code);
  DBMS_OUTPUT.PUT_LINE('Operand value: '||x_line_detail_tbl(I).operand_value);
  DBMS_OUTPUT.PUT_LINE('Automatic Flag: '||x_line_detail_tbl(I).automatic_flag);
  DBMS_OUTPUT.PUT_LINE('Override Flag: '||x_line_detail_tbl(I).override_flag);
  DBMS_OUTPUT.PUT_LINE('status_code: '||x_line_detail_tbl(I).status_code);
  DBMS_OUTPUT.PUT_LINE('status text: '||x_line_detail_tbl(I).status_text);
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
  EXIT WHEN I =  x_line_detail_tbl.LAST;
  I := x_line_detail_tbl.NEXT(I);
 END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('--------------Related Lines Information for Price Breaks/Service Items---------------');

I := x_related_lines_tbl.FIRST;
IF I IS NOT NULL THEN
 LOOP
  DBMS_OUTPUT.PUT_LINE('Line Index :'||x_related_lines_tbl(I).line_index);
  DBMS_OUTPUT.PUT_LINE('Line Detail Index: '||x_related_lines_tbl(I).LINE_DETAIL_INDEX);
  DBMS_OUTPUT.PUT_LINE('Relationship Type Code: '||x_related_lines_tbl(I).relationship_type_code);
  DBMS_OUTPUT.PUT_LINE('Related Line Index: '||x_related_lines_tbl(I).RELATED_LINE_INDEX);
  DBMS_OUTPUT.PUT_LINE('Related Line Detail Index: '||x_related_lines_tbl(I).related_line_detail_index);
  DBMS_OUTPUT.PUT_LINE('Status Code: '|| x_related_lines_tbl(I).STATUS_CODE);
  EXIT WHEN I =  x_related_lines_tbl.LAST;
  I :=  x_related_lines_tbl.NEXT(I);
 END LOOP;
END IF; 

END;


The log output is as follows:
Return Status text Routine: QP_PREQ_PUB.PRICE_REQUEST SUCCESS
Return Status  S
+---------Information Returned to Caller---------------------+ 
-------------Request Line Information-------------------
Line Index: 1
Unit_price: .0516
Percent price: 
Adjusted Unit Price: .0516
Pricing status code: UPDATED
Pricing status text: 
-----------Pricing Attributes Information-------------
Line detail Index 2
Context ITEM
Attribute PRICING_ATTRIBUTE1
Value 72968
Status Code 
---------------------------------------------------
-----------Qualifier Attributes Information-------------
------------Price List/Discount Information------------
Line Index: 1
Line Detail Index: 2
Line Detail Type:NULL
List Header Id: 6019
List Line Id: 6036
List Line Type Code: PLL
Adjustment Amount : .0516
Line Quantity : 
Operand Calculation Code: UNIT_PRICE
Operand value: .0516
Automatic Flag: Y
Override Flag: 
status_code: N
status text: INSERTED IN L_LIST_CUR
-------------------------------------------
--------------Related Lines Information for Price Breaks/Service Items---------------




As you can see the Line Quantity is not being retrieved by the API ? Why is it so ?
I am specifying the line quantity in input using
line_rec.line_quantity := 1884

Also the adjusted price is coming same as the unit price which should not be the case. The adjusted price should be different.
Previous Topic: Applying CM to Invoice in AR
Next Topic: Debug option disabled, How do i enable it ?
Goto Forum:
  


Current Time: Mon Dec 05 04:40:28 CST 2016

Total time taken to generate the page: 0.09109 seconds