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