[Home] [Help]
552: l_min_pattern_id number;
553:
554: cursor c_dupl_pattern is
555: select distinct b.pattern_type, b.pattern_string, b.pattern_id
556: from qp_patterns a, qp_patterns b
557: where a.pattern_type = b.pattern_type
558: and a.pattern_string = b.pattern_string
559: and a.pattern_id <> b.pattern_id
560: and b.pattern_id >(select min(c.pattern_id)
557: where a.pattern_type = b.pattern_type
558: and a.pattern_string = b.pattern_string
559: and a.pattern_id <> b.pattern_id
560: and b.pattern_id >(select min(c.pattern_id)
561: from qp_patterns c
562: where c.pattern_type = a.pattern_type
563: and c.pattern_string = a.pattern_string);
564: begin
565:
583: for i in 1..g_pattern_pattern_id_final_tbl.count
584: loop
585: select min(c.pattern_id)
586: into l_min_pattern_id
587: from qp_patterns c
588: where c.pattern_type = g_pattern_pat_type_final_tbl(i)
589: and c.pattern_string = g_pattern_pat_string_final_tbl(i);
590: IF g_qp_pattern_search = 'M' THEN
591: update qp_attribute_groups
615: end loop;
616: end if;
617:
618: FORALL i in 1 .. G_pattern_pattern_id_final_tbl.count
619: DELETE qp_patterns
620: where pattern_id = g_pattern_pattern_id_final_tbl(i);
621:
622: g_pattern_pattern_id_final_tbl.delete;
623: g_pattern_pat_type_final_tbl.delete;
758: set used_in_search ='Y'
759: where NVL(used_in_search,'N') ='N'
760: and segment_id in
761: ( select DISTINCT segment_id
762: from qp_patterns );
763: update qp_pte_segments
764: set used_in_search ='N'
765: where NVL(used_in_search,'Y') ='Y'
766: and segment_id not in
764: set used_in_search ='N'
765: where NVL(used_in_search,'Y') ='Y'
766: and segment_id not in
767: ( select DISTINCT segment_id
768: from qp_patterns );
769: UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
770: SET used_in_search = 'Y'
771: WHERE NVL(used_in_search,'N') = 'N'
772: AND segment_id in
818: --AND comparison_operator_code <> '='
819: and segment_id is not null);
820: END IF;
821: update_pattern_phases(p_list_header_id,p_low_list_line_id,p_high_list_line_id);
822: update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
823: /*Populate row in qp_list_heder_phases when no qualifer is
824: present for lines or headers for that phase and will poulate 'Y' for PRIC_PROD_ATTR_ONLY_FLAG*/
825: IF p_list_header_id IS NULL THEN
826: insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
884: l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.qp_pattern_phases';
885: EXECUTE IMMEDIATE l_stmt;
886: l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.qp_attribute_groups';
887: EXECUTE IMMEDIATE l_stmt;
888: l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.qp_patterns';
889: EXECUTE IMMEDIATE l_stmt;
890:
891: END IF;
892:
3378: end if;
3379:
3380: END Move_data_from_tmp_to_final;
3381:
3382: -- bulk insert patterns into qp_patterns table
3383: PROCEDURE Populate_Patterns
3384: is
3385: BEGIN
3386:
3392: end if;
3393: */
3394:
3395: FORALL i in 1 .. G_pattern_pattern_id_final_tbl.count
3396: INSERT INTO qp_patterns
3397: (
3398: pattern_id,
3399: segment_id,
3400: segment_index,
3652: end if;
3653:
3654: end update_list_lines_cache_key;
3655:
3656: -- bulk insert patterns into qp_patterns table
3657: PROCEDURE Populate_Atgrps
3658: is
3659: BEGIN
3660:
3878: end if;
3879: */
3880: begin
3881: l_pattern_to_be_created := 'N';
3882: select /*+ index(qp_pat QP_PATTERNS_N1) */ pattern_id
3883: into l_pattern_id
3884: from qp_patterns qp_pat
3885: where pattern_string = p_pat_string
3886: -- and pattern_type = p_pattern_type
3880: begin
3881: l_pattern_to_be_created := 'N';
3882: select /*+ index(qp_pat QP_PATTERNS_N1) */ pattern_id
3883: into l_pattern_id
3884: from qp_patterns qp_pat
3885: where pattern_string = p_pat_string
3886: -- and pattern_type = p_pattern_type
3887: and rownum = 1;
3888: exception
3886: -- and pattern_type = p_pattern_type
3887: and rownum = 1;
3888: exception
3889: when no_data_found then
3890: select qp_patterns_s.nextval into l_pattern_id from dual;
3891: l_pattern_to_be_created := 'Y';
3892:
3893: when others then
3894: raise;
3901: write_log( 'l_pattern_to_be_created='||l_pattern_to_be_created);
3902: end if;
3903: */
3904:
3905: -- move the data from temp tables to final tables for qp_patterns, if new pattern to be created
3906: if l_pattern_to_be_created = 'Y' then
3907: for k in 1..g_pattern_grouping_no_tmp_tbl.count
3908: loop
3909: if (g_pattern_grouping_no_tmp_tbl(k) = -1 or g_pattern_grouping_no_tmp_tbl(k) = p_grp_no) then
4527: set used_in_search ='Y'
4528: where NVL(used_in_search,'N') ='N'
4529: and segment_id in
4530: ( select DISTINCT segment_id
4531: from qp_patterns );
4532:
4533:
4534: update qp_pte_segments
4535: set used_in_search ='N'
4535: set used_in_search ='N'
4536: where NVL(used_in_search,'Y') ='Y'
4537: and segment_id not in
4538: ( select DISTINCT segment_id
4539: from qp_patterns ); */
4540:
4541: UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
4542: SET used_in_search = 'Y'
4543: WHERE NVL(used_in_search,'N') = 'N'
4564:
4565:
4566:
4567: update_pattern_phases(p_list_header_id,null,null);
4568: update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
4569:
4570:
4571: EXCEPTION
4572: WHEN OTHERS THEN
4777: set used_in_search ='Y'
4778: where NVL(used_in_search,'N') ='N'
4779: and segment_id in
4780: (select DISTINCT segment_id
4781: from qp_patterns );
4782:
4783:
4784: update qp_pte_segments
4785: set used_in_search ='N'
4785: set used_in_search ='N'
4786: where NVL(used_in_search,'Y') ='Y'
4787: and segment_id not in
4788: (select DISTINCT segment_id
4789: from qp_patterns );*/
4790:
4791: UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
4792: SET used_in_search = 'Y'
4793: WHERE NVL(used_in_search,'N') = 'N'
4812: --AND comparison_operator_code <> '='
4813: and segment_id is not null);
4814:
4815: update_pattern_phases(p_list_header_id,p_list_line_id,p_list_line_id);
4816: update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
4817:
4818:
4819: --------- Added for PL/SQL Pattern Search
4820:
4943: set used_in_search ='Y'
4944: where NVL(used_in_search,'N') ='N'
4945: and segment_id in
4946: (select DISTINCT segment_id
4947: from qp_patterns );
4948:
4949:
4950: update qp_pte_segments
4951: set used_in_search ='N'
4951: set used_in_search ='N'
4952: where NVL(used_in_search,'Y') ='Y'
4953: and segment_id not in
4954: ( select DISTINCT segment_id
4955: from qp_patterns );*/
4956:
4957:
4958: UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
4959: SET used_in_search = 'Y'
4980: and segment_id is not null);
4981:
4982:
4983: update_pattern_phases(p_list_header_id,p_list_line_id,p_list_line_id);
4984: update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
4985:
4986: -------- Added for PL/SQL Pattern Search
4987:
4988: EXCEPTION