[Home] [Help]
PACKAGE BODY: APPS.EGO_STYLE_SKU_ITEM_PVT
Source
1 PACKAGE BODY EGO_STYLE_SKU_ITEM_PVT AS
2 /* $Header: EGOSITMB.pls 120.22 2010/09/17 07:23:56 ccsingh ship $ */
3
4 G_LOG_TIMESTAMP_FORMAT CONSTANT VARCHAR2( 30 ) := 'dd-mon-yyyy hh:mi:ss.ff';
5
6 g_ego_item_object_id NUMBER;
7 g_ego_item_data_level_id NUMBER;
8
9 CURSOR Is_STYLE_SKU_EXIST(l_catalog_group_id NUMBER,
10 l_flag VARCHAR2)
11 IS
12 SELECT 1
13 FROM mtl_system_items_b
14 WHERE rownum = 1 AND
15 style_item_flag = l_flag AND
16 item_catalog_group_id IN
17 (SELECT item_catalog_group_id
18 FROM mtl_item_catalog_groups_b
19 CONNECT BY PRIOR item_catalog_group_id = parent_catalog_group_id
20 START WITH item_catalog_group_id = l_catalog_group_id);
21
22 /*
23 * This method writes into concurrent program log
24 */
25 PROCEDURE Debug_Conc_Log( p_message IN VARCHAR2
26 , p_add_timestamp IN BOOLEAN DEFAULT TRUE )
27 IS
28 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
29 BEGIN
30 IF l_inv_debug_level IN(101, 102) THEN
31 INVPUTLI.info( ( CASE
32 WHEN p_add_timestamp THEN to_char( systimestamp, G_LOG_TIMESTAMP_FORMAT ) || ': '
33 ELSE ''
34 END )
35 || p_message );
36 END IF;
37 END Debug_Conc_Log;
38
39 FUNCTION IsStyle_Item_Exist_For_ICC ( p_item_catalog_group_id IN NUMBER ) RETURN VARCHAR2
40 IS
41 l_Style_Item_Exist BOOLEAN := FALSE;
42 l_Style_Item_Count NUMBER;
43 BEGIN
44
45 /* SELECT COUNT(1)
46 INTO l_Style_Item_Count
47 FROM MTL_SYSTEM_ITEMS_B
48 WHERE STYLE_ITEM_FLAG = 'Y'
49 AND ITEM_CATALOG_GROUP_ID IN
50 ( SELECT ITEM_CATALOG_GROUP_ID
51 FROM MTL_ITEM_CATALOG_GROUPS_B
52 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
53 START WITH ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id
54 );
55 IF ( l_Style_Item_Count > 0 ) THEN
56 l_Style_Item_Exist := TRUE;
57 END IF;
58 IF( l_Style_Item_Exist ) THEN
59 RETURN FND_API.G_TRUE;
60 ELSE
61 RETURN FND_API.G_FALSE;
62 END IF; */
63
64 OPEN IS_STYLE_SKU_EXIST(p_item_catalog_group_id, 'Y');
65 FETCH IS_STYLE_SKU_EXIST INTO l_Style_Item_Count;
66 l_Style_Item_Exist := Is_STYLE_SKU_Exist%FOUND;
67 CLOSE IS_STYLE_SKU_EXIST;
68
69 IF( l_Style_Item_Exist ) THEN
70 RETURN FND_API.G_TRUE;
71 ELSE
72 RETURN FND_API.G_FALSE;
73 END IF;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 CLOSE IS_STYLE_SKU_EXIST;
78 RETURN FND_API.G_FALSE;
79 END IsStyle_Item_Exist_For_ICC;
80
81 FUNCTION IsSKU_Item_Exist_For_ICC ( p_item_catalog_group_id IN NUMBER ) RETURN VARCHAR2
82 IS
83 l_SKU_Item_Exist BOOLEAN := FALSE;
84 l_SKU_Item_Count NUMBER;
85 BEGIN
86
87 /*SELECT COUNT(1)
88 INTO l_SKU_Item_Count
89 FROM MTL_SYSTEM_ITEMS_B
90 WHERE STYLE_ITEM_FLAG = 'N'
91 AND ITEM_CATALOG_GROUP_ID IN
92 ( SELECT ITEM_CATALOG_GROUP_ID
93 FROM MTL_ITEM_CATALOG_GROUPS_B
94 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
95 START WITH ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id
96 );
97 IF ( l_SKU_Item_Count > 0 ) THEN
98 l_SKU_Item_Exist := TRUE;
99 END IF;
100 IF( l_SKU_Item_Exist ) THEN
101 RETURN FND_API.G_TRUE;
102 ELSE
103 RETURN FND_API.G_FALSE;
104 END IF; */
105
106 OPEN IS_STYLE_SKU_EXIST(p_item_catalog_group_id, 'N');
107 FETCH IS_STYLE_SKU_EXIST INTO l_SKU_Item_Count;
108 l_SKU_Item_Exist := IS_STYLE_SKU_EXIST%FOUND;
109 CLOSE IS_STYLE_SKU_EXIST;
110
111 IF( l_SKU_Item_Exist ) THEN
112 RETURN FND_API.G_TRUE;
113 ELSE
114 RETURN FND_API.G_FALSE;
115 END IF;
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 CLOSE Is_STYLE_SKU_Exist;
120 RETURN FND_API.G_FALSE;
121 END IsSKU_Item_Exist_For_ICC;
122
123 PROCEDURE Process_Items
124 (
125 p_set_process_id IN NUMBER
126 ,p_Process_Flag IN NUMBER
127 ,p_commit IN VARCHAR2 DEFAULT G_FALSE
128 ,p_Transaction_Type IN VARCHAR2 DEFAULT NULL
129 ,p_Template_Id IN NUMBER DEFAULT NULL
130 ,p_copy_inventory_item_Id IN NUMBER DEFAULT NULL
131 ,p_copy_revision_Id IN NUMBER DEFAULT NULL
132 ,p_inventory_item_id IN NUMBER DEFAULT NULL
133 ,p_organization_id IN NUMBER DEFAULT NULL
134 ,p_description IN VARCHAR2 DEFAULT NULL
135 ,p_long_description IN VARCHAR2 DEFAULT NULL
136 ,p_primary_uom_code IN VARCHAR2 DEFAULT NULL
137 ,p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL
138 ,p_item_type IN VARCHAR2 DEFAULT NULL
139 ,p_inventory_item_status_code IN VARCHAR2 DEFAULT NULL
140 ,p_allowed_units_lookup_code IN NUMBER DEFAULT NULL
141 ,p_item_catalog_group_id IN NUMBER DEFAULT NULL
142 ,p_bom_enabled_flag IN VARCHAR2 DEFAULT NULL
143 ,p_eng_item_flag IN VARCHAR2 DEFAULT NULL
144 ,p_weight_uom_code IN VARCHAR2 DEFAULT NULL
145 ,p_unit_weight IN NUMBER DEFAULT NULL
146 ,p_Item_Number IN VARCHAR2 DEFAULT NULL
147 ,p_Style_Item_Flag IN VARCHAR2 DEFAULT NULL
148 ,p_Style_Item_Id IN NUMBER DEFAULT NULL
149 ,p_Style_Item_Number IN VARCHAR2 DEFAULT NULL
150 ,p_Gdsn_Outbound_Enabled_Flag IN VARCHAR2 DEFAULT NULL
151 ,p_Trade_Item_Descriptor IN VARCHAR2 DEFAULT NULL
152 )
153 IS
154 l_copy_from_organization_id NUMBER;
155 BEGIN
156 IF (p_copy_inventory_item_Id IS NOT NULL) THEN
157 l_copy_from_organization_id := p_organization_id;
158 END IF;
159 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
160 ( SET_PROCESS_ID,
161 PROCESS_FLAG,
162 TRANSACTION_TYPE,
163 TEMPLATE_ID,
164 COPY_ITEM_ID,
165 COPY_REVISION_ID,
166 INVENTORY_ITEM_ID,
167 ORGANIZATION_ID,
168 DESCRIPTION,
169 LONG_DESCRIPTION,
170 PRIMARY_UOM_CODE,
171 PRIMARY_UNIT_OF_MEASURE,
172 ITEM_TYPE,
173 INVENTORY_ITEM_STATUS_CODE,
174 ALLOWED_UNITS_LOOKUP_CODE,
175 ITEM_CATALOG_GROUP_ID,
176 BOM_ENABLED_FLAG,
177 ENG_ITEM_FLAG,
178 WEIGHT_UOM_CODE,
179 UNIT_WEIGHT,
180 ITEM_NUMBER,
181 STYLE_ITEM_FLAG,
182 STYLE_ITEM_ID,
183 STYLE_ITEM_NUMBER,
184 GDSN_OUTBOUND_ENABLED_FLAG,
185 TRADE_ITEM_DESCRIPTOR,
186 COPY_ORGANIZATION_ID
187 )
188 VALUES
189 ( p_set_process_id,
190 p_Process_Flag,
191 p_Transaction_Type,
192 p_Template_Id,
193 p_copy_inventory_item_Id ,
194 p_copy_revision_Id,
195 p_inventory_item_id ,
196 p_organization_id,
197 p_description,
198 p_long_description ,
199 p_primary_uom_code,
200 p_primary_unit_of_measure,
201 p_item_type,
202 p_inventory_item_status_code,
203 p_allowed_units_lookup_code,
204 p_item_catalog_group_id,
205 p_bom_enabled_flag,
206 p_eng_item_flag ,
207 p_weight_uom_code ,
208 p_unit_weight ,
209 p_Item_Number,
210 p_Style_Item_Flag,
211 p_Style_Item_Id,
212 p_Style_item_number,
213 p_Gdsn_Outbound_Enabled_Flag,
214 p_Trade_Item_Descriptor,
215 l_copy_from_organization_id
216 );
217 END Process_Items;
218
219 PROCEDURE Process_Items ( p_commit IN VARCHAR2 DEFAULT G_FALSE
220 ,p_Item_Intf_Data_Tab IN OUT NOCOPY EGO_ITEM_INTF_DATA_TAB
221 ,x_return_status OUT NOCOPY VARCHAR2
222 ,x_msg_data OUT NOCOPY VARCHAR2
223 ,x_msg_count OUT NOCOPY NUMBER )
224 IS
225 l_set_process_id NUMBER ;
226 l_process_flag NUMBER ;
227 l_transaction_type VARCHAR2(10) ;
228 l_template_id NUMBER ;
229 l_copy_inventory_item_id NUMBER ;
230 l_copy_revision_id NUMBER ;
231 l_inventory_item_id NUMBER ;
232 l_organization_id NUMBER ;
233 l_description VARCHAR2(240) ;
234 l_long_description VARCHAR2(4000) ;
235 l_primary_uom_code VARCHAR2(3) ;
236 l_primary_unit_of_measure VARCHAR2(25) ;
237 l_item_type VARCHAR2(30) ;
238 l_inventory_item_status_code VARCHAR2(10) ;
239 l_allowed_units_lookup_code NUMBER ;
240 l_tracking_quantity_ind VARCHAR2(30) ;
241 l_ont_pricing_qty_source VARCHAR2(30) ;
242 l_secondary_default_ind VARCHAR2(30) ;
243 l_dual_uom_deviation_high NUMBER ;
244 l_dual_uom_deviation_low NUMBER ;
245 l_secondary_uom_code VARCHAR2(3) ;
246 l_lifecycle_id NUMBER ;
247 l_current_phase_id NUMBER ;
248 l_item_catalog_group_id NUMBER ;
249 l_bom_enabled_flag VARCHAR2(1) ;
250 l_eng_item_flag VARCHAR2(1) ;
251 l_weight_uom_code VARCHAR2(3) ;
252 l_unit_weight NUMBER ;
253 l_item_number VARCHAR2(700) ;
254 l_style_item_flag VARCHAR2(1) ;
255 l_style_item_id NUMBER ;
256 l_style_item_number VARCHAR2(700) ;
257 l_gdsn_outbound_enabled_flag VARCHAR2(1) ;
258 l_trade_item_descriptor VARCHAR2(35) ;
259 l_transaction_id NUMBER;
260 l_source_system_reference VARCHAR2(255);
261 l_copy_from_organization_id NUMBER;
262 l_org_assignment VARCHAR2(1);
263 x_ret_sts VARCHAR2(1);
264
265 CURSOR ITEM_DATA_RECS
266 IS
267 SELECT A.SET_PROCESS_ID SET_PROCESS_ID ,
268 A.PROCESS_FLAG PROCESS_FLAG ,
269 A.TRANSACTION_TYPE TRANSACTION_TYPE ,
270 A.TEMPLATE_ID TEMPLATE_ID ,
271 A.COPY_INVENTORY_ITEM_ID COPY_INVENTORY_ITEM_ID ,
272 A.COPY_REVISION_ID COPY_REVISION_ID ,
273 A.INVENTORY_ITEM_ID INVENTORY_ITEM_ID ,
274 A.ORGANIZATION_ID ORGANIZATION_ID ,
275 A.DESCRIPTION DESCRIPTION ,
276 A.LONG_DESCRIPTION LONG_DESCRIPTION ,
277 A.PRIMARY_UOM_CODE PRIMARY_UOM_CODE ,
278 A.PRIMARY_UNIT_OF_MEASURE PRIMARY_UNIT_OF_MEASURE ,
279 A.ITEM_TYPE ITEM_TYPE ,
280 A.INVENTORY_ITEM_STATUS_CODE INVENTORY_ITEM_STATUS_CODE ,
281 A.ALLOWED_UNITS_LOOKUP_CODE ALLOWED_UNITS_LOOKUP_CODE ,
282 A.TRACKING_QUANTITY_IND TRACKING_QUANTITY_IND ,
283 A.ONT_PRICING_QTY_SOURCE ONT_PRICING_QTY_SOURCE ,
284 A.SECONDARY_DEFAULT_IND SECONDARY_DEFAULT_IND ,
285 A.DUAL_UOM_DEVIATION_HIGH DUAL_UOM_DEVIATION_HIGH ,
286 A.DUAL_UOM_DEVIATION_LOW DUAL_UOM_DEVIATION_LOW ,
287 A.SECONDARY_UOM_CODE SECONDARY_UOM_CODE ,
288 A.LIFECYCLE_ID LIFECYCLE_ID ,
289 A.CURRENT_PHASE_ID CURRENT_PHASE_ID ,
290 A.ITEM_CATALOG_GROUP_ID ITEM_CATALOG_GROUP_ID ,
291 A.BOM_ENABLED_FLAG BOM_ENABLED_FLAG ,
292 A.ENG_ITEM_FLAG ENG_ITEM_FLAG ,
293 A.WEIGHT_UOM_CODE WEIGHT_UOM_CODE ,
294 A.UNIT_WEIGHT UNIT_WEIGHT ,
295 A.ITEM_NUMBER ITEM_NUMBER ,
296 A.STYLE_ITEM_FLAG STYLE_ITEM_FLAG ,
297 A.STYLE_ITEM_ID STYLE_ITEM_ID ,
298 A.GDSN_OUTBOUND_ENABLED_FLAG GDSN_OUTBOUND_ENABLED_FLAG ,
299 A.TRADE_ITEM_DESCRIPTOR TRADE_ITEM_DESCRIPTOR ,
300 A.TRANSACTION_ID TRANSACTION_ID,
301 A.SOURCE_SYSTEM_REFERENCE SOURCE_SYSTEM_REFERENCE
302 FROM THE (SELECT CAST( p_Item_Intf_Data_Tab AS EGO_ITEM_INTF_DATA_TAB) FROM DUAL)
303 A;
304 BEGIN
305 FOR item_data_rec IN ITEM_DATA_RECS
306 LOOP
307 l_set_process_id := item_data_rec.SET_PROCESS_ID ;
308 l_process_flag := item_data_rec.PROCESS_FLAG ;
309 l_transaction_type := item_data_rec.TRANSACTION_TYPE ;
310 l_template_id := item_data_rec.TEMPLATE_ID ;
311 l_copy_inventory_item_id := item_data_rec.COPY_INVENTORY_ITEM_ID ;
312 l_copy_revision_id := item_data_rec.COPY_REVISION_ID ;
313 l_inventory_item_id := item_data_rec.INVENTORY_ITEM_ID ;
314 l_organization_id := item_data_rec.ORGANIZATION_ID ;
315 l_description := item_data_rec.DESCRIPTION ;
316 l_long_description := item_data_rec.LONG_DESCRIPTION ;
317 l_primary_uom_code := item_data_rec.PRIMARY_UOM_CODE ;
318 l_primary_unit_of_measure := item_data_rec.PRIMARY_UNIT_OF_MEASURE ;
319 l_item_type := item_data_rec.ITEM_TYPE ;
320 l_inventory_item_status_code := item_data_rec.INVENTORY_ITEM_STATUS_CODE ;
321 l_allowed_units_lookup_code := item_data_rec.ALLOWED_UNITS_LOOKUP_CODE ;
322 l_tracking_quantity_ind := item_data_rec.TRACKING_QUANTITY_IND ;
323 l_ont_pricing_qty_source := item_data_rec.ONT_PRICING_QTY_SOURCE ;
324 l_secondary_default_ind := item_data_rec.SECONDARY_DEFAULT_IND ;
325 l_dual_uom_deviation_high := item_data_rec.DUAL_UOM_DEVIATION_HIGH ;
326 l_dual_uom_deviation_low := item_data_rec.DUAL_UOM_DEVIATION_LOW ;
327 l_secondary_uom_code := item_data_rec.SECONDARY_UOM_CODE ;
328 l_lifecycle_id := item_data_rec.LIFECYCLE_ID ;
329 l_current_phase_id := item_data_rec.CURRENT_PHASE_ID ;
330 l_item_catalog_group_id := item_data_rec.ITEM_CATALOG_GROUP_ID ;
331 l_bom_enabled_flag := item_data_rec.BOM_ENABLED_FLAG ;
332 l_eng_item_flag := item_data_rec.ENG_ITEM_FLAG ;
333 l_weight_uom_code := item_data_rec.WEIGHT_UOM_CODE ;
334 l_unit_weight := item_data_rec.UNIT_WEIGHT ;
335 l_item_number := item_data_rec.ITEM_NUMBER ;
336 l_style_item_flag := item_data_rec.STYLE_ITEM_FLAG ;
337 l_style_item_id := item_data_rec.STYLE_ITEM_ID ;
338 l_gdsn_outbound_enabled_flag := item_data_rec.GDSN_OUTBOUND_ENABLED_FLAG ;
339 l_trade_item_descriptor := item_data_rec.TRADE_ITEM_DESCRIPTOR ;
340 l_transaction_id := item_data_rec.TRANSACTION_ID ;
341 l_source_system_reference := item_data_rec.SOURCE_SYSTEM_REFERENCE ;
342
343 IF l_copy_inventory_item_id IS NOT NULL THEN
344 l_copy_from_organization_id := l_organization_id;
345 END IF;
346
347 BEGIN
348 SELECT 'Y' INTO l_org_assignment
349 FROM MTL_PARAMETERS
350 WHERE ORGANIZATION_ID = l_organization_id
351 AND ORGANIZATION_ID <> MASTER_ORGANIZATION_ID;
352 EXCEPTION WHEN NO_DATA_FOUND THEN
353 l_org_assignment := 'N';
354 l_inventory_item_id := NULL;
355 END;
356
357 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
358 ( SET_PROCESS_ID ,
359 PROCESS_FLAG ,
360 TRANSACTION_TYPE ,
361 TEMPLATE_ID ,
362 COPY_ITEM_ID ,
363 COPY_REVISION_ID ,
364 INVENTORY_ITEM_ID ,
365 ORGANIZATION_ID ,
366 DESCRIPTION ,
367 LONG_DESCRIPTION ,
368 PRIMARY_UOM_CODE ,
369 PRIMARY_UNIT_OF_MEASURE ,
370 ITEM_TYPE ,
371 INVENTORY_ITEM_STATUS_CODE ,
372 ALLOWED_UNITS_LOOKUP_CODE ,
373 TRACKING_QUANTITY_IND ,
374 ONT_PRICING_QTY_SOURCE ,
375 SECONDARY_DEFAULT_IND ,
376 DUAL_UOM_DEVIATION_HIGH ,
377 DUAL_UOM_DEVIATION_LOW ,
378 SECONDARY_UOM_CODE ,
379 LIFECYCLE_ID ,
380 CURRENT_PHASE_ID ,
381 ITEM_CATALOG_GROUP_ID ,
382 BOM_ENABLED_FLAG ,
383 ENG_ITEM_FLAG ,
384 WEIGHT_UOM_CODE ,
385 UNIT_WEIGHT ,
386 ITEM_NUMBER ,
387 STYLE_ITEM_FLAG ,
388 STYLE_ITEM_ID ,
389 GDSN_OUTBOUND_ENABLED_FLAG ,
390 TRADE_ITEM_DESCRIPTOR,
391 TRANSACTION_ID,
392 SOURCE_SYSTEM_REFERENCE,
393 SOURCE_SYSTEM_ID,
394 COPY_ORGANIZATION_ID
395 )
396 VALUES
397 ( l_set_process_id ,
398 l_process_flag ,
399 l_transaction_type ,
400 l_template_id ,
401 l_copy_inventory_item_id ,
402 l_copy_revision_id ,
403 l_inventory_item_id ,
404 l_organization_id ,
405 l_description ,
406 l_long_description ,
407 l_primary_uom_code ,
408 l_primary_unit_of_measure ,
409 l_item_type ,
410 l_inventory_item_status_code ,
411 l_allowed_units_lookup_code ,
412 l_tracking_quantity_ind ,
413 l_ont_pricing_qty_source ,
414 l_secondary_default_ind ,
415 l_dual_uom_deviation_high ,
416 l_dual_uom_deviation_low ,
417 l_secondary_uom_code ,
418 l_lifecycle_id ,
419 l_current_phase_id ,
420 l_item_catalog_group_id ,
421 l_bom_enabled_flag ,
422 l_eng_item_flag ,
423 l_weight_uom_code ,
424 l_unit_weight ,
425 l_item_number ,
426 l_style_item_flag ,
427 l_style_item_id ,
428 l_gdsn_outbound_enabled_flag ,
429 l_trade_item_descriptor,
430 l_transaction_id,
431 l_source_system_reference,
432 EGO_IMPORT_PVT.G_PDH_SOURCE_SYSTEM_ID,
433 l_copy_from_organization_id
434 );
435 -- 9913552 Serial tagging enh
436 -- this is for supporting copying serial tagging assignments in copy item org assignment case
437 -- for single item flow through PIM UI.
438
439 IF l_copy_inventory_item_id IS NOT NULL AND l_inventory_item_id IS NOT NULL and l_organization_id is not null THEN
440
441 IF INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_inventory_item_id => l_copy_inventory_item_id,
442 p_organization_id => l_organization_id)=2 THEN
443
444 INV_SERIAL_NUMBER_PUB.copy_serial_tag_assignments(
445 p_from_item_id =>l_copy_inventory_item_id,
446 p_from_org_id =>l_organization_id,
447 p_to_item_id => l_inventory_item_id,
448 p_to_org_id =>l_organization_id,
449 x_return_status => x_ret_sts);
450
451 END IF ;
452 END IF ;
453 -- END 9913552
454 END LOOP;
455 END Process_Items;
456
457 /*
458 * This API validates that the variant attribute combination for the SKU
459 * is unique. It also inserts the record if combination does not exists
460 * This API sets x_sku_exists as TRUE if combination already exists
461 * This API sets x_sku_exists as FALSE if combination is not found
462 * This API sets x_var_attrs_missing as TRUE if some variant attribute
463 * values are missing.
464 *
465 * This API returns 0 if no unexpected errors are there, else
466 * returns the SQLCODE
467 *
468 * This API assumes that INVENTORY_ITEM_ID will be present in the intf table
469 */
470 FUNCTION Validate_SKU_Variant_Usage( p_intf_row_id IN ROWID
471 , x_sku_exists OUT NOCOPY BOOLEAN
472 , x_var_attrs_missing OUT NOCOPY BOOLEAN
473 , x_err_text OUT NOCOPY VARCHAR2
474 )
475 RETURN INTEGER IS
476 CURSOR c_attr_values(c_batch_id NUMBER, c_item_id NUMBER, c_org_id NUMBER, c_item_number VARCHAR2,c_category_id NUMBER)
477 IS
478 SELECT
479 AG_EXT.ATTR_GROUP_ID,
480 FL_COL.END_USER_COLUMN_NAME,
481 ATTR_EXT.ATTR_ID,
482 (CASE ATTR_EXT.DATA_TYPE
483 WHEN 'C' THEN INTF.ATTR_VALUE_STR
484 WHEN 'A' THEN INTF.ATTR_VALUE_STR
485 WHEN 'N' THEN To_Char(INTF.ATTR_VALUE_NUM)
486 WHEN 'X' THEN To_Char(INTF.ATTR_VALUE_DATE)
487 WHEN 'Y' THEN To_Char(INTF.ATTR_VALUE_DATE)
488 END) ATTR_VALUE
489 FROM
490 EGO_FND_DSC_FLX_CTX_EXT AG_EXT,
491 EGO_FND_DF_COL_USGS_EXT ATTR_EXT,
492 FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
493 EGO_ITM_USR_ATTR_INTRFC INTF
494 WHERE AG_EXT.APPLICATION_ID = ATTR_EXT.APPLICATION_ID
495 AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = ATTR_EXT.DESCRIPTIVE_FLEXFIELD_NAME /* AG_TYPE*/
496 AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = ATTR_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE /* AG_NAME*/
497 AND ATTR_EXT.APPLICATION_ID = FL_COL.APPLICATION_ID
498 AND ATTR_EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME /* AG_TYPE*/
499 AND ATTR_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE /* AG_NAME*/
500 AND ATTR_EXT.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME /* DATABASE_COLUMN */
501 AND AG_EXT.VARIANT = 'Y'
502 AND INTF.ATTR_GROUP_INT_NAME(+) = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
503 AND INTF.ATTR_GROUP_TYPE(+) = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
504 AND INTF.ATTR_INT_NAME (+) = FL_COL.END_USER_COLUMN_NAME
505 AND INTF.DATA_SET_ID (+) = c_batch_id
506 AND INTF.PROCESS_STATUS (+) = 2
507 AND INTF.INVENTORY_ITEM_ID (+) = c_item_id /* OR INTF.ITEM_NUMBER (+)= c_item_number*/
508 AND INTF.ORGANIZATION_ID (+) = c_org_id
509 AND AG_EXT.ATTR_GROUP_ID IN (SELECT A.ATTR_GROUP_ID
510 FROM EGO_OBJ_AG_ASSOCS_B a
511 WHERE A.CLASSIFICATION_CODE IN (SELECT To_Char(micg.ITEM_CATALOG_GROUP_ID)
512 FROM MTL_ITEM_CATALOG_GROUPS_B micg
513 CONNECT BY PRIOR micg.PARENT_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID
514 START WITH micg.ITEM_CATALOG_GROUP_ID = c_category_id
515 )
516 )
517 ORDER BY ATTR_EXT.ATTR_ID;
518
519 l_sku_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
520 l_style_item_id MTL_SYSTEM_ITEMS_INTERFACE.STYLE_ITEM_ID%TYPE;
521 l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
522 l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
523 l_category_id MTL_SYSTEM_ITEMS_INTERFACE.ITEM_CATALOG_GROUP_ID%TYPE;
524 l_batch_id MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE;
525 l_concat_value VARCHAR2(32000);
526 l_user_id NUMBER := FND_GLOBAL.USER_ID;
527 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
528 l_delimeter EGO_DEFAULT_OPTIONS.OPTION_VALUE%TYPE;
529 l_attr_missing BOOLEAN;
530 l_attr_value EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_STR%TYPE;
531 l_old_attr_id NUMBER;
532 BEGIN
533 Debug_Conc_Log('Starting EGO_STYLE_SKU_ITEM_PVT.Validate_SKU_Variant_Usage');
534
535 l_delimeter := NVL(EGO_COMMON_PVT.GET_OPTION_VALUE('EGO_SKU_CONCAT_VA_DELIM'), '.');
536 SELECT SET_PROCESS_ID, INVENTORY_ITEM_ID, ITEM_NUMBER, ORGANIZATION_ID, ITEM_CATALOG_GROUP_ID, STYLE_ITEM_ID
537 INTO l_batch_id, l_sku_item_id, l_item_number, l_org_id, l_category_id, l_style_item_id
538 FROM MTL_SYSTEM_ITEMS_INTERFACE
539 WHERE ROWID = p_intf_row_id;
540
541 Debug_Conc_Log('Batch_id, item_id, item_number, org_id, icc_id, style_item_id='||
542 l_batch_id||', '||l_sku_item_id||', '||l_item_number||', '||l_org_id||', '||
543 l_category_id||', '||l_style_item_id);
544 l_attr_missing := FALSE;
545 FOR i IN c_attr_values(l_batch_id, l_sku_item_id, l_org_id, l_item_number, l_category_id) LOOP
546 IF NVL(l_old_attr_id, -1) = i.ATTR_ID THEN
547 l_attr_missing := TRUE;
548 EXIT;
549 ELSE
550 l_old_attr_id := i.ATTR_ID;
551 END IF;
552
553 IF i.ATTR_VALUE IS NULL THEN
554 l_attr_missing := TRUE;
555 EXIT;
556 ELSE
557 l_attr_value := i.ATTR_VALUE;
558 IF INSTR(l_attr_value, l_delimeter) > 0 THEN
559 l_attr_value := REPLACE(l_attr_value, l_delimeter, '\'||l_delimeter);
560 END IF;
561 l_concat_value := l_concat_value || l_delimeter || l_attr_value;
562 END IF; --IF i.ATTR_VALUE IS NULL THEN
563 END LOOP;
564 l_concat_value := SUBSTR(l_concat_value, 2);
565
566 Debug_Conc_Log('l_concat_value='||l_concat_value);
567
568 IF l_attr_missing THEN
569 Debug_Conc_Log('Some Variant attributes are missing');
570 Debug_Conc_Log('Done EGO_STYLE_SKU_ITEM_PVT.Validate_SKU_Variant_Usage with Error');
571 x_sku_exists := false;
572 x_var_attrs_missing := true;
573 x_err_text := 'Some Variant attributes are missing';
574 RETURN 0;
575 END IF;
576
577 BEGIN
578 INSERT INTO EGO_SKU_VARIANT_ATTR_USAGES
579 (
580 ORGANIZATION_ID,
581 STYLE_ITEM_ID,
582 CONCATENATED_VA_SEGMENTS,
583 SKU_ITEM_ID,
584 LAST_UPDATE_LOGIN,
585 CREATION_DATE,
586 CREATED_BY,
587 LAST_UPDATE_DATE,
588 LAST_UPDATED_BY
589 )
590 VALUES
591 (
592 l_org_id,
593 l_style_item_id,
594 l_concat_value,
595 l_sku_item_id,
596 l_login_id,
597 SYSDATE,
598 l_user_id,
599 SYSDATE,
600 l_user_id
601 );
602
603 Debug_Conc_Log('Inserted Successfully');
604 Debug_Conc_Log('Done EGO_STYLE_SKU_ITEM_PVT.Validate_SKU_Variant_Usage with Success');
605 x_sku_exists := false;
606 x_var_attrs_missing := false;
607 x_err_text := NULL;
608 RETURN 0;
609 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
610 Debug_Conc_Log('SKU Already exists');
611 Debug_Conc_Log('Done EGO_STYLE_SKU_ITEM_PVT.Validate_SKU_Variant_Usage with Error');
612 x_sku_exists := true;
613 x_var_attrs_missing := false;
614 x_err_text := 'SKU Already exists';
615 RETURN 0;
616 END;
617 EXCEPTION
618 WHEN OTHERS THEN
619 x_sku_exists := true;
620 x_var_attrs_missing := true;
621 x_err_text := 'Unexpected Error in Validate_SKU_Variant_Usage - ' || SQLERRM;
622 Debug_Conc_Log(x_err_text);
623 Debug_Conc_Log('Done EGO_STYLE_SKU_ITEM_PVT.Validate_SKU_Variant_Usage with Unexpected error');
624 RETURN SQLCODE;
625 END Validate_SKU_Variant_Usage;
626
627 FUNCTION Default_Style_Variant_Attrs(p_inventory_item_id IN NUMBER,
628 p_item_catalog_group_id IN NUMBER,
629 x_err_text OUT NOCOPY VARCHAR2)
630 RETURN INTEGER
631 IS
632
633 l_user_id NUMBER := FND_GLOBAL.USER_ID;
634 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
635 l_sysdate DATE := sysdate;
636
637 BEGIN
638 INSERT INTO ego_style_variant_attr_vs
639 ( inventory_item_id,
640 value_set_id,
641 attribute_id,
642 last_update_login,
643 creation_date,
644 created_by
645 )
646 SELECT p_inventory_item_id,
647 fl_col.flex_value_set_id,
648 attr_ext.attr_id,
649 l_login_id,
650 l_sysdate,
651 l_user_id
652 FROM EGO_FND_DSC_FLX_CTX_EXT AG_EXT,
653 EGO_FND_DF_COL_USGS_EXT ATTR_EXT,
654 FND_DESCR_FLEX_COLUMN_USAGES FL_COL
655 WHERE AG_EXT.APPLICATION_ID = ATTR_EXT.APPLICATION_ID
656 AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = ATTR_EXT.DESCRIPTIVE_FLEXFIELD_NAME /* AG_TYPE*/
657 AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = ATTR_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE /* AG_NAME*/
658 AND ATTR_EXT.APPLICATION_ID = FL_COL.APPLICATION_ID
659 AND ATTR_EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME /* AG_TYPE*/
660 AND ATTR_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE /* AG_NAME*/
661 AND ATTR_EXT.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME /* DATABASE_COLUMN */
662 AND AG_EXT.VARIANT = 'Y'
663 AND AG_EXT.ATTR_GROUP_ID IN (SELECT A.ATTR_GROUP_ID
664 FROM EGO_OBJ_AG_ASSOCS_B a
665 WHERE A.OBJECT_ID = g_ego_item_object_id
666 AND A.DATA_LEVEL_ID = g_ego_item_data_level_id
667 AND A.CLASSIFICATION_CODE IN (SELECT To_Char(micg.ITEM_CATALOG_GROUP_ID)
668 FROM MTL_ITEM_CATALOG_GROUPS_B micg
669 CONNECT BY PRIOR micg.PARENT_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID
670 START WITH micg.ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id));
671
672 RETURN(0);
673 EXCEPTION
674 WHEN others THEN
675 x_err_text := SUBSTR(SQLERRM, 1, 240);
676 RETURN(SQLCODE);
677 END Default_Style_Variant_Attrs;
678
679
680 /*
681 * This method returns FND_API.G_TRUE or FND_API.G_FALSE
682 * This method computes whether it is ok to have the new parent ICC
683 * wrt style functionality i.e. we should not allow a ICC that has
684 * different variant attributes than that are currently associated
685 * with the ICC, if ICC already has some styles created.
686 */
687 FUNCTION Is_Parent_ICC_Valid_For_Style(p_item_catalog_group_id NUMBER,
688 p_parent_catalog_group_id NUMBER)
689 RETURN VARCHAR2 IS
690 --l_itm_obj_id NUMBER;
691 --l_item_data_level_id NUMBER;
692 l_index NUMBER;
693 l_style_item_count NUMBER;
694
695 TYPE t_variants IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
696 l_existing_variants t_variants;
697 l_new_variants t_variants;
698
699 CURSOR c_existing_variants IS
700 SELECT assoc.ATTR_GROUP_ID
701 FROM
702 EGO_OBJ_AG_ASSOCS_B assoc,
703 EGO_FND_DSC_FLX_CTX_EXT ag_ext
704 WHERE assoc.ATTR_GROUP_ID = AG_EXT.ATTR_GROUP_ID
705 AND NVL(ag_ext.VARIANT, 'N') = 'Y'
706 AND NVL(assoc.ENABLED_FLAG, 'Y') = 'Y'
707 AND assoc.DATA_LEVEL_ID = g_ego_item_data_level_id
708 AND assoc.OBJECT_ID = g_ego_item_object_id
709 AND assoc.CLASSIFICATION_CODE IN (SELECT TO_CHAR(ITEM_CATALOG_GROUP_ID)
710 FROM MTL_ITEM_CATALOG_GROUPS_B
711 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
712 START WITH ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id
713 );
714
715 CURSOR c_new_variants IS
716 SELECT assoc.ATTR_GROUP_ID
717 FROM
718 EGO_OBJ_AG_ASSOCS_B assoc,
719 EGO_FND_DSC_FLX_CTX_EXT ag_ext
720 WHERE assoc.ATTR_GROUP_ID = AG_EXT.ATTR_GROUP_ID
721 AND NVL(ag_ext.VARIANT, 'N') = 'Y'
722 AND NVL(assoc.ENABLED_FLAG, 'Y') = 'Y'
723 AND assoc.DATA_LEVEL_ID = g_ego_item_data_level_id
724 AND assoc.OBJECT_ID = g_ego_item_object_id
725 AND assoc.CLASSIFICATION_CODE IN (SELECT TO_CHAR(ITEM_CATALOG_GROUP_ID)
726 FROM MTL_ITEM_CATALOG_GROUPS_B
727 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
728 START WITH ITEM_CATALOG_GROUP_ID = p_parent_catalog_group_id
729 UNION ALL
730 SELECT TO_CHAR(p_item_catalog_group_id) FROM DUAL
731 );
732
733 CURSOR IS_ICC_EXIST_FOR_STYLE(l_catalog_group_id NUMBER, l_flag VARCHAR2) IS
734 SELECT 1
735 FROM MTL_SYSTEM_ITEMS_B
736 WHERE ROWNUM=1
737 AND STYLE_ITEM_FLAG = l_flag
738 AND ITEM_CATALOG_GROUP_ID = l_catalog_group_id;
739
740 BEGIN
741
742 /*SELECT OBJECT_ID INTO l_itm_obj_id
743 FROM FND_OBJECTS
744 WHERE OBJ_NAME = 'EGO_ITEM';
745
746 SELECT DATA_LEVEL_ID INTO l_item_data_level_id
747 FROM EGO_DATA_LEVEL_B
748 WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
749 AND APPLICATION_ID = 431
750 AND DATA_LEVEL_NAME = 'ITEM_LEVEL'; */
751
752 FOR i IN c_existing_variants LOOP
753 l_existing_variants(i.ATTR_GROUP_ID) := 'Y';
754 END LOOP;
755
756 IF l_existing_variants.COUNT > 0 THEN
757 /*SELECT COUNT(1)
758 INTO l_style_item_count
759 FROM MTL_SYSTEM_ITEMS_B
760 WHERE STYLE_ITEM_FLAG = 'Y'
761 AND ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id; */
762
763 OPEN Is_Icc_Exist_For_Style(p_item_catalog_group_id, 'Y');
764 FETCH Is_Icc_Exist_For_Style INTO l_style_item_count;
765 IF Is_Icc_Exist_For_Style%FOUND THEN
766 l_style_item_count := 1;
767 ELSE
768 l_style_item_count := 0;
769 END IF;
770 CLOSE Is_Icc_Exist_For_Style;
771 ELSE
772 l_style_item_count := 0;
773 END IF;
774
775 IF l_style_item_count = 0 THEN
776 RETURN FND_API.G_TRUE;
777 END IF;
778
779 FOR i IN c_new_variants LOOP
780 l_new_variants(i.ATTR_GROUP_ID) := 'Y';
781 END LOOP;
782
783 IF l_new_variants.COUNT = 0 THEN
784 RETURN FND_API.G_TRUE;
785 END IF;
786
787 IF l_existing_variants.COUNT <> l_new_variants.COUNT THEN
788 RETURN FND_API.G_FALSE;
789 END IF;
790
791 IF l_existing_variants.FIRST IS NOT NULL THEN
792 l_index := l_existing_variants.FIRST;
793 WHILE l_index IS NOT NULL LOOP
794 IF l_new_variants.EXISTS(l_index) THEN
795 l_new_variants.DELETE(l_index);
796 l_existing_variants.DELETE(l_index);
797 END IF;
798 l_index := l_existing_variants.NEXT(l_index);
799 END LOOP;
800 END IF;
801
802 IF l_existing_variants.COUNT <> l_new_variants.COUNT THEN
803 RETURN FND_API.G_FALSE;
804 ELSE
805 RETURN FND_API.G_TRUE;
806 END IF;
807
808 EXCEPTION
809 WHEN OTHERS THEN
810 CLOSE IS_ICC_EXIST_FOR_STYLE;
811 RETURN FND_API.G_FALSE;
812
813 END Is_Parent_ICC_Valid_For_Style;
814
815
816 PROCEDURE Insert_Fake_Row_For_Item( p_commit IN VARCHAR2 DEFAULT G_FALSE
817 ,p_batch_id IN NUMBER
818 ,p_inventory_item_id IN NUMBER
819 ,p_organization_id IN NUMBER
820 ,p_item_number IN VARCHAR2
821 ,p_style_item_flag IN VARCHAR2
822 ,p_style_item_id IN NUMBER
823 ,p_item_catalog_group_id IN NUMBER
824 ,x_return_status OUT NOCOPY VARCHAR2
825 ,x_msg_data OUT NOCOPY VARCHAR2)
826 IS
827 BEGIN
828 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
829 ( SET_PROCESS_ID,
830 PROCESS_FLAG,
831 TRANSACTION_TYPE,
832 ITEM_CATALOG_GROUP_ID,
833 INVENTORY_ITEM_ID,
834 ORGANIZATION_ID,
835 ITEM_NUMBER,
836 STYLE_ITEM_FLAG,
837 STYLE_ITEM_ID,
838 TRANSACTION_ID,
839 SOURCE_SYSTEM_ID,
840 CONFIRM_STATUS
841 )
842 VALUES
843 ( p_batch_id,
844 1,
845 'SYNC',
846 p_item_catalog_group_id,
847 p_inventory_item_id,
848 p_organization_id,
849 p_item_number,
850 p_style_item_flag,
851 p_style_item_id,
852 MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL,
853 EGO_IMPORT_PVT.G_PDH_SOURCE_SYSTEM_ID,
854 'FK'
855 );
856
857 IF p_commit = G_TRUE THEN
858 COMMIT;
859 END IF;
860 x_return_status := 'S';
861 x_msg_data := NULL;
862 EXCEPTION WHEN OTHERS THEN
863 x_return_status := 'U';
864 x_msg_data := SQLERRM;
865 END Insert_Fake_Row_For_Item;
866
867
868
869 PROCEDURE Propagate_Role_To_SKUs ( p_commit IN VARCHAR2 DEFAULT G_FALSE
870 ,p_batch_id IN NUMBER
871 ,p_style_item_id IN NUMBER
872 ,p_organization_id IN NUMBER
873 ,p_role_name IN VARCHAR2
874 ,p_grantee_type IN VARCHAR2
875 ,p_grantee_party_id IN NUMBER
876 ,p_end_date IN DATE
877 ,x_return_status OUT NOCOPY VARCHAR2
878 ,x_msg_data OUT NOCOPY VARCHAR2)
879 IS
880 l_menu_id NUMBER;
881 l_menu_disp_name FND_MENUS_VL.USER_MENU_NAME%TYPE;
882 l_org_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
883 --l_object_id NUMBER;
884 l_ss_id NUMBER := EGO_IMPORT_PVT.G_PDH_SOURCE_SYSTEM_ID;
885 BEGIN
886 BEGIN
887 SELECT MENU_ID, USER_MENU_NAME INTO l_menu_id, l_menu_disp_name
888 FROM FND_MENUS_VL
889 WHERE MENU_NAME = p_role_name;
890 EXCEPTION WHEN NO_DATA_FOUND THEN
891 l_menu_id := NULL;
892 l_menu_disp_name := '-X-';
893 END;
894
895 BEGIN
896 SELECT ORGANIZATION_CODE INTO l_org_code
897 FROM MTL_PARAMETERS
898 WHERE ORGANIZATION_ID = p_organization_id;
899 EXCEPTION WHEN NO_DATA_FOUND THEN
900 l_org_code := '-X-';
901 END;
902
903 /*SELECT OBJECT_ID INTO l_object_id
904 FROM FND_OBJECTS
905 WHERE OBJ_NAME = 'EGO_ITEM';*/
906
907 INSERT INTO EGO_ITEM_PEOPLE_INTF
908 ( DATA_SET_ID,
909 PROCESS_STATUS,
910 TRANSACTION_TYPE,
911 INVENTORY_ITEM_ID,
912 ORGANIZATION_ID,
913 GRANTEE_PARTY_ID,
914 INTERNAL_ROLE_NAME,
915 GRANTEE_TYPE,
916 START_DATE,
917 END_DATE,
918 ORGANIZATION_CODE,
919 DISPLAY_ROLE_NAME,
920 SOURCE_SYSTEM_ID,
921 CREATED_BY
922 )
923 SELECT
924 p_batch_id,
925 1,
926 'CREATE',
927 msib.INVENTORY_ITEM_ID,
928 p_organization_id,
929 p_grantee_party_id,
930 p_role_name,
931 p_grantee_type,
932 SYSDATE,
933 p_end_date,
934 l_org_code,
935 l_menu_disp_name,
936 l_ss_id,
937 -99
938 FROM MTL_SYSTEM_ITEMS_B msib, MTL_PARAMETERS mp
939 WHERE msib.STYLE_ITEM_ID = p_style_item_id
940 AND msib.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
941 AND mp.ORGANIZATION_ID = p_organization_id
942 AND NOT EXISTS (SELECT NULL FROM FND_GRANTS fg
943 WHERE fg.INSTANCE_TYPE = 'INSTANCE'
944 AND fg.INSTANCE_PK1_VALUE = TO_CHAR(msib.INVENTORY_ITEM_ID)
945 AND fg.INSTANCE_PK2_VALUE = TO_CHAR(p_organization_id)
946 AND fg.OBJECT_ID = g_ego_item_object_id
947 AND NVL(fg.END_DATE, SYSDATE) >= SYSDATE
948 AND fg.MENU_ID = l_menu_id
949 AND fg.GRANTEE_TYPE = p_grantee_type
950 AND fg.GRANTEE_KEY = 'HZ_PARTY:'||p_grantee_party_id
951 );
952 --6504765 : Style to sku item people is not defaulted for item author role
953 --Added the grantee type and grantee key criteria's.
954 IF p_commit = G_TRUE THEN
955 COMMIT;
956 END IF;
957 x_return_status := 'S';
958 x_msg_data := NULL;
959 EXCEPTION WHEN OTHERS THEN
960 x_return_status := 'U';
961 x_msg_data := SQLERRM;
962 END Propagate_Role_To_SKUs;
963
964 /*
965 * This method inserts Category assignment records for SKUs in the mtl categories interface table.
966 */
967 PROCEDURE Propagate_Category_To_SKUs ( p_commit IN VARCHAR2 DEFAULT G_FALSE
968 ,p_batch_id IN NUMBER
969 ,p_style_item_id IN NUMBER
970 ,p_organization_id IN NUMBER
971 ,p_category_set_id IN NUMBER
972 ,p_category_id IN NUMBER
973 ,x_return_status OUT NOCOPY VARCHAR2
974 ,x_msg_data OUT NOCOPY VARCHAR2)
975 IS
976 l_ss_id NUMBER := EGO_IMPORT_PVT.G_PDH_SOURCE_SYSTEM_ID;
977 BEGIN
978 INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
979 ( SET_PROCESS_ID,
980 PROCESS_FLAG,
981 TRANSACTION_TYPE,
982 INVENTORY_ITEM_ID,
983 ORGANIZATION_ID,
984 CATEGORY_SET_ID,
985 CATEGORY_ID,
986 SOURCE_SYSTEM_ID,
987 CREATED_BY
988 )
989 SELECT
990 p_batch_id,
991 1,
992 'CREATE',
993 msib.INVENTORY_ITEM_ID,
994 p_organization_id,
995 p_category_set_id,
996 p_category_id,
997 l_ss_id,
998 -99
999 FROM MTL_SYSTEM_ITEMS_B msib, MTL_PARAMETERS mp
1000 WHERE msib.STYLE_ITEM_ID = p_style_item_id
1001 AND msib.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
1002 AND mp.ORGANIZATION_ID = p_organization_id
1003 AND NOT EXISTS (SELECT NULL FROM MTL_ITEM_CATEGORIES mic
1004 WHERE mic.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
1005 AND mic.ORGANIZATION_ID = p_organization_id
1006 AND mic.CATEGORY_SET_ID = p_category_set_id
1007 AND mic.CATEGORY_ID = p_category_id
1008 );
1009
1010 IF p_commit = G_TRUE THEN
1011 COMMIT;
1012 END IF;
1013 x_return_status := 'S';
1014 x_msg_data := NULL;
1015 EXCEPTION WHEN OTHERS THEN
1016 x_return_status := 'U';
1017 x_msg_data := SQLERRM;
1018 END Propagate_Category_To_SKUs;
1019
1020 BEGIN
1021 SELECT object_id
1022 INTO g_ego_item_object_id
1023 FROM fnd_objects
1024 WHERE obj_name = 'EGO_ITEM';
1025
1026 SELECT data_level_id
1027 INTO g_ego_item_data_level_id
1028 FROM ego_data_level_b
1029 WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
1030 AND APPLICATION_ID = 431
1031 AND DATA_LEVEL_NAME = 'ITEM_LEVEL';
1032
1033 END EGO_STYLE_SKU_ITEM_PVT;