This discussion is archived
8 Replies Latest reply: Jun 7, 2013 4:11 AM by user7751421 RSS

Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different

user7751421 Newbie
Currently Being Moderated
Hi,

I have trouble in using QP_PREQ_PUB.PRICE_REQUEST to derive the price of an item.

RDBMS : 11.1.0.7.0
Oracle Applications : 12.1.3

From the Pricing Request Viewer or from Order Entry Line, The price lis coming as expected, but not from QP_PREQ_PUB.PRICE_REQUEST API.

Here are the details -

Primary Price List - 'Primary'
Secondary Price List - 'Secondary1' - Item Price 40 (Precedence 150)
Secondary Price List - 'Secondary2' - Item Price 25 (Precedence 130) , Custom Qualifier Qualifier_Attribute31 = > Attribute1('Yes/No') from Item Master.

From the application, it is giving 25. Where as using the below code it is giving 40, Can you please advise what could be the issue/fix for it?


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;
detail_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
ldet_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
rltd_rec QP_PREQ_GRP.RELATED_LINES_REC_TYPE;
x_price_contexts_result_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
x_qual_contexts_result_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
v_line_tbl_cnt integer;

I BINARY_INTEGER;
l_version VARCHAR2(240);
l_file_val VARCHAR2(60);
L_MODIFIER VARCHAR2(4000);
L_ADJUSTMENT_AMOUNT VARCHAR2(300);
L_MOD_NAME VARCHAR2(240);
l_unit_price number;
l_selling_price number;

BEGIN

MO_GLOBAL.INIT('ONT');
MO_GLOBAL.SET_POLICY_CONTEXT('S',89);
--FND_GLOBAL.APPS_INITIALIZE(1130,21623,660);


DBMS_OUTPUT.PUT_LINE('1');
v_line_tbl_cnt := 1;

---- Control Record
p_control_rec.pricing_event := 'BATCH';
p_control_rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
p_control_rec.simulation_flag := 'Y';
p_control_rec.source_order_amount_flag := 'Y';
p_control_rec.PUBLIC_API_CALL_FLAG := 'N';
p_control_rec.rounding_flag := 'Q';
p_control_Rec.manual_discount_flag := 'N';
--p_control_rec.MANUAL_ADJUSTMENTS_CALL_FLAG := 'Y';
p_control_rec.request_type_code := 'ONT';
p_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';

DBMS_OUTPUT.PUT_LINE('2');

line_rec.request_type_code :='ONT';
--line_rec.PRICE_LIST_HEADER_ID := 8007;
line_rec.line_id :=2125125; -- 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 := TRUNC(sysdate); -- Pricing as of what date ?
line_rec.active_date_first := TRUNC(sysdate); -- Can be Ordered Date or Ship Date
line_rec.active_date_second := TRUNC(sysdate); -- Can be Ordered Date or Ship Date
line_rec.active_date_first_type := 'ORD'; -- ORD/SHIP
line_rec.active_date_second_type :='SHIP'; -- ORD/SHIP
line_rec.line_quantity := 1; -- Ordered Quantity
line_rec.line_uom_code := 'EA'; -- 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;

DBMS_OUTPUT.PUT_LINE('3');
---- Line Attribute Record
line_attr_rec.LINE_INDEX := 1;
line_attr_rec.PRICING_CONTEXT :='ITEM'; --
line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE3';
line_attr_rec.PRICING_ATTR_VALUE_FROM :='ALL';
line_attr_rec.VALIDATED_FLAG :='Y';
p_line_attr_tbl(1) := line_attr_rec;


line_attr_rec.LINE_INDEX := 1;
line_attr_rec.PRICING_CONTEXT :='ITEM'; --
line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
--line_attr_rec.COMPARISON_OPERATOR_CODE         := 'BETWEEN';
line_attr_rec.PRICING_ATTR_VALUE_FROM := '4466'; -- INVENTORY ITEM ID
line_attr_rec.VALIDATED_FLAG :='Y';
p_line_attr_tbl(2) := line_attr_rec;


line_attr_rec.LINE_INDEX := 1;
line_attr_rec.PRICING_CONTEXT :='ITEM'; --
line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
--line_attr_rec.COMPARISON_OPERATOR_CODE         := 'BETWEEN';
line_attr_rec.PRICING_ATTR_VALUE_FROM := '2395'; -- INVENTORY ITEM ID
line_attr_rec.VALIDATED_FLAG :='Y';
p_line_attr_tbl(3) := line_attr_rec;

