[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CONTEXT , ATTRIBUTE
FROM qp_npreq_line_attrs_tmp
WHERE LINE_INDEX = p_line_index
AND ATTRIBUTE_TYPE = qp_preq_grp.G_PRICING_TYPE
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW
AND LIST_HEADER_ID = p_list_header_id
AND LIST_LINE_ID = p_list_line_id
AND CONTEXT = qp_preq_grp.G_PRIC_VOLUME_CONTEXT;
SELECT a.VALUE_FROM,a.GROUP_QUANTITY,a.GROUP_AMOUNT
FROM qp_npreq_line_attrs_tmp a
WHERE a.LINE_INDEX = p_line_index
AND a.CONTEXT = qp_preq_grp.G_PRIC_VOLUME_CONTEXT
AND a.ATTRIBUTE = p_pricing_attribute
AND a.ATTRIBUTE_TYPE = qp_preq_grp.G_PRICING_TYPE
AND a.PRICING_STATUS_CODE in (qp_preq_grp.G_STATUS_UNCHANGED,qp_preq_grp.G_STATUS_NEW);*/
SELECT LINE_QUANTITY,GROUP_QUANTITY,GROUP_AMOUNT
FROM qp_npreq_ldets_tmp
WHERE CREATED_FROM_LIST_LINE_ID = p_list_line_id
AND LINE_INDEX = p_line_index
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
SELECT a.SETUP_VALUE_FROM , b.CREATED_FROM_LIST_LINE_TYPE,b.OPERAND_VALUE,b.BENEFIT_QTY
FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp b
WHERE a.LINE_INDEX = p_line_index
AND a.LINE_INDEX = b.LINE_INDEX
AND a.LIST_LINE_ID = b.CREATED_FROM_LIST_LINE_ID
AND a.CONTEXT = qp_preq_grp.G_PRIC_VOLUME_CONTEXT
AND a.ATTRIBUTE = p_pricing_attribute
AND a.ATTRIBUTE_TYPE = qp_preq_grp.G_PRICING_TYPE
AND a.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW
AND b.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW
AND a.LIST_LINE_ID = p_list_line_id
AND a.LIST_HEADER_ID = p_list_header_id;
SELECT LIST_LINE_TYPE_CODE,OPERAND,BENEFIT_QTY
FROM QP_LIST_LINES
WHERE LIST_LINE_ID = p_line_id;
SELECT MODIFIER_LEVEL_CODE
FROM QP_LIST_LINES
WHERE LIST_LINE_ID = p_list_line_id;
SELECT QPLT.LINE_DETAIL_INDEX,
QPLT.CREATED_FROM_LIST_LINE_ID,
QPLT.CREATED_FROM_LIST_HEADER_ID,
QPLT.LINE_INDEX
FROM qp_npreq_ldets_tmp QPLT
WHERE PRICING_PHASE_ID = p_pricing_phase_id
AND QPLT.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES -- 5632314
AND QPLT.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT, QP_PREQ_GRP.G_SURCHARGE, QP_PREQ_GRP.G_FREIGHT_CHARGE)
AND PRICE_BREAK_TYPE_CODE = QP_PREQ_GRP.G_RECURRING_BREAK;
UPDATE qp_npreq_ldets_tmp --upd1
SET operand_value = l_benefit_line_qty_tbl(I)
WHERE LINE_DETAIL_INDEX = l_line_detail_index_tbl(I);
QP_PREQ_GRP.engine_debug('Updated count: ' || l_line_detail_index_tbl.count);
SELECT distinct a.PRICE_BREAK_TYPE_CODE , b.FROM_RLTD_MODIFIER_ID,b.TO_RLTD_MODIFIER_ID,c.LINE_DETAIL_INDEX ,
a.PRICING_PHASE_ID , a.AUTOMATIC_FLAG,a.LIST_HEADER_ID
FROM QP_LIST_LINES a, QP_RLTD_MODIFIERS b,qp_npreq_ldets_tmp c
WHERE a.LIST_LINE_ID = b.FROM_RLTD_MODIFIER_ID
AND a.LIST_LINE_ID = c.CREATED_FROM_LIST_LINE_ID
AND b.RLTD_MODIFIER_GRP_TYPE = qp_preq_grp.G_BENEFIT_TYPE
AND c.CREATED_FROM_LIST_LINE_TYPE = qp_preq_grp.G_PROMO_GOODS_DISCOUNT
AND c.PRICING_PHASE_ID = p_pricing_phase_id
AND c.LINE_INDEX = p_line_index
AND a.LIST_LINE_ID = p_list_line_id
AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
SELECT a.LIST_TYPE_CODE,b.LIST_HEADER_ID,b.LIST_LINE_ID,b.LIST_LINE_TYPE_CODE,
b.PRICING_GROUP_SEQUENCE, b.ARITHMETIC_OPERATOR,b.OPERAND,b.PRICING_PHASE_ID,
b.BENEFIT_PRICE_LIST_LINE_ID, b.BENEFIT_UOM_CODE,
b.SERVICE_DURATION,b.SERVICE_PERIOD,-- SERVICE PROJECT
b.BENEFIT_QTY,b.LIST_PRICE,
b.PRICE_BREAK_TYPE_CODE,b.AUTOMATIC_FLAG, c.PRODUCT_ATTRIBUTE_CONTEXT, c.PRODUCT_ATTRIBUTE,
c.PRODUCT_ATTR_VALUE,c.PRODUCT_UOM_CODE , b.ACCRUAL_FLAG,b.MODIFIER_LEVEL_CODE
FROM QP_LIST_HEADERS_B a ,QP_LIST_LINES b,QP_PRICING_ATTRIBUTES c
WHERE a.LIST_HEADER_ID = b.LIST_HEADER_ID
AND b.LIST_LINE_ID = c.LIST_LINE_ID
AND b.LIST_LINE_ID= p_related_modifier_id;
SELECT CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp c
WHERE c.CREATED_FROM_LIST_LINE_ID = p_list_line_id
AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW
AND c.LINE_INDEX = p_line_index ;
SELECT a.LIST_HEADER_ID,a.LIST_LINE_ID,a.OPERAND,b.ROUNDING_FACTOR, a.ARITHMETIC_OPERATOR -- service project
FROM QP_LIST_LINES a ,QP_LIST_HEADERS_B b
WHERE a.LIST_LINE_ID = p_line_id
AND a.LIST_HEADER_ID = b.LIST_HEADER_ID;
SELECT REQUEST_TYPE_CODE
FROM qp_npreq_lines_tmp
WHERE LINE_INDEX = p_line_index;
SELECT UNIT_PRICE
FROM qp_npreq_lines_tmp
WHERE LINE_INDEX = p_line_index;
SELECT MAX(LINE_INDEX)
FROM qp_npreq_lines_tmp;
SELECT distinct RELATED_LINE_DETAIL_INDEX,RELATED_LINE_INDEX -- fix bug 2998770
,related_list_line_id -- bug 2979447
FROM qp_npreq_rltd_lines_tmp a, qp_npreq_lines_tmp b
WHERE a.LIST_LINE_ID = p_list_line_id
AND b.line_index = a.related_line_index
AND (instr(b.PROCESS_STATUS,'FREEGOOD')=0)
AND a.RELATIONSHIP_TYPE_CODE = qp_preq_grp.G_GENERATED_LINE
AND a.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
INSERT INTO qp_npreq_rltd_lines_tmp
(REQUEST_TYPE_CODE
,LINE_DETAIL_INDEX
,RELATIONSHIP_TYPE_CODE
,RELATED_LINE_DETAIL_INDEX
,PRICING_STATUS_CODE
,LIST_LINE_ID
,LINE_INDEX
,RELATED_LINE_INDEX
,related_list_line_id) -- bug 2979447
VALUES
(v_request_type_code
,p_line_detail_index
,qp_preq_grp.G_GENERATED_LINE
,j.related_line_detail_index
,qp_preq_grp.G_STATUS_NEW
,p_list_line_id
,p_line_index
,j.related_line_index
,j.related_list_line_id); -- bug 2979447
INSERT INTO qp_npreq_lines_tmp
(LINE_INDEX
, PRICE_LIST_HEADER_ID
, LINE_TYPE_CODE
, PRICING_EFFECTIVE_DATE
, LINE_QUANTITY
, LINE_UOM_CODE
, SERVICE_DURATION -- SERVICE_PROJECT
, SERVICE_PERIOD -- SERVICE_PROJECT
, CURRENCY_CODE
, PRICING_STATUS_CODE
, PROCESSED_FLAG
, ADJUSTED_UNIT_PRICE
, PRICE_FLAG
, UNIT_PRICE
, REQUEST_TYPE_CODE
, PRICED_UOM_CODE
, PRICED_QUANTITY
, PROCESSED_CODE
, ROUNDING_FACTOR
, ROUNDING_FLAG
--added by spgopal for prg
, PROCESS_STATUS
--to create returns of prg to create return for freegood spgopal
, LINE_CATEGORY)
SELECT v_line_index
, v_list_header_id
, LINE_TYPE_CODE
, PRICING_EFFECTIVE_DATE
, x_total_benefit_qty
, i.BENEFIT_UOM_CODE
, i.SERVICE_DURATION -- SERVICE PROJECT
, i.SERVICE_PERIOD -- SERVICE PROJECT
, CURRENCY_CODE
, qp_preq_grp.G_STATUS_UNCHANGED
, PROCESSED_FLAG
, v_list_price
, qp_preq_grp.G_NO
, v_list_price
, REQUEST_TYPE_CODE
, i.BENEFIT_UOM_CODE
, x_total_benefit_qty
, qp_preq_grp.G_BY_ENGINE
, v_rounding_factor
, qp_preq_grp.G_YES
, qp_preq_grp.G_STATUS_NEW
, LINE_CATEGORY
FROM qp_npreq_lines_tmp
WHERE LINE_INDEX = p_line_index;
/* INSERT INTO qp_npreq_rltd_lines_tmp(REQUEST_TYPE_CODE, LINE_INDEX,RELATIONSHIP_TYPE_CODE,
RELATED_LINE_INDEX, PRICING_STATUS_CODE) VALUES
(v_request_type_code,p_line_index,qp_preq_grp.G_GENERATED_LINE,v_line_index,
qp_preq_grp.G_STATUS_NEW);
INSERT INTO qp_npreq_ldets_tmp
(LINE_DETAIL_INDEX
,LINE_DETAIL_TYPE_CODE
,LINE_INDEX
,CREATED_FROM_LIST_HEADER_ID
,CREATED_FROM_LIST_LINE_ID
,CREATED_FROM_LIST_LINE_TYPE
,PRICING_GROUP_SEQUENCE
,OPERAND_CALCULATION_CODE
,OPERAND_VALUE
,PROCESSED_FLAG
,CREATED_FROM_LIST_TYPE_CODE
,PRICING_STATUS_CODE
,LINE_QUANTITY
,ROUNDING_FACTOR
,PROCESS_CODE)
VALUES
(v_detail_line_index
,qp_preq_grp.G_GENERATED_LINE
,v_line_index
,v_list_header_id
,v_list_line_id
,qp_preq_grp.G_PRICE_LIST_TYPE
,0
,qp_preq_grp.G_UNIT_PRICE
,v_list_price
,qp_preq_grp.G_NO
,qp_preq_grp.G_PRICE_LIST_HEADER
,qp_preq_grp.G_STATUS_NEW
,x_total_benefit_qty
,v_rounding_factor
,qp_preq_grp.G_STATUS_NEW);
INSERT INTO qp_npreq_ldets_tmp
(LINE_DETAIL_INDEX
,LINE_DETAIL_TYPE_CODE
,LINE_INDEX
,CREATED_FROM_LIST_HEADER_ID
,CREATED_FROM_LIST_LINE_ID
,CREATED_FROM_LIST_LINE_TYPE
,PRICING_GROUP_SEQUENCE
,OPERAND_CALCULATION_CODE
,PRICING_PHASE_ID
,OPERAND_VALUE
,PROCESSED_FLAG
,AUTOMATIC_FLAG
,APPLIED_FLAG
,ACCRUAL_FLAG
,CREATED_FROM_LIST_TYPE_CODE
,PRICING_STATUS_CODE
,LINE_QUANTITY
,PROCESS_CODE
,MODIFIER_LEVEL_CODE
,CALCULATION_CODE)
VALUES
(v_detail_line_index
,qp_preq_grp.G_GENERATED_LINE
,v_line_index
,i.LIST_HEADER_ID
,i.LIST_LINE_ID
,i.LIST_LINE_TYPE_CODE
,i.PRICING_GROUP_SEQUENCE
,i.ARITHMETIC_OPERATOR
,j.PRICING_PHASE_ID
,i.OPERAND
,qp_preq_grp.G_NO
,j.AUTOMATIC_FLAG
,qp_preq_grp.G_YES
,nvl(i.ACCRUAL_FLAG ,'N')
,i.LIST_TYPE_CODE
,qp_preq_grp.G_STATUS_NEW
,x_total_benefit_qty
,qp_preq_grp.G_STATUS_NEW
,i.MODIFIER_LEVEL_CODE
--fix for bug 2988476
,QP_PREQ_PUB.G_FREEGOOD);
INSERT INTO qp_npreq_rltd_lines_tmp
(REQUEST_TYPE_CODE,
LINE_DETAIL_INDEX,
RELATIONSHIP_TYPE_CODE,
RELATED_LINE_DETAIL_INDEX,
PRICING_STATUS_CODE,
LIST_LINE_ID,
-- begin shu,side fix bug 2491158,missing data in qp_npreq_rltd_lines_tmp
line_index,
related_line_index,
related_list_line_id,
related_list_line_type,
operand_calculation_code,
operand,
qualifier_value
-- end shu, side fix bug 2491158, missing data in qp_npreq_rltd_lines_tmp
)
VALUES
(v_request_type_code,
j.LINE_DETAIL_INDEX,
qp_preq_grp.G_GENERATED_LINE,
v_detail_line_index,
qp_preq_grp.G_STATUS_NEW,
p_list_line_id,
-- begin shu,side fix bug 2491158,missing data in qp_npreq_rltd_lines_tmp
p_line_index,
v_line_index,
i.LIST_LINE_ID,
i.LIST_LINE_TYPE_CODE,
i.ARITHMETIC_OPERATOR,
i.OPERAND,
x_total_benefit_qty
-- end shu, side fix bug 2491158, missing data in qp_npreq_rltd_lines_tmp
);
INSERT INTO qp_npreq_line_attrs_tmp
(LIST_LINE_ID
,LINE_INDEX
,line_detail_index
,CONTEXT
,ATTRIBUTE
,VALUE_FROM
,SETUP_VALUE_FROM
,VALIDATED_FLAG
,PRODUCT_UOM_CODE
,ATTRIBUTE_LEVEL
,ATTRIBUTE_TYPE
,PRICING_STATUS_CODE)
VALUES
(i.LIST_LINE_ID
,v_line_index
,v_detail_line_index
,qp_preq_grp.G_PRIC_ITEM_CONTEXT
,qp_preq_grp.G_PRIC_ATTRIBUTE1
,i.PRODUCT_ATTR_VALUE
,i.PRODUCT_ATTR_VALUE
,qp_preq_grp.G_NO
,i.PRODUCT_UOM_CODE
,qp_preq_grp.G_LINE_LEVEL
,qp_preq_grp.G_PRODUCT_TYPE
,qp_preq_grp.G_STATUS_NEW); --Item
UPDATE qp_npreq_ldets_tmp --upd1
SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE PRICING_PHASE_ID = p_pricing_phase_id
AND LINE_INDEX = p_line_index
AND CREATED_FROM_LIST_LINE_ID = p_list_line_id
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
UPDATE qp_npreq_line_attrs_tmp
SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE LINE_INDEX = p_line_index
AND LIST_LINE_ID = p_list_line_id
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
-- Insert a line into the qp_npreq_lines_tmp table for the other item
-- Insert a line into qp_npreq_rltd_lines_tmp
-- Insert a line into qp_npreq_ldets_tmp table for discount on the new line
-- Insert a line into qp_npreq_rltd_lines_tmp
-- Insert a line into qp_npreq_line_attrs_tmp
CURSOR get_related_modifier_id_cur IS
SELECT distinct a.LIST_LINE_TYPE_CODE,b.FROM_RLTD_MODIFIER_ID,b.TO_RLTD_MODIFIER_ID , c.LINE_DETAIL_INDEX,
a.PRICING_PHASE_ID , a.AUTOMATIC_FLAG
FROM QP_LIST_LINES a, QP_RLTD_MODIFIERS b,qp_npreq_ldets_tmp c
WHERE a.LIST_LINE_ID = b.FROM_RLTD_MODIFIER_ID
AND a.LIST_LINE_ID = c.CREATED_FROM_LIST_LINE_ID
AND b.RLTD_MODIFIER_GRP_TYPE = qp_preq_grp.G_BENEFIT_TYPE
AND c.CREATED_FROM_LIST_LINE_TYPE = qp_preq_grp.G_OTHER_ITEM_DISCOUNT
AND c.PRICING_PHASE_ID = p_pricing_phase_id
AND c.LINE_INDEX = p_line_index
AND a.LIST_LINE_ID = p_list_line_id
AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
SELECT distinct a.LIST_TYPE_CODE,b.LIST_HEADER_ID,b.LIST_LINE_ID,b.LIST_LINE_TYPE_CODE,
b.PRICING_GROUP_SEQUENCE, b.ARITHMETIC_OPERATOR,b.OPERAND,b.AUTOMATIC_FLAG,
b.BENEFIT_PRICE_LIST_LINE_ID,b.LIST_PRICE, c.PRODUCT_ATTRIBUTE_CONTEXT,
c.PRODUCT_ATTRIBUTE,c.PRODUCT_ATTR_VALUE , b.ACCRUAL_FLAG,b.MODIFIER_LEVEL_CODE
FROM QP_LIST_HEADERS_B a ,QP_LIST_LINES b, QP_PRICING_ATTRIBUTES c
WHERE a.LIST_HEADER_ID = b.LIST_HEADER_ID
AND b.LIST_LINE_ID = c.LIST_LINE_ID
AND b.LIST_LINE_ID= p_related_modifier_id;
SELECT /*+ ORDERED USE_NL(a)
index(b qp_preq_lines_tmp_n2)
index(a qp_preq_line_attrs_tmp_n2)
get_benefit_line_index_cur */
distinct a.LINE_INDEX,nvl(b.PRICED_QUANTITY,b.LINE_QUANTITY) LINE_QUANTITY
FROM qp_npreq_lines_tmp b , qp_npreq_line_attrs_tmp a
WHERE a.LINE_INDEX = b.LINE_INDEX
AND a.CONTEXT = p_context
AND a.ATTRIBUTE = p_attribute
AND a.VALUE_FROM = p_value
AND a.ATTRIBUTE_TYPE = qp_preq_grp.G_PRODUCT_TYPE
AND a.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_UNCHANGED
AND b.LINE_TYPE_CODE <> qp_preq_grp.G_ORDER_LEVEL
AND NOT EXISTS ( SELECT /*+ index(c qp_preq_ldets_tmp_n1) */ 'x'
FROM qp_npreq_ldets_tmp c
WHERE c.LINE_INDEX = a.LINE_INDEX
AND c.CREATED_FROM_LIST_LINE_ID = p_list_line_id
AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW);
SELECT REQUEST_TYPE_CODE
FROM qp_npreq_lines_tmp
WHERE LINE_INDEX = p_line_index;
UPDATE qp_npreq_ldets_tmp --upd1
SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE PRICING_PHASE_ID = p_pricing_phase_id
AND LINE_INDEX = p_line_index
AND CREATED_FROM_LIST_LINE_ID = j.FROM_RLTD_MODIFIER_ID
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
/* UPDATE qp_npreq_line_attrs_tmp
SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE LIST_LINE_ID = j.FROM_RLTD_MODIFIER_ID
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW; */
-- In this case , the engine would select line index 2 and line index 4 as part of the cursor
-- It will insert the same benefit line against line2 and line 4 when processing OID 1
-- For OID2 , the cursor would not fetch any records and the v_benefits_exists flag is FALSE and the
-- second OID line gets deleted
-- Essentially , the second benefit line is also because of OID 1
FOR m IN get_benefit_line_index_cur
(i.PRODUCT_ATTRIBUTE_CONTEXT, i.PRODUCT_ATTRIBUTE, i.PRODUCT_ATTR_VALUE,i.LIST_LINE_ID)
LOOP
v_benefit_exists := TRUE;
INSERT INTO qp_npreq_ldets_tmp
(LINE_DETAIL_INDEX,
LINE_DETAIL_TYPE_CODE,
LINE_INDEX,
CREATED_FROM_LIST_HEADER_ID,
CREATED_FROM_LIST_LINE_ID,
CREATED_FROM_LIST_LINE_TYPE,
PRICING_GROUP_SEQUENCE,
OPERAND_CALCULATION_CODE,
OPERAND_VALUE,
PROCESSED_FLAG,
AUTOMATIC_FLAG,
APPLIED_FLAG,
ACCRUAL_FLAG,
PRICING_PHASE_ID,
CREATED_FROM_LIST_TYPE_CODE,
PRICING_STATUS_CODE,
LINE_QUANTITY,
PROCESS_CODE,
MODIFIER_LEVEL_CODE)
VALUES
(v_detail_line_index,
qp_preq_grp.G_GENERATED_LINE,
m.line_index,
i.LIST_HEADER_ID,
i.LIST_LINE_ID,
i.LIST_LINE_TYPE_CODE,
i.PRICING_GROUP_SEQUENCE,
i.ARITHMETIC_OPERATOR,
i.OPERAND,
qp_preq_grp.G_NO,
j.AUTOMATIC_FLAG,
qp_preq_grp.G_YES,nvl(i.ACCRUAL_FLAG,'N'),
j.PRICING_PHASE_ID,
i.LIST_TYPE_CODE,
qp_preq_grp.G_STATUS_NEW,
m.LINE_QUANTITY,
qp_preq_grp.G_STATUS_NEW,
i.MODIFIER_LEVEL_CODE);
INSERT INTO qp_npreq_rltd_lines_tmp
(REQUEST_TYPE_CODE,
LINE_DETAIL_INDEX,
RELATIONSHIP_TYPE_CODE,
RELATED_LINE_DETAIL_INDEX,
PRICING_STATUS_CODE,
line_index, -- begin shu, fix 2491158, missing data in qp_npreq_rltd_lines_tmp
related_line_index,
list_line_id,
related_list_line_id,
related_list_line_type,
operand_calculation_code,
operand,
qualifier_value -- end shu, fix 2491158, missing data in qp_npreq_rltd_lines_tmp
)
VALUES
(v_request_type_code,
j.LINE_DETAIL_INDEX,
qp_preq_grp.G_GENERATED_LINE,
v_detail_line_index,
qp_preq_grp.G_STATUS_NEW,
p_line_index, -- begin shu, fix 2491158, missing data in qp_npreq_rltd_lines_tmp
m.line_index,
p_list_line_id,
i.list_line_id,
i.list_line_type_code,
i.arithmetic_operator,
i.operand,
m.line_quantity -- end shu, fix 2491158, missing data in qp_npreq_rltd_lines_tmp
);
-- This is the code to delete the OID lines which do not have adjustments/benefits even thou
-- x_qualifier_flag=TRUE
--Begin Bug No: 7323590
/* commented out for bug No: 7323590
IF (v_benefit_exists = FALSE) THEN
UPDATE qp_npreq_ldets_tmp --upd2
SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE PRICING_PHASE_ID = p_pricing_phase_id
AND LINE_INDEX = p_line_index
AND CREATED_FROM_LIST_LINE_ID = p_list_line_id
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
/* UPDATE qp_npreq_line_attrs_tmp
SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE LINE_INDEX = p_line_index
AND LIST_LINE_ID = p_list_line_id
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
SELECT RLTD_MODIFIER_GRP_NO,COUNT(*) NO_OF_MODIFIERS_IN_GRP
FROM QP_RLTD_MODIFIERS
WHERE FROM_RLTD_MODIFIER_ID = p_list_line_id
AND RLTD_MODIFIER_GRP_TYPE = p_rltd_modifier_type
GROUP BY RLTD_MODIFIER_GRP_NO;
SELECT a.LIST_LINE_TYPE_CODE , b.TO_RLTD_MODIFIER_ID
FROM QP_LIST_LINES a , QP_RLTD_MODIFIERS b
WHERE a.LIST_LINE_ID = b.FROM_RLTD_MODIFIER_ID
AND b.FROM_RLTD_MODIFIER_ID = p_list_line_id
AND b.RLTD_MODIFIER_GRP_TYPE = p_rltd_modifier_type
AND b.RLTD_MODIFIER_GRP_NO = p_rltd_grp_no;
SELECT distinct c.LIST_LINE_ID
FROM QP_PRICING_ATTRIBUTES c
WHERE c.LIST_LINE_ID = p_rltd_modifier_id
AND c.PRICING_ATTRIBUTE_CONTEXT IS NULL
AND EXISTS
(SELECT /*+ ORDERED NO_UNNEST index(b qp_preq_line_attrs_tmp_n2)
index(a qp_preq_lines_tmp_u1)
index(d qp_preq_lines_tmp_u1) */ 'X'
FROM qp_npreq_line_attrs_tmp b,
qp_npreq_lines_tmp a,
qp_npreq_lines_tmp d
WHERE b.CONTEXT = c.PRODUCT_ATTRIBUTE_CONTEXT
AND b.ATTRIBUTE = c.PRODUCT_ATTRIBUTE
AND b.VALUE_FROM = c.PRODUCT_ATTR_VALUE
AND b.ATTRIBUTE_TYPE = qp_preq_grp.G_PRODUCT_TYPE
AND b.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_UNCHANGED
AND a.LINE_INDEX = b.LINE_INDEX
AND a.LINE_TYPE_CODE = QP_PREQ_GRP.G_LINE_LEVEL
AND instr(a.PROCESS_STATUS, QP_PREQ_PUB.G_FREEGOOD) = 0 -- bug 3006670
AND a.PRICED_UOM_CODE = nvl(c.PRODUCT_UOM_CODE, a.PRICED_UOM_CODE)
AND d.LINE_INDEX = p_line_index
AND d.LINE_TYPE_CODE = QP_PREQ_GRP.G_LINE_LEVEL
AND nvl(d.LINE_CATEGORY,'ORDER') = nvl(a.LINE_CATEGORY,'ORDER'))
UNION
-- 10g/R12 performance fixes for 5573393
SELECT distinct a.LIST_LINE_ID -- Index N7 can be replaced with N2
FROM QP_PRICING_ATTRIBUTES a
WHERE a.LIST_LINE_ID = p_rltd_modifier_id
AND EXISTS
(SELECT /*+ ORDERED NO_UNNEST index(b qp_preq_line_attrs_n2)
index(d qp_preq_lines_tmp_u1)
index(c qp_preq_line_attrs_tmp_n2)
index(e qp_preq_lines_tmp_u1) */ 'X'
FROM qp_npreq_line_attrs_tmp b,
qp_npreq_lines_tmp d,
qp_npreq_line_attrs_tmp c,
qp_npreq_lines_tmp e
WHERE b.CONTEXT = a.PRODUCT_ATTRIBUTE_CONTEXT
AND b.ATTRIBUTE = a.PRODUCT_ATTRIBUTE
AND b.VALUE_FROM = a.PRODUCT_ATTR_VALUE
AND b.ATTRIBUTE_TYPE = qp_preq_grp.G_PRODUCT_TYPE
AND b.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_UNCHANGED
AND d.PRICED_UOM_CODE = nvl(a.PRODUCT_UOM_CODE, d.PRICED_UOM_CODE)
AND d.LINE_INDEX = b.LINE_INDEX
AND d.LINE_TYPE_CODE = QP_PREQ_GRP.G_LINE_LEVEL
AND b.LINE_INDEX = c.LINE_INDEX
AND instr(d.PROCESS_STATUS, QP_PREQ_PUB.G_FREEGOOD) = 0 -- bug 3006670
AND c.CONTEXT = qp_preq_grp.G_PRIC_VOLUME_CONTEXT
AND c.CONTEXT = a.PRICING_ATTRIBUTE_CONTEXT
AND c.ATTRIBUTE = a.PRICING_ATTRIBUTE
AND qp_number.canonical_to_number(c.VALUE_FROM) BETWEEN
nvl(a.PRICING_ATTR_VALUE_FROM,qp_number.canonical_to_number(c.VALUE_FROM)) AND
nvl(a.PRICING_ATTR_VALUE_TO,qp_number.canonical_to_number(c.VALUE_FROM))
AND c.ATTRIBUTE_TYPE = qp_preq_grp.G_PRICING_TYPE
AND c.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_UNCHANGED
AND e.LINE_INDEX = p_line_index
AND e.LINE_TYPE_CODE = QP_PREQ_GRP.G_LINE_LEVEL
AND nvl(e.LINE_CATEGORY,'ORDER') = nvl(d.LINE_CATEGORY,'ORDER'));
v_update_flag BOOLEAN := FALSE;
v_update_flag := FALSE;
v_update_flag := TRUE;
v_update_flag := FALSE;
v_update_flag := TRUE;
IF (v_update_flag = TRUE) THEN
UPDATE qp_npreq_ldets_tmp --upd1
SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE CREATED_FROM_LIST_LINE_ID = p_list_line_id;
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE a.LINE_DETAIL_INDEX IN (SELECT b.LINE_DETAIL_INDEX
FROM qp_npreq_ldets_tmp b
WHERE b.CREATED_FROM_LIST_LINE_ID = p_list_line_id); */
qp_preq_grp.engine_debug ('BENEFIT STATUS UPDATED');
SELECT /*+ index (ldets qp_preq_ldets_tmp_N2) */
CREATED_FROM_LIST_LINE_ID,CREATED_FROM_LIST_LINE_TYPE,LINE_DETAIL_INDEX , MODIFIER_LEVEL_CODE
FROM qp_npreq_ldets_tmp ldets
WHERE CREATED_FROM_LIST_LINE_TYPE IN (qp_preq_grp.G_OTHER_ITEM_DISCOUNT, qp_preq_grp.G_PROMO_GOODS_DISCOUNT,
qp_preq_grp.G_COUPON_ISSUE)
AND PRICING_PHASE_ID = p_pricing_phase_id
AND LINE_INDEX = p_line_index
AND ASK_FOR_FLAG IN (qp_preq_grp.G_YES,qp_preq_grp.G_NO)
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
and qp_preq_grp.G_TEMP_TABLE_INSERT_FLAG = qp_preq_grp.G_YES)) THEN
QP_COUPON_PVT.PROCESS_COUPON_ISSUE(i.line_detail_index,
p_pricing_phase_id,
p_line_quantity,
p_simulation_flag,
x_ret_status,
x_ret_status_txt);
UPDATE qp_npreq_ldets_tmp --upd1
SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE PRICING_PHASE_ID = p_pricing_phase_id
AND LINE_INDEX = p_line_index
AND CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW;
/* UPDATE qp_npreq_line_attrs_tmp
SET PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_OTHER_ITEM_BENEFITS
WHERE LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
AND PRICING_STATUS_CODE = qp_preq_grp.G_STATUS_NEW; */