[Home] [Help]
80: END;
81: ELSIF (p_mode = 'DATE EFFECTIVE') THEN
82: BEGIN
83:
84: DELETE FROM jty_dea_attr_prod_factors
85: WHERE dea_attr_products_id IN
86: ( SELECT dea_attr_products_id
87: FROM jty_dea_attr_products
88: WHERE source_id = p_source_id
1544: FROM jty_dea_attr_factors
1545: WHERE qual_usg_id = qual_name.qual_usg_id
1546: AND rownum < 2;
1547:
1548: SELECT JTY_DEA_ATTR_PROD_FACTORS_S.NEXTVAL
1549: INTO l_qual_prod_factor_id
1550: FROM dual;
1551:
1552: INSERT INTO JTY_DEA_ATTR_PROD_FACTORS
1548: SELECT JTY_DEA_ATTR_PROD_FACTORS_S.NEXTVAL
1549: INTO l_qual_prod_factor_id
1550: FROM dual;
1551:
1552: INSERT INTO JTY_DEA_ATTR_PROD_FACTORS
1553: ( DEA_ATTR_PROD_FACTORS_ID,
1554: DEA_ATTR_PRODUCTS_ID,
1555: DEA_ATTR_FACTORS_ID,
1556: LAST_UPDATE_DATE,
1575: );
1576:
1577: EXCEPTION
1578: WHEN NO_DATA_FOUND THEN
1579: x_msg_data := 'Populating JTY_DEA_ATTR_PROD_FACTORS table : Error no_data_found.';
1580: RAISE FND_API.G_EXC_ERROR;
1581:
1582: WHEN OTHERS THEN
1583: x_msg_data := SQLCODE || ' : ' || SQLERRM;
2110:
2111: CURSOR all_sets(cl_source_id number, cl_trans_id number) is
2112: SELECT p.trans_type_id, count(*) num_components, p.dea_attr_products_id dea_attr_products_id, p.attr_relation_product
2113: FROM jty_dea_attr_products p,
2114: jty_dea_attr_prod_factors pf,
2115: jty_dea_attr_factors f
2116: WHERE p.dea_attr_products_id = pf.dea_attr_products_id
2117: AND pf.dea_attr_factors_id = f.dea_attr_factors_id
2118: AND f.tae_col_map is not null
2127: , cl_trans_id NUMBER ) is
2128: SELECT * FROM (
2129: SELECT count(*) num_components, p.dea_attr_products_id dea_attr_products_id, p.attr_relation_product
2130: FROM jty_dea_attr_products p,
2131: jty_dea_attr_prod_factors pf
2132: WHERE p.dea_attr_products_id = pf.dea_attr_products_id
2133: AND p.source_id = cl_source_id
2134: AND p.trans_type_id = cl_trans_id
2135: GROUP BY p.dea_attr_products_id, p.attr_relation_product )
2141: SELECT p.trans_type_id, p.dea_attr_products_id, p.attr_relation_product
2142: FROM jty_dea_attr_products p
2143: WHERE NOT EXISTS (SELECT *
2144: FROM jty_dea_attr_products ip,
2145: jty_dea_attr_prod_factors ipf,
2146: jty_dea_attr_factors ifc
2147: WHERE use_tae_col_in_index_flag = 'Y'
2148: AND ip.dea_attr_products_id = ipf.dea_attr_products_id
2149: AND ipf.dea_attr_factors_id = ifc.dea_attr_factors_id
2179: SELECT rownum row_count, tae_col_map, input_selectivity
2180: FROM (
2181: SELECT DISTINCT p.attr_relation_product, f.tae_col_map, f.input_selectivity
2182: FROM jty_dea_attr_products p,
2183: jty_dea_attr_prod_factors pf,
2184: jty_dea_attr_factors f
2185: WHERE f.dea_attr_factors_id = pf.dea_attr_factors_id
2186: AND pf.dea_attr_products_id = p.dea_attr_products_id
2187: AND p.attr_relation_product = cl_set_S.attr_relation_product
2193: SELECT rownum row_count, tae_col_map, input_selectivity
2194: FROM (
2195: SELECT DISTINCT p.attr_relation_product, f.tae_col_map, f.input_selectivity
2196: FROM jty_dea_attr_products p,
2197: jty_dea_attr_prod_factors pf,
2198: jty_dea_attr_factors f
2199: WHERE f.dea_attr_factors_id = pf.dea_attr_factors_id
2200: AND pf.dea_attr_products_id = p.dea_attr_products_id
2201: AND p.attr_relation_product = cl_set_L.attr_relation_product
2221: FROM (
2222: SELECT qual_usg_id, tae_col_map, rownum row_count
2223: FROM (
2224: SELECT f.qual_usg_id, f.relation_factor, f.tae_col_map
2225: FROM jty_dea_attr_prod_factors pf,
2226: jty_dea_attr_factors f
2227: WHERE pf.dea_attr_factors_id = f.dea_attr_factors_id
2228: AND pf.dea_attr_products_id = cl_set_S.dea_attr_products_id
2229: ORDER BY f.input_selectivity)) ilv1,