line_attr_rec.LINE_INDEX := 1;
line_attr_rec.PRICING_CONTEXT :='ITEM'; --
line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE30';
--line_attr_rec.COMPARISON_OPERATOR_CODE         := 'BETWEEN';
line_attr_rec.PRICING_ATTR_VALUE_FROM := 'Yes'; -- INVENTORY ITEM ID
line_attr_rec.VALIDATED_FLAG :='Y';
p_line_attr_tbl(3) := line_attr_rec;


DBMS_OUTPUT.PUT_LINE('4');

---- Qualifier Attribute Record

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 :='8007'; -- PRICE LIST ID
qual_rec.COMPARISON_OPERATOR_CODE := '=';
qual_rec.VALIDATED_FLAG :='Y';
p_qual_tbl(1) := qual_rec;

DBMS_OUTPUT.PUT_LINE('5');

qual_rec.line_index := 1;
qual_rec.qualifier_context := 'ITEM';
qual_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE31';
qual_rec.qualifier_attr_value_from := 'Yes';
qual_rec.comparison_operator_code := '=';
qual_rec.validated_flag := 'Y';
p_qual_tbl (2) := qual_rec;

DBMS_OUTPUT.PUT_LINE('6');

OE_ORDER_PUB.G_LINE.inventory_item_id := 4466;

DBMS_OUTPUT.PUT_LINE(Fnd_Profile.VALUE('QP_CUSTOM_SOURCED') );


--QP_Attr_Mapping_PUB.Build_Contexts
--( p_request_type_code => 'ONT',
-- p_line_index => 1,
-- p_pricing_type_code => 'L'
--);

QP_Attr_Mapping_PUB.Build_Contexts(
p_request_type_code => 'ONT',
p_pricing_type => 'L',
--p_org_id => 89,
x_price_contexts_result_tbl => x_price_contexts_result_tbl,
x_qual_contexts_result_tbl => x_qual_contexts_result_tbl );


I := x_qual_contexts_result_tbl.FIRST;
IF I IS NOT NULL THEN
LOOP
DBMS_OUTPUT.PUT_LINE('Context: '||x_qual_contexts_result_tbl(I).context_name);
DBMS_OUTPUT.PUT_LINE('attribute_name: '||x_qual_contexts_result_tbl(I).attribute_name);
DBMS_OUTPUT.PUT_LINE('attribute_value: '||x_qual_contexts_result_tbl(I).attribute_value);
EXIT WHEN I = x_qual_contexts_result_tbl.LAST;
I := x_qual_contexts_result_tbl.NEXT(I);
END LOOP;
END IF;


I := x_price_contexts_result_tbl.FIRST;
IF I IS NOT NULL THEN
LOOP
DBMS_OUTPUT.PUT_LINE('Context: '||x_price_contexts_result_tbl(I).context_name);
DBMS_OUTPUT.PUT_LINE('attribute_name: '||x_price_contexts_result_tbl(I).attribute_name);
DBMS_OUTPUT.PUT_LINE('attribute_value: '||x_price_contexts_result_tbl(I).attribute_value);
EXIT WHEN I = x_price_contexts_result_tbl.LAST;
I := x_price_contexts_result_tbl.NEXT(I);
END LOOP;
END IF;



DBMS_OUTPUT.PUT_LINE(Fnd_Profile.VALUE('QP_CUSTOM_SOURCED') );


DBMS_OUTPUT.PUT_LINE('7');

l_version := QP_PREQ_GRP.GET_VERSION;

dbms_output.put_line(OE_ORDER_PUB.G_LINE.price_list_id);

dbms_output.put_line(OE_ORDER_PUB.G_LINE.inventory_item_id);

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


-- Return Status Information ..
DBMS_OUTPUT.PUT_LINE('8');
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
L_MOD_NAME := '';
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;

BEGIN
SELECT NAME INTO L_MOD_NAME
FROM QP_LIST_HEADERS_ALL
WHERE LIST_HEADER_ID=x_line_detail_tbl(I).list_header_id
AND x_line_detail_tbl(I).list_line_type_code <>'PLL'
AND x_line_detail_tbl(I).adjustment_amount IS NOT NULL;
L_MODIFIER := L_MODIFIER||'~'||L_MOD_NAME;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;



