[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT line, text
FROM user_errors
WHERE name = UPPER(l_pkg_name)
AND TYPE = DECODE(p_pkg_type,'SPEC','PACKAGE',
'BODY','PACKAGE BODY');
l_insert_row VARCHAR2(10):='N';
SELECT SEEDED_VALUE INTO l_pkg_ver FROM qp_parameters_b WHERE PARAMETER_CODE='BLD_SRC_ATTR_ACTV_VER' AND ROWNUM=1;
l_insert_row := 'Y';
UPDATE qp_pte_segments
SET sourcing_status = 'N',
used_in_setup = 'N'
WHERE sourcing_status = 'Y' OR used_in_setup = 'Y';
UPDATE qp_pte_segments
SET sourcing_status = 'Y',
used_in_setup = 'Y'
WHERE segment_id = v_segment_id;
IF (l_insert_row = 'N') THEN
PUT_LINE('found row in qp_params row updated with new version: ' || To_Char(To_Number(l_pkg_ver)+1));
oe_debug_pub.add('found row in qp_params row updated with new version: ' || To_Char(To_Number(l_pkg_ver)+1));
UPDATE qp_parameters_b
SET SEEDED_VALUE=Decode(l_pkg_ver,'5','1',To_Char(To_Number(l_pkg_ver)+1)) ,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = nvl(TO_NUMBER (FND_PROFILE.VALUE ('USER_ID')),-1) ,
LAST_UPDATE_LOGIN = nvl(TO_NUMBER (FND_PROFILE.VALUE ('LOGIN_ ID')),-1)
WHERE parameter_code='BLD_SRC_ATTR_ACTV_VER';
INSERT INTO qp_parameters_b (parameter_id, parameter_level,parameter_code,value_set_id,
ADVANCED_PRICING_ONLY,SEEDED_VALUE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
VALUES (-9999,'CONC','BLD_SRC_ATTR_ACTV_VER',-9999,
'N','1',SYSDATE,
-1,SYSDATE,-1,-1);
PUT_LINE('found row in qp_params but no row updated so insert: ' );
oe_debug_pub.add('found row in qp_params but no row updated so insert: ');
PUT_LINE('insert row flag is Y ' );
oe_debug_pub.add('insert row flag is Y ');
INSERT INTO qp_parameters_b (parameter_id, parameter_level,parameter_code,value_set_id,
ADVANCED_PRICING_ONLY,SEEDED_VALUE,CREATION_DATE,CREATED_BY,
LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
VALUES (-9999,'CONC','BLD_SRC_ATTR_ACTV_VER',-9999,
'N','1',SYSDATE,
-1,SYSDATE,-1,-1);
g_Segment_Ctr.DELETE;
SELECT
arules.attribute_code,
arules.src_type,
arules.src_api_pkg,
arules.src_api_fn,
arules.src_profile_option,
arules.src_system_variable_expr,
arules.src_constant_value,
condelem.value_string,
condelem.attribute_code
FROM
oe_def_attr_condns aconds,
oe_def_condn_elems condelem,
oe_def_attr_def_rules arules
WHERE
aconds.database_object_name IN (p_db1, p_db2)
AND condelem.condition_id = aconds.condition_id
AND condelem.attribute_code IN ('PRICING_CONTEXT', 'QUALIFIER_CONTEXT')
AND arules.attr_def_condition_id = aconds.attr_def_condition_id
--added this condition to look at enabled_flag to avoid duplicate sourcing due to
--OM changes to lct. enabled_flag is a new column introduced--spgopal
AND NVL(aconds.enabled_flag, 'Y') = 'Y'
AND EXISTS (SELECT 'x' FROM qp_price_req_sources prs,oe_def_condn_elems condelem1
WHERE condelem1.attribute_code = 'SRC_SYSTEM_CODE'
AND condelem1.value_string = prs.source_system_code
AND prs.request_type_code = p_request_type_code
AND condelem.condition_id = condelem1.condition_id);
SELECT qpseg.segment_mapping_column attribute_code,
qpsour.user_sourcing_type src_type,
qpsour.user_value_string value_string,
qpsour.segment_id,
qpcon.prc_context_code context_code,
qpcon.prc_context_type context_type,
'2' is_product
FROM
qp_segments_b qpseg,
qp_attribute_sourcing qpsour,
qp_prc_contexts_b qpcon,
qp_pte_request_types_b qpreq,
qp_pte_segments qppseg
WHERE
qpsour.segment_id = qpseg.segment_id
AND qpsour.attribute_sourcing_level = p_sourcing_level
AND qpsour.enabled_flag = 'Y'
AND qpsour.request_type_code = p_request_type_code
AND qpseg.prc_context_id = qpcon.prc_context_id
AND qpreq.request_type_code = qpsour.request_type_code
AND qppseg.pte_code = qpreq.pte_code
AND qppseg.segment_id = qpsour.segment_id
AND qppseg.sourcing_enabled = 'Y'
AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
AND qpcon.prc_context_type IN ('PRICING_ATTRIBUTE', 'QUALIFIER')
UNION
SELECT qpseg.segment_mapping_column attribute_code,
qpsour.user_sourcing_type src_type,
qpsour.user_value_string value_string,
qpsour.segment_id,
qpcon.prc_context_code context_code,
qpcon.prc_context_type context_type,
'1' is_product
FROM
qp_segments_b qpseg,
qp_attribute_sourcing qpsour,
qp_prc_contexts_b qpcon,
qp_pte_request_types_b qpreq,
qp_pte_segments qppseg
WHERE
qpsour.segment_id = qpseg.segment_id
AND qpsour.attribute_sourcing_level = p_sourcing_level
AND qpsour.enabled_flag = 'Y'
AND qpsour.request_type_code = p_request_type_code
AND qpseg.prc_context_id = qpcon.prc_context_id
AND qpreq.request_type_code = qpsour.request_type_code
AND qppseg.pte_code = qpreq.pte_code
AND qppseg.segment_id = qpsour.segment_id
AND qppseg.sourcing_enabled = 'Y'
AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
AND qpcon.prc_context_type = 'PRODUCT'
ORDER BY is_product,attribute_code;
Text('QP_ATTR_MAPPING_PUB.G_Product_Attr_Tbl.delete;',1);
G_Sourced_Contexts_Tbl.DELETE;
SELECT 'Y'
INTO v_is_used
FROM qp_qualifiers
WHERE qualifier_context = l_context_name
AND qualifier_attribute = l_attribute_name
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM QP_PRICE_FORMULA_LINES A
WHERE A.PRICING_ATTRIBUTE_CONTEXT = l_context_name
AND A.PRICING_ATTRIBUTE = l_attribute_name
AND EXISTS
(SELECT /*+ no_unest */ 'x'
FROM QP_LIST_LINES B
WHERE A.PRICE_FORMULA_ID = B.PRICE_BY_FORMULA_ID)
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_price_formula_lines a
WHERE a.pricing_attribute_context = l_context_name
AND a.pricing_attribute = l_attribute_name
AND EXISTS
(SELECT /*+ no_unest */ 'x'
FROM qp_currency_details b
WHERE a.price_formula_id = b.price_formula_id
OR a.price_formula_id = b.markup_formula_id
)
AND ROWNUM < 2;
SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
INTO v_is_used
FROM qp_pricing_attributes
WHERE pricing_attribute_context = l_context_name
AND pricing_attribute = l_attribute_name
AND ROWNUM < 2;
SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n1) */ 'Y'
INTO v_is_used
FROM qp_pricing_attributes
WHERE product_attribute_context = l_context_name
AND product_attribute = l_attribute_name
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_qualifiers
WHERE qualifier_context = l_context_name
AND qualifier_attribute = l_attribute_name
AND active_flag = 'Y'
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_price_formula_lines a, qp_list_lines b, qp_list_headers_b c
WHERE a.pricing_attribute_context = l_context_name
AND a.pricing_attribute = l_attribute_name
AND a.price_formula_id = b.price_by_formula_id
AND b.list_header_id = c.list_header_id
AND c.active_flag = 'Y'
AND rownum < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_price_formula_lines a
WHERE a.pricing_attribute_context = l_context_name AND
a.pricing_attribute = l_attribute_name AND
EXISTS (SELECT 'x'
FROM qp_list_lines b, qp_list_headers_all c -- qp_list_headers_b c --bug 14065769
WHERE a.price_formula_id = b.price_by_formula_id AND
b.list_header_id = c.list_header_id AND
c.active_flag = 'Y')
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_price_formula_lines a, qp_currency_details b
WHERE a.pricing_attribute_context = l_context_name
AND a.pricing_attribute = l_attribute_name
AND EXISTS
(SELECT /*+ no_unest */ 'x'
FROM qp_currency_details b
WHERE a.price_formula_id = b.price_formula_id
OR a.price_formula_id = b.markup_formula_id
)
AND ROWNUM < 2;
SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
INTO v_is_used
FROM qp_pricing_attributes
WHERE pricing_attribute_context = l_context_name
AND pricing_attribute = l_attribute_name
AND list_header_id IN
(SELECT list_header_id FROM qp_list_headers_all -- qp_list_headers_b --bug 14065769
WHERE active_flag = 'Y')
AND ROWNUM < 2;
SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n1) */ 'Y'
INTO v_is_used
FROM qp_pricing_attributes
WHERE product_attribute_context = l_context_name
AND product_attribute = l_attribute_name
AND list_header_id IN
(SELECT list_header_id FROM qp_list_headers_all -- qp_list_headers_b --bug 14065769
WHERE active_flag = 'Y')
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_limits a, qp_list_headers_all b -- qp_list_headers_b b --bug 14065769
WHERE ((a.multival_attr1_context = l_context_name
AND a.multival_attribute1 = l_attribute_name)
OR (a.multival_attr2_context = l_context_name
AND a.multival_attribute2 = l_attribute_name))
AND a.list_header_id = b.list_header_id
AND b.active_flag = 'Y'
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_limit_attributes a, qp_limits b, qp_list_headers_all c -- qp_list_headers_b c --bug 14065769
WHERE a.limit_attribute_context = l_context_name
AND a.limit_attribute = l_attribute_name
AND a.limit_id = b.limit_id
AND b.list_header_id = c.list_header_id
AND c.active_flag = 'Y'
AND ROWNUM < 2;
Text('SELECT ' || v_attr_src_string || ' INTO v_attr_value FROM DUAL;', 3);
G_New_Sourced_Contexts_Tbl.DELETE;
SELECT 'Y'
INTO v_is_used
FROM qp_qualifiers
WHERE qualifier_context = l_context_name
AND qualifier_attribute = l_attribute_name
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_price_formula_lines a
WHERE a.pricing_attribute_context = l_context_name
AND a.pricing_attribute = l_attribute_name
AND EXISTS
(SELECT /*+ no_unest */ 'x'
FROM qp_list_lines b
WHERE a.price_formula_id = b.price_by_formula_id
)
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_price_formula_lines a
WHERE a.pricing_attribute_context = l_context_name
AND a.pricing_attribute = l_attribute_name
AND EXISTS
(SELECT /*+ no_unest */ 'x'
FROM qp_currency_details b
WHERE a.price_formula_id = b.price_formula_id
OR a.price_formula_id = b.markup_formula_id
)
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_currency_details
WHERE curr_attribute_type = l_context_type
AND curr_attribute_context = l_context_name
AND curr_attribute = l_attribute_name
AND rownum < 2;
SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
INTO v_is_used
FROM qp_pricing_attributes
WHERE pricing_attribute_context = l_context_name
AND pricing_attribute = l_attribute_name
AND ROWNUM < 2;
SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n1) */ 'Y'
INTO v_is_used
FROM qp_pricing_attributes
WHERE product_attribute_context = l_context_name
AND product_attribute = l_attribute_name
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_list_lines
WHERE ((break_uom_context = l_context_name
AND break_uom_attribute = l_attribute_name)
OR
(accum_context = l_context_name
AND accum_attribute = l_attribute_name)
)
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_qualifiers
WHERE qualifier_context = l_context_name
AND qualifier_attribute = l_attribute_name
AND active_flag = 'Y'
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_price_formula_lines a, qp_list_lines b, qp_list_headers_b c
WHERE a.pricing_attribute_context = l_context_name
AND a.pricing_attribute = l_attribute_name
AND a.price_formula_id = b.price_by_formula_id
AND b.list_header_id = c.list_header_id
AND c.active_flag = 'Y'
AND rownum < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_price_formula_lines a
WHERE a.pricing_attribute_context = l_context_name AND
a.pricing_attribute = l_attribute_name AND
EXISTS (SELECT /*+ no_unest */ 'x'
FROM qp_list_lines b, qp_list_headers_all c -- qp_list_headers_b c --bug 14065769
WHERE a.price_formula_id = b.price_by_formula_id AND
b.list_header_id = c.list_header_id AND
c.active_flag = 'Y')
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_price_formula_lines a
WHERE a.pricing_attribute_context = l_context_name
AND a.pricing_attribute = l_attribute_name
AND EXISTS
(SELECT /*+ no_unest */ 'x'
FROM qp_currency_details b
WHERE a.price_formula_id = b.price_formula_id
OR a.price_formula_id = b.markup_formula_id
)
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_currency_details
WHERE curr_attribute_type = l_context_type
AND curr_attribute_context = l_context_name
AND curr_attribute = l_attribute_name
AND rownum < 2;
SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
INTO v_is_used
FROM qp_pricing_attributes
WHERE pricing_attribute_context = l_context_name
AND pricing_attribute = l_attribute_name
AND list_header_id IN
(SELECT list_header_id FROM qp_list_headers_all -- qp_list_headers_b --bug 14065769
WHERE active_flag = 'Y')
AND ROWNUM < 2;
SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n1) */ 'Y'
INTO v_is_used
FROM qp_pricing_attributes
WHERE product_attribute_context = l_context_name
AND product_attribute = l_attribute_name
AND list_header_id IN
(SELECT list_header_id FROM qp_list_headers_all -- qp_list_headers_b --bug 14065769
WHERE active_flag = 'Y')
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_limits a, qp_list_headers_all b -- qp_list_headers_b b --bug 14065769
WHERE ((a.multival_attr1_context = l_context_name
AND a.multival_attribute1 = l_attribute_name)
OR (a.multival_attr2_context = l_context_name
AND a.multival_attribute2 = l_attribute_name))
AND a.list_header_id = b.list_header_id
AND b.active_flag = 'Y'
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_limit_attributes a, qp_limits b, qp_list_headers_all c -- qp_list_headers_b c --bug 14065769
WHERE a.limit_attribute_context = l_context_name
AND a.limit_attribute = l_attribute_name
AND a.limit_id = b.limit_id
AND b.list_header_id = c.list_header_id
AND c.active_flag = 'Y'
AND ROWNUM < 2;
SELECT 'Y'
INTO v_is_used
FROM qp_list_lines a, qp_list_headers_all b -- qp_list_headers_b b --bug 14065769
WHERE ((a.break_uom_context = l_context_name
AND a.break_uom_attribute = l_attribute_name)
OR
(a.accum_context = l_context_name
AND a.accum_attribute = l_attribute_name)
)
AND a.list_header_id = b.list_header_id
AND b.active_flag = 'Y'
AND ROWNUM < 2;
Text('x_price_ctxts_result_tbl.delete;',3);
Text('SELECT ' || v_attr_src_string || ' INTO v_attr_value FROM DUAL;', 3);
SELECT DISTINCT request_type_code
FROM qp_price_req_sources;
SELECT DISTINCT request_type_code
FROM QP_PTE_REQUEST_TYPES_B
WHERE ENABLED_FLAG = 'Y'; -- 5365644, 5365968
SELECT SEEDED_VALUE INTO l_pkg_ver FROM qp_parameters_b WHERE PARAMETER_CODE='BLD_SRC_ATTR_ACTV_VER' AND ROWNUM=1;
l_price_contexts_result_tbl.DELETE;
l_qual_contexts_result_tbl.DELETE;
to insert into tmp tables directly for OM Integration
changes by spgopal
*/
PROCEDURE Build_Contexts
( p_request_type_code IN VARCHAR2,
p_line_index IN NUMBER,
p_pricing_type_code IN VARCHAR2,
p_price_list_validated_flag IN VARCHAR2,
--added for MOAC
p_org_id IN NUMBER DEFAULT NULL
)
IS
CURSOR l_line_cur IS
SELECT line_index
FROM qp_npreq_lines_tmp
WHERE line_type_code = Qp_Preq_Pub.G_LINE_LEVEL
AND price_flag IN (Qp_Preq_Pub.G_YES, Qp_Preq_Pub.G_PHASE);
SELECT line_index
FROM qp_npreq_lines_tmp
WHERE line_type_code = Qp_Preq_Pub.G_ORDER_LEVEL
AND price_flag IN (Qp_Preq_Pub.G_YES, Qp_Preq_Pub.G_PHASE);
l_price_contexts_result_tbl.DELETE;
l_qual_contexts_result_tbl.DELETE;
v_price_contexts_result_tbl.DELETE;
v_qual_contexts_result_tbl.DELETE;
l_line_index_tbl.DELETE;
l_attribute_type_tbl.DELETE;
l_context_tbl.DELETE;
l_attribute_tbl.DELETE;
l_value_from_tbl.DELETE;
l_validated_flag_tbl.DELETE;
l_ATTRIBUTE_LEVEL_tbl.DELETE;
l_LIST_HEADER_ID_tbl.DELETE;
l_LIST_LINE_ID_tbl.DELETE;
l_SETUP_VALUE_FROM_tbl.DELETE;
l_SETUP_VALUE_TO_tbl.DELETE;
l_GROUPING_NUMBER_tbl.DELETE;
l_NO_QUALIFIERS_IN_GRP_tbl.DELETE;
l_COMPARISON_OPERATOR_TYPE_tbl.DELETE;
l_APPLIED_FLAG_tbl.DELETE;
l_PRICING_STATUS_CODE_tbl.DELETE;
l_PRICING_STATUS_TEXT_tbl.DELETE;
l_QUALIFIER_PRECEDENCE_tbl.DELETE;
l_DATATYPE_tbl.DELETE;
l_PRICING_ATTR_FLAG_tbl.DELETE;
l_QUALIFIER_type_tbl.DELETE;
l_PRODUCT_UOM_CODE_tbl.DELETE;
l_EXCLUDER_FLAG_tbl.DELETE;
l_PRICING_PHASE_ID_tbl.DELETE;
l_INCOMPATABILITY_GRP_CODE_tbl.DELETE;
l_LINE_DETAIL_type_CODE_tbl.DELETE;
l_MODIFIER_LEVEL_CODE_tbl.DELETE;
l_PRIMARY_UOM_FLAG_tbl.DELETE;
SELECT SEEDED_VALUE INTO l_pkg_ver FROM qp_parameters_b WHERE PARAMETER_CODE='BLD_SRC_ATTR_ACTV_VER' AND ROWNUM=1;
v_price_contexts_result_tbl.DELETE;
v_qual_contexts_result_tbl.DELETE;
l_price_contexts_result_tbl.DELETE;
l_qual_contexts_result_tbl.DELETE;
v_price_contexts_result_tbl.DELETE;
v_qual_contexts_result_tbl.DELETE;
SELECT SEEDED_VALUE INTO l_pkg_ver FROM qp_parameters_b WHERE PARAMETER_CODE='BLD_SRC_ATTR_ACTV_VER' AND ROWNUM=1;
v_price_contexts_result_tbl.DELETE;
v_qual_contexts_result_tbl.DELETE;
Qp_Preq_Grp.ENGINE_DEBUG('Tata Inserting line attributes ----------');
INSERT INTO qp_npreq_line_attrs_tmp
(line_index,
attribute_level,
attribute_type,
context,
attribute,
value_from,
validated_flag,
applied_flag,
pricing_status_code,
pricing_attr_flag
)
VALUES (l_line_index_tbl(i),
Qp_Preq_Pub.G_LINE_LEVEL,
l_attribute_type_tbl(i),
l_context_tbl(i),
l_attribute_tbl(i),
l_value_from_tbl(i),
l_validated_flag_tbl(i),
Qp_Preq_Pub.G_LIST_NOT_APPLIED,
Qp_Preq_Pub.G_STATUS_UNCHANGED,
Qp_Preq_Pub.G_YES
);
/*QP_PREQ_GRP.INSERT_LINE_ATTRS_AT( l_line_index_tbl,
QP_PREQ_PUB.G_LINE_LEVEL,
l_attribute_type_tbl,
l_context_tbl,
l_attribute_tbl,
l_value_from_tbl,
l_validated_flag_tbl,
QP_PREQ_PUB.G_LIST_NOT_APPLIED,
QP_PREQ_PUB.G_STATUS_UNCHANGED,
QP_PREQ_PUB.G_YES,
l_status_code ,
l_status_text );
Qp_Preq_Grp.INSERT_LINE_ATTRS2 ( p_LINE_INDEX_tbl => l_line_index_tbl,
p_LINE_DETAIL_INDEX_tbl => l_line_Detail_index_tbl,
p_ATTRIBUTE_LEVEL_tbl=> l_ATTRIBUTE_LEVEL_tbl,
p_ATTRIBUTE_TYPE_tbl => l_attribute_type_tbl,
p_LIST_HEADER_ID_tbl=> l_LIST_HEADER_ID_tbl,
p_LIST_LINE_ID_tbl=> l_LIST_LINE_ID_tbl,
p_CONTEXT_tbl => l_context_tbl,
p_ATTRIBUTE_tbl => l_attribute_tbl,
p_VALUE_FROM_tbl => l_value_from_tbl,
p_SETUP_VALUE_FROM_tbl=> l_SETUP_VALUE_FROM_tbl,
p_VALUE_TO_tbl => l_value_to_tbl,
p_SETUP_VALUE_TO_tbl=> l_SETUP_VALUE_TO_tbl,
p_GROUPING_NUMBER_tbl=> l_GROUPING_NUMBER_tbl,
p_NO_QUALIFIERS_IN_GRP_tbl=> l_NO_QUALIFIERS_IN_GRP_tbl,
p_COMPARISON_OPERATOR_TYPE_tbl=> l_COMPARISON_OPERATOR_TYPE_tbl,
p_VALIDATED_FLAG_tbl => l_validated_flag_tbl,
p_APPLIED_FLAG_tbl=> l_APPLIED_FLAG_tbl,
p_PRICING_STATUS_CODE_tbl=> l_PRICING_STATUS_CODE_tbl,
p_PRICING_STATUS_TEXT_tbl=> l_PRICING_STATUS_TEXT_tbl,
p_QUALIFIER_PRECEDENCE_tbl=> l_QUALIFIER_PRECEDENCE_tbl,
p_DATATYPE_tbl=> l_DATATYPE_tbl,
p_PRICING_ATTR_FLAG_tbl=> l_PRICING_ATTR_FLAG_tbl,
p_QUALIFIER_TYPE_tbl=> l_QUALIFIER_TYPE_tbl,
p_PRODUCT_UOM_CODE_tbl=> l_PRODUCT_UOM_CODE_tbl,
p_EXCLUDER_FLAG_tbl=> l_EXCLUDER_FLAG_tbl,
p_PRICING_PHASE_ID_tbl=> l_PRICING_PHASE_ID_tbl,
p_INCOMPATABILITY_GRP_CODE_tbl=> l_INCOMPATABILITY_GRP_CODE_tbl,
p_LINE_DETAIL_TYPE_CODE_tbl=> l_LINE_DETAIL_TYPE_CODE_tbl,
p_MODIFIER_LEVEL_CODE_tbl=> l_MODIFIER_LEVEL_CODE_tbl,
p_PRIMARY_UOM_FLAG_tbl=> l_PRIMARY_UOM_FLAG_tbl,
x_status_code => l_status_code,
x_status_text => l_status_text);
Qp_Preq_Grp.ENGINE_DEBUG('End Inserting line attributes ----------');
Qp_Preq_Grp.engine_debug('QP_ATTR_MAPPING_PUB:Bld Contxt Insert LINE_ATTR '||''||l_status_text);
Qp_Preq_Grp.engine_debug('QP_ATTR_MAPPING_PUB:Bld Contxt Insert LINE_ATTR '||' '||SQLERRM);
SELECT DISTINCT pricing_attribute_context, pricing_attribute
FROM qp_pricing_attributes
WHERE product_attribute = 'PRICING_ATTRIBUTE1'
AND product_attribute_context = 'ITEM'
AND product_attr_value = p_item_id
AND pricing_attribute_context IS NOT NULL
AND pricing_attribute IS NOT NULL;
SELECT c.condition_id, d.attr_def_condition_id
FROM oe_def_conditions a, oe_def_condn_elems b,
oe_def_attr_condns c, oe_def_attr_def_rules d, qp_price_req_sources e
WHERE a.database_object_name LIKE 'QP%'
AND a.condition_id = b.condition_id
AND b.attribute_code = 'SRC_SYSTEM_CODE'
AND b.value_string = e.source_system_code
AND e.request_type_code = p_request_type_code
AND b.condition_id = c.condition_id
AND c.attribute_code = p_pricing_attr
AND c.attr_def_condition_id = d.attr_def_condition_id;
SELECT DISTINCT qpseg.segment_code, qpseg.segment_mapping_column,
qpcon.prc_context_code, qpcon.prc_context_type
FROM qp_segments_b qpseg, qp_prc_contexts_b qpcon,
qp_pte_segments qppteseg, qp_pte_request_types_b qpptereq
WHERE qpseg.segment_id = qppteseg.segment_id AND
qpseg.segment_mapping_column = p_pricing_attr AND
qpseg.prc_context_id = qpcon.prc_context_id AND
qpcon.enabled_flag = 'Y' AND
qpptereq.request_type_code = p_request_type_code AND
qpptereq.pte_code = qppteseg.pte_code AND
qppteseg.user_sourcing_method = 'USER ENTERED';
SELECT value_string
INTO l_context_name
FROM oe_def_condn_elems
WHERE condition_id = l_condition_id
AND attribute_code = 'PRICING_CONTEXT';
SELECT DISTINCT pricing_attribute_context, pricing_attribute
FROM qp_pricing_attributes
WHERE pricing_attribute_context IS NOT NULL
AND pricing_attribute_context NOT IN ('VOLUME','ITEM')
AND pricing_attribute_context <> Fnd_Api.G_MISS_CHAR
AND pricing_attribute <> Fnd_Api.G_MISS_CHAR
AND pricing_attribute IS NOT NULL;
SELECT c.condition_id, d.attr_def_condition_id
FROM oe_def_conditions a, oe_def_condn_elems b,
oe_def_attr_condns c, oe_def_attr_def_rules d, qp_price_req_sources e
WHERE a.database_object_name LIKE 'QP%'
AND a.condition_id = b.condition_id
AND b.attribute_code = 'SRC_SYSTEM_CODE'
AND b.value_string = e.source_system_code
AND e.request_type_code = p_request_type_code
AND b.condition_id = c.condition_id
AND c.attribute_code = p_pricing_attr
AND c.attr_def_condition_id = d.attr_def_condition_id;
SELECT DISTINCT qpseg.segment_code, qpseg.segment_mapping_column,
qpcon.prc_context_code, qpcon.prc_context_type
FROM qp_segments_b qpseg, qp_prc_contexts_b qpcon,
qp_pte_segments qppteseg, qp_pte_request_types_b qpptereq
WHERE qpseg.segment_id = qppteseg.segment_id AND
qpseg.segment_mapping_column = p_pricing_attr AND
qpseg.prc_context_id = qpcon.prc_context_id AND
qpcon.enabled_flag = 'Y' AND
qpptereq.request_type_code = p_request_type_code AND
qpptereq.pte_code = qppteseg.pte_code AND
qppteseg.user_sourcing_method = 'USER ENTERED';
SELECT value_string
INTO l_context_name
FROM oe_def_condn_elems
WHERE condition_id = l_condition_id
AND attribute_code = 'PRICING_CONTEXT';
SELECT descriptive_flex_context_code
FROM fnd_descr_flex_contexts
WHERE enabled_flag = 'Y'
AND application_id = 661
AND descriptive_flexfield_name = 'QP_ATTR_DEFNS_PRICING';
SELECT DISTINCT b.condition_id
FROM oe_def_conditions a, oe_def_condn_elems b, qp_price_req_sources c,
oe_def_attr_condns d
WHERE a.database_object_name LIKE 'QP%'
AND a.condition_id = b.condition_id
AND b.attribute_code = 'SRC_SYSTEM_CODE'
AND b.value_string = c.source_system_code
AND c.request_type_code = p_request_type_code
AND d.condition_id = a.condition_id
AND d.attribute_code LIKE 'PRICING_ATTRIBUTE%';
SELECT DISTINCT qpcon.prc_context_code, qpcon.prc_context_type
FROM qp_segments_b qpseg, qp_prc_contexts_b qpcon,
qp_pte_segments qppteseg, qp_pte_request_types_b qpptereq
WHERE qpseg.segment_id = qppteseg.segment_id AND
qpseg.prc_context_id = qpcon.prc_context_id AND
qpcon.enabled_flag = 'Y' AND
qpcon.PRC_CONTEXT_TYPE IN ('PRICING_ATTRIBUTE', 'PRODUCT') AND
qpptereq.request_type_code = p_request_type_code AND
qpptereq.pte_code = qppteseg.pte_code AND
qppteseg.user_sourcing_method = 'USER ENTERED';
SELECT value_string
INTO l_context_name
FROM oe_def_condn_elems
WHERE condition_id = l_condition_id
AND attribute_code = 'PRICING_CONTEXT';
SELECT 'Y'
INTO x_out
FROM qp_price_formula_lines
WHERE pricing_attribute_context = p_attribute_context
AND pricing_attribute = p_attribute_code
AND ROWNUM < 2;
SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
INTO x_out
FROM qp_pricing_attributes
WHERE pricing_attribute_context = p_attribute_context
AND pricing_attribute = p_attribute_code
AND ROWNUM < 2;
SELECT 'Y' INTO l_all_items_exist
FROM qp_event_phases evt, qp_adv_mod_products item
WHERE INSTR(G_PRICING_EVENT, evt.pricing_event_code) > 0
AND item.pricing_phase_id = evt.pricing_phase_id
AND item.product_attribute = 'PRICING_ATTRIBUTE3'
AND item.product_attr_value = 'ALL'
AND ROWNUM = 1;
SELECT 'Y' INTO l_prod_exists
FROM qp_adv_mod_products item, qp_event_phases evt
WHERE INSTR(G_PRICING_EVENT, evt.pricing_event_code) > 0
AND item.pricing_phase_id = evt.pricing_phase_id
AND item.product_attribute =
G_Product_Attr_tbl(i).attribute_name
AND item.product_attr_value =
G_Product_Attr_tbl(i).attribute_value
AND ROWNUM = 1;
G_PRODUCT_ATTR_TBL.DELETE;
l_sql_stmt := 'SELECT ' || p_sys_variable || ' FROM DUAL';
SELECT qpseg.segment_mapping_column,
qpsour.user_sourcing_type src_type,
qpsour.user_value_string value_string,
qpcon.prc_context_code context_code,
qpcon.prc_context_type context_type
FROM
qp_segments_b qpseg,
qp_attribute_sourcing qpsour,
qp_prc_contexts_b qpcon,
qp_pte_request_types_b qpreq,
qp_pte_segments qppseg
WHERE
qpsour.segment_id = qpseg.segment_id
AND qpsour.attribute_sourcing_level = p_sourcing_level
AND qpsour.enabled_flag = 'Y'
AND qppseg.sourcing_status = 'N'
AND qppseg.used_in_setup = 'Y'
AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
AND qpsour.request_type_code = p_request_type_code
AND qpseg.prc_context_id = qpcon.prc_context_id
AND qpreq.request_type_code = qpsour.request_type_code
AND qppseg.pte_code = qpreq.pte_code
AND qppseg.segment_id = qpsour.segment_id
AND qppseg.sourcing_enabled = 'Y'
AND qpcon.prc_context_type IN ('PRICING_ATTRIBUTE', 'PRODUCT','QUALIFIER');
SELECT qpseg.segment_mapping_column,
qpsour.user_sourcing_type src_type,
qpsour.user_value_string value_string,
qpcon.prc_context_code context_code,
qpsour.attribute_sourcing_level,
qpsour.request_type_code,
qpreq.pte_code,
qpcon.prc_context_type,
qpseg.segment_code
FROM
qp_segments_b qpseg,
qp_attribute_sourcing qpsour,
qp_prc_contexts_b qpcon,
qp_pte_request_types_b qpreq,
qp_pte_segments qppseg
WHERE
qpsour.segment_id = qpseg.segment_id
AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
AND qpseg.prc_context_id = qpcon.prc_context_id
AND qpreq.request_type_code = qpsour.request_type_code
AND qppseg.pte_code = qpreq.pte_code
AND qppseg.segment_id = qpsour.segment_id
AND qppseg.sourcing_enabled = 'Y'
AND qpsour.enabled_flag='Y' -- Modified for Bug no 5559174 by rassharm
AND qpcon.prc_context_type IN ('PRICING_ATTRIBUTE', 'PRODUCT','QUALIFIER');
SELECT qpseg.segment_mapping_column,
qpsour.user_sourcing_type src_type,
qpsour.user_value_string value_string,
qpcon.prc_context_code context_code,
qpsour.attribute_sourcing_level,
qpsour.request_type_code,
qpreq.pte_code,
qpcon.prc_context_type,
qpseg.segment_code
FROM
qp_segments_b qpseg,
qp_attribute_sourcing qpsour,
qp_prc_contexts_b qpcon,
qp_pte_request_types_b qpreq,
qp_pte_segments qppseg
WHERE
qpsour.segment_id = qpseg.segment_id
AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
AND qpsour.request_type_code = p_request_type_code
AND qpseg.prc_context_id = qpcon.prc_context_id
AND qpreq.request_type_code = qpsour.request_type_code
AND qppseg.pte_code = qpreq.pte_code
AND qppseg.segment_id = qpsour.segment_id
AND qppseg.sourcing_enabled = 'Y'
AND qpsour.enabled_flag='Y' -- Modified for Bug no 5559174 by rassharm
AND qpcon.prc_context_type IN ('PRICING_ATTRIBUTE', 'PRODUCT','QUALIFIER');