[Home] [Help]
PACKAGE BODY: APPS.EGO_COM_ATTR_VALIDATION
Source
1 PACKAGE BODY EGO_COM_ATTR_VALIDATION AS
2 /* $Header: EGOCOMVB.pls 120.0.12010000.7 2009/04/22 19:53:32 mshirkol noship $ */
3
4 -----------------------------------------------------------------------
5 -- This procedure validates PIM Telco item user defined attributes --
6 -----------------------------------------------------------------------
7
8 PROCEDURE Validate_Attributes (
9 p_attr_group_type IN VARCHAR2
10 ,p_attr_group_name IN VARCHAR2
11 ,p_attr_group_id IN NUMBER
12 ,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
13 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
14 ,x_return_status OUT NOCOPY VARCHAR2
15 ,x_error_messages OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
16 ) IS
17
18 l_curr_data_element EGO_USER_ATTR_DATA_OBJ;
19 l_curr_pk_col_name_val_element EGO_COL_NAME_VALUE_PAIR_OBJ;
20 l_curr_class_cd_val_element EGO_COL_NAME_VALUE_PAIR_OBJ;
21 l_error_messages EGO_COL_NAME_VALUE_PAIR_ARRAY;
22
23 -- attribute group --
24 l_attr_group_type VARCHAR2(40);
25 l_attrgrp_name VARCHAR2(40);
26 l_attr_group_id NUMBER;
27
28 -- attributes --
29 l_curr_data_id NUMBER;
30 l_curr_data_attr_name VARCHAR2(30);
31
32 -- COM item user defined attributes --
33 l_minimum_price NUMBER;
34 l_maximum_price NUMBER;
35 l_targeted_min_age NUMBER;
36 l_targeted_max_age NUMBER;
37 l_end_t NUMBER;
38 l_start_t NUMBER;
39
40 -- COM component user defined attributes --
41 l_max_cardinality NUMBER;
42 l_min_cardinality NUMBER;
43 l_default_cardinality NUMBER;
44 l_min NUMBER;
45 l_max NUMBER;
46 l_default_value NUMBER;
47
48 -- COM item user defined attributes --
49 l_minimum_price_exists BOOLEAN := FALSE;
50 l_maximum_price_exists BOOLEAN := FALSE;
51 l_targeted_min_age_exists BOOLEAN := FALSE;
52 l_targeted_max_age_exists BOOLEAN := FALSE;
53 l_end_t_exists BOOLEAN := FALSE;
54 l_start_t_exists BOOLEAN := FALSE;
55
56 -- COM component user defined attributes --
57 l_max_cardinality_exists BOOLEAN := FALSE;
58 l_min_cardinality_exists BOOLEAN := FALSE;
59 l_default_cardinality_exists BOOLEAN := FALSE;
60 l_max_exists BOOLEAN := FALSE;
61 l_min_exists BOOLEAN := FALSE;
62 l_default_value_exists BOOLEAN := FALSE;
63
64 -- error --
65 l_error_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY := EGO_COL_NAME_VALUE_PAIR_ARRAY();
66 l_error_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
67
68 l_appl_id NUMBER;
69 l_attr_grp_display_name VARCHAR2(60);
70
71 -- Cursor to derive display name
72 Cursor c_get_attr_grp_display_name(p_application_id in number
73 ,p_descriptive_flexfield_name in varchar2
74 ,p_attr_grp_name in varchar2) is
75 Select descriptive_flex_context_name
76 from fnd_descr_flex_contexts_vl
77 where application_id = p_application_id
78 and descriptive_flexfield_name = p_descriptive_flexfield_name
79 and descriptive_flex_context_code = p_attr_grp_name;
80
81 BEGIN
82
83 -- Initialize Return Status
84 x_return_status := 'S';
85 x_error_messages := EGO_COL_NAME_VALUE_PAIR_ARRAY();
86 l_attrgrp_name := p_attr_group_name;
87
88 IF (Is_Attribute_Group_Telco(l_attrgrp_name,p_attr_group_type)) THEN
89
90 IF ( p_attr_group_type = 'EGO_ITEMMGMT_GROUP') THEN
91
92 l_appl_id := 431;
93
94 -- get attribute group information
95 l_attr_group_type := p_attr_group_type;
96 l_attr_group_id := p_attr_group_id;
97
98 -- get attribute display name
99 OPEN c_get_attr_grp_display_name(l_appl_id,p_attr_group_type,l_attrgrp_name);
100 FETCH c_get_attr_grp_display_name into l_attr_grp_display_name;
101 CLOSE c_get_attr_grp_display_name;
102
103 -- create an array list with Telco attribute groups
104 -- check whether the attribute group exists in the array list or not
105 -- if attribute group exists in the array list then only do validations
106
107 IF (p_attr_name_value_pairs.count > 0) THEN
108 FOR j IN p_attr_name_value_pairs.FIRST .. p_attr_name_value_pairs.LAST
109 LOOP
110 l_curr_data_element := p_attr_name_value_pairs(j);
111 l_curr_data_id := l_curr_data_element.ROW_IDENTIFIER;
112 l_curr_data_attr_name := l_curr_data_element.ATTR_NAME;
113
114 -- not sure whether row_identifier should be same
115 -- for ego_user_attr_row_table and ego_user_attr_data_table
116 --IF(l_curr_row_id = l_curr_data_id) THEN
117 IF(l_curr_data_attr_name = 'Minimum_Price') THEN
118 l_minimum_price := l_curr_data_element.ATTR_VALUE_NUM;
119 l_minimum_price_exists := TRUE;
120 ELSIF (l_curr_data_attr_name = 'Maximum_Price') THEN
121 l_maximum_price := l_curr_data_element.ATTR_VALUE_NUM;
122 l_maximum_price_exists := TRUE;
123 ELSIF (l_curr_data_attr_name = 'Targeted_Min_Age') THEN
124 l_targeted_min_age := l_curr_data_element.ATTR_VALUE_NUM;
125 l_targeted_min_age_exists := TRUE;
126 ELSIF (l_curr_data_attr_name = 'Targeted_Max_Age') THEN
127 l_targeted_max_age := l_curr_data_element.ATTR_VALUE_NUM;
128 l_targeted_max_age_exists := TRUE;
129 ELSIF (l_curr_data_attr_name = 'End_T') THEN
130 l_end_t := l_curr_data_element.ATTR_VALUE_NUM;
131 l_end_t_exists := TRUE;
132 ELSIF (l_curr_data_attr_name = 'Start_T') THEN
133 l_start_t := l_curr_data_element.ATTR_VALUE_NUM;
134 l_start_t_exists := TRUE;
135 END IF;
136 END LOOP;
137 END IF;
138
139 -- validate the user defined attributes
140 -- if one of the attributes value is NULL then get it from database
141 IF ( l_attrgrp_name = 'COM_Pricing_Price_Lists' ) THEN
142
143 IF (NOT l_minimum_price_exists) THEN
144 l_minimum_price := Get_Attr_Value_From_db
145 (l_attr_group_id
146 ,l_attr_group_type
147 ,l_attrgrp_name
148 ,'Minimum_Price'
149 ,p_attr_name_value_pairs
150 ,p_pk_column_name_value_pairs
151 );
152 l_minimum_price := to_number(l_minimum_price);
153 ELSIF (NOT l_maximum_price_exists) THEN
154 l_maximum_price := Get_Attr_Value_From_db
155 (l_attr_group_id
156 ,l_attr_group_type
157 ,l_attrgrp_name
158 ,'Maximum_Price'
159 ,p_attr_name_value_pairs
160 ,p_pk_column_name_value_pairs
161 );
162 l_maximum_price := to_number(l_maximum_price);
163 END IF;
164
165 -- validate attributes
166 IF ((l_minimum_price IS NOT NULL) AND (l_maximum_price IS NOT NULL)) THEN
167 IF (l_minimum_price > l_maximum_price) THEN
168 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_GROUP_NAME', l_attr_grp_display_name);
169 l_error_name_value_pairs.EXTEND();
170 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
171
172 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ERROR_MESSAGE_NAME', 'EGO_COM_INVLD_MIN_MAX_PRICE');
173 l_error_name_value_pairs.EXTEND();
174 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
175
176 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Minimum_Price');
177 l_error_name_value_pairs.EXTEND();
178 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
179
180 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Maximum_Price');
181 l_error_name_value_pairs.EXTEND();
182 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
183
184 x_return_status := 'E';
185 END IF;
186 END IF;
187
188 ELSIF ( l_attrgrp_name = 'MDM_Product_Details_Marketing' ) THEN
189
190 IF (NOT l_targeted_min_age_exists) THEN
191 l_targeted_min_age := Get_Attr_Value_From_db
192 (l_attr_group_id
193 ,l_attr_group_type
194 ,l_attrgrp_name
195 ,'Targeted_Min_Age'
196 ,p_attr_name_value_pairs
197 ,p_pk_column_name_value_pairs
198 );
199 l_targeted_min_age := to_number(l_targeted_min_age);
200 ELSIF (NOT l_targeted_max_age_exists) THEN
201 l_targeted_max_age := Get_Attr_Value_From_db
202 (l_attr_group_id
203 ,l_attr_group_type
204 ,l_attrgrp_name
205 ,'Targeted_Max_Age'
206 ,p_attr_name_value_pairs
207 ,p_pk_column_name_value_pairs
208 );
209 l_targeted_max_age := to_number(l_targeted_max_age);
210 END IF;
211
212 -- validate attributes
213 IF ((l_targeted_min_age IS NOT NULL) AND (l_targeted_max_age IS NOT NULL)) THEN
214 IF ((l_targeted_min_age > l_targeted_max_age) OR (l_targeted_min_age = l_targeted_max_age)) THEN
215 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_GROUP_NAME', l_attr_grp_display_name);
216 l_error_name_value_pairs.EXTEND();
217 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
218
219 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ERROR_MESSAGE_NAME', 'EGO_COM_INVLD_TRGET_MINMAX_AGE');
220 l_error_name_value_pairs.EXTEND();
221 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
222
223 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Targeted_Min_Age');
224 l_error_name_value_pairs.EXTEND();
225 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
226
227 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Targeted_Max_Age');
228 l_error_name_value_pairs.EXTEND();
229 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
230
231 x_return_status := 'E';
232 END IF;
233 END IF;
234
235 ELSIF ( l_attrgrp_name = 'COM_Billing_Attributes_General' ) THEN
236
237 IF (NOT l_end_t_exists) THEN
238 l_end_t := Get_Attr_Value_From_db
239 (l_attr_group_id
240 ,l_attr_group_type
241 ,l_attrgrp_name
242 ,'End_T'
243 ,p_attr_name_value_pairs
244 ,p_pk_column_name_value_pairs
245 );
246 l_end_t := to_number(l_end_t);
247 ELSIF (NOT l_start_t_exists) THEN
248 l_start_t := Get_Attr_Value_From_db
249 (l_attr_group_id
250 ,l_attr_group_type
251 ,l_attrgrp_name
252 ,'Start_T'
253 ,p_attr_name_value_pairs
254 ,p_pk_column_name_value_pairs
255 );
256 l_start_t := to_number(l_start_t);
257 END IF;
258
259 -- validate attributes
260 IF ((l_start_t IS NOT NULL) AND (l_end_t IS NOT NULL)) THEN
261 IF ((l_start_t > l_end_t) OR (l_start_t = l_end_t)) THEN
262 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_GROUP_NAME', l_attr_grp_display_name);
263 l_error_name_value_pairs.EXTEND();
264 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
265
266 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ERROR_MESSAGE_NAME', 'EGO_COM_INVLD_START_END_T');
267 l_error_name_value_pairs.EXTEND();
268 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
269
270 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'End_T');
271 l_error_name_value_pairs.EXTEND();
272 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
273
274 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Start_T');
275 l_error_name_value_pairs.EXTEND();
276 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
277
278 x_return_status := 'E';
279 END IF;
280 END IF;
281
282 END IF;
283
284 ELSIF ( p_attr_group_type = 'BOM_COMPONENTMGMT_GROUP') THEN -- Attribute Group Type
285
286 -- get attribute group information
287 l_attr_group_type := p_attr_group_type;
288 l_attr_group_id := p_attr_group_id;
289 l_appl_id := 702;
290
291 -- get attribute display name
292 OPEN c_get_attr_grp_display_name(l_appl_id,p_attr_group_type,l_attrgrp_name);
293 FETCH c_get_attr_grp_display_name into l_attr_grp_display_name;
294 CLOSE c_get_attr_grp_display_name;
295
296 -- create an array list with Telco attribute groups
297 -- check whether the attribute group exists in the array list or not
298 -- if attribute group exists in the array list then only do validations
299
300 IF (p_attr_name_value_pairs.count > 0) THEN
301 FOR j IN p_attr_name_value_pairs.FIRST .. p_attr_name_value_pairs.LAST
302 LOOP
303 l_curr_data_element := p_attr_name_value_pairs(j);
304 l_curr_data_id := l_curr_data_element.ROW_IDENTIFIER;
305 l_curr_data_attr_name := l_curr_data_element.ATTR_NAME;
306
307
308 IF(l_curr_data_attr_name = 'Max_Cardinality') THEN
309 l_max_cardinality := l_curr_data_element.ATTR_VALUE_NUM;
310 l_max_cardinality_exists := TRUE;
311 ELSIF (l_curr_data_attr_name = 'Min_Cardinality') THEN
312 l_min_cardinality := l_curr_data_element.ATTR_VALUE_NUM;
313 l_min_cardinality_exists := TRUE;
314 ELSIF (l_curr_data_attr_name = 'Default_Cardinality') THEN
315 l_default_cardinality := l_curr_data_element.ATTR_VALUE_NUM;
316 l_default_cardinality_exists := TRUE;
317 ELSIF (l_curr_data_attr_name = 'Min') THEN
318 l_min := l_curr_data_element.ATTR_VALUE_NUM;
319 l_min_exists := TRUE;
320 ELSIF (l_curr_data_attr_name = 'Max') THEN
321 l_max := l_curr_data_element.ATTR_VALUE_NUM;
322 l_max_exists := TRUE;
323 ELSIF (l_curr_data_attr_name = 'Default_Value') THEN
324 l_default_value := l_curr_data_element.ATTR_VALUE_NUM;
325 l_default_value_exists := TRUE;
326 END IF;
327 END LOOP;
328 END IF;
329
330 -- validate the user defined attributes
331 -- if one of the attributes value is NULL then get it from database
332 IF ( l_attrgrp_name = 'COM_Version_Structure' ) THEN
333
334 IF (NOT l_max_cardinality_exists) THEN
335 l_max_cardinality := Get_Attr_Value_From_db
336 (l_attr_group_id
337 ,l_attr_group_type
338 ,l_attrgrp_name
339 ,'Max_Cardinality'
340 ,p_attr_name_value_pairs
341 ,p_pk_column_name_value_pairs
342 );
343 l_max_cardinality := to_number(l_max_cardinality);
344 END IF;
345
346 IF (NOT l_min_cardinality_exists) THEN
347 l_min_cardinality := Get_Attr_Value_From_db
348 (l_attr_group_id
349 ,l_attr_group_type
350 ,l_attrgrp_name
351 ,'Min_Cardinality'
352 ,p_attr_name_value_pairs
353 ,p_pk_column_name_value_pairs
354 );
355 l_min_cardinality := to_number(l_min_cardinality);
356 END IF;
357
358 IF (NOT l_default_cardinality_exists) THEN
359 l_default_cardinality := Get_Attr_Value_From_db
360 (l_attr_group_id
361 ,l_attr_group_type
362 ,l_attrgrp_name
363 ,'Default_Cardinality'
364 ,p_attr_name_value_pairs
365 ,p_pk_column_name_value_pairs
366 );
367 l_default_cardinality := to_number(l_default_cardinality);
368 END IF;
369
370 -- validate attributes
371 IF ((l_min_cardinality is not null AND l_default_cardinality is not null AND l_min_cardinality > l_default_cardinality) OR
372 (l_default_cardinality is not null AND l_max_cardinality is not null AND l_default_cardinality > l_max_cardinality ) OR
373 (l_min_cardinality is not null AND l_max_cardinality is not null AND l_min_cardinality > l_max_cardinality )) THEN
374
375 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_GROUP_NAME', l_attr_grp_display_name);
376 l_error_name_value_pairs.EXTEND();
377 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
378
379 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ERROR_MESSAGE_NAME', 'EGO_COM_VS_CARDINALITY_VALDN');
380 l_error_name_value_pairs.EXTEND();
381 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
382
383 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Min_Cardinality');
384 l_error_name_value_pairs.EXTEND();
385 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
386
387 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Max_Cardinality');
388 l_error_name_value_pairs.EXTEND();
389 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
390
391 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Default_Cardinality');
392 l_error_name_value_pairs.EXTEND();
393 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
394
395 x_return_status := 'E';
396 END IF;
397
398 ELSIF ( l_attrgrp_name = 'COM_Prod_Promotions_Components' ) THEN
399
400 IF (NOT l_min_exists) THEN
401 l_min := Get_Attr_Value_From_db
402 (l_attr_group_id
403 ,l_attr_group_type
404 ,l_attrgrp_name
405 ,'Min'
406 ,p_attr_name_value_pairs
407 ,p_pk_column_name_value_pairs
408 );
409 l_min := to_number(l_min);
410 END IF;
411
412 IF (NOT l_max_exists) THEN
413 l_max := Get_Attr_Value_From_db
414 (l_attr_group_id
415 ,l_attr_group_type
416 ,l_attrgrp_name
417 ,'Max'
418 ,p_attr_name_value_pairs
419 ,p_pk_column_name_value_pairs
420 );
421 l_max := to_number(l_max);
422 END IF;
423
424 IF (NOT l_default_value_exists) THEN
425 l_default_value := Get_Attr_Value_From_db
426 (l_attr_group_id
427 ,l_attr_group_type
428 ,l_attrgrp_name
429 ,'Default_Value'
430 ,p_attr_name_value_pairs
431 ,p_pk_column_name_value_pairs
432 );
433 l_default_value := to_number(l_default_value);
434 END IF;
435
436 -- validate attributes
437 IF ((l_min is not null AND l_default_value is not null AND l_min > l_default_value) OR
438 (l_default_value is not null AND l_max is not null AND l_default_value > l_max ) OR
439 (l_min is not null AND l_max is not null AND l_min > l_max )) THEN
440
441 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_GROUP_NAME', l_attr_grp_display_name);
442 l_error_name_value_pairs.EXTEND();
443 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
444
445 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ERROR_MESSAGE_NAME','EGO_COM_PROD_PROMO_VALDN');
446 l_error_name_value_pairs.EXTEND();
447 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
448
449 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Min');
450 l_error_name_value_pairs.EXTEND();
451 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
452
453 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Max');
454 l_error_name_value_pairs.EXTEND();
455 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
456
457 l_error_obj := EGO_COL_NAME_VALUE_PAIR_OBJ('ATTR_INT_NAME', 'Default_Value');
458 l_error_name_value_pairs.EXTEND();
459 l_error_name_value_pairs(l_error_name_value_pairs.LAST) := l_error_obj;
460
461 x_return_status := 'E';
462 END IF;
463
464 END IF;
465
466 END IF;
467
468 IF ( x_return_status = 'E' ) THEN
469 x_error_messages := l_error_name_value_pairs;
470 ELSE
471 x_error_messages := EGO_COL_NAME_VALUE_PAIR_ARRAY();
472 END IF;
473
474 END IF;
475
476 END Validate_Attributes;
477
478 -------------------------------------------------------------------------------------
479 -- This function gets data from database for the item and component uda --
480 -------------------------------------------------------------------------------------
481
482 FUNCTION Get_Attr_Value_From_db(p_attr_grp_id IN NUMBER
483 ,p_attr_grp_type IN VARCHAR2
484 ,p_attr_grp_name IN VARCHAR2
485 ,p_attr_name IN VARCHAR2
486 ,p_attr_name_value_pairs IN ego_user_attr_data_table
487 ,p_pk_column_name_value_pairs IN ego_col_name_value_pair_array
488 ) RETURN VARCHAR2
489 IS
490 l_appl_id NUMBER;
491 l_object_name VARCHAR2(30);
492 l_pk_col1 VARCHAR2(30);
493 l_pk_col2 VARCHAR2(30);
494 l_pk_col3 VARCHAR2(30);
495 l_pk_col4 VARCHAR2(30);
496 l_pk_value1 VARCHAR2(10);
497 l_pk_value2 VARCHAR2(10);
498 l_pk_value3 VARCHAR2(10);
499 l_pk_value4 VARCHAR2(10);
500 l_user_attr_val VARCHAR2(30);
501 l_object_id NUMBER;
502 l_attr_group_metadata_obj EGO_ATTR_GROUP_METADATA_OBJ;
503 l_ext_table_metadata_obj EGO_EXT_TABLE_METADATA_OBJ;
504 l_data_level_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
505 l_extension_id NUMBER;
506 l_index NUMBER;
507 l_attr_grp_id NUMBER;
508
509 Cursor c_get_attr_grp_id(p_application_id in number
510 ,p_descriptive_flexfield_name in varchar2
511 ,p_attr_grp_name in varchar2) is
512 Select attr_group_id
513 from ego_fnd_dsc_flx_ctx_ext
514 where application_id = p_application_id
515 and descriptive_flexfield_name = p_descriptive_flexfield_name
516 and descriptive_flex_context_code = p_attr_grp_name;
517
518 BEGIN
519
520 IF (p_attr_grp_type = 'EGO_ITEMMGMT_GROUP') THEN
521
522 l_appl_id := 431;
523 l_object_name := 'EGO_ITEM';
524 l_pk_col1 := 'INVENTORY_ITEM_ID';
525 l_pk_col2 := 'ORGANIZATION_ID';
526 l_pk_col3 := 'REVISION_ID';
527
528 -- Derive Attr Group Id
529 IF (p_attr_grp_id is NULL) THEN
530 -- Derive the Attribute Group Id
531 OPEN c_get_attr_grp_id(l_appl_id,p_attr_grp_type,p_attr_grp_name);
532 FETCH c_get_attr_grp_id into l_attr_grp_id;
533 CLOSE c_get_attr_grp_id;
534 ELSE
535 l_attr_grp_id := p_attr_grp_id;
536 END IF;
537
538 -- get pk values - inventory_item_id, organization_id, revision_id
539 l_index := p_pk_column_name_value_pairs.FIRST;
540 While (l_index IS NOT NULL)
541 LOOP
542 IF ((p_pk_column_name_value_pairs(l_index).NAME IS NOT NULL) AND (p_pk_column_name_value_pairs(l_index).NAME = 'INVENTORY_ITEM_ID')) THEN
543 l_pk_value1 := p_pk_column_name_value_pairs(l_index).VALUE;
544 END IF;
545 IF ((p_pk_column_name_value_pairs(l_index).NAME IS NOT NULL) AND (p_pk_column_name_value_pairs(l_index).NAME = 'ORGANIZATION_ID')) THEN
546 l_pk_value2 := p_pk_column_name_value_pairs(l_index).VALUE;
547 END IF;
548 IF ((p_pk_column_name_value_pairs(l_index).NAME IS NOT NULL) AND (p_pk_column_name_value_pairs(l_index).NAME = 'REVISION_ID')) THEN
549 l_pk_value3 := p_pk_column_name_value_pairs(l_index).VALUE;
550 END IF;
551 l_index := p_pk_column_name_value_pairs.NEXT(l_index);
552 END LOOP;
553
554 ELSIF (p_attr_grp_type = 'BOM_COMPONENTMGMT_GROUP') THEN
555
556 l_appl_id := 702;
557 l_object_name := 'BOM_COMPONENTS';
558 l_pk_col1 := 'BILL_SEQUENCE_ID';
559 l_pk_col2 := 'STRUCTURE_TYPE_ID';
560 l_pk_col3 := 'COMPONENT_SEQUENCE_ID';
561
562 -- Derive Attr Group Id
563 IF (p_attr_grp_id is NULL) THEN
564 -- Derive the Attribute Group Id
565 OPEN c_get_attr_grp_id(l_appl_id,p_attr_grp_type,p_attr_grp_name);
566 FETCH c_get_attr_grp_id into l_attr_grp_id;
567 CLOSE c_get_attr_grp_id;
568 ELSE
569 l_attr_grp_id := p_attr_grp_id;
570 END IF;
571
572 -- get pk values - bill_sequence_id, structure_type_id, component_sequence_id
573 l_index := p_pk_column_name_value_pairs.FIRST;
574 While (l_index IS NOT NULL)
575 LOOP
576 IF ((p_pk_column_name_value_pairs(l_index).NAME IS NOT NULL) AND (p_pk_column_name_value_pairs(l_index).NAME = 'BILL_SEQUENCE_ID')) THEN
577 l_pk_value1 := p_pk_column_name_value_pairs(l_index).VALUE;
578 END IF;
579 IF ((p_pk_column_name_value_pairs(l_index).NAME IS NOT NULL) AND (p_pk_column_name_value_pairs(l_index).NAME = 'STRUCTURE_TYPE_ID')) THEN
580 l_pk_value2 := p_pk_column_name_value_pairs(l_index).VALUE;
581 END IF;
582 IF ((p_pk_column_name_value_pairs(l_index).NAME IS NOT NULL) AND (p_pk_column_name_value_pairs(l_index).NAME = 'COMPONENT_SEQUENCE_ID')) THEN
583 l_pk_value3 := p_pk_column_name_value_pairs(l_index).VALUE;
584 END IF;
585 IF ((p_pk_column_name_value_pairs(l_index).NAME IS NOT NULL) AND (p_pk_column_name_value_pairs(l_index).NAME = 'EXTENSION_ID')) THEN
586 l_extension_id := p_pk_column_name_value_pairs(l_index).VALUE;
587 END IF;
588 l_index := p_pk_column_name_value_pairs.NEXT(l_index);
589 END LOOP;
590
591 END IF;
592
593 -- Derive the Extension for MR AG only if it is null
594
595
596 l_attr_group_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata
597 ( p_attr_group_id => l_attr_grp_id );
598
599
600 IF (l_extension_id IS NULL) THEN
601
602 IF (p_attr_grp_type = 'EGO_ITEMMGMT_GROUP') THEN
603
604 l_object_id := EGO_USER_ATTRS_DATA_PVT.Get_Object_Id_From_Name('EGO_ITEM');
605 l_ext_table_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Get_Ext_Table_Metadata(p_object_id => l_object_id);
606
607 l_data_level_name_value_pairs :=
608 EGO_COL_NAME_VALUE_PAIR_ARRAY(
609 EGO_COL_NAME_VALUE_PAIR_OBJ( 'REVISION_ID', l_pk_value3 )
610 );
611
612 ELSIF (p_attr_grp_type = 'BOM_COMPONENTMGMT_GROUP') THEN
613
614 l_object_id := EGO_USER_ATTRS_DATA_PVT.Get_Object_Id_From_Name('BOM_COMPONENTS');
615 l_ext_table_metadata_obj := EGO_USER_ATTRS_COMMON_PVT.Get_Ext_Table_Metadata(p_object_id => l_object_id);
616
617 l_data_level_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY();
618
619 END IF;
620
621
622 l_extension_id := EGO_USER_ATTRS_DATA_PVT.Get_Extension_Id_For_Row
623 ( p_attr_group_metadata_obj => l_attr_group_metadata_obj
624 , p_ext_table_metadata_obj => l_ext_table_metadata_obj
625 , p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
626 , p_data_level => NULL -- p_data_level
627 , p_data_level_name_value_pairs => l_data_level_name_value_pairs
628 , p_attr_name_value_pairs => p_attr_name_value_pairs
629 );
630
631
632 IF (l_extension_id is NOT NULL) THEN
633 l_pk_col4 := 'EXTENSION_ID';
634 l_pk_value4 := to_char(l_extension_id);
635 END IF;
636
637 ELSE
638
639 l_pk_col4 := 'EXTENSION_ID';
640 l_pk_value4 := to_char(l_extension_id);
641
642 END IF;
643
644 l_user_attr_val := EGO_USER_ATTRS_DATA_PVT.Get_User_Attr_Val (
645 p_appl_id => l_appl_id
646 ,p_attr_grp_type => p_attr_grp_type
647 ,p_attr_grp_name => p_attr_grp_name
648 ,p_attr_name => p_attr_name
649 ,p_object_name => l_object_name
650 ,p_pk_col1 => l_pk_col1
651 ,p_pk_col2 => l_pk_col2
652 ,p_pk_col3 => l_pk_col3
653 ,p_pk_col4 => l_pk_col4
654 ,p_pk_value1 => l_pk_value1
655 ,p_pk_value2 => l_pk_value2
656 ,p_pk_value3 => l_pk_value3
657 ,p_pk_value4 => l_pk_value4
658 );
659 RETURN l_user_attr_val;
660
661 END Get_Attr_Value_From_db;
662
663 -----------------------------------------------------------------------
664 -- This function checks whether the the attribute group is PIM --
665 -- Telco attribute group or not --
666 -----------------------------------------------------------------------
667
668 FUNCTION Is_Attribute_Group_Telco(p_attr_grp_name IN VARCHAR2,p_attr_grp_type IN VARCHAR2) RETURN BOOLEAN
669 IS
670
671 TYPE com_itemattr_grps IS VARRAY(6) OF VARCHAR2(30);
672 TYPE com_bomattr_grps IS VARRAY(6) OF VARCHAR2(30);
673
674 Com_ItemAttrGrps com_itemattr_grps;
675 Com_BOMAttrGrps com_bomattr_grps;
676
677 l_attr_grp_found BOOLEAN := FALSE;
678
679 BEGIN
680
681 Com_ItemAttrGrps := com_itemattr_grps('COM_Pricing_Price_Lists'
682 , 'MDM_Product_Details_Marketing'
683 , 'COM_Billing_Attributes_General');
684
685 Com_BOMAttrGrps := com_bomattr_grps('COM_Version_Structure'
686 , 'COM_Prod_Promotions_Components');
687
688 FOR i IN Com_ItemAttrGrps.FIRST .. Com_ItemAttrGrps.LAST
689 LOOP
690 IF (Com_ItemAttrGrps(i) = p_attr_grp_name and p_attr_grp_type = 'EGO_ITEMMGMT_GROUP') THEN
691 l_attr_grp_found := TRUE;
692 EXIT;
693 END IF;
694 END LOOP;
695
696 FOR i IN Com_BOMAttrGrps.FIRST .. Com_BOMAttrGrps.LAST
697 LOOP
698 IF (Com_BOMAttrGrps(i) = p_attr_grp_name and p_attr_grp_type = 'BOM_COMPONENTMGMT_GROUP') THEN
699 l_attr_grp_found := TRUE;
700 EXIT;
701 END IF;
702 END LOOP;
703
704 RETURN l_attr_grp_found;
705
706 END Is_Attribute_Group_Telco;
707
708
709 -----------------------------------------------------------------------
710 -- Procedure to validate Component Attribute Default Values --
711 -- Validation is done for only Single Row AG's since --
712 -- defaulting is not done for MR AG's as per current functionality --
713 -----------------------------------------------------------------------
714 PROCEDURE Validate_Default_CompAttr(
715 p_pk_column_name_value_pairs IN ego_col_name_value_pair_array DEFAULT NULL
716 ,x_return_status OUT NOCOPY VARCHAR2
717 ,x_error_messages OUT NOCOPY ego_col_name_value_pair_array
718 )IS
719
720
721 l_object_id NUMBER;
722 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE;
723 l_bill_sequence_id NUMBER;
724 l_structure_type_id NUMBER;
725 l_component_sequence_id NUMBER;
726 l_index NUMBER;
727 --
728 -- Cursor to derive attribute groups
729 --
730 Cursor c_get_attr_grp(p_structure_type_id in number,
731 p_object_id in number) is
732 Select a.attr_group_id
733 ,a.attr_group_name
734 ,a.attr_group_type
735 ,b.multi_row
736 from ego_obj_attr_grp_assocs_v a,
737 ego_fnd_dsc_flx_ctx_ext b
738 where a.object_id = p_object_id
739 and a.classification_code = to_char(p_structure_type_id)
740 and a.data_level_int_name = 'COMPONENTS_LEVEL'
741 and a.attr_group_id = b.attr_group_id;
742
743
744 BEGIN
745 -- Initialize Return Status
746 x_return_status := 'S';
747 x_error_messages := EGO_COL_NAME_VALUE_PAIR_ARRAY();
748 l_attr_name_value_pairs := EGO_USER_ATTR_DATA_TABLE();
749
750 -- get pk values - bill_sequence_id, structure_type_id, component_sequence_id
751 l_index := p_pk_column_name_value_pairs.FIRST;
752 While (l_index IS NOT NULL)
753 LOOP
754 IF ((p_pk_column_name_value_pairs(l_index).NAME IS NOT NULL) AND (p_pk_column_name_value_pairs(l_index).NAME = 'STRUCTURE_TYPE_ID')) THEN
755 l_structure_type_id := p_pk_column_name_value_pairs(l_index).VALUE;
756 exit;
757 END IF;
758 l_index := p_pk_column_name_value_pairs.NEXT(l_index);
759 END LOOP;
760
761 -- Derive the object id
762 l_object_id := EGO_USER_ATTRS_DATA_PVT.Get_Object_Id_From_Name('BOM_COMPONENTS');
763
764 -- Attribute Groups associated with the Structure
765 FOR c_attr_grp IN c_get_attr_grp(l_structure_type_id,l_object_id)
766 LOOP
767
768 IF (Is_Attribute_Group_Telco(c_attr_grp.attr_group_name,c_attr_grp.attr_group_type) and
769 c_attr_grp.multi_row = 'N') THEN
770
771 -- Since Attribute Values are derived in Validate_Attribute procedure
772 -- passing null values in l_attr_name_value_pairs parameter
773
774 Validate_Attributes (
775 p_attr_group_type => 'BOM_COMPONENTMGMT_GROUP'
776 ,p_attr_group_name => c_attr_grp.attr_group_name
777 ,p_attr_group_id => c_attr_grp.attr_group_id
778 ,p_attr_name_value_pairs => l_attr_name_value_pairs
779 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
780 ,x_return_status => x_return_status
781 ,x_error_messages => x_error_messages);
782
783 IF ( x_return_status = 'E' ) THEN
784 return;
785 ELSE
786 x_error_messages := EGO_COL_NAME_VALUE_PAIR_ARRAY();
787 END IF;
788
789 END IF;
790
791 END LOOP;
792
793 END;
794
795 END EGO_COM_ATTR_VALIDATION;