[Home] [Help]
90: END;
91: ELSIF (p_mode = 'DATE EFFECTIVE') THEN
92: BEGIN
93:
94: DELETE FROM jty_dea_attr_prod_factors
95: WHERE dea_attr_products_id IN
96: ( SELECT dea_attr_products_id
97: FROM jty_dea_attr_products
98: WHERE source_id = p_source_id
176: RETURNING dea_attr_products_id, attr_relation_product BULK COLLECT INTO l_qual_prd_id_tbl, l_rel_prd_tbl;
177:
178: IF (l_qual_prd_id_tbl.COUNT > 0) THEN
179: FORALL i IN l_qual_prd_id_tbl.FIRST .. l_qual_prd_id_tbl.LAST
180: DELETE FROM jty_dea_attr_prod_factors
181: WHERE dea_attr_products_id = l_qual_prd_id_tbl(i);
182: END IF;
183:
184: DELETE FROM jty_dea_attr_factors o
1574: FROM jty_dea_attr_factors
1575: WHERE qual_usg_id = qual_name.qual_usg_id
1576: AND rownum < 2;
1577:
1578: SELECT JTY_DEA_ATTR_PROD_FACTORS_S.NEXTVAL
1579: INTO l_qual_prod_factor_id
1580: FROM dual;
1581:
1582: INSERT INTO JTY_DEA_ATTR_PROD_FACTORS
1578: SELECT JTY_DEA_ATTR_PROD_FACTORS_S.NEXTVAL
1579: INTO l_qual_prod_factor_id
1580: FROM dual;
1581:
1582: INSERT INTO JTY_DEA_ATTR_PROD_FACTORS
1583: ( DEA_ATTR_PROD_FACTORS_ID,
1584: DEA_ATTR_PRODUCTS_ID,
1585: DEA_ATTR_FACTORS_ID,
1586: LAST_UPDATE_DATE,
1605: );
1606:
1607: EXCEPTION
1608: WHEN NO_DATA_FOUND THEN
1609: x_msg_data := 'Populating JTY_DEA_ATTR_PROD_FACTORS table : Error no_data_found.';
1610: RAISE FND_API.G_EXC_ERROR;
1611:
1612: WHEN OTHERS THEN
1613: x_msg_data := SQLCODE || ' : ' || SQLERRM;
2122:
2123: CURSOR all_sets(cl_source_id number, cl_trans_id number) is
2124: SELECT p.trans_type_id, count(*) num_components, p.dea_attr_products_id dea_attr_products_id, p.attr_relation_product
2125: FROM jty_dea_attr_products p,
2126: jty_dea_attr_prod_factors pf,
2127: jty_dea_attr_factors f
2128: WHERE p.dea_attr_products_id = pf.dea_attr_products_id
2129: AND pf.dea_attr_factors_id = f.dea_attr_factors_id
2130: AND f.tae_col_map is not null
2139: , cl_trans_id NUMBER ) is
2140: SELECT * FROM (
2141: SELECT count(*) num_components, p.dea_attr_products_id dea_attr_products_id, p.attr_relation_product
2142: FROM jty_dea_attr_products p,
2143: jty_dea_attr_prod_factors pf
2144: WHERE p.dea_attr_products_id = pf.dea_attr_products_id
2145: AND p.source_id = cl_source_id
2146: AND p.trans_type_id = cl_trans_id
2147: GROUP BY p.dea_attr_products_id, p.attr_relation_product )
2153: SELECT p.trans_type_id, p.dea_attr_products_id, p.attr_relation_product
2154: FROM jty_dea_attr_products p
2155: WHERE NOT EXISTS (SELECT *
2156: FROM jty_dea_attr_products ip,
2157: jty_dea_attr_prod_factors ipf,
2158: jty_dea_attr_factors ifc
2159: WHERE use_tae_col_in_index_flag = 'Y'
2160: AND ip.dea_attr_products_id = ipf.dea_attr_products_id
2161: AND ipf.dea_attr_factors_id = ifc.dea_attr_factors_id
2189: SELECT rownum row_count, tae_col_map, input_selectivity
2190: FROM (
2191: SELECT DISTINCT p.attr_relation_product, f.tae_col_map, f.input_selectivity
2192: FROM jty_dea_attr_products p,
2193: jty_dea_attr_prod_factors pf,
2194: jty_dea_attr_factors f
2195: WHERE f.dea_attr_factors_id = pf.dea_attr_factors_id
2196: AND pf.dea_attr_products_id = p.dea_attr_products_id
2197: AND p.attr_relation_product = cl_set_S.attr_relation_product
2203: SELECT rownum row_count, tae_col_map, input_selectivity
2204: FROM (
2205: SELECT DISTINCT p.attr_relation_product, f.tae_col_map, f.input_selectivity
2206: FROM jty_dea_attr_products p,
2207: jty_dea_attr_prod_factors pf,
2208: jty_dea_attr_factors f
2209: WHERE f.dea_attr_factors_id = pf.dea_attr_factors_id
2210: AND pf.dea_attr_products_id = p.dea_attr_products_id
2211: AND p.attr_relation_product = cl_set_L.attr_relation_product
2231: FROM (
2232: SELECT qual_usg_id, tae_col_map, rownum row_count
2233: FROM (
2234: SELECT f.qual_usg_id, f.relation_factor, f.tae_col_map
2235: FROM jty_dea_attr_prod_factors pf,
2236: jty_dea_attr_factors f
2237: WHERE pf.dea_attr_factors_id = f.dea_attr_factors_id
2238: AND pf.dea_attr_products_id = cl_set_S.dea_attr_products_id
2239: ORDER BY f.input_selectivity)) ilv1,