[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE_TYPE VARCHAR2(50);
SELECT 'N' FROM dual WHERE
EXISTS(
SELECT 'Y'
FROM qp_list_headers_b qh,
qp_list_lines ql
WHERE qh.list_type_code = 'PRO'
and qh.active_flag = 'Y'
and ql.list_header_id = qh.list_header_id
and ql.list_line_type_code in ('PRG','IUE','TSN','CIE')
and rownum = 1
);
SELECT 'N'
FROM qp_list_headers_b qh
WHERE qh.active_flag = 'Y'
and qh.list_type_code in ('PRO','DLT','SLT','DEL','CHARGES')
and exists (select 'Y'
from qp_limits qlim
where qlim.list_header_id = qh.list_header_id)
and rownum = 1;
procedure update_adv_mod_products(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
BEGIN
--this procedure is called to populate the product dependencies for
--line group based and OID/PRG based discounts to identify which lines
--need to be passed to the pricing engine
--this same operation is done in the delayed request API for delayed requests
--done from the forms. In case any bug fixes are done to this, the same
--needs to be propagated to QPXUREQB.pls procedures
--update_changed_lines_add/del/act/ph
if G_UPDATE_TYPE <> 'BATCH_ADV_MOD_PRODUCTS' then
--added for bug 5237249
--delete should not happen for parallel threads from qpxsourc.sql
--otherwise it will delete the previous worker's rows
--moved the delete to qpxsourc.sql
delete from qp_adv_mod_products;
insert into qp_adv_mod_products
(pricing_phase_id, product_attribute, product_attr_value)
(select /*+ ORDERED USE_NL(qlh) */ ql.pricing_phase_id, 'PRICING_ATTRIBUTE3', 'ALL_ITEMS'
from qp_list_lines ql, qp_list_headers_b qlh
where ql.qualification_ind = 0
and ql.pricing_phase_id <> 1
and ql.modifier_level_code = 'LINEGROUP'
--added for bug 5237249
and ((G_LIST_HEADER_ID IS NOT null
and G_LIST_HEADER_ID_HIGH IS NOT null
and qlh.list_header_id between G_LIST_HEADER_ID and G_LIST_HEADER_ID_HIGH)
or (G_LIST_HEADER_ID IS NULL)
or (G_LIST_HEADER_ID_HIGH IS NULL))
and not exists (select 'Y' from qp_pricing_attributes qpa
where qpa.list_line_id = ql.list_line_id)
and qlh.list_header_id = ql.list_header_id
and qlh.active_flag = 'Y'
and rownum =1);
insert into qp_adv_mod_products
(pricing_phase_id, product_attribute, product_attr_value)
(select
distinct ql.pricing_phase_id, qpa.product_attribute, qpa.product_attr_value
from qp_rltd_modifiers rltd, qp_list_lines ql, qp_list_headers_b qlh
,qp_pricing_attributes qpa
where rltd.rltd_modifier_grp_type = 'BENEFIT'
and ql.list_line_id = rltd.to_rltd_modifier_id
--and ql.list_line_type_code = 'DIS'
and qlh.list_header_id = ql.list_header_id
--added for bug 5237249
and ((G_LIST_HEADER_ID IS NOT null
and G_LIST_HEADER_ID_HIGH IS NOT null
and qlh.list_header_id between G_LIST_HEADER_ID and G_LIST_HEADER_ID_HIGH)
or (G_LIST_HEADER_ID IS NULL)
or (G_LIST_HEADER_ID_HIGH IS NULL))
and qlh.active_flag = 'Y'
--and qlh.list_type_code in ( 'DEL', 'PRO')
and qpa.list_line_id = ql.list_line_id
and not exists (select 'Y' from qp_adv_mod_products item
where item.pricing_phase_id = qpa.pricing_phase_id
and item.product_attribute = qpa.product_attribute
and item.product_attr_value = qpa.product_attr_value)
UNION
select
distinct ql.pricing_phase_id, qpa.product_attribute, qpa.product_attr_value
from qp_list_lines ql
, qp_list_headers_b qlh
, qp_pricing_attributes qpa
where ql.pricing_phase_id > 1
and ql.qualification_ind > 0
and ql.list_line_type_code in ('OID', 'PRG', 'RLTD')
and qpa.list_line_id = ql.list_line_id
and qlh.list_header_id = ql.list_header_id
and qlh.active_flag = 'Y'
--added for bug 5237249
and ((G_LIST_HEADER_ID IS NOT null
and G_LIST_HEADER_ID_HIGH IS NOT null
and qlh.list_header_id between G_LIST_HEADER_ID and G_LIST_HEADER_ID_HIGH)
or (G_LIST_HEADER_ID IS NULL)
or (G_LIST_HEADER_ID_HIGH IS NULL))
and qlh.list_type_code in ('DLT', 'SLT', 'DEL', 'PRO', 'CHARGES')
and not exists (select 'Y' from qp_adv_mod_products item
where item.pricing_phase_id = qpa.pricing_phase_id
and item.product_attribute = qpa.product_attribute
and item.product_attr_value = qpa.product_attr_value)
UNION
select
distinct ql.pricing_phase_id, qpa.product_attribute, qpa.product_attr_value
from qp_list_lines ql
, qp_list_headers_b qlh
, qp_pricing_attributes qpa
where ql.modifier_level_code = 'LINEGROUP'
and ql.pricing_phase_id > 1
and qpa.list_line_id = ql.list_line_id
and qlh.list_header_id = ql.list_header_id
and qlh.active_flag = 'Y'
--added for bug 5237249
and ((G_LIST_HEADER_ID IS NOT null
and G_LIST_HEADER_ID_HIGH IS NOT null
and qlh.list_header_id between G_LIST_HEADER_ID and G_LIST_HEADER_ID_HIGH)
or (G_LIST_HEADER_ID IS NULL)
or (G_LIST_HEADER_ID_HIGH IS NULL))
and qlh.list_type_code in ('DLT', 'SLT', 'DEL', 'PRO', 'CHARGES')
and not exists (select 'Y' from qp_adv_mod_products item
where item.pricing_phase_id = qpa.pricing_phase_id
and item.product_attribute = qpa.product_attribute
and item.product_attr_value = qpa.product_attr_value));
x_return_status_text := 'update_adv_mod_products exception '||SQLERRM;
END update_adv_mod_products;
procedure update_pricing_phases(p_update_type IN VARCHAR2
, p_pricing_phase_id IN NUMBER
, p_automatic_flag IN VARCHAR2
, p_count NUMBER
, p_call_from NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_return_status_text OUT NOCOPY VARCHAR2) IS
/* Changed the cursor below for the bug#2572053 */
CURSOR l_basic_modifiers_cur IS
SELECT 'Y' FROM DUAL WHERE
EXISTS(
SELECT 'Y'
FROM qp_list_headers_b qh,
qp_list_lines ql
WHERE qh.list_type_code = 'PRO'
and qh.active_flag = 'Y'
and ql.list_header_id = qh.list_header_id
and ql.list_line_type_code in ('OID','PRG','IUE','TSN','CIE')
and rownum = 1
);
SELECT 'Y'
FROM qp_list_headers_b qh
WHERE qh.active_flag = 'Y'
and qh.list_type_code in ('PRO','DLT','SLT','DEL','CHARGES')
and exists (select 'Y'
from qp_limits qlim
where qlim.list_header_id = qh.list_header_id)
and rownum = 1;
select pricing_phase_id
from qp_pricing_phases
where pricing_phase_id = nvl(p_phase_id, pricing_phase_id);
IF p_update_type in ('PHASE', 'ALL')
THEN
put_line('Begin Pricing Phase Update');
/*update qp_pricing_phases PH
--at least 1 PRG modifier exists with rltd line
set rltd_exists = (
select /*+ ordered use_nl(rlt lh) index(ll QP_LIST_LINES_N5) * / 'Y'
from qp_list_lines LL, qp_rltd_modifiers RLT, qp_list_headers_b LH
where LH.active_flag = 'Y'
and LH.list_type_code = 'PRO'
and LL.pricing_phase_id = PH.pricing_phase_id
and LL.list_header_id = LH.list_header_id
and LL.list_line_id = RLT.from_rltd_modifier_id
and RLT.rltd_modifier_grp_type = 'QUALIFIER'
and LL.list_line_type_code = 'PRG'
and rownum = 1)
--atleast 1 modifier of type OID exist
, oid_exists = (
SELECT /*+ ordered use_nl(lh) index(ll QP_LIST_LINES_N5) * / 'Y'
from qp_list_lines LL, qp_list_headers_b LH
where LH.list_type_code = 'PRO'
and LH.active_flag = 'Y'
and LL.pricing_phase_id = PH.pricing_phase_id
and LL.list_line_type_code = 'OID'
and LL.list_header_id = LH.list_header_id
and rownum = 1)
--at least 1 modifier of level line_group exist
, line_group_exists = (
SELECT /*+ ordered use_nl(lh) index(ll QP_LIST_LINES_N4) * / 'Y'
from qp_list_lines LL, qp_list_headers_b LH
where LH.list_type_code in ('DLT','DEL','SLT','PRO','CHARGES')
and LH.active_flag = 'Y'
and LL.list_header_id = LH.list_header_id
and LL.pricing_phase_id = PH.pricing_phase_id
and LL.modifier_level_code = 'LINEGROUP'
and rownum = 1)
--at least 1 freight charge modifier exist
, freight_exists = (
SELECT /*+ ordered use_nl(lh) index(ll QP_LIST_LINES_N5) * / 'Y'
from qp_list_lines LL, qp_list_headers_b LH
where LH.list_type_code = 'CHARGES'
and LH.active_flag = 'Y'
and LL.list_header_id = LH.list_header_id
and LL.pricing_phase_id = PH.pricing_phase_id
and LL.list_line_type_code = 'FREIGHT_CHARGE'
and rownum = 1)
where PH.pricing_phase_id = nvl(p_pricing_phase_id,
PH.pricing_phase_id)
and ph.pricing_phase_id > 1;
update qp_pricing_phases PH
--at least 1 PRG modifier exists with rltd line
--[julin/4698834] removed qp_rltd_modifiers RLT; per bug, needs to be 'Y' if PRG simply exists
select 'Y'
from qp_list_lines LL, qp_list_headers_b LH
where LH.active_flag = 'Y'
and LH.list_type_code = 'PRO'
and LL.pricing_phase_id = PH.pricing_phase_id
and LL.list_header_id = LH.list_header_id
--and LL.list_line_id = RLT.from_rltd_modifier_id
--and RLT.rltd_modifier_grp_type = 'QUALIFIER'
and LL.list_line_type_code = 'PRG'
and rownum = 1)
where PH.pricing_phase_id = nvl(p_pricing_phase_id,
PH.pricing_phase_id)
and ph.pricing_phase_id > 1;
update qp_pricing_phases PH
set oid_exists = (
SELECT 'Y'
from qp_list_lines LL, qp_list_headers_b LH
where LH.list_type_code = 'PRO'
and LH.active_flag = 'Y'
and LL.pricing_phase_id = PH.pricing_phase_id
and LL.list_line_type_code = 'OID'
and LL.list_header_id = LH.list_header_id
and rownum = 1)
where PH.pricing_phase_id = nvl(p_pricing_phase_id,
PH.pricing_phase_id)
and ph.pricing_phase_id > 1;
update qp_pricing_phases PH
set line_group_exists = (
SELECT /*+ ordered use_nl(lh) index(ll QP_LIST_LINES_N4) */ 'Y'
from qp_list_lines LL, qp_list_headers_b LH
where LH.list_type_code in ('DLT','DEL','SLT','PRO','CHARGES')
and LH.active_flag = 'Y'
and LL.list_header_id = LH.list_header_id
and LL.pricing_phase_id = PH.pricing_phase_id
and LL.modifier_level_code = 'LINEGROUP'
and ph.pricing_phase_id >2
and rownum = 1)
where PH.pricing_phase_id = nvl(p_pricing_phase_id,
PH.pricing_phase_id)
and ph.pricing_phase_id > 1;
update qp_pricing_phases PH
set freight_exists = (
SELECT 'Y'
from qp_list_lines LL, qp_list_headers_b LH
where LH.list_type_code = 'CHARGES'
and LH.active_flag = 'Y'
and LL.list_header_id = LH.list_header_id
and LL.pricing_phase_id = PH.pricing_phase_id
and LL.list_line_type_code = 'FREIGHT_CHARGE'
and rownum = 1)
where PH.pricing_phase_id = nvl(p_pricing_phase_id,
PH.pricing_phase_id)
and ph.pricing_phase_id > 1;
select manual_modifier_flag into d_manual_modifier_flag from qp_pricing_phases
where pricing_phase_id = I.pricing_phase_id;
select 'Y' into l_automatic_exists
from qp_list_lines l, qp_list_headers_b h
where l.automatic_flag = 'Y'
and l.pricing_phase_id = I.pricing_phase_id
and l.list_header_id = h.list_header_id
and l.modifier_level_code in ('LINE', 'LINEGROUP', 'ORDER')
and h.active_flag = 'Y'
and rownum = 1;
select 'Y' into l_automatic_exists from dual
where exists (select 1 from qp_list_lines l
where l.automatic_flag = 'Y'
and l.pricing_phase_id = I.pricing_phase_id
and exists (select 'x' from qp_list_headers_b h
where l.list_header_id = h.list_header_id
and h.active_flag = 'Y'));
select 'Y' into l_manual_exists
from qp_list_lines l, qp_list_headers_b h
where l.automatic_flag = 'N'
and l.pricing_phase_id = I.pricing_phase_id
and l.list_header_id = h.list_header_id
and l.modifier_level_code in ('LINE', 'LINEGROUP', 'ORDER')
and h.active_flag = 'Y'
and rownum = 1;
select 'Y' into l_manual_exists from dual
where exists (select 1 from qp_list_lines l
where l.automatic_flag = 'N'
and l.pricing_phase_id = I.pricing_phase_id
and exists (select 'x' from qp_list_headers_b h
where l.list_header_id = h.list_header_id
and h.active_flag = 'Y'));
update qp_pricing_phases
set manual_modifier_flag = l_manual_modifier_flag
where pricing_phase_id = I.pricing_phase_id;
/* update qp_pricing_phases I
set manual_modifier_flag =
(select /*+ ordered use_nl(h) index(l QP_LIST_LINES_N5)
decode(min(l.automatic_flag)||max(l.automatic_flag),'YY','A','NN','M','B')
from qp_list_lines l, qp_list_headers_b h
where l.pricing_phase_id = I.pricing_phase_id
and l.list_header_id = h.list_header_id
and h.active_flag = 'Y')
where pricing_phase_id = nvl(p_pricing_phase_id, pricing_phase_id)
and pricing_phase_id > 1;
IF p_update_type in ('PHASE', 'ALL')
THEN
put_line('End Pricing Phase Update');
IF p_update_type in ('PHASE', 'ALL')
THEN
put_line('Completed Update of Profile: limits exist: '||l_limits_exist||' basic modifiers exist: '||l_basic_modifiers_exist);
END IF;--update_type
IF p_update_type in ('PHASE', 'ALL')
THEN
put_line('Completed Update of Profile: limits exist: '||l_limits_exist||' basic modifiers exist: '||l_basic_modifiers_exist);
END IF;--update_type
IF p_update_type in ('PHASE', 'ALL')
THEN
put_line('Completed Update of Profile: limits exist: '||l_limits_exist||' basic modifiers exist: '||l_basic_modifiers_exist);
END IF;--update_type
IF p_update_type in ('PHASE', 'ALL', 'BATCH')
THEN
commit;
IF p_update_type in ('PHASE', 'ALL')
THEN
FND_PROFILE.GET('QP_BASIC_MODIFIERS_SETUP',l_profile_val);
put_line('Completed Update of Profile:'||l_profile_val);
END IF;--update_type
x_return_status_text := 'QP_DENOB.update_pricing_phases:'||substr(SQLERRM,1,200);
IF p_update_type in ('ALL', 'PHASE')
THEN
put_line('EXCEPTION RAISED IN PHASE UPDATE'||SQLERRM);
x_return_status_text := 'QP_DENOB.update_pricing_phases:'||substr(SQLERRM,1,200);
IF p_update_type in ('ALL', 'PHASE')
THEN
put_line('EXCEPTION RAISED IN PHASE UPDATE'||SQLERRM);
end update_pricing_phases;
procedure update_row_count(p_List_Header_Id_low NUMBER
,p_List_Header_Id_High NUMBER
,p_update_type VARCHAR
,x_return_status OUT NOCOPY VARCHAR2
,x_return_status_text OUT NOCOPY VARCHAR2)
is
/* Changes for bug 3136350.
This is performance bug fix. The update statement for volume data was taking long time.
Modified the logic to eliminate the corelated query update logic. Changed the login to do
a bulk update.
*/
-- bug 3136350 start
CURSOR upd_distinct_row_count IS
SELECT qpq1.qualifier_context,
qpq1.qualifier_attribute,
qpq1.comparison_operator_code,
qpq1.qualifier_attr_value,
qpq1.qualifier_attr_value_to,
count(*) distinct_rows
FROM qp_qualifiers qpq1
WHERE qpq1.list_header_id between p_List_Header_Id_low and p_List_Header_Id_High --5860276
and qpq1.list_header_id is not null
AND qpq1.active_flag = 'Y'
--for bug 5121471
and qpq1.list_type_code not in ('PRL', 'AGR')
GROUP BY qpq1.qualifier_context,
qpq1.qualifier_attribute,
qpq1.comparison_operator_code,
qpq1.qualifier_attr_value,
qpq1.qualifier_attr_value_to;
IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
fnd_file.put_line(FND_FILE.LOG,'Begin Row Count Update');
update qp_qualifiers qpq set DISTINCT_ROW_COUNT=
(select count(*) from qp_qualifiers qpq1 where
qpq.qualifier_context=qpq1.qualifier_context and
qpq.qualifier_attribute=qpq1.qualifier_attribute and
qpq.qualifier_attr_value=qpq1.qualifier_attr_value and
nvl(qpq.qualifier_attr_value_to,'-x') = nvl(qpq1.qualifier_attr_value_to,'-x') and
qpq.comparison_operator_code=qpq1.comparison_operator_code and
qpq1.active_flag='Y' and
qpq1.list_header_id is not null
and qpq1.list_header_id between p_List_Header_Id_low and p_list_header_id_high)
where (qpq.list_header_id between p_List_Header_Id_low and p_list_header_id_high);
UPDATE qp_qualifiers qpq
SET DISTINCT_ROW_COUNT = rec.distinct_rows
WHERE qpq.qualifier_context = rec.qualifier_context
AND qpq.qualifier_attribute = rec.qualifier_attribute
AND qpq.comparison_operator_code = rec.comparison_operator_code
AND qpq.qualifier_attr_value = rec.qualifier_attr_value
AND nvl(qpq.qualifier_attr_value_to,'-x') = nvl(rec.qualifier_attr_value_to,'-x')
--for bug 5121471
AND qpq.list_type_code not in ('PRL', 'AGR')
AND (qpq.list_header_id between p_List_Header_Id_low and p_list_header_id_high);
IF P_UPDATE_TYPE IN ('BATCH','ALL', 'DENORMALIZED') THEN
commit;
IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
fnd_file.put_line(FND_FILE.LOG,'Completed Row Count Update');
x_return_status_text:='Exception In Update Row '||substr(sqlerrm,1,300);
end update_row_count;
PROCEDURE Update_Qualifiers
(err_buff out NOCOPY VARCHAR2,
retcode out NOCOPY NUMBER,
p_List_Header_Id NUMBER,
p_List_Header_Id_high NUMBER,
p_update_type VARCHAR2,
p_dummy VARCHAR2,
p_request_id NUMBER := NULL --bug 8359554
) Is
l_old_Header_id number := -9999;
null then cursor will return only those list lines which have been updated
or inserted for this request.
*/
cursor list_lines_cur(a_list_header_id number,b_list_header_id NUMBER, l_request_id NUMBER)
is
select /*+ index(l qp_list_lines_n15) index(h qp_list_headers_b_n7)*/ --8418006
l.list_line_id, l.qualification_ind, h.list_type_code, h.list_header_id
from qp_list_lines l, qp_list_headers_b h
where l.list_header_id = h.list_header_id
and h.active_flag = 'Y'
and (h.list_header_id between a_list_header_id and b_list_header_id)
and decode (l_request_id,null,1,l.REQUEST_ID) = nvl (l_request_id,1) --bug 8359554
order by h.list_header_id; --7321919
select list_header_id
from qp_list_headers_b
where list_header_id between a_list_header_id and b_list_header_id
and list_type_code not in ('PRL', 'AGR', 'PML');
select list_header_id, pricing_phase_id from qp_list_header_phases
where list_header_id between
--for bug 5121471
p_List_Header_Id and p_List_Header_Id_high;
select list_line_id from qp_list_lines where
list_header_id = p_list_header_id
and pricing_phase_id = p_pricing_phase_id
minus
select list_line_id from qp_qualifiers where
list_header_id = p_list_header_id
and list_line_id <> -1;
IF P_UPDATE_TYPE in ('HVOP_PRICING_SETUP','ALL')
and QP_CODE_CONTROL.Get_Code_Release_Level > '110509' THEN
Set_HVOP_Pricing(l_return_status, err_buff);
IF P_UPDATE_TYPE in ('HVOP_PRICING_SETUP','ALL') THEN
put_line('Exception in Update_HVOP Profile: '||l_return_status);
END IF;--P_UPDATE_TYPE = 'HVOP_PRICING_SETUP'
IF P_UPDATE_TYPE in ('BATCH_ADV_MOD_PRODUCTS', 'ADV_MOD_PRODUCTS','ALL')
and QP_CODE_CONTROL.Get_Code_Release_Level > '110508' THEN
--for bug 5237249
IF p_list_header_id IS NOT NULL THEN
G_LIST_HEADER_ID := p_list_header_id;
G_UPDATE_TYPE := P_UPDATE_TYPE;
Update_adv_mod_products(l_return_status, err_buff);
IF P_UPDATE_TYPE in ('ADV_MOD_PRODUCTS','ALL') THEN
put_line('Exception in Update_adv_mod_products: '||l_return_status);
END IF;--P_UPDATE_TYPE = 'ADV_MOD_PRODUCTS'
IF P_UPDATE_TYPE in ('PHASE','ALL') THEN
put_line('Begin Update Pricing Phases - update_type '||p_update_type);
update_pricing_phases(p_update_type => p_update_type,
x_return_status => l_return_status,
x_return_status_text => err_buff);
put_line('Completed Update of Pricing Phases');
END IF;--P_UPDATE_TYPE = 'PHASE'
IF p_update_type IN ('ALL','FACTOR','BATCH')
THEN
IF P_UPDATE_TYPE IN ('ALL','FACTOR') THEN
put_line('Begin Factor Attrs Denormalization');
QP_Denormalized_Pricing_Attrs.Update_Pricing_Attributes(
p_list_header_id,
p_list_header_id_high,
p_update_type);
put_line('Exception occured while excecuting QP_Denormalized_Pricing_Attrs.Update_Pricing_Attributes');
IF P_UPDATE_TYPE IN ('ALL','FACTOR') THEN
put_line('End Factor Attrs Denormalization');
put_line('Begin Insertion of Factor List Attrs');
IF P_UPDATE_TYPE IN ('ALL','FACTOR') THEN
put_line('End Insertion of Factor List Attrs');
END IF; --If p_update_type in ALL, FACTOR, BATCH
and P_UPDATE_TYPE <> 'BATCH_ADV_MOD_PRODUCTS' THEN
IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
put_line('Begin Update list_header_id '||p_list_header_id||' '||p_list_header_id_high||' update_type '||p_update_type);
IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
put_line('list_header_ids '||l_list_header_id_low||'- '||l_list_header_id_high||' update_type '||p_update_type);
IF p_update_type IN ('QUAL_IND','ALL') THEN --(Qual Ind)
-- IF P_UPDATE_TYPE IN ('ALL','QUAL_IND') THEN
put_line('Begin Update Qualification_Ind ');
l_list_line_id_tbl.delete;
l_list_header_id_tbl.delete;
l_qualification_ind_tbl.delete;
l_list_type_code_tbl.delete;
select 1
into l_count
from dual where exists
(select 'x'
from qp_rltd_modifiers
where to_rltd_modifier_id = l_list_line_id_tbl(i)
and rltd_modifier_grp_type <> 'COUPON');
select 1
into l_count
from dual where exists
(select 'x'
from qp_qualifiers
where list_header_id = l_list_header_id_tbl(i)
and NOT (qualifier_context = 'MODLIST' and
qualifier_attribute = 'QUALIFIER_ATTRIBUTE4')
);
select 1
into l_count
from dual where exists
(select 'x'
from qp_qualifiers
where list_header_id = l_list_header_id_tbl(i)
and nvl(list_line_id,-1) = -1);
select 1
into l_count
from dual where exists
(select 'x'
from qp_qualifiers
where list_header_id = l_list_header_id_tbl(i)
and list_line_id = l_list_line_id_tbl(i));
select 1
into l_count
from dual where exists
(select 'x'
from qp_pricing_attributes
where list_line_id = l_list_line_id_tbl(i)
and excluder_flag = 'N');
select 1
into l_count
from dual where exists
(select 'x'
from qp_pricing_attributes
where list_line_id = l_list_line_id_tbl(i)
and pricing_attribute_context is not null
and pricing_attribute is not null
-- changes made per rchellam's request--spgopal
and pricing_attr_value_from IS NOT NULL);
IF P_UPDATE_TYPE IN ('ALL','QUAL_IND') THEN
put_line( substr(sqlerrm, 1, 240) );
UPDATE qp_list_lines
SET qualification_ind = l_qualification_ind_tbl(j)
WHERE list_line_id = l_list_line_id_tbl(j);
UPDATE qp_pricing_attributes
SET qualification_ind = l_qualification_ind_tbl(k)
WHERE list_line_id = l_list_line_id_tbl(k);
IF P_UPDATE_TYPE IN ('ALL','QUAL_IND') THEN
put_line( substr(sqlerrm, 1, 240));
IF P_UPDATE_TYPE IN ('ALL','QUAL_IND') THEN
put_line('Qualification_Ind Update Completed');
END IF; --IF update_type IN (ALL, QUAL_IND), (see matching (Qual Ind))
IF P_UPDATE_TYPE IN ('BATCH','HEADER_PHASE','ALL')
THEN
IF P_UPDATE_TYPE IN ('ALL','HEADER_PHASE') THEN
put_line('Begin Maintain List Header Phases ');
delete from qp_list_header_phases
where list_header_id = l_rec.list_header_id;
insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG) /* Added column names for 2236671 */
(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 = l_rec.list_header_id);
IF P_UPDATE_TYPE IN ('ALL','HEADER_PHASE') THEN
put_line('Completed Maintain List Header Phases');
--We do not want the update of qualification_ind when calling from delayed request package as this would have been done already by the other delayed requests update_list_qual_ind and update_line_qual_ind --spgopal
null;
IF p_update_type IN ('BATCH', 'ALL', 'DENORMALIZED', 'DELAYED_REQ', 'UPD_QUAL') THEN
--we want to process the request for different updates based on update type
--Added for 5922279. procedure update_row_count does not need to be called for AGR,PRL through UI
IF NOT( l_list_header_id_low = l_list_header_id_high AND l_list_type_code in ('PRL','AGR')) then
Update_row_count(l_list_header_id_low,l_list_header_id_high, p_update_type,l_return_status,err_buff);
IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
put_line('Begin Update Denormalized columns ');
SELECT MAX(ABS(qualifier_grouping_no))
INTO l_max_qual_no
FROM qp_qualifiers
WHERE list_header_id between l_list_header_id_low and l_list_header_id_high;
for c1 in (select rowid,list_header_id,nvl(list_line_id,-1) list_line_id,
nvl(qualifier_grouping_no,-1) qualifier_grouping_no, Distinct_row_count, comparison_operator_code
from qp_qualifiers
where (list_header_id between l_list_header_id_low and l_list_header_id_high)
and list_type_code not in ('PRL','AGR')
--order by list_header_id,nvl(list_line_id,-1),decode(nvl(qualifier_grouping_no,-1), -1, -9999, qualifier_grouping_no), comparison_operator_code, Distinct_row_count) -- 7038849
order by list_header_id,nvl(list_line_id,-1),decode(nvl(qualifier_grouping_no,-1),-1,nvl(qualifier_grouping_no,-1),nvl(qualifier_grouping_no,-1)+l_max_qual_no), comparison_operator_code, Distinct_row_count) -- 7038849
loop
--oe_debug_pub.add(c1.rowid ||', '|| c1.list_header_id ||', '|| c1.list_line_id ||', '|| c1.qualifier_grouping_no ||', '|| l_qual_cnt ||', '|| l_group_cnt ||', '|| l_null_grp_count);
/* Update the rows of the group with group count */
If l_group_cnt_tbl.count > 0 then
For k in l_Grp_Start_Index..l_group_cnt_tbl.Last loop
If c1.qualifier_grouping_no <> -1 then
If (c1.list_header_id = l_old_header_id and
c1.list_line_id = l_old_line_id and l_null_grp_count > 0 ) then
--fix for bug 2102211 performance problem
--populating the l_null_header_id_tbl and
--l_null_line_id_tbl only when list_header_id
--or list_line_id changes
If not l_null_header_exists_tbl.exists(l_old_header_id) then
l_null_header_exists_tbl(l_old_header_id) := 1;
l_null_Line_exists_tbl.delete;
update qp_qualifiers
Set SEARCH_IND = L_Search_Ind_tbl(K),
QUALIFIER_GROUP_CNT = l_group_cnt_tbl(K),
-- qualifier_group_cnt = decode(qualifier_grouping_no, -1, 0, l_group_cnt_tbl(k)),
HEADER_QUALS_EXIST_FLAG=l_header_qual_exists_tbl(K),
others_group_cnt = l_others_group_cnt_tbl(k)
Where rowid = l_rowid_tbl(K)
and list_type_code not in ('PRL', 'AGR');
IF p_update_type IN ('BATCH','DENORMALIZED', 'ALL') THEN
Commit;
l_rowid_tbl.delete;
L_Search_Ind_tbl.delete;
l_group_cnt_tbl.delete;
l_others_group_cnt_tbl.delete;
l_header_qual_exists_tbl.delete;
--update the remaining groups
if l_search_ind_1_set = 0 then
l_search_ind_tbl(l_search_ind_tbl.count) := 1;
update qp_qualifiers
Set SEARCH_IND = L_Search_Ind_tbl(K),
QUALIFIER_GROUP_CNT = l_group_cnt_tbl(K),
-- qualifier_group_cnt = decode(qualifier_grouping_no, -1, 0, l_group_cnt_tbl(k)),
HEADER_QUALS_EXIST_FLAG=l_header_qual_exists_tbl(K),
others_group_cnt = l_others_group_cnt_tbl(k)
Where rowid = l_rowid_tbl(K)
and list_type_code not in ('PRL', 'AGR');
IF P_UPDATE_TYPE IN ('BATCH','DENORMALIZED', 'ALL', 'UPD_QUAL') THEN
Commit;
oe_debug_pub.add(' update: ' || l_rowid_tbl(K) ||', '|| l_group_cnt_tbl(K) ||', '|| l_others_group_cnt_tbl(K));
l_rowid_tbl.delete;
L_Search_Ind_tbl.delete;
l_group_cnt_tbl.delete;
l_others_group_cnt_tbl.delete;
l_header_qual_exists_tbl.delete;
-- Update all the null grouping number rows to search ind 2 , if there is any other grp
If l_null_header_id_tbl.count > 0 then
Forall J in l_null_header_id_tbl.First..l_null_header_id_tbl.Last
update qp_qualifiers
set search_ind = 2
-- qualifier_group_cnt = qualifier_group_cnt + 1 -- Arbitary increasing the ct by 1 for null grp -- dont care
where nvl(qualifier_grouping_no,-1) = -1
and list_header_id = l_null_header_id_tbl(J)
and list_line_id = l_null_line_id_tbl(J);
IF P_UPDATE_TYPE IN ('BATCH' ,'DENORMALIZED', 'ALL')THEN
commit;
END IF;--p_update_type in 'BATCH', 'DENORMALIZED', 'ALL', 'DELAYED_REQ'
IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
put_line('Completed Update Denormalized columns ');
IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND','HEADER PHASE') THEN
put_line('Could not perform updates');
err_buff :='Could not perform updates , At least one value of modifier list or pricelist must be entered';
IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND','HEADER PHASE','UPD_QUAL', 'BATCH') THEN --BATCH added for bug 5121471
update qp_list_header_phases set qualifier_flag = NULL
WHERE qualifier_flag is not null; --9732576
update qp_list_header_phases
set qualifier_flag = 'Y'
where list_header_id = i.list_header_id
and pricing_phase_id = i.pricing_phase_id;
IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND','HEADER_PHASE', 'FACTOR') THEN
put_line(substr(sqlerrm,1,300));
END Update_Qualifiers;