[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(a.QUALIFIER_PRECEDENCE,5000) PRECED
FROM qp_npreq_line_attrs_tmp a
WHERE a.LIST_LINE_ID = p_list_line_id
AND a.LIST_HEADER_ID = p_list_header_id
AND a.ATTRIBUTE_TYPE = 'QUALIFIER'
AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND a.PRICING_PHASE_ID = p_pricing_phase_id
AND a.PRICING_STATUS_CODE = 'N'
AND a.LINE_INDEX = p_line_index
UNION
SELECT nvl(a.PRODUCT_PRECEDENCE,5000) PRECED
FROM qp_npreq_ldets_tmp a
WHERE a.CREATED_FROM_LIST_LINE_ID = p_list_line_id
AND a.CREATED_FROM_LIST_HEADER_ID = p_list_header_id
AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND a.PRICING_PHASE_ID = p_pricing_phase_id
AND a.PRICING_STATUS_CODE = 'N'
AND a.LINE_INDEX = p_line_index
ORDER BY 1;
SELECT product_uom_code,attribute,value_from inventory_item_id
FROM qp_npreq_line_attrs_tmp a
WHERE a.LIST_LINE_ID = p_list_line_id
AND a.LINE_INDEX = p_line_index
AND a.ATTRIBUTE_TYPE = 'PRODUCT'
AND a.PRICING_PHASE_ID = 1
AND a.PRICING_STATUS_CODE = 'N';
SELECT value_from
FROM qp_npreq_line_attrs_tmp
WHERE line_index = p_line_index
AND attribute_type = QP_PREQ_GRP.G_PRODUCT_TYPE
AND context = 'ITEM'
AND attribute = 'PRICING_ATTRIBUTE1'
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
SELECT distinct nvl(a.QUALIFIER_PRECEDENCE,5000) PRECED , a.LIST_LINE_ID , -99999 QUAL_PRECED
FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp c , qp_npreq_line_attrs_tmp b
WHERE a.ATTRIBUTE_TYPE = 'QUALIFIER'
AND a.INCOMPATABILITY_GRP_CODE = 'EXCL'
AND a.PRICING_PHASE_ID = 1
AND a.PRICING_STATUS_CODE = 'N'
AND a.LINE_INDEX = p_line_index
AND b.PRICING_STATUS_CODE = 'N'
AND b.LINE_INDEX = p_line_index
AND b.INCOMPATABILITY_GRP_CODE = 'EXCL'
AND b.PRICING_PHASE_ID = 1
AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
AND b.PRODUCT_UOM_CODE = p_order_uom_code
AND b.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND b.list_line_id = a.LIST_LINE_ID
/* AND EXISTS (SELECT 'X' -- uom match, shulin
FROM qp_npreq_line_attrs_tmp b
WHERE LINE_INDEX = p_line_index
AND PRICING_STATUS_CODE = 'N'
AND INCOMPATABILITY_GRP_CODE = 'EXCL'
AND PRICING_PHASE_ID = 1
AND ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
AND PRODUCT_UOM_CODE = p_order_uom_code
AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND a.LIST_LINE_ID = b.LIST_LINE_ID) */
AND a.LINE_DETAIL_INDEX = c.LINE_DETAIL_INDEX
AND c.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
UNION
SELECT z.PRECED , z.LIST_LINE_ID , z.QUALIFIER_PRECEDENCE QUAL_PRECED
FROM (
SELECT /*+ ORDERED USE_NL(b) index(c qp_preq_line_attrs_tmp_N5) rec_exist_with_order_uom_cur */
nvl(b.PRODUCT_PRECEDENCE,5000) PRECED , c.LIST_LINE_ID , nvl(c.QUALIFIER_PRECEDENCE,-99999) QUALIFIER_PRECEDENCE
FROM qp_npreq_line_attrs_tmp c,qp_npreq_ldets_tmp b
WHERE c.PRICING_PHASE_ID = L_PRICE_LIST_PHASE_ID
AND c.PRODUCT_UOM_CODE = p_order_uom_code
AND c.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
AND c.LINE_INDEX = p_line_index
AND c.INCOMPATABILITY_GRP_CODE = 'EXCL'
AND c.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND c.LINE_DETAIL_INDEX = b.LINE_DETAIL_INDEX
AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND c.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
ORDER BY 3,1) z
ORDER BY 3,1;
SELECT nvl(a.QUALIFIER_PRECEDENCE,5000) PRECED , a.LIST_LINE_ID , -99999 QUAL_PRECED
FROM qp_npreq_line_attrs_tmp a, qp_npreq_ldets_tmp c, qp_npreq_line_attrs_tmp b
WHERE a.ATTRIBUTE_TYPE = 'QUALIFIER'
AND a.INCOMPATABILITY_GRP_CODE = 'EXCL'
AND a.PRICING_PHASE_ID = 1
AND a.PRICING_STATUS_CODE = 'N'
AND a.LINE_INDEX = p_line_index
AND b.PRICING_STATUS_CODE = 'N'
AND b.LINE_INDEX = p_line_index
AND b.INCOMPATABILITY_GRP_CODE = 'EXCL'
AND b.PRICING_PHASE_ID = 1
AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
AND b.PRIMARY_UOM_FLAG = QP_PREQ_GRP.G_YES
AND b.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND b.list_line_id = a.LIST_LINE_ID
AND a.LINE_DETAIL_INDEX = c.LINE_DETAIL_INDEX
AND c.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
UNION
SELECT z.PRECED , z.LIST_LINE_ID , z.QUALIFIER_PRECEDENCE QUAL_PRECED
FROM(
SELECT /*+ ORDERED USE_NL(c d) index(b qp_preq_line_attrs_tmp_N5) rec_exist_with_pri_flag_cur */
nvl(c.PRODUCT_PRECEDENCE,5000) PRECED , b.LIST_LINE_ID , nvl(b.QUALIFIER_PRECEDENCE ,-99999) QUALIFIER_PRECEDENCE
FROM qp_npreq_line_attrs_tmp b,qp_npreq_ldets_tmp c
WHERE b.PRIMARY_UOM_FLAG = QP_PREQ_GRP.G_YES
AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND b.CONTEXT = 'ITEM'
AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRODUCT_TYPE
AND b.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND b.INCOMPATABILITY_GRP_CODE = 'EXCL'
AND b.LINE_INDEX = p_line_index
AND b.PRICING_PHASE_ID = L_PRICE_LIST_PHASE_ID
AND c.LINE_DETAIL_INDEX = b.LINE_DETAIL_INDEX
AND c.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
ORDER BY 3,1) z
ORDER BY 3,1;
g_precedence_tbl.delete;
g_qual_precedence_tbl.delete;
g_list_line_id_tbl.delete;
PROCEDURE Update_Invalid_List_Lines(p_incomp_grp_code VARCHAR2,
p_line_index NUMBER,
p_pricing_phase_id NUMBER,
p_status_code VARCHAR2,
p_status_text VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) AS
v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Resolve_Incompatability_PVTRUN.Update_Invalid_List_Lines';
INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd2,qp_npreq_lines_tmp_N1,LINE_INDEX,1
*/
IF p_status_code IN (QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV) THEN
UPDATE qp_npreq_lines_tmp -- upd1
SET PRICING_STATUS_CODE = p_status_code,
PRICING_STATUS_TEXT = p_status_text,
PROCESSED_CODE = NULL -- To prevent big search from hapenning , if failed in mini search due to above reasons
WHERE LINE_INDEX = p_line_index;
UPDATE qp_npreq_lines_tmp -- upd2
SET PROCESSED_CODE = QP_PREQ_GRP.G_STS_LHS_NOT_FOUND,
PRICING_STATUS_CODE = p_status_code,
PRICING_STATUS_TEXT = p_status_text
WHERE LINE_INDEX = p_line_index;
INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
INDX,QP_Resolve_Incompatability_PVTRUN.update_invalid_list_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
*/
UPDATE qp_npreq_ldets_tmp -- upd3
SET PRICING_STATUS_CODE = p_status_code
WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_code
AND LINE_INDEX = p_line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = p_status_code
WHERE a.LIST_LINE_ID
IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = p_status_code
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_code
AND b.LINE_INDEX = p_line_index)
AND a.LINE_INDEX = p_line_index; */
END Update_Invalid_List_lines;
SELECT /*+ ORDERED USE_NL(a) INDEX(a QP_PREQ_LDETS_TMP_N2) */
DISTINCT a.LINE_INDEX , a.INCOMPATABILITY_GRP_CODE ,a.PRICING_STATUS_CODE,b.LINE_UOM_CODE,b.LINE_QUANTITY,
b.PROCESSING_ORDER,b.UOM_QUANTITY, b.CONTRACT_START_DATE, b.CONTRACT_END_DATE
FROM qp_npreq_lines_tmp b , qp_npreq_ldets_tmp a
WHERE a.INCOMPATABILITY_GRP_CODE IS NOT NULL
AND a.PRICING_PHASE_ID = p_pricing_phase_id
AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND a.LINE_INDEX = b.LINE_INDEX
--AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED
ORDER BY a.LINE_INDEX;
SELECT COUNT(*) ATTRIBUTE_COUNT
FROM qp_npreq_line_attrs_tmp
WHERE LIST_LINE_ID = p_list_line_id
AND LINE_INDEX = p_line_index
AND ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRICING_TYPE
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
l_precedence_tbl.delete;
select name into v_price_list from qp_list_headers_vl where
list_header_id = (select list_header_id from qp_list_lines
where list_line_id = v_list_line_id);
select name into v_dup_price_list from qp_list_headers_vl where
list_header_id = (select list_header_id from qp_list_lines
where list_line_id = v_dup_list_line_id);
Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,l_status_text, v_return_status,
v_return_status_text);
UPDATE qp_npreq_ldets_tmp -- upd1
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UOM_FAILURE
WHERE INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
AND LINE_INDEX = i.line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> v_list_line_id
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UOM_FAILURE
WHERE a.LIST_LINE_ID
IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UOM_FAILURE
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
AND b.LINE_INDEX = i.line_index)
AND a.LINE_INDEX = i.line_index; */
UPDATE qp_npreq_lines_tmp a
SET a.price_list_header_id = (SELECT list_header_id from qp_list_lines where list_line_id = v_list_line_id )
WHERE ( a.price_list_header_id < 0 OR a.price_list_header_id IS NULL)
AND a.line_index = i.line_index;
l_sql_stmt := 'select ' || nvl(ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date, i.contract_end_date, i.line_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits), -999999) ||' from dual';
-- no need to update l_order_qty, since it has been initialized to i.line_quantity
IF l_debug = FND_API.G_TRUE THEN
QP_PREQ_GRP.engine_debug('pric_qty same as order_qty :' || v_pricing_qty); --shu 12/26/2001
-- no need to update l_order_qty, since it has been initialized to i.line_quantity
l_uom_quantity := nvl(i.uom_quantity, 1); -- to back support OM, for case user order 2 of 6 MTH service
/* no need if bulk update later
UPDATE qp_npreq_lines_tmp
SET PRICED_UOM_CODE = i.line_uom_code,
PRICED_QUANTITY = v_pricing_qty, -- shulin bug 1829731
LINE_UOM_CODE = i.line_uom_code, -- order_uom
LINE_QUANTITY = l_order_qty, -- shu_latest
UOM_QUANTITY =1
WHERE LINE_INDEX = i.line_index;
select name into v_price_list from qp_list_headers_vl where
list_header_id = (select list_header_id from qp_list_lines
where list_line_id = v_list_line_id);
select name into v_dup_price_list from qp_list_headers_vl where
list_header_id = (select list_header_id from qp_list_lines
where list_line_id = v_dup_list_line_id);
Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,l_status_text, v_return_status,
v_return_status_text);
-- Update all the other list lines to status 'P'
UPDATE qp_npreq_ldets_tmp -- upd2
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
WHERE INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
AND LINE_INDEX = i.line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> v_list_line_id
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
AND b.LINE_INDEX = i.line_index)
AND a.LINE_INDEX = i.line_index; */
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
WHERE a.LIST_LINE_ID
IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_PRIMARY_UOM_FLAG
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.INCOMPATABILITY_GRP_CODE = i.INCOMPATABILITY_GRP_CODE
AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
AND b.LINE_INDEX = i.line_index)
AND a.LINE_INDEX = i.line_index; */
UPDATE qp_npreq_lines_tmp a
SET a.price_list_header_id = (SELECT list_header_id from qp_list_lines where list_line_id = v_list_line_id )
WHERE ( a.price_list_header_id < 0 OR a.price_list_header_id IS NULL)
AND a.line_index = i.line_index;
QP_PREQ_GRP.engine_debug('Data Updated');
l_sql_stmt := 'select ' || ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date,
i.contract_end_date, v_primary_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits) ||' from dual';
l_sql_stmt := 'select ' || ROUND (OKS_OMINT_PUB.get_quantity (i.contract_start_date,
i.contract_end_date, i.line_uom_code, QP_PREQ_GRP.G_CURRENT_USER_OP_UNIT), l_max_decimal_digits) ||' from dual';
Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV,l_status_text, v_return_status,
v_return_status_text);
UPDATE qp_npreq_lines_tmp -- upd1
SET PRICED_UOM_CODE = v_primary_uom_code,
PRICED_QUANTITY = v_pricing_qty, -- shulin bug 1829731
LINE_UOM_CODE = l_order_uom_code, -- order_uom, i.line_uom_code
LINE_QUANTITY = l_order_qty, -- shu_latest
UOM_QUANTITY = decode(l_duration_passed,
'Y', l_uom_quantity,
'N', uom_quantity,
uom_quantity)
WHERE LINE_INDEX = i.line_index;
Update_Invalid_List_Lines(i.INCOMPATABILITY_GRP_CODE,i.line_index,p_pricing_phase_id,
QP_PREQ_GRP.G_STATUS_INVALID_UOM,l_status_text, v_return_status,
v_return_status_text);
UPDATE qp_npreq_lines_tmp
SET PRICED_UOM_CODE = l_line_uom_code_tbl(i), --priced uom is the same as order uom
PRICED_QUANTITY = l_priced_quantity_tbl(i), -- shu_latest
LINE_UOM_CODE = l_line_uom_code_tbl(i), -- order uom
LINE_QUANTITY = l_line_quantity_tbl(i), -- shu_latest
UOM_QUANTITY = l_uom_quantity_tbl(i) -- shu_latest
WHERE LINE_INDEX = l_line_index_tbl(i);
UPDATE qp_npreq_line_attrs_tmp
SET VALUE_FROM = qp_number.number_to_canonical(l_upd_priced_qty_tbl(i))
WHERE LINE_INDEX = l_upd_line_index_tbl(i)
AND CONTEXT = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
AND ATTRIBUTE = QP_PREQ_GRP.G_QUANTITY_ATTRIBUTE
AND ATTRIBUTE_TYPE = QP_PREQ_GRP.G_PRICING_TYPE
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED;
SELECT b.CREATED_FROM_LIST_HEADER_ID, b.CREATED_FROM_LIST_LINE_ID,b.line_detail_index, --3244060
b.CREATED_FROM_LIST_LINE_TYPE, b.OPERAND_CALCULATION_CODE, b.OPERAND_VALUE,a.ESTIM_GL_VALUE ,
a.BENEFIT_PRICE_LIST_LINE_ID,a.PRICE_BREAK_TYPE_CODE,b.LINE_QUANTITY,b.GROUP_QUANTITY,b.GROUP_AMOUNT, --[julin/4240067/4307242]
b.modifier_level_code
FROM QP_LIST_LINES a, qp_npreq_ldets_tmp b, qp_npreq_line_attrs_tmp c
WHERE a.LIST_HEADER_ID = b.CREATED_FROM_LIST_HEADER_ID
AND a.LIST_LINE_ID = b.CREATED_FROM_LIST_LINE_ID
AND b.CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND b.LINE_INDEX = p_line_index
AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND ((b.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES)
OR p_manual_dis_flag = QP_PREQ_GRP.G_NO)
AND c.LIST_LINE_ID(+) = a.LIST_LINE_ID
AND c.LIST_HEADER_ID(+) = a.LIST_HEADER_ID
AND c.ATTRIBUTE_TYPE(+) = 'QUALIFIER'
AND c.INCOMPATABILITY_GRP_CODE(+) = p_incomp_grp_id
AND c.PRICING_PHASE_ID(+) = p_pricing_phase_id
AND c.PRICING_STATUS_CODE(+) = 'N'
AND c.LINE_INDEX(+) = p_line_index
AND least(nvl(b.PRODUCT_PRECEDENCE,5000),nvl(c.QUALIFIER_PRECEDENCE,5000)) = nvl(p_precedence, least(nvl(b.PRODUCT_PRECEDENCE,5000),nvl(c.QUALIFIER_PRECEDENCE,5000)));
SELECT b.CREATED_FROM_LIST_HEADER_ID, b.CREATED_FROM_LIST_LINE_ID,
b.CREATED_FROM_LIST_LINE_TYPE, b.OPERAND_CALCULATION_CODE, b.OPERAND_VALUE,a.ESTIM_GL_VALUE ,
a.BENEFIT_PRICE_LIST_LINE_ID,a.PRICE_BREAK_TYPE_CODE,b.GROUP_QUANTITY,b.GROUP_AMOUNT
FROM QP_LIST_LINES a, qp_npreq_ldets_tmp b
WHERE a.LIST_HEADER_ID = b.CREATED_FROM_LIST_HEADER_ID
AND a.LIST_LINE_ID = b.CREATED_FROM_LIST_LINE_ID
AND b.CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND b.LINE_INDEX = p_line_index
AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND ((b.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
p_manual_dis_flag = QP_PREQ_GRP.G_NO);
SELECT nvl(PRICED_QUANTITY,LINE_QUANTITY)
FROM qp_npreq_lines_tmp
WHERE LINE_INDEX = p_line_index;
SELECT OPERAND
FROM QP_LIST_LINES
WHERE LIST_LINE_ID = p_list_line_id
AND ARITHMETIC_OPERATOR = QP_PREQ_GRP.G_UNIT_PRICE;
SELECT a.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp a, qp_npreq_line_attrs_tmp b
WHERE a.CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
AND a.PRICING_PHASE_ID = p_pricing_phase_id
AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND a.LINE_INDEX = p_line_index
AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND ((a.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
p_manual_dis_flag = QP_PREQ_GRP.G_NO)
AND b.LIST_LINE_ID(+) = a.CREATED_FROM_LIST_LINE_ID
AND b.LIST_HEADER_ID(+) = a.CREATED_FROM_LIST_HEADER_ID
AND b.ATTRIBUTE_TYPE(+) = 'QUALIFIER'
AND b.INCOMPATABILITY_GRP_CODE(+) = p_incomp_grp_id
AND b.PRICING_PHASE_ID(+) = p_pricing_phase_id
AND b.PRICING_STATUS_CODE(+) = 'N'
AND b.LINE_INDEX(+) = p_line_index
AND least(nvl(a.PRODUCT_PRECEDENCE,5000),nvl(b.QUALIFIER_PRECEDENCE,5000)) = nvl(p_precedence, least(nvl(a.PRODUCT_PRECEDENCE,5000),nvl(b.QUALIFIER_PRECEDENCE,5000)))
ORDER BY BEST_PERCENT DESC;
SELECT CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp
WHERE CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
AND PRICING_PHASE_ID = p_pricing_phase_id
AND INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND LINE_INDEX = p_line_index
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND ((AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
p_manual_dis_flag = QP_PREQ_GRP.G_NO)
ORDER BY BEST_PERCENT DESC;
select pricing_attribute
into l_volume_attribute
from qp_pricing_attributes
where list_line_id = i.created_from_list_line_id
and pricing_attribute_context = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
and excluder_flag='N'; --3607956
UPDATE qp_npreq_ldets_tmp -- upd1
SET BEST_PERCENT = v_benefit_percent
WHERE CREATED_FROM_LIST_HEADER_ID = i.CREATED_FROM_LIST_HEADER_ID
AND CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
AND PRICING_PHASE_ID = p_pricing_phase_id
AND INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND LINE_INDEX = p_line_index;
UPDATE qp_npreq_ldets_tmp -- upd2
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
WHERE CREATED_FROM_LIST_LINE_ID <> v_list_line_id
AND CREATED_FROM_LIST_TYPE_CODE not in (QP_PREQ_GRP.G_PRICE_LIST_HEADER,QP_PREQ_GRP.G_AGR_LIST_HEADER)
AND PRICING_PHASE_ID = p_pricing_phase_id
AND INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND LINE_INDEX = p_line_index
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
QP_PREQ_GRP.engine_debug('Number of parent lines updated with status B :' || SQL%ROWCOUNT);
UPDATE qp_npreq_ldets_tmp x-- upd2
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
WHERE CREATED_FROM_LIST_LINE_ID in
(select RELATED_LIST_LINE_ID
FROM QP_NPREQ_RLTD_LINES_TMP y,qp_npreq_ldets_tmp z
WHERE y.LIST_LINE_ID =z.CREATED_FROM_LIST_LINE_ID
AND y.LINE_DETAIL_INDEX = z.LINE_DETAIL_INDEX
AND z.PRICING_STATUS_CODE=QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
);
QP_PREQ_GRP.engine_debug('Number of lines child updated with status B :' || SQL%ROWCOUNT);
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
AND b.LINE_INDEX = p_line_index)
AND a.LINE_INDEX = p_line_index; */
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
WHERE a.LIST_LINE_ID
IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND b.CREATED_FROM_LIST_LINE_ID <> v_list_line_id
AND b.LINE_INDEX = p_line_index)
AND a.LINE_INDEX = p_line_index; */
PROCEDURE Delete_Lines_Complete (p_line_index_tbl IN QP_PREQ_GRP.NUMBER_TYPE,
p_pricing_status_text IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_return_status_txt OUT NOCOPY VARCHAR2) AS
BEGIN
l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
UPDATE qp_npreq_lines_tmp
SET pricing_status_code = QP_PREQ_PUB.G_NOT_VALID, process_status = QP_PREQ_PUB.G_NOT_VALID
WHERE line_index = p_line_index_tbl(i)
and line_id is null; --bug 7539796
UPDATE qp_npreq_ldets_tmp
SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,2000)
WHERE LINE_INDEX = p_line_index_tbl(i);
UPDATE qp_npreq_line_attrs_tmp
SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,240)
WHERE LINE_INDEX = p_line_index_tbl(i);
UPDATE qp_npreq_rltd_lines_tmp
SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,240)
WHERE RELATED_LINE_INDEX = p_line_index_tbl(i);
x_return_status_txt := 'Unexpected error in QP_Resolve_Incompatability_PVT.Delete_Lines_Complete: ' || SQLERRM;
END Delete_Lines_Complete;
PROCEDURE Delete_Ldets_Complete (p_line_detail_index_tbl IN QP_PREQ_GRP.NUMBER_TYPE,
p_pricing_status_text IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_return_status_txt OUT NOCOPY VARCHAR2) AS
BEGIN
l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
UPDATE qp_npreq_ldets_tmp a
SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,2000)
WHERE LINE_DETAIL_INDEX = p_line_detail_index_tbl(i) OR
EXISTS (SELECT 1 --[julin/4671446] also deleting children ldets
FROM qp_npreq_rltd_lines_tmp
WHERE LINE_DETAIL_INDEX = p_line_detail_index_tbl(i)
AND RELATED_LINE_DETAIL_INDEX = a.LINE_DETAIL_INDEX);
UPDATE qp_npreq_rltd_lines_tmp
SET PRICING_STATUS_CODE = QP_PREQ_PUB.G_STATUS_DELETED,
PRICING_STATUS_TEXT = substr(p_pricing_status_text,1,240)
WHERE (LINE_DETAIL_INDEX = p_line_detail_index_tbl(i) OR
RELATED_LINE_DETAIL_INDEX = p_line_detail_index_tbl(i)); --[julin/4671446] also deleting children ldets
x_return_status_txt := 'Unexpected error in QP_Resolve_Incompatability_PVT.Delete_Ldets_Complete: ' || SQLERRM;
END Delete_Ldets_Complete;
PROCEDURE Delete_Incompatible_Lines(p_pricing_phase_id NUMBER,
p_line_index NUMBER,
p_incomp_grp_id VARCHAR2 := NULL,
p_list_line_id NUMBER,
p_excl_discount BOOLEAN,
p_manual_dis_flag VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) AS
-- [prarasto/4141235] find loser prg line indexes to be deleted, incompatibility group
--frontported fix done in 4134088
CURSOR l_del_prg_lines_grp_cur IS
SELECT rltd.RELATED_LINE_INDEX
FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
WHERE ldets.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND ldets.LINE_INDEX = p_line_index
AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT)
AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
AND rltd.LINE_INDEX = p_line_index;
SELECT rltd.RELATED_LINE_DETAIL_INDEX
FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
WHERE ldets.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND ldets.LINE_INDEX = p_line_index
AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT)
AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
AND rltd.LINE_INDEX = p_line_index;
SELECT rltd.RELATED_LINE_INDEX
FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
WHERE ldets.LINE_INDEX = p_line_index
AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_PROMO_GOODS_DISCOUNT)
AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
AND rltd.LINE_INDEX = p_line_index;
SELECT rltd.RELATED_LINE_DETAIL_INDEX
FROM qp_npreq_ldets_tmp ldets, qp_npreq_rltd_lines_tmp rltd
WHERE ldets.LINE_INDEX = p_line_index
AND ldets.PRICING_PHASE_ID = p_pricing_phase_id
AND ldets.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ldets.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND ldets.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_OTHER_ITEM_DISCOUNT)
AND ldets.CREATED_FROM_LIST_LINE_ID = rltd.LIST_LINE_ID
AND rltd.LINE_INDEX = p_line_index;
v_routine_name CONSTANT VARCHAR2(240):='Routine:QP_Resolve_Incompatability_PVTRUN.Delete_Incompatible_Lines';
QP_PREQ_GRP.engine_debug('Enter Delete_Incompatible_Lines:'||p_line_index||':'||p_incomp_grp_id||':'||p_list_line_id||':'||p_manual_dis_flag);
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
*/
UPDATE qp_npreq_ldets_tmp --upd1
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND LINE_INDEX = p_line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND CREATED_FROM_LIST_LINE_TYPE NOT IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
UPDATE qp_npreq_ldets_tmp
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND LINE_INDEX = p_line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd2,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
*/
UPDATE qp_npreq_ldets_tmp --upd2
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW,
AUTOMATIC_FLAG = QP_PREQ_GRP.G_NO,
APPLIED_FLAG = QP_PREQ_GRP.G_NO
WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND LINE_INDEX = p_line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
WHERE a.LIST_LINE_ID
IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND b.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
AND b.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
AND b.LINE_INDEX = p_line_index)
AND a.LINE_INDEX = p_line_index; */
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
*/
UPDATE qp_npreq_ldets_tmp --upd3
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
WHERE LINE_INDEX = p_line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND CREATED_FROM_LIST_LINE_TYPE NOT IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd4,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
*/
--Begin Bug No: 7691661
UPDATE qp_npreq_ldets_tmp
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
WHERE LINE_INDEX = p_line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
UPDATE qp_npreq_ldets_tmp --upd4
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW,
AUTOMATIC_FLAG = QP_PREQ_GRP.G_NO,
APPLIED_FLAG = QP_PREQ_GRP.G_NO
WHERE LINE_INDEX = p_line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
AND CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
AND PRICING_STATUS_CODE IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL);
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
WHERE a.LIST_LINE_ID
IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND b.CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND ASK_FOR_FLAG = QP_PREQ_GRP.G_NO
AND b.CREATED_FROM_LIST_LINE_TYPE IN (QP_PREQ_GRP.G_DISCOUNT,QP_PREQ_GRP.G_SURCHARGE)
AND b.LINE_INDEX = p_line_index)
AND a.LINE_INDEX = p_line_index; */
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
INDX,QP_Resolve_Incompatability_PVTRUN.delete_incompatible_lines.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
*/
UPDATE qp_npreq_ldets_tmp --upd5
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
WHERE INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND LINE_INDEX = p_line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
UPDATE qp_npreq_ldets_tmp
SET PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
WHERE LINE_INDEX = p_line_index
AND PRICING_PHASE_ID = p_pricing_phase_id
AND CREATED_FROM_LIST_LINE_ID <> p_list_line_id
AND LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE -- Don't delete PBH Children any time
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
Delete_Lines_Complete(l_del_index_tbl, 'PRG DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
Delete_Ldets_Complete(l_del_index_tbl, 'OID DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
Delete_Lines_Complete(l_del_index_tbl, 'PRG DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
Delete_Ldets_Complete(l_del_index_tbl, 'OID DELETED BY INCOMPATIBILITY LOGIC', l_status_code, l_status_text);
END Delete_Incompatible_Lines;
SELECT DISTINCT INCOMPATABILITY_GRP_CODE
FROM qp_npreq_ldets_tmp
WHERE PRICING_PHASE_ID = p_pricing_phase_id
AND INCOMPATABILITY_GRP_CODE IS NOT NULL
AND LINE_INDEX = p_line_index
AND ((AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND p_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
p_manual_dis_flag = QP_PREQ_GRP.G_NO)
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
AND b.LINE_INDEX = p_line_index)
AND a.LINE_INDEX = p_line_index; */
Delete_Incompatible_Lines(p_pricing_phase_id,
p_line_index,
i.INCOMPATABILITY_GRP_CODE,
x_list_line_id,
v_excl_flag,
v_manual_dis_flag,
x_ret_status);
Delete_Incompatible_Lines(p_pricing_phase_id,
p_line_index,
NULL, -- incomp_grp_id
x_list_line_id,
v_excl_flag,
v_manual_dis_flag,
x_ret_status);
SELECT DISTINCT INCOMPATABILITY_GRP_CODE , PRICING_STATUS_CODE
FROM qp_npreq_ldets_tmp
WHERE PRICING_PHASE_ID = p_pricing_phase_id
AND INCOMPATABILITY_GRP_CODE IS NOT NULL
AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND ((AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND l_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
l_manual_dis_flag = QP_PREQ_GRP.G_NO)
AND LINE_INDEX = p_line_index;
SELECT a.CREATED_FROM_LIST_HEADER_ID,a.CREATED_FROM_LIST_LINE_ID,a.INCOMPATABILITY_GRP_CODE,a.ASK_FOR_FLAG
FROM qp_npreq_ldets_tmp a
WHERE EXISTS (SELECT 'X'
FROM qp_npreq_line_attrs_tmp b
WHERE a.LINE_INDEX = b.LINE_INDEX
AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_QUALIFIER_TYPE
AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED
AND b.ATTRIBUTE IN (QP_PREQ_GRP.G_QUAL_ATTRIBUTE1,
QP_PREQ_GRP.G_QUAL_ATTRIBUTE2,
QP_PREQ_GRP.G_QUAL_ATTRIBUTE6)
AND b.CONTEXT = QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
AND b.VALUE_FROM = decode(b.ATTRIBUTE,
QP_PREQ_GRP.G_QUAL_ATTRIBUTE1,to_char(a.CREATED_FROM_LIST_HEADER_ID),
QP_PREQ_GRP.G_QUAL_ATTRIBUTE2,to_char(a.CREATED_FROM_LIST_LINE_ID),
QP_PREQ_GRP.G_QUAL_ATTRIBUTE6,to_char(a.CREATED_FROM_LIST_HEADER_ID)))
AND a.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND a.PRICING_PHASE_ID = p_pricing_phase_id
AND a.LINE_INDEX = p_line_index
AND a.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND ((a.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND l_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
l_manual_dis_flag = QP_PREQ_GRP.G_NO)
UNION
SELECT a.CREATED_FROM_LIST_HEADER_ID,a.CREATED_FROM_LIST_LINE_ID,a.INCOMPATABILITY_GRP_CODE,'N' ASK_FOR_FLAG
FROM qp_npreq_ldets_tmp a
WHERE a.ASK_FOR_FLAG = QP_PREQ_GRP.G_NO -- Removed NVL , expect some issues
AND a.INCOMPATABILITY_GRP_CODE = p_incomp_grp_id
AND a.PRICING_PHASE_ID = p_pricing_phase_id
AND a.LINE_INDEX = p_line_index
AND a.LINE_DETAIL_TYPE_CODE <> QP_PREQ_GRP.G_CHILD_DETAIL_TYPE
AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
AND ((a.AUTOMATIC_FLAG = QP_PREQ_GRP.G_YES AND l_manual_dis_flag = QP_PREQ_GRP.G_YES) OR
l_manual_dis_flag = QP_PREQ_GRP.G_NO)
ORDER BY 4 desc;
/* UPDATE qp_npreq_line_attrs_tmp a
SET a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
WHERE a.LIST_LINE_ID IN (SELECT b.CREATED_FROM_LIST_LINE_ID
FROM qp_npreq_ldets_tmp b
WHERE b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC
AND b.PRICING_PHASE_ID = p_pricing_phase_id
AND b.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
AND b.LINE_INDEX = p_line_index)
AND a.LINE_INDEX = p_line_index; */
l_precedence_tbl.delete;
QP_PREQ_GRP.engine_debug('Before Update ......');
-- Update all the other list lines to status 'I' for each incomp grp
IF (v_excl_flag = FALSE) THEN
Delete_Incompatible_Lines(p_pricing_phase_id,
p_line_index,
i.INCOMPATABILITY_GRP_CODE,
v_first_list_line_id,
v_excl_flag,
p_manual_dis_flag,
x_ret_status);
Delete_Incompatible_Lines(p_pricing_phase_id,
p_line_index,
NULL, -- incomp_grp_id
v_excl_list_line_id,
v_excl_flag,
p_manual_dis_flag,
x_ret_status);