[Home] [Help]
60:
61: IF (p_mode = 'TOTAL') THEN
62: BEGIN
63:
64: DELETE FROM jtf_tae_qual_prod_factors
65: WHERE qual_product_id IN
66: ( SELECT qual_product_id
67: FROM jtf_tae_qual_products
68: WHERE source_id = p_source_id
135: RETURNING qual_product_id, relation_product BULK COLLECT INTO l_qual_prd_id_tbl, l_rel_prd_tbl;
136:
137: IF (l_qual_prd_id_tbl.COUNT > 0) THEN
138: FORALL i IN l_qual_prd_id_tbl.FIRST .. l_qual_prd_id_tbl.LAST
139: DELETE FROM jtf_tae_qual_prod_factors
140: WHERE qual_product_id = l_qual_prd_id_tbl(i);
141: END IF;
142:
143: DELETE FROM jtf_tae_qual_factors o
865: FROM JTF_TAE_QUAL_factors
866: WHERE qual_usg_id = qual_name.qual_usg_id
867: AND rownum < 2;
868:
869: SELECT JTF_TAE_QUAL_PROD_FACTORS_S.NEXTVAL
870: INTO l_qual_prod_factor_id
871: FROM dual;
872:
873: INSERT INTO JTF_TAE_QUAL_prod_factors
869: SELECT JTF_TAE_QUAL_PROD_FACTORS_S.NEXTVAL
870: INTO l_qual_prod_factor_id
871: FROM dual;
872:
873: INSERT INTO JTF_TAE_QUAL_prod_factors
874: ( QUAL_PROD_FACTOR_ID,
875: QUAL_PRODUCT_ID,
876: QUAL_FACTOR_ID,
877: LAST_UPDATE_DATE,
898: );
899:
900: EXCEPTION
901: WHEN NO_DATA_FOUND THEN
902: x_msg_data := 'Populating JTF_TAE_QUAL_PROD_FACTORS table : Error no_data_found.';
903: RAISE FND_API.G_EXC_ERROR;
904:
905: WHEN OTHERS THEN
906: x_msg_data := SQLCODE || ' : ' || SQLERRM;
1925:
1926: CURSOR all_sets(cl_source_id number, cl_trans_id number) is
1927: SELECT p.trans_object_type_id, count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
1928: FROM JTF_TAE_QUAL_products p,
1929: JTF_TAE_QUAL_prod_factors pf,
1930: JTF_TAE_QUAL_factors f
1931: WHERE p.qual_product_id = pf.qual_product_id
1932: AND pf.qual_factor_id = f.qual_factor_id
1933: AND f.tae_col_map is not null
1942: , cl_trans_id NUMBER ) is
1943: SELECT * FROM (
1944: SELECT count(*) num_components, p.qual_product_id qual_product_id, p.relation_product
1945: FROM JTF_TAE_QUAL_products p,
1946: JTF_TAE_QUAL_prod_factors pf
1947: WHERE p.qual_product_id = pf.qual_product_id
1948: AND p.source_id = cl_source_id
1949: AND p.trans_object_type_id = cl_trans_id
1950: GROUP BY p.qual_product_id, p.relation_product )
1956: SELECT p.trans_object_type_id, p.qual_product_id, p.relation_product
1957: FROM JTF_TAE_QUAL_products p
1958: WHERE NOT EXISTS (SELECT *
1959: FROM JTF_TAE_QUAL_products ip,
1960: JTF_TAE_QUAL_prod_factors ipf,
1961: JTF_TAE_QUAL_factors ifc
1962: WHERE use_tae_col_in_index_flag = 'Y'
1963: AND ip.qual_product_id = ipf.qual_product_id
1964: AND ipf.qual_factor_id = ifc.qual_factor_id
1993: SELECT rownum row_count, tae_col_map, input_selectivity
1994: FROM (
1995: SELECT DISTINCT p.relation_product, f.tae_col_map, f.input_selectivity
1996: FROM JTF_TAE_QUAL_products p,
1997: JTF_TAE_QUAL_prod_factors pf,
1998: JTF_TAE_QUAL_factors f
1999: WHERE f.qual_factor_id = pf.qual_factor_id
2000: AND pf.qual_product_id = p.qual_product_id
2001: AND p.relation_product = cl_set_S.relation_product
2007: SELECT rownum row_count, tae_col_map, input_selectivity
2008: FROM (
2009: SELECT DISTINCT p.relation_product, f.tae_col_map, f.input_selectivity
2010: FROM JTF_TAE_QUAL_products p,
2011: JTF_TAE_QUAL_prod_factors pf,
2012: JTF_TAE_QUAL_factors f
2013: WHERE f.qual_factor_id = pf.qual_factor_id
2014: AND pf.qual_product_id = p.qual_product_id
2015: AND p.relation_product = cl_set_L.relation_product
2035: FROM (
2036: SELECT qual_usg_id, tae_col_map, rownum row_count
2037: FROM (
2038: SELECT f.qual_usg_id, f.relation_factor, f.tae_col_map
2039: FROM JTF_TAE_QUAL_prod_factors pf,
2040: JTF_TAE_QUAL_factors f
2041: WHERE pf.qual_factor_id = f.qual_factor_id
2042: AND pf.qual_product_id = cl_set_S.qual_product_id
2043: ORDER BY f.input_selectivity)) ilv1,