[Home] [Help]
84: SELECT 1
85: INTO l_num_val
86: FROM DUAL
87: WHERE EXISTS (SELECT 1
88: FROM icx_cat_attributes_tl
89: WHERE UPPER(key) = UPPER(p_key)
90: AND language = p_language);
91: ELSE
92: l_err_loc := 120;
96: SELECT 1
97: INTO l_num_val
98: FROM DUAL
99: WHERE EXISTS (SELECT 1
100: FROM icx_cat_attributes_tl
101: WHERE UPPER(key) = UPPER(p_key)
102: AND language = p_language
103: AND (rt_category_id = p_cat_id OR
104: rt_category_id = 0));
129: SELECT 1
130: INTO l_num_val
131: FROM DUAL
132: WHERE EXISTS (SELECT 1
133: FROM icx_cat_attributes_tl
134: WHERE UPPER(attribute_name) = UPPER(p_name)
135: AND language = p_language);
136: ELSE
137: l_err_loc := 230;
139: SELECT 1
140: INTO l_num_val
141: FROM DUAL
142: WHERE EXISTS (SELECT 1
143: FROM icx_cat_attributes_tl
144: WHERE UPPER(attribute_name) = UPPER(p_name)
145: AND language = p_language
146: AND (rt_category_id = p_cat_id OR
147: rt_category_id = 0));
170: -- for base
171: l_err_loc := 310;
172: SELECT COUNT(*)
173: INTO l_num_val
174: FROM icx_cat_attributes_tl
175: WHERE rt_category_id = 0
176: AND language = p_language
177: AND to_char(type) = p_type
178: AND attribute_id > 100;
210: -- for category
211: l_err_loc := 340;
212: SELECT COUNT(*)
213: INTO l_num_val
214: FROM icx_cat_attributes_tl
215: WHERE rt_category_id = p_cat_id
216: AND language = p_language
217: AND to_char(type) = p_type;
218:
703:
704: l_err_loc := 310;
705:
706: -- then we delete the attributes for that category
707: DELETE FROM icx_cat_attributes_tl
708: WHERE rt_category_id = p_category_id;
709:
710: l_err_loc := 320;
711:
1687: IF (p_sequence IS NULL) THEN
1688: l_err_loc := 130;
1689: SELECT floor(nvl(max(sequence), 0)) + 1
1690: INTO l_sequence
1691: FROM icx_cat_attributes_tl
1692: WHERE rt_category_id = p_category_id
1693: AND language = p_language;
1694: ELSE
1695: l_err_loc := 135;
1736:
1737: l_err_loc := 200;
1738: -- now we create the descriptor
1739:
1740: INSERT INTO icx_cat_attributes_tl (attribute_id, rt_category_id, language,
1741: source_lang, attribute_name, description, type, key, sequence, searchable,
1742: search_results_visible, item_detail_visible, request_id, rebuild_flag,
1743: created_by, creation_date, last_updated_by, last_update_date, last_update_login,
1744: program_id, program_application_id, program_login_id)
1854:
1855: -- First check whether a section tag is already assigned
1856: SELECT section_tag, stored_in_table, stored_in_column
1857: INTO p_section_tag, p_stored_in_table, p_stored_in_column
1858: FROM icx_cat_attributes_tl
1859: WHERE attribute_id = p_descriptor_id
1860: AND rownum = 1;
1861:
1862: l_err_loc := 110;
1966:
1967: l_err_loc := 250;
1968: -- now we update the attributes table with the section tag,
1969: -- stored_in_table and stored_in_column
1970: UPDATE icx_cat_attributes_tl
1971: SET section_tag = p_section_tag,
1972: stored_in_table = p_stored_in_table,
1973: stored_in_column = p_stored_in_column,
1974: request_id = p_request_id,
2029:
2030: -- now get the existing section tag
2031: SELECT section_tag
2032: INTO l_section_tag
2033: FROM icx_cat_attributes_tl
2034: WHERE attribute_id = p_descriptor_id
2035: AND rownum = 1;
2036:
2037: l_err_loc := 120;
2041:
2042: l_err_loc := 130;
2043:
2044: -- now nullify the section tag in attributes table
2045: UPDATE icx_cat_attributes_tl
2046: SET section_tag = null,
2047: request_id = p_request_id,
2048: program_id = fnd_global.conc_program_id,
2049: program_application_id = fnd_global.prog_appl_id,
2119: -- first select the current value of searchable
2120:
2121: SELECT to_char(searchable)
2122: INTO l_searchable
2123: FROM icx_cat_attributes_tl
2124: WHERE attribute_id = p_descriptor_id
2125: AND rownum = 1;
2126:
2127: -- if searchable has changed then we need to rebuild
2135:
2136: l_err_loc := 120;
2137: -- first we update the translatable attributes only in the current lang
2138: -- i.e. name and description
2139: UPDATE icx_cat_attributes_tl
2140: SET attribute_name = nvl (p_name, attribute_name),
2141: description = decode(p_description, '#DEL', null, null, description, p_description),
2142: source_lang = p_language,
2143: last_updated_by = p_user_id,
2152:
2153: l_err_loc := 130;
2154:
2155: -- now update searchable, SRV and IDV and sequence in all languages
2156: UPDATE icx_cat_attributes_tl
2157: SET sequence = decode (p_sequence, '#DEL', null, null, sequence, p_sequence),
2158: searchable = to_number(nvl(p_searchable, searchable)),
2159: search_results_visible = to_number(nvl(p_search_results_visible, search_results_visible)),
2160: item_detail_visible = to_number(nvl(p_item_detail_visible, item_detail_visible)),
2216:
2217: -- select some information about the descriptor
2218: SELECT rt_category_id, to_char(searchable)
2219: INTO l_category_id, l_searchable
2220: FROM icx_cat_attributes_tl
2221: WHERE attribute_id = p_descriptor_id
2222: AND rownum = 1;
2223:
2224: l_err_loc := 130;
2228:
2229: l_err_loc := 140;
2230:
2231: -- now delete the descriptor
2232: DELETE from icx_cat_attributes_tl
2233: WHERE attribute_id = p_descriptor_id;
2234:
2235: l_err_loc := 150;
2236:
2280:
2281: -- first select stored in table and stored in column
2282: SELECT rt_category_id, stored_in_table, stored_in_column
2283: INTO l_category_id, l_stored_in_table, l_stored_in_column
2284: FROM icx_cat_attributes_tl
2285: WHERE attribute_id = p_descriptor_id
2286: AND rownum = 1;
2287:
2288: -- now assume that the descriptor is not referenced
2546: -- we try to get the descriptor from the database
2547: -- for this key and category
2548: SELECT attribute_id, to_char(type)
2549: INTO x_descriptor_id, l_current_type
2550: FROM icx_cat_attributes_tl
2551: WHERE UPPER(key) = UPPER(p_key)
2552: AND language = p_language
2553: AND rt_category_id = x_owner_id
2554: AND rownum = 1;
2603: SELECT 1
2604: INTO l_num_val
2605: FROM DUAL
2606: WHERE EXISTS (SELECT 1
2607: FROM icx_cat_attributes_tl
2608: WHERE UPPER(key) = UPPER(p_key)
2609: AND language = p_language);
2610: ELSE
2611: l_err_loc := 553;
2612: SELECT 1
2613: INTO l_num_val
2614: FROM DUAL
2615: WHERE EXISTS (SELECT 1
2616: FROM icx_cat_attributes_tl
2617: WHERE UPPER(key) = UPPER(p_key)
2618: AND language = p_language
2619: AND (rt_category_id = x_owner_id OR
2620: rt_category_id = 0));
2654: SELECT 1
2655: INTO l_num_val
2656: FROM DUAL
2657: WHERE EXISTS (SELECT 1
2658: FROM icx_cat_attributes_tl
2659: WHERE UPPER(attribute_name) = UPPER(p_name)
2660: AND language = p_language);
2661: ELSE
2662: l_err_loc := 590;
2664: SELECT 1
2665: INTO l_num_val
2666: FROM DUAL
2667: WHERE EXISTS (SELECT 1
2668: FROM icx_cat_attributes_tl
2669: WHERE UPPER(attribute_name) = UPPER(p_name)
2670: AND language = p_language
2671: AND (rt_category_id = x_owner_id OR
2672: rt_category_id = 0));
2694: -- for base
2695: l_err_loc := 630;
2696: SELECT COUNT(*)
2697: INTO l_num_val
2698: FROM icx_cat_attributes_tl
2699: WHERE rt_category_id = 0
2700: AND language = p_language
2701: AND to_char(type) = p_type
2702: AND attribute_id > 100;
2735: -- for category
2736: l_err_loc := 660;
2737: SELECT COUNT(*)
2738: INTO l_num_val
2739: FROM icx_cat_attributes_tl
2740: WHERE rt_category_id = x_owner_id
2741: AND language = p_language
2742: AND to_char(type) = p_type;
2743:
2794: SELECT 1
2795: INTO l_num_val
2796: FROM dual
2797: WHERE EXISTS (SELECT 1
2798: FROM icx_cat_attributes_tl
2799: WHERE UPPER(attribute_name) = UPPER(p_name)
2800: AND attribute_id <> x_descriptor_id);
2801: ELSE
2802: l_err_loc := 710;
2804: SELECT 1
2805: INTO l_num_val
2806: FROM dual
2807: WHERE EXISTS (SELECT 1
2808: FROM icx_cat_attributes_tl
2809: WHERE UPPER(attribute_name) = UPPER(p_name)
2810: AND (rt_category_id = x_owner_id OR
2811: rt_category_id = 0)
2812: AND attribute_id <> x_descriptor_id);
3065: -- cursor to handle special descriptor update
3066: CURSOR populate_special_descs_csr
3067: IS
3068: SELECT key, searchable
3069: FROM icx_cat_attributes_tl
3070: WHERE request_id = p_request_id
3071: AND rebuild_flag = 'Y'
3072: AND rt_category_id = 0
3073: AND key IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID', 'SUPPLIER',
3077: -- cursor that handles the descriptor update for regular (non-special) descriptors
3078: CURSOR populate_regular_descs_csr
3079: IS
3080: SELECT distinct rt_category_id
3081: FROM icx_cat_attributes_tl
3082: WHERE request_id = p_request_id
3083: AND rebuild_flag = 'Y'
3084: AND key NOT IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID', 'SUPPLIER',
3085: 'INTERNAL_ITEM_NUM', 'SOURCE', 'ITEM_REVISION',
3173:
3174: l_err_loc := 600;
3175:
3176: -- reset rebuild flag to null
3177: UPDATE icx_cat_attributes_tl
3178: SET rebuild_flag = null
3179: WHERE rebuild_flag is not null;
3180:
3181: l_err_loc := 700;