[Home] [Help]
PACKAGE BODY: APPS.BOM_ROLLUP_PUB
Source
1 PACKAGE BODY Bom_Rollup_Pub AS
2 /* $Header: BOMRLUPB.pls 120.7.12020000.2 2012/07/13 01:49:42 mshirkol ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMRLUPB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Rollup_Pub
15 --
16 -- NOTES
17 -- Rollup will happen on the objects within a BOM. Every object would have a
18 -- Attribute Map, with every attribute identifying the compute function. If the
19 -- Compute function for the attribute is not specified, the value of the attribute
20 -- is taken as-is.
21 -- Attribute Map is created from the Object attributes' metadata and is not required
22 -- for the calling application to be aware of or even to modify it directly within this
23 -- sub-process.
24 -- Every object also has a list of rollup actions that it supports.
25 -- Every supported action has a rollup function. When a object in bom is being rolled up
26 -- the calling application can indicate which rollup actions should be performed
27 -- on the Object.
28 -- The rollup actions are always performed in a reverse topological order. Attribute
29 -- computation or propogation would start at the leaf nodes and end with parent.
30 --
31 -- HISTORY
32 --
33 -- 09-May-04 Rahul Chitko Initial Creation
34 ***************************************************************************/
35
36
37 /* Package Globals */
38 pG_Attribute_Map Bom_Rollup_Pub.Attribute_Map;
39 pG_Rollup_Action_Map Bom_Rollup_Pub.Rollup_Action_Map;
40
41 pG_Item_Org_Tbl Bom_Rollup_Pub.Item_Org_Tbl;
42
43 /****************** Local procedures Section ******************/
44
45
46
47 FUNCTION Get_Current_Item_Id RETURN NUMBER
48 IS
49 BEGIN
50 IF pG_Item_Org_Tbl.Exists(2)
51 THEN
52 RETURN pG_Item_Org_Tbl(2).Inventory_Item_Id;
53 ELSE
54 RETURN null;
55 END IF;
56
57 END Get_Current_Item_Id;
58
59 FUNCTION Get_Current_Organization_Id RETURN NUMBER
60 IS
61 BEGIN
62 IF pG_Item_Org_Tbl.Exists(2)
63 THEN
64 RETURN pG_Item_Org_Tbl(2).Organization_Id;
65 ELSE
66 RETURN null;
67 END IF;
68
69 END Get_Current_Organization_Id;
70
71
72 FUNCTION Get_Top_Item_Id RETURN NUMBER
73 IS
74 BEGIN
75 IF pG_Item_Org_Tbl.Exists(1)
76 THEN
77 RETURN pG_Item_Org_Tbl(1).Inventory_Item_Id;
78 ELSE
79 RETURN null;
80 END IF;
81
82 END Get_Top_Item_Id;
83
84 FUNCTION Get_Top_Organization_Id RETURN NUMBER
85 IS
86 BEGIN
87 IF pG_Item_Org_Tbl.Exists(1)
88 THEN
89 RETURN pG_Item_Org_Tbl(1).Organization_Id;
90 ELSE
91 RETURN null;
92 END IF;
93
94 END Get_Top_Organization_Id;
95
96 PROCEDURE Set_Top_Organization_Id
97 (p_Organization_Id IN NUMBER)
98 IS
99 BEGIN
100 pG_Item_Org_Tbl(1).Organization_Id := p_Organization_Id;
101
102 END Set_Top_Organization_Id;
103
104 PROCEDURE Set_Top_Item_Id
105 (p_Inventory_Item_Id IN NUMBER)
106 IS
107 BEGIN
108 pG_Item_Org_Tbl(1).Inventory_Item_Id := p_Inventory_Item_Id;
109 END Set_Top_Item_Id;
110
111 PROCEDURE Set_Current_Organization_Id
112 (p_Organization_Id IN NUMBER)
113 IS
114 BEGIN
115 pG_Item_Org_Tbl(2).Organization_Id := p_Organization_Id;
116 END Set_Current_Organization_Id;
117
118 PROCEDURE Set_Current_Item_Id
119 (p_Inventory_Item_Id IN NUMBER)
120 IS
121 BEGIN
122 pG_Item_Org_Tbl(2).Inventory_Item_Id := p_Inventory_Item_Id;
123 END Set_Current_Item_Id;
124
125 /* Procedure to CLOSE the Error handling and Debug Logging
126 */
127 Procedure Close_ErrorDebug_Handler
128 is
129 BEGIN
130 --Removed for 11.5.10-E
131 --DELETE FROM BOM_EXPLOSIONS_ALL;
132 DELETE FROM BOM_SMALL_IMPL_TEMP;
133
134 Error_Handler.Close_Debug_Session;
135 END;
136
137 /* Procedure to initialize the Error handling and Debug Logging
138 */
139 Procedure Initialize_ErrorDebug_Handler
140 IS
141 CURSOR c_get_utl_file_dir IS
142 SELECT
143 VALUE
144 FROM
145 V$PARAMETER
146 WHERE
147 NAME = 'utl_file_dir';
148 l_log_return_status varchar2(1);
149 l_errbuff varchar2(3000);
150 l_err_text varchar2(3000);
151 BEGIN
152
153 G_DEBUG_FLAG := fnd_profile.value('MRP_DEBUG');
154
155
156 -- Error_Handler.Get_Message_Count
157 -- Initialize_ErrorDebug_Handler;
158
159 Error_Handler.initialize();
160 Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
161 IF (G_DEBUG_FLAG = 'Y') THEN
162 OPEN c_get_utl_file_dir;
163 FETCH c_get_utl_file_dir INTO G_LOG_FILE_DIR;
164 IF c_get_utl_file_dir%FOUND THEN
165 ------------------------------------------------------
166 -- Trim to get only the first directory in the list --
167 ------------------------------------------------------
168 IF INSTR(G_LOG_FILE_DIR,',') <> 0 THEN
169 G_LOG_FILE_DIR := SUBSTR(G_LOG_FILE_DIR, 1, INSTR(G_LOG_FILE_DIR, ',') - 1);
170 END IF;
171
172 G_LOG_FILE := G_BO_IDENTIFIER||'_'||TO_CHAR(SYSDATE, 'DDMONYYYY_HH24MISS')||'.err';
173 Error_Handler.Set_Debug(G_DEBUG_FLAG);
174 Error_Handler.Open_Debug_Session(
175 p_debug_filename => G_LOG_FILE
176 ,p_output_dir => G_LOG_FILE_DIR
177 ,x_return_status => l_log_return_status
178 ,x_error_mesg => l_errbuff
179 );
180
181 IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
182 WRITE_ERROR_LOG (
183 p_bo_identifier => G_BO_IDENTIFIER
184 , p_message => 'Unable to Open File');
185 ELSE
186 WRITE_ERROR_LOG (
187 p_bo_identifier => G_BO_IDENTIFIER
188 , p_message => 'Debug Log Location' || G_LOG_FILE_DIR || G_LOG_FILE);
189 END IF;
190
191 END IF;--IF c_get_utl_file_dir%FOUND THEN
192
193 END IF;
194 EXCEPTION
195 WHEN OTHERS THEN
196 l_err_text := SQLERRM;
197 l_err_text := 'Error : '||TO_CHAR(SQLCODE)||'---'||l_err_text;
198 WRITE_ERROR_LOG (
199 p_bo_identifier => G_BO_IDENTIFIER
200 , p_message => l_err_text);
201 END Initialize_ErrorDebug_Handler;
202
203
204 /* Helper for creating the Attribute Map */
205
206 Procedure Add_Attribute_Map_Entry
207 ( p_Attribute_Name IN VARCHAR2
208 , p_Attribute_Value IN VARCHAR2
209 , p_Attribute_Type IN VARCHAR2
210 , p_Compute_Function IN VARCHAR2
211 , p_Object_Name IN VARCHAR2
212 , p_Attribute_Table_Name IN VARCHAR2 := NULL
213 )
214 IS
215 l_attr_Index NUMBER := G_Attribute_Map.COUNT+1;
216 BEGIN
217 G_Attribute_Map(l_attr_index).Attribute_Name := p_Attribute_Name;
218 G_Attribute_Map(l_attr_index).Attribute_Value := p_Attribute_Value;
219 G_Attribute_Map(l_attr_index).Attribute_Type := p_Attribute_Type;
220 G_Attribute_Map(l_attr_index).Compute_Function := p_Compute_Function;
221 G_Attribute_Map(l_attr_index).Object_Name := p_Object_Name;
222 END Add_Attribute_Map_Entry;
223
224
225 /* Load the Local Attribute Map */
226 Procedure Load_Attribute_Map
227 IS
228 l_Attribute_Name VARCHAR2(30);
229 l_Attribute_Value VARCHAR2(2000);
230 l_Attribute_Type VARCHAR2(81);
231 l_Compute_Function VARCHAR2(240);
232 l_Object_Name VARCHAR2(30);
233 BEGIN
234
235 /*
236 -- In release 1 we would not allow passing the attribute value.
237 -- Hence the attribute is not exposed.
238
239 -- In the next phase we would read this as the attribute meta-data
240 -- and form the attribute map
241 -- based on that rather than what it is built here.
242 */
243 --UOM ROLLUP
244 Add_Attribute_Map_Entry
245 ( p_Attribute_Name => 'NET_WEIGHT_UOM'
246 , p_Attribute_Value => NULL
247 , p_Attribute_Type => 'VARCHAR2'
248 , p_Compute_Function => NULL
249 , p_Object_Name => 'EGO_ITEM'
250 , p_Attribute_Table_Name => 'MTL_SYSTEM_ITEMS_B'
251 );
252
253 Add_Attribute_Map_Entry
254 ( p_Attribute_Name => 'GROSS_WEIGHT_UOM'
255 , p_Attribute_Value => NULL
256 , p_Attribute_Type => 'VARCHAR2'
257 , p_Compute_Function => NULL
258 , p_Object_Name => 'EGO_ITEM'
259 , p_Attribute_Table_Name => 'MTL_SYSTEM_ITEMS_B'
260 );
261 --UOM ROLLUP
262 Add_Attribute_Map_Entry
263 ( p_Attribute_Name => 'UNIT_WEIGHT'
264 , p_Attribute_Value => NULL
265 , p_Attribute_Type => 'NUMBER'
266 , p_Compute_Function => 'Bom_Compute_Functions.Compute_Net_Weight'
267 , p_Object_Name => 'EGO_ITEM'
268 , p_Attribute_Table_Name => 'MTL_SYSTEM_ITEMS_B'
269 );
270
271
272 Add_Attribute_Map_Entry
273 ( p_Attribute_Name => 'GROSS_WEIGHT'
274 , p_Attribute_Value => NULL
275 , p_Attribute_Type => 'NUMBER'
276 , p_Compute_Function => 'Bom_Compute_Functions.Compute_Gross_Weight'
277 , p_Object_Name => 'EGO_ITEM'
278 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
279 );
280
281 Add_Attribute_Map_Entry
282 ( p_Attribute_Name => 'COMPONENT_QUANTITY'
283 , p_Attribute_Value => NULL
284 , p_Attribute_Type => 'NUMBER'
285 , p_Compute_Function => NULL
286 , p_Object_Name => 'EGO_ITEM'
287 , p_Attribute_Table_Name => 'BOM_COMPONENTS_B'
288 );
289
290 Add_Attribute_Map_Entry
291 ( p_Attribute_Name => 'IS_TRADE_ITEM_INFO_PRIVATE'
292 , p_Attribute_Value => NULL
293 , p_Attribute_Type => 'VARCHAR2'
294 , p_Compute_Function => NULL
295 , p_Object_Name => 'EGO_ITEM'
296 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
297 );
298
299 Add_Attribute_Map_Entry
300 ( p_Attribute_Name => 'BRAND_OWNER_NAME'
301 , p_Attribute_Value => NULL
302 , p_Attribute_Type => 'VARCHAR2'
303 , p_Compute_Function => NULL
304 , p_Object_Name => 'EGO_ITEM'
305 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
306 );
307 Add_Attribute_Map_Entry
308 ( p_Attribute_Name => 'BRAND_OWNER_GLN'
309 , p_Attribute_Value => NULL
310 , p_Attribute_Type => 'VARCHAR2'
311 , p_Compute_Function => NULL
312 , p_Object_Name => 'EGO_ITEM'
313 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
314 );
315 Add_Attribute_Map_Entry
316 ( p_Attribute_Name => 'FUNCTIONAL_NAME'
317 , p_Attribute_Value => NULL
318 , p_Attribute_Type => 'VARCHAR2'
319 , p_Compute_Function => NULL
320 , p_Object_Name => 'EGO_ITEM'
321 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_TL'
322 );
323 Add_Attribute_Map_Entry
324 ( p_Attribute_Name => 'IS_TRADE_ITEM_A_CONSUMER_UNIT'
325 , p_Attribute_Value => NULL
326 , p_Attribute_Type => 'VARCHAR2'
327 , p_Compute_Function => NULL
328 , p_Object_Name => 'EGO_ITEM'
329 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
330 );
331
332 Add_Attribute_Map_Entry
333 ( p_Attribute_Name => 'CUSTOMER_ORDER_ENABLED_FLAG'
334 , p_Attribute_Value => NULL
335 , p_Attribute_Type => 'VARCHAR2'
336 , p_Compute_Function => NULL
337 , p_Object_Name => 'EGO_ITEM'
338 , p_Attribute_Table_Name => 'MTL_SYSTEM_ITEMS_B'
339 );
340
341 Add_Attribute_Map_Entry
342 ( p_Attribute_Name => 'PARENT_IS_ITEM_CONS_UNIT'
343 , p_Attribute_Value => NULL
344 , p_Attribute_Type => 'VARCHAR2'
345 , p_Compute_Function => NULL
346 , p_Object_Name => 'EGO_ITEM'
347 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
348 );
349
350 Add_Attribute_Map_Entry
351 ( p_Attribute_Name => 'PARENT_CUST_ORD_ENABLED_FLAG'
352 , p_Attribute_Value => NULL
353 , p_Attribute_Type => 'VARCHAR2'
354 , p_Compute_Function => NULL
355 , p_Object_Name => 'EGO_ITEM'
356 , p_Attribute_Table_Name => 'MTL_SYSTEM_ITEMS_B'
357 );
358
359 Add_Attribute_Map_Entry
360 ( p_Attribute_Name => 'MANUFACTURER_ID'
361 , p_Attribute_Value => NULL
362 , p_Attribute_Type => 'VARCHAR2'
363 , p_Compute_Function => NULL
364 , p_Object_Name => 'EGO_ITEM'
365 , p_Attribute_Table_Name => 'EGO_ITM_GTN_MUL_ATTRS_B'
366 );
367
368 Add_Attribute_Map_Entry
369 ( p_Attribute_Name => 'MANUFACTURER_GLN'
370 , p_Attribute_Value => NULL
371 , p_Attribute_Type => 'VARCHAR2'
372 , p_Compute_Function => NULL
373 , p_Object_Name => 'EGO_ITEM'
374 , p_Attribute_Table_Name => 'EGO_ITM_GTN_MUL_ATTRS_B'
375 );
376
377 Add_Attribute_Map_Entry
378 ( p_Attribute_Name => 'TOP_GTIN'
379 , p_Attribute_Value => NULL
380 , p_Attribute_Type => 'VARCHAR2'
381 , p_Compute_Function => NULL
382 , p_Object_Name => 'EGO_ITEM'
383 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
384 );
385
386 Add_Attribute_Map_Entry
387 ( p_Attribute_Name => 'SUB_BRAND'
388 , p_Attribute_Value => NULL
389 , p_Attribute_Type => 'VARCHAR2'
390 , p_Compute_Function => NULL
391 , p_Object_Name => 'EGO_ITEM'
392 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
393 );
394
395 Add_Attribute_Map_Entry
396 ( p_Attribute_Name => 'STORAGE_HANDLING_TEMP_MIN'
397 , p_Attribute_Value => NULL
398 , p_Attribute_Type => 'NUMBER'
399 , p_Compute_Function => NULL
400 , p_Object_Name => 'EGO_ITEM'
401 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
402 );
403
404 Add_Attribute_Map_Entry
405 ( p_Attribute_Name => 'UOM_STORAGE_HANDLING_TEMP_MIN'
406 , p_Attribute_Value => NULL
407 , p_Attribute_Type => 'VARCHAR2'
408 , p_Compute_Function => NULL
409 , p_Object_Name => 'EGO_ITEM'
410 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
411 );
412
413 Add_Attribute_Map_Entry
414 ( p_Attribute_Name => 'STORAGE_HANDLING_TEMP_MAX'
415 , p_Attribute_Value => NULL
416 , p_Attribute_Type => 'NUMBER'
417 , p_Compute_Function => NULL
418 , p_Object_Name => 'EGO_ITEM'
419 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
420 );
421
422 Add_Attribute_Map_Entry
423 ( p_Attribute_Name => 'UOM_STORAGE_HANDLING_TEMP_MAX'
424 , p_Attribute_Value => NULL
425 , p_Attribute_Type => 'VARCHAR2'
426 , p_Compute_Function => NULL
427 , p_Object_Name => 'EGO_ITEM'
428 , p_Attribute_Table_Name => 'EGO_ITEM_GTN_ATTRS_B'
429 );
430
431 END Load_Attribute_Map;
432
433 Procedure Add_Action_Entry
434 ( p_Rollup_Action IN VARCHAR2
435 , p_Rollup_Function IN VARCHAR2
436 , p_Object_Name IN VARCHAR2
437 , p_DML_Function IN VARCHAR2
438 , p_DML_Delayed_Write IN VARCHAR2
439 )
440 IS
441 l_action_map_index NUMBER := G_Rollup_Action_Map.COUNT + 1;
442 BEGIN
443 G_Rollup_Action_Map(l_action_map_index).Rollup_Action := p_Rollup_Action;
444 G_Rollup_Action_Map(l_action_map_index).Rollup_Function := p_Rollup_Function;
445 G_Rollup_Action_Map(l_action_map_index).Object_Name := p_Object_Name;
446 G_Rollup_Action_Map(l_action_map_index).DML_Function := p_DML_Function;
447 G_Rollup_Action_Map(l_action_map_index).DML_Delayed_Write := p_DML_Delayed_Write;
448 END Add_Action_Entry;
449
450 Procedure Load_Rollup_Action_Map
451 IS
452 BEGIN
453 Add_Action_Entry
454 ( p_Rollup_Action => G_COMPUTE_NET_WEIGHT
455 , p_Rollup_Function => 'Bom_Compute_Functions.Rollup_Net_Weight'
456 , p_Object_Name => 'EGO_ITEM'
457 , p_DML_Function => 'Bom_Compute_Functions.Set_Net_Weight'
458 , p_DML_Delayed_Write => 'N'
459 );
460 /*
461 Add_Action_Entry
462 ( p_Rollup_Action => G_COMPUTE_GROSS_WEIGHT
463 , p_Rollup_Function => 'Bom_Compute_Functions.Rollup_Gross_Weight'
464 , p_Object_Name => 'EGO_ITEM'
465 , p_DML_Function => 'Bom_Compute_Functions.Set_User_Attributes'
466 , p_DML_Delayed_Write => 'N'
467 );
468
469 Add_Action_Entry
470 ( p_Rollup_Action => G_PROPOGATE_PRIVATE_FLAG
471 , p_Rollup_Function => 'Bom_Compute_Functions.Propogate_Private_Flag'
472 , p_Object_Name => 'EGO_ITEM'
473 , p_DML_Function => 'Bom_Compute_Functions.Set_Private_Flag'
474 , p_DML_Delayed_Write => 'N'
475 );
476 */
477 Add_Action_Entry
478 ( p_Rollup_Action => G_PROPOGATE_BRAND_INFO
479 , p_Rollup_Function => 'Bom_Compute_Functions.Propogate_Brand_Info'
480 , p_Object_Name => 'EGO_ITEM'
481 , p_DML_Function => 'Bom_Compute_Functions.Set_Brand_Info'
482 , p_DML_Delayed_Write => 'N'
483 );
484
485 Add_Action_Entry
486 ( p_Rollup_Action => G_COMPUTE_TOP_GTIN_FLAG
487 , p_Rollup_Function => 'Bom_Compute_Functions.Propogate_TOP_GTIN_Flag'
488 , p_Object_Name => 'EGO_ITEM'
489 , p_DML_Function => 'Bom_Compute_Functions.Set_Top_GTIN_Flag'
490 , p_DML_Delayed_Write => 'N'
491 );
492
493 Add_Action_Entry
494 ( p_Rollup_Action => G_COMPUTE_MULTI_ROW_ATTRS
495 , p_Rollup_Function => 'Bom_Compute_Functions.Compute_Multi_Row_Attrs'
496 , p_Object_Name => 'EGO_ITEM'
497 , p_DML_Function => 'Bom_Compute_Functions.Set_Multirow_Attributes'
498 , p_DML_Delayed_Write => 'N'
499 );
500
501 Add_Action_Entry
502 ( p_Rollup_Action => G_PROPAGATE_SH_TEMPS
503 , p_Rollup_Function => 'Bom_Compute_Functions.Propagate_SH_Temps'
504 , p_Object_Name => 'EGO_ITEM'
505 , p_DML_Function => 'Bom_Compute_Functions.Set_SH_Temps'
506 , p_DML_Delayed_Write => 'N'
507 );
508
509 END Load_Rollup_Action_Map;
510
511
512 PROCEDURE Load_Top_Items
513 ( p_Item_Id IN NUMBER
514 , p_organization_id IN NUMBER
515 , p_alternate_bom_Code IN VARCHAR2
516 , p_structure_type_id IN NUMBER DEFAULT NULL
517 , x_Sequence IN OUT NOCOPY NUMBER
518 )
519 IS
520 l_err_msg varchar2(2000);
521 l_err_code varchar2(2000);
522 l_used_in_struct varchar2(2000);
523 BEGIN
524 x_Sequence := to_number(to_char(sysdate,'SSSS'));
525 Bom_Imploder_Pub.Imploder_Userexit
526 ( sequence_id => x_Sequence
527 , eng_mfg_flag => 2
528 , org_id => p_Organization_Id
529 , impl_flag => 2
530 , display_option => 2
531 , levels_to_implode => 10
532 , obj_name => 'EGO_ITEM'
533 , pk1_value => p_Item_Id
534 , pk2_value => p_Organization_Id
535 , impl_date => to_char(sysdate,'YYYY/MM/DD HH24:MI:SS')
536 , unit_number_from => NULL
537 , unit_number_to => NULL
538 , err_msg => l_err_msg
539 , err_code => l_err_code
540 , organization_option => 1
541 , struct_name => 'PIM_PBOM_S'
542 , struct_type => 'Packaging Hierarchy'
543 , revision => null
544 , used_in_structure => l_used_in_struct
545 );
546 WRITE_DEBUG_LOG (
547 p_bo_identifier => G_BO_IDENTIFIER
548 , p_message => 'Uploaded Imploder with sequence Id ' || x_Sequence || ' error ' || l_err_msg );
549
550 END Load_Top_Items;
551
552
553 PROCEDURE Handle_Attribute_Updates
554 ( p_Header_Item_Id IN NUMBER
555 , p_Organization_Id IN NUMBER
556 , p_Header_Attrs_Flag IN VARCHAR2
557 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map
558 , x_return_status OUT NOCOPY VARCHAR2
559 , x_msg_count OUT NOCOPY NUMBER
560 , x_msg_data OUT NOCOPY VARCHAR2
561 )
562 IS
563 -- l_indx NUMBER;
564 l_act_indx NUMBER;
565 BEGIN
566 -- For each entry in the action map, call the dml function
567 FOR action_index in 1..p_action_map.COUNT
568 LOOP
569 --
570 -- Perform rollup for all the top level
571 --
572 FOR l_indx IN 1..G_Rollup_Action_Map.COUNT
573 LOOP
574 IF G_Rollup_Action_Map(l_indx).Rollup_Action =
575 p_action_map(action_index).Rollup_Action
576 THEN
577 l_act_indx := l_indx;
578 END IF;
579 END LOOP;
580
581 WRITE_DEBUG_LOG (
582 p_bo_identifier => G_BO_IDENTIFIER
583 , p_message => 'Calling DML Function '||G_Rollup_Action_Map(l_act_indx).DML_Function
584 ||' with header_attrs_flag '||p_header_attrs_flag||' for item '||p_header_item_id||'-'||p_organization_id);
585
586 EXECUTE IMMEDIATE ' ' ||
587 ' BEGIN ' || G_Rollup_Action_Map(l_act_indx).DML_Function ||
588 '( p_header_item_id => :header_item_id '||
589 ', p_organization_id => :organization_id '||
590 ', p_header_attrs_flag => :header_attrs_flag '||
591 ', x_return_status => :return_status '||
592 ', x_msg_count => :msg_count '||
593 ', x_msg_data => :msg_data '||
594 '); END;'
595 USING IN p_header_item_id
596 ,IN p_organization_id
597 ,IN p_header_attrs_flag
598 ,OUT x_return_status
599 ,OUT x_msg_count
600 ,OUT x_msg_data;
601
602 WRITE_DEBUG_LOG (
603 p_bo_identifier => G_BO_IDENTIFIER
604 , p_message => 'Called DML Function '||x_return_status||':'||x_msg_count||' '||x_msg_data);
605
606 END LOOP;
607 EXCEPTION
608 WHEN OTHERS THEN
609 WRITE_DEBUG_LOG(
610 p_bo_identifier => G_BO_IDENTIFIER
611 , p_message => 'EXCEPTION IN HAU: '||sqlerrm
612 );
613
614 END Handle_Attribute_Updates;
615
616
617 /*
618 The item id passed in here is the TOP Item id. This top item will be available
619 to all the subsequent processes Get_Top_Item_Id and Get_Top_Organization_Id
620 Current Item and current Organization is available Get_Current_Item_Id and
621 Get_Current_Organization_Id.
622 */
623 PROCEDURE Handle_Rollup_Actions
624 ( p_rollup_id IN NUMBER
625 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map
626 , p_item_id IN NUMBER
627 , p_organization_id IN NUMBER
628 , p_parent_item_id IN NUMBER := NULL -- passed in if add/delete comp case
629 , p_component_item_id IN NUMBER := NULL -- passed in if add/delete comp case
630 , p_alternate_bom_code IN VARCHAR2
631 , p_validate IN VARCHAR2
632 , p_halt_on_error IN VARCHAR2
633 , x_return_status OUT NOCOPY VARCHAR2
634 , x_msg_count OUT NOCOPY NUMBER
635 , x_msg_data OUT NOCOPY VARCHAR2
636 )
637 IS
638 CURSOR l_LowLevelCode_csr is
639 SELECT
640 nvl(max(plan_level), -1) depth
641 FROM
642 bom_explosions_all
643 WHERE
644 group_id = p_rollup_id;
645
646 l_depth number;
647 l_Component_Attrs Bom_Rollup_Pub.Attribute_Map;
648
649 CURSOR c_assy_csr(
650 p_level NUMBER
651 , p_rollup_id NUMBER
652 )
653 IS
654 SELECT
655 MTL.ROWID row_id,
656 MTL.INVENTORY_ITEM_ID inventory_item_id,
657 nvl(UNIT_WEIGHT,0) unit_weight,
658 LLC.assembly_item_id parent_item_id
659 FROM
660 Mtl_System_Items_b MTL,
661 bom_explosions_all LLC
662 WHERE
663 LLC.group_id = p_rollup_id
664 AND LLC.plan_level = p_level
665 AND MTL.INVENTORY_ITEM_ID = LLC.pk1_value
666 AND LLC.obj_name IS NULL -- EGO_ITEM
667 AND MTL.ORGANIZATION_ID = p_organization_id;
668
669 CURSOR l_comps_csr ( p_org_id NUMBER
670 , p_item_id NUMBER
671 , p_unit_number VARCHAR2
672 , p_eff_date DATE
673 , p_alternate_bom_code VARCHAR2
674 )
675 IS
676 SELECT MTL2.UNIT_WEIGHT unit_weight
677 ,gtn_attrs.gross_weight gross_weight
678 --UOM ROLLUP
679 , MTL2.WEIGHT_UOM_CODE NET_WT_UOM
680 , gtn_attrs.UOM_GROSS_WEIGHT GROSS_WT_UOM
681 --UOM ROLLUP
682 ,component_sequence_id
683 ,com.component_quantity
684 ,com.bill_sequence_id
685 ,com.component_item_id
686 ,gtn_attrs.is_trade_item_info_private
687 ,gtn_attrs.brand_owner_name brand_owner_name
688 ,gtn_attrs.brand_owner_gln brand_owner_gln
689 ,gtn_attrs.sub_brand sub_brand
690 ,gtn_attrs_tl.functional_name functional_name
691 ,0 manufacturer_id
692 ,' ' manufacturer_gln
693 ,mtl2.customer_order_enabled_flag
694 ,gtn_attrs.is_trade_item_a_consumer_unit
695 ,mtl1.customer_order_enabled_flag parent_cust_ord_enabled_flag
696 ,gtn_attrs_parent.is_trade_item_a_consumer_unit parent_is_item_cons_unit
697 ,gtn_attrs.storage_handling_temp_min
698 ,gtn_attrs.uom_storage_handling_temp_min
699 ,gtn_attrs.storage_handling_temp_max
700 ,gtn_attrs.uom_storage_handling_temp_max
701 ,mtl2.trade_item_descriptor tiud
702 ,gtn_attrs.inventory_item_id inv_id
703 ,gtn_attrs_parent.inventory_item_id inv_id1
704 FROM mtl_system_items MTL2,
705 bom_inventory_components COM,
706 mtl_system_items MTL1,
707 bom_bill_of_materials BOM,
708 ego_item_gtn_attrs_b gtn_attrs,
709 ego_item_gtn_attrs_tl gtn_attrs_tl,
710 ego_item_gtn_attrs_b gtn_attrs_parent
711 WHERE
712 NVL(BOM.ALTERNATE_BOM_DESIGNATOR,'XXXXXXXXXXX') =
713 NVL(p_alternate_bom_code,'XXXXXXXXXXX')
714 AND COM.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID
715 AND BOM.ORGANIZATION_ID = p_organization_id
716 AND BOM.ASSEMBLY_ITEM_ID = p_item_id
717 AND MTL1.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
718 AND MTL1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
719 AND MTL2.INVENTORY_ITEM_ID = COM.COMPONENT_ITEM_ID
720 AND MTL2.ORGANIZATION_ID = BOM.ORGANIZATION_ID
721 AND COM.IMPLEMENTATION_DATE IS NOT NULL
722 AND NVL(COM.ECO_FOR_PRODUCTION,2) = 2
723 AND COM.COMPONENT_QUANTITY > 0
724 AND NOT (mtl1.replenish_to_order_flag = 'Y'
725 AND mtl1.bom_item_type = 4
726 AND mtl1.base_item_id IS NOT NULL
727 AND MTL2.BOM_ITEM_TYPE IN (1,2)
728 )
729 AND ( COM.DISABLE_DATE IS NULL
730 OR
731 COM.DISABLE_DATE > p_eff_date
732 )
733 AND ( ( MTL1.EFFECTIVITY_CONTROL <> 1
734 AND p_unit_number is NOT NULL
735 AND COM.DISABLE_DATE IS NULL
736 AND p_unit_number BETWEEN COM.FROM_END_ITEM_UNIT_NUMBER
737 AND NVL(COM.TO_END_ITEM_UNIT_NUMBER, p_unit_number)
738 )
739 OR
740 (
741 MTL1.EFFECTIVITY_CONTROL = 1
742 AND COM.EFFECTIVITY_DATE <= p_eff_date
743 )
744 )
745 AND mtl2.inventory_item_id = gtn_attrs.inventory_item_id (+)
746 AND mtl2.organization_id = gtn_attrs.organization_id (+)
747 AND mtl1.inventory_item_id = gtn_attrs_parent.inventory_item_id (+) -- attributes of the parent
748 AND mtl1.organization_id = gtn_attrs_parent.organization_id (+) -- attributes of the parent
749 AND gtn_attrs_tl.inventory_item_id(+) = gtn_attrs.inventory_item_id
750 AND gtn_attrs_tl.organization_id(+)= gtn_attrs.organization_id
751 AND gtn_attrs_tl.extension_id(+)= gtn_attrs.extension_id
752 AND gtn_attrs_tl.language(+) = USERENV('LANG')
753 FOR UPDATE OF
754 mtl2.unit_weight
755 ,gtn_attrs.gross_weight
756 --UOM ROLLUP
757 , mtl2.WEIGHT_UOM_CODE
758 ,gtn_attrs.UOM_GROSS_WEIGHT
759 --UOM ROLLUP
760 ,gtn_attrs.top_gtin
761 ,gtn_attrs.brand_owner_name
762 ,gtn_attrs.brand_owner_gln
763 ,gtn_attrs_tl.functional_name
764 ,gtn_attrs.storage_handling_temp_min
765 ,gtn_attrs.uom_storage_handling_temp_min
766 ,gtn_attrs.storage_handling_temp_max
767 ,gtn_attrs.uom_storage_handling_temp_max
768 NOWAIT;
769
770 CURSOR row_exist_gtin_attrs IS
771 SELECT inventory_item_id
772 FROM ego_item_gtn_attrs_b
773 WHERE inventory_item_id = p_item_id
774 AND organization_id = p_organization_id;
775
776 CURSOR get_gdsn_enabled IS
777 SELECT GDSN_OUTBOUND_ENABLED_FLAG
778 FROM mtl_system_items_b
779 WHERE inventory_item_id = p_item_id
780 AND organization_id = p_organization_id;
781
782 x_row_id VARCHAR2(1000);
783
784 l_stmt varchar2(5);
785 l_comp_sequence NUMBER;
786 l_indx NUMBER;
787 l_act_indx NUMBER;
788 l_Hdr_Bill_Seq_Id NUMBER;
789 l_first_run BOOLEAN;
790 l_parent_item_tbl Item_Org_Tbl;
791
792 l_inv_item_id NUMBER;
793 l_ext_id NUMBER;
794 l_catalog_id NUMBER;
795 l_login_id NUMBER;
796 l_user_id NUMBER;
797 l_gdsn_enabled VARCHAR2(5);
798
799 BEGIN
800
801 l_stmt := '1';
802
803
804 --
805 -- Set the top Organization and Item Id
806 --
807 Set_Top_Item_Id(p_Inventory_Item_Id => p_item_id);
808 Set_Top_Organization_Id(p_Organization_Id => p_Organization_Id);
809
810 -- Rollup optimization:
811 -- store ids of items in the lineage leading up to added/deleted component
812 -- so that we can suppress dml updates of components outside this lineage
813 IF p_parent_item_id IS NOT NULL THEN
814 l_parent_item_tbl(p_component_item_id).inventory_item_id := p_component_item_id;
815 l_parent_item_tbl(p_component_item_id).organization_id := p_organization_id;
816 l_parent_item_tbl(p_parent_item_id).inventory_item_id := p_parent_item_id;
817 l_parent_item_tbl(p_parent_item_id).organization_id := p_organization_id;
818 END IF;
819
820 For l_LevelCode_rec in l_LowLevelCode_csr loop
821 l_depth := l_LevelCode_rec.depth;
822 End loop;
823
824 For l_level in reverse 0..l_depth
825 LOOP
826 WRITE_DEBUG_LOG (
827 p_bo_identifier => G_BO_IDENTIFIER
828 , p_message => l_depth || ' with depth and level ' || l_level);
829
830 l_stmt := '2';
831 --
832 -- Begin the reverse topology traversal
833 --
834 l_Header_Attrs_Map.DELETE; -- clean and start
835 l_component_seq_tbl.DELETE;
836 l_component_seq_attrs_tbl.DELETE;
837 FOR l_assy_rec in c_assy_csr( p_level => l_level,
838 p_rollup_id => p_rollup_id)
839 LOOP -- Assembly cursor
840 WRITE_DEBUG_LOG (
841 p_bo_identifier => G_BO_IDENTIFIER
842 , p_message => 'Inside Assembly Cursor for '||l_assy_rec.inventory_item_id);
843
844 l_stmt := '3';
845 Set_Current_Item_Id(p_Inventory_Item_Id => l_assy_rec.inventory_item_id);
846 Set_Current_Organization_Id(p_Organization_Id => p_Organization_Id);
847
848 l_Header_Attrs_Map.DELETE; -- clean and start
849 l_component_seq_tbl.DELETE;
850 l_component_seq_attrs_tbl.DELETE;
851 l_component_attrs.DELETE; -- this is the attributes table of a component stored in attrs_map
852
853 l_inv_item_id := NULL;
854
855 l_user_id := FND_GLOBAL.USER_ID;
856 l_login_id := FND_GLOBAL.LOGIN_ID;
857
858 SELECT item_catalog_group_id
859 INTO l_catalog_id
860 FROM mtl_system_items_b
861 WHERE inventory_item_id = p_item_id
862 AND organization_id = p_organization_id;
863
864 IF l_catalog_id IS NULL THEN
865 l_catalog_id := -1;
866 END IF;
867
868 OPEN row_exist_gtin_attrs;
869 FETCH row_exist_gtin_attrs INTO l_inv_item_id;
870 CLOSE row_exist_gtin_attrs;
871
872 OPEN get_gdsn_enabled;
873 FETCH get_gdsn_enabled INTO l_gdsn_enabled;
874 CLOSE get_gdsn_enabled;
875
876
877 IF l_inv_item_id IS NULL THEN
878 IF nvl(l_gdsn_enabled,'N') = 'Y' THEN
879
880 SELECT EGO_EXTFWK_S.NEXTVAL INTO l_ext_id FROM dual;
881
882 EGO_ITEM_GTN_ATTRS_PKG.INSERT_ROW (
883 x_ROWID => x_row_id
884 ,x_EXTENSION_ID => l_ext_id
885 ,x_REQUEST_ID => null
886 ,x_DELIVERY_TO_MRKT_TEMP_MIN => null
887 ,x_UOM_DELIVERY_TO_MRKT_TEMP_MI => null
888 ,x_SUB_BRAND => null
889 ,x_UOM_DEL_TO_DIST_CNTR_TEMP_MI => null
890 ,x_DELIVERY_TO_MRKT_TEMP_MAX => null
891 ,x_UOM_DELIVERY_TO_MRKT_TEMP_MA => null
892 ,x_INVENTORY_ITEM_ID => p_item_id
893 ,x_ORGANIZATION_ID => p_organization_id
894 ,x_ITEM_CATALOG_GROUP_ID => l_catalog_id
895 ,x_REVISION_ID => null
896 ,x_IS_TRADE_ITEM_A_CONSUMER_UNI => null
897 ,x_IS_TRADE_ITEM_INFO_PRIVATE => null
898 ,x_GROSS_WEIGHT => null
899 ,x_UOM_GROSS_WEIGHT => null
900 ,x_EFFECTIVE_DATE => sysdate
901 ,x_CANCELED_DATE => null
902 ,x_DISCONTINUED_DATE => null
903 ,x_END_AVAILABILITY_DATE_TIME => null
904 ,x_START_AVAILABILITY_DATE_TIME => null
905 ,x_BRAND_NAME => null
906 ,x_IS_TRADE_ITEM_A_BASE_UNIT => null
907 ,x_IS_TRADE_ITEM_A_VARIABLE_UNI => null
908 ,x_IS_PACK_MARKED_WITH_EXP_DATE => null
909 ,x_IS_PACK_MARKED_WITH_GREEN_DO => null
910 ,x_IS_PACK_MARKED_WITH_INGRED => null
911 ,x_IS_PACKAGE_MARKED_AS_REC => null
912 ,x_IS_PACKAGE_MARKED_RET => null
913 ,x_STACKING_FACTOR => null
914 ,x_STACKING_WEIGHT_MAXIMUM => null
915 ,x_UOM_STACKING_WEIGHT_MAXIMUM => null
916 ,x_ORDERING_LEAD_TIME => null
917 ,x_UOM_ORDERING_LEAD_TIME => null
918 ,x_ORDER_QUANTITY_MAX => null
919 ,x_ORDER_QUANTITY_MIN => null
920 ,x_ORDER_QUANTITY_MULTIPLE => null
921 ,x_ORDER_SIZING_FACTOR => null
922 ,x_EFFECTIVE_START_DATE => null
923 ,x_CATALOG_PRICE => null
924 ,x_EFFECTIVE_END_DATE => null
925 ,x_SUGGESTED_RETAIL_PRICE => null
926 ,x_MATERIAL_SAFETY_DATA_SHEET_N => null
927 ,x_HAS_BATCH_NUMBER => null
928 ,x_IS_NON_SOLD_TRADE_RET_FLAG => null
929 ,x_IS_TRADE_ITEM_MAR_REC_FLAG => null
930 ,x_DIAMETER => null
931 ,x_UOM_DIAMETER => null
932 ,x_DRAINED_WEIGHT => null
933 ,x_UOM_DRAINED_WEIGHT => null
934 ,x_GENERIC_INGREDIENT => null
935 ,x_GENERIC_INGREDIENT_STRGTH => null
936 ,x_UOM_GENERIC_INGREDIENT_STRGT => null
937 ,x_INGREDIENT_STRENGTH => null
938 ,x_IS_NET_CONTENT_DEC_FLAG => null
939 ,x_NET_CONTENT => null
940 ,x_UOM_NET_CONTENT => null
941 ,x_PEG_HORIZONTAL => null
942 ,x_UOM_PEG_HORIZONTAL => null
943 ,x_PEG_VERTICAL => null
944 ,x_UOM_PEG_VERTICAL => null
945 ,x_CONSUMER_AVAIL_DATE_TIME => null
946 ,x_DEL_TO_DIST_CNTR_TEMP_MAX => null
947 ,x_UOM_DEL_TO_DIST_CNTR_TEMP_MA => null
948 ,x_DEL_TO_DIST_CNTR_TEMP_MIN => null
949 ,x_TRADE_ITEM_DESCRIPTOR => null
950 ,x_EANUCC_CODE => null
951 ,x_EANUCC_TYPE => null
952 ,x_RETAIL_PRICE_ON_TRADE_ITEM => null
953 ,x_QUANTITY_OF_COMP_LAY_ITEM => null
954 ,x_QUANITY_OF_ITEM_IN_LAYER => null
955 ,x_QUANTITY_OF_ITEM_INNER_PACK => null
956 ,x_TARGET_MARKET_DESC => null
957 ,x_QUANTITY_OF_INNER_PACK => null
958 ,x_BRAND_OWNER_GLN => null
959 ,x_BRAND_OWNER_NAME => null
960 ,x_STORAGE_HANDLING_TEMP_MAX => null
961 ,x_UOM_STORAGE_HANDLING_TEMP_MA => null
962 ,x_STORAGE_HANDLING_TEMP_MIN => null
963 ,x_UOM_STORAGE_HANDLING_TEMP_MI => null
964 ,x_TRADE_ITEM_COUPON => null
965 ,x_DEGREE_OF_ORIGINAL_WORT => null
966 ,x_FAT_PERCENT_IN_DRY_MATTER => null
967 ,x_PERCENT_OF_ALCOHOL_BY_VOL => null
968 ,x_ISBN_NUMBER => null
969 ,x_ISSN_NUMBER => null
970 ,x_IS_INGREDIENT_IRRADIATED => null
971 ,x_IS_RAW_MATERIAL_IRRADIATED => null
972 ,x_IS_TRADE_ITEM_GENETICALLY_MO => null
973 ,x_IS_TRADE_ITEM_IRRADIATED => null
974 ,x_PUBLICATION_STATUS => null
975 ,x_TOP_GTIN => null
976 ,x_SECURITY_TAG_LOCATION => null
977 ,x_URL_FOR_WARRANTY => null
978 ,x_NESTING_INCREMENT => null
979 ,x_UOM_NESTING_INCREMENT => null
980 ,x_IS_TRADE_ITEM_RECALLED => null
981 ,x_MODEL_NUMBER => null
982 ,x_PIECES_PER_TRADE_ITEM => null
983 ,x_UOM_PIECES_PER_TRADE_ITEM => null
984 ,x_DEPT_OF_TRNSPRT_DANG_GOODS_N => null
985 ,x_RETURN_GOODS_POLICY => null
986 ,x_IS_OUT_OF_BOX_PROVIDED => null
987 ,x_REGISTRATION_UPDATE_DATE => null
988 ,x_TP_NEUTRAL_UPDATE_DATE => null
989 ,x_IS_BARCODE_SYMBOLOGY_DERIVAB => null
990 ,x_INVOICE_NAME => null
991 ,x_DESCRIPTIVE_SIZE => null
992 ,x_FUNCTIONAL_NAME => null
993 ,x_TRADE_ITEM_FORM_DESCRIPTION => null
994 ,x_WARRANTY_DESCRIPTION => null
995 ,x_TRADE_ITEM_FINISH_DESCRIPTIO => null
996 ,x_DESCRIPTION_SHORT => null
997 ,x_CREATION_DATE => sysdate
998 ,x_CREATED_BY => l_user_id
999 ,x_LAST_UPDATE_DATE => sysdate
1000 ,x_LAST_UPDATED_BY => l_user_id
1001 ,x_LAST_UPDATE_LOGIN => l_login_id);
1002
1003 END IF; -- tiud
1004 END IF; -- inv_id
1005
1006 For l_comps_rec in l_comps_csr (
1007 p_org_id => p_organization_id,
1008 p_item_id => l_assy_rec.inventory_item_id,
1009 p_unit_number => null,
1010 p_eff_date => sysdate,
1011 p_alternate_bom_code => p_alternate_bom_code
1012 )
1013 LOOP
1014 l_stmt := '4';
1015
1016 -- For all components first compute their
1017 -- attribute values
1018 l_component_seq_tbl(l_component_seq_tbl.COUNT + 1).component_sequence_id :=
1019 l_comps_rec.component_sequence_id;
1020 l_component_seq_tbl(l_component_seq_tbl.COUNT).component_item_id :=
1021 l_comps_rec.component_item_id;
1022 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT + 1).component_sequence_id :=
1023 l_comps_rec.component_sequence_id;
1024
1025 l_Component_Attrs := G_ATTRIBUTE_MAP;
1026
1027 FOR map_index in 1..G_Attribute_Map.COUNT
1028 LOOP
1029 --
1030 -- Call the compute function of the
1031 -- attributes that have been defined
1032 -- else use the attribute_value
1033 --
1034 l_component_attrs(map_index).attribute_name := G_Attribute_Map(map_index).attribute_name;
1035
1036 --
1037 -- since we are indexing by number, need to have the if
1038 -- for attribute name
1039 --
1040 IF G_Attribute_Map(map_index).attribute_name = 'UNIT_WEIGHT'
1041 THEN
1042 l_component_attrs(map_index).attribute_value :=
1043 l_comps_rec.unit_weight;
1044 ELSIF G_Attribute_Map(map_index).attribute_name = 'GROSS_WEIGHT'
1045 THEN
1046 l_component_attrs(map_index).attribute_value :=
1047 l_comps_rec.gross_weight;
1048 ELSIF G_Attribute_Map(map_index).attribute_name = 'IS_TRADE_ITEM_INFO_PRIVATE'
1049 THEN
1050 l_component_attrs(map_index).attribute_value :=
1051 l_comps_rec.is_trade_item_info_private;
1052 ELSIF G_Attribute_Map(map_index).attribute_name = 'CUSTOMER_ORDER_ENABLED_FLAG'
1053 THEN
1054 l_component_attrs(map_index).attribute_value :=
1055 l_comps_rec.customer_order_enabled_flag;
1056 ELSIF G_Attribute_Map(map_index).attribute_name = 'COMPONENT_QUANTITY'
1057 THEN
1058 l_component_attrs(map_index).attribute_value :=
1059 l_comps_rec.component_quantity;
1060 ELSIF G_Attribute_Map(map_index).attribute_name = 'BRAND_OWNER_NAME'
1061 THEN
1062 l_component_attrs(map_index).attribute_value :=
1063 l_comps_rec.brand_owner_name;
1064 ELSIF G_Attribute_Map(map_index).attribute_name = 'BRAND_OWNER_GLN'
1065 THEN
1066 l_component_attrs(map_index).attribute_value :=
1067 l_comps_rec.brand_owner_gln;
1068 ELSIF G_Attribute_Map(map_index).attribute_name = 'FUNCTIONAL_NAME'
1069 THEN
1070 l_component_attrs(map_index).attribute_value :=
1071 l_comps_rec.functional_name;
1072 ELSIF G_Attribute_Map(map_index).attribute_name = 'SUB_BRAND'
1073 THEN
1074 l_component_attrs(map_index).attribute_value :=
1075 l_comps_rec.sub_brand;
1076 ELSIF G_Attribute_Map(map_index).attribute_name = 'NET_WEIGHT_UOM'
1077 THEN
1078 l_component_attrs(map_index).attribute_value :=
1079 l_comps_rec.NET_WT_UOM;
1080 ELSIF G_Attribute_Map(map_index).attribute_name = 'GROSS_WEIGHT_UOM'
1081 THEN
1082 l_component_attrs(map_index).attribute_value :=
1083 l_comps_rec.GROSS_WT_UOM;
1084 ELSIF G_Attribute_Map(map_index).attribute_name = 'STORAGE_HANDLING_TEMP_MIN'
1085 THEN
1086 l_component_attrs(map_index).attribute_value :=
1087 l_comps_rec.storage_handling_temp_min;
1088 ELSIF G_Attribute_Map(map_index).attribute_name = 'UOM_STORAGE_HANDLING_TEMP_MIN'
1089 THEN
1090 l_component_attrs(map_index).attribute_value :=
1091 l_comps_rec.uom_storage_handling_temp_min;
1092 ELSIF G_Attribute_Map(map_index).attribute_name = 'STORAGE_HANDLING_TEMP_MAX'
1093 THEN
1094 l_component_attrs(map_index).attribute_value :=
1095 l_comps_rec.storage_handling_temp_max;
1096 ELSIF G_Attribute_Map(map_index).attribute_name = 'UOM_STORAGE_HANDLING_TEMP_MAX'
1097 THEN
1098 l_component_attrs(map_index).attribute_value :=
1099 l_comps_rec.uom_storage_handling_temp_max;
1100 END IF;
1101
1102 l_comp_sequence := l_comps_rec.component_sequence_id;
1103
1104 IF G_Attribute_Map(map_index).compute_function is not null
1105 THEN
1106 /*
1107
1108 -- call the compute function
1109 EXECUTE IMMEDIATE ' ' ||
1110 ' BEGIN ' ||
1111 G_Attribute_Map(map_index).compute_function ||
1112 '(:attr,:component_sequence_id); END;'
1113 USING IN OUT l_component_attrs(map_index).computed_value,
1114 l_comp_sequence;
1115 */
1116 null;
1117 ELSE
1118 null;
1119 --
1120 -- no need to copy the original value into the computed value
1121 -- since we'll use the original value if the computed field is
1122 -- blank.
1123 --
1124 END IF;
1125
1126 END LOOP; --1..G_ATTRIBUTE_MAP.Count
1127
1128 -- store back the component attribute map
1129 -- this location is initialized at the start so just use COUNT.
1130 l_component_seq_tbl(l_component_seq_tbl.COUNT).component_sequence_id :=
1131 l_comps_rec.component_sequence_id;
1132
1133 -- copy into comp_map_map
1134 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT).component_sequence_id :=
1135 l_comps_rec.component_sequence_id;
1136
1137 l_first_run := TRUE;
1138 FOR i IN l_Component_Attrs.FIRST .. l_Component_Attrs.LAST LOOP
1139
1140 IF NOT l_first_run THEN
1141
1142 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT + 1).component_sequence_id :=
1143 l_comps_rec.component_sequence_id;
1144
1145 END IF;
1146
1147 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT).object_name :=
1148 l_Component_Attrs(i).object_name;
1149 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT).attribute_group :=
1150 l_Component_Attrs(i).attribute_group;
1151 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT).attribute_name :=
1152 l_Component_Attrs(i).attribute_name;
1153 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT).attribute_value :=
1154 l_Component_Attrs(i).attribute_value;
1155 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT).computed_value :=
1156 l_Component_Attrs(i).computed_value;
1157 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT).compute_function :=
1158 l_Component_Attrs(i).compute_function;
1159 l_component_seq_attrs_tbl(l_component_seq_attrs_tbl.COUNT).attribute_type :=
1160 l_Component_Attrs(i).attribute_type;
1161
1162 l_first_run := FALSE;
1163
1164 END LOOP;
1165
1166 END LOOP; -- components
1167
1168 l_stmt := '5';
1169 --
1170 -- Once all the attributes are computed then proceed with invoking the
1171 -- attribute rollup functions. The function would know what it should do
1172 -- with the attributes. The Rollup function will have access to the
1173 --
1174 l_Header_Attrs_Map.DELETE; -- clean and start
1175
1176 --Commented for leaf node
1177 /* IF l_component_attrs_map.COUNT <> 0
1178 THEN*/
1179 FOR action_index in 1..p_action_map.COUNT
1180 LOOP
1181 --
1182 -- Perform rollup for all the top level
1183 --
1184 FOR l_indx IN 1..G_Rollup_Action_Map.COUNT
1185 LOOP
1186 IF G_Rollup_Action_Map(l_indx).Rollup_Action =
1187 p_action_map(action_index).Rollup_Action
1188 THEN
1189 l_act_indx := l_indx;
1190 END IF;
1191 END LOOP;
1192
1193
1194 WRITE_DEBUG_LOG (
1195 p_bo_identifier => G_BO_IDENTIFIER
1196 , p_message => 'Calling Rollup Function '||G_Rollup_Action_Map(l_act_indx).Rollup_function);
1197
1198 EXECUTE IMMEDIATE ' ' ||
1199 ' BEGIN ' || G_Rollup_Action_Map(l_act_indx).Rollup_function ||
1200 '(:header_item_id, :organization_id, :validate, :halt_on_error, :return_status, :error_message); END;'
1201 USING IN l_assy_rec.inventory_item_id
1202 ,IN p_organization_id
1203 ,IN p_validate
1204 ,IN p_halt_on_error
1205 ,OUT x_return_status
1206 ,OUT x_msg_data;
1207
1208 IF p_halt_on_error = 'Y' AND
1209 x_return_status IS NOT NULL AND
1210 x_return_status <> 'S' THEN
1211
1212 WRITE_DEBUG_LOG (
1213 p_bo_identifier => G_BO_IDENTIFIER
1214 , p_message => 'Error in Rollup Function, halting rollup. ret='||x_return_status||' msgdata='||x_msg_data);
1215
1216 CLOSE l_LowLevelCode_csr;
1217 CLOSE c_assy_csr;
1218 CLOSE l_comps_csr;
1219 RETURN;
1220
1221 END IF;
1222
1223 END LOOP;
1224 --END IF;
1225
1226 --
1227 -- Now that the attributes are computed and the rollup for that level has happened
1228 -- write the data for that sub-tree node.
1229 --
1230
1231 -- For the TOP Item also process the attributes only maintained at the top level.
1232 IF l_assy_rec.inventory_item_id = Bom_Rollup_Pub.Get_top_Item_Id
1233 THEN
1234 Handle_Attribute_Updates( p_Header_Item_Id => l_assy_rec.inventory_item_id
1235 , p_organization_id => p_organization_id
1236 , p_header_attrs_flag => 'N'
1237 , p_action_map => p_action_map
1238 , x_return_status => x_return_status
1239 , x_msg_count => x_msg_count
1240 , x_msg_data => x_msg_data
1241 );
1242 END IF;
1243
1244 -- suppress updates of attributes for items outside p_parent_item_id's lineage
1245 IF p_parent_item_id IS NULL OR
1246 l_parent_item_tbl.EXISTS(l_assy_rec.inventory_item_id) THEN
1247
1248 -- add to table keeping track of p_component_item_id's lineage
1249 IF p_parent_item_id IS NOT NULL AND
1250 l_assy_rec.parent_item_id IS NOT NULL AND
1251 NOT l_parent_item_tbl.EXISTS(l_assy_rec.parent_item_id)
1252 THEN
1253 l_parent_item_tbl(l_assy_rec.parent_item_id).inventory_item_id := l_assy_rec.parent_item_id;
1254 l_parent_item_tbl(l_assy_rec.parent_item_id).organization_id := p_organization_id;
1255 END IF;
1256
1257 Handle_Attribute_Updates( p_Header_Item_Id => l_assy_rec.inventory_item_id
1258 , p_organization_id => p_organization_id
1259 , p_header_attrs_flag => 'Y'
1260 , p_action_map => p_action_map
1261 , x_return_status => x_return_status
1262 , x_msg_count => x_msg_count
1263 , x_msg_data => x_msg_data
1264 );
1265
1266 END IF; -- p_parent_item_id is null or l_parent_item_tbl contains item_id
1267
1268 END LOOP; -- Assembly cursor
1269
1270 WRITE_DEBUG_LOG (
1271 p_bo_identifier => G_BO_IDENTIFIER
1272 , p_message => 'After Assembly Cursor');
1273
1274 END LOOP; -- Reverse Topology Traversal Ends
1275
1276 END Handle_Rollup_Actions;
1277
1278 PROCEDURE Perform_Rollup_Private
1279 ( p_item_id IN NUMBER
1280 , p_organization_id IN NUMBER
1281 , p_parent_item_id IN NUMBER
1282 , p_structure_type_id IN NUMBER
1283 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map := G_EMPTY_ACTION_MAP
1284 , p_validate IN VARCHAR2
1285 , p_halt_on_error IN VARCHAR2
1286 , x_error_message OUT NOCOPY VARCHAR2
1287 )
1288 IS
1289
1290
1291 CURSOR c_Top_Items(p_sequence_id NUMBER)
1292 IS
1293 --Commented for Bug 7022872
1294 /*
1295 SELECT impl.parent_item_id
1296 , impl.organization_id
1297 , impl.parent_alternate_designator
1298 , structure_type_id
1299 FROM bom_implosions_v impl
1300 where
1301 impl.sequence_id = p_sequence_id
1302 and impl.top_item_flag='Y'
1303 and ( current_level = 0
1304 OR EXISTS(SELECT 1
1305 FROM bom_structures_b bom
1306 WHERE bom.assembly_item_id = impl.parent_item_id
1307 AND bom.organization_id = impl.organization_id
1308 AND nvl(bom.alternate_bom_designator,'xxxxxxxxxxx') =
1309 nvl(impl.parent_alternate_designator,'xxxxxxxxxxx')
1310 AND bom.is_preferred = 'Y'
1311 AND bom.structure_type_id = p_structure_type_id
1312 AND impl.structure_type_id = p_structure_type_id
1313 )
1314 );
1315 */
1316 --Query modified to improve performance --bug 7022872
1317 SELECT IMPL.PARENT_ITEM_ID ,
1318 IMPL.ORGANIZATION_ID ,
1319 IMPL.ALTERNATE_DESIGNATOR AS PARENT_ALTERNATE_DESIGNATOR ,
1320 IMPL.STRUCTURE_TYPE_ID
1321 FROM BOM_SMALL_IMPL_TEMP IMPL
1322 WHERE IMPL.SEQUENCE_ID = p_sequence_id
1323 AND IMPL.TOP_ITEM_FLAG = 'Y'
1324 AND ( CURRENT_LEVEL = 0
1325 OR EXISTS(
1326 SELECT 1
1327 FROM BOM_STRUCTURES_B BOM
1328 WHERE BOM.ASSEMBLY_ITEM_ID = IMPL.PARENT_ITEM_ID
1329 AND BOM.ORGANIZATION_ID = IMPL.ORGANIZATION_ID
1330 AND NVL(BOM.ALTERNATE_BOM_DESIGNATOR,'xxxxxxxxxxx') =
1331 NVL(IMPL.ALTERNATE_DESIGNATOR,'xxxxxxxxxxx')
1332 AND BOM.IS_PREFERRED = 'Y'
1333 AND BOM.STRUCTURE_TYPE_ID = p_structure_type_id
1334 AND IMPL.STRUCTURE_TYPE_ID = p_structure_type_id ) )
1335 AND EXISTS (
1336 SELECT ORGANIZATION_ID
1337 FROM MTL_PARAMETERS MP
1338 WHERE MP.ORGANIZATION_ID = IMPL.ORGANIZATION_ID
1339 )
1340 AND EXISTS (
1341 SELECT MSIVL_PARENT.INVENTORY_ITEM_ID
1342 FROM MTL_SYSTEM_ITEMS_VL MSIVL_PARENT,
1343 FND_LOOKUP_VALUES FL_ITEM_TYPE
1344 WHERE MSIVL_PARENT.INVENTORY_ITEM_ID = IMPL.PARENT_PK1_VALUE
1345 AND MSIVL_PARENT.ORGANIZATION_ID = IMPL.PARENT_PK2_VALUE
1346 AND MSIVL_PARENT.ITEM_TYPE = FL_ITEM_TYPE.LOOKUP_CODE(+)
1347 AND FL_ITEM_TYPE.LOOKUP_TYPE(+) = 'ITEM_TYPE'
1348 AND FL_ITEM_TYPE.LANGUAGE(+) = USERENV('LANG')
1349 AND FL_ITEM_TYPE.VIEW_APPLICATION_ID(+) = 3
1350 AND ( ( FL_ITEM_TYPE.LOOKUP_CODE IS NULL )
1351 OR ( FL_ITEM_TYPE.SECURITY_GROUP_ID =
1352 FND_GLOBAL.LOOKUP_SECURITY_GROUP(FL_ITEM_TYPE.LOOKUP_TYPE,
1353 FL_ITEM_TYPE.VIEW_APPLICATION_ID) )
1354 )
1355 )
1356 AND EXISTS (
1357 SELECT ML_EFFECTIVITY_CONTROL.LOOKUP_TYPE
1358 FROM MFG_LOOKUPS ML_EFFECTIVITY_CONTROL
1359 WHERE ML_EFFECTIVITY_CONTROL.LOOKUP_TYPE = 'BOM_EFFECTIVITY_CONTROL'
1360 AND ( ( ( IMPL.EFFECTIVITY_CONTROL IS NULL ) AND
1361 ( ML_EFFECTIVITY_CONTROL.LOOKUP_CODE = 1 ) )
1362 OR ( ML_EFFECTIVITY_CONTROL.LOOKUP_CODE = IMPL.EFFECTIVITY_CONTROL )
1363 )
1364 );
1365
1366 CURSOR c_Preferred_Structure(p_assembly_item_id in varchar2,
1367 p_org_id in varchar2,
1368 p_struct_type_id in varchar2)
1369 IS
1370 SELECT
1371 alternate_bom_designator
1372 FROM
1373 bom_structures_b
1374 WHERE
1375 assembly_item_id = p_assembly_item_id
1376 AND organization_id = p_org_id
1377 AND structure_type_id = p_struct_type_id
1378 AND is_Preferred = 'Y';
1379
1380 l_rollup_id NUMBER;
1381 l_Sequence NUMBER;
1382 l_alternate_bom_code varchar2(30) := FND_LOAD_UTIL.NULL_VALUE;
1383 l_parents_for_pk1 number;
1384 l_check_no_bill boolean := true;
1385 l_check_preferred boolean := true;
1386 l_rollup_item_id number;
1387 l_return_status VARCHAR2(1);
1388 l_msg_count NUMBER;
1389 BEGIN
1390 if (Bom_Rollup_Pub.g_attr_diffs is null) then
1391 --Initialize Error Hanlder
1392 Initialize_ErrorDebug_Handler;
1393 end if;
1394
1395 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'Perform_Rollup_Private called item '||p_item_id||'-'||p_organization_id||' parent item '||p_parent_item_id);
1396 -- Rollup optimization:
1397 -- changed to parent so that comp. addition does not trigger additional rollups
1398 l_rollup_item_id := NVL(p_parent_item_id, p_item_id);
1399
1400 -- Ensure that this item is UCCNet enabled
1401 IF (Is_Pack_Item(p_item_id, p_organization_id) <> 'Y')
1402 THEN
1403 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'Perform_Rollup_Private called on non-pack item '||p_item_id||'-'||p_organization_id||' ['||Is_Pack_Item(p_item_id, p_organization_id)||']');
1404 RETURN;
1405 END IF;
1406
1407 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, '*****************TESTING****************');
1408 IF G_Attribute_Map.COUNT = 0
1409 THEN
1410 Load_Attribute_Map;
1411 END IF;
1412
1413 IF G_Rollup_Action_Map.COUNT = 0
1414 THEN
1415 Load_Rollup_Action_Map;
1416 END IF;
1417
1418 --For disabling we need to get the preferred alternate structure
1419 for pralt in c_Preferred_Structure(p_assembly_item_id => p_item_id
1420 ,p_org_id => p_organization_id
1421 ,p_struct_type_id => p_structure_type_id)
1422
1423 LOOP
1424 l_alternate_bom_code := pralt.alternate_bom_designator;
1425 END LOOP;
1426
1427
1428 --
1429 -- Load the top items for rollup
1430 --
1431 Load_Top_Items( p_item_id => l_rollup_item_id
1432 , p_organization_id => p_organization_id
1433 , p_alternate_bom_code => l_alternate_bom_code
1434 , x_Sequence => l_Sequence
1435 );
1436
1437 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'Structure Type Id is ' || p_structure_type_id);
1438
1439 BEGIN
1440 IF (p_structure_type_id IS NOT NULL)
1441 THEN
1442 SELECT
1443 COUNT(COMPONENT_SEQUENCE_ID) INTO l_parents_for_pk1
1444 from
1445 BOM_SMALL_IMPL_TEMP
1446 WHERE
1447 LOWEST_ITEM_ID = l_rollup_item_id
1448 AND ORGANIZATION_ID = p_organization_id
1449 AND CURRENT_LEVEL > 0
1450 AND SEQUENCE_ID = l_Sequence; --changed to 0 as the sql won't work
1451
1452 BEGIN
1453 IF (l_parents_for_pk1 = 0)
1454 THEN
1455 SELECT
1456 ALTERNATE_BOM_DESIGNATOR INTO l_alternate_bom_code
1457 FROM
1458 BOM_STRUCTURES_B
1459 WHERE
1460 ASSEMBLY_ITEM_ID = l_rollup_item_id
1461 AND ORGANIZATION_ID = p_organization_id
1462 AND STRUCTURE_TYPE_ID = p_structure_type_id
1463 AND IS_PREFERRED = 'Y';
1464
1465 UPDATE
1466 BOM_SMALL_IMPL_TEMP
1467 SET
1468 TOP_ITEM_FLAG ='Y',
1469 ALTERNATE_DESIGNATOR = l_alternate_bom_code,
1470 STRUCTURE_TYPE_ID = p_structure_type_id
1471 WHERE
1472 CURRENT_LEVEL = 0
1473 AND SEQUENCE_ID = l_Sequence
1474 AND LOWEST_ITEM_ID = l_rollup_item_id
1475 AND ORGANIZATION_ID = p_organization_id ;
1476 END IF;
1477 EXCEPTION
1478 WHEN OTHERS THEN
1479 l_alternate_bom_code := NULL;
1480 BEGIN
1481 UPDATE
1482 BOM_SMALL_IMPL_TEMP
1483 SET
1484 TOP_ITEM_FLAG ='Y'
1485 WHERE
1486 CURRENT_LEVEL = 0
1487 AND SEQUENCE_ID = l_Sequence
1488 AND LOWEST_ITEM_ID = l_rollup_item_id
1489 AND ORGANIZATION_ID = p_organization_id ;
1490 END;
1491 END;
1492 END IF;
1493 END;
1494
1495 FOR top_items in c_Top_Items(p_sequence_id => l_Sequence)
1496 LOOP
1497 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE C_TOP_ITEMS' || top_items.structure_type_id
1498 || ' sequence '||l_Sequence||' parent alt '||top_items.parent_alternate_designator
1499 );
1500 --if (top_items.structure_type_id is not null) then
1501 l_check_no_bill := false;
1502 --Removed for 11.5.10-E
1503 --delete from bom_explosions_all;
1504 l_rollup_id := to_number(to_char(sysdate,'SSSS'));
1505
1506
1507 l_Top_Item_Attrs_Map.DELETE;
1508 l_Header_Attrs_Map.DELETE;
1509 -- This map must be clean for every new top item.
1510
1511 WRITE_DEBUG_LOG (
1512 p_bo_identifier => G_BO_IDENTIFIER
1513 , p_message => 'Calling Explosion');
1514
1515 BOMPCCLT.Process_Items(
1516 p_org_id => p_organization_id
1517 , p_item_id => top_items.parent_item_id
1518 , p_roll_id => l_rollup_id
1519 , p_unit_number => null
1520 , p_eff_date => sysdate
1521 , p_alternate_bom_code => top_items.parent_alternate_designator
1522 , p_prgm_id => -1
1523 , p_prgm_app_id => -1
1524 , p_req_id => -1
1525 , x_err_msg => x_error_message
1526 );
1527
1528 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'B4 Validate Hierarchy Attrs');
1529
1530 /* Added validation for UCCNET 3.0 Certification */
1531 BOM_GTIN_RULES.Validate_Hierarchy_ATTRS (
1532 p_group_id => l_rollup_id
1533 , x_return_status => l_return_status
1534 , x_error_message => x_error_message);
1535
1536 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'After Validate Hierarchy Attrs Status' || l_return_status);
1537
1538 IF l_return_status IS NOT NULL AND
1539 l_return_status <> 'S'
1540 THEN
1541 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'ERROR in Validate Hierarchy Attrs! ['||l_return_status||']'||x_error_message);
1542 Close_ErrorDebug_Handler;
1543 RETURN;
1544 END IF;
1545
1546 WRITE_DEBUG_LOG (
1547 p_bo_identifier => G_BO_IDENTIFIER
1548 , p_message => 'Finished Explosion with error message ' || x_error_message);
1549
1550 --Run Rollup for only preferred Structures
1551 --Check isPrefered
1552 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr assitem' || top_items.parent_item_id );
1553 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr orgid' || p_organization_id );
1554 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr structure' || p_structure_type_id );
1555
1556 For is_preferred_check_cr in c_Preferred_Structure(p_assembly_item_id => top_items.parent_item_id
1557 ,p_org_id => p_organization_id
1558 ,p_struct_type_id => p_structure_type_id)
1559
1560 LOOP
1561 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr top_items. parent alt desig' || top_items.parent_alternate_designator );
1562 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'INSIDE is_preferred_check_cr pref alt desig ' || is_preferred_check_cr.alternate_bom_designator );
1563
1564 if (nvl(top_items.parent_alternate_designator,'XXXXXXXX') = nvl(is_preferred_check_cr.alternate_bom_designator,'XXXXXXXX')) then
1565 l_check_preferred := true;
1566 else
1567 l_check_preferred := false;
1568 end if;
1569 END LOOP;
1570
1571 if (l_check_preferred) then
1572 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'Before Handle Rollup Actions');
1573 Handle_Rollup_Actions (
1574 p_rollup_id => l_rollup_id
1575 , p_action_map => p_action_map
1576 , p_item_id => top_items.parent_item_id
1577 , p_organization_id => p_organization_id
1578 , p_parent_item_id => p_parent_item_id
1579 , p_component_item_id => p_item_id
1580 , p_alternate_bom_code => top_items.parent_alternate_designator
1581 , p_validate => p_validate
1582 , p_halt_on_error => p_halt_on_error
1583 , x_return_status => l_return_status
1584 , x_msg_count => l_msg_count
1585 , x_msg_data => x_error_message
1586 );
1587 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'After Handle Rollup Actions');
1588
1589 -- do not proceed with any other rollups if error is found
1590 IF p_halt_on_error = 'Y' AND
1591 l_return_status IS NOT NULL AND
1592 l_return_status <> 'S'
1593 THEN
1594
1595 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'ERROR in Handle Rollup Actions! ['||l_return_status||']'||x_error_message);
1596 Close_ErrorDebug_Handler;
1597 RETURN;
1598
1599 END IF;
1600 end if;
1601 --end if;
1602 END LOOP;
1603
1604 --Close Error Hanlder
1605 Close_ErrorDebug_Handler;
1606
1607
1608
1609 END Perform_Rollup_Private;
1610
1611 PROCEDURE Perform_Rollup_Private
1612 ( p_item_id IN NUMBER
1613 , p_organization_id IN NUMBER
1614 , p_alternate_bom_code IN VARCHAR2
1615 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map := G_EMPTY_ACTION_MAP
1616 , p_validate IN VARCHAR2
1617 , p_halt_on_error IN VARCHAR2
1618 , x_error_message OUT NOCOPY VARCHAR2
1619 )
1620 IS
1621 CURSOR c_Top_Items(p_sequence_id NUMBER)
1622 IS
1623 SELECT parent_item_id
1624 , organization_id
1625 FROM bom_implosions_v
1626 WHERE sequence_id = p_sequence_id
1627 AND top_item_flag = 'Y';
1628
1629 l_rollup_id NUMBER;
1630 l_Sequence NUMBER;
1631 l_return_status VARCHAR2(1);
1632 l_msg_count NUMBER;
1633 BEGIN
1634 if (Bom_Rollup_Pub.g_attr_diffs is null) then
1635 --Initialize Error Hanlder
1636 Initialize_ErrorDebug_Handler;
1637 end if;
1638
1639 IF G_Attribute_Map.COUNT = 0
1640 THEN
1641 Load_Attribute_Map;
1642 END IF;
1643
1644 IF G_Rollup_Action_Map.COUNT = 0
1645 THEN
1646 Load_Rollup_Action_Map;
1647 END IF;
1648 --
1649 -- Load the top items for rollup
1650 --
1651
1652 Load_Top_Items( p_item_id => p_item_id
1653 , p_organization_id => p_organization_id
1654 , p_alternate_bom_code => p_alternate_bom_code
1655 , x_Sequence => l_Sequence
1656 );
1657
1658 FOR top_items in c_Top_Items(p_sequence_id => l_Sequence)
1659 LOOP
1660 --Removed for 11.5.10-E
1661 --delete from bom_explosions_all;
1662 l_rollup_id := to_number(to_char(sysdate,'SSSS'));
1663
1664
1665 l_Top_Item_Attrs_Map.DELETE;
1666 l_Header_Attrs_Map.DELETE;
1667 -- This map must be clean for every new top item.
1668
1669 WRITE_DEBUG_LOG (
1670 p_bo_identifier => G_BO_IDENTIFIER
1671 , p_message => 'Processing Items for Explosion' );
1672
1673 BOMPCCLT.Process_Items(
1674 p_org_id => p_organization_id
1675 , p_item_id => top_items.parent_item_id
1676 , p_roll_id => l_rollup_id
1677 , p_unit_number => null
1678 , p_eff_date => sysdate
1679 , p_alternate_bom_code => p_alternate_bom_code
1680 , p_prgm_id => -1
1681 , p_prgm_app_id => -1
1682 , p_req_id => -1
1683 , x_err_msg => x_error_message
1684 );
1685 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'B4 - 1 Validate Hierarchy Attrs');
1686
1687 BOM_GTIN_RULES.Validate_Hierarchy_ATTRS (
1688 p_group_id => l_rollup_id
1689 , x_return_status => l_return_status
1690 , x_error_message => x_error_message);
1691
1692 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'After - 1 Validate Hierarchy Attrs Status' || l_return_status);
1693
1694 IF l_return_status IS NOT NULL AND
1695 l_return_status <> 'S'
1696 THEN
1697 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'ERROR in Validate Hierarchy Attrs! ['||l_return_status||']'||x_error_message);
1698 Close_ErrorDebug_Handler;
1699 RETURN;
1700 END IF;
1701
1702 WRITE_DEBUG_LOG (
1703 p_bo_identifier => G_BO_IDENTIFIER
1704 , p_message => 'Handling ROllup Actions' );
1705
1706 Handle_Rollup_Actions
1707 ( p_rollup_id => l_rollup_id
1708 , p_action_map => p_action_map
1709 , p_item_id => top_items.parent_item_id
1710 , p_organization_id => p_organization_id
1711 , p_alternate_bom_code => p_alternate_bom_code
1712 , p_validate => p_validate
1713 , p_halt_on_error => p_halt_on_error
1714 , x_return_status => l_return_status
1715 , x_msg_count => l_msg_count
1716 , x_msg_data => x_error_message
1717 );
1718
1719 -- do not proceed with any other rollups if error is found
1720 IF p_halt_on_error = 'Y' AND
1721 l_return_status IS NOT NULL AND
1722 l_return_status <> 'S'
1723 THEN
1724
1725 WRITE_DEBUG_LOG(G_BO_IDENTIFIER, 'ERROR in Handle Rollup Actions! ['||l_return_status||']'||x_error_message);
1726 Close_ErrorDebug_Handler;
1727 RETURN;
1728
1729 END IF;
1730
1731 --
1732 -- Delete the processed rows and clean slate for the next
1733 -- rollup
1734 --
1735 --BOMPCCLT.Delete_Processed_Rows(l_rollup_id);
1736
1737 END LOOP;
1738
1739 --Close Error Hanlder
1740 Close_ErrorDebug_Handler;
1741
1742 END Perform_Rollup_Private;
1743
1744 /****************** Local Procedures Section Ends ******************/
1745
1746 /************************************************************************
1747 * Procedure: Perform_Rollup
1748 * Purpose : This method will perform rollup or propogation of attributes
1749 * The attribute value propogated or computed up the bom is based
1750 * on the value returned by the compute_function.
1751 * Compute function will be passed Attribute Map and the list of
1752 * child components.
1753 *
1754 **************************************************************************/
1755
1756 PROCEDURE Perform_Rollup
1757 ( p_item_id IN NUMBER
1758 , p_organization_id IN NUMBER
1759 , p_alternate_bom_code IN VARCHAR2
1760 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map DEFAULT G_EMPTY_ACTION_MAP
1761 , p_validate IN VARCHAR2 /*DEFAULT 'N'*/
1762 , p_halt_on_error IN VARCHAR2 /*DEFAULT 'N'*/
1763 , x_error_message OUT NOCOPY VARCHAR2
1764 )
1765 IS
1766 BEGIN
1767
1768 Perform_Rollup_Private
1769 ( p_item_id => p_item_id
1770 , p_organization_id => p_organization_id
1771 , p_alternate_bom_code => p_alternate_bom_code
1772 , p_action_map => p_action_map
1773 , p_validate => p_validate
1774 , p_halt_on_error => p_halt_on_error
1775 , x_error_message => x_error_message
1776 );
1777 END Perform_Rollup;
1778
1779 /************************************************************************
1780 * Procedure: Perform_Rollup
1781 * Purpose : Method accepts a Structure Type. The Rollup will happen for the
1782 * Preferred Structure within the given Structure.
1783 * This method will perform rollup or propogation of attributes
1784 * The attribute value propogated or computed up the bom is based
1785 * on the value returned by the compute_function.
1786 * Compute function will be passed Attribute Map and the list of
1787 * child components.
1788 *
1789 **************************************************************************/
1790
1791 PROCEDURE Perform_Rollup
1792 ( p_item_id IN NUMBER
1793 , p_organization_id IN NUMBER
1794 , p_parent_item_id IN NUMBER := NULL
1795 , p_structure_type_id IN NUMBER
1796 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map := G_EMPTY_ACTION_MAP
1797 , p_validate IN VARCHAR2 /*DEFAULT 'N'*/
1798 , p_halt_on_error IN VARCHAR2 /*DEFAULT 'N'*/
1799 , x_error_message OUT NOCOPY VARCHAR2
1800 )
1801 IS
1802 BEGIN
1803 Perform_Rollup_Private
1804 ( p_item_id => p_item_id
1805 , p_organization_id => p_organization_id
1806 , p_parent_item_id => p_parent_item_id
1807 , p_structure_type_id => p_structure_type_id
1808 , p_action_map => p_action_map
1809 , p_validate => p_validate
1810 , p_halt_on_error => p_halt_on_error
1811 , x_error_message => x_error_message
1812 );
1813
1814 END Perform_Rollup;
1815
1816 /************************************************************************
1817 * Procedure: Perform_Rollup
1818 * Purpose : Method accepts a Structure Type identifier. The Rollup will happen
1819 * for the Preferred Structure within the given Structure.
1820 * This method will perform rollup or propogation of attributes
1821 * The attribute value propogated or computed up the bom is based
1822 * on the value returned by the compute_function.
1823 * Compute function will be passed Attribute Map and the list of
1824 * child components.
1825 *
1826 **************************************************************************/
1827 PROCEDURE Perform_Rollup
1828 ( p_item_id IN NUMBER
1829 , p_organization_id IN NUMBER
1830 , p_parent_item_id IN NUMBER := NULL
1831 , p_structure_type_name IN VARCHAR2
1832 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map := G_EMPTY_ACTION_MAP
1833 , p_validate IN VARCHAR2 /*DEFAULT 'N'*/
1834 , p_halt_on_error IN VARCHAR2 /*DEFAULT 'N'*/
1835 , x_error_message OUT NOCOPY VARCHAR2
1836 )
1837 IS
1838 CURSOR c_structure_type_id IS
1839 SELECT structure_type_id
1840 FROM bom_structure_types_B
1841 WHERE structure_type_name = p_structure_type_name;
1842 BEGIN
1843
1844
1845 FOR struct_type IN c_structure_type_id
1846 LOOP
1847 Perform_Rollup_Private
1848 ( p_item_id => p_item_id
1849 , p_organization_id => p_organization_id
1850 , p_parent_item_id => p_parent_item_id
1851 , p_structure_type_id => struct_type.structure_type_id
1852 , p_action_map => p_action_map
1853 , p_validate => p_validate
1854 , p_halt_on_error => p_halt_on_error
1855 , x_error_message => x_error_message
1856 );
1857 END LOOP;
1858
1859 END Perform_Rollup;
1860
1861
1862 /*********************************************************************
1863 * Function: Get_Rollup_Function
1864 * Purpose : Given an Object and the Rollup Action to be performed, this
1865 * function will return the Roll function that will be executed.
1866 *
1867 ************************************************************************/
1868 Function Get_Rollup_Function
1869 ( p_object_name IN VARCHAR2
1870 , p_rollup_action IN VARCHAR2
1871 ) RETURN VARCHAR2
1872 IS
1873 BEGIN
1874 NULL;
1875 -- not implemented yet.
1876 END Get_Rollup_Function;
1877
1878 /*********************************************************************
1879 * Procedure : Add_Rollup_Function
1880 * Purpose : This procedure helps build the Action Map. Once the action
1881 * map is ready, calling application can call perfom_rollup
1882 * with the Rollup Action Map.
1883 * The object/action is checked against the supported set of
1884 * Actions, hence, an incorrect combination will throw a
1885 * 'BOM_OBJECT_ACTION_INVALID' exception.
1886 ************************************************************************/
1887 Procedure Add_Rollup_Function
1888 ( p_Object_Name IN VARCHAR2
1889 , p_Rollup_Action IN VARCHAR2
1890 , p_DML_Function IN VARCHAR2
1891 , p_DML_Delayed_Write IN VARCHAR2
1892 , x_Rollup_Action_Map IN OUT NOCOPY Bom_Rollup_Pub.Rollup_Action_Map
1893 )
1894 IS
1895 l_map_index NUMBER := x_Rollup_Action_Map.COUNT + 1;
1896 BEGIN
1897 FOR cindex IN 1..x_Rollup_Action_Map.COUNT
1898 LOOP
1899 IF(x_Rollup_Action_Map(cindex).Object_Name = p_Object_Name AND
1900 x_Rollup_Action_Map(cindex).Rollup_Action = p_Rollup_Action AND
1901 x_Rollup_Action_Map(cindex).DML_Function = p_DML_Function AND
1902 x_Rollup_Action_Map(cindex).DML_Delayed_Write = p_DML_Delayed_Write
1903 )
1904 THEN
1905 -- do nothing since the action was already registered for the
1906 -- current session.
1907 return;
1908 END IF;
1909
1910 END LOOP;
1911
1912 x_Rollup_Action_Map(l_map_index).Object_Name := p_Object_Name;
1913 x_Rollup_Action_Map(l_map_index).Rollup_Action := p_Rollup_Action;
1914 x_Rollup_Action_Map(l_map_index).DML_Function := p_DML_Function;
1915 x_Rollup_Action_Map(l_map_index).DML_Delayed_Write := p_DML_Delayed_Write;
1916
1917 END Add_Rollup_Function;
1918
1919 /*********************************************************************
1920 * Procedure : Get_Item_Rollup_Map
1921 * Purpose : Returns the supported list of Actions for an Object
1922 *
1923 ************************************************************************/
1924 Function Get_Item_Rollup_Map
1925 ( p_Object_Name IN VARCHAR2 )
1926 RETURN Bom_Rollup_Pub.Rollup_Action_Map
1927 IS
1928 BEGIN
1929 return G_EMPTY_ACTION_MAP;
1930 -- not implemented yet.
1931
1932 END Get_Item_Rollup_Map;
1933
1934 /************************************************************************
1935 * Procedure: Set_Parent_Attribute
1936 * Purpose : Sets the parent attribute. When a computation function
1937 * completes, if it is an attribute that is updated for the immediate
1938 * parent, then the computation function needs to set the attribute value
1939 * for the parent so that the rollup process can issue an call for
1940 * affected parent.
1941 **************************************************************************/
1942 PROCEDURE Set_Parent_Attribute
1943 ( p_attribute_name IN VARCHAR2
1944 , p_attribute_value IN VARCHAR2
1945 )
1946 IS
1947 l_header_attr_cnt NUMBER := l_Header_Attrs_Map.COUNT;
1948 BEGIN
1949 --
1950 -- This is a helper method and is intended simply to take the attributed and append
1951 -- it to the map. this will not offer any intelligence of making sure if the attribute
1952 -- was already set. So, the calling process should ensure of this.
1953 -- we would later add this.
1954 --
1955 l_Header_Attrs_Map(l_Header_Attr_cnt).attribute_name :=
1956 p_attribute_name;
1957
1958 l_Header_Attrs_Map(l_Header_Attr_cnt).attribute_value :=
1959 p_attribute_value;
1960
1961 END Set_Parent_Attribute;
1962
1963 /************************************************************************
1964 * Procedure: Set_Parent_Attribute
1965 * Purpose : Sets the top item's attribute. When a computation function
1966 * completes, for an attribute that is only computed for the top
1967 * most parent, the computation function will call this methid to
1968 * set the Top Item's attribute. The Rollup process will at the
1969 * end issue an appropriate call to update the affected Item.
1970 **************************************************************************/
1971 PROCEDURE Set_Top_Item_Attribute
1972 ( p_attribute_name IN VARCHAR2
1973 , p_attribute_value IN VARCHAR2
1974 )
1975 IS
1976 l_header_attr_cnt NUMBER := l_Top_Item_Attrs_Map.COUNT + 1;
1977 BEGIN
1978 --
1979 -- This is a helper method and is intended simply to take the attributed and append
1980 -- it to the map. this will not offer any intelligence of making sure if the attribute
1981 -- was already set. So, the calling process should ensure of this.
1982 -- we would later add this.
1983 --
1984 FOR map_index IN 1..l_Top_Item_Attrs_Map.COUNT
1985 LOOP
1986 IF(l_Top_Item_Attrs_Map.exists(map_index) AND
1987 l_Top_Item_Attrs_Map(map_index).attribute_name = p_attribute_name)
1988 THEN
1989 l_header_attr_cnt := map_index;
1990 END IF;
1991 END LOOP;
1992 l_Top_Item_Attrs_Map(l_Header_Attr_cnt).attribute_name :=
1993 p_attribute_name;
1994 l_Top_Item_Attrs_Map(l_Header_Attr_cnt).attribute_value :=
1995 p_attribute_value;
1996
1997 END Set_Top_Item_Attribute;
1998
1999
2000 /************************************************************************
2001 * Procedure: Get_Attribute_Value
2002 * Purpose : Returns the attribute value for an attribute. This method
2003 * works only off of the reverse tology tree and has access to
2004 * to immediate children for a parent being processed.
2005 * When a computation function needs attribute value for a
2006 * component, it will request so by passing the component identifier
2007 * and the attribute name.
2008 **************************************************************************/
2009 FUNCTION Get_Attribute_Value
2010 ( p_component_sequence_id IN NUMBER
2011 , p_attribute_name IN VARCHAR2
2012 )
2013 RETURN VARCHAR2
2014 IS
2015 BEGIN
2016 FOR cmp_index IN Bom_Rollup_Pub.l_component_seq_attrs_tbl.FIRST..Bom_Rollup_Pub.l_component_seq_attrs_tbl.LAST
2017 LOOP
2018 IF Bom_Rollup_Pub.l_component_seq_attrs_tbl.EXISTS(cmp_index) AND
2019 Bom_Rollup_Pub.l_component_seq_attrs_tbl(cmp_index).component_sequence_id = p_component_sequence_id AND
2020 Bom_Rollup_Pub.l_component_seq_attrs_tbl(cmp_index).attribute_name = p_attribute_name
2021 THEN
2022 return Bom_Rollup_Pub.l_component_seq_attrs_tbl(cmp_index).attribute_value;
2023 END IF;
2024 END LOOP;
2025
2026 END Get_Attribute_Value;
2027
2028
2029 /************************************************************************
2030 * Procedure: Get_Top_Item_Attribute_Value
2031 * Purpose : Returns the attribute value for an attribute. This method
2032 * works only off of the reverse tology tree and has access to
2033 * to immediate children for a parent being processed.
2034 * When a computation function needs attribute value for a
2035 * component, it will request so by passing the component identifier
2036 * and the attribute name.
2037 **************************************************************************/
2038 FUNCTION Get_Top_Item_Attribute_Value
2039 ( p_attribute_name IN VARCHAR2
2040 )
2041 RETURN VARCHAR2
2042 IS
2043 BEGIN
2044 IF Bom_Rollup_Pub.l_Top_Item_Attrs_Map.COUNT = 0
2045 THEN
2046 RETURN NULL;
2047 END IF;
2048
2049 FOR cmp_index IN Bom_Rollup_Pub.l_Top_Item_Attrs_Map.FIRST..Bom_Rollup_Pub.l_Top_Item_Attrs_Map.LAST
2050 LOOP
2051 IF Bom_Rollup_Pub.l_Top_Item_Attrs_Map.EXISTS(cmp_index) AND
2052 Bom_Rollup_Pub.l_Top_Item_Attrs_Map(cmp_index).attribute_name = p_attribute_name
2053 THEN
2054 return Bom_Rollup_Pub.l_Top_Item_Attrs_Map(cmp_index).attribute_value;
2055 END IF;
2056 END LOOP;
2057
2058 -- else return NULL
2059 RETURN null;
2060
2061 END Get_Top_Item_Attribute_Value;
2062
2063 /************************************************************************
2064 * Procedure: Perform_Rollup
2065 * Purpose : This method will perform rollup for multi-row attributes or
2066 * propogation of multi-row attributes
2067 * The attribute value propogated or computed up the bom is based
2068 * on the value returned by the compute_function.
2069 * The propogation happens for only the attributes being passed
2070 * in EGO_USER_ATTR_DIFF_TABLE
2071 **************************************************************************/
2072 PROCEDURE Perform_Rollup
2073 ( p_item_id IN NUMBER
2074 , p_organization_id IN NUMBER
2075 , p_structure_type_name IN VARCHAR2
2076 , p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2077 , p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2078 , p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
2079 , p_attr_diffs IN EGO_USER_ATTR_DIFF_TABLE
2080 , p_transaction_type IN VARCHAR2
2081 , p_attr_group_id IN NUMBER
2082 , p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map DEFAULT G_EMPTY_ACTION_MAP
2083 , x_error_message OUT NOCOPY VARCHAR2
2084 ) is
2085 begin
2086
2087 Initialize_ErrorDebug_Handler;
2088
2089 WRITE_DEBUG_LOG (
2090 p_bo_identifier => G_BO_IDENTIFIER
2091 , p_message => 'INSIDE Multi-Row Item Call');
2092
2093 IF (p_attr_diffs IS NOT NULL AND p_attr_diffs.COUNT > 0) THEN -- is the object null
2094 Bom_Rollup_Pub.g_pk_column_name_value_pairs := p_pk_column_name_value_pairs;
2095 Bom_Rollup_Pub.g_class_code_name_value_pairs := p_class_code_name_value_pairs;
2096 Bom_Rollup_Pub.g_data_level_name_value_pairs := p_data_level_name_value_pairs;
2097 Bom_Rollup_Pub.g_attr_diffs := p_attr_diffs;
2098 Bom_Rollup_Pub.g_transaction_type := p_transaction_type;
2099 Bom_Rollup_Pub.g_attr_group_id := p_attr_group_id;
2100 WRITE_DEBUG_LOG (
2101 p_bo_identifier => G_BO_IDENTIFIER
2102 , p_message => 'INSIDE Multi-Row Item Call with txn type' || p_transaction_type);
2103 END IF;
2104
2105 Perform_Rollup
2106 ( p_item_id => p_item_id
2107 , p_organization_id => p_organization_id
2108 , p_structure_type_name => p_structure_type_name
2109 , p_action_map => p_action_map
2110 , x_error_message => x_error_message
2111 );
2112
2113 end Perform_Rollup;
2114
2115 FUNCTION Is_UCCNet_Enabled(p_inventory_item_id IN NUMBER
2116 ,p_organization_id IN NUMBER
2117 )
2118 RETURN VARCHAR2
2119 IS
2120 CURSOR c_check_if_uccnet(p_inventory_item_id NUMBER
2121 ,p_organization_id NUMBER) IS
2122 SELECT 'X' assignment_present
2123 FROM mtl_default_category_sets a
2124 , mtl_item_categories b
2125 WHERE a.functional_area_id = 12
2126 AND a.category_set_id = b.category_set_id
2127 AND rownum = 1
2128 AND inventory_item_id = p_inventory_item_id
2129 AND organization_id = p_organization_id;
2130
2131 l_assignment_present VARCHAR2(1);
2132
2133 BEGIN
2134 -- note: do not embed any DML calls (even in debug statements in this function
2135 -- or else we'll see ORA-14551: cannot perform a DML operation inside a query
2136 OPEN c_check_if_uccnet(p_inventory_item_id, p_organization_id);
2137
2138 FETCH c_check_if_uccnet INTO l_assignment_present;
2139 IF c_check_if_uccnet%FOUND THEN
2140
2141 CLOSE c_check_if_uccnet;
2142 RETURN 'Y';
2143
2144 ELSE
2145
2146 CLOSE c_check_if_uccnet;
2147 RETURN 'N';
2148
2149 END IF;
2150
2151 END Is_UCCNet_Enabled;
2152
2153 FUNCTION Get_Trade_Item_Unit_Descriptor
2154 ( p_inventory_item_id IN NUMBER
2155 , p_organization_id IN NUMBER
2156 ) RETURN VARCHAR2
2157 IS
2158 CURSOR c_get_tiud(p_inventory_item_id NUMBER
2159 ,p_organization_id NUMBER) IS
2160 SELECT trade_item_descriptor
2161 FROM ego_items_v
2162 WHERE inventory_item_id = p_inventory_item_id
2163 AND organization_id = p_organization_id;
2164
2165 l_tiud VARCHAR2(35);
2166
2167 BEGIN
2168
2169 OPEN c_get_tiud(p_inventory_item_id, p_organization_id);
2170
2171 FETCH c_get_tiud INTO l_tiud;
2172
2173 IF c_get_tiud%FOUND THEN
2174
2175 CLOSE c_get_tiud;
2176 RETURN l_tiud;
2177
2178 ELSE
2179
2180 CLOSE c_get_tiud;
2181 RETURN null;
2182
2183 END IF;
2184
2185 END Get_Trade_Item_Unit_Descriptor;
2186
2187 FUNCTION Is_Pack_Item(p_inventory_item_id IN NUMBER
2188 ,p_organization_id IN NUMBER
2189 )
2190 RETURN VARCHAR2
2191 IS
2192
2193 l_pack_type VARCHAR2(80);
2194
2195 BEGIN
2196 -- Get the pack type attribute from MSI.
2197 -- IF the pack type is not null then return 'Y' otherwise return 'N'
2198 --SELECT Pack_Type INTO l_pack_type
2199 SELECT TRADE_ITEM_DESCRIPTOR INTO l_pack_type
2200 FROM MTL_SYSTEM_ITEMS_B
2201 WHERE inventory_item_id = p_inventory_item_id
2202 AND organization_id = p_organization_id;
2203
2204 IF l_pack_type IS NOT NULL THEN
2205 RETURN 'Y';
2206 ELSE
2207 RETURN 'N';
2208 END IF;
2209 EXCEPTION
2210 WHEN OTHERS THEN
2211 RETURN 'N';
2212
2213 END Is_Pack_Item;
2214
2215 /************************************************************************
2216 * Procedure: WRITE_DEBUG_LOG
2217 * Purpose : This method will write debug information to the
2218 * to the log file based on MRP_DEBUG Flag
2219 **************************************************************************/
2220 PROCEDURE WRITE_DEBUG_LOG
2221 ( p_bo_identifier IN varchar2
2222 , p_message IN varchar2
2223 )
2224 IS
2225 l_err_text varchar2(3000);
2226 BEGIN
2227 IF (G_DEBUG_FLAG = 'Y') THEN
2228 Error_HandLer.Write_Debug
2229 ( p_debug_message => p_message );
2230 END IF;
2231 exception
2232 WHEN OTHERS THEN
2233 l_err_text := SQLERRM;
2234 l_err_text := 'Error : '||TO_CHAR(SQLCODE)||'---'||l_err_text;
2235 WRITE_ERROR_LOG (
2236 p_bo_identifier => G_BO_IDENTIFIER
2237 , p_message => l_err_text);
2238 END;
2239
2240
2241 /************************************************************************
2242 * Procedure: WRITE_ERROR_LOG
2243 * Purpose : This method will write Errors to
2244 * error handler
2245 **************************************************************************/
2246 PROCEDURE WRITE_ERROR_LOG
2247 ( p_bo_identifier IN varchar2
2248 , p_message IN varchar2
2249 )
2250 IS
2251 BEGIN
2252 Error_Handler.Add_Error_Message
2253 ( p_message_text => p_message
2254 , p_message_type => 'E'
2255 );
2256 END;
2257
2258
2259 END Bom_Rollup_Pub;