[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 'DUPLICATE'
from qp_qualifiers a , qp_qualifiers b
where a.qualifier_rule_id = b.qualifier_rule_id
and a.qualifier_rule_id = p_qualifier_rule_id
and a.qualifier_grouping_no = b.qualifier_grouping_no
and a.qualifier_context = b.qualifier_context
and a. qualifier_attribute = b.qualifier_attribute
and a.qualifier_id <> b.qualifier_id;
delete from qp_list_header_phases
where list_header_id = p_List_Header_ID;
insert into qp_list_header_phases
(list_header_id, pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
(
select distinct list_header_id , pricing_phase_id,'N'
from qp_list_lines
where pricing_phase_id > 1
and qualification_ind in (2,6,8,10,12,14,22,28,30)
and list_header_id = p_List_Header_ID);
Procedure Update_Qualifier_Status(p_list_header_id in NUMBER,
p_active_flag in VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
UPDATE qp_qualifiers
SET active_flag = p_active_flag
WHERE list_header_id = p_list_header_id;
update qp_pte_segments set used_in_setup='Y'
where nvl(used_in_setup,'N')='N'
and segment_id in
(select a.segment_id
from qp_segments_b a, qp_prc_contexts_b b, qp_qualifiers c
where c.list_header_id = p_list_header_id
and a.segment_mapping_column = c.qualifier_attribute
and a.prc_context_id = b.prc_context_id
and b.prc_context_type = 'QUALIFIER'
and b.prc_context_code = c.qualifier_context);
update qp_pte_segments set used_in_setup='Y'
where nvl(used_in_setup,'N')='N'
and segment_id in
(select a.segment_id
from qp_segments_b a, qp_prc_contexts_b b, qp_pricing_attributes c
where c.list_header_id = p_list_header_id
and a.segment_mapping_column = c.pricing_attribute
and a.prc_context_id = b.prc_context_id
and b.prc_context_type = 'PRICING_ATTRIBUTE'
and b.prc_context_code = c.pricing_attribute_context);
update qp_pte_segments set used_in_setup='Y'
where nvl(used_in_setup,'N')='N'
and segment_id in
(select a.segment_id
from qp_segments_b a, qp_prc_contexts_b b, qp_pricing_attributes c
where c.list_header_id = p_list_header_id
and a.segment_mapping_column = c.product_attribute
and a.prc_context_id = b.prc_context_id
and b.prc_context_type = 'PRODUCT'
and b.prc_context_code = c.product_attribute_context);
END Update_Qualifier_Status;
Procedure Update_Attribute_Status(p_list_header_id in NUMBER,
p_list_line_id in NUMBER,
p_context_type in VARCHAR2,
p_context_code in VARCHAR2,
p_segment_mapping_column VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_check_active_flag VARCHAR2(1);
SELECT ACTIVE_FLAG
INTO l_active_flag
FROM QP_LIST_HEADERS_B
WHERE list_header_id=p_list_header_id;
SELECT ListHeaders.ACTIVE_FLAG
INTO l_active_flag
FROM QP_LIST_HEADERS_B ListHeaders, QP_LIST_LINES ListLines
WHERE ListHeaders.LIST_HEADER_ID = ListLines.LIST_HEADER_ID AND
ListLines.LIST_LINE_ID = p_list_line_id AND
rownum = 1;
UPDATE qp_pte_segments set used_in_setup='Y'
WHERE nvl(used_in_setup,'N')='N'
AND segment_id IN
(SELECT a.segment_id FROM qp_segments_b a, qp_prc_contexts_b b
WHERE a.segment_mapping_column=p_segment_mapping_column
AND a.prc_context_id=b.prc_context_id
AND b.prc_context_type = p_context_type
AND b.prc_context_code=p_context_code);
END Update_Attribute_Status;
SELECT count(1)
FROM QP_LIST_LINES qll,
QP_RLTD_MODIFIERS qrm
WHERE qll.list_line_id = p_list_line_id
AND qll.list_line_id= qrm.from_rltd_modifier_id
--AND qrm.rltd_modifier_grp_type = 'PRICE BREAK'
AND qrm.rltd_modifier_grp_type = l_modifier_grp_type
AND qll.list_line_type_code = p_list_line_type_code;
select 'FOUND' from
QP_LIST_LINES
WHERE LIST_LINE_ID = P_LIST_LINE_ID;
SELECT PRICING_ATTR_VALUE_FROM_NUMBER,
PRICING_ATTR_VALUE_TO_NUMBER
FROM QP_RLTD_MODIFIERS QRMA, QP_PRICING_ATTRIBUTES QPBVA
WHERE QRMA.FROM_RLTD_MODIFIER_ID = p_list_line_id AND
QRMA.TO_RLTD_MODIFIER_ID = QPBVA.LIST_LINE_ID
AND QRMA.RLTD_MODIFIER_GRP_TYPE = 'PRICE BREAK'
AND QPBVA.PRICING_ATTRIBUTE_DATATYPE = 'N'
ORDER BY QPBVA.PRICING_ATTR_VALUE_FROM_NUMBER;
oe_debug_pub.add('Before overlapping breaks select stmt');
oe_debug_pub.add('After overlapping breaks select stmt');
SELECT qpa.PRICING_ATTR_VALUE_FROM,
qpa.PRICING_ATTR_VALUE_TO
from qp_list_lines ql, qp_pricing_attributes qpa,qp_rltd_modifiers qrm
WHERE ql.list_line_id = qpa.list_line_id
and ql.list_line_type_code IN ('SUR', 'DIS', 'PLL')
and qrm.to_rltd_modifier_id = ql.list_line_id
and qrm.rltd_modifier_grp_type = 'PRICE BREAK'
and qrm.from_rltd_modifier_id = p_list_line_id
order by qp_number.canonical_to_number(qpa.PRICING_ATTR_VALUE_FROM);
SELECT qpa.PRICING_ATTR_VALUE_FROM_NUMBER,
qpa.PRICING_ATTR_VALUE_FROM,
qpa.PRICING_ATTR_VALUE_TO_NUMBER,
qpa.PRICING_ATTR_VALUE_TO
from qp_list_lines ql,qp_pricing_attributes qpa,qp_rltd_modifiers qrm
WHERE ql.list_line_id = qpa.list_line_id
and ql.list_line_type_code IN ('SUR', 'DIS', 'PLL')
and qrm.to_rltd_modifier_id = ql.list_line_id
and qrm.rltd_modifier_grp_type = 'PRICE BREAK'
and qrm.from_rltd_modifier_id = p_list_line_id
order by 1
FOR UPDATE OF PRICING_ATTR_VALUE_FROM,PRICING_ATTR_VALUE_FROM_NUMBER;
UPDATE qp_pricing_attributes SET
PRICING_ATTR_VALUE_FROM = l_prc_attr_val_from,
PRICING_ATTR_VALUE_FROM_NUMBER = l_prc_attr_val_from_number
WHERE CURRENT OF c_break_lines_attr_values;
select count(DISTINCT pricing_attribute)
into l_count
from qp_price_breaks_v
where parent_list_line_id = p_parent_list_line_id;
SELECT a.qualifier_grouping_no
FROM qp_qualifiers a
WHERE a.qualifier_rule_id = a_qualifier_rule_id
AND EXISTS (SELECT 'x'
FROM qp_qualifiers b, qp_prc_contexts_b d,
qp_segments_b e, qp_pte_segments f
WHERE b.qualifier_context = d.prc_context_code
AND b.qualifier_attribute = e.segment_mapping_column
AND d.prc_context_id = e.prc_context_id
AND e.segment_id = f.segment_id
AND f.pte_code = a_pte_code
AND f.segment_level = 'LINE'
AND b.qualifier_rule_id = a_qualifier_rule_id
AND (b.qualifier_grouping_no = a.qualifier_grouping_no or
b.qualifier_grouping_no = -1))
AND EXISTS (SELECT 'x'
FROM qp_qualifiers c, qp_prc_contexts_b d1,
qp_segments_b e1, qp_pte_segments f1
WHERE c.qualifier_context = d1.prc_context_code
AND c.qualifier_attribute = e1.segment_mapping_column
AND d1.prc_context_id = e1.prc_context_id
AND e1.segment_id = f1.segment_id
AND f1.pte_code = a_pte_code
AND f1.segment_level = 'ORDER'
AND c.qualifier_rule_id = a_qualifier_rule_id
AND (c.qualifier_grouping_no = a.qualifier_grouping_no or
c.qualifier_grouping_no = -1))
GROUP BY a.qualifier_grouping_no;
SELECT count(1)
FROM QP_LIST_HEADERS qplh ,
QP_QUALIFIERS qpq
WHERE qplh.list_header_id = p_list_header_id
AND qplh.list_header_id = qpq.list_header_id
AND qpq.qualifier_context = 'MODLIST'
AND qpq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4';
QP_Maintain_Denormalized_Data.Update_Qualifiers(err_buff => l_err_buf,
retcode => l_ret_code,
p_list_header_id => p_list_header_id,
p_update_type => 'DELAYED_REQ');
, 'Unexpected error occured in the procedure : QP_Maintain_Denormalized_Data.Update_Qualifiers');
, 'Update_Qualification_Ind');
SELECT list_header_id
INTO l_list_header_id
FROM qp_list_lines
WHERE list_line_id = p_list_line_id;
QP_Denormalized_Pricing_Attrs.Update_Pricing_Attributes(
p_list_header_id_low => l_list_header_id,
p_list_header_id_high => l_list_header_id,
p_update_type => 'FACTOR_DELAYED_REQ');
Procedure Update_List_Qualification_Ind
( x_return_status OUT NOCOPY Varchar2
, p_list_header_id IN NUMBER
) IS
CURSOR list_lines_cur (a_list_header_id NUMBER)
IS
SELECT list_line_id, qualification_ind
FROM qp_list_lines
WHERE list_header_id = a_list_header_id;
select list_type_code
into l_list_type_code
from qp_list_headers_vl
where list_header_id = p_list_header_id;
update qp_list_lines qpl
set qpl.qualification_ind = 0
where qpl.list_header_id=p_list_header_id;
update qp_list_lines qpl
set qpl.qualification_ind=nvl(qualification_ind,0) + 1
where qpl.list_header_id=p_list_header_id
and exists (
select 'X'
from qp_rltd_modifiers qprltd
where qprltd.to_rltd_modifier_id=qpl.list_line_id
and rltd_modifier_grp_type<>'COUPON');
select 1 into l_dummy from dual where
exists ( Select 'Y'
from qp_qualifiers
where list_header_id = p_list_header_id
and NOT (qualifier_context = 'MODLIST' AND
qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'));
update qp_list_lines qpl
set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 2
where qpl.list_header_id=p_list_header_id;
update qp_list_lines qpl
set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 2
where qpl.list_header_id=p_list_header_id
and exists (
select 'X'
from qp_qualifiers q
where q.list_header_id=qpl.list_header_id
and q.list_line_id = -1);
update qp_list_lines qpl
set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 8
where qpl.list_header_id=p_list_header_id
and exists (
select 'X'
from qp_qualifiers q
where q.list_header_id=qpl.list_header_id
and q.list_line_id=qpl.list_line_id);
update qp_list_lines qpl
set qpl.qualification_ind= nvl(qpl.qualification_ind, 0) + 4
where qpl.list_header_id=p_list_header_id
and exists (
select /*+ no_unnest */ 'X' --5612361
from qp_pricing_attributes qpprod
where qpprod.list_line_id = qpl.list_line_id
and qpprod.excluder_flag = 'N');
update qp_list_lines qpl
set qpl.qualification_ind= nvl(qpl.qualification_ind, 0) + 16
where qpl.list_header_id=p_list_header_id
and exists (
select 'X'
from qp_pricing_attributes qpprod
where qpprod.list_line_id = qpl.list_line_id
and qpprod.list_header_id = p_list_header_id --bug#4261111
and qpprod.pricing_attribute_context is not null
and qpprod.pricing_attribute is not null
-- changes made per rchellam's request --spgopal
and qpprod.pricing_attr_value_from is not null);
update qp_pricing_attributes
set qualification_ind = list_lines_rec.qualification_ind
where list_line_id = list_lines_rec.list_line_id;
UPDATE QP_PRICING_ATTRIBUTES A
SET QUALIFICATION_IND = (SELECT QUALIFICATION_IND
FROM QP_LIST_LINES
where A.LIST_LINE_ID = LIST_LINE_ID
and LIST_HEADER_ID = p_list_header_id)
WHERE LIST_LINE_ID in
(SELECT /*+ cardinality(QP_LIST_LINES 1) */ LIST_LINE_ID
FROM QP_LIST_LINES WHERE LIST_HEADER_ID = p_list_header_id);
, 'Update_Qualification_Ind');
END Update_List_Qualification_Ind;
Procedure Update_Limits_Columns
( p_Limit_Id IN NUMBER
,x_return_status OUT NOCOPY Varchar2
)
IS
l_Organization_Count NUMBER := 0;
SELECT COUNT(*)
INTO l_Organization_Count
FROM QP_LIMITS
WHERE (limit_id = p_Limit_Id) AND
(UPPER(ORGANIZATION_FLAG) = 'Y');
SELECT COUNT(*)
INTO l_Customer_Attr_Count
FROM QP_LIMITS
WHERE ((limit_id = p_Limit_Id) AND
((MULTIVAL_ATTR1_TYPE IS NOT NULL) OR
(MULTIVAL_ATTR1_CONTEXT IS NOT NULL) OR
(MULTIVAL_ATTRIBUTE1 IS NOT NULL) OR
(MULTIVAL_ATTR1_DATATYPE IS NOT NULL)));
SELECT COUNT(*)
INTO l_Product_Attr_Count
FROM QP_LIMITS
WHERE ((limit_id = p_Limit_Id) AND
((MULTIVAL_ATTR2_TYPE IS NOT NULL) OR
(MULTIVAL_ATTR2_CONTEXT IS NOT NULL) OR
(MULTIVAL_ATTRIBUTE2 IS NOT NULL) OR
(MULTIVAL_ATTR2_DATATYPE IS NOT NULL)));
SELECT COUNT(*)
INTO l_Limit_Attrs_Count
FROM QP_LIMIT_ATTRIBUTES
WHERE limit_id = p_Limit_Id;
SELECT COUNT(*)
INTO l_dummy
FROM QP_LIMITS
WHERE limit_id = p_Limit_Id;
UPDATE QP_LIMITS
SET EACH_ATTR_EXISTS = 'Y'
WHERE limit_id = p_Limit_Id;
UPDATE QP_LIMITS
SET EACH_ATTR_EXISTS = 'N'
WHERE limit_id = p_Limit_Id;
UPDATE QP_LIMITS
SET NON_EACH_ATTR_COUNT = l_Limit_Attrs_Count
WHERE limit_id = p_Limit_Id;
UPDATE QP_LIMITS
SET TOTAL_ATTR_COUNT = l_Total_Attr_Count
WHERE limit_id = p_Limit_Id;
QP_Maintain_Denormalized_Data.Update_Pricing_Phases
(p_update_type => 'DELAYED_REQ'
--,p_pricing_phase_id => p_pricing_phase_id
-- commenting out as suggested by SPGOPAL
,x_return_status => x_return_status
,x_return_status_text => l_return_status_text);
oe_debug_pub.add('error update_pricing_phase begin'||l_return_status_text);
oe_debug_pub.add('end update_pricing_phase begin');
, 'Update_Pricing_Phase');
, 'Update_Pricing_Phase');
END Update_Limits_Columns;
Procedure Update_Line_Qualification_Ind
( x_return_status OUT NOCOPY Varchar2
, p_list_line_id IN NUMBER
) IS
l_qualification_ind NUMBER;
select list_type_code, list_header_id
into l_list_type_code, l_list_header_id
from qp_list_headers_vl
where list_header_id = (select list_header_id
from qp_list_lines
where list_line_id = p_list_line_id);
update qp_list_lines qpl
set qpl.qualification_ind = 0
where qpl.list_line_id=p_list_line_id;
update qp_list_lines qpl
set qpl.qualification_ind=nvl(qualification_ind,0) + 1
where qpl.list_line_id=p_list_line_id
and exists (
select 'X'
from qp_rltd_modifiers qprltd
where qprltd.to_rltd_modifier_id=p_list_line_id
and rltd_modifier_grp_type<>'COUPON')
returning qpl.qualification_ind into l_qualification_ind;
select 1 into l_dummy from dual where
exists ( Select 'Y'
from qp_qualifiers
where list_header_id = l_list_header_id
and NOT (qualifier_context = 'MODLIST' AND
qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'));
update qp_list_lines qpl
set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 2
where qpl.list_line_id=p_list_line_id
returning qpl.qualification_ind into l_qualification_ind;
update qp_list_lines qpl
set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 2
where qpl.list_line_id=p_list_line_id
and exists (
select 'X'
from qp_qualifiers q
where q.list_header_id=qpl.list_header_id
and q.list_line_id = -1)
returning qpl.qualification_ind into l_qualification_ind;
update qp_list_lines qpl
set qpl.qualification_ind=nvl(qpl.qualification_ind,0) + 8
where qpl.list_line_id=p_list_line_id
and exists (
select 'X'
from qp_qualifiers q
where q.list_header_id=qpl.list_header_id
and q.list_line_id=p_list_line_id)
returning qpl.qualification_ind into l_qualification_ind;
update qp_list_lines qpl
set qpl.qualification_ind= nvl(qpl.qualification_ind, 0) + 4
where qpl.list_line_id=p_list_line_id
and exists (
select 'X'
from qp_pricing_attributes qpprod
where qpprod.list_line_id = p_list_line_id
and qpprod.excluder_flag = 'N')
returning qpl.qualification_ind into l_qualification_ind;
update qp_list_lines qpl
set qpl.qualification_ind= nvl(qpl.qualification_ind, 0) + 16
where qpl.list_line_id=p_list_line_id
and exists (
select 'X'
from qp_pricing_attributes qpprod
where qpprod.list_line_id = p_list_line_id
and qpprod.pricing_attribute_context is not null
and qpprod.pricing_attribute is not null
-- changes made per rchellam's request --spgopal
and qpprod.pricing_attr_value_from is not null)
returning qpl.qualification_ind into l_qualification_ind;
update qp_pricing_attributes pra
set pra.qualification_ind = l_qualification_ind
where pra.list_line_id = p_list_line_id;
, 'Update_Qualification_Ind');
END Update_Line_Qualification_Ind;
Procedure Update_Child_Break_Lines
( x_return_status OUT NOCOPY Varchar2
, p_list_line_id IN NUMBER
) IS
l_price_break_type varchar2(30);
SELECT
a.modifier_level_code
,a.automatic_flag
,a.override_flag
,a.Print_on_invoice_flag
,a.price_break_type_code
,a.arithmetic_operator -- 4936019
,a.Proration_type_code
,a.Incompatibility_Grp_code
,a.Pricing_phase_id
,a.Pricing_group_sequence
,a.accrual_flag
,a.estim_accrual_rate
,a.expiration_date
,a.expiration_period_start_date
,a.expiration_period_uom
,a.number_expiration_periods
,a.rebate_transaction_type_code
FROM qp_list_lines a
WHERE a.list_line_id = p_list_line_id;
select price_break_type_code,pricing_phase_id, arithmetic_operator -- 4936019
into l_price_break_type,l_pricing_phase_id, l_arithmetic_operator -- 4936019
from qp_list_lines
where list_line_id = p_list_line_id;
update qp_list_lines qpl
set qpl.price_break_type_code = l_price_break_type
where qpl.list_line_id in ( select to_rltd_modifier_id
from qp_rltd_modifiers
where from_rltd_modifier_id = p_list_line_id );
update qp_list_lines qpl
set qpl.arithmetic_operator = 'UNIT_PRICE'
where qpl.list_line_id in ( select to_rltd_modifier_id
from qp_rltd_modifiers
where from_rltd_modifier_id = p_list_line_id )
and qpl.arithmetic_operator <> 'UNIT_PRICE';
update qp_list_lines qpl
set qpl.arithmetic_operator = 'BLOCK_PRICE'
where qpl.list_line_id in ( select to_rltd_modifier_id
from qp_rltd_modifiers
where from_rltd_modifier_id = p_list_line_id )
and qpl.arithmetic_operator <> 'BLOCK_PRICE';
update qp_list_lines qpl
set qpl.arithmetic_operator = 'BLOCK_PRICE'
where qpl.list_line_id in ( select to_rltd_modifier_id
from qp_rltd_modifiers
where from_rltd_modifier_id = p_list_line_id )
and qpl.arithmetic_operator not in ('BLOCK_PRICE', 'BREAKUNIT_PRICE');
UPDATE qp_list_lines
SET modifier_level_code = i.modifier_level_code
,automatic_flag = i.automatic_flag
,override_flag = i.override_flag
,Print_on_invoice_flag = i.Print_on_invoice_flag
,price_break_type_code = i.price_break_type_code
,Proration_type_code = i.Proration_type_code
,Incompatibility_Grp_code= i.Incompatibility_Grp_code
,Pricing_phase_id = i.Pricing_phase_id
,Pricing_group_sequence = i.Pricing_group_sequence
,accrual_flag = i.accrual_flag
,rebate_transaction_type_code = i.rebate_transaction_type_code
,estim_accrual_rate = i.estim_accrual_rate
,expiration_date = i.expiration_date
,expiration_period_start_date = i.expiration_period_start_date
,expiration_period_uom = i.expiration_period_uom
,number_expiration_periods = i.number_expiration_periods
WHERE list_line_id in (select to_rltd_modifier_id
from qp_rltd_modifiers
where from_rltd_modifier_id = p_list_line_id);
, 'Update_Child_Break_Lines');
END Update_Child_Break_Lines;
PROCEDURE UPDATE_CHILD_PRICING_ATTR
( x_return_status OUT NOCOPY Varchar2
, p_list_line_id IN NUMBER) IS
l_status NUMBER;
SELECT product_attribute_context,
product_attribute,
product_attr_value,
product_uom_code
FROM QP_PRICING_ATTRIBUTES
WHERE list_line_id = p_list_line_id;
/* select list_line_type_code
into l_list_line_type_code
from qp_list_lines where
list_line_id = p_list_line_id; */
UPDATE qp_Pricing_Attributes SET
Product_attribute_context = i.Product_attribute_context
,Product_attribute = i.Product_attribute
,Product_attr_value = i.Product_attr_value
,Product_uom_code = i.Product_Uom_Code
WHERE list_line_id IN (select to_rltd_modifier_id
from qp_rltd_modifiers qrm
where from_rltd_modifier_id = p_list_line_id);
, 'Update_Child_Pricing_Attr'
);
END UPDATE_CHILD_PRICING_ATTR;
Procedure Update_Pricing_Attr_Phase
( x_return_status OUT NOCOPY Varchar2
, p_List_Line_ID IN NUMBER
) IS
l_Pricing_Phase_id QP_PRICING_PHASES.PRICING_PHASE_ID%TYPE
:= FND_API.G_MISS_NUM;
SELECT *
FROM QP_LIST_LINES WHERE list_line_id = p_list_line_id;
SELECT LIST_HEADER_ID, PRICING_PHASE_ID INTO
l_List_Header_ID, l_Pricing_Phase_ID FROM QP_LIST_LINES
WHERE LIST_LINE_ID = p_List_Line_ID;
for C_Pricing_Attr in (select pricing_attribute_id
from qp_pricing_attributes
where list_line_id = p_list_line_id) LOOP
Update QP_PRICING_ATTRIBUTES Set
LIST_HEADER_ID = l_List_Header_ID,
PRICING_PHASE_ID = l_Pricing_Phase_ID
where PRICING_ATTRIBUTE_ID = C_Pricing_Attr.Pricing_Attribute_ID;
, 'Update_Pricing_Attr_Phase');
END Update_Pricing_Attr_Phase;
Procedure Update_Pricing_Phase
( x_return_status OUT NOCOPY Varchar2
, p_pricing_phase_id IN NUMBER
, p_automatic_flag IN Varchar2 --fix for bug 3756625
, p_count IN NUMBER
, p_call_from IN NUMBER
) IS
l_line_type VARCHAR2(30) := 'NONE';
oe_debug_pub.add('update_pricing_phase begin'||p_pricing_phase_id);--||' '||p_parent_line_id||' '||p_modifier_level_code);
select decode(LL.list_line_type_code, 'PRG','Y','N')
into l_rltd from qp_list_lines LL
where LL.list_line_id = p_parent_line_id;
update qp_pricing_phases PH set
PH.oid_exists = decode(l_line,'N',PH.oid_exists,'Y',l_line)
, PH.rltd_exists = decode(l_rltd,'N',PH.rltd_exists,'Y',l_rltd)
, PH.line_group_exists =
decode(l_level,'N',PH.line_group_exists,'Y',l_level)
where pricing_phase_id = p_pricing_phase_id;
QP_Maintain_Denormalized_Data.Update_Pricing_Phases
(p_update_type => 'DELAYED_REQ'
,p_pricing_phase_id => p_pricing_phase_id
,p_automatic_flag => p_automatic_flag --fix for bug 3756625
,p_count => p_count
,p_call_from => p_call_from
,x_return_status => x_return_status
,x_return_status_text => l_return_status_text);
oe_debug_pub.add('error update_pricing_phase begin'||l_return_status_text);
oe_debug_pub.add('end update_pricing_phase begin');
, 'Update_Pricing_Phase');
, 'Update_Pricing_Phase');
END Update_Pricing_Phase;
Procedure Update_manual_modifier_flag
( x_return_status OUT NOCOPY Varchar2
, p_automatic_flag IN Varchar2
, p_pricing_phase_id IN NUMBER
) IS
l_manual_modifier_flag VARCHAR2(1);
oe_debug_pub.add('Update manual modifier flag Begin ');
select manual_modifier_flag into l_manual_modifier_flag
from qp_pricing_phases
where pricing_phase_id = p_pricing_phase_id;
update qp_pricing_phases
set manual_modifier_flag = l_set_manual_flag
where pricing_phase_id = p_pricing_phase_id;
oe_debug_pub.add('Update manual modifier flag End');
, 'Update_Pricing_Phase');
END Update_manual_modifier_flag;
select distinct nvl(selling_rounding_factor, 9999999) selling_rounding_factor
from qp_currency_details
where currency_header_id = p_currency_header_id
and to_currency_code = p_to_currency_code;
select qualifier_context, qualifier_attribute
from qp_qualifiers
where list_header_id = p_list_header_id
and ((qualifier_grouping_no = p_qualifier_grouping_no) OR (qualifier_grouping_no = -1))
and list_line_id = p_list_line_id;
select distinct modifier_level_code modifier_level
from qp_list_lines
where list_header_id = p_list_header_id;
select distinct qualifier_grouping_no qualifier_grouping_no
from qp_qualifiers
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and qualifier_grouping_no <> -1;
procedure update_changed_lines_add (
p_list_line_id in number,
p_list_header_id in number,
p_pricing_phase_id in number,
x_return_status out NOCOPY varchar2
) is
begin
oe_debug_pub.add('process update_changed_lines_add');
insert into qp_adv_mod_products (
product_attribute,
product_attr_value,
pricing_phase_id) (
select distinct qpa.product_attribute,
qpa.product_attr_value,
p_pricing_phase_id
from qp_pricing_attributes qpa
where qpa.list_line_id = p_list_line_id
and not exists (
select 'Y' from qp_adv_mod_products
where pricing_phase_id = p_pricing_phase_id
and product_attribute = qpa.product_attribute
and product_attr_value = qpa.product_attr_value));
end update_changed_lines_add;
procedure update_changed_lines_del (
p_list_line_id in number,
p_list_header_id in number,
p_pricing_phase_id in number,
p_product_attribute in varchar2,
p_product_attr_value in varchar2,
x_return_status out NOCOPY varchar2
) is
begin
oe_debug_pub.add('process update_changed_lines_del');
delete from qp_adv_mod_products
where pricing_phase_id = p_pricing_phase_id
and product_attribute = p_product_attribute
and product_attr_value = p_product_attr_value
and not exists (
select 'Y'
from qp_list_lines qpl,
qp_list_headers_b qph
where qpl.list_line_id = p_list_line_id
and qpl.modifier_level_code = 'LINEGROUP'
and qph.list_header_id = p_list_header_id
and qph.active_flag = 'Y'
and rownum = 1
union
select 'Y'
from qp_rltd_modifiers qpr,
qp_list_lines qpl,
qp_list_headers_b qph
where qpl.list_line_id = p_list_line_id
and qpr.to_rltd_modifier_id = p_list_line_id
and qpr.rltd_modifier_grp_type = 'BENEFIT'
and qpl.list_line_type_code = 'DIS'
and qph.list_header_id = p_list_header_id
and qph.active_flag = 'Y'
and qpr.to_rltd_modifier_id = qpl.list_line_id
and qph.list_header_id = qpl.list_header_id
and rownum = 1
union
select 'Y'
from qp_list_lines qpl,
qp_list_headers_b qph
where qpl.list_line_id = p_list_line_id
and qpl.list_line_type_code in ('OID', 'PRG', 'RLTD')
and qph.list_header_id = p_list_header_id
and qph.active_flag = 'Y'
and rownum = 1);
end update_changed_lines_del;
procedure update_changed_lines_ph (
p_list_line_id in number,
p_list_header_id in number,
p_pricing_phase_id in number,
p_old_pricing_phase_id in number,
x_return_status out NOCOPY varchar2
) is
l_product_attribute varchar2(30);
oe_debug_pub.add('process update_changed_lines_ph');
insert into qp_adv_mod_products (
product_attribute,
product_attr_value,
pricing_phase_id) (
select distinct qpa.product_attribute,
qpa.product_attr_value,
p_pricing_phase_id
from qp_pricing_attributes qpa
where qpa.list_line_id = p_list_line_id
and not exists (
select 'Y' from qp_adv_mod_products
where pricing_phase_id = p_pricing_phase_id
and product_attribute = qpa.product_attribute
and product_attr_value = qpa.product_attr_value));
select distinct product_attribute,
product_attr_value,
p_pricing_phase_id
into l_product_attribute,
l_product_attr_value,
l_pricing_phase_id
from qp_pricing_attributes
where list_line_id = p_list_line_id;
delete from qp_adv_mod_products
where pricing_phase_id = p_old_pricing_phase_id
and product_attribute = l_product_attribute
and product_attr_value = l_product_attr_value
and not exists (
select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
from qp_pricing_attributes qpa,
qp_list_lines qpl,
qp_list_headers_b qph
where qpa.pricing_phase_id = p_old_pricing_phase_id
and qpa.product_attribute = l_product_attribute
and qpa.product_attr_value = l_product_attr_value
and qpl.list_line_id = qpa.list_line_id
and qpl.modifier_level_code = 'LINEGROUP'
and qph.list_header_id = qpa.list_header_id
and qph.active_flag = 'Y'
and rownum = 1
union
select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
from qp_rltd_modifiers qpr,
qp_list_lines qpl,
qp_pricing_attributes qpa,
qp_list_headers_b qph
where qpa.pricing_phase_id = p_old_pricing_phase_id
and qpa.product_attribute = l_product_attribute
and qpa.product_attr_value = l_product_attr_value
and qpl.list_line_id = qpa.list_line_id
and qpr.rltd_modifier_grp_type = 'BENEFIT'
and qpr.to_rltd_modifier_id = qpl.list_line_id
and qpl.list_line_type_code = 'DIS'
and qph.list_header_id = qpa.list_header_id
and qph.active_flag = 'Y'
and rownum = 1
union
select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
from qp_list_lines qpl,
qp_pricing_attributes qpa,
qp_list_headers_b qph
where qpa.pricing_phase_id = p_old_pricing_phase_id
and qpa.product_attribute = l_product_attribute
and qpa.product_attr_value = l_product_attr_value
and qpl.list_line_type_code in ('OID', 'PRG', 'RLTD')
and qpl.list_line_id = qpa.list_line_id
and qph.list_header_id = qpl.list_header_id--p_list_header_id
and qph.active_flag = 'Y'
and rownum = 1);
end update_changed_lines_ph;
procedure update_changed_lines_act (
p_list_header_id in number,
p_active_flag varchar2,
x_return_status out NOCOPY varchar2
) is
l_product_attribute varchar2(30);
select distinct qpl.list_line_id,
qpl.pricing_phase_id,
qpa.product_attribute,
qpa.product_attr_value
from qp_list_lines qpl,
qp_pricing_attributes qpa
where qpl.list_header_id = p_list_header_id
and qpa.list_line_id = qpl.list_line_id;
oe_debug_pub.add('process update_changed_lines_act');
insert into qp_adv_mod_products (
pricing_phase_id,
product_attribute,
product_attr_value
) (
select line_cursor.pricing_phase_id,
line_cursor.product_attribute,
line_cursor.product_attr_value
from dual
where not exists (
select 'Y' from qp_adv_mod_products
where pricing_phase_id = line_cursor.pricing_phase_id
and product_attribute = line_cursor.product_attribute
and product_attr_value = line_cursor.product_attr_value));
delete from qp_adv_mod_products
where pricing_phase_id = line_cursor.pricing_phase_id
and product_attribute = line_cursor.product_attribute
and product_attr_value = line_cursor.product_attr_value
and not exists (
select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
from qp_pricing_attributes qpa,
qp_list_lines qpl,
qp_list_headers_b qph
where qpa.pricing_phase_id = line_cursor.pricing_phase_id
and qpa.product_attribute = line_cursor.product_attribute
and qpa.product_attr_value = line_cursor.product_attr_value
and qpl.list_line_id = qpa.list_line_id
and qpl.modifier_level_code = 'LINEGROUP'
and qph.list_header_id = qpa.list_header_id
and qph.active_flag = 'Y'
and rownum = 1
union
select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
from qp_rltd_modifiers qpr,
qp_list_lines qpl,
qp_pricing_attributes qpa,
qp_list_headers_b qph
where qpa.pricing_phase_id = line_cursor.pricing_phase_id
and qpa.product_attribute = line_cursor.product_attribute
and qpa.product_attr_value = line_cursor.product_attr_value
and qpl.list_line_id = qpa.list_line_id
and qpr.rltd_modifier_grp_type = 'BENEFIT'
and qpr.to_rltd_modifier_id = qpl.list_line_id
and qpl.list_line_type_code = 'DIS'
and qph.list_header_id = qpa.list_header_id
and qph.active_flag = 'Y'
and rownum = 1
union
select /*+ index(qpa QP_PRICING_ATTRIBUTES_N12) */ 'Y' --bug#11813168
from qp_list_lines qpl,
qp_pricing_attributes qpa,
qp_list_headers_b qph
where qpa.pricing_phase_id = line_cursor.pricing_phase_id
and qpa.product_attribute = line_cursor.product_attribute
and qpa.product_attr_value = line_cursor.product_attr_value
and qpl.list_line_type_code in ('OID', 'PRG', 'RLTD')
and qpl.list_line_id = qpa.list_line_id
and qph.list_header_id = qpl.list_header_id
and qph.active_flag = 'Y'
and rownum = 1);
end update_changed_lines_act;
SELECT 'x'
INTO l_exists
FROM qp_sourcesystem_fnarea_map
WHERE pte_source_system_id = p_pte_source_system_id
AND enabled_flag = 'Y'
AND rownum = 1;
select pte_code, application_short_name
into l_pte_code, l_ss_code
from qp_pte_source_systems
where pte_source_system_id = p_pte_source_system_id;