[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LINES_PER_INSERT CONSTANT NUMBER := 5000;
select list_line_id from qp_list_lines where qualification_ind in (8,10,12,14,28,30, 4,6,20,22)
and list_header_id = p_list_header_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 /*+ 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;
select sid into v_sid from v$session where audsid = userenv('SESSIONID');
select hsecs into l_start_time from v$timer;
fnd_file.put_line(FND_FILE.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;
fnd_file.put_line(FND_FILE.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);
select count(*)
into l_total_lines
from qp_list_lines
where qualification_ind in (8,10,12,14,28,30, 4,6,20,22)
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
where cache_key is not 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
and 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
and 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_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
and list_header_id = p_list_header_id
and list_line_id between p_low_list_line_id and p_high_list_line_id;
-- update the segment_id columns for qualifiers
Update_Qual_Segment_id(p_list_header_id, null,
p_low_list_line_id,
p_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,
p_low_list_line_id,
p_high_list_line_id);
update_pp_lines(p_list_header_id,
p_low_list_line_id,
p_high_list_line_id);
select hsecs into l_end_time from v$timer;
select qpq.list_header_id,
qpq.list_line_id,
qpq.segment_id,
qpq.active_flag,
qpq.list_type_code,
qpq.start_date_active start_date_active_q,
qpq.end_date_active end_date_active_q,
qph.currency_code,
qph.ask_for_flag,
qph.limit_exists_flag limit_exists,
qph.source_system_code,
qpq.qualifier_precedence effective_precedence,
qpq.qualifier_grouping_no,
qpq.comparison_operator_code,
-1 pricing_phase_id,
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_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')
)
order by qpq.list_header_id, qpq.list_line_id, qpq.segment_id;
select * from
(select /*+ ordered use_nl(qpq, qph) index(qpl qp_list_lines_pk) */ qpq.list_header_id,
qpq.list_line_id,
qpq.segment_id,
qpq.active_flag,
qpq.list_type_code,
qpq.start_date_active start_date_active_q,
qpq.end_date_active end_date_active_q,
qph.currency_code,
qph.ask_for_flag,
qpl.limit_exists_flag limit_exists,
qph.source_system_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_lines qpl, qp_qualifiers qpq, qp_list_headers_b qph
where qpq.list_line_id <> -1
and qph.list_header_id = qpq.list_header_id
and qpl.list_header_id = qph.list_header_id
and qpl.list_line_id = qpq.list_line_id
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)
)
union
select /*+ ordered use_nl(qpa, qph) index(qpl qp_list_lines_pk) */ 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,
qph.currency_code,
qph.ask_for_flag,
qpl.limit_exists_flag limit_exists,
qph.source_system_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_lines qpl, qp_pricing_attributes qpa, qp_list_headers_b qph
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.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
union
select /*+ ordered use_nl(qpa, qph) index(qpl qp_list_lines_pk) */ 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,
qph.currency_code,
qph.ask_for_flag,
qpl.limit_exists_flag limit_exists,
qph.source_system_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_lines qpl, qp_pricing_attributes qpa, qp_list_headers_b qph
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
) 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_pk) */ 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,
'PROD' attribute_type
from qp_list_lines qpl,
qp_pricing_attributes qpa
where 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
union
select /*+ ordered use_nl(qpa) index(qpl qp_list_lines_pk) */ 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,
'PRIC' attribute_type
from qp_list_lines qpl,
qp_pricing_attributes qpa
where 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
) attr_view
order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
oe_debug_pub.add('ATTR_GRP_PVT.Update_Pp_Lines ' || SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG, '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');
fnd_file.put_line(FND_FILE.LOG, 'inserting data for ' || G_LINES_PER_INSERT || ' lines');
update_list_lines_cache_key;
fnd_file.put_line(FND_FILE.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');
fnd_file.put_line(FND_FILE.LOG, 'inserting data for ' || G_LINES_PER_INSERT || ' lines');
update_list_lines;
fnd_file.put_line(FND_FILE.LOG, 'committing data for ' || G_LINES_PER_INSERT || ' lines');
update_list_lines;
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_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,
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_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;
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)
where list_line_id = g_list_line_id_final_tbl(i);
oe_debug_pub.add('ATTR_GRP_PVT.Update_List_Lines ' || SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Update_List_Lines ' || SQLERRM );
end update_list_lines;
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);
oe_debug_pub.add('ATTR_GRP_PVT.Update_List_Lines_Cache_Key ' || SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG, '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,
currency_code,
ask_for_flag,
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
)
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),
g_currency_code_final_tbl(i),
g_ask_for_flag_final_tbl(i),
g_limit_exists_final_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_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_currency_code_tmp_tbl.delete;
g_ask_for_flag_tmp_tbl.delete;
g_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;
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_ask_for_flag_final_tbl.delete;
g_limit_exists_final_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_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_currency_code_c_tbl.delete;
g_ask_for_flag_c_tbl.delete;
g_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
begin
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 /*+ 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');
fnd_file.put_line(FND_FILE.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);
fnd_file.put_line(FND_FILE.LOG, 'Insert failure:'||sqlerrm);
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
into l_ACTIVE_FLAG,
l_LIST_TYPE_CODE,
l_CURRENCY_CODE,
l_ASK_FOR_FLAG,
l_HEADER_LIMIT_EXISTS,
l_SOURCE_SYSTEM_CODE
from qp_list_headers_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,
LIMIT_EXISTS = l_HEADER_LIMIT_EXISTS,
SOURCE_SYSTEM_CODE = l_SOURCE_SYSTEM_CODE
where list_header_id = p_list_header_id
and list_line_id = -1;
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;
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 = null,
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 = null,
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 = null,
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 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 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);
select qualification_ind
into l_qual_ind
from qp_list_lines
where list_line_id = p_list_line_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 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);
update 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;
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 = nvl(p_list_header_id, 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)
);
oe_debug_pub.add('Inside Update_Qual_Segment_id');
fnd_file.put_line(FND_FILE.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);
fnd_file.put_line(FND_FILE.LOG, 'No of qualifiers updated='||SQL%ROWCOUNT);
oe_debug_pub.add('End Update_Qual_Segment_id');
fnd_file.put_line(FND_FILE.LOG, 'End Update_Qual_Segment_id');
oe_debug_pub.add('ATTR_GRP_PVT.Update_Qual_Segment_Id ' || SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG, '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 = nvl(p_list_header_id, 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_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('Inside Update_Prod_Pric_Segment_id');
fnd_file.put_line(FND_FILE.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);
fnd_file.put_line(FND_FILE.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);
fnd_file.put_line(FND_FILE.LOG, 'No of pricing segment ids updated='||SQL%ROWCOUNT);
oe_debug_pub.add('End Update_Prod_Pric_Segment_id');
fnd_file.put_line(FND_FILE.LOG, 'End Update_Prod_Pric_Segment_id');
oe_debug_pub.add('No data found in Update_Prod_Pric_Segment_id');
fnd_file.put_line(FND_FILE.LOG, 'No data found in Update_Prod_Pric_Segment_id');
oe_debug_pub.add('ATTR_GRP_PVT.Update_Prod_Pric_Segment_id ' || SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Update_Prod_Pric_Segment_id ' || SQLERRM );
end Update_Prod_Pric_Segment_id;