[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LINES_PER_INSERT CONSTANT NUMBER := 5000;
PROCEDURE update_pattern_phases (
p_list_header_id NUMBER,
p_min_list_line_id NUMBER,
p_max_list_line_id NUMBER
)
IS
CURSOR pattern_phases_dates_flags_all IS
SELECT pricing_phase_id, pattern_id,
DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
NVL(MAX(active_flag),'N') active_flag
FROM
( -- Modifier Lines
SELECT pricing_phase_id, pattern_id, START_DATE_ACTIVE_L sda,
END_DATE_ACTIVE_L eda, active_flag
FROM qp_attribute_groups
WHERE list_line_id <> -1
UNION ALL
-- Modifier Headers
SELECT /*+ ordered USE_NL(qplhp qpag) */
qplhp.pricing_phase_id, qpag.pattern_id,
qpag.START_DATE_ACTIVE_H sda, qpag.END_DATE_ACTIVE_H eda, qpag.active_flag
FROM qp_list_header_phases qplhp, qp_attribute_groups qpag
WHERE qpag.list_header_id = qplhp.list_header_id
AND qpag.list_line_id = -1
UNION ALL
-- Price List Lines
SELECT /*+ ordered USE_NL(qpl qph) */
qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
qpl.END_DATE_ACTIVE eda, qph.active_flag
FROM qp_list_lines qpl, qp_list_headers_all_b qph
WHERE pricing_phase_id = 1
AND qph.list_header_id = qpl.list_header_id
UNION ALL
-- Price List headers
SELECT pricing_phase_id, pattern_id,
START_DATE_ACTIVE_H sda, END_DATE_ACTIVE_H eda, active_flag
FROM qp_attribute_groups
WHERE pricing_phase_id = 1
AND list_line_id = -1
)b
GROUP BY pricing_phase_id, pattern_id;
SELECT pricing_phase_id, pattern_id,
DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
NVL(MAX(active_flag),'N') active_flag
FROM
( -- Modifier Lines
SELECT pricing_phase_id, pattern_id, START_DATE_ACTIVE_L sda,
END_DATE_ACTIVE_L eda, active_flag
FROM qp_attribute_groups
WHERE list_line_id <> -1
AND list_header_id = p_list_header_id
UNION ALL
-- Modifier Headers
SELECT /*+ ordered USE_NL(qplhp qpag) */
qplhp.pricing_phase_id, qpag.pattern_id,
qpag.START_DATE_ACTIVE_H sda, qpag.END_DATE_ACTIVE_H eda, qpag.active_flag
FROM qp_list_header_phases qplhp, qp_attribute_groups qpag
WHERE qplhp.list_header_id = p_list_header_id
AND qpag.list_header_id = qplhp.list_header_id
AND qpag.list_line_id = -1
)b
GROUP BY pricing_phase_id, pattern_id;
SELECT pricing_phase_id, pattern_id,
DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
NVL(MAX(active_flag),'N') active_flag
FROM
(
-- Price List Lines
SELECT /*+ ordered USE_NL(qpl qph) index(qpl QP_LIST_LINES_N1) */
qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
qpl.END_DATE_ACTIVE eda, qph.active_flag
FROM qp_list_lines qpl, qp_list_headers_all_b qph
WHERE pricing_phase_id = 1
AND qpl.list_header_id = p_list_header_id --NVL(p_list_header_id, qpl.list_header_id)
AND qph.list_header_id = qpl.list_header_id
UNION ALL
-- Price List headers
SELECT pricing_phase_id, pattern_id,
START_DATE_ACTIVE_H sda, END_DATE_ACTIVE_H eda, active_flag
FROM qp_attribute_groups
WHERE pricing_phase_id = 1
AND list_header_id = p_list_header_id
AND list_line_id = -1
)b
GROUP BY pricing_phase_id, pattern_id;
SELECT pricing_phase_id, pattern_id,
DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
NVL(MAX(active_flag),'N') active_flag
FROM
( -- Modifier Lines
SELECT pricing_phase_id, pattern_id, START_DATE_ACTIVE_L sda,
END_DATE_ACTIVE_L eda, active_flag
FROM qp_attribute_groups
WHERE list_line_id <> -1
UNION ALL
-- Modifier Headers
SELECT /*+ ordered */
qplhp.pricing_phase_id, qpag.pattern_id,
qpag.START_DATE_ACTIVE_H sda, qpag.END_DATE_ACTIVE_H eda, qpag.active_flag
FROM qp_list_header_phases qplhp, qp_attribute_groups qpag
WHERE qpag.list_line_id = -1
AND qpag.list_header_id = qplhp.list_header_id
)b
GROUP BY pricing_phase_id, pattern_id;
SELECT pricing_phase_id, pattern_id,
DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
NVL(MAX(active_flag),'N') active_flag
FROM
(
-- Price List Lines
SELECT /*+ ordered */
qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
qpl.END_DATE_ACTIVE eda, qph.active_flag
FROM qp_list_lines qpl, qp_list_headers_all_b qph
WHERE pricing_phase_id = 1
AND qph.list_header_id = qpl.list_header_id
UNION ALL
-- Price List headers
SELECT pricing_phase_id, pattern_id,
START_DATE_ACTIVE_H sda, END_DATE_ACTIVE_H eda, active_flag
FROM qp_attribute_groups
WHERE pricing_phase_id = 1
AND list_line_id = -1
)b
GROUP BY pricing_phase_id, pattern_id;
l_routine VARCHAR2(240):='Routine : QP_PS_ATTR_GRP_PVT.update_pattern_phases';
l_phase_ids_tbl.delete;
l_pattern_ids_tbl.delete;
l_start_date_tbl.delete;
l_end_date_tbl.delete;
l_active_flag_tbl.delete;
SELECT qpl.pricing_phase_id,
qpl.pattern_id,
qpl.start_date_active,
qpl.end_date_active,
qph.active_flag
--bug 9594320 start
BULK COLLECT INTO
l_phase_ids_tbl,
l_pattern_ids_tbl,
l_start_date_tbl,
l_end_date_tbl,
l_active_flag_tbl
--bug 9594320 end
FROM qp_list_lines qpl, qp_list_headers_all_b qph
WHERE qpl.list_line_id = p_min_list_line_id
AND qph.list_header_id = qpl.list_header_id;
SELECT pricing_phase_id,
pattern_id,
start_date_active_l,
end_date_active_l,
active_flag
--bug 9594320 start
BULK COLLECT INTO
l_phase_ids_tbl,
l_pattern_ids_tbl,
l_start_date_tbl,
l_end_date_tbl,
l_active_flag_tbl
--bug 9594320 start
FROM qp_attribute_groups qpg
WHERE qpg.list_line_id = p_min_list_line_id;
SELECT start_date_active,
end_date_active,
active_flag
INTO l_old_start_date,
l_old_end_date,
l_old_active_flag
FROM qp_pattern_phases
WHERE pricing_phase_id = l_phase_ids_tbl(i)
AND pattern_id = l_pattern_ids_tbl(i);
UPDATE qp_pattern_phases
SET start_date_active = l_start_date_tbl(i),
end_date_active = l_end_date_tbl(i),
active_flag = l_active_flag_tbl(i)
WHERE pricing_phase_id = l_phase_ids_tbl(i)
AND pattern_id = l_pattern_ids_tbl(i);
UPDATE qp_pattern_phases
SET start_date_active = l_start_date_tbl(i),
end_date_active = l_end_date_tbl(i),
active_flag = l_active_flag_tbl(i)
WHERE pricing_phase_id = l_phase_ids_tbl(i)
AND pattern_id = l_pattern_ids_tbl(i);
UPDATE qp_pattern_phases
SET start_date_active = l_start_date_tbl(i)
WHERE pricing_phase_id = l_phase_ids_tbl(i)
AND pattern_id = l_pattern_ids_tbl(i)
AND ((l_start_date_tbl(i) IS NULL )
OR
(start_date_active IS NOT NULL AND
start_date_active > l_start_date_tbl(i)
));
UPDATE qp_pattern_phases
SET end_date_active = l_end_date_tbl(i)
WHERE pricing_phase_id = l_phase_ids_tbl(i)
AND pattern_id = l_pattern_ids_tbl(i)
AND ((l_end_date_tbl(i) IS NULL )
OR
(end_date_active IS NOT NULL AND
end_date_active < l_end_date_tbl(i)
));
UPDATE qp_pattern_phases
SET active_flag = NVL(l_active_flag_tbl(i),'N')
WHERE pricing_phase_id = l_phase_ids_tbl(i)
AND pattern_id = l_pattern_ids_tbl(i)
AND NVL(active_flag,'N') <> 'Y';
oe_debug_pub.add('QP_PS_ATTR_GRP_PVT.update_pattern_phases ' || SQLERRM);
write_log( 'QP_PS_ATTR_GRP_PVT.update_pattern_phases ' || SQLERRM );
END update_pattern_phases;
select list_line_id from qp_list_lines where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0)
and list_header_id = nvl(p_list_header_id, list_header_id)
order by list_line_id;
select list_line_id from qp_list_lines where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0)
and list_line_id BETWEEN p_low_list_line_id AND p_high_list_line_id
order by list_line_id;
select distinct b.pattern_type, b.pattern_string, b.pattern_id
from qp_patterns a, qp_patterns b
where a.pattern_type = b.pattern_type
and a.pattern_string = b.pattern_string
and a.pattern_id <> b.pattern_id
and b.pattern_id >(select min(c.pattern_id)
from qp_patterns c
where c.pattern_type = a.pattern_type
and c.pattern_string = a.pattern_string);
g_pattern_pattern_id_final_tbl.delete;
g_pattern_pat_type_final_tbl.delete;
g_pattern_pat_string_final_tbl.delete;
select min(c.pattern_id)
into l_min_pattern_id
from qp_patterns c
where c.pattern_type = g_pattern_pat_type_final_tbl(i)
and c.pattern_string = g_pattern_pat_string_final_tbl(i);
update qp_attribute_groups
set pattern_id = l_min_pattern_id
where pattern_id = g_pattern_pattern_id_final_tbl(i);
update /*+ index(lines QP_LIST_LINES_N9) */ qp_list_lines lines
set pattern_id = l_min_pattern_id
where pattern_id = g_pattern_pattern_id_final_tbl(i);
update qp_attribute_groups
set pattern_id = l_min_pattern_id
where pattern_id = g_pattern_pattern_id_final_tbl(i);
delete from qp_pattern_phases a
where a.pattern_id = g_pattern_pattern_id_final_tbl(i)
and a.pricing_phase_id in (select b.pricing_phase_id
from qp_pattern_phases b
where b.pattern_id = l_min_pattern_id);
update qp_pattern_phases
set pattern_id = l_min_pattern_id
where pattern_id = g_pattern_pattern_id_final_tbl(i);
DELETE qp_patterns
where pattern_id = g_pattern_pattern_id_final_tbl(i);
g_pattern_pattern_id_final_tbl.delete;
g_pattern_pat_type_final_tbl.delete;
g_pattern_pat_string_final_tbl.delete;
update qp_pattern_phases a
set (start_date_active, end_date_active, active_flag) =
(
select decode(min(nvl(sda,g_min_date)),g_min_date,null,min(sda)) min_start_date,
decode(max(nvl(eda,g_max_date)),g_max_date,null,max(eda)) max_end_date,
nvl(max(active_flag),'N') active_flag
from
( -- Modifier Lines
select pricing_phase_id, pattern_id, START_DATE_ACTIVE_L sda, END_DATE_ACTIVE_L eda, active_flag
from qp_attribute_groups
where list_line_id <> -1
UNION ALL
-- Modifier Headers
select /*+ ordered */ qplhp.pricing_phase_id, qpag.pattern_id, qpag.START_DATE_ACTIVE_H sda,
qpag.END_DATE_ACTIVE_H eda, qpag.active_flag
from qp_list_header_phases qplhp, qp_attribute_groups qpag
where qpag.list_header_id = qplhp.list_header_id
and qpag.list_line_id = -1
union all
-- Price List Lines
select /*+ ordered */ qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
qpl.END_DATE_ACTIVE eda, qph.active_flag
from qp_list_lines qpl, qp_list_headers_all_b qph
where pricing_phase_id = 1
and qph.list_header_id = qpl.list_header_id
union ALL
-- Price List headers
select pricing_phase_id, pattern_id, START_DATE_ACTIVE_H sda,
END_DATE_ACTIVE_H eda, active_flag
from qp_attribute_groups
where pricing_phase_id = 1
and list_line_id = -1
) b
where b.pricing_phase_id = a.pricing_phase_id
and b.pattern_id = a.pattern_id
--group by pricing_phase_id, pattern_id
);
SELECT list_type_code INTO g_list_type FROM qp_list_headers WHERE list_header_id = p_list_header_id;
select sid into v_sid from v$session where audsid = userenv('SESSIONID');
select hsecs into l_end_time from v$timer;
update qp_pte_segments
set used_in_search ='Y'
where NVL(used_in_search,'N') ='N'
and segment_id in
( select DISTINCT segment_id
from qp_patterns );
update qp_pte_segments
set used_in_search ='N'
where NVL(used_in_search,'Y') ='Y'
and segment_id not in
( select DISTINCT segment_id
from qp_patterns );
UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
SET used_in_search = 'Y'
WHERE NVL(used_in_search,'N') = 'N'
AND segment_id in
(select pricing_segment_id
from qp_pricing_attributes
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */
--list_header_id = nvl(p_list_header_id, list_header_id)
comparison_operator_code <> '='
and pricing_segment_id is not NULL
UNION all
select segment_id
from qp_qualifiers
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */
--list_header_id = nvl(p_list_header_id, list_header_id)
comparison_operator_code <> '='
and segment_id is not null);
UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
SET used_in_search = 'Y'
WHERE NVL(used_in_search,'N') = 'N'
AND segment_id in
(select pricing_segment_id
from qp_pricing_attributes
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */
list_header_id = p_list_header_id
--AND comparison_operator_code <> '='
and pricing_segment_id is not NULL
UNION all
select segment_id
from qp_qualifiers
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */
list_header_id = p_list_header_id
--AND comparison_operator_code <> '='
and segment_id is not null);
update_pattern_phases(p_list_header_id,p_low_list_line_id,p_high_list_line_id);
update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_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,'Y'
from qp_list_lines
where pricing_phase_id > 1
and qualification_ind in (0,4,20)
MINUS
SELECT distinct list_header_id, pricing_phase_id,'Y'
FROM qp_list_header_phases
);
UPDATE qp_list_lines
SET pattern_id=NULL
WHERE pattern_id IN (-2,-3)
AND list_line_type_code IN ('PLL','PBH');
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,'Y'
from qp_list_lines
where pricing_phase_id > 1
and qualification_ind in (0,4,20)
and list_header_id = nvl(p_list_header_id, list_header_id)
MINUS
SELECT distinct list_header_id, pricing_phase_id,'Y'
FROM qp_list_header_phases
where list_header_id = nvl(p_list_header_id, list_header_id)
);
UPDATE qp_list_lines
SET pattern_id=NULL
WHERE pattern_id IN (-2,-3)
AND list_line_type_code IN ('PLL','PBH')
AND list_header_id = p_list_header_id;
/*DELETE qp_pattern_phases
where pattern_id IN (-2,-3)
AND pricing_phase_id=1;*/
select hsecs into l_end_time from v$timer;
write_log( 'Time taken for the Update process (sec):' ||(l_end_time - l_start_time)/100);
select hsecs into l_start_time from v$timer;
write_log( 'Deleted all records from 3 Pattern Master tables');
delete from qp_attribute_groups
where list_header_id = p_list_header_id
and list_line_id = -1;
write_log( 'Deleted records from qp_attribute_groups for HP for list_header_id:'||p_list_header_id);
Update_Qual_Segment_id(p_list_header_id, null, -1, -1);
g_pattern_upg_slab_table.delete;
select count(*)
into l_total_lines
from qp_list_lines
where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0);
select count(*)
into l_total_lines
from qp_list_lines
where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0)
and list_header_id = p_list_header_id;
select hsecs into l_end_time from v$timer;
/* update qp_list_lines
set pattern_id = null,
pricing_attribute_count = null,
product_uom_code = null,
hash_key = null,
cache_key = null;
delete from qp_attribute_groups
where list_header_id = p_list_header_id
and list_line_id <> -1;
/* update qp_list_lines
set pattern_id = null,
pricing_attribute_count = null,
product_uom_code = null,
hash_key = null,
cache_key = null
where --cache_key is not null
list_header_id = p_list_header_id;*/
select hsecs into l_start_time from v$timer;
/* update /*+ index_asc(lines QP_LIST_LINES_PK) */ /*qp_list_lines lines
set pattern_id = null,
pricing_attribute_count = null,
product_uom_code = null,
hash_key = null,
cache_key = null
where --cache_key is not null
list_line_id between p_low_list_line_id and p_high_list_line_id; */
delete from qp_attribute_groups
where list_header_id = p_list_header_id
and list_line_id between p_low_list_line_id and p_high_list_line_id;
/*update /*+ index_asc(lines QP_LIST_LINES_N18) *//* qp_list_lines lines
set pattern_id = null,
pricing_attribute_count = null,
product_uom_code = null,
hash_key = null,
cache_key = null
where --cache_key is not null
list_header_id = p_list_header_id
and list_line_id between p_low_list_line_id and p_high_list_line_id;*/
g_pattern_upg_chunk_table.delete;
-- update the segment_id columns for qualifiers
Update_Qual_Segment_id(p_list_header_id, null,
g_pattern_upg_chunk_table(l_count).low_list_line_id,
g_pattern_upg_chunk_table(l_count).high_list_line_id);
-- update the product_segment_id and pricing_segment_id columns in
-- qp_pricing_attributes
Update_Prod_Pric_Segment_id(p_list_header_id,
g_pattern_upg_chunk_table(l_count).low_list_line_id,
g_pattern_upg_chunk_table(l_count).high_list_line_id);
update_pp_lines(p_list_header_id,
g_pattern_upg_chunk_table(l_count).low_list_line_id,
g_pattern_upg_chunk_table(l_count).high_list_line_id);
select hsecs into l_end_time from v$timer;
select hsecs into l_start_time from v$timer;
select hsecs into l_end_time from v$timer;
SELECT * FROM (
select qpq.list_header_id,
qpq.list_line_id,
qpq.segment_id,
qph.active_flag, --bug#11927380
qpq.list_type_code,
qpq.start_date_active start_date_active_q,
qpq.end_date_active end_date_active_q,
---Added for PL/SQL Pattern Search
qpq.header_quals_exist_flag,
qph.orig_org_id,
qph.global_flag,
null product_uom_code,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
qpq.qualify_hier_descendents_flag,
NULL,
NULL list_line_type_code,
qph.automatic_flag,
----Added for PL/SQL Pattern Search
qph.currency_code,
qph.ask_for_flag,
qph.limit_exists_flag header_limit_exists,
NULL line_limit_exists,
qph.source_system_code,
qph.pte_code,
qpq.qualifier_precedence effective_precedence,
qpq.qualifier_grouping_no,
qpq.comparison_operator_code,
---1 pricing_phase_id,
DECODE(qph.list_type_code,'PRL',1,'AGR',1,'PML',null,-1),
null modifier_level_code,
qpq.qualifier_datatype attribute_datatype,
qpq.qualifier_attr_value attribute_value,
'QUAL' attribute_type
from qp_qualifiers qpq,
qp_list_headers_all_b qph
where qpq.list_line_id = -1
and qph.list_header_id = qpq.list_header_id
and qpq.list_header_id = nvl(p_list_header_id, qpq.list_header_id)
and ((p_qualifier_group is not null and qpq.qualifier_grouping_no in (-1, p_qualifier_group))
OR
(p_qualifier_group is null)
)
and ((qpq.list_type_code = 'PRL' and qpq.qualifier_context <> 'MODLIST'
and qpq.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE4')
OR
(qpq.list_type_code <> 'PRL')
)
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B')
UNION ALL
SELECT qph.list_header_id,
-1 list_line_id,
NULL segment_id,
qph.active_flag,
qph.list_type_code,
NULL start_date_active_q,
NULL end_date_active_q,
---Added for PL/SQL Pattern Search
'N' header_quals_exist_flag,
qph.orig_org_id,
qph.global_flag,
null product_uom_code,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
'N',
NULL,
NULL list_line_type_code,
qph.automatic_flag,
----Added for PL/SQL Pattern Search
qph.currency_code,
qph.ask_for_flag,
qph.limit_exists_flag header_limit_exists,
NULL line_limit_exists,
qph.source_system_code,
qph.pte_code,
null,--qpq.qualifier_precedence effective_precedence,
-1,--qpq.qualifier_grouping_no,
'BLIND' comparison_operator_code,--qpq.comparison_operator_code,
---1 pricing_phase_id,
DECODE(qph.list_type_code,'PRL',1,'AGR',1,'PML',null,-1),
null modifier_level_code,
'C' attribute_datatype,--qpq.qualifier_datatype attribute_datatype,
NULL attribute_value, --qpq.qualifier_attr_value attribute_value,
'BLIN' attribute_type
from qp_list_headers_all_b qph
where
qph.list_header_id = nvl(p_list_header_id, qph.list_header_id)
AND NOT EXISTS ( SELECT 1
FROM qp_qualifiers qpq
WHERE qpq.list_header_id = qph.list_header_id
AND qpq.list_line_id = -1
AND (( qph.list_type_code IN ('PRL','AGR') AND qpq.qualifier_context <> 'MODLIST'
and qpq.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE4'
)
OR qph.list_type_code NOT IN ('PRL','AGR'))
)
)attr_view
order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
select * from
(select /*+ ordered use_nl(qpq, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr*/ qpq.list_header_id,
qpq.list_line_id,
qpq.segment_id,
qph.active_flag, --bug#11927380
qpq.list_type_code,
qpq.start_date_active start_date_active_q,
qpq.end_date_active end_date_active_q,
---Added for PL/SQL Pattern Search
qpq.header_quals_exist_flag,
qph.orig_org_id,
qph.global_flag,
null product_uom_code_j,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
qpq.qualify_hier_descendents_flag,
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
----Added for PL/SQL Pattern Search
qph.currency_code,
qph.ask_for_flag,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qph.source_system_code,
qph.pte_code,
qpq.qualifier_precedence effective_precedence,
qpq.qualifier_grouping_no,
qpq.comparison_operator_code,
qpl.pricing_phase_id pricing_phase_id,
qpl.modifier_level_code modifier_level_code,
qpq.qualifier_datatype attribute_datatype,
qpq.qualifier_attr_value attribute_value,
'QUAL' attribute_type
from qp_list_headers_all_b qph, qp_list_lines qpl, qp_qualifiers qpq
where qph.list_header_id = p_list_header_id
AND qpl.list_header_id = qph.list_header_id
and qph.list_header_id = qpq.list_header_id
and qpl.list_line_id = qpq.list_line_id
AND qpq.list_line_id <> -1
and qpl.pricing_phase_id > 1
and qpl.qualification_ind in (8,10,12,14,28,30)
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
and ((p_qualifier_group is not null and qpq.qualifier_grouping_no in (-1, p_qualifier_group))
OR
(p_qualifier_group is null)
)
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
union
select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr*/ distinct qpl.list_header_id,
qpl.list_line_id,
qpa.product_segment_id segment_id,
qph.active_flag,
qph.list_type_code,
to_date(null) start_date_active_q,
to_date(null) end_date_active_q,
---Added for PL/SQL Pattern Search
--'N' header_quals_exist_flag,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.orig_org_id,
qph.global_flag,
qpa.product_uom_code product_uom_code_j,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
'N',
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
----Added for PL/SQL Pattern Search
qph.currency_code,
qph.ask_for_flag,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qph.source_system_code,
qph.pte_code,
qpl.product_precedence effective_precedence,
-1 qualifier_grouping_no,
'=' comparison_operator_code,
qpl.pricing_phase_id pricing_phase_id,
qpl.modifier_level_code modifier_level_code,
qpa.product_attribute_datatype attribute_datatype,
qpa.product_attr_value attribute_value,
'PROD' attribute_type
from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
where qph.list_header_id = p_list_header_id
AND qpl.list_header_id = qph.list_header_id
and qpl.list_line_id = qpa.list_line_id
and qpa.excluder_flag = 'N'
and qpl.pricing_phase_id > 1
and qpl.qualification_ind in (8,10,12,14,28,30)
and qpa.product_attribute_context is not null
and (qpa.pricing_attribute_context = 'VOLUME' or
qpa.pricing_attribute_context is null
)
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
union
select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr*/ qpl.list_header_id,
qpl.list_line_id,
qpa.pricing_segment_id segment_id,
qph.active_flag,
qph.list_type_code,
to_date(null) start_date_active_q,
to_date(null) end_date_active_q,
---Added for PL/SQL Pattern Search
--'N' header_quals_exist_flag,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.orig_org_id,
qph.global_flag,
qpa.product_uom_code product_uom_code_j,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
'N',
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
----Added for PL/SQL Pattern Search
qph.currency_code,
qph.ask_for_flag,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qph.source_system_code,
qph.pte_code,
qpl.product_precedence effective_precedence,
-1 qualifier_grouping_no,
qpa.comparison_operator_code,
qpl.pricing_phase_id pricing_phase_id,
qpl.modifier_level_code modifier_level_code,
qpa.pricing_attribute_datatype attribute_datatype,
qpa.pricing_attr_value_from attribute_value,
'PRIC' attribute_type
from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
where qph.list_header_id = p_list_header_id
AND qph.list_header_id = qpl.list_header_id
and qpl.list_line_id = qpa.list_line_id
and qpl.pricing_phase_id > 1
and qpl.qualification_ind in (8,10,12,14,28,30)
and qpa.pricing_attribute_context is not null
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
) attr_view
order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
select * from
(select /*+ ordered use_nl(qpq, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr_hdr_null*/ qpq.list_header_id,
qpq.list_line_id,
qpq.segment_id,
qph.active_flag,
qpq.list_type_code,
qpq.start_date_active start_date_active_q,
qpq.end_date_active end_date_active_q,
---Added for PL/SQL Pattern Search
qpq.header_quals_exist_flag,
qph.orig_org_id,
qph.global_flag,
null product_uom_code_j,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
'N',
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
----Added for PL/SQL Pattern Search
qph.currency_code,
qph.ask_for_flag,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qph.source_system_code,
qph.pte_code,
qpq.qualifier_precedence effective_precedence,
qpq.qualifier_grouping_no,
qpq.comparison_operator_code,
qpl.pricing_phase_id pricing_phase_id,
qpl.modifier_level_code modifier_level_code,
qpq.qualifier_datatype attribute_datatype,
qpq.qualifier_attr_value attribute_value,
'QUAL' attribute_type
from qp_list_headers_all_b qph, qp_list_lines qpl, qp_qualifiers qpq
where qpl.list_header_id = qph.list_header_id
and qph.list_header_id = qpq.list_header_id
and qpl.list_line_id = qpq.list_line_id
AND qpq.list_line_id <> -1
and qpl.pricing_phase_id > 1
and qpl.qualification_ind in (8,10,12,14,28,30)
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
and ((p_qualifier_group is not null and qpq.qualifier_grouping_no in (-1, p_qualifier_group))
OR
(p_qualifier_group is null)
)
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
union
select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr_hdr_null*/ distinct qpl.list_header_id,
qpl.list_line_id,
qpa.product_segment_id segment_id,
qph.active_flag,
qph.list_type_code,
to_date(null) start_date_active_q,
to_date(null) end_date_active_q,
---Added for PL/SQL Pattern Search
--'N' header_quals_exist_flag,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.orig_org_id,
qph.global_flag,
qpa.product_uom_code product_uom_code_j,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
'N',
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
----Added for PL/SQL Pattern Search
qph.currency_code,
qph.ask_for_flag,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qph.source_system_code,
qph.pte_code,
qpl.product_precedence effective_precedence,
-1 qualifier_grouping_no,
'=' comparison_operator_code,
qpl.pricing_phase_id pricing_phase_id,
qpl.modifier_level_code modifier_level_code,
qpa.product_attribute_datatype attribute_datatype,
qpa.product_attr_value attribute_value,
'PROD' attribute_type
from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
where qpl.list_header_id = qph.list_header_id
and qpl.list_line_id = qpa.list_line_id
and qpa.excluder_flag = 'N'
and qpl.pricing_phase_id > 1
and qpl.qualification_ind in (8,10,12,14,28,30)
and qpa.product_attribute_context is not null
and (qpa.pricing_attribute_context = 'VOLUME' or
qpa.pricing_attribute_context is null
)
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
union
select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr_hdr_null*/ qpl.list_header_id,
qpl.list_line_id,
qpa.pricing_segment_id segment_id,
qph.active_flag,
qph.list_type_code,
to_date(null) start_date_active_q,
to_date(null) end_date_active_q,
---Added for PL/SQL Pattern Search
--'N' header_quals_exist_flag,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.orig_org_id,
qph.global_flag,
qpa.product_uom_code product_uom_code_j,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
'N',
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
----Added for PL/SQL Pattern Search
qph.currency_code,
qph.ask_for_flag,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qph.source_system_code,
qph.pte_code,
qpl.product_precedence effective_precedence,
-1 qualifier_grouping_no,
qpa.comparison_operator_code,
qpl.pricing_phase_id pricing_phase_id,
qpl.modifier_level_code modifier_level_code,
qpa.pricing_attribute_datatype attribute_datatype,
qpa.pricing_attr_value_from attribute_value,
'PRIC' attribute_type
from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
where qph.list_header_id = qpl.list_header_id
and qpl.list_line_id = qpa.list_line_id
and qpl.pricing_phase_id > 1
and qpl.qualification_ind in (8,10,12,14,28,30)
and qpa.pricing_attribute_context is not null
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
) attr_view
order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
procedure update_pp_lines(p_list_header_id number
-- ,p_list_line_id number
,p_low_list_line_id IN NUMBER
,p_high_list_line_id IN NUMBER)
IS
cursor c_lines_pp_csr is
select * from
(select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr*/ distinct qpa.list_header_id,
qpa.list_line_id,
qpa.product_segment_id segment_id,
'=' comparison_operator_code,
qpa.pricing_phase_id,
qpa.product_uom_code,
qpa.product_attribute_datatype attribute_datatype,
qpa.product_attr_value attribute_value,
--- Added for PL/SQL Pattern Search
qpl.created_by,
qpl.creation_date,
qpl.request_id,
qpl.program_update_date,
qpl.program_id,
qpl.program_application_id,
qpl.modifier_level_code,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qpl.product_precedence effective_precedence,
qph.active_flag,
qph.list_type_code,
qph.currency_code,
qph.ask_for_flag,
qph.source_system_code,
qph.pte_code,
qph.global_flag,
qph.orig_org_id,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
---- Added for PL/SQL Pattern Search
'PROD' attribute_type
from qp_list_headers_all_b qph,
qp_list_lines qpl,
qp_pricing_attributes qpa
where qph.list_header_id = p_list_header_id
AND qph.list_header_id = qpl.list_header_id
AND qpl.list_line_id = qpa.list_line_id
and qpa.excluder_flag = 'N'
and qpl.qualification_ind in (4,6,20,22)
and qpa.product_attribute_context is not null
and (qpa.pricing_attribute_context = 'VOLUME' or
qpa.pricing_attribute_context is null
)
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
union
select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr*/ qpa.list_header_id,
qpa.list_line_id,
qpa.pricing_segment_id segment_id,
qpa.comparison_operator_code,
qpa.pricing_phase_id,
qpa.product_uom_code,
qpa.pricing_attribute_datatype attribute_datatype,
qpa.pricing_attr_value_from attribute_value,
--- Added for PL/SQL Pattern Search
qpl.created_by,
qpl.creation_date,
qpl.request_id,
qpl.program_update_date,
qpl.program_id,
qpl.program_application_id,
qpl.modifier_level_code,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qpl.product_precedence effective_precedence,
qph.active_flag,
qph.list_type_code,
qph.currency_code,
qph.ask_for_flag,
qph.source_system_code,
qph.pte_code,
qph.global_flag,
qph.orig_org_id,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
---- Added for PL/SQL Pattern Search
'PRIC' attribute_type
from qp_list_headers_all_b qph,
qp_list_lines qpl,
qp_pricing_attributes qpa
where qph.list_header_id = p_list_header_id
AND qpl.list_header_id = qph.list_header_id
AND qpl.list_line_id = qpa.list_line_id
and qpl.qualification_ind in (20,22)
and qpa.pricing_attribute_context is not null
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
union
select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr*/ distinct qph.list_header_id,
qpl.list_line_id,
NULL segment_id,
'BLIND' comparison_operator_code,
qpl.pricing_phase_id,
NULL product_uom_code,
'C' attribute_datatype,
'NULL' attribute_value,
---Added for PL/SQL Pattern Search
qpl.created_by,
qpl.creation_date,
qpl.request_id,
qpl.program_update_date,
qpl.program_id,
qpl.program_application_id,
qpl.modifier_level_code,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qpl.product_precedence effective_precedence,
qph.active_flag,
qph.list_type_code,
qph.currency_code,
qph.ask_for_flag,
qph.source_system_code,
qph.pte_code,
qph.global_flag,
qph.orig_org_id,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
---- Added for PL/SQL Pattern Search
'BLIN' attribute_type
from qp_list_headers_all_b qph,
qp_list_lines qpl
where qph.list_header_id = p_list_header_id
AND qpl.list_header_id = qph.list_header_id
and qpl.qualification_ind in (0,2)
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
) attr_view
order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
select * from
(select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr_hdr_null*/ distinct qpa.list_header_id,
qpa.list_line_id,
qpa.product_segment_id segment_id,
'=' comparison_operator_code,
qpa.pricing_phase_id,
qpa.product_uom_code,
qpa.product_attribute_datatype attribute_datatype,
qpa.product_attr_value attribute_value,
--- Added for PL/SQL Pattern Search
qpl.created_by,
qpl.creation_date,
qpl.request_id,
qpl.program_update_date,
qpl.program_id,
qpl.program_application_id,
qpl.modifier_level_code,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qpl.product_precedence effective_precedence,
qph.active_flag,
qph.list_type_code,
qph.currency_code,
qph.ask_for_flag,
qph.source_system_code,
qph.pte_code,
qph.global_flag,
qph.orig_org_id,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
---- Added for PL/SQL Pattern Search
'PROD' attribute_type
from qp_list_headers_all_b qph,
qp_list_lines qpl,
qp_pricing_attributes qpa
where qph.list_header_id = qpl.list_header_id
AND qpl.list_line_id = qpa.list_line_id
and qpa.excluder_flag = 'N'
and qpl.qualification_ind in (4,6,20,22)
and qpa.product_attribute_context is not null
and (qpa.pricing_attribute_context = 'VOLUME' or
qpa.pricing_attribute_context is null
)
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
union
select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr_hdr_null*/ qpa.list_header_id,
qpa.list_line_id,
qpa.pricing_segment_id segment_id,
qpa.comparison_operator_code,
qpa.pricing_phase_id,
qpa.product_uom_code,
qpa.pricing_attribute_datatype attribute_datatype,
qpa.pricing_attr_value_from attribute_value,
--- Added for PL/SQL Pattern Search
qpl.created_by,
qpl.creation_date,
qpl.request_id,
qpl.program_update_date,
qpl.program_id,
qpl.program_application_id,
qpl.modifier_level_code,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qpl.product_precedence effective_precedence,
qph.active_flag,
qph.list_type_code,
qph.currency_code,
qph.ask_for_flag,
qph.source_system_code,
qph.pte_code,
qph.global_flag,
qph.orig_org_id,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
---- Added for PL/SQL Pattern Search
'PRIC' attribute_type
from qp_list_headers_all_b qph,
qp_list_lines qpl,
qp_pricing_attributes qpa
where qpl.list_header_id = qph.list_header_id
AND qpl.list_line_id = qpa.list_line_id
and qpl.qualification_ind in (20,22)
and qpa.pricing_attribute_context is not null
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
union
select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr_hdr_null*/ distinct qph.list_header_id,
qpl.list_line_id,
NULL segment_id,
'BLIND' comparison_operator_code,
qpl.pricing_phase_id,
NULL product_uom_code,
'C' attribute_datatype,
'NULL' attribute_value,
---Added for PL/SQL Pattern Search
qpl.created_by,
qpl.creation_date,
qpl.request_id,
qpl.program_update_date,
qpl.program_id,
qpl.program_application_id,
qpl.modifier_level_code,
qph.limit_exists_flag header_limit_exists,
qpl.limit_exists_flag line_limit_exists,
qpl.product_precedence effective_precedence,
qph.active_flag,
qph.list_type_code,
qph.currency_code,
qph.ask_for_flag,
qph.source_system_code,
qph.pte_code,
qph.global_flag,
qph.orig_org_id,
qpl.start_date_active start_date_active_l,
qpl.end_date_active end_date_active_l,
qph.start_date_active_first,
qph.end_date_active_first,
qph.start_date_active_second,
qph.end_date_active_second,
qph.start_date_active start_date_active_h,
qph.end_date_active end_date_active_h,
decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
qph.active_date_first_type,
qph.active_date_second_type,
qph.currency_header_id,
qpl.price_break_type_code,
qpl.list_line_type_code,
qpl.automatic_flag,
---- Added for PL/SQL Pattern Search
'BLIN' attribute_type
from qp_list_headers_all_b qph,
qp_list_lines qpl
where qpl.list_header_id = qph.list_header_id
and qpl.qualification_ind in (0,2)
and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
) attr_view
order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
g_program_update_date_c_tbl,
g_program_id_c_tbl,
g_program_applic_id_c_tbl,
g_modifier_level_code_c_tbl,
g_header_limit_exists_c_tbl,
g_line_limit_exists_c_tbl,
g_effective_precedence_c_tbl,
g_active_flag_c_tbl,
g_list_type_code_c_tbl,
g_currency_code_c_tbl,
g_ask_for_flag_c_tbl,
g_source_system_code_c_tbl,
g_pte_code_c_tbl,
g_global_flag_c_tbl,
g_orig_org_id_c_tbl,
g_start_date_act_l_c_tbl,
g_end_date_act_l_c_tbl,
g_start_date_act_firs_c_tbl,
g_end_date_act_firs_c_tbl,
g_start_date_act_sec_c_tbl,
g_end_date_act_sec_c_tbl,
g_start_date_act_h_c_tbl,
g_end_date_act_h_c_tbl,
g_header_quals_exist_c_tbl,
g_act_date_firs_type_c_tbl,
g_act_date_sec_type_c_tbl,
g_currency_header_id_c_tbl,
g_prc_brk_typ_code_c_tbl,
g_list_line_typ_code_c_tbl,
g_automatic_flag_c_tbl,
---- Added for PL/SQL Pattern Search
g_attribute_type_c_tbl;
g_program_update_date_c_tbl,
g_program_id_c_tbl,
g_program_applic_id_c_tbl,
g_modifier_level_code_c_tbl,
g_header_limit_exists_c_tbl,
g_line_limit_exists_c_tbl,
g_effective_precedence_c_tbl,
g_active_flag_c_tbl,
g_list_type_code_c_tbl,
g_currency_code_c_tbl,
g_ask_for_flag_c_tbl,
g_source_system_code_c_tbl,
g_pte_code_c_tbl,
g_global_flag_c_tbl,
g_orig_org_id_c_tbl,
g_start_date_act_l_c_tbl,
g_end_date_act_l_c_tbl,
g_start_date_act_firs_c_tbl,
g_end_date_act_firs_c_tbl,
g_start_date_act_sec_c_tbl,
g_end_date_act_sec_c_tbl,
g_start_date_act_h_c_tbl,
g_end_date_act_h_c_tbl,
g_header_quals_exist_c_tbl,
g_act_date_firs_type_c_tbl,
g_act_date_sec_type_c_tbl,
g_currency_header_id_c_tbl,
g_prc_brk_typ_code_c_tbl,
g_list_line_typ_code_c_tbl,
g_automatic_flag_c_tbl,
---- Added for PL/SQL Pattern Search
g_attribute_type_c_tbl;
oe_debug_pub.add('exiting update pp lines' || g_list_header_id_c_tbl.count);
oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_Pp_Lines ' || SQLERRM);
write_log( 'PS_ATTR_GRP_PVT.Update_Pp_Lines ' || SQLERRM );
end update_pp_lines;
if l_line_counter >= G_LINES_PER_INSERT then
if g_call_from_setup = 'Y' then
oe_debug_pub.add('inserting data for ' || G_LINES_PER_INSERT || ' lines');
write_log( 'inserting data for ' || G_LINES_PER_INSERT || ' lines');
update_list_lines_cache_key;
write_log( 'committing data for ' || G_LINES_PER_INSERT || ' lines');
update_list_lines_cache_key;
if l_line_counter >= G_LINES_PER_INSERT then
if g_call_from_setup = 'Y' then
oe_debug_pub.add('inserting data for ' || G_LINES_PER_INSERT || ' lines');
write_log( 'inserting data for ' || G_LINES_PER_INSERT || ' lines');
update_list_lines;
write_log( 'committing data for ' || G_LINES_PER_INSERT || ' lines');
g_program_update_date_tmp_tbl(-1) := g_program_update_date_c_tbl(i);
update_list_lines;
oe_debug_pub.add('Count final table before final insertion - '||g_list_line_id_final_tbl.count);
select product_precedence into l_product_precedence
from qp_list_lines where list_line_id = g_list_line_id_final_tbl(l_atgrp_final_index);
g_program_update_date_fnl_tbl(l_atgrp_final_index) := g_program_update_date_tmp_tbl(grp_no_index);
g_last_update_date_final_tbl(l_atgrp_final_index) := sysdate;
g_last_updated_by_final_tbl(l_atgrp_final_index) := FND_GLOBAL.USER_ID;
g_last_update_login_final_tbl(l_atgrp_final_index) := FND_GLOBAL.LOGIN_ID;
INSERT INTO qp_patterns
(
pattern_id,
segment_id,
segment_index,
pattern_type,
pattern_string,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id
)
VALUES
(
g_pattern_pattern_id_final_tbl(i),
g_pattern_segment_id_final_tbl(i),
g_pattern_segment_ind_fnl_tbl(i),
g_pattern_pat_type_final_tbl(i),
g_pattern_pat_string_final_tbl(i),
g_pattern_cr_dt_final_tbl(i),
g_pattern_cr_by_final_tbl(i),
g_pattern_lst_up_dt_final_tbl(i),
g_pattern_lt_up_by_final_tbl(i),
g_pattern_lt_up_lg_final_tbl(i),
g_pattern_pr_ap_id_final_tbl(i),
g_pattern_pr_id_final_tbl(i),
g_pattern_pr_up_dt_final_tbl(i),
g_pattern_req_id_final_tbl(i)
);
g_pattern_pattern_id_final_tbl.delete;
g_pattern_segment_id_final_tbl.delete;
g_pattern_pat_type_final_tbl.delete;
g_pattern_pat_string_final_tbl.delete;
g_pattern_cr_dt_final_tbl.delete;
g_pattern_cr_by_final_tbl.delete;
g_pattern_lst_up_dt_final_tbl.delete;
g_pattern_lt_up_by_final_tbl.delete;
g_pattern_lt_up_lg_final_tbl.delete;
g_pattern_pr_ap_id_final_tbl.delete;
g_pattern_pr_id_final_tbl.delete;
g_pattern_pr_up_dt_final_tbl.delete;
g_pattern_req_id_final_tbl.delete;
g_pattern_segment_ind_fnl_tbl.delete;
PROCEDURE update_list_lines
is
BEGIN
FORALL i in 1 .. g_list_line_id_final_tbl.count
UPDATE /*+ index(lines QP_LIST_LINES_PK) */ qp_list_lines lines
set pattern_id = g_pattern_id_final_tbl(i),
product_uom_code = g_product_uom_code_final_tbl(i),
pricing_attribute_count = g_pricing_attr_count_final_tbl(i),
hash_key = g_hash_key_final_tbl(i),
cache_key = g_cache_key_final_tbl(i),
last_update_date = g_last_update_date_final_tbl(i),
last_updated_by = g_last_updated_by_final_tbl(i),
last_update_login = g_last_update_login_final_tbl(i),
eq_flag = g_eq_flag_fnl_tbl(i),
null_other_oprt_count = g_null_other_oprt_cnt_fnl_tbl(i),
pte_code = g_pte_code_fnl_tbl(i),
source_system_code = g_source_system_code_final_tbl(i)
where list_header_id = g_list_header_id_final_tbl(i)
AND list_line_id = g_list_line_id_final_tbl(i)
--- Added for PL/SQL Pattern Search
AND g_pricing_phase_id_final_tbl(i)= 1; --- for price lists only
insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
(select /*+ index(QP_LIST_LINES QP_LIST_LINES_U1) */ distinct list_header_id, pricing_phase_id,'Y'
from qp_list_lines
where pricing_phase_id > 1
and qualification_ind in (0,4,20)
and list_header_id = g_list_header_id_final_tbl(i)
MINUS
SELECT distinct list_header_id, pricing_phase_id,'Y'
FROM qp_list_header_phases
where list_header_id = g_list_header_id_final_tbl(i)
);
oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_List_Lines ' || SQLERRM);
write_log( 'PS_ATTR_GRP_PVT.Update_List_Lines ' || SQLERRM );
end update_list_lines;
INSERT INTO qp_attribute_groups
(list_header_id,
list_line_id,
pattern_id,
product_uom_code,
hash_key,
cache_key,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
request_id,
program_update_date,
program_id,
program_application_id,
pricing_phase_id,
modifier_level_code,
header_limit_exists,
line_limit_exists,
effective_precedence,
active_flag,
list_type_code,
currency_code,
ask_for_flag,
source_system_code,
orig_org_id,
global_flag,
eq_flag,
descendents_quals_exist,
grouping_no,
start_date_active_first,
end_date_active_first,
start_date_active_second,
end_date_active_second,
start_date_active_h,
end_date_active_h,
start_date_active_l,
end_date_active_l,
header_quals_exist_flag,
active_date_first_type,
active_date_second_type,
currency_header_id,
other_oprt_count,
null_other_oprt_count,
pte_code,
price_break_type_code,
list_line_type_code,
automatic_flag)
SELECT g_list_header_id_final_tbl(i),
g_list_line_id_final_tbl(i),
g_pattern_id_final_tbl(i),
g_product_uom_code_final_tbl(i),
g_hash_key_final_tbl(i),
g_cache_key_final_tbl(i),
g_last_update_date_final_tbl(i),
g_last_updated_by_final_tbl(i),
g_last_update_login_final_tbl(i),
g_created_by_final_tbl(i),
g_creation_date_final_tbl(i),
g_request_id_fnl_tbl(i),
g_program_update_date_fnl_tbl(i),
g_program_id_fnl_tbl(i),
g_program_applic_id_fnl_tbl(i),
g_pricing_phase_id_final_tbl(i),
g_modifier_lvl_code_final_tbl(i),
g_header_limit_exists_fnl_tbl(i),
g_line_limit_exists_fnl_tbl(i),
g_effec_precedence_final_tbl(i),
g_active_flag_final_tbl(i),
g_list_type_code_final_tbl(i),
g_currency_code_final_tbl(i),
g_ask_for_flag_final_tbl(i),
g_source_system_code_final_tbl(i),
g_orig_org_id_fnl_tbl(i),
g_global_flag_fnl_tbl(i),
g_eq_flag_fnl_tbl(i) ,
g_desc_quals_exist_fnl_tbl(i),
-1,
g_start_date_act_firs_fnl_tbl(i),
g_end_date_act_firs_fnl_tbl(i),
g_start_date_act_sec_fnl_tbl(i),
g_end_date_act_sec_fnl_tbl(i),
g_start_date_act_h_fnl_tbl(i),
g_end_date_act_h_fnl_tbl(i),
g_start_date_act_l_fnl_tbl(i),
g_end_date_act_l_fnl_tbl(i),
g_header_quals_exist_fnl_tbl(i),
g_act_date_first_type_fnl_tbl(i),
g_act_date_sec_type_fnl_tbl(i),
g_currency_header_id_fnl_tbl(i),
g_other_oprt_count_fnl_tbl(i),
g_null_other_oprt_cnt_fnl_tbl(i),
g_pte_code_fnl_tbl(i),
g_prc_brk_typ_code_fnl_tbl(i),
g_list_line_typ_code_fnl_tbl(i),
g_automatic_flag_fnl_tbl(i) FROM dual WHERE g_pricing_phase_id_final_tbl(i) <> 1 ;
PROCEDURE update_list_lines_cache_key
is
BEGIN
FORALL i in 1 .. g_list_line_id_final_tbl.count
UPDATE qp_list_lines
set cache_key = g_cache_key_final_tbl(i),
last_update_date = g_last_update_date_final_tbl(i),
last_updated_by = g_last_updated_by_final_tbl(i),
last_update_login = g_last_update_login_final_tbl(i)
where list_line_id = g_list_line_id_final_tbl(i)
AND pricing_phase_id = 1;
oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_List_Lines_Cache_Key ' || SQLERRM);
write_log( 'PS_ATTR_GRP_PVT.Update_List_Lines_Cache_Key ' || SQLERRM );
end update_list_lines_cache_key;
INSERT INTO qp_attribute_groups
(list_header_id,
list_line_id,
active_flag,
list_type_code,
start_date_active_q,
end_date_active_q,
pattern_id,
header_quals_exist_flag,
orig_org_id,
global_flag,
product_uom_code,
currency_code,
ask_for_flag,
header_limit_exists,
line_limit_exists,
source_system_code,
effective_precedence,
grouping_no,
pricing_phase_id,
modifier_level_code,
hash_key,
cache_key,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
eq_flag,
descendents_quals_exist,
start_date_active_first,
end_date_active_first,
start_date_active_second,
end_date_active_second,
start_date_active_h,
end_date_active_h,
start_date_active_l,
end_date_active_l,
active_date_first_type,
active_date_second_type,
currency_header_id,
other_oprt_count,
null_other_oprt_count,
pte_code,
price_break_type_code,
list_line_type_code,
automatic_flag
)
VALUES
(g_list_header_id_final_tbl(i),
g_list_line_id_final_tbl(i),
g_active_flag_final_tbl(i),
g_list_type_code_final_tbl(i),
g_st_date_active_q_final_tbl(i),
g_end_date_active_q_final_tbl(i),
g_pattern_id_final_tbl(i),
----------- Added for PL/SQL Pattern Search
g_header_quals_exist_fnl_tbl(i),
g_orig_org_id_fnl_tbl(i),
g_global_flag_fnl_tbl(i),
g_product_uom_code_final_tbl(i),
----------- Added for PL/SQL Pattern Search
g_currency_code_final_tbl(i),
g_ask_for_flag_final_tbl(i),
g_header_limit_exists_fnl_tbl(i),
g_line_limit_exists_fnl_tbl(i),
g_source_system_code_final_tbl(i),
g_effec_precedence_final_tbl(i),
g_qual_grouping_no_final_tbl(i),
g_pricing_phase_id_final_tbl(i),
g_modifier_lvl_code_final_tbl(i),
g_hash_key_final_tbl(i),
g_cache_key_final_tbl(i),
g_creation_date_final_tbl(i),
g_created_by_final_tbl(i),
g_last_update_date_final_tbl(i),
g_last_updated_by_final_tbl(i),
g_last_update_login_final_tbl(i),
g_program_appl_id_final_tbl(i),
g_program_id_final_tbl(i),
g_program_upd_date_final_tbl(i),
g_request_id_final_tbl(i),
g_eq_flag_fnl_tbl(i) ,
g_desc_quals_exist_fnl_tbl(i),
g_start_date_act_firs_fnl_tbl(i),
g_end_date_act_firs_fnl_tbl(i),
g_start_date_act_sec_fnl_tbl(i),
g_end_date_act_sec_fnl_tbl(i),
g_start_date_act_h_fnl_tbl(i),
g_end_date_act_h_fnl_tbl(i),
g_start_date_act_l_fnl_tbl(i),
g_end_date_act_l_fnl_tbl(i),
g_act_date_first_type_fnl_tbl(i),
g_act_date_sec_type_fnl_tbl(i),
g_currency_header_id_fnl_tbl(i),
g_other_oprt_count_fnl_tbl(i),
g_null_other_oprt_cnt_fnl_tbl(i),
g_pte_code_fnl_tbl(i),
g_prc_brk_typ_code_fnl_tbl(i),
g_list_line_typ_code_fnl_tbl(i),
g_automatic_flag_fnl_tbl(i)
);
insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
(select /*+ index(QP_LIST_LINES QP_LIST_LINES_U1) */ distinct list_header_id, pricing_phase_id,'Y'
from qp_list_lines
where pricing_phase_id > 1
and qualification_ind in (0,4,20)
and list_header_id = g_list_header_id_final_tbl(i)
MINUS
SELECT distinct list_header_id, pricing_phase_id,'Y'
FROM qp_list_header_phases
where list_header_id = g_list_header_id_final_tbl(i)
);
g_list_header_id_tmp_tbl.delete;
g_list_line_id_tmp_tbl.delete;
g_active_flag_tmp_tbl.delete;
g_list_type_code_tmp_tbl.delete;
g_start_date_active_q_tmp_tbl.delete;
g_end_date_active_q_tmp_tbl.delete;
g_header_quals_exist_tmp_tbl.delete;
g_orig_org_id_tmp_tbl.delete;
g_global_flag_tmp_tbl.delete;
g_product_uom_code_j_tmp_tbl.delete;
g_product_uom_code_tmp_tbl.delete;
g_creation_date_tmp_tbl.delete;
g_created_by_tmp_tbl.delete;
g_request_id_tmp_tbl.delete;
g_program_update_date_tmp_tbl.delete;
g_program_id_tmp_tbl.delete;
g_program_applic_id_tmp_tbl.delete;
g_start_date_act_firs_tmp_tbl.delete;
g_end_date_act_firs_tmp_tbl.delete;
g_start_date_act_sec_tmp_tbl.delete;
g_end_date_act_sec_tmp_tbl.delete;
g_start_date_act_h_tmp_tbl.delete;
g_end_date_act_h_tmp_tbl.delete;
g_start_date_act_l_tmp_tbl.delete;
g_end_date_act_l_tmp_tbl.delete;
g_eq_flag_tmp_tbl.delete;
g_act_date_firs_type_tmp_tbl.delete;
g_act_date_sec_type_tmp_tbl.delete;
g_currency_header_id_tmp_tbl.delete;
g_pte_code_tmp_tbl.delete;
g_desc_quals_exist_tmp_tbl.delete;
g_prc_brk_typ_code_tmp_tbl.delete;
g_list_line_typ_code_tmp_tbl.delete;
g_automatic_flag_tmp_tbl.delete;
g_currency_code_tmp_tbl.delete;
g_ask_for_flag_tmp_tbl.delete;
g_header_limit_exists_tmp_tbl.delete;
g_line_limit_exists_tmp_tbl.delete;
g_source_system_code_tmp_tbl.delete;
g_effective_precedence_tmp_tbl.delete;
g_qual_grouping_no_tmp_tbl.delete;
g_pricing_phase_id_tmp_tbl.delete;
g_modifier_level_code_tmp_tbl.delete;
g_hash_key_tmp_tbl.delete;
g_cache_key_tmp_tbl.delete;
g_pat_string_tmp_tbl.delete;
g_pattern_grouping_no_tmp_tbl.delete;
g_pattern_segment_id_tmp_tbl.delete;
g_product_uom_code_tmp_tbl.delete;
g_pricing_attr_count_tmp_tbl.delete;
g_other_oprt_count_tmp_tbl.delete;
g_null_other_oprt_cnt_tmp_tbl.delete;
select /*+ index(qp_pat QP_PATTERNS_N1) */ pattern_id
into l_pattern_id
from qp_patterns qp_pat
where pattern_string = p_pat_string
-- and pattern_type = p_pattern_type
and rownum = 1;
select qp_patterns_s.nextval into l_pattern_id from dual;
g_list_header_id_final_tbl.delete;
g_list_line_id_final_tbl.delete;
g_active_flag_final_tbl.delete;
g_list_type_code_final_tbl.delete;
g_st_date_active_q_final_tbl.delete;
g_end_date_active_q_final_tbl.delete;
g_pattern_id_final_tbl.delete;
g_currency_code_final_tbl.delete;
g_header_quals_exist_fnl_tbl.delete;
g_orig_org_id_fnl_tbl.delete;
g_global_flag_fnl_tbl.delete;
g_product_uom_code_j_fnl_tbl.delete;
g_request_id_fnl_tbl.delete;
g_program_update_date_fnl_tbl.delete;
g_program_id_fnl_tbl.delete;
g_program_applic_id_fnl_tbl.delete;
g_start_date_act_firs_fnl_tbl.delete;
g_end_date_act_firs_fnl_tbl.delete;
g_start_date_act_sec_fnl_tbl.delete;
g_end_date_act_sec_fnl_tbl.delete;
g_start_date_act_h_fnl_tbl.delete;
g_end_date_act_h_fnl_tbl.delete;
g_start_date_act_l_fnl_tbl.delete;
g_end_date_act_l_fnl_tbl.delete;
g_eq_flag_fnl_tbl.delete;
g_act_date_first_type_fnl_tbl.delete;
g_act_date_sec_type_fnl_tbl.delete;
g_currency_header_id_fnl_tbl.delete;
g_pte_code_fnl_tbl.delete;
g_desc_quals_exist_fnl_tbl.delete;
g_prc_brk_typ_code_fnl_tbl.delete;
g_list_line_typ_code_fnl_tbl.delete;
g_automatic_flag_fnl_tbl.delete;
g_ask_for_flag_final_tbl.delete;
g_header_limit_exists_fnl_tbl.delete;
g_line_limit_exists_fnl_tbl.delete;
g_source_system_code_final_tbl.delete;
g_effec_precedence_final_tbl.delete;
g_qual_grouping_no_final_tbl.delete;
g_pricing_phase_id_final_tbl.delete;
g_modifier_lvl_code_final_tbl.delete;
g_hash_key_final_tbl.delete;
g_cache_key_final_tbl.delete;
g_product_uom_code_final_tbl.delete;
g_pricing_attr_count_final_tbl.delete;
g_creation_date_final_tbl.delete;
g_created_by_final_tbl.delete;
g_last_update_date_final_tbl.delete;
g_last_updated_by_final_tbl.delete;
g_last_update_login_final_tbl.delete;
g_program_appl_id_final_tbl.delete;
g_program_id_final_tbl.delete;
g_program_upd_date_final_tbl.delete;
g_request_id_final_tbl.delete;
g_null_other_oprt_cnt_fnl_tbl.delete;
g_other_oprt_count_fnl_tbl.delete;
g_list_header_id_c_tbl.delete;
g_list_line_id_c_tbl.delete;
g_segment_id_c_tbl.delete;
g_active_flag_c_tbl.delete;
g_list_type_code_c_tbl.delete;
g_start_date_active_q_c_tbl.delete;
g_end_date_active_q_c_tbl.delete;
g_header_quals_exist_c_tbl.delete;
g_orig_org_id_c_tbl.delete;
g_global_flag_c_tbl.delete;
g_product_uom_code_j_c_tbl.delete;
g_creation_date_c_tbl.delete;
g_created_by_c_tbl.delete;
g_request_id_c_tbl.delete;
g_program_update_date_c_tbl.delete;
g_program_id_c_tbl.delete;
g_program_applic_id_c_tbl.delete;
g_start_date_act_firs_c_tbl.delete;
g_end_date_act_firs_c_tbl.delete;
g_start_date_act_sec_c_tbl.delete;
g_end_date_act_sec_c_tbl.delete;
g_start_date_act_h_c_tbl.delete;
g_end_date_act_h_c_tbl.delete;
g_start_date_act_l_c_tbl.delete;
g_end_date_act_l_c_tbl.delete;
g_act_date_firs_type_c_tbl.delete;
g_act_date_sec_type_c_tbl.delete;
g_currency_header_id_c_tbl.delete;
g_pte_code_c_tbl.delete;
g_desc_quals_exist_c_tbl.delete;
g_prc_brk_typ_code_c_tbl.delete;
g_list_line_typ_code_c_tbl.delete;
g_automatic_flag_c_tbl.delete;
g_currency_code_c_tbl.delete;
g_ask_for_flag_c_tbl.delete;
g_header_limit_exists_c_tbl.delete;
g_line_limit_exists_c_tbl.delete;
g_source_system_code_c_tbl.delete;
g_effective_precedence_c_tbl.delete;
g_qual_grouping_no_c_tbl.delete;
g_comparison_opr_code_c_tbl.delete;
g_pricing_phase_id_c_tbl.delete;
g_modifier_level_code_c_tbl.delete;
g_qual_datatype_c_tbl.delete;
g_qual_attr_val_c_tbl.delete;
g_attribute_type_c_tbl.delete;
g_product_uom_code_c_tbl.delete;
CURSOR l_phase_id_to_insert_csr IS
SELECT distinct pricing_phase_id, list_header_id
FROM qp_list_header_phases
WHERE list_header_id = p_list_header_id;
FOR j IN l_phase_id_to_insert_csr LOOP
l_prl_flag := 'Y';
select /*+ index(qp_pp QP_PATTERN_PHASES_N1) */ 'Y'
into l_exists
from qp_pattern_phases qp_pp
where pattern_id = p_pattern_id
and pricing_phase_id = j.pricing_phase_id;
INSERT INTO qp_pattern_phases
(pattern_id,
pricing_phase_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id
)
VALUES
(p_pattern_id,
j.pricing_phase_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
sysdate,
FND_GLOBAL.CONC_REQUEST_ID
);
END LOOP; --j IN l_phase_id_to_insert_csr
select list_type_code into l_list_type
from qp_list_headers_all_b
where list_header_id = p_list_header_id;
select /*+ index(qp_pp QP_PATTERN_PHASES_N1) */ 'Y'
into l_exists
from qp_pattern_phases qp_pp
where pattern_id = p_pattern_id
and pricing_phase_id = 1;
INSERT INTO qp_pattern_phases
(pattern_id,
pricing_phase_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id
)
VALUES
(p_pattern_id,
1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
sysdate,
FND_GLOBAL.CONC_REQUEST_ID
);
select /*+ index(qp_pp QP_PATTERN_PHASES_N1) */ 'Y'
into l_exists
from qp_pattern_phases qp_pp
where pattern_id = p_pattern_id
and pricing_phase_id = p_pricing_phase_id
and rownum = 1; -- needed in case same combination is inserted by 2 diff. threads and one has commited before other
oe_debug_pub.add('No pattern_phases found; go insert');
write_log( 'No pattern_phases found; go insert');
INSERT INTO qp_pattern_phases
(pattern_id,
pricing_phase_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id
)
VALUES
(p_pattern_id,
p_pricing_phase_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
sysdate,
FND_GLOBAL.CONC_REQUEST_ID
);
oe_debug_pub.add('Insert failure:'||sqlerrm);
write_log( 'Insert failure:'||sqlerrm);
SELECT list_type_code INTO g_list_type FROM qp_list_headers WHERE list_header_id = p_list_header_id;
delete from qp_attribute_groups
where list_header_id = p_list_header_id
and list_line_id = -1;
delete from qp_attribute_groups
where list_header_id = p_list_header_id
and list_line_id = -1
and GROUPING_NO in (-1, p_qualifier_group);
Update_Qual_Segment_id(p_list_header_id, p_qualifier_group, -1, -1);
select 'Y'
into l_qual_exists
from qp_qualifiers
where list_header_id = p_list_header_id
and list_line_id = -1
and ((list_type_code = 'PRL'
AND QUALIFIER_CONTEXT <> 'MODLIST'
AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4')
OR
(list_type_code <> 'PRL')
)
and QUALIFIER_GROUPING_NO = p_qualifier_group
and rownum = 1;
select 'Y'
into l_qual_exists
from qp_qualifiers
where list_header_id = p_list_header_id
and list_line_id = -1
and ((list_type_code = 'PRL'
AND QUALIFIER_CONTEXT <> 'MODLIST'
AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4')
OR
(list_type_code <> 'PRL')
)
and QUALIFIER_GROUPING_NO <> -1
and rownum = 1;
select ACTIVE_FLAG,
LIST_TYPE_CODE,
CURRENCY_CODE,
ASK_FOR_FLAG,
LIMIT_EXISTS_FLAG,
SOURCE_SYSTEM_CODE,
END_DATE_ACTIVE,
START_DATE_ACTIVE,
START_DATE_ACTIVE_FIRST,
END_DATE_ACTIVE_FIRST,
ACTIVE_DATE_FIRST_TYPE,
START_DATE_ACTIVE_SECOND,
END_DATE_ACTIVE_SECOND,
ACTIVE_DATE_SECOND_TYPE,
GLOBAL_FLAG
into l_ACTIVE_FLAG,
l_LIST_TYPE_CODE,
l_CURRENCY_CODE,
l_ASK_FOR_FLAG,
l_HEADER_LIMIT_EXISTS,
l_SOURCE_SYSTEM_CODE,
l_END_DATE_ACTIVE,
l_START_DATE_ACTIVE,
l_START_DATE_ACTIVE_FIRST,
l_END_DATE_ACTIVE_FIRST,
l_ACTIVE_DATE_FIRST_TYPE,
l_START_DATE_ACTIVE_SECOND,
l_END_DATE_ACTIVE_SECOND,
l_ACTIVE_DATE_SECOND_TYPE,
l_GLOBAL_FLAG
from qp_list_headers_all_b
where list_header_id = p_list_header_id;
update qp_attribute_groups
set ACTIVE_FLAG = l_ACTIVE_FLAG,
LIST_TYPE_CODE = l_LIST_TYPE_CODE,
CURRENCY_CODE = l_CURRENCY_CODE,
ASK_FOR_FLAG = l_ASK_FOR_FLAG,
HEADER_LIMIT_EXISTS = l_HEADER_LIMIT_EXISTS,
SOURCE_SYSTEM_CODE = l_SOURCE_SYSTEM_CODE,
END_DATE_ACTIVE_H = l_END_DATE_ACTIVE,
START_DATE_ACTIVE_H = l_START_DATE_ACTIVE,
START_DATE_ACTIVE_FIRST = l_START_DATE_ACTIVE_FIRST,
END_DATE_ACTIVE_FIRST = l_END_DATE_ACTIVE_FIRST,
ACTIVE_DATE_FIRST_TYPE = l_ACTIVE_DATE_FIRST_TYPE,
START_DATE_ACTIVE_SECOND = l_START_DATE_ACTIVE_SECOND,
END_DATE_ACTIVE_SECOND = l_END_DATE_ACTIVE_SECOND,
ACTIVE_DATE_SECOND_TYPE = l_ACTIVE_DATE_SECOND_TYPE,
GLOBAL_FLAG = l_GLOBAL_FLAG
where list_header_id = p_list_header_id;
/* update qp_attribute_groups
set ACTIVE_FLAG = l_ACTIVE_FLAG,
LIST_TYPE_CODE = l_LIST_TYPE_CODE,
CURRENCY_CODE = l_CURRENCY_CODE,
ASK_FOR_FLAG = l_ASK_FOR_FLAG,
SOURCE_SYSTEM_CODE = l_SOURCE_SYSTEM_CODE
where list_header_id = p_list_header_id
and list_line_id <> -1;*/
select 'Y'
into l_qual_exists
from qp_qualifiers
where list_header_id = p_list_header_id
and list_line_id = -1
and ((list_type_code = 'PRL'
AND QUALIFIER_CONTEXT <> 'MODLIST'
AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4')
OR
(list_type_code <> 'PRL')
)
and rownum = 1;
update qp_attribute_groups
set HEADER_QUALS_EXIST_FLAG = l_QUAL_EXISTS
where list_header_id = p_list_header_id;
/* update qp_pte_segments
set used_in_search ='Y'
where NVL(used_in_search,'N') ='N'
and segment_id in
( select DISTINCT segment_id
from qp_patterns );
update qp_pte_segments
set used_in_search ='N'
where NVL(used_in_search,'Y') ='Y'
and segment_id not in
( select DISTINCT segment_id
from qp_patterns ); */
UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
SET used_in_search = 'Y'
WHERE NVL(used_in_search,'N') = 'N'
AND segment_id in
(select pricing_segment_id
from qp_pricing_attributes
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */list_header_id = nvl(p_list_header_id, list_header_id)
--AND comparison_operator_code <> '='
and pricing_segment_id is not null
UNION all
select segment_id
from qp_qualifiers
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */list_header_id = nvl(p_list_header_id, list_header_id)
--AND comparison_operator_code <> '='
and segment_id is not null);
update_pattern_phases(p_list_header_id,null,null);
update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
SELECT list_type_code INTO g_list_type FROM qp_list_headers WHERE list_header_id = p_list_header_id;
delete from qp_attribute_groups
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id;
delete from qp_attribute_groups
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and GROUPING_NO in (-1, p_qualifier_group);
Update_Qual_Segment_id(p_list_header_id, p_qualifier_group, p_list_line_id, p_list_line_id);
Update_Prod_Pric_Segment_id(p_list_header_id, p_list_line_id, p_list_line_id );
update qp_list_lines
set pattern_id = DECODE(qualification_ind,0,-2,-3),
hash_key = null,
cache_key = null
where list_line_id = p_list_line_id
and qualification_ind in (0, 2);
select 'Y'
into l_qual_exists
from qp_qualifiers
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and QUALIFIER_GROUPING_NO = p_qualifier_group
and rownum = 1;
update qp_list_lines
set pattern_id = DECODE(qualification_ind,0,-2,-3),
hash_key = null,
cache_key = null
where list_line_id = p_list_line_id
and qualification_ind in (0, 2);
select 'Y'
into l_qual_exists
from qp_qualifiers
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and QUALIFIER_GROUPING_NO <> -1
and rownum = 1;
update qp_list_lines
set pattern_id = DECODE(qualification_ind,0,-2,-3),
hash_key = null,
cache_key = null
where list_line_id = p_list_line_id
and qualification_ind in (0, 2);
select LIMIT_EXISTS_FLAG
into l_line_LIMIT_EXISTS
from qp_list_lines
where list_line_id = p_list_line_id;
update qp_attribute_groups
set LINE_LIMIT_EXISTS = l_line_LIMIT_EXISTS
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id;
select 'Y'
into l_qual_exists
from qp_attribute_groups
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and rownum = 1;
update /*+ index(lines QP_LIST_LINES_PK) */ /*qp_list_lines
set pattern_id = null,
pricing_attribute_count = null,
product_uom_code = null,
hash_key = null
where list_line_id = p_list_line_id
and pattern_id is not null;
update_pp_lines(p_list_header_id, p_list_line_id, p_list_line_id);
/* update qp_pte_segments
set used_in_search ='Y'
where NVL(used_in_search,'N') ='N'
and segment_id in
(select DISTINCT segment_id
from qp_patterns );
update qp_pte_segments
set used_in_search ='N'
where NVL(used_in_search,'Y') ='Y'
and segment_id not in
(select DISTINCT segment_id
from qp_patterns );*/
UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
SET used_in_search = 'Y'
WHERE NVL(used_in_search,'N') = 'N'
AND segment_id in
(select pricing_segment_id
from qp_pricing_attributes
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */list_header_id = nvl(p_list_header_id, list_header_id)
--AND comparison_operator_code <> '='
and pricing_segment_id is not null
UNION all
select segment_id
from qp_qualifiers
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */list_header_id = nvl(p_list_header_id, list_header_id)
--AND comparison_operator_code <> '='
and segment_id is not null);
update_pattern_phases(p_list_header_id,p_list_line_id,p_list_line_id);
update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
select qualification_ind
into l_qual_ind
from qp_list_lines
where list_line_id = p_list_line_id;
SELECT list_type_code INTO g_list_type FROM qp_list_headers WHERE list_header_id = p_list_header_id;
select product_uom_code
into l_product_uom_code
from qp_pricing_attributes
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and product_uom_code is not null
and rownum = 1;
update /*+ index(lines QP_LIST_LINES_PK) */ qp_list_lines
set product_uom_code = l_product_uom_code
where list_line_id = p_list_line_id;
select 'Y'
into l_qual_exists
from qp_qualifiers
where list_header_id = p_list_header_id
and list_line_id = p_list_line_id
and rownum = 1;
Update_Prod_Pric_Segment_id(p_list_header_id, p_list_line_id,
p_list_line_id);
update_pp_lines(p_list_header_id, p_list_line_id, p_list_line_id);
delete from qp_attribute_groups
where list_header_id = p_list_header_id
AND list_line_id = -1;
/* update qp_pte_segments
set used_in_search ='Y'
where NVL(used_in_search,'N') ='N'
and segment_id in
(select DISTINCT segment_id
from qp_patterns );
update qp_pte_segments
set used_in_search ='N'
where NVL(used_in_search,'Y') ='Y'
and segment_id not in
( select DISTINCT segment_id
from qp_patterns );*/
UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
SET used_in_search = 'Y'
WHERE NVL(used_in_search,'N') = 'N'
AND segment_id in
(select pricing_segment_id
from qp_pricing_attributes
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */list_header_id = nvl(p_list_header_id, list_header_id)
--AND comparison_operator_code <> '='
and pricing_segment_id is not null
UNION all
select segment_id
from qp_qualifiers
where /*list_line_id in (
select list_line_id from qp_attribute_groups where eq_flag = 'N'
UNION
select list_line_id from qp_list_lines where eq_flag = 'N')
and */list_header_id = nvl(p_list_header_id, list_header_id)
--AND comparison_operator_code <> '='
and segment_id is not null);
update_pattern_phases(p_list_header_id,p_list_line_id,p_list_line_id);
update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
/* update /*+ index(lines QP_LIST_LINES_PK) */ /*qp_list_lines
set pattern_id = null,
pricing_attribute_count = null,
product_uom_code = null,
hash_key = null,
cache_key = null
where list_line_id = p_list_line_id
and pattern_id is not null;*/
delete from qp_attribute_groups
where list_line_id = p_list_line_id;
oe_debug_pub.add('Deleted records from qp_attribute_groups for PP for list_line_id:'||p_list_line_id);
write_log( 'Deleted records from qp_attribute_groups for PP for list_line_id:'||p_list_line_id);
procedure Update_Qual_Segment_id(p_list_header_id IN NUMBER
,p_qualifier_group IN NUMBER
,p_low_list_line_id IN NUMBER
,p_high_list_line_id IN NUMBER)
is
cursor c_qual_seg_id is
select distinct QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE
from qp_qualifiers
where QUALIFIER_CONTEXT is not null
and QUALIFIER_ATTRIBUTE is not null
and list_header_id = p_list_header_id
and list_line_id between p_low_list_line_id and p_high_list_line_id
and ((p_qualifier_group is not null and qualifier_grouping_no in (-1, p_qualifier_group))
OR
(p_qualifier_group is null)
);
select distinct QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE
from qp_qualifiers
where QUALIFIER_CONTEXT is not null
and QUALIFIER_ATTRIBUTE is not null
and list_line_id between p_low_list_line_id and p_high_list_line_id
and ((p_qualifier_group is not null and qualifier_grouping_no in (-1, p_qualifier_group))
OR
(p_qualifier_group is null)
);
oe_debug_pub.add('Inside Update_Qual_Segment_id');
write_log( 'Inside Update_Qual_Segment_id');
segment_id_t.delete;
context_t.delete;
attribute_t.delete;
select b.segment_id
into segment_id_t(i)
from qp_prc_contexts_b a, qp_segments_b b
where b.prc_context_id = a.prc_context_id
and a.PRC_CONTEXT_CODE = context_t(i)
and b.SEGMENT_MAPPING_COLUMN = attribute_t(i);
update qp_qualifiers
set segment_id = segment_id_t(j)
where QUALIFIER_CONTEXT = context_t(j)
and QUALIFIER_ATTRIBUTE = attribute_t(j)
and list_header_id = nvl(p_list_header_id, list_header_id)
and list_line_id between p_low_list_line_id and p_high_list_line_id;
oe_debug_pub.add('No of qualifiers updated='||SQL%ROWCOUNT);
write_log( 'No of qualifiers updated='||SQL%ROWCOUNT);
oe_debug_pub.add('End Update_Qual_Segment_id');
write_log( 'End Update_Qual_Segment_id');
oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_Qual_Segment_Id ' || SQLERRM);
write_log( 'PS_ATTR_GRP_PVT.Update_Qual_Segment_Id ' || SQLERRM );
end Update_Qual_Segment_id;
procedure Update_Prod_Pric_Segment_id(p_list_header_id IN NUMBER
-- ,p_list_line_id IN NUMBER
,p_low_list_line_id IN NUMBER
,p_high_list_line_id IN NUMBER)
is
cursor c_prod_seg_id is
select distinct PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE
from qp_pricing_attributes
where PRODUCT_ATTRIBUTE_CONTEXT is not null
and PRODUCT_ATTRIBUTE is not null
and list_header_id = p_list_header_id
and list_line_id between p_low_list_line_id and p_high_list_line_id;
select distinct PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE
from qp_pricing_attributes
where PRODUCT_ATTRIBUTE_CONTEXT is not null
and PRODUCT_ATTRIBUTE is not null
and list_line_id between p_low_list_line_id and p_high_list_line_id;
select distinct PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE
from qp_pricing_attributes
where PRICING_ATTRIBUTE_CONTEXT is not null
and PRICING_ATTRIBUTE is not null
and list_header_id = p_list_header_id
and list_line_id between p_low_list_line_id and p_high_list_line_id;
select distinct PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE
from qp_pricing_attributes
where PRICING_ATTRIBUTE_CONTEXT is not null
and PRICING_ATTRIBUTE is not null
and list_line_id between p_low_list_line_id and p_high_list_line_id;
oe_debug_pub.add('Inside Update_Prod_Pric_Segment_id');
write_log( 'Inside Update_Prod_Pric_Segment_id');
segment_id_t.delete;
context_t.delete;
attribute_t.delete;
select b.segment_id
into segment_id_t(i)
from qp_prc_contexts_b a, qp_segments_b b
where b.prc_context_id = a.prc_context_id
and a.PRC_CONTEXT_CODE = context_t(i)
and b.SEGMENT_MAPPING_COLUMN = attribute_t(i);
update qp_pricing_attributes
set product_segment_id = segment_id_t(j)
where PRODUCT_ATTRIBUTE_CONTEXT = context_t(j)
and PRODUCT_ATTRIBUTE = attribute_t(j)
and list_header_id = nvl(p_list_header_id, list_header_id)
and list_line_id between p_low_list_line_id and p_high_list_line_id;
oe_debug_pub.add('No of product segment ids updated='||SQL%ROWCOUNT);
write_log( 'No of product segment ids updated='||SQL%ROWCOUNT);
segment_id_t.delete;
context_t.delete;
attribute_t.delete;
select b.segment_id
into segment_id_t(i)
from qp_prc_contexts_b a, qp_segments_b b
where b.prc_context_id = a.prc_context_id
and a.PRC_CONTEXT_CODE = context_t(i)
and b.SEGMENT_MAPPING_COLUMN = attribute_t(i);
update qp_pricing_attributes
set pricing_segment_id = segment_id_t(j)
where PRICING_ATTRIBUTE_CONTEXT = context_t(j)
and PRICING_ATTRIBUTE = attribute_t(j)
and list_header_id = nvl(p_list_header_id, list_header_id)
and list_line_id between p_low_list_line_id and p_high_list_line_id;
oe_debug_pub.add('No of pricing segment ids updated='||SQL%ROWCOUNT);
write_log( 'No of pricing segment ids updated='||SQL%ROWCOUNT);
oe_debug_pub.add('End Update_Prod_Pric_Segment_id');
write_log( 'End Update_Prod_Pric_Segment_id');
oe_debug_pub.add('No data found in Update_Prod_Pric_Segment_id');
write_log( 'No data found in Update_Prod_Pric_Segment_id');
oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id ' || SQLERRM);
write_log( 'PS_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id ' || SQLERRM );
end Update_Prod_Pric_Segment_id;