[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_USER_ATTRS_CP_PUB
Source
1 PACKAGE BODY EGO_ITEM_USER_ATTRS_CP_PUB AS
2 /* $Header: EGOCIUAB.pls 120.80.12020000.6 2012/09/28 10:37:49 jewen ship $ */
3
4
5 -----------------------
6 -- Private Data Type --
7 -----------------------
8
9 TYPE LOCAL_MEDIUM_VARCHAR_TABLE IS TABLE OF VARCHAR2(4000)
10 INDEX BY BINARY_INTEGER;
11
12 ------------------------------
13 -- Private Global Variables --
14 ------------------------------
15
16 G_LOG_HEAD CONSTANT VARCHAR2(50) := 'fnd.plsql.ego.EGO_ITEM_USER_ATTRS_CP_PUB.';
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM_USER_ATTRS_CP_PUB';
18 G_API_VERSION NUMBER := 1.0;
19 G_ITEM_NAME CONSTANT VARCHAR2(10) := 'EGO_ITEM';
20 G_ITEM_OBJECT_ID NUMBER;
21
22 /*** The following two variables are for Error_Handler ***/
23 G_ENTITY_ID NUMBER;
24 G_ENTITY_CODE CONSTANT VARCHAR2(30) := 'ITEM_USER_ATTRS_ENTITY_CODE';
25
26 G_REQUEST_ID NUMBER;
27 G_PROGAM_APPLICATION_ID NUMBER;
28 G_PROGAM_ID NUMBER;
29 G_USER_NAME FND_USER.USER_NAME%TYPE;
30 G_USER_ID NUMBER;
31 G_LOGIN_ID NUMBER;
32 G_HZ_PARTY_ID VARCHAR2(30);
33
34 G_NO_CURRVAL_YET EXCEPTION;
35 G_NO_USER_NAME_TO_VALIDATE EXCEPTION;
36
37 --To return new set of RETCODE
38 L_CONC_RET_STS_SUCCESS VARCHAR2(1):= '0';
39 L_CONC_RET_STS_WARNING VARCHAR2(1):= '1';
40 L_CONC_RET_STS_ERROR VARCHAR2(1):= '2';
41
42 G_FND_RET_STS_WARNING VARCHAR2(1) := 'W';
43
44 G_DEBUG_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
45 G_DEBUG_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
46 G_DEBUG_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
47 G_DEBUG_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
48 G_DEBUG_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
49 G_DEBUG_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
50 G_LOG_TIMESTAMP_FORMAT CONSTANT VARCHAR2( 30 ) := 'dd-mon-yyyy hh:mi:ss.ff';
51
52 G_CURRENT_DEBUG_LEVEL NUMBER;
53 G_TABLE_NAME VARCHAR2(30) := 'EGO_ITM_USR_ATTR_INTRFC';--BUG 5352217
54
55 -------------------------------------
56 -- Pragma for Data Set ID function --
57 -------------------------------------
58 PRAGMA EXCEPTION_INIT (G_NO_CURRVAL_YET, -08002);
59
60 ----------------------------------
61 -- Private Function Declaration --
62 ----------------------------------
63
64 ----------------------------------------------------------------------
65 -- Private Procedure
66 ----------------------------------------------------------------------
67 PROCEDURE code_debug (p_log_level IN NUMBER
68 ,p_module IN VARCHAR2
69 ,p_message IN VARCHAR2
70 ) IS
71 BEGIN
72 IF (p_log_level >= G_CURRENT_DEBUG_LEVEL ) THEN
73 IF NVL(FND_GLOBAL.conc_request_id, -1) <> -1 THEN
74 FND_FILE.put_line(which => FND_FILE.LOG
75 ,buff => '['||G_PKG_NAME||' - '||p_module||'] - '||p_message);
76 ELSE
77 fnd_log.string(log_level => p_log_level
78 ,module => G_LOG_HEAD||p_module
79 ,message => p_message
80 );
81 END IF;
82 END IF;
83 /***
84 IF p_module = 'Copy_data_to_Intf' THEN
85 sri_debug(G_PKG_NAME||' - '||p_module||' - '||p_message);
86 END IF;
87 ***/
88 EXCEPTION
89 WHEN OTHERS THEN
90 RAISE;
91 END code_debug;
92
93
94 /*
95 * This method writes into concurrent program log
96 */
97 PROCEDURE Debug_Conc_Log( p_message IN VARCHAR2
98 , p_add_timestamp IN BOOLEAN DEFAULT TRUE )
99 IS
100 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
101 l_message VARCHAR2(3800);
102 BEGIN
103 IF l_inv_debug_level IN(101, 102) THEN
104 IF LENGTH(p_message) > 3800 THEN
105 FOR i IN 1..( CEIL(LENGTH(p_message)/3800) ) LOOP
106 l_message := SUBSTR(p_message, ( 3800*(i-1) + 1 ), 3800 );
107 INVPUTLI.info( ( CASE
108 WHEN p_add_timestamp THEN to_char( systimestamp, G_LOG_TIMESTAMP_FORMAT ) || ': '
109 ELSE ''
110 END )
111 || l_message );
112 END LOOP;
113 ELSE
114 INVPUTLI.info( ( CASE
115 WHEN p_add_timestamp THEN to_char( systimestamp, G_LOG_TIMESTAMP_FORMAT ) || ': '
116 ELSE ''
117 END )
118 || p_message );
119 END IF;
120 END IF;
121 END Debug_Conc_Log;
122
123 ----------------------------------------------------------------------
124 -- Private Procedure
125 ----------------------------------------------------------------------
126 PROCEDURE write_intf_records (p_data_set_id in number
127 ,p_process_status in number
128 ,p_transaction_type in varchar2
129 ,p_data_level_id in number
130 ,p_count IN NUMBER
131 ) IS
132 -- PRAGMA AUTONOMOUS_TRANSACTION;
133
134 l_rec ego_itm_usr_attr_intrfc%ROWTYPE;
135 l_dummy_number NUMBER;
136 BEGIN
137 NULL;
138 /*
139 SELECT count(*)
140 INTO l_dummy_number
141 FROM ego_itm_usr_attr_intrfc
142 WHERE data_set_id = p_data_set_id
143 AND process_status = p_process_status
144 AND transaction_type = p_transaction_type
145 AND data_level_id = p_data_level_id;
146 code_debug(p_log_level => 0
147 ,p_module => 'Copy_data_to_Intf'
148 ,p_message => p_count||' - No records in intf table '||l_dummy_number
149 );
150 IF l_dummy_number > 0 THEN
151 code_debug(p_log_level => 0
152 ,p_module => 'Copy_data_to_Intf'
153 ,p_message => p_count||' Writing data in format '||
154 ' inventory_item_id, organization_id, data_level_id, revision_id, pk1_value, pk2_value, '||
155 ' attr_group_id, attr_group_int_name, attr_int_name, attr_value_str, attr_value_num, '||
156 ' row_identifier');
157 IF p_count < 100 THEN
158 FOR cr in (SELECT * FROM ego_itm_usr_attr_intrfc
159 WHERE data_set_id = p_data_set_id
160 AND process_status = p_process_status
161 AND transaction_type = p_transaction_type
162 AND data_level_id = p_data_level_id
163 ORDER BY inventory_item_id, organization_id, data_level_id,
164 revision_id, pk1_value, pk2_value, attr_group_id,
165 attr_int_name, row_identifier
166 ) LOOP
167 code_debug(p_log_level => 0
168 ,p_module => 'Copy_data_to_Intf'
169 ,p_message => p_count||' - '||cr.inventory_item_id||', '||
170 cr.organization_id||', '||cr.data_level_id||', '||cr.revision_id||', '||
171 cr.pk1_value||', '||cr.pk2_value||', '||cr.attr_group_id||', '||
172 cr.attr_group_int_name||', '||cr.attr_int_name||', '||
173 cr.attr_value_str||', '||cr.attr_value_num||', '||cr.row_identifier);
174 END LOOP;
175 END IF;
176 END IF;
177
178 BEGIN
179 SELECT count(*) abc
180 into l_dummy_number
181 FROM EGO_ITM_USR_ATTR_INTRFC
182 WHERE data_set_id = p_data_set_id
183 -- AND process_status = 1
184 -- AND transaction_type = p_transaction_type
185 -- AND data_level_id = p_data_level_id
186 AND attr_int_name = 'QualityManual'
187 HAVING count(*) > 2;
188
189 IF l_dummy_number > 2 THEN
190 commit;
191 sri_debug('Copy_data_to_Intf: Returning now');
192 RAISE TOO_MANY_ROWS;
193 END IF;
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 NULL;
197 END;
198 */
199
200 END;
201
202 ----------------------------------------------------------------------
203 -- Private Procedure
204 ----------------------------------------------------------------------
205 PROCEDURE write_records (p_data_set_id IN NUMBER
206 ,p_module IN VARCHAR2
207 ,p_message IN VARCHAR2) IS
208 l_rec ego_itm_usr_attr_intrfc%ROWTYPE;
209 CURSOR get_records IS
210 SELECT *
211 FROM ego_itm_usr_attr_intrfc
212 WHERE data_set_id = p_data_set_id;
213 begin
214 FOR cr IN get_records LOOP
215 code_debug (p_log_level => G_DEBUG_LEVEL_STATEMENT
216 ,p_module => p_module
217 ,p_message => 'Rec Info '||p_message||' : '||
218 ' inventory_item_id: '|| cr.inventory_item_id||
219 ' organization_id: '|| cr.organization_id||
220 ' row_identifier: ' ||cr.row_identifier||
221 ' process_status: ' ||cr.process_status||
222 ' attr_group_id: '||cr.attr_group_id||
223 ' attr_group_int_name: '||cr.attr_group_int_name||
224 ' attr_int_name: '||cr.attr_int_name||
225 ' item_catalog_group_id: '||cr.item_catalog_group_id||
226 ' parent_catalog_group_id: '||cr.prog_int_num1||
227 ' item_lc_id: '||cr.prog_int_num2||
228 ' item_phase_id: '||cr.prog_int_num3||
229 ' item_approval_status: '||cr.prog_int_char1||
230 ' item_rev_just_created: '||cr.prog_int_char2
231 );
232 END LOOP;
233 end write_records;
234 ----------------------------------------------------------------------
235 -- Private Procedure
236 ----------------------------------------------------------------------
237
238 PROCEDURE SetGlobals IS
239 BEGIN
240 G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
241 G_PROGAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
242 G_PROGAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
243 G_USER_NAME := FND_GLOBAL.USER_NAME;
244 G_USER_ID := FND_GLOBAL.USER_ID;
245 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
246 G_CURRENT_DEBUG_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
247 END;
248
249 ----------------------------------------------------------------------
250 -- Private Function
251 ----------------------------------------------------------------------
252
253 FUNCTION Build_Parent_Cat_Group_List (
254 p_catalog_group_id IN NUMBER
255 ,p_entity_index IN NUMBER
256 )
257 RETURN VARCHAR2
258 IS
259
260 l_parent_cat_group_list VARCHAR2(1000) := '';
261 l_token_table ERROR_HANDLER.Token_Tbl_Type;
262 -------------------------------------------------------------------------
263 -- For finding all parent catalog groups for the current catalog group --
264 -------------------------------------------------------------------------
265 CURSOR parent_catalog_group_cursor IS
266 SELECT ITEM_CATALOG_GROUP_ID, PARENT_CATALOG_GROUP_ID
267 FROM MTL_ITEM_CATALOG_GROUPS_B
268 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
269 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id;
270
271 BEGIN
272
273 -------------------------------------------------------------------
274 -- We build a list of all parent catalog groups, as long as the --
275 -- list is less than 151 characters long (the longest we can fit --
276 -- into the EGO_COL_NAME_VALUE_PAIR_OBJ is 150 chars); if the --
277 -- list is too long to fully copy, we can only hope that the --
278 -- portion we copied will contain all the information we need. --
279 -------------------------------------------------------------------
280 FOR cat_rec IN parent_catalog_group_cursor
281 LOOP
282 IF (cat_rec.PARENT_CATALOG_GROUP_ID IS NOT NULL) THEN
283 l_parent_cat_group_list := l_parent_cat_group_list ||
284 cat_rec.PARENT_CATALOG_GROUP_ID || ',';
285 END IF;
286 END LOOP;
287 ---------------------------------------------------------------------
288 -- Trim the trailing ',' from l_parent_cat_group_list if necessary --
289 ---------------------------------------------------------------------
290 IF (LENGTH(l_parent_cat_group_list) > 0) THEN
291 l_parent_cat_group_list := SUBSTR(l_parent_cat_group_list, 1, LENGTH(l_parent_cat_group_list) - LENGTH(','));
292 END IF;
293 RETURN l_parent_cat_group_list;
294 EXCEPTION
295 WHEN OTHERS THEN
296 l_token_table(1).TOKEN_NAME := 'CAT_GROUP_NAME';
297 SELECT CONCATENATED_SEGMENTS
298 INTO l_token_table(1).TOKEN_VALUE
299 FROM MTL_ITEM_CATALOG_GROUPS_KFV
300 WHERE ITEM_CATALOG_GROUP_ID = p_catalog_group_id;
301 ERROR_HANDLER.Add_Error_Message(
302 p_message_name => 'EGO_TOO_MANY_CAT_GROUPS'
303 ,p_application_id => 'EGO'
304 ,p_token_tbl => l_token_table
305 ,p_message_type => FND_API.G_RET_STS_ERROR
306 ,p_entity_id => G_ENTITY_ID
307 ,p_entity_index => p_entity_index
308 ,p_entity_code => G_ENTITY_CODE
309 ,p_table_name => G_TABLE_NAME
310 );
311
312 ---------------------------------------------------------------------
313 -- Trim the trailing ',' from l_parent_cat_group_list if necessary --
314 ---------------------------------------------------------------------
315 IF (LENGTH(l_parent_cat_group_list) > 0) THEN
316 l_parent_cat_group_list :=
317 SUBSTR(l_parent_cat_group_list, 1,
318 LENGTH(l_parent_cat_group_list) - LENGTH(','));
319 END IF;
320
321 RETURN l_parent_cat_group_list;
322
323 END Build_Parent_Cat_Group_List;
324
325 ----------------------------------------------------------------------
326
327 ----------------------------------
328 -- Public Function Declaration --
329 ----------------------------------
330
331 ----------------------------------------------------------------------
332
333 PROCEDURE Get_Item_Security_Predicate (
334 p_object_name IN VARCHAR2
335 ,p_party_id IN VARCHAR2
336 ,p_privilege_name IN VARCHAR2
337 ,p_table_alias IN VARCHAR2
338 ,x_security_predicate OUT NOCOPY VARCHAR2
339 ) IS
340
341 l_return_status VARCHAR2(30);
342 l_table_alias VARCHAR2(100);
343 l_security_predicate VARCHAR2(32767);
344 l_api_name VARCHAR2(30);
345 l_request_id_clause VARCHAR2(500);
346 l_process_flag VARCHAR2(10);
347 BEGIN
348 l_api_name := 'Get_Item_Security_Predicate';
349 SetGlobals();
350 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
351 ,p_module => l_api_name
352 ,p_message => 'Started with 5 params '||
353 ' p_object_name: '|| p_object_name ||
354 ' - p_party_id: '|| p_party_id ||
355 ' - p_privilege_name: '|| p_privilege_name ||
356 ' - p_table_alias: '|| p_table_alias
357 );
358
359 IF (LENGTH(p_table_alias) > 0) THEN
360 l_table_alias := p_table_alias || '.';
361 END IF;
362
363 EGO_DATA_SECURITY.get_security_predicate(
364 p_api_version => 1.0
365 ,p_function => p_privilege_name
366 ,p_object_name => p_object_name
367 ,p_user_name => p_party_id
368 ,p_statement_type => 'EXISTS'
369 ,p_pk1_alias => l_table_alias||'INVENTORY_ITEM_ID'
370 ,p_pk2_alias => l_table_alias||'ORGANIZATION_ID'
371 ,x_predicate => x_security_predicate
372 ,x_return_status => l_return_status
373 );
374
375 IF (x_security_predicate IS NULL) THEN
376 x_security_predicate := ' 1=1 '; --for internal users the security predicate is returned as null.
377 ELSE
378 x_security_predicate := x_security_predicate ||
379 ' AND NOT EXISTS
380 (SELECT 1
381 FROM MTL_SYSTEM_ITEMS_INTERFACE msii_e
382 WHERE msii_e.TRANSACTION_TYPE = ''CREATE''
383 AND msii_e.PROCESS_FLAG = 1
384 AND msii_e.SET_PROCESS_ID = UAI2.DATA_SET_ID
385 AND msii_e.INVENTORY_ITEM_ID = UAI2.INVENTORY_ITEM_ID
386 AND msii_e.ORGANIZATION_ID = UAI2.ORGANIZATION_ID)';
387 END IF;
388 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
389 ,p_module => l_api_name
390 ,p_message => 'Returning params '||
391 ' x_security_predicate: '|| x_security_predicate
392 );
393 END Get_Item_Security_Predicate;
394
395
396 ----------------
397 -- Procedures --
398 ----------------
399
400 ----------------------------------------------------------------------
401
402 PROCEDURE Process_Item_User_Attrs_Data
403 (
404 ERRBUF OUT NOCOPY VARCHAR2
405 ,RETCODE OUT NOCOPY VARCHAR2
406 ,p_data_set_id IN NUMBER
407 ,p_debug_level IN NUMBER DEFAULT 0
408 ,p_purge_successful_lines IN VARCHAR2 DEFAULT FND_API.G_FALSE
409 ,p_initialize_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
410 ,p_validate_only IN VARCHAR2 DEFAULT FND_API.G_FALSE
411 ,p_ignore_security_for_validate IN VARCHAR2 DEFAULT FND_API.G_FALSE
412 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE /* Added to fix Bug#7422423*/
413 ,p_is_id_validations_reqd IN VARCHAR2 DEFAULT FND_API.G_TRUE /* Fix for bug#9660659 */
414 ) IS
415 l_api_name VARCHAR2(30);
416 l_error_message_name VARCHAR2(30);
417 l_entity_index_counter NUMBER := 0;
418 l_catalog_category_names_table LOCAL_MEDIUM_VARCHAR_TABLE;
419 l_current_attr_group_obj EGO_ATTR_GROUP_METADATA_OBJ;
420 l_policy_check_name VARCHAR2(30);
421 l_add_all_to_cm VARCHAR2(1);
422 l_current_attr_group_name FND_DESCR_FLEX_CONTEXTS_TL.descriptive_flex_context_name%TYPE;
423 l_current_category_name MTL_ITEM_CATALOG_GROUPS_KFV.concatenated_segments%TYPE;
424 l_current_life_cycle VARCHAR2(240);
425 l_current_phase_name VARCHAR2(240);
426 l_prev_loop_org_id NUMBER;
427 l_prev_loop_inv_item_id NUMBER;
428 l_prev_loop_row_identifier NUMBER;
429 l_at_start_of_instance BOOLEAN;
430 l_can_edit_this_instance VARCHAR2(1);
431 l_token_table ERROR_HANDLER.Token_Tbl_Type;
432 l_could_edit_prev_instance VARCHAR2(1);
433 l_at_start_of_row BOOLEAN;
434 p_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
435 p_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
436 p_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
437 p_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
438 l_user_privileges_table EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE;
439 l_privilege_table_index NUMBER;
440 l_previous_privs_table EGO_VARCHAR_TBL_TYPE;
441 l_current_privs_table EGO_VARCHAR_TBL_TYPE;
442 l_failed_row_id_buffer VARCHAR2(32767);
443 l_failed_row_id_list VARCHAR2(32767);
444 l_failed_row_id_sql VARCHAR2(32767);
445 l_return_status VARCHAR2(1);
446 l_errorcode NUMBER;
447 l_msg_count NUMBER;
448 l_msg_data VARCHAR2(1000);
449 l_dynamic_sql VARCHAR2(32767);
450 l_policy_check_sql VARCHAR2(32767);
451 l_debug_rowcount NUMBER := 0;
452 l_rec_count NUMBER;
453 l_err_reporting_transaction_id NUMBER;
454 l_related_class_codes_query VARCHAR2(1000);
455 l_user_attrs_return_status VARCHAR2(1);
456 l_item_return_status VARCHAR2(1);
457 l_attr_group_type VARCHAR2(30);
458 l_entity_sql VARCHAR2(5000);
459 G_UNHANDLED_EXCEPTION EXCEPTION;
460 l_target_entity_sql VARCHAR2(5000);
461 l_excluded_ag_list VARCHAR2(1000);
462 l_gtinval_ret_code VARCHAR2(1);
463 l_user_id NUMBER := FND_GLOBAL.USER_ID;
464 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
465 l_privilege_predicate_api_name VARCHAR2(1000);
466 l_item_sup_dl_id NUMBER;
467 l_item_sup_site_dl_id NUMBER;
468 l_item_sup_site_org_dl_id NUMBER;
469 l_item_data_level_id_str VARCHAR2(10000); --Bug 9325678
470 l_erase_revision_sql VARCHAR2(10000); --Bug 9325678
471
472 /* Fix for bug#9660659 - Start */
473 l_item_mgmt_count NUMBER := 0;
474 l_item_gtin_count NUMBER := 0;
475 l_item_gtin_multi_count NUMBER := 0;
476 /* Fix for bug#9660659 - End */
477
478 -- Bug 10263673 : Start
479 l_enabled_for_data_pool VARCHAR2(1);
480 l_copy_option_exists VARCHAR2(1);
481 l_retcode VARCHAR2(1);
482 l_errbuf VARCHAR2(4000);
483 l_return BOOLEAN;
484 -- Bug 10263673 : End
485
486 -------------------------------------------------------------------------
487 -- PIM for Telco item uda validations --
488 -------------------------------------------------------------------------
489
490 /*profile_value varchar2(1) := fnd_profile.value('EGO_ENABLE_P4T');
491 l_com_attr_group_type VARCHAR2(40);
492 l_com_attr_group_name VARCHAR2(30) := NULL;
493 l_com_attr_group_id NUMBER;
494 l_com_attr_int_name VARCHAR2(30);
495 l_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
496 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
497 --l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
498 l_telco_return_status VARCHAR2(1);
499 l_error_messages EGO_COL_NAME_VALUE_PAIR_ARRAY := EGO_COL_NAME_VALUE_PAIR_ARRAY();
500 l_error_col_name_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY :=EGO_COL_NAME_VALUE_PAIR_ARRAY();
501
502 l_old_com_attr_group_type VARCHAR2(30);
503 l_old_com_attr_group_name VARCHAR2(30);
504 l_old_com_attr_group_id NUMBER;
505 l_old_inventory_item_id NUMBER;
506 l_old_revision_id NUMBER;
507 l_old_organization_id NUMBER;
508 l_old_catalog_category_id NUMBER;
509
510 l_curr_data_element EGO_USER_ATTR_DATA_OBJ;
511 l_curr_pk_col_name_val_element EGO_COL_NAME_VALUE_PAIR_OBJ;
512 l_curr_class_cd_val_element EGO_COL_NAME_VALUE_PAIR_OBJ;
513 l_error_element EGO_COL_NAME_VALUE_PAIR_OBJ;
514
515 l_inventory_item_id NUMBER;
516 l_revision_id NUMBER;
517 l_organization_id NUMBER;
518 l_catalog_category_id NUMBER;
519
520 l_value_str VARCHAR2(1000);
521 l_value_num NUMBER;
522 l_value_date DATE;
523 l_value VARCHAR2(1000);
524 l_next_attr_group BOOLEAN := FALSE;
525 l_validate_data BOOLEAN := FALSE;
526 l_mark_error_record BOOLEAN := FALSE;
527 l_row_identifier NUMBER;
528
529 l_error_attr_name VARCHAR2(1000);
530 l_error_attr_group_name VARCHAR2(30);
531 l_name VARCHAR2(30);
532 l_err_value VARCHAR2(150);
533 l_error_message VARCHAR2(30);
534 l_error_row_identifier NUMBER;
535 l_dynamic_sqlt VARCHAR2(32767);
536
537 -- PIM for Telco item uda validations ends
538 */
539 -------------------------------------------------------------------------
540 -- For finding Inventory Item ID using Organization ID and Item Number --
541 -------------------------------------------------------------------------
542 CURSOR item_num_to_id_cursor (cp_data_set_id IN NUMBER)
543 IS
544 SELECT DISTINCT
545 ORGANIZATION_ID
546 ,ITEM_NUMBER
547 FROM EGO_ITM_USR_ATTR_INTRFC
548 WHERE DATA_SET_ID = cp_data_set_id
549 AND PROCESS_STATUS = G_PS_IN_PROCESS
550 AND ITEM_NUMBER IS NOT NULL
551 AND INVENTORY_ITEM_ID IS NULL;
552
553 ---------------------------------------------------------------
554 -- For reporting errors for all of the four conversion steps --
555 ---------------------------------------------------------------
556
557 CURSOR error_case_cursor (cp_data_set_id IN NUMBER)
558 IS
559 SELECT DISTINCT
560 PROCESS_STATUS
561 ,ORGANIZATION_CODE
562 ,ORGANIZATION_ID
563 ,ITEM_NUMBER
564 ,INVENTORY_ITEM_ID
565 ,ATTR_GROUP_ID
566 ,ATTR_GROUP_INT_NAME
567 ,REVISION
568 ,REVISION_ID
569 ,ITEM_CATALOG_GROUP_ID
570 ,TRANSACTION_ID
571 ,ATTR_GROUP_TYPE
572 ,PROG_INT_NUM1
573 ,PROG_INT_NUM2
574 ,PROG_INT_NUM3
575 ,ATTR_VALUE_STR
576 ,ATTR_VALUE_NUM
577 ,ATTR_VALUE_DATE
578 ,ATTR_DISP_VALUE
579 ,PK1_VALUE
580 ,PK2_VALUE
581 ,DATA_LEVEL_NAME
582 ,USER_DATA_LEVEL_NAME
583 FROM EGO_ITM_USR_ATTR_INTRFC
584 WHERE DATA_SET_ID = cp_data_set_id
585 AND PROCESS_STATUS IN (G_PS_BAD_ORG_ID,
586 G_PS_BAD_ORG_CODE,
587 G_PS_BAD_ITEM_ID,
588 G_PS_BAD_ITEM_NUMBER,
589 G_PS_BAD_REVISION_ID,
590 G_PS_BAD_REVISION_CODE,
591 G_PS_BAD_CATALOG_GROUP_ID,
592 G_PS_BAD_ATTR_GROUP_ID,
593 G_PS_BAD_ATTR_GROUP_NAME,
594 G_PS_CHG_POLICY_NOT_ALLOWED,
595 G_PS_DATA_LEVEL_INCORRECT,
596 G_PS_BAD_DATA_LEVEL,
597 G_PS_BAD_SUPPLIER,
598 G_PS_BAD_SUPPLIER_SITE,
599 G_PS_BAD_SUPPLIER_SITE_ORG,
600 G_PS_BAD_STYLE_VAR_VALUE_SET,
601 G_PS_VAR_VSET_CHG_NOT_ALLOWED,
602 G_PS_SKU_VAR_VALUE_NOT_UPD,
603 G_PS_INH_ATTR_FOR_SKU_NOT_UPD
604 );
605
606 -------------------------------------------------------------------
607 -- For processing all rows that passed the four conversion steps --
608 -------------------------------------------------------------------
609 CURSOR data_set_cursor (cp_data_set_id IN NUMBER)
610 IS
611 SELECT TRANSACTION_ID
612 ,PROCESS_STATUS
613 ,ORGANIZATION_CODE
614 ,ITEM_NUMBER
615 ,ATTR_GROUP_INT_NAME
616 ,ROW_IDENTIFIER
617 ,ATTR_INT_NAME
618 ,ATTR_VALUE_STR
619 ,ATTR_VALUE_NUM
620 ,ATTR_VALUE_DATE
621 ,ATTR_DISP_VALUE
622 ,TRANSACTION_TYPE
623 ,ORGANIZATION_ID
624 ,INVENTORY_ITEM_ID
625 ,ITEM_CATALOG_GROUP_ID
626 ,REVISION_ID
627 ,ATTR_GROUP_ID
628 FROM EGO_ITM_USR_ATTR_INTRFC
629 WHERE DATA_SET_ID = cp_data_set_id
630 AND PROCESS_STATUS = G_PS_IN_PROCESS
631 ORDER BY ORGANIZATION_ID,
632 INVENTORY_ITEM_ID,
633 (DECODE (UPPER(TRANSACTION_TYPE),
634 'DELETE', 1,
635 'UPDATE', 2,
636 'SYNC', 3,
637 'CREATE', 4, 5)),
638 ROW_IDENTIFIER,
639 ATTR_GROUP_INT_NAME;
640
641 --------------------------------------------------------------------------
642 -- For getting this distinct catalog groups passing all the validations --
643 --------------------------------------------------------------------------
644 CURSOR distinct_catalaog_groups (cp_data_set_id IN NUMBER)
645 IS
646 SELECT ITEM_CATALOG_GROUP_ID
647 FROM EGO_ITM_USR_ATTR_INTRFC
648 WHERE DATA_SET_ID = cp_data_set_id
649 AND PROCESS_STATUS = G_PS_IN_PROCESS
650 GROUP BY ITEM_CATALOG_GROUP_ID;
651
652 --------------------------------------------------------
653 -- For validations related to pk1_value and pk2_value --
654 --------------------------------------------------------
655 CURSOR c_data_levels IS
656 SELECT DATA_LEVEL_ID, DATA_LEVEL_NAME
657 FROM EGO_DATA_LEVEL_B
658 WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
659 AND APPLICATION_ID = 431
660 AND DATA_LEVEL_NAME IN ('ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG');
661
662 -------------------------------------------------------------
663 -- For sending default privilege names for each data level --
664 -------------------------------------------------------------
665 CURSOR c_data_levels_for_sec IS
666 SELECT DATA_LEVEL_ID, DATA_LEVEL_NAME, ATTR_GROUP_TYPE
667 FROM EGO_DATA_LEVEL_B
668 WHERE APPLICATION_ID = 431
669 AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';
670
671 l_default_dl_view_priv_list EGO_COL_NAME_VALUE_PAIR_ARRAY;
672 l_default_dl_edit_priv_list EGO_COL_NAME_VALUE_PAIR_ARRAY;
673 l_default_dl_view_priv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
674 l_default_dl_edit_priv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
675
676 -------------------------------------------------------------------------
677 -- PIM for Telco item uda validations --
678 -------------------------------------------------------------------------
679
680 /*CURSOR c_row_identfier (cp_data_set_id IN NUMBER)
681 IS
682 SELECT distinct ROW_IDENTIFIER
683 FROM EGO_ITM_USR_ATTR_INTRFC
684 WHERE DATA_SET_ID = cp_data_set_id
685 AND PROCESS_STATUS = G_PS_IN_PROCESS;
686
687 CURSOR c_com_attr_groups (cp_data_set_id IN NUMBER, cp_row_identifier NUMBER)
688 IS
689 SELECT PROCESS_STATUS
690 ,TRANSACTION_ID
691 ,ATTR_GROUP_INT_NAME
692 ,ROW_IDENTIFIER
693 ,ATTR_INT_NAME
694 ,ATTR_VALUE_STR
695 ,ATTR_VALUE_NUM
696 ,ATTR_VALUE_DATE
697 ,ATTR_DISP_VALUE
698 ,TRANSACTION_TYPE
699 ,ORGANIZATION_ID
700 ,INVENTORY_ITEM_ID
701 ,ITEM_CATALOG_GROUP_ID
702 ,REVISION_ID
703 ,ATTR_GROUP_ID
704 ,ATTR_GROUP_TYPE
705 FROM EGO_ITM_USR_ATTR_INTRFC
706 WHERE DATA_SET_ID = cp_data_set_id
707 AND PROCESS_STATUS = G_PS_IN_PROCESS
708 AND ROW_IDENTIFIER = cp_row_identifier
709 ORDER BY ORGANIZATION_ID,
710 INVENTORY_ITEM_ID,
711 (DECODE (UPPER(TRANSACTION_TYPE),
712 'DELETE', 1,
713 'UPDATE', 2,
714 'SYNC', 3,
715 'CREATE', 4, 5)),
716 ROW_IDENTIFIER,
717 ATTR_GROUP_INT_NAME;
718 */
719 -- PIM for Telco item uda validations ends here
720
721 -- Fix for bug#9336604
722 l_schema VARCHAR2(30);
723 l_status VARCHAR2(1);
724 l_industry VARCHAR2(1);
725
726 BEGIN
727 l_api_name := 'Process_Item_User_Attrs_Data';
728 SetGlobals();
729 RETCODE := L_CONC_RET_STS_SUCCESS;
730 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
731 ,p_module => l_api_name
732 ,p_message => 'Started with 7 params '||
733 ' p_data_set_id: '|| p_data_set_id ||
734 ' - p_purge_successful_lines: '|| p_purge_successful_lines ||
735 ' - p_initialize_error_handler: '|| p_initialize_error_handler ||
736 ' - p_validate_only: ' || p_validate_only
737 );
738 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message=> 'As given');
739 --If there are no rows to process for this data_set_id, return success.
740 SELECT
741 COUNT(DATA_SET_ID)
742 INTO l_rec_count
743 FROM EGO_ITM_USR_ATTR_INTRFC intf
744 WHERE DATA_SET_ID = p_data_set_id
745 AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS IN (G_PS_TO_BE_PROCESSED, G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS) );
746
747 IF (l_rec_count = 0) THEN
748 l_return := TRUE; -- Bug 10263673, Set the falg to true, to identify that user didn't provide any values for UDAs.
749 -- EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Returning because no data exists in interface table to process', 0);
750 -- RETURN; -- Bug 10263673, Do not return now only.
751 ELSE
752 l_return := FALSE; -- Bug 10263673, Set the falg to false, to identify that the user has given some UDA values.
753 END IF;
754
755 l_attr_group_type := 'EGO_ITEMMGMT_GROUP';
756 -----------------------------------------------
757 -- Set this global variable once per session --
758 -----------------------------------------------
759 IF (G_HZ_PARTY_ID IS NULL) THEN
760 IF (G_USER_NAME IS NOT NULL) THEN
761 SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
762 INTO G_HZ_PARTY_ID
763 FROM EGO_PEOPLE_V
764 WHERE USER_NAME = G_USER_NAME
765 AND ROWNUM = 1;
766 -- added rownum = 1 above bug 13830878
767 ELSE
768 RAISE G_NO_USER_NAME_TO_VALIDATE;
769 END IF;
770 END IF;
771
772 --======================--
773 -- ERROR_HANDLER SET-UP --
774 --======================--
775
776 IF (FND_API.To_Boolean(p_initialize_error_handler)) THEN
777
778 ERROR_HANDLER.Initialize();
779 ERROR_HANDLER.Set_Bo_Identifier(EGO_USER_ATTRS_DATA_PVT.G_BO_IDENTIFIER);
780
781 ---------------------------------------------------
782 -- If we're debugging, we have to set up a Debug --
783 -- session (unless our caller already did so) --
784 ---------------------------------------------------
785
786 IF (p_debug_level > 0 AND ERROR_HANDLER.Get_Debug() = 'N') THEN
787 EGO_USER_ATTRS_DATA_PVT.Set_Up_Debug_Session(G_ENTITY_ID, G_ENTITY_CODE, p_debug_level);
788 END IF;
789 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Item/Item Revision Concurrent Program for data set ID: '||p_data_set_id, 0);
790 END IF;
791 --------------------------------------------------------
792 -- Related classification query is required for
793 -- User Attributes Bulk Load API
794 --------------------------------------------------------
795
796 l_related_class_codes_query :=
797 ' (SELECT ITEM_CATALOG_GROUP_ID '||
798 ' FROM MTL_ITEM_CATALOG_GROUPS_B ' ||
799 ' CONNECT BY PRIOR ' ||
800 ' PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID ' ||
801 ' START WITH ' ||
802 ' ITEM_CATALOG_GROUP_ID = UAI2.ITEM_CATALOG_GROUP_ID)' ||
803 ' UNION ALL ' ||
804 ' (SELECT UAI2.ITEM_CATALOG_GROUP_ID FROM DUAL)' ;
805
806 --===================================--
807 -- GETTING THE INTERFACE TABLE READY --
808 --===================================--
809
810 IF (p_debug_level > 0) THEN
811 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Preparing interface table', 0);
812 END IF;
813
814 -------------------------------------------------------------------
815 -- Gather statistics: since the data in interface tables changes --
816 -- frequently, our indexes are not very useful unless we gather --
817 -- statistics for the *current* data in the table. (APPS has a --
818 -- standard to gather statistics at the beginning of interface --
819 -- import programs.) --
820 -------------------------------------------------------------------
821 /*6602290 : Stats gather through profile : Stats are gathered in EGOVIMPB
822 SELECT COUNT(data_set_id)
823 INTO l_rec_count
824 FROM EGO_ITM_USR_ATTR_INTRFC
825 WHERE data_set_id = p_data_set_id;
826 IF l_rec_count > 50 THEN
827 FND_STATS.Gather_Table_Stats(
828 ownname => 'EGO'
829 ,tabname => 'EGO_ITM_USR_ATTR_INTRFC'
830 ,cascade => TRUE
831 );
832 END IF;
833 */
834 --
835 -- get the item id and store in global
836 --
837 BEGIN
838 SELECT OBJECT_ID
839 INTO G_ITEM_OBJECT_ID
840 FROM FND_OBJECTS
841 WHERE OBJ_NAME = G_ITEM_NAME;
842 EXCEPTION
843 WHEN OTHERS THEN
844 IF (p_debug_level > 0) THEN
845 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Cannot find object EGO_ITEM in fnd_objects ', 0);
846 END IF;
847 G_ITEM_OBJECT_ID := NULL;
848 END;
849
850 IF (l_return = FALSE) THEN -- Bug 10263673, If there exists user entered records, then validate them.
851 ---------------------------------------------------------------------
852 -- Mark all rows we'll be processing, and null out user input for --
853 -- the ITEM_CATALOG_GROUP_ID column (so we can validate it); also --
854 -- update Concurrent Request information for better user tracking --
855 -- and update the "WHO" columns on the assumption that the current --
856 -- user is also the person who loaded this data set into the table --
857 ---------------------------------------------------------------------
858 UPDATE EGO_ITM_USR_ATTR_INTRFC
859 SET PROCESS_STATUS = G_PS_IN_PROCESS
860 ,ITEM_CATALOG_GROUP_ID = NULL
861 ,PROG_INT_NUM1 = NULL
862 ,PROG_INT_NUM2 = NULL
863 ,PROG_INT_NUM3 = NULL
864 ,PROG_INT_CHAR1 = 'N'
865 ,PROG_INT_CHAR2 = 'N'
866 ,REQUEST_ID = G_REQUEST_ID
867 ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
868 ,PROGRAM_ID = G_PROGAM_ID
869 ,PROGRAM_UPDATE_DATE = SYSDATE
870 ,CREATED_BY = NVL(CREATED_BY, G_USER_ID)
871 ,CREATION_DATE = NVL(CREATION_DATE, SYSDATE)
872 ,LAST_UPDATED_BY = G_USER_ID
873 ,LAST_UPDATE_DATE = SYSDATE
874 ,LAST_UPDATE_LOGIN = G_LOGIN_ID
875 ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
876 WHERE DATA_SET_ID = p_data_set_id
877 AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
878
879 --Fix for Bug:13716796
880 --Provide default value for PROG_INT_CHAR2 if it's value is NULL
881 UPDATE EGO_ITM_USR_ATTR_INTRFC
882 SET PROG_INT_CHAR2 = 'N'
883 WHERE DATA_SET_ID = p_data_set_id
884 AND PROG_INT_CHAR2 IS NULL
885 AND PROCESS_STATUS =G_PS_IN_PROCESS;
886
887 -- Fix for bug#9336604
888 -- Added gather stats based on profile
889 IF SQL%ROWCOUNT > 0 THEN
890 IF (FND_INSTALLATION.GET_APP_INFO('EGO', l_status, l_industry, l_schema)) THEN
891
892 IF (nvl(fnd_profile.value('EGO_ENABLE_GATHER_STATS'),'N') >= 'Y') THEN
893
894 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
895 TABNAME => 'EGO_ITM_USR_ATTR_INTRFC',
896 CASCADE => True);
897 END IF;
898
899 END IF;
900
901 END IF;
902
903 --==================================--
904 -- THE FIVE PRELIMINARY CONVERSIONS --
905 --==================================--
906
907 IF (p_debug_level > 0) THEN
908 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting conversions', 0);
909 END IF;
910
911 IF(p_is_id_validations_reqd = FND_API.G_TRUE) THEN /* Fix for bug#9660659 */
912 ----------------------------------------------
913 -- 1). Validate passed-in Organization IDs --
914 -- and convert passed-in Organization Codes --
915 ----------------------------------------------
916 IF (p_debug_level > 0) THEN
917 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Org validation/conversion', 0);
918 END IF;
919
920
921 -----------------------------------------------------------------
922 -- Next, try to turn Master Organization Codes into Master Org --
923 -- IDs for those rows where the user didn't pass in an Org ID; --
924 -- as above, if the AG is associated at the Rev level then the --
925 -- Orgs don't have to be Master Orgs. --
926 -----------------------------------------------------------------
927 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
928 SET UAI.ORGANIZATION_ID =
929 (SELECT MP.ORGANIZATION_ID
930 FROM MTL_PARAMETERS MP
931 WHERE MP.ORGANIZATION_CODE = UAI.ORGANIZATION_CODE)
932 WHERE UAI.DATA_SET_ID = p_data_set_id
933 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
934 AND UAI.ORGANIZATION_CODE IS NOT NULL
935 AND UAI.ORGANIZATION_ID IS NULL;
936 /* AND EXISTS (SELECT MP2.ORGANIZATION_ID
937 FROM MTL_PARAMETERS MP2
938 WHERE MP2.ORGANIZATION_CODE = UAI.ORGANIZATION_CODE
939 AND ((UAI.REVISION_ID IS NOT NULL
940 OR
941 UAI.REVISION IS NOT NULL)
942 OR
943 MP2.ORGANIZATION_ID = MP2.MASTER_ORGANIZATION_ID));*/
944
945 ------------------------------------------------------------------------------
946 -- Finally, mark as errors all rows that are in the same logical Attribute --
947 -- Group row as any row whose Org Code doesn't correspond to a valid Master --
948 -- Org ID (marking errors as we go avoids further processing of bad rows) --
949 ------------------------------------------------------------------------------
950 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
951 SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_CODE
952 WHERE UAI.DATA_SET_ID = p_data_set_id
953 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
954 AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
955 UAI2.ROW_IDENTIFIER
956 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
957 WHERE UAI2.DATA_SET_ID = p_data_set_id
958 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
959 AND UAI2.ORGANIZATION_ID IS NULL);
960
961 END IF; /* end of IF(p_is_id_validations_reqd = FND_API.G_TRUE) - Bug 9696621
962 ending the IF, so that the item_number/item_id are validated always */
963
964 --------------------------------------------
965 -- 2). Validate passed-in Inventory Item --
966 -- IDs and convert passed-in Item Numbers --
967 --------------------------------------------
968 IF (p_debug_level > 0) THEN
969 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Item Number validation/conversion', 0);
970 END IF;
971
972 ----------------------------------------------------------------------------
973 -- First, verify that all passed-in Inventory Item IDs belong to existing --
974 -- Items; if any row has an invalid Item ID, error it out along with all --
975 -- other rows in its logical Attribute Group row --
976 ----------------------------------------------------------------------------
977 IF p_validate_only = FND_API.G_FALSE THEN
978 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
979 SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_ID
980 WHERE UAI.DATA_SET_ID = p_data_set_id
981 AND UAI.PROCESS_STATUS IN (G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS)
982 AND UAI.INVENTORY_ITEM_ID IS NOT NULL
983 AND NOT EXISTS (SELECT 'X'
984 FROM MTL_SYSTEM_ITEMS_B MSIB
985 WHERE MSIB.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
986 AND MSIB.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
987 ELSE
988 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
989 SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_ID
990 WHERE UAI.DATA_SET_ID = p_data_set_id
991 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
992 AND UAI.INVENTORY_ITEM_ID IS NOT NULL
993 AND NOT EXISTS (SELECT 'X'
994 FROM MTL_SYSTEM_ITEMS_B MSIB
995 WHERE MSIB.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
996 AND MSIB.ORGANIZATION_ID = UAI.ORGANIZATION_ID
997 UNION ALL
998 SELECT 'X'
999 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1000 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1001 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1002 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1003 AND MSII.PROCESS_FLAG = 1
1004 AND MSII.TRANSACTION_TYPE = 'CREATE');
1005 END IF;
1006
1007 -------------------------------------------------------
1008 -- Next, convert Item Number into Item ID for those --
1009 -- rows where the user only passed in an Item Number --
1010 -- (Note that we only convert any Item Number/Org ID --
1011 -- combination once and then set the Item ID we find --
1012 -- to all rows with the same Item Number and Org ID) --
1013 -------------------------------------------------------
1014 IF p_validate_only = FND_API.G_FALSE THEN
1015 UPDATE EGO_ITM_USR_ATTR_INTRFC intrfc
1016 SET INVENTORY_ITEM_ID =
1017 (SELECT INVENTORY_ITEM_ID
1018 FROM MTL_SYSTEM_ITEMS_B_KFV
1019 WHERE CONCATENATED_SEGMENTS = intrfc.ITEM_NUMBER
1020 AND ORGANIZATION_ID = intrfc.ORGANIZATION_ID)
1021 WHERE DATA_SET_ID = p_data_set_id
1022 AND PROCESS_STATUS = G_PS_IN_PROCESS
1023 AND ITEM_NUMBER IS NOT NULL
1024 AND INVENTORY_ITEM_ID IS NULL;
1025 ELSE
1026 -- Bug 13434831 : Start (Performance Change)
1027 /*
1028 UPDATE EGO_ITM_USR_ATTR_INTRFC intrfc
1029 SET INVENTORY_ITEM_ID =
1030 NVL( (SELECT INVENTORY_ITEM_ID
1031 FROM MTL_SYSTEM_ITEMS_B_KFV
1032 WHERE CONCATENATED_SEGMENTS = intrfc.ITEM_NUMBER
1033 AND ORGANIZATION_ID = intrfc.ORGANIZATION_ID
1034 ),
1035 (SELECT INVENTORY_ITEM_ID
1036 FROM MTL_SYSTEM_ITEMS_INTERFACE msii
1037 WHERE msii.SET_PROCESS_ID = intrfc.DATA_SET_ID
1038 AND msii.PROCESS_FLAG = 1
1039 AND (msii.ITEM_NUMBER = intrfc.ITEM_NUMBER OR msii.SOURCE_SYSTEM_REFERENCE = intrfc.SOURCE_SYSTEM_REFERENCE)
1040 AND msii.ORGANIZATION_ID = intrfc.ORGANIZATION_ID
1041 AND ROWNUM = 1
1042 )
1043 )
1044 WHERE DATA_SET_ID = p_data_set_id
1045 AND PROCESS_STATUS = G_PS_IN_PROCESS
1046 AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL)
1047 AND INVENTORY_ITEM_ID IS NULL;
1048 */
1049
1050 UPDATE EGO_ITM_USR_ATTR_INTRFC INTRFC
1051 SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
1052 FROM MTL_SYSTEM_ITEMS_KFV MSIK
1053 WHERE MSIK.CONCATENATED_SEGMENTS = INTRFC.ITEM_NUMBER
1054 AND MSIK.ORGANIZATION_ID = INTRFC.ORGANIZATION_ID
1055 ),
1056 NVL((SELECT INVENTORY_ITEM_ID
1057 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1058 WHERE ( MSII.ITEM_NUMBER = INTRFC.ITEM_NUMBER )
1059 AND MSII.ORGANIZATION_ID = INTRFC.ORGANIZATION_ID
1060 AND MSII.SET_PROCESS_ID = INTRFC.DATA_SET_ID
1061 AND MSII.PROCESS_FLAG = 1
1062 AND ROWNUM = 1),
1063 (SELECT INVENTORY_ITEM_ID
1064 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1065 WHERE ( MSII.SOURCE_SYSTEM_REFERENCE = INTRFC.SOURCE_SYSTEM_REFERENCE )
1066 AND MSII.ORGANIZATION_ID = INTRFC.ORGANIZATION_ID
1067 AND MSII.SET_PROCESS_ID = INTRFC.DATA_SET_ID
1068 AND MSII.PROCESS_FLAG = 1
1069 AND ROWNUM = 1)))
1070 WHERE DATA_SET_ID = P_DATA_SET_ID
1071 AND PROCESS_STATUS = G_PS_IN_PROCESS
1072 AND INVENTORY_ITEM_ID IS NULL
1073 AND ( ITEM_NUMBER IS NOT NULL
1074 OR SOURCE_SYSTEM_REFERENCE IS NOT NULL );
1075 -- Bug 13434831 : End (Performance Change)
1076 END IF;
1077
1078 ----------------------------------------------------------------
1079 -- As with the Organization step, we mark as errors all rows --
1080 -- that share the same logical Attribute Group row with any --
1081 -- rows where we didn't end up with a valid Inventory Item ID --
1082 ----------------------------------------------------------------
1083 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1084 SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_NUMBER
1085 WHERE UAI.DATA_SET_ID = p_data_set_id
1086 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1087 AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1088 UAI2.ROW_IDENTIFIER
1089 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1090 WHERE UAI2.DATA_SET_ID = p_data_set_id
1091 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1092 AND UAI2.INVENTORY_ITEM_ID IS NULL);
1093
1094 IF(p_is_id_validations_reqd = FND_API.G_TRUE) THEN /* Fix for bug#9660659 */
1095 -----------------------------------------
1096 -- 3). Validate passed-in Revision IDs --
1097 -- and convert passed-in Revisions --
1098 -----------------------------------------
1099 IF (p_debug_level > 0) THEN
1100 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Revision conversion', 0);
1101 END IF;
1102
1103 ---------------------------------------------------------------
1104 -- First, verify that all passed-in Revision IDs are valid; --
1105 -- if any row has an invalid Revision ID, error it out along --
1106 -- with all other rows in its logical Attribute Group row --
1107 ---------------------------------------------------------------
1108 IF p_validate_only = FND_API.G_FALSE THEN
1109 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1110 SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_ID
1111 WHERE UAI.DATA_SET_ID = p_data_set_id
1112 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1113 AND UAI.REVISION_ID IS NOT NULL
1114 AND NOT EXISTS (SELECT 'X'
1115 FROM MTL_ITEM_REVISIONS MIR
1116 WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1117 AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1118 AND MIR.REVISION_ID = UAI.REVISION_ID);
1119 ELSE
1120 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1121 SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_ID
1122 WHERE UAI.DATA_SET_ID = p_data_set_id
1123 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1124 AND UAI.REVISION_ID IS NOT NULL
1125 AND NOT EXISTS (SELECT 'X'
1126 FROM MTL_ITEM_REVISIONS MIR
1127 WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1128 AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1129 AND MIR.REVISION_ID = UAI.REVISION_ID
1130 UNION ALL
1131 SELECT 'X'
1132 FROM MTL_ITEM_REVISIONS_INTERFACE miri
1133 WHERE miri.SET_PROCESS_ID = UAI.DATA_SET_ID
1134 AND miri.PROCESS_FLAG = 1
1135 AND miri.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1136 AND miri.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1137 AND miri.REVISION_ID = UAI.REVISION_ID
1138 );
1139 END IF;
1140
1141 ----------------------------------------------------------------
1142 -- Next, convert Revision to Revision ID for those rows where --
1143 -- the user passed in the Revision (note that by "Revision", --
1144 -- we mean the Revision *Code* and not the Revision Label) --
1145 ----------------------------------------------------------------
1146 IF p_validate_only = FND_API.G_FALSE THEN
1147 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1148 SET UAI.REVISION_ID = (SELECT MIR.REVISION_ID
1149 FROM MTL_ITEM_REVISIONS MIR
1150 WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1151 AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1152 AND MIR.REVISION = UAI.REVISION)
1153 WHERE UAI.DATA_SET_ID = p_data_set_id
1154 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1155 AND UAI.REVISION IS NOT NULL
1156 AND UAI.REVISION_ID IS NULL
1157 AND EXISTS (SELECT MIR2.REVISION_ID
1158 FROM MTL_ITEM_REVISIONS MIR2
1159 WHERE MIR2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1160 AND MIR2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1161 AND MIR2.REVISION = UAI.REVISION);
1162 ELSE
1163 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1164 SET UAI.REVISION_ID = NVL( (SELECT MIR.REVISION_ID
1165 FROM MTL_ITEM_REVISIONS MIR
1166 WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1167 AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1168 AND MIR.REVISION = UAI.REVISION
1169 ),
1170 (SELECT miri.REVISION_ID
1171 FROM MTL_ITEM_REVISIONS_INTERFACE miri
1172 WHERE miri.SET_PROCESS_ID = UAI.DATA_SET_ID
1173 AND miri.PROCESS_FLAG = 1
1174 AND miri.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1175 AND miri.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1176 AND miri.REVISION = UAI.REVISION
1177 AND ROWNUM = 1
1178 )
1179 )
1180 WHERE UAI.DATA_SET_ID = p_data_set_id
1181 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1182 AND UAI.REVISION IS NOT NULL
1183 AND UAI.REVISION_ID IS NULL
1184 AND EXISTS (SELECT 'X'
1185 FROM MTL_ITEM_REVISIONS MIR2
1186 WHERE MIR2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1187 AND MIR2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1188 AND MIR2.REVISION = UAI.REVISION
1189 UNION ALL
1190 SELECT 'X'
1191 FROM MTL_ITEM_REVISIONS_INTERFACE miri
1192 WHERE miri.SET_PROCESS_ID = UAI.DATA_SET_ID
1193 AND miri.PROCESS_FLAG = 1
1194 AND miri.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1195 AND miri.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1196 AND miri.REVISION = UAI.REVISION
1197 );
1198 END IF;
1199
1200
1201 -------------------------------------------------------------------------
1202 -- Mark as errors all rows that share the same logical Attribute Group --
1203 -- row with any rows where we started with a Revision and didn't end --
1204 -- up with a valid Revision ID (because many rows may not have either --
1205 -- a Revision or a Revision ID, and that is not an error condition) --
1206 -------------------------------------------------------------------------
1207 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1208 SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_CODE
1209 WHERE UAI.DATA_SET_ID = p_data_set_id
1210 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1211 AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1212 UAI2.ROW_IDENTIFIER
1213 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1214 WHERE UAI2.DATA_SET_ID = p_data_set_id
1215 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1216 AND UAI2.REVISION IS NOT NULL
1217 AND UAI2.REVISION_ID IS NULL);
1218
1219 END IF; -- end of IF(p_is_id_validations_reqd = FND_API.G_TRUE) /* Fix for bug#9660659 */
1220
1221 ---------------------------------------------------------
1222 -- 4). Find the Item Catalog Group ID for each Item --
1223 ---------------------------------------------------------
1224 IF (p_debug_level > 0) THEN
1225 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Catalog Group ID conversion', 0);
1226 END IF;
1227
1228 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After init');
1229 -- Fix for bug#9660659
1230 IF p_validate_only = FND_API.G_FALSE THEN
1231 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1232 SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
1233 UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1234 = (SELECT NVL(MSI.ITEM_CATALOG_GROUP_ID,-1), NVL(MSI.APPROVAL_STATUS,'A'),
1235 MSI.LIFECYCLE_ID, MSI.CURRENT_PHASE_ID
1236 FROM MTL_SYSTEM_ITEMS_B MSI
1237 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1238 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1239 ),
1240 PROG_INT_CHAR2 =
1241 NVL((SELECT 'Y'
1242 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1243 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1244 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1245 AND MSII.REQUEST_ID = UAI.REQUEST_ID
1246 AND MSII.TRANSACTION_TYPE = 'CREATE'
1247 AND MSII.PROCESS_FLAG = 7
1248 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1249 ),PROG_INT_CHAR2)
1250 WHERE UAI.DATA_SET_ID = p_data_set_id
1251 AND PROCESS_STATUS = G_PS_IN_PROCESS
1252 AND EXISTS (SELECT /*+ NO_UNNEST */ MSI.ITEM_CATALOG_GROUP_ID
1253 FROM MTL_SYSTEM_ITEMS_B MSI
1254 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1255 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
1256 ELSE
1257 -- Bug 11719885 : Start
1258 -- For ICC, check the value in MSII first then check in MSIB
1259 -- (as in case of ICC change for an item, the correct value will be present in MSII)
1260 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1261 SET (UAI.ITEM_CATALOG_GROUP_ID,
1262 UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1263 = (SELECT
1264 NVL(UAI.ITEM_CATALOG_GROUP_ID, MSII.ITEM_CATALOG_GROUP_ID),
1265 NVL( UAI.PROG_INT_NUM2, MSII.LIFECYCLE_ID),
1266 NVL( UAI.PROG_INT_NUM3, MSII.CURRENT_PHASE_ID)
1267 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1268 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1269 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1270 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1271 AND MSII.PROCESS_FLAG = 1
1272 AND ROWNUM = 1
1273 ),
1274 PROG_INT_CHAR2 =
1275 NVL((SELECT 'Y'
1276 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1277 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1278 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1279 AND MSII.TRANSACTION_TYPE = 'CREATE'
1280 AND MSII.PROCESS_FLAG = 1
1281 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1282 AND ROWNUM = 1
1283 ),PROG_INT_CHAR2)
1284 WHERE UAI.DATA_SET_ID = p_data_set_id
1285 AND PROCESS_STATUS = G_PS_IN_PROCESS
1286 AND EXISTS (SELECT 'X'
1287 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
1288 WHERE MSII2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1289 AND MSII2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1290 AND MSII2.SET_PROCESS_ID = UAI.DATA_SET_ID
1291 AND MSII2.PROCESS_FLAG = 1
1292 );
1293
1294
1295 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1296 SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
1297 UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1298 = (SELECT NVL(Nvl(UAI.ITEM_CATALOG_GROUP_ID,MSI.ITEM_CATALOG_GROUP_ID),-1) ,
1299 NVL(MSI.APPROVAL_STATUS,'A'),
1300 MSI.LIFECYCLE_ID, MSI.CURRENT_PHASE_ID
1301 FROM MTL_SYSTEM_ITEMS_B MSI
1302 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1303 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1304 ),
1305 PROG_INT_CHAR2 =
1306 NVL((SELECT 'Y'
1307 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1308 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1309 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1310 AND MSII.TRANSACTION_TYPE = 'CREATE'
1311 AND MSII.PROCESS_FLAG = 1
1312 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1313 AND ROWNUM = 1
1314 ),PROG_INT_CHAR2)
1315 WHERE UAI.DATA_SET_ID = p_data_set_id
1316 AND PROCESS_STATUS = G_PS_IN_PROCESS
1317 AND EXISTS (SELECT MSI.ITEM_CATALOG_GROUP_ID
1318 FROM MTL_SYSTEM_ITEMS_B MSI
1319 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1320 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
1321 /*
1322 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1323 SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
1324 UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1325 = (SELECT MSI.ITEM_CATALOG_GROUP_ID, NVL(MSI.APPROVAL_STATUS,'A'), -- Fix by bug 11782276
1326 MSI.LIFECYCLE_ID, MSI.CURRENT_PHASE_ID
1327 FROM MTL_SYSTEM_ITEMS_B MSI
1328 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1329 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1330 ),
1331 PROG_INT_CHAR2 =
1332 NVL((SELECT 'Y'
1333 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1334 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1335 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1336 AND MSII.TRANSACTION_TYPE = 'CREATE'
1337 AND MSII.PROCESS_FLAG = 1
1338 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1339 AND ROWNUM = 1
1340 ),PROG_INT_CHAR2)
1341 WHERE UAI.DATA_SET_ID = p_data_set_id
1342 AND PROCESS_STATUS = G_PS_IN_PROCESS
1343 AND EXISTS (SELECT MSI.ITEM_CATALOG_GROUP_ID
1344 FROM MTL_SYSTEM_ITEMS_B MSI
1345 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1346 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
1347
1348 -- If item is not found in production then get the values from interface
1349 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1350 SET (UAI.ITEM_CATALOG_GROUP_ID,
1351 UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1352 = (SELECT
1353 NVL( NVL(UAI.ITEM_CATALOG_GROUP_ID, MSII.ITEM_CATALOG_GROUP_ID), -1),
1354 NVL( UAI.PROG_INT_NUM2, MSII.LIFECYCLE_ID),
1355 NVL( UAI.PROG_INT_NUM3, MSII.CURRENT_PHASE_ID)
1356 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1357 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1358 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1359 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1360 AND MSII.PROCESS_FLAG = 1
1361 AND ROWNUM = 1
1362 ),
1363 PROG_INT_CHAR2 =
1364 NVL((SELECT 'Y'
1365 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1366 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1367 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1368 AND MSII.TRANSACTION_TYPE = 'CREATE'
1369 AND MSII.PROCESS_FLAG = 1
1370 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1371 AND ROWNUM = 1
1372 ),PROG_INT_CHAR2)
1373 WHERE UAI.DATA_SET_ID = p_data_set_id
1374 AND PROCESS_STATUS = G_PS_IN_PROCESS
1375 AND EXISTS (SELECT 'X'
1376 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
1377 WHERE MSII2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1378 AND MSII2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1379 AND MSII2.SET_PROCESS_ID = UAI.DATA_SET_ID
1380 AND MSII2.PROCESS_FLAG = 1
1381 );
1382 */
1383 -- Bug 11719885 : End
1384 END IF;
1385 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After item init');
1386
1387 ----------------------------------------------------------------------------
1388 -- Mark as errors all rows that share the same logical Attribute Group --
1389 -- row with any rows where we didn't end up with a valid Catalog Group ID --
1390 ----------------------------------------------------------------------------
1391 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1392 SET UAI.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID
1393 WHERE UAI.DATA_SET_ID = p_data_set_id
1394 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1395 AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1396 UAI2.ROW_IDENTIFIER
1397 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1398 WHERE UAI2.DATA_SET_ID = p_data_set_id
1399 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1400 AND UAI2.ITEM_CATALOG_GROUP_ID IS NULL);
1401
1402 IF (p_is_id_validations_reqd = FND_API.G_TRUE) THEN /* Fix for bug#9660659 */
1403 ---------------------------------------------------------
1404 -- Find the Attr Group Type for Attribute Name --
1405 ---------------------------------------------------------
1406 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1407 SET ATTR_GROUP_TYPE = NVL(ATTR_GROUP_TYPE,(SELECT DESCRIPTIVE_FLEXFIELD_NAME
1408 FROM EGO_FND_DSC_FLX_CTX_EXT
1409 WHERE APPLICATION_ID = 431
1410 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME
1411 AND ROWNUM = 1))
1412 ,PROCESS_STATUS = DECODE((SELECT COUNT(DESCRIPTIVE_FLEXFIELD_NAME)
1413 FROM EGO_FND_DSC_FLX_CTX_EXT
1414 WHERE APPLICATION_ID = 431
1415 AND (UAI.ATTR_GROUP_TYPE IS NULL OR UAI.ATTR_GROUP_TYPE=DESCRIPTIVE_FLEXFIELD_NAME)
1416 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME),
1417 1,PROCESS_STATUS
1418 ,G_PS_BAD_ATTR_GROUP_NAME)
1419 WHERE UAI.DATA_SET_ID = p_data_set_id
1420 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1421 AND UAI.ATTR_GROUP_ID IS NULL;
1422
1423 ---------------------------------------------------------
1424 -- Find the Bad Attr Group Id for Attribute Name --
1425 ---------------------------------------------------------
1426 IF (p_debug_level > 0) THEN
1427 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Attr Group ID validation', 0);
1428 END IF;
1429 ----------------------------------------------------------------------------
1430 -- Note: Attribute Internal Name take precidence over Attribute Group Id --
1431 ----------------------------------------------------------------------------
1432 -- to do check the performance cost 35
1433 -- UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1434 -- SET UAI.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_ID
1435 -- WHERE UAI.DATA_SET_ID = p_data_set_id
1436 -- AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1437 -- AND UAI.ATTR_GROUP_ID IS NOT NULL
1438 -- AND UAI.ATTR_GROUP_ID <> ( SELECT ATTR_GROUP_ID
1439 -- FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
1440 -- WHERE APPLICATION_ID = 431
1441 -- AND DESCRIPTIVE_FLEXFIELD_NAME = l_attr_group_type
1442 -- AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME);
1443
1444 -- to do check the performance cost 3
1445 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1446 SET UAI.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID
1447 WHERE UAI.DATA_SET_ID = p_data_set_id
1448 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1449 AND UAI.ATTR_GROUP_ID IS NOT NULL
1450 AND NOT EXISTS
1451 ( SELECT 'X'
1452 FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
1453 WHERE APPLICATION_ID = 431
1454 AND DESCRIPTIVE_FLEXFIELD_NAME = UAI.ATTR_GROUP_TYPE --l_attr_group_type
1455 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME
1456 AND ATTR_GROUP_ID = UAI.ATTR_GROUP_ID);
1457
1458 ---------------------------------------------------------
1459 -- Find the Attr Group Id for Attribute Name --
1460 ---------------------------------------------------------
1461 IF (p_debug_level > 0) THEN
1462 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Attr Group ID conversion', 0);
1463 END IF;
1464 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1465 SET ATTR_GROUP_ID = (SELECT ATTR_GROUP_ID
1466 FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
1467 WHERE APPLICATION_ID = 431
1468 AND DESCRIPTIVE_FLEXFIELD_NAME = UAI.ATTR_GROUP_TYPE --l_attr_group_type
1469 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME)
1470 WHERE UAI.DATA_SET_ID = p_data_set_id
1471 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1472 AND UAI.ATTR_GROUP_ID IS NULL;
1473
1474 ------------------------------------------------------------------------------
1475 -- Mark as errors all rows that didn't end up with a valid Attr Group ID --
1476 ------------------------------------------------------------------------------
1477
1478 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1479 SET UAI.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_NAME
1480 WHERE UAI.DATA_SET_ID = p_data_set_id
1481 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1482 AND UAI.ATTR_GROUP_ID IS NULL;
1483
1484 END IF; -- end of IF(p_is_id_validations_reqd = FND_API.G_TRUE) /* Fix for bug#9660659 */
1485 -------------------------------------------------------------------------
1486 -- Erase Revision information for Attr Groups associated at ITEM_LEVEL --
1487 -------------------------------------------------------------------------
1488 /*
1489 UPDATE EGO_ITM_USR_ATTR_INTRFC
1490 SET REVISION = NULL, REVISION_ID = NULL
1491 WHERE ROWID IN (SELECT I.ROWID
1492 FROM EGO_OBJ_AG_ASSOCS_B A,
1493 EGO_ITM_USR_ATTR_INTRFC I
1494 WHERE A.CLASSIFICATION_CODE = I.ITEM_CATALOG_GROUP_ID
1495 AND A.OBJECT_ID = G_ITEM_OBJECT_ID
1496 AND A.ATTR_GROUP_ID = I.ATTR_GROUP_ID
1497 AND A.DATA_LEVEL = 'ITEM_LEVEL'
1498 AND I.DATA_SET_ID = p_data_set_id
1499 AND I.PROCESS_STATUS = G_PS_IN_PROCESS);
1500 */
1501 --Bug 9325678 begin
1502 l_item_data_level_id_str := '';
1503 for x in (SELECT DATA_LEVEL_ID
1504 FROM EGO_DATA_LEVEL_B
1505 WHERE DATA_LEVEL_NAME = 'ITEM_LEVEL')
1506 loop
1507 if length(l_item_data_level_id_str) > 0 then
1508 l_item_data_level_id_str := l_item_data_level_id_str || ',';
1509 end if;
1510 l_item_data_level_id_str := l_item_data_level_id_str || x.DATA_LEVEL_ID;
1511 end loop;
1512 Debug_Conc_Log('l_item_data_level_id_str: '||l_item_data_level_id_str);
1513
1514 l_erase_revision_sql :=
1515 q'#UPDATE EGO_ITM_USR_ATTR_INTRFC S
1516 SET REVISION = NULL, REVISION_ID = NULL
1517 WHERE EXISTS (SELECT A.ROWID
1518 FROM EGO_OBJ_AG_ASSOCS_B A
1519 WHERE A.CLASSIFICATION_CODE = S.ITEM_CATALOG_GROUP_ID
1520 AND A.OBJECT_ID = :1
1521 AND A.ATTR_GROUP_ID = S.ATTR_GROUP_ID
1522 AND TO_CHAR(A.DATA_LEVEL_ID) in (:2))
1523 AND DATA_SET_ID = :3
1524 AND PROCESS_STATUS = :4 #';
1525 Debug_Conc_Log('Erase Revision sql statement: '||l_erase_revision_sql);
1526 EXECUTE IMMEDIATE l_erase_revision_sql USING G_ITEM_OBJECT_ID,
1527 l_item_data_level_id_str,
1528 p_data_set_id,
1529 G_PS_IN_PROCESS;
1530 --Bug 9325678 end
1531 /*
1532 ------------------------------------------------------------------------------
1533 -- Verify that all passed-in Organization IDs belong to Master Orgs --
1534 -- if they are for AGs associated at the Item level and that the Org IDs at --
1535 -- least exist for AGs associated at the Item Revision level. --
1536 -- if any row has an invalid Org ID, error it out along with all other rows --
1537 -- in its logical Attribute Group row (because it won't make sense to keep --
1538 -- processing the errored-out row's companions without the errored-out row) --
1539 ------------------------------------------------------------------------------
1540 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1541 SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_ID
1542 WHERE UAI.DATA_SET_ID = p_data_set_id
1543 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1544 AND UAI.ROW_IDENTIFIER IN
1545 (SELECT DISTINCT UAI2.ROW_IDENTIFIER
1546 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1547 WHERE UAI2.DATA_SET_ID = p_data_set_id
1548 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1549 AND UAI2.ORGANIZATION_ID IS NOT NULL
1550 AND NOT EXISTS
1551 (SELECT 'X'
1552 FROM MTL_PARAMETERS MP
1553 WHERE MP.ORGANIZATION_ID = UAI2.ORGANIZATION_ID
1554 AND (UAI2.REVISION_ID IS NOT NULL
1555 OR
1556 UAI2.REVISION IS NOT NULL
1557 OR
1558 MP.MASTER_ORGANIZATION_ID = UAI2.ORGANIZATION_ID
1559 )
1560 )
1561 );
1562
1563 */
1564
1565 ----------------------------------------------------
1566 -- Validate and convert data level entered --
1567 -- First convert DATA_LEVEL_NAME to data_level_id --
1568 -- where data_level_id is not populated --
1569 ----------------------------------------------------
1570 -- Bug 13434831 : Start (Performance Change)
1571 /*
1572 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1573 SET uai.DATA_LEVEL_ID = (SELECT edlb.DATA_LEVEL_ID
1574 FROM EGO_DATA_LEVEL_B edlb
1575 WHERE edlb.DATA_LEVEL_NAME = uai.DATA_LEVEL_NAME
1576 AND edlb.APPLICATION_ID = 431
1577 AND edlb.ATTR_GROUP_TYPE = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
1578 )
1579 WHERE uai.DATA_SET_ID = p_data_set_id
1580 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1581 AND uai.DATA_LEVEL_NAME IS NOT NULL
1582 AND uai.DATA_LEVEL_ID IS NULL;
1583
1584
1585 ----------------------------------------------------------
1586 -- Then convert USER_DATA_LEVEL_NAME to --
1587 -- data_level_id where data_level_id is not --
1588 -- populated and data_level_name is also not populated --
1589 ----------------------------------------------------------
1590
1591 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1592 SET uai.DATA_LEVEL_ID = (SELECT edlv.DATA_LEVEL_ID
1593 FROM EGO_DATA_LEVEL_VL edlv
1594 WHERE edlv.USER_DATA_LEVEL_NAME = uai.USER_DATA_LEVEL_NAME
1595 AND edlv.APPLICATION_ID = 431
1596 AND edlv.ATTR_GROUP_TYPE = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
1597 )
1598 WHERE uai.DATA_SET_ID = p_data_set_id
1599 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1600 AND uai.USER_DATA_LEVEL_NAME IS NOT NULL
1601 AND uai.DATA_LEVEL_NAME IS NULL
1602 AND uai.DATA_LEVEL_ID IS NULL;
1603 */
1604
1605 -- Consolidating above two queries into single query as below.
1606 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1607 SET uai.DATA_LEVEL_ID = Nvl ((SELECT edlb.DATA_LEVEL_ID
1608 FROM EGO_DATA_LEVEL_B edlb
1609 WHERE edlb.DATA_LEVEL_NAME = uai.DATA_LEVEL_NAME
1610 AND edlb.APPLICATION_ID = 431
1611 AND edlb.ATTR_GROUP_TYPE = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
1612 ),
1613 (SELECT edlv.DATA_LEVEL_ID
1614 FROM EGO_DATA_LEVEL_VL edlv
1615 WHERE edlv.USER_DATA_LEVEL_NAME = uai.USER_DATA_LEVEL_NAME
1616 AND edlv.APPLICATION_ID = 431
1617 AND edlv.ATTR_GROUP_TYPE = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
1618 )
1619 )
1620 WHERE uai.DATA_SET_ID = p_data_set_id
1621 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1622 AND uai.DATA_LEVEL_ID IS NULL;
1623 -- Bug 13434831 : End (Performance Change)
1624
1625
1626 -----------------------------------------------------------
1627 -- If all data level columns are null, then check if the --
1628 -- attribute group is associated at only one level, then --
1629 -- put that data level id here. --
1630 -----------------------------------------------------------
1631 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1632 SET DATA_LEVEL_ID = (SELECT DATA_LEVEL_ID
1633 FROM EGO_ATTR_GROUP_DL
1634 WHERE ATTR_GROUP_ID = uai.ATTR_GROUP_ID
1635 )
1636 WHERE uai.DATA_SET_ID = p_data_set_id
1637 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1638 AND uai.DATA_LEVEL_ID IS NULL
1639 AND uai.DATA_LEVEL_NAME IS NULL
1640 AND uai.USER_DATA_LEVEL_NAME IS NULL
1641 AND (SELECT COUNT(*)
1642 FROM EGO_ATTR_GROUP_DL
1643 WHERE ATTR_GROUP_ID = uai.ATTR_GROUP_ID) = 1;
1644
1645
1646 -------------------------------------------------
1647 -- Now, mark all the rows that does not have a --
1648 -- valid data_level_id populated --
1649 -------------------------------------------------
1650 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1651 SET uai.PROCESS_STATUS = G_PS_BAD_DATA_LEVEL
1652 WHERE uai.DATA_SET_ID = p_data_set_id
1653 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1654 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1655 uai2.ROW_IDENTIFIER
1656 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1657 WHERE uai2.DATA_SET_ID = p_data_set_id
1658 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1659 AND NOT EXISTS (SELECT NULL
1660 FROM EGO_DATA_LEVEL_B edlb
1661 WHERE edlb.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1662 )
1663 );
1664
1665 --------------------------------------------------
1666 -- Validating the Orag Id to be master org for --
1667 -- item, supplier and supplier site level --
1668 --------------------------------------------------
1669
1670 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1671 --SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_ID
1672 -- bug 8649262 Restore to Pre-R12C behavior, mark with Generic Error so
1673 -- this line will not be processed
1674 SET UAI.PROCESS_STATUS = G_PS_GENERIC_ERROR
1675
1676 WHERE UAI.DATA_SET_ID = p_data_set_id
1677 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1678 AND UAI.DATA_LEVEL_ID IN (43101, 43103,43104,43107,43108)
1679 AND EXISTS (SELECT MP2.ORGANIZATION_ID
1680 FROM MTL_PARAMETERS MP2
1681 WHERE MP2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1682 AND MP2.ORGANIZATION_ID <> MP2.MASTER_ORGANIZATION_ID);
1683
1684
1685
1686 ----------------------------------------------------------------
1687 -- Get data_level_ids to validate PK1_VALUE and PK2_VALUE --
1688 ----------------------------------------------------------------
1689 FOR i IN c_data_levels LOOP
1690 IF i.DATA_LEVEL_NAME = 'ITEM_SUP' THEN
1691 l_item_sup_dl_id := i.DATA_LEVEL_ID;
1692 ELSIF i.DATA_LEVEL_NAME = 'ITEM_SUP_SITE' THEN
1693 l_item_sup_site_dl_id := i.DATA_LEVEL_ID;
1694 ELSIF i.DATA_LEVEL_NAME = 'ITEM_SUP_SITE_ORG' THEN
1695 l_item_sup_site_org_dl_id := i.DATA_LEVEL_ID;
1696 END IF; -- IF i.DATA_LEVEL_NAME
1697 END LOOP; -- FOR i IN c_data_levels LOOP
1698
1699 ----------------------------------------------------------------
1700 -- Next, validate the Item Supplier attrs. Validate that the --
1701 -- pk1_value exists in ego_item_associations for this item --
1702 ----------------------------------------------------------------
1703 IF p_validate_only = FND_API.G_FALSE THEN
1704 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1705 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER
1706 WHERE uai.DATA_SET_ID = p_data_set_id
1707 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1708 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1709 UAI2.ROW_IDENTIFIER
1710 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1711 WHERE uai2.DATA_SET_ID = p_data_set_id
1712 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1713 AND uai2.DATA_LEVEL_ID = l_item_sup_dl_id
1714 AND ( uai2.PK1_VALUE IS NULL
1715 OR
1716 ( uai2.PK1_VALUE IS NOT NULL
1717 AND uai2.PK2_VALUE IS NOT NULL
1718 )
1719 OR
1720 ( uai2.PK1_VALUE IS NOT NULL
1721 AND uai2.PK2_VALUE IS NULL
1722 AND NOT EXISTS (SELECT NULL
1723 FROM EGO_ITEM_ASSOCIATIONS eia
1724 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1725 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1726 AND eia.PK1_VALUE = uai2.PK1_VALUE
1727 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1728 )
1729 )
1730 )
1731 );
1732 ELSE
1733 -- Bug 13434831 : Start (Performance Change)
1734 /*
1735 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1736 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER
1737 WHERE uai.DATA_SET_ID = p_data_set_id
1738 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1739 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1740 UAI2.ROW_IDENTIFIER
1741 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1742 WHERE uai2.DATA_SET_ID = p_data_set_id
1743 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1744 AND uai2.DATA_LEVEL_ID = l_item_sup_dl_id
1745 AND ( uai2.PK1_VALUE IS NULL
1746 OR
1747 ( uai2.PK1_VALUE IS NOT NULL
1748 AND uai2.PK2_VALUE IS NOT NULL
1749 )
1750 OR
1751 ( uai2.PK1_VALUE IS NOT NULL
1752 AND uai2.PK2_VALUE IS NULL
1753 AND NOT EXISTS (SELECT NULL
1754 FROM EGO_ITEM_ASSOCIATIONS eia
1755 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1756 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1757 AND eia.PK1_VALUE = uai2.PK1_VALUE
1758 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1759 UNION ALL
1760 SELECT NULL
1761 FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
1762 WHERE (eiai.ITEM_NUMBER = uai2.ITEM_NUMBER -- Bug 13246699
1763 OR eiai.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID)
1764 AND eiai.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1765 AND eiai.PK1_VALUE = uai2.PK1_VALUE
1766 AND eiai.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1767 AND eiai.BATCH_ID = uai2.DATA_SET_ID
1768 AND eiai.PROCESS_FLAG = 1
1769 )
1770 )
1771 )
1772 );
1773 */
1774 -- above query is modifed a below.
1775 UPDATE ego_itm_usr_attr_intrfc uai
1776 SET uai.process_status = G_PS_BAD_SUPPLIER
1777 WHERE uai.data_set_id = p_data_set_id
1778 AND uai.process_status = G_PS_IN_PROCESS
1779 AND uai.data_level_id = l_item_sup_dl_id
1780 AND ( uai.pk1_value IS NULL
1781 OR ( uai.pk1_value IS NOT NULL
1782 AND uai.pk2_value IS NOT NULL )
1783 OR ( uai.pk1_value IS NOT NULL
1784 AND uai.pk2_value IS NULL
1785 AND NOT EXISTS (SELECT NULL
1786 FROM ego_item_associations eia
1787 WHERE eia.inventory_item_id = uai.inventory_item_id
1788 AND eia.organization_id = uai.organization_id
1789 AND eia.pk1_value = uai.pk1_value
1790 AND eia.data_level_id = uai.data_level_id
1791 UNION ALL
1792 SELECT NULL
1793 FROM ego_item_associations_intf eiai
1794 WHERE (eiai.ITEM_NUMBER = uai.ITEM_NUMBER -- Bug 13246699
1795 OR eiai.inventory_item_id = uai.inventory_item_id)
1796 AND eiai.organization_id = uai.organization_id
1797 AND eiai.pk1_value = uai.pk1_value
1798 AND eiai.data_level_id = uai.data_level_id
1799 AND eiai.batch_id = uai.data_set_id
1800 AND eiai.process_flag = 1)
1801 )
1802 );
1803 -- Bug 13434831 : End (Performance Change)
1804 END IF;
1805
1806 -----------------------------------------------------------------
1807 -- Next, validate the Item Supplier site attrs. Validate that --
1808 -- the pk2_value exists in ego_item_associations for this item --
1809 -----------------------------------------------------------------
1810 IF p_validate_only = FND_API.G_FALSE THEN
1811 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1812 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
1813 WHERE uai.DATA_SET_ID = p_data_set_id
1814 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1815 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1816 UAI2.ROW_IDENTIFIER
1817 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1818 WHERE uai2.DATA_SET_ID = p_data_set_id
1819 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1820 AND uai2.DATA_LEVEL_ID = l_item_sup_site_dl_id
1821 AND ( uai2.PK1_VALUE IS NULL
1822 OR
1823 uai2.PK2_VALUE IS NULL
1824 OR
1825 ( uai2.PK1_VALUE IS NOT NULL
1826 AND uai2.PK2_VALUE IS NOT NULL
1827 AND NOT EXISTS (SELECT NULL
1828 FROM EGO_ITEM_ASSOCIATIONS eia
1829 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1830 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1831 AND eia.PK1_VALUE = uai2.PK1_VALUE
1832 AND eia.PK2_VALUE = uai2.PK2_VALUE
1833 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1834 )
1835 )
1836 )
1837 );
1838 ELSE
1839 -- Bug 13434831 : Start (Performance Change)
1840 /*
1841 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1842 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
1843 WHERE uai.DATA_SET_ID = p_data_set_id
1844 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1845 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1846 UAI2.ROW_IDENTIFIER
1847 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1848 WHERE uai2.DATA_SET_ID = p_data_set_id
1849 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1850 AND uai2.DATA_LEVEL_ID = l_item_sup_site_dl_id
1851 AND ( uai2.PK1_VALUE IS NULL
1852 OR
1853 uai2.PK2_VALUE IS NULL
1854 OR
1855 ( uai2.PK1_VALUE IS NOT NULL
1856 AND uai2.PK2_VALUE IS NOT NULL
1857 AND NOT EXISTS (SELECT NULL
1858 FROM EGO_ITEM_ASSOCIATIONS eia
1859 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1860 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1861 AND eia.PK1_VALUE = uai2.PK1_VALUE
1862 AND eia.PK2_VALUE = uai2.PK2_VALUE
1863 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1864 UNION ALL
1865 SELECT NULL
1866 FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
1867 WHERE (eiai.ITEM_NUMBER = uai2.ITEM_NUMBER -- Bug 13246699
1868 OR eiai.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID)
1869 AND eiai.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1870 AND eiai.PK1_VALUE = uai2.PK1_VALUE
1871 AND eiai.PK2_VALUE = uai2.PK2_VALUE
1872 AND eiai.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1873 AND eiai.BATCH_ID = uai2.DATA_SET_ID
1874 AND eiai.PROCESS_FLAG = 1
1875 )
1876 )
1877 )
1878 );
1879 */
1880
1881 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1882 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
1883 WHERE uai.DATA_SET_ID = p_data_set_id
1884 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1885 AND uai.DATA_LEVEL_ID = l_item_sup_site_dl_id
1886 AND ( uai.PK1_VALUE IS NULL
1887 OR
1888 uai.PK2_VALUE IS NULL
1889 OR
1890 ( uai.PK1_VALUE IS NOT NULL
1891 AND uai.PK2_VALUE IS NOT NULL
1892 AND NOT EXISTS (SELECT NULL
1893 FROM EGO_ITEM_ASSOCIATIONS eia
1894 WHERE eia.INVENTORY_ITEM_ID = uai.INVENTORY_ITEM_ID
1895 AND eia.ORGANIZATION_ID = uai.ORGANIZATION_ID
1896 AND eia.PK1_VALUE = uai.PK1_VALUE
1897 AND eia.PK2_VALUE = uai.PK2_VALUE
1898 AND eia.DATA_LEVEL_ID = uai.DATA_LEVEL_ID
1899 UNION ALL
1900 SELECT NULL
1901 FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
1902 WHERE (eiai.ITEM_NUMBER = uai.ITEM_NUMBER -- Bug 13246699
1903 OR eiai.INVENTORY_ITEM_ID = uai.INVENTORY_ITEM_ID)
1904 AND eiai.ORGANIZATION_ID = uai.ORGANIZATION_ID
1905 AND eiai.PK1_VALUE = uai.PK1_VALUE
1906 AND eiai.PK2_VALUE = uai.PK2_VALUE
1907 AND eiai.DATA_LEVEL_ID = uai.DATA_LEVEL_ID
1908 AND eiai.BATCH_ID = uai.DATA_SET_ID
1909 AND eiai.PROCESS_FLAG = 1
1910 )
1911 )
1912 );
1913 -- Bug 13434831 : End (Performance Change)
1914 END IF;
1915
1916
1917 ------------------------------------------------------------------
1918 -- Next, validate the Item Supplier site Org attrs. Validate --
1919 -- that the pk1,pk2_value along with the organization_id exists --
1920 -- in ego_item_associations for this item --
1921 ------------------------------------------------------------------
1922 IF p_validate_only = FND_API.G_FALSE THEN
1923 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1924 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
1925 WHERE uai.DATA_SET_ID = p_data_set_id
1926 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1927 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1928 UAI2.ROW_IDENTIFIER
1929 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1930 WHERE uai2.DATA_SET_ID = p_data_set_id
1931 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1932 AND uai2.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
1933 AND ( uai2.PK1_VALUE IS NULL
1934 OR
1935 uai2.PK2_VALUE IS NULL
1936 OR
1937 ( uai2.PK1_VALUE IS NOT NULL
1938 AND uai2.PK2_VALUE IS NOT NULL
1939 AND NOT EXISTS (SELECT NULL
1940 FROM EGO_ITEM_ASSOCIATIONS eia
1941 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1942 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1943 AND eia.PK1_VALUE = uai2.PK1_VALUE
1944 AND eia.PK2_VALUE = uai2.PK2_VALUE
1945 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1946 )
1947 )
1948 )
1949 );
1950 ELSE
1951 -- Bug 13434831 : Start (Performance Change)
1952 /*
1953 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1954 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
1955 WHERE uai.DATA_SET_ID = p_data_set_id
1956 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1957 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1958 UAI2.ROW_IDENTIFIER
1959 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1960 WHERE uai2.DATA_SET_ID = p_data_set_id
1961 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1962 AND uai2.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
1963 AND ( uai2.PK1_VALUE IS NULL
1964 OR
1965 uai2.PK2_VALUE IS NULL
1966 OR
1967 ( uai2.PK1_VALUE IS NOT NULL
1968 AND uai2.PK2_VALUE IS NOT NULL
1969 AND NOT EXISTS (SELECT NULL
1970 FROM EGO_ITEM_ASSOCIATIONS eia
1971 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1972 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1973 AND eia.PK1_VALUE = uai2.PK1_VALUE
1974 AND eia.PK2_VALUE = uai2.PK2_VALUE
1975 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1976 UNION ALL
1977 SELECT NULL
1978 FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
1979 WHERE (eiai.ITEM_NUMBER = uai2.ITEM_NUMBER -- Bug 13246699
1980 OR eiai.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID)
1981 AND eiai.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1982 AND eiai.PK1_VALUE = uai2.PK1_VALUE
1983 AND eiai.PK2_VALUE = uai2.PK2_VALUE
1984 AND eiai.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1985 AND eiai.BATCH_ID = uai2.DATA_SET_ID
1986 AND eiai.PROCESS_FLAG = 1
1987 )
1988 )
1989 )
1990 );
1991 */
1992 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1993 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
1994 WHERE uai.DATA_SET_ID = p_data_set_id
1995 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1996 AND uai.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
1997 AND ( uai.PK1_VALUE IS NULL
1998 OR
1999 uai.PK2_VALUE IS NULL
2000 OR
2001 ( uai.PK1_VALUE IS NOT NULL
2002 AND uai.PK2_VALUE IS NOT NULL
2003 AND NOT EXISTS (SELECT NULL
2004 FROM EGO_ITEM_ASSOCIATIONS eia
2005 WHERE eia.INVENTORY_ITEM_ID = uai.INVENTORY_ITEM_ID
2006 AND eia.ORGANIZATION_ID = uai.ORGANIZATION_ID
2007 AND eia.PK1_VALUE = uai.PK1_VALUE
2008 AND eia.PK2_VALUE = uai.PK2_VALUE
2009 AND eia.DATA_LEVEL_ID = uai.DATA_LEVEL_ID
2010 UNION ALL
2011 SELECT NULL
2012 FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
2013 WHERE (eiai.ITEM_NUMBER = uai.ITEM_NUMBER -- Bug 13246699
2014 OR eiai.INVENTORY_ITEM_ID = uai.INVENTORY_ITEM_ID)
2015 AND eiai.ORGANIZATION_ID = uai.ORGANIZATION_ID
2016 AND eiai.PK1_VALUE = uai.PK1_VALUE
2017 AND eiai.PK2_VALUE = uai.PK2_VALUE
2018 AND eiai.DATA_LEVEL_ID = uai.DATA_LEVEL_ID
2019 AND eiai.BATCH_ID = uai.DATA_SET_ID
2020 AND eiai.PROCESS_FLAG = 1
2021 )
2022 )
2023 );
2024 -- Bug 13434831 : End (Performance Change)
2025 END IF;
2026
2027
2028 --------------------------------------------------------------------------------
2029 -- Mark as errors all rows that share the same logical Attribute Group --
2030 -- Variant attribute values for existing SKUs can not be updated --
2031 --------------------------------------------------------------------------------
2032 IF p_validate_only = FND_API.G_TRUE THEN
2033 UPDATE EGO_ITM_USR_ATTR_INTRFC
2034 SET PROCESS_STATUS = G_PS_SKU_VAR_VALUE_NOT_UPD
2035 WHERE DATA_SET_ID = p_data_set_id
2036 AND PROCESS_STATUS = G_PS_IN_PROCESS
2037 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2038 FROM
2039 EGO_ITM_USR_ATTR_INTRFC intf,
2040 EGO_FND_DSC_FLX_CTX_EXT ag_ext,
2041 MTL_SYSTEM_ITEMS_B msib
2042 WHERE intf.DATA_SET_ID = p_data_set_id
2043 AND intf.PROCESS_STATUS = G_PS_IN_PROCESS
2044 AND intf.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
2045 AND ag_ext.VARIANT = 'Y'
2046 AND intf.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
2047 AND intf.ORGANIZATION_ID = msib.ORGANIZATION_ID
2048 AND msib.STYLE_ITEM_FLAG = 'N'
2049 AND EXISTS (SELECT NULL
2050 FROM EGO_MTL_SY_ITEMS_EXT_B ext_prod
2051 WHERE ext_prod.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
2052 AND ext_prod.ORGANIZATION_ID = msib.ORGANIZATION_ID
2053 AND ext_prod.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
2054 )
2055 );
2056
2057 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked all SKU records to error, if trying to update variant value, count='||SQL%ROWCOUNT, 1);
2058 END IF;
2059
2060 --------------------------------------------------------------------------------
2061 -- Mark as errors all rows that share the same logical Attribute Group --
2062 -- Inherited attribute values can not be processed for SKUs --
2063 --------------------------------------------------------------------------------
2064 UPDATE EGO_ITM_USR_ATTR_INTRFC
2065 SET PROCESS_STATUS = G_PS_INH_ATTR_FOR_SKU_NOT_UPD
2066 WHERE DATA_SET_ID = p_data_set_id
2067 AND PROCESS_STATUS = G_PS_IN_PROCESS
2068 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2069 FROM
2070 EGO_ITM_USR_ATTR_INTRFC intf,
2071 EGO_ATTR_GROUP_DL eagd
2072 WHERE intf.DATA_SET_ID = p_data_set_id
2073 AND intf.PROCESS_STATUS = G_PS_IN_PROCESS
2074 AND intf.ATTR_GROUP_ID = eagd.ATTR_GROUP_ID
2075 AND intf.DATA_LEVEL_ID = eagd.DATA_LEVEL_ID
2076 AND NVL(eagd.DEFAULTING, 'D') = 'I'
2077 AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
2078 WHERE msii.SET_PROCESS_ID = intf.DATA_SET_ID
2079 AND msii.PROCESS_FLAG = 1
2080 AND msii.INVENTORY_ITEM_ID = intf.INVENTORY_ITEM_ID
2081 AND msii.ORGANIZATION_ID = intf.ORGANIZATION_ID
2082 AND msii.STYLE_ITEM_FLAG = 'N'
2083 UNION ALL
2084 SELECT NULL FROM MTL_SYSTEM_ITEMS_B msib
2085 WHERE intf.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
2086 AND intf.ORGANIZATION_ID = msib.ORGANIZATION_ID
2087 AND msib.STYLE_ITEM_FLAG = 'N'
2088 )
2089 );
2090
2091 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked all SKU records to error, if trying to update inherited attribute value, count='||SQL%ROWCOUNT, 1);
2092
2093
2094 ---------------------------------------
2095 -- Set Lifecycle of the revision items.
2096 ---------------------------------------
2097 IF p_validate_only = FND_API.G_FALSE THEN
2098 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2099 SET (UAI.PROG_INT_NUM3)
2100 = NVL((SELECT MIR.CURRENT_PHASE_ID
2101 FROM MTL_ITEM_REVISIONS MIR
2102 WHERE MIR.REVISION_ID = UAI.REVISION_ID
2103 ), UAI.PROG_INT_NUM3),
2104 PROG_INT_CHAR2 =
2105 NVL((SELECT 'Y'
2106 FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
2107 WHERE MIRI.REVISION_ID = UAI.REVISION_ID
2108 AND MIRI.request_id = UAI.REQUEST_ID
2109 AND MIRI.TRANSACTION_TYPE = 'CREATE'
2110 AND MIRI.PROCESS_FLAG = 7
2111 AND ROWNUM = 1
2112 ),PROG_INT_CHAR2)
2113 WHERE UAI.DATA_SET_ID = p_data_set_id
2114 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
2115 AND UAI.REVISION_ID IS NOT NULL
2116 AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL;
2117 ELSE
2118 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2119 SET UAI.PROG_INT_NUM3
2120 = NVL((CASE WHEN EXISTS (SELECT 1
2121 FROM MTL_ITEM_REVISIONS mir
2122 WHERE mir.REVISION_ID = UAI.REVISION_ID
2123 )
2124 THEN (SELECT mir1.CURRENT_PHASE_ID
2125 FROM MTL_ITEM_REVISIONS mir1
2126 WHERE mir1.REVISION_ID = UAI.REVISION_ID
2127 )
2128 ELSE (SELECT miri.CURRENT_PHASE_ID
2129 FROM MTL_ITEM_REVISIONS_INTERFACE miri
2130 WHERE miri.REVISION_ID = UAI.REVISION_ID
2131 AND miri.SET_PROCESS_ID = UAI.DATA_SET_ID
2132 AND miri.PROCESS_FLAG = 1
2133 AND ROWNUM = 1
2134 )
2135 END
2136 ), UAI.PROG_INT_NUM3),
2137 PROG_INT_CHAR2 =
2138 NVL((SELECT 'Y'
2139 FROM MTL_ITEM_REVISIONS_INTERFACE MIRI1
2140 WHERE MIRI1.REVISION_ID = UAI.REVISION_ID
2141 AND MIRI1.SET_PROCESS_ID = UAI.DATA_SET_ID
2142 AND MIRI1.TRANSACTION_TYPE = 'CREATE'
2143 AND MIRI1.PROCESS_FLAG = 1
2144 AND ROWNUM = 1
2145 ),PROG_INT_CHAR2)
2146 WHERE UAI.DATA_SET_ID = p_data_set_id
2147 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
2148 AND UAI.REVISION_ID IS NOT NULL
2149 AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL;
2150 END IF;
2151 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After rev lc');
2152
2153 ----------------------------------------------
2154 -- Set the actual cc where LC is associated --
2155 ----------------------------------------------
2156 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2157 SET UAI.PROG_INT_NUM1 =
2158 (SELECT ic.item_catalog_group_id
2159 FROM mtl_item_catalog_groups_b ic
2160 WHERE EXISTS
2161 ( SELECT olc.object_classification_code CatalogId
2162 FROM ego_obj_type_lifecycles olc
2163 WHERE olc.object_id = G_ITEM_OBJECT_ID
2164 AND olc.lifecycle_id = UAI.PROG_INT_NUM2
2165 AND olc.object_classification_code = ic.item_catalog_group_id
2166 )
2167 AND ROWNUM = 1
2168 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
2169 START WITH item_catalog_group_id = UAI.item_catalog_group_id
2170 )
2171 WHERE UAI.DATA_SET_ID = p_data_set_id
2172 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
2173 AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL
2174 AND UAI.PROG_INT_NUM2 IS NOT NULL
2175 AND UAI.PROG_INT_CHAR1 = 'A'
2176 AND UAI.PROG_INT_CHAR2 = 'N';
2177 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After hier init');
2178
2179 l_policy_check_sql :=
2180 ' UPDATE EGO_ITM_USR_ATTR_INTRFC UAI '||
2181 ' SET UAI.PROCESS_STATUS = :1 '||
2182 ' WHERE UAI.DATA_SET_ID = :2 '||
2183 ' AND UAI.PROCESS_STATUS = :3 '||
2184 ' AND UAI.ROW_IDENTIFIER IN '||
2185 ' (SELECT DISTINCT UAI2.ROW_IDENTIFIER '||
2186 ' FROM EGO_ITM_USR_ATTR_INTRFC UAI2, ENG_CHANGE_POLICIES_V ECP '||
2187 ' WHERE UAI2.DATA_SET_ID = :4 '||
2188 ' AND UAI2.PROCESS_STATUS = :5 '||
2189 ' AND UAI2.ITEM_CATALOG_GROUP_ID IS NOT NULL '||
2190 ' AND UAI2.PROG_INT_NUM2 IS NOT NULL '||
2191 ' AND UAI2.PROG_INT_CHAR1 = ''A''' ||
2192 ' AND UAI2.PROG_INT_CHAR2 = ''N''' ||
2193 ' AND ECP.ATTRIBUTE_OBJECT_NAME = ''EGO_CATALOG_GROUP'' '||
2194 ' AND ECP.ATTRIBUTE_CODE = ''ATTRIBUTE_GROUP'' '||
2195 ' AND ECP.POLICY_OBJECT_NAME = ''CATALOG_LIFECYCLE_PHASE'' '||
2196 ' AND ECP.POLICY_CHAR_VALUE IS NOT NULL '||
2197 ' AND ECP.POLICY_CHAR_VALUE = :6 '||
2198 ' AND ECP.ATTRIBUTE_NUMBER_VALUE = UAI2.ATTR_GROUP_ID '||
2199 ' AND ECP.POLICY_OBJECT_PK1_VALUE = TO_CHAR(UAI2.PROG_INT_NUM1) '||
2200 ' AND ECP.POLICY_OBJECT_PK2_VALUE = TO_CHAR(UAI2.PROG_INT_NUM2) '||
2201 ' AND ECP.POLICY_OBJECT_PK3_VALUE = TO_CHAR(UAI2.PROG_INT_NUM3) '||
2202 ' AND DATA_LEVEL_ID IN ( SELECT DATA_LEVEL_ID FROM EGO_DATA_LEVEL_B '||
2203 ' WHERE APPLICATION_ID = ''431'' '||
2204 ' AND DATA_LEVEL_NAME IN ( ''ITEM_LEVEL'', ''ITEM_REVISION_LEVEL'', ''ITEM_ORG'')) '||
2205 ' )';
2206
2207
2208
2209 IF (p_debug_level > 0) THEN
2210 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Change Policy conversion/erroring', 0);
2211 END IF;
2212 -------------------------------------------------------------------------------
2213 -- 5). Mark as errors all rows that share the same logical Attribute Group --
2214 -- row with any rows for which the Change Policy is defined as NOT_ALLOWED --
2215 -- (we do not process such rows; they cannot be modified); --
2216 -- the exception to this is rows for pending Items, which we still processed --
2217 -------------------------------------------------------------------------------
2218
2219 BEGIN
2220 l_policy_check_name := 'NOT_ALLOWED';
2221 EXECUTE IMMEDIATE l_policy_check_sql USING G_PS_CHG_POLICY_NOT_ALLOWED,
2222 p_data_set_id,
2223 G_PS_IN_PROCESS,
2224 p_data_set_id,
2225 G_PS_IN_PROCESS,
2226 l_policy_check_name;
2227 EXCEPTION
2228 WHEN OTHERS THEN
2229 NULL;
2230 END;
2231
2232 -------------------------------------------------------------------------------
2233 -- Processing variant attribute groups for Style item --
2234 -- Assumption is that STYLE_ITEM_FLAG in MSII will be populated always --
2235 -- before this call, even for items that exists in production --
2236 -- We are changing the process_status of all the variant attribute groups --
2237 -- to G_PS_STYLE_VARIANT_IN_PROCESS so that they are not picked up by --
2238 -- UDA bulkloader. --
2239 -------------------------------------------------------------------------------
2240 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Style value set processing', 1);
2241
2242 -- Fix for bug#9336604
2243 -- Added index hint
2244 UPDATE /*+ index(INTF, EGO_ITM_USR_ATTR_INTRFC_N3 ) */ EGO_ITM_USR_ATTR_INTRFC INTF
2245 SET PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2246 WHERE INTF.DATA_SET_ID = p_data_set_id
2247 AND INTF.PROCESS_STATUS = G_PS_IN_PROCESS
2248 AND EXISTS (SELECT NULL
2249 FROM EGO_FND_DSC_FLX_CTX_EXT AG_EXT, MTL_SYSTEM_ITEMS_INTERFACE MSII
2250 WHERE AG_EXT.VARIANT = 'Y'
2251 AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL(INTF.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
2252 AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = INTF.ATTR_GROUP_INT_NAME
2253 AND INTF.ORGANIZATION_ID = MSII.ORGANIZATION_ID
2254 AND MSII.STYLE_ITEM_FLAG = 'Y'
2255 AND INTF.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
2256 AND AG_EXT.APPLICATION_ID = 431 /* Bug 9678667 */
2257 UNION ALL
2258 SELECT NULL
2259 FROM EGO_FND_DSC_FLX_CTX_EXT AG_EXT1, MTL_SYSTEM_ITEMS_B MSIB
2260 WHERE AG_EXT1.VARIANT = 'Y'
2261 AND AG_EXT1.DESCRIPTIVE_FLEXFIELD_NAME = NVL(INTF.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
2262 AND AG_EXT1.DESCRIPTIVE_FLEX_CONTEXT_CODE = INTF.ATTR_GROUP_INT_NAME
2263 AND INTF.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
2264 AND MSIB.STYLE_ITEM_FLAG = 'Y'
2265 AND INTF.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
2266 AND AG_EXT1.APPLICATION_ID = 431 /* Bug 9678667 */
2267 );
2268
2269 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Updated Style records count='||SQL%ROWCOUNT, 1);
2270
2271 -------------------------------------------------------------------------------
2272 -- Mark as errors all rows that share the same logical Attribute Group --
2273 -- Associated value set can not be changed if any sku exists for the style --
2274 -------------------------------------------------------------------------------
2275 UPDATE EGO_ITM_USR_ATTR_INTRFC
2276 SET PROCESS_STATUS = G_PS_VAR_VSET_CHG_NOT_ALLOWED
2277 WHERE DATA_SET_ID = p_data_set_id
2278 AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2279 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2280 FROM EGO_ITM_USR_ATTR_INTRFC INTF, MTL_SYSTEM_ITEMS_KFV MSIK
2281 WHERE INTF.DATA_SET_ID = p_data_set_id
2282 AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2283 AND MSIK.STYLE_ITEM_ID = INTF.INVENTORY_ITEM_ID
2284 AND MSIK.ORGANIZATION_ID = INTF.ORGANIZATION_ID
2285 );
2286
2287 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Updated records to error where SKU exists for style, count='||SQL%ROWCOUNT, 1);
2288
2289 -------------------------------------------------------------------------------
2290 -- Converting all the value set names entered by user to value set id --
2291 -- Value set name is expected in ATTR_DISP_VALUE column, we convert it to --
2292 -- value set id and store it in ATTR_VALUE_NUM col. --
2293 -------------------------------------------------------------------------------
2294 UPDATE EGO_ITM_USR_ATTR_INTRFC INTF
2295 SET ATTR_VALUE_NUM = (SELECT VALUE_SET_ID FROM EGO_VALUE_SETS_V VS
2296 WHERE VALUE_SET_NAME = INTF.ATTR_DISP_VALUE)
2297 WHERE INTF.DATA_SET_ID = p_data_set_id
2298 AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2299 AND INTF.ATTR_DISP_VALUE IS NOT NULL;
2300
2301
2302 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Converted value set name to value set Id for styles, count='||SQL%ROWCOUNT, 1);
2303 --------------------------------------------------------------------------------
2304 -- Mark as errors all rows that share the same logical Attribute Group --
2305 -- If not a valid value set i.e. Only value set associated at attribute level --
2306 -- or its child value set can be associated. --
2307 --------------------------------------------------------------------------------
2308 UPDATE EGO_ITM_USR_ATTR_INTRFC
2309 SET PROCESS_STATUS = G_PS_BAD_STYLE_VAR_VALUE_SET
2310 WHERE DATA_SET_ID = p_data_set_id
2311 AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2312 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2313 FROM EGO_ITM_USR_ATTR_INTRFC INTF
2314 WHERE INTF.DATA_SET_ID = p_data_set_id
2315 AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2316 AND NOT EXISTS (SELECT NULL
2317 FROM FND_DESCR_FLEX_COLUMN_USAGES FL_COL
2318 WHERE FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = INTF.ATTR_GROUP_TYPE
2319 AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = INTF.ATTR_GROUP_INT_NAME
2320 AND FL_COL.END_USER_COLUMN_NAME = INTF.ATTR_INT_NAME
2321 AND FL_COL.APPLICATION_ID = 431
2322 AND (INTF.ATTR_VALUE_NUM = FL_COL.FLEX_VALUE_SET_ID
2323 OR INTF.ATTR_VALUE_NUM IN (SELECT VS.VALUE_SET_ID
2324 FROM EGO_VALUE_SET_EXT VS
2325 WHERE VS.PARENT_VALUE_SET_ID = FL_COL.FLEX_VALUE_SET_ID)
2326 )
2327 )
2328 );
2329
2330 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked error for invalid value sets, for styles, count='||SQL%ROWCOUNT, 1);
2331
2332 --------------------------------------------------------------------------------
2333 -- Processing Style variant value sets. Using a merge statement so that bulk --
2334 -- feature can be used --
2335 --------------------------------------------------------------------------------
2336 IF p_validate_only = FND_API.G_FALSE THEN
2337 MERGE INTO EGO_STYLE_VARIANT_ATTR_VS ESVAV
2338 USING (SELECT
2339 INTF.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
2340 INTF.ATTR_VALUE_NUM AS VALUE_SET_ID,
2341 ATTR.ATTR_ID AS ATTRIBUTE_ID
2342 FROM
2343 EGO_ITM_USR_ATTR_INTRFC INTF,
2344 EGO_FND_DF_COL_USGS_EXT ATTR,
2345 FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
2346 MTL_SYSTEM_ITEMS_B MSIB
2347 WHERE INTF.ATTR_GROUP_TYPE = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
2348 AND INTF.ATTR_GROUP_INT_NAME = ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE
2349 AND ATTR.APPLICATION_ID = 431
2350 AND ATTR.APPLICATION_ID = FL_COL.APPLICATION_ID
2351 AND ATTR.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
2352 AND ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
2353 AND ATTR.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
2354 AND INTF.ATTR_INT_NAME = FL_COL.END_USER_COLUMN_NAME
2355 AND INTF.DATA_SET_ID = p_data_set_id
2356 AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2357 AND MSIB.INVENTORY_ITEM_ID = INTF.INVENTORY_ITEM_ID
2358 AND MSIB.ORGANIZATION_ID = INTF.ORGANIZATION_ID) INTRFC
2359 ON (ESVAV.INVENTORY_ITEM_ID = INTRFC.INVENTORY_ITEM_ID
2360 AND ESVAV.ATTRIBUTE_ID = INTRFC.ATTRIBUTE_ID)
2361 WHEN MATCHED THEN
2362 UPDATE SET ESVAV.VALUE_SET_ID = INTRFC.VALUE_SET_ID
2363 WHEN NOT MATCHED THEN
2364 INSERT
2365 (
2366 INVENTORY_ITEM_ID,
2367 VALUE_SET_ID,
2368 ATTRIBUTE_ID,
2369 LAST_UPDATE_LOGIN,
2370 CREATION_DATE,
2371 CREATED_BY,
2372 LAST_UPDATE_DATE,
2373 LAST_UPDATED_BY
2374 )
2375 VALUES
2376 (
2377 INTRFC.INVENTORY_ITEM_ID,
2378 INTRFC.VALUE_SET_ID,
2379 INTRFC.ATTRIBUTE_ID,
2380 l_login_id,
2381 SYSDATE,
2382 l_user_id,
2383 SYSDATE,
2384 l_user_id
2385 );
2386
2387 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Inserted value sets to EGO_STYLE_VARIANT_ATTR_VS, for styles, count='||SQL%ROWCOUNT, 1);
2388
2389 ---------------------------------------------------------------------------------
2390 -- Marking all these records as processed --
2391 -- All the records that are in status G_PS_STYLE_VARIANT_IN_PROCESS to success --
2392 ---------------------------------------------------------------------------------
2393 UPDATE EGO_ITM_USR_ATTR_INTRFC
2394 SET PROCESS_STATUS = G_PS_SUCCESS
2395 WHERE DATA_SET_ID = p_data_set_id
2396 AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS;
2397
2398 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked all style records to success, count='||SQL%ROWCOUNT, 1);
2399 END IF; --IF p_validate_only = FND_API.G_FALSE THEN
2400
2401
2402
2403 --========================================--
2404 -- ERROR REPORTING FOR FAILED CONVERSIONS --
2405 --========================================--
2406 IF (p_debug_level > 0) THEN
2407 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting conversion error-reporting', 0);
2408 END IF;
2409
2410 -------------------------------------------------------------------------
2411 -- PIM for Telco item uda validations --
2412 -------------------------------------------------------------------------
2413
2414 /*IF (profile_value = 'Y') THEN
2415 l_row_identifier := NULL;
2416 FOR c_row_identfier_rec IN c_row_identfier(p_data_set_id)
2417 LOOP
2418 l_row_identifier := c_row_identfier_rec.ROW_IDENTIFIER;
2419 FOR c_com_atttr_groups_rec IN c_com_attr_groups(p_data_set_id, l_row_identifier)
2420 LOOP
2421
2422 -- get attribute group
2423 l_com_attr_group_type := c_com_atttr_groups_rec.ATTR_GROUP_TYPE;
2424 l_com_attr_group_name := c_com_atttr_groups_rec.ATTR_GROUP_INT_NAME;
2425 l_com_attr_group_id := c_com_atttr_groups_rec.ATTR_GROUP_ID;
2426 -- get inventory_item_id, organization_id, revision_id
2427 l_inventory_item_id := c_com_atttr_groups_rec.INVENTORY_ITEM_ID;
2428 l_revision_id := c_com_atttr_groups_rec.REVISION_ID;
2429 l_organization_id := c_com_atttr_groups_rec.ORGANIZATION_ID;
2430 l_com_attr_int_name := c_com_atttr_groups_rec.ATTR_INT_NAME;
2431 IF (c_com_atttr_groups_rec.ATTR_VALUE_STR IS NOT NULL) THEN
2432 l_value_str := c_com_atttr_groups_rec.ATTR_VALUE_STR;
2433 ELSIF (c_com_atttr_groups_rec.ATTR_VALUE_NUM IS NOT NULL) THEN
2434 l_value_num := c_com_atttr_groups_rec.ATTR_VALUE_NUM;
2435 ELSIF (c_com_atttr_groups_rec.ATTR_VALUE_DATE IS NOT NULL) THEN
2436 l_value_date := c_com_atttr_groups_rec.ATTR_VALUE_DATE;
2437 ELSE
2438 l_value := TO_CHAR(c_com_atttr_groups_rec.ATTR_DISP_VALUE);
2439 END IF;
2440 l_curr_data_element := EGO_USER_ATTR_DATA_OBJ( NULL
2441 , l_com_attr_int_name
2442 , l_value_str
2443 , l_value_num
2444 , l_value_date
2445 , l_value
2446 , NULL -- ATTR_UNIT_OF_MEASURE
2447 , NULL -- USER_ROW_IDENTIFIER
2448 );
2449 IF (l_attributes_data_table IS NULL) THEN
2450 l_attributes_data_table := EGO_USER_ATTR_DATA_TABLE();
2451 END IF;
2452 l_attributes_data_table.EXTEND();
2453 l_attributes_data_table(l_attributes_data_table.LAST) := l_curr_data_element;
2454 END LOOP; -- loop for interface records for COM item uda for row_identifer ends
2455 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2456 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', l_inventory_item_id)
2457 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', l_organization_id)
2458 ,EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID', l_revision_id));
2459 -- call package.procedure
2460 IF (EGO_COM_ATTR_VALIDATION.Is_Attribute_Group_Telco(l_com_attr_group_name,l_com_attr_group_type)) THEN
2461 EGO_COM_ATTR_VALIDATION.Validate_Attributes (
2462 p_attr_group_type => l_com_attr_group_type
2463 ,p_attr_group_name => l_com_attr_group_name
2464 ,p_attr_group_id => l_com_attr_group_id
2465 ,p_attr_name_value_pairs => l_attributes_data_table
2466 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
2467 ,x_return_status => l_telco_return_status
2468 ,x_error_messages => l_error_messages
2469 );
2470 END IF;
2471 -- get error message varray
2472 IF (l_telco_return_status = 'E') THEN
2473 FOR i IN l_error_messages.FIRST .. l_error_messages.LAST
2474 LOOP
2475 l_mark_error_record := FALSE;
2476 l_name := l_error_messages(i).NAME;
2477 l_err_value := l_error_messages(i).VALUE;
2478 l_error_row_identifier := l_row_identifier;
2479 IF (l_name = 'ATTR_GROUP_NAME') THEN
2480 l_error_attr_group_name := l_err_value;
2481 END IF;
2482 IF (l_name = 'ERROR_MESSAGE_NAME') THEN
2483 l_error_message := l_err_value;
2484 END IF;
2485 IF (l_name = 'ATTR_INT_NAME') THEN
2486 l_error_attr_name := l_err_value;
2487 l_mark_error_record := TRUE;
2488 END IF;
2489 IF (l_mark_error_record) THEN
2490 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2491 SET UAI.PROCESS_STATUS = G_COM_VALDN_FAIL
2492 WHERE UAI.DATA_SET_ID = p_data_set_id
2493 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
2494 AND UAI.ROW_IDENTIFIER = l_error_row_identifier
2495 AND UAI.ATTR_GROUP_INT_NAME = l_com_attr_group_name
2496 AND UAI.ATTR_INT_NAME = l_error_attr_name;
2497
2498 l_token_table(1).TOKEN_NAME := 'ATTR_GROUP_NAME';
2499 l_token_table(1).TOKEN_VALUE := l_error_attr_group_name;
2500 l_error_message_name := l_error_message;
2501 l_item_return_status := FND_API.G_RET_STS_ERROR;
2502 ERROR_HANDLER.Add_Error_Message(
2503 p_message_name => l_error_message_name
2504 ,p_application_id => 'EGO'
2505 ,p_token_tbl => l_token_table
2506 ,p_message_type => FND_API.G_RET_STS_ERROR
2507 ,p_row_identifier => l_error_row_identifier
2508 ,p_entity_id => G_ENTITY_ID
2509 ,p_entity_code => G_ENTITY_CODE
2510 ,p_table_name => G_TABLE_NAME
2511 );
2512 l_token_table.DELETE();
2513 END IF;
2514 END LOOP;
2515 END IF;
2516 -- do validations ends
2517 END LOOP; -- loop for interface records for row_identifier ends
2518 END IF;
2519 */
2520 -- PIM for Telco item uda validations code ends
2521
2522 --------------------------------------------------------------------------
2523 -- We fetch representative rows marked as errors and add error messages --
2524 -- explaining the point in our conversion process at which each failed; --
2525 -- note that to avoid multiple error messages for the same missing data --
2526 -- we use DISTINCT in our cursor query and thus should only get one row --
2527 -- for each ROW_IDENTIFIER (since Org Code, Item Number, Revision and --
2528 -- Catalog Group Name should be the same for a given ROW_IDENTIFIER). --
2529 --------------------------------------------------------------------------
2530 FOR error_rec IN error_case_cursor(p_data_set_id)
2531 LOOP
2532 -- there is an error in processing.
2533 RETCODE := L_CONC_RET_STS_WARNING;
2534 ------------------------------------------------------------------------------------
2535 -- Increment our debugging row counter so we can report how many rows have failed --
2536 ------------------------------------------------------------------------------------
2537 l_debug_rowcount := l_debug_rowcount + 1;
2538 --
2539 -- get the attribute group display name
2540 --
2541 IF error_rec.PROCESS_STATUS IN (G_PS_BAD_ATTR_GROUP_ID, G_PS_BAD_ATTR_GROUP_NAME,
2542 G_PS_CHG_POLICY_NOT_ALLOWED,G_PS_DATA_LEVEL_INCORRECT,
2543 G_PS_BAD_DATA_LEVEL, G_PS_INH_ATTR_FOR_SKU_NOT_UPD)
2544 THEN
2545 l_current_attr_group_obj := EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(
2546 p_attr_group_id => error_rec.ATTR_GROUP_ID
2547 ,p_application_id => 431
2548 ,p_attr_group_type => error_rec.ATTR_GROUP_TYPE --l_attr_group_type
2549 ,p_attr_group_name => error_rec.ATTR_GROUP_INT_NAME
2550 );
2551 IF l_current_attr_group_obj IS NULL THEN
2552 l_current_attr_group_name := error_rec.attr_group_int_name;
2553 ELSE
2554 l_current_attr_group_name := l_current_attr_group_obj.ATTR_GROUP_DISP_NAME;
2555 END IF;
2556 END IF;
2557 -----------------------------------------------
2558 -- 1). It may be a bad Org ID or Org Code... --
2559 -----------------------------------------------
2560 IF (error_rec.PROCESS_STATUS = G_PS_BAD_ORG_ID) THEN
2561
2562 l_token_table(1).TOKEN_NAME := 'ORG_ID';
2563 l_token_table(1).TOKEN_VALUE := error_rec.ORGANIZATION_ID;
2564
2565 l_error_message_name := 'EGO_EF_BL_ORG_ID_ERR';
2566
2567 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ORG_CODE) THEN
2568
2569 l_token_table(1).TOKEN_NAME := 'ORG_CODE';
2570 l_token_table(1).TOKEN_VALUE := error_rec.ORGANIZATION_CODE;
2571
2572 l_error_message_name := 'EGO_EF_BL_ORG_CODE_ERR';
2573
2574 ---------------------------------------------------------
2575 -- 2). ...or it may be a bad Item ID or Item Number... --
2576 ---------------------------------------------------------
2577 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ITEM_ID) THEN
2578
2579 l_token_table(1).TOKEN_NAME := 'ITEM_ID';
2580 l_token_table(1).TOKEN_VALUE := error_rec.INVENTORY_ITEM_ID;
2581
2582 l_error_message_name := 'EGO_EF_BL_INV_ITEM_ID_ERR';
2583
2584 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ITEM_NUMBER) THEN
2585
2586 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2587 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2588
2589 l_error_message_name := 'EGO_EF_BL_ITEM_NUM_ERR';
2590
2591 ---------------------------------------------------------------
2592 -- 3). ...or it may be a bad Revision ID or Revision Code... --
2593 ---------------------------------------------------------------
2594 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_REVISION_ID) THEN
2595
2596 l_token_table(1).TOKEN_NAME := 'REVISION_ID';
2597 l_token_table(1).TOKEN_VALUE := error_rec.REVISION_ID;
2598
2599 l_error_message_name := 'EGO_EF_BL_REV_ID_ERR';
2600
2601 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_REVISION_CODE) THEN
2602
2603 l_token_table(1).TOKEN_NAME := 'REVISION';
2604 l_token_table(1).TOKEN_VALUE := error_rec.REVISION;
2605
2606 l_error_message_name := 'EGO_EF_BL_REV_CODE_ERR';
2607
2608 ------------------------------------------------
2609 -- 4). ...or it may be a bad Catalog Group ID --
2610 ------------------------------------------------
2611 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID) THEN
2612
2613 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2614 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2615 l_token_table(2).TOKEN_NAME := 'ORG_CODE';
2616 l_token_table(2).TOKEN_VALUE := error_rec.ORGANIZATION_CODE;
2617
2618 l_error_message_name := 'EGO_EF_BL_CAT_GROUP_ID_ERR';
2619
2620 -------------------------------------------
2621 -- . ...or it may be a bad Attr Group ID --
2622 -------------------------------------------
2623 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_ID) THEN
2624 l_token_table(1).TOKEN_NAME := 'AG_ID';
2625 l_token_table(1).TOKEN_VALUE := error_rec.ATTR_GROUP_ID;
2626 l_token_table(2).TOKEN_NAME := 'AG_NAME';
2627 l_token_table(2).TOKEN_VALUE := l_current_attr_group_name;
2628
2629 l_error_message_name := 'EGO_EF_BAD_AG_ID';
2630
2631 ---------------------------------------------
2632 -- . ...or it may be a bad Attr Group Name --
2633 ---------------------------------------------
2634 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_NAME) THEN
2635 l_token_table(1).TOKEN_NAME := 'AG_NAME';
2636 l_token_table(1).TOKEN_VALUE := l_current_attr_group_name;
2637
2638 l_error_message_name := 'EGO_EF_BAD_AG_NAME';
2639 ------------------------------------------------
2640 -- 5)...If the incorrect data level changes
2641 ------------------------------------------------
2642 ELSIF (error_rec.PROCESS_STATUS = G_PS_DATA_LEVEL_INCORRECT) THEN
2643 l_token_table(1).TOKEN_NAME := 'AG_NAME';
2644 l_token_table(1).TOKEN_VALUE := l_current_attr_group_name;
2645
2646 l_error_message_name := 'EGO_EF_DATA_LEVEL_INCORRECT';
2647 ------------------------------------------------
2648 -- 6)...If data level is not correct
2649 ------------------------------------------------
2650 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_DATA_LEVEL) THEN
2651 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2652 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2653
2654 l_token_table(2).TOKEN_NAME := 'AG_NAME';
2655 l_token_table(2).TOKEN_VALUE := l_current_attr_group_name;
2656
2657 l_error_message_name := 'EGO_EF_BAD_DATA_LEVEL';
2658 ------------------------------------------------
2659 -- 7)...If supplier is not correct
2660 ------------------------------------------------
2661 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_SUPPLIER) THEN
2662 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2663 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2664
2665 l_token_table(2).TOKEN_NAME := 'SUPPLIER';
2666 l_token_table(2).TOKEN_VALUE := error_rec.PK1_VALUE;
2667
2668 l_error_message_name := 'EGO_EF_BAD_SUPPLIER';
2669 ------------------------------------------------
2670 -- 8)...If supplier site is not correct
2671 ------------------------------------------------
2672 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE) THEN
2673 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2674 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2675
2676 l_token_table(2).TOKEN_NAME := 'SUP';
2677 l_token_table(2).TOKEN_VALUE := error_rec.PK1_VALUE;
2678
2679 l_token_table(3).TOKEN_NAME := 'SUP_SITE';
2680 l_token_table(3).TOKEN_VALUE := error_rec.PK2_VALUE;
2681
2682 l_error_message_name := 'EGO_EF_BAD_SUPPLIER_SITE';
2683 ------------------------------------------------
2684 -- 9)...If supplier is not correct
2685 ------------------------------------------------
2686 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG) THEN
2687 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2688 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2689
2690 l_token_table(2).TOKEN_NAME := 'SUP';
2691 l_token_table(2).TOKEN_VALUE := error_rec.PK1_VALUE;
2692
2693 l_token_table(3).TOKEN_NAME := 'SUP_SITE';
2694 l_token_table(3).TOKEN_VALUE := error_rec.PK2_VALUE;
2695
2696 l_error_message_name := 'EGO_EF_BAD_SUPPLIER_SITE_ORG';
2697 ------------------------------------------------
2698 -- 10)...If value set name for variant attribute
2699 -- for style item is not correct
2700 ------------------------------------------------
2701 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_STYLE_VAR_VALUE_SET ) THEN
2702 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2703 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2704
2705 l_token_table(2).TOKEN_NAME := 'VSET_NAME';
2706 l_token_table(2).TOKEN_VALUE := error_rec.ATTR_DISP_VALUE;
2707
2708 l_error_message_name := 'EGO_BAD_VAR_VALUE_SET';
2709 -------------------------------------------------------
2710 -- 11)...If changing of variant value set is not allowed
2711 -------------------------------------------------------
2712 ELSIF (error_rec.PROCESS_STATUS = G_PS_VAR_VSET_CHG_NOT_ALLOWED ) THEN
2713 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2714 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2715
2716 l_error_message_name := 'EGO_VAR_VSET_CHG_NOT_ALLOWED';
2717 ------------------------------------------------
2718 -- 12)...If changing SKU variant attribute value
2719 -- is not allowed
2720 ------------------------------------------------
2721 ELSIF (error_rec.PROCESS_STATUS = G_PS_SKU_VAR_VALUE_NOT_UPD ) THEN
2722 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2723 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2724
2725 l_token_table(2).TOKEN_NAME := 'VALUE';
2726 l_token_table(2).TOKEN_VALUE := COALESCE(error_rec.ATTR_VALUE_STR, TO_CHAR(error_rec.ATTR_VALUE_NUM), TO_CHAR(error_rec.ATTR_VALUE_DATE), error_rec.ATTR_DISP_VALUE);
2727
2728 l_error_message_name := 'EGO_VAR_VALUE_CHG_NOT_ALLOWED';
2729 ------------------------------------------------
2730 -- 13)...Inherited attribute value for SKU
2731 -- is not allowed
2732 ------------------------------------------------
2733 ELSIF (error_rec.PROCESS_STATUS = G_PS_INH_ATTR_FOR_SKU_NOT_UPD ) THEN
2734 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2735 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2736
2737 l_token_table(2).TOKEN_NAME := 'AG_NAME';
2738 l_token_table(2).TOKEN_VALUE := l_current_attr_group_name;
2739
2740 l_error_message_name := 'EGO_INH_ATTR_FOR_SKU_NOT_UPD';
2741
2742 ----------------------------------------------------
2743 -- 14). ...or else the row is under Change control --
2744 ----------------------------------------------------
2745 ELSIF (error_rec.PROCESS_STATUS = G_PS_CHG_POLICY_NOT_ALLOWED ) THEN
2746 --------------------------------------------------------------
2747 -- We fetch the Attr Group metadata object (which is cached --
2748 -- by EGO_USER_ATTRS_COMMON_PVT after it's fetched once) --
2749 --------------------------------------------------------------
2750 -- decide the message based upon
2751 -- item /item revision
2752 -- change order required OR changes not allowed
2753 l_token_table(1).TOKEN_NAME := 'ATTR_GROUP_NAME';
2754 l_token_table(1).TOKEN_VALUE := l_current_attr_group_name;
2755 l_token_table(2).TOKEN_NAME := 'ITEM_NUMBER';
2756 l_token_table(2).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2757 ----------------------------------------------------------------
2758 -- If we've already fetched the Catalog Group name, we reuse --
2759 -- it; otherwise, we fetch it and store it in our local table --
2760 ----------------------------------------------------------------
2761 IF (l_catalog_category_names_table.EXISTS(error_rec.prog_int_num1)) THEN
2762 l_current_category_name := l_catalog_category_names_table(error_rec.prog_int_num1);--Bugfix:5343821
2763 ELSE
2764 SELECT concatenated_segments
2765 INTO l_current_category_name
2766 FROM MTL_ITEM_CATALOG_GROUPS_KFV
2767 WHERE ITEM_CATALOG_GROUP_ID = error_rec.prog_int_num1;
2768 l_catalog_category_names_table(error_rec.prog_int_num1) := l_current_category_name;
2769 END IF;
2770 l_token_table(3).TOKEN_NAME := 'CATALOG_CATEGORY_NAME';
2771 l_token_table(3).TOKEN_VALUE := l_current_category_name;
2772 SELECT PEP.NAME
2773 INTO l_current_life_cycle
2774 FROM PA_EGO_LIFECYCLES_V PEP
2775 WHERE PEP.PROJ_ELEMENT_ID = error_rec.prog_int_num2;
2776 l_token_table(4).TOKEN_NAME := 'LIFE_CYCLE';
2777 l_token_table(4).TOKEN_VALUE := l_current_life_cycle;
2778
2779 IF error_rec.revision_id IS NULL THEN
2780 -- error is in context of item.
2781 --IF (error_rec.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED) THEN
2782 --l_error_message_name := 'EGO_EF_BL_ITM_CO_REQD_ERR';
2783 -- ELSE
2784 l_error_message_name := 'EGO_EF_BL_ITM_NOT_ALLOW_ERR';
2785 --END IF;
2786 -----------------------------------------------------------------
2787 -- Since it's not as convenient, we don't have a local storing --
2788 -- scheme for Phase name; but we can create one if necessary --
2789 -----------------------------------------------------------------
2790 SELECT PEP.NAME
2791 INTO l_current_phase_name
2792 FROM PA_EGO_PHASES_V PEP
2793 WHERE PEP.PROJ_ELEMENT_ID = error_rec.prog_int_num3;
2794 l_token_table(5).TOKEN_NAME := 'PHASE';
2795 l_token_table(5).TOKEN_VALUE := l_current_phase_name;
2796 ELSE
2797 -- error is in context of item revision.
2798 --IF (error_rec.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED) THEN
2799 --l_error_message_name := 'EGO_EF_BL_REV_CO_REQD_ERR';
2800 --ELSE
2801 l_error_message_name := 'EGO_EF_BL_REV_NOT_ALLOW_ERR';
2802 --END IF;
2803 -----------------------------------------------------------------
2804 -- Since it's not as convenient, we don't have a local storing --
2805 -- scheme for Phase name; but we can create one if necessary --
2806 -----------------------------------------------------------------
2807 SELECT PEP.NAME
2808 INTO l_current_phase_name
2809 FROM PA_EGO_PHASES_V PEP
2810 WHERE PEP.PROJ_ELEMENT_ID = error_rec.prog_int_num3;
2811 l_token_table(5).TOKEN_NAME := 'PHASE';
2812 l_token_table(5).TOKEN_VALUE := l_current_phase_name;
2813 l_token_table(6).TOKEN_NAME := 'ITEM_REV';
2814 l_token_table(6).TOKEN_VALUE := error_rec.REVISION;
2815 END IF;
2816 END IF;
2817
2818 l_item_return_status := FND_API.G_RET_STS_ERROR;
2819 ERROR_HANDLER.Add_Error_Message(
2820 p_message_name => l_error_message_name
2821 ,p_application_id => 'EGO'
2822 ,p_token_tbl => l_token_table
2823 ,p_message_type => FND_API.G_RET_STS_ERROR
2824 ,p_row_identifier => error_rec.TRANSACTION_ID
2825 ,p_entity_id => G_ENTITY_ID
2826 ,p_entity_code => G_ENTITY_CODE
2827 ,p_table_name => G_TABLE_NAME
2828 );
2829 l_token_table.DELETE();
2830
2831 END LOOP;
2832
2833 IF (p_debug_level > 0) THEN
2834 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Reported errors with '||l_debug_rowcount||' interface table rows', 0);
2835 END IF;
2836
2837 --=====================================--
2838 -- LOOP PROCESSING OF STILL-VALID ROWS --
2839 --=====================================--
2840 IF (p_debug_level > 0) THEN
2841 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting loop processing of valid rows', 0);
2842 END IF;
2843
2844 ---------------------------------------------------------------
2845 -- ...and then use it to mark as errors all rows in the list --
2846 -- (note that we have to use dynamic SQL because 1). static --
2847 -- SQL treats the failed Row ID list as a string instead of --
2848 -- a list of numbers, and 2). bulk-binding would cause us to --
2849 -- execute one SQL statement per failed Row ID. Dynamic SQL --
2850 -- only executes one SQL statement for a given call to our --
2851 -- concurrent program--so the fact that our failed Row IDs --
2852 -- aren't passed as a bind variable doesn't matter, because --
2853 -- the statement won't get parsed more than once anyway). --
2854 ---------------------------------------------------------------
2855
2856 l_dynamic_sql := ' UPDATE EGO_ITM_USR_ATTR_INTRFC'||
2857 ' SET PROCESS_STATUS = '||G_PS_GENERIC_ERROR||
2858 ' WHERE DATA_SET_ID = :1'||
2859 ' AND (PROCESS_STATUS IN ('||G_PS_BAD_ORG_ID||', '||
2860 G_PS_BAD_ORG_CODE||', '||
2861 G_PS_BAD_ITEM_ID||', '||
2862 G_PS_BAD_ITEM_NUMBER||', '||
2863 G_PS_BAD_REVISION_ID||', '||
2864 G_PS_BAD_REVISION_CODE||', '||
2865 G_PS_BAD_CATALOG_GROUP_ID||', '||
2866 G_PS_BAD_ATTR_GROUP_ID||', '||
2867 G_PS_BAD_ATTR_GROUP_NAME||', '||
2868 G_PS_CHG_POLICY_NOT_ALLOWED||', '||
2869 G_PS_BAD_DATA_LEVEL||', '||
2870 G_PS_BAD_SUPPLIER||', '||
2871 G_PS_BAD_SUPPLIER_SITE||', '||
2872 G_PS_BAD_SUPPLIER_SITE_ORG||', '||
2873 G_PS_BAD_STYLE_VAR_VALUE_SET||', '||
2874 G_PS_VAR_VSET_CHG_NOT_ALLOWED||', '||
2875 G_PS_SKU_VAR_VALUE_NOT_UPD||', '||
2876 G_PS_INH_ATTR_FOR_SKU_NOT_UPD||', '||
2877 G_PS_DATA_LEVEL_INCORRECT||' ) )';
2878
2879 EXECUTE IMMEDIATE l_dynamic_sql USING p_data_set_id;
2880 END IF; -- End of l_return = FALSE; -- Bug 10263673
2881
2882 -- Telco Item UDA validation --
2883 /*IF (profile_value = 'Y') THEN
2884
2885 l_dynamic_sqlt := ' UPDATE EGO_ITM_USR_ATTR_INTRFC'||
2886 ' SET PROCESS_STATUS = '||G_PS_GENERIC_ERROR||
2887 ' WHERE DATA_SET_ID = :1'||
2888 ' AND (PROCESS_STATUS IN ('||G_COM_VALDN_FAIL||') )';
2889
2890 EXECUTE IMMEDIATE l_dynamic_sqlt USING p_data_set_id;
2891
2892 END IF; */
2893
2894 -- Bug 10263673 : Start
2895 /* Doing the TEMPLATE APPLICATION for UDAs before calling EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data,
2896 the below peice of code will be executed only in import flow.
2897 This code will be executed always in import flow.
2898 */
2899
2900 IF (INV_EGO_REVISION_VALIDATE.Get_Process_Control_HTML_API() = 'IMPORT')
2901 THEN
2902 BEGIN
2903 SELECT NVL(ENABLED_FOR_DATA_POOL, 'N')
2904 INTO l_enabled_for_data_pool
2905 FROM EGO_IMPORT_OPTION_SETS
2906 WHERE BATCH_ID = p_data_set_id;
2907 EXCEPTION WHEN OTHERS THEN
2908 l_enabled_for_data_pool := 'N';
2909 END;
2910
2911 BEGIN
2912 SELECT 'Y' INTO l_copy_option_exists
2913 FROM EGO_IMPORT_COPY_OPTIONS
2914 WHERE BATCH_ID = p_data_set_id
2915 AND ROWNUM = 1;
2916 EXCEPTION
2917 WHEN NO_DATA_FOUND THEN
2918 l_copy_option_exists := 'N';
2919 END;
2920
2921 IF l_enabled_for_data_pool = 'N' THEN
2922 -- call process copy options for UDAs
2923 -- this will do the Apply Multiple Templates
2924 -- and copying of attributes if any
2925 EGO_IMPORT_UTIL_PVT.Process_Copy_Options_For_UDAs(retcode => l_retcode,
2926 errbuf => l_errbuf,
2927 p_batch_id => p_data_set_id,
2928 p_copy_options_exist => l_copy_option_exists);
2929
2930 EGO_USER_ATTRS_DATA_PVT.Debug_Msg(' Done Process_Copy_Options_For_UDAs with l_retcode, l_errbuf='||l_retcode||','||l_errbuf, 1);
2931
2932 IF NVL(l_retcode, '0') > 0 THEN
2933 RETCODE := l_retcode;
2934 ERRBUF := l_errbuf;
2935 END IF;
2936
2937 IF RETCODE = '2' THEN
2938 RETURN;
2939 END IF;
2940 END IF; -- End of l_enabled_for_data_pool = 'N'
2941
2942 EGO_IMPORT_UTIL_PVT.INSERT_FUN_GEN_SETUP_UDAS( p_data_set_id );
2943 END IF;
2944
2945 /* If no data exists in interface table before template application.
2946 Check if data exists after template application, If no data exists then do not progress.
2947 */
2948 IF (l_return = TRUE) THEN
2949 SELECT
2950 COUNT(DATA_SET_ID)
2951 INTO l_rec_count
2952 FROM EGO_ITM_USR_ATTR_INTRFC intf
2953 WHERE DATA_SET_ID = p_data_set_id
2954 AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS IN (G_PS_TO_BE_PROCESSED, G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS) );
2955
2956 IF (l_rec_count = 0) THEN
2957 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Returning because no data exists in interface table to process', 0);
2958 RETURN;
2959 END IF;
2960 END IF; -- End of l_return = TRUE -- Bug 10263673
2961 -- Bug 10263673 : End
2962
2963 -- Call the EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data API instead of
2964 -- EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
2965
2966 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting User Attributes Bulk Load', 1);
2967
2968 IF p_validate_only = FND_API.G_TRUE AND p_ignore_security_for_validate = FND_API.G_FALSE THEN
2969 l_privilege_predicate_api_name := 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Item_Security_Predicate';
2970 ELSE
2971 l_privilege_predicate_api_name := NULL;
2972 END IF;
2973
2974 -- creating default privileges
2975 l_default_dl_view_priv_list := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2976 l_default_dl_edit_priv_list := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2977 FOR i IN c_data_levels_for_sec LOOP
2978 IF i.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND i.DATA_LEVEL_NAME IN ('ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG') THEN
2979 l_default_dl_view_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(i.DATA_LEVEL_ID, 'EGO_VIEW_ITEM_SUP_ASSIGN');
2980 l_default_dl_edit_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(i.DATA_LEVEL_ID, 'EGO_EDIT_ITEM_SUP_ASSIGN');
2981 ELSE
2982 l_default_dl_view_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(i.DATA_LEVEL_ID, 'EGO_VIEW_ITEM');
2983 l_default_dl_edit_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(i.DATA_LEVEL_ID, 'EGO_EDIT_ITEM');
2984 END IF;
2985 l_default_dl_view_priv_list.EXTEND;
2986 l_default_dl_view_priv_list(l_default_dl_view_priv_list.COUNT) := l_default_dl_view_priv_obj;
2987
2988 l_default_dl_edit_priv_list.EXTEND;
2989 l_default_dl_edit_priv_list(l_default_dl_edit_priv_list.COUNT) := l_default_dl_edit_priv_obj;
2990 END LOOP;
2991
2992 /* Fix for bug#9660659 - Start*/
2993 -- Bug 13434831 : Start (Performance Changes)
2994 l_item_mgmt_count := 0;
2995 l_item_gtin_count := 0;
2996 l_item_gtin_multi_count := 0;
2997
2998 for i in (SELECT Count(1) as ag_type_count,
2999 attr_group_type
3000 FROM EGO_ITM_USR_ATTR_INTRFC
3001 WHERE DATA_SET_ID = p_data_set_id
3002 AND attr_group_type IN ('EGO_ITEM_GTIN_MULTI_ATTRS', 'EGO_ITEM_GTIN_ATTRS', 'EGO_ITEMMGMT_GROUP')
3003 GROUP BY attr_Group_type)
3004 loop
3005 if (i.attr_group_type = 'EGO_ITEMMGMT_GROUP') then
3006 l_item_mgmt_count := i.ag_type_count;
3007 elsif (i.attr_group_type = 'EGO_ITEM_GTIN_ATTRS') then
3008 l_item_gtin_count := i.ag_type_count;
3009 elsif (i.attr_group_type = 'EGO_ITEM_GTIN_MULTI_ATTRS') then
3010 l_item_gtin_multi_count := i.ag_type_count;
3011 end if;
3012 end loop;
3013
3014 -- Commenting the below query and getting the counts for 3 AG types using single query as above.
3015 /*
3016 SELECT Count(1)
3017 INTO l_item_mgmt_count
3018 FROM EGO_ITM_USR_ATTR_INTRFC
3019 WHERE data_set_id = p_data_set_id
3020 AND attr_group_type = 'EGO_ITEMMGMT_GROUP'
3021 AND ROWNUM = 1;
3022 */
3023 -- Bug 13434831 : End (Performance Changes)
3024
3025 IF(l_item_mgmt_count > 0) THEN -- Bug 13434831
3026 /* Fix for bug#9660659 - End */
3027
3028 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
3029 p_api_version => G_API_VERSION --IN NUMBER
3030 ,p_application_id => 431 --IN NUMBER
3031 ,p_attr_group_type => 'EGO_ITEMMGMT_GROUP' --IN VARCHAR2
3032 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3033 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3034 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3035 ,p_data_set_id => p_data_set_id --IN NUMBER
3036 ,p_entity_id => G_ENTITY_ID --IN NUMBER
3037 ,p_entity_index => l_entity_index_counter --IN NUMBER
3038 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3039 ,p_debug_level => p_debug_level --IN NUMBER
3040 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
3041 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
3042 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
3043 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
3044 ,p_commit => p_commit -- FND_API.G_TRUE --IN VARCHAR2/* Added to fix Bug#7422423*/
3045 ,p_default_dl_view_priv_list => l_default_dl_view_priv_list
3046 ,p_default_dl_edit_priv_list => l_default_dl_edit_priv_list
3047 ,p_privilege_predicate_api_name => l_privilege_predicate_api_name --IN VARCHAR2
3048 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
3049 ,p_validate => TRUE
3050 ,p_do_dml => FALSE
3051 ,p_do_req_def_valiadtion => TRUE -- Fix for bug#9660659 FALSE --we will do this validation after the template is applied
3052 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
3053 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
3054 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
3055 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
3056 );
3057
3058 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3059 ERRBUF := l_msg_data;
3060 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
3061 RAISE G_UNHANDLED_EXCEPTION;
3062 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
3063 RETCODE := L_CONC_RET_STS_WARNING;
3064 END IF;
3065
3066 END IF; -- end of IF (l_item_mgmt_count > 0) /* Fix for bug#9660659 */
3067
3068 ---------------------------------------------------------------------------------
3069 -- Mark as errors all rows that share the same logical Attribute Group --
3070 -- If attribute value does not belong to a valid value set i.e. in case of --
3071 -- variant attributes, user can associate a child value set as well. So this --
3072 -- validation will check that value belongs to child value set also,else error --
3073 -- --
3074 -- This validation needs to be done after the normal UDA validations are over --
3075 ---------------------------------------------------------------------------------
3076
3077 UPDATE EGO_ITM_USR_ATTR_INTRFC
3078 SET PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE
3079 WHERE DATA_SET_ID = p_data_set_id
3080 AND PROCESS_STATUS = G_PS_IN_PROCESS
3081 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
3082 FROM
3083 EGO_FND_DSC_FLX_CTX_EXT AG_EXT,
3084 EGO_ITM_USR_ATTR_INTRFC INTF,
3085 EGO_STYLE_VARIANT_ATTR_VS SVA,
3086 EGO_FND_DF_COL_USGS_EXT ATTR,
3087 FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
3088 MTL_SYSTEM_ITEMS_INTERFACE MSII
3089 WHERE INTF.DATA_SET_ID = p_data_set_id
3090 AND INTF.PROCESS_STATUS = G_PS_IN_PROCESS
3091 AND INTF.ATTR_GROUP_TYPE = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
3092 AND INTF.ATTR_GROUP_INT_NAME = ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE
3093 AND ATTR.APPLICATION_ID = 431
3094 AND ATTR.APPLICATION_ID = FL_COL.APPLICATION_ID
3095 AND ATTR.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
3096 AND ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
3097 AND ATTR.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
3098 AND AG_EXT.APPLICATION_ID = ATTR.APPLICATION_ID
3099 AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
3100 AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE
3101 AND Nvl(AG_EXT.VARIANT, 'N') = 'Y'
3102 AND INTF.ATTR_INT_NAME = FL_COL.END_USER_COLUMN_NAME
3103 AND SVA.ATTRIBUTE_ID = ATTR.ATTR_ID
3104 AND SVA.INVENTORY_ITEM_ID = MSII.STYLE_ITEM_ID
3105 AND SVA.VALUE_SET_ID <> FL_COL.FLEX_VALUE_SET_ID
3106 AND INTF.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
3107 AND INTF.ORGANIZATION_ID = MSII.ORGANIZATION_ID
3108 AND (CASE WHEN ATTR.DATA_TYPE IN ('A', 'C') THEN INTF.ATTR_VALUE_STR
3109 WHEN ATTR.DATA_TYPE = 'N' THEN TO_CHAR(INTF.ATTR_VALUE_NUM)
3110 WHEN ATTR.DATA_TYPE IN ('X', 'Y') THEN TO_CHAR(INTF.ATTR_VALUE_DATE)
3111 ELSE NULL
3112 END) NOT IN ( SELECT val.FLEX_VALUE
3113 FROM
3114 FND_FLEX_VALUES_VL val,
3115 EGO_VS_VALUES_DISP_ORDER disp_order,
3116 EGO_VALUE_SET_EXT ext
3117 WHERE val.FLEX_VALUE_ID = disp_order.VALUE_SET_VALUE_ID
3118 AND disp_order.VALUE_SET_ID = SVA.VALUE_SET_ID
3119 AND val.FLEX_VALUE_SET_ID = ext.PARENT_VALUE_SET_ID
3120 AND ext.VALUE_SET_ID = disp_order.VALUE_SET_ID
3121 AND Nvl(val.ENABLED_FLAG,'Y') = 'Y'
3122 AND Nvl(val.START_DATE_ACTIVE, SYSDATE) <= SYSDATE
3123 AND Nvl(val.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
3124 )
3125 );
3126
3127
3128
3129
3130 ---------------------------------------------------------------------------------
3131 -- Adding validation errors for Style/SKU related validations --
3132 ---------------------------------------------------------------------------------
3133 FOR i IN (SELECT /*+ INDEX(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N3) */ /* Bug 9678667 */
3134 PROCESS_STATUS, TRANSACTION_ID, ATTR_VALUE_STR, ATTR_VALUE_NUM, ATTR_VALUE_DATE, ATTR_DISP_VALUE, ITEM_NUMBER
3135 FROM EGO_ITM_USR_ATTR_INTRFC
3136 WHERE DATA_SET_ID = p_data_set_id
3137 AND PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE
3138 )
3139 LOOP
3140 RETCODE := L_CONC_RET_STS_WARNING;
3141 IF (i.PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE ) THEN
3142 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
3143 l_token_table(1).TOKEN_VALUE := i.ITEM_NUMBER;
3144
3145 l_token_table(2).TOKEN_NAME := 'VALUE';
3146 l_token_table(2).TOKEN_VALUE := COALESCE(i.ATTR_VALUE_STR, TO_CHAR(i.ATTR_VALUE_NUM), TO_CHAR(i.ATTR_VALUE_DATE));
3147
3148 l_error_message_name := 'EGO_BAD_VAR_VALUE';
3149 END IF;
3150
3151 ERROR_HANDLER.Add_Error_Message(
3152 p_message_name => l_error_message_name
3153 ,p_application_id => 'EGO'
3154 ,p_token_tbl => l_token_table
3155 ,p_message_type => FND_API.G_RET_STS_ERROR
3156 ,p_row_identifier => i.TRANSACTION_ID
3157 ,p_entity_id => G_ENTITY_ID
3158 ,p_entity_code => G_ENTITY_CODE
3159 ,p_table_name => G_TABLE_NAME
3160 );
3161 l_token_table.DELETE();
3162 END LOOP;
3163
3164
3165 ---------------------------------------------------
3166 -- Marking all these rows to process_status = 3 --
3167 ---------------------------------------------------
3168 UPDATE EGO_ITM_USR_ATTR_INTRFC
3169 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
3170 WHERE DATA_SET_ID = p_data_set_id
3171 AND PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE;
3172
3173
3174
3175 ----------------------------------------------------
3176 -- Processing data for GDSN Attribute group types --
3177 ----------------------------------------------------
3178
3179 /* Fix for bug#9660659 - Start*/
3180 -- Bug Bug 13434831 : Start
3181 -- Commenting the below query as we got count for AG type EGO_ITEM_GTIN_ATTRS already
3182 /*
3183 SELECT Count(1)
3184 INTO l_item_gtin_count
3185 FROM EGO_ITM_USR_ATTR_INTRFC
3186 WHERE data_set_id = p_data_set_id
3187 AND attr_group_type = 'EGO_ITEM_GTIN_ATTRS'
3188 AND ROWNUM = 1;
3189 */
3190 -- Bug 13434831 : End
3191
3192 IF(l_item_gtin_count > 0) THEN -- Bug 13434831
3193 /* Fix for bug#9660659 - End */
3194
3195 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
3196 p_api_version => G_API_VERSION --IN NUMBER
3197 ,p_application_id => 431 --IN NUMBER
3198 ,p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS' --IN VARCHAR2
3199 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3200 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3201 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3202 ,p_data_set_id => p_data_set_id --IN NUMBER
3203 ,p_entity_id => G_ENTITY_ID --IN NUMBER
3204 ,p_entity_index => l_entity_index_counter --IN NUMBER
3205 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3206 ,p_debug_level => p_debug_level --IN NUMBER
3207 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
3208 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
3209 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
3210 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
3211 ,p_commit => p_commit -- bug 10060587 FND_API.G_TRUE --IN VARCHAR2
3212 ,p_default_view_privilege => 'EGO_VIEW_ITEM' --IN VARCHAR2
3213 ,p_default_edit_privilege => 'EGO_EDIT_ITEM' --IN VARCHAR2
3214 ,p_privilege_predicate_api_name => l_privilege_predicate_api_name --IN VARCHAR2
3215 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
3216 ,p_validate => TRUE
3217 ,p_do_dml => FALSE
3218 ,p_do_req_def_valiadtion => FALSE --we will do this validation after the template is applied
3219 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
3220 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
3221 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
3222 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
3223 );
3224
3225 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3226 ERRBUF := l_msg_data;
3227 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
3228 RAISE G_UNHANDLED_EXCEPTION;
3229 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
3230 RETCODE := L_CONC_RET_STS_WARNING;
3231 END IF;
3232 END IF; -- end of IF (l_item_gtin_count > 0) /* Fix for bug#9660659 */
3233
3234
3235 /* Fix for bug#9660659 - Start*/
3236 -- Bug 13434831 : Start
3237 -- Commenting the below query as we got count for AG type EGO_ITEM_GTIN_MULTI_ATTRS already
3238 /*
3239 SELECT Count(1)
3240 INTO l_item_gtin_multi_count
3241 FROM EGO_ITM_USR_ATTR_INTRFC
3242 WHERE data_set_id = p_data_set_id
3243 AND attr_group_type = 'EGO_ITEM_GTIN_MULTI_ATTRS'
3244 AND ROWNUM = 1;
3245 */
3246 -- Bug 13434831 : End
3247
3248 IF(l_item_gtin_multi_count > 0) THEN -- Bug 13434831
3249 /* Fix for bug#9660659 - End */
3250
3251 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
3252 p_api_version => G_API_VERSION --IN NUMBER
3253 ,p_application_id => 431 --IN NUMBER
3254 ,p_attr_group_type => 'EGO_ITEM_GTIN_MULTI_ATTRS' --IN VARCHAR2
3255 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3256 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3257 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3258 ,p_data_set_id => p_data_set_id --IN NUMBER
3259 ,p_entity_id => G_ENTITY_ID --IN NUMBER
3260 ,p_entity_index => l_entity_index_counter --IN NUMBER
3261 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3262 ,p_debug_level => p_debug_level --IN NUMBER
3263 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
3264 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
3265 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
3266 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
3267 ,p_commit => p_commit -- bug 10060587 FND_API.G_TRUE --IN VARCHAR2
3268 ,p_default_view_privilege => 'EGO_VIEW_ITEM' --IN VARCHAR2
3269 ,p_default_edit_privilege => 'EGO_EDIT_ITEM' --IN VARCHAR2
3270 ,p_privilege_predicate_api_name => l_privilege_predicate_api_name --IN VARCHAR2
3271 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
3272 ,p_validate => TRUE
3273 ,p_do_dml => FALSE
3274 ,p_do_req_def_valiadtion => FALSE --we will do this validation after the template is applied
3275 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
3276 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
3277 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
3278 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
3279 );
3280
3281 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3282 ERRBUF := l_msg_data;
3283 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
3284 RAISE G_UNHANDLED_EXCEPTION;
3285 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
3286 RETCODE := L_CONC_RET_STS_WARNING;
3287 END IF;
3288 END IF; -- end of IF (l_item_gtin_multi_count > 0) /* Fix for bug#9660659 */
3289
3290
3291 IF p_validate_only = FND_API.G_FALSE THEN
3292 ---------------------------------------------------------------------
3293 -- Calling the API to Mark all such rows which have the same data --
3294 -- as we have for the attribute in the production tables. --
3295 ---------------------------------------------------------------------
3296
3297 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Calling EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows for EGO_ITEMMGMT_GROUP ', 1);
3298 /** bug 14151154 passing p_commit flag in cal to procedure EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows */
3299 EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows (
3300 p_api_version =>1.0
3301 ,p_application_id =>431
3302 ,p_attr_group_type =>'EGO_ITEMMGMT_GROUP'
3303 ,p_object_name =>'EGO_ITEM'
3304 ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
3305 ,p_data_set_id =>p_data_set_id
3306 ,p_new_status =>4
3307 ,p_commit =>p_commit
3308 ,x_return_status =>l_return_status
3309 ,x_msg_data =>l_msg_data
3310 );
3311 EGO_USER_ATTRS_DATA_PVT.Debug_Msg(' ... Completed EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows Return Status-'||l_return_status||' '||l_msg_data, 1);
3312
3313 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3314 ERRBUF := l_msg_data;
3315 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
3316 ERROR_HANDLER.Add_Error_Message(
3317 p_message_text => l_msg_data
3318 ,p_application_id => 'EGO'
3319 ,p_message_type => FND_API.G_RET_STS_ERROR
3320 ,p_row_identifier => l_err_reporting_transaction_id
3321 ,p_entity_id => G_ENTITY_ID
3322 ,p_entity_code => G_ENTITY_CODE
3323 ,p_table_name => G_TABLE_NAME
3324 );
3325 RAISE G_UNHANDLED_EXCEPTION;
3326 ELSIF l_return_status = G_FND_RET_STS_WARNING THEN
3327 RETCODE := L_CONC_RET_STS_WARNING;
3328 END IF;
3329
3330
3331
3332
3333 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Calling EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows for EGO_ITEM_GTIN_ATTRS ', 1);
3334 /** bug 14151154 passing p_commit flag in cal to procedure EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows */
3335 EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows (
3336 p_api_version =>1.0
3337 ,p_application_id =>431
3338 ,p_attr_group_type =>'EGO_ITEM_GTIN_ATTRS'
3339 ,p_object_name =>'EGO_ITEM'
3340 ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
3341 ,p_data_set_id =>p_data_set_id
3342 ,p_new_status =>4
3343 ,p_commit =>p_commit
3344 ,x_return_status =>l_return_status
3345 ,x_msg_data =>l_msg_data
3346 );
3347 EGO_USER_ATTRS_DATA_PVT.Debug_Msg(' ... Completed EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows Return Status-'||l_return_status||' '||l_msg_data, 1);
3348
3349 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3350 ERRBUF := l_msg_data;
3351 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
3352 ERROR_HANDLER.Add_Error_Message(
3353 p_message_text => l_msg_data
3354 ,p_application_id => 'EGO'
3355 ,p_message_type => FND_API.G_RET_STS_ERROR
3356 ,p_row_identifier => l_err_reporting_transaction_id
3357 ,p_entity_id => G_ENTITY_ID
3358 ,p_entity_code => G_ENTITY_CODE
3359 ,p_table_name => G_TABLE_NAME
3360 );
3361 RAISE G_UNHANDLED_EXCEPTION;
3362 ELSIF l_return_status = G_FND_RET_STS_WARNING THEN
3363 RETCODE := L_CONC_RET_STS_WARNING;
3364 END IF;
3365
3366 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Calling EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows for EGO_ITEM_GTIN_MULTI_ATTRS ', 1);
3367 /** bug 14151154 passing p_commit flag in cal to procedure EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows */
3368 EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows (
3369 p_api_version =>1.0
3370 ,p_application_id =>431
3371 ,p_attr_group_type =>'EGO_ITEM_GTIN_MULTI_ATTRS'
3372 ,p_object_name =>'EGO_ITEM'
3373 ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
3374 ,p_data_set_id =>p_data_set_id
3375 ,p_new_status =>4
3376 ,p_commit =>p_commit
3377 ,x_return_status =>l_return_status
3378 ,x_msg_data =>l_msg_data
3379 );
3380 EGO_USER_ATTRS_DATA_PVT.Debug_Msg(' ... Completed EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows Return Status-'||l_return_status||' '||l_msg_data, 1);
3381
3382 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3383 ERRBUF := l_msg_data;
3384 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
3385 ERROR_HANDLER.Add_Error_Message(
3386 p_message_text => l_msg_data
3387 ,p_application_id => 'EGO'
3388 ,p_message_type => FND_API.G_RET_STS_ERROR
3389 ,p_row_identifier => l_err_reporting_transaction_id
3390 ,p_entity_id => G_ENTITY_ID
3391 ,p_entity_code => G_ENTITY_CODE
3392 ,p_table_name => G_TABLE_NAME
3393 );
3394 RAISE G_UNHANDLED_EXCEPTION;
3395 ELSIF l_return_status = G_FND_RET_STS_WARNING THEN
3396 RETCODE := L_CONC_RET_STS_WARNING;
3397 END IF;
3398 END IF; --IF p_validate_only = FND_API.G_FALSE THEN
3399
3400 ---------------------------------------------------------------------------------------
3401 -- Mark all rows to satus 5 that share the same logical Attribute Group row --
3402 -- with any rows for which the Change Policy is defined as CHANGE_ORDER_REQUIRED --
3403 -- (we do not process such rows; they must be bulkloaded through Change Management); --
3404 -- the exception to this is rows for pending Items, which we still processed --
3405 -- The user attrs API would not do the DML for these --
3406 ---------------------------------------------------------------------------------------
3407 IF (p_debug_level > 0) THEN
3408 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Change Policy conversion/erroring', 0);
3409 END IF;
3410
3411 BEGIN
3412 --------------------------------------------------------------------------------------
3413 -- Bug#4679902 (If the user wants to have Change Order, Change ALLOWED attributes
3414 -- will also be forced to undergo Change Order)
3415 --------------------------------------------------------------------------------------
3416 l_add_all_to_cm := EGO_IMPORT_PVT.getAddAllToChangeFlag(p_batch_id => p_data_set_id);
3417 EGO_USER_ATTRS_DATA_PVT.Debug_Msg(' Add all to Change flag is '||l_add_all_to_cm, 0);
3418 IF( l_add_all_to_cm = 'Y' ) THEN
3419 /*UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3420 SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
3421 WHERE UAI.DATA_SET_ID = p_data_set_id
3422 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3423 AND UAI.PROG_INT_CHAR2 = 'N' OR UAI.DATA_LEVEL_ID = 43106;--BugFix:6315828(for revisions even in case revision is created in the batch
3424 --the attrs should go through the CO int his case.*/
3425 -- Bug 9705689 adding condition to the filter for revision level updated item
3426 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3427 SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
3428 WHERE UAI.DATA_SET_ID = p_data_set_id
3429 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3430 AND UAI.PROG_INT_CHAR2 = 'N';
3431
3432 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3433 SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
3434 WHERE UAI.DATA_LEVEL_ID = 43106
3435 AND UAI.DATA_SET_ID = p_data_set_id
3436 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3437 AND EXISTS
3438 (
3439 SELECT 1
3440 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3441 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
3442 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
3443 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
3444 AND MSII.PROCESS_FLAG IN (1, 7)
3445 AND MSII.TRANSACTION_TYPE = 'UPDATE'
3446 );
3447
3448 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3449 SET UAI.TRANSACTION_TYPE = 'CREATE'
3450 WHERE UAI.DATA_LEVEL_ID = 43106
3451 AND UAI.DATA_SET_ID = p_data_set_id
3452 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3453 AND UAI.TRANSACTION_TYPE='SYNC'
3454 AND EXISTS
3455 (
3456 SELECT 1
3457 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
3458 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
3459 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
3460 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
3461 AND MSII.PROCESS_FLAG = 1
3462 AND MSII.TRANSACTION_TYPE = 'CREATE'
3463 );
3464 -- end of bug 9705689 code changes
3465
3466 ELSE
3467 l_policy_check_name := 'CHANGE_ORDER_REQUIRED';
3468 EXECUTE IMMEDIATE l_policy_check_sql USING G_PS_CHG_POLICY_CO_REQUIRED,
3469 p_data_set_id,
3470 G_PS_IN_PROCESS,
3471 p_data_set_id,
3472 G_PS_IN_PROCESS,
3473 l_policy_check_name;
3474 END IF;
3475 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After CO REQD policy');
3476
3477 EXCEPTION
3478 WHEN OTHERS THEN
3479 NULL;
3480 END;
3481 /* Dont need this right now.
3482 --------------------------------------------------------------
3483 -- Loop through all the distinct catalog group id's in the --
3484 -- current data set and mark the rows for catalog's which --
3485 -- have a NIR required and leave them in status 5 for CM --
3486 --------------------------------------------------------------
3487 FOR catalog_groups IN distinct_catalaog_groups(p_data_set_id)
3488 LOOP
3489 IF (INVIDIT3.CHECK_NPR_CATALOG(catalog_groups.ITEM_CATALOG_GROUP_ID)) THEN
3490 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3491 SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
3492 WHERE UAI.DATA_SET_ID = p_data_set_id
3493 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3494 AND UAI.ITEM_CATALOG_GROUP_ID = catalog_groups.ITEM_CATALOG_GROUP_ID;
3495 END IF;
3496 END LOOP;
3497 */
3498
3499 ------------------------------------------------------------
3500
3501
3502 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Before calling the GTIN validation API', 0);
3503
3504 EGO_GTIN_ATTRS_PVT.VALIDATE_INTF_ROWS(
3505 p_data_set_id => p_data_set_id
3506 ,p_entity_id => G_ENTITY_ID
3507 ,p_entity_code => G_ENTITY_CODE
3508 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
3509 ,x_return_status => l_gtinval_ret_code
3510 );
3511 IF (l_gtinval_ret_code = 'U') THEN
3512 RETCODE := L_CONC_RET_STS_ERROR;
3513 ELSIF (l_gtinval_ret_code = 'E') THEN
3514 RETCODE := L_CONC_RET_STS_WARNING;
3515 END IF;
3516
3517 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('After calling the GTIN validation API', 0);
3518
3519 ------------------------------------------------
3520 -- Here we call the API in DML only mode for --
3521 -- all the attr group types --
3522 ------------------------------------------------
3523 IF p_validate_only = FND_API.G_FALSE THEN
3524 IF(l_item_mgmt_count > 0) THEN -- Bug 13434831 /* Fix for bug#9660659 */
3525 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
3526 p_api_version => G_API_VERSION --IN NUMBER
3527 ,p_application_id => 431 --IN NUMBER
3528 ,p_attr_group_type => 'EGO_ITEMMGMT_GROUP' --IN VARCHAR2
3529 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3530 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3531 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3532 ,p_data_set_id => p_data_set_id --IN NUMBER
3533 ,p_entity_id => G_ENTITY_ID --IN NUMBER
3534 ,p_entity_index => l_entity_index_counter --IN NUMBER
3535 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3536 ,p_debug_level => p_debug_level --IN NUMBER
3537 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
3538 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
3539 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
3540 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
3541 ,p_commit => p_commit -- bug 10060587 FND_API.G_TRUE --IN VARCHAR2
3542 ,p_default_dl_view_priv_list => l_default_dl_view_priv_list
3543 ,p_default_dl_edit_priv_list => l_default_dl_edit_priv_list
3544 ,p_privilege_predicate_api_name => 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Item_Security_Predicate' --IN VARCHAR2
3545 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
3546 ,p_validate => FALSE
3547 ,p_do_dml => TRUE
3548 ,p_do_req_def_valiadtion => FALSE -- Fix for bug#9336604 TRUE --Doing this validation here since the value may have come from template
3549 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
3550 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
3551 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
3552 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
3553 );
3554 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3555 ERRBUF := l_msg_data;
3556 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
3557 RAISE G_UNHANDLED_EXCEPTION;
3558 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
3559 RETCODE := L_CONC_RET_STS_WARNING;
3560 END IF;
3561 END IF; -- end of IF (l_item_mgmt_count > 0) /* Fix for bug#9660659 */
3562
3563 IF(l_item_gtin_count > 0) THEN -- Bug 13434831 /* Fix for bug#9660659 */
3564 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
3565 p_api_version => G_API_VERSION --IN NUMBER
3566 ,p_application_id => 431 --IN NUMBER
3567 ,p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS' --IN VARCHAR2
3568 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3569 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3570 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3571 ,p_data_set_id => p_data_set_id --IN NUMBER
3572 ,p_entity_id => G_ENTITY_ID --IN NUMBER
3573 ,p_entity_index => l_entity_index_counter --IN NUMBER
3574 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3575 ,p_debug_level => p_debug_level --IN NUMBER
3576 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
3577 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
3578 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
3579 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
3580 ,p_commit => p_commit -- bug 10060587 FND_API.G_TRUE --IN VARCHAR2
3581 ,p_default_view_privilege => 'EGO_VIEW_ITEM' --IN VARCHAR2
3582 ,p_default_edit_privilege => 'EGO_EDIT_ITEM' --IN VARCHAR2
3583 ,p_privilege_predicate_api_name => 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Item_Security_Predicate' --IN VARCHAR2
3584 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
3585 ,p_validate => FALSE
3586 ,p_do_dml => TRUE
3587 ,p_do_req_def_valiadtion => TRUE --Doing this validation here since the value may have come from template
3588 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
3589 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
3590 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
3591 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
3592 );
3593
3594 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3595 ERRBUF := l_msg_data;
3596 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
3597 RAISE G_UNHANDLED_EXCEPTION;
3598 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
3599 RETCODE := L_CONC_RET_STS_WARNING;
3600 END IF;
3601 END IF; -- end of IF (l_item_gtin_count > 0) /* Fix for bug#9660659 */
3602
3603 IF(l_item_gtin_multi_count > 0) THEN -- Bug 13434831 /* Fix for bug#9660659 */
3604 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
3605 p_api_version => G_API_VERSION --IN NUMBER
3606 ,p_application_id => 431 --IN NUMBER
3607 ,p_attr_group_type => 'EGO_ITEM_GTIN_MULTI_ATTRS' --IN VARCHAR2
3608 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3609 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3610 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3611 ,p_data_set_id => p_data_set_id --IN NUMBER
3612 ,p_entity_id => G_ENTITY_ID --IN NUMBER
3613 ,p_entity_index => l_entity_index_counter --IN NUMBER
3614 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3615 ,p_debug_level => p_debug_level --IN NUMBER
3616 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
3617 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
3618 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
3619 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
3620 ,p_commit => p_commit -- bug 10060587 FND_API.G_TRUE --IN VARCHAR2
3621 ,p_default_view_privilege => 'EGO_VIEW_ITEM' --IN VARCHAR2
3622 ,p_default_edit_privilege => 'EGO_EDIT_ITEM' --IN VARCHAR2
3623 ,p_privilege_predicate_api_name => 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Item_Security_Predicate' --IN VARCHAR2
3624 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
3625 ,p_validate => FALSE
3626 ,p_do_dml => TRUE
3627 ,p_do_req_def_valiadtion => TRUE --Doing this validation here since the value may have come from template
3628 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
3629 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
3630 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
3631 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
3632 );
3633
3634 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3635 ERRBUF := l_msg_data;
3636 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
3637 RAISE G_UNHANDLED_EXCEPTION;
3638 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
3639 RETCODE := L_CONC_RET_STS_WARNING;
3640 END IF;
3641 END IF; -- end of IF (l_item_gtin_multi_count > 0) /* Fix for bug#9660659 */
3642
3643 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Completing User Attributes Bulk Load return status is ' || l_user_attrs_return_status, 1);
3644
3645 --------------------------------------------------------
3646 -- This takes care of rolling up of GDSN attributes --
3647 -- and registration/publication status. --
3648 --------------------------------------------------------
3649
3650 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting EGO_GTIN_ATTRS_PVT.Do_Post_UCCnet_Attrs_Action', 1);
3651
3652 EGO_GTIN_ATTRS_PVT.Do_Post_UCCnet_Attrs_Action ( p_data_set_id => p_data_set_id
3653 ,p_entity_id => G_ENTITY_ID
3654 ,p_entity_code => G_ENTITY_CODE
3655 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
3656 );
3657
3658
3659 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Done EGO_GTIN_ATTRS_PVT.Do_Post_UCCnet_Attrs_Action', 1);
3660
3661
3662 IF (FND_API.To_Boolean(p_purge_successful_lines)) THEN
3663 -----------------------------------------------
3664 -- Delete all successful rows from the table --
3665 -- (they're the only rows still in process) --
3666 -----------------------------------------------
3667 DELETE FROM EGO_ITM_USR_ATTR_INTRFC
3668 WHERE DATA_SET_ID = p_data_set_id
3669 AND PROCESS_STATUS = G_PS_IN_PROCESS;
3670 ELSE
3671 ----------------------------------------------
3672 -- Mark all rows we've processed as success --
3673 -- if they weren't marked as failure above --
3674 ----------------------------------------------
3675 UPDATE EGO_ITM_USR_ATTR_INTRFC
3676 SET PROCESS_STATUS = G_PS_SUCCESS
3677 WHERE DATA_SET_ID = p_data_set_id
3678 AND PROCESS_STATUS = G_PS_IN_PROCESS;
3679 END IF;
3680
3681 IF (p_debug_level > 0) THEN
3682 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Done with Item/Item Revision Concurrent Program', 0);
3683 END IF;
3684 END IF; -- IF p_validate_only = FND_API.G...
3685
3686 IF (FND_API.To_Boolean(p_initialize_error_handler)) THEN
3687
3688 -------------------------------------------------------------------
3689 -- Finally, we log any errors that we've accumulated throughout --
3690 -- our conversions and looping (including all errors encountered --
3691 -- within our Business Object's processing) --
3692 -------------------------------------------------------------------
3693 ERROR_HANDLER.Log_Error(
3694 p_write_err_to_inttable => 'Y'
3695 ,p_write_err_to_conclog => 'Y'
3696 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
3697 );
3698
3699 IF (ERROR_HANDLER.Get_Debug() = 'Y') THEN
3700 ERROR_HANDLER.Close_Debug_Session();
3701 END IF;
3702 END IF;
3703 IF FND_API.To_Boolean( p_commit ) THEN /* Added to fix Bug#7422423*/
3704 COMMIT WORK;
3705 END IF;
3706 EXCEPTION
3707 WHEN G_NO_USER_NAME_TO_VALIDATE THEN
3708
3709 ----------------------------------------
3710 -- Mark all rows in process as errors --
3711 ----------------------------------------
3712 UPDATE EGO_ITM_USR_ATTR_INTRFC
3713 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
3714 WHERE DATA_SET_ID = p_data_set_id
3715 AND PROCESS_STATUS = G_PS_IN_PROCESS;
3716
3717 ---------------------------------------------------------------------
3718 -- Use any random transaction ID in the data set to log this error --
3719 -- If no rows are found, please use -1 as transaction_id --
3720 ---------------------------------------------------------------------
3721 IF SQL%ROWCOUNT > 0 THEN
3722 SELECT TRANSACTION_ID
3723 INTO l_err_reporting_transaction_id
3724 FROM EGO_ITM_USR_ATTR_INTRFC
3725 WHERE DATA_SET_ID = p_data_set_id
3726 AND ROWNUM = 1;
3727 ELSE
3728 l_err_reporting_transaction_id := -1;
3729 END IF;
3730
3731 ERROR_HANDLER.Add_Error_Message(
3732 p_message_name => 'EGO_EF_NO_NAME_TO_VALIDATE'
3733 ,p_application_id => 'EGO'
3734 ,p_message_type => FND_API.G_RET_STS_ERROR
3735 ,p_row_identifier => l_err_reporting_transaction_id
3736 ,p_entity_id => G_ENTITY_ID
3737 ,p_entity_code => G_ENTITY_CODE
3738 ,p_table_name => G_TABLE_NAME
3739 );
3740
3741 IF (FND_API.To_Boolean(p_initialize_error_handler)) THEN
3742
3743 ---------------------------------------------------------------
3744 -- No matter what the error, we want to make sure everything --
3745 -- we've logged gets to the appropriate error locations --
3746 ---------------------------------------------------------------
3747 ERROR_HANDLER.Log_Error(
3748 p_write_err_to_inttable => 'Y'
3749 ,p_write_err_to_conclog => 'Y'
3750 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
3751 );
3752
3753 IF (ERROR_HANDLER.Get_Debug() = 'Y') THEN
3754 ERROR_HANDLER.Close_Debug_Session();
3755 END IF;
3756 END IF;
3757
3758 RETCODE := L_CONC_RET_STS_WARNING;
3759
3760 WHEN OTHERS THEN
3761
3762 ----------------------------------------
3763 -- Mark all rows in process as errors --
3764 ----------------------------------------
3765 UPDATE EGO_ITM_USR_ATTR_INTRFC
3766 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
3767 WHERE DATA_SET_ID = p_data_set_id
3768 AND PROCESS_STATUS = G_PS_IN_PROCESS;
3769
3770 ---------------------------------------------------------------------
3771 -- Use any random transaction ID in the data set to log this error --
3772 -- If no rows are found, please use -1 as transaction_id --
3773 ---------------------------------------------------------------------
3774 IF SQL%ROWCOUNT > 0 THEN
3775 SELECT TRANSACTION_ID
3776 INTO l_err_reporting_transaction_id
3777 FROM EGO_ITM_USR_ATTR_INTRFC
3778 WHERE DATA_SET_ID = p_data_set_id
3779 AND ROWNUM = 1;
3780 ELSE
3781 l_err_reporting_transaction_id := -1;
3782 END IF;
3783
3784 ERROR_HANDLER.Add_Error_Message(
3785 p_message_text => 'Unexpected error in '||G_PKG_NAME||'.Process_Item_User_Attrs_Data: '||SQLERRM
3786 ,p_application_id => 'EGO'
3787 ,p_message_type => FND_API.G_RET_STS_ERROR
3788 ,p_row_identifier => l_err_reporting_transaction_id
3789 ,p_entity_id => G_ENTITY_ID
3790 ,p_entity_code => G_ENTITY_CODE
3791 ,p_table_name => G_TABLE_NAME
3792 );
3793
3794 IF (FND_API.To_Boolean(p_initialize_error_handler)) THEN
3795
3796 ---------------------------------------------------------------
3797 -- No matter what the error, we want to make sure everything --
3798 -- we've logged gets to the appropriate error locations --
3799 ---------------------------------------------------------------
3800 ERROR_HANDLER.Log_Error(
3801 p_write_err_to_inttable => 'Y'
3802 ,p_write_err_to_conclog => 'Y'
3803 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
3804 );
3805
3806 IF (ERROR_HANDLER.Get_Debug() = 'Y') THEN
3807 ERROR_HANDLER.Close_Debug_Session();
3808 END IF;
3809 END IF;
3810
3811 RETCODE := L_CONC_RET_STS_ERROR;
3812
3813 END Process_Item_User_Attrs_Data;
3814
3815 ----------------------------------------------------------------------
3816
3817 PROCEDURE Get_Related_Class_Codes (
3818 p_classification_code IN VARCHAR2
3819 ,x_related_class_codes_list OUT NOCOPY VARCHAR2
3820 ) IS
3821
3822 BEGIN
3823
3824 x_related_class_codes_list :=
3825 Build_Parent_Cat_Group_List(TO_NUMBER(p_classification_code), NULL);
3826
3827 END Get_Related_Class_Codes;
3828
3829 ----------------------------------------------------------------------
3830
3831 PROCEDURE Impl_Item_Attr_Change_Line (
3832 p_api_version IN NUMBER
3833 ,p_change_id IN NUMBER
3834 ,p_change_line_id IN NUMBER
3835 ,p_old_revision_id IN NUMBER DEFAULT NULL
3836 ,p_new_revision_id IN NUMBER DEFAULT NULL
3837 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
3838 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
3839 ,x_return_status OUT NOCOPY VARCHAR2
3840 ,x_errorcode OUT NOCOPY NUMBER
3841 ,x_msg_count OUT NOCOPY NUMBER
3842 ,x_msg_data OUT NOCOPY VARCHAR2
3843 ) IS
3844
3845 l_old_data_level_nv_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3846 l_new_data_level_nv_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3847 l_api_name VARCHAR2(30);
3848
3849 BEGIN
3850 l_api_name := 'Impl_Item_Attr_Change_Line';
3851 SetGlobals();
3852 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
3853 ,p_module => l_api_name
3854 ,p_message => 'Started with 11 params '||
3855 ' p_api_version: '|| to_char(p_api_version) ||
3856 ' - p_change_id: '|| p_change_id ||
3857 ' - p_change_line_id: '|| p_change_line_id ||
3858 ' - p_old_revision_id: '|| p_old_revision_id ||
3859 ' - p_new_revision_id: '|| p_new_revision_id ||
3860 ' - p_init_msg_list: '|| p_init_msg_list ||
3861 ' - p_commit: '|| p_commit
3862 );
3863 ---------------------------------------------------------------------
3864 -- Build data structures to pass in Data Level info, if applicable --
3865 ---------------------------------------------------------------------
3866 IF (p_old_revision_id IS NOT NULL) THEN
3867 l_old_data_level_nv_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3868 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID',
3869 p_old_revision_id)
3870 );
3871 END IF;
3872 IF (p_new_revision_id IS NOT NULL) THEN
3873 l_new_data_level_nv_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3874 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID',
3875 p_new_revision_id)
3876 );
3877 END IF;
3878
3879 -------------------------------------------------------------------------
3880 -- Now we invoke the UserAttrs procedure, passing Item-specific params --
3881 -------------------------------------------------------------------------
3882 EGO_USER_ATTRS_DATA_PVT.Implement_Change_Line(
3883 p_api_version => 1.0
3884 ,p_object_name => G_ITEM_NAME
3885 ,p_production_b_table_name => 'EGO_MTL_SY_ITEMS_EXT_B'
3886 ,p_production_tl_table_name => 'EGO_MTL_SY_ITEMS_EXT_TL'
3887 ,p_change_b_table_name => 'EGO_ITEMS_ATTRS_CHANGES_B'
3888 ,p_change_tl_table_name => 'EGO_ITEMS_ATTRS_CHANGES_TL'
3889 ,p_tables_application_id => 431
3890 ,p_change_line_id => p_change_line_id
3891 ,p_old_data_level_nv_pairs => l_old_data_level_nv_pairs
3892 ,p_new_data_level_nv_pairs => l_new_data_level_nv_pairs
3893 ,p_related_class_code_function => 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Related_Class_Codes'
3894 ,p_init_msg_list => p_init_msg_list
3895 ,p_commit => p_commit
3896 ,x_return_status => x_return_status
3897 ,x_errorcode => x_errorcode
3898 ,x_msg_count => x_msg_count
3899 ,x_msg_data => x_msg_data
3900 );
3901 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
3902 ,p_module => l_api_name
3903 ,p_message => 'Returning with params '||
3904 ' x_return_status: '|| x_return_status ||
3905 ' - x_errorcode: '|| x_errorcode ||
3906 ' - x_msg_count: '|| x_msg_count ||
3907 ' - x_msg_data: '|| x_msg_data
3908 );
3909
3910 END Impl_Item_Attr_Change_Line;
3911
3912 ----------------------------------------------------------------------
3913 /*
3914 * Copy_data_to_Intf
3915 * --------------------------
3916 * A procedure for ITEMS use
3917 * which copies data from production/interface table to interface table
3918 * The inherited attribute groups are filtered at the source sql only.
3919 *
3920 */
3921 PROCEDURE Copy_data_to_Intf
3922 (
3923 p_api_version IN NUMBER
3924 ,p_commit IN VARCHAR2
3925 ,p_copy_from_intf_table IN VARCHAR2 -- T/F
3926 ,p_source_entity_sql IN VARCHAR2
3927 ,p_source_attr_groups_sql IN VARCHAR2
3928 ,p_dest_process_status IN VARCHAR2
3929 ,p_dest_data_set_id IN VARCHAR2
3930 ,p_dest_transaction_type IN VARCHAR2
3931 ,p_cleanup_row_identifiers IN VARCHAR2 DEFAULT FND_API.G_TRUE -- T/F
3932 ,x_return_status OUT NOCOPY VARCHAR2
3933 ,x_msg_count OUT NOCOPY NUMBER
3934 ,x_msg_data OUT NOCOPY VARCHAR2
3935 )
3936 IS
3937 TYPE DYNAMIC_CUR IS REF CURSOR;
3938 l_dynamic_cursor DYNAMIC_CUR;
3939 l_prog_int_char1_value VARCHAR2(100);
3940 l_prog_int_num4_value NUMBER;
3941 l_attr_group_sql VARCHAR2(32767);
3942 l_dynamic_sql VARCHAR2(32767);
3943 l_dummy_char VARCHAR2(32767);
3944 l_dest_transaction_type VARCHAR2(50);
3945 l_multi_row VARCHAR2(10);
3946
3947 l_insert_cols VARCHAR2(32767);
3948 l_select_sql VARCHAR2(32767);
3949 l_where_clause VARCHAR2(32767);
3950 l_when_matched VARCHAR2(32767);
3951 l_when_not_matched VARCHAR2(32767);
3952
3953 l_attr_value_str_sql VARCHAR2(32767);
3954 l_attr_value_date_sql VARCHAR2(32767);
3955 l_attr_value_num_sql VARCHAR2(32767);
3956 l_attr_value_uom_sql VARCHAR2(32767);
3957
3958 /* Bug 10263673 : Start */
3959 l_attr_str_where_sql VARCHAR2(32767);
3960 l_attr_date_where_sql VARCHAR2(32767);
3961 l_attr_num_where_sql VARCHAR2(32767);
3962 /* Bug 10263673 : End */
3963
3964 l_max_row_identifier NUMBER;
3965
3966 l_curr_attr_grp_id NUMBER;
3967 l_data_level_id NUMBER;
3968
3969 CURSOR c_attr_rec(c_attr_group_id IN NUMBER)
3970 IS
3971 SELECT
3972 attr_ext.ATTR_ID,
3973 attr_col.END_USER_COLUMN_NAME AS ATTR_NAME,
3974 attr_ext.DATA_TYPE AS DATA_TYPE_CODE,
3975 attr_ext.APPLICATION_COLUMN_NAME AS DATABASE_COLUMN,
3976 attr_ext.UOM_CLASS AS UOM_CLASS,
3977 ag_ext.MULTI_ROW
3978 FROM
3979 FND_DESCR_FLEX_COLUMN_USAGES attr_col,
3980 EGO_FND_DF_COL_USGS_EXT attr_ext,
3981 EGO_FND_DSC_FLX_CTX_EXT ag_ext
3982 WHERE ag_ext.ATTR_GROUP_ID = c_attr_group_id
3983 AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = attr_ext.DESCRIPTIVE_FLEXFIELD_NAME
3984 AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = attr_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
3985 AND ag_ext.APPLICATION_ID = attr_ext.APPLICATION_ID
3986 AND attr_ext.APPLICATION_ID = attr_col.APPLICATION_ID
3987 AND attr_ext.DESCRIPTIVE_FLEXFIELD_NAME = attr_col.DESCRIPTIVE_FLEXFIELD_NAME
3988 AND attr_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = attr_col.DESCRIPTIVE_FLEX_CONTEXT_CODE
3989 AND attr_ext.APPLICATION_COLUMN_NAME = attr_col.APPLICATION_COLUMN_NAME
3990 AND attr_col.ENABLED_FLAG = 'Y';
3991 BEGIN
3992 SetGlobals();
3993 Debug_Conc_Log('Starting Copy_data_to_Intf');
3994 -- Standard start of API savepoint
3995 IF FND_API.To_Boolean(p_commit) THEN
3996 SAVEPOINT Copy_data_to_Intf_SP;
3997 END IF;
3998
3999 BEGIN
4000 SELECT NVL(MAX(row_identifier),0)
4001 INTO l_max_row_identifier
4002 FROM EGO_ITM_USR_ATTR_INTRFC
4003 WHERE DATA_SET_ID = p_dest_data_set_id;
4004 EXCEPTION
4005 WHEN OTHERS THEN
4006 l_max_row_identifier := 0;
4007 END;
4008
4009 l_dest_transaction_type := q'# '#'||p_dest_transaction_type||q'#'#';
4010 Debug_Conc_Log('Copy_data_to_Intf: l_max_row_identifier='||l_max_row_identifier);
4011 l_insert_cols := q'#
4012 ( TRANSACTION_ID,
4013 PROCESS_STATUS,
4014 DATA_SET_ID,
4015 TRANSACTION_TYPE,
4016 ORGANIZATION_ID,
4017 ORGANIZATION_CODE,
4018 INVENTORY_ITEM_ID,
4019 ITEM_NUMBER,
4020 ITEM_CATALOG_GROUP_ID,
4021 DATA_LEVEL_ID,
4022 REVISION_ID,
4023 REVISION,
4024 PK1_VALUE,
4025 PK2_VALUE,
4026 PK3_VALUE,
4027 PK4_VALUE,
4028 PK5_VALUE,
4029 ROW_IDENTIFIER,
4030 ATTR_GROUP_TYPE,
4031 ATTR_GROUP_INT_NAME,
4032 ATTR_GROUP_ID,
4033 ATTR_INT_NAME,
4034 ATTR_VALUE_STR,
4035 ATTR_VALUE_NUM,
4036 ATTR_VALUE_DATE,
4037 ATTR_VALUE_UOM,
4038 CREATED_BY,
4039 CREATION_DATE,
4040 LAST_UPDATED_BY,
4041 LAST_UPDATE_DATE,
4042 LAST_UPDATE_LOGIN,
4043 REQUEST_ID,
4044 CHANGE_ID,
4045 CHANGE_LINE_ID,
4046 SOURCE_SYSTEM_ID,
4047 SOURCE_SYSTEM_REFERENCE,
4048 PROG_INT_CHAR1,
4049 PROG_INT_NUM4,
4050 BUNDLE_ID ) #';
4051
4052 Debug_Conc_Log('Copy_data_to_Intf: p_copy_from_intf_table='||p_copy_from_intf_table);
4053 IF FND_API.to_boolean(p_copy_from_intf_table) THEN
4054 l_prog_int_char1_value := q'#'FROM_INTF'#';
4055 l_prog_int_num4_value := 2;
4056 l_select_sql := q'#
4057 ( SELECT
4058 src.TRANSACTION_ID,
4059 #'|| p_dest_process_status || q'# AS PROCESS_STATUS,
4060 #'|| p_dest_data_set_id || q'# AS DATA_SET_ID,
4061 #'|| l_dest_transaction_type|| q'# AS TRANSACTION_TYPE,
4062 src.ORGANIZATION_ID,
4063 src.ORGANIZATION_CODE,
4064 src.INVENTORY_ITEM_ID,
4065 src.ITEM_NUMBER,
4066 src.ITEM_CATALOG_GROUP_ID,
4067 src.DATA_LEVEL_ID,
4068 src.REVISION_ID,
4069 src.REVISION,
4070 src.PK1_VALUE,
4071 src.PK2_VALUE,
4072 src.PK3_VALUE,
4073 src.PK4_VALUE,
4074 src.PK5_VALUE,
4075 #'|| l_max_row_identifier || q'# + src.ROW_IDENTIFIER AS ROW_IDENTIFIER,
4076 src.ATTR_GROUP_TYPE,
4077 src.ATTR_GROUP_INT_NAME,
4078 src.ATTR_GROUP_ID,
4079 src.ATTR_INT_NAME,
4080 src.ATTR_VALUE_STR,
4081 src.ATTR_VALUE_NUM,
4082 src.ATTR_VALUE_DATE,
4083 src.ATTR_VALUE_UOM,
4084 #' || G_USER_ID || q'# AS CREATED_BY,
4085 SYSDATE AS CREATION_DATE,
4086 #' || G_USER_ID || q'# AS LAST_UPDATED_BY,
4087 SYSDATE AS LAST_UPDATE_DATE,
4088 #' || G_LOGIN_ID || q'# AS LAST_UPDATE_LOGIN,
4089 #' || G_REQUEST_ID || q'# AS REQUEST_ID,
4090 src.CHANGE_ID,
4091 src.CHANGE_LINE_ID,
4092 src.SOURCE_SYSTEM_ID,
4093 src.SOURCE_SYSTEM_REFERENCE,
4094 #'|| l_prog_int_char1_value|| q'# AS PROG_INT_CHAR1,
4095 #'|| l_prog_int_num4_value|| q'# AS PROG_INT_NUM4,
4096 src.BUNDLE_ID
4097 FROM (#' || p_source_entity_sql || q'#) src, EGO_FND_DSC_FLX_CTX_EXT ag_ext
4098 WHERE ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(src.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
4099 AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = src.ATTR_GROUP_INT_NAME
4100 AND ag_ext.APPLICATION_ID = 431
4101 AND ( ag_ext.MULTI_ROW= 'N'
4102 OR NOT EXISTS (SELECT NULL FROM EGO_ITM_USR_ATTR_INTRFC intfx
4103 WHERE intfx.DATA_SET_ID = src.DATA_SET_ID
4104 AND intfx.PROCESS_STATUS = #'|| p_dest_process_status || q'#
4105 AND intfx.INVENTORY_ITEM_ID = src.INVENTORY_ITEM_ID
4106 AND intfx.ORGANIZATION_ID = src.ORGANIZATION_ID
4107 AND intfx.DATA_LEVEL_ID = src.DATA_LEVEL_ID
4108 AND NVL(intfx.BUNDLE_ID, -1) = NVL(src.BUNDLE_ID, -1)
4109 AND NVL(intfx.REVISION_ID, -1) = NVL(src.REVISION_ID, -1)
4110 AND NVL(intfx.PK1_VALUE, -1) = NVL(src.PK1_VALUE, -1)
4111 AND NVL(intfx.PK2_VALUE, -1) = NVL(src.PK2_VALUE, -1)
4112 AND NVL(intfx.PK3_VALUE, -1) = NVL(src.PK3_VALUE, -1)
4113 AND NVL(intfx.PK4_VALUE, -1) = NVL(src.PK4_VALUE, -1)
4114 AND NVL(intfx.PK5_VALUE, -1) = NVL(src.PK5_VALUE, -1)
4115 AND NVL(intfx.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = NVL(src.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
4116 AND intfx.ATTR_GROUP_INT_NAME = src.ATTR_GROUP_INT_NAME
4117 )
4118 )
4119 ) source #';
4120
4121 l_where_clause := q'#
4122 ON
4123 ( intf.DATA_SET_ID = source.DATA_SET_ID
4124 AND intf.PROCESS_STATUS = source.PROCESS_STATUS
4125 AND intf.INVENTORY_ITEM_ID = source.INVENTORY_ITEM_ID
4126 AND intf.ORGANIZATION_ID = source.ORGANIZATION_ID
4127 AND intf.DATA_LEVEL_ID = source.DATA_LEVEL_ID
4128 AND NVL(intf.BUNDLE_ID, -1) = NVL(source.BUNDLE_ID, -1)
4129 AND NVL(intf.REVISION_ID, -1) = NVL(source.REVISION_ID, -1)
4130 AND NVL(intf.PK1_VALUE, -1) = NVL(source.PK1_VALUE, -1)
4131 AND NVL(intf.PK2_VALUE, -1) = NVL(source.PK2_VALUE, -1)
4132 AND NVL(intf.PK3_VALUE, -1) = NVL(source.PK3_VALUE, -1)
4133 AND NVL(intf.PK4_VALUE, -1) = NVL(source.PK4_VALUE, -1)
4134 AND NVL(intf.PK5_VALUE, -1) = NVL(source.PK5_VALUE, -1)
4135 AND NVL(intf.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = NVL(source.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
4136 AND intf.ATTR_GROUP_INT_NAME = source.ATTR_GROUP_INT_NAME
4137 AND intf.ATTR_INT_NAME = source.ATTR_INT_NAME
4138 ) #';
4139
4140 l_when_matched := q'#
4141 WHEN MATCHED THEN
4142 UPDATE SET
4143 intf.ATTR_VALUE_STR = source.ATTR_VALUE_STR,
4144 intf.ATTR_VALUE_NUM = source.ATTR_VALUE_NUM,
4145 intf.ATTR_VALUE_UOM = source.ATTR_VALUE_UOM,
4146 intf.ATTR_VALUE_DATE = source.ATTR_VALUE_DATE,
4147 intf.PROG_INT_NUM4 = 3
4148 WHERE NVL(intf.PROG_INT_NUM4, -1) <> 0
4149 #';
4150
4151 l_when_not_matched := q'#
4152 WHEN NOT MATCHED THEN
4153 INSERT #' || l_insert_cols || q'# VALUES
4154 ( source.TRANSACTION_ID,
4155 source.PROCESS_STATUS,
4156 source.DATA_SET_ID,
4157 source.TRANSACTION_TYPE,
4158 source.ORGANIZATION_ID,
4159 source.ORGANIZATION_CODE,
4160 source.INVENTORY_ITEM_ID,
4161 source.ITEM_NUMBER,
4162 source.ITEM_CATALOG_GROUP_ID,
4163 source.DATA_LEVEL_ID,
4164 source.REVISION_ID,
4165 source.REVISION,
4166 source.PK1_VALUE,
4167 source.PK2_VALUE,
4168 source.PK3_VALUE,
4169 source.PK4_VALUE,
4170 source.PK5_VALUE,
4171 source.ROW_IDENTIFIER,
4172 source.ATTR_GROUP_TYPE,
4173 source.ATTR_GROUP_INT_NAME,
4174 source.ATTR_GROUP_ID,
4175 source.ATTR_INT_NAME,
4176 source.ATTR_VALUE_STR,
4177 source.ATTR_VALUE_NUM,
4178 source.ATTR_VALUE_DATE,
4179 source.ATTR_VALUE_UOM,
4180 source.CREATED_BY,
4181 source.CREATION_DATE,
4182 source.LAST_UPDATED_BY,
4183 source.LAST_UPDATE_DATE,
4184 source.LAST_UPDATE_LOGIN,
4185 source.REQUEST_ID,
4186 source.CHANGE_ID,
4187 source.CHANGE_LINE_ID,
4188 source.SOURCE_SYSTEM_ID,
4189 source.SOURCE_SYSTEM_REFERENCE,
4190 source.PROG_INT_CHAR1,
4191 source.PROG_INT_NUM4,
4192 source.BUNDLE_ID )
4193 #';
4194 l_dynamic_sql := 'MERGE INTO EGO_ITM_USR_ATTR_INTRFC intf USING ' ||
4195 l_select_sql || l_where_clause || l_when_matched || l_when_not_matched;
4196
4197 Debug_Conc_Log('Copy_data_to_Intf: l_dynamic_sql='||l_dynamic_sql);
4198 EXECUTE IMMEDIATE l_dynamic_sql;
4199 Debug_Conc_Log('Copy_data_to_Intf: Processed '||SQL%ROWCOUNT||' rows');
4200 ELSE -- FND_API.to_boolean(p_copy_from_intf_table)
4201 l_prog_int_char1_value := q'#'FROM_PROD'#';
4202 l_prog_int_num4_value := 1;
4203 l_attr_group_sql := 'SELECT DISTINCT ATTR_GROUP_ID FROM ( '|| p_source_attr_groups_sql ||' ) ';
4204 OPEN l_dynamic_cursor FOR l_attr_group_sql;
4205 LOOP
4206 FETCH l_dynamic_cursor INTO l_curr_attr_grp_id;
4207 EXIT WHEN l_dynamic_cursor%NOTFOUND;
4208 Debug_Conc_Log('Copy_data_to_Intf: Processing AG, ID='||l_curr_attr_grp_id);
4209
4210 l_attr_value_str_sql := '(CASE';
4211 l_attr_value_date_sql := '(CASE';
4212 l_attr_value_num_sql := '(CASE';
4213 l_attr_value_uom_sql := '(CASE';
4214 FOR i IN c_attr_rec(l_curr_attr_grp_id)
4215 LOOP
4216 l_attr_value_str_sql := l_attr_value_str_sql || q'# WHEN attr_col.END_USER_COLUMN_NAME = '#'|| i.ATTR_NAME || q'#' #';
4217 l_attr_value_date_sql := l_attr_value_date_sql || q'# WHEN attr_col.END_USER_COLUMN_NAME = '#'|| i.ATTR_NAME || q'#' #';
4218 l_attr_value_num_sql := l_attr_value_num_sql || q'# WHEN attr_col.END_USER_COLUMN_NAME = '#'|| i.ATTR_NAME || q'#' #';
4219 l_attr_value_uom_sql := l_attr_value_uom_sql || q'# WHEN attr_col.END_USER_COLUMN_NAME = '#'|| i.ATTR_NAME || q'#' #';
4220 l_multi_row := i.MULTI_ROW;
4221
4222 IF i.DATA_TYPE_CODE IN (EGO_EXT_FWK_PUB.G_TRANS_TEXT_DATA_TYPE, EGO_EXT_FWK_PUB.G_CHAR_DATA_TYPE) THEN
4223 l_attr_value_str_sql := l_attr_value_str_sql || ' THEN src.'||i.DATABASE_COLUMN;
4224 l_attr_value_date_sql := l_attr_value_date_sql || ' THEN NULL ';
4225 l_attr_value_num_sql := l_attr_value_num_sql || ' THEN NULL ';
4226 l_attr_value_uom_sql := l_attr_value_uom_sql || ' THEN NULL ';
4227 ELSIF i.DATA_TYPE_CODE IN (EGO_EXT_FWK_PUB.G_DATE_DATA_TYPE, EGO_EXT_FWK_PUB.G_DATE_TIME_DATA_TYPE) THEN
4228 l_attr_value_str_sql := l_attr_value_str_sql || ' THEN NULL ';
4229 l_attr_value_date_sql := l_attr_value_date_sql || ' THEN src.'||i.DATABASE_COLUMN;
4230 l_attr_value_num_sql := l_attr_value_num_sql || ' THEN NULL ';
4231 l_attr_value_uom_sql := l_attr_value_uom_sql || ' THEN NULL ';
4232 ELSE
4233 l_attr_value_str_sql := l_attr_value_str_sql || ' THEN NULL ';
4234 l_attr_value_date_sql := l_attr_value_date_sql || ' THEN NULL ';
4235 IF i.UOM_CLASS IS NULL THEN
4236 l_attr_value_num_sql := l_attr_value_num_sql || ' THEN src.'||i.DATABASE_COLUMN;
4237 l_attr_value_uom_sql := l_attr_value_uom_sql || ' THEN NULL ';
4238 ELSE
4239 l_dummy_char := 'UOM'||SUBSTR(i.DATABASE_COLUMN, 2);
4240 l_attr_value_num_sql := l_attr_value_num_sql || q'# THEN src.#' || i.DATABASE_COLUMN || q'#
4241 /(SELECT CONVERSION_RATE
4242 FROM MTL_UOM_CONVERSIONS
4243 WHERE UOM_CLASS = '#' || i.UOM_CLASS ||q'#'
4244 AND UOM_CODE = src.#' || l_dummy_char || q'#
4245 AND ROWNUM = 1)#';
4246 l_attr_value_uom_sql := l_attr_value_uom_sql || ' THEN src.'|| l_dummy_char ||' ';
4247 END IF; --IF i.UOM_CLASS IS NULL THEN
4248 END IF; --IF i.DATA_TYPE_CODE IN
4249 END LOOP;
4250
4251 /* Bug 10263673 : Start */
4252 l_attr_str_where_sql := l_attr_value_str_sql || ' END)';
4253 l_attr_date_where_sql := l_attr_value_date_sql || ' END)';
4254 l_attr_num_where_sql := l_attr_value_num_sql || ' END)';
4255 /* Bug 10263673 : End */
4256
4257 l_attr_value_str_sql := l_attr_value_str_sql || ' END) AS ATTR_VALUE_STR, ';
4258 l_attr_value_date_sql := l_attr_value_date_sql || ' END) AS ATTR_VALUE_DATE, ';
4259 l_attr_value_num_sql := l_attr_value_num_sql || ' END) AS ATTR_VALUE_NUM, ';
4260 l_attr_value_uom_sql := l_attr_value_uom_sql || ' END) AS ATTR_VALUE_UOM, ';
4261
4262 l_select_sql := q'#
4263 SELECT
4264 src.TRANSACTION_ID,
4265 #'|| p_dest_process_status || q'# AS PROCESS_STATUS,
4266 #'|| p_dest_data_set_id || q'# AS DATA_SET_ID,
4267 #'|| l_dest_transaction_type|| q'# AS TRANSACTION_TYPE,
4268 src.ORGANIZATION_ID,
4269 src.ORGANIZATION_CODE,
4270 src.INVENTORY_ITEM_ID,
4271 src.ITEM_NUMBER,
4272 src.ITEM_CATALOG_GROUP_ID,
4273 src.DATA_LEVEL_ID,
4274 src.REVISION_ID,
4275 src.REVISION,
4276 src.PK1_VALUE,
4277 src.PK2_VALUE,
4278 src.PK3_VALUE,
4279 src.PK4_VALUE,
4280 src.PK5_VALUE,
4281 #'|| l_max_row_identifier || q'# + src.ROW_IDENTIFIER AS ROW_IDENTIFIER,
4282 ag_ext.DESCRIPTIVE_FLEXFIELD_NAME,
4283 ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE,
4284 ag_ext.ATTR_GROUP_ID,
4285 attr_col.END_USER_COLUMN_NAME, #' ||
4286 l_attr_value_str_sql ||
4287 l_attr_value_num_sql ||
4288 l_attr_value_date_sql ||
4289 l_attr_value_uom_sql ||
4290 G_USER_ID || q'# AS CREATED_BY,
4291 SYSDATE AS CREATION_DATE,
4292 #' || G_USER_ID || q'# AS LAST_UPDATED_BY,
4293 SYSDATE AS LAST_UPDATE_DATE,
4294 #' || G_LOGIN_ID || q'# AS LAST_UPDATE_LOGIN,
4295 #' || G_REQUEST_ID || q'# AS REQUEST_ID,
4296 src.CHANGE_ID,
4297 src.CHANGE_LINE_ID,
4298 src.SOURCE_SYSTEM_ID,
4299 src.SOURCE_SYSTEM_REFERENCE,
4300 #'|| l_prog_int_char1_value|| q'# AS PROG_INT_CHAR1,
4301 #'|| l_prog_int_num4_value|| q'# AS PROG_INT_NUM4,
4302 src.BUNDLE_ID
4303 FROM (#' || p_source_entity_sql || q'#) src,
4304 FND_DESCR_FLEX_COLUMN_USAGES attr_col,
4305 EGO_FND_DSC_FLX_CTX_EXT ag_ext #';
4306
4307 l_where_clause := q'#
4308 WHERE src.ATTR_GROUP_ID = #' || l_curr_attr_grp_id || q'#
4309 AND src.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
4310 AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = attr_col.DESCRIPTIVE_FLEXFIELD_NAME
4311 AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = attr_col.DESCRIPTIVE_FLEX_CONTEXT_CODE
4312 AND ag_ext.APPLICATION_ID = attr_col.APPLICATION_ID
4313 AND attr_col.ENABLED_FLAG = 'Y'
4314 AND NOT EXISTS (SELECT NULL FROM EGO_ITM_USR_ATTR_INTRFC intf
4315 WHERE intf.DATA_SET_ID = src.DATA_SET_ID
4316 AND intf.PROCESS_STATUS = #' || p_dest_process_status || q'#
4317 AND intf.INVENTORY_ITEM_ID = src.INVENTORY_ITEM_ID
4318 AND intf.ORGANIZATION_ID = src.ORGANIZATION_ID
4319 AND intf.DATA_LEVEL_ID = src.DATA_LEVEL_ID
4320 AND NVL(intf.BUNDLE_ID, -1) = NVL(src.BUNDLE_ID, -1)
4321 AND NVL(intf.REVISION_ID, -1) = NVL(src.REVISION_ID, -1)
4322 AND NVL(intf.PK1_VALUE, -1) = NVL(src.PK1_VALUE, -1)
4323 AND NVL(intf.PK2_VALUE, -1) = NVL(src.PK2_VALUE, -1)
4324 AND NVL(intf.PK3_VALUE, -1) = NVL(src.PK3_VALUE, -1)
4325 AND NVL(intf.PK4_VALUE, -1) = NVL(src.PK4_VALUE, -1)
4326 AND NVL(intf.PK5_VALUE, -1) = NVL(src.PK5_VALUE, -1)
4327 AND NVL(intf.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = ag_ext.DESCRIPTIVE_FLEXFIELD_NAME
4328 AND intf.ATTR_GROUP_INT_NAME = ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE #';
4329 IF l_multi_row = 'Y' THEN
4330 l_where_clause := l_where_clause || ' ) ';
4331 ELSE
4332 l_where_clause := l_where_clause || ' AND intf.ATTR_INT_NAME = attr_col.END_USER_COLUMN_NAME ) ';
4333 END IF;
4334
4335 -- Bug 10263673 : Do not insert the attrs which have null values.
4336 -- Bug 11816309 : Changes
4337 l_where_clause := l_where_clause || '
4338 AND (' || l_attr_str_where_sql || ' IS NOT NULL OR
4339 '|| l_attr_date_where_sql || ' IS NOT NULL OR
4340 '|| l_attr_num_where_sql || ' IS NOT NULL
4341 )';
4342
4343
4344 l_dynamic_sql := 'INSERT INTO EGO_ITM_USR_ATTR_INTRFC ' || l_insert_cols || l_select_sql || l_where_clause;
4345 Debug_Conc_Log('Copy_data_to_Intf: l_dynamic_sql='||l_dynamic_sql);
4346 EXECUTE IMMEDIATE l_dynamic_sql;
4347 Debug_Conc_Log('Copy_data_to_Intf: Inserted '||SQL%ROWCOUNT||' rows');
4348 END LOOP; -- attr_group_id LOOP
4349 CLOSE l_dynamic_cursor;
4350 END IF; -- FND_API.to_boolean(p_copy_from_intf_table)
4351
4352 Debug_Conc_Log('Copy_data_to_Intf: Done inserting');
4353
4354 /* Fix for bug#9678667 - Start: Commenting the below code - the below code should be done after this api call */
4355 /*
4356 IF p_dest_process_status = G_PS_IN_PROCESS THEN
4357 UPDATE ego_itm_usr_attr_intrfc
4358 SET PROG_INT_NUM1 = NULL
4359 ,PROG_INT_NUM2 = NULL
4360 ,PROG_INT_NUM3 = NULL
4361 ,PROG_INT_CHAR1 = 'N'
4362 ,PROG_INT_CHAR2 = 'N'
4363 ,REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
4364 ,PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID
4365 ,PROGRAM_ID = FND_GLOBAL.CONC_PROGRAM_ID
4366 ,PROGRAM_UPDATE_DATE = SYSDATE
4367 WHERE PROCESS_STATUS = p_dest_process_status
4368 AND DATA_SET_ID = p_dest_data_set_id
4369 AND TRANSACTION_TYPE = p_dest_transaction_type
4370 AND PROG_INT_CHAR1 IN ('FROM_INTF', 'FROM_PROD');
4371 END IF;
4372 */ /* Fix for bug#9678667 : End */
4373
4374 IF p_cleanup_row_identifiers = FND_API.G_TRUE THEN
4375 Debug_Conc_Log('Copy_data_to_Intf: Calling Clean_Up_UDA_Row_Idents with process_status='||p_dest_process_status);
4376 EGO_IMPORT_PVT.Clean_Up_UDA_Row_Idents(
4377 p_batch_id => p_dest_data_set_id,
4378 p_process_status => p_dest_process_status,
4379 p_ignore_item_num_upd => FND_API.G_TRUE,
4380 p_commit => FND_API.G_FALSE );
4381 Debug_Conc_Log('Copy_data_to_Intf: Clean_Up_UDA_Row_Idents Done.');
4382 END IF;
4383 Debug_Conc_Log('Copy_data_to_Intf: Done');
4384 x_return_status := FND_API.G_RET_STS_SUCCESS;
4385 EXCEPTION WHEN OTHERS THEN
4386 IF FND_API.To_Boolean(p_commit) THEN
4387 ROLLBACK TO Copy_data_to_Intf_SP;
4388 END IF;
4389 IF l_dynamic_cursor%ISOPEN THEN
4390 CLOSE l_dynamic_cursor;
4391 END IF;
4392 Debug_Conc_Log('Copy_data_to_Intf: Error-'||SQLERRM);
4393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4394 x_msg_data := SQLERRM;
4395 END Copy_data_to_Intf;
4396
4397 ----------------------------------------------------------------------
4398
4399 END EGO_ITEM_USER_ATTRS_CP_PUB;