[Home] [Help]
PACKAGE BODY: APPS.EGO_TRANSACTION_ATTRS_PVT
Source
1 PACKAGE BODY EGO_TRANSACTION_ATTRS_PVT AS
2 /* $Header: EGOVITAB.pls 120.13.12020000.3 2012/11/26 20:29:04 trudave ship $ */
3 ---------------------------------------------------------------
4 -- Global Variables and Constants --
5 ---------------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_TRANSACTION_ATTRS_PVT';
7 G_APP_NAME CONSTANT VARCHAR2(3) := 'EGO';
8
9 G_CHAR_DATA_TYPE CONSTANT VARCHAR2(1) := 'C';
10 G_TL_DATA_TYPE CONSTANT VARCHAR2(1) := 'A';
11 G_NUMBER_DATA_TYPE CONSTANT VARCHAR2(1) := 'N';
12 G_DATE_DATA_TYPE CONSTANT VARCHAR2(1) := 'X';
13 G_DATE_TIME_DATA_TYPE CONSTANT VARCHAR2(1) := 'Y';
14
15 G_CURRENT_USER_ID NUMBER := FND_GLOBAL.User_Id;
16 G_CURRENT_LOGIN_ID NUMBER := FND_GLOBAL.Login_Id;
17 G_APPLICATION_ID NUMBER := 431;
18
19 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
20 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
21
22 ---------------------------------------------------------------
23 -- API Return statuses. --
24 ---------------------------------------------------------------
25 G_STATUS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
26 G_STATUS_ERROR CONSTANT VARCHAR2(1) := 'E';
27
28 /* Create transaction attribute API*/
29 PROCEDURE Create_Transaction_Attribute (
30 p_api_version IN NUMBER,
31 p_tran_attrs_tbl IN EGO_TRAN_ATTR_TBL,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2)
35 IS
36 /* User Defined Exception object*/
37 e_ta_int_name_exist EXCEPTION;
38 e_ta_disp_name_exist EXCEPTION;
39 e_ta_sequence_exist EXCEPTION;
40 e_ta_default_value_null EXCEPTION;
41 e_ag_create EXCEPTION;
42 e_ta_create EXCEPTION;
43 e_ta_association EXCEPTION;
44 e_vs_data_type EXCEPTION;
45 e_vs_not_versioned EXCEPTION;
46 e_ta_int_name_invalidchars EXCEPTION;
47
48 /* Declaring local parameters*/
49
50 l_attr_desc VARCHAR2(100); --confirm about size
51 l_count NUMBER:=0;
52 l_ag_seq_value NUMBER;
53 l_ag_int_name VARCHAR2(100); --confirm about size
54 l_ag_disp_name VARCHAR2(100); --confirm about size
55 l_ag_desc VARCHAR2(100); --confirm about size
56 l_ag_type VARCHAR2(30) := 'EGO_ITEM_TRANS_ATTR_GROUP';
57 l_attr_group_id NUMBER;
58 l_column VARCHAR2(30):=NULL;
59
60 l_return_status VARCHAR2(1);
61 l_errorcode NUMBER;
62 l_msg_count NUMBER;
63 l_msg_data VARCHAR2(2000);
64
65
66 l_association_id NUMBER;
67 l_attr_id NUMBER:=0;
68 l_attr_sequence EGO_ATTRS_V.SEQUENCE%TYPE;
69 l_value_set_id NUMBER;
70 l_uom_class VARCHAR2(10);
71 l_default_value VARCHAR2(2000);
72 l_rejectedvalue VARCHAR2(2000);
73 l_required VARCHAR2(1);
74 l_readonlyflag VARCHAR2(1);
75 l_hiddenflag VARCHAR2(1);
76 l_searchable VARCHAR2(1);
77 l_checkeligibility VARCHAR2(1);
78 l_inventoryitemid NUMBER;
79 l_organizationid NUMBER;
80 l_metadatalevel VARCHAR2(10);
81 l_programapplicationid NUMBER;
82 l_programid NUMBER;
83 l_programupdatedate DATE;
84 l_requestid NUMBER;
85 l_item_cat_group_id EGO_TRANS_ATTR_VERS_B.ITEM_CATALOG_GROUP_ID%TYPE;
86 l_attr_name EGO_ATTRS_V.ATTR_NAME%TYPE:=NULL;
87 l_attr_disp_name EGO_ATTRS_V.ATTR_DISPLAY_NAME%TYPE:=NULL;
88 l_data_type VARCHAR2(1);
89 l_display VARCHAR2(1);
90
91 l_api_name CONSTANT VARCHAR2(30) := 'Create_Transaction_Attribute';
92 l_data_level_id NUMBER;
93 l_icc_version_number NUMBER;
94 l_revision_id NUMBER;
95 l_item_obj_id NUMBER;
96 l_versioned_value_set NUMBER:=0;
97 l_has_invalid_chars VARCHAR2(1);
98 l_is_column_indexed VARCHAR2(80);
99
100 BEGIN
101 --Reset all global variables
102 FND_MSG_PUB.Initialize;
103 FOR i IN p_tran_attrs_tbl.first..p_tran_attrs_tbl.last
104 LOOP
105
106 l_item_cat_group_id := p_tran_attrs_tbl(i).ItemCatalogGroupId;
107 l_attr_name := p_tran_attrs_tbl(i).AttrName;
108 l_attr_disp_name := p_tran_attrs_tbl(i).AttrDisplayName;
109 l_attr_sequence := p_tran_attrs_tbl(i).SEQUENCE;
110
111 --l_association_id := p_tran_attrs_tbl(i).associationid;
112 --l_attr_id := p_tran_attrs_tbl(i).attrid;
113 l_value_set_id := p_tran_attrs_tbl(i).valuesetid;
114 l_uom_class := p_tran_attrs_tbl(i).uomclass;
115 l_default_value := p_tran_attrs_tbl(i).defaultvalue;
116 l_rejectedvalue := p_tran_attrs_tbl(i).rejectedvalue;
117 l_required := p_tran_attrs_tbl(i).requiredflag;
118 l_readonlyflag := p_tran_attrs_tbl(i).readonlyflag;
119 l_hiddenflag := p_tran_attrs_tbl(i).hiddenflag;
120 l_searchable := p_tran_attrs_tbl(i).searchableflag;
121 l_checkeligibility := p_tran_attrs_tbl(i).checkeligibility;
122 l_inventoryitemid := p_tran_attrs_tbl(i).inventoryitemid;
123 l_organizationid := p_tran_attrs_tbl(i).organizationid;
124 l_metadatalevel := p_tran_attrs_tbl(i).metadatalevel;
125 l_programapplicationid := p_tran_attrs_tbl(i).programapplicationid;
126 l_programid := p_tran_attrs_tbl(i).programid;
127 l_programupdatedate := p_tran_attrs_tbl(i).programupdatedate;
128 l_requestid := p_tran_attrs_tbl(i).requestid;
129
130 l_icc_version_number:= p_tran_attrs_tbl(i).icc_version_number;
131 l_revision_id := p_tran_attrs_tbl(i).revision_id;
132
133 l_data_type := p_tran_attrs_tbl(i).datatype;
134 l_display := p_tran_attrs_tbl(i).displayas;
135
136
137
138 --
139 -- Bug 9980051. Passing version number parameter.
140 -- For MIB we need to validate the attributes
141 -- and its other properties against the version
142 -- created and not against the default version.
143 -- sreharih. Tue Apr 19 11:06:55 PDT 2011
144 --
145
146 IF (Check_TA_IS_INVALID (p_item_cat_group_id => l_item_cat_group_id,
147 p_icc_version_number => p_tran_attrs_tbl(i).ICC_VERSION_NUMBER, -- bug 9980051
148 p_attr_id => l_attr_id,
149 p_attr_name => l_attr_name)) THEN
150
151 RAISE e_ta_int_name_exist;
152 END IF;
153 /*check whether the internal has some specail characters*/
154 has_invalid_char(p_internal_name => l_attr_name,
155 x_has_invalid_chars => l_has_invalid_chars);
156 IF( l_has_invalid_chars ='Y') THEN
157 RAISE e_ta_int_name_invalidchars;
158 END IF ;
159
160 /* Check if att_disp_name already exist*/
161 IF (Check_TA_IS_INVALID (p_item_cat_group_id => l_item_cat_group_id,
162 p_icc_version_number => p_tran_attrs_tbl(i).ICC_VERSION_NUMBER, -- bug 9980051
163 p_attr_id => l_attr_id,
164 p_attr_disp_name => l_attr_disp_name) ) THEN
165 RAISE e_ta_disp_name_exist;
166 END IF;
167
168 /* Check if sequence already exist*/
169 IF (Check_TA_IS_INVALID (p_item_cat_group_id => l_item_cat_group_id,
170 p_icc_version_number => p_tran_attrs_tbl(i).ICC_VERSION_NUMBER, -- bug 9980051
171 p_attr_id => l_attr_id,
172 p_attr_sequence => l_attr_sequence) ) THEN
173 RAISE e_ta_sequence_exist;
174 END IF;
175
176 /* Check for default value of a TA */
177 IF ( l_readonlyflag='Y' AND l_required ='Y' AND l_default_value IS NULL) THEN
178 RAISE e_ta_default_value_null;
179 END IF;
180
181 -------------------------------------------------------------------------------------
182 -- Make sure that if a Value Set was passed in, is a versioned value set --
183 -------------------------------------------------------------------------------------
184 IF (l_value_set_id IS NOT NULL) THEN
185
186 SELECT Count(*) CNT
187 INTO l_versioned_value_set
188 FROM EGO_FLEX_VALUESET_VERSION_B
189 WHERE FLEX_VALUE_SET_ID = l_value_set_id
190 AND VERSION_SEQ_ID>0;
191
192 IF (l_versioned_value_set=0) THEN
193 RAISE e_vs_not_versioned;
194 END IF;
195 --------------------------------------------------------------------------
196
197 IF (NOT Check_VS_Data_Type(l_value_set_id,l_data_type) ) THEN
198 RAISE e_vs_data_type;
199 END IF;
200
201 END IF;
202
203 BEGIN
204 SELECT EGO_TRANS_AG_S.NEXTVAL INTO l_ag_seq_value FROM dual;
205 EXCEPTION
206 WHEN OTHERS THEN
207 x_return_status := G_STATUS_ERROR;
208 x_msg_data := 'TA_SEQUENCE_NOT_EXIST';
209 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
210 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
211 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
212 FND_MESSAGE.Set_Token('SQL_ERR_MSG',x_msg_data );
213 FND_MSG_PUB.Add;
214 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
215 ,p_count => x_msg_count
216 ,p_data => x_msg_data);
217 END;
218
219 l_ag_int_name := 'EGO_TRANS_AG_'||l_ag_seq_value;
220 l_ag_disp_name := l_ag_int_name;
221 l_ag_desc := l_ag_int_name;
222
223
224 /* Create a attribute group to keep sync with existing framework*/
225 EGO_EXT_FWK_PUB.Create_Attribute_Group
226 ( p_api_version => 1.0,
227 p_application_id => G_APPLICATION_ID ,
228 p_attr_group_type => l_ag_type,
229 p_internal_name => l_ag_int_name,
230 p_display_name => l_ag_disp_name ,
231 p_attr_group_desc => l_ag_desc ,
232 p_security_type => NULL ,
233 p_multi_row_attrib_group => 'N' ,
234 p_variant_attrib_group => 'N' ,
235 p_num_of_cols => NULL,
236 p_num_of_rows => NULL,
237 p_owning_company_id => NULL,
238 p_view_privilege_id => NULL,
239 p_edit_privilege_id => NULL,
240 p_business_event_flag => 'N',
241 p_pre_business_event_flag => 'N',
242 p_init_msg_list => NULL,
243 p_commit => NULL,
244 x_attr_group_id => l_attr_group_id,
245 x_return_status => l_return_status,
246 x_errorcode => l_errorcode,
247 x_msg_count => l_msg_count,
248 x_msg_data => x_msg_data);
249
250 IF (l_return_status<> G_STATUS_SUCCESS) THEN
251 RAISE e_ag_create;
252 END IF;
253
254 /* Get data level id */
255 BEGIN
256 SELECT max(data_level_id) INTO l_data_level_id
257 FROM ego_data_level_b
258 WHERE application_id = G_APPLICATION_ID;
259 EXCEPTION
260 WHEN No_Data_Found THEN
261 x_return_status := G_STATUS_ERROR;
262 x_msg_data := 'TA_NO_DATA_LEVEL_FOUND';
263 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
264 FND_MESSAGE.Set_Token('SQL_ERR_MSG',x_msg_data );
265 FND_MSG_PUB.Add;
266 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
267 ,p_count => x_msg_count
268 ,p_data => x_msg_data);
269 END ;
270
271 /* INSERT record into ego_attr_group_dl*/
272 INSERT INTO ego_attr_group_dl
273 (attr_group_id
274 ,data_level_id
275 ,defaulting
276 ,view_privilege_id
277 ,edit_privilege_id
278 ,raise_pre_event
279 ,raise_post_event
280 ,created_by
281 ,creation_date
282 ,last_updated_by
283 ,last_update_date
284 ,last_update_login)
285 VALUES(l_attr_group_id,l_data_level_id,
286 null,null,null,'N','N',G_CURRENT_USER_ID,SYSDATE,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID);
287
288 /* Find out column name based on data type */
289 IF ( l_data_type = G_NUMBER_DATA_TYPE )
290 THEN
291 l_column := 'N_EXT_ATTR';
292 ELSIF (l_data_type = G_DATE_DATA_TYPE OR l_data_type = G_DATE_TIME_DATA_TYPE )
293 THEN
294 l_column := 'D_EXT_ATTR';
295 ELSIF (l_data_type = G_TL_DATA_TYPE )
296 THEN
297 l_column := 'TL_EXT_ATTR';
298 ELSE
299 l_column := 'C_EXT_ATTR' ;
300 END IF;
301 SELECT MEANING
302 INTO l_is_column_indexed
303 FROM FND_LOOKUP_VALUES
304 WHERE LOOKUP_TYPE = 'YES_NO'
305 AND LANGUAGE = USERENV('LANG')
306 AND VIEW_APPLICATION_ID = 0
307 AND LOOKUP_CODE = 'Y';
308
309 /* Call below API to create transaction atribute*/
310 EGO_EXT_FWK_PUB.Create_Attribute
311 ( p_api_version => 1.0
312 ,p_application_id => G_APPLICATION_ID
313 ,p_attr_group_type => l_ag_type
314 ,p_attr_group_name => l_ag_int_name
315 ,p_internal_name => l_attr_name
316 ,p_display_name => l_attr_disp_name
317 ,p_description => l_attr_desc
318 ,p_sequence => l_attr_sequence
319 ,p_data_type => l_data_type
320 ,p_required => l_required
321 ,p_searchable => l_searchable
322 ,p_column => l_column
323 ,p_is_column_indexed => l_is_column_indexed --'No' --- ToDo
324 ,p_value_set_id => l_value_set_id
325 ,p_info_1 => null
326 ,p_default_value => l_default_value
327 ,p_unique_key_flag => null
328 ,p_enabled => 'Y'
329 ,p_display => l_display
330 ,p_uom_class => l_uom_class
331 ,p_init_msg_list => null
332 ,p_commit => null
333 ,x_return_status => l_return_status
334 ,x_errorcode => l_errorcode
335 ,x_msg_count => l_msg_count
336 ,x_msg_data => x_msg_data
337 );
338
339 IF (l_return_status<>G_STATUS_SUCCESS) THEN
340 RAISE e_ta_create;
341 END IF;
342 BEGIN
343 SELECT object_id INTO l_item_obj_id FROM fnd_objects WHERE obj_name = 'EGO_ITEM';
344 EXCEPTION
345 WHEN No_Data_Found THEN
346 x_return_status := G_STATUS_ERROR;
347 x_msg_data := 'TA_NO_OBJECT_ID';
348 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
349 FND_MESSAGE.Set_Token('SQL_ERR_MSG',x_msg_data );
350 FND_MSG_PUB.Add;
351 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
352 ,p_count => x_msg_count
353 ,p_data => x_msg_data);
354 END;
355 EGO_EXT_FWK_PUB.Create_Association(
356 p_api_version => 1.0 ,
357 p_object_id => l_item_obj_id,
358 p_classification_code => l_item_cat_group_id ,
359 p_data_level => 'ITEM_REVISION_LEVEL',
360 p_application_id => G_APPLICATION_ID ,
361 p_attr_group_type => l_ag_type ,
362 p_attr_group_name => l_ag_int_name ,
363 p_enabled_flag => 'Y' ,
364 p_view_privilege_id => 0 ,
365 p_edit_privilege_id => 0 ,
366 x_association_id => l_association_id ,
367 x_return_status => l_return_status,
368 x_errorcode => l_errorcode,
369 x_msg_count => l_msg_count,
370 x_msg_data => x_msg_data);
371
372
373
374 SELECT ASSOCIATION_ID INTO l_association_id
375 FROM EGO_OBJ_AG_ASSOCS_B
376 WHERE CLASSIFICATION_CODE= l_item_cat_group_id
377 AND ATTR_GROUP_ID= l_attr_group_id
378 AND OBJECT_ID= l_item_obj_id;
379
380 IF (l_return_status<>G_STATUS_SUCCESS) THEN
381 RAISE e_ta_association;
382 END IF;
383
384 SELECT attr_id INTO l_attr_id
385 FROM EGO_FND_DF_COL_USGS_EXT
386 WHERE APPLICATION_ID = G_APPLICATION_ID
387 AND DESCRIPTIVE_FLEXFIELD_NAME = l_ag_type
388 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = l_ag_int_name
389 AND APPLICATION_COLUMN_NAME = l_column;
390
391
392 /* INSERTING values in tables*/
393 BEGIN
394
395 INSERT INTO EGO_TRANS_ATTR_VERS_B
396 (association_id,attr_id,icc_version_number,attr_display_name,"SEQUENCE",value_set_id,uom_class,
397 default_value,rejected_value,required_flag,readonly_flag,hidden_flag, searchable_flag,
398 check_eligibility,inventory_item_id,organization_id, revision_id,metadata_level,created_by,
399 creation_date,last_updated_by,last_update_date,last_update_login,program_application_id,
400 program_id,program_update_date,request_id,item_catalog_group_id)
401 VALUES(l_association_id,l_attr_id,l_icc_version_number,l_attr_disp_name,
402 l_attr_sequence,l_value_set_id,l_uom_class,l_default_value,l_rejectedvalue,l_required,l_readonlyflag,
403 l_hiddenflag,l_searchable,l_checkeligibility,l_inventoryitemid,l_organizationid,l_revision_id,
404 l_metadatalevel,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID,
405 l_programapplicationid,l_programid,l_programupdatedate,l_requestid,l_item_cat_group_id);
406 EXCEPTION
407 WHEN OTHERS THEN
408 x_return_status := G_STATUS_ERROR;
409 x_msg_data := 'TA_INSERT_FAILED';
410 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
411 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
412 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
413 FND_MESSAGE.Set_Token('SQL_ERR_MSG',SQLERRM);
414 FND_MSG_PUB.Add;
415 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
416 ,p_count => x_msg_count
417 ,p_data => x_msg_data);
418 END ;
419 END LOOP; /* FOR i IN p_tran_attrs_tbl.first..p_tran_attrs_tbl.last */
420 IF (Nvl(x_return_status,G_STATUS_SUCCESS) <>G_STATUS_ERROR )
421 THEN
422 x_return_status := G_STATUS_SUCCESS;
423 END IF;
424 EXCEPTION
425 WHEN e_ta_int_name_exist THEN
426 x_return_status := G_STATUS_ERROR;
427 FND_MESSAGE.Set_Name('EGO', 'EGO_EF_INTERNAL_NAME_UNIQUE');
428 /*SELECT * FROM fnd_new_messages WHERE message_name LIKE 'EGO_PLSQL_ERR%'*/
429 FND_MSG_PUB.Add;
430 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
431 ,p_count => x_msg_count
432 ,p_data => x_msg_data);
433 WHEN e_ta_disp_name_exist THEN
434 x_return_status := G_STATUS_ERROR;
435 FND_MESSAGE.Set_Name('EGO', 'EGO_TA_DISPLAY_NAME_UNIQUE');
436 FND_MSG_PUB.Add;
437 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
438 ,p_count => x_msg_count
439 ,p_data => x_msg_data);
440 WHEN e_ta_sequence_exist THEN
441 x_return_status := G_STATUS_ERROR;
442 FND_MESSAGE.Set_Name('EGO', 'EGO_EF_CR_ATTR_DUP_SEQ_ERR');
443 FND_MSG_PUB.Add;
444 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
445 ,p_count => x_msg_count
446 ,p_data => x_msg_data);
447 WHEN e_ta_default_value_null
448 THEN
449 x_return_status := G_STATUS_ERROR;
450 FND_MESSAGE.Set_Name('EGO', 'EGO_TA_DEFAULT_VALUE_NULL');
451 FND_MSG_PUB.Add;
452 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
453 ,p_count => x_msg_count
454 ,p_data => x_msg_data);
455 WHEN e_vs_not_versioned
456 THEN
457 x_return_status := G_STATUS_ERROR;
458 FND_MESSAGE.Set_Name('EGO', 'EGO_VALUE_SET_NOT_VERSIONED');
459 FND_MSG_PUB.Add;
460 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
461 ,p_count => x_msg_count
462 ,p_data => x_msg_data);
463 WHEN e_vs_data_type
464 THEN
465 x_return_status := G_STATUS_ERROR;
466 FND_MESSAGE.Set_Name('EGO', 'EGO_EF_CR_ATTR_VS_DT_ERR');
467 FND_MSG_PUB.Add;
468 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
469 ,p_count => x_msg_count
470 ,p_data => x_msg_data);
471
472 WHEN e_ag_create
473 THEN
474 x_return_status := G_STATUS_ERROR;
475 FND_MESSAGE.Set_Name('EGO', 'EGO_TA_AG_CREATE');
476 FND_MSG_PUB.Add;
477 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
478 ,p_count => x_msg_count
479 ,p_data => x_msg_data);
480 WHEN e_ta_create
481 THEN
482 x_return_status := G_STATUS_ERROR;
483 FND_MESSAGE.Set_Name('EGO', 'EGO_TA_CREATE_FAILED');
484 FND_MSG_PUB.Add;
485 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
486 ,p_count => x_msg_count
487 ,p_data => x_msg_data);
488 WHEN e_ta_association
489 THEN
490 x_return_status := G_STATUS_ERROR;
491 FND_MESSAGE.Set_Name('EGO', 'EGO_TA_ASSOC_FAILED');
492 FND_MSG_PUB.Add;
493 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
494 ,p_count => x_msg_count
495 ,p_data => x_msg_data);
496 WHEN e_ta_int_name_invalidchars
497 THEN
498 x_return_status := G_STATUS_ERROR;
499 FND_MESSAGE.Set_Name('EGO', 'EGO_EF_INTERNAL_NAME_TIP');
500 FND_MSG_PUB.Add;
501 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
502 ,p_count => x_msg_count
503 ,p_data => x_msg_data);
504
505 WHEN OTHERS THEN
506 x_return_status := G_STATUS_ERROR;
507 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
508 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
509 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
510 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
511 FND_MSG_PUB.Add;
512 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
513 ,p_count => x_msg_count
514 ,p_data => x_msg_data);
515 END Create_Transaction_Attribute;
516
517
518
519
520 --
521 -- Bug 9980051. Added version number parameter.
522 -- For MIB we need to validate the attributes
523 -- and its other properties against the version
524 -- created and not against the default version.
525 -- sreharih. Tue Apr 19 11:06:55 PDT 2011
526 --
527
528
529 --=================Check_TA_IS_INVALID===============--------
530 FUNCTION Check_TA_IS_INVALID (
531 p_item_cat_group_id IN NUMBER,
532 p_attr_id IN NUMBER,
533 p_attr_name IN VARCHAR2,
534 p_attr_disp_name IN VARCHAR2,
535 p_attr_sequence IN NUMBER,
536 p_icc_version_number IN NUMBER DEFAULT 0
537 )
538 RETURN BOOLEAN
539 IS
540
541 l_attr_id NUMBER;
542 l_attr_name VARCHAR2(80);
543 l_attr_disp_name VARCHAR2(80);
544 l_attr_sequence NUMBER;
545 l_ta_is_invalid BOOLEAN := FALSE;
546 --p_attr_id NUMBER :=2972 ; --parameter to test API
547 --p_item_cat_group_id NUMBER:=609 ; --parameter to test API
548
549 /**------Query to fetch all associated attribute with passed in ICC--------**/
550 CURSOR cur_list
551 IS
552 SELECT item_catalog_group_id,
553 icc_version_NUMBER ,
554 SEQUENCE ,
555 attr_display_name ,
556 attr_name ,
557 attr_id ,
558 lev
559 FROM
560 (SELECT versions.item_catalog_group_id,
561 versions.icc_version_NUMBER ,
562 versions.SEQUENCE ,
563 attrs.attr_display_name ,
564 attrs.attr_name ,
565 attrs.attr_id ,
566 hier.lev
567 FROM ego_obj_AG_assocs_b assocs ,
568 ego_attrs_v attrs ,
569 ego_attr_groups_v ag ,
570 EGO_TRANS_ATTR_VERS_B versions ,
571 mtl_item_catalog_groups_kfv icv ,
572 (SELECT item_catalog_group_id ,
573 LEVEL lev
574 FROM mtl_item_catalog_groups_b START
575 WITH item_catalog_group_id = p_item_cat_group_id CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
576 ) hier
577 WHERE ag.attr_group_type = 'EGO_ITEM_TRANS_ATTR_GROUP'
578 AND assocs.attr_group_id = ag.attr_group_id
579 AND assocs.classification_code = TO_CHAR(hier.item_catalog_group_id)
580 AND attrs.attr_group_name = ag.attr_group_name
581 AND TO_CHAR(icv.item_catalog_group_id) = assocs.classification_code
582 AND TO_CHAR(versions.association_id) = assocs.association_id
583 AND TO_CHAR(versions.item_catalog_group_id) = assocs.classification_code
584 AND attrs.attr_id = versions.attr_id
585 )
586 WHERE
587 (
588 (
589 LEV = 1
590 AND ICC_VERSION_NUMBER = p_icc_version_number -- bug 9980051
591 )
592 OR
593 (
594 LEV <> 1
595 AND
596 (
597 item_catalog_group_id, ICC_VERSION_NUMBER
598 )
599 IN
600 (SELECT item_catalog_group_id,
601 VERSION_SEQ_ID
602 FROM EGO_MTL_CATALOG_GRP_VERS_B
603 WHERE start_active_date <=
604 (SELECT NVL(start_active_date,SYSDATE)
605 FROM EGO_MTL_CATALOG_GRP_VERS_B
606 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
607 AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
608 )
609 AND NVL(end_active_date, sysdate) >=
610 (SELECT NVL(start_active_date,SYSDATE)
611 FROM EGO_MTL_CATALOG_GRP_VERS_B
612 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
613 AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
614 )
615 AND version_seq_id > 0
616 )
617 )
618 ); --end CURSOR cur_list
619
620
621 /**------Query to fetch overridden values for a transaction attribute------**/
622 CURSOR cur_metadata
623 IS
624 SELECT *
625 FROM
626 (SELECT *
627 FROM
628 (SELECT versions.item_catalog_group_id,
629 versions.ICC_VERSION_NUMBER ,
630 versions.ATTR_ID ,
631 versions.SEQUENCE ,
632 versions.attr_display_name ,
633 versions.metadata_level ,
634 attrs.attr_name ,
635 Hier.lev
636 FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
637 EGO_ATTRS_V ATTRS ,
638 (SELECT ITEM_CATALOG_GROUP_ID ,
639 LEVEL LEV
640 FROM MTL_ITEM_CATALOG_GROUPS_B START
641 WITH ITEM_CATALOG_GROUP_ID = p_item_cat_group_id CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
642 ) HIER
643 WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
644 AND attrs.attr_id = versions.attr_id
645 AND attrs.attr_group_type ='EGO_ITEM_TRANS_ATTR_GROUP'
646 AND versions.metadata_level ='ICC'
647 )
648 WHERE
649 (
650 (
651 LEV = 1
652 AND ICC_VERSION_number = p_icc_version_number -- bug 9980051
653 )
654 OR
655 (
656 LEV <> 1
657 AND
658 (
659 item_catalog_group_id, ICC_VERSION_NUMBER
660 )
661 IN
662 (SELECT item_catalog_group_id,
663 VERSION_SEQ_ID
664 FROM EGO_MTL_CATALOG_GRP_VERS_B
665 WHERE
666 (
667 item_catalog_group_id,start_active_date
668 )
669 IN
670 (SELECT item_catalog_group_id,
671 MAX(start_active_date) start_active_date
672 FROM EGO_MTL_CATALOG_GRP_VERS_B
673 WHERE NVL(end_active_date, sysdate) >=
674 (SELECT NVL(start_active_date,SYSDATE)
675 FROM EGO_MTL_CATALOG_GRP_VERS_B
676 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
677 AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
678 )
679 AND version_seq_id > 0
680
681 AND start_active_date <=
682 (SELECT NVL(start_active_date,SYSDATE)
683 FROM EGO_MTL_CATALOG_GRP_VERS_B
684 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
685 AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
686 )
687
688
689
690 GROUP BY item_catalog_group_id
691 HAVING MAX(start_active_date)<=
692 (SELECT NVL(start_active_date,SYSDATE)
693 FROM EGO_MTL_CATALOG_GRP_VERS_B
694 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
695 AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
696 )
697 )
698 )
699 )
700 )
701 )
702 WHERE
703 (
704 lev,attr_id
705 )
706 IN
707 (SELECT MIN(lev),
708 attr_id
709 FROM
710 (SELECT versions.item_catalog_group_id,
711 versions.ICC_VERSION_NUMBER ,
712 versions.ATTR_ID ,
713 versions.SEQUENCE ,
714 versions.attr_display_name ,
715 versions.metadata_level ,
716 Hier.lev
717 FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
718 (SELECT ITEM_CATALOG_GROUP_ID ,
719 LEVEL LEV
720 FROM MTL_ITEM_CATALOG_GROUPS_B
721 START WITH ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
722 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
723 ) HIER
724 WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
725 AND versions.metadata_level ='ICC'
726 AND versions.attr_display_name IS NOT NULL
727 )
728 WHERE
729 (
730 (
731 LEV =1
732 AND ICC_VERSION_number = p_icc_version_number -- bug 9980051
733 )
734 OR
735 (
736 LEV <> 1
737 AND
738 (
739 item_catalog_group_id, ICC_VERSION_NUMBER
740 )
741 IN
742 (SELECT item_catalog_group_id,
743 VERSION_SEQ_ID
744 FROM EGO_MTL_CATALOG_GRP_VERS_B
745 WHERE
746 (
747 item_catalog_group_id,start_active_date
748 )
749 IN
750 (SELECT item_catalog_group_id,
751 MAX(start_active_date) start_active_date
752 FROM EGO_MTL_CATALOG_GRP_VERS_B
753 WHERE NVL(end_active_date, sysdate) >=
754 (SELECT NVL(start_active_date,SYSDATE)
755 FROM EGO_MTL_CATALOG_GRP_VERS_B
756 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
757 AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
758 )
759 AND version_seq_id > 0
760
761
762 AND start_active_date <=
763 (SELECT NVL(start_active_date,SYSDATE)
764 FROM EGO_MTL_CATALOG_GRP_VERS_B
765 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
766 AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
767 )
768
769
770
771
772 GROUP BY item_catalog_group_id
773 HAVING MAX(start_active_date)<=
774 (SELECT NVL(start_active_date,SYSDATE)
775 FROM EGO_MTL_CATALOG_GRP_VERS_B
776 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
777 AND VERSION_SEQ_ID = p_icc_version_number -- bug 9980051
778 )
779 )
780 )
781 )
782 --AND metadata_level ='ICC'
783 )
784 GROUP BY attr_id
785 )
786 AND attr_id=l_attr_id
787 AND attr_id<> nvl(p_attr_id,-1); -- bug 9980051
788 --end cur_metadata
789 BEGIN
790
791 FOR i IN cur_list
792 LOOP
793 l_attr_id := i.attr_id;
794 FOR j IN cur_metadata
795 LOOP
796 l_attr_name := j.attr_name;
797 l_attr_disp_name := j.attr_display_name;
798 l_attr_sequence := j.SEQUENCE;
799
800 /** Validate if any transaction atrribute exist with same
801 internal name while creating/ updating a transaction attribute**/
802 IF (p_attr_name IS NOT NULL ) THEN
803 IF (p_attr_name= l_attr_name) THEN
804 l_ta_is_invalid := TRUE;
805 END IF; --IF (p_attr_name= l_attr_name) THEN
806 END IF ; --IF (p_attr_name IS NOT NULL ) THEN
807
808 /** Validate if any transaction atrribute exist with same
809 display name while creating/ updating a transaction attribute**/
810 IF (p_attr_disp_name IS NOT NULL ) THEN
811 IF (p_attr_disp_name= l_attr_disp_name) THEN
812 l_ta_is_invalid := TRUE;
813 END IF; --IF (p_attr_disp_name= l_attr_disp_name) THEN
814 END IF; --IF (p_attr_disp_name IS NOT NULL ) THEN
815
816 /** Validate if any transaction atrribute exist with same
817 sequence while creating/ updating a transaction attribute**/
818 IF (p_attr_sequence IS NOT NULL ) THEN
819 IF (p_attr_sequence = l_attr_sequence) THEN
820 l_ta_is_invalid := TRUE;
821 END IF; --IF (p_attr_sequence= l_attr_sequence) THEN
822 END IF ; --IF (p_attr_sequence IS NOT NULL )
823
824 END LOOP;--FOR j IN cur_metadata
825
826 END LOOP; --FOR i IN cur_list
827 RETURN l_ta_is_invalid;
828 END;
829
830
831
832 PROCEDURE IS_METADATA_CHANGE (p_tran_attrs_tbl IN EGO_TRAN_ATTR_TBL,
833 p_ta_metadata_tbl OUT NOCOPY EGO_TRAN_ATTR_TBL,
834 x_return_status OUT NOCOPY VARCHAR2,
835 x_msg_count OUT NOCOPY NUMBER,
836 x_msg_data OUT NOCOPY VARCHAR2)
837 IS
838
839 l_api_name CONSTANT VARCHAR2(30) := 'IS_METADATA_CHANGE';
840
841 l_uom_change BOOLEAN:= FALSE;
842 l_default_change BOOLEAN:= FALSE;
843 l_rejected_change BOOLEAN:= FALSE;
844 l_require_change BOOLEAN:= FALSE;
845 l_readonly_change BOOLEAN:= FALSE;
846 l_hidden_change BOOLEAN:= FALSE;
847 l_searchable_change BOOLEAN:= FALSE;
848 l_eligible_change BOOLEAN:= FALSE;
849 l_attr_disp_change BOOLEAN:= FALSE;
850 l_inherited_attr BOOLEAN:= FALSE;
851
852
853
854 l_out_uom VARCHAR2(10);
855 l_out_default VARCHAR2(2000);
856 l_out_rejected VARCHAR2(2000);
857 l_out_required VARCHAR2(1);
858 l_out_readonly VARCHAR2(1);
859 l_out_hidden VARCHAR2(1);
860 l_out_searchable VARCHAR2(1);
861 l_out_eligibile VARCHAR2(1);
862 l_out_attr_disp_name VARCHAR2(80);
863
864
865 l_attr_id NUMBER:=0;
866 l_value_set_id NUMBER;
867 l_uom_class VARCHAR2(10);
868 l_default_value VARCHAR2(2000);
869 l_rejectedvalue VARCHAR2(2000);
870 l_required VARCHAR2(1);
871 l_readonlyflag VARCHAR2(1);
872 l_hiddenflag VARCHAR2(1);
873 l_searchable VARCHAR2(1);
874 l_checkeligibility VARCHAR2(1);
875 l_item_cat_group_id EGO_TRANS_ATTR_VERS_B.ITEM_CATALOG_GROUP_ID%TYPE;
876 l_metadatalevel VARCHAR2(10);
877 l_attr_disp_name VARCHAR2(80);
878
879 indexcount NUMBER:=1;
880
881 CURSOR CUR_METADATA(CP_ITEM_CAT_GROUP_ID IN NUMBER,
882 CP_ATTR_ID IN NUMBER)
883 IS
884 SELECT * FROM
885 (SELECT versions.item_catalog_group_id,
886 versions.ICC_VERSION_NUMBER ,
887 versions.ATTR_ID ,
888 versions.attr_display_name ,
889 versions.metadata_level ,
890 versions.association_id ,
891 VERSIONS.VALUE_SET_ID ,
892 VERSIONS.UOM_CLASS ,
893 VERSIONS.DEFAULT_VALUE ,
894 versions.revision_id ,
895 versions.organization_id ,
896 versions.inventory_item_id ,
897 VERSIONS.REJECTED_VALUE ,
898 VERSIONS.REQUIRED_FLAG ,
899 VERSIONS.READONLY_FLAG ,
900 VERSIONS.HIDDEN_FLAG ,
901 VERSIONS.SEARCHABLE_FLAG ,
902 VERSIONS.CHECK_ELIGIBILITY ,
903 Hier.lev
904 FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
905 (SELECT ITEM_CATALOG_GROUP_ID ,
906 LEVEL LEV
907 FROM MTL_ITEM_CATALOG_GROUPS_B
908 START WITH ITEM_CATALOG_GROUP_ID = CP_ITEM_CAT_GROUP_ID
909 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
910 ) HIER
911 WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
912 AND versions.attr_id = CP_ATTR_ID
913 )
914 WHERE
915 (
916 /*(
917 LEV =1
918 AND ICC_VERSION_number = 0
919 AND metadata_level ='ICC'
920 )
921 OR */
922 (
923 LEV > 1
924 AND metadata_level='ICC'
925 AND
926 (
927 item_catalog_group_id, ICC_VERSION_NUMBER
928 )
929 IN
930 (SELECT item_catalog_group_id,
931 VERSION_SEQ_ID
932 FROM EGO_MTL_CATALOG_GRP_VERS_B
933 WHERE start_active_date <=
934 (SELECT NVL(start_active_date,SYSDATE)
935 FROM EGO_MTl_CATALOG_GRP_VERS_B
936 WHERE ITEM_CATALOG_GROUP_ID = CP_ITEM_CAT_GROUP_ID
937 AND VERSION_SEQ_ID = 0
938 )
939 AND NVL(end_active_date, sysdate) >=
940 (SELECT NVL(start_active_date,SYSDATE)
941 FROM EGO_MTl_CATALOG_GRP_VERS_B
942 WHERE ITEM_CATALOG_GROUP_ID = CP_ITEM_CAT_GROUP_ID
943 AND VERSION_SEQ_ID = 0
944 )
945 AND version_seq_id > 0
946 )
947 )
948 )
949 ORDER BY Lev ASC ;
950
951 BEGIN
952 --Reset all global variables
953 FND_MSG_PUB.Initialize;
954 p_ta_metadata_tbl := EGO_TRAN_ATTR_TBL(NULL);
955 FOR i IN p_tran_attrs_tbl.first..p_tran_attrs_tbl.last
956 LOOP
957 l_item_cat_group_id := p_tran_attrs_tbl(i).ItemCatalogGroupId;
958 l_attr_id := p_tran_attrs_tbl(i).attrid;
959 l_value_set_id := p_tran_attrs_tbl(i).valuesetid;
960 l_uom_class := p_tran_attrs_tbl(i).uomclass;
961 l_default_value := p_tran_attrs_tbl(i).defaultvalue;
962 l_rejectedvalue := p_tran_attrs_tbl(i).rejectedvalue;
963 l_required := p_tran_attrs_tbl(i).requiredflag;
964 l_readonlyflag := p_tran_attrs_tbl(i).readonlyflag;
965 l_hiddenflag := p_tran_attrs_tbl(i).hiddenflag;
966 l_searchable := p_tran_attrs_tbl(i).searchableflag;
967 l_checkeligibility := p_tran_attrs_tbl(i).checkeligibility;
968 l_metadatalevel := p_tran_attrs_tbl(i).metadatalevel;
969 l_attr_disp_name := p_tran_attrs_tbl(i).AttrDisplayName;
970
971
972 FOR J IN CUR_METADATA(CP_ITEM_CAT_GROUP_ID =>l_item_cat_group_id , CP_ATTR_ID=>l_attr_id)
973 LOOP
974
975 IF (CUR_METADATA%ROWCOUNT>0 ) THEN
976 l_inherited_attr :=TRUE;
977 END IF;
978
979
980
981 IF(l_attr_disp_name IS NULL) THEN
982 l_attr_disp_change:= TRUE;
983 ELSIF (l_attr_disp_change = FALSE) THEN
984 IF (j.attr_display_name IS NULL) THEN
985 l_attr_disp_change:= TRUE;
986 ELSIF (l_attr_disp_name <> j.attr_display_name) THEN
987 l_attr_disp_change:= TRUE;
988 ELSE
989 l_attr_disp_change:= FALSE;
990 END IF;
991 END IF;
992
993
994
995
996
997 IF(l_uom_class IS NULL) THEN
998 l_uom_change:= TRUE;
999 ELSIF (l_uom_change = FALSE) THEN
1000
1001 IF (j.uom_class IS NULL) THEN
1002 l_uom_change:= TRUE;
1003 ELSIF (l_uom_class <> j.uom_class) THEN
1004 l_uom_change:= TRUE;
1005 ELSE
1006 l_uom_change:= FALSE;
1007 END IF;
1008 END IF;
1009
1010 IF(l_default_value IS NULL) THEN
1011 l_default_change:= TRUE;
1012 ELSIF (l_default_change = FALSE) THEN
1013 IF (j.default_value IS NULL) THEN
1014 l_default_change:= TRUE;
1015 ELSIF (l_default_value <> j.default_value) THEN
1016 l_default_change:= TRUE ;
1017 ELSE
1018 l_default_change:= FALSE ;
1019 END IF;
1020 END IF;
1021
1022
1023 IF(l_rejectedvalue IS NULL) THEN
1024 l_rejected_change:= TRUE;
1025 ELSIF (l_rejected_change = FALSE) THEN
1026 IF (j.REJECTED_VALUE IS NULL) THEN
1027 l_rejected_change:= TRUE;
1028 ELSIF (l_rejectedvalue <> j.REJECTED_VALUE) THEN
1029 l_rejected_change:= TRUE;
1030 ELSE
1031 l_rejected_change:= FALSE;
1032 END IF;
1033 END IF;
1034
1035 IF(l_required IS NULL) THEN
1036 l_require_change:= TRUE;
1037 ELSIF (l_require_change = FALSE) THEN
1038 IF (j.REQUIRED_FLAG IS NULL) THEN
1039 l_require_change:= TRUE;
1040 ELSIF (l_required <> j.REQUIRED_FLAG) THEN
1041 l_require_change:= TRUE;
1042 ELSE
1043 l_require_change:= FALSE;
1044 END IF;
1045 END IF;
1046
1047
1048 IF(l_readonlyflag IS NULL) THEN
1049 l_readonly_change:= TRUE;
1050 ELSIF (l_readonly_change = FALSE) THEN
1051 IF (j.READONLY_FLAG IS NULL) THEN
1052 l_readonly_change:= TRUE;
1053 ELSIF (l_readonlyflag <> j.READONLY_FLAG) THEN
1054 l_readonly_change:= TRUE;
1055 ELSE
1056 l_readonly_change:= FALSE;
1057 END IF;
1058 END IF;
1059
1060 IF(l_hiddenflag IS NULL) THEN
1061 l_hidden_change:= TRUE;
1062 ELSIF (l_hidden_change = FALSE) THEN
1063 IF (j.HIDDEN_FLAG IS NULL) THEN
1064 l_hidden_change:= TRUE;
1065 ELSIF (l_hiddenflag <> j.HIDDEN_FLAG) THEN
1066 l_hidden_change:= TRUE;
1067 ELSE
1068 l_hidden_change:= FALSE;
1069 END IF;
1070 END IF;
1071
1072 IF(l_searchable IS NULL) THEN
1073 l_searchable_change:= TRUE;
1074 ELSIF (l_searchable_change = FALSE) THEN
1075 IF (j.SEARCHABLE_FLAG IS NULL) THEN
1076 l_searchable_change:= TRUE;
1077 ELSIF (l_searchable <> j.SEARCHABLE_FLAG) THEN
1078 l_searchable_change:= TRUE;
1079 ELSE
1080 l_searchable_change:= FALSE;
1081 END IF;
1082 END IF;
1083
1084 IF(l_checkeligibility IS NULL) THEN
1085 l_eligible_change:= TRUE;
1086 ELSIF (l_eligible_change = FALSE) THEN
1087 IF (j.CHECK_ELIGIBILITY IS NULL) THEN
1088 l_eligible_change:= TRUE;
1089 ELSIF (l_checkeligibility <> j.CHECK_ELIGIBILITY) THEN
1090 l_eligible_change:= TRUE;
1091 ELSE
1092 l_eligible_change:= FALSE;
1093 END IF;
1094 END IF;
1095
1096
1097
1098 END LOOP; -- Loop FOR J IN CUR_METADATA(CP_ITEM_CAT_GROUP_ID =>l_item_cat_group_id , CP_ATTR_ID=>l_attr_id)
1099
1100
1101 IF (l_inherited_attr) THEN
1102 IF (l_attr_disp_change = TRUE) THEN
1103 l_out_attr_disp_name:=l_attr_disp_name;
1104 ELSE
1105 l_out_attr_disp_name:=NULL;
1106 END IF;
1107
1108 IF (l_uom_change = TRUE) THEN
1109 l_out_uom:=l_uom_class;
1110 ELSE
1111 l_out_uom:=NULL;
1112 END IF;
1113
1114 IF (l_default_change = TRUE) THEN
1115 l_out_default:=l_default_value;
1116 ELSE
1117 l_out_default:=NULL;
1118 END IF;
1119
1120 IF (l_rejected_change = TRUE) THEN
1121 l_out_rejected:=l_rejectedvalue;
1122 ELSE
1123 l_out_rejected:=NULL;
1124 END IF;
1125
1126 IF (l_require_change = TRUE) THEN
1127 l_out_required:=l_required;
1128 ELSE
1129 l_out_required:=NULL;
1130 END IF;
1131
1132 IF (l_readonly_change = TRUE) THEN
1133 l_out_readonly:=l_readonlyflag;
1134 ELSE
1135 l_out_readonly:=NULL;
1136 END IF;
1137
1138 IF (l_hidden_change = TRUE) THEN
1139 l_out_hidden:=l_hiddenflag;
1140 ELSE
1141 l_out_hidden:=NULL;
1142 END IF;
1143
1144 IF (l_searchable_change = TRUE) THEN
1145 l_out_searchable:=l_searchable;
1146 ELSE
1147 l_out_searchable:=NULL;
1148 END IF;
1149
1150 IF (l_eligible_change = TRUE) THEN
1151 l_out_eligibile:=l_checkeligibility;
1152 ELSE
1153 l_out_eligibile:=NULL;
1154 END IF;
1155 --p_ta_metadata_tbl.extend;
1156 p_ta_metadata_tbl(indexcount) := EGO_TRAN_ATTR_REC(
1157 --EGO_TA_METADATA_REC(
1158 p_tran_attrs_tbl(i).AssociationId,
1159 p_tran_attrs_tbl(i).AttrId,
1160 p_tran_attrs_tbl(i).icc_version_number,
1161 p_tran_attrs_tbl(i).revision_id,
1162 p_tran_attrs_tbl(i).Sequence,
1163 p_tran_attrs_tbl(i).ValueSetId,
1164 l_out_uom,
1165 l_out_default,
1166 l_out_rejected,
1167 l_out_required,
1168 l_out_readonly,
1169 l_out_hidden,
1170 l_out_searchable,
1171 l_out_eligibile,
1172 p_tran_attrs_tbl(i).InventoryItemId,
1173 p_tran_attrs_tbl(i).OrganizationId,
1174 p_tran_attrs_tbl(i).MetadataLevel,
1175 p_tran_attrs_tbl(i).CreatedBy,
1176 p_tran_attrs_tbl(i).CreationDate,
1177 p_tran_attrs_tbl(i).LastUpdatedBy,
1178 p_tran_attrs_tbl(i).LastUpdateDate,
1179 p_tran_attrs_tbl(i).LastUpdateLogin,
1180 p_tran_attrs_tbl(i).ProgramApplicationId,
1181 p_tran_attrs_tbl(i).ProgramId,
1182 p_tran_attrs_tbl(i).ProgramUpdateDate,
1183 p_tran_attrs_tbl(i).RequestId,
1184 p_tran_attrs_tbl(i).ItemCatalogGroupId,
1185 p_tran_attrs_tbl(i).AttrName,
1186 --p_tran_attrs_tbl(i).AttrDisplayName,
1187 l_out_attr_disp_name,
1188 p_tran_attrs_tbl(i).DataType,
1189 p_tran_attrs_tbl(i).DisplayAs ,
1190 p_tran_attrs_tbl(i).ValueSetName
1191 --)
1192 );
1193
1194 indexcount:=indexcount+1;
1195
1196 ELSE
1197 p_ta_metadata_tbl(indexcount) := EGO_TRAN_ATTR_REC(
1198 --EGO_TA_METADATA_REC(
1199 p_tran_attrs_tbl(i).AssociationId,
1200 p_tran_attrs_tbl(i).AttrId,
1201 p_tran_attrs_tbl(i).icc_version_number,
1202 p_tran_attrs_tbl(i).revision_id,
1203 p_tran_attrs_tbl(i).Sequence,
1204 p_tran_attrs_tbl(i).ValueSetId,
1205 p_tran_attrs_tbl(i).UomClass,
1206 p_tran_attrs_tbl(i).DefaultValue,
1207 p_tran_attrs_tbl(i).RejectedValue,
1208 p_tran_attrs_tbl(i).RequiredFlag,
1209 p_tran_attrs_tbl(i).ReadonlyFlag,
1210 p_tran_attrs_tbl(i).HiddenFlag,
1211 p_tran_attrs_tbl(i).SearchableFlag,
1212 p_tran_attrs_tbl(i).CheckEligibility,
1213 p_tran_attrs_tbl(i).InventoryItemId,
1214 p_tran_attrs_tbl(i).OrganizationId,
1215 p_tran_attrs_tbl(i).MetadataLevel,
1216 p_tran_attrs_tbl(i).CreatedBy,
1217 p_tran_attrs_tbl(i).CreationDate,
1218 p_tran_attrs_tbl(i).LastUpdatedBy,
1219 p_tran_attrs_tbl(i).LastUpdateDate,
1220 p_tran_attrs_tbl(i).LastUpdateLogin,
1221 p_tran_attrs_tbl(i).ProgramApplicationId,
1222 p_tran_attrs_tbl(i).ProgramId,
1223 p_tran_attrs_tbl(i).ProgramUpdateDate,
1224 p_tran_attrs_tbl(i).RequestId,
1225 p_tran_attrs_tbl(i).ItemCatalogGroupId,
1226 p_tran_attrs_tbl(i).AttrName,
1227 p_tran_attrs_tbl(i).AttrDisplayName,
1228 p_tran_attrs_tbl(i).DataType,
1229 p_tran_attrs_tbl(i).DisplayAs ,
1230 p_tran_attrs_tbl(i).ValueSetName
1231 --)
1232 );
1233
1234 END IF ; -- end IF (l_inherited_attr) THEN
1235
1236 l_attr_disp_change := FALSE;
1237 l_uom_change := FALSE;
1238 l_default_change := FALSE;
1239 l_rejected_change := FALSE;
1240 l_require_change := FALSE;
1241 l_readonly_change := FALSE;
1242 l_hidden_change := FALSE;
1243 l_searchable_change := FALSE;
1244 l_eligible_change := FALSE;
1245 l_inherited_attr := FALSE;
1246
1247
1248
1249 END LOOP;
1250 /* FOR i IN p_tran_attrs_tbl.first..p_tran_attrs_tbl.last */
1251 x_return_status := G_STATUS_SUCCESS;
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 x_return_status := G_STATUS_ERROR;
1255 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1256 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1257 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1258 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
1259 FND_MSG_PUB.Add;
1260 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE ,p_count => x_msg_count ,p_data => x_msg_data);
1261 END;
1262
1263
1264
1265
1266
1267
1268
1269
1270 /* Create inherited transaction attribute API*/
1271 PROCEDURE Create_Inherited_Trans_Attr
1272 (
1273 p_api_version IN NUMBER,
1274 p_tran_attrs_tbl IN EGO_TRAN_ATTR_TBL,
1275 x_return_status OUT NOCOPY VARCHAR2,
1276 x_msg_count OUT NOCOPY NUMBER,
1277 x_msg_data OUT NOCOPY VARCHAR2)
1278 IS
1279 /* User Defined Exception object*/
1280 e_ta_disp_name_exist EXCEPTION;
1281 e_ta_default_value_null EXCEPTION;
1282 e_vs_data_type EXCEPTION;
1283 e_vs_not_versioned EXCEPTION;
1284
1285 /* Declaring local parameters*/
1286 l_attr_desc VARCHAR2(100); --confirm about size
1287 l_count NUMBER:=0;
1288 l_ag_seq_value NUMBER;
1289 l_ag_int_name VARCHAR2(100); --confirm about size
1290 l_ag_disp_name VARCHAR2(100); --confirm about size
1291 l_ag_desc VARCHAR2(100); --confirm about size
1292 l_ag_type VARCHAR2(30) := 'EGO_ITEM_TRANS_ATTR_GROUP';
1293 l_attr_group_id NUMBER;
1294 l_column VARCHAR2(30):=NULL;
1295 l_return_status VARCHAR2(1);
1296 l_errorcode NUMBER;
1297 l_msg_count NUMBER;
1298 l_msg_data VARCHAR2(2000);
1299 l_association_id NUMBER;
1300 l_attr_id NUMBER:=0;
1301 l_attr_sequence EGO_ATTRS_V.SEQUENCE%TYPE;
1302 l_value_set_id NUMBER;
1303 l_uom_class VARCHAR2(10);
1304 l_default_value VARCHAR2(2000);
1305 l_rejectedvalue VARCHAR2(2000);
1306 l_required VARCHAR2(1);
1307 l_readonlyflag VARCHAR2(1);
1308 l_hiddenflag VARCHAR2(1);
1309 l_searchable VARCHAR2(1);
1310 l_checkeligibility VARCHAR2(1);
1311 l_inventoryitemid NUMBER;
1312 l_organizationid NUMBER;
1313 l_metadatalevel VARCHAR2(10);
1314 l_programapplicationid NUMBER;
1315 l_programid NUMBER;
1316 l_programupdatedate DATE;
1317 l_requestid NUMBER;
1318 l_item_cat_group_id EGO_TRANS_ATTR_VERS_B.ITEM_CATALOG_GROUP_ID%TYPE;
1319 l_attr_name EGO_ATTRS_V.ATTR_NAME%TYPE :=NULL;
1320 l_attr_disp_name EGO_ATTRS_V.ATTR_DISPLAY_NAME%TYPE:=NULL;
1321 l_data_type VARCHAR2(1);
1322 l_display VARCHAR2(1);
1323 l_api_name CONSTANT VARCHAR2(30) := 'Create_Inherited_Trans_Attr';
1324 l_data_level_id NUMBER;
1325 l_icc_version_number NUMBER;
1326 l_revision_id NUMBER;
1327 l_item_obj_id NUMBER;
1328 l_versioned_value_set NUMBER:=0;
1329
1330 l_ta_metadata_tbl EGO_TRAN_ATTR_TBL;
1331
1332 BEGIN
1333 --Reset all global variables
1334 FND_MSG_PUB.Initialize;
1335 l_ta_metadata_tbl := EGO_TRAN_ATTR_TBL(NULL);
1336 IS_METADATA_CHANGE(p_tran_attrs_tbl,l_ta_metadata_tbl,l_return_status,l_msg_count,l_msg_data );
1337
1338 FOR i IN l_ta_metadata_tbl.first..l_ta_metadata_tbl.last
1339 LOOP
1340
1341
1342 l_item_cat_group_id := l_ta_metadata_tbl(i).ItemCatalogGroupId;
1343 --l_attr_name := p_tran_attrs_tbl(i).AttrName;
1344 l_attr_disp_name := l_ta_metadata_tbl(i).AttrDisplayName;
1345 l_attr_sequence := l_ta_metadata_tbl(i).SEQUENCE;
1346 l_association_id := l_ta_metadata_tbl(i).associationid;
1347 l_attr_id := l_ta_metadata_tbl(i).attrid;
1348 l_value_set_id := l_ta_metadata_tbl(i).valuesetid;
1349 l_uom_class := l_ta_metadata_tbl(i).uomclass;
1350 l_default_value := l_ta_metadata_tbl(i).defaultvalue;
1351 l_rejectedvalue := l_ta_metadata_tbl(i).rejectedvalue;
1352 l_required := l_ta_metadata_tbl(i).requiredflag;
1353 l_readonlyflag := l_ta_metadata_tbl(i).readonlyflag;
1354 l_hiddenflag := l_ta_metadata_tbl(i).hiddenflag;
1355 l_searchable := l_ta_metadata_tbl(i).searchableflag;
1356 l_checkeligibility := l_ta_metadata_tbl(i).checkeligibility;
1357 l_inventoryitemid := l_ta_metadata_tbl(i).inventoryitemid;
1358 l_organizationid := l_ta_metadata_tbl(i).organizationid;
1359 l_metadatalevel := l_ta_metadata_tbl(i).metadatalevel;
1360 l_programapplicationid := l_ta_metadata_tbl(i).programapplicationid;
1361 l_programid := l_ta_metadata_tbl(i).programid;
1362 l_programupdatedate := l_ta_metadata_tbl(i).programupdatedate;
1363 l_requestid := l_ta_metadata_tbl(i).requestid;
1364 l_icc_version_number := l_ta_metadata_tbl(i).icc_version_number;
1365 l_revision_id := p_tran_attrs_tbl(i).revision_id;
1366 l_data_type := l_ta_metadata_tbl(i).datatype;
1367 --l_display := p_tran_attrs_tbl(i).displayas;
1368
1369 --
1370 -- Bug 9980051. Pass version number parameter.
1371 -- For MIB we need to validate the attributes
1372 -- and its other properties against the version
1373 -- created and not against the default version.
1374 -- sreharih. Tue Apr 19 11:06:55 PDT 2011
1375 --
1376
1377 /* Check if att_disp_name already exist*/
1378 IF ( Check_TA_IS_INVALID (p_item_cat_group_id => l_item_cat_group_id,
1379 p_icc_version_number => l_icc_version_number, -- bug 9980051
1380 p_attr_id => l_attr_id,
1381 p_attr_disp_name => l_attr_disp_name) ) THEN
1382 RAISE e_ta_disp_name_exist;
1383 END IF;
1384
1385 IF ( l_readonlyflag='Y' AND l_required ='Y' AND l_default_value IS NULL) THEN
1386 RAISE e_ta_default_value_null;
1387 END IF;
1388 -------------------------------------------------------------------------------------
1389 -- Make sure that if a Value Set was passed in, is a versioned value set --
1390 -------------------------------------------------------------------------------------
1391 IF (l_value_set_id IS NOT NULL) THEN
1392 SELECT COUNT(*) CNT
1393 INTO l_versioned_value_set
1394 FROM EGO_FLEX_VALUESET_VERSION_B
1395 WHERE FLEX_VALUE_SET_ID = l_value_set_id
1396 AND VERSION_SEQ_ID >0;
1397
1398 IF (l_versioned_value_set=0) THEN
1399 RAISE e_vs_not_versioned;
1400 END IF;
1401 --------------------------------------------------------------------------
1402 IF (NOT Check_VS_Data_Type(l_value_set_id,l_data_type) ) THEN
1403 RAISE e_vs_data_type;
1404 END IF;
1405 END IF;
1406 /* checking whether any of the columns are updated before inserting
1407 bug 8356736 */
1408 IF(l_attr_disp_name IS NOT NULL OR l_default_value IS NOT NULL OR l_rejectedvalue IS NOT NULL OR l_required IS NOT NULL OR
1409 l_readonlyflag IS NOT NULL OR l_hiddenflag IS NOT NULL OR l_searchable IS NOT NULL OR l_checkeligibility IS NOT NULL ) THEN
1410 /* INSERTING values in tables*/
1411 BEGIN
1412 INSERT INTO EGO_TRANS_ATTR_VERS_B
1413 (association_id,
1414 attr_id,
1415 icc_version_number,
1416 attr_display_name,
1417 sequence,
1418 value_set_id,
1419 uom_class,
1420 default_value,
1421 rejected_value,
1422 required_flag,
1423 readonly_flag,
1424 hidden_flag,
1425 searchable_flag,
1426 check_eligibility,
1427 inventory_item_id,
1428 organization_id,
1429 revision_id,
1430 metadata_level,
1431 created_by,
1432 creation_date,
1433 last_updated_by,
1434 last_update_date,
1435 last_update_login,
1436 program_application_id,
1437 program_id,
1438 program_update_date,
1439 request_id,
1440 item_catalog_group_id)
1441 VALUES (l_association_id ,
1442 l_attr_id ,
1443 l_icc_version_number ,
1444 l_attr_disp_name ,
1445 l_attr_sequence ,
1446 l_value_set_id ,
1447 l_uom_class ,
1448 l_default_value ,
1449 l_rejectedvalue ,
1450 l_required ,
1451 l_readonlyflag ,
1452 l_hiddenflag ,
1453 l_searchable ,
1454 l_checkeligibility ,
1455 l_inventoryitemid ,
1456 l_organizationid ,
1457 l_revision_id ,
1458 l_metadatalevel ,
1459 G_CURRENT_USER_ID ,
1460 SYSDATE ,
1461 G_CURRENT_USER_ID ,
1462 SYSDATE ,
1463 G_CURRENT_LOGIN_ID ,
1464 l_programapplicationid,
1465 l_programid ,
1466 l_programupdatedate ,
1467 l_requestid ,
1468 l_item_cat_group_id
1469 );
1470
1471 EXCEPTION
1472 WHEN OTHERS
1473 THEN
1474 x_return_status := G_STATUS_ERROR;
1475 x_msg_data := 'TA_REC_INSERT_FAILED';
1476 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1477 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1478 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1479 FND_MESSAGE.Set_Token('SQL_ERR_MSG',x_msg_data );
1480 FND_MSG_PUB.Add;
1481 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1482 ,p_count => x_msg_count
1483 ,p_data => x_msg_data);
1484 END ;
1485 END IF; --BUG 8356736
1486 END LOOP;
1487 /* FOR i IN p_tran_attrs_tbl.first..p_tran_attrs_tbl.last */
1488 IF (Nvl(x_return_status,G_STATUS_SUCCESS) <>G_STATUS_ERROR )
1489 THEN
1490 x_return_status := G_STATUS_SUCCESS;
1491 END IF;
1492 EXCEPTION
1493 WHEN e_ta_disp_name_exist THEN
1494 x_return_status := G_STATUS_ERROR;
1495 FND_MESSAGE.Set_Name('EGO', 'EGO_TA_DISPLAY_NAME_UNIQUE');
1496 --FND_MESSAGE.Set_Token('SQL_ERR_MSG',x_msg_data );
1497 FND_MSG_PUB.Add;
1498 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1499 ,p_count => x_msg_count
1500 ,p_data => x_msg_data);
1501 WHEN e_ta_default_value_null THEN
1502 x_return_status := G_STATUS_ERROR;
1503 FND_MESSAGE.Set_Name('EGO', 'EGO_TA_DEFAULT_VALUE_NULL');
1504 FND_MSG_PUB.Add;
1505 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1506 ,p_count => x_msg_count
1507 ,p_data => x_msg_data);
1508 WHEN e_vs_not_versioned
1509 THEN
1510 x_return_status := G_STATUS_ERROR;
1511 FND_MESSAGE.Set_Name('EGO', 'EGO_VALUE_SET_NOT_VERSIONED');
1512 FND_MSG_PUB.Add;
1513 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1514 ,p_count => x_msg_count
1515 ,p_data => x_msg_data);
1516 WHEN e_vs_data_type
1517 THEN
1518 x_return_status := G_STATUS_ERROR;
1519 FND_MESSAGE.Set_Name('EGO', 'EGO_EF_CR_ATTR_VS_DT_ERR');
1520 FND_MSG_PUB.Add;
1521 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1522 ,p_count => x_msg_count
1523 ,p_data => x_msg_data);
1524
1525 WHEN OTHERS THEN
1526 x_return_status := G_STATUS_ERROR;
1527 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1528 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1529 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1530 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
1531 FND_MSG_PUB.Add;
1532 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1533 ,p_count => x_msg_count
1534 ,p_data => x_msg_data);
1535 END Create_Inherited_Trans_Attr;
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545 --============ Update_Transaction_Attribute API===============
1546
1547 PROCEDURE Update_Transaction_Attribute (
1548 p_api_version IN NUMBER,
1549 p_tran_attrs_tbl IN EGO_TRAN_ATTR_TBL,
1550 x_return_status OUT NOCOPY VARCHAR2,
1551 x_msg_count OUT NOCOPY NUMBER,
1552 x_msg_data OUT NOCOPY VARCHAR2)
1553 IS
1554 /* Declaring local parameters*/
1555 l_attr_desc VARCHAR2(100); --confirm about size
1556 l_ag_int_name VARCHAR2(100); --confirm about size
1557 l_ag_type VARCHAR2(30) := 'EGO_ITEM_TRANS_ATTR_GROUP';
1558 l_icc_version_number NUMBER;
1559 --l_attr_group_id NUMBER;
1560 l_column VARCHAR2(30):=NULL;
1561
1562 l_return_status VARCHAR2(1);
1563 l_errorcode NUMBER;
1564 l_msg_count NUMBER;
1565 l_msg_data VARCHAR2(2000);
1566
1567 l_association_id NUMBER;
1568 l_attr_id NUMBER;
1569 l_attr_sequence EGO_ATTRS_V.SEQUENCE%TYPE;
1570 l_value_set_id NUMBER;
1571 l_uom_class VARCHAR2(10);
1572 l_default_value VARCHAR2(2000);
1573 l_rejectedvalue VARCHAR2(2000);
1574 l_required VARCHAR2(1);
1575 l_readonlyflag VARCHAR2(1);
1576 l_hiddenflag VARCHAR2(1);
1577 l_searchable VARCHAR2(1);
1578 l_checkeligibility VARCHAR2(1);
1579 l_item_cat_group_id EGO_TRANS_ATTR_VERS_B.ITEM_CATALOG_GROUP_ID%TYPE;
1580 l_attr_name EGO_ATTRS_V.ATTR_NAME%TYPE:=NULL;
1581 l_attr_disp_name EGO_ATTRS_V.ATTR_DISPLAY_NAME%TYPE:=NULL;
1582 l_data_type VARCHAR2(1);
1583 l_display VARCHAR2(1);
1584 l_inventoryitemid NUMBER;
1585 l_revisionid NUMBER;
1586 l_organizationid NUMBER;
1587
1588
1589 l_api_name CONSTANT VARCHAR2(30) := 'Update_Transaction_Attribute';
1590
1591 /* User Defined Exception object*/
1592 e_ta_int_name_exist EXCEPTION;
1593 e_ta_disp_name_exist EXCEPTION;
1594 e_ta_sequence_exist EXCEPTION;
1595 e_ta_default_value_null EXCEPTION;
1596 l_ta_metadata_tbl EGO_TRAN_ATTR_TBL;
1597
1598 BEGIN
1599 --Reset all global variables
1600 FND_MSG_PUB.Initialize;
1601 IS_METADATA_CHANGE(p_tran_attrs_tbl,l_ta_metadata_tbl,l_return_status,l_msg_count,l_msg_data );
1602 FOR i IN l_ta_metadata_tbl.first..l_ta_metadata_tbl.last
1603 LOOP
1604 l_association_id := l_ta_metadata_tbl(i).associationid;
1605 l_attr_id := l_ta_metadata_tbl(i).attrid;
1606 l_item_cat_group_id := l_ta_metadata_tbl(i).ItemCatalogGroupId;
1607 l_attr_sequence := l_ta_metadata_tbl(i).SEQUENCE;
1608 l_icc_version_number:= l_ta_metadata_tbl(i).icc_version_number;
1609
1610 l_attr_name := l_ta_metadata_tbl(i).AttrName;
1611 l_attr_disp_name := l_ta_metadata_tbl(i).AttrDisplayName;
1612 l_attr_sequence := l_ta_metadata_tbl(i).SEQUENCE;
1613
1614
1615 l_value_set_id := l_ta_metadata_tbl(i).valuesetid;
1616 l_uom_class := l_ta_metadata_tbl(i).uomclass;
1617 l_default_value := l_ta_metadata_tbl(i).defaultvalue;
1618 l_rejectedvalue := l_ta_metadata_tbl(i).rejectedvalue;
1619 l_required := l_ta_metadata_tbl(i).requiredflag;
1620 l_readonlyflag := l_ta_metadata_tbl(i).readonlyflag;
1621 l_hiddenflag := l_ta_metadata_tbl(i).hiddenflag;
1622 l_searchable := l_ta_metadata_tbl(i).searchableflag;
1623 l_checkeligibility := l_ta_metadata_tbl(i).checkeligibility;
1624 l_inventoryitemid := l_ta_metadata_tbl(i).InventoryItemId;
1625 l_revisionid := l_ta_metadata_tbl(i).revision_id;
1626 l_organizationid := l_ta_metadata_tbl(i).OrganizationId;
1627 --l_data_type := p_tran_attrs_tbl(i).datatype;
1628 l_display := l_ta_metadata_tbl(i).displayas;
1629
1630
1631 /* Check if att_disp_name already exist*/
1632 /*IF (Check_Ta_Disp_Name_Exist(l_item_cat_group_id,l_attr_id,l_attr_disp_name)) THEN
1633 RAISE e_ta_disp_name_exist;
1634 END IF; */
1635
1636 /* Check if att_disp_name already exist*/
1637 IF ( Check_TA_IS_INVALID (p_item_cat_group_id => l_item_cat_group_id,
1638 p_attr_id => l_attr_id,
1639 p_attr_disp_name => l_attr_disp_name) ) THEN
1640 RAISE e_ta_disp_name_exist;
1641 END IF;
1642
1643 IF(l_inventoryitemid IS NOT NULL AND l_revisionid IS NOT NULL AND l_organizationid IS NOT NULL ) THEN
1644 UPDATE EGO_TRANS_ATTR_VERS_B
1645 SET "SEQUENCE" =l_attr_sequence,
1646 ATTR_DISPLAY_NAME = l_attr_disp_name,
1647 value_set_id = l_value_set_id,
1648 uom_class = l_uom_class,
1649 default_value = l_default_value,
1650 rejected_value = l_rejectedvalue,
1651 required_flag = l_required,
1652 readonly_flag = l_readonlyflag,
1653 hidden_flag = l_hiddenflag,
1654 searchable_flag = l_searchable,
1655 check_eligibility = l_checkeligibility,
1656 last_updated_by = G_CURRENT_USER_ID,
1657 last_update_date = SYSDATE,
1658 last_update_login = G_CURRENT_LOGIN_ID
1659 WHERE ASSOCIATION_ID= l_association_id
1660 AND ATTR_ID =l_attr_id
1661 AND INVENTORY_ITEM_ID = l_inventoryitemid
1662 AND ORGANIZATION_ID = l_organizationid
1663 AND REVISION_ID = l_revisionid
1664 AND metadata_level = 'ITM';
1665 ELSE
1666 UPDATE EGO_TRANS_ATTR_VERS_B
1667 SET "SEQUENCE" =l_attr_sequence,
1668 ATTR_DISPLAY_NAME = l_attr_disp_name,
1669 value_set_id = l_value_set_id,
1670 uom_class = l_uom_class,
1671 default_value = l_default_value,
1672 rejected_value = l_rejectedvalue,
1673 required_flag = l_required,
1674 readonly_flag = l_readonlyflag,
1675 hidden_flag = l_hiddenflag,
1676 searchable_flag = l_searchable,
1677 check_eligibility = l_checkeligibility,
1678 last_updated_by = G_CURRENT_USER_ID,
1679 last_update_date = SYSDATE,
1680 last_update_login = G_CURRENT_LOGIN_ID
1681 WHERE ASSOCIATION_ID= l_association_id
1682 AND ATTR_ID =l_attr_id
1683 AND ITEM_CATALOG_GROUP_ID = l_item_cat_group_id
1684 AND ICC_VERSION_NUMBER =0
1685 AND metadata_level = 'ICC';
1686
1687 END IF;
1688
1689 SELECT attr_group_name INTO l_ag_int_name
1690 FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
1691 WHERE (object_id, classification_code, attr_group_id) IN
1692 (SELECT object_id, classification_code, attr_group_id
1693 FROM ego_obj_ag_assocs_b
1694 where association_id = l_association_id);
1695
1696
1697 /*EGO_EXT_FWK_PUB.Update_Attribute (
1698 p_api_version => 1.0
1699 ,p_application_id => G_APPLICATION_ID
1700 ,p_attr_group_type => l_ag_type
1701 ,p_attr_group_name => l_ag_int_name
1702 ,p_internal_name => l_attr_name
1703 ,p_display_name => l_attr_disp_name
1704 ,p_description => l_attr_desc
1705 ,p_sequence => l_attr_sequence
1706 ,p_required => l_required
1707 ,p_searchable => l_searchable
1708 ,p_column => l_column
1709 ,p_value_set_id => l_value_set_id
1710 ,p_info_1 => null
1711 ,p_default_value => l_default_value
1712 ,p_unique_key_flag => null
1713 ,p_enabled => 'Y'
1714 ,p_display => l_display
1715 ,p_control_level => -1
1716 ,p_attribute_code => G_MISS_CHAR
1717 ,p_view_in_hierarchy_code => G_MISS_CHAR
1718 ,p_edit_in_hierarchy_code => G_MISS_CHAR
1719 ,p_customization_level => G_MISS_CHAR
1720 ,p_owner => NULL
1721 ,p_lud => SYSDATE
1722 ,p_init_msg_list => null
1723 ,p_commit => null
1724 ,p_is_nls_mode => FND_API.G_FALSE
1725 ,p_uom_class => l_uom_class
1726 ,x_return_status => l_return_status
1727 ,x_errorcode => l_errorcode
1728 ,x_msg_count => l_msg_count
1729 ,x_msg_data => l_msg_data);*/
1730 END LOOP; /* FOR i IN p_tran_attrs_tbl.first..p_tran_attrs_tbl.last */
1731 x_return_status:=G_STATUS_SUCCESS;
1732
1733 EXCEPTION
1734 WHEN e_ta_disp_name_exist THEN
1735 x_return_status := G_STATUS_ERROR;
1736 FND_MESSAGE.Set_Name('EGO', 'EGO_TA_DISPLAY_NAME_UNIQUE');
1737 FND_MSG_PUB.Add;
1738 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1739 ,p_count => x_msg_count
1740 ,p_data => x_msg_data);
1741 WHEN OTHERS THEN
1742 x_return_status := G_STATUS_ERROR;
1743 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1744 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1745 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1746 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
1747 FND_MSG_PUB.Add;
1748 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1749 ,p_count => x_msg_count
1750 ,p_data => x_msg_data);
1751
1752 END Update_Transaction_Attribute;
1753
1754
1755
1756 --============ Delete_Transaction_Attribute API===============
1757 PROCEDURE Delete_Transaction_Attribute (
1758 p_api_version IN NUMBER,
1759 p_association_id IN NUMBER,
1760 p_attr_id IN NUMBER,
1761 x_return_status OUT NOCOPY VARCHAR2,
1762 x_msg_count OUT NOCOPY NUMBER,
1763 x_msg_data OUT NOCOPY VARCHAR2)
1764 IS
1765
1766 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Transaction_Attribute';
1767
1768 BEGIN
1769 --Reset all global variables
1770 FND_MSG_PUB.Initialize;
1771 DELETE FROM EGO_TRANS_ATTR_VERS_B
1772 WHERE ASSOCIATION_ID= p_association_id
1773 AND ATTR_ID =p_attr_id
1774 AND ICC_VERSION_NUMBER=0;
1775 x_return_status := G_STATUS_SUCCESS;
1776
1777 EXCEPTION
1778 WHEN OTHERS THEN
1779 x_return_status := G_STATUS_ERROR;
1780 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1781 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1782 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1783 FND_MESSAGE.Set_Token('SQL_ERR_MSG',SQLERRM);
1784 FND_MSG_PUB.Add;
1785 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1786 ,p_count => x_msg_count
1787 ,p_data => x_msg_data);
1788 END Delete_Transaction_Attribute;
1789
1790
1791 --========Override method=======
1792 PROCEDURE Delete_Transaction_Attribute (
1793 p_api_version IN NUMBER,
1794 p_tran_attrs_tbl IN EGO_TRAN_ATTR_TBL,
1795 x_return_status OUT NOCOPY VARCHAR2,
1796 x_msg_count OUT NOCOPY NUMBER,
1797 x_msg_data OUT NOCOPY VARCHAR2) IS
1798
1799 /* Declaring local parameters*/
1800 l_association_id EGO_TRANS_ATTR_VERS_B.ASSOCIATION_ID%TYPE;
1801 l_attr_id EGO_TRANS_ATTR_VERS_B.ATTR_ID%TYPE;
1802 l_icc_version_number NUMBER;
1803
1804 l_item_cat_group_id EGO_TRANS_ATTR_VERS_B.ITEM_CATALOG_GROUP_ID%TYPE;
1805 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Transaction_Attribute';
1806
1807 BEGIN
1808 --Reset all global variables
1809 FND_MSG_PUB.Initialize;
1810 FOR i IN p_tran_attrs_tbl.first..p_tran_attrs_tbl.last
1811 LOOP
1812 l_association_id := p_tran_attrs_tbl(i).associationid;
1813 l_attr_id := p_tran_attrs_tbl(i).attrid;
1814
1815 DELETE FROM EGO_TRANS_ATTR_VERS_B
1816 WHERE ASSOCIATION_ID= l_association_id
1817 AND ATTR_ID =l_attr_id
1818 AND ICC_VERSION_NUMBER=0;
1819 END LOOP; /* FOR i IN p_tran_attrs_tbl.first..p_tran_attrs_tbl.last */
1820
1821 x_return_status := G_STATUS_SUCCESS;
1822
1823 EXCEPTION
1824 WHEN OTHERS THEN
1825 x_return_status := G_STATUS_ERROR;
1826 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1827 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1828 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1829 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
1830 FND_MSG_PUB.Add;
1831 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1832 ,p_count => x_msg_count
1833 ,p_data => x_msg_data);
1834 END Delete_Transaction_Attribute;
1835
1836
1837
1838
1839 --============ Release_Transaction_Attribute API===============
1840
1841 /* Releasing a version of a ICC*/
1842 PROCEDURE Release_Transaction_Attribute (
1843 p_api_version IN NUMBER,
1844 p_icc_id IN NUMBER,
1845 p_version_number IN NUMBER,
1846 --p_tran_attrs_tbl IN EGO_TRAN_ATTR_TBL,
1847 x_return_status OUT NOCOPY VARCHAR2
1848 ,x_msg_count OUT NOCOPY NUMBER
1849 ,x_msg_data OUT NOCOPY VARCHAR2)
1850 IS
1851
1852 /* Declaring local parameters*/
1853 --l_association_id EGO_TRANS_ATTR_VERS_B.ASSOCIATION_ID%TYPE;
1854 --l_attr_id EGO_TRANS_ATTR_VERS_B.ATTR_ID%TYPE;
1855 l_icc_version_number NUMBER:=p_version_number;
1856 l_max_ver_number NUMBER:=0;
1857 l_item_cat_group_id EGO_TRANS_ATTR_VERS_B.ITEM_CATALOG_GROUP_ID%TYPE:=p_icc_id;
1858 l_api_name CONSTANT VARCHAR2(30) := 'Release_Transaction_Attribute';
1859
1860 l_return_status VARCHAR2(1);
1861 l_errorcode NUMBER;
1862 l_msg_count NUMBER;
1863 l_msg_data VARCHAR2(2000);
1864
1865 e_version_error EXCEPTION ;
1866
1867
1868 CURSOR cur_tran_attr_vers_exist
1869 IS
1870 SELECT Max(icc_version_number) maxver
1871 FROM EGO_TRANS_ATTR_VERS_B
1872 WHERE ITEM_CATALOG_GROUP_ID=l_item_cat_group_id;
1873
1874 BEGIN
1875 --Reset all global variables
1876 FND_MSG_PUB.Initialize;
1877 FOR i IN cur_tran_attr_vers_exist
1878 LOOP
1879 l_max_ver_number:=i.maxver;
1880 END LOOP;
1881
1882 IF (l_max_ver_number>= p_version_number ) THEN
1883 RAISE e_version_error;
1884 END IF;
1885 /*FOR i IN p_tran_attrs_tbl.first..p_tran_attrs_tbl.last
1886 LOOP */
1887 /*l_association_id := p_tran_attrs_tbl(i).associationid;
1888 l_attr_id := p_tran_attrs_tbl(i).attrid;*/
1889 --l_item_cat_group_id := p_tran_attrs_tbl(i).ItemCatalogGroupId;
1890
1891 Copy_Transaction_Attribute(l_item_cat_group_id,l_icc_version_number,l_return_status,l_msg_count,l_msg_data);
1892 x_return_status := G_STATUS_SUCCESS;
1893 --END LOOP;
1894
1895 EXCEPTION
1896 WHEN e_version_error
1897 THEN
1898 x_return_status := G_STATUS_ERROR;
1899 FND_MESSAGE.Set_Name('EGO', 'EGO_ICC_VER_ERROR');
1900 FND_MSG_PUB.Add;
1901 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1902 ,p_count => x_msg_count
1903 ,p_data => x_msg_data);
1904 WHEN OTHERS
1905 THEN
1906 x_return_status := G_STATUS_ERROR;
1907 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1908 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1909 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1910 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
1911 FND_MSG_PUB.Add;
1912 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1913 ,p_count => x_msg_count
1914 ,p_data => x_msg_data);
1915 END;
1916
1917 PROCEDURE Copy_Transaction_Attribute (
1918 p_item_cat_group_id IN NUMBER,
1919 p_version_number IN NUMBER,
1920 x_return_status OUT NOCOPY VARCHAR2,
1921 x_msg_count OUT NOCOPY NUMBER,
1922 x_msg_data OUT NOCOPY VARCHAR2)
1923 IS
1924 /* Declaring local parameters*/
1925 l_association_id EGO_TRANS_ATTR_VERS_B.ASSOCIATION_ID%TYPE;
1926 l_attr_id EGO_TRANS_ATTR_VERS_B.ATTR_ID%TYPE;
1927 l_item_cat_group_id EGO_TRANS_ATTR_VERS_B.ITEM_CATALOG_GROUP_ID%TYPE:=p_item_cat_group_id;
1928 l_icc_version_number NUMBER:=p_version_number;
1929 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Transaction_Attribute';
1930
1931 CURSOR cur_tran_attr_vers
1932 IS
1933 SELECT *
1934 FROM EGO_TRANS_ATTR_VERS_B
1935 WHERE ITEM_CATALOG_GROUP_ID=l_item_cat_group_id
1936 AND ICC_VERSION_NUMBER =0;
1937 BEGIN
1938 --Reset all global variables
1939 FND_MSG_PUB.Initialize;
1940 FOR i IN cur_tran_attr_vers
1941 LOOP
1942 INSERT INTO EGO_TRANS_ATTR_VERS_B
1943 (association_id,attr_id,icc_version_number,attr_display_name,"SEQUENCE",value_set_id,uom_class,
1944 default_value,rejected_value,required_flag,readonly_flag,hidden_flag, searchable_flag,
1945 check_eligibility,inventory_item_id,organization_id, revision_id,metadata_level,created_by,
1946 creation_date,last_updated_by,last_update_date,last_update_login,program_application_id,
1947 program_id,program_update_date,request_id,item_catalog_group_id)
1948 VALUES (i.association_id,i.attr_id,l_icc_version_number,i.attr_display_name,i.SEQUENCE,i.value_set_id,i.uom_class,
1949 i.default_value,i.rejected_value,i.required_flag,i.readonly_flag,i.hidden_flag,i.searchable_flag,
1950 i.check_eligibility,i.inventory_item_id,i.organization_id,i.revision_id,i.metadata_level,G_CURRENT_USER_ID,
1951 sysdate,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID,i.program_application_id,
1952 i.program_id,i.program_update_date,i.request_id,i.item_catalog_group_id);
1953 END LOOP;
1954 EXCEPTION
1955 WHEN OTHERS THEN
1956 x_return_status := G_STATUS_ERROR;
1957 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1958 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1959 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
1960 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
1961 FND_MSG_PUB.Add;
1962 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1963 ,p_count => x_msg_count
1964 ,p_data => x_msg_data);
1965 END;
1966
1967
1968 /* procedure to copy data from source Id's to destination Id's*/
1969
1970 PROCEDURE Copy_Transaction_Attribute (
1971 p_source_icc_id IN NUMBER,
1972 p_source_ver_no IN NUMBER,
1973 p_sorce_item_id IN NUMBER,
1974 p_source_rev_id IN NUMBER,
1975 p_source_org_id IN NUMBER,
1976 p_dest_icc_id IN NUMBER,
1977 p_dest_ver_no IN NUMBER,
1978 p_dest_item_id IN NUMBER,
1979 p_dest_rev_id IN NUMBER,
1980 p_dest_org_id IN NUMBER,
1981 p_init_msg_list IN BOOLEAN, --- Bug 9791391, made default true in spec to maintain existing TA code
1982 x_return_status OUT NOCOPY VARCHAR2,
1983 x_msg_count OUT NOCOPY NUMBER,
1984 x_msg_data OUT NOCOPY VARCHAR2)
1985 IS
1986 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Transaction_Attribute';
1987 /* Declaring local parameters*/
1988 /* l_association_id EGO_TRANS_ATTR_VERS_B.ASSOCIATION_ID%TYPE;
1989 l_attr_id EGO_TRANS_ATTR_VERS_B.ATTR_ID%TYPE;
1990 l_item_cat_group_id EGO_TRANS_ATTR_VERS_B.ITEM_CATALOG_GROUP_ID%TYPE:=p_item_cat_group_id;
1991 l_icc_version_number NUMBER:=p_version_number;
1992
1993 CURSOR cur_tran_attr_vers
1994 IS
1995 SELECT *
1996 FROM EGO_TRANS_ATTR_VERS_B
1997 WHERE ITEM_CATALOG_GROUP_ID=l_item_cat_group_id
1998 AND ICC_VERSION_NUMBER =0;*/
1999 BEGIN
2000 --Reset all global variables
2001 IF p_init_msg_list THEN --- BUG 9791391 added if condition
2002 FND_MSG_PUB.Initialize;
2003 END IF;
2004
2005
2006 IF (p_sorce_item_id IS NOT NULL AND p_source_rev_id IS NOT NULL AND p_source_org_id IS NOT NULL ) THEN
2007 INSERT INTO EGO_TRANS_ATTR_VERS_B
2008 (SELECT association_id,attr_id,icc_version_number,attr_display_name,SEQUENCE,value_set_id,uom_class,
2009 default_value,rejected_value,required_flag,readonly_flag,hidden_flag,searchable_flag,
2010 check_eligibility,p_dest_item_id,p_dest_org_id,p_dest_rev_id,'ITM',G_CURRENT_USER_ID,
2011 sysdate,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID,program_application_id,
2012 program_id,program_update_date,request_id,item_catalog_group_id
2013 FROM EGO_TRANS_ATTR_VERS_B
2014 WHERE inventory_item_id =p_sorce_item_id
2015 AND organization_id =p_source_org_id
2016 AND revision_id= p_source_rev_id);
2017 /*When icc_id and ver_id is passed*/
2018 ELSIF (p_source_icc_id IS NOT NULL AND p_source_ver_no IS NOT NULL) THEN
2019 INSERT INTO EGO_TRANS_ATTR_VERS_B
2020 (SELECT association_id,attr_id,p_dest_ver_no,attr_display_name,SEQUENCE,value_set_id,uom_class,
2021 default_value,rejected_value,required_flag,readonly_flag,hidden_flag,searchable_flag,
2022 check_eligibility,inventory_item_id,organization_id,revision_id,'ICC',G_CURRENT_USER_ID,
2023 sysdate,G_CURRENT_USER_ID,SYSDATE,G_CURRENT_LOGIN_ID,program_application_id,
2024 program_id,program_update_date,request_id,p_dest_icc_id
2025 FROM EGO_TRANS_ATTR_VERS_B
2026 WHERE ITEM_CATALOG_GROUP_ID =p_source_icc_id
2027 AND ICC_VERSION_NUMBER = p_source_ver_no);
2028
2029 END IF;
2030
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 x_return_status := G_STATUS_ERROR;
2034 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
2035 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
2036 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
2037 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
2038 FND_MSG_PUB.Add;
2039 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2040 ,p_count => x_msg_count
2041 ,p_data => x_msg_data);
2042
2043 END;
2044
2045
2046 /* procedure to copy data from source Id's to destination Id's*/
2047 PROCEDURE Revert_Transaction_Attribute (
2048 p_source_icc_id IN NUMBER,
2049 p_source_ver_no IN NUMBER,
2050 p_init_msg_list IN BOOLEAN, --- Bug 9791391, made default true in spec to maintain existing TA code
2051 x_return_status OUT NOCOPY VARCHAR2,
2052 x_msg_count OUT NOCOPY NUMBER,
2053 x_msg_data OUT NOCOPY VARCHAR2)
2054 IS
2055
2056 l_api_name CONSTANT VARCHAR2(30) := 'Revert_Transaction_Attribute';
2057
2058 BEGIN
2059 --Reset all global variables
2060 IF p_init_msg_list THEN --- Bug 9791391 , added IF condition
2061 FND_MSG_PUB.Initialize;
2062 END IF;
2063
2064
2065 DELETE FROM EGO_TRANS_ATTR_VERS_B
2066 WHERE ITEM_CATALOG_GROUP_ID = p_source_icc_id
2067 AND ICC_VERSION_NUMBER = 0;
2068
2069 Copy_Transaction_Attribute (
2070 p_source_icc_id => p_source_icc_id
2071 ,p_source_ver_no => p_source_ver_no
2072 ,p_sorce_item_id => NULL
2073 ,p_source_rev_id => NULL
2074 ,p_source_org_id => NULL
2075 ,p_dest_icc_id => p_source_icc_id
2076 ,p_dest_ver_no => 0
2077 ,p_dest_item_id => NULL
2078 ,p_dest_rev_id => NULL
2079 ,p_dest_org_id => NULL
2080 ,x_return_status => x_return_status
2081 ,x_msg_count => x_msg_count
2082 ,x_msg_data => x_msg_data
2083 );
2084
2085
2086 EXCEPTION
2087 WHEN OTHERS
2088 THEN
2089 x_return_status := G_STATUS_ERROR;
2090 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
2091 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
2092 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
2093 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
2094 FND_MSG_PUB.Add;
2095 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2096 ,p_count => x_msg_count
2097 ,p_data => x_msg_data);
2098 END;
2099
2100
2101 /* Function will return true if a Value Set was passed in is not compatible with
2102 the data type */
2103 FUNCTION Check_VS_Data_Type (
2104 p_value_set_id IN NUMBER,
2105 p_data_type IN VARCHAR2
2106 )
2107 RETURN BOOLEAN
2108 IS
2109 l_value_set_id NUMBER:=p_value_set_id;
2110 l_ta_invalid_vs_data_type BOOLEAN := FALSE;
2111 l_value_set_format_code VARCHAR2(1);
2112 l_vs_valid_data_type NUMBER:=0;
2113
2114 BEGIN
2115
2116 SELECT Count(*) cnt
2117 INTO l_vs_valid_data_type
2118 FROM EGO_VS_FORMAT_CODES_V
2119 WHERE lookup_code IN (p_data_type);
2120
2121 IF (l_vs_valid_data_type=0) THEN
2122 l_ta_invalid_vs_data_type:=FALSE;
2123 ELSE
2124 l_ta_invalid_vs_data_type:=TRUE ;
2125 END IF ;
2126
2127
2128 -------------------------------------------------------------------------------------
2129 -- Make sure that if a Value Set was passed in, it's compatible with the data type --
2130 -------------------------------------------------------------------------------------
2131 SELECT FORMAT_TYPE
2132 INTO l_value_set_format_code
2133 FROM FND_FLEX_VALUE_SETS
2134 WHERE FLEX_VALUE_SET_ID = l_value_set_id;
2135
2136 IF (l_value_set_format_code IS NULL OR (l_value_set_format_code <> p_data_type))
2137 THEN
2138 l_ta_invalid_vs_data_type:=FALSE;
2139 ELSE
2140 l_ta_invalid_vs_data_type:= TRUE;
2141 END IF;
2142
2143 RETURN l_ta_invalid_vs_data_type;
2144
2145 END Check_VS_Data_Type;
2146
2147 /* Function to get attribute display name for passed in parameter. */
2148
2149 FUNCTION GET_ATTR_DISP_NAME (
2150 P_ITEM_CAT_GROUP_ID IN NUMBER,
2151 P_ICC_VERSION_NUMBER IN NUMBER,
2152 P_INVENTORY_ITEM_ID IN NUMBER DEFAULT NULL,
2153 P_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
2154 P_REVISION_ID IN NUMBER DEFAULT NULL,
2155 P_CREATION_DATE IN DATE DEFAULT NULL,
2156 P_START_DATE IN DATE DEFAULT NULL,
2157 P_ATTR_ID IN NUMBER
2158 )
2159 RETURN VARCHAR2
2160 IS
2161 l_dynamic_sql VARCHAR2(4000):=NULL;
2162 l_attr_disp_name_sql VARCHAR2(4000):=NULL;
2163 l_attr_disp_name VARCHAR2(80) :=NULL;
2164 BEGIN
2165 l_attr_disp_name_sql := ' SELECT ATTR_DISPLAY_NAME FROM (';
2166 l_dynamic_sql :=
2167 ' SELECT * '||
2168 ' FROM '||
2169 ' ( SELECT * '||
2170 ' FROM '||
2171 ' ( SELECT TA_VERS.item_catalog_group_id, '||
2172 ' TA_VERS.ICC_VERSION_NUMBER , '||
2173 ' TA_VERS.ATTR_ID , '||
2174 ' TA_VERS.attr_display_name , '||
2175 ' TA_VERS.metadata_level , '||
2176 ' TA_VERS.INVENTORY_ITEM_ID , '||
2177 ' TA_VERS.ORGANIZATION_ID , '||
2178 ' TA_VERS.REVISION_ID , '||
2179 ' TA_VERS.VALUE_SET_ID , '||
2180 ' HIERLEVEL.lev '||
2181 ' FROM EGO_TRANS_ATTR_VERS_B TA_VERS , '||
2182 ' ( SELECT ITEM_CATALOG_GROUP_ID , '||
2183 ' LEVEL LEV '||
2184 ' FROM MTL_ITEM_CATALOG_GROUPS_B '||
2185 ' START WITH ITEM_CATALOG_GROUP_ID =:1 '||
2186 ' CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID '||
2187 ' ) HIERLEVEL '||
2188 ' WHERE HIERLEVEL.ITEM_CATALOG_GROUP_ID = TA_VERS.item_catalog_group_id '||
2189 ' ) '||
2190 ' WHERE '||
2191 ' ( '||
2192 ' ( '||
2193 ' LEV = 1 '||
2194 ' AND ICC_VERSION_NUMBER = :2 '||
2195 ' ) '||
2196 ' OR '||
2197 ' ( '||
2198 ' METADATA_LEVEL = ''ITM'''||
2199 ' AND INVENTORY_ITEM_ID = :3 '||
2200 ' AND ORGANIZATION_ID = :4 '||
2201 ' AND REVISION_ID = :5 '||
2202 ' ) '||
2203 ' OR '||
2204 ' ( '||
2205 ' LEV > 1 '||
2206 ' AND metadata_level=''ICC'''||
2207 ' AND '||
2208 ' ( '||
2209 ' item_catalog_group_id, ICC_VERSION_NUMBER '||
2210 ' ) '||
2211 ' IN '||
2212 ' ( SELECT item_catalog_group_id,VERSION_SEQ_ID '||
2213 ' FROM EGO_MTL_CATALOG_GRP_VERS_B ';
2214
2215 IF (P_ICC_VERSION_NUMBER =0 ) THEN
2216 l_dynamic_sql := l_dynamic_sql ||
2217 ' WHERE start_active_date <= :6 '||
2218 ' AND '||
2219 ' ( '||
2220 ' end_active_date IS NULL '||
2221 ' OR end_active_date>=:7 '||
2222 ' ) ';
2223 ELSE
2224 l_dynamic_sql := l_dynamic_sql ||
2225 ' WHERE '||
2226 ' ( '||
2227 ' item_catalog_group_id,start_active_date '||
2228 ' ) '||
2229 ' IN '||
2230 ' ( SELECT item_catalog_group_id, MAX(start_active_date) start_active_date '||
2231 ' FROM EGO_MTL_CATALOG_GRP_VERS_B '||
2232 ' WHERE creation_date <= :6 '||
2233 ' AND version_seq_id > 0 '||
2234 ' AND start_active_date <= :7 '||
2235 ' GROUP BY item_catalog_group_id '||
2236 ' HAVING MAX(start_active_date)<=:8 '||
2237 ' ) ';
2238 END IF;
2239 /* l_value_set_sql:= l_dynamic_sql ||
2240 ' AND version_seq_id > 0 '||
2241 ' ) '||
2242 ' ) '||
2243 ' ) '||
2244 ' ORDER BY METADATA_LEVEL DESC ,LEV ASC '||
2245 ' ) '||
2246 ' WHERE attr_id = :9 --ATTRS.attr_id '||
2247 ' AND ROWNUM<2 ' ; */
2248 l_dynamic_sql := l_dynamic_sql ||
2249 ' AND version_seq_id > 0 '||
2250 ' ) '||
2251 ' ) '||
2252 ' ) '||
2253 ' ORDER BY METADATA_LEVEL DESC ,LEV ASC '||
2254 ' ) ';
2255
2256 IF (P_ICC_VERSION_NUMBER =0 ) THEN
2257 l_dynamic_sql := l_dynamic_sql ||
2258 ' WHERE attr_id =:8';
2259 ELSE
2260 l_dynamic_sql := l_dynamic_sql ||
2261 ' WHERE attr_id =:9';
2262 END IF;
2263 l_dynamic_sql := l_dynamic_sql ||
2264 ' ORDER BY METADATA_LEVEL DESC ,LEV ASC ) ';
2265
2266
2267 l_attr_disp_name_sql:= l_attr_disp_name_sql||l_dynamic_sql ||
2268 ' WHERE ATTR_DISPLAY_NAME IS NOT NULL '||
2269 ' AND ROWNUM<2 ' ;
2270
2271 IF (P_ICC_VERSION_NUMBER =0 ) THEN
2272 EXECUTE IMMEDIATE l_attr_disp_name_sql INTO l_attr_disp_name
2273 USING P_ITEM_CAT_GROUP_ID,
2274 P_ICC_VERSION_NUMBER,
2275 P_INVENTORY_ITEM_ID,
2276 P_ORGANIZATION_ID,
2277 P_REVISION_ID,
2278 P_START_DATE,
2279 P_START_DATE,
2280 P_ATTR_ID;
2281 ELSE
2282 EXECUTE IMMEDIATE l_attr_disp_name_sql INTO l_attr_disp_name
2283 USING P_ITEM_CAT_GROUP_ID,
2284 P_ICC_VERSION_NUMBER,
2285 P_INVENTORY_ITEM_ID,
2286 P_ORGANIZATION_ID,
2287 P_REVISION_ID,
2288 P_CREATION_DATE,
2289 P_START_DATE,
2290 P_START_DATE,
2291 P_ATTR_ID;
2292 END IF;
2293 RETURN L_ATTR_DISP_NAME;
2294 END;
2295
2296
2297
2298 /* Function to get value set id associated to a transaction attribute for passed in parameter. */
2299 FUNCTION GET_VS_ID (
2300 P_ITEM_CAT_GROUP_ID IN NUMBER,
2301 P_ICC_VERSION_NUMBER IN NUMBER,
2302 P_INVENTORY_ITEM_ID IN NUMBER DEFAULT NULL,
2303 P_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
2304 P_REVISION_ID IN NUMBER DEFAULT NULL,
2305 P_CREATION_DATE IN DATE DEFAULT NULL,
2306 P_START_DATE IN DATE DEFAULT NULL,
2307 P_ATTR_ID IN NUMBER
2308 )
2309 RETURN NUMBER
2310 IS
2311 l_dynamic_sql VARCHAR2(4000):=NULL;
2312 l_value_set_sql VARCHAR2(4000):=NULL;
2313 l_value_set_id NUMBER :=NULL;
2314 BEGIN
2315 l_value_set_sql := ' SELECT VALUE_SET_ID FROM (';
2316 l_dynamic_sql :=
2317 ' SELECT * '||
2318 ' FROM '||
2319 ' ( SELECT * '||
2320 ' FROM '||
2321 ' ( SELECT TA_VERS.item_catalog_group_id, '||
2322 ' TA_VERS.ICC_VERSION_NUMBER , '||
2323 ' TA_VERS.ATTR_ID , '||
2324 ' TA_VERS.attr_display_name , '||
2325 ' TA_VERS.metadata_level , '||
2326 ' TA_VERS.INVENTORY_ITEM_ID , '||
2327 ' TA_VERS.ORGANIZATION_ID , '||
2328 ' TA_VERS.REVISION_ID , '||
2329 ' TA_VERS.VALUE_SET_ID , '||
2330 ' HIERLEVEL.lev '||
2331 ' FROM EGO_TRANS_ATTR_VERS_B TA_VERS , '||
2332 ' ( SELECT ITEM_CATALOG_GROUP_ID , '||
2333 ' LEVEL LEV '||
2334 ' FROM MTL_ITEM_CATALOG_GROUPS_B '||
2335 ' START WITH ITEM_CATALOG_GROUP_ID =:1 '||
2336 ' CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID '||
2337 ' ) HIERLEVEL '||
2338 ' WHERE HIERLEVEL.ITEM_CATALOG_GROUP_ID = TA_VERS.item_catalog_group_id '||
2339 ' ) '||
2340 ' WHERE '||
2341 ' ( '||
2342 ' ( '||
2343 ' LEV = 1 '||
2344 ' AND ICC_VERSION_NUMBER = :2 '||
2345 ' ) '||
2346 ' OR '||
2347 ' ( '||
2348 ' METADATA_LEVEL = ''ITM'''||
2349 ' AND INVENTORY_ITEM_ID = :3 '||
2350 ' AND ORGANIZATION_ID = :4 '||
2351 ' AND REVISION_ID = :5 '||
2352 ' ) '||
2353 ' OR '||
2354 ' ( '||
2355 ' LEV > 1 '||
2356 ' AND metadata_level=''ICC'''||
2357 ' AND '||
2358 ' ( '||
2359 ' item_catalog_group_id, ICC_VERSION_NUMBER '||
2360 ' ) '||
2361 ' IN '||
2362 ' ( SELECT item_catalog_group_id,VERSION_SEQ_ID '||
2363 ' FROM EGO_MTL_CATALOG_GRP_VERS_B ';
2364
2365 IF (P_ICC_VERSION_NUMBER =0 ) THEN
2366 l_dynamic_sql := l_dynamic_sql ||
2367 ' WHERE start_active_date <= :6 '||
2368 ' AND '||
2369 ' ( '||
2370 ' end_active_date IS NULL '||
2371 ' OR end_active_date>=:7 '||
2372 ' ) ';
2373 ELSE
2374 l_dynamic_sql := l_dynamic_sql ||
2375 ' WHERE '||
2376 ' ( '||
2377 ' item_catalog_group_id,start_active_date '||
2378 ' ) '||
2379 ' IN '||
2380 ' ( SELECT item_catalog_group_id, MAX(start_active_date) start_active_date '||
2381 ' FROM EGO_MTL_CATALOG_GRP_VERS_B '||
2382 ' WHERE creation_date <= :6 '||
2383 ' AND version_seq_id > 0 '||
2384 ' AND start_active_date <= :7 '||
2385 ' GROUP BY item_catalog_group_id '||
2386 ' HAVING MAX(start_active_date)<=:8 '||
2387 ' ) ';
2388 END IF;
2389 /* l_value_set_sql:= l_dynamic_sql ||
2390 ' AND version_seq_id > 0 '||
2391 ' ) '||
2392 ' ) '||
2393 ' ) '||
2394 ' ORDER BY METADATA_LEVEL DESC ,LEV ASC '||
2395 ' ) '||
2396 ' WHERE attr_id = :9 --ATTRS.attr_id '||
2397 ' AND ROWNUM<2 ' ; */
2398 l_dynamic_sql := l_dynamic_sql ||
2399 ' AND version_seq_id > 0 '||
2400 ' ) '||
2401 ' ) '||
2402 ' ) '||
2403 ' ORDER BY METADATA_LEVEL DESC ,LEV ASC '||
2404 ' ) ';
2405
2406
2407 IF (P_ICC_VERSION_NUMBER =0 ) THEN
2408 l_dynamic_sql := l_dynamic_sql ||
2409 ' WHERE attr_id =:8';
2410 ELSE
2411 l_dynamic_sql := l_dynamic_sql ||
2412 ' WHERE attr_id =:9';
2413 END IF;
2414 l_dynamic_sql := l_dynamic_sql ||
2415 ' ORDER BY METADATA_LEVEL DESC ,LEV ASC ) ';
2416
2417 l_value_set_sql:= l_value_set_sql||l_dynamic_sql ||
2418 ' WHERE ROWNUM<2 ' ;
2419
2420 IF (P_ICC_VERSION_NUMBER =0 ) THEN
2421 EXECUTE IMMEDIATE l_value_set_sql INTO l_value_set_id
2422 USING P_ITEM_CAT_GROUP_ID,
2423 P_ICC_VERSION_NUMBER,
2424 P_INVENTORY_ITEM_ID,
2425 P_ORGANIZATION_ID,
2426 P_REVISION_ID,
2427 P_START_DATE,
2428 P_START_DATE,
2429 P_ATTR_ID;
2430 ELSE
2431 EXECUTE IMMEDIATE l_value_set_sql INTO l_value_set_id
2432 USING P_ITEM_CAT_GROUP_ID,
2433 P_ICC_VERSION_NUMBER,
2434 P_INVENTORY_ITEM_ID,
2435 P_ORGANIZATION_ID,
2436 P_REVISION_ID,
2437 P_CREATION_DATE,
2438 P_START_DATE,
2439 P_START_DATE,
2440 P_ATTR_ID;
2441
2442 END IF;
2443 RETURN L_VALUE_SET_ID;
2444 END;
2445
2446 PROCEDURE has_invalid_char (
2447 p_internal_name IN VARCHAR2,
2448 x_has_invalid_chars OUT NOCOPY VARCHAR2
2449 )IS
2450 l_internal_name varchar2(1000);
2451 l_counter number :=0;
2452 l_curr_char varchar2(1);
2453 BEGIN
2454 l_internal_name:=p_internal_name;
2455 IF (l_internal_name IS null) THEN
2456 x_has_invalid_chars :='N';
2457 END IF;
2458 l_internal_name:=trim(l_internal_name);
2459 WHILE(l_counter <=length(l_internal_name)) loop
2460 l_curr_char:=SubStr(l_internal_name, l_counter,1);
2461 IF (regexp_like(l_curr_char, '[0-9a-zA-Z_]')) THEN
2462 l_counter:=l_counter+1;
2463 x_has_invalid_chars := 'N';
2464 ELSE
2465 x_has_invalid_chars :='Y';
2466 exit;
2467 END IF;
2468 END LOOP;
2469 EXCEPTION
2470 WHEN OTHERS THEN
2471 x_has_invalid_chars:='N';
2472 NULL;
2473 END has_invalid_char;
2474
2475
2476 /*API will return the record of transaction attribute metadata based on given input.It will return null
2477 if given input is invalid */
2478 PROCEDURE GET_TRANS_ATTR_METADATA( x_ta_metadata_tbl OUT NOCOPY EGO_TRAN_ATTR_TBL,
2479 p_item_catalog_category_id IN number,
2480 p_icc_version IN number,
2481 p_attribute_id IN NUMBER,
2482 p_inventory_item_id IN NUMBER ,
2483 p_organization_id IN NUMBER,
2484 p_revision_id IN NUMBER ,
2485 x_return_status OUT NOCOPY VARCHAR2 ,
2486 x_is_inherited OUT NOCOPY VARCHAR2 ,
2487 x_is_modified OUT NOCOPY varchar2
2488 )
2489
2490 IS
2491
2492 l_item_catalog_group_id VARCHAR2(10);
2493 l_efectivity_date DATE ; /*Item revision effective date.*/
2494 l_creation_date DATE;-- MTL_ITEM_REVISIONS_VL.CREATION_DATE
2495 l_icc_start_date DATE ;/*ICC start effective date*/
2496 l_start_active_date DATE ;/*ICC start effective date*/
2497 l_icc_create_date DATE ;/*ICC create date*/
2498 l_version_seq_id VARCHAR2(5);
2499 l_max_start_date DATE;
2500 l_exception EXCEPTION;
2501 l_associationid VARCHAR2(5);
2502 l_ATTRID NUMBER:=0;
2503 l_icc_version_number VARCHAR2(15);
2504 l_Value_Set_Id VARCHAR2(15);
2505 l_uom_class VARCHAR2(10);
2506 l_ta_metadata_tbl EGO_TRAN_ATTR_TBL;
2507 l_default_value VARCHAR2(2000);
2508 l_rejectedvalue VARCHAR2(2000);
2509 l_required VARCHAR2(1);
2510 l_readonlyflag VARCHAR2(1);
2511 l_hiddenflag VARCHAR2(1);
2512 l_searchable VARCHAR2(1);
2513 l_checkeligibility VARCHAR2(1);
2514 l_organization_id VARCHAR2(10);
2515 l_metadatalevel VARCHAR2(10);
2516 l_attr_disp_name VARCHAR2(80);
2517 l_no_of_rows NUMBER;
2518 l_level VARCHAR2(50);
2519 l_value_set_name VARCHAR2(60); -- Bug 8643860
2520 l_attr_seq NUMBER; -- Bug 8643860
2521 l_data_type_code VARCHAR2(1); -- Bug 14851479
2522
2523 CURSOR get_Item_TA_metadata(
2524 v_ITEM_CATALOG_GROUP_ID in NUMBER,
2525 v_attr_id in number,
2526 v_INVENTORY_ITEM_ID in number,
2527 v_ORGANIZATION_ID in number,
2528 v_REVISION_ID in number,
2529 v_creation_date in date,
2530 v_start_active_date in date,
2531 v_max_start_active_date in date
2532 ) IS
2533
2534 SELECT * FROM
2535 ( SELECT versions.item_catalog_group_id,
2536 versions.ICC_VERSION_NUMBER ,
2537 versions.ATTR_ID ,
2538 versions.attr_display_name ,
2539 versions.SEQUENCE , -- Bug 8643860
2540 versions.metadata_level ,
2541 versions.association_id ,
2542 VERSIONS.VALUE_SET_ID ,
2543 VERSIONS.UOM_CLASS ,
2544 VERSIONS.DEFAULT_VALUE ,
2545 versions.revision_id ,
2546 versions.organization_id ,
2547 versions.inventory_item_id ,
2548 VERSIONS.REJECTED_VALUE ,
2549 VERSIONS.REQUIRED_FLAG ,
2550 VERSIONS.READONLY_FLAG ,
2551 VERSIONS.HIDDEN_FLAG ,
2552 VERSIONS.SEARCHABLE_FLAG ,
2553 VERSIONS.CHECK_ELIGIBILITY ,
2554 Hier.lev
2555 FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
2556 (SELECT ITEM_CATALOG_GROUP_ID , LEVEL LEV
2557 FROM MTL_ITEM_CATALOG_GROUPS_B START WITH ITEM_CATALOG_GROUP_ID = v_ITEM_CATALOG_GROUP_ID CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
2558 ) HIER
2559 WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id AND versions.attr_id = v_attr_id )
2560 WHERE
2561 ( (LEV =1 AND ICC_VERSION_NUMBER = l_version_seq_id AND METADATA_LEVEL = 'ICC' )
2562 OR
2563 (
2564 METADATA_LEVEL = 'ITM' AND
2565 INVENTORY_ITEM_ID = v_INVENTORY_ITEM_ID AND
2566 ORGANIZATION_ID = v_ORGANIZATION_ID AND
2567 REVISION_ID = v_REVISION_ID
2568 )
2569 OR
2570 (
2571 LEV > 1 AND
2572 metadata_level='ICC'
2573 AND
2574 (
2575 item_catalog_group_id, ICC_VERSION_NUMBER
2576 )
2577 IN
2578 ( SELECT item_catalog_group_id,VERSION_SEQ_ID
2579 FROM EGO_MTL_CATALOG_GRP_VERS_B
2580 WHERE
2581 (
2582 item_catalog_group_id,start_active_date
2583 )
2584 IN
2585 (SELECT item_catalog_group_id,MAX(start_active_date) start_active_date
2586 FROM EGO_MTL_CATALOG_GRP_VERS_B
2587 WHERE creation_date <= v_creation_date
2588 AND version_seq_id > 0
2589 AND start_active_date <= v_start_active_date
2590 GROUP BY item_catalog_group_id
2591 HAVING MAX(start_active_date)<= v_max_start_active_date
2592 )
2593 AND version_seq_id > 0
2594 )
2595 ) )
2596 ORDER BY METADATA_LEVEL DESC ,LEV ASC ; -- Bug 14121048
2597
2598 get_Item_TA_metadata_rec get_Item_TA_metadata%ROWTYPE;
2599
2600 CURSOR get_ICC_TA_metadata(
2601 v_ITEM_CATALOG_GROUP_ID in NUMBER,
2602 v_attr_id in number,
2603 v_creation_date in date,
2604 v_start_active_date in date,
2605 v_max_start_active_date in date
2606 ) IS
2607
2608 SELECT * FROM
2609 ( SELECT versions.item_catalog_group_id,
2610 versions.ICC_VERSION_NUMBER ,
2611 versions.ATTR_ID ,
2612 versions.attr_display_name ,
2613 versions.SEQUENCE , -- Bug 8643860
2614 versions.metadata_level ,
2615 versions.association_id ,
2616 VERSIONS.VALUE_SET_ID ,
2617 VERSIONS.UOM_CLASS ,
2618 VERSIONS.DEFAULT_VALUE ,
2619 versions.revision_id ,
2620 versions.organization_id ,
2621 versions.inventory_item_id ,
2622 VERSIONS.REJECTED_VALUE ,
2623 VERSIONS.REQUIRED_FLAG ,
2624 VERSIONS.READONLY_FLAG ,
2625 VERSIONS.HIDDEN_FLAG ,
2626 VERSIONS.SEARCHABLE_FLAG ,
2627 VERSIONS.CHECK_ELIGIBILITY ,
2628 Hier.lev
2629 FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
2630 (SELECT ITEM_CATALOG_GROUP_ID , LEVEL LEV
2631 FROM MTL_ITEM_CATALOG_GROUPS_B START WITH ITEM_CATALOG_GROUP_ID = v_ITEM_CATALOG_GROUP_ID CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
2632 ) HIER
2633 WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id AND versions.attr_id = v_attr_id )
2634 WHERE
2635 ( (LEV =1 AND ICC_VERSION_NUMBER = p_icc_version AND METADATA_LEVEL = 'ICC' )
2636
2637 OR
2638 (
2639 LEV > 1
2640 AND metadata_level='ICC'
2641 AND
2642 (
2643 item_catalog_group_id, ICC_VERSION_NUMBER
2644 )
2645 IN
2646 ( SELECT item_catalog_group_id,
2647 VERSION_SEQ_ID
2648 FROM EGO_MTL_CATALOG_GRP_VERS_B
2649 WHERE
2650 (
2651 item_catalog_group_id,start_active_date
2652 )
2653 IN
2654 (SELECT item_catalog_group_id,
2655 MAX(start_active_date) start_active_date
2656 FROM EGO_MTL_CATALOG_GRP_VERS_B
2657 WHERE creation_date <= v_creation_date
2658 AND version_seq_id > 0
2659 AND start_active_date <= v_start_active_date
2660 GROUP BY item_catalog_group_id
2661 HAVING MAX(start_active_date)<= v_max_start_active_date
2662 )
2663 AND version_seq_id > 0
2664 )
2665 )
2666 )
2667 ORDER BY LEV ASC ; -- Bug 14121048 , Though not require for this bug, However it may result wrong data if sql does not sort. Added to avoid any future bug.
2668
2669 get_ICC_TA_metadata_rec get_ICC_TA_metadata%ROWTYPE;
2670
2671 BEGIN
2672 x_ta_metadata_tbl := EGO_TRAN_ATTR_TBL(NULL);
2673 x_is_inherited := 'N';
2674 x_is_modified := 'N';
2675 IF(p_attribute_id IS null) THEN
2676 RAISE l_exception ;
2677 END IF ;
2678
2679 IF(p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL AND p_revision_id IS NOT null)
2680 THEN
2681 x_is_inherited := 'Y';
2682 --Finding which ICC is associated ot the item.
2683 SELECT ITEM_CATALOG_GROUP_ID INTO l_item_catalog_group_id FROM MTL_SYSTEM_ITEMS_VL
2684 WHERE INVENTORY_ITEM_ID = p_inventory_item_id AND ORGANIZATION_ID = p_organization_id ;
2685
2686 IF(l_ITEM_CATALOG_GROUP_ID IS NULL ) THEN
2687 RAISE l_exception ;
2688 ELSE
2689 SELECT EFFECTIVITY_DATE ,CREATION_DATE INTO l_efectivity_date,l_creation_date
2690 FROM MTL_ITEM_REVISIONS_VL WHERE INVENTORY_ITEM_ID = p_inventory_item_id
2691 AND ORGANIZATION_ID = p_organization_id AND REVISION_ID = p_revision_id;
2692
2693 --Finding out which ICC version is effective at a time of item creation.
2694
2695 SELECT VERSION_SEQ_ID INTO l_version_seq_id FROM EGO_MTL_CATALOG_GRP_VERS_B
2696 WHERE (ITEM_CATALOG_GROUP_ID, start_active_date) IN
2697 (
2698
2699 SELECT ITEM_CATALOG_GROUP_ID,Max(START_ACTIVE_DATe) START_ACTIVE_DATe
2700 FROM EGO_MTL_CATALOG_GRP_VERS_B
2701 WHERE CREATION_DATE <= l_creation_date AND
2702 ITEM_CATALOG_GROUP_ID = l_item_catalog_group_id AND
2703 START_ACTIVE_DATE <= l_efectivity_date AND VERSION_SEQ_ID >0
2704 GROUP BY ITEM_CATALOG_GROUP_ID
2705 HAVING MAX(START_ACTIVE_DATE) <= l_efectivity_date
2706 ) ;
2707 /*bug 8767080
2708 --Finding out start effcetive date and creation date of ICC Version
2709 SELECT START_ACTIVE_DATE,CREATION_DATE INTO l_icc_start_date, l_icc_create_date
2710 FROM EGO_MTL_CATALOG_GRP_VERS_B
2711 WHERE ITEM_CATALOG_GROUP_ID = l_item_catalog_group_id AND VERSION_SEQ_ID = l_version_seq_id;
2712 */
2713 END IF;
2714
2715 OPEN get_Item_TA_metadata(
2716 v_ITEM_CATALOG_GROUP_ID => l_item_catalog_group_id,
2717 v_attr_id =>p_attribute_id,
2718 v_INVENTORY_ITEM_ID =>p_inventory_item_id,
2719 v_ORGANIZATION_ID => p_organization_id,
2720 v_REVISION_ID => p_revision_id,
2721 v_creation_date =>l_creation_date, --bug 8767080
2722 v_start_active_date => l_efectivity_date, --bug 8767080
2723 v_max_start_active_date => l_efectivity_date --bug 8767080
2724 ) ;
2725
2726 LOOP
2727 FETCH get_Item_TA_metadata INTO get_Item_TA_metadata_rec;
2728 EXIT WHEN get_Item_TA_metadata%NOTFOUND;
2729
2730 IF(get_Item_TA_metadata_rec.association_id is not null ) THEN
2731 l_associationid := get_Item_TA_metadata_rec.association_id;
2732 END IF;
2733
2734
2735 IF(get_Item_TA_metadata_rec.ATTR_ID is not null ) THEN
2736 l_ATTRID := get_Item_TA_metadata_rec.ATTR_ID ;
2737 END IF;
2738
2739 IF(get_Item_TA_metadata_rec.ICC_VERSION_NUMBER is not null AND l_icc_version_number IS null ) THEN
2740 l_icc_version_number := get_Item_TA_metadata_rec.ICC_VERSION_NUMBER ;
2741 END IF;
2742 if(get_Item_TA_metadata_rec.VALUE_SET_ID is not null AND l_Value_Set_Id IS null ) THEN
2743 l_Value_Set_Id := get_Item_TA_metadata_rec.VALUE_SET_ID ;
2744
2745 -- Bug 8643860
2746 SELECT FLEX_VALUE_SET_NAME INTO l_value_set_name
2747 FROM FND_FLEX_VALUE_SETS
2748 WHERE FLEX_VALUE_SET_ID = l_Value_Set_Id;
2749 END IF;
2750
2751 IF(get_Item_TA_metadata_rec.UOM_CLASS is not NULL AND l_uom_class IS null ) THEN
2752 l_uom_class := get_Item_TA_metadata_rec.UOM_CLASS ;
2753 END IF;
2754
2755 IF(get_Item_TA_metadata_rec.DEFAULT_VALUE is not NULL AND l_default_value IS null ) THEN
2756 l_default_value := get_Item_TA_metadata_rec.DEFAULT_VALUE ;
2757 END IF;
2758
2759 if(get_Item_TA_metadata_rec.REJECTED_VALUE is not null AND l_rejectedvalue IS null ) THEN
2760 l_rejectedvalue := get_Item_TA_metadata_rec.REJECTED_VALUE ;
2761 END IF;
2762
2763 IF(get_Item_TA_metadata_rec.REQUIRED_FLAG is not null AND l_required IS null) THEN
2764 l_required := get_Item_TA_metadata_rec.REQUIRED_FLAG ;
2765 END IF;
2766
2767
2768 IF(get_Item_TA_metadata_rec.READONLY_FLAG is not null AND l_readonlyflag IS null) THEN
2769 l_readonlyflag := get_Item_TA_metadata_rec.READONLY_FLAG ;
2770 END IF;
2771
2772 IF(get_Item_TA_metadata_rec.HIDDEN_FLAG is not null AND l_hiddenflag IS null ) THEN
2773 l_hiddenflag := get_Item_TA_metadata_rec.HIDDEN_FLAG ;
2774 END IF;
2775
2776 IF(get_Item_TA_metadata_rec.SEARCHABLE_FLAG is not null AND l_searchable IS null) THEN
2777 l_searchable := get_Item_TA_metadata_rec.SEARCHABLE_FLAG ;
2778 END IF;
2779
2780 IF(get_Item_TA_metadata_rec.CHECK_ELIGIBILITY is not NULL AND l_checkeligibility IS null ) THEN
2781 l_checkeligibility := get_Item_TA_metadata_rec.CHECK_ELIGIBILITY ;
2782 END IF;
2783 if(get_Item_TA_metadata_rec.organization_id is not NULL AND l_organization_id IS null ) THEN
2784 l_organization_id := get_Item_TA_metadata_rec.organization_id ;
2785 END IF;
2786
2787 IF(get_Item_TA_metadata_rec.metadata_level is not null AND l_metadatalevel IS null) THEN
2788 l_metadatalevel := get_Item_TA_metadata_rec.metadata_level ;
2789
2790 -- Bug 8643860
2791 IF (l_metadatalevel = 'ITM') THEN
2792 x_is_modified :='Y';
2793 END IF;
2794
2795 END IF;
2796
2797 IF(get_Item_TA_metadata_rec.attr_display_name is not null AND l_attr_disp_name IS null ) THEN
2798 l_attr_disp_name := get_Item_TA_metadata_rec.attr_display_name ;
2799 END IF;
2800
2801 -- Bug 8643860
2802 IF (get_Item_TA_metadata_rec.SEQUENCE IS NOT NULL AND l_attr_seq is NULL) THEN
2803 l_attr_seq := get_Item_TA_metadata_rec.SEQUENCE;
2804 END IF;
2805
2806 END LOOP;
2807 CLOSE get_Item_TA_metadata;
2808 ELSE IF (p_item_catalog_category_id IS NOT NULL AND p_icc_version IS NOT NULL) THEN
2809 /*Finding out start effcetive date and creation date of ICC Version */
2810 SELECT START_ACTIVE_DATE,CREATION_DATE INTO l_icc_start_date, l_icc_create_date
2811 FROM EGO_MTL_CATALOG_GRP_VERS_B
2812 WHERE ITEM_CATALOG_GROUP_ID = p_item_catalog_category_id AND VERSION_SEQ_ID = p_icc_version;
2813
2814 OPEN GET_ICC_TA_METADATA(
2815 v_ITEM_CATALOG_GROUP_ID => p_item_catalog_category_id,
2816 v_attr_id =>p_attribute_id,
2817 v_creation_date =>l_icc_create_date,
2818 v_start_active_date => l_icc_start_date,
2819 v_max_start_active_date => l_icc_start_date
2820 ) ;
2821
2822
2823 LOOP
2824 FETCH get_ICC_TA_metadata INTO get_ICC_TA_metadata_rec;
2825 EXIT WHEN get_ICC_TA_metadata%NOTFOUND;
2826
2827 IF(get_ICC_TA_metadata_rec.association_id is not null ) THEN
2828 l_associationid := get_ICC_TA_metadata_rec.association_id;
2829 END IF;
2830
2831 IF(get_ICC_TA_metadata_rec.ATTR_ID is not null ) THEN
2832 l_ATTRID := get_ICC_TA_metadata_rec.ATTR_ID ;
2833
2834 -- fix for bug 14851479 trudave
2835 --- get data_type_code from ego_attrs_v for l_ATTRID
2836
2837 SELECT DATA_TYPE_CODE INTO l_data_type_code
2838 FROM EGO_ATTRS_V
2839 WHERE ATTR_ID = l_attrid;
2840
2841 END IF;
2842
2843 IF(get_ICC_TA_metadata_rec.ICC_VERSION_NUMBER is not null AND l_icc_version_number IS null ) THEN
2844 l_icc_version_number := get_ICC_TA_metadata_rec.ICC_VERSION_NUMBER ;
2845 END IF;
2846 IF(get_ICC_TA_metadata_rec.VALUE_SET_ID is not null AND l_Value_Set_Id IS null ) THEN
2847 l_Value_Set_Id := get_ICC_TA_metadata_rec.VALUE_SET_ID ;
2848
2849 -- Bug 8643860
2850 SELECT FLEX_VALUE_SET_NAME INTO l_value_set_name
2851 FROM FND_FLEX_VALUE_SETS
2852 WHERE FLEX_VALUE_SET_ID = l_Value_Set_Id;
2853 END IF;
2854
2855
2856 IF(get_ICC_TA_metadata_rec.UOM_CLASS is not NULL AND l_uom_class IS null ) THEN
2857 l_uom_class := get_ICC_TA_metadata_rec.UOM_CLASS ;
2858 END IF;
2859
2860
2861 IF(get_ICC_TA_metadata_rec.DEFAULT_VALUE is not NULL AND l_default_value IS null ) THEN
2862 l_default_value := get_ICC_TA_metadata_rec.DEFAULT_VALUE ;
2863 END IF;
2864
2865 IF(get_ICC_TA_metadata_rec.REJECTED_VALUE is not null AND l_rejectedvalue IS null ) THEN
2866 l_rejectedvalue := get_ICC_TA_metadata_rec.REJECTED_VALUE ;
2867 END IF;
2868
2869 IF(get_ICC_TA_metadata_rec.REQUIRED_FLAG is not null AND l_required IS null) THEN
2870 l_required := get_ICC_TA_metadata_rec.REQUIRED_FLAG ;
2871 END IF;
2872
2873 IF(get_ICC_TA_metadata_rec.READONLY_FLAG is not null AND l_readonlyflag IS null) THEN
2874 l_readonlyflag := get_ICC_TA_metadata_rec.READONLY_FLAG ;
2875 END IF;
2876
2877 IF(get_ICC_TA_metadata_rec.HIDDEN_FLAG is not null AND l_hiddenflag IS null ) THEN
2878 l_hiddenflag := get_ICC_TA_metadata_rec.HIDDEN_FLAG ;
2879 END IF;
2880
2881 IF(get_ICC_TA_metadata_rec.SEARCHABLE_FLAG is not null AND l_searchable IS null) THEN
2882 l_searchable := get_ICC_TA_metadata_rec.SEARCHABLE_FLAG ;
2883 END IF;
2884
2885 IF(get_ICC_TA_metadata_rec.CHECK_ELIGIBILITY is not NULL AND l_checkeligibility IS null ) THEN
2886 l_checkeligibility := get_ICC_TA_metadata_rec.CHECK_ELIGIBILITY ;
2887 END IF;
2888 IF(get_ICC_TA_metadata_rec.organization_id is not NULL AND l_organization_id IS null ) THEN
2889 l_organization_id := get_ICC_TA_metadata_rec.organization_id ;
2890 END IF;
2891
2892 IF(get_ICC_TA_metadata_rec.lev is not null AND l_level IS null) THEN
2893 l_level := get_ICC_TA_metadata_rec.lev ;
2894 IF(l_level =1 ) THEN
2895 x_is_modified :='Y';
2896 END IF ;
2897 END IF;
2898 IF(get_ICC_TA_metadata_rec.metadata_level is not null AND l_metadatalevel IS null) THEN
2899 l_metadatalevel := get_ICC_TA_metadata_rec.metadata_level ;
2900 END IF;
2901
2902 IF(get_ICC_TA_metadata_rec.attr_display_name is not null AND l_attr_disp_name IS null ) THEN
2903 l_attr_disp_name := get_ICC_TA_metadata_rec.attr_display_name ;
2904 END IF;
2905
2906 -- Bug 8643860
2907 -- Bug 9744800 change the get_Item_TA_metadata_rec to get_ICC_TA_metadata_rec
2908 IF (get_ICC_TA_metadata_rec.SEQUENCE IS NOT NULL AND l_attr_seq is NULL) THEN
2909 l_attr_seq := get_ICC_TA_metadata_rec.SEQUENCE;
2910 END IF;
2911
2912 END LOOP;
2913 l_no_of_rows := get_ICC_TA_metadata%ROWCOUNT;
2914 IF(l_no_of_rows >1) THEN
2915 x_is_inherited := 'y';
2916 ELSIF (x_is_modified = 'N') THEN
2917 x_is_inherited := 'Y';
2918 END IF ;
2919 CLOSE GET_ICC_TA_METADATA;
2920
2921
2922 ELSE
2923 RAISE l_exception ;
2924
2925 END if;
2926 END IF ;
2927 x_ta_metadata_tbl(1) := EGO_TRAN_ATTR_REC(
2928 l_associationid,
2929 l_ATTRID,
2930 l_icc_version_number,
2931 p_revision_id,
2932 l_attr_seq, -- Bug 8643860
2933 l_Value_Set_Id,
2934 l_uom_class,
2935 l_default_value,
2936 l_rejectedvalue,
2937 l_required,
2938 l_readonlyflag,
2939 l_hiddenflag,
2940 l_searchable,
2941 l_checkeligibility,
2942 p_inventory_item_id,
2943 l_organization_id,
2944 l_metadatalevel,
2945 null,
2946 null,
2947 null,
2948 null,
2949 null,
2950 null,
2951 null,
2952 null,
2953 null,
2954 l_item_catalog_group_id,
2955 null,
2956 l_attr_disp_name,
2957 l_data_type_code, -- Bug 14851479
2958 null ,
2959 l_value_set_name -- Bug 8643860
2960 );
2961 EXCEPTION
2962 WHEN OTHERS THEN
2963 x_return_status := 'F' ;
2964 END GET_TRANS_ATTR_METADATA;
2965
2966
2967
2968 END EGO_TRANSACTION_ATTRS_PVT;