[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.59 2007/11/09 03:02:54 mantyaku 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 ) IS
413 l_api_name VARCHAR2(30);
414 l_error_message_name VARCHAR2(30);
415 l_entity_index_counter NUMBER := 0;
416 l_catalog_category_names_table LOCAL_MEDIUM_VARCHAR_TABLE;
417 l_current_attr_group_obj EGO_ATTR_GROUP_METADATA_OBJ;
418 l_policy_check_name VARCHAR2(30);
419 l_add_all_to_cm VARCHAR2(1);
420 l_current_attr_group_name FND_DESCR_FLEX_CONTEXTS_TL.descriptive_flex_context_name%TYPE;
421 l_current_category_name MTL_ITEM_CATALOG_GROUPS_KFV.concatenated_segments%TYPE;
422 l_current_life_cycle VARCHAR2(240);
423 l_current_phase_name VARCHAR2(240);
424 l_prev_loop_org_id NUMBER;
425 l_prev_loop_inv_item_id NUMBER;
426 l_prev_loop_row_identifier NUMBER;
427 l_at_start_of_instance BOOLEAN;
428 l_can_edit_this_instance VARCHAR2(1);
429 l_token_table ERROR_HANDLER.Token_Tbl_Type;
430 l_could_edit_prev_instance VARCHAR2(1);
431 l_at_start_of_row BOOLEAN;
432 p_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
433 p_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
434 p_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
435 p_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
436 l_user_privileges_table EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE;
437 l_privilege_table_index NUMBER;
438 l_previous_privs_table EGO_VARCHAR_TBL_TYPE;
439 l_current_privs_table EGO_VARCHAR_TBL_TYPE;
440 l_failed_row_id_buffer VARCHAR2(32767);
441 l_failed_row_id_list VARCHAR2(32767);
442 l_failed_row_id_sql VARCHAR2(32767);
443 l_return_status VARCHAR2(1);
444 l_errorcode NUMBER;
445 l_msg_count NUMBER;
446 l_msg_data VARCHAR2(1000);
447 l_dynamic_sql VARCHAR2(32767);
448 l_policy_check_sql VARCHAR2(32767);
449 l_debug_rowcount NUMBER := 0;
450 l_rec_count NUMBER;
451 l_err_reporting_transaction_id NUMBER;
452 l_related_class_codes_query VARCHAR2(1000);
453 l_user_attrs_return_status VARCHAR2(1);
454 l_item_return_status VARCHAR2(1);
455 l_attr_group_type VARCHAR2(30);
456 l_entity_sql VARCHAR2(5000);
457 G_UNHANDLED_EXCEPTION EXCEPTION;
458 l_target_entity_sql VARCHAR2(5000);
459 l_excluded_ag_list VARCHAR2(1000);
460 l_gtinval_ret_code VARCHAR2(1);
461 l_user_id NUMBER := FND_GLOBAL.USER_ID;
462 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
463 l_privilege_predicate_api_name VARCHAR2(1000);
464 l_item_sup_dl_id NUMBER;
465 l_item_sup_site_dl_id NUMBER;
466 l_item_sup_site_org_dl_id NUMBER;
467
468 -------------------------------------------------------------------------
469 -- For finding Inventory Item ID using Organization ID and Item Number --
470 -------------------------------------------------------------------------
471 CURSOR item_num_to_id_cursor (cp_data_set_id IN NUMBER)
472 IS
473 SELECT DISTINCT
474 ORGANIZATION_ID
475 ,ITEM_NUMBER
476 FROM EGO_ITM_USR_ATTR_INTRFC
477 WHERE DATA_SET_ID = cp_data_set_id
478 AND PROCESS_STATUS = G_PS_IN_PROCESS
479 AND ITEM_NUMBER IS NOT NULL
480 AND INVENTORY_ITEM_ID IS NULL;
481
482 ---------------------------------------------------------------
483 -- For reporting errors for all of the four conversion steps --
484 ---------------------------------------------------------------
485 CURSOR error_case_cursor (cp_data_set_id IN NUMBER)
486 IS
487 SELECT DISTINCT
488 PROCESS_STATUS
489 ,ORGANIZATION_CODE
490 ,ORGANIZATION_ID
491 ,ITEM_NUMBER
492 ,INVENTORY_ITEM_ID
493 ,ATTR_GROUP_ID
494 ,ATTR_GROUP_INT_NAME
495 ,REVISION
496 ,REVISION_ID
497 ,ITEM_CATALOG_GROUP_ID
498 ,TRANSACTION_ID
499 ,ATTR_GROUP_TYPE
500 ,PROG_INT_NUM1
501 ,PROG_INT_NUM2
502 ,PROG_INT_NUM3
503 ,ATTR_VALUE_STR
504 ,ATTR_VALUE_NUM
505 ,ATTR_VALUE_DATE
506 ,ATTR_DISP_VALUE
507 ,PK1_VALUE
508 ,PK2_VALUE
509 ,DATA_LEVEL_NAME
510 ,USER_DATA_LEVEL_NAME
511 FROM EGO_ITM_USR_ATTR_INTRFC
512 WHERE DATA_SET_ID = cp_data_set_id
513 AND PROCESS_STATUS IN (G_PS_BAD_ORG_ID,
514 G_PS_BAD_ORG_CODE,
515 G_PS_BAD_ITEM_ID,
516 G_PS_BAD_ITEM_NUMBER,
517 G_PS_BAD_REVISION_ID,
518 G_PS_BAD_REVISION_CODE,
519 G_PS_BAD_CATALOG_GROUP_ID,
520 G_PS_BAD_ATTR_GROUP_ID,
521 G_PS_BAD_ATTR_GROUP_NAME,
522 G_PS_CHG_POLICY_NOT_ALLOWED,
523 G_PS_DATA_LEVEL_INCORRECT,
524 G_PS_BAD_DATA_LEVEL,
525 G_PS_BAD_SUPPLIER,
526 G_PS_BAD_SUPPLIER_SITE,
527 G_PS_BAD_SUPPLIER_SITE_ORG,
528 G_PS_BAD_STYLE_VAR_VALUE_SET,
529 G_PS_VAR_VSET_CHG_NOT_ALLOWED,
530 G_PS_SKU_VAR_VALUE_NOT_UPD,
531 G_PS_INH_ATTR_FOR_SKU_NOT_UPD
532 );
533
534 -------------------------------------------------------------------
535 -- For processing all rows that passed the four conversion steps --
536 -------------------------------------------------------------------
537 CURSOR data_set_cursor (cp_data_set_id IN NUMBER)
538 IS
539 SELECT TRANSACTION_ID
540 ,PROCESS_STATUS
541 ,ORGANIZATION_CODE
542 ,ITEM_NUMBER
543 ,ATTR_GROUP_INT_NAME
544 ,ROW_IDENTIFIER
545 ,ATTR_INT_NAME
546 ,ATTR_VALUE_STR
547 ,ATTR_VALUE_NUM
548 ,ATTR_VALUE_DATE
549 ,ATTR_DISP_VALUE
550 ,TRANSACTION_TYPE
551 ,ORGANIZATION_ID
552 ,INVENTORY_ITEM_ID
553 ,ITEM_CATALOG_GROUP_ID
554 ,REVISION_ID
555 ,ATTR_GROUP_ID
556 FROM EGO_ITM_USR_ATTR_INTRFC
557 WHERE DATA_SET_ID = cp_data_set_id
558 AND PROCESS_STATUS = G_PS_IN_PROCESS
559 ORDER BY ORGANIZATION_ID,
560 INVENTORY_ITEM_ID,
561 (DECODE (UPPER(TRANSACTION_TYPE),
562 'DELETE', 1,
563 'UPDATE', 2,
564 'SYNC', 3,
565 'CREATE', 4, 5)),
566 ROW_IDENTIFIER,
567 ATTR_GROUP_INT_NAME;
568
569 --------------------------------------------------------------------------
570 -- For getting this distinct catalog groups passing all the validations --
571 --------------------------------------------------------------------------
572 CURSOR distinct_catalaog_groups (cp_data_set_id IN NUMBER)
573 IS
574 SELECT ITEM_CATALOG_GROUP_ID
575 FROM EGO_ITM_USR_ATTR_INTRFC
576 WHERE DATA_SET_ID = cp_data_set_id
577 AND PROCESS_STATUS = G_PS_IN_PROCESS
578 GROUP BY ITEM_CATALOG_GROUP_ID;
579
580 --------------------------------------------------------
581 -- For validations related to pk1_value and pk2_value --
582 --------------------------------------------------------
583 CURSOR c_data_levels IS
584 SELECT DATA_LEVEL_ID, DATA_LEVEL_NAME
585 FROM EGO_DATA_LEVEL_B
586 WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
587 AND APPLICATION_ID = 431
588 AND DATA_LEVEL_NAME IN ('ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG');
589
590 -------------------------------------------------------------
591 -- For sending default privilege names for each data level --
592 -------------------------------------------------------------
593 CURSOR c_data_levels_for_sec IS
594 SELECT DATA_LEVEL_ID, DATA_LEVEL_NAME, ATTR_GROUP_TYPE
595 FROM EGO_DATA_LEVEL_B
596 WHERE APPLICATION_ID = 431
597 AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';
598
599 l_default_dl_view_priv_list EGO_COL_NAME_VALUE_PAIR_ARRAY;
600 l_default_dl_edit_priv_list EGO_COL_NAME_VALUE_PAIR_ARRAY;
601 l_default_dl_view_priv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
602 l_default_dl_edit_priv_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
603 BEGIN
604 l_api_name := 'Process_Item_User_Attrs_Data';
605 SetGlobals();
606 RETCODE := L_CONC_RET_STS_SUCCESS;
607 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
608 ,p_module => l_api_name
609 ,p_message => 'Started with 7 params '||
610 ' p_data_set_id: '|| p_data_set_id ||
611 ' - p_purge_successful_lines: '|| p_purge_successful_lines ||
612 ' - p_initialize_error_handler: '|| p_initialize_error_handler ||
613 ' - p_validate_only: ' || p_validate_only
614 );
615 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message=> 'As given');
616 --If there are no rows to process for this data_set_id, return success.
617 SELECT
618 COUNT(DATA_SET_ID)
619 INTO l_rec_count
620 FROM EGO_ITM_USR_ATTR_INTRFC intf
621 WHERE DATA_SET_ID = p_data_set_id
622 AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS IN (G_PS_TO_BE_PROCESSED, G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS) );
623
624 IF (l_rec_count = 0) THEN
625 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Returning because no data exists in interface table to process', 0);
626 RETURN;
627 END IF;
628
629 l_attr_group_type := 'EGO_ITEMMGMT_GROUP';
630 -----------------------------------------------
631 -- Set this global variable once per session --
632 -----------------------------------------------
633 IF (G_HZ_PARTY_ID IS NULL) THEN
634 IF (G_USER_NAME IS NOT NULL) THEN
635 SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
636 INTO G_HZ_PARTY_ID
637 FROM EGO_PEOPLE_V
638 WHERE USER_NAME = G_USER_NAME;
639 ELSE
640 RAISE G_NO_USER_NAME_TO_VALIDATE;
641 END IF;
642 END IF;
643
644 --======================--
645 -- ERROR_HANDLER SET-UP --
646 --======================--
647
648 IF (FND_API.To_Boolean(p_initialize_error_handler)) THEN
649
650 ERROR_HANDLER.Initialize();
651 ERROR_HANDLER.Set_Bo_Identifier(EGO_USER_ATTRS_DATA_PVT.G_BO_IDENTIFIER);
652
653 ---------------------------------------------------
654 -- If we're debugging, we have to set up a Debug --
655 -- session (unless our caller already did so) --
656 ---------------------------------------------------
657
658 IF (p_debug_level > 0 AND ERROR_HANDLER.Get_Debug() = 'N') THEN
659 EGO_USER_ATTRS_DATA_PVT.Set_Up_Debug_Session(G_ENTITY_ID, G_ENTITY_CODE, p_debug_level);
660 END IF;
661 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Item/Item Revision Concurrent Program for data set ID: '||p_data_set_id, 0);
662 END IF;
663 --------------------------------------------------------
664 -- Related classification query is required for
665 -- User Attributes Bulk Load API
666 --------------------------------------------------------
667
668 l_related_class_codes_query :=
669 ' (SELECT ITEM_CATALOG_GROUP_ID '||
670 ' FROM MTL_ITEM_CATALOG_GROUPS_B ' ||
671 ' CONNECT BY PRIOR ' ||
672 ' PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID ' ||
673 ' START WITH ' ||
674 ' ITEM_CATALOG_GROUP_ID = UAI2.ITEM_CATALOG_GROUP_ID)' ||
675 ' UNION ALL ' ||
676 ' (SELECT UAI2.ITEM_CATALOG_GROUP_ID FROM DUAL)' ;
677
678 --===================================--
679 -- GETTING THE INTERFACE TABLE READY --
680 --===================================--
681
682 IF (p_debug_level > 0) THEN
683 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Preparing interface table', 0);
684 END IF;
685
686 -------------------------------------------------------------------
687 -- Gather statistics: since the data in interface tables changes --
688 -- frequently, our indexes are not very useful unless we gather --
689 -- statistics for the *current* data in the table. (APPS has a --
690 -- standard to gather statistics at the beginning of interface --
691 -- import programs.) --
692 -------------------------------------------------------------------
693 /*6602290 : Stats gather through profile : Stats are gathered in EGOVIMPB
694 SELECT COUNT(data_set_id)
695 INTO l_rec_count
696 FROM EGO_ITM_USR_ATTR_INTRFC
697 WHERE data_set_id = p_data_set_id;
698 IF l_rec_count > 50 THEN
699 FND_STATS.Gather_Table_Stats(
700 ownname => 'EGO'
701 ,tabname => 'EGO_ITM_USR_ATTR_INTRFC'
702 ,cascade => TRUE
703 );
704 END IF;
705 */
706 --
707 -- get the item id and store in global
708 --
709 BEGIN
710 SELECT OBJECT_ID
711 INTO G_ITEM_OBJECT_ID
712 FROM FND_OBJECTS
713 WHERE OBJ_NAME = G_ITEM_NAME;
714 EXCEPTION
715 WHEN OTHERS THEN
716 IF (p_debug_level > 0) THEN
717 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Cannot find object EGO_ITEM in fnd_objects ', 0);
718 END IF;
719 G_ITEM_OBJECT_ID := NULL;
720 END;
721 ---------------------------------------------------------------------
722 -- Mark all rows we'll be processing, and null out user input for --
723 -- the ITEM_CATALOG_GROUP_ID column (so we can validate it); also --
724 -- update Concurrent Request information for better user tracking --
725 -- and update the "WHO" columns on the assumption that the current --
726 -- user is also the person who loaded this data set into the table --
727 ---------------------------------------------------------------------
728 UPDATE EGO_ITM_USR_ATTR_INTRFC
729 SET PROCESS_STATUS = G_PS_IN_PROCESS
730 ,ITEM_CATALOG_GROUP_ID = NULL
731 ,PROG_INT_NUM1 = NULL
732 ,PROG_INT_NUM2 = NULL
733 ,PROG_INT_NUM3 = NULL
734 ,PROG_INT_CHAR1 = 'N'
735 ,PROG_INT_CHAR2 = 'N'
736 ,REQUEST_ID = G_REQUEST_ID
737 ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
738 ,PROGRAM_ID = G_PROGAM_ID
739 ,PROGRAM_UPDATE_DATE = SYSDATE
740 ,CREATED_BY = NVL(CREATED_BY, G_USER_ID)
741 ,CREATION_DATE = NVL(CREATION_DATE, SYSDATE)
742 ,LAST_UPDATED_BY = G_USER_ID
743 ,LAST_UPDATE_DATE = SYSDATE
744 ,LAST_UPDATE_LOGIN = G_LOGIN_ID
745 ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
746 WHERE DATA_SET_ID = p_data_set_id
747 AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
748
749 --==================================--
750 -- THE FIVE PRELIMINARY CONVERSIONS --
751 --==================================--
752
753 IF (p_debug_level > 0) THEN
754 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting conversions', 0);
755 END IF;
756
757 ----------------------------------------------
758 -- 1). Validate passed-in Organization IDs --
759 -- and convert passed-in Organization Codes --
760 ----------------------------------------------
761 IF (p_debug_level > 0) THEN
762 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Org validation/conversion', 0);
763 END IF;
764
765
766 -----------------------------------------------------------------
767 -- Next, try to turn Master Organization Codes into Master Org --
768 -- IDs for those rows where the user didn't pass in an Org ID; --
769 -- as above, if the AG is associated at the Rev level then the --
770 -- Orgs don't have to be Master Orgs. --
771 -----------------------------------------------------------------
772 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
773 SET UAI.ORGANIZATION_ID =
774 (SELECT MP.ORGANIZATION_ID
775 FROM MTL_PARAMETERS MP
776 WHERE MP.ORGANIZATION_CODE = UAI.ORGANIZATION_CODE)
777 WHERE UAI.DATA_SET_ID = p_data_set_id
778 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
779 AND UAI.ORGANIZATION_CODE IS NOT NULL
780 AND UAI.ORGANIZATION_ID IS NULL;
781 /* AND EXISTS (SELECT MP2.ORGANIZATION_ID
782 FROM MTL_PARAMETERS MP2
783 WHERE MP2.ORGANIZATION_CODE = UAI.ORGANIZATION_CODE
784 AND ((UAI.REVISION_ID IS NOT NULL
785 OR
786 UAI.REVISION IS NOT NULL)
787 OR
788 MP2.ORGANIZATION_ID = MP2.MASTER_ORGANIZATION_ID));*/
789
790 ------------------------------------------------------------------------------
791 -- Finally, mark as errors all rows that are in the same logical Attribute --
792 -- Group row as any row whose Org Code doesn't correspond to a valid Master --
793 -- Org ID (marking errors as we go avoids further processing of bad rows) --
794 ------------------------------------------------------------------------------
795 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
796 SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_CODE
797 WHERE UAI.DATA_SET_ID = p_data_set_id
798 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
799 AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
800 UAI2.ROW_IDENTIFIER
801 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
802 WHERE UAI2.DATA_SET_ID = p_data_set_id
803 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
804 AND UAI2.ORGANIZATION_ID IS NULL);
805
806 --------------------------------------------
807 -- 2). Validate passed-in Inventory Item --
808 -- IDs and convert passed-in Item Numbers --
809 --------------------------------------------
810 IF (p_debug_level > 0) THEN
811 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Item Number validation/conversion', 0);
812 END IF;
813
814 ----------------------------------------------------------------------------
815 -- First, verify that all passed-in Inventory Item IDs belong to existing --
816 -- Items; if any row has an invalid Item ID, error it out along with all --
817 -- other rows in its logical Attribute Group row --
818 ----------------------------------------------------------------------------
819 IF p_validate_only = FND_API.G_FALSE THEN
820 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
821 SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_ID
822 WHERE UAI.DATA_SET_ID = p_data_set_id
823 AND UAI.PROCESS_STATUS IN (G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS)
824 AND UAI.INVENTORY_ITEM_ID IS NOT NULL
825 AND NOT EXISTS (SELECT 'X'
826 FROM MTL_SYSTEM_ITEMS_B MSIB
827 WHERE MSIB.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
828 AND MSIB.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
829 ELSE
830 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
831 SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_ID
832 WHERE UAI.DATA_SET_ID = p_data_set_id
833 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
834 AND UAI.INVENTORY_ITEM_ID IS NOT NULL
835 AND NOT EXISTS (SELECT 'X'
836 FROM MTL_SYSTEM_ITEMS_B MSIB
837 WHERE MSIB.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
838 AND MSIB.ORGANIZATION_ID = UAI.ORGANIZATION_ID
839 UNION ALL
840 SELECT 'X'
841 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
842 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
843 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
844 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
845 AND MSII.PROCESS_FLAG = 1
846 AND MSII.TRANSACTION_TYPE = 'CREATE');
847 END IF;
848
849 -------------------------------------------------------
850 -- Next, convert Item Number into Item ID for those --
851 -- rows where the user only passed in an Item Number --
852 -- (Note that we only convert any Item Number/Org ID --
853 -- combination once and then set the Item ID we find --
854 -- to all rows with the same Item Number and Org ID) --
855 -------------------------------------------------------
856 IF p_validate_only = FND_API.G_FALSE THEN
857 UPDATE EGO_ITM_USR_ATTR_INTRFC intrfc
858 SET INVENTORY_ITEM_ID =
859 (SELECT INVENTORY_ITEM_ID
860 FROM MTL_SYSTEM_ITEMS_B_KFV
861 WHERE CONCATENATED_SEGMENTS = intrfc.ITEM_NUMBER
862 AND ORGANIZATION_ID = intrfc.ORGANIZATION_ID)
863 WHERE DATA_SET_ID = p_data_set_id
864 AND PROCESS_STATUS = G_PS_IN_PROCESS
865 AND ITEM_NUMBER IS NOT NULL
866 AND INVENTORY_ITEM_ID IS NULL;
867 ELSE
868 UPDATE EGO_ITM_USR_ATTR_INTRFC intrfc
869 SET INVENTORY_ITEM_ID =
870 NVL( (SELECT INVENTORY_ITEM_ID
871 FROM MTL_SYSTEM_ITEMS_B_KFV
872 WHERE CONCATENATED_SEGMENTS = intrfc.ITEM_NUMBER
873 AND ORGANIZATION_ID = intrfc.ORGANIZATION_ID
874 ),
875 (SELECT INVENTORY_ITEM_ID
876 FROM MTL_SYSTEM_ITEMS_INTERFACE msii
877 WHERE msii.SET_PROCESS_ID = intrfc.DATA_SET_ID
878 AND msii.PROCESS_FLAG = 1
879 AND (msii.ITEM_NUMBER = intrfc.ITEM_NUMBER OR msii.SOURCE_SYSTEM_REFERENCE = intrfc.SOURCE_SYSTEM_REFERENCE)
880 AND msii.ORGANIZATION_ID = intrfc.ORGANIZATION_ID
881 AND ROWNUM = 1
882 )
883 )
884 WHERE DATA_SET_ID = p_data_set_id
885 AND PROCESS_STATUS = G_PS_IN_PROCESS
886 AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL)
887 AND INVENTORY_ITEM_ID IS NULL;
888 END IF;
889
890 ----------------------------------------------------------------
891 -- As with the Organization step, we mark as errors all rows --
892 -- that share the same logical Attribute Group row with any --
893 -- rows where we didn't end up with a valid Inventory Item ID --
894 ----------------------------------------------------------------
895 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
896 SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_NUMBER
897 WHERE UAI.DATA_SET_ID = p_data_set_id
898 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
899 AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
900 UAI2.ROW_IDENTIFIER
901 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
902 WHERE UAI2.DATA_SET_ID = p_data_set_id
903 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
904 AND UAI2.INVENTORY_ITEM_ID IS NULL);
905
906 -----------------------------------------
907 -- 3). Validate passed-in Revision IDs --
908 -- and convert passed-in Revisions --
909 -----------------------------------------
910 IF (p_debug_level > 0) THEN
911 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Revision conversion', 0);
912 END IF;
913
914 ---------------------------------------------------------------
915 -- First, verify that all passed-in Revision IDs are valid; --
916 -- if any row has an invalid Revision ID, error it out along --
917 -- with all other rows in its logical Attribute Group row --
918 ---------------------------------------------------------------
919 IF p_validate_only = FND_API.G_FALSE THEN
920 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
921 SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_ID
922 WHERE UAI.DATA_SET_ID = p_data_set_id
923 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
924 AND UAI.REVISION_ID IS NOT NULL
925 AND NOT EXISTS (SELECT 'X'
926 FROM MTL_ITEM_REVISIONS MIR
927 WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
928 AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
929 AND MIR.REVISION_ID = UAI.REVISION_ID);
930 ELSE
931 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
932 SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_ID
933 WHERE UAI.DATA_SET_ID = p_data_set_id
934 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
935 AND UAI.REVISION_ID IS NOT NULL
936 AND NOT EXISTS (SELECT 'X'
937 FROM MTL_ITEM_REVISIONS MIR
938 WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
939 AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
940 AND MIR.REVISION_ID = UAI.REVISION_ID
941 UNION ALL
942 SELECT 'X'
943 FROM MTL_ITEM_REVISIONS_INTERFACE miri
944 WHERE miri.SET_PROCESS_ID = UAI.DATA_SET_ID
945 AND miri.PROCESS_FLAG = 1
946 AND miri.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
947 AND miri.ORGANIZATION_ID = UAI.ORGANIZATION_ID
948 AND miri.REVISION_ID = UAI.REVISION_ID
949 );
950 END IF;
951
952 ----------------------------------------------------------------
953 -- Next, convert Revision to Revision ID for those rows where --
954 -- the user passed in the Revision (note that by "Revision", --
955 -- we mean the Revision *Code* and not the Revision Label) --
956 ----------------------------------------------------------------
957 IF p_validate_only = FND_API.G_FALSE THEN
958 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
959 SET UAI.REVISION_ID = (SELECT MIR.REVISION_ID
960 FROM MTL_ITEM_REVISIONS MIR
961 WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
962 AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
963 AND MIR.REVISION = UAI.REVISION)
964 WHERE UAI.DATA_SET_ID = p_data_set_id
965 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
966 AND UAI.REVISION IS NOT NULL
967 AND UAI.REVISION_ID IS NULL
968 AND EXISTS (SELECT MIR2.REVISION_ID
969 FROM MTL_ITEM_REVISIONS MIR2
970 WHERE MIR2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
971 AND MIR2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
972 AND MIR2.REVISION = UAI.REVISION);
973 ELSE
974 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
975 SET UAI.REVISION_ID = NVL( (SELECT MIR.REVISION_ID
976 FROM MTL_ITEM_REVISIONS MIR
977 WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
978 AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
979 AND MIR.REVISION = UAI.REVISION
980 ),
981 (SELECT miri.REVISION_ID
982 FROM MTL_ITEM_REVISIONS_INTERFACE miri
983 WHERE miri.SET_PROCESS_ID = UAI.DATA_SET_ID
984 AND miri.PROCESS_FLAG = 1
985 AND miri.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
986 AND miri.ORGANIZATION_ID = UAI.ORGANIZATION_ID
987 AND miri.REVISION = UAI.REVISION
988 AND ROWNUM = 1
989 )
990 )
991 WHERE UAI.DATA_SET_ID = p_data_set_id
992 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
993 AND UAI.REVISION IS NOT NULL
994 AND UAI.REVISION_ID IS NULL
995 AND EXISTS (SELECT 'X'
996 FROM MTL_ITEM_REVISIONS MIR2
997 WHERE MIR2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
998 AND MIR2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
999 AND MIR2.REVISION = UAI.REVISION
1000 UNION ALL
1001 SELECT 'X'
1002 FROM MTL_ITEM_REVISIONS_INTERFACE miri
1003 WHERE miri.SET_PROCESS_ID = UAI.DATA_SET_ID
1004 AND miri.PROCESS_FLAG = 1
1005 AND miri.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1006 AND miri.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1007 AND miri.REVISION = UAI.REVISION
1008 );
1009 END IF;
1010
1011
1012 -------------------------------------------------------------------------
1013 -- Mark as errors all rows that share the same logical Attribute Group --
1014 -- row with any rows where we started with a Revision and didn't end --
1015 -- up with a valid Revision ID (because many rows may not have either --
1016 -- a Revision or a Revision ID, and that is not an error condition) --
1017 -------------------------------------------------------------------------
1018 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1019 SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_CODE
1020 WHERE UAI.DATA_SET_ID = p_data_set_id
1021 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1022 AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1023 UAI2.ROW_IDENTIFIER
1024 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1025 WHERE UAI2.DATA_SET_ID = p_data_set_id
1026 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1027 AND UAI2.REVISION IS NOT NULL
1028 AND UAI2.REVISION_ID IS NULL);
1029
1030 ---------------------------------------------------------
1031 -- 4). Find the Item Catalog Group ID for each Item --
1032 ---------------------------------------------------------
1033 IF (p_debug_level > 0) THEN
1034 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Catalog Group ID conversion', 0);
1035 END IF;
1036
1037 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After init');
1038 IF p_validate_only = FND_API.G_FALSE THEN
1039 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1040 SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
1041 UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1042 = (SELECT NVL(MSI.ITEM_CATALOG_GROUP_ID,-1), NVL(MSI.APPROVAL_STATUS,'A'),
1043 MSI.LIFECYCLE_ID, MSI.CURRENT_PHASE_ID
1044 FROM MTL_SYSTEM_ITEMS_B MSI
1045 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1046 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1047 ),
1048 PROG_INT_CHAR2 =
1049 NVL((SELECT 'Y'
1050 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1051 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1052 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1053 AND MSII.REQUEST_ID = UAI.REQUEST_ID
1054 AND MSII.TRANSACTION_TYPE = 'CREATE'
1055 AND MSII.PROCESS_FLAG = 7
1056 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1057 ),PROG_INT_CHAR2)
1058 WHERE UAI.DATA_SET_ID = p_data_set_id
1059 AND PROCESS_STATUS = G_PS_IN_PROCESS
1060 AND EXISTS (SELECT MSI.ITEM_CATALOG_GROUP_ID
1061 FROM MTL_SYSTEM_ITEMS_B MSI
1062 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1063 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
1064 ELSE
1065 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1066 SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
1067 UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1068 = (SELECT NVL(MSI.ITEM_CATALOG_GROUP_ID,-1), NVL(MSI.APPROVAL_STATUS,'A'),
1069 MSI.LIFECYCLE_ID, MSI.CURRENT_PHASE_ID
1070 FROM MTL_SYSTEM_ITEMS_B MSI
1071 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1072 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1073 ),
1074 PROG_INT_CHAR2 =
1075 NVL((SELECT 'Y'
1076 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1077 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1078 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1079 AND MSII.TRANSACTION_TYPE = 'CREATE'
1080 AND MSII.PROCESS_FLAG = 1
1081 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1082 AND ROWNUM = 1
1083 ),PROG_INT_CHAR2)
1084 WHERE UAI.DATA_SET_ID = p_data_set_id
1085 AND PROCESS_STATUS = G_PS_IN_PROCESS
1086 AND EXISTS (SELECT MSI.ITEM_CATALOG_GROUP_ID
1087 FROM MTL_SYSTEM_ITEMS_B MSI
1088 WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1089 AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
1090
1091 -- If item is not found in production then get the values from interface
1092 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1093 SET (UAI.ITEM_CATALOG_GROUP_ID,
1094 UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1095 = (SELECT
1096 NVL( NVL(UAI.ITEM_CATALOG_GROUP_ID, MSII.ITEM_CATALOG_GROUP_ID), -1),
1097 NVL( UAI.PROG_INT_NUM2, MSII.LIFECYCLE_ID),
1098 NVL( UAI.PROG_INT_NUM3, MSII.CURRENT_PHASE_ID)
1099 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1100 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1101 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1102 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1103 AND MSII.PROCESS_FLAG = 1
1104 AND ROWNUM = 1
1105 ),
1106 PROG_INT_CHAR2 =
1107 NVL((SELECT 'Y'
1108 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
1109 WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1110 AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1111 AND MSII.TRANSACTION_TYPE = 'CREATE'
1112 AND MSII.PROCESS_FLAG = 1
1113 AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
1114 AND ROWNUM = 1
1115 ),PROG_INT_CHAR2)
1116 WHERE UAI.DATA_SET_ID = p_data_set_id
1117 AND PROCESS_STATUS = G_PS_IN_PROCESS
1118 AND EXISTS (SELECT 'X'
1119 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
1120 WHERE MSII2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1121 AND MSII2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1122 AND MSII2.SET_PROCESS_ID = UAI.DATA_SET_ID
1123 AND MSII2.PROCESS_FLAG = 1
1124 );
1125 END IF;
1126 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After item init');
1127
1128 ----------------------------------------------------------------------------
1129 -- Mark as errors all rows that share the same logical Attribute Group --
1130 -- row with any rows where we didn't end up with a valid Catalog Group ID --
1131 ----------------------------------------------------------------------------
1132 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1133 SET UAI.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID
1134 WHERE UAI.DATA_SET_ID = p_data_set_id
1135 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1136 AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1137 UAI2.ROW_IDENTIFIER
1138 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1139 WHERE UAI2.DATA_SET_ID = p_data_set_id
1140 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1141 AND UAI2.ITEM_CATALOG_GROUP_ID IS NULL);
1142
1143 ---------------------------------------------------------
1144 -- Find the Attr Group Type for Attribute Name --
1145 ---------------------------------------------------------
1146 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1147 SET ATTR_GROUP_TYPE = NVL(ATTR_GROUP_TYPE,(SELECT DESCRIPTIVE_FLEXFIELD_NAME
1148 FROM EGO_FND_DSC_FLX_CTX_EXT
1149 WHERE APPLICATION_ID = 431
1150 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME
1151 AND ROWNUM = 1))
1152 ,PROCESS_STATUS = DECODE((SELECT COUNT(DESCRIPTIVE_FLEXFIELD_NAME)
1153 FROM EGO_FND_DSC_FLX_CTX_EXT
1154 WHERE APPLICATION_ID = 431
1155 AND (UAI.ATTR_GROUP_TYPE IS NULL OR UAI.ATTR_GROUP_TYPE=DESCRIPTIVE_FLEXFIELD_NAME)
1156 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME),
1157 1,PROCESS_STATUS
1158 ,G_PS_BAD_ATTR_GROUP_NAME)
1159 WHERE UAI.DATA_SET_ID = p_data_set_id
1160 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1161 AND UAI.ATTR_GROUP_ID IS NULL;
1162
1163 ---------------------------------------------------------
1164 -- Find the Bad Attr Group Id for Attribute Name --
1165 ---------------------------------------------------------
1166 IF (p_debug_level > 0) THEN
1167 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Attr Group ID validation', 0);
1168 END IF;
1169 ----------------------------------------------------------------------------
1170 -- Note: Attribute Internal Name take precidence over Attribute Group Id --
1171 ----------------------------------------------------------------------------
1172 -- to do check the performance cost 35
1173 -- UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1174 -- SET UAI.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_ID
1175 -- WHERE UAI.DATA_SET_ID = p_data_set_id
1176 -- AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1177 -- AND UAI.ATTR_GROUP_ID IS NOT NULL
1178 -- AND UAI.ATTR_GROUP_ID <> ( SELECT ATTR_GROUP_ID
1179 -- FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
1180 -- WHERE APPLICATION_ID = 431
1181 -- AND DESCRIPTIVE_FLEXFIELD_NAME = l_attr_group_type
1182 -- AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME);
1183
1184 -- to do check the performance cost 3
1185 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1186 SET UAI.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID
1187 WHERE UAI.DATA_SET_ID = p_data_set_id
1188 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1189 AND UAI.ATTR_GROUP_ID IS NOT NULL
1190 AND NOT EXISTS
1191 ( SELECT 'X'
1192 FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
1193 WHERE APPLICATION_ID = 431
1194 AND DESCRIPTIVE_FLEXFIELD_NAME = UAI.ATTR_GROUP_TYPE --l_attr_group_type
1195 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME
1196 AND ATTR_GROUP_ID = UAI.ATTR_GROUP_ID);
1197
1198 ---------------------------------------------------------
1199 -- Find the Attr Group Id for Attribute Name --
1200 ---------------------------------------------------------
1201 IF (p_debug_level > 0) THEN
1202 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Attr Group ID conversion', 0);
1203 END IF;
1204 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1205 SET ATTR_GROUP_ID = (SELECT ATTR_GROUP_ID
1206 FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
1207 WHERE APPLICATION_ID = 431
1208 AND DESCRIPTIVE_FLEXFIELD_NAME = UAI.ATTR_GROUP_TYPE --l_attr_group_type
1209 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME)
1210 WHERE UAI.DATA_SET_ID = p_data_set_id
1211 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1212 AND UAI.ATTR_GROUP_ID IS NULL;
1213
1214 ------------------------------------------------------------------------------
1215 -- Mark as errors all rows that didn't end up with a valid Attr Group ID --
1216 ------------------------------------------------------------------------------
1217
1218 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1219 SET UAI.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_NAME
1220 WHERE UAI.DATA_SET_ID = p_data_set_id
1221 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1222 AND UAI.ATTR_GROUP_ID IS NULL;
1223
1224 -------------------------------------------------------------------------
1225 -- Erase Revision information for Attr Groups associated at ITEM_LEVEL --
1226 -------------------------------------------------------------------------
1227 UPDATE EGO_ITM_USR_ATTR_INTRFC
1228 SET REVISION = NULL, REVISION_ID = NULL
1229 WHERE ROWID IN (SELECT I.ROWID
1230 FROM EGO_OBJ_AG_ASSOCS_B A,
1231 EGO_ITM_USR_ATTR_INTRFC I
1232 WHERE A.CLASSIFICATION_CODE = I.ITEM_CATALOG_GROUP_ID
1233 AND A.OBJECT_ID = G_ITEM_OBJECT_ID
1234 AND A.ATTR_GROUP_ID = I.ATTR_GROUP_ID
1235 AND A.DATA_LEVEL = 'ITEM_LEVEL'
1236 AND I.DATA_SET_ID = p_data_set_id
1237 AND I.PROCESS_STATUS = G_PS_IN_PROCESS);
1238 /*
1239 ------------------------------------------------------------------------------
1240 -- Verify that all passed-in Organization IDs belong to Master Orgs --
1241 -- if they are for AGs associated at the Item level and that the Org IDs at --
1242 -- least exist for AGs associated at the Item Revision level. --
1243 -- if any row has an invalid Org ID, error it out along with all other rows --
1244 -- in its logical Attribute Group row (because it won't make sense to keep --
1245 -- processing the errored-out row's companions without the errored-out row) --
1246 ------------------------------------------------------------------------------
1247 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1248 SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_ID
1249 WHERE UAI.DATA_SET_ID = p_data_set_id
1250 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1251 AND UAI.ROW_IDENTIFIER IN
1252 (SELECT DISTINCT UAI2.ROW_IDENTIFIER
1253 FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1254 WHERE UAI2.DATA_SET_ID = p_data_set_id
1255 AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1256 AND UAI2.ORGANIZATION_ID IS NOT NULL
1257 AND NOT EXISTS
1258 (SELECT 'X'
1259 FROM MTL_PARAMETERS MP
1260 WHERE MP.ORGANIZATION_ID = UAI2.ORGANIZATION_ID
1261 AND (UAI2.REVISION_ID IS NOT NULL
1262 OR
1263 UAI2.REVISION IS NOT NULL
1264 OR
1265 MP.MASTER_ORGANIZATION_ID = UAI2.ORGANIZATION_ID
1266 )
1267 )
1268 );
1269
1270 */
1271
1272 ----------------------------------------------------
1273 -- Validate and convert data level entered --
1274 -- First convert DATA_LEVEL_NAME to data_level_id --
1275 -- where data_level_id is not populated --
1276 ----------------------------------------------------
1277 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1278 SET uai.DATA_LEVEL_ID = (SELECT edlb.DATA_LEVEL_ID
1279 FROM EGO_DATA_LEVEL_B edlb
1280 WHERE edlb.DATA_LEVEL_NAME = uai.DATA_LEVEL_NAME
1281 AND edlb.APPLICATION_ID = 431
1282 AND edlb.ATTR_GROUP_TYPE = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
1283 )
1284 WHERE uai.DATA_SET_ID = p_data_set_id
1285 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1286 AND uai.DATA_LEVEL_NAME IS NOT NULL
1287 AND uai.DATA_LEVEL_ID IS NULL;
1288
1289
1290 ----------------------------------------------------------
1291 -- Then convert USER_DATA_LEVEL_NAME to --
1292 -- data_level_id where data_level_id is not --
1293 -- populated and data_level_name is also not populated --
1294 ----------------------------------------------------------
1295 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1296 SET uai.DATA_LEVEL_ID = (SELECT edlv.DATA_LEVEL_ID
1297 FROM EGO_DATA_LEVEL_VL edlv
1298 WHERE edlv.USER_DATA_LEVEL_NAME = uai.USER_DATA_LEVEL_NAME
1299 AND edlv.APPLICATION_ID = 431
1300 AND edlv.ATTR_GROUP_TYPE = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
1301 )
1302 WHERE uai.DATA_SET_ID = p_data_set_id
1303 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1304 AND uai.USER_DATA_LEVEL_NAME IS NOT NULL
1305 AND uai.DATA_LEVEL_NAME IS NULL
1306 AND uai.DATA_LEVEL_ID IS NULL;
1307
1308
1309 -----------------------------------------------------------
1310 -- If all data level columns are null, then check if the --
1311 -- attribute group is associated at only one level, then --
1312 -- put that data level id here. --
1313 -----------------------------------------------------------
1314 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1315 SET DATA_LEVEL_ID = (SELECT DATA_LEVEL_ID
1316 FROM EGO_ATTR_GROUP_DL
1317 WHERE ATTR_GROUP_ID = uai.ATTR_GROUP_ID
1318 )
1319 WHERE uai.DATA_SET_ID = p_data_set_id
1320 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1321 AND uai.DATA_LEVEL_ID IS NULL
1322 AND uai.DATA_LEVEL_NAME IS NULL
1323 AND uai.USER_DATA_LEVEL_NAME IS NULL
1324 AND (SELECT COUNT(*)
1325 FROM EGO_ATTR_GROUP_DL
1326 WHERE ATTR_GROUP_ID = uai.ATTR_GROUP_ID) = 1;
1327
1328
1329 -------------------------------------------------
1330 -- Now, mark all the rows that does not have a --
1331 -- valid data_level_id populated --
1332 -------------------------------------------------
1333 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1334 SET uai.PROCESS_STATUS = G_PS_BAD_DATA_LEVEL
1335 WHERE uai.DATA_SET_ID = p_data_set_id
1336 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1337 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1338 uai2.ROW_IDENTIFIER
1339 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1340 WHERE uai2.DATA_SET_ID = p_data_set_id
1341 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1342 AND NOT EXISTS (SELECT NULL
1343 FROM EGO_DATA_LEVEL_B edlb
1344 WHERE edlb.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1345 )
1346 );
1347
1348 --------------------------------------------------
1349 -- Validating the Orag Id to be master org for --
1350 -- item, supplier and supplier site level --
1351 --------------------------------------------------
1352
1353 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1354 SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_ID
1355 WHERE UAI.DATA_SET_ID = p_data_set_id
1356 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1357 AND UAI.DATA_LEVEL_ID IN (43101, 43103,43104,43107,43108)
1358 AND EXISTS (SELECT MP2.ORGANIZATION_ID
1359 FROM MTL_PARAMETERS MP2
1360 WHERE MP2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1361 AND MP2.ORGANIZATION_ID <> MP2.MASTER_ORGANIZATION_ID);
1362
1363
1364
1365 ----------------------------------------------------------------
1366 -- Get data_level_ids to validate PK1_VALUE and PK2_VALUE --
1367 ----------------------------------------------------------------
1368 FOR i IN c_data_levels LOOP
1369 IF i.DATA_LEVEL_NAME = 'ITEM_SUP' THEN
1370 l_item_sup_dl_id := i.DATA_LEVEL_ID;
1371 ELSIF i.DATA_LEVEL_NAME = 'ITEM_SUP_SITE' THEN
1372 l_item_sup_site_dl_id := i.DATA_LEVEL_ID;
1373 ELSIF i.DATA_LEVEL_NAME = 'ITEM_SUP_SITE_ORG' THEN
1374 l_item_sup_site_org_dl_id := i.DATA_LEVEL_ID;
1375 END IF; -- IF i.DATA_LEVEL_NAME
1376 END LOOP; -- FOR i IN c_data_levels LOOP
1377
1378 ----------------------------------------------------------------
1379 -- Next, validate the Item Supplier attrs. Validate that the --
1380 -- pk1_value exists in ego_item_associations for this item --
1381 ----------------------------------------------------------------
1382 IF p_validate_only = FND_API.G_FALSE THEN
1383 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1384 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER
1385 WHERE uai.DATA_SET_ID = p_data_set_id
1386 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1387 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1388 UAI2.ROW_IDENTIFIER
1389 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1390 WHERE uai2.DATA_SET_ID = p_data_set_id
1391 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1392 AND uai2.DATA_LEVEL_ID = l_item_sup_dl_id
1393 AND ( uai2.PK1_VALUE IS NULL
1394 OR
1395 ( uai2.PK1_VALUE IS NOT NULL
1396 AND uai2.PK2_VALUE IS NOT NULL
1397 )
1398 OR
1399 ( uai2.PK1_VALUE IS NOT NULL
1400 AND uai2.PK2_VALUE IS NULL
1401 AND NOT EXISTS (SELECT NULL
1402 FROM EGO_ITEM_ASSOCIATIONS eia
1403 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1404 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1405 AND eia.PK1_VALUE = uai2.PK1_VALUE
1406 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1407 )
1408 )
1409 )
1410 );
1411 ELSE
1412 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1413 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER
1414 WHERE uai.DATA_SET_ID = p_data_set_id
1415 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1416 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1417 UAI2.ROW_IDENTIFIER
1418 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1419 WHERE uai2.DATA_SET_ID = p_data_set_id
1420 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1421 AND uai2.DATA_LEVEL_ID = l_item_sup_dl_id
1422 AND ( uai2.PK1_VALUE IS NULL
1423 OR
1424 ( uai2.PK1_VALUE IS NOT NULL
1425 AND uai2.PK2_VALUE IS NOT NULL
1426 )
1427 OR
1428 ( uai2.PK1_VALUE IS NOT NULL
1429 AND uai2.PK2_VALUE IS NULL
1430 AND NOT EXISTS (SELECT NULL
1431 FROM EGO_ITEM_ASSOCIATIONS eia
1432 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1433 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1434 AND eia.PK1_VALUE = uai2.PK1_VALUE
1435 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1436 UNION ALL
1437 SELECT NULL
1438 FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
1439 WHERE eiai.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1440 AND eiai.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1441 AND eiai.PK1_VALUE = uai2.PK1_VALUE
1442 AND eiai.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1443 AND eiai.BATCH_ID = uai2.DATA_SET_ID
1444 AND eiai.PROCESS_FLAG = 1
1445 )
1446 )
1447 )
1448 );
1449 END IF;
1450
1451 -----------------------------------------------------------------
1452 -- Next, validate the Item Supplier site attrs. Validate that --
1453 -- the pk2_value exists in ego_item_associations for this item --
1454 -----------------------------------------------------------------
1455 IF p_validate_only = FND_API.G_FALSE THEN
1456 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1457 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
1458 WHERE uai.DATA_SET_ID = p_data_set_id
1459 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1460 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1461 UAI2.ROW_IDENTIFIER
1462 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1463 WHERE uai2.DATA_SET_ID = p_data_set_id
1464 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1465 AND uai2.DATA_LEVEL_ID = l_item_sup_site_dl_id
1466 AND ( uai2.PK1_VALUE IS NULL
1467 OR
1468 uai2.PK2_VALUE IS NULL
1469 OR
1470 ( uai2.PK1_VALUE IS NOT NULL
1471 AND uai2.PK2_VALUE IS NOT NULL
1472 AND NOT EXISTS (SELECT NULL
1473 FROM EGO_ITEM_ASSOCIATIONS eia
1474 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1475 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1476 AND eia.PK1_VALUE = uai2.PK1_VALUE
1477 AND eia.PK2_VALUE = uai2.PK2_VALUE
1478 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1479 )
1480 )
1481 )
1482 );
1483 ELSE
1484 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1485 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
1486 WHERE uai.DATA_SET_ID = p_data_set_id
1487 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1488 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1489 UAI2.ROW_IDENTIFIER
1490 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1491 WHERE uai2.DATA_SET_ID = p_data_set_id
1492 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1493 AND uai2.DATA_LEVEL_ID = l_item_sup_site_dl_id
1494 AND ( uai2.PK1_VALUE IS NULL
1495 OR
1496 uai2.PK2_VALUE IS NULL
1497 OR
1498 ( uai2.PK1_VALUE IS NOT NULL
1499 AND uai2.PK2_VALUE IS NOT NULL
1500 AND NOT EXISTS (SELECT NULL
1501 FROM EGO_ITEM_ASSOCIATIONS eia
1502 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1503 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1504 AND eia.PK1_VALUE = uai2.PK1_VALUE
1505 AND eia.PK2_VALUE = uai2.PK2_VALUE
1506 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1507 UNION ALL
1508 SELECT NULL
1509 FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
1510 WHERE eiai.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1511 AND eiai.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1512 AND eiai.PK1_VALUE = uai2.PK1_VALUE
1513 AND eiai.PK2_VALUE = uai2.PK2_VALUE
1514 AND eiai.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1515 AND eiai.BATCH_ID = uai2.DATA_SET_ID
1516 AND eiai.PROCESS_FLAG = 1
1517 )
1518 )
1519 )
1520 );
1521 END IF;
1522
1523
1524 ------------------------------------------------------------------
1525 -- Next, validate the Item Supplier site Org attrs. Validate --
1526 -- that the pk1,pk2_value along with the organization_id exists --
1527 -- in ego_item_associations for this item --
1528 ------------------------------------------------------------------
1529 IF p_validate_only = FND_API.G_FALSE THEN
1530 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1531 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
1532 WHERE uai.DATA_SET_ID = p_data_set_id
1533 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1534 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1535 UAI2.ROW_IDENTIFIER
1536 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1537 WHERE uai2.DATA_SET_ID = p_data_set_id
1538 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1539 AND uai2.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
1540 AND ( uai2.PK1_VALUE IS NULL
1541 OR
1542 uai2.PK2_VALUE IS NULL
1543 OR
1544 ( uai2.PK1_VALUE IS NOT NULL
1545 AND uai2.PK2_VALUE IS NOT NULL
1546 AND NOT EXISTS (SELECT NULL
1547 FROM EGO_ITEM_ASSOCIATIONS eia
1548 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1549 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1550 AND eia.PK1_VALUE = uai2.PK1_VALUE
1551 AND eia.PK2_VALUE = uai2.PK2_VALUE
1552 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1553 )
1554 )
1555 )
1556 );
1557 ELSE
1558 UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1559 SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
1560 WHERE uai.DATA_SET_ID = p_data_set_id
1561 AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1562 AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1563 UAI2.ROW_IDENTIFIER
1564 FROM EGO_ITM_USR_ATTR_INTRFC uai2
1565 WHERE uai2.DATA_SET_ID = p_data_set_id
1566 AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1567 AND uai2.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
1568 AND ( uai2.PK1_VALUE IS NULL
1569 OR
1570 uai2.PK2_VALUE IS NULL
1571 OR
1572 ( uai2.PK1_VALUE IS NOT NULL
1573 AND uai2.PK2_VALUE IS NOT NULL
1574 AND NOT EXISTS (SELECT NULL
1575 FROM EGO_ITEM_ASSOCIATIONS eia
1576 WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1577 AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1578 AND eia.PK1_VALUE = uai2.PK1_VALUE
1579 AND eia.PK2_VALUE = uai2.PK2_VALUE
1580 AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1581 UNION ALL
1582 SELECT NULL
1583 FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
1584 WHERE eiai.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
1585 AND eiai.ORGANIZATION_ID = uai2.ORGANIZATION_ID
1586 AND eiai.PK1_VALUE = uai2.PK1_VALUE
1587 AND eiai.PK2_VALUE = uai2.PK2_VALUE
1588 AND eiai.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
1589 AND eiai.BATCH_ID = uai2.DATA_SET_ID
1590 AND eiai.PROCESS_FLAG = 1
1591 )
1592 )
1593 )
1594 );
1595 END IF;
1596
1597
1598 --------------------------------------------------------------------------------
1599 -- Mark as errors all rows that share the same logical Attribute Group --
1600 -- Variant attribute values for existing SKUs can not be updated --
1601 --------------------------------------------------------------------------------
1602 IF p_validate_only = FND_API.G_TRUE THEN
1603 UPDATE EGO_ITM_USR_ATTR_INTRFC
1604 SET PROCESS_STATUS = G_PS_SKU_VAR_VALUE_NOT_UPD
1605 WHERE DATA_SET_ID = p_data_set_id
1606 AND PROCESS_STATUS = G_PS_IN_PROCESS
1607 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
1608 FROM
1609 EGO_ITM_USR_ATTR_INTRFC intf,
1610 EGO_FND_DSC_FLX_CTX_EXT ag_ext,
1611 MTL_SYSTEM_ITEMS_B msib
1612 WHERE intf.DATA_SET_ID = p_data_set_id
1613 AND intf.PROCESS_STATUS = G_PS_IN_PROCESS
1614 AND intf.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
1615 AND ag_ext.VARIANT = 'Y'
1616 AND intf.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
1617 AND intf.ORGANIZATION_ID = msib.ORGANIZATION_ID
1618 AND msib.STYLE_ITEM_FLAG = 'N'
1619 AND EXISTS (SELECT NULL
1620 FROM EGO_MTL_SY_ITEMS_EXT_B ext_prod
1621 WHERE ext_prod.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
1622 AND ext_prod.ORGANIZATION_ID = msib.ORGANIZATION_ID
1623 AND ext_prod.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
1624 )
1625 );
1626
1627 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked all SKU records to error, if trying to update variant value, count='||SQL%ROWCOUNT, 1);
1628 END IF;
1629
1630 --------------------------------------------------------------------------------
1631 -- Mark as errors all rows that share the same logical Attribute Group --
1632 -- Inherited attribute values can not be processed for SKUs --
1633 --------------------------------------------------------------------------------
1634 UPDATE EGO_ITM_USR_ATTR_INTRFC
1635 SET PROCESS_STATUS = G_PS_INH_ATTR_FOR_SKU_NOT_UPD
1636 WHERE DATA_SET_ID = p_data_set_id
1637 AND PROCESS_STATUS = G_PS_IN_PROCESS
1638 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
1639 FROM
1640 EGO_ITM_USR_ATTR_INTRFC intf,
1641 EGO_ATTR_GROUP_DL eagd
1642 WHERE intf.DATA_SET_ID = p_data_set_id
1643 AND intf.PROCESS_STATUS = G_PS_IN_PROCESS
1644 AND intf.ATTR_GROUP_ID = eagd.ATTR_GROUP_ID
1645 AND intf.DATA_LEVEL_ID = eagd.DATA_LEVEL_ID
1646 AND NVL(eagd.DEFAULTING, 'D') = 'I'
1647 AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
1648 WHERE msii.SET_PROCESS_ID = intf.DATA_SET_ID
1649 AND msii.PROCESS_FLAG = 1
1650 AND msii.INVENTORY_ITEM_ID = intf.INVENTORY_ITEM_ID
1651 AND msii.ORGANIZATION_ID = intf.ORGANIZATION_ID
1652 AND msii.STYLE_ITEM_FLAG = 'N'
1653 UNION ALL
1654 SELECT NULL FROM MTL_SYSTEM_ITEMS_B msib
1655 WHERE intf.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
1656 AND intf.ORGANIZATION_ID = msib.ORGANIZATION_ID
1657 AND msib.STYLE_ITEM_FLAG = 'N'
1658 )
1659 );
1660
1661 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked all SKU records to error, if trying to update inherited attribute value, count='||SQL%ROWCOUNT, 1);
1662
1663
1664 ---------------------------------------
1665 -- Set Lifecycle of the revision items.
1666 ---------------------------------------
1667 IF p_validate_only = FND_API.G_FALSE THEN
1668 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1669 SET (UAI.PROG_INT_NUM3)
1670 = NVL((SELECT MIR.CURRENT_PHASE_ID
1671 FROM MTL_ITEM_REVISIONS MIR
1672 WHERE MIR.REVISION_ID = UAI.REVISION_ID
1673 ), UAI.PROG_INT_NUM3),
1674 PROG_INT_CHAR2 =
1675 NVL((SELECT 'Y'
1676 FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
1677 WHERE MIRI.REVISION_ID = UAI.REVISION_ID
1678 AND MIRI.request_id = UAI.REQUEST_ID
1679 AND MIRI.TRANSACTION_TYPE = 'CREATE'
1680 AND MIRI.PROCESS_FLAG = 7
1681 AND ROWNUM = 1
1682 ),PROG_INT_CHAR2)
1683 WHERE UAI.DATA_SET_ID = p_data_set_id
1684 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1685 AND UAI.REVISION_ID IS NOT NULL
1686 AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL;
1687 ELSE
1688 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1689 SET UAI.PROG_INT_NUM3
1690 = NVL((CASE WHEN EXISTS (SELECT 1
1691 FROM MTL_ITEM_REVISIONS mir
1692 WHERE mir.REVISION_ID = UAI.REVISION_ID
1693 )
1694 THEN (SELECT mir1.CURRENT_PHASE_ID
1695 FROM MTL_ITEM_REVISIONS mir1
1696 WHERE mir1.REVISION_ID = UAI.REVISION_ID
1697 )
1698 ELSE (SELECT miri.CURRENT_PHASE_ID
1699 FROM MTL_ITEM_REVISIONS_INTERFACE miri
1700 WHERE miri.REVISION_ID = UAI.REVISION_ID
1701 AND miri.SET_PROCESS_ID = UAI.DATA_SET_ID
1702 AND miri.PROCESS_FLAG = 1
1703 AND ROWNUM = 1
1704 )
1705 END
1706 ), UAI.PROG_INT_NUM3),
1707 PROG_INT_CHAR2 =
1708 NVL((SELECT 'Y'
1709 FROM MTL_ITEM_REVISIONS_INTERFACE MIRI1
1710 WHERE MIRI1.REVISION_ID = UAI.REVISION_ID
1711 AND MIRI1.SET_PROCESS_ID = UAI.DATA_SET_ID
1712 AND MIRI1.TRANSACTION_TYPE = 'CREATE'
1713 AND MIRI1.PROCESS_FLAG = 1
1714 AND ROWNUM = 1
1715 ),PROG_INT_CHAR2)
1716 WHERE UAI.DATA_SET_ID = p_data_set_id
1717 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1718 AND UAI.REVISION_ID IS NOT NULL
1719 AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL;
1720 END IF;
1721 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After rev lc');
1722
1723 ----------------------------------------------
1724 -- Set the actual cc where LC is associated --
1725 ----------------------------------------------
1726 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1727 SET UAI.PROG_INT_NUM1 =
1728 (SELECT ic.item_catalog_group_id
1729 FROM mtl_item_catalog_groups_b ic
1730 WHERE EXISTS
1731 ( SELECT olc.object_classification_code CatalogId
1732 FROM ego_obj_type_lifecycles olc
1733 WHERE olc.object_id = G_ITEM_OBJECT_ID
1734 AND olc.lifecycle_id = UAI.PROG_INT_NUM2
1735 AND olc.object_classification_code = ic.item_catalog_group_id
1736 )
1737 AND ROWNUM = 1
1738 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
1739 START WITH item_catalog_group_id = UAI.item_catalog_group_id
1740 )
1741 WHERE UAI.DATA_SET_ID = p_data_set_id
1742 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1743 AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL
1744 AND UAI.PROG_INT_NUM2 IS NOT NULL
1745 AND UAI.PROG_INT_CHAR1 = 'A'
1746 AND UAI.PROG_INT_CHAR2 = 'N';
1747 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After hier init');
1748
1749 l_policy_check_sql :=
1750 ' UPDATE EGO_ITM_USR_ATTR_INTRFC UAI '||
1751 ' SET UAI.PROCESS_STATUS = :1 '||
1752 ' WHERE UAI.DATA_SET_ID = :2 '||
1753 ' AND UAI.PROCESS_STATUS = :3 '||
1754 ' AND UAI.ROW_IDENTIFIER IN '||
1755 ' (SELECT DISTINCT UAI2.ROW_IDENTIFIER '||
1756 ' FROM EGO_ITM_USR_ATTR_INTRFC UAI2, ENG_CHANGE_POLICIES_V ECP '||
1757 ' WHERE UAI2.DATA_SET_ID = :4 '||
1758 ' AND UAI2.PROCESS_STATUS = :5 '||
1759 ' AND UAI2.ITEM_CATALOG_GROUP_ID IS NOT NULL '||
1760 ' AND UAI2.PROG_INT_NUM2 IS NOT NULL '||
1761 ' AND UAI2.PROG_INT_CHAR1 = ''A''' ||
1762 ' AND UAI2.PROG_INT_CHAR2 = ''N''' ||
1763 ' AND ECP.ATTRIBUTE_OBJECT_NAME = ''EGO_CATALOG_GROUP'' '||
1764 ' AND ECP.ATTRIBUTE_CODE = ''ATTRIBUTE_GROUP'' '||
1765 ' AND ECP.POLICY_OBJECT_NAME = ''CATALOG_LIFECYCLE_PHASE'' '||
1766 ' AND ECP.POLICY_CHAR_VALUE IS NOT NULL '||
1767 ' AND ECP.POLICY_CHAR_VALUE = :6 '||
1768 ' AND ECP.ATTRIBUTE_NUMBER_VALUE = UAI2.ATTR_GROUP_ID '||
1769 ' AND ECP.POLICY_OBJECT_PK1_VALUE = TO_CHAR(UAI2.PROG_INT_NUM1) '||
1770 ' AND ECP.POLICY_OBJECT_PK2_VALUE = TO_CHAR(UAI2.PROG_INT_NUM2) '||
1771 ' AND ECP.POLICY_OBJECT_PK3_VALUE = TO_CHAR(UAI2.PROG_INT_NUM3) '||
1772 ' AND DATA_LEVEL_ID IN ( SELECT DATA_LEVEL_ID FROM EGO_DATA_LEVEL_B '||
1773 ' WHERE APPLICATION_ID = ''431'' '||
1774 ' AND DATA_LEVEL_NAME IN ( ''ITEM_LEVEL'', ''ITEM_REVISION_LEVEL'', ''ITEM_ORG'')) '||
1775 ' )';
1776
1777
1778
1779 IF (p_debug_level > 0) THEN
1780 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Change Policy conversion/erroring', 0);
1781 END IF;
1782 -------------------------------------------------------------------------------
1783 -- 5). Mark as errors all rows that share the same logical Attribute Group --
1784 -- row with any rows for which the Change Policy is defined as NOT_ALLOWED --
1785 -- (we do not process such rows; they cannot be modified); --
1786 -- the exception to this is rows for pending Items, which we still processed --
1787 -------------------------------------------------------------------------------
1788
1789 BEGIN
1790 l_policy_check_name := 'NOT_ALLOWED';
1791 EXECUTE IMMEDIATE l_policy_check_sql USING G_PS_CHG_POLICY_NOT_ALLOWED,
1792 p_data_set_id,
1793 G_PS_IN_PROCESS,
1794 p_data_set_id,
1795 G_PS_IN_PROCESS,
1796 l_policy_check_name;
1797 EXCEPTION
1798 WHEN OTHERS THEN
1799 NULL;
1800 END;
1801
1802 -------------------------------------------------------------------------------
1803 -- Processing variant attribute groups for Style item --
1804 -- Assumption is that STYLE_ITEM_FLAG in MSII will be populated always --
1805 -- before this call, even for items that exists in production --
1806 -- We are changing the process_status of all the variant attribute groups --
1807 -- to G_PS_STYLE_VARIANT_IN_PROCESS so that they are not picked up by --
1808 -- UDA bulkloader. --
1809 -------------------------------------------------------------------------------
1810 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Style value set processing', 1);
1811
1812 UPDATE EGO_ITM_USR_ATTR_INTRFC INTF
1813 SET PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
1814 WHERE INTF.DATA_SET_ID = p_data_set_id
1815 AND INTF.PROCESS_STATUS = G_PS_IN_PROCESS
1816 AND EXISTS (SELECT NULL
1817 FROM EGO_FND_DSC_FLX_CTX_EXT AG_EXT, MTL_SYSTEM_ITEMS_INTERFACE MSII
1818 WHERE AG_EXT.VARIANT = 'Y'
1819 AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL(INTF.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
1820 AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = INTF.ATTR_GROUP_INT_NAME
1821 AND INTF.ORGANIZATION_ID = MSII.ORGANIZATION_ID
1822 AND MSII.STYLE_ITEM_FLAG = 'Y'
1823 AND INTF.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
1824 UNION ALL
1825 SELECT NULL
1826 FROM EGO_FND_DSC_FLX_CTX_EXT AG_EXT1, MTL_SYSTEM_ITEMS_B MSIB
1827 WHERE AG_EXT1.VARIANT = 'Y'
1828 AND AG_EXT1.DESCRIPTIVE_FLEXFIELD_NAME = NVL(INTF.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
1829 AND AG_EXT1.DESCRIPTIVE_FLEX_CONTEXT_CODE = INTF.ATTR_GROUP_INT_NAME
1830 AND INTF.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
1831 AND MSIB.STYLE_ITEM_FLAG = 'Y'
1832 AND INTF.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
1833 );
1834
1835 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Updated Style records count='||SQL%ROWCOUNT, 1);
1836
1837 -------------------------------------------------------------------------------
1838 -- Mark as errors all rows that share the same logical Attribute Group --
1839 -- Associated value set can not be changed if any sku exists for the style --
1840 -------------------------------------------------------------------------------
1841 UPDATE EGO_ITM_USR_ATTR_INTRFC
1842 SET PROCESS_STATUS = G_PS_VAR_VSET_CHG_NOT_ALLOWED
1843 WHERE DATA_SET_ID = p_data_set_id
1844 AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
1845 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
1846 FROM EGO_ITM_USR_ATTR_INTRFC INTF, MTL_SYSTEM_ITEMS_KFV MSIK
1847 WHERE INTF.DATA_SET_ID = p_data_set_id
1848 AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
1849 AND MSIK.STYLE_ITEM_ID = INTF.INVENTORY_ITEM_ID
1850 AND MSIK.ORGANIZATION_ID = INTF.ORGANIZATION_ID
1851 );
1852
1853 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Updated records to error where SKU exists for style, count='||SQL%ROWCOUNT, 1);
1854
1855 -------------------------------------------------------------------------------
1856 -- Converting all the value set names entered by user to value set id --
1857 -- Value set name is expected in ATTR_DISP_VALUE column, we convert it to --
1858 -- value set id and store it in ATTR_VALUE_NUM col. --
1859 -------------------------------------------------------------------------------
1860 UPDATE EGO_ITM_USR_ATTR_INTRFC INTF
1861 SET ATTR_VALUE_NUM = (SELECT VALUE_SET_ID FROM EGO_VALUE_SETS_V VS
1862 WHERE VALUE_SET_NAME = INTF.ATTR_DISP_VALUE)
1863 WHERE INTF.DATA_SET_ID = p_data_set_id
1864 AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
1865 AND INTF.ATTR_DISP_VALUE IS NOT NULL;
1866
1867
1868 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Converted value set name to value set Id for styles, count='||SQL%ROWCOUNT, 1);
1869 --------------------------------------------------------------------------------
1870 -- Mark as errors all rows that share the same logical Attribute Group --
1871 -- If not a valid value set i.e. Only value set associated at attribute level --
1872 -- or its child value set can be associated. --
1873 --------------------------------------------------------------------------------
1874 UPDATE EGO_ITM_USR_ATTR_INTRFC
1875 SET PROCESS_STATUS = G_PS_BAD_STYLE_VAR_VALUE_SET
1876 WHERE DATA_SET_ID = p_data_set_id
1877 AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
1878 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
1879 FROM EGO_ITM_USR_ATTR_INTRFC INTF
1880 WHERE INTF.DATA_SET_ID = p_data_set_id
1881 AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
1882 AND NOT EXISTS (SELECT NULL
1883 FROM FND_DESCR_FLEX_COLUMN_USAGES FL_COL
1884 WHERE FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = INTF.ATTR_GROUP_TYPE
1885 AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = INTF.ATTR_GROUP_INT_NAME
1886 AND FL_COL.END_USER_COLUMN_NAME = INTF.ATTR_INT_NAME
1887 AND FL_COL.APPLICATION_ID = 431
1888 AND (INTF.ATTR_VALUE_NUM = FL_COL.FLEX_VALUE_SET_ID
1889 OR INTF.ATTR_VALUE_NUM IN (SELECT VS.VALUE_SET_ID
1890 FROM EGO_VALUE_SET_EXT VS
1891 WHERE VS.PARENT_VALUE_SET_ID = FL_COL.FLEX_VALUE_SET_ID)
1892 )
1893 )
1894 );
1895
1896 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked error for invalid value sets, for styles, count='||SQL%ROWCOUNT, 1);
1897
1898 --------------------------------------------------------------------------------
1899 -- Processing Style variant value sets. Using a merge statement so that bulk --
1900 -- feature can be used --
1901 --------------------------------------------------------------------------------
1902 IF p_validate_only = FND_API.G_FALSE THEN
1903 MERGE INTO EGO_STYLE_VARIANT_ATTR_VS ESVAV
1904 USING (SELECT
1905 INTF.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
1906 INTF.ATTR_VALUE_NUM AS VALUE_SET_ID,
1907 ATTR.ATTR_ID AS ATTRIBUTE_ID
1908 FROM
1909 EGO_ITM_USR_ATTR_INTRFC INTF,
1910 EGO_FND_DF_COL_USGS_EXT ATTR,
1911 FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
1912 MTL_SYSTEM_ITEMS_B MSIB
1913 WHERE INTF.ATTR_GROUP_TYPE = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
1914 AND INTF.ATTR_GROUP_INT_NAME = ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE
1915 AND ATTR.APPLICATION_ID = 431
1916 AND ATTR.APPLICATION_ID = FL_COL.APPLICATION_ID
1917 AND ATTR.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
1918 AND ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
1919 AND ATTR.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
1920 AND INTF.ATTR_INT_NAME = FL_COL.END_USER_COLUMN_NAME
1921 AND INTF.DATA_SET_ID = p_data_set_id
1922 AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
1923 AND MSIB.INVENTORY_ITEM_ID = INTF.INVENTORY_ITEM_ID
1924 AND MSIB.ORGANIZATION_ID = INTF.ORGANIZATION_ID) INTRFC
1925 ON (ESVAV.INVENTORY_ITEM_ID = INTRFC.INVENTORY_ITEM_ID
1926 AND ESVAV.ATTRIBUTE_ID = INTRFC.ATTRIBUTE_ID)
1927 WHEN MATCHED THEN
1928 UPDATE SET ESVAV.VALUE_SET_ID = INTRFC.VALUE_SET_ID
1929 WHEN NOT MATCHED THEN
1930 INSERT
1931 (
1932 INVENTORY_ITEM_ID,
1933 VALUE_SET_ID,
1934 ATTRIBUTE_ID,
1935 LAST_UPDATE_LOGIN,
1936 CREATION_DATE,
1937 CREATED_BY,
1938 LAST_UPDATE_DATE,
1939 LAST_UPDATED_BY
1940 )
1941 VALUES
1942 (
1943 INTRFC.INVENTORY_ITEM_ID,
1944 INTRFC.VALUE_SET_ID,
1945 INTRFC.ATTRIBUTE_ID,
1946 l_login_id,
1947 SYSDATE,
1948 l_user_id,
1949 SYSDATE,
1950 l_user_id
1951 );
1952
1953 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Inserted value sets to EGO_STYLE_VARIANT_ATTR_VS, for styles, count='||SQL%ROWCOUNT, 1);
1954
1955 ---------------------------------------------------------------------------------
1956 -- Marking all these records as processed --
1957 -- All the records that are in status G_PS_STYLE_VARIANT_IN_PROCESS to success --
1958 ---------------------------------------------------------------------------------
1959 UPDATE EGO_ITM_USR_ATTR_INTRFC
1960 SET PROCESS_STATUS = G_PS_SUCCESS
1961 WHERE DATA_SET_ID = p_data_set_id
1962 AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS;
1963
1964 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked all style records to success, count='||SQL%ROWCOUNT, 1);
1965 END IF; --IF p_validate_only = FND_API.G_FALSE THEN
1966
1967
1968
1969 --========================================--
1970 -- ERROR REPORTING FOR FAILED CONVERSIONS --
1971 --========================================--
1972 IF (p_debug_level > 0) THEN
1973 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting conversion error-reporting', 0);
1974 END IF;
1975
1976 --------------------------------------------------------------------------
1977 -- We fetch representative rows marked as errors and add error messages --
1978 -- explaining the point in our conversion process at which each failed; --
1979 -- note that to avoid multiple error messages for the same missing data --
1980 -- we use DISTINCT in our cursor query and thus should only get one row --
1981 -- for each ROW_IDENTIFIER (since Org Code, Item Number, Revision and --
1982 -- Catalog Group Name should be the same for a given ROW_IDENTIFIER). --
1983 --------------------------------------------------------------------------
1984 FOR error_rec IN error_case_cursor(p_data_set_id)
1985 LOOP
1986 -- there is an error in processing.
1987 RETCODE := L_CONC_RET_STS_WARNING;
1988 ------------------------------------------------------------------------------------
1989 -- Increment our debugging row counter so we can report how many rows have failed --
1990 ------------------------------------------------------------------------------------
1991 l_debug_rowcount := l_debug_rowcount + 1;
1992 --
1993 -- get the attribute group display name
1994 --
1995 IF error_rec.PROCESS_STATUS IN (G_PS_BAD_ATTR_GROUP_ID, G_PS_BAD_ATTR_GROUP_NAME,
1996 G_PS_CHG_POLICY_NOT_ALLOWED,G_PS_DATA_LEVEL_INCORRECT,
1997 G_PS_BAD_DATA_LEVEL, G_PS_INH_ATTR_FOR_SKU_NOT_UPD)
1998 THEN
1999 l_current_attr_group_obj := EGO_USER_ATTRS_COMMON_PVT.Get_Attr_Group_Metadata(
2000 p_attr_group_id => error_rec.ATTR_GROUP_ID
2001 ,p_application_id => 431
2002 ,p_attr_group_type => error_rec.ATTR_GROUP_TYPE --l_attr_group_type
2003 ,p_attr_group_name => error_rec.ATTR_GROUP_INT_NAME
2004 );
2005 IF l_current_attr_group_obj IS NULL THEN
2006 l_current_attr_group_name := error_rec.attr_group_int_name;
2007 ELSE
2008 l_current_attr_group_name := l_current_attr_group_obj.ATTR_GROUP_DISP_NAME;
2009 END IF;
2010 END IF;
2011 -----------------------------------------------
2012 -- 1). It may be a bad Org ID or Org Code... --
2013 -----------------------------------------------
2014 IF (error_rec.PROCESS_STATUS = G_PS_BAD_ORG_ID) THEN
2015
2016 l_token_table(1).TOKEN_NAME := 'ORG_ID';
2017 l_token_table(1).TOKEN_VALUE := error_rec.ORGANIZATION_ID;
2018
2019 l_error_message_name := 'EGO_EF_BL_ORG_ID_ERR';
2020
2021 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ORG_CODE) THEN
2022
2023 l_token_table(1).TOKEN_NAME := 'ORG_CODE';
2024 l_token_table(1).TOKEN_VALUE := error_rec.ORGANIZATION_CODE;
2025
2026 l_error_message_name := 'EGO_EF_BL_ORG_CODE_ERR';
2027
2028 ---------------------------------------------------------
2029 -- 2). ...or it may be a bad Item ID or Item Number... --
2030 ---------------------------------------------------------
2031 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ITEM_ID) THEN
2032
2033 l_token_table(1).TOKEN_NAME := 'ITEM_ID';
2034 l_token_table(1).TOKEN_VALUE := error_rec.INVENTORY_ITEM_ID;
2035
2036 l_error_message_name := 'EGO_EF_BL_INV_ITEM_ID_ERR';
2037
2038 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ITEM_NUMBER) THEN
2039
2040 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2041 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2042
2043 l_error_message_name := 'EGO_EF_BL_ITEM_NUM_ERR';
2044
2045 ---------------------------------------------------------------
2046 -- 3). ...or it may be a bad Revision ID or Revision Code... --
2047 ---------------------------------------------------------------
2048 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_REVISION_ID) THEN
2049
2050 l_token_table(1).TOKEN_NAME := 'REVISION_ID';
2051 l_token_table(1).TOKEN_VALUE := error_rec.REVISION_ID;
2052
2053 l_error_message_name := 'EGO_EF_BL_REV_ID_ERR';
2054
2055 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_REVISION_CODE) THEN
2056
2057 l_token_table(1).TOKEN_NAME := 'REVISION';
2058 l_token_table(1).TOKEN_VALUE := error_rec.REVISION;
2059
2060 l_error_message_name := 'EGO_EF_BL_REV_CODE_ERR';
2061
2062 ------------------------------------------------
2063 -- 4). ...or it may be a bad Catalog Group ID --
2064 ------------------------------------------------
2065 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID) THEN
2066
2067 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2068 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2069 l_token_table(2).TOKEN_NAME := 'ORG_CODE';
2070 l_token_table(2).TOKEN_VALUE := error_rec.ORGANIZATION_CODE;
2071
2072 l_error_message_name := 'EGO_EF_BL_CAT_GROUP_ID_ERR';
2073
2074 -------------------------------------------
2075 -- . ...or it may be a bad Attr Group ID --
2076 -------------------------------------------
2077 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_ID) THEN
2078 l_token_table(1).TOKEN_NAME := 'AG_ID';
2079 l_token_table(1).TOKEN_VALUE := error_rec.ATTR_GROUP_ID;
2080 l_token_table(2).TOKEN_NAME := 'AG_NAME';
2081 l_token_table(2).TOKEN_VALUE := l_current_attr_group_name;
2082
2083 l_error_message_name := 'EGO_EF_BAD_AG_ID';
2084
2085 ---------------------------------------------
2086 -- . ...or it may be a bad Attr Group Name --
2087 ---------------------------------------------
2088 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_NAME) THEN
2089 l_token_table(1).TOKEN_NAME := 'AG_NAME';
2090 l_token_table(1).TOKEN_VALUE := l_current_attr_group_name;
2091
2092 l_error_message_name := 'EGO_EF_BAD_AG_NAME';
2093 ------------------------------------------------
2094 -- 5)...If the incorrect data level changes
2095 ------------------------------------------------
2096 ELSIF (error_rec.PROCESS_STATUS = G_PS_DATA_LEVEL_INCORRECT) THEN
2097 l_token_table(1).TOKEN_NAME := 'AG_NAME';
2098 l_token_table(1).TOKEN_VALUE := l_current_attr_group_name;
2099
2100 l_error_message_name := 'EGO_EF_DATA_LEVEL_INCORRECT';
2101 ------------------------------------------------
2102 -- 6)...If data level is not correct
2103 ------------------------------------------------
2104 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_DATA_LEVEL) THEN
2105 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2106 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2107
2108 l_token_table(2).TOKEN_NAME := 'AG_NAME';
2109 l_token_table(2).TOKEN_VALUE := l_current_attr_group_name;
2110
2111 l_error_message_name := 'EGO_EF_BAD_DATA_LEVEL';
2112 ------------------------------------------------
2113 -- 7)...If supplier is not correct
2114 ------------------------------------------------
2115 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_SUPPLIER) THEN
2116 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2117 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2118
2119 l_token_table(2).TOKEN_NAME := 'SUPPLIER';
2120 l_token_table(2).TOKEN_VALUE := error_rec.PK1_VALUE;
2121
2122 l_error_message_name := 'EGO_EF_BAD_SUPPLIER';
2123 ------------------------------------------------
2124 -- 8)...If supplier site is not correct
2125 ------------------------------------------------
2126 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE) THEN
2127 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2128 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2129
2130 l_token_table(2).TOKEN_NAME := 'SUP';
2131 l_token_table(2).TOKEN_VALUE := error_rec.PK1_VALUE;
2132
2133 l_token_table(3).TOKEN_NAME := 'SUP_SITE';
2134 l_token_table(3).TOKEN_VALUE := error_rec.PK2_VALUE;
2135
2136 l_error_message_name := 'EGO_EF_BAD_SUPPLIER_SITE';
2137 ------------------------------------------------
2138 -- 9)...If supplier is not correct
2139 ------------------------------------------------
2140 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG) THEN
2141 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2142 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2143
2144 l_token_table(2).TOKEN_NAME := 'SUP';
2145 l_token_table(2).TOKEN_VALUE := error_rec.PK1_VALUE;
2146
2147 l_token_table(3).TOKEN_NAME := 'SUP_SITE';
2148 l_token_table(3).TOKEN_VALUE := error_rec.PK2_VALUE;
2149
2150 l_error_message_name := 'EGO_EF_BAD_SUPPLIER_SITE_ORG';
2151 ------------------------------------------------
2152 -- 10)...If value set name for variant attribute
2153 -- for style item is not correct
2154 ------------------------------------------------
2155 ELSIF (error_rec.PROCESS_STATUS = G_PS_BAD_STYLE_VAR_VALUE_SET ) THEN
2156 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2157 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2158
2159 l_token_table(2).TOKEN_NAME := 'VSET_NAME';
2160 l_token_table(2).TOKEN_VALUE := error_rec.ATTR_DISP_VALUE;
2161
2162 l_error_message_name := 'EGO_BAD_VAR_VALUE_SET';
2163 -------------------------------------------------------
2164 -- 11)...If changing of variant value set is not allowed
2165 -------------------------------------------------------
2166 ELSIF (error_rec.PROCESS_STATUS = G_PS_VAR_VSET_CHG_NOT_ALLOWED ) THEN
2167 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2168 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2169
2170 l_error_message_name := 'EGO_VAR_VSET_CHG_NOT_ALLOWED';
2171 ------------------------------------------------
2172 -- 12)...If changing SKU variant attribute value
2173 -- is not allowed
2174 ------------------------------------------------
2175 ELSIF (error_rec.PROCESS_STATUS = G_PS_SKU_VAR_VALUE_NOT_UPD ) THEN
2176 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2177 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2178
2179 l_token_table(2).TOKEN_NAME := 'VALUE';
2180 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);
2181
2182 l_error_message_name := 'EGO_VAR_VALUE_CHG_NOT_ALLOWED';
2183 ------------------------------------------------
2184 -- 13)...Inherited attribute value for SKU
2185 -- is not allowed
2186 ------------------------------------------------
2187 ELSIF (error_rec.PROCESS_STATUS = G_PS_INH_ATTR_FOR_SKU_NOT_UPD ) THEN
2188 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2189 l_token_table(1).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2190
2191 l_token_table(2).TOKEN_NAME := 'AG_NAME';
2192 l_token_table(2).TOKEN_VALUE := l_current_attr_group_name;
2193
2194 l_error_message_name := 'EGO_INH_ATTR_FOR_SKU_NOT_UPD';
2195
2196 ----------------------------------------------------
2197 -- 14). ...or else the row is under Change control --
2198 ----------------------------------------------------
2199 ELSIF (error_rec.PROCESS_STATUS = G_PS_CHG_POLICY_NOT_ALLOWED ) THEN
2200 --------------------------------------------------------------
2201 -- We fetch the Attr Group metadata object (which is cached --
2202 -- by EGO_USER_ATTRS_COMMON_PVT after it's fetched once) --
2203 --------------------------------------------------------------
2204 -- decide the message based upon
2205 -- item /item revision
2206 -- change order required OR changes not allowed
2207 l_token_table(1).TOKEN_NAME := 'ATTR_GROUP_NAME';
2208 l_token_table(1).TOKEN_VALUE := l_current_attr_group_name;
2209 l_token_table(2).TOKEN_NAME := 'ITEM_NUMBER';
2210 l_token_table(2).TOKEN_VALUE := error_rec.ITEM_NUMBER;
2211 ----------------------------------------------------------------
2212 -- If we've already fetched the Catalog Group name, we reuse --
2213 -- it; otherwise, we fetch it and store it in our local table --
2214 ----------------------------------------------------------------
2215 IF (l_catalog_category_names_table.EXISTS(error_rec.prog_int_num1)) THEN
2216 l_current_category_name := l_catalog_category_names_table(error_rec.prog_int_num1);--Bugfix:5343821
2217 ELSE
2218 SELECT concatenated_segments
2219 INTO l_current_category_name
2220 FROM MTL_ITEM_CATALOG_GROUPS_KFV
2221 WHERE ITEM_CATALOG_GROUP_ID = error_rec.prog_int_num1;
2222 l_catalog_category_names_table(error_rec.prog_int_num1) := l_current_category_name;
2223 END IF;
2224 l_token_table(3).TOKEN_NAME := 'CATALOG_CATEGORY_NAME';
2225 l_token_table(3).TOKEN_VALUE := l_current_category_name;
2226 SELECT PEP.NAME
2227 INTO l_current_life_cycle
2228 FROM PA_EGO_LIFECYCLES_V PEP
2229 WHERE PEP.PROJ_ELEMENT_ID = error_rec.prog_int_num2;
2230 l_token_table(4).TOKEN_NAME := 'LIFE_CYCLE';
2231 l_token_table(4).TOKEN_VALUE := l_current_life_cycle;
2232
2233 IF error_rec.revision_id IS NULL THEN
2234 -- error is in context of item.
2235 --IF (error_rec.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED) THEN
2236 --l_error_message_name := 'EGO_EF_BL_ITM_CO_REQD_ERR';
2237 -- ELSE
2238 l_error_message_name := 'EGO_EF_BL_ITM_NOT_ALLOW_ERR';
2239 --END IF;
2240 -----------------------------------------------------------------
2241 -- Since it's not as convenient, we don't have a local storing --
2242 -- scheme for Phase name; but we can create one if necessary --
2243 -----------------------------------------------------------------
2244 SELECT PEP.NAME
2245 INTO l_current_phase_name
2246 FROM PA_EGO_PHASES_V PEP
2247 WHERE PEP.PROJ_ELEMENT_ID = error_rec.prog_int_num3;
2248 l_token_table(5).TOKEN_NAME := 'PHASE';
2249 l_token_table(5).TOKEN_VALUE := l_current_phase_name;
2250 ELSE
2251 -- error is in context of item revision.
2252 --IF (error_rec.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED) THEN
2253 --l_error_message_name := 'EGO_EF_BL_REV_CO_REQD_ERR';
2254 --ELSE
2255 l_error_message_name := 'EGO_EF_BL_REV_NOT_ALLOW_ERR';
2256 --END IF;
2257 -----------------------------------------------------------------
2258 -- Since it's not as convenient, we don't have a local storing --
2259 -- scheme for Phase name; but we can create one if necessary --
2260 -----------------------------------------------------------------
2261 SELECT PEP.NAME
2262 INTO l_current_phase_name
2263 FROM PA_EGO_PHASES_V PEP
2264 WHERE PEP.PROJ_ELEMENT_ID = error_rec.prog_int_num3;
2265 l_token_table(5).TOKEN_NAME := 'PHASE';
2266 l_token_table(5).TOKEN_VALUE := l_current_phase_name;
2267 l_token_table(6).TOKEN_NAME := 'ITEM_REV';
2268 l_token_table(6).TOKEN_VALUE := error_rec.REVISION;
2269 END IF;
2270 END IF;
2271
2272 l_item_return_status := FND_API.G_RET_STS_ERROR;
2273 ERROR_HANDLER.Add_Error_Message(
2274 p_message_name => l_error_message_name
2275 ,p_application_id => 'EGO'
2276 ,p_token_tbl => l_token_table
2277 ,p_message_type => FND_API.G_RET_STS_ERROR
2278 ,p_row_identifier => error_rec.TRANSACTION_ID
2279 ,p_entity_id => G_ENTITY_ID
2280 ,p_entity_code => G_ENTITY_CODE
2281 ,p_table_name => G_TABLE_NAME
2282 );
2283 l_token_table.DELETE();
2284
2285 END LOOP;
2286
2287 IF (p_debug_level > 0) THEN
2288 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Reported errors with '||l_debug_rowcount||' interface table rows', 0);
2289 END IF;
2290
2291 --=====================================--
2292 -- LOOP PROCESSING OF STILL-VALID ROWS --
2293 --=====================================--
2294 IF (p_debug_level > 0) THEN
2295 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting loop processing of valid rows', 0);
2296 END IF;
2297
2298 ---------------------------------------------------------------
2299 -- ...and then use it to mark as errors all rows in the list --
2300 -- (note that we have to use dynamic SQL because 1). static --
2301 -- SQL treats the failed Row ID list as a string instead of --
2302 -- a list of numbers, and 2). bulk-binding would cause us to --
2303 -- execute one SQL statement per failed Row ID. Dynamic SQL --
2304 -- only executes one SQL statement for a given call to our --
2305 -- concurrent program--so the fact that our failed Row IDs --
2306 -- aren't passed as a bind variable doesn't matter, because --
2307 -- the statement won't get parsed more than once anyway). --
2308 ---------------------------------------------------------------
2309 l_dynamic_sql := ' UPDATE EGO_ITM_USR_ATTR_INTRFC'||
2310 ' SET PROCESS_STATUS = '||G_PS_GENERIC_ERROR||
2311 ' WHERE DATA_SET_ID = :1'||
2312 ' AND (PROCESS_STATUS IN ('||G_PS_BAD_ORG_ID||', '||
2313 G_PS_BAD_ORG_CODE||', '||
2314 G_PS_BAD_ITEM_ID||', '||
2315 G_PS_BAD_ITEM_NUMBER||', '||
2316 G_PS_BAD_REVISION_ID||', '||
2317 G_PS_BAD_REVISION_CODE||', '||
2318 G_PS_BAD_CATALOG_GROUP_ID||', '||
2319 G_PS_BAD_ATTR_GROUP_ID||', '||
2320 G_PS_BAD_ATTR_GROUP_NAME||', '||
2321 G_PS_CHG_POLICY_NOT_ALLOWED||', '||
2322 G_PS_BAD_DATA_LEVEL||', '||
2323 G_PS_BAD_SUPPLIER||', '||
2324 G_PS_BAD_SUPPLIER_SITE||', '||
2325 G_PS_BAD_SUPPLIER_SITE_ORG||', '||
2326 G_PS_BAD_STYLE_VAR_VALUE_SET||', '||
2327 G_PS_VAR_VSET_CHG_NOT_ALLOWED||', '||
2328 G_PS_SKU_VAR_VALUE_NOT_UPD||', '||
2329 G_PS_INH_ATTR_FOR_SKU_NOT_UPD||', '||
2330 G_PS_DATA_LEVEL_INCORRECT||' ) )';
2331
2332 EXECUTE IMMEDIATE l_dynamic_sql USING p_data_set_id;
2333
2334
2335
2336
2337 ---------------------------------------------------------
2338 -- !!! I M P O R T A N T I N F O R M A T I O N !!! --
2339 ---------------------------------------------------------
2340 -- TEMPLATE APPLICATION AND DEFAULTING IS MOVED TO --
2341 -- EGO_IMPORT_UTIL_PVT.Preprocess_Import --
2342 -- AS PER R12C REQUIREMENTS - DSAKALLE --
2343 ---------------------------------------------------------
2344
2345
2346
2347
2348
2349 -- Call the EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data API instead of
2350 -- EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
2351
2352 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting User Attributes Bulk Load', 1);
2353
2354 IF p_validate_only = FND_API.G_TRUE AND p_ignore_security_for_validate = FND_API.G_FALSE THEN
2355 l_privilege_predicate_api_name := 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Item_Security_Predicate';
2356 ELSE
2357 l_privilege_predicate_api_name := NULL;
2358 END IF;
2359
2360 -- creating default privileges
2361 l_default_dl_view_priv_list := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2362 l_default_dl_edit_priv_list := EGO_COL_NAME_VALUE_PAIR_ARRAY();
2363 FOR i IN c_data_levels_for_sec LOOP
2364 IF i.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND i.DATA_LEVEL_NAME IN ('ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG') THEN
2365 l_default_dl_view_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(i.DATA_LEVEL_ID, 'EGO_VIEW_ITEM_SUP_ASSIGN');
2366 l_default_dl_edit_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(i.DATA_LEVEL_ID, 'EGO_EDIT_ITEM_SUP_ASSIGN');
2367 ELSE
2368 l_default_dl_view_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(i.DATA_LEVEL_ID, 'EGO_VIEW_ITEM');
2369 l_default_dl_edit_priv_obj := EGO_COL_NAME_VALUE_PAIR_OBJ(i.DATA_LEVEL_ID, 'EGO_EDIT_ITEM');
2370 END IF;
2371 l_default_dl_view_priv_list.EXTEND;
2372 l_default_dl_view_priv_list(l_default_dl_view_priv_list.COUNT) := l_default_dl_view_priv_obj;
2373
2374 l_default_dl_edit_priv_list.EXTEND;
2375 l_default_dl_edit_priv_list(l_default_dl_edit_priv_list.COUNT) := l_default_dl_edit_priv_obj;
2376 END LOOP;
2377
2378 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
2379 p_api_version => G_API_VERSION --IN NUMBER
2380 ,p_application_id => 431 --IN NUMBER
2381 ,p_attr_group_type => 'EGO_ITEMMGMT_GROUP' --IN VARCHAR2
2382 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
2383 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
2384 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
2385 ,p_data_set_id => p_data_set_id --IN NUMBER
2386 ,p_entity_id => G_ENTITY_ID --IN NUMBER
2387 ,p_entity_index => l_entity_index_counter --IN NUMBER
2388 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
2389 ,p_debug_level => p_debug_level --IN NUMBER
2390 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
2391 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
2392 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
2393 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
2394 ,p_commit => FND_API.G_TRUE --IN VARCHAR2
2395 ,p_default_dl_view_priv_list => l_default_dl_view_priv_list
2396 ,p_default_dl_edit_priv_list => l_default_dl_edit_priv_list
2397 ,p_privilege_predicate_api_name => l_privilege_predicate_api_name --IN VARCHAR2
2398 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
2399 ,p_validate => TRUE
2400 ,p_do_dml => FALSE
2401 ,p_do_req_def_valiadtion => FALSE --we will do this validation after the template is applied
2402 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
2403 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
2404 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
2405 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
2406 );
2407
2408 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2409 ERRBUF := l_msg_data;
2410 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2411 RAISE G_UNHANDLED_EXCEPTION;
2412 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
2413 RETCODE := L_CONC_RET_STS_WARNING;
2414 END IF;
2415
2416 ---------------------------------------------------------------------------------
2417 -- Mark as errors all rows that share the same logical Attribute Group --
2418 -- If attribute value does not belong to a valid value set i.e. in case of --
2419 -- variant attributes, user can associate a child value set as well. So this --
2420 -- validation will check that value belongs to child value set also,else error --
2421 -- --
2422 -- This validation needs to be done after the normal UDA validations are over --
2423 ---------------------------------------------------------------------------------
2424
2425 UPDATE EGO_ITM_USR_ATTR_INTRFC
2426 SET PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE
2427 WHERE DATA_SET_ID = p_data_set_id
2428 AND PROCESS_STATUS = G_PS_IN_PROCESS
2429 AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2430 FROM
2431 EGO_FND_DSC_FLX_CTX_EXT AG_EXT,
2432 EGO_ITM_USR_ATTR_INTRFC INTF,
2433 EGO_STYLE_VARIANT_ATTR_VS SVA,
2434 EGO_FND_DF_COL_USGS_EXT ATTR,
2435 FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
2436 MTL_SYSTEM_ITEMS_INTERFACE MSII
2437 WHERE INTF.DATA_SET_ID = p_data_set_id
2438 AND INTF.PROCESS_STATUS = G_PS_IN_PROCESS
2439 AND INTF.ATTR_GROUP_TYPE = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
2440 AND INTF.ATTR_GROUP_INT_NAME = ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE
2441 AND ATTR.APPLICATION_ID = 431
2442 AND ATTR.APPLICATION_ID = FL_COL.APPLICATION_ID
2443 AND ATTR.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
2444 AND ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
2445 AND ATTR.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
2446 AND AG_EXT.APPLICATION_ID = ATTR.APPLICATION_ID
2447 AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
2448 AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE
2449 AND Nvl(AG_EXT.VARIANT, 'N') = 'Y'
2450 AND INTF.ATTR_INT_NAME = FL_COL.END_USER_COLUMN_NAME
2451 AND SVA.ATTRIBUTE_ID = ATTR.ATTR_ID
2452 AND SVA.INVENTORY_ITEM_ID = MSII.STYLE_ITEM_ID
2453 AND SVA.VALUE_SET_ID <> FL_COL.FLEX_VALUE_SET_ID
2454 AND INTF.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
2455 AND INTF.ORGANIZATION_ID = MSII.ORGANIZATION_ID
2456 AND (CASE WHEN ATTR.DATA_TYPE IN ('A', 'C') THEN INTF.ATTR_VALUE_STR
2457 WHEN ATTR.DATA_TYPE = 'N' THEN TO_CHAR(INTF.ATTR_VALUE_NUM)
2458 WHEN ATTR.DATA_TYPE IN ('X', 'Y') THEN TO_CHAR(INTF.ATTR_VALUE_DATE)
2459 ELSE NULL
2460 END) NOT IN ( SELECT val.FLEX_VALUE
2461 FROM
2462 FND_FLEX_VALUES_VL val,
2463 EGO_VS_VALUES_DISP_ORDER disp_order,
2464 EGO_VALUE_SET_EXT ext
2465 WHERE val.FLEX_VALUE_ID = disp_order.VALUE_SET_VALUE_ID
2466 AND disp_order.VALUE_SET_ID = SVA.VALUE_SET_ID
2467 AND val.FLEX_VALUE_SET_ID = ext.PARENT_VALUE_SET_ID
2468 AND ext.VALUE_SET_ID = disp_order.VALUE_SET_ID
2469 AND Nvl(val.ENABLED_FLAG,'Y') = 'Y'
2470 AND Nvl(val.START_DATE_ACTIVE, SYSDATE) <= SYSDATE
2471 AND Nvl(val.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
2472 )
2473 );
2474
2475
2476
2477
2478 ---------------------------------------------------------------------------------
2479 -- Adding validation errors for Style/SKU related validations --
2480 ---------------------------------------------------------------------------------
2481 FOR i IN (SELECT PROCESS_STATUS, TRANSACTION_ID, ATTR_VALUE_STR, ATTR_VALUE_NUM, ATTR_VALUE_DATE, ATTR_DISP_VALUE, ITEM_NUMBER
2482 FROM EGO_ITM_USR_ATTR_INTRFC
2483 WHERE DATA_SET_ID = p_data_set_id
2484 AND PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE
2485 )
2486 LOOP
2487 RETCODE := L_CONC_RET_STS_WARNING;
2488 IF (i.PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE ) THEN
2489 l_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2490 l_token_table(1).TOKEN_VALUE := i.ITEM_NUMBER;
2491
2492 l_token_table(2).TOKEN_NAME := 'VALUE';
2493 l_token_table(2).TOKEN_VALUE := COALESCE(i.ATTR_VALUE_STR, TO_CHAR(i.ATTR_VALUE_NUM), TO_CHAR(i.ATTR_VALUE_DATE));
2494
2495 l_error_message_name := 'EGO_BAD_VAR_VALUE';
2496 END IF;
2497
2498 ERROR_HANDLER.Add_Error_Message(
2499 p_message_name => l_error_message_name
2500 ,p_application_id => 'EGO'
2501 ,p_token_tbl => l_token_table
2502 ,p_message_type => FND_API.G_RET_STS_ERROR
2503 ,p_row_identifier => i.TRANSACTION_ID
2504 ,p_entity_id => G_ENTITY_ID
2505 ,p_entity_code => G_ENTITY_CODE
2506 ,p_table_name => G_TABLE_NAME
2507 );
2508 l_token_table.DELETE();
2509 END LOOP;
2510
2511
2512 ---------------------------------------------------
2513 -- Marking all these rows to process_status = 3 --
2514 ---------------------------------------------------
2515 UPDATE EGO_ITM_USR_ATTR_INTRFC
2516 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
2517 WHERE DATA_SET_ID = p_data_set_id
2518 AND PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE;
2519
2520
2521
2522 ----------------------------------------------------
2523 -- Processing data for GDSN Attribute group types --
2524 ----------------------------------------------------
2525
2526 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
2527 p_api_version => G_API_VERSION --IN NUMBER
2528 ,p_application_id => 431 --IN NUMBER
2529 ,p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS' --IN VARCHAR2
2530 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
2531 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
2532 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
2533 ,p_data_set_id => p_data_set_id --IN NUMBER
2534 ,p_entity_id => G_ENTITY_ID --IN NUMBER
2535 ,p_entity_index => l_entity_index_counter --IN NUMBER
2536 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
2537 ,p_debug_level => p_debug_level --IN NUMBER
2538 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
2539 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
2540 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
2541 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
2542 ,p_commit => FND_API.G_TRUE --IN VARCHAR2
2543 ,p_default_view_privilege => 'EGO_VIEW_ITEM' --IN VARCHAR2
2544 ,p_default_edit_privilege => 'EGO_EDIT_ITEM' --IN VARCHAR2
2545 ,p_privilege_predicate_api_name => l_privilege_predicate_api_name --IN VARCHAR2
2546 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
2547 ,p_validate => TRUE
2548 ,p_do_dml => FALSE
2549 ,p_do_req_def_valiadtion => FALSE --we will do this validation after the template is applied
2550 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
2551 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
2552 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
2553 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
2554 );
2555
2556 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2557 ERRBUF := l_msg_data;
2558 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2559 RAISE G_UNHANDLED_EXCEPTION;
2560 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
2561 RETCODE := L_CONC_RET_STS_WARNING;
2562 END IF;
2563
2564 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
2565 p_api_version => G_API_VERSION --IN NUMBER
2566 ,p_application_id => 431 --IN NUMBER
2567 ,p_attr_group_type => 'EGO_ITEM_GTIN_MULTI_ATTRS' --IN VARCHAR2
2568 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
2569 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
2570 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
2571 ,p_data_set_id => p_data_set_id --IN NUMBER
2572 ,p_entity_id => G_ENTITY_ID --IN NUMBER
2573 ,p_entity_index => l_entity_index_counter --IN NUMBER
2574 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
2575 ,p_debug_level => p_debug_level --IN NUMBER
2576 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
2577 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
2578 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
2579 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
2580 ,p_commit => FND_API.G_TRUE --IN VARCHAR2
2581 ,p_default_view_privilege => 'EGO_VIEW_ITEM' --IN VARCHAR2
2582 ,p_default_edit_privilege => 'EGO_EDIT_ITEM' --IN VARCHAR2
2583 ,p_privilege_predicate_api_name => l_privilege_predicate_api_name --IN VARCHAR2
2584 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
2585 ,p_validate => TRUE
2586 ,p_do_dml => FALSE
2587 ,p_do_req_def_valiadtion => FALSE --we will do this validation after the template is applied
2588 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
2589 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
2590 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
2591 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
2592 );
2593
2594 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2595 ERRBUF := l_msg_data;
2596 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2597 RAISE G_UNHANDLED_EXCEPTION;
2598 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
2599 RETCODE := L_CONC_RET_STS_WARNING;
2600 END IF;
2601
2602
2603 IF p_validate_only = FND_API.G_FALSE THEN
2604 ---------------------------------------------------------------------
2605 -- Calling the API to Mark all such rows which have the same data --
2606 -- as we have for the attribute in the production tables. --
2607 ---------------------------------------------------------------------
2608
2609 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Calling EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows for EGO_ITEMMGMT_GROUP ', 1);
2610 EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows (
2611 p_api_version =>1.0
2612 ,p_application_id =>431
2613 ,p_attr_group_type =>'EGO_ITEMMGMT_GROUP'
2614 ,p_object_name =>'EGO_ITEM'
2615 ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
2616 ,p_data_set_id =>p_data_set_id
2617 ,p_new_status =>4
2618 ,x_return_status =>l_return_status
2619 ,x_msg_data =>l_msg_data
2620 );
2621 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);
2622
2623 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2624 ERRBUF := l_msg_data;
2625 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2626 ERROR_HANDLER.Add_Error_Message(
2627 p_message_text => l_msg_data
2628 ,p_application_id => 'EGO'
2629 ,p_message_type => FND_API.G_RET_STS_ERROR
2630 ,p_row_identifier => l_err_reporting_transaction_id
2631 ,p_entity_id => G_ENTITY_ID
2632 ,p_entity_code => G_ENTITY_CODE
2633 ,p_table_name => G_TABLE_NAME
2634 );
2635 RAISE G_UNHANDLED_EXCEPTION;
2636 ELSIF l_return_status = G_FND_RET_STS_WARNING THEN
2637 RETCODE := L_CONC_RET_STS_WARNING;
2638 END IF;
2639
2640
2641
2642
2643 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Calling EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows for EGO_ITEM_GTIN_ATTRS ', 1);
2644 EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows (
2645 p_api_version =>1.0
2646 ,p_application_id =>431
2647 ,p_attr_group_type =>'EGO_ITEM_GTIN_ATTRS'
2648 ,p_object_name =>'EGO_ITEM'
2649 ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
2650 ,p_data_set_id =>p_data_set_id
2651 ,p_new_status =>4
2652 ,x_return_status =>l_return_status
2653 ,x_msg_data =>l_msg_data
2654 );
2655 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);
2656
2657 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2658 ERRBUF := l_msg_data;
2659 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2660 ERROR_HANDLER.Add_Error_Message(
2661 p_message_text => l_msg_data
2662 ,p_application_id => 'EGO'
2663 ,p_message_type => FND_API.G_RET_STS_ERROR
2664 ,p_row_identifier => l_err_reporting_transaction_id
2665 ,p_entity_id => G_ENTITY_ID
2666 ,p_entity_code => G_ENTITY_CODE
2667 ,p_table_name => G_TABLE_NAME
2668 );
2669 RAISE G_UNHANDLED_EXCEPTION;
2670 ELSIF l_return_status = G_FND_RET_STS_WARNING THEN
2671 RETCODE := L_CONC_RET_STS_WARNING;
2672 END IF;
2673
2674 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Calling EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows for EGO_ITEM_GTIN_MULTI_ATTRS ', 1);
2675 EGO_USER_ATTRS_BULK_PVT.Mark_Unchanged_Attr_Rows (
2676 p_api_version =>1.0
2677 ,p_application_id =>431
2678 ,p_attr_group_type =>'EGO_ITEM_GTIN_MULTI_ATTRS'
2679 ,p_object_name =>'EGO_ITEM'
2680 ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
2681 ,p_data_set_id =>p_data_set_id
2682 ,p_new_status =>4
2683 ,x_return_status =>l_return_status
2684 ,x_msg_data =>l_msg_data
2685 );
2686 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);
2687
2688 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2689 ERRBUF := l_msg_data;
2690 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2691 ERROR_HANDLER.Add_Error_Message(
2692 p_message_text => l_msg_data
2693 ,p_application_id => 'EGO'
2694 ,p_message_type => FND_API.G_RET_STS_ERROR
2695 ,p_row_identifier => l_err_reporting_transaction_id
2696 ,p_entity_id => G_ENTITY_ID
2697 ,p_entity_code => G_ENTITY_CODE
2698 ,p_table_name => G_TABLE_NAME
2699 );
2700 RAISE G_UNHANDLED_EXCEPTION;
2701 ELSIF l_return_status = G_FND_RET_STS_WARNING THEN
2702 RETCODE := L_CONC_RET_STS_WARNING;
2703 END IF;
2704 END IF; --IF p_validate_only = FND_API.G_FALSE THEN
2705
2706 ---------------------------------------------------------------------------------------
2707 -- Mark all rows to satus 5 that share the same logical Attribute Group row --
2708 -- with any rows for which the Change Policy is defined as CHANGE_ORDER_REQUIRED --
2709 -- (we do not process such rows; they must be bulkloaded through Change Management); --
2710 -- the exception to this is rows for pending Items, which we still processed --
2711 -- The user attrs API would not do the DML for these --
2712 ---------------------------------------------------------------------------------------
2713 IF (p_debug_level > 0) THEN
2714 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Change Policy conversion/erroring', 0);
2715 END IF;
2716
2717 BEGIN
2718 --------------------------------------------------------------------------------------
2719 -- Bug#4679902 (If the user wants to have Change Order, Change ALLOWED attributes
2720 -- will also be forced to undergo Change Order)
2721 --------------------------------------------------------------------------------------
2722 l_add_all_to_cm := EGO_IMPORT_PVT.getAddAllToChangeFlag(p_batch_id => p_data_set_id);
2723 EGO_USER_ATTRS_DATA_PVT.Debug_Msg(' Add all to Change flag is '||l_add_all_to_cm, 0);
2724 IF( l_add_all_to_cm = 'Y' ) THEN
2725 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2726 SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
2727 WHERE UAI.DATA_SET_ID = p_data_set_id
2728 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
2729 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
2730 ELSE --the attrs should go through the CO int his case.
2731 l_policy_check_name := 'CHANGE_ORDER_REQUIRED';
2732 EXECUTE IMMEDIATE l_policy_check_sql USING G_PS_CHG_POLICY_CO_REQUIRED,
2733 p_data_set_id,
2734 G_PS_IN_PROCESS,
2735 p_data_set_id,
2736 G_PS_IN_PROCESS,
2737 l_policy_check_name;
2738 END IF;
2739 -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After CO REQD policy');
2740
2741 EXCEPTION
2742 WHEN OTHERS THEN
2743 NULL;
2744 END;
2745 /* Dont need this right now.
2746 --------------------------------------------------------------
2747 -- Loop through all the distinct catalog group id's in the --
2748 -- current data set and mark the rows for catalog's which --
2749 -- have a NIR required and leave them in status 5 for CM --
2750 --------------------------------------------------------------
2751 FOR catalog_groups IN distinct_catalaog_groups(p_data_set_id)
2752 LOOP
2753 IF (INVIDIT3.CHECK_NPR_CATALOG(catalog_groups.ITEM_CATALOG_GROUP_ID)) THEN
2754 UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2755 SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
2756 WHERE UAI.DATA_SET_ID = p_data_set_id
2757 AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
2758 AND UAI.ITEM_CATALOG_GROUP_ID = catalog_groups.ITEM_CATALOG_GROUP_ID;
2759 END IF;
2760 END LOOP;
2761 */
2762
2763 ------------------------------------------------------------
2764
2765
2766 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Before calling the GTIN validation API', 0);
2767
2768 EGO_GTIN_ATTRS_PVT.VALIDATE_INTF_ROWS(
2769 p_data_set_id => p_data_set_id
2770 ,p_entity_id => G_ENTITY_ID
2771 ,p_entity_code => G_ENTITY_CODE
2772 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
2773 ,x_return_status => l_gtinval_ret_code
2774 );
2775 IF (l_gtinval_ret_code = 'U') THEN
2776 RETCODE := L_CONC_RET_STS_ERROR;
2777 ELSIF (l_gtinval_ret_code = 'E') THEN
2778 RETCODE := L_CONC_RET_STS_WARNING;
2779 END IF;
2780
2781 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('After calling the GTIN validation API', 0);
2782
2783 ------------------------------------------------
2784 -- Here we call the API in DML only mode for --
2785 -- all the attr group types --
2786 ------------------------------------------------
2787 IF p_validate_only = FND_API.G_FALSE THEN
2788 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
2789 p_api_version => G_API_VERSION --IN NUMBER
2790 ,p_application_id => 431 --IN NUMBER
2791 ,p_attr_group_type => 'EGO_ITEMMGMT_GROUP' --IN VARCHAR2
2792 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
2793 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
2794 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
2795 ,p_data_set_id => p_data_set_id --IN NUMBER
2796 ,p_entity_id => G_ENTITY_ID --IN NUMBER
2797 ,p_entity_index => l_entity_index_counter --IN NUMBER
2798 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
2799 ,p_debug_level => p_debug_level --IN NUMBER
2800 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
2801 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
2802 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
2803 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
2804 ,p_commit => FND_API.G_TRUE --IN VARCHAR2
2805 ,p_default_dl_view_priv_list => l_default_dl_view_priv_list
2806 ,p_default_dl_edit_priv_list => l_default_dl_edit_priv_list
2807 ,p_privilege_predicate_api_name => 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Item_Security_Predicate' --IN VARCHAR2
2808 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
2809 ,p_validate => FALSE
2810 ,p_do_dml => TRUE
2811 ,p_do_req_def_valiadtion => TRUE --Doing this validation here since the value may have come from template
2812 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
2813 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
2814 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
2815 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
2816 );
2817
2818 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2819 ERRBUF := l_msg_data;
2820 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2821 RAISE G_UNHANDLED_EXCEPTION;
2822 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
2823 RETCODE := L_CONC_RET_STS_WARNING;
2824 END IF;
2825
2826 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
2827 p_api_version => G_API_VERSION --IN NUMBER
2828 ,p_application_id => 431 --IN NUMBER
2829 ,p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS' --IN VARCHAR2
2830 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
2831 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
2832 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
2833 ,p_data_set_id => p_data_set_id --IN NUMBER
2834 ,p_entity_id => G_ENTITY_ID --IN NUMBER
2835 ,p_entity_index => l_entity_index_counter --IN NUMBER
2836 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
2837 ,p_debug_level => p_debug_level --IN NUMBER
2838 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
2839 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
2840 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
2841 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
2842 ,p_commit => FND_API.G_TRUE --IN VARCHAR2
2843 ,p_default_view_privilege => 'EGO_VIEW_ITEM' --IN VARCHAR2
2844 ,p_default_edit_privilege => 'EGO_EDIT_ITEM' --IN VARCHAR2
2845 ,p_privilege_predicate_api_name => 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Item_Security_Predicate' --IN VARCHAR2
2846 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
2847 ,p_validate => FALSE
2848 ,p_do_dml => TRUE
2849 ,p_do_req_def_valiadtion => TRUE --Doing this validation here since the value may have come from template
2850 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
2851 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
2852 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
2853 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
2854 );
2855
2856 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2857 ERRBUF := l_msg_data;
2858 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2859 RAISE G_UNHANDLED_EXCEPTION;
2860 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
2861 RETCODE := L_CONC_RET_STS_WARNING;
2862 END IF;
2863
2864 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
2865 p_api_version => G_API_VERSION --IN NUMBER
2866 ,p_application_id => 431 --IN NUMBER
2867 ,p_attr_group_type => 'EGO_ITEM_GTIN_MULTI_ATTRS' --IN VARCHAR2
2868 ,p_object_name => G_ITEM_NAME --IN VARCHAR2
2869 ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
2870 ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
2871 ,p_data_set_id => p_data_set_id --IN NUMBER
2872 ,p_entity_id => G_ENTITY_ID --IN NUMBER
2873 ,p_entity_index => l_entity_index_counter --IN NUMBER
2874 ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
2875 ,p_debug_level => p_debug_level --IN NUMBER
2876 ,p_init_error_handler => FND_API.G_FALSE --IN VARCHAR2
2877 ,p_init_fnd_msg_list => FND_API.G_FALSE --IN VARCHAR2
2878 ,p_log_errors => FND_API.G_TRUE --IN VARCHAR2
2879 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE --IN VARCHAR2
2880 ,p_commit => FND_API.G_TRUE --IN VARCHAR2
2881 ,p_default_view_privilege => 'EGO_VIEW_ITEM' --IN VARCHAR2
2882 ,p_default_edit_privilege => 'EGO_EDIT_ITEM' --IN VARCHAR2
2883 ,p_privilege_predicate_api_name => 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Item_Security_Predicate' --IN VARCHAR2
2884 ,p_related_class_codes_query => l_related_class_codes_query --IN VARCHAR2
2885 ,p_validate => FALSE
2886 ,p_do_dml => TRUE
2887 ,p_do_req_def_valiadtion => TRUE --Doing this validation here since the value may have come from template
2888 ,x_return_status => l_user_attrs_return_status --OUT NOCOPY VARCHAR2
2889 ,x_errorcode => l_errorcode --OUT NOCOPY NUMBER
2890 ,x_msg_count => l_msg_count --OUT NOCOPY NUMBER
2891 ,x_msg_data => l_msg_data --OUT NOCOPY VARCHAR2
2892 );
2893
2894 IF (l_user_attrs_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2895 ERRBUF := l_msg_data;
2896 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
2897 RAISE G_UNHANDLED_EXCEPTION;
2898 ELSIF l_user_attrs_return_status = G_FND_RET_STS_WARNING THEN
2899 RETCODE := L_CONC_RET_STS_WARNING;
2900 END IF;
2901
2902 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Completing User Attributes Bulk Load return status is ' || l_user_attrs_return_status, 1);
2903
2904 --------------------------------------------------------
2905 -- This takes care of rolling up of GDSN attributes --
2906 -- and registration/publication status. --
2907 --------------------------------------------------------
2908
2909 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting EGO_GTIN_ATTRS_PVT.Do_Post_UCCnet_Attrs_Action', 1);
2910
2911 EGO_GTIN_ATTRS_PVT.Do_Post_UCCnet_Attrs_Action ( p_data_set_id => p_data_set_id
2912 ,p_entity_id => G_ENTITY_ID
2913 ,p_entity_code => G_ENTITY_CODE
2914 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
2915 );
2916
2917
2918 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Done EGO_GTIN_ATTRS_PVT.Do_Post_UCCnet_Attrs_Action', 1);
2919
2920
2921 IF (FND_API.To_Boolean(p_purge_successful_lines)) THEN
2922 -----------------------------------------------
2923 -- Delete all successful rows from the table --
2924 -- (they're the only rows still in process) --
2925 -----------------------------------------------
2926 DELETE FROM EGO_ITM_USR_ATTR_INTRFC
2927 WHERE DATA_SET_ID = p_data_set_id
2928 AND PROCESS_STATUS = G_PS_IN_PROCESS;
2929 ELSE
2930 ----------------------------------------------
2931 -- Mark all rows we've processed as success --
2932 -- if they weren't marked as failure above --
2933 ----------------------------------------------
2934 UPDATE EGO_ITM_USR_ATTR_INTRFC
2935 SET PROCESS_STATUS = G_PS_SUCCESS
2936 WHERE DATA_SET_ID = p_data_set_id
2937 AND PROCESS_STATUS = G_PS_IN_PROCESS;
2938 END IF;
2939
2940 IF (p_debug_level > 0) THEN
2941 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Done with Item/Item Revision Concurrent Program', 0);
2942 END IF;
2943 END IF; -- IF p_validate_only = FND_API.G...
2944
2945 IF (FND_API.To_Boolean(p_initialize_error_handler)) THEN
2946
2947 -------------------------------------------------------------------
2948 -- Finally, we log any errors that we've accumulated throughout --
2949 -- our conversions and looping (including all errors encountered --
2950 -- within our Business Object's processing) --
2951 -------------------------------------------------------------------
2952 ERROR_HANDLER.Log_Error(
2953 p_write_err_to_inttable => 'Y'
2954 ,p_write_err_to_conclog => 'Y'
2955 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
2956 );
2957
2958 IF (ERROR_HANDLER.Get_Debug() = 'Y') THEN
2959 ERROR_HANDLER.Close_Debug_Session();
2960 END IF;
2961 END IF;
2962
2963 COMMIT WORK;
2964 EXCEPTION
2965 WHEN G_NO_USER_NAME_TO_VALIDATE THEN
2966
2967 ----------------------------------------
2968 -- Mark all rows in process as errors --
2969 ----------------------------------------
2970 UPDATE EGO_ITM_USR_ATTR_INTRFC
2971 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
2972 WHERE DATA_SET_ID = p_data_set_id
2973 AND PROCESS_STATUS = G_PS_IN_PROCESS;
2974
2975 ---------------------------------------------------------------------
2976 -- Use any random transaction ID in the data set to log this error --
2977 -- If no rows are found, please use -1 as transaction_id --
2978 ---------------------------------------------------------------------
2979 IF SQL%ROWCOUNT > 0 THEN
2980 SELECT TRANSACTION_ID
2981 INTO l_err_reporting_transaction_id
2982 FROM EGO_ITM_USR_ATTR_INTRFC
2983 WHERE DATA_SET_ID = p_data_set_id
2984 AND ROWNUM = 1;
2985 ELSE
2986 l_err_reporting_transaction_id := -1;
2987 END IF;
2988
2989 ERROR_HANDLER.Add_Error_Message(
2990 p_message_name => 'EGO_EF_NO_NAME_TO_VALIDATE'
2991 ,p_application_id => 'EGO'
2992 ,p_message_type => FND_API.G_RET_STS_ERROR
2993 ,p_row_identifier => l_err_reporting_transaction_id
2994 ,p_entity_id => G_ENTITY_ID
2995 ,p_entity_code => G_ENTITY_CODE
2996 ,p_table_name => G_TABLE_NAME
2997 );
2998
2999 IF (FND_API.To_Boolean(p_initialize_error_handler)) THEN
3000
3001 ---------------------------------------------------------------
3002 -- No matter what the error, we want to make sure everything --
3003 -- we've logged gets to the appropriate error locations --
3004 ---------------------------------------------------------------
3005 ERROR_HANDLER.Log_Error(
3006 p_write_err_to_inttable => 'Y'
3007 ,p_write_err_to_conclog => 'Y'
3008 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
3009 );
3010
3011 IF (ERROR_HANDLER.Get_Debug() = 'Y') THEN
3012 ERROR_HANDLER.Close_Debug_Session();
3013 END IF;
3014 END IF;
3015
3016 RETCODE := L_CONC_RET_STS_WARNING;
3017
3018 WHEN OTHERS THEN
3019
3020 ----------------------------------------
3021 -- Mark all rows in process as errors --
3022 ----------------------------------------
3023 UPDATE EGO_ITM_USR_ATTR_INTRFC
3024 SET PROCESS_STATUS = G_PS_GENERIC_ERROR
3025 WHERE DATA_SET_ID = p_data_set_id
3026 AND PROCESS_STATUS = G_PS_IN_PROCESS;
3027
3028 ---------------------------------------------------------------------
3029 -- Use any random transaction ID in the data set to log this error --
3030 -- If no rows are found, please use -1 as transaction_id --
3031 ---------------------------------------------------------------------
3032 IF SQL%ROWCOUNT > 0 THEN
3033 SELECT TRANSACTION_ID
3034 INTO l_err_reporting_transaction_id
3035 FROM EGO_ITM_USR_ATTR_INTRFC
3036 WHERE DATA_SET_ID = p_data_set_id
3037 AND ROWNUM = 1;
3038 ELSE
3039 l_err_reporting_transaction_id := -1;
3040 END IF;
3041
3042 ERROR_HANDLER.Add_Error_Message(
3043 p_message_text => 'Unexpected error in '||G_PKG_NAME||'.Process_Item_User_Attrs_Data: '||SQLERRM
3044 ,p_application_id => 'EGO'
3045 ,p_message_type => FND_API.G_RET_STS_ERROR
3046 ,p_row_identifier => l_err_reporting_transaction_id
3047 ,p_entity_id => G_ENTITY_ID
3048 ,p_entity_code => G_ENTITY_CODE
3049 ,p_table_name => G_TABLE_NAME
3050 );
3051
3052 IF (FND_API.To_Boolean(p_initialize_error_handler)) THEN
3053
3054 ---------------------------------------------------------------
3055 -- No matter what the error, we want to make sure everything --
3056 -- we've logged gets to the appropriate error locations --
3057 ---------------------------------------------------------------
3058 ERROR_HANDLER.Log_Error(
3059 p_write_err_to_inttable => 'Y'
3060 ,p_write_err_to_conclog => 'Y'
3061 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
3062 );
3063
3064 IF (ERROR_HANDLER.Get_Debug() = 'Y') THEN
3065 ERROR_HANDLER.Close_Debug_Session();
3066 END IF;
3067 END IF;
3068
3069 RETCODE := L_CONC_RET_STS_ERROR;
3070
3071 END Process_Item_User_Attrs_Data;
3072
3073 ----------------------------------------------------------------------
3074
3075 PROCEDURE Get_Related_Class_Codes (
3076 p_classification_code IN VARCHAR2
3077 ,x_related_class_codes_list OUT NOCOPY VARCHAR2
3078 ) IS
3079
3080 BEGIN
3081
3082 x_related_class_codes_list :=
3083 Build_Parent_Cat_Group_List(TO_NUMBER(p_classification_code), NULL);
3084
3085 END Get_Related_Class_Codes;
3086
3087 ----------------------------------------------------------------------
3088
3089 PROCEDURE Impl_Item_Attr_Change_Line (
3090 p_api_version IN NUMBER
3091 ,p_change_id IN NUMBER
3092 ,p_change_line_id IN NUMBER
3093 ,p_old_revision_id IN NUMBER DEFAULT NULL
3094 ,p_new_revision_id IN NUMBER DEFAULT NULL
3095 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
3096 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
3097 ,x_return_status OUT NOCOPY VARCHAR2
3098 ,x_errorcode OUT NOCOPY NUMBER
3099 ,x_msg_count OUT NOCOPY NUMBER
3100 ,x_msg_data OUT NOCOPY VARCHAR2
3101 ) IS
3102
3103 l_old_data_level_nv_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3104 l_new_data_level_nv_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3105 l_api_name VARCHAR2(30);
3106
3107 BEGIN
3108 l_api_name := 'Impl_Item_Attr_Change_Line';
3109 SetGlobals();
3110 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
3111 ,p_module => l_api_name
3112 ,p_message => 'Started with 11 params '||
3113 ' p_api_version: '|| to_char(p_api_version) ||
3114 ' - p_change_id: '|| p_change_id ||
3115 ' - p_change_line_id: '|| p_change_line_id ||
3116 ' - p_old_revision_id: '|| p_old_revision_id ||
3117 ' - p_new_revision_id: '|| p_new_revision_id ||
3118 ' - p_init_msg_list: '|| p_init_msg_list ||
3119 ' - p_commit: '|| p_commit
3120 );
3121 ---------------------------------------------------------------------
3122 -- Build data structures to pass in Data Level info, if applicable --
3123 ---------------------------------------------------------------------
3124 IF (p_old_revision_id IS NOT NULL) THEN
3125 l_old_data_level_nv_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3126 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID',
3127 p_old_revision_id)
3128 );
3129 END IF;
3130 IF (p_new_revision_id IS NOT NULL) THEN
3131 l_new_data_level_nv_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3132 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID',
3133 p_new_revision_id)
3134 );
3135 END IF;
3136
3137 -------------------------------------------------------------------------
3138 -- Now we invoke the UserAttrs procedure, passing Item-specific params --
3139 -------------------------------------------------------------------------
3140 EGO_USER_ATTRS_DATA_PVT.Implement_Change_Line(
3141 p_api_version => 1.0
3142 ,p_object_name => G_ITEM_NAME
3143 ,p_production_b_table_name => 'EGO_MTL_SY_ITEMS_EXT_B'
3144 ,p_production_tl_table_name => 'EGO_MTL_SY_ITEMS_EXT_TL'
3145 ,p_change_b_table_name => 'EGO_ITEMS_ATTRS_CHANGES_B'
3146 ,p_change_tl_table_name => 'EGO_ITEMS_ATTRS_CHANGES_TL'
3147 ,p_tables_application_id => 431
3148 ,p_change_line_id => p_change_line_id
3149 ,p_old_data_level_nv_pairs => l_old_data_level_nv_pairs
3150 ,p_new_data_level_nv_pairs => l_new_data_level_nv_pairs
3151 ,p_related_class_code_function => 'EGO_ITEM_USER_ATTRS_CP_PUB.Get_Related_Class_Codes'
3152 ,p_init_msg_list => p_init_msg_list
3153 ,p_commit => p_commit
3154 ,x_return_status => x_return_status
3155 ,x_errorcode => x_errorcode
3156 ,x_msg_count => x_msg_count
3157 ,x_msg_data => x_msg_data
3158 );
3159 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
3160 ,p_module => l_api_name
3161 ,p_message => 'Returning with params '||
3162 ' x_return_status: '|| x_return_status ||
3163 ' - x_errorcode: '|| x_errorcode ||
3164 ' - x_msg_count: '|| x_msg_count ||
3165 ' - x_msg_data: '|| x_msg_data
3166 );
3167
3168 END Impl_Item_Attr_Change_Line;
3169
3170 ----------------------------------------------------------------------
3171 /*
3172 * Copy_data_to_Intf
3173 * --------------------------
3174 * A procedure for ITEMS use
3175 * which copies data from production/interface table to interface table
3176 * The inherited attribute groups are filtered at the source sql only.
3177 *
3178 */
3179 PROCEDURE Copy_data_to_Intf
3180 (
3181 p_api_version IN NUMBER
3182 ,p_commit IN VARCHAR2
3183 ,p_copy_from_intf_table IN VARCHAR2 -- T/F
3184 ,p_source_entity_sql IN VARCHAR2
3185 ,p_source_attr_groups_sql IN VARCHAR2
3186 ,p_dest_process_status IN VARCHAR2
3187 ,p_dest_data_set_id IN VARCHAR2
3188 ,p_dest_transaction_type IN VARCHAR2
3189 ,p_cleanup_row_identifiers IN VARCHAR2 DEFAULT FND_API.G_TRUE -- T/F
3190 ,x_return_status OUT NOCOPY VARCHAR2
3191 ,x_msg_count OUT NOCOPY NUMBER
3192 ,x_msg_data OUT NOCOPY VARCHAR2
3193 )
3194 IS
3195 TYPE DYNAMIC_CUR IS REF CURSOR;
3196 l_dynamic_cursor DYNAMIC_CUR;
3197 l_prog_int_char1_value VARCHAR2(100);
3198 l_prog_int_num4_value NUMBER;
3199 l_attr_group_sql VARCHAR2(32767);
3200 l_dynamic_sql VARCHAR2(32767);
3201 l_dummy_char VARCHAR2(32767);
3202 l_dest_transaction_type VARCHAR2(50);
3203 l_multi_row VARCHAR2(10);
3204
3205 l_insert_cols VARCHAR2(32767);
3206 l_select_sql VARCHAR2(32767);
3207 l_where_clause VARCHAR2(32767);
3208 l_when_matched VARCHAR2(32767);
3209 l_when_not_matched VARCHAR2(32767);
3210
3211 l_attr_value_str_sql VARCHAR2(32767);
3212 l_attr_value_date_sql VARCHAR2(32767);
3213 l_attr_value_num_sql VARCHAR2(32767);
3214 l_attr_value_uom_sql VARCHAR2(32767);
3215
3216 l_max_row_identifier NUMBER;
3217
3218 l_curr_attr_grp_id NUMBER;
3219 l_data_level_id NUMBER;
3220
3221 CURSOR c_attr_rec(c_attr_group_id IN NUMBER)
3222 IS
3223 SELECT
3224 attr_ext.ATTR_ID,
3225 attr_col.END_USER_COLUMN_NAME AS ATTR_NAME,
3226 attr_ext.DATA_TYPE AS DATA_TYPE_CODE,
3227 attr_ext.APPLICATION_COLUMN_NAME AS DATABASE_COLUMN,
3228 attr_ext.UOM_CLASS AS UOM_CLASS,
3229 ag_ext.MULTI_ROW
3230 FROM
3231 FND_DESCR_FLEX_COLUMN_USAGES attr_col,
3232 EGO_FND_DF_COL_USGS_EXT attr_ext,
3233 EGO_FND_DSC_FLX_CTX_EXT ag_ext
3234 WHERE ag_ext.ATTR_GROUP_ID = c_attr_group_id
3235 AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = attr_ext.DESCRIPTIVE_FLEXFIELD_NAME
3236 AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = attr_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
3237 AND ag_ext.APPLICATION_ID = attr_ext.APPLICATION_ID
3238 AND attr_ext.APPLICATION_ID = attr_col.APPLICATION_ID
3239 AND attr_ext.DESCRIPTIVE_FLEXFIELD_NAME = attr_col.DESCRIPTIVE_FLEXFIELD_NAME
3240 AND attr_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = attr_col.DESCRIPTIVE_FLEX_CONTEXT_CODE
3241 AND attr_ext.APPLICATION_COLUMN_NAME = attr_col.APPLICATION_COLUMN_NAME
3242 AND attr_col.ENABLED_FLAG = 'Y';
3243 BEGIN
3244 SetGlobals();
3245 Debug_Conc_Log('Starting Copy_data_to_Intf');
3246 -- Standard start of API savepoint
3247 IF FND_API.To_Boolean(p_commit) THEN
3248 SAVEPOINT Copy_data_to_Intf_SP;
3249 END IF;
3250
3251 BEGIN
3252 SELECT NVL(MAX(row_identifier),0)
3253 INTO l_max_row_identifier
3254 FROM EGO_ITM_USR_ATTR_INTRFC
3255 WHERE DATA_SET_ID = p_dest_data_set_id;
3256 EXCEPTION
3257 WHEN OTHERS THEN
3258 l_max_row_identifier := 0;
3259 END;
3260
3261 l_dest_transaction_type := q'# '#'||p_dest_transaction_type||q'#'#';
3262 Debug_Conc_Log('Copy_data_to_Intf: l_max_row_identifier='||l_max_row_identifier);
3263 l_insert_cols := q'#
3264 ( TRANSACTION_ID,
3265 PROCESS_STATUS,
3266 DATA_SET_ID,
3267 TRANSACTION_TYPE,
3268 ORGANIZATION_ID,
3269 ORGANIZATION_CODE,
3270 INVENTORY_ITEM_ID,
3271 ITEM_NUMBER,
3272 ITEM_CATALOG_GROUP_ID,
3273 DATA_LEVEL_ID,
3274 REVISION_ID,
3275 REVISION,
3276 PK1_VALUE,
3277 PK2_VALUE,
3278 PK3_VALUE,
3279 PK4_VALUE,
3280 PK5_VALUE,
3281 ROW_IDENTIFIER,
3282 ATTR_GROUP_TYPE,
3283 ATTR_GROUP_INT_NAME,
3284 ATTR_GROUP_ID,
3285 ATTR_INT_NAME,
3286 ATTR_VALUE_STR,
3287 ATTR_VALUE_NUM,
3288 ATTR_VALUE_DATE,
3289 ATTR_VALUE_UOM,
3290 CREATED_BY,
3291 CREATION_DATE,
3292 LAST_UPDATED_BY,
3293 LAST_UPDATE_DATE,
3294 LAST_UPDATE_LOGIN,
3295 REQUEST_ID,
3296 CHANGE_ID,
3297 CHANGE_LINE_ID,
3298 SOURCE_SYSTEM_ID,
3299 SOURCE_SYSTEM_REFERENCE,
3300 PROG_INT_CHAR1,
3301 PROG_INT_NUM4,
3302 BUNDLE_ID ) #';
3303
3304 Debug_Conc_Log('Copy_data_to_Intf: p_copy_from_intf_table='||p_copy_from_intf_table);
3305 IF FND_API.to_boolean(p_copy_from_intf_table) THEN
3306 l_prog_int_char1_value := q'#'FROM_INTF'#';
3307 l_prog_int_num4_value := 2;
3308 l_select_sql := q'#
3309 ( SELECT
3310 src.TRANSACTION_ID,
3311 #'|| p_dest_process_status || q'# AS PROCESS_STATUS,
3312 #'|| p_dest_data_set_id || q'# AS DATA_SET_ID,
3313 #'|| l_dest_transaction_type|| q'# AS TRANSACTION_TYPE,
3314 src.ORGANIZATION_ID,
3315 src.ORGANIZATION_CODE,
3316 src.INVENTORY_ITEM_ID,
3317 src.ITEM_NUMBER,
3318 src.ITEM_CATALOG_GROUP_ID,
3319 src.DATA_LEVEL_ID,
3320 src.REVISION_ID,
3321 src.REVISION,
3322 src.PK1_VALUE,
3323 src.PK2_VALUE,
3324 src.PK3_VALUE,
3325 src.PK4_VALUE,
3326 src.PK5_VALUE,
3327 #'|| l_max_row_identifier || q'# + src.ROW_IDENTIFIER AS ROW_IDENTIFIER,
3328 src.ATTR_GROUP_TYPE,
3329 src.ATTR_GROUP_INT_NAME,
3330 src.ATTR_GROUP_ID,
3331 src.ATTR_INT_NAME,
3332 src.ATTR_VALUE_STR,
3333 src.ATTR_VALUE_NUM,
3334 src.ATTR_VALUE_DATE,
3335 src.ATTR_VALUE_UOM,
3336 #' || G_USER_ID || q'# AS CREATED_BY,
3337 SYSDATE AS CREATION_DATE,
3338 #' || G_USER_ID || q'# AS LAST_UPDATED_BY,
3339 SYSDATE AS LAST_UPDATE_DATE,
3340 #' || G_LOGIN_ID || q'# AS LAST_UPDATE_LOGIN,
3341 #' || G_REQUEST_ID || q'# AS REQUEST_ID,
3342 src.CHANGE_ID,
3343 src.CHANGE_LINE_ID,
3344 src.SOURCE_SYSTEM_ID,
3345 src.SOURCE_SYSTEM_REFERENCE,
3346 #'|| l_prog_int_char1_value|| q'# AS PROG_INT_CHAR1,
3347 #'|| l_prog_int_num4_value|| q'# AS PROG_INT_NUM4,
3348 src.BUNDLE_ID
3349 FROM (#' || p_source_entity_sql || q'#) src, EGO_FND_DSC_FLX_CTX_EXT ag_ext
3350 WHERE ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(src.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
3351 AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = src.ATTR_GROUP_INT_NAME
3352 AND ag_ext.APPLICATION_ID = 431
3353 AND ( ag_ext.MULTI_ROW= 'N'
3354 OR NOT EXISTS (SELECT NULL FROM EGO_ITM_USR_ATTR_INTRFC intfx
3355 WHERE intfx.DATA_SET_ID = src.DATA_SET_ID
3356 AND intfx.PROCESS_STATUS = #'|| p_dest_process_status || q'#
3357 AND intfx.INVENTORY_ITEM_ID = src.INVENTORY_ITEM_ID
3358 AND intfx.ORGANIZATION_ID = src.ORGANIZATION_ID
3359 AND intfx.DATA_LEVEL_ID = src.DATA_LEVEL_ID
3360 AND NVL(intfx.BUNDLE_ID, -1) = NVL(src.BUNDLE_ID, -1)
3361 AND NVL(intfx.REVISION_ID, -1) = NVL(src.REVISION_ID, -1)
3362 AND NVL(intfx.PK1_VALUE, -1) = NVL(src.PK1_VALUE, -1)
3363 AND NVL(intfx.PK2_VALUE, -1) = NVL(src.PK2_VALUE, -1)
3364 AND NVL(intfx.PK3_VALUE, -1) = NVL(src.PK3_VALUE, -1)
3365 AND NVL(intfx.PK4_VALUE, -1) = NVL(src.PK4_VALUE, -1)
3366 AND NVL(intfx.PK5_VALUE, -1) = NVL(src.PK5_VALUE, -1)
3367 AND NVL(intfx.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = NVL(src.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
3368 AND intfx.ATTR_GROUP_INT_NAME = src.ATTR_GROUP_INT_NAME
3369 )
3370 )
3371 ) source #';
3372
3373 l_where_clause := q'#
3374 ON
3375 ( intf.DATA_SET_ID = source.DATA_SET_ID
3376 AND intf.PROCESS_STATUS = source.PROCESS_STATUS
3377 AND intf.INVENTORY_ITEM_ID = source.INVENTORY_ITEM_ID
3378 AND intf.ORGANIZATION_ID = source.ORGANIZATION_ID
3379 AND intf.DATA_LEVEL_ID = source.DATA_LEVEL_ID
3380 AND NVL(intf.BUNDLE_ID, -1) = NVL(source.BUNDLE_ID, -1)
3381 AND NVL(intf.REVISION_ID, -1) = NVL(source.REVISION_ID, -1)
3382 AND NVL(intf.PK1_VALUE, -1) = NVL(source.PK1_VALUE, -1)
3383 AND NVL(intf.PK2_VALUE, -1) = NVL(source.PK2_VALUE, -1)
3384 AND NVL(intf.PK3_VALUE, -1) = NVL(source.PK3_VALUE, -1)
3385 AND NVL(intf.PK4_VALUE, -1) = NVL(source.PK4_VALUE, -1)
3386 AND NVL(intf.PK5_VALUE, -1) = NVL(source.PK5_VALUE, -1)
3387 AND NVL(intf.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = NVL(source.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
3388 AND intf.ATTR_GROUP_INT_NAME = source.ATTR_GROUP_INT_NAME
3389 AND intf.ATTR_INT_NAME = source.ATTR_INT_NAME
3390 ) #';
3391
3392 l_when_matched := q'#
3393 WHEN MATCHED THEN
3394 UPDATE SET
3395 intf.ATTR_VALUE_STR = source.ATTR_VALUE_STR,
3396 intf.ATTR_VALUE_NUM = source.ATTR_VALUE_NUM,
3397 intf.ATTR_VALUE_UOM = source.ATTR_VALUE_UOM,
3398 intf.ATTR_VALUE_DATE = source.ATTR_VALUE_DATE,
3399 intf.PROG_INT_NUM4 = 3
3400 WHERE NVL(intf.PROG_INT_NUM4, -1) <> 0
3401 #';
3402
3403 l_when_not_matched := q'#
3404 WHEN NOT MATCHED THEN
3405 INSERT #' || l_insert_cols || q'# VALUES
3406 ( source.TRANSACTION_ID,
3407 source.PROCESS_STATUS,
3408 source.DATA_SET_ID,
3409 source.TRANSACTION_TYPE,
3410 source.ORGANIZATION_ID,
3411 source.ORGANIZATION_CODE,
3412 source.INVENTORY_ITEM_ID,
3413 source.ITEM_NUMBER,
3414 source.ITEM_CATALOG_GROUP_ID,
3415 source.DATA_LEVEL_ID,
3416 source.REVISION_ID,
3417 source.REVISION,
3418 source.PK1_VALUE,
3419 source.PK2_VALUE,
3420 source.PK3_VALUE,
3421 source.PK4_VALUE,
3422 source.PK5_VALUE,
3423 source.ROW_IDENTIFIER,
3424 source.ATTR_GROUP_TYPE,
3425 source.ATTR_GROUP_INT_NAME,
3426 source.ATTR_GROUP_ID,
3427 source.ATTR_INT_NAME,
3428 source.ATTR_VALUE_STR,
3429 source.ATTR_VALUE_NUM,
3430 source.ATTR_VALUE_DATE,
3431 source.ATTR_VALUE_UOM,
3432 source.CREATED_BY,
3433 source.CREATION_DATE,
3434 source.LAST_UPDATED_BY,
3435 source.LAST_UPDATE_DATE,
3436 source.LAST_UPDATE_LOGIN,
3437 source.REQUEST_ID,
3438 source.CHANGE_ID,
3439 source.CHANGE_LINE_ID,
3440 source.SOURCE_SYSTEM_ID,
3441 source.SOURCE_SYSTEM_REFERENCE,
3442 source.PROG_INT_CHAR1,
3443 source.PROG_INT_NUM4,
3444 source.BUNDLE_ID )
3445 #';
3446 l_dynamic_sql := 'MERGE INTO EGO_ITM_USR_ATTR_INTRFC intf USING ' ||
3447 l_select_sql || l_where_clause || l_when_matched || l_when_not_matched;
3448
3449 Debug_Conc_Log('Copy_data_to_Intf: l_dynamic_sql='||l_dynamic_sql);
3450 EXECUTE IMMEDIATE l_dynamic_sql;
3451 Debug_Conc_Log('Copy_data_to_Intf: Processed '||SQL%ROWCOUNT||' rows');
3452 ELSE -- FND_API.to_boolean(p_copy_from_intf_table)
3453 l_prog_int_char1_value := q'#'FROM_PROD'#';
3454 l_prog_int_num4_value := 1;
3455 l_attr_group_sql := 'SELECT DISTINCT ATTR_GROUP_ID FROM ( '|| p_source_attr_groups_sql ||' ) ';
3456 OPEN l_dynamic_cursor FOR l_attr_group_sql;
3457 LOOP
3458 FETCH l_dynamic_cursor INTO l_curr_attr_grp_id;
3459 EXIT WHEN l_dynamic_cursor%NOTFOUND;
3460 Debug_Conc_Log('Copy_data_to_Intf: Processing AG, ID='||l_curr_attr_grp_id);
3461
3462 l_attr_value_str_sql := '(CASE';
3463 l_attr_value_date_sql := '(CASE';
3464 l_attr_value_num_sql := '(CASE';
3465 l_attr_value_uom_sql := '(CASE';
3466 FOR i IN c_attr_rec(l_curr_attr_grp_id)
3467 LOOP
3468 l_attr_value_str_sql := l_attr_value_str_sql || q'# WHEN attr_col.END_USER_COLUMN_NAME = '#'|| i.ATTR_NAME || q'#' #';
3469 l_attr_value_date_sql := l_attr_value_date_sql || q'# WHEN attr_col.END_USER_COLUMN_NAME = '#'|| i.ATTR_NAME || q'#' #';
3470 l_attr_value_num_sql := l_attr_value_num_sql || q'# WHEN attr_col.END_USER_COLUMN_NAME = '#'|| i.ATTR_NAME || q'#' #';
3471 l_attr_value_uom_sql := l_attr_value_uom_sql || q'# WHEN attr_col.END_USER_COLUMN_NAME = '#'|| i.ATTR_NAME || q'#' #';
3472 l_multi_row := i.MULTI_ROW;
3473
3474 IF i.DATA_TYPE_CODE IN (EGO_EXT_FWK_PUB.G_TRANS_TEXT_DATA_TYPE, EGO_EXT_FWK_PUB.G_CHAR_DATA_TYPE) THEN
3475 l_attr_value_str_sql := l_attr_value_str_sql || ' THEN src.'||i.DATABASE_COLUMN;
3476 l_attr_value_date_sql := l_attr_value_date_sql || ' THEN NULL ';
3477 l_attr_value_num_sql := l_attr_value_num_sql || ' THEN NULL ';
3478 l_attr_value_uom_sql := l_attr_value_uom_sql || ' THEN NULL ';
3479 ELSIF i.DATA_TYPE_CODE IN (EGO_EXT_FWK_PUB.G_DATE_DATA_TYPE, EGO_EXT_FWK_PUB.G_DATE_TIME_DATA_TYPE) THEN
3480 l_attr_value_str_sql := l_attr_value_str_sql || ' THEN NULL ';
3481 l_attr_value_date_sql := l_attr_value_date_sql || ' THEN src.'||i.DATABASE_COLUMN;
3482 l_attr_value_num_sql := l_attr_value_num_sql || ' THEN NULL ';
3483 l_attr_value_uom_sql := l_attr_value_uom_sql || ' THEN NULL ';
3484 ELSE
3485 l_attr_value_str_sql := l_attr_value_str_sql || ' THEN NULL ';
3486 l_attr_value_date_sql := l_attr_value_date_sql || ' THEN NULL ';
3487 IF i.UOM_CLASS IS NULL THEN
3488 l_attr_value_num_sql := l_attr_value_num_sql || ' THEN src.'||i.DATABASE_COLUMN;
3489 l_attr_value_uom_sql := l_attr_value_uom_sql || ' THEN NULL ';
3490 ELSE
3491 l_dummy_char := 'UOM'||SUBSTR(i.DATABASE_COLUMN, 2);
3492 l_attr_value_num_sql := l_attr_value_num_sql || q'# THEN src.#' || i.DATABASE_COLUMN || q'#
3493 /(SELECT CONVERSION_RATE
3494 FROM MTL_UOM_CONVERSIONS
3495 WHERE UOM_CLASS = '#' || i.UOM_CLASS ||q'#'
3496 AND UOM_CODE = src.#' || l_dummy_char || q'#
3497 AND ROWNUM = 1)#';
3498 l_attr_value_uom_sql := l_attr_value_uom_sql || ' THEN src.'|| l_dummy_char ||' ';
3499 END IF; --IF i.UOM_CLASS IS NULL THEN
3500 END IF; --IF i.DATA_TYPE_CODE IN
3501 END LOOP;
3502 l_attr_value_str_sql := l_attr_value_str_sql || ' END) AS ATTR_VALUE_STR, ';
3503 l_attr_value_date_sql := l_attr_value_date_sql || ' END) AS ATTR_VALUE_DATE, ';
3504 l_attr_value_num_sql := l_attr_value_num_sql || ' END) AS ATTR_VALUE_NUM, ';
3505 l_attr_value_uom_sql := l_attr_value_uom_sql || ' END) AS ATTR_VALUE_UOM, ';
3506
3507 l_select_sql := q'#
3508 SELECT
3509 src.TRANSACTION_ID,
3510 #'|| p_dest_process_status || q'# AS PROCESS_STATUS,
3511 #'|| p_dest_data_set_id || q'# AS DATA_SET_ID,
3512 #'|| l_dest_transaction_type|| q'# AS TRANSACTION_TYPE,
3513 src.ORGANIZATION_ID,
3514 src.ORGANIZATION_CODE,
3515 src.INVENTORY_ITEM_ID,
3516 src.ITEM_NUMBER,
3517 src.ITEM_CATALOG_GROUP_ID,
3518 src.DATA_LEVEL_ID,
3519 src.REVISION_ID,
3520 src.REVISION,
3521 src.PK1_VALUE,
3522 src.PK2_VALUE,
3523 src.PK3_VALUE,
3524 src.PK4_VALUE,
3525 src.PK5_VALUE,
3526 #'|| l_max_row_identifier || q'# + src.ROW_IDENTIFIER AS ROW_IDENTIFIER,
3527 ag_ext.DESCRIPTIVE_FLEXFIELD_NAME,
3528 ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE,
3529 ag_ext.ATTR_GROUP_ID,
3530 attr_col.END_USER_COLUMN_NAME, #' ||
3531 l_attr_value_str_sql ||
3532 l_attr_value_num_sql ||
3533 l_attr_value_date_sql ||
3534 l_attr_value_uom_sql ||
3535 G_USER_ID || q'# AS CREATED_BY,
3536 SYSDATE AS CREATION_DATE,
3537 #' || G_USER_ID || q'# AS LAST_UPDATED_BY,
3538 SYSDATE AS LAST_UPDATE_DATE,
3539 #' || G_LOGIN_ID || q'# AS LAST_UPDATE_LOGIN,
3540 #' || G_REQUEST_ID || q'# AS REQUEST_ID,
3541 src.CHANGE_ID,
3542 src.CHANGE_LINE_ID,
3543 src.SOURCE_SYSTEM_ID,
3544 src.SOURCE_SYSTEM_REFERENCE,
3545 #'|| l_prog_int_char1_value|| q'# AS PROG_INT_CHAR1,
3546 #'|| l_prog_int_num4_value|| q'# AS PROG_INT_NUM4,
3547 src.BUNDLE_ID
3548 FROM (#' || p_source_entity_sql || q'#) src,
3549 FND_DESCR_FLEX_COLUMN_USAGES attr_col,
3550 EGO_FND_DSC_FLX_CTX_EXT ag_ext #';
3551
3552 l_where_clause := q'#
3553 WHERE src.ATTR_GROUP_ID = #' || l_curr_attr_grp_id || q'#
3554 AND src.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
3555 AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = attr_col.DESCRIPTIVE_FLEXFIELD_NAME
3556 AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = attr_col.DESCRIPTIVE_FLEX_CONTEXT_CODE
3557 AND ag_ext.APPLICATION_ID = attr_col.APPLICATION_ID
3558 AND attr_col.ENABLED_FLAG = 'Y'
3559 AND NOT EXISTS (SELECT NULL FROM EGO_ITM_USR_ATTR_INTRFC intf
3560 WHERE intf.DATA_SET_ID = src.DATA_SET_ID
3561 AND intf.PROCESS_STATUS = #' || p_dest_process_status || q'#
3562 AND intf.INVENTORY_ITEM_ID = src.INVENTORY_ITEM_ID
3563 AND intf.ORGANIZATION_ID = src.ORGANIZATION_ID
3564 AND intf.DATA_LEVEL_ID = src.DATA_LEVEL_ID
3565 AND NVL(intf.BUNDLE_ID, -1) = NVL(src.BUNDLE_ID, -1)
3566 AND NVL(intf.REVISION_ID, -1) = NVL(src.REVISION_ID, -1)
3567 AND NVL(intf.PK1_VALUE, -1) = NVL(src.PK1_VALUE, -1)
3568 AND NVL(intf.PK2_VALUE, -1) = NVL(src.PK2_VALUE, -1)
3569 AND NVL(intf.PK3_VALUE, -1) = NVL(src.PK3_VALUE, -1)
3570 AND NVL(intf.PK4_VALUE, -1) = NVL(src.PK4_VALUE, -1)
3571 AND NVL(intf.PK5_VALUE, -1) = NVL(src.PK5_VALUE, -1)
3572 AND NVL(intf.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = ag_ext.DESCRIPTIVE_FLEXFIELD_NAME
3573 AND intf.ATTR_GROUP_INT_NAME = ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE #';
3574 IF l_multi_row = 'Y' THEN
3575 l_where_clause := l_where_clause || ' ) ';
3576 ELSE
3577 l_where_clause := l_where_clause || ' AND intf.ATTR_INT_NAME = attr_col.END_USER_COLUMN_NAME ) ';
3578 END IF;
3579
3580 l_dynamic_sql := 'INSERT INTO EGO_ITM_USR_ATTR_INTRFC ' || l_insert_cols || l_select_sql || l_where_clause;
3581 Debug_Conc_Log('Copy_data_to_Intf: l_dynamic_sql='||l_dynamic_sql);
3582 EXECUTE IMMEDIATE l_dynamic_sql;
3583 Debug_Conc_Log('Copy_data_to_Intf: Inserted '||SQL%ROWCOUNT||' rows');
3584 END LOOP; -- attr_group_id LOOP
3585 CLOSE l_dynamic_cursor;
3586 END IF; -- FND_API.to_boolean(p_copy_from_intf_table)
3587
3588 Debug_Conc_Log('Copy_data_to_Intf: Done inserting');
3589 IF p_dest_process_status = G_PS_IN_PROCESS THEN
3590 UPDATE ego_itm_usr_attr_intrfc
3591 SET PROG_INT_NUM1 = NULL
3592 ,PROG_INT_NUM2 = NULL
3593 ,PROG_INT_NUM3 = NULL
3594 ,PROG_INT_CHAR1 = 'N'
3595 ,PROG_INT_CHAR2 = 'N'
3596 ,REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
3597 ,PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID
3598 ,PROGRAM_ID = FND_GLOBAL.CONC_PROGRAM_ID
3599 ,PROGRAM_UPDATE_DATE = SYSDATE
3600 WHERE PROCESS_STATUS = p_dest_process_status
3601 AND DATA_SET_ID = p_dest_data_set_id
3602 AND TRANSACTION_TYPE = p_dest_transaction_type
3603 AND PROG_INT_CHAR1 IN ('FROM_INTF', 'FROM_PROD');
3604 END IF;
3605
3606 IF p_cleanup_row_identifiers = FND_API.G_TRUE THEN
3607 Debug_Conc_Log('Copy_data_to_Intf: Calling Clean_Up_UDA_Row_Idents with process_status='||p_dest_process_status);
3608 EGO_IMPORT_PVT.Clean_Up_UDA_Row_Idents(
3609 p_batch_id => p_dest_data_set_id,
3610 p_process_status => p_dest_process_status,
3611 p_ignore_item_num_upd => FND_API.G_TRUE,
3612 p_commit => FND_API.G_FALSE );
3613 Debug_Conc_Log('Copy_data_to_Intf: Clean_Up_UDA_Row_Idents Done.');
3614 END IF;
3615 Debug_Conc_Log('Copy_data_to_Intf: Done');
3616 x_return_status := FND_API.G_RET_STS_SUCCESS;
3617 EXCEPTION WHEN OTHERS THEN
3618 IF FND_API.To_Boolean(p_commit) THEN
3619 ROLLBACK TO Copy_data_to_Intf_SP;
3620 END IF;
3621 IF l_dynamic_cursor%ISOPEN THEN
3622 CLOSE l_dynamic_cursor;
3623 END IF;
3624 Debug_Conc_Log('Copy_data_to_Intf: Error-'||SQLERRM);
3625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3626 x_msg_data := SQLERRM;
3627 END Copy_data_to_Intf;
3628
3629 ----------------------------------------------------------------------
3630
3631 END EGO_ITEM_USER_ATTRS_CP_PUB;