1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
DECLARE
g_number number;
TYPE vs$type IS RECORD
(v_flex_value_set_name VARCHAR2( 30 )
, v_validation_type VARCHAR2( 1 )
, v_format_type VARCHAR2( 1 )
, v_maximum_size NUMBER( 3 )
, v_alphanumeric_allowed_flag VARCHAR2( 1 )
, v_uppercase_only_flag VARCHAR2( 1 )
, v_numeric_mode_enabled_flag VARCHAR2( 1 )
, v_description VARCHAR2( 100 )
, v_minimum_value VARCHAR2( 5 )
, v_maximum_value VARCHAR2( 5 )
, v_protected_flag VARCHAR2( 1 )
, v_application_table_name VARCHAR2( 30 )
, v_value_column_name VARCHAR2( 30 )
, v_value_column_type VARCHAR2( 1 )
, v_value_column_size NUMBER( 3 )
, v_id_column_name VARCHAR2( 30 )
, v_id_column_type VARCHAR2( 1 )
, v_id_column_size NUMBER( 3 )
, v_meaning_column_name VARCHAR2( 30 )
, v_meaning_column_type VARCHAR2( 1 )
, v_meaning_column_size NUMBER( 3 )
, v_additional_where_clause LONG
, v_e_user_exit LONG
, v_v_user_exit LONG
, v_l_user_exit LONG
);
TYPE vsets$type IS TABLE OF vs$type INDEX BY BINARY_INTEGER;
t_vsets vsets$type;
BEGIN
g_number:= 0 ;
g_number:=g_number+ 1 ;
t_vsets(g_number).v_flex_value_set_name := 'XX_CATEGORY_STRUCTURE';
t_vsets(g_number).v_validation_type := 'F';
t_vsets(g_number).v_format_type := 'C';
t_vsets(g_number).v_maximum_size := 44 ;
t_vsets(g_number).v_alphanumeric_allowed_flag := 'Y';
t_vsets(g_number).v_uppercase_only_flag := 'N';
t_vsets(g_number).v_numeric_mode_enabled_flag := 'N';
t_vsets(g_number).v_description :='Структуры категорий';
t_vsets(g_number).v_minimum_value := '';
t_vsets(g_number).v_maximum_value := '';
t_vsets(g_number).v_protected_flag := 'N';
t_vsets(g_number).v_application_table_name := 'MTL_CATEGORY_SETS';
t_vsets(g_number).v_value_column_name := 'STRUCTURE_ID';
t_vsets(g_number).v_value_column_type := 'C';
t_vsets(g_number).v_value_column_size := '44';
t_vsets(g_number).v_id_column_name := 'STRUCTURE_ID';
t_vsets(g_number).v_id_column_type := 'C';
t_vsets(g_number).v_id_column_size := '44';
t_vsets(g_number).v_meaning_column_name := '';
t_vsets(g_number).v_meaning_column_type := '';
t_vsets(g_number).v_meaning_column_size := '';
t_vsets(g_number).v_additional_where_clause := '';
g_number:=g_number+ 1 ;
t_vsets(g_number).v_flex_value_set_name :='XX_SRS_CATEGORY';
t_vsets(g_number).v_validation_type :='U';
t_vsets(g_number).v_format_type :='C';
t_vsets(g_number).v_maximum_size:= 240 ;
t_vsets(g_number).v_alphanumeric_allowed_flag:= 'Y';
t_vsets(g_number).v_uppercase_only_flag :='N';
t_vsets(g_number).v_numeric_mode_enabled_flag :='N';
t_vsets(g_number).v_description :='Категория';
t_vsets(g_number).v_minimum_value :='';
t_vsets(g_number).v_maximum_value :='';
t_vsets(g_number).v_protected_flag :='N';
t_vsets(g_number).v_application_table_name :='';
t_vsets(g_number).v_value_column_name :='';
t_vsets(g_number).v_value_column_type :='';
t_vsets(g_number).v_value_column_size :='';
t_vsets(g_number).v_id_column_name :='';
t_vsets(g_number).v_id_column_type :='';
t_vsets(g_number).v_id_column_size :='';
t_vsets(g_number).v_meaning_column_name:='';
t_vsets(g_number).v_meaning_column_type :='';
t_vsets(g_number).v_meaning_column_size :='';
t_vsets(g_number).v_additional_where_clause :='';
t_vsets(g_number).v_e_user_exit :=
'FND POPID CODE="MCAT"
SEG=:!VALUE NUM=:$FLEX$.XX_CATEGORY_STRUCTURE
VALIDATE="FULL" ID=:!ID NAVIGATE=:!DIR
REQUIRED="N" COLUMN="Description(50)"
DINSERT="N" APPL_SHORT_NAME="INV"';
t_vsets(g_number).v_v_user_exit :=
'FND LOADID CODE="MCAT"
SEG=:!VALUE NUM=:$FLEX$.XX_CATEGORY_STRUCTURE
VALIDATE="FULL" ID=:!ID REQUIRED="N"
DINSERT="N" APPL_SHORT_NAME="INV"';
t_vsets(g_number).v_l_user_exit :=
'FND LOADID CODE="MCAT"
SEG=:!VALUE SET=:$FLEX$.XX_CATEGORY_STRUCTURE
VALIDATE="FULL" ID=:!ID REQUIRED="N"
DINSERT="N" APPL_SHORT_NAME="INV"';
FOR i IN t_vsets.FIRST..t_vsets.LAST LOOP
IF (fnd_flex_val_api.valueset_exists(t_vsets(i).v_flex_value_set_name)) THEN
dbms_output.put_line('Value set '||t_vsets(i).v_flex_value_set_name||' exists, deleting...');
fnd_flex_val_api.delete_valueset(
value_set => t_vsets(i).v_flex_value_set_name
);
END IF;
dbms_output.put_line('Value set '||t_vsets(i).v_flex_value_set_name||' creating...');
INSERT INTO
fnd_flex_value_sets(flex_value_set_id
, flex_value_set_name
, validation_type
, security_enabled_flag
, longlist_flag
, format_type
, maximum_size
, alphanumeric_allowed_flag
, uppercase_only_flag
, numeric_mode_enabled_flag
, description
, minimum_value
, maximum_value
, number_precision
, protected_flag
, last_update_login
, last_update_date
, last_updated_by
, creation_date
, created_by
, dependant_default_value
, dependant_default_meaning
, parent_flex_value_set_id
) VALUES(fnd_flex_value_sets_s.nextval
, t_vsets(i).v_flex_value_set_name
, t_vsets(i).v_validation_type
, 'N'
, 'N'
, t_vsets(i).v_format_type
, t_vsets(i).v_maximum_size
, t_vsets(i).v_alphanumeric_allowed_flag
, t_vsets(i).v_uppercase_only_flag
, t_vsets(i).v_numeric_mode_enabled_flag
, t_vsets(i).v_description
, t_vsets(i).v_minimum_value
, t_vsets(i).v_maximum_value
, null
, t_vsets(i).v_protected_flag
, 1
, sysdate
, 1
, sysdate
, 1
, null
, null
, null
);
IF (t_vsets(i).v_validation_type='F') THEN
SELECT fnd_flex_value_sets_s.CURRVAL
INTO value_set_id
FROM dual;
INSERT INTO
fnd_flex_validation_tables(flex_value_set_id
, application_table_name
, value_column_name
, value_column_type
, value_column_size
, id_column_name
, id_column_type
, id_column_size
, meaning_column_name
, meaning_column_type
, meaning_column_size
, additional_where_clause
, summary_allowed_flag
, table_application_id
, additional_quickpick_columns
, compiled_attribute_column_name
, enabled_column_name
, hierarchy_level_column_name
, start_date_column_name
, end_date_column_name
, summary_column_name
, last_update_login
, last_update_date
, last_updated_by
, creation_date
, created_by
) VALUES(value_set_id
, t_vsets(i).v_application_table_name
, t_vsets(i).v_value_column_name
, t_vsets(i).v_value_column_type
, t_vsets(i).v_value_column_size
, t_vsets(i).v_id_column_name
, t_vsets(i).v_id_column_type
, t_vsets(i).v_id_column_size
, t_vsets(i).v_meaning_column_name
, t_vsets(i).v_meaning_column_type
, t_vsets(i).v_meaning_column_size
, t_vsets(i).v_additional_where_clause
, 'N'
, table_application_id
, null
, '''NULL'''
, '''Y'''
, 'NULL'
, 'TO_DATE(NULL)'
, 'TO_DATE(NULL)'
, '''N'''
, 1
, sysdate
, 1
, sysdate
, 1
);
END IF;
IF (t_vsets(i).v_validation_type='U') THEN
SELECT fnd_flex_value_sets_s.CURRVAL
INTO value_set_id
FROM dual;
INSERT INTO
fnd_flex_validation_events(flex_value_set_id
, event_code
, user_exit
, last_update_login
, last_update_date
, last_updated_by
, creation_date
, created_by
) VALUES(value_set_id
, 'E'
, t_vsets(i).v_e_user_exit
, 1
, sysdate
, 1
, sysdate
, 1
);
INSERT INTO
fnd_flex_validation_events(flex_value_set_id
, event_code
, user_exit
, last_update_login
, last_update_date
, last_updated_by
, creation_date
, created_by
) VALUES(value_set_id
, 'L'
, t_vsets(i).v_l_user_exit
, 1
, sysdate
, 1
, sysdate
, 1
);
INSERT INTO
fnd_flex_validation_events(flex_value_set_id
, event_code
, user_exit
, last_update_login
, last_update_date
, last_updated_by
, creation_date
, created_by
) VALUES(value_set_id
, 'V'
, t_vsets(i).v_v_user_exit
, 1
, sysdate
, 1
, sysdate
, 1
);
END IF;
dbms_output.put_line('Value set '||t_vsets(i).v_flex_value_set_name||' created.');
END LOOP;
END;
