[Home] [Help]
42: -- 04/08/2002 EIHSU renamed a lot of stuff
43: -- 04/15/2002 SBEHERA added source_id and tran_id to update
44: -- jtf_terr_denorm_rules_all
45: -- 04/16/2002 SBEHERA added source_id and tran_id to delete
46: -- jtf_tae_qual_prod_factors
47: -- 05/03/2002 EIHSU set build_index_flag to 'N' if the index
48: -- does not have any columns in it
49: -- 03/08/2004 ACHANDA Fix bug # 3373687
50: -- 11/17/2004 ACHANDA Fix bug # 4009495
458:
459: BEGIN
460:
461: -- CLEAR AGGREGATE/COMPONENT RELATION KEY ALGORITHM STORAGE TABLES
462: DELETE FROM jtf_tae_qual_prod_factors
463: where qual_product_id in
464: (select qual_product_id from jtf_tae_qual_products
465: where source_id = l_source_id
466: and trans_object_type_id = l_trans_id);
655: from JTF_TAE_QUAL_factors
656: where qual_usg_id = qual_name.qual_usg_id
657: AND rownum < 2;
658:
659: SELECT JTF_TAE_QUAL_PROD_FACTORS_S.NEXTVAL
660: INTO l_qual_prod_factor_id
661: FROM dual;
662:
663: INSERT INTO JTF_TAE_QUAL_prod_factors
659: SELECT JTF_TAE_QUAL_PROD_FACTORS_S.NEXTVAL
660: INTO l_qual_prod_factor_id
661: FROM dual;
662:
663: INSERT INTO JTF_TAE_QUAL_prod_factors
664: ( QUAL_PROD_FACTOR_ID,
665: QUAL_PRODUCT_ID,
666: QUAL_FACTOR_ID,
667: LAST_UPDATE_DATE,
689:
690: EXCEPTION
691: WHEN NO_DATA_FOUND THEN
692: ERRBUF := 'JTF_TAE_CONTROL.Classify_Territories: [6] NO_DATA_FOUND: ' ||
693: 'Populating JTF_TAE_QUAL_PROD_FACTORS table.';
694: JTF_TAE_CONTROL_PVT.Write_Log(2, ERRBUF);
695: RAISE FND_API.G_EXC_ERROR;
696:
697: WHEN OTHERS THEN
801:
802: Cursor all_sets is
803: select p.trans_object_type_id, count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
804: from JTF_TAE_QUAL_products p,
805: JTF_TAE_QUAL_prod_factors pf,
806: JTF_TAE_QUAL_factors f
807: where p.qual_product_id = pf.qual_product_id
808: and pf.qual_factor_id = f.qual_factor_id
809: and f.tae_col_map is not null
813: order by p.relation_product;
814:
815: /* select count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
816: from JTF_TAE_QUAL_products p,
817: JTF_TAE_QUAL_prod_factors pf
818: where p.qual_product_id = pf.qual_product_id
819: group by p.qual_product_id, p.relation_product
820: order by 1;
821: */
825: , cl_relation_product NUMBER ) is
826: select * from (
827: select count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
828: from JTF_TAE_QUAL_products p,
829: JTF_TAE_QUAL_prod_factors pf
830: where p.qual_product_id = pf.qual_product_id
831: and p.source_id = p_source_id
832: and p.trans_object_type_id = p_trans_id
833: group by p.qual_product_id, p.relation_product
840: select p.trans_object_type_id, p.qual_product_id, p.relation_product
841: from JTF_TAE_QUAL_products p
842: where not exists (select *
843: from JTF_TAE_QUAL_products ip,
844: JTF_TAE_QUAL_prod_factors ipf,
845: JTF_TAE_QUAL_factors ifc
846:
847: where use_tae_col_in_index_flag = 'Y'
848: and ip.qual_product_id = ipf.qual_product_id
883: select rownum row_count, tae_col_map, input_selectivity
884: from (
885: select distinct p.relation_product, f.tae_col_map, f.input_selectivity
886: from JTF_TAE_QUAL_products p,
887: JTF_TAE_QUAL_prod_factors pf,
888: JTF_TAE_QUAL_factors f
889: where f.qual_factor_id = pf.qual_factor_id
890: and pf.qual_product_id = p.qual_product_id
891: and p.relation_product = cl_set_S.relation_product
899: select rownum row_count, tae_col_map, input_selectivity
900: from (
901: select distinct p.relation_product, f.tae_col_map, f.input_selectivity
902: from JTF_TAE_QUAL_products p,
903: JTF_TAE_QUAL_prod_factors pf,
904: JTF_TAE_QUAL_factors f
905: where f.qual_factor_id = pf.qual_factor_id
906: and pf.qual_product_id = p.qual_product_id
907: and p.relation_product = cl_set_L.relation_product
932: select count(*) into l_first_char_flag_count
933: from
934: (select qual_usg_id, tae_col_map, rownum row_count
935: from ( select f.qual_usg_id, f.relation_factor, f.tae_col_map
936: from JTF_TAE_QUAL_prod_factors pf,
937: JTF_TAE_QUAL_factors f
938: where pf.qual_factor_id = f.qual_factor_id
939: and pf.qual_product_id = cl_set_S.qual_product_id
940: order by f.input_selectivity