IF x_line_detail_tbl(I).list_line_type_code <>'PLL' AND x_line_detail_tbl(I).adjustment_amount IS NOT NULL THEN
L_ADJUSTMENT_AMOUNT := L_ADJUSTMENT_AMOUNT||'~'||x_line_detail_tbl(I).adjustment_amount;
END IF;
I := x_line_detail_tbl.NEXT(I);
END LOOP;
END IF;

SELECT line_unit_price , order_uom_selling_price
INTO l_unit_price , l_selling_price
FROM qp_preq_lines_tmp
WHERE pricing_status_code = Qp_Preq_Pub.g_status_updated
ORDER BY line_id;

DBMS_OUTPUT.PUT_LINE('Unit Price := '||l_unit_price);--x_line_tbl(1).UNIT_PRICE );
DBMS_OUTPUT.PUT_LINE('Selling Price := '||l_selling_price);---x_line_tbl(1).LINE_UNIT_PRICE );
DBMS_OUTPUT.PUT_LINE('L_MODIFIER: '||L_MODIFIER);
DBMS_OUTPUT.PUT_LINE('L_ADJUSTMENT_AMOUNT: '||L_ADJUSTMENT_AMOUNT);
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; -- procedure --;

Edited by: user7751421 on Apr 20, 2013 8:54 AM
  • 1. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
    ActiveSomeTimes Explorer
    Currently Being Moderated
    Why are you passing two inventory item id's for 'PRICING_ATTRIBUTE1' ?
  • 2. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
    user7751421 Newbie
    Currently Being Moderated
    Answer is

    Pass ine_attr_rec.VALIDATED_FLAG :='Y' as 'N' instead of 'Y' which will consider the qualifiers on the price list.
  • 3. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
    Akil Pariyani Newbie
    Currently Being Moderated
    I tried with bellow code but it shows both Unit Price (price after apply all adjustment) and List price same_

    What I need is get unit selling price before order book, when customer called for inquiry only._

    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;
    detail_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
    ldet_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
    rltd_rec QP_PREQ_GRP.RELATED_LINES_REC_TYPE;
    x_price_contexts_result_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
    x_qual_contexts_result_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
    v_line_tbl_cnt INTEGER;

    I BINARY_INTEGER;
    l_version VARCHAR2 (240);
    l_file_val VARCHAR2 (60);
    L_MODIFIER VARCHAR2 (4000);
    L_ADJUSTMENT_AMOUNT VARCHAR2 (300);
    L_MOD_NAME VARCHAR2 (240);
    l_unit_price NUMBER;
    l_selling_price NUMBER;
    l_item_id number:=17155;
    BEGIN
    MO_GLOBAL.INIT ('ONT');
    MO_GLOBAL.SET_POLICY_CONTEXT ('S', 91);
    --FND_GLOBAL.APPS_INITIALIZE(1130,21623,660);


    DBMS_OUTPUT.PUT_LINE ('1');
    v_line_tbl_cnt := 1;

    ---- Control Record
    p_control_rec.pricing_event := 'LINE';
    p_control_rec.calculate_flag := 'Y';
    p_control_rec.simulation_flag := 'Y';
    p_control_rec.source_order_amount_flag := 'Y';
    p_control_rec.PUBLIC_API_CALL_FLAG := 'N';
    p_control_rec.rounding_flag := 'Q';
    p_control_Rec.manual_discount_flag := 'N';
    --p_control_rec.MANUAL_ADJUSTMENTS_CALL_FLAG := 'Y';
    p_control_rec.request_type_code := 'ONT';
    p_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';

    DBMS_OUTPUT.PUT_LINE ('2');

    line_rec.request_type_code := 'ONT';
    -- line_rec.PRICE_LIST_HEADER_ID := 386481;
    line_rec.line_id := 72202; -- 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 := TRUNC (SYSDATE); -- Pricing as of what date ?
    line_rec.active_date_first := TRUNC (SYSDATE); -- Can be Ordered Date or Ship Date
    line_rec.active_date_second := TRUNC (SYSDATE); -- Can be Ordered Date or Ship Date
    line_rec.active_date_first_type := 'ORD'; -- ORD/SHIP
    line_rec.active_date_second_type := 'SHIP'; -- ORD/SHIP
    line_rec.line_quantity := 10; -- Ordered Quantity
    line_rec.line_uom_code := 'Ea'; -- 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;

    DBMS_OUTPUT.PUT_LINE ('3');
    ---- Line Attribute Record
    line_attr_rec.LINE_INDEX := 1;
    line_attr_rec.PRICING_CONTEXT := 'ITEM'; --
    line_attr_rec.PRICING_ATTRIBUTE := 'PRICING_ATTRIBUTE3';
    line_attr_rec.PRICING_ATTR_VALUE_FROM := 'ALL';
    line_attr_rec.VALIDATED_FLAG := 'N';
    p_line_attr_tbl (1) := line_attr_rec;


    line_attr_rec.LINE_INDEX := 1;
    line_attr_rec.PRICING_CONTEXT := 'ITEM'; --
    line_attr_rec.PRICING_ATTRIBUTE := 'PRICING_ATTRIBUTE1';
    --line_attr_rec.COMPARISON_OPERATOR_CODE := 'BETWEEN';
    line_attr_rec.PRICING_ATTR_VALUE_FROM := '17155'; -- INVENTORY ITEM ID
    line_attr_rec.VALIDATED_FLAG := 'Y';
    p_line_attr_tbl (2) := line_attr_rec;


    -- line_attr_rec.LINE_INDEX := 1;
    -- line_attr_rec.PRICING_CONTEXT := 'ITEM'; --
    -- line_attr_rec.PRICING_ATTRIBUTE := 'PRICING_ATTRIBUTE1';
    -- --line_attr_rec.COMPARISON_OPERATOR_CODE := 'BETWEEN';
    -- line_attr_rec.PRICING_ATTR_VALUE_FROM := '72201'; -- INVENTORY ITEM ID
    -- line_attr_rec.VALIDATED_FLAG := 'Y';
    -- p_line_attr_tbl (3) := line_attr_rec;
    --
    -- line_attr_rec.LINE_INDEX := 1;
    -- line_attr_rec.PRICING_CONTEXT := 'ITEM'; --
    -- line_attr_rec.PRICING_ATTRIBUTE := 'PRICING_ATTRIBUTE30';
    -- --line_attr_rec.COMPARISON_OPERATOR_CODE := 'BETWEEN';
    -- line_attr_rec.PRICING_ATTR_VALUE_FROM := 'Yes'; -- INVENTORY ITEM ID
    -- line_attr_rec.VALIDATED_FLAG := 'Y';
    -- p_line_attr_tbl (3) := line_attr_rec;


    DBMS_OUTPUT.PUT_LINE ('4');

    ---- Qualifier Attribute Record

    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 := '386481'; -- PRICE LIST ID
    qual_rec.COMPARISON_OPERATOR_CODE := '=';
    qual_rec.VALIDATED_FLAG := 'Y';
    p_qual_tbl (1) := qual_rec;

    DBMS_OUTPUT.PUT_LINE ('5');

    -- qual_rec.line_index := 1;
    -- qual_rec.qualifier_context := 'ITEM';
    -- qual_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE31';
    -- qual_rec.qualifier_attr_value_from := 'Yes';
    -- qual_rec.comparison_operator_code := '=';
    -- qual_rec.validated_flag := 'Y';
    -- p_qual_tbl (2) := 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_ATTRIBUTE5';
    qual_rec.QUALIFIER_ATTR_VALUE_FROM := 5728; -- Customer Account ID
    qual_rec.COMPARISON_OPERATOR_CODE := '=';
    qual_rec.VALIDATED_FLAG := 'Y';
    p_qual_tbl (3) := qual_rec;

    qual_rec.LINE_INDEX := 1;
    qual_rec.QUALIFIER_CONTEXT := 'ITEM_CATEGORY';
    qual_rec.QUALIFIER_ATTRIBUTE := 'QUALIFIER_ATTRIBUTE35';
    qual_rec.QUALIFIER_ATTR_VALUE_FROM := 'TOOLS.HARDWARE TOOLS (DISCOUNTABLE).'; -- item category
    qual_rec.COMPARISON_OPERATOR_CODE := '=';
    qual_rec.VALIDATED_FLAG := 'N';
    p_qual_tbl (4) := qual_rec;

    DBMS_OUTPUT.PUT_LINE ('6');

    OE_ORDER_PUB.G_LINE.inventory_item_id := l_item_id;

    DBMS_OUTPUT.PUT_LINE (Fnd_Profile.VALUE ('QP_CUSTOM_SOURCED'));


    --QP_Attr_Mapping_PUB.Build_Contexts
    --( p_request_type_code => 'ONT',
    -- p_line_index => 1,
    -- p_pricing_type_code => 'L'
    --);

    QP_Attr_Mapping_PUB.
    Build_Contexts (
    p_request_type_code => 'ONT',
    p_pricing_type => 'L',
    --p_org_id => 89,
    x_price_contexts_result_tbl => x_price_contexts_result_tbl,
    x_qual_contexts_result_tbl => x_qual_contexts_result_tbl);


    I := x_qual_contexts_result_tbl.FIRST;

    IF I IS NOT NULL
    THEN
    LOOP
    DBMS_OUTPUT.
    PUT_LINE (
    'Context: ' || x_qual_contexts_result_tbl (I).context_name);
    DBMS_OUTPUT.
    PUT_LINE (
    'attribute_name: '
    || x_qual_contexts_result_tbl (I).attribute_name);
    DBMS_OUTPUT.
    PUT_LINE (
    'attribute_value: '
    || x_qual_contexts_result_tbl (I).attribute_value);
    EXIT WHEN I = x_qual_contexts_result_tbl.LAST;
    I := x_qual_contexts_result_tbl.NEXT (I);
    END LOOP;
    END IF;


    I := x_price_contexts_result_tbl.FIRST;

    IF I IS NOT NULL
    THEN
    LOOP
    DBMS_OUTPUT.
    PUT_LINE (
    'Context: ' || x_price_contexts_result_tbl (I).context_name);
    DBMS_OUTPUT.
    PUT_LINE (
    'attribute_name: '
    || x_price_contexts_result_tbl (I).attribute_name);
    DBMS_OUTPUT.
    PUT_LINE (
    'attribute_value: '
    || x_price_contexts_result_tbl (I).attribute_value);
    EXIT WHEN I = x_price_contexts_result_tbl.LAST;
    I := x_price_contexts_result_tbl.NEXT (I);
    END LOOP;
    END IF;



    DBMS_OUTPUT.PUT_LINE (' Fnd_Profile.VALUE (QP_CUSTOM_SOURCED): ' || Fnd_Profile.VALUE ('QP_CUSTOM_SOURCED'));


    DBMS_OUTPUT.PUT_LINE ('7');

    l_version := QP_PREQ_GRP.GET_VERSION;

    DBMS_OUTPUT.put_line (OE_ORDER_PUB.G_LINE.price_list_id);

    DBMS_OUTPUT.put_line (OE_ORDER_PUB.G_LINE.inventory_item_id);

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


    -- Return Status Information ..
    DBMS_OUTPUT.PUT_LINE ('8');
    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
    L_MOD_NAME := '';
    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;


    BEGIN
    SELECT NAME
    INTO L_MOD_NAME
    FROM QP_LIST_HEADERS_ALL
    WHERE LIST_HEADER_ID = x_line_detail_tbl (I).list_header_id
    AND x_line_detail_tbl (I).list_line_type_code = 'PLL'
    AND x_line_detail_tbl (I).adjustment_amount IS NOT NULL;

    L_MODIFIER := L_MODIFIER || '~' || L_MOD_NAME;
    EXCEPTION
    WHEN OTHERS
    THEN
    NULL;
    END;



    IF x_line_detail_tbl (I).list_line_type_code = 'PLL'
    AND x_line_detail_tbl (I).adjustment_amount IS NOT NULL
    THEN
    L_ADJUSTMENT_AMOUNT :=
    L_ADJUSTMENT_AMOUNT
    || '~'
    || x_line_detail_tbl (I).adjustment_amount;
    END IF;

    I := x_line_detail_tbl.NEXT (I);
    END LOOP;
    END IF;

    SELECT line_unit_price, order_uom_selling_price
    INTO l_unit_price, l_selling_price
    FROM qp_preq_lines_tmp
    WHERE pricing_status_code = Qp_Preq_Pub.g_status_updated
    ORDER BY line_id;

    DBMS_OUTPUT.PUT_LINE ('Unit Price := ' || l_unit_price); --x_line_tbl(1).UNIT_PRICE );
    DBMS_OUTPUT.PUT_LINE ('Selling Price := ' || l_selling_price); ---x_line_tbl(1).LINE_UNIT_PRICE );
    DBMS_OUTPUT.PUT_LINE ('L_MODIFIER: ' || L_MODIFIER);
    DBMS_OUTPUT.PUT_LINE ('L_ADJUSTMENT_AMOUNT: ' || L_ADJUSTMENT_AMOUNT);
    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;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.
    PUT_LINE ('SQLERRM' || SQLERRM);
    END;


    Kindly provide sugession if I miss any thing.


    Regards,
    Akil

    Edited by: 1004537 on May 9, 2013 7:20 AM

    Edited by: 1004537 on May 9, 2013 7:20 AM
  • 4. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
    ActiveSomeTimes Explorer
    Currently Being Moderated
    You hijacked another's thread ;)

    p_control_rec.pricing_event := 'LINE'
    Give it as 'BATCH'.
    Normally Batch processing event will be attached all pricing phases. Modifiers will be attached to Pricing phases like 'List Line Adjustment','All Lines Adjustment'..
    So Batch processing will apply all eligible modifiers in all pricing phases.
  • 5. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
    Akil Pariyani Newbie
    Currently Being Moderated
    ActiveSomeTimes wrote:
    You hijacked another's thread ;)

    p_control_rec.pricing_event := 'LINE'
    Give it as 'BATCH'.
    Normally Batch processing event will be attached all pricing phases. Modifiers will be attached to Pricing phases like 'List Line Adjustment','All Lines Adjustment'..
    So Batch processing will apply all eligible modifiers in all pricing phases.
    Thanx for your reply sir,

    I tried with 'BATCH' also but didn't get different price,


    I am fresher and don't have much more information regarding this, can u please send me working code if you have, or any setup that I suppose to check before implement it.

    You can send it on akil.pariyani@gmail.com

    Regards,
    Akil
  • 6. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
    user7751421 Newbie
    Currently Being Moderated
    Try using this as I stated -


    Answer is

    Pass ine_attr_rec.VALIDATED_FLAG :='Y' as 'N' instead of 'Y' which will consider the qualifiers on the price list.

    Pass all VALIDATED_FLAG as 'N'
  • 7. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
    Akil Pariyani Newbie
    Currently Being Moderated
    user7751421 wrote:
    Try using this as I stated -


    Answer is

    Pass ine_attr_rec.VALIDATED_FLAG :='Y' as 'N' instead of 'Y' which will consider the qualifiers on the price list.

    Pass all VALIDATED_FLAG as 'N'
    Thanx for your replay

    I try with all line_attr_rec.VALIDATED_FLAG := 'N' and qual_rec.VALIDATED_FLAG := 'Y';

    In toad I found Following error, I Pest few line of output

    File : /usr/tmp/l0017087.dbg
    Stratergic Product linep NOT Found --Error not occured with our other client is there any setup issue?
    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: 44.71
    Percent price:
    Adjusted Unit Price: 44.71
    Pricing status code: UPDATED
    Pricing status text:
    -----------Pricing Attributes Information-------------
    Line detail Index 2
    Context ITEM
    Attribute PRICING_ATTRIBUTE1
    Value 20485
    Status Code
    ---------------------------------------------------

    If possible can you please send me any use full document or working code, If possibe u can send it on akil.pariyani@gmail.com

    THANX and Regards,
    Akil

    Edited by: Akil Pariyani on Jun 7, 2013 12:13 AM
  • 8. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
    user7751421 Newbie
    Currently Being Moderated
    CREATE OR REPLACE PACKAGE BODY APPS.xx_price_book_pkg_2 AS

    FUNCTION get_unit_price (p_item_id IN NUMBER, p_price_list IN VARCHAR2,P_ORG_CODE IN VARCHAR2)
    RETURN NUMBER IS
    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;
    g_sysdate DATE := SYSDATE;
    l_list_name qp_list_headers_all.name%TYPE;
    l_list_dtl_name qp_list_headers_all.name%TYPE;
    i BINARY_INTEGER;
    l_version VARCHAR2 (240);
    p_price_list_id NUMBER;
    l_list_price NUMBER := 0;
    l_selling_price NUMBER := 0;
    l_charges NUMBER := 0;
    l_shipping_charges NUMBER := 0;
    l_modifier VARCHAR2 (4000);
    l_adjustment_amount VARCHAR2 (300) := 0;
    l_mod_name VARCHAR2 (240);
    l_currency VARCHAR2 (10);
    l_attribute1 VARCHAR2 (240);
    l_uom_code VARCHAR2 (30);
    L_CATEGORY_ID NUMBER;
    L_ORGANIZATION_ID NUMBER;

    TYPE PRICE_TMP IS TABLE OF xx.xx_ITEM_PRICE_TMP%ROWTYPE INDEX BY BINARY_INTEGER;

    L_PRICE_TMP PRICE_TMP;

    BEGIN
    BEGIN
    SELECT list_header_id, currency_code
    INTO p_price_list_id, l_currency
    FROM qp_list_headers_all qh
    WHERE name = p_price_list;
    EXCEPTION
    WHEN OTHERS THEN
    p_price_list_id := '';
    l_currency := 'USD';
    END;

    BEGIN
    SELECT primary_uom_code, NVL (msi.attribute1,'X'),msi.ORGANIZATION_ID
    INTO l_uom_code, l_attribute1, L_ORGANIZATION_ID
    FROM mtl_system_items_b msi, mtl_parameters mp
    WHERE msi.inventory_item_id = p_item_id
    AND msi.organization_id = mp.organization_id
    AND mp.organization_code = p_org_code ;
    EXCEPTION
    WHEN OTHERS THEN
    l_uom_code := 'EA';
    l_attribute1 := 'X';
    L_ORGANIZATION_ID := 90;
    END;

    BEGIN
    SELECT CATEGORY_ID
    INTO L_CATEGORY_ID
    FROM MTL_ITEM_CATEGORIES_V MIC
    WHERE CATEGORY_SET_NAME = 'Primary Item Category'
    AND INVENTORY_ITEM_ID=p_item_id
    AND ORGANIZATION_ID = L_ORGANIZATION_ID;
    EXCEPTION
    WHEN OTHERS THEN
    L_CATEGORY_ID := 0;
    END;

    ---- Control Record
    p_control_rec.pricing_event := 'BATCH';
    p_control_rec.calculate_flag := 'Y';
    p_control_rec.simulation_flag := 'N';
    p_control_rec.source_order_amount_flag := 'Y';
    p_control_rec.public_api_call_flag := 'N';
    p_control_rec.rounding_flag := 'Q';
    p_control_rec.manual_discount_flag := 'N';
    p_control_rec.manual_adjustments_call_flag := 'N';
    p_control_rec.request_type_code := 'ONT';
    p_control_rec.temp_table_insert_flag := 'Y';

    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';
    line_rec.line_type_code := 'LINE';
    line_rec.pricing_effective_date := g_sysdate;
    line_rec.active_date_first := g_sysdate;
    line_rec.active_date_second := g_sysdate;
    line_rec.active_date_first_type := 'NO TYPE';
    line_rec.active_date_second_type := 'NO TYPE';
    line_rec.line_quantity := 1;
    line_rec.line_uom_code := l_uom_code;
    line_rec.currency_code := l_currency;
    line_rec.price_flag := 'Y';
    p_line_tbl (1) := line_rec;

    line_attr_rec.line_index := 1;
    line_attr_rec.pricing_context := 'ITEM'; --
    line_attr_rec.pricing_attribute := 'PRICING_ATTRIBUTE3';
    line_attr_rec.pricing_attr_value_from := 'ALL';
    line_attr_rec.validated_flag := 'N';
    p_line_attr_tbl (1) := line_attr_rec;

    line_attr_rec.line_index := 1;
    line_attr_rec.pricing_context := 'ITEM';
    line_attr_rec.pricing_attribute := 'PRICING_ATTRIBUTE1';
    line_attr_rec.pricing_attr_value_from := TO_CHAR (p_item_id);
    line_attr_rec.validated_flag := 'N';
    p_line_attr_tbl (2) := line_attr_rec;

    line_attr_rec.line_index := 1;
    line_attr_rec.pricing_context := 'ITEM';
    line_attr_rec.pricing_attribute := 'PRICING_ATTRIBUTE2';
    line_attr_rec.pricing_attr_value_from := TO_CHAR (L_CATEGORY_ID);
    line_attr_rec.validated_flag := 'N';
    p_line_attr_tbl (3) := line_attr_rec;

    qual_rec.line_index := 1;
    qual_rec.qualifier_context := 'MODLIST';
    qual_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE4';
    qual_rec.qualifier_attr_value_from := TO_CHAR (p_price_list_id);
    qual_rec.comparison_operator_code := '=';
    qual_rec.validated_flag := 'N';
    p_qual_tbl (1) := qual_rec;

    qual_rec.line_index := 1;
    qual_rec.qualifier_context := 'ITEM';
    qual_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE31';
    qual_rec.qualifier_attr_value_from := l_attribute1;
    qual_rec.comparison_operator_code := '=';
    qual_rec.validated_flag := 'N';
    p_qual_tbl (2) := qual_rec;


    l_version := qp_preq_grp.get_version;

    BEGIN
    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);
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;

    IF x_return_status = 'E' THEN
    l_list_price := 0;
    l_selling_price := 0;
    ELSE
    i := x_line_tbl.FIRST;

    IF i IS NOT NULL THEN
    LOOP
    l_list_price := l_list_price + NVL (x_line_tbl (i).unit_price, 0);
    l_selling_price := l_selling_price + NVL (x_line_tbl (i).adjusted_unit_price, 0);
    EXIT WHEN i = x_line_tbl.LAST;
    i := x_line_tbl.NEXT (i);
    END LOOP;
    END IF;



    IF x_line_tbl (i).header_id IS NOT NULL THEN
    BEGIN
    SELECT name
    INTO l_list_name
    FROM qp_list_headers_all
    WHERE list_header_id = x_line_tbl (i).header_id;
    EXCEPTION
    WHEN OTHERS THEN
    l_list_name := '';
    END;
    END IF;

    i := x_line_detail_tbl.FIRST;



    IF i IS NOT NULL THEN
    LOOP
    l_mod_name := '';
    l_list_dtl_name := '';

    IF x_line_detail_tbl (i).list_header_id IS NOT NULL THEN
    BEGIN
    SELECT name
    INTO l_list_dtl_name
    FROM qp_list_headers_all
    WHERE list_header_id = x_line_detail_tbl(I).list_header_id
    AND ACTIVE_FLAG = 'Y'
    AND NVL(START_DATE_ACTIVE,SYSDATE)<=SYSDATE
    AND NVL(END_DATE_ACTIVE,SYSDATE)>=SYSDATE;
    EXCEPTION
    WHEN OTHERS THEN
    l_list_dtl_name := '';
    END;
    END IF;


    IF x_line_detail_tbl (i).CREATED_FROM_LIST_TYPE_CODE = 'CHARGES' AND l_list_dtl_name <> 'SHIPPING CHARGE' THEN
    l_charges := l_charges + NVL (x_line_detail_tbl (i).adjustment_amount, 0);
    END IF;

    IF x_line_detail_tbl (i).CREATED_FROM_LIST_TYPE_CODE = 'CHARGES' AND l_list_dtl_name = 'SHIPPING CHARGE' THEN
    l_shipping_charges := l_shipping_charges + NVL (x_line_detail_tbl (i).adjustment_amount, 0);
    END IF;

    BEGIN
    SELECT name
    INTO l_mod_name
    FROM qp_list_headers_all
    WHERE list_header_id = x_line_detail_tbl (i).list_header_id AND x_line_detail_tbl (i).list_line_type_code <> 'PLL' AND x_line_detail_tbl (i).adjustment_amount IS NOT NULL;

    l_modifier := l_modifier || '~' || l_mod_name;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;



    IF x_line_detail_tbl (i).list_line_type_code <> 'PLL' AND x_line_detail_tbl (i).adjustment_amount IS NOT NULL THEN
    l_adjustment_amount := l_adjustment_amount + NVL (x_line_detail_tbl (i).adjustment_amount, 0);
    END IF;

    EXIT WHEN i = x_line_detail_tbl.LAST;
    i := x_line_detail_tbl.NEXT (i);
    END LOOP;
    END IF;
    END IF;

    L_PRICE_TMP(1).NAME :='LIST_NAME';
    L_PRICE_TMP(1).VALUE := l_list_name;
    L_PRICE_TMP(2).NAME :='SELLING_PRICE';
    L_PRICE_TMP(2).VALUE := l_selling_price;
    L_PRICE_TMP(3).NAME :='LIST_PRICE';
    L_PRICE_TMP(3).VALUE := l_list_price;
    L_PRICE_TMP(4).NAME :='CHARGES';
    L_PRICE_TMP(4).VALUE := l_charges;
    L_PRICE_TMP(5).NAME :='SHIPPING_CHARGE';
    L_PRICE_TMP(5).VALUE := l_shipping_charges;
    L_PRICE_TMP(6).NAME :='MODIFIER';
    L_PRICE_TMP(6).VALUE := l_modifier;


    FOR J IN 1..L_PRICE_TMP.COUNT
    LOOP
    INSERT INTO xx.xx_ITEM_PRICE_TMP VALUES(L_PRICE_TMP(J).NAME,L_PRICE_TMP(J).VALUE,P_ORG_CODE);
    END LOOP;
    L_PRICE_TMP.DELETE;
    RETURN 1;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN 0;
    END get_unit_price;
    END xx_price_book_pkg_2;
